/// <summary>
/// 绑定查询条件
/// </summary>
/// <returns></returns>
private EyouSoft.Model.CompanyStructure.MCustomerSoldStatSearchInfo SearchPara()
{
EyouSoft.Model.CompanyStructure.MCustomerSoldStatSearchInfo seachInfo = new EyouSoft.Model.CompanyStructure.MCustomerSoldStatSearchInfo();
LeaveTourStartDate = Utils.GetDateTimeNullable(Utils.GetQueryStringValue("todate"));
LeaveTourEndDate = Utils.GetDateTimeNullable(Utils.GetQueryStringValue("endate"));
QueryOperator = (EyouSoft.Model.EnumType.FinanceStructure.QueryOperator)Utils.GetInt(Utils.GetQueryStringValue("qo"));
TradeNum = Utils.GetInt(Utils.GetQueryStringValue("tn"));
customerId = Utils.GetIntNull(Utils.GetQueryStringValue("customerId"));
SalserId = Utils.GetIntArray(Utils.GetQueryStringValue("xsyId"), ",");
cityId = Utils.GetIntArray(Utils.GetQueryStringValue("cityId"), ",");
seachInfo.TourStartDate = LeaveTourStartDate;
seachInfo.TourEndDate = LeaveTourEndDate;
seachInfo.SearchCompare = QueryOperator;
seachInfo.SendTourPeopleNumber = TradeNum;
seachInfo.CustomerId = customerId;
seachInfo.SellerIds = SalserId;
seachInfo.CityIdList = cityId;
seachInfo.AreaId = Utils.GetIntNull(Utils.GetQueryStringValue("areaid"));
seachInfo.DeptIds = Utils.GetIntArray(Utils.GetQueryStringValue("deptids"), ",");
seachInfo.XSTJFXOrderByType = Utils.GetInt(Utils.GetQueryStringValue("orderbytype"));
if (seachInfo.AreaId.HasValue && seachInfo.AreaId.Value < 1)
{
seachInfo.AreaId = null;
}
return(seachInfo);
}
/// <summary>
/// 加载组团社数据
/// </summary>
private void InitCustomerList()
{
PageIndex = Utils.GetInt(Utils.GetQueryStringValue("Page"), 1);//页码
if (PageIndex < 1)
{
PageIndex = 1;
}
IList <EyouSoft.Model.CompanyStructure.CustomerInfo> list = null;//客户资料集合
//客户资料排序实体
EyouSoft.Model.CompanyStructure.MCustomerSoldStatSearchInfo Seachinfo = new EyouSoft.Model.CompanyStructure.MCustomerSoldStatSearchInfo();
Seachinfo = SearchPara();
list = new EyouSoft.BLL.StatisticStructure.SoldStatistic().GetCustomers(SiteUserInfo.CompanyID, PageSize, PageIndex, ref RecordCount, Seachinfo);
if (list != null && list.Count > 0)
{
double pageCount = Math.Ceiling((double)RecordCount / (double)PageSize);
if (PageIndex > pageCount)
{
PageIndex = (int)pageCount;
}
rptCustomer.DataSource = list;
rptCustomer.DataBind();
BindExportPage();
}
else
{
rptCustomer.EmptyText = "<tr><td colspan='10' align='center'>对不起，暂无客户资料信息！</td></tr>";
ExportPageInfo1.Visible = false;
}
RegisterScript(string.Format("var recordCount={0};", RecordCount));
}
/// <summary>
/// 绑定纺计数据
/// </summary>
private void InitSumStat()
{
EyouSoft.Model.CompanyStructure.MCustomerSoldStatSearchInfo Seachinfo = new EyouSoft.Model.CompanyStructure.MCustomerSoldStatSearchInfo();
Seachinfo = SearchPara();
Dictionary <string, int> soldStat = new EyouSoft.BLL.StatisticStructure.SoldStatistic().GetSoldStatSumInfo(SiteUserInfo.CompanyID, PageSize, PageIndex, ref RecordCount, Seachinfo);
this.ltrSumTourNumber.Text = soldStat["TradeTourNum"].ToString();
this.ltrSumTradeNumber.Text = soldStat["TradeNum"].ToString();
this.ltrSumVistorNumber.Text = (soldStat["TradeNum"] - soldStat["TradeTourNum"]).ToString();
}
/// <summary>
/// 按指定条件获取客户资料信息集合
/// </summary>
/// <param name="companyId">公司（专线）编号</param>
/// <param name="pageSize">每页记录数</param>
/// <param name="pageIndex">当前页索引</param>
/// <param name="recordCount">总记录数</param>
/// <param name="seachInfo">查询条件业务实体</param>
/// <returns></returns>
public IList <EyouSoft.Model.CompanyStructure.CustomerInfo> GetCustomers(int companyId, int pageSize, int pageIndex, ref int recordCount, EyouSoft.Model.CompanyStructure.MCustomerSoldStatSearchInfo seachInfo)
{
IList <EyouSoft.Model.CompanyStructure.CustomerInfo> items = new List <EyouSoft.Model.CompanyStructure.CustomerInfo>();
StringBuilder cmdQuery = new StringBuilder();
string tableName = String.Format("select Id,ProvinceName,CityId,CityName,[Name],Saler,SaleId,ContactName,Phone,Mobile,Fax,CompanyId,IsNull((select sum(O.PeopleNumber-O.LeaguePepoleNum) from tbl_TourOrder AS O INNER JOIN tbl_Tour AS T ON O.TourId = T.TourId where T.CompanyId = {0} AND (T.TourType in (0,1)) And T.IsDelete= 0 And O.IsDelete = 0 AND O.BuyCompanyID=tbl_Customer.Id AND O.OrderState=5),0) AS TradeNum,IsNull((select sum(O.PeopleNumber-O.LeaguePepoleNum) from tbl_TourOrder AS O INNER JOIN tbl_Tour AS T ON O.TourId = T.TourId where T.CompanyId = {0} AND (T.TourType=0) AND T.IsDelete= 0 And O.IsDelete = 0 AND O.BuyCompanyID=tbl_Customer.Id AND O.OrderState=5),0) AS TradeTourNum from tbl_Customer WHERE Companyid={0} AND IsDelete='0'", companyId);
bool IsGroupBy = false;
string orderByString = "TradeNum DESC";
string fields = "*";
#region 拼接查询条件
cmdQuery.Append(" (1=1)");
if (seachInfo != null)
{
//出团日期
if (seachInfo.TourStartDate.HasValue || seachInfo.TourEndDate.HasValue || seachInfo.AreaId.HasValue)
{
//出团日期查询
string tmpTourLeaveDateSearchPara = String.Empty;
if (seachInfo.TourStartDate.HasValue)
{
tmpTourLeaveDateSearchPara = " and (T.LeaveDate > '" + seachInfo.TourStartDate.Value.AddDays(-1) + "') ";
}
if (seachInfo.TourEndDate.HasValue)
{
tmpTourLeaveDateSearchPara += " and (T.LeaveDate < '" + seachInfo.TourEndDate.Value.AddDays(1) + "') ";
}
if (seachInfo.AreaId.HasValue)
{
tmpTourLeaveDateSearchPara += " AND (T.AreaId=" + seachInfo.AreaId.Value + ") ";
}
tableName = String.Format("select Id,ProvinceName,CityId,CityName,[Name],Saler,SaleId,ContactName,Phone,Mobile,Fax,CompanyId,IsNull((SELECT sum(O.PeopleNumber-O.LeaguePepoleNum) FROM tbl_TourOrder AS O INNER JOIN tbl_Tour AS T ON O.TourId = T.TourId WHERE T.CompanyId = {0} AND (T.TourType in (0,1)) AND T.IsDelete= 0 And O.IsDelete = 0 AND O.BuyCompanyID=tbl_Customer.Id AND O.OrderState=5 {1}),0) as TradeNum,IsNull((SELECT sum(O.PeopleNumber-O.LeaguePepoleNum) FROM tbl_TourOrder AS O INNER JOIN tbl_Tour AS T ON O.TourId = T.TourId WHERE T.CompanyId = {0} AND (T.TourType=0) AND T.IsDelete= 0 And O.IsDelete = 0 AND O.BuyCompanyID=tbl_Customer.Id AND O.OrderState=5 {2}),0) AS TradeTourNum from tbl_Customer WHERE Companyid={0} AND IsDelete='0'", companyId, tmpTourLeaveDateSearchPara, tmpTourLeaveDateSearchPara);
cmdQuery.Append(" AND (Id in (select O.BuyCompanyID From tbl_TourOrder O,tbl_Tour T WHERE O.TourId = T.TourId " + tmpTourLeaveDateSearchPara + "))");
}
if (seachInfo.CityIdList != null && seachInfo.CityIdList.Length > 0)
{
string CityIdList = "";
for (int i = 0; i < seachInfo.CityIdList.Length; i++)
{
CityIdList = CityIdList + seachInfo.CityIdList[i].ToString() + ',';
}
CityIdList = CityIdList.Trim(',');
cmdQuery.AppendFormat(" AND CityId in ({0}) ", CityIdList);
}
if (seachInfo.CustomerId.HasValue)
{
cmdQuery.AppendFormat(" AND Id = {0} ", seachInfo.CustomerId.Value);
}
if (!string.IsNullOrEmpty(seachInfo.SellerName))
{
cmdQuery.AppendFormat(" AND Saler LIKE '%{0}%' ", seachInfo.SellerName);
}
if (seachInfo.SellerIds != null && seachInfo.SellerIds.Length > 0)
{
cmdQuery.AppendFormat(" AND SaleId IN({0}) ", EyouSoft.Toolkit.Utils.GetSqlIdStrByArray(seachInfo.SellerIds));
}
if (!string.IsNullOrEmpty(seachInfo.Mobile))
{
cmdQuery.AppendFormat(" AND Mobile LIKE '%{0}%' ", seachInfo.Mobile);
}
if (seachInfo.DeptIds != null && seachInfo.DeptIds.Length > 0 && (seachInfo.SellerIds == null || seachInfo.SellerIds.Length == 0))
{
cmdQuery.AppendFormat(" AND EXISTS(SELECT 1 FROM tbl_CompanyUser WHERE Id=SaleId AND DepartId IN({0}) ) ", EyouSoft.Toolkit.Utils.GetSqlIdStrByArray(seachInfo.DeptIds));
}
//交易数比较
switch (seachInfo.SearchCompare)
{
case EyouSoft.Model.EnumType.FinanceStructure.QueryOperator.大于等于:
cmdQuery.AppendFormat(" AND TradeNum > {0} ", seachInfo.SendTourPeopleNumber - 1);
break;
case EyouSoft.Model.EnumType.FinanceStructure.QueryOperator.等于:
cmdQuery.AppendFormat(" AND TradeNum = {0} ", seachInfo.SendTourPeopleNumber);
break;
case EyouSoft.Model.EnumType.FinanceStructure.QueryOperator.小于等于:
cmdQuery.AppendFormat(" AND TradeNum < {0} ", seachInfo.SendTourPeopleNumber + 1);
break;
}
switch (seachInfo.XSTJFXOrderByType)
{
case 0:
orderByString = "TradeNum DESC";
break;
case 1:
orderByString = "TradeNum ASC";
break;
case 2:
orderByString = "TradeTourNum DESC";
break;
case 3:
orderByString = "TradeTourNum ASC";
break;
case 4:
orderByString = "TradeNum-TradeTourNum DESC";
break;
case 5:
orderByString = "TradeNum-TradeTourNum ASC";
break;
default:
orderByString = "TradeNum DESC";
break;
}
}
#endregion
using (IDataReader rdr = DbHelper.ExecuteReaderBySqlTbl(this._db, pageSize, pageIndex, ref recordCount, tableName, fields, cmdQuery.ToString(), orderByString, IsGroupBy))
{
while (rdr.Read())
{
EyouSoft.Model.CompanyStructure.CustomerInfo item = new EyouSoft.Model.CompanyStructure.CustomerInfo();
item.CityName = rdr.IsDBNull(rdr.GetOrdinal("CityName")) ? "" : rdr["CityName"].ToString();
item.CompanyId = rdr.GetInt32(rdr.GetOrdinal("CompanyId"));
item.ContactName = rdr.IsDBNull(rdr.GetOrdinal("ContactName")) ? "" : rdr["ContactName"].ToString();
item.Fax = rdr.IsDBNull(rdr.GetOrdinal("Fax")) ? "" : rdr["Fax"].ToString();
item.Id = rdr.GetInt32(rdr.GetOrdinal("Id"));
item.Mobile = rdr.IsDBNull(rdr.GetOrdinal("Mobile")) ? "" : rdr["Mobile"].ToString();
item.Name = rdr.IsDBNull(rdr.GetOrdinal("Name")) ? "" : rdr["Name"].ToString();
item.Phone = rdr.IsDBNull(rdr.GetOrdinal("Phone")) ? "" : rdr["Phone"].ToString();
item.ProvinceName = rdr.IsDBNull(rdr.GetOrdinal("ProvinceName")) ? "" : rdr["ProvinceName"].ToString();
item.SaleId = rdr.GetInt32(rdr.GetOrdinal("SaleId"));
item.Saler = rdr.IsDBNull(rdr.GetOrdinal("Saler")) ? "" : rdr["Saler"].ToString();
item.TradeTourNum = rdr.GetInt32(rdr.GetOrdinal("TradeTourNum"));
item.TradeNum = rdr.GetInt32(rdr.GetOrdinal("TradeNum"));
items.Add(item);
}
}
return(items);
}
/// <summary>
/// 按指定条件获取 人数合计信息
/// </summary>
/// <param name="companyId">公司（专线）编号</param>
/// <param name="pageSize">每页记录数</param>
/// <param name="pageIndex">当前页索引</param>
/// <param name="recordCount">总记录数</param>
/// <param name="seachInfo">查询条件业务实体</param>
/// <returns></returns>
public Dictionary <string, int> GetSoldStatSumInfo(int companyId, int pageSize, int pageIndex, ref int recordCount, EyouSoft.Model.CompanyStructure.MCustomerSoldStatSearchInfo seachInfo)
{
Dictionary <string, int> SoldStat = new Dictionary <string, int>();
StringBuilder cmdQuery = new StringBuilder();
string strSql = "select IsNUll(sum(t.TradeNum),0) as TradeNum,IsNull(sum(t.TradeTourNum),0) as TradeTourNum from (";
string tableName = String.Format("select IsNull((select sum(O.PeopleNumber-O.LeaguePepoleNum) from tbl_TourOrder AS O INNER JOIN tbl_Tour AS T ON O.TourId = T.TourId where T.CompanyId = {0} AND (T.TourType in (0,1)) And T.IsDelete= 0 And O.IsDelete = 0 AND O.BuyCompanyID=tbl_Customer.Id AND O.OrderState=5),0) AS TradeNum,IsNull((select sum(O.PeopleNumber-O.LeaguePepoleNum) from tbl_TourOrder AS O INNER JOIN tbl_Tour AS T ON O.TourId = T.TourId where T.CompanyId = {0} AND (T.TourType=0) AND T.IsDelete= 0 And O.IsDelete = 0 AND O.BuyCompanyID=tbl_Customer.Id AND O.OrderState=5),0) AS TradeTourNum from tbl_Customer WHERE Companyid={0} AND IsDelete='0'", companyId);
#region 拼接查询条件
if (seachInfo != null)
{
//出团日期
if (seachInfo.TourStartDate.HasValue || seachInfo.TourEndDate.HasValue || seachInfo.AreaId.HasValue)
{
//出团日期查询
string tmpTourLeaveDateSearchPara = String.Empty;
if (seachInfo.TourStartDate.HasValue)
{
tmpTourLeaveDateSearchPara = " and (T.LeaveDate > '" + seachInfo.TourStartDate.Value.AddDays(-1) + "') ";
}
if (seachInfo.TourEndDate.HasValue)
{
tmpTourLeaveDateSearchPara += " and (T.LeaveDate < '" + seachInfo.TourEndDate.Value.AddDays(1) + "') ";
}
if (seachInfo.AreaId.HasValue)
{
tmpTourLeaveDateSearchPara += " and (T.AreaId = " + seachInfo.AreaId + ") ";
}
tableName = String.Format("select Id,ProvinceName,CityId,CityName,[Name],Saler,SaleId,ContactName,Phone,Mobile,Fax,CompanyId,IsNull((SELECT sum(O.PeopleNumber-O.LeaguePepoleNum) FROM tbl_TourOrder AS O INNER JOIN tbl_Tour AS T ON O.TourId = T.TourId WHERE T.CompanyId = {0} AND (T.TourType in (0,1)) AND T.IsDelete= 0 And O.IsDelete = 0 AND O.BuyCompanyID=tbl_Customer.Id AND O.OrderState=5 {1}),0) as TradeNum,IsNull((SELECT sum(O.PeopleNumber-O.LeaguePepoleNum) FROM tbl_TourOrder AS O INNER JOIN tbl_Tour AS T ON O.TourId = T.TourId WHERE T.CompanyId = {0} AND (T.TourType=0) AND T.IsDelete= 0 And O.IsDelete = 0 AND O.BuyCompanyID=tbl_Customer.Id AND O.OrderState=5 {2}),0) AS TradeTourNum from tbl_Customer WHERE Companyid={0} AND IsDelete='0'", companyId, tmpTourLeaveDateSearchPara, tmpTourLeaveDateSearchPara);
cmdQuery.Append(" AND (Id in (select O.BuyCompanyID From tbl_TourOrder O,tbl_Tour T WHERE O.TourId = T.TourId " + tmpTourLeaveDateSearchPara + "))");
}
if (seachInfo.CityIdList != null && seachInfo.CityIdList.Length > 0)
{
string CityIdList = "";
for (int i = 0; i < seachInfo.CityIdList.Length; i++)
{
CityIdList = CityIdList + seachInfo.CityIdList[i].ToString() + ',';
}
CityIdList = CityIdList.Trim(',');
cmdQuery.AppendFormat(" AND CityId in ({0}) ", CityIdList);
}
if (seachInfo.CustomerId.HasValue)
{
cmdQuery.AppendFormat(" AND Id = {0} ", seachInfo.CustomerId.Value);
}
if (!string.IsNullOrEmpty(seachInfo.SellerName))
{
cmdQuery.AppendFormat(" AND Saler LIKE '%{0}%' ", seachInfo.SellerName);
}
if (seachInfo.SellerIds != null && seachInfo.SellerIds.Length > 0)
{
cmdQuery.AppendFormat(" AND SaleId IN({0}) ", EyouSoft.Toolkit.Utils.GetSqlIdStrByArray(seachInfo.SellerIds));
}
if (!string.IsNullOrEmpty(seachInfo.Mobile))
{
cmdQuery.AppendFormat(" AND Mobile LIKE '%{0}%' ", seachInfo.Mobile);
}
if (seachInfo.DeptIds != null && seachInfo.DeptIds.Length > 0 && (seachInfo.SellerIds == null || seachInfo.SellerIds.Length == 0))
{
cmdQuery.AppendFormat(" AND EXISTS(SELECT 1 FROM tbl_CompanyUser WHERE Id=tbl_Customer.SaleId AND DepartId IN({0}) ) ", EyouSoft.Toolkit.Utils.GetSqlIdStrByArray(seachInfo.DeptIds));
}
}
#endregion
strSql = strSql + tableName + cmdQuery.ToString() + ") as t";
//交易数比较
switch (seachInfo.SearchCompare)
{
case EyouSoft.Model.EnumType.FinanceStructure.QueryOperator.大于等于:
strSql = strSql + String.Format(" WHERE t.TradeNum > {0} ", seachInfo.SendTourPeopleNumber - 1);
break;
case EyouSoft.Model.EnumType.FinanceStructure.QueryOperator.等于:
strSql = strSql + String.Format(" WHERE t.TradeNum = {0} ", seachInfo.SendTourPeopleNumber);
break;
case EyouSoft.Model.EnumType.FinanceStructure.QueryOperator.小于等于:
strSql = strSql + String.Format(" WHERE t.TradeNum < {0} ", seachInfo.SendTourPeopleNumber + 1);
break;
}
DbCommand cmd = this._db.GetSqlStringCommand(strSql);
using (IDataReader rdr = DbHelper.ExecuteReader(cmd, this._db))
{
while (rdr.Read())
{
SoldStat.Add("TradeNum", rdr.GetInt32(rdr.GetOrdinal("TradeNum")));
SoldStat.Add("TradeTourNum", rdr.GetInt32(rdr.GetOrdinal("TradeTourNum")));
}
}
return(SoldStat);
}
/// <summary>
/// 按指定条件获取 人数合计信息
/// </summary>
/// <param name="companyId">公司（专线）编号</param>
/// <param name="pageSize">每页记录数</param>
/// <param name="pageIndex">当前页索引</param>
/// <param name="recordCount">总记录数</param>
/// <param name="seachInfo">查询条件业务实体</param>
/// <returns></returns>
public Dictionary <string, int> GetSoldStatSumInfo(int companyId, int pageSize, int pageIndex, ref int recordCount, EyouSoft.Model.CompanyStructure.MCustomerSoldStatSearchInfo seachInfo)
{
return(idal.GetSoldStatSumInfo(companyId, pageSize, pageIndex, ref recordCount, seachInfo));
}
/// <summary>
/// 按指定条件获取客户资料信息集合
/// </summary>
/// <param name="companyId">公司（专线）编号</param>
/// <param name="pageSize">每页记录数</param>
/// <param name="pageIndex">当前页索引</param>
/// <param name="recordCount">总记录数</param>
/// <param name="seachInfo">查询条件业务实体</param>
/// <returns></returns>
public IList <EyouSoft.Model.CompanyStructure.CustomerInfo> GetCustomers(int companyId, int pageSize, int pageIndex, ref int recordCount, EyouSoft.Model.CompanyStructure.MCustomerSoldStatSearchInfo seachInfo)
{
return(idal.GetCustomers(companyId, pageSize, pageIndex, ref recordCount, seachInfo));
}