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

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

/// <summary>
/// 同步WMS系统条码
/// </summary>
public class SyncWMS
{
    /// <summary>
    /// 同步WMS系统条码到产成品表
    /// </summary>
    /// <returns></returns>
    public static string SyncWMSToFinishedProduct(DateTime dateBegin, DateTime dateEnd)
    {
        DateTime beginMonthDate = dateBegin.AddDays(1 - dateBegin.Day);         // 月初
        DateTime endMonthDate = beginMonthDate.AddMonths(1).AddDays(-1);        // 月末

        DataTable dtWMS = null;
        int result = 0;

        /******************************************************* 调WMS系统，查里面有的条码begin **************************************/
        string connStr = @"Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=172.17.193.152)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=wmsdpdb.hegii.com)));User Id=wms_dkselect;Password=Wmshgdk0815";
        using (IDataAccess connWMS = DataAccess.CreateByString(DataBaseType.Oracle, connStr))
        {
            string sqlStringWMS = @"
            SELECT T.*
              FROM (SELECT DAS.SERIALNO,
                           MIN(DAS.SCANTIME) AS SCANTIME
                      FROM WMS_HG.DOC_ASN_SERIALNO DAS
                      LEFT JOIN WMS_HG.BAS_ZTBARCODE BZ
                        ON DAS.SERIALNO = BZ.SERIALNO
                     WHERE BZ.SPS in ( '5000', '5020')
                       AND DAS.SCANTIME >= @DATEBEGIN@
                       AND DAS.SCANTIME < @DATEEND@
                     GROUP BY DAS.SERIALNO) T
             ORDER BY T.SCANTIME ";

            dtWMS = connWMS.ExecuteDatatable(sqlStringWMS,
                new CDAParameter("DATEBEGIN", dateBegin, DataType.Date),
                new CDAParameter("DATEEND", dateEnd.AddDays(1), DataType.Date)
            );
        }

        using (IDataAccess connFinishedProductWMS = DataAccess.Create())
        {
            if (dtWMS != null && dtWMS.Rows.Count > 0)
            {
                // 更新条码上的同步WMS标识语句
                string updateFinishedProductIsWMSFlag = @"
                UPDATE TP_PM_FINISHEDPRODUCT FP
                   SET FP.ISWMSFLAG = '1',
                       FP.SCANTIME  = @SCANTIME@
                 WHERE EXISTS (SELECT 1
                          FROM TP_PM_GROUTINGDAILYDETAIL GDD
                         WHERE GDD.GROUTINGDAILYDETAILID = FP.GROUTINGDAILYDETAILID
                           AND GDD.OUTLABELCODE = @OUTLABELCODE@) ";

                foreach (DataRow row in dtWMS.Rows)
                {
                    // 更新条码上的同步WMS标识
                    result += connFinishedProductWMS.ExecuteNonQuery(updateFinishedProductIsWMSFlag,
                        new CDAParameter("SCANTIME", row["SCANTIME"]),
                        new CDAParameter("OUTLABELCODE", row["SERIALNO"])
                    );
                }
            }
        }

        /******************************************************* 调WMS系统，查里面有的条码end *****************************************/

        using (IDataAccess conn = DataAccess.Create())
        {
            try
            {
                // 开启事务
                conn.BeginTransaction();

                /*************************************************** 获取需要同步的sap采购订单明细begin ************************************/
                // 采购订单交货日期都是月末，要查出本月所有订单
                string sqlString = @"
                SELECT I.EBELP,
                       I.EBELN,
                       I.MATNR,
                       P.MENGE,
                       0 AS ZYSSL
                  FROM TP_SAP_ITEM I
                 INNER JOIN TP_SAP_PLAN P
                    ON P.EBELP = I.EBELP
                   AND P.EBELN = I.EBELN
                 WHERE P.EINDT >= @EINDTBEGIN@
                   AND P.EINDT <= @EINDTEND@
                 ORDER BY P.EINDT DESC,
                          I.EBELN DESC ";

                DataTable dtSapOrderDetail = conn.ExecuteDatatable(sqlString,
                    new CDAParameter("EINDTBEGIN", beginMonthDate, DataType.Date),
                    new CDAParameter("EINDTEND", endMonthDate, DataType.Date)
                );

                // 调SAP接口，查完工数量
                if (dtSapOrderDetail != null && dtSapOrderDetail.Rows.Count > 0)
                {
                    // 提取采购订单编码
                    List<string> ebelns = new List<string>();
                    foreach (DataRow row in dtSapOrderDetail.Rows)
                    {
                        if (!ebelns.Contains(row["EBELN"] + ""))
                        {
                            ebelns.Add(row["EBELN"] + "");
                        }
                    }

                    // 查完工量
                    DataTable dtSapOrder = null;
                    DataTable dtTemp = null;
                    string ZTYPE;
                    string ZMSG;
                    foreach (string ebeln in ebelns)
                    {
                        dtTemp = SapApi.ZMMFM_MES_POCX(ebeln, out ZTYPE, out ZMSG);
                        if (dtSapOrder == null)
                        {
                            dtSapOrder = dtTemp;
                        }
                        else
                        {
                            dtSapOrder.Merge(dtTemp);
                        }
                    }

                    // 更新同步时的完工数量语句
                    string updateSapOrder = @"
                    UPDATE TP_SAP_ITEM
                       SET FINISHMENGE = @FINISHMENGE@
                     WHERE EBELP = @EBELP@
                       AND EBELN = @EBELN@";

                    if (dtSapOrder.Rows.Count > 0)
                    {
                        DataRow[] drs = null;
                        decimal finishmenge = 0;
                        foreach (DataRow row in dtSapOrderDetail.Rows)
                        {
                            drs = dtSapOrder.Select("EBELN = '" + row["EBELN"] + "' AND EBELP = '" + row["EBELP"] + "'");
                            if (drs.Length > 0)
                            {
                                decimal.TryParse(drs[0]["ZYSSL"] + "", out finishmenge);
                                row["ZYSSL"] = finishmenge;

                                // 更新同步时的完工数量语句
                                result += conn.ExecuteNonQuery(updateSapOrder,
                                    new CDAParameter("FINISHMENGE", finishmenge),
                                    new CDAParameter("EBELP", row["EBELP"]),
                                    new CDAParameter("EBELN", row["EBELN"])
                                );
                            }
                        }
                    }
                }
                /*************************************************** 获取需要同步的sap采购订单明细end **************************************/


                /*************************************************** 获取这段时间交接的条码begin ******************************************/
                // 先更掉不是WMS系统的
                string updateNotWMS = @"
                UPDATE TP_PM_FINISHEDPRODUCT FP
                   SET FP.EBELN = NULL,
                       FP.EBELP = NULL
                 WHERE FP.ISWMSFLAG = '0'
                   AND FP.EBELN IS NOT NULL
                   AND EXISTS (SELECT 1
                          FROM TP_SAP_ITEM I
                         WHERE I.EBELN = FP.EBELN
                           AND I.EBELP = FP.EBELP
                          -- AND I.PLANFLAG = '1'
                           AND I.EINDT >= @EINDTBEGIN@
                           AND I.EINDT < @EINDTEND@) ";

                // 更新条码上的采购订单号和销售行号
                result += conn.ExecuteNonQuery(updateNotWMS,
                    new CDAParameter("EINDTBEGIN", beginMonthDate, DataType.Date),
                    new CDAParameter("EINDTEND", endMonthDate, DataType.Date)
                );

                // 取本月有差额的订单明细
                sqlString = @"
                SELECT T2.*,
                       T2.FINISHMENGE - T2.CC AS DIFFER
                  FROM (SELECT I.EBELN,
                               I.EBELP,
                               I.MATNR,
                               I.FINISHMENGE,
                               NVL(T.CC, 0) AS CC
                          FROM TP_SAP_ITEM I
                          LEFT JOIN (SELECT EBELN,
                                           EBELP,
                                           COUNT(1) AS CC
                                      FROM TP_PM_FINISHEDPRODUCT
                                     WHERE EBELN IS NOT NULL
                                     GROUP BY EBELN,
                                              EBELP) T
                            ON T.EBELN = I.EBELN
                           AND T.EBELP = I.EBELP
                         WHERE I.EINDT >= @EINDTBEGIN@
                           AND I.EINDT <= @EINDTEND@) T2
                 WHERE T2.FINISHMENGE <> T2.CC ";

                DataTable dtDifferOrder = conn.ExecuteDatatable(sqlString,
                    new CDAParameter("EINDTBEGIN", beginMonthDate, DataType.Date),
                    new CDAParameter("EINDTEND", endMonthDate, DataType.Date)
                );
                /*************************************************** 获取这段时间交接的条码end ********************************************/

                string updateFinishedProduct = @"
                UPDATE TP_PM_FINISHEDPRODUCT F
                   SET F.EBELN = @EBELN@,
                       F.EBELP = @EBELP@
                 WHERE F.EBELN IS NULL
                   AND EXISTS
                 (SELECT 1
                          FROM (SELECT T2.BARCODE
                                  FROM (SELECT ROWNUM AS CC,
                                               T.BARCODE
                                          FROM (SELECT FP.BARCODE
                                                  FROM TP_PM_FINISHEDPRODUCT FP
                                                 INNER JOIN TP_PM_GROUTINGDAILYDETAIL GDD
                                                    ON GDD.GROUTINGDAILYDETAILID = FP.GROUTINGDAILYDETAILID
                                                 WHERE FP.ISWMSFLAG = '1'
                                                   AND FP.EBELN IS NULL
                                                   AND GDD.MATERIALCODE = @MATNR@
                                                 ORDER BY FP.SCANTIME DESC) T) T2
                                 WHERE T2.CC <= @DIFFER@) T3
                         WHERE T3.BARCODE = F.BARCODE) ";

                string updateFinishedProduct2 = @"
                UPDATE TP_PM_FINISHEDPRODUCT F
                   SET F.EBELN = @EBELN@,
                       F.EBELP = @EBELP@
                 WHERE F.EBELN IS NULL
                   AND EXISTS
                 (SELECT 1
                          FROM (SELECT T2.BARCODE
                                  FROM (SELECT ROWNUM AS CC,
                                               T.BARCODE
                                          FROM (SELECT FP.BARCODE
                                                  FROM TP_PM_FINISHEDPRODUCT FP
                                                 INNER JOIN TP_PM_GROUTINGDAILYDETAIL GDD
                                                    ON GDD.GROUTINGDAILYDETAILID = FP.GROUTINGDAILYDETAILID
                                                 WHERE FP.ISWMSFLAG = '0'
                                                   AND FP.EBELN IS NULL
                                                   AND GDD.MATERIALCODE = @MATNR@
                                                 ORDER BY FP.CREATETIME DESC) T) T2
                                 WHERE T2.CC <= @DIFFER@) T3
                         WHERE T3.BARCODE = F.BARCODE) ";

                int executeRows, differ, differ2;
                foreach (DataRow drSapOrderDetail in dtDifferOrder.Rows)
                {
                    int.TryParse(drSapOrderDetail["DIFFER"] + "", out differ);

                    // 更新条码上的采购订单号和销售行号
                    executeRows = conn.ExecuteNonQuery(updateFinishedProduct,
                        new CDAParameter("EBELN", drSapOrderDetail["EBELN"]),
                        new CDAParameter("EBELP", drSapOrderDetail["EBELP"]),
                        new CDAParameter("MATNR", drSapOrderDetail["MATNR"]),
                        new CDAParameter("DIFFER", differ)
                    );

                    if (executeRows < differ)
                    {
                        differ2 = differ - executeRows;
                        executeRows = conn.ExecuteNonQuery(updateFinishedProduct2,
                            new CDAParameter("EBELN", drSapOrderDetail["EBELN"]),
                            new CDAParameter("EBELP", drSapOrderDetail["EBELP"]),
                            new CDAParameter("MATNR", drSapOrderDetail["MATNR"]),
                            new CDAParameter("DIFFER", differ2)
                        );
                    }
                }


                /*************************************************** 更新条码上的采购订单号和销售行号begin **********************************/
                //decimal zyssl = 0;
                //decimal differ = 0;
                //DataRow[] rows = null;

                //// 更新条码上的采购订单号和销售行号语句
                //string updateFinishedProduct = @"
                //UPDATE TP_PM_FINISHEDPRODUCT
                //   SET EBELN = @EBELN@,
                //       EBELP = @EBELP@
                // WHERE BARCODE = @BARCODE@ ";

                //// 循环采购订单明细
                //foreach (DataRow drSapOrderDetail in dtSapOrderDetail.Rows)
                //{
                //    // 达成数
                //    decimal.TryParse(drSapOrderDetail["ZYSSL"] + "", out zyssl);

                //    // 当前订单明细的所有条码
                //    rows = dtFinishedProduct.Select("EBELN = '" + drSapOrderDetail["EBELN"] + "' AND EBELP = '" + drSapOrderDetail["EBELP"] + "'");

                //    // 如果条码数大于等于达成数，就不用同步了，跳出当前行，执行下一行
                //    if (rows.Length >= zyssl)
                //    {
                //        continue;
                //    }

                //    // 达成数与条码数的差额
                //    differ = zyssl - rows.Length;

                //    // 筛选出当前物料编码，还没同步的条码
                //    rows = dtFinishedProduct.Select("EBELN IS NULL AND EBELP IS NULL AND MATERIALCODE = '" + drSapOrderDetail["MATNR"] + "'", "SCANTIME DESC");
                //    for (int i = 0; i < differ; i++)
                //    {
                //        // 如果条码不够了，就跳出当前循环
                //        if (rows.Length <= i)
                //        {
                //            break;
                //        }

                //        rows[i]["EBELN"] = drSapOrderDetail["EBELN"];
                //        rows[i]["EBELP"] = drSapOrderDetail["EBELP"];

                //        // 更新条码上的采购订单号和销售行号
                //        result += conn.ExecuteNonQuery(updateFinishedProduct,
                //            new CDAParameter("EBELN", drSapOrderDetail["EBELN"]),
                //            new CDAParameter("EBELP", drSapOrderDetail["EBELP"]),
                //            new CDAParameter("BARCODE", rows[i]["BARCODE"])
                //        );
                //    }
                //}
                /*************************************************** 更新条码上的采购订单号和销售行号end ************************************/

                if (result > 0)
                {
                    conn.Commit();
                    return "操作成功";

                }
                else
                {
                    conn.Rollback();
                    return "操作失败";
                }
            }
            catch (Exception e)
            {
                conn.Rollback();
                return "操作失败：" + e.Message;
            }
        }
    }
}