<?php

$PageSecurity=1;

include('includes/DefineCartClass.php');

include('includes/session.inc');

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

include('includes/header.inc');
include('includes/GetPrice.inc');
include('includes/SQL_CommonFunctions.inc');
include('includes/GetSalesTransGLCodes.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_sales'])) 
{ 
  //=====Get Zencart Details (Start)=====
  $sqlGetZencartDetails = "SELECT id, zencart_db, emp_stock_location, bank_account, payment_method 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'];
	$BankAccount = $myrowGetZencartDetails['bank_account'];
	$PaymentMethod = $myrowGetZencartDetails['payment_method'];
	//=====Get Zencart Details (End)=====
	
	//=====Add Sales (Start)=====
	$sqlGetZencartSales = "SELECT orders_id, customers_id, 
                                delivery_name, delivery_company, delivery_street_address, delivery_suburb, delivery_city, 
                                delivery_postcode, delivery_state, delivery_country, 
                                payment_method, date_purchased, currency, order_total, order_tax                                
                                  FROM ".$zencartDB.".orders";
	$resultGetZencartSales = DB_query($sqlGetZencartSales,$db);
  
  while($myrowGetZencartSales = DB_fetch_array($resultGetZencartSales))
  {
    $AddSalesToEMP = 1;
    $zencartSalesID = $myrowGetZencartSales['orders_id'];
    $zencartSalesCustomerID = $myrowGetZencartSales['customers_id'];
    $zencartSalesDeliveryName = $myrowGetZencartSales['delivery_name'];
    $zencartSalesDeliveryCompany = $myrowGetZencartSales['delivery_company'];
    $zencartSalesDeliveryStreet = $myrowGetZencartSales['delivery_street_address'];
    $zencartSalesDeliverySuburb = $myrowGetZencartSales['delivery_suburb'];
    $zencartSalesDeliveryCity = $myrowGetZencartSales['delivery_city'];
    $zencartSalesDeliveryPostcode = $myrowGetZencartSales['delivery_postcode'];
    $zencartSalesDeliveryState = $myrowGetZencartSales['delivery_state'];
    $zencartSalesDeliveryCountry = $myrowGetZencartSales['delivery_country'];
    $zencartSalesDeliveryTel = $myrowGetZencartSales['delivery_telephone'];
    $zencartSalesDeliveryEmail = $myrowGetZencartSales['delivery_email_address'];
    $zencartSalesPaymentMethod = $myrowGetZencartSales['payment_method'];
    $zencartSalesDatePurchased = Date('Y-m-d', strtotime($myrowGetZencartSales['date_purchased']));
    $zencartSalesCurrency = $myrowGetZencartSales['currency'];
    
    $sqlGetZencartSubTotals = "SELECT value FROM ".$zencartDB.".orders_total WHERE orders_id='".$zencartSalesID."' AND class='ot_subtotal'";
    $resultGetZencartSubTotals = DB_query($sqlGetZencartSubTotals,$db);
    if (DB_num_rows($resultGetZencartSubTotals)!=0)
    {
        $myrowGetZencartSubTotals = DB_fetch_array($resultGetZencartSubTotals);
        $zencartSalesSubTotal = $myrowGetZencartSubTotals['value'];
    } else {
        $zencartSalesSubTotal = 0;
    }
    
    
    $sqlGetZencartShipping = "SELECT value FROM ".$zencartDB.".orders_total WHERE orders_id='".$zencartSalesID."' AND class='ot_shipping'";
    $resultGetZencartShipping = DB_query($sqlGetZencartShipping,$db);
    if (DB_num_rows($resultGetZencartShipping)!=0)
    {
        $myrowGetZencartShipping = DB_fetch_array($resultGetZencartShipping);
        $zencartSalesShipping = $myrowGetZencartShipping['value'];
    } else {
        $zencartSalesShipping = 0;
    }

    $sqlGetZencartTax = "SELECT value FROM ".$zencartDB.".orders_total WHERE orders_id='".$zencartSalesID."' AND class='ot_tax'";
    $resultGetZencartTax = DB_query($sqlGetZencartTax,$db);
    if (DB_num_rows($resultGetZencartTax)!=0)
    {
        $myrowGetZencartTax = DB_fetch_array($resultGetZencartTax);
        $zencartSalesTax = $myrowGetZencartTax['value'];
    } else {
        $zencartSalesTax = 0;
    }
    
    $sqlGetEMPExistingOrder = "SELECT orderno FROM salesorders WHERE comments LIKE 'Zencart Order ID: ".$zencartSalesID."%'";
    $resultGetEMPExistingOrder = DB_query($sqlGetEMPExistingOrder,$db);
    
    if (DB_num_rows($resultGetEMPExistingOrder)!=0)
    {
        $AddSalesToEMP = 0;
        prnMsg(_('Zencart order') . ' ' . $zencartSalesID . ' ' . _(' exists in EMP database'), 'error');
    }
    
    $sqlGetEMPDebtorNo = "SELECT debtorno FROM custbranch WHERE custbranchcode='".$zencartSalesCustomerID."'";
    $resultGetEMPDebtorNo = DB_query($sqlGetEMPDebtorNo,$db);
    
    if (DB_num_rows($resultGetEMPDebtorNo)==0)
    {
        $AddSalesToEMP = 0;
        prnMsg(_('Zencart customer') . ' ' . $zencartSalesCustomerID . ' ' . _(' does not exist in EMP database'), 'error');
    }

    if($AddSalesToEMP == 1)
    {     
      $LocationName = 'ECOMM';
			$Location = 'ECOMM';
			$SalesLocation = 'ECOMM';
			$DispatchTaxProvince = '1';
			
			$InputError = false; //always assume the best
			
			// Now check to ensure this account exists and set defaults */
			$sqlGetEMPCustomerDetails = "SELECT debtorsmaster.name,
                                  custbranch.branchcode,
                                  custbranch.debtorno,
                                  holdreasons.dissallowinvoices,
                                  debtorsmaster.salestype,
                                  salestypes.sales_type,
                                  debtorsmaster.currcode,
                                  debtorsmaster.customerpoline,
                                  paymentterms.terms,
                                  currencies.decimalplaces
                                FROM debtorsmaster INNER JOIN holdreasons
                                ON debtorsmaster.holdreason=holdreasons.reasoncode
                                INNER JOIN salestypes 
                                ON debtorsmaster.salestype=salestypes.typeabbrev
                                INNER JOIN paymentterms 
                                ON debtorsmaster.paymentterms=paymentterms.termsindicator 
                                INNER JOIN currencies 
                                ON debtorsmaster.currcode=currencies.currabrev
                                INNER JOIN custbranch 
                                ON debtorsmaster.debtorno=custbranch.debtorno
                                WHERE custbranch.custbranchcode = '" . $zencartSalesCustomerID . "'";
			
			$ErrMsgGetEMPCustomerDetails = _('The details of the Zencart customer selected') . ': ' .  $zencartSalesCustomerID . ' ' . _('cannot be retrieved because');
			$DbgMsgGetEMPCustomerDetails = _('The SQL used to retrieve the customer details and failed was') . ':';

			$resultGetEMPCustomerDetails = DB_query($sqlGetEMPCustomerDetails,$db,$ErrMsgGetEMPCustomerDetails,$DbgMsgGetEMPCustomerDetails);
			
			$myrowGetEMPCustomerDetails = DB_fetch_array($resultGetEMPCustomerDetails);
			
			if ($myrowGetEMPCustomerDetails['dissallowinvoices'] != 1){
			
				if ($myrowGetEMPCustomerDetails['dissallowinvoices']==2){
					prnMsg($myrowGetEMPCustomerDetails['name'] . ' ' . _('Although this account is defined as the Zencart sale account for the location.  The account is currently flagged as an account that needs to be watched. Please contact the credit control personnel to discuss'),'warn');
				}
	
				//$_SESSION['RequireCustomerSelection']=0;
				$DebtorNo = $myrowGetEMPCustomerDetails['debtorno'];
				$BranchCode = $myrowGetEMPCustomerDetails['branchcode'];
				$CustomerName = $myrowGetEMPCustomerDetails['name'];
				// the sales type is the price list to be used for this sale
				$DefaultSalesType = $myrowGetEMPCustomerDetails['salestype'];
				$SalesTypeName = $myrowGetEMPCustomerDetails['sales_type'];
				$DefaultCurrency = $myrowGetEMPCustomerDetails['currcode'];
				$DefaultPOLine = $myrowGetEMPCustomerDetails['customerpoline'];
				$PaymentTerms = $myrowGetEMPCustomerDetails['terms'];
				$CurrDecimalPlaces = $myrowGetEMPCustomerDetails['decimalplaces'];
        
        /* now get the branch defaults from the customer branches table CustBranch. */
	
				$sqlGetEMPCustomerBranchDetails = "SELECT custbranch.brname,
					       custbranch.braddress1,
					       custbranch.defaultshipvia,
					       custbranch.deliverblind,
					       custbranch.specialinstructions,
					       custbranch.estdeliverydays,
					       custbranch.salesman,
					       custbranch.taxgroupid,
					       custbranch.defaultshipvia
					FROM custbranch
					WHERE custbranch.branchcode='" . $BranchCode . "'
					AND custbranch.debtorno = '" . $DebtorNo . "'";
		    $ErrMsgGetEMPCustomerBranchDetails = _('The customer branch record of the customer selected') . ': ' . $BranchCode . ' ' . _('cannot be retrieved because');
				$DbgMsgGetEMPCustomerBranchDetails = _('SQL used to retrieve the branch details was') . ':';
				$resultGetEMPCustomerBranchDetails =DB_query($sqlGetEMPCustomerBranchDetails,$db,$ErrMsgGetEMPCustomerBranchDetails,$DbgMsgGetEMPCustomerBranchDetails);
	
				if (DB_num_rows($resultGetEMPCustomerBranchDetails)==0)
				{
					prnMsg(_('The branch details for branch code') . ': ' . $BranchCode . ' ' . _('against customer code') . ': ' . $DebtorNo . ' ' . _('could not be retrieved') . '. ' . _('Check the set up of the customer and branch'),'error');
					$InputError = true;
				}

				$myrowGetEMPCustomerBranchDetails = DB_fetch_array($resultGetEMPCustomerBranchDetails);
	
				$ShipVia = $myrowGetEMPCustomerBranchDetails['defaultshipvia'];
				$DeliverBlind = $myrowGetEMPCustomerBranchDetails['deliverblind'];
				$SpecialInstructions = $myrowGetEMPCustomerBranchDetails['specialinstructions'];
				$DeliveryDays = $myrowGetEMPCustomerBranchDetails['estdeliverydays'];
				$TaxGroup = $myrowGetEMPCustomerBranchDetails['taxgroupid'];
	
				if ($SpecialInstructions) {
					prnMsg($SpecialInstructions,'warn');
				}
	
	
			} else {
				prnMsg($myrowGetEMPCustomerBranchDetails['brname'] . ' ' . _('Although the account is defined as the cash sale account for the location  the account is currently on hold. Please contact the credit control personnel to discuss'),'warn');
			}
	
      //Get The exchange rate used for GPPercent calculations on adding or amending items
      if ($DefaultCurrency != $_SESSION['CompanyRecord']['currencydefault']){
        $ExRateResult = DB_query("SELECT rate FROM currencies WHERE currabrev='" . $DefaultCurrency . "'",$db);
        if (DB_num_rows($ExRateResult)>0){
          $ExRateRow = DB_fetch_row($ExRateResult);
          $ExRate = $ExRateRow[0];
        } else {
          $ExRate =1;
        }
      } else {
        $ExRate = 1;
      }		
			
			/*
      //but check for the worst
      if ($_SESSION['Items'.$identifier]->LineCounter == 0){
        prnMsg(_('There are no lines on this sale. Please enter lines to invoice first'),'error');
        $InputError = true;
      }
      if (abs(filter_number_format($_POST['AmountPaid']) -($_SESSION['Items'.$identifier]->total+filter_number_format($_POST['TaxTotal'])))>=0.01) {
        prnMsg(_('The amount entered as payment does not equal the amount of the invoice. Please ensure the customer has paid the correct amount and re-enter'),'error');
        $InputError = true;
      }
      */

      //20130404 YS Chin: Check if Sales Branch is selected
      if ($SalesLocation == "")
      {
        prnMsg(_('There is no Sales Branch defined'),'error');
        $InputError = true;
      }
      
      //=====Get the products for the sales (start)=====
      
      $sqlGetProductsForSales = "SELECT products_id, products_name, products_price, final_price, products_tax, products_quantity
                                  FROM ".$zencartDB.".orders_products WHERE orders_id = '".$zencartSalesID."'";
      $ErrMsgGetProductsForSales = _('The products of the selected Zencart order') . ': ' . $zencartSalesID . ' ' . _('cannot be retrieved because');
			$DbgMsgGetProductsForSales = _('SQL used to retrieve the products details was') . ':';
			$resultGetProductsForSales =DB_query($sqlGetProductsForSales,$db,$ErrMsgGetProductsForSales,$DbgMsgGetProductsForSales);
	
      if (DB_num_rows($resultGetProductsForSales)==0)
      {
        prnMsg(_('The product details for branch code') . ': ' . $BranchCode . ' ' . _('against customer code') . ': ' . $DebtorNo . ' ' . _('could not be retrieved') . '. ' . _('Check the set up of the customer and branch'),'error');
        $InputError = true;
        
      } else {
        
        if ($_SESSION['ProhibitNegativeStock']==1)
        { 
          // checks for negative stock after processing invoice
          //sadly this check does not combine quantities occuring twice on and order and each line is considered individually :-(
          $NegativesFound = false;
          
          while($myrowGetProductsForSales=DB_fetch_array($resultGetProductsForSales)) 
          {
            $SQL = "SELECT stockmaster.description,
                      locstock.quantity,
                      stockmaster.mbflag
                FROM locstock
                INNER JOIN stockmaster
                ON stockmaster.stockid=locstock.stockid
                WHERE stockmaster.stockid='" . $myrowGetProductsForSales['products_name'] . "'
                AND locstock.loccode='" . $Location . "'";

            $ErrMsg = _('Could not retrieve the quantity left at the location once this order is invoiced (for the purposes of checking that stock will not go negative because)');
            $Result = DB_query($SQL,$db,$ErrMsg);
            $CheckNegRow = DB_fetch_array($Result);
            if ($CheckNegRow['mbflag']=='B' OR $CheckNegRow['mbflag']=='M'){
              if ($CheckNegRow['quantity'] < $myrowGetProductsForSales['products_quantity']){
                prnMsg( _('Invoicing the selected order would result in negative stock. The system parameters are set to prohibit negative stocks from occurring. This invoice cannot be created until the stock on hand is corrected.'),'error',$myrowGetProductsForSales['products_name'] . ' ' . $CheckNegRow['description'] . ' - ' . _('Negative Stock Prohibited'));
                $NegativesFound = true;
              }
            } else if ($CheckNegRow['mbflag']=='A') {

              //Now look for assembly components that would go negative 
              $SQL = "SELECT bom.component,
                       stockmaster.description,
                       locstock.quantity-(" . $myrowGetProductsForSales['products_quantity']  . "*bom.quantity) AS qtyleft
                  FROM bom
                  INNER JOIN locstock
                  ON bom.component=locstock.stockid
                  INNER JOIN stockmaster
                  ON stockmaster.stockid=bom.component
                  WHERE bom.parent='" . $myrowGetProductsForSales['products_name'] . "'
                  AND locstock.loccode='" . $Location . "'
                  AND effectiveafter <'" . Date('Y-m-d') . "'
                  AND effectiveto >='" . Date('Y-m-d') . "'";

              $ErrMsg = _('Could not retrieve the component quantity left at the location once the assembly item on this order is invoiced (for the purposes of checking that stock will not go negative because)');
              $Result = DB_query($SQL,$db,$ErrMsg);
              while ($NegRow = DB_fetch_array($Result)){
                if ($NegRow['qtyleft']<0){
                  prnMsg(_('Invoicing the selected order would result in negative stock for a component of an assembly item on the order. The system parameters are set to prohibit negative stocks from occurring. This invoice cannot be created until the stock on hand is corrected.'),'error',$NegRow['component'] . ' ' . $NegRow['description'] . ' - ' . _('Negative Stock Prohibited'));
                  $NegativesFound = true;
                } // end if negative would result
              } //loop around the components of an assembly item
            } //end if its an assembly item - check component stock

          } //end of loop around items on the order for negative check
  
        if ($NegativesFound)
        {
          prnMsg(_('The parameter to prohibit negative stock is set and invoicing this sale would result in negative stock. No futher processing can be performed. Alter the sale first changing quantities or deleting lines which do not have sufficient stock.'),'error');
          $InputError = true;
        }
      
      }//end of testing for negative stocks
      
      
      if ($InputError == false) 
      { 
        //all good so let's get on with the processing
        //Now Get the area where the sale is to from the branches table 

        $SQL = "SELECT 	area,
                defaultshipvia
            FROM custbranch
            WHERE custbranch.debtorno ='". $DebtorNo . "'
            AND custbranch.branchcode = '" . $BranchCode . "'";

        $ErrMsg = _('We were unable to load the area where the sale is to from the custbranch table');
        $Result = DB_query($SQL,$db, $ErrMsg);
        $myrow = DB_fetch_row($Result);
        $Area = $myrow[0];
        $DefaultShipVia = $myrow[1];
        DB_free_result($Result);

        //company record read in on login with info on GL Links and debtors GL account

        if ($_SESSION['CompanyRecord']==0){
          //The company data and preferences could not be retrieved for some reason 
          prnMsg( _('The company information and preferences could not be retrieved. See your system administrator'), 'error');
          include('includes/footer.inc');
          exit;
        }

        // *************************************************************************
        //   S T A R T   O F   I N V O I C E   S Q L   P R O C E S S I N G
        // *************************************************************************
        
          $roundAdjustmentAmount = ($zencartSalesSubTotal + $zencartSalesShipping + $zencartSalesTax) - (round(($zencartSalesSubTotal + $zencartSalesShipping + $zencartSalesTax)/0.05) * 0.05);
          //$roundAdjustmentAmount=0;
          $result = DB_Txn_Begin($db);
        /*First add the order to the database - it only exists in the session currently! */
          $OrderNo = GetNextTransNo(30, $db);
          //20130404 YS Chin: Add Salesman and Sales Branch
          $HeaderSQL = "INSERT INTO salesorders (	orderno,
                              debtorno,
                              branchcode,
                              customerref,
                              comments,
                              orddate,
                              ordertype,
                              shipvia,
                              deliverto,
                              deladd1,
                              deladd2,
                              deladd3,
                              deladd4,
                              deladd5,
                              deladd6,
                              contactphone,
                              contactemail,
                              fromstkloc,
                              deliverydate,
                              confirmeddate,
                              deliverblind,
                              ra_salesbranch,
                              ra_salesman)
                            VALUES (
                              '" . $OrderNo . "',
                              '" . $DebtorNo . "',
                              '" . $BranchCode . "',
                              '',
                              'Zencart Order ID: ".$zencartSalesID." ',
                              '" . $zencartSalesDatePurchased . "',
                              '" . $DefaultSalesType . "',
                              '" . $ShipVia . "',
                              '" . $zencartSalesDeliveryName . "',
                              '" . $zencartSalesDeliveryCompany . "',
                              '" . $zencartSalesDeliveryStreet . ", " . $zencartSalesDeliverySuburb . "',
                              '" . $zencartSalesDeliveryPostcode . ", " . $zencartSalesDeliveryCity . "',
                              '" . $zencartSalesDeliveryState . "',
                              '" . $zencartSalesDeliveryCountry . "',
                              '',
                              '" . $zencartSalesDeliveryTel . "',
                              '" . $zencartSalesDeliveryEmail . "',
                              '" . $Location ."',
                              '" . $zencartSalesDatePurchased . "',
                              '" . $zencartSalesDatePurchased . "',
                              0,
                              '" . $SalesLocation ."',
                              'ECOMM'
                              )";

          $ErrMsg = _('The order cannot be added because');
          $InsertQryResult = DB_query($HeaderSQL,$db,$ErrMsg);

          $StartOf_LineItemsSQL = "INSERT INTO salesorderdetails (orderlineno,
                                      orderno,
                                      stkcode,
                                      unitprice,
                                      quantity,
                                      discountpercent,
                                      narrative,
                                      itemdue,
                                      actualdispatchdate,
                                      qtyinvoiced,
                                      completed)
                                    VALUES (";

          $DbgMsg = _('Trouble inserting a line of a sales order. The SQL that failed was');
          
          $sqlGetProductsForSales = "SELECT products_id, products_name, products_price, final_price, products_tax, products_quantity
                                      FROM ".$zencartDB.".orders_products WHERE orders_id = '".$zencartSalesID."'";
          $ErrMsgGetProductsForSales = _('The products of the selected Zencart order') . ': ' . $zencartSalesID . ' ' . _('cannot be retrieved because');
          $DbgMsgGetProductsForSales = _('SQL used to retrieve the products details was') . ':';
          $resultGetProductsForSales =DB_query($sqlGetProductsForSales,$db,$ErrMsgGetProductsForSales,$DbgMsgGetProductsForSales);
	
          if (DB_num_rows($resultGetProductsForSales)==0)
          {
            prnMsg(_('The product details for branch code') . ': ' . $BranchCode . ' ' . _('against customer code') . ': ' . $DebtorNo . ' ' . _('could not be retrieved') . '. ' . _('Check the set up of the customer and branch'),'error');
            continue;
            
          } else {
          
            $LineNumber=0;
            
            while($myrowGetProductsForSales=DB_fetch_array($resultGetProductsForSales)) 
            {
                $StockID = $myrowGetProductsForSales['products_name'];
                $Price = $myrowGetProductsForSales['final_price'];
                $Quantity = $myrowGetProductsForSales['products_quantity'];
                $Tax = $myrowGetProductsForSales['products_tax'];
                
                $LineItemsSQL = $StartOf_LineItemsSQL .
                    "'".$LineNumber . "',
                    '" . $OrderNo . "',
                    '" . $StockID . "',
                    '" . $Price . "',
                    '" . $Quantity . "',
                    '0.00',
                    '',
                    '" . $zencartSalesDatePurchased . "',
                    '" . $zencartSalesDatePurchased . "',
                    '" . $Quantity . "',
                    1)";

                $ErrMsg = _('Unable to add the sales order line');
                $Ins_LineItemResult = DB_query($LineItemsSQL,$db,$ErrMsg,$DbgMsg,true);

                
                
                $LineNumber++;
                
              } // end inserted line items into sales order details 

              prnMsg(_('Order Number') . ' ' . $OrderNo . ' ' . _('has been entered'),'success');

            }// End of insertion of new sales order 

            //Now Get the next invoice number - GetNextTransNo() function in SQL_CommonFunctions
            // GetPeriod() in includes/DateFunctions.inc 

            $InvoiceNo = GetNextTransNo(10, $db);
            $PeriodNo = GetPeriod(Date($_SESSION['DefaultDateFormat'],strtotime($zencartSalesDatePurchased)), $db);

            $DefaultDispatchDate = $zencartSalesDatePurchased;

            /*Update order header for invoice charged on */
            $SQL = "UPDATE salesorders SET comments = CONCAT(comments,'" . ' ' . _('Invoice') . ': ' . "','" . $InvoiceNo . "') WHERE orderno= '" . $OrderNo."'";

            $ErrMsg = _('CRITICAL ERROR') . ' ' . _('NOTE DOWN THIS ERROR AND SEEK ASSISTANCE') . ': ' . _('The sales order header could not be updated with the invoice number');
            $DbgMsg = _('The following SQL to update the sales order was used');
            $Result = DB_query($SQL,$db,$ErrMsg,$DbgMsg,true);
            
            /*Now insert the DebtorTrans */

            $TotalAlloc = $zencartSalesSubTotal + $zencartSalesShipping + $zencartSalesTax;
            
            $SQL = "INSERT INTO debtortrans (transno,
                            type,
                            debtorno,
                            branchcode,
                            trandate,
                            inputdate,
                            prd,
                            reference,
                            tpe,
                            order_,
                            ovamount,
                            ovgst,
                            ovfreight,
                            ovdiscount,
                            rate,
                            invtext,
                            shipvia,
                            alloc,
                            settled )
              VALUES (
                '". $InvoiceNo . "',
                10,
                '" . $DebtorNo . "',
                '" . $BranchCode . "',
                '" . $DefaultDispatchDate . "',
                '" . date('Y-m-d H-i-s', strtotime($DefaultDispatchDate)) . "',
                '" . $PeriodNo . "',
                '',
                '" . $DefaultSalesType . "',
                '" . $OrderNo . "',
                '" . $zencartSalesSubTotal . "',
                '" . $zencartSalesTax . "',
                '" . $zencartSalesShipping . "',
                '" . -$roundAdjustmentAmount . "',
                '" . $ExRate . "',
                '',
                '" . $ShipVia . "',
                '" . $TotalAlloc . "',
                '1')";

          $ErrMsg =_('CRITICAL ERROR') . '! ' . _('NOTE DOWN THIS ERROR AND SEEK ASSISTANCE') . ': ' . _('The debtor transaction record could not be inserted because');
          $DbgMsg = _('The following SQL to insert the debtor transaction record was used');
          $Result = DB_query($SQL,$db,$ErrMsg,$DbgMsg,true);

          $DebtorTransID = DB_Last_Insert_ID($db,'debtortrans','id');
          
          $TaxAuthID = 1; //Hardcoded for Malaysian GST

          $SQL = "INSERT INTO debtortranstaxes (debtortransid,
                              taxauthid,
                              taxamount)
                        VALUES ('" . $DebtorTransID . "',
                          '" . $TaxAuthID . "',
                          '" . $zencartSalesTax/$ExRate . "')";

          $ErrMsg =_('CRITICAL ERROR') . '! ' . _('NOTE DOWN THIS ERROR AND SEEK ASSISTANCE') . ': ' . _('The debtor transaction taxes records could not be inserted because');
          $DbgMsg = _('The following SQL to insert the debtor transaction taxes record was used');
          $Result = DB_query($SQL,$db,$ErrMsg,$DbgMsg,true);
          
          
          // Edward 20150407: Update to accomodate for posting to rounding adjustment
          //=========================================================================
          if(locale_number_format($roundAdjustmentAmount, $_SESSION['CompanyRecord']['decimalplaces']) != 0) {
            
            $SQL = "INSERT INTO emp_debtortrans (id,
                    transno,
                    rounding)
                VALUES ('" . $DebtorTransID . "',
                  '" . $InvoiceNo . "',
                  '" . locale_number_format($roundAdjustmentAmount, $_SESSION['CompanyRecord']['decimalplaces']) . "')";
                  
            $ErrMsg =_('CRITICAL ERROR') . '! ' . _('NOTE DOWN THIS ERROR AND SEEK ASSISTANCE') . ': ' . _('The debtor transaction rounding adjustment records could not be inserted because');
            $DbgMsg = _('The following SQL to insert the debtor transaction adjustement rounding record was used');
            $Result = DB_query($SQL,$db,$ErrMsg,$DbgMsg,true);
          }
          //=========================================================================
	
	
          $sqlGetItemsFromSODetails = "SELECT stkcode, qtyinvoiced, unitprice,
                                              discountpercent, narrative
                                        FROM salesorderdetails WHERE orderno='".$OrderNo."'";
          $ErrMsgGetItemsFromSODetails = _('The sales order details of the selected EMP order') . ': ' . $OrderNo . ' ' . _('cannot be retrieved because');
          $DbgMsgGetItemsFromSODetails = _('SQL used to retrieve the sales order details was') . ':';
          $resultGetItemsFromSODetails =DB_query($sqlGetItemsFromSODetails,$db,$ErrMsgGetItemsFromSODetails,$DbgMsgGetItemsFromSODetails);
	
          if (DB_num_rows($resultGetItemsFromSODetails)==0)
          {
            prnMsg(_('The sales order details for order') . ': ' . $OrderNo .  ' ' . _('could not be retrieved') . '. ' . _('Check the insert into EMP salesorderdetails'),'error');
            break;
            
          } else {
          
            while($myrowGetItemsFromSODetails=DB_fetch_array($resultGetItemsFromSODetails)) 
            {
                $sqlGetStandardCost = "SELECT (stockmaster.materialcost+stockmaster.labourcost+stockmaster.overheadcost) AS standardcost,
                                                 taxcatid
                                              FROM stockmaster
                                              WHERE stockmaster.stockid = '" . $myrowGetItemsFromSODetails['stkcode'] . "'";
                $ErrMsgGetStandardCost = _('WARNING') . ': ' . _('Could not retrieve current location stock');
                $resultGetStandardCost = DB_query($sqlGetStandardCost, $db, $ErrMsgGetStandardCost);    
                $myrowGetStandardCost = DB_fetch_array($resultGetStandardCost);
                
                $StdCost=$myrowGetStandardCost['standardcost'];
                $TaxCatID = $myrowGetStandardCost['taxcatid'];
                
                $StkCode = $myrowGetItemsFromSODetails['stkcode'];
                $QtyInvoiced = $myrowGetItemsFromSODetails['qtyinvoiced'];
                $UnitPrice = $myrowGetItemsFromSODetails['unitprice'];
                $DiscountPercent=$myrowGetItemsFromSODetails['discountpercent'];
                $Narrative=$myrowGetItemsFromSODetails['narrative'];
                 
                 /* Update location stock records if not a dummy stock item
                 need the MBFlag later too so save it to $MBFlag */
                $Result = DB_query("SELECT mbflag FROM stockmaster WHERE stockid = '" . $StkCode . "'",$db);
                $myrow = DB_fetch_row($Result);
                $MBFlag = $myrow[0];
                if ($MBFlag=='B' OR $MBFlag=='M') {
                  $Assembly = False;
                      
                  /* Need to get the current location quantity
                  will need it later for the stock movement */
                  $SQL="SELECT locstock.quantity
                          FROM locstock
                          WHERE locstock.stockid='" . $StkCode . "'
                          AND loccode= '" . $Location . "'";
                  $ErrMsg = _('WARNING') . ': ' . _('Could not retrieve current location stock');
                  $Result = DB_query($SQL, $db, $ErrMsg);

                  if (DB_num_rows($Result)==1){
                    $LocQtyRow = DB_fetch_row($Result);
                    $QtyOnHandPrior = $LocQtyRow[0];
                  } else {
                    /* There must be some error this should never happen */
                    $QtyOnHandPrior = 0;
                  }

                  $SQL = "UPDATE locstock
                        SET quantity = locstock.quantity - " . $QtyInvoiced . "
                        WHERE locstock.stockid = '" . $StkCode . "'
                        AND loccode = '" . $Location . "'";

                  $ErrMsg = _('CRITICAL ERROR') . '! ' . _('NOTE DOWN THIS ERROR AND SEEK ASSISTANCE') . ': ' . _('Location stock record could not be updated because');
                  $DbgMsg = _('The following SQL to update the location stock record was used');
                  $Result = DB_query($SQL,$db,$ErrMsg,$DbgMsg,true);

                } else if ($MBFlag=='A') { /* its an assembly */
                  /*Need to get the BOM for this part and make
                  stock moves for the components then update the Location stock balances */
                  $Assembly=True;
                  $StandardCost =0; /*To start with - accumulate the cost of the comoponents for use in journals later on */
                  $SQL = "SELECT bom.component,
                      bom.quantity,
                      stockmaster.materialcost+stockmaster.labourcost+stockmaster.overheadcost AS standard
                      FROM bom,
                        stockmaster
                      WHERE bom.component=stockmaster.stockid
                      AND bom.parent='" . $StkCode . "'
                      AND bom.effectiveto > '" . Date('Y-m-d') . "'
                      AND bom.effectiveafter < '" . Date('Y-m-d') . "'";

                  $ErrMsg = _('CRITICAL ERROR') . '! ' . _('NOTE DOWN THIS ERROR AND SEEK ASSISTANCE') . ': ' . _('Could not retrieve assembly components from the database for'). ' '. $StkCode . _('because').' ';
                  $DbgMsg = _('The SQL that failed was');
                  $AssResult = DB_query($SQL,$db,$ErrMsg,$DbgMsg,true);

                  while ($AssParts = DB_fetch_array($AssResult,$db)){

                    $StandardCost += ($AssParts['standard'] * $AssParts['quantity']) ;
                    /* Need to get the current location quantity
                    will need it later for the stock movement */
                    $SQL="SELECT locstock.quantity
                            FROM locstock
                            WHERE locstock.stockid='" . $AssParts['component'] . "'
                            AND loccode= '" . $Location . "'";

                    $ErrMsg = _('CRITICAL ERROR') . '! ' . _('NOTE DOWN THIS ERROR AND SEEK ASSISTANCE') . ': ' . _('Can not retrieve assembly components location stock quantities because ');
                    $DbgMsg = _('The SQL that failed was');
                    $Result = DB_query($SQL,$db,$ErrMsg,$DbgMsg,true);
                    if (DB_num_rows($Result)==1){
                      $LocQtyRow = DB_fetch_row($Result);
                      $QtyOnHandPrior = $LocQtyRow[0];
                    } else {
                      /*There must be some error this should never happen */
                      $QtyOnHandPrior = 0;
                    }
                    if (empty($AssParts['standard'])) {
                      $AssParts['standard']=0;
                    }
                    $SQL = "INSERT INTO stockmoves (stockid,
                                    type,
                                    transno,
                                    loccode,
                                    trandate,
                                    debtorno,
                                    branchcode,
                                    prd,
                                    reference,
                                    qty,
                                    standardcost,
                                    show_on_inv_crds,
                                    newqoh
                      ) VALUES (
                                    '" . $AssParts['component'] . "',
                                     10,
                                    '" . $InvoiceNo . "',
                                    '" . $Location . "',
                                    '" . $DefaultDispatchDate . "',
                                    '" . $DebtorNo . "',
                                    '" . $BranchCode . "',
                                    '" . $PeriodNo . "',
                                    '" . _('Assembly') . ': ' . $StkCode . ' ' . _('Order') . ': ' . $OrderNo . "',
                                    '" . -$AssParts['quantity'] * $QtyInvoiced . "',
                                    '" . $AssParts['standard'] . "',
                                    0,
                                    newqoh-" . ($AssParts['quantity'] * $QtyInvoiced) . " )";

                    $ErrMsg = _('CRITICAL ERROR') . '! ' . _('NOTE DOWN THIS ERROR AND SEEK ASSISTANCE') . ': ' . _('Stock movement records for the assembly components of'). ' '. $StkCode . ' ' . _('could not be inserted because');
                    $DbgMsg = _('The following SQL to insert the assembly components stock movement records was used');
                    $Result = DB_query($SQL,$db,$ErrMsg,$DbgMsg,true);


                    $SQL = "UPDATE locstock
                        SET quantity = locstock.quantity - " . $AssParts['quantity'] * $QtyInvoiced . "
                        WHERE locstock.stockid = '" . $AssParts['component'] . "'
                        AND loccode = '" . $Location . "'";

                    $ErrMsg = _('CRITICAL ERROR') . '! ' . _('NOTE DOWN THIS ERROR AND SEEK ASSISTANCE') . ': ' . _('Location stock record could not be updated for an assembly component because');
                    $DbgMsg = _('The following SQL to update the locations stock record for the component was used');
                    $Result = DB_query($SQL,$db,$ErrMsg,$DbgMsg,true);
                  } /* end of assembly explosion and updates */

                  /*Update the cart with the recalculated standard cost from the explosion of the assembly's components*/
                  //$_SESSION['Items'.$identifier]->LineItems[$OrderLine->LineNumber]->StandardCost = $StandardCost;
                  $StdCost = $StandardCost;
                } /* end of its an assembly */

                // Insert stock movements - with unit cost
                $LocalCurrencyPrice = ($UnitPrice / $ExRate);

                if (empty($StdCost)) {
                  $StdCost=0;
                }
                
                if ($MBFlag=='B' OR $MBFlag=='M')
                {
                  $SQL = "INSERT INTO stockmoves (stockid,
                                  type,
                                  transno,
                                  loccode,
                                  trandate,
                                  debtorno,
                                  branchcode,
                                  price,
                                  prd,
                                  reference,
                                  qty,
                                  discountpercent,
                                  standardcost,
                                  newqoh,
                                  narrative )
                      VALUES ('" . $StkCode . "',
                          10,
                          '" . $InvoiceNo . "',
                          '" . $Location . "',
                          '" . $DefaultDispatchDate . "',
                          '" . $DebtorNo . "',
                          '" . $BranchCode . "',
                          '" . $LocalCurrencyPrice . "',
                          '" . $PeriodNo . "',
                          '" . $OrderNo . "',
                          '" . -$QtyInvoiced . "',
                          '" . $DiscountPercent . "',
                          '" . $StdCost . "',
                          '" . ($QtyOnHandPrior - $QtyInvoiced) . "',
                          '" . $Narrative . "' )";
                } else {
                // its an assembly or dummy and assemblies/dummies always have nil stock (by definition they are made up at the time of dispatch  so new qty on hand will be nil
                  if (empty($StdCost)) {
                    $StdCost = 0;
                  }
                  
                  $SQL = "INSERT INTO stockmoves (stockid,
                                  type,
                                  transno,
                                  loccode,
                                  trandate,
                                  debtorno,
                                  branchcode,
                                  price,
                                  prd,
                                  reference,
                                  qty,
                                  discountpercent,
                                  standardcost,
                                  narrative )
                      VALUES ('" . $StkCode . "',
                              10,
                              '" . $InvoiceNo . "',
                              '" . $Location . "',
                              '" . $DefaultDispatchDate . "',
                              '" . $DebtorNo . "',
                              '" . $BranchCode . "',
                              '" . $LocalCurrencyPrice . "',
                              '" . $PeriodNo . "',
                              '" . $OrderNo . "',
                              '" . -$QtyInvoiced . "',
                              '" . $DiscountPercent . "',
                              '" . $StdCost . "',
                              '" . $Narrative . "')";
                }

                $ErrMsg = _('CRITICAL ERROR') . '! ' . _('NOTE DOWN THIS ERROR AND SEEK ASSISTANCE') . ': ' . _('Stock movement records could not be inserted because');
                $DbgMsg = _('The following SQL to insert the stock movement records was used');
                $Result = DB_query($SQL,$db,$ErrMsg,$DbgMsg,true);

              /*Get the ID of the StockMove... */
                $StkMoveNo = DB_Last_Insert_ID($db,'stockmoves','stkmoveno');

                $sqlGetTaxRates = "SELECT taxgrouptaxes.calculationorder,
                            taxauthorities.description,
                            taxgrouptaxes.taxauthid,
                            taxauthorities.taxglcode,
                            taxgrouptaxes.taxontax,
                            taxauthrates.taxrate
                        FROM taxauthrates INNER JOIN taxgrouptaxes ON
                          taxauthrates.taxauthority=taxgrouptaxes.taxauthid
                          INNER JOIN taxauthorities ON
                          taxauthrates.taxauthority=taxauthorities.taxid
                        WHERE taxgrouptaxes.taxgroupid=" . $TaxGroup . "
                        AND taxauthrates.dispatchtaxprovince=" . $DispatchTaxProvince . "
                        AND taxauthrates.taxcatid = " . $TaxCatID . "
                        ORDER BY taxgrouptaxes.calculationorder";

                $ErrMsgGetTaxRates = _('The taxes and rates for this item could not be retrieved because');
                $resultGetTaxRates = DB_query($sqlGetTaxRates,$db,$ErrMsgGetTaxRates);
      
                if (DB_num_rows($resultGetTaxRates)==0){
                  prnMsg(_('It appears that taxes are not defined correctly for this customer tax group') ,'error');
                } else {
          
                  $TaxCalculationOrder = 0;
                  $TaxAuthID = 0;
                  $TaxDescription = '';
                  $TaxRate = 0;
                  $TaxOnTax = 0;
                  $TaxGLCode = '';
                  
                  //Insert the taxes that applied to this line 
                  while ($myrowGetTaxRates = DB_fetch_array($resultGetTaxRates))
                  {
                    $TaxCalculationOrder = $myrowGetTaxRates['calculationorder'];
                    $TaxAuthID = $myrowGetTaxRates['taxauthid'];
                    $TaxDescription = $myrowGetTaxRates['description'];
                    $TaxRate = $myrowGetTaxRates['taxrate'];
                    $TaxOnTax = $myrowGetTaxRates['taxontax'];
                    $TaxGLCode = $myrowGetTaxRates['taxglcode'];
                            
                    $SQL = "INSERT INTO stockmovestaxes (stkmoveno,
                                  taxauthid,
                                  taxrate,
                                  taxcalculationorder,
                                  taxontax)
                            VALUES ('" . $StkMoveNo . "',
                              '" . $TaxAuthID . "',
                              '" . $TaxRate . "',
                              '" . $TaxCalculationOrder . "',
                              '" . $TaxOnTax . "')";

                        $ErrMsg = _('CRITICAL ERROR') . '! ' . _('NOTE DOWN THIS ERROR AND SEEK ASSISTANCE') . ': ' . _('Taxes and rates applicable to this invoice line item could not be inserted because');
                        $DbgMsg = _('The following SQL to insert the stock movement tax detail records was used');
                        $Result = DB_query($SQL,$db,$ErrMsg,$DbgMsg,true);
                      //end for each tax for the line
                   } //end loop around different taxes
                 }


              /*Insert Sales Analysis records */

                $SQL="SELECT COUNT(*),
                    salesanalysis.stockid,
                    salesanalysis.stkcategory,
                    salesanalysis.cust,
                    salesanalysis.custbranch,
                    salesanalysis.area,
                    salesanalysis.periodno,
                    salesanalysis.typeabbrev,
                    salesanalysis.salesperson
                  FROM salesanalysis,
                    custbranch,
                    stockmaster
                  WHERE salesanalysis.stkcategory=stockmaster.categoryid
                  AND salesanalysis.stockid=stockmaster.stockid
                  AND salesanalysis.cust=custbranch.debtorno
                  AND salesanalysis.custbranch=custbranch.branchcode
                  AND salesanalysis.area=custbranch.area
                  AND salesanalysis.salesperson=custbranch.salesman
                  AND salesanalysis.typeabbrev ='" . $DefaultSalesType . "'
                  AND salesanalysis.periodno='" . $PeriodNo . "'
                  AND salesanalysis.cust " . LIKE . " '" . $DebtorNo . "'
                  AND salesanalysis.custbranch " . LIKE . " '" . $BranchCode . "'
                  AND salesanalysis.stockid " . LIKE . " '" . $StkCode . "'
                  AND salesanalysis.budgetoractual=1
                  GROUP BY salesanalysis.stockid,
                    salesanalysis.stkcategory,
                    salesanalysis.cust,
                    salesanalysis.custbranch,
                    salesanalysis.area,
                    salesanalysis.periodno,
                    salesanalysis.typeabbrev,
                    salesanalysis.salesperson";

                $ErrMsg = _('The count of existing Sales analysis records could not run because');
                $DbgMsg = _('SQL to count the no of sales analysis records');
                $Result = DB_query($SQL,$db,$ErrMsg,$DbgMsg,true);

                $myrow = DB_fetch_row($Result);

                if ($myrow[0]>0){  /*Update the existing record that already exists */

                  $SQL = "UPDATE salesanalysis
                        SET amt=amt+" . (($UnitPrice * $QtyInvoiced) / $ExRate) . ",
                          cost=cost+" . ($StdCost * $QtyInvoiced) . ",
                          qty=qty +" . $QtyInvoiced . ",
                          disc=disc+" . (($DiscountPercent * $UnitPrice * $QtyInvoiced) / $ExRate) . "
                        WHERE salesanalysis.area='" . $myrow[5] . "'
                          AND salesanalysis.salesperson='" . $myrow[8] . "'
                          AND typeabbrev ='" . $DefaultSalesType . "'
                          AND periodno = '" . $PeriodNo . "'
                          AND cust " . LIKE . " '" . $DebtorNo . "'
                          AND custbranch " . LIKE . " '" . $BranchCode . "'
                          AND stockid " . LIKE . " '" . $StkCode . "'
                          AND salesanalysis.stkcategory ='" . $myrow[2] . "'
                          AND budgetoractual=1";

                } else { /* insert a new sales analysis record */

                  $SQL = "INSERT INTO salesanalysis (	typeabbrev,
                                    periodno,
                                    amt,
                                    cost,
                                    cust,
                                    custbranch,
                                    qty,
                                    disc,
                                    stockid,
                                    area,
                                    budgetoractual,
                                    salesperson,
                                    stkcategory	)
                    SELECT '" . $DefaultSalesType . "',
                      '" . $PeriodNo . "',
                      '" . (($UnitPrice * $QtyInvoiced) / $ExRate) . "',
                      '" . ($StdCost * $QtyInvoiced) . "',
                      '" . $DebtorNo . "',
                      '" . $BranchCode . "',
                      '" . $QtyInvoiced . "',
                      '" . (($DiscountPercent * $UnitPrice * $QtyInvoiced) / $ExRate) . "',
                      '" . $StkCode . "',
                      custbranch.area,
                      1,
                      custbranch.salesman,
                      stockmaster.categoryid
                    FROM stockmaster,
                      custbranch
                    WHERE stockmaster.stockid = '" . $StkCode . "'
                    AND custbranch.debtorno = '" . $DebtorNo . "'
                    AND custbranch.branchcode='" . $BranchCode . "'";
                }

                $ErrMsg = _('Sales analysis record could not be added or updated because');
                $DbgMsg = _('The following SQL to insert the sales analysis record was used');
                $Result = DB_query($SQL,$db,$ErrMsg,$DbgMsg,true);

              /* If GLLink_Stock then insert GLTrans to credit stock and debit cost of sales at standard cost*/

                if ($_SESSION['CompanyRecord']['gllink_stock']==1 AND $StdCost !=0){

              /*first the cost of sales entry*/

                  $SQL = "INSERT INTO gltrans (	type,
                                  typeno,
                                  trandate,
                                  periodno,
                                  account,
                                  narrative,
                                  amount)
                              VALUES ( 10,
                                  '" . $InvoiceNo . "',
                                  '" . $DefaultDispatchDate . "',
                                  '" . $PeriodNo . "',
                                  '" . GetCOGSGLAccount($Area, $StkCode, $DefaultSalesType, $db) . "',
                                  '" . $DebtorNo . " - " . $StkCode . " x " . $QtyInvoiced . " @ " . $StdCost . "',
                                  '" . round(($StdCost * $QtyInvoiced), $_SESSION['CompanyRecord']['decimalplaces']) . "')";

                  $ErrMsg = _('CRITICAL ERROR') . '! ' . _('NOTE DOWN THIS ERROR AND SEEK ASSISTANCE') . ': ' . _('The cost of sales GL posting could not be inserted because');
                  $DbgMsg = _('The following SQL to insert the GLTrans record was used');
                  $Result = DB_query($SQL,$db,$ErrMsg,$DbgMsg,true);

              /*now the stock entry*/
                  $StockGLCode = GetStockGLCode($StkCode,$db);

                  $SQL = "INSERT INTO gltrans (type,
                                typeno,
                                trandate,
                                periodno,
                                account,
                                narrative,
                                amount )
                              VALUES ( 10,
                                '" . $InvoiceNo . "',
                                '" . $DefaultDispatchDate . "',
                                '" . $PeriodNo . "',
                                '" . $StockGLCode['stockact'] . "',
                                '" . $DebtorNo . " - " . $StkCode . " x " . $QtyInvoiced . " @ " . $StdCost . "',
                                '" . round((-$StdCost * $QtyInvoiced), $_SESSION['CompanyRecord']['decimalplaces']) . "')";

                  $ErrMsg = _('CRITICAL ERROR') . '! ' . _('NOTE DOWN THIS ERROR AND SEEK ASSISTANCE') . ': ' . _('The stock side of the cost of sales GL posting could not be inserted because');
                  $DbgMsg = _('The following SQL to insert the GLTrans record was used');
                  $Result = DB_query($SQL,$db,$ErrMsg,$DbgMsg,true);
                } /* end of if GL and stock integrated and standard cost !=0 */

                if ($_SESSION['CompanyRecord']['gllink_debtors']==1 AND $UnitPrice !=0){

              //Post sales transaction to GL credit sales
                  $SalesGLAccounts = GetSalesGLAccount($Area, $StkCode, $DefaultSalesType, $db);

                  $SQL = "INSERT INTO gltrans (type,
                                typeno,
                                trandate,
                                periodno,
                                account,
                                narrative,
                                amount )
                              VALUES ( 10,
                                '" . $InvoiceNo . "',
                                '" . $DefaultDispatchDate . "',
                                '" . $PeriodNo . "',
                                '" . $SalesGLAccounts['salesglcode'] . "',
                                '" . $DebtorNo . " - " . $StkCode . " x " . $QtyInvoiced . " @ " . $UnitPrice . "',
                                '" . round((-($UnitPrice * $QtyInvoiced)/$ExRate), $_SESSION['CompanyRecord']['decimalplaces']) . "')";

                  $ErrMsg = _('CRITICAL ERROR') . '! ' . _('NOTE DOWN THIS ERROR AND SEEK ASSISTANCE') . ': ' . _('The sales GL posting could not be inserted because');
                  $DbgMsg = '<br />' ._('The following SQL to insert the GLTrans record was used');
                  $Result = DB_query($SQL,$db,$ErrMsg,$DbgMsg,true);

                  if ($DiscountPercent !=0){

                    $SQL = "INSERT INTO gltrans (type,
                                  typeno,
                                  trandate,
                                  periodno,
                                  account,
                                  narrative,
                                  amount )
                                  VALUES ( 10,
                                    '" . $InvoiceNo . "',
                                    '" . $DefaultDispatchDate . "',
                                    '" . $PeriodNo . "',
                                    '" . $SalesGLAccounts['discountglcode'] . "',
                                    '" . $DebtorNo . " - " . $StkCode . " @ " . ($DiscountPercent * 100) . "%',
                                    '" . round((($UnitPrice * $QtyInvoiced * $DiscountPercent)/$ExRate), $_SESSION['CompanyRecord']['decimalplaces']) . "')";

                    $ErrMsg = _('CRITICAL ERROR') . '! ' . _('NOTE DOWN THIS ERROR AND SEEK ASSISTANCE') . ': ' . _('The sales discount GL posting could not be inserted because');
                    $DbgMsg = _('The following SQL to insert the GLTrans record was used');
                    $Result = DB_query($SQL,$db,$ErrMsg,$DbgMsg,true);
                  } /*end of if discount !=0 */
                } /*end of if sales integrated with debtors */
              } /*end of OrderLine loop */
            }

              if ($_SESSION['CompanyRecord']['gllink_debtors']==1){

                // Edward 20150407: Update to accomodate for posting to rounding adjustment
                //=========================================================================
                if(locale_number_format($roundAdjustmentAmount, $_SESSION['CompanyRecord']['decimalplaces']) != 0) {
                    
                    $sql = "SELECT roundadjact
                        FROM emp_companies
                        WHERE coycode=1";
                    $Result = DB_query($sql,$db);
                  
                    while ($row = mysqli_fetch_array($Result)){
                      $roundAdjGLAccount = $row['roundadjact'] ;
                    }
                    
                    
                    $SQL = "INSERT INTO gltrans (type,
                        typeno,
                        trandate,
                        periodno,
                        account,
                        narrative,
                        amount )
                      VALUES ( 10,
                        '" . $InvoiceNo . "',
                        '" . $DefaultDispatchDate . "',
                        '" . $PeriodNo . "',
                        '" . $roundAdjGLAccount . "',
                        'Rounding Adjustment',
                        '" . -locale_number_format($roundAdjustmentAmount/$ExRate, $_SESSION['CompanyRecord']['decimalplaces']) . "')";
                
                    $ErrMsg = _('CRITICAL ERROR') . '! ' . _('NOTE DOWN THIS ERROR AND SEEK ASSISTANCE') . ': ' . _('The sales GL posting could not be inserted because');
                    $DbgMsg = '<br />' ._('The following SQL to insert the GLTrans record was used');
                    $Result = DB_query($SQL,$db,$ErrMsg,$DbgMsg,true);
                    
                }
                
            /*Post debtors transaction to GL debit debtors, credit freight re-charged and credit sales */
                if (($zencartSalesSubTotal + $zencartSalesShipping + $zencartSalesTax) !=0) {
                  $SQL = "INSERT INTO gltrans (	type,
                                  typeno,
                                  trandate,
                                  periodno,
                                  account,
                                  narrative,
                                  amount	)
                                VALUES ( 10,
                                  '" . $InvoiceNo . "',
                                  '" . $DefaultDispatchDate . "',
                                  '" . $PeriodNo . "',
                                  '" . $_SESSION['CompanyRecord']['debtorsact'] . "',
                                  '" . $DebtorNo . " zcsales plus zctax',
                                  '" . round((($zencartSalesSubTotal + $zencartSalesShipping + $zencartSalesTax)/$ExRate), $_SESSION['CompanyRecord']['decimalplaces']) . "')";

                  $ErrMsg = _('CRITICAL ERROR') . '! ' . _('NOTE DOWN THIS ERROR AND SEEK ASSISTANCE') . ': ' . _('The total debtor GL posting could not be inserted because');
                  $DbgMsg = _('The following SQL to insert the total debtors control GLTrans record was used');
                  $Result = DB_query($SQL,$db,$ErrMsg,$DbgMsg,true);
                }
                
                /*Could do with setting up a more flexible freight posting schema that looks at the sales type and area of the customer branch to determine where to post the freight recovery */

                if ($zencartSalesShipping !=0) {
                  $SQL = "INSERT INTO gltrans (
                        type,
                        typeno,
                        trandate,
                        periodno,
                        account,
                        narrative,
                        amount	)
                    VALUES (
                      10,
                      '" . $InvoiceNo . "',
                      '" . $DefaultDispatchDate . "',
                      '" . $PeriodNo . "',
                      '" . $_SESSION['CompanyRecord']['freightact'] . "',
                      '" . $DebtorNo . " zcshipping',
                      '" . round((-$zencartSalesShipping/$ExRate), $_SESSION['CompanyRecord']['decimalplaces']) . "')";

                  $ErrMsg = _('CRITICAL ERROR') . '! ' . _('NOTE DOWN THIS ERROR AND SEEK ASSISTANCE') . ': ' . _('The freight GL posting could not be inserted because');
                  $DbgMsg = _('The following SQL to insert the GLTrans record was used');
                  $Result = DB_query($SQL,$db,$ErrMsg,$DbgMsg,true);
                }

                
                $sqlGetTaxRates = "SELECT taxgrouptaxes.calculationorder,
                            taxauthorities.description,
                            taxgrouptaxes.taxauthid,
                            taxauthorities.taxglcode,
                            taxgrouptaxes.taxontax,
                            taxauthrates.taxrate
                        FROM taxauthrates INNER JOIN taxgrouptaxes ON
                          taxauthrates.taxauthority=taxgrouptaxes.taxauthid
                          INNER JOIN taxauthorities ON
                          taxauthrates.taxauthority=taxauthorities.taxid
                        WHERE taxgrouptaxes.taxgroupid=" . $TaxGroup . "
                        AND taxauthrates.dispatchtaxprovince=" . $DispatchTaxProvince . "
                        AND taxauthrates.taxcatid = " . $TaxCatID . "
                        ORDER BY taxgrouptaxes.calculationorder";

                $ErrMsgGetTaxRates = _('The taxes and rates for this item could not be retrieved because');
                $resultGetTaxRates = DB_query($sqlGetTaxRates,$db,$ErrMsgGetTaxRates);
      
                if (DB_num_rows($resultGetTaxRates)==0){
                  prnMsg(_('It appears that taxes are not defined correctly for this customer tax group') ,'error');
                } else {
          
                  $TaxCalculationOrder = 0;
                  $TaxAuthID = 0;
                  $TaxDescription = '';
                  $TaxRate = 0;
                  $TaxOnTax = 0;
                  $TaxGLCode = '';
                  
                  //Insert the taxes that applied to this line 
                  while ($myrowGetTaxRates = DB_fetch_array($resultGetTaxRates))
                  {
                    $TaxCalculationOrder = $myrowGetTaxRates['calculationorder'];
                    $TaxAuthID = $myrowGetTaxRates['taxauthid'];
                    $TaxDescription = $myrowGetTaxRates['description'];
                    $TaxRate = $myrowGetTaxRates['taxrate'];
                    $TaxOnTax = $myrowGetTaxRates['taxontax'];
                    $TaxGLCode = $myrowGetTaxRates['taxglcode'];
                            
                    if ($zencartSalesTax !=0 ){
                      $SQL = "INSERT INTO gltrans (	type,
                                      typeno,
                                      trandate,
                                      periodno,
                                      account,
                                      narrative,
                                      amount	)
                                    VALUES ( 10,
                                      '" . $InvoiceNo . "',
                                      '" . $DefaultDispatchDate . "',
                                      '" . $PeriodNo . "',
                                      '" . $TaxGLCode . "',
                                      '" . $DebtorNo . " zctax',
                                      '" . round((-$zencartSalesTax/$ExRate), $_SESSION['CompanyRecord']['decimalplaces']) . "')";

                      $ErrMsg = _('CRITICAL ERROR') . '! ' . _('NOTE DOWN THIS ERROR AND SEEK ASSISTANCE') . ': ' . _('The tax GL posting could not be inserted because');
                      $DbgMsg = _('The following SQL to insert the GLTrans record was used');
                      $Result = DB_query($SQL,$db,$ErrMsg,$DbgMsg,true);
                    } //end for each tax for the line
                  } //end loop around different taxes
                }

              
              
                EnsureGLEntriesBalance(10,$InvoiceNo,$db);

                /*Also if GL is linked to debtors need to process the debit to bank and credit to debtors for the payment */
                /*Need to figure out the cross rate between customer currency and bank account currency */
                
                $AmountPaid = $zencartSalesSubTotal + $zencartSalesShipping + $zencartSalesTax;

                
                if ($AmountPaid != 0)
                {
                  $ReceiptNumber = GetNextTransNo(12,$db);
                  $SQL="INSERT INTO gltrans (type,
                                typeno,
                                trandate,
                                periodno,
                                account,
                                narrative,
                                amount)
                      VALUES (12,
                        '" . $ReceiptNumber . "',
                        '" . $DefaultDispatchDate . "',
                        '" . $PeriodNo . "',
                        '" . $BankAccount . "',
                        '" . $LocationName . ' ' . _('Zencart Sale') . ' ' . $InvoiceNo . "',
                        '" . round(($AmountPaid/$ExRate), $_SESSION['CompanyRecord']['decimalplaces']) . "')";
                  $DbgMsg = _('The SQL that failed to insert the GL transaction for the bank account debit was');
                  $ErrMsg = _('Cannot insert a GL transaction for the bank account debit');
                  $result = DB_query($SQL,$db,$ErrMsg,$DbgMsg,true);

                  /* Now Credit Debtors account with receipt */
                  $SQL="INSERT INTO gltrans ( type,
                      typeno,
                      trandate,
                      periodno,
                      account,
                      narrative,
                      amount)
                  VALUES (12,
                    '" . $ReceiptNumber . "',
                    '" . $DefaultDispatchDate . "',
                    '" . $PeriodNo . "',
                    '" . $_SESSION['CompanyRecord']['debtorsact'] . "',
                    '" . $LocationName . ' ' . _('Zencart Sale') . ' ' . $InvoiceNo . "',
                    '" . round(-($AmountPaid/$ExRate), $_SESSION['CompanyRecord']['decimalplaces']) . "')";
                  $DbgMsg = _('The SQL that failed to insert the GL transaction for the debtors account credit was');
                  $ErrMsg = _('Cannot insert a GL transaction for the debtors account credit');
                  $result = DB_query($SQL,$db,$ErrMsg,$DbgMsg,true);
                }//amount paid we not zero
                
                EnsureGLEntriesBalance(12,$ReceiptNumber,$db);
                
              } /*end of if Sales and GL integrated */
              
              if ($AmountPaid != 0)
              {
                if (!isset($ReceiptNumber)){
                  $ReceiptNumber = GetNextTransNo(12,$db);
                }
                //Now need to add the receipt banktrans record
                //First get the account currency that it has been banked into
                $result = DB_query("SELECT rate FROM currencies
                          INNER JOIN bankaccounts 
                          ON currencies.currabrev=bankaccounts.currcode
                          WHERE bankaccounts.accountcode='" . $BankAccount . "'",$db);
                $myrow = DB_fetch_row($result);
                $BankAccountExRate = $myrow[0];

                /*
                 * Some interesting exchange rate conversion going on here
                 * Say :
                 * The business's functional currency is NZD
                 * Customer location counter sales are in AUD - 1 NZD = 0.80 AUD
                 * Banking money into a USD account - 1 NZD = 0.68 USD
                 *
                 * Customer sale is for $100 AUD
                 * GL entries  conver the AUD 100 to NZD  - 100 AUD / 0.80 = $125 NZD
                 * Banktrans entries convert the AUD 100 to USD using 100/0.8 * 0.68
                */

                //insert the banktrans record in the currency of the bank account

                $SQL="INSERT INTO banktrans (type,
                      transno,
                      bankact,
                      ref,
                      exrate,
                      functionalexrate,
                      transdate,
                      banktranstype,
                      amount,
                      currcode)
                    VALUES (12,
                      '" . $ReceiptNumber . "',
                      '" . $BankAccount . "',
                      '" . $LocationName . ' ' . _('Counter Sale') . ' ' . $InvoiceNo . "',
                      '" . $ExRate . "',
                      '" . $BankAccountExRate . "',
                      '" . $DefaultDispatchDate . "',
                      '" . $PaymentMethod . "',
                      '" . $AmountPaid * $BankAccountExRate . "',
                      '" . $DefaultCurrency . "')";

                $DbgMsg = _('The SQL that failed to insert the bank account transaction was');
                $ErrMsg = _('Cannot insert a bank transaction');
                $result = DB_query($SQL,$db,$ErrMsg,$DbgMsg,true);

                //insert a new debtortrans for the receipt

                $SQL = "INSERT INTO debtortrans (transno,
                        type,
                        debtorno,
                        trandate,
                        inputdate,
                        prd,
                        reference,
                        rate,
                        ovamount,
                        alloc,
                        invtext,
                        settled)
                    VALUES ('" . $ReceiptNumber . "',
                      12,
                      '" . $DebtorNo . "',
                      '" . $DefaultDispatchDate . "',
                      '" . date('Y-m-d H-i-s') . "',
                      '" . $PeriodNo . "',
                      '" . $InvoiceNo . "',
                      '" . $ExRate . "',
                      '" . -$AmountPaid . "',
                      '" . -$AmountPaid . "',
                      '" . $LocationName . ' ' . _('Zencart Sale') ."',
                      '1')";

                $DbgMsg = _('The SQL that failed to insert the customer receipt transaction was');
                $ErrMsg = _('Cannot insert a receipt transaction against the customer because') ;
                $result = DB_query($SQL,$db,$ErrMsg,$DbgMsg,true);

                $ReceiptDebtorTransID = DB_Last_Insert_ID($db,'debtortrans','id');

                $SQL = "UPDATE debtorsmaster SET lastpaiddate = '" . $DefaultDispatchDate . "',
                                lastpaid='" . $AmountPaid . "'
                            WHERE debtorsmaster.debtorno='" . $DebtorNo . "'";

                $DbgMsg = _('The SQL that failed to update the date of the last payment received was');
                $ErrMsg = _('Cannot update the customer record for the date of the last payment received because');
                $result = DB_query($SQL,$db,$ErrMsg,$DbgMsg,true);

                //and finally add the allocation record between receipt and invoice

                $SQL = "INSERT INTO custallocns (	amt,
                                  datealloc,
                                  transid_allocfrom,
                                  transid_allocto )
                            VALUES  ('" . $AmountPaid . "',
                                '" . $DefaultDispatchDate . "',
                                 '" . $ReceiptDebtorTransID . "',
                                 '" . $DebtorTransID . "')";
                $DbgMsg = _('The SQL that failed to insert the allocation of the receipt to the invoice was');
                $ErrMsg = _('Cannot insert the customer allocation of the receipt to the invoice because');
                $result = DB_query($SQL,$db,$ErrMsg,$DbgMsg,true);
              } //end if $_POST['AmountPaid']!= 0

              DB_Txn_Commit($db);
            // *************************************************************************
            //   E N D   O F   I N V O I C E   S Q L   P R O C E S S I N G
            // *************************************************************************

              echo prnMsg( _('Invoice number'). ' '. $InvoiceNo .' '. _('processed'), 'success');

             
            }
            
            
            
            //=====Get the products for the sales (end)=====
            
           
      }
    }
  }
}
      
echo '<tr><td>';
echo '<div class="centre"><input type="submit" name="sync_sales" value="Synchronise Sales from Zencart"></div>';
echo '</td></tr>';	
echo '</table>';

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