1<?php
2/*This page adds the total of allocation records and compares this to the recorded allocation total in DebtorTrans table */
3
4include('includes/session.php');
5$Title = _('Customer Allocations != DebtorTrans.Alloc');
6include('includes/header.php');
7
8/*First off get the DebtorTransID of all invoices where allocations dont agree to the recorded allocation */
9$sql = "SELECT debtortrans.id,
10		debtortrans.debtorno,
11		debtortrans.transno,
12		ovamount+ovgst AS totamt,
13		SUM(custallocns.Amt) AS totalalloc,
14		debtortrans.alloc
15	FROM debtortrans INNER JOIN custallocns
16	ON debtortrans.id=custallocns.transid_allocto
17	WHERE debtortrans.type=10
18	GROUP BY debtortrans.ID,
19		debtortrans.type,
20		ovamount+ovgst,
21		debtortrans.alloc
22	HAVING SUM(custallocns.amt) < debtortrans.alloc - 1";
23
24$result = DB_query($sql);
25
26if (DB_num_rows($result)==0){
27	prnMsg(_('There are no inconsistencies with allocations') . ' - ' . _('all is well'),'info');
28}
29
30while ($myrow = DB_fetch_array($result)){
31	$AllocToID = $myrow['id'];
32
33	echo '<br />' . _('Allocations made against') . ' ' . $myrow['debtorno'] . ' ' . _('Invoice Number') . ': ' . $myrow['transno'];
34	echo '<br />' . _('Original Invoice Total') . ': '. $myrow['totamt'];
35	echo '<br />' . _('Total amount recorded as allocated against it') . ': ' . $myrow['alloc'];
36	echo '<br />' . _('Total of allocation records') . ': ' . $myrow['totalalloc'];
37
38	$sql = "SELECT type,
39				transno,
40				trandate,
41				debtortrans.debtorno,
42				reference,
43				debtortrans.rate,
44				ovamount+ovgst+ovfreight+ovdiscount AS totalamt,
45				custallocns.amt,
46				decimalplaces AS currdecimalplaces
47			FROM debtortrans INNER JOIN custallocns
48			ON debtortrans.id=custallocns.transid_allocfrom
49			INNER JOIN debtorsmaster ON
50			debtortrans.debtorno=debtorsmaster.debtorno
51			INNER JOIN currencies ON
52			debtorsmaster.currcode=currencies.currabrev
53			WHERE custallocns.transid_allocto='" . $AllocToID . "'";
54
55	$ErrMsg = _('The customer transactions for the selected criteria could not be retrieved because');
56	$TransResult = DB_query($sql,$ErrMsg);
57
58	echo '<table class="selection">';
59
60	$tableheader = '<tr>
61				<th>' . _('Type') . '</th>
62				<th>' . _('Number') . '</th>
63				<th>' . _('Reference') . '</th>
64				<th>' . _('Ex Rate') . '</th>
65				<th>' . _('Amount') . '</th>
66				<th>' . _('Alloc') . '</th></tr>';
67	echo $tableheader;
68
69	$RowCounter = 1;
70	$AllocsTotal = 0;
71
72	while ($myrow1=DB_fetch_array($TransResult)) {
73
74		if ($myrow1['type']==11){
75			$TransType = _('Credit Note');
76		} else {
77			$TransType = _('Receipt');
78		}
79		$CurrDecimalPlaces = $myrow1['currdecimalplaces'];
80
81		printf( '<tr class="striped_row">
82				<td>%s</td>
83				<td>%s</td>
84				<td>%s</td>
85				<td>%s</td>
86				<td class="number">%s</td>
87				<td class="number">%s</td>
88				</tr>',
89				$TransType,
90				$myrow1['transno'],
91				$myrow1['reference'],
92				locale_number_format($myrow1['exrate'],4),
93				locale_number_format($myrow1['totalamt'],$CurrDecimalPlaces),
94				locale_number_format($myrow1['amt'],$CurrDecimalPlaces));
95
96		$RowCounter++;
97		If ($RowCounter == 12){
98			$RowCounter=1;
99			echo $tableheader;
100		}
101		//end of page full new headings if
102		$AllocsTotal +=$myrow1['amt'];
103	}
104	//end of while loop
105	echo '<tr><td colspan="6" class="number">' . locale_number_format($AllocsTotal,$CurrDecimalPlaces) . '</td></tr>';
106	echo '</table>
107		<br />';
108}
109
110include('includes/footer.php');
111
112?>