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

/*******************************************************************************
 * Copyright(c) 2014 DongkeSoft All rights reserved. / Confidential
 * 类的信息：
 *      1.程序名称：PCModuleLogic.cs
 *      2.功能描述：生产配置数据库访问类
 * 编辑履历：
 *      作者            日期                版本            修改内容
 *      陈冰            2014/09/3           1.00            新建
 *******************************************************************************/
using System;
using System.Collections.Generic;
using System.Data;
using System.Text;
using Dongke.IBOSS.PRD.Basics.BaseResources;
using Dongke.IBOSS.PRD.Basics.DataAccess;
using Dongke.IBOSS.PRD.Service.DataModels;
using Dongke.IBOSS.PRD.WCF.DataModels;
using Dongke.IBOSS.PRD.WCF.DataModels.ConfigModule;
using Oracle.ManagedDataAccess.Client;

namespace Dongke.IBOSS.PRD.Service.PCModuleLogic
{
    /// <summary>
    /// 生产配置数据库访问类
    /// </summary>
    public partial class PCModuleLogic
    {
        #region 生产线配置

        /// <summary>
        /// 获得生产线配置
        /// </summary>
        /// <param name="lineID">生产线ID</param>
        /// <returns>DataSet</returns>
        public static DataSet GetProductionLineDetail(int lineID)
        {
            IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString);
            try
            {
                con.Open();
                OracleParameter[] paras1 = new OracleParameter[]{
                    new OracleParameter("in_productionLineID",OracleDbType.Int32,lineID,ParameterDirection.Input),
                };

                string sqlText = @"SELECT * FROM TP_PC_ProductionLine WHERE ProductionLineID= :in_productionLineID";
                DataTable dataTable0 = con.GetSqlResultToDt(sqlText, paras1);
                sqlText = @"SELECT p.*,
										(
										SELECT
											listagg ( to_char( l.GOODSLEVELTYPEID ), ',' ) within GROUP ( ORDER BY l.GOODSLEVELTYPEID )
										FROM
											TP_PC_PROCEDUREGOODSLEVEL l
										WHERE
											l.PRODUCTIONLINEID = p.PRODUCTIONLINEID
											AND l.PROCEDUREID = p.PROCEDUREID
										) AS SemiGoodsLevel
							FROM
								TP_PC_PROCEDURE p
							WHERE p.valueflag='1' and
								p.productionLineID = :in_productionLineID ORDER BY NodeNo";
                DataTable dataTable1 = con.GetSqlResultToDt(sqlText, paras1);
                sqlText = @"SELECT * FROM TP_PC_ProcedureFlow pfl
  inner join   TP_PC_PROCEDURE p on p.procedureid = pfl.procedureid and p.valueflag='1'
WHERE pfl.ProductionLineID= :in_productionLineID";
                DataTable dataTable2 = con.GetSqlResultToDt(sqlText, paras1);
                sqlText = @"SELECT pro.*,g.GoodsCode,g.GoodsName,g.GoodsSpecification,g.GoodsModel,type.GoodsTypeName
  FROM TP_PC_ProcedureGoods pro
  inner join   TP_PC_PROCEDURE p on p.procedureid = pro.procedureid and p.valueflag='1'
   left join TP_MST_Goods g on pro.GoodsID=g.GoodsID
   left join TP_MST_GoodsType type on type.GoodsTypeID=g.GoodsTypeID
   where pro.ProductionLineID= :in_productionLineID";
                DataTable dataTable3 = con.GetSqlResultToDt(sqlText, paras1);
                sqlText = @"SELECT proUser.*,tuser.usercode,tuser.username 
  FROM TP_PC_ProcedureUser proUser 
  inner join   TP_PC_PROCEDURE p on p.procedureid = proUser.procedureid and p.valueflag='1'
  inner join tp_mst_user tuser on tuser.userid=prouser.userid 
  WHERE proUser.ProductionLineID= :in_productionLineID";
                DataTable dataTable4 = con.GetSqlResultToDt(sqlText, paras1);
                sqlText = @"SELECT proDefect.*,def.DefectCode,def.DefectName 
  FROM TP_PC_ProcedureDefect proDefect 
  inner join   TP_PC_PROCEDURE p on p.procedureid = proDefect.procedureid and p.valueflag='1'
  inner join tp_mst_Defect def on def.DefectID = proDefect.DefectID 
  WHERE proDefect.ProductionLineID= :in_productionLineID";
                DataTable dataTable5 = con.GetSqlResultToDt(sqlText, paras1);
                sqlText = @"SELECT dj.*,defect.defectcode, defect.defectName,jobs.jobsCode,jobs.jobsName 
   FROM TP_PC_DefectProcedureJobs dj
  inner join   TP_PC_PROCEDURE p on p.procedureid = dj.procedureid and p.valueflag='1'
                            inner join tp_mst_defect defect
                                  on defect.defectid=dj.defectid
                            inner join tp_mst_jobs jobs
                                  on jobs.jobsid=dj.jobsid
              WHERE dj.ProductionLineID= :in_productionLineID";
                DataTable dataTable6 = con.GetSqlResultToDt(sqlText, paras1);
                sqlText = @"SELECT prokiln.*,kiln.kilncode,kiln.kilnname
   FROM tp_pc_procedurekiln prokiln 
  inner join   TP_PC_PROCEDURE p on p.procedureid = prokiln.procedureid and p.valueflag='1'
   inner join tp_mst_kiln kiln on kiln.kilnid=prokiln.kilnid
    WHERE prokiln.ProductionLineID= :in_productionLineID";
                DataTable dataTable7 = con.GetSqlResultToDt(sqlText, paras1);

                DataSet ds1 = new DataSet();
                ds1.Tables.Add(dataTable0);
                ds1.Tables.Add(dataTable1);
                ds1.Tables.Add(dataTable2);
                ds1.Tables.Add(dataTable3);
                ds1.Tables.Add(dataTable4);
                ds1.Tables.Add(dataTable5);
                ds1.Tables.Add(dataTable6);
                ds1.Tables.Add(dataTable7);
                // 命名 方便客户端取值
                ds1.Tables[0].TableName = "lineTable";
                ds1.Tables[1].TableName = "procedureTable";
                ds1.Tables[2].TableName = "procedureFlowTable";
                ds1.Tables[3].TableName = "procedureGoodsTable";
                ds1.Tables[4].TableName = "procedureUserTable";
                ds1.Tables[5].TableName = "procedureDefectTable";
                ds1.Tables[6].TableName = "defectProcedureJobsTable";
                ds1.Tables[7].TableName = "procedureKilnTable";

                return ds1;
                /*

                OracleParameter[] paras = new OracleParameter[]{
                    new OracleParameter("productionLineID",OracleDbType.Int32,lineID,ParameterDirection.Input),
                    new OracleParameter("rs_line",OracleDbType.RefCursor,ParameterDirection.Output),
                    new OracleParameter("rs_procedure",OracleDbType.RefCursor,ParameterDirection.Output),
                    new OracleParameter("rs_procedureFlow",OracleDbType.RefCursor,ParameterDirection.Output),
                    new OracleParameter("rs_procedureGoods",OracleDbType.RefCursor,ParameterDirection.Output),
                    new OracleParameter("rs_procedureUser",OracleDbType.RefCursor,ParameterDirection.Output),
                    new OracleParameter("rs_procedureDefect",OracleDbType.RefCursor,ParameterDirection.Output),
                    new OracleParameter("rs_defectProcedureJobs",OracleDbType.RefCursor,ParameterDirection.Output),
                    new OracleParameter("rs_procedureKiln",OracleDbType.RefCursor,ParameterDirection.Output),
                };
                DataSet ds = con.ExecStoredProcedure("PRO_PC_GetProductionLineDetail", paras);
                // 命名 方便客户端取值
                ds.Tables[0].TableName = "lineTable";
                ds.Tables[1].TableName = "procedureTable";
                ds.Tables[2].TableName = "procedureFlowTable";
                ds.Tables[3].TableName = "procedureGoodsTable";
                ds.Tables[4].TableName = "procedureUserTable";
                ds.Tables[5].TableName = "procedureDefectTable";
                ds.Tables[6].TableName = "defectProcedureJobsTable";
                ds.Tables[7].TableName = "procedureKilnTable";
                return ds;
                 */
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (con.ConnState == ConnectionState.Open)
                {
                    con.Close();
                }
            }
        }

        /// <summary>
        /// 查询生产线配置
        /// </summary>
        /// <param name="productionLine">生产线实体类</param>
        /// <param name="sUserInfo">用户基本信息</param>
        /// <returns>DataSet</returns>
        public static DataSet SearchProductionLine(ProductionLineEntity productionLine, SUserInfo sUserInfo)
        {
            IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString);
            try
            {
                con.Open();
                string str_vauleFlags = null;
                if (productionLine.ValueFlags != null && productionLine.ValueFlags.Length > 0)
                {
                    foreach (var item in productionLine.ValueFlags)
                    {
                        str_vauleFlags += "," + item;
                    }
                    str_vauleFlags = str_vauleFlags.Substring(1);
                }
                OracleParameter[] paras = new OracleParameter[]{
                    new OracleParameter("in_productionLineID",OracleDbType.Int32,productionLine.ProductionLineID,ParameterDirection.Input),
                    new OracleParameter("in_productionLineCode",OracleDbType.NVarchar2,productionLine.ProductionLineCode,ParameterDirection.Input),
                    new OracleParameter("in_productionLineName",OracleDbType.NVarchar2,productionLine.ProductionLineName,ParameterDirection.Input),
                    new OracleParameter("in_remarks",OracleDbType.NVarchar2,productionLine.Remarks,ParameterDirection.Input),
                    new OracleParameter("in_accountID",OracleDbType.Int32,sUserInfo.AccountID,ParameterDirection.Input),
                    new OracleParameter("in_valueFlags",OracleDbType.NVarchar2,str_vauleFlags,ParameterDirection.Input),
                    new OracleParameter("rs_result",OracleDbType.RefCursor,ParameterDirection.Output),
                    new OracleParameter("rs_resultDetail",OracleDbType.RefCursor,ParameterDirection.Output),
                };
                /*
                OracleParameter[] paras = new OracleParameter[]{
                    new OracleParameter("productionLineID",OracleDbType.Int32,productionLine.ProductionLineID,ParameterDirection.Input),
                    new OracleParameter("productionLineCode",OracleDbType.NVarchar2,productionLine.ProductionLineCode,ParameterDirection.Input),
                    new OracleParameter("productionLineName",OracleDbType.NVarchar2,productionLine.ProductionLineName,ParameterDirection.Input),
                    new OracleParameter("remarks",OracleDbType.NVarchar2,productionLine.Remarks,ParameterDirection.Input),
                    new OracleParameter("accountID",OracleDbType.Int32,sUserInfo.AccountID,ParameterDirection.Input),
                    new OracleParameter("valueFlags",OracleDbType.NVarchar2,str_vauleFlags,ParameterDirection.Input),
                    new OracleParameter("rs_result",OracleDbType.RefCursor,ParameterDirection.Output),
                    new OracleParameter("rs_resultDetail",OracleDbType.RefCursor,ParameterDirection.Output),
                };
                */
                DataSet ds = con.ExecStoredProcedure("PRO_PC_SearchProductionLine", paras);
                return ds;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (con.ConnState == ConnectionState.Open)
                {
                    con.Close();
                }
            }
        }

        #endregion

        #region 班次配置

        /// <summary>
        /// 获取班次配置信息
        /// </summary>
        /// <param name="searchEntity">班次配配置查询实体</param>
        /// <param name="sUserInfo">用户基本信息</param>
        /// <returns>DataSet</returns>
        public static DataSet SearchClassesSetting(SearchClassesSettingEntity searchEntity, SUserInfo sUserInfo)
        {
            IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString);
            try
            {
                con.Open();
                OracleParameter[] paras = new OracleParameter[]{
                    new OracleParameter("in_accountId",OracleDbType.Int32,sUserInfo.AccountID,ParameterDirection.Input),
                    new OracleParameter("in_userid",OracleDbType.Int32,searchEntity.UserID,ParameterDirection.Input),
                    new OracleParameter("in_userCode",OracleDbType.Varchar2,searchEntity.UserCode,ParameterDirection.Input),
                    new OracleParameter("in_startAccountDate",OracleDbType.Date,searchEntity.StartAccountDate,ParameterDirection.Input),
                    new OracleParameter("in_endAccountDate",OracleDbType.Date,searchEntity.EndAccountDate,ParameterDirection.Input),
                    new OracleParameter("in_remarks",OracleDbType.Varchar2,searchEntity.Remarks,ParameterDirection.Input),
                    new OracleParameter("in_valueFlag",OracleDbType.Int32,searchEntity.ValueFlag,ParameterDirection.Input),
                    new OracleParameter("out_result",OracleDbType.RefCursor, ParameterDirection.Output),
                };
                DataSet ds = con.ExecStoredProcedure("PRO_PC_SearchClassesSetting", paras);
                return ds;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (con.ConnState == ConnectionState.Open)
                {
                    con.Close();
                }
            }
        }

        /// <summary>
        /// 获取指定班次配置明细信息
        /// </summary>
        /// <param name="classesSettingID">班次配置Id</param>
        /// <param name="sUserInfo">用户基本信息</param>
        /// <returns>DataSet</returns>
        /// <remarks>
        /// 作者                  日期                  内容
        /// 冯雪                  2014-9-23       新建
        /// </remarks>
        public static DataSet SearchClassesDetail(int classesSettingID, SUserInfo sUserInfo)
        {
            IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString);
            try
            {
                con.Open();
                string strSql = " SELECT classesDetail.ClassesSettingID,classesDetail.UserID,classesDetail.userCode,"
                                    + " classesDetail.StaffID,classesDetail.Createtime,"
                    //+ " classesDetail.UjobsId jobs,classesDetail.SjobsId,classesDetail.Remarks,classesDetail.CreateUserID,"
                                    + " classesDetail.UjobsId UjobsId,classesDetail.SjobsId jobs,classesDetail.Remarks,classesDetail.CreateUserID,"
                                    + " classesDetail.UpdateTime,MUser.Username mUsername,CUser.Username createUserName,"
                                    + " Jobs.Jobsname,Muser.Organizationid,Org.Organizationname,Staff.Staffname,Staff.Staffcode,"
                                    + " status.staffstatusname AS staffStatusName,classesDetail.staffStatus,UJobs.JobsName as UJobsName,classesDetail.Ujobsid as UJobsId "
                                    + " FROM TP_PC_ClassesDetail classesDetail "
                                    + " LEFT JOIN TP_MST_User MUser ON MUser.UserId = classesDetail.UserID "
                                    + " LEFT JOIN TP_MST_User CUser ON CUser.UserId = classesDetail.CreateUserID "
                                    + " LEFT JOIN TP_MST_Jobs Jobs ON  Jobs.Jobsid = classesDetail.SjobsId "
                                    + " LEFT JOIN TP_MST_Organization Org ON Org.Organizationid = Muser.Organizationid "
                                    + " LEFT JOIN TP_HR_Staff Staff ON staff.staffid = classesDetail.Staffid "
                                    + " LEFT JOIN TP_MST_UserStaff userStaff ON classesDetail.STAFFID = userStaff.Staffid and classesDetail.Userid = userStaff.UserId"
                                    + " LEFT JOIN TP_MST_JOBS UJobs ON classesDetail.Ujobsid = UJobs.Jobsid"
                                    + " Inner JOIN TP_SYS_StaffStatus status on classesDetail.Staffstatus = status.staffstatusid"
                                    + " WHERE classesDetail.AccountID = :accountID "
                                    + " AND classesDetail.ClassesSettingID = :classesSettingID ";
                OracleParameter[] paras = new OracleParameter[]{
                    new OracleParameter(":accountID",OracleDbType.Int32,sUserInfo.AccountID,ParameterDirection.Input),
                    new OracleParameter(":classesSettingID",OracleDbType.Int32,classesSettingID,ParameterDirection.Input),
                };
                DataSet ds = con.GetSqlResultToDs(strSql, paras);
                return ds;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (con.ConnState == ConnectionState.Open)
                {
                    con.Close();
                }
            }
        }

        #endregion

        /// <summary>
        /// 生产配置删除节点时，此节点是否在产
        /// </summary>
        /// <param name="procedureID">工序ID</param>
        /// <returns>bool</returns>
        /// <remarks>
        /// </remarks>
        public static bool GetAllowDelete(int procedureID)
        {
            IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString);
            try
            {
                con.Open();
                string strSql = " select 1 from TP_PM_InProduction where FlowProcedureID=:procedureID";
                OracleParameter[] paras = new OracleParameter[]{
                    new OracleParameter(":procedureID",OracleDbType.Int32,procedureID,ParameterDirection.Input),
                };
                DataSet ds = con.GetSqlResultToDs(strSql, paras);
                if (ds != null && ds.Tables[0].Rows.Count > 0) // 有在产信息
                {
                    return true;
                }
                return false;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (con.ConnState == ConnectionState.Open)
                {
                    con.Close();
                }
            }
        }


        /// <summary>
        /// 取得FPM2105画面（注浆登记）的查询数据
        /// </summary>
        /// <param name="ids">多个注浆id（用[,]分开）</param>
        /// <param name="user">登录用户信息</param>
        /// <returns>查询结果</returns>
        public static ServiceResultEntity GetFPM2105Data(GroutingDailyEntity se, SUserInfo user)
        {
            IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString);
            try
            {
                con.Open();
                List<OracleParameter> parameters = new List<OracleParameter>();
                StringBuilder sql = new StringBuilder(GetFPM2105SQL());
                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 (se.BarCode != null)
                    {
                        sql.Append(" AND GDD.BARCODE = :barcode");
                        parameters.Add(new OracleParameter(":barcode", OracleDbType.Char,se.BarCode, ParameterDirection.Input));
                    }

                    // 成型线ID
                    if (se.GroutingLineID.HasValue)
                    {
                        sql.Append(" AND GD.GroutingLineID = :GroutingLineID");
                        parameters.Add(new OracleParameter(":GroutingLineID", OracleDbType.Int32, se.GroutingLineID.Value, ParameterDirection.Input));
                    }

                    // 注浆日期
                    if (se.GroutingDate.HasValue)
                    {
                        sql.Append(" AND GD.GroutingDate = :GroutingDate");
                        parameters.Add(new OracleParameter(":GroutingDate", OracleDbType.Date, se.GroutingDate.Value.Date, ParameterDirection.Input));

                        //sql.Append(" AND GD.GroutingDate <= :GroutingDateEnd");
                        //parameters.Add(new OracleParameter(":GroutingDateEnd", OracleDbType.Date, se.GroutingDate.Value.AddDays(1).AddMinutes(-1), ParameterDirection.Input));
                    }

                    // 注浆批次
                    if (se.GroutingBatchNo.HasValue)
                    {
                        sql.Append(" AND GDD.GroutingBatchNo = :GroutingBatchNo");
                        parameters.Add(new OracleParameter(":GroutingBatchNo", OracleDbType.Int32, se.GroutingBatchNo.Value, ParameterDirection.Input));
}
                    
                }

                sql.Append(" ORDER BY GDD.GroutingDate, GDD.GroutingLineCode, GDD.GroutingDailyID, GDD.GroutingMouldCode");
                DataTable data = con.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);
                return sre;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (con.ConnState == ConnectionState.Open)
                {
                    con.Close();
                }
            }
        }

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