<?php

$PageSecurity=1;

include('includes/session.inc');

$title=_('Synchronise Stocks to Zencart');

include('includes/header.inc');

echo '<form method="post" action="' . htmlspecialchars($_SERVER['PHP_SELF']) . '" >';
echo '<input type="hidden" name="FormID" value="' . $_SESSION['FormID'] . '" />';

echo '<p class="page_title_text"><img src="'.$rootpath.'/css/'.$theme.'/images/group_add.png" title="' . _('Search') . '" alt="" />' . ' ' . $title.'</p>
	<br />';

if (isset($_POST['sync_stocks'])) 
{ 
  //=====Get Zencart Details (Start)=====
  $sqlGetZencartDetails = "SELECT id, zencart_db, emp_stock_location FROM emp_zencart_integration WHERE id=1";
  $resultGetZencartDetails = DB_query($sqlGetZencartDetails,$db);
  $myrowGetZencartDetails = DB_fetch_array($resultGetZencartDetails);
	
	$zencartDB = $myrowGetZencartDetails['zencart_db'];
	$empLocation = $myrowGetZencartDetails['emp_stock_location'];
	//=====Get Zencart Details (End)=====
	
	//=====Add Stock Categories (Start)=====
	$sqlGetEMPStockCategories = "SELECT categoryid, categorydescription FROM stockcategory";
	$resultGetEMPStockCategories = DB_query($sqlGetEMPStockCategories,$db);
  
  while($myrowGetEMPStockCategories = DB_fetch_array($resultGetEMPStockCategories))
  {
    $AddCategoryToZencart = 1;
    $empCategory = $myrowGetEMPStockCategories['categorydescription'];
    
    $sqlGetZencartStockCategories = "SELECT categories_name FROM ".$zencartDB.".categories_description";
    $resultGetZencartStockCategories = DB_query($sqlGetZencartStockCategories,$db);
    
    while($myrowGetZencartStockCategories = DB_fetch_array($resultGetZencartStockCategories))
    {
      $zencartCategory = $myrowGetZencartStockCategories['categories_name'];
      
      if(strcmp($zencartCategory, $empCategory) == 0)
      {
        $AddCategoryToZencart = 0;
      }
    }
    
    if($AddCategoryToZencart == 1)
    {
      $sqlAddNewCategory = "INSERT INTO ".$zencartDB.".categories (parent_id, sort_order, date_added, categories_status)
                              VALUES ('0', '0', NOW(), '1')";
      $ErrMsgAddNewCategory = _('The category') . ' ' . $empCategory . ' ' . _('could not be added to Zencart database because');
      $DbgMsgAddNewCategory = _('The SQL that failed was');
      $resultAddNewCategory = DB_query($sqlAddNewCategory, $db, $ErrMsgAddNewCategory, $DbgMsgAddNewCategory);
      
      //=====Get Latest Category ID of Zencart (Start)=====
      DB_query("LOCK TABLES ".$zencartDB.".categories WRITE",$db);

      $sqlGetLatestCatID = "SELECT MAX(categories_id) FROM ".$zencartDB.".categories";

      $ErrMsgGetLatestCatID = _('CRITICAL ERROR') . '! ' . _('NOTE DOWN THIS ERROR AND SEEK ASSISTANCE') . ': <BR>' . _('The latest category ID could not be retrieved from the database because');
      $DbgMsgGetLatestCatID =  _('The following SQL to retrieve the latest category ID was used');
      $resultGetLatestCatID = DB_query($sqlGetLatestCatID,$db,$ErrMsgGetLatestCatID,$DbgMsgGetLatestCatID);

      $myrowGetLatestCatID = DB_fetch_row($resultGetLatestCatID);
      
      $LatestCategoryID=$myrowGetLatestCatID[0];
      
      DB_query("UNLOCK TABLES",$db);
      //=====Get Latest Category ID of Zencart (End)=====
            
      $sqlAddNewCategoryDescription = "INSERT INTO ".$zencartDB.".categories_description (categories_id, language_id, categories_name, categories_description)
                                        VALUES ('".$LatestCategoryID."', '1', '".$empCategory."', 'EMP ID: ".$myrowGetEMPStockCategories['categoryid']."')";
      $ErrMsgAddNewCategoryDescription = _('The category') . ' ' . $empCategory . ' ' . _('could not be added to Zencart database because');
      $DbgMsgAddNewCategoryDescription = _('The SQL that failed was');
      $resultAddNewCategoryDescription = DB_query($sqlAddNewCategoryDescription, $db, $ErrMsgAddNewCategoryDescription, $DbgMsgAddNewCategoryDescription);
      prnMsg(_('Category Description') . ' ' . $empCategory . ' ' . _(' has been successfully added to Zencart database'), 'success');
        
    }else{
      //prnMsg(_('No new category to be added to Zencart database'), 'warn');
    }
  }
  //=====Add Stock Categories (End)=====
  
  
	//=====Add/Update Stock Details (Start)=====
	$sqlGetEMPStockDetails = "SELECT stockid, categoryid, description, taxcatid FROM stockmaster WHERE discontinued=0";
	$resultGetEMPStockDetails = DB_query($sqlGetEMPStockDetails,$db);
  
  while($myrowGetEMPStockDetails = DB_fetch_array($resultGetEMPStockDetails))
  {
    $AddStockToZencart = 1;
    $empStockID = $myrowGetEMPStockDetails['stockid'];
    $empStockCategoryID = $myrowGetEMPStockDetails['categoryid'];
    $empStockDescription = $myrowGetEMPStockDetails['description'];
    $empStockTaxCatID = $myrowGetEMPStockDetails['taxcatid'];
    
    $sqlGetZencartStockDetails = "SELECT products_name FROM ".$zencartDB.".products_description";
    $resultGetZencartStockDetails = DB_query($sqlGetZencartStockDetails,$db);
    
    while($myrowGetZencartStockDetails = DB_fetch_array($resultGetZencartStockDetails))
    {
      $zencartStockName = $myrowGetZencartStockDetails['products_name'];
      
      if(strcmp($zencartStockName, $empStockID) == 0)
      {
        $AddStockToZencart = 0;
      }
    }
    
    //=====Get price and quantity of stock (Start)=====
    //Now use the default salestype/price list cos all else has failed
    $sqlGetPrice="SELECT price
                    FROM prices
                    WHERE prices.stockid = '" . $empStockID . "'
                    AND prices.currabrev = 'MYR'
                    AND prices.typeabbrev='NE'
                    AND prices.startdate <='" . Date('Y-m-d') . "'
                    AND prices.enddate >='" . Date('Y-m-d') . "'";
    $ErrMsgGetPrice =  _('There is a problem in retrieving the pricing information for part') . ' ' . $empStockID  .  ' ' . _('the error message returned by the SQL server was');
    $resultGetPrice = DB_query($sqlGetPrice, $db,$ErrMsgGetPrice);
    
    if (DB_num_rows($resultGetPrice)==0){	
      
      //Now use the default salestype/price list cos all else has failed
      $sqlGetPrice="SELECT price
                    FROM prices
                    WHERE prices.stockid = '" . $empStockID . "'
                    AND prices.currabrev = 'MYR'
                    AND prices.typeabbrev='NE'
                    AND prices.startdate <='" . Date('Y-m-d') . "'
                    AND prices.enddate ='0000-00-00'
                    ORDER BY prices.startdate DESC";
            
      $resultGetPrice = DB_query($sqlGetPrice, $db,$ErrMsgGetPrice);
    }
    
    if (DB_num_rows($resultGetPrice)!=0){
      //There is a price from one of the above so return that

      $myrowGetPrice = DB_fetch_array($resultGetPrice);
      $stockPrice = $myrowGetPrice['price'];
    } else {
      $stockPrice = 0;
    }
    
    $sqlGetQtty = "SELECT quantity FROM locstock WHERE loccode = '".$empLocation."' AND stockid = '".$empStockID."'";
    $ErrMsgGetQtty =  _('There is a problem in retrieving the quantity information for part') . ' ' . $empStockID  .  ' ' . _('the error message returned by the SQL server was');
    $resultGetQtty = DB_query($sqlGetQtty, $db,$ErrMsgGetQtty);
    $myrowGetQtty = DB_fetch_array($resultGetQtty);
    $stockQtty = $myrowGetQtty['quantity'];
    //$stockQtty = 10; //for testing, to be removed
    //=====Get price and quantity of stock (End)=====  
      
    if($AddStockToZencart == 1)
    {
      $sqlGetEMPStockCategoryName = "SELECT categorydescription FROM stockcategory WHERE categoryid='".$empStockCategoryID."'";
      $resultGetEMPStockCategoryName = DB_query($sqlGetEMPStockCategoryName,$db);
      $myrowGetEMPStockCategoryName = DB_fetch_array($resultGetEMPStockCategoryName);
  
      $sqlGetZencartStockCategoryID = "SELECT categories_id FROM ".$zencartDB.".categories_description WHERE categories_name = '".$myrowGetEMPStockCategoryName['categorydescription']."'";
      $resultGetZencartStockCategoryID = DB_query($sqlGetZencartStockCategoryID,$db);
      $myrowGetZencartStockCategoryID = DB_fetch_array($resultGetZencartStockCategoryID);
      $zencartStockCategoryID = $myrowGetZencartStockCategoryID['categories_id'];
      
      $sqlAddNewStock = "INSERT INTO ".$zencartDB.".products (products_type, products_quantity, products_price, 
                                                                  products_date_added, products_status, products_tax_class_id, manufacturers_id, 
                                                                  products_price_sorter, master_categories_id)
                                                          VALUES ('1', '".$stockQtty."', '".$stockPrice."', 
                                                                  NOW(), '1', '".$empStockTaxCatID."', '1', 
                                                                  '".$stockPrice."', '".$zencartStockCategoryID."')";
      $ErrMsgAddNewStock = _('The stock') . ' ' . $empStockID . ' ' . _('could not be added to Zencart database because');
      $DbgMsgAddNewStock = _('The SQL that failed was');
      $resultAddNewStock = DB_query($sqlAddNewStock, $db, $ErrMsgAddNewStock, $DbgMsgAddNewStock);
      
      //=====Get Latest Product ID of Zencart (Start)=====
      DB_query("LOCK TABLES ".$zencartDB.".products WRITE",$db);

      $sqlGetLatestProductID = "SELECT MAX(products_id) FROM ".$zencartDB.".products";

      $ErrMsgGetLatestProductID = _('CRITICAL ERROR') . '! ' . _('NOTE DOWN THIS ERROR AND SEEK ASSISTANCE') . ': <BR>' . _('The latest category ID could not be retrieved from the database because');
      $DbgMsgGetLatestProductID =  _('The following SQL to retrieve the latest category ID was used');
      $resultGetLatestProductID = DB_query($sqlGetLatestProductID,$db,$ErrMsgGetLatestProductID,$DbgMsgGetLatestProductID);

      $myrowGetLatestProductID = DB_fetch_row($resultGetLatestProductID);
      
      $LatestProductID=$myrowGetLatestProductID[0];
      
      DB_query("UNLOCK TABLES",$db);
      //=====Get Latest Product ID of Zencart (End)=====
            
      $sqlAddNewProductDescription = "INSERT INTO ".$zencartDB.".products_description (products_id, language_id, products_name, products_description)
                                        VALUES ('".$LatestProductID."', '1', '".$empStockID."', '".$empStockDescription ."')";
      $ErrMsgAddNewProductDescription = _('The stock') . ' ' . $empStockID . ' ' . _('could not be added to Zencart database because');
      $DbgMsgAddNewProductDescription = _('The SQL that failed was');
      $resultAddNewProductDescription = DB_query($sqlAddNewProductDescription, $db, $ErrMsgAddNewProductDescription, $DbgMsgAddNewProductDescription);
      prnMsg(_('Product') . ' ' . $empStockID . ' ' . _(' has been successfully added to Zencart database'), 'success');
        
      $sqlMatchProductToCategory = "INSERT INTO ".$zencartDB.".products_to_categories (products_id, categories_id)
                                    VALUES ('".$LatestProductID."', '".$zencartStockCategoryID."')";
      $ErrMsgMatchProductToCategory = _('The stock') . ' ' . $empStockID . ' ' . _('could not be matched to Zencart product category because');
      $DbgMsgMatchProductToCategory = _('The SQL that failed was');
      $resultMatchProductToCategory = DB_query($sqlMatchProductToCategory, $db, $ErrMsgMatchProductToCategory, $DbgMsgMatchProductToCategory);
      prnMsg(_('Product') . ' ' . $empStockID . ' ' . _(' has been successfully matched to Zencart product category' ), 'success');
                                    
    }else{
    
      $sqlUpdateProductDescription = "UPDATE ".$zencartDB.".products_description 
                                      SET products_description = '".$empStockDescription."'
                                      WHERE products_name = '".$empStockID."'";
      $ErrMsgUpdateProductDescription = _('The stock') . ' ' . $empStockID . ' ' . _('could not be updated to Zencart database because');
      $DbgMsgUpdateProductDescription = _('The SQL that failed was');
      $resultUpdateProductDescription = DB_query($sqlUpdateProductDescription, $db, $ErrMsgUpdateProductDescription, $DbgMsgUpdateProductDescription);
      
      $sqlGetZencartProductID = "SELECT products_id FROM ".$zencartDB.".products_description WHERE products_name = '".$empStockID."'";
      $resultGetZencartProductID = DB_query($sqlGetZencartProductID,$db);
      $myrowGetZencartProductID = DB_fetch_array($resultGetZencartProductID);
      $zencartProductID = $myrowGetZencartProductID['products_id'];
      
      $sqlUpdateStock = "UPDATE ".$zencartDB.".products 
                        SET products_quantity = '".$stockQtty."',
                            products_price = '".$stockPrice."',
                            products_tax_class_id = '".$empStockTaxCatID."',
                            products_last_modified = NOW(),
                            products_price_sorter = '".$stockPrice."'
                        WHERE products_id = '".$zencartProductID."'" ;                                    
      $ErrMsgUpdateStock = _('The stock') . ' ' . $empStockID . ' ' . _('could not be updated to Zencart database because');
      $DbgMsgUpdateStock = _('The SQL that failed was');
      $resultUpdateStock = DB_query($sqlUpdateStock, $db, $ErrMsgUpdateStock, $DbgMsgUpdateStock);
    
      prnMsg(_('Product') . ' ' . $empStockID . ' ' . _(' has been successfully updated to Zencart database'), 'success');
    }
  }
	//=====Add/Update Stock Details (End)=====
	
}
      
echo '<tr><td>';
		echo '<div class="centre"><input type="submit" name="sync_stocks" value="Synchronise Stocks to Zencart"></div>';
echo '</td></tr>';	
echo '</table>';

	
	
include('includes/footer.inc'); ?>