Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Text
Imports System.Web
Imports System.Web.UI
Imports System.Web.UI.WebControls
Imports Microsoft.VisualBasic


Public MustInherit Class StructureDAL
    Inherits getCommonData
    Protected daHeader, daDetails, daAddress As SqlDataAdapter
    Protected intUID As String
    Public MustOverride Function getAdd(ByVal strid As String, ByVal strAddrid As String) As String
    Public MustOverride Sub getData(ByVal userid As String, ByVal dg As DataGrid)
    Public MustOverride Sub getcmbData(ByVal strid As String, ByVal ddshipname As DropDownList)
    Protected MustOverride Function getcmbadd(ByVal strId As String) As Hashtable
    Public MustOverride Sub getShopCart(ByVal intUID As String)
    Protected MustOverride Sub getTable()
    Protected MustOverride Function getBillNo(ByVal strtype As String) As Int32

End Class

Public Class BillingModule
    Inherits StructureDAL
    Dim con As New SqlConnection(ConfigurationManager.AppSettings("ConnectionString"))
    Dim k As Integer = 0
    '**************************** Get the Address from the Buyer Table ***************************************
    Public Overrides Function getAdd(ByVal strid As String, ByVal strAddrid As String) As String
        cmd = New SqlClient.SqlCommand("AddressBook_Proc", con)
        cmd.CommandType = CommandType.StoredProcedure
        cmd.Parameters.AddWithValue("@uid", strid.Trim)
        cmd.Parameters.AddWithValue("@addid", strAddrid.Trim)
        cmd.Parameters.AddWithValue("@SCriteria", "P")
        con.Open()
        dr = cmd.ExecuteReader()
        If dr.Read = True Then
            Dim strAdd As New StringBuilder()
            strAdd.Append(dr.Item("AddressBook_UserName").ToString & "," & "<br>")
            strAdd.Append(dr.Item("AddressBook_Add1").ToString & "," & " " & "<br>")
            strAdd.Append(dr.Item("City_Name").ToString & " " & "-" & " ")
            strAdd.Append(dr.Item("AddressBook_Zipcode") & "<br>")
            strAdd.Append(dr.Item("State_Name") & "<br>" & dr.Item("Country_Name") & "<br>")
            strAdd.Append("<b>TelePhone No. : " & "</b> " & dr.Item("AddressBook_TelNumber") & "<br>")
            strAdd.Append("<b>Email ID: " & "</b> ")
            strAdd.Append("<a href=mailto:" & dr.Item("AddressBook_Email") & " > " & dr.Item("AddressBook_Email") & "</a>")
            strAdd.ToString()
            con.Close()
            Return strAdd.ToString()
            strAdd = Nothing
        Else
            con.Close()
            Return ""
        End If
        con.Close()
    End Function

    '**********************Get the Address from the AddressBook Table for a particular user *******************
    Public Overrides Sub getData(ByVal userid As String, ByVal dg As DataGrid)
        cmd = New SqlClient.SqlCommand("getAddress", con)
        cmd.CommandType = CommandType.StoredProcedure
        cmd.Parameters.AddWithValue("@uid", userid.Trim)
        cmd.Parameters.AddWithValue("@addid", "0")
        cmd.Parameters.AddWithValue("@SCriteria", "A")
        da = New SqlClient.SqlDataAdapter(cmd)
        da.Fill(ds, "AddBook")
        dg.DataSource = ds.Tables("AddBook")
        dg.DataBind()
    End Sub

    '************** Get the Data for Changing the  shipping Address for Single shipping in combobox**********
    Public Overrides Sub getcmbData(ByVal strid As String, ByVal ddshipname As DropDownList)
        'Dim con As New SqlConnection(ConfigurationManager.AppSettings("ConnectionString"))
        'cmd = New SqlClient.SqlCommand("AddressBook_Proc", con)
        'cmd.CommandType = CommandType.StoredProcedure
        'cmd.Parameters.AddWithValue("@uid", strid.Trim)
        'cmd.Parameters.AddWithValue("@addid", "0")
        'cmd.Parameters.AddWithValue("@SCriteria", "A")
        'If con.State = ConnectionState.Open Then
        '    con.Close()
        'End If
        'con.Open()
        'ddshipname.Items.Clear()
        '' Insert Blank Row at index 0  

        'dr = cmd.ExecuteReader()
        'ddshipname.DataSource = dr

        'ddshipname.DataTextField = "AddressBook_UserName"
        'ddshipname.DataValueField = "AddressBook_Kid"
        'ddshipname.Items.Insert(0, New ListItem("Select", "Select"))

        'ddshipname.DataBind()


        Dim strQuery As String = "select AddressBook_Kid,AddressBook_UserName from AddressBook where AddressBook_BuyerID='" & strid & "'"
        Try
            Dim SqlCon As New SqlConnection(ConfigurationManager.AppSettings("ConnectionString"))  ' Declare connection Object
            Dim SqlAdapter As New SqlDataAdapter      ' Declare Dataadapter Object
            Dim SqlDataSet As New Data.DataSet        ' Declare Dataset Object
            Dim DT As New Data.DataTable              ' Declare Datatable Oblect
            Dim sqlCmd As New SqlCommand(strQuery, SqlCon) ' Declare Command Object
            Dim i As Integer

            SqlCon.Open()                                 ' Open Connection
            SqlAdapter.SelectCommand = sqlCmd             ' Set command to Adapter
            SqlAdapter.Fill(SqlDataSet, "TableName")      ' Links Adapter with DataSet
            DT = SqlDataSet.Tables("TableName")           ' Get datatable from dataset
            sqlCmd.ExecuteNonQuery()                      ' Executes Command

            ddshipname.Items.Clear()                             ' Clear the Items of DropDownList  
            ddshipname.Items.Insert(0, New ListItem("Select Name", "Select Name"))     ' Insert Blank Row at index 0  

            For i = 0 To DT.Rows.Count - 1
                ddshipname.Items.Insert(i + 1, New ListItem(DT.Rows(i).Item(1).ToString.Trim, DT.Rows(i).Item(0).ToString.Trim))  '' Add Items(code,Exact Value) into DropDownList
            Next i
            ddshipname.SelectedIndex = ddshipname.Items.IndexOf(ddshipname.Items.FindByValue(""))     '' Assigning 0 th Index to DropDownList

            SqlCon.Close()                                '' Close Connection Object
            SqlCon.Dispose()                              '' Dispose Connection Oblect
            sqlCmd.Dispose()                              '' Dispose command object
            SqlAdapter.Dispose()                          '' Dispose adapter Object
            SqlDataSet.Dispose()                          '' Dispose dataset Object
            DT.Dispose()                                  '' Dispose datatable Object
        Catch ex As Exception
            CreateMessageAlert(ddshipname.Page, ex.Message, "StrKeyVal")     'Display the Error Message
            ddshipname = Nothing
        End Try
        ' dr.Close()
        ' con.Close()
    End Sub

    '********************************* Fills Multiple shipping Exemp and Add *******************************
    Protected Overrides Function getcmbadd(ByVal strId As String) As Hashtable
        Dim hash As New Hashtable()
        cmd = New SqlClient.SqlCommand("AddressBook_Proc", con)
        cmd.CommandType = CommandType.StoredProcedure
        cmd.Parameters.AddWithValue("@uid", strId.Trim)
        cmd.Parameters.AddWithValue("@addid", "0")
        cmd.Parameters.AddWithValue("@SCriteria", "A")
        If con.State = ConnectionState.Open Then
            con.Close()
        End If
        con.Open()
        dr = cmd.ExecuteReader()
        hash.Add(1, "Billing Address")
        While dr.Read = True
            hash.Add(dr.Item("AddressBook_Kid"), dr.Item("AddressBook_UserName") & " - " & dr.Item("Country_Name"))
        End While
        dr.Close()
        con.Close()
        Return hash
    End Function

    '********************************* get Original Shop Cart Table ********************************************
    Public Overrides Sub getShopCart(ByVal intUID As String)
        If Not ds.Tables("UserShopCart") Is Nothing Then
            ds.Tables("UserShopCart").Clear()
        End If
        cmd = New SqlCommand("getShopCart", con)
        cmd.CommandType = CommandType.StoredProcedure
        cmd.Parameters.AddWithValue("@ShopCart_BuyerUserId", intUID)
        If con.State = ConnectionState.Open Then
            con.Close()
        End If
        da = New SqlDataAdapter(cmd)
        da.Fill(ds, "UserShopCart")
    End Sub

    '******************************** Gets the Reqd. tables for billing ***************************************
    Protected Overrides Sub getTable()
        Dim strBNo As String = "@#$@#$@#$@#$"
        If con.State = ConnectionState.Open Then
            con.Close()
        End If
        If Not ds.Tables("Header") Is Nothing Then
            ds.Tables("Header").Clear()
        End If
        sql = "Select * from BillingHeader where BillingHeader_BillNo='" & strBNo.Trim & "'"
        daHeader = New SqlClient.SqlDataAdapter(sql, con)
        daHeader.Fill(ds, "Header")

        If Not ds.Tables("BD") Is Nothing Then
            ds.Tables("BD").Clear()
        End If
        sql = "Select * from BillDetails where BillDetails_BillNo='" & strBNo.Trim & "'"
        daDetails = New SqlClient.SqlDataAdapter(sql, con)
        daDetails.Fill(ds, "BD")

        If Not ds.Tables("Address") Is Nothing Then
            ds.Tables("Address").Clear()
        End If
        sql = "Select * from BillingAddress where BillingAddress_BillNo='" & strBNo.Trim & "'"
        daAddress = New SqlClient.SqlDataAdapter(sql, con)
        daAddress.Fill(ds, "Address")
    End Sub

    '***************************** Gets the BillNo for the Current Bill ******************************************
    Protected Overrides Function getBillNo(ByVal strtype As String) As Int32
        Dim intTOB As Int32
        strtype = "I"
        sql = "Select top 1 BillingHeader_IncNo from BillingHeader where BillingHeader_TypeofBill=@TOB order By BillingHeader_IncNo Desc"
        cmd = New SqlClient.SqlCommand(sql, con)
        cmd.Parameters.AddWithValue("@TOB", "I")
        If con.State = ConnectionState.Open Then
            con.Close()
        End If
        con.Open()
        dr = cmd.ExecuteReader(CommandBehavior.CloseConnection)
        If dr.Read = True Then
            intTOB = dr.Item("BillingHeader_IncNo")
        Else
            intTOB = 0
        End If
        dr.Close()
        con.Close()
        Return intTOB
    End Function

    'Protected Function CalTax1(ByVal BuyerId As String, ByVal strTos As String, ByVal GvMultipleShip As GridView, ByVal GvTax As GridView, ByVal GvOctroi As GridView, ByVal aspxpage As System.Web.UI.Page)
    '    Try
    '        Dim drRow As DataRow
    '        Dim intAmount As Integer = 0
    '        Dim strBillNo As String
    '        Dim i, j As Int32
    '        Dim strkid As String = NewPrimaryKey(aspxpage)
    '        'BillingAddress_Kid
    '        strBillNo = "AMF/INV/06-07/000" & (Val(getBillNo("I")) + 1)
    '        getTable()

    '        For i = 0 To GvMultipleShip.Rows.Count - 1
    '            drRow = ds.Tables("Address").NewRow
    '            drRow.Item("BillingAddress_Kid") = NewPrimaryKey(aspxpage)
    '            drRow.Item("BillingAddress_Code") = Generate_Code("select count(*) from BillingAddress where BillingAddress_Code='", "BA")
    '            drRow.Item("BillingAddress_AddID") = NewPrimaryKey(aspxpage)
    '            drRow.Item("BillingAddress_BillNo") = strkid.Trim
    '            drRow.Item("BillingAddress_BillDate") = DateTime.Now()
    '            drRow.Item("BillingAddress_BuyerID") = BuyerId
    '            drRow.Item("BillingAddress_ProdID") = GvMultipleShip.Rows(i).Cells(1).Text.Trim
    '            drRow.Item("BillingAddress_ProdQty") = 1
    '            drRow.Item("BillingAddress_ProdRate") = 12
    '            drRow.Item("BillingAddress_ProdAmt") = 123
    '            ''calculation for octroi
    '            drRow.Item("BillingAddress_OctroiPer") = Get1Octroi()
    '            drRow.Item("BillingAddress_OctroiAmt") = 0.0
    '            drRow.Item("BillingAddress_TypeofShip") = "M"
    '            drRow.Item("BillingAddress_ShipAddID") = ""
    '            drRow.Item("BillingAddress_CompanyId") = ""
    '            drRow.Item("BillingAddress_FinancialYearId") = ""
    '            drRow.Item("BillingAddress_UserId") = ""
    '            drRow.Item("BillingAddress_IsDeleted") = ""
    '            ds.Tables("Address").Rows.Add(drRow)
    '        Next

    '        Dim drRow1 As DataRow
    '        For i = 0 To GvMultipleShip.Rows.Count - 1
    '            '********************************BILL DETAIL*************************************************
    '            drRow1 = ds.Tables("BD").NewRow
    '            drRow1.Item("BillDetails_kid") = NewPrimaryKey(aspxpage)
    '            drRow1.Item("BillDetails_code") = Generate_Code("select count(*) from BillDetails where billdetails_code='", "BD")
    '            drRow1.Item("BillDetails_BuyerID") = BuyerId
    '            drRow1.Item("BillDetails_BillNo") = strkid.Trim 'strBillNo.Trim
    '            drRow1.Item("BillDetails_BillDate") = DateTime.Now()
    '            drRow1.Item("BillDetails_SupplierId") = GvMultipleShip.Rows(i).Cells(9).Text.Trim
    '            drRow1.Item("BillDetails_ProdId") = GvMultipleShip.Rows(i).Cells(1).Text.Trim
    '            drRow1.Item("BillDetails_ProdQty") = GvMultipleShip.Rows(i).Cells(2).Text.Trim
    '            drRow1.Item("BillDetails_ProdRate") = GvMultipleShip.Rows(i).Cells(5).Text.Trim
    '            drRow1.Item("BillDetails_ProdAmt") = GvMultipleShip.Rows(i).Cells(6).Text.Trim
    '            drRow1.Item("BillDetails_PFCharge") = Val(drRow1.Item("BillDetails_ProdAmt")) * 5 / 100
    '            'calculate tax amount
    '            For k = 0 To GvTax.Rows.Count - 1
    '                If GvTax.Rows(k).Cells(2).Text.Trim = drRow1.Item("BillDetails_ProdId") Then
    '                    drRow1.Item("BillDetails_TaxRate") = GvTax.Rows(k).Cells(6).Text
    '                    drRow1.Item("BillDetails_TaxAmt") = GvTax.Rows(k).Cells(9).Text
    '                    Exit For
    '                Else
    '                    drRow1.Item("BillDetails_TaxRate") = 0
    '                    drRow1.Item("BillDetails_TaxAmt") = 0
    '                End If
    '            Next
    '            'calculate octroi
    '            If GvOctroi.Rows.Count >= 1 Then
    '                For k = 0 To GvOctroi.Rows.Count - 1
    '                    If GvOctroi.Rows(k).Cells(2).Text.Trim = drRow1.Item("BillDetails_ProdId") Then
    '                        'drRow.Item("BillDetails_TaxRate") = GvTax.Rows(k).Cells(6).Text
    '                        drRow1.Item("BillDetails_OctroiAmount") = GvOctroi.Rows(k).Cells(8).Text.Trim
    '                        Exit For
    '                    Else
    '                        drRow1.Item("BillDetails_OctroiAmount") = 0
    '                    End If
    '                Next
    '            Else
    '                drRow1.Item("BillDetails_OctroiAmount") = 0
    '            End If

    '            drRow1.Item("BillDetails_Freight") = Val(IsDBNull(drRow1.Item("BillDetails_ProdAmt"))) * 5 / 100
    '            drRow1.Item("BillDetails_NetAmount") = Val(IsDBNull(drRow1.Item("BillDetails_ProdAmt"))) + Val(IsDBNull(drRow1.Item("BillDetails_PFCharge"))) + _
    '                                   Val(IsDBNull(drRow1.Item("BillDetails_TaxAmt"))) + Val(IsDBNull(drRow1.Item("BillDetails_OctroiAmount"))) + Val(IsDBNull(drRow1.Item("BillDetails_Freight")))

    '            drRow1.Item("BillDetails_TypeofBill") = "I"
    '            drRow1.Item("BillDetails_Status") = "IO"
    '            drRow1.Item("BillDetails_CompanyId") = ""
    '            drRow1.Item("BillDetails_FinancialYearId") = ""
    '            drRow1.Item("BillDetails_UserID") = ""
    '            drRow1.Item("BillDetails_IsDeleted") = ""
    '            ds.Tables("BD").Rows.Add(drRow1)
    '        Next

    '        Dim drRow2 As DataRow
    '        '*************************************HEADER DATA *********************************************
    '        drRow2 = ds.Tables("Header").NewRow
    '        drRow2.Item("BillingHeader_Kid") = strkid.Trim
    '        drRow2.Item("BillingHeader_Code") = Generate_Code("select count(*) from billingheader where billingheader_code='", "BH")
    '        drRow2.Item("BillingHeader_BillNo") = strBillNo.Trim
    '        drRow2.Item("BillingHeader_BuyerID") = BuyerId
    '        drRow2.Item("BillingHeader_BillDate") = DateTime.Now()
    '        drRow2.Item("BillingHeader_BillGrossAmt") = ds.Tables("BD").Compute("sum(BillDetails_ProdAmt)", "BillDetails_ProdAmt>0")
    '        drRow2.Item("BillingHeader_TaxAmt") = ds.Tables("BD").Compute("sum(BillDetails_TaxAmt)", "BillDetails_TaxAmt>=0")
    '        drRow2.Item("BillingHeader_OctroiAmt") = ds.Tables("BD").Compute("sum(BillDetails_OctroiAmount)", "BillDetails_OctroiAmount>=0")
    '        drRow2.Item("BillingHeader_PFCharge") = ds.Tables("BD").Compute("sum(BillDetails_PFCharge)", "BillDetails_ProdAmt>0")
    '        drRow2.Item("BillingHeader_Freight") = ds.Tables("BD").Compute("sum(BillDetails_Freight)", "BillDetails_ProdAmt>0")
    '        drRow2.Item("BillingHeader_NetAmt") = Val(IsDBNull(drRow2.Item("BillingHeader_BillGrossAmt"))) + Val(IsDBNull(drRow2.Item("BillingHeader_PFCharge")) + Val(IsDBNull(drRow2.Item("BillingHeader_Freight"))) + _
    '                          Val(IsDBNull(drRow2.Item("BillingHeader_TaxAmt"))) + Val(IsDBNull(drRow2.Item("BillingHeader_OctroiAmt"))))

    '        drRow2.Item("BillingHeader_TypeofBill") = "I"
    '        drRow2.Item("BillingHeader_IncNo") = (Val(getBillNo("I")) + 1)
    '        drRow2.Item("BillingHeader_Status") = "Pending"
    '        drRow2.Item("BillingHeader_CompanyId") = " "
    '        drRow2.Item("BillingHeader_FinancialYearId") = " "
    '        drRow2.Item("BillingHeader_UserID") = " "
    '        drRow2.Item("BillingHeader_IsDeleted") = " "
    '        'drRow.Item("BillingHeader_BillGrossAmt") = ds.Tables("BD").Compute("sum(BillDetails_ProdAmt)", "BillDetails_ProdAmt>0")
    '        intAmount = drRow2.Item("BillingHeader_NetAmt")
    '        ds.Tables("Header").Rows.Add(drRow2)
    '        'Update All Three Billing Tables
    '        updateBillTables()
    '        cmd = New SqlCommand("delete from ShopCart where ShopCart_BuyerUserId='" & intUID & "'", con)
    '        If con.State = ConnectionState.Open Then
    '            con.Close()
    '        End If
    '        con.Open()
    '        cmd.ExecuteNonQuery()
    '        con.Close()
    '        Return intAmount
    '    Catch ex As Exception
    '        ex.Message.ToString()
    '    End Try
    'End Function


    Protected Function CalTax1(ByVal BuyerId As String, ByVal strTos As String, ByVal GvMultipleShip As GridView, ByVal gvvat As GridView, ByVal GvTax As GridView, ByVal GvOctroi As GridView, ByVal aspxpage As System.Web.UI.Page)
        Try
            Dim GvRow As GridViewRow
            Dim OctroiPer As Decimal = 0.0
            Dim OctroiAmt As Decimal = 0.0
            Dim drRow As DataRow
            Dim intAmount As Integer = 0
            Dim strBillNo As String
            Dim i, j As Int32
            Dim strkid As String = NewPrimaryKey(aspxpage)
            'BillingAddress_Kid
            strkid = RemoveLiterals(strkid)
            strBillNo = "PO/INV/06-07/000" & (Val(getBillNo("I")) + 1)
            getTable()
            For i = 0 To GvMultipleShip.Rows.Count - 1
                drRow = ds.Tables("Address").NewRow
                drRow.Item("BillingAddress_Kid") = RemoveLiterals(NewPrimaryKey(aspxpage))
                drRow.Item("BillingAddress_Code") = Generate_Code("select count(*) from BillingAddress where BillingAddress_Code='", "BA")
                drRow.Item("BillingAddress_AddID") = RemoveLiterals(NewPrimaryKey(aspxpage))
                drRow.Item("BillingAddress_BillNo") = strkid.Trim
                drRow.Item("BillingAddress_BillDate") = DateTime.Now()
                drRow.Item("BillingAddress_BuyerID") = BuyerId
                drRow.Item("BillingAddress_ProdID") = GvMultipleShip.Rows(i).Cells(1).Text.Trim
                drRow.Item("BillingAddress_ProdQty") = GvMultipleShip.Rows(i).Cells(2).Text.Trim
                drRow.Item("BillingAddress_ProdRate") = GvMultipleShip.Rows(i).Cells(4).Text.Trim
                Dim lblTotalPrice As Label = CType(gvvat.Rows(i).FindControl("lblTotalPrice"), Label)
                If lblTotalPrice.Text = "" Then
                    drRow.Item("BillingAddress_ProdAmt") = Val(GvMultipleShip.Rows(i).Cells(4).Text.Trim) * GvMultipleShip.Rows(i).Cells(2).Text.Trim
                Else
                    drRow.Item("BillingAddress_ProdAmt") = Val(lblTotalPrice.Text) * GvMultipleShip.Rows(i).Cells(2).Text.Trim

                End If

                'drRow.Item("BillingAddress_ProdAmt") = Val(lblTotalPrice.Text) * GvMultipleShip.Rows(i).Cells(2).Text.Trim
                ''calculation for octroi
                For Each GvRow In GvOctroi.Rows
                    If GvRow.Cells(2).Text = GvMultipleShip.Rows(i).Cells(1).Text.Trim Then
                        Dim chk As New CheckBox
                        chk = CType(GvOctroi.Rows(k).Cells(1).FindControl("Chk2"), CheckBox)
                        If chk.Checked = True Then
                            OctroiPer = GvRow.Cells(5).Text
                            OctroiAmt = GvRow.Cells(9).Text
                        ElseIf chk.Checked = False Then
                            OctroiPer = GvRow.Cells(6).Text
                            OctroiAmt = GvRow.Cells(8).Text
                        End If

                        Exit For
                    End If
                Next
                GvOctroi.Visible = False
                '*********************************************************************************
                drRow.Item("BillingAddress_OctroiPer") = OctroiPer
                drRow.Item("BillingAddress_OctroiAmt") = OctroiAmt
                'drRow.Item("BillingAddress_OctroiPer") = Get1Octroi()
                'drRow.Item("BillingAddress_OctroiAmt") = 0.0
                drRow.Item("BillingAddress_TypeofShip") = "M"
                drRow.Item("BillingAddress_ShipAddID") = GvMultipleShip.Rows(i).Cells(3).Text.Trim
                drRow.Item("BillingAddress_CompanyId") = ""
                drRow.Item("BillingAddress_FinancialYearId") = ""
                drRow.Item("BillingAddress_UserId") = ""
                drRow.Item("BillingAddress_IsDeleted") = ""
                ds.Tables("Address").Rows.Add(drRow)
            Next
            Dim drRow1 As DataRow
            For i = 0 To GvMultipleShip.Rows.Count - 1
                '********************************BILL DETAIL*************************************************
                drRow1 = ds.Tables("BD").NewRow
                drRow1.Item("BillDetails_kid") = RemoveLiterals(NewPrimaryKey(aspxpage))
                drRow1.Item("BillDetails_code") = Generate_Code("select count(*) from BillDetails where billdetails_code='", "BD")
                drRow1.Item("BillDetails_BuyerID") = BuyerId
                drRow1.Item("BillDetails_BillNo") = strkid.Trim 'strBillNo.Trim
                drRow1.Item("BillDetails_BillDate") = DateTime.Now()
                drRow1.Item("BillDetails_SupplierId") = GvMultipleShip.Rows(i).Cells(8).Text.Trim
                drRow1.Item("BillDetails_ProdId") = GvMultipleShip.Rows(i).Cells(1).Text.Trim

                Dim qty As Integer = GvMultipleShip.Rows(i).Cells(2).Text.Trim
                Dim lblcst As Label = CType(Gvvat.Rows(i).FindControl("lblCST"), Label)
                Dim lblcstcform As Label = CType(Gvvat.Rows(i).FindControl("lblCSTAgainst"), Label)
                Dim lblTotalPrice As Label = CType(Gvvat.Rows(i).FindControl("lblTotalPrice"), Label)
                Dim lbltotalamt As Label = CType(Gvvat.Rows(i).FindControl("lbltotalamt"), Label)
                Dim lblExciseDuty As Label = CType(gvvat.Rows(i).FindControl("lblExciseDuty"), Label)
                Dim lblEduCsse As Label = CType(Gvvat.Rows(i).FindControl("lblEduCsse"), Label)
                Dim lblvat As Label = CType(Gvvat.Rows(i).FindControl("lblvat"), Label)
                Dim lblAtcomaarPrice As Label = CType(gvvat.Rows(i).FindControl("lblAtcomaarPrice"), Label)
             
                drRow1.Item("BillDetails_ExciteDuty") = FormatNumber(Val(lblExciseDuty.Text) * qty, 2)
                drRow1.Item("BillDetails_EduCess") = FormatNumber(Val(lblEduCsse.Text) * qty, 2)
                drRow1.Item("BillDetails_Vat") = Val(lblvat.Text) * qty
                drRow1.Item("BillDetails_CST") = Val(lblcst.Text) * qty
                drRow1.Item("BillDetails_CSTAgainst") = Val(lblcstcform.Text) * qty
                drRow1.Item("BillDetails_ProdRate") = GvMultipleShip.Rows(i).Cells(4).Text.Trim
                If lblTotalPrice.Text <> "" Then
                    drRow1.Item("BillDetails_ProdAmt") = Val(lblTotalPrice.Text) * qty 'ds.Tables("UserShopCart").Rows(i).Item("ShopCart_NetAmount")
                Else
                    drRow1.Item("BillDetails_ProdAmt") = Val(lblAtcomaarPrice.Text) * qty

                End If

                drRow1.Item("BillDetails_ProdQty") = GvMultipleShip.Rows(i).Cells(2).Text.Trim
                'drRow1.Item("BillDetails_ProdRate") = GvMultipleShip.Rows(i).Cells(4).Text.Trim
                'drRow1.Item("BillDetails_ProdAmt") = GvMultipleShip.Rows(i).Cells(5).Text.Trim

                drRow1.Item("BillDetails_PFCharge") = Val(drRow1.Item("BillDetails_ProdAmt")) * 5 / 100
                'calculate tax amount
                'calculation of tax rate and tax amount
                For k = 0 To GvTax.Rows.Count - 1
                    If GvTax.Rows(k).Cells(2).Text.Trim = drRow1.Item("BillDetails_ProdId") Then
                        'check here P or F
                        Dim chk As New CheckBox
                        chk = CType(GvTax.Rows(k).Cells(1).FindControl("Chk1"), CheckBox)
                        If chk.Checked = True Then
                            Dim lb As Label = CType(GvTax.Rows(k).FindControl("lblproductid"), Label)
                            drRow1.Item("BillDetails_TaxRate") = lb.Text.Trim
                            drRow1.Item("BillDetails_TaxAmt") = GvTax.Rows(k).Cells(10).Text
                        ElseIf chk.Checked = False Then
                            drRow1.Item("BillDetails_TaxRate") = GvTax.Rows(k).Cells(6).Text
                            drRow1.Item("BillDetails_TaxAmt") = GvTax.Rows(k).Cells(9).Text
                        End If
                        Exit For
                    Else
                        drRow1.Item("BillDetails_TaxRate") = 0
                        drRow1.Item("BillDetails_TaxAmt") = 0
                    End If
                Next
                'calculate octroi
                'calculation of octroi rate and octroi amount
                If GvOctroi.Rows.Count >= 1 Then
                    For k = 0 To GvOctroi.Rows.Count - 1
                        If GvOctroi.Rows(k).Cells(2).Text.Trim = drRow1.Item("BillDetails_ProdId") Then
                            Dim chk As New CheckBox
                            chk = CType(GvOctroi.Rows(k).Cells(1).FindControl("Chk2"), CheckBox)
                            'drRow.Item("BillDetails_TaxRate") = GvTax.Rows(k).Cells(6).Text
                            If chk.Checked = True Then
                                drRow1.Item("BillDetails_OctroiAmount") = GvOctroi.Rows(k).Cells(9).Text
                            ElseIf chk.Checked = False Then
                                drRow1.Item("BillDetails_OctroiAmount") = GvOctroi.Rows(k).Cells(8).Text
                            End If
                            'drRow1.Item("BillDetails_OctroiAmount") = GvOctroi.Rows(k).Cells(8).Text.Trim
                            Exit For
                        Else
                            drRow1.Item("BillDetails_OctroiAmount") = 0
                        End If
                    Next
                Else
                    drRow1.Item("BillDetails_OctroiAmount") = 0
                End If

                drRow1.Item("BillDetails_Freight") = Val(drRow1.Item("BillDetails_ProdAmt")) * 5 / 100
                drRow1.Item("BillDetails_NetAmount") = Val(chkDBNull(drRow1.Item("BillDetails_ProdAmt"))) '+ Val(chkDBNull(drRow1.Item("BillDetails_PFCharge"))) + _
                ' Val(chkDBNull(drRow1.Item("BillDetails_TaxAmt"))) + Val(chkDBNull(drRow1.Item("BillDetails_OctroiAmount"))) + Val(chkDBNull(drRow1.Item("BillDetails_Freight")))
                drRow1.Item("BillDetails_ShipAddID") = GvMultipleShip.Rows(i).Cells(3).Text.Trim

                drRow1.Item("BillDetails_TypeofBill") = "I"
                drRow1.Item("BillDetails_Status") = "pending"
                drRow1.Item("BillDetails_CompanyId") = ""
                drRow1.Item("BillDetails_FinancialYearId") = ""
                drRow1.Item("BillDetails_UserID") = ""
                drRow1.Item("BillDetails_IsDeleted") = ""
                ds.Tables("BD").Rows.Add(drRow1)
            Next

            Dim drRow2 As DataRow
            '*************************************HEADER DATA *********************************************
            drRow2 = ds.Tables("Header").NewRow
            drRow2.Item("BillingHeader_Kid") = strkid.Trim
            drRow2.Item("BillingHeader_Code") = Generate_Code("select count(*) from billingheader where billingheader_code='", "BH")
            drRow2.Item("BillingHeader_BillNo") = strBillNo.Trim
            drRow2.Item("BillingHeader_BuyerID") = BuyerId
            drRow2.Item("BillingHeader_BillDate") = DateTime.Now()
            drRow2.Item("BillingHeader_BillGrossAmt") = ds.Tables("BD").Compute("sum(BillDetails_ProdAmt)", "BillDetails_ProdAmt>0")
            drRow2.Item("BillingHeader_TaxAmt") = ds.Tables("BD").Compute("sum(BillDetails_TaxAmt)", "BillDetails_TaxAmt>=0")
            drRow2.Item("BillingHeader_OctroiAmt") = ds.Tables("BD").Compute("sum(BillDetails_OctroiAmount)", "BillDetails_OctroiAmount>=0")
            drRow2.Item("BillingHeader_PFCharge") = ds.Tables("BD").Compute("sum(BillDetails_PFCharge)", "BillDetails_ProdAmt>0")
            drRow2.Item("BillingHeader_Freight") = ds.Tables("BD").Compute("sum(BillDetails_Freight)", "BillDetails_ProdAmt>0")
            drRow2.Item("BillingHeader_NetAmt") = Val(chkDBNull(drRow2.Item("BillingHeader_BillGrossAmt"))) + Val(chkDBNull(drRow2.Item("BillingHeader_PFCharge"))) + Val(chkDBNull(drRow2.Item("BillingHeader_Freight"))) + _
                              Val(chkDBNull(drRow2.Item("BillingHeader_TaxAmt"))) + Val(chkDBNull(drRow2.Item("BillingHeader_OctroiAmt")))

            drRow2.Item("BillingHeader_TypeofBill") = "I"
            drRow2.Item("BillingHeader_IncNo") = (Val(getBillNo("I")) + 1)
            drRow2.Item("BillingHeader_Status") = "Pending"
            drRow2.Item("BillingHeader_CompanyId") = " "
            drRow2.Item("BillingHeader_FinancialYearId") = " "
            drRow2.Item("BillingHeader_UserID") = " "
            drRow2.Item("BillingHeader_IsDeleted") = " "
            'drRow.Item("BillingHeader_BillGrossAmt") = ds.Tables("BD").Compute("sum(BillDetails_ProdAmt)", "BillDetails_ProdAmt>0")
            intAmount = drRow2.Item("BillingHeader_NetAmt")
            ds.Tables("Header").Rows.Add(drRow2)
            'Update All Three Billing Tables
            updateBillTables()
            'cmd = New SqlCommand("delete from ShopCart where ShopCart_BuyerUserId='" & BuyerId & "'", con)
            'If con.State = ConnectionState.Open Then
            '    con.Close()
            'End If
            'con.Open()
            'cmd.ExecuteNonQuery()
            'con.Close()
            Return intAmount
        Catch ex As Exception
            ex.Message.ToString()
        End Try
    End Function

    Private Function Get1Octroi()
        Return 0
    End Function

    '******************************** Inserts Bill Data in respective tables **********************************
    Protected Function CalTax(ByVal UserId As String, ByVal strTOS As String, ByVal intDA As String, _
                          ByVal GvShopCart As GridView, ByVal gvvat As GridView, ByVal GvTax As GridView, ByVal GvOctroi As GridView) As Int32

        Dim Gvrow As GridViewRow

        Dim strBillNo As String
        Dim drRow As DataRow
        Dim i, j, intAmt As Int32
        intUID = UserId
        strBillNo = "PO/INV/06-07/000" & (Val(getBillNo("I")) + 1)
        getTable()
        '*******************************************
        If Not ds.Tables("UserShopCart") Is Nothing Then
            ds.Tables("UserShopCart").Clear()
        End If
        cmd = New SqlCommand("getShopCart", con)
        cmd.CommandType = CommandType.StoredProcedure
        cmd.Parameters.AddWithValue("@ShopCart_BuyerUserId", UserId)
        If con.State = ConnectionState.Open Then
            con.Close()
        End If
        Dim newpage As New System.Web.UI.Page
        Dim strkid As String = NewPrimaryKey(newpage)
        strkid = RemoveLiterals(strkid)
        da = New SqlDataAdapter(cmd)
        da.Fill(ds, "UserShopCart")
        '*******************************************
        getAddressTable(intDA, strTOS.Trim, ds.Tables("UserShopCart"), strkid.Trim, GvOctroi)
        If strTOS.Trim = "M" Then
            getShopCart(intUID)
        End If


        For i = 0 To ds.Tables("UserShopCart").Rows.Count - 1
            drRow = ds.Tables("BD").NewRow
            drRow.Item("BillDetails_kid") = RemoveLiterals(NewPrimaryKey(newpage))
            drRow.Item("BillDetails_code") = Generate_Code("select count(*) from BillDetails where billdetails_code='", "BD")
            drRow.Item("BillDetails_BillNo") = strkid.Trim 'strBillNo.Trim
            drRow.Item("BillDetails_BillDate") = DateTime.Now()
            drRow.Item("BillDetails_BuyerID") = intUID
            drRow.Item("BillDetails_SupplierId") = ds.Tables("UserShopCart").Rows(i).Item("Product_SupplierId")

            drRow.Item("BillDetails_ProdId") = ds.Tables("UserShopCart").Rows(i).Item("Product_Kid")
            drRow.Item("BillDetails_ProdQty") = ds.Tables("UserShopCart").Rows(i).Item("ShopCart_Quantity")
           

            'calculate tax amount
            GvTax.Visible = True
            For k = 0 To GvTax.Rows.Count - 1
                If GvTax.Rows(k).Cells(2).Text.Trim = ds.Tables("UserShopCart").Rows(i).Item("Product_Kid") Then
                    Dim chk As New CheckBox
                    chk = CType(GvTax.Rows(k).Cells(1).FindControl("Chk1"), CheckBox)
                    If chk.Checked = True Then
                        drRow.Item("BillDetails_TaxRate") = GvTax.Rows(k).Cells(7).Text
                        drRow.Item("BillDetails_TaxAmt") = GvTax.Rows(k).Cells(10).Text
                    ElseIf chk.Checked = False Then
                        drRow.Item("BillDetails_TaxRate") = GvTax.Rows(k).Cells(6).Text
                        drRow.Item("BillDetails_TaxAmt") = GvTax.Rows(k).Cells(9).Text
                    End If
                    ' drRow.Item("BillDetails_TaxRate") = GvTax.Rows(k).Cells(6).Text
                    'drRow.Item("BillDetails_TaxAmt") = GvTax.Rows(k).Cells(9).Text
                    Exit For
                Else
                    drRow.Item("BillDetails_TaxRate") = 0
                    drRow.Item("BillDetails_TaxAmt") = 0
                End If
            Next
            GvTax.Visible = False
            'calculate octroi
            If GvOctroi.Rows.Count >= 1 Then
                For k = 0 To GvOctroi.Rows.Count - 1
                    If GvOctroi.Rows(k).Cells(2).Text.Trim = ds.Tables("UserShopCart").Rows(i).Item("Product_Kid") Then
                        'drRow.Item("BillDetails_TaxRate") = GvTax.Rows(k).Cells(6).Text
                        Dim chk As New CheckBox
                        chk = CType(GvOctroi.Rows(k).Cells(1).FindControl("Chk2"), CheckBox)
                        If chk.Checked = True Then
                            drRow.Item("BillDetails_OctroiAmount") = GvOctroi.Rows(k).Cells(9).Text
                        ElseIf chk.Checked = False Then
                            drRow.Item("BillDetails_OctroiAmount") = GvOctroi.Rows(k).Cells(8).Text
                        End If
                        'drRow.Item("BillDetails_OctroiAmount") = GvOctroi.Rows(k).Cells(8).Text.Trim
                        Exit For
                    Else
                        drRow.Item("BillDetails_OctroiAmount") = 0
                    End If
                Next
            Else
                drRow.Item("BillDetails_OctroiAmount") = 0
            End If
            Try
                Dim qty As Integer = ds.Tables("UserShopCart").Rows(i).Item("ShopCart_Quantity").ToString
                Dim lblcst As Label = CType(gvvat.Rows(i).FindControl("lblCST"), Label)
                Dim lblcstcform As Label = CType(gvvat.Rows(i).FindControl("lblCSTAgainst"), Label)
                Dim lblTotalPrice As Label = CType(gvvat.Rows(i).FindControl("lblTotalPrice"), Label)
                Dim lbltotalamt As Label = CType(gvvat.Rows(i).FindControl("lbltotalamt"), Label)
                Dim lblExciseDuty As Label = CType(gvvat.Rows(i).FindControl("lblExciseDuty"), Label)
                Dim lblEduCsse As Label = CType(gvvat.Rows(i).FindControl("lblEduCsse"), Label)
                Dim lblvat As Label = CType(gvvat.Rows(i).FindControl("lblvat"), Label)
                Dim lblAtcomaarPrice As Label = CType(gvvat.Rows(i).FindControl("lblAtcomaarPrice"), Label)
                drRow.Item("BillDetails_ExciteDuty") = FormatNumber(Val(lblExciseDuty.Text) * qty, 2)
                drRow.Item("BillDetails_EduCess") = FormatNumber(Val(lblEduCsse.Text) * qty, 2)
                drRow.Item("BillDetails_Vat") = Val(lblvat.Text) * qty
                drRow.Item("BillDetails_CST") = Val(lblcst.Text) * qty
                drRow.Item("BillDetails_CSTAgainst") = Val(lblcstcform.Text) * qty
                drRow.Item("BillDetails_ProdRate") = ds.Tables("UserShopCart").Rows(i).Item("Product_AtcoPrice")
                If lblTotalPrice.Text = "" Then
                    drRow.Item("BillDetails_ProdAmt") = Val(lblAtcomaarPrice.Text) * qty 'ds.Tables("UserShopCart").Rows(i).Item("ShopCart_NetAmount")
                Else
                    drRow.Item("BillDetails_ProdAmt") = Val(lblTotalPrice.Text) * qty 'ds.Tables("UserShopCart").Rows(i).Item("ShopCart_NetAmount")
                End If
              
            Catch
                drRow.Item("BillDetails_ProdRate") = ds.Tables("UserShopCart").Rows(i).Item("Product_AtcoPrice").ToString
                drRow.Item("BillDetails_ProdAmt") = ds.Tables("UserShopCart").Rows(i).Item("ShopCart_NetAmount").ToString
            End Try
            drRow.Item("BillDetails_PFCharge") = Val(drRow.Item("BillDetails_ProdAmt")) * 5 / 100
            drRow.Item("BillDetails_Freight") = Val(drRow.Item("BillDetails_ProdAmt")) * 5 / 100
            drRow.Item("BillDetails_ShipAddID") = intDA

            drRow.Item("BillDetails_NetAmount") = Val(chkDBNull(drRow.Item("BillDetails_ProdAmt"))) ' + Val(chkDBNull(drRow.Item("BillDetails_PFCharge"))) + _
            'Val(chkDBNull(drRow.Item("BillDetails_TaxAmt"))) + Val(chkDBNull(drRow.Item("BillDetails_OctroiAmount"))) + Val(chkDBNull(drRow.Item("BillDetails_Freight")))

            drRow.Item("BillDetails_TypeofBill") = "I"
            drRow.Item("BillDetails_Status") = "pending"
            ds.Tables("BD").Rows.Add(drRow)
        Next

        '*********************************************Header Data*********************************************
        'calculateTax


        drRow = ds.Tables("Header").NewRow
        drRow.Item("BillingHeader_Kid") = strkid.Trim
        drRow.Item("BillingHeader_Code") = Generate_Code("select count(*) from billingheader where billingheader_code='", "BH")
        drRow.Item("BillingHeader_IncNo") = (Val(getBillNo("I")) + 1)
        drRow.Item("BillingHeader_BillNo") = strBillNo.Trim
        drRow.Item("BillingHeader_BuyerID") = intUID
        drRow.Item("BillingHeader_BillDate") = DateTime.Now()

        'drRow.Item("BillingHeader_BillGrossAmt") = ds.Tables("BD").Compute("sum(BillDetails_ProdAmt)", "BillDetails_ProdAmt>0")
        drRow.Item("BillingHeader_BillGrossAmt") = ds.Tables("BD").Compute("sum(BillDetails_ProdAmt)", "BillDetails_ProdAmt>0")

        drRow.Item("BillingHeader_PFCharge") = ds.Tables("BD").Compute("sum(BillDetails_PFCharge)", "BillDetails_ProdAmt>0")
        drRow.Item("BillingHeader_Freight") = ds.Tables("BD").Compute("sum(BillDetails_Freight)", "BillDetails_ProdAmt>0")
        drRow.Item("BillingHeader_TaxAmt") = ds.Tables("BD").Compute("sum(BillDetails_TaxAmt)", "BillDetails_TaxAmt>=0")
        drRow.Item("BillingHeader_OctroiAmt") = ds.Tables("BD").Compute("sum(BillDetails_OctroiAmount)", "BillDetails_OctroiAmount>=0")
        drRow.Item("BillingHeader_TypeofBill") = "I"
        drRow.Item("BillingHeader_NetAmt") = Val(chkDBNull(drRow.Item("BillingHeader_BillGrossAmt"))) + Val(chkDBNull(drRow.Item("BillingHeader_PFCharge"))) + Val(chkDBNull(drRow.Item("BillingHeader_Freight"))) + _
                   Val(chkDBNull(drRow.Item("BillingHeader_TaxAmt"))) + Val(chkDBNull(drRow.Item("BillingHeader_OctroiAmt")))
        intAmt = chkDBNull(drRow.Item("BillingHeader_NetAmt"))
        ds.Tables("Header").Rows.Add(drRow)
        updateBillTables()
        'cmd = New SqlCommand("delete from ShopCart where ShopCart_BuyerUserId='" & intUID & "'", con)
        'If con.State = ConnectionState.Open Then
        '    con.Close()
        'End If
        'con.Open()
        'cmd.ExecuteNonQuery()
        'con.Close()
        Return intAmt
    End Function




    Public Function CalculateTax(ByVal GvShopCart As GridView, ByVal GvTax As GridView, ByVal GvOctroi As GridView, ByVal lbl As Label) As Decimal
        Dim sb As New StringBuilder
        Dim totalAmount As Decimal = 0.0
        Dim Taxamount As Decimal = 0.0
        Dim OctroiAmount As Decimal = 0.0
        Dim ProductAmount As Decimal = 0.0
        Dim ServiceTax As Decimal = 0.0
        Dim ShipCharges As Decimal = 0.0
        Dim gvRow As GridViewRow
        'Calculate Total of Special Prices of all product
        ProductAmount = NetProdAmount(GvShopCart)
        sb.Append("<b>Net Product Amount" & "   " & "Rs. " & ProductAmount & "<br>")
        'Calculate shipping charges of the product
        'ShipCharges = GetShippingCharges(GvShopCart)
        'sb.Append("<b>Shipping Charges" & "   " & "Rs. " & ShipCharges & "<br>")
        'calculate service tax
        'ServiceTax = GetServiceTax(ProductAmount)
        sb.Append("<b>Service Charges" & "   " & "Rs. " & ServiceTax & "<br>")
        'Calculate All taxes on Product
        For Each gvRow In GvTax.Rows
            Dim chkselect As CheckBox = CType(gvRow.FindControl("Chk1"), CheckBox)
            If chkselect.Checked = True Then
                Taxamount = Taxamount + (Convert.ToDecimal(gvRow.Cells(10).Text))
            ElseIf chkselect.Checked = False Then
                Taxamount = Taxamount + (Convert.ToDecimal(gvRow.Cells(9).Text))
            End If
        Next
        sb.Append("<b>Tax" & "   " & "Rs. " & Taxamount & "<br>")
        'Calculate octroi on all product
        For Each gvRow In GvOctroi.Rows
            Dim chkselect As CheckBox = CType(gvRow.FindControl("Chk2"), CheckBox)
            If chkselect.Checked = True Then
                OctroiAmount = OctroiAmount + Convert.ToDecimal(gvRow.Cells(9).Text)
            ElseIf chkselect.Checked = False Then
                OctroiAmount = OctroiAmount + Convert.ToDecimal(gvRow.Cells(8).Text)
            End If
        Next
        sb.Append("<b>Octroi Charges" & "   " & "Rs. " & OctroiAmount & "<br>")
        'Calculate total Amount with Taxes
        totalAmount = ProductAmount + Taxamount + OctroiAmount + ServiceTax + ShipCharges
        sb.Append("total amount with taxes Rs." & "   " & totalAmount & "</b>" & "<br>")
        lbl.Text = sb.ToString
        Return totalAmount
    End Function



    Private Function NetProdAmount(ByVal gvShopcart As GridView) As Decimal
        Dim prodamt As Decimal = 0.0
        Dim gvRow As GridViewRow
        For Each gvRow In gvShopcart.Rows
            prodamt = prodamt + Convert.ToDecimal(gvRow.Cells(7).Text)
        Next
        Return prodamt
    End Function


    '************************************ Fills the BillingAddress Table Accordingly ***************************
    Private Sub getAddressTable(ByVal intDA As String, ByVal strTOS As String, _
                                ByVal dt As DataTable, ByVal strBNo As String, ByVal gvOctroi As GridView)
        Dim i, j, intOctroi As Int32
        Dim drRow As DataRow
        Dim newpage As New System.Web.UI.Page

        Dim OctroiPer As Integer = 0
        Dim OctroiAmt As Decimal = 0.0
        Dim gvRow As GridViewRow

        If strTOS.Trim = "S" Then

            'intOctroi = getOctroi(intUID, intDA, "AB")
            '*******************************calculation of octroi ***************************
           
          
            '*******************************************************************************
            For i = 0 To ds.Tables("UserShopCart").Rows.Count - 1
                drRow = ds.Tables("Address").NewRow
                drRow.Item("BillingAddress_Kid") = RemoveLiterals(NewPrimaryKey(newpage))
                drRow.Item("BillingAddress_Code") = Generate_Code("select count(*) from BillingAddress where BillingAddress_code='", "BA")
                drRow.Item("BillingAddress_BillNo") = strBNo.Trim
                drRow.Item("BillingAddress_BillDate") = DateTime.Now()
                drRow.Item("BillingAddress_BuyerID") = intUID
                drRow.Item("BillingAddress_ProdID") = ds.Tables("UserShopCart").Rows(i).Item("Product_Kid").ToString
                drRow.Item("BillingAddress_ProdQty") = ds.Tables("UserShopCart").Rows(i).Item("ShopCart_Quantity")
                drRow.Item("BillingAddress_ProdRate") = ds.Tables("UserShopCart").Rows(i).Item("Product_MarketPrice")
                drRow.Item("BillingAddress_ProdAmt") = Val(drRow.Item("BillingAddress_ProdRate")) * Val(drRow.Item("BillingAddress_ProdQty"))
                drRow.Item("BillingAddress_TypeofShip") = "S"
                drRow.Item("BillingAddress_ShipAddID") = intDA
                '****************************************************************************
                For Each gvRow In gvOctroi.Rows
                    If gvRow.Cells(2).Text = ds.Tables("UserShopCart").Rows(i).Item("Product_Kid").ToString Then
                        'OctroiPer = gvRow.Cells(5).Text
                        'OctroiAmt = gvRow.Cells(9).Text
                        Dim chk As New CheckBox
                        chk = CType(gvOctroi.Rows(k).Cells(1).FindControl("Chk2"), CheckBox)
                        If chk.Checked = True Then
                            OctroiPer = gvRow.Cells(5).Text
                            OctroiAmt = gvRow.Cells(9).Text
                        ElseIf chk.Checked = False Then
                            OctroiPer = gvRow.Cells(6).Text
                            OctroiAmt = gvRow.Cells(8).Text
                        End If
                        Exit For
                    End If
                Next
                '*********************************************************************************
                drRow.Item("BillingAddress_OctroiPer") = OctroiPer
                drRow.Item("BillingAddress_OctroiAmt") = OctroiAmt
                drRow.Item("BillingAddress_AddID") = j + 1
                j = Val(drRow.Item("BillingAddress_AddID"))
                ds.Tables("Address").Rows.Add(drRow)
            Next
        ElseIf strTOS.Trim = "M" Then
            For i = 0 To dt.Rows.Count - 1
                drRow = ds.Tables("Address").NewRow
                drRow.Item("BillingAddress_BillNo") = strBNo.Trim
                drRow.Item("BillingAddress_BillDate") = DateTime.Now()
                drRow.Item("BillingAddress_BuyerID") = intUID
                drRow.Item("BillingAddress_ProdID") = dt.Rows(i).Item("ProdID")
                drRow.Item("BillingAddress_ProdQty") = dt.Rows(i).Item("pi_SellQty")
                drRow.Item("BillingAddress_ProdRate") = dt.Rows(i).Item("pi_price")
                drRow.Item("BillingAddress_ProdAmt") = Val(drRow.Item("ProdRate")) * Val(drRow.Item("ProdQty"))
                drRow.Item("BillingAddress_TypeofShip") = "M"
                drRow.Item("BillingAddress_ShipAddID") = dt.Rows(i).Item("TOS")
                'calculation for octroi
                drRow.Item("BillingAddress_OctroiPer") = getOctroi(intUID, dt.Rows(i).Item("TOS"), "AB")
                drRow.Item("BillingAddress_OctroiAmt") = Val(drRow.Item("ProdRate")) * Val(drRow.Item("OctroiPer")) / 100
                drRow.Item("BillingAddress_AddID") = j + 1
                j = Val(drRow.Item("BillingAddress_AddID"))
                ds.Tables("Address").Rows.Add(drRow)
            Next
        End If
    End Sub

    Private Sub GetBillingOctroi(ByVal gvOctroi As GridView, ByVal ProductId As String)
        Dim OctroiPer As Integer = 0
        Dim OctroiAmt As Decimal = 0.0
        Dim gvRow As GridViewRow
        For Each gvRow In gvOctroi.Rows
            If gvRow.Cells(1).Text = ProductId Then
                OctroiPer = gvRow.Cells(5).Text
                OctroiAmt = gvRow.Cells(9).Text
                Exit For
            End If
        Next
    End Sub

    Private Sub updateBillTables()
        updateDataTable(daAddress, ds.Tables("Address"))
        updateDataTable(daDetails, ds.Tables("BD"))
        updateDataTable(daHeader, ds.Tables("Header"))
    End Sub
End Class