I guess the question of how to regularly synchronize your Ecommerce Templates Website stock count with Store Stock levels is quite complicated and not easily answered. Yes, we can extract a stockinventory.csv file from the website and alter figures in that to agree with the actual Store Stock count. But that is a manual process which seems quite laborious to me.

However, perhaps I am not understanding the best way to go about doing this ?

Has anybody figured a quick method, or even a regularly timed automated method, to accurately sync stock count between website and store ? If so, wow, could you kindly share your solution ?

Gary

Hi Gary,

I made some quick and easy changes to the GMerchLister.php so that it generates a csv file instead of an xml file and a cron job that saves it on the server everyday.

For example here is one that is used to sync with GS1:

5 0 * * * curl YOURWEBSITE.COM/GS1Lister.php >/var/www/html/gs1.csv 2>/dev/null

You can define what columns you want in the includes file for example here is one to sync with GS1:

$gs1columnlist = 'pSKU,pName,pLongDescription,pBrand,pCollection,pPlink,pParent,pID,pDateAdded';

Define the header in a content region, for example:

NumberType: GTIN-13,,,,,,,,
Prefix: 50554271,,,,,,,,
,,,,,,,,
Number,Product Name,Description,Main Brand,Sub Brand,Product Link,MPN,SKU,Updated

Define the context region number:
$gs1listingtemplate=3;


Copy GMerchLister.php to for example GS1Lister.php and define new functions to generate the csv file:

[code]

function gs1listingsheader(){
global $gs1listingtemplate;
$sSQL = "SELECT ".getlangid('contentData',32768)." FROM contentregions WHERE contentID='".escape_string($gs1listingtemplate)."'";
$result=ect_query($sSQL) or ect_error();
if($rs=ect_fetch_assoc($result))
$contentdata=$rs[getlangid("contentData",32768)];
print $contentdata;
}


function Displaygs1Products(){
global $store_name,$GoogleVar1,$seoURL,$countryTaxRate,$includeVAT,$trackingcode,$pCnt,$usepnamefordetaillinks,$detlinkspacechar,$gs1columnlist;
$stext=getrequest('stext');
$stype=getrequest('stype');
$sprice=getrequest('sprice');
$thecat=getrequest('scat');
if($thecat!='') $thecat=(int)$thecat;
$sortorder=@$_COOKIE['psort'];
$catorman=@$_COOKIE['pcatorman'];
$columnlist.=$gs1columnlist;


if(@$_POST['act']=='filteredinventory'){
$whereand=' WHERE ';
if($thecat=='' || $sortorder=='nsf')
$sSQL=' FROM products LEFT OUTER JOIN sections ON products.pSection=sections.sectionID';
else
$sSQL=" FROM multisections RIGHT JOIN products ON products.pId=multisections.pId LEFT OUTER JOIN sections ON products.pSection=sections.sectionID";
if($thecat!=''){
if($catorman=='dis'){
$sSQL.=" INNER JOIN cpnassign ON products.pID=cpnassign.cpaAssignment" . $whereand . "cpnassign.cpaCpnID=" . $thecat;
$whereand=' AND ';
}elseif($catorman=='man'){
$sSQL.=" INNER JOIN multisearchcriteria ON products.pID=multisearchcriteria.mSCpID" . $whereand . "multisearchcriteria.mSCscID=" . $thecat;
$whereand=' AND ';
}else{
$sectionids=getsectionids($thecat, TRUE);
if($sectionids!=''){
if(@$sortorder=='nsf')
$sSQL.=$whereand . " products.pSection IN (" . $sectionids . ") ";
else
$sSQL.=$whereand . " (products.pSection IN (" . $sectionids . ") OR multisections.pSection IN (" . $sectionids . ")) ";
$whereand=' AND ';
}
}
}
if($sprice!=''){
if(strpos($sprice, '-') !== FALSE){
$pricearr=explode('-', $sprice);
if(! is_numeric($pricearr[0])) $pricearr[0]=0;
if(! is_numeric($pricearr[1])) $pricearr[1]=10000000;
$sSQL.=$whereand . "pPrice BETWEEN " . $pricearr[0] . " AND " . $pricearr[1];
$whereand=' AND ';
}elseif(is_numeric($sprice)){
$sSQL.=$whereand . "pPrice='" . escape_string($sprice) . "' ";
$whereand=' AND ';
}
}
if(trim($stext)!=''){
$Xstext=escape_string($stext);
$aText=explode(' ',$Xstext);
if(@$nosearchadmindescription) $maxsearchindex=2; else $maxsearchindex=3;
$aFields[0]='products.pId';
$aFields[1]='pSKU';
$aFields[2]=getlangid('pName',1);
$aFields[3]=getlangid('pDescription',2);
if($stype=='exact'){
$sSQL.=$whereand . "(products.pId LIKE '%" . $Xstext . "%' OR ".getlangid("pName",1)." LIKE '%" . $Xstext . "%' OR ".getlangid("pDescription",2)." LIKE '%" . $Xstext . "%' OR ".getlangid("pLongDescription",4)." LIKE '%" . $Xstext . "%') ";
$whereand=' AND ';
}else{
$sJoin='AND ';
if($stype=='any') $sJoin='OR ';
$sSQL.=$whereand . '(';
$whereand=' AND ';
for($index=0;$index<=$maxsearchindex;$index++){
$sSQL.='(';
$rowcounter=0;
$arrelms=count($aText);
foreach($aText as $theopt){
if(is_array($theopt))$theopt=$theopt[0];
$sSQL.=$aFields[$index] . " LIKE '%" . $theopt . "%' ";
if(++$rowcounter < $arrelms) $sSQL.=$sJoin;
}
$sSQL.=') ';
if($index < $maxsearchindex) $sSQL.='OR ';
}
$sSQL.=') ';
}
}
if(@$_REQUEST['disp']=='6'){ $sSQL.=$whereand . 'pBackOrder<>0'; $whereand=' AND '; }
if(@$_REQUEST['disp']=='7'){ $sSQL.=$whereand . 'pBackOrder=0'; $whereand=' AND '; }
if(@$_REQUEST['disp']=='8'){ $sSQL.=$whereand . 'pGiftWrap<>0'; $whereand=' AND '; }
if(@$_REQUEST['disp']=='9'){ $sSQL.=$whereand . 'pGiftWrap=0'; $whereand=' AND '; }
if(@$_REQUEST['disp']=='10'){ $sSQL.=$whereand . 'pRecommend<>0'; $whereand=' AND '; }
if(@$_REQUEST['disp']=='11'){ $sSQL.=$whereand . 'pRecommend=0'; $whereand=' AND '; }
if(@$_REQUEST['disp']=='12'){ $sSQL.=$whereand . 'pStaticPage<>0'; $whereand=' AND '; }
if(@$_REQUEST['disp']=='13'){ $sSQL.=$whereand . 'pStaticPage=0'; $whereand=' AND '; }
if(@$_REQUEST['disp']=='4'){ $sSQL.=$whereand . '(rootSection IS NULL OR rootSection=0)'; $whereand=' AND '; }
if(@$_REQUEST['disp']=='3'){ $sSQL.=$whereand . '(pInStock<=0 AND pStockByOpts=0)'; $whereand=' AND '; }
if(@$_REQUEST['disp']=='' || @$_REQUEST['disp']=='5'){ $sSQL.=$whereand . 'pDisplay<>0'; $whereand=' AND '; }
if(@$_REQUEST['disp']=='2'){ $sSQL.=$whereand . 'pDisplay=0'; $whereand=' AND '; }
}else
$sSQL=' FROM products';
if($sortorder=='ida')
$sSQL.=' ORDER BY products.pid';
elseif($sortorder=='idd')
$sSQL.=' ORDER BY products.pid DESC';
elseif($sortorder=='')
$sSQL.=' ORDER BY pName';
elseif($sortorder=='na2')
$sSQL.=' ORDER BY pName2';
elseif($sortorder=='na3')
$sSQL.=' ORDER BY pName3';
elseif($sortorder=='nad')
$sSQL.=' ORDER BY pName DESC';
elseif($sortorder=='pra')
$sSQL.=' ORDER BY pPrice';
elseif($sortorder=='prd')
$sSQL.=' ORDER BY pPrice DESC';
elseif($sortorder=='daa')
$sSQL.=' ORDER BY pDateAdded';
elseif($sortorder=='dad')
$sSQL.=' ORDER BY pDateAdded DESC';
elseif($sortorder=='poa')
$sSQL.=' ORDER BY pOrder';
elseif($sortorder=='pod')
$sSQL.=' ORDER BY pOrder DESC';
elseif($sortorder=='sta')
$sSQL.=' ORDER BY products.pInStock';
elseif($sortorder=='std')
$sSQL.=' ORDER BY products.pInStock DESC';
$sSQL='SELECT DISTINCT ' . $columnlist . $sSQL;
$result=ect_query($sSQL) or ect_error();

$fieldlistarr=explode(',',str_replace('products.','',$columnlist));
$addcomma='';

foreach($fieldlistarr as $flarrval){

$addcomma=',';
}
print "\r\n";

while($rs=ect_fetch_assoc($result)){
$addcomma='';
foreach($fieldlistarr as $flarrval){
print $addcomma;




if ($flarrval=="pParent" AND trim($rs["pParent"])=="") {
print '"' . str_replace('"','""',$rs['pID']) . '"' ;

}else{
if ($flarrval=="pPlink" AND trim($rs["pParent"])=="") $prURL=getdetailsurl($rs['pID'],$rs['pStaticPage'],$rs[getlangid('pName',1)],$rs['pStaticURL'],'','').(($trackingcode!='')?"&PARTNER=".$trackingcode:"");

else

$prURL=getdetailsurl($rs['pParent'],$rs['pStaticPage'],$rs[getlangid('pName',1)],$rs['pStaticURL'],'','').(($trackingcode!='')?"&PARTNER=".$trackingcode:"");
if ($flarrval=="pPlink" AND trim($rs["pParent"])!="")
print $GLOBALS['storeurl'].$prURL;
else
if ($flarrval=="pPlink" AND trim($rs["pParent"])=="") print $GLOBALS['storeurl'].$prURL;
else
print '"' . str_replace('"','""',$rs[$flarrval]) . '"';

}


$addcomma=',';
}

print $addcomma;
print "\r\n";
}
ect_free_result($result);
}

[/code]

Change to the new functions instead of the original ones.

gs1listingsheader();

Displaygs1Products();

save the new GS1Lister.php to the web server and then add to your cron:

5 0 * * * curl YOURWEBSITE.COM/GS1Lister.php >/var/www/html/gs1.csv 2>/dev/null

We have all sorts that create custom xml and csv feeds that sync with the likes of Amazon, Bing, facebook, etc...

John