HEGII.ss.mes.check/wwwroot/main/Assemble/api/GetBOMData.ashx

<%@ WebHandler Language="C#" Class="GetBOMData" %>


using System;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.SessionState;
using System.Web.Configuration;
using System.Collections;
using System.Collections.Generic;
using System.Collections.Specialized;
using System.Configuration;

using Newtonsoft.Json;
using Newtonsoft.Json.Linq;
using Curtain.DataAccess;
using Curtain.Log;
using DK.XuWei.WebMes;


/// <summary>
/// 获取 预检数据
/// xuwei 2020-06-11
/// </summary>
public class GetBOMData : IHttpHandler, IReadOnlySessionState
{
    //SAP服务器配置参数
    public static string sapBOMUrl = ConfigurationManager.AppSettings["SapBOMUrl"].ToString();
    public static string sapUserInfo = ConfigurationManager.AppSettings["SapUserInfo"].ToString();

    public void ProcessRequest(HttpContext context)
    {
        DataRow[] rows = null;
        string datuv = System.DateTime.Now.Date.ToString("yyyyMMdd");
        using (IDataAccess conn = DataAccess.Create())
        {
            DataTable dtMatnr = conn.ExecuteDatatable(@"
                SELECT GDD.MATERIALCODE AS MATNR,
                       DECODE(GDD.TESTMOULDFLAG, 0, 'C', 1, 'Y', '-') AS ZSCMS,
                       CASE
                           WHEN (INSTR(GDD.GROUTINGLINECODE, 'A') = 1 OR INSTR(GDD.GROUTINGLINECODE, 'D') = 1)
                                AND INSTR(GT.GOODSTYPECODE, '001002') = 1 THEN
                            1
                           WHEN (INSTR(GDD.GROUTINGLINECODE, 'B') = 1 OR INSTR(GDD.GROUTINGLINECODE, 'D') = 1)
                                AND INSTR(GT.GOODSTYPECODE, '001001') = 1 THEN
                            2
                           WHEN INSTR(GDD.GROUTINGLINECODE, 'C') = 1 THEN
                            3
                           ELSE
                            9
                       END AS WORKSHOP
                  FROM TP_PM_GROUTINGDAILYDETAIL GDD
                 INNER JOIN TP_MST_GOODS G
                    ON G.GOODSID = GDD.GOODSID
                 INNER JOIN TP_MST_GOODSTYPE GT
                    ON GT.GOODSTYPEID = G.GOODSTYPEID
                 WHERE GDD.BARCODE = @BARCODE@ ",
                new CDAParameter("BARCODE", context.Request["barCode"])
            );

            // 如果有logoid,就是换商标，重新查一下
            if (context.Request["logoId"] != null)
            {
                object newMatnr = conn.ExecuteScalar(@"
                    SELECT T.MATERIALCODE
                      FROM (SELECT G.GOODSID,
                                   G.LOGOID,
                                   G.MATERIALCODE
                              FROM TP_MST_GOODS G
                            UNION ALL
                            SELECT S.GOODSID,
                                   S.LOGOID,
                                   S.MATERIALCODE
                              FROM TP_MST_GOODSLOGOSAP S) T
                     WHERE T.LOGOID = @LOGOID@
                       AND EXISTS (SELECT 1
                              FROM TP_PM_GROUTINGDAILYDETAIL G
                             WHERE G.GOODSID = T.GOODSID
                               AND G.BARCODE = @BARCODE@) ",

                    new CDAParameter("LOGOID", context.Request["logoId"]),
                    new CDAParameter("BARCODE", context.Request["barCode"])
                );
                dtMatnr.Rows[0]["MATNR"] = newMatnr;
            }

            object procedurename = conn.ExecuteScalar(@"
                SELECT PROCEDURENAME
                  FROM TP_PC_PROCEDURE
                 WHERE PROCEDUREID = @PROCEDUREID@ ",
                new CDAParameter("PROCEDUREID", context.Request["procedureId"])
            );

            #region 直接查bom
            //DataTable dtBOMDetail = conn.ExecuteDatatable(@"
            //SELECT PB.MATNR,
            //       PBD.IDNRK,
            //       PBD.NAME AS IDNRKNAME,
            //       PBD.MEINS,
            //       PBD.MENGE,
            //       '9999' AS CHARG,
            //       '2420' AS LGORT
            //  FROM TP_MST_PACKINGBOMDETAIL PBD
            // INNER JOIN TP_MST_PACKINGBOM PB
            //    ON PB.PACKINGBOMID = PBD.PACKINGBOMID
            // WHERE PB.VERID = 'V100'
            //   AND PB.MATNR = @MATNR@ ",
            //    new CDAParameter("MATNR", dtMatnr.Rows[0]["MATNR"])
            //);
            //context.Response.Write(new JsonResult() { success = true, message = "操作成功！", rows = dtBOMDetail }.ToJson()); 
            #endregion

            #region 调实时bom接口，查数据
            //Hashtable pars = new Hashtable();
            //pars.Add("MATNR", dtMatnr.Rows[0]["MATNR"]);  // 物料
            //pars.Add("WERKS", "5000");                    // 工厂
            //pars.Add("ZSCS", "T");                        // 生产工艺
            //pars.Add("ZSCCJ", dtMatnr.Rows[0]["WORKSHOP"]); // 生产车间
            //pars.Add("ZSCMS", dtMatnr.Rows[0]["ZSCMS"]);  // 生产模式
            //pars.Add("ZJDNU", "60");                      // 节点
            //pars.Add("DATUV", datuv);                     // 查询日期
            //pars.Add("EMENG", 1);                         // 需求数量

            //Hashtable item = new Hashtable();
            //item.Add("item", pars);

            //Hashtable body = new Hashtable();
            //body.Add("T_INPUT", item);
            //string json = JsonConvert.SerializeObject(body);

            ////string url = "http://hgs4podev.hegii.com:50200/RESTAdapter/DKMES/ZPPFM039";// 测试
            ////string userName = "hgsapdk:Sapdk#240";    // 测试

            //string message = JsonClient.PostData2(sapBOMUrl, json, "POST", sapUserInfo);

            //JObject returnObj = JsonConvert.DeserializeObject<JObject>(message);
            //JObject output = returnObj["T_OUTPUT"] as JObject;
            //JArray array = output["item"] as JArray;
            //DataTable dtIdnrk = ConvertToDataTable(array);

            //if ("E".Equals(dtIdnrk.Rows[0]["ZTYPE"] + ""))
            //{
            //    context.Response.Write(new JsonResult() { success = false, message = dtIdnrk.Rows[0]["MESSAGE"] + "" }.ToJson());
            //    return;
            //}

            DataTable dtIdnrk = conn.ExecuteDatatable(@"
            SELECT P.MATNR,
                   PD.IDNRK,
                   PD.NAME AS MAKTX,
                   PD.MENGE,
                   PD.IDNRKTYPE,
                   PD.MEINS
              FROM TP_MST_PACKINGBOM P
             INNER JOIN TP_MST_PACKINGBOMDETAIL PD
                ON PD.PACKINGBOMID = P.PACKINGBOMID
             WHERE INSTR(PD.NAME, '半成品') = 0 
               AND P.MATNR = @MATNR@ ",
                new CDAParameter("MATNR", dtMatnr.Rows[0]["MATNR"])
            );

            // 过滤当前工序需要的组件
            DataTable dtIdnrkType = conn.ExecuteDatatable(@"
            SELECT IDNRKTYPE,SCANFLAG
              FROM TP_PC_PROCEDUREIDNRKTYPE
             WHERE PROCEDUREID = @PROCEDUREID@ ",
                new CDAParameter("PROCEDUREID", context.Request["procedureId"])
            );

            // 查询当前工序是否已经绑定过组件，并且不是上一道工序
            DataTable isIdnrkData = conn.ExecuteDatatable(@"
            SELECT DISTINCT PD.BARCODE FROM TP_PM_PRODUCTIONDATA PD
               LEFT JOIN TP_PM_INPRODUCTION I ON PD.GROUTINGDAILYDETAILID = I.GROUTINGDAILYDETAILID
               LEFT JOIN TP_PM_BARCODEIDNRKREL BI ON BI.BARCODE = PD.BARCODE AND BI.PROCEDUREID =  PD.PROCEDUREID
               WHERE PD.BARCODE = @BARCODE@
               AND PD.PROCEDUREID = @PROCEDUREID@
               AND PD.VALUEFLAG = 1 
               AND I.PROCEDUREID <> @PROCEDUREID@ ",
                new CDAParameter("PROCEDUREID", context.Request["procedureId"]),
                new CDAParameter("BARCODE", context.Request["barCode"])
            );

            dtIdnrk.DefaultView.RowFilter = "MAKTX NOT LIKE '%半成品%'";
            dtIdnrk = dtIdnrk.DefaultView.ToTable();

            // 如果包含其它，就不判断了
            if (dtIdnrkType.Select("IDNRKTYPE = '其它'").Length == 0)
            {
                string fifter = "";
                foreach (DataRow row in dtIdnrkType.Rows)
                {
                    fifter += " IDNRKTYPE LIKE '%" + row["IDNRKTYPE"] + "%' OR";
                }

                // 如果没有条件，也不判断了
                if (fifter.Length > 0)
                {
                    fifter = fifter.Substring(0, fifter.Length - 2);
                    dtIdnrk.DefaultView.RowFilter = fifter;
                    dtIdnrk = dtIdnrk.DefaultView.ToTable();
                }
            }

            // 过滤掉已安装的组件
            object idnrklist = conn.ExecuteScalar(@"
            SELECT LISTAGG(IDNRK, ''',''') WITHIN GROUP(ORDER BY IDNRK) AS IDNRKLIST
              FROM TP_PM_BARCODEIDNRKREL
             WHERE VALUEFLAG = '1'
               AND BARCODE = @BARCODE@ ",
                new CDAParameter("BARCODE", context.Request["barCode"])
            );

            if (idnrklist != null)
            {
                dtIdnrk.DefaultView.RowFilter = "IDNRK NOT IN ('" + idnrklist + "')";
                dtIdnrk = dtIdnrk.DefaultView.ToTable();
            }

            if (dtIdnrk.Rows.Count == 0 && (isIdnrkData == null || isIdnrkData.Rows.Count == 0))
            {
                context.Response.Write(new JsonResult() { success = false, message = "当前工序没有需要安装的组件。" }.ToJson());
                return;
            } 

            // 加上需要的列
            dtIdnrk.Columns.Add("LGORT", typeof(string));
            dtIdnrk.Columns.Add("CHARG", typeof(string));
            dtIdnrk.Columns.Add("PROCEDURENAME", typeof(string));
            dtIdnrk.Columns.Add("CREATEUSERNAME", typeof(string));
            dtIdnrk.Columns.Add("CREATETIME", typeof(string));
            dtIdnrk.Columns.Add("IDNRKONLYCODE", typeof(string));
            dtIdnrk.Columns.Add("SCANFLAG", typeof(string));

            foreach (DataRow rows1 in dtIdnrk.Rows)
            {
                foreach (DataRow rows2 in dtIdnrkType.Rows)
                {
                    if (rows1["IDNRKTYPE"].ToString().Contains(rows2["IDNRKTYPE"].ToString()))
                    {
                        rows1["SCANFLAG"] = rows2["SCANFLAG"];
                    }
                }
            }
            //// 提取包材物料编码
            //List<string> matnrs = new List<string>();
            //foreach (DataRow row in dtIdnrk.Rows)
            //{
            //    if (!matnrs.Contains(row["IDNRK"] + ""))
            //    {
            //        matnrs.Add(row["IDNRK"] + "");
            //    }
            //}

            //string ZMSG = string.Empty;

            //// 获取SAP库存
            //DataTable dtSapInventory = SapApi.ZMM_WMS016("5000", matnrs, "", out ZMSG);
            //dtSapInventory.DefaultView.RowFilter = "LGORT IN('2420','2430','2440','2450','2460','2470','2480','2490')";
            //dtSapInventory = dtSapInventory.DefaultView.ToTable();

            ////if (dtSapInventory != null && dtSapInventory.Rows.Count > 0)
            //{
            //    // 改名
            //    dtSapInventory.Columns["MATNR"].ColumnName = "IDNRK";

            //    // 判断是否缺库存
            //    List<string> notEnoughIdnrks = new List<string>();
            //    foreach (string idnrk in matnrs)
            //    {
            //        rows = dtSapInventory.Select("IDNRK = '" + idnrk + "'"); ;
            //        if (rows.Length == 0)
            //        {
            //            notEnoughIdnrks.Add(idnrk);
            //        }
            //    }

            //    //if (notEnoughIdnrks.Count > 0)
            //    //{
            //    //    context.Response.Write(new JsonResult()
            //    //    {
            //    //        success = false,
            //    //        message = "以下组件在SAP系统中库存不足：\n" + string.Join(",", notEnoughIdnrks.ToArray())
            //    //    }.ToJson());
            //    //    return;
            //    //}

            //    // 库存数量要改为数字类型
            //    dtSapInventory.Columns.Add("BALANCE", typeof(decimal));
            //    decimal balance = 0;

            //    foreach (DataRow row in dtSapInventory.Rows)
            //    {
            //        decimal.TryParse(row["KYKC"] + "", out balance);
            //        row["BALANCE"] = balance;
            //    }
            //}
            ////else
            //{
            //    //context.Response.Write(new JsonResult()
            //    //{
            //    //    success = false,
            //    //    message = "以下组件在SAP系统中库存不足：\n" + string.Join(",", matnrs.ToArray())
            //    //}.ToJson());
            //    //return;
            //}

            //dtSapInventory.DefaultView.RowFilter = "BALANCE <> 0";
            //dtSapInventory = dtSapInventory.DefaultView.ToTable();

            //dtSapInventory.DefaultView.Sort = "LGORT, CHARG";
            //dtSapInventory = dtSapInventory.DefaultView.ToTable();

            //DateTime now = DateTime.Now;
            //foreach (DataRow row in dtIdnrk.Rows)
            //{
            //    //rows = dtSapInventory.Select("IDNRK = '" + row["IDNRK"] + "' AND BALANCE >= " + row["MENGE"]);
            //    rows = dtSapInventory.Select("IDNRK = '" + row["IDNRK"] + "'");
            //    if (rows.Length > 0)
            //    {
            //        row["LGORT"] = rows[0]["LGORT"];
            //        row["CHARG"] = rows[0]["CHARG"];
            //        row["PROCEDURENAME"] = procedurename;
            //        row["CREATEUSERNAME"] = "";
            //        //row["CREATETIME"] = now.ToString("yyyyy-MM-dd HH:mm:ss");
            //        row["CREATETIME"] = "";
            //        row["IDNRKONLYCODE"] = "";
            //    }
            //    else
            //    {
            //        //context.Response.Write(new JsonResult()
            //        //{
            //        //    success = false,
            //        //    message = "以下组件在SAP系统中库存不足：" + row["IDNRK"]
            //        //}.ToJson());
            //        //return;
            //    }
            //}


            //// 查当前组件的库存
            //foreach (DataRow row in dtIdnrk.Rows)
            //{
            //    DataTable dtLgort = conn.ExecuteDatatable(@"
            //    SELECT LGORT
            //      FROM TP_PM_LGORTIDNRKTYPEREL LT
            //     WHERE INSTR('合格证HC0979', IDNRKTYPE) > 0 ",
            //        new CDAParameter("PROCEDUREID", context.Request["procedureId"])
            //    );
            //}

            //dtIdnrk.DefaultView.RowFilter = "LGORT IS NOT NULL AND LGORT <> ''";
            //dtIdnrk = dtIdnrk.DefaultView.ToTable();

            foreach (DataRow row in dtIdnrk.Rows)
            {
                row["PROCEDURENAME"] = procedurename;
                row["CHARG"] = "";
                row["LGORT"] = "";
                row["CREATEUSERNAME"] = "";
                row["CREATETIME"] = "";
                row["IDNRKONLYCODE"] = "";
            }

            if (dtIdnrk.Rows.Count == 0 && (isIdnrkData == null || isIdnrkData.Rows.Count == 0))
            {
                context.Response.Write(new JsonResult() { success = false, message = "当前工序没有需要安装的组件。" }.ToJson());
                return;
            }
            else if (isIdnrkData != null && isIdnrkData.Rows.Count > 0)
            {
                context.Response.Write(new JsonResult() { success = false, message = "该工序已绑定组件" }.ToJson());
                return;
            }
            else
            {
                context.Response.Write(new JsonResult() { success = true, message = "操作成功！", rows = dtIdnrk }.ToJson());
            }

            #endregion
        }
    }

    /// <summary>
    /// JArray转DataTable
    /// </summary>
    /// <param name="dataArr"></param>
    /// <returns></returns>
    public static DataTable ConvertToDataTable(JArray dataArr)
    {
        if (dataArr == null || dataArr.Count <= 0)
            return null;
        DataTable result = new DataTable();
        var colnames = ((JObject)(dataArr.First)).Properties();
        List<string> columnNames = new List<string>();
        if (colnames == null || colnames.Count() <= 0)
            return null;
        foreach (var item in colnames)
        {
            if (!columnNames.Contains(item.Name))
                columnNames.Add(item.Name);
            result.Columns.Add(item.Name, typeof(string));
        }
        foreach (JObject data in dataArr)
        {
            JObject jo = JObject.Parse(data.ToString());
            DataRow row = result.NewRow();
            foreach (var columnName in columnNames)
            {
                if (jo.Property(columnName) == null)
                {
                    data.Add(columnName, "");
                    row[columnName] = data[columnName].ToString();
                }
                else
                {
                    row[columnName] = data[columnName].ToString();
                }
            }
            result.Rows.Add(row);
        }
        return result;
    }

    public bool IsReusable
    {
        get
        {
            return false;
        }
    }

}