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

Imports System
Imports OfficeOpenXml
Imports System.IO
Imports OfficeOpenXml.Style
Imports System.Net
Imports System.Web
Imports System.Web.Script.Serialization
Imports System.Data
Imports BusinessLogicLayer

Public Class ProjectWiseRate : Implements IHttpHandler

    Public Sub ProcessRequest(ByVal context As HttpContext) Implements IHttpHandler.ProcessRequest
        Dim json As String = ""
        Dim LibraryNo As Integer = 0
        Dim TreeId As String = ""
        Dim ProjectId As Integer = 0
        Dim GUID As Integer = 0
        Dim Type As Integer = 0
        Dim UserName As String = ""

        If context.Request.Files.Count > 0 Then
            LibraryNo = Convert.ToInt32(context.Request.Form("LibraryNo"))
            TreeId = context.Request.Form("TreeId")
            ProjectId = Convert.ToInt32(context.Request.Form("ProjectId"))
            GUID = Convert.ToInt32(context.Request.Form("GUID"))
            Type = Convert.ToInt32(context.Request.Form("Type"))
            UserName = context.Request.Form("UserName")

            Dim files As HttpFileCollection = context.Request.Files
            Dim ext As String = ""
            For i As Integer = 0 To files.Count - 1
                Dim file As HttpPostedFile = files(i)
                ext = Path.GetExtension(file.FileName)
                If ext <> ".xlsx" Then

                    json = New JavaScriptSerializer().Serialize(New With {
                     .IsSucess = False,
                     .ClientMessage = "Please select .xlsx file only"
                 })
                    context.Response.StatusCode = CInt(HttpStatusCode.OK)
                    context.Response.ContentType = "text/json"
                    context.Response.Write(json)
                    context.Response.End()
                End If

                If Not Directory.Exists(HttpContext.Current.Server.MapPath("~/Temp/")) Then
                    Directory.CreateDirectory(HttpContext.Current.Server.MapPath("~/Temp/"))
                End If
                Dim strfilename As String = HttpContext.Current.Server.MapPath("~/Temp/") & files(i).FileName
                files(i).SaveAs(strfilename)

                Dim ProjectName As String = ""
                Dim dtexcel As DataTable
                Try
                    dtexcel = ImportFromExcel(strfilename, ProjectName)
                    If Not String.IsNullOrEmpty(ProjectName) Then
                        Dim str1 As String() = ProjectName.Split(New Char() {"["c})
                        Dim str2 As String() = str1(1).Split(New Char() {"_"c})

                        If (Convert.ToInt32(str2(0)) <> ProjectId) Then
                            json = New JavaScriptSerializer().Serialize(New With {
                                  .IsSucess = False,
                                  .ClientMessage = "Incorrect Excel Columns or Column Sequence"
                             })
                            context.Response.StatusCode = CInt(HttpStatusCode.OK)
                            context.Response.ContentType = "text/json"
                            context.Response.Write(json)
                            context.Response.End()
                        End If

                    Else
                        json = New JavaScriptSerializer().Serialize(New With {
                                                                  .IsSucess = False,
                                                                  .ClientMessage = "Incorrect Excel Columns or Column Sequence"
                                                             })
                        context.Response.StatusCode = CInt(HttpStatusCode.OK)
                        context.Response.ContentType = "text/json"
                        context.Response.Write(json)
                        context.Response.End()
                    End If
                Catch ex As Exception
                    json = New JavaScriptSerializer().Serialize(New With {
                                             .IsSucess = False,
                                             .ClientMessage = "Incorrect Excel Columns or Column Sequence"
                                        })
                    context.Response.StatusCode = CInt(HttpStatusCode.OK)
                    context.Response.ContentType = "text/json"
                    context.Response.Write(json)
                    context.Response.End()
                End Try


                If (dtexcel.Rows.Count > 0) Then
                    Dim aa As String = ""

                    Dim chkexceleror As Boolean = True
                    Dim Type1_columnNames As List(Of String) = New List(Of String)()
                    Type1_columnNames.Add("Mat_Lab")
                    Type1_columnNames.Add("Rate")
                    Type1_columnNames.Add("Tax Scheme")
                    Type1_columnNames.Add("Tax")
                    Type1_columnNames.Add("Library Rate")
                    If (dtexcel.Columns.Count = 5) Then
                        Dim chkexcelcoleror As Boolean = True
                        Dim ColCount2 As Integer = 0
                        For Each col In dtexcel.Columns
                            If (col.ColumnName.ToString() <> Type1_columnNames(ColCount2).ToString()) Then
                                chkexcelcoleror = False
                            End If
                            ColCount2 = ColCount2 + 1
                        Next

                        If (chkexceleror = False) Then
                            json = New JavaScriptSerializer().Serialize(New With {
                                          .IsSucess = False,
                                          .ClientMessage = "Incorrect Excel Columns or Column Sequence"
                                     })
                            context.Response.StatusCode = CInt(HttpStatusCode.OK)
                            context.Response.ContentType = "text/json"
                            context.Response.Write(json)
                            context.Response.End()
                        Else
                            Try
                                Dim objpurchasecontrolbl As New PurchaseControlBL
                                objpurchasecontrolbl.ProjectWiseImportExportData(dtexcel, GUID, ProjectId, UserName, "", Type)

                                json = New JavaScriptSerializer().Serialize(New With {
                                      .IsSucess = True,
                                      .ClientMessage = "File Imported Successfully !!"
                                 })

                            Catch ex As Exception
                                json = New JavaScriptSerializer().Serialize(New With {
                                          .IsSucess = False,
                                          .ClientMessage = ex.Message.ToString()
                                     })
                                context.Response.StatusCode = CInt(HttpStatusCode.OK)
                                context.Response.ContentType = "text/json"
                                context.Response.Write(json)
                                context.Response.End()
                            End Try
                        End If
                    Else
                        json = New JavaScriptSerializer().Serialize(New With {
                                          .IsSucess = False,
                                          .ClientMessage = "Incorrect Excel Columns or Column Sequence"
                                     })
                        context.Response.StatusCode = CInt(HttpStatusCode.OK)
                        context.Response.ContentType = "text/json"
                        context.Response.Write(json)
                        context.Response.End()
                    End If
                Else

                    json = New JavaScriptSerializer().Serialize(New With {
                          .IsSucess = False,
                          .ClientMessage = "No Records in Selected Excel"
                     })
                    context.Response.StatusCode = CInt(HttpStatusCode.OK)
                    context.Response.ContentType = "text/json"
                    context.Response.Write(json)
                    context.Response.End()

                End If

            Next
        Else

            json = New JavaScriptSerializer().Serialize(New With {
                    .IsSucess = False,
                    .ClientMessage = "Please Select Valid .xlsx File"
                })
            context.Response.StatusCode = CInt(HttpStatusCode.OK)
            context.Response.ContentType = "text/json"
            context.Response.Write(json)
            context.Response.End()
        End If

        context.Response.StatusCode = CInt(HttpStatusCode.OK)
        context.Response.ContentType = "text/json"
        context.Response.Write(json)
        context.Response.End()
    End Sub

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

    Public Function ImportFromExcel(ByVal strFilePath As String, ByRef ProjectName 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

            Dim projrowrow = worksheet.Cells(2, 1, 2, 5)
            For Each projnmcell In projrowrow
                ProjectName = projnmcell.Text.Trim()
            Next


            For Each cell In worksheet.Cells(3, 1, 3, 5)
                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)
                If (columnName.Trim().ToLower() = "material" Or columnName.Trim().ToLower() = "labour") Then
                    Table.Columns.Add("Mat_Lab")
                Else
                    Table.Columns.Add(columnName)
                End If

                currentColumn += 1
            Next

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

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

                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

End Class