sales/includes/db/customers_db.inc

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