Hi all, so I have this site on Wordpress and at 2 pm & 6 pm this php file runs and puts adds products to drafts in woocommerce automatically.
I would like to add the manufacturer barcode as another field that is uploaded to the website. I have a field created in woocommerce, but it’s getting it to there that is the problem. I’m not skilled enough in PHP to do this.
This is a sample product from the xml
the manufacturerbarcode is what i want added.
this is the php file
<?php
/*
ini_set('display_errors', 1);
ini_set('display_startup_errors', 1);
error_reporting(E_ALL);
*/
require_once("../../../../../wp-blog-header.php");
$wp->init();
$wp->parse_request();
$wp->query_posts();
$wp->register_globals();
status_header(200);
$wp->send_headers();
function getVatRate($exvat, $inclvat)
{
$result= '';//standard class, nothing in
$rate= round(($inclvat * 100) / $exvat)-100;
if ($rate!=23)
{
if ($rate == 0)
{
$result= 'zero-rate';
}
else if ($rate == 13.5)
{
$result= 'reduced-rate';
}
else
{
$result= 'Error: rate '. $rate. '% is not matching any known rate';
}
}//end if
return $result;
}
$start=0;
$limit= 10000000;
if ( isset($_GET['start']) ){$start = $_GET['start']; }
if ( isset($_GET['limit']) ){$limit = $_GET['limit']; }
$errors= array();
$result= array();
$sales_warning= array();
$prods_added= array();
$stats= array('prods'=> 0,'invalid'=> 0, 'valid'=> 0, 'new_to_be_added'=> 0,'new_added_errors'=> 0, 'new_ign'=> 0, 'updated'=>0,'updated_stock'=>0,'updated_price'=>0, 'no_update_needed'=>0);
//connect to the database
$host = DB_HOST;$username = DB_USER;$password= DB_PASSWORD;$dbname = DB_NAME;
$conn = new mysqli($host,$username,$password,$dbname);
if ($conn->connect_errno)
{
$errors[]= "Error connecting to the DB";
}
else
{
//start with the feed
$file = "file/parts.xml";
if ( !file_exists($file) )
{
$errors[]='Could not find the file parts.xml';
}
else
{//file exists
$xml = simplexml_load_file($file);
$rowN=1;
if (count($xml) < 7000)
{
//we stop the script if there is less rows than expected
echo "Error, less rows than expected";
$errors[]="The system has detected than you have less than 7,000 products in your feed, you currently have ".count($xml)." products in. This is less than usual and therefore your feed has not run, please check the feed or contact the web developer to reduce the number of expected products.";
}//end if
else
{
//get all current sku's so we do not go to the database every time
$skus= array();
$getSku= $conn-> query("SELECT meta_value, post_id FROM peab_postmeta WHERE meta_key = '_sku' AND meta_value !='' ");
$getDetail= $conn -> prepare("SELECT meta_key, meta_value FROM peab_postmeta WHERE (meta_key= '_regular_price' OR meta_key= '_sale_price' OR meta_key= '_price' OR meta_key= '_stock' OR meta_key= '_stock_status' OR meta_key= '_tax_class' )AND post_id= ?");
while ($rsSku= $getSku -> fetch_array())
{
//$getDetail = $conn-> query("SELECT meta_value FROM peab_postmeta WHERE meta_key = '_sku'");
$this_sku= array('post_id' => $rsSku['post_id'] ,'_regular_price'=>99999, '_price'=> 99999, '_sale_price'=> '', '_stock'=> 99999,'_stock_status'=> '', 'tax_class'=> '');
$getDetail -> bind_param('i', $rsSku['post_id']);
$getDetail -> execute();
$result = $getDetail -> get_result();
while ($res = $result -> fetch_object())
{
if ($res -> meta_key == '_regular_price'){$this_sku['_regular_price']= floatval($res-> meta_value);}
if ($res -> meta_key == '_sale_price'){$this_sku['_sale_price']= $res-> meta_value;}//keep as a string until we actual need it
if ($res -> meta_key == '_price'){$this_sku['_price']= floatval($res-> meta_value);}
if ($res -> meta_key == '_stock'){$this_sku['_stock']= (int)$res-> meta_value;}
if ($res -> meta_key == '_stock_status'){$this_sku['_stock_status']= (string)$res-> meta_value;}
if ($res -> meta_key == '_tax_class'){$this_sku['_tax_class']= (string)$res-> meta_value;}
}
$skus[(string)$rsSku['meta_value']]= $this_sku;
}//end while
$getDetail -> close();
//var_dump($skus);
//print_r($skus);
//exit;
//number of rows seems ok
foreach($xml AS $row)
{
if ($rowN > $start && $rowN < ($start + $limit) )
{
$partnumber= (string)$row['partnumber'];
$partnumber= preg_replace('/\s+/', ' ', $partnumber);
$description= (string)$row['description'];
$retailprice= floatval($row['retailprice']);
$vatincprice= floatval($row['vatincprice']);
$freestock= (int)$row['freestock'];
//var_dump($partnumber);
//echo "\n ". $partnumber;
$stats['prods']++;
$isValid = 1;
if (strlen($partnumber) < 5)
{
$result[]= "At row $rowN the part number '".$partnumber."' does not appear valid";
$isValid = 0;
$stats['invalid']++;
}
if ($isValid == 1)
{
$stats['valid']++;
//see if the product already exist in the database
if ( isset($skus[$partnumber]) )
{
$thisSku= $skus[$partnumber];
$dd = $partnumber. " (".$thisSku['post_id'].")\n";//used for debug detail only
//this is an update
//check to see if some of the data has changed
$has_changes= 0;
//price, first we see if the regular price has changed
//see if the stock has changed
if ($freestock == 0)
{
//no stock
if ($thisSku['_stock_status'] == 'outofstock')
{
//stock is already marked as out, no need to change anything else
//nothing to do in this case
}
else
{
//stock is still mark as in stock, we mark it as out
$conn -> query("UPDATE peab_postmeta SET meta_value= 'outofstock' WHERE meta_key = '_stock_status' AND post_id = ".$thisSku['post_id']." ");
$conn -> query("UPDATE peab_postmeta SET meta_value= 0 WHERE meta_key = '_stock' AND post_id = ".$thisSku['post_id']." ");
$conn -> query("UPDATE peab_postmeta SET meta_value= 'yes' WHERE meta_key = '_manage_stock' AND post_id = ".$thisSku['post_id']." ");
$stats['updated_stock']++;
$has_changes= 1;
}//end else
}//end of no stock
else
{
//there is stock
//see if the stock has changed or the stock status has changed
if ($thisSku['_stock_status']!='instock' || $thisSku['_stock'] != $freestock)
{
$conn -> query("UPDATE peab_postmeta SET meta_value= 'instock' WHERE meta_key = '_stock_status' AND post_id = ".$thisSku['post_id']." ");
$conn -> query("UPDATE peab_postmeta SET meta_value= ".$freestock." WHERE meta_key = '_stock' AND post_id = ".$thisSku['post_id']." ");
$conn -> query("UPDATE peab_postmeta SET meta_value= 'yes' WHERE meta_key = '_manage_stock' AND post_id = ".$thisSku['post_id']." ");
$stats['updated_stock']++;
$has_changes= 1;
}//end if
//now check for price changes
if ( $retailprice == 0)
{
$errors[] = "Product part number $partnumber was sent with a retail price of 0, this product was ignored";
}
else
{//retail price not 0
if ( $retailprice != $thisSku['_price'])
{
//fix in case where sale price would be at 0
if ($thisSku['_sale_price']==0)
{
//this is wrong, we update now
$conn -> query("UPDATE peab_postmeta SET meta_value= '' WHERE meta_key = '_sale_price' AND post_id = ".$thisSku['post_id']." ");
$has_changes= 1;
}
//we check _price as this is the price if it is on sale or not
//echo "\n change of price ". $dd . "\$retailprice $retailprice\n";
//first we need to see if the product is on sale
if ($thisSku['_sale_price'] == '')
{
//this is a regular price, we update the two _price and _regular_price
$conn -> query("UPDATE peab_postmeta SET meta_value= ".$retailprice." WHERE meta_key = '_price' AND post_id = ".$thisSku['post_id']." ");
$conn -> query("UPDATE peab_postmeta SET meta_value= ".$retailprice." WHERE meta_key = '_regular_price' AND post_id = ".$thisSku['post_id']." ");
$stats['updated_price']++;
$has_changes= 1;
}//end product is not on sale
else
{//product is on sale
//now we float the sales price
$thisSku['_sale_price']= floatval($thisSku['_sale_price']);
//if the product is on sale we check to see if we keep it on sale
// if the new price is higher than the regular price, we remove the sale
if ($retailprice >= $thisSku['_regular_price'])
{
$sales_warning[]= "Product part number $partnumber: sale removed because the new price is above the old regular price";
//remove the sale
$conn -> query("UPDATE peab_postmeta SET meta_value= ".$retailprice." WHERE meta_key = '_price' AND post_id = ".$thisSku['post_id']." ");
$conn -> query("UPDATE peab_postmeta SET meta_value= ".$retailprice." WHERE meta_key = '_regular_price' AND post_id = ".$thisSku['post_id']." ");
$conn -> query("UPDATE peab_postmeta SET meta_value= '' WHERE meta_key = '_sale_price' AND post_id = ".$thisSku['post_id']." ");
$stats['updated_price']++;
$has_changes= 1;
}//end remove sale
else
{//keep the sale
//in this case we do not touch the regular price
$conn -> query("UPDATE peab_postmeta SET meta_value= ".$retailprice." WHERE meta_key = '_price' AND post_id = ".$thisSku['post_id']." ");
$conn -> query("UPDATE peab_postmeta SET meta_value= ".$retailprice." WHERE meta_key = '_sale_price' AND post_id = ".$thisSku['post_id']." ");
$stats['updated_price']++;
$has_changes= 1;
}//end keep the sale
}//end else product is on sale
//print_r($thisSku);
}//end if the price is different
else
{
//echo $dd;
}
}//end else retail price 0
//check that the VAT rate is correct
$vat_class= getVatRate($retailprice, $vatincprice);
if (strpos($vat_class,'Error:')!==false)
{
$errors[] = "Product part number $partnumber VAT error: $vat_class -> the product was updated with the standard rate of VAT";
//for this, mark the product at the standard rate
$vat_class= '';//standard VAT
}
//see if there is a change
if ($vat_class != $thisSku['_tax_class'])
{
$conn -> query("UPDATE peab_postmeta SET meta_value= '".$vat_class."' WHERE meta_key = '_tax_class' AND post_id = ".$thisSku['post_id']." ");
$has_changes= 1;
}
}//end else, there is stock
if ($has_changes==1)
{
$stats['updated']++;
//clear the cache
if (function_exists('rocket_clean_post'))
{
rocket_clean_post( $thisSku['post_id'] );
}
}
else
{
$stats['no_update_needed']++;
}
}
else
{//this is a new product
//echo "\n New: ". $row['partnumber'];
//if it is new but the stock is at 0 we do not add it as it is considered as redundant in the feed
if ($row['freestock']==0)
{
//no stock, we do nothing
$stats['new_ign']++;
}
else
{
//add this product as draft
if ($retailprice==0)
{
$errors[] = "Product part number $partnumber the price is 0, the product was not added ";
$stats['new_ign']++;
}
else
{//$retailprice not 0
//get the VAT first
$vat_class= getVatRate($retailprice, $vatincprice);
if (strpos($vat_class,'Error:')!==false)
{
$errors[] = "Product part number $partnumber VAT error: $vat_class -> the product was not added ";
$stats['new_added_errors']++;
//for this, mark the product at the standard rate
$vat_class= '';//standard VAT
$stats['new_ign']++;
}
else
{//VAT rate found
//echo "\nAdding ". $partnumber . " -> $description , VAT: ". $vat_class;
$post = array(
'post_author' => 1,
'post_content' => '',
'post_status' => "draft",
'post_title' => sanitize_text_field($description),
'post_parent' => '',
'post_type' => "product"
);
//Create post
$post_id = wp_insert_post( $post );
if($post_id)
{
//wp_set_object_terms($post_id, 'simple', 'product_type');
update_post_meta( $post_id, '_visibility', 'visible' );
update_post_meta( $post_id, '_stock_status', 'instock');
update_post_meta( $post_id, '_downloadable', 'no');
update_post_meta( $post_id, '_virtual', 'no');
update_post_meta( $post_id, '_regular_price', $retailprice );
update_post_meta( $post_id, '_price', $retailprice );
update_post_meta( $post_id, '_sale_price', "" );
update_post_meta($post_id, '_sku', sanitize_text_field($partnumber));
update_post_meta( $post_id, '_manage_stock', "yes" );
update_post_meta( $post_id, '_backorders', "no" );
update_post_meta( $post_id, '_stock', $freestock);
update_post_meta( $post_id, 'from_feed', 1);
update_post_meta( $post_id, '_tax_class', $vat_class);
//update_post_meta( $post_id, 'total_sales', '0');
//update_post_meta( $post_id, '_purchase_note', "" );
//update_post_meta( $post_id, '_featured', "no" );
//update_post_meta( $post_id, '_weight', "" );
//update_post_meta( $post_id, '_length', "" );
//update_post_meta( $post_id, '_width', "" );
//update_post_meta( $post_id, '_height', "" );
//update_post_meta( $post_id, '_product_attributes', array());
//update_post_meta( $post_id, '_sale_price_dates_from', "" );
//update_post_meta( $post_id, '_sale_price_dates_to', "" );
//update_post_meta( $post_id, '_sold_individually', "" );
$prods_added[]= "Part Number $partnumber -> ".$description;
}
else
{
$stats['new_added_errors']++;
$errors[]= "Error adding a new product part number $partnumber (Unknown error, no post ID returned )";
}
$stats['new_to_be_added']++;
}//end else vat rate found
}//end else $retailprice not 0
}//end else add this product as draft
}//end of new product
}//end if is valid
}//end limit the number of rows, to be removed
$rowN++;
}//end foreach $xml
}//end else, number of rows OK
}//end file exists
}//end else
//logs
$conn -> query("INSERT INTO tblfeed_log SET date_run = NOW(), stats= '".$conn -> real_escape_string(json_encode($stats,JSON_HEX_APOS))."', sales_warnings = '".$conn -> real_escape_string(json_encode($sales_warning,JSON_HEX_APOS))."' , prods_added = '".$conn -> real_escape_string(json_encode($prods_added,JSON_HEX_APOS))."'");
//email
echo "\n\n\n";
print_r($stats);
$subject= date('j F h:i:s A');
$body = '<P>Dear Administrator, <br> The product feed has completed running on the site at '.date('l jS \of F Y h:i:s A').'</p><strong>Result:</strong>
<ul>
<li><strong>Number of products in the feed: </strong>'.$stats['prods'].'</li>
<li><strong>Valid rows (some may include errors listed below): </strong>'.$stats['valid'].'</li>
<li><strong>Invalid rows: </strong>'.$stats['invalid'].'</li>
<li><strong>New products to be added: </strong>'.$stats['new_to_be_added'].'
<ul>
<li><strong>New products with errors (could not be added): </strong>'.$stats['new_added_errors'].'</li>
</ul>
</li>
<li><strong>New products found but ignored: </strong>'.$stats['new_ign'].' (products with a 0 stock level that have never been added to the database will be ignored and considered as old parts)</li>
<li><strong>Number of products updated: </strong>'.$stats['updated'].'
<ul>
<li><strong>Stock update: </strong>'.$stats['updated_stock'].'</li>
<li><strong>Price update: </strong>'.$stats['updated_stock'].'</li>
</ul>
</li>
<li><strong>Number of products with no update needed: </strong>'.$stats['no_update_needed'].'</li>
</ul>
';
if ( count($errors) )
{
$subject .= ' - '.count($errors).' ERRORS';
$body .= '<br><strong>'. count($errors) . ' ERROR(S):</strong><br><ul>';
foreach($errors AS $error)
{
$body .= '<li>'.$error.'</li>';
}
$body .='</ul>';
}
if ( count($sales_warning) )
{
$subject .= ' - '.count($sales_warning).' WARNINGS';
$body .= '<br><strong>'. count($sales_warning) . ' SALE WARNING(S):</strong><br>Sale warning are to let you know of items sales removed because of price change<ul>';
foreach($sales_warning AS $sales_w)
{
$body .= '<li>'.$sales_w.'</li>';
}
$body .='</ul>';
}
if ( count($prods_added) )
{
$subject .= ' - '.count($prods_added).' NEW PRODUCTS';
$body .= '<br><strong>'. count($prods_added) . ' NEW PRODUCT(S):</strong><br>The following have been added as drafts<ul>';
foreach($prods_added AS $prod)
{
$body .= '<li>'.$prod.'</li>';
}
$body .='</ul>';
}
//echo $body;
function wpmail_html_set_content_type(){return "text/html";}
add_filter( 'wp_mail_content_type','wpmail_html_set_content_type' );
wp_mail('', $subject, $body);
//wp_mail('', $subject, $body);
?>
Any body have any ideas?