Linq 无限制多条件查询

本文介绍了一个用于获取业务组别的方法以及订单操作时间报表的实现,包括组别信息查询、订单操作时间数据获取和总数计算。
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Uzai.Shop.Entity.OrderReport;
using System.Data;
using Uzai.DB.SQLHelper;
using System.Data.SqlClient;
using Uzai.DB.IDAL.OrderReport;

namespace Uzai.DB.SonDAL.OrderReport
{
    public class OrderReportDAL : IOrderReport
    {
        #region tanyong 2013-6-17
        /// <summary>
        /// 获得组别
        /// </summary>
        /// <returns></returns>
        public List<UzaiAdminBusinessGroup> GetAdminGroup(UzaiAdminBusinessGroup entity)
        {
            List<UzaiAdminBusinessGroup> list = new List<UzaiAdminBusinessGroup>();
            StringBuilder sql = new StringBuilder();
            sql.Append("select AdminBusinessGroupID,AdminBusinessGroupParentID,AdminBusinessGroupName,AdminBusinessGroupList from UzaiAdminBusinessGroup where isuse=1 ");
            if (entity.AdminBusinessGroupID != 0)
                sql.Append(" and AdminBusinessGroupID=@AdminBusinessGroupID");
            if (entity.AdminBusinessGroupParentID != 0)
                sql.Append(" and AdminBusinessGroupParentID=@AdminBusinessGroupParentID");

            SqlParameter[] para = { 
                                      new SqlParameter("@AdminBusinessGroupID",entity.AdminBusinessGroupID)
                                      ,new SqlParameter("@AdminBusinessGroupParentID",entity.AdminBusinessGroupParentID)
                                  };
            DataTable dt = SqlHelper.ExecuteDataTable(SqlHelper.connstr, CommandType.Text, sql.ToString(),para);
            foreach (DataRow row in dt.Rows)
            {
                UzaiAdminBusinessGroup model = new UzaiAdminBusinessGroup();
                model.AdminBusinessGroupID = row["AdminBusinessGroupID"] is DBNull ? 0 : int.Parse(row["AdminBusinessGroupID"].ToString());
                model.AdminBusinessGroupParentID = row["AdminBusinessGroupParentID"] is DBNull ? 0 : int.Parse(row["AdminBusinessGroupParentID"].ToString());
                model.AdminBusinessGroupName = row["AdminBusinessGroupName"] is DBNull ? "" : row["AdminBusinessGroupName"].ToString();
                model.AdminBusinessGroupList = row["AdminBusinessGroupList"] is DBNull ? "" : row["AdminBusinessGroupList"].ToString();
                list.Add(model);
            }

            return list;
        }

        /// <summary>
        /// 获得人的信息(部门,级别,姓名)
        /// </summary>
        /// <returns></returns>
        public List<UzaiAdminBusinessGroup> GetPeopleInfo()
        {
            List<UzaiAdminBusinessGroup> list = new List<UzaiAdminBusinessGroup>();
            string sql = @"select AdminBusinessGroupID,AdminBusinessGroupParentID,AdminBusinessGroupName,AdminBusinessGroupList from UzaiAdminBusinessGroup where AdminBusinessGroupParentID  in (
                            select  AdminBusinessGroupID from UzaiAdminBusinessGroup where 
                            AdminBusinessGroupID in(2,9,15,63,77)
                             and AdminBusinessGroupParentId=-1)";
            DataTable dt= SqlHelper.ExecuteDataTable(SqlHelper.connstr, CommandType.Text, sql);
            foreach (DataRow row in dt.Rows)
            {
                UzaiAdminBusinessGroup model = new UzaiAdminBusinessGroup();
                model.AdminBusinessGroupID = row["AdminBusinessGroupID"] is DBNull ? 0 : int.Parse(row["AdminBusinessGroupID"].ToString());
                model.AdminBusinessGroupParentID = row["AdminBusinessGroupParentID"] is DBNull ? 0 : int.Parse(row["AdminBusinessGroupParentID"].ToString());
                model.AdminBusinessGroupName = row["AdminBusinessGroupName"] is DBNull ? "" : row["AdminBusinessGroupName"].ToString();
                model.AdminBusinessGroupList = row["AdminBusinessGroupList"] is DBNull ? "" : row["AdminBusinessGroupList"].ToString();
                list.Add(model);
            }
            return list;
        }

        #region 订单操作时间报表
        /// <summary>
        /// 获得订单操作时间数据
        /// </summary>
        /// <returns></returns>
        public DataTable GetOrderOperateStatusRecord(string userId, string startDay, string endDay, string Department, string Group, int startindex, int endindex)
        {
            StringBuilder sb = new StringBuilder();
            string sqlwhere = GetOrderRecordWhere(userId, startDay,endDay);
            sb.AppendFormat(@"BEGIN WITH tempTable AS(  
                    select ROW_NUMBER() OVER(ORDER BY OperateTime DESC) AS ROW,UzaiOrderStatusTypeId,OperateTime,UzaiAdminId,UzaiUserName,UzaiRealName,CompleteTime
                    ,LastOpAdminId,LastOpAdminRealName from UzaiOrderOperateStatusRecord where 1=1 {2} )
                    select * from tempTable where 1=1 and ROW between {0} and {1} END", startindex, endindex,sqlwhere);
            SqlParameter[] para = GetOrderRecordPara(userId, startDay, endDay);

            DataTable dt = SqlHelper.ExecuteDataTable(SqlHelper.connstr, CommandType.Text, sb.ToString(),para);
            return dt;
        }

        /// <summary>
        /// 获得订单操作时间数据(总数)
        /// </summary>
        /// <param name="userId"></param>
        /// <param name="startDay"></param>
        /// <param name="endDay"></param>
        /// <returns></returns>
        public int GetOrderRecordCount(string userId, string startDay, string endDay)
        {
            StringBuilder sb = new StringBuilder();
            string sqlwhere = GetOrderRecordWhere(userId, startDay, endDay);
            sb.AppendFormat(@"select count(*) as count from UzaiOrderOperateStatusRecord where 1=1 {0}", sqlwhere);
            SqlParameter[] para = GetOrderRecordPara(userId, startDay, endDay);
            DataTable dt = SqlHelper.ExecuteDataTable(SqlHelper.connstr, CommandType.Text, sb.ToString(), para);
            int result = 0;
            if (dt != null && dt.Rows.Count > 0)
                result = int.Parse(dt.Rows[0]["count"].ToString());
             return result;
        }

        private string GetOrderRecordWhere(string userId, string startDay, string endDay)
        {
            StringBuilder sb = new StringBuilder();
            if (userId != "0")
                sb.Append(" and UzaiAdminId=@UzaiAdminId");
            if (startDay != "")
                sb.Append(" and OperateTime>=@startDay");
            if (endDay != "")
                sb.Append(" and OperateTime<@endDay");
            return sb.ToString();
        }
        private SqlParameter[] GetOrderRecordPara(string userId, string startDay, string endDay)
        {
            SqlParameter[] para = { 
                                    new SqlParameter("@UzaiAdminId",userId)
                                    ,new SqlParameter("@startDay",startDay)
                                    ,new SqlParameter("@endDay",endDay)
                                  };
            return para;
        }
        #endregion

        /// <summary>
        /// 获得操作记录
        /// </summary>
        /// <returns></returns>
        public string GetorderstatustypeRemark(int typeid)
        {
            string remark = string.Empty;
            string sqlorderstatus = "select statestr from uzaiorderstatustype where id=@UzaiOrderStatusTypeId";
            DataTable dtOrderstatustype = SqlHelper.ExecuteDataTable(SqlHelper.connstr, CommandType.Text, sqlorderstatus, new SqlParameter("@UzaiOrderStatusTypeId", typeid));
            if (dtOrderstatustype != null && dtOrderstatustype.Rows.Count > 0)
            {
                remark = dtOrderstatustype.Rows[0]["statestr"].ToString();
            }
            return remark;
        }
        #endregion

    }
}

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Uzai.Shop.Entity.OrderReport;
using Uzai.DB.IDAL.OrderReport;
using Uzai.DB.SonDAL.OrderReport;
using System.Data;

namespace Uzai.Shop.BLL.OrderReport
{
    public class OrderReportBLL
    {
        #region tanyong 2013-6-17
        IOrderReport dal = new OrderReportDAL();

        /// <summary>
        /// 获得组别
        /// </summary>
        /// <returns></returns>
        public List<UzaiAdminBusinessGroup> GetAdminGroup(UzaiAdminBusinessGroup entity)
        {
            return dal.GetAdminGroup(entity);
        }
        /// <summary>
        /// 获得人的信息(部门,级别,姓名)
        /// </summary>
        /// <returns></returns>
        public List<UzaiAdminBusinessGroup> GetPeopleInfo()
        {
            return dal.GetPeopleInfo();
        }
        /// <summary>
        /// 获得订单操作时间数据
        /// </summary>
        /// <returns></returns>
        public DataTable GetOrderOperateStatusRecord(string userId, string startDay, string endDay, string Department, string Group, int startindex, int endindex)
        {
            DataTable dt = dal.GetOrderOperateStatusRecord(userId, startDay, endDay, Department,Group,startindex,endindex);
            dt.Columns.Add("remark");
            dt.Columns.Add("Department");
            dt.Columns.Add("Group");
            dt.Columns.Add("UserId");
            dt.Columns.Add("UserName");
            foreach (DataRow row in dt.Rows)
            {
                int typeid = row["UzaiOrderStatusTypeId"] is DBNull ? 0 : int.Parse(row["UzaiOrderStatusTypeId"].ToString());
                row["remark"] = GetorderstatustypeRemark(typeid);
                peopleModel model = GetPeopleInfoCache(userId, Department, Group);
                row["Department"] = model.Department;
                row["Group"] = model.Group;
                row["UserId"] = model.UserId;
                row["UserName"] = model.UserName;
            }

            return dt;
        }

        /// <summary>
        /// 获得订单操作时间数据(总数)
        /// </summary>
        /// <param name="userId"></param>
        /// <param name="startDay"></param>
        /// <param name="endDay"></param>
        /// <returns></returns>
        public int GetOrderRecordCount(string userId, string startDay, string endDay)
        {
            return dal.GetOrderRecordCount(userId, startDay, endDay);
        }
        /// <summary>
        /// 获得操作记录
        /// </summary>
        /// <returns></returns>
        public string GetorderstatustypeRemark(int typeid)
        {
            return dal.GetorderstatustypeRemark(typeid);
        }


        /// <summary>
        /// 获得人缓存里面信息
        /// </summary>
        /// <returns></returns>
        private peopleModel GetPeopleInfoCache(string userId, string Department, string Group)
        {
            if (System.Web.HttpRuntime.Cache["PeopleInfo"] == null)//缓存为空,创建缓存
                SetPeoTable();

            DataTable dtPeople = (DataTable)System.Web.HttpRuntime.Cache["PeopleInfo"];

            var query = (from a in dtPeople.AsEnumerable()
                         select new
                         {
                             Department = a.Field<string>("Department"),
                             Group = a.Field<string>("Group"),
                             UserId = a.Field<string>("UserId"),
                             UserName = a.Field<string>("UserName")
                         });
            if (!string.IsNullOrEmpty(userId) && userId!="0")
            {
                query = query.Where(p => p.UserId == userId);
            }
            if (!string.IsNullOrEmpty(Department) && Department!="请选择")
            {
                query = query.Where(p => p.Department == Department);
            }
            if (!string.IsNullOrEmpty(Group) && Group != "请选择")
            {
                query = query.Where(p => p.Group == Group);
            }
 

            peopleModel model = new peopleModel();
            foreach (var item in query)
            {
                model.Department = item.Department;
                model.Group = item.Group;
                model.UserId = item.UserId;
                model.UserName = item.UserName;
            }
            return model;
        }

        /// <summary>
        /// 拿到人的信息存入缓存
        /// </summary>
        private void SetPeoTable()
        {
            DataTable table = new DataTable();
            table.Columns.Add("Department");
            table.Columns.Add("Group");
            table.Columns.Add("UserId");
            table.Columns.Add("UserName");

            List<UzaiAdminBusinessGroup> list = GetPeopleInfo();
            foreach (UzaiAdminBusinessGroup entity in list)
            {
                string[] GropList = entity.AdminBusinessGroupList.Split(';');
                for (int i = 0; i < GropList.Length - 1; i++)
                {
                    string[] peopleList = GropList[i].Split(',');
                    if (peopleList[1] == "1")
                    {//在职员工
                        object[] o = { GetDepartmentName(entity.AdminBusinessGroupParentID), entity.AdminBusinessGroupName, peopleList[0], peopleList[3] };
                        table.Rows.Add(o);
                    }
                }
            }
            System.Web.HttpRuntime.Cache.Insert("PeopleInfo", table, null, DateTime.Now.AddHours(1), System.Web.Caching.Cache.NoSlidingExpiration);

        }

        /// <summary>
        /// 根据部门编号查询部门名称
        /// </summary>
        /// <param name="DepartmentID">部门编号</param>
        /// <returns></returns>
        private string GetDepartmentName(int DepartmentID)
        {
            string strDepartment = string.Empty;
            switch (DepartmentID)
            {
                case 2:
                    strDepartment = "国内订单组";
                    break;
                case 9:
                    strDepartment = "国际订单组";
                    break;
                case 15:
                    strDepartment = "大客户订单组";
                    break;
                case 63:
                    strDepartment = "签证订单组";
                    break;
                case 77:
                    strDepartment = "自由行订单组";
                    break;
            }
            return strDepartment;
        }
        #endregion
    }
}
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace Uzai.Shop.Entity.OrderReport
{
    public class OrderReportModel
    {
        
    }

    public class UzaiAdminBusinessGroup
    {
        /// <summary>
        /// 编号
        /// </summary>
        public int AdminBusinessGroupID { get; set; }
        /// <summary>
        /// 业务组别父级ID
        /// </summary>
        public int AdminBusinessGroupParentID { get; set; }
        /// <summary>
        /// 级别名称
        /// </summary>
        public string AdminBusinessGroupName { get; set; }
        /// <summary>
        /// 数据形式
        /// </summary>
        public string AdminBusinessGroupList { get; set; }
    }

    public class peopleModel
    {
        /// <summary>
        /// 部门名称
        /// </summary>
        public string Department { get; set; }
        /// <summary>
        /// 组别
        /// </summary>
        public string Group { get; set; }
        /// <summary>
        /// 用户编号
        /// </summary>
        public string UserId { get; set; }
        /// <summary>
        /// 用户名
        /// </summary>
        public string UserName { get; set; }
    }
}


评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值