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

using System;
using System.Collections.Generic;
using System.Data;
using Curtain.DataAccess;

/// <summary>
/// 自动生成下月计划
/// </summary>
public class CreateMonthPlan
{
    /// <summary>
    /// 自动生成下月计划
    /// </summary>
    /// <returns></returns>
    public static string CreateNextMonthPlan(DateTime accountdatebegin)
    {
        string accountMonth = accountdatebegin.ToString("yyyy-MM");
        DateTime accountdateend = accountdatebegin.AddMonths(1).AddDays(-1);
        int accountID = 1, userID = 2;  // 账套、用户ID
        int result = 0;

        using (IDataAccess conn = DataAccess.Create())
        {
            conn.BeginTransaction();
            try
            {
                object isExists = conn.ExecuteScalar(@"
                    SELECT 1
                      FROM TP_PC_MONTHPLAN
                     WHERE ACCOUNTMONTH = @ACCOUNTMONTH@ ",
                    new CDAParameter("ACCOUNTMONTH", accountMonth)
                );

                if (isExists != null)
                {
                    return "已经生成";
                }

                // 获取采购订单
                DataTable dtPMCOrder = conn.ExecuteDatatable(@"
                    SELECT I.EBELN,
                           I.EBELP,
                           P.ETENR,
                           I.MATNR,
                           P.MENGE
                      FROM TP_SAP_ITEM I
                     INNER JOIN TP_SAP_PLAN P
                        ON P.EBELP = I.EBELP
                       AND P.EBELN = I.EBELN
                     WHERE P.VALUEFLAG = '1'
                      -- AND I.PLANFLAG = '1'
                       AND P.EINDT >= @EINDTBEGIN@
                       AND P.EINDT <= @EINDTEND@ ",
                    new CDAParameter("EINDTBEGIN", accountdatebegin),
                    new CDAParameter("EINDTEND", accountdateend)
                );


                /*************************************************** 建包装计划begin *************************************************/
                object primaryKeyBZ = conn.GetSequenceNextval("SEQ_PC_MONTHPLAN");
                result += conn.ExecuteNonQuery(@"
                    INSERT INTO TP_PC_MONTHPLAN (
                        MONTHPLANID,
                        ACCOUNTMONTH,
                        MONTHPLANNAME,
                        PROCEDUREID,
                        SAPDATEBEGIN,
                        SAPDATEEND,
                        ACCOUNTID,
                        CREATEUSERID,
                        UPDATEUSERID
                    ) VALUES (
                        @MONTHPLANID@,
                        @ACCOUNTMONTH@,
                        @MONTHPLANNAME@,
                        107,
                        @SAPDATEBEGIN@,
                        @SAPDATEEND@,
                        @ACCOUNTID@,
                        @USERID@,
                        @USERID@) ",
                    new CDAParameter("MONTHPLANID", primaryKeyBZ),
                    new CDAParameter("ACCOUNTMONTH", accountMonth),
                    new CDAParameter("MONTHPLANNAME", "包装计划" + accountMonth),
                    new CDAParameter("SAPDATEBEGIN", accountdatebegin),
                    new CDAParameter("SAPDATEEND", accountdateend),
                    new CDAParameter("ACCOUNTID", accountID),
                    new CDAParameter("USERID", userID)
                );

                string sqlIniMonthPlanDetail = @"
                INSERT INTO TP_PC_MONTHPLANDETAIL (
                    MONTHPLANID,
                    MATNR,
                    MENGE,
                    PMCORDERNO,
                    EBELP,
                    ACCOUNTID,
                    CREATEUSERID,
                    UPDATEUSERID)
                VALUES (
                    @MONTHPLANID@,
                    @MATNR@,
                    @MENGE@,
                    @PMCORDERNO@,
                    @EBELP@,
                    @ACCOUNTID@,
                    @USERID@,
                    @USERID@) ";

                foreach (DataRow drPMCOrder in dtPMCOrder.Rows)
                {
                    result += conn.ExecuteNonQuery(sqlIniMonthPlanDetail,
                        new CDAParameter("MONTHPLANID", primaryKeyBZ),
                        new CDAParameter("MATNR", drPMCOrder["MATNR"]),
                        new CDAParameter("MENGE", drPMCOrder["MENGE"]),
                        new CDAParameter("PMCORDERNO", drPMCOrder["EBELN"]),
                        new CDAParameter("EBELP", drPMCOrder["EBELP"]),
                        new CDAParameter("ACCOUNTID", accountID),
                        new CDAParameter("USERID", userID)
                    );
                }
                /*************************************************** 建包装计划end ***************************************************/


                /*************************************************** 建成检计划begin *************************************************/
                object primaryKeyCJ = conn.GetSequenceNextval("SEQ_PC_MONTHPLAN");
                result += conn.ExecuteNonQuery(@"
                    INSERT INTO TP_PC_MONTHPLAN (
                        MONTHPLANID,
                        ACCOUNTMONTH,
                        MONTHPLANNAME,
                        PROCEDUREID,
                        TODAILYFLAG,
                        ACCOUNTID,
                        CREATEUSERID,
                        UPDATEUSERID
                    ) VALUES (
                        @MONTHPLANID@,
                        @ACCOUNTMONTH@,
                        @MONTHPLANNAME@,
                        104,
                        '1',
                        @ACCOUNTID@,
                        @USERID@,
                        @USERID@
                    ) ",

                    new CDAParameter("MONTHPLANID", primaryKeyCJ),
                    new CDAParameter("ACCOUNTMONTH", accountMonth),
                    new CDAParameter("MONTHPLANNAME", "成检计划" + accountMonth),
                    new CDAParameter("ACCOUNTID", accountID),
                    new CDAParameter("USERID", userID)
                );

                result += conn.ExecuteNonQuery(@"
                    INSERT INTO TP_PC_MONTHPLANDETAIL
                        (MONTHPLANID,
                            MATNR,
                            MENGE,
                            ACCOUNTID,
                            CREATEUSERID,
                            UPDATEUSERID)
                        SELECT @MONTHPLANID@,
                                MATNR,
                                SUM(MENGE),
                                @ACCOUNTID@,
                                @USERID@,
                                @USERID@
                            FROM TP_PC_MONTHPLANDETAIL
                            WHERE MONTHPLANID = @MONTHPLANIDBZ@
                            GROUP BY MATNR ",
                    new CDAParameter("MONTHPLANID", primaryKeyCJ),
                    new CDAParameter("ACCOUNTID", accountID),
                    new CDAParameter("USERID", userID),
                    new CDAParameter("MONTHPLANIDBZ", primaryKeyBZ)
                );

                DataTable dtCJMonthPlanDetail = conn.ExecuteDatatable(@"
                        SELECT MPD.DETAILID
                          ,PB.GOODSID
                          ,MPD.MENGE
                      FROM TP_PC_MONTHPLANDETAIL MPD
                      LEFT JOIN TP_MST_PACKINGBOM PB
                        ON PB.MATNR = MPD.MATNR
                     WHERE MPD.MONTHPLANID = @MONTHPLANID@ ",
                    new CDAParameter("MONTHPLANID", primaryKeyCJ)
                );

                string sqlUpdate = @"
                UPDATE TP_PC_MONTHPLANDETAIL
                   SET OUTQUANTITY      = @OUTQUANTITY@
                      ,QUALIFIEDPERCENT = @QUALIFIEDPERCENT@
                 WHERE DETAILID = @DETAILID@ ";

                // 获取合格率
                DataTable dtQualifiedPercent = QualifiedPercent.GetQualifiedPercent(conn, accountdatebegin);
                DataRow[] rows;
                decimal planquantity;
                foreach (DataRow item in dtCJMonthPlanDetail.Rows)
                {
                    // 数量
                    decimal.TryParse(item["MENGE"] + "", out planquantity);
                    decimal outquantity = planquantity;
                    rows = dtQualifiedPercent.Select("GOODSID = " + item["GOODSID"]);

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

                    result = conn.ExecuteNonQuery(sqlUpdate,
                        new CDAParameter("OUTQUANTITY", outquantity, DataType.Int32),
                        new CDAParameter("QUALIFIEDPERCENT", qualifiedpercent * 100, DataType.Decimal),
                        new CDAParameter("DETAILID", item["DETAILID"])
                    );
                }

                /*************************************************** 生成成检日计划begin **********************************************/
                DataTable dtMonthPlanDetail = conn.ExecuteDatatable(@"
                    SELECT MPD.DETAILID,
                           MPD.MATNR,
                           PB.GOODSID,
                           PB.LOGOID,
                           MPD.MENGE
                      FROM TP_PC_MONTHPLANDETAIL MPD
                      LEFT JOIN (SELECT PB.MATNR,
                                        GOODSID,
                                        LOGOID
                                   FROM TP_MST_PACKINGBOM PB
                                  GROUP BY PB.MATNR,
                                           GOODSID,
                                           LOGOID) PB
                        ON PB.MATNR = MPD.MATNR
                     WHERE MPD.MONTHPLANID = @MONTHPLANID@ ",
                    new CDAParameter("MONTHPLANID", primaryKeyCJ)
                );

                DataRow[] drMonthPlanDetails = dtMonthPlanDetail.Select("GOODSID IS NULL OR LOGOID IS NULL");
                if (drMonthPlanDetails.Length > 0)
                {
                    string message = string.Empty;
                    foreach (DataRow row in drMonthPlanDetails)
                    {
                        message += row["MATNR"] + "，";
                    }
                    return "存在无法绑定产品的物料编码：" + message;
                }

                DateTime begindate = Convert.ToDateTime(accountMonth + "-01");
                DateTime enddate = begindate.AddMonths(1).AddDays(-1);

                // 获取合格率
                TimeSpan sp = enddate.Subtract(begindate);
                int days = sp.Days;

                if (days > 0)
                {
                    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,
                         MONTHPLANDETAILID,
                         MATNR,
                         GOODSID,
                         LOGOID,
                         OUTQUANTITY,
                         PLANQUANTITY,
                         QUALIFIEDPERCENT,
                         ACCOUNTID,
                         CREATEUSERID,
                         UPDATEUSERID)
                    VALUES
                        (@DAILYPLANID@,
                         @MONTHPLANDETAILID@,
                         @MATNR@,
                         @GOODSID@,
                         @LOGOID@,
                         @OUTQUANTITY@,
                         @PLANQUANTITY@,
                         @QUALIFIEDPERCENT@,
                         @ACCOUNTID@,
                         @USERID@,
                         @USERID@) ";

                    rows = null;
                    for (int i = 0; i <= days; i++)
                    {
                        //获取日计划主键
                        object dailyPlanID = conn.GetSequenceNextval("SEQ_PC_DAILYPLAN");
                        DateTime dailyAccountDate = begindate.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 dtMonthPlanDetail.Rows)
                        {
                            // 日均值，向下取整
                            decimal avgquantity = Math.Floor(Convert.ToDecimal(drMonthPlanDetail["MENGE"]) / (days + 1));
                            planquantity = avgquantity;

                            if (i == days)
                            {
                                planquantity = Convert.ToDecimal(drMonthPlanDetail["MENGE"]) - 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("MONTHPLANDETAILID", drMonthPlanDetail["DETAILID"], DataType.Int32),
                                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)
                            );
                        }
                    }
                }
                /*************************************************** 生成成检日计划end ***************************************************/


                /*************************************************** 更新成检月计划出窑量begin ********************************************/
                result += conn.ExecuteNonQuery(@"
                    UPDATE TP_PC_MONTHPLANDETAIL T
                       SET (T.OUTQUANTITY, T.QUALIFIEDPERCENT) =
                           (SELECT SUM(DPD.OUTQUANTITY),
                                   MAX(QUALIFIEDPERCENT)
                              FROM TP_PC_DAILYPLANDETAIL DPD
                             WHERE DPD.MONTHPLANDETAILID = T.DETAILID)
                     WHERE T.MONTHPLANID = @MONTHPLANID@ ",
                    new CDAParameter("MONTHPLANID", primaryKeyCJ)
                );
                /*************************************************** 更新成检月计划出窑量end ***********************************************/

                List<string> ebelns = new List<string>();

                // 更新采购订单明细生成标识
                foreach (DataRow row in dtPMCOrder.Rows)
                {
                    string order = row["EBELN"] + "";
                    if (!ebelns.Contains(order))
                    {
                        ebelns.Add(order);
                    }

                    result += conn.ExecuteNonQuery(@"
                        UPDATE TP_SAP_PLAN T
                           SET T.MONTHPLANFLAG = '1'
                         WHERE T.EBELN = @EBELN@
                           AND T.EBELP = @EBELP@ 
                           AND T.ETENR = @ETENR@ ",
                        new CDAParameter("EBELN", order),
                        new CDAParameter("EBELP", row["EBELP"]),
                        new CDAParameter("ETENR", row["ETENR"])
                    );
                }

                // 更新采购订单总表生成标识
                foreach (string order in ebelns)
                {
                    result += conn.ExecuteNonQuery(@"
                        UPDATE TP_SAP_HEAD
                           SET CREATEFLAG =
                               (SELECT DECODE(COUNT(1), 0, '2', '1')
                                  FROM TP_SAP_PLAN P
                                 WHERE P.MONTHPLANFLAG = '0'
                                   AND P.EBELN = @EBELN@)
                         WHERE EBELN = @EBELN@ ",
                        new CDAParameter("EBELN", order)
                    );
                }

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