Set rsAmount = New ADODB.Recordset strsql1 = "SELECT ACCTNUM FROM TBLSELECTEDACCOUNTS WHERE USERID='" & strUserID & "' and Computer='" & strComputer & "'" rsAmount.Open strsql1, gCN, adOpenDynamic, adLockBatchOptimistic
If Not rsAmount.EOF Then For i = 1 To rsAmount.RecordCount
strSql2 = "SELECT tblrevenue.revc,TBLAGEBILL.Arrearage FROM TBLAGEBILL left outer join tblrevenue on tblrevenue.RevenueCodeID=TBLAGEBILL.RevenueCodeID WHERE TBLAGEBILL.ACCTNUM='" & rsAmount("ACCTNUM") & "' AND TBLAGEBILL.AGINGCYCLE='0' and convert(varchar(10),TBLAGEBILL.TransactionDate,121) ='" & Format(dtBillDate, "YYYY-MM-DD") & "'" RsDetial.Open strSql2, gCN, adOpenDynamic, adLockBatchOptimistic DETIAL = "" DETIAL1 = "" If Not RsDetial.EOF Then
For j = 1 To RsDetial.RecordCount DETIAL = DETIAL + str(RsDetial("Arrearage")) + Chr(13) DETIAL1 = DETIAL1 + RsDetial("revc") + ":" + Chr(13) RsDetial.MoveNext Next End If 'MsgBox (DETIAL) RsDetial.Close 'DETIAL = "7897987" strSql3 = "insert #TBLAMOUNTDETIAL(acctnum,AMOUNTDETIAL,AMOUNTDETIAL1) values('" & rsAmount("ACCTNUM") & "','" & DETIAL & "','" & DETIAL1 & "')" gCN.Execute strSql3 rsAmount.MoveNext Next End If rsAmount.Close
strSql = "SELECT " & vbCrLf
Dim Notice As String Dim DUEDATE1 As String DUEDATE1 = DueDate Notice = "DISCONNECT NOTICE If total amount is not received by " & Replace(Format(dtpDelinquentDueDate, "MM/DD/YYYY"), "-", "/") & " service to your property will be disconnected." Dim NOTICE1 As String NOTICE1 = "AVISO DE DESCONECCION Si el pago total no es recibido el " & Replace(Format(dtpDelinquentDueDate, "MM/DD/YYYY"), "-", "/") & " el servicio de su propiedad sera desconectado." strSql = strSql & " tblLastBillHistory.CustomerID,tblLastBillHistory.acctnum,tblLastBillHistory.SrvFrom, tblLastBillHistory.SrvTo,tblLastBillHistory.Days,tblLastBillHistory.totaldue, (tblLastBillHistory.totaldue-tblLastBillHistory.past1-tblLastBillHistory.past2-tblLastBillHistory.past3) as Currdue, '/U'+tblLastBillHistory.acctnum+'$' AS barcode ,CASE WHEN (tblLastBillHistory.past1+tblLastBillHistory.past2+tblLastBillHistory.past3-tblcompany.minshutAmount>=0) THEN'" & Notice & "' ELSE '' END AS DelinquentDueDate ,CASE WHEN (tblLastBillHistory.past1+tblLastBillHistory.past2+tblLastBillHistory.past3-tblcompany.minshutAmount>=0) THEN'" & NOTICE1 & "' ELSE '' END AS DelinquentDueDate1 , " & vbCrLf 'strSql = strSql & " tblcompany.minshutamount, e.FirstName, e.LastName, CASE When ISNULL(e.MI,'')='' THEN tblCustomer.MI ELSE e.MI END AS MI, " 'strSql = strSql & " tblcustomer.past1+tblcustomer.past2+tblcustomer.past3-tblcompany.minshutAmount as CheckDisConnect " & vbCrLf strSql = strSql & " (isnull(tblpremise.StrNum,'')+' '+isnull(tblStreetName.StreetName,'')+' '+isnull(tblpremise.city,'')+' '+isnull(tblpremise.state,'')+' '+isnull(tblpremise.zip4,0)+'+'+isnull(tblpremise.Zip5,0)) as address,tblMetrHist.PARTPEAKREAD,(tblMetrHist.PARTPEAKREAD-tblMetrHist.PartPeakUsage) as LastPartPeakRead ,case when tblMetrHist.Multiplier=0 then tblMetrHist.PartPeakUsage else tblMetrHist.PartPeakUsage*tblMetrHist.Multiplier END as cffused,E.AMOUNTDETIAL,E.AMOUNTDETIAL1,'" & DUEDATE1 & "' AS DueDate" & vbCrLf
strSql = strSql & "FROM tblLastBillHistory left outer JOIN tblcustomer on tblcustomer.customerid = tblLastBillHistory.customerid left outer JOIN" & vbCrLf strSql = strSql & " tblcompany ON tblcompany.companyID = tblcustomer.companyID left outer join #TBLAMOUNTDETIAL E ON E.ACCTNUM = tblLastBillHistory.ACCTNUM left outer join" & vbCrLf strSql = strSql & " tblpremise ON tblpremise.premiseID = tblCustomer.premiseID left outer join" & vbCrLf strSql = strSql & " tblStreetName ON tblStreetName.StreetCodeID = tblpremise.StreetCodeID left outer join" & vbCrLf strSql = strSql & " tblMetrHist ON tblMetrHist.acctnum = tblLastBillHistory.acctnum and convert(varchar(10),tblMetrHist.BillDt,121) ='" & Format(dtBillDate, "YYYY-MM-DD") & "'" & vbCrLf
gCN.Execute strSql3
Set rsAmount = New ADODB.Recordset
strsql1 = "SELECT ACCTNUM FROM TBLSELECTEDACCOUNTS WHERE USERID='" & strUserID & "' and Computer='" & strComputer & "'"
rsAmount.Open strsql1, gCN, adOpenDynamic, adLockBatchOptimistic
If Not rsAmount.EOF Then
For i = 1 To rsAmount.RecordCount
strSql2 = "SELECT tblrevenue.revc,TBLAGEBILL.Arrearage FROM TBLAGEBILL left outer join tblrevenue on tblrevenue.RevenueCodeID=TBLAGEBILL.RevenueCodeID WHERE TBLAGEBILL.ACCTNUM='" & rsAmount("ACCTNUM") & "' AND TBLAGEBILL.AGINGCYCLE='0' and convert(varchar(10),TBLAGEBILL.TransactionDate,121) ='" & Format(dtBillDate, "YYYY-MM-DD") & "'"
RsDetial.Open strSql2, gCN, adOpenDynamic, adLockBatchOptimistic
DETIAL = ""
DETIAL1 = ""
If Not RsDetial.EOF Then
For j = 1 To RsDetial.RecordCount
DETIAL = DETIAL + str(RsDetial("Arrearage")) + Chr(13)
DETIAL1 = DETIAL1 + RsDetial("revc") + ":" + Chr(13)
RsDetial.MoveNext
Next
End If
'MsgBox (DETIAL)
RsDetial.Close
'DETIAL = "7897987"
strSql3 = "insert #TBLAMOUNTDETIAL(acctnum,AMOUNTDETIAL,AMOUNTDETIAL1) values('" & rsAmount("ACCTNUM") & "','" & DETIAL & "','" & DETIAL1 & "')"
gCN.Execute strSql3
rsAmount.MoveNext
Next
End If
rsAmount.Close
strSql = "SELECT " & vbCrLf
Dim Notice As String
Dim DUEDATE1 As String
DUEDATE1 = DueDate
Notice = "DISCONNECT NOTICE If total amount is not received by " & Replace(Format(dtpDelinquentDueDate, "MM/DD/YYYY"), "-", "/") & " service to your property will be disconnected."
Dim NOTICE1 As String
NOTICE1 = "AVISO DE DESCONECCION Si el pago total no es recibido el " & Replace(Format(dtpDelinquentDueDate, "MM/DD/YYYY"), "-", "/") & " el servicio de su propiedad sera desconectado."
strSql = strSql & " tblLastBillHistory.CustomerID,tblLastBillHistory.acctnum,tblLastBillHistory.SrvFrom, tblLastBillHistory.SrvTo,tblLastBillHistory.Days,tblLastBillHistory.totaldue, (tblLastBillHistory.totaldue-tblLastBillHistory.past1-tblLastBillHistory.past2-tblLastBillHistory.past3) as Currdue, '/U'+tblLastBillHistory.acctnum+'$' AS barcode ,CASE WHEN (tblLastBillHistory.past1+tblLastBillHistory.past2+tblLastBillHistory.past3-tblcompany.minshutAmount>=0) THEN'" & Notice & "' ELSE '' END AS DelinquentDueDate ,CASE WHEN (tblLastBillHistory.past1+tblLastBillHistory.past2+tblLastBillHistory.past3-tblcompany.minshutAmount>=0) THEN'" & NOTICE1 & "' ELSE '' END AS DelinquentDueDate1 , " & vbCrLf
'strSql = strSql & " tblcompany.minshutamount, e.FirstName, e.LastName, CASE When ISNULL(e.MI,'')='' THEN tblCustomer.MI ELSE e.MI END AS MI, "
'strSql = strSql & " tblcustomer.past1+tblcustomer.past2+tblcustomer.past3-tblcompany.minshutAmount as CheckDisConnect " & vbCrLf
strSql = strSql & " (isnull(tblpremise.StrNum,'')+' '+isnull(tblStreetName.StreetName,'')+' '+isnull(tblpremise.city,'')+' '+isnull(tblpremise.state,'')+' '+isnull(tblpremise.zip4,0)+'+'+isnull(tblpremise.Zip5,0)) as address,tblMetrHist.PARTPEAKREAD,(tblMetrHist.PARTPEAKREAD-tblMetrHist.PartPeakUsage) as LastPartPeakRead ,case when tblMetrHist.Multiplier=0 then tblMetrHist.PartPeakUsage else tblMetrHist.PartPeakUsage*tblMetrHist.Multiplier END as cffused,E.AMOUNTDETIAL,E.AMOUNTDETIAL1,'" & DUEDATE1 & "' AS DueDate" & vbCrLf
strSql = strSql & "FROM tblLastBillHistory left outer JOIN tblcustomer on tblcustomer.customerid = tblLastBillHistory.customerid left outer JOIN" & vbCrLf
strSql = strSql & " tblcompany ON tblcompany.companyID = tblcustomer.companyID left outer join #TBLAMOUNTDETIAL E ON E.ACCTNUM = tblLastBillHistory.ACCTNUM left outer join" & vbCrLf
strSql = strSql & " tblpremise ON tblpremise.premiseID = tblCustomer.premiseID left outer join" & vbCrLf
strSql = strSql & " tblStreetName ON tblStreetName.StreetCodeID = tblpremise.StreetCodeID left outer join" & vbCrLf
strSql = strSql & " tblMetrHist ON tblMetrHist.acctnum = tblLastBillHistory.acctnum and convert(varchar(10),tblMetrHist.BillDt,121) ='" & Format(dtBillDate, "YYYY-MM-DD") & "'" & vbCrLf