From: <dai...@us...> - 2011-05-25 10:30:55
|
Revision: 4574 http://web-erp.svn.sourceforge.net/web-erp/?rev=4574&view=rev Author: daintree Date: 2011-05-25 10:30:45 +0000 (Wed, 25 May 2011) Log Message: ----------- new sales inquiries Modified Paths: -------------- trunk/COGSGLPostings.php trunk/PeriodsInquiry.php trunk/SalesGLPostings.php trunk/UpgradeDatabase.php trunk/build/make_release.sh trunk/config.distrib.php trunk/doc/Change.log trunk/doc/Manual/ManualContents.php trunk/index.php trunk/locale/cs_CZ.utf8/LC_MESSAGES/messages.mo trunk/locale/cs_CZ.utf8/LC_MESSAGES/messages.po trunk/locale/de_DE.utf8/LC_MESSAGES/messages.mo trunk/locale/de_DE.utf8/LC_MESSAGES/messages.po trunk/locale/el_GR.utf8/LC_MESSAGES/messages.mo trunk/locale/el_GR.utf8/LC_MESSAGES/messages.po trunk/locale/en_GB.utf8/LC_MESSAGES/messages.pot trunk/locale/en_US.utf8/LC_MESSAGES/messages.mo trunk/locale/en_US.utf8/LC_MESSAGES/messages.po trunk/locale/es_ES.utf8/LC_MESSAGES/messages.mo trunk/locale/es_ES.utf8/LC_MESSAGES/messages.po trunk/locale/et_EE.utf8/LC_MESSAGES/messages.mo trunk/locale/et_EE.utf8/LC_MESSAGES/messages.po trunk/locale/fa_IR.utf8/LC_MESSAGES/messages.mo trunk/locale/fa_IR.utf8/LC_MESSAGES/messages.po trunk/locale/fr_FR.utf8/LC_MESSAGES/messages.mo trunk/locale/fr_FR.utf8/LC_MESSAGES/messages.po trunk/locale/hi_IN.utf8/LC_MESSAGES/messages.mo trunk/locale/hi_IN.utf8/LC_MESSAGES/messages.po trunk/locale/hr_HR.utf8/LC_MESSAGES/messages.mo trunk/locale/hr_HR.utf8/LC_MESSAGES/messages.po trunk/locale/hu_HU.utf8/LC_MESSAGES/messages.mo trunk/locale/hu_HU.utf8/LC_MESSAGES/messages.po trunk/locale/id_ID.utf8/LC_MESSAGES/messages.mo trunk/locale/id_ID.utf8/LC_MESSAGES/messages.po trunk/locale/it_IT.utf8/LC_MESSAGES/messages.mo trunk/locale/it_IT.utf8/LC_MESSAGES/messages.po trunk/locale/ja_JP.utf8/LC_MESSAGES/messages.mo trunk/locale/ja_JP.utf8/LC_MESSAGES/messages.po trunk/locale/lv_LV.utf8/LC_MESSAGES/messages.mo trunk/locale/lv_LV.utf8/LC_MESSAGES/messages.po trunk/locale/nl_NL.utf8/LC_MESSAGES/messages.mo trunk/locale/nl_NL.utf8/LC_MESSAGES/messages.po trunk/locale/pl_PL.utf8/LC_MESSAGES/messages.mo trunk/locale/pl_PL.utf8/LC_MESSAGES/messages.po trunk/locale/pt_BR.utf8/LC_MESSAGES/messages.mo trunk/locale/pt_BR.utf8/LC_MESSAGES/messages.po trunk/locale/pt_PT.utf8/LC_MESSAGES/messages.mo trunk/locale/pt_PT.utf8/LC_MESSAGES/messages.po trunk/locale/ro_RO.utf8/LC_MESSAGES/messages.mo trunk/locale/ro_RO.utf8/LC_MESSAGES/messages.po trunk/locale/ru_RU.utf8/LC_MESSAGES/messages.mo trunk/locale/ru_RU.utf8/LC_MESSAGES/messages.po trunk/locale/sq_AL.utf8/LC_MESSAGES/messages.mo trunk/locale/sq_AL.utf8/LC_MESSAGES/messages.po trunk/locale/sv_SE.utf8/LC_MESSAGES/messages.mo trunk/locale/sv_SE.utf8/LC_MESSAGES/messages.po trunk/locale/sw_KE.utf8/LC_MESSAGES/messages.mo trunk/locale/sw_KE.utf8/LC_MESSAGES/messages.po trunk/locale/tr_TR.utf8/LC_MESSAGES/messages.mo trunk/locale/tr_TR.utf8/LC_MESSAGES/messages.po trunk/locale/vi_VN.utf8/LC_MESSAGES/messages.mo trunk/locale/vi_VN.utf8/LC_MESSAGES/messages.po trunk/locale/zh_CN.utf8/LC_MESSAGES/messages.mo trunk/locale/zh_CN.utf8/LC_MESSAGES/messages.po trunk/locale/zh_HK.utf8/LC_MESSAGES/messages.mo trunk/locale/zh_HK.utf8/LC_MESSAGES/messages.po trunk/sql/mysql/upgrade4.03-4.04.sql trunk/sql/mysql/weberp-demo.sql trunk/sql/mysql/weberp-new.sql Added Paths: ----------- trunk/SalesByTypePeriodInquiry.php trunk/SalesCategoryPeriodInquiry.php trunk/SalesTopItemsInquiry.php Modified: trunk/COGSGLPostings.php =================================================================== --- trunk/COGSGLPostings.php 2011-05-23 10:20:46 UTC (rev 4573) +++ trunk/COGSGLPostings.php 2011-05-25 10:30:45 UTC (rev 4574) @@ -84,7 +84,7 @@ if (DB_num_rows($result)>0){ $ShowLivePostingRecords = false; prnMsg (_('The following cost of sales posting records that do not have valid general ledger code specified - these records must be amended.'),'error'); - echo '<table class=selection>'; + echo '<table class="selection">'; echo '<tr><th>' . _('Area') . '</th> <th>' . _('Stock Category') . '</th> <th>' . _('Sales Type') . '</th> @@ -209,7 +209,7 @@ <td>%s</td> <td>%s</td> <td><a href="%sSelectedCOGSPostingID=%s">' . _('Edit') . '</td> - <td><a href="%sSelectedCOGSPostingID=%s&delete=yes">' . _('Delete') . '</td> + <td><a href="%sSelectedCOGSPostingID=%s&delete=yes" onclick="return confirm(\'' . _('Are you sure you wish to delete this COGS GL posting record?') . '\');">' . _('Delete') . '</td> </tr>', $myrow['area'], $myrow['stkcat'], Modified: trunk/PeriodsInquiry.php =================================================================== --- trunk/PeriodsInquiry.php 2011-05-23 10:20:46 UTC (rev 4573) +++ trunk/PeriodsInquiry.php 2011-05-25 10:30:45 UTC (rev 4574) @@ -1,10 +1,7 @@ <?php /* $Id$*/ -/* $Revision: 1.8 $ */ -//$PageSecurity = 2; - include ('includes/session.inc'); $title = _('Periods Inquiry'); @@ -13,13 +10,13 @@ $SQL = "SELECT periodno , lastdate_in_period - FROM periods - ORDER BY periodno"; + FROM periods + ORDER BY periodno"; $ErrMsg = _('No periods were returned by the SQL because'); $PeriodsResult = DB_query($SQL,$db,$ErrMsg); - echo '<p class="page_title_text"><img src="'.$rootpath.'/css/'.$theme.'/images/transactions.png" title="' . $title . '" alt="" />' . ' ' +echo '<p class="page_title_text"><img src="'.$rootpath.'/css/'.$theme.'/images/transactions.png" title="' . $title . '" alt="" />' . ' ' . $title . '</p>'; /*show a table of the orders returned by the SQL */ @@ -28,16 +25,18 @@ $PeriodsInTable = round($NumberOfPeriods/3,0); $TableHeader = '<tr><th>' . _('Period Number') . '</th> - <th>' . _('Date of Last Day') . '</th> - </tr>'; + <th>' . _('Date of Last Day') . '</th> + </tr>'; + echo '<table><tr>'; -for ($i=0;$i<2;$i++) { - echo '<td>'; - echo '<table cellpadding=2 colspan=2 class=selection>'; + +for ($i=0;$i<3;$i++) { + echo '<td valign="top">'; + echo '<table cellpadding=2 colspan=2 class="selection">'; echo $TableHeader; $k=0; - for ($j=0; $j<$PeriodsInTable;$j++) { - $myrow=DB_fetch_array($PeriodsResult); + $j=0; + while ($myrow=DB_fetch_array($PeriodsResult)){ if ($k==1){ echo '<tr class="EvenTableRows">'; $k=0; @@ -45,33 +44,18 @@ echo '<tr class="OddTableRows">'; $k++; } - $FormatedLastDate = ConvertSQLDate($myrow['lastdate_in_period']); - echo "<td>".$myrow['periodno']."</td> - <td>".$FormatedLastDate."</td> - </tr>"; + echo '<td>' . $myrow['periodno'] . '</td> + <td>' . ConvertSQLDate($myrow['lastdate_in_period']) . '</td> + </tr>'; + $j++; + if ($j==$PeriodsInTable){ + break; + } } echo '</table>'; echo '</td>'; } -echo '<td>'; -echo '<table cellpadding=2 colspan=2 class=selection>'; -echo $TableHeader; -$k = 0; //row colour counter -while ($myrow=DB_fetch_array($PeriodsResult)) { - if ($k==1){ - echo '<tr class="EvenTableRows">'; - $k=0; - } else { - echo '<tr class="OddTableRows">'; - $k++; - } - $FormatedLastDate = ConvertSQLDate($myrow['lastdate_in_period']); - echo "<td>".$myrow['periodno']."</td> - <td>".$FormatedLastDate."</td> - </tr>"; -} -echo '</table>'; -echo '</td>'; + echo '</tr></table>'; //end of while loop Added: trunk/SalesByTypePeriodInquiry.php =================================================================== --- trunk/SalesByTypePeriodInquiry.php (rev 0) +++ trunk/SalesByTypePeriodInquiry.php 2011-05-25 10:30:45 UTC (rev 4574) @@ -0,0 +1,560 @@ +<?php + +/* $Id: SalesByTypePeriodInquiry.php 4261 2010-12-22 15:56:50Z tim_schofield $*/ + +include('includes/session.inc'); +$title = _('Sales Report'); +include('includes/header.inc'); +include('includes/DefineCartClass.php'); + +echo '<p class="page_title_text"><img src="'.$rootpath.'/css/'.$theme.'/images/transactions.png" title="' . _('Sales Report') . '" alt="" />' . ' ' . _('Sales Report') . '</p>'; +echo '<div class="page_help_text">' . _('Select the parameters for the report') . '</div><br>'; + +if (!isset($_POST['DisplayData'])){ + /* then assume to display daily - maybe wrong to do this but hey better than reporting an error?*/ + $_POST['DisplayData']='Weekly'; +} +if (!isset($_POST['DateRange'])){ + /* then assume report is for This Month - maybe wrong to do this but hey better than reporting an error?*/ + $_POST['DateRange']='ThisMonth'; +} + +echo '<form action="' . $_SERVER['PHP_SELF'] . '" method="post">'; +echo '<input type="hidden" name="FormID" value="' . $_SESSION['FormID'] . '" />'; + +echo '<table cellpadding=2 class="selection"> + <tr><td valign=top> + <table>'; + +echo '<tr><th colspan="2" class="centre">' . _('Date Selection') . '</th> + </tr> + <tr> + <td>' . _('Custom Range') . ':</td> + <td><input type="radio" name="DateRange" value="Custom" '; +if ($_POST['DateRange']=='Custom'){ + echo 'checked'; +} +echo '></td> + </tr> + <tr> + <td>' . _('This Week') . ':</td> + <td><input type="radio" name="DateRange" value="ThisWeek" '; +if ($_POST['DateRange']=='ThisWeek'){ + echo 'checked'; +} +echo '></td> + </tr> + <tr> + <td>' . _('This Month') . ':</td> + <td><input type="radio" name="DateRange" value="ThisMonth" '; +if ($_POST['DateRange']=='ThisMonth'){ + echo 'checked'; +} +echo '></td> + </tr> + <tr> + <td>' . _('This Quarter') . ':</td> + <td><input type="radio" name="DateRange" value="ThisQuarter" '; +if ($_POST['DateRange']=='ThisQuarter'){ + echo 'checked'; +} +echo '></td> + </tr>'; +if ($_POST['DateRange']=='Custom'){ + echo '<tr> + <td>' . _('Date From') . ':</td> + <td><input type="text" class="date" alt="' . $_SESSION['DefaultDateFormat'] . '" name="FromDate" maxlength="10" size="11" value="' . $_POST['FromDate'] . '" /></td> + </tr>'; + echo '<tr> + <td>' . _('Date To') . ':</td> + <td><input type="text" class="date" alt="' . $_SESSION['DefaultDateFormat'] . '" name="ToDate" maxlength="10" size="11" value="' . $_POST['ToDate'] . '" /></td> + </tr>'; +} +echo '</table> + </td><td valign=top> + <table>'; + +echo '<tr><th colspan="2" class="centre">' . _('Display Data') . '</th> + </tr> + <tr> + <td>' . _('Daily') . ':</td> + <td><input type="radio" name="DisplayData" value="Daily" '; +if ($_POST['DisplayData']=='Daily'){ + echo 'checked'; +} +echo '></td> + </tr> + <tr> + <td>' . _('Weekly') . ':</td> + <td><input type="radio" name="DisplayData" value="Weekly" '; +if ($_POST['DisplayData']=='Weekly'){ + echo 'checked'; +} +echo '></td> + </tr> + <tr> + <td>' . _('Monthly') . ':</td> + <td><input type="radio" name="DisplayData" value="Monthly" '; +if ($_POST['DisplayData']=='Monthly'){ + echo 'checked'; +} +echo '></td> + </tr> + <tr> + <td>' . _('Quarterly') . ':</td> + <td><input type="radio" name="DisplayData" value="Quarterly" '; +if ($_POST['DisplayData']=='Monthly'){ + echo 'checked'; +} +echo '></td> + </tr>'; +echo '</table> + </td></tr> + </table>'; + + +echo '<br /><div class="centre"><input tabindex=4 type=submit name="ShowSales" value="' . _('Show Sales') . '">'; +echo '</form></div>'; +echo '<br />'; + +if ($_POST['DateRange']=='Custom' AND !isset($_POST['FromDate']) AND !isset($_POST['ToDate'])){ + //Don't run the report until custom dates entered + unset($_POST['ShowSales']); +} + +if (isset($_POST['ShowSales'])){ + $InputError=0; //assume no input errors now test for errors + if ($_POST['DateRange']=='Custom'){ + if (!Is_Date($_POST['FromDate'])){ + $InputError = 1; + prnMsg(_('The date entered for the from date is not in the appropriate format. Dates must be entered in the format') . ' ' . $_SESSION['DefaultDateFormat'], 'error'); + } + if (!Is_Date($_POST['ToDate'])){ + $InputError = 1; + prnMsg(_('The date entered for the to date is not in the appropriate format. Dates must be entered in the format') . ' ' . $_SESSION['DefaultDateFormat'], 'error'); + } + if (Date1GreaterThanDate2($_POST['FromDate'],$_POST['ToDate'])){ + $InputError = 1; + prnMsg(_('The from date is expected to be a date prior to the to date. Please review the selected date range'),'error'); + } + } + switch ($_POST['DateRange']) { + case 'ThisWeek': + $FromDate = date('Y-m-d',mktime(0,0,0,date('m'),date('d')-date('w')+1,date('Y'))); + $ToDate = date('Y-m-d'); + break; + case 'ThisMonth': + $FromDate = date('Y-m-d',mktime(0,0,0,date('m'),1,date('Y'))); + $ToDate = date('Y-m-d'); + break; + case 'ThisQuarter': + switch (date('m')) { + case 1: + case 2: + case 3: + $QuarterStartMonth=1; + break; + case 4: + case 5: + case 6: + $QuarterStartMonth=4; + break; + case 7: + case 8: + case 9: + $QuarterStartMonth=7; + break; + default: + $QuarterStartMonth=10; + } + $FromDate = date('Y-m-d',mktime(0,0,0,$QuarterStartMonth,1,date('Y'))); + $ToDate = date('Y-m-d'); + break; + case 'Custom': + $FromDate = FormatDateForSQL($_POST['FromDate']); + $ToDate = FormatDateForSQL($_POST['ToDate']); + } + switch ($_POST['DisplayData']) { + case 'Daily': + $sql = "SELECT debtortrans.trandate, + debtortrans.tpe, + SUM(CASE WHEN stockmoves.type=10 THEN + price*(1-discountpercent)* -qty + ELSE 0 END) + as salesvalue, + SUM(CASE WHEN stockmoves.type=10 THEN + 1 ELSE 0 END) + as nooforders, + SUM(CASE WHEN stockmoves.type=11 THEN + price*(1-discountpercent)* (-qty) + ELSE 0 END) + as returnvalue, + SUM((standardcost * -qty)) as cost + FROM stockmoves + INNER JOIN custbranch + ON stockmoves.debtorno=custbranch.debtorno + AND stockmoves.branchcode=custbranch.branchcode + INNER JOIN debtortrans + ON stockmoves.type=debtortrans.type + AND stockmoves.transno=debtortrans.transno + WHERE (stockmoves.type=10 or stockmoves.type=11) + AND show_on_inv_crds =1 + AND debtortrans.trandate>='" . $FromDate . "' + AND debtortrans.trandate<='" . $ToDate . "' + GROUP BY debtortrans.trandate, + tpe + ORDER BY debtortrans.trandate, + tpe"; + + break; + case 'Weekly': + $sql = "SELECT WEEKOFYEAR(debtortrans.trandate) as week_no, + YEAR(debtortrans.trandate) as transyear, + debtortrans.tpe, + SUM(CASE WHEN stockmoves.type=10 THEN + price*(1-discountpercent)* -qty + ELSE 0 END) + as salesvalue, + SUM(CASE WHEN stockmoves.type=10 THEN + 1 ELSE 0 END) + as nooforders, + SUM(CASE WHEN stockmoves.type=11 THEN + price*(1-discountpercent)* (-qty) + ELSE 0 END) + as returnvalue, + SUM((standardcost * -qty)) as cost + FROM stockmoves + INNER JOIN custbranch + ON stockmoves.debtorno=custbranch.debtorno + AND stockmoves.branchcode=custbranch.branchcode + INNER JOIN debtortrans + ON stockmoves.type=debtortrans.type + AND stockmoves.transno=debtortrans.transno + WHERE (stockmoves.type=10 or stockmoves.type=11) + AND show_on_inv_crds =1 + AND debtortrans.trandate>='" . $FromDate . "' + AND debtortrans.trandate<='" . $ToDate . "' + GROUP BY week_no, + transyear, + tpe + ORDER BY transyear, + week_no, + tpe"; + + break; + case 'Monthly': + $sql = "SELECT MONTH(debtortrans.trandate) as month_no, + MONTHNAME(debtortrans.trandate) as month_name, + YEAR(debtortrans.trandate) as transyear, + debtortrans.tpe, + SUM(CASE WHEN stockmoves.type=10 THEN + price*(1-discountpercent)* -qty + ELSE 0 END) + as salesvalue, + SUM(CASE WHEN stockmoves.type=10 THEN + 1 ELSE 0 END) + as nooforders, + SUM(CASE WHEN stockmoves.type=11 THEN + price*(1-discountpercent)* (-qty) + ELSE 0 END) + as returnvalue, + SUM((standardcost * -qty)) as cost + FROM stockmoves + INNER JOIN custbranch + ON stockmoves.debtorno=custbranch.debtorno + AND stockmoves.branchcode=custbranch.branchcode + INNER JOIN debtortrans + ON stockmoves.type=debtortrans.type + AND stockmoves.transno=debtortrans.transno + WHERE (stockmoves.type=10 or stockmoves.type=11) + AND show_on_inv_crds =1 + AND debtortrans.trandate>='" . $FromDate . "' + AND debtortrans.trandate<='" . $ToDate . "' + GROUP BY month_no, + month_name, + transyear, + debtortrans.tpe + ORDER BY transyear, + month_no, + tpe"; + + break; + case 'Quarterly': + $sql = "SELECT QUARTER(debtortrans.trandate) as quarter_no, + YEAR(debtortrans.trandate) as transyear, + debtortrans.tpe, + SUM(CASE WHEN stockmoves.type=10 THEN + price*(1-discountpercent)* -qty + ELSE 0 END) + as salesvalue, + SUM(CASE WHEN stockmoves.type=10 THEN + 1 ELSE 0 END) + as nooforders, + SUM(CASE WHEN stockmoves.type=11 THEN + price*(1-discountpercent)* (-qty) + ELSE 0 END) + as returnvalue, + SUM((standardcost * -qty)) as cost + FROM stockmoves + INNER JOIN custbranch + ON stockmoves.debtorno=custbranch.debtorno + AND stockmoves.branchcode=custbranch.branchcode + INNER JOIN debtortrans + ON stockmoves.type=debtortrans.type + AND stockmoves.transno=debtortrans.transno + WHERE (stockmoves.type=10 or stockmoves.type=11) + AND show_on_inv_crds =1 + AND debtortrans.trandate>='" . $FromDate . "' + AND debtortrans.trandate<='" . $ToDate . "' + GROUP BY quarter_no, + transyear, + tpe + ORDER BY transyear, + quarter_no, + tpe"; + + break; + } + + $ErrMsg = _('The sales data could not be retrieved because') . ' - ' . DB_error_msg($db); + $SalesResult = DB_query($sql,$db,$ErrMsg); + + + echo '<table cellpadding=2 class="selection">'; + + echo'<tr> + <th>' . _('Period') . '</th> + <th>' . _('Sales') . '<br />' . _('Type') . '</th> + <th>' . _('No Orders') . '</th> + <th>' . _('Total Sales') . '</th> + <th>' . _('Refunds') . '</th> + <th>' . _('Net Sales') . '</th> + <th>' . _('Cost of Sales') . '</th> + <th>' . _('Gross Profit') . '</th> + </tr>'; + + $CumulativeTotalSales = 0; + $CumulativeTotalOrders = 0; + $CumulativeTotalRefunds = 0; + $CumulativeTotalNetSales = 0; + $CumulativeTotalCost = 0; + $CumulativeTotalGP = 0; + + $PrdTotalOrders =0; + $PrdTotalSales=0; + $PrdTotalRefunds=0; + $PrdTotalNetSales=0; + $PrdTotalCost=0; + $PrdTotalGP=0; + + $PeriodHeadingDone = false; + $LastPeriodHeading = 'First Run Through'; + $k=0; + while ($SalesRow=DB_fetch_array($SalesResult)) { + if ($k==1){ + echo '<tr class="EvenTableRows">'; + $k=0; + } else { + echo '<tr class="OddTableRows">'; + $k=1; + } + switch ($_POST['DisplayData']){ + case 'Daily': + if ($LastPeriodHeading != ConvertSQLDate($SalesRow['trandate'])) { + $PeriodHeadingDone=false; + if ($LastPeriodHeading != 'First Run Through'){ //print the footer for the period + echo '<td colspan="2" class="number">' . _('Total') . '-' . $LastPeriodHeading . '</td> + <td class="number">' . $PrdTotalOrders . '</td> + <td class="number">' . number_format($PrdTotalSales,2) . '</td> + <td class="number">' . number_format($PrdTotalRefunds,2) . '</td> + <td class="number">' . number_format($PrdTotalNetSales,2) . '</td> + <td class="number">' . number_format($PrdTotalCost,2) . '</td> + <td class="number">' . number_format($PrdTotalGP,2) . '</td> + </tr>'; + if ($k==1){ + echo '<tr class="EvenTableRows"><td colspan="8"><hr></td></tr>'; + echo '<tr class="OddTableRows">'; + } else { + echo '<tr class="OddTableRows"><td colspan="8"><hr></td></tr>'; + echo '<tr class="EvenTableRows">'; + } + $PrdTotalOrders =0; + $PrdTotalSales=0; + $PrdTotalRefunds=0; + $PrdTotalNetSales=0; + $PrdTotalCost=0; + $PrdTotalGP=0; + } + } + if (! $PeriodHeadingDone){ + echo '<td>' . ConvertSQLDate($SalesRow['trandate']) . '</td>'; + $LastPeriodHeading = ConvertSQLDate($SalesRow['trandate']); + $PeriodHeadingDone = true; + } else { + echo '<td></td>'; + } + break; + case 'Weekly': + if ($LastPeriodHeading != _('wk'). '-' . $SalesRow['week_no'] . ' ' . $SalesRow['transyear']) { + $PeriodHeadingDone=false; + if ($LastPeriodHeading != 'First Run Through'){ + echo '<td colspan="2" class="number">' . _('Total') . '-' . $LastPeriodHeading . '</td> + <td class="number">' . $PrdTotalOrders . '</td> + <td class="number">' . number_format($PrdTotalSales,2) . '</td> + <td class="number">' . number_format($PrdTotalRefunds,2) . '</td> + <td class="number">' . number_format($PrdTotalNetSales,2) . '</td> + <td class="number">' . number_format($PrdTotalCost,2) . '</td> + <td class="number">' . number_format($PrdTotalGP,2) . '</td> + </tr>'; + if ($k==1){ + echo '<tr class="EvenTableRows"><td colspan="8"><hr></td></tr>'; + echo '<tr class="OddTableRows">'; + } else { + echo '<tr class="OddTableRows"><td colspan="8"><hr></td></tr>'; + echo '<tr class="EvenTableRows">'; + } + $PrdTotalOrders =0; + $PrdTotalSales=0; + $PrdTotalRefunds=0; + $PrdTotalNetSales=0; + $PrdTotalCost=0; + $PrdTotalGP=0; + } + } + if (! $PeriodHeadingDone){ + echo '<td>' . _('wk'). '-' . $SalesRow['week_no'] . ' ' . $SalesRow['transyear'] . '</td>'; + $LastPeriodHeading = _('wk'). '-' . $SalesRow['week_no'] . ' ' . $SalesRow['transyear']; + $PeriodHeadingDone = true; + } else { + echo '<td></td>'; + } + break; + case 'Monthly': + if ($LastPeriodHeading != $SalesRow['month_name'] . ' ' . $SalesRow['transyear']) { + $PeriodHeadingDone=false; + if ($LastPeriodHeading != 'First Run Through'){ + echo '<td colspan="2" class="number">' . _('Total') . '-' . $LastPeriodHeading . '</td> + <td class="number">' . $PrdTotalOrders . '</td> + <td class="number">' . number_format($PrdTotalSales,2) . '</td> + <td class="number">' . number_format($PrdTotalRefunds,2) . '</td> + <td class="number">' . number_format($PrdTotalNetSales,2) . '</td> + <td class="number">' . number_format($PrdTotalCost,2) . '</td> + <td class="number">' . number_format($PrdTotalGP,2) . '</td> + </tr>'; + if ($k==1){ + echo '<tr class="EvenTableRows"><td colspan="8"><hr></td></tr>'; + echo '<tr class="OddTableRows">'; + } else { + echo '<tr class="OddTableRows"><td colspan="8"><hr></td></tr>'; + echo '<tr class="EvenTableRows">'; + } + $PrdTotalOrders =0; + $PrdTotalSales=0; + $PrdTotalRefunds=0; + $PrdTotalNetSales=0; + $PrdTotalCost=0; + $PrdTotalGP=0; + } + } + if (! $PeriodHeadingDone){ + echo '<td>' . $SalesRow['month_name'] . ' ' . $SalesRow['transyear'] . '</td>'; + $LastPeriodHeading = $SalesRow['month_name'] . ' ' . $SalesRow['transyear']; + $PeriodHeadingDone = true; + } else { + echo '<td></td>'; + } + break; + case 'Quarterly': + if ($LastPeriodHeading != _('Qtr'). '-' . $SalesRow['quarter_no'] . ' ' . $SalesRow['transyear']) { + $PeriodHeadingDone=false; + if ($LastPeriodHeading != 'First Run Through'){ + echo '<td colspan="2" class="number">' . _('Total') . '-'. $LastPeriodHeading . '</td> + <td class="number">' . $PrdTotalOrders . '</td> + <td class="number">' . number_format($PrdTotalSales,2) . '</td> + <td class="number">' . number_format($PrdTotalRefunds,2) . '</td> + <td class="number">' . number_format($PrdTotalNetSales,2) . '</td> + <td class="number">' . number_format($PrdTotalCost,2) . '</td> + <td class="number">' . number_format($PrdTotalGP,2) . '</td> + </tr>'; + if ($k==1){ + echo '<tr class="EvenTableRows"><td colspan="8"><hr></td></tr>'; + echo '<tr class="OddTableRows">'; + } else { + echo '<tr class="OddTableRows"><td colspan="8"><hr></td></tr>'; + echo '<tr class="EvenTableRows">'; + } + $PrdTotalOrders =0; + $PrdTotalSales=0; + $PrdTotalRefunds=0; + $PrdTotalNetSales=0; + $PrdTotalCost=0; + $PrdTotalGP=0; + } + } + if (! $PeriodHeadingDone){ + echo '<td>' . _('Qtr'). '-' . $SalesRow['quarter_no'] . ' ' . $SalesRow['transyear'] . '</td>'; + $LastPeriodHeading = _('Qtr'). '-' . $SalesRow['quarter_no'] . ' ' . $SalesRow['transyear']; + $PeriodHeadingDone = true; + } else { + echo '<td></td>'; + } + break; + } + echo '<td>' . $SalesRow['tpe'] . '</td> + <td class="number">' . $SalesRow['nooforders'] . '</td> + <td class="number">' . number_format($SalesRow['salesvalue'],2) . '</td> + <td class="number">' . number_format($SalesRow['returnvalue'],2) . '</td> + <td class="number">' . number_format($SalesRow['salesvalue']+$SalesRow['returnvalue'],2) . '</td> + <td class="number">' . number_format($SalesRow['cost'],2) . '</td> + <td class="number">' . number_format(($SalesRow['salesvalue']+$SalesRow['returnvalue']-$SalesRow['cost']),2) . '</td> + </tr>'; + $PrdTotalOrders +=$SalesRow['nooforders']; + $PrdTotalSales += $SalesRow['salesvalue']; + $PrdTotalRefunds += $SalesRow['returnvalue']; + $PrdTotalNetSales += ($SalesRow['salesvalue']+$SalesRow['returnvalue']); + $PrdTotalCost += $SalesRow['cost']; + $PrdTotalGP += ($SalesRow['salesvalue']+$SalesRow['returnvalue']-$SalesRow['cost']); + + $CumulativeTotalSales += $SalesRow['salesvalue']; + $CumulativeTotalOrders = $SalesRow['nooforders']; + $CumulativeTotalRefunds += $SalesRow['returnvalue']; + $CumulativeTotalNetSales += ($SalesRow['salesvalue']+$SalesRow['returnvalue']); + $CumulativeTotalCost += $SalesRow['cost']; + $CumulativeTotalGP += ($SalesRow['salesvalue']+$SalesRow['returnvalue']-$SalesRow['cost']); + } + if ($k==1){ + echo '<tr class="EvenTableRows">'; + $k=0; + } else { + echo '<tr class="OddTableRows">'; + $k=1; + } + echo '<td colspan="2" class="number">' . _('Total') . ' ' . $LastPeriodHeading . '</td> + <td class="number">' . $PrdTotalOrders . '</td> + <td class="number">' . number_format($PrdTotalSales,2) . '</td> + <td class="number">' . number_format($PrdTotalRefunds,2) . '</td> + <td class="number">' . number_format($PrdTotalNetSales,2) . '</td> + <td class="number">' . number_format($PrdTotalCost,2) . '</td> + <td class="number">' . number_format($PrdTotalGP,2) . '</td> + </tr>'; + if ($k==1){ + echo '<tr class="EvenTableRows"><td colspan="8"><hr></td></tr>'; + echo '<tr class="OddTableRows">'; + } else { + echo '<tr class="OddTableRows"><td colspan="8"><hr></td></tr>'; + echo '<tr class="EvenTableRows">'; + } + echo '<td colspan="2" class="number">' . _('GRAND Total') . '</td> + <td class="number">' . $CumulativeTotalOrders . '</td> + <td class="number">' . number_format($CumulativeTotalSales,2) . '</td> + <td class="number">' . number_format($CumulativeTotalRefunds,2) . '</td> + <td class="number">' . number_format($CumulativeTotalNetSales,2) . '</td> + <td class="number">' . number_format($CumulativeTotalCost,2) . '</td> + <td class="number">' . number_format($CumulativeTotalGP,2) . '</td> + </tr>'; + + echo '</table>'; + +} //end of if user hit show sales +include('includes/footer.inc'); +?> \ No newline at end of file Added: trunk/SalesCategoryPeriodInquiry.php =================================================================== --- trunk/SalesCategoryPeriodInquiry.php (rev 0) +++ trunk/SalesCategoryPeriodInquiry.php 2011-05-25 10:30:45 UTC (rev 4574) @@ -0,0 +1,220 @@ +<?php + +/* $Id: SalesCategoryPeriodInquiry.php 4261 2010-12-22 15:56:50Z $*/ + +include('includes/session.inc'); +$title = _('Sales Category Report'); +include('includes/header.inc'); +include('includes/DefineCartClass.php'); + +echo '<p class="page_title_text"><img src="'.$rootpath.'/css/'.$theme.'/images/transactions.png" title="' . _('Sales Report') . '" alt="" />' . ' ' . _('Sales Category Report') . '</p>'; +echo '<div class="page_help_text">' . _('Select the parameters for the report') . '</div><br>'; + +if (!isset($_POST['DateRange'])){ + /* then assume report is for This Month - maybe wrong to do this but hey better than reporting an error?*/ + $_POST['DateRange']='ThisMonth'; +} + +echo '<form action="' . $_SERVER['PHP_SELF'] . '" method="post">'; +echo '<input type="hidden" name="FormID" value="' . $_SESSION['FormID'] . '" />'; + +echo '<table cellpadding=2 class="selection">'; + +echo '<tr><th colspan="2" class="centre">' . _('Date Selection') . '</th> + </tr> + <tr> + <td>' . _('Custom Range') . ':</td> + <td><input type="radio" name="DateRange" value="Custom" '; +if ($_POST['DateRange']=='Custom'){ + echo 'checked'; +} +echo '></td> + </tr> + <tr> + <td>' . _('This Week') . ':</td> + <td><input type="radio" name="DateRange" value="ThisWeek" '; +if ($_POST['DateRange']=='ThisWeek'){ + echo 'checked'; +} +echo '></td> + </tr> + <tr> + <td>' . _('This Month') . ':</td> + <td><input type="radio" name="DateRange" value="ThisMonth" '; +if ($_POST['DateRange']=='ThisMonth'){ + echo 'checked'; +} +echo '></td> + </tr> + <tr> + <td>' . _('This Quarter') . ':</td> + <td><input type="radio" name="DateRange" value="ThisQuarter" '; +if ($_POST['DateRange']=='ThisQuarter'){ + echo 'checked'; +} +echo '></td> + </tr>'; +if ($_POST['DateRange']=='Custom'){ + echo '<tr> + <td>' . _('Date From') . ':</td> + <td><input type="text" class="date" alt="' . $_SESSION['DefaultDateFormat'] . '" name="FromDate" maxlength="10" size="11" value="' . $_POST['FromDate'] . '" /></td> + </tr>'; + echo '<tr> + <td>' . _('Date To') . ':</td> + <td><input type="text" class="date" alt="' . $_SESSION['DefaultDateFormat'] . '" name="ToDate" maxlength="10" size="11" value="' . $_POST['ToDate'] . '" /></td> + </tr>'; +} +echo '</table>'; + + +echo '<br /><div class="centre"><input tabindex=4 type=submit name="ShowSales" value="' . _('Show Sales') . '">'; +echo '</form></div>'; +echo '<br />'; + +if ($_POST['DateRange']=='Custom' AND !isset($_POST['FromDate']) AND !isset($_POST['ToDate'])){ + //Don't run the report until custom dates entered + unset($_POST['ShowSales']); +} + +if (isset($_POST['ShowSales'])){ + $InputError=0; //assume no input errors now test for errors + if ($_POST['DateRange']=='Custom'){ + if (!Is_Date($_POST['FromDate'])){ + $InputError = 1; + prnMsg(_('The date entered for the from date is not in the appropriate format. Dates must be entered in the format') . ' ' . $_SESSION['DefaultDateFormat'], 'error'); + } + if (!Is_Date($_POST['ToDate'])){ + $InputError = 1; + prnMsg(_('The date entered for the to date is not in the appropriate format. Dates must be entered in the format') . ' ' . $_SESSION['DefaultDateFormat'], 'error'); + } + if (Date1GreaterThanDate2($_POST['FromDate'],$_POST['ToDate'])){ + $InputError = 1; + prnMsg(_('The from date is expected to be a date prior to the to date. Please review the selected date range'),'error'); + } + } + switch ($_POST['DateRange']) { + case 'ThisWeek': + $FromDate = date('Y-m-d',mktime(0,0,0,date('m'),date('d')-date('w')+1,date('Y'))); + $ToDate = date('Y-m-d'); + break; + case 'ThisMonth': + $FromDate = date('Y-m-d',mktime(0,0,0,date('m'),1,date('Y'))); + $ToDate = date('Y-m-d'); + break; + case 'ThisQuarter': + switch (date('m')) { + case 1: + case 2: + case 3: + $QuarterStartMonth=1; + break; + case 4: + case 5: + case 6: + $QuarterStartMonth=4; + break; + case 7: + case 8: + case 9: + $QuarterStartMonth=7; + break; + default: + $QuarterStartMonth=10; + } + $FromDate = date('Y-m-d',mktime(0,0,0,$QuarterStartMonth,1,date('Y'))); + $ToDate = date('Y-m-d'); + break; + case 'Custom': + $FromDate = FormatDateForSQL($_POST['FromDate']); + $ToDate = FormatDateForSQL($_POST['ToDate']); + } + $sql = "SELECT stockmaster.categoryid, + stockcategory.categorydescription, + SUM(CASE WHEN stockmoves.type=10 THEN + price*(1-discountpercent)* -qty + ELSE 0 END) as salesvalue, + SUM(CASE WHEN stockmoves.type=11 THEN + price*(1-discountpercent)* (-qty) + ELSE 0 END) as returnvalue, + SUM(CASE WHEN stockmoves.type=11 + OR stockmoves.type=10 THEN + price*(1-discountpercent)* (-qty) + ELSE 0 END) as netsalesvalue, + SUM((standardcost * -qty)) as cost + FROM stockmoves INNER JOIN stockmaster + ON stockmoves.stockid=stockmaster.stockid + INNER JOIN stockcategory + ON stockmaster.categoryid=stockcategory.categoryid + WHERE (stockmoves.type=10 or stockmoves.type=11) + AND show_on_inv_crds =1 + AND trandate>='" . $FromDate . "' + AND trandate<='" . $ToDate . "' + GROUP BY stockmaster.categoryid + ORDER BY netsalesvalue DESC"; + + $ErrMsg = _('The sales data could not be retrieved because') . ' - ' . DB_error_msg($db); + $SalesResult = DB_query($sql,$db,$ErrMsg); + $OrdersResult = DB_query($OrdersSQL,$db,$ErrMsg); + + echo '<table cellpadding=2 class="selection">'; + + echo'<tr> + <th>' . _('Category') . '</th> + <th>' . _('Total Sales') . '</th> + <th>' . _('Refunds') . '</th> + <th>' . _('Net Sales') . '</th> + <th>' . _('Cost of Sales') . '</th> + <th>' . _('Gross Profit') . '</th> + </tr>'; + + $CumulativeTotalSales = 0; + $CumulativeTotalRefunds = 0; + $CumulativeTotalNetSales = 0; + $CumulativeTotalCost = 0; + $CumulativeTotalGP = 0; + + $k=0; + while ($SalesRow=DB_fetch_array($SalesResult)) { + if ($k==1){ + echo '<tr class="EvenTableRows">'; + $k=0; + } else { + echo '<tr class="OddTableRows">'; + $k=1; + } + + echo '<td>' . $SalesRow['categoryid'] . ' - ' . $SalesRow['categorydescription'] . '</td> + <td class="number">' . number_format($SalesRow['salesvalue'],2) . '</td> + <td class="number">' . number_format($SalesRow['returnvalue'],2) . '</td> + <td class="number">' . number_format($SalesRow['salesvalue']+$SalesRow['returnvalue'],2) . '</td> + <td class="number">' . number_format($SalesRow['cost'],2) . '</td> + <td class="number">' . number_format(($SalesRow['salesvalue']+$SalesRow['returnvalue']-$SalesRow['cost']),2) . '</td> + </tr>'; + + $CumulativeTotalSales += $SalesRow['salesvalue']; + $CumulativeTotalRefunds += $SalesRow['returnvalue']; + $CumulativeTotalNetSales += ($SalesRow['salesvalue']+$SalesRow['returnvalue']); + $CumulativeTotalCost += $SalesRow['cost']; + $CumulativeTotalGP += ($SalesRow['salesvalue']+$SalesRow['returnvalue']-$SalesRow['cost']); + } //loop around category sales for the period + + if ($k==1){ + echo '<tr class="EvenTableRows"><td colspan="8"><hr></td></tr>'; + echo '<tr class="OddTableRows">'; + } else { + echo '<tr class="OddTableRows"><td colspan="8"><hr></td></tr>'; + echo '<tr class="EvenTableRows">'; + } + echo '<td class="number">' . _('GRAND Total') . '</td> + <td class="number">' . number_format($CumulativeTotalSales,2) . '</td> + <td class="number">' . number_format($CumulativeTotalRefunds,2) . '</td> + <td class="number">' . number_format($CumulativeTotalNetSales,2) . '</td> + <td class="number">' . number_format($CumulativeTotalCost,2) . '</td> + <td class="number">' . number_format($CumulativeTotalGP,2) . '</td> + </tr>'; + + echo '</table>'; + +} //end of if user hit show sales +include('includes/footer.inc'); +?> \ No newline at end of file Modified: trunk/SalesGLPostings.php =================================================================== --- trunk/SalesGLPostings.php 2011-05-23 10:20:46 UTC (rev 4573) +++ trunk/SalesGLPostings.php 2011-05-25 10:30:45 UTC (rev 4574) @@ -92,7 +92,7 @@ $ShowLivePostingRecords = true; - $SQL = 'SELECT salesglpostings.id, + $SQL = "SELECT salesglpostings.id, salesglpostings.area, salesglpostings.stkcat, salesglpostings.salestype, @@ -100,7 +100,7 @@ salesglpostings.discountglcode FROM salesglpostings LEFT JOIN chartmaster ON salesglpostings.salesglcode = chartmaster.accountcode - WHERE chartmaster.accountcode IS NULL'; + WHERE chartmaster.accountcode IS NULL"; $result = DB_query($SQL,$db); if (DB_num_rows($result)>0){ @@ -124,13 +124,13 @@ $k=1; } - printf("<td>%s</td> + printf('<td>%s</td> <td>%s</td> <td>%s</td> <td>%s</td> <td>%s</td> - <td><a href=\"%sSelectedSalesPostingID=%s\">" . _('Edit') . "</td> - <td><a href=\"%sSelectedSalesPostingID=%s&delete=yes\">". _('Delete') . "</td></tr>", + <td><a href="%sSelectedSalesPostingID=%s">' . _('Edit') . '</td> + <td><a href="%sSelectedSalesPostingID=%s&delete=yes" onclick="return confirm(\'' . _('Are you sure you wish to delete this sales GL posting record?') . '\');">'. _('Delete') . '</td></tr>', $myrow[1], $myrow[2], $myrow[3], @@ -143,11 +143,11 @@ } } - $SQL = 'SELECT salesglpostings.id, + $SQL = "SELECT salesglpostings.id, salesglpostings.area, salesglpostings.stkcat, salesglpostings.salestype - FROM salesglpostings'; + FROM salesglpostings"; $result = DB_query($SQL,$db); @@ -203,7 +203,7 @@ } if ($ShowLivePostingRecords){ - $SQL = 'SELECT salesglpostings.id, + $SQL = "SELECT salesglpostings.id, salesglpostings.area, salesglpostings.stkcat, salesglpostings.salestype, @@ -213,12 +213,13 @@ chartmaster as chart1, chartmaster as chart2 WHERE salesglpostings.salesglcode = chart1.accountcode - AND salesglpostings.discountglcode = chart2.accountcode'; + AND salesglpostings.discountglcode = chart2.accountcode"; $result = DB_query($SQL,$db); - echo '<table class=selection>'; - echo '<tr><th>' . _('Area') . '</th> + echo '<table class=selection> + <tr> + <th>' . _('Area') . '</th> <th>' . _('Stock Category') . '</th> <th>' . _('Sales Type') . '</th> <th>' . _('Sales Account') . '</th> @@ -236,13 +237,13 @@ $k=1; } - printf("<td>%s</td> + printf('<td>%s</td> <td>%s</td> <td>%s</td> <td>%s</td> <td>%s</td> - <td><a href=\"%sSelectedSalesPostingID=%s\">" . _('Edit') . "</td> - <td><a href=\"%sSelectedSalesPostingID=%s&delete=yes\">". _('Delete') . "</td></tr>", + <td><a href="%sSelectedSalesPostingID=%s">' . _('Edit') . '</td> + <td><a href="%sSelectedSalesPostingID=%s&delete=yes" onclick="return confirm(\'' . _('Are you sure you wish to delete this sales GL posting record?') . '\');">'. _('Delete') . '</td></tr>', $myrow[1], $myrow[2], $myrow[3], @@ -296,21 +297,21 @@ } /*end of if $SelectedSalesPostingID only do the else when a new record is being entered */ - $SQL = 'SELECT areacode, - areadescription FROM areas'; + $SQL = "SELECT areacode, + areadescription FROM areas"; $result = DB_query($SQL,$db); - echo '<br /><table class=selection> + echo '<br /><table class="selection"> <tr> <td>' . _('Area') . ':</td> <td><select name="Area"> - <option VALUE="AN">' . _('Any Other') . '</option>'; + <option value="AN">' . _('Any Other') . '</option>'; while ($myrow = DB_fetch_array($result)) { if (isset($_POST['Area']) and $myrow['areacode']==$_POST['Area']) { echo '<option selected value="'; } else { - echo '<option VALUE="'; + echo '<option value="'; } echo $myrow['areacode'] . '">'. $myrow['areadescription'] . '</option>'; @@ -318,7 +319,7 @@ DB_free_result($result); - $SQL = 'SELECT categoryid, categorydescription FROM stockcategory'; + $SQL = "SELECT categoryid, categorydescription FROM stockcategory"; $result = DB_query($SQL,$db); echo '</select></td></tr>'; @@ -326,7 +327,7 @@ echo '<tr><td>' . _('Stock Category') . ':</td> <td><select name="StkCat"> - <option VALUE="ANY">' . _('Any Other') . '</option>'; + <option value="ANY">' . _('Any Other') . '</option>'; while ($myrow = DB_fetch_array($result)) { @@ -344,21 +345,21 @@ DB_free_result($result); - $SQL = 'SELECT typeabbrev, + $SQL = "SELECT typeabbrev, sales_type - FROM salestypes'; + FROM salestypes"; $result = DB_query($SQL,$db); echo '<tr><td>' . _('Sales Type') . ' / ' . _('Price List') . ':</td> <td><select name="SalesType">'; - echo '<option VALUE="AN">' . _('Any Other') . '</option>'; + echo '<option value="AN">' . _('Any Other') . '</option>'; while ($myrow = DB_fetch_array($result)) { if (isset($_POST['SalesType']) and $myrow['typeabbrev']==$_POST['SalesType']) { - echo '<option selected VALUE="'; + echo '<option selected value="'; } else { - echo '<option VALUE="'; + echo '<option value="'; } echo $myrow['typeabbrev'] . '">' . $myrow['sales_type'] . '</option>'; @@ -386,7 +387,7 @@ if (isset($_POST['SalesGLCode']) and $myrow['accountcode']==$_POST['SalesGLCode']) { echo '<option selected value="'; } else { - echo '<option VALUE="'; + echo '<option value="'; } echo $myrow['accountcode'] . '">' . $myrow['accountcode'] . ' - ' . $myrow['accountname'] . '</option>'; @@ -404,7 +405,7 @@ } else { echo '<option value="'; } - echo $myrow['accountcode'] . "'>" . $myrow['accountcode'] . ' - ' . $myrow['accountname'] . '</option>'; + echo $myrow['accountcode'] . '">' . $myrow['accountcode'] . ' - ' . $myrow['accountname'] . '</option>'; } //end while loop Added: trunk/SalesTopItemsInquiry.php =================================================================== --- trunk/SalesTopItemsInquiry.php (rev 0) +++ trunk/SalesTopItemsInquiry.php 2011-05-25 10:30:45 UTC (rev 4574) @@ -0,0 +1,275 @@ +<?php + +/* $Id: SalesTopItemsInquiry.php 4261 2010-12-22 15:56:50Z $*/ + +include('includes/session.inc'); +$title = _('Sales Category Report'); +include('includes/header.inc'); +include('includes/DefineCartClass.php'); + +echo '<p class="page_title_text"><img src="'.$rootpath.'/css/'.$theme.'/images/transactions.png" title="' . _('Sales Report') . '" alt="" />' . ' ' . _('Sales Category Report') . '</p>'; +echo '<div class="page_help_text">' . _('Select the parameters for the report') . '</div><br>'; + +if (!isset($_POST['DateRange'])){ + /* then assume report is for This Month - maybe wrong to do this but hey better than reporting an error?*/ + $_POST['DateRange']='ThisMonth'; +} + +echo '<form action="' . $_SERVER['PHP_SELF'] . '" method="post">'; +echo '<input type="hidden" name="FormID" value="' . $_SESSION['FormID'] . '" />'; + +echo '<table cellpadding=2 class="selection"> + <tr><td valign=top> + <table>'; + +echo '<tr><th colspan="2" class="centre">' . _('Date Selection') . '</th> + </tr> + <tr> + <td>' . _('Custom Range') . ':</td> + <td><input type="radio" name="DateRange" value="Custom" '; +if ($_POST['DateRange']=='Custom'){ + echo 'checked'; +} +echo '></td> + </tr> + <tr> + <td>' . _('This Week') . ':</td> + <td><input type="radio" name="DateRange" value="ThisWeek" '; +if ($_POST['DateRange']=='ThisWeek'){ + echo 'checked'; +} +echo '></td> + </tr> + <tr> + <td>' . _('This Month') . ':</td> + <td><input type="radio" name="DateRange" value="ThisMonth" '; +if ($_POST['DateRange']=='ThisMonth'){ + echo 'checked'; +} +echo '></td> + </tr> + <tr> + <td>' . _('This Quarter') . ':</td> + <td><input type="radio" name="DateRange" value="ThisQuarter" '; +if ($_POST['DateRange']=='ThisQuarter'){ + echo 'checked'; +} +echo '></td> + </tr>'; +if ($_POST['DateRange']=='Custom'){ + echo '<tr> + <td>' . _('Date From') . ':</td> + <td><input type="text" class="date" alt="' . $_SESSION['DefaultDateFormat'] . '" name="FromDate" maxlength="10" size="11" value="' . $_POST['FromDate'] . '" /></td> + </tr>'; + echo '<tr> + <td>' . _('Date To') . ':</td> + <td><input type="text" class="date" alt="' . $_SESSION['DefaultDateFormat'] . '" name="ToDate" maxlength="10" size="11" value="' . $_POST['ToDate'] . '" /></td> + </tr>'; +} +echo '</table></td> + <td valign=top> + <table>'; //new sub table to set parameters for order of display + + +if (!isset($_POST['OrderBy'])){ //default to order by net sales + $_POST['OrderBy']='NetSales'; +} +echo '<tr><th colspan="2" class="centre">' . _('Display') . '</th> + </tr> + <tr> + <td>' . _('Order By Net Sales') . ':</td> + <td><input type="radio" name="OrderBy" value="NetSales" '; +if ($_POST['OrderBy']=='NetSales'){ + echo 'checked'; +} +echo '></td> + </tr> + <tr> + <td>' . _('Order By Quantity') . ':</td> + <td><input type="radio" name="OrderBy" value="Quantity" '; +if ($_POST['OrderBy']=='Quantity'){ + echo 'checked'; +} +if (!isset($_POST['NoToDisplay'])){ + $_POST['NoToDisplay']=20; +} +echo '></td> + </tr> + <tr> + <td>' . _('Number to Display') . ':</td> + <td><input type="text class="number" name="NoToDisplay" size="4" maxlength="4" value="' . $_POST['NoToDisplay'] .'" ></td> + </tr> + </table> + </td></tr> + </table>'; + + +echo '<br /><div class="centre"><input tabindex=4 type=submit name="ShowSales" value="' . _('Show Sales') . '">'; +echo '</form></div>'; +echo '<br />'; + +if ($_POST['DateRange']=='Custom' AND !isset($_POST['FromDate']) AND !isset($_POST['ToDate'])){ + //Don't run the report until custom dates entered + unset($_POST['ShowSales']); +} + +if (isset($_POST['ShowSales'])){ + $InputError=0; //assume no input errors now test for errors + if ($_POST['DateRange']=='Custom'){ + if (!Is_Date($_POST['FromDate'])){ + $InputError = 1; + prnMsg(_('The date entered for the from date is not in the appropriate format. Dates must be entered in the format') . ' ' . $_SESSION['DefaultDateFormat'], 'error'); + } + if (!Is_Date($_POST['ToDate'])){ + $InputError = 1; + prnMsg(_('The date entered for the to date is not in the appropriate format. Dates must be entered in the format') . ' ' . $_SESSION['DefaultDateFormat'], 'error'); + } + if (Date1GreaterThanDate2($_POST['FromDate'],$_POST['ToDate'])){ + $InputError = 1; + prnMsg(_('The from date is expected to be a date prior to the to date. Please review the selected date range'),'error'); + } + } + switch ($_POST['DateRange']) { + case 'ThisWeek': + $FromDate = date('Y-m-d',mktime(0,0,0,date('m'),date('d')-date('w')+1,date('Y'))); + $ToDate = date('Y-m-d'); + break; + case 'ThisMonth': + $FromDate = date('Y-m-d',mktime(0,0,0,date('m'),1,date('Y'))); + $ToDate = date('Y-m-d'); + break; + case 'ThisQuarter': + switch (date('m')) { + case 1: + case 2: + case 3: + $QuarterStartMonth=1; + break; + case 4: + case 5: + case 6: + $QuarterStartMonth=4; + break; + case 7: + case 8: + case 9: + $QuarterStartMonth=7; + break; + default: + $QuarterStartMonth=10; + } + $FromDate = date('Y-m-d',mktime(0,0,0,$QuarterStartMonth,1,date('Y'))); + $ToDate = date('Y-m-d'); + break; + case 'Custom': + $FromDate = FormatDateForSQL($_POST['FromDate']); + $ToDate = FormatDateForSQL($_POST['ToDate']); + } + $sql = "SELECT stockmaster.stockid, + stockmaster.description, + stockcategory.categorydescription, + SUM(CASE WHEN stockmoves.type=10 + OR stockmoves.type=11 THEN + -qty + ELSE 0 END) as salesquantity, + SUM(CASE WHEN stockmoves.type=10 THEN + price*(1-discountpercent)* -qty + ELSE 0 END) as salesvalue, + SUM(CASE WHEN stockmoves.type=11 THEN + price*(1-discountpercent)* (-qty) + ELSE 0 END) as returnvalue, + SUM(CASE WHEN stockmoves.type=11 + OR stockmoves.type=10 THEN + price*(1-discountpercent)* (-qty) + ELSE 0 END) as netsalesvalue, + SUM((standardcost * -qty)) as cost + FROM stockmoves INNER JOIN stockmaster + ON stockmoves.stockid=stockmaster.stockid + INNER JOIN stockcategory + ON stockmaster.categoryid=stockcategory.categoryid + WHERE (stockmoves.type=10 or stockmoves.type=11) + AND show_on_inv_crds =1 + AND trandate>='" . $FromDate . "' + AND trandate<='" . $ToDate . "' + GROUP BY stockmaster.stockid, + stockmaster.description, + stockcategory.categorydescription "; + + if ($_POST['OrderBy']=='NetSales'){ + $sql .= " ORDER BY netsalesvalue DESC "; + } else { + $sql .= " ORDER BY salesquantity DESC "; + } + if (is_numeric($_POST['NoToDisplay'])){ + if ($_POST['NoToDisplay'] > 0){ + $sql .= " LIMIT " . $_POST['NoToDisplay']; + } + } + + $ErrMsg = _('The sales data could not be retrieved because') . ' - ' . DB_error_msg($db); + $SalesResult = DB_query($sql,$db,$ErrMsg); + + + echo '<table cellpadding=2 class="selection">'; + + echo'<tr> + <th>' . _('Rank') . '</th> + <th>' . _('Item') . '</th> + <th>' . _('Category') . '</th> + <th>' . _('Sales Value') . '</th> + <th>' . _('Refunds') . '</th> + <th>' . _('Net Sales') . '</th> + <th>' . _('Sales') .'<br />' . _('Quantity') . '</th> + </tr>'; + + $CumulativeTotalSales = 0; + $CumulativeTotalRefunds = 0; + $CumulativeTotalNetSales = 0; + $CumulativeTotalQuantity = 0; + $i=1; + $k=0; + while ($SalesRow=DB_fetch_array($SalesResult)) { + if ($k==1){ + echo '<tr class="EvenTableRows">'; + $k=0; + } else { + echo '<tr class="OddTableRows">'; + $k=1; + } + + echo '<td>' . $i . '</td> + <td>' . $SalesRow['stockid'] . ' - ' . $SalesRow['description'] . '</td> + <td>' . $SalesRow['categorydescription'] . '</td> + <td class="number">' . number_format($SalesRow['salesvalue'],2) . '</td> + <td class="number">' . number_format($SalesRow['returnvalue'],2) . '</td> + <td class="number">' . number_format($SalesRow['netsalesvalue'],2) . '</td> + <td class="number">' . $SalesRow['salesquantity'] . '</td> + </tr>'; + $i++; + + $CumulativeTotalSales += $SalesRow['salesvalue']; + $CumulativeTotalRefunds += $SalesRow['returnvalue']; + $CumulativeTotalNetSales += ($SalesRow['salesvalue']+$SalesRow['returnvalue']); + $CumulativeTotalQuantity += $SalesRow['salesquantity']; + + } //loop around category sales for the period + + if ($k==1){ + echo '<tr class="EvenTableRows"><td colspan="8"><hr></td></tr>'; + echo '<tr class="OddTableRows">'; + } else { + echo '<tr class="OddTableRows"><td colspan="8"><hr></td></tr>'; + echo '<tr class="EvenTableRows">'; + } + echo '<td class="number" colspan=3>' . _('GRAND Total') . '</td> + <td class="number">' . number_format($CumulativeTotalSales,2) . '</td> + <td class="number">' . number_format($CumulativeTotalRefunds,2) . '</td> + <td class="number">' . number_format($CumulativeTotalNetSales,2) . '</td> + <td class="number">' . $CumulativeTotalQuantity . '</td> + </tr>'; + + echo '</table>'; + +} //end of if user hit show sales +include('includes/footer.inc'); +?> \ No newline at end of file Modified: trunk/UpgradeDatabase.php =================================================================== --- trunk/UpgradeDatabase.php 2011-05-23 10:20:46 UTC (rev 4573) +++ trunk/UpgradeDatabase.php 2011-05-25 10:30:45 UTC (rev 4574) @@ -109,7 +109,9 @@ case '4.03RC2': case '4.03': case '4.03.2': + case '4.03.3': case '4.03.5': + case '4.03.6': case '4.03.7': $SQLScripts[] = './sql/mysql/upgrade3.11.1-4.00.sql'; case '4.03.8': Modified: trunk/build/make_release.sh =================================================================== --- trunk/build/make_release.sh 2011-05-23 10:20:46 UTC (rev 4573) +++ trunk/build/make_release.sh 2011-05-25 10:30:45 UTC (rev 4574) @@ -67,6 +67,8 @@ msgfmt -o locale/zh_HK.utf8/LC_MESSAGES/messages.mo locale/zh_HK.utf8/LC_MESSAGES/messages.po msgfmt -o locale/vi_VN.utf8/LC_MESSAGES/messages.mo locale/vi_VN.utf8/LC_MESSAGES/messages.po +mysql -u$MYSQL_USER -p$MYSQL_PWD < $BASE_DIR/build/TruncateAuditTrail.sql + echo "SET FOREIGN_KEY_CHECKS = 0;" > $BASE_DIR/sql/mysql/weberp-new.sql mysqldump -u$MYSQL_USER -p$MYSQL_PWD --skip-opt --create-options --skip-set-charset --ignore-table=weberpdemo.mrpsupplies --ignore-table=weberpdemo.mrpplanedorders --ignore-table=weberpdemo.mrpparameters --ignore-table=weberpdemo.levels --ignore-table=weberpdemo.mrprequirements --ignore-table=weberpdemo.audittrail --no-data weberpdemo >> $BASE_DIR/sql/mysql/weberp-new.sql @@ -118,7 +120,6 @@ echo "UPDATE systypes SET typeno=0;" >> $BASE_DIR/sql/mysql/weberp-new.sql echo "INSERT INTO shippers VALUES (1,'Default Shipper',0);" >> $BASE_DIR/sql/mysql/weberp-new.sql echo "UPDATE config SET confvalue='1' WHERE confname='Default_Shipper';" >> $BASE_DIR/sql/mysql/weberp-new.sql -echo "TRUNCATE TABLE audittrail;" >> $BASE_DIR/sql/mysql/weberp-demo.sql echo "SET FOREIGN_KEY_CHECKS = 1;" >> $BASE_DIR/sql/mysql/weberp-demo.sql rm $OUTPUT_DIR/webERP.zip Modified: trunk/config.distrib.php =================================================================== --- trunk/config.distrib.php 2011-05-23 10:20:46 UTC (rev 4573) +++ trunk/config.distrib.php 2011-05-25 10:30:45 UTC (rev 4574) @@ -18,7 +18,7 @@ // The timezone of the business - this allows the possibility of having // the web-server on a overseas machine but record local time // this is not necessary if you have your own server locally -// putenv('TZ=Europe/London'); +putenv('TZ=Europe/London'); // putenv('TZ=Australia/Melbourne'); // putenv('TZ=Australia/Sydney'); // putenv('TZ=Pacific/Auckland'); @@ -86,8 +86,8 @@ if ($rootpath == "/" OR $rootpath == "\\") { $rootpath = ""; } -//$rootpath = '/web-erp'; + /* Report all errors except E_NOTICE This is the default value set in php.ini for most installations but just to be sure it is forced here turning on NOTICES destroys things */ @@ -95,4 +95,4 @@ error_reporting (E_ALL && ~E_NOTICE); /*Make sure there is nothing - not even spaces after this last ?> */ -?> +?> \ No newline at end of file Modified: trunk/doc/Change.log =================================================================== --- trunk/doc/Change.log 2011-05-23 10:20:46 UTC (rev 4573) +++ trunk/doc/Change.log 2011-05-25 10:30:45 UTC (rev 4574) @@ -1,5 +1,7 @@ webERP Change Log +25/5/11 New Sales Inquiry scripts by sales type/price list by category and top sellers +24/5/11 Updated from launchpad translations those that had changed from 18/4/11 including Viatnamese, Albanian, Russian, Spanish 23/5/11 Tim: Remove SystemCheck.php and code standards changes(launchpad 4711-4718) 23/5/11 Tim: Make link from SelectProduct.php to place purchase orders factor in the lead time into the delivery date in the purchase order (launchpad 4710) 23/5/11 Exson: fix PaymentMethods could not add new - comma missing in INSERT SQL Modified: trunk/doc/Manual/ManualContents.php =================================================================== --- trunk/doc/Manual/ManualContents.php 2011-05-23 10:20:46 UTC (rev 4573) +++ trunk/doc/Manual/ManualContents.php 2011-05-25 10:30:45 UTC (rev 4574) @@ -21,7 +21,7 @@ -->';*/ $PathPrefix='../../'; -include($PathPrefix.'includes/session.inc'); +//include($PathPrefix.'includes/session.inc'); include('ManualHeader.html'); ?> Modified: trunk/index.php =================================================================== --- trunk/index.php 2011-05-23 10:20:46 UTC (rev 4573) +++ trunk/index.php 2011-05-25 10:30:45 UTC (rev 4574) @@ -188,6 +188,22 @@ </tr> <tr> <td class="menu_group_item"> + <?php echo '<p>• <a href="' . $rootpath . '/SalesByTypePeriodInquiry.php">' . _('Sales By Sales Type Inquiry') . '</a></p>'; ?> + </td> + </tr> + <tr> + <td class="menu_group_item"> + <?php echo '<p>• <a href="' . $rootpath . '/SalesCategoryPeriodInquiry.php">' . _('Sales By Category Inquiry') . '</a></p>'; ?> + </td> + </tr> + <tr> + <td class="menu_group_item"> + <?php echo '<p>• <a href="' . $rootpath . '/SalesTopItemsInquiry.php">' . _('Top Sellers Inquiry') . '</a></p>'; ?> + </td> + </tr> + + <tr> + <td class="menu_group_item"> <?php echo '<p>• <a href="' . $rootpath . '/PDFDeliveryDifferences.php">' . _('Order Delivery Differences Report') . '</a></p>'; ?> </td> </tr> Modified: trunk/locale/cs_CZ.utf8/LC_MESSAGES/messages.mo =================================================================== (Binary files differ) Modified: trunk/locale/cs_CZ.utf8/LC_MESSAGES/messages.po =================================================================== --- trunk/locale/cs_CZ.utf8/LC_MESSAGES/messages.po 2011-05-23 10:20:46 UTC (rev 4573) +++ trunk/locale/cs_CZ.utf8/LC_MESSAGES/messages.po 2011-05-25 10:30:45 UTC (rev 4574) @@ -7,7 +7,7 @@ msgstr "" "Project-Id-Version: webERP 3.08\n" "Report-Msgid-Bugs-To: \n" -"POT-Creation-Date: 2011-04-29 20:59+1200\n" +"POT-Creation-Date: 2011-05-24 21:53+1200\n" "PO-Revision-Date: 2011-02-07 15:33+0000\n" "Last-Translator: Tim Schofield <Unknown>\n" "Language-Team: Czech <cs...@li...>\n" @@ -21,7 +21,7 @@ "X-Poedit-Language: Czech\n" "X-Poedit-SourceCharset: utf-8\n" -#: AccountGroups.php:7 index.php:1275 +#: AccountGroups.php:7 index.php:1280 msgid "Account Groups" msgstr "Účetní skupiny" @@ -58,8 +58,8 @@ msgid "The account group name cannot contain the character" msgstr "Název účetní skupiny nemůže obsahovat znak" -#: AccountGroups.php:69 AccountSections.php:75 TaxCategories.php:33 -#: TaxProvinces.php:30 UnitsOfMeasure.php:30 +#: AccountGroups.php:69 TaxCategories.php:31 TaxProvinces.php:30 +#: UnitsOfMeasure.php:30 msgid "or the character" msgstr "nebo znak" @@ -101,7 +101,7 @@ msgid "The SQL that was used to update the account group was" msgstr "SQL, který byl použit k aktualizaci účtu skupina byla" -#: AccountGroups.php:137 AccountSections.php:106 PaymentMethods.php:90 +#: AccountGroups.php:137 AccountSections.php:104 PaymentMethods.php:82 msgid "Record Updated" msgstr "Záznam aktualizován" @@ -113,7 +113,7 @@ msgid "The SQL that was used to insert the account group was" msgstr "SQL, který byl použit pro vložení na účet skupina byla" -#: AccountGroups.php:157 AccountSections.php:118 PaymentMethods.php:114 +#: AccountGroups.php:157 AccountSections.php:116 PaymentMethods.php:106 msgid "Record inserted" msgstr "Záznam vložen" @@ -129,24 +129,24 @@ "Nelze smazat tento účet skupiny, protože účtů hlavní knihy byly vytvořeny " "pomocí této skupině" -#: AccountGroups.php:180 AccountGroups.php:190 AccountSections.php:139 -#: Areas.php:117 Areas.php:126 BankAccounts.php:163 CreditStatus.php:126 -#: Currencies.php:144 Currencies.php:152 Currencies.php:159 +#: AccountGroups.php:180 AccountGroups.php:190 AccountSections.php:137 +#: Areas.php:116 Areas.php:125 BankAccounts.php:157 CreditStatus.php:125 +#: Currencies.php:143 Currencies.php:151 Currencies.php:158 #: CustomerBranches.php:286 CustomerBranches.php:296 CustomerBranches.php:306 #: CustomerBranches.php:316 Customers.php:311 Customers.php:320 #: Customers.php:328 Customers.php:336 CustomerTypes.php:147 -#: CustomerTypes.php:157 Factors.php:136 FixedAssetCategories.php:131 +#: CustomerTypes.php:157 Factors.php:134 FixedAssetCategories.php:130 #: GLAccounts.php:80 GLAccounts.php:94 Locations.php:244 Locations.php:252 #: Locations.php:261 Locations.php:269 Locations.php:277 Locations.php:285 #: Locations.php:293 Locations.php:301 MRPDemandTypes.php:87 -#: PaymentMethods.php:148 PaymentTerms.php:147 PaymentTerms.php:154 -#: PcExpenses.php:128 SalesCategories.php:127 SalesCategories.php:135 -#: SalesPeople.php:144 SalesPeople.php:151 SalesTypes.php:147 -#: SalesTypes.php:157 Shippers.php:82 Shippers.php:94 StockCategories.php:179 +#: PaymentMethods.php:140 PaymentTerms.php:147 PaymentTerms.php:154 +#: PcExpenses.php:152 SalesCategories.php:125 SalesCategories.php:133 +#: SalesPeople.php:144 SalesPeople.php:151 SalesTypes.php:145 +#: SalesTypes.php:155 Shippers.php:82 Shippers.php:94 StockCategories.php:179 #: Stocks.php:475 Stocks.php:484 Stocks.php:492 Stocks.php:500 Stocks.php:508 #: Stocks.php:516 Suppliers.php:609 Suppliers.php:618 Suppliers.php:626 -#: SupplierTypes.php:145 TaxCategories.php:133 TaxGroups.php:127 -#: TaxGroups.php:134 TaxProvinces.php:125 UnitsOfMeasure.php:137 +#: SupplierTypes.php:145 TaxCategories.php:131 TaxGroups.php:127 +#: TaxGroups.php:134 TaxProvinces.php:125 UnitsOfMeasure.php:138 #: WorkCentres.php:89 WorkCentres.php:95 WWW_Access.php:83 msgid "There are" msgstr "Jsou zde" @@ -192,35 +192,36 @@ msgid "Could not get account groups because" msgstr "Nemohu získat účetní sk... [truncated message content] |