[Weberp-cvs] SF.net SVN: web-erp:[3462] trunk

[<dai...@us...>]

Revision: 3462
          http://web-erp.svn.sourceforge.net/web-erp/?rev=3462&view=rev
Author:   daintree
Date:     2010-05-16 06:11:39 +0000 (Sun, 16 May 2010)

Log Message:
-----------

<p>16/5/10 Phil: reworked PDFPriceList to use the new effective dates fields and print out effective prices as at a specified date - showing effective dates on the report - also ditched includes/PDFPriceListPageHeader.php in favour of a PageHeader() function inside PDFPriceList.php. Also made the script work with tcpdf - not sure how it was missed before?</p>
<p>16/5/10 Phil: reworked PricesBasedOnMarkUp.php to insert new prices with effectivity dates and update the prices where effectivity dates specified.</p>
<p>16/5/10 Phil: reworked PricesByCost.php this was bit of a dodgy script - well I found it hard to follow - in the words of Frank Sinatra - I did it my way! Also built in effectivity dates to display and ensure correct prices updated now the primary key of prices is changed.</p>
<p>16/5/10 Phil: Wrote up the manual so that the logic of pricing with effective dates is explained</p>

Modified Paths:
--------------
    trunk/PDFDIFOT.php
    trunk/PDFPriceList.php
    trunk/PDFStockNegatives.php
    trunk/Prices.php
    trunk/PricesBasedOnMarkUp.php
    trunk/PricesByCost.php
    trunk/Z_PriceChanges.php
    trunk/doc/Change.log.html
    trunk/doc/Manual/ManualPrices.html
    trunk/javascripts/MiscFunctions.js

Removed Paths:
-------------
    trunk/includes/PDFPriceListPageHeader.inc

Modified: trunk/PDFDIFOT.php
===================================================================
--- trunk/PDFDIFOT.php	2010-05-15 10:53:04 UTC (rev 3461)
+++ trunk/PDFDIFOT.php	2010-05-16 06:11:39 UTC (rev 3462)
@@ -265,18 +265,7 @@
 $YPos-=$line_height;
 $LeftOvers = $pdf->addTextWrap($Left_Margin,$YPos,200,$FontSize,_('DIFOT') . ' ' . number_format((1-($TotalDiffs/$myrow[0])) * 100,2) . '%', 'left');
 
-/* UldisN
-$pdfcode = $pdf->output();
-$len = strlen($pdfcode);
-header('Content-type: application/pdf');
-header('Content-Length: ' . $len);
-header('Content-Disposition: inline; filename=DIFOT.pdf');
-header('Expires: 0');
-header('Cache-Control: must-revalidate, post-check=0, pre-check=0');
-header('Pragma: public');
 
-$pdf->stream();
-*/
 $ReportFileName = $_SESSION['DatabaseName'] . '_DIFOT_' . date('Y-m-d').'.pdf';
 $pdf->OutputD($ReportFileName);//UldisN
 $pdf->__destruct(); //UldisN

Modified: trunk/PDFPriceList.php
===================================================================
--- trunk/PDFPriceList.php	2010-05-15 10:53:04 UTC (rev 3461)
+++ trunk/PDFPriceList.php	2010-05-16 06:11:39 UTC (rev 3462)
@@ -16,7 +16,6 @@
 	$pdf->addinfo('Title', _('Price Listing Report') );
 	$pdf->addinfo('Subject', _('Price List') );
 
-
 	$PageNumber=1;
 	$line_height=12;
 
@@ -28,49 +27,62 @@
 			include('includes/header.inc');
 			echo '<br>';
 			prnMsg( _('The customer must first be selected from the select customer link') . '. ' . _('Re-run the price list once the customer has been selected') );
+			echo '<br><br><a href="' . $_SERVER['PHP_SELF'] . '?' . SID . '">' . _('Back') . '</a>';
 			include('includes/footer.inc');
 			exit;
 		}
+		if (!Is_Date($_POST['EffectiveDate'])){
+			$title = _('Special price List - No Customer Selected');
+			include('includes/header.inc');
+			prnMsg(_('The effective date must be entered in the format') . ' ' . $_SESSION['DefaultDateFormat'],'error');
+			echo '<br><br><a href="' . $_SERVER['PHP_SELF'] . '?' . SID . '">' . _('Back') . '</a>';
+			include('includes/footer.inc');
+			exit;
+		}	
 
 		$SQL = "SELECT debtorsmaster.name,
 				debtorsmaster.salestype
-			FROM debtorsmaster
-			WHERE debtorno = '" . $_SESSION['CustomerID'] . "'";
+				FROM debtorsmaster
+				WHERE debtorno = '" . $_SESSION['CustomerID'] . "'";
 		$CustNameResult = DB_query($SQL,$db);
 		$CustNameRow = DB_fetch_row($CustNameResult);
 		$CustomerName = $CustNameRow[0];
 		$SalesType = $CustNameRow[1];
 
 		$SQL = "SELECT prices.typeabbrev,
-			prices.stockid,
-			stockmaster.description,
-			stockmaster.longdescription,
-			prices.currabrev,
-			prices.price,
-			stockmaster.materialcost+stockmaster.labourcost+stockmaster.overheadcost AS standardcost,
-			stockmaster.categoryid,
-			stockcategory.categorydescription,
-			prices.debtorno,
-			prices.branchcode,
-			custbranch.brname
-			FROM stockmaster,
-				stockcategory,
-				prices LEFT JOIN custbranch
-			ON prices.debtorno=custbranch.debtorno
-			AND prices.branchcode=custbranch.branchcode
-			WHERE stockmaster.stockid=prices.stockid
-			AND stockmaster.categoryid=stockcategory.categoryid
-			AND prices.typeabbrev = '" . $SalesType . "'
-			AND stockmaster.categoryid >= '" . $_POST['FromCriteria'] . "'
-			AND stockmaster.categoryid <= '" . $_POST['ToCriteria'] . "'
-			AND prices.debtorno='" . $_SESSION['CustomerID'] . "'
-			ORDER BY prices.currabrev,
-				stockmaster.categoryid,
-				stockmaster.stockid";
+						prices.stockid,
+						stockmaster.description,
+						stockmaster.longdescription,
+						prices.currabrev,
+						prices.startdate,
+						prices.enddate,
+						prices.price,
+						stockmaster.materialcost+stockmaster.labourcost+stockmaster.overheadcost AS standardcost,
+						stockmaster.categoryid,
+						stockcategory.categorydescription,
+						prices.debtorno,
+						prices.branchcode,
+						custbranch.brname
+						FROM stockmaster,
+							stockcategory,
+							prices LEFT JOIN custbranch
+						ON prices.debtorno=custbranch.debtorno
+						AND prices.branchcode=custbranch.branchcode
+						WHERE stockmaster.stockid=prices.stockid
+						AND stockmaster.categoryid=stockcategory.categoryid
+						AND prices.typeabbrev = '" . $SalesType . "'
+						AND stockmaster.categoryid >= '" . $_POST['FromCriteria'] . "'
+						AND stockmaster.categoryid <= '" . $_POST['ToCriteria'] . "'
+						AND prices.debtorno='" . $_SESSION['CustomerID'] . "'
+						AND prices.startdate<='" . FormatDateForSQL($_POST['EffectiveDate']) . "'
+						AND (prices.enddate='0000-00-00' OR prices.enddate>'" . FormatDateForSQL($_POST['EffectiveDate']) . "')
+						ORDER BY prices.currabrev,
+							stockmaster.categoryid,
+							stockmaster.stockid,
+							prices.startdate";
 
 	} else { /* the sales type list only */
 
-
 		$SQL = "SELECT sales_type FROM salestypes WHERE typeabbrev='" . $_POST['SalesType'] . "'";
 		$SalesTypeResult = DB_query($SQL,$db);
 		$SalesTypeRow = DB_fetch_row($SalesTypeResult);
@@ -78,6 +90,8 @@
 
 		$SQL = "SELECT prices.typeabbrev,
 				prices.stockid,
+				prices.startdate,
+				prices.enddate,
 				stockmaster.description,
 				stockmaster.longdescription,
 				prices.currabrev,
@@ -93,10 +107,13 @@
 			AND stockmaster.categoryid >= '" . $_POST['FromCriteria'] . "'
 			AND stockmaster.categoryid <= '" . $_POST['ToCriteria'] . "'
 			AND prices.typeabbrev='" . $_POST['SalesType'] . "'
+			AND prices.startdate<='" . FormatDateForSQL($_POST['EffectiveDate']) . "'
+			AND (prices.enddate='0000-00-00' OR prices.enddate>'" . FormatDateForSQL($_POST['EffectiveDate']) . "')
 			AND prices.debtorno=''
 			ORDER BY prices.currabrev,
 				stockmaster.categoryid,
-				stockmaster.stockid";
+				stockmaster.stockid,
+				prices.startdate";
 	}
 	$PricesResult = DB_query($SQL,$db,'','',false,false);
 
@@ -106,13 +123,22 @@
 		prnMsg( _('The Price List could not be retrieved by the SQL because'). ' - ' . DB_error_msg($db), 'error');
 		echo '<br><a href="' .$rootpath .'/index.php?' . SID . '">'.  _('Back to the menu'). '</a>';
 		if ($debug==1){
-			echo '<br>'. $SQL;
+			prnMsg(_('For debugging purposes the SQL used was:') . $SQL,'error');
 		}
 		include('includes/footer.inc');
 		exit;
 	}
-
-	include('includes/PDFPriceListPageHeader.inc');
+	if (DB_num_rows($PricesResult)==0){
+		$title = _('Print Price List Error');
+		include('includes/header.inc');
+		prnMsg(_('There were no price details to print out for the customer or category specified'),'warn');
+		echo '<br><a href="'.$_SERVER['PHP_SELF'] .'?' . SID . '">'. _('Back').'</a>';
+		include('includes/footer.inc');
+		exit;
+	}
+	
+	PageHeader();
+	
 	$CurrCode ='';
 	$Category = '';
 	$CatTot_Val=0;
@@ -122,28 +148,33 @@
 
 		if ($CurrCode != $PriceList['currabrev']){
 			$FontSize=10;
-			$YPos -= $line_height;
+			$YPos -= 2*$line_height;
 			$LeftOvers = $pdf->addTextWrap($Left_Margin,$YPos,300-$Left_Margin,$FontSize, $PriceList['currabrev'] . ' ' . _('Prices'));
 			$CurrCode = $PriceList['currabrev'];
 			$FontSize = 8;
-			$YPos -= $line_height;
 		}
 
 		if ($Category!=$PriceList['categoryid']){
 			$FontSize=10;
-			$YPos -= $line_height;
+			$YPos -= 2*$line_height;
 			$LeftOvers = $pdf->addTextWrap($Left_Margin,$YPos,300-$Left_Margin,$FontSize,$PriceList['categoryid'] . ' - ' . $PriceList['categorydescription']);
 			$Category = $PriceList['categoryid'];
 			$CategoryName = $PriceList['categorydescription'];
 			$FontSize=8;
-			$YPos -=$line_height;
 		}
 
 		$YPos -=$line_height;
-		$LeftOvers = $pdf->addTextWrap($Left_Margin,$YPos,60,$FontSize,$PriceList['stockid']);
-		$LeftOvers = $pdf->addTextWrap(190,$YPos,260,$FontSize,$PriceList['description']);
+		$LeftOvers = $pdf->addTextWrap($Left_Margin,$YPos,80,$FontSize,$PriceList['stockid']);
+		$LeftOvers = $pdf->addTextWrap($Left_Margin+80,$YPos,47,$FontSize,ConvertSQLDate($PriceList['startdate']));
+		if ($PriceList['enddate']!='0000-00-00'){
+			$DisplayEndDate = ConvertSQLDate($PriceList['enddate']);
+		} else {
+			$DisplayEndDate = _('No End Date');
+		}
+		$LeftOvers = $pdf->addTextWrap($Left_Margin+80+47,$YPos,47,$FontSize,$DisplayEndDate);
+		$LeftOvers = $pdf->addTextWrap($Left_Margin+80+47+47,$YPos,130,$FontSize,$PriceList['description']);
 		$DisplayUnitPrice = number_format($PriceList['price'],2);
-		$LeftOvers = $pdf->addTextWrap(440,$YPos,60,$FontSize,$DisplayUnitPrice, 'right');
+		$LeftOvers = $pdf->addTextWrap($Left_Margin+80+47+47+130,$YPos,60,$FontSize,$DisplayUnitPrice, 'right');
 
 		if ($PriceList['price']!=0){
 			$DisplayGPPercent = (int)(($PriceList['price']-$PriceList['standardcost'])*100/$PriceList['price']) . '%';
@@ -152,58 +183,53 @@
 		}
 
 		if ($_POST['ShowGPPercentages']=='Yes'){
-			$LeftOvers = $pdf->addTextWrap(530,$YPos,20,$FontSize,$DisplayGPPercent, 'right');
+			$LeftOvers = $pdf->addTextWrap($Left_Margin+80+47+47+130+65,$YPos,20,$FontSize,$DisplayGPPercent, 'right');
 		}
 
 		if ($_POST['CustomerSpecials']=='Customer Special Prices Only'){
 			/*Need to show to which branch the price relates */
 			if ($PriceList['branchcode']!=''){
-				$LeftOvers = $pdf->addTextWrap(320,$YPos,130,$FontSize,$PriceList['brname'],'left');
+				$LeftOvers = $pdf->addTextWrap($Left_Margin+80+47+47+130+65+25,$YPos,60,$FontSize,$PriceList['brname'],'left');
 			} else {
-				$LeftOvers = $pdf->addTextWrap(320,$YPos,130,$FontSize,_('All'),'left');
+				$LeftOvers = $pdf->addTextWrap($Left_Margin+80+47+47+130+65+25,$YPos,60,$FontSize,_('All'),'left');
 			}
 
-		}else If ($_POST['CustomerSpecials']=='Full Description'){
+		} else If ($_POST['CustomerSpecials']=='Full Description'){
 
-		if(file_exists($_SESSION['part_pics_dir'] . '/' .$PriceList['stockid'].'.jpg') ) {
-		$img = imagecreatefromjpeg($_SESSION['part_pics_dir'] . '/' .$PriceList['stockid'].'.jpg');
-		$width = imagesx( $img );
-		$height = imagesy( $img );
+			if(file_exists($_SESSION['part_pics_dir'] . '/' .$PriceList['stockid'].'.jpg') ) {
+				$img = imagecreatefromjpeg($_SESSION['part_pics_dir'] . '/' .$PriceList['stockid'].'.jpg');
+				$width = imagesx( $img );
+				$height = imagesy( $img );
 				if($width>$height){
 					$LeftOvers = $pdf->Image($_SESSION['part_pics_dir'] . '/'.$PriceList['stockid'].'.jpg',120,$Page_Height-$Top_Margin-$YPos+20,50,0);
 				}else{
 					$LeftOvers = $pdf->Image($_SESSION['part_pics_dir'] . '/'.$PriceList['stockid'].'.jpg',120,$Page_Height-$Top_Margin-$YPos+20,0,40);
 				}
-		}/*end checked file exist*/
+			}/*end checked file exist*/
+	
+			$Split = explode("\r\n", $PriceList['longdescription']);
+			$FontSize2=6;
+			if ($YPos < ($Bottom_Margin + (count($Split)*$line_height))){
+		 	  PageHeader();
+			}
+			for ($i=0; $i<=count($Split); $i++)	{
+				if(count($Split)==1){
+					$YPos -=(1*$line_height);
+					$LeftOvers = $pdf->addTextWrap(140,$YPos,300,$FontSize2,$Split[$i]);
+					$YPos -=(1*$line_height);
+					$LeftOvers = $pdf->addTextWrap(140,$YPos,300,$FontSize2,'');
+				}elseif(count($Split)==2){
+					$YPos -=(1*$line_height);
+					$LeftOvers = $pdf->addTextWrap(140,$YPos,300,$FontSize2,$Split[$i]);
+				}elseif(count($Split)>=3){
+					$YPos -=(1*$line_height);
+					$LeftOvers = $pdf->addTextWrap(140,$YPos,300,$FontSize2,$Split[$i]);
+				}
+			}/*end for*/
+		}/*end if full descriptions*/
 
-		$Split = explode("\r\n", $PriceList['longdescription']);
-		$FontSize2=6;
-
-					for ($i=0; $i<=count($Split); $i++)
-					{
-								if(count($Split)==1){
-								$YPos -=(1*$line_height);
-								$LeftOvers = $pdf->addTextWrap(190,$YPos,260,$FontSize2,$Split[$i]);
-								$YPos -=(1*$line_height);
-								$LeftOvers = $pdf->addTextWrap(190,$YPos,260,$FontSize2,'');
-								}elseif(count($Split)==2){
-								$YPos -=(1*$line_height);
-								$LeftOvers = $pdf->addTextWrap(190,$YPos,260,$FontSize2,$Split[$i]);
-								}elseif(count($Split)>=3){
-								$YPos -=(1*$line_height);
-								$LeftOvers = $pdf->addTextWrap(190,$YPos,260,$FontSize2,$Split[$i]);
-								}
-
-
-					}/*end for*/
-
-		}/*end if*/
-
-
-
 		if ($YPos < $Bottom_Margin + $line_height){
-		   include('includes/PDFPriceListPageHeader.inc');
-
+		   PageHeader();
 		}
 
 	} /*end inventory valn while loop */
@@ -211,26 +237,10 @@
 	$FontSize =10;
 /*Print out the category totals */
 
-	$pdfcode = $pdf->output();
-	$len = strlen($pdfcode);
+	$FileName=$_SESSION['DatabaseName']. '_' . _('Price_List') . '_' . date('Y-m-d').'.pdf';
+	$pdf->OutputD($FileName);
+	$pdf->__destruct();
 
-      if ($len<=20){
-		$title = _('Print Price List Error');
-		include('includes/header.inc');
-		prnMsg(_('There were no price details to print out for the customer or category specified'),'warn');
-		echo '<br><a href="'.$rootpath.'/index.php?' . SID . '">'. _('Back to the menu').'</a>';
-		include('includes/footer.inc');
-		exit;
-      } else {
-		header('Content-type: application/pdf');
-		header('Content-Length: ' . $len);
-		header('Content-Disposition: inline; filename=PriceList.pdf');
-		header('Expires: 0');
-		header('Cache-Control: must-revalidate, post-check=0, pre-check=0');
-		header('Pragma: public');
-		$FileName=$_SESSION['DatabaseName'].'_'.date('Y-m-d').'.pdf';
-		$pdf->Output($FileName,'I');
-	}
 } else { /*The option to print PDF was not hit */
 
 	$title= _('Price Listing');
@@ -280,10 +290,77 @@
 		echo '<option Value="Customer Special Prices Only">'. _('Customer Special Prices Only');
 		echo '<option Value="Full Description">'. _('Full Description');
 		echo '</select></td></tr>';
+		
+		echo '<tr><td>' . _('Effective As At') . ':</td><td><input type="text" class="date"	alt="' . $_SESSION['DefaultDateFormat'] . '" name="EffectiveDate" value="' . Date($_SESSION['DefaultDateFormat']) . '">';
 
 		echo '</table><div class="centre"><input type=Submit Name="PrintPDF" Value="'. _('Print PDF'). '"></div>';
 	}
 	include('includes/footer.inc');
 
 } /*end of else not PrintPDF */
+
+function PageHeader () {
+	global $pdf;
+	global $PageNumber;
+	global $YPos;
+	global $Xpos;
+	global $line_height;
+	global $Page_Height;
+	global $Top_Margin;
+	global $Page_Width;
+	global $Right_Margin;
+	global $Left_Margin;
+	global $Bottom_Margin;
+	global $FontSize;
+	global $SalesTypeName;
+	global $CustomerName;
+	
+	if ($PageNumber>1){
+		$pdf->newPage();
+	}
+	
+	$FontSize=10;
+	$YPos= $Page_Height-$Top_Margin;
+	
+	$LeftOvers = $pdf->addTextWrap($Left_Margin,$YPos,300,$FontSize,$_SESSION['CompanyRecord']['coyname']);
+	$LeftOvers = $pdf->addTextWrap($Page_Width-$Right_Margin-140,$YPos,140,$FontSize, _('Printed').': ' . Date($_SESSION['DefaultDateFormat']) . '   '. _('Page'). ' ' . $PageNumber);
+	
+	$YPos -=$line_height;
+	//Note, this is ok for multilang as this is the value of a Select, text in option is different
+	if ($_POST['CustomerSpecials']==_('Customer Special Prices Only')){
+		$LeftOvers = $pdf->addTextWrap($Left_Margin,$YPos,550,$FontSize, $CustomerName . ' ' . _('Prices for Categories').' ' . $_POST['FromCriteria'] . ' - ' . $_POST['ToCriteria'] . ' ' . _('Effective As At') . ' ' . $_POST['EffectiveDate']);
+	} else {
+		$LeftOvers = $pdf->addTextWrap($Left_Margin,$YPos,550,$FontSize, $SalesTypeName . ' ' ._('Prices For Categories') . ' ' . $_POST['FromCriteria'] . ' - ' . $_POST['ToCriteria'] . ' ' . _('Effective As At') . ' ' . $_POST['EffectiveDate'] );
+	}
+		
+	$YPos -=(2*$line_height);
+	/*Draw a rectangle to put the headings in     */
+	
+	$pdf->line($Left_Margin, $YPos+$line_height,$Page_Width-$Right_Margin, $YPos+$line_height);
+	$pdf->line($Left_Margin, $YPos+$line_height,$Left_Margin, $YPos- $line_height);
+	$pdf->line($Left_Margin, $YPos- $line_height,$Page_Width-$Right_Margin, $YPos- $line_height);
+	$pdf->line($Page_Width-$Right_Margin, $YPos+$line_height,$Page_Width-$Right_Margin, $YPos- $line_height);
+	
+	/*set up the headings */
+	$Xpos = $Left_Margin+1;
+	
+	$LeftOvers = $pdf->addTextWrap($Xpos,$YPos,60,$FontSize, _('Category') . '/' . _('Item'), 'left');
+	$LeftOvers = $pdf->addTextWrap($Xpos+70,$YPos,60,$FontSize, _('Effective Date Range'), 'left');
+	
+	if ($_POST['CustomerSpecials']==_('Customer Special Prices Only')){
+		$LeftOvers = $pdf->addTextWrap($Left_Margin+80+47+47+130+65+25,$YPos,60,$FontSize, _('Branch'), 'centre');
+	}
+	
+	$LeftOvers = $pdf->addTextWrap($Left_Margin+80+47+47+130+20,$YPos,60,$FontSize, _('Price') , 'centre');
+	
+	if ($_POST['ShowGPPercentages']=='Yes'){
+		$LeftOvers = $pdf->addTextWrap($Left_Margin+80+47+47+130+65,$YPos,20,$FontSize, _('GP') .'%', 'centre');
+	}
+
+	$FontSize=8;
+	$YPos -= (1.5 * $line_height);
+
+	$PageNumber++;	
+}	
+
 ?>
\ No newline at end of file

Modified: trunk/PDFStockNegatives.php
===================================================================
--- trunk/PDFStockNegatives.php	2010-05-15 10:53:04 UTC (rev 3461)
+++ trunk/PDFStockNegatives.php	2010-05-16 06:11:39 UTC (rev 3462)
@@ -60,29 +60,7 @@
 	}
 
 } while ($NegativesRow = DB_fetch_array($result));
-/*
-$pdfcode = $pdf->output();
-$len = strlen($pdfcode);
 
-
-if ($len<=10){
-	include('includes/header.inc');
-	echo '<p>';
-	prnMsg( _('There was no negative stocks to print out'), 'warn');
-	echo '<br><a href="' . $rootpath. '/index.php?' . SID . '">'. _('Back to the menu'). '</a>';
-	include('includes/footer.inc');
-	exit;
-} else {
-	header('Content-type: application/pdf');
-	header('Content-Length: ' . $len);
-	header('Content-Disposition: inline; filename=NegativeStocks.pdf');
-	header('Expires: 0');
-	header('Cache-Control: must-revalidate, post-check=0, pre-check=0');
-	header('Pragma: public');
-
-	$pdf->Output('PDFStockNegatives.pdf', 'I');
-}
-*/
 $pdf->OutputD($_SESSION['DatabaseName'] . '_NegativeStocks_' . date('Y-m-d') . '.pdf');//UldisN
 $pdf->__destruct(); //UldisN
 ?>

Modified: trunk/Prices.php
===================================================================
--- trunk/Prices.php	2010-05-15 10:53:04 UTC (rev 3461)
+++ trunk/Prices.php	2010-05-16 06:11:39 UTC (rev 3462)
@@ -69,7 +69,7 @@
 	ie the page has called itself with some user input */
 
 	//first off validate inputs sensible
-	$ZeroDate = Date($_SESSION['DefaultDateFormat'],Mktime(0,0,0,0,0,0));
+	// This gives some date in 1999?? $ZeroDate = Date($_SESSION['DefaultDateFormat'],Mktime(0,0,0,0,0,0));
 	
 	if (!is_double((double) trim($_POST['Price'])) OR $_POST['Price']=="") {
 		$InputError = 1;
@@ -83,14 +83,11 @@
 		$InputError =1;
 		prnMsg (_('The date this price is be in effect to must be entered in the format') . ' ' . $_SESSION['DefaultDateFormat'],'error');
 	}
-	if (Date1GreaterThanDate2($_POST['StartDate'],$_POST['EndDate']) AND $_POST['EndDate']!='' AND $_POST['EndDate']!=$ZeroDate){
+	if (Date1GreaterThanDate2($_POST['StartDate'],$_POST['EndDate']) AND $_POST['EndDate']!=''){
 		$InputError =1;
-		
-		echo 'Debug - start date ' . $_POST['StartDate'] . ' end date: ' . $_POST['EndDate'];
-		
 		prnMsg (_('The end date is expected to be after the start date, enter an end date after the start date for this price'),'error');
 	}
-	if (Date1GreaterThanDate2(Date($_SESSION['DefaultDateFormat']),$_POST['EndDate']) AND $_POST['EndDate']!='' AND $_POST['EndDate']!=$ZeroDate){
+	if (Date1GreaterThanDate2(Date($_SESSION['DefaultDateFormat']),$_POST['EndDate']) AND $_POST['EndDate']!=''){
 		$InputError =1;
 		prnMsg(_('The end date is expected to be after today. There is no point entering a new price where the effective date is before today!'),'error');
 	}
@@ -253,7 +250,7 @@
 				$myrow['sales_type'],
 				$myrow['price'],
 				ConvertSQLDate($myrow['startdate']),
-				$EndDateDisplay;
+				$EndDateDisplay);
 		}
 
 	}

Modified: trunk/PricesBasedOnMarkUp.php
===================================================================
--- trunk/PricesBasedOnMarkUp.php	2010-05-15 10:53:04 UTC (rev 3461)
+++ trunk/PricesBasedOnMarkUp.php	2010-05-16 06:11:39 UTC (rev 3462)
@@ -1,14 +1,14 @@
 <?php
 /* $Revision: 1.9 $ */
 /* $Id$*/
-$PageSecurity=2;
+$PageSecurity=11;
 
 
 include('includes/session.inc');
 $title=_('Update Pricing From Costs');
 include('includes/header.inc');
 
-echo '<br><div class="page_help_text">' . _('This page adds new prices or updates already existing prices for a specified sales type (price list) and currency for the stock category selected - based on a percentage mark up from cost prices or from preferred supplier cost data. This script does not care about the dates when prices are effective from and to - it will update all prices for the selections made irrespective of effectivity dates.') . '</div><br><div class="centre">';
+echo '<br><div class="page_help_text">' . _('This page adds new prices or updates already existing prices for a specified sales type (price list) and currency for the stock category selected - based on a percentage mark up from cost prices or from preferred supplier cost data.') . '</div><br><div class="centre">';
 
 echo "<form method='POST' action='" . $_SERVER['PHP_SELF'] . '?' . SID . "'>";
 
@@ -17,9 +17,9 @@
 $PricesResult = DB_query($SQL,$db);
 
 echo '<p><table>
-                        <tr>
-                            <td>' . _('Select the Price List to update') .':</td>
-                            <td><select name="PriceList">';
+             <tr>
+          	   <td>' . _('Select the Price List to update') .':</td>
+               <td><select name="PriceList">';
 
 if (!isset($_POST['PriceList']) OR $_POST['PriceList']=='0'){
 	echo '<option selected VALUE="0">' . _('No Price List Selected');
@@ -69,7 +69,7 @@
      echo ' <option selected value="PreferredSupplier">' . _('Preferred Supplier Cost Data') . '</option>
             <option value="StandardCost">' . $CostingBasis . '</option>
             <option value="OtherPriceList">' . _('Another Price List') . '</option>';
-}elseif ($_POST['CostType']=='StandardCost'){
+} elseif ($_POST['CostType']=='StandardCost'){
 	 echo ' <option value="PreferredSupplier">' . _('Preferred Supplier Cost Data') . '</option>
             <option selected value="StandardCost">' . $CostingBasis . '</option>
             <option value="OtherPriceList">' . _('Another Price List') . '</option>';
@@ -138,6 +138,12 @@
 echo '<tr><td>' . _('Rounding Factor') . ':</td>
                 <td><input type=text class=number name="RoundingFactor" size="6" maxlength="6" value=' . $_POST['RoundingFactor'] . '></td></tr>';
 
+echo '<tr><td>' . _('New Price To Be Effective From') . ':</td>
+                <td><input type=text class=date alt="' . $_SESSION['DefaultDateFormat'] . '" name="PriceStartDate" size="10" maxlength="10" value="' . $_POST['PriceStartDate'] . '"></td></tr>';
+
+echo '<tr><td>' . _('New Price To Be Effective To (Blank = No End Date)') . ':</td>
+                <td><input type=text class=date alt="' . $_SESSION['DefaultDateFormat'] . '" name="PriceEndDate" size="10" maxlength="10" value="' . $_POST['PriceEndDate'] . '"></td></tr>';
+
 if (!isset($_POST['IncreasePercent'])){
 	$_POST['IncreasePercent']=0;
 }
@@ -146,158 +152,194 @@
                 <td><input type=text name='IncreasePercent' class=number size=4 maxlength=4 VALUE=" . $_POST['IncreasePercent'] . "></td></tr></table>";
 
 
-echo "<p><div class='centre'><input type=submit name='UpdatePrices' VALUE='" . _('Update Prices') . '\'  onclick="return confirm(\'' . _('Are you sure you wish to update all the prices according to the criteria selected?') . '\');"></div>';
+echo "<p><div class='centre'><input type=submit name='UpdatePrices' VALUE='" . _('Update Prices') . '\'  onclick="return confirm(\'' . _('Are you sure you wish to update or add all the prices according to the criteria selected?') . '\');"></div>';
 
 echo '</form>';
 
-$InputError =0; //assume the best
-if (isset($_POST['UpdatePrices']) AND $_POST['PriceList']=='0'){
-	prnMsg(_('No price list is selected to update. No updates will take place'),'error');
-	$InputError =1;
-}
-if (isset($_POST['UpdatePrices']) AND $_POST['CurrCode']=='0'){
-	prnMsg(_('No price list currency is selected to update. No updates will take place'),'error');
-	$InputError =1;
-}
-
-if (isset($_POST['UpdatePrices']) AND $_POST['StkCatTo']<$_POST['StkCatFrom']){
-	prnMsg(_('The stock category from must be before the stock category to - there would be not items in the range to update'),'error');
-	$InputError =1;
-}
-if (isset($_POST['UpdatePrices']) AND $_POST['CostType']=='OtherPriceList' AND $_POST['BasePriceList']=='0'){
-	echo '<br>Base price list selected: ' .$_POST['BasePriceList'];
-	prnMsg(_('When you are updating prices based on another price list - the other price list must also be selected. No updates will take place until the other price list is selected'),'error');
-	$InputError =1;
-}
-if (isset($_POST['UpdatePrices']) AND $_POST['CostType']=='OtherPriceList' AND $_POST['BasePriceList']==$_POST['PriceList']){
-	prnMsg(_('When you are updating prices based on another price list - the other price list cannot be the same as the price list being used for the calculation. No updates will take place until the other price list selected is different from the price list to be updated' ),'error');
-	$InputError =1;
-}
-
-if (isset($_POST['UpdatePrices']) AND $InputError==0) {
-	echo '<br>' . _('So we are using a price list/sales type of') .' : ' . $_POST['PriceList'];
-	echo '<br>' . _('updating only prices in') . ' : ' . $_POST['CurrCode'];
-	echo '<br>' . _('and the stock category range from') . ' : ' . $_POST['StkCatFrom'] . ' ' . _('to') . ' ' . $_POST['StkCatTo'];
-	echo '<br>' . _('and we are applying a markup percent of') . ' : ' . $_POST['IncreasePercent'];
-	echo '<br>' . _('against') . ' ';
-
-	if ($_POST['CostType']=='PreferredSupplier'){
-		echo _('Preferred Supplier Cost Data');
-	} elseif ($_POST['CostType']=='OtherPriceList') {
-		echo _('Price List')  . ' ' . $_POST['BasePriceList'];
-	} else {
-		echo $CostingBasis;
-	}
-
+if (isset($_POST['UpdatePrices'])){
+	$InputError =0; //assume the best
 	if ($_POST['PriceList']=='0'){
-		echo '<br>' . _('The price list/sales type to be updated must be selected first');
-		include ('includes/footer.inc');
-		exit;
+		prnMsg(_('No price list is selected to update. No updates will take place'),'error');
+		$InputError =1;
 	}
 	if ($_POST['CurrCode']=='0'){
-		echo '<br>' . _('The currency of prices to be updated must be selected first');
-		include ('includes/footer.inc');
-		exit;
+		prnMsg(_('No price list currency is selected to update. No updates will take place'),'error');
+		$InputError =1;
 	}
-
-	$sql = "SELECT stockid,
-					materialcost+labourcost+overheadcost AS cost
-			FROM stockmaster
-			WHERE categoryid>='" . $_POST['StkCatFrom'] . "'
-			AND categoryid <='" . $_POST['StkCatTo'] . "'";
-	$PartsResult = DB_query($sql,$db);
-
-	$IncrementPercentage = $_POST['IncreasePercent']/100;
-
-	$CurrenciesResult = DB_query("SELECT rate FROM currencies WHERE currabrev='" . $_POST['CurrCode'] . "'",$db);
-	$CurrencyRow = DB_fetch_row($CurrenciesResult);
-	$CurrencyRate = $CurrencyRow[0];
-
-	while ($myrow=DB_fetch_array($PartsResult)){
-
-//Figure out the cost to use
+	
+	if (! Is_Date($_POST['PriceEndDate']) AND $_POST['PriceEndDate']!=''){
+		$InputError =1;
+		prnMsg (_('The date the new price is to be in effect to must be entered in the format') . ' ' . $_SESSION['DefaultDateFormat'],'error');
+	}
+	if (! Is_Date($_POST['PriceStartDate'])){
+		$InputError =1;
+		prnMsg (_('The date this price is to take effect from must be entered in the format') . ' ' . $_SESSION['DefaultDateFormat'],'error');
+	}
+	if (Date1GreaterThanDate2($_POST['PriceStartDate'],$_POST['PriceEndDate']) AND $_POST['PriceEndDate']!=''){
+		$InputError =1;
+		prnMsg (_('The end date is expected to be after the start date, enter an end date after the start date for this price'),'error');
+	}
+	if (Date1GreaterThanDate2(Date($_SESSION['DefaultDateFormat']),$_POST['PriceStartDate'])){
+		$InputError =1;
+		prnMsg(_('The date this new price is to start from is expected to be after today'),'error');
+	}	
+	if ($_POST['StkCatTo']<$_POST['StkCatFrom']){
+		prnMsg(_('The stock category from must be before the stock category to - there would be not items in the range to update'),'error');
+		$InputError =1;
+	}
+	if ($_POST['CostType']=='OtherPriceList' AND $_POST['BasePriceList']=='0'){
+		echo '<br>Base price list selected: ' .$_POST['BasePriceList'];
+		prnMsg(_('When you are updating prices based on another price list - the other price list must also be selected. No updates will take place until the other price list is selected'),'error');
+		$InputError =1;
+	}
+	if ($_POST['CostType']=='OtherPriceList' AND $_POST['BasePriceList']==$_POST['PriceList']){
+		prnMsg(_('When you are updating prices based on another price list - the other price list cannot be the same as the price list being used for the calculation. No updates will take place until the other price list selected is different from the price list to be updated' ),'error');
+		$InputError =1;
+	}
+	
+	if ($InputError==0) {
+		prnMsg(_('For a log of all the prices changed this page should be printed with CTRL+P'),'info');
+		echo '<br>' . _('So we are using a price list/sales type of') .' : ' . $_POST['PriceList'];
+		echo '<br>' . _('updating only prices in') . ' : ' . $_POST['CurrCode'];
+		echo '<br>' . _('and the stock category range from') . ' : ' . $_POST['StkCatFrom'] . ' ' . _('to') . ' ' . $_POST['StkCatTo'];
+		echo '<br>' . _('and we are applying a markup percent of') . ' : ' . $_POST['IncreasePercent'];
+		echo '<br>' . _('against') . ' ';
+	
 		if ($_POST['CostType']=='PreferredSupplier'){
-			$sql = "SELECT purchdata.price/purchdata.conversionfactor/currencies.rate AS cost
-						FROM purchdata INNER JOIN suppliers
-							ON purchdata.supplierno=suppliers.supplierid
-							INNER JOIN currencies
-							ON suppliers.currcode=currencies.currabrev
-						WHERE purchdata.preferred=1 AND purchdata.stockid='" . $myrow['stockid'] ."'";
-			$ErrMsg = _('Could not get the supplier purchasing information for a preferred supplier for the item') . ' ' . $myrow['stockid'];
-			$PrefSuppResult = DB_query($sql,$db,$ErrMsg);
-			if (DB_num_rows($PrefSuppResult)==0){
-				prnMsg(_('There is no preferred supplier data for the item') . ' ' . $myrow['stockid'] . ' ' . _('prices will not be updated for this item'),'warn');
-				$Cost = 0;
-			} elseif(DB_num_rows($PrefSuppResult)>1) {
-				prnMsg(_('There is more than a single preferred supplier data for the item') . ' ' . $myrow['stockid'] . ' ' . _('prices will not be updated for this item'),'warn');
-				$Cost = 0;
+			echo _('Preferred Supplier Cost Data');
+		} elseif ($_POST['CostType']=='OtherPriceList') {
+			echo _('Price List')  . ' ' . $_POST['BasePriceList'];
+		} else {
+			echo $CostingBasis;
+		}
+	
+		if ($_POST['PriceList']=='0'){
+			echo '<br>' . _('The price list/sales type to be updated must be selected first');
+			include ('includes/footer.inc');
+			exit;
+		}
+		if ($_POST['CurrCode']=='0'){
+			echo '<br>' . _('The currency of prices to be updated must be selected first');
+			include ('includes/footer.inc');
+			exit;
+		}
+		if (Is_Date($_POST['PriceEndDate'])){
+			$SQLEndDate = FormatDateForSQL($_POST['PriceEndDate']);
+		} else {
+			$SQLEndDate = '0000-00-00';
+		}	
+		$sql = "SELECT stockid,
+						materialcost+labourcost+overheadcost AS cost
+				FROM stockmaster
+				WHERE categoryid>='" . $_POST['StkCatFrom'] . "'
+				AND categoryid <='" . $_POST['StkCatTo'] . "'";
+		$PartsResult = DB_query($sql,$db);
+	
+		$IncrementPercentage = $_POST['IncreasePercent']/100;
+	
+		$CurrenciesResult = DB_query("SELECT rate FROM currencies WHERE currabrev='" . $_POST['CurrCode'] . "'",$db);
+		$CurrencyRow = DB_fetch_row($CurrenciesResult);
+		$CurrencyRate = $CurrencyRow[0];
+	
+		while ($myrow=DB_fetch_array($PartsResult)){
+	
+	//Figure out the cost to use
+			if ($_POST['CostType']=='PreferredSupplier'){
+				$sql = "SELECT purchdata.price/purchdata.conversionfactor/currencies.rate AS cost
+							FROM purchdata INNER JOIN suppliers
+								ON purchdata.supplierno=suppliers.supplierid
+								INNER JOIN currencies
+								ON suppliers.currcode=currencies.currabrev
+							WHERE purchdata.preferred=1 AND purchdata.stockid='" . $myrow['stockid'] ."'";
+				$ErrMsg = _('Could not get the supplier purchasing information for a preferred supplier for the item') . ' ' . $myrow['stockid'];
+				$PrefSuppResult = DB_query($sql,$db,$ErrMsg);
+				if (DB_num_rows($PrefSuppResult)==0){
+					prnMsg(_('There is no preferred supplier data for the item') . ' ' . $myrow['stockid'] . ' ' . _('prices will not be updated for this item'),'warn');
+					$Cost = 0;
+				} elseif(DB_num_rows($PrefSuppResult)>1) {
+					prnMsg(_('There is more than a single preferred supplier data for the item') . ' ' . $myrow['stockid'] . ' ' . _('prices will not be updated for this item'),'warn');
+					$Cost = 0;
+				} else {
+					$PrefSuppRow = DB_fetch_row($PrefSuppResult);
+					$Cost = $PrefSuppRow[0];
+				}
+			} elseif ($_POST['CostType']=='OtherPriceList'){
+				$sql = "SELECT price FROM
+								prices
+							WHERE typeabbrev= '" . $_POST['BasePriceList'] . "' 
+								AND currabrev='" . $_POST['CurrCode'] . "' 
+								AND debtorno=''
+								AND enddate='0000-00-00'
+								AND stockid='" . $myrow['stockid'] . "'
+							ORDER BY startdate DESC";
+				$ErrMsg = _('Could not get the base price for the item') . ' ' . $myrow['stockid'] . _('from the price list') . ' ' . $_POST['BasePriceList'];
+				$BasePriceResult = DB_query($sql,$db,$ErrMsg);
+				if (DB_num_rows($BasePriceResult)==0){
+					prnMsg(_('There is no default price defined in the base price list for the item') . ' ' . $myrow['stockid'] . ' ' . _('prices will not be updated for this item'),'warn');
+					$Cost = 0;
+				} else {
+					$BasePriceRow = DB_fetch_row($BasePriceResult);
+					$Cost = $BasePriceRow[0];
+				}
+			} else { //Must be using standard/weighted average costs
+				$Cost = $myrow['cost'];
+				if ($Cost<=0){
+					prnMsg(_('The cost for this item is not set up or is set up as less than or equal to zero - no price changes will be made based on zero cost items. The item concerned is:') . ' ' . $myrow['stockid'],'warn');
+				}
+			}
+	
+			if ($_POST['CostType']!='OtherPriceList'){
+				$RoundedPrice = round(($Cost * (1+ $IncrementPercentage) * $CurrencyRate+($_POST['RoundingFactor']/2))/$_POST['RoundingFactor']) * $_POST['RoundingFactor'];
+				if ($RoundedPrice <=0){
+					$RoundedPrice = $_POST['RoundingFactor'];
+				}
 			} else {
-				$PrefSuppRow = DB_fetch_row($PrefSuppResult);
-				$Cost = $PrefSuppRow[0];
+				$RoundedPrice = round(($Cost * (1+ $IncrementPercentage)+($_POST['RoundingFactor']/2))/$_POST['RoundingFactor']) * $_POST['RoundingFactor'];
+				if ($RoundedPrice <=0){
+					$RoundedPrice = $_POST['RoundingFactor'];
+				}
 			}
-		} elseif ($_POST['CostType']=='OtherPriceList'){
-			$sql = "SELECT price FROM
-							prices
-							WHERE typeabbrev= '" . $_POST['BasePriceList'] . "'
+	
+			if ($Cost > 0) {
+				$CurrentPriceResult = DB_query("SELECT price FROM
+												prices
+												WHERE typeabbrev= '" . $_POST['PriceList'] . "'
+												AND debtorno ='' 
+												AND currabrev='" . $_POST['CurrCode'] . "'
+												AND enddate='" . $SQLEndDate . "'
+												AND startdate='" . FormatDateForSQL($_POST['PriceStartDate']) . "'
+												AND stockid='" . $myrow['stockid'] . "'",$db);
+				if (DB_num_rows($CurrentPriceResult)==1){
+					$sql = 'UPDATE prices SET price=' . $RoundedPrice . "
+							WHERE typeabbrev='" . $_POST['PriceList'] . "'
 							AND currabrev='" . $_POST['CurrCode'] . "'
+							AND debtorno=''
+							AND startdate ='" . FormatDateForSQL($_POST['PriceStartDate']) . "'
+							AND enddate ='" . $SQLEndDate . "' 
 							AND stockid='" . $myrow['stockid'] . "'";
-			$ErrMsg = _('Could not get the base price for the item') . ' ' . $myrow['stockid'] . _('from the price list') . ' ' . $_POST['BasePriceList'];
-			$BasePriceResult = DB_query($sql,$db,$ErrMsg);
-			if (DB_num_rows($BasePriceResult)==0){
-				prnMsg(_('There is no price defined in the base price list for the item') . ' ' . $myrow['stockid'] . ' ' . _('prices will not be updated for this item'),'warn');
-				$Cost = 0;
-			} else {
-				$BasePriceRow = DB_fetch_row($BasePriceResult);
-				$Cost = $BasePriceRow[0];
-			}
-		} else { //Must be using standard/weighted average costs
-			$Cost = $myrow['cost'];
-			if ($Cost<=0){
-				prnMsg(_('The cost for this item is not set up or is set up as less than or equal to zero - no price changes will be made based on zero cost items. The item concerned is:') . ' ' . $myrow['stockid'],'warn');
-			}
-		}
-
-		if ($_POST['CostType']!='OtherPriceList'){
-			$RoundedPrice = round(($Cost * (1+ $IncrementPercentage) * $CurrencyRate+($_POST['RoundingFactor']/2))/$_POST['RoundingFactor']) * $_POST['RoundingFactor'];
-			if ($RoundedPrice <=0){
-				$RoundedPrice = $_POST['RoundingFactor'];
-			}
-		} else {
-			$RoundedPrice = round(($Cost * (1+ $IncrementPercentage)+($_POST['RoundingFactor']/2))/$_POST['RoundingFactor']) * $_POST['RoundingFactor'];
-			if ($RoundedPrice <=0){
-				$RoundedPrice = $_POST['RoundingFactor'];
-			}
-		}
-
-		if ($Cost > 0) {
-			$CurrentPriceResult = DB_query("SELECT price FROM
-											prices
-											WHERE typeabbrev= '" . $_POST['PriceList'] . "'
-											AND currabrev='" . $_POST['CurrCode'] . "'
-											AND stockid='" . $myrow['stockid'] . "'",$db);
-			if (DB_num_rows($CurrentPriceResult)==1){
-				$sql = 'UPDATE prices SET price=' . $RoundedPrice . "
-						WHERE typeabbrev='" . $_POST['PriceList'] . "'
-						AND currabrev='" . $_POST['CurrCode'] . "'
-						AND stockid='" . $myrow['stockid'] . "'";
-				$ErrMsg =_('Error updating prices for') . ' ' . $myrow['stockid'] . ' ' . _('because');
-				$result = DB_query($sql,$db,$ErrMsg);
-				prnMsg(_('Updating prices for') . ' ' . $myrow['stockid'] . ' ' . _('to') . ' ' . $RoundedPrice,'info');
-			} else {
-				$sql = "INSERT INTO prices (stockid,
-											typeabbrev,
-											currabrev,
-											price)
-								VALUES ('" . $myrow['stockid'] . "',
-										'" . $_POST['PriceList'] . "',
-										'" . $_POST['CurrCode'] . "',
-								 		" . $RoundedPrice . ")";
-				$ErrMsg =_('Error inserting prices for') . ' ' . $myrow['stockid'] . ' ' . _('because');
-				$result = DB_query($sql,$db,$ErrMsg);
-				prnMsg(_('Inserting new price for') . ' ' . $myrow['stockid'] . ' ' . _('to') . ' ' . $RoundedPrice,'info');
-			} //end if update or insert
-		}// end if cost > 0
-	}//end while loop around items in the category
+					$ErrMsg =_('Error updating prices for') . ' ' . $myrow['stockid'] . ' ' . _('because');
+					$result = DB_query($sql,$db,$ErrMsg);
+					prnMsg(_('Updating price for') . ' ' . $myrow['stockid'] . ' ' . _('to') . ' ' . $RoundedPrice,'info');
+				} else {
+					$sql = "INSERT INTO prices (stockid,
+												typeabbrev,
+												currabrev,
+												startdate,
+												enddate,
+												price)
+									VALUES ('" . $myrow['stockid'] . "',
+											'" . $_POST['PriceList'] . "',
+											'" . $_POST['CurrCode'] . "',
+											'" . FormatDateForSQL($_POST['PriceStartDate']) . "',
+											'" . $SQLEndDate . "',
+									 		" . $RoundedPrice . ")";
+					$ErrMsg =_('Error inserting prices for') . ' ' . $myrow['stockid'] . ' ' . _('because');
+					$result = DB_query($sql,$db,$ErrMsg);
+					prnMsg(_('Inserting new price for') . ' ' . $myrow['stockid'] . ' ' . _('to') . ' ' . $RoundedPrice,'info');
+				} //end if update or insert
+			}// end if cost > 0
+		}//end while loop around items in the category
+	}
 }
 include('includes/footer.inc');
 ?>
\ No newline at end of file

Modified: trunk/PricesByCost.php
===================================================================
--- trunk/PricesByCost.php	2010-05-15 10:53:04 UTC (rev 3461)
+++ trunk/PricesByCost.php	2010-05-16 06:11:39 UTC (rev 3462)
@@ -1,11 +1,13 @@
 <?php
 /* $Id$ */
 // PricesByCost.php -
-$PageSecurity = 2;
+$PageSecurity = 11;
 include ('includes/session.inc');
 $title = _('Update of Prices By Cost');
 include ('includes/header.inc');
+
 echo '<p class="page_title_text"><img src="' . $rootpath . '/css/' . $theme . '/images/inventory.png" title="' . _('Inventory') . '" alt="">' . ' ' . _('Update Price By Cost') . '';
+
 if (isset($_POST['submit']) or isset($_POST['update'])) {
 	if ($_POST['Margin'] == "") {
 		header('Location: PricesByCost.php');
@@ -15,64 +17,95 @@
 	} else {
 		$Comparator = ">=";
 	} /*end of else Comparator */
-	if ($_POST['StockCat'] == 'all') {
-		$Category = 'stockmaster.stockid = prices.stockid';
+	if ($_POST['StockCat'] != 'all') {
+		$Category = " AND stockmaster.categoryid = '" . $_POST['StockCat'] . "'";
 	} else {
-		$Category = "stockmaster.stockid = prices.stockid AND stockmaster.categoryid = '" . $_POST['StockCat'] . "'";
-	} /*end of else StockCat */
+		$Category ='';
+	}/*end of else StockCat */
+
 	$sql = 'SELECT 	stockmaster.stockid,
 				stockmaster.description,
+				prices.debtorno,
+				prices.branchcode,
 				(stockmaster.materialcost + stockmaster.labourcost + stockmaster.overheadcost) as cost,
-				prices.price as price, prices.debtorno as customer, prices.branchcode as branch
-		FROM stockmaster, prices
-		WHERE ' . $Category . '
-		AND   prices.price' . $Comparator . '(stockmaster.materialcost + stockmaster.labourcost + stockmaster.overheadcost) * ' . $_POST['Margin'] . "
-		AND prices.typeabbrev ='" . $_POST['SalesType'] . "'
-		AND prices.currabrev ='" . $_POST['CurrCode'] . "'";
+				prices.price as price, prices.debtorno as customer, prices.branchcode as branch,
+				prices.startdate,
+				prices.enddate
+			FROM stockmaster, prices
+			WHERE stockmaster.stockid=prices.stockid' . $Category . '
+			AND   prices.price' . $Comparator . '(stockmaster.materialcost + stockmaster.labourcost + stockmaster.overheadcost) * ' . $_POST['Margin'] . "
+			AND prices.typeabbrev ='" . $_POST['SalesType'] . "'
+			AND prices.currabrev ='" . $_POST['CurrCode'] . "'
+			AND (prices.enddate>='" . Date('Y-m-d') . "' OR prices.enddate='0000-00-00')";
 	$result = DB_query($sql, $db);
 	$numrow = DB_num_rows($result);
+	
+	if ($_POST['submit'] == 'Update') {
+			//Update Prices
+		$PriceCounter =0;	
+		while ($myrow = DB_fetch_array($result)) {
+			//update database if update pressed
+			$SQLUpdate = "UPDATE prices
+								SET price = '" . $_POST['Price_' . $PriceCounter] . "'
+							WHERE `prices`.`stockid` = '" . $_POST['StockID_' . $PriceCounter] . "'
+							AND prices.typeabbrev ='" . $_POST['SalesType'] . "'
+							AND prices.currabrev ='" . $_POST['CurrCode'] . "'
+							AND prices.debtorno ='" . $_POST['DebtorNo_' . $PriceCounter] . "'
+							AND prices.branchcode ='" . $_POST['BranchCode_' . $PriceCounter] . "'
+							AND prices.startdate ='" . $_POST['StartDate_' . $PriceCounter] . "'
+							AND prices.enddate ='" . $_POST['EndDate_' . $PriceCounter] . "'";
+			$ResultUpdate = DB_query($SQLUpdate, $db);
+			$PriceCounter++;
+		}
+		DB_free_result($result); //clear the old result
+		$result = DB_query($sql, $db); //re-run the query with the updated prices
+		$numrow = DB_num_rows($result); // get the new number - should be the same!!
+	}
+	
 	$sqlcat = "SELECT categorydescription
 				FROM stockcategory
 				WHERE categoryid='" . $_POST['StockCat'] . "'";
 	$ResultCat = DB_query($sqlcat, $db);
-	$Category = DB_fetch_array($ResultCat);
+	$CategoryRow = DB_fetch_array($ResultCat);
+	
 	$sqltype = "SELECT sales_type
 				FROM salestypes
 				WHERE typeabbrev='" . $_POST['SalesType'] . "'";
 	$ResultType = DB_query($sqltype, $db);
-	$Type = DB_fetch_array($ResultType);
-	if (isset($Category[0])) {
-		$Cat = $Category[0];
+	$SalesTypeRow = DB_fetch_array($ResultType);
+	
+	if (isset($CategoryRow['categorgdescription'])) {
+		$CategoryText = _('the') . ' ' . $CategoryRow['categorgdescription'] . ' ' . _('category');
 	} else {
-		$Cat = 'All Category';
+		$CategoryText = _('all Categories');
 	} /*end of else Category */
-	echo '<div class="page_help_text">' . _('Items in category ') . '' . $Cat . '' . _(' With Price ') . '' . $Comparator . '' . $_POST['Margin'] . '' . _('  times ') . '' . _('Cost in Price List ') . '' . $Type['0'] . '</div><br><br>';
-	if ($numrow != 0) {
+	
+	echo '<div class="page_help_text">' . _('Items in') . ' ' . $CategoryText . ' ' . _('With Prices') . ' ' . $Comparator . '' . $_POST['Margin'] . ' ' . _('times') . ' ' . _('Cost in Price List') . ' ' . $SalesTypeRow['sales_type'] . '</div><br><br>';
+	
+	if ($numrow > 0) { //the number of prices returned from the main prices query is 
 		echo '<table>';
 		echo '<tr><th>' . _('Code') . '</th>
 						<th>' . _('Description') . '</th>
 						<th>' . _('Customer') . '</th>
 						<th>' . _('Branch') . '</th>
+						<th>' . _('Start Date') . '</th>
+						<th>' . _('End Date') . '</th>
 						<th>' . _('Cost') . '</th>
 						<th>' . _('Current Margin') . '</th>
 						<th>' . _('Price Proposed') . '</th>
 						<th>' . _('Price in pricelist') . '</th>
 					<tr>';
 		$k = 0; //row colour counter
-		echo '<form action="PricesByCost.php" method="POST" name="' . _('update') . '">';
+		echo '<form action="' .$_SERVER['PHP_SELF'] .'" method="POST" name="update">';
+		echo'<input type="hidden" value=' . $_POST['StockCat'] . ' name="StockCat">
+			<input type="hidden" value=' . $_POST['Margin'] . ' name="Margin">
+			<input type="hidden" value=' . $_POST['CurrCode'] . ' name="CurrCode">
+			<input type="hidden" value=' . $_POST['Comparator'] . ' name="Comparator">
+			<input type="hidden" value=' . $_POST['SalesType'] . ' name="SalesType">';
+		
+		$PriceCounter =0;
 		while ($myrow = DB_fetch_array($result)) {
-			//update database if update pressed
-			if ($_POST['submit'] == 'Update') {
-				//Update Prices
-				$SQLUpdate = "UPDATE prices
-						SET price = '" . $_POST[$myrow['0']] . "'
-						WHERE `prices`.`stockid` = '" . $myrow['0'] . "'
-						AND prices.typeabbrev ='" . $_POST['SalesType'] . "'
-						AND prices.currabrev ='" . $_POST['CurrCode'] . "'
-						AND prices.debtorno ='" . $myrow['customer'] . "'
-						AND prices.branchcode ='" . $myrow['branch'] . "'";
-				$Resultup = DB_query($SQLUpdate, $db);
-			}
+			
 			if ($k == 1) {
 				echo '<tr class="EvenTableRows">';
 				$k = 0;
@@ -81,54 +114,59 @@
 				$k = 1;
 			}
 			//get cost
-			if ($myrow['cost'] == "") {
-				$Cost = "0";
+			if ($myrow['cost'] == '') {
+				$Cost = 0;
 			} else {
 				$Cost = $myrow['cost'];
 			} /*end of else Cost */
-			//get qty price
-			if (isset($_POST[$myrow['0']])) {
-				$price = $_POST[$myrow['0']];
+						
+			//variables for update
+			echo '<input type="hidden" value=' . $myrow['stockid'] . ' name="StockID_' . $PriceCounter .'">
+					<input type="hidden" value=' . $myrow['debtorno'] . ' name="DebtorNo_' . $PriceCounter .'">
+					<input type="hidden" value=' . $myrow['branchcode'] . ' name="BranchCode_' . $PriceCounter .'">
+					<input type="hidden" value=' . $myrow['startdate'] . ' name="StartDate_' . $PriceCounter .'">
+					<input type="hidden" value=' . $myrow['enddate'] . ' name="EndDate_' . $PriceCounter .'">';
+			//variable for current margin
+			if ($Cost != 0){
+				$CurrentMargin = $Price / $Cost;
 			} else {
-				$price = $myrow['price'];
-			} /*end of else price	*/
-			//variable for update data
-			echo '
-				<input type="hidden" value=' . $_POST['StockCat'] . ' name=' . _('StockCat') . ' />
-				<input type="hidden" value=' . $_POST['Margin'] . ' name=' . _('Margin') . ' />
-				<input type="hidden" value=' . $_POST['CurrCode'] . ' name=' . _('CurrCode') . ' />
-				<input type="hidden" value=' . $_POST['Comparator'] . ' name=' . _('Comparator') . ' />
-				<input type="hidden" value=' . $_POST['SalesType'] . ' name=' . _('SalesType') . ' />
-				<input type="hidden" value=' . $myrow['0'] . ' name=' . _('Id') . ' />
-				<input type="hidden" value=' . $_POST['Price'] . ' name=' . _('Price') . ' />
-				';
-			//variable for current margin
-			$currentmargin = $price / $Cost;
+				$CurrentMargin = 0;
+			}
 			//variable for proposed
-			$proposed = $Cost * $_POST['Margin'];
-			echo '   <td>' . $myrow['0'] . '</td>
-						<td>' . $myrow['1'] . '</td>
+			$Proposed = $Cost * $_POST['Margin'];
+			if ($myrow['enddate']=='0000-00-00'){
+				$EndDateDisplay = _('No End Date');
+			} else {
+				$EndDateDisplay = ConvertSQLDate($myrow['enddate']);
+			}
+			echo '   <td>' . $myrow['stockid'] . '</td>
+						<td>' . $myrow['description'] . '</td>
 						<td>' . $myrow['customer'] . '</td>
 						<td>' . $myrow['branch'] . '</td>
+						<td>' . ConvertSQLDate($myrow['startdate']) . '</td>
+						<td>' . $EndDateDisplay . '</td>
 						<td class="number">' . number_format($Cost, 2) . '</td>
-						<td class="number">' . number_format($currentmargin, 2) . '</td>
-						<td class="number">' . number_format($proposed, 2) . '</td>
-						<td><input type="text" class="number" name="' . $myrow['0'] . '" MAXLENGTH =14 size=15 value="' . $price . '"></td>
+						<td class="number">' . number_format($CurrentMargin, 2) . '</td>
+						<td class="number">' . number_format($Proposed, 2) . '</td>
+						<td><input type="text" class="number" name="Price_' . $PriceCounter . '" MAXLENGTH =14 size=15 value="' . $myrow['price'] . '"></td>
 					</tr> ';
+			$PriceCounter++;
 		} //end of looping
 		echo '<tr>
-			<td style="text-align:right" colspan=4><input type=submit name=submit value=' . _("Update") . '></td>
-			<td style="text-align:left" colspan=3><a href="' . $_SERVER['PHP_SELF'] . '?' . SID . '"><input type=submit  value=' . _("Back") . '><a/></td>
+			<td style="text-align:right" colspan=4><input type=submit name=submit value=' . _('Update') . '></td>
+			<td style="text-align:left" colspan=3><a href="' . $_SERVER['PHP_SELF'] . '?' . SID . '"><input type=submit  value=' . _('Back') . '><a/></td>
 			 </tr></form>';
 	} else {
+		prnMsg(_('There were no prices meeting the criteria specified to review'),'info');
 		echo '<p><div class="centre"><a href="' . $_SERVER['PHP_SELF'] . '?' . SID . '">' . _('Back') . '<a/></div><p>';
 	}
 } else { /*The option to submit was not hit so display form */
 	echo '<div class="page_help_text">' . _('Use this report to display price list with the cost.') . '</div><br>';
-	echo '</br></br><form action=' . $_SERVER['PHP_SELF'] . " method='post'><table>";
+	echo '</br></br><form action="' . $_SERVER['PHP_SELF'] . '" method="post"><table>';
+	
 	$SQL = 'SELECT categoryid, categorydescription
-	      FROM stockcategory
-		  ORDER BY categorydescription';
+		      FROM stockcategory
+			  ORDER BY categorydescription';
 	$result1 = DB_query($SQL, $db);
 	echo '<tr>
 			<td>' . _('Category') . ':</td>
@@ -147,8 +185,11 @@
 	} else {
 		echo '</select>'.' '. _('Standard Cost') . ' x </td>';
 	}
+	if (!isset($_POST['Margin'])){
+		$_POST['Margin']=1;
+	}
 	echo '<td>
-				<input type="text" class="number" name="Margin" MAXLENGTH =10 size=11 value=0></td></tr>';
+				<input type="text" class="number" name="Margin" MAXLENGTH =2 size=2 value=' .$_POST['Margin'] . '></td></tr>';
 	$result = DB_query('SELECT typeabbrev, sales_type FROM salestypes ', $db);
 	echo '<tr><td>' . _('Sales Type') . '/' . _('Price List') . ":</td>
 		<td><select name='SalesType'>";

Modified: trunk/Z_PriceChanges.php
===================================================================
--- trunk/Z_PriceChanges.php	2010-05-15 10:53:04 UTC (rev 3461)
+++ trunk/Z_PriceChanges.php	2010-05-16 06:11:39 UTC (rev 3462)
@@ -10,6 +10,8 @@
 
 echo '<br>' . _('This page updates already existing prices for a specified sales type (price list)') . '. ' . _('Choose between updating only customer special prices where the customer is set up under the price list selected, or all prices under the sales type or just specific prices for a customer for the stock category selected');
 
+prnMsg (_('This script takes no account of start and end dates of prices and updates all historical prices as well as current prices - better to use new scripts under Inventory -> Maintenance'),'warn');
+
 echo "<form method='POST' action='" . $_SERVER['PHP_SELF'] . '?' . SID . "'>";
 
 $SQL = 'SELECT sales_type, typeabbrev FROM salestypes';
@@ -131,7 +133,7 @@
 		}
 
 		$result = DB_query($sql,$db);
-                $ErrMsg =_('Error updating prices for') . ' ' . $myrow['stockid'] . ' ' . _('because');
+        $ErrMsg =_('Error updating prices for') . ' ' . $myrow['stockid'] . ' ' . _('because');
 		prnMsg(_('Updating prices for') . ' ' . $myrow['stockid'],'info');
 	}
 

Modified: trunk/doc/Change.log.html
===================================================================
--- trunk/doc/Change.log.html	2010-05-15 10:53:04 UTC (rev 3461)
+++ trunk/doc/Change.log.html	2010-05-16 06:11:39 UTC (rev 3462)
@@ -1,5 +1,9 @@
 <p><font SIZE=4 COLOR=BLUE><b>webERP Change Log</b></font></p>
 <p></p>
+<p>16/5/10 Phil: reworked PDFPriceList to use the new effective dates fields and print out effective prices as at a specified date - showing effective dates on the report - also ditched includes/PDFPriceListPageHeader.php in favour of a PageHeader() function inside PDFPriceList.php. Also made the script work with tcpdf - not sure how it was missed before?</p>
+<p>16/5/10 Phil: reworked PricesBasedOnMarkUp.php to insert new prices with effectivity dates and update the prices where effectivity dates specified.</p>
+<p>16/5/10 Phil: reworked PricesByCost.php this was bit of a dodgy script - well I found it hard to follow - in the words of Frank Sinatra - I did it my way! Also built in effectivity dates to display and ensure correct prices updated now the primary key of prices is changed.</p>
+<p>16/5/10 Phil: Wrote up the manual so that the logic of pricing with effective dates is explained</p>
 <p>15/5/10 Phil: Used Lindsay/Ngaraj's nice email address checking function to replace the existing function in MiscFunctions.php and includes MiscFunctions.php in install/save.php to avoid duplication of the function</p>
 <p>15/5/10 Phil: $debug variable in UserLogin.php was only set on first login - not subsequent page calls (its not a session variable) - moved it back into session.inc so that full info about bugs is available to sysadmins
 <p>15/5/10 Phil: GetPrices.inc Prices.php and Prices_Customer.php - modified to allow default prices - with no end dates - reducing requirement to administer - also updated Prices section of the manual

Modified: trunk/doc/Manual/ManualPrices.html
===================================================================
--- trunk/doc/Manual/ManualPrices.html	2010-05-15 10:53:04 UTC (rev 3461)
+++ trunk/doc/Manual/ManualPrices.html	2010-05-16 06:11:39 UTC (rev 3462)
@@ -22,19 +22,30 @@
 <br><br>
 Prices can be amended at any time. However, changes to a price do not automatically cascade into sales orders for the item that are yet to be delivered, these orders would have to be changed manually as necessary.
 <br><br>
-There is a utility page that has some options for making bulk changes to pricing avoiding extensive re-keying exercises. Great care should be taken with this facility. System administrator permission is required to access this page accordingly.
+<font size="+1"><b>Price Maintenance Utilities</b></font>
 <br><br>
-The script is called: Z_PriceChanges.php and is accessed from the Utilities index Z_index.php
-<br><br>
-This script allows bulk increase/decreases by percentage for specific or a range of stock categories and for specific sales types. There is the opportunity to update the pricing:
-<br><br>
+There is a utility script that has some options for making bulk changes to pricing avoiding extensive re-keying. This script is accessed from the main menu under "Inventory->Add Or Update Prices Based On Cost". Great care should be taken with this facilities since bulk updates and inserts of new prices are performed as a result. System administrator permission is required to access this page accordingly. The script named PricesBasedOnMarkUp.php is quite flexible and can update/insert new default prices for a particular price list/currency based on any of:
 <ul>
-<li>For all prices for the sales type within the stock categories selected or
-<li>For only the default prices for the sales type within the stock categories selected or
-<li>For the selected customer special prices - provided the customer selected belongs to the sales type selected in the bulk change screen.
+<li>another price list plus a markup</li>
+<li>the supplier purchasing cost data plus a markup</li>
+<li>the system cost plus a markup</li>
 </ul>
-Equally flexible options exist for printing price lists from the main menu under the orders tab under inquiries and reports.
+If using another price list the price used as the base for the new price will be the latest default price i.e. no customer speicifc prices will be used - nor updated - and the price with the most recent start date will be chosen as the basis for the calculation of the new price.
 <br><br>
+The script allows you to specifiy when the new prices will be effective from and to - if the field for effective to is left blank then the new price is assumed to be effective until a later price is entered. If you have run this script once and wish to change the parameters to use say a different markup - the prices previously entered by the script can be updated rather than creating a lot of new prices, provided that the same start and end dates are specified. You can only specify price effective from dates for days after the current day.
+<br><br>
+<font size="+1"><b>Printing Price Lists</b></font>
+<br><br>
+Flexible options exist for printing price lists from the main menu under the sales tab under inquiries and reports ->Print Price Lists. Price lists for a particular price list and specific category or range of categories can be printed to pdf. It is also possible to print the price list with the current gross profitability of those prices displayed for internal use. Prices specific to particular customers can also be printed having selected the customer first.
+<br><br>
+
+<br><br>
+<font size="+1"><b>Reviewing Prices Based on Cost</b></font>
+<br><br>
+It is possible to review prices based on the proportion of cost that they represent - e.g. you may wish to review costs that are less than 100% of cost (or 1 times the cost) - i.e. prices that would result in a gross loss - a wise idea indeed! The prices meeting the criteria are displayed and available to be modified. You can specify any number of times the cost for the critiera - to look at the prices where the margin is less than 50% on cost the multiple would be 1.5 times cost. You can also look at prices where the price is more than a multiple of the cost. The prices returned can then be updated all in place without having to select them individually this is much more convenient than identifying the errant prices manually and then going in and selecting them individually in the normal price maintenace screen.
+<br><br>
+From the main menu Inventory -> View Or Update Prices Based On Costs. You can select the Inventory category you wish to review or leave the default - to view all categories. Initially it is assumed you wish ...
 
[truncated message content]