/// <summary>
/// 统计中心-获取欠款金额汇总信息(统计分析-支出对账单总支出、已付、未付明细列表合计)
/// </summary>
/// <param name="searchModel">查询实体</param>
/// <param name="totalAmount">总支出</param>
/// <param name="arrear">未付</param>
/// <returns></returns>
public void GetArrearAllMoeny(ArrearSearchInfo searchModel, ref decimal totalAmount, ref decimal arrear)
{
if (searchModel == null)
{
return;
}
searchModel = new ArrearSearchInfo()
{
AreaId = searchModel.AreaId,
CompanyId = searchModel.CompanyId,
IsAccount = searchModel.IsAccount,
LeaveDate1 = searchModel.LeaveDate1,
LeaveDate2 = searchModel.LeaveDate2,
OperateId = searchModel.OperateId,
SalerId = searchModel.SalerId,
SeachType = searchModel.SeachType,
SupplierId = searchModel.SupplierId,
SupplierName = searchModel.SupplierName,
TourType = searchModel.TourType
};
if (!searchModel.LeaveDate2.HasValue)
{
searchModel.LeaveDate2 = new DateTime(DateTime.Now.Year + 1, 1, 1).AddMilliseconds(-1);
}
else
{
searchModel.LeaveDate2 = searchModel.LeaveDate2.Value.AddDays(1).AddMilliseconds(-1);
}
if (!searchModel.LeaveDate1.HasValue)
{
searchModel.LeaveDate1 = new DateTime(DateTime.Now.Year, 1, 1);
}
dal.GetArrearAllMoeny(searchModel, HaveUserIds, ref totalAmount, ref arrear);
}
/// <summary>
/// 统计中心-获取欠款列表（总支出；已付；未付）(统计分析-支出对账单总支出、已付、未付明细列表)
/// </summary>
/// <param name="SearchModel"></param>
/// <returns></returns>
public IList <ArrearInfo> GetArrear(EyouSoft.Model.PlanStructure.ArrearSearchInfo SearchModel)
{
if (SearchModel != null)
{
SearchModel = new ArrearSearchInfo()
{
AreaId = SearchModel.AreaId,
CompanyId = SearchModel.CompanyId,
IsAccount = SearchModel.IsAccount,
LeaveDate1 = SearchModel.LeaveDate1,
LeaveDate2 = SearchModel.LeaveDate2,
OperateId = SearchModel.OperateId,
SalerId = SearchModel.SalerId,
SeachType = SearchModel.SeachType,
SupplierId = SearchModel.SupplierId,
SupplierName = SearchModel.SupplierName,
TourType = SearchModel.TourType
};
if (!SearchModel.LeaveDate2.HasValue)
{
SearchModel.LeaveDate2 = new DateTime(DateTime.Now.Year + 1, 1, 1).AddMilliseconds(-1);
}
else
{
SearchModel.LeaveDate2 = SearchModel.LeaveDate2.Value.AddDays(1).AddMilliseconds(-1);
}
if (!SearchModel.LeaveDate1.HasValue)
{
SearchModel.LeaveDate1 = new DateTime(DateTime.Now.Year, 1, 1);
}
}
return(dal.GetArrear(SearchModel, HaveUserIds));
}
/// <summary>
/// 统计中心-获取欠款金额汇总信息(统计分析-支出对账单总支出、已付、未付明细列表合计)
/// </summary>
/// <param name="searchModel">查询实体</param>
/// <param name="haveUserIds">用户Ids</param>
/// <param name="totalAmount">总支出</param>
/// <param name="arrear">未付</param>
/// <returns></returns>
public void GetArrearAllMoeny(ArrearSearchInfo searchModel, string haveUserIds, ref decimal totalAmount, ref decimal arrear)
{
if (searchModel.CompanyId <= 0)
{
return;
}
totalAmount = 0;
arrear = 0;
var searchCon = new StringBuilder();
searchCon.AppendFormat("select TotalAmount,Arrear from View_TravelAndTicketArrear where companyid={0} ", searchModel.CompanyId);
if (searchModel.SeachType == 1) //总支出
{
searchCon.Append(" and TotalAmount <> 0 ");
}
if (searchModel.SeachType == 2) //已付
{
searchCon.Append(" and PayAmount <> 0 ");
}
if (searchModel.SeachType == 3) //未付
{
searchCon.Append(" and arrear <> 0 ");
}
if (searchModel.LeaveDate1 != null)
{
searchCon.AppendFormat(" and leaveDate>='{0}' ", searchModel.LeaveDate1.Value);
}
if (searchModel.LeaveDate1 != null)
{
searchCon.AppendFormat(" and leaveDate<='{0}' ", searchModel.LeaveDate2.Value);
}
if (searchModel.OperateId > 0)
{
searchCon.AppendFormat(" and operateid={0}", searchModel.OperateId);
}
if (searchModel.SalerId > 0)
{
searchCon.AppendFormat(" and salerid={0}", searchModel.SalerId);
}
if (searchModel.AreaId > 0)
{
searchCon.AppendFormat(" and areaid={0}", searchModel.AreaId);
}
if (searchModel.TourType > 0)
{
searchCon.AppendFormat(" and TourType={0}", searchModel.TourType);
}
if (searchModel.SupplierId != null && searchModel.SupplierId.Length > 0)
{
string strIds = string.Empty;
foreach (int i in searchModel.SupplierId)
{
if (i > 0)
{
strIds += i.ToString() + ",";
}
}
strIds = strIds.Trim(',');
if (!string.IsNullOrEmpty(strIds))
{
searchCon.AppendFormat(" and ServerId in ({0})", strIds);
}
}
if (!string.IsNullOrEmpty(searchModel.SupplierName))
{
searchCon.AppendFormat(" and Supplier like '%{0}%' ", searchModel.SupplierName);
}
if (searchModel.IsAccount.HasValue)
{
searchCon.Append(searchModel.IsAccount.Value ? " and Arrear = 0 " : " and Arrear <> 0 ");
}
/*if (!string.IsNullOrEmpty(haveUserIds))
* searchCon.AppendFormat(" and TourOperatorId in ({0}) ", haveUserIds);*/
DbCommand cmd = this._db.GetSqlStringCommand(searchCon.ToString());
using (IDataReader dr = DbHelper.ExecuteReader(cmd, _db))
{
while (dr.Read())
{
if (!dr.IsDBNull(0))
{
totalAmount += dr.GetDecimal(0);
}
if (!dr.IsDBNull(1))
{
arrear += dr.GetDecimal(1);
}
}
}
}
