classes/dataupload.vb - main - ivan/ModMas

classes/dataupload.vb
462 строки17 KB
i.krivokon
Initial commit
13 мар 2026, 13:49
13 мар 2026, 13:4900d9fb6
Imports System.IO
Imports sharedClasses.dbFunctions

Namespace dataUpload
    Public Class dataUpload

        Private sqlf As SqlFunctions

        Public Sub New(conString As String)
            'Object to work with database
            sqlf = New SqlFunctions(conString)
        End Sub

        Public Function UploadTable(ByVal tabName As String, _
                                    ByVal fileName As String, _
                                    Optional ByVal ClearData As Boolean = False, _
                                    Optional ByVal FixDecimal As Boolean = False, _
                                    Optional skipColumns As Collection = Nothing) As String
            Dim rowCount As Integer = 1
            Try
                Dim SQL As String
                If ClearData Then
                    SQL = "DELETE FROM " & tabName
                    sqlf.NonQuery(SQL)
                End If


                Dim sqlScript As String = "select name as COLUMN_NAME from sys.columns where object_id in ( " & _
                    "select object_id from sys.tables " & _
                    "where name = '" & tabName & "' " & _
                    ") order by column_id "
                Dim conf As Data.DataTable = sqlf.GetData(sqlScript)

                '----------------------------------------------
                Dim i As Integer = 0
                Dim sColumnsList As String = ""
                Dim iColumnsNumber As Integer = 0
                For Each dr As DataRow In conf.Rows
                    If dr("COLUMN_NAME") <> "ID" Then
                        If i > 0 Then sColumnsList &= ","
                        sColumnsList &= dr("COLUMN_NAME")
                        i += 1
                    End If
                Next
                iColumnsNumber = i

                Dim f As New StreamReader(fileName)
                Dim s As String = ""
                Dim ss() As String
                Dim fieldsCount As Integer = 1
                Dim skipColumnsNumbers As New Collection
                Do While f.Peek >= 0
                    s = f.ReadLine
                    ss = s.Split(vbTab)
                    SQL = "INSERT INTO " & tabName & " ("
                    SQL &= sColumnsList
                    SQL &= ") VALUES ("
                    i = 1
                    fieldsCount = 0
                    For Each s In ss
                        fieldsCount += 1
                        If rowCount = 1 Then
                            If Not IsNothing(skipColumns) Then
                                If skipColumns.Contains(s) Then
                                    skipColumnsNumbers.Add(fieldsCount, fieldsCount)
                                    Continue For
                                End If
                            End If
                        End If

                        If Not skipColumnsNumbers.Contains(fieldsCount) Then
                            If i > iColumnsNumber Then Exit For
                            If i > 1 Then SQL &= ","
                            If FixDecimal Then s = s.Replace(".", ",")
                            SQL &= "'" & s.Replace("'", "") & "'"
                            i += 1
                        End If
                    Next
                    SQL &= ")"
                    sqlf.NonQuery(SQL)

                    rowCount += 1
                Loop

            Catch ex As Exception
                Return ex.Message
            End Try
            Return "File " & fileName & " was sucessfully uploaded to " & tabName & " table. Uploaded " & rowCount & " rows."
        End Function

        Public Function DailyPlanTranslate(numberOfDates As Integer, dateStartColumnIndex As Integer) As Boolean
            Dim SQL As String
            Dim PlanDates(numberOfDates - 1) As String
            Dim i As Integer = 0
            Dim j As Integer = 0
            Dim sColumnsList As String = ""
            Dim saColumnsList(100, 1) As String
            Dim iColumnsNumber As Integer = 0

            Dim numberType As String = ""


            SQL = "DELETE FROM translate_plan"
            sqlf.NonQuery(SQL, False)

            SQL = "SELECT * FROM raw_plan p  WHERE p.model = 'Model'"


            Dim dt As DataTable = sqlf.GetData(SQL)
            '----------------------------------------------
            If Not IsNothing(dt) Then
                For Each rr As DataRow In dt.Rows
                    For i = 1 To 14
                        PlanDates(i - 1) = Date.Today.Year.ToString & "." & rr.Item("DATE" & i).ToString.Replace("/", ".").Substring(0, 5)
                    Next
                Next
            Else
                Return False
            End If
            i = 0
            SQL = "select sc.name as COLUMN_NAME,  " & _
                    "(select name from sys.types where user_type_id = sc.user_type_id ) as DATA_TYPE " & _
                    "from sys.columns as sc " & _
                    "where sc.object_id in ( " & _
                    "select object_id from sys.tables " & _
                    "where name = 'translate_plan' " & _
                    ") order by column_id "
            numberType = "numeric"


            dt = sqlf.GetData(SQL)

            If Not IsNothing(dt) Then
                For Each rr As DataRow In dt.Rows
                    If rr.Item("COLUMN_NAME") <> "ID" Then
                        If i > 0 Then sColumnsList &= ","
                        sColumnsList &= rr.Item("COLUMN_NAME")
                        saColumnsList(i, 0) = rr.Item("COLUMN_NAME")
                        saColumnsList(i, 1) = rr.Item("DATA_TYPE")
                        i += 1
                    End If
                Next
                iColumnsNumber = i
            Else
                Return False
            End If

            i = 0
            SQL = "SELECT * FROM raw_plan p  WHERE p.model <> 'Model'"

            dt = sqlf.GetData(SQL)

            If Not IsNothing(dt) Then
                For Each rr As DataRow In dt.Rows
                    j = 0
                    SQL = "INSERT INTO translate_plan (" & sColumnsList & ") VALUES ("

                    For i = 0 To rr.ItemArray.Count - 1
                        If i > 0 Then SQL &= ","
                        '28  -  start date columns
                        If i >= dateStartColumnIndex And j < numberOfDates Then
                            SQL &= "'" & PlanDates(j) & "',"
                            j += 1
                        End If
                        If saColumnsList(i + j, 1) = numberType Then
                            SQL &= rr.Item(i).ToString.Replace(" ", "") '.Replace(".", ",") 
                        Else
                            SQL &= "'" & rr.Item(i).ToString & "'"
                        End If
                    Next
                    SQL &= ")"
                    sqlf.NonQuery(SQL)
                Next
            Else
                Return False
            End If
            Return True
        End Function

        Public Function DailyPlan(numberOfDates As Integer, dateStartColumnIndex As Integer) As Boolean
            Dim SQL As String
            Dim PlanDates(13) As String
            Dim i As Integer = 0
            Dim j As Integer = 0
            Dim k As Integer = 0
            Dim sColumnsList As String = ""
            Dim saColumnsList(100, 2) As String
            Dim iColumnsNumber As Integer = 0

            Dim numberType As String = ""

            SQL = "DELETE FROM daily_plan"
            sqlf.NonQuery(SQL)
            i = 0
            SQL = "select sc.name as COLUMN_NAME,  " & _
                       "(select name from sys.types where user_type_id = sc.user_type_id ) as DATA_TYPE " & _
                       "from sys.columns as sc " & _
                       "where sc.object_id in ( " & _
                       "select object_id from sys.tables " & _
                       "where name = 'daily_plan' " & _
                       ") and sc.name <> 'ID' order by column_id "
            numberType = "numeric"


            Dim dt As DataTable = sqlf.GetData(SQL)
            '----------------------------------------------

            If Not IsNothing(dt) Then
                For Each rr As DataRow In dt.Rows

                    If i > 0 Then sColumnsList &= ","
                    sColumnsList &= rr.Item("COLUMN_NAME")
                    saColumnsList(i, 0) = rr.Item("COLUMN_NAME")
                    saColumnsList(i, 1) = rr.Item("DATA_TYPE")
                    i += 1
                Next
                iColumnsNumber = i
            Else
                Return False
            End If

            SQL = "SELECT * FROM translate_plan"
            dt = sqlf.GetData(SQL)

            If Not IsNothing(dt) Then
                For Each rr As DataRow In dt.Rows
                    For i = 1 To numberOfDates
                        If rr.Item("QTY" & i) = 0 Then
                            'SKIPPING DATES WITH 0 PRODUCTION PLAN
                            Continue For
                        End If
                        k = 0
                        SQL = "INSERT INTO daily_plan (" & sColumnsList & ") VALUES ("
                        For j = 0 To dateStartColumnIndex - 1
                            If j > 0 Then SQL &= ","
                            If saColumnsList(k, 1) = numberType Then
                                SQL &= rr.Item(j).ToString.Replace(",", ".")
                            Else
                                SQL &= "'" & rr.Item(j).ToString & "'"
                            End If
                            k += 1
                        Next
                        SQL &= ",'" & rr.Item("DATE" & i).ToString & "'," & "'" & rr.Item("QTY" & i).ToString.Replace(",", ".") & "'"
                        k += 2
                        For j = dateStartColumnIndex + numberOfDates * 2 To rr.ItemArray.Count - 1
                            If saColumnsList(k, 1) = numberType Then
                                SQL &= "," & rr.Item(j).ToString.Replace(",", ".")
                            Else
                                SQL &= "," & "'" & rr.Item(j).ToString & "'"
                            End If
                            k += 1
                        Next
                        SQL &= ")"
                        sqlf.NonQuery(SQL, True)
                    Next
                Next
            Else
                Return False
            End If
            Return True
        End Function


    End Class

    Public Class BulkUpload
        Implements IDataReader

        Private _streamReader As StreamReader
        Private _currentLine As String
        Private _currentLineValues() As String
        Public separator As String = vbTab

        Public Sub New(filePath As String)
            _streamReader = New StreamReader(filePath)
        End Sub

        Public Sub Close() Implements IDataReader.Close
            _streamReader.Close()
            _currentLine = Nothing
            _currentLineValues = Nothing
        End Sub

        Public ReadOnly Property Depth As Integer Implements IDataReader.Depth
            Get
                Return Nothing
            End Get
        End Property

        Public Function GetSchemaTable() As DataTable Implements IDataReader.GetSchemaTable
            Return Nothing
        End Function

        Public ReadOnly Property IsClosed As Boolean Implements IDataReader.IsClosed
            Get
                Return Nothing
            End Get
        End Property

        Public Function NextResult() As Boolean Implements IDataReader.NextResult
            Return Nothing

        End Function

        Public Function Read() As Boolean Implements IDataReader.Read
            If _streamReader.EndOfStream Then Return False
            _currentLine = _streamReader.ReadLine
            _currentLineValues = _currentLine.Split(separator)
            Return True 'Read()
        End Function

        Public ReadOnly Property RecordsAffected As Integer Implements IDataReader.RecordsAffected
            Get
                Return Nothing
            End Get
        End Property

        Public ReadOnly Property FieldCount As Integer Implements IDataRecord.FieldCount
            Get
                Return _currentLineValues.Count
            End Get
        End Property

        Public Function GetBoolean(i As Integer) As Boolean Implements IDataRecord.GetBoolean
            Return Nothing
        End Function

        Public Function GetByte(i As Integer) As Byte Implements IDataRecord.GetByte
            Return Nothing
        End Function

        Public Function GetBytes(i As Integer, fieldOffset As Long, buffer() As Byte, bufferoffset As Integer, length As Integer) As Long Implements IDataRecord.GetBytes
            Return Nothing
        End Function

        Public Function GetChar(i As Integer) As Char Implements IDataRecord.GetChar
            Return Nothing
        End Function

        Public Function GetChars(i As Integer, fieldoffset As Long, buffer() As Char, bufferoffset As Integer, length As Integer) As Long Implements IDataRecord.GetChars
            Return Nothing
        End Function

        Public Function GetData(i As Integer) As IDataReader Implements IDataRecord.GetData
            Return Nothing
        End Function

        Public Function GetDataTypeName(i As Integer) As String Implements IDataRecord.GetDataTypeName
            Return Nothing
        End Function

        Public Function GetDateTime(i As Integer) As Date Implements IDataRecord.GetDateTime
            Return Nothing
        End Function

        Public Function GetDecimal(i As Integer) As Decimal Implements IDataRecord.GetDecimal
            Return Nothing
        End Function

        Public Function GetDouble(i As Integer) As Double Implements IDataRecord.GetDouble
            Return Nothing
        End Function

        Public Function GetFieldType(i As Integer) As Type Implements IDataRecord.GetFieldType
            Return Nothing
        End Function

        Public Function GetFloat(i As Integer) As Single Implements IDataRecord.GetFloat
            Return Nothing
        End Function

        Public Function GetGuid(i As Integer) As Guid Implements IDataRecord.GetGuid
            Return Nothing
        End Function

        Public Function GetInt16(i As Integer) As Short Implements IDataRecord.GetInt16
            Return Nothing
        End Function

        Public Function GetInt32(i As Integer) As Integer Implements IDataRecord.GetInt32
            Return Nothing
        End Function

        Public Function GetInt64(i As Integer) As Long Implements IDataRecord.GetInt64
            Return Nothing
        End Function

        Public Function GetName(i As Integer) As String Implements IDataRecord.GetName
            Return Nothing
        End Function

        Public Function GetOrdinal(name As String) As Integer Implements IDataRecord.GetOrdinal
            Return Nothing
        End Function

        Public Function GetString(i As Integer) As String Implements IDataRecord.GetString
            Return Nothing
        End Function

        Public Function GetValue(i As Integer) As Object Implements IDataRecord.GetValue
            Try
                Return _currentLineValues(i)
            Catch ex As Exception
                Return Nothing
            End Try
        End Function

        Public Function GetValues(values() As Object) As Integer Implements IDataRecord.GetValues
            Return Nothing
        End Function

        Public Function IsDBNull(i As Integer) As Boolean Implements IDataRecord.IsDBNull
            Return Nothing
        End Function

        Default Public Overloads ReadOnly Property Item(i As Integer) As Object Implements IDataRecord.Item
            Get
                Return Nothing
            End Get
        End Property

        Default Public Overloads ReadOnly Property Item(name As String) As Object Implements IDataRecord.Item
            Get
                Return Nothing
            End Get
        End Property

#Region "IDisposable Support"
        Private disposedValue As Boolean ' To detect redundant calls

        ' IDisposable
        Protected Overridable Sub Dispose(disposing As Boolean)
            If Not Me.disposedValue Then
                If disposing Then
                    ' TODO: dispose managed state (managed objects).
                    _streamReader.Close()
                End If

                ' TODO: free unmanaged resources (unmanaged objects) and override Finalize() below.
                ' TODO: set large fields to null.
            End If
            Me.disposedValue = True
        End Sub

        ' TODO: override Finalize() only if Dispose(ByVal disposing As Boolean) above has code to free unmanaged resources.
        'Protected Overrides Sub Finalize()
        '    ' Do not change this code.  Put cleanup code in Dispose(ByVal disposing As Boolean) above.
        '    Dispose(False)
        '    MyBase.Finalize()
        'End Sub

        ' This code added by Visual Basic to correctly implement the disposable pattern.
        Public Sub Dispose() Implements IDisposable.Dispose
            ' Do not change this code.  Put cleanup code in Dispose(disposing As Boolean) above.
            Dispose(True)
            GC.SuppressFinalize(Me)
        End Sub
#End Region

    End Class

End Namespace