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