Jump to content

[Solved] Automatic Stock Update (Csv) Solution

thijsvk

By thijsvk
in Core developers

 Share

Recommended Posts

WisQQ Enthusiast

WisQQ

Posted
Posted
7 hours ago, Kaper said:

Does anyone using that script also for .XML File? Is that possible? Can you share the code please? Thanks!

It shouldnt be a problem to adapt it to XML. You only have to change part reading CSV file to read XML data into array.

Link to comment
Share on other sites
WisQQ Enthusiast

WisQQ

Posted
Posted
On 4/7/2022 at 4:23 PM, Kaper said:

 

@WisQQ

Thanks, It would be great if somebody can contribute to community this type of php script with possibility to read XML file. Unfortunatelly i am not familiar in programming, otherwise i would be glad to help. 

To do anything with XML you have to show how data is structured. It's much easier to do it with csv because it's simple as excel file.

Link to comment
Share on other sites
  • 3 weeks later...
WisQQ Enthusiast

WisQQ

Posted
Posted
50 minutes ago, Kaper said:

Please I have following problem,

my quantity row is like "> 12" , so i need to remove/replace the ">" character, can you please help me how in php? 

 $quantity      = ($data[3] >= 0) ? $data[3] : 0;  // need to replace ">" sign

 

Screenshot from 2022-04-23 20-00-16.png

Use str_replace to remove it.
$qty = str_replace('>','',$data[3]);
$quantity = ($qty >= 0) ? $qty : 0;

Link to comment
Share on other sites
Kaper Explorer

Kaper

Posted
Posted (edited)

 

WoW, that was quick :) Thanks, i have tried it, but stock is not updated  :/, isn't problem somwhere here? " !is_numeric($data[3])"

  

<?php

// MAIN CYCLE
$row_num = 0;
if (($handle = fopen($remote_csv_file, "r")) !== false) {
  while (($data = fgetcsv($handle, 100000, ";")) !== false) {
    $row_num++;
    if ($row_num == 1) {
      // SKIP FIRST LINE (HEADER)
      continue;
    }
    //quantity = EAN -->  STOCK
    if ($data[2] == '' || !is_numeric($data[3])) {
      // SKIP EMPTY VALUES
      continue;
    }
    //price = EAN -->  PRICE
    if ($data[2] == '' || !is_numeric($data[1])) {
      // SKIP EMPTY VALUES
      continue;
    }
    //price bezna = EAN --> PRICE BEZNA
    if ($data[2] == '' || !is_numeric($data[0])) {
      // SKIP EMPTY VALUES
      continue;
    }

    // INPUT SANITIZATION   
    $ean13         = trim($data[2]);    
      
    $qty = str_replace('>','',$data[3]); //  STOCK, QUANTITY
    $quantity = ($qty >= 0) ? $qty : 0;  

    $price         = ($data[1] >= 0) ? $data[1] : 0;  //  PRICE
    $price_bezna   = ($data[0] >= 0) ? $data[0] : 0;  //  PRICE BEZNA
Edited by Kaper (see edit history)
Link to comment
Share on other sites
WisQQ Enthusiast

WisQQ

Posted
Posted
10 minutes ago, Kaper said:

WoW, that was quick :) Thanks, i have tried it, but stock is not updated  :/, isn't problem somwhere here? " !is_numeric($data[3])"

 

  

// MAIN CYCLE
$row_num = 0;
if (($handle = fopen($remote_csv_file, "r")) !== false) {
  while (($data = fgetcsv($handle, 100000, ";")) !== false) {
    $row_num++;
    if ($row_num == 1) {
      // SKIP FIRST LINE (HEADER)
      continue;
    }
    //quantity = EAN -->  STOCK
    if ($data[2] == '' || !is_numeric($data[3])) {
      // SKIP EMPTY VALUES
      continue;
    }
    //price = EAN -->  PRICE
    if ($data[2] == '' || !is_numeric($data[1])) {
      // SKIP EMPTY VALUES
      continue;
    }
    //price bezna = EAN --> PRICE BEZNA
    if ($data[2] == '' || !is_numeric($data[0])) {
      // SKIP EMPTY VALUES
      continue;
    }

    // INPUT SANITIZATION   
    $ean13         = trim($data[2]);
    
    $quantity      = ($data[3] >= 0) ? $data[3] : 0;  //  STOCK, QUNTITY
    $qty = str_replace('>','',$data[3]);
    $quantity = ($qty >= 0) ? $qty : 0;  

    $price         = ($data[1] >= 0) ? $data[1] : 0;  //  PRICE
    $price_bezna   = ($data[0] >= 0) ? $data[0] : 0;  //  PRICE BEZNA

Yes, "!is_numeric($data[3])" is the problem. You are trying to check if string value is number, before you remove special characters. Because of that it reads it as plain text and continues to the next argument.

You should remove special characters before IF's 

<?php
$row_num = 0;
if (($handle = fopen($remote_csv_file, "r")) !== false) {
  while (($data = fgetcsv($handle, 100000, ";")) !== false) {
    $row_num++;
    if ($row_num == 1) {
      // SKIP FIRST LINE (HEADER)
      continue;
    }
	$qty = str_replace('>','',$data[3]);
    //quantity = EAN -->  STOCK
    if ($data[2] == '' || !is_numeric($qty)) {
      // SKIP EMPTY VALUES
      continue;
    }
    //price = EAN -->  PRICE
    if ($data[2] == '' || !is_numeric($data[1])) {
      // SKIP EMPTY VALUES
      continue;
    }
    //price bezna = EAN --> PRICE BEZNA
    if ($data[2] == '' || !is_numeric($data[0])) {
      // SKIP EMPTY VALUES
      continue;
    }

    // INPUT SANITIZATION   
    $ean13         = trim($data[2]);
    
    $quantity = ($qty >= 0) ? $qty : 0;    //  STOCK, QUNTITY
    


    $price         = ($data[1] >= 0) ? $data[1] : 0;  //  PRICE
    $price_bezna   = ($data[0] >= 0) ? $data[0] : 0;  //  PRICE BEZNA

 

Link to comment
Share on other sites
Kaper Explorer

Kaper

Posted
Posted (edited)

edit: i have changed  !is_numeric to  !is_string and it works, if the csv looks like this: (Without quotes "" ) ... 

> 12

When the csv  file contains 

"> 12"

it won't work

 

//nevermind:
It works, i have removed the "" from the CSV files, and it's okey right now, many thanks to you my friend :) .

Edited by Kaper (see edit history)
Link to comment
Share on other sites
WisQQ Enthusiast

WisQQ

Posted
Posted
5 minutes ago, Kaper said:

edit: i have changed  !is_numeric to  !is_string and it works, if the csv looks like this: (Without quotes "" ) ... 

> 12

When the csv  file contains 

"> 12"

it won't work

Try the code i posted above. You need to remove ">" first then you can check if variable is numeric.

Link to comment
Share on other sites
Kaper Explorer

Kaper

Posted
Posted

I have one more Q, is it possible skip import / ignore product quantity update, when quantity of imported product = 0 ? Where to put the condition , where it will be the best?

Thanks for your time, i really appreciate it.

Link to comment
Share on other sites
WisQQ Enthusiast

WisQQ

Posted
Posted
1 godzinę temu, Kaper napisał:

I have one more Q, is it possible skip import / ignore product quantity update, when quantity of imported product = 0 ? Where to put the condition , where it will be the best?

Thanks for your time, i really appreciate it.

Update function already checks if new quantity is different than the one in stock, but you can expand it with additional argument.
  

Function Update(){
	$db = connect();
	$csvFile = 'fichier/StockInternet.csv';
	$csv = readCSV($csvFile);
	$sp = shop_products(0);
	$plupdate=array();
	$product = $db->prepare("UPDATE ps_product LEFT JOIN ps_stock_available on ps_stock_available.id_product=ps_product.id_product SET ps_stock_available.quantity = :avbl WHERE ps_product.reference = :indeks AND ps_stock_available.id_product_attribute = 0");	
	$combination = $db->prepare("UPDATE ps_product_attribute LEFT JOIN ps_stock_available on ps_stock_available.id_product_attribute=ps_product_attribute.id_product_attribute LEFT JOIN ps_product on ps_product_attribute.id_product=ps_product.id_product SET ps_stock_available.quantity = :avbl WHERE ps_product_attribute.reference = :indeks");	
	foreach($sp as $ps){
		$indeks = $ps['REF'];
		$qty = $ps['QTY'];
		$ID = $ps['ID'];
		$base = $ps['BaseP'];
		if(!empty($indeks)){
			foreach($csv as $stock){
				$index = $stock['Reference'];
				$avbl = $stock['Quantite'];
				if($indeks == $index){
					if($qty != $avbl){ // check if new quantity is different than in stock
						if(isset($base)){
							try{
								$product->execute([':avbl'=>$avbl,':indeks'=>$indeks]);
								$log  = $indeks.PHP_EOL.
									"simple produit - quantite changee de ".$qty." a ".$avbl.PHP_EOL.
									"-------------------------".PHP_EOL;
								file_put_contents('./log/log_'.date("j.n.Y").'.log', $log, FILE_APPEND);
							}catch(PDOException $e){
								$log  = $indeks.PHP_EOL.
									"error ".$e->getMessage().PHP_EOL.
									"-------------------------".PHP_EOL;
								file_put_contents('./log/log_'.date("j.n.Y").'.log', $log, FILE_APPEND);
								die();
							}							
						}else{
							$plupdate[]=$ID;
							try{
								$combination->execute([':avbl'=>$avbl,':indeks'=>$indeks]);
								$log  = $indeks.PHP_EOL.
									"product combination - quantity change from ".$qty." to ".$avbl.PHP_EOL.
									"-------------------------".PHP_EOL;
								file_put_contents('./log/log_'.date("j.n.Y").'.log', $log, FILE_APPEND);
							}catch(PDOException $e){
								$log  = $indeks.PHP_EOL.
									"error ".$e->getMessage().PHP_EOL.
									"-------------------------".PHP_EOL;
								file_put_contents('./log/log_'.date("j.n.Y").'.log', $log, FILE_APPEND);
								die();
							}							
						}
					}
				}
			}
		}	
	}
	return $plupdate;
}

 

Link to comment
Share on other sites
WisQQ Enthusiast

WisQQ

Posted
Posted
6 godzin temu, Kaper napisał:

If I understood correctly, my code is different from yours, that's why i am looking just for one if statement, when quantity row $data[3] is EMPTY or = 0, ignore that line.

 

if(!empty($data[3]) && data[3] != 0) should do the work

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share
Go to topic listing
×
×
  • Create New...