#! /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.$$ <<SQL0
--# Determine the SystemID - used for searching the tTransasactionType table.
SELECT BatchTypeID
FROM tBatch
WHERE ID = $sBatchID;
SQL0
echo "Running..."
cat /tmp/s22.$$
echo "Result:"
cat /tmp/s22.$$ | tbssql | grep -v "using: sqsh"
echo ""

#
echo ""
echo "Get SystemID"
cat >/tmp/s22.$$ <<SQL1
SELECT ID
FROM tSystem
WHERE BatchTypeID = $sBatchTypeID
SQL1
echo "Running..."
cat /tmp/s22.$$
echo "Result:"
cat /tmp/s22.$$ | tbssql | grep -v "using: sqsh"
echo ""


echo ""
echo "Get TransactionGroupID"
cat <<COMMENTTR1
# PP GetQuoteJObs procedure uses first returned to get TransactionGroupID
# sTransactionTypeID=(1st from returned List)
# sTransactionGroupID=(1st from returned List )
COMMENTTR1
echo ""
cat >/tmp/s22.$$ <<SQL2
SELECT ID, Description, TransactionGroupID
FROM tTransactionType
WHERE SystemID = $sSystemID
  AND Active = 1
SQL2
echo "Running..."
cat /tmp/s22.$$
echo "Result:"
cat /tmp/s22.$$ | tbssql | grep -v "using: sqsh" | head -1
echo ""
#
#
# returns all these..
#71|60025 JOBS DATA&VOICE PABX LABOUR|3|
#72|60037 JOBS DATA&VOICE LABOUR|3|
#84|81003 VRT JOBS LABOUR DATA&VOICE|3|
#86|82528 VRT JOBS LABOUR CENTROL|3|
#87|81001 VRT JOBS LABOUR CUST SERV|3|
#89|84004 VRT JOBS LABOUR EXTERNAL PLANT|3|
#90|81507 VRT JOBS LABOUR DATA&VOICE PABX|3|
#91|81508 VRT JOBS LABOUR DATA&VOICE|3|
#123|60028 JOBS RADIO LABOUR|3|
#124|81503 VRT JOBS LABOUR RADIO|3|
#125|81502 VRT JOBS LABOUR EXTERNAL PLANT|3|
#128|60029 JOBS SALES CONNECTION FEE LABOUR|3|
#142|60009 JOBS DATA&VOICE LABOUR|3|
#143|60012 JOBS CENTROL LABOUR|3|
#144|60014 JOBS CUST SERV LABOUR|3|
#145|60017 JOBS EXTERNAL PLANT LABOUR|3|
#146|81516 VRT JOBS SALES CONNECTION FEE LABOUR|3|
#147|60035 JOBS EXTERNAL PLANT LABOUR|3|
#151|33002 VRT JOBS CAPITAL LABOUR DATA&VOICE|3|
#152|33002 VRT JOBS CAPITAL EXTERNAL PLANT|3|
#169|VRT 81001 VRT JOBS LABOUR CUST SERV|3|
#170|68503 PURCHASING NON VRT ASSET REVENUE|3|
#171|60029 PURCHASING SALES CONNECTION FEE|3|
#172|VRT 60037 JOBS DATA&VOICE LABOUR|3|
#173|60026 PURCHASING RADIO|3|
#174|60011 PURCHASING CUST SERV|3|
#175|60058 PURCHASING DTRS|3|
#176|68003 PURCHASING SALES GOVT CONTRIBUTION|3|
#177|JOBS INSTALL LABOUR|3|


##################################################################
##
## telmax user does not have access to VRTQuote or QuetzalSC
##
##  // 170416: ppTaken added to the BillJobs view by RLD.
##  with qryStringList do begin
##    Add('SELECT *');
##    Add('FROM BillJobs');
##    Add('WHERE BillJobs.BillPeriod LIKE '''+sBillPeriod+'''');
##    if lAnyTime then begin
##      Add('  AND (BillJobs.ppTaken IS NULL OR '+
##                 'BillJobs.ppTaken = 0)');
##    end;
##    Add('ORDER BY BillJobs.LogDateTime');
##  end;  // with qryStringList.
##qryQuoteSystem.SQL := qryStringList;
##
cat <<COMMENTQUOTE0

# the PP Quote Installations code then runs this SQL on VRTQuote syetm
#    SELECT *
#    FROM BillJobs
#    WHERE BillJobs.BillPeriod LIKE '$sBillPeriod'
#    --# if lAnyTime then begin
#        AND (BillJobs.ppTaken IS NULL OR BillJobs.ppTaken = 0)
#    --# endif
#    ORDER BY BillJobs.LogDateTime

COMMENTQUOTE0
#
##
## VRTQuote system from Telmax21
## for each returned Var01, ServiceTypeID from above
#sCallNumber="$Var01"
#sServiceTypeID="$ServiceTypeID"
#
##quoutesql <<SQLCMD1
#cat <<SQLCMD1
#      --#UPDATE Quote_Items
#      --#SET Quote_Items.PPTaken = 1
#SELECT *
#FROM Quote_Items
#      WHERE Quote_Items.BillPeriod LIKE '$Billperiod'
#--#      AND Quote_Items.BatchTypeID = $sBatchTypeID
#      --# This is actually where VRTquote is updated for each CallNumber.
#      sCallNumber := qry.FieldByName('Var01').AsString;
#--#      sServiceTypeID := qry.FieldByName('ServiceTypeID').AsString;
#      if (Length(sCallNumber) > 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 <<COMMENTQUOTE1

# Running the below SQL in SQLVER Management Studio on Citrix 
#    USE VRTQuote;
#    SELECT *
#    FROM BillJobs
#    WHERE BillJobs.BillPeriod LIKE '2024/02'
#    --# if lAnyTime then begin
#       AND (BillJobs.ppTaken IS NULL OR BillJobs.ppTaken = 0)
#    --# endif
#    ORDER BY BillJobs.LogDateTime
#
# Returns these (NO S000003377)...
#LogDateTime	BillPeriod	ServiceTypeID	CallNumber	Supplier	BilledDate	CustRef	Description	ShipTo	Location	ProgOnly	ProgLocation	Lastname	Bill	AmtExGST	Seqno	IthelpUser	PPTaken
#21/02/2024	2024/02	988	SO00003604	NULL	2024-02-21 10:01:13.187	30917	NULL	HOL	NULL	1.00	Functional Testing Certification and HMI upgrade x	Jess Mackie	1	18502.34	SO00003604	NULL	NULL
#21/02/2024	2024/02	988	SO00003510	NULL	2024-02-21 17:06:43.480	MTM157382	NULL	ZPF	NULL	1.00	MTM - Planned Power Outage Flinders Street - 4th F	Richard Ma	1	10545.61	SO00003510	NULL	NULL
#
# removing #   AND (BillJobs.ppTaken IS NULL OR BillJobs.ppTaken = 0)
# from the sql
# Returns 23 rows one of them beeing SO00003377 (Showing PPTaken set to 1)
#02/02/2024	2024/02	988	SO00003377	NULL	2024-02-02 15:54:26.833	76583	NULL	NNA	NULL	1.00	Termination of (D)4831 from Deer Pk to william st	Olga Votrubec	1	1384.00	SO00003377	NULL	1

COMMENTQUOTE1


cat <<COMMENTqryQuote0


# for each qryQuoteSystem BillJobs row
#

# The PP Quote Installations code then creats
# and executes Insert and update statments
# based on the  Value found in BillJobs.* and addition sql to tbsdata
#
# here is the code...
#
#  qryLook.SQL.Add('SELECT Code, Description');
#  qryLook.SQL.Add('FROM tServiceType');
#  qryLook.SQL.Add('WHERE ID =');
#
#  //********* START READING BILLS INTO 'tDataCollection' ***********
#
#       sListFields := '(';
#       sListValues := 'VALUES (';
#       sListFields := sListFields + 'SequenceNo,';
#       sListValues := sListValues + '''' +
#         qryQuoteSystem.FieldByName('SeqNo').AsString +''',';
#       // NB ShipTo (following) needed NOW for the default values.
#       sShipTo := Trim(UpperCase(qryQuoteSystem.FieldByName('ShipTo').AsString));
#       if VarIsNull(qryQuoteSystem['ServiceTypeID']) then begin
#         sServiceTypeID := '';          // Forces the next entry to reset values.
#         if (sShipTo = 'VRT') then begin
#           sCode := 'JI1';              // Default.
#           sDescription := 'JOBS LABOUR INSTALL';
#         end
#         else begin
#           sCode := 'JIL';              // Default.
#           sDescription := 'JOBS INSTALL LABOUR';
#         end;
#         // Look up the code in the tServiceType table using description.
#         qryLook.Close;
#         qryLook.SQL.Strings[2] := 'WHERE Description LIKE '+
#           ''''+ sDescription +'%''';
#         qryLook.Open;
#         if not qryLook.Eof then begin
#           sCode := qryLook.FieldByName('Code').AsString;
#         end;
#       end
#       else begin
#         // Reset the values only if the contents of sServiceTypeID have changed.
#         if (sServiceTypeID <> 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