IMEX.mes.wcf/DK.Service/PublicModuleService/PublicModuleLogic.cs

/*******************************************************************************
 * Copyright(c) 2014 DongkeSoft All rights reserved. / Confidential
 * 类的信息：
 *      1.程序名称：PMModuleLogic.cs
 *      2.功能描述：生产管理服务端业务逻辑
 * 编辑履历：
 *      作者            日期                版本            修改内容
 *      陈晓野          2015/03/21          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.WCF.DataModels;
using Oracle.ManagedDataAccess.Client;

namespace Dongke.IBOSS.PRD.Service.PublicModuleService
{
    /// <summary>
    /// 生产管理服务端业务逻辑
    /// </summary>
    public static class PublicModuleLogic
    {
        /*
        /// <summary>
        /// 取得FP00002画面（工号产量质量分析表）的查询数据
        /// </summary>
        /// <param name="user">登录用户信息</param>
        /// <param name="se">查询条件</param>
        /// <returns>查询结果</returns>
        public static ServiceResultEntity GetFP00002Data(int accountID, int rptSProcedureID, string usercode, DateTime date)
        {
            //if(string.IsNullOrWhiteSpace(usercode))
            //{
            //    ServiceResultEntity sre = new ServiceResultEntity();
            //    sre.Status = Constant.ServiceResultStatus.NoSearchResults;
            //    return sre;
            //}
            IDBConnection conn = null;
            try
            {
                conn = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString);
                List<OracleParameter> parameters = new List<OracleParameter>();
                date = date.Date;
                //parameters.Add(new OracleParameter(":AccountID", OracleDbType.Int32, user.AccountID, ParameterDirection.Input));
                parameters.Add(new OracleParameter(":AccountID", OracleDbType.Int32, accountID, ParameterDirection.Input));
                parameters.Add(new OracleParameter(":CreateTimeStart", OracleDbType.Date, date, ParameterDirection.Input));
                parameters.Add(new OracleParameter(":CreateTimeEnd", OracleDbType.Date, date.AddDays(1), ParameterDirection.Input));
                parameters.Add(new OracleParameter(":Usercode", OracleDbType.NVarchar2, usercode, ParameterDirection.Input));
                // 产量
                string sqlString = "\n" +
                "select --qdgroup.gid,\n" +
                "       decode(qdgroup.gid, 7, '总计', 0, qdgroup.productionlinename, '--') productionlinename,\n" +
                "       decode(qdgroup.gid, 3, '合计', 0, qdgroup.procedurename, '--') procedurename,\n" +
                "       decode(qdgroup.gid, 1, '小计', 0, qdgroup.goodstypename2, '--') goodstypename2,\n" +
                "       --decode(qdgroup.gid, 0, qdgroup.usercode, '--') usercode,\n" +
                "       decode(qdgroup.gid, 0, qdgroup.goodstypename, '--') goodstypename,\n" +
                "       decode(qdgroup.gid, 0, qdgroup.goodscode, '--') goodscode,\n" +
                "       qdgroup.production\n" +
                "  from (select grouping_id(pcpl.productionlinename,\n" +
                "                           pcp.procedurename,\n" +
                "                           mstgoodstype2.goodstypename,\n" +
                "                           --qd.usercode,\n" +
                "                           mstgoods.goodscode) gid,\n" +
                "               pcpl.productionlinename,\n" +
                "               pcp.procedurename,\n" +
                "               mstgoodstype2.goodstypename goodstypename2,\n" +
                "               --qd.usercode,\n" +
                "               mstgoodstype.goodstypename,\n" +
                "               mstgoods.goodscode,\n" +
                "               sum(qd.production) production\n" +
                "          from (select --pd.barcode,\n" +
                "                 pd.productionlineid,\n" +
                "                 pd.procedureid,\n" +
                "                 pd.goodsid,\n" +
                "                 --pd.userid,\n" +
                "                 --pd.usercode,\n" +
                "                 1 production\n" +
                "                  from tp_pm_productiondata pd\n" +
                "                 where pd.valueflag = '1'\n" +
                "                   and ((pd.modeltype <> 5) or\n" +
                "                       (pd.modeltype = 5 and pd.SpecialRepairFlag = '0'))\n" +
                "                   and pd.Usercode = :Usercode\n" +
                "                   and pd.AccountID = :AccountID\n" +
                "                   AND pd.createtime >= :CreateTimeStart\n" +
                "                   AND pd.createtime < :CreateTimeEnd) qd\n" +
                "          left join tp_pc_productionline pcpl\n" +
                "            on pcpl.productionlineid = qd.productionlineid\n" +
                "          left join tp_pc_procedure pcp\n" +
                "            on pcp.procedureid = qd.procedureid\n" +
                "          left join tp_mst_goods mstgoods\n" +
                "            on mstgoods.goodsid = qd.goodsid\n" +
                "          left join tp_mst_goodstype mstgoodstype\n" +
                "            on mstgoodstype.goodstypeid = mstgoods.goodstypeid\n" +
                "          left join tp_mst_goodstype mstgoodstype2\n" +
                "            on mstgoodstype2.goodstypecode =\n" +
                "               substr(mstgoodstype.goodstypecode, 0, 6)\n" +
                "           and mstgoodstype.AccountID = mstgoodstype2.AccountID\n" +
                "         group by grouping sets((pcpl.productionlinename,\n" +
                "                                 pcp.procedurename,\n" +
                "                                 mstgoodstype2.goodstypename,\n" +
                "                                 --qd.usercode,\n" +
                "                                 mstgoodstype.goodstypename,\n" +
                "                                 mstgoods.goodscode),\n" +
                "                                (pcpl.productionlinename,\n" +
                "                                 pcp.procedurename,\n" +
                "                                 mstgoodstype2.goodstypename),\n" +
                "                                (pcpl.productionlinename,\n" +
                "                                 pcp.procedurename),\n" +
                "                                pcpl.productionlinename)\n" +
                "         order by pcpl.productionlinename,\n" +
                "                  pcp.procedurename,\n" +
                "                  mstgoodstype2.goodstypename,\n" +
                "                  --qd.usercode,\n" +
                "                  mstgoodstype.goodstypename,\n" +
                "                  mstgoods.goodscode) qdgroup";

                DataTable data = conn.GetSqlResultToDt(sqlString, 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);

                parameters.Add(new OracleParameter(":in_rptSprocedureId", OracleDbType.Int32, rptSProcedureID, ParameterDirection.Input));

                string sqlString1 = "\n" +
                "select qdgroup.gid,\n" +
                "       decode(qdgroup.gid, 0, qdgroup.productionlinename, '--') productionlinename,\n" +
                "       decode(qdgroup.gid, 7, '总计', 0, qdgroup.procedurename, '--') procedurename,\n" +
                "       decode(qdgroup.gid, 3, '合计', 0, qdgroup.kilncode, '--') kilncode,\n" +
                "       decode(qdgroup.gid, 1, '小计', 0, qdgroup.goodstypename2, '--') goodstypename2,\n" +
                "       --decode(qdgroup.gid, 0, qdgroup.usercode, '--') usercode,\n" +
                "       decode(qdgroup.gid, 0, qdgroup.goodstypename, '--') goodstypename,\n" +
                "       decode(qdgroup.gid, 0, qdgroup.goodscode, '--') goodscode,\n" +
                "       qdgroup.OutKilnCount,\n" +
                "       qdgroup.GoodCount,\n" +
                "       qdgroup.GoodCount,\n" +
                "       to_char((qdgroup.GoodCount / qdgroup.OutKilnCount) * 100, '990.00') || '%' GoodPercent,\n" +
                "       to_char(((qdgroup.GoodCount+qdgroup.BadCount) / qdgroup.OutKilnCount) * 100, '990.00') || '%' QualifiedPercent,\n" +
                "       qdgroup.SubstandardCount,\n" +
                "       to_char((qdgroup.SubstandardCount / qdgroup.OutKilnCount) * 100,\n" +
                "               '990.00') || '%' SubstandardPercent,\n" +
                "       qdgroup.GoodCount+qdgroup.BadCount Qualified,\n" +
                "       to_char((qdgroup.BadCount / qdgroup.OutKilnCount) * 100, '990.00') || '%' BadPercent,\n" +
                "       qdgroup.ReFireCount,\n" +
                "       to_char((qdgroup.ReFireCount / qdgroup.OutKilnCount) * 100,\n" +
                "               '990.00') || '%' ReFirePercent\n" +
                "  from (select grouping_id(pcpl.productionlinename,\n" +
                "                           pcp.procedurename,\n" +
                "                           mstkiln.kilncode,\n" +
                "                           mstgoodstype2.goodstypename,\n" +
                "                           --qd.usercode,\n" +
                "                           mstgoods.goodscode) gid,\n" +
                "               pcpl.productionlinename,\n" +
                "               pcp.procedurename,\n" +
                "               mstkiln.kilncode,\n" +
                "               mstgoodstype2.goodstypename goodstypename2,\n" +
                "               --qd.usercode,\n" +
                "               mstgoodstype.goodstypename,\n" +
                "               mstgoods.goodscode,\n" +
                "               sum(qd.OutKilnCount) OutKilnCount,\n" +
                "               sum(qd.GoodCount) GoodCount,\n" +
                "               sum(qd.SubstandardCount) SubstandardCount,\n" +
                "               sum(qd.BadCount) BadCount,\n" +
                "               sum(qd.ReFireCount) ReFireCount\n" +
                "          from (select --pdbc.barcode,\n" +
                "                 pdbc.productionlineid,\n" +
                "                 pdata.procedureid,\n" +
                "                 --pdata.usercode,\n" +
                "                 pdbc.goodsid,\n" +
                "                 pdbc.kilnid,\n" +
                "                 1 OutKilnCount,\n" +
                "                 case\n" +
                "                   when defect.defectprocedureid is not null and glt.goodsleveltypeid in (5, 6, 7) then\n" +
                "                    0\n" +
                "                   else\n" +
                "                    1\n" +
                "                 end GoodCount,\n" +
                "                 case\n" +
                "                   when defect.defectprocedureid is not null and glt.goodsleveltypeid = 7 then\n" +
                "                    1\n" +
                "                   else\n" +
                "                    0\n" +
                "                 end SubstandardCount,\n" +
                "                 case\n" +
                "                   when defect.defectprocedureid is not null and glt.goodsleveltypeid = 6 then\n" +
                "                    1\n" +
                "                   else\n" +
                "                    0\n" +
                "                 end ReFireCount,\n" +
                "                 case\n" +
                "                   when defect.defectprocedureid is not null and glt.goodsleveltypeid = 5 then\n" +
                "                    1\n" +
                "                   else\n" +
                "                    0\n" +
                "                 end BadCount\n" +
                "                  from (select distinct pd.barcode,\n" +
                "                                        pd.productionlineid,\n" +
                "                                        pd.goodsid,\n" +
                "                                        pd.kilnid,\n" +
                "                                        pd.kilncarbatchno\n" +
                "                          from tp_pm_productiondata pd\n" +
                "                         where pd.valueflag = '1'\n" +
                "                           and pd.procedureid = :in_rptSprocedureId \n" +
                "                           and pd.AccountID = :AccountID\n" +
                "                           and pd.createtime >= :CreateTimeStart\n" +
                "                           and pd.createtime < :CreateTimeEnd\n" +
                "                        ) pdbc\n" +
                "                 inner join (select pd.barcode,\n" +
                "                                    --pd.userid,\n" +
                "                                    --pd.usercode,\n" +
                "                                    pd.procedureid\n" +
                "                               from tp_pm_productiondata pd\n" +
                "                              where pd.valueflag = '1'\n" +
                "                                and pd.AccountID = :AccountID\n" +
                "                                and pd.usercode = :Usercode\n" +
                    // 公坯、干补不算交坯工序的质量。
                    //"                                and ((pd.modeltype <> 5) or (pd.modeltype = 5 and pd.IsPublicBody = '0' and pd.SpecialRepairFlag = '0')) \n" +
                    // 干补不算交坯工序的质量。
                "                                and ((pd.modeltype <> 5) or (pd.modeltype = 5 and pd.SpecialRepairFlag = '0')) \n" +
                "                        ) pdata\n" +
                "                    on pdata.barcode = pdbc.barcode\n" +
                "                  left join (select kcbc.barcode, kcbc.kilncarbatchno, kcbc.goodsleveltypeid, kcbc.productiondataid\n" +
                "                              from (select pd.barcode,\n" +
                "                                           pd.kilncarbatchno,\n" +
                "                                           pd.goodsleveltypeid,\n" +
                "                                           pd.productiondataid,\n" +
                "                                           ROW_NUMBER() OVER(PARTITION BY pd.barcode, pd.kilncarbatchno ORDER BY pd.productiondataid desc) AS dataid\n" +
                "                                      from tp_pm_productiondata pd\n" +
                "                                     where pd.valueflag = '1'\n" +
                "                                       and pd.AccountID = :AccountID\n" +
                "                                       and length(pd.kilncarbatchno) > 0\n" +
                "                                       AND pd.createtime >= :CreateTimeStart\n" +
                "                                       and pd.modeltype = -1) kcbc\n" +
                "                             where kcbc.dataid = 1) glt\n" +
                "                    on pdbc.kilncarbatchno = glt.kilncarbatchno\n" +
                "                   and pdbc.barcode = glt.barcode\n" +
                "                  left join (select distinct productiondataid, defectprocedureid" +
                "                               from tp_pm_defect def " +
                "                              where def.valueflag='1'" +
                "                                and def.AccountID = :AccountID\n" +
                "                                AND def.createtime >= :CreateTimeStart) defect\n" +
                "                    on defect.productiondataid = glt.productiondataid\n" +
                "                   and pdata.procedureid = defect.defectprocedureid) qd\n" +
                "         inner join tp_pc_productionline pcpl\n" +
                "            on pcpl.productionlineid = qd.productionlineid\n" +
                "         inner join tp_pc_procedure pcp\n" +
                "            on pcp.procedureid = qd.procedureid\n" +
                "         inner join tp_mst_kiln mstkiln\n" +
                "            on mstkiln.kilnid = qd.kilnid\n" +
                "         inner join tp_mst_goods mstgoods\n" +
                "            on mstgoods.goodsid = qd.goodsid\n" +
                "         inner join tp_mst_goodstype mstgoodstype\n" +
                "            on mstgoodstype.goodstypeid = mstgoods.goodstypeid\n" +
                "         inner join tp_mst_goodstype mstgoodstype2\n" +
                "            on mstgoodstype2.goodstypecode =\n" +
                "               substr(mstgoodstype.goodstypecode, 0, 6)\n" +
                "           and mstgoodstype.AccountID = mstgoodstype2.AccountID\n" +
                "         group by grouping sets((pcpl.productionlinename,\n" +
                "                                 pcp.procedurename,\n" +
                "                                 mstkiln.kilncode,\n" +
                "                                 mstgoodstype2.goodstypename,\n" +
                "                                 --qd.usercode,\n" +
                "                                 mstgoodstype.goodstypename,\n" +
                "                                 mstgoods.goodscode),\n" +
                "                                (pcpl.productionlinename,\n" +
                "                                 pcp.procedurename,\n" +
                "                                 mstkiln.kilncode,\n" +
                "                                 mstgoodstype2.goodstypename),\n" +
                "                                (pcpl.productionlinename,\n" +
                "                                 pcp.procedurename,\n" +
                "                                 mstkiln.kilncode),\n" +
                "                                 (pcpl.productionlinename, pcp.procedurename))\n" +
                "         order by pcpl.productionlinename,\n" +
                "                  pcp.procedurename,\n" +
                "                  mstkiln.kilncode,\n" +
                "                  mstgoodstype2.goodstypename,\n" +
                "                  --qd.usercode,\n" +
                "                  mstgoodstype.goodstypename,\n" +
                "                  mstgoods.goodscode\n" +
                "       ) qdgroup\n";
                DataTable data1 = conn.GetSqlResultToDt(sqlString1, parameters.ToArray());
                if (data1 == null || data1.Rows.Count == 0)
                {
                    sre.Status = Constant.ServiceResultStatus.NoSearchResults;
                    return sre;
                }
                sre.Data.Tables.Add(data1);
                return sre;

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

        /// <summary>
        /// 取得FP00002画面（工号产量质量分析表）的查询数据
        /// </summary>
        /// <param name="user">登录用户信息</param>
        /// <param name="se">查询条件</param>
        /// <returns>查询结果</returns>
        public static ServiceResultEntity GetFP00002Data(int accountID, string usercode, DateTime date)
        {
            IDBTransaction tran = null;
            try
            {
                tran = ClsDbFactory.CreateDBTransaction(DataBaseType.ORACLE, DataManager.ConnectionString);
                tran.Connect();
                // 只限成型工查询
                string sqlString = "select u.userid, u.isgroutingworker from tp_mst_user u where u.AccountID = :AccountID and u.usercode = :usercode";
                OracleParameter[] paraUser = new OracleParameter[]
                {
                    new OracleParameter(":AccountID", OracleDbType.Int32, accountID, ParameterDirection.Input),
                    new OracleParameter(":usercode", OracleDbType.NVarchar2, usercode, ParameterDirection.Input),
                };
                DataTable dt = tran.GetSqlResultToDt(sqlString, paraUser);
                if (dt == null || dt.Rows.Count == 0)
                {
                    ServiceResultEntity sreUser = new ServiceResultEntity();
                    sreUser.Status = Constant.ServiceResultStatus.Other;
                    sreUser.Message = "系统中不存在此成型工号";
                    return sreUser;
                }
                if ("1" != (dt.Rows[0]["isgroutingworker"] + ""))
                {
                    ServiceResultEntity sreUser = new ServiceResultEntity();
                    sreUser.Status = Constant.ServiceResultStatus.Other;
                    sreUser.Message = "此工号不是成型工号";
                    return sreUser;
                }

                DateTime month = new DateTime(date.Year, date.Month, 1);
                date = DateTime.Now.Date;
                DateTime month1 = new DateTime(date.Year, date.Month, 1).AddMonths(-1);
                if (month < month1)
                {
                    month = month1;
                }

                OracleParameter[] parameters = new OracleParameter[]
                {
                    new OracleParameter("in_AccountID", OracleDbType.Int32, accountID, ParameterDirection.Input),
                    new OracleParameter("in_UserCode", OracleDbType.NVarchar2, usercode, ParameterDirection.Input),
                    //new OracleParameter("in_OutKilnProcedureID", OracleDbType.Int32, rptSProcedureID, ParameterDirection.Input),
                    new OracleParameter("in_DateBegin", OracleDbType.Date, date, ParameterDirection.Input),
                    new OracleParameter("in_DateEnd", OracleDbType.Date, date.AddDays(1).AddSeconds(-1), ParameterDirection.Input),
                    new OracleParameter("in_MonthBegin", OracleDbType.Date, month, ParameterDirection.Input),
                    new OracleParameter("in_NextMonth", OracleDbType.Date, month.AddMonths(1), ParameterDirection.Input),
                    new OracleParameter("out_DataD", OracleDbType.RefCursor, null, ParameterDirection.Output),
                    new OracleParameter("out_DataM", OracleDbType.RefCursor, null, ParameterDirection.Output),
                    new OracleParameter("out_DataCC", OracleDbType.RefCursor, null, ParameterDirection.Output),
                    new OracleParameter("out_DataNS", OracleDbType.RefCursor, null, ParameterDirection.Output),
                    new OracleParameter("out_DataSC", OracleDbType.RefCursor, null, ParameterDirection.Output),
                    new OracleParameter("out_DataRC", OracleDbType.RefCursor, null, ParameterDirection.Output),
                };

                DataSet data = tran.ExecStoredProcedure("PRO_P2_GetUserProductionInfo", parameters);

                ServiceResultEntity sre = new ServiceResultEntity();
                if (data != null)
                {
                    //,out_DataD             OUT SYS_REFCURSOR -- 总体信息
                    //,out_DataM             OUT SYS_REFCURSOR -- 月度信息
                    //,out_DataCC            OUT SYS_REFCURSOR -- 成检明细
                    //,out_DataNS            OUT SYS_REFCURSOR -- 后损明细
                    //,out_DataSC            OUT SYS_REFCURSOR -- 半检不合格
                    //,out_DataRC            OUT SYS_REFCURSOR -- 返工合格
                    data.Tables[0].TableName = "DataD";
                    data.Tables[1].TableName = "DataM";
                    data.Tables[2].TableName = "DataCC";
                    data.Tables[3].TableName = "DataNS";
                    data.Tables[4].TableName = "DataSC";
                    data.Tables[5].TableName = "DataRC";
                }

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

        /// <summary>
        /// 取得报表数据源数据
        /// </summary>
        /// <param name="user">登录用户信息</param> 
        /// <returns>查询结果</returns>
        public static ServiceResultEntity GetRptProcedureModule(int accountid)
        {
            IDBConnection conn = null;
            try
            {
                conn = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString);
                string selSql = " select "
                            + " TP_MST_RptProcedure.Rptprocedureid,"
                            + " TP_MST_RptProcedure.Rptprocedurecode,"
                            + " TP_MST_RptProcedure.Rptprocedurename,"
                            + " TP_MST_RptProcedure.Rptproceduretype,"
                            + " decode(TP_MST_RptProcedure.Rptproceduretype,'A0001','出窑统计','质量统计') RptproceduretypeName,"
                            + " (select wm_concat(s.procedureid) sprocedureid from tp_mst_rptsprocedure s where s.rptprocedureid = TP_MST_RptProcedure.Rptprocedureid) sprocedureid,"
                            + " (select wm_concat(t.procedureid) tprocedureid from tp_mst_rpttprocedure t where t.rptprocedureid = TP_MST_RptProcedure.Rptprocedureid) tprocedureid,"
                            + " TP_MST_RptProcedure.Optimestamp "
                            + " from TP_MST_RptProcedure "
                            + " where TP_MST_RptProcedure.ValueFlag=1 "
                            + " and TP_MST_RptProcedure.AccountID=:accountID order by TP_MST_RptProcedure.displayno";

                List<OracleParameter> parameters = new List<OracleParameter>();
                parameters.Add(new OracleParameter(":AccountID", OracleDbType.Int32, accountid, ParameterDirection.Input));

                DataTable data = conn.GetSqlResultToDt(selSql.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 (conn != null &&
                    conn.ConnState == ConnectionState.Open)
                {
                    conn.Close();
                }
            }
        }


        /// <summary>
        /// 取得报表的查询数据源统计工序数据
        /// </summary>
        /// <param name="user">登录用户信息</param>
        /// <param name="se">查询条件</param>
        /// <returns>查询结果</returns> 
        public static ServiceResultEntity GetRptSourceProcedureModule(int accountid, int? RptProcedureID)
        {
            IDBConnection conn = null;
            try
            {
                conn = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString);


                OracleParameter[] paras = new OracleParameter[]{                  
                   new OracleParameter("in_rptProcedureId",OracleDbType.Int32,RptProcedureID,ParameterDirection.Input),
                   new OracleParameter("out_rptSResult",OracleDbType.RefCursor, ParameterDirection.Output),
                    new OracleParameter("out_rptTresult",OracleDbType.RefCursor, ParameterDirection.Output),
                };
                DataSet data = conn.ExecStoredProcedure("PRO_MST_GetRptProcedureBYID", paras);
                ServiceResultEntity sre = new ServiceResultEntity();
                if (data == null || data.Tables.Count == 0)
                {
                    sre.Status = Constant.ServiceResultStatus.NoSearchResults;
                    return sre;
                }
                sre.Data = new DataSet();
                sre.Data = data;
                return sre;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (conn != null &&
                    conn.ConnState == ConnectionState.Open)
                {
                    conn.Close();
                }
            }
        }
    }
}