recalculate for multiple clients?

samoht · November 1, 2007

Hello all,

I am trying to write a function or functions to recalculate two price fields for multiple clients.

My price fields are:

PriceRetail

PriceSell

which I want to control based on a $Cost variable (also a field I will save) and a $PriceCode variable that references a table that stores the factors for the two price fields to be changed/recalculated

this involves 2 db tables (productprice, pricecodeprice)

here is what I have so far -

<?php 

# For recalculating product prices.
if ($_GET['ProductId'] && $_GET['Cost'] && $_GET['PriceCode']) {

$ProductId = $_GET['ProductId'];
$Cost			 = $_GET['Cost'];
$PriceCode = $_GET['PriceCode'];

//grab all the client types to recalculate prices for.
$q = dbQuery("SELECT ClientPriceCode FROM productprice WHERE ProductId ='$ProductId'");
$nClients = dbNumRows($q);

while($row = dbFetchAssoc($q))
{
 $CPC_list[] = $row['ClientPriceCode'];
}

 // get the factors for each client type from pricecodeprice.
   $q = dbQuery("SELECT RetailFactor, SellFactor FROM pricecodeprice WHERE ClientPriceCode IN('".join("', '", $CPC_list)."')"); 
   $row = dbFetchAssoc($q);
   extract($row);

I'm having trouble setting up my queries to do the math I need to and then display them in an HTML table.

Any Ideas??

Najjar · November 1, 2007

Your question(s) are not clear enough.

---- I'm having trouble setting up my queries to do the math I need to and then display them in an HTML table. ---

Show me in regular math what you want to calculate and how.

samoht · November 1, 2007

ok on the current page lets say I have a product with a cost = 3.00 a retail = 6.00 and a sell = 5.95 for client R1

then I have a retail = 4.25 and a sell = 4.25 for client W1

I have a recalc btn that brings up a prompt to change the cost for this product. So I change the cost to 2.50

now I want to look up the retail factor for client R1 where the PriceCode = "A1" (this code comes from a select box on the page) and multiply the new cost by the RetailFactor (e.g. 2.50 x 2) to get the new retail price. The the same for the sell, and then repeat the process for each type of client (e.g. client R1, and W1)

so the original retail of 6.00 would be 5.00 and the sell would change etc.

Does that help clear things up??

Barand · November 1, 2007

something like this?

<?php
if ($_GET['ProductId'] && $_GET['Cost'] && $_GET['PriceCode']) {

    $ProductId = $_GET['ProductId'];
    $Cost = $_GET['Cost'];
    $PriceCode = $_GET['PriceCode'];


    $sql = "SELECT p.ClientPriceCode, c.RetailFactor, c.SellFactor
            FROM productprice p
            INNER JOIN pricecodeprice c ON p.ClientPriceCode = c.ClientPriceCode
            WHERE p.ProductId ='$ProductId'
            ORDER BY p.ClientPriceCode";
    $res = mysql_query ($sql);

    echo "Product : $productId <br/>Cost : $Cost <br/>";
    echo '<table>
            <tr>
            <th>ClientPriceCode</th>
            <th>Retail Factor</th>
            <th>Retail Price</th>
            <th>Sell Factor</th>
            <th>Sell Price</th>
            </tr>';
    while (list($cpc, $rf, $sf) = mysql_fetch_row($res))
    {
        printf ('<tr><td>%s</td><td>%0.2f</td><td>%0.2f</td><td>%0.2f</td><td>%0.2f</td></tr>', $cpc, $rf, $Cost*$rf, $sf, $Cost*$sf);
    }
    echo '</table>';
}

samoht · November 1, 2007

thanks Barand!

only thing is that I am getting the results upside down?

<?php
if ($_GET['ProductId'] && $_GET['Cost'] && $_GET['PriceCode']) {

    $ProductId = $_GET['ProductId'];
    $Cost = $_GET['Cost'];
    $PriceCode = $_GET['PriceCode'];

    $sql = "SELECT p.ClientPriceCode, c.RetailFactor, c.SellFactor
            FROM productprice p
            INNER JOIN pricecodeprice c ON p.ClientPriceCode = c.ClientPriceCode
            WHERE p.ProductId ='$ProductId'
    AND c.PriceCode = '$PriceCode'
            ORDER BY p.ClientPriceCode";
    $res = dbQuery ($sql);

$table = '';

$table .= '
					<table border="0" cellspacing="0" cellpadding="1">
              <tr>
                <td colspan="4" class="mb">Prices</td>
              </tr>
              <tr>
                <td><span class="errors">*</span>Price Code</td>
                <td><span class="errors">*</span>Cost</td>
              </tr>
              <tr>
                <td><select name="PriceCode" id="PriceCode" tabindex="5" style="width:180px">';
                   
						$sql = dbQuery("SELECT PriceCode, Name FROM pricecode ORDER BY PriceCode");
						  while($row = dbFetchAssoc($sql))
							  {
								extract($row);
$table .= '							
                    <option value="'.$PriceCode.'">'.$PriceCode.'|'.$Name.'</option>';

							  }
  $table .= '   </select></td>
                <td><input size="7" name="Cost" type="text" id="Cost" style="text-align:right" value="'.$Cost.'"></td>
                <td>
  <form name="calc">
    <a class="calc" style="cursor:pointer" onClick="recalcTotals($ProductId,$Cost,$pc);">Recalc prices</a>
  </form>
</td>
</tr>
<tr>
    <td> </td>
  </tr>
  <tr>
  <td> </td>
  <td><span class="errors">*</span>Retail</td>
  <td><input size="7" name="PriceRetail" type="text" id="PriceRetail" style="text-align:right" value="$PriceRetail"></td>
  </tr>
  <tr>
    <td> </td>
  </tr>
<tr>
    <td>Client</td>
    <td><span class="errors">*</span>Sell</td>
    <td><span class="errors">*</span>Hold</td>
  </tr>
<tr>
    <td>'; 
while (list($cpc, $rf, $sf) = dbFetchRow($res))
    	{
 printf ('<tr><td>%s</td><td><input size="7" name="PriceRetail" value=".%0.2f"></td><td><input size="7" name="PriceSell" value="%0.2f"></td></tr>', $cpc, $Cost*$rf, $Cost*$sf);
 }
	$table .= '</td>
  </tr>
</table>';

echo $return_value = $table;

}

this puts the looped prices above the select box and my table headings ??

Barand · November 2, 2007

Printf() outputs immediately, like print or echo. You are storing the rest of the output into a variable.

instead of "printf (...)" use

$table .= sprintf(...).

You will need to adjust the < tr >, < td > tags

samoht · November 2, 2007

Thanks again Barand!

I have one last problem here. In the prompt I enter a value like 2.25 for the $Cost

When the return spits out the first value (i.e. $Cost * $rf) comes out with a . in front of it?? (like .4.50 if the $rf = 2.00)

Barand · November 2, 2007

Looks like you have extra "." in the sprintf format string before the %0.2f

value=".%0.2f">

samoht · November 2, 2007

thanks again!!

I am not quite solved though. perhaps u could help a little more.

I am now have trouble saving the new values.

This is what I have tried:

<?php
		#UDATE PRODUCTPRICE TABLE
	$SQL = "";
		#This allows us to save multi-records in the right spot!
		for($n=0;$n<count($_POST['PriceSell']);$n++)
		{
		$SQL = sprintf("UPDATE productprice SET PriceSell=%s, PriceHold=%s WHERE ClientPriceCode=%s AND ProductId=%s",
						//GetSQLValueString($_POST['PriceRetail'][$n],	"decimal"),
						GetSQLValueString($_POST['PriceSell'][$n],	"decimal"),
						GetSQLValueString($_POST['PriceHold'][$n],	"decimal"),
						GetSQLValueString($_POST['ClientPriceCode'][$n], "text"),
						GetSQLValueString($ProductId,				"int"));
				//$Result = dbQuery($SQL)or die(mysql_error());							
		}
		echo $SQL; //uncomment this and comment $Result to debug

my save

and here is the function currently:

<?php
if ($_GET['ProductId'] && $_GET['Cost'] && $_GET['PriceCode']) {

    $ProductId = $_GET['ProductId'];
    $Cost = $_GET['Cost'];
    $PriceCode = $_GET['PriceCode'];

    $sql = "SELECT p.ClientPriceCode, cp.ShortName, c.RetailFactor, c.SellFactor, c.HoldFactor
            FROM productprice p
            INNER JOIN pricecodeprice c ON p.ClientPriceCode = c.ClientPriceCode
					INNER JOIN clientpricecode cp ON p.ClientPriceCode = cp.ClientPriceCode
            WHERE p.ProductId ='$ProductId'
					AND c.PriceCode = '$PriceCode'
            ORDER BY p.ClientPriceCode";
    $res = dbQuery ($sql);
	$row = dbFetchAssoc($res);
	extract($row);
	$PriceRetail = $Cost * $RetailFactor;

$table = '';

$table .= '
					<table border="0" cellspacing="0" cellpadding="1">
              <tr>
                <td colspan="4" class="mb">Prices</td>
              </tr>
              <tr>
                <td><span class="errors">*</span>Price Code</td>
                <td><span class="errors">*</span>Cost</td>
              </tr>
              <tr>
                <td><select name="PriceCode" id="PriceCode" tabindex="5" style="width:180px">';
                   
	$sql = dbQuery("SELECT PriceCode, Name FROM pricecode ORDER BY PriceCode");
	while($row = dbFetchAssoc($sql))
	{
	    if($row['PriceCode']== $PriceCode){
$table .= '
		<option value="'.$PriceCode.'" selected>'.$PriceCode.'|'.$row['Name'].'</option>';
	}else{
$table .= '							
                    <option value="'.$row['PriceCode'].'">'.$row['PriceCode'].'|'.$row['Name'].'</option>';

	 }
}
  $table .= '   </select></td>
                <td><input size="7" name="Cost" type="text" id="Cost" style="text-align:right" value="'.$Cost.'"></td>
                <td>
  <form name="calc">
    <a class="calc" style="cursor:pointer" onClick="recalcTotals($ProductId,$Cost,$pc);">Recalc prices</a>
  </form>
</td>
</tr>
<tr>
    <td> </td>
  </tr>
  <tr>
  <td> </td>
  <td><span class="errors">*</span>Retail</td>
  <td><input size="7" name="PriceRetail" type="text" id="PriceRetail" style="text-align:right" value="'.$PriceRetail.'"></td>
  </tr>
  <tr>
    <td> </td>
  </tr>
<tr>
    <td>Client</td>
    <td><span class="errors">*</span>Sell</td>
    <td><span class="errors">*</span>Hold</td>
  </tr>
<tr>
    <td>'; 
    mysql_data_seek($res,0);
    while (list($cpc, $sn, $rf, $sf, $hf) = dbFetchRow($res))
     {
     $n = 0;
     $table .= sprintf ('<tr><td><input name="ClientPriceCode['.$n.']" type="hidden" id="ClientPriceCode" value="['.$n.']%s">%s</td><td><input size="7" name="PriceSell['.$n.']" value="%0.2f"></td><td><input size="7" name="PriceHold['.$n.']" value="%0.2f"></td></tr>', $cpc, $sn, $Cost*$sf, $Cost*$hf);
     $n++;			  
     }
$table .= '</td>
  </tr>
</table>';

echo $return_value = $table;

}

I figure my little $n counting varible is not working with the list() ??

Thanks for all your help!

Barand · November 2, 2007

sprintf would be

<?php
     $table .= sprintf ('
     <tr>
        <td>
            <input name="ClientPriceCode[%d]" type="hidden" id="ClientPriceCode" value="%s">%s
        </td>
        <td>
            <input size="7" name="PriceSell[%d]" value="%0.2f">
        </td>
        <td>
            <input size="7" name="PriceHold[%d]" value="%0.2f">
        </td></tr>', $n, $cpc, $cpc, $n, $Cost*$sf, $n, $Cost*$hf);

?>

To process

<?php 
foreach ($_POST['ClientPriceCode'] as $n => $cpc)
{
    	$SQL = sprintf("UPDATE productprice SET PriceSell=%s, PriceHold=%s WHERE ClientPriceCode=%s AND ProductId=%s",
        //GetSQLValueString($_POST['PriceRetail'][$n],	"decimal"),
        GetSQLValueString($_POST['PriceSell'][$n],	"decimal"),
        GetSQLValueString($_POST['PriceHold'][$n],	"decimal"),
        GetSQLValueString($cpc,    "text"),
        GetSQLValueString($ProductId,				"int"));

}
?>

Sign In

recalculate for multiple clients?

Recommended Posts

samoht

Link to comment

Share on other sites

Najjar

Link to comment

Share on other sites

samoht

Link to comment

Share on other sites

Barand

Link to comment

Share on other sites

samoht

Link to comment

Share on other sites

Barand

Link to comment

Share on other sites

samoht

Link to comment

Share on other sites

Barand

Link to comment

Share on other sites

samoht

Link to comment

Share on other sites

Barand

Link to comment

Share on other sites

Join the conversation

Browse

Activity

Important Information