391 lines
15 KiB
VB.net
391 lines
15 KiB
VB.net
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 |