/// <summary>
/// 获取回款率统计集合，统计截止时间前beforeDays天的回款率信息
/// </summary>
/// <param name="companyId">公司编号</param>
/// <param name="tongJiShiJianE">统计截止时间</param>
/// <param name="beforeDays">统计截止时间前N天</param>
/// <param name="chaXun">查询信息</param>
/// <returns></returns>
public IList <EyouSoft.Model.StatisticStructure.HuiKuanLvInfo> GetHuiKuanLv(int companyId, DateTime tongJiShiJianE, int beforeDays, EyouSoft.Model.StatisticStructure.HuiKuanLvChaXunInfo chaXun)
{
if (beforeDays > 31)
{
beforeDays = 31;
}
if (beforeDays < 1)
{
beforeDays = 1;
}
DateTime now = DateTime.Now;
tongJiShiJianE = tongJiShiJianE.Date;
if (chaXun == null)
{
chaXun = new EyouSoft.Model.StatisticStructure.HuiKuanLvChaXunInfo();
chaXun.LSDate = new DateTime(now.Year, now.Month, 1);
chaXun.LEDate = chaXun.LSDate.Value.AddMonths(1).AddDays(-1);
chaXun.OperatorDepartIds = null;
chaXun.OperatorIds = null;
chaXun.SFBHWeiShenHe = true;
chaXun.KeHuDanWei = null;
}
if (chaXun.LSDate.HasValue && chaXun.LEDate.HasValue && chaXun.LSDate.Value > chaXun.LEDate)
{
var _tmp = chaXun.LSDate;
chaXun.LSDate = chaXun.LEDate;
chaXun.LEDate = _tmp;
}
IList <EyouSoft.Model.StatisticStructure.HuiKuanLvInfo> items = new List <EyouSoft.Model.StatisticStructure.HuiKuanLvInfo>();
for (int i = beforeDays; i >= 0; i--)
{
items.Add(dal.GetHuiKuanLv(companyId, tongJiShiJianE.AddDays(-i), chaXun));
}
items.Add(dal.GetHuiKuanLv(companyId, now, chaXun));
return(items);
}
/// <summary>
/// init huikuanlv
/// </summary>
private void InitHuiKuanLv()
{
EyouSoft.Model.StatisticStructure.HuiKuanLvChaXunInfo searchInfo = new EyouSoft.Model.StatisticStructure.HuiKuanLvChaXunInfo();
searchInfo.KeHuDanWei = Utils.GetQueryStringValue("kehumingcheng");
searchInfo.LSDate = Utils.GetDateTimeNullable(Utils.GetQueryStringValue("lsdate"));
searchInfo.LEDate = Utils.GetDateTimeNullable(Utils.GetQueryStringValue("ledate"));
searchInfo.OperatorDepartIds = Utils.GetIntArray(Utils.GetQueryStringValue("departids"), ",");
searchInfo.OperatorIds = Utils.GetIntArray(Utils.GetQueryStringValue("sellerids"), ",");
searchInfo.SFBHWeiShenHe = new EyouSoft.BLL.CompanyStructure.CompanySetting().GetHuiKuanLvSFBHWeiShenHe(CurrentUserCompanyID);
string departNames = Utils.GetQueryStringValue("departnames");
string sellerNames = Utils.GetQueryStringValue("sellernames");
DateTime now = DateTime.Now;
if (!searchInfo.LSDate.HasValue && !searchInfo.LEDate.HasValue)
{
searchInfo.LSDate = new DateTime(now.Year, now.Month, 1);
searchInfo.LEDate = searchInfo.LSDate.Value.AddMonths(1).AddDays(-1);
}
//设置查询model的ComputeOrderType值
EyouSoft.Model.EnumType.CompanyStructure.ComputeOrderType?tongJiDingDanFangShi = new EyouSoft.BLL.CompanyStructure.CompanySetting().GetComputeOrderType(SiteUserInfo.CompanyID);
if (tongJiDingDanFangShi.HasValue)
{
searchInfo.TongJiDingDanFangShi = tongJiDingDanFangShi.Value;
}
EyouSoft.BLL.StatisticStructure.HuiKuanLv bll = new EyouSoft.BLL.StatisticStructure.HuiKuanLv();
var items = bll.GetHuiKuanLv(CurrentUserCompanyID, now, 13, searchInfo);
bll = null;
rptHuiKuanLv.DataSource = items;
rptHuiKuanLv.DataBind();
StringBuilder s = new StringBuilder();
if (searchInfo.LSDate.HasValue && searchInfo.LEDate.HasValue)
{
s.AppendFormat("出团时间从{0}至{1}", searchInfo.LSDate.Value.ToString("yyyy-MM-dd"), searchInfo.LEDate.Value.ToString("yyyy-MM-dd"));
}
else if (searchInfo.LSDate.HasValue)
{
s.AppendFormat("出团时间在{0}以后", searchInfo.LSDate.Value.ToString("yyyy-MM-dd"));
}
else if (searchInfo.LEDate.HasValue)
{
s.AppendFormat("出团时间在{0}以前", searchInfo.LEDate.Value.ToString("yyyy-MM-dd"));
}
if (searchInfo.OperatorDepartIds != null && searchInfo.OperatorDepartIds.Length > 0)
{
s.AppendFormat("，部门：{0}", departNames);
}
if (searchInfo.OperatorIds != null && searchInfo.OperatorIds.Length > 0)
{
s.AppendFormat("，销售员：{0}", sellerNames);
}
if (!string.IsNullOrEmpty(searchInfo.KeHuDanWei))
{
s.AppendFormat("，客户单位：{0}", searchInfo.KeHuDanWei);
}
s.Append("。回款率信息如下：");
ltrHuiKuanLvBiaoTi.Text = s.ToString();
}
//static constants
#endregion
#region public members
/// <summary>
/// 获取回款率分析信息
/// </summary>
/// <param name="companyId">公司编号</param>
/// <param name="tongJiShiJian">统计时间</param>
/// <param name="chaXun">查询信息</param>
/// <returns></returns>
public EyouSoft.Model.StatisticStructure.HuiKuanLvInfo GetHuiKuanLv(int companyId, DateTime tongJiShiJian, EyouSoft.Model.StatisticStructure.HuiKuanLvChaXunInfo chaXun)
{
StringBuilder cmdText = new StringBuilder();
EyouSoft.Model.StatisticStructure.HuiKuanLvInfo info = null;
#region SQL
cmdText.Append("SELECT SUM(YingShouKuan) AS YingShouKuan,SUM(YiShouKuan) AS YiShouKuan,SUM(YiTuiKuan) AS YiTuiKuan FROM(");
cmdText.Append(" SELECT A.FinanceSum AS YingShouKuan ");
cmdText.AppendFormat(" ,(SELECT ISNULL(SUM(RefundMoney),0) FROM tbl_ReceiveRefund AS B WHERE B.ItemId=A.Id AND B.ItemType=1 AND B.IsReceive=1 {0} AND B.IssueTime<='{1}') AS YiShouKuan ", chaXun.SFBHWeiShenHe ? "" : " AND B.IsCheck=1 ", tongJiShiJian);
cmdText.AppendFormat(" ,(SELECT ISNULL(SUM(RefundMoney),0) FROM tbl_ReceiveRefund AS B WHERE B.ItemId=A.Id AND B.ItemType=1 AND B.IsReceive=0 AND B.IsCheck=1 AND B.IssueTime<='{0}' ) AS YiTuiKuan ", tongJiShiJian);
cmdText.AppendFormat(" FROM tbl_TourOrder AS A WHERE IsDelete='0' AND SellCompanyId={0} ", companyId);
if (!string.IsNullOrEmpty(chaXun.KeHuDanWei))
{
cmdText.AppendFormat(" AND BuyCompanyName LIKE '%{0}%' ", chaXun.KeHuDanWei);
}
if (chaXun.OperatorIds != null && chaXun.OperatorIds.Length > 0)
{
cmdText.AppendFormat(" AND PerTimeSellerId IN({0}) ", Utils.GetSqlIdStrByList(chaXun.OperatorIds));
}
if (chaXun.OperatorDepartIds != null && chaXun.OperatorDepartIds.Length > 0)
{
cmdText.AppendFormat(" AND PerTimeSellerId IN(SELECT Id FROM tbl_CompanyUser WHERE DepartId IN({0})) ", Utils.GetSqlIdStrByList(chaXun.OperatorDepartIds));
}
if (chaXun.LSDate.HasValue || chaXun.LEDate.HasValue)
{
cmdText.Append(" AND EXISTS(SELECT 1 FROM tbl_Tour AS C WHERE C.TourId=A.TourId ");
if (chaXun.LSDate.HasValue)
{
cmdText.AppendFormat(" AND C.LeaveDate>='{0}' ", chaXun.LSDate.Value);
}
if (chaXun.LEDate.HasValue)
{
cmdText.AppendFormat(" AND C.LeaveDate<='{0}' ", chaXun.LEDate.Value);
}
cmdText.Append(" ) ");
}
if (chaXun.TongJiDingDanFangShi.HasValue)
{
switch (chaXun.TongJiDingDanFangShi.Value)
{
case EyouSoft.Model.EnumType.CompanyStructure.ComputeOrderType.统计确认成交订单:
cmdText.AppendFormat(" AND OrderState = {0} ", (int)Model.EnumType.TourStructure.OrderState.已成交);
break;
case EyouSoft.Model.EnumType.CompanyStructure.ComputeOrderType.统计有效订单:
cmdText.AppendFormat(" AND OrderState IN({0},{1},{2}) ", (int)Model.EnumType.TourStructure.OrderState.未处理, (int)Model.EnumType.TourStructure.OrderState.已成交, (int)Model.EnumType.TourStructure.OrderState.已留位);
break;
}
}
cmdText.Append(" ) D ");
#endregion
DbCommand cmd = _db.GetSqlStringCommand(cmdText.ToString());
using (IDataReader rdr = DbHelper.ExecuteReader(cmd, _db))
{
if (rdr.Read())
{
decimal yiTuiKuan = 0;
info = new EyouSoft.Model.StatisticStructure.HuiKuanLvInfo();
info.TongJiShiJian = tongJiShiJian;
info.YingShouKuan = rdr.IsDBNull(rdr.GetOrdinal("YingShouKuan")) ? 0 : rdr.GetDecimal(rdr.GetOrdinal("YingShouKuan"));
info.YiShouKuan = rdr.IsDBNull(rdr.GetOrdinal("YiShouKuan")) ? 0 : rdr.GetDecimal(rdr.GetOrdinal("YiShouKuan"));
yiTuiKuan = rdr.IsDBNull(rdr.GetOrdinal("YiTuiKuan")) ? 0 : rdr.GetDecimal(rdr.GetOrdinal("YiTuiKuan"));
info.YiShouKuan = info.YiShouKuan - yiTuiKuan;
}
}
return(info);
}