350 lines
13 KiB
VB.net
350 lines
13 KiB
VB.net
Public Class CPartNumber
|
||
Dim m_SQL As New CSQLInterface
|
||
Public Shared cst_TABLE_NAME As String = "料号表"
|
||
Public Shared m_COLS_NAME() As String = {
|
||
"序号",
|
||
"料号PN",
|
||
"物料名称",
|
||
"规格描述",
|
||
"单位",
|
||
"单重",
|
||
"图片",
|
||
"供应商ID"
|
||
}
|
||
Public Enum COLS
|
||
序号
|
||
料号PN
|
||
物料名称
|
||
规格描述
|
||
单位
|
||
单重
|
||
图片
|
||
供应商ID
|
||
max
|
||
End Enum
|
||
|
||
|
||
'权限
|
||
Public Shared AccessRight As Integer = RIGHTS.NONE
|
||
|
||
|
||
Public Function AddItem(ByVal 料号PN As String,
|
||
ByVal 物料名称 As String,
|
||
ByVal 规格描述 As String,
|
||
ByVal 单位 As String,
|
||
ByVal 单重 As Double,
|
||
ByVal 供应商ID As String
|
||
) As ERROR_CODE
|
||
If CBool(AccessRight And RIGHTS.WRITE) = False Then
|
||
Return ERROR_CODE.NORIGHT
|
||
End If
|
||
|
||
'"INSERT INTO [dbo].[TABLE1] ([11],[22],[33],[44],[55]) VALUES ('11','2','3','4','5')"
|
||
Dim strSql As String = "INSERT INTO `" & cst_TABLE_NAME & "` ("
|
||
'加入列名
|
||
Dim isFirst As Boolean = True
|
||
For i As Integer = COLS.料号PN To COLS.max - 1
|
||
If i <> COLS.图片 Then
|
||
If isFirst = True Then
|
||
isFirst = False
|
||
Else
|
||
strSql &= ","
|
||
End If
|
||
|
||
strSql &= "`" & m_COLS_NAME(i) & "`"
|
||
End If
|
||
Next
|
||
strSql &= ") VALUES ("
|
||
strSql &= "'" & 料号PN & "',"
|
||
strSql &= "'" & 物料名称 & "',"
|
||
strSql &= "'" & 规格描述 & "',"
|
||
strSql &= "'" & 单位 & "',"
|
||
strSql &= "'" & 单重 & "',"
|
||
strSql &= "'" & 供应商ID & "'"
|
||
strSql &= ")"
|
||
|
||
If SQL_ExeCommand(COL_RIGHTS.料号管理, strSql) = True Then
|
||
Return ERROR_CODE.SUCCESS
|
||
End If
|
||
|
||
Return ERROR_CODE.ACCESS
|
||
End Function
|
||
|
||
Public Function AddItem(ByVal 料号PN As String,
|
||
ByVal 物料名称 As String,
|
||
ByVal 规格描述 As String,
|
||
ByVal 单位 As String,
|
||
ByVal 单重 As Double,
|
||
ByVal 图片() As Byte,
|
||
ByVal 供应商ID As String
|
||
) As ERROR_CODE
|
||
If CBool(AccessRight And RIGHTS.WRITE) = False Then
|
||
Return ERROR_CODE.NORIGHT
|
||
End If
|
||
|
||
'"INSERT INTO [dbo].[TABLE1] ([11],[22],[33],[44],[55]) VALUES ('11','2','3','4','5')"
|
||
Dim strSql As String = "INSERT INTO `" & cst_TABLE_NAME & "` ("
|
||
'加入列名
|
||
Dim isFirst As Boolean = True
|
||
For i As Integer = COLS.料号PN To COLS.max - 1
|
||
If isFirst = True Then
|
||
isFirst = False
|
||
Else
|
||
strSql &= ","
|
||
End If
|
||
strSql &= "`" & m_COLS_NAME(i) & "`"
|
||
Next
|
||
strSql &= ") VALUES ("
|
||
strSql &= "'" & 料号PN & "',"
|
||
strSql &= "'" & 物料名称 & "',"
|
||
strSql &= "'" & 规格描述 & "',"
|
||
strSql &= "'" & 单位 & "',"
|
||
strSql &= "'" & 单重 & "',"
|
||
strSql &= "@pic,"
|
||
strSql &= "'" & 供应商ID & "'"
|
||
strSql &= ")"
|
||
|
||
Dim ParamName() As String = {"@pic"}
|
||
Dim ParamValue() As Object = {图片}
|
||
|
||
If SQL_ExeCommandWithParamters(COL_RIGHTS.料号管理, strSql, ParamName, ParamValue) = True Then
|
||
Return ERROR_CODE.SUCCESS
|
||
End If
|
||
|
||
Return ERROR_CODE.ACCESS
|
||
End Function
|
||
|
||
'UPDATE `w&t database`.`料号表` SET `物料名称` = '电阻，001',
|
||
'`规格描述` = '10k,0603，1' WHERE `料号表`.`序号` =13;
|
||
Public Function UpdateItem(ByVal 料号PN As String,
|
||
ByVal 物料名称 As String,
|
||
ByVal 规格描述 As String,
|
||
ByVal 单位 As String,
|
||
ByVal 单重 As Double,
|
||
ByVal 图片() As Byte,
|
||
ByVal 供应商ID As String
|
||
) As ERROR_CODE
|
||
If CBool(AccessRight And RIGHTS.WRITE) = False Then
|
||
Return ERROR_CODE.NORIGHT
|
||
End If
|
||
|
||
Dim strSql As String = "UPDATE `" & cst_TABLE_NAME & "` SET "
|
||
'加入列名
|
||
strSql &= "`" & m_COLS_NAME(COLS.物料名称) & "` = '" & 物料名称 & "', "
|
||
strSql &= "`" & m_COLS_NAME(COLS.规格描述) & "` = '" & 规格描述 & "', "
|
||
strSql &= "`" & m_COLS_NAME(COLS.单位) & "` = '" & 单位 & "', "
|
||
strSql &= "`" & m_COLS_NAME(COLS.单重) & "` = '" & 单重 & "', "
|
||
If Not 图片 Is Nothing Then
|
||
strSql &= "`" & m_COLS_NAME(COLS.图片) & "` = @pic, "
|
||
End If
|
||
strSql &= "`" & m_COLS_NAME(COLS.供应商ID) & "` = '" & 供应商ID & "' "
|
||
strSql &= " WHERE " & "`" & m_COLS_NAME(COLS.料号PN) & "` = '" & 料号PN & "' "
|
||
|
||
If Not 图片 Is Nothing Then
|
||
Dim ParamName() As String = {"@pic"}
|
||
Dim ParamValue() As Object = {图片}
|
||
|
||
If SQL_ExeCommandWithParamters(COL_RIGHTS.料号管理, strSql, ParamName, ParamValue) = True Then
|
||
Return ERROR_CODE.SUCCESS
|
||
End If
|
||
Else
|
||
If SQL_ExeCommand(COL_RIGHTS.料号管理, strSql) = True Then
|
||
Return ERROR_CODE.SUCCESS
|
||
End If
|
||
End If
|
||
|
||
Return ERROR_CODE.ACCESS
|
||
End Function
|
||
|
||
Public Function QueryItem(ByVal 料号PN As String,
|
||
ByRef 物料名称 As String,
|
||
ByRef 规格描述 As String,
|
||
ByRef 单位 As String,
|
||
ByRef 单重 As Double,
|
||
ByRef 图片() As Byte,
|
||
ByRef 供应商ID As String
|
||
) As ERROR_CODE
|
||
If CBool(AccessRight And RIGHTS.READ) = False Then
|
||
Return ERROR_CODE.NORIGHT
|
||
End If
|
||
|
||
Dim strSQL As String = "SELECT * FROM " & cst_TABLE_NAME & " WHERE "
|
||
strSQL &= m_COLS_NAME(COLS.料号PN) & " = '" & 料号PN & "'"
|
||
|
||
Dim rValueTable(0) As Object
|
||
If SQL_DataReader(COL_RIGHTS.料号管理, strSQL, rValueTable) = True Then
|
||
物料名称 = rValueTable(COLS.物料名称)
|
||
规格描述 = rValueTable(COLS.规格描述)
|
||
单位 = rValueTable(COLS.单位)
|
||
单重 = rValueTable(COLS.单重)
|
||
'图片 = rValueTable(COLS.图片)
|
||
供应商ID = rValueTable(COLS.供应商ID)
|
||
|
||
Return ERROR_CODE.SUCCESS
|
||
End If
|
||
|
||
Return ERROR_CODE.ACCESS
|
||
End Function
|
||
|
||
Public Function QueryItem(ByVal 料号PN As String,
|
||
ByRef 物料名称 As String,
|
||
ByRef 规格描述 As String,
|
||
ByRef 单位 As String,
|
||
ByRef 单重 As Double,
|
||
ByRef 供应商ID As String
|
||
) As ERROR_CODE
|
||
If CBool(AccessRight And RIGHTS.READ) = False Then
|
||
Return ERROR_CODE.NORIGHT
|
||
End If
|
||
|
||
Dim strSQL As String = "SELECT * FROM " & cst_TABLE_NAME & " WHERE "
|
||
strSQL &= m_COLS_NAME(COLS.料号PN) & " = '" & 料号PN & "'"
|
||
|
||
Dim rValueTable(0) As Object
|
||
If SQL_DataReader(COL_RIGHTS.料号管理, strSQL, rValueTable) = True Then
|
||
物料名称 = rValueTable(COLS.物料名称)
|
||
规格描述 = rValueTable(COLS.规格描述)
|
||
单位 = rValueTable(COLS.单位)
|
||
单重 = rValueTable(COLS.单重)
|
||
供应商ID = rValueTable(COLS.供应商ID)
|
||
|
||
Return ERROR_CODE.SUCCESS
|
||
End If
|
||
|
||
Return ERROR_CODE.ACCESS
|
||
End Function
|
||
|
||
Public Function QueryItemIndex(ByVal 料号PN As String,ByRef 序号 As Integer) As ERROR_CODE
|
||
If CBool(AccessRight And RIGHTS.READ) = False Then
|
||
Return ERROR_CODE.NORIGHT
|
||
End If
|
||
|
||
Dim strSQL As String = "SELECT `" & m_COLS_NAME(COLS.序号) & "` " &
|
||
" FROM " & cst_TABLE_NAME & " WHERE "
|
||
strSQL &= m_COLS_NAME(COLS.料号PN) & " = '" & 料号PN & "'"
|
||
|
||
Dim rValueTable(0) As Object
|
||
If SQL_DataReader(COL_RIGHTS.料号管理, strSQL, rValueTable) = True Then
|
||
Try
|
||
序号 = rValueTable(0)
|
||
Return ERROR_CODE.SUCCESS
|
||
Catch ex As Exception
|
||
|
||
End Try
|
||
End If
|
||
|
||
Return ERROR_CODE.ACCESS
|
||
End Function
|
||
|
||
Public Function QueryInformation(ByRef Count As Integer, ByRef minIndex As Integer, ByRef maxIndex As Integer
|
||
) As ERROR_CODE
|
||
If CBool(AccessRight And RIGHTS.READ) = False Then
|
||
Return ERROR_CODE.NORIGHT
|
||
End If
|
||
|
||
Dim strSQL As String = "SELECT " &
|
||
"COUNT(`" & m_COLS_NAME(COLS.序号) & "`), " &
|
||
"MIN(`" & m_COLS_NAME(COLS.序号) & "`), " &
|
||
"MAX(`" & m_COLS_NAME(COLS.序号) & "`) " &
|
||
" FROM `" & cst_TABLE_NAME & "`"
|
||
|
||
Dim rValueTable(0) As Object
|
||
If SQL_DataReader(COL_RIGHTS.料号管理, strSQL, rValueTable) = True Then
|
||
Try
|
||
Count = rValueTable(0)
|
||
minIndex = rValueTable(1)
|
||
maxIndex = rValueTable(2)
|
||
Return ERROR_CODE.SUCCESS
|
||
Catch ex As Exception
|
||
|
||
End Try
|
||
End If
|
||
|
||
Return ERROR_CODE.ACCESS
|
||
End Function
|
||
|
||
|
||
Public Function CheckPartNumber(ByVal 料号PN As String, ByRef isExist As Boolean) As ERROR_CODE
|
||
If CBool(AccessRight And RIGHTS.READ) = False Then
|
||
Return ERROR_CODE.NORIGHT
|
||
End If
|
||
|
||
Dim strSql As String = "SELECT `" & m_COLS_NAME(COLS.料号PN) & "` FROM `" & cst_TABLE_NAME & "` WHERE "
|
||
strSql &= "`" & m_COLS_NAME(COLS.料号PN) & "` COLLATE gbk_chinese_ci ='" & 料号PN & "'"
|
||
|
||
Dim rTable As New System.Data.DataTable
|
||
If SQL_Query(COL_RIGHTS.料号管理, strSql, rTable) = True Then
|
||
isExist = False
|
||
|
||
If rTable.Rows.Count > 0 Then
|
||
'If rTable.Rows(0).Item(0) = 料号PN Then
|
||
isExist = True
|
||
'End If
|
||
End If
|
||
|
||
Return ERROR_CODE.SUCCESS
|
||
End If
|
||
|
||
Return ERROR_CODE.ACCESS
|
||
End Function
|
||
|
||
'=================================================================================================
|
||
'获取当前库位库存
|
||
Public Function GetStockSum(ByVal 仓库 As String, ByVal 库位 As String, ByRef isExist As Boolean) As Integer
|
||
|
||
If CBool(AccessRight And RIGHTS.READ) = False Then
|
||
Return ERROR_CODE.NORIGHT
|
||
End If
|
||
|
||
Dim strSql As String = "SELECT `库位库存` FROM `原料库存表` WHERE "
|
||
strSql &= "`仓库` COLLATE gbk_chinese_ci ='" & 仓库 & "' AND `库位` COLLATE gbk_chinese_ci ='" & 库位 & "'"
|
||
|
||
Dim rTable As New System.Data.DataTable
|
||
If SQL_Query(COL_RIGHTS.原料库存管理, strSql, rTable) = True Then
|
||
isExist = False
|
||
|
||
If rTable.Rows.Count > 0 Then
|
||
isExist = True
|
||
Return rTable.Rows(rTable.Rows.Count - 1).Item(0).ToString()
|
||
End If
|
||
End If
|
||
Return 0
|
||
End Function
|
||
'=================================================================================================
|
||
|
||
Public Function GetAddItemCmd(ByVal 料号PN As String,
|
||
ByVal 物料名称 As String,
|
||
ByVal 规格描述 As String,
|
||
ByVal 单位 As String,
|
||
ByVal 单重 As Double,
|
||
ByVal 供应商ID As String
|
||
) As String
|
||
|
||
|
||
'"INSERT INTO [dbo].[TABLE1] ([11],[22],[33],[44],[55]) VALUES ('11','2','3','4','5')"
|
||
Dim strSql As String = "INSERT INTO `" & cst_TABLE_NAME & "` ("
|
||
'加入列名
|
||
Dim isFirst As Boolean = True
|
||
For i As Integer = COLS.料号PN To COLS.max - 1
|
||
If i <> COLS.图片 Then
|
||
If isFirst = True Then
|
||
isFirst = False
|
||
Else
|
||
strSql &= ","
|
||
End If
|
||
|
||
strSql &= "`" & m_COLS_NAME(i) & "`"
|
||
End If
|
||
Next
|
||
strSql &= ") VALUES ("
|
||
strSql &= "'" & 料号PN & "',"
|
||
strSql &= "'" & 物料名称 & "',"
|
||
strSql &= "'" & 规格描述 & "',"
|
||
strSql &= "'" & 单位 & "',"
|
||
strSql &= "'" & 单重 & "',"
|
||
strSql &= "'" & 供应商ID & "'"
|
||
strSql &= ")"
|
||
Return strSql
|
||
End Function
|
||
End Class
|