Untitled

<?php
include_once ('dbcon.php');
function movetoArchive($rules)
  {
	$sqlTxt = 'INSERT INTO UPSGlobalArchive SELECT UPSGlobal.* FROM UPSGlobal WHERE (' . $rules . ');';
	mysql_query ($sqlTxt);
	echo "<br>" . $sqlTxt ;
	$sqlTxt = 'DELETE UPSGlobal.* FROM UPSGlobal WHERE (' . $rules . ');';
	mysql_query ($sqlTxt);
	echo "<br>" . $sqlTxt ;
  }
$invdate = "'2008-01-12'";
$revband1 = 20000000;
$revband2 = 20000000;
$cont1 = 6;
$cont2 = 11;
$baserate1 = 10;
$baserate2 = 10;
$strDate = "'2008-01-06'";
$sqlTxt = 'UPDATE UPSGlobal SET UPSGlobal.MSIrate1 = UPSGlobal.NetAmount, UPSGlobal.MSIrate2 = UPSGlobal.NetAmount WHERE (transactiondate <= ' . $strDate . ');' ;
mysql_query($sqlTxt);
movetoArchive('transactiondate <= ' . $strDate); 

//---fill in svc and gsr
    $sqlTxt = "UPDATE UPSGlobal SET UPSGlobal.MSIrate1 = [NetAmount], UPSGlobal.MSIrate2 = [NetAmount] WHERE (((UPSGlobal.ChargeCatDtlCode) in ('GSR','SVCH','VOID','CADJ','FPOD','MISC')));";
	mysql_query($sqlTxt);
    movetoArchive ("((UPSGlobal.ChargeCatDtlCode) In ('GSR','SVCH','VOID','CADJ','FPOD','MISC'))");
    
    $sqlTxt = "UPDATE UPSGlobal SET UPSGlobal.MSIrate1 = [NetAmount], UPSGlobal.MSIrate2 = [NetAmount] WHERE (((UPSGlobal.ChargeDescCode) In ('IRS','AKC','CBF','EVS','HHC','HIC','HIR','IRP','LSC','RSO','SAT','F/D')));";
mysql_query($sqlTxt);
    movetoArchive ("((UPSGlobal.ChargeDescCode) In ('IRS','AKC','CBF','EVS','HHC','HIC','HIR','IRP','LSC','RSO','SAT','F/D'))");
    
    $sqlTxt = "UPDATE UPSGlobal SET UPSGlobal.MSIrate1 = [NetAmount] + [IncentiveAmount], UPSGlobal.MSIrate2 = [NetAmount] WHERE (((UPSGlobal.ChargeDescCode) in ('AHC','RDC','RDR','LDC','LDR','RES','ADS','HRC','HRS','HSC','DCS','DCR')));";
	mysql_query($sqlTxt);
    movetoArchive ("ChargeDescCode in ('SUR','AHC','RDC','RDR','LDC','LDR','RES','ADS','HRC','HRS','HSC','DCS','DCR')");
    
    $sqlTxt = "UPDATE UPSGlobal SET UPSGlobal.MSIrate1 = [NetAmount] + [IncentiveAmount], UPSGlobal.MSIrate2 = [NetAmount] WHERE (UPSGlobal.ChargeCatDtlCode = 'ADC');";
	mysql_query($sqlTxt);
    movetoArchive ("UPSGlobal.ChargeCatDtlCode = 'ADC'");
    movetoArchive ("UPSGlobal.Billoptioncode in ('DCB','DFC','DTP')");

//---calculate ARS charges

    $sqlTxt = "UPDATE UPSGlobal SET MSIrate1 = [pkgqty]*IIf([accountnumber] In ('0000E4W567','0000E4W698','000027E1W0'),17.65,10.95), MSIrate2 = [pkgqty]*IIf([accountnumber] In ('0000E4W567','0000E4W698','000027E1W0'),17.65,10.95) WHERE (((chargedesc)='ARS Ground'));";
	mysql_query($sqlTxt);
    $sqlTxt = "UPDATE UPSGlobal SET MSIrate1 = [pkgqty]*IIf([accountnumber] In ('0000E4W567','0000E4W698','000027E1W0'),98,59.25), MSIrate2 = [pkgqty]*IIf([accountnumber] In ('0000E4W567','0000E4W698','000027E1W0'),98,59.25) WHERE (((chargedesc)='ARS Next Day Air'));";
	mysql_query($sqlTxt);
    movetoArchive ("(chargedesc) in ('ARS Next Day Air','ARS Ground')");

//---fill in rates in
        $filler = "((UPSGlobal.ChargeCatCode) in ('ADJ','SHP')) AND (ChargeDescCode in ('001','002','003','013','014','059') ) AND (InvoiceDate = " . $invdate . ") AND ";
        $sqlTxt = "UPDATE UPSGlobal INNER JOIN tblservice ON UPSGlobal.ChargeDescCode = tblservice.serviceCode SET UPSGlobal.MSIrate1 = (1-getincentive(" . $cont1 . ",[serviceid],[billedweight]," . $revband1 . ",No , zone))*GETRATE(" . $baserate1 . ",[serviceid],[billedweight],[zone]), UPSGlobal.MSIrate2 = (1-getincentive(" . $cont2 . ",[serviceid],[billedweight]," . $revband2 . ",No,zone))*GETRATE(" . $baserate2 . ",[serviceid],[billedweight],[zone]) WHERE (" . $filler . " (chargedesc like '*commercial*'));";
		mysql_query($sqlTxt);
        $sqlTxt = "UPDATE UPSGlobal INNER JOIN tblservice ON UPSGlobal.ChargeDescCode = tblservice.serviceCode SET UPSGlobal.MSIrate1 = (1-getincentive(" . $cont1 . ",[serviceid],[billedweight]," . $revband1 . ",yes , zone))*GETRATE(" . $baserate1 . ",[serviceid],[billedweight],[zone]), UPSGlobal.MSIrate2 = (1-getincentive(" . $cont2 . ",[serviceid],[billedweight]," . $revband2 . ",yes, zone))*GETRATE(" . $baserate2 . ",[serviceid],[billedweight],[zone]) WHERE (" . $filler . " (chargedesc like '*residential*'));";
		mysql_query($sqlTxt);
//---apply minimums to rates
        $sqlTxt = "UPDATE UPSGlobal INNER JOIN (tblContractMins INNER JOIN tblservice ON (tblservice.serviceID = tblContractMins.ServiceID) AND (tblContractMins.ServiceID = tblservice.serviceID)) ON UPSGlobal.ChargeDescCode = tblservice.serviceCode SET UPSGlobal.MSIrate1 = getmax([msirate1],[minvalue]) WHERE (" . $filler . " ((UPSGlobal.LeadShipmentNo)=[trackingno]) AND ((tblContractMins.contractID)=" . $cont1 . "));";
		mysql_query($sqlTxt);
        $sqlTxt = "UPDATE UPSGlobal INNER JOIN (tblContractMins INNER JOIN tblservice ON (tblservice.serviceID = tblContractMins.ServiceID) AND (tblContractMins.ServiceID = tblservice.serviceID)) ON UPSGlobal.ChargeDescCode = tblservice.serviceCode SET UPSGlobal.MSIrate2 = getmax([msirate2],[minvalue]) WHERE (" . $filler . " ((UPSGlobal.LeadShipmentNo)=[trackingno]) AND ((tblContractMins.contractID)=" . $cont2 . "));";
		mysql_query($sqlTxt);
        
        movetoArchive ($filler . " (chargedesc like '*commercial*')");
        movetoArchive ($filler . " (chargedesc like '*residential*')");

?>