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     $value = "IF(".TB_PREF."debtor_trans.type=11 OR ".TB_PREF."debtor_trans.type=12 OR ".TB_PREF."debtor_trans.type=2,
  15         -1, 1) *".
  16       "(".TB_PREF."debtor_trans.ov_amount + ".TB_PREF."debtor_trans.ov_gst + "
  17                 .TB_PREF."debtor_trans.ov_freight + ".TB_PREF."debtor_trans.ov_freight_tax + "
  18                 .TB_PREF."debtor_trans.ov_discount)";
  19         $due = "IF (".TB_PREF."debtor_trans.type=10,".TB_PREF."debtor_trans.due_date,".TB_PREF."debtor_trans.tran_date)";
  20     $sql = "SELECT ".TB_PREF."debtors_master.name, ".TB_PREF."debtors_master.curr_code, ".TB_PREF."payment_terms.terms,
  21                 ".TB_PREF."debtors_master.credit_limit, ".TB_PREF."credit_status.dissallow_invoices, ".TB_PREF."credit_status.reason_description,
  22
  23                 Sum(".$value.") AS Balance,
  24
  25                 Sum(IF ((TO_DAYS('$todate') - TO_DAYS($due)) >= 0,$value,0)) AS Due,
  26                 Sum(IF ((TO_DAYS('$todate') - TO_DAYS($due)) >= $past1,$value,0)) AS Overdue1,
  27                 Sum(IF ((TO_DAYS('$todate') - TO_DAYS($due)) >= $past2,$value,0)) AS Overdue2
  28
  29                 FROM ".TB_PREF."debtors_master,
  30                          ".TB_PREF."payment_terms,
  31                          ".TB_PREF."credit_status,
  32                          ".TB_PREF."debtor_trans
  33
  34                 WHERE
  35                          ".TB_PREF."debtors_master.payment_terms = ".TB_PREF."payment_terms.terms_indicator
  36                          AND ".TB_PREF."debtors_master.credit_status = ".TB_PREF."credit_status.id
  37                          AND ".TB_PREF."debtors_master.debtor_no = $customer_id
  38                          AND ".TB_PREF."debtor_trans.tran_date <= '$todate'
  39                          AND ".TB_PREF."debtor_trans.type <> 13
  40                          AND ".TB_PREF."debtors_master.debtor_no = ".TB_PREF."debtor_trans.debtor_no
  41
  42                 GROUP BY
  43                           ".TB_PREF."debtors_master.name,
  44                           ".TB_PREF."payment_terms.terms,
  45                           ".TB_PREF."payment_terms.days_before_due,
  46                           ".TB_PREF."payment_terms.day_in_following_month,
  47                           ".TB_PREF."debtors_master.credit_limit,
  48                           ".TB_PREF."credit_status.dissallow_invoices,
  49                           ".TB_PREF."credit_status.reason_description";
  50     $result = db_query($sql,"The customer details could not be retrieved");
  51
  52     if (db_num_rows($result) == 0)
  53     {
  54
  55         /*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 */
  56
  57         $nil_balance = true;
  58
  59         $sql = "SELECT ".TB_PREF."debtors_master.name, ".TB_PREF."debtors_master.curr_code, ".TB_PREF."debtors_master.debtor_no,  ".TB_PREF."payment_terms.terms,
  60                 ".TB_PREF."debtors_master.credit_limit, ".TB_PREF."credit_status.dissallow_invoices, ".TB_PREF."credit_status.reason_description
  61                 FROM ".TB_PREF."debtors_master,
  62                      ".TB_PREF."payment_terms,
  63                      ".TB_PREF."credit_status
  64
  65                 WHERE
  66                      ".TB_PREF."debtors_master.payment_terms = ".TB_PREF."payment_terms.terms_indicator
  67                      AND ".TB_PREF."debtors_master.credit_status = ".TB_PREF."credit_status.id
  68                      AND ".TB_PREF."debtors_master.debtor_no = '$customer_id'";
  69
  70         $result = db_query($sql,"The customer details could not be retrieved");
  71
  72     }
  73     else
  74     {
  75         $nil_balance = false;
  76     }
  77
  78     $customer_record = db_fetch($result);
  79
  80     if ($nil_balance == true)
  81     {
  82         $customer_record["Balance"] = 0;
  83         $customer_record["Due"] = 0;
  84         $customer_record["Overdue1"] = 0;
  85         $customer_record["Overdue2"] = 0;
  86     }
  87
  88     return $customer_record;
  89
  90 }
  91
  92 function get_customer($customer_id)
  93 {
  94         $sql = "SELECT * FROM ".TB_PREF."debtors_master WHERE debtor_no=$customer_id";
  95
  96         $result = db_query($sql, "could not get customer");
  97
  98         return db_fetch($result);
  99 }
 100
 101 function get_customer_name($customer_id)
 102 {
 103         $sql = "SELECT name FROM ".TB_PREF."debtors_master WHERE debtor_no=$customer_id";
 104
 105         $result = db_query($sql, "could not get customer");
 106
 107         $row = db_fetch_row($result);
 108
 109         return $row[0];
 110 }
 111
 112 function get_area_name($id)
 113 {
 114         $sql = "SELECT description FROM ".TB_PREF."areas WHERE area_code=$id";
 115
 116         $result = db_query($sql, "could not get sales type");
 117
 118         $row = db_fetch_row($result);
 119         return $row[0];
 120 }
 121
 122 function get_salesman_name($id)
 123 {
 124         $sql = "SELECT salesman_name FROM ".TB_PREF."salesman WHERE salesman_code=$id";
 125
 126         $result = db_query($sql, "could not get sales type");
 127
 128         $row = db_fetch_row($result);
 129         return $row[0];
 130 }
 131
 132
 133
 134
 135 ?>