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

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


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 GetLogoData : IHttpHandler, IReadOnlySessionState
{
    public void ProcessRequest(HttpContext context)
    {
        using (IDataAccess conn = DataAccess.Create())
        {
            DataTable planData = conn.ExecuteDatatable(@"
                        SELECT PP.GOODSID,PP.LOGOID,PP.QUANTITY,GDD.MATERIALCODE,PP.BEGINTIME,PP.ENDTIME 
                     FROM TP_PM_PRODUCTION_PLAN PP
                     LEFT JOIN TP_PM_GROUTINGDAILYDETAIL GDD ON PP.GOODSID = GDD.GOODSID 
                     WHERE GDD.BARCODE = @BARCODE@
                     AND PP.BEGINTIME <= SYSDATE
                     AND PP.VALUEFLAG = 1
                     AND PP.CONTROLRANGE = 1
                     AND PP.ENDTIME > SYSDATE  ",
               new CDAParameter("BARCODE", context.Request["barCode"])
           );
            if (planData != null && planData.Rows.Count > 0)
            {
                DataTable planFlag = conn.ExecuteDatatable(@"
                        SELECT PLANFLAG FROM TP_MST_User WHERE USERID = @USERID@ ",
                    new CDAParameter("USERID", HttpContext.Current.Session["userid"])
                    );
                if (planFlag != null && planFlag.Rows.Count > 0 && planFlag.Rows[0]["PLANFLAG"].ToString() == "0")
                {
                    //低级权限
                    DataTable plansTable = conn.ExecuteDatatable(@"
                                  SELECT l.logoid AS id
                                          ,CASE
                                               WHEN linshi.residuequantity IS NOT NULL THEN
                                                 l.logoname || '[' || t2.materialcode || ']' || ' 剩余[' || linshi.residuequantity || ']'
                                               ELSE
                                                l.logoname || '[' || t2.materialcode || ']'
                                           END AS NAME
                                          ,t2.materialcode
                                          ,nvl(gdd.valueflag, '0') AS iscurrentlogo
                                          ,1 AS ISCONTROL
                                      FROM tp_mst_logo l
                                     INNER JOIN (SELECT DISTINCT t.goodsid,t.logoid,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 EXISTS (SELECT 1 FROM tp_pm_groutingdailydetail g
                                                          WHERE g.goodsid = t.goodsid AND g.barcode = @BARCODE@)) t2 ON t2.logoid = l.logoid
                                      LEFT JOIN tp_pm_groutingdailydetail gdd ON gdd.logoid = l.logoid
                                       AND gdd.barcode = @BARCODE@
                                      RIGHT JOIN ( 
                                        SELECT case when t.goodsid is null then tt.goodsid  else t.goodsid end as goodsid 
                                              ,case when t.logoid is null then tt.logoid else t.logoid end as logoid 
                                              ,case when t.changenum is null then sum(0) else SUM(t.changenum) end as changenum 
                                               ,tt.quantity
                                                ,case when t.changenum is null then  tt.quantity else tt.quantity - SUM(t.changenum) end as residuequantity  
                                                   FROM (
                                                    --在装配环节变更数量
                                                   SELECT ip.goodsid,ip.logoid,COUNT(distinct l.barcode) changenum
                                                           FROM tp_pm_logochangedrecord l
                                                           right JOIN tp_pm_groutingdailydetail ip ON l.barcode = ip.barcode 
                                                           LEFT JOIN tp_pc_procedure p ON l.procedureid = p.procedureid
                                                           LEFT JOIN (SELECT pp.goodsid,pp.logoid,pp.quantity,gdd.materialcode,pp.begintime ,pp.endtime
                                                                       FROM tp_pm_production_plan pp
                                                                       LEFT JOIN tp_pm_groutingdailydetail gdd ON pp.goodsid = gdd.goodsid
                                                                      WHERE gdd.barcode = @BARCODE@
                                                                        AND pp.begintime <= SYSDATE
                                                                        AND pp.endtime > SYSDATE and pp.valueflag = 1 AND pp.CONTROLRANGE = 1) plans  ON plans.goodsid = ip.goodsid AND plans.logoid =  ip.logoid
                                                          WHERE l.createtime >= plans.begintime
                                                            AND l.createtime < plans.endtime
                                                            AND ip.goodsid = @GOODSID@ 
                                                            and ip.logoid = l.newlogoid 
                                                            AND l.oldlogoid <> l.newlogoid
                                                            AND p.modeltype = '-5' 
                                                            AND p.procedureid not in(126,152) 
                                                            AND NOT EXISTS (SELECT s.barcode FROM tp_pm_scrapproduct s
                                                                  WHERE s.barcode = l.barcode AND s.valueflag = 1 AND s.createtime >= l.createtime)
                                                            GROUP BY ip.goodsid,ip.logoid
                                                          UNION ALL
                                                         --生产数据该产品商标在装配环节保存的数量(装配环节没有变更商标) 
                                                         SELECT  pd.goodsid ,pd.logoid ,COUNT(distinct pd.barcode) changenum
                                                           FROM tp_pm_productiondata pd  
                                                           LEFT JOIN tp_pc_procedure p ON pd.procedureid = p.procedureid 
                                                           left join (SELECT pp.goodsid,pp.logoid,pp.quantity,gdd.materialcode,pp.begintime ,pp.endtime
                                                                            FROM tp_pm_production_plan pp
                                                                            LEFT JOIN tp_pm_groutingdailydetail gdd ON pp.goodsid = gdd.goodsid  
                                                                            WHERE gdd.barcode = @BARCODE@
                                                                            AND pp.begintime <= SYSDATE
                                                                            AND pp.endtime > SYSDATE AND pp.CONTROLRANGE = 1 AND pp.VALUEFLAG = 1 ) plans1 on plans1.goodsid = pd.goodsid and plans1.logoid = pd.logoid
                                                                      WHERE  pd.createtime >= plans1.begintime
                                                                      and pd.createtime < plans1.endtime
                                                                      and pd.valueflag = 1 
                                                                      and p.modeltype = '-5' 
                                                                      and p.procedureid not in(126,152) 
                                                                      AND NOT EXISTS (SELECT s.barcode
                                                                          FROM tp_pm_logochangedrecord s
                                                                          LEFT JOIN tp_pc_procedure p ON s.procedureid = p.procedureid
                                                                         WHERE s.barcode = pd.barcode
                                                                          AND  p.modeltype = '-5' 
                                                                          and p.procedureid not in(126,152) 
                                                                          --AND  s.oldlogoid = pd.logoid
                                                                          AND  s.oldlogoid <> s.newlogoid)  
                                                                    GROUP BY pd.goodsid,pd.logoid) t
                                                   RIGHT JOIN (SELECT pp.goodsid,pp.logoid,pp.quantity
                                                               FROM tp_pm_production_plan pp
                                                               LEFT JOIN tp_pm_groutingdailydetail gdd ON pp.goodsid = gdd.goodsid
                                                              WHERE gdd.barcode = @BARCODE@
                                                                AND pp.begintime <= SYSDATE
                                                                AND pp.endtime > SYSDATE
                                                                and pp.valueflag = 1 AND pp.CONTROLRANGE = 1) tt
                                                     ON t.logoid = tt.logoid
                                                  GROUP BY  t.goodsid,tt.goodsid,t.logoid,tt.logoid,tt.quantity ,t.changenum
                                                 ) linshi  ON linshi.logoid = l.logoid
                                     WHERE l.valueflag = '1' AND l.accountid = @ACCOUNTID@
                                     ORDER BY l.displayno",
                            new CDAParameter("BARCODE", context.Request["barCode"]),
                            new CDAParameter("ACCOUNTID", HttpContext.Current.Session["accountId"]),
                            new CDAParameter("GOODSID", planData.Rows[0]["GOODSID"].ToString())
                        );
                    context.Response.Write(new JsonResult() { success = true, message = "操作成功！", rows = plansTable }.ToJson());
                }
                else
                {
                    DataTable plansTable = conn.ExecuteDatatable(@"
                                SELECT l.logoid AS id
                                          ,CASE
                                               WHEN linshi.residuequantity IS NOT NULL THEN
                                                 l.logoname || '[' || t2.materialcode || ']' || ' 剩余[' || linshi.residuequantity || ']'
                                               ELSE
                                                l.logoname || '[' || t2.materialcode || ']'
                                           END AS NAME
                                          ,t2.materialcode
                                          ,nvl(gdd.valueflag, '0') AS iscurrentlogo
                                          ,0 AS ISCONTROL
                                      FROM tp_mst_logo l
                                     INNER JOIN (SELECT DISTINCT t.goodsid,t.logoid,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 EXISTS (SELECT 1 FROM tp_pm_groutingdailydetail g
                                                          WHERE g.goodsid = t.goodsid AND g.barcode = @BARCODE@)) t2 ON t2.logoid = l.logoid
                                      LEFT JOIN tp_pm_groutingdailydetail gdd ON gdd.logoid = l.logoid
                                       AND gdd.barcode = @BARCODE@
                                      LEFT JOIN ( 
                                        SELECT case when t.goodsid is null then tt.goodsid  else t.goodsid end as goodsid 
                                              ,case when t.logoid is null then tt.logoid else t.logoid end as logoid 
                                              ,case when t.changenum is null then sum(0) else SUM(t.changenum) end as changenum 
                                               ,tt.quantity
                                                ,case when t.changenum is null then  tt.quantity else tt.quantity - SUM(t.changenum) end as residuequantity 
                                                   FROM (SELECT ip.goodsid,ip.logoid,COUNT(distinct l.barcode) changenum
                                                           FROM tp_pm_logochangedrecord l
                                                           right JOIN tp_pm_groutingdailydetail ip ON l.barcode = ip.barcode 
                                                           LEFT JOIN tp_pc_procedure p ON l.procedureid = p.procedureid
                                                           LEFT JOIN (SELECT pp.goodsid,pp.logoid,pp.quantity,gdd.materialcode,pp.begintime ,pp.endtime
                                                                       FROM tp_pm_production_plan pp
                                                                       LEFT JOIN tp_pm_groutingdailydetail gdd ON pp.goodsid = gdd.goodsid
                                                                      WHERE gdd.barcode = @BARCODE@
                                                                        AND pp.begintime <= SYSDATE
                                                                        AND pp.endtime > SYSDATE and pp.valueflag = 1 AND pp.CONTROLRANGE = 1) plans  ON plans.goodsid = ip.goodsid AND plans.logoid =  ip.logoid
                                                          WHERE l.createtime >= plans.begintime
                                                            AND l.createtime < plans.endtime
                                                            AND ip.goodsid = @GOODSID@ 
                                                            and ip.logoid = l.newlogoid 
                                                            AND l.oldlogoid <> l.newlogoid
                                                            AND p.modeltype = '-5' 
                                                            AND p.procedureid not in(126,152) 
                                                            AND NOT EXISTS (SELECT s.barcode FROM tp_pm_scrapproduct s
                                                                  WHERE s.barcode = l.barcode AND s.valueflag = 1 AND s.createtime >= l.createtime)
                                                            GROUP BY ip.goodsid,ip.logoid 
                                                          UNION ALL
                                                          --生产数据该产品商标在装配环节保存的数量(装配环节没有变更商标) 
                                                         SELECT  pd.goodsid ,pd.logoid ,COUNT(distinct pd.barcode) changenum
                                                           FROM tp_pm_productiondata pd  
                                                           LEFT JOIN tp_pc_procedure p ON pd.procedureid = p.procedureid 
                                                           left join (SELECT pp.goodsid,pp.logoid,pp.quantity,gdd.materialcode,pp.begintime ,pp.endtime
                                                                            FROM tp_pm_production_plan pp
                                                                            LEFT JOIN tp_pm_groutingdailydetail gdd ON pp.goodsid = gdd.goodsid  
                                                                            WHERE gdd.barcode = @BARCODE@
                                                                            AND pp.begintime <= SYSDATE
                                                                            AND pp.endtime > SYSDATE AND pp.CONTROLRANGE = 1 AND pp.VALUEFLAG = 1 ) plans1 on plans1.goodsid = pd.goodsid and plans1.logoid = pd.logoid
                                                                      WHERE  pd.createtime >= plans1.begintime
                                                                      and pd.createtime < plans1.endtime
                                                                      and pd.valueflag = 1 
                                                                      and p.modeltype = '-5' 
                                                                      and p.procedureid not in(126,152) 
                                                                      AND NOT EXISTS (SELECT s.barcode
                                                                          FROM tp_pm_logochangedrecord s
                                                                          LEFT JOIN tp_pc_procedure p ON s.procedureid = p.procedureid
                                                                         WHERE s.barcode = pd.barcode
                                                                          AND  p.modeltype = '-5' 
                                                                          and p.procedureid not in(126,152) 
                                                                          --AND  s.oldlogoid = pd.logoid
                                                                          AND  s.oldlogoid <> s.newlogoid)  
                                                                    GROUP BY pd.goodsid,pd.logoid) t
                                                   RIGHT JOIN (SELECT pp.goodsid,pp.logoid,pp.quantity
                                                               FROM tp_pm_production_plan pp
                                                               LEFT JOIN tp_pm_groutingdailydetail gdd ON pp.goodsid = gdd.goodsid
                                                              WHERE gdd.barcode = @BARCODE@
                                                                AND pp.begintime <= SYSDATE
                                                                AND pp.endtime > SYSDATE
                                                                and pp.valueflag = 1 AND pp.CONTROLRANGE = 1) tt
                                                     ON t.logoid = tt.logoid
                                                   GROUP BY  t.goodsid,tt.goodsid,t.logoid,tt.logoid,tt.quantity ,t.changenum
                                                 ) linshi  ON linshi.logoid = l.logoid
                                     WHERE l.valueflag = '1' AND l.accountid = @ACCOUNTID@
                                     ORDER BY l.displayno",
                            new CDAParameter("BARCODE", context.Request["barCode"]),
                            new CDAParameter("ACCOUNTID", HttpContext.Current.Session["accountId"]),
                            new CDAParameter("GOODSID", planData.Rows[0]["GOODSID"].ToString())
                        );
                    context.Response.Write(new JsonResult() { success = true, message = "操作成功！", rows = plansTable }.ToJson());
                }
            }
            else
            {
                DataTable dtLogo = conn.ExecuteDatatable(@"
                    SELECT L.LOGOID AS ID,
                           L.LOGONAME || '[' || T2.MATERIALCODE || ']' AS NAME,
                           T2.MATERIALCODE,
                           NVL(GDD.VALUEFLAG, '0') AS ISCURRENTLOGO
                           ,0 AS ISCONTROL
                      FROM TP_MST_LOGO L
                     INNER JOIN (SELECT DISTINCT T.GOODSID,
                                                 T.LOGOID,
                                                 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 EXISTS (SELECT 1
                                           FROM TP_PM_GROUTINGDAILYDETAIL G
                                          WHERE G.GOODSID = T.GOODSID
                                            AND G.BARCODE = @BARCODE@)) T2
                        ON T2.LOGOID = L.LOGOID
                      LEFT JOIN TP_PM_GROUTINGDAILYDETAIL GDD
                        ON GDD.LOGOID = L.LOGOID
                       AND GDD.BARCODE = @BARCODE@
                     WHERE L.VALUEFLAG = '1'
                       AND L.ACCOUNTID = @ACCOUNTID@
                     ORDER BY L.DISPLAYNO ",
                    new CDAParameter("BARCODE", context.Request["barCode"]),
                    new CDAParameter("ACCOUNTID", HttpContext.Current.Session["accountId"])
                );
                context.Response.Write(new JsonResult() { success = true, message = "操作成功！", rows = dtLogo }.ToJson());
            }
        }
    }


    public bool IsReusable
    {
        get
        {
            return false;
        }
    }

}