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     $sql = "SELECT ".TB_PREF."suppliers.supp_name, ".TB_PREF."suppliers.curr_code, ".TB_PREF."payment_terms.terms,
  15
  16                 Sum(".TB_PREF."supp_trans.ov_amount + ".TB_PREF."supp_trans.ov_gst + ".TB_PREF."supp_trans.ov_discount) AS Balance,
  17
  18                 Sum(IF (".TB_PREF."payment_terms.days_before_due > 0,
  19                         CASE WHEN (TO_DAYS('$todate') - TO_DAYS(".TB_PREF."supp_trans.tran_date)) >= ".TB_PREF."payment_terms.days_before_due
  20                         THEN
  21                                 ".TB_PREF."supp_trans.ov_amount + ".TB_PREF."supp_trans.ov_gst + ".TB_PREF."supp_trans.ov_discount
  22                         ELSE
  23                                 0
  24                         END,
  25
  26                         CASE WHEN TO_DAYS('$todate') - TO_DAYS(DATE_ADD(DATE_ADD(".TB_PREF."supp_trans.tran_date,
  27                                 INTERVAL 1 MONTH), INTERVAL (".TB_PREF."payment_terms.day_in_following_month -
  28                                 DAYOFMONTH(".TB_PREF."supp_trans.tran_date)) DAY)) >= 0
  29                         THEN
  30                                 ".TB_PREF."supp_trans.ov_amount + ".TB_PREF."supp_trans.ov_gst + ".TB_PREF."supp_trans.ov_discount
  31                         ELSE
  32                                 0
  33                         END
  34
  35                 )) AS Due,
  36
  37                 Sum(IF (".TB_PREF."payment_terms.days_before_due > 0,
  38                         CASE WHEN TO_DAYS('$todate') - TO_DAYS(".TB_PREF."supp_trans.tran_date) > ".TB_PREF."payment_terms.days_before_due
  39                                 AND TO_DAYS('$todate') - TO_DAYS(".TB_PREF."supp_trans.tran_date) >= (".TB_PREF."payment_terms.days_before_due + $past1)
  40                         THEN
  41                                 ".TB_PREF."supp_trans.ov_amount + ".TB_PREF."supp_trans.ov_gst + ".TB_PREF."supp_trans.ov_discount
  42                         ELSE
  43                                 0
  44                         END,
  45
  46                         CASE WHEN (TO_DAYS('$todate') - TO_DAYS(DATE_ADD(DATE_ADD(".TB_PREF."supp_trans.tran_date,
  47                                 INTERVAL 1  MONTH), INTERVAL (".TB_PREF."payment_terms.day_in_following_month -
  48                                 DAYOFMONTH(".TB_PREF."supp_trans.tran_date)) DAY)) >= $past1)
  49                         THEN
  50                                 ".TB_PREF."supp_trans.ov_amount + ".TB_PREF."supp_trans.ov_gst + ".TB_PREF."supp_trans.ov_discount
  51                         ELSE
  52                                 0
  53                         END
  54
  55                 )) AS Overdue1,
  56
  57                 Sum(IF (".TB_PREF."payment_terms.days_before_due > 0,
  58                         CASE WHEN TO_DAYS('$todate') - TO_DAYS(".TB_PREF."supp_trans.tran_date) > ".TB_PREF."payment_terms.days_before_due
  59                                 AND TO_DAYS('$todate') - TO_DAYS(".TB_PREF."supp_trans.tran_date) >= (".TB_PREF."payment_terms.days_before_due + $past2)
  60                         THEN
  61                                 ".TB_PREF."supp_trans.ov_amount + ".TB_PREF."supp_trans.ov_gst + ".TB_PREF."supp_trans.ov_discount
  62                         ELSE
  63                                 0
  64                         END,
  65
  66                         CASE WHEN (TO_DAYS('$todate') - TO_DAYS(DATE_ADD(DATE_ADD(".TB_PREF."supp_trans.tran_date,
  67                                 INTERVAL 1  MONTH), INTERVAL (".TB_PREF."payment_terms.day_in_following_month -
  68                                 DAYOFMONTH(".TB_PREF."supp_trans.tran_date)) DAY)) >= $past2)
  69                         THEN
  70                                 ".TB_PREF."supp_trans.ov_amount + ".TB_PREF."supp_trans.ov_gst + ".TB_PREF."supp_trans.ov_discount
  71                         ELSE
  72                                 0
  73                         END
  74
  75                 )) AS Overdue2
  76
  77                 FROM ".TB_PREF."suppliers,
  78                          ".TB_PREF."payment_terms,
  79                          ".TB_PREF."supp_trans
  80
  81                 WHERE
  82                          ".TB_PREF."suppliers.payment_terms = ".TB_PREF."payment_terms.terms_indicator
  83                          AND ".TB_PREF."suppliers.supplier_id = $supplier_id
  84                          AND ".TB_PREF."supp_trans.tran_date <= '$todate'
  85                          AND ".TB_PREF."suppliers.supplier_id = ".TB_PREF."supp_trans.supplier_id
  86
  87                 GROUP BY
  88                           ".TB_PREF."suppliers.supp_name,
  89                           ".TB_PREF."payment_terms.terms,
  90                           ".TB_PREF."payment_terms.days_before_due,
  91                           ".TB_PREF."payment_terms.day_in_following_month";
  92
  93     $result = db_query($sql,"The customer details could not be retrieved");
  94
  95     if (db_num_rows($result) == 0)
  96     {
  97
  98         /*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 */
  99
 100         $nil_balance = true;
 101
 102         $sql = "SELECT ".TB_PREF."suppliers.supp_name, ".TB_PREF."suppliers.curr_code, ".TB_PREF."suppliers.supplier_id,  ".TB_PREF."payment_terms.terms
 103                         FROM ".TB_PREF."suppliers,
 104                                  ".TB_PREF."payment_terms
 105                         WHERE
 106                                  ".TB_PREF."suppliers.payment_terms = ".TB_PREF."payment_terms.terms_indicator
 107                                  AND ".TB_PREF."suppliers.supplier_id = '$supplier_id'";
 108
 109         $result = db_query($sql,"The customer details could not be retrieved");
 110
 111     }
 112     else
 113     {
 114         $nil_balance = false;
 115     }
 116
 117     $supp = db_fetch($result);
 118
 119     if ($nil_balance == true)
 120     {
 121         $supp["Balance"] = 0;
 122         $supp["Due"] = 0;
 123         $supp["Overdue1"] = 0;
 124         $supp["Overdue2"] = 0;
 125     }
 126
 127     return $supp;
 128
 129 }
 130
 131 function get_supplier($supplier_id)
 132 {
 133         $sql = "SELECT * FROM ".TB_PREF."suppliers WHERE supplier_id=$supplier_id";
 134
 135         $result = db_query($sql, "could not get supplier");
 136
 137         return db_fetch($result);
 138 }
 139
 140 function get_supplier_name($supplier_id)
 141 {
 142         $sql = "SELECT supp_name AS name FROM ".TB_PREF."suppliers WHERE supplier_id=$supplier_id";
 143
 144         $result = db_query($sql, "could not get supplier");
 145
 146         $row = db_fetch_row($result);
 147
 148         return $row[0];
 149 }
 150
 151 function get_supplier_accounts($supplier_id)
 152 {
 153         $sql = "SELECT payable_account,purchase_account,payment_discount_account FROM ".TB_PREF."suppliers WHERE supplier_id=$supplier_id";
 154
 155         $result = db_query($sql, "could not get supplier");
 156
 157         return db_fetch($result);
 158 }
 159
 160
 161 ?>