/// <summary>
/// 区间查询
/// </summary>
/// <param name="info"></param>
/// <returns></returns>
public List <FundsStat> GetTimesStatistics(StatisticsSearch info)
{
StringBuilder sql = new StringBuilder();
sql.Append("select isnull(sum(r_bill_amount),0) as hasUsed,r_funds_id as id,COUNT(r_funds_id) as applyNum,f_name as name");
sql.Append(" from Reimbursements");
sql.Append(" inner join Funds on f_id=r_funds_id");
sql.Append(" where r_bill_state=1");
if (info.fund > 0)
{
sql.Append(" and f_id=").Append(info.fund);
}
if (info.manager > 0)
{
sql.Append(" and f_manager=").Append(info.manager);
}
if (info.userId > 0)
{
sql.Append(" and r_add_user_id=").Append(info.userId);
}
if (info.beginDate != null)
{
sql.Append(" and r_add_date>='").Append(((DateTime)info.beginDate).ToString("yyyy-MM-dd 00:00:00")).Append("'");
}
if (info.endDate != null)
{
sql.Append(" and r_add_date<='").Append(((DateTime)info.endDate).ToString("yyyy-MM-dd 23:59:59.999")).Append("'");
}
sql.Append(" group by r_funds_id,f_name order by r_funds_id");
return(db.Database.SqlQuery <FundsStat>(sql.ToString()).ToList());
}
public ActionResult Detail(StatisticsSearch search)
{
if (!User.Identity.IsAuthenticated)
{
return(RedirectToRoute(new { controller = "Login", action = "LogOut" }));
}
int user = PageValidate.FilterParam(User.Identity.Name);
setSearchSelect(user);
if (!RoleCheck.CheckHasAuthority(user, db, "经费管理"))
{
search.manager = user;
search.userId = user;
}
if (search.userId == null)
{
search.userId = 0;
}
ApplyManager dal = new ApplyManager(db);
var query = dal.GetReimbursement("", (int)search.userId).Where(x => x.state == 1);
if (search.manager > 0)
{
query = query.Where(x => x.manager == search.manager);
}
if (search.beginDate != null)
{
search.beginDate = DateTime.Parse(((DateTime)search.beginDate).ToString("yyyy-MM-dd 00:00:00.000"));
query = query.Where(x => x.time >= search.beginDate);
}
if (search.endDate != null)
{
search.endDate = DateTime.Parse(((DateTime)search.endDate).ToString("yyyy-MM-dd 23:59:59.999"));
query = query.Where(x => x.time <= search.endDate);
}
search.Amount = query.Count();
query = query.OrderByDescending(x => x.time).Skip(search.PageSize * (search.PageIndex - 1)).Take(search.PageSize);
var list = query.ToList();
foreach (var item in list)
{
item.userName = AESEncrypt.Decrypt(item.userName);
item.attachmentsCount = (from content in db.Reimbursement_Content
join detail in db.Reimbursement_Detail on content.content_id equals detail.detail_content_id
where content.c_reimbursement_code == item.reimbursementCode
select content.content_id).Count();
}
ViewData["Details"] = list;
return(View(search));
}
/// <summary>
/// 统计
/// </summary>
/// <param name="info"></param>
/// <returns></returns>
public List <FundsStat> GetFundsStatistics(StatisticsSearch info)
{
StringBuilder sql = new StringBuilder();
sql.Append("select f_id as id,f_name as name,f_amount as amount,f_balance as balance");
sql.Append(",isnull((select sum(r_fact_amount) from Reimbursements where r_funds_id=f_id and r_bill_state=1),0) as hasUsed");
sql.Append(",(select count(1) from Reimbursements where r_funds_id=f_id and r_bill_state=1) as applyNum");
sql.Append(" from Funds where 1=1");
if (info.fund > 0)
{
sql.Append(" and f_id=").Append(info.fund);
}
sql.Append(" order by f_id");
return(db.Database.SqlQuery <FundsStat>(sql.ToString()).ToList());
}
public ActionResult TimesStaticstics(StatisticsSearch search)
{
if (!User.Identity.IsAuthenticated)
{
return(RedirectToRoute(new { controller = "Login", action = "LogOut" }));
}
int user = PageValidate.FilterParam(User.Identity.Name);
setSearchSelect(user);
if (!RoleCheck.CheckHasAuthority(user, db, "统计"))
{
return(RedirectToRoute(new { controller = "Error", action = "Index", err = "没有权限。" }));
}
Statistics dal = new Statistics(db);
var query = dal.GetTimesStatistics(search);
ViewData["StatData"] = query;
return(View(search));
}
public ActionResult Logs(StatisticsSearch search)
{
if (!User.Identity.IsAuthenticated)
{
return(RedirectToRoute(new { controller = "Login", action = "LogOut" }));
}
int user = PageValidate.FilterParam(User.Identity.Name);
List <SelectOption> options = DropDownList.UserSelect(user);
ViewData["Users"] = DropDownList.SetDropDownList(options);
var query = from log in db.Sys_Log
join t in db.Dic_Log_Type on log.log_type equals t.dlt_log_id into T
from t1 in T.DefaultIfEmpty()
join u in db.User_Info on log.log_user_id equals u.user_id into U
from u1 in U.DefaultIfEmpty()
select new ViewLogsModel
{
uid = log.log_user_id,
user = u1.real_name,
info = log.log_content,
ip = log.log_ip,
id = log.log_id,
time = log.log_time,
device = log.log_device,
target = log.log_target,
targetStr = log.log_target,
type = log.log_type,
typeStr = t1.dlt_log_name
};
if (!RoleCheck.CheckHasAuthority(user, db, "经费管理"))
{
query = query.Where(x => x.uid == user);
}
if (search.beginDate != null)
{
search.beginDate = DateTime.Parse(((DateTime)search.beginDate).ToString("yyyy-MM-dd 00:00:00.000"));
query = query.Where(x => x.time >= search.beginDate);
}
if (search.endDate != null)
{
search.endDate = DateTime.Parse(((DateTime)search.endDate).ToString("yyyy-MM-dd 23:59:59.999"));
query = query.Where(x => x.time <= search.endDate);
}
if (search.userId != null && search.userId != 0)
{
query = query.Where(x => x.uid == search.userId);
}
string keyword = PageValidate.InputText(search.KeyWord, 100);
if (!string.IsNullOrEmpty(keyword))
{
query = query.Where(x => x.info.Contains(keyword));
}
//统计总条目
search.Amount = query.Count();
//分页
query = query.OrderByDescending(x => x.time).Skip(search.PageSize * (search.PageIndex - 1)).Take(search.PageSize);
var list = query.ToList();
foreach (var item in list)
{
if (item.type == 2)
{
int uid = PageValidate.FilterParam(item.target);
var t = (from u in db.User_Info
where u.user_id == item.uid
select u.real_name).FirstOrDefault();
if (!string.IsNullOrEmpty(t))
{
item.targetStr = Common.DEncrypt.AESEncrypt.Decrypt(t);
}
}
item.user = Common.DEncrypt.AESEncrypt.Decrypt(item.user);
}
ViewData["Logs"] = list;
return(View(search));
}
