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

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

Public Class MaterialLibFileUpload : 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("Material_SrNo")
            Type1_columnNames.Add("Material_Name")
            Type1_columnNames.Add("Parent_SrNo")
            Type1_columnNames.Add("Item_type")
            Type1_columnNames.Add("Unit_Commercial")
            Type1_columnNames.Add("Unit_Technical")
            Type1_columnNames.Add("Conversion_Factor")
            Type1_columnNames.Add("Transport_Charges")
            Type1_columnNames.Add("MCat1_Id")
            Type1_columnNames.Add("MCat2_Id")
            Type1_columnNames.Add("MCat3_Id")
            Type1_columnNames.Add("Price_Basic")
            Type1_columnNames.Add("Tax_Scheme_Id")
            Type1_columnNames.Add("Specification")

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


            Dim hdFileName As String = "MaterialLib_" & 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 = 14) 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
                        Dim dttabletype As New DataTable

                        dttabletype.Columns.Add("Material_SrNo")
                        dttabletype.Columns.Add("Material_Name")
                        dttabletype.Columns.Add("Parent_SrNo")
                        dttabletype.Columns.Add("Item_type")
                        dttabletype.Columns.Add("Unit_Commercial")
                        dttabletype.Columns.Add("Unit_Technical")
                        dttabletype.Columns.Add("Conversion_Factor")
                        dttabletype.Columns.Add("Transport_Charges")
                        dttabletype.Columns.Add("MCat1_Id")
                        dttabletype.Columns.Add("MCat2_Id")
                        dttabletype.Columns.Add("MCat3_Id")
                        dttabletype.Columns.Add("Price_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)("Material_SrNo"))) Then
                                dr("Material_SrNo") = 0
                                'ShowMessage("Material_SrNo is mandatory Field in Excel at Row No." + ((iDt + 2).ToString()).ToString(), "error")
                                _excelInfo.IsSucess = False
                                _excelInfo.ClientMessage = "Material_SrNo is mandatory Field in Excel at Row No." + (iDt + 2).ToString()
                                context.Response.Write(JsonConvert.SerializeObject(_excelInfo))
                                Return
                            Else
                                Try
                                    dr("Material_SrNo") = Convert.ToInt32(Val(Table.Rows(iDt)("Material_SrNo").replace(",", "")))
                                Catch ex As Exception
                                    dr("Material_SrNo") = 0

                                    _excelInfo.IsSucess = False
                                    _excelInfo.ClientMessage = "Material_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)("Material_Name"))) Then
                                dr("Material_Name") = ""

                                _excelInfo.IsSucess = False
                                _excelInfo.ClientMessage = "Material_Name is mandatory Field in Excel at Row No." + (iDt + 2).ToString()
                                context.Response.Write(JsonConvert.SerializeObject(_excelInfo))
                                Return
                            Else
                                dr("Material_Name") = Convert.ToString(Table.Rows(iDt)("Material_Name")).Substring(0, Math.Min(Convert.ToString(Table.Rows(iDt)("Material_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)("Item_type"))) Then
                                dr("Item_type") = ""

                                _excelInfo.IsSucess = False
                                _excelInfo.ClientMessage = "Item_type 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)("Item_type")).Trim().ToLower() = "material") Then
                                    dr("Item_type") = "Material"

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

                                        _excelInfo.IsSucess = False
                                        _excelInfo.ClientMessage = "Unit_Technical 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_Technical").ToString().Trim()
                                            Dim strtasknmArray() As String = strtasknm.Split("_")
                                            dr("Unit_Technical") = strtasknmArray(0)
                                        Catch ex As Exception
                                            dr("Unit_Technical") = 0

                                            _excelInfo.IsSucess = False
                                            _excelInfo.ClientMessage = "Unit_Technical 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)("MCat1_Id"))) Then
                                        dr("MCat1_Id") = 0

                                        _excelInfo.IsSucess = False
                                        _excelInfo.ClientMessage = "MCat1_Id 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)("MCat1_Id").ToString().Trim()
                                            Dim strtasknmArray() As String = strtasknm.Split("_")
                                            dr("MCat1_Id") = strtasknmArray(0)
                                        Catch ex As Exception
                                            dr("MCat1_Id") = 0

                                            _excelInfo.IsSucess = False
                                            _excelInfo.ClientMessage = "MCat1_Id 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)("Item_type")).Trim().ToLower() = "group") Then
                                    dr("Item_type") = "Group"

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

                                    _excelInfo.IsSucess = False
                                    _excelInfo.ClientMessage = "Invalid Item_type 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)("Unit_Commercial"))) Then
                                dr("Unit_Commercial") = 0
                            Else
                                Try
                                    Dim strtasknm As String = Table.Rows(iDt)("Unit_Commercial").ToString().Trim()
                                    Dim strtasknmArray() As String = strtasknm.Split("_")
                                    dr("Unit_Commercial") = strtasknmArray(0)
                                Catch ex As Exception
                                    dr("Unit_Commercial") = 0
                                End Try
                            End If

                            If String.IsNullOrEmpty(Convert.ToString(Table.Rows(iDt)("Conversion_Factor"))) Then
                                dr("Conversion_Factor") = 0
                            Else
                                Try
                                    dr("Conversion_Factor") = Convert.ToDecimal(Table.Rows(iDt)("Conversion_Factor").ToString().Trim())
                                Catch ex As Exception
                                    dr("Conversion_Factor") = 0
                                End Try
                            End If
                            If String.IsNullOrEmpty(Convert.ToString(Table.Rows(iDt)("Transport_Charges"))) Then
                                dr("Transport_Charges") = 0
                            Else
                                Try
                                    dr("Transport_Charges") = Convert.ToDecimal(Table.Rows(iDt)("Transport_Charges").ToString().Trim())
                                Catch ex As Exception
                                    dr("Transport_Charges") = 0
                                End Try
                            End If



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


                            If String.IsNullOrEmpty(Convert.ToString(Table.Rows(iDt)("Price_Basic"))) Then
                                dr("Price_Basic") = 0
                            Else
                                Try
                                    dr("Price_Basic") = Convert.ToDecimal(Table.Rows(iDt)("Price_Basic").ToString().Trim())
                                Catch ex As Exception
                                    dr("Price_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 objMaterialLibraryBL As New MaterialLibraryBL
                            objMaterialLibraryBL.MatLibImport(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

                    Else

                        _excelInfo.IsSucess = True
                        _excelInfo.ClientMessage = "InCorrect Excel Sheet "
                        context.Response.Write(JsonConvert.SerializeObject(_excelInfo))
                        Return
                    End If

                Else

                    _excelInfo.IsSucess = True
                    _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 = "MaterialLibraryDetails") Then
                For Each cell In worksheet.Cells(1, 1, 1, 14)
                    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, 14)

                    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



    ' Function to read the Excel file and return a DataTable
    Private Function ReadExcelFile(ByVal filePath As String) As DataTable
        ' Initialize DataTable to store Excel data
        Dim dt As New DataTable()

        ' Use EPPlus to open the Excel file
        Using package As New ExcelPackage(New FileInfo(filePath))
            ' Assuming we are working with the first worksheet
            Dim worksheet As ExcelWorksheet = package.Workbook.Worksheets(0)

            ' Loop through the rows and columns to fill the DataTable
            Dim startRow As Integer = worksheet.Dimension.Start.Row
            Dim endRow As Integer = worksheet.Dimension.End.Row
            Dim startCol As Integer = worksheet.Dimension.Start.Column
            Dim endCol As Integer = worksheet.Dimension.End.Column

            ' Add columns to DataTable based on the first row (headers)
            For col As Integer = startCol To endCol
                dt.Columns.Add(worksheet.Cells(1, col).Text)
            Next

            ' Add data rows to the DataTable
            For row As Integer = startRow + 1 To endRow
                Dim dataRow As DataRow = dt.NewRow()
                For col As Integer = startCol To endCol
                    dataRow(col - 1) = worksheet.Cells(row, col).Text
                Next
                dt.Rows.Add(dataRow)
            Next
        End Using

        Return dt
    End Function

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

End Class