<?php
define('TEST_EMAIL', 'cbake@livemercial.com');
$RunNow = $_SERVER["argv"][1];

/*

This script is designed to re-calculate the tracking database as well as orders/lineitems
whenever a discrepency has been discovered.

*/


	error_reporting (E_ERROR | E_WARNING | E_PARSE);
	require_once("mimemail.lib");
	require_once("sql.lib");
	require_once("mysql.lib");

	$DevMode = 1;
	$TDB = new clsDB();
	$TDB->Connect();
	$TDB->SetDatabase("taxes");
	$TDB->DevMode = $DevMode;

	$SDB = new clsDB();
	$SDB->Connect();
	$SDB->SetDatabase("tracking");
	$SDB->DevMode = $DevMode;

	$SDB2 = new clsDB();
	$SDB2->Connect();
	$SDB2->SetDatabase("tracking");
	$SDB2->DevMode = $DevMode;

	$D4B2 = new mysqlDB();
	$D4B2->Connect();
	$D4B2->SetDatabase("vcc4");
	$D4B2->DevMode = $DevMode;

	$D4B = new mysqlDB();
	$D4B->Connect();
	$D4B->SetDatabase("vcc4");
	$D4B->DevMode = $DevMode;


function FixThePuff() {
	global $TDB;
	global $SDB;
	global $D4B;
	global $D4B2;

	// Production Values
	$EmailTo            = "cbake@livemercial.com";
    $ClientID           = 432;
	$Offers             = array(1,2);
    $ProductIDs         = array(1000011, 1000007);
    $TaxableStatesArray = array("CA");
    $MissingPrice       = 3.99;

    // Get all orders with missing amount
    $SQL = "SELECT
                   o.orderid,
                   o.shipping as shipping,
                   o.total,
                   o.tax,
                   o.shipstate,
                   o.shipzip,
                   o.date_ordered,
                   o.cid,
                 oli.quantity
              FROM orders_2007 o
              LEFT JOIN orderlineitems_2007 oli
                ON o.clientid = oli.clientid
               AND o.orderid = oli.orderid
             WHERE o.clientid = '".$ClientID."'
               AND o.offerid
                IN (".implode(",", $Offers).")
               AND oli.productid
                IN (".implode(",", $ProductIDs).")";

     $D4B->Execute($SQL);
     $TotalOrdersRepaired = $D4B->NumRows();

     WHILE($ORDER = $D4B->GetRow())
     {
        $Shipping  = $ORDER->shipping;
        $Tax       = $ORDER->tax;
        $Total     = $ORDER->total;
        $OrderID   = $ORDER->orderid;

        // Calculate Missing Money
        $MissingTotal += ($MissingPrice * $ORDER->quantity);

        // Re-calculate Tax - If Any
        if(in_Array($ORDER->shipstate, $TaxableStatesArray)){
           // Get Zip Tax
           $TaxRate      = $TDB->ExecuteAndReturn("SELECT sales_tax FROM tax_rates WHERE state='".$ORDER->shipstate."'
                                                   AND zipcode='".$ORDER->shipzip."'");
           // Get Missing Tax Amount
           $MissingTax   = ($MissingTotal * $TaxRate);
           // Set New Tax Amount
           $NewTax       = number_format(($MissingTax + $Tax),2);
           // Set New Total
           $NewTotal    += $NewTax;
           // Set New Shipping
           $NewShipping += $MissingTotal;
        }
        else
        {
           $NewTotal    += $MissingTotal;    // Set New Total
           $NewShipping += $MissingTotal;    // Set New Shipping
           $NewTax       = $Tax;             // Carry Over $0 Tax Value
        }

        // Update Orderlineitems Table
        $Update_SQL = "UPDATE orderlineitems_2007 oli
                          SET oli.shipping = '".$NewShipping."'
                        WHERE oli.orderid  = '".$ORDER->orderid."'
                          AND oli.clientid = '".$ClientID."'
                          AND oli.productid
                           IN (".implode(",", $ProductIDs).")";
        // Process Query
        //$D4B2->Execute($Update_SQL);

        // Update Orders Table for each OrderID
        $Update_SQL = "UPDATE orders_2007 o
                          SET
                                  shipping = '".$NewShipping."',
                                  total    = '".$NewTotal."',
                                  tax      = '".$NewTax."'
                          WHERE   clientid = '".$ClientID."'
                            AND   offerid
                             IN   (".implode(",", $Offers).")
                            AND   orderid  = '".$ORDER->orderid."' ";

        // Process Query
        //$D4B2->Execute($Update_SQL);

        // Global Values For Tracking Update
        $GLOBAL[($ORDER->date_ordered)][($ORDER->cid)]->Shipping += $MissingTotal;
        $GLOBAL[($ORDER->date_ordered)][($ORDER->cid)]->Total    += $MissingTotal;
        $GLOBAL[($ORDER->date_ordered)][($ORDER->cid)]->Tax      += $NewTax;
        $GLOBAL[($ORDER->date_ordered)][($ORDER->cid)]->Date      = $ORDER->date_ordered;
        $GLOBAL[($ORDER->date_ordered)][($ORDER->cid)]->CID       = $ORDER->cid;


     } // End While - Orders Update


     // Update Tracking Values
     foreach($GLOBAL as $DATE)
     {
        foreach($DATE as $CID)
        {
               // Get Original Values
               $Select_SQL = "SELECT tax, shipping, total
                                FROM orders_day
                               WHERE clientid = '".$ClientID."'
                                 AND offerid
                                  IN (".implode(",", $Offers).")
                                 AND date = '".$CID->Date."'
                                 AND cid  = '".$CID->CID."' ";
               // Process Query
               $SDB->Execute($Select_SQL);

               // Loop Tracking Rows
               WHILE($TrROW = $SDB->GetRow())
               {
                    // Set New Values
                    $TotalShipping = ($TrROW->shipping + $CID->Shipping);
                    $TotalTotal    = ($TrROW->total    + $CID->Total);
                    $TotalTax      = ($TrROW->tax      + $CID->Tax);

                    // Update Tracking row's values
                    $Update_SQL = "UPDATE orders_day
                                      SET shipping = '".$TotalShipping."',
                                          total    = '".$TotalTotal."',
                                          tax      = '".$TotalTax."'
                                    WHERE
                                          date     = '".$TrRow->date."'
                                      AND cid      = '".$TrRow->cid."
                                      AND offerid
                                       IN (".implode(",", $Offers).")
                                      AND clientid = '".$ClientID."'
                                      AND shipping = '".$TrROW->shipping."'
                                      AND total    = '".$TrROW->total."'
                                      AND tax      = '".$TrROW->tax."' ";

                    // Process Query
                    //$SDB2->Execute($Update_SQL);

               } // end While TrROW

        }  // end Foreach
     }  // end Foreach




	// Send Confirmation Email
    $sendmail = new mime_mail;
    $sendmail->MsgHeader["Sender"] = $sendmail->MsgHeader["X-From"] = "orderexports@livemercial.com";
    $sendmail->MsgHeader["From"] =  "orderexports@livemercial.com";
    $sendmail->MsgHeader["To"] =    $EmailTo;
    $sendmail->MsgHeader["Date"] =  date("r");
    $sendmail->MsgHeader["Subject"] = "Pancake Puff Fix Report";
    $sendmail->body = "Total Repaired Orders: ".$TotalOrdersRepaired."\n";
    $sendmail->add_message($sendmail->body);
    $sendmail->SendMessage = 1;
    $sendmail->send_mail();

    return $TotalRecords;
}


FixThePuff();

$TDB->Close();
$SDB->Close();
$D4B->Close();
$D4B2->Close();

?>