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

using System;
using System.Data;
using System.Text;

using Curtain.DataAccess;

/// <summary>
/// 生成滚动日计划
/// </summary>
public class RollingDailyPlan
{
    /// <summary>
    /// 生成滚动日计划
    /// </summary>
    /// <returns></returns>
    public static string RollingNewDailyPlan(DateTime today)
    {
        int accountID, userID;                      // 账套、用户ID
        DateTime yesterday = today.AddDays(-1);     // 昨天
        int yesterdayDay = yesterday.Day;           // 昨天日期号
        int monthDays = DateTime.DaysInMonth(yesterday.Year, yesterday.Month);      // 昨天所在月份的日数     
        int result = 0;

        using (IDataAccess conn = DataAccess.Create())
        {
            conn.BeginTransaction();
            try
            {
                // 如果做了成检数结转，先清掉
                result += conn.ExecuteNonQuery(@"
                    DELETE FROM TP_PC_CJDAILY WHERE ACCOUNTDATE = @YESTERDAY@ ",
                    new CDAParameter("YESTERDAY", yesterday, DataType.Date)
                );

                // 插入新的成检数结转
                result += conn.ExecuteNonQuery(@"
                    INSERT INTO TP_PC_CJDAILY
                        (ACCOUNTDATE
                        ,MATNR
                        ,CJQUANTITY)
                        SELECT TRUNC(T.CREATETIME) AS SJ
                              ,T.MATERIALCODE
                              ,NVL(SUM(DECODE(T.GOODSLEVELTYPEID, 0, 1, 0)), 0) NUM2
                          FROM (SELECT 0 GOODSLEVELTYPEID
                                      ,GDD.MATERIALCODE
                                      ,PD.GOODSID
                                      ,PD.CREATETIME
                                  FROM TP_PM_PRODUCTIONDATA PD
                                 INNER JOIN TP_PM_GROUTINGDAILYDETAIL GDD
                                    ON GDD.GROUTINGDAILYDETAILID = PD.GROUTINGDAILYDETAILID
                                 WHERE ((PD.PROCEDUREID <> 104 AND PD.VALUEFLAG = '1') OR
                                       (PD.PROCEDUREID = 104 AND PD.CHECKFLAG = '1'))
                                   AND (11 IS NULL OR ((11 = 11 AND ((PD.PROCEDUREID = 11) OR
                                       (PD.PROCEDUREID = 104 AND PD.ISREFIRE = '0'))) OR
                                       (11 = 58 AND ((PD.PROCEDUREID = 58) OR
                                       (PD.PROCEDUREID = 104 AND PD.ISREFIRE = '6'))) OR
                                       (11 NOT IN (11, 58) AND PD.PROCEDUREID = 11)))
                                   AND (NULL IS NULL OR INSTR(',' || NULL || ',', ',' || PD.PROCEDUREID || ',') > 0)
                                   AND PD.ACCOUNTID = 1
                                   AND PD.CREATETIME >= @YESTERDAY@
                                   AND PD.CREATETIME < TRUNC(@YESTERDAY@) + 1
                                UNION ALL
                                SELECT PD.GOODSLEVELTYPEID
                                      ,GDD.MATERIALCODE
                                      ,PD.GOODSID
                                      ,PD.CREATETIME
                                  FROM TP_PM_PRODUCTIONDATA PD
                                 INNER JOIN TP_PM_GROUTINGDAILYDETAIL GDD
                                    ON GDD.GROUTINGDAILYDETAILID = PD.GROUTINGDAILYDETAILID
                                 WHERE PD.VALUEFLAG = '1'
                                   AND PD.MODELTYPE = -1
                                   AND PD.ACCOUNTID = 1
                                   AND LENGTH(PD.KILNCARBATCHNO) > 0
                                   AND PD.CREATETIME >= @YESTERDAY@
                                   AND PD.CREATETIME < TRUNC(@YESTERDAY@) + 1
                                   AND PD.ISREFIRE = '0') T
                         INNER JOIN TP_MST_GOODS MSTGOODS
                            ON MSTGOODS.GOODSID = T.GOODSID
                         INNER JOIN TP_MST_GOODSTYPE MSTGOODSTYPE
                            ON MSTGOODSTYPE.GOODSTYPEID = MSTGOODS.GOODSTYPEID
                         WHERE MSTGOODSTYPE.GOODSTYPECODE LIKE '001001%'
                         GROUP BY TRUNC(T.CREATETIME)
                                 ,T.MATERIALCODE
                         ORDER BY TRUNC(T.CREATETIME)
                                 ,T.MATERIALCODE ",
                    new CDAParameter("YESTERDAY", yesterday, DataType.Date)
                );

                // 更新昨日成检数到日计划
                result += conn.ExecuteNonQuery(@"
                    UPDATE (SELECT DPD.DETAILID
                                  ,DP.ACCOUNTDATE
                                  ,DPD.MATNR
                                  ,DPD.CJQUANTITY
                              FROM TP_PC_DAILYPLANDETAIL DPD
                             INNER JOIN TP_PC_DAILYPLAN DP
                                ON DP.DAILYPLANID = DPD.DAILYPLANID
                             WHERE DP.ACCOUNTDATE = @YESTERDAY@) T1
                       SET T1.CJQUANTITY =
                           (SELECT CJ.CJQUANTITY
                              FROM TP_PC_CJDAILY CJ
                             WHERE CJ.MATNR = T1.MATNR
                               AND CJ.ACCOUNTDATE = T1.ACCOUNTDATE)
                     WHERE EXISTS (SELECT 1
                              FROM TP_PC_CJDAILY CJ
                             WHERE CJ.MATNR = T1.MATNR
                               AND CJ.ACCOUNTDATE = T1.ACCOUNTDATE) ",
                    new CDAParameter("YESTERDAY", yesterday, DataType.Date)
                );

                // 查询未完成的计划量
                // (packingbom不存在goodsid时，先给个固定值)
                DataTable dtNoFinish = conn.ExecuteDatatable(@"
                    SELECT T1.MONTHPLANID
                          ,T1.MATNR
                          ,NVL(PB.GOODSID, 1) AS GOODSID
                          ,NVL(PB.LOGOID, 15) AS LOGOID
                          ,T1.MENGE
                          ,NVL(T2.CJQUANTITY, 0) AS CJQUANTITY
                          ,T1.MENGE - NVL(T2.CJQUANTITY, 0) AS DIFFER
                          ,T1.ACCOUNTID
                          ,T1.CREATEUSERID
                      FROM (SELECT MP.MONTHPLANID
                                  ,MP.ACCOUNTID
                                  ,MP.CREATEUSERID
                                  ,MPD.MATNR
                                  ,SUM(MPD.MENGE) AS MENGE
                              FROM TP_PC_MONTHPLANDETAIL MPD
                             INNER JOIN TP_PC_MONTHPLAN MP
                                ON MP.MONTHPLANID = MPD.MONTHPLANID
                             WHERE MP.PROCEDUREID = 104
                               AND MP.ACCOUNTMONTH = TO_CHAR(TRUNC(@YESTERDAY@), 'YYYY-MM')
                             GROUP BY MP.MONTHPLANID
                                     ,MP.ACCOUNTID
                                     ,MP.CREATEUSERID
                                     ,MPD.MATNR) T1
                     INNER JOIN TP_MST_PACKINGBOM PB
                        ON PB.MATNR = T1.MATNR
                      LEFT JOIN (SELECT DPD.MATNR
                                       ,SUM(DPD.CJQUANTITY) AS CJQUANTITY
                                   FROM TP_PC_DAILYPLANDETAIL DPD
                                  INNER JOIN TP_PC_DAILYPLAN DP
                                     ON DP.DAILYPLANID = DPD.DAILYPLANID
                                  WHERE DP.ACCOUNTDATE >= TRUNC(ADD_MONTHS(LAST_DAY(@YESTERDAY@), -1) + 1)
                                    AND DP.ACCOUNTDATE < TRUNC(@YESTERDAY@) + 1
                                  GROUP BY DPD.MATNR) T2
                        ON T2.MATNR = T1.MATNR
                     WHERE T1.MENGE > NVL(T2.CJQUANTITY, 0) ",
                    new CDAParameter("YESTERDAY", yesterday, DataType.Date)
                );

                if (dtNoFinish.Rows.Count == 0)
                {
                    return "月计划均已达成，没有需要生成的日计划";
                }

                // 如果昨天不是最后一天
                if (yesterdayDay != monthDays)
                {
                    int primaryKeyCJ = Convert.ToInt32(dtNoFinish.Rows[0]["MONTHPLANID"]);
                    accountID = Convert.ToInt32(dtNoFinish.Rows[0]["ACCOUNTID"]);
                    userID = Convert.ToInt32(dtNoFinish.Rows[0]["CREATEUSERID"]);

                    // 本月最后一天
                    DateTime enddate = Convert.ToDateTime(today.AddMonths(1).ToString("yyyy-MM-01")).AddDays(-1);
                    TimeSpan sp = enddate.Subtract(today);
                    int days = sp.Days;

                    // 获取合格率
                    DataTable dtQualifiedPercent = QualifiedPercent.GetQualifiedPercent(conn, today);

                    // 删除之后的日计划
                    result += conn.ExecuteNonQuery(@"
                        DELETE FROM TP_PC_DAILYPLANDETAIL DPD
                         WHERE DPD.DAILYPLANID IN (SELECT DP.DAILYPLANID
                                                     FROM TP_PC_DAILYPLAN DP
                                                    WHERE DP.ACCOUNTDATE > @YESTERDAY@
                                                      AND DP.ACCOUNTDATE <= LAST_DAY(@YESTERDAY@)) ",
                        new CDAParameter("YESTERDAY", yesterday, DataType.Date)
                    );

                    result += conn.ExecuteNonQuery(@"
                        DELETE FROM TP_PC_DAILYPLAN DP
                         WHERE DP.ACCOUNTDATE > @YESTERDAY@
                           AND DP.ACCOUNTDATE <= LAST_DAY(@YESTERDAY@) ",
                        new CDAParameter("YESTERDAY", yesterday, DataType.Date)
                    );

                    string sqlIniDailyPlan = @"
                    INSERT INTO TP_PC_DAILYPLAN
                        (DAILYPLANID,
                         DAILYPLANNAME,
                         MONTHPLANID,
                         ACCOUNTDATE,
                         ACCOUNTID,
                         CREATEUSERID,
                         UPDATEUSERID)
                    VALUES
                        (@DAILYPLANID@,
                         @DAILYPLANNAME@,
                         @MONTHPLANID@,
                         @ACCOUNTDATE@,
                         @ACCOUNTID@,
                         @USERID@,
                         @USERID@) ";

                    string sqlIniDailyPlanDetail = @"
                    INSERT INTO TP_PC_DAILYPLANDETAIL
                        (DAILYPLANID,
                         MATNR,
                         GOODSID,
                         LOGOID,
                         OUTQUANTITY,
                         PLANQUANTITY,
                         QUALIFIEDPERCENT,
                         ACCOUNTID,
                         CREATEUSERID,
                         UPDATEUSERID)
                    VALUES
                        (@DAILYPLANID@,
                         @MATNR@,
                         @GOODSID@,
                         @LOGOID@,
                         @OUTQUANTITY@,
                         @PLANQUANTITY@,
                         @QUALIFIEDPERCENT@,
                         @ACCOUNTID@,
                         @USERID@,
                         @USERID@) ";

                    DataRow[] rows = null;
                    for (int i = 0; i <= days; i++)
                    {
                        //获取日计划主键
                        object dailyPlanID = conn.GetSequenceNextval("SEQ_PC_DAILYPLAN");
                        DateTime dailyAccountDate = today.AddDays(i);

                        //新建日计划总单
                        result += conn.ExecuteNonQuery(sqlIniDailyPlan,
                            new CDAParameter("DAILYPLANID", dailyPlanID),
                            new CDAParameter("DAILYPLANNAME", "成检日计划" + dailyAccountDate.ToString("yyyy-MM-dd")),
                            new CDAParameter("MONTHPLANID", primaryKeyCJ),
                            new CDAParameter("ACCOUNTDATE", dailyAccountDate),
                            new CDAParameter("ACCOUNTID", accountID),
                            new CDAParameter("USERID", userID)
                            );

                        //新建日计划明细
                        foreach (DataRow drMonthPlanDetail in dtNoFinish.Rows)
                        {
                            // 日均值，向下取整
                            decimal avgquantity = Math.Floor(Convert.ToDecimal(drMonthPlanDetail["DIFFER"]) / (days + 1));
                            decimal planquantity = avgquantity;

                            if (i == days)
                            {
                                planquantity = Convert.ToDecimal(drMonthPlanDetail["DIFFER"]) - days * avgquantity;
                            }

                            // 出窑数量
                            decimal outquantity = planquantity;
                            rows = dtQualifiedPercent.Select("GOODSID = " + drMonthPlanDetail["GOODSID"]);

                            decimal qualifiedpercent = 1;
                            if (rows.Length > 0)
                            {
                                qualifiedpercent = Convert.ToDecimal(rows[0]["QUALIFIEDPERCENT"]);
                                outquantity = Math.Floor(planquantity / qualifiedpercent);
                            }

                            result += conn.ExecuteNonQuery(sqlIniDailyPlanDetail,
                                new CDAParameter("DAILYPLANID", dailyPlanID),
                                new CDAParameter("MATNR", drMonthPlanDetail["MATNR"], DataType.NVarChar),
                                new CDAParameter("GOODSID", drMonthPlanDetail["GOODSID"], DataType.Int32),
                                new CDAParameter("LOGOID", drMonthPlanDetail["LOGOID"], DataType.Int32),
                                new CDAParameter("OUTQUANTITY", outquantity, DataType.Int32),
                                new CDAParameter("PLANQUANTITY", planquantity, DataType.Int32),
                                new CDAParameter("QUALIFIEDPERCENT", qualifiedpercent * 100, DataType.Decimal),
                                new CDAParameter("ACCOUNTID", accountID),
                                new CDAParameter("USERID", userID)
                            );
                        }
                    }
                }

                if (result > 0)
                {
                    conn.Commit();
                    return "操作成功";
                }
                else
                {
                    conn.Rollback();
                    return "操作失败";
                }
            }
            catch (Exception e)
            {
                conn.Rollback();
                return "操作失败：" + e.Message;
            }
        }
    }


    /// <summary>
    /// 生成领料计划（按领料计划设置）
    /// </summary>
    /// <returns></returns>
    public static string AutoMateriaPlanOut()
    {  
        int result = 0;
        int autoid = 0;

        string ZMSG = null;
        string msg = "";
        //List<string> matnrs = new List<string>();
        //matnrs.Add("");

        DataTable dtInventory = new DataTable("T");
        dtInventory.Columns.Add("IDNRK");
        dtInventory.Columns.Add("POSITION");
        dtInventory.Rows.Add("", "1001");
        dtInventory.Rows.Add("", "1002");
        dtInventory.Rows.Add("", "1003");
        dtInventory.Rows.Add("", "1004");
        dtInventory.Rows.Add("", "1005");
        dtInventory.Rows.Add("", "1006");

        //DataTable dtSapInventory = SapApi.ZMM_WMS016("5000", dtInventory, out ZMSG);
        //dtSapInventory.DefaultView.RowFilter = "LGORT IN('1001','1002','1003','1004','1005','1006')";
        //dtSapInventory = dtSapInventory.DefaultView.ToTable();
        /*
        string tmp_sql = @"
insert into tmp_ZMM_WMS016 (WERKS, MATNR, LGORT, CHARG, LABST, GYSKC, KALAB, KYKC, SPEME, VBELN, POSNR, SOBKZ, SSNUM, WMSNUM, LIFNR, SPEME_103)
values (@WERKS@, @MATNR@, @LGORT@, @CHARG@, @LABST@, @GYSKC@, @KALAB@, @KYKC@, @SPEME@, @VBELN@, @POSNR@, @SOBKZ@, @SSNUM@, @WMSNUM@, @LIFNR@, @SPEME_103@)
";*/

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

            try
            {
                CDAParameter aid = new CDAParameter("@in_accountid@", 1);
                CDAParameter aoid = new CDAParameter("@rs_result@", 0, DataType.Int32, ParameterDirection.Output);
                result = conn.ExecuteSPNonQuery("pro_auto_pc_materiaplan_check", aid, aoid);
                autoid =Convert.ToInt32(((Oracle.ManagedDataAccess.Types.OracleDecimal)(aoid.Value)).Value);

                msg += " 【pro_auto_pc_materiaplan_check】操作成功 [" + autoid + "]";
            }
            catch (Exception e)
            {
                autoid = 0;
                msg += " 【pro_auto_pc_materiaplan_check】操作失败：" + e.Message;
            }

            if (autoid == 0)
            {
                return msg;
            }

            /* 线边仓库存，暂不启用，目前领料不用这个自动生成的单子
        try
        {
            DataTable dtSapInventory = SapApi.ZMM_WMS016("5000", dtInventory, out ZMSG);
            if (dtSapInventory != null && dtSapInventory.Rows.Count > 0)
            {
                //dtSapInventory.DefaultView.RowFilter = "LGORT IN('1001','1002','1003','1004','1005','1006')";
                //dtSapInventory.TableName = "TMP_ZMM_WMS016";
                //CDASqlStrParameter[] cpp = new CDASqlStrParameter[]
                //{
                //    CDASqlStrParameter.ForInsert("WERKS", null, DataType.NVarChar),
                //    CDASqlStrParameter.ForInsert("MATNR", null, DataType.NVarChar),
                //    CDASqlStrParameter.ForInsert("LGORT", null, DataType.NVarChar),
                //    CDASqlStrParameter.ForInsert("CHARG", null, DataType.NVarChar),
                //    CDASqlStrParameter.ForInsert("LABST", null, DataType.Decimal),
                //    CDASqlStrParameter.ForInsert("GYSKC", null, DataType.Decimal),
                //    CDASqlStrParameter.ForInsert("KALAB", null, DataType.Decimal),
                //    CDASqlStrParameter.ForInsert("KYKC", null, DataType.Decimal),
                //    CDASqlStrParameter.ForInsert("SPEME", null, DataType.Decimal),
                //    CDASqlStrParameter.ForInsert("VBELN", null, DataType.NVarChar),
                //    CDASqlStrParameter.ForInsert("POSNR", null, DataType.Decimal),
                //    CDASqlStrParameter.ForInsert("SOBKZ", null, DataType.NVarChar),
                //    CDASqlStrParameter.ForInsert("SSNUM", null, DataType.NVarChar),
                //    CDASqlStrParameter.ForInsert("WMSNUM", null, DataType.NVarChar),
                //    CDASqlStrParameter.ForInsert("LIFNR", null, DataType.NVarChar),
                //    CDASqlStrParameter.ForInsert("SPEME_103", null, DataType.Decimal),
                //};

                //result = conn.InsertForeach(dtSapInventory, cpp);
                StringBuilder tmp_sql = new StringBuilder(" insert all ");
                tmp_sql.AppendLine();
                foreach (DataRow item in dtSapInventory.Rows)
                {
                    tmp_sql.Append("into tmp_ZMM_WMS016 (WERKS, MATNR, LGORT, CHARG, LABST, GYSKC, KALAB, KYKC, SPEME, VBELN, POSNR, SOBKZ, SSNUM, WMSNUM, LIFNR, SPEME_103) values (");
                    tmp_sql.Append(GetDbValue(item["WERKS"]));
                    tmp_sql.Append(",");
                    tmp_sql.Append(GetDbValue(item["MATNR"]));
                    tmp_sql.Append(",");
                    tmp_sql.Append(GetDbValue(item["LGORT"]));
                    tmp_sql.Append(",");
                    tmp_sql.Append(GetDbValue(item["CHARG"]));
                    tmp_sql.Append(",");
                    tmp_sql.Append(GetDbValue(Convert.ToDecimal(item["LABST"])));
                    tmp_sql.Append(",");
                    tmp_sql.Append(GetDbValue(Convert.ToDecimal(item["GYSKC"])));
                    tmp_sql.Append(",");
                    tmp_sql.Append(GetDbValue(Convert.ToDecimal(item["KALAB"])));
                    tmp_sql.Append(",");
                    tmp_sql.Append(GetDbValue(Convert.ToDecimal(item["KYKC"])));
                    tmp_sql.Append(",");
                    tmp_sql.Append(GetDbValue(Convert.ToDecimal(item["SPEME"])));
                    tmp_sql.Append(",");
                    tmp_sql.Append(GetDbValue(item["VBELN"]));
                    tmp_sql.Append(",");
                    tmp_sql.Append(GetDbValue(Convert.ToDecimal(item["POSNR"])));
                    tmp_sql.Append(",");
                    tmp_sql.Append(GetDbValue(item["SOBKZ"]));
                    tmp_sql.Append(",");
                    tmp_sql.Append(GetDbValue(item["SSNUM"]));
                    tmp_sql.Append(",");
                    tmp_sql.Append(GetDbValue(item["WMSNUM"]));
                    tmp_sql.Append(",");
                    tmp_sql.Append(GetDbValue(item["LIFNR"]));
                    tmp_sql.Append(",");
                    tmp_sql.Append(GetDbValue(Convert.ToDecimal(item["SPEME_103"])));
                    tmp_sql.AppendLine(")");
                }
                tmp_sql.AppendLine("select 1 from dual");

                result = conn.ExecuteNonQuery(tmp_sql.ToString());
                msg += " 【TMP_ZMM_WMS016】操作成功";
            }
        }
        catch (Exception e)
        {
            //conn.Rollback();
            msg += " 【TMP_ZMM_WMS016】操作失败：" + e.Message;
        }
            */

            try
            {
                result += conn.ExecuteSPNonQuery("pro_auto_pc_materiaplan_out", new CDAParameter("@in_accountid@", 1), new CDAParameter("@in_autoid@", autoid));
                conn.Commit();
                msg += " 【pro_auto_pc_materiaplan_out】操作成功";
            }
            catch (Exception e)
            {
                conn.Rollback();
                msg += " 【pro_auto_pc_materiaplan_out】操作失败：" + e.Message;
            }
        }

        return msg;
    }

    private static string GetDbValue(object value)
    {
        if (value == null || value == DBNull.Value)
        {
            return "null";
        }
        if (value is string)
        {
            return "'" + value+ "'";
        }
        if (value is DateTime)
        {
            return "'" + (value as DateTime?).Value.ToString("yyyy-MM-dd hh:mm:ss.ssssss") + "'";
        }
        else
        {
            return value + "";
        }
    }
}