将VB6代码转换为SQL查询的最佳方法

Question

我不是VB 6程序员，我正在努力将SQL从该VB 6表格中撤出。任何技巧都将不胜感激。我删除了一些昏暗的语句。基本上，这是一种格式化数据的4个不同报告的表格。当然，这是一个旧应用程序，我正在尝试在此处提取报告逻辑。

****删除了一些表单代码以符合字符限制****

#End Region
    Private Sub frmrptProductionControl_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        mkdtPC()
        mkdtReport()
        mkdtPCSorted()
    End Sub
    Function getMinEOQ(ByVal Material As String, ByVal DBConnection As SqlConnection) As Double
        Dim UserValues As Int64

        popdtParam(Material, "STRING", "MATERIAL")
        Dim strSQL As String = "SELECT USER_VALUES FROM MATERIAL WHERE MATERIAL = @MATERIAL"
        Dim getValue As New getSingleValuePQ(strSQL, "USER_VALUES", "NUMERIC", dtParam, DBConnection)
        UserValues = getValue.Value

        dtParam.Clear()
        popdtParam(UserValues, "NUMERIC", "USERVALUES")
        strSQL = "SELECT NUMERIC2 FROM USER_VALUES WHERE USER_VALUES = @USERVALUES"
        getValue = New getSingleValuePQ(strSQL, "NUMERIC2", "NUMERIC", dtParam, DBConnection)
        Return getValue.Value
    End Function
    Private Sub popdtPC(ByVal StartDate As Date, ByVal EndDate As Date, ByVal ReportType As String, Optional ByVal SortByPart As Boolean = 0)
        dsPC.Clear()
        Dim strsql As String
        strsql = "SELECT MATERIAL, JOB, STOCK, INPROCESS, LTAQTY, SALESPRICE, "
        strsql &= "LT, LTDEMANDDATE, DEPLETIONDATE, DEMAND6MONTHS, "
        strsql &= "DEMAND12MONTHS, PROFIT, UNITCOST, LTDEMAND FROM tblPartData  "

        If ReportType = "PRODUCTIONCONTROL" Then
            strsql &= "WHERE MATERIAL NOT IN (SELECT MATERIAL FROM TBLCRITICALPARTS) "
        End If

        Dim drdGetData As SqlDataReader = Nothing
        Dim cmmGetData As New SqlCommand(strsql, cnSQL)
        Try
            drdGetData = cmmGetData.ExecuteReader()
        Catch objException As SqlException
            Dim objerror As SqlError
            For Each objerror In objException.Errors
                MsgBox(objException.Message)
            Next
        End Try
        Dim Material, NewestJob, Deliveries, Comments, PersonResp, ExpireDate, CriticalColor, Program As String
        Dim Stock, InProcess, LTAQty, LT, Demand, Demand6Months, _
            Demand12Months, PromisedQty, PartCount, DelinquentCount, LTDemand, _
            NewestJobQty As Int32
        Dim Profit, DollarSum, DelinquentSum, SalesPrice As Double
        Dim LTDemandDate, DepletionDate, DemandDate, PromisedDate As Date
        Dim Critical As String
        Dim ins As Boolean
        PartCount = 0
        DelinquentCount = 0
        DelinquentSum = 0
        DollarSum = 0

        While drdGetData.Read
            Material = drdGetData("MATERIAL")
            Program = getProgram(Material, cnSQL2)
            ExpireDate = getExpireDate(Material, cnSQL2)
            'MinEOQ = getMinEOQ(Material, cnSQL2)
            Stock = drdGetData("STOCK")
            Dim returnDemand As Demand = New Demand(Material, StartDate, EndDate, cnSQL2)
            Demand = returnDemand.Value
            LTDemand = drdGetData("LTDEMAND")
            SalesPrice = drdGetData("SALESPRICE")
            PartCount = PartCount + 1
            DollarSum = DollarSum + Demand * SalesPrice
            NewestJob = drdGetData("JOB")
            NewestJobQty = getJobQty(NewestJob, cnSQL2)
            InProcess = drdGetData("INPROCESS")
            LTAQty = drdGetData("LTAQTY")
            Demand6Months = drdGetData("DEMAND6MONTHS")
            Demand12Months = drdGetData("DEMAND12MONTHS")
            LTDemandDate = drdGetData("LTDEMANDDATE")
            DepletionDate = drdGetData("DEPLETIONDATE")
            Profit = drdGetData("PROFIT")
            LT = drdGetData("LT")
            Dim returnDeliveries As Deliveries = New Deliveries(Material, StartDate, EndDate, cnSQL2)
            Deliveries = returnDeliveries.Value
            returnDeliveries = Nothing
            Dim returnDemandDate As DemandDate = New DemandDate(Material, cnSQL2)
            DemandDate = returnDemandDate.Value
            returnDemandDate = Nothing
            LTDemandDate = drdGetData("LTDEMANDDATE")
            If DemandDate < Now Then
                DelinquentCount = DelinquentCount + 1
                DelinquentSum = DelinquentSum + Demand * SalesPrice
            End If
            Dim returnCritical As CriticalPart = New CriticalPart(Material, cnSQL2)
            Critical = returnCritical.Critical
            PersonResp = returnCritical.PersonResp
            Comments = returnCritical.Comments
            PromisedQty = returnCritical.Quantity
            PromisedDate = IIf(IsDBNull(returnCritical.PromisedDate), Nothing, returnCritical.PromisedDate)
            CriticalColor = IIf(IsDBNull(returnCritical.CriticalColor), Nothing, returnCritical.CriticalColor)

            returnCritical = Nothing
           
            Select Case ReportType
                Case "PRODUCTIONCONTROL"
                    If Demand > Stock Then
                        ins = True
                    Else
                        ins = False
                    End If
                Case "PRODUCTIONPLANNING"
                    If Demand > Stock + InProcess Then
                        ins = True
                    Else
                        ins = False
                    End If
                Case "PRODUCTIONPLANNINGLT"
                    If InProcess + Stock < LTDemand Then
                        ins = True
                    Else
                        ins = False
                    End If
                Case "CRITICAL"
                    If Critical = "YES" Then
                        ins = True
                    Else
                        ins = False
                    End If
            End Select
            If ins = True Then
                insertdtPC(Material, Stock, Demand, NewestJob, InProcess, LTAQty, Demand6Months, _
                           Demand12Months, LTDemandDate, DepletionDate, Deliveries, DemandDate, Profit, _
                           Critical, PersonResp, Comments, PromisedQty, PromisedDate, LTDemand, NewestJobQty, _
                           LT, ExpireDate, CriticalColor, Program)
            End If
        End While
        Dim myrow As DataRow = dtReport.NewRow()

        myrow("DELINQUENT_COUNT") = DelinquentCount
        myrow("PART_COUNT") = PartCount
        myrow("DOLLAR_SUM") = DollarSum
        myrow("DELINQUENT_SUM") = DelinquentSum
        myrow("START_DATE") = StartDate
        myrow("END_DATE") = EndDate
        dtReport.Rows.Add(myrow)
        popdtPCSorted(ReportType, SortByPart)
        drdGetData.Close()
    End Sub
    Function getProgram(Material As String, DBConnection As SqlConnection) As String
        Dim strSQL As String
        strSQL = "SELECT T2.TEXT2 "
        strSQL &= "FROM MATERIAL T1, USER_VALUES T2 WHERE "
        strSQL &= "T1.USER_VALUES = T2.USER_VALUES AND "
        strSQL &= "T1.MATERIAL = @MATERIAL "
        popdtParam(Material, "STRING", "MATERIAL")
        Dim getValue As New getSingleValuePQ(strSQL, "TEXT2", "STRING", dtParam, DBConnection)

        If getValue.Value = "NOTHING" Then
            Return ""
        Else
            Return getValue.Value
        End If
    End Function


    Function getExpireDate(ByVal Material As String, ByVal DBConnection As SqlConnection) As String
        Dim strSQL As String
        strSQL = "SELECT MAX(T1.PROMISED_DATE) AS EXPIRE_DATE "
        strSQL &= "FROM SO_Header T1, SO_Detail T2 "
        strSQL &= "WHERE "
        strSQL &= "T1.Sales_Order = T2.Sales_Order AND "
        strSQL &= "T1.Customer = 'GECO500' AND SUBSTRING(T1.CUSTOMER_PO,1,2) = '90' AND "
        strSQL &= "T1.Status = 'Open' AND T2.Status = 'Open' AND "
        strSQL &= "T2.Material = @MATERIAL "
        popdtParam(Material, "STRING", "MATERIAL")
        Dim getDate As New getSingleValuePQ(strSQL, "EXPIRE_DATE", "DATE", dtParam, DBConnection)
        If getDate.Value = #1/1/1900# Then
            getExpireDate = " "
        Else
            getExpireDate = Format(getDate.Value, "Short Date")
        End If
    End Function
    Private Sub insertdtPC(ByVal Material As String, ByVal Stock As Int64, ByVal Demand As Int64, ByVal NewestJob As String, _
                           ByVal InProcess As Int64, ByVal LTAQty As Int64, ByVal Demand6Months As Int64, ByVal Demand12Months As Int64, _
                           ByVal LTDemandDate As Date, ByVal DepletionDate As Date, ByVal Deliveries As String, ByVal DemandDate As Date, ByVal Profit As Double, _
                           ByVal Critical As String, ByVal PersonResp As String, ByVal Comments As String, ByVal PromisedQty As Int64, ByVal PromisedDate As Date, ByVal LTDemand As Int64, _
                           ByVal NewestJobQty As Int64, ByVal LT As Int64, ByVal ExpireDate As String, CriticalColor As String, _
                           ByVal Program As String)

        

        strsql = "SELECT JOB, MAKE_QUANTITY, COMPLETED_QUANTITY FROM JOB "
        strsql &= "WHERE PART_NUMBER = '" + Material + "' AND STATUS = 'Active'"
        Dim Job, Routing As String
        Dim MakeQuantity, CompletedQuantity As Int32
        Routing = Nothing
        Dim drdGetData As SqlDataReader = Nothing
        Dim cmmGetData As New SqlCommand(strsql, DB1)
        Try
            drdGetData = cmmGetData.ExecuteReader()
        Catch objException As SqlException
            Dim objerror As SqlError
            For Each objerror In objException.Errors
                MsgBox(objException.Message)
            Next
        End Try
        Dim drdRead As Boolean = False

        While drdGetData.Read
            drdRead = True
            Job = drdGetData("JOB")
            MakeQuantity = drdGetData("MAKE_QUANTITY")
            CompletedQuantity = drdGetData("COMPLETED_QUANTITY")
            Dim returnRouting As RoutingString = New RoutingString(Job, DB2, DB3)
            Routing = returnRouting.Value
            returnRouting = Nothing
            insertDTRecord(Material, Stock, Demand, NewestJob, InProcess, LTAQty, Demand6Months, _
                           Demand12Months, LTDemandDate, DepletionDate, Job, MakeQuantity, _
                           CompletedQuantity, Routing, Deliveries, DemandDate, Profit, _
                           Critical, PersonResp, Comments, PromisedQty, PromisedDate, LTDemand, _
                           NewestJobQty, LT, Program, ExpireDate, dtPC, CriticalColor)
        End While
        If drdRead = False Then
            Job = "NONE"
            MakeQuantity = 0
            CompletedQuantity = 0
            insertDTRecord(Material, Stock, Demand, NewestJob, InProcess, LTAQty, Demand6Months, _
                                       Demand12Months, LTDemandDate, DepletionDate, Job, MakeQuantity, _
                                       CompletedQuantity, Routing, Deliveries, DemandDate, Profit, _
                                       Critical, PersonResp, Comments, PromisedQty, PromisedDate, LTDemand, _
                                       NewestJobQty, LT, Program, ExpireDate, dtPC, CriticalColor)
        End If
        DB1.Close()
        DB2.Close()
        DB3.Close()
    End Sub
    Private Sub insertDTRecord(ByVal Material As String, ByVal Stock As Int64, ByVal Demand As Int64, ByVal NewestJob As String, ByVal InProcess As Int64, ByVal LTAQty As Int64, ByVal Demand6Months As Int64, _
                               ByVal Demand12Months As Int64, ByVal LTDemandDate As Date, ByVal DepletionDate As Date, ByVal Job As String, ByVal MakeQuantity As Int64, _
                               ByVal CompletedQuantity As Int64, ByVal Routing As String, ByVal Deliveries As String, ByVal DemandDate As Date, ByVal Profit As Double, _
                               ByVal Critical As String, ByVal PersonResp As String, ByVal Comments As String, ByVal PromisedQty As Int64, ByVal PromisedDate As Date, ByVal LTDemand As Int64, _
                               ByVal NewestJobQty As Int64, ByVal LT As String, Program As String, _
                               ByVal ExpireDate As String, ByVal myDatatable As DataTable, Optional CriticalColor As String = "BLACK", Optional ByVal SORT_ORDER As Integer = -1)

        Dim myrow As DataRow = myDatatable.NewRow()
        Dim minEOQ As Int64
        minEOQ = getMinEOQ(Material, cnSQL2)
        If Not IsDBNull(minEOQ) Then
            If minEOQ <> 0 Then
                myrow("MIN_EOQ") = minEOQ
            End If
        End If
        myrow("MATERIAL") = Material
        myrow("PROGRAM") = Program
        myrow("STOCK") = Stock
        myrow("DEMAND") = Demand
        myrow("NEWEST_JOB") = NewestJob
        myrow("IN_PROCESS") = InProcess
        myrow("LTA_QUANTITY") = LTAQty
        myrow("DEMAND_6MONTHS") = Demand6Months
        myrow("DEMAND_12MONTHS") = Demand12Months
        myrow("LT_DEMAND_DATE") = LTDemandDate
        myrow("DEPLETION_DATE") = DepletionDate
        myrow("JOB") = Job
        myrow("MAKE_QUANTITY") = MakeQuantity
        myrow("COMPLETED_QUANTITY") = CompletedQuantity
        myrow("ROUTING") = Routing
        myrow("DELIVERIES") = Deliveries
        myrow("DEMAND_DATE") = DemandDate
        myrow("PROFIT") = Profit
        myrow("CRITICAL") = Critical
        myrow("PERSON_RESP") = PersonResp
        myrow("COMMENTS") = Comments
        myrow("PROMISED_QUANTITY") = PromisedQty
        If PromisedDate <> Nothing Then
            myrow("PROMISED_DATE") = Convert.ToString(Format(PromisedDate, "M/d/yy"))
        End If
        myrow("LT_DEMAND") = LTDemand
        myrow("NEWEST_JOB_QTY") = NewestJobQty
        myrow("LT") = Convert.ToInt32(LT)
        myrow("LT_DEMAND_DATE") = LTDemandDate
        myrow("LTA_QUANTITY") = LTAQty
        myrow("EXPIRE_DATE") = ExpireDate
        myrow("COLOR") = CriticalColor
        If SORT_ORDER <> -1 Then
            myrow("SORT_ORDER") = SORT_ORDER
        End If
        myDatatable.Rows.Add(myrow)
    End Sub
    Private Sub orderdtPC()
        Dim myRows As Array = dtPC.Select()
        Dim i As Integer
        For i = 0 To myRows.GetUpperBound(0)
            myRows(i)("SORT_ORDER") = i
        Next
    End Sub
    Private Sub mkdtReport()
        ' Create new DataColumn, set DataType, ColumnName and add to DataTable.    

     
        
    Private Sub mkdtPC()
        ' Create new DataColumn, set DataType, ColumnName and add to DataTable.    

***删除了一些昏暗的陈述以发布****

    Dim myDataColumn7 As DataColumn = New DataColumn
    myDataColumn7.DataType = System.Type.GetType("System.String")
    myDataColumn7.ColumnName = "ROUTING"
    myDataColumn7.ReadOnly = True
    myDataColumn7.Unique = False
    dtPCSorted.Columns.Add(myDataColumn7)

    Dim myDataColumn8 As DataColumn = New DataColumn
    myDataColumn8.DataType = System.Type.GetType("System.Int32")
    myDataColumn8.ColumnName = "MAKE_QUANTITY"
    myDataColumn8.ReadOnly = True
    myDataColumn8.Unique = False
    dtPCSorted.Columns.Add(myDataColumn8)

    Dim myDataColumn88 As DataColumn = New DataColumn
    myDataColumn88.DataType = System.Type.GetType("System.Int32")
    myDataColumn88.ColumnName = "COMPLETED_QUANTITY"
    myDataColumn88.ReadOnly = True
    myDataColumn88.Unique = False
    dtPCSorted.Columns.Add(myDataColumn88)

    Dim myDataColumn9 As DataColumn = New DataColumn
    myDataColumn9.DataType = System.Type.GetType("System.DateTime")
    myDataColumn9.ColumnName = "DEMAND_DATE"
    myDataColumn9.ReadOnly = True
    myDataColumn9.Unique = False
    dtPCSorted.Columns.Add(myDataColumn9)

    Dim myDataColumn11 As DataColumn = New DataColumn
    myDataColumn11.DataType = System.Type.GetType("System.String")
    myDataColumn11.ColumnName = "CRITICAL"
    myDataColumn11.ReadOnly = True
    myDataColumn11.Unique = False
    dtPCSorted.Columns.Add(myDataColumn11)

    Dim myDataColumn12 As DataColumn = New DataColumn
    myDataColumn12.DataType = System.Type.GetType("System.DateTime")
    myDataColumn12.ColumnName = "PROMISED_DATE"
    myDataColumn12.ReadOnly = True
    myDataColumn12.Unique = False
    dtPCSorted.Columns.Add(myDataColumn12)

    Dim myDataColumn13 As DataColumn = New DataColumn
    myDataColumn13.DataType = System.Type.GetType("System.Int32")
    myDataColumn13.ColumnName = "PROMISED_QUANTITY"
    myDataColumn13.ReadOnly = True
    myDataColumn13.Unique = False
    dtPCSorted.Columns.Add(myDataColumn13)

    Dim myDataColumn14 As DataColumn = New DataColumn
    myDataColumn14.DataType = System.Type.GetType("System.String")
    myDataColumn14.ColumnName = "COMMENTS"
    myDataColumn14.ReadOnly = True
    myDataColumn14.Unique = False
    dtPCSorted.Columns.Add(myDataColumn14)

    Dim myDataColumn15 As DataColumn = New DataColumn
    myDataColumn15.DataType = System.Type.GetType("System.String")
    myDataColumn15.ColumnName = "PERSON_RESP"
    myDataColumn15.ReadOnly = True
    myDataColumn15.Unique = False
    dtPCSorted.Columns.Add(myDataColumn15)

    Dim myDataColumn16 As DataColumn = New DataColumn
    myDataColumn16.DataType = System.Type.GetType("System.Int32")
    myDataColumn16.ColumnName = "LTA_QUANTITY"
    myDataColumn16.ReadOnly = True
    myDataColumn16.Unique = False
    dtPCSorted.Columns.Add(myDataColumn16)

    Dim myDataColumn17 As DataColumn = New DataColumn
    myDataColumn17.DataType = System.Type.GetType("System.DateTime")
    myDataColumn17.ColumnName = "LT_DEMAND_DATE"
    myDataColumn17.ReadOnly = True
    myDataColumn17.Unique = False
    dtPCSorted.Columns.Add(myDataColumn17)

    Dim myDataColumn18 As DataColumn = New DataColumn
    myDataColumn18.DataType = System.Type.GetType("System.Int32")
    myDataColumn18.ColumnName = "LT_DEMAND"
    myDataColumn18.ReadOnly = True
    myDataColumn18.Unique = False
    dtPCSorted.Columns.Add(myDataColumn18)

    Dim myDataColumn19 As DataColumn = New DataColumn
    myDataColumn19.DataType = System.Type.GetType("System.String")
    myDataColumn19.ColumnName = "NEWEST_JOB"
    myDataColumn19.ReadOnly = True
    myDataColumn19.Unique = False
    dtPCSorted.Columns.Add(myDataColumn19)

    Dim myDataColumn20 As DataColumn = New DataColumn
    myDataColumn20.DataType = System.Type.GetType("System.Double")
    myDataColumn20.ColumnName = "PROFIT"
    myDataColumn20.ReadOnly = True
    myDataColumn20.Unique = False
    dtPCSorted.Columns.Add(myDataColumn20)

    Dim myDataColumn21 As DataColumn = New DataColumn
    myDataColumn21.DataType = System.Type.GetType("System.Int32")
    myDataColumn21.ColumnName = "DEMAND_6MONTHS"
    myDataColumn21.ReadOnly = True
    myDataColumn21.Unique = False
    dtPCSorted.Columns.Add(myDataColumn21)

    Dim myDataColumn22 As DataColumn = New DataColumn
    myDataColumn22.DataType = System.Type.GetType("System.Int32")
    myDataColumn22.ColumnName = "DEMAND_12MONTHS"
    myDataColumn22.ReadOnly = True
    myDataColumn22.Unique = False
    dtPCSorted.Columns.Add(myDataColumn22)

    Dim myDataColumn23 As DataColumn = New DataColumn
    myDataColumn23.DataType = System.Type.GetType("System.Int32")
    myDataColumn23.ColumnName = "NEWEST_JOB_QTY"
    myDataColumn23.ReadOnly = True
    myDataColumn23.Unique = False
    dtPCSorted.Columns.Add(myDataColumn23)

    Dim myDataColumn24 As DataColumn = New DataColumn
    myDataColumn24.DataType = System.Type.GetType("System.String")
    myDataColumn24.ColumnName = "LT"
    myDataColumn24.ReadOnly = True
    myDataColumn24.Unique = False
    dtPCSorted.Columns.Add(myDataColumn24)

    Dim myDataColumn25 As DataColumn = New DataColumn
    myDataColumn25.DataType = System.Type.GetType("System.Double")
    myDataColumn25.ColumnName = "MIN_EOQ"
    myDataColumn25.ReadOnly = True
    myDataColumn25.Unique = False
    dtPCSorted.Columns.Add(myDataColumn25)

    Dim myDataColumn26 As DataColumn = New DataColumn
    myDataColumn26.DataType = System.Type.GetType("System.String")
    myDataColumn26.ColumnName = "EXPIRE_DATE"
    myDataColumn26.ReadOnly = True
    myDataColumn26.Unique = False
    dtPCSorted.Columns.Add(myDataColumn26)

    Dim myDataColumn27 As DataColumn = New DataColumn
    myDataColumn27.DataType = System.Type.GetType("System.String")
    myDataColumn27.ColumnName = "PROGRAM"
    myDataColumn27.ReadOnly = True
    myDataColumn27.Unique = False
    dtPCSorted.Columns.Add(myDataColumn27)




    'Dim PrimaryKeyColumns(1) As DataColumn
    'PrimaryKeyColumns(0) = dtPCSorted.Columns("SORT_ORDER")
    'dtPCSorted.PrimaryKey = PrimaryKeyColumns
End Sub
Private Sub popdtPCSorted(ByVal ReportType As String, SortbyPart As Boolean)
    Dim PCDV As DataView
    dtPCSorted.Clear()
    Select Case ReportType
        Case "CRITICAL"
            PCDV = New DataView(dsPC.Tables("PRODUCTIONCONTROL"), _
                                                            "MATERIAL <> ''", _
                                                            "MATERIAL, JOB", _
                                                            DataViewRowState.CurrentRows)
        Case "PRODUCTIONPLANNINGLT"
            PCDV = New DataView(dsPC.Tables("PRODUCTIONCONTROL"), _
                                                         "MATERIAL <> ''", _
                                                         "DEPLETION_DATE, MATERIAL, JOB", _
                                                        DataViewRowState.CurrentRows)
        Case Else
            If SortbyPart = False Then
                PCDV = New DataView(dsPC.Tables("PRODUCTIONCONTROL"), _
                                                    "MATERIAL <> ''", _
                                                    "DEMAND_DATE, MATERIAL, JOB", _
                                                    DataViewRowState.CurrentRows)
            Else
                PCDV = New DataView(dsPC.Tables("PRODUCTIONCONTROL"), _
                                                    "MATERIAL <> ''", _
                                                    "MATERIAL, DEMAND_DATE, JOB", _
                                                    DataViewRowState.CurrentRows)
            End If
    End Select

    Dim myDRV As DataRowView
    Dim i, InProcess, LT, Stock, Demand, LTAQty, Demand6Months, Demand12Months, _
        MakeQuantity, CompletedQuantity, PromisedQty, NewestJobQty, LTDemand As Int64
    Dim Profit, MinEOQ As Double
    Dim Material, NewestJob, Job, Routing, Deliveries, PersonResp, Comments, Critical, ExpireDate, CriticalColor, Program As String
    Dim LTDemandDate, DepletionDate, PromisedDate, DemandDate As Date
    i = 1
    For Each myDRV In PCDV
        Material = myDRV("MATERIAL")
        Program = myDRV("PROGRAM")
        CriticalColor = IIf(IsDBNull(myDRV("COLOR")), "BLACK", myDRV("COLOR"))
        If Not IsDBNull(myDRV("MIN_EOQ")) Then
            MinEOQ = myDRV("MIN_EOQ")
        End If
        Demand = myDRV("DEMAND")
        Stock = myDRV("STOCK")
        NewestJob = myDRV("NEWEST_JOB")
        InProcess = myDRV("IN_PROCESS")
        LTAQty = myDRV("LTA_QUANTITY")
        Demand6Months = myDRV("DEMAND_6MONTHS")
        Demand12Months = myDRV("DEMAND_12MONTHS")
        LTDemandDate = myDRV("LT_DEMAND_DATE")
        ExpireDate = myDRV("EXPIRE_DATE")
        If ReportType = "CRITICAL" Then
            DepletionDate = myDRV("DEMAND_DATE")
        Else
            DepletionDate = myDRV("DEPLETION_DATE")
        End If

        Job = myDRV("JOB")
        MakeQuantity = myDRV("MAKE_QUANTITY")
        CompletedQuantity = myDRV("COMPLETED_QUANTITY")

        If IsDBNull(myDRV("ROUTING")) Then
            Routing = " "
        Else
            Routing = myDRV("ROUTING")
        End If

        Deliveries = myDRV("DELIVERIES")
        DemandDate = myDRV("DEMAND_DATE")
        Profit = myDRV("PROFIT")
        Critical = myDRV("CRITICAL")
        If IsDBNull(myDRV("PERSON_RESP")) Then
            PersonResp = "X"
        Else
            PersonResp = myDRV("PERSON_RESP")
        End If
        If IsDBNull(myDRV("COMMENTS")) Then
            Comments = "X"
        Else
            Comments = myDRV("COMMENTS")
        End If
        PromisedQty = myDRV("PROMISED_QUANTITY")
        If IsDBNull(myDRV("PROMISED_DATE")) Then
            PromisedDate = Nothing
        Else
            PromisedDate = myDRV("PROMISED_DATE")
        End If
        LTDemand = myDRV("LT_DEMAND")
        NewestJobQty = myDRV("NEWEST_JOB_QTY")
        LT = myDRV("LT")

        insertDTRecord(Material, Stock, Demand, NewestJob, InProcess, LTAQty, Demand6Months, _
                               Demand12Months, LTDemandDate, DepletionDate, Job, MakeQuantity, _
                               CompletedQuantity, Routing, Deliveries, DemandDate, Profit, _
                               Critical, PersonResp, Comments, PromisedQty, PromisedDate, LTDemand, _
                               NewestJobQty, LT, Program, ExpireDate, dtPCSorted, CriticalColor, i)
        i = i + 1
    Next
    myDRV = Nothing
End Sub
Function getSortDate(ByVal inDate As Date) As Date
    Dim strYear, strMonth, strDay As String
    strYear = Trim(Str(DatePart(DateInterval.Year, inDate)))
    Dim numMonth As Int64 = DatePart(DateInterval.Month, inDate)
    Dim numDay As Int64 = DatePart(DateInterval.Day, inDate)


    If numMonth < 10 Then
        strMonth = "0" & Trim(Str(numMonth))
    Else
        strMonth = Trim(Str(numMonth))
    End If

    If numDay < 10 Then
        strDay = "0" & Trim(Str(numDay))
    Else
        strDay = Trim(Str(numDay))
    End If
    getSortDate = strYear & strMonth & strDay
End Function
Private Sub btnrptProductionControl_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnrptProductionControl.Click
    Dim sortByPart As Boolean
    If cbSortByPart.Checked Then
        sortByPart = True
    Else
        sortByPart = False
    End If

    Dim tStartDate As String = txtStartDate.Text
    Dim tEndDate As String = txtEndDate.Text
    dsPC.Clear()
    If tStartDate = "" Or tEndDate = "" Then
        MsgBox("Please enter valid start and end dates.")
    Else
        If Not IsDate(tStartDate) Or Not IsDate(tEndDate) Then
            MsgBox("Please enter valid start and stop dates.")
        Else
            popdtPC(Convert.ToDateTime(tStartDate), Convert.ToDateTime(tEndDate), "PRODUCTIONCONTROL", sortByPart)
            Dim myfrm As New afrmReportPreview(afrmReportPreview.ReportType.rptProductionControl, dsPC)
            myfrm.ShowDialog()
        End If
    End If
End Sub