public DataPack <TravelPaperDto> FindTravelPaper(string buyBusinessmanCode, string buyBusinessmanName, string useOffice,
string startTripNumber, string endTripNumber,
string startTicketNumber, string endTicketNumber,
DateTime?startCreateTime, DateTime?endCreateTime,
DateTime?startVoidTime, DateTime?endVoidTime,
DateTime?startGrantTime, DateTime?endGrantTime,
DateTime?startRecoveryTime, DateTime?endRecoveryTime,
string PageSource,
int?tripStatus, int pageIndex, int pageSize, bool isPager = true, int?[] tripStatuss = null, string OrderId = ""
)
{
string BusinessmanCode = string.Empty;
StringBuilder sbSqlWhere = new StringBuilder();
if (tripStatuss == null)
{
BusinessmanCode = currentUser.Code; //"111";//
sbSqlWhere.AppendFormat(" and BusinessmanCode='{0}' ", BusinessmanCode);
//采购商户号
if (!string.IsNullOrEmpty(buyBusinessmanCode))
{
sbSqlWhere.AppendFormat(" and UseBusinessmanCode='{0}' ", buyBusinessmanCode.Trim());
}
}
else
{
//采购商户号
if (!string.IsNullOrEmpty(currentUser.Code))
{
sbSqlWhere.AppendFormat(" and UseBusinessmanCode='{0}' ", currentUser.Code.Trim());
}
}
//采购商户名
if (!string.IsNullOrEmpty(buyBusinessmanName))
{
sbSqlWhere.AppendFormat(" and UseBusinessmanName='{0}' ", buyBusinessmanName.Trim());
}
//office
if (!string.IsNullOrEmpty(useOffice))
{
sbSqlWhere.AppendFormat(" and UseOffice='{0}' ", useOffice.Trim());
}
////票号段
if (!string.IsNullOrEmpty(startTicketNumber) && !string.IsNullOrEmpty(endTicketNumber))
{
startTicketNumber = startTicketNumber.Replace("-", "").Replace("\'", "").Trim();
endTicketNumber = endTicketNumber.Replace("-", "").Replace("\'", "").Trim();
sbSqlWhere.AppendFormat(" and TicketNumber between '{0}' and '{1}' ", startTicketNumber.Replace("'", ""), endTicketNumber.Replace("'", ""));
}
if (!string.IsNullOrEmpty(startTripNumber) && !string.IsNullOrEmpty(endTripNumber))
{
//行程单号段
sbSqlWhere.AppendFormat(" and TripNumber between '{0}' and '{1}' ", startTripNumber.Replace("'", "").Trim(), endTripNumber.Replace("'", "").Trim());
}
//行程单状态
if (PageSource == "black")//来源空白行程单
{
if (tripStatus != null && tripStatus.HasValue && tripStatus.Value != -1)
{
sbSqlWhere.AppendFormat(" and TripStatus ={0} ", tripStatus.Value);
}
else
{
sbSqlWhere.AppendFormat(" and TripStatus in(3,4) ");
}
}
else
{
if (tripStatus != null && tripStatus.HasValue && tripStatus.Value != -1)
{
sbSqlWhere.AppendFormat(" and TripStatus ={0} ", tripStatus.Value);
}
}
if (startCreateTime != null && startCreateTime.HasValue)
{
sbSqlWhere.AppendFormat(" and PrintTime >='{0}' ", startCreateTime.Value.ToString("yyyy-MM-dd"));
}
if (endCreateTime != null && endCreateTime.HasValue)
{
sbSqlWhere.AppendFormat(" and PrintTime <='{0} 23:59:59' ", endCreateTime.Value.ToString("yyyy-MM-dd"));
}
if (startVoidTime != null && startVoidTime.HasValue)
{
sbSqlWhere.AppendFormat(" and InvalidTime >='{0}' ", startVoidTime.Value.ToString("yyyy-MM-dd"));
}
if (endVoidTime != null && endVoidTime.HasValue)
{
sbSqlWhere.AppendFormat(" and InvalidTime <='{0} 23:59:59' ", endVoidTime.Value.ToString("yyyy-MM-dd"));
}
if (startGrantTime != null && startGrantTime.HasValue)
{
sbSqlWhere.AppendFormat(" and GrantTime >='{0}' ", startGrantTime.Value.ToString("yyyy-MM-dd"));
}
if (endGrantTime != null && endGrantTime.HasValue)
{
sbSqlWhere.AppendFormat(" and GrantTime <='{0} 23:59:59' ", endGrantTime.Value.ToString("yyyy-MM-dd"));
}
if (startRecoveryTime != null && startRecoveryTime.HasValue)
{
sbSqlWhere.AppendFormat(" and BlankRecoveryTime >='{0}' ", startRecoveryTime.Value.ToString("yyyy-MM-dd"));
}
if (endRecoveryTime != null && endRecoveryTime.HasValue)
{
sbSqlWhere.AppendFormat(" and BlankRecoveryTime <='{0} 23:59:59' ", endRecoveryTime.Value.ToString("yyyy-MM-dd"));
}
if (tripStatuss != null && tripStatuss.Count() > 0)//行程单号状态
{
sbSqlWhere.AppendFormat(" and TripStatus in({0}) ", string.Join(",", tripStatuss.ToArray()));
}
if (!string.IsNullOrEmpty(OrderId))//订单号
{
sbSqlWhere.AppendFormat(" and OrderId = '{0}' ", OrderId);
}
string sql = "";
if (isPager)
{
sql = "SELECT TOP " + pageSize + " * " +
"FROM TravelPaper " +
" WHERE " +
"(ID >(SELECT isnull(MAX(ID),0) " +
" FROM (SELECT TOP " + ((pageIndex - 1) * pageSize) + " ID " +
" FROM TravelPaper where 1=1 " + sbSqlWhere.ToString() + " " +
" ORDER BY ID) AS T)) " + sbSqlWhere.ToString() + " " +
" ORDER BY ID ";
}
else
{
sql = "select * from TravelPaper where 1=1 " + sbSqlWhere.ToString() + " Order By ID";
}
DataPack <TravelPaperDto> data = new DataPack <TravelPaperDto>();
List <TravelPaper> TravelPaperList = new List <TravelPaper>();
DbRawSqlQuery dbRawSqlQuery = unitOfWorkRepository.SqlQuery(sql, typeof(TravelPaper));
TravelPaperList = dbRawSqlQuery.Cast <TravelPaper>().ToList();
sql = "select count(*) from TravelPaper where 1=1 " + sbSqlWhere.ToString();
dbRawSqlQuery = unitOfWorkRepository.SqlQuery(sql, typeof(int));
data.TotalCount = dbRawSqlQuery.Cast <int>().FirstOrDefault();
data.List = Mapper.Map <List <TravelPaper>, List <TravelPaperDto> >(TravelPaperList);
return(data);
}