sales/inquiry/customer_inquiry.php

   1 <?php
   2 /**********************************************************************
   3     Copyright (C) FrontAccounting, LLC.
   4         Released under the terms of the GNU General Public License, GPL,
   5         as published by the Free Software Foundation, either version 3
   6         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/gpl-3.0.html>.
  11 ***********************************************************************/
  12 $page_security = 'SA_SALESTRANSVIEW';
  13 $path_to_root = "../..";
  14 include($path_to_root . "/includes/db_pager.inc");
  15 include_once($path_to_root . "/includes/session.inc");
  16
  17 include_once($path_to_root . "/sales/includes/sales_ui.inc");
  18 include_once($path_to_root . "/sales/includes/sales_db.inc");
  19 include_once($path_to_root . "/reporting/includes/reporting.inc");
  20
  21 $js = "";
  22 if ($use_popup_windows)
  23         $js .= get_js_open_window(900, 500);
  24 if ($use_date_picker)
  25         $js .= get_js_date_picker();
  26 page(_($help_context = "Customer Transactions"), false, false, "", $js);
  27
  28
  29 if (isset($_GET['customer_id']))
  30 {
  31         $_POST['customer_id'] = $_GET['customer_id'];
  32 }
  33
  34 //------------------------------------------------------------------------------------------------
  35
  36 start_form();
  37
  38 if (!isset($_POST['customer_id']))
  39         $_POST['customer_id'] = get_global_customer();
  40
  41 start_table("class='tablestyle_noborder'");
  42 start_row();
  43
  44 customer_list_cells(_("Select a customer: "), 'customer_id', null, true);
  45
  46 date_cells(_("From:"), 'TransAfterDate', '', null, -30);
  47 date_cells(_("To:"), 'TransToDate', '', null, 1);
  48
  49 if (!isset($_POST['filterType']))
  50         $_POST['filterType'] = 0;
  51
  52 cust_allocations_list_cells(null, 'filterType', $_POST['filterType'], true);
  53
  54 submit_cells('RefreshInquiry', _("Search"),'',_('Refresh Inquiry'), 'default');
  55 end_row();
  56 end_table();
  57
  58 set_global_customer($_POST['customer_id']);
  59
  60 //------------------------------------------------------------------------------------------------
  61
  62 function display_customer_summary($customer_record)
  63 {
  64         global $table_style;
  65
  66         $past1 = get_company_pref('past_due_days');
  67         $past2 = 2 * $past1;
  68     if ($customer_record["dissallow_invoices"] != 0)
  69     {
  70         echo "<center><font color=red size=4><b>" . _("CUSTOMER ACCOUNT IS ON HOLD") . "</font></b></center>";
  71     }
  72
  73         $nowdue = "1-" . $past1 . " " . _('Days');
  74         $pastdue1 = $past1 + 1 . "-" . $past2 . " " . _('Days');
  75         $pastdue2 = _('Over') . " " . $past2 . " " . _('Days');
  76
  77     start_table("width=80% $table_style");
  78     $th = array(_("Currency"), _("Terms"), _("Current"), $nowdue,
  79         $pastdue1, $pastdue2, _("Total Balance"));
  80     table_header($th);
  81
  82         start_row();
  83     label_cell($customer_record["curr_code"]);
  84     label_cell($customer_record["terms"]);
  85         amount_cell($customer_record["Balance"] - $customer_record["Due"]);
  86         amount_cell($customer_record["Due"] - $customer_record["Overdue1"]);
  87         amount_cell($customer_record["Overdue1"] - $customer_record["Overdue2"]);
  88         amount_cell($customer_record["Overdue2"]);
  89         amount_cell($customer_record["Balance"]);
  90         end_row();
  91
  92         end_table();
  93 }
  94 //------------------------------------------------------------------------------------------------
  95
  96 div_start('totals_tbl');
  97 if ($_POST['customer_id'] != "" && $_POST['customer_id'] != ALL_TEXT)
  98 {
  99         $customer_record = get_customer_details($_POST['customer_id'], $_POST['TransToDate']);
 100     display_customer_summary($customer_record);
 101     echo "<br>";
 102 }
 103 div_end();
 104
 105 if(get_post('RefreshInquiry'))
 106 {
 107         $Ajax->activate('totals_tbl');
 108 }
 109 //------------------------------------------------------------------------------------------------
 110
 111 function systype_name($dummy, $type)
 112 {
 113         global $systypes_array;
 114
 115         return $systypes_array[$type];
 116 }
 117
 118 function order_view($row)
 119 {
 120         return $row['order_']>0 ?
 121                 get_customer_trans_view_str(ST_SALESORDER, $row['order_'])
 122                 : "";
 123 }
 124
 125 function trans_view($trans)
 126 {
 127         return get_trans_view_str($trans["type"], $trans["trans_no"]);
 128 }
 129
 130 function due_date($row)
 131 {
 132         return  $row["type"] == ST_SALESINVOICE ? $row["due_date"] : '';
 133 }
 134
 135 function gl_view($row)
 136 {
 137         return get_gl_view_str($row["type"], $row["trans_no"]);
 138 }
 139
 140 function fmt_debit($row)
 141 {
 142         $value =
 143             $row['type']==ST_CUSTCREDIT || $row['type']==ST_CUSTPAYMENT || $row['type']==ST_BANKDEPOSIT ?
 144                 -$row["TotalAmount"] : $row["TotalAmount"];
 145         return $value>=0 ? price_format($value) : '';
 146
 147 }
 148
 149 function fmt_credit($row)
 150 {
 151         $value =
 152             !($row['type']==ST_CUSTCREDIT || $row['type']==ST_CUSTPAYMENT || $row['type']==ST_BANKDEPOSIT) ?
 153                 -$row["TotalAmount"] : $row["TotalAmount"];
 154         return $value>0 ? price_format($value) : '';
 155 }
 156
 157 function credit_link($row)
 158 {
 159         return $row['type'] == ST_SALESINVOICE && $row["TotalAmount"] - $row["Allocated"] > 0 ?
 160                 pager_link(_("Credit This"),
 161                         "/sales/customer_credit_invoice.php?InvoiceNumber=".
 162                         $row['trans_no'], ICON_CREDIT)
 163                         : '';
 164 }
 165
 166 function edit_link($row)
 167 {
 168         $str = '';
 169
 170         switch($row['type']) {
 171         case ST_SALESINVOICE:
 172                 if (get_voided_entry(ST_SALESINVOICE, $row["trans_no"]) === false && $row['Allocated'] == 0)
 173                         $str = "/sales/customer_invoice.php?ModifyInvoice=".$row['trans_no'];
 174                 break;
 175         case ST_CUSTCREDIT:
 176                 if (get_voided_entry(ST_CUSTCREDIT, $row["trans_no"]) === false && $row['Allocated'] == 0) // 2008-11-19 Joe Hunt
 177                 {
 178                         if ($row['order_']==0) // free-hand credit note
 179                             $str = "/sales/credit_note_entry.php?ModifyCredit=".$row['trans_no'];
 180                         else    // credit invoice
 181                             $str = "/sales/customer_credit_invoice.php?ModifyCredit=".$row['trans_no'];
 182                 }
 183                 break;
 184          case ST_CUSTDELIVERY:
 185                 if (get_voided_entry(ST_CUSTDELIVERY, $row["trans_no"]) === false)
 186                         $str = "/sales/customer_delivery.php?ModifyDelivery=".$row['trans_no'];
 187                 break;
 188         }
 189         if ($str != "" && !is_closed_trans($row['type'], $row["trans_no"]))
 190                 return pager_link(_('Edit'), $str, ICON_EDIT);
 191         return '';
 192 }
 193
 194 function prt_link($row)
 195 {
 196         if ($row['type'] != ST_CUSTPAYMENT && $row['type'] != ST_BANKDEPOSIT) // customer payment or bank deposit printout not defined yet.
 197                 return print_document_link($row['trans_no'], _("Print"), true, $row['type'], ICON_PRINT);
 198 }
 199
 200 function check_overdue($row)
 201 {
 202         return $row['OverDue'] == 1
 203                 && (abs($row["TotalAmount"]) - $row["Allocated"] != 0);
 204 }
 205 //------------------------------------------------------------------------------------------------
 206     $date_after = date2sql($_POST['TransAfterDate']);
 207     $date_to = date2sql($_POST['TransToDate']);
 208
 209   $sql = "SELECT
 210                 trans.type,
 211                 trans.trans_no,
 212                 trans.order_,
 213                 trans.reference,
 214                 trans.tran_date,
 215                 trans.due_date,
 216                 debtor.name,
 217                 branch.br_name,
 218                 debtor.curr_code,
 219                 (trans.ov_amount + trans.ov_gst + trans.ov_freight
 220                         + trans.ov_freight_tax + trans.ov_discount)     AS TotalAmount, ";
 221         if ($_POST['filterType'] != ALL_TEXT)
 222                 $sql .= "@bal := @bal+(trans.ov_amount + trans.ov_gst + trans.ov_freight + trans.ov_freight_tax + trans.ov_discount), ";
 223
 224 //      else
 225 //              $sql .= "IF(trans.type=".ST_CUSTDELIVERY.",'', IF(trans.type=".ST_SALESINVOICE." OR trans.type=".ST_BANKPAYMENT.",@bal := @bal+
 226 //                      (trans.ov_amount + trans.ov_gst + trans.ov_freight + trans.ov_freight_tax + trans.ov_discount), @bal := @bal-
 227 //                      (trans.ov_amount + trans.ov_gst + trans.ov_freight + trans.ov_freight_tax + trans.ov_discount))) , ";
 228                 $sql .= "trans.alloc AS Allocated,
 229                 ((trans.type = ".ST_SALESINVOICE.")
 230                         AND trans.due_date < '" . date2sql(Today()) . "') AS OverDue
 231                 FROM "
 232                         .TB_PREF."debtor_trans as trans, "
 233                         .TB_PREF."debtors_master as debtor, "
 234                         .TB_PREF."cust_branch as branch
 235                 WHERE debtor.debtor_no = trans.debtor_no
 236                         AND trans.tran_date >= '$date_after'
 237                         AND trans.tran_date <= '$date_to'
 238                         AND trans.branch_code = branch.branch_code";
 239
 240         if ($_POST['customer_id'] != ALL_TEXT)
 241                 $sql .= " AND trans.debtor_no = ".db_escape($_POST['customer_id']);
 242
 243         if ($_POST['filterType'] != ALL_TEXT)
 244         {
 245                 if ($_POST['filterType'] == '1')
 246                 {
 247                         $sql .= " AND (trans.type = ".ST_SALESINVOICE." OR trans.type = ".ST_BANKPAYMENT.") ";
 248                 }
 249                 elseif ($_POST['filterType'] == '2')
 250                 {
 251                         $sql .= " AND (trans.type = ".ST_SALESINVOICE.") ";
 252                 }
 253                 elseif ($_POST['filterType'] == '3')
 254                 {
 255                         $sql .= " AND (trans.type = " . ST_CUSTPAYMENT
 256                                         ." OR trans.type = ".ST_BANKDEPOSIT.") ";
 257                 }
 258                 elseif ($_POST['filterType'] == '4')
 259                 {
 260                         $sql .= " AND trans.type = ".ST_CUSTCREDIT." ";
 261                 }
 262                 elseif ($_POST['filterType'] == '5')
 263                 {
 264                         $sql .= " AND trans.type = ".ST_CUSTDELIVERY." ";
 265                 }
 266
 267         if ($_POST['filterType'] == '2')
 268         {
 269                 $today =  date2sql(Today());
 270                 $sql .= " AND trans.due_date < '$today'
 271                                 AND (trans.ov_amount + trans.ov_gst + trans.ov_freight_tax +
 272                                 trans.ov_freight + trans.ov_discount - trans.alloc > 0) ";
 273         }
 274         }
 275
 276 //------------------------------------------------------------------------------------------------
 277 db_query("set @bal:=0");
 278
 279 $cols = array(
 280         _("Type") => array('fun'=>'systype_name', 'ord'=>''),
 281         _("#") => array('fun'=>'trans_view', 'ord'=>''),
 282         _("Order") => array('fun'=>'order_view'),
 283         _("Reference"),
 284         _("Date") => array('name'=>'tran_date', 'type'=>'date', 'ord'=>'desc'),
 285         _("Due Date") => array('type'=>'date', 'fun'=>'due_date'),
 286         _("Customer") => array('ord'=>''),
 287         _("Branch") => array('ord'=>''),
 288         _("Currency") => array('align'=>'center'),
 289         _("Debit") => array('align'=>'right', 'fun'=>'fmt_debit'),
 290         _("Credit") => array('align'=>'right','insert'=>true, 'fun'=>'fmt_credit'),
 291         _("RB") => array('align'=>'right', 'type'=>'amount'),
 292                 array('insert'=>true, 'fun'=>'gl_view'),
 293                 array('insert'=>true, 'fun'=>'credit_link'),
 294                 array('insert'=>true, 'fun'=>'edit_link'),
 295                 array('insert'=>true, 'fun'=>'prt_link')
 296         );
 297
 298
 299 if ($_POST['customer_id'] != ALL_TEXT) {
 300         $cols[_("Customer")] = 'skip';
 301         $cols[_("Currency")] = 'skip';
 302 }
 303 if ($_POST['filterType'] == ALL_TEXT)
 304         $cols[_("RB")] = 'skip';
 305
 306 $table =& new_db_pager('trans_tbl', $sql, $cols);
 307 $table->set_marker('check_overdue', _("Marked items are overdue."));
 308
 309 $table->width = "85%";
 310
 311 display_db_pager($table);
 312
 313 end_form();
 314 end_page();
 315
 316 ?>