[smbzleon]

I have 2 string variables
subpaymentref
finalpaymentref

if the initial value of dr("tran_particular")='BCL/BC/NWSC/NWSC/18-May-2012/000001'
subpaymentref = dr("tran_particular")
finalpaymentref = subpaymentref.Substring(13, 50)

i have tried to use the substring function to format subpaymentref variable to produce a formatted string as below

'NWSC/18-May-2012/000001' but the final value of finalpaymentref is still 'BCL/BC/NWSC/NWSC/18-May-2012/000001'

Is the subtring function wrong?

Attached File(s)

•  str code.txt (2.52K)
  Number of downloads: 22

[deanobravo]

worked fine for me except had to change

finalpaymentref = subpaymentref.Substring(13, 50)

to

finalpaymentref = subpaymentref.Substring(12, 23)

[smbzleon]

I tried finalpaymentref = subpaymentref.Substring(12, 23) but the isssue is that it
keeps on returning back the original string, meaning that it does not format subpaymentref at all.
could there be something wrong with the values being read from the datareader?

Also , the month in the final string keeps on changing suc as January and May are not the same

[smbzleon]

am still getting the original string, is there any other way i can format that string

[CharlieMay]

Is there always 6 elements separated by / ?

If so you could split the string by the "/" and then rebuild on the last 3 elements of the split.

Also for variable length strings, you can still achieve the result for length in a substring by taking the string length - the starting index.

How about showing us the code leading up to this so that we can make sure you haven't made a mistake that is causing something to be changed after the substring is performed.

What you are showing and what you are telling us is happening doesn't add up so post the code that's causing these results.

[smbzleon]

    Sub spooleverydayStatement()
        Dim valresponsenwsc As String = Nothing

        valresponsenwsc = validatetodaydate2()

        If valresponsenwsc = "No" Then
            'Don't proceed, just exit the sub
            Exit Sub

        Else
            'You can now run the script to generate the etax running balance statement file
            Try
                Dim com As New SqlConnection(AppSettings("connString"))
                Dim comm As New SqlConnection(AppSettings("connString"))
                Dim comc As New SqlConnection(AppSettings("connString"))
                'Dim com1 As New SqlCommand("SELECT ITEMVALUE FROM PARAMETERS WHERE ITEMKEY='AFRICASH_ACC'", com)
                Dim com3 As New SqlCommand("Add_nwscbankstatement_temp", com)
                Dim com4 As New SqlCommand("Add_nwscrunbal", com)
                Dim com1 As New SqlCommand("SELECT ITEMVALUE FROM PARAMETERS WHERE ITEMKEY='WATER_ACC'", com)
                Dim com5 As New SqlCommand("SELECT run_bal FROM nwsc_running_balances", com)
                Dim com6 As New SqlCommand("Add_nwscbankstatement", com)
                Dim com71 As New SqlCommand("update_nwscrun_bal", com)
                Dim com61 As New SqlCommand("Add_nwscbankstatement", comm)
                Dim payment_ref As String = Nothing
                com.Open()
                msg = "Opening up of SQL DB connection has been set successfully"
                logMessage(msg, 0, "Info", "None", "All", "None", 0)
                EventLog1.WriteEntry("Opening up of SQL DB Connection has been set successfully", EventLogEntryType.Information)

                Dim strNWSCAccount As String = com1.ExecuteScalar & ""
                'Dim oconn As New OleDb.OleDbConnection("")
                'Dim Connstring As String = "data source =( DESCRIPTION =(ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.100.4.92)(PORT = 1521)) ) (CONNECT_DATA = (SID = ubaug) (SERVER = DEDICATED)));User ID=chk;Password = viewing;Unicode = True;provider=msdaora"
                ''"data source =( DESCRIPTION =(ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.100.4.92)(PORT = 1521)) ) (CONNECT_DATA = (SID = ubaug) (SERVER = DEDICATED)));User ID=chk;Password = viewing;Unicode = True;provider=msdaora"/>
                ''"data source =( DESCRIPTION =(ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.100.4.92)(PORT = 1521)) ) (CONNECT_DATA = (SID = ubaug) (SERVER = DEDICATED)));User ID=chk;Password = viewing;Unicode = True;provider=msdaora"/>
                ''Dim Connstring As String = AppSettings("OracleconnString") ' "data source =( DESCRIPTION =(ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.100.4.92)(PORT = 1521)) ) (CONNECT_DATA = (SID = ubaug) (SERVER = DEDICATED)));User ID=chk;Password = viewing;Unicode = True;provider=msdaora"
                'oconn.ConnectionString = Connstring
                'oconn.Open()
                Dim oconn As New OleDb.OleDbConnection()
                Dim Connstring As String = AppSettings("OracleconnString") ' "data source =( DESCRIPTION =(ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.100.4.92)(PORT = 1521)) ) (CONNECT_DATA = (SID = ubaug) (SERVER = DEDICATED)));User ID=chk;Password = viewing;Unicode = True;provider=msdaora"
                'Dim Connstring As String = "data source =( DESCRIPTION =(ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.100.4.92)(PORT = 1521)) ) (CONNECT_DATA = (SID = ubaug) (SERVER = DEDICATED)));User ID=chk;Password = viewing;Unicode = True;provider=msdaora"
                oconn.ConnectionString = Connstring
                'oconn.ConnectionString = "File Name=D:\Helpdesk\Application\finc.udl"
                oconn.Open()
                msg = "Opening up of Oracle DB connection has been set successfully"
                logMessage(msg, 0, "Info", "None", "All", "None", 0)
                EventLog1.WriteEntry("Opening up of Oracle DB connection has been set  successfully", EventLogEntryType.Information)

                'oconn.ConnectionTimeout = 3600
                'Dim prcDate As DateTime = dat.ProcessingDate(0)

                msg = "Ready to spool data"
                logMessage(msg, 0, "Info", "None", "All", "None", 0)

                'Dim prcDate As DateTime = DateTime.Now.AddDays(-62)
                Dim prcDate As DateTime = DateTime.Now.AddDays(-intervaldigit)
                Dim prcDate1 As DateTime = DateTime.Now.AddDays(-intervaldigit)

                'Dim prcDate As DateTime = dat.ProcessingDate(0)
                'Dim stract As String = "select foracid,trim(tran_id),gam.acct_crncy_code,sum(htd.tran_amt * decode(htd.part_tran_type,'D',1,0)), " & _
                '"sum(htd.tran_amt * decode(htd.part_tran_type,'C',1,0)),tran_date,value_date,htd.rcre_time,htd.part_tran_type,tran_particular,eab.tran_date_bal " & _
                '"from gam,htd,eab " & _
                '"where gam.acid=htd.acid " & _
                '"and gam.acid=eab.acid " & _
                '"and gam.foracid='" & strURAAccount & "' " & _
                '"and tran_date between '" & Format(prcDate, "dd MMM yyyy") & "' and '" & Format(prcDate, "dd MMM yyyy") & "'" & _
                '"and eab.eod_date = (select max(i.eod_date) from eab i where eab.acid=i.acid and i.eod_date<='" & Format(prcDate, "dd MMM yyyy") & "') " & _
                '"and htd.del_flg!='Y'" & _
                '"group by foracid,trim(tran_id),gam.acct_crncy_code,tran_date,value_date,htd.rcre_time,htd.part_tran_type,tran_particular,eab.tran_date_bal  order by htd.rcre_time"


                'Dim stract As String = "select foracid ,substr(acct_name,1,35) ,trim(htd.tran_id) , " & _
                '         "gam.acct_crncy_code , " & _
                '         "sum(htd.tran_amt * decode(htd.part_tran_type,'D',1,0)),sum(htd.tran_amt * decode(htd.part_tran_type,'C',1,0)) ,htd.tran_date , " & _
                '         "htd.value_date,htd.rcre_time,htd.gl_sub_head_code ,schm_code,htd.part_tran_type,substr(tran_particular,13,50) , " & _
                '         "tran_particular , " & _
                '         "tran_rmks ,entry_user_Id ,pstd_user_id,eab.tran_date_bal " & _
                '         "from gam,htd,eab " & _
                '         "where gam.acid=htd.acid " & _
                '         "and gam.acid=eab.acid " & _
                '         "and gam.foracid='" & strNWSCAccount & "' " & _
                '         "and tran_date between '" & Format(prcDate, "dd MMM yyyy") & "' and '" & Format(prcDate1, "dd MMM yyyy") & "'" & _
                '         "and eab.eod_date = (select max(i.eod_date) from eab i where eab.acid=i.acid and i.eod_date<='" & Format(prcDate1, "dd MMM yyyy") & "') " & _
                '         "and htd.del_flg!='Y' " & _
                '         "group by foracid,substr(acct_name,1,35),trim(tran_id),gam.acct_crncy_code,tran_date,value_date,htd.rcre_time,htd.part_tran_type,htd.gl_sub_head_code ,schm_code ,substr(tran_particular,3,14),tran_particular,tran_rmks ,entry_user_Id ,pstd_user_id,eab.tran_date_bal  order by htd.rcre_time"


                Dim stract As String = "select foracid ,substr(acct_name,1,35) ,trim(tran_id) , " & _
                          "gam.acct_crncy_code , " & _
                          "sum(htd.tran_amt * decode(htd.part_tran_type,'D',1,0)),sum(htd.tran_amt * decode(htd.part_tran_type,'C',1,0)) ,htd.tran_date , " & _
                          "htd.value_date,htd.rcre_time,htd.gl_sub_head_code ,schm_code,htd.part_tran_type,substr(tran_particular,13,50) , " & _
                          "tran_particular , " & _
                          "tran_rmks ,entry_user_Id ,pstd_user_id,eab.tran_date_bal " & _
                          "from gam,htd,eab " & _
                          "where gam.acid=htd.acid " & _
                          "and gam.acid=eab.acid " & _
                          "and gam.foracid='" & strNWSCAccount & "' " & _
                          "and value_date between '" & Format(prcDate, "dd MMM yyyy") & "' and '" & Format(prcDate1, "dd MMM yyyy") & "'" & _
                          "and eab.eod_date = (select max(i.eod_date) from eab i where eab.acid=i.acid and i.eod_date<='" & Format(prcDate1, "dd MMM yyyy") & "') " & _
                          "and htd.del_flg!='Y' " & _
                          "group by foracid,substr(acct_name,1,35),trim(tran_id),gam.acct_crncy_code,tran_date,value_date,htd.rcre_time,htd.part_tran_type,htd.gl_sub_head_code ,schm_code ,substr(tran_particular,13,50),tran_particular,tran_rmks ,entry_user_Id ,pstd_user_id,eab.tran_date_bal  order by htd.rcre_time"


                '"group by  foracid,substr(acct_name,1,35),trim(htd.tran_id),htd.tran_date,htd.gl_sub_head_code,schm_code,substr(tran_particular,3,14),htd.part_tran_type,tran_particular,tran_rmks,entry_user_id,pstd_user_id"
                '"and tran_date between '" & Format(prcDate, "dd MMM yyyy") & "' and '" & Format(prcDate1, "dd MMM yyyy") & "'" & _

                Dim com2 As New OleDb.OleDbCommand(stract, oconn)
                Dim dr As OleDb.OleDbDataReader = com2.ExecuteReader
                Dim Sqlcom As New SqlCommand("", com)
                Dim Sqldr As SqlDataReader ' = Sqlcom.ExecuteReader
                'Dim com5 As New SqlCommand("", com)
                Dim Sqlcom2 As New SqlCommand("", com)
                Dim Sqlcomcollect As New SqlCommand("", com)
                Dim Sqldr2 As SqlDataReader ' = Sqlcom.ExecuteReader
                Dim Sqldrcollect As SqlDataReader ' = Sqlcom.ExecuteReader
                'Dim Sqlcom24 As New SqlCommand("", com)
                'Dim Sqldr24 As SqlDataReader ' = Sqlcom.ExecuteReader
                Dim Sqlcom3 As New SqlCommand("", com)
                Dim Sqldr3 As SqlDataReader ' = Sqlcom.ExecuteReader
                ' "and tran_particular like '%NWSC%' " & _
                Try
                    msg = "Established connection to Finacle and spooled data" & stract
                    logMessage(msg, 0, "Info", "None", "All", "None", 0)
                    ' EventLog1.WriteEntry("Established connection to Finnacle and spooleddata", EventLogEntryType.Information)

                Catch ex As Exception
                    msg = "Error connecting to core banking" & stract '"Established connection to Finnacle and spooled data"
                    logMessage(msg, 0, "Error", "None", "All", "None", 0)

                    'EventLog1.WriteEntry("Error connecting to core banking" & ex.Message, EventLogEntryType.Error)

                End Try
                Dim itemType As String = ""
                Dim StrRecs As String = ""
                Dim StrRecs2 As String = ""
                Dim StrRecscollect As String = ""
                Dim StrRecs24 As String = ""
                Dim StrRecs3 As String = ""
                Dim StrRecs4 As String = ""
                Dim StrRecs5 As String = ""
                Dim StrRecs6 As String = ""
                Dim StrRecs7 As String = ""
                Dim StrRecs8 As String = ""
                Dim finalpaymentref As String = ""
                Dim subpaymentref As String = ""
                Dim amount, amountcredit, amountdebit, ACB, totalDebit, totalCredit, BBF, amount2, amount3, runbal, runbalnow, closed_bal, runopen, totalDebit2, totalDebit3, totalCredit2, totalCredit3 As Double
                'CLEAR ANY PREVIOUS TRANSACTIONS FIRST
                com1.CommandText = "DELETE FROM nwscBANKSTATEMENT_TEMP WHERE GENERATED=0"
                com1.ExecuteNonQuery()
                msg = "Deleted old data from nwscbankstatement_temp table successfully"
                logMessage(msg, 0, "Info", "None", "All", "None", 0)
                EventLog1.WriteEntry("Finished deleting old data from nwscBankstatement_temp table", EventLogEntryType.Information)

                If dr.HasRows Then
                   
                    Do While dr.Read
                        
                        If dr("part_tran_type") = "D" And dr("tran_particular") Like "BCL/BC/NWSC/NWSC/%" Then
                            
                            amountdebit = dr(4)
                            amount = amountdebit
                            itemType = "DEBIT"
                            'subpaymentref = dr("tran_particular")[10:28:50 PM] nyake Mbondji: finalpaymentref = subpaymentref.substring(13,subpaymentref.length - 13)
                            subpaymentref = Convert.ToString(dr(13))
                            finalpaymentref = subpaymentref.Substring(13, subpaymentref.Length - 13) 'subpaymentref.Substring(12)
                            'finalpaymentref = Mid(subpaymentref, 13, 50)
                            finalpaymentref = finalpaymentref.Replace("/", "|")
                            totalDebit = totalDebit + amountdebit
                            msg = "computed" & subpaymentref & finalpaymentref '"Established connection to Finnacle and spooled data"
                            logMessage(msg, 0, "Notice", "None", "All", "None", 0)
                            EventLog1.WriteEntry("computed" & subpaymentref & finalpaymentref, EventLogEntryType.Information)

                        ElseIf dr("part_tran_type") = "D" And dr("tran_particular") Like "/BC/NWSC/NWSC/%" Then
                            amountdebit = dr(4)
                            amount = amountdebit
                            itemType = "DEBIT"
                            'subpaymentref = dr("tran_particular")
                            subpaymentref = Convert.ToString(dr(13))
                            finalpaymentref = finalpaymentref = subpaymentref.Substring(10, subpaymentref.Length - 10) 'subpaymentref.Substring(9)
                            'finalpaymentref = Mid(subpaymentref, 10, 50)
                            finalpaymentref = finalpaymentref.Replace("/", "|")
                            totalDebit2 = totalDebit2 + amountdebit
                            msg = "computed" & subpaymentref & finalpaymentref '"Established connection to Finnacle and spooled data"
                            logMessage(msg, 0, "Notice", "None", "All", "None", 0)
                            EventLog1.WriteEntry("computed" & subpaymentref & finalpaymentref, EventLogEntryType.Information)


                        ElseIf dr("part_tran_type") = "D" And (dr("tran_particular") Like "BCL/BC/NWSC/NWSC/%") = False And (dr("tran_particular") Like "/BC/NWSC/NWSC/%") = False Then
                            amountdebit = dr(4)
                            amount = amountdebit
                            itemType = "DEBIT"
                            'subpaymentref = dr("tran_particular")
                            subpaymentref = Convert.ToString(dr("tran_particular"))
                            finalpaymentref = subpaymentref
                            finalpaymentref = finalpaymentref.Replace("/", "|")
                            totalDebit3 = totalDebit3 + amountdebit
                            msg = "computed" & subpaymentref & finalpaymentref '"Established connection to Finnacle and spooled data"
                            logMessage(msg, 0, "Notice", "None", "All", "None", 0)
                            EventLog1.WriteEntry("computed" & subpaymentref & finalpaymentref, EventLogEntryType.Information)

                        ElseIf dr("part_tran_type") = "C" And dr("tran_particular") Like "BCL/BC/NWSC/NWSC/%" Then
                            amountcredit = dr(5)
                            amount = amountcredit
                            itemType = "CREDIT"
                            'subpaymentref = dr("tran_particular")
                            subpaymentref = Convert.ToString(dr(13))
                            finalpaymentref = finalpaymentref = subpaymentref.Substring(13, subpaymentref.Length - 13) 'subpaymentref.Substring(12)
                            'finalpaymentref = Mid(subpaymentref, 13, 50)
                            finalpaymentref = finalpaymentref.Replace("/", "|")
                            totalCredit = totalCredit + amountcredit
                            msg = "computed" & subpaymentref & finalpaymentref '"Established connection to Finnacle and spooled data"
                            logMessage(msg, 0, "Notice", "None", "All", "None", 0)
                            EventLog1.WriteEntry("computed" & subpaymentref & finalpaymentref, EventLogEntryType.Information)

                        ElseIf dr("part_tran_type") = "C" And dr("tran_particular") Like "/BC/NWSC/NWSC/%" Then
                            amountcredit = dr(5)
                            amount = amountcredit
                            itemType = "CREDIT"
                            'subpaymentref = dr("tran_particular")
                            subpaymentref = Convert.ToString(dr(13))
                            finalpaymentref = finalpaymentref = subpaymentref.Substring(10, subpaymentref.Length - 10) 'subpaymentref.Substring(9)
                            'finalpaymentref = Mid(subpaymentref, 10, 50)
                            finalpaymentref = finalpaymentref.Replace("/", "|")
                            totalCredit2 = totalCredit2 + amountcredit
                            msg = "computed" & subpaymentref & finalpaymentref '"Established connection to Finnacle and spooled data"
                            logMessage(msg, 0, "Notice", "None", "All", "None", 0)
                            EventLog1.WriteEntry("computed" & subpaymentref & finalpaymentref, EventLogEntryType.Information)

                        ElseIf dr("part_tran_type") = "C" And (dr("tran_particular") Like "BCL/BC/NWSC/NWSC/%") = False And (dr("tran_particular") Like "/BC/NWSC/NWSC/%") = False Then
                            amountcredit = dr(5)
                            amount = amountcredit
                            itemType = "CREDIT"
                            'subpaymentref = dr("tran_particular")
                            subpaymentref = Convert.ToString(dr("tran_particular"))
                            finalpaymentref = subpaymentref
                            finalpaymentref = finalpaymentref.Replace("/", "|")
                            totalCredit3 = totalCredit3 + amountcredit
                            msg = "computed" & subpaymentref & finalpaymentref '"Established connection to Finnacle and spooled data"
                            logMessage(msg, 0, "Notice", "None", "All", "None", 0)
                            EventLog1.WriteEntry("computed" & subpaymentref & finalpaymentref, EventLogEntryType.Information)

                        End If
                        'validate if the record has been inserted
                        msg = "validation if the record has been inserted has been started successfully"
                        logMessage(msg, 0, "Info", "None", "All", "None", 0)
                        EventLog1.WriteEntry("Started the validation if a record has already been inserted", EventLogEntryType.Information)

                        'StrRecs = "SELECT * FROM nwscBANKSTATEMENT_TEMP WHERE sub_particular='" & dr("substr(tran_particular,3,14)") & "'"
                        'StrRecs = "SELECT * FROM nwscBANKSTATEMENT_TEMP WHERE sub_particular='" & dr(2) & "'"
                        StrRecs = "SELECT * FROM nwscBANKSTATEMENT_TEMP WHERE sub_particular='" & finalpaymentref & "'"
                        Sqlcom.CommandText = StrRecs
                        Sqldr = Sqlcom.ExecuteReader
                        If Sqldr.HasRows Then
                            'dont insert recs
                            msg = "Do not insert has records already exists in the bankstatement_temp table"
                            logMessage(msg, 0, "Info", "None", "All", "None", 0)
                            EventLog1.WriteEntry("Start inserting data into the nwscbankstatement_temp table", EventLogEntryType.Information)
                            checklogs()
                        Else
                            msg = "Start inserting data into the nwscbankstatement_temp table"
                            logMessage(msg, 0, "Info", "None", "All", "None", 0)
                            EventLog1.WriteEntry("Started inserting data into the nwscbanksatemnt_temp table", EventLogEntryType.Information)

                            'stract = "INSERT INTO BANKSTATEMENT_TEMP(ITEM_TYPE,BENE_ACCOUNT,STATEMENT_DATE,VALUE_DATE,AMOUNT,ENTRY_TYPE,PRN,OUR_REF,USER_REF,DR_REF,ACTION_ONENTRY)VALUES(" & _
                            '"'" & itemType & "','" & dr("FORACID") & "','" & dr("tran_date") & "','" & dr("value_date") & "'," & amount & ",'" & dr("part_tran_type") & "R','" & dr("tran_particular") & "','" & dr(1) & "','','" & dr("tran_date_bal") & "','CLEANED')"
                            'com1.CommandText = stract
                            'com1.ExecuteNonQuery()
                            'msg = "Script going to start replacing the unwanted xter / with |"
                            'logMessage(msg, 0, "Info", "None", "All", "None", 0)

                            'payment_ref = IIf(IsDBNull(dr("substr(tran_particular,13,50)")) = True, "", dr("substr(tran_particular,13,50)")) 'dr("substr(tran_particular,10,50)") 'dr("payment_ref")

                            'payment_ref = payment_ref.Replace("/", "|")


                            com3.CommandType = Data.CommandType.StoredProcedure
                            com3.Parameters.Clear()
                            com3.Parameters.Add("@ITEM_TYPE", Data.SqlDbType.VarChar, 50).Value = itemType '" & itemType & " 'txtusername.Text.Trim.ToLower
                            com3.Parameters.Add("@foracid", Data.SqlDbType.VarChar, 50).Value = dr("FORACID") 'txtfullname.Text
                            com3.Parameters.Add("@acct_name", Data.SqlDbType.VarChar, 50).Value = IIf(IsDBNull(dr("substr(acct_name,1,35)")) = True, "", dr("substr(acct_name,1,35)")) 'dr("substr(acct_name,1,35)") 'txtfullname.Text
                            com3.Parameters.Add("@tran_id", Data.SqlDbType.VarChar, 50).Value = IIf(IsDBNull(dr("trim(tran_id)")) = True, "", dr("trim(tran_id)")) 'dr("trim(tran_id)") 'FormatDateTime(dr("tran_date"), DateFormat.ShortDate)
                            com3.Parameters.Add("@crncy_code", Data.SqlDbType.VarChar, 50).Value = IIf(IsDBNull(dr("acct_crncy_code")) = True, "", dr("acct_crncy_code")) 'dr("acct_crncy_code") 'Val(amount2) 'FormatDateTime(dr("tran_date"), DateFormat.ShortDate) 'CDate(txtChangedate.Text) 'DateAdd(Day" & DateDiff(DateInterval.Day, dt, DateTime.Now & ", " & DateTime.Now & ")
                            com3.Parameters.Add("@debits", Data.SqlDbType.Decimal, 18).Value = Val(dr(4)) 'Val("sum(htd.tran_amt * decode(htd.part_tran_type,'D',1,0))") 'dr("part_tran_type") & "R"
                            com3.Parameters.Add("@credits", Data.SqlDbType.Decimal, 18).Value = Val(dr(5)) 'Val("sum(htd.tran_amt * decode(htd.part_tran_type,'C',1,0))") 'dr("tran_particular") 'Val(dr(3))  'txtExpirydays.Text
                            com3.Parameters.Add("@amount", Data.SqlDbType.Decimal, 18).Value = Val(amount) 'dr("tran_particular") 'Val(dr(3))  'txtExpirydays.Text
                            com3.Parameters.Add("@tran_date", Data.SqlDbType.VarChar, 50).Value = FormatDateTime(dr("tran_date"), DateFormat.ShortDate) 'dr("trim(tran_id)")
                            com3.Parameters.Add("@value_date", Data.SqlDbType.VarChar, 50).Value = FormatDateTime(dr("value_date"), DateFormat.ShortDate) '"" 'FormatDateTime(dr(5), DateFormat.ShortDate)
                            com3.Parameters.Add("@glshc", Data.SqlDbType.VarChar, 50).Value = IIf(IsDBNull(dr(9)) = True, "", dr(9)) 'dr(9) 'dr("htd.gl_sub_head_code") 'dr("tran_date_bal")
                            com3.Parameters.Add("@scheme_code", Data.SqlDbType.VarChar, 50).Value = IIf(IsDBNull(dr("schm_code")) = True, "", dr("schm_code")) 'dr("schm_code") 'FormatDateTime(dr("value_date"), DateFormat.ShortDate) 'IIf(chkSystemAdmin.Checked = True, 1, 0)
                            com3.Parameters.Add("@part_tran_type", Data.SqlDbType.VarChar, 50).Value = IIf(IsDBNull(dr("part_tran_type")) = True, "", dr("part_tran_type")) 'dr("part_tran_type") '& "R"'"CLEANED" 'IIf(chkhaschanged.Checked = True, 1, 0)
                            'com3.Parameters.Add("@sub_particular", Data.SqlDbType.VarChar, 50).Value = dr("substr(tran_particular,10,50)")
                            com3.Parameters.Add("@sub_particular", Data.SqlDbType.VarChar, 50).Value = finalpaymentref 'payment_ref  'IIf(IsDBNull(dr("payment_ref")) = True, "", dr("payment_ref")) 'payment_ref 'dr("substr(tran_particular,10,50)")
                            com3.Parameters.Add("@particulars", Data.SqlDbType.VarChar, 50).Value = IIf(IsDBNull(dr("tran_particular")) = True, "", dr("tran_particular")) 'dr("tran_particular")
                            com3.Parameters.Add("@remarks", Data.SqlDbType.VarChar, 50).Value = IIf(IsDBNull(dr("tran_rmks")) = True, "", dr("tran_rmks")) 'dr("tran_rmks") '"" 'FormatDateTime(dr("tran_date_bal"), DateFormat.ShortDate)
                            com3.Parameters.Add("@entry_id", Data.SqlDbType.VarChar, 50).Value = IIf(IsDBNull(dr("entry_user_Id")) = True, "", dr("entry_user_Id")) 'dr("entry_user_Id")
                            com3.Parameters.Add("@pstd_id", Data.SqlDbType.VarChar, 50).Value = IIf(IsDBNull(dr("pstd_user_id")) = True, "", dr("pstd_user_id")) 'dr("pstd_user_id")
                            'IIf(IsDBNull(Sqldr2("sub_particular")) = True, "ABCD", Sqldr2("sub_particular"))
                            'com3.Parameters.Add("@generated", Data.SqlDbType.Int, 4).Value = 0 'dr(10)
                            'com3.Parameters.Add("@run_code", Data.SqlDbType.Int, 4).Value = 0 'dr(11) 'Date.Now 'CDate(Session("MyLastLogin")) 'MyLastLoginDate 
                            'com3.Parameters.Add("@SOLID", Data.SqlDbType.VarChar, 50).Value = cmbbranch.SelectedValue
                            'com3.Parameters.Add("@Datecreated", Data.SqlDbType.VarChar, 50).Value = Date.Now
                            'com3.Parameters.Add("@Empno", Data.SqlDbType.VarChar, 50).Value = txtempid.Text
                            'com3.Parameters.Add("@Email", Data.SqlDbType.VarChar, 50).Value = txtemail.Text.Trim.ToLower
                            'Dim stract As String = "select foracid ,substr(acct_name,1,35) ,trim(htd.tran_id) , " & _
                            '"gam.acct_crncy_code , " & _
                            '"sum(htd.tran_amt * decode(htd.part_tran_type,'D',1,0)),sum(htd.tran_amt * decode(htd.part_tran_type,'C',1,0)) ,htd.tran_date , " & _
                            '"htd.value_date,htd.rcre_time,htd.gl_sub_head_code ,schm_code,htd.part_tran_type,substr(tran_particular,3,14) , " & _
                            '"tran_particular , " & _
                            '"tran_rmks ,entry_user_Id ,pstd_user_id,eab.tran_date_bal " & _
                            com3.ExecuteNonQuery()
                        End If
                        Sqldr.Close()
                        'ACB = Val(dr("eab.tran_date_bal"))
                        ACB = Val(dr("tran_date_bal"))
                        'msg = "Account closing balance has been set successfully to" & ACB
                        'logMessage(msg, 0, "Info", "None", "All", "None", 0)
                        'EventLog1.WriteEntry("ACB has been set successfully for NWSC running balances statement", EventLogEntryType.Information)
                        checklogs()
                    Loop
                    'Now compute balances
                    'THIS IS THE CLOSING BALANCE
                    'msg = "Account closing balance has been set successfully to" & ACB
                    'logMessage(msg, 0, "Info", "None", "All", "None", 0)
                    'EventLog1.WriteEntry("ACB has been set successfully for NWSC running balances statement", EventLogEntryType.Information)

                    'StrRecs = "SELECT * FROM BANKSTATEMENT WHERE ITEM_TYPE='ACB' and AMOUNT=" & ACB & ""
                    'Sqlcom.CommandText = StrRecs
                    'Sqldr = Sqlcom.ExecuteReader
                    'If Sqldr.HasRows = True Then
                    '    'DONT INSERT
                    'Else
                    'stract = "INSERT INTO BANKSTATEMENT_TEMP(ITEM_TYPE,BENE_ACCOUNT,STATEMENT_DATE,VALUE_DATE,AMOUNT,ENTRY_TYPE,PRN,OUR_REF,USER_REF,DR_REF,ACTION_ONENTRY)VALUES(" & _
                    '    "'ACB','" & strURAAccount & "','" & Format(prcDate, "dd/MM/yyyy") & "','" & Format(prcDate, "dd/MM/yyyy") & "'," & ACB & ",'CR','ACL " & Format(prcDate, "ddMMyy") & "','1','','','CLEANED')"
                    ''End If
                    'com1.CommandText = stract
                    'com1.ExecuteNonQuery()
                    'ACB = Val(dr("tran_date_bal"))
                    msg = "Account closing balance has been set successfully to" & ACB
                    logMessage(msg, 0, "Info", "None", "All", "None", 0)
                    EventLog1.WriteEntry("ACB has been set successfully for NWSC running balances statement", EventLogEntryType.Information)


                    com3.CommandType = Data.CommandType.StoredProcedure
                    com3.Parameters.Clear()
                    'com3.Parameters.Add("@ITEM_TYPE", Data.SqlDbType.VarChar, 50).Value = "ACB" '" & itemType & " 'txtusername.Text.Trim.ToLower
                    'com3.Parameters.Add("@BENE_ACCOUNT", Data.SqlDbType.VarChar, 50).Value = strNWSCAccount 'dr("FORACID") 'txtfullname.Text
                    'com3.Parameters.Add("@STATEMENT_DATE", Data.SqlDbType.VarChar, 50).Value = FormatDateTime(prcDate, DateFormat.ShortDate)
                    'com3.Parameters.Add("@AMOUNT", Data.SqlDbType.Decimal, 18).Value = Val(ACB) 'FormatDateTime(dr("tran_date"), DateFormat.ShortDate) 'CDate(txtChangedate.Text) 'DateAdd(Day" & DateDiff(DateInterval.Day, dt, DateTime.Now & ", " & DateTime.Now & ")
                    'com3.Parameters.Add("@ENTRY_TYPE", Data.SqlDbType.VarChar, 50).Value = "CR" 'dr("part_tran_type") & "R"
                    'com3.Parameters.Add("@PRN", Data.SqlDbType.VarChar, 50).Value = "ACL1 " & Format(prcDate, "ddMMyy") 'dr("tran_particular") 'Val(dr(3))  'txtExpirydays.Text
                    'com3.Parameters.Add("@OUR_REF", Data.SqlDbType.VarChar, 50).Value = 1
                    'com3.Parameters.Add("@USER_REF", Data.SqlDbType.VarChar, 50).Value = "" 'FormatDateTime(dr(5), DateFormat.ShortDate)
                    'com3.Parameters.Add("@DR_REF", Data.SqlDbType.VarChar, 50).Value = "" 'dr("tran_date_bal")
                    'com3.Parameters.Add("@VALUE_DATE", Data.SqlDbType.VarChar, 50).Value = FormatDateTime(prcDate, DateFormat.ShortDate) 'IIf(chkSystemAdmin.Checked = True, 1, 0)
                    'com3.Parameters.Add("@ACTION_ONENTRY", Data.SqlDbType.VarChar, 50).Value = "CLEANED" 'IIf(chkhaschanged.Checked = True, 1, 0)
                    'com3.Parameters.Add("@SOURCEZIPFILE", Data.SqlDbType.VarChar, 50).Value = dr(7)
                    'com3.Parameters.Add("@REASON", Data.SqlDbType.VarChar, 50).Value = dr(8)
                    'com3.Parameters.Add("@BACKUPDATE", Data.SqlDbType.VarChar, 50).Value = "" 'FormatDateTime(dr("tran_date_bal"), DateFormat.ShortDate)
                    'com3.Parameters.Add("@GENERATED", Data.SqlDbType.Int, 4).Value = 0 'dr(10)
                    'com3.Parameters.Add("@ACKNOWLEDGED", Data.SqlDbType.Int, 4).Value = 0 'dr(11) 'Date.Now 'CDate(Session("MyLastLogin")) 'MyLastLoginDate 
                    'com3.Parameters.Add("@pstd_user", Data.SqlDbType.VarChar, 50).Value = dr(12)
                    'com3.Parameters.Add("@SOLID", Data.SqlDbType.VarChar, 50).Value = cmbbranch.SelectedValue
                    'com3.Parameters.Add("@Datecreated", Data.SqlDbType.VarChar, 50).Value = Date.Now
                    'com3.Parameters.Add("@Empno", Data.SqlDbType.VarChar, 50).Value = txtempid.Text
                    'com3.Parameters.Add("@Email", Data.SqlDbType.VarChar, 50).Value = txtemail.Text.Trim.ToLower
                    com3.Parameters.Add("@ITEM_TYPE", Data.SqlDbType.VarChar, 50).Value = "ACB" '" & itemType & " 'txtusername.Text.Trim.ToLower
                    com3.Parameters.Add("@foracid", Data.SqlDbType.VarChar, 50).Value = strNWSCAccount 'dr("FORACID") 'txtfullname.Text
                    com3.Parameters.Add("@acct_name", Data.SqlDbType.VarChar, 50).Value = "" 'dr("substr(acct_name,1,35)") 'txtfullname.Text
                    com3.Parameters.Add("@tran_id", Data.SqlDbType.VarChar, 50).Value = 1 'dr("trim(htd.tran_id)") 'FormatDateTime(dr("tran_date"), DateFormat.ShortDate)
                    com3.Parameters.Add("@crncy_code", Data.SqlDbType.VarChar, 50).Value = "" 'dr("acct_crncy_code") 'Val(amount2) 'FormatDateTime(dr("tran_date"), DateFormat.ShortDate) 'CDate(txtChangedate.Text) 'DateAdd(Day" & DateDiff(DateInterval.Day, dt, DateTime.Now & ", " & DateTime.Now & ")
                    com3.Parameters.Add("@debits", Data.SqlDbType.Decimal, 18).Value = Val("") 'Val("sum(htd.tran_amt * decode(htd.part_tran_type,'D',1,0))") 'dr("part_tran_type") & "R"
                    com3.Parameters.Add("@credits", Data.SqlDbType.Decimal, 18).Value = Val("") 'Val("sum(htd.tran_amt * decode(htd.part_tran_type,'C',1,0))") 'dr("tran_particular") 'Val(dr(3))  'txtExpirydays.Text
                    com3.Parameters.Add("@amount", Data.SqlDbType.Decimal, 18).Value = Val(ACB) 'Val(amount) 'dr("tran_particular") 'Val(dr(3))  'txtExpirydays.Text
                    com3.Parameters.Add("@tran_date", Data.SqlDbType.VarChar, 50).Value = FormatDateTime(prcDate1, DateFormat.ShortDate) 'FormatDateTime(dr("tran_date"), DateFormat.ShortDate) 'dr("trim(tran_id)")
                    com3.Parameters.Add("@value_date", Data.SqlDbType.VarChar, 50).Value = FormatDateTime(prcDate1, DateFormat.ShortDate) 'FormatDateTime(dr("value_date"), DateFormat.ShortDate) '"" 'FormatDateTime(dr(5), DateFormat.ShortDate)
                    com3.Parameters.Add("@glshc", Data.SqlDbType.VarChar, 50).Value = "" 'dr("htd.gl_sub_head_code") 'dr("tran_date_bal")
                    com3.Parameters.Add("@scheme_code", Data.SqlDbType.VarChar, 50).Value = "" 'dr("schm_code") 'FormatDateTime(dr("value_date"), DateFormat.ShortDate) 'IIf(chkSystemAdmin.Checked = True, 1, 0)
                    com3.Parameters.Add("@part_tran_type", Data.SqlDbType.VarChar, 50).Value = "" 'dr("part_tran_type") '& "R"'"CLEANED" 'IIf(chkhaschanged.Checked = True, 1, 0)
                    com3.Parameters.Add("@sub_particular", Data.SqlDbType.VarChar, 50).Value = "ACL1 " & Format(prcDate1, "ddMMyy") 'dr("substr(tran_particular,3,14)")
                    com3.Parameters.Add("@particulars", Data.SqlDbType.VarChar, 50).Value = "ACL1 " & Format(prcDate1, "ddMMyy") 'dr("tran_particular")
                    com3.Parameters.Add("@remarks", Data.SqlDbType.VarChar, 50).Value = "ACL1 " & Format(prcDate1, "ddMMyy") 'dr("tran_rmks") '"" 'FormatDateTime(dr("tran_date_bal"), DateFormat.ShortDate)
                    com3.Parameters.Add("@entry_id", Data.SqlDbType.VarChar, 50).Value = "" 'dr("entry_user_Id")
                    com3.Parameters.Add("@pstd_id", Data.SqlDbType.VarChar, 50).Value = "" 'dr("pstd_user_id")
                    com3.ExecuteNonQuery()
                    msg = "inserted ACB data item successfully"
                    logMessage(msg, 0, "Info", "None", "All", "None", 0)
                    EventLog1.WriteEntry("Finished inserting ACB data item for NWSC running balances statement", EventLogEntryType.Information)

                    msg = "NWSC running balance statement BBF computation has started successfully"
                    logMessage(msg, 0, "Info", "None", "All", "None", 0)
                    EventLog1.WriteEntry("BBF computation has been started for NWSC running balances statement", EventLogEntryType.Information)

                    'THIS IS THE OPENING BALANCE
                    BBF = ACB - totalCredit - totalCredit2 - totalCredit3 + totalDebit + totalDebit2 + totalDebit3
                    msg = "Etax running balance statement BBF has been sent successfully to " & BBF
                    logMessage(msg, 0, "Info", "None", "All", "None", 0)
                    EventLog1.WriteEntry("Finished computing BBF for NWSC running balances statement", EventLogEntryType.Information)

                    'stract = "INSERT INTO BANKSTATEMENT-TEMP(ITEM_TYPE,BENE_ACCOUNT,STATEMENT_DATE,VALUE_DATE,AMOUNT,ENTRY_TYPE,PRN,OUR_REF,USER_REF,DR_REF,ACTION_ONENTRY)VALUES(" & _
                    '    "'BBF','" & strURAAccount & "','" & Format(prcDate, "dd/MM/yyyy") & "','" & Format(prcDate, "dd/MM/yyyy") & "'," & BBF & ",'CR','BBF " & Format(prcDate, "ddMMyy") & "','1','','','CLEANED')"
                    'com1.CommandText = stract
                    'com1.ExecuteNonQuery()

                    com3.CommandType = Data.CommandType.StoredProcedure
                    com3.Parameters.Clear()
                    'com3.Parameters.Add("@ITEM_TYPE", Data.SqlDbType.VarChar, 50).Value = "BBF" '" & itemType & " 'txtusername.Text.Trim.ToLower
                    'com3.Parameters.Add("@BENE_ACCOUNT", Data.SqlDbType.VarChar, 50).Value = strURAAccount 'dr("FORACID") 'txtfullname.Text
                    'com3.Parameters.Add("@STATEMENT_DATE", Data.SqlDbType.VarChar, 50).Value = FormatDateTime(prcDate, DateFormat.ShortDate)
                    'com3.Parameters.Add("@AMOUNT", Data.SqlDbType.Decimal, 18).Value = Val(BBF) 'FormatDateTime(dr("tran_date"), DateFormat.ShortDate) 'CDate(txtChangedate.Text) 'DateAdd(Day" & DateDiff(DateInterval.Day, dt, DateTime.Now & ", " & DateTime.Now & ")
                    'com3.Parameters.Add("@ENTRY_TYPE", Data.SqlDbType.VarChar, 50).Value = "CR" 'dr("part_tran_type") & "R"
                    'com3.Parameters.Add("@PRN", Data.SqlDbType.VarChar, 50).Value = "ACL2 " & Format(prcDate, "ddMMyy") 'dr("tran_particular") 'Val(dr(3))  'txtExpirydays.Text
                    'com3.Parameters.Add("@OUR_REF", Data.SqlDbType.VarChar, 50).Value = 1
                    'com3.Parameters.Add("@USER_REF", Data.SqlDbType.VarChar, 50).Value = "" 'FormatDateTime(dr(5), DateFormat.ShortDate)
                    'com3.Parameters.Add("@DR_REF", Data.SqlDbType.VarChar, 50).Value = "" 'dr("tran_date_bal")
                    'com3.Parameters.Add("@VALUE_DATE", Data.SqlDbType.VarChar, 50).Value = FormatDateTime(prcDate, DateFormat.ShortDate) 'IIf(chkSystemAdmin.Checked = True, 1, 0)
                    'com3.Parameters.Add("@ACTION_ONENTRY", Data.SqlDbType.VarChar, 50).Value = "CLEANED" 'IIf(chkhaschanged.Checked = True, 1, 0)
                    'com3.Parameters.Add("@SOURCEZIPFILE", Data.SqlDbType.VarChar, 50).Value = dr(7)
                    'com3.Parameters.Add("@REASON", Data.SqlDbType.VarChar, 50).Value = dr(8)
                    'com3.Parameters.Add("@BACKUPDATE", Data.SqlDbType.VarChar, 50).Value = "" 'FormatDateTime(dr("tran_date_bal"), DateFormat.ShortDate)
                    'com3.Parameters.Add("@GENERATED", Data.SqlDbType.Int, 4).Value = 0 'dr(10)
                    'com3.Parameters.Add("@ACKNOWLEDGED", Data.SqlDbType.Int, 4).Value = 0 'dr(11) 'Date.Now 'CDate(Session("MyLastLogin")) 'MyLastLoginDate 
                    'com3.Parameters.Add("@pstd_user", Data.SqlDbType.VarChar, 50).Value = dr(12)
                    'com3.Parameters.Add("@SOLID", Data.SqlDbType.VarChar, 50).Value = cmbbranch.SelectedValue
                    'com3.Parameters.Add("@Datecreated", Data.SqlDbType.VarChar, 50).Value = Date.Now
                    'com3.Parameters.Add("@Empno", Data.SqlDbType.VarChar, 50).Value = txtempid.Text
                    'com3.Parameters.Add("@Email", Data.SqlDbType.VarChar, 50).Value = txtemail.Text.Trim.ToLower
                    com3.Parameters.Add("@ITEM_TYPE", Data.SqlDbType.VarChar, 50).Value = "BBF" '" & itemType & " 'txtusername.Text.Trim.ToLower
                    com3.Parameters.Add("@foracid", Data.SqlDbType.VarChar, 50).Value = strNWSCAccount 'dr("FORACID") 'txtfullname.Text
                    com3.Parameters.Add("@acct_name", Data.SqlDbType.VarChar, 50).Value = "" 'dr("substr(acct_name,1,35)") 'txtfullname.Text
                    com3.Parameters.Add("@tran_id", Data.SqlDbType.VarChar, 50).Value = 1 'dr("trim(htd.tran_id)") 'FormatDateTime(dr("tran_date"), DateFormat.ShortDate)
                    com3.Parameters.Add("@crncy_code", Data.SqlDbType.VarChar, 50).Value = "" 'dr("acct_crncy_code") 'Val(amount2) 'FormatDateTime(dr("tran_date"), DateFormat.ShortDate) 'CDate(txtChangedate.Text) 'DateAdd(Day" & DateDiff(DateInterval.Day, dt, DateTime.Now & ", " & DateTime.Now & ")
                    com3.Parameters.Add("@debits", Data.SqlDbType.Decimal, 18).Value = Val("") 'Val("sum(htd.tran_amt * decode(htd.part_tran_type,'D',1,0))") 'dr("part_tran_type") & "R"
                    com3.Parameters.Add("@credits", Data.SqlDbType.Decimal, 18).Value = Val("") 'Val("sum(htd.tran_amt * decode(htd.part_tran_type,'C',1,0))") 'dr("tran_particular") 'Val(dr(3))  'txtExpirydays.Text
                    com3.Parameters.Add("@amount", Data.SqlDbType.Decimal, 18).Value = Val(BBF) 'Val(amount) 'dr("tran_particular") 'Val(dr(3))  'txtExpirydays.Text
                    com3.Parameters.Add("@tran_date", Data.SqlDbType.VarChar, 50).Value = FormatDateTime(prcDate, DateFormat.ShortDate) 'FormatDateTime(dr("tran_date"), DateFormat.ShortDate) 'dr("trim(tran_id)")
                    com3.Parameters.Add("@value_date", Data.SqlDbType.VarChar, 50).Value = FormatDateTime(prcDate, DateFormat.ShortDate) 'FormatDateTime(dr("value_date"), DateFormat.ShortDate) '"" 'FormatDateTime(dr(5), DateFormat.ShortDate)
                    com3.Parameters.Add("@glshc", Data.SqlDbType.VarChar, 50).Value = "" 'dr("htd.gl_sub_head_code") 'dr("tran_date_bal")
                    com3.Parameters.Add("@scheme_code", Data.SqlDbType.VarChar, 50).Value = "" 'dr("schm_code") 'FormatDateTime(dr("value_date"), DateFormat.ShortDate) 'IIf(chkSystemAdmin.Checked = True, 1, 0)
                    com3.Parameters.Add("@part_tran_type", Data.SqlDbType.VarChar, 50).Value = "" 'dr("part_tran_type") '& "R"'"CLEANED" 'IIf(chkhaschanged.Checked = True, 1, 0)
                    com3.Parameters.Add("@sub_particular", Data.SqlDbType.VarChar, 50).Value = "ACL2 " & Format(prcDate, "ddMMyy") 'dr("substr(tran_particular,3,14)")
                    com3.Parameters.Add("@particulars", Data.SqlDbType.VarChar, 50).Value = "ACL2 " & Format(prcDate, "ddMMyy") 'dr("tran_particular")
                    com3.Parameters.Add("@remarks", Data.SqlDbType.VarChar, 50).Value = "ACL2 " & Format(prcDate, "ddMMyy") 'dr("tran_rmks") '"" 'FormatDateTime(dr("tran_date_bal"), DateFormat.ShortDate)
                    com3.Parameters.Add("@entry_id", Data.SqlDbType.VarChar, 50).Value = "" 'dr("entry_user_Id")
                    com3.Parameters.Add("@pstd_id", Data.SqlDbType.VarChar, 50).Value = "" 'dr("pstd_user_id")
                    com3.ExecuteNonQuery()
                    msg = "NWSC running balance statement BBF  has been inserted successfully"
                    logMessage(msg, 0, "Info", "None", "All", "None", 0)
                    EventLog1.WriteEntry("Finished inserting BBF for NWSC running balances statement", EventLogEntryType.Information)


                    StrRecs4 = "DELETE FROM nwsc_running_balances"
                    com5.CommandText = StrRecs4
                    com5.ExecuteNonQuery()
                    msg = "NWSC running balance statement nwsc_running_balances table has been cleared successfully"
                    logMessage(msg, 0, "Info", "None", "All", "None", 0)
                    EventLog1.WriteEntry("Finished clearing nwsc_running_balances table for NWSC  running balances statement", EventLogEntryType.Information)

                    'StrRecs5 = "insert into running_balances values(FormatDateTime(prcDate, DateFormat.ShortDate))"

                    com4.CommandType = Data.CommandType.StoredProcedure
                    com4.Parameters.Clear()
                    com4.Parameters.Add("@report_date", Data.SqlDbType.VarChar, 50).Value = FormatDateTime(prcDate1, DateFormat.ShortDate) '" & itemType & " 'txtusername.Text.Trim.ToLower
                    com4.Parameters.Add("@close_bal", Data.SqlDbType.Decimal, 18).Value = Val(ACB) 'txtfullname.Text
                    com4.Parameters.Add("@open_bal", Data.SqlDbType.Decimal, 18).Value = Val(BBF) 'FormatDateTime(dr("tran_date"), DateFormat.ShortDate)
                    com4.Parameters.Add("@run_bal", Data.SqlDbType.Decimal, 18).Value = Val(BBF) 'FormatDateTime(dr("tran_date"), DateFormat.ShortDate) 'CDate(txtChangedate.Text) 'DateAdd(Day" & DateDiff(DateInterval.Day, dt, DateTime.Now & ", " & DateTime.Now & ")
                    'com4.Parameters.Add("@ENTRY_TYPE", Data.SqlDbType.VarChar, 50).Value = dr("part_tran_type") & "R"
                    'com4.Parameters.Add("@PRN", Data.SqlDbType.VarChar, 50).Value = dr("tran_particular") 'Val(dr(3))  'txtExpirydays.Text
                    'com4.Parameters.Add("@OUR_REF", Data.SqlDbType.VarChar, 50).Value = dr(1)
                    'com4.Parameters.Add("@USER_REF", Data.SqlDbType.VarChar, 50).Value = "" 'FormatDateTime(dr(5), DateFormat.ShortDate)
                    'com4.Parameters.Add("@DR_REF", Data.SqlDbType.VarChar, 50).Value = dr("tran_date_bal")
                    'com4.Parameters.Add("@VALUE_DATE", Data.SqlDbType.Int, 4).Value = FormatDateTime(dr("value_date"), DateFormat.ShortDate) 'IIf(chkSystemAdmin.Checked = True, 1, 0)
                    'com4.Parameters.Add("@ACTION_ONENTRY", Data.SqlDbType.Int, 4).Value = "CLEANED" 'IIf(chkhaschanged.Checked = True, 1, 0)
                    'com4.Parameters.Add("@SOURCEZIPFILE", Data.SqlDbType.VarChar, 50).Value = dr(7)
                    'com4.Parameters.Add("@REASON", Data.SqlDbType.VarChar, 50).Value = dr(8)
                    'com4.Parameters.Add("@BACKUPDATE", Data.SqlDbType.VarChar, 50).Value = "" 'FormatDateTime(dr("tran_date_bal"), DateFormat.ShortDate)
                    'com4.Parameters.Add("@GENERATED", Data.SqlDbType.Int, 4).Value = 0 'dr(10)
                    'com4.Parameters.Add("@ACKNOWLEDGED", Data.SqlDbType.Int, 4).Value = 0 'dr(11) 'Date.Now 'CDate(Session("MyLastLogin")) 'MyLastLoginDate 
                    'com4.Parameters.Add("@pstd_user", Data.SqlDbType.VarChar, 50).Value = dr(12)
                    'com4.Parameters.Add("@SOLID", Data.SqlDbType.VarChar, 50).Value = cmbbranch.SelectedValue
                    'com4.Parameters.Add("@Datecreated", Data.SqlDbType.VarChar, 50).Value = Date.Now
                    'com4.Parameters.Add("@Empno", Data.SqlDbType.VarChar, 50).Value = txtempid.Text
                    'com4.Parameters.Add("@Email", Data.SqlDbType.VarChar, 50).Value = txtemail.Text.Trim.ToLower
                    com4.ExecuteNonQuery()
                    msg = "NWSC running balance statement nwsc_running_balances Temp table has been updates successfully"
                    logMessage(msg, 0, "Info", "None", "All", "None", 0)
                    EventLog1.WriteEntry("Finished updating nwsc_running_balances Temp table for NWSC running balances statement", EventLogEntryType.Information)
                    checklogs()


                    'StrRecs24 = "SELECT DISTINCT * FROM BANKSTATEMENT_TEMP where ITEM_TYPE='BBF'" ' order by our_ref" ' WHERE PRN='" & dr("tran_particular") & "'"
                    'Sqlcom24.CommandText = StrRecs24
                    'Sqldr24 = Sqlcom24.ExecuteReader

                    StrRecs8 = "DELETE FROM nwscbankstatement" ' WHERE GENERATED=0"
                    com1.CommandText = StrRecs8
                    com1.ExecuteNonQuery()
                    msg = "NWSC running balance statement bankstatement online table has been cleared successfully"
                    logMessage(msg, 0, "Info", "None", "All", "None", 0)
                    EventLog1.WriteEntry("Finished clearing the bankstatement online table for NWSC running balances statement", EventLogEntryType.Information)

                    insertNWSCBBFbalances()
                    msg = "Finished inserting Open balances data into the nwscbankstatement"
                    logMessage(msg, 0, "Info", "None", "All", "None", 0)
                    'Dim Sqlcom24 As New SqlCommand("", comm)
                    'Dim Sqldr24 As SqlDataReader ' = Sqlcom.ExecuteReader
                    'comm.Open()
                    'msg = "comm connection has been opened successfully"
                    'logMessage(msg, 0, "Info", "None", "All", "None", 0)
                    'StrRecs24 = "SELECT DISTINCT * FROM BANKSTATEMENT_TEMP where ITEM_TYPE='BBF' " ' order by our_ref" ' WHERE PRN='" & dr("tran_particular") & "'"
                    'Sqlcom24.CommandText = StrRecs24
                    'Sqldr24 = Sqlcom24.ExecuteReader
                    'msg = "Data reader sqldr24 has been executed successfully"
                    'logMessage(msg, 0, "Info", "None", "All", "None", 0)
                    'If Sqldr24.HasRows Then
                    '    msg = "Data reader sqldr24 has data to be inserted into bank statement table"
                    '    logMessage(msg, 0, "Info", "None", "All", "None", 0)

                    '    'While Sqldr24.Read
                    '    If Sqldr24.Read Then
                    '        msg = "Reading data from the data reader Sqldr24 started"
                    '        logMessage(msg, 0, "Info", "None", "All", "None", 0)
                    '        itemType = "OpeningBalance"
                    '        StrRecs24 = "select open_bal FROM running_balances"
                    '        Sqlcom24.CommandText = StrRecs24
                    '        runopen = Sqlcom24.ExecuteScalar()
                    '        runbal = runopen
                    '        amount2 = runopen
                    '        'insert Opening balance records

                    '        com61.CommandType = Data.CommandType.StoredProcedure
                    '        com61.Parameters.Clear()
                    '        com61.Parameters.Add("@ITEM_TYPE", Data.SqlDbType.VarChar, 50).Value = "OpeningBalance" 'itemType '"OpeningBalance" 'itemType '" & itemType & " 'txtusername.Text.Trim.ToLower
                    '        com61.Parameters.Add("@BENE_ACCOUNT", Data.SqlDbType.VarChar, 50).Value = Sqldr24("BENE_ACCOUNT") 'txtfullname.Text
                    '        com61.Parameters.Add("@STATEMENT_DATE", Data.SqlDbType.VarChar, 50).Value = FormatDateTime(Sqldr24("STATEMENT_DATE"), DateFormat.ShortDate)
                    '        com61.Parameters.Add("@AMOUNT", Data.SqlDbType.Decimal, 18).Value = Sqldr24("amount") 'FormatDateTime(dr("tran_date"), DateFormat.ShortDate) 'CDate(txtChangedate.Text) 'DateAdd(Day" & DateDiff(DateInterval.Day, dt, DateTime.Now & ", " & DateTime.Now & ")
                    '        com61.Parameters.Add("@ENTRY_TYPE", Data.SqlDbType.VarChar, 50).Value = Sqldr24("ENTRY_TYPE") '& "R"
                    '        com61.Parameters.Add("@PRN", Data.SqlDbType.VarChar, 50).Value = Sqldr24("PRN") 'Val(dr(3))  'txtExpirydays.Text
                    '        com61.Parameters.Add("@OUR_REF", Data.SqlDbType.VarChar, 50).Value = Sqldr24("OUR_REF")
                    '        com61.Parameters.Add("@USER_REF", Data.SqlDbType.VarChar, 50).Value = IIf(IsDBNull(Sqldr24("USER_REF")) = True, "", Sqldr24("USER_REF")) 'FormatDateTime(dr(5), DateFormat.ShortDate)
                    '        com61.Parameters.Add("@DR_REF", Data.SqlDbType.VarChar, 50).Value = Sqldr24("DR_REF")
                    '        com61.Parameters.Add("@VALUE_DATE", Data.SqlDbType.VarChar, 50).Value = FormatDateTime(Sqldr24("VALUE_DATE"), DateFormat.ShortDate) 'IIf(chkSystemAdmin.Checked = True, 1, 0)
                    '        com61.Parameters.Add("@ACTION_ONENTRY", Data.SqlDbType.VarChar, 50).Value = Sqldr24("ACTION_ONENTRY") 'IIf(chkhaschanged.Checked = True, 1, 0)
                    '        'com6.Parameters.Add("@SOURCEZIPFILE", Data.SqlDbType.VarChar, 50).Value = dr(7)
                    '        com61.Parameters.Add("@RUNNING_BALANCE", Data.SqlDbType.Decimal, 18).Value = amount2
                    '        'com6.Parameters.Add("@BACKUPDATE", Data.SqlDbType.VarChar, 50).Value = "" 'FormatDateTime(dr("tran_date_bal"), DateFormat.ShortDate)
                    '        'com6.Parameters.Add("@GENERATED", Data.SqlDbType.Int, 4).Value = 0 'dr(10)
                    '        'com6.Parameters.Add("@ACKNOWLEDGED", Data.SqlDbType.Int, 4).Value = 0 'dr(11) 'Date.Now 'CDate(Session("MyLastLogin")) 'MyLastLoginDate 
                    '        'com6.Parameters.Add("@pstd_user", Data.SqlDbType.VarChar, 50).Value = dr(12)
                    '        'com6.Parameters.Add("@SOLID", Data.SqlDbType.VarChar, 50).Value = cmbbranch.SelectedValue
                    '        'com6.Parameters.Add("@Datecreated", Data.SqlDbType.VarChar, 50).Value = Date.Now
                    '        'com6.Parameters.Add("@Empno", Data.SqlDbType.VarChar, 50).Value = txtempid.Text
                    '        'com6.Parameters.Add("@Email", Data.SqlDbType.VarChar, 50).Value = txtemail.Text.Trim.ToLower
                    '        com61.ExecuteNonQuery()

                    '        msg = "Etax Opening balance statement value has been inserted successfully"
                    '        logMessage(msg, 0, "Info", "None", "All", "None", 0)
                    '        EventLog1.WriteEntry("Finished updating running_balances table for Etax running balances statement", EventLogEntryType.Information)


                    '        'End While
                    '    End If
                    '    msg = "Data reader sqldr24 has been closed successfully"
                    '    logMessage(msg, 0, "Info", "None", "All", "None", 0)
                    '    Sqldr24.Close()
                    '    Sqldr24.Dispose()
                    '    msg = "Checking if Data reader sqldr24 has been closed successfully"
                    '    logMessage(msg, 0, "Info", "None", "All", "None", 0)
                    '    If Sqldr24.IsClosed = False Then
                    '        msg = "Data reader sqldr24 has not been closed successfully, so close it"
                    '        logMessage(msg, 0, "Info", "None", "All", "None", 0)
                    '        Sqldr24.Close()
                    '        Sqldr24.Dispose()
                    '    End If
                    '    'Do While Sqldr24.Read


                    '    'Loop
                    '    'Sqldr24.Close()

                    'End If
                    ''Sqldr24.Close()
                    'comm.Close()

                    'msg = "Data reader sqldr24 and comm have been closed successfully"
                    'logMessage(msg, 0, "Info", "None", "All", "None", 0)


                    'StrRecs2 = "SELECT * FROM BANKSTATEMENT_TEMP where   order by our_ref asc" ' order by our_ref" ' WHERE PRN='" & dr("tran_particular") & "'"
                    StrRecs2 = "select * from nwscbankstatement_temp where item_type NOT IN ('ACB','BBF')order by run_code asc" 'our_ref asc"
                    Sqlcom2.CommandText = StrRecs2
                    Sqldr2 = Sqlcom2.ExecuteReader
                    msg = "Data reader sqldr2 has been executed successfully"
                    logMessage(msg, 0, "Info", "None", "All", "None", 0)

                    If Sqldr2.HasRows Then
                        msg = "Data reader sqldr2 has data to be inserted into nwscbank statement table"
                        logMessage(msg, 0, "Info", "None", "All", "None", 0)
                        Do While Sqldr2.Read
                            msg = "Reading data from the data reader Sqldr2 started"
                            logMessage(msg, 0, "Info", "None", "All", "None", 0)

                            'If Sqldr2("item_type") = "BBF" Then
                            '    'amount2 = Sqldr2("amount")
                            '    itemType = "OpeningBalance"
                            '    StrRecs4 = "select open_bal FROM running_balances"
                            '    com5.CommandText = StrRecs4
                            '    runopen = com5.ExecuteScalar()
                            '    runbal = runopen
                            '    amount2 = runopen
                            '    'amount3=

                            If Sqldr2("item_type") = "CREDIT" Then
                                amount3 = Sqldr2("amount")
                                itemType = "Credit"
                                'totalDebit = totalDebit + amount
                                StrRecs4 = "select run_bal FROM nwsc_running_balances"
                                com5.CommandText = StrRecs4
                                runbalnow = com5.ExecuteScalar()
                                runbal = runbalnow + amount3
                                'StrRecs5 = "update running_balances set run_bal= runbal"
                                'com5.CommandText = StrRecs5
                                'com5.ExecuteNonQuery()
                                amount2 = runbal

                                com71.CommandType = Data.CommandType.StoredProcedure
                                com71.Parameters.Clear()
                                com71.Parameters.Add("@RUNNING_BALANCE", Data.SqlDbType.Decimal, 18).Value = Val(runbal) 'FormatDateTime(dr("tran_date"), DateFormat.ShortDate) 'CDate(txtChangedate.Text) 'DateAdd(Day" & DateDiff(DateInterval.Day, dt, DateTime.Now & ", " & DateTime.Now & ")
                                com71.ExecuteNonQuery()
                                'msg = "NWSC running balance statement value has been updated successfully"
                                'logMessage(msg, 0, "Info", "None", "All", "None", 0)
                                'EventLog1.WriteEntry("Finished updating running_balances table for NWSC running balances statement", EventLogEntryType.Information)
                                checklogs()



                            ElseIf Sqldr2("item_type") = "DEBIT" Then
                                amount3 = Sqldr2("amount")
                                itemType = "Debit"
                                'totalCredit = totalCredit + amount
                                StrRecs4 = "select run_bal FROM nwsc_running_balances"
                                com5.CommandText = StrRecs4
                                runbalnow = com5.ExecuteScalar()
                                runbal = runbalnow - amount3
                                'StrRecs5 = "update running_balances set run_bal= val(runbal)"
                                'com5.CommandText = StrRecs5
                                'com5.ExecuteNonQuery()
                                amount2 = runbal


                                com71.CommandType = Data.CommandType.StoredProcedure
                                com71.Parameters.Clear()
                                com71.Parameters.Add("@RUNNING_BALANCE", Data.SqlDbType.Decimal, 18).Value = Val(runbal) 'FormatDateTime(dr("tran_date"), DateFormat.ShortDate) 'CDate(txtChangedate.Text) 'DateAdd(Day" & DateDiff(DateInterval.Day, dt, DateTime.Now & ", " & DateTime.Now & ")
                                com71.ExecuteNonQuery()
                                'msg = "NWSC running balance statement value has been updated successfully"
                                'logMessage(msg, 0, "Info", "None", "All", "None", 0)
                                'EventLog1.WriteEntry("Finished updating running_balances table for NWSC running balances statement", EventLogEntryType.Information)

                                checklogs()
                            ElseIf Sqldr2("item_type") = "Reversal" Then
                                amount3 = Sqldr2("amount")
                                itemType = "Reversal"
                                'totalCredit = totalCredit + amount
                                StrRecs4 = "select run_bal FROM nwsc_running_balances"
                                com5.CommandText = StrRecs4
                                runbalnow = com5.ExecuteScalar()
                                runbal = runbalnow - amount3
                                'StrRecs5 = "update running_balances set run_bal= val(runbal)"
                                'com5.CommandText = StrRecs5
                                'com5.ExecuteNonQuery()
                                amount2 = runbal

                                com71.CommandType = Data.CommandType.StoredProcedure
                                com71.Parameters.Clear()
                                com71.Parameters.Add("@RUNNING_BALANCE", Data.SqlDbType.Decimal, 18).Value = Val(runbal) 'FormatDateTime(dr("tran_date"), DateFormat.ShortDate) 'CDate(txtChangedate.Text) 'DateAdd(Day" & DateDiff(DateInterval.Day, dt, DateTime.Now & ", " & DateTime.Now & ")
                                com71.ExecuteNonQuery()
                                'msg = "NWSC running balance statement value has been updated successfully"
                                'logMessage(msg, 0, "Info", "None", "All", "None", 0)
                                'EventLog1.WriteEntry("Finished updating running_balances table for NWSC running balances statement", EventLogEntryType.Information)


                                'ElseIf Sqldr2("item_type") = "ACB" Then
                                '    'amount2 = Sqldr2("amount")
                                '    itemType = "ClosingBalance"
                                '    StrRecs4 = "select close_bal FROM running_balances"
                                '    com5.CommandText = StrRecs4
                                '    closed_bal = com5.ExecuteScalar()
                                '    'amount2 = closed_bal 'Sqldr2("amount")
                                '    runbal = closed_bal
                                '    amount2 = runbal
                                '    'ElseIf Sqldr2("item_type") = "BBF" Then
                                '    '    amount3 = Sqldr2("amount")
                                '    '    itemType = "OpeningBalance"
                                checklogs()

                            Else
                                'do not insert any item as they will be cartered for by other scripts flying in very soon-Leone Jobs

                                checklogs()
                            End If
                            msg = "Checking if a record has already been inserted into bank statement table"
                            logMessage(msg, 0, "Info", "None", "All", "None", 0)
                            EventLog1.WriteEntry("Starting to check if a record already exists in the bankstatement table for Etax running balances statement", EventLogEntryType.Information)

                            'validate if the record has been inserted
                            StrRecs = "SELECT * FROM nwscBANKSTATEMENT WHERE sub_particular='" & Sqldr2("sub_particular") & "'"
                            Sqlcom3.CommandText = StrRecs
                            Sqldr3 = Sqlcom3.ExecuteReader
                            If Sqldr3.HasRows Then
                                'dont insert recs
                                msg = "Record already exists in the nwscbanakstatement table, please, don't insert"
                                logMessage(msg, 0, "Info", "None", "All", "None", 0)
                                EventLog1.WriteEntry("records already exist , don't insert data for NWSC running balances statement", EventLogEntryType.Information)
                                checklogs()
                            Else
                                msg = "This record does not exist, insert data for NWSC running balance statement has been sent successfully"
                                logMessage(msg, 0, "Info", "None", "All", "None", 0)
                                EventLog1.WriteEntry("Started inserting data into bankstatement table for NWSC running balances statement", EventLogEntryType.Information)
                                '================================================================
                                'Call the function to populate bankcollect table in order for validation to be kawa
                                'spoolrawbankcollectStatement()
                                'msg = "raw bankcollect table has been filled with data successfully"
                                'logMessage(msg, 0, "Info", "None", "All", "None", 0)

                                '=================

                                Dim responsename As String = spoolrawcustomerdetails()
                                'responsename = spoolrawcustomerdetails()
                                'responsename = spoolnwscCSVfileStatement()
                                'call a function to send email
                                If responsename = "No" Then
                                    'getyestonwscrunningbalstatement() = "" Then
                                    'Do not send statement as it hno file has been created
                                    msg = "NO email notification will be sent for NWSC running balances statement file as no file was created" '& tilbal
                                    logMessage(msg, 0, "Info", "None", "All", "None", 0)

                                    Exit Try
                                    Exit Sub
                                    checklogs()

                                Else
                                    'spoolrawbankcollectStatement()
                                    msg = "raw bankcollect table has been filled with data successfully"
                                    logMessage(msg, 0, "Info", "None", "All", "None", 0)

                                    'Get Bankcollect customer details from UBA E-water platform now
                                    Dim sub_particular As String = Nothing
                                    'sub_particular = Sqldr2("sub_particular")
                                    sub_particular = IIf(IsDBNull(Sqldr2("sub_particular")) = True, "ABCD", Sqldr2("sub_particular"))
                                    Dim MyData As Returndata
                                    MyData = AddcustdetailsData(sub_particular, 8, 9, 10)

                                    Dim customer_ref As String = Nothing
                                    Dim customer_name As String = Nothing
                                    Dim property_ref As String = Nothing

                                    customer_ref = MyData.customer_ref_result.ToString()
                                    customer_name = MyData.customer_name_result.ToString()
                                    property_ref = MyData.property_ref_result.ToString()

                                    'If customer_ref = "" Or customer_name = "" Then
                                    '    'dont insert recs
                                    '    msg = "No need to add data record as it does not have a matching partner record from bankcollect_nwsc table, please, don't insert"
                                    '    logMessage(msg, 0, "Info", "None", "All", "None", 0)
                                    '    EventLog1.WriteEntry("record does not have a matching partner , don't insert data for NWSC running balances statement", EventLogEntryType.Information)


                                    'Else
                                    'do insert recs
                                    msg = "Please add data record as it has a matching partner record from bankcollect_nwsc table" ', please, don't insert"
                                    logMessage(msg, 0, "Info", "None", "All", "None", 0)
                                    EventLog1.WriteEntry("record does have a matching partner , insert data for NWSC running balances statement", EventLogEntryType.Information)

                                    com6.CommandType = Data.CommandType.StoredProcedure
                                    com6.Parameters.Clear()
                                    'com6.Parameters.Add("@ITEM_TYPE", Data.SqlDbType.VarChar, 50).Value = itemType '"OpeningBalance" 'itemType '" & itemType & " 'txtusername.Text.Trim.ToLower
                                    'com6.Parameters.Add("@BENE_ACCOUNT", Data.SqlDbType.VarChar, 50).Value = Sqldr2("BENE_ACCOUNT") 'txtfullname.Text
                                    'com6.Parameters.Add("@STATEMENT_DATE", Data.SqlDbType.VarChar, 50).Value = FormatDateTime(Sqldr2("STATEMENT_DATE"), DateFormat.ShortDate)
                                    'com6.Parameters.Add("@AMOUNT", Data.SqlDbType.Decimal, 18).Value = Sqldr2("amount") 'Val(Sqldr2("amount")) 'FormatDateTime(dr("tran_date"), DateFormat.ShortDate) 'CDate(txtChangedate.Text) 'DateAdd(Day" & DateDiff(DateInterval.Day, dt, DateTime.Now & ", " & DateTime.Now & ")
                                    'com6.Parameters.Add("@ENTRY_TYPE", Data.SqlDbType.VarChar, 50).Value = Sqldr2("ENTRY_TYPE") '& "R"
                                    'com6.Parameters.Add("@PRN", Data.SqlDbType.VarChar, 50).Value = Sqldr2("PRN") 'Val(dr(3))  'txtExpirydays.Text
                                    'com6.Parameters.Add("@OUR_REF", Data.SqlDbType.VarChar, 50).Value = Sqldr2("OUR_REF")
                                    'com6.Parameters.Add("@USER_REF", Data.SqlDbType.VarChar, 50).Value = IIf(IsDBNull(Sqldr2("USER_REF")) = True, "", Sqldr2("USER_REF")) 'FormatDateTime(dr(5), DateFormat.ShortDate)
                                    'com6.Parameters.Add("@DR_REF", Data.SqlDbType.VarChar, 50).Value = Sqldr2("DR_REF")
                                    'com6.Parameters.Add("@VALUE_DATE", Data.SqlDbType.VarChar, 50).Value = FormatDateTime(Sqldr2("VALUE_DATE"), DateFormat.ShortDate) 'IIf(chkSystemAdmin.Checked = True, 1, 0)
                                    'com6.Parameters.Add("@ACTION_ONENTRY", Data.SqlDbType.VarChar, 50).Value = Sqldr2("ACTION_ONENTRY") 'IIf(chkhaschanged.Checked = True, 1, 0)
                                    ''com6.Parameters.Add("@SOURCEZIPFILE", Data.SqlDbType.VarChar, 50).Value = dr(7)
                                    'com6.Parameters.Add("@RUNNING_BALANCE", Data.SqlDbType.Decimal, 18).Value = amount2 'Val(amount2)
                                    'com6.Parameters.Add("@BACKUPDATE", Data.SqlDbType.VarChar, 50).Value = "" 'FormatDateTime(dr("tran_date_bal"), DateFormat.ShortDate)
                                    'com6.Parameters.Add("@GENERATED", Data.SqlDbType.Int, 4).Value = 0 'dr(10)
                                    'com6.Parameters.Add("@ACKNOWLEDGED", Data.SqlDbType.Int, 4).Value = 0 'dr(11) 'Date.Now 'CDate(Session("MyLastLogin")) 'MyLastLoginDate 
                                    'com6.Parameters.Add("@pstd_user", Data.SqlDbType.VarChar, 50).Value = dr(12)
                                    'com6.Parameters.Add("@SOLID", Data.SqlDbType.VarChar, 50).Value = cmbbranch.SelectedValue
                                    'com6.Parameters.Add("@Datecreated", Data.SqlDbType.VarChar, 50).Value = Date.Now
                                    'com6.Parameters.Add("@Empno", Data.SqlDbType.VarChar, 50).Value = txtempid.Text
                                    'com6.Parameters.Add("@Email", Data.SqlDbType.VarChar, 50).Value = txtemail.Text.Trim.ToLower

                                    com6.Parameters.Add("@ITEM_TYPE", Data.SqlDbType.VarChar, 50).Value = itemType '" & itemType & " 'txtusername.Text.Trim.ToLower
                                    com6.Parameters.Add("@foracid", Data.SqlDbType.VarChar, 50).Value = Sqldr2("foracid") 'strNWSCAccount 'dr("FORACID") 'txtfullname.Text
                                    com6.Parameters.Add("@acct_name", Data.SqlDbType.VarChar, 50).Value = Sqldr2("acct_name") 'IIf(IsDBNull(Sqldr2("acct_name")) = True, "", Sqldr2("acct_name")) 'Sqldr2("acct_name") '"" 'dr("substr(acct_name,1,35)") 'txtfullname.Text
                                    com6.Parameters.Add("@tran_id", Data.SqlDbType.VarChar, 50).Value = Sqldr2("tran_id") '1 'dr("trim(htd.tran_id)") 'FormatDateTime(dr("tran_date"), DateFormat.ShortDate)
                                    com6.Parameters.Add("@crncy_code", Data.SqlDbType.VarChar, 50).Value = Sqldr2("crncy_code") 'IIf(IsDBNull(Sqldr2("crncy_code")) = True, "", Sqldr2("crncy_code")) 'Sqldr2("crncy_code") '"" 'dr("acct_crncy_code") 'Val(amount2) 'FormatDateTime(dr("tran_date"), DateFormat.ShortDate) 'CDate(txtChangedate.Text) 'DateAdd(Day" & DateDiff(DateInterval.Day, dt, DateTime.Now & ", " & DateTime.Now & ")
                                    com6.Parameters.Add("@debits", Data.SqlDbType.Decimal, 18).Value = IIf(IsDBNull(Val(Sqldr2("debits"))) = True, "", Val(Sqldr2("debits"))) 'Val(Sqldr2("debits")) 'Val("") 'Val("sum(htd.tran_amt * decode(htd.part_tran_type,'D',1,0))") 'dr("part_tran_type") & "R"
                                    com6.Parameters.Add("@credits", Data.SqlDbType.Decimal, 18).Value = IIf(IsDBNull(Val(Sqldr2("credits"))) = True, "", Val(Sqldr2("credits"))) 'Val(Sqldr2("credits")) 'Val("") 'Val("sum(htd.tran_amt * decode(htd.part_tran_type,'C',1,0))") 'dr("tran_particular") 'Val(dr(3))  'txtExpirydays.Text
                                    com6.Parameters.Add("@amount", Data.SqlDbType.Decimal, 18).Value = Val(Sqldr2("amount")) 'Val(BBF) 'Val(amount) 'dr("tran_particular") 'Val(dr(3))  'txtExpirydays.Text
                                    com6.Parameters.Add("@tran_date", Data.SqlDbType.VarChar, 50).Value = FormatDateTime(Sqldr2("tran_date"), DateFormat.ShortDate) 'FormatDateTime(prcDate, DateFormat.ShortDate) 'FormatDateTime(dr("tran_date"), DateFormat.ShortDate) 'dr("trim(tran_id)")
                                    com6.Parameters.Add("@value_date", Data.SqlDbType.VarChar, 50).Value = FormatDateTime(Sqldr2("value_date"), DateFormat.ShortDate) 'FormatDateTime(prcDate, DateFormat.ShortDate) 'FormatDateTime(dr("value_date"), DateFormat.ShortDate) '"" 'FormatDateTime(dr(5), DateFormat.ShortDate)
                                    com6.Parameters.Add("@glshc", Data.SqlDbType.VarChar, 50).Value = Sqldr2("glshc") 'IIf(IsDBNull(Sqldr2("glshc")) = True, "", Sqldr2("glshc")) 'Sqldr2("glshc") '"" 'dr("htd.gl_sub_head_code") 'dr("tran_date_bal")
                                    com6.Parameters.Add("@scheme_code", Data.SqlDbType.VarChar, 50).Value = Sqldr2("scheme_code") 'IIf(IsDBNull(Sqldr2("scheme_code")) = True, "", Sqldr2("scheme_code")) 'Sqldr2("scheme_code") '"" 'dr("schm_code") 'FormatDateTime(dr("value_date"), DateFormat.ShortDate) 'IIf(chkSystemAdmin.Checked = True, 1, 0)
                                    com6.Parameters.Add("@part_tran_type", Data.SqlDbType.VarChar, 50).Value = Sqldr2("part_tran_type") 'IIf(IsDBNull(Sqldr2("part_tran_type")) = True, "", Sqldr2("part_tran_type")) 'Sqldr2("part_tran_type") '"" 'dr("part_tran_type") '& "R"'"CLEANED" 'IIf(chkhaschanged.Checked = True, 1, 0)
                                    com6.Parameters.Add("@sub_particular", Data.SqlDbType.VarChar, 50).Value = Sqldr2("sub_particular") '"ACL2 " & Format(prcDate, "ddMMyy") 'dr("substr(tran_particular,3,14)")
                                    com6.Parameters.Add("@particulars", Data.SqlDbType.VarChar, 50).Value = Sqldr2("particulars") '"ACL2 " & Format(prcDate, "ddMMyy") 'dr("tran_particular")
                                    com6.Parameters.Add("@remarks", Data.SqlDbType.VarChar, 50).Value = Sqldr2("remarks") '"ACL2 " & Format(prcDate, "ddMMyy") 'dr("tran_rmks") '"" 'FormatDateTime(dr("tran_date_bal"), DateFormat.ShortDate)
                                    com6.Parameters.Add("@entry_id", Data.SqlDbType.VarChar, 50).Value = Sqldr2("entry_id") 'IIf(IsDBNull(Sqldr2("entry_id")) = True, "", Sqldr2("entry_id")) 'Sqldr2("entry_id") '"" 'dr("entry_user_Id")
                                    com6.Parameters.Add("@pstd_id", Data.SqlDbType.VarChar, 50).Value = Sqldr2("pstd_id") 'IIf(IsDBNull(Sqldr2("pstd_id")) = True, "", Sqldr2("pstd_id")) 'Sqldr2("pstd_id") '"" 'dr("pstd_user_id")
                                    com6.Parameters.Add("@RUNNING_BALANCE", Data.SqlDbType.Decimal, 18).Value = amount2 'Val(amount2)
                                    com6.Parameters.Add("@customer_ref", Data.SqlDbType.VarChar, 50).Value = customer_ref 'Sqldrcollect("customer_ref") '"" 'Sqldr2("remarks") '"ACL2 " & Format(prcDate, "ddMMyy") 'dr("tran_rmks") '"" 'FormatDateTime(dr("tran_date_bal"), DateFormat.ShortDate)
                                    com6.Parameters.Add("@customer_name", Data.SqlDbType.VarChar, 50).Value = customer_name 'Sqldrcollect("customer_name") '"" 'IIf(IsDBNull(Sqldr2("entry_id")) = True, "", Sqldr2("entry_id")) 'Sqldr2("entry_id") '"" 'dr("entry_user_Id")
                                    com6.Parameters.Add("@property_ref", Data.SqlDbType.VarChar, 50).Value = property_ref 'Sqldrcollect("property_ref") '"" 'IIf(IsDBNull(Sqldr2("pstd_id")) = True, "", Sqldr2("pstd_id")) 'Sqldr2("pstd_id") '"" 'dr("pstd_user_id")

                                    com6.ExecuteNonQuery()
                                    msg = "nwscbanakstatement table has been filled with data successfully and customer details are" & customer_ref & customer_name
                                    logMessage(msg, 0, "Info", "None", "All", "None", 0)


                                    'End If

                                    '=============================================================================
                                    ''check if the item is in bankcollect report for the previous day before inserting it
                                    'StrRecscollect = "select * from bankcollect_nwsc where payment_ref= '" & Sqldr2("sub_particular") & "' " 'order by payment_ref asc" 'where item_type NOT IN ('ACB','BBF') 'our_ref asc"
                                    'Sqlcomcollect.CommandText = StrRecscollect
                                    'Sqldrcollect = Sqlcomcollect.ExecuteReader
                                    'msg = "Data reader sqldrcollect has been executed successfully"
                                    'logMessage(msg, 0, "Info", "None", "All", "None", 0)
                                    'If Sqldrcollect.HasRows Then

                                    '    While Sqldrcollect.Read
                                    '        ''insert closing balances
                                    '        'com6.CommandType = Data.CommandType.StoredProcedure
                                    '        'com6.Parameters.Clear()
                                    '        'com6.Parameters.Add("@ITEM_TYPE", Data.SqlDbType.VarChar, 50).Value = itemType '"OpeningBalance" 'itemType '" & itemType & " 'txtusername.Text.Trim.ToLower
                                    '        'com6.Parameters.Add("@BENE_ACCOUNT", Data.SqlDbType.VarChar, 50).Value = Sqldr2("BENE_ACCOUNT") 'txtfullname.Text
                                    '        'com6.Parameters.Add("@STATEMENT_DATE", Data.SqlDbType.VarChar, 50).Value = FormatDateTime(Sqldr2("STATEMENT_DATE"), DateFormat.ShortDate)
                                    '        'com6.Parameters.Add("@AMOUNT", Data.SqlDbType.Decimal, 18).Value = Val(Sqldr2("amount")) 'FormatDateTime(dr("tran_date"), DateFormat.ShortDate) 'CDate(txtChangedate.Text) 'DateAdd(Day" & DateDiff(DateInterval.Day, dt, DateTime.Now & ", " & DateTime.Now & ")
                                    '        'com6.Parameters.Add("@ENTRY_TYPE", Data.SqlDbType.VarChar, 50).Value = Sqldr2("ENTRY_TYPE") '& "R"
                                    '        'com6.Parameters.Add("@PRN", Data.SqlDbType.VarChar, 50).Value = Sqldr2("PRN") 'Val(dr(3))  'txtExpirydays.Text
                                    '        'com6.Parameters.Add("@OUR_REF", Data.SqlDbType.VarChar, 50).Value = Sqldr2("OUR_REF")
                                    '        'com6.Parameters.Add("@USER_REF", Data.SqlDbType.VarChar, 50).Value = IIf(IsDBNull(Sqldr2("USER_REF")) = True, "", Sqldr2("USER_REF")) 'FormatDateTime(dr(5), DateFormat.ShortDate)
                                    '        'com6.Parameters.Add("@DR_REF", Data.SqlDbType.VarChar, 50).Value = Sqldr2("DR_REF")
                                    '        'com6.Parameters.Add("@VALUE_DATE", Data.SqlDbType.VarChar, 50).Value = FormatDateTime(Sqldr2("VALUE_DATE"), DateFormat.ShortDate) 'IIf(chkSystemAdmin.Checked = True, 1, 0)
                                    '        'com6.Parameters.Add("@ACTION_ONENTRY", Data.SqlDbType.VarChar, 50).Value = Sqldr2("ACTION_ONENTRY") 'IIf(chkhaschanged.Checked = True, 1, 0)
                                    '        ''com6.Parameters.Add("@SOURCEZIPFILE", Data.SqlDbType.VarChar, 50).Value = dr(7)
                                    '        'com6.Parameters.Add("@RUNNING_BALANCE", Data.SqlDbType.Decimal, 18).Value = Val(amount2)
                                    '        ''com6.Parameters.Add("@BACKUPDATE", Data.SqlDbType.VarChar, 50).Value = "" 'FormatDateTime(dr("tran_date_bal"), DateFormat.ShortDate)
                                    '        ''com6.Parameters.Add("@GENERATED", Data.SqlDbType.Int, 4).Value = 0 'dr(10)
                                    '        ''com6.Parameters.Add("@ACKNOWLEDGED", Data.SqlDbType.Int, 4).Value = 0 'dr(11) 'Date.Now 'CDate(Session("MyLastLogin")) 'MyLastLoginDate 
                                    '        ''com6.Parameters.Add("@pstd_user", Data.SqlDbType.VarChar, 50).Value = dr(12)
                                    '        ''com6.Parameters.Add("@SOLID", Data.SqlDbType.VarChar, 50).Value = cmbbranch.SelectedValue
                                    '        ''com6.Parameters.Add("@Datecreated", Data.SqlDbType.VarChar, 50).Value = Date.Now
                                    '        ''com6.Parameters.Add("@Empno", Data.SqlDbType.VarChar, 50).Value = txtempid.Text
                                    '        ''com6.Parameters.Add("@Email", Data.SqlDbType.VarChar, 50).Value = txtemail.Text.Trim.ToLower
                                    '        'com6.ExecuteNonQuery()
                                    '        'msg = "Etax Closing balance statement value has been inserted successfully"
                                    '        'logMessage(msg, 0, "Info", "None", "All", "None", 0)
                                    '        'EventLog1.WriteEntry("Finished updating running_balances table for Etax running balances statement", EventLogEntryType.Information)

                                    '        'stract = "INSERT INTO BANKSTATEMENT_TEMP(ITEM_TYPE,BENE_ACCOUNT,STATEMENT_DATE,VALUE_DATE,AMOUNT,ENTRY_TYPE,PRN,OUR_REF,USER_REF,DR_REF,ACTION_ONENTRY)VALUES(" & _
                                    '        '"'" & itemType & "','" & dr("FORACID") & "','" & dr("tran_date") & "','" & dr("value_date") & "'," & amount & ",'" & dr("part_tran_type") & "R','" & dr("tran_particular") & "','" & dr(1) & "','','" & dr("tran_date_bal") & "','CLEANED')"
                                    '        'com1.CommandText = stract
                                    '        'com1.ExecuteNonQuery()
                                    '        '=================================================================

                                    '        com6.CommandType = Data.CommandType.StoredProcedure
                                    '        com6.Parameters.Clear()
                                    '        'com6.Parameters.Add("@ITEM_TYPE", Data.SqlDbType.VarChar, 50).Value = itemType '"OpeningBalance" 'itemType '" & itemType & " 'txtusername.Text.Trim.ToLower
                                    '        'com6.Parameters.Add("@BENE_ACCOUNT", Data.SqlDbType.VarChar, 50).Value = Sqldr2("BENE_ACCOUNT") 'txtfullname.Text
                                    '        'com6.Parameters.Add("@STATEMENT_DATE", Data.SqlDbType.VarChar, 50).Value = FormatDateTime(Sqldr2("STATEMENT_DATE"), DateFormat.ShortDate)
                                    '        'com6.Parameters.Add("@AMOUNT", Data.SqlDbType.Decimal, 18).Value = Sqldr2("amount") 'Val(Sqldr2("amount")) 'FormatDateTime(dr("tran_date"), DateFormat.ShortDate) 'CDate(txtChangedate.Text) 'DateAdd(Day" & DateDiff(DateInterval.Day, dt, DateTime.Now & ", " & DateTime.Now & ")
                                    '        'com6.Parameters.Add("@ENTRY_TYPE", Data.SqlDbType.VarChar, 50).Value = Sqldr2("ENTRY_TYPE") '& "R"
                                    '        'com6.Parameters.Add("@PRN", Data.SqlDbType.VarChar, 50).Value = Sqldr2("PRN") 'Val(dr(3))  'txtExpirydays.Text
                                    '        'com6.Parameters.Add("@OUR_REF", Data.SqlDbType.VarChar, 50).Value = Sqldr2("OUR_REF")
                                    '        'com6.Parameters.Add("@USER_REF", Data.SqlDbType.VarChar, 50).Value = IIf(IsDBNull(Sqldr2("USER_REF")) = True, "", Sqldr2("USER_REF")) 'FormatDateTime(dr(5), DateFormat.ShortDate)
                                    '        'com6.Parameters.Add("@DR_REF", Data.SqlDbType.VarChar, 50).Value = Sqldr2("DR_REF")
                                    '        'com6.Parameters.Add("@VALUE_DATE", Data.SqlDbType.VarChar, 50).Value = FormatDateTime(Sqldr2("VALUE_DATE"), DateFormat.ShortDate) 'IIf(chkSystemAdmin.Checked = True, 1, 0)
                                    '        'com6.Parameters.Add("@ACTION_ONENTRY", Data.SqlDbType.VarChar, 50).Value = Sqldr2("ACTION_ONENTRY") 'IIf(chkhaschanged.Checked = True, 1, 0)
                                    '        ''com6.Parameters.Add("@SOURCEZIPFILE", Data.SqlDbType.VarChar, 50).Value = dr(7)
                                    '        'com6.Parameters.Add("@RUNNING_BALANCE", Data.SqlDbType.Decimal, 18).Value = amount2 'Val(amount2)
                                    '        'com6.Parameters.Add("@BACKUPDATE", Data.SqlDbType.VarChar, 50).Value = "" 'FormatDateTime(dr("tran_date_bal"), DateFormat.ShortDate)
                                    '        'com6.Parameters.Add("@GENERATED", Data.SqlDbType.Int, 4).Value = 0 'dr(10)
                                    '        'com6.Parameters.Add("@ACKNOWLEDGED", Data.SqlDbType.Int, 4).Value = 0 'dr(11) 'Date.Now 'CDate(Session("MyLastLogin")) 'MyLastLoginDate 
                                    '        'com6.Parameters.Add("@pstd_user", Data.SqlDbType.VarChar, 50).Value = dr(12)
                                    '        'com6.Parameters.Add("@SOLID", Data.SqlDbType.VarChar, 50).Value = cmbbranch.SelectedValue
                                    '        'com6.Parameters.Add("@Datecreated", Data.SqlDbType.VarChar, 50).Value = Date.Now
                                    '        'com6.Parameters.Add("@Empno", Data.SqlDbType.VarChar, 50).Value = txtempid.Text
                                    '        'com6.Parameters.Add("@Email", Data.SqlDbType.VarChar, 50).Value = txtemail.Text.Trim.ToLower

                                    '        com6.Parameters.Add("@ITEM_TYPE", Data.SqlDbType.VarChar, 50).Value = itemType '" & itemType & " 'txtusername.Text.Trim.ToLower
                                    '        com6.Parameters.Add("@foracid", Data.SqlDbType.VarChar, 50).Value = Sqldr2("foracid") 'strNWSCAccount 'dr("FORACID") 'txtfullname.Text
                                    '        com6.Parameters.Add("@acct_name", Data.SqlDbType.VarChar, 50).Value = Sqldr2("acct_name") 'IIf(IsDBNull(Sqldr2("acct_name")) = True, "", Sqldr2("acct_name")) 'Sqldr2("acct_name") '"" 'dr("substr(acct_name,1,35)") 'txtfullname.Text
                                    '        com6.Parameters.Add("@tran_id", Data.SqlDbType.VarChar, 50).Value = Sqldr2("tran_id") '1 'dr("trim(htd.tran_id)") 'FormatDateTime(dr("tran_date"), DateFormat.ShortDate)
                                    '        com6.Parameters.Add("@crncy_code", Data.SqlDbType.VarChar, 50).Value = Sqldr2("crncy_code") 'IIf(IsDBNull(Sqldr2("crncy_code")) = True, "", Sqldr2("crncy_code")) 'Sqldr2("crncy_code") '"" 'dr("acct_crncy_code") 'Val(amount2) 'FormatDateTime(dr("tran_date"), DateFormat.ShortDate) 'CDate(txtChangedate.Text) 'DateAdd(Day" & DateDiff(DateInterval.Day, dt, DateTime.Now & ", " & DateTime.Now & ")
                                    '        com6.Parameters.Add("@debits", Data.SqlDbType.Decimal, 18).Value = IIf(IsDBNull(Val(Sqldr2("debits"))) = True, "", Val(Sqldr2("debits"))) 'Val(Sqldr2("debits")) 'Val("") 'Val("sum(htd.tran_amt * decode(htd.part_tran_type,'D',1,0))") 'dr("part_tran_type") & "R"
                                    '        com6.Parameters.Add("@credits", Data.SqlDbType.Decimal, 18).Value = IIf(IsDBNull(Val(Sqldr2("credits"))) = True, "", Val(Sqldr2("credits"))) 'Val(Sqldr2("credits")) 'Val("") 'Val("sum(htd.tran_amt * decode(htd.part_tran_type,'C',1,0))") 'dr("tran_particular") 'Val(dr(3))  'txtExpirydays.Text
                                    '        com6.Parameters.Add("@amount", Data.SqlDbType.Decimal, 18).Value = Val(Sqldr2("amount")) 'Val(BBF) 'Val(amount) 'dr("tran_particular") 'Val(dr(3))  'txtExpirydays.Text
                                    '        com6.Parameters.Add("@tran_date", Data.SqlDbType.VarChar, 50).Value = FormatDateTime(Sqldr2("tran_date"), DateFormat.ShortDate) 'FormatDateTime(prcDate, DateFormat.ShortDate) 'FormatDateTime(dr("tran_date"), DateFormat.ShortDate) 'dr("trim(tran_id)")
                                    '        com6.Parameters.Add("@value_date", Data.SqlDbType.VarChar, 50).Value = FormatDateTime(Sqldr2("value_date"), DateFormat.ShortDate) 'FormatDateTime(prcDate, DateFormat.ShortDate) 'FormatDateTime(dr("value_date"), DateFormat.ShortDate) '"" 'FormatDateTime(dr(5), DateFormat.ShortDate)
                                    '        com6.Parameters.Add("@glshc", Data.SqlDbType.VarChar, 50).Value = Sqldr2("glshc") 'IIf(IsDBNull(Sqldr2("glshc")) = True, "", Sqldr2("glshc")) 'Sqldr2("glshc") '"" 'dr("htd.gl_sub_head_code") 'dr("tran_date_bal")
                                    '        com6.Parameters.Add("@scheme_code", Data.SqlDbType.VarChar, 50).Value = Sqldr2("scheme_code") 'IIf(IsDBNull(Sqldr2("scheme_code")) = True, "", Sqldr2("scheme_code")) 'Sqldr2("scheme_code") '"" 'dr("schm_code") 'FormatDateTime(dr("value_date"), DateFormat.ShortDate) 'IIf(chkSystemAdmin.Checked = True, 1, 0)
                                    '        com6.Parameters.Add("@part_tran_type", Data.SqlDbType.VarChar, 50).Value = Sqldr2("part_tran_type") 'IIf(IsDBNull(Sqldr2("part_tran_type")) = True, "", Sqldr2("part_tran_type")) 'Sqldr2("part_tran_type") '"" 'dr("part_tran_type") '& "R"'"CLEANED" 'IIf(chkhaschanged.Checked = True, 1, 0)
                                    '        com6.Parameters.Add("@sub_particular", Data.SqlDbType.VarChar, 50).Value = Sqldr2("sub_particular") '"ACL2 " & Format(prcDate, "ddMMyy") 'dr("substr(tran_particular,3,14)")
                                    '        com6.Parameters.Add("@particulars", Data.SqlDbType.VarChar, 50).Value = Sqldr2("particulars") '"ACL2 " & Format(prcDate, "ddMMyy") 'dr("tran_particular")
                                    '        com6.Parameters.Add("@remarks", Data.SqlDbType.VarChar, 50).Value = Sqldr2("remarks") '"ACL2 " & Format(prcDate, "ddMMyy") 'dr("tran_rmks") '"" 'FormatDateTime(dr("tran_date_bal"), DateFormat.ShortDate)
                                    '        com6.Parameters.Add("@entry_id", Data.SqlDbType.VarChar, 50).Value = Sqldr2("entry_id") 'IIf(IsDBNull(Sqldr2("entry_id")) = True, "", Sqldr2("entry_id")) 'Sqldr2("entry_id") '"" 'dr("entry_user_Id")
                                    '        com6.Parameters.Add("@pstd_id", Data.SqlDbType.VarChar, 50).Value = Sqldr2("pstd_id") 'IIf(IsDBNull(Sqldr2("pstd_id")) = True, "", Sqldr2("pstd_id")) 'Sqldr2("pstd_id") '"" 'dr("pstd_user_id")
                                    '        com6.Parameters.Add("@RUNNING_BALANCE", Data.SqlDbType.Decimal, 18).Value = amount2 'Val(amount2)
                                    '        com6.Parameters.Add("@customer_ref", Data.SqlDbType.VarChar, 50).Value = Sqldrcollect("customer_ref") '"" 'Sqldr2("remarks") '"ACL2 " & Format(prcDate, "ddMMyy") 'dr("tran_rmks") '"" 'FormatDateTime(dr("tran_date_bal"), DateFormat.ShortDate)
                                    '        com6.Parameters.Add("@customer_name", Data.SqlDbType.VarChar, 50).Value = Sqldrcollect("customer_name") '"" 'IIf(IsDBNull(Sqldr2("entry_id")) = True, "", Sqldr2("entry_id")) 'Sqldr2("entry_id") '"" 'dr("entry_user_Id")
                                    '        com6.Parameters.Add("@property_ref", Data.SqlDbType.VarChar, 50).Value = Sqldrcollect("property_ref") '"" 'IIf(IsDBNull(Sqldr2("pstd_id")) = True, "", Sqldr2("pstd_id")) 'Sqldr2("pstd_id") '"" 'dr("pstd_user_id")

                                    '        com6.ExecuteNonQuery()

                                    '    End While
                                    '    Sqldrcollect.Close()

                                    'Else
                                    '    'Don't insert any data, just exit the entire function or sub says Leonjobs
                                    'End If
                                    ''Sqldrcollect.Close()
                                    '================================================================

                                End If


                                '=============




                            End If
                            Sqldr3.Close()
                            checklogs()
                        Loop
                        '===========================================
                        'Dim StrRecs24 As String = Nothing
                        'Dim Sqlcom4 As New SqlCommand("", com)
                        'Dim Sqldr4 As SqlDataReader ' = Sqlcom.ExecuteReader
                        'StrRecs24 = "SELECT DISTINCT * FROM BANKSTATEMENT_TEMP WHERE ITEM_TYPE= 'BBF' "  '" & dr("tran_particular") & "'"
                        'Sqlcom4.CommandText = StrRecs24
                        'Sqldr4 = Sqlcom4.ExecuteReader
                        'If Sqldr4.HasRows Then
                        '    Do While Sqldr4.Read
                        '        com6.CommandType = Data.CommandType.StoredProcedure
                        '        com6.Parameters.Clear()
                        '        com6.Parameters.Add("@ITEM_TYPE", Data.SqlDbType.VarChar, 50).Value = "OpeningBalance" '" & itemType & " 'txtusername.Text.Trim.ToLower
                        '        com6.Parameters.Add("@BENE_ACCOUNT", Data.SqlDbType.VarChar, 50).Value = Sqldr4("BENE_ACCOUNT") 'txtfullname.Text
                        '        com6.Parameters.Add("@STATEMENT_DATE", Data.SqlDbType.VarChar, 50).Value = FormatDateTime(Sqldr4("STATEMENT_DATE"), DateFormat.ShortDate)
                        '        com6.Parameters.Add("@AMOUNT", Data.SqlDbType.Decimal, 18).Value = Val(runopen) 'FormatDateTime(dr("tran_date"), DateFormat.ShortDate) 'CDate(txtChangedate.Text) 'DateAdd(Day" & DateDiff(DateInterval.Day, dt, DateTime.Now & ", " & DateTime.Now & ")
                        '        com6.Parameters.Add("@ENTRY_TYPE", Data.SqlDbType.VarChar, 50).Value = Sqldr4("ENTRY_TYPE") '& "R"
                        '        com6.Parameters.Add("@PRN", Data.SqlDbType.VarChar, 50).Value = Sqldr4("PRN") 'Val(dr(3))  'txtExpirydays.Text
                        '        com6.Parameters.Add("@OUR_REF", Data.SqlDbType.VarChar, 50).Value = Sqldr4("OUR_REF")
                        '        com6.Parameters.Add("@USER_REF", Data.SqlDbType.VarChar, 50).Value = IIf(IsDBNull(Sqldr4("USER_REF")) = True, "", Sqldr4("USER_REF")) 'FormatDateTime(dr(5), DateFormat.ShortDate)
                        '        com6.Parameters.Add("@DR_REF", Data.SqlDbType.VarChar, 50).Value = Sqldr4("DR_REF")
                        '        com6.Parameters.Add("@VALUE_DATE", Data.SqlDbType.VarChar, 50).Value = FormatDateTime(Sqldr4("VALUE_DATE"), DateFormat.ShortDate) 'IIf(chkSystemAdmin.Checked = True, 1, 0)
                        '        com6.Parameters.Add("@ACTION_ONENTRY", Data.SqlDbType.VarChar, 50).Value = Sqldr4("ACTION_ONENTRY") 'IIf(chkhaschanged.Checked = True, 1, 0)
                        '        'com6.Parameters.Add("@SOURCEZIPFILE", Data.SqlDbType.VarChar, 50).Value = dr(7)
                        '        com6.Parameters.Add("@RUNNING_BALANCE", Data.SqlDbType.Decimal, 18).Value = Val(runopen)
                        '        'com6.Parameters.Add("@BACKUPDATE", Data.SqlDbType.VarChar, 50).Value = "" 'FormatDateTime(dr("tran_date_bal"), DateFormat.ShortDate)
                        '        'com6.Parameters.Add("@GENERATED", Data.SqlDbType.Int, 4).Value = 0 'dr(10)
                        '        'com6.Parameters.Add("@ACKNOWLEDGED", Data.SqlDbType.Int, 4).Value = 0 'dr(11) 'Date.Now 'CDate(Session("MyLastLogin")) 'MyLastLoginDate 
                        '        'com6.Parameters.Add("@pstd_user", Data.SqlDbType.VarChar, 50).Value = dr(12)
                        '        'com6.Parameters.Add("@SOLID", Data.SqlDbType.VarChar, 50).Value = cmbbranch.SelectedValue
                        '        'com6.Parameters.Add("@Datecreated", Data.SqlDbType.VarChar, 50).Value = Date.Now
                        '        'com6.Parameters.Add("@Empno", Data.SqlDbType.VarChar, 50).Value = txtempid.Text
                        '        'com6.Parameters.Add("@Email", Data.SqlDbType.VarChar, 50).Value = txtemail.Text.Trim.ToLower
                        '        com6.ExecuteNonQuery()
                        '    Loop

                        'End If
                        'Sqldr4.Close()

                        '================================================================

                        msg = "Finished inserting data into the nwscbankstatement"
                        logMessage(msg, 0, "Info", "None", "All", "None", 0)
                        EventLog1.WriteEntry("Finished inserting data into the nwscbankstatement table for NWSC running balances statement", EventLogEntryType.Information)

                    End If
                    Sqldr2.Close()
                    insertNWSCACBbalances()
                    msg = "Finished inserting Closing balances data into the nwscbankstatement"
                    logMessage(msg, 0, "Info", "None", "All", "None", 0)
                    'spoolnwscCSVfileStatement()
                    spoolnwscCSVfileStatement()
                    msg = "Finished creating  and sending NWSC CSV file statement"
                    logMessage(msg, 0, "Info", "None", "All", "None", 0)

                End If
                dr.Close()
                oconn.Close()

                '===============================================



                '================================================================

                Try
                    Dim reportname As String = Nothing

                    'Else ' we assume the time is past 2:00 AM  AND WE HAVENT YET RECEIVED THE REGEOD. FOR REASONS SUCH AS THE FACT THAT WE DIDNT HAVE REGISTRATIONS FOR THAT DAY : JOHNSON 06 JAN 2011
                    msg = "checking if eodtimecheck has been met" '& tilbal
                    logMessage(msg, 0, "Info", "None", "All", "None", 0)
                    If FormatDateTime(TimeOfDay, DateFormat.ShortTime) >= FormatDateTime(EODNWSCTIMECHECK, DateFormat.ShortTime) Then
                        msg = "Eodtimecheck has been met" & TimeOfDay & EODNWSCTIMECHECK '& tilbal
                        logMessage(msg, 0, "Info", "None", "All", "None", 0)

                        Dim stract13 As String = "select * from generation_nwsc where generation_date='" & FormatDateTime(Date.Now, DateFormat.ShortDate) & "'"
                        ''" & FormatDateTime(Date.Now, DateFormat.ShortDate) & "'
                        Dim com7 As New SqlCommand(stract13, com)
                        Dim ddr2 As SqlDataReader = com7.ExecuteReader
                        If ddr2.HasRows Then
                            'Do not generate statement as it has already been generated
                            msg = "Do not generate statement as it has already been generated" '& tilbal
                            logMessage(msg, 0, "Info", "None", "All", "None", 0)
                            'Exit Try
                            Exit Sub

                        Else
                            'Generate nwsc statement as none has been generated
                            msg = "Generate Etax running balances statement as none has been generated" '& tilbal
                            logMessage(msg, 0, "Info", "None", "All", "None", 0)
                            'generateStatementFile()
                            'EventLog1.WriteEntry("Finished Generating nwscstatement file", EventLogEntryType.Information)
                            Try
                                'getyestonwscrunningbalstatement()
                                Dim nwscreportname As String = Nothing
                                nwscreportname = getyestonwscrunningbalstatement()
                                'call a function to send email
                                If nwscreportname = "No" Then 'getyestonwscrunningbalstatement() = "" Then
                                    'Do not send statement as no file has been created
                                    msg = "NO email notification will be sent for NWSC running balances statement file as no file was created" '& tilbal
                                    logMessage(msg, 0, "Info", "None", "All", "None", 0)

                                    Exit Try
                                    Exit Sub


                                Else
                                    msg = "NWSC running balance statement has been generated successfully" '& reportname
                                    logMessage(msg, 0, "Info", "None", "All", "None", 0)
                                    'start sending the email notification to URA & UBA guys

                                    msg = "Going to send email notification for NWSC running balances statement file as file has been created" '& tilbal
                                    logMessage(msg, 0, "Info", "None", "All", "None", 0)
                                    Dim stracta3 As String = "select * from sent_nwsc where sent_date='" & FormatDateTime(Date.Now, DateFormat.ShortDate) & "'"
                                    ''" & FormatDateTime(Date.Now, DateFormat.ShortDate) & "'
                                    Dim coma7 As New SqlCommand(stracta3, com)
                                    Dim startacta5 As String = "insert into sent_nwsc values ('" & FormatDateTime(Date.Now, DateFormat.ShortDate) & "',1)"
                                    Dim coma8 As New SqlCommand(startacta5, com)
                                    Dim dra2 As SqlDataReader = coma7.ExecuteReader

                                    If dra2.HasRows Then
                                        'Do not send statement as it has already been sent
                                        msg = "No need to send email notification for nwsc statement file" '& tilbal
                                        logMessage(msg, 0, "Error", "Critical", "All", "None", 0)
                                        EventLog1.WriteEntry("Did not send notification Email for nwsc statement, file already generated", EventLogEntryType.Information)

                                        Exit Try
                                        Exit Sub


                                    Else
                                        'Send nwsc statement as none has been sent
                                        'generateStatementFile()
                                        ''EventLog1.WriteEntry("Finished Generating nwscstatement file", EventLogEntryType.Information)
                                        'Sendnwscstatement()
                                        'EventLog1.WriteEntry("Finished Sending notification Email for nwsc statement", EventLogEntryType.Information)
                                        'com8.ExecuteNonQuery()
                                        Try
                                            'SendEtaxrunningbalancesstatement(reportname)
                                            Sendnwscrunningbalstatement()
                                            msg = "NWSC running balance statement email notification has been sent successfully"
                                            logMessage(msg, 0, "Info", "None", "All", "None", 0)
                                            EventLog1.WriteEntry("Finished Sending notification Email for NWSC running balances statement", EventLogEntryType.Information)
                                            coma8.ExecuteNonQuery()

                                        Catch ex As Exception
                                            msg = "Failed Sending notification Email for NWSC running balances statement with a Reason " & ex.Message
                                            logMessage(msg, 1, "Error", "Critical", "All", "Check the error in the message. Check data generated by the view vw_ura_statement to be correct", 1)

                                            EventLog1.WriteEntry("Failed Sending notification Email for NWSC running balances statement", EventLogEntryType.Information)
                                            Exit Try

                                        End Try


                                        'Try
                                        '    Sendnwscstatement()
                                        '    EventLog1.WriteEntry("Finished Sending notification Email for nwsc statement", EventLogEntryType.Information)
                                        '    coma8.ExecuteNonQuery()
                                        'Catch ex As Exception
                                        '    'generateStatementFile()
                                        '    msg = "Failed Sending notification Email for nwsc statement with a Reason " & ex.Message
                                        '    logMessage(msg, 1, "Error", "Critical", "All", "Check the error in the message. Check data generated by the view vw_ura_statement to be correct", 1)

                                        '    EventLog1.WriteEntry("Failed Sending notification Email for nwsc statement", EventLogEntryType.Information)
                                        '    Exit Try


                                    End If
                                    dra2.Close()
                                    dra2 = Nothing
                                End If


                            Catch ex As Exception
                                'generateStatementFile()
                                msg = "Error Generating NWSC statement file" & ex.Message  '"Established connection to Finnacle and spooled data"
                                logMessage(msg, 0, "Error", "Critical", "All", "None", 0)
                                EventLog1.WriteEntry("Failed Generating NWSC statement file", EventLogEntryType.Information)
                                Exit Sub

                            End Try

                        End If
                        ddr2.Close()
                        ddr2 = Nothing


                    Else
                        msg = "Eodtimecheck has not been met" & TimeOfDay & EODNWSCTIMECHECK '& tilbal
                        logMessage(msg, 0, "Error", "Critical", "All", "None", 0)

                        Exit Sub
                    End If

                Catch ex As Exception

                End Try

                com.Close()
            Catch ex As Exception
                msg = "Error connecting to core banking" & ex.Message  '"Established connection to Finnacle and spooled data"
                logMessage(msg, 0, "Error", "Critical", "All", "None", 0)

            End Try


        End If

        
    End Sub