/// <summary> /// get chaxun info /// </summary> /// <returns></returns> EyouSoft.Model.StatStructure.MYuSuanBiaoChaXunInfo GetChaXunInfo() { var info = new EyouSoft.Model.StatStructure.MYuSuanBiaoChaXunInfo(); info.ChuTuanERiQi = Utils.GetDateTimeNullable(Utils.GetQueryStringValue("txtChuTuanERiQi")); info.ChuTuanSRiQi = Utils.GetDateTimeNullable(Utils.GetQueryStringValue("txtChuTuanSRiQi")); info.TourCode = Utils.GetQueryStringValue("txtTourCode"); info.TourSellerDeptIds = null; info.TourStatus = null; info.XianLuName = Utils.GetQueryStringValue("txtXianLuName"); txtXiaoShouYuan.SellsID = info.XiaoShouYuanId = Utils.GetQueryStringValue(txtXiaoShouYuan.SellsIDClient);; txtXiaoShouYuan.SellsName = info.XiaoShouYuanName = Utils.GetQueryStringValue(txtXiaoShouYuan.SellsNameClient); string status = Utils.GetQueryStringValue(txtTourStatus.ValueClientID); if (!string.IsNullOrEmpty(status)) { string[] items = status.Split(','); if (items != null && items.Length > 0) { info.TourStatus = new EyouSoft.Model.EnumType.TourStructure.TourStatus[items.Length]; for (int i = 0; i < items.Length; i++) { info.TourStatus[i] = Utils.GetEnumValue <EyouSoft.Model.EnumType.TourStructure.TourStatus>(items[i], EyouSoft.Model.EnumType.TourStructure.TourStatus.销售未派计划); } } } string tourSellerDepts = txtXiaoShouYuanBuMen.SectionID = Utils.GetQueryStringValue(txtXiaoShouYuanBuMen.SelectIDClient); txtXiaoShouYuanBuMen.SectionName = Utils.GetQueryStringValue(txtXiaoShouYuanBuMen.SelectNameClient); if (!string.IsNullOrEmpty(tourSellerDepts)) { string[] items = tourSellerDepts.Split(','); if (items != null && items.Length > 0) { info.TourSellerDeptIds = new int[items.Length]; for (int i = 0; i < items.Length; i++) { info.TourSellerDeptIds[i] = Utils.GetInt(items[i]); } } } return(info); }
/// <summary> /// 获取预算表信息信息集合 /// </summary> /// <param name="companyId">公司编号</param> /// <param name="userId">数据级浏览权限控制-用户编号</param> /// <param name="depts">数据级浏览权限控制-部门编号</param> /// <param name="pageSize">页记录数</param> /// <param name="pageIndex">页索引</param> /// <param name="recordCount">总记录数</param> /// <param name="chaXun">查询</param> /// <param name="heJi">合计 [0:int:实收人数] [1:decimal:收入合计] [2:decimal:支出合计]</param> /// <returns></returns> public IList <EyouSoft.Model.StatStructure.MYuSuanBiaoInfo> GetYuSuanBiaos(string companyId, string userId, int[] depts, int pageSize, int pageIndex, ref int recordCount, EyouSoft.Model.StatStructure.MYuSuanBiaoChaXunInfo chaXun, out object[] heJi) { heJi = new object[] { 0, 0M, 0M }; IList <EyouSoft.Model.StatStructure.MYuSuanBiaoInfo> items = new List <EyouSoft.Model.StatStructure.MYuSuanBiaoInfo>(); string tableName = "view_TongJi_YuSuanBiao"; string fields = "*"; string paiXuString = "LDate DESC"; string heJiString = "SUM(ShiShouRenShu) AS ShiShouRenShu,SUM(ShouRuJinE) AS ShouRuJinE,SUM(ZhiChuJinE) AS ZhiChuJinE"; StringBuilder sql = new StringBuilder(); #region sql sql.AppendFormat(" CompanyId='{0}' AND TourStatus IN(0,1,2,3,4,5,6,7,8,9,10,13,15) ", companyId); if (depts != null && depts.Length == 1 && depts[0] == -1)//查看自己 { sql.AppendFormat(" AND SellerId='{0}' ", userId); } else { if (depts != null && depts.Length > 0) { sql.AppendFormat(" AND( DeptId IN({0}) ", Utils.GetSqlIn <int>(depts)); if (!string.IsNullOrEmpty(userId)) { sql.AppendFormat(" OR SellerId='{0}' ", userId); } sql.Append(" ) "); } } if (chaXun != null) { if (chaXun.ChuTuanERiQi.HasValue) { sql.AppendFormat(" AND LDate<'{0}' ", chaXun.ChuTuanERiQi.Value.AddDays(1)); } if (chaXun.ChuTuanSRiQi.HasValue) { sql.AppendFormat(" AND LDate>'{0}' ", chaXun.ChuTuanSRiQi.Value.AddDays(-1)); } if (!string.IsNullOrEmpty(chaXun.XiaoShouYuanId)) { sql.AppendFormat(" AND SellerId='{0}' ", chaXun.XiaoShouYuanId); } else if (!string.IsNullOrEmpty(chaXun.XiaoShouYuanName)) { sql.AppendFormat(" AND SellerName LIKE '%{0}%' ", chaXun.XiaoShouYuanName); } if (!string.IsNullOrEmpty(chaXun.TourCode)) { sql.AppendFormat(" AND TourCode LIKE '%{0}%' ", chaXun.TourCode); } if (!string.IsNullOrEmpty(chaXun.XianLuName)) { sql.AppendFormat(" AND RouteName LIKE '%{0}%' ", chaXun.XianLuName); } if (chaXun.TourStatus != null && chaXun.TourStatus.Length > 0) { sql.AppendFormat(" AND TourStatus IN({0})", Utils.GetSqlIn <EyouSoft.Model.EnumType.TourStructure.TourStatus>(chaXun.TourStatus)); } if (chaXun.TourSellerDeptIds != null && chaXun.TourSellerDeptIds.Length > 0) { sql.AppendFormat(" AND DeptId IN({0})", Utils.GetSqlIn <int>(chaXun.TourSellerDeptIds)); } } #endregion using (IDataReader rdr = DbHelper.ExecuteReader1(_db, pageSize, pageIndex, ref recordCount, tableName, fields, sql.ToString(), paiXuString, heJiString)) { while (rdr.Read()) { var item = new EyouSoft.Model.StatStructure.MYuSuanBiaoInfo(); item.ChuTuanRiQi = rdr.GetDateTime(rdr.GetOrdinal("LDate")); item.DaoYouName = rdr["DaoYouName"].ToString(); //item.HuiTuanRiQi= item.JiDiaoYuanName = rdr["JiDiaoYuanName"].ToString(); //item.MaoLi= item.ShiShouRenShu = rdr.GetInt32(rdr.GetOrdinal("ShiShouRenShu")); item.ShouRuJinE = rdr.GetDecimal(rdr.GetOrdinal("ShouRuJinE")); item.TianShu = rdr.GetInt32(rdr.GetOrdinal("TourDays")); item.TourCode = rdr["TourCode"].ToString(); item.TourId = rdr["TourId"].ToString(); item.TourStatus = (EyouSoft.Model.EnumType.TourStructure.TourStatus)rdr.GetByte(rdr.GetOrdinal("TourStatus")); item.TourType = (EyouSoft.Model.EnumType.TourStructure.TourType)rdr.GetByte(rdr.GetOrdinal("TourType")); item.XianLuName = rdr["RouteName"].ToString(); item.XiaoShouYuanName = rdr["SellerName"].ToString(); item.ZhiChuJinE = rdr.GetDecimal(rdr.GetOrdinal("ZhiChuJinE")); items.Add(item); } rdr.NextResult(); if (rdr.Read()) { if (!rdr.IsDBNull(rdr.GetOrdinal("ShiShouRenShu"))) { heJi[0] = rdr.GetInt32(rdr.GetOrdinal("ShiShouRenShu")); } if (!rdr.IsDBNull(rdr.GetOrdinal("ShouRuJinE"))) { heJi[1] = rdr.GetDecimal(rdr.GetOrdinal("ShouRuJinE")); } if (!rdr.IsDBNull(rdr.GetOrdinal("ZhiChuJinE"))) { heJi[2] = rdr.GetDecimal(rdr.GetOrdinal("ZhiChuJinE")); } } } return(items); }
/// <summary> /// 获取预算表信息信息集合 /// </summary> /// <param name="companyId">公司编号</param> /// <param name="userId">数据级浏览权限控制-用户编号</param> /// <param name="depts">数据级浏览权限控制-部门编号</param> /// <param name="pageSize">页记录数</param> /// <param name="pageIndex">页索引</param> /// <param name="recordCount">总记录数</param> /// <param name="chaXun">查询</param> /// <param name="heJi">合计 [0:int:实收人数] [1:decimal:收入合计] [2:decimal:支出合计]</param> /// <returns></returns> public IList <EyouSoft.Model.StatStructure.MYuSuanBiaoInfo> GetYuSuanBiaos(string companyId, int pageSize, int pageIndex, ref int recordCount, EyouSoft.Model.StatStructure.MYuSuanBiaoChaXunInfo chaXun, out object[] heJi) { heJi = new object[] { 0, 0M, 0M }; if (string.IsNullOrEmpty(companyId)) { return(null); } bool isOnlySelf = false; int[] depts = null; depts = GetDataPrivs(EyouSoft.Model.EnumType.PrivsStructure.Menu2.统计分析_预算表, out isOnlySelf); return(dal.GetYuSuanBiaos(companyId, LoginUserId, depts, pageSize, pageIndex, ref recordCount, chaXun, out heJi)); }