sales/inquiry/customer_inquiry.php

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