Public Class Form_合并备料查询
    Dim m_Table As New System.Data.DataTable
    Dim m_InitOver As Boolean = False
    Dim m_PartNumber As New CPartNumber

    Dim validBomPnList As New ArrayList
    Dim validBomBackupCountList As New ArrayList

    Public Enum COLS_INPUT
        序号
        料号PN
        数量
        max
    End Enum

    Public Shared m_COLS_NAME_INPUT(,) As String = {
        {"序号", "0"},
        {"料号PN", "1"},
        {"数量", "1"}
    }

    Public Enum COLS_QUERYINFO
        序号
        料号PN
        物料名称
        规格描述
        单位
        总数量
        库存数量
        剩余数量
        max
    End Enum

    Public Shared m_COLS_NAME_QUERYINFO(,) As String = {
        {"序号", "0"},
        {"料号PN", "1"},
        {"物料名称", "0"},
        {"规格描述", "0"},
        {"单位", "0"},
        {"总数量", "0"},
        {"库存数量", "0"},
        {"剩余数量", "0"}
    }


    Private Sub Form_合并备料查询_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        InitGrid_Input()
    End Sub

    Private Sub InitGrid_Input()
        Grid_Input.NewFile()

        Grid_Input.AllowUserSort = False
        Grid_Input.DisplayFocusRect = False
        Grid_Input.ExtendLastCol = True
        Grid_Input.DisplayRowArrow = True
        Grid_Input.BoldFixedCell = False
        Grid_Input.SortIndicatorStyle = FlexCell.SortIndicatorStyleEnum.Light3D

        Grid_Input.Cols = COLS_INPUT.max
        For col As Integer = 0 To COLS_INPUT.max - 1
            Grid_Input.Cell(0, col).Text = m_COLS_NAME_INPUT(col, 0)
        Next

        '调整宽度
        Grid_Input.Column(COLS_INPUT.序号).Width = 50
        Grid_Input.Column(COLS_INPUT.料号PN).Width = 100
        Grid_Input.Column(COLS_INPUT.数量).Width = 50

        Grid_Input.AllowUserSort = True
        Grid_Input.Locked = False
        Grid_Input.Column(COLS_INPUT.序号).Locked = True

        Grid_Input.Rows = 500

        Application.DoEvents()
    End Sub

    Private Sub InitGrid_QueryInfo(ByVal BomPnCount As Integer, ByRef bomArray As ArrayList)
        Grid_QueryInfo.NewFile()

        Grid_QueryInfo.AllowUserSort = False
        Grid_QueryInfo.DisplayFocusRect = False
        Grid_QueryInfo.ExtendLastCol = True
        Grid_QueryInfo.DisplayRowArrow = True
        Grid_QueryInfo.BoldFixedCell = False
        Grid_QueryInfo.SortIndicatorStyle = FlexCell.SortIndicatorStyleEnum.Light3D

        Grid_QueryInfo.Cols = COLS_QUERYINFO.max
        For col As Integer = 0 To COLS_QUERYINFO.max - 1
            Grid_QueryInfo.Cell(0, col).Text = m_COLS_NAME_QUERYINFO(col, 0)
        Next

        If BomPnCount > 0 Then
            For i = 0 To BomPnCount - 1
                Grid_QueryInfo.Cols += 1
                Grid_QueryInfo.Cell(0, Grid_QueryInfo.Cols - 1).Text = bomArray.Item(i)
            Next
        End If

        '调整宽度
        Grid_QueryInfo.Column(COLS_QUERYINFO.序号).Width = My.Settings.FORM_合并用料查询_COL_WIDTH_序号
        Grid_QueryInfo.Column(COLS_QUERYINFO.料号PN).Width = My.Settings.FORM_合并用料查询_COL_WIDTH_料号PN
        Grid_QueryInfo.Column(COLS_QUERYINFO.物料名称).Width = My.Settings.FORM_合并用料查询_COL_WIDTH_物料名称
        Grid_QueryInfo.Column(COLS_QUERYINFO.规格描述).Width = My.Settings.FORM_合并用料查询_COL_WIDTH_规格描述
        Grid_QueryInfo.Column(COLS_QUERYINFO.单位).Width = My.Settings.FORM_合并用料查询_COL_WIDTH_单位
        Me.Height = My.Settings.FORM_合并用料查询_HEIGHT
        Me.Width = My.Settings.FORM_合并用料查询_WIDTH

        Grid_QueryInfo.AllowUserSort = True
        Grid_QueryInfo.Locked = True
        Grid_QueryInfo.Rows = 1

        Application.DoEvents()
    End Sub


    Private Function QueryBom(ByVal BomName As String, ByRef dataTable As Data.DataTable) As Boolean
        Dim strSelect As String = "SELECT * FROM `" & "bom_" & BomName & "`"

        '查询入库数量
        Dim result As Boolean = False
        Dim strSQL As String = strSelect
        dataTable.Rows.Clear()
        dataTable.Columns.Clear()
        If SQL_Query(COL_RIGHTS.BOM管理, strSQL, dataTable) = True Then
            result = True
        End If

        Return result
    End Function

    Private Function CheckBomExist(ByVal bomName As String, ByRef isExist As Boolean) As Boolean
        Dim strSQL As String = "CHECK TABLE `" & "bom_" & bomName & "`" 'CHECK TABLE `BOM_U01-ATMEGA328P-TQFP32`
        Dim result As Boolean = False
        m_Table.Rows.Clear()
        m_Table.Columns.Clear()
        isExist = False
        If SQL_Query(COL_RIGHTS.BOM管理, strSQL, m_Table) = True Then
            If m_Table.Rows.Count > 0 Then
                If m_Table.Rows(0).Item(3) = "OK" Then
                    isExist = True
                End If
            End If
            result = True
        End If

        Return result
    End Function

    Private Sub bt_Go_Click(sender As Object, e As EventArgs) Handles bt_Go.Click
        '检测Input表格是否存在错误
        Dim inputSn As String = ""
        Dim validBomPnCount As Integer = 0
        validBomPnList.Clear()
        validBomBackupCountList.Clear()
        Dim notEmptyCount As Integer = 0
        Dim curRowResult As Boolean = False

        For row = 1 To Grid_Input.Rows - 1
            Grid_Input.Cell(row, COLS_INPUT.序号).Text = ""
            Grid_Input.Cell(row, COLS_INPUT.料号PN).BackColor = Color.White
            Grid_Input.Cell(row, COLS_INPUT.数量).BackColor = Color.White
        Next
        For row = 1 To Grid_Input.Rows - 1
            inputSn = Grid_Input.Cell(row, COLS_INPUT.料号PN).Text
            If inputSn.Length > 0 Then
                notEmptyCount += 1
                '检查输入料号是否有效
                curRowResult = True

                Dim isExist As Boolean = False
                If m_PartNumber.CheckPartNumber(inputSn, isExist) = ERROR_CODE.SUCCESS Then
                    If isExist = False Then
                        Grid_Input.Cell(row, COLS_INPUT.料号PN).BackColor = Color.Red
                        curRowResult = False
                    End If
                Else
                    MsgBox("查询BOM料号错误!")
                    Return
                End If

                '检测输入料号的料表是否已经存在
                If CheckBomExist(inputSn, isExist) = True Then
                    If isExist = False Then
                        Grid_Input.Cell(row, COLS_INPUT.料号PN).BackColor = Color.Red
                        curRowResult = False
                    End If
                Else
                    MsgBox("无法查询输入料号对应的料表是否存在，请检查网络连接!")
                    Return
                End If

                If curRowResult = True Then
                    Grid_Input.Cell(row, COLS_INPUT.料号PN).BackColor = Color.Green

                    '检查数量是否正确
                    Dim numStr As String = Grid_Input.Cell(row, COLS_INPUT.数量).Text
                    Dim numNum As Integer = 0
                    Try
                        numNum = numStr
                        validBomPnCount += 1
                        validBomPnList.Add(inputSn)
                        validBomBackupCountList.Add(numNum)
                        Grid_Input.Cell(row, COLS_INPUT.数量).BackColor = Color.Green
                    Catch ex As Exception
                        Grid_Input.Cell(row, COLS_INPUT.数量).BackColor = Color.Red
                    End Try
                Else
                    Grid_Input.Cell(row, COLS_INPUT.料号PN).BackColor = Color.Red
                End If
                Grid_Input.Cell(row, COLS_INPUT.序号).Text = validBomPnCount
            End If
        Next

        If notEmptyCount > 0 Then
            If validBomPnCount <> notEmptyCount Then
                MsgBox("输入BOM料号列表存在错误")
                Return
            End If
        Else
            MsgBox("输入不能为空")
            Return
        End If

        '重新设置QueryInfo表格内容
        InitGrid_QueryInfo(validBomPnCount, validBomPnList)

        '逐个机种查询-----------------------------
        For bomIndex = 0 To validBomPnCount - 1
            '查询物料列表
            Dim queryBomName As String = validBomPnList.Item(bomIndex)
            If QueryBom(queryBomName, m_Table) = True Then
                CompareAndFillToGridByDataTable(bomIndex)
            End If
        Next

        '查询物料其它信息
        For rowIndex = 1 To Grid_QueryInfo.Rows - 1
            If Grid_QueryInfo.Cell(rowIndex, COLS_QUERYINFO.料号PN).Text.Length > 0 Then
                If GetPNItemInfo(rowIndex) = False Then
                    Return
                End If
            End If
        Next

        '查询库存数量
        For rowIndex = 1 To Grid_QueryInfo.Rows - 1
            Dim strPN As String = Grid_QueryInfo.Cell(rowIndex, COLS_QUERYINFO.料号PN).Text
            If strPN.Length > 0 Then
                Dim countInStore As Integer = 0
                Dim usageCount As Integer = Grid_QueryInfo.Cell(rowIndex, COLS_QUERYINFO.总数量).Text
                If QueryStorageCount(strPN, countInStore) = True Then
                    Grid_QueryInfo.Cell(rowIndex, COLS_QUERYINFO.库存数量).Text = countInStore
                    Grid_QueryInfo.Cell(rowIndex, COLS_QUERYINFO.剩余数量).Text = countInStore - usageCount
                Else
                    Return
                End If
            End If
        Next

        MsgBox("查询完成.")
    End Sub

    Private Function QueryStorageCount(ByVal PartNumber As String, ByRef rCount As Integer) As Boolean
        Dim strSelect As String = "SELECT SUM(`" & CMaterialInOut.m_COLS_NAME(CMaterialInOut.COLS.数量) & "`) FROM "
        strSelect &= "`" & CMaterialInOut.cst_TABLE_NAME & "` "
        strSelect &= " WHERE `" & CMaterialInOut.m_COLS_NAME(CMaterialInOut.COLS.料号PN) & "` COLLATE gbk_chinese_ci = '" & PartNumber & "' "

        '查询入库数量
        Dim result As Boolean = False
        Dim Count_In As Integer = 0
        Dim strSQL As String = strSelect
        strSQL &= "AND `" & CMaterialInOut.m_COLS_NAME(CMaterialInOut.COLS.入库出库) & "` = '0'"
        m_Table.Rows.Clear()
        m_Table.Columns.Clear()
        If SQL_Query(COL_RIGHTS.原料库存管理, strSQL, m_Table) = True Then
            Try
                Count_In = m_Table.Rows(0).Item(0)
            Catch ex As Exception
                Count_In = 0
            End Try

            result = True
        End If

        '查询出库数量
        Dim Count_Out As Integer = 0
        strSQL = strSelect
        strSQL &= "AND `" & CMaterialInOut.m_COLS_NAME(CMaterialInOut.COLS.入库出库) & "` = '1'"
        If result Then
            result = False
            m_Table.Rows.Clear()
            m_Table.Columns.Clear()
            If SQL_Query(COL_RIGHTS.原料库存管理, strSQL, m_Table) = True Then
                Try
                    Count_Out = m_Table.Rows(0).Item(0)
                Catch ex As Exception
                    Count_Out = 0
                End Try
                result = True
            End If
        End If

        rCount = Count_In - Count_Out

        Return result
    End Function

    Private Sub CompareAndFillToGridByDataTable(ByVal bomIndex As Integer)
        Dim existRows As Integer = Grid_QueryInfo.Rows
        Dim insertRow As Integer = 0
        Dim queryPn As String = ""
        Dim queryCount As Integer = 0
        Dim curCount As Integer = 0
        Dim CountForBackup As Integer = validBomBackupCountList.Item(bomIndex)
        Dim MultiCount As Integer = 0
        If m_Table.Rows.Count > 0 Then
            For row = 0 To m_Table.Rows.Count - 1
                queryPn = m_Table.Rows.Item(row).Item(2)
                queryCount = m_Table.Rows.Item(row).Item(3)
                MultiCount = queryCount * CountForBackup

                Dim findIndex As Integer = -1
                For rowIndex = 1 To Grid_QueryInfo.Rows - 1
                    If Grid_QueryInfo.Cell(rowIndex, COLS_QUERYINFO.料号PN).Text.ToUpper = queryPn.ToUpper Then
                        findIndex = rowIndex
                        Exit For
                    End If
                Next

                If findIndex > 0 Then
                    Try
                        curCount = Grid_QueryInfo.Cell(findIndex, COLS_QUERYINFO.总数量).Text
                    Catch ex As Exception
                        curCount = 0
                    End Try
                    curCount += MultiCount
                    Grid_QueryInfo.Cell(findIndex, COLS_QUERYINFO.总数量).Text = curCount
                Else
                    Grid_QueryInfo.Rows += 1
                    findIndex = Grid_QueryInfo.Rows - 1
                    Grid_QueryInfo.Cell(findIndex, COLS_QUERYINFO.料号PN).Text = queryPn
                    Grid_QueryInfo.Cell(findIndex, COLS_QUERYINFO.总数量).Text = MultiCount
                End If

                Grid_QueryInfo.Cell(findIndex, COLS_QUERYINFO.max + bomIndex).Text = MultiCount
            Next
        End If
    End Sub

    Private Function GetPNItemInfo(ByVal gridRowIndex As Integer) As Boolean
        Dim 物料名称 As String = ""
        Dim 规格描述 As String = ""
        Dim 单位 As String = ""
        Dim 单重 As Double = 0
        Dim 图片(0) As Byte
        Dim 供应商ID As String = ""

        Grid_QueryInfo.Cell(gridRowIndex, COLS_QUERYINFO.物料名称).Text = ""
        Grid_QueryInfo.Cell(gridRowIndex, COLS_QUERYINFO.规格描述).Text = ""
        Grid_QueryInfo.Cell(gridRowIndex, COLS_QUERYINFO.单位).Text = ""

        Dim result As Boolean = False
        Dim strPn As String = Grid_QueryInfo.Cell(gridRowIndex, COLS_QUERYINFO.料号PN).Text
        If m_PartNumber.QueryItem(strPn, 物料名称, 规格描述, 单位, 单重, 供应商ID) = ERROR_CODE.SUCCESS Then
            Grid_QueryInfo.Cell(gridRowIndex, COLS_QUERYINFO.物料名称).Text = 物料名称
            Grid_QueryInfo.Cell(gridRowIndex, COLS_QUERYINFO.规格描述).Text = 规格描述
            Grid_QueryInfo.Cell(gridRowIndex, COLS_QUERYINFO.单位).Text = 单位

            result = True
        Else
            MsgBox("查询物料信息错误,料号:" & strPn)
        End If

        Return result
    End Function

    Private Sub bt_ExportResult_Click(sender As Object, e As EventArgs) Handles bt_ExportResult.Click
        Dim dlg As New SaveFileDialog()
        dlg.DefaultExt = "xls"
        dlg.Filter = "Excel File|*.xls"
        If dlg.ShowDialog() <> Windows.Forms.DialogResult.OK Then Return

        If Grid_QueryInfo.ExportToExcel(dlg.FileName, True, True) Then
            MsgBox("保存为Excel成功：" & dlg.FileName)
        Else
            MsgBox("保存失败!")
        End If
    End Sub
End Class