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

Namespace Acgg

    Public Class Orders
        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 Label3 As System.Web.UI.WebControls.Label
        Protected WithEvents Label2 As System.Web.UI.WebControls.Label
        Protected WithEvents Label5 As System.Web.UI.WebControls.Label
        Protected WithEvents cmdOk As System.Web.UI.WebControls.Button
        Protected WithEvents txtFileNo 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 Label4 As System.Web.UI.WebControls.Label
        Protected WithEvents txtComtDate As System.Web.UI.WebControls.TextBox
        Protected WithEvents Label7 As System.Web.UI.WebControls.Label
        Protected WithEvents txtClDate As System.Web.UI.WebControls.TextBox
        Protected WithEvents Label6 As System.Web.UI.WebControls.Label
        Protected WithEvents txtBorrower As System.Web.UI.WebControls.TextBox
        Protected WithEvents Label9 As System.Web.UI.WebControls.Label
        Protected WithEvents txtLoanAmt As System.Web.UI.WebControls.TextBox
        Protected WithEvents Label10 As System.Web.UI.WebControls.Label
        Protected WithEvents txtPurPrice As System.Web.UI.WebControls.TextBox
        Protected WithEvents cmbBroker As System.Web.UI.WebControls.DropDownList
        Protected WithEvents Label11 As System.Web.UI.WebControls.Label
        Protected WithEvents cmbStatus As System.Web.UI.WebControls.DropDownList
        Protected WithEvents Label12 As System.Web.UI.WebControls.Label
        Protected WithEvents lblError As System.Web.UI.WebControls.Label
        Protected WithEvents DataGrid1 As System.Web.UI.WebControls.DataGrid
        Protected WithEvents lnkUpload As System.Web.UI.WebControls.LinkButton
        Protected WithEvents Label8 As System.Web.UI.WebControls.Label
        Protected WithEvents cmbAMPM As System.Web.UI.WebControls.DropDownList
        Protected WithEvents cmbCLTime As System.Web.UI.WebControls.DropDownList
        Protected WithEvents Label13 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

        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=" & txtFileNo.Text & "');")

            If Not IsPostBack Then
                BindDevCombo()
                'If (Not cmbDevelopment.Items.Count > 0) Then
                BindPropertyCombo(cmbDevelopment.Items(cmbDevelopment.SelectedIndex).Value)
                'End If
            BindBrokerCombo()
            BindStatusCombo()
            End If
            PopulateDocGrid()
        End Sub

        Sub PopulateDocGrid()
            Dim objDBClass As New Acgg.dbClass
            Dim strQuery As String = "SELECT A.Desc,A.Location,A.Date,'Development Specific' AS Type FROM Doc A ,Developments B  WHERE A.DevId=B.DevID AND A.DevId=" & cmbDevelopment.SelectedValue
            strQuery = strQuery & " UNION SELECT A.Desc,A.Location,A.Date,'Order Specific' AS Type FROM Doc A,NewOrders B WHERE A.DevId=0 AND A.OrdId=B.OrdId AND B.FileNo='" & txtFileNo.Text & "'"
            strQuery = strQuery & " UNION SELECT Desc,Location,Date,'General' AS Type 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()


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

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

                objDBClass.OpenDB()
                cmbDevelopment.DataSource = objDBClass.retReader(strQuery)

                'objRead = objDBClass.retReader(strQuery)
                'If (Not objRead.HasRows) Then
                'Response.Redirect("Developments.aspx")
                'End If

                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 BindPropertyCombo(ByVal intDevID As Integer)
            Dim objDBClass As New Acgg.dbClass

            Dim objRead As OleDbDataReader

            Dim strQuery As String = "SELECT PropID,PropDesc FROM Property WHERE DevId=" & intDevID
            Try
                'objDBClass.strConn = sConnString
                objDBClass.OpenDB()
                cmbProperty.DataSource = objDBClass.retReader(strQuery)

                'objRead = objDBClass.retReader(strQuery)
                'If (Not objRead.HasRows()) Then
                'Response.Redirect("Properties.aspx")
                'End If

                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 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

        Private Sub BindStatusCombo()
            cmbStatus.Items.Add("OPEN")
            cmbStatus.Items.Add("CLOSED")
            cmbStatus.Items.Add("CANCELLED")
        End Sub

        Public Sub cmbDevelopment_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmbDevelopment.SelectedIndexChanged
            BindPropertyCombo(Request("cmbDevelopment"))
        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 Sub
                End If
                If blNumeric() = False Then
                    lblError.Text = "Please enter numeric values only, in Loan Amount and Purchase Price fields."
                    Exit Sub
                End If
                If blBlank() = False Then
                    lblError.Text = "File Number can not contain blank spaces."
                    Exit Sub
                End If
                If CheckFileNo() Then
                    lblError.Text = "Duplicate File No..."
                    Exit Try
                Else
                    If txtClDate.Text <> "" Then
                        strQuery = "INSERT INTO NewOrders(FileNo,PropId,Borrower,LoanAmt,PurPrice,Status,OpenDate,DueDate,PrjCLDate,ClDate,TitleCoUserId,CLTime) VALUES('" & Replace(Request("txtFileNo"), "'", "") & "'," & Request("cmbProperty") & ",'" & Replace(Request("txtBorrower"), "'", "") & "'," & Replace(Request("txtLoanAmt"), "'", "") & "," & Replace(Request("txtPurPrice"), "'", "") & ",'" & Request("cmbStatus") & "',Date(),'" & Replace(Request("txtComtDate"), "'", "") & "','" & Replace(Request("txtPrjCLDate"), "'", "") & "','" & Replace(Request("txtClDate"), "'", "") & "','" & Request("cmbBroker") & "','" & strTime & "')"
                    Else
                        strQuery = "INSERT INTO NewOrders(FileNo,PropId,Borrower,LoanAmt,PurPrice,Status,OpenDate,DueDate,PrjCLDate,TitleCoUserId,CLTime) VALUES('" & Replace(Request("txtFileNo"), "'", "") & "'," & Request("cmbProperty") & ",'" & Replace(Request("txtBorrower"), "'", "") & "'," & Replace(Request("txtLoanAmt"), "'", "") & "," & Replace(Request("txtPurPrice"), "'", "") & ",'" & Request("cmbStatus") & "',Date(),'" & Replace(Request("txtComtDate"), "'", "") & "','" & Replace(Request("txtPrjCLDate"), "'", "") & "','" & Request("cmbBroker") & "','" & strTime & "')"
                    End If
                End If

                objDBClass.OpenDB()
                If objDBClass.boolManipulateData(strQuery) Then
                    lblError.Text = "Updation successful..."
                    lnkUpload.Visible = True
                    cmdOk.Enabled = False
                End If
                objDBClass.CloseDB()
                'CreateOrderFolder(txtFileNo.Text)
                'Response.Redirect("Orders.aspx")

            Catch Exc As Exception
                lblError.Text = "Error: " & Exc.Message
            Finally
                'objRead = Nothing
                objDBClass = Nothing
            End Try
        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='" & Request("txtFileNo") & "' AND Status = '" & Request("cmbStatus") & "'"
                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

        Function boolValidateFields() As Boolean
            boolValidateFields = False

            If Trim(txtFileNo.Text) = "" Then
                boolValidateFields = False
                Exit Function
            End If
            If Trim(txtBorrower.Text) = "" Then
                boolValidateFields = False
                Exit Function
            End If
            If Trim(txtLoanAmt.Text) = "" Then
                boolValidateFields = False
                Exit Function
            End If
            If Trim(txtPurPrice.Text) = "" Then
                boolValidateFields = False
                Exit Function
            End If
            If Trim(txtComtDate.Text) = "" Then
                boolValidateFields = False
                Exit Function
            End If
            If Trim(txtPrjCLDate.Text) = "" Then
                boolValidateFields = False
                Exit Function
            End If
            boolValidateFields = True
        End Function
        Function blNumeric() As Boolean
            blNumeric = False
            If Not IsNumeric(txtLoanAmt.Text) Then
                blNumeric = False
                Exit Function
            End If
            If Not IsNumeric(txtPurPrice.Text) Then
                blNumeric = False
                Exit Function
            End If
            blNumeric = True
        End Function
        Function blBlank() As Boolean
            blBlank = False
            If InStr(txtFileNo.Text, " ", CompareMethod.Text) <> 0 Then
                blBlank = False
                Exit Function
            End If
            blBlank = True
        End Function

        Sub Paginate(ByVal Source As Object, ByVal E As DataGridPageChangedEventArgs)
            DataGrid1.CurrentPageIndex = E.NewPageIndex
            PopulateDocGrid()
        End Sub
        Private Sub CreateOrderFolder(ByVal FolderName As String)
            Dim myFolderName As String
            'myFolderName = "d:/inetpub/virtual/imagery/wwwroot/AcggDoc/" & FolderName
            myFolderName = "Orders"

            If (Directory.Exists(myFolderName) = False) Then
                Directory.CreateDirectory(myFolderName)
            End If
        End Sub

        Private Sub DataGrid1_PageIndexChanged(ByVal source As Object, ByVal e As System.Web.UI.WebControls.DataGridPageChangedEventArgs) Handles DataGrid1.PageIndexChanged

        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
    End Class
End Namespace