" . $sqlTxt ; $sqlTxt = 'DELETE UPSGlobal.* FROM UPSGlobal WHERE (' . $rules . ');'; mysql_query ($sqlTxt); echo "
" . $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*')"); */ ?>