purchasing/includes/db/suppliers_db.inc

   1 <?php
   2
   3 function get_supplier_details($supplier_id, $to=null)
   4 {
   5
   6         if ($to == null)
   7                 $todate = date("Y-m-d");
   8         else
   9                 $todate = date2sql($to);
  10         $past1 = get_company_pref('past_due_days');
  11         $past2 = 2 * $past1;
  12         // removed - supp_trans.alloc from all summations
  13
  14     $value = "(".TB_PREF."supp_trans.ov_amount + ".TB_PREF."supp_trans.ov_gst + ".TB_PREF."supp_trans.ov_discount)";
  15         $due = "IF (".TB_PREF."supp_trans.type=20 OR ".TB_PREF."supp_trans.type=21,".TB_PREF."supp_trans.due_date,".TB_PREF."supp_trans.tran_date)";
  16     $sql = "SELECT ".TB_PREF."suppliers.supp_name, ".TB_PREF."suppliers.curr_code, ".TB_PREF."payment_terms.terms,
  17
  18                 Sum($value) AS Balance,
  19
  20                 Sum(IF ((TO_DAYS('$todate') - TO_DAYS($due)) >= 0,$value,0)) AS Due,
  21                 Sum(IF ((TO_DAYS('$todate') - TO_DAYS($due)) >= $past1,$value,0)) AS Overdue1,
  22                 Sum(IF ((TO_DAYS('$todate') - TO_DAYS($due)) >= $past2,$value,0)) AS Overdue2
  23
  24                 FROM ".TB_PREF."suppliers,
  25                          ".TB_PREF."payment_terms,
  26                          ".TB_PREF."supp_trans
  27
  28                 WHERE
  29                          ".TB_PREF."suppliers.payment_terms = ".TB_PREF."payment_terms.terms_indicator
  30                          AND ".TB_PREF."suppliers.supplier_id = $supplier_id
  31                          AND ".TB_PREF."supp_trans.tran_date <= '$todate'
  32                          AND ".TB_PREF."suppliers.supplier_id = ".TB_PREF."supp_trans.supplier_id
  33
  34                 GROUP BY
  35                           ".TB_PREF."suppliers.supp_name,
  36                           ".TB_PREF."payment_terms.terms,
  37                           ".TB_PREF."payment_terms.days_before_due,
  38                           ".TB_PREF."payment_terms.day_in_following_month";
  39
  40     $result = db_query($sql,"The customer details could not be retrieved");
  41
  42     if (db_num_rows($result) == 0)
  43     {
  44
  45         /*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 */
  46
  47         $nil_balance = true;
  48
  49         $sql = "SELECT ".TB_PREF."suppliers.supp_name, ".TB_PREF."suppliers.curr_code, ".TB_PREF."suppliers.supplier_id,  ".TB_PREF."payment_terms.terms
  50                         FROM ".TB_PREF."suppliers,
  51                                  ".TB_PREF."payment_terms
  52                         WHERE
  53                                  ".TB_PREF."suppliers.payment_terms = ".TB_PREF."payment_terms.terms_indicator
  54                                  AND ".TB_PREF."suppliers.supplier_id = '$supplier_id'";
  55
  56         $result = db_query($sql,"The customer details could not be retrieved");
  57
  58     }
  59     else
  60     {
  61         $nil_balance = false;
  62     }
  63
  64     $supp = db_fetch($result);
  65
  66     if ($nil_balance == true)
  67     {
  68         $supp["Balance"] = 0;
  69         $supp["Due"] = 0;
  70         $supp["Overdue1"] = 0;
  71         $supp["Overdue2"] = 0;
  72     }
  73
  74     return $supp;
  75
  76 }
  77
  78 function get_supplier($supplier_id)
  79 {
  80         $sql = "SELECT * FROM ".TB_PREF."suppliers WHERE supplier_id=$supplier_id";
  81
  82         $result = db_query($sql, "could not get supplier");
  83
  84         return db_fetch($result);
  85 }
  86
  87 function get_supplier_name($supplier_id)
  88 {
  89         $sql = "SELECT supp_name AS name FROM ".TB_PREF."suppliers WHERE supplier_id=$supplier_id";
  90
  91         $result = db_query($sql, "could not get supplier");
  92
  93         $row = db_fetch_row($result);
  94
  95         return $row[0];
  96 }
  97
  98 function get_supplier_accounts($supplier_id)
  99 {
 100         $sql = "SELECT payable_account,purchase_account,payment_discount_account FROM ".TB_PREF."suppliers WHERE supplier_id=$supplier_id";
 101
 102         $result = db_query($sql, "could not get supplier");
 103
 104         return db_fetch($result);
 105 }
 106
 107
 108 ?>