purchasing/includes/db/invoice_db.inc

   1 <?php
   2
   3 include_once($path_to_root . "/purchasing/includes/db/invoice_items_db.inc");
   4
   5 //--------------------------------------------------------------------------------------------------
   6
   7 function read_supplier_details_to_trans(&$supp_trans, $supplier_id)
   8 {
   9         $sql = "SELECT ".TB_PREF."suppliers.supp_name, ".TB_PREF."payment_terms.terms, ".TB_PREF."payment_terms.days_before_due,
  10                 ".TB_PREF."payment_terms.day_in_following_month,
  11                 ".TB_PREF."suppliers.tax_group_id, ".TB_PREF."tax_groups.name As tax_group_name
  12                 From ".TB_PREF."suppliers, ".TB_PREF."payment_terms, ".TB_PREF."tax_groups
  13                 WHERE ".TB_PREF."suppliers.tax_group_id = ".TB_PREF."tax_groups.id
  14                 AND ".TB_PREF."suppliers.payment_terms=".TB_PREF."payment_terms.terms_indicator
  15                 AND ".TB_PREF."suppliers.supplier_id = '" . $supplier_id . "'";
  16
  17         $result = db_query($sql, "The supplier record selected: " . $supplier_id . " cannot be retrieved");
  18
  19         $myrow = db_fetch($result);
  20
  21     $supp_trans->supplier_id = $supplier_id;
  22     $supp_trans->supplier_name = $myrow['supp_name'];
  23         $supp_trans->terms_description = $myrow['terms'];
  24
  25         if ($myrow['days_before_due'] == 0)
  26         {
  27                 $supp_trans->terms = "1" . $myrow['day_in_following_month'];
  28         }
  29         else
  30         {
  31                 $supp_trans->terms = "0" . $myrow['days_before_due'];
  32         }
  33         $supp_trans->tax_description = $myrow['tax_group_name'];
  34         $supp_trans->tax_group_id = $myrow['tax_group_id'];
  35
  36     if ($supp_trans->tran_date == "")
  37     {
  38                 $supp_trans->tran_date = Today();
  39                 if (!is_date_in_fiscalyear($supp_trans->tran_date))
  40                         $supp_trans->tran_date = end_fiscalyear();
  41         }
  42     //if ($supp_trans->due_date=="") {
  43     //  get_duedate_from_terms($supp_trans);
  44     //}
  45     get_duedate_from_terms($supp_trans);
  46 }
  47
  48 //--------------------------------------------------------------------------------------------------
  49
  50 function update_supp_received_items_for_invoice($id, $po_detail_item, $qty_invoiced, $chg_price=null)
  51 {
  52         if ($chg_price != null)
  53         {
  54                 $sql = "SELECT act_price FROM ".TB_PREF."purch_order_details WHERE
  55                         po_detail_item = $po_detail_item";
  56                 $result = db_query($sql, "The old actual price of the purchase order line could not be retrieved");
  57                 $row = db_fetch_row($result);
  58                 $ret = $row[0];
  59                 $sql = "SELECT delivery_date FROM ".TB_PREF."grn_batch,".TB_PREF."grn_items WHERE
  60                         ".TB_PREF."grn_batch.id = ".TB_PREF."grn_items.grn_batch_id AND ".TB_PREF."grn_items.id=$id";
  61                 $result = db_query($sql, "The old delivery date from the received record cout not be retrieved");
  62                 $row = db_fetch_row($result);
  63                 $date = $row[0];
  64         }
  65         else
  66         {
  67                 $ret = 0;
  68                 $date = "";
  69         }
  70     $sql = "UPDATE ".TB_PREF."purch_order_details
  71                 SET qty_invoiced = qty_invoiced + $qty_invoiced ";
  72
  73         if ($chg_price != null)
  74                 $sql .= " , act_price = $chg_price ";
  75
  76         $sql .= " WHERE po_detail_item = $po_detail_item";
  77     db_query($sql, "The quantity invoiced of the purchase order line could not be updated");
  78
  79     $sql = "UPDATE ".TB_PREF."grn_items
  80         SET quantity_inv = quantity_inv + $qty_invoiced
  81         WHERE id = $id";
  82         db_query($sql, "The quantity invoiced off the items received record could not be updated");
  83         return array($ret, $date);
  84 }
  85
  86 function get_deliveries_between($stock_id, $from, $to)
  87 {
  88         $from = date2sql($from);
  89         $to = date2sql($to);
  90         $sql = "SELECT SUM(quantity), AVG(standard_cost) FROM ".TB_PREF."debtor_trans, ".TB_PREF."debtor_trans_details
  91                 WHERE ".TB_PREF."debtor_trans.trans_no = ".TB_PREF."debtor_trans_details.debtor_trans_no AND
  92                         ".TB_PREF."debtor_trans.tran_date >= '$from' AND
  93                         ".TB_PREF."debtor_trans.tran_date <= '$to' AND
  94                         ".TB_PREF."debtor_trans_details.stock_id = '$stock_id' AND
  95                         ".TB_PREF."debtor_trans_details.debtor_trans_type = 13";
  96         $result = db_query($sql, "The deliveries could not be updated");
  97         return db_fetch_row($result);
  98 }
  99 //----------------------------------------------------------------------------------------
 100
 101 function add_supp_invoice($supp_trans) // do not receive as ref because we change locally
 102 {
 103         //$company_currency = get_company_currency();
 104         /*Start an sql transaction */
 105         begin_transaction();
 106
 107         $tax_total = 0;
 108     $taxes = $supp_trans->get_taxes($supp_trans->tax_group_id);
 109
 110     foreach ($taxes as $taxitem)
 111     {
 112         $tax_total += $taxitem['Value'];
 113     }
 114
 115     $invoice_items_total = $supp_trans->get_total_charged($supp_trans->tax_group_id);
 116
 117         if ($supp_trans->is_invoice)
 118                 $trans_type = 20;
 119         else
 120         {
 121                 $trans_type = 21;
 122                 // let's negate everything because it's a credit note
 123                 $invoice_items_total = -$invoice_items_total;
 124                 $tax_total = -$tax_total;
 125                 $supp_trans->ov_discount = -$supp_trans->ov_discount; // this isn't used at all...
 126         }
 127
 128     $date_ = $supp_trans->tran_date;
 129
 130     /*First insert the invoice into the supp_trans table*/
 131         $invoice_id = add_supp_trans($trans_type, $supp_trans->supplier_id, $date_, $supp_trans->due_date,
 132                 $supp_trans->reference, $supp_trans->supp_reference,
 133                 $invoice_items_total, $tax_total, $supp_trans->ov_discount);
 134
 135     /* Now the control account */
 136     $supplier_accounts = get_supplier_accounts($supp_trans->supplier_id);
 137     add_gl_trans_supplier($trans_type, $invoice_id, $date_, $supplier_accounts["payable_account"], 0, 0,
 138                 -($invoice_items_total +  $tax_total + $supp_trans->ov_discount),
 139                 $supp_trans->supplier_id,
 140                 "The general ledger transaction for the control total could not be added");
 141
 142     /*Loop through the GL Entries and create a debit posting for each of the accounts entered */
 143
 144     /*the postings here are a little tricky, the logic goes like this:
 145     if its a general ledger amount it goes straight to the account specified
 146
 147     if its a GRN amount invoiced then :
 148
 149     The cost as originally credited to GRN suspense on arrival of items is debited to GRN suspense. Any difference
 150     between the std cost and the currency cost charged as converted at the ex rate of of the invoice is written off
 151     to the purchase price variance account applicable to the item being invoiced.
 152     */
 153
 154     foreach ($supp_trans->gl_codes as $entered_gl_code)
 155     {
 156
 157             /*GL Items are straight forward - just do the debit postings to the GL accounts specified -
 158             the credit is to creditors control act  done later for the total invoice value + tax*/
 159
 160                 if (!$supp_trans->is_invoice)
 161                         $entered_gl_code->amount = -$entered_gl_code->amount;
 162
 163                 $memo_ = $entered_gl_code->memo_;
 164                 add_gl_trans_supplier($trans_type, $invoice_id, $date_, $entered_gl_code->gl_code,
 165                         $entered_gl_code->gl_dim, $entered_gl_code->gl_dim2, $entered_gl_code->amount, $supp_trans->supplier_id);
 166
 167                 add_supp_invoice_gl_item($trans_type, $invoice_id, $entered_gl_code->gl_code,
 168                         $entered_gl_code->amount, $memo_);
 169     }
 170
 171     foreach ($supp_trans->grn_items as $entered_grn)
 172     {
 173
 174         if (!$supp_trans->is_invoice)
 175         {
 176                         $entered_grn->this_quantity_inv = -$entered_grn->this_quantity_inv;
 177         }
 178
 179                 $line_taxfree = $entered_grn->taxfree_charge_price($supp_trans->tax_group_id);
 180                 $line_tax = $entered_grn->full_charge_price($supp_trans->tax_group_id) - $line_taxfree;
 181
 182         $old = update_supp_received_items_for_invoice($entered_grn->id, $entered_grn->po_detail_item,
 183                 $entered_grn->this_quantity_inv, $entered_grn->chg_price);
 184                 $stock_gl_code = get_stock_gl_code($entered_grn->item_code);
 185                 $stock_entry_account = $stock_gl_code["inventory_account"];
 186
 187                 add_gl_trans_supplier($trans_type, $invoice_id, $date_, $stock_entry_account,
 188                         $stock_gl_code['dimension_id'], $stock_gl_code['dimension2_id'],
 189                         $entered_grn->this_quantity_inv * $line_taxfree, $supp_trans->supplier_id);
 190         // -------------- if price changed since po received. 16 Aug 2008 Joe Hunt
 191                 $old_price = $old[0];
 192         if ($old_price != $entered_grn->chg_price) // price-change, so update
 193         {
 194                 $diff = $entered_grn->chg_price - $old_price;
 195                         $old_date = sql2date($old[1]);
 196                         // only update the diff (last parameter, adj_only is set to true).
 197                 $mat_cost = update_average_material_cost($supp_trans->supplier_id, $entered_grn->item_code,
 198                         $diff, $entered_grn->this_quantity_inv, $old_date, true);
 199                 // function just above this
 200                 $deliveries = get_deliveries_between($entered_grn->item_code, $old_date, $date_);
 201                 if ($deliveries[0] != 0) // have deliveries been done during the period?
 202                 {
 203
 204                         $amt = ($mat_cost - $deliveries[1]) * $deliveries[0]; // $amt in home currency
 205
 206                                 add_gl_trans($trans_type, $invoice_id, $date_,  $stock_gl_code["cogs_account"],
 207                                         $stock_gl_code['dimension_id'], $stock_gl_code['dimension2_id'], _("Cost diff."),
 208                                 $amt, null, null, null,
 209                                 "The general ledger transaction could not be added for the price variance of the inventory item");
 210                                 add_gl_trans($trans_type, $invoice_id, $date_,  $stock_gl_code["inventory_account"],
 211                                         0, 0, _("Cost diff."), -$amt, null, null, null,
 212                                 "The general ledger transaction could not be added for the price variance of the inventory item");
 213                 }
 214         }
 215
 216         // ----------------------------------------------------------------------
 217
 218                 add_supp_invoice_item($trans_type, $invoice_id, $entered_grn->item_code,
 219                         $entered_grn->item_description, 0,      $line_taxfree, $line_tax,
 220                         $entered_grn->this_quantity_inv, $entered_grn->id, $entered_grn->po_detail_item, "");
 221     } /* end of GRN postings */
 222
 223     /* Now the TAX account */
 224     foreach ($taxes as $taxitem)
 225     {
 226         if ($taxitem['Value'] != 0)
 227         {
 228
 229                 if (!$supp_trans->is_invoice)
 230                         $taxitem['Value'] = -$taxitem['Value'];
 231                 // here we suppose that tax is never included in price (we are company customer).
 232                         add_supp_invoice_tax_item($trans_type, $invoice_id, $taxitem['tax_type_id'],
 233                                 $taxitem['rate'], 0, $taxitem['Value']);
 234
 235                 add_gl_trans_supplier($trans_type, $invoice_id, $date_,
 236                         $taxitem['purchasing_gl_code'], 0, 0, $taxitem['Value'],
 237                         $supp_trans->supplier_id,
 238                         "A general ledger transaction for the tax amount could not be added");
 239         }
 240     }
 241         add_comments($trans_type, $invoice_id, $date_, $supp_trans->Comments);
 242
 243         references::save_last($supp_trans->reference, $trans_type);
 244
 245     commit_transaction();
 246
 247     return $invoice_id;
 248 }
 249
 250 //----------------------------------------------------------------------------------------
 251
 252 // get all the invoices/credits for a given PO - quite long route to get there !
 253
 254 function get_po_invoices_credits($po_number)
 255 {
 256         $sql = "SELECT DISTINCT ".TB_PREF."supp_trans.trans_no, ".TB_PREF."supp_trans.type,
 257                 ov_amount+ov_discount+ov_gst AS Total,
 258                 ".TB_PREF."supp_trans.tran_date
 259                 FROM ".TB_PREF."supp_trans, ".TB_PREF."supp_invoice_items, ".TB_PREF."purch_order_details
 260                 WHERE ".TB_PREF."supp_invoice_items.supp_trans_no = ".TB_PREF."supp_trans.trans_no
 261                 AND ".TB_PREF."supp_invoice_items.po_detail_item_id = ".TB_PREF."purch_order_details.po_detail_item
 262                 AND ".TB_PREF."purch_order_details.order_no = $po_number";
 263
 264         return db_query($sql, "The invoices/credits for the po $po_number could not be retreived");
 265 }
 266
 267 //----------------------------------------------------------------------------------------
 268
 269 function read_supp_invoice($trans_no, $trans_type, &$supp_trans)
 270 {
 271         $sql = "SELECT ".TB_PREF."supp_trans.*, supp_name FROM ".TB_PREF."supp_trans,".TB_PREF."suppliers
 272                 WHERE trans_no = $trans_no AND type = $trans_type
 273                 AND ".TB_PREF."suppliers.supplier_id=".TB_PREF."supp_trans.supplier_id";
 274         $result = db_query($sql, "Cannot retreive a supplier transaction");
 275
 276         if (db_num_rows($result) == 1)
 277         {
 278                 $trans_row = db_fetch($result);
 279
 280                 $supp_trans->supplier_id = $trans_row["supplier_id"];
 281                 $supp_trans->supplier_name = $trans_row["supp_name"];
 282                 $supp_trans->tran_date = sql2date($trans_row["tran_date"]);
 283                 $supp_trans->due_date = sql2date($trans_row["due_date"]);
 284                 //$supp_trans->Comments = $trans_row["TransText"];
 285                 $supp_trans->Comments = "";
 286                 $supp_trans->reference = $trans_row["reference"];
 287                 $supp_trans->supp_reference = $trans_row["supp_reference"];
 288                 $supp_trans->ov_amount = $trans_row["ov_amount"];
 289                 $supp_trans->ov_discount = $trans_row["ov_discount"];
 290                 $supp_trans->ov_gst = $trans_row["ov_gst"];
 291
 292                 $id = $trans_row["trans_no"];
 293
 294                 $result = get_supp_invoice_items($trans_type, $id);
 295
 296                 if (db_num_rows($result) > 0)
 297                 {
 298
 299             while ($details_row = db_fetch($result))
 300             {
 301
 302                 if ($details_row["gl_code"] == 0)
 303                 {
 304                         $supp_trans->add_grn_to_trans($details_row["grn_item_id"], $details_row["po_detail_item_id"], $details_row["stock_id"],
 305                                         $details_row["description"], 0, 0, $details_row["quantity"], 0, $details_row["FullUnitPrice"],
 306                                         false, 0, 0);
 307                 }
 308                 else
 309                 {
 310                         $supp_trans->add_gl_codes_to_trans($details_row["gl_code"], get_gl_account_name($details_row["gl_code"]), 0, 0,
 311                                         $details_row["FullUnitPrice"], $details_row["memo_"]);
 312                 }
 313             }
 314         }
 315         else
 316         {
 317                         return display_db_error("Invalid supptrans details for supptrans number : $trans_no and type : $trans_type", $sql, true);
 318                 }
 319
 320         }
 321         else
 322         {
 323                 return display_db_error("Invalid supptrans number : $trans_no and type : $trans_type", $sql, true);
 324         }
 325 }
 326
 327 //----------------------------------------------------------------------------------------
 328
 329 function void_supp_invoice($type, $type_no)
 330 {
 331         begin_transaction();
 332
 333         void_bank_trans($type, $type_no, true);
 334
 335         void_gl_trans($type, $type_no, true);
 336
 337         void_supp_allocations($type, $type_no);
 338
 339         void_supp_trans($type, $type_no);
 340
 341         $result = get_supp_invoice_items($type, $type_no);
 342
 343         // now remove this invoice/credit from any GRNs/POs that it's related to
 344         if (db_num_rows($result) > 0)
 345         {
 346         while ($details_row = db_fetch($result))
 347         {
 348                 if (strlen($details_row["grn_item_id"]) > 0) // it can be empty for GL items
 349                 {
 350                                 update_supp_received_items_for_invoice($details_row["grn_item_id"],
 351                                         $details_row["po_detail_item_id"], -$details_row["quantity"]);
 352                 }
 353         }
 354         }
 355
 356         void_supp_invoice_items($type, $type_no);
 357         void_supp_invoice_tax_items($type, $type_no);
 358
 359         commit_transaction();
 360 }
 361
 362 //----------------------------------------------------------------------------------------
 363
 364
 365 ?>