5 include($path_to_root . "/includes/db_pager.inc");
6 include_once($path_to_root . "/includes/session.inc");
8 include_once($path_to_root . "/sales/includes/sales_ui.inc");
9 include_once($path_to_root . "/sales/includes/sales_db.inc");
12 if ($use_popup_windows)
13 $js .= get_js_open_window(900, 500);
15 $js .= get_js_date_picker();
16 page(_("Customer Allocation Inquiry"), false, false, "", $js);
18 if (isset($_GET['customer_id']))
20 $_POST['customer_id'] = $_GET['customer_id'];
23 //------------------------------------------------------------------------------------------------
25 if (!isset($_POST['customer_id']))
26 $_POST['customer_id'] = get_global_customer();
28 start_form(false, true);
30 start_table("class='tablestyle_noborder'");
33 customer_list_cells(_("Select a customer: "), 'customer_id', $_POST['customer_id'], true);
35 date_cells(_("from:"), 'TransAfterDate', '', null, -30);
36 date_cells(_("to:"), 'TransToDate', '', null, 1);
38 cust_allocations_list_cells(_("Type:"), 'filterType', null);
40 check_cells(" " . _("show settled:"), 'showSettled', null);
42 submit_cells('RefreshInquiry', _("Search"),'',_('Refresh Inquiry'), true);
44 set_global_customer($_POST['customer_id']);
50 //------------------------------------------------------------------------------------------------
51 function check_overdue($row)
53 return ($row['OverDue'] == 1
54 && (abs($row["TotalAmount"]) - $row["Allocated"] != 0));
57 function order_link($row)
59 return $row['order_']>0 ?
60 get_customer_trans_view_str(systypes::sales_order(), $row['order_'])
64 function systype_name($dummy, $type)
66 return systypes::name($type);
69 function view_link($trans)
71 return get_trans_view_str($trans["type"], $trans["trans_no"]);
74 function due_date($row)
76 return $row["type"] == 10 ? sql2date($row["due_date"]) : '';
79 function fmt_balance($row)
81 return price_format($row["TotalAmount"] - $row["Allocated"]);
84 function alloc_link($row)
87 pager_link(_("Allocation"),
88 "/sales/allocations/customer_allocate.php?trans_no=" . $row["trans_no"]
89 ."&trans_type=" . $row["type"]);
91 if ($row["type"] == 11 && $row['TotalAmount'] > 0)
93 /*its a credit note which could have an allocation */
96 elseif (($row["type"] == systypes::cust_payment() || $row["type"] == systypes::bank_deposit()) &&
97 ($row['TotalAmount'] - $row['Allocated']) > 0)
99 /*its a receipt which could have an allocation*/
102 elseif ($row["type"] == systypes::cust_payment() && $row['TotalAmount'] < 0)
104 /*its a negative receipt */
109 function fmt_debit($row)
112 $row['type']==11 || $row['type']==12 || $row['type']==2 ?
113 -$row["TotalAmount"] : $row["TotalAmount"];
114 return $value>=0 ? price_format($value) : '';
118 function fmt_credit($row)
121 !($row['type']==11 || $row['type']==12 || $row['type']==2) ?
122 -$row["TotalAmount"] : $row["TotalAmount"];
123 return $value>0 ? price_format($value) : '';
125 //------------------------------------------------------------------------------------------------
127 $data_after = date2sql($_POST['TransAfterDate']);
128 $date_to = date2sql($_POST['TransToDate']);
139 (trans.ov_amount + trans.ov_gst + trans.ov_freight
140 + trans.ov_freight_tax + trans.ov_discount) AS TotalAmount,
141 trans.alloc AS Allocated,
143 AND trans.due_date < '" . date2sql(Today()) . "') AS OverDue
145 .TB_PREF."debtor_trans as trans, "
146 .TB_PREF."debtors_master as debtor
147 WHERE debtor.debtor_no = trans.debtor_no
148 AND (trans.ov_amount + trans.ov_gst + trans.ov_freight
149 + trans.ov_freight_tax + trans.ov_discount != 0)
150 AND trans.tran_date >= '$data_after'
151 AND trans.tran_date <= '$date_to'";
153 if ($_POST['customer_id'] != reserved_words::get_all())
154 $sql .= " AND trans.debtor_no = '" . $_POST['customer_id'] . "'";
156 if (isset($_POST['filterType']) && $_POST['filterType'] != reserved_words::get_all())
158 if ($_POST['filterType'] == '1' || $_POST['filterType'] == '2')
160 $sql .= " AND trans.type = 10 ";
162 elseif ($_POST['filterType'] == '3')
164 $sql .= " AND trans.type = " . systypes::cust_payment();
166 elseif ($_POST['filterType'] == '4')
168 $sql .= " AND trans.type = 11 ";
171 if ($_POST['filterType'] == '2')
173 $today = date2sql(Today());
174 $sql .= " AND trans.due_date < '$today'
175 AND (round(abs(trans.ov_amount + "
176 ."trans.ov_gst + trans.ov_freight + "
177 ."trans.ov_freight_tax + trans.ov_discount) - trans.alloc,6) > 0) ";
181 $sql .= " AND trans.type != 13 ";
185 if (!check_value('showSettled'))
187 $sql .= " AND (round(abs(trans.ov_amount + trans.ov_gst + "
188 ."trans.ov_freight + trans.ov_freight_tax + "
189 ."trans.ov_discount) - trans.alloc,6) != 0) ";
192 // $sql .= " ORDER BY trans.tran_date";
195 if (db_num_rows($result) == 0)
197 display_note(_("The selected customer has no transactions for the given dates."), 1, 1);
200 // start_table("$table_style width='80%'");
201 //------------------------------------------------------------------------------------------------
203 _("Type") => array('type'=>'spec', 'fun'=>'systype_name'),
204 _("Number") => array('type'=>'spec', 'fun'=>'view_link'),
206 _("Order") => array('type'=>'spec', 'fun'=>'order_link'),
207 _("Date") => array('type'=>'date', 'ord'=>'asc'),
208 _("Due Date") => array('type'=>'spec', 'fun'=>'due_date'),
209 _("Customer") => 'text',
210 _("Currency") => 'text',
211 _("Debit") => array('type'=>'spec', 'fun'=>'fmt_debit'),
212 _("Credit") => array('type'=>'insert', 'fun'=>'fmt_credit'),
213 _("Allocated") => 'amount',
214 _("Balance") => array('type'=>'insert', 'fun'=>'fmt_balance'),
215 array('type'=>'insert', 'fun'=>'alloc_link')
218 if ($_POST['customer_id'] != reserved_words::get_all()) {
219 $cols[_("Customer")] = 'skip';
220 $cols[_("Currency")] = 'skip';
223 $table =& new_db_pager('doc_tbl', $sql, $cols);
224 $table->set_marker('check_overdue', _("Marked items are overdue."));
227 if(get_post('RefreshInquiry'))
229 $table->set_sql($sql);
230 $table->set_columns($cols);
231 $Ajax->activate('doc_tbl');
235 display_db_pager($table);