Public Class Form_QueryStorage
    Dim m_Table As New System.Data.DataTable
    Dim 仓库列表 As New ArrayList
    Dim m_PartNumber As New CPartNumber
    Dim m_InitOver As Boolean = False

    Public Enum COLS
        序号
        料号
        物料名称
        规格描述
        总数量
        START
    End Enum

    Private Function GetStoreList() As Boolean
        Dim m_Store As New CStore

        仓库列表.Clear()

        m_Table.Rows.Clear()
        m_Table.Columns.Clear()
        If m_Store.QueryAll(m_Table) = ERROR_CODE.SUCCESS Then
            For i As Integer = 0 To m_Table.Rows.Count - 1
                仓库列表.Add(m_Table.Rows(i).Item(CStore.COLS.仓库名称))
            Next
            Return True
        End If

        Return False
    End Function

    Private Sub Set_GridTitles()
        Grid1.Rows = 500
        Grid1.Cols = 5
        Grid1.Cell(0, COLS.序号).Text = "序号"
        Grid1.Cell(0, COLS.料号).Text = "料号"
        Grid1.Cell(0, COLS.物料名称).Text = "物料名称"
        Grid1.Cell(0, COLS.规格描述).Text = "规格描述"
        Grid1.Cell(0, COLS.总数量).Text = "总数量"
        Dim col As Integer = COLS.START
        For Each pItem As String In 仓库列表
            Grid1.Cols += 1
            Grid1.Cell(0, col).Text = pItem
            col += 1
            'V1.6.0.0 Momo: 查库存同时查库位
            Grid1.Cols += 1
            Grid1.Cell(0, col).Text = "库位" 'V1.6.0.0 Momo: 查库存同时查库位
            col += 1

        Next

        Grid1.FixedRows = 1
        Grid1.FixedCols = 1
    End Sub

    Private Sub Form_QueryStorage_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

        Me.Width = My.Settings.FORM_QUERYSTORAGE_WIDTH
        Me.Height = My.Settings.FORM_QUERYSTORAGE_HEIGHT
        Me.Left = My.Settings.FORM_QUERYSTORAGE_X
        Me.Top = My.Settings.FORM_QUERYSTORAGE_Y
        Me.WindowState = My.Settings.FORM_QUERYSTORAGE_WINSTATE


        If CBool(Current_Rights(COL_RIGHTS.料号管理) And RIGHTS.READ) = False OrElse
            CBool(Current_Rights(COL_RIGHTS.原料库存管理) And RIGHTS.READ) = False Then
            bt_Query.Enabled = False
            bt_Add.Enabled = False
        End If

        If GetStoreList() = False Then
            MsgBox("无法获取仓库列表，请检查网络连接！")
        End If

        Set_GridTitles()

        CGirdInfo.LoadGridInfo("物料库存查询", Grid1)

        m_InitOver = True
    End Sub

    Private Sub bt_Add_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles bt_Add.Click

        Grid1.NewFile()
        Set_GridTitles()

        If Not m_Table Is Nothing Then
            m_Table.Dispose()
        End If
        m_Table = New DataTable

        QueryData_Quick()

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

        'Grid1.SetDataBinding(m_Table, "", True, 1)
        If m_Table.Rows.Count < 500 Then
            Grid1.Rows = 500
        Else
            Grid1.Rows = m_Table.Rows.Count
        End If

        For i As Integer = 0 To m_Table.Rows.Count - 1
            Grid1.Cell(i + 1, COLS.料号).Text = m_Table.Rows(i).Item(0)
        Next

        For i As Integer = 1 To Grid1.Rows - 1
            Grid1.Cell(i, 0).Text = i
        Next

        CGirdInfo.LoadGridInfo("料号查询", Grid1)
        Grid1.AllowUserSort = True

        'Grid1.Locked = True
    End Sub

    Private Sub bt_Clear_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles bt_Clear.Click
        Grid1.Rows = 1
    End Sub

    Private Sub bt_Delete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles bt_Delete.Click
        If Grid1.Selection.FirstRow >= 1 Then
            Grid1.RemoveItem(Grid1.Selection.FirstRow)
            Grid1.Selection.ClearAll()
        End If
    End Sub

    Private Function QueryStorageCount(ByVal storeIndex As Integer, 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 & "' "
        strSelect &= "AND `" & CMaterialInOut.m_COLS_NAME(CMaterialInOut.COLS.仓库) & "` = '" & 仓库列表.Item(storeIndex) & "' "

        '查询入库数量
        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 Function QueryStorageLocation(ByVal storeIndex As Integer, ByVal PartNumber As String, ByRef dataTable As System.Data.DataTable) As Boolean
        Dim strSelect As String = "SELECT DISTINCT `" & 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 & "' "
        strSelect &= "AND `" & CMaterialInOut.m_COLS_NAME(CMaterialInOut.COLS.仓库) & "` = '" & 仓库列表.Item(storeIndex) & "' "

        '查询入库数量
        Dim result As Boolean = False
        Dim strSQL As String = strSelect
        strSQL &= "AND `" & CMaterialInOut.m_COLS_NAME(CMaterialInOut.COLS.库位库存) & "` > '0'"
        dataTable.Rows.Clear()
        dataTable.Columns.Clear()
        If SQL_Query(COL_RIGHTS.原料库存管理, strSQL, dataTable) = True Then
            result = True
        End If

        Return result
    End Function

    Private Sub bt_Query_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles bt_Query.Click
        lbl_Result.Text = ""
        Grid1.AutoRedraw = False

        '检测查询列是否有效
        If Grid1.Rows > 1 Then
            Dim col As Integer
            Dim row As Integer

            '清除每行的颜色
            For row = 1 To Grid1.Rows - 1
                Grid1.Cell(row, 1).BackColor = Grid1.Cell(0, 0).BackColor
            Next

            Dim result As Boolean
            Dim rCount As Integer = 0
            Dim TotalCount As Integer = 0
            Dim OK_Cnt As Integer = 0
            Dim Fail_Cnt As Integer = 0

            Dim m_物料名称 As String = ""
            Dim m_规格描述 As String = ""
            Dim m_单位 As String = ""
            Dim m_单重 As Double = 0.0
            Dim m_供应商ID As String = ""

            For row = 1 To Grid1.Rows - 1
                Grid1.Cell(row, 0).Text = row
                result = False
                TotalCount = 0
                col = COLS.START
                For i As Integer = 0 To 仓库列表.Count - 1
                    'V1.6.0.0 Momo 增加对空白栏的检测
                    Dim temp_tb_PN = Grid1.Cell(row, COLS.料号).Text
                    If temp_tb_PN.Length > 0 Then
                        '检测料号是否有效
                        Dim isExist As Boolean = True
                        If m_PartNumber.CheckPartNumber(temp_tb_PN, isExist) = ERROR_CODE.SUCCESS Then
                            If isExist = False Then
                                'MsgBox("当前料号不存在: " & tb_PN.Text)
                                result = False
                            Else
                                '查询料号信息
                                m_物料名称 = ""
                                m_规格描述 = ""
                                m_单位 = ""
                                m_单重 = 0.0
                                m_供应商ID = ""
                                If m_PartNumber.QueryItem(temp_tb_PN, m_物料名称, m_规格描述, m_单位, m_单重, m_供应商ID) = ERROR_CODE.SUCCESS Then
                                    Grid1.Cell(row, COLS.物料名称).Text = m_物料名称
                                    Grid1.Cell(row, COLS.规格描述).Text = m_规格描述
                                End If

                                If QueryStorageCount(i, Grid1.Cell(row, COLS.料号).Text, rCount) = True Then
                                    Grid1.Cell(row, col).Text = rCount
                                    TotalCount += rCount
                                    Grid1.Cell(row, 1).ForeColor = Color.Green
                                    result = True
                                Else
                                    Grid1.Cell(row, 1).ForeColor = Color.Red
                                    result = False
                                End If

                                Dim strLoc As String = ""
                                If QueryStorageLocation(i, Grid1.Cell(row, COLS.料号).Text, m_Table) = True Then
                                    If m_Table.Rows.Count > 0 Then
                                        For k As Integer = 0 To m_Table.Rows.Count - 1
                                            Dim isExit As Boolean
                                            If m_PartNumber.GetStockSum(仓库列表.Item(i), m_Table.Rows(k).Item(0), isExit) <= 0 Then '若库位库存量为0则跳过本次循环
                                                Continue For
                                            End If
                                            If strLoc.Length > 0 Then
                                                strLoc &= ","
                                            End If
                                            strLoc &= m_Table.Rows(k).Item(0)
                                        Next
                                    End If
                                End If
                                Grid1.Cell(row, col + 1).Text = strLoc
                                Grid1.Cell(row, 4).Font = New Font("Arial", 8)
                                Grid1.Cell(row, 4).FontSize = 11
                                Grid1.Cell(row, 4).Alignment = FlexCell.AlignmentEnum.CenterCenter
                                Grid1.Cell(row, 4).FontBold = True
                                Grid1.Cell(row, 4).ForeColor = Color.Blue

                                Grid1.Cell(row, 5).Alignment = FlexCell.AlignmentEnum.CenterCenter
                                Grid1.Cell(row, 6).Alignment = FlexCell.AlignmentEnum.CenterCenter
                                Grid1.Cell(row, 7).Alignment = FlexCell.AlignmentEnum.CenterCenter
                                Grid1.Cell(row, 8).Alignment = FlexCell.AlignmentEnum.CenterCenter

                            End If
                        Else
                            'MsgBox("访问数据库失败: 请检查网络连接或联系管理员!")
                            result = False
                        End If
                    Else
                        result = False
                    End If
                    col += 2
                Next i

                If result = True Then
                    Grid1.Cell(row, COLS.总数量).Text = TotalCount
                    OK_Cnt += 1
                Else
                    Fail_Cnt += 1
                End If
            Next row

            lbl_Result.Text = "查询完成: 共" & OK_Cnt + Fail_Cnt & "条, 成功" & OK_Cnt & "条"
        End If

        'Format

        Grid1.Refresh()
        Grid1.AutoRedraw = True
    End Sub

    Private Sub Grid1_ColWidthChange(ByVal Sender As System.Object, ByVal e As FlexCell.Grid.ColWidthChangeEventArgs) Handles Grid1.ColWidthChange
        CGirdInfo.SaveGirdInfo("物料库存查询", Grid1)
    End Sub

    Private Sub Form_QueryStorage_Resize(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Resize
        If m_InitOver = True Then
            My.Settings.FORM_QUERYSTORAGE_WIDTH = Me.Width
            My.Settings.FORM_QUERYSTORAGE_HEIGHT = Me.Height
            My.Settings.FORM_QUERYSTORAGE_WINSTATE = Me.WindowState
            My.Settings.Save()
        End If
    End Sub

    Private Sub Form_QueryStorage_LocationChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.LocationChanged
        If m_InitOver = True Then
            My.Settings.FORM_QUERYSTORAGE_X = Me.Left
            My.Settings.FORM_QUERYSTORAGE_Y = Me.Top
            My.Settings.Save()
        End If
    End Sub
    ' V1.6.0.0 Momo 库存页面增加模糊查询
    Private Sub QueryData_Quick()
        Dim strSQL As String = "SELECT `" &
                                CPartNumber.m_COLS_NAME(CPartNumber.COLS.料号PN) & "`"
        strSQL &= " FROM `" & CPartNumber.cst_TABLE_NAME & "`"

        If tb_PN.Text.Length > 0 Then
            strSQL &= " WHERE "

            Dim strSplit() As String
            Dim firstLike As Boolean = True
            If tb_PN.Text.Length > 0 Then
                strSplit = Split(tb_PN.Text, ",")
                For i As Integer = 0 To strSplit.Length - 1
                    If firstLike = True Then
                        firstLike = False
                    Else
                        strSQL &= " AND "
                    End If
                    strSQL &= "`" & CPartNumber.m_COLS_NAME(CPartNumber.COLS.料号PN) & "`"
                    strSQL &= " COLLATE gbk_chinese_ci "
                    strSQL &= " LIKE '%"
                    strSQL &= form_QuerySim.GetLikeString(strSplit(i)) & "%'"
                    strSQL &= " escape '/'"
                Next
            End If
        End If
        SQL_Query(COL_RIGHTS.料号管理, strSQL, m_Table)
    End Sub

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

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