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
|