<%@ WebHandler Language="VB" Class="LabourLibFileUpload" %>

Imports System
Imports System.Web
Imports System.IO
Imports OfficeOpenXml
Imports System.Data
Imports Newtonsoft.Json
Imports BusinessLogicLayer

Public Class LabourLibFileUpload : Implements IHttpHandler, System.Web.SessionState.IRequiresSessionState


    Public Sub ProcessRequest(ByVal context As HttpContext) Implements IHttpHandler.ProcessRequest
        Dim _excelInfo As New ExcelInfo
        If context.Request.Files.Count > 0 Then


            Dim Type1_columnNames As List(Of String) = New List(Of String)()

            Type1_columnNames.Add("Labour_SrNo")
            Type1_columnNames.Add("Labour_Name")
            Type1_columnNames.Add("Parent_SrNo")
            Type1_columnNames.Add("ItemType")
            Type1_columnNames.Add("Unit")
            Type1_columnNames.Add("LCat1")
            Type1_columnNames.Add("LCat2")
            Type1_columnNames.Add("LCat3")
            Type1_columnNames.Add("LabourRate_Basic")
            Type1_columnNames.Add("Tax_Scheme_Id")
            Type1_columnNames.Add("Specification")

            Dim uploadedFile As HttpPostedFile = context.Request.Files(0)


            Dim hdFileName As String = "LabourLib_" & HttpContext.Current.Session("UserName") & "_" & uploadedFile.FileName

            Dim strFilePath As String = HttpContext.Current.Server.MapPath("~/MSPFiles/") & hdFileName
            uploadedFile.SaveAs(strFilePath)


            Dim Lib_Id As Integer = context.Request.Form("LibId")
            Dim Tree_ParentTaskNo As Integer = context.Request.Form("Tree_ParentTaskNo")

            Dim extension As String
            extension = Path.GetExtension(strFilePath)


            If (extension <> ".xlsx") Then

                _excelInfo.IsSucess = False
                _excelInfo.ClientMessage = "Please Select XLSX File Only!"
                context.Response.Write(JsonConvert.SerializeObject(_excelInfo))
                Return
            Else

                Dim Table = ImportFromExcel(strFilePath)

                If Table.Rows.Count > 0 Then

                    Dim chkexceleror As Boolean = True

                    If (Table.Columns.Count = 11) Then
                        Dim ColCount2 As Integer = 0
                        For Each col In Table.Columns
                            If (col.ColumnName.ToString() <> Type1_columnNames(ColCount2).ToString()) Then
                                chkexceleror = False
                            End If
                            ColCount2 = ColCount2 + 1
                        Next
                    Else
                        chkexceleror = False
                    End If

                    If (chkexceleror) Then
                        Try

                            Dim dttabletype As New DataTable

                            dttabletype.Columns.Add("Labour_SrNo")
                            dttabletype.Columns.Add("Labour_Name")
                            dttabletype.Columns.Add("Parent_SrNo")
                            dttabletype.Columns.Add("ItemType")
                            dttabletype.Columns.Add("Unit")
                            dttabletype.Columns.Add("LCat1")
                            dttabletype.Columns.Add("LCat2")
                            dttabletype.Columns.Add("LCat3")
                            dttabletype.Columns.Add("LabourRate_Basic")
                            dttabletype.Columns.Add("Tax_Scheme_Id")
                            dttabletype.Columns.Add("Specification")

                            Dim iDt As Integer = 0
                            For iDt = 0 To Table.Rows.Count - 1
                                Dim dr As DataRow = dttabletype.NewRow

                                If String.IsNullOrEmpty(Convert.ToString(Table.Rows(iDt)("Labour_SrNo"))) Then
                                    dr("Labour_SrNo") = 0
                                    ' ShowMessage("Labour_SrNo is mandatory Field in Excel at Row No." + (iDt + 2).ToString(), "error")

                                    _excelInfo.IsSucess = False
                                    _excelInfo.ClientMessage = "Labour_SrNo is mandatory Field in Excel at Row No." + (iDt + 2).ToString()
                                    context.Response.Write(JsonConvert.SerializeObject(_excelInfo))
                                    Return
                                Else
                                    Try
                                        dr("Labour_SrNo") = Convert.ToInt32(Val(Table.Rows(iDt)("Labour_SrNo").replace(",", "")))
                                    Catch ex As Exception
                                        dr("Labour_SrNo") = 0

                                        _excelInfo.IsSucess = False
                                        _excelInfo.ClientMessage = "Labour_SrNo Must be Numeric Field in Excel at Row No." + (iDt + 2).ToString()
                                        context.Response.Write(JsonConvert.SerializeObject(_excelInfo))
                                        Return

                                    End Try
                                End If



                                If String.IsNullOrEmpty(Convert.ToString(Table.Rows(iDt)("Labour_Name"))) Then
                                    dr("Labour_Name") = ""

                                    _excelInfo.IsSucess = False
                                    _excelInfo.ClientMessage = "Labour_Name is mandatory Field in Excel at Row No." + (iDt + 2).ToString()
                                    context.Response.Write(JsonConvert.SerializeObject(_excelInfo))
                                    Return
                                Else
                                    dr("Labour_Name") = Convert.ToString(Table.Rows(iDt)("Labour_Name")).Substring(0, Math.Min(Convert.ToString(Table.Rows(iDt)("Labour_Name")).Length, 59))
                                End If



                                If String.IsNullOrEmpty(Convert.ToString(Table.Rows(iDt)("Parent_SrNo"))) Then
                                    dr("Parent_SrNo") = 0

                                    _excelInfo.IsSucess = False
                                    _excelInfo.ClientMessage = "Parent_SrNo is mandatory Field in Excel at Row No." + (iDt + 2).ToString()
                                    context.Response.Write(JsonConvert.SerializeObject(_excelInfo))
                                    Return

                                Else
                                    Try
                                        dr("Parent_SrNo") = Convert.ToInt32(Val(Table.Rows(iDt)("Parent_SrNo").replace(",", "")))
                                    Catch ex As Exception
                                        dr("Parent_SrNo") = 0

                                        _excelInfo.IsSucess = False
                                        _excelInfo.ClientMessage = "Parent_SrNo Must be Numeric Field in Excel at Row No." + (iDt + 2).ToString()
                                        context.Response.Write(JsonConvert.SerializeObject(_excelInfo))
                                        Return
                                    End Try
                                End If



                                If String.IsNullOrEmpty(Convert.ToString(Table.Rows(iDt)("ItemType"))) Then
                                    dr("ItemType") = ""

                                    _excelInfo.IsSucess = False
                                    _excelInfo.ClientMessage = "ItemType is mandatory Field in Excel at Row No." + (iDt + 2).ToString()
                                    context.Response.Write(JsonConvert.SerializeObject(_excelInfo))
                                    Return


                                Else
                                    If (Convert.ToString(Table.Rows(iDt)("ItemType")).Trim().ToLower() = "labour") Then
                                        dr("ItemType") = "Labour"

                                        If String.IsNullOrEmpty(Convert.ToString(Table.Rows(iDt)("Unit"))) Then
                                            dr("Unit") = 0
                                            _excelInfo.IsSucess = False
                                            _excelInfo.ClientMessage = "Unit is mandatory Field in Excel at Row No." + (iDt + 2).ToString()
                                            context.Response.Write(JsonConvert.SerializeObject(_excelInfo))
                                            Return
                                        Else
                                            Try
                                                Dim strtasknm As String = Table.Rows(iDt)("Unit").ToString().Trim()
                                                Dim strtasknmArray() As String = strtasknm.Split("_")
                                                dr("Unit") = strtasknmArray(0)
                                            Catch ex As Exception
                                                dr("Unit") = 0

                                                _excelInfo.IsSucess = False
                                                _excelInfo.ClientMessage = "Unit is mandatory Field in Excel at Row No." + (iDt + 2).ToString()
                                                context.Response.Write(JsonConvert.SerializeObject(_excelInfo))
                                                Return
                                            End Try
                                        End If

                                        If String.IsNullOrEmpty(Convert.ToString(Table.Rows(iDt)("LCat1"))) Then
                                            dr("LCat1") = 0

                                            _excelInfo.IsSucess = False
                                            _excelInfo.ClientMessage = "LCat1 is mandatory Field in Excel at Row No." + (iDt + 2).ToString()
                                            context.Response.Write(JsonConvert.SerializeObject(_excelInfo))
                                            Return
                                        Else
                                            Try
                                                Dim strtasknm As String = Table.Rows(iDt)("LCat1").ToString().Trim()
                                                Dim strtasknmArray() As String = strtasknm.Split("_")
                                                dr("LCat1") = strtasknmArray(0)
                                            Catch ex As Exception
                                                dr("LCat1") = 0

                                                _excelInfo.IsSucess = False
                                                _excelInfo.ClientMessage = "LCat1 is mandatory Field in Excel at Row No." + (iDt + 2).ToString()
                                                context.Response.Write(JsonConvert.SerializeObject(_excelInfo))
                                                Return
                                            End Try
                                        End If

                                    ElseIf (Convert.ToString(Table.Rows(iDt)("ItemType")).Trim().ToLower() = "group") Then
                                        dr("ItemType") = "Group"

                                        If String.IsNullOrEmpty(Convert.ToString(Table.Rows(iDt)("Unit"))) Then
                                            dr("Unit") = 0
                                        Else
                                            Try
                                                Dim strtasknm As String = Table.Rows(iDt)("Unit").ToString().Trim()
                                                Dim strtasknmArray() As String = strtasknm.Split("_")
                                                dr("Unit") = strtasknmArray(0)
                                            Catch ex As Exception
                                                dr("Unit") = 0
                                            End Try
                                        End If

                                        If String.IsNullOrEmpty(Convert.ToString(Table.Rows(iDt)("LCat1"))) Then
                                            dr("LCat1") = 0
                                        Else
                                            Try
                                                Dim strtasknm As String = Table.Rows(iDt)("LCat1").ToString().Trim()
                                                Dim strtasknmArray() As String = strtasknm.Split("_")
                                                dr("LCat1") = strtasknmArray(0)
                                            Catch ex As Exception
                                                dr("LCat1") = 0
                                            End Try
                                        End If


                                    Else
                                        dr("ItemType") = ""
                                        _excelInfo.IsSucess = False
                                        _excelInfo.ClientMessage = "Invalid ItemType in Excel at Row No." + (iDt + 2).ToString()
                                        context.Response.Write(JsonConvert.SerializeObject(_excelInfo))
                                        Return
                                    End If
                                End If

                                If String.IsNullOrEmpty(Convert.ToString(Table.Rows(iDt)("LCat2"))) Then
                                    dr("LCat2") = 0
                                Else
                                    Try
                                        Dim strtasknm As String = Table.Rows(iDt)("LCat2").ToString().Trim()
                                        Dim strtasknmArray() As String = strtasknm.Split("_")
                                        dr("LCat2") = strtasknmArray(0)
                                    Catch ex As Exception
                                        dr("LCat2") = 0
                                    End Try
                                End If
                                If String.IsNullOrEmpty(Convert.ToString(Table.Rows(iDt)("LCat3"))) Then
                                    dr("LCat3") = 0
                                Else
                                    Try
                                        Dim strtasknm As String = Table.Rows(iDt)("LCat3").ToString().Trim()
                                        Dim strtasknmArray() As String = strtasknm.Split("_")
                                        dr("LCat3") = strtasknmArray(0)
                                    Catch ex As Exception
                                        dr("LCat3") = 0
                                    End Try
                                End If


                                If String.IsNullOrEmpty(Convert.ToString(Table.Rows(iDt)("LabourRate_Basic"))) Then
                                    dr("LabourRate_Basic") = 0
                                Else
                                    Try
                                        dr("LabourRate_Basic") = Convert.ToDecimal(Table.Rows(iDt)("LabourRate_Basic").ToString().Trim())
                                    Catch ex As Exception
                                        dr("LabourRate_Basic") = 0
                                    End Try
                                End If
                                If String.IsNullOrEmpty(Convert.ToString(Table.Rows(iDt)("Tax_Scheme_Id"))) Then
                                    dr("Tax_Scheme_Id") = 0
                                Else
                                    Try
                                        Dim strtasknm As String = Table.Rows(iDt)("Tax_Scheme_Id").ToString().Trim()
                                        Dim strtasknmArray() As String = strtasknm.Split("_")
                                        dr("Tax_Scheme_Id") = strtasknmArray(0)
                                    Catch ex As Exception
                                        dr("Tax_Scheme_Id") = 0
                                    End Try
                                End If
                                dr("Specification") = Convert.ToString(Table.Rows(iDt)("Specification"))

                                dttabletype.Rows.Add(dr)
                            Next

                            Try
                                Dim objLabourLibraryBL As New LabourLibraryBL
                                objLabourLibraryBL.LabourLibImport(dttabletype, Lib_Id, Tree_ParentTaskNo, HttpContext.Current.Session("UserName"), HttpContext.Current.Session("CompName"), SessionManager.GUID)
                                _excelInfo.IsSucess = True
                                _excelInfo.ClientMessage = "Excel updated successfully."
                                context.Response.Write(JsonConvert.SerializeObject(_excelInfo))

                            Catch ex As Exception
                                _excelInfo.IsSucess = False
                                _excelInfo.ClientMessage = ex.Message
                                context.Response.Write(JsonConvert.SerializeObject(_excelInfo))
                                Return
                            End Try


                        Catch ex As Exception

                            _excelInfo.IsSucess = False
                            _excelInfo.ClientMessage = ex.Message
                            context.Response.Write(JsonConvert.SerializeObject(_excelInfo))
                            Return
                        End Try
                    Else
                        _excelInfo.IsSucess = False
                        _excelInfo.ClientMessage = "InCorrect Excel Sheet"
                        context.Response.Write(JsonConvert.SerializeObject(_excelInfo))
                        Return

                    End If

                Else

                    _excelInfo.IsSucess = False
                    _excelInfo.ClientMessage = "Excel File has no records!"
                    context.Response.Write(JsonConvert.SerializeObject(_excelInfo))
                    Return

                End If

            End If




        Else

            _excelInfo.IsSucess = False
            _excelInfo.ClientMessage = "Excel File Not Attached!"
            context.Response.Write(JsonConvert.SerializeObject(_excelInfo))
            Return

        End If

    End Sub


    Public Function ImportFromExcel(ByVal strFilePath As String) As DataTable
        Dim columnNames As List(Of String) = New List(Of String)()
        Dim currentColumn As Integer = 1
        Dim Table As DataTable = New DataTable()
        Dim excelData As List(Of String) = New List(Of String)()
        Dim bin As Byte() = File.ReadAllBytes(strFilePath)
        Dim stream As MemoryStream = New MemoryStream(bin)
        Dim excelPackage As New ExcelPackage(stream)
        'Using excelPackage As New ExcelPackage(stream)
        For Each worksheet As ExcelWorksheet In excelPackage.Workbook.Worksheets
            If (worksheet.Name = "LabourLibraryDetails") Then
                For Each cell In worksheet.Cells(1, 1, 1, 11)
                    Dim columnName As String = cell.Text.Trim()

                    If cell.Start.Column <> currentColumn Then
                        columnNames.Add("Header_" & currentColumn)
                        Table.Columns.Add("Header_" & currentColumn)
                        currentColumn += 1
                    End If

                    columnNames.Add(columnName)
                    Table.Columns.Add(columnName)
                    currentColumn += 1
                Next

                For i As Integer = 2 To worksheet.Dimension.[End].Row
                    Dim row = worksheet.Cells(i, 1, i, 11)

                    Dim newRow As DataRow = Table.NewRow()
                    For Each cell In row
                        newRow(cell.Start.Column - 1) = cell.Text.Trim()
                    Next
                    Table.Rows.Add(newRow)

                Next
            End If
        Next
        Return Table
    End Function

    Public ReadOnly Property IsReusable As Boolean Implements IHttpHandler.IsReusable
        Get
            Return False
        End Get
    End Property
End Class