1<?php
2
3include('includes/session.php');
4include('includes/phplot/phplot.php');
5$Title=_('Sales Report Graph');
6
7$ViewTopic = 'ARInquiries';
8$BookMark = 'SalesGraph';
9
10include('includes/header.php');
11
12$SelectADifferentPeriod ='';
13
14if (isset($_POST['FromPeriod']) AND isset($_POST['ToPeriod'])){
15
16	if ($_POST['FromPeriod'] > $_POST['ToPeriod']){
17		prnMsg(_('The selected period from is actually after the period to! Please re-select the reporting period'),'error');
18		$SelectADifferentPeriod =_('Select A Different Period');
19	}
20/*	There is no PHPlot reason to restrict the graph to 12 months...
21	if ($_POST['ToPeriod'] - $_POST['FromPeriod'] >12){
22		prnMsg(_('The selected period range is more than 12 months - only graphs for a period less than 12 months can be created'),'error');
23		$SelectADifferentPeriod= _('Select A Different Period');
24	}
25*/	if ((!isset($_POST['ValueFrom']) OR $_POST['ValueFrom']='' OR !isset($_POST['ValueTo']) OR $_POST['ValueTo']='') AND $_POST['GraphOn'] !='All'){
26		prnMsg(_('For graphs including either a customer or item range - the range must be specified. Please enter the value from and the value to for the range'),'error');
27		$SelectADifferentPeriod= _('Select A Different Period');
28	}
29}
30
31if ((! isset($_POST['FromPeriod']) OR ! isset($_POST['ToPeriod']))
32	OR $SelectADifferentPeriod==_('Select A Different Period')){
33
34	echo '<form method="post" action="' . htmlspecialchars($_SERVER['PHP_SELF'],ENT_QUOTES,'UTF-8') . '">';
35    echo '<div>';
36	echo '<input type="hidden" name="FormID" value="' . $_SESSION['FormID'] . '" />';
37
38	echo '<p class="page_title_text"><img src="'.$RootPath.'/css/'.$Theme.'/images/maintenance.png" title="' . _('Search') . '" alt="" />' . ' ' . $Title . '</p>';
39
40	echo '<table class="selection">
41			<tr><td>' . _('Select Period From') . ':</td>
42			<td><select name="FromPeriod">';
43
44	if (Date('m') > $_SESSION['YearEnd']){
45		/*Dates in SQL format */
46		$DefaultFromDate = Date ('Y-m-d', Mktime(0,0,0,$_SESSION['YearEnd'] + 2,0,Date('Y')));
47	} else {
48		$DefaultFromDate = Date ('Y-m-d', Mktime(0,0,0,$_SESSION['YearEnd'] + 2,0,Date('Y')-1));
49	}
50	$sql = "SELECT periodno, lastdate_in_period FROM periods ORDER BY periodno";
51	$Periods = DB_query($sql);
52
53	while ($myrow=DB_fetch_array($Periods)){
54		if(isset($_POST['FromPeriod']) AND $_POST['FromPeriod']!=''){
55			if( $_POST['FromPeriod']== $myrow['periodno']){
56				echo '<option selected="selected" value="' . $myrow['periodno'] . '">' .MonthAndYearFromSQLDate($myrow['lastdate_in_period']) . '</option>';
57			} else {
58				echo '<option value="' . $myrow['periodno'] . '">' . MonthAndYearFromSQLDate($myrow['lastdate_in_period']) . '</option>';
59			}
60		} else {
61			if($myrow['lastdate_in_period']==$DefaultFromDate){
62				echo '<option selected="selected" value="' . $myrow['periodno'] . '">' . MonthAndYearFromSQLDate($myrow['lastdate_in_period']) . '</option>';
63			} else {
64				echo '<option value="' . $myrow['periodno'] . '">' . MonthAndYearFromSQLDate($myrow['lastdate_in_period']) . '</option>';
65			}
66		}
67	}
68
69	echo '</select></td></tr>';
70	if (!isset($_POST['ToPeriod']) OR $_POST['ToPeriod']==''){
71		$DefaultToPeriod = GetPeriod(DateAdd(ConvertSQLDate($DefaultFromDate),'m',11));
72	} else {
73		$DefaultToPeriod = $_POST['ToPeriod'];
74	}
75
76	echo '<tr>
77			<td>' . _('Select Period To')  . ':</td>
78			<td><select name="ToPeriod">';
79
80	$RetResult = DB_data_seek($Periods,0);
81
82	while ($myrow=DB_fetch_array($Periods)){
83
84		if($myrow['periodno']==$DefaultToPeriod){
85			echo '<option selected="selected" value="' . $myrow['periodno'] . '">' . MonthAndYearFromSQLDate($myrow['lastdate_in_period']) . '</option>';
86		} else {
87			echo '<option value ="' . $myrow['periodno'] . '">' . MonthAndYearFromSQLDate($myrow['lastdate_in_period']) . '</option>';
88		}
89	}
90	echo '</select></td></tr>';
91
92	echo '<tr>
93			<td>
94				<h3>', _('OR'), '</h3>
95			</td>
96		</tr>';
97
98	if (!isset($_POST['Period'])) {
99		$_POST['Period'] = '';
100	}
101
102	echo '<tr>
103			<td>', _('Select Period'), '</td>
104			<td>', ReportPeriodList($_POST['Period'], array('l', 't')), '</td>
105		</tr>';
106
107	$AreasResult = DB_query("SELECT areacode, areadescription FROM areas ORDER BY areadescription");
108
109	if (!isset($_POST['SalesArea'])){
110		$_POST['SalesArea']='';
111	}
112	echo '<tr>
113			<td>' . _('For Sales Area/Region:')  . '</td>
114			<td><select name="SalesArea">';
115	if($_POST['SalesArea']=='All'){
116		echo '<option selected="selected" value="All">' . _('All') . '</option>';
117	} else {
118		echo '<option value="All">' . _('All') . '</option>';
119	}
120	while ($myrow=DB_fetch_array($AreasResult)){
121		if($myrow['areacode']==$_POST['SalesArea']){
122			echo '<option selected="selected" value="' . $myrow['areacode'] . '">' . $myrow['areadescription'] . '</option>';
123		} else {
124			echo '<option value="' . $myrow['areacode'] . '">' . $myrow['areadescription'] . '</option>';
125		}
126	}
127	echo '</select></td></tr>';
128
129	$CategoriesResult = DB_query("SELECT categoryid, categorydescription FROM stockcategory ORDER BY categorydescription");
130
131	if (!isset($_POST['CategoryID'])){
132		$_POST['CategoryID']='';
133	}
134	echo '<tr>
135			<td>' . _('For Stock Category')  . ':</td>
136			<td><select name="CategoryID">';
137	if($_POST['CategoryID']=='All'){
138		echo '<option selected="selected" value="All">' . _('All') . '</option>';
139	} else {
140		echo '<option value="All">' . _('All') . '</option>';
141	}
142	while ($myrow=DB_fetch_array($CategoriesResult)){
143		if($myrow['categoryid']==$_POST['CategoryID']){
144			echo '<option selected="selected" value="' . $myrow['categoryid'] . '">' . $myrow['categorydescription'] . '</option>';
145		} else {
146			echo '<option value="' . $myrow['categoryid'] . '">' . $myrow['categorydescription'] . '</option>';
147		}
148	}
149	echo '</select></td></tr>';
150
151	$SalesFolkResult = DB_query("SELECT salesmancode, salesmanname FROM salesman ORDER BY salesmanname");
152
153	if (! isset($_POST['SalesmanCode'])){
154 		$_POST['SalesmanCode'] = '';
155	}
156
157	echo '<tr>
158			<td>' . _('For Salesperson:') . '</td>
159			<td><select name="SalesmanCode">';
160
161	if($_POST['SalesmanCode']=='All'){
162		echo '<option selected="selected" value="All">' . _('All') . '</option>';
163	} else {
164		echo '<option value="All">' . _('All') . '</option>';
165	}
166	while ($myrow=DB_fetch_array($SalesFolkResult)){
167		if ($myrow['salesmancode']== $_POST['SalesmanCode']){
168			echo '<option selected="selected" value="' . $myrow['salesmancode'] . '">' . $myrow['salesmanname'] . '</option>';
169		} else {
170			echo '<option value="' . $myrow['salesmancode'] . '">' . $myrow['salesmanname'] . '</option>';
171		}
172	}
173	echo '</select></td>
174			<td>' . $_POST['SalesmanCode'] . '</td>
175		</tr>';
176
177	echo '<tr>
178			<td>' . _('Graph Type') . '</td>
179			<td><select name="GraphType">
180				<option value="bars">' . _('Bar Graph') . '</option>
181				<option value="stackedbars">' . _('Stacked Bar Graph') . '</option>
182				<option value="lines">' . _('Line Graph') . '</option>
183				<option value="linepoints">' . _('Line Point Graph') . '</option>
184				<option value="area">' . _('Area Graph') . '</option>
185				<option value="points">' . _('Points Graph') . '</option>
186				<option value="pie">' . _('Pie Graph') . '</option>
187				<option value="thinbarline">' . _('Thin Bar Line Graph') . '</option>
188				<option value="squared">' . _('Squared Graph') . '</option>
189				<option value="stackedarea">' . _('Stacked Area Graph') . '</option>
190				</select></td>
191			</tr>';
192
193	if (!isset($_POST['ValueFrom'])){
194		$_POST['ValueFrom']='';
195	}
196	if (!isset($_POST['ValueTo'])){
197		$_POST['ValueTo']='';
198	}
199	echo '<tr><td>' . _('Graph On:') . '</td><td>
200			<input type="radio" name="GraphOn" value="All" checked="checked" />' . _('All') . '<br />
201			<input type="radio" name="GraphOn" value="Customer" />' . _('Customer') . '<br />
202			<input type="radio" name="GraphOn" value="StockID" />' . _('Item Code') . '</td></tr>';
203	echo '<tr><td>' . _('From:') . ' <input type="text" name="ValueFrom" value="' . $_POST['ValueFrom'] . '" /></td>
204	 		<td>' . _('To:') . ' <input type="text" name="ValueTo" value="' . $_POST['ValueTo'] . '" /></td></tr>';
205
206	echo '<tr>
207			<td>' . _('Graph Value:') . '</td>
208			<td><input type="radio" name="GraphValue" value="Net" checked="checked" />' . _('Net Sales Value') . '<br />
209				<input type="radio" name="GraphValue" value="GP" />' . _('Gross Profit') . '<br />
210				<input type="radio" name="GraphValue" value="Quantity" />' . _('Quantity') . '</td>
211			</tr>
212			</table>
213		<br />
214			<div class="centre"><input type="submit" name="ShowGraph" value="' . _('Show Sales Graph') .'" /></div>
215		</div>
216        </form>';
217	include('includes/footer.php');
218} else {
219
220	$graph = new PHPlot(950,450);
221	$SelectClause ='';
222	$WhereClause ='';
223	$GraphTitle ='';
224	if ($_POST['GraphValue']=='Net') {
225		$GraphTitle = _('Sales Value');
226		$SelectClause = 'amt - disc';
227	} elseif ($_POST['GraphValue']=='GP'){
228		$GraphTitle = _('Gross Profit');
229		$SelectClause = '(amt - disc - cost)';
230	} else {
231		$GraphTitle = _('Unit Sales');
232		$SelectClause = 'qty';
233	}
234
235	if ($_POST['Period'] != '') {
236		$_POST['FromPeriod'] = ReportPeriod($_POST['Period'], 'From');
237		$_POST['ToPeriod'] = ReportPeriod($_POST['Period'], 'To');
238	}
239
240	$SQL = "SELECT YEAR(`lastdate_in_period`) AS year, MONTHNAME(`lastdate_in_period`) AS month
241			  FROM `periods`
242			 WHERE `periodno`='" . $_POST['FromPeriod'] . "' OR periodno='" . $_POST['ToPeriod'] . "'";
243
244	$result = DB_query($SQL);
245
246	$fromPeriod = DB_fetch_array($result);
247	$starting = $fromPeriod['month'] . ' ' . $fromPeriod['year'];
248
249	$toPeriod = DB_fetch_array($result);
250	$ending = $toPeriod['month'] . ' ' . $toPeriod['year'];
251
252	$GraphTitle .= ' ' . _('From Period') . ' ' . $starting . ' ' . _('to') . ' ' . $ending . "\n\r";
253
254	if ($_POST['SalesArea']=='All'){
255		$GraphTitle .= ' ' . _('For All Sales Areas');
256	} else {
257		$result = DB_query("SELECT areadescription FROM areas WHERE areacode='" . $_POST['SalesArea'] . "'");
258		$myrow = DB_fetch_row($result);
259		$GraphTitle .= ' ' . _('For') . ' ' . $myrow[0];
260		$WhereClause .= " area='" . $_POST['SalesArea'] . "' AND";
261	}
262	if ($_POST['CategoryID']=='All'){
263		$GraphTitle .= ' ' . _('For All Stock Categories');
264	} else {
265		$result = DB_query("SELECT categorydescription FROM stockcategory WHERE categoryid='" . $_POST['CategoryID'] . "'");
266		$myrow = DB_fetch_row($result);
267		$GraphTitle .= ' ' . _('For') . ' ' . $myrow[0];
268		$WhereClause .= " stkcategory='" . $_POST['CategoryID'] . "' AND";
269
270	}
271	if ($_POST['SalesmanCode']=='All'){
272		$GraphTitle .= ' ' . _('For All Salespeople');
273	} else {
274		$result = DB_query("SELECT salesmanname FROM salesman WHERE salesmancode='" . $_POST['SalesmanCode'] . "'");
275		$myrow = DB_fetch_row($result);
276		$GraphTitle .= ' ' . _('For Salesperson:') . ' ' . $myrow[0];
277		$WhereClause .= " salesperson='" . $_POST['SalesmanCode'] . "' AND";
278
279	}
280	if ($_POST['GraphOn']=='Customer'){
281		$GraphTitle .= ' ' . _('For Customers from') . ' ' . $_POST['ValueFrom'] . ' ' . _('to') . ' ' . $_POST['ValueTo'];
282		$WhereClause .= "  cust >='" . $_POST['ValueFrom'] . "' AND cust <='" . $_POST['ValueTo'] . "' AND";
283	}
284	if ($_POST['GraphOn']=='StockID'){
285		$GraphTitle .= ' ' . _('For Items from') . ' ' . $_POST['ValueFrom'] . ' ' . _('to') . ' ' . $_POST['ValueTo'];
286		$WhereClause .= "  stockid >='" . $_POST['ValueFrom'] . "' AND stockid <='" . $_POST['ValueTo'] . "' AND";
287	}
288
289	$WhereClause = "WHERE " . $WhereClause . " salesanalysis.periodno>='" . $_POST['FromPeriod'] . "' AND salesanalysis.periodno <= '" . $_POST['ToPeriod'] . "'";
290
291	$SQL = "SELECT salesanalysis.periodno,
292				periods.lastdate_in_period,
293				SUM(CASE WHEN budgetoractual=1 THEN " . $SelectClause . " ELSE 0 END) AS sales,
294				SUM(CASE WHEN  budgetoractual=0 THEN " . $SelectClause . " ELSE 0 END) AS budget
295		FROM salesanalysis INNER JOIN periods ON salesanalysis.periodno=periods.periodno " . $WhereClause . "
296		GROUP BY salesanalysis.periodno,
297			periods.lastdate_in_period
298		ORDER BY salesanalysis.periodno";
299
300
301	$graph->SetTitle($GraphTitle);
302	$graph->SetTitleColor('blue');
303	$graph->SetOutputFile('companies/' .$_SESSION['DatabaseName'] .  '/reports/salesgraph.png');
304	$graph->SetXTitle(_('Month'));
305	if ($_POST['GraphValue']=='Net'){
306		$graph->SetYTitle(_('Sales Value'));
307	} elseif ($_POST['GraphValue']=='GP'){
308		$graph->SetYTitle(_('Gross Profit'));
309	} else {
310		$graph->SetYTitle(_('Quantity'));
311	}
312	$graph->SetXTickPos('none');
313	$graph->SetXTickLabelPos('none');
314	$graph->SetXLabelAngle(90);
315	$graph->SetBackgroundColor('white');
316	$graph->SetTitleColor('blue');
317	$graph->SetFileFormat('png');
318	$graph->SetPlotType($_POST['GraphType']);
319	$graph->SetIsInline('1');
320	$graph->SetShading(5);
321	$graph->SetDrawYGrid(TRUE);
322	$graph->SetDataType('text-data');
323	$graph->SetNumberFormat($DecimalPoint, $ThousandsSeparator);
324	$graph->SetPrecisionY($_SESSION['CompanyRecord']['decimalplaces']);
325
326	$SalesResult = DB_query($SQL);
327	if (DB_error_no() !=0) {
328
329		prnMsg(_('The sales graph data for the selected criteria could not be retrieved because') . ' - ' . DB_error_msg(),'error');
330		include('includes/footer.php');
331		exit;
332	}
333	if (DB_num_rows($SalesResult)==0){
334		prnMsg(_('There is not sales data for the criteria entered to graph'),'info');
335		include('includes/footer.php');
336		exit;
337	}
338
339	$GraphArray = array();
340	$i = 0;
341	while ($myrow = DB_fetch_array($SalesResult)){
342		$GraphArray[$i] = array(MonthAndYearFromSQLDate($myrow['lastdate_in_period']),$myrow['sales'],$myrow['budget']);
343		$i++;
344	}
345
346	$graph->SetDataValues($GraphArray);
347	$graph->SetDataColors(
348		array('grey','wheat'),  //Data Colors
349		array('black')	//Border Colors
350	);
351	$graph->SetLegend(array(_('Actual'),_('Budget')));
352	$graph->SetYDataLabelPos('plotin');
353
354	//Draw it
355	$graph->DrawGraph();
356	echo '<table class="selection">
357			<tr>
358				<td><p><img src="companies/' .$_SESSION['DatabaseName'] .  '/reports/salesgraph.png" alt="Sales Report Graph"></img></p></td>
359			</tr>
360		  </table>';
361	include('includes/footer.php');
362}
363?>
364