Imports AcggDoc.Acgg.dbClass
Imports System.Data
Imports System.Data.OleDb





Namespace Acgg
    Public Class Details1
        Inherits System.Web.UI.Page

#Region " Web Form Designer Generated Code "

        'This call is required by the Web Form Designer.
        <System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()

        End Sub
        Protected WithEvents Label1 As System.Web.UI.WebControls.Label
        Protected WithEvents Label2 As System.Web.UI.WebControls.Label
        Protected WithEvents cmdOk As System.Web.UI.WebControls.Button
        Protected WithEvents Label3 As System.Web.UI.WebControls.Label
        Protected WithEvents Label4 As System.Web.UI.WebControls.Label
        Protected WithEvents Label5 As System.Web.UI.WebControls.Label
        Protected WithEvents Label6 As System.Web.UI.WebControls.Label
        Protected WithEvents Label7 As System.Web.UI.WebControls.Label
        Protected WithEvents Label8 As System.Web.UI.WebControls.Label
        Protected WithEvents Label10 As System.Web.UI.WebControls.Label
        Protected WithEvents txtFile As System.Web.UI.WebControls.TextBox
        Protected WithEvents txtDueDate As System.Web.UI.WebControls.TextBox
        Protected WithEvents txtCldate As System.Web.UI.WebControls.TextBox
        Protected WithEvents cmbStatus As System.Web.UI.WebControls.DropDownList
        Protected WithEvents Label12 As System.Web.UI.WebControls.Label
        Protected WithEvents txtOpDate As System.Web.UI.WebControls.TextBox
        Protected WithEvents txtPurPrice As System.Web.UI.WebControls.TextBox
        Protected WithEvents Label13 As System.Web.UI.WebControls.Label
        Protected WithEvents txtLoanAmt As System.Web.UI.WebControls.TextBox
        Protected WithEvents cmbDevelopment As System.Web.UI.WebControls.DropDownList
        Protected WithEvents cmbProperty As System.Web.UI.WebControls.DropDownList
        Protected WithEvents lblError As System.Web.UI.WebControls.Label
        Protected WithEvents cmbBroker As System.Web.UI.WebControls.DropDownList
        Protected WithEvents txtBorrower As System.Web.UI.WebControls.TextBox
        Protected WithEvents Label14 As System.Web.UI.WebControls.Label
        Protected WithEvents DataGrid1 As System.Web.UI.WebControls.DataGrid
        Protected WithEvents Label17 As System.Web.UI.WebControls.Label
        Protected WithEvents Label18 As System.Web.UI.WebControls.Label
        Protected WithEvents Textbox1 As System.Web.UI.WebControls.TextBox
        Protected WithEvents File1 As System.Web.UI.HtmlControls.HtmlInputFile
        Protected WithEvents Submit1 As System.Web.UI.HtmlControls.HtmlInputButton
        Protected WithEvents hplupload As System.Web.UI.WebControls.HyperLink
        Protected WithEvents lnkUpload As System.Web.UI.WebControls.LinkButton
        Protected WithEvents Label9 As System.Web.UI.WebControls.Label
        Protected WithEvents cmbCLTime As System.Web.UI.WebControls.DropDownList
        Protected WithEvents cmbAMPM As System.Web.UI.WebControls.DropDownList
        Protected WithEvents lnkClose As System.Web.UI.WebControls.HyperLink
        Protected WithEvents Label11 As System.Web.UI.WebControls.Label
        Protected WithEvents txtPrjCLDate As System.Web.UI.WebControls.TextBox

        'NOTE: The following placeholder declaration is required by the Web Form Designer.
        'Do not delete or move it.
        Private designerPlaceholderDeclaration As System.Object

        Private Sub Page_Init(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Init
            'CODEGEN: This method call is required by the Web Form Designer
            'Do not modify it using the code editor.
            InitializeComponent()
        End Sub

#End Region
        Dim sConnString As String
        Dim fileNo As String

        Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
            'Put user code to initialize the page here
            'sConnString = "Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Registry Path=;Jet OLEDB:Database Locking Mode=1;Jet OLEDB:Database Password=;Data Source=" & Server.MapPath("AcggDoc.mdb") & ";Password=;Jet OLEDB:Engine Type=5;Jet OLEDB:Global Bulk Transactions=1;Provider=Microsoft.Jet.OLEDB.4.0;Jet OLEDB:System database=;Jet OLEDB:SFP=False;Extended Properties=;Mode=Share Deny None;Jet OLEDB:New Database Password=;Jet OLEDB:Create System Database=False;Jet OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;User ID=Admin;Jet OLEDB:Encrypt Database=False"

            If Session("UserId") = "" Then Response.End()

            If Session("UserCat") = 1 Or Session("UserCat") = 2 Then
                cmdOk.Enabled = True
            Else
                cmdOk.Enabled = False
            End If

            'lnkUpload.Attributes.Add("onclick", "javascript:window.open('AddDoc.aspx?FileNo=" & Request("FileNo") & "');")
            lnkUpload.Attributes.Add("onclick", "javascript:window.open('AddDoc.aspx?FileNo=" & Request("OrdID") & "');")

            If Not IsPostBack Then
                BindDevCombo()
                BindBrokerCombo()
                'BindPropertyCombo(cmbDevelopment.Items(cmbDevelopment.SelectedIndex).Value)
                PopulateFileds()
            End If
            PopulateDocGrid()
            If Request.QueryString("FromClose") = "True" Then
                Session("FromOrder") = "True"
                lnkClose.Visible = True
            Else
                Session("FromOrder") = "False"
                lnkClose.Visible = False
            End If
        End Sub

        Sub PopulateFileds()
            Dim objDBClass As New Acgg.dbClass
            Dim objRead As OleDb.OleDbDataReader
            ''Dim strQuery As String = "SELECT A.OrdId , A.FileNo , A.Opendate,A.DueDate,A.ClDate,A.LegalDesc, A.Status ,A.Borrower , A.LoanAmt,A.PurPrice,B.PropDesc, C.DevName , E.Name FROM NewOrders A , Property B , Developments C , Users E WHERE A.PropId=B.PropId AND B.DevId=C.DevID AND E.UserId=A.TitleCoUserID AND A.FileNo='" & Request.QueryString("FileNo") & "'"
            Dim strQuery As String = "SELECT A.OrdId , A.FileNo , A.Opendate,A.DueDate,A.PrjCLDate,A.ClDate, A.CLTime,A.LegalDesc, A.Status ,A.Borrower , A.LoanAmt,A.PurPrice,A.TitleCoUserID,B.PropDesc, C.DevId  FROM NewOrders A , Property B , Developments C WHERE A.PropId=B.PropId AND B.DevId=C.DevID AND OrdId = " & Request.QueryString("OrdId")
            'Dim strQuery As String = "SELECT A.OrdId , A.FileNo , A.Opendate,A.DueDate,A.PrjCLDate,A.ClDate, A.CLTime,A.LegalDesc, A.Status ,A.Borrower , A.LoanAmt,A.PurPrice,A.TitleCoUserID,B.PropDesc, C.DevId  FROM NewOrders A , Property B , Developments C WHERE A.PropId=B.PropId AND B.DevId=C.DevID AND A.FileNo='" & Request.QueryString("FileNo") & "'"
            Try
                'objDBClass.strConn = sConnString
                objDBClass.OpenDB()
                objRead = objDBClass.retReader(strQuery)
                cmbDevelopment.ClearSelection()
                cmbProperty.ClearSelection()
                cmbBroker.ClearSelection()
                cmbStatus.ClearSelection()
                cmbCLTime.ClearSelection()
                cmbAMPM.ClearSelection()
                cmbStatus.ClearSelection()

                Do While objRead.Read()

                    Application.Lock()
                    Application("OrderID") = objRead("OrdId")
                    Application.UnLock()

                    txtFile.Text = objRead("FileNo")
                    Session("fileNo") = objRead("FileNo")
                    'cmbDevelopment = objRead("DevId")
                    cmbDevelopment.Items.FindByValue(objRead("DevID")).Selected = True
                    BindPropertyCombo(objRead("DevID"))
                    cmbProperty.Items.FindByText(objRead("PropDesc")).Selected = True
                    txtBorrower.Text = objRead("Borrower")
                    txtLoanAmt.Text = objRead("LoanAmt")
                    txtPurPrice.Text = objRead("PurPrice")
                    txtOpDate.Text = objRead("OpenDate")
                    txtDueDate.Text = objRead("DueDate")
                    txtPrjCLDate.Text = objRead("PrjCLDate")
                    txtCldate.Text = IIf(Microsoft.VisualBasic.IsDBNull(objRead("ClDate")), "", objRead("ClDate"))
                    cmbCLTime.ClearSelection()
                    cmbCLTime.Items.FindByValue(Mid(objRead("CLTime"), 1, 2)).Selected = True

                    cmbAMPM.ClearSelection()
                    If Mid(objRead("CLTime"), 7, 2) = "AM" Then
                        cmbAMPM.Items(1).Selected = True
                    Else
                        cmbAMPM.Items(0).Selected = True
                    End If
                    cmbBroker.Items.FindByValue(objRead("TitleCoUserId")).Selected = True
                    cmbStatus.ClearSelection()
                    cmbStatus.Items.FindByValue(objRead("Status")).Selected = True
                Loop
                objRead.Close()
                objDBClass.CloseDB()

            Catch Exc As Exception
                lblError.Text = "Error: " & Exc.Message
            Finally
                objRead = Nothing
                objDBClass = Nothing
            End Try
        End Sub

        Sub PopulateDocGrid()
            Dim objDBClass As New Acgg.dbClass
            Dim strQuery As String = "SELECT A.ID,A.Desc,A.Location,A.Date,'Development Specific' AS Type,'' as MyDel FROM Doc A ,Developments B  WHERE A.DevId=B.DevID AND A.DevId=" & cmbDevelopment.SelectedValue
            strQuery = strQuery & " UNION SELECT A.ID,A.Desc,A.Location,A.Date,'Order Specific' AS Type, '[Delete]' as MyDel FROM Doc A,NewOrders B WHERE A.DevId=0 AND A.OrdId=B.OrdId AND B.OrdId=" & Request.QueryString("OrdId")
            strQuery = strQuery & " UNION SELECT ID,Desc,Location,Date,'General' AS Type, '' as MyDel FROM Doc WHERE DevId=0 AND OrdId=0"


            'strQuery = strQuery & " UNION SELECT ID,Desc,Location,Date,'Development Specific' AS Type, '' as DevName,'' as Prop FROM Doc WHERE DevId<>0 AND PropId=0"
            'strQuery = strQuery & " UNION SELECT A.ID,A.Desc,A.Location,A.Date,'Development Specific' AS Type , B.DevName,'' as Prop FROM Doc A ,Developments B WHERE A.DevId=B.DevID AND A.DevId<>0 AND A.PropId=0"

            Try
                'objDBClass.strConn = sConnString
                objDBClass.OpenDB()
                DataGrid1.DataSource = objDBClass.retDataSet(strQuery)
                DataGrid1.DataBind()
                objDBClass.CloseDB()

                'lblPage.Text = "Page " & DataGrid1.CurrentPageIndex + 1 & " of " & DataGrid1.PageCount

            Catch Exc As Exception
                Label3.Text = "Error: " & Exc.Message
            Finally
                objDBClass = Nothing
            End Try
        End Sub

        Private Sub cmdOk_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdOk.Click
            Dim objDBClass As New Acgg.dbClass
            Dim objRead As OleDb.OleDbDataReader
            Dim strQuery As String
            Dim strTime As String

            strTime = cmbCLTime.SelectedValue.ToString() & ":" & "00" & " " & cmbAMPM.SelectedValue.ToString()

            Try
                If boolValidateFields() = False Then
                    lblError.Text = "You have missed out on one or more compulsory fields."
                    Exit Try
                End If
                If CheckFileNo() Then
                    lblError.Text = "Duplicate File No..."
                    Exit Try
                End If

                'strQuery = "INSERT INTO NewOrders(FileNo,PropId,Borrower,LegalDesc,LoanAmt,PurPrice,Status,OpenDate,DueDate,ClDate,TitleCoUserID) VALUES('" & txtFile.Text & "'," & cmbProperty.SelectedValue & ",'" & txtBorrower.Text & "','" & txtDesc.Text & "'," & txtLoanAmt.Text & "," & txtPurPrice.Text & ",'" & cmbStatus.SelectedValue & "','" & txtOpDate.Text & "','" & txtDueDate.Text & "','" & txtCldate.Text & "','" & txtBroker.Text & "')"
                If txtCldate.Text <> "" Then
                    strQuery = "UPDATE NewOrders SET PropId=" & Request("cmbProperty") & ",Borrower='" & Request("txtBorrower") & "',LoanAmt=" & Request("txtLoanAmt") & ",PurPrice=" & Request("txtPurPrice") & ",Status='" & Request("cmbStatus") & "',OpenDate='" & Request("txtOpDate") & "',DueDate='" & Request("txtDueDate") & "',PrjCLDate = '" & Request("txtPrjCLdate") & "',ClDate='" & Request("txtCldate") & "',ClTime='" & strTime & "',TitleCoUserID='" & Request("cmbBroker") & "' WHERE OrdId=" & Request.QueryString("OrdID")
                Else
                    strQuery = "UPDATE NewOrders SET PropId=" & Request("cmbProperty") & ",Borrower='" & Request("txtBorrower") & "',LoanAmt=" & Request("txtLoanAmt") & ",PurPrice=" & Request("txtPurPrice") & ",Status='" & Request("cmbStatus") & "',OpenDate='" & Request("txtOpDate") & "',DueDate='" & Request("txtDueDate") & "',PrjCLDate = '" & Request("txtPrjCLdate") & "',ClTime='" & strTime & "',TitleCoUserID='" & Request("cmbBroker") & "' WHERE OrdId=" & Request.QueryString("OrdID")
                End If

                objDBClass.OpenDB()
                If objDBClass.boolManipulateData(strQuery) Then
                    lblError.Text = "Updation successful..."
                End If
                objDBClass.CloseDB()

            Catch Exc As Exception
                lblError.Text = "Error: " & Exc.Message
            Finally
                objRead = Nothing
                objDBClass = Nothing
            End Try

        End Sub

        Function boolValidateFields() As Boolean
            If Trim(txtFile.Text) = "" Or Trim(txtBorrower.Text) = "" Or Trim(txtLoanAmt.Text) = "" Or Trim(txtPurPrice.Text) = "" Or Trim(txtOpDate.Text) = "" Or Trim(txtDueDate.Text) = "" Or Trim(txtPrjCLDate.Text) = "" Then
                boolValidateFields = False
                Exit Function
            Else
                boolValidateFields = True
            End If
        End Function


        Private Sub BindPropertyCombo(ByVal intDevID As Integer)
            cmbProperty.Items.Clear()
            Dim objDBClass As New Acgg.dbClass
            Dim strQuery As String = "SELECT PropID,PropDesc FROM Property WHERE DevId=" & intDevID
            Try
                'objDBClass.strConn = sConnString
                objDBClass.OpenDB()
                cmbProperty.DataSource = objDBClass.retReader(strQuery)
                cmbProperty.DataMember = "PropId"
                cmbProperty.DataTextField = "PropDesc"
                cmbProperty.DataValueField = "PropId"
                cmbProperty.DataBind()
                objDBClass.CloseDB()

            Catch Exc As Exception
                'Label4.Text = "Error: " & Exc.Message
            Finally
                objDBClass = Nothing
            End Try
        End Sub

        Private Sub BindDevCombo()
            Dim objDBClass As New Acgg.dbClass
            Dim strQuery As String = "SELECT DevID,DevName FROM Developments"
            Try
                'bjDBClass.strConn = sConnString

                objDBClass.OpenDB()
                cmbDevelopment.DataSource = objDBClass.retReader(strQuery)
                cmbDevelopment.DataMember = "DevId"
                cmbDevelopment.DataTextField = "DevName"
                cmbDevelopment.DataValueField = "DevId"
                cmbDevelopment.DataBind()
                objDBClass.CloseDB()

            Catch Exc As Exception
                'Label4.Text = "Error: " & Exc.Message
            Finally
                objDBClass = Nothing
            End Try
        End Sub

        Private Sub BindBrokerCombo()
            Dim objDBClass As New Acgg.dbClass
            Dim strQuery As String = "SELECT UserId,Name FROM Users WHERE UserCat=3"
            Try
                'objDBClass.strConn = sConnString
                objDBClass.OpenDB()
                cmbBroker.DataSource = objDBClass.retReader(strQuery)
                cmbBroker.DataMember = "UserId"
                cmbBroker.DataTextField = "Name"
                cmbBroker.DataValueField = "UserId"
                cmbBroker.DataBind()
                objDBClass.CloseDB()
            Catch Exc As Exception
                'Label4.Text = "Error: " & Exc.Message
            Finally
                objDBClass = Nothing
            End Try
        End Sub

        Public Sub cmbDevelopment_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmbDevelopment.SelectedIndexChanged
            BindPropertyCombo(Request("cmbDevelopment"))
        End Sub

        Public Sub cmbDocs_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs)
            If Not Request("cmbDocs") = "[Select]" Then
                Dim sb As String
                sb = "<script>"
                sb = sb & "window.open('//localhost/AcggDoc/Documents/" & Request("cmbDocs") & "','','');"
                sb = sb & "</script>"
                Page.RegisterStartupScript("test", sb)
            End If
        End Sub

        Sub Paginate(ByVal Source As Object, ByVal E As DataGridPageChangedEventArgs)
            DataGrid1.CurrentPageIndex = E.NewPageIndex
            PopulateDocGrid()
        End Sub

        Private Sub DataGrid1_ItemCommand(ByVal source As Object, ByVal e As System.Web.UI.WebControls.DataGridCommandEventArgs) Handles DataGrid1.ItemCommand
            Dim iid As Integer
            Dim l As LinkButton
            If e.Item.ItemType = ListItemType.Item Or e.Item.ItemType = ListItemType.AlternatingItem Then
                l = CType(e.Item.Cells(3).FindControl("cmdDel"), LinkButton)
                iid = Convert.ToInt32(e.Item.Cells(1).Text)

                Dim strQuery As String = "Delete FROM Doc WHERE Id= " & iid
                Dim myConnection As New OleDbConnection
                'myConnection.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\Inetpub\wwwroot\AcggDoc\AcggDoc.mdb"
                myConnection.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=d:/inetpub/virtual/imagery/db/AcggDoc.mdb"

                Dim myDeleteCommand As OleDbCommand = New OleDbCommand(strQuery, myConnection)
                myDeleteCommand.CommandType = CommandType.Text
                'DeleteFiles(iid)
                myConnection.Open()
                myDeleteCommand.ExecuteNonQuery() 'Delete the record
                myConnection.Close()

                PopulateDocGrid()
            End If
        End Sub

        Private Sub DataGrid1_ItemDataBound(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.DataGridItemEventArgs) Handles DataGrid1.ItemDataBound
            Dim l As LinkButton
            If e.Item.ItemType = ListItemType.Item Or e.Item.ItemType = ListItemType.AlternatingItem Then
                l = CType(e.Item.Cells(3).FindControl("cmdDel"), LinkButton)
                l.Attributes.Add("onclick", "return confirm('Are you sure you want to delete this File?');")
            End If
        End Sub
        Sub PagerButtonClick(ByVal sender As Object, ByVal e As ImageClickEventArgs)
            'Dim arg As String = sender.CommandArgument
            'Select Case arg
            '    Case "Next"
            '        If (DataGrid1.CurrentPageIndex < (DataGrid1.PageCount - 1)) Then
            '            DataGrid1.CurrentPageIndex() += 1

            '            If (DataGrid1.CurrentPageIndex = (DataGrid1.PageCount - 1)) Then
            '                NextButton.Enabled = False
            '                LastButton.Enabled = False
            '            Else
            '                FirstButton.Enabled = True
            '                PrevButton.Enabled = True
            '            End If
            '        End If
            '    Case "Prev"
            '        If (DataGrid1.CurrentPageIndex > 0) Then
            '            DataGrid1.CurrentPageIndex() -= 1

            '            If (DataGrid1.CurrentPageIndex = 0) Then
            '                FirstButton.Enabled = False
            '                PrevButton.Enabled = False
            '            Else
            '                NextButton.Enabled = True
            '                LastButton.Enabled = True
            '            End If
            '        End If
            '    Case "Last"
            '        DataGrid1.CurrentPageIndex() = (DataGrid1.PageCount - 1)

            '        FirstButton.Enabled = True
            '        PrevButton.Enabled = True
            '        NextButton.Enabled = False
            '        LastButton.Enabled = False
            '    Case Else
            '        DataGrid1.CurrentPageIndex = Convert.ToInt32(arg)

            '        FirstButton.Enabled = False
            '        PrevButton.Enabled = False
            '        NextButton.Enabled = True
            '        LastButton.Enabled = True

            'End Select
            'PopulateDocGrid()
        End Sub
        Private Sub DataGrid1_ItemCreated(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.DataGridItemEventArgs) Handles DataGrid1.ItemCreated
            If e.Item.ItemType = ListItemType.Pager Then
                Dim oControl As Control
                Dim x As Integer
                x = 0

                For Each oControl In e.Item.Cells(0).Controls
                    If x > 0 Then Exit For
                    If oControl.GetType.ToString = "System.Web.UI.WebControls.DataGridLinkButton" Then
                        CType(oControl, LinkButton).Text = "Page " & CType(oControl, LinkButton).Text
                        x = x + 1
                    ElseIf TypeOf oControl Is Label Then
                        CType(oControl, Label).Text = "Page " & CType(oControl, Label).Text
                        x = x + 1
                    End If
                Next
            End If
        End Sub
        Function CheckFileNo() As Boolean
            Dim objDBClass As New Acgg.dbClass
            Dim objRead As OleDb.OleDbDataReader
            Dim strQuery As String

            Try
                strQuery = "SELECT FileNo FROM NewOrders WHERE FileNo='" & Session("fileNo") & "' AND Status = '" & Request("cmbStatus") & "' and OrdId <> " & Request.QueryString("OrdId")
                objDBClass.OpenDB()
                objRead = objDBClass.retReader(strQuery)
                Return objRead.HasRows
                objDBClass.CloseDB()

            Catch Exc As Exception
                lblError.Text = "Error: " & Exc.Message
            Finally
                objRead = Nothing
                objDBClass = Nothing
            End Try

        End Function

    End Class
End Namespace