Menu
▾
▴
[Weberp-cvs] SF.net SVN: web-erp:[2893] trunk
|
From: <dai...@us...> - 2009-11-07 20:17:27
|
Revision: 2893
http://web-erp.svn.sourceforge.net/web-erp/?rev=2893&view=rev
Author: daintree
Date: 2009-11-07 20:17:20 +0000 (Sat, 07 Nov 2009)
Log Message:
-----------
New Daily Sales Inquiry showing predictably the daily sales for a selected month and salesman
Modified Paths:
--------------
trunk/doc/Change.log.html
trunk/includes/DateFunctions.inc
trunk/sql/mysql/upgrade3.10-3.11.sql
Added Paths:
-----------
trunk/DailySalesInquiry.php
Added: trunk/DailySalesInquiry.php
===================================================================
--- trunk/DailySalesInquiry.php (rev 0)
+++ trunk/DailySalesInquiry.php 2009-11-07 20:17:20 UTC (rev 2893)
@@ -0,0 +1,166 @@
+<?php
+
+/* $Revision: 1.00$ */
+
+$PageSecurity = 2;
+
+include('includes/session.inc');
+$title = _('Daily Sales Inquiry');
+include('includes/header.inc');
+
+echo '<p class="page_title_text"><img src="'.$rootpath.'/css/'.$theme.'/images/transactions.png" title="' . _('Daily Sales') . '" alt="">' . ' ' . _('Daily Sales') . '</p>';
+echo '<div class="page_help_text">' . _('Select the month to show daily sales for') . '</div><br>';
+
+echo '<form action="' . $_SERVER['PHP_SELF'] . '" method="post">';
+
+echo '<table cellpadding=2><tr>';
+
+echo '<td>' . _('Month to Show') . ':</td><td><select tabindex=1 name="MonthToShow">';
+
+
+if (!isset($_POST['MonthToShow'])){
+ $_POST['MonthToShow'] = GetPeriod(Date($_SESSION['DefaultDateFormat']),$db);
+}
+
+$PeriodsResult = DB_query('SELECT periodno, lastdate_in_period FROM periods',$db);
+
+while ($PeriodRow = DB_fetch_array($PeriodsResult)){
+ if ($_POST['MonthToShow']==$PeriodRow['periodno']) {
+ echo '<option selected Value="' . $PeriodRow['periodno'] . '">' . MonthAndYearFromSQLDate($PeriodRow['lastdate_in_period']) . '</option>';
+ $EndDateSQL = $PeriodRow['lastdate_in_period'];
+ } else {
+ echo '<option Value="' . $PeriodRow['periodno'] . '">' . MonthAndYearFromSQLDate($PeriodRow['lastdate_in_period']) . '</option>';
+ }
+}
+echo '</select></td>';
+echo '<td>' . _('Salesperson') . ':</td><td><select tabindex=2 name="Salesperson">';
+
+$SalespeopleResult = DB_query('SELECT salesmancode, salesmanname FROM salesman',$db);
+if (!isset($_POST['Salesperson'])){
+ $_POST['Salesperson'] = 'All';
+ echo '<option selected value="All">' . _('All') . '</option>';
+} else {
+ echo '<option value="All">' . _('All') . '</option>';
+}
+while ($SalespersonRow = DB_fetch_array($SalespeopleResult)){
+
+ if ($_POST['Salesperson']==$SalespersonRow['salesmancode']) {
+ echo '<option selected value="' . $SalespersonRow['salesmancode'] . '">' . $SalespersonRow['salesmanname'] . '</option>';
+ } else {
+ echo '<option Value="' . $SalespersonRow['salesmancode'] . '">' . $SalespersonRow['salesmanname'] . '</option>';
+ }
+}
+echo '</select></td>';
+
+echo '</tr></table><div class="centre"><input tabindex=4 type=submit name="ShowResults" VALUE="' . _('Show Daily Sales For The Selected Month') . '">';
+echo '<hr>';
+
+echo '</form></div>';
+/*Now get and display the sales data returned */
+if (strpos($EndDateSQL,'/')) {
+ $Date_Array = explode('/',$EndDateSQL);
+} elseif (strpos ($EndDateSQL,'-')) {
+ $Date_Array = explode('-',$EndDateSQL);
+} elseif (strpos ($EndDateSQL,'.')) {
+ $Date_Array = explode('.',$EndDateSQL);
+}
+
+if (strlen($Date_Array[2])>4) {
+ $Date_Array[2]= substr($Date_Array[2],0,2);
+}
+
+$StartDateSQL = date('Y-m-d', mktime(0,0,0, (int)$Date_Array[1],1,(int)$Date_Array[0]));
+
+$sql = "SELECT trandate,
+ SUM((price*(1-discountpercent) * -qty)) as salesvalue,
+ SUM((standardcost * -qty)) as cost
+ FROM stockmoves
+ INNER JOIN custbranch ON stockmoves.debtorno=custbranch.debtorno
+ AND stockmoves.branchcode=custbranch.branchcode
+ WHERE (stockmoves.type=10 or stockmoves.type=11)
+ AND trandate>='" . $StartDateSQL . "'
+ AND trandate<='" . $EndDateSQL . "'";
+
+if ($_POST['Salesperson']!='All') {
+ $sql .= " AND custbranch.salesman='" . $_POST['Salesperson'] . "'";
+}
+
+$sql .= " GROUP BY stockmoves.trandate ORDER BY stockmoves.trandate";
+
+$ErrMsg = _('The sales data could not be retrieved because') . ' - ' . DB_error_msg($db);
+$SalesResult = DB_query($sql, $db,$ErrMsg,$DbgMsg);
+
+echo '<table cellpadding=2>';
+
+echo'<tr>
+ <th>' . _('Sunday') . '</th>
+ <th>' . _('Monday') . '</th>
+ <th>' . _('Tuesday') . '</th>
+ <th>' . _('Wednesday') . '</th>
+ <th>' . _('Thursday') . '</th>
+ <th>' . _('Friday') . '</th>
+ <th>' . _('Saturday') . '</th></tr>';
+
+$CumulativeTotalSales = 0;
+$BilledDays = 0;
+$DaySalesArray = array();
+while ($DaySalesRow=DB_fetch_array($SalesResult)) {
+ $DaySalesArray[DayOfMonthFromSQLDate($DaySalesRow['trandate'])]->Sales = $DaySalesRow['salesvalue'];
+ $DaySalesArray[DayOfMonthFromSQLDate($DaySalesRow['trandate'])]->GPPercent = ($DaySalesRow['salesvalue']-$DaySalesRow['cost'])/$DaySalesRow['salesvalue'];
+ $BilledDays++;
+ $CumulativeTotalSales += $DaySalesRow['salesvalue'];
+ $CumulativeTotalCost += $DaySalesRow['cost'];
+}
+//end of while loop
+echo '<tr>';
+$ColumnCounter = DayOfWeekFromSQLDate($StartDateSQL);
+for ($i=0;$i<$ColumnCounter;$i++){
+ echo '<td></td>';
+}
+$DayNumber = 1;
+/*Set up day number headings*/
+for ($i=$ColumnCounter;$i<=6;$i++){
+ echo '<th>' . $DayNumber . '</th>';
+ $DayNumber++;
+}
+echo '</tr><tr>';
+for ($i=0;$i<$ColumnCounter;$i++){
+ echo '<td></td>';
+}
+
+$LastDayOfMonth = DayOfMonthFromSQLDate($EndDateSQL);
+
+for ($i=1;$i<=$LastDayOfMonth;$i++){
+ echo '<td class="number">' . number_format($DaySalesArray[$i]->Sales,0) . '<br />' . number_format($DaySalesArray[$i]->GPPercent*100,1) . '%</td>';
+ $ColumnCounter++;
+ if ($ColumnCounter==7){
+ echo '</tr><tr>';
+ for ($j=1;$j<=7;$j++){
+ echo '<th>' . $DayNumber . '</th>';
+ $DayNumber++;
+ if($DayNumber>$LastDayOfMonth){
+ break;
+ }
+ }
+ echo '</tr><tr>';
+ $ColumnCounter=0;
+ }
+}
+if ($ColumnCounter!=0) {
+ echo '</tr><tr>';
+}
+
+if ($CumulativeTotalSales !=0){
+ $AverageGPPercent = ($CumulativeTotalSales - $CumulativeTotalCost)/$CumulativeTotalSales;
+ $AverageDailySales = $CumulativeTotalSales/$BilledDays;
+} else {
+ $AverageGPPercent = 0;
+ $AverageDailySales = 0;
+}
+
+echo '<td colspan=7>' . _('Total Sales for month') . ': ' . number_format($CumulativeTotalSales,0) . ' ' . _('GP%') . ': ' . number_format($AverageGPPercent,1) . ' ' . _('Avg Daily Sales') . ': ' . number_format($AverageDailySales,0) . '</td></tr>';
+
+echo '</table>';
+
+include('includes/footer.inc');
+?>
\ No newline at end of file
Modified: trunk/doc/Change.log.html
===================================================================
--- trunk/doc/Change.log.html 2009-11-07 16:53:54 UTC (rev 2892)
+++ trunk/doc/Change.log.html 2009-11-07 20:17:20 UTC (rev 2893)
@@ -1,5 +1,6 @@
<p><font SIZE=4 COLOR=BLUE><b>webERP Change Log</b></font></p>
<p>
+<p>08/11/09 Phil: New DailySalesInquiry.php - show sales for a selected month - day by day with average sales per billing day and GP% - work sponsored by Manny Neri
<p>07/11/09 Tim: PrintCstTransPortrait.php - Correct $pdf->Output() call to show invoice without saving first
<p>07/11/09 Tim: upgrade3.11-3.12.sql - Remove duplicated line and corrected syntax for consistency
<p>06/11/09 Javier: Added charset declaration to login.css and default.css
Modified: trunk/includes/DateFunctions.inc
===================================================================
--- trunk/includes/DateFunctions.inc 2009-11-07 16:53:54 UTC (rev 2892)
+++ trunk/includes/DateFunctions.inc 2009-11-07 20:17:20 UTC (rev 2893)
@@ -104,7 +104,6 @@
function DayOfWeekFromSQLDate($DateEntry) {
-
if (strpos($DateEntry,'/')) {
$Date_Array = explode('/',$DateEntry);
} elseif (strpos ($DateEntry,'-')) {
@@ -121,7 +120,25 @@
}
+function DayOfMonthFromSQLDate($DateEntry) {
+ if (strpos($DateEntry,'/')) {
+ $Date_Array = explode('/',$DateEntry);
+ } elseif (strpos ($DateEntry,'-')) {
+ $Date_Array = explode('-',$DateEntry);
+ } elseif (strpos ($DateEntry,'.')) {
+ $Date_Array = explode('.',$DateEntry);
+ }
+
+ if (strlen($Date_Array[2])>4) {
+ $Date_Array[2]= substr($Date_Array[2],0,2);
+ }
+
+ return date( 'j', mktime(0,0,0, (int)$Date_Array[1],(int)$Date_Array[2],(int)$Date_Array[0]));
+
+}
+
+
// Returns the timestamp for the financial year end. To find other year ends,
// use $YearIncrement to move back and fgorward. in -1 gives last year end, 1
// gives next year end.
Modified: trunk/sql/mysql/upgrade3.10-3.11.sql
===================================================================
--- trunk/sql/mysql/upgrade3.10-3.11.sql 2009-11-07 16:53:54 UTC (rev 2892)
+++ trunk/sql/mysql/upgrade3.10-3.11.sql 2009-11-07 20:17:20 UTC (rev 2893)
@@ -1,4 +1,3 @@
---Create `mrpdemandtypes` table
CREATE TABLE `mrpdemandtypes` (
`mrpdemandtype` varchar(6) NOT NULL default '',
`description` char(30) NOT NULL default '',
@@ -6,7 +5,6 @@
KEY `mrpdemandtype` (`mrpdemandtype`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
---Create `mrpdemands` table
CREATE TABLE `mrpdemands` (
`demandid` int(11) NOT NULL AUTO_INCREMENT,
`stockid` varchar(20) NOT NULL default '',
@@ -19,11 +17,9 @@
CONSTRAINT `mrpdemands_ibfk_2` FOREIGN KEY (`stockid`) REFERENCES `stockmaster` (`stockid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
---Add `pansize` and `shrinkfactor` columns to stockmaster table
ALTER TABLE `stockmaster` ADD `pansize` double NOT NULL default '0',
ADD `shrinkfactor` double NOT NULL default '0';
---Create `mrpcalendar` table
CREATE TABLE `mrpcalendar` (
calendardate date NOT NULL,
daynumber int(6) NOT NULL,
@@ -32,39 +28,21 @@
PRIMARY KEY (calendardate)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
---Add default value to `mrpdemandtypes` table
+
INSERT INTO mrpdemandtypes (mrpdemandtype,description) VALUES ('FOR','Forecast');
---Add primary key to `geocode_param` table
ALTER TABLE `geocode_param` add PRIMARY KEY (`geocodeid`);
---Alter definition of `geocodeid` field in `geocode_param` table
ALTER TABLE `geocode_param` CHANGE `geocodeid` `geocodeid` TINYINT( 4 ) NOT NULL AUTO_INCREMENT;
-
---Create a unique index in table `factorcompanies` for `coyname` field
CREATE UNIQUE INDEX factor_name ON factorcompanies (coyname);
---Insert default value into `factorcompanies` table
INSERT INTO `factorcompanies` ( `id` , `coyname` ) VALUES (null, 'None');
-
---Alter definition of `role` field in `custcontacts` table
ALTER TABLE `custcontacts` CHANGE `role` `role` VARCHAR( 40 ) NOT NULL;
---Alter definition of `phoneno` field in `custcontacts` table
ALTER TABLE `custcontacts` CHANGE `phoneno` `phoneno` VARCHAR( 20 ) NOT NULL;
---Alter definition of `notes` field in `custcontacts` table
ALTER TABLE `custcontacts` CHANGE `notes` `notes` VARCHAR( 255 ) NOT NULL;
-
---Set `effectivefrom` field to today in `purchdata` where no value currebtly exists
UPDATE `purchdata` SET `effectivefrom`=NOW() WHERE `effectivefrom`='0000-00-00';
---Drop the primary key in the `purchdata` table
ALTER TABLE `purchdata` DROP PRIMARY KEY;
---Create a new primary key in the `purchdata` table
ALTER TABLE `purchdata` ADD PRIMARY KEY (`supplierno`,`stockid`, `effectivefrom`);
-
---Add `quotedate` field to `salesorders` table
ALTER TABLE `salesorders` ADD `quotedate` date NOT NULL default '0000-00-00';
---Add `confirmeddate` field to `salesorders` table
ALTER TABLE `salesorders` ADD `confirmeddate` date NOT NULL default '0000-00-00';
-
---Create `woserialnos` table
CREATE TABLE `woserialnos` (
`wo` INT NOT NULL ,
`stockid` VARCHAR( 20 ) NOT NULL ,
@@ -73,24 +51,13 @@
`qualitytext` TEXT NOT NULL,
PRIMARY KEY (`wo`,`stockid`,`serialno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-
---Create new config value 'AutoCreateWOs'
INSERT INTO config (confname, confvalue) VALUES ('AutoCreateWOs',1);
---Create new config value 'DefaultFactoryLocation'
INSERT INTO config (confname, confvalue) VALUES ('DefaultFactoryLocation','MEL');
---Create new config value 'FactoryManagerEmail'
INSERT INTO config (confname, confvalue) VALUES ('FactoryManagerEmail','ma...@co...');
---Create new config value 'DefineControlledOnWOEntry'
INSERT INTO config (`confname`,`confvalue`) VALUES ('DefineControlledOnWOEntry', '1');
-
---Add column`nextserialno` to `stockmaster` table
ALTER TABLE `stockmaster` ADD `nextserialno` BIGINT NOT NULL DEFAULT '0';
---Alter definition of column `orderno` in `salesorders` table
ALTER TABLE `salesorders` CHANGE `orderno` `orderno` INT( 11 ) NOT NULL;
---Add column`qualitytext` to `stockserialitems` table
ALTER TABLE `stockserialitems` ADD `qualitytext` TEXT NOT NULL;
-
---Create table `purchorderauth`
CREATE TABLE `purchorderauth` (
`userid` varchar(20) NOT NULL DEFAULT '',
`currabrev` char(3) NOT NULL DEFAULT '',
@@ -98,77 +65,35 @@
`authlevel` int(11) NOT NULL DEFAULT 0,
PRIMARY KEY (`userid`,`currabrev`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-
---Add column`version` to `purchorders` table
ALTER TABLE `purchorders` ADD `version` decimal(3,2) NOT NULL default '1.00';
---Add column`revised` to `purchorders` table
ALTER TABLE `purchorders` ADD `revised` date NOT NULL default '0000-00-00';
---Add column`realorderno` to `purchorders` table
ALTER TABLE `purchorders` ADD `realorderno` varchar(16) NOT NULL default '';
---Add column`deliveryby` to `purchorders` table
ALTER TABLE `purchorders` ADD `deliveryby` varchar(100) NOT NULL default '';
---Add column`deliverydate` to `purchorders` table
ALTER TABLE `purchorders` ADD `deliverydate` date NOT NULL default '0000-00-00';
---Add column`status` to `purchorders` table
ALTER TABLE `purchorders` ADD `status` varchar(12) NOT NULL default '';
---Add column`stat_comment` to `purchorders` table
ALTER TABLE `purchorders` ADD `stat_comment` text NOT NULL;
-
---Add column`itemno` to `purchorderdetails` table
ALTER TABLE `purchorderdetails` ADD `itemno` varchar(50) NOT NULL default '';
---Add column`uom` to `purchorderdetails` table
ALTER TABLE `purchorderdetails` ADD `uom` varchar(50) NOT NULL default '';
---Add column`subtotal_amount` to `purchorderdetails` table
ALTER TABLE `purchorderdetails` ADD `subtotal_amount` varchar(50) NOT NULL default '';
---Add column`package` to `purchorderdetails` table
ALTER TABLE `purchorderdetails` ADD `package` varchar(100) NOT NULL default '';
---Add column`pcunit` to `purchorderdetails` table
ALTER TABLE `purchorderdetails` ADD `pcunit` varchar(50) NOT NULL default '';
---Add column`nw` to `purchorderdetails` table
ALTER TABLE `purchorderdetails` ADD `nw` varchar(50) NOT NULL default '';
---Add column`suppliers_partno` to `purchorderdetails` table
ALTER TABLE `purchorderdetails` ADD `suppliers_partno` varchar(50) NOT NULL default '';
---Add column`gw` to `purchorderdetails` table
ALTER TABLE `purchorderdetails` ADD `gw` varchar(50) NOT NULL default '';
---Add column`cuft` to `purchorderdetails` table
ALTER TABLE `purchorderdetails` ADD `cuft` varchar(50) NOT NULL default '';
---Add column`total_quantity` to `purchorderdetails` table
ALTER TABLE `purchorderdetails` ADD `total_quantity` varchar(50) NOT NULL default '';
---Add column`total_amount` to `purchorderdetails` table
ALTER TABLE `purchorderdetails` ADD `total_amount` varchar(50) NOT NULL default '';
-
---Add column`phn` to `suppliers` table
ALTER TABLE `suppliers` ADD `phn` varchar(50) NOT NULL default '';
---Add column`port` to `suppliers` table
ALTER TABLE `suppliers` ADD `port` varchar(200) NOT NULL default '';
-
---Add column`netweight` to `stockmaster` table
ALTER TABLE `stockmaster` ADD `netweight` decimal(20,4) NOT NULL default '0.0000';
-
---Add column`suppliers_partno` to `purchdata` table
ALTER TABLE `purchdata` ADD `suppliers_partno` varchar(50) NOT NULL default '';
-
---Set status of all orders to 'Authorised'
UPDATE `purchorders` SET `status`='Authorised';
---Change status of all orders where allowprint is not set to 'Printed'
UPDATE `purchorders` SET `status`='Printed' WHERE `allowprint`=0;
---Change status of all orders which have been delivered to 'Completed'
UPDATE `purchorders` SET `status`='Completed' WHERE (SELECT SUM(`purchorderdetails`.`completed`)-COUNT(`purchorderdetails`.`podetailitem`) FROM `purchorderdetails` where `purchorderdetails`.`orderno`=`purchorders`.`orderno`)=0;
---Set the delivery date of all orders to the latest line delivery date
UPDATE `purchorders` SET `deliverydate`=(SELECT MAX(`purchorderdetails`.`deliverydate`) FROM `purchorderdetails` WHERE `purchorderdetails`.`orderno`=`purchorders`.`orderno`);
-
---Alter definition of column `note` in `custnotes` table
ALTER TABLE custnotes CHANGE note note TEXT NOT NULL;
-
---Add column`bankaccountcode` to `bankaccounts` table
ALTER TABLE `bankaccounts` ADD `bankaccountcode` varchar(50) NOT NULL default '' AFTER `currcode`;
---Add column`invoice` to `bankaccounts` table
ALTER TABLE `bankaccounts` ADD `invoice` smallint(2) NOT NULL default 0 AFTER `currcode`;
-
---Add column`salesman` to `www_users` table
ALTER TABLE `www_users` ADD `salesman` CHAR( 3 ) NOT NULL AFTER `customerid`;
-
---Alter definition of column `shipvia` in `debtortrans` table
ALTER TABLE debtortrans CHANGE shipvia shipvia int(11) NOT NULL DEFAULT 0;
-
INSERT INTO `config` ( `confname` , `confvalue` ) VALUES ('SalesOrder_FOI', '0');
This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site.
|
