Query help for invoicing on mass items

[SupaMonkey]

Hey guys,

First, my schema (just the parts pertinent to the question):

CREATE TABLE `credit_usages` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `client_id` int(10) unsigned DEFAULT NULL,
  `client_user_id` int(10) unsigned DEFAULT NULL,
  `credits_used` int(11) DEFAULT NULL,
  `description` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `usage_value` decimal(9,2) DEFAULT NULL,
  `used` datetime DEFAULT NULL,
  `invoiced` tinyint(1) DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `client_id` (`client_id`),
  KEY `client_user_id` (`client_user_id`)
);

CREATE TABLE `invoice_products` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `invoice_id` int(10) unsigned DEFAULT NULL,
  `invoice_product` varchar(255) DEFAULT NULL,
  `sell_price` decimal(10,2) DEFAULT NULL,
  `cost_price` decimal(10,2) DEFAULT NULL,
  `taxed` tinyint(1) DEFAULT NULL,
  PRIMARY KEY (`id`)
);

CREATE TABLE `invoices` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `client_id` int(10) unsigned DEFAULT NULL,
  `client` varchar(255) DEFAULT NULL,
  `invoiced` date DEFAULT NULL,
  `postal_address` text,
  `invoice` int(10) unsigned DEFAULT NULL,
  `emailed` tinyint(1) DEFAULT NULL,
  `tax_rate` decimal(10,2) DEFAULT NULL,
  `tax` decimal(10,2) DEFAULT NULL,
  `subtotal` decimal(10,2) DEFAULT NULL,
  `total` decimal(10,2) DEFAULT NULL,
  `notes` text,
  PRIMARY KEY (`id`)
);

 

So heres my scenario, I have system where every time someone views something on my site, they get charged a credit. The system inserts a row in the 'credit_usages' table with all the relevant details of that credit usage with its 'invoiced' field set to 0 by default. Once a month I plan to run a cron that takes all the uninvoiced "credit usages" and charge them to a client on a single invoice.

Problem comes in that clients are constantly adding items to the 'credit_usages' table so I dont know how to handle this without for example setting credit_usages invoiced='1' for something that hasnt been invoiced.

 

Let me elaborate:

1) my cron runs

2) select all credit_usages for all clients where invoiced = 0 'grouped by client'

3) inserts invoice and invoice_product with relevant details from (2)

4) updates credit_usages to invoiced=1 where invoiced=0

 

Problem:

Inbetween (2) and (4) clients 'consume' more credits and so (2) doesnt have the new credits, yet on (4) these will be updated to '1'...

 

Your thoughts?

My first impulse is to make 'invoiced' a tinyint instead of boolean and before (2) update invoiced to '2' for example where invoiced='0', then i only invoice what is set to '2' and only set invoiced=1 where invoiced =2.

Dont know if this is the best way forward - only had 2 hours of sleep last night - so some non-zombie thought patterns would be appreciated

[Muddy_Funster]

can't you use your datetime field as a marker? capture time sat start of cronjob, perform cronjob, update only fields where datetime field <= cronjob start time?