'****************************************************************************************************************
'* Requirement        : IMPORT - LOG CHANGED/DELETED CASES                                                      *
'* Traceability - #   : CMS01_RQ509 - CMS01_RQ507                                                               *
'* --------------------------------------------------------------------------------------------                 *
'* Id Application modified by:   Date(MM/dd/yyyy)   Description													*
'* -- ------------------------   ---------------    -------------------------------------------	    			*
'* 01 Vanessa Ayala  - PACNET    03/28/2006                                                                     *
'****************************************************************************************************************
Imports System.IO
Imports System.Data.OleDb
Imports CAMS_ADMIN
Imports CAMS_BL
Imports System.Diagnostics
Imports System.GC
Imports Microsoft.Office.Core
Imports System.Runtime.InteropServices.Marshal
Imports System.Globalization
Imports System.Text
'Imports Microsoft.Office.Interop

Public Class frmImport

    Inherits CAMSBasePage
    Protected WithEvents txtLog As System.Web.UI.WebControls.TextBox
    Protected WithEvents txtLogImport As System.Web.UI.WebControls.TextBox
    Dim InvalidCell As String

    'Added By Raul Callupe D.
    Private myDTFI As New DateTimeFormatInfo
    Private strDateFormat As String = "dd-MMM-yyyy" 'In temporary excel file
    Private strDBDateFormat As String = "MM/dd/yyyy" 'In database
    'End Added

#Region " Web Form Designer Generated Code "

    'This call is required by the Web Form Designer.
    <System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()

    End Sub

    'NOTE: The following placeholder declaration is required by the Web Form Designer.
    'Do not delete or move it.
    Private designerPlaceholderDeclaration As System.Object
    Protected WithEvents btnImport As System.Web.UI.WebControls.Button
    Protected WithEvents btnCancel As System.Web.UI.WebControls.Button
    Protected WithEvents fileField As System.Web.UI.HtmlControls.HtmlInputFile
    Protected WithEvents lblMsgImport As System.Web.UI.WebControls.Label
    Protected WithEvents mnuCams As Menu

    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


    Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        myDTFI.ShortDatePattern = strDateFormat

        'Access Validation to page
        If Not Me.IsPostBack Then
            Dim oEC_User As EC_User = Me.GetUserFromSession

            If Not oEC_User Is Nothing Then
                If Me.HasAccessTo(Me.GetControlID("frmImport.aspx"), oEC_User.AccessGroup) = False Then
                    Me.RedirectToMsgPage(Constants.CONST_ERROR_ACCESSDENIED_TOPAGE)
                End If
            End If

        End If
        'End Access Validation
        'Menu
        mnuCams.CurrentGroup = Constants.MENU_SPREADSHEET_ID
        mnuCams.CurrentItem = Constants.MENU_SPREADSHEET_IMPORT_ID
        'End Menu

        Me.lblMsgImport.Text = String.Empty
        Me.txtLog.Text = String.Empty
        Me.txtLog.Visible = False

        'Added by Raul Callupe
        If Me.IsPostBack = False Then
            Dim stbOutput As New StringBuilder
            stbOutput.Append("<script language='Javascript'>")
            stbOutput.Append("var pag = window.parent.frames(0);")
            stbOutput.Append("pag.document.all.lblPath.innerHTML='Spreadsheet/Import'")
            stbOutput.Append("</script>")
            Response.Write(stbOutput.ToString)
        End If
        'End Added
    End Sub


#Region "Upload file to webserver"

    Private Sub btnImport_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnImport.Click
        Me.txtLog.Visible = False
        txtLog.Text = String.Empty
        Try
            If Not fileField.PostedFile Is Nothing And fileField.PostedFile.ContentLength > 0 Then
                Dim fn As String = System.IO.Path.GetFileName(fileField.PostedFile.FileName)

                If Right(fn, 4) = ".xls" Then
                    Dim strLocation As String = Server.MapPath(ConfigurationSettings.AppSettings("FolderName")) & "\" & Me.Session.SessionID & Date.Now.Ticks.ToString & ".xls"
                    fileField.PostedFile.SaveAs(strLocation)
                    ValidateFile(strLocation, fn)
                Else
                    Me.lblMsgImport.Text = "Please select a valid Excel file."
                End If
            Else
                Me.lblMsgImport.Text = "Please select a file to import."
            End If
        Catch Exc As Exception
            'Me.lblMsgImport.Text = "Error: " & Exc.Message
        End Try
    End Sub
#End Region


#Region "Validate Excel file"

    Private Sub ValidateFile(ByVal sLocation As String, ByVal sFileName As String)
        'Added By Raul Callupe
        'Dim thisThread As System.Threading.Thread = System.Threading.Thread.CurrentThread
        'Dim originalCulture As System.Globalization.CultureInfo = thisThread.CurrentCulture

        Try
            'Set the Language Option to "en-US"

            'Modified By Raul Callupe
            'System.Threading.Thread.CurrentThread.CurrentCulture = System.Globalization.CultureInfo.CreateSpecificCulture("en-US")
            'End Modified

            'Added
            'thisThread.CurrentCulture = New System.Globalization.CultureInfo("en-US")
            'End Added

            'Creates and Open an instance of excel to verify if file is standardized 
            Dim oXL As Excel.Application
            Dim oWB As Excel.Workbook
            Dim oSheet As Excel.Worksheet
            Dim Range As Excel.Range

            'oXL = CreateObject("Excel.Application")
            oXL = New Excel.Application
            oXL.Visible = False
            oWB = oXL.Workbooks.Open(sLocation)
            'oSheet = oWB.ActiveSheet
            oSheet = CType(oWB.Worksheets.Item(1), Excel.Worksheet)

            If oSheet.Range("A1:AG1").Text Is String.Empty Then
                oSheet.Range("A1").EntireRow.Delete()
            End If

            'Validate if spreadsheet is standarized
            If Left(Trim(oSheet.Range("A1").Text), 3).ToUpper <> "CAS" Or Left(Trim(oSheet.Range("B1").Text), 3).ToUpper <> "SUR" Or Left(Trim(oSheet.Range("C1").Text), 3).ToUpper <> "NAM" Or Left(Trim(oSheet.Range("D1").Text), 3).ToUpper <> "TIT" Or Left(Trim(oSheet.Range("E1").Text), 3).ToUpper <> "ADD" Or Left(Trim(oSheet.Range("F1").Text), 1).ToUpper <> "D" Or Left(Trim(oSheet.Range("G1").Text), 3).ToUpper <> "TEL" Or Left(Trim(oSheet.Range("H1").Text), 3).ToUpper <> "APP" Or Left(Trim(oSheet.Range("I1").Text), 2).ToUpper <> "HA" Or _
                Left(Trim(oSheet.Range("J1").Text), 3).ToUpper <> "SCH" Or Left(Trim(oSheet.Range("K1").Text), 3).ToUpper <> "CLI" Or Left(Trim(oSheet.Range("L1").Text), 3).ToUpper <> "VIS" Or Left(Trim(oSheet.Range("M1").Text), 3).ToUpper <> "AME" Or Left(Trim(oSheet.Range("N1").Text), 2).ToUpper <> "GO" Or Right(Trim(oSheet.Range("O1").Text), 4).ToUpper <> "TYPE" Or Left(Trim(oSheet.Range("P1").Text), 3).ToUpper <> "REF" Or Right(Trim(oSheet.Range("Q1").Text), 4).ToUpper <> "TYPE" Or Left(Trim(oSheet.Range("R1").Text), 1).ToUpper <> "S" Or _
                Left(Trim(oSheet.Range("S1").Text), 4).ToUpper <> "APPL" Or Left(Trim(oSheet.Range("T1").Text), 4).ToUpper <> "APPR" Or Left(Trim(oSheet.Range("U1").Text), 3).ToUpper <> "REV" Or Left(Trim(oSheet.Range("V1").Text), 4).ToUpper <> "CONT" Or Left(Trim(oSheet.Range("W1").Text), 2).ToUpper <> "ON" Or Right(Trim(oSheet.Range("X1").Text), 4).ToUpper <> "SITE" Or Left(Trim(oSheet.Range("Y1").Text), 4).ToUpper <> "COMP" Or Right(Trim(oSheet.Range("Z1").Text), 4).ToUpper <> "COST" Or Left(Trim(oSheet.Range("AA1").Text), 3).ToUpper <> "GRA" Or _
                Right(Trim(oSheet.Range("AB1").Text), 4).ToUpper <> "COST" Or Left(Trim(oSheet.Range("AC1").Text), 1).ToUpper <> "S" Or Left(Trim(oSheet.Range("AD1").Text), 3).ToUpper <> "STA" Or Left(Trim(oSheet.Range("AE1").Text), 4).ToUpper <> "CANC" Or Left(Trim(oSheet.Range("AF1").Text), 3).ToUpper <> "REA" Or Left(Trim(oSheet.Range("AG1").Text), 4).ToUpper <> "COMM" Then

                Me.lblMsgImport.Text = "File not standarized. Please verify the number of columns of the spreadsheet."

                If Not oWB Is Nothing Then
                    DeleteReferences(oSheet)
                    oXL.Workbooks(1).Close(False)
                    DeleteReferences(oWB)
                    oXL.Quit()
                    DeleteReferences(oXL)
                    System.GC.Collect()
                End If
            Else
                With oSheet
                    .Cells(1, 30) = "STATUS"
                    .Range("A:E, G:I, N:R, V:V, AD:AD, AF:AG").NumberFormat = "@"
                    'Modified by raul
                    '.Range("F:F, J:M, S:U, W:Y, AE:AE").NumberFormat = "mm/dd/yyyy"
                    'End modified
                    .Range("Z:AC").NumberFormat = "0.00"
                    Range = .Range("AD1")
                    Range.Sort(Range, Orientation:=Excel.XlSortOrientation.xlSortColumns, Header:=Excel.XlYesNoGuess.xlYes)
                    .Name = "Cases"
                    oWB.Save()
                End With

                'Evaluate Each row of the spreadsheet
                Dim msFile As System.Text.StringBuilder = New System.Text.StringBuilder
                Dim nRows As Integer
                Dim nValidRows, nInvalidRows As Integer
                Dim cStatus As Excel.Range
                Dim j As Int32
                Dim sCasesXML As String
                msFile.Append("<?xml version=""1.0"" encoding=""iso-8859-1"" ?>" + vbCrLf)
                msFile.Append("<ROOT>" + vbCrLf)

                Dim m_date As DateTime
                Dim m_strdate As String = ""

                For j = 0 To (oSheet.Rows.Count - 1)

                    With oSheet
                        cStatus = .Range("AD" & j + 1) 'Status column
                        m_strdate = ""

                        If cStatus.Text <> String.Empty Then
                            If Not j + 1 = 1 Then

                                If ValidateLength(.Range("A1").Text, .Range("A" & j + 1).Text, 6) And ValidateFormatDate(.Range("F1").Text, .Range("F" & j + 1).Text) And ValidateFormatDate(.Range("J1").Text, .Range("J" & j + 1).Text) And ValidateLength(.Range("G1").Text, .Range("G" & j + 1).Text, 12) And _
                                    ValidateLength(.Range("H1").Text, .Range("H" & j + 1).Text, 9) And ValidateLength(.Range("I1").Text, .Range("I" & j + 1).Text, 2) And ValidateFormatDate(.Range("K1").Text, .Range("K" & j + 1).Text) And ValidateFormatDate(.Range("L1").Text, .Range("L" & j + 1).Text) And _
                                    ValidateFormatDate(.Range("M1").Text, .Range("M" & j + 1).Text) And ValidateLength(.Range("N1").Text, .Range("N" & j + 1).Text, 2) And ValidateFormatDate(.Range("S1").Text, .Range("S" & j + 1).Text) And ValidateLength(.Range("O1").Text, .Range("O" & j + 1).Text, 3) And ValidateLength(.Range("P1").Text, .Range("P" & j + 1).Text, 10) And _
                                    ValidateLength(.Range("Q1").Text, .Range("Q" & j + 1).Text, 3) And ValidateFormatDate(.Range("T1").Text, .Range("T" & j + 1).Text) And ValidateFormatDate(.Range("U1").Text, .Range("U" & j + 1).Text) And _
                                    ValidateFormatDate(.Range("W1").Text, .Range("W" & j + 1).Text) And ValidateFormatDate(.Range("X1").Text, .Range("X" & j + 1).Text) And _
                                    ValidateFormatDate(.Range("Y1").Text, .Range("Y" & j + 1).Text) And ValidateFormatDate(.Range("AE1").Text, .Range("AE" & j + 1).Text) And _
                                    ValidateFormatNumber(.Range("Z1").Text, .Range("Z" & j + 1).Text) And ValidateFormatNumber(.Range("AA1").Text, .Range("AA" & j + 1).Text) And _
                                    ValidateFormatNumber(.Range("AB1").Text, .Range("AB" & j + 1).Text) And ValidateFormatNumber(.Range("AC1").Text, .Range("AC" & j + 1).Text) And ValidateLength(.Range("AD1").Text, .Range("AD" & j + 1).Text, 2) And ValidateLength(.Range("AF1").Text, .Range("AF" & j + 1).Text, 2) Then

                                    msFile.Append("<TABLA")
                                    msFile.Append(" CASEID=""" + TestString(.Range("A" & j + 1).Text) + """")
                                    msFile.Append(" SURNAME=""" + TestString(.Range("B" & j + 1).Text) + """")
                                    msFile.Append(" NAME=""" + TestString(.Range("C" & j + 1).Text) + """")
                                    msFile.Append(" TITLEDESC=""" + TestString(.Range("D" & j + 1).Text) + """")
                                    msFile.Append(" ADDRESS=""" + TestString(.Range("E" & j + 1).Text) + """")

                                    If TestString(.Range("F" & j + 1).Text).Trim.Equals(String.Empty) Then
                                        m_strdate = ""
                                    Else
                                        m_date = DateTime.Parse(TestString(.Range("F" & j + 1).Text), myDTFI)
                                        m_strdate = m_date.ToString(strDBDateFormat)
                                    End If
                                    msFile.Append(" DOB=""" + m_strdate + """")
                                    'msFile.Append(" DOB=""" + TestString(.Range("F" & j + 1).Text) + """") 'Datetime

                                    msFile.Append(" TEL=""" + TestString(.Range("G" & j + 1).Text) + """")
                                    msFile.Append(" APPLIDN=""" + TestString(.Range("H" & j + 1).Text) + """")
                                    msFile.Append(" HA=""" + TestString(.Range("I" & j + 1).Text).Trim + """")

                                    If TestString(.Range("J" & j + 1).Text).Trim.Equals(String.Empty) Then
                                        m_strdate = ""
                                    Else
                                        m_date = DateTime.Parse(TestString(.Range("J" & j + 1).Text), myDTFI)
                                        m_strdate = m_date.ToString(strDBDateFormat)
                                    End If
                                    msFile.Append(" SCHEDULE=""" + m_strdate + """")  'Datetime
                                    'msFile.Append(" SCHEDULE=""" + TestString(.Range("J" & j + 1).Text) + """") 'Datetime

                                    If TestString(.Range("K" & j + 1).Text).Trim.Equals(String.Empty) Then
                                        m_strdate = ""
                                    Else
                                        m_date = DateTime.Parse(TestString(.Range("K" & j + 1).Text), myDTFI)
                                        m_strdate = m_date.ToString(strDBDateFormat)
                                    End If
                                    msFile.Append(" CLIENT=""" + m_strdate + """")  'Datetime
                                    'msFile.Append(" CLIENT=""" + TestString(.Range("K" & j + 1).Text) + """") 'Datetime

                                    If TestString(.Range("L" & j + 1).Text).Trim.Equals(String.Empty) Then
                                        m_strdate = ""
                                    Else
                                        m_date = DateTime.Parse(TestString(.Range("L" & j + 1).Text), myDTFI)
                                        m_strdate = m_date.ToString(strDBDateFormat)
                                    End If
                                    msFile.Append(" VISIT=""" + m_strdate + """")  'Datetime
                                    'msFile.Append(" VISIT=""" + TestString(.Range("L" & j + 1).Text) + """") 'Datetime

                                    If TestString(.Range("M" & j + 1).Text).Trim.Equals(String.Empty) Then
                                        m_strdate = ""
                                    Else
                                        m_date = DateTime.Parse(TestString(.Range("M" & j + 1).Text), myDTFI)
                                        m_strdate = m_date.ToString(strDBDateFormat)
                                    End If
                                    msFile.Append(" AMENDED=""" + m_strdate + """")  'Datetime
                                    'msFile.Append(" AMENDED=""" + TestString(.Range("M" & j + 1).Text) + """") 'Datetime

                                    msFile.Append(" GO=""" + TestString(.Range("N" & j + 1).Text) + """")
                                    msFile.Append(" GTYPE=""" + TestString(.Range("O" & j + 1).Text) + """")
                                    msFile.Append(" REFNUM=""" + TestString(.Range("P" & j + 1).Text) + """")
                                    msFile.Append(" SGTYPE=""" + TestString(.Range("Q" & j + 1).Text) + """")
                                    msFile.Append(" SREFNUM=""" + TestString(.Range("R" & j + 1).Text) + """")

                                    If TestString(.Range("S" & j + 1).Text).Trim.Equals(String.Empty) Then
                                        m_strdate = ""
                                    Else
                                        m_date = DateTime.Parse(TestString(.Range("S" & j + 1).Text), myDTFI)
                                        m_strdate = m_date.ToString(strDBDateFormat)
                                    End If
                                    msFile.Append(" APPLICATION=""" + m_strdate + """")  'Datetime
                                    'msFile.Append(" APPLICATION=""" + TestString(.Range("S" & j + 1).Text) + """") 'Datetime

                                    If TestString(.Range("T" & j + 1).Text).Trim.Equals(String.Empty) Then
                                        m_strdate = ""
                                    Else
                                        m_date = DateTime.Parse(TestString(.Range("T" & j + 1).Text), myDTFI)
                                        m_strdate = m_date.ToString(strDBDateFormat)
                                    End If
                                    msFile.Append(" APPROVED=""" + m_strdate + """")  'Datetime
                                    'msFile.Append(" APPROVED=""" + TestString(.Range("T" & j + 1).Text) + """") 'Datetime

                                    If TestString(.Range("U" & j + 1).Text).Trim.Equals(String.Empty) Then
                                        m_strdate = ""
                                    Else
                                        m_date = DateTime.Parse(TestString(.Range("U" & j + 1).Text), myDTFI)
                                        m_strdate = m_date.ToString(strDBDateFormat)
                                    End If
                                    msFile.Append(" REVISED=""" + m_strdate + """")  'Datetime
                                    'msFile.Append(" REVISED=""" + TestString(.Range("U" & j + 1).Text) + """") 'Datetime

                                    msFile.Append(" CONTRACTORNAME=""" + TestString(.Range("V" & j + 1).Text) + """")

                                    If TestString(.Range("W" & j + 1).Text).Trim.Equals(String.Empty) Then
                                        m_strdate = ""
                                    Else
                                        m_date = DateTime.Parse(TestString(.Range("W" & j + 1).Text), myDTFI)
                                        m_strdate = m_date.ToString(strDBDateFormat)
                                    End If
                                    msFile.Append(" ONSITE=""" + m_strdate + """")  'Datetime
                                    'msFile.Append(" ONSITE=""" + TestString(.Range("W" & j + 1).Text) + """") 'Datetime

                                    If TestString(.Range("X" & j + 1).Text).Trim.Equals(String.Empty) Then
                                        m_strdate = ""
                                    Else
                                        m_date = DateTime.Parse(TestString(.Range("X" & j + 1).Text), myDTFI)
                                        m_strdate = m_date.ToString(strDBDateFormat)
                                    End If
                                    msFile.Append(" COMS=""" + m_strdate + """")  'Datetime
                                    'msFile.Append(" COMS=""" + TestString(.Range("X" & j + 1).Text) + """") 'Datetime

                                    If TestString(.Range("Y" & j + 1).Text).Trim.Equals(String.Empty) Then
                                        m_strdate = ""
                                    Else
                                        m_date = DateTime.Parse(TestString(.Range("Y" & j + 1).Text), myDTFI)
                                        m_strdate = m_date.ToString(strDBDateFormat)
                                    End If
                                    msFile.Append(" COMP=""" + m_strdate + """")  'Datetime
                                    'msFile.Append(" COMP=""" + TestString(.Range("Y" & j + 1).Text) + """") 'Datetime

                                    msFile.Append(" OVCOST=""" + TestString(.Range("Z" & j + 1).Text) + """")
                                    msFile.Append(" CGRANT=""" + TestString(.Range("AA" & j + 1).Text) + """")
                                    msFile.Append(" SOVCOST=""" + TestString(.Range("AB" & j + 1).Text) + """")
                                    msFile.Append(" SCGRANT=""" + TestString(.Range("AC" & j + 1).Text) + """")
                                    msFile.Append(" STATUS=""" + TestString(.Range("AD" & j + 1).Text) + """")

                                    If TestString(.Range("AE" & j + 1).Text).Trim.Equals(String.Empty) Then
                                        m_strdate = ""
                                    Else
                                        m_date = DateTime.Parse(TestString(.Range("AE" & j + 1).Text), myDTFI)
                                        m_strdate = m_date.ToString(strDBDateFormat)
                                    End If
                                    msFile.Append(" CANCDATE=""" + m_strdate + """")  'Datetime
                                    'msFile.Append(" CANCDATE=""" + TestString(.Range("AE" & j + 1).Text) + """") 'Datetime

                                    msFile.Append(" REASONCODE=""" + TestString(.Range("AF" & j + 1).Text) + """")
                                    msFile.Append(" COMMENT=""" + TestString(.Range("AG" & j + 1).Text) + """")
                                    msFile.Append("></TABLA>" + vbCrLf)
                                    nValidRows = nValidRows + 1
                                Else
                                    nInvalidRows = nInvalidRows + 1
                                End If
                            End If
                        Else
                            msFile.Append("</ROOT>")
                            Exit For
                        End If
                    End With
                Next

                sCasesXML = msFile.ToString
                msFile.Length = 0
                EnviarXML(sCasesXML, sFileName, (nValidRows + nInvalidRows), nInvalidRows)

                If Not oWB Is Nothing Then
                    DeleteReferences(oSheet)
                    oXL.Workbooks(1).Close(False)
                    DeleteReferences(oWB)
                    oXL.Quit()
                    DeleteReferences(oXL)
                    System.GC.Collect()
                End If
            End If

            If File.Exists(sLocation) Then
                File.Delete(sLocation)
            End If

        Catch Exc As Exception
            Me.lblMsgImport.Text = "The file can not be read. Please verify that it is a valid Excel file."
            'Me.lblMsgImport.Text = Exc.Message
            'Throw Exc
        Finally
            'Added By raul Callupe 
            'thisThread.CurrentCulture = originalCulture
            'End Added

        End Try
    End Sub

    Private Sub DeleteReferences(ByRef References As Object)
        Try
            Do Until System.Runtime.InteropServices.Marshal.ReleaseComObject(References) <= 0
            Loop
        Catch
        Finally
            References = Nothing
        End Try
    End Sub
#End Region


#Region "Validate Format Cells"
    Public Function ValidateFormatDate(ByVal sColumn As String, ByVal sCell As String) As Boolean
        'If (IsDate(sCell.Trim) = True And (sCell.Trim).Length = 10) Or sCell.Trim.Equals(String.Empty) Then
        '    Return True
        'Else
        '    InvalidCell = InvalidCell & vbTab & "COLUMN [" & sColumn.ToUpper & "] VALUE [" & sCell & "]" & Chr(13)
        '    Return False
        'End If

        Try
            If sCell.Trim.Equals(String.Empty) = False Then
                DateTime.Parse(sCell.Trim, myDTFI)
            End If

            Return True
        Catch
            InvalidCell = InvalidCell & vbTab & "COLUMN [" & sColumn.ToUpper & "] VALUE [" & sCell & "]" & Chr(13)
            Return False
        End Try

    End Function

    Public Function ValidateFormatNumber(ByVal sColumn As String, ByVal sCell As String) As Boolean
        If IsNumeric(sCell.Trim) = True Or sCell.Trim.Equals(String.Empty) Then
            Return True
        Else
            InvalidCell = InvalidCell & vbTab & "COLUMN [" & sColumn.ToUpper & "] VALUE [" & sCell & "]" & Chr(13)
            Return False
        End If
    End Function

    Public Function ValidateLength(ByVal sColumn As String, ByVal sCell As String, ByVal sLength As Integer) As Boolean
        If Len(sCell.Trim) <= sLength Or sCell.Trim.Equals(String.Empty) Then
            Return True
        Else
            InvalidCell = InvalidCell & vbTab & "COLUMN [" & sColumn.ToUpper & "] VALUE [" & sCell & "]" & Chr(13)
        End If
        Return False
    End Function


    Public Function TestString(ByVal sCell As String) As String
        If Not sCell Is String.Empty Then
            sCell = Replace(sCell, "'", "´").Trim
            If sCell.Length > 0 Then sCell = Replace(sCell, "&", "and").Trim
            If sCell.Length > 0 Then sCell = Replace(sCell, "=", "").Trim
            If sCell.Length > 0 Then sCell = Replace(sCell, "<", "").Trim
            If sCell.Length > 0 Then sCell = Replace(sCell, ">", "").Trim
            If sCell.Length > 0 Then Return sCell Else Return ""
        Else : Return ""
        End If
    End Function

#End Region


#Region "Generate XML"
    Private Sub EnviarXML(ByVal sXML As String, ByVal sFileName As String, ByVal nRows As Integer, ByVal nInvalid As Integer)
        Dim oEC_User As EC_User = Me.GetUserFromSession
        Dim oCC_Case As New CC_Case
        Dim strMsg As String
        If oCC_Case.ImportCases(sXML, oEC_User.AccessGroup, oEC_User.UserID, oEC_User.Login, sFileName, nRows, nInvalid, strMsg) Then
            Me.lblMsgImport.Text = "File has been uploaded successfully."
            ShowLog(CInt(strMsg))
        Else
            Me.lblMsgImport.Text = strMsg
        End If
    End Sub



    Private Sub ShowLog(ByVal nLogImportId As Integer)
        Dim oCC_Case As New CC_Case
        Dim i, j As Integer
        Dim dsTotal, dsErrors, dsDetailErrors As DataSet
        Dim sLogDetails As String
        dsTotal = oCC_Case.GetSummaryImport(nLogImportId)

        sLogDetails = sLogDetails & Chr(13) & "---------------------------- Results ----------------------------------" & Chr(13)
        sLogDetails = sLogDetails & "File Name" + vbTab & vbTab + ":  " + dsTotal.Tables(0).Rows(0)("FileName").ToString() & Chr(13)
        sLogDetails = sLogDetails & "Total Records" + vbTab & ":  " + dsTotal.Tables(0).Rows(0)("TOTAL").ToString() & Chr(13)
        sLogDetails = sLogDetails & "Total Records Added" + vbTab + ":  " + dsTotal.Tables(0).Rows(0)("ADDED").ToString() & Chr(13)
        sLogDetails = sLogDetails & "Total Records Updated" + vbTab + ":  " + dsTotal.Tables(0).Rows(0)("UPDATED").ToString() & Chr(13)
        sLogDetails = sLogDetails & "Total Records Failed" + vbTab + ":  " + dsTotal.Tables(0).Rows(0)("FAILED").ToString() & Chr(13)
        sLogDetails = sLogDetails & "------------------------------------------------------------------------" & Chr(13)

        dsErrors = oCC_Case.GetErrorImport(nLogImportId)

        If Not InvalidCell Is String.Empty And Not InvalidCell Is Nothing Then
            sLogDetails = sLogDetails & "CASE NOT PROCESSED: Invalid Format" & Chr(13)
            sLogDetails = sLogDetails & InvalidCell
        End If
        For i = 0 To dsErrors.Tables(0).Rows.Count - 1
            sLogDetails = sLogDetails & Chr(13) & dsErrors.Tables(0).Rows(i)("description") & Chr(13)
            dsDetailErrors = oCC_Case.GetDetailErrorsImport(nLogImportId, dsErrors.Tables(0).Rows(i)("errorid"))
            For j = 0 To dsDetailErrors.Tables(0).Rows.Count - 1
                sLogDetails = sLogDetails + vbTab + dsDetailErrors.Tables(0).Rows(j)(0).ToString + Chr(13)
            Next
        Next
        txtLog.Visible = True
        txtLog.Text = sLogDetails
    End Sub

#End Region

    Private Sub btnCancel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCancel.Click
        Me.RedirectToHome()
    End Sub

End Class