IMEX.mes.wcf/DK.Service/PMModuleLogic/PMModuleLogicNew.cs

/*******************************************************************************
 * Copyright(c) 2014 DongkeSoft All rights reserved. / Confidential
 * 类的信息：
 *      1.程序名称：PMModuleLogicNew.cs
 *      2.功能描述：生产管理服务端业务逻辑
 * 编辑履历：
 *      作者            日期                版本            修改内容
 *      陈晓野          2015/03/21          1.00            新建
 *******************************************************************************/

using System;
using System.Collections.Generic;
using System.Data;
using System.Text;

using Oracle.ManagedDataAccess.Client;

using Dongke.IBOSS.PRD.Basics.BaseResources;
using Dongke.IBOSS.PRD.Basics.DataAccess;
using Dongke.IBOSS.PRD.WCF.DataModels;
using Dongke.IBOSS.PRD.Service.DataModels;

namespace Dongke.IBOSS.PRD.Service.PMModuleLogic
{
    /// <summary>
    /// 生产管理服务端业务逻辑
    /// </summary>
    public static class PMModuleLogicNew
    {
        #region 注浆管理

        #region 注浆登记

        /// <summary>
        /// 取得FPM0101画面（注浆登记）的查询数据
        /// </summary>
        /// <param name="ids">多个注浆id（用[,]分开）</param>
        /// <param name="user">登录用户信息</param>
        /// <returns>查询结果</returns>
        public static DataSet GetFPM0101SData(string ids, SUserInfo user)
        {
            IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString);
            try
            {
                con.Open();
                List<OracleParameter> parameters = new List<OracleParameter>();
                StringBuilder sql = new StringBuilder(GetFPM0101SSQL());
                parameters.Add(new OracleParameter(":AccountID", OracleDbType.Int32, user.AccountID, ParameterDirection.Input));
                parameters.Add(new OracleParameter(":UPUserId", OracleDbType.Int32, user.UserID, ParameterDirection.Input));

                if (!string.IsNullOrWhiteSpace(ids))
                {
                    ids = "," + ids + ",";
                    sql.Append(" AND INSTR(:GroutingDailyIDs, ',' || GroutingDailyID || ',') > 0");
                    parameters.Add(new OracleParameter(":GroutingDailyIDs", OracleDbType.Varchar2, ids, ParameterDirection.Input));
                }

                sql.Append(" ORDER BY GDD.GroutingLineCode, GDD.GroutingDate, GDD.GroutingDailyID, GDD.GroutingMouldCode");

                DataSet ds = con.GetSqlResultToDs(sql.ToString(), parameters.ToArray());
                return ds;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (con.ConnState == ConnectionState.Open)
                {
                    con.Close();
                }
            }
        }

        /// <summary>
        /// 取得FPM0101画面（注浆登记）的查询数据
        /// </summary>
        /// <param name="se">查询条件</param>
        /// <param name="user">登录用户信息</param>
        /// <returns>查询结果</returns>
        public static DataSet GetFPM0101SData(FPM0101_SE se, SUserInfo user)
        {
            IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString);
            try
            {
                con.Open();
                List<OracleParameter> parameters = new List<OracleParameter>();
                StringBuilder sql = new StringBuilder(GetFPM0101SSQL());
                parameters.Add(new OracleParameter(":AccountID", OracleDbType.Int32, user.AccountID, ParameterDirection.Input));
                parameters.Add(new OracleParameter(":UPUserId", OracleDbType.Int32, user.UserID, ParameterDirection.Input));

                if (se != null)
                {
                    // 成型线编码
                    if (!string.IsNullOrEmpty(se.GroutingLineCode))
                    {
                        sql.Append(" AND INSTR(GDD.GroutingLineCode, :GroutingLineCode) > 0");
                        parameters.Add(new OracleParameter(":GroutingLineCode", OracleDbType.NVarchar2, se.GroutingLineCode, ParameterDirection.Input));
                    }
                    // 成型线名称
                    if (!string.IsNullOrEmpty(se.GroutingLineName))
                    {
                        sql.Append(" AND INSTR(GDD.GroutingLineName, :GroutingLineName) > 0");
                        parameters.Add(new OracleParameter(":GroutingLineName", OracleDbType.NVarchar2, se.GroutingLineName, ParameterDirection.Input));
                    }
                    // 成型线类别
                    if (se.GMouldTypeID.HasValue)
                    {
                        sql.Append(" AND GD.GMouldTypeID = :GMouldTypeID");
                        parameters.Add(new OracleParameter(":GMouldTypeID", OracleDbType.Int32, se.GMouldTypeID.Value, ParameterDirection.Input));
                    }
                    // 注浆日期-开始
                    if (se.GroutingDateBegin.HasValue)
                    {
                        sql.Append(" AND GDD.GroutingDate >= :GroutingDateBegin");
                        parameters.Add(new OracleParameter(":GroutingDateBegin", OracleDbType.Date, se.GroutingDateBegin.Value, ParameterDirection.Input));
                    }
                    // 注浆日期-结束
                    if (se.GroutingDateEnd.HasValue)
                    {
                        sql.Append(" AND GDD.GroutingDate <= :GroutingDateEnd");
                        parameters.Add(new OracleParameter(":GroutingDateEnd", OracleDbType.Date, se.GroutingDateEnd.Value, ParameterDirection.Input));
                    }
                    // 注浆模具编号
                    if (!string.IsNullOrEmpty(se.GroutingMouldCode))
                    {
                        sql.Append(" AND INSTR(GDD.GroutingMouldCode, :GroutingMouldCode) > 0");
                        parameters.Add(new OracleParameter(":GroutingMouldCode", OracleDbType.NVarchar2, se.GroutingMouldCode, ParameterDirection.Input));
                    }
                    // 产品编号
                    if (!string.IsNullOrEmpty(se.GoodsCode))
                    {
                        sql.Append(" AND INSTR(GDD.GoodsCode, :GoodsCode) > 0");
                        parameters.Add(new OracleParameter(":GoodsCode", OracleDbType.NVarchar2, se.GoodsCode, ParameterDirection.Input));
                    }
                    // 产品名称
                    if (!string.IsNullOrEmpty(se.GoodsName))
                    {
                        sql.Append(" AND INSTR(GDD.GoodsName, :GoodsName) > 0");
                        parameters.Add(new OracleParameter(":GoodsName", OracleDbType.NVarchar2, se.GoodsName, ParameterDirection.Input));
                    }
                    // 备注
                    if (!string.IsNullOrEmpty(se.Remarks))
                    {
                        sql.Append(" AND INSTR(GDD.GoodsName, :GoodsName) > 0");
                        parameters.Add(new OracleParameter(":GoodsName", OracleDbType.NVarchar2, se.GoodsName, ParameterDirection.Input));
                    }
                    // 注浆标识
                    if (!string.IsNullOrEmpty(se.GroutingFlag))
                    {
                        sql.Append(" AND GDD.GroutingFlag = :GroutingFlag");
                        parameters.Add(new OracleParameter(":GroutingFlag", OracleDbType.Char, se.GroutingFlag, ParameterDirection.Input));
                    }
                    // 损坯标识
                    if (!string.IsNullOrEmpty(se.ScrapFlag))
                    {
                        sql.Append(" AND GDD.ScrapFlag = :ScrapFlag");
                        parameters.Add(new OracleParameter(":ScrapFlag", OracleDbType.Char, se.ScrapFlag, ParameterDirection.Input));
                    }
                }

                sql.Append(" ORDER BY GDD.GroutingDate, GDD.GroutingLineCode, GDD.GroutingDailyID, GDD.GroutingMouldCode");

                DataSet ds = con.GetSqlResultToDs(sql.ToString(), parameters.ToArray());
                return ds;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (con.ConnState == ConnectionState.Open)
                {
                    con.Close();
                }
            }
        }

        /// <summary>
        /// 获取查询sql
        /// </summary>
        /// <returns>sql</returns>
        private static string GetFPM0101SSQL()
        {
            string selSql = "SELECT GDD.GroutingDailyDetailID," +
                            "       GDD.GroutingDailyID," +
                            "       GDD.GroutingLineDetailID," +
                            "       GDD.GroutingLineID," +
                            // 成型线编码
                            "       GDD.GroutingLineCode," +
                            // 成型线名称
                            "       GDD.GroutingLineName," +
                            // 注浆日期
                            "       GDD.GroutingDate," +
                            // 成型工号
                            "       GDD.UserCode," +
                            // 产品编码
                            "       GDD.GoodsCode," +
                            // 产品名称 20210728 modify lsq goodsname不取TP_PM_GroutingDailyDetail，取TP_MST_GOODS的
                            "       G.GoodsName," +
                            // 注浆线模具编码
                            "       GDD.GroutingMouldCode," +
                            // 模具状态
                            "       GMS.GMouldStatusName," +
                            // 注浆标识
                            "       GDD.GroutingFlag," +
                            // 未注浆原因
                            "       DD.DictionaryValue AS NoGroutingRreason," +
                            // 注浆次数
                            "       GDD.GroutingCount," +
                            // 损坯标识
                            "       GDD.ScrapFlag," +
                            // 备注
                            "       GDD.Remarks," +
                            // 成型线类型
                            "       GMT.GMouldTypeName," +
                            // 绑定条码" +
                            "       GP.BarCode," +
                            // 创建时间
                            "       GDD.CreateTime," +
                            // 创建工号
                            "       U.UserName AS Createuser" +
                            // 注浆日报明细表
                            "  FROM TP_PM_GroutingDailyDetail GDD" +
                            //20210728 modify lsq goodsname不取TP_PM_GroutingDailyDetail，取TP_MST_GOODS的
                            //BEGIN
                            " INNER JOIN TP_MST_Goods G" +
                            "    ON G.GoodsID = GDD.GoodsID" +
                            //END
                            // 注浆日报
                            " INNER JOIN TP_PM_GroutingDaily GD" +
                            "    ON GD.GroutingDailyID = GDD.GroutingDailyID" +
                            // 成型线类型
                            " INNER JOIN TP_MST_GMouldType GMT" +
                            "    ON GD.GMouldTypeID = GMT.GMouldTypeID" +
                            // 注浆条码
                            "  LEFT JOIN TP_PM_GroutingProduct GP" +
                            "    ON GP.GroutingDailyDetailID = GDD.GroutingDailyDetailID" +
                            // 模具状态
                            "  INNER JOIN TP_SYS_GMouldStatus GMS" +
                            "    ON GMS.GMouldStatusID = GDD.GMouldStatus" +
                            // 未注浆原因
                            "  LEFT JOIN TP_MST_DataDictionary DD" +
                            "    ON DD.DictionaryID = GDD.NoGroutingRreason" +
                            // 创建工号
                            " INNER JOIN TP_MST_User U" +
                            "    ON U.UserID = GDD.CreateUserID" +
                            // 账套
                            " WHERE GDD.AccountID = :AccountID" +
                            "   AND GDD.ValueFlag = '" + (int)Constant.ValueFlag.Effective + "'" +
                            // 成型线操作权限
                            "   AND EXISTS (SELECT UP.PurviewID" +
                            "          FROM TP_MST_UserPurview UP" +
                            "         WHERE UP.PurviewType = '" + (int)Constant.PurviewType.OperateGroutingLine + "'" +
                            "           AND UP.PurviewID = GDD.GroutingLineID" +
                            "           AND UP.UserId = :UPUserId)";
            return selSql;
        }

        #endregion 注浆登记

        #endregion 注浆管理
    }
}