Jump to content

paulvz

Members
  • Posts

    11
  • Joined

  • Last visited

Everything posted by paulvz

  1. But i only used one product here, there are multiple products
  2. Yes that is what i need, We wil work our cost price on stock on hand for that day, it might be that no stck has gone out for 3 days, but the stock level has to be in the day it was received as prices changed daily.
  3. +------------+-----+----+-----+-------+ id| Date | ID | In | Out | Level |new level +------------+-----+----+-----+-------+ a| 2020-10-01 | 9 | 15 | 10 | 5 | 0 | b| 2020-10-02 | 9 | 10 | 7 |8 | 0 | b stock out 7-a-level5 leaves a-level with 0 and a remainders of b-stock out with -2. then b-stockin was 10 so 10-2 = 8 c| 2020-10-03 | 9 | 23 | 25 |6 | 0 | now c-stock out - 25. b-stock level = 8(c-stockout 25 - b-stock level 8 = 17. so bstocklevel now becomoes 0, but c-stock in was 23 so it would be 23 -17 = 6. c-level now = 6 d| 2020-10-04 | 9 | 30 | 16 |20 | d-stockout is 16 - c-level is 6 so leavin c level with 0 and d-out with 1o. d-stick in has 30 wich leaves new stock value with 20 e| +------------+-----+----+-----+-------+ | 78 | 58 | +----+-----+ But levels are allowed to go into negative. ig for example stock out in c was only 2 then c level would be 6 and d level 30
  4. https://forums.phpfreaks.com/profile/3105-barand/ good day - mysql wit 5 field, date - stock id - stock in stock out - stock level 2020-10-01 | 9 | 15 | 10 | 5 2020-10-02| 9 | 10 | 7 | 2020-10-03| 9 | 23 | 25 | 2020-10-04| 9 | 30 | 16 | on 2020-10-01 stok level is correct as it is the first day 15-10 = 5 so 5 stock left 2020-10-01 stock out is 7. so 7 needs to be subtructed from oldest stock level that os > 0 in this case 7-5 leaving 2020-10-1 with 0 and now before wa add stock in with 2020-10-02with stock of -2. then add stick in of 10 wich leaves 2020-10-2 with a stock value of 8 2020-10-03 has stock out of 23 - sp 2020-10-02 stock wil be 23-8 leaving it 0 and then 2020-10-03 stoin in subtracted from remaining. 9-15 leaving a negative stock of -6 on the 2020-10-04 stock out is 16, but previous day already has a minus, so it wont effect previous day 2020-10-04 stock wil be -16. now we add stock in for 2020-10-04 wich is 16. so first previous stok day calculation 30 -8 making 2020-10-03 0, leaving stock in with 24. now we calculate todays stock 24-16 leaving a stock level of 8. Hope this makes more sense
  5. Good morning. I need to subtract stock levels from oldest stock first then to the next date. It is allowed to move into negative values. example 01-10 stock in 10 stock out 5 - stock count 5 02-10 stock in 10 stock out 7 stock count ... here we need to subtract the 7 from previous days 5 til it reches 0 then stock in subtraction 01-10 stock =0 02-10 stock = 8 --- as 7 -5 gives me -2 in incoming stock is 10 leaving me with stock count of 8. 03-10 stock in 8 stock out 21 02-10 stck level must be 0 03-10 stock lever now is 8-14 = -6 and so on below is my code. $lq_in = new ListQuery('stock_audit5f5795042f369'); $lq_in->addSimpleFilter('name', '%PEPPER yellow%', 'LIKE' ); //$lq_in->addSimpleFilter('product_id', $product_id, '='); $lq_in->setOrderBy('date_entered'); $res_in = $lq_in->fetchAll(); $StockArray = []; foreach($res_in as $out_rec) { /*$upd_out = array(); $upd_out['stock_out_done'] = 0; $out_rec_u = ListQuery::quick_fetch('stock_audit5f5795042f369', $out_rec->getField('id')); $aud_upd_out = RowUpdate::for_result($out_rec_u); $aud_upd_out->set($upd_out); $aud_upd_out->save(); continue; */ $stock_out = $out_rec->getField('stock_out'); $stock_out_done = $out_rec->getField('stock_out_done'); $date_entered = $out_rec->getField('date_entered'); $product_id = $out_rec->getField('product_id'); //echo '<pre>'; // print_r($out_rec->row); $StockItems[] = $out_rec->row; //$stock_done = 0; /* foreach($res_in as $in_rec) { $upd = array(); if($stock_out_new > $in_rec->getField('stock_level')) { $upd['stock_level'] = 0; $stock_out_new = $stock_out_new-$in_rec->getField('stock_level'); $stock_done = $in_rec->getField('stock_level'); } elseif ($stock_out_new == $in_rec->getField('stock_level')) { $upd['stock_level'] = 0; $stock_out_new = 0; $stock_done = $stock_out; } elseif($stock_out_new < $in_rec->getField('stock_level')) { $upd['stock_level'] = $in_rec->getField('stock_level')-$stock_out_new; $stock_out_new = 0; $stock_done = $stock_out; } else { continue; } $in_rec_u = ListQuery::quick_fetch('stock_audit5f5795042f369', $in_rec->getField('id')); $aud_upd = RowUpdate::for_result($in_rec_u); $aud_upd->set($upd); $aud_upd->save(); if($stock_out_new == $stock_done) break; } $upd_out = array(); $upd_out['stock_out_done'] = $stock_done; $out_rec_u = ListQuery::quick_fetch('stock_audit5f5795042f369', $out_rec->getField('id')); $aud_upd_out = RowUpdate::for_result($out_rec_u); $aud_upd_out->set($upd_out); $aud_upd_out->save(); */ } function GetFirstItemWithStockKey($StockItemsarrayk = null){ if($StockItemsarrayk != null){ foreach($StockItemsarrayk as $key => $value){ if(((int) $StockItemsarrayk[$key]['stock_level']) > 0){ return $key; } } } } function SetFirstItemWithStock($StockItemsarray = null){ if($StockItemsarray != null){ foreach($StockItemsarray as $key => $value){ if(((int) $StockItemsarray[$key]['stock_level']) > 0){ return $StockItemsarray[$key]; } } } } $remainder = 0; $pkey = ""; $StockLevelKey = 0; $StockIn = []; $StockOut = []; $InStock = []; $NewStockItems = $StockItems; $ArrayKeys = []; foreach($StockItems as $key => $value){ $StockIn[$key] = (int) $StockItems[$key]['stock_in']; $StockOut[$key] = (int) $StockItems[$key]['stock_out']; $InStock[$key] = (int) $StockItems[$key]['stock_level']; $ArrayKeys[] = (int)$key; } //var_dump($InStock); foreach($NewStockItems as $key => $value){ if($key < 1){ if($StockIn[$key] > 0 && $StockOut[$key] == 0 && $InStock[$key] == 0){ $StockItems[$key]['stock_level'] = ($InStock[$key] + $StockIn[$key]); } if($StockIn[$key] == 0 && $StockOut[$key] > 0 && $InStock[$key] == 0){ $StockItems[$key]['stock_level'] = ($InStock[$key] - $StockOut[$key]); } if($StockIn[$key] > 0 && $StockOut[$key] > 0 && $InStock[$key] == 0){ $StockItems[$key]['stock_level'] = ($InStock[$key] - $StockOut[$key] + $StockIn[$key]); $StockItems[$key]['stock_out'] = 0; } } if($key > 0){ $previousWithStockItem = SetFirstItemWithStock($StockItems); $previousItemWithStockKey = GetFirstItemWithStockKey($StockItems); // echo "<pre>"; // print_r($previousWithStockItem); // var_dump($StockIn[$key]); echo "&nbsp;&nbsp;&nbsp;&nbsp;--IN"; // var_dump($InStock[$key]); echo "&nbsp;&nbsp;&nbsp;&nbsp;--- current"; // var_dump($StockOut[$key]); echo "&nbsp;&nbsp;&nbsp;&nbsp;--- OUT"; while($StockOut[$key] > 0){ if($StockOut[$key] > 0 && $previousWithStockItem['stock_level'] > 0){ $Counter = 0; $maxIteration = 0; for($Counter = $previousWithStockItem['stock_level']; $Counter >= 0; $Counter--){ $StockItems[$previousItemWithStockKey]['stock_level'] = $Counter; if($Counter == 0){ $StockOut[$key] = $StockOut[$key] - $maxIteration; } $maxIteration++; } } if((((int) $StockItems[$key]['stock_level'] < 0) || ((int) $StockItems[$key]['stock_level'] === 0))&& ($StockIn[$key] > 0)){ $valueTotal = $StockItems[$key]['stock_level'] + $StockIn[$key]; $StockItems[$key]['stock_level'] = $valueTotal; } echo "<hr/>"; echo (int) $StockOut[$key]; echo "<br/>"; echo (int) $StockItems[$key]['stock_level']; echo "<br/>"; echo "<hr/>"; if(((int) $StockOut[$key] > 0) && ((int) $StockItems[$key]['stock_level'] > 0) && ((int) $StockItems[$key]['stock_level'] > $StockOut[$key])){ $newStockLevel = $StockItems[$key]['stock_level'] - $StockOut[$key]; echo $newStockLevel; $StockItems[$key]['stock_level'] = $newStockLevel; $StockItems[$key]['stock_out'] = 0; $StockOut[$key] = 0; } if((((int) $StockItems[$key]['stock_level'] < 0) || ((int) $StockItems[$key]['stock_level'] === 0))&& ($StockIn[$key] > 0)){ $valueTotal = $StockItems[$key]['stock_level'] + $StockIn[$key]; echo $valueTotal; $StockItems[$key]['stock_level'] = $valueTotal; } } } } echo "<table><tr><td><pre>"; print_r($NewStockItems); echo "</pre></td><td><pre>"; print_r($StockItems); echo "</pre></td></table>"; /* if($StockIn[$key] > 0 && $StockOut[$key] >0 && $InStock[$key] == 0){ $StockItems[$key]['stock_level'] = ($InStock[$key] + ($StockIn[$key] + $StockOut[$key])); $StockOut[$key] = 0; $StockItems[$key]['stock_out'] = 0; } if($StockIn[$key] != 0 && $StockOut[$key] != 0 && $InStock[$key] != 0){ $StockItems[$key]['stock_level'] = ($InStock[$key] - ($StockOut[$key] + $StockIn[$key])); $StockOut[$key] = 0; $StockItems[$key]['stock_out'] = 0; }*/ /** @Rule 1 # Stockin has value and stock_out = 0 and stock_level = 0 and stock_out_done = null, actualstock to show actual stock level; # */ /*if($StockCalculation[$Skey]['stock_out'] >= $StockItems[$key]['stock_level']){ $StockItems[$key]['stock_out'] = ($StockItems[$key] - 1); $StockItems[$key]['stock_level'] = ($StockItems[$key]['stock_level'] - 1); // If StockOut == 0 next StockOutItem if($StockCalculation[$Skey]['stock_out'] == 0){ $remainder = 0; continue; }elseif($StockItems[$key]['stock_level'] == 0){ //CurrentInStock == 0 then continue to next CurrentItem $remainder = $StockItems[$key]['stock_out']; continue(2); } } $CurrentStockIn = $StockItems[$key]['stock_in']; $CurrentStockOut = $StockItems[$key]['stock_out']; $CurrentInStock = $StockItems[$key]['stock_level']; if($key == 0){ if($CurrentStockIn > 0 && $CurrentStockOut === 0 && $CurrentInStock == 0){ $CurrentInStock = $CurrentStockIn; //Query update stock level set = stock_in //"UPDATE STOCK SET stock_level = {$CurrentStockIn} where id = {$StockItems[$key]['id']}"; $StockItems[$key]['stock_level'] = $CurrentStockIn; } if($CurrentStockIn != 0 && $CurrentStockOut != 0 && $CurrentInStock == 0){ //Query Update Stocklevel if stock_out > 0 and stock_level = 0 //"UPDATE STOCK SET stock_level = "+($CurrentStockIn - $CurrentStockOut) + "where id = {$StockItems[$key]['id']}"; if($CurrentStockIn > $CurrentStockOut && $CurrentStockOut == 0){ $StockItems[$key]['stock_out'] = 0; $StockItems[$key]['stock_level'] = $CurrentInStock = $CurrentStockIn - $CurrentStockOut; } if($CurrentStockOut > $CurrentStockIn){ $StockItems[$key]['stock_level'] = $CurrentInStock = $CurrentInStock - $CurrentStockOut; } } if($CurrentInStock != 0 && $CurrentStockOut > 0){ //If Current in stock below 0 and stock out > 0 then negative more //"UPDATE STOCK SET stock_level = "+($CurrentInStock - $CurrentStockOut) + "where id = {$StockItems[$key]['id']}"; $StockItems[$key]['stock_level'] = $CurrentInStock = ($CurrentInStock - $CurrentStockOut); $StockItems[$key]['stock_out'] = 0; } if($CurrentInStock != 0 && $CurrentStockIn > 0){ //If Current in stock below 0 and stock out > 0 then negative more //"UPDATE STOCK SET stock_level = "+($CurrentInStock - $CurrentStockOut) + "where id = {$StockItems[$key]['id']}"; $StockItems[$key]['stock_level'] = $CurrentInStock = ($CurrentInStock + $CurrentStockIn); } // Run row update for first item }else{ foreach($StockCalculation as $Skey => $Sval){ $NextStockOut = $Sval['stock_out']; $NextStockIn = $Sval['stock_in']; $NextStockLevel = $Sval['stock_level']; if($Skey > 0 && $key > 0){ // print_r($NextStockOut); for($i = $NextStockOut; $i >= -1; $i--){ if($NextStockOut > 0){ if($NextStockOut > 0){ /* if($StockItems[$StockLevelKey]['stock_level'] != 0){ $StockItems[($StockLevelKey)]['stock_level'] = ($StockItems[($StockLevelKey)]['stock_level'] - 1); //$StockItems[($Skey-1)]['stock_out'] = ($StockItems[($Skey-1)]['stock_out'] -1); } if($StockItems[($Skey-1)]['stock_level'] != 0){ $StockItems[($Skey-1)]['stock_level'] = ($StockItems[($Skey-1)]['stock_level'] - 1); } } } $NextStockOut = ($NextStockOut -1); if($NextStockOut != 0){ $StockItems[$Skey]['stock_out'] = 0; break; } } } unset($StockCalculation[$Skey]); } }/* */
  6. Do you mean something like this <?php //MySQL Server 1 $dbhost1 = "dedi212.jnb2.host-h.net"; $dbuser1 = "******"; $dbpassword1 = "*********"; $db1 = "risingfs_rcjan2010"; $connection1 = mysql_connect($dbhost1,$dbuser1,$dbpassword1) or die (mysql_error()); mysql_select_db($db1,$connection1); //The 1st SQL statement $mysql_query =" INSERT INTO risingfs_rcjan2010.aa(risingfs_rcjan2010.aa.a,risingfs_rcjan2010.aa.b)"; //MySQL Server 2 $dbhost2 = "41.66.140.74"; $dbuser2 = "**********"; $dbpassword2 = "**************"; $db2 = "edutoy"; $connection2 = mysql_connect($dbhost1,$dbuser1,$dbpassword1) or die (mysql_error()); mysql_select_db($db2,$connection2); //The 2nd SQL statement $mysql_query =" SELECT edutoy.products.id,edutoy.products.NAME FROM edutoy.products.products"; ?>
  7. thanks for the quick reply - i should have mentioned i am a php newbie!!!!!! If you say 2 seperate queries? tot php files, or split my code up? And how di i then tel what should be imported? Sorry for the questions but this confuses the hell outa me. Regards Paul. Ps one up for PHPFreaks. Out of 20 posts on 20 different forums this is the first response and within an hour. This is great
  8. Thanks for the response. It is not a lot of data i need to send them about 2600 rows. Yes you are correct i am trying to do the 2 at once. Read from my local servers mysql, and then inserting into the warehouse the data wich is on another server. Regards Paul
  9. I have tried the above statement, but nothing happens? i need to export data from my sugarcrm database to a data warehouse on another server. any help will be appreciated
  10. Hi there. I need help to update fields on another server from my server. <?php $dbhost1 = "dedi212.jnb2.host-h.net"; $dbuser1 = "gilan"; $dbpassword1 = "********"; $db1 = "risingfs_rcjan2010"; $connection1 = mysql_connect($dbhost1,$dbuser1,$dbpassword1) or die (mysql_error()); mysql_select_db($db1,$connection1); $dbhost2 = "41.66.140.74"; $dbuser2 = "*********"; $dbpassword2 = "**********"; $db2 = "edutoy"; $connection2 = mysql_connect($dbhost1,$dbuser1,$dbpassword1) or die (mysql_error()); mysql_select_db($db2,$connection2); $mysql_query =" INSERT INTO risingfs_rcjan2010.aa(risingfs_rcjan2010.aa.a,risingfs_rcjan2010.aa.b) SELECT edutoy.products.id,edutoy.products.NAME FROM edutoy.products.products } ?>
×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.