when aggregating, group by day so only one day's worth of data goes into each agg...
smbaker [Thu, 13 Sep 2012 02:35:24 +0000 (19:35 -0700)]
apps/gacks/gacksinvoice.py

index e6e156a..7098715 100644 (file)
@@ -427,22 +427,24 @@ class GacksInvoiceManager(GacksDB):
         self.execute("BEGIN;")
         try:
             # get the totals, grouped by object_id
-            recs = self.execute_dict("SELECT SUM(amount) as total,SUM(core_hours) as total_hours,object_id FROM `" + self.tablename + "` WHERE account_id=%s AND kind_id=%s AND state=%s GROUP BY object_id;" %
+            recs = self.execute_dict("SELECT SUM(amount) as total,SUM(core_hours) as total_hours,object_id,MAX(UNIX_TIMESTAMP(date)) as max_date,DATE(date) as agg_date FROM `" + self.tablename + "` WHERE account_id=%s AND kind_id=%s AND state=%s GROUP BY object_id, DATE(date);" %
                                        (str(account_id), str(from_kind), str(from_state)))
 
             for rec in recs:
                 total = float(rec["total"])
                 total_hours = float(Default(rec["total_hours"],0.0))
                 object_id = rec["object_id"]
+                max_date = rec["max_date"]
+                agg_date = rec["agg_date"]
                 if total>0:
-                    print "account", account_id, "object", object_id, "total", total
+                    print "account", account_id, "object", object_id, "total", total, "max_date", max_date, "agg_date", agg_date
                     # add a new aggregate charge
-                    self.add_charge(account_id, rec["object_id"], agg_kind, tNow, total, core_hours=total_hours)
+                    self.add_charge(account_id, rec["object_id"], agg_kind, max_date, total, core_hours=total_hours)
                     parent_id = self.execute_dict("SELECT LAST_INSERT_ID();")[0]["LAST_INSERT_ID()"]
 
                     # change all the old charges to to_kind/to_state and set the parent_id
-                    self.execute_dict("UPDATE `" + self.tablename + "` SET state=%s,parent_id=%s WHERE account_id=%s AND kind_id=%s AND state=%s AND object_id=%s;" %
-                               (str(orphan_state), str(parent_id), str(account_id), str(from_kind), str(from_state), str(object_id)))
+                    self.execute_dict("UPDATE `" + self.tablename + "` SET state=%s,parent_id=%s WHERE account_id=%s AND kind_id=%s AND state=%s AND object_id=%s AND DATE(date)='%s';" %
+                               (str(orphan_state), str(parent_id), str(account_id), str(from_kind), str(from_state), str(object_id), str(agg_date)))
 
             self.execute("COMMIT;")
         except: