TG.WXCRM.V4/DAL/QH/QH_PerformanceQuery_DAL.cs

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OracleClient;
using WX.CRM.Common;

namespace WX.CRM.DAL.QH
{
    public class QH_PerformanceQuery_DAL
    {
        public DataSet QH_UserPerformanceQuery(
              ref Pager pager,
              string p_groupId,
              string p_innseruserId,
              decimal p_saleEid,
              DateTime? p_sd1,
              DateTime? p_sd2,
              decimal p_isDismiss,
            string businessType
         )
        {
            var conn = new OracleConnection(OracleHelper.AYCRMConn);
            if (conn.State == ConnectionState.Closed)
                conn.Open();
            try
            {
                using (var trans = conn.BeginTransaction())
                {
                    var p2 = new List<OracleParameter>
                    {
                        new OracleParameter()
                        {
                            ParameterName = "p_groupId",
                            OracleType = OracleType.VarChar,
                            Value = p_groupId
                        },
                        new OracleParameter()
                        {
                            ParameterName = "p_inneruserId",
                            OracleType = OracleType.VarChar,
                            Value = p_innseruserId
                        },
                        new OracleParameter() {ParameterName = "p_sd1", OracleType = OracleType.DateTime, Value = p_sd1},
                        new OracleParameter() {ParameterName = "p_sd2", OracleType = OracleType.DateTime, Value = p_sd2},
                        new OracleParameter()
                        {
                            ParameterName = "p_pageIndex",
                            OracleType = OracleType.Number,
                            Value = pager.page
                        },
                        new OracleParameter()
                        {
                            ParameterName = "p_pageSize",
                            OracleType = OracleType.Number,
                            Value = pager.rows
                        },
                        new OracleParameter()
                        {
                            ParameterName = "p_rowCount",
                            OracleType = OracleType.Cursor,
                            Direction = ParameterDirection.Output
                        },
                        new OracleParameter()
                        {
                            ParameterName = "v_emps",
                            OracleType = OracleType.Cursor,
                            Direction = ParameterDirection.Output
                        },
                        new OracleParameter()
                        {
                            ParameterName = "p_dataCount",
                            OracleType = OracleType.Cursor,
                            Direction = ParameterDirection.Output
                        },
                        p_isDismiss != -1
                            ? new OracleParameter()
                            {
                                ParameterName = "p_isDismiss",
                                OracleType = OracleType.Number,
                                Value = p_isDismiss
                            }
                            : new OracleParameter()
                            {
                                ParameterName = "p_isDismiss",
                                OracleType = OracleType.Number,
                                Value = DBNull.Value
                            },
                        p_saleEid > 0
                            ? new OracleParameter()
                            {
                                ParameterName = "p_saleEid",
                                OracleType = OracleType.Number,
                                Value = p_saleEid
                            }
                            : new OracleParameter()
                            {
                                ParameterName = "p_saleEid",
                                OracleType = OracleType.Number,
                                Value = DBNull.Value
                            },
                        string.IsNullOrEmpty(pager.order)
                        ?new OracleParameter(){ ParameterName = "p_Sort",OracleType = OracleType.VarChar,Value = DBNull.Value}
                        :new OracleParameter(){ ParameterName = "p_Sort",OracleType = OracleType.VarChar,Value = pager.order}
                        ,
                        string.IsNullOrEmpty(pager.sort)
                        ?new OracleParameter(){ ParameterName = "p_Column",OracleType = OracleType.VarChar,Value = DBNull.Value}
                        :new OracleParameter(){ ParameterName = "p_Column",OracleType = OracleType.VarChar,Value = pager.sort},
                    };
                    var ds = OracleHelper.DataQueray(trans, CommandType.StoredProcedure,
                        "PACK_REPORT_QH_COMMISSION.UserPerformanceQuery", p2.ToArray());
                    trans.Commit();
                    return ds;
                }
            }
            catch (Exception)
            {

                throw;
            }
            finally
            {
                conn.Close();
            }
        }

        public DataSet QH_GroupPerformanceQuery(
              ref Pager pager,
            string p_deptId,
              string p_groupId,
              DateTime? p_sd1,
              DateTime? p_sd2
         )
        {
            OracleConnection conn = new OracleConnection(OracleHelper.AYCRMConn);
            if (conn.State == ConnectionState.Closed)
                conn.Open();
            try
            {
                using (OracleTransaction trans = conn.BeginTransaction())
                {
                    OracleParameter[] p2 =
                    {
                        new OracleParameter()
                        {
                            ParameterName = "p_deptId",
                            OracleType = OracleType.VarChar,
                            Value = p_deptId
                        },
                        new OracleParameter()
                        {
                            ParameterName = "p_groupId",
                            OracleType = OracleType.VarChar,
                            Value = p_groupId
                        },
                        new OracleParameter() {ParameterName = "p_sd1", OracleType = OracleType.DateTime, Value = p_sd1},
                        new OracleParameter() {ParameterName = "p_sd2", OracleType = OracleType.DateTime, Value = p_sd2},
                        new OracleParameter()
                        {
                            ParameterName = "p_rowCount",
                            OracleType = OracleType.Cursor,
                            Direction = ParameterDirection.Output
                        },
                        new OracleParameter()
                        {
                            ParameterName = "v_emps",
                            OracleType = OracleType.Cursor,
                            Direction = ParameterDirection.Output
                        },
                        string.IsNullOrEmpty(pager.order)
                        ?new OracleParameter(){ ParameterName = "p_Sort",OracleType = OracleType.VarChar,Value = DBNull.Value}
                        :new OracleParameter(){ ParameterName = "p_Sort",OracleType = OracleType.VarChar,Value = pager.order}
                        ,
                        string.IsNullOrEmpty(pager.sort)
                        ?new OracleParameter(){ ParameterName = "p_Column",OracleType = OracleType.VarChar,Value = DBNull.Value}
                        :new OracleParameter(){ ParameterName = "p_Column",OracleType = OracleType.VarChar,Value = pager.sort}
                    };
                    var ds = OracleHelper.DataQueray(trans, CommandType.StoredProcedure,
                        "PACK_REPORT_QH_COMMISSION.GroupPerformanceQuery", p2);
                    trans.Commit();
                    return ds;
                }
            }
            catch (Exception)
            {

                throw;
            }
            finally
            {
                if (conn.State == ConnectionState.Open)
                    conn.Close();
            }
        }



        /// <summary>
        /// 交易部员工业绩实时查询
        /// </summary>
        /// <param name="pager"></param>
        /// <param name="saledeptid"></param>
        /// <param name="groupid"></param>
        /// <param name="userList"></param>
        /// <param name="username"></param>
        /// <param name="groupName"></param>
        /// <param name="eid"></param>
        /// <param name="dtstart"></param>
        /// <param name="dtend"></param>
        /// <param name="dis"></param>
        /// <param name="total"></param>
        /// <param name="qtype"></param>
        /// <param name="orderField"></param>
        /// <param name="orderDirec"></param>
        /// <returns></returns>

    }
}