| 1 | <pre> |
| 2 | <h1>-- Remove all uncountable items</h1> |
| 3 | SELECT DISTINCT model_countable FROM model; |
| 4 | DELETE item FROM item LEFT JOIN barcode USING(barcode_id) LEFT JOIN model USING(model_id) WHERE model_countable=0; |
| 5 | UPDATE model SET model_countable=1; |
| 6 | <h1>-- populate barcode table</h1><?php |
| 7 | $data = $this->db->safe_query_fetch('SELECT model_id as id, model_barcode as bar FROM model;', false); |
| 8 | //print_r($data); |
| 9 | echo("UPDATE item SET barcode_id=model_id;\n"); |
| 10 | echo("INSERT INTO barcode (barcode_id,model_id,barcode_name) VALUES\n"); |
| 11 | foreach($data as $line) echo("('".$line['id']."','".$line['id']."',".$this->db->quote($line['bar'])."),\n"); |
| 12 | ?> |
| 13 | <h1>-- populate columns item_date_bought and item_date_sold</h1><?php |
| 14 | $data = $this->db->safe_query_fetch('SELECT item_id,MIN(item_valid_from) AS min,MAX(item_valid_from) AS max FROM item GROUP BY item_id;', false); |
| 15 | //print_r($data); |
| 16 | echo("START TRANSACTION;\n"); |
| 17 | foreach($data as $line) echo("UPDATE item SET item_date_bought='".$line['min']."',item_date_sold='".$line['max']."' WHERE item_id=".$line['item_id'].";\n"); |
| 18 | echo("COMMIT;\n"); |
| 19 | ?> |
| 20 | </pre> |