HEGII.ss.mes.packingline/wwwroot/App_Code/Packing.cs

using System;
using System.Collections.Generic;
using System.Configuration;
using System.Linq;
using System.Web;
using Curtain.DataAccess;
using Curtain.Log;
using System.Data;
using System.Drawing;
using Newtonsoft.Json.Linq;
using Newtonsoft.Json;

/// <summary>
/// AutoPacking 的摘要说明
/// </summary>
public class Packing
{
    /// <summary>
    /// 自动生成包装单 toQianrun = -1 不提交到乾润接口 toQianrun = 0 提交到润接口
    /// </summary>
    public static int AutoPacking(int toQianrun = -1)
    {
        int PackingMinQueue = 2;
        int PackingMaxQueue = 2;
        int AutoPacking = 1;

        int result = 1;

        //从数据库加载配置
        using (IDataAccess conn = DataAccess.Create())
        {
            //读取包装线配置表
            DataTable dt = conn.ExecuteDatatable(@"
				SELECT 
					SETTINGNAME,
					SETTINGVALUE
				FROM
					TP_MST_PACKINGSETTING
				WHERE
					SETTINGTYPE = 1
				"
            );

            //配置参数保存到Application中
            //WareHouseRefreshTime 仓库队列刷新时间
            //PackingStart 包装线启动地址
            //PackingMinQueue 仓库队列最小值，小于此数值开始自动产生包装单
            //PackingMaxQueue 仓库队列最大值，达到此数值，停止产生包装单
            //AutoPacking 是否启用自动生成包装单
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                if (dt.Rows[i]["SETTINGNAME"].ToString() == "AutoPacking") AutoPacking = Convert.ToInt32(dt.Rows[i]["SETTINGVALUE"]);
                if (dt.Rows[i]["SETTINGNAME"].ToString() == "PackingMinQueue") PackingMinQueue = Convert.ToInt32(dt.Rows[i]["SETTINGVALUE"]);
                if (dt.Rows[i]["SETTINGNAME"].ToString() == "PackingMaxQueue") PackingMaxQueue = Convert.ToInt32(dt.Rows[i]["SETTINGVALUE"]);
            }

        }

        //执行自动下单
        if (AutoPacking == 1)
        {
            using (IDataAccess conn = DataAccess.Create())
            {
                #region 开启事务，默认走1号包装线,包装队列为0
                conn.BeginTransaction();
                string packingLine = "1";
                int packingNum = 0;
                int maxOrderNo = 0;
                int minOrderNo = 0;
                #endregion

                #region 读取包装线号，用于判断哪条线队列小，优先走哪条线
                //统计包装线队列数量
                DataTable dt = conn.ExecuteDatatable(@"
                    SELECT
	                    L.PACKINGLINE,
	                    NVL( P.MAXORDERNO, 0 ) AS MAXORDERNO,
	                    NVL( P.MINORDERNO, 0 ) AS MINORDERNO,
	                    NVL( P.NUM, 0 ) AS NUM
                    FROM
	                    (
		                    ( SELECT 1 AS PACKINGLINE FROM DUAL UNION SELECT 2 AS PACKINGLINE FROM DUAL ) L
		                    LEFT JOIN (
		                    SELECT
			                    F.PACKINGLINE,
			                    MAX( F.ORDERNO ) AS MAXORDERNO,
			                    MIN( F.ORDERNO ) AS MINORDERNO,
			                    COUNT( * ) AS NUM 
		                    FROM
			                    TP_MST_PACKINGFORM F 
		                    WHERE
			                    F.STATUS IN ( '-1', '0', '1' ) --统计 未确认-1、队列中0、执行中1 的包装单
		                    GROUP BY
			                    F.PACKINGLINE 
		                    ) P ON L.PACKINGLINE = P.PACKINGLINE 
	                    ) 
                    ORDER BY
	                    NUM
				    "
                );
                //取包装单最少的包装线,并记录包装单数量
                if (dt.Rows.Count > 0) 
                { 
                    packingLine = dt.Rows[0]["PACKINGLINE"].ToString();
                    packingNum = Convert.ToInt32(dt.Rows[0]["NUM"]);
                    maxOrderNo = Convert.ToInt32(dt.Rows[0]["MAXORDERNO"]);
                    minOrderNo = Convert.ToInt32(dt.Rows[0]["MINORDERNO"]);
                }
                Logger.Debug("自动生产包装单：线号 = " + packingLine.ToString());
                #endregion

                #region 读取最小最大队列阀值，确认是否自动生成包装单

                //小于最小值时执行自动生成包装单，直到等于最大值停止
                if (packingNum < PackingMinQueue)
                {
                    //Logger.Debug((maxQueue - packingNum).ToString());
                    //生成包装单
                    for (int i = 0 ; i < PackingMaxQueue - packingNum ; i++)
                    {
                        if (result > 0)
                        {
                            result = CreatePacking(conn, packingLine, maxOrderNo + i + 1, toQianrun);
                            Logger.Debug("自动生产包装单："+ result.ToString());
                        }
                    }
                    //最小化包装单执序号
                    //MinOrderNo(conn, packingLine, minOrderNo);
                }
                else
                {
                    Logger.Debug("自动生产包装单：不满足条件");
                }
                #endregion

                #region 执行成功提交，否则回滚

                if (result > 0)
                    conn.Commit();
                else
                    conn.Rollback();

                #endregion

            }
        }

        return result;
    }

    /// <summary>
    /// 按规则生成一个包装单
    /// </summary>
    /// <param name="conn">数据连接</param>
    /// <param name="packingLine">包装线</param>
    /// <param name="orderNo">执行顺序</param>
    /// <returns>1执行成功，-1执行失败</returns>
    public static int CreatePacking(IDataAccess conn, string packingLine,int orderNo,int toQianrun = 0)
    {
        //生成包装单号
        string packingFormCode = Guid.NewGuid().ToString();
        string packingFormName = "系统自动包装单";

        #region 乾润接口读成瓷库存，查找最多数量的产品，生成包装单（现阶段不验证包材充足）

        //string goodsCode = "H0175M#CT175PD2210B01";
        string goodsCode = "";
        int goodsCount = 0;
        //读取乾润接口读取
        string goodsStr = QianRunApi.GetStorageStatus(1);

        JObject json = JObject.Parse(goodsStr);
        if (json["success"].ToString().ToLower() == "true")
        {
            //直接取第一个产品
            int goodsIndex = 0;


            //智能的只能下1线，9开头的是智能
            //待处理========================


            //==============================


            //验证包材
            //待处理========================


            //==============================



            goodsCode = json["rows"][goodsIndex]["GoodsType"].ToString();
            goodsCount = Convert.ToInt32(json["rows"][goodsIndex]["GoodsCount"]);

            //测试数据===============================
            //goodsCode = "H0175L#CT175PD4210B01";
            //goodsCount = 36;
            //=======================================


            //从队列中（未确定）读取数据，不要与队列产品重复
            DataTable dtp = conn.ExecuteDatatable(@"
                SELECT G.GOODSCODE||'#'||G.MATERIALCODE AS GOODSCODE FROM TP_MST_PACKINGFORM P LEFT JOIN V_GOODS_MATERIALCODE G ON P.GOODSID = G.GOODSID AND P.MATERIALCODE = G.MATERIALCODE WHERE STATUS = -1 
            ");
            if(dtp.Rows.Count>0)
            {
                string allgoods = ",";
                for (int i = 0; i < dtp.Rows.Count; i++) allgoods += dtp.Rows[i]["GOODSCODE"] + ",";
                int j = 0;
                while(allgoods.IndexOf("," +json["rows"][j]["GoodsType"].ToString() + ",")>=0) j++;
                if (j <= json["rows"].Count())
                {
                    //记录不重复的产品
                    goodsCode = json["rows"][j]["GoodsType"].ToString();
                    goodsCount = Convert.ToInt32(json["rows"][j]["GoodsCount"]);
                }
            }
            //-----------------------------------------------------------


            Logger.Debug("自动生产包装单：产品型号 = " + goodsCode.ToString());

        }
        else
        {
            Logger.Debug("自动生产包装单：乾润接口库存数据读取失败！" + goodsStr);
            return -1;
        }
        #endregion


        //如果找到可出库的产品
        if (goodsCode != "")
        {
            #region 从包装单配置找默认的包装配置属性

            string packingBomId = "";
            string goodsSeries = "";
            string goodsId = "";
            string materialCode = goodsCode.Split('#')[1];

            DataTable bom = conn.ExecuteDatatable(@"
                SELECT
	                p.PACKINGBOMID,
                    g.GOODSID,
                    p.MATNR,
	                g.PLATELIMITNUM,
	                DECODE( g.GOODSTYPEID, 18, 1, 19, 1, 2 ) AS GOODSSERIES 
                FROM
	                TP_MST_PACKINGBOM p

                    -- xuwei modify 2021-05-21 用物料码直接关联，因为可能存在多个产品使用同一BOM配置
	                --LEFT JOIN TP_MST_GOODS g ON g.GOODSID = p.GOODSID 
	                LEFT JOIN V_GOODS_MATERIALCODE g ON g.MATERIALCODE = p.MATNR

                WHERE
	                p.VALUEFLAG = '1' 
	                AND p.DEFAULTFLAG = '1'
				    AND g.GOODSCODE = @GOODSCODE@ 
                    AND g.MATERIALCODE = @MATERIALCODE@
			    ",
                new CDAParameter("GOODSCODE", goodsCode.Split('#')[0]),
                new CDAParameter("MATERIALCODE", goodsCode.Split('#')[1])
            );

            if (bom.Rows.Count > 0)
            {
                packingBomId = bom.Rows[0]["PACKINGBOMID"].ToString();
                goodsId = bom.Rows[0]["GOODSID"].ToString();
                goodsSeries = bom.Rows[0]["GOODSSERIES"].ToString();
                //不取整板数量，按乾润整道数量直接出
                //goodsCount = Convert.ToInt32(bom.Rows[0]["PLATELIMITNUM"]);
                Logger.Debug("自动生产包装单：包装配置 = " + packingBomId);
            }
            else
            {
                Logger.Debug("自动生产包装单：包装配置单未找到产品" + goodsCode);
                return -1;
            }
            #endregion

            #region 写入包装单
            int result = conn.ExecuteNonQuery(@"
                INSERT INTO TP_MST_PACKINGFORM (
                    PACKINGFORMCODE,PACKINGFORMNAME,PACKINGLINE,PACKINGBOMID,
                    ORDERNO,STATUS,PLATENUM,OUTNUM,GOODSID,MATERIALCODE,
                    ACCOUNTID,CREATEUSERID,UPDATEUSERID
                ) VALUES (
                    @PACKINGFORMCODE@,@PACKINGFORMNAME@,@PACKINGLINE@,@PACKINGBOMID@,
                    @ORDERNO@,@STATUS@,@PLATENUM@,@OUTNUM@,@GOODSID@,@MATERIALCODE@,
                    @ACCOUNTID@,@CREATEUSERID@,@UPDATEUSERID@
                )
                ",
                new CDAParameter("PACKINGFORMCODE", packingFormCode),
                new CDAParameter("PACKINGFORMNAME", packingFormName),
                new CDAParameter("PACKINGLINE", packingLine),
                new CDAParameter("PACKINGBOMID", packingBomId),
                new CDAParameter("ORDERNO", orderNo),
                new CDAParameter("STATUS", toQianrun), //自动建立的包装单，需要人工确认才能启动 2021-05-10 xuwei
                new CDAParameter("PLATENUM", goodsCount),
                new CDAParameter("OUTNUM", 0),
                new CDAParameter("GOODSID", goodsId),
                new CDAParameter("MATERIALCODE", materialCode),
                new CDAParameter("ACCOUNTID", 1),
                new CDAParameter("CREATEUSERID", 1),
                new CDAParameter("UPDATEUSERID", 1)
              );

            if (result <= 0)
            {
                Logger.Debug("自动生产包装单：包装单写入失败！");
                return -1;
            }
            else
            {
                Logger.Debug("自动生产包装单：包装单编码 = " + packingFormCode);
            }

            #endregion

            #region 乾润接口包装单写入，toQianrun==0时调接口，toQianrun==-1时不调用接口
            if (toQianrun == 0)
            {
                string data = QianRunApi.CreateOutStorageOrder(
                    packingLine,
                    packingFormCode,
                    goodsCode,
                    goodsSeries,
                    goodsCount,
                    orderNo,
                    DateTime.Now.ToString(),
                    0,0,0
                    );

                JObject jsonOrder = JObject.Parse(data);
                if (jsonOrder["Success"].ToString().ToLower() == "false")
                {
                    Logger.Debug("自动生产包装单：乾润接口包装单创建失败！" + data);
                    return -1;
                }
            }
            #endregion

            //写入自动包装单生成日志
            Logger.Debug("自动生产包装单" + (toQianrun == -1 ? "（未确认）" : "") + "：" + packingFormCode);
            return 1;
        }
        else
        {
            //写入自动包装单生成日志
            Logger.Debug("自动生产包装单：没有符合的产品");
            return 0;
        }

    }


    /// <summary>
    /// 更新出库数量 每次-1 只更新未完成的单子 并更新包装单状态
    /// </summary>
    /// <param name="conn">数据连接</param>
    /// <param name="packingFormCode">包装单号</param>
    /// <param name="outNum">outNum=0执行异常终止操作</param>
    /// <param name="outMessage">outNum=0时的异常中止原因</param>
    /// <returns></returns>
    public static int updatePackingOutNum(IDataAccess conn,string packingFormCode, string innumcount="",int outNum = 1,   string outMesssage="")
    {
        if (outNum < 0) outNum = 0;
        int result = 0;
        //20240118 qq 新增参数innumcount ，不传时启用老方式，传值则用新方式，
        if (string.IsNullOrEmpty(innumcount))
		{
            //更新包装单数量
            result = conn.ExecuteNonQuery(@"
            UPDATE 
                TP_MST_PACKINGFORM 
            SET 
                OUTNUM = OUTNUM + @OUTNUM@
            WHERE 
                STATUS <> 2 --不是完成状态的单子
                AND STATUS >= 0 -- 已确认的单子，自动下单未确认的是-1，不能自动开启 2021-05-10
                AND PACKINGFORMCODE = @PACKINGFORMCODE@
            ",
                new CDAParameter("OUTNUM", outNum),
                new CDAParameter("PACKINGFORMCODE", packingFormCode)
                );
        }
		else
		{
            //更新包装单数量
            DataTable dt = conn.ExecuteDatatable(@"
                        SELECT
	                        F.PLATENUM 
                        FROM
	                        TP_MST_PACKINGFORM F 
                        WHERE
	                        STATUS <> 2 --不是完成状态的单子
                            AND STATUS >= 0 -- 已确认的单子，自动下单未确认的是-1，不能自动开启 2021-05-10
                            AND PACKINGFORMCODE = @PACKINGFORMCODE@
				    ",
                         new CDAParameter("PACKINGFORMCODE", packingFormCode)
                    );
			if (dt != null  && dt.Rows.Count > 0)
			{
				if (Convert.ToInt32(dt.Rows[0]["PLATENUM"]) < Convert.ToInt32(innumcount))
				{
                    innumcount = dt.Rows[0]["PLATENUM"].ToString(); 
                }

            }
            result = conn.ExecuteNonQuery(@"
            UPDATE 
                TP_MST_PACKINGFORM 
            SET 
                OUTNUM = PLATENUM - @INNUMCOUNT@
            WHERE 
                STATUS <> 2 --不是完成状态的单子
                AND STATUS >= 0 -- 已确认的单子，自动下单未确认的是-1，不能自动开启 2021-05-10
                AND PACKINGFORMCODE = @PACKINGFORMCODE@
            ",
                new CDAParameter("INNUMCOUNT", Convert.ToInt32(innumcount)),
                new CDAParameter("PACKINGFORMCODE", packingFormCode)
                );
        }
              
        //更新包装单状态
        updatePackingStatus(conn, packingFormCode, outNum, outMesssage);
        return result;
    }

    /// <summary>
    /// 计算并更新包装单状态
    /// </summary>
    /// <param name="conn">数据连接</param>
    /// <param name="packingFormCode">包装单号</param>
    /// <param name="outNum">outNum=0执行异常终止操作</param>
    /// <param name="outMessage">outNum=0时的异常中止原因</param>
    public static void updatePackingStatus(IDataAccess conn,string packingFormCode, int outNum = 1, string outMessage="")
    {
        //更新包装单为执行中状态，状态是0未开始执行的，有出库数量的，更新为执行中1
        conn.ExecuteNonQuery(@"
            UPDATE 
                TP_MST_PACKINGFORM 
            SET 
                BEGINTIME = SYSDATE,
                STATUS = 1
            WHERE 
                PACKINGFORMCODE = @PACKINGFORMCODE@ 
                AND STATUS = 0 
                AND OUTNUM > 0
            ",
            new CDAParameter("PACKINGFORMCODE", packingFormCode)
        );

        //兼容新老系统，不传  outNum，默认为1 ，为正常完成，否则为异常终止
        if (outNum > 0)
        {
                //更新包装单为完成状态，状态是1执行中的，出库数量等于包装单数量的，更新为完成2
                conn.ExecuteNonQuery(@"
                UPDATE 
                    TP_MST_PACKINGFORM 
                SET 
                    ENDTIME = SYSDATE,
                    STATUS = 2
                WHERE 
                    PACKINGFORMCODE = @PACKINGFORMCODE@ 
                    AND OUTNUM = PLATENUM 
                    AND STATUS = 1
                ",
                new CDAParameter("PACKINGFORMCODE", packingFormCode)
            );
        }

        //更新包装单为强制终止状态,状态是1执行中的，当出库数量等于0的，更新为异常终止3
        if(outNum <= 0)
        {
            if (outMessage == "") outMessage = "异常终止！";
            conn.ExecuteNonQuery(@"
                UPDATE 
                    TP_MST_PACKINGFORM 
                SET 
                    ENDTIME = SYSDATE,
                    STATUS = 3,
                    REMARKS = @REMARKS@
                WHERE 
                    PACKINGFORMCODE = @PACKINGFORMCODE@ 
                    AND OUTNUM <> PLATENUM 
                    AND STATUS = 1
                ",
                new CDAParameter("PACKINGFORMCODE", packingFormCode),
                new CDAParameter("REMARKS", outMessage)
            );
        }
    }


    /// <summary>
    /// 发送包装单给乾润
    /// </summary>
    /// <param name="packingFormCode">包装单号</param>
    /// <returns></returns>
    public static int sendToQianrun(string packingFormCode)
    {

        using (IDataAccess conn = DataAccess.Create())
        {
            conn.BeginTransaction();

            //写入包装单状态 变更状态为队列中
            int result = conn.ExecuteNonQuery(@"
				UPDATE TP_MST_PACKINGFORM
				SET
					STATUS = 0
				WHERE 
					PACKINGFORMCODE = @PACKINGFORMCODE@
				",
                    new CDAParameter("PACKINGFORMCODE", packingFormCode)
                );

            if (result > 0)
            {
                //读取包装单必要的参数
                DataTable dt = conn.ExecuteDatatable(@"
                    SELECT 
						P.PACKINGLINE,
						P.PACKINGFORMCODE,
						G.GOODSCODE,
                        G.MATERIALCODE,
						DECODE(G.GOODSTYPEID, 18, 1, 19, 1, 2) AS GOODSSERIES,
                        P.PLATENUM AS GOODSCOUNT,
						P.ORDERNO
                    FROM
                        TP_MST_PACKINGFORM P

                        --xuwei modify 2021-05-28 
                        --LEFT JOIN TP_MST_GOODS G ON P.GOODSID = G.GOODSID
                        LEFT JOIN V_GOODS_MATERIALCODE G ON P.GOODSID = G.GOODSID AND P.MATERIALCODE = G.MATERIALCODE

                    WHERE
                        P.PACKINGFORMCODE = @PACKINGFORMCODE@
                    ",
                        new CDAParameter("PACKINGFORMCODE", packingFormCode)
                    );
                string packingLine = dt.Rows[0]["PACKINGLINE"].ToString();
                string goodsCode = dt.Rows[0]["GOODSCODE"].ToString() + "#" + dt.Rows[0]["MATERIALCODE"].ToString();
                string goodsSeries = dt.Rows[0]["GOODSSERIES"].ToString();
                int goodsCount = Convert.ToInt32(dt.Rows[0]["GOODSCOUNT"]);
                int orderNo = Convert.ToInt32(dt.Rows[0]["ORDERNO"]);

                //调用乾润接口，发送包装单数据
                string data = QianRunApi.CreateOutStorageOrder(
                    packingLine,
                    packingFormCode,
                    goodsCode,
                    goodsSeries,
                    goodsCount,
                    orderNo,
                    DateTime.Now.ToString(), 0, 0, 0
                    );

                JObject jsonOrder = JObject.Parse(data);
                if (jsonOrder["success"].ToString().ToLower() == "false")
                {
                    conn.Rollback();
                    Logger.Debug("自动生产包装单：发送包装单，乾润接口包装单创建失败！" + data);
                    return -1;
                }
                else
                {
                    conn.Commit();
                    Logger.Debug("自动生产包装单：发送包装单，乾润接口包装单创建成功！" + packingFormCode);
                    return 1;
                }
            }
            else
            {
                conn.Rollback();
                Logger.Debug("自动生产包装单：发送包装单，没找到包装单 = " + packingFormCode);
                return -1;
            }
        }
    }

    /// <summary>
    /// 最小化执行顺序号
    /// </summary>
    /// <param name="conn"></param>
    /// <param name="minOrderNo"></param>
    /// <param name="packingLine"></param>
    private static void MinOrderNo(IDataAccess conn, string packingLine, int minOrderNo)
    {
        int result = conn.ExecuteNonQuery(@"
				UPDATE TP_MST_PACKINGFORM 
				SET
					ORDERNO = ORDERNO - @MINORDERNO@
				WHERE 
                    VALUEFLAG = '1'
                    AND STATUS IN ('0','1') --统计未开始和执行中的包装单
                    AND PACKINGLINE = @PACKINGLINE@
				",
            new CDAParameter("MINORDERNO", minOrderNo),
            new CDAParameter("PACKINGLINE", packingLine)
        );
    }



}