assistants/migrations.inc.php

   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>