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     $sql = "UPDATE ".TB_PREF."purch_order_details
  53                 SET qty_invoiced = qty_invoiced + $qty_invoiced ";
  54
  55         if ($chg_price != null)
  56                 $sql .= " , act_price = $chg_price ";
  57
  58         $sql .= " WHERE po_detail_item = $po_detail_item";
  59     db_query($sql, "The quantity invoiced of the purchase order line could not be updated");
  60
  61     $sql = "UPDATE ".TB_PREF."grn_items
  62         SET quantity_inv = quantity_inv + $qty_invoiced
  63         WHERE id = $id";
  64         db_query($sql, "The quantity invoiced off the items received record could not be updated");
  65 }
  66
  67 //----------------------------------------------------------------------------------------
  68
  69 function add_supp_invoice($supp_trans) // do not receive as ref because we change locally
  70 {
  71         $company_currency = get_company_currency();
  72
  73         /*Start an sql transaction */
  74         begin_transaction();
  75
  76         $tax_total = 0;
  77     $taxes = $supp_trans->get_taxes($supp_trans->tax_group_id);
  78     foreach ($taxes as $taxitem)
  79     {
  80         $tax_total += $taxitem['Value'];
  81     }
  82
  83     $invoice_items_total = $supp_trans->get_total_charged($supp_trans->tax_group_id);
  84
  85         if ($supp_trans->is_invoice)
  86                 $trans_type = 20;
  87         else
  88         {
  89                 $trans_type = 21;
  90                 // let's negate everything because it's a credit note
  91                 $invoice_items_total = -$invoice_items_total;
  92                 $tax_total = -$tax_total;
  93                 $supp_trans->ov_discount = -$supp_trans->ov_discount; // this isn't used at all...
  94         }
  95
  96     $date_ = $supp_trans->tran_date;
  97
  98     /*First insert the invoice into the supp_trans table*/
  99         $invoice_id = add_supp_trans($trans_type, $supp_trans->supplier_id, $date_, $supp_trans->due_date,
 100                 $supp_trans->reference, $supp_trans->supp_reference,
 101                 $invoice_items_total, $tax_total, $supp_trans->ov_discount);
 102
 103     /* Now the control account */
 104     $supplier_accounts = get_supplier_accounts($supp_trans->supplier_id);
 105     add_gl_trans_supplier($trans_type, $invoice_id, $date_, $supplier_accounts["payable_account"], 0, 0,
 106                 -($invoice_items_total +  $tax_total + $supp_trans->ov_discount),
 107                 $supp_trans->supplier_id,
 108                 "The general ledger transaction for the control total could not be added");
 109
 110     /*Loop through the GL Entries and create a debit posting for each of the accounts entered */
 111
 112     /*the postings here are a little tricky, the logic goes like this:
 113     if its a general ledger amount it goes straight to the account specified
 114
 115     if its a GRN amount invoiced then :
 116
 117     The cost as originally credited to GRN suspense on arrival of items is debited to GRN suspense. Any difference
 118     between the std cost and the currency cost charged as converted at the ex rate of of the invoice is written off
 119     to the purchase price variance account applicable to the item being invoiced.
 120     */
 121
 122     foreach ($supp_trans->gl_codes as $entered_gl_code)
 123     {
 124
 125             /*GL Items are straight forward - just do the debit postings to the GL accounts specified -
 126             the credit is to creditors control act  done later for the total invoice value + tax*/
 127
 128                 if (!$supp_trans->is_invoice)
 129                         $entered_gl_code->amount = -$entered_gl_code->amount;
 130
 131                 $memo_ = $entered_gl_code->memo_;
 132                 add_gl_trans_supplier($trans_type, $invoice_id, $date_, $entered_gl_code->gl_code,
 133                         $entered_gl_code->gl_dim, $entered_gl_code->gl_dim2, $entered_gl_code->amount, $supp_trans->supplier_id);
 134
 135                 add_supp_invoice_gl_item($trans_type, $invoice_id, $entered_gl_code->gl_code,
 136                         $entered_gl_code->amount, $memo_);
 137     }
 138
 139     foreach ($supp_trans->grn_items as $entered_grn)
 140     {
 141
 142         if (!$supp_trans->is_invoice)
 143         {
 144                         $entered_grn->this_quantity_inv = -$entered_grn->this_quantity_inv;
 145         }
 146
 147                 $line_taxfree = $entered_grn->taxfree_charge_price($supp_trans->tax_group_id);
 148                 $line_tax = $entered_grn->full_charge_price() - $line_taxfree;
 149
 150         update_supp_received_items_for_invoice($entered_grn->id, $entered_grn->po_detail_item,
 151                 $entered_grn->this_quantity_inv, $entered_grn->chg_price);
 152
 153                 $stock_gl_code = get_stock_gl_code($entered_grn->item_code);
 154                 $stock_entry_account = $stock_gl_code["inventory_account"];
 155
 156                 add_gl_trans_supplier($trans_type, $invoice_id, $date_, $stock_entry_account,
 157                         $stock_gl_code['dimension_id'], $stock_gl_code['dimension2_id'],
 158                         $entered_grn->this_quantity_inv * $line_taxfree, $supp_trans->supplier_id);
 159
 160 /*
 161         $supplierCurrency = get_supplier_currency($supp_trans->supplier_id);
 162         $localChgPrice = to_home_currency($entered_grn->chg_price, $supplierCurrency, $date_);
 163         $PurchPriceVar = $entered_grn->this_quantity_inv * ($localChgPrice - $entered_grn->std_cost_unit);
 164
 165         echo "purchase price variance is $PurchPriceVar";
 166
 167         // Yes but where to post this difference to - if its an inventory item the variance account
 168         //      must be retreived from the stock category record
 169
 170         if ($PurchPriceVar !=0){ // don't bother with this lot if there is no difference !
 171         // need to get the stock category record for this inventory item -
 172                 $stock_gl_code = get_stock_gl_code($entered_grn->item_code);
 173
 174
 175                 add_gl_trans_supplier($trans_type, $invoice_id, $date_, $stock_gl_code["purch_price_var_act"],
 176                         $PurchPriceVar, $supp_trans->supplier_id,
 177                         "The general ledger transaction could not be added for the price variance of the inventory item");
 178         }*/
 179
 180                 add_supp_invoice_item($trans_type, $invoice_id, $entered_grn->item_code,
 181                         $entered_grn->item_description, 0,      $line_taxfree, $line_tax,
 182                         $entered_grn->this_quantity_inv, $entered_grn->id, $entered_grn->po_detail_item, "");
 183     } /* end of GRN postings */
 184
 185     /* Now the TAX account */
 186     foreach ($taxes as $taxitem)
 187     {
 188         if ($taxitem['Value'] != 0)
 189         {
 190
 191                 if (!$supp_trans->is_invoice)
 192                         $taxitem['Value'] = -$taxitem['Value'];
 193
 194                         add_supp_invoice_tax_item($trans_type, $invoice_id, $taxitem['tax_type_id'],
 195                                 $taxitem['rate'], $taxitem['included_in_price'], $taxitem['Value']);
 196
 197                 add_gl_trans_supplier($trans_type, $invoice_id, $date_,
 198                         $taxitem['purchasing_gl_code'], 0, 0, $taxitem['Value'],
 199                         $supp_trans->supplier_id,
 200                         "A general ledger transaction for the tax amount could not be added");
 201         }
 202     }
 203
 204         add_comments($trans_type, $invoice_id, $date_, $supp_trans->Comments);
 205
 206         references::save_last($supp_trans->reference, $trans_type);
 207
 208     commit_transaction();
 209
 210     return $invoice_id;
 211 }
 212
 213 //----------------------------------------------------------------------------------------
 214
 215 // get all the invoices/credits for a given PO - quite long route to get there !
 216
 217 function get_po_invoices_credits($po_number)
 218 {
 219         $sql = "SELECT DISTINCT ".TB_PREF."supp_trans.trans_no, ".TB_PREF."supp_trans.type,
 220                 ov_amount+ov_discount+ov_gst AS Total,
 221                 ".TB_PREF."supp_trans.tran_date
 222                 FROM ".TB_PREF."supp_trans, ".TB_PREF."supp_invoice_items, ".TB_PREF."purch_order_details
 223                 WHERE ".TB_PREF."supp_invoice_items.supp_trans_no = ".TB_PREF."supp_trans.trans_no
 224                 AND ".TB_PREF."supp_invoice_items.po_detail_item_id = ".TB_PREF."purch_order_details.po_detail_item
 225                 AND ".TB_PREF."purch_order_details.order_no = $po_number";
 226
 227         return db_query($sql, "The invoices/credits for the po $po_number could not be retreived");
 228 }
 229
 230 //----------------------------------------------------------------------------------------
 231
 232 function read_supp_invoice($trans_no, $trans_type, &$supp_trans)
 233 {
 234         $sql = "SELECT ".TB_PREF."supp_trans.*, supp_name FROM ".TB_PREF."supp_trans,".TB_PREF."suppliers
 235                 WHERE trans_no = $trans_no AND type = $trans_type
 236                 AND ".TB_PREF."suppliers.supplier_id=".TB_PREF."supp_trans.supplier_id";
 237         $result = db_query($sql, "Cannot retreive a supplier transaction");
 238
 239         if (db_num_rows($result) == 1)
 240         {
 241                 $trans_row = db_fetch($result);
 242
 243                 $supp_trans->supplier_id = $trans_row["supplier_id"];
 244                 $supp_trans->supplier_name = $trans_row["supp_name"];
 245                 $supp_trans->tran_date = sql2date($trans_row["tran_date"]);
 246                 $supp_trans->due_date = sql2date($trans_row["due_date"]);
 247                 //$supp_trans->Comments = $trans_row["TransText"];
 248                 $supp_trans->Comments = "";
 249                 $supp_trans->reference = $trans_row["reference"];
 250                 $supp_trans->supp_reference = $trans_row["supp_reference"];
 251                 $supp_trans->ov_amount = $trans_row["ov_amount"];
 252                 $supp_trans->ov_discount = $trans_row["ov_discount"];
 253                 $supp_trans->ov_gst = $trans_row["ov_gst"];
 254
 255                 $id = $trans_row["trans_no"];
 256
 257                 $result = get_supp_invoice_items($trans_type, $id);
 258
 259                 if (db_num_rows($result) > 0)
 260                 {
 261
 262             while ($details_row = db_fetch($result))
 263             {
 264
 265                 if ($details_row["gl_code"] == 0)
 266                 {
 267                         $supp_trans->add_grn_to_trans($details_row["grn_item_id"], $details_row["po_detail_item_id"], $details_row["stock_id"],
 268                                         $details_row["description"], 0, 0, $details_row["quantity"], 0, $details_row["FullUnitPrice"],
 269                                         false, 0, 0);
 270                 }
 271                 else
 272                 {
 273                         $supp_trans->add_gl_codes_to_trans($details_row["gl_code"], get_gl_account_name($details_row["gl_code"]), 0, 0,
 274                                         $details_row["FullUnitPrice"], $details_row["memo_"]);
 275                 }
 276             }
 277         }
 278         else
 279         {
 280                         return display_db_error("Invalid supptrans details for supptrans number : $trans_no and type : $trans_type", $sql, true);
 281                 }
 282
 283         }
 284         else
 285         {
 286                 return display_db_error("Invalid supptrans number : $trans_no and type : $trans_type", $sql, true);
 287         }
 288 }
 289
 290 //----------------------------------------------------------------------------------------
 291
 292 function void_supp_invoice($type, $type_no)
 293 {
 294         begin_transaction();
 295
 296         void_bank_trans($type, $type_no, true);
 297
 298         void_gl_trans($type, $type_no, true);
 299
 300         void_supp_allocations($type, $type_no);
 301
 302         void_supp_trans($type, $type_no);
 303
 304         $result = get_supp_invoice_items($type, $type_no);
 305
 306         // now remove this invoice/credit from any GRNs/POs that it's related to
 307         if (db_num_rows($result) > 0)
 308         {
 309         while ($details_row = db_fetch($result))
 310         {
 311                 if (strlen($details_row["grn_item_id"]) > 0) // it can be empty for GL items
 312                 {
 313                                 update_supp_received_items_for_invoice($details_row["grn_item_id"],
 314                                         $details_row["po_detail_item_id"], -$details_row["quantity"]);
 315                 }
 316         }
 317         }
 318
 319         void_supp_invoice_items($type, $type_no);
 320         void_supp_invoice_tax_items($type, $type_no);
 321
 322         commit_transaction();
 323 }
 324
 325 //----------------------------------------------------------------------------------------
 326
 327
 328 ?>