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

Imports System
Imports System.Web
Imports System.IO
Imports Newtonsoft.Json
Imports System.Data.OleDb
Imports System.Data
Imports ExcelInfo
Imports OfficeOpenXml

Public Class MatchExcelColumnHandler : Implements IHttpHandler, IRequiresSessionState
    
    Public Function ImportFromExcel(ByVal strFilePath As String, ByVal MBType As Integer) As DataTable
        
        Dim ColCount As Integer = 0
        If (MBType = 1) Then
            ColCount = 11
        ElseIf (MBType = 2) Then
            ColCount = 10
        End If
        
            
        
        
        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
            For Each cell In worksheet.Cells(1, 1, 1, ColCount)
                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, ColCount)

                If String.IsNullOrEmpty(DirectCast(row.Value, Object(,))(0, 0)) Then
                    Continue For
                Else
                    Dim newRow As DataRow = Table.NewRow()

                    For Each cell In row
                        newRow(cell.Start.Column - 1) = cell.Text.Trim()
                    Next
                    Table.Rows.Add(newRow)
                End If
            Next

        Next
        Return Table
    End Function

    
    Public Sub ProcessRequest(ByVal context As HttpContext) Implements IHttpHandler.ProcessRequest
        Dim excelInfo As New ExcelInfo
        Dim docPath As String = ""
        Dim newFilePath As String = ""
        Dim strStep As String = ""
        Dim connExcel As New OleDbConnection()
        Dim cmdExcel As New OleDbCommand()
        Dim MBType As Integer = 0
        Try
            If context.Request.Files.Count > 0 Then
                strStep = context.Request.Form("Step")
                MBType = context.Request.Form("MBType")
                
                Dim files As HttpFileCollection = context.Request.Files
                Dim ext As String = ""
                Dim conStr As String = ""
                Dim strNewFileName As String = ""
                Dim Filepath As String = ""
                Dim Sql As String = ""
                
                If strStep = "1" Then
                    For i As Integer = 0 To files.Count - 1
                        
                        Dim file As HttpPostedFile = files(i)
                        ext = Path.GetExtension(file.FileName)
                        If ext <> ".xls" And ext <> ".xlsx" Then
                            Throw New Exception("File Are not Supported For Import Attendance")
                        End If
                        
                        strNewFileName = Format(DateTime.Now, "yyyyMMdd") & "_" & Format(DateTime.Now, "HHmmss") & Path.GetExtension(file.FileName)
                        strNewFileName = strNewFileName.Replace(",", " ")
                        If System.IO.File.Exists(HttpContext.Current.Server.MapPath("~/Temp/" & strNewFileName)) Then
                            System.IO.File.Delete(HttpContext.Current.Server.MapPath("~/Temp/" & strNewFileName))
                        End If
                        Filepath = HttpContext.Current.Server.MapPath("~/Temp/")
                        docPath = Filepath + file.FileName
                        newFilePath = Filepath + strNewFileName
                        file.SaveAs(docPath)
                        My.Computer.FileSystem.RenameFile(Filepath & file.FileName, strNewFileName)
                    Next
                    GC.Collect()
                    GC.WaitForPendingFinalizers()
  
            
                    Dim Table = ImportFromExcel(newFilePath, MBType)
                    If Table.Rows.Count > 0 Then
                        Dim lstExcelColumns As New List(Of ExcelColumns)
                        Dim lstRequiredColumns As New List(Of String)
                        Dim status As Integer = 0
                        If (MBType = 1) Then
                            lstRequiredColumns.Add("Description")
                            lstRequiredColumns.Add("ChainageFrom")
                            lstRequiredColumns.Add("ChainageTo")
                            lstRequiredColumns.Add("Side")
                            lstRequiredColumns.Add("Number")
                            lstRequiredColumns.Add("Length")
                            lstRequiredColumns.Add("Breadth")
                            lstRequiredColumns.Add("Depth")
                            lstRequiredColumns.Add("Qty")
                            lstRequiredColumns.Add("Remark")
                            lstRequiredColumns.Add("TaskNo")
                        ElseIf (MBType = 2) Then
                            lstRequiredColumns.Add("Description")
                            lstRequiredColumns.Add("Material_Name")
                            lstRequiredColumns.Add("Dia_of_Rod")
                            lstRequiredColumns.Add("Wt_Per_Mtr")
                            lstRequiredColumns.Add("No_of_Speimen")
                            lstRequiredColumns.Add("No_of_Rod")
                            lstRequiredColumns.Add("Len_of_Rod_in_Mtr")
                            lstRequiredColumns.Add("Total_Length_in_Meter")
                            lstRequiredColumns.Add("Total_Wt_in_kg")
                            lstRequiredColumns.Add("TaskNo")
                        End If
                       
                    
                        Dim lstWorkSheetColumns As New List(Of String)
                        For iCol = 0 To Table.Columns.Count - 1
                            Dim excelCol As New ExcelColumns()
                            If iCol < 11 Then
                                lstWorkSheetColumns.Add(Convert.ToString(Table.Columns(iCol).ColumnName))
                            Else
                                Exit For
                            End If
                        Next
                        
                
                        Try
                            Dim chkmatchcolumns As Boolean = True
                            
                            For iReqCol = 0 To lstRequiredColumns.Count - 1
                                If lstRequiredColumns(iReqCol) = lstWorkSheetColumns(iReqCol) Then
                                    lstExcelColumns.Add(New ExcelColumns() With { _
                                                        .Required_Column = lstRequiredColumns(iReqCol),
                                                        .WorkSheet_Column = lstWorkSheetColumns(iReqCol),
                                                        .tblFlag = "<i class='fa fa-check text-green'></i>",
                                                        .Status = 0})
                                Else
                                    chkmatchcolumns = False
                                    lstExcelColumns.Add(New ExcelColumns() With { _
                                                        .Required_Column = lstRequiredColumns(iReqCol),
                                                        .WorkSheet_Column = lstWorkSheetColumns(iReqCol),
                                                        .tblFlag = "<i class='fa fa-window-close-o text-red'></i>",
                                                        .Status = 1})
                                End If
                            Next

                            excelInfo.lstExcelColumns = lstExcelColumns
                            excelInfo.File_Path = newFilePath
                            If (chkmatchcolumns) Then
                                excelInfo.IsSucess = True
                                excelInfo.ClientMessage = "Success"
                            Else
                                excelInfo.IsSucess = False
                                excelInfo.ClientMessage = "Please Select Valid .xlsx File"
                            End If
                            
                        Catch ex As Exception
                            excelInfo.lstExcelColumns = lstExcelColumns
                            excelInfo.File_Path = newFilePath
                            excelInfo.IsSucess = False
                            excelInfo.ClientMessage = "Please Select Valid .xlsx File"
                        End Try
                    End If
                    
                    
                   
                ElseIf strStep = "2" Then
                    Dim strFilePath As String = context.Request.Form("FilePath")
                    Dim Task_no As Integer = context.Request.Form("Task_no")
                    Dim MBType2 As Integer = context.Request.Form("MBType")
                    
                    'ext = Path.GetExtension(strFilePath)
                    GC.Collect()
                    GC.WaitForPendingFinalizers()
                   
                    Dim Table = ImportFromExcel(strFilePath, MBType2)
    
                    If (MBType2 = 1) Then
                        Dim Description As String = ""
                        Dim ChainageFrom As String = ""
                        Dim ChainageTo As String = ""
                        Dim Side As String = ""
                        Dim Number As Decimal = 0
                        Dim Length As Decimal = 0
                        Dim Breadth As Decimal = 0
                        Dim Depth As Decimal = 0
                        Dim Qty As Decimal = 0
                        Dim Remark As String = ""
                        Dim TaskNo As Integer = 0
                    
                        Dim lstExcelData As New List(Of MeasurmentList)
                        Dim lstExcelList As New List(Of List(Of MeasurmentList))
                        Dim i As Integer = 0
                        For i = 0 To Table.Rows.Count - 1
                            lstExcelData = New List(Of MeasurmentList)
                            If Convert.ToString(Table.Rows(i)("Description")) = "" Then
                                Exit For
                            End If
                            Try
                                Number = Convert.ToDecimal(Table.Rows(i)("Number"))
                            Catch ex As Exception
                                Number = 0
                            End Try
                            
                            Try
                                Dim tn As Decimal = Convert.ToDecimal(Table.Rows(i)("TaskNo").replace(",", ""))
                                TaskNo = Convert.ToInt64(tn)
                            Catch ex As Exception
                                TaskNo = 0
                            End Try
                            
                            Try
                                Length = Convert.ToDecimal(Table.Rows(i)("Length"))
                            Catch ex As Exception
                                Length = 0
                            End Try
                            Try
                                Breadth = Convert.ToDecimal(Table.Rows(i)("Breadth"))
                            Catch ex As Exception
                                Breadth = 0
                            End Try
                            Try
                                Depth = Convert.ToDecimal(Table.Rows(i)("Depth"))
                            Catch ex As Exception
                                Depth = 0
                            End Try
                            Try
                                Qty = Convert.ToDecimal(Table.Rows(i)("Qty"))
                            Catch ex As Exception
                                Qty = 0
                            End Try
                                               
                            lstExcelData.Add(New MeasurmentList() With { _
                                             .Description = Convert.ToString(Table.Rows(i)("Description")),
                                             .ChainageFrom = Convert.ToString(Table.Rows(i)("ChainageFrom")),
                                             .ChainageTo = Convert.ToString(Table.Rows(i)("ChainageTo")),
                                             .Side = Convert.ToString(Table.Rows(i)("Side")),
                                             .Number = Number,
                                             .Length = Length,
                                             .Breadth = Breadth,
                                             .Depth = Depth,
                                             .Qty = Qty,
                                             .Remark = Convert.ToString(Table.Rows(i)("Remark")),
                                             .TaskNo = TaskNo
                                                })
                            lstExcelList.Add(lstExcelData)
                        Next
                        
                        excelInfo.Task_no = Task_no
                        excelInfo.UserName = context.Session("username")
                        excelInfo.CompName = context.Session("CompName")
                        excelInfo.TaskMB_Id = 0
                        excelInfo.lstExcelData = lstExcelList
                        excelInfo.IsSucess = True
                        excelInfo.ClientMessage = "Success"
                        
                    ElseIf (MBType2 = 2) Then
                        
                        Dim Description As String = ""
                        Dim Material_Name As String = ""
                        Dim Dia_of_Rod As Decimal = 0
                        Dim Wt_Per_Mtr As Decimal = 0
                        Dim No_of_Speimen As Decimal = 0
                        Dim No_of_Rod As Decimal = 0
                        Dim Len_of_Rod_in_Mtr As Decimal = 0
                        Dim Total_Length_in_Meter As Decimal = 0
                        Dim Total_Wt_in_kg As Decimal = 0
                        Dim TaskNo As Integer = 0
                    
                        Dim lstExcelData As New List(Of MeasurmentList_Steel)
                        Dim lstExcelList As New List(Of List(Of MeasurmentList_Steel))
                        Dim i As Integer = 0
                        For i = 0 To Table.Rows.Count - 1
                            lstExcelData = New List(Of MeasurmentList_Steel)
                            If Convert.ToString(Table.Rows(i)("Description")) = "" Then
                                Exit For
                            End If
                            If Convert.ToString(Table.Rows(i)("Material_Name")) = "" Then
                                Exit For
                            End If
                            
                            Try
                                Dim tn As Decimal = Convert.ToDecimal(Table.Rows(i)("TaskNo").replace(",", ""))
                                TaskNo = Convert.ToInt64(tn)
                            Catch ex As Exception
                                TaskNo = 0
                            End Try
                            
                            Try
                                Dia_of_Rod = Convert.ToDecimal(Table.Rows(i)("Dia_of_Rod"))
                            Catch ex As Exception
                                Dia_of_Rod = 0
                            End Try
                            Try
                                Wt_Per_Mtr = Convert.ToDecimal(Table.Rows(i)("Wt_Per_Mtr"))
                            Catch ex As Exception
                                Wt_Per_Mtr = 0
                            End Try
                            Try
                                No_of_Speimen = Convert.ToDecimal(Table.Rows(i)("No_of_Speimen"))
                            Catch ex As Exception
                                No_of_Speimen = 0
                            End Try
                            Try
                                No_of_Rod = Convert.ToDecimal(Table.Rows(i)("No_of_Rod"))
                            Catch ex As Exception
                                No_of_Rod = 0
                            End Try
                            Try
                                Len_of_Rod_in_Mtr = Convert.ToDecimal(Table.Rows(i)("Len_of_Rod_in_Mtr"))
                            Catch ex As Exception
                                Len_of_Rod_in_Mtr = 0
                            End Try
                            Try
                                Total_Length_in_Meter = Convert.ToDecimal(Table.Rows(i)("Total_Length_in_Meter"))
                            Catch ex As Exception
                                Total_Length_in_Meter = 0
                            End Try
                            Try
                                Total_Wt_in_kg = Convert.ToDecimal(Table.Rows(i)("Total_Wt_in_kg"))
                            Catch ex As Exception
                                Total_Wt_in_kg = 0
                            End Try
                                               
                            lstExcelData.Add(New MeasurmentList_Steel() With { _
                                             .Description = Convert.ToString(Table.Rows(i)("Description")),
                                             .Material_Name = Convert.ToString(Table.Rows(i)("Material_Name")),
                                             .Dia_of_Rod = Dia_of_Rod,
                                             .Wt_Per_Mtr = Wt_Per_Mtr,
                                             .No_of_Speimen = No_of_Speimen,
                                             .No_of_Rod = No_of_Rod,
                                             .Len_of_Rod_in_Mtr = Len_of_Rod_in_Mtr,
                                             .Total_Length_in_Meter = Total_Length_in_Meter,
                                             .Total_Wt_in_kg = Total_Wt_in_kg,
                                               .TaskNo = TaskNo
                                                })
                            
                           
                            lstExcelList.Add(lstExcelData)
                        Next
                        
                        excelInfo.Task_no = Task_no
                        excelInfo.UserName = context.Session("username")
                        excelInfo.CompName = context.Session("CompName")
                        excelInfo.TaskMB_Id = 0
                        excelInfo.lstExcelData_steel = lstExcelList
                        excelInfo.IsSucess = True
                        excelInfo.ClientMessage = "Success"
                        
                    End If
                    
                  
                ElseIf strStep = "3" Then
                    Dim strFilePath As String = context.Request.Form("FilePath")
                    If System.IO.File.Exists(strFilePath) Then
                        System.IO.File.Delete(strFilePath)
                    End If
                End If
                
                cmdExcel.Dispose()
                connExcel.Close()
                connExcel.Dispose()
            End If
            
        Catch ex As Exception
            excelInfo.IsSucess = False
            excelInfo.ClientMessage = ex.Message
        End Try
        context.Response.Write(JsonConvert.SerializeObject(excelInfo))
    End Sub
 
    Public ReadOnly Property IsReusable() As Boolean Implements IHttpHandler.IsReusable
        Get
            Return False
        End Get
    End Property

End Class