|
|
Posted by "Richard Lynch" on 06/17/05 00:54
Is they any way you could combine the UPDATE queries?
I suspect not, but those are the ones that are probably what will get
worse and worse over time as the file/db grows.
All the left outer joins are not gonna help either, but that's only a few
hundred rows, right?... But will it grow?
Finally, if "nice -19" isn't supported on Windows (I suspect not) a simple
"sleep" call (see below) could give the rest of the system a chance to do
useful work.
On Thu, June 16, 2005 3:28 am, Gustav Wiberg said:
> <?php
> //This script was updated 2005-06-16
> //by Gustav Wiberg / gustav@varupiraten.se
> //Please visit www.varupiraten.se
> //
> require("phpfunctions/dbsafety.php");
>
> //Parameters that sets the rules for importing
> //
> static $fileName = "import/GNTprisfil.txt";
> static $logFileName = "logfiles/gnt.txt";
> static $limitSteps = 25;
> static $saldoColumn = 6;
> static $artNrColumn = 2;
> static $priceColumn = 5;
> static $row = 1;
> static $updateActions = 0;
> static $deleteActions = 0;
> static $checkLev = "gn-";
>
> //Function for creating a logfile
> //and writing to screen
> //
> function writeNow($str, $logFileName) {
>
> //Create a handle for writing (appending)
> //
> $logHandle = fopen($logFileName,"a");
>
> fwrite($logHandle, "$str\r\n");
> echo $str . "<br>";
>
> //Close file for writing to logfile
> //
> fclose($logHandle);
>
> }
> //Set limitstart for first time
> //
> if (!isset($limitStart)) {$limitStart = 0;}
>
> if (isset($_REQUEST["updateActions"])) {$updateActions =
> $_REQUEST["updateActions"];}
> if (isset($_REQUEST["deleteActions"])) {$deleteActions =
> $_REQUEST["deleteActions"];}
> if (isset($_REQUEST["startTime"])) {$startTime = $_REQUEST["startTime"];}
>
> if ($_REQUEST["limitstart"]) {
> $limitStart = $_REQUEST["limitstart"];
> }
>
>
> require ("phpfunctions/opendb.php");
>
>
> $sql = "SELECT COUNT(IDVara) cn FROM tbvara WHERE Varunamn LIKE
> '$checkLev%'";
> $querys = mysql_query($sql);
>
> //Count products in db
> //
> if ($limitStart == 0) {
>
> $dbArray = mysql_fetch_array($querys);
> $nrOfProducts = $dbArray["cn"];
> echo "Antal produkter: $nrOfProducts";
>
> //Create logfile or delete all content from current logfile
> //
> $logHandle = fopen($logFileName,"wb");
> fclose($logHandle);
>
>
> //Get starttime of script
> //
> $startTime = time();
> }
>
> else {
>
> $nrOfProducts = $_REQUEST["nrofproducts"];
>
> }
>
> if ($limitStart > $nrOfProducts) {
> $nrSeconds = time() - $startTime;
> $nrMinutes = $nrSeconds / 60;
> ?>
> <b>Klar</b> med uppdatering av saldo och ev. borttagningar för
> <b>GNT</b>.<br><br>
> <b>Tid för uppdatering:</b> <?php echo $nrSeconds;?> sekunder eller
> <?php echo $nrMinutes;?> minuter<br><br>
> <b>Antal uppdateringar:</b> <?php echo $updateActions;?><br>
> <b>Antal produkter som ej visas efter uppdatering: </b> <?php echo
> $deleteActions;?><br>
> <a
> href="captech_checksaldo_step2.php?deleteActions=0&updateActions=0">Kolla
> captech produkter</a>
> <?php
> exit;
> }
>
> //Check if the filename exists first!
> //If not, then exit script
>
> if (!file_exists($fileName)) {
>
> echo "Filen $fileName finns inte!<br>Avslutar scriptet nu!";
> exit;
>
> }
>
>
>
>
> //Go through database with products from GNT
> //
> $sql = "SELECT tbvara.IDVara, tbvara.Saldo, tbvara.startPris,
> tbvara.Varunamn, tbvara.synligVara, tbvara.lastPris,
> tbunderkategori.marginalProcent, tbunderkategori.Underkategori FROM
> tbvara";
> $sql .= " LEFT JOIN tbunderkategorivara ON (tbvara.IDVara =
> tbunderkategorivara.ForIDVara)";
> $sql .= " LEFT JOIN tbunderkategori ON
> (tbunderkategori.IDUnderKategori
> = tbunderkategorivara.ForIDUnderKategori)";
> $sql .= " WHERE Varunamn LIKE '$checkLev%' AND
> tbunderkategori.marginalProcent>-1 AND tbunderkategori.Underkategori<>'Ej
> tilldelade' ORDER BY IDVara LIMIT $limitStart,$limitSteps";
> //echo $sql;
> //exit;
> $querys = mysql_query($sql);
>
> while ($toarray = mysql_fetch_array($querys)) {
>
> //Get current row from db
> //
> $idproduct = $toarray["IDVara"];
> $dbSaldo = $toarray["Saldo"];
> $dbArtNr = $toarray["Varunamn"];
> $dbPris = $toarray["startPris"];
> $dbSynligVara = $toarray["synligVara"];
> $dbLastPris = $toarray["lastPris"];
> $dbMarginalProcent = $toarray["marginalProcent"];
> $dbUnderKategori = $toarray["Underkategori"];
>
> //Taken from table tbunderkategori
> //
> $marginalProcent = intval($dbMarginalProcent);
>
> //Delete $checkLev from string $dbArtNr for the sake of
> comparing
> //(take away the three first characters)
> //
> $dbArtNr = substr($dbArtNr, 3);
>
>
> //Go through whole textfile and compare with current row in db
> //
> $handle = fopen($fileName, "r");
>
>
>
> $foundProduct= false;
> ob_start();
>
> while (($data = fgetcsv($handle, 1000, ";")) !== FALSE) {
>
> $textSaldo = $data[$saldoColumn];
> $textArtNr = $data[$artNrColumn];
> $textPris = $data[$priceColumn];
>
>
> //Default marginal is 15 when marginal isn't set for
> undercategory
> //
> if (intval($marginalProcent) == 0) {
>
> $marginalProcent = 15;
>
> }
>
> //echo "Marginal för underkategori $dbUnderKategori är
> $marginalProcent%<br>";
>
> //Set price based on price from textfile + marginal set
> //for undercategory
> //
> $newPrice = $textPris + (($textPris *
> ($marginalProcent/100)));
> $newPrice = round($newPrice,0);
>
>
> if (isset($textArtNr) AND isset($textSaldo) AND $row>1) {
>
>
> //If there is an occurence of article-nr for product
> in
> textfile
> //that matches the one in current row in database...
> //
> if ($textArtNr == $dbArtNr) {
>
> $foundProduct= true;
>
> //If saldo for product isn't the same for product
> in
> textfile,
> //OR price for product isn't' the same for product
> in textfile
> //THEN do something...
> //
> if (strtolower($textSaldo) != strtolower($dbSaldo)
> OR strtolower($newPrice) != strtolower($dbPris) OR $dbSynligVara == 'N') {
>
>
> $sql2 = "UPDATE tbvara";
> $sql2 .= " SET Saldo=" .
> safeQuote($textSaldo);
>
> //Update price only if newPrice is over zero
> //newPrice IS zero when marginalProcent is set
> to zero based on marginalProcent
> //from tbunderkategori
> //Price from vendor must also be higher than
> zero
> //If price is locked (lastPris), then don't
> update specific price
> //
> if ($dbLastPris == 'N') {
>
> if (intval($newPrice) > 0 AND
> intval($textPris) > 0) {
>
> $sql2 .= ", startPris=" .
> safeQuote($newPrice);
>
> }
>
> }
>
> $sql2 .= ", synligVara=" . safeQuote('Y');
> $sql2 .= " WHERE IDVara=" .
> safeQuote($idproduct) . " LIMIT 1";
> $querys2 = mysql_query($sql2);
> $writeTo = "Produkt: $dbArtNr";
> writeNow($writeTo, $logFileName);
>
> $writeTo = "marginalpåslag i %:
> $marginalProcent
> (Underkategori: $dbUnderKategori)";
> writeNow($writeTo, $logFileName);
>
> if ($dbSaldo != $textSaldo) {
> $writeTo = "Föregående saldo: $dbSaldo har
> ändrats till: $textSaldo";
> writeNow($writeTo, $logFileName);
> }
>
> if ($dbPris != $newPrice) {
> $writeTo = "Föregående pris = $dbPris har
> ändrats till: $newPrice";
> writeNow($writeTo, $logFileName);
> }
>
> $writeTo = "Ändring fungerade bra:";
> writeNow($writeTo, $logFileName);
>
> $wrongSQL = mysql_error();
>
> //No errors when sql query was executed
> //
> if (strlen($wrongSQL)==0) {
> $updateActions++;
> $writeTo = "Ja";
> writeNow($writeTo, $logFileName);
>
> }
> else {
> //Error when sql-query was executed
> //
> $writeTo = "Nej - fel $wrongSQL";
> writeNow($writeTo, $logFileName);
> }
>
> $writeTo =
> "-------------------------------------------------";
> writeNow($writeTo, $logFileName);
>
>
>
> }
> //echo "<b>YES</b>! $sql2";
>
> }
>
> }
>
>
> $row++;
>
sleep(1); //Give the cpu a chance to do something else for a second.
> }
> //End of textfile
> ob_flush();
>
>
> fclose($handle);
>
> if ($foundProduct == false AND $dbSynligVara == 'Y') {
>
> $sql2 = "UPDATE tbvara SET synligVara=" . safeQuote('N') . "
> WHERE IDVara=" . safeQuote($idproduct) . " LIMIT 1";
> //echo $sql2;
> $querys2 = mysql_query($sql2);
> $deleteActions++;
>
> }
>
>
> }
> //END Go through database with products from GNT
>
>
>
>
>
>
> mysql_close();
> //Increase limitstart to begin after current limitSteps
> //
> $limitStart = $limitStart + $limitSteps + 1;
>
>
> ?>
> <script language="Javascript">
> window.location = 'gnt_checksaldo_step2.php?limitstart=<?php echo
> $limitStart;?>&nrofproducts=<?php echo
> $nrOfProducts;?>&deleteActions=<?php
> echo $deleteActions;?>&updateActions=<?php echo
> $updateActions;?>&startTime=<?php echo $startTime;?>';
> </script>
>
> --
> PHP General Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
>
>
--
Like Music?
http://l-i-e.com/artists.htm
Navigation:
[Reply to this message]
|