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, unit_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
  60                 $unit_price = $row[1]; //Added by Rasmus
  61
  62                 $sql = "SELECT delivery_date FROM ".TB_PREF."grn_batch,".TB_PREF."grn_items WHERE
  63                         ".TB_PREF."grn_batch.id = ".TB_PREF."grn_items.grn_batch_id AND ".TB_PREF."grn_items.id=$id";
  64                 $result = db_query($sql, "The old delivery date from the received record cout not be retrieved");
  65                 $row = db_fetch_row($result);
  66                 $date = $row[0];
  67         }
  68         else
  69         {
  70                 $ret = 0;
  71                 $date = "";
  72                 $unit_price = 0; // Added by Rasmus
  73         }
  74     $sql = "UPDATE ".TB_PREF."purch_order_details
  75                 SET qty_invoiced = qty_invoiced + $qty_invoiced ";
  76
  77         if ($chg_price != null)
  78                 $sql .= " , act_price = $chg_price ";
  79
  80         $sql .= " WHERE po_detail_item = $po_detail_item";
  81     db_query($sql, "The quantity invoiced of the purchase order line could not be updated");
  82
  83     $sql = "UPDATE ".TB_PREF."grn_items
  84         SET quantity_inv = quantity_inv + $qty_invoiced
  85         WHERE id = $id";
  86         db_query($sql, "The quantity invoiced off the items received record could not be updated");
  87         return array($ret, $date, $unit_price);
  88 }
  89
  90 function get_deliveries_between($stock_id, $from, $to)
  91 {
  92         $from = date2sql($from);
  93         $to = date2sql($to);
  94         $sql = "SELECT SUM(-qty), SUM(-qty*standard_cost) FROM ".TB_PREF."stock_moves
  95                 WHERE type=13 AND stock_id='$stock_id' AND
  96                         tran_date>='$from' AND tran_date<='$to' GROUP BY stock_id";
  97         $result = db_query($sql, "The deliveries could not be updated");
  98         return db_fetch_row($result);
  99 }
 100
 101 function get_diff_in_home_currency($supplier, $old_date, $date, $amount1, $amount2)
 102 {
 103         $currency = get_supplier_currency($supplier);
 104         $amount1 = to_home_currency($amount1, $currency, $old_date);
 105         $amount2 = to_home_currency($amount2, $currency, $date);
 106         return $amount2 - $amount1;
 107 }
 108 //----------------------------------------------------------------------------------------
 109
 110 function add_supp_invoice($supp_trans) // do not receive as ref because we change locally
 111 {
 112         //$company_currency = get_company_currency();
 113         /*Start an sql transaction */
 114         begin_transaction();
 115
 116         $tax_total = 0;
 117     $taxes = $supp_trans->get_taxes($supp_trans->tax_group_id);
 118
 119     foreach ($taxes as $taxitem)
 120     {
 121                 $taxitem['Value'] =  round2($taxitem['Value'], user_price_dec());
 122         $tax_total += $taxitem['Value'];
 123     }
 124
 125     $invoice_items_total = $supp_trans->get_total_charged($supp_trans->tax_group_id);
 126
 127         if ($supp_trans->is_invoice)
 128                 $trans_type = 20;
 129         else
 130         {
 131                 $trans_type = 21;
 132                 // let's negate everything because it's a credit note
 133                 $invoice_items_total = -$invoice_items_total;
 134                 $tax_total = -$tax_total;
 135                 $supp_trans->ov_discount = -$supp_trans->ov_discount; // this isn't used at all...
 136         }
 137
 138     $date_ = $supp_trans->tran_date;
 139
 140     /*First insert the invoice into the supp_trans table*/
 141         $invoice_id = add_supp_trans($trans_type, $supp_trans->supplier_id, $date_, $supp_trans->due_date,
 142                 $supp_trans->reference, $supp_trans->supp_reference,
 143                 $invoice_items_total, $tax_total, $supp_trans->ov_discount);
 144
 145         $total = 0;
 146     /* Now the control account */
 147     $supplier_accounts = get_supplier_accounts($supp_trans->supplier_id);
 148
 149     /*Loop through the GL Entries and create a debit posting for each of the accounts entered */
 150
 151     /*the postings here are a little tricky, the logic goes like this:
 152     if its a general ledger amount it goes straight to the account specified
 153
 154     if its a GRN amount invoiced then :
 155
 156     The cost as originally credited to GRN suspense on arrival of items is debited to GRN suspense. Any difference
 157     between the std cost and the currency cost charged as converted at the ex rate of of the invoice is written off
 158     to the purchase price variance account applicable to the item being invoiced.
 159     */
 160     foreach ($supp_trans->gl_codes as $entered_gl_code)
 161     {
 162
 163             /*GL Items are straight forward - just do the debit postings to the GL accounts specified -
 164             the credit is to creditors control act  done later for the total invoice value + tax*/
 165
 166                 if (!$supp_trans->is_invoice)
 167                         $entered_gl_code->amount = -$entered_gl_code->amount;
 168
 169                 $memo_ = $entered_gl_code->memo_;
 170                 $total += add_gl_trans_supplier($trans_type, $invoice_id, $date_, $entered_gl_code->gl_code,
 171                         $entered_gl_code->gl_dim, $entered_gl_code->gl_dim2, $entered_gl_code->amount, $supp_trans->supplier_id);
 172
 173                 add_supp_invoice_gl_item($trans_type, $invoice_id, $entered_gl_code->gl_code,
 174                         $entered_gl_code->amount, $memo_);
 175     }
 176     foreach ($supp_trans->grn_items as $entered_grn)
 177     {
 178
 179         if (!$supp_trans->is_invoice)
 180         {
 181                         $entered_grn->this_quantity_inv = -$entered_grn->this_quantity_inv;
 182                         set_grn_item_credited($entered_grn, $supp_trans->supplier_id, $invoice_id, $date_);
 183         }
 184
 185                 $line_taxfree = $entered_grn->taxfree_charge_price($supp_trans->tax_group_id);
 186                 $line_tax = $entered_grn->full_charge_price($supp_trans->tax_group_id) - $line_taxfree;
 187                 $stock_gl_code = get_stock_gl_code($entered_grn->item_code);
 188
 189                 $total += add_gl_trans_supplier($trans_type, $invoice_id, $date_, $stock_gl_code["inventory_account"],
 190                         $stock_gl_code['dimension_id'], $stock_gl_code['dimension2_id'],
 191                         $entered_grn->this_quantity_inv * $line_taxfree, $supp_trans->supplier_id);
 192         // -------------- if price changed since po received. 16 Aug 2008 Joe Hunt
 193         if($supp_trans->is_invoice)
 194         {
 195                 $old = update_supp_received_items_for_invoice($entered_grn->id, $entered_grn->po_detail_item,
 196                         $entered_grn->this_quantity_inv, $entered_grn->chg_price);
 197                         // Since the standard cost is always calculated on basis of the po unit_price,
 198                         // this is also the price that should be the base of calculating the price diff.
 199                         // In cases where there is two different po invoices on the same delivery with different unit prices this will not work either
 200
 201                         //$old_price = $old[0];
 202
 203                         $old_price = $old[2];
 204
 205                         /*
 206                         If statement is removed. Should always check for deliveries nomatter if there has been a price change.
 207                         */
 208                         //if ($old_price != $entered_grn->chg_price) // price-change, so update
 209                         //{
 210                         //$diff = $entered_grn->chg_price - $old_price;
 211                         $old_date = sql2date($old[1]);
 212                         $diff = get_diff_in_home_currency($supp_trans->supplier_id, $old_date, $date_, $old_price,
 213                                 $entered_grn->chg_price);
 214                         // always return due to change in currency.
 215                         $mat_cost = update_average_material_cost(null, $entered_grn->item_code,
 216                                 $diff, $entered_grn->this_quantity_inv, $old_date, true);
 217                         $deliveries = get_deliveries_between($entered_grn->item_code, $old_date, Today()); // extend the period, if invoice is before any deliveries.
 218                         if ($deliveries[0] != 0) // have deliveries been done during the period?
 219                         {
 220                                 $deliveries[1] /= $deliveries[0];
 221                                 $amt = ($mat_cost - $deliveries[1]) * $deliveries[0]; // $amt in home currency
 222                                 if ($amt != 0.0)
 223                                 {
 224                                         add_gl_trans($trans_type, $invoice_id, $date_,  $stock_gl_code["cogs_account"],
 225                                                 $stock_gl_code['dimension_id'], $stock_gl_code['dimension2_id'], _("Cost diff."),
 226                                                 $amt, null, null, null,
 227                                                 "The general ledger transaction could not be added for the price variance of the inventory item");
 228                                         add_gl_trans($trans_type, $invoice_id, $date_,  $stock_gl_code["inventory_account"],
 229                                                 0, 0, _("Cost diff."), -$amt, null, null, null,
 230                                                 "The general ledger transaction could not be added for the price variance of the inventory item");
 231                                 }
 232                                 update_stock_move_pid(13, $entered_grn->item_code, $old_date, $date_, 0, $mat_cost);
 233                         }
 234                         update_stock_move_pid(25, $entered_grn->item_code, $old_date, $old_date, $supp_trans->supplier_id, $mat_cost);
 235                 //}
 236                 }
 237         // ----------------------------------------------------------------------
 238
 239                 add_supp_invoice_item($trans_type, $invoice_id, $entered_grn->item_code,
 240                         $entered_grn->item_description, 0,      $line_taxfree, $line_tax,
 241                         $entered_grn->this_quantity_inv, $entered_grn->id, $entered_grn->po_detail_item, "");
 242     } /* end of GRN postings */
 243     /* Now the TAX account */
 244     foreach ($taxes as $taxitem)
 245     {
 246         if ($taxitem['Value'] != 0)
 247         {
 248
 249                 if (!$supp_trans->is_invoice)
 250                         $taxitem['Value'] = -$taxitem['Value'];
 251                 // here we suppose that tax is never included in price (we are company customer).
 252                         add_supp_invoice_tax_item($trans_type, $invoice_id, $taxitem['tax_type_id'],
 253                                 $taxitem['rate'], 0, $taxitem['Value']);
 254
 255                 $total += add_gl_trans_supplier($trans_type, $invoice_id, $date_,
 256                         $taxitem['purchasing_gl_code'], 0, 0, $taxitem['Value'],
 257                         $supp_trans->supplier_id,
 258                         "A general ledger transaction for the tax amount could not be added");
 259         }
 260     }
 261
 262         $total += add_gl_trans($trans_type, $invoice_id, $date_, $supplier_accounts["payable_account"], 0, 0, "",
 263                 -$total, null, payment_person_types::supplier(), $supp_trans->supplier_id,
 264                 "The general ledger transaction for the control total could not be added");
 265
 266         /*Post a balance post if $total != 0 */
 267         add_gl_balance($trans_type, $invoice_id, $date_, -$total, payment_person_types::supplier(), $supp_trans->supplier_id);
 268
 269         add_comments($trans_type, $invoice_id, $date_, $supp_trans->Comments);
 270
 271         references::save_last($supp_trans->reference, $trans_type);
 272
 273     commit_transaction();
 274
 275     return $invoice_id;
 276 }
 277
 278 //----------------------------------------------------------------------------------------
 279
 280 // get all the invoices/credits for a given PO - quite long route to get there !
 281
 282 function get_po_invoices_credits($po_number)
 283 {
 284         $sql = "SELECT DISTINCT ".TB_PREF."supp_trans.trans_no, ".TB_PREF."supp_trans.type,
 285                 ov_amount+ov_discount+ov_gst AS Total,
 286                 ".TB_PREF."supp_trans.tran_date
 287                 FROM ".TB_PREF."supp_trans, ".TB_PREF."supp_invoice_items, ".TB_PREF."purch_order_details
 288                 WHERE ".TB_PREF."supp_invoice_items.supp_trans_no = ".TB_PREF."supp_trans.trans_no
 289                 AND ".TB_PREF."supp_invoice_items.po_detail_item_id = ".TB_PREF."purch_order_details.po_detail_item
 290                 AND ".TB_PREF."purch_order_details.order_no = $po_number";
 291
 292         return db_query($sql, "The invoices/credits for the po $po_number could not be retreived");
 293 }
 294
 295 //----------------------------------------------------------------------------------------
 296
 297 function read_supp_invoice($trans_no, $trans_type, &$supp_trans)
 298 {
 299         $sql = "SELECT ".TB_PREF."supp_trans.*, supp_name FROM ".TB_PREF."supp_trans,".TB_PREF."suppliers
 300                 WHERE trans_no = $trans_no AND type = $trans_type
 301                 AND ".TB_PREF."suppliers.supplier_id=".TB_PREF."supp_trans.supplier_id";
 302         $result = db_query($sql, "Cannot retreive a supplier transaction");
 303
 304         if (db_num_rows($result) == 1)
 305         {
 306                 $trans_row = db_fetch($result);
 307
 308                 $supp_trans->supplier_id = $trans_row["supplier_id"];
 309                 $supp_trans->supplier_name = $trans_row["supp_name"];
 310                 $supp_trans->tran_date = sql2date($trans_row["tran_date"]);
 311                 $supp_trans->due_date = sql2date($trans_row["due_date"]);
 312                 //$supp_trans->Comments = $trans_row["TransText"];
 313                 $supp_trans->Comments = "";
 314                 $supp_trans->reference = $trans_row["reference"];
 315                 $supp_trans->supp_reference = $trans_row["supp_reference"];
 316                 $supp_trans->ov_amount = $trans_row["ov_amount"];
 317                 $supp_trans->ov_discount = $trans_row["ov_discount"];
 318                 $supp_trans->ov_gst = $trans_row["ov_gst"];
 319
 320                 $id = $trans_row["trans_no"];
 321
 322                 $result = get_supp_invoice_items($trans_type, $id);
 323
 324                 if (db_num_rows($result) > 0)
 325                 {
 326
 327             while ($details_row = db_fetch($result))
 328             {
 329
 330                 if ($details_row["gl_code"] == 0)
 331                 {
 332                         $supp_trans->add_grn_to_trans($details_row["grn_item_id"], $details_row["po_detail_item_id"], $details_row["stock_id"],
 333                                         $details_row["description"], 0, 0, $details_row["quantity"], 0, $details_row["FullUnitPrice"],
 334                                         false, 0, 0);
 335                 }
 336                 else
 337                 {
 338                         $supp_trans->add_gl_codes_to_trans($details_row["gl_code"], get_gl_account_name($details_row["gl_code"]), 0, 0,
 339                                         $details_row["FullUnitPrice"], $details_row["memo_"]);
 340                 }
 341             }
 342         }
 343         else
 344         {
 345                         return display_db_error("Invalid supptrans details for supptrans number : $trans_no and type : $trans_type", $sql, true);
 346                 }
 347
 348         }
 349         else
 350         {
 351                 return display_db_error("Invalid supptrans number : $trans_no and type : $trans_type", $sql, true);
 352         }
 353 }
 354
 355 //----------------------------------------------------------------------------------------
 356
 357 function void_supp_invoice($type, $type_no)
 358 {
 359         begin_transaction();
 360
 361         void_bank_trans($type, $type_no, true);
 362
 363         void_gl_trans($type, $type_no, true);
 364
 365         void_supp_allocations($type, $type_no);
 366
 367         void_supp_trans($type, $type_no);
 368
 369         $result = get_supp_invoice_items($type, $type_no);
 370
 371         // now remove this invoice/credit from any GRNs/POs that it's related to
 372         if (db_num_rows($result) > 0)
 373         {
 374                 $date_ = Today();
 375         while ($details_row = db_fetch($result))
 376         {
 377                 if ((int)$details_row["grn_item_id"] > 0) // it can be empty for GL items
 378                 {
 379                         // Changed 2008-10-17 by Joe Hunt to get the avg. material cost updated
 380                                 $old = update_supp_received_items_for_invoice($details_row["grn_item_id"],
 381                                         $details_row["po_detail_item_id"], -$details_row["quantity"], $details_row["FullUnitPrice"]);
 382
 383                                 //$diff = $details_row["FullUnitPrice"] - $old[2];
 384                                 $old_date = sql2date($old[1]);
 385
 386                                 $batch = get_grn_batch_from_item($details_row["grn_item_id"]);
 387                                 $grn = get_grn_batch($batch);
 388
 389                                 $diff = get_diff_in_home_currency($grn["supplier_id"], $old_date, $date_, $old[2],
 390                                         $details_row["FullUnitPrice"]);
 391                                 // Only adjust the avg for the diff
 392                                 $mat_cost = update_average_material_cost(null, $details_row["stock_id"],
 393                                         $diff, -$details_row["quantity"], $date_, true);
 394
 395                                 $deliveries = get_deliveries_between($details_row["stock_id"], $old_date, $date_);
 396                                 if ($deliveries[0] != 0) // have deliveries been done during the period?
 397                                 {
 398                                         $deliveries[1] /= $deliveries[0];
 399                                         $amt = ($mat_cost - $deliveries[1]) * $deliveries[0]; // $amt in home currency
 400                                         if ($amt != 0.0)
 401                                         {
 402                                                 $stock_gl_code = get_stock_gl_code($details_row["stock_id"]);
 403                                                 add_gl_trans($type, $type_no, $date_,   $stock_gl_code["cogs_account"],
 404                                                         $stock_gl_code['dimension_id'], $stock_gl_code['dimension2_id'], _("Cost diff."),
 405                                                         -$amt, null, null, null,
 406                                                         "The general ledger transaction could not be added for the price variance of the inventory item");
 407                                                 add_gl_trans($type, $type_no, $date_, $stock_gl_code["inventory_account"],
 408                                                         0, 0, _("Cost diff."), $amt, null, null, null,
 409                                                         "The general ledger transaction could not be added for the price variance of the inventory item");
 410                                         }
 411                                         update_stock_move_pid(13, $details_row["stock_id"], $old_date, $date_, 0, $mat_cost);
 412                                 }
 413                                 update_stock_move_pid(25, $details_row["stock_id"], $old_date, $old_date, $grn['supplier_id'], $mat_cost);
 414
 415                 }
 416         }
 417         }
 418
 419         void_supp_invoice_items($type, $type_no);
 420         void_supp_invoice_tax_items($type, $type_no);
 421
 422         commit_transaction();
 423 }
 424
 425 //----------------------------------------------------------------------------------------
 426
 427
 428 ?>