purchasing/includes/db/suppliers_db.inc

   1 <?php
   2 /**********************************************************************
   3     Copyright (C) FrontAccounting, LLC.
   4         Released under the terms of the GNU General Public License, GPL,
   5         as published by the Free Software Foundation, either version 3
   6         of the License, or (at your option) any later version.
   7     This program is distributed in the hope that it will be useful,
   8     but WITHOUT ANY WARRANTY; without even the implied warranty of
   9     MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.
  10     See the License here <http://www.gnu.org/licenses/gpl-3.0.html>.
  11 ***********************************************************************/
  12 function get_supplier_details($supplier_id, $to=null)
  13 {
  14
  15         if ($to == null)
  16                 $todate = date("Y-m-d");
  17         else
  18                 $todate = date2sql($to);
  19         $past1 = get_company_pref('past_due_days');
  20         $past2 = 2 * $past1;
  21         // removed - supp_trans.alloc from all summations
  22
  23     $value = "(".TB_PREF."supp_trans.ov_amount + ".TB_PREF."supp_trans.ov_gst + ".TB_PREF."supp_trans.ov_discount)";
  24         $due = "IF (".TB_PREF."supp_trans.type=".ST_SUPPINVOICE." OR ".TB_PREF."supp_trans.type=".ST_SUPPCREDIT.",".TB_PREF."supp_trans.due_date,".TB_PREF."supp_trans.tran_date)";
  25     $sql = "SELECT ".TB_PREF."suppliers.supp_name, ".TB_PREF."suppliers.curr_code, ".TB_PREF."payment_terms.terms,
  26
  27                 Sum($value) AS Balance,
  28
  29                 Sum(IF ((TO_DAYS('$todate') - TO_DAYS($due)) >= 0,$value,0)) AS Due,
  30                 Sum(IF ((TO_DAYS('$todate') - TO_DAYS($due)) >= $past1,$value,0)) AS Overdue1,
  31                 Sum(IF ((TO_DAYS('$todate') - TO_DAYS($due)) >= $past2,$value,0)) AS Overdue2
  32
  33                 FROM ".TB_PREF."suppliers,
  34                          ".TB_PREF."payment_terms,
  35                          ".TB_PREF."supp_trans
  36
  37                 WHERE
  38                          ".TB_PREF."suppliers.payment_terms = ".TB_PREF."payment_terms.terms_indicator
  39                          AND ".TB_PREF."suppliers.supplier_id = $supplier_id
  40                          AND ".TB_PREF."supp_trans.tran_date <= '$todate'
  41                          AND ".TB_PREF."suppliers.supplier_id = ".TB_PREF."supp_trans.supplier_id
  42
  43                 GROUP BY
  44                           ".TB_PREF."suppliers.supp_name,
  45                           ".TB_PREF."payment_terms.terms,
  46                           ".TB_PREF."payment_terms.days_before_due,
  47                           ".TB_PREF."payment_terms.day_in_following_month";
  48
  49     $result = db_query($sql,"The customer details could not be retrieved");
  50
  51     if (db_num_rows($result) == 0)
  52     {
  53
  54         /*Because there is no balance - so just retrieve the header information about the customer - the choice is do one query to get the balance and transactions for those customers who have a balance and two queries for those who don't have a balance OR always do two queries - I opted for the former */
  55
  56         $nil_balance = true;
  57
  58         $sql = "SELECT ".TB_PREF."suppliers.supp_name, ".TB_PREF."suppliers.curr_code, ".TB_PREF."suppliers.supplier_id,  ".TB_PREF."payment_terms.terms
  59                         FROM ".TB_PREF."suppliers,
  60                                  ".TB_PREF."payment_terms
  61                         WHERE
  62                                  ".TB_PREF."suppliers.payment_terms = ".TB_PREF."payment_terms.terms_indicator
  63                                  AND ".TB_PREF."suppliers.supplier_id = ".db_escape($supplier_id);
  64
  65         $result = db_query($sql,"The customer details could not be retrieved");
  66
  67     }
  68     else
  69     {
  70         $nil_balance = false;
  71     }
  72
  73     $supp = db_fetch($result);
  74
  75     if ($nil_balance == true)
  76     {
  77         $supp["Balance"] = 0;
  78         $supp["Due"] = 0;
  79         $supp["Overdue1"] = 0;
  80         $supp["Overdue2"] = 0;
  81     }
  82
  83     return $supp;
  84
  85 }
  86
  87 function get_supplier($supplier_id)
  88 {
  89         $sql = "SELECT * FROM ".TB_PREF."suppliers WHERE supplier_id=".db_escape($supplier_id);
  90
  91         $result = db_query($sql, "could not get supplier");
  92
  93         return db_fetch($result);
  94 }
  95
  96 function get_supplier_name($supplier_id)
  97 {
  98         $sql = "SELECT supp_name AS name FROM ".TB_PREF."suppliers WHERE supplier_id=".db_escape($supplier_id);
  99
 100         $result = db_query($sql, "could not get supplier");
 101
 102         $row = db_fetch_row($result);
 103
 104         return $row[0];
 105 }
 106
 107 function get_supplier_accounts($supplier_id)
 108 {
 109         $sql = "SELECT payable_account,purchase_account,payment_discount_account FROM ".TB_PREF."suppliers WHERE supplier_id=".db_escape($supplier_id);
 110
 111         $result = db_query($sql, "could not get supplier");
 112
 113         return db_fetch($result);
 114 }
 115
 116
 117 ?>