/// <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));
}
