Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.IO
'Imports BCL.easyPDF.Interop.EasyPDFPrinter
Partial Class BusinessAssociates_SSA_NewQuotation
    Inherits System.Web.UI.Page
    Dim QueryStr As String
    Dim DtEmployee As New DataTable
    Dim drRow As DataRow
    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        Try
            If (Not IsPostBack) Then
                txtVCompanyID.Text = "f6b59677-9946-489c-8628-465872369428"
                txtSSAID.Text = Session("SSAID").ToString
                tblProduct.Visible = False
                tblProductPrice.Visible = False
                lblSelectProduct.Visible = False
                lblQuotationCart.Visible = False
                tblFinapProducts.Visible = False
                cmdAddToQuot.Visible = False
                cmdViewQuot.Visible = False
                cmdSendQuot.Visible = False
                cmdSearchOtherProd.Visible = False
                cmdGenerateQuot.Visible = False
                FillCustomer()
                BindFirstCategory()
            End If
        Catch ex As Exception
            Response.Write(ex.Message)
        End Try
    End Sub

    Public Sub FillCustomer()
        Dim cn As New System.Data.SqlClient.SqlConnection(ConfigurationManager.AppSettings("Connection4Voffice"))
        Dim objAdapter As SqlDataAdapter
        Dim ObjDS As DataSet

        cn.Open()

        Try
            objAdapter = New SqlDataAdapter("Select_CRMCustomerBySSAIDAndMode_Proc", cn)
            objAdapter.SelectCommand.CommandType = CommandType.StoredProcedure

            objAdapter.SelectCommand.Parameters.Add(New SqlParameter("@CustomerMode", SqlDbType.Int))
            objAdapter.SelectCommand.Parameters.Add(New SqlParameter("@CompanyID", SqlDbType.VarChar))
            objAdapter.SelectCommand.Parameters.Add(New SqlParameter("@SSAID", SqlDbType.VarChar))

            objAdapter.SelectCommand.Parameters("@CustomerMode").Value = 2
            objAdapter.SelectCommand.Parameters("@CompanyID").Value = txtVCompanyID.Text.Trim
            objAdapter.SelectCommand.Parameters("@SSAID").Value = txtSSAID.Text.Trim

            ObjDS = New DataSet
            objAdapter.Fill(ObjDS, "CustomerDetails")

            If ObjDS.Tables(0).Rows.Count > 0 Then
                ddlCustomer.DataSource = ObjDS
                ddlCustomer.DataBind()
                ddlCustomer.Items.Insert(0, New ListItem("Select", "-1"))
            End If
            ObjDS.Clear()
            ObjDS.Dispose()

        Catch ex As Exception
            Response.Write(ex.Message)
        Finally
            cn.Close()
            cn.Dispose()
        End Try
    End Sub

    Public Sub BindTempTable()

        DtEmployee.Columns.Add("ProductID", GetType(String))
        DtEmployee.Columns.Add("ProductCode", GetType(String))
        DtEmployee.Columns.Add("ProductName", GetType(String))
        DtEmployee.Columns.Add("ProductImage", GetType(String))
        DtEmployee.Columns.Add("ProductModelNo", GetType(String))
        DtEmployee.Columns.Add("ProductBrand", GetType(String))
        DtEmployee.Columns.Add("AtcoPrice", GetType(Decimal))
        DtEmployee.Columns.Add("Excise", GetType(Decimal))
        DtEmployee.Columns.Add("EduCess", GetType(Decimal))
        DtEmployee.Columns.Add("VAT", GetType(Decimal))
        DtEmployee.Columns.Add("CST", GetType(Decimal))
        DtEmployee.Columns.Add("CSTC", GetType(Decimal))
        DtEmployee.Columns.Add("Quantity", GetType(Decimal))
        DtEmployee.Columns.Add("TotalPrice", GetType(Decimal))
        DtEmployee.Columns.Add("CategoryID", GetType(String))
    End Sub

    Private Sub BindFirstCategory()
        Try
            Dim DsCategory As New DataSet

            ddlFirstCategory.Items.Clear()

            QueryStr = "select Category_Kid,Category_Name from Category_Master where Category_ParentId='Root Category' and Category_IsDeleted='0' order by category_name asc"

            DsCategory = SelectCategory(QueryStr)

            If DsCategory.Tables(0).Rows.Count > 0 Then
                ddlFirstCategory.DataSource = DsCategory
                ddlFirstCategory.DataBind()
                ddlFirstCategory.Items.Insert(0, New ListItem("Select", "-1"))
                ddlSecondCategory.Items.Clear()
                ddlSecondCategory.Items.Insert(0, New ListItem("Select", "-1"))
                ddlThirdCategory.Items.Clear()
                ddlThirdCategory.Items.Insert(0, New ListItem("Select", "-1"))
            Else
                ddlFirstCategory.Items.Clear()
                ddlFirstCategory.Items.Insert(0, New ListItem("Select", "-1"))
                ddlSecondCategory.Items.Clear()
                ddlSecondCategory.Items.Insert(0, New ListItem("Select", "-1"))
                ddlThirdCategory.Items.Clear()
                ddlThirdCategory.Items.Insert(0, New ListItem("Select", "-1"))
            End If
        Catch ex As Exception
            ex.Message.ToString()
        End Try
    End Sub


    Public Shared Function SelectCategory(ByVal Query As String) As DataSet
        Dim cn As New System.Data.SqlClient.SqlConnection(ConfigurationManager.AppSettings("ConnectionString"))
        Dim objAdapter As SqlDataAdapter
        Dim ObjDS As DataSet

        cn.Open()

        Try
            objAdapter = New SqlDataAdapter(Query, cn)
            objAdapter.SelectCommand.CommandType = CommandType.Text

            ObjDS = New DataSet
            objAdapter.Fill(ObjDS, "Category")

            Return ObjDS

        Catch ex As Exception
            Throw ex
        Finally
            cn.Close()
            cn.Dispose()
        End Try

    End Function



    Protected Sub ddlFirstCategory_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles ddlFirstCategory.SelectedIndexChanged
        If ddlFirstCategory.SelectedIndex > 0 Then

            Dim DsCategory As New DataSet

            QueryStr = "select Category_Kid,Category_Name  from Category_Master where Category_ParentId ='" & ddlFirstCategory.SelectedValue.Trim & "' and Category_IsDeleted='0' "

            DsCategory = SelectCategory(QueryStr)

            If DsCategory.Tables(0).Rows.Count > 0 Then
                ddlSecondCategory.Items.Clear()
                ddlThirdCategory.Items.Clear()
                ddlSecondCategory.DataSource = DsCategory
                ddlSecondCategory.DataBind()
                ddlSecondCategory.Items.Insert(0, New ListItem("Select", "-1"))
                ddlThirdCategory.Items.Clear()
                ddlThirdCategory.Items.Insert(0, New ListItem("Select", "-1"))
            Else
                ddlSecondCategory.Items.Clear()
                ddlSecondCategory.Items.Insert(0, New ListItem("Select", "-1"))
                ddlThirdCategory.Items.Clear()
                ddlThirdCategory.Items.Insert(0, New ListItem("Select", "-1"))

                'Bind Grid With Products"
                tblProduct.Visible = True
                lblSelectProduct.Visible = True
                txtProductID.Text = ddlFirstCategory.SelectedValue
                txtProductCode.Text = ""
                txtKey.Text = ""
                BindProducts()
            End If

        Else
            ddlSecondCategory.Items.Clear()
            ddlSecondCategory.Items.Insert(0, New ListItem("Select", "-1"))
            ddlThirdCategory.Items.Clear()
            ddlThirdCategory.Items.Insert(0, New ListItem("Select", "-1"))
        End If
    End Sub

    Protected Sub ddlSecondCategory_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles ddlSecondCategory.SelectedIndexChanged
        If ddlSecondCategory.SelectedIndex > 0 Then

            Dim DsCategory As New DataSet


            QueryStr = "select Category_Kid,Category_Name  from Category_Master where Category_ParentId ='" & ddlSecondCategory.SelectedValue.Trim & "' and Category_IsDeleted='0' "

            DsCategory = SelectCategory(QueryStr)

            If DsCategory.Tables(0).Rows.Count > 0 Then
                ddlThirdCategory.Items.Clear()
                ddlThirdCategory.DataSource = DsCategory
                ddlThirdCategory.DataBind()
                ddlThirdCategory.Items.Insert(0, New ListItem("Select", "-1"))
            Else
                '   ddlThirdCategory.Items.Clear()
                '  ddlThirdCategory.Items.Insert(0, New ListItem("Select", "-1"))

                'Bind Grid With Products"
                tblProduct.Visible = True
                lblSelectProduct.Visible = True
                txtProductID.Text = ddlSecondCategory.SelectedValue
                txtProductCode.Text = ""
                txtKey.Text = ""
                BindProducts()
            End If

        Else
            ddlThirdCategory.Items.Clear()
            ddlThirdCategory.Items.Insert(0, New ListItem("Select", "-1"))
        End If
    End Sub

    Protected Sub ddlThirdCategory_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles ddlThirdCategory.SelectedIndexChanged
        If ddlThirdCategory.SelectedIndex > 0 Then

            Dim DsCategory As New DataSet

            QueryStr = "select Category_Kid,Category_Name  from Category_Master where Category_ParentId ='" & ddlThirdCategory.SelectedValue.Trim & "' and Category_IsDeleted='0' "

            DsCategory = SelectCategory(QueryStr)

            If DsCategory.Tables(0).Rows.Count > 0 Then
                ddlSecondCategory.Items.Clear()
                ddlSecondCategory.DataSource = DsCategory
                ddlSecondCategory.DataBind()
                ddlSecondCategory.Items.Insert(0, New ListItem("Select", "-1"))
            Else
                'Bind Grid With Products"
                tblProduct.Visible = True
                lblSelectProduct.Visible = True
                txtProductID.Text = ddlThirdCategory.SelectedValue
                txtProductCode.Text = ""
                txtKey.Text = ""
                BindProducts()
            End If
        Else

        End If
    End Sub

    Protected Sub cmdGenSearch_Click(ByVal sender As Object, ByVal e As System.Web.UI.ImageClickEventArgs)
        tblProduct.Visible = True
        lblSelectProduct.Visible = True
        txtProductCode.Text = ""
        txtProductID.Text = ""
        txtKey.Text = ddlSearch.SelectedValue
        ddlFirstCategory.SelectedValue = "-1"
        ddlSecondCategory.Items.Clear()
        ddlSecondCategory.Items.Insert(0, New ListItem("Select", "-1"))
        ddlThirdCategory.Items.Clear()
        ddlThirdCategory.Items.Insert(0, New ListItem("Select", "-1"))
        BindProducts()
    End Sub

    Protected Sub cmdProdSearch_Click(ByVal sender As Object, ByVal e As System.Web.UI.ImageClickEventArgs)
        tblProduct.Visible = True
        lblSelectProduct.Visible = True
        txtProductID.Text = ""
        txtKey.Text = ""
        ddlSearch.SelectedValue = "-1"
        ddlFirstCategory.SelectedValue = "-1"
        ddlSecondCategory.Items.Clear()
        ddlSecondCategory.Items.Insert(0, New ListItem("Select", "-1"))
        ddlThirdCategory.Items.Clear()
        ddlThirdCategory.Items.Insert(0, New ListItem("Select", "-1"))
        txtsearch.Text = ""
        BindProducts()
    End Sub

    Public Sub BindProducts()
        Dim Str As String

        lblSelectProduct.Visible = True
        Str = ""

        If Not txtProductID.Text.Trim = "" Then
            Str = "select ProductDisplay.*, Row_Number() over (order by (Product_Name)) as PIndex from ProductDisplay where Product_CategoryId='" & txtProductID.Text.Trim & "'"
        ElseIf Not txtKey.Text.Trim = "" Then
            If txtKey.Text = 0 Then
                Str = "select ProductDisplay.*, Row_Number() over (order by (Product_Name)) as PIndex from ProductDisplay where product_kid in (select Keyword_ProductId from Product_Keyword where Keyword_Name like '%" & txtsearch.Text.Trim & "%') or Product_Name Like '%" & txtsearch.Text.Trim & "%' or Product_ModelNo Like '%" & txtsearch.Text.Trim & "'"
            ElseIf txtKey.Text = 1 Then
                Str = "select ProductDisplay.*, Row_Number() over (order by (Product_Name)) as PIndex from ProductDisplay where Product_ModelNo Like '%" & txtsearch.Text.Trim & "%'"
            ElseIf txtKey.Text = 2 Then
                Str = "select ProductDisplay.*, Row_Number() over (order by (Product_Name)) as PIndex from ProductDisplay where Product_Code Like '%" & txtsearch.Text.Trim & "%'"
            ElseIf txtKey.Text = 3 Then
                Str = "select ProductDisplay.*, Row_Number() over (order by (Product_Name)) as PIndex from ProductDisplay where Brand_Name Like '%" & txtsearch.Text.Trim & "%'"
            End If
        ElseIf Not txtProductCode.Text.Trim = "" Then
            Str = "select ProductDisplay.*, Row_Number() over (order by (Product_Name)) as PIndex from ProductDisplay where Product_Code Like '%" & txtProductCode.Text.Trim & "%'"
        End If

        Dim cn As New System.Data.SqlClient.SqlConnection(ConfigurationManager.AppSettings("ConnectionString"))
        Dim objAdapter As SqlDataAdapter
        Dim ObjDS As DataSet

        cn.Open()

        Try
            objAdapter = New SqlDataAdapter(Str, cn)
            objAdapter.SelectCommand.CommandType = CommandType.Text

            ObjDS = New DataSet
            objAdapter.Fill(ObjDS, "Products")

            lblProductsCount.Visible = True
            lblProductsCount.Text = "<b>" & ObjDS.Tables(0).Rows.Count & " Item(s) Found</b>"

            gvProductDisplay.DataSource = ObjDS
            gvProductDisplay.DataBind()

            ObjDS.Clear()
            ObjDS.Dispose()

            Dim count As Integer
            For count = 0 To gvProductDisplay.Rows.Count - 1
                Dim imgpath As String
                imgpath = Server.MapPath("~/Upload Image/Product Image/UploadImages/" & gvProductDisplay.Rows(count).Cells(3).Text)
                KeepAspectRatioNew(CType(gvProductDisplay.Rows(count).Cells(2).FindControl("cmdProdImg"), Image), imgpath, 100, 100)
            Next

            cmdSearchOtherProd.Visible = True

        Catch ex As Exception

        Finally
            cn.Close()
            cn.Dispose()
        End Try

    End Sub

    Protected Sub gvProductDisplay_PageIndexChanging(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewPageEventArgs)
        gvProductDisplay.PageIndex = e.NewPageIndex
        BindProducts()
    End Sub

    Protected Sub gvProductDisplay_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs)

        tblProductPrice.Visible = True
        lblSelectProduct.Visible = True
        cmdAddToQuot.Visible = False

        txtPrice.Text = ""
        txtExcise.Text = ""
        txtEduCess.Text = ""
        txtVAT.Text = ""
        txtCST.Text = ""
        txtCSTAgainstC.Text = ""
        txtQuantity.Text = ""
        lblTotalPrice.Text = ""

        'To Clear Error Msg
        lblErrorData.Text = ""
        lblErrorData.Visible = False

        txtSelectedCategoryID.Text = gvProductDisplay.SelectedRow.Cells(9).Text
        txtSelectedProductID.Text = gvProductDisplay.SelectedRow.Cells(1).Text
        txtSelectedProductCode.Text = gvProductDisplay.SelectedRow.Cells(6).Text
        txtSelectedProductName.Text = gvProductDisplay.SelectedRow.Cells(5).Text
        txtSelectedProductImage.Text = gvProductDisplay.SelectedRow.Cells(3).Text
        txtSelectedProductModelNo.Text = gvProductDisplay.SelectedRow.Cells(7).Text
        txtSelectedProductBrand.Text = gvProductDisplay.SelectedRow.Cells(8).Text

        lblProductName.Visible = True
        lblProductName.Text = "<b>Price For : </b> " & gvProductDisplay.SelectedRow.Cells(5).Text & " (Product Code : " & gvProductDisplay.SelectedRow.Cells(6).Text & ")"
        txtPrice.Text = gvProductDisplay.SelectedRow.Cells(4).Text
        txtPrice.ReadOnly = True

        tblProductPrice.Focus()

        BindTaxes()

    End Sub

    Public Sub BindTaxes()

        Dim Str As String = "SELECT  ProductVat_ExciseDuty, ProductVat_EduCess,  ProductVat_VAT, ProductVat_CST, ProductVat_CSTAgainstC, ProductVat_SalesTaxRemark FROM ProductVat where  ProductVat_ProductId='" & txtSelectedProductID.Text.ToString & "'"

        Dim cn As New System.Data.SqlClient.SqlConnection(ConfigurationManager.AppSettings("ConnectionString"))
        Dim objAdapter As SqlDataAdapter
        Dim ObjDS As DataSet
        Dim ObjDR As DataRow

        cn.Open()

        Try
            objAdapter = New SqlDataAdapter(Str, cn)
            objAdapter.SelectCommand.CommandType = CommandType.Text

            ObjDS = New DataSet
            objAdapter.Fill(ObjDS, "TaxDetails")


            If ObjDS.Tables(0).Rows.Count > 0 Then
                ObjDR = ObjDS.Tables(0).Rows(0)
                txtExcise.Text = IIf(IsDBNull(ObjDR("ProductVat_ExciseDuty")), 0, ObjDR("ProductVat_ExciseDuty"))
                txtEduCess.Text = IIf(IsDBNull(ObjDR("ProductVat_EduCess")), 0, ObjDR("ProductVat_EduCess"))
                txtVAT.Text = IIf(IsDBNull(ObjDR("ProductVat_VAT")), 0, ObjDR("ProductVat_VAT"))
                txtCST.Text = IIf(IsDBNull(ObjDR("ProductVat_CST")), 0, ObjDR("ProductVat_CST"))
                txtCSTAgainstC.Text = IIf(IsDBNull(ObjDR("ProductVat_CSTAgainstC")), 0, ObjDR("ProductVat_CSTAgainstC"))
            End If

        Catch ex As Exception

        Finally
            cn.Close()
            cn.Dispose()
        End Try

    End Sub

    Protected Sub cmdCalculate_Click(ByVal sender As Object, ByVal e As System.Web.UI.ImageClickEventArgs)
        'To Clear Error Msg
        lblErrorData.Text = ""
        lblErrorData.Visible = False


        If ddlCustomer.SelectedValue = "-1" Then
            showmessage("Please Select Company.", lblErrorData)
            ddlCustomer.Focus()
            Exit Sub
        End If

        If txtVAT.Text <> "" And txtCST.Text <> "" Then
            showmessage("You can Apply Vat or CST.", lblErrorData)
            txtVAT.Focus()
            Exit Sub
        ElseIf txtVAT.Text <> "" And txtCSTAgainstC.Text <> "" Then
            showmessage("You can Apply VAT or CST Against CForm.", lblErrorData)
            txtVAT.Focus()
            Exit Sub
        ElseIf txtCST.Text <> "" And txtCSTAgainstC.Text <> "" Then
            showmessage("You can Apply CST or CST Against CForm.", lblErrorData)
            txtCST.Focus()
            Exit Sub
        End If

        If Not txtExcise.Text = "" Then
            If txtEduCess.Text = "" Then
                showmessage("Please Enter Value in Education Cess.", lblErrorData)
                txtEduCess.Focus()
                Exit Sub
            End If
        End If

        If Not txtExcise.Text = "" Then
            If Not IsNumeric(txtExcise.Text) Then
                showmessage("Please Enter Numeric Value in Excise Duty.", lblErrorData)
                txtExcise.Focus()
                Exit Sub
            End If
        End If

        If Not txtEduCess.Text = "" Then
            If Not IsNumeric(txtEduCess.Text) Then
                showmessage("Please Enter Numeric Value in Education Cess.", lblErrorData)
                txtEduCess.Focus()
                Exit Sub
            End If
        End If

        If Not txtVAT.Text = "" Then
            If Not IsNumeric(txtVAT.Text) Then
                showmessage("Please Enter Numeric Value in VAT.", lblErrorData)
                txtVAT.Focus()
                Exit Sub
            End If
        End If

        If Not txtCST.Text = "" Then
            If Not IsNumeric(txtCST.Text) Then
                showmessage("Please Enter Numeric Value in CST.", lblErrorData)
                txtCST.Focus()
                Exit Sub
            End If
        End If

        If Not txtCSTAgainstC.Text = "" Then
            If Not IsNumeric(txtCSTAgainstC.Text) Then
                showmessage("Please Enter Numeric Value in CST Against C Form.", lblErrorData)
                txtCSTAgainstC.Focus()
                Exit Sub
            End If
        End If

        If Not IsNumeric(txtQuantity.Text) Then
            showmessage("Please Enter Numeric Value in Quantity.", lblErrorData)
            txtQuantity.Text = ""
            txtQuantity.Focus()
            Exit Sub
        End If

        cmdAddToQuot.Visible = True

        Dim ExciseDutyStr, EduCessStr, VatStr, CSTStr As Decimal

        ExciseDutyStr = 0
        EduCessStr = 0
        VatStr = 0
        CSTStr = 0

        If Not txtExcise.Text = "" Then
            ExciseDutyStr = (txtPrice.Text * txtExcise.Text) / 100
        End If

        If Not txtEduCess.Text = "" Then
            EduCessStr = (ExciseDutyStr * txtEduCess.Text) / 100
        End If

        If Not txtVAT.Text = "" Then   'Only For Gujarat
            VatStr = ((ExciseDutyStr + EduCessStr + txtPrice.Text) * txtVAT.Text) / 100
        End If
        If Not txtCST.Text = "" Then
            CSTStr = ((ExciseDutyStr + EduCessStr + txtPrice.Text) * txtCST.Text) / 100
        End If
        If Not txtCSTAgainstC.Text = "" Then
            CSTStr = ((ExciseDutyStr + EduCessStr + txtPrice.Text) * txtCSTAgainstC.Text) / 100
        End If

        lblTotalPrice.Text = (txtPrice.Text + ExciseDutyStr + EduCessStr + VatStr + CSTStr) * txtQuantity.Text

    End Sub

    Protected Sub cmdSearchOtherProd_Click(ByVal sender As Object, ByVal e As System.Web.UI.ImageClickEventArgs)
        tblProduct.Visible = False
        lblSelectProduct.Visible = False
        tblSearchProduct.Focus()
    End Sub

    Protected Sub cmdAddToQuot_Click(ByVal sender As Object, ByVal e As System.Web.UI.ImageClickEventArgs)
        lblQuotationCart.Visible = True
        tblFinapProducts.Visible = True
        lblProductName.Visible = False
        tblProductPrice.Visible = False
        cmdAddToQuot.Visible = False
        cmdGenerateQuot.Visible = True


        BindTempTable()

        Dim I As Integer

        For I = 0 To gvFinalProducts.Rows.Count - 1
            drRow = DtEmployee.NewRow()

            drRow("ProductID") = gvFinalProducts.Rows(I).Cells(2).Text.Trim
            drRow("ProductCode") = gvFinalProducts.Rows(I).Cells(3).Text.Trim
            drRow("ProductName") = gvFinalProducts.Rows(I).Cells(12).Text.Trim
            drRow("ProductImage") = gvFinalProducts.Rows(I).Cells(11).Text.Trim
            drRow("ProductModelNo") = gvFinalProducts.Rows(I).Cells(13).Text.Trim
            drRow("ProductBrand") = gvFinalProducts.Rows(I).Cells(14).Text.Trim
            drRow("AtcoPrice") = gvFinalProducts.Rows(I).Cells(4).Text.Trim
            drRow("Excise") = gvFinalProducts.Rows(I).Cells(5).Text.Trim
            drRow("EduCess") = gvFinalProducts.Rows(I).Cells(6).Text.Trim
            drRow("VAT") = gvFinalProducts.Rows(I).Cells(7).Text.Trim
            drRow("CST") = gvFinalProducts.Rows(I).Cells(8).Text.Trim
            drRow("CSTC") = gvFinalProducts.Rows(I).Cells(9).Text.Trim
            drRow("Quantity") = gvFinalProducts.Rows(I).Cells(15).Text.Trim
            drRow("TotalPrice") = gvFinalProducts.Rows(I).Cells(10).Text.Trim
            drRow("CategoryID") = gvFinalProducts.Rows(I).Cells(17).Text.Trim

            DtEmployee.Rows.Add(drRow)

        Next


        drRow = DtEmployee.NewRow()

        drRow("ProductID") = txtSelectedProductID.Text.Trim.ToString
        drRow("ProductCode") = txtSelectedProductCode.Text.Trim
        drRow("ProductName") = txtSelectedProductName.Text.Trim
        drRow("ProductImage") = txtSelectedProductImage.Text.Trim
        drRow("ProductModelNo") = txtSelectedProductModelNo.Text.Trim
        drRow("ProductBrand") = txtSelectedProductBrand.Text.Trim
        drRow("AtcoPrice") = txtPrice.Text.Trim
        drRow("Excise") = IIf(txtExcise.Text.Trim = "", 0, txtExcise.Text.Trim)
        drRow("EduCess") = IIf(txtEduCess.Text.Trim = "", 0, txtEduCess.Text.Trim)
        drRow("VAT") = IIf(txtVAT.Text.Trim = "", 0, txtVAT.Text.Trim)
        drRow("CST") = IIf(txtCST.Text.Trim = "", 0, txtCST.Text.Trim)
        drRow("CSTC") = IIf(txtCSTAgainstC.Text.Trim = "", 0, txtCSTAgainstC.Text.Trim)
        drRow("Quantity") = IIf(txtQuantity.Text.Trim = "", 0, txtQuantity.Text.Trim)
        drRow("TotalPrice") = IIf(lblTotalPrice.Text.Trim = "", 0, lblTotalPrice.Text.Trim)
        drRow("CategoryID") = txtSelectedCategoryID.Text.Trim.ToString

        DtEmployee.Rows.Add(drRow)

        lblFinalProdCount.Visible = True
        lblFinalProdCount.Text = "<b>" & DtEmployee.Rows.Count & " Item(s) Selected</b>"

        If DtEmployee.Rows.Count = 0 Then
            cmdGenerateQuot.Visible = False
            lblQuotationCart.Visible = False
            tblFinapProducts.Visible = False
        Else
            cmdGenerateQuot.Visible = True
            lblQuotationCart.Visible = True
            tblFinapProducts.Visible = True
        End If

        gvFinalProducts.DataSource = DtEmployee
        gvFinalProducts.DataBind()


        Dim count As Integer
        Dim QuotationAmount As Decimal

        QuotationAmount = 0

        For count = 0 To gvFinalProducts.Rows.Count - 1
            Dim imgpath As String
            QuotationAmount += gvFinalProducts.Rows(count).Cells(10).Text
            imgpath = Server.MapPath("~/Upload Image/Product Image/UploadImages/" & gvFinalProducts.Rows(count).Cells(11).Text)
            KeepAspectRatioNew(CType(gvFinalProducts.Rows(count).Cells(1).FindControl("cmdProdImg"), Image), imgpath, 100, 100)
        Next

        lblQuotationAmount.Text = QuotationAmount

        gvFinalProducts.Focus()

    End Sub

    Protected Sub gvFinalProducts_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs)

        Dim I As Integer
        Dim ProductID As String

        ProductID = gvFinalProducts.SelectedRow.Cells(0).Text

        BindTempTable()

        For I = 0 To gvFinalProducts.Rows.Count - 1
            drRow = DtEmployee.NewRow()

            drRow("ProductID") = gvFinalProducts.Rows(I).Cells(2).Text.Trim
            drRow("ProductCode") = gvFinalProducts.Rows(I).Cells(3).Text.Trim
            drRow("ProductName") = gvFinalProducts.Rows(I).Cells(12).Text.Trim
            drRow("ProductImage") = gvFinalProducts.Rows(I).Cells(11).Text.Trim
            drRow("ProductModelNo") = gvFinalProducts.Rows(I).Cells(13).Text.Trim
            drRow("ProductBrand") = gvFinalProducts.Rows(I).Cells(14).Text.Trim
            drRow("AtcoPrice") = gvFinalProducts.Rows(I).Cells(4).Text.Trim
            drRow("Excise") = gvFinalProducts.Rows(I).Cells(5).Text.Trim
            drRow("EduCess") = gvFinalProducts.Rows(I).Cells(6).Text.Trim
            drRow("VAT") = gvFinalProducts.Rows(I).Cells(7).Text.Trim
            drRow("CST") = gvFinalProducts.Rows(I).Cells(8).Text.Trim
            drRow("CSTC") = gvFinalProducts.Rows(I).Cells(9).Text.Trim
            drRow("TotalPrice") = gvFinalProducts.Rows(I).Cells(10).Text.Trim
            drRow("CategoryID") = gvFinalProducts.Rows(I).Cells(17).Text.Trim

            If drRow("ProductID") = ProductID Then

            Else
                DtEmployee.Rows.Add(drRow)
            End If
        Next

        lblFinalProdCount.Visible = True
        lblFinalProdCount.Text = "<b>" & DtEmployee.Rows.Count & " Item(s) Selected</b>"

        If DtEmployee.Rows.Count = 0 Then
            cmdGenerateQuot.Visible = False
            lblQuotationCart.Visible = False
            tblFinapProducts.Visible = False
        Else
            cmdGenerateQuot.Visible = True
            lblQuotationCart.Visible = True
            tblFinapProducts.Visible = True
        End If

        gvFinalProducts.DataSource = DtEmployee
        gvFinalProducts.DataBind()


        Dim count As Integer
        Dim QuotationAmount As Decimal

        QuotationAmount = 0
        For count = 0 To gvFinalProducts.Rows.Count - 1
            QuotationAmount += gvFinalProducts.Rows(count).Cells(10).Text
            Dim imgpath As String
            imgpath = Server.MapPath("~/Upload Image/Product Image/UploadImages/" & gvFinalProducts.Rows(count).Cells(11).Text)
            KeepAspectRatioNew(CType(gvFinalProducts.Rows(count).Cells(1).FindControl("cmdProdImg"), Image), imgpath, 100, 100)
        Next

        lblQuotationAmount.Text = QuotationAmount


    End Sub

    Protected Sub cmdGenerateQuot_Click(ByVal sender As Object, ByVal e As System.Web.UI.ImageClickEventArgs)

        Dim CategoryID, ProductID, ProductCode As String
        Dim ProductPrice, ExciseDuty, EducationCess, VAT, CST, CSTC, Quantity, TotalPrice As Decimal
        ProductID = ""
        ProductCode = ""
        ProductPrice = ExciseDuty = EducationCess = VAT = CST = CSTC = Quantity = TotalPrice = 0

        txtQuotationID.Text = System.Guid.NewGuid.ToString
        txtQuotationCode.Text = GenerateCode()
        Try
            AddQuotation()
            Dim J As Integer

            For J = 0 To gvFinalProducts.Rows.Count - 1
                FillCategoryName(gvFinalProducts.Rows(J).Cells(17).Text)
                CategoryID = txtFinalCategoryID.Text.Trim
                ProductID = gvFinalProducts.Rows(J).Cells(2).Text
                ProductCode = gvFinalProducts.Rows(J).Cells(3).Text
                ProductPrice = Convert.ToDecimal(gvFinalProducts.Rows(J).Cells(4).Text)
                ExciseDuty = gvFinalProducts.Rows(J).Cells(5).Text
                EducationCess = gvFinalProducts.Rows(J).Cells(6).Text
                VAT = gvFinalProducts.Rows(J).Cells(7).Text
                CST = gvFinalProducts.Rows(J).Cells(8).Text
                CSTC = gvFinalProducts.Rows(J).Cells(9).Text
                Quantity = gvFinalProducts.Rows(J).Cells(15).Text
                TotalPrice = Convert.ToDecimal(gvFinalProducts.Rows(J).Cells(10).Text)

                AddQuotationData(CategoryID, ProductID, ProductCode, ProductPrice, ExciseDuty, EducationCess, VAT, CST, CSTC, Quantity, TotalPrice)
            Next

            'Generate Html File

            GenQuotation()

            cmdGenerateQuot.Visible = False
            cmdViewQuot.Visible = True
            cmdSendQuot.Visible = True
            showmessage("Quotation generated Sucessfully.", lblErrorData1)
        Catch ex As Exception
            showmessage(ex.Message, lblErrorData1)
            'showmessage("Please try again later.", lblErrorData1)
        Finally

        End Try

    End Sub

    Public Sub FillCategoryName(ByVal Catid As String)
        ' used to fill category hirarchy in label
        Dim dt As DataTable

        Dim I As Integer

        dt = ReturnDataTable("Select * From Category_Master Where Category_Kid ='" & Catid & "' and Category_Isdeleted = '0' and Category_CompanyId ='" & Session("g_Company").ToString & "'")
        Dim ParentId As String
        ParentId = dt.Rows(0).Item(3).ToString()
        If ParentId = "Root Category" Then

            '  Dim j As Integer
            Category(I) = dt.Rows(0).Item(2).ToString() & "  >  "

            txtFinalCategoryID.Text = dt.Rows(0).Item(0).ToString()
            'Array.Reverse(Category)
            'For j = 0 To I
            '    lbl1.Text += Category(I)
            '    I = I - 1
            'Next
            Exit Sub
        Else
            Category(i) = dt.Rows(0).Item(2).ToString() & "  >  "
            I = I + 1
            'Label1.Text += dt.Rows(0).Item(2).ToString() & "  >  "
            FillCategoryName(dt.Rows(0).Item(3).ToString())
        End If
        dt.Clear()

    End Sub

    Public Sub AddQuotation()
        Dim cn As New System.Data.SqlClient.SqlConnection(ConfigurationManager.AppSettings("Connection4Voffice"))
        Dim objAdapter As SqlDataAdapter

        cn.Open()

        Try
            objAdapter = New SqlDataAdapter("Insert_CRMQuotation_Proc", cn)
            objAdapter.SelectCommand.CommandType = CommandType.StoredProcedure

            objAdapter.SelectCommand.Parameters.Add(New SqlParameter("@QuotationID", SqlDbType.VarChar))
            objAdapter.SelectCommand.Parameters.Add(New SqlParameter("@QuotationNo", SqlDbType.VarChar, 50))
            objAdapter.SelectCommand.Parameters.Add(New SqlParameter("@QuotationDate", SqlDbType.DateTime))
            objAdapter.SelectCommand.Parameters.Add(New SqlParameter("@CustomerID", SqlDbType.VarChar))
            objAdapter.SelectCommand.Parameters.Add(New SqlParameter("@QuotationAmount", SqlDbType.Decimal))
            objAdapter.SelectCommand.Parameters.Add(New SqlParameter("@ReceivedAmount", SqlDbType.Decimal))
            objAdapter.SelectCommand.Parameters.Add(New SqlParameter("@ApprovalStatus", SqlDbType.Int))
            objAdapter.SelectCommand.Parameters.Add(New SqlParameter("@QuotationStatus", SqlDbType.NVarChar, 50))
            objAdapter.SelectCommand.Parameters.Add(New SqlParameter("@QuotationPaymentStatus", SqlDbType.NVarChar, 50))
            objAdapter.SelectCommand.Parameters.Add(New SqlParameter("@QuotationLastDate", SqlDbType.DateTime))
            objAdapter.SelectCommand.Parameters.Add(New SqlParameter("@CompanyID", SqlDbType.VarChar))
            objAdapter.SelectCommand.Parameters.Add(New SqlParameter("@SSAID", SqlDbType.VarChar))
            objAdapter.SelectCommand.Parameters.Add(New SqlParameter("@QuotationMode", SqlDbType.Int))
            objAdapter.SelectCommand.Parameters.Add(New SqlParameter("@FormName", SqlDbType.NVarChar, 50))
            objAdapter.SelectCommand.Parameters.Add(New SqlParameter("@Mode", SqlDbType.VarChar, 10))

            objAdapter.SelectCommand.Parameters("@QuotationID").Value = txtQuotationID.Text.Trim
            objAdapter.SelectCommand.Parameters("@QuotationNo").Value = txtQuotationCode.Text.Trim
            objAdapter.SelectCommand.Parameters("@QuotationDate").Value = System.DateTime.Now.Date
            objAdapter.SelectCommand.Parameters("@CustomerID").Value = ddlCustomer.SelectedValue.Trim
            objAdapter.SelectCommand.Parameters("@QuotationAmount").Value = Convert.ToDecimal(lblQuotationAmount.Text.Trim)
            objAdapter.SelectCommand.Parameters("@ReceivedAmount").Value = Convert.ToDecimal("0.0")
            objAdapter.SelectCommand.Parameters("@ApprovalStatus").Value = 0
            objAdapter.SelectCommand.Parameters("@QuotationStatus").Value = "In Process"
            objAdapter.SelectCommand.Parameters("@QuotationPaymentStatus").Value = "Unpaid"
            objAdapter.SelectCommand.Parameters("@QuotationLastDate").Value = System.DateTime.Now.Date
            objAdapter.SelectCommand.Parameters("@CompanyID").Value = txtVCompanyID.Text.Trim
            objAdapter.SelectCommand.Parameters("@SSAID").Value = txtSSAID.Text.Trim
            objAdapter.SelectCommand.Parameters("@QuotationMode").Value = 1
            objAdapter.SelectCommand.Parameters("@FormName").Value = "Quotation Bank"
            objAdapter.SelectCommand.Parameters("@Mode").Value = "Insert"

            objAdapter.SelectCommand.ExecuteNonQuery()

        Catch ex As Exception
            Throw ex
        Finally
            cn.Close()
            cn.Dispose()
        End Try
    End Sub

    Public Sub AddQuotationData(ByVal CategoryID As String, ByVal ProductID As String, ByVal ProductCode As String, ByVal ProductPrice As Decimal, ByVal ExciseDuty As Decimal, ByVal EducationCess As Decimal, ByVal VAT As Decimal, ByVal CST As Decimal, ByVal CSTC As Decimal, ByVal Quantity As Decimal, ByVal TotalPrice As Decimal)
        Dim cn As New System.Data.SqlClient.SqlConnection(ConfigurationManager.AppSettings("Connection4Voffice"))
        Dim objAdapter As SqlDataAdapter

        cn.Open()

        Try
            objAdapter = New SqlDataAdapter("Insert_CRMQuotationData_Proc", cn)
            objAdapter.SelectCommand.CommandType = CommandType.StoredProcedure

            objAdapter.SelectCommand.Parameters.Add(New SqlParameter("@QuotationDataID", SqlDbType.VarChar))
            objAdapter.SelectCommand.Parameters.Add(New SqlParameter("@QuotationID", SqlDbType.VarChar))
            objAdapter.SelectCommand.Parameters.Add(New SqlParameter("@CategoryID", SqlDbType.NVarChar, 10))
            objAdapter.SelectCommand.Parameters.Add(New SqlParameter("@ProductID", SqlDbType.NVarChar, 10))
            objAdapter.SelectCommand.Parameters.Add(New SqlParameter("@ProductCode", SqlDbType.NVarChar, 25))
            objAdapter.SelectCommand.Parameters.Add(New SqlParameter("@ProductPrice", SqlDbType.Decimal))
            objAdapter.SelectCommand.Parameters.Add(New SqlParameter("@ExciseDuty", SqlDbType.Decimal))
            objAdapter.SelectCommand.Parameters.Add(New SqlParameter("@EducationCess", SqlDbType.Decimal))
            objAdapter.SelectCommand.Parameters.Add(New SqlParameter("@VAT", SqlDbType.Decimal))
            objAdapter.SelectCommand.Parameters.Add(New SqlParameter("@CST", SqlDbType.Decimal))
            objAdapter.SelectCommand.Parameters.Add(New SqlParameter("@CSTC", SqlDbType.Decimal))
            objAdapter.SelectCommand.Parameters.Add(New SqlParameter("@Quantity", SqlDbType.Decimal))
            objAdapter.SelectCommand.Parameters.Add(New SqlParameter("@TotalPrice", SqlDbType.Decimal))
            objAdapter.SelectCommand.Parameters.Add(New SqlParameter("@CompanyID", SqlDbType.VarChar))
            objAdapter.SelectCommand.Parameters.Add(New SqlParameter("@FormName", SqlDbType.NVarChar, 50))
            objAdapter.SelectCommand.Parameters.Add(New SqlParameter("@Mode", SqlDbType.VarChar, 10))

            objAdapter.SelectCommand.Parameters("@QuotationDataID").Value = System.Guid.NewGuid.ToString
            objAdapter.SelectCommand.Parameters("@QuotationID").Value = txtQuotationID.Text.Trim
            objAdapter.SelectCommand.Parameters("@CategoryID").Value = CategoryID
            objAdapter.SelectCommand.Parameters("@ProductID").Value = ProductID
            objAdapter.SelectCommand.Parameters("@ProductCode").Value = ProductCode
            objAdapter.SelectCommand.Parameters("@ProductPrice").Value = Convert.ToDecimal(ProductPrice)
            objAdapter.SelectCommand.Parameters("@ExciseDuty").Value = ExciseDuty
            objAdapter.SelectCommand.Parameters("@EducationCess").Value = EducationCess
            objAdapter.SelectCommand.Parameters("@VAT").Value = VAT
            objAdapter.SelectCommand.Parameters("@CST").Value = CST
            objAdapter.SelectCommand.Parameters("@CSTC").Value = CSTC
            objAdapter.SelectCommand.Parameters("@Quantity").Value = Quantity
            objAdapter.SelectCommand.Parameters("@TotalPrice").Value = Convert.ToDecimal(TotalPrice)
            objAdapter.SelectCommand.Parameters("@CompanyID").Value = txtVCompanyID.Text.Trim
            objAdapter.SelectCommand.Parameters("@FormName").Value = "Quotation Data"
            objAdapter.SelectCommand.Parameters("@Mode").Value = "Insert"

            objAdapter.SelectCommand.ExecuteNonQuery()

        Catch ex As Exception
            Throw ex
        Finally
            cn.Close()
            cn.Dispose()
        End Try
    End Sub

    Public Sub AddQuotationTable()
        Dim cn As New System.Data.SqlClient.SqlConnection(ConfigurationManager.AppSettings("Connection4Voffice"))
        Dim objAdapter As SqlDataAdapter

        cn.Open()

        Try
            objAdapter = New SqlDataAdapter("Insert_CRMQuotationTable_Proc", cn)

            objAdapter.SelectCommand.CommandType = CommandType.StoredProcedure

            objAdapter.SelectCommand.Parameters.Add(New SqlParameter("@QuotationTableID", SqlDbType.VarChar))
            objAdapter.SelectCommand.Parameters.Add(New SqlParameter("@QuotationID", SqlDbType.VarChar))
            objAdapter.SelectCommand.Parameters.Add(New SqlParameter("@QuotationNo", SqlDbType.VarChar, 50))
            objAdapter.SelectCommand.Parameters.Add(New SqlParameter("@QuotationTable", SqlDbType.Text))
            objAdapter.SelectCommand.Parameters.Add(New SqlParameter("@FormName", SqlDbType.NVarChar, 50))
            objAdapter.SelectCommand.Parameters.Add(New SqlParameter("@Mode", SqlDbType.VarChar, 10))

            objAdapter.SelectCommand.Parameters("@QuotationTableID").Value = txtQuotationTableID.text.trim
            objAdapter.SelectCommand.Parameters("@QuotationID").Value = txtQuotationID.Text.Trim
            objAdapter.SelectCommand.Parameters("@QuotationNo").Value = txtQuotationCode.Text.Trim
            objAdapter.SelectCommand.Parameters("@QuotationTable").Value = txtQuotationTable.Text.Trim
            objAdapter.SelectCommand.Parameters("@FormName").Value = "Quotation Table"
            objAdapter.SelectCommand.Parameters("@Mode").Value = "Insert"

            objAdapter.SelectCommand.ExecuteNonQuery()

        Catch ex As Exception
            Throw ex
        Finally

            cn.Close()
            cn.Dispose()
        End Try
    End Sub

    Public Function GenerateCode() As String
        Dim QuotationCode As String
        QuotationCode = ""
        Try
            Dim i As String
            i = "1"
            i = Format(Val(i), "000")
            While True
                QuotationCode = "QUO" & Year(Date.Today) & Format(Month(Date.Today), "00") & Format(Day(Date.Today), "00") & i

                If IsAlreadyPresent(QuotationCode, txtVCompanyID.Text.Trim) Then
                    i = Val(i) + 1
                    i = Format(Val(i), "000")
                Else
                    Return QuotationCode
                    Exit Function
                End If
            End While
        Catch ex As Exception
        End Try
        Return QuotationCode
    End Function

    Public Function IsAlreadyPresent(ByVal QuotationNo As String, ByVal CompanyID As String) As Boolean
        Dim cn As New System.Data.SqlClient.SqlConnection(ConfigurationManager.AppSettings("Connection4Voffice"))
        Dim objAdapter As SqlDataAdapter
        Dim ObjDS As DataSet

        cn.Open()

        Try
            objAdapter = New SqlDataAdapter("Select_CRMQuotationByQuotationNo_Proc", cn)
            objAdapter.SelectCommand.CommandType = CommandType.StoredProcedure

            objAdapter.SelectCommand.Parameters.Add(New SqlParameter("@QuotationNo", SqlDbType.NVarChar, 50))
            objAdapter.SelectCommand.Parameters.Add(New SqlParameter("@CompanyID", SqlDbType.VarChar))

            objAdapter.SelectCommand.Parameters("@QuotationNo").Value = QuotationNo
            objAdapter.SelectCommand.Parameters("@CompanyID").Value = CompanyID

            ObjDS = New DataSet
            objAdapter.Fill(ObjDS, "QuotationBank")

            If ObjDS.Tables(0).Rows.Count > 0 Then
                Return True
            End If
        Catch ex As Exception
            Return False
        Finally
            cn.Close()
            cn.Dispose()
        End Try

    End Function

    Protected Sub GenQuotation()
        Try
            Dim total As Double

            Dim DsCustomer As New DataSet
            Dim DrCustomer As DataRow
            Dim DsCompany As New DataSet
            Dim DrCompany As DataRow
            Dim DsProduct As New DataSet
            Dim DrProduct As DataRow
            Dim DsTechSpec As New DataSet
            Dim DrTechSpec As DataRow
            Dim DsQuotationData As New DataSet
            Dim DrQuotationData As DataRow

            DsCustomer = SelectCustomerByCustomerID(ddlCustomer.SelectedValue.ToString)

            DrCustomer = DsCustomer.Tables(0).Rows(0)


            DsCompany = SelectCompanyByCompany(txtVCompanyID.Text.Trim)

            DrCompany = DsCompany.Tables(0).Rows(0)

            Dim logopath As String = ""

            logopath = DrCompany("V_Company_Logo")
            logopath = "~/Images" & logopath

            Dim HtmlString As String
            HtmlString = ""

            HtmlString += "<html><head><title>Quotation</title></head><body>"
            HtmlString += "<table border='1' width='80%' style='font-family:verdana;font-size:8pt;'><tr><td align='center'><b>Quotation</b></td></tr><tr><td>"
            HtmlString += "<table border='1' width='100%' style='font-family:verdana;font-size:8pt;'><tr><td width='50%'>Quotation No.:" & txtQuotationCode.Text & "<br>Date: " & Convert.ToDateTime(Format(System.DateTime.Now.Date, "dd-MMM-yyyy")) & "</td>"
            HtmlString += "<td><img src='" & logopath & "' align='top'></td></tr>"
            HtmlString += "<tr><td colspan='2'>Customer Name: " & DrCustomer("V_CRM_ContactPerson") & "</td></tr>"
            HtmlString += "<tr><td>Address:<br>" & DrCustomer("V_CRM_CompanyName") & "<br>" & DrCustomer("V_CRM_CompanyAddress") & "</td>"
            HtmlString += "<td>" & DrCompany("V_Company_Name") & "<br>Registered Office:<br>"
            HtmlString += DrCompany("V_Company_Address") & "," & DrCompany("CityName") & "," & DrCompany("StateName") & "," & DrCompany("CountryName") & "," & "<br>"
            HtmlString += "Tel." & DrCompany("V_Company_PhoneNo") & ", Fax." & DrCompany("V_Company_FaxNo") & "<br>"
            '            HtmlString +="Email " & DrCompany("V_Company_PhoneNo") & "<br></td></tr>"
            HtmlString += "<tr><td>&nbsp;</td><td>Sales Tax Registration Nos.<br>VAT TIN is: " & DrCompany("V_Company_VatNo") & "<br>"
            HtmlString += "CST TIN is: " & DrCompany("V_Company_CSTNo") & "</td></tr></table>"
            HtmlString += "</td></tr><tr><td>Product Description</td></tr><tr><td>"

            DsQuotationData = SelectQuotationDataByQuotationID(txtQuotationID.Text.Trim, txtVCompanyID.Text.Trim)

            HtmlString += "<table border='1'width='100%' style='font-family:verdana;font-size:8pt;'><tr><td align='center'><b>SRNo</b></td><td align='center' ><b>Product Details</b></td><td align='center'><b>Amount</b></td></tr>"

            Dim I As Integer
            For I = 0 To DsQuotationData.Tables(0).Rows.Count - 1
                DrQuotationData = DsQuotationData.Tables(0).Rows(I)

                DsProduct = SelectProductByProductID(DrQuotationData("V_CRM_ProductID"))

                DsTechSpec = SelectProductTechnicalSpecificationByProductID(DrQuotationData("V_CRM_ProductID"))

                DrProduct = DsProduct.Tables(0).Rows(0)
                HtmlString += "<tr><td align='center'>" & I + 1 & "</td><td><table style='font-family:verdana;font-size:8pt;'>"
                HtmlString += "<tr><td><b>Product Code :</b>" & DrQuotationData("V_CRM_ProductCode") & "</td></tr>"
                HtmlString += "<tr><td><b>Product Name :</b>" & DrProduct("Product_Name") & "</td></tr>"

                If DsTechSpec.Tables(0).Rows.Count > 0 Then
                    HtmlString += "<tr><td><b>Technical Specification :</b></td></tr>"
                    HtmlString += "<tr><td><table cellpadding='0' border='1' cellspacing='0' style='border-right: #8c8e81 thin solid;border-top: #8c8e81 thin solid; font-size: 7pt; border-left: #8c8e81 thin solid;border-bottom: #8c8e81 thin  solid; font-family: Verdana;'><tr><td><span style='font-size: 7pt; font-family: Verdana;'>Parameters</span></td><td><span style='font-size: 7pt; font-family: Verdana;'>Value</span></td></tr>"

                    Dim J As Integer

                    For J = 0 To DsTechSpec.Tables(0).Rows.Count - 1
                        DrTechSpec = DsTechSpec.Tables(0).Rows(J)
                        HtmlString += "<tr><td align='center'><span style='font-size: 7pt; font-family: Verdana;'>" & DrTechSpec("TechSpec_parameter") & "</span></td><td align='center'><span style='font-size: 7pt; font-family: Verdana;'>" & DrTechSpec("TechSpec_value") & "</span></td></tr>"
                    Next
                    HtmlString += "</table>"
                    HtmlString += "<tr><td></td></tr>"
                End If


                HtmlString += "<tr><td><table cellpadding='0' border='1' cellspacing='0' style='border-right: #8c8e81 thin solid;border-top: #8c8e81 thin solid; font-size: 7pt; border-left: #8c8e81 thin solid;border-bottom: #8c8e81 thin  solid; font-family: Verdana;'><tr><td><span style='font-size: 7pt; font-family: Verdana;'>Price</span></td><td><span style='font-size: 7pt; font-family: Verdana;'>Excise</span></td><td><span style='font-size: 7pt; font-family: Verdana;'>Edu. Cess</span></td><td><span style='font-size: 7pt; font-family: Verdana;'>VAT</span></td><td><span style='font-size: 7pt; font-family: Verdana;'>CST</span></td><td><span style='font-size: 7pt; font-family: Verdana;'>CST -C</span></td><td><span style='font-size: 7pt; font-family: Verdana;'>Qty</span></td><td><span style='font-size: 7pt; font-family: Verdana;'>Total Price</span></td></tr><tr>"
                HtmlString += "<td align='center'><span style='font-size: 7pt; font-family: Verdana;'>" & DrQuotationData("V_CRM_ProductPrice") & "</span></td><td align='center'><span style='font-size: 7pt; font-family: Verdana;'>" & DrQuotationData("V_CRM_ExciseDuty") & "%</span></td><td align='center'><span style='font-size: 7pt; font-family: Verdana;'>" & DrQuotationData("V_CRM_EduCess") & "%</span></td><td align='center'><span style='font-size: 7pt; font-family: Verdana;'>" & DrQuotationData("V_CRM_VAT") & "%</span></td><td align='center'><span style='font-size: 7pt; font-family: Verdana;'>" & DrQuotationData("V_CRM_CST") & "%</span></td><td align='center'><span style='font-size: 7pt; font-family: Verdana;'>" & DrQuotationData("V_CRM_CSTAgainstC") & "%</span></td><td align='center'><span style='font-size: 7pt; font-family: Verdana;'>" & DrQuotationData("V_CRM_ProductQuantity") & "</span></td><td align='center'><span style='font-size: 7pt; font-family: Verdana;'>" & DrQuotationData("V_CRM_TotalPrice") & "</span></td></tr></table>"
                HtmlString += "</td></tr></table></td><td align='center'>" & DrQuotationData("V_CRM_TotalPrice") & "</td></tr>"

                total = total + DrQuotationData("V_CRM_TotalPrice")

            Next

            HtmlString += "<tr><td>&nbsp;</td><td align='right'>Total :&nbsp; </td><td align='center'>" & total & "</td></tr>"
            '      HtmlString +="<tr><td>&nbsp;</td><td align='right'>" & RupeesToWord(total) & "</td></tr>"
            HtmlString += "</table>"
            HtmlString += "<table border='1' width='100%' style='font-family:verdana;font-size:8pt;'><tr><td>TERMS AND CONDITIONS:</td></tr>"
            HtmlString += "<tr><td>Price Offered is ex-warehouse Daman, and F.O.R. Destinations anywhere in India.</td></tr>"
            HtmlString += "<tr><td>Price Offered is exclusive of all charges such as taxes (VAT / CST @12.5% or 3% against Form C).</td></tr>"
            HtmlString += "<tr><td>Payment Terms: 100% advance alongwith your Purchase Order.</td></tr>"
            HtmlString += "<tr><td>Local Stamping Charges and other incidental expenses related to it will be borne by the customer.</td></tr>"
            HtmlString += "<tr><td>All payments by Account Payee Cheque / Demand Draft only drawn in favour of AtcoMaart Ltd.</td></tr>"
            HtmlString += "<tr><td>Validity of Offer: 30 days.</td></tr>"
            HtmlString += "<tr><td>Octroi wherever applicable will be extra at actual.</td></tr>"
            HtmlString += "<tr><td>Warranty 12 months against any manufacturing defects.</td></tr>"
            '     HtmlString +="<tr><td>" & txtTerms1.Text & "</td></tr>"
            '     HtmlString +="<tr><td>" & txtTerms2.Text & "</td></tr>"
            '     HtmlString +="<tr><td>" & txtTerms3.Text & "</td></tr>"
            '     HtmlString +="<tr><td>" & txtTerms4.Text & "</td></tr>"
            '     HtmlString +="<tr><td>" & txtTerms5.Text & "</td></tr>"
            '     HtmlString +="<tr><td>" & txtTerms6.Text & "</td></tr>"
            '     HtmlString +="<tr><td>" & txtTerms7.Text & "</td></tr>"
            '     HtmlString +="<tr><td>" & txtTerms8.Text & "</td></tr>"
            HtmlString += "<tr><td>&nbsp;</td></tr>"
            HtmlString += "<tr><td align='center'>This is computer generated document. No signature is required.</td></tr></table>"
            HtmlString += "</td></tr></table>"
        
            'htmltopdf(filename)
            '        ConvertToPDF(Server.MapPath("") & "/quotations/" & filename & ".html", Server.MapPath("") & "/quotations/" & filename & ".pdf")

            txtQuotationTableID.Text = System.Guid.NewGuid.ToString
            txtQuotationTable.Text = HtmlString

            AddQuotationTable()
        Catch ex As Exception
            Throw ex
        End Try
    End Sub

    Protected Sub ddlCustomer_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles ddlCustomer.SelectedIndexChanged
        If ddlCustomer.SelectedIndex > 0 Then
            'Select_CRMCustomerByCustomerID_Proc
            Dim cn As New System.Data.SqlClient.SqlConnection(ConfigurationManager.AppSettings("Connection4Voffice"))
            Dim objAdapter As SqlDataAdapter
            Dim ObjDS As DataSet

            cn.Open()

            Try
                objAdapter = New SqlDataAdapter("Select_CRMCustomerByCustomerID_Proc", cn)
                objAdapter.SelectCommand.CommandType = CommandType.StoredProcedure

                objAdapter.SelectCommand.Parameters.Add(New SqlParameter("@CustomerID", SqlDbType.VarChar))

                objAdapter.SelectCommand.Parameters("@CustomerID").Value = ddlCustomer.SelectedValue.ToString

                ObjDS = New DataSet
                objAdapter.Fill(ObjDS, "CustomerDetails")

                If ObjDS.Tables(0).Rows.Count > 0 Then
                    lblBilingDetails.Text = ObjDS.Tables(0).Rows(0).Item("V_CRM_CompanyAddress")
                    lblcompanyName.Text = ObjDS.Tables(0).Rows(0).Item("V_CRM_CompanyName")
                    lblAddress.Text = ObjDS.Tables(0).Rows(0).Item("V_CRM_CompanyAddress")
                End If

            Catch ex As Exception

            Finally
                cn.Close()
                cn.Dispose()
            End Try

        End If
    End Sub

    Public Function SelectCompanyByCompany(ByVal CompanyID As String) As DataSet
        Dim cn As New System.Data.SqlClient.SqlConnection(ConfigurationManager.AppSettings("Connection4Voffice"))
        Dim objAdapter As SqlDataAdapter
        Dim ObjDS As DataSet

        cn.Open()

        Try
            objAdapter = New SqlDataAdapter("Select_CompanyByCompany_Proc", cn)
            objAdapter.SelectCommand.CommandType = CommandType.StoredProcedure

            objAdapter.SelectCommand.Parameters.Add(New SqlParameter("@CompanyID", SqlDbType.VarChar))

            objAdapter.SelectCommand.Parameters("@CompanyID").Value = CompanyID

            ObjDS = New DataSet
            objAdapter.Fill(ObjDS, "CompanyDetails")

            Return ObjDS

        Catch ex As Exception
            Throw ex
        Finally
            cn.Close()
            cn.Dispose()
        End Try
    End Function

    Public Function SelectCustomerByCustomerID(ByVal CustomerID As String) As DataSet
        Dim cn As New System.Data.SqlClient.SqlConnection(ConfigurationManager.AppSettings("Connection4Voffice"))
        Dim objAdapter As SqlDataAdapter
        Dim ObjDS As DataSet

        cn.Open()

        Try
            objAdapter = New SqlDataAdapter("Select_CRMCustomerByCustomerID_Proc", cn)
            objAdapter.SelectCommand.CommandType = CommandType.StoredProcedure

            objAdapter.SelectCommand.Parameters.Add(New SqlParameter("@CustomerID", SqlDbType.VarChar))

            objAdapter.SelectCommand.Parameters("@CustomerID").Value = CustomerID

            ObjDS = New DataSet
            objAdapter.Fill(ObjDS, "CustomerDetails")

            Return ObjDS

        Catch ex As Exception
            Throw ex
        Finally
            cn.Close()
            cn.Dispose()
        End Try
    End Function

    Public Shared Function SelectProductByProductID(ByVal ProductID As String) As DataSet
        Dim cn As New System.Data.SqlClient.SqlConnection(ConfigurationManager.AppSettings("ConnectionString"))

        Dim Str As String

        Str = "select ProductInfo.* from ProductInfo where Product_Kid='" & ProductID.Trim & "'"

        Dim objAdapter As SqlDataAdapter
        Dim ObjDS As DataSet

        cn.Open()

        Try
            objAdapter = New SqlDataAdapter(Str, cn)
            objAdapter.SelectCommand.CommandType = CommandType.Text

            ObjDS = New DataSet
            objAdapter.Fill(ObjDS, "Products")

            Return ObjDS

        Catch ex As Exception
            Throw ex
        Finally
            cn.Close()
            cn.Dispose()
        End Try

    End Function

    Public Shared Function SelectProductTechnicalSpecificationByProductID(ByVal ProductID As String) As DataSet
        Dim cn As New System.Data.SqlClient.SqlConnection(ConfigurationManager.AppSettings("ConnectionString"))

        Dim Str As String

        Str = "select ProductTechnicalSpecification.* from ProductTechnicalSpecification where TechSpec_ProductId='" & ProductID.Trim & "'"

        Dim objAdapter As SqlDataAdapter
        Dim ObjDS As DataSet

        cn.Open()

        Try
            objAdapter = New SqlDataAdapter(Str, cn)
            objAdapter.SelectCommand.CommandType = CommandType.Text

            ObjDS = New DataSet
            objAdapter.Fill(ObjDS, "Products")

            Return ObjDS

        Catch ex As Exception
            Throw ex
        Finally
            cn.Close()
            cn.Dispose()
        End Try

    End Function

    Public Function SelectQuotationDataByQuotationID(ByVal QuotationID As String, ByVal CompanyID As String) As DataSet

        Dim cn As New System.Data.SqlClient.SqlConnection(ConfigurationManager.AppSettings("Connection4Voffice"))
        Dim objAdapter As SqlDataAdapter
        Dim ObjDS As DataSet

        cn.Open()

        Try
            objAdapter = New SqlDataAdapter("Select_CRMQuotationDataByQuotationID_Proc", cn)
            objAdapter.SelectCommand.CommandType = CommandType.StoredProcedure

            objAdapter.SelectCommand.Parameters.Add(New SqlParameter("@QuotationID", SqlDbType.VarChar))
            objAdapter.SelectCommand.Parameters.Add(New SqlParameter("@CompanyID", SqlDbType.VarChar))

            objAdapter.SelectCommand.Parameters("@QuotationID").Value = QuotationID
            objAdapter.SelectCommand.Parameters("@CompanyID").Value = CompanyID

            ObjDS = New DataSet
            objAdapter.Fill(ObjDS, "QuotationData")

            Return ObjDS

        Catch ex As Exception
            Throw ex
        Finally
            cn.Close()
            cn.Dispose()
        End Try

    End Function

    Protected Sub cmdViewQuot_Click(ByVal sender As Object, ByVal e As System.Web.UI.ImageClickEventArgs)
        'Try
        Dim script As String

        script = "window.open(""{0}"", ""{1}"", ""{2}"");"

        script = String.Format(script, "viewquotation.aspx?qid=" & txtQuotationID.Text.Trim & "", "_blank", "status=yes,scollbars=yes,menubar=yes,location=no,toolbars=no,width=600,height=800,right=300,left=0,top=0")

        'status=yes,toolbar=no,menubar=no,location=no
        ScriptManager.RegisterStartupScript(Page, GetType(Page), "Redirect", script, True)

        '    Session("CRMProspectsId") = txtQuotationID.Text
        '    Call Redirect("QuotationDetails.aspx", "_blank", "")
        'Catch ex As Exception

        'End Try
    End Sub

    Protected Sub cmdSendQuot_Click(ByVal sender As Object, ByVal e As System.Web.UI.ImageClickEventArgs)
        Try
            Dim script As String

            script = "window.open(""{0}"", ""{1}"", ""{2}"");"

            script = String.Format(script, "sendquotation.aspx?qid=" & txtQuotationID.Text.Trim, "_blank", "status=no,scollbars=yes,toolbars=no,width=600,height=600,right=300,left=0,top=0")

            ScriptManager.RegisterStartupScript(Page, GetType(Page), "Redirect", script, True)

            '    Session("CRMProspectsId") = txtQuotationID.Text
            '    Call Redirect("QuotationDetails.aspx", "_blank", "")
        Catch ex As Exception

        End Try
    End Sub

End Class