/// <summary>
/// 作用：显示转账支出的报销单
/// </summary>
/// <param name="coding"></param>
/// <param name="checkkey"></param>
/// <param name="time"></param>
/// <param name="minMoney"></param>
/// <param name="maxMoney"></param>
/// <param name="creater"></param>
/// <param name="order"></param>
/// <param name="page"></param>
/// <param name="rows"></param>
/// <returns></returns>
public ActionResult Bank(
string coding = null, string checkkey = null,
string time = null, double?minMoney = null, double?maxMoney = null,
string creater = null, Order order = Order.Time, int?year = null, int?month = null,
int page = 1, int rows = 20)
{
var parameter = new SheetVerifyParameter
{
Coding = coding,
CheckKey = checkkey,
Time = time,
Year = year,
Month = month,
MinMoney = minMoney,
MaxMoney = maxMoney,
Creater = creater,
Order = order,
Checker = Identity.Name,
//Page = new PageParameter(page, rows)
};
var stringKey = Identity.UserID + ParameterManager.ParameterKey;
SessionHelper.SetSession(stringKey, parameter);
//RedisManager.Set(, parameter, RedisManager.Client);
parameter.Page = new PageParameter(page, rows);
var list = Core.VerifyViewManager.Search(parameter, true);
ViewBag.List = list;
ViewBag.Parameter = parameter;
return(View());
}
/// <summary>
/// 作用：只查看除了转账支出的报销单
/// </summary>
/// <param name="Coding"></param>
/// <param name="CheckKey"></param>
/// <param name="Time"></param>
/// <param name="MinMoney"></param>
/// <param name="MaxMoney"></param>
/// <param name="Creater"></param>
/// <param name="order"></param>
/// <param name="page"></param>
/// <param name="sheetType"></param>
/// <param name="content"></param>
/// <returns></returns>
public ActionResult Review(
string Coding = null, string CheckKey = null,
string Time = null,
double?MinMoney = null, double?MaxMoney = null,
string Creater = null, Order order = Order.Time, int page = 1, string sheetType = null, string content = null)
{
var parameter = new SheetVerifyParameter()
{
Page = new PageParameter(page, 20),
Coding = Coding,
CheckKey = CheckKey,
Time = Time,
MinMoney = MinMoney,
MaxMoney = MaxMoney,
Creater = Creater,
Order = order,
Checker = Identity.Name,
Content = content
};
if (!string.IsNullOrEmpty(sheetType))
{
parameter.SheetType = EnumHelper.GetEnum <SheetType>(sheetType);
}
ViewBag.List = Core.VerifyViewManager.Search(parameter);
ViewBag.Parameter = parameter;
return(View());
}
public ActionResult DownloadExcel(
string name = null, string coding = null,
string checkKey = null, string time = null,
double?minMoney = null, double?maxmoney = null,
string creater = null, Order order = Order.Time,
string sheetType = null, string content = null, string month = null)
{
var parameter = new SheetVerifyParameter()
{
Coding = coding,
Time = time,
MinMoney = minMoney,
MaxMoney = maxmoney,
Creater = creater,
Order = order,
Checker = name,
Content = content,
CheckKey = checkKey
};
if (!string.IsNullOrEmpty(content))
{
var reporttype = Core.Report_TypeManager.Get(content);
if (reporttype != null)
{
parameter.RID = reporttype.ID;
}
}
if (!string.IsNullOrEmpty(month))
{
var array = month.Replace("年", ";").Replace("月", ";").Split(';');
var starttime = Convert.ToDateTime(string.Format("{0}-{1}-01 00:00:00", array[0], array[1]));
parameter.Year = int.Parse(array[0]);
parameter.Month = int.Parse(array[1]);
//parameter.StartTime = starttime;
//parameter.EndTime = starttime.AddMonths(1);
}
if (!string.IsNullOrEmpty(sheetType))
{
parameter.SheetType = EnumHelper.GetEnum <SheetType>(sheetType);
}
//var list = Core.VerifyViewManager.Search(parameter);
var list = Core.VerifyManager.GetSheetByVerify(parameter);
IWorkbook workbook = Core.VerifyManager.GetWorkbook(list);
MemoryStream ms = new MemoryStream();
workbook.Write(ms);
ms.Flush();
byte[] fileContents = ms.ToArray();
return(File(fileContents, "application/ms-excel", DateTime.Now.ToLongDateString() + ".xls"));
}
public List <Sheet> GetSheetByVerify(SheetVerifyParameter parameter)
{
var checks = GetSheetByVerify(parameter.Checker, parameter.StartTime, parameter.EndTime, parameter.Year, parameter.Month);
var query = checks.AsQueryable();
if (!string.IsNullOrEmpty(parameter.Coding))
{
query = query.Where(e => e.PrintNumber.Contains(parameter.Coding));
}
if (!string.IsNullOrEmpty(parameter.CheckKey))
{
query = query.Where(e => e.CheckNumber.Contains(parameter.CheckKey));
}
if (parameter.MinMoney.HasValue && parameter.MinMoney.Value > 0)
{
query = query.Where(e => e.Money >= parameter.MinMoney.Value);
}
if (parameter.MaxMoney.HasValue && parameter.MaxMoney.Value > 0)
{
query = query.Where(e => e.Money <= parameter.MaxMoney.Value);
}
if (!string.IsNullOrEmpty(parameter.Creater))
{
query = query.Where(e => e.Name.Contains(parameter.Creater));
}
if (!string.IsNullOrEmpty(parameter.Time))
{
var currentTime = DateTime.Now;
switch (parameter.Time)
{
case "一周内":
currentTime = currentTime.AddDays(7);
break;
case "一个月内":
currentTime = currentTime.AddMonths(1);
break;
case "半年内":
currentTime = currentTime.AddMonths(6);
break;
case "一年内":
currentTime = currentTime.AddYears(1);
break;
}
query = query.Where(e => e.Time <= currentTime);
}
if (parameter.SheetType.HasValue)
{
query = query.Where(e => e.Type == parameter.SheetType.Value);
if (!string.IsNullOrEmpty(parameter.Content))
{
if (parameter.SheetType.Value == SheetType.Daily)
{
if (parameter.RID.HasValue)
{
query = query.Where(e => e.Substances.Any(k => k.RID == parameter.RID.Value));
}
//if (parameter.SRID.HasValue)
//{
// query = query.Where(e => e.Substances.Any(k => k.SRID == parameter.SRID.Value));
//}
//try
//{
// var category = EnumHelper.GetEnum<Category>(parameter.Content);
// query = query.Where(e => e.Substances.Any(k => k.Category == category));
//}
//catch
//{
//}
}
else if (parameter.SheetType.Value == SheetType.Errand)
{
query = query.Where(e => (!string.IsNullOrEmpty(e.Remarks) && e.Remarks.Contains(parameter.Content)) || (!string.IsNullOrEmpty(e.Evection.Place) && e.Evection.Place.Contains(parameter.Content)) || (!string.IsNullOrEmpty(e.Evection.Reason) && e.Evection.Reason.Contains(parameter.Content)));
}
}
}
switch (parameter.Order)
{
case Order.Time:
query = query.OrderByDescending(e => e.Time);
break;
case Order.Money:
query = query.OrderByDescending(e => e.Money);
break;
case Order.PrintNumber:
query = query.OrderByDescending(e => e.PrintNumber);
break;
case Order.CheckNumber:
query = query.OrderByDescending(e => e.CheckNumber);
break;
}
query = query.SetPage(parameter.Page);
return(query.ToList());
}
public List <VerifyView> Search(SheetVerifyParameter parameter, bool IsTransfer = false)
{
using (var db = GetDbContext())
{
var query = db.VerifyViews.Where(e => e.Deleted == false && e.Step != Step.Create).AsQueryable();
if (parameter.Year.HasValue)
{
query = query.Where(e => e.SheetTime.Year == parameter.Year.Value);
}
if (parameter.Month.HasValue)
{
query = query.Where(e => e.SheetTime.Month == parameter.Month.Value);
}
if (parameter.Position.HasValue)
{
query = query.Where(e => e.Position == parameter.Position.Value);
}
if (!string.IsNullOrEmpty(parameter.Checker))
{
query = query.Where(e => e.Name == parameter.Checker);
}
if (parameter.StartTime.HasValue)
{
query = query.Where(e => e.Time >= parameter.StartTime.Value);
}
if (parameter.EndTime.HasValue)
{
query = query.Where(e => e.Time <= parameter.EndTime.Value);
}
if (!string.IsNullOrEmpty(parameter.Coding))
{
query = query.Where(e => e.PrintNumber.Contains(parameter.Coding));
}
//if (!string.IsNullOrEmpty(parameter.Coding))
//{
// query = query.Where(e => e.Coding.ToLower().Contains(parameter.Coding.ToLower()));
//}
if (parameter.MinMoney.HasValue)
{
query = query.Where(e => e.Money >= parameter.MinMoney.Value);
}
if (parameter.MaxMoney.HasValue)
{
query = query.Where(e => e.Money <= parameter.MaxMoney.Value);
}
if (!string.IsNullOrEmpty(parameter.Creater))
{
query = query.Where(e => e.SheetName.ToLower().Contains(parameter.Creater.ToLower()));
}
if (!string.IsNullOrEmpty(parameter.Time))
{
var currentTime = DateTime.Now;
switch (parameter.Time)
{
case "一周内":
currentTime = currentTime.AddDays(-7);
break;
case "一个月内":
currentTime = currentTime.AddMonths(-1);
break;
case "半年内":
currentTime = currentTime.AddMonths(-6);
break;
case "一年内":
currentTime = currentTime.AddYears(-1);
break;
}
query = query.Where(e => e.SheetTime >= currentTime);
}
if (IsTransfer == true)
{
query = query.Where(e => e.Type == SheetType.Transfer);
}
else
{
query = query.Where(e => e.Type != SheetType.Transfer);
}
if (parameter.SheetType.HasValue)
{
query = query.Where(e => e.Type == parameter.SheetType.Value);
}
if (parameter.RID.HasValue)
{
query = query.Where(e => e.RID.HasValue && e.RID.Value == parameter.RID.Value);
}
if (parameter.SRID.HasValue)
{
query = query.Where(e => e.SRID.HasValue && e.SRID.Value == parameter.SRID.Value);
}
if (!string.IsNullOrEmpty(parameter.Content))
{
query = query.Where(e => (!string.IsNullOrEmpty(e.Remarks) && e.Remarks.Contains(parameter.Content)) || (!string.IsNullOrEmpty(e.Place) && e.Place.Contains(parameter.Content)));
}
//var list = query.ToList();
//query = list.GroupBy(e=>e.PrintNumber).Select(e=>e.First()).AsQueryable();
if (!string.IsNullOrEmpty(parameter.CheckKey))
{
query = query.Where(e => e.CheckNumber.Contains(parameter.CheckKey));
}
var list = query.ToList();
query = list.OrderBy(e => e.ID).GroupBy(e => e.PrintNumber).Select(e => e.First()).AsQueryable();
switch (parameter.Order)
{
case Order.Time:
query = query.OrderByDescending(e => e.Time);
break;
case Order.Money:
query = query.OrderByDescending(e => e.Money);
break;
case Order.PrintNumber:
query = query.OrderByDescending(e => e.PrintNumber);
break;
case Order.CheckNumber:
query = query.OrderByDescending(e => e.CheckNumber);
break;
}
query = query.SetPage(parameter.Page);
return(query.ToList());
}
}
