sales/includes/db/customers_db.inc

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