HEGII.ss.mes.wcf/DK.Service/PCModuleService/PCModuleLogic.cs

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

using Dongke.IBOSS.PRD.Basics.BaseResources;
using Dongke.IBOSS.PRD.Basics.DataAccess;
using Dongke.IBOSS.PRD.Service.CMNModuleService;
using Dongke.IBOSS.PRD.Service.DataModels;
using Dongke.IBOSS.PRD.WCF.DataModels;
using Dongke.WinForm.Utilities;
using Oracle.ManagedDataAccess.Client;
using SAP.Middleware.Connector;
using System;
using System.Collections.Generic;
using System.Data;
using System.Text;

namespace Dongke.IBOSS.PRD.Service.PCModuleService

{
	/// <summary>
	/// 生产配置服务端业务逻辑
	/// </summary>
	public static class PCModuleLogic
    {
        //SAP服务器配置参数
        //public static string appServerHost = ConfigurationManager.AppSettings["SapAppServerHost"].ToString();
        //public static string systemNumber = ConfigurationManager.AppSettings["SapSystemNumber"].ToString();
        //public static string user = ConfigurationManager.AppSettings["SapUser"].ToString();
        //public static string password = ConfigurationManager.AppSettings["SapPassword"].ToString();
        //public static string client = ConfigurationManager.AppSettings["SapClient"].ToString();
        //SAP服务器配置参数
        public static Basics.Library.INIUtility ini = Basics.Library.INIUtility.Instance(Basics.Library.INIUtility.IniFile.SAP_HEGII);
        public static string appServerHost = ini.ReadIniData("SAP_RFC_INFO", "SapAppServerHost");
        public static string systemNumber = ini.ReadIniData("SAP_RFC_INFO", "SapSystemNumber");
        public static string user = ini.ReadIniData("SAP_RFC_INFO", "SapUser");
        public static string password = ini.ReadIniData("SAP_RFC_INFO", "SapPassword");
        public static string client = ini.ReadIniData("SAP_RFC_INFO", "SapClient");


        #region 成型线管理

        #region 成型线一览
        /// <summary>
        /// 取得FPC0101画面（成型线管理）的初始化数据
        /// </summary>
        /// <param name="user">登录用户信息</param>
        /// <returns>初始化数据</returns>
        public static ServiceResultEntity GetFPC0101IData(SUserInfo user)
        {
            IDBConnection conn = null;
            try
            {
                conn = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString);
                DataTable type = CMNModuleLogic.GetTP_MST_GMouldType(conn, user, Constant.ValueFlag.Effective);
                DataTable status = CMNModuleLogic.GetTP_SYS_GMouldStatus(conn, true);
                ServiceResultEntity sre = new ServiceResultEntity();
                sre.Data = new DataSet();
                sre.Data.Tables.Add(type);
                sre.Data.Tables.Add(status);

                return sre;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (conn != null &&
                    conn.ConnState == ConnectionState.Open)
                {
                    conn.Close();
                }
            }
        }

        /// <summary>
        /// 取得FPC0101画面（成型线管理）的查询数据
        /// </summary>
        /// <param name="user">登录用户信息</param>
        /// <param name="se">查询条件</param>
        /// <returns>查询结果</returns>
        public static ServiceResultEntity GetFPC0101SData(SUserInfo user, FPC0101_SE se)
        {
            IDBConnection conn = null;
            try
            {
                conn = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString);
                List<OracleParameter> parameters = new List<OracleParameter>();
                StringBuilder sql = new StringBuilder(PCModuleLogic.GetGroutingLineSQL());
                parameters.Add(new OracleParameter(":AccountID", OracleDbType.Int32, user.AccountID, ParameterDirection.Input));

                if (se != null)
                {
                    // 成型线范围权限
                    if (se.PurviewType.HasValue)
                    {
                        sql.Append("   AND EXISTS (SELECT UP.PurviewID");
                        sql.Append("          FROM TP_MST_UserPurview UP");
                        sql.Append("         WHERE UP.PurviewType = '" + (se.PurviewType.Value ? (int)Constant.PurviewType.OperateGroutingLine : (int)Constant.PurviewType.ViewGroutingLine) + "'");
                        sql.Append("           AND (UP.PurviewID = -1 OR UP.PurviewID = gl.GroutingLineID)");
                        sql.Append("           AND UP.UserId = :UPUserId) ");
                        parameters.Add(new OracleParameter(":UPUserId", OracleDbType.Int32, user.UserID, ParameterDirection.Input));
                    }
                    // 楼号
                    if (!string.IsNullOrEmpty(se.BuildingNo))
                    {
                        sql.Append(" AND INSTR(gl.BuildingNo, :BuildingNo) > 0");
                        parameters.Add(new OracleParameter(":BuildingNo", OracleDbType.NVarchar2, se.BuildingNo, ParameterDirection.Input));
                    }
                    // 楼层
                    if (!string.IsNullOrEmpty(se.FloorNo))
                    {
                        sql.Append(" AND INSTR(gl.FloorNo, :FloorNo) > 0");
                        parameters.Add(new OracleParameter(":FloorNo", OracleDbType.NVarchar2, se.FloorNo, ParameterDirection.Input));
                    }
                    // 线号
                    if (!string.IsNullOrEmpty(se.GroutingLineNo))
                    {
                        sql.Append(" AND INSTR(gl.GroutingLineNo, :GroutingLineNo) > 0");
                        parameters.Add(new OracleParameter(":GroutingLineNo", OracleDbType.NVarchar2, se.GroutingLineNo, ParameterDirection.Input));
                    }
                    // 成型线编码
                    if (!string.IsNullOrEmpty(se.GroutingLineCode))
                    {
                        sql.Append(" AND INSTR(gl.GroutingLineCode, :GroutingLineCode) > 0");
                        parameters.Add(new OracleParameter(":GroutingLineCode", OracleDbType.NVarchar2, se.GroutingLineCode, ParameterDirection.Input));
                    }
                    // 成型线名称
                    if (!string.IsNullOrEmpty(se.GroutingLineName))
                    {
                        sql.Append(" AND INSTR(gl.GroutingLineName, :GroutingLineName) > 0");
                        parameters.Add(new OracleParameter(":GroutingLineName", OracleDbType.NVarchar2, se.GroutingLineName, ParameterDirection.Input));
                    }
                    // 成型线类别
                    if (se.GMouldTypeID.HasValue)
                    {
                        sql.Append(" AND gl.GMouldTypeID = :GMouldTypeID");
                        parameters.Add(new OracleParameter(":GMouldTypeID", OracleDbType.Int32, se.GMouldTypeID.Value, ParameterDirection.Input));
                    }
                    // 成型线状态
                    if (se.GMouldStatus.HasValue)
                    {
                        sql.Append(" AND gl.GMouldStatus = :GMouldStatus");
                        parameters.Add(new OracleParameter(":GMouldStatus", OracleDbType.Int32, se.GMouldStatus.Value, ParameterDirection.Input));
                    }
                    // 成型工号
                    if (!string.IsNullOrEmpty(se.GUserCode))
                    {
                        sql.Append(" AND INSTR(U.UserCode, :GroutingUserCode) > 0");
                        parameters.Add(new OracleParameter(":GroutingUserCode", OracleDbType.NVarchar2, se.GUserCode, ParameterDirection.Input));
                    }
                    // 开始使用日期-开始
                    if (se.BeginUsedDateBegin.HasValue)
                    {
                        sql.Append(" AND gl.BeginUsedDate >= :BeginUsedDateBegin");
                        parameters.Add(new OracleParameter(":BeginUsedDateBegin", OracleDbType.Date, se.BeginUsedDateBegin.Value, ParameterDirection.Input));
                    }
                    // 开始使用日期-结束
                    if (se.BeginUsedDateEnd.HasValue)
                    {
                        sql.Append(" AND gl.BeginUsedDate <= :BeginUsedDateEnd");
                        parameters.Add(new OracleParameter(":BeginUsedDateEnd", OracleDbType.Date, se.BeginUsedDateEnd.Value, ParameterDirection.Input));
                    }
                    // 结束使用日期-开始
                    if (se.EndUsedDateBegin.HasValue)
                    {
                        sql.Append(" AND gl.EndUsedDate >= :EndUsedDateBegin");
                        parameters.Add(new OracleParameter(":EndUsedDateBegin", OracleDbType.Date, se.EndUsedDateBegin.Value, ParameterDirection.Input));
                    }
                    // 结束使用日期-结束
                    if (se.EndUsedDateEnd.HasValue)
                    {
                        sql.Append(" AND gl.EndUsedDate <= :EndUsedDateEnd");
                        parameters.Add(new OracleParameter(":EndUsedDateEnd", OracleDbType.Date, se.EndUsedDateEnd.Value, ParameterDirection.Input));
                    }
                    // 产品编号
                    if (!string.IsNullOrEmpty(se.GoodsCode))
                    {
                        sql.Append(" AND EXISTS(SELECT 1");
                        sql.Append("              FROM tp_pc_groutinglinedetail gdd");
                        sql.Append("             INNER JOIN tp_mst_goods g");
                        sql.Append("                ON g.goodsid = gdd.goodsid");
                        sql.Append("             WHERE gdd.groutinglineid = gl.groutinglineid");
                        sql.Append("               AND INSTR(g.GoodsCode, :GoodsCode) > 0");
                        sql.Append("           )");
                        parameters.Add(new OracleParameter(":GoodsCode", OracleDbType.NVarchar2, se.GoodsCode, ParameterDirection.Input));
                    }
                    // 备注
                    if (!string.IsNullOrEmpty(se.Remarks))
                    {
                        sql.Append(" AND INSTR(gl.Remarks, :Remarks) > 0");
                        parameters.Add(new OracleParameter(":Remarks", OracleDbType.NVarchar2, se.Remarks, ParameterDirection.Input));
                    }
                }

                sql.Append(" ORDER BY gl.GroutingLineCode");
                DataTable data = conn.GetSqlResultToDt(sql.ToString(), parameters.ToArray());

                ServiceResultEntity sre = new ServiceResultEntity();
                if (data == null || data.Rows.Count == 0)
                {
                    sre.Status = Constant.ServiceResultStatus.NoSearchResults;
                    return sre;
                }
                sre.Data = new DataSet();
                sre.Data.Tables.Add(data);

                OracleParameter[] paras = new OracleParameter[]
                {
                    new OracleParameter(":GroutingLineID", OracleDbType.Int32, data.Rows[0]["GroutingLineID"], ParameterDirection.Input),
                };
                DataTable dData = conn.GetSqlResultToDt(PCModuleLogic.GetGroutingLineDetailSQL(), paras);

                if (dData != null)
                {
                    sre.Data.Tables.Add(dData);
                }

                return sre;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (conn != null &&
                    conn.ConnState == ConnectionState.Open)
                {
                    conn.Close();
                }
            }
        }

        /// <summary>
        /// 取得FPC0101画面（成型线管理）的查询数据
        /// </summary>
        /// <param name="user">登录用户信息</param>
        /// <param name="ids">多个注浆id（用[,]分开）</param>
        /// <returns>查询结果</returns>
        public static ServiceResultEntity GetFPC0101SData(SUserInfo user, string ids)
        {
            IDBConnection conn = null;
            try
            {
                conn = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString);
                List<OracleParameter> parameters = new List<OracleParameter>();
                StringBuilder sql = new StringBuilder(PCModuleLogic.GetGroutingLineSQL());
                parameters.Add(new OracleParameter(":AccountID", OracleDbType.Int32, user.AccountID, ParameterDirection.Input));

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

                sql.Append(" ORDER BY gl.GroutingLineCode");
                DataTable data = conn.GetSqlResultToDt(sql.ToString(), parameters.ToArray());

                ServiceResultEntity sre = new ServiceResultEntity();
                if (data == null || data.Rows.Count == 0)
                {
                    sre.Status = Constant.ServiceResultStatus.NoSearchResults;
                    return sre;
                }
                sre.Data = new DataSet();
                sre.Data.Tables.Add(data);

                OracleParameter[] paras = new OracleParameter[]
                {
                    new OracleParameter(":GroutingLineID", OracleDbType.Int32, data.Rows[0]["GroutingLineID"], ParameterDirection.Input),
                };
                DataTable dData = conn.GetSqlResultToDt(PCModuleLogic.GetGroutingLineDetailSQL(), paras);

                if (dData != null)
                {
                    sre.Data.Tables.Add(dData);
                }

                return sre;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (conn != null &&
                    conn.ConnState == ConnectionState.Open)
                {
                    conn.Close();
                }
            }
        }

        /// <summary>
        /// 取得FPC0101画面（成型线管理）的查询成型线明细数据
        /// </summary>
        /// <param name="user">登录用户信息</param>
        /// <param name="id">成型线ID</param>
        /// <returns>成型线明细数据</returns>
        public static ServiceResultEntity GetFPC0101SDData(SUserInfo user, int id)
        {
            IDBConnection conn = null;
            try
            {
                conn = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString);

                OracleParameter[] paras = new OracleParameter[]
                {
                    new OracleParameter(":GroutingLineID", OracleDbType.Int32, id, ParameterDirection.Input),
                };

                ServiceResultEntity sre = new ServiceResultEntity();
                sre.Data = conn.GetSqlResultToDs(PCModuleLogic.GetGroutingLineDetailSQL(), paras);

                return sre;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (conn != null &&
                    conn.ConnState == ConnectionState.Open)
                {
                    conn.Close();
                }
            }
        }

        /// <summary>
        /// 获取查询sql
        /// </summary>
        /// <returns>sql</returns>
        private static string GetGroutingLineSQL()
        {
            string selSql =
                "SELECT gl.GroutingLineID," +
                "       gl.BuildingNo," +
                "       gl.FloorNo," +
                "       gl.GroutingLineNo," +
                "       gl.GroutingLineCode," +
                "       gl.GroutingLineName," +
                "       gl.MouldQuantity," +
                "       gl.userid," +
                "       u.usercode," +
                "       u.username," +
                "       gl.gmouldtypeid," +
                "       gmt.gmouldtypename," +
                "       gl.gmouldstatus," +
                "       gms.gmouldstatusname," +
                "       gl.beginuseddate," +
                "       gl.enduseddate," +
                "       gl.remarks," +
                "       gl.OPTimeStamp" +
                "  FROM tp_pc_groutingline gl" +
                " INNER JOIN tp_mst_gmouldtype gmt" +
                "    ON gl.gmouldtypeid = gmt.gmouldtypeid" +
                " INNER JOIN tp_sys_gmouldstatus gms" +
                "    ON gl.gmouldstatus = gms.gmouldstatusid" +
                " INNER JOIN tp_mst_user u" +
                "    ON u.userid = gl.userid" +
                // 账套
                " WHERE gl.AccountID = :AccountID" +
                "   AND gl.ValueFlag = '" + (int)Constant.ValueFlag.Effective + "'";
            return selSql;
        }

        /// <summary>
        /// 获取查询sql
        /// </summary>
        /// <returns>sql</returns>
        private static string GetGroutingLineDetailSQL()
        {
            string selSql =
                "select gld.groutinglinedetailid," +
                "       gld.groutinglineid," +
                "       gld.groutingmouldcode," +
                "       gld.goodsid," +
                "       g.goodscode," +
                "       g.goodsname," +
                "       g.goodsspecification," +
                "       gld.groutingcount," +
                "       gld.gmouldstatus," +
                "       gms.gmouldstatusname," +
                "       gld.beginuseddate," +
                "       gld.mouldcode," +
                "       gld.remarks" +
                "  from tp_pc_groutinglinedetail gld" +
                " inner join tp_mst_goods g" +
                "    on g.goodsid = gld.goodsid" +
                " inner join tp_mst_goodstype gt" +
                "    on gt.goodstypeid = g.goodstypeid" +
                " inner join tp_sys_gmouldstatus gms" +
                "    on gld.gmouldstatus = gms.gmouldstatusid" +
                " where gld.groutinglineid = :GroutingLineID" +
                "   and gld.ValueFlag = '" + (int)Constant.ValueFlag.Effective + "'" +
                " order by gld.groutingmouldcode";

            return selSql;
        }

        /// <summary>
        /// FPC0101画面（成型线管理）停用、启用成型线
        /// </summary>
        /// <param name="user">登录用户信息</param>
        /// <param name="groutingLineID">成型线ID</param>
        /// <param name="opTimeStamp">时间戳</param>
        /// <param name="valueFlag">有效标识</param>
        /// <returns>操作结果</returns>
        public static ServiceResultEntity SetFPC0101DataStatus(SUserInfo user, int groutingLineID, object opTimeStamp, Constant.ValueFlag valueFlag)
        {
            IDBTransaction tran = null;
            try
            {
                ServiceResultEntity sre = new ServiceResultEntity();

                tran = ClsDbFactory.CreateDBTransaction(DataBaseType.ORACLE, DataManager.ConnectionString);
                int executeResult = 0;

                #region 校验时间戳
                // 校验时间戳
                string checkSql =
                    "SELECT GroutingLineID" +
                    "  FROM TP_PC_GroutingLine" +
                    " WHERE GroutingLineID = :GroutingLineID" +
                    "   AND OPTimeStamp = :OPTimeStamp" +
                    "   AND ValueFlag = '" + (int)Constant.ValueFlag.Effective + "'";
                OracleParameter[] checkParas = new OracleParameter[]
                {
                    new OracleParameter(":GroutingLineID", OracleDbType.Int32, groutingLineID, ParameterDirection.Input),
                    new OracleParameter(":OPTimeStamp", OracleDbType.TimeStamp, opTimeStamp, ParameterDirection.Input),
                };
                object checkResult = tran.GetSqlResultToObj(checkSql, checkParas);
                if (checkResult == null)
                {
                    sre.Status = Constant.ServiceResultStatus.DataChanged;
                    return sre;
                }
                #endregion 校验时间戳
                StringBuilder sql = new StringBuilder("UPDATE TP_PC_GroutingLine");
                if (valueFlag == Constant.ValueFlag.Invalid)
                {
                    sql.Append("   SET GMouldStatus = '" + (int)Constant.GMouldStatus.Disable + "',");
                    sql.Append("       EndUsedDate = to_date(sysdate, 'yyyy-mm-dd')");
                    sql.Append(" WHERE GMouldStatus = '" + (int)Constant.GMouldStatus.Normal + "'");
                }
                else if (valueFlag == Constant.ValueFlag.Effective)
                {
                    sql.Append("   SET GMouldStatus = '" + (int)Constant.GMouldStatus.Normal + "',");
                    sql.Append("       BeginUsedDate = to_date(sysdate, 'yyyy-mm-dd'),");
                    sql.Append("       EndUsedDate = null");
                    sql.Append(" WHERE GMouldStatus = '" + (int)Constant.GMouldStatus.Disable + "'");
                }
                sql.Append(" AND GroutingLineID = :GroutingLine");

                OracleParameter[] paras = new OracleParameter[]
                {
                    new OracleParameter(":GroutingLineID", OracleDbType.Int32, groutingLineID, ParameterDirection.Input),
                };
                executeResult = tran.ExecuteNonQuery(sql.ToString(), paras);
                if (executeResult <= 0)
                {
                    sre.Status = Constant.ServiceResultStatus.NoModifyData;
                    return sre;
                }

                tran.Commit();
                return sre;
            }
            catch (Exception ex)
            {
                if (tran != null &&
                    tran.ConnState == ConnectionState.Open)
                {
                    tran.Rollback();
                }
                throw ex;
            }
            finally
            {
                if (tran != null &&
                    tran.ConnState == ConnectionState.Open)
                {
                    tran.Disconnect();
                }
            }
        }
        #endregion 成型线一览

        #region 成型线
        /// <summary>
        /// 取得FPC0102画面（成型线管理）的查询数据
        /// </summary>
        /// <param name="user">登录用户信息</param>
        /// <param name="groutingLineID">成型线ID</param>
        /// <returns>查询结果</returns>
        public static ServiceResultEntity GetFPC0102Data(SUserInfo user, int groutingLineID)
        {
            IDBConnection conn = null;
            try
            {
                conn = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString);
                List<OracleParameter> parameters = new List<OracleParameter>();
                StringBuilder sql = new StringBuilder(PCModuleLogic.GetGroutingLineSQL());
                sql.Append(" AND gl.GroutingLineID = :GroutingLineID");
                parameters.Add(new OracleParameter(":AccountID", OracleDbType.Int32, user.AccountID, ParameterDirection.Input));
                parameters.Add(new OracleParameter(":GroutingLineID", OracleDbType.Int32, groutingLineID, ParameterDirection.Input));

                DataTable data = conn.GetSqlResultToDt(sql.ToString(), parameters.ToArray());

                ServiceResultEntity sre = new ServiceResultEntity();
                sre.Data = new DataSet();
                if (data != null)
                {
                    sre.Data.Tables.Add(data);
                }

                OracleParameter[] paras = new OracleParameter[]
                {
                    new OracleParameter(":GroutingLineID", OracleDbType.Int32, groutingLineID, ParameterDirection.Input),
                };
                DataTable dData = conn.GetSqlResultToDt(PCModuleLogic.GetGroutingLineDetailSQL(), paras);

                if (dData != null)
                {
                    dData.Columns["GroutingMouldCode"].Unique = true;
                    sre.Data.Tables.Add(dData);
                }

                return sre;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (conn != null &&
                    conn.ConnState == ConnectionState.Open)
                {
                    conn.Close();
                }
            }
        }

        /// <summary>
        /// 保存FPC0102画面（成型线管理）的数据
        /// </summary>
        /// <param name="user">登录用户信息</param>
        /// <param name="groutingLineInfo">成型线数据</param>
        /// <returns>保存结果</returns>
        public static ServiceResultEntity SetFPC0102Data(SUserInfo user, DataSet groutingLineInfo)
        {
            IDBTransaction tran = null;
            try
            {
                ServiceResultEntity sre = new ServiceResultEntity();
                if (groutingLineInfo == null ||
                    groutingLineInfo.Tables.Count != 2 ||
                    groutingLineInfo.Tables[0].Rows.Count < 1 ||
                    groutingLineInfo.Tables[1].Rows.Count < 1)
                {
                    sre.Status = Constant.ServiceResultStatus.NoModifyData;
                    return sre;
                }

                DataRow line = groutingLineInfo.Tables[0].Rows[0];
                DataTable detail = groutingLineInfo.Tables[1];
                int lineID = 0;
                object id = line["GroutingLine"];
                if (id != null && id != DBNull.Value)
                {
                    lineID = Convert.ToInt32(id);
                }

                tran = ClsDbFactory.CreateDBTransaction(DataBaseType.ORACLE, DataManager.ConnectionString);
                int executeResult = 0;

                // 新建成型线
                if (lineID < 1)
                {
                    #region 数据校验 成型线编码唯一
                    // 成型线编码唯一
                    string checkLineCodeSql =
                        "SELECT GL.GroutingLineCode" +
                        "  FROM TP_PM_GroutingLine GL" +
                        " WHERE GL.AccountID = :AccountID" +
                        "   AND GL.GroutingLineCode = :GroutingLineCode";
                    OracleParameter[] checkLineCodeParas = new OracleParameter[]
                    {
                        new OracleParameter(":AccountID", OracleDbType.Int32, user.AccountID, ParameterDirection.Input),
                        new OracleParameter(":GroutingLineCode", OracleDbType.NVarchar2, line["GroutingLineCode"], ParameterDirection.Input),
                    };
                    DataTable checkLineCodeData = tran.GetSqlResultToDt(checkLineCodeSql, checkLineCodeParas);
                    if (checkLineCodeData != null && checkLineCodeData.Rows.Count > 0)
                    {
                        sre.Status = Constant.ServiceResultStatus.DataDuplicated;
                        sre.Message = string.Format("（成型线编码：{0}）", line["GroutingLineCode"]);
                    }
                    #endregion 数据校验 成型线编码唯一

                    #region 新建成型线
                    // 新建成型线
                    #region SQL
                    string seqGroutingLineSql =
                        "select SEQ_PC_GroutingLine_ID.Nextval from dual;";
                    #region LineSQL
                    string insertLineSQL =
                        "insert into tp_pc_groutingline" +
                        "  (groutinglineid," +
                        "   buildingno," +
                        "   floorno," +
                        "   groutinglineno," +
                        "   groutinglinecode," +
                        "   groutinglinename," +
                        "   mouldquantity," +
                        "   gmouldtypeid," +
                        "   userid," +
                        //"   MonitorID," +
                        "   beginuseddate," +
                        "   enduseddate," +
                        "   gmouldstatus," +
                        "   remarks," +
                        "   accountid," +
                        "   valueflag," +
                        "   createtime," +
                        "   createuserid," +
                        "   updatetime," +
                        "   updateuserid," +
                        "   optimestamp)" +
                        "values" +
                        "  (:groutinglineid," +
                        "   :buildingno," +
                        "   :floorno," +
                        "   :groutinglineno," +
                        "   :groutinglinecode," +
                        "   :groutinglinename," +
                        "   :mouldquantity," +
                        "   :gmouldtypeid," +
                        "   :userid," +
                        //"   :MonitorID," +
                        "   to_date(sysdate, 'yyyy-mm-dd')," +
                        "   null," +
                        "   '" + (int)Constant.GMouldStatus.Normal + "'," +
                        "   :remarks," +
                        "   :accountid," +
                        "   '" + (int)Constant.ValueFlag.Effective + "'," +
                        "   sysdate," +
                        "   :opuserid," +
                        "   sysdate," +
                        "   :opuserid," +
                        "   systimestamp);";
                    #endregion LineSQL
                    #region DetailSQL
                    string insertDetailSQL =
                        "insert into tp_pc_groutinglinedetail" +
                        "  (groutinglineid," +
                        "   groutingmouldcode," +
                        "   mouldcode," +
                        "   userid," +
                        "   goodsid," +
                        "   groutingcount," +
                        "   gmouldstatus," +
                        "   beginuseddate," +
                        "   enduseddate," +
                        "   remarks," +
                        "   accountid," +
                        "   valueflag," +
                        "   createtime," +
                        "   createuserid," +
                        "   updatetime," +
                        "   updateuserid," +
                        "   optimestamp)" +
                        "values" +
                        "  (:groutinglineid," +
                        "   :groutingmouldcode," +
                        "   :mouldcode," +
                        "   :userid," +
                        "   :goodsid," +
                        "   0," +
                        "   '" + (int)Constant.GMouldStatus.Normal + "'," +
                        "   to_date(sysdate, 'yyyy-mm-dd')," +
                        "   null," +
                        "   :remarks," +
                        "   :accountid," +
                        "   '" + (int)Constant.ValueFlag.Effective + "'," +
                        "   sysdate," +
                        "   :opuserid," +
                        "   sysdate," +
                        "   :opuserid," +
                        "   systimestamp);";
                    #endregion DetailSQL
                    #endregion SQL

                    object glSeq = tran.GetSqlResultToObj(seqGroutingLineSql);

                    OracleParameter[] insertLineParas = new OracleParameter[]
                    {
                        new OracleParameter(":groutinglineid", OracleDbType.Int32, glSeq, ParameterDirection.Input),
                        new OracleParameter(":buildingno", OracleDbType.NVarchar2, line["buildingno"], ParameterDirection.Input),
                        new OracleParameter(":floorno", OracleDbType.NVarchar2, line["floorno"], ParameterDirection.Input),
                        new OracleParameter(":groutinglineno", OracleDbType.NVarchar2, line["groutinglineno"], ParameterDirection.Input),
                        new OracleParameter(":groutinglinecode", OracleDbType.NVarchar2, line["groutinglinecode"], ParameterDirection.Input),
                        new OracleParameter(":groutinglinename", OracleDbType.NVarchar2, line["groutinglinename"], ParameterDirection.Input),
                        new OracleParameter(":mouldquantity", OracleDbType.Decimal, line["mouldquantity"], ParameterDirection.Input),
                        new OracleParameter(":gmouldtypeid", OracleDbType.Int32, line["gmouldtypeid"], ParameterDirection.Input),
                        new OracleParameter(":userid", OracleDbType.Int32, line["userid"], ParameterDirection.Input),
                        new OracleParameter(":userid", OracleDbType.Int32, line["userid"], ParameterDirection.Input),
                        new OracleParameter(":remarks", OracleDbType.NVarchar2, line["remarks"], ParameterDirection.Input),
                        new OracleParameter(":accountid", OracleDbType.Int32, user.AccountID, ParameterDirection.Input),
                        new OracleParameter(":opuserid", OracleDbType.Int32, user.UserID, ParameterDirection.Input),
                    };
                    executeResult = tran.ExecuteNonQuery(insertLineSQL, insertLineParas);
                    if (executeResult <= 0)
                    {
                        sre.Status = Constant.ServiceResultStatus.NoModifyData;
                        return sre;
                    }

                    foreach (DataRow row in detail.Rows)
                    {
                        if (row.RowState != DataRowState.Added)
                        {
                            continue;
                        }

                        OracleParameter[] insertDetailParas = new OracleParameter[]
                        {
                            new OracleParameter(":groutinglineid", OracleDbType.Int32, glSeq, ParameterDirection.Input),
                            new OracleParameter(":groutingmouldcode", OracleDbType.NVarchar2, row["groutingmouldcode"], ParameterDirection.Input),
                            new OracleParameter(":mouldcode", OracleDbType.NVarchar2, row["mouldcode"], ParameterDirection.Input),
                            new OracleParameter(":userid", OracleDbType.Int32, row["userid"], ParameterDirection.Input),
                            new OracleParameter(":goodsid", OracleDbType.Int32, row["goodsid"], ParameterDirection.Input),
                            new OracleParameter(":remarks", OracleDbType.NVarchar2, row["remarks"], ParameterDirection.Input),
                            new OracleParameter(":accountid", OracleDbType.Int32, user.AccountID, ParameterDirection.Input),
                            new OracleParameter(":opuserid", OracleDbType.Int32, user.UserID, ParameterDirection.Input),
                        };
                        executeResult = tran.ExecuteNonQuery(insertDetailSQL, insertDetailParas);
                        if (executeResult <= 0)
                        {
                            sre.Status = Constant.ServiceResultStatus.NoModifyData;
                            return sre;
                        }
                    }
                    #endregion 新建成型线
                }
                // 编辑成型线
                else
                {
                    #region 校验时间戳
                    string checkSql =
                        "SELECT GroutingLineID" +
                        "  FROM TP_PC_GroutingLine" +
                        " WHERE GroutingLineID = :GroutingLineID" +
                        "   AND OPTimeStamp = :OPTimeStamp" +
                        "   AND ValueFlag = '" + (int)Constant.ValueFlag.Effective + "'";
                    OracleParameter[] checkParas = new OracleParameter[]
                    {
                        new OracleParameter(":GroutingLineID", OracleDbType.Int32, lineID, ParameterDirection.Input),
                        new OracleParameter(":OPTimeStamp", OracleDbType.TimeStamp, line["OPTimeStamp"], ParameterDirection.Input),
                    };
                    object checkResult = tran.GetSqlResultToObj(checkSql, checkParas);
                    if (checkResult == null)
                    {
                        sre.Status = Constant.ServiceResultStatus.DataChanged;
                        return sre;
                    }
                    #endregion 校验时间戳

                    #region 编辑成型线
                    #region LineSQL
                    string updateLineSQL =
                        "update tp_pc_groutingline" +
                        "   set groutinglinename = :groutinglinename," +
                        "       mouldquantity    = :mouldquantity," +
                        "       gmouldtypeid     = :gmouldtypeid," +
                        "       userid           = :userid," +
                        "       remarks          = :remarks," +
                        "       updateuserid     = :opuserid" +
                        " where groutinglineid = :groutinglineid;";
                    #endregion LineSQL
                    #region DetailSQL
                    string insertDetailSQL =
                        "insert into tp_pc_groutinglinedetail" +
                        "  (groutinglineid," +
                        "   groutingmouldcode," +
                        "   mouldcode," +
                        "   userid," +
                        "   goodsid," +
                        "   groutingcount," +
                        "   gmouldstatus," +
                        "   beginuseddate," +
                        "   enduseddate," +
                        "   remarks," +
                        "   accountid," +
                        "   valueflag," +
                        "   createtime," +
                        "   createuserid," +
                        "   updatetime," +
                        "   updateuserid," +
                        "   optimestamp)" +
                        "values" +
                        "  (:groutinglineid," +
                        "   :groutingmouldcode," +
                        "   :mouldcode," +
                        "   :userid," +
                        "   :goodsid," +
                        "   0," +
                        "   '" + (int)Constant.GMouldStatus.Normal + "'," +
                        "   to_date(sysdate, 'yyyy-mm-dd')," +
                        "   null," +
                        "   :remarks," +
                        "   :accountid," +
                        "   '" + (int)Constant.ValueFlag.Effective + "'," +
                        "   sysdate," +
                        "   :opuserid," +
                        "   sysdate," +
                        "   :opuserid," +
                        "   systimestamp);";
                    string updateDetailSQL =
                        "update tp_pc_groutinglinedetail" +
                        "   set remarks = :remarks, updateuserid = :opuserid" +
                        " where groutinglinedetailid = :groutinglinedetailid;";
                    #endregion DetailSQL

                    OracleParameter[] updateLineParas = new OracleParameter[]
                    {
                        new OracleParameter(":groutinglineid", OracleDbType.Int32, lineID, ParameterDirection.Input),
                        new OracleParameter(":mouldquantity", OracleDbType.Decimal, line["mouldquantity"], ParameterDirection.Input),
                        new OracleParameter(":gmouldtypeid", OracleDbType.Int32, line["gmouldtypeid"], ParameterDirection.Input),
                        new OracleParameter(":userid", OracleDbType.Int32, line["userid"], ParameterDirection.Input),
                        new OracleParameter(":remarks", OracleDbType.NVarchar2, line["remarks"], ParameterDirection.Input),
                        new OracleParameter(":opuserid", OracleDbType.Int32, user.UserID, ParameterDirection.Input),
                    };
                    executeResult = tran.ExecuteNonQuery(updateLineSQL, updateLineParas);
                    if (executeResult <= 0)
                    {
                        sre.Status = Constant.ServiceResultStatus.NoModifyData;
                        return sre;
                    }

                    foreach (DataRow row in detail.Rows)
                    {
                        if (row.RowState == DataRowState.Added)
                        {
                            OracleParameter[] insertDetailParas = new OracleParameter[]
                            {
                                new OracleParameter(":groutinglineid", OracleDbType.Int32, lineID, ParameterDirection.Input),
                                new OracleParameter(":groutingmouldcode", OracleDbType.NVarchar2, row["groutingmouldcode"], ParameterDirection.Input),
                                new OracleParameter(":mouldcode", OracleDbType.NVarchar2, row["mouldcode"], ParameterDirection.Input),
                                new OracleParameter(":userid", OracleDbType.Int32, row["userid"], ParameterDirection.Input),
                                new OracleParameter(":goodsid", OracleDbType.Int32, row["goodsid"], ParameterDirection.Input),
                                new OracleParameter(":remarks", OracleDbType.NVarchar2, row["remarks"], ParameterDirection.Input),
                                new OracleParameter(":accountid", OracleDbType.Int32, user.AccountID, ParameterDirection.Input),
                                new OracleParameter(":opuserid", OracleDbType.Int32, user.UserID, ParameterDirection.Input),
                            };
                            executeResult = tran.ExecuteNonQuery(insertDetailSQL, insertDetailParas);
                            if (executeResult <= 0)
                            {
                                sre.Status = Constant.ServiceResultStatus.NoModifyData;
                                return sre;
                            }
                        }

                        if (row.RowState == DataRowState.Modified)
                        {
                            OracleParameter[] updateDetailParas = new OracleParameter[]
                            {
                                new OracleParameter(":groutinglinedetailid", OracleDbType.Int32, row["groutinglinedetailid"], ParameterDirection.Input),
                                new OracleParameter(":remarks", OracleDbType.NVarchar2, row["remarks"], ParameterDirection.Input),
                                new OracleParameter(":opuserid", OracleDbType.Int32, user.UserID, ParameterDirection.Input),
                            };
                            executeResult = tran.ExecuteNonQuery(updateDetailSQL, updateDetailParas);
                            if (executeResult <= 0)
                            {
                                sre.Status = Constant.ServiceResultStatus.NoModifyData;
                                return sre;
                            }
                        }
                    }
                    #endregion 编辑成型线
                }

                tran.Commit();
                return sre;
            }
            catch (Exception ex)
            {
                if (tran != null &&
                    tran.ConnState == ConnectionState.Open)
                {
                    tran.Rollback();
                }
                throw ex;
            }
            finally
            {
                if (tran != null &&
                    tran.ConnState == ConnectionState.Open)
                {
                    tran.Disconnect();
                }
            }
        }
        #endregion 成型线

        #region 模具管理

        #endregion 模具管理

        #endregion 成型线管理

        #region 班次配置管理

        /// <summary>
        /// 取得FPC0401画面（班次配置）的查询数据
        /// </summary>
        /// <param name="user">登录用户信息</param>
        /// <param name="se">查询条件</param>
        /// <returns>查询结果</returns>
        public static ServiceResultEntity GetFPC0401SData(SUserInfo user, FPC0401_SE se)
        {
            IDBConnection conn = null;
            try
            {
                conn = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString);

                StringBuilder sql = new StringBuilder(PCModuleLogic.GetClassesSettingSQL());
                List<OracleParameter> parameters = new List<OracleParameter>();
                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.UserCode))
                    {
                        sql.Append(" AND INSTR(U.UserCode, :UserCode) > 0");
                        parameters.Add(new OracleParameter(":UserCode", OracleDbType.NVarchar2, se.UserCode, ParameterDirection.Input));
                    }
                    // 开始时间
                    if (se.DateBegin.HasValue)
                    {
                        sql.Append(" AND C.AccountDate >= :DateBegin");
                        parameters.Add(new OracleParameter(":DateBegin", OracleDbType.Date, se.DateBegin, ParameterDirection.Input));
                    }
                    // 结束时间
                    if (se.DateEnd.HasValue)
                    {
                        sql.Append(" AND C.AccountDate <= :DateEnd");
                        parameters.Add(new OracleParameter(":DateEnd", OracleDbType.Date, se.DateEnd, ParameterDirection.Input));
                    }
                    // 员工工号
                    if (!string.IsNullOrEmpty(se.Remarks))
                    {
                        sql.Append(" AND INSTR(C.Remarks, :Remarks) > 0");
                        parameters.Add(new OracleParameter(":Remarks", OracleDbType.NVarchar2, se.Remarks, ParameterDirection.Input));
                    }
                }
                sql.Append(" ORDER BY C.AccountDate, U.UserCode, C.ClassesSettingID");
                DataTable data = conn.GetSqlResultToDt(sql.ToString(), parameters.ToArray());

                ServiceResultEntity sre = new ServiceResultEntity();
                if (data == null || data.Rows.Count == 0)
                {
                    //sre.Status = Constant.ServiceResultStatus.NoSearchResults;
                    return sre;
                }
                sre.Data = new DataSet();
                sre.Data.Tables.Add(data);

                OracleParameter[] paras = new OracleParameter[]
                {
                    new OracleParameter(":ClassesSettingID", OracleDbType.Int32, data.Rows[0]["ClassesSettingID"], ParameterDirection.Input),
                };
                DataTable dData = conn.GetSqlResultToDt(PCModuleLogic.GetClassesDetailSQL(), paras);

                if (dData != null)
                {
                    sre.Data.Tables.Add(dData);
                }

                return sre;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (conn != null &&
                    conn.ConnState == ConnectionState.Open)
                {
                    conn.Close();
                }
            }
        }

        /// <summary>
        /// 取得FPC0401画面（班次配置）的查询数据
        /// </summary>
        /// <param name="user">登录用户信息</param>
        /// <param name="ids">班次配置ID（多个用【，】隔开）</param>
        /// <returns>查询结果</returns>
        public static ServiceResultEntity GetFPC0401SData(SUserInfo user, string ids)
        {
            IDBConnection conn = null;
            try
            {
                conn = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString);

                StringBuilder sql = new StringBuilder(PCModuleLogic.GetClassesSettingSQL());
                List<OracleParameter> parameters = new List<OracleParameter>();
                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(:ClassesSettingIDs, ',' || C.ClassesSettingID || ',') > 0");
                    parameters.Add(new OracleParameter(":ClassesSettingIDs", OracleDbType.Varchar2, ids, ParameterDirection.Input));
                }

                sql.Append(" ORDER BY C.AccountDate, U.UserCode, C.ClassesSettingID");
                DataTable data = conn.GetSqlResultToDt(sql.ToString(), parameters.ToArray());

                ServiceResultEntity sre = new ServiceResultEntity();
                if (data == null || data.Rows.Count == 0)
                {
                    sre.Status = Constant.ServiceResultStatus.NoSearchResults;
                    return sre;
                }
                sre.Data = new DataSet();
                sre.Data.Tables.Add(data);

                OracleParameter[] paras = new OracleParameter[]
                {
                    new OracleParameter(":ClassesSettingID", OracleDbType.Int32, data.Rows[0]["ClassesSettingID"], ParameterDirection.Input),
                };
                DataTable dData = conn.GetSqlResultToDt(PCModuleLogic.GetClassesDetailSQL(), paras);

                if (dData != null)
                {
                    sre.Data.Tables.Add(data);
                }

                return sre;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (conn != null &&
                    conn.ConnState == ConnectionState.Open)
                {
                    conn.Close();
                }
            }
        }

        /// <summary>
        /// 取得FPC0401画面（班次配置明细）的查询数据
        /// </summary>
        /// <param name="user">登录用户信息</param>
        /// <param name="id">班次配置ID</param>
        /// <returns>班次配置明细</returns>
        public static ServiceResultEntity GetFPC0401SDData(SUserInfo user, int id)
        {
            IDBConnection conn = null;
            try
            {
                conn = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString);

                OracleParameter[] paras = new OracleParameter[]
                {
                    new OracleParameter(":ClassesSettingID", OracleDbType.Int32, id, ParameterDirection.Input),
                };

                ServiceResultEntity sre = new ServiceResultEntity();
                sre.Data = conn.GetSqlResultToDs(PCModuleLogic.GetClassesDetailSQL(), paras);

                return sre;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (conn != null &&
                    conn.ConnState == ConnectionState.Open)
                {
                    conn.Close();
                }
            }
        }

        /// <summary>
        /// 取得FPC0402画面（班次配置）的查询数据
        /// </summary>
        /// <param name="user">登录用户信息</param>
        /// <param name="id">班次配置ID</param>
        /// <returns>班次配置明细</returns>
        public static ServiceResultEntity GetFPC0402SData(SUserInfo user, int id)
        {
            IDBConnection conn = null;
            try
            {
                conn = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString);

                OracleParameter[] paras = new OracleParameter[]
                {
                    new OracleParameter(":userid", OracleDbType.Int32, id, ParameterDirection.Input),
                };

                ServiceResultEntity sre = new ServiceResultEntity();
                sre.Data = conn.GetSqlResultToDs(PCModuleLogic.GetClassUserStaffSQL(), paras);

                return sre;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (conn != null &&
                    conn.ConnState == ConnectionState.Open)
                {
                    conn.Close();
                }
            }
        }

        /// <summary>
        /// 取得FPC0401画面，班次配置查询SQL
        /// </summary>
        /// <returns>班次配置查询SQL</returns>
        private static string GetClassesSettingSQL()
        {
            string sqlString =
                "SELECT C.ClassesSettingID, " +
                "       C.AccountDate, " +
                "       U.UserID," +
                "       U.UserCode, " +
                "       U.UserName, " +
                "       O.OrganizationCode, " +
                "       O.OrganizationName, " +
                "       C.Remarks, " +
                "       C.ValueFlag, " +
                "       C.CreateTime, " +
                "       CU.UserName AS CreateUserName " +
                "  FROM TP_PC_ClassesSetting C " +
                " INNER JOIN TP_MST_User U " +
                "    ON U.UserID = C.UserID " +
                " INNER JOIN TP_MST_Organization O " +
                "    ON O.OrganizationID = C.OrganizationID " +
                " INNER JOIN TP_MST_User CU " +
                "    ON CU.UserID = C.CreateUserID " +
                " WHERE C.AccountID = :AccountID " +
                "   AND U.ValueFlag = '" + (int)Constant.ValueFlag.Effective + "'" +
                // 工号操作权限
                "   AND EXISTS (SELECT UP.PurviewID" +
                "                 FROM TP_MST_UserPurview UP" +
                "                WHERE UP.PurviewType = '" + (int)Constant.PurviewType.OperateUser + "'" +
                "                  AND (UP.PurviewID = U.UserID  OR UP.PurviewID = -1)" +
                "                  AND UP.UserId = :UPUserId)";
            return sqlString;
        }

        /// <summary>
        /// 取得FPC0401画面，班次配置明细查询SQL
        /// </summary>
        /// <returns>班次配置明细查询SQL</returns>
        private static string GetClassesDetailSQL()
        {
            string sqlString =
                "SELECT CD.ClassesDetailID, " +
                "       CD.userid," +
                "       CD.usercode," +
                "       UJ.JobsCode AS UJobsCode, " +
                "       UJ.JobsName AS UJobsName, " +
                "       UJ.JobsId AS UJobsId, " +
                "       CD.StaffID," +
                "       S.StaffCode, " +
                "       S.StaffName, " +
                "       CD.SOrganizationID," +
                "       SO.OrganizationCode AS SOrganizationCode, " +
                "       SO.OrganizationName AS SOrganizationName, " +
                "       CD.SJobsID," +
                "       CD.SJobsID as jobs," +
                "       SJ.JobsCode AS SJobsCode, " +
                "       SJ.JobsName AS SJobsName, " +
                "       CD.StaffStatus," +
                "       SSS.StaffStatusName, " +
                "       CD.Remarks " +
                "  FROM TP_PC_ClassesDetail CD " +
                " INNER JOIN TP_MST_Jobs UJ " +
                "    ON UJ.JobsID = CD.UJobsID " +
                " INNER JOIN TP_MST_Jobs SJ " +
                "    ON SJ.JobsID = CD.SJobsID " +
                " INNER JOIN TP_HR_Staff S " +
                "    ON S.StaffID = CD.StaffID " +
                " INNER JOIN TP_MST_Organization SO " +
                "    ON SO.OrganizationID = CD.SOrganizationID " +
                " INNER JOIN TP_SYS_StaffStatus SSS " +
                "    ON SSS.StaffStatusID = CD.StaffStatus " +
                " WHERE CD.ClassesSettingID = :ClassesSettingID" +
                " ORDER BY S.StaffCode";
            return sqlString;
        }

        /// <summary>
        /// 取得FPC0402画面，工号对应的员工信息SQL
        /// </summary>
        /// <returns></returns>
        private static string GetClassUserStaffSQL()
        {
            string sqlString = " " +
                        "SELECT usf.userstaffid AS infoid, " +
                        "     usf.UserID, " +
                        "     usr.UserCode, " +
                        "     usf.ujobsid AS UJobsID, " +
                        "     usf.StaffID, " +
                        "     usf.optimestamp, " +
                        "     j.jobsname AS UJobsName, " +
                        "     j.jobscode AS UJobsCode, " +
                        "     s.StaffCode, " +
                        "     s.StaffName, " +
                        "     s.jobs, " +
                        "     sj.jobsname as SJobsName, " +
                        "     sj.jobscode as SJobsCode , " +
                        "     s.organizationid as SOrganizationid, " +
                        "     og.organizationcode as SOrganizationCode, " +
                        "     og.organizationname as SOrganizationName, " +
                        "     og.organizationfullname as SOrganizationFullName, " +
                        "     s.staffstatus, " +
                        "     ss.staffstatusname, " +
                        "     s.remarks " +
                        "  FROM tp_mst_userstaff usf " +
                        "       inner join tp_mst_user usr on usf.userid=usr.userid" +
                        "       INNER JOIN tp_mst_jobs j ON usf.ujobsid = j.jobsid " +
                        "       INNER JOIN tp_hr_staff s ON usf.staffid = s.staffid " +
                        "       INNER JOIN tp_mst_jobs sj ON s.jobs = sj.jobsid " +
                        "       inner join TP_MST_ORGANIZATION og on s.organizationid=og.organizationid " +
                        "       inner join tp_sys_staffstatus ss on ss.staffstatusid=s.staffstatus " +
                        " WHERE usf.userid = :userid " +
                        " order by sj.jobscode";
            return sqlString;
        }

        /// <summary>
        /// 保存班次配置信息
        /// </summary>
        /// <param name="sUserInfo"></param>
        /// <param name="se">班次配置实体</param>
        /// <param name="editStatus">配置情况,0--新增,1--更新</param>
        /// <param name="dtClassesSettingDetail"></param>
        /// <returns></returns>
        public static ServiceResultEntity SetFPC0402SData(SUserInfo sUserInfo, FPC0402_SE se, int editStatus, DataTable dtClassesSettingDetail)
        {
            IDBTransaction tran = null;
            try
            {
                ServiceResultEntity sre = new ServiceResultEntity();
                //if (dsClassesSettingDetailInfo == null ||
                //    dsClassesSettingDetailInfo.Tables.Count != 1 ||
                //    dsClassesSettingDetailInfo.Tables[0].Rows.Count < 1)
                //{
                //    sre.Status = Constant.ServiceResultStatus.NoModifyData;
                //    return sre;
                //}

                //DataTable dtClassesSettingDetail = dsClassesSettingDetailInfo.Tables[0];
                tran = ClsDbFactory.CreateDBTransaction(DataBaseType.ORACLE, DataManager.ConnectionString);
                int executeResult = 0;

                if (editStatus == 0)//新增
                {
                    // 查询新插入的配置数据ID
                    string sqlText = "SELECT SEQ_PC_ClassesSetting_ID.NextVAL FROM dual";
                    int classesSettingID = int.Parse(tran.GetSqlResultToStr(sqlText));
                    // 插入班次配置表
                    string sqlInsMainString = " INSERT INTO TP_PC_ClassesSetting "
                                              + " (ClassesSettingID,AccountDate,UserID,UserCode, "
                                              + "        Remarks,AccountID,CreateTime,CreateUserID,UpdateUserID)  "
                                              + " VALUES (:ClassesSettingID,:AccountDate,:userID,:userCode, "
                                              + "         :remarks,:accountID,sysdate,:createUserID,:updateUserID)";
                    OracleParameter[] paramMain = new OracleParameter[]
                        {
                            new OracleParameter(":ClassesSettingID",OracleDbType.Int32,classesSettingID,ParameterDirection.Input),
                            new OracleParameter(":accountID",OracleDbType.Int32,sUserInfo.AccountID,ParameterDirection.Input),
                            new OracleParameter(":userID",OracleDbType.Int32,se.UserID,ParameterDirection.Input),
                            new OracleParameter(":userCode",OracleDbType.Varchar2,se.UserCode,ParameterDirection.Input),
                            new OracleParameter(":AccountDate",OracleDbType.Date,se.AccountDate,ParameterDirection.Input),
                            new OracleParameter(":remarks",OracleDbType.Varchar2,se.ClassRemarks,ParameterDirection.Input),
                            new OracleParameter(":accountID",OracleDbType.Int32,sUserInfo.AccountID,ParameterDirection.Input),
                            new OracleParameter(":createUserID",OracleDbType.Int32,sUserInfo.UserID,ParameterDirection.Input),
                            new OracleParameter(":updateUserID",OracleDbType.Int32,sUserInfo.UserID,ParameterDirection.Input),
                        };
                    executeResult = tran.ExecuteNonQuery(sqlInsMainString, paramMain);



                    //明细

                    foreach (DataRow drFor in dtClassesSettingDetail.Rows)
                    {
                        if (drFor["StaffID"] == DBNull.Value)
                        {
                            continue;
                        }
                        string sqlInsStr = "insert into TP_PC_ClassesDetail(ClassesSettingID,AccountDate,userid,usercode,staffid," +
                                        "staffstatus,remarks,accountid,valueflag,createtime,createuserid,updatetime,updateuserid," +
                                        "ujobsid,sjobsid,uorganizationid,sorganizationid)" +
                                        "values(:settingID,:AccountDate,:UserID,:UserCode,:StaffID," +
                                        ":StaffStatus,:StaffRemarks,:AccountID,1,sysdate,:CreateUserID,sysdate,:UpdateUserID," +
                                        ":UserJobsID,:StaffJobsID,:UserOrganizationID,:StaffOrganizationID)";
                        OracleParameter[] oracleParameter = new OracleParameter[]
                            {
                                new OracleParameter(":settingID",OracleDbType.Int32,classesSettingID,ParameterDirection.Input),
                                new OracleParameter(":AccountDate",OracleDbType.Date,se.AccountDate,ParameterDirection.Input),
                                new OracleParameter(":UserID",OracleDbType.Int32,se.UserID,ParameterDirection.Input),
                                new OracleParameter(":UserCode",OracleDbType.Varchar2,se.UserCode,ParameterDirection.Input),
                                new OracleParameter(":StaffID",OracleDbType.Int32,drFor["staffid"].ToString(),ParameterDirection.Input),
                                new OracleParameter(":StaffStatus",OracleDbType.Int32,drFor["staffstatus"].ToString(),ParameterDirection.Input),
                                new OracleParameter(":StaffRemarks",OracleDbType.Varchar2,drFor["remarks"].ToString(),ParameterDirection.Input),
                                new OracleParameter(":AccountID",OracleDbType.Int32,sUserInfo.AccountID,ParameterDirection.Input),
                                new OracleParameter(":CreateUserID",OracleDbType.Int32,sUserInfo.UserID,ParameterDirection.Input),
                                new OracleParameter(":UpdateUserID",OracleDbType.Int32,sUserInfo.UserID,ParameterDirection.Input),
                                new OracleParameter(":UserJobsID",OracleDbType.Int32,drFor["ujobsid"].ToString(),ParameterDirection.Input),
                                new OracleParameter(":StaffJobsID",OracleDbType.Int32,drFor["jobs"].ToString(),ParameterDirection.Input),
                                new OracleParameter(":UserOrganizationID",OracleDbType.Int32,se.UserOrganizationID,ParameterDirection.Input),
                                new OracleParameter(":StaffOrganizationID",OracleDbType.Int32,drFor["sorganizationid"].ToString(),ParameterDirection.Input)
                            };
                        executeResult = tran.ExecuteNonQuery(sqlInsStr, oracleParameter);
                    }
                }
                else
                {
                    //更新主表
                    string sqlUpMainString = "update TP_PC_ClassesSetting set Remarks=:remarks where CLASSESSETTINGID=:classSettingID";
                    OracleParameter[] paramMain = new OracleParameter[]
                        {
                            new OracleParameter(":remarks",OracleDbType.Varchar2,se.ClassRemarks,ParameterDirection.Input),
                            new OracleParameter(":classSettingID",OracleDbType.Int32,se.ClassSettingID,ParameterDirection.Input)
                        };
                    executeResult = tran.ExecuteNonQuery(sqlUpMainString, paramMain);
                    int classesSettingID = se.ClassSettingID;
                    //明细

                    foreach (DataRow drFor in dtClassesSettingDetail.Rows)
                    {
                        if (drFor.RowState == DataRowState.Added)
                        {
                            if (drFor["StaffID"] == DBNull.Value)
                            {
                                continue;
                            }

                            string sqlInsStr = "insert into TP_PC_ClassesDetail(ClassesSettingID,AccountDate,userid,usercode,staffid," +
                                            "staffstatus,remarks,accountid,valueflag,createtime,createuserid,updatetime,updateuserid," +
                                            "ujobsid,sjobsid,uorganizationid,sorganizationid)" +
                                            "values(:settingID,:AccountDate,:UserID,:UserCode,:StaffID," +
                                            ":StaffStatus,:StaffRemarks,:AccountID,1,sysdate,:CreateUserID,sysdate,:UpdateUserID," +
                                            ":UserJobsID,:StaffJobsID,:UserOrganizationID,:StaffOrganizationID)";
                            OracleParameter[] oracleParameter = new OracleParameter[]
                            {
                                new OracleParameter(":settingID",OracleDbType.Int32,classesSettingID,ParameterDirection.Input),
                                new OracleParameter(":AccountDate",OracleDbType.Date,se.AccountDate,ParameterDirection.Input),
                                new OracleParameter(":UserID",OracleDbType.Int32,se.UserID,ParameterDirection.Input),
                                new OracleParameter(":UserCode",OracleDbType.Varchar2,se.UserCode,ParameterDirection.Input),
                                new OracleParameter(":StaffID",OracleDbType.Int32,drFor["staffid"].ToString(),ParameterDirection.Input),
                                new OracleParameter(":StaffStatus",OracleDbType.Int32,drFor["staffstatus"].ToString(),ParameterDirection.Input),
                                new OracleParameter(":StaffRemarks",OracleDbType.Varchar2,drFor["remarks"].ToString(),ParameterDirection.Input),
                                new OracleParameter(":AccountID",OracleDbType.Int32,sUserInfo.AccountID,ParameterDirection.Input),
                                new OracleParameter(":CreateUserID",OracleDbType.Int32,sUserInfo.UserID,ParameterDirection.Input),
                                new OracleParameter(":UpdateUserID",OracleDbType.Int32,sUserInfo.UserID,ParameterDirection.Input),
                                new OracleParameter(":UserJobsID",OracleDbType.Int32,drFor["ujobsid"].ToString(),ParameterDirection.Input),
                                new OracleParameter(":StaffJobsID",OracleDbType.Int32,drFor["jobs"].ToString(),ParameterDirection.Input),
                                new OracleParameter(":UserOrganizationID",OracleDbType.Int32,se.UserOrganizationID,ParameterDirection.Input),
                                new OracleParameter(":StaffOrganizationID",OracleDbType.Int32,drFor["sorganizationid"].ToString(),ParameterDirection.Input)
                            };
                            executeResult = tran.ExecuteNonQuery(sqlInsStr, oracleParameter);
                        }
                        else if (drFor.RowState == DataRowState.Deleted)
                        {
                            string sqlstring = @"Delete from TP_PC_ClassesDetail Where CLASSESDETAILID=:ClassesDetailID";
                            OracleParameter[] parmeters = new OracleParameter[]
                        {
                            new OracleParameter(":ClassesDetailID",drFor["ClassesDetailID",DataRowVersion.Original].ToString()),
                        };
                            executeResult = tran.ExecuteNonQuery(sqlstring, parmeters);
                        }
                        else if (drFor.RowState == DataRowState.Modified)
                        {
                            string sqlstring = @"update TP_PC_ClassesDetail 
                                            set STAFFID=:StaffID,
                                            STAFFSTATUS=:StaffStatus,
                                            REMARKS=:Remarks,
                                            UPDATETIME=sysdate,
                                            UPDATEUSERID=:UserID,
                                            SJOBSID=:SJobID,
                                            SORGANIZATIONID=:SorganizationID
                                            where CLASSESDETAILID=:ClassesDetailID ";
                            OracleParameter[] parmeters = new OracleParameter[]
                        {
                            new OracleParameter(":StaffID",drFor["StaffID"].ToString()),
                            new OracleParameter(":StaffStatus",drFor["StaffStatus"].ToString()),
                            new OracleParameter(":Remarks",drFor["Remarks"].ToString()),
                            new OracleParameter(":UserID",sUserInfo.UserID),
                            new OracleParameter(":SJobID",drFor["Jobs"].ToString()),
                            new OracleParameter(":SorganizationID",drFor["SorganizationID"].ToString()),
                            new OracleParameter(":ClassesDetailID",drFor["ClassesDetailID"].ToString()),
                        };
                            executeResult = tran.ExecuteNonQuery(sqlstring, parmeters);
                        }
                    }
                }


                tran.Commit();
                return sre;
            }
            catch (Exception ex)
            {
                if (tran != null &&
                    tran.ConnState == ConnectionState.Open)
                {
                    tran.Rollback();
                }
                throw ex;
            }
            finally
            {
                if (tran != null &&
                    tran.ConnState == ConnectionState.Open)
                {
                    tran.Disconnect();
                }
            }
        }

        #endregion 班次配置管理

        #region 人员关联

        /// <summary>
        /// 取得FPC0601画面（人员关联）的查询数据
        /// </summary>
        /// <param name="user">登录用户信息</param>
        /// <param name="se">查询条件</param>
        /// <returns>查询结果</returns>
        public static ServiceResultEntity GetFPC0601SData(SUserInfo user, FPC0601_SE se)
        {
            IDBConnection conn = null;
            try
            {
                conn = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString);

                StringBuilder sql = new StringBuilder(PCModuleLogic.GetFPC0601UserSQL());
                List<OracleParameter> parameters = new List<OracleParameter>();
                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.UserCode))
                    {
                        sql.Append(" AND INSTR(U.UserCode, :UserCode) > 0");
                        parameters.Add(new OracleParameter(":UserCode", OracleDbType.NVarchar2, se.UserCode, ParameterDirection.Input));
                    }
                    // 员工工号备注
                    if (!string.IsNullOrEmpty(se.Remarks))
                    {
                        sql.Append(" AND INSTR(U.Remarks, :Remarks) > 0");
                        parameters.Add(new OracleParameter(":Remarks", OracleDbType.NVarchar2, se.Remarks, ParameterDirection.Input));
                    }
                }
                sql.Append(" ORDER BY  U.UserCode");
                DataTable data = conn.GetSqlResultToDt(sql.ToString(), parameters.ToArray());

                ServiceResultEntity sre = new ServiceResultEntity();
                if (data == null || data.Rows.Count == 0)
                {
                    sre.Status = Constant.ServiceResultStatus.NoSearchResults;
                    return sre;
                }
                sre.Data = new DataSet();
                sre.Data.Tables.Add(data);

                OracleParameter[] paras = new OracleParameter[]
                {
                    new OracleParameter(":userid", OracleDbType.Int32, data.Rows[0]["userid"], ParameterDirection.Input),
                };
                DataTable dData = conn.GetSqlResultToDt(PCModuleLogic.GetFPC0601UserStaffSQL(), paras);

                if (dData != null)
                {
                    sre.Data.Tables.Add(dData);
                }

                return sre;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (conn != null &&
                    conn.ConnState == ConnectionState.Open)
                {
                    conn.Close();
                }
            }
        }


        /// <summary>
        /// 取得FPC0601画面（人员关联）的查询数据
        /// </summary>
        /// <param name="user">登录用户信息</param>
        /// <param name="id">工号人员关联ID</param>
        /// <returns>工号人员关联明细</returns>
        public static ServiceResultEntity GetFPC0601SNData(SUserInfo user, int userid)
        {
            IDBConnection conn = null;
            try
            {
                conn = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString);

                OracleParameter[] paras = new OracleParameter[]
                {
                    new OracleParameter(":userid", OracleDbType.Int32, userid, ParameterDirection.Input),
                };
                ServiceResultEntity sre = new ServiceResultEntity();
                sre.Data = conn.GetSqlResultToDs(PCModuleLogic.GetFPC0601UserStaffSQL(), paras);
                return sre;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (conn != null &&
                    conn.ConnState == ConnectionState.Open)
                {
                    conn.Close();
                }
            }
        }

        /// <summary>
        /// 取得FPC0601画面，工号查询SQL
        /// </summary>
        /// <returns>工号查询SQL</returns>
        private static string GetFPC0601UserSQL()
        {
            string sqlString =
            "SELECT 0 AS sel, " +
            "       U.userid, " +
            "       U.usercode, " +
            "       U.limitmac, " +
            "       U.username, " +
            "       U.valueflag, " +
            "       U.valueflag AS checkvalueflag, " +
            "       U.organizationid, " +
            "       tp_mst_organization.organizationname, " +
            "       tp_mst_organization.organizationfullname, " +
            "       tp_mst_userlogin.islocked, " +
            "       U.remarks " +
            "  FROM tp_mst_user u LEFT JOIN tp_mst_organization ON u.organizationid = tp_mst_organization.organizationid " +
            "       INNER JOIN tp_mst_userlogin ON u.userid = tp_mst_userlogin.userid " +
            " WHERE u.accountid = :AccountID " +
            "   AND U.ValueFlag = '" + (int)Constant.ValueFlag.Effective + "'" +
            // 工号操作权限
            "   AND EXISTS (SELECT UP.PurviewID" +
            "                 FROM TP_MST_UserPurview UP" +
            "                WHERE UP.PurviewType = '" + (int)Constant.PurviewType.OperateUser + "'" +
            "                  AND (UP.PurviewID = U.UserID  OR UP.PurviewID = -1)" +
            "                  AND UP.UserId = :UPUserId)";

            return sqlString;
        }

        /// <summary>
        /// 取得FPC0601画面，关联人员查询SQL
        /// </summary>
        /// <returns>关联人员查询SQL</returns>
        private static string GetFPC0601UserStaffSQL()
        {
            string sqlString = " " +
                        "SELECT usf.userstaffid AS infoid, " +
                        "     usf.UserID, " +
                        "     usf.ujobsid AS UserJobsID, " +
                        "       usf.StaffID, " +
                        "     usf.optimestamp, " +
                        "     j.jobsname AS UserJobsName, " +
                        "       j.jobscode AS UserJobsCode, " +
                        "     s.StaffCode, " +
                        "     s.StaffName, " +
                        "     sj.jobsname, " +
                        "     sj.jobscode , " +
                        "     s.organizationid, " +
                        "     og.organizationcode, " +
                        "     og.organizationname, " +
                        "     og.organizationfullname, " +
                        "     s.staffstatus, " +
                        "     ss.staffstatusname, " +
                        "     s.remarks " +
                        "  FROM tp_mst_userstaff usf INNER JOIN tp_mst_jobs j ON usf.ujobsid = j.jobsid " +
                        "       INNER JOIN tp_hr_staff s ON usf.staffid = s.staffid " +
                        "       INNER JOIN tp_mst_jobs sj ON s.jobs = sj.jobsid " +
                        "       inner join TP_MST_ORGANIZATION og on s.organizationid=og.organizationid " +
                        "       inner join tp_sys_staffstatus ss on ss.staffstatusid=s.staffstatus " +
                        " WHERE userid = :userid " +
                        " order by sj.jobscode";

            return sqlString;
        }

        /// <summary>
        /// 保存FPC0602画面（班次配置管理）的数据
        /// </summary>
        /// <param name="userInfo"></param>
        /// <param name="UserId"></param>
        /// <param name="StaffTable"></param>
        /// <returns></returns>
        public static ServiceResultEntity SetFPC0602Data(SUserInfo userInfo, int UserId, DataSet StaffInfo)
        {
            IDBTransaction tran = null;
            try
            {
                ServiceResultEntity sre = new ServiceResultEntity();
                if (StaffInfo == null ||
                    StaffInfo.Tables.Count != 1 ||
                    StaffInfo.Tables[0].Rows.Count < 1)
                {
                    sre.Status = Constant.ServiceResultStatus.NoModifyData;
                    return sre;
                }
                DataTable StaffTable = StaffInfo.Tables[0];
                tran = ClsDbFactory.CreateDBTransaction(DataBaseType.ORACLE, DataManager.ConnectionString);
                int executeResult = 0;

                foreach (DataRow drFor in StaffTable.Rows)
                {
                    if (drFor.RowState == DataRowState.Added)
                    {
                        if (drFor["StaffID"] == DBNull.Value)
                        {
                            continue;
                        }
                        string sqlstring = @"Insert into TP_MST_UserStaff(UserID,UJobsID,StaffID,AccountID,CreateUserID,UpdateUserID)
                            Values(:UserID,:UJobsID,:StaffID,:AccountID,:CreateUserID,:UpdateUserID)";
                        OracleParameter[] parmeters = new OracleParameter[]
                        {
                            new OracleParameter(":UserID",UserId),
                            new OracleParameter(":UJobsID",drFor["UserJobsID"].ToString()),
                            new OracleParameter(":StaffID",drFor["StaffID"].ToString()),
                            new OracleParameter(":AccountID",userInfo.AccountID),
                            new OracleParameter(":CreateUserID",userInfo.UserID),
                            new OracleParameter(":UpdateUserID",userInfo.UserID),
                        };
                        executeResult = tran.ExecuteNonQuery(sqlstring, parmeters);
                    }
                    else if (drFor.RowState == DataRowState.Deleted)
                    {
                        string sqlstring = @"Delete from TP_MST_UserStaff Where UserStaffID=:UserStaffID";
                        OracleParameter[] parmeters = new OracleParameter[]
                        {
                            new OracleParameter(":UserStaffID",drFor["InfoID",DataRowVersion.Original].ToString()),
                        };
                        executeResult = tran.ExecuteNonQuery(sqlstring, parmeters);

                    }
                    else if (drFor.RowState == DataRowState.Modified)
                    {
                        string sqlstring = @"Update TP_MST_UserStaff
                                              Set UJobsID=:UJobsID,
                                              StaffID=:StaffID,
                                              UpdateUserID=:UpdateUserID 
                                              Where UserStaffID=:UserStaffID";
                        OracleParameter[] parmeters = new OracleParameter[]
                        {
                            new OracleParameter(":UJobsID",drFor["UserJobsId"].ToString()),
                            new OracleParameter(":StaffID",drFor["StaffID"].ToString()),
                            new OracleParameter(":UpdateUserID",userInfo.UserID),
                            new OracleParameter(":UserStaffID",drFor["InfoID"].ToString())
                        };
                        executeResult = tran.ExecuteNonQuery(sqlstring, parmeters);
                    }
                }
                tran.Commit();
                return sre;
            }
            catch (Exception ex)
            {
                if (tran != null &&
                    tran.ConnState == ConnectionState.Open)
                {
                    tran.Rollback();
                }
                throw ex;
            }
            finally
            {
                if (tran != null &&
                    tran.ConnState == ConnectionState.Open)
                {
                    tran.Disconnect();
                }
            }
        }


        /// <summary>
        /// 根据工号ID获取该工号的工种关联
        /// </summary>
        /// <param name="UserId">用户ID</param>
        /// <returns>DataSet</returns>    
        public static ServiceResultEntity GetJobByUserId(int UserId)
        {
            IDBConnection conn = null;
            try
            {
                conn = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString);

                StringBuilder sbSql = new StringBuilder();
                sbSql.Append(@"Select JobsID as UserJobsID,JobsCode as UserJobsCode,JobsName as UserJobsName,JobsCode||'-'|| JobsName as UserJobsCodeName
                        from TP_MST_Jobs where JobsID in 
                        (Select JobsID From TP_MST_UserJobs where UserID=:UserID) ");
                OracleParameter[] paras = new OracleParameter[]
                {
                    new OracleParameter(":UserID", OracleDbType.Int32, UserId, ParameterDirection.Input),
                };
                ServiceResultEntity sre = new ServiceResultEntity();
                sre.Data = conn.GetSqlResultToDs(sbSql.ToString(), paras);

                return sre;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (conn != null &&
                    conn.ConnState == ConnectionState.Open)
                {
                    conn.Close();
                }
            }
        }


        /// <summary>
        /// 根据工号ID获取该工号的基础信息
        /// </summary>
        /// <param name="UserId">用户ID</param>
        /// <returns>DataSet</returns>    
        public static ServiceResultEntity GetUserInfo(SUserInfo user, int UserId)
        {
            IDBConnection conn = null;
            try
            {
                conn = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString);
                StringBuilder sbSql = new StringBuilder(GetUserInfoSQL());
                // List<OracleParameter> parameters = new List<OracleParameter>();

                OracleParameter[] paras = new OracleParameter[]
                {
                    new OracleParameter(":AccountID", OracleDbType.Int32, user.AccountID, ParameterDirection.Input),
                    new OracleParameter(":UPUserId", OracleDbType.Int32, user.UserID, ParameterDirection.Input),
                    new OracleParameter(":UserID", OracleDbType.Int32,UserId, ParameterDirection.Input)
                };
                ServiceResultEntity sre = new ServiceResultEntity();
                sre.Data = conn.GetSqlResultToDs(sbSql.ToString(), paras);
                return sre;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (conn != null &&
                    conn.ConnState == ConnectionState.Open)
                {
                    conn.Close();
                }
            }
        }


        /// <summary>
        /// 取得FPC0601画面，工号查询SQL
        /// </summary>
        /// <returns>工号查询SQL</returns>
        private static string GetUserInfoSQL()
        {
            string sqlString =
            "SELECT 0 AS sel, " +
            "       U.userid, " +
            "       U.usercode, " +
            "       U.limitmac, " +
            "       U.username, " +
            "       U.valueflag, " +
            "       U.valueflag AS checkvalueflag, " +
            "       U.organizationid, " +
            "       tp_mst_organization.organizationname, " +
            "       tp_mst_organization.organizationfullname, " +
            "       tp_mst_userlogin.islocked, " +
            "       U.remarks " +
            "  FROM tp_mst_user u LEFT JOIN tp_mst_organization ON u.organizationid = tp_mst_organization.organizationid " +
            "       INNER JOIN tp_mst_userlogin ON u.userid = tp_mst_userlogin.userid " +
            " WHERE u.accountid = :AccountID " +
            "   AND U.ValueFlag = '" + (int)Constant.ValueFlag.Effective + "'" +
            "   and u.userid=:UserID " +
            // 工号操作权限
            "   AND EXISTS (SELECT UP.PurviewID" +
            "                 FROM TP_MST_UserPurview UP" +
            "                WHERE UP.PurviewType = '" + (int)Constant.PurviewType.OperateUser + "'" +
            "                  AND (UP.PurviewID = U.UserID OR UP.PurviewID = -1)" +
            "                  AND UP.UserId = :UPUserId)";

            return sqlString;
        }

        #endregion 人员关联

        #region 模具档案

        /// <summary>
        /// 获取模具档案一览画面期初数据
        /// </summary>
        /// <param name="sUserInfo"></param>
        /// <returns></returns>
        public static ServiceResultEntity GetFPC1001LoadData(SUserInfo sUserInfo, ClientRequestEntity cre)
        {
            ServiceResultEntity sre = new ServiceResultEntity();
            IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString);
            try
            {
                con.Open();
                // 模具类型
                string sqlString = "SELECT dd.dictionaryid, dd.dictionaryvalue, dd.displayno\n" +
                "  FROM TP_MST_DataDictionary dd\n" +
                " WHERE dd.DictionaryType = 'GMM002'\n" +
                "   AND dd.valueflag = '1'\n" +
                "   AND dd.accountid = :accountid\n" +
                " ORDER BY dd.displayno";
                OracleParameter[] parameters = new OracleParameter[]
                {
                    new OracleParameter(":accountid", OracleDbType.Int32, sUserInfo.AccountID, ParameterDirection.Input),
                };
                sre.Data = con.GetSqlResultToDs(sqlString, parameters);
                sre.Data.Tables[0].TableName = "MouldType";

                // 材料供应商
                sqlString = "SELECT mms.supplierid, mms.suppliername, mms.displayno\n" +
                "  FROM TP_MST_MouldMaterialSuppliers mms\n" +
                " WHERE mms.valueflag = '1'\n" +
                "   AND mms.accountid = :accountid\n" +
                " ORDER BY mms.displayno";
                sre.Data.Tables.Add(con.GetSqlResultToDt(sqlString, parameters));
                sre.Data.Tables[1].TableName = "Suppliers";

                sqlString =
                "SELECT ms.mouldstatusid, ms.mouldstatusname, ms.displayno\n" +
                "  FROM TP_SYS_MouldStatus ms\n" +
                " WHERE ms.mouldstatusid > 0\n" +
                " ORDER BY ms.displayno";
                sre.Data.Tables.Add(con.GetSqlResultToDt(sqlString));
                sre.Data.Tables[2].TableName = "MouldStatus";

                return sre;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (con.ConnState == ConnectionState.Open)
                {
                    con.Close();
                }
            }
        }

        /// <summary>
        /// 获取模具档案一览查询数据
        /// </summary>
        /// <param name="sUserInfo"></param>
        /// <returns></returns>
        public static ServiceResultEntity GetFPC1001QueryData(SUserInfo sUserInfo, ClientRequestEntity cre)
        {
            ServiceResultEntity sre = new ServiceResultEntity();
            IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString);
            try
            {
                con.Open();
                string sqlString =
                "SELECT 0 Sel, m.MouldID\n" +
                "      ,m.MouldCode\n" +
                "      ,m.MouldBarcode\n" +
                "      ,mtype.dictionaryvalue MouldTypeName\n" +
                "      ,mms.SupplierName\n" +
                "      ,m.ProductionDate\n" +
                "      ,m.GoodsID\n" +
                "      ,m.GoodsCode\n" +
                "      ,m.UserID\n" +
                "      ,m.UserCode\n" +
                "      ,m.Weight\n" +
                "      ,m.Cost\n" +
                "      ,m.StandardGroutingNum\n" +
                "      ,m.StandardGroutingSum\n" +
                "      ,m.GroutingNum GroutingNums\n" +
                "      ,case when m.OutputCount=1 then to_char(m.GroutingNum)\n" +
                "      else (select listagg(to_char(t.GroutingNum), ';') within GROUP(ORDER BY t.outputno) from TP_PC_MouldOutput t where t.MouldID=m.MouldID) end GroutingNum\n" +
                "      ,m.GroutingDateBegin\n" +
                "      ,m.GroutingDateEnd\n" +
                "      ,m.LastGroutingBatchNo\n" +
                "      ,m.MouldStatus\n" +
                "      ,ms.mouldstatusname\n" +
                "      ,m.RepairFlag\n" +
                "      ,m.ScrapDate\n" +
                "      ,m.ScrapUserID\n" +
                "      ,m.ScrapUserCode\n" +
                "      ,ScReason.dictionaryvalue ScrapReason\n" +
                "      ,m.ScrapResponsibility\n" +
                "      ,gl.groutinglinecode\n" +
                //"      ,gld.groutingmouldcode\n" +
                "      ,case when m.GroutingLineDetailID is null or m.OutputCount=1 then to_char(gld.groutingmouldcode) else \n" +
                "      (select listagg(to_char(gldd.groutingmouldcode), ',') within GROUP(ORDER BY gldd.groutinglinedetailid) from tp_pc_groutinglinedetail gldd where gldd.valueflag='1' and gldd.MouldID=m.MouldID) end groutingmouldcode\n" +
                "      ,m.Remarks\n" +
                "      ,m.CreateTime\n" +
                "      ,cu.usercode CreateUserCode\n" +
                "      ,m.OutputCount\n" +
                "      ,g.mouldmaterialcode\n" +
                "      ,m.STANDARDGROUTINGSUM - m.GroutingNum notusedGroutingNum\n" +
                //2021年12月21日11:41:10 by fy modify 列表中增加一列【卸模线号】，显示他最后一次上线的线号
                //begin
                ",gl2.groutinglinecode UnloadingMouldLine\n" +
                //end
                ",mp.plan_code\n" + // add 夏常明 模具生产计划单号 2023-6-25
                "  FROM tp_pc_mould m\n" +
                "  LEFT JOIN tp_mst_datadictionary mtype\n" +
                "    ON mtype.dictionaryid = m.mouldtype\n" +
                "  LEFT JOIN tp_mst_goods g\n" +
                "    ON g.goodsid = m.goodsid\n" +
                "  LEFT JOIN tp_mst_mouldmaterialsuppliers mms\n" +
                "    ON mms.SupplierID = m.MaterialSupplier\n" +
                " INNER JOIN tp_sys_mouldstatus ms\n" +
                "    ON ms.mouldstatusid = m.MouldStatus\n" +
                "  LEFT JOIN tp_mst_datadictionary ScReason\n" +
                "    ON ScReason.dictionaryid = m.ScrapReason\n" +
                "  LEFT JOIN tp_pc_groutinglinedetail gld\n" +
                "    ON gld.groutinglinedetailid = m.GroutingLineDetailID\n" +
                "  LEFT JOIN tp_pc_groutingline gl\n" +
                "    ON gl.groutinglineid = gld.groutinglineid\n" +
                " INNER JOIN tp_mst_user cu\n" +
                "    ON cu.userid = m.createuserid\n" +
                //2021年12月21日11:41:10 by fy modify 列表中增加一列【卸模线号】，显示他最后一次上线的线号
                //begin
                "LEFT JOIN(\n" +
                "SELECT\n" +
                "    t.groutinglinedetailid,\n" +
                "    t.mouldcode,\n" +
                "    t.rk\n" +
                "FROM\n" +
                "    (\n" +
                "    SELECT\n" +
                "        t.groutinglinedetailid,\n" +
                "        t.mouldcode,\n" +
                "        rank ( ) over (PARTITION BY t.mouldcode ORDER BY t.createtime DESC) rk\n" +
                "  FROM\n" +
                "        tp_pc_mouldchangehistory t\n" +
                "    WHERE\n" +
                "        t.valueflag = 1\n" +
                "        AND t.mouldstatus IN( 2, 3 ) \n" +
                "        AND t.mouldstatusafter IN( 1, 4 ) \n" +
                "    ) t\n" +
                "WHERE\n" +
                "    t.rk <= 1\n" +
                ") mch ON mch.mouldcode = m.mouldcode\n" +
                "LEFT JOIN tp_pc_groutinglinedetail gld2 ON gld2.GROUTINGLINEDETAILID = mch.GROUTINGLINEDETAILID\n" +
                "LEFT JOIN tp_pc_groutingline gl2 ON gl2.groutinglineid = gld2.groutinglineid\n" +
                //end
                "left join t_mould_plan mp on m.MOULD_PLAN_ID = mp.plan_id \n" +//模具生产计划 add 夏常明 2023-6-25
                " WHERE m.accountid = :accountid\n" +
                "   AND m.valueflag = '1'\n" +
                "   AND (:MouldBarcode IS NULL OR instr(m.MouldBarcode, :MouldBarcode) > 0)\n" +
                "   AND (:PDateBegin IS NULL OR m.ProductionDate >= :PDateBegin)\n" +
                "   AND (:PDateEnd IS NULL OR m.ProductionDate <= :PDateEnd)\n" +
                "   AND (:UserCode IS NULL OR instr(m.UserCode, :UserCode) > 0)\n" +
                "   AND (:GoodsCode IS NULL OR instr(m.GoodsCode, :GoodsCode) > 0)\n" +
                "   AND (:Remarks IS NULL OR instr(m.Remarks, :Remarks) > 0)\n" +
                "   AND (:MouldTypes IS NULL OR\n" +
                "       instr(',' || :MouldTypes || ',', ',' || m.MouldType || ',') > 0)\n" +
                "   AND (:Suppliers IS NULL OR\n" +
                "       instr(',' || :Suppliers || ',', ',' || m.MaterialSupplier || ',') > 0)\n" +
                "   AND (:MouldStatus IS NULL OR\n" +
                "       instr(',' || :MouldStatus || ',', ',' || m.MouldStatus || ',') > 0)\n" +
                "   AND (:SDateBegin IS NULL OR m.ScrapDate >= :SDateBegin)\n" +
                "   AND (:SDateEnd IS NULL OR m.ScrapDate <= :SDateEnd)\n" +
                "   AND (:MouldIDs IS NULL OR\n" +
                "       instr(',' || :MouldIDs || ',', ',' || m.MouldID || ',') > 0)\n" +
                " ORDER BY m.MouldBarcode";

                OracleParameter[] parameters = new OracleParameter[]
                {
                    new OracleParameter(":MouldIDs", OracleDbType.Varchar2, cre.Properties["MouldIDs"], ParameterDirection.Input),
                    new OracleParameter(":accountid", OracleDbType.Int32, sUserInfo.AccountID, ParameterDirection.Input),
                    new OracleParameter(":PDateBegin", OracleDbType.Date, cre.Properties["PDateBegin"], ParameterDirection.Input),
                    new OracleParameter(":PDateEnd", OracleDbType.Date, cre.Properties["PDateEnd"], ParameterDirection.Input),
                    new OracleParameter(":MouldBarcode", OracleDbType.NVarchar2, cre.Properties["MouldBarcode"], ParameterDirection.Input),
                    new OracleParameter(":UserCode", OracleDbType.NVarchar2, cre.Properties["UserCode"], ParameterDirection.Input),
                    new OracleParameter(":GoodsCode", OracleDbType.NVarchar2, cre.Properties["GoodsCode"], ParameterDirection.Input),
                    new OracleParameter(":Remarks", OracleDbType.NVarchar2, cre.Properties["Remarks"], ParameterDirection.Input),
                    new OracleParameter(":MouldTypes", OracleDbType.NVarchar2, cre.Properties["MouldTypes"], ParameterDirection.Input),
                    new OracleParameter(":Suppliers", OracleDbType.NVarchar2, cre.Properties["Suppliers"], ParameterDirection.Input),
                    new OracleParameter(":MouldStatus", OracleDbType.NVarchar2, cre.Properties["MouldStatus"], ParameterDirection.Input),
                    new OracleParameter(":SDateBegin", OracleDbType.Date, cre.Properties["ScrapDateBegin"], ParameterDirection.Input),
                    new OracleParameter(":SDateEnd", OracleDbType.Date, cre.Properties["ScrapDateEnd"], ParameterDirection.Input),
                };
                sre.Data = con.GetSqlResultToDs(sqlString, parameters);

                if (sre.Data == null || sre.Data.Tables.Count == 0 || sre.Data.Tables[0].Rows.Count == 0)
                {
                    sre.Status = Constant.ServiceResultStatus.NoSearchResults;
                }

                return sre;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (con.ConnState == ConnectionState.Open)
                {
                    con.Close();
                }
            }
        }

        /// <summary>
        /// 查询模具是否报工
        /// </summary>
        /// <param name="sUserInfo"></param>
        /// <param name="cre"></param>
        /// <returns></returns>
        public static ServiceResultEntity GetMouldPlan(SUserInfo sUserInfo, ClientRequestEntity cre)
        {
            ServiceResultEntity sre = new ServiceResultEntity();
            IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString);

            try
            {
                con.Open();

                string sqlString = "SELECT COUNT(1)\n" +
                "  FROM tp_pc_mould pm\n" +
                " WHERE pm.mould_plan_id IS NOT NULL\n" +
                "   AND pm.accountid = :accountid\n" +
                "   AND instr(',' || :MouldIDs || ',', ',' || pm.mouldid || ',') > 0";
                OracleParameter[] parameters = new OracleParameter[]
               {
                    new OracleParameter(":MouldIDs", OracleDbType.Varchar2, cre.Properties["MouldIDs"], ParameterDirection.Input),
                    new OracleParameter(":accountid", OracleDbType.Int32, sUserInfo.AccountID, ParameterDirection.Input),
               };
                sre.Data = con.GetSqlResultToDs(sqlString, parameters);
                return sre;

            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (con.ConnState == ConnectionState.Open)
                {
                    con.Close();
                }
            }
        }

        /// <summary>
        /// 产品型号是否相同
        /// </summary>
        /// <param name="sUserInfo"></param>
        /// <param name="cre"></param>
        /// <returns></returns>
        public static ServiceResultEntity GoodsIdSame(SUserInfo sUserInfo, ClientRequestEntity cre)
        {
            ServiceResultEntity sre = new ServiceResultEntity();
            IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString);

            try
            {
                con.Open();

                string sqlString = "SELECT DISTINCT pm.goodsid\n" +
                "  FROM tp_pc_mould pm\n" +
                " WHERE pm.accountid = :accountid\n" +
                "   AND instr(',' || :MouldIDs || ',', ',' || pm.mouldid || ',') > 0";
                OracleParameter[] parameters = new OracleParameter[]
               {
                    new OracleParameter(":MouldIDs", OracleDbType.Varchar2, cre.Properties["MouldIDs"], ParameterDirection.Input),
                    new OracleParameter(":accountid", OracleDbType.Int32, sUserInfo.AccountID, ParameterDirection.Input),
               };
                sre.Data = con.GetSqlResultToDs(sqlString, parameters);
                return sre;

            }
            catch (Exception ex)
            {

                throw ex;
            }
            finally
            {
                if (con.ConnState == ConnectionState.Open)
                {
                    con.Close();
                }
            }
        }

        /// <summary>
        /// 根据产品查询模具生产计划数据源
        /// </summary>
        /// <param name="sUserInfo"></param>
        /// <param name="cre"></param>
        /// <returns></returns>
        public static ServiceResultEntity GetFPC1004MouldPlanData(SUserInfo sUserInfo, ClientRequestEntity cre)
        {
            ServiceResultEntity sre = new ServiceResultEntity();
            int goodsid = Convert.ToInt32(cre.Properties["goodsid"]);
            IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString);
            try
            {
                string sqlString = "SELECT mp.plan_id\n" +
                       "      ,mp.plan_code ||'('||TO_CHAR(mp.plan_qty-mp.make_qty,'fm9999')||')' as plan_code\n" +
                       "      ,mp.plan_status\n" +
                       "  FROM t_mould_plan mp\n" +
                       "  WHERE mp.goodsid = :goodsid\n" +
                       "  AND mp.plan_status <>3";
                OracleParameter[] parameters = new OracleParameter[]
               {
                  new OracleParameter(":goodsid", OracleDbType.Int32, goodsid, ParameterDirection.Input),
               };
                sre.Data = con.GetSqlResultToDs(sqlString, parameters);
                return sre;
            }
            catch (Exception ex)
            {

                throw ex;
            }
            finally
            {
                if (con.ConnState == ConnectionState.Open)
                {
                    con.Close();
                }
            }

        }

        /// <summary>
        /// 获取模具新建、编辑画面期初数据
        /// </summary>
        /// <param name="sUserInfo"></param>
        /// <returns></returns>
        public static ServiceResultEntity GetFPC1002LoadData(SUserInfo sUserInfo, ClientRequestEntity cre)
        {
            ServiceResultEntity sre = new ServiceResultEntity();
            int mouldID = cre.Properties.GetValue<int>("MouldID");
            IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString);
            try
            {
                con.Open();
                // 模具类型
                string sqlString = "SELECT dd.dictionaryid, dd.dictionaryvalue, dd.displayno\n" +
                "  FROM TP_MST_DataDictionary dd\n" +
                " WHERE dd.DictionaryType = 'GMM002'\n" +
                "   AND dd.valueflag = '1'\n" +
                "   AND dd.accountid = :accountid\n" +
                " ORDER BY dd.displayno";
                OracleParameter[] parameters = new OracleParameter[]
                {
                    new OracleParameter(":accountid", OracleDbType.Int32, sUserInfo.AccountID, ParameterDirection.Input),
                };
                sre.Data = con.GetSqlResultToDs(sqlString, parameters);
                sre.Data.Tables[0].TableName = "MouldType";

                // 材料供应商
                sqlString = "SELECT mms.supplierid, mms.suppliername, mms.displayno\n" +
                "  FROM TP_MST_MouldMaterialSuppliers mms\n" +
                " WHERE mms.valueflag = '1'\n" +
                "   AND mms.accountid = :accountid\n" +
                " ORDER BY mms.displayno";
                sre.Data.Tables.Add(con.GetSqlResultToDt(sqlString, parameters));
                sre.Data.Tables[1].TableName = "Suppliers";

                if (mouldID > 0)
                {
                    sqlString = "SELECT m.mouldid\n" +
                    "      ,m.mouldbarcode\n" +
                    "      ,m.mouldtype\n" +
                    "      ,dd.dictionaryvalue mouldtypeName\n" +
                    "      ,m.materialsupplier\n" +
                    "      ,mms.suppliername\n" +
                    "      ,m.productiondate\n" +
                    "      ,m.goodsid\n" +
                    "      ,m.goodscode\n" +
                    "      ,m.userid\n" +
                    "      ,m.usercode\n" +
                    "      ,m.weight\n" +
                    "      ,m.cost\n" +
                    "      ,m.standardgroutingnum\n" +
                    "      ,m.remarks\n" +
                    "      ,m.OutputCount\n" +
                    "      ,m.MOULD_PLAN_ID\n" +
                    "  FROM tp_pc_mould m\n" +
                    "  LEFT JOIN tp_mst_datadictionary dd\n" +
                    "    ON dd.dictionaryid = m.mouldtype\n" +
                    "  LEFT JOIN tp_mst_mouldmaterialsuppliers mms\n" +
                    "    ON mms.supplierid = m.materialsupplier\n" +
                    " WHERE m.mouldid = :mouldid";

                    parameters = new OracleParameter[]
                    {
                        new OracleParameter(":mouldid", OracleDbType.Int32, mouldID, ParameterDirection.Input),
                    };
                    sre.Data.Tables.Add(con.GetSqlResultToDt(sqlString, parameters));
                    sre.Data.Tables[2].TableName = "InfoData";

                    //模具生产计划不为空 返回模具生产计划数据源
                    if (!string.IsNullOrEmpty(sre.Data.Tables[2].Rows[0]["MOULD_PLAN_ID"] + ""))
                    {

                        sqlString = "SELECT mp.plan_id\n" +
                       "      ,mp.plan_code\n" +
                       "      ,mp.plan_status\n" +
                       "  FROM t_mould_plan mp\n" +
                       "  WHERE mp.goodsid = :goodsid and mp.valueflag = 1 and mp.plan_status != 3";
                        parameters = new OracleParameter[]
                   {
                        new OracleParameter(":goodsid", OracleDbType.Int32, Convert.ToInt32(sre.Data.Tables[2].Rows[0]["goodsid"]), ParameterDirection.Input),
                   };
                        sre.Data.Tables.Add(con.GetSqlResultToDt(sqlString, parameters));
                        sre.Data.Tables[3].TableName = "Mouldplan";

                    }
                }

                return sre;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (con.ConnState == ConnectionState.Open)
                {
                    con.Close();
                }
            }
        }

        /// <summary>
        /// 获取模具产品期初数据
        /// </summary>
        /// <param name="sUserInfo"></param>
        /// <returns></returns>
        public static ServiceResultEntity GetFPC1002GoodsData(SUserInfo sUserInfo, ClientRequestEntity cre)
        {
            ServiceResultEntity sre = new ServiceResultEntity();
            string mouldID = cre.Properties.GetValue<string>("GoodsCode");
            IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString);
            try
            {
                con.Open();
                string sqlString = "SELECT g.goodsid, g.goodscode,g.GOODSSPECIFICATION, g.mouldweight, g.mouldcost\n" +
                " , g.StandardGroutingNum, g.MouldOutputCount" +
                "  FROM tp_mst_goods g\n" +
                " WHERE g.goodscode = :goodscode";
                OracleParameter[] parameters = new OracleParameter[]
                {
                    new OracleParameter(":goodscode", OracleDbType.NVarchar2,
                        cre.Properties["GoodsCode"], ParameterDirection.Input),
                };
                DataSet DS = con.GetSqlResultToDs(sqlString, parameters);
                //获取未完成模具生产计划 
                sqlString = "SELECT mp.plan_id\n" +
               "      ,mp.plan_code ||'('||TO_CHAR(mp.plan_qty-mp.make_qty,'fm9999')||')' as plan_code\n" +
               "  FROM t_mould_plan mp\n" +
               " INNER JOIN tp_mst_goods mg\n" +
               "    ON mp.goodsid = mg.goodsid\n" +
               " WHERE mg.accountid = :accountid\n" +
               "   AND mg.goodscode = :goodscode\n" +
               " AND mp.plan_status <>3";
                parameters = new OracleParameter[]
               {
                    new OracleParameter(":goodscode", OracleDbType.NVarchar2,
                        cre.Properties["GoodsCode"], ParameterDirection.Input),
                    new OracleParameter(":accountid", OracleDbType.Int32, sUserInfo.AccountID, ParameterDirection.Input),
               };
                DataTable dt = con.GetSqlResultToDt(sqlString, parameters);
                DS.Tables.Add(dt.Copy());
                sre.Data = DS;

                return sre;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (con.ConnState == ConnectionState.Open)
                {
                    con.Close();
                }
            }
        }

        /// <summary>
        /// 获取模具新建、编辑画面期初数据
        /// </summary>
        /// <param name="sUserInfo"></param>
        /// <returns></returns>
        public static ServiceResultEntity SetFPC1002Data(SUserInfo sUserInfo, ClientRequestEntity cre)
        {
            ServiceResultEntity sre = new ServiceResultEntity();
            int mouldID = Convert.ToInt32(cre.Properties["MouldID"]);
            int retrunRow = 0;
            IDBTransaction con = ClsDbFactory.CreateDBTransaction(DataBaseType.ORACLE, DataManager.ConnectionString);
            try
            {
                con.Connect();

                if (mouldID == 0)
                {
                    // 新建
                    //// 验证 条码是否重复
                    //string sql = "Select count(*) from TP_PC_Mould m where m.MouldBarcode = :MouldBarcode";
                    //OracleParameter[] paras = new OracleParameter[]
                    //{
                    //    new OracleParameter(":MouldBarcode", OracleDbType.Varchar2,
                    //        cre.Properties["MouldBarcode"], ParameterDirection.Input),
                    //};
                    //string count = con.GetSqlResultToStr(sql, paras);
                    //if ("0" != count)
                    //{
                    //    sre.Status = Constant.ServiceResultStatus.Other;
                    //    sre.Message = "模具条码【" + cre.Properties["MouldBarcode"] + "】已存在";
                    //    return sre;
                    //}
                    string plan_code = string.Empty; //模具计划编码
                    //模具计划 未完成数量 模具数大小校验
                    if (cre.Properties["MOULD_PLAN_ID"] != null)
                    {
                        string sqlString = "SELECT mp.plan_qty - mp.make_qty AS incomplete,mp.PLAN_CODE\n" +
                        "  FROM t_mould_plan mp\n" +
                        " WHERE mp.plan_id = :mould_plan_id";
                        OracleParameter[] paras1 = new OracleParameter[]
                        {
                            new OracleParameter(":mould_plan_id", OracleDbType.Varchar2,
                                cre.Properties["MOULD_PLAN_ID"], ParameterDirection.Input),
                        };
                        DataTable dt = con.GetSqlResultToDt(sqlString, paras1);
                        plan_code = dt.Rows[0]["PLAN_CODE"].ToString();
                        if (Convert.ToInt32(dt.Rows[0][0]) < Convert.ToInt32(cre.Properties["MCount"]))
                        {
                            sre.Message = " 模具个数不能大于模具生产计划未完成数量！";
                            sre.Status = Constant.ServiceResultStatus.Other;
                            return sre;
                        }
                    }
                    int mCount = 1;
                    if (cre.Properties["MCount"] != null)
                    {
                        mCount = Convert.ToInt32(cre.Properties["MCount"]);
                    }
                    mCount = (mCount < 1 ? 1 : mCount);

                    string sql = null;
                    OracleParameter[] paras = null;

                    for (int i = 0; i < mCount; i++)
                    {
                        mouldID = Convert.ToInt32(con.GetSqlResultToObj("select seq_pc_mould_id.nextval from dual "));
                        string mouldcode = con.GetSqlResultToStr("select fun_cmn_convertto36(" + mouldID + ", 6) from dual ");
                        int mouldHistoryID = Convert.ToInt32(con.GetSqlResultToObj("select seq_pc_mouldhistory_id.nextval from dual "));

                        int len = mouldcode.Length;
                        string mouldBarcode = DateTime.Now.ToString("yyMMdd") + (len > 6 ? mouldcode.Substring(len - 6) : mouldcode);
                        if (i > 0)
                        {
                            sre.Result += "," + mouldBarcode;
                        }
                        else
                        {
                            sre.Result = mouldBarcode;
                        }
                        //cre.Properties["MouldBarcode"] = mouldBarcode;

                        sql =
                        "INSERT INTO tp_pc_mouldchangehistory\n" +
                        "  (CHANGEID\n" +
                        "  ,MOULDID\n" +
                        "  ,MOULDCODE\n" +
                        "  ,MOULDBARCODE\n" +
                        "  ,GOODSID\n" +
                        "  ,GOODSCODE\n" +
                        "  ,OPERATIONTYPE\n" +
                        "  ,MOULDSTATUS\n" +
                        "  ,MOULDSTATUSAFTER\n" +
                        "  ,GROUTINGNUM\n" +
                        "  ,REMARKS\n" +
                        "  ,CHANGEFLAG\n" +
                        "  ,ACCOUNTID\n" +
                        "  ,CREATEUSERID)\n" +
                        "  Values( :changeID\n" +
                        "        ,:MOULDID\n" +
                        "        ,:MOULDCODE\n" +
                        "        ,:MOULDBARCODE\n" +
                        "        ,:GOODSID\n" +
                        "        ,:GOODSCODE\n" +
                        "        ,:OPERATIONTYPE\n" +
                        "        ,0\n" +
                        "        ,1\n" +
                        "        ,0\n" +
                        "        ,:REMARKS\n" +
                        "        ,'1'\n" +
                        "        ,:ACCOUNTID\n" +
                        "        ,:CREATEUSERID\n" +
                        " )";

                        paras = new OracleParameter[]
                        {
                            new OracleParameter(":changeID", OracleDbType.Int32,
                                mouldHistoryID, ParameterDirection.Input),
                            new OracleParameter(":MOULDID", OracleDbType.Int32,
                                mouldID, ParameterDirection.Input),
                            new OracleParameter(":MOULDCODE", OracleDbType.Varchar2,
                                mouldcode, ParameterDirection.Input),
                            new OracleParameter(":MOULDBARCODE", OracleDbType.Varchar2,
                                mouldBarcode, ParameterDirection.Input),
                            new OracleParameter(":GOODSID", OracleDbType.Int32,
                                cre.Properties["GoodsID"], ParameterDirection.Input),
                            new OracleParameter(":GOODSCODE", OracleDbType.NVarchar2,
                                cre.Properties["GoodsCode"], ParameterDirection.Input),
                            new OracleParameter(":OPERATIONTYPE", OracleDbType.Int32,
                                (int)Constant.MouldOperationType.ToInv, ParameterDirection.Input),
                            new OracleParameter(":REMARKS", OracleDbType.NVarchar2,
                                cre.Properties["Remarks"], ParameterDirection.Input),
                            new OracleParameter(":ACCOUNTID", OracleDbType.Int32,
                                sUserInfo.AccountID, ParameterDirection.Input),
                            new OracleParameter(":CREATEUSERID", OracleDbType.Int32,
                                sUserInfo.UserID, ParameterDirection.Input),
                        };
                        retrunRow += con.ExecuteNonQuery(sql, paras);

                        sql =
                        "INSERT INTO tp_pc_mould\n" +
                        "  (MOULDID\n" +
                        "  ,MOULDCODE\n" +
                        "  ,MOULDBARCODE\n" +
                        "  ,MOULDTYPE\n" +
                        "  ,MATERIALSUPPLIER\n" +
                        "  ,PRODUCTIONDATE\n" +
                        "  ,GOODSID\n" +
                        "  ,GOODSCODE\n" +
                        "  ,USERID\n" +
                        "  ,USERCODE\n" +
                        "  ,WEIGHT\n" +
                        "  ,COST\n" +
                        "  ,STANDARDGROUTINGNUM\n" +
                        "  ,OUTPUTCOUNT\n" +
                        "  ,MOULDSTATUS\n" +
                        "  ,PREMOULDSTATUS\n" +
                        "  ,LASTCHANGEHISTORYID\n" +
                        "  ,REMARKS\n" +
                        "  ,MOULD_PLAN_ID\n" + //add 夏常明 模具生产计划id 2023-6-26
                        "  ,ACCOUNTID\n" +
                        "  ,CREATEUSERID\n" +
                        "  ,UPDATEUSERID)\n" +
                        "VALUES\n" +
                        "  (:MOULDID\n" +
                        "  ,:MOULDCODE\n" +
                        "  ,:MOULDBARCODE\n" +
                        "  ,:MOULDTYPE\n" +
                        "  ,:MATERIALSUPPLIER\n" +
                        "  ,:PRODUCTIONDATE\n" +
                        "  ,:GOODSID\n" +
                        "  ,:GOODSCODE\n" +
                        "  ,:USERID\n" +
                        "  ,:USERCODE\n" +
                        "  ,:WEIGHT\n" +
                        "  ,:COST\n" +
                        "  ,:STANDARDGROUTINGNUM\n" +
                        "  ,:OUTPUTCOUNT\n" +
                        "  ,:MOULDSTATUS\n" +
                        "  ,:PREMOULDSTATUS\n" +
                        "  ,:LASTCHANGEHISTORYID\n" +
                        "  ,:REMARKS\n" +
                        "  ,:MOULD_PLAN_ID\n" + //add 夏常明 模具生产计划id 2023-6-26
                        "  ,:ACCOUNTID\n" +
                        "  ,:CREATEUSERID\n" +
                        "  ,:CREATEUSERID)";
                        paras = new OracleParameter[]
                        {
                            new OracleParameter(":MOULDCODE", OracleDbType.Varchar2,
                                mouldcode, ParameterDirection.Input),
                            new OracleParameter(":MOULDSTATUS", OracleDbType.Int32,
                                (int)Constant.MouldStatus.Inv, ParameterDirection.Input),
                            new OracleParameter(":PREMOULDSTATUS", OracleDbType.Int32,
                                (int)Constant.MouldStatus.Add, ParameterDirection.Input),
                            new OracleParameter(":LASTCHANGEHISTORYID", OracleDbType.Int32,
                                mouldHistoryID, ParameterDirection.Input),
                            new OracleParameter(":MOULDID", OracleDbType.Int32,
                                mouldID, ParameterDirection.Input),
                            new OracleParameter(":MOULDBARCODE", OracleDbType.Varchar2,
                                mouldBarcode, ParameterDirection.Input),
                            new OracleParameter(":MOULDTYPE", OracleDbType.Int32,
                                cre.Properties["MouldType"], ParameterDirection.Input),
                            new OracleParameter(":MATERIALSUPPLIER", OracleDbType.Int32,
                                cre.Properties["MaterialSupplier"], ParameterDirection.Input),
                            new OracleParameter(":PRODUCTIONDATE", OracleDbType.Date,
                                cre.Properties["ProductionDate"], ParameterDirection.Input),
                            new OracleParameter(":GOODSID", OracleDbType.Int32,
                                cre.Properties["GoodsID"], ParameterDirection.Input),
                            new OracleParameter(":GOODSCODE", OracleDbType.NVarchar2,
                                cre.Properties["GoodsCode"], ParameterDirection.Input),
                            new OracleParameter(":USERID", OracleDbType.Int32,
                                cre.Properties["UserID"], ParameterDirection.Input),
                            new OracleParameter(":USERCODE", OracleDbType.NVarchar2,
                                cre.Properties["UserCode"], ParameterDirection.Input),
                            new OracleParameter(":WEIGHT", OracleDbType.Decimal,
                                cre.Properties["Weight"], ParameterDirection.Input),
                            new OracleParameter(":COST", OracleDbType.Decimal,
                                cre.Properties["Cost"], ParameterDirection.Input),
                            new OracleParameter(":STANDARDGROUTINGNUM", OracleDbType.Decimal,
                                cre.Properties["StandardGroutingNum"], ParameterDirection.Input),
                            new OracleParameter(":OUTPUTCOUNT", OracleDbType.Decimal,
                                cre.Properties["OutputCount"], ParameterDirection.Input),
                            new OracleParameter(":REMARKS", OracleDbType.NVarchar2,
                                cre.Properties["Remarks"], ParameterDirection.Input),
                            new OracleParameter(":MOULD_PLAN_ID", OracleDbType.Int32,
                                cre.Properties["MOULD_PLAN_ID"], ParameterDirection.Input),// 模具生产计划id
                            new OracleParameter(":ACCOUNTID", OracleDbType.Int32,
                                sUserInfo.AccountID, ParameterDirection.Input),
                            new OracleParameter(":CREATEUSERID", OracleDbType.Int32,
                                sUserInfo.UserID, ParameterDirection.Input),
                        };
                        retrunRow += con.ExecuteNonQuery(sql, paras);

                        //更新模具生产计划完成数量 添加报工履历 add 夏常明 2023-6-26
                        if (!string.IsNullOrEmpty(cre.Properties["MOULD_PLAN_ID"] + ""))
                        {
                            //更新模具生产计划完成数量
                            string sqlString = "UPDATE t_mould_plan\n" +
                            "   SET make_qty = make_qty + 1\n" +
                            " WHERE plan_id = :MOULD_PLAN_ID";
                            paras = new OracleParameter[]
                            {
                                new OracleParameter(":MOULD_PLAN_ID", OracleDbType.Int32,
                                cre.Properties["MOULD_PLAN_ID"], ParameterDirection.Input),// 模具生产计划id
                            };
                            retrunRow += con.ExecuteNonQuery(sqlString, paras);

                            //添加报工履历

                            sqlString = "INSERT INTO t_mould_plan_report_his\n" +
                            "    (his_type\n" +
                            "    ,plan_id\n" +
                            "    ,plan_code\n" +
                            "    ,mouldid\n" +
                            "    ,goodsid\n" +
                            "    ,materialsupplier\n" +
                            "    ,CREATEUSER\n" +
                            "    ,accountid)\n" +
                            "VALUES\n" +
                            "    (1\n" +
                            "    ,:plan_id\n" +
                            "    ,:plan_code\n" +
                            "    ,:mouldid\n" +
                            "    ,:goodsid\n" +
                            "    ,:materialsupplier\n" +
                            "    ,:CREATEUSER\n" +
                            "    ,:accountid)";
                            paras = new OracleParameter[]
                            {
                                 new OracleParameter(":plan_id", OracleDbType.Int32,
                                cre.Properties["MOULD_PLAN_ID"], ParameterDirection.Input),// 模具生产计划id
                                 new OracleParameter(":plan_code", OracleDbType.Varchar2,
                                plan_code ,ParameterDirection.Input),
                                 new OracleParameter(":mouldid", OracleDbType.Int32,
                                mouldID ,ParameterDirection.Input),
                                 new OracleParameter(":goodsid", OracleDbType.Int32,
                                cre.Properties["GoodsID"] ,ParameterDirection.Input),
                                 new OracleParameter(":materialsupplier", OracleDbType.Int32,
                                cre.Properties["MaterialSupplier"], ParameterDirection.Input),
                                 new OracleParameter(":accountid", OracleDbType.Int32,
                                sUserInfo.AccountID, ParameterDirection.Input),
                                 new OracleParameter(":CREATEUSER", OracleDbType.Int32,
                                sUserInfo.UserID, ParameterDirection.Input),
                            };
                            retrunRow += con.ExecuteNonQuery(sqlString, paras);

                        }

                        // 多胞胎
                        int outputcount = Convert.ToInt32(cre.Properties["OutputCount"]);
                        if (outputcount > 1)
                        {
                            string sqlString = "insert into TP_PC_MouldOutput\n" +
                            "  (MouldID, MouldCode, OutputNo, OutputCount, GroutingNum, CreateUserID)\n" +
                            "values\n" +
                            "  (:MouldID, :MouldCode, :OutputNo, :OutputCount, 0, :CreateUserID)";
                            for (int j = 1; j <= outputcount; j++)
                            {
                                paras = new OracleParameter[]
                                {
                                    new OracleParameter(":MouldID", OracleDbType.Int32,
                                        mouldID, ParameterDirection.Input),
                                    new OracleParameter(":MouldCode", OracleDbType.Varchar2,
                                        mouldcode, ParameterDirection.Input),
                                    new OracleParameter(":OutputNo", OracleDbType.Int32,
                                        j, ParameterDirection.Input),
                                    new OracleParameter(":OutputCount", OracleDbType.Int32,
                                        outputcount , ParameterDirection.Input),
                                    new OracleParameter(":CreateUserID", OracleDbType.Int32,
                                        sUserInfo.UserID, ParameterDirection.Input),
                                };
                                retrunRow += con.ExecuteNonQuery(sqlString, paras);
                            }
                        }
                    }
                }
                else
                {
                    sre.Result = cre.Properties["MouldBarcode"];
                    // 编辑
                    string sqlString = "UPDATE tp_pc_mould m\n" +
                    "   SET m.mouldtype           = :mouldtype\n" +
                    "      ,m.materialsupplier    = :materialsupplier\n" +
                    "      ,m.productiondate      = :productiondate\n" +
                    "      ,m.userid              = :userid\n" +
                    "      ,m.usercode            = :usercode\n" +
                    "      ,m.weight              = :weight\n" +
                    "      ,m.cost                = :cost\n" +
                    "      ,m.standardgroutingnum = :standardgroutingnum\n" +
                    "      ,m.remarks             = :remarks\n" +
                    "      ,m.UpdateUserID        = :UpdateUserID\n" +
                    " WHERE m.mouldid = :mouldid";

                    OracleParameter[] parameters = new OracleParameter[]
                    {
                        new OracleParameter(":mouldtype", OracleDbType.Int32,
                            cre.Properties["MouldType"], ParameterDirection.Input),
                        new OracleParameter(":materialsupplier", OracleDbType.Int32,
                            cre.Properties["MaterialSupplier"], ParameterDirection.Input),
                        new OracleParameter(":productiondate", OracleDbType.Date,
                            cre.Properties["ProductionDate"], ParameterDirection.Input),
                        new OracleParameter(":userid", OracleDbType.Int32,
                            cre.Properties["UserID"], ParameterDirection.Input),
                        new OracleParameter(":usercode", OracleDbType.NVarchar2,
                            cre.Properties["UserCode"], ParameterDirection.Input),
                        new OracleParameter(":weight", OracleDbType.Decimal,
                            cre.Properties["Weight"], ParameterDirection.Input),
                        new OracleParameter(":cost", OracleDbType.Decimal,
                            cre.Properties["Cost"], ParameterDirection.Input),
                        new OracleParameter(":standardgroutingnum", OracleDbType.Decimal,
                            cre.Properties["StandardGroutingNum"], ParameterDirection.Input),
                        new OracleParameter(":remarks", OracleDbType.NVarchar2,
                            cre.Properties["Remarks"], ParameterDirection.Input),
                        new OracleParameter(":mouldid", OracleDbType.Int32,
                            mouldID, ParameterDirection.Input),
                        new OracleParameter(":UpdateUserID", OracleDbType.Int32,
                            sUserInfo.UserID, ParameterDirection.Input),
                    };
                    retrunRow = con.ExecuteNonQuery(sqlString, parameters);
                }

                con.Commit();
                return sre;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (con.ConnState == ConnectionState.Open)
                {
                    con.Disconnect();
                }
            }
        }

        /// <summary>
        /// 获取模具信息
        /// </summary>
        /// <param name="sUserInfo"></param>
        /// <returns></returns>
        public static ServiceResultEntity GetMouldData(SUserInfo sUserInfo, ClientRequestEntity cre)
        {
            ServiceResultEntity sre = new ServiceResultEntity();
            int? mouldID = null;
            string mouldBarcode = null;
            if (cre.Properties.ContainsKey("MouldID"))
            {
                mouldID = cre.Properties.GetValue<int?>("MouldID");
            }
            if (cre.Properties.ContainsKey("MouldBarcode"))
            {
                mouldBarcode = cre.Properties["MouldBarcode"].ToString();
            }
            IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString);
            try
            {
                con.Open();
                string sqlString =
                    "SELECT m.MouldID\n" +
                    "      ,m.MouldCode\n" +
                    "      ,m.MouldBarcode\n" +
                    "      ,mtype.dictionaryvalue    MouldTypeName\n" +
                    "      ,mms.SupplierName\n" +
                    "      ,m.ProductionDate\n" +
                    "      ,m.GoodsID\n" +
                    "      ,m.GoodsCode\n" +
                    "      ,g.GoodsName\n" +
                    "      ,g.GoodsSpecification\n" +
                    "      ,m.UserID\n" +
                    "      ,m.UserCode\n" +
                    "      ,m.Weight\n" +
                    "      ,m.Cost\n" +
                    "      ,m.StandardGroutingNum\n" +
                    "      ,m.OutputCount\n" +
                    "      ,m.GroutingNum GroutingNums\n" +
                    "      ,m.GroutingDateBegin\n" +
                    "      ,m.GroutingDateEnd\n" +
                    "      ,m.LastGroutingBatchNo\n" +
                    "      ,m.MouldStatus\n" +
                    "      ,ms.mouldstatusname\n" +
                    "      ,m.RepairFlag\n" +
                    "      ,m.ScrapDate\n" +
                    "      ,m.ScrapUserID\n" +
                    "      ,m.ScrapUserCode\n" +
                    "      ,ScReason.dictionaryvalue ScrapReason\n" +
                    "      ,m.ScrapResponsibility\n" +
                    "      ,gl.groutinglinecode\n" +
                    "      ,gld.groutingmouldcode\n" +
                    "      ,m.Remarks\n" +
                    "      ,m.CreateTime\n" +
                    "      ,cu.usercode              CreateUserCode\n" +
                    "      ,nvl(mo.OutputNo,0) OutputNo\n" +
                    "      ,nvl(mo.GroutingNum, m.GroutingNum) GroutingNum\n" +
                    "  FROM tp_pc_mould m\n" +
                    "  LEFT JOIN tp_mst_datadictionary mtype\n" +
                    "    ON mtype.dictionaryid = m.mouldtype\n" +
                    "  LEFT JOIN tp_mst_mouldmaterialsuppliers mms\n" +
                    "    ON mms.SupplierID = m.MaterialSupplier\n" +
                    " INNER JOIN tp_sys_mouldstatus ms\n" +
                    "    ON ms.mouldstatusid = m.MouldStatus\n" +
                    "  LEFT JOIN tp_mst_datadictionary ScReason\n" +
                    "    ON ScReason.dictionaryid = m.ScrapReason\n" +
                    "  LEFT JOIN tp_pc_groutinglinedetail gld\n" +
                    "    ON gld.groutinglinedetailid = m.GroutingLineDetailID\n" +
                    "  LEFT JOIN tp_pc_groutingline gl\n" +
                    "    ON gl.groutinglineid = gld.groutinglineid\n" +
                    " INNER JOIN tp_mst_user cu\n" +
                    "    ON cu.userid = m.createuserid\n" +
                    " INNER JOIN tp_mst_goods g\n" +
                    "    ON g.goodsid = m.goodsid\n" +
                    "  LEFT JOIN TP_PC_MouldOutput mo\n" +
                    "    ON mo.mouldid = m.mouldid\n" +
                    " WHERE m.accountid = :accountid\n" +
                    "   AND m.valueflag = '1'\n" +
                    "   AND (:MouldBarcode IS NULL OR m.MouldBarcode = :MouldBarcode)\n" +
                    "   AND (:MouldStatus IS NULL OR\n" +
                    "       instr(',' || :MouldStatus || ',', ',' || m.MouldStatus || ',') > 0)\n" +
                    "   AND (:MouldID IS NULL OR m.MouldID = :MouldID)";

                OracleParameter[] parameters = new OracleParameter[]
                {
                    new OracleParameter(":accountid", OracleDbType.Int32, sUserInfo.AccountID, ParameterDirection.Input),
                    new OracleParameter(":MouldID", OracleDbType.Int32, mouldID, ParameterDirection.Input),
                    new OracleParameter(":MouldBarcode", OracleDbType.NVarchar2, mouldBarcode, ParameterDirection.Input),
                    new OracleParameter(":MouldStatus", OracleDbType.Varchar2, cre.Properties["MouldStatus"], ParameterDirection.Input),
                };
                sre.Data = con.GetSqlResultToDs(sqlString, parameters);
                sre.Data.Tables[0].TableName = "InfoData";

                return sre;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (con.ConnState == ConnectionState.Open)
                {
                    con.Close();
                }
            }
        }

        /// <summary>
        /// 替换模具条码
        /// </summary>
        /// <param name="sUserInfo"></param>
        /// <returns></returns>
        public static ServiceResultEntity SetFPC1003Data(SUserInfo sUserInfo, ClientRequestEntity cre)
        {
            ServiceResultEntity sre = new ServiceResultEntity();
            int mouldID = Convert.ToInt32(cre.Properties["MouldID"]);
            string mouldBarcode = cre.Properties["MouldBarcode"].ToString();
            string newMouldBarcode = cre.Properties["NewMouldBarcode"].ToString();
            int retrunRow = 0;
            IDBTransaction con = ClsDbFactory.CreateDBTransaction(DataBaseType.ORACLE, DataManager.ConnectionString);
            try
            {
                con.Connect();

                // 验证 条码是否重复
                string sql = "Select count(*) from TP_PC_Mould m where m.MouldBarcode = :MouldBarcode";
                OracleParameter[] paras = new OracleParameter[]
                {
                    new OracleParameter(":MouldBarcode", OracleDbType.Varchar2,
                        newMouldBarcode, ParameterDirection.Input),
                };

                string count = con.GetSqlResultToStr(sql, paras);
                if ("0" != count)
                {
                    sre.Status = Constant.ServiceResultStatus.Other;
                    sre.Message = "新模具条码【" + newMouldBarcode + "】已存在";
                    return sre;
                }

                int mouldHistoryID = Convert.ToInt32(con.GetSqlResultToObj("select seq_pc_mouldhistory_id.nextval from dual "));

                sql =
                "INSERT INTO tp_pc_mouldchangehistory\n" +
                "  (CHANGEID\n" +
                "  ,MOULDID\n" +
                "  ,MOULDCODE\n" +
                "  ,MOULDBARCODE\n" +
                "  ,GOODSID\n" +
                "  ,GOODSCODE\n" +
                "  ,OPERATIONTYPE\n" +
                "  ,GroutingLineDetailID\n" +
                "  ,MOULDSTATUS\n" +
                "  ,MOULDSTATUSAFTER\n" +
                "  ,GROUTINGNUM\n" +
                "  ,REMARKS\n" +
                "  ,CHANGEFLAG\n" +
                "  ,MouldBarcodeAfter\n" +
                "  ,ACCOUNTID\n" +
                "  ,CREATEUSERID)\n" +
                "  SELECT :changeID\n" +
                "        ,MOULDID\n" +
                "        ,MOULDCODE\n" +
                "        ,:MOULDBARCODE\n" +
                "        ,GOODSID\n" +
                "        ,GOODSCODE\n" +
                "        ,:OPERATIONTYPE\n" +
                "        ,GroutingLineDetailID\n" +
                "        ,MOULDSTATUS\n" +
                "        ,MOULDSTATUS\n" +
                "        ,GROUTINGNUM\n" +
                "        ,:REMARKS\n" +
                "        ,'0'\n" +
                "        ,:NEWMOULDBARCODE\n" +
                "        ,ACCOUNTID\n" +
                "        ,CREATEUSERID\n" +
                "    FROM tp_pc_mould m\n" +
                "   WHERE m.mouldID = :MouldID";

                paras = new OracleParameter[]
                {
                    new OracleParameter(":MouldID", OracleDbType.Int32,
                        mouldID, ParameterDirection.Input),
                    new OracleParameter(":OPERATIONTYPE", OracleDbType.Int32,
                        (int)Constant.MouldOperationType.ChangeBarcode, ParameterDirection.Input),
                    new OracleParameter(":changeID", OracleDbType.Int32,
                        mouldHistoryID, ParameterDirection.Input),
                    new OracleParameter(":NEWMOULDBARCODE", OracleDbType.NVarchar2,
                        newMouldBarcode, ParameterDirection.Input),
                    new OracleParameter(":MOULDBARCODE", OracleDbType.NVarchar2,
                        mouldBarcode, ParameterDirection.Input),
                    new OracleParameter(":REMARKS", OracleDbType.NVarchar2,
                        cre.Properties["Remarks"], ParameterDirection.Input),
                };
                retrunRow += con.ExecuteNonQuery(sql, paras);

                sql = "UPDATE tp_pc_mould m\n" +
                "   SET m.mouldbarcode        = :newmouldbarcode\n" +
                "      ,m.updateuserid        = :userid\n" +
                "      ,m.lastchangehistoryid = :changeid\n" +
                " WHERE m.mouldid = :mouldid";

                paras = new OracleParameter[]
                {
                    new OracleParameter(":newmouldbarcode", OracleDbType.NVarchar2,
                        newMouldBarcode, ParameterDirection.Input),
                    new OracleParameter(":mouldid", OracleDbType.Int32,
                        mouldID, ParameterDirection.Input),
                    new OracleParameter(":changeid", OracleDbType.Int32,
                        mouldHistoryID, ParameterDirection.Input),
                    new OracleParameter(":userid", OracleDbType.Int32,
                        sUserInfo.UserID, ParameterDirection.Input),
                };
                retrunRow += con.ExecuteNonQuery(sql, paras);

                con.Commit();
                return sre;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (con.ConnState == ConnectionState.Open)
                {
                    con.Disconnect();
                }
            }
        }

        /// <summary>
        /// 获取模具操作画面期初数据
        /// </summary>
        /// <param name="sUserInfo"></param>
        /// <returns></returns>
        public static ServiceResultEntity GetFPC1004LoadData(SUserInfo sUserInfo, ClientRequestEntity cre)
        {
            ServiceResultEntity sre = new ServiceResultEntity();
            int mouldOperationType = Convert.ToInt32(cre.Properties["MouldOperationType"]);
            IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString);
            try
            {
                string sqlString = null;
                OracleParameter[] parameters = null;

                if (mouldOperationType == (int)Constant.MouldOperationType.InvToScrap ||
                    mouldOperationType == (int)Constant.MouldOperationType.OnLineToScrap ||
                    mouldOperationType == (int)Constant.MouldOperationType.OutToScrap)
                {
                    // 报废原因
                    sqlString = "SELECT dd.dictionaryid, dd.dictionaryvalue, dd.displayno\n" +
                    "  FROM TP_MST_DataDictionary dd\n" +
                    " WHERE dd.DictionaryType = 'GMM001'\n" +
                    "   AND dd.valueflag = '1'\n" +
                    "   AND dd.accountid = :accountid\n" +
                    " ORDER BY dd.displayno";
                    parameters = new OracleParameter[]
                    {
                        new OracleParameter(":accountid", OracleDbType.Int32, sUserInfo.AccountID, ParameterDirection.Input),
                    };
                    sre.Data = con.GetSqlResultToDs(sqlString, parameters);
                }

                return sre;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (con.ConnState == ConnectionState.Open)
                {
                    con.Close();
                }
            }
        }

        /// <summary>
        /// 模具操作
        /// </summary>
        /// <param name="sUserInfo"></param>
        /// <returns></returns>
        public static ServiceResultEntity SetFPC1004Data(SUserInfo sUserInfo, ClientRequestEntity cre)
        {
            ServiceResultEntity sre = new ServiceResultEntity();
            int mouldOperationType = Convert.ToInt32(cre.Properties["MouldOperationType"]);
            int retrunRow = 0;
            IDBTransaction con = ClsDbFactory.CreateDBTransaction(DataBaseType.ORACLE, DataManager.ConnectionString);
            try
            {
                con.Connect();

                OracleParameter[] paras = null;
                object goods = cre.Properties["GoodsID"];
                string sql =
                "SELECT m.MouldID\n" +
                "      ,m.MouldCode\n" +
                "      ,m.MouldBarcode\n" +
                "      ,m.GoodsID\n" +
                "      ,m.GoodsCode\n" +
                "      ,m.GroutingNum\n" +
                "      ,m.MouldStatus\n" +
                //"      ,m.RepairFlag\n" +
                //"      ,m.ScrapDate\n" +
                //"      ,m.ScrapUserID\n" +
                //"      ,m.ScrapUserCode\n" +
                "      ,m.GroutingLineDetailID\n" +
                "      ,m.MaterialSupplier\n" +
                "      ,m.mould_plan_id\n" + //add 夏常明 模具生产计划id 
                "  FROM tp_pc_mould m\n" +
                " WHERE m.accountid = :accountid\n" +
                "   AND m.valueflag = '1'\n" +
                "   AND m.MouldID in (" + cre.Properties["MouldIDs"] + ")\n" +
                " ORDER BY m.MouldBarcode for update";
                paras = new OracleParameter[]
                {
                    new OracleParameter(":accountid", OracleDbType.Int32,
                        sUserInfo.AccountID, ParameterDirection.Input),
                };

                DataTable mouldTable = con.GetSqlResultToDt(sql, paras);



                // 变更型号
                if (mouldOperationType == (int)Constant.MouldOperationType.ChangeGoodsCode)
                {
                    #region
                    //校验是否存在已报工模具
                    DataRow[] mouldPlanDt = mouldTable.Select("  mould_plan_id IS NOT NULL", null);
                    if (mouldPlanDt.Length > 0)
                    {
                        sre.Message = "已报工模具，不能变更型号！";
                        sre.Status = Constant.ServiceResultStatus.Other;
                        return sre;
                    }
                    foreach (DataRow item in mouldTable.Rows)
                    {
                        int mouldStatus = Convert.ToInt32(item["MouldStatus"]);
                        if (mouldStatus != 1 && mouldStatus != 2 && mouldStatus != 3)
                        {
                            continue;
                        }

                        int mouldHistoryID = Convert.ToInt32(con.GetSqlResultToObj("select seq_pc_mouldhistory_id.nextval from dual "));
                        //操作类别
                        DataTable OPERATIONTYPE = con.GetSqlResultToDt(@"SELECT OPERATIONTYPE FROM TP_PC_MOULDCHANGEHISTORY
                                                                WHERE CREATETIME=(SELECT MAX(CREATETIME)
                                                                FROM TP_PC_MOULDCHANGEHISTORY WHERE MOULDID=" + item["MouldID"] + ") AND  MOULDID = " + item["MouldID"] + "", null);
                        sql =
                        "INSERT INTO tp_pc_mouldchangehistory\n" +
                        "  (CHANGEID\n" +
                        "  ,MOULDID\n" +
                        "  ,MOULDCODE\n" +
                        "  ,MOULDBARCODE\n" +
                        "  ,GOODSID\n" +
                        "  ,GOODSCODE\n" +
                        "  ,OPERATIONTYPE\n" +
                        "  ,GroutingLineDetailID\n" +
                        "  ,MOULDSTATUS\n" +
                        "  ,MOULDSTATUSAFTER\n" +
                        "  ,GROUTINGNUM\n" +
                        "  ,REMARKS\n" +
                        "  ,CHANGEFLAG\n" +
                        "  ,GoodsIDAfter\n" +
                        "  ,GoodsCodeAfter\n" +
                        "  ,ACCOUNTID\n" +
                        "  ,CREATEUSERID)\n" +
                        "  Values( :changeID\n" +
                        "        ,:MOULDID\n" +
                        "        ,:MOULDCODE\n" +
                        "        ,:MOULDBARCODE\n" +
                        "        ,:GOODSID\n" +
                        "        ,:GOODSCODE\n" +
                        "        ,:OPERATIONTYPE\n" +
                        "        ,:GroutingLineDetailID\n" +
                        "        ,:MOULDSTATUS\n" +
                        "        ,:MOULDSTATUS\n" +
                        "        ,:GROUTINGNUM\n" +
                        "        ,:REMARKS\n" +
                        "        ,decode(:MOULDSTATUS,1,'3','0')\n" +
                        "        ,:GoodsIDAfter\n" +
                        "        ,:GoodsCodeAfter\n" +
                        "        ,:ACCOUNTID\n" +
                        "        ,:USERID\n" +
                        ")";
                        paras = new OracleParameter[]
                        {
                            new OracleParameter(":GroutingLineDetailID", OracleDbType.Int32,
                                item["GroutingLineDetailID"], ParameterDirection.Input),
                            new OracleParameter(":changeID", OracleDbType.Int32,
                                mouldHistoryID, ParameterDirection.Input),
                            new OracleParameter(":MOULDID", OracleDbType.Int32,
                                item["MouldID"], ParameterDirection.Input),
                            new OracleParameter(":MOULDCODE", OracleDbType.Varchar2,
                                item["MOULDCODE"], ParameterDirection.Input),
                            new OracleParameter(":MOULDBARCODE", OracleDbType.NVarchar2,
                                item["MOULDBARCODE"], ParameterDirection.Input),
                            new OracleParameter(":GoodsCodeAfter", OracleDbType.NVarchar2,
                                cre.Properties["GoodsCode"], ParameterDirection.Input),
                            new OracleParameter(":GoodsIDAfter", OracleDbType.Int32,
                                cre.Properties["GoodsID"], ParameterDirection.Input),
                            new OracleParameter(":GOODSID", OracleDbType.Int32,
                                item["GOODSID"], ParameterDirection.Input),
                            new OracleParameter(":GOODSCODE", OracleDbType.NVarchar2,
                                item["GOODSCODE"], ParameterDirection.Input),
                            new OracleParameter(":OPERATIONTYPE", OracleDbType.Int32,
                                mouldOperationType, ParameterDirection.Input),
                            new OracleParameter(":MOULDSTATUS", OracleDbType.Int32,
                                item["MOULDSTATUS"], ParameterDirection.Input),
                            new OracleParameter(":GROUTINGNUM", OracleDbType.Int32,
                                item["GROUTINGNUM"], ParameterDirection.Input),
                            new OracleParameter(":REMARKS", OracleDbType.NVarchar2,
                                cre.Properties["Remarks"], ParameterDirection.Input),
                            new OracleParameter(":ACCOUNTID", OracleDbType.Int32,
                                sUserInfo.AccountID, ParameterDirection.Input),
                            new OracleParameter(":USERID", OracleDbType.Int32,
                                sUserInfo.UserID, ParameterDirection.Input),
                        };
                        retrunRow += con.ExecuteNonQuery(sql, paras);

                        

                        if (mouldStatus == 2)
                        {
                            // 改成型线明细的产品型号
                            sql = "UPDATE TP_PC_GroutingLineDetail gld\n" +
                            "   SET gld.goodsid             = :goodsid\n" +
                            "      ,gld.updateuserid        = :userid\n" +
                            " WHERE gld.GroutingLineDetailID = :DetailID and gld.mouldid = :mouldid";
                            paras = new OracleParameter[]
                            {
                                new OracleParameter(":goodsid", OracleDbType.Int32,
                                    cre.Properties["GoodsID"], ParameterDirection.Input),
                                new OracleParameter(":DetailID", OracleDbType.Int32,
                                    item["GroutingLineDetailID"], ParameterDirection.Input),
                                new OracleParameter(":userid", OracleDbType.Int32,
                                    sUserInfo.UserID, ParameterDirection.Input),
                                new OracleParameter(":mouldid", OracleDbType.Int32,
                                    item["MouldID"], ParameterDirection.Input),
                            };
                            retrunRow += con.ExecuteNonQuery(sql, paras);
                        }
						#region SAP模具变更接口传值
						if (goods.ToString()!=item["GOODSID"].ToString() && (OPERATIONTYPE.Rows[0]["OPERATIONTYPE"].ToString() == "8" || OPERATIONTYPE.Rows[0]["OPERATIONTYPE"].ToString() == "-1"))
                        {
                            sql = @"SELECT
                                     m.MouldID,
                                     m.MOULDBARCODE,
                                     (SELECT g.mouldmaterialcode FROM tp_mst_goods g WHERE g.goodsid = :goodsID ) AS 变更后,
                                     g.mouldmaterialcode AS 变更前,
                                     m.STANDARDGROUTINGSUM - m.GroutingNum  as 剩余注浆次数,
                                    CASE WHEN INSTR(GL.GROUTINGLINECODE, 'A') = 1 THEN '2110'
						                                    WHEN (INSTR(GL.GROUTINGLINECODE, 'B') = 1 OR INSTR(GL.GROUTINGLINECODE, 'D') = 1) THEN '2210'
						                                    WHEN INSTR(GL.GROUTINGLINECODE, 'C') = 1 THEN '2310'
						                                    ELSE '3001' END  AS 库存地点
                                   FROM tp_pc_mould m
                                    left join tp_mst_goods g  on m.goodsid = g.goodsid
                                    LEFT JOIN TP_PC_MOULDCHANGEHISTORY mhis on mhis.MOULDID=m.MOULDID
                                    LEFT JOIN TP_PC_GROUTINGLINEDETAIL gd on gd.GROUTINGLINEDETAILID=mhis.GROUTINGLINEDETAILID
                                    LEFT JOIN TP_PC_GROUTINGLINE gl ON gl.GROUTINGLINEID=gd.GROUTINGLINEID
                                    WHERE m.MouldID =:MouldIDs
                                    AND m.ACCOUNTID=:accountid
                                    AND m.VALUEFLAG='1'
                                    AND (mhis.OPERATIONTYPE='8' OR mhis.OPERATIONTYPE='-1')
                                    AND MHIS.CREATETIME=(SELECT MAX(CREATETIME) FROM TP_PC_MOULDCHANGEHISTORY WHERE MouldID =:MouldIDs  AND ( OPERATIONTYPE='8'OR OPERATIONTYPE='-1')) ";
                            paras = new OracleParameter[]
                            {
                                new OracleParameter(":accountid", OracleDbType.NVarchar2,  sUserInfo.AccountID, ParameterDirection.Input),
                                new OracleParameter(":MouldIDs", OracleDbType.NVarchar2, item["MouldID"], ParameterDirection.Input),
                                new OracleParameter(":goodsID", OracleDbType.NVarchar2,  goods.ToString(), ParameterDirection.Input),
                            };
                            DataTable dt = con.GetSqlResultToDt(sql, paras);

                            string ZTYPE;
                            string ZMSG;
                            ZPPFM050(DateTime.Now.Date.ToString("yyyyMMdd"), dt, out ZTYPE, out ZMSG);
              
                            string upsql = string.Empty;
                            if ("S".Equals(ZTYPE))
                            {
                                //数据库写入日志表名TP_SAP_MOULDCHANGELOG（成功）；
                                upsql = @"INSERT INTO TP_SAP_MOULDCHANGELOG(WERKS,MOULDID,MOULDBARCODE,MATERIALCODEFORMER,MATERIALCODEAFTER,INVENTORYPLACE,MSGTY,MSGTX)
                                                 	VALUES(5000,:MOULDID,:MOULDBARCODE,:MATERIALCODEFORMER,:MATERIALCODEAFTER,:INVENTORYPLACE,:MSGTY,:MSGTX)";
                                paras = new OracleParameter[]
                                {
                                new OracleParameter(":MOULDID", OracleDbType.NVarchar2, dt.Rows[0]["MouldID"].ToString(), ParameterDirection.Input),
                                new OracleParameter(":MOULDBARCODE", OracleDbType.NVarchar2,  dt.Rows[0]["MOULDBARCODE"].ToString(), ParameterDirection.Input),
                                new OracleParameter(":MATERIALCODEFORMER", OracleDbType.NVarchar2,   dt.Rows[0]["变更前"].ToString(), ParameterDirection.Input),
                                new OracleParameter(":MATERIALCODEAFTER", OracleDbType.NVarchar2,  dt.Rows[0]["变更后"].ToString(), ParameterDirection.Input),
                                new OracleParameter(":INVENTORYPLACE", OracleDbType.NVarchar2,  dt.Rows[0]["库存地点"].ToString(), ParameterDirection.Input),
                                new OracleParameter(":MSGTY", OracleDbType.NVarchar2,  ZTYPE, ParameterDirection.Input),
                                new OracleParameter(":MSGTX", OracleDbType.NVarchar2, ZMSG, ParameterDirection.Input),
                                };
                                con.ExecuteNonQuery(upsql, paras);
                                sre.Message = "";
                                sre.Status = Constant.ServiceResultStatus.Success;
                            }
                            else if ("E".Equals(ZTYPE))
                            {
                                //数据库写入日志表名TP_SAP_MOULDCHANGELOG（失败）；
                                upsql = @"INSERT INTO TP_SAP_MOULDCHANGELOG(WERKS,MOULDID,MOULDBARCODE,MATERIALCODEFORMER,MATERIALCODEAFTER,INVENTORYPLACE,MSGTY,MSGTX)
                                                 	VALUES(5000,:MOULDID,:MOULDBARCODE,:MATERIALCODEFORMER,:MATERIALCODEAFTER,:INVENTORYPLACE,:MSGTY,:MSGTX)";
                                paras = new OracleParameter[]
                                {
                                new OracleParameter(":MOULDID", OracleDbType.NVarchar2, dt.Rows[0]["MouldID"].ToString(), ParameterDirection.Input),
                                new OracleParameter(":MOULDBARCODE", OracleDbType.NVarchar2,  dt.Rows[0]["MOULDBARCODE"].ToString(), ParameterDirection.Input),
                                new OracleParameter(":MATERIALCODEFORMER", OracleDbType.NVarchar2,   dt.Rows[0]["变更前"].ToString(), ParameterDirection.Input),
                                new OracleParameter(":MATERIALCODEAFTER", OracleDbType.NVarchar2,  dt.Rows[0]["变更后"].ToString(), ParameterDirection.Input),
                                new OracleParameter(":INVENTORYPLACE", OracleDbType.NVarchar2,  dt.Rows[0]["库存地点"].ToString(), ParameterDirection.Input),
                                new OracleParameter(":MSGTY", OracleDbType.NVarchar2,  ZTYPE, ParameterDirection.Input),
                                new OracleParameter(":MSGTX", OracleDbType.NVarchar2, ZMSG, ParameterDirection.Input),
                                };
                                con.ExecuteNonQuery(upsql, paras);
                                con.Commit();

                                sre.Message = ZMSG;
                                sre.Status = Constant.ServiceResultStatus.Other;
                                return sre;
                            }
                        }

                        #endregion
                        //模具变更sql
                        sql = "UPDATE tp_pc_mould m\n" +
                "   SET m.goodsid             = :goodsid\n" +
                "      ,m.goodscode           = :goodscode\n" +
                "      ,m.updateuserid        = :userid\n" +
                "      ,m.lastchangehistoryid = :changeid\n" +
                " WHERE m.mouldid = :mouldid";

                        paras = new OracleParameter[]
                        {
                            new OracleParameter(":goodscode", OracleDbType.NVarchar2,
                                cre.Properties["GoodsCode"], ParameterDirection.Input),
                            new OracleParameter(":goodsid", OracleDbType.Int32,
                                cre.Properties["GoodsID"], ParameterDirection.Input),
                            new OracleParameter(":changeid", OracleDbType.Int32,
                                mouldHistoryID, ParameterDirection.Input),
                            new OracleParameter(":userid", OracleDbType.Int32,
                                sUserInfo.UserID, ParameterDirection.Input),
                            new OracleParameter(":mouldid", OracleDbType.Int32,
                                item["MouldID"], ParameterDirection.Input),
                        };
                        retrunRow += con.ExecuteNonQuery(sql, paras);
                    }
                    #region 转模具料号SAP
                    //待产
                    //sql = "SELECT m.MouldID\n" +
                    // "      ,m.MouldCode\n" +
                    // "      ,m.MouldBarcode\n" +
                    // "      ,m.GoodsID\n" +
                    // "      ,m.GoodsCode\n" +
                    // "      ,m.GroutingNum\n" +
                    // "      ,m.MouldStatus\n" +
                    // "      ,m.GroutingLineDetailID\n" +
                    // "      ,g.mouldmaterialcode\n" +
                    // "      ,m.STANDARDGROUTINGSUM - m.GroutingNum notusedGroutingNum\n" +
                    // "  FROM tp_pc_mould m\n" +
                    // "  left join tp_mst_goods g  on m.goodsid = g.goodsid\n" +
                    // " WHERE m.accountid = :accountid\n" +
                    // "   AND m.valueflag = '1'\n" +
                    // "   AND m.MouldID in (" + cre.Properties["MouldIDs"] + ")\n" +
                    // "   AND m.MOULDSTATUS = 3\n";
                    //paras = new OracleParameter[]
                    //{
                    //    new OracleParameter(":accountid", OracleDbType.Int32,  sUserInfo.AccountID, ParameterDirection.Input),
                    //};
                    //mouldTable = con.GetSqlResultToDt(sql, paras);
                    //string ZTYPE;
                    //string ZMSG;
                    //DataTable dt = new DataTable();
                    //// 查询更新后的产品编码-物料编码
                    //string sqlString = "SELECT g.mouldmaterialcode FROM tp_mst_goods g WHERE g.goodscode = :goodsCode";
                    //paras = new OracleParameter[]
                    //{
                    //    new OracleParameter(":goodsCode", OracleDbType.NVarchar2,cre.Properties["GoodsCode"].ToString(), ParameterDirection.Input),
                    //};
                    //DataTable newGoodsCodeTable = con.GetSqlResultToDt(sqlString, paras);
                    ////更新前的模具信息
                    //sqlString = "";
                    //DataTable dtResult = ZPPFM050(DateTime.Now.Date.ToString("yyyyMMdd"), dt, out ZTYPE, out ZMSG);

                    #endregion

                    #endregion
                }
                // 模具领用
                else if (mouldOperationType == (int)Constant.MouldOperationType.InvToOut)
                {
                    #region
                    foreach (DataRow item in mouldTable.Rows)
                    {
                        int mouldStatus = Convert.ToInt32(item["MouldStatus"]);
                        if (mouldStatus != 1)
                        {
                            continue;
                        }

                        int mouldHistoryID = Convert.ToInt32(con.GetSqlResultToObj("select seq_pc_mouldhistory_id.nextval from dual "));

                        sql =
                        "INSERT INTO tp_pc_mouldchangehistory\n" +
                        "  (CHANGEID\n" +
                        "  ,MOULDID\n" +
                        "  ,MOULDCODE\n" +
                        "  ,MOULDBARCODE\n" +
                        "  ,GOODSID\n" +
                        "  ,GOODSCODE\n" +
                        "  ,OPERATIONTYPE\n" +
                        "  ,MOULDSTATUS\n" +
                        "  ,MOULDSTATUSAFTER\n" +
                        "  ,GROUTINGNUM\n" +
                        "  ,REMARKS\n" +
                        "  ,CHANGEFLAG\n" +
                        "  ,ACCOUNTID\n" +
                        "  ,CREATEUSERID)\n" +
                        "  Values( :changeID\n" +
                        "        ,:MOULDID\n" +
                        "        ,:MOULDCODE\n" +
                        "        ,:MOULDBARCODE\n" +
                        "        ,:GOODSID\n" +
                        "        ,:GOODSCODE\n" +
                        "        ,:OPERATIONTYPE\n" +
                        "        ,:MOULDSTATUS\n" +
                        "        ,:MOULDSTATUSAFTER\n" +
                        "        ,:GROUTINGNUM\n" +
                        "        ,:REMARKS\n" +
                        "        ,'2'\n" +
                        "        ,:ACCOUNTID\n" +
                        "        ,:USERID\n" +
                        ")";
                        paras = new OracleParameter[]
                        {
                            new OracleParameter(":changeID", OracleDbType.Int32,
                                mouldHistoryID, ParameterDirection.Input),
                            new OracleParameter(":MOULDID", OracleDbType.Int32,
                                item["MouldID"], ParameterDirection.Input),
                            new OracleParameter(":MOULDCODE", OracleDbType.Varchar2,
                                item["MOULDCODE"], ParameterDirection.Input),
                            new OracleParameter(":MOULDBARCODE", OracleDbType.NVarchar2,
                                item["MOULDBARCODE"], ParameterDirection.Input),
                            new OracleParameter(":GOODSID", OracleDbType.Int32,
                                item["GOODSID"], ParameterDirection.Input),
                            new OracleParameter(":GOODSCODE", OracleDbType.NVarchar2,
                                item["GOODSCODE"], ParameterDirection.Input),
                            new OracleParameter(":OPERATIONTYPE", OracleDbType.Int32,
                                mouldOperationType, ParameterDirection.Input),
                            new OracleParameter(":MOULDSTATUSAFTER", OracleDbType.Int32,
                                (int)Constant.MouldStatus.Out, ParameterDirection.Input),
                            new OracleParameter(":MOULDSTATUS", OracleDbType.Int32,
                                item["MOULDSTATUS"], ParameterDirection.Input),
                            new OracleParameter(":GROUTINGNUM", OracleDbType.Int32,
                                item["GROUTINGNUM"], ParameterDirection.Input),
                            new OracleParameter(":REMARKS", OracleDbType.NVarchar2,
                                cre.Properties["Remarks"], ParameterDirection.Input),
                            new OracleParameter(":ACCOUNTID", OracleDbType.Int32,
                                sUserInfo.AccountID, ParameterDirection.Input),
                            new OracleParameter(":USERID", OracleDbType.Int32,
                                sUserInfo.UserID, ParameterDirection.Input),
                        };
                        retrunRow += con.ExecuteNonQuery(sql, paras);

                        sql = "UPDATE tp_pc_mould m\n" +
                        "   SET m.premouldstatus      = m.mouldstatus\n" +
                        "      ,m.mouldstatus         = :mouldstatus\n" +
                        "      ,m.updateuserid        = :userid\n" +
                        "      ,m.lastchangehistoryid = :changeid\n" +
                        " WHERE m.mouldid = :mouldid";

                        paras = new OracleParameter[]
                        {
                            new OracleParameter(":mouldstatus", OracleDbType.Int32,
                                (int)Constant.MouldStatus.Out, ParameterDirection.Input),
                            new OracleParameter(":changeid", OracleDbType.Int32,
                                mouldHistoryID, ParameterDirection.Input),
                            new OracleParameter(":userid", OracleDbType.Int32,
                                sUserInfo.UserID, ParameterDirection.Input),
                            new OracleParameter(":mouldid", OracleDbType.Int32,
                                item["MouldID"], ParameterDirection.Input),
                        };
                        retrunRow += con.ExecuteNonQuery(sql, paras);
                    }
                    #endregion
                }
                // 模具回收
                else if (mouldOperationType == (int)Constant.MouldOperationType.OutToInv)
                {
                    #region
                    foreach (DataRow item in mouldTable.Rows)
                    {
                        int mouldStatus = Convert.ToInt32(item["MouldStatus"]);
                        if (mouldStatus != 3)
                        {
                            continue;
                        }

                        int mouldHistoryID = Convert.ToInt32(con.GetSqlResultToObj("select seq_pc_mouldhistory_id.nextval from dual "));

                        sql =
                        "INSERT INTO tp_pc_mouldchangehistory\n" +
                        "  (CHANGEID\n" +
                        "  ,MOULDID\n" +
                        "  ,MOULDCODE\n" +
                        "  ,MOULDBARCODE\n" +
                        "  ,GOODSID\n" +
                        "  ,GOODSCODE\n" +
                        "  ,OPERATIONTYPE\n" +
                        "  ,MOULDSTATUS\n" +
                        "  ,MOULDSTATUSAFTER\n" +
                        "  ,GROUTINGNUM\n" +
                        "  ,REMARKS\n" +
                        "  ,CHANGEFLAG\n" +
                        "  ,ACCOUNTID\n" +
                        "  ,CREATEUSERID)\n" +
                        "  Values( :changeID\n" +
                        "        ,:MOULDID\n" +
                        "        ,:MOULDCODE\n" +
                        "        ,:MOULDBARCODE\n" +
                        "        ,:GOODSID\n" +
                        "        ,:GOODSCODE\n" +
                        "        ,:OPERATIONTYPE\n" +
                        "        ,:MOULDSTATUS\n" +
                        "        ,:MOULDSTATUSAFTER\n" +
                        "        ,:GROUTINGNUM\n" +
                        "        ,:REMARKS\n" +
                        "        ,'1'\n" +
                        "        ,:ACCOUNTID\n" +
                        "        ,:USERID\n" +
                        ")";
                        paras = new OracleParameter[]
                        {
                            new OracleParameter(":changeID", OracleDbType.Int32,
                                mouldHistoryID, ParameterDirection.Input),
                            new OracleParameter(":MOULDID", OracleDbType.Int32,
                                item["MouldID"], ParameterDirection.Input),
                            new OracleParameter(":MOULDCODE", OracleDbType.Varchar2,
                                item["MOULDCODE"], ParameterDirection.Input),
                            new OracleParameter(":MOULDBARCODE", OracleDbType.NVarchar2,
                                item["MOULDBARCODE"], ParameterDirection.Input),
                            new OracleParameter(":GOODSID", OracleDbType.Int32,
                                item["GOODSID"], ParameterDirection.Input),
                            new OracleParameter(":GOODSCODE", OracleDbType.NVarchar2,
                                item["GOODSCODE"], ParameterDirection.Input),
                            new OracleParameter(":OPERATIONTYPE", OracleDbType.Int32,
                                mouldOperationType, ParameterDirection.Input),
                            new OracleParameter(":MOULDSTATUSAFTER", OracleDbType.Int32,
                                (int)Constant.MouldStatus.Inv, ParameterDirection.Input),
                            new OracleParameter(":MOULDSTATUS", OracleDbType.Int32,
                                item["MOULDSTATUS"], ParameterDirection.Input),
                            new OracleParameter(":GROUTINGNUM", OracleDbType.Int32,
                                item["GROUTINGNUM"], ParameterDirection.Input),
                            new OracleParameter(":REMARKS", OracleDbType.NVarchar2,
                                cre.Properties["Remarks"], ParameterDirection.Input),
                            new OracleParameter(":ACCOUNTID", OracleDbType.Int32,
                                sUserInfo.AccountID, ParameterDirection.Input),
                            new OracleParameter(":USERID", OracleDbType.Int32,
                                sUserInfo.UserID, ParameterDirection.Input),
                        };
                        retrunRow += con.ExecuteNonQuery(sql, paras);

                        sql = "UPDATE tp_pc_mould m\n" +
                        "   SET m.premouldstatus      = m.mouldstatus\n" +
                        "      ,m.mouldstatus         = :mouldstatus\n" +
                        "      ,m.updateuserid        = :userid\n" +
                        "      ,m.lastchangehistoryid = :changeid\n" +
                        " WHERE m.mouldid = :mouldid";

                        paras = new OracleParameter[]
                        {
                            new OracleParameter(":mouldstatus", OracleDbType.Int32,
                                (int)Constant.MouldStatus.Inv, ParameterDirection.Input),
                            new OracleParameter(":changeid", OracleDbType.Int32,
                                mouldHistoryID, ParameterDirection.Input),
                            new OracleParameter(":userid", OracleDbType.Int32,
                                sUserInfo.UserID, ParameterDirection.Input),
                            new OracleParameter(":mouldid", OracleDbType.Int32,
                                item["MouldID"], ParameterDirection.Input),
                        };
                        retrunRow += con.ExecuteNonQuery(sql, paras);
                    }
                    #endregion
                }
                // 报废撤销
                else if (mouldOperationType == (int)Constant.MouldOperationType.ScrapToInv)
                {
                    #region
                    foreach (DataRow item in mouldTable.Rows)
                    {
                        int mouldStatus = Convert.ToInt32(item["MouldStatus"]);
                        if (mouldStatus != 4)
                        {
                            continue;
                        }

                        int mouldHistoryID = Convert.ToInt32(con.GetSqlResultToObj("select seq_pc_mouldhistory_id.nextval from dual "));

                        sql =
                        "INSERT INTO tp_pc_mouldchangehistory\n" +
                        "  (CHANGEID\n" +
                        "  ,MOULDID\n" +
                        "  ,MOULDCODE\n" +
                        "  ,MOULDBARCODE\n" +
                        "  ,GOODSID\n" +
                        "  ,GOODSCODE\n" +
                        "  ,OPERATIONTYPE\n" +
                        "  ,MOULDSTATUS\n" +
                        "  ,MOULDSTATUSAFTER\n" +
                        "  ,GROUTINGNUM\n" +
                        "  ,REMARKS\n" +
                        "  ,CHANGEFLAG\n" +
                        "  ,ACCOUNTID\n" +
                        "  ,CREATEUSERID)\n" +
                        "  Values( :changeID\n" +
                        "        ,:MOULDID\n" +
                        "        ,:MOULDCODE\n" +
                        "        ,:MOULDBARCODE\n" +
                        "        ,:GOODSID\n" +
                        "        ,:GOODSCODE\n" +
                        "        ,:OPERATIONTYPE\n" +
                        "        ,:MOULDSTATUS\n" +
                        "        ,:MOULDSTATUSAFTER\n" +
                        "        ,:GROUTINGNUM\n" +
                        "        ,:REMARKS\n" +
                        "        ,'1'\n" +
                        "        ,:ACCOUNTID\n" +
                        "        ,:USERID\n" +
                        ")";
                        paras = new OracleParameter[]
                        {
                            new OracleParameter(":changeID", OracleDbType.Int32,
                                mouldHistoryID, ParameterDirection.Input),
                            new OracleParameter(":MOULDID", OracleDbType.Int32,
                                item["MouldID"], ParameterDirection.Input),
                            new OracleParameter(":MOULDCODE", OracleDbType.Varchar2,
                                item["MOULDCODE"], ParameterDirection.Input),
                            new OracleParameter(":MOULDBARCODE", OracleDbType.NVarchar2,
                                item["MOULDBARCODE"], ParameterDirection.Input),
                            new OracleParameter(":GOODSID", OracleDbType.Int32,
                                item["GOODSID"], ParameterDirection.Input),
                            new OracleParameter(":GOODSCODE", OracleDbType.NVarchar2,
                                item["GOODSCODE"], ParameterDirection.Input),
                            new OracleParameter(":OPERATIONTYPE", OracleDbType.Int32,
                                mouldOperationType, ParameterDirection.Input),
                            new OracleParameter(":MOULDSTATUSAFTER", OracleDbType.Int32,
                                (int)Constant.MouldStatus.Inv, ParameterDirection.Input),
                            new OracleParameter(":MOULDSTATUS", OracleDbType.Int32,
                                item["MOULDSTATUS"], ParameterDirection.Input),
                            new OracleParameter(":GROUTINGNUM", OracleDbType.Int32,
                                item["GROUTINGNUM"], ParameterDirection.Input),
                            new OracleParameter(":REMARKS", OracleDbType.NVarchar2,
                                cre.Properties["Remarks"], ParameterDirection.Input),
                            new OracleParameter(":ACCOUNTID", OracleDbType.Int32,
                                sUserInfo.AccountID, ParameterDirection.Input),
                            new OracleParameter(":USERID", OracleDbType.Int32,
                                sUserInfo.UserID, ParameterDirection.Input),
                        };
                        retrunRow += con.ExecuteNonQuery(sql, paras);

                        sql = "UPDATE tp_pc_mould m\n" +
                        "   SET m.premouldstatus      = m.mouldstatus\n" +
                        "      ,m.mouldstatus         = :mouldstatus\n" +
                        "      ,m.updateuserid        = :userid\n" +
                        "      ,m.lastchangehistoryid = :changeid\n" +
                        "      ,m.ScrapDate = null\n" +
                        "      ,m.ScrapUserID = null\n" +
                        "      ,m.ScrapUserCode = null\n" +
                        "      ,m.ScrapReason = null\n" +
                        "      ,m.ScrapResponsibility = null\n" +
                        " WHERE m.mouldid = :mouldid";

                        paras = new OracleParameter[]
                        {
                            new OracleParameter(":mouldstatus", OracleDbType.Int32,
                                (int)Constant.MouldStatus.Inv, ParameterDirection.Input),
                            new OracleParameter(":changeid", OracleDbType.Int32,
                                mouldHistoryID, ParameterDirection.Input),
                            new OracleParameter(":userid", OracleDbType.Int32,
                                sUserInfo.UserID, ParameterDirection.Input),
                            new OracleParameter(":mouldid", OracleDbType.Int32,
                                item["MouldID"], ParameterDirection.Input),
                        };
                        retrunRow += con.ExecuteNonQuery(sql, paras);
                    }
                    #endregion
                }
                // 报废
                else if (mouldOperationType == (int)Constant.MouldOperationType.InvToScrap)
                {
                    #region
                    foreach (DataRow item in mouldTable.Rows)
                    {
                        int mouldStatus = Convert.ToInt32(item["MouldStatus"]);
                        if (mouldStatus != 1 && mouldStatus != 3)
                        {
                            continue;
                        }

                        int mouldHistoryID = Convert.ToInt32(con.GetSqlResultToObj("select seq_pc_mouldhistory_id.nextval from dual "));

                        sql =
                        "INSERT INTO tp_pc_mouldchangehistory\n" +
                        "  (CHANGEID\n" +
                        "  ,MOULDID\n" +
                        "  ,MOULDCODE\n" +
                        "  ,MOULDBARCODE\n" +
                        "  ,GOODSID\n" +
                        "  ,GOODSCODE\n" +
                        "  ,OPERATIONTYPE\n" +
                        "  ,MOULDSTATUS\n" +
                        "  ,MOULDSTATUSAFTER\n" +
                        "  ,GROUTINGNUM\n" +
                        "  ,REMARKS\n" +
                        "  ,CHANGEFLAG\n" +
                        "  ,ScrapReason\n" +
                        "  ,ScrapResponsibility\n" +
                        "  ,ACCOUNTID\n" +
                        "  ,CREATEUSERID)\n" +
                        "  Values( :changeID\n" +
                        "        ,:MOULDID\n" +
                        "        ,:MOULDCODE\n" +
                        "        ,:MOULDBARCODE\n" +
                        "        ,:GOODSID\n" +
                        "        ,:GOODSCODE\n" +
                        "        ,:OPERATIONTYPE\n" +
                        "        ,:MOULDSTATUS\n" +
                        "        ,:MOULDSTATUSAFTER\n" +
                        "        ,:GROUTINGNUM\n" +
                        "        ,:REMARKS\n" +
                        "        ,:CHANGEFLAG\n" +
                        "        ,:ScrapReason\n" +
                        "        ,:ScrapResponsibility\n" +
                        "        ,:ACCOUNTID\n" +
                        "        ,:USERID\n" +
                        ")";
                        paras = new OracleParameter[]
                        {
                            new OracleParameter(":changeID", OracleDbType.Int32,
                                mouldHistoryID, ParameterDirection.Input),
                            new OracleParameter(":MOULDID", OracleDbType.Int32,
                                item["MouldID"], ParameterDirection.Input),
                            new OracleParameter(":MOULDCODE", OracleDbType.Varchar2,
                                item["MOULDCODE"], ParameterDirection.Input),
                            new OracleParameter(":MOULDBARCODE", OracleDbType.NVarchar2,
                                item["MOULDBARCODE"], ParameterDirection.Input),
                            new OracleParameter(":GOODSID", OracleDbType.Int32,
                                item["GOODSID"], ParameterDirection.Input),
                            new OracleParameter(":GOODSCODE", OracleDbType.NVarchar2,
                                item["GOODSCODE"], ParameterDirection.Input),
                            new OracleParameter(":OPERATIONTYPE", OracleDbType.Int32,
                                (int)(mouldStatus == 1 ? Constant.MouldOperationType.InvToScrap : Constant.MouldOperationType.OutToScrap)
                                , ParameterDirection.Input),
                            new OracleParameter(":CHANGEFLAG", OracleDbType.Char,
                                (mouldStatus == 1 ? "2" : "0")
                                , ParameterDirection.Input),
                            new OracleParameter(":MOULDSTATUSAFTER", OracleDbType.Int32,
                                (int)Constant.MouldStatus.Scrap, ParameterDirection.Input),
                            new OracleParameter(":MOULDSTATUS", OracleDbType.Int32,
                                item["MOULDSTATUS"], ParameterDirection.Input),
                            new OracleParameter(":GROUTINGNUM", OracleDbType.Int32,
                                item["GROUTINGNUM"], ParameterDirection.Input),
                            new OracleParameter(":REMARKS", OracleDbType.NVarchar2,
                                cre.Properties["Remarks"], ParameterDirection.Input),
                            new OracleParameter(":ACCOUNTID", OracleDbType.Int32,
                                sUserInfo.AccountID, ParameterDirection.Input),
                            new OracleParameter(":USERID", OracleDbType.Int32,
                                sUserInfo.UserID, ParameterDirection.Input),
                            new OracleParameter(":ScrapReason", OracleDbType.Int32,
                                cre.Properties["ScrapReason"], ParameterDirection.Input),
                            new OracleParameter(":ScrapResponsibility", OracleDbType.NVarchar2,
                                cre.Properties["ScrapResponsibility"], ParameterDirection.Input),
                        };
                        retrunRow += con.ExecuteNonQuery(sql, paras);

                        sql = "UPDATE tp_pc_mould m\n" +
                        "   SET m.premouldstatus      = m.mouldstatus\n" +
                        "      ,m.mouldstatus         = :mouldstatus\n" +
                        "      ,m.updateuserid        = :userid\n" +
                        "      ,m.lastchangehistoryid = :changeid\n" +
                        "      ,m.scrapdate           = SYSDATE\n" +
                        "      ,m.scrapuserid         = :scrapuserid\n" +
                        "      ,m.scrapusercode       = :scrapusercode\n" +
                        "      ,m.ScrapReason = :ScrapReason\n" +
                        "      ,m.ScrapResponsibility = :ScrapResponsibility\n" +
                        " WHERE m.mouldid = :mouldid";

                        paras = new OracleParameter[]
                        {
                            new OracleParameter(":mouldstatus", OracleDbType.Int32,
                                (int)Constant.MouldStatus.Scrap, ParameterDirection.Input),
                            new OracleParameter(":changeid", OracleDbType.Int32,
                                mouldHistoryID, ParameterDirection.Input),
                            new OracleParameter(":userid", OracleDbType.Int32,
                                sUserInfo.UserID, ParameterDirection.Input),
                            new OracleParameter(":mouldid", OracleDbType.Int32,
                                item["MouldID"], ParameterDirection.Input),
                            new OracleParameter(":scrapuserid", OracleDbType.Int32,
                                sUserInfo.UserID, ParameterDirection.Input),
                            new OracleParameter(":scrapusercode", OracleDbType.NVarchar2,
                                sUserInfo.UserCode, ParameterDirection.Input),
                            new OracleParameter(":ScrapReason", OracleDbType.Int32,
                                cre.Properties["ScrapReason"], ParameterDirection.Input),
                            new OracleParameter(":ScrapResponsibility", OracleDbType.NVarchar2,
                                cre.Properties["ScrapResponsibility"], ParameterDirection.Input),
                        };
                        retrunRow += con.ExecuteNonQuery(sql, paras);
                    }
                    #endregion
                }
                // 撤销报工
                else if (mouldOperationType == (int)Constant.MouldOperationType.UnMouldPlan)
                {
                    //校验是否存在未报工模具
                    DataRow[] mouldPlanDt = mouldTable.Select(" mould_plan_id IS NULL", null);
                    if (mouldPlanDt.Length > 0)
                    {
                        sre.Message = "存在未报工模具！";
                        sre.Status = Constant.ServiceResultStatus.Other;
                        return sre;
                    }
                    foreach (DataRow item in mouldTable.Rows)
                    {
                        string sqlString = "SELECT mp.plan_qty - mp.make_qty AS incomplete,mp.PLAN_CODE\n" +
                       "  FROM t_mould_plan mp\n" +
                       " WHERE mp.plan_id = :mould_plan_id";
                        OracleParameter[] paras1 = new OracleParameter[]
                        {
                            new OracleParameter(":mould_plan_id", OracleDbType.Varchar2,
                                item["mould_plan_id"], ParameterDirection.Input),
                        };
                        DataTable dt = con.GetSqlResultToDt(sqlString, paras1);
                        string plan_code = dt.Rows[0]["PLAN_CODE"].ToString();
                        // 撤销报工（清空模具关联模具生产计划id）
                        sqlString = "UPDATE tp_pc_mould\n" +
                       "   SET mould_plan_id = NULL\n" +
                       "   ,updateuserid =:userid\n" +
                       " WHERE mouldid = :mouldid";

                        paras = new OracleParameter[]
                       {
                            new OracleParameter(":userid", OracleDbType.Int32,
                                sUserInfo.UserID, ParameterDirection.Input),
                            new OracleParameter(":mouldid", OracleDbType.Int32,
                                item["MouldID"], ParameterDirection.Input),
                       };
                        retrunRow += con.ExecuteNonQuery(sqlString, paras);
                        // 报工计划 完成数量-1
                        sqlString = "UPDATE t_mould_plan\n" +
                       "   SET make_qty = make_qty - 1\n" +
                        "   ,updateuserid =:userid\n" +
                       " WHERE plan_id = :plan_id";
                        paras = new OracleParameter[]
                      {
                            new OracleParameter(":userid", OracleDbType.Int32,
                                sUserInfo.UserID, ParameterDirection.Input),
                            new OracleParameter(":plan_id", OracleDbType.Int32,
                                item["mould_plan_id"], ParameterDirection.Input),
                      };
                        retrunRow += con.ExecuteNonQuery(sqlString, paras);
                        //报工计划履历
                        sqlString = "INSERT INTO t_mould_plan_report_his\n" +
                        "    (his_type\n" +
                        "    ,plan_id\n" +
                        "    ,plan_code\n" +
                        "    ,mouldid\n" +
                        "    ,goodsid\n" +
                        "    ,materialsupplier\n" +
                        "    ,remarks\n" +
                            "    ,CREATEUSER\n" +
                        "    ,accountid)\n" +
                        "VALUES\n" +
                        "    (-1\n" +
                        "    ,:plan_id\n" +
                        "    ,:plan_code\n" +
                        "    ,:mouldid\n" +
                        "    ,:goodsid\n" +
                        "    ,:materialsupplier\n" +
                        "    ,:remarks\n" +
                            "    ,:CREATEUSER\n" +
                        "    ,:accountid)";
                        paras = new OracleParameter[]
                        {
                                 new OracleParameter(":plan_id", OracleDbType.Int32,
                                item["MOULD_PLAN_ID"], ParameterDirection.Input),// 模具生产计划id
                                 new OracleParameter(":plan_code", OracleDbType.Varchar2,
                                plan_code ,ParameterDirection.Input),
                                 new OracleParameter(":mouldid", OracleDbType.Int32,
                                item["MouldID"], ParameterDirection.Input),
                                 new OracleParameter(":goodsid", OracleDbType.Int32,
                                item["GoodsID"] ,ParameterDirection.Input),
                                 new OracleParameter(":materialsupplier", OracleDbType.Int32,
                               item["MaterialSupplier"], ParameterDirection.Input),
                                 new OracleParameter(":accountid", OracleDbType.Int32,
                                sUserInfo.AccountID, ParameterDirection.Input),
                                 new OracleParameter(":remarks", OracleDbType.Varchar2,
                                cre.Properties["Remarks"] ,ParameterDirection.Input),
                                 new OracleParameter(":CREATEUSER", OracleDbType.Int32,
                                sUserInfo.UserID, ParameterDirection.Input),
                        };
                        retrunRow += con.ExecuteNonQuery(sqlString, paras);
                    }
                }
                // 生产报工
                else if (mouldOperationType == (int)Constant.MouldOperationType.MouldPlan)
                {
                    int mould_plan_id = Convert.ToInt32(cre.Properties["mould_plan_id"]);
                    //校验是否存在已报工模具
                    DataRow[] mouldPlanDt = mouldTable.Select(" mould_plan_id IS not NULL", null);
                    if (mouldPlanDt.Length > 0)
                    {
                        sre.Message = "存在已报工模具！";
                        sre.Status = Constant.ServiceResultStatus.Other;
                        return sre;
                    }
                    DataView dataView = new DataView(mouldTable);
                    DataTable dts = dataView.ToTable(true, "GoodsID");
                    if (dts.Rows.Count > 1)
                    {
                        sre.Message = "存在不同的产品型号！";
                        sre.Status = Constant.ServiceResultStatus.Other;
                        return sre;
                    }

                    string sqlString = "SELECT mp.plan_qty - mp.make_qty AS incomplete,mp.PLAN_CODE\n" +
                       "  FROM t_mould_plan mp\n" +
                       " WHERE mp.plan_id = :mould_plan_id";
                    OracleParameter[] paras1 = new OracleParameter[]
                    {
                            new OracleParameter(":mould_plan_id", OracleDbType.Varchar2,
                                mould_plan_id, ParameterDirection.Input),
                    };

                    DataTable dt = con.GetSqlResultToDt(sqlString, paras1);
                    //生产计划未完成数量
                    int incomplete = Convert.ToInt32(dt.Rows[0]["incomplete"]);
                    if (incomplete < mouldTable.Rows.Count)
                    {
                        sre.Message = "报工数量大于生产计划未完成数量！";
                        sre.Status = Constant.ServiceResultStatus.Other;
                        return sre;
                    }
                    string plan_code = dt.Rows[0]["PLAN_CODE"].ToString();
                    foreach (DataRow item in mouldTable.Rows)
                    {

                        // 生产报工（清空模具关联模具生产计划id）
                        sqlString = "UPDATE tp_pc_mould\n" +
                       "   SET mould_plan_id = :mould_plan_id\n" +
                       "   ,updateuserid =:userid\n" +
                       " WHERE mouldid = :mouldid";

                        paras = new OracleParameter[]
                       {
                           new OracleParameter(":mould_plan_id", OracleDbType.Varchar2,
                                mould_plan_id, ParameterDirection.Input),
                            new OracleParameter(":userid", OracleDbType.Int32,
                                sUserInfo.UserID, ParameterDirection.Input),
                            new OracleParameter(":mouldid", OracleDbType.Int32,
                                item["MouldID"], ParameterDirection.Input),
                       };
                        retrunRow += con.ExecuteNonQuery(sqlString, paras);
                        // 报工计划 完成数量+1
                        sqlString = "UPDATE t_mould_plan\n" +
                       "   SET make_qty = make_qty + 1\n" +
                        "   ,updateuserid =:userid\n" +
                       " WHERE plan_id = :plan_id";
                        paras = new OracleParameter[]
                      {
                            new OracleParameter(":userid", OracleDbType.Int32,
                                sUserInfo.UserID, ParameterDirection.Input),
                            new OracleParameter(":plan_id", OracleDbType.Int32,
                                mould_plan_id, ParameterDirection.Input),
                      };
                        retrunRow += con.ExecuteNonQuery(sqlString, paras);
                        //报工计划履历
                        sqlString = "INSERT INTO t_mould_plan_report_his\n" +
                        "    (his_type\n" +
                        "    ,plan_id\n" +
                        "    ,plan_code\n" +
                        "    ,mouldid\n" +
                        "    ,goodsid\n" +
                        "    ,materialsupplier\n" +
                        "    ,remarks\n" +
                            "    ,CREATEUSER\n" +
                        "    ,accountid)\n" +
                        "VALUES\n" +
                        "    (1\n" +
                        "    ,:plan_id\n" +
                        "    ,:plan_code\n" +
                        "    ,:mouldid\n" +
                        "    ,:goodsid\n" +
                        "    ,:materialsupplier\n" +
                        "    ,:remarks\n" +
                            "    ,:CREATEUSER\n" +
                        "    ,:accountid)";
                        paras = new OracleParameter[]
                        {
                                 new OracleParameter(":plan_id", OracleDbType.Int32,
                                mould_plan_id, ParameterDirection.Input),// 模具生产计划id
                                 new OracleParameter(":plan_code", OracleDbType.Varchar2,
                                plan_code ,ParameterDirection.Input),
                                 new OracleParameter(":mouldid", OracleDbType.Int32,
                                item["MouldID"], ParameterDirection.Input),
                                 new OracleParameter(":goodsid", OracleDbType.Int32,
                                item["GoodsID"] ,ParameterDirection.Input),
                                 new OracleParameter(":materialsupplier", OracleDbType.Int32,
                               item["MaterialSupplier"], ParameterDirection.Input),
                                 new OracleParameter(":accountid", OracleDbType.Int32,
                                sUserInfo.AccountID, ParameterDirection.Input),
                                 new OracleParameter(":remarks", OracleDbType.Varchar2,
                                cre.Properties["Remarks"] ,ParameterDirection.Input),
                                 new OracleParameter(":CREATEUSER", OracleDbType.Int32,
                                sUserInfo.UserID, ParameterDirection.Input),
                        };
                        retrunRow += con.ExecuteNonQuery(sqlString, paras);
                    }

                }

                con.Commit();
                return sre;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (con.ConnState == ConnectionState.Open)
                {
                    con.Disconnect();
                }
            }
        }

        /// <summary>
        /// 保存试验标识
        /// </summary>
        /// <param name="sUserInfo"></param>
        /// <returns></returns>
        public static ServiceResultEntity SaveTestMouldFlag(SUserInfo sUserInfo, ClientRequestEntity cre)
        {
            ServiceResultEntity sre = new ServiceResultEntity();
            IDBTransaction con = ClsDbFactory.CreateDBTransaction(DataBaseType.ORACLE, DataManager.ConnectionString);
            try
            {
                string testMouldFlag = cre.Properties["TestMouldFlag"] + "";
                con.Connect();
                OracleParameter[] paras = null;

                string sql = @"
                SELECT DISTINCT G.GROUTINGLINEID
                               ,G.GROUTINGLINECODE
                               ,G.TESTFLAG
                  FROM TP_PC_GROUTINGLINE G
                 INNER JOIN TP_PC_GROUTINGLINEDETAIL GD
                    ON GD.GROUTINGLINEID = G.GROUTINGLINEID
                 WHERE INSTR(:MOULDIDS, ',' || GD.MOULDID || ',') > 0 ";

                paras = new OracleParameter[]
                {
                    new OracleParameter(":MOULDIDS", OracleDbType.Varchar2, cre.Properties["MouldIDs"], ParameterDirection.Input),
                };

                DataTable dt = con.GetSqlResultToDt(sql, paras);

                foreach (DataRow row in dt.Rows)
                {
                    // 如果要改成‘0’，但成型线整线试验，则不允许改
                    if (testMouldFlag.Equals("0") && (row["TESTFLAG"] + "").Equals("1"))
                    {
                        sre.OtherStatus = -1;
                        sre.Message = row["GROUTINGLINECODE"] + "是试验成型线，不能改为非试验模具。";
                        return sre;
                    }
                }

                sql = @"
                UPDATE TP_PC_MOULD M
                   SET M.TESTMOULDFLAG = :TESTMOULDFLAG,
                       M.UPDATEUSERID  = :USERID
                 WHERE INSTR(:MOULDIDS, ',' || M.MOULDID || ',') > 0 ";

                paras = new OracleParameter[]
                {
                    new OracleParameter(":TESTMOULDFLAG", OracleDbType.Char, testMouldFlag, ParameterDirection.Input),
                    new OracleParameter(":USERID", OracleDbType.Int32, sUserInfo.UserID, ParameterDirection.Input),
                    new OracleParameter(":MOULDIDS", OracleDbType.Varchar2, cre.Properties["MouldIDs"], ParameterDirection.Input),
                };
                sre.OtherStatus += con.ExecuteNonQuery(sql, paras);

                sql = @"
                INSERT INTO TP_PC_MOULDHISTORY
                    (MOULDID,
                     TESTMOULDFLAG,
                     ACCOUNTID,
                     CREATEUSERID)
                VALUES
                    (:MOULDID,
                     :TESTMOULDFLAG,
                     :ACCOUNTID,
                     :USERID) ";

                string[] mouldids = (cre.Properties["MouldIDs"] + "").Split(',');
                foreach (string mouldid in mouldids)
                {
                    if (!string.IsNullOrEmpty(mouldid))
                    {
                        paras = new OracleParameter[]
                        {
                            new OracleParameter(":MOULDID", OracleDbType.Varchar2, mouldid, ParameterDirection.Input),
                            new OracleParameter(":TESTMOULDFLAG", OracleDbType.Char, testMouldFlag, ParameterDirection.Input),
                            new OracleParameter(":ACCOUNTID", OracleDbType.Int32, sUserInfo.AccountID, ParameterDirection.Input),
                            new OracleParameter(":USERID", OracleDbType.Int32, sUserInfo.UserID, ParameterDirection.Input),
                        };
                        sre.OtherStatus += con.ExecuteNonQuery(sql, paras);
                    }
                }

                foreach (DataRow row in dt.Rows)
                {
                    sql = @"
                    SELECT 1
                      FROM TP_PC_GROUTINGLINE G
                     INNER JOIN TP_PC_GROUTINGLINEDETAIL GD
                        ON GD.GROUTINGLINEID = G.GROUTINGLINEID
                     INNER JOIN TP_PC_MOULD M
                        ON M.MOULDID = GD.MOULDID
                     WHERE M.TESTMOULDFLAG = '0'
                       AND G.GROUTINGLINEID = :GROUTINGLINEID ";

                    paras = new OracleParameter[]
                    {
                        new OracleParameter(":GROUTINGLINEID", OracleDbType.Int32, row["GROUTINGLINEID"], ParameterDirection.Input),
                    };

                    object isExists = con.GetSqlResultToObj(sql, paras);
                    if (isExists == null)
                    {
                        sql = @"
                        UPDATE TP_PC_GROUTINGLINE
                           SET TESTFLAG = '1'
                         WHERE GROUTINGLINEID = :GROUTINGLINEID ";

                        paras = new OracleParameter[]
                        {
                            new OracleParameter(":GROUTINGLINEID", OracleDbType.Int32, row["GROUTINGLINEID"], ParameterDirection.Input),
                        };
                        sre.OtherStatus += con.ExecuteNonQuery(sql, paras);
                    }
                }

                con.Commit();
                return sre;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (con.ConnState == ConnectionState.Open)
                {
                    con.Disconnect();
                }
            }
        }

        /// <summary>
        /// sap接口-模具型号变更-转模具料号接口 ZPPFM050
        /// </summary>
        /// <param name="sUserInfo"></param>
        /// <param name="cre"></param>
        /// <returns></returns>
        public static void ZPPFM050(string BUDAT, DataTable dtTable, out string ZTYPE, out string ZMSG)
        {
            RfcConfigParameters rfcPara = new RfcConfigParameters();
            rfcPara.Add(RfcConfigParameters.AppServerHost, appServerHost);
            rfcPara.Add(RfcConfigParameters.SystemNumber, systemNumber);
            rfcPara.Add(RfcConfigParameters.User, user);
            rfcPara.Add(RfcConfigParameters.Password, password);
            rfcPara.Add(RfcConfigParameters.Client, client);

            rfcPara.Add(RfcConfigParameters.Name, "CON");
            rfcPara.Add(RfcConfigParameters.Language, "ZH");
            rfcPara.Add(RfcConfigParameters.PoolSize, "5");
            rfcPara.Add(RfcConfigParameters.ConnectionIdleTimeout, "60");

            RfcDestination rfcDest = RfcDestinationManager.GetDestination(rfcPara);
            RfcRepository rfcRep = rfcDest.Repository;

            //接口API
            IRfcFunction rfcApi = rfcRep.CreateFunction("ZPPFM050");

            //WERKS   CHAR    4   0   工厂             Y   5000
            //MATNR_B CHAR    40  0   物料编号_转换前  Y   211860087N2000
            //MATNR_A CHAR    40  0   物料编号_转换后  Y   211860088N2000
            //MENGE   QUAN    13  3   数量             Y   50
            //MEINS   UNIT    3   0   基本计量单位     Y   CI
            //BUDAT   DATS    8   0   凭证中的过帐日期 Y   格式: yyyymmdd
            //LGORT   CHAR    4   0   库存地点         Y   2110、2210、2310
            //输入参数
            IRfcStructure imTable = rfcApi.GetStructure("IS_ITAB");
         
            foreach (DataRow dr in dtTable.Rows)
            {
                imTable.SetValue("WERKS", "5000");
                imTable.SetValue("MATNR_B", dr["变更前"]);
                imTable.SetValue("MATNR_A", dr["变更后"]);
                imTable.SetValue("MENGE", dr["剩余注浆次数"]);
                imTable.SetValue("MEINS", "CI");
                imTable.SetValue("BUDAT", BUDAT);
                imTable.SetValue("LGORT", dr["库存地点"]);
            }

            //调用接口
            rfcApi.Invoke(rfcDest);

            //获取输出
            ZTYPE = rfcApi.GetValue("ZTYPE").ToString();
            ZMSG = rfcApi.GetValue("ZMSG").ToString();;

            rfcDest = null;
            rfcRep = null;
        }

        /// <summary>
        /// IRfcTable转DataTable
        /// </summary>
        /// <param name="myrfcTable"></param>
        /// <returns></returns>
        private static DataTable GetDataTableFromRFCTable(IRfcTable myrfcTable)
        {

            DataTable loTable = new DataTable();
            int liElement = 0;
            for (liElement = 0; liElement <= myrfcTable.ElementCount - 1; liElement++)
            {
                RfcElementMetadata metadata = myrfcTable.GetElementMetadata(liElement);
                loTable.Columns.Add(metadata.Name);
            }

            foreach (IRfcStructure Row in myrfcTable)
            {
                DataRow ldr = loTable.NewRow();
                for (liElement = 0; liElement <= myrfcTable.ElementCount - 1; liElement++)
                {
                    RfcElementMetadata metadata = myrfcTable.GetElementMetadata(liElement);
                    ldr[metadata.Name] = Row.GetString(metadata.Name);
                }
                loTable.Rows.Add(ldr);
            }
            return loTable;
        }
        #endregion

        #region 模具盘点

        /// <summary>
        /// 获取盘点单列表（searchbox用）
        /// </summary>
        /// <param name="sUserInfo"></param>
        /// <returns></returns>
        public static ServiceResultEntity GetMouldCheckedList(SUserInfo sUserInfo, ClientRequestEntity cre)
        {
            ServiceResultEntity sre = new ServiceResultEntity();
            IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString);
            try
            {
                con.Open();
                string sqlString = @"select CheckedID inCheckedID,CheckedNo inCheckedNo,AccountDate,Remarks from TP_PC_MouldChecked where accountid=:accountid and ValueFlag=1 order by CheckedID desc";
                List<OracleParameter> parameters = new List<OracleParameter>();
                parameters.Add(new OracleParameter(":accountid", OracleDbType.Int32, sUserInfo.AccountID, ParameterDirection.Input));
                sre.Data = con.GetSqlResultToDs(sqlString, parameters.ToArray());
                return sre;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (con.ConnState == ConnectionState.Open)
                {
                    con.Close();
                }
            }
        }

        /// <summary>
        /// 获取盘点单列表（一览用）
        /// </summary>
        /// <param name="sUserInfo"></param>
        /// <returns></returns>
        public static ServiceResultEntity GetAllMouldCheckList(SUserInfo sUserInfo, ClientRequestEntity cre)
        {
            ServiceResultEntity sre = new ServiceResultEntity();
            IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString);
            try
            {
                con.Open();
                string sqlString = "SELECT gbc.checkedid\n" +
                "      ,gbc.checkedno\n" +
                "      ,gbc.checkname\n" +
                "      ,gbc.remarks\n" +
                "      ,gbc.createtime\n" +
                "      ,u.usercode      CreateUserCode\n" +
                "      ,u.username      CreateUserName\n" +
                "  FROM TP_PC_MouldChecked gbc\n" +
                "  LEFT JOIN tp_mst_user u\n" +
                "    ON u.userid = gbc.createuserid\n" +
                " WHERE gbc.valueflag = '1'\n" +
                "   AND gbc.accountid = :accountid\n" +
                "   AND (:datebegin IS NULL OR gbc.createtime >= :datebegin)\n" +
                "   AND (:dateend IS NULL OR gbc.createtime <= :dateend)\n" +
                "   AND (:checkedno IS NULL OR  instr(gbc.checkedno, :checkedno) > 0)\n" +
                "   AND (:checkname IS NULL OR   instr(gbc.checkname, :checkname) > 0)\n" +
                "   AND (:remarks IS NULL OR  instr(gbc.remarks, :remarks) > 0)" +
                " ORDER BY checkedid desc";

                OracleParameter[] parameters = new OracleParameter[] {
                    new OracleParameter(":accountid", OracleDbType.Int32, sUserInfo.AccountID, ParameterDirection.Input),
                    new OracleParameter(":datebegin", OracleDbType.Date, cre.Properties["datebegin"], ParameterDirection.Input),
                    new OracleParameter(":dateend", OracleDbType.Date, cre.Properties["dateend"], ParameterDirection.Input),
                    new OracleParameter(":checkedno", OracleDbType.NVarchar2, cre.Properties["checkedno"], ParameterDirection.Input),
                    new OracleParameter(":checkname", OracleDbType.NVarchar2, cre.Properties["checkname"], ParameterDirection.Input),
                    new OracleParameter(":remarks", OracleDbType.NVarchar2, cre.Properties["remarks"], ParameterDirection.Input),
                };
                sre.Data = con.GetSqlResultToDs(sqlString, parameters);
                return sre;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (con.ConnState == ConnectionState.Open)
                {
                    con.Close();
                }
            }
        }

        /// <summary>
        /// 获取盘点单明细（一览用）
        /// </summary>
        /// <param name="sUserInfo"></param>
        /// <returns></returns>
        public static ServiceResultEntity GetAllMouldCheckDetail(SUserInfo sUserInfo, ClientRequestEntity cre)
        {
            ServiceResultEntity sre = new ServiceResultEntity();
            int checkedID = Convert.ToInt32(cre.Properties["CheckedID"]);
            IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString);
            try
            {
                sre.Data = new DataSet();
                con.Open();
                string sqlString = "SELECT gbu.checkedid, u.userid, u.usercode, u.username\n" +
                "  FROM Tp_PC_Mouldcheckeduser gbu\n" +
                "  LEFT JOIN tp_mst_user u\n" +
                "    ON u.userid = gbu.userid\n" +
                " WHERE gbu.checkedid = :checkedid";

                OracleParameter[] parameters = new OracleParameter[] {
                    new OracleParameter(":checkedid", OracleDbType.Int32, checkedID, ParameterDirection.Input),
                };
                DataTable userTable = con.GetSqlResultToDt(sqlString, parameters);
                userTable.TableName = "UserTable";
                sre.Data.Tables.Add(userTable);

                sqlString = "SELECT 0 sel\n" +
                "      ,gbcd.CheckedID -- 盘点单ID\n" +
                "      ,gbcd.mouldBarCode -- 模具条码\n" +
                "      ,gbcd.MouldID\n" +
                "      ,mtype.dictionaryvalue    MouldTypeName\n" +
                "      ,mms.SupplierName\n" +
                "      ,gbcd.ProductionDate\n" +
                "      ,gbcd.GoodsID\n" +
                "      ,gbcd.GoodsCode\n" +
                "      ,gbcd.UserID\n" +
                "      ,gbcd.UserCode\n" +
                "      ,gbcd.Weight\n" +
                "      ,gbcd.Cost\n" +
                //"      ,gbcd.StandardGroutingNum\n" +
                "      ,m.STANDARDGROUTINGSUM StandardGroutingNum\n" +
                "      ,m.STANDARDGROUTINGSUM - m.GroutingNum notusedGroutingNum\n" +
                "      ,m.GroutingNum\n" +
                "      ,m.GroutingDateBegin\n" +
                "      ,m.GroutingDateEnd\n" +
                "      ,m.LastGroutingBatchNo\n" +
                "      ,m.MouldStatus\n" +
                //"      ,gbcd.GroutingNum\n" +
                //"      ,gbcd.GroutingDateBegin\n" +
                //"      ,gbcd.GroutingDateEnd\n" +
                //"      ,gbcd.LastGroutingBatchNo\n" +
                //"      ,gbcd.MouldStatus\n" +
                "      ,ms.mouldstatusname\n" +
                "      ,gbcd.RepairFlag\n" +
                "      ,gbcd.ScrapDate\n" +
                "      ,gbcd.ScrapUserID\n" +
                "      ,gbcd.ScrapUserCode\n" +
                "      ,ScReason.dictionaryvalue ScrapReason\n" +
                "      ,gbcd.ScrapResponsibility\n" +
                "      ,gl.groutinglinecode\n" +
                "      ,gld.groutingmouldcode\n" +
                "      ,gbcd.Remarks -- 备注\n" +
                "      ,gbcd.CheckedFlag -- 盘点标识\n" +
                "      ,gbcd.CheckedDate -- 盘点时间\n" +
                "      ,CUser.usercode   CheckedUserCode -- 盘点工号\n" +
                "      ,g.mouldmaterialcode\n" +
                "  FROM tp_pc_mouldcheckeddetail gbcd\n" +
                "  LEFT JOIN tp_mst_datadictionary mtype\n" +
                "    ON mtype.dictionaryid = gbcd.mouldtype\n" +
                "  LEFT JOIN tp_pc_mould m\n" +
                "    ON m.MouldID = gbcd.MouldID\n" +
                "  LEFT JOIN tp_mst_goods g\n" +
                "    ON g.goodsid = m.goodsid\n" +
                "  LEFT JOIN tp_mst_mouldmaterialsuppliers mms\n" +
                "    ON mms.SupplierID = gbcd.MaterialSupplier\n" +
                " INNER JOIN tp_sys_mouldstatus ms\n" +
                "    ON ms.mouldstatusid = gbcd.MouldStatus\n" +
                "  LEFT JOIN tp_mst_datadictionary ScReason\n" +
                "    ON ScReason.dictionaryid = gbcd.ScrapReason\n" +
                "  LEFT JOIN tp_pc_groutinglinedetail gld\n" +
                "    ON gld.groutinglinedetailid = gbcd.GroutingLineDetailID\n" +
                "  LEFT JOIN tp_pc_groutingline gl\n" +
                "    ON gl.groutinglineid = gld.groutinglineid\n" +
                "  LEFT JOIN tp_mst_user CUser\n" +
                "    ON CUser.userid = gbcd.CheckedUserID" +
                " WHERE gbcd.checkedid = :checkedid\n" +
                "   AND gbcd.checkedflag = :checkedflag";

                OracleParameter[] paras = new OracleParameter[]{
                    new OracleParameter(":checkedflag",OracleDbType.Int32,(int)Constant.InCheckedFlag.InCheckedNo,ParameterDirection.Input),
                    new OracleParameter(":checkedid",OracleDbType.Int32,checkedID,ParameterDirection.Input),
                };
                sre.Data.Tables.Add(con.GetSqlResultToDt(sqlString, paras));

                paras[0].Value = (int)Constant.InCheckedFlag.InCheckeded;
                sre.Data.Tables.Add(con.GetSqlResultToDt(sqlString, paras));

                paras[0].Value = (int)Constant.InCheckedFlag.InCheckedWin;
                sre.Data.Tables.Add(con.GetSqlResultToDt(sqlString, paras));

                paras[0].Value = (int)Constant.InCheckedFlag.Loss;
                sre.Data.Tables.Add(con.GetSqlResultToDt(sqlString, paras));

                return sre;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (con.ConnState == ConnectionState.Open)
                {
                    con.Close();
                }
            }
        }

        /// <summary>
        /// 获取盘点单信息（编辑用）
        /// </summary>
        /// <param name="sUserInfo"></param>
        /// <returns></returns>
        public static ServiceResultEntity GetMouldCheckedInfoForUpdate(SUserInfo sUserInfo, ClientRequestEntity cre)
        {
            ServiceResultEntity sre = new ServiceResultEntity();
            int checkedID = Convert.ToInt32(cre.Properties["CheckedID"]);
            IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString);
            try
            {
                con.Open();

                string sqlString =
                    "SELECT gbc.checkedid, gbc.checkedno, gbc.checkname, gbc.remarks\n" +
                    "  FROM TP_PC_MouldChecked gbc\n" +
                    " WHERE gbc.checkedid = :checkedid";
                OracleParameter[] parameters = new OracleParameter[] {
                    new OracleParameter(":checkedid", OracleDbType.Int32, checkedID, ParameterDirection.Input)
                };
                sre.Data = con.GetSqlResultToDs(sqlString, parameters);

                sqlString =
                    "SELECT u.userid, u.usercode, u.username\n" +
                    "  FROM Tp_PC_Mouldcheckeduser gbu\n" +
                    " INNER JOIN tp_mst_user u\n" +
                    "    ON u.userid = gbu.userid\n" +
                    " WHERE gbu.checkedid = :checkedid" +
                    " ORDER BY u.usercode";
                sre.Data.Tables.Add(con.GetSqlResultToDt(sqlString, parameters));
                return sre;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (con.ConnState == ConnectionState.Open)
                {
                    con.Close();
                }
            }
        }

        /// <summary>
        /// 保存盘点单
        /// </summary>
        /// <param name="sUserInfo">用户基本信息</param>
        /// <returns></returns>
        public static ServiceResultEntity SaveMouldChecked(SUserInfo sUserInfo, ClientRequestEntity cre)
        {
            ServiceResultEntity sre = new ServiceResultEntity();
            int checkedID = Convert.ToInt32(cre.Properties["CheckedID"]);
            string checkedName = cre.Properties["CheckedName"].ToString();
            string remarks = cre.Properties["Remarks"].ToString();

            int returnRows = 0;
            IDBTransaction oracleTrConn = ClsDbFactory.CreateDBTransaction(DataBaseType.ORACLE, DataManager.ConnectionString);
            try
            {
                string sql = null;
                OracleParameter[] paras = null;

                if (checkedID > 0)
                {
                    // 编辑
                    sql = "UPDATE TP_PC_MouldChecked gbc\n" +
                    "   SET gbc.checkname  = :checkname\n" +
                    "      ,gbc.remarks      = :remarks\n" +
                    "      ,gbc.updateuserid = :updateuserid\n" +
                    " WHERE gbc.checkedid = :checkedid";
                    paras = new OracleParameter[] {
                        new OracleParameter(":CheckedID",OracleDbType.Int32,
                            checkedID,ParameterDirection.Input),
                        new OracleParameter(":checkname",OracleDbType.NVarchar2,
                            checkedName,ParameterDirection.Input),
                        new OracleParameter(":remarks",OracleDbType.NVarchar2,
                            remarks,ParameterDirection.Input),
                        new OracleParameter(":updateuserid",OracleDbType.Int32,
                            sUserInfo.UserID,ParameterDirection.Input)
                    };
                    returnRows += oracleTrConn.ExecuteNonQuery(sql, paras);

                    sql = "DELETE FROM Tp_PC_Mouldcheckeduser gbu\n" +
                    " WHERE gbu.checkedid = :checkedid";
                    paras = new OracleParameter[] {
                        new OracleParameter(":CheckedID",OracleDbType.Int32,
                            checkedID,ParameterDirection.Input),
                    };
                    returnRows += oracleTrConn.ExecuteNonQuery(sql, paras);

                    if (cre.Data != null && cre.Data.Tables.Count > 0)
                    {
                        sql = "INSERT INTO Tp_PC_Mouldcheckeduser gbu\n" +
                        "  (checkedid, userid, usercode)\n" +
                        "VALUES\n" +
                        "  (:checkedid, :userid, :usercode)";
                        foreach (DataRow item in cre.Data.Tables[0].Rows)
                        {
                            paras = new OracleParameter[] {
                                new OracleParameter(":CheckedID",OracleDbType.Int32,
                                    checkedID,ParameterDirection.Input),
                                new OracleParameter(":userid",OracleDbType.Int32,
                                    item["UserID"],ParameterDirection.Input),
                                new OracleParameter(":usercode",OracleDbType.NVarchar2,
                                    item["UserCode"],ParameterDirection.Input),
                            };
                            returnRows += oracleTrConn.ExecuteNonQuery(sql, paras);
                        }
                    }
                }
                else
                {
                    // 新建
                    //盘点单号
                    string checkedNo = string.Format("MIC{0}", DateTime.Now.ToString("yyyyMMddHHmmss"));
                    //获得账务日期
                    DateTime accountDate = CommonModuleLogic.CommonModuleLogic.GetAccountDate(oracleTrConn, sUserInfo);
                    //盘点单ID
                    sql = "select SEQ_PC_MouldChecked_ID.nextval from dual";
                    checkedID = Convert.ToInt32(oracleTrConn.GetSqlResultToStr(sql));

                    #region 插入盘点单主表信息
                    sql = @"insert into TP_PC_MouldChecked
                                (
                                    CheckedID,
                                    CheckedNo,
                                    AccountDate,
                                    Remarks,
                                    AccountID,
                                    CreateUserID,
                                    UpdateUserID,
                                    CHECKNAME
                                ) values
                                (   :CheckedID,
                                    :CheckedNo,
                                    :AccountDate,
                                    :Remarks,
                                    :AccountID,
                                    :CreateUserID,
                                    :CreateUserID,
                                    :CHECKNAME
                                )";
                    OracleParameter[] Paras = new OracleParameter[] {
                        new OracleParameter(":CheckedID",OracleDbType.Int32,
                            checkedID,ParameterDirection.Input),
                        new OracleParameter(":CheckedNo",OracleDbType.NVarchar2,
                            checkedNo,ParameterDirection.Input),
                        new OracleParameter(":AccountDate",OracleDbType.Date,
                            accountDate,ParameterDirection.Input),
                        new OracleParameter(":Remarks",OracleDbType.NVarchar2,
                            remarks,ParameterDirection.Input),
                        new OracleParameter(":AccountID",OracleDbType.Int32,
                            sUserInfo.AccountID,ParameterDirection.Input),
                        new OracleParameter(":CreateUserID",OracleDbType.Int32,
                            sUserInfo.UserID,ParameterDirection.Input),
                        new OracleParameter(":CHECKNAME",OracleDbType.NVarchar2,
                            checkedName,ParameterDirection.Input),
                    };
                    returnRows += oracleTrConn.ExecuteNonQuery(sql, Paras);
                    #endregion

                    #region 盘点工号
                    if (cre.Data != null && cre.Data.Tables.Count > 0)
                    {
                        sql = "INSERT INTO Tp_PC_Mouldcheckeduser gbu\n" +
                        "  (checkedid, userid, usercode)\n" +
                        "VALUES\n" +
                        "  (:checkedid, :userid, :usercode)";
                        foreach (DataRow item in cre.Data.Tables[0].Rows)
                        {
                            paras = new OracleParameter[] {
                                new OracleParameter(":CheckedID",OracleDbType.Int32,
                                    checkedID,ParameterDirection.Input),
                                new OracleParameter(":userid",OracleDbType.Int32,
                                    item["UserID"],ParameterDirection.Input),
                                new OracleParameter(":usercode",OracleDbType.NVarchar2,
                                    item["UserCode"],ParameterDirection.Input),
                            };
                            returnRows += oracleTrConn.ExecuteNonQuery(sql, paras);
                        }
                    }
                    #endregion

                    #region 盘点信息

                    sql =
                    "INSERT INTO TP_PC_MouldCHECKEDDETAIL\n" +
                    "  (CHECKEDID\n" +
                    "  ,CHECKEDNO\n" +
                    "  ,MouldID\n" +
                    "  ,MouldCODE\n" +
                    "  ,MouldBARCODE\n" +
                    "  ,mouldtype\n" +
                    "  ,materialsupplier\n" +
                    "  ,productiondate\n" +
                    "  ,goodsid\n" +
                    "  ,goodscode\n" +
                    "  ,userid\n" +
                    "  ,usercode\n" +
                    "  ,weight\n" +
                    "  ,cost\n" +
                    "  ,standardgroutingnum\n" +
                    "  ,groutingnum\n" +
                    "  ,groutingdatebegin\n" +
                    "  ,groutingdateend\n" +
                    "  ,lastgroutingbatchno\n" +
                    "  ,mouldstatus\n" +
                    "  ,premouldstatus\n" +
                    "  ,repairflag\n" +
                    "  ,scrapdate\n" +
                    "  ,scrapuserid\n" +
                    "  ,scrapusercode\n" +
                    "  ,scrapreason\n" +
                    "  ,scrapresponsibility\n" +
                    "  ,lastchangehistoryid\n" +
                    "  ,groutinglinedetailid\n" +
                    "  ,REMARKS\n" +
                    "  ,ACCOUNTID\n" +
                    "  ,VALUEFLAG\n" +
                    "  ,CREATEUSERID\n" +
                    "  ,UPDATEUSERID)\n" +
                    "  SELECT :CHECKEDID\n" +
                    "        ,:CHECKEDNO\n" +
                    "        ,gdd.MouldID\n" +
                    "        ,gdd.MouldCODE\n" +
                    "        ,gdd.MouldBARCODE\n" +
                    "        ,gdd.mouldtype\n" +
                    "        ,gdd.materialsupplier\n" +
                    "        ,gdd.productiondate\n" +
                    "        ,gdd.goodsid\n" +
                    "        ,gdd.goodscode\n" +
                    "        ,gdd.userid\n" +
                    "        ,gdd.usercode\n" +
                    "        ,gdd.weight\n" +
                    "        ,gdd.cost\n" +
                    "        ,gdd.standardgroutingnum\n" +
                    "        ,gdd.groutingnum\n" +
                    "        ,gdd.groutingdatebegin\n" +
                    "        ,gdd.groutingdateend\n" +
                    "        ,gdd.lastgroutingbatchno\n" +
                    "        ,gdd.mouldstatus\n" +
                    "        ,gdd.premouldstatus\n" +
                    "        ,gdd.repairflag\n" +
                    "        ,gdd.scrapdate\n" +
                    "        ,gdd.scrapuserid\n" +
                    "        ,gdd.scrapusercode\n" +
                    "        ,gdd.scrapreason\n" +
                    "        ,gdd.scrapresponsibility\n" +
                    "        ,gdd.lastchangehistoryid\n" +
                    "        ,gdd.groutinglinedetailid\n" +
                    "        ,gdd.REMARKS\n" +
                    "        ,gdd.ACCOUNTID\n" +
                    "        ,'1'\n" +
                    "        ,:CHECKEDUSERID\n" +
                    "        ,:CHECKEDUSERID\n" +
                    " FROM tp_pC_Mould gdd\n" +
                    " LEFT JOIN tp_mst_goods g\n" +
                    "   ON g.goodsid = gdd.goodsid\n" +
                    " LEFT JOIN tp_mst_goodsType gt\n" +
                    "   ON gt.goodstypeid = g.goodstypeid\n" +
                    "WHERE gdd.valueflag = '1'\n" +
                    "  AND gdd.accountid = :accountid\n" +
                    "  AND gdd.mouldstatus in (1,2,3) \n" +
                    "  AND (:DateBegin IS NULL OR gdd.productiondate >= :DateBegin)\n" +
                    "  AND (:DateEnd IS NULL OR gdd.productiondate <= :DateEnd)\n" +
                    "  AND (:mouldstatus IS NULL OR\n" +
                    "      instr(',' || :mouldstatus || ',', ',' || gdd.mouldstatus || ',') > 0)\n" +
                    "  AND (:GoodsIDS IS NULL OR\n" +
                    "      instr(',' || :GoodsIDS || ',', ',' || gdd.GoodsID || ',') > 0)\n" +
                    "  AND (:GoodsTypeCode IS NULL OR\n" +
                    "      instr(gt.goodstypecode, :GoodsTypeCode) = 1)";

                    paras = new OracleParameter[] {
                        new OracleParameter(":accountid",OracleDbType.Int32,
                            sUserInfo.AccountID,ParameterDirection.Input),
                        new OracleParameter(":CHECKEDID",OracleDbType.Int32,
                            checkedID,ParameterDirection.Input),
                        new OracleParameter(":CHECKEDNO",OracleDbType.NVarchar2,
                            checkedNo,ParameterDirection.Input),
                        new OracleParameter(":CHECKEDUSERID",OracleDbType.Int32,
                            sUserInfo.UserID,ParameterDirection.Input),
                        new OracleParameter(":DateBegin",OracleDbType.Date,
                            cre.Properties["DateBegin"],ParameterDirection.Input),
                        new OracleParameter(":DateEnd",OracleDbType.Date,
                            cre.Properties["DateEnd"],ParameterDirection.Input),
                        new OracleParameter(":mouldstatus",OracleDbType.NVarchar2,
                            cre.Properties["MouldStatus"],ParameterDirection.Input),
                        new OracleParameter(":GoodsIDS",OracleDbType.NVarchar2,
                            cre.Properties["GoodsCodeList"],ParameterDirection.Input),
                        new OracleParameter(":GoodsTypeCode",OracleDbType.NVarchar2,
                            cre.Properties["GoodsTypeCode"],ParameterDirection.Input),
                    };
                    returnRows += oracleTrConn.ExecuteNonQuery(sql, paras);
                    #endregion
                }
                oracleTrConn.Commit();
            }
            catch (Exception ex)
            {
                if (oracleTrConn.ConnState == System.Data.ConnectionState.Open)
                {
                    oracleTrConn.Rollback();
                    oracleTrConn.Disconnect();
                }
                throw ex;
            }
            finally
            {
                if (oracleTrConn.ConnState == System.Data.ConnectionState.Open)
                {
                    oracleTrConn.Disconnect();
                }
            }
            return sre;
        }

        /// <summary>
        /// 停用盘点单
        /// </summary>
        /// <param name="sUserInfo">用户基本信息</param>
        /// <returns></returns>
        public static ServiceResultEntity DisableMouldChecked(SUserInfo sUserInfo, ClientRequestEntity cre)
        {
            ServiceResultEntity sre = new ServiceResultEntity();
            int checkedID = Convert.ToInt32(cre.Properties["CheckedID"]);
            int returnRows = 0;
            IDBTransaction oracleTrConn = ClsDbFactory.CreateDBTransaction(DataBaseType.ORACLE, DataManager.ConnectionString);
            try
            {
                string sql = "update TP_PC_MouldChecked set valueflag='0', updateuserid=:updateuserid where CheckedID=:CheckedID and valueflag='1'";
                OracleParameter[] Paras = new OracleParameter[] {
                    new OracleParameter(":CheckedID",OracleDbType.Int32,
                        checkedID,ParameterDirection.Input),
                    new OracleParameter(":updateuserid",OracleDbType.Int32,
                        sUserInfo.UserID,ParameterDirection.Input)
                };
                returnRows = oracleTrConn.ExecuteNonQuery(sql, Paras);
                if (returnRows > 0)
                {
                    sre.Status = Constant.ServiceResultStatus.Success;
                }
                else
                {
                    sre.Status = Constant.ServiceResultStatus.Other;
                }
                oracleTrConn.Commit();
                oracleTrConn.Disconnect();
            }
            catch (Exception ex)
            {
                if (oracleTrConn.ConnState == System.Data.ConnectionState.Open)
                {
                    oracleTrConn.Rollback();
                    oracleTrConn.Disconnect();
                }
                throw ex;
            }
            finally
            {
                if (oracleTrConn.ConnState == System.Data.ConnectionState.Open)
                {
                    oracleTrConn.Disconnect();
                }
            }
            return sre;
        }

        /// <summary>
        /// 更新盘点单(条码盘点)
        /// </summary>
        /// <param name="InCheckedID">盘点单号</param>
        /// <param name="BarCode">产品条码</param>
        /// <param name="sUserInfo">用户基本信息</param>
        /// <returns></returns>
        public static ServiceResultEntity UpdateMouldChecked(SUserInfo sUserInfo, ClientRequestEntity cre)
        {
            ServiceResultEntity sre = new ServiceResultEntity();
            int checkedID = Convert.ToInt32(cre.Properties["CheckedID"]);
            string barcode = cre.Properties["Barcode"].ToString();
            int returnRows = 0;
            IDBTransaction oracleTrConn = ClsDbFactory.CreateDBTransaction(DataBaseType.ORACLE, DataManager.ConnectionString);
            try
            {
                string sql = null;
                OracleParameter[] paras = null;
                DataSet ds = null;

                sql = @"select CheckedFlag,CHECKEDUSERID from TP_PC_MouldCheckedDetail where CheckedID=:CheckedID and MouldBarCode=:MouldBarCode and Valueflag=1";
                paras = new OracleParameter[] {
                    new OracleParameter(":CheckedID",OracleDbType.Int32,
                        checkedID,ParameterDirection.Input),
                    new OracleParameter(":MouldBarCode",OracleDbType.NVarchar2,
                        barcode,ParameterDirection.Input)
                };

                ds = oracleTrConn.GetSqlResultToDs(sql, paras);
                // 不在盘点单中
                if (ds != null && ds.Tables[0].Rows.Count == 0)
                {
                    sql = @"select CheckedNo from tp_pC_Mouldchecked where CheckedID=:CheckedID";
                    paras = new OracleParameter[] {
                    new OracleParameter(":CheckedID",OracleDbType.Int32,
                        checkedID,ParameterDirection.Input),
                    };
                    ds = oracleTrConn.GetSqlResultToDs(sql, paras);
                    if (ds == null || ds.Tables.Count == 0 || ds.Tables[0].Rows.Count == 0)
                    {
                        sre.Status = Constant.ServiceResultStatus.Other;
                        sre.OtherStatus = -1;
                        sre.Message = "盘点单不存在";
                        return sre;
                    }
                    string checkedNo = ds.Tables[0].Rows[0]["CheckedNo"].ToString();

                    // 条码是否在模具档案中
                    sql = "SELECT gdd.MouldID\n" +
                        "  FROM tp_pC_Mould gdd\n" +
                        " WHERE gdd.Mouldbarcode = :MouldBarCode";
                    paras = new OracleParameter[] {
                        new OracleParameter(":MouldBarCode",OracleDbType.NVarchar2,
                            barcode,ParameterDirection.Input)
                    };
                    DataTable dt = oracleTrConn.GetSqlResultToDt(sql, paras);
                    if (dt == null || dt.Rows.Count == 0)
                    {
                        // 无效条码
                        sre.Status = Constant.ServiceResultStatus.Other;
                        sre.OtherStatus = -1;
                        sre.Message = "条码【" + barcode + "】为无效条码";
                        return sre;
                    }

                    sql =
                    "INSERT INTO TP_PC_MouldCHECKEDDETAIL\n" +
                    "  (CHECKEDID\n" +
                    "  ,CHECKEDNO\n" +
                    "  ,MouldID\n" +
                    "  ,MouldCODE\n" +
                    "  ,MouldBARCODE\n" +
                    "  ,mouldtype\n" +
                    "  ,materialsupplier\n" +
                    "  ,productiondate\n" +
                    "  ,goodsid\n" +
                    "  ,goodscode\n" +
                    "  ,userid\n" +
                    "  ,usercode\n" +
                    "  ,weight\n" +
                    "  ,cost\n" +
                    "  ,standardgroutingnum\n" +
                    "  ,groutingnum\n" +
                    "  ,groutingdatebegin\n" +
                    "  ,groutingdateend\n" +
                    "  ,lastgroutingbatchno\n" +
                    "  ,mouldstatus\n" +
                    "  ,premouldstatus\n" +
                    "  ,repairflag\n" +
                    "  ,scrapdate\n" +
                    "  ,scrapuserid\n" +
                    "  ,scrapusercode\n" +
                    "  ,scrapreason\n" +
                    "  ,scrapresponsibility\n" +
                    "  ,lastchangehistoryid\n" +
                    "  ,groutinglinedetailid\n" +
                    "  ,REMARKS\n" +
                    "  ,CHECKEDFLAG\n" +
                    "  ,CHECKEDDATE\n" +
                    "  ,CHECKEDUSERID\n" +
                    "  ,ACCOUNTID\n" +
                    "  ,VALUEFLAG\n" +
                    "  ,CREATEUSERID\n" +
                    "  ,UPDATEUSERID)\n" +
                    "  SELECT :CHECKEDID\n" +
                    "        ,:CHECKEDNO\n" +
                    "        ,gdd.MouldID\n" +
                    "        ,gdd.MouldCODE\n" +
                    "        ,gdd.MouldBARCODE\n" +
                    "        ,gdd.mouldtype\n" +
                    "        ,gdd.materialsupplier\n" +
                    "        ,gdd.productiondate\n" +
                    "        ,gdd.goodsid\n" +
                    "        ,gdd.goodscode\n" +
                    "        ,gdd.userid\n" +
                    "        ,gdd.usercode\n" +
                    "        ,gdd.weight\n" +
                    "        ,gdd.cost\n" +
                    "        ,gdd.standardgroutingnum\n" +
                    "        ,gdd.groutingnum\n" +
                    "        ,gdd.groutingdatebegin\n" +
                    "        ,gdd.groutingdateend\n" +
                    "        ,gdd.lastgroutingbatchno\n" +
                    "        ,gdd.mouldstatus\n" +
                    "        ,gdd.premouldstatus\n" +
                    "        ,gdd.repairflag\n" +
                    "        ,gdd.scrapdate\n" +
                    "        ,gdd.scrapuserid\n" +
                    "        ,gdd.scrapusercode\n" +
                    "        ,gdd.scrapreason\n" +
                    "        ,gdd.scrapresponsibility\n" +
                    "        ,gdd.lastchangehistoryid\n" +
                    "        ,gdd.groutinglinedetailid\n" +
                    "        ,REMARKS\n" +
                    "        ,'2'\n" +
                    "        ,SYSDATE\n" +
                    "        ,:CHECKEDUSERID\n" +
                    "        ,ACCOUNTID\n" +
                    "        ,'1'\n" +
                    "        ,:CHECKEDUSERID\n" +
                    "        ,:CHECKEDUSERID\n" +
                    " FROM tp_pC_Mould gdd\n" +
                    "   WHERE MouldID = :MouldID";

                    paras = new OracleParameter[] {
                        new OracleParameter(":CHECKEDID",OracleDbType.Int32,
                            checkedID,ParameterDirection.Input),
                        new OracleParameter(":CHECKEDNO",OracleDbType.NVarchar2,
                            checkedNo,ParameterDirection.Input),
                        new OracleParameter(":CHECKEDUSERID",OracleDbType.Int32,
                            sUserInfo.UserID,ParameterDirection.Input),
                        new OracleParameter(":MouldID",OracleDbType.Int32,
                            dt.Rows[0]["MouldID"],ParameterDirection.Input),
                    };
                    returnRows = oracleTrConn.ExecuteNonQuery(sql, paras);
                    //returnRows = 10;
                }
                else
                {
                    string checkedFlag = ds.Tables[0].Rows[0]["CheckedFlag"].ToString();
                    if (checkedFlag == "1")
                    {
                        sre.Status = Constant.ServiceResultStatus.Other;
                        sre.OtherStatus = -1;
                        sre.Message = "条码【" + barcode + "】已被盘点";
                        return sre;
                    }
                    else if (checkedFlag == "2")
                    {
                        sre.Status = Constant.ServiceResultStatus.Other;
                        sre.OtherStatus = -1;
                        sre.Message = "条码【" + barcode + "】已被盘盈";
                        return sre;
                    }
                    sql = "update TP_PC_MouldCheckedDetail set CheckedFlag=:CheckedFlag,CheckedDate=sysdate,CheckedUserID=:CheckedUserID,UpdateUserID=:UpdateUserID where CheckedID=:CheckedID and MouldBarCode=:BarCode and CheckedFlag=:whereCheckedFlag";
                    paras = new OracleParameter[] {
                        new OracleParameter(":CheckedUserID",OracleDbType.Int32,
                            sUserInfo.UserID,ParameterDirection.Input),
                        new OracleParameter(":CheckedFlag",OracleDbType.Int32,
                            (int)Constant.InCheckedFlag.InCheckeded,ParameterDirection.Input),
                        new OracleParameter(":CheckedID",OracleDbType.Int32,
                            checkedID,ParameterDirection.Input),
                        new OracleParameter(":BarCode",OracleDbType.NVarchar2,
                            barcode,ParameterDirection.Input),
                        new OracleParameter(":whereCheckedFlag",OracleDbType.Int32,
                            (int)Constant.InCheckedFlag.InCheckedNo,ParameterDirection.Input),
                        new OracleParameter(":UpdateUserID",OracleDbType.Int32,
                            sUserInfo.UserID,ParameterDirection.Input),
                    };
                    returnRows = oracleTrConn.ExecuteNonQuery(sql, paras);
                    //returnRows = 1;
                }

                if (returnRows == 0)
                {
                    oracleTrConn.Rollback();
                    oracleTrConn.Disconnect();
                }
                else
                {
                    oracleTrConn.Commit();
                    oracleTrConn.Disconnect();
                }
            }
            catch (Exception ex)
            {
                if (oracleTrConn.ConnState == System.Data.ConnectionState.Open)
                {
                    oracleTrConn.Rollback();
                    oracleTrConn.Disconnect();
                }
                throw ex;
            }
            finally
            {
                if (oracleTrConn.ConnState == System.Data.ConnectionState.Open)
                {
                    oracleTrConn.Disconnect();
                }
            }
            return sre;
        }

        /// <summary>
        /// 报损未盘点
        /// </summary>
        /// <param name="sUserInfo">用户基本信息</param>
        /// <returns></returns>
        public static ServiceResultEntity SaveClearMouldChecked(SUserInfo sUserInfo, ClientRequestEntity cre)
        {
            ServiceResultEntity sre = new ServiceResultEntity();
            int checkedID = Convert.ToInt32(cre.Properties["CheckedID"]);
            string barcodes = cre.Properties["Barcodes"].ToString();
            int barcodeCount = Convert.ToInt32(cre.Properties["BarcodeCount"]);
            int returnRows = 0;
            IDBTransaction oracleTrConn = ClsDbFactory.CreateDBTransaction(DataBaseType.ORACLE, DataManager.ConnectionString);
            try
            {
                // 待产 在库 可以报损
                string sqlString = "SELECT m.mouldid,m.mouldcode, m.mouldbarcode, m.mouldstatus,m.GoodsID,m.GoodsCode,m.GroutingNum\n" +
                "  FROM TP_PC_Mould m\n" +
                " WHERE m.valueflag = '1'\n" +
                "   AND m.accountid = :accountid\n" +
                "   AND m.mouldstatus IN (1, 3)\n" +
                "   AND instr(:barcodes, m.mouldbarcode) > 0\n" +
                " for update";
                OracleParameter[] paras = new OracleParameter[] {
                    new OracleParameter(":barcodes",OracleDbType.NVarchar2,
                        barcodes,ParameterDirection.Input),
                    new OracleParameter(":accountid",OracleDbType.Int32,
                        sUserInfo.AccountID,ParameterDirection.Input),
                };
                DataTable dt = oracleTrConn.GetSqlResultToDt(sqlString, paras);
                if (dt == null || dt.Rows.Count == 0)
                {
                    sre.Status = Constant.ServiceResultStatus.Other;
                    sre.Message = "【在产】或【已报废】的模具不能直接报废";
                    return sre;
                }

                if (dt.Rows.Count != barcodeCount)
                {
                    sre.Status = Constant.ServiceResultStatus.Other;
                    sre.Message = "【在产】或【已报废】的模具不能直接报废";
                }

                string sqlStringInsertHis =
                "INSERT INTO TP_PC_MouldChangeHistory\n" +
                "  (ChangeID\n" +
                "  ,MouldID\n" +
                "  ,MouldCode\n" +
                "  ,MouldBarcode\n" +
                "  ,GoodsID\n" +
                "  ,GoodsCode\n" +
                "  ,OperationType\n" +
                "  ,MouldStatus\n" +
                "  ,MouldStatusAfter\n" +
                "  ,GroutingNum\n" +
                "  ,ChangeFlag\n" +
                "  ,ScrapResponsibility\n" +
                "  ,AccountID\n" +
                "  ,CreateUserID)\n" +
                "VALUES\n" +
                "  (:ChangeID\n" +
                "  ,:MouldID\n" +
                "  ,:MouldCode\n" +
                "  ,:MouldBarcode\n" +
                "  ,:GoodsID\n" +
                "  ,:GoodsCode\n" +
                "  ,:OperationType\n" +
                "  ,:MouldStatus\n" +
                "  ,4\n" +
                "  ,:GroutingNum\n" +
                "  ,decode(:OperationType, 2, '2','0')\n" +
                "  ,'盘亏报损'\n" +
                "  ,:AccountID\n" +
                "  ,:CreateUserID)";

                string sqlStringUpdateMould = "UPDATE TP_PC_Mould m\n" +
                "   SET m.premouldstatus      = m.mouldstatus\n" +
                "      ,m.mouldstatus         = 4\n" +
                "      ,m.scrapdate           = SYSDATE\n" +
                "      ,m.scrapuserid         = :scrapuserid\n" +
                "      ,m.scrapusercode       = :scrapusercode\n" +
                "      ,m.ScrapResponsibility = '盘亏报损'\n" +
                "      ,m.LastChangeHistoryID = :LastChangeHistoryID\n" +
                "      ,m.updateuserid        = :updateuserid\n" +
                " WHERE m.mouldid = :mouldid";

                sqlString = "UPDATE tp_pc_Mouldcheckeddetail gdd\n" +
                "   SET gdd.checkedflag   = '3'\n" +
                "      ,gdd.CheckedDate   = SYSDATE\n" +
                "      ,gdd.CheckedUserID = :UserID\n" +
                "      ,gdd.UpdateUserID = :UserID\n" +
                " WHERE gdd.checkedflag = '0'\n" +
                "   AND gdd.CheckedID = :CheckedID" +
                "   AND gdd.mouldid = :MouldID";
                foreach (DataRow item in dt.Rows)
                {
                    int mouldHistoryID = Convert.ToInt32(oracleTrConn.GetSqlResultToObj("select seq_pc_mouldhistory_id.nextval from dual "));
                    // 插入变更履历
                    paras = new OracleParameter[] {
                        new OracleParameter(":ChangeID",OracleDbType.Int32,
                            mouldHistoryID,ParameterDirection.Input),
                        new OracleParameter(":MouldID",OracleDbType.Int32,
                            item["MouldID"],ParameterDirection.Input),
                        new OracleParameter(":MouldCode",OracleDbType.NVarchar2,
                            item["MouldCode"],ParameterDirection.Input),
                        new OracleParameter(":MouldBarcode",OracleDbType.NVarchar2,
                            item["MouldBarcode"],ParameterDirection.Input),
                        new OracleParameter(":GoodsID",OracleDbType.Int32,
                            item["GoodsID"],ParameterDirection.Input),
                        new OracleParameter(":GoodsCode",OracleDbType.NVarchar2,
                            item["GoodsCode"],ParameterDirection.Input),
                        new OracleParameter(":MouldStatus",OracleDbType.Int32,
                            item["MouldStatus"],ParameterDirection.Input),
                        new OracleParameter(":GroutingNum",OracleDbType.Int32,
                            item["GroutingNum"],ParameterDirection.Input),
                        new OracleParameter(":AccountID",OracleDbType.Int32,
                            sUserInfo.AccountID,ParameterDirection.Input),
                        new OracleParameter(":CreateUserID",OracleDbType.Int32,
                            sUserInfo.UserID,ParameterDirection.Input),
                        new OracleParameter(":OperationType",OracleDbType.Int32,
                            (item["MouldStatus"].ToString() == "1"? (int)Constant.MouldOperationType.InvToScrap:(int)Constant.MouldOperationType.OutToScrap)
                            ,ParameterDirection.Input),
                    };
                    returnRows += oracleTrConn.ExecuteNonQuery(sqlStringInsertHis, paras);
                    // 修改模具状态
                    paras = new OracleParameter[] {
                        new OracleParameter(":MouldID",OracleDbType.Int32,
                            item["MouldID"],ParameterDirection.Input),
                        new OracleParameter(":scrapuserid",OracleDbType.Int32,
                            sUserInfo.UserID,ParameterDirection.Input),
                        new OracleParameter(":LastChangeHistoryID",OracleDbType.Int32,
                            mouldHistoryID,ParameterDirection.Input),
                        new OracleParameter(":updateuserid",OracleDbType.Int32,
                            sUserInfo.UserID,ParameterDirection.Input),
                        new OracleParameter(":scrapusercode",OracleDbType.NVarchar2,
                            sUserInfo.UserCode,ParameterDirection.Input),
                    };
                    returnRows += oracleTrConn.ExecuteNonQuery(sqlStringUpdateMould, paras);
                    // 清除盘点明细
                    paras = new OracleParameter[] {
                        new OracleParameter(":MouldID",OracleDbType.Int32,
                            item["MouldID"],ParameterDirection.Input),
                        new OracleParameter(":UserID",OracleDbType.Int32,
                            sUserInfo.UserID,ParameterDirection.Input),
                        new OracleParameter(":CheckedID",OracleDbType.Int32,
                            checkedID,ParameterDirection.Input),
                    };
                    returnRows += oracleTrConn.ExecuteNonQuery(sqlString, paras);
                }

                oracleTrConn.Commit();
            }
            catch (Exception ex)
            {
                if (oracleTrConn.ConnState == System.Data.ConnectionState.Open)
                {
                    oracleTrConn.Rollback();
                    oracleTrConn.Disconnect();
                }
                throw ex;
            }
            finally
            {
                if (oracleTrConn.ConnState == System.Data.ConnectionState.Open)
                {
                    oracleTrConn.Disconnect();
                }
            }
            return sre;
        }

        /// <summary>
        /// 获取盘点单明细
        /// </summary>
        /// <param name="procedureID">工序ID</param>
        /// <returns>DataSet</returns>
        public static DataSet GetUpdateMouldCheckedInfo(int checkedID, SUserInfo sUserInfo)
        {
            IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString);
            try
            {
                con.Open();
                string sqlString = @"SELECT to_char(checked.UserCheckedCount) || '/' ||
                                   to_char(checked.CheckedCount) CheckedCount
                                  ,to_char(checked.UserOverageCount) || '/' ||
                                   to_char(checked.OverageCount) OverageCount
                                  ,to_char(checked.UserCheckedCount + checked.UserOverageCount) || '/' ||
                                   to_char(checked.CheckedCount + checked.OverageCount) || '/' ||
                                   to_char(TCount) TCount
                              FROM (SELECT icd.checkedid
                                          , SUM(CASE
                                                  WHEN icd.checkeduserid = :userid AND icd.CheckedFlag = '1' THEN
                                                   1
                                                  ELSE
                                                   0
                                                END) UserCheckedCount
                                          , SUM(CASE
                                                  WHEN icd.checkeduserid = :userid AND icd.CheckedFlag = '2' THEN
                                                   1
                                                  ELSE
                                                   0
                                                END) UserOverageCount
                                          , SUM(CASE
                                                  WHEN icd.CheckedFlag = '1' THEN
                                                   1
                                                  ELSE
                                                   0
                                                END) CheckedCount
                                          , SUM(CASE
                                                  WHEN icd.CheckedFlag = '2' THEN
                                                   1
                                                  ELSE
                                                   0
                                                END) OverageCount
                                          ,COUNT(icd.checkedid) TCount
                                      FROM tp_pC_Mouldcheckeddetail icd
                                     WHERE icd.checkedid = :checkedid
                                       AND icd.valueflag = '1'
                                        -- xuwei add 2022-02-26
                                        group by icd.checkedid 
                                        -- xuwei end
                                    ) checked";
                OracleParameter[] paras = new OracleParameter[]{
                        new OracleParameter(":checkedid",OracleDbType.Int32, checkedID, ParameterDirection.Input),
                        new OracleParameter(":userid",OracleDbType.Int32, sUserInfo.UserID,ParameterDirection.Input),
                    };
                DataSet ds = con.GetSqlResultToDs(sqlString, paras);
                ds.Tables[0].TableName = "Table1";

                sqlString = @"SELECT icd.goodscode, COUNT(icd.goodscode) goodscount
                                FROM tp_pC_Mouldcheckeddetail icd
                                WHERE icd.checkedid = :checkedid
                                AND icd.checkeduserid = :userid
                                AND icd.CheckedFlag = '1'
                                AND icd.valueflag = '1'
                                GROUP BY icd.goodscode";

                DataTable dt = con.GetSqlResultToDt(sqlString, paras);
                dt.TableName = "Table2";
                ds.Tables.Add(dt);

                return ds;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (con.ConnState == ConnectionState.Open)
                {
                    con.Close();
                }
            }
        }
        #endregion

        #region 模具库存结转

        /// <summary>
        /// 获取模具库存结转一览查询数据
        /// </summary>
        /// <param name="sUserInfo"></param>
        /// <returns></returns>
        public static ServiceResultEntity GetFPC1201QueryData(SUserInfo sUserInfo, ClientRequestEntity cre)
        {
            ServiceResultEntity sre = new ServiceResultEntity();
            IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString);
            try
            {
                con.Open();

                string sqlString =
                "SELECT mc.yyyymm, mc.begindate, mc.enddate, mc.remarks\n" +
                "  FROM TP_PC_MouldCarryOver mc\n" +
                " WHERE mc.accountid = :accountid\n" +
                "   AND mc.yyyymm >= :DateBegin\n" +
                "   AND mc.yyyymm <= :DateEnd\n" +
                " ORDER BY mc.yyyymm desc";

                OracleParameter[] parameters = new OracleParameter[]
                {
                    new OracleParameter(":accountid", OracleDbType.Int32, sUserInfo.AccountID, ParameterDirection.Input),
                    new OracleParameter(":DateBegin", OracleDbType.Char, cre.Properties["DateBegin"], ParameterDirection.Input),
                    new OracleParameter(":DateEnd", OracleDbType.Char, cre.Properties["DateEnd"], ParameterDirection.Input),
                };
                sre.Data = con.GetSqlResultToDs(sqlString, parameters);

                if (sre.Data == null || sre.Data.Tables.Count == 0 || sre.Data.Tables[0].Rows.Count == 0)
                {
                    sre.Status = Constant.ServiceResultStatus.NoSearchResults;
                    return sre;
                }

                sqlString = "SELECT mcd.yyyymm\n" +
                "      ,gt2.goodstypecode goodstypecode2\n" +
                "      ,gt2.goodstypename goodstypename2\n" +
                "      ,mcd.goodscode\n" +
                "      ,mcd.mouldnum\n" +
                "  FROM TP_PC_Mouldcarryoverdetail mcd\n" +
                " INNER JOIN tp_mst_goods g\n" +
                "    ON g.goodsid = mcd.goodsid\n" +
                " INNER JOIN tp_mst_goodstype gt\n" +
                "    ON gt.goodstypeid = g.goodstypeid\n" +
                " INNER JOIN tp_mst_goodstype gt2\n" +
                "    ON gt2.accountid = gt.accountid\n" +
                "   AND gt2.goodstypecode = substr(gt.goodstypecode, 1, 6)\n" +
                " WHERE mcd.accountid = :accountid\n" +
                "   AND mcd.yyyymm = :YYYYMM\n" +
                "   AND mcd.mouldstatus = 1 -- 在库\n" +
                " ORDER BY gt2.goodstypecode, mcd.goodscode";
                parameters = new OracleParameter[]
                {
                    new OracleParameter(":accountid", OracleDbType.Int32, sUserInfo.AccountID, ParameterDirection.Input),
                    new OracleParameter(":YYYYMM", OracleDbType.Char, sre.Data.Tables[0].Rows[0]["YYYYMM"], ParameterDirection.Input),
                };
                sre.Data.Tables.Add(con.GetSqlResultToDt(sqlString, parameters));

                return sre;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (con.ConnState == ConnectionState.Open)
                {
                    con.Close();
                }
            }
        }

        /// <summary>
        /// 获取模具库存结转明细查询数据
        /// </summary>
        /// <param name="sUserInfo"></param>
        /// <returns></returns>
        public static ServiceResultEntity GetFPC1201QueryDetailData(SUserInfo sUserInfo, ClientRequestEntity cre)
        {
            ServiceResultEntity sre = new ServiceResultEntity();
            IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString);
            try
            {
                con.Open();

                string sqlString = "SELECT mcd.yyyymm\n" +
                "      ,gt2.goodstypecode goodstypecode2\n" +
                "      ,gt2.goodstypename goodstypename2\n" +
                "      ,mcd.goodscode\n" +
                "      ,mcd.mouldnum\n" +
                "  FROM TP_PC_Mouldcarryoverdetail mcd\n" +
                " INNER JOIN tp_mst_goods g\n" +
                "    ON g.goodsid = mcd.goodsid\n" +
                " INNER JOIN tp_mst_goodstype gt\n" +
                "    ON gt.goodstypeid = g.goodstypeid\n" +
                " INNER JOIN tp_mst_goodstype gt2\n" +
                "    ON gt2.accountid = gt.accountid\n" +
                "   AND gt2.goodstypecode = substr(gt.goodstypecode, 1, 6)\n" +
                " WHERE mcd.accountid = :accountid\n" +
                "   AND mcd.yyyymm = :YYYYMM\n" +
                "   AND mcd.mouldstatus = 1 -- 在库\n" +
                " ORDER BY gt2.goodstypecode, mcd.goodscode";

                OracleParameter[] parameters = new OracleParameter[]
                {
                    new OracleParameter(":accountid", OracleDbType.Int32, sUserInfo.AccountID, ParameterDirection.Input),
                    new OracleParameter(":YYYYMM", OracleDbType.Char, cre.Properties["YYYYMM"], ParameterDirection.Input),
                };
                sre.Data = con.GetSqlResultToDs(sqlString, parameters);

                return sre;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (con.ConnState == ConnectionState.Open)
                {
                    con.Close();
                }
            }
        }

        #region 模具库存自动结转设置
        /// <summary>
        /// 获取模具库存自动结转设置
        /// </summary>
        /// <param name="cre"></param>
        /// <param name="sUserInfo"></param>
        /// <returns></returns>
        public static ServiceResultEntity GetFPC1204Data(ClientRequestEntity cre, SUserInfo sUserInfo)
        {
            IDBConnection oracleConn = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString);
            try
            {
                ServiceResultEntity sre = new ServiceResultEntity();

                string sqlString = "SELECT JOB_NAME, REPEAT_INTERVAL, LAST_START_DATE, NEXT_RUN_DATE, ENABLED\n" +
                "  FROM USER_SCHEDULER_JOBS\n" +
                " WHERE JOB_NAME = 'JOB_AUTOMOULDCARRYOVER'";

                oracleConn.Open();

                DataSet data = oracleConn.GetSqlResultToDs(sqlString);
                sre.Data = data;

                oracleConn.Close();
                return sre;
            }
            catch (Exception ex)
            {
                if (oracleConn.ConnState == ConnectionState.Open)
                {
                    oracleConn.Close();
                }
                throw ex;
            }
        }

        /// <summary>
        /// 保存模具库存自动结转设置
        /// </summary>
        /// <param name="cre"></param>
        /// <param name="sUserInfo"></param>
        /// <returns></returns>
        public static ServiceResultEntity SetFPC1204Data(ClientRequestEntity cre, SUserInfo sUserInfo)
        {
            IDBTransaction oracleTrConn = ClsDbFactory.CreateDBTransaction(DataBaseType.ORACLE, DataManager.ConnectionString);
            try
            {
                ServiceResultEntity sre = new ServiceResultEntity();
                // 检验参数的有效性
                if (cre == null || cre.Request == null || cre.Request.ToString() == "")
                {
                    sre.Status = Constant.ServiceResultStatus.NoModifyData;
                    return sre;
                }

                oracleTrConn.Connect();

                OracleParameter[] oracleParameter = new OracleParameter[]
                {
                    new OracleParameter("name","JOB_AUTOMOULDCARRYOVER"),
                    new OracleParameter("attribute", "repeat_interval"),
                    new OracleParameter("value", cre.Request.ToString())
                };
                oracleTrConn.ExecStoredProcedure("sys.dbms_scheduler.set_attribute", oracleParameter);

                oracleParameter = new OracleParameter[]
                {
                    new OracleParameter("name","JOB_AUTOMOULDCARRYOVER")
                };
                if (cre.Properties["Enabled"].ToString() == "1")
                {
                    oracleTrConn.ExecStoredProcedure("sys.dbms_scheduler.enable", oracleParameter);
                }
                else
                {
                    oracleTrConn.ExecStoredProcedure("sys.dbms_scheduler.disable", oracleParameter);
                }

                //if (cre.Properties["Run"].ToString() == "1")
                //{
                //    // oracleParameter = new OracleParameter[] 
                //    // {
                //    //     new OracleParameter("job_name","JOB_AUTOMOULDCARRYOVER"),
                //    //     new OracleParameter("use_current_session", OracleDbType., 0)
                //    // };
                //    //oracleTrConn.ExecStoredProcedure("sys.dbms_scheduler.run_job", oracleParameter);

                //    string sqlString = "begin\n" +
                //    "sys.dbms_scheduler.run_job('JOB_AUTOMOULDCARRYOVER', false);\n" +
                //    "end;";
                //    oracleTrConn.ExecuteNonQuery(sqlString);
                //}

                oracleTrConn.Commit();
                oracleTrConn.Disconnect();
                return sre;
            }
            catch (Exception ex)
            {
                if (oracleTrConn.ConnState == ConnectionState.Open)
                {
                    oracleTrConn.Rollback();
                    oracleTrConn.Disconnect();
                }
                throw ex;
            }
        }
        #endregion

        /// <summary>
        /// 获取模具库存结转期初
        /// </summary>
        /// <param name="cre"></param>
        /// <param name="sUserInfo"></param>
        /// <returns></returns>
        public static ServiceResultEntity GetFPC1202Data(ClientRequestEntity cre, SUserInfo sUserInfo)
        {
            IDBConnection oracleConn = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString);
            try
            {
                ServiceResultEntity sre = new ServiceResultEntity();

                string sqlString =
                "SELECT --nvl(MAX(mc.yyyymm), ' ') LastYYYYMM\n" +
                "       MAX(mc.yyyymm) LastYYYYMM\n" +
                "      ,to_char(SYSDATE, 'yyyymm') YYYYMM\n" +
                "      ,trunc(SYSDATE, 'mm') CurMonth\n" +
                "  FROM tp_pc_mouldcarryover mc\n" +
                " WHERE mc.accountid = :accountid";

                OracleParameter[] oracleParameter = new OracleParameter[]
                {
                    new OracleParameter(":accountid", OracleDbType.Int32, sUserInfo.AccountID, ParameterDirection.Input),
                };

                sre.Data = oracleConn.GetSqlResultToDs(sqlString, oracleParameter);

                oracleConn.Close();
                return sre;
            }
            catch (Exception ex)
            {
                if (oracleConn.ConnState == ConnectionState.Open)
                {
                    oracleConn.Close();
                }
                throw ex;
            }
        }

        /// <summary>
        /// 模具库存结转
        /// </summary>
        /// <param name="cre"></param>
        /// <param name="sUserInfo"></param>
        /// <returns></returns>
        public static ServiceResultEntity SetFPC1202Data(ClientRequestEntity cre, SUserInfo sUserInfo)
        {
            IDBTransaction oracleTrConn = ClsDbFactory.CreateDBTransaction(DataBaseType.ORACLE, DataManager.ConnectionString);
            try
            {
                ServiceResultEntity sre = new ServiceResultEntity();

                oracleTrConn.Connect();

                OracleParameter[] oracleParameter = new OracleParameter[]
                {
                    new OracleParameter("in_CarryoverMonth", OracleDbType.Date, cre.Properties["CarryoverMonth"], ParameterDirection.Input),
                    new OracleParameter("in_AccountID", OracleDbType.Int32, sUserInfo.AccountID, ParameterDirection.Input),
                    new OracleParameter("in_UserID", OracleDbType.Int32, sUserInfo.UserID, ParameterDirection.Input),
                    new OracleParameter("in_Remarks", OracleDbType.NVarchar2, cre.Properties["Remarks"], ParameterDirection.Input),
                };
                oracleTrConn.ExecStoredProcedure("pro_job_pc_mouldcarryover", oracleParameter);

                oracleTrConn.Commit();
                oracleTrConn.Disconnect();
                return sre;
            }
            catch (Exception ex)
            {
                if (oracleTrConn.ConnState == ConnectionState.Open)
                {
                    oracleTrConn.Rollback();
                    oracleTrConn.Disconnect();
                }
                throw ex;
            }
        }

        /// <summary>
        /// 模具库存反结转
        /// </summary>
        /// <param name="cre"></param>
        /// <param name="sUserInfo"></param>
        /// <returns></returns>
        public static ServiceResultEntity SetFPC1203Data(ClientRequestEntity cre, SUserInfo sUserInfo)
        {
            IDBTransaction oracleTrConn = ClsDbFactory.CreateDBTransaction(DataBaseType.ORACLE, DataManager.ConnectionString);
            try
            {
                ServiceResultEntity sre = new ServiceResultEntity();

                oracleTrConn.Connect();

                OracleParameter[] oracleParameter = new OracleParameter[]
                {
                    new OracleParameter(":accountid", OracleDbType.Int32, sUserInfo.AccountID, ParameterDirection.Input),
                    new OracleParameter(":yyyymm", OracleDbType.Char, cre.Properties["YYYYMM"], ParameterDirection.Input),
                };

                string sqlString =
                "DELETE FROM tp_pc_mouldcarryover mc\n" +
                " WHERE mc.accountid = :accountid\n" +
                "   AND mc.yyyymm >= :yyyymm";
                oracleTrConn.ExecuteNonQuery(sqlString, oracleParameter);

                sqlString =
                "DELETE FROM tp_pc_mouldcarryoverdetail mcd\n" +
                " WHERE mcd.accountid = :accountid\n" +
                "   AND mcd.yyyymm >= :yyyymm";
                oracleTrConn.ExecuteNonQuery(sqlString, oracleParameter);

                sqlString =
                "UPDATE tp_pc_mouldchangehistory mch\n" +
                "   SET mch.yyyymm = NULL\n" +
                " WHERE mch.accountid = :accountid\n" +
                "   AND mch.yyyymm >= :yyyymm";
                oracleTrConn.ExecuteNonQuery(sqlString, oracleParameter);

                oracleTrConn.Commit();
                oracleTrConn.Disconnect();
                return sre;
            }
            catch (Exception ex)
            {
                if (oracleTrConn.ConnState == ConnectionState.Open)
                {
                    oracleTrConn.Rollback();
                    oracleTrConn.Disconnect();
                }
                throw ex;
            }
        }
        #endregion

        #region 成检材料报损配置
        /// <summary>
        /// 成检材料报损配置工序查询
        /// </summary>
        /// <param name="sUserInfo"></param>
        /// <returns></returns>
        public static ServiceResultEntity GetFinishedCheckProcedure(SUserInfo sUserInfo, ClientRequestEntity cre)
        {
            ServiceResultEntity sre = new ServiceResultEntity();
            IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString);
            try
            {
                con.Open();

                string sqlString = "SELECT t.procedureid\n" +
                "      ,t.procedurename\n" +
                "  FROM tp_pc_procedure t\n" +
                " WHERE t.valueflag = '1'\n" +
                " AND t.AccountID = :accountid\n" +
                "   AND t.modeltype = -5" +
                "   AND (:PROCEDURENAME is null or :PROCEDURENAME = '' or instr(t.procedurename, :PROCEDURENAME) > 0 )\n";


                OracleParameter[] parameters = new OracleParameter[]
                {
                    new OracleParameter(":accountid", OracleDbType.Int32, sUserInfo.AccountID, ParameterDirection.Input),
                    new OracleParameter(":PROCEDURENAME", OracleDbType.Varchar2, cre.Properties["PROCEDURENAME"], ParameterDirection.Input),


                };
                sre.Data = con.GetSqlResultToDs(sqlString, parameters);

                return sre;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (con.ConnState == ConnectionState.Open)
                {
                    con.Close();
                }
            }
        }

        /// <summary>
        /// 成检材料报损配置查询
        /// </summary>
        /// <param name="sUserInfo"></param>
        /// <returns></returns>
        public static ServiceResultEntity GetIdnrktype(SUserInfo sUserInfo, ClientRequestEntity cre)
        {
            ServiceResultEntity sre = new ServiceResultEntity();
            IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString);
            try
            {
                con.Open();

                string sqlString = "SELECT (SELECT 1\n" +
                "          FROM tp_pc_procedureidnrktype pit\n" +
                "         WHERE pit.idnrktype = d.dictionaryvalue\n" +
                "           AND pit.procedureid = :PROCEDUREID) AS check1\n" +
                "      ,d.dictionaryvalue as idnrktype \n" +
                "      ,(SELECT 1\n" +
                "          FROM tp_pc_procedureidnrktype pit\n" +
                "         WHERE pit.idnrktype = d.dictionaryvalue\n" +
                "           AND pit.procedureid = :PROCEDUREID AND pit.SCANFLAG = 1) AS SCANFLAG\n" +
                "  FROM tp_mst_datadictionary d\n" +
                " WHERE d.dictionarytype = 'TPC010'\n" +
                "   AND d.valueflag = '1'\n" +
                "   AND d.accountid = :accountid" +
                "   AND (:PROCEDURENAME is null or :PROCEDURENAME = '' or instr(d.dictionaryvalue, :PROCEDURENAME) > 0 )\n";


                OracleParameter[] parameters = new OracleParameter[]
                {
                    new OracleParameter(":accountid", OracleDbType.Int32, sUserInfo.AccountID, ParameterDirection.Input),
                    new OracleParameter(":PROCEDUREID", OracleDbType.Int32, cre.Properties["PROCEDUREID"], ParameterDirection.Input),
                    new OracleParameter(":PROCEDURENAME", OracleDbType.Varchar2, cre.Properties["PROCEDURENAME"]+"", ParameterDirection.Input),

                };
                sre.Data = con.GetSqlResultToDs(sqlString, parameters);

                return sre;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (con.ConnState == ConnectionState.Open)
                {
                    con.Close();
                }
            }
        }

        /// <summary>
        /// 已选成检材料类型查询
        /// </summary>
        /// <param name="sUserInfo"></param>
        /// <returns></returns>
        public static ServiceResultEntity GetProcedureIdnrktype(SUserInfo sUserInfo, ClientRequestEntity cre)
        {
            ServiceResultEntity sre = new ServiceResultEntity();
            IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString);
            try
            {
                con.Open();

                string sqlString = "SELECT pi.procedureid\n" +
                "         ,pi.idnrktype\n" +
                "         ,pi.SCANFLAG\n" +
                "         ,1 as AlreadyCheck\n" +
                "     FROM tp_pc_procedureidnrktype pi\n" +
                "    WHERE pi.procedureid = :PROCEDUREID";

                OracleParameter[] parameters = new OracleParameter[]
                {
                    new OracleParameter(":PROCEDUREID", OracleDbType.Int32, cre.Properties["PROCEDUREID"], ParameterDirection.Input),

                };
                sre.Data = con.GetSqlResultToDs(sqlString, parameters);

                return sre;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (con.ConnState == ConnectionState.Open)
                {
                    con.Close();
                }
            }
        }

        /// <summary>
        /// 保存成检材料类型
        /// </summary>
        /// <param name="sUserInfo"></param>
        /// <returns></returns>
        public static ServiceResultEntity SavePackingBOMData(SUserInfo sUserInfo, ClientRequestEntity cre)
        {
            ServiceResultEntity sre = new ServiceResultEntity();
            int procedureId = Convert.ToInt32(cre.Properties["ProcedureId"]);
            DataTable procedureTable = cre.Data.Tables[0];

            int returnRows = 0;
            IDBTransaction oracleTrConn = ClsDbFactory.CreateDBTransaction(DataBaseType.ORACLE, DataManager.ConnectionString);
            try
            {
                string sqlString = null;
                if (procedureTable != null && procedureTable.Rows.Count > 0)
                {
                    //先删除后插入
                    sqlString = "delete from tp_pc_procedureidnrktype where procedureid=" + procedureId;
                    oracleTrConn.ExecuteNonQuery(sqlString);
                    foreach (DataRow item in procedureTable.Rows)
                    {
                        sqlString = @"insert into tp_pc_procedureidnrktype 
                                (
                                    procedureid,
                                    idnrktype,
                                    SCANFLAG
                                ) values
                                (   :procedureid,
                                    :idnrktype,
                                    :scanflag
                                )";
                        OracleParameter[] Paras = new OracleParameter[] {
                            new OracleParameter(":procedureid",OracleDbType.Int32,procedureId,ParameterDirection.Input),
                            new OracleParameter(":idnrktype",OracleDbType.Varchar2, item["idnrktype"] + "",ParameterDirection.Input),
                            new OracleParameter(":scanflag",OracleDbType.Varchar2, item["SCANFLAG"] + "",ParameterDirection.Input),
                        };
                        returnRows += oracleTrConn.ExecuteNonQuery(sqlString, Paras);
                    }
                }

                oracleTrConn.Commit();
            }
            catch (Exception ex)
            {
                if (oracleTrConn.ConnState == System.Data.ConnectionState.Open)
                {
                    oracleTrConn.Rollback();
                    oracleTrConn.Disconnect();
                }
                throw ex;
            }
            finally
            {
                if (oracleTrConn.ConnState == System.Data.ConnectionState.Open)
                {
                    oracleTrConn.Disconnect();
                }
            }
            return sre;
        }

        #endregion

        #region 线边仓组件配置

        /// <summary>
        /// 线边仓查询
        /// </summary>
        /// <param name="sUserInfo"></param>
        /// <param name="cre"></param>
        /// <returns></returns>
        public static ServiceResultEntity GetLGORT(SUserInfo sUserInfo, ClientRequestEntity cre)
        {
            ServiceResultEntity sre = new ServiceResultEntity();
            IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString);
            try
            {
                con.Open();
                string sqlString = @"SELECT dictionaryvalue,dictionaryID FROM
                                    TP_MST_DATADICTIONARY MDD
                                    WHERE DICTIONARYTYPE = :DICTIONARYTYPE
                                    AND ACCOUNTID =:accountid
                                    AND VALUEFLAG =1  
                                    AND (:dictionaryvalue IS NULL OR :dictionaryvalue = '' OR INSTR(MDD.DICTIONARYVALUE,:dictionaryvalue)>0)";
                OracleParameter[] parameters = new OracleParameter[]
               {
                    new OracleParameter(":DICTIONARYTYPE", OracleDbType.NVarchar2, cre.Properties["DICTIONARYTYPE"], ParameterDirection.Input),
                    new OracleParameter(":accountid", OracleDbType.Int32, sUserInfo.AccountID, ParameterDirection.Input),
                    new OracleParameter(":dictionaryvalue", OracleDbType.NVarchar2, cre.Properties["dictionaryvalue"], ParameterDirection.Input),
               };
                sre.Data = con.GetSqlResultToDs(sqlString, parameters);
                return sre;
            }
            catch (Exception ex)
            {

                throw ex;
            }
            finally
            {
                if (con.ConnState == ConnectionState.Open)
                {
                    con.Close();
                }
            }
        }

        /// <summary>
        /// 组件类型配置
        /// </summary>
        /// <param name="sUserInfo"></param>
        /// <param name="cre"></param>
        /// <returns></returns>
        public static ServiceResultEntity GetIDNRKTYPE(SUserInfo sUserInfo, ClientRequestEntity cre)
        {
            ServiceResultEntity sre = new ServiceResultEntity();
            IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString);

            try
            {
                con.Open();
                string sqlString = @"SELECT mdd.dictionaryvalue ModuleName
                                              ,CASE
                                                   WHEN pl.idnrktype IS NULL THEN
                                                    0
                                                   ELSE
                                                    1
                                               END check1
                                          FROM tp_mst_datadictionary mdd
                                          LEFT JOIN (SELECT tpl.idnrktype
                                                       FROM tp_pm_lgortidnrktyperel tpl
                                                      WHERE tpl.lgort = :lgort) pl
                                            ON mdd.dictionaryvalue = pl.idnrktype
                                          WHERE mdd.valueflag = 1
                                           AND mdd.dictionarytype = 'TPC010'
                                           AND mdd.accountid = :accountid
                                           AND (:ModuleName is null or :ModuleName = '' or instr (mdd.dictionaryvalue,:ModuleName)>0)";
                OracleParameter[] parameters = new OracleParameter[]
              {
                    new OracleParameter(":LGORT", OracleDbType.NVarchar2, cre.Properties["LGORT"], ParameterDirection.Input),
                    new OracleParameter(":accountid", OracleDbType.Int32, sUserInfo.AccountID, ParameterDirection.Input),
                    new OracleParameter(":ModuleName", OracleDbType.NVarchar2,cre.Properties["ModuleName"], ParameterDirection.Input),
              };
                sre.Data = con.GetSqlResultToDs(sqlString, parameters);
                return sre;
            }
            catch (Exception ex)
            {

                throw ex;
            }
            finally
            {
                if (con.ConnState == ConnectionState.Open)
                {
                    con.Close();
                }
            }
        }

        /// <summary>
        /// 保存线边仓与组件类别关联关系
        /// </summary>
        /// <param name="sUserInfo"></param>
        /// <param name="cre"></param>
        /// <returns></returns>
        public static ServiceResultEntity SaveLgortIdnrktype(SUserInfo sUserInfo, ClientRequestEntity cre)
        {
            ServiceResultEntity sre = new ServiceResultEntity();
            string LGORT = cre.Properties["LGORT"].ToString();
            DataTable LGORTTable = cre.Data.Tables[0];

            int returnRows = 0;
            IDBTransaction oracleTrConn = ClsDbFactory.CreateDBTransaction(DataBaseType.ORACLE, DataManager.ConnectionString);

            try
            {
                string sqlString = "delete from tp_pm_lgortidnrktyperel where LGORT =" + LGORT;
                oracleTrConn.ExecuteNonQuery(sqlString);
                if (LGORTTable != null && LGORTTable.Rows.Count > 0)
                {
                    foreach (DataRow item in LGORTTable.Rows)
                    {
                        sqlString = @"INSERT INTO tp_pm_lgortidnrktyperel
                                            (lgort
                                            ,idnrktype)
                                        VALUES
                                            (:lgort
                                            ,:idnrktype)";
                        OracleParameter[] Paras = new OracleParameter[] {
                            new OracleParameter(":lgort",OracleDbType.NVarchar2,LGORT,ParameterDirection.Input),
                            new OracleParameter(":idnrktype",OracleDbType.Varchar2, item["idnrktype"] + "",ParameterDirection.Input),
                        };
                        returnRows += oracleTrConn.ExecuteNonQuery(sqlString, Paras);
                    }
                }
                oracleTrConn.Commit();
            }
            catch (Exception ex)
            {

                if (oracleTrConn.ConnState == System.Data.ConnectionState.Open)
                {
                    oracleTrConn.Rollback();
                    oracleTrConn.Disconnect();
                }
                throw ex;
            }
            finally
            {
                if (oracleTrConn.ConnState == System.Data.ConnectionState.Open)
                {
                    oracleTrConn.Disconnect();
                }
            }
            return sre;
        }
        #endregion

        #region 产品装配明细表
        /// <summary>
        /// 产品装配明细表查询
        /// </summary>
        /// <param name="sUserInfo"></param>
        /// <returns></returns>
        public static ServiceResultEntity GetBarCodeIdnrkRel(SUserInfo sUserInfo, ClientRequestEntity cre)
        {
            ServiceResultEntity sre = new ServiceResultEntity();
            IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString);
            try
            {
                string sqlString = @"
                SELECT T.RELATIONID,
                       T.BARCODE,
                       T.MATNR,
                       T.IDNRK,
                       T.MEINS,
                       T.MENGE,
                       T.IDNRKNAME,
                       case when instr(T.IDNRKONLYCODE,',') > 0 then 
                       substr(T.IDNRKONLYCODE,0,instr(T.IDNRKONLYCODE,',')-1) 
                       else T.IDNRKONLYCODE end as IDNRKONLYCODE,
                       T.CHARG,
                       T.LGORT,
                       P.PROCEDURENAME,
                       T.CREATETIME,
                       CU.USERNAME AS CREATEUSERNAME
                  FROM TP_PM_BARCODEIDNRKREL T
                 INNER JOIN TP_PM_GROUTINGDAILYDETAIL GDD
                    ON GDD.BARCODE = T.BARCODE
                   AND (GDD.ONELEVELTYPEID IS NULL OR GDD.ONELEVELTYPEID <> 7)
                   AND (GDD.TWOLEVELTYPEID IS NULL OR GDD.TWOLEVELTYPEID <> 7)
                  LEFT JOIN TP_PC_PROCEDURE P
                    ON T.PROCEDUREID = P.PROCEDUREID
                  LEFT JOIN TP_MST_USER CU
                    ON CU.USERID = T.CREATEUSERID
                 WHERE T.ACCOUNTID = :ACCOUNTID
                   AND T.CREATETIME >= :CREATETIMEBEGIN
                   AND T.CREATETIME < :CREATETIMEEND
                   AND (:BARCODE IS NULL OR :BARCODE = '' OR INSTR(T.BARCODE, :BARCODE) > 0)
                   AND (:MATNR IS NULL OR :MATNR = '' OR INSTR(T.MATNR, :MATNR) > 0)
                   AND (:IDNRK IS NULL OR :IDNRK = '' OR INSTR(T.IDNRK, :IDNRK) > 0)
                   AND (:IDNRKNAME IS NULL OR :IDNRKNAME = '' OR INSTR(T.IDNRKNAME, :IDNRKNAME) > 0)
                   AND (:NOONLYCODEFLAG = 1 OR T.IDNRKONLYCODE IS NULL OR T.IDNRKONLYCODE = '') ";

                OracleParameter[] parameters = new OracleParameter[]
                {
                    new OracleParameter(":ACCOUNTID", OracleDbType.Int32, sUserInfo.AccountID, ParameterDirection.Input),
                    new OracleParameter(":BARCODE", OracleDbType.Varchar2, cre.Properties["BarCode"], ParameterDirection.Input),
                    new OracleParameter(":MATNR", OracleDbType.Varchar2, cre.Properties["Matnr"], ParameterDirection.Input),
                    new OracleParameter(":IDNRK", OracleDbType.Varchar2, cre.Properties["Idnrk"], ParameterDirection.Input),
                    new OracleParameter(":IDNRKNAME", OracleDbType.Varchar2, cre.Properties["IdnrkName"], ParameterDirection.Input),
                    new OracleParameter(":CREATETIMEBEGIN", OracleDbType.Date, cre.Properties["CreateTimeBegin"], ParameterDirection.Input),
                    new OracleParameter(":CREATETIMEEND", OracleDbType.Date, cre.Properties["CreateTimeEnd"], ParameterDirection.Input),
                    new OracleParameter(":NOONLYCODEFLAG", OracleDbType.Int32, cre.Properties["NoOnlyCodeFlag"], ParameterDirection.Input),
                };

                sre.Data = con.GetSqlResultToDs(sqlString, parameters);
                return sre;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (con.ConnState == ConnectionState.Open)
                {
                    con.Close();
                }
            }
        }

        /// <summary>
        /// 保存产品装配组件唯一编码
        /// </summary>
        /// <param name="sUserInfo"></param>
        /// <returns></returns>
        public static ServiceResultEntity SaveIdnrkOnlyCode(SUserInfo sUserInfo, ClientRequestEntity cre)
        {
            ServiceResultEntity sre = new ServiceResultEntity();
            int relationID = Convert.ToInt32(cre.Properties["RelationID"]);
            string idnrkOnlyCode = cre.Properties["IdnrkOnlyCode"] + "";

            IDBTransaction oracleTrConn = ClsDbFactory.CreateDBTransaction(DataBaseType.ORACLE, DataManager.ConnectionString);
            try
            {
                string sqlString = null;

                //更新
                sqlString = @"update TP_PM_BARCODEIDNRKREL set IDNRKONLYCODE = :idnrkOnlyCode where RELATIONID = :relationID";
                OracleParameter[] Paras = new OracleParameter[] {
                    new OracleParameter(":relationID",OracleDbType.Int32,relationID,ParameterDirection.Input),
                    new OracleParameter(":idnrkOnlyCode",OracleDbType.Varchar2, idnrkOnlyCode,ParameterDirection.Input),
                };
                sre.OtherStatus = oracleTrConn.ExecuteNonQuery(sqlString, Paras);

                oracleTrConn.Commit();
            }
            catch (Exception ex)
            {
                if (oracleTrConn.ConnState == System.Data.ConnectionState.Open)
                {
                    oracleTrConn.Rollback();
                    oracleTrConn.Disconnect();
                }
                throw ex;
            }
            finally
            {
                if (oracleTrConn.ConnState == System.Data.ConnectionState.Open)
                {
                    oracleTrConn.Disconnect();
                }
            }
            return sre;
        }


        #endregion

        #region 品管数据记录明细表
        /// <summary>
        /// 品管数据记录明细表
        /// </summary>
        /// <param name="sUserInfo"></param>
        /// <returns></returns>
        public static ServiceResultEntity GetPinGuanDetail(SUserInfo sUserInfo, ClientRequestEntity cre)
        {
            ServiceResultEntity sre = new ServiceResultEntity();
            IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString);
            try
            {
                string sqlString = @"
                 SELECT DISTINCT PGI.BARCODE,
                    GD.GOODSCODE,
                    GD.GROUTINGLINECODE,
                    PGI.WORKSTATION,
                    U.USERNAME,
                    U.USERCODE,
                    PGI.CREATETIME, 
                    PGI.INSPECTIONLEVEL,
                    PGI.PACKINGDEFECT,
                    PGI.INSPECTIONGOODSLEVEL,
                    L.GOODSLEVELNAME,
                        (SELECT LISTAGG(to_char(D.defectcode) || '#' || to_char(d.defectname) || '#' ||
                                                to_char(d.defectusercode), ',') within GROUP(ORDER BY d.productiondefectid)
                                FROM tp_pm_defect d
                                WHERE D.PRODUCTIONDATAID = PDD.PRODUCTIONDATAID
                                AND D.VALUEFLAG = '1')   AS DEFECT, 
                    PGI.VALUEFLAG
                    FROM TP_PM_PinGuanInspection PGI
                    LEFT JOIN TP_MST_USER U ON PGI.CREATEUSERID = U.USERID 
                    LEFT JOIN TP_PM_GROUTINGDAILYDETAIL GD ON GD.BARCODE = PGI.BARCODE
                    LEFT JOIN TP_MST_GOODSLEVEL L ON L.GOODSLEVELTYPEID = GD.GOODSLEVELTYPEID
                    LEFT JOIN (SELECT  PD2.BARCODE,
                        PD2.PRODUCTIONDATAID FROM (
                        SELECT PD1.BARCODE,
                        PD1.PRODUCTIONDATAID,
                        ROW_NUMBER() OVER(PARTITION BY PD1.BARCODE ORDER BY PD1.PRODUCTIONDATAID DESC) AS DATAID
                        FROM TP_PM_PRODUCTIONDATA PD1 
                        WHERE PD1.VALUEFLAG = '1'
                        AND PD1.CHECKBATCHNO = '1'  
                        ORDER BY PD1.CREATETIME DESC
                        ) PD2 WHERE PD2.DATAID = 1) PDD ON PDD.BARCODE = PGI.BARCODE 
                    WHERE  PGI.CREATETIME >= :datebeginMasterStar 
                    AND PGI.CREATETIME <= :datebeginMasterEnd ";


                if (cre.Properties["BarCode"] + "" != "" && cre.Properties["BarCode"] + "" != null)
                {
                    string barcode = cre.Properties["BarCode"].ToString();
                    sqlString += "  AND PGI.BARCODE = :BARCODE  ";

                }

                if (cre.Properties["WorkStation"] + "" != "" && cre.Properties["WorkStation"] + "" != null)
                {
                    sqlString += "  AND PGI.WORKSTATION = :WORKSTATION  ";

                }

                if (cre.Properties["User"] + "" != "" && cre.Properties["User"] + "" != null)
                {
                    sqlString += "  AND U.USERCODE like :USERCODE  ";

                }
                sqlString += "  order by PGI.CREATETIME,PGI.BARCODE  ";

                OracleParameter[] parameters = new OracleParameter[]
                {
                    new OracleParameter(":datebeginMasterStar", OracleDbType.Date, cre.Properties["CreateTimeBegin"], ParameterDirection.Input),
                    new OracleParameter(":datebeginMasterEnd", OracleDbType.Date, cre.Properties["CreateTimeEnd"], ParameterDirection.Input),
                    new OracleParameter(":BARCODE", OracleDbType.Varchar2, cre.Properties["BarCode"], ParameterDirection.Input),
                    new OracleParameter(":WORKSTATION", OracleDbType.Varchar2, cre.Properties["WorkStation"], ParameterDirection.Input),
                    new OracleParameter(":USERCODE", OracleDbType.Varchar2, "%"+cre.Properties["User"]+"%", ParameterDirection.Input),
            };

                sre.Data = con.GetSqlResultToDs(sqlString, parameters);
                return sre;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (con.ConnState == ConnectionState.Open)
                {
                    con.Close();
                }
            }
        }
        #endregion

        #region 作业指导书配置
        /// <summary>
        /// 作业指导书配置
        /// </summary>
        /// <param name="sUserInfo"></param>
        /// <param name="cre"></param>
        /// <returns></returns>
        public static ServiceResultEntity GetProcedure(SUserInfo sUserInfo, ClientRequestEntity cre)
        {
            ServiceResultEntity sre = new ServiceResultEntity();
            IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString);

            try
            {
                con.Open();
                string sqlString = "";

                if (cre.Properties["onlyflag"].ToString() == "1")
                {
                    sqlString = " SELECT sd.procedureName,sd.procedureID,1 as AlreadyCheck" +
                "                          FROM TP_PC_SOPBOOKTYPEDATA sd\n" +
                "                        WHERE sd.soptypeid = :soptypeid";
                }
                else
                {
                    sqlString = "SELECT (SELECT 1\n" +
                "                          FROM TP_PC_SOPBOOKTYPEDATA sd\n" +
                "                        WHERE sd.procedurename = p.procedureName " +
                "                           and sd.soptypeid = :soptypeid) AS check1\n" +
                "                      ,p.procedureName as procedureName\n" +
                "                      ,p.procedureID\n" +
                "                  FROM tp_pc_procedure p\n" +
                "                 WHERE p.valueflag = '1'\n" +
                "                   AND p.accountid = :accountid";

                    if (!string.IsNullOrWhiteSpace(cre.Properties["ProcedureName"] + ""))
                    {
                        sqlString += " AND P.PROCEDURENAME LIKE :PROCEDURENAME";

                    }
                }



                OracleParameter[] parameters = new OracleParameter[]
                {
                    new OracleParameter(":accountid", OracleDbType.Int32, sUserInfo.AccountID, ParameterDirection.Input),
                    new OracleParameter(":soptypeid", OracleDbType.NVarchar2, cre.Properties["SOPTYPEID"], ParameterDirection.Input),
                    new OracleParameter(":PROCEDURENAME", OracleDbType.NVarchar2, "%"+cre.Properties["ProcedureName"]+"%", ParameterDirection.Input),

                };
                sre.Data = con.GetSqlResultToDs(sqlString, parameters);
                return sre;
            }
            catch (Exception ex)
            {

                throw ex;
            }
            finally
            {
                if (con.ConnState == ConnectionState.Open)
                {
                    con.Close();
                }
            }
        }

        /// <summary>
        /// 保存 作业指导书配置
        /// </summary>
        /// <param name="sUserInfo"></param>
        /// <returns></returns>
        public static ServiceResultEntity SaveSOPData(SUserInfo sUserInfo, ClientRequestEntity cre)
        {
            ServiceResultEntity sre = new ServiceResultEntity();
            int soptypeID = Convert.ToInt32(cre.Properties["SOPTYPEID"]);
            string soptype = cre.Properties["SOPTYPE"] + "";
            DataTable procedureTable = cre.Data.Tables[0];

            int returnRows = 0;
            IDBTransaction oracleTrConn = ClsDbFactory.CreateDBTransaction(DataBaseType.ORACLE, DataManager.ConnectionString);
            try
            {
                string sqlString = null;
                if (procedureTable != null && procedureTable.Rows.Count > 0)
                {
                    //先删除后插入
                    sqlString = "delete from TP_PC_SOPBOOKTYPEDATA where soptypeid=" + soptypeID;
                    oracleTrConn.ExecuteNonQuery(sqlString);
                    foreach (DataRow item in procedureTable.Rows)
                    {
                        sqlString = @"insert into TP_PC_SOPBOOKTYPEDATA 
                                (
                                    soptypeid,
                                    SOPTYPENAME,
                                    PROCEDUREID,
                                    PROCEDURENAME,
                                    VALUEFLAG
                                ) values
                                (   :soptypeid,
                                    :SOPTYPENAME,
                                    :PROCEDUREID,
                                    :PROCEDURENAME,
                                     1
                                )";
                        OracleParameter[] Paras = new OracleParameter[] {
                            new OracleParameter(":soptypeid",OracleDbType.Int32,soptypeID,ParameterDirection.Input),
                            new OracleParameter(":SOPTYPENAME",OracleDbType.Varchar2, soptype,ParameterDirection.Input),
                            new OracleParameter(":PROCEDUREID",OracleDbType.Int32,item["PROCEDUREID"],ParameterDirection.Input),
                            new OracleParameter(":PROCEDURENAME",OracleDbType.Varchar2, item["PROCEDURENAME"] + "",ParameterDirection.Input),
                        };
                        returnRows += oracleTrConn.ExecuteNonQuery(sqlString, Paras);
                    }
                }

                oracleTrConn.Commit();
            }
            catch (Exception ex)
            {
                if (oracleTrConn.ConnState == System.Data.ConnectionState.Open)
                {
                    oracleTrConn.Rollback();
                    oracleTrConn.Disconnect();
                }
                throw ex;
            }
            finally
            {
                if (oracleTrConn.ConnState == System.Data.ConnectionState.Open)
                {
                    oracleTrConn.Disconnect();
                }
            }
            return sre;
        }

        #endregion

        #region 分配到工序节点
        /// <summary>
        /// 分配到工序节点
        /// </summary>
        /// <param name="sUserInfo"></param>
        /// <param name="cre"></param>
        /// <returns></returns>
        public static ServiceResultEntity GetProcedureID(SUserInfo sUserInfo, ClientRequestEntity cre)
        {
            ServiceResultEntity sre = new ServiceResultEntity();
            IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString);

            try
            {
                string sqlString = "SELECT PU.PROCEDUREID,PU.NODENO,P.PROCEDURENAME,1 as AlreadyCheck\n" +
                "FROM TP_PC_ProcedureUser PU\n" +
                "LEFT JOIN  TP_PC_procedure P ON PU.PROCEDUREID = P.PROCEDUREID\n" +
                "WHERE PU.USERID = :userID";

                OracleParameter[] parameters = new OracleParameter[]
                {
                    new OracleParameter(":userID", OracleDbType.NVarchar2, cre.Properties["userID"], ParameterDirection.Input),
                };
                sre.Data = con.GetSqlResultToDs(sqlString, parameters);
                return sre;
            }
            catch (Exception ex)
            {

                throw ex;
            }
            finally
            {
                if (con.ConnState == ConnectionState.Open)
                {
                    con.Close();
                }
            }
        }

        /// <summary>
        /// 保存 分配到工序节点
        /// </summary>
        /// <param name="sUserInfo"></param>
        /// <returns></returns>
        public static ServiceResultEntity SaveProcedureData(SUserInfo sUserInfo, ClientRequestEntity cre)
        {
            ServiceResultEntity sre = new ServiceResultEntity();
            int userID = Convert.ToInt32(cre.Properties["UserID"]);
            DataTable procedureTable = cre.Data.Tables[0];

            int returnRows = 0;
            IDBTransaction oracleTrConn = ClsDbFactory.CreateDBTransaction(DataBaseType.ORACLE, DataManager.ConnectionString);
            try
            {
                string sqlString = null;
                if (procedureTable != null && procedureTable.Rows.Count > 0)
                {
                    //先删除后插入
                    sqlString = "delete from TP_PC_ProcedureUser where userid=" + userID;
                    oracleTrConn.ExecuteNonQuery(sqlString);
                    foreach (DataRow item in procedureTable.Rows)
                    {
                        sqlString = @"SELECT NODENO FROM TP_PC_procedure WHERE PROCEDUREID = " + item["PROCEDUREID"];
                        int nodeNo = int.Parse(oracleTrConn.GetSqlResultToStr(sqlString));

                        sqlString = @"insert into TP_PC_ProcedureUser 
                                (
                                    USERID, 
                                    PROCEDUREID,
                                    PRODUCTIONLINEID,
                                    NODENO,
                                    CREATEUSERID
                                ) values
                                (   :USERID, 
                                    :PROCEDUREID,
                                    1,
                                    :NODENO,
                                    :CREATEUSERID
                                )";
                        OracleParameter[] Paras = new OracleParameter[] {
                            new OracleParameter(":USERID",OracleDbType.Int32,userID,ParameterDirection.Input),
                            new OracleParameter(":PROCEDUREID",OracleDbType.Int32,item["PROCEDUREID"],ParameterDirection.Input),
                            new OracleParameter(":NODENO",OracleDbType.Varchar2, nodeNo,ParameterDirection.Input),
                            new OracleParameter(":CREATEUSERID",OracleDbType.Int32, sUserInfo.UserID,ParameterDirection.Input),
                        };
                        returnRows += oracleTrConn.ExecuteNonQuery(sqlString, Paras);
                    }
                }

                oracleTrConn.Commit();
            }
            catch (Exception ex)
            {
                if (oracleTrConn.ConnState == System.Data.ConnectionState.Open)
                {
                    oracleTrConn.Rollback();
                    oracleTrConn.Disconnect();
                }
                throw ex;
            }
            finally
            {
                if (oracleTrConn.ConnState == System.Data.ConnectionState.Open)
                {
                    oracleTrConn.Disconnect();
                }
            }
            return sre;
        }
        #endregion
    }
}