HF-MIS-SYS--LEEHSIEHJUI/WindowsApp1/00、MyModule/SQL業務管理.vb

Module SQL業務管理
    Friend Sub SQL_客戶群組()
        ConnOpen()
        SQL1 = "SELECT    case when remark='' then N'无品牌' else remark end as 品牌,sum(qty) as 尺数,count(*) as 笔数, cast(sum(qty)/count(*) as numeric(10,0)) as 平均 
                FROM      hf_00_orders_detail
                GROUP BY  remark order by remark desc"
        CmdSet_For_DGV()
    End Sub
    Friend Sub SQL_訂單資料()
        ConnOpen() : SQL1 = "select bill_date as 下单日期,customer_name as 客户,bill_no as 订单号 from hf_00_orders_main" : CmdSet_For_DGV()
    End Sub
    Friend Sub SQL_訂單明細(品牌 As String)
        ConnOpen()
        SQL1 = "SELECT    hf_00_orders_main.bill_date AS 下单日期, hf_00_orders_detail.delivery_date AS 交期, hf_00_orders_main.customer_name AS 客户名, 
                          hf_00_orders_detail.comment AS 品名, hf_00_orders_detail.material_name AS 分类, hf_00_orders_detail.stock_name AS 仓库, 
                          hf_00_orders_detail.card AS 卡号, hf_00_orders_detail.qty AS 订单数量, hf_00_orders_detail.price AS 单价, hf_00_orders_detail.remark AS 品牌, 
                          hf_00_orders_main.bill_no as 金蝶订单号
                FROM      hf_00_orders_detail RIGHT OUTER JOIN hf_00_orders_main ON hf_00_orders_detail.order_no = hf_00_orders_main.bill_no 
                WHERE     hf_00_orders_detail.remark LIKE N'" & 品牌 & "'
                ORDER BY  下单日期 DESC"
        CmdSet_For_DGV()
    End Sub
    Friend Sub SQL_訂單生產(卡号 As String)
        ConnOpen()
        SQL1 = "SELECT    createTime As 作业时间, procedureName As 作业名称, workshopName As 车间,accountname As 班组长, orderNumber As 流程卡号, remark As 备注
                FROM      HT_Cloud_WorkShop   WHERE  orderNumber LIKE N'" & 卡号 & "'  ORDER BY  作业时间 "
        CmdSet_For_DGV()
    End Sub
    Friend Sub SQL_銷售客戶()
        ConnOpen() : SQL1 = "SELECT date as 出货日期,cust as 客户,sum(qty) as 尺数 FROM hf_00_shipped GROUP BY date , cust ORDER BY date DESC, cust" : CmdSet_For_DGV()
    End Sub
    Friend Sub SQL_銷售客戶_尺數()
        ConnOpen() : SQL1 = "SELECT cust as 客户,sum(qty) as 尺数 FROM hf_00_shipped GROUP BY cust ORDER BY sum(qty) DESC" : CmdSet_For_DGV()
    End Sub
    Friend Sub SQL_銷售客戶_出貨明細(條件 As String)
        ConnOpen()
        SQL1 = "SELECT    date as 出货日期,cust as 客户, name as 品名,color as 颜色,thick as 厚度,lot as 流程卡号,qty as 出货数量,order_no as 订单号,work_no as 型体号 
                FROM      hf_00_shipped " & 條件 & "
                ORDER BY  date DESC"
        CmdSet_For_DGV()
    End Sub
    Friend Sub SQL_銷售客戶_生產明細(流程卡号 As String)
        ConnOpen()
        SQL1 = "SELECT   createTime AS 作业时间, procedureName AS 作业名称, workshopName AS 车间,accountname as 班组长, ordername as 品名, remark AS 备注
                FROM      HT_Cloud_WorkShop  WHERE  orderNumber = N'" & 流程卡号 & "'  ORDER BY  作业时间 "
        CmdSet_For_DGV()
    End Sub
End Module