/// <summary>
/// 获取付款提醒查看明细信息集合
/// </summary>
/// <param name="CompanyId"></param>
/// <param name="ServerId"></param>
/// <param name="Type">1代表地接，2代表票务</param>
/// <param name="searchInfo">查询信息</param>
/// <returns></returns>
public IList <ArrearInfo> GetFuKuanTiXingMingXi(int CompanyId, int ServerId, int ServerType, int PageSize, int PageIndex, ref int RecordCount, EyouSoft.Model.PersonalCenterStructure.FuKuanTiXingChaXun searchInfo)
{
string tableName = "View_TravelAndTicketArrear";
string field = " * ";
string orderBy = " leaveDate ";
//string sqlWhere = string.Format(" arrear <>0 and companyid={0} and serverid={1} and ServerType={2}", CompanyId, ServerId, ServerType);
StringBuilder sqlWhere = new StringBuilder();
sqlWhere.AppendFormat(" arrear <>0 and companyid={0} and serverid={1}", CompanyId, ServerId);
if (searchInfo != null)
{
if (searchInfo.LEDate.HasValue ||
searchInfo.LSDate.HasValue ||
(searchInfo.OperatorDepartIds != null && searchInfo.OperatorDepartIds.Length > 0) ||
(searchInfo.OperatorIds != null && searchInfo.OperatorIds.Length > 0))
{
if (searchInfo.LSDate.HasValue)
{
sqlWhere.AppendFormat(" AND LeaveDate>='{0}' ", searchInfo.LSDate.Value);
}
if (searchInfo.LEDate.HasValue)
{
sqlWhere.AppendFormat(" AND LeaveDate<='{0}' ", searchInfo.LEDate.Value.AddDays(1).AddMilliseconds(-1));
}
if (searchInfo.OperatorIds != null && searchInfo.OperatorIds.Length > 0)
{
sqlWhere.AppendFormat(" AND TourOperatorId IN({0}) ", Utils.GetSqlIdStrByArray(searchInfo.OperatorIds));
}
if (searchInfo.OperatorDepartIds != null && searchInfo.OperatorDepartIds.Length > 0)
{
sqlWhere.AppendFormat(" AND TourOperatorId IN(SELECT Id FROM tbl_CompanyUser WHERE DepartId IN({0})) ", Utils.GetSqlIdStrByArray(searchInfo.OperatorDepartIds));
}
}
}
IList <ArrearInfo> ArrearList = new List <ArrearInfo>();
ArrearInfo model = null;
using (IDataReader rd = DbHelper.ExecuteReader(this._db, PageSize, PageIndex, ref RecordCount, tableName, " tourid ", field, sqlWhere.ToString(), orderBy))
{
while (rd.Read())
{
model = new ArrearInfo();
model.Arrear = rd.GetDecimal(rd.GetOrdinal("Arrear"));
model.LeaveDate = rd.IsDBNull(rd.GetOrdinal("LeaveDate")) ? DateTime.Parse("2000-1-1") : rd.GetDateTime(rd.GetOrdinal("LeaveDate"));
model.RouteName = rd.IsDBNull(rd.GetOrdinal("RouteName")) ? "" : rd.GetString(rd.GetOrdinal("RouteName"));
model.TotalAmount = rd.GetDecimal(rd.GetOrdinal("TotalAmount"));;
model.TourCode = rd.IsDBNull(rd.GetOrdinal("TourCode")) ? "" : rd.GetString(rd.GetOrdinal("TourCode"));
model.TourId = rd.IsDBNull(rd.GetOrdinal("TourId")) ? "" : rd.GetString(rd.GetOrdinal("TourId"));
ArrearList.Add(model);
}
}
return(ArrearList);
}
/// <summary>
/// 统计中心-获取欠款列表（总支出；已付；未付）(统计分析-支出对账单总支出、已付、未付明细列表)
/// </summary>
/// <param name="searchModel">查询实体</param>
/// <param name="haveUserIds">用户Ids</param>
/// <returns></returns>
public IList <ArrearInfo> GetArrear(EyouSoft.Model.PlanStructure.ArrearSearchInfo searchModel, string haveUserIds)
{
if (searchModel.CompanyId <= 0)
{
return(null);
}
StringBuilder searchCon = new StringBuilder();
searchCon.AppendFormat("select * 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());
IList <ArrearInfo> ArrearList = new List <ArrearInfo>();
ArrearInfo model = null;
using (IDataReader rd = DbHelper.ExecuteReader(cmd, this._db))
{
while (rd.Read())
{
model = new ArrearInfo();
model.Arrear = rd.GetDecimal(rd.GetOrdinal("Arrear"));
model.LeaveDate = rd.IsDBNull(rd.GetOrdinal("LeaveDate")) ? DateTime.MinValue : rd.GetDateTime(rd.GetOrdinal("LeaveDate"));
model.RouteName = rd.IsDBNull(rd.GetOrdinal("RouteName")) ? "" : rd.GetString(rd.GetOrdinal("RouteName"));
model.TotalAmount = rd.GetDecimal(rd.GetOrdinal("TotalAmount"));;
model.TourCode = rd.IsDBNull(rd.GetOrdinal("TourCode")) ? "" : rd.GetString(rd.GetOrdinal("TourCode"));
model.TourId = rd.IsDBNull(rd.GetOrdinal("TourId")) ? "" : rd.GetString(rd.GetOrdinal("TourId"));
model.Supplier = rd.IsDBNull(rd.GetOrdinal("Supplier")) ? "" : rd.GetString(rd.GetOrdinal("Supplier"));
model.Operator = rd.IsDBNull(rd.GetOrdinal("Operator")) ? "" : rd.GetString(rd.GetOrdinal("Operator"));
model.TourType = (EyouSoft.Model.EnumType.TourStructure.TourType)rd.GetByte(rd.GetOrdinal("TourType"));
ArrearList.Add(model);
}
}
return(ArrearList);
}
