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