Commits

Robert Brewer committed 29fb413

Moved to flows having multiple days to create multiple obligations. Also reified the fulfillments data

Comments (0)

Files changed (5)

flowrate/__init__.py

 
         # Accept the new transaction definition.
         vals = req.json["body"]
-        db.execute("UPDATE transactions SET amount = %s, "
-                   "credit_account = %s, debit_account = %s, postdate = %s, "
-                   "description = %s "
-                   "WHERE id = %s;",
-                   (vals['amount'],
-                    popint(vals['credit']), popint(vals['debit']),
-                    vals['postdate'], vals['description'],
-                    req.txid))
+        credit, debit = popint(vals['credit']), popint(vals['debit'])
+        txrow = db.execute(
+            "UPDATE transactions SET amount = %s, "
+            "credit_account = %s, debit_account = %s, postdate = %s, "
+            "description = %s WHERE id = %s RETURNING *;",
+            (vals['amount'], credit, debit,
+             vals['postdate'], vals['description'], req.txid)).fetchone()
+
+        flows.fulfill(txrow)
+
         cherrypy.response.status = 204
 
     def DELETE(self):
         """Destroy the definition of this transaction."""
         req = cherrypy.serving.request
-        db.execute("DELETE FROM transactions WHERE id = %s;", (req.txid,))
+        db.execute("DELETE FROM fulfillments WHERE transactionid = %s; "
+                   "DELETE FROM transactions WHERE id = %s;",
+                   (req.txid, req.txid))
         cherrypy.response.status = 204
 
 
         if "body" not in req.json:
             raise cherrypy.HTTPError("400 No Body",
                 "Transactions MUST include a 'body' member.")
-    
+
+        credit, debit = popint(vals['credit']), popint(vals['debit'])
+
         # Accept the new transaction definition.
         vals = req.json["body"]
-        newrow = db.execute("INSERT INTO transactions"
+        newrow = db.execute(
+            "INSERT INTO transactions"
             " (amount, credit_account, debit_account, postdate, description) "
             "VALUES (%s, %s, %s, %s, %s) RETURNING id",
-            (vals["amount"],
-             popint(vals["credit"]),
-             popint(vals["debit"]),
-             vals["postdate"], vals['description'],
-             )).fetchone()
+            (vals["amount"], credit, debit,
+             vals["postdate"], vals['description'])).fetchone()
+
+        flows.fulfill(newrow)
+
         cherrypy.response.status = 201
         cherrypy.response.headers['Location'] = cherrypy.url("%s" % newrow.id)
 
                     'end': row['range_end'],
                     'period': row['period'],
                     'unit': row['unit'],
-                    'day': row['day'],
+                    'days': row['days'],
                     'description': row['description'],
                     },
                 }
 
         # Accept the new flow definition.
         vals = req.json["body"]
-        db.execute("UPDATE flows SET amount = %s, "
-                   "credit_account = %s, debit_account = %s, "
-                   "range_start = %s, range_end = %s, "
-                   "period = %s, unit = %s, day = %s, "
-                   "description = %s "
-                   "WHERE id = %s RETURNING *;",
-                   (vals['amount'],
-                    popint(vals['credit']), popint(vals['debit']),
-                    vals['start'], vals['end'],
-                    vals['period'], vals['unit'], vals['day'],
-                    vals['description'],
-                    req.flowid))
+        credit, debit = popint(vals['credit']), popint(vals['debit'])
+        flowrow = db.execute(
+            "UPDATE flows SET amount = %s, "
+            "credit_account = %s, debit_account = %s, "
+            "range_start = %s, range_end = %s, "
+            "period = %s, unit = %s, days = %s, "
+            "description = %s "
+            "WHERE id = %s RETURNING *;",
+            (vals['amount'], credit, debit, vals['start'], vals['end'],
+            vals['period'], vals['unit'], vals['days'], vals['description'],
+            req.flowid)).fetchone()
 
+        # Remove all existing obligations
+        # (and their fulfillments) for this flow
         db.execute(
-            "DELETE FROM obligations WHERE flowid = %s;", (req.flowid,))
-        if vals['unit'] == 'years':
-            dategroupformat = 'YYYY'
-        elif vals['unit'] == 'days':
-            dategroupformat = 'YYYY-MM-DD'
-        else:
-            dategroupformat = 'YYYY-MM'
-        for tx in flows.obligations(row):
-            # TODO: this is slow. Can we change it to an INSERT INTO ... FROM"?
-            db.execute(
-                "INSERT INTO obligations "
-                "(flowid, postdate, credit_account, debit_account,"
-                " description, amount, dategroupformat) "
-                "VALUES (%s, %s, %s, %s, %s, %s, %s);",
-                (req.flowid, tx['postdate'], tx['credit'], tx['debit'],
-                 tx['description'], tx['amount'], dategroupformat))
+            "DELETE FROM fulfillments "
+            "WHERE obligationid"
+            " IN (SELECT id FROM obligations WHERE flowid = %s); "
+
+            "DELETE FROM obligations WHERE flowid = %s;",
+            (req.flowid, req.flowid))
+
+        flows.obligate(flowrow)
 
         cherrypy.response.status = 204
 
     def DELETE(self):
         """Destroy the definition of this flow."""
         req = cherrypy.serving.request
+
+        # Remove all existing obligations
+        # (and their fulfillments) for this flow
         db.execute(
-            "DELETE FROM obligations WHERE flowid = %s;", (req.flowid,))
+            "DELETE FROM fulfillments "
+            "WHERE obligationid"
+            " IN (SELECT id FROM obligations WHERE flowid = %s); "
+
+            "DELETE FROM obligations WHERE flowid = %s;",
+            (req.flowid, req.flowid))
+
         db.execute("DELETE FROM flows WHERE id = %s;", (req.flowid,))
         cherrypy.response.status = 204
 
 to self to define a new flow.
 
 Set the unit field to 'months' to declare that the flow repeats every month.
-For example, if a flow repeats on the 3rd of each month, set day to 3 and
-unit to 'months'. If a flow repeats on February 14th of each year, set day
+For example, if a flow repeats on the 3rd of each month, set days to 3 and
+unit to 'months'. If a flow repeats on February 14th of each year, set days
 to 14, and unit to 'months'.
 
 The start and end dates are inclusive.
                                  "start": "2011-11-01",
                                  "end": "2012-03-01",
                                  "period": "1",
-                                 "unit": "months", "day": 15,
+                                 "unit": "months", "days": [15],
                                  "description": "Rent",
                                  }},
             }
              "end": row.range_end.isoformat(),
              "period": row.period,
              "unit": row.unit,
-             "day": row.day,
+             "days": row.days,
              "amount": row.amount,
              "credit": cherrypy.url("/accounts/%s" % row.credit_account),
              "debit": cherrypy.url("/accounts/%s" % row.debit_account),
         vals = req.json["body"]
         newrow = db.execute("INSERT INTO flows"
                    " (amount, credit_account, debit_account, "
-                   "range_start, range_end, period, unit, day, description) "
+                   "range_start, range_end, period, unit, days, description) "
                    "VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s) RETURNING *",
                    (vals["amount"],
                     popint(vals["credit"]), popint(vals["debit"]),
                     vals["start"], vals["end"],
-                    vals["period"], vals['unit'], vals["day"],
+                    vals["period"], vals['unit'], vals["days"],
                     vals["description"],
                     )).fetchone()
 
-        if vals['unit'] == 'years':
-            dategroupformat = 'YYYY'
-        elif vals['unit'] == 'days':
-            dategroupformat = 'YYYY-MM-DD'
-        else:
-            dategroupformat = 'YYYY-MM'
-        for tx in flows.obligations(newrow):
-            # TODO: this is slow. Can we change it to an INSERT INTO ... FROM"?
-            db.execute(
-                "INSERT INTO obligations "
-                "(flowid, postdate, credit_account, debit_account,"
-                " description, amount, dategroupformat) "
-                "VALUES (%s, %s, %s, %s, %s, %s, %s);",
-                (newrow.id, tx['postdate'], tx['credit'], tx['debit'],
-                 tx['description'], tx['amount'], dategroupformat))
+        flows.obligate(newrow)
 
         cherrypy.response.status = 201
         cherrypy.response.headers['Location'] = cherrypy.url("%s" % newrow.id)

flowrate/csvutil.py

 import sqlalchemy
 
 import flowrate
+from flowrate import flows
 
 
 class Peekable(object):
         if exists is None:
             newrow = flowrate.db.execute("INSERT INTO transactions "
                 "(amount, credit_account, debit_account, postdate,"
-                " description) VALUES (%s, %s, %s, %s, %s) RETURNING id",
-                (amount, credit, debit, postdate, description))
-            return newrow
+                " description) VALUES (%s, %s, %s, %s, %s) RETURNING *",
+                (amount, credit, debit, postdate, description)).fetchone()
+            flows.fulfill(newrow)
+            return newrow.id
         else:
             return None
 

flowrate/flowrate.sql

     range_end date NOT NULL,
     period integer,
     unit text,
-    day integer,
+    days integer[],
 );
 
 
 
 
 --
+-- Name: fulfillments; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
+--
+
+CREATE TABLE fulfillments (
+    transactionid integer NOT NULL,
+    obligationid integer NOT NULL,
+    amount numeric(10, 2) NOT NULL
+);
+
+
+ALTER TABLE public.fulfillments OWNER TO postgres;
+
+--
 -- Name: obligations; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
 --
 
     credit_account integer NOT NULL,
     debit_account integer NOT NULL,
     description text,
-    amount numeric(10, 2) NOT NULL
+    amount numeric(10, 2) NOT NULL,
+    obligationid integer
 );
 
 
 
 
 --
+-- Name: fki_fulfillments_transactionid; Type: INDEX; Schema: public; Owner: postgres; Tablespace: 
+--
+
+CREATE INDEX fki_fulfillments_transactionid ON fulfillments USING btree (transactionid);
+
+
+--
+-- Name: fki_fulfillments_obligationid; Type: INDEX; Schema: public; Owner: postgres; Tablespace: 
+--
+
+CREATE INDEX fki_fulfillments_obligationid ON fulfillments USING btree (obligationid);
+
+
+--
+-- Name: fulfillments_transactionid_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
+--
+
+ALTER TABLE ONLY fulfillments
+    ADD CONSTRAINT fulfillments_transactionid_fkey FOREIGN KEY (transactionid) REFERENCES transactions(id);
+
+
+--
+-- Name: fulfillments_obligationid_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
+--
+
+ALTER TABLE ONLY fulfillments
+    ADD CONSTRAINT fulfillments_obligationid_fkey FOREIGN KEY (obligationid) REFERENCES obligations(id);
+
+
+--
 -- Name: obligations_credit_account_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
 --
 
     ADD CONSTRAINT transactions_debit_account_fkey FOREIGN KEY (debit_account) REFERENCES accounts(id);
 
 
+--
+-- Name: transactions_obligationid_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
+--
+
+ALTER TABLE ONLY transactions
+    ADD CONSTRAINT transactions_obligationid_fkey FOREIGN KEY (obligationid) REFERENCES obligations(id);
+
+
 CREATE OR REPLACE FUNCTION isSubAccount(child integer, parent integer) RETURNS boolean AS $$
   -- Return true if the given child is a subaccount of the given parent.
   SELECT $1 >= $2 AND $1 < $2 + (CASE WHEN $2 % 1000 = 0 THEN 1000
 ALTER TABLE ledger OWNER TO postgres;
 
 
--- View: fulfillments
--- TODO: this doesn't take into account the period
-CREATE OR REPLACE VIEW fulfillments AS
-    SELECT o.*,
-        (SELECT COALESCE(SUM(t.amount), 0) FROM transactions t
-         WHERE isSubAccount(t.credit_account, o.credit_account)
-         AND isSubAccount(t.debit_account, o.debit_account)
-         AND (to_char(t.postdate, o.dategroupformat) = to_char(o.postdate, o.dategroupformat))
-         ) AS fulfilled
-    FROM obligations o;
-
-ALTER TABLE fulfillments OWNER TO postgres;
-
-
 -- View: flowledger
 
 CREATE OR REPLACE VIEW flowledger AS
+    WITH o AS (
+        SELECT o1.*,
+        amount - (SELECT COALESCE(SUM(amount), 0) FROM fulfillments f
+         WHERE f.obligationID = o1.id) AS remaining
+        FROM obligations o1
+        WHERE o1.postdate >= (CURRENT_DATE - '1 month'::interval)
+        )
     SELECT postdate,
            credit_account AS account,
            (CASE WHEN a1.type IN ('asset', 'expense')
-                 THEN 0 - (amount - fulfilled) ELSE (amount - fulfilled)
+                 THEN 0 - remaining ELSE remaining
             END) AS amount
-    FROM fulfillments o1 LEFT JOIN accounts a1 ON o1.credit_account = a1.id
-    WHERE o1.postdate >= (CURRENT_DATE - '1 month'::interval)
+    FROM o LEFT JOIN accounts a1 ON o.credit_account = a1.id
+    WHERE remaining > 0
     UNION ALL
     SELECT postdate,
            debit_account AS account,
            (CASE WHEN a2.type IN ('asset', 'expense')
-                 THEN (amount - fulfilled) ELSE 0 - (amount - fulfilled)
+                 THEN remaining ELSE 0 - remaining
             END) AS amount
-    FROM fulfillments o2 LEFT JOIN accounts a2 ON o2.debit_account = a2.id
-    WHERE o2.postdate >= (CURRENT_DATE - '1 month'::interval)
+    FROM o LEFT JOIN accounts a2 ON o.debit_account = a2.id
+    WHERE remaining > 0
     ORDER BY 1, 2;
 
 ALTER TABLE flowledger OWNER TO postgres;

flowrate/flows.html

     set_text(d, flow.unit);
     r.appendChild(d);
     
-    // day
+    // days
     d = document.createElement("td");
     d.className = 'flowday';
-    set_text(d, flow.day);
+    set_text(d, flow.days.join(","));
     r.appendChild(d);
     
     // credit
         $('edit_end').value = flow.end;
         $('edit_period').value = flow.period;
         $('edit_unit').value = flow.unit;
-        $('edit_day').value = flow.day;
+        $('edit_days').value = flow.days.join(",");
         $('edit_credit').value = flow.credit;
         $('edit_debit').value = flow.debit;
         $('edit_description').value = flow.description;
     flow.end = $('edit_end').value;
     flow.period = $('edit_period').value;
     flow.unit = $('edit_unit').value;
-    flow.day = $('edit_day').value;
+    flow.days = [];
+    var d = $('edit_days').value.split(",");
+    for (var i = 0; i < d.length; i++) {
+        flow.days.push(parseInt(d[i]));
+    }
     flow.credit = $('edit_credit').value;
     flow.debit = $('edit_debit').value;
     flow.description = $('edit_description').value;
         <option selected="selected">months</option>
         <option>weeks</option>
         </select></td>
-    <td><input id='edit_day' type='text' size='2' /></td>
+    <td><input id='edit_days' type='text' size='4' /></td>
     <td><select id='edit_credit'></select></td>
     <td><select id='edit_debit'></select></td>
     <td><input id='edit_description' type='text' value="New flow" /></td>
     <th>End&nbsp;Date</th>
     <th>Period</th>
     <th>Unit</th>
-    <th>Day</th>
+    <th>Days</th>
     <th>Credit</th>
     <th>Debit</th>
     <th>Description</th>

flowrate/flows.py

 
     for d in range(0, (flow.range_end - flow.range_start).days + 1):
         postdate = flow.range_start + datetime.timedelta(days=d)
-        if day(postdate) != flow.day:
+        if day(postdate) not in flow.days:
             continue
 
         p = unit(postdate)
         # This will be an integer number of units between post and start
         diff = p - unit(flow.range_start)
-        # But range_start might be after our flow.day
-        if day(flow.range_start) > flow.day:
-            diff -= 1
+        # But range_start might be after one or more of our flow.day(s)
+        for fd in sorted(flow.days):
+            if day(flow.range_start) > fd:
+                diff -= 1
 
         if diff % flow.period == 0:
             # Yield one transaction for the year/month/week on the day.
                 'amount': flow.amount,
                 }
 
+def obligate(flowrow):
+    """Insert obligation rows for the given flow; fulfill as possible."""
+    if flowrow.unit == 'years':
+        dategroupformat = 'YYYY'
+    elif flowrow.unit == 'days':
+        dategroupformat = 'YYYY-MM-DD'
+    else:
+        dategroupformat = 'YYYY-MM'
+
+    obs = {}
+    for ob in obligations(flowrow):
+        # TODO: this is slow. Can we change it to an "INSERT INTO ... FROM"?
+        row = flowrate.db.execute(
+            "INSERT INTO obligations "
+            "(flowid, postdate, credit_account, debit_account,"
+            " description, amount, dategroupformat) "
+            "VALUES (%s, %s, %s, %s, %s, %s, %s) RETURNING id;",
+            (flowrow.id, ob['postdate'], ob['credit'], ob['debit'],
+             ob['description'], ob['amount'], dategroupformat)).fetchone()
+        obs[row.id] = ob
+
+    # Now, fulfill the new obligations
+    for obid, ob in obs.iteritems():
+        obrem = ob['amount']
+        for tx in flowrate.db.execute(
+            "SELECT t.*, "
+            "(SELECT COALESCE(SUM(f.amount), 0) FROM fulfillments f"
+            " WHERE f.transactionid = t.id) AS fulfilled "
+            "FROM transactions t "
+            "WHERE isSubAccount(t.credit_account, %s) "
+            "AND isSubAccount(t.debit_account, %s) "
+            "AND (to_char(t.postdate, %s) = to_char(%s, %s)) "
+            "ORDER BY t.postdate ASC;",
+            (ob['credit'], ob['debit'],
+             dategroupformat, ob['postdate'], dategroupformat)).fetchall(
+            ):
+            txrem = tx.amount - tx.fulfilled
+            if obrem > 0 and txrem > 0:
+                f_amt = min(obrem, txrem)
+                flowrate.db.execute(
+                    "INSERT INTO fulfillments "
+                    "(transactionid, obligationid, amount) "
+                    "VALUES (%s, %s, %s);", (tx.id, obid, f_amt))
+                obrem -= f_amt
+                if obrem <= 0:
+                    break
+
 def isSubAccount(child, parents):
     for p in parents:
         for scale in (1000, 100, 10, 1):
                 return True
     return False
 
+def fulfill(txrow):
+    """Use the given transaction to fulfill an obligation, if possible."""
+    flowrate.db.execute(
+        "DELETE FROM fulfillments WHERE transactionid = %s", (txrow.id,))
+
+    if txrow.amount <= 0:
+        return
+
+    obs = [(row.id, row.amount, row.fulfilled)
+           for row in flowrate.db.execute(
+                "SELECT o.*, "
+                "(SELECT COALESCE(SUM(f.amount), 0) FROM fulfillments f"
+                " WHERE f.obligationid = o.id) AS fulfilled "
+                "FROM obligations o "
+                "WHERE isSubAccount(%s, o.credit_account) "
+                "AND isSubAccount(%s, o.debit_account) "
+                "AND (to_char(%s, o.dategroupformat) = "
+                     "to_char(o.postdate, o.dategroupformat)) "
+                "ORDER BY o.postdate ASC",
+                (txrow.credit_account, txrow.debit_account,
+                 txrow.postdate)).fetchall()]
+
+    txrem = txrow.amount
+    for obid, obamount, obfulfilled in obs:
+        obrem = obamount - obfulfilled
+        f_amt = min(obrem, txrem)
+        if f_amt > 0:
+            flowrate.db.execute(
+                "INSERT INTO fulfillments "
+                "(transactionid, obligationid, amount) VALUES (%s, %s, %s);",
+                (txrow.id, obid, f_amt))
+            txrem -= f_amt
+            if txrem <= 0:
+                break
+
+
 def transactions(accounts=None, credits=None, debits=None,
                  years=None, months=None, days=None,
                  description=None):
     """Yield flow transactions matching the given criteria (by postdate desc)."""
     whereclause, args = [], {}
     if accounts:
-        whereclause.append("(ARRAY[credit_account] <@ %(accounts)s"
-                           " OR ARRAY[debit_account] <@ %(accounts)s)")
+        whereclause.append("(ARRAY[o.credit_account] <@ %(accounts)s"
+                           " OR ARRAY[o.debit_account] <@ %(accounts)s)")
         all_accts = [row.id for row in flowrate.db.execute(
                      "SELECT id FROM accounts;").fetchall()]
         args['accounts'] = [a for a in all_accts if isSubAccount(a, accounts)]
     if credits:
-        whereclause.append("ARRAY[credit_account] <@ %(credits)s")
+        whereclause.append("ARRAY[o.credit_account] <@ %(credits)s")
         args['credits'] = credits
     if debits:
-        whereclause.append("ARRAY[debit_account] <@ %(debits)s")
+        whereclause.append("ARRAY[o.debit_account] <@ %(debits)s")
         args['debits'] = debits
     if description:
-        whereclause.append("description ILIKE %(desc)s")
+        whereclause.append("o.description ILIKE %(desc)s")
         args['desc'] = '%' + description + '%';
     if years:
-        whereclause.append("ARRAY[EXTRACT(year FROM postdate)::integer] <@ %(years)s")
+        whereclause.append("ARRAY[EXTRACT(year FROM o.postdate)::integer] <@ %(years)s")
         args['years'] = years
     if months:
-        whereclause.append("ARRAY[EXTRACT(month FROM postdate)::integer] <@ %(months)s")
+        whereclause.append("ARRAY[EXTRACT(month FROM o.postdate)::integer] <@ %(months)s")
         args['months'] = months
     if days:
-        whereclause.append("ARRAY[EXTRACT(day FROM postdate)::integer] <@ %(days)s")
+        whereclause.append("ARRAY[EXTRACT(day FROM o.postdate)::integer] <@ %(days)s")
         args['days'] = days
 
     if not whereclause:
         return
 
     for ob in flowrate.db.execute(
-        "SELECT * FROM fulfillments WHERE " + " AND ".join(whereclause) +
+        "SELECT o.*, "
+        "(SELECT COALESCE(SUM(f.amount), 0) FROM fulfillments f"
+        " WHERE f.obligationid = o.id) AS fulfilled "
+        "FROM obligations o "
+        "WHERE " + " AND ".join(whereclause) +
         # Don't let old unfulfilled obligations screw up balances
-        " AND postdate >= (CURRENT_DATE - '1 month'::interval) "
-        "ORDER BY postdate DESC;", args).fetchall(
+        " AND o.postdate >= (CURRENT_DATE - '1 month'::interval) "
+        "ORDER BY o.postdate DESC;", args).fetchall(
         ):
         amount = ob.amount - ob.fulfilled
         if amount > 0:
Tip: Filter by directory path e.g. /media app.js to search for public/media/app.js.
Tip: Use camelCasing e.g. ProjME to search for ProjectModifiedEvent.java.
Tip: Filter by extension type e.g. /repo .js to search for all .js files in the /repo directory.
Tip: Separate your search with spaces e.g. /ssh pom.xml to search for src/ssh/pom.xml.
Tip: Use ↑ and ↓ arrow keys to navigate and return to view the file.
Tip: You can also navigate files with Ctrl+j (next) and Ctrl+k (previous) and view the file with Ctrl+o.
Tip: You can also navigate files with Alt+j (next) and Alt+k (previous) and view the file with Alt+o.