#! /bin/sh sBillPeriod="2024/02" # in vi searched tdatacollection.unl for all batchid and found none #\(|62615|\||62662|\||62665|\||62666|\||62669|\||62674|\||62675|\||62676|\) sBatchID=62615 #62615,62662,62665,62666,62669,62674,62675,62676 sBatchTypeID=200 #VicTrack Installation sSystemID=200 #QSM Jobs sProgramID=2303 #AnyTime QuoteSys Installations # (2301 is "QuoteSys Installations", and does not test PPTaken) sSyplierID=29 #VICTRACK sServiceID=131192 #OBS NNA JCS| sTransactionTypeID=219 #60009 JOBS LABOUR sTransactionGroup=3 #OTHER sCallNumber="SO00003377" #JOB NUMBER (Var01 from VRTQuote BillJobs view) sServiceTypeID=988 #JCS|60009 JOBS LABOUR (from VRTQuote BillJobs view) sSeqNo="SO00003377" #SeqNo field (from VRTQuote BillJobs view) sShipTo="NNA" #ShipTo field (from VRTQuote BillJobs view) echo "Pulling apart the QuoteSuetm PP code SQL's....." echo "" echo "" echo "Get BatchTypeID" cat >/tmp/s22.$$ </tmp/s22.$$ </tmp/s22.$$ < 0) then begin # AND Quote_Items.CallNumber LIKE '$sCallNumber' # AND Quote_Items.ServiceTypeID = $sServiceTypeID #SQLCMD0 # # ## TESTING... ## ##USE VRTQuote; ##SELECT * ##FROM Quote_Items ##WHERE Quote_Items.BillPeriod LIKE '022024' ##AND Quote_Items.CallNumber LIKE 'SO00003377' ## ##Result ##ItemID CallNumber OptionID OptionNumber ItemTypeID ItemType ItemDescription ItemQuantity SellPrice AdminFee ItemGST RevAmtExGST RevGST RevAmtIncGST Active Bill PurchaseOrder PurchaseOrderDate Requisition RequisitionDate Supplier PurchasePrice ServiceType BillDate BillPeriod Centre Activity Element Subledger ServiceTypeID SupplierID PPTaken ## ## ##USE VRTQuote; ##SELECT * ##FROM Quote_Items ##WHERE BillPeriod LIKE '022024' ## AND ServiceTypeID = 988; ##SELECT * ##FROM BillJobs ##WHERE BillPeriod LIKE '022024' ## AND ServiceTypeID = 988; ##SELECT * ##FROM BillPurchasing ##WHERE BillPeriod LIKE '022024' ## AND ServiceTypeID = 988; cat < IntToStr(qryQuoteSystem['ServiceTypeID'])) then begin # sServiceTypeID := IntToStr( # qryQuoteSystem.FieldByName('ServiceTypeID').AsInteger); # // Look up the code in the tServiceType table using ID. # qryLook.Close; # qryLook.SQL.Strings[2] := 'WHERE ID = ' + sServiceTypeID; # qryLook.Open; # if qryLook.Eof then begin # if (sShipTo = 'VRT') then begin # sCode := 'JI1'; // VRT Default. # sDescription := 'JOBS LABOUR INSTALL'; # end # else begin # sCode := 'JIL'; // Default if not VRT. # sDescription := 'JOBS INSTALL LABOUR'; # end; # end # else begin # sCode := qryLook.FieldByName('Code').AsString; # sDescription := qryLook.FieldByName('Description').AsString; # end; # end; # end; # sListFields := sListFields + 'ServiceType,'; # sListValues := sListValues + '''' + sCode +''','; # sListFields := sListFields + 'ServiceID,'; # // RJC 060621: sCode added to the ServiceID entry. # sServiceID := Trim('JOBS '+ sShipTo +' '+ sCode); # sListValues := sListValues + '''' + sServiceID +''','; # if not VarIsNull(qryQuoteSystem['LogDateTime']) then begin # sListFields := sListFields + 'TxnDate,'; # dLogDateTime := qryQuoteSystem.FieldByName('LogDateTime').AsDateTime; # sListValues := sListValues +''''+FormattedDateString(dLogDateTime)+''','; # sListFields := sListFields + 'TxnTime,'; # sListValues := sListValues +''''+ TimeToStr(dLogDateTime) +''','; # end; # # // Now set the transaction type values. # if (sShipTo = 'VRT') then sServiceID := Trim(sShipTo +' '+ sDescription) # else sServiceID := Trim(sDescription); # // Now set the transaction type values - first the description. # if (sShipTo = 'VRT') then sServiceID := Trim(sShipTo +' '+ sDescription) # else sServiceID := Trim(sDescription); # // Now set the transaction type values - search for it. # vDescription := sServiceID; # with qryTT do # lFound := Locate('Description', vDescription, [loCaseInsensitive]); # // We have it in the table. # if lFound then begin # sTransactionTypeID := IntToStr(qryTT.FieldByName('ID').AsInteger); # end # else begin # // New - create a new entry in tTransactionType. # AddNewTransactionType( sServiceID, nTransactionGroupID, nSystemID); # qryTT.Close; # qryTT.Open; # // Now retrieve the index number - ID. # with qryTT do # lFound := Locate('Description', vDescription, [loCaseInsensitive]); # if lFound then begin # // We have it - of course. # sTransactionTypeID := IntToStr(qryTT.FieldByName('ID').AsInteger); # end # else begin # // Something wrong - use a generic value. # sTransactionTypeID := IntToStr(DEFAULT_TRANSACTIONTYPEID); # end; // lFound. # end; // lFound. # sListFields := sListFields + 'TransactionTypeID,'; # sListValues := sListValues + sTransactionTypeID + ','; # { # nTransactionType := -1; # for i := 0 to slTransactionTypeName.Count-1 do begin # sSubString := slTransactionTypeName.Strings[i]; # nPos := Pos(' ',sSubString)+1; # if (Copy(sSubString,nPos,50) = sServiceID) then begin # nTransactionType := i; # break; # end; # end; // for. # if (nTransactionType > -1) and # (nTransactionType < slTransactionTypeName.Count) then begin # sListFields := sListFields + 'TransactionTypeID,'; # sListValues := sListValues + slTransactionTypeID[nTransactionType]+','; # end; # } # FieldStringList.Add(sListFields); # ValueStringList.Add(sListValues); # sListFields := ''; # sListValues := ''; # # if not VarIsNull(qryQuoteSystem['AmtExGST']) then begin # // curAmtExGST := curFormat(qryQuoteSystem['AmtExGST']); # curAmtExGST := qryQuoteSystem.FieldByName('AmtExGST').AsCurrency; # sListFields := sListFields + 'AmountExGST,'; // AmtExGST. # // sListValues := sListValues + Curr2Str(curAmtExGST)+','; # sListValues := sListValues + CurrToStr(curAmtExGST)+','; # lGSTFlag := (UpperCase(sShipTo) <> 'VRT'); # sListFields := sListFields + 'GSTFlag,'; # if (lGSTFlag) then begin # sListValues := sListValues + '''Y'','; # // curAmtGST := curFormat(curAmtExGST * fGSTRate); # curAmtGST := curAmtExGST * fGSTRate; # end # else begin # sListValues := sListValues + '''N'','; # curAmtGST := 0.00; # end; // if GSTFlag='Y' then begin. # sListFields := sListFields + 'GSTAmount,AmountIncGST,'; # // sListValues := sListValues + Curr2Str(curAmtGST)+','+ # // Curr2Str(curSum(curAmtExGST,curAmtGST))+','; # sListValues := sListValues + CurrToStr(curAmtGST)+','+ # CurrToStr(curAmtExGST + curAmtGST)+','; # end; // AmtExGST. # sListFields := sListFields + 'FromDate,'; # sListValues := sListValues +''''+ sFromDate +''','; # sListFields := sListFields + 'ToDate,'; # sListValues := sListValues +''''+ sToDate +''','; # if not VarIsNull(qryQuoteSystem['CallNumber']) then begin # sListFields := sListFields + 'Var01,'; // CallNumber. # sListValues := sListValues +''''+ # qryQuoteSystem.FieldByName('CallNumber').AsString +''','; # end; # if not VarIsNull(qryQuoteSystem['Supplier']) then begin # sListFields := sListFields + 'Var02,'; // Supplier. # sListValues := sListValues +''''+ # qryQuoteSystem.FieldByName('Supplier').AsString+''','; # end; # if not VarIsNull(qryQuoteSystem['BilledDate']) then begin # sListFields := sListFields + 'Var03,'; // BilledDate. # sListValues := sListValues +''''+ # StandardDateTimeString(qryQuoteSystem.FieldByName('BilledDate').AsDateTime)+ # ''','; # end; # if not VarIsNull(qryQuoteSystem['CustRef']) then begin # sListFields := sListFields + 'Var04,'; // CustRef. # sListValues := sListValues +''''+ # CleanString(qryQuoteSystem.FieldByName('CustRef').AsString) +''','; # end; # FieldStringList.Add(sListFields); # ValueStringList.Add(sListValues); # sListFields := ''; # sListValues := ''; # if not VarIsNull(qryQuoteSystem['Description']) then begin # sListFields := sListFields + 'Var05,'; // Description. # sListValues := sListValues +''''+ # CleanString(qryQuoteSystem.FieldByName('Description').AsString) +''','; # end; # if (Length(sShipTo) > 0) then begin # sListFields := sListFields + 'Var06,'; // ShipTo. # sListValues := sListValues +''''+ sShipTo +''','; # end; # if not VarIsNull(qryQuoteSystem['CustRef']) then begin # // Repeat the CustRef saved in Var04 into Var07 too - RLD 170120. # sListFields := sListFields + 'Var07,'; // CustRef. # sListValues := sListValues +''''+ # CleanString(qryQuoteSystem.FieldByName('CustRef').AsString) +''','; # end; # if not VarIsNull(qryQuoteSystem['ITHelpUser']) then begin # sListFields := sListFields + 'Var09,'; // ITHelpUser. # sListValues := sListValues +''''+ # qryQuoteSystem.FieldByName('ITHelpUser').AsString +''','; # end; # FieldStringList.Add(sListFields); # ValueStringList.Add(sListValues); # sListFields := ''; # sListValues := ''; # if not VarIsNull(qryQuoteSystem['Location']) then begin # sListFields := sListFields + 'Var10,'; // Location. # sListValues := sListValues +''''+ # CleanString(qryQuoteSystem.FieldByName('Location').AsString) +''','; # end; # if not VarIsNull(qryQuoteSystem['ProgOnly']) then begin # sListFields := sListFields + 'Var12,'; // ProgOnly. # sListValues := sListValues +''''+ # CleanString(qryQuoteSystem.FieldByName('ProgOnly').AsString) +''','; # end; # if not VarIsNull(qryQuoteSystem['ProgLocation']) then begin # sListFields := sListFields + 'Var13,'; // ProgLocation. # sListValues := sListValues +''''+ # CleanString(qryQuoteSystem.FieldByName('ProgLocation').AsString) +''','; # end; # if not VarIsNull(qryQuoteSystem['LastName']) then begin # sListFields := sListFields + 'Var14,'; // LastName. # sListValues := sListValues +''''+ # CleanString(qryQuoteSystem.FieldByName('LastName').AsString) +''','; # end; # if not VarIsNull(qryQuoteSystem['Bill']) then begin # sListFields := sListFields + 'Var15,'; // Bill. # sListValues := sListValues + IntToStr(qryQuoteSystem['Bill']) +','; # end; # sListFields := sListFields + 'BatchID)'; // BatchID. # sListValues := sListValues + sBatch +')'; # # // Now make the SQL statement to save to the database table. # FieldStringList.Add(sListFields); # ValueStringList.Add(sListValues); # qryStringList.Clear; # qryStringList.Add('INSERT INTO tDataCollection'); # for nListValues := 0 to FieldStringList.Count-1 do begin # qryStringList.Add(FieldStringList[nListValues]); # end; # for nListValues := 0 to ValueStringList.Count-1 do begin # qryStringList.Add(ValueStringList[nListValues]); # end; # // Update and execute the query. # qryDC.Close; # qryDC.SQL := qryStringList; # qryDC.ExecSQL; # # // Now update the values stored in the tBatch table. # with qryStringList do begin # Clear; # Add('UPDATE tBatch'); # Add('SET GSTAmount = BatchRate.TotalGST,'); # Add(' TotalPayable = BatchRate.TotalIncGST'); # Add('FROM (SELECT Sum(tDataCollection.AmountIncGST) '+ # 'AS TotalIncGST,'); # Add(' Sum(tDataCollection.GSTAmount) AS TotalGST'); # Add('FROM tDataCollection'); # Add('WHERE tDataCollection.BatchID = '+ sBatch + ') AS BatchRate'); # Add('WHERE tBatch.ID = '+ sBatch); # qryDC.ExecSQL; # UpdateBatchStatus(nBatchNumber, MainForm.BATCH_STATUS_IMPORT); COMMENTqryQuote0 exit 0