<?php

$PageSecurity=1;

include('includes/session.inc');

$title=_('Synchronise Customers from Zencart');

include('includes/header.inc');
include('includes/SQL_CommonFunctions.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_customers'])) 
{ 
  //=====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/Update Customers (Start)=====
	$sqlGetZencartCustomers = "SELECT customers_id, customers_firstname, customers_lastname, customers_email_address, customers_telephone, customers_fax
                                  FROM ".$zencartDB.".customers";
	$resultGetZencartCustomers = DB_query($sqlGetZencartCustomers,$db);
  
  while($myrowGetZencartCustomers = DB_fetch_array($resultGetZencartCustomers))
  {
    $AddCustomerToEMP = 1;
    $zencartCustomerName = $myrowGetZencartCustomers['customers_firstname'].' '.$myrowGetZencartCustomers['customers_lastname'];
    $zencartCustomerID = $myrowGetZencartCustomers['customers_id'];
    $zencartCustomerEmail = $myrowGetZencartCustomers['customers_email_address'];
    $zencartCustomerTel = $myrowGetZencartCustomers['customers_telephone'];
    $zencartCustomerFax = $myrowGetZencartCustomers['customers_fax'];
    
    $sqlGetEMPDebtorNo = "SELECT debtorno FROM custbranch WHERE custbranchcode='".$zencartCustomerID."'";
    $resultGetEMPDebtorNo = DB_query($sqlGetEMPDebtorNo,$db);
    
    if (DB_num_rows($resultGetEMPDebtorNo)!=0)
    {
        $AddCustomerToEMP = 0;
    }
    
    $sqlGetZencartCustomerAddr = "SELECT entry_company, entry_street_address, entry_suburb, entry_postcode, entry_city
                                        entry_state, entry_country_id
                                  FROM ".$zencartDB.".address_book
                                  WHERE customers_id = '".$zencartCustomerID."'";
    $resultGetZencartCustomerAddr = DB_query($sqlGetZencartCustomerAddr,$db);
    $myrowGetZencartCustomerAddr = DB_fetch_array($resultGetZencartCustomerAddr);
    
    $zencartCustomerCompany = $myrowGetZencartCustomerAddr['entry_company'];
    $zencartCustomerStreet = $myrowGetZencartCustomerAddr['entry_street_address'];
    $zencartCustomerSuburb = $myrowGetZencartCustomerAddr['entry_suburb'];
    $zencartCustomerPostcode = $myrowGetZencartCustomerAddr['entry_postcode'];
    $zencartCustomerCity = $myrowGetZencartCustomerAddr['entry_city'];
    $zencartCustomerState = $myrowGetZencartCustomerAddr['entry_state'];
    $zencartCustomerCountry = $myrowGetZencartCustomerAddr['entry_country_id'];
      
      
    if($AddCustomerToEMP == 1)
    {
      //=====Get Latest Customer ID of EMP (Start)=====
      if ($_SESSION['AutoDebtorNo']== 1) {
					$LatestDebtorNo = GetNextTransNo(500, $db);
			}
      //=====Get Latest Customer ID of EMP (End)=====
      
      $sqlAddNewCustomer = "INSERT INTO debtorsmaster (debtorno,
                                                      name,
                                                      address1,
                                                      address2,
                                                      address3,
                                                      address4,
                                                      address5,
                                                      address6,
                                                      currcode,
                                                      clientsince,
                                                      holdreason,
                                                      paymentterms,
                                                      discount,
                                                      discountcode,
                                                      pymtdiscount,
                                                      creditlimit,
                                                      salestype,
                                                      invaddrbranch,
                                                      taxref,
                                                      customerpoline,
                                                      typeid)
                                                VALUES ('" . $LatestDebtorNo ."',
                                                  '" . $zencartCustomerName ."',
                                                  '" . $zencartCustomerCompany ."',
                                                  '" . $zencartCustomerStreet ."',
                                                  '" . $zencartCustomerSuburb . "',
                                                  '" . $zencartCustomerPostcode . " " . $zencartCustomerCity . "',
                                                  '" . $zencartCustomerState . "',
                                                  '[Via E-Commerce]',
                                                  'MYR',
                                                  NOW(),
                                                  '1',
                                                  'NA',
                                                  '0',
                                                  '0',
                                                  '0',
                                                  '999999',
                                                  'NE',
                                                  '0',
                                                  '',
                                                  '0',
                                                  '1'
                                                  )";
      $ErrMsgAddNewCustomer = _('The customer') . ' ' . $zencartCustomerName . ' ' . _('could not be added to EMP database because');
      $DbgMsgAddNewCustomer = _('The SQL that failed was');
      $resultAddNewCustomer = DB_query($sqlAddNewCustomer, $db, $ErrMsgAddNewCustomer, $DbgMsgAddNewCustomer);
      
      
      $sqlAddCustomerBranch = "INSERT INTO custbranch (branchcode,
                                                      debtorno,
                                                      brname,
                                                      braddress1,
                                                      braddress2,
                                                      braddress3,
                                                      braddress4,
                                                      braddress5,
                                                      braddress6,
                                                      lat,
                                                      lng,
                                                      specialinstructions,
                                                      estdeliverydays,
                                                      fwddate,
                                                      salesman,
                                                      phoneno,
                                                      faxno,
                                                      contactname,
                                                      area,
                                                      email,
                                                      taxgroupid,
                                                      defaultlocation,
                                                      brpostaddr1,
                                                      brpostaddr2,
                                                      brpostaddr3,
                                                      brpostaddr4,
                                                      disabletrans,
                                                      defaultshipvia,
                                                      custbranchcode,
                                                      deliverblind)
                                                  VALUES ('" . $LatestDebtorNo . "',
                                                    '" . $LatestDebtorNo . "',
                                                    '" . $zencartCustomerName ."',
                                                    '" . $zencartCustomerCompany ."',
                                                    '" . $zencartCustomerStreet ."',
                                                    '" . $zencartCustomerSuburb . "',
                                                    '" . $zencartCustomerPostcode . " " . $zencartCustomerCity . "',
                                                    '" . $zencartCustomerState . "',
                                                    '[Via E-Commerce]',
                                                    '0.000000',
                                                    '0.000000',
                                                    '',
                                                    '0',
                                                    '0',
                                                    'ECOMM',
                                                    '" . $zencartCustomerTel . "',
                                                    '" . $zencartCustomerFax . "',
                                                    '" . $zencartCustomerName . "',
                                                    'ECOMM',
                                                    '" . $zencartCustomerEmail . "',
                                                    '1',
                                                    '" . $empLocation . "',
                                                    '',
                                                    '',
                                                    '',
                                                    '',
                                                    '0',
                                                    '1',
                                                    '" .$zencartCustomerID. "',
                                                    '1')";
      $ErrMsgAddCustomerBranch = _('The customer branch') . ' ' . $zencartCustomerName . ' ' . _('could not be added to EMP database because');
      $DbgMsgAddCustomerBranch = _('The SQL that failed was');
      $resultAddCustomerBranch = DB_query($sqlAddCustomerBranch, $db, $ErrMsgAddCustomerBranch, $DbgMsgAddCustomerBranch);

      prnMsg(_('Customer') . ' ' . $zencartCustomerName . ' ' . _(' has been successfully added to EMP database'), 'success');
        
    }else{
          
      $sqlUpdateCustomerBranch = "UPDATE custbranch SET brname = '" . $zencartCustomerName ."',
                                                      braddress1 = '" . $zencartCustomerCompany ."',
                                                      braddress2 = '" . $zencartCustomerStreet ."',
                                                      braddress3 =  '" . $zencartCustomerSuburb . "',
                                                      braddress4 = '" . $zencartCustomerPostcode . " " . $zencartCustomerCity . "',
                                                      braddress5 = '" . $zencartCustomerState . "'
                                                WHERE custbranchcode = '" . $zencartCustomerID . "'";                
      $ErrMsgUpdateCustomerBranch = _('The customer branch') . ' ' . $zencartCustomerName . ' ' . _('could not be updated to EMP database because');
      $DbgMsgUpdateCustomerBranch = _('The SQL that failed was');
      $resultUpdateCustomerBranch = DB_query($sqlUpdateCustomerBranch, $db, $ErrMsgUpdateCustomerBranch, $DbgMsgUpdateCustomerBranch);

      $sqlGetEMPDebtorNo = "SELECT debtorno FROM custbranch WHERE custbranchcode = '" . $zencartCustomerID . "'";  
      $resultGetEMPDebtorNo = DB_query($sqlGetEMPDebtorNo,$db);
      $myrowGetEMPDebtorNo = DB_fetch_array($resultGetEMPDebtorNo);
      $empDebtorNo = $myrowGetEMPDebtorNo['debtorno'];

      $sqlUpdateCustomer = "UPDATE debtorsmaster SET name = '" . $zencartCustomerName ."',
                                                      address1 = '" . $zencartCustomerCompany ."',
                                                      address2 = '" . $zencartCustomerStreet ."',
                                                      address3 = '" . $zencartCustomerSuburb . "',
                                                      address4 = '" . $zencartCustomerPostcode . " " . $zencartCustomerCity . "',
                                                      address5 = '" . $zencartCustomerState . "'
                                          WHERE debtorno = '" . $empDebtorNo . "'";    
      $ErrMsgUpdateCustomer = _('The customer') . ' ' . $zencartCustomerName . ' ' . _('could not be updated to EMP database because');
      $DbgMsgUpdateCustomer = _('The SQL that failed was');
      $resultUpdateCustomer = DB_query($sqlUpdateCustomer, $db, $ErrMsgUpdateCustomer, $DbgMsgUpdateCustomer);
      
      prnMsg(_('Customer') . ' ' . $zencartCustomerName . ' ' . _(' has been successfully updated to EMP database'), 'success');
    }
  }
  //=====Add/Update Customers (End)=====
  
  /*
	//=====Add/Update Stock Details (Start)=====
	$sqlGetEMPStockDetails = "SELECT stockid, categoryid, description 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'];
    
    $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, manufacturers_id, 
                                                                  products_price_sorter, master_categories_id)
                                                          VALUES ('1', '".$stockQtty."', '".$stockPrice."', 
                                                                  NOW(), '1', '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_last_modified = NOW(),
                            products_price_sorter = '".$stockPrice."'
                        WHERE products_id = '".$empStockID."'" ;                                    
      $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_customers" value="Synchronise Customers from Zencart"></div>';
echo '</td></tr>';	
echo '</table>';

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