manufacturing/includes/db/work_orders_db.inc

   1 <?php
   2
   3 //--------------------------------------------------------------------------------------
   4
   5 function add_material_cost($stock_id, $qty, $date_)
   6 {
   7         $m_cost = 0;
   8     $result = get_bom($stock_id);
   9         while ($bom_item = db_fetch($result))
  10         {
  11                 $sql = "SELECT material_cost FROM ".TB_PREF."stock_master WHERE stock_id = '".$bom_item['component']."'";
  12                 $res = db_query($sql);
  13                 $myrow = db_fetch($res);
  14                 $m_cost += ($bom_item['quantity'] * $myrow['material_cost']);
  15         }
  16         $sql = "SELECT material_cost FROM ".TB_PREF."stock_master WHERE stock_id = '$stock_id'";
  17         $result = db_query($sql);
  18         $myrow = db_fetch($result);
  19         $material_cost =  $myrow['material_cost'];
  20         $qoh = get_qoh_on_date($stock_id, null, $date_);
  21         if ($qoh + $qty <= 0)
  22                 $material_cost = 0;
  23         else
  24                 $material_cost = ($qoh * $material_cost + $qty * $m_cost) /     ($qoh + $qty);
  25         $sql = "UPDATE ".TB_PREF."stock_master SET material_cost=$material_cost
  26                 WHERE stock_id='$stock_id'";
  27         db_query($sql,"The cost details for the inventory item could not be updated");
  28 }
  29
  30 function add_work_order($wo_ref, $loc_code, $units_reqd, $stock_id,
  31         $type, $date_, $required_by, $costs, $memo_)
  32 {
  33         if (!($type == wo_types::advanced()))
  34                 return add_work_order_quick($wo_ref, $loc_code, $units_reqd, $stock_id, $type, $date_, $costs, $memo_);
  35
  36         begin_transaction();
  37
  38         add_material_cost($stock_id, $units_reqd, $date_);
  39
  40         $date = date2sql($date_);
  41         $required = date2sql($required_by);
  42
  43         $sql = "INSERT INTO ".TB_PREF."workorders (wo_ref, loc_code, units_reqd, stock_id,
  44                 type, date_, required_by)
  45         VALUES ('$wo_ref', '$loc_code', $units_reqd, '$stock_id',
  46                 $type, '$date', '$required')";
  47         db_query($sql, "could not add work order");
  48
  49         $woid = db_insert_id();
  50
  51         add_comments(systypes::work_order(), $woid, $required_by, $memo_);
  52
  53         add_forms_for_sys_type(systypes::work_order(), $woid);
  54
  55         references::save_last($wo_ref, systypes::work_order());
  56
  57         commit_transaction();
  58
  59         return $woid;
  60 }
  61
  62 //--------------------------------------------------------------------------------------
  63
  64 function update_work_order($woid, $loc_code, $units_reqd, $stock_id,
  65                                         $date_, $required_by, $memo_)
  66 {
  67         begin_transaction();
  68
  69         add_material_cost($_POST['old_stk_id'], -$_POST['old_qty'], $date_);
  70         add_material_cost($stock_id, $units_reqd, $date_);
  71
  72         $date = date2sql($date_);
  73         $required = date2sql($required_by);
  74
  75         $sql = "UPDATE ".TB_PREF."workorders SET loc_code='$loc_code',
  76                 units_reqd=$units_reqd, stock_id='$stock_id',
  77                 required_by='$required',
  78                 date_='$date'
  79                 WHERE id = $woid";
  80
  81         db_query($sql, "could not update work order");
  82
  83         update_comments(systypes::work_order(), $woid, null, $memo_);
  84
  85         commit_transaction();
  86 }
  87
  88 function delete_work_order($woid)
  89 {
  90         begin_transaction();
  91
  92         add_material_cost($_POST['stock_id'], -$_POST['quantity'], $_POST['date_']);
  93
  94         // delete the work order requirements
  95         delete_wo_requirements($woid);
  96
  97         // delete the actual work order
  98         $sql = "DELETE FROM ".TB_PREF."workorders WHERE id=$woid";
  99         db_query($sql,"The work order could not be deleted");
 100
 101         delete_comments(systypes::work_order(), $woid);
 102
 103         delete_forms_for_systype(systypes::work_order(), $woid);
 104
 105         commit_transaction();
 106 }
 107
 108 //--------------------------------------------------------------------------------------
 109
 110 function get_work_order($woid, $allow_null=false)
 111 {
 112     $sql = "SELECT ".TB_PREF."workorders.*, ".TB_PREF."stock_master.description As StockItemName,
 113                 ".TB_PREF."locations.location_name
 114                 FROM ".TB_PREF."workorders, ".TB_PREF."stock_master, ".TB_PREF."locations
 115                 WHERE ".TB_PREF."stock_master.stock_id=".TB_PREF."workorders.stock_id
 116                 AND     ".TB_PREF."locations.loc_code=".TB_PREF."workorders.loc_code
 117                 AND ".TB_PREF."workorders.id=$woid
 118                 GROUP BY ".TB_PREF."workorders.id";
 119
 120         $result = db_query($sql, "The work order issues could not be retrieved");
 121
 122         if (!$allow_null && db_num_rows($result) == 0)
 123                 display_db_error("Could not find work order $workOrder", $sql);
 124
 125         return db_fetch($result);
 126 }
 127
 128 //--------------------------------------------------------------------------------------
 129
 130 function work_order_has_productions($woid)
 131 {
 132         $sql = "SELECT COUNT(*) FROM ".TB_PREF."wo_manufacture WHERE workorder_id=$woid";
 133         $result = db_query($sql, "query work order for productions");
 134
 135         $myrow = db_fetch_row($result);
 136         return ($myrow[0] > 0);
 137 }
 138
 139
 140 //--------------------------------------------------------------------------------------
 141
 142 function work_order_has_issues($woid)
 143 {
 144         $sql = "SELECT COUNT(*) FROM ".TB_PREF."wo_issues WHERE workorder_id=$woid";
 145         $result = db_query($sql, "query work order for issues");
 146
 147         $myrow = db_fetch_row($result);
 148         return ($myrow[0] > 0);
 149 }
 150
 151 //--------------------------------------------------------------------------------------
 152
 153 function work_order_has_payments($woid)
 154 {
 155     $result = get_bank_trans(null, null, payment_person_types::WorkOrder(), $woid);
 156
 157     return (db_num_rows($result) != 0);
 158 }
 159
 160 //--------------------------------------------------------------------------------------
 161
 162 function release_work_order($woid, $releaseDate, $memo_)
 163 {
 164         begin_transaction();
 165
 166         $myrow = get_work_order($woid);
 167         $stock_id = $myrow["stock_id"];
 168
 169         $date = date2sql($releaseDate);
 170
 171         $sql = "UPDATE ".TB_PREF."workorders SET released_date='$date',
 172                 released=1 WHERE id = $woid";
 173         db_query($sql, "could not release work order");
 174
 175         // create Work Order Requirements based on the bom
 176         create_wo_requirements($woid, $stock_id);
 177
 178         add_comments(systypes::work_order(), $woid, $releaseDate, $memo_);
 179
 180         commit_transaction();
 181 }
 182
 183 //--------------------------------------------------------------------------------------
 184
 185 function close_work_order($woid)
 186 {
 187         $sql = "UPDATE ".TB_PREF."workorders SET closed=1 WHERE id = $woid";
 188         db_query($sql, "could not close work order");
 189 }
 190
 191 //--------------------------------------------------------------------------------------
 192
 193 function work_order_is_closed($woid)
 194 {
 195         $sql = "SELECT closed FROM ".TB_PREF."workorders WHERE id = $woid";
 196         $result = db_query($sql, "could not query work order");
 197         $row = db_fetch_row($result);
 198         return ($row[0] > 0);
 199 }
 200
 201 //--------------------------------------------------------------------------------------
 202
 203 function work_order_update_finished_quantity($woid, $quantity, $force_close=0)
 204 {
 205         $sql = "UPDATE ".TB_PREF."workorders SET units_issued = units_issued + $quantity,
 206                 closed = ((units_issued >= units_reqd) OR $force_close)
 207                 WHERE id = $woid";
 208
 209         db_query($sql, "The work order issued quantity couldn't be updated");
 210 }
 211
 212 //--------------------------------------------------------------------------------------
 213
 214 function void_work_order($woid)
 215 {
 216         $work_order = get_work_order($woid);
 217
 218         if (!($work_order["type"] == wo_types::advanced()))
 219         {
 220                 begin_transaction();
 221
 222                 $sql = "UPDATE ".TB_PREF."workorders SET closed=1,units_issued=0 WHERE id = $woid";
 223                 db_query($sql, "The work order couldn't be voided");
 224
 225                 // void all related stock moves
 226                 void_stock_move(systypes::work_order(), $woid);
 227
 228                 // void any related gl trans
 229                 void_gl_trans(systypes::work_order(), $woid, true);
 230
 231                 // clear the requirements units received
 232                 void_wo_requirements($woid);
 233
 234                 commit_transaction();
 235         }
 236         else
 237         {
 238                 // void everything inside the work order : issues, productions, payments
 239         }
 240 }
 241
 242 //--------------------------------------------------------------------------------------
 243
 244 ?>