HF-MIS-SYS--LEEHSIEHJUI/WindowsApp1/00、MyModule/SQL金蝶串接.vb

Module SQL金蝶串接
    Friend Sub SQL_染色領料清單(開始日期 As String, 結束日期 As String)
        ConnOpen()
        SQL1 = "SELECT    outDate AS 日期, 
                          CASE WHEN CHARINDEX(' ', Card) > 0 THEN SUBSTRING(Card, 1, CHARINDEX(' ', Card) - 1) ELSE Card END AS 卡号, 
                          CASE WHEN CHARINDEX(' ', Card) > 0 THEN LTRIM(SUBSTRING(Card, CHARINDEX(' ', Card)  + 1, LEN(Card))) ELSE NULL END AS 片数,
                          Rcp_name AS 工艺, CAST(AVG(Weight) AS numeric(10, 1)) AS 重量
                FROM      HT_Retan_Chem_Out
                WHERE    (outDate BETWEEN N'" & 開始日期 & "' AND N'" & 結束日期 & "')
                GROUP BY  outDate, Card, Rcp_name"
        CmdSet_For_DGV()
    End Sub
    Friend Sub SQL_染色領料明細清單(工艺 As String, 日期 As String, 卡号 As String)
        ConnOpen()
        SQL1 = "SELECT    HT_RetanRcp.item AS 项次, HT_RetanRcp.chem AS 化料名称, 
                          CASE WHEN type1 IS NULL THEN '' ELSE HT_Chem_Main.type1 END AS 化工分类, 
                          CASE WHEN type2 IS NULL THEN N'不列印' ELSE HT_Chem_Main.type2 END AS 类别, 
                          HT_RetanRcp.percents AS 百分比, HT_RetanRcp.time AS 时间, HT_RetanRcp.comment AS 备注, 
                          HT_Chem_Main.d1, HT_Chem_Main.d2, HT_Chem_Main.d3, HT_Chem_Main.d4, HT_Chem_Main.d5, 
                          HT_Chem_Main.d6, HT_Chem_Main.d7, HT_Chem_Main.d8, HT_Chem_Main.d9, HT_MSDS_File_1.filename, 
                          CASE WHEN HT_K3_Material.name is NULL THEN HT_RetanRcp.chem ELSE HT_K3_Material.name END as 系统品名, 
                          HT_K3_Material.code as 系统代码
                FROM      HT_K3_Material RIGHT OUTER JOIN
                          HT_Rcp_ChemName_table ON HT_K3_Material.code = HT_Rcp_ChemName_table.code AND 
                          HT_K3_Material.name = HT_Rcp_ChemName_table.sys_chem_name LEFT OUTER JOIN
                          HT_Chem_Main LEFT OUTER JOIN
                                (SELECT  filename, SUBSTRING(filename, 1, CHARINDEX('_', filename) - 1) AS no
                                 FROM    HT_MSDS_File) AS HT_MSDS_File_1 ON HT_Chem_Main.no = HT_MSDS_File_1.no ON 
                          HT_Rcp_ChemName_table.sys_chem_name = HT_Chem_Main.sys_name RIGHT OUTER JOIN
                          HT_RetanRcp ON HT_Rcp_ChemName_table.rcp_chem_name = HT_RetanRcp.chem
                WHERE     HT_RetanRcp.name = N'" & 工艺 & "' AND HT_RetanRcp.no LIKE N'%" & 卡号 & "%' AND
                          HT_RetanRcp.chem NOT LIKE N''
                ORDER BY  HT_RetanRcp.rcp_no DESC, 项次"   ' AND HT_RetanRcp.date = N'" & 日期 & "'
        CmdSet_For_DGV()
    End Sub
    Friend Sub SQL_塗飾領料清單(開始日期 As String, 結束日期 As String)
        ConnOpen()
        SQL1 = "SELECT    outdate AS 日期, SUBSTRING(card, 1, CHARINDEX(' ', card) - 1) AS 卡号, trim(SUBSTRING(card, CHARINDEX(' ', card) + 1, LEN(card))) AS 片数,
                          rcp_name AS 工艺,CAST(AVG(weight) AS numeric(10,1)) AS 重量 
                FROM      HT_Retan_Chem_Out 
                WHERE     outdate BETWEEN '" & 開始日期 & "' AND '" & 結束日期 & "' 
                GROUP BY  outdate, card, rcp_name"
        CmdSet_For_DGV()
    End Sub
    Friend Sub SQL_即時庫存主表()
        ConnOpen()
        SQL1 = "SELECT    hf_00_k3_realtime_stock.material_number AS 物料代码, HT_K3_Material.name AS 品名, 
                          CAST(SUM(hf_00_k3_realtime_stock.qty) AS numeric(10, 1)) AS 即时库存
                FROM      hf_00_k3_realtime_stock LEFT OUTER JOIN HT_K3_Material ON hf_00_k3_realtime_stock.material_number = HT_K3_Material.code
                GROUP BY  hf_00_k3_realtime_stock.material_number, HT_K3_Material.name
                ORDER BY  SUM(hf_00_k3_realtime_stock.qty) DESC"
        CmdSet_For_DGV()
    End Sub
    Friend Sub SQL_即時庫存明細(物料代码 As String)
        ConnOpen()
        SQL1 = "SELECT hf_00_k3_realtime_stock.stockid_name as 仓库,  hf_00_k3_realtime_stock.material_number AS 物料代码, HT_K3_Material.name AS 品名, 
                       CAST(hf_00_k3_realtime_stock.qty AS numeric(10, 1)) AS 即时库存
                FROM   hf_00_k3_realtime_stock LEFT OUTER JOIN HT_K3_Material ON hf_00_k3_realtime_stock.material_number = HT_K3_Material.code 
                WHERE  hf_00_k3_realtime_stock.material_number = '" & 物料代码 & "'"

        CmdSet_For_DGV()
    End Sub
End Module