Imports System.Data
Imports System.Data.SqlClient
Partial Class BusinessAssociates_ssa_query
    Inherits System.Web.UI.Page

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        txtSubject.ReadOnly = False
        If (Not IsPostBack) Then
            txtVCompanyID.Text = "f6b59677-9946-489c-8628-465872369428"
            txtSSAID.Text = Session("SSAID").ToString
            tblQuery.Visible = False
            gvReply.Visible = False
            lblReplyMsg.Visible = False
            FillQueriesForYou()
            FillQuery()
        End If
    End Sub

    Public Sub FillQueriesForYou()
        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_CRMQueryByToUserIDStr_Proc", cn)
            objAdapter.SelectCommand.CommandType = CommandType.StoredProcedure

            objAdapter.SelectCommand.Parameters.Add(New SqlParameter("@UserIDStr", SqlDbType.NVarChar, 500))
            objAdapter.SelectCommand.Parameters.Add(New SqlParameter("@UserMode", SqlDbType.Int))
            objAdapter.SelectCommand.Parameters.Add(New SqlParameter("@CompanyID", SqlDbType.NVarChar, 50))

            objAdapter.SelectCommand.Parameters("@UserIDStr").Value = "'" + txtSSAID.Text.Trim + "'"
            objAdapter.SelectCommand.Parameters("@UserMode").Value = 2
            objAdapter.SelectCommand.Parameters("@CompanyID").Value = "'" + txtVCompanyID.Text.Trim + "'"

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

            gvQueryForYou.DataSource = ObjDS
            gvQueryForYou.DataBind()

            ObjDS.Clear()
            ObjDS.Dispose()

        Catch ex As Exception

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

    Public Sub FillQuery()
        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_CRMQueryByUserID_Proc", cn)
            objAdapter.SelectCommand.CommandType = CommandType.StoredProcedure

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

            objAdapter.SelectCommand.Parameters("@UserID").Value = txtSSAID.Text
            objAdapter.SelectCommand.Parameters("@UserMode").Value = 1
            objAdapter.SelectCommand.Parameters("@CompanyID").Value = txtVCompanyID.Text.Trim

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

            gvMyQuery.DataSource = ObjDS
            gvMyQuery.DataBind()

            ObjDS.Clear()
            ObjDS.Dispose()

        Catch ex As Exception

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

    Public Sub FillReply()
        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_CRMReplyByQueryID_Proc", cn)
            objAdapter.SelectCommand.CommandType = CommandType.StoredProcedure

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

            objAdapter.SelectCommand.Parameters("@QueryID").Value = txtQueryID.text
            objAdapter.SelectCommand.Parameters("@CompanyID").Value = txtVCompanyID.Text

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

            gvReply.DataSource = ObjDS
            gvReply.DataBind()

            ObjDS.Clear()
            ObjDS.Dispose()

            Dim J As Integer

            For J = 0 To gvReply.Rows.Count - 1
                If gvReply.Rows(J).Cells(6).Text = 2 Then
                    gvReply.Rows(J).Cells(7).Text = FillAdminDetails(gvReply.Rows(J).Cells(5).Text.ToString) & " (Admin)"
                ElseIf gvReply.Rows(J).Cells(6).Text = 1 Then
                    gvReply.Rows(J).Cells(7).Text = FillSSADetails(gvReply.Rows(J).Cells(5).Text.ToString) & " (SSA)"
                End If
            Next

        Catch ex As Exception

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

    Public Function FillAdminDetails(ByVal AdminID As String) As String

        Dim Name As String

        Name = ""

        Dim Dr As DataRow
        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_EmployeeByAdminID_Proc", cn)
            objAdapter.SelectCommand.CommandType = CommandType.StoredProcedure

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

            objAdapter.SelectCommand.Parameters("@AdminID").Value = AdminID.ToString
            objAdapter.SelectCommand.Parameters("@CompanyID").Value = txtVCompanyID.Text.ToString

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

            If ObjDS.Tables(0).Rows.Count > 0 Then
                Dr = ObjDS.Tables(0).Rows(0)
                Name = Dr("V_Emp_Name")
            End If

        Catch ex As Exception

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

        Return Name

        ObjDS.Clear()
        ObjDS.Dispose()

    End Function

    Public Function FillSSADetails(ByVal SSAID As String) As String

        Dim Name As String

        Name = ""
        Dim cmd As New Data.SqlClient.SqlCommand()
        Dim con As New Data.SqlClient.SqlConnection
        con.ConnectionString = System.Configuration.ConfigurationManager.AppSettings("ConnectionString")

        Dim Str As String
        Str = "select * From SSA_Master Where SSA_KID='" & SSAID.ToString & "'"
        If con.State = Data.ConnectionState.Open Then
            con.Close()
        End If
        con.Open()
        cmd = New SqlCommand(Str, con)
        Dim adp As New SqlDataAdapter
        Dim ds As New DataSet
        Dim Dr As DataRow
        adp.SelectCommand = cmd
        adp.Fill(ds)

        If ds.Tables(0).Rows.Count > 0 Then
            Dr = ds.Tables(0).Rows(0)
            Name = Dr("SSA_FirstName") & "  " & Dr("SSA_LastName")
        End If

        Return Name
        ds.Clear()
        ds.Dispose()

        con.Close()
        con.Dispose()

    End Function

    Protected Sub gvQueryForYou_PageIndexChanging(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewPageEventArgs)
        gvQueryForYou.PageIndex = e.NewPageIndex
        FillQueriesForYou()
    End Sub

    Protected Sub gvMyQuery_PageIndexChanging(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewPageEventArgs)
        gvMyQuery.PageIndex = e.NewPageIndex
        FillQuery()
    End Sub

    Protected Sub lnkReplies1_Click(ByVal sender As Object, ByVal e As System.EventArgs)
        Dim KID As String
        Dim lnk As LinkButton
        lnk = CType(sender, LinkButton)
        KID = lnk.CommandArgument

        txtQueryID.Text = KID
        tblQuery.Visible = False
        lblReplyMsg.Visible = True
        FillQueryDetails()
        gvReply.Visible = True
        txtSubject.Text = ""
        FillReply()
    End Sub

    Protected Sub lnkReplies_Click(ByVal sender As Object, ByVal e As System.EventArgs)
        Dim KID As String
        Dim lnk As LinkButton
        lnk = CType(sender, LinkButton)
        KID = lnk.CommandArgument

        txtQueryID.Text = KID

        tblQuery.Visible = False
        lblReplyMsg.Visible = True
        FillQueryDetails()
        gvReply.Visible = True
        FillReply()
    End Sub

    Protected Sub gvReply_PageIndexChanging(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewPageEventArgs)
        gvReply.PageIndex = e.NewPageIndex
        FillReply()
    End Sub

    Protected Sub lnkPostReply1_Click(ByVal sender As Object, ByVal e As System.EventArgs)
        Dim KID As String
        Dim lnk As LinkButton
        lnk = CType(sender, LinkButton)
        KID = lnk.CommandArgument

        txtQueryID.Text = KID
        FillQueryDetails()
        txtSubject.ReadOnly = True
        lblReplyMsg.Visible = False
        lblErrorData.Visible = False
        lblErrorData.Text = ""
        gvReply.Visible = False
        txtQueryMode.Text = "Reply"
        tblQuery.Visible = True
       
    End Sub

    Protected Sub lnkPostReply_Click(ByVal sender As Object, ByVal e As System.EventArgs)
        Dim KID As String
        Dim lnk As LinkButton
        lnk = CType(sender, LinkButton)
        KID = lnk.CommandArgument

        txtQueryID.Text = KID
        lblErrorData.Visible = False
        lblErrorData.Text = ""
        FillQueryDetails()
        txtSubject.ReadOnly = True
        txtQueryMode.Text = "Reply"
        tblQuery.Visible = True
    End Sub

    Public Sub FillQueryDetails()

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

        Dim ObjDR As DataRow

        cn.Open()

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

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

            objAdapter.SelectCommand.Parameters("@QueryID").Value = txtQueryID.Text.Trim
            objAdapter.SelectCommand.Parameters("@CompanyID").Value = txtVCompanyID.Text.Trim

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

            If ObjDS.Tables(0).Rows.Count > 0 Then
                ObjDR = ObjDS.Tables(0).Rows(0)
                txtSubject.Text = "Re : " & ObjDR("V_CRM_QuerySubject")
                lblReplyMsg.Text = "<b>Replies :</b>" & ObjDR("V_CRM_QuerySubject")

                ' txtDescription.Text = ObjDR("V_CRM_QueryDescription")
                ' txtAttachment.Text = ObjDR("V_CRM_QueryAttachment")
            End If


        Catch ex As Exception

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


    End Sub

    Protected Sub cmdQuery_Click(ByVal sender As Object, ByVal e As System.Web.UI.ImageClickEventArgs)
        tblQuery.Visible = True
        lblReplyMsg.Visible = False
        gvReply.Visible = False
        txtSubject.Text = ""
        txtDescription.Text = ""
        txtQueryMode.Text = "Query"
    End Sub

    Protected Sub cmdSubmit_Click(ByVal sender As Object, ByVal e As System.Web.UI.ImageClickEventArgs)
        Dim cn As New System.Data.SqlClient.SqlConnection(ConfigurationManager.AppSettings("Connection4Voffice"))
        Dim objAdapter As SqlDataAdapter

        If txtQueryMode.Text = "Query" Then

            cn.Open()

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

                objAdapter.SelectCommand.Parameters.Add(New SqlParameter("@QueryID", SqlDbType.VarChar))
                objAdapter.SelectCommand.Parameters.Add(New SqlParameter("@QueryDate", SqlDbType.DateTime))
                objAdapter.SelectCommand.Parameters.Add(New SqlParameter("@QuerySubject", SqlDbType.NVarChar, 100))
                objAdapter.SelectCommand.Parameters.Add(New SqlParameter("@QueryDescription", SqlDbType.Text))
                objAdapter.SelectCommand.Parameters.Add(New SqlParameter("@QueryAttachment", SqlDbType.NVarChar, 100))
                objAdapter.SelectCommand.Parameters.Add(New SqlParameter("@QueryUserID", SqlDbType.VarChar))
                objAdapter.SelectCommand.Parameters.Add(New SqlParameter("@QueryToUserID", SqlDbType.VarChar))
                objAdapter.SelectCommand.Parameters.Add(New SqlParameter("@QueryUserMode", SqlDbType.Int))
                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("@QueryID").Value = System.Guid.NewGuid.ToString
                objAdapter.SelectCommand.Parameters("@QueryDate").Value = System.DateTime.Now
                objAdapter.SelectCommand.Parameters("@QuerySubject").Value = txtSubject.Text.Trim
                objAdapter.SelectCommand.Parameters("@QueryDescription").Value = txtDescription.Text.Trim
                objAdapter.SelectCommand.Parameters("@QueryAttachment").Value = ""
                objAdapter.SelectCommand.Parameters("@QueryUserID").Value = txtSSAID.Text.Trim
                objAdapter.SelectCommand.Parameters("@QueryToUserID").Value = "00000000-0000-0000-0000-000000000000"
                objAdapter.SelectCommand.Parameters("@QueryUserMode").Value = 1
                objAdapter.SelectCommand.Parameters("@CompanyID").Value = txtVCompanyID.Text.Trim
                objAdapter.SelectCommand.Parameters("@FormName").Value = "CRM Query"
                objAdapter.SelectCommand.Parameters("@Mode").Value = "Insert"

                objAdapter.SelectCommand.ExecuteNonQuery()

                con.Close()
                con.Dispose()
                showmessage("Record Added Successfully.", lblErrorData)

                txtSubject.Text = ""
                txtDescription.Text = ""
                tblQuery.Visible = False

                FillQueriesForYou()
                FillQuery()

            Catch ex As Exception
                showmessage("Please try again later.", lblErrorData)
            Finally

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

        ElseIf txtQueryMode.Text = "Reply" Then

            cn.Open()

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

                objAdapter.SelectCommand.Parameters.Add(New SqlParameter("@ReplyID", SqlDbType.VarChar))
                objAdapter.SelectCommand.Parameters.Add(New SqlParameter("@QueryDate", SqlDbType.DateTime))
                objAdapter.SelectCommand.Parameters.Add(New SqlParameter("@QueryID", SqlDbType.VarChar))
                objAdapter.SelectCommand.Parameters.Add(New SqlParameter("@QueryDescription", SqlDbType.Text))
                objAdapter.SelectCommand.Parameters.Add(New SqlParameter("@QueryAttachment", SqlDbType.NVarChar, 100))
                objAdapter.SelectCommand.Parameters.Add(New SqlParameter("@QueryUserID", SqlDbType.VarChar))
                objAdapter.SelectCommand.Parameters.Add(New SqlParameter("@QueryUserMode", SqlDbType.Int))
                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("@ReplyID").Value = System.Guid.NewGuid.ToString
                objAdapter.SelectCommand.Parameters("@QueryDate").Value = System.DateTime.Now
                objAdapter.SelectCommand.Parameters("@QueryID").Value = txtQueryID.Text.Trim
                objAdapter.SelectCommand.Parameters("@QueryDescription").Value = txtDescription.Text.Trim
                objAdapter.SelectCommand.Parameters("@QueryAttachment").Value = ""
                objAdapter.SelectCommand.Parameters("@QueryUserID").Value = txtSSAID.Text.Trim
                objAdapter.SelectCommand.Parameters("@QueryUserMode").Value = 1
                objAdapter.SelectCommand.Parameters("@CompanyID").Value = txtVCompanyID.Text.Trim
                objAdapter.SelectCommand.Parameters("@FormName").Value = "CRM Reply"
                objAdapter.SelectCommand.Parameters("@Mode").Value = "Insert"

                objAdapter.SelectCommand.ExecuteNonQuery()

                con.Close()
                con.Dispose()
                showmessage("Record Added Successfully.", lblErrorData)

                txtSubject.Text = ""
                txtDescription.Text = ""
                tblQuery.Visible = False

                FillQueriesForYou()
                FillReply()

            Catch ex As Exception
                showmessage("Please try again later.", lblErrorData)
            Finally

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

    Protected Sub gvMyQuery_RowDataBound(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewRowEventArgs) Handles gvMyQuery.RowDataBound
        If e.Row.RowType = DataControlRowType.DataRow Then
            e.Row.Cells(1).Text = Format$(Convert.ToDateTime(e.Row.Cells(1).Text), "MMM-dd-yyyy")
        End If
    End Sub

    Protected Sub gvReply_RowDataBound(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewRowEventArgs) Handles gvReply.RowDataBound
        If e.Row.RowType = DataControlRowType.DataRow Then
            e.Row.Cells(2).Text = Format$(Convert.ToDateTime(e.Row.Cells(2).Text), "MMM-dd-yyyy")
        End If
    End Sub

    Protected Sub gvQueryForYou_RowDataBound(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewRowEventArgs) Handles gvQueryForYou.RowDataBound
        If e.Row.RowType = DataControlRowType.DataRow Then
            e.Row.Cells(1).Text = Format$(Convert.ToDateTime(e.Row.Cells(1).Text), "MMM-dd-yyyy")
            If e.Row.Cells(6).Text = 2 Then
                e.Row.Cells(7).Text = FillAdminDetails(e.Row.Cells(5).Text.ToString) & " (Admin)"
            ElseIf e.Row.Cells(6).Text = 1 Then
                e.Row.Cells(7).Text = FillSSADetails(e.Row.Cells(5).Text.ToString)
            End If

            txtQueryID.Text = e.Row.Cells(0).Text.ToString

            Dim DsReply As New DataSet

            DsReply = GetReply()

            If DsReply.Tables(0).Rows.Count > 0 Then
                e.Row.Cells(5).FindControl("lnkPostReply1").Visible = False
            Else
                e.Row.Cells(5).FindControl("lnkReplies1").Visible = False
            End If

            DsReply.Clear()
            DsReply.Dispose()
        End If
    End Sub

    Public Function GetReply() 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_CRMReplyByQueryID_Proc", cn)
            objAdapter.SelectCommand.CommandType = CommandType.StoredProcedure

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

            objAdapter.SelectCommand.Parameters("@QueryID").Value = txtQueryID.Text
            objAdapter.SelectCommand.Parameters("@CompanyID").Value = txtVCompanyID.Text

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

            Return ObjDS
        Catch

        End Try

    End Function
End Class