/// <summary>
/// 生成报销单的where条件
/// </summary>
/// <param name="userInfo"></param>
/// <returns></returns>
private string GetWhere(ExpenseAccountSM sm, AccountVM userInfo, bool isFromApprove)
{
var sql = "";
//根据不同的角色类型看到报销单也是不一样的
switch ((RoleType)Enum.ToObject(typeof(RoleType), userInfo.RoleType))
{
case RoleType.Admin:
break;
case RoleType.CompanyManager:
sql += string.Format("and ( b.CompanyId is null or b.CompanyId in ({0}) )", userInfo.CompanyIds);
break;
case RoleType.Area:
sql += " and a.Creater='" + userInfo.UserName + "'";
break;
case RoleType.Staff:
sql += " and ( b.CompanyId in (" + userInfo.CompanyIds + ") or a.Creater='" + userInfo.UserName + "' ) ";
break;
}
//根据不同的角色类型看到报销单也是不一样的
switch ((RoleViewRightType)Enum.ToObject(typeof(RoleViewRightType), userInfo.ViewRightType))
{
case RoleViewRightType.All:
break;
case RoleViewRightType.Owner:
sql += " and a.Creater='" + userInfo.UserName + "' ";
break;
case RoleViewRightType.OwnerAndCompany:
sql += " and ( b.CompanyId in (" + userInfo.CompanyIds + ") or a.Creater='" + userInfo.UserName + "' ) ";
break;
}
//根据角色进行分类的过滤
//老板只能看5大类汇总
//录入人和admin能看所有(admin是拥有父类的权限，录入人只有子类的权限，父类可以同时查看报表)
sql += @" and (b.CateId is null or b.CateId in (select * from dbo.FC_GetRoleChildrenCateIds('" + userInfo.RoleType + "'))) ";
//添加部分特殊分类的逻辑
if (!string.IsNullOrEmpty(userInfo.CateIds))
{
sql += @" and b.CateId in(" + userInfo.CateIds + ") ";
}
//审核的是不能看到草稿箱的
if (isFromApprove)
{
sql += " and a.ApproveStatus != " + (int)ExpenseAccountApproveStatus.Created;
}
sql += sm.SearchSql;
return(sql);
}
public async Task <ActionResult> ToExcel(ExpenseAccountSM Sm)
{
var Dtos = expenseAccountRepo.GetExcelListByDto(Sm, ViewHelp.UserInfo());
var Vms = Mapper.Map <List <ExpenseAccountExcelVM> >(Dtos);
var FilePath = ExpenseAccountManager.Instance.ToExcel(Vms);
Log(Vms, "路径:" + FilePath);
return(File(new FileStream(FilePath, FileMode.Open), "application/octet-stream", "报销清单_" + ViewHelp.GetUserName() + "_" + DateTime.Now.ToString("yyyyMMddss") + ".xls"));
}
public PagedResult <ExpenseAccountListDTO> GetListByDto(ExpenseAccountSM sm, AccountVM UserInfo, int Page, int PageSize, bool IsFromApprove = false)
{
var sql = @" select distinct @CompanyId SearchCompanyId,a.EANumber, a.Id,a.ApproveStatus,a.ModifyDate,a.Name,a.SumMoney ,a.ApplyDate,a.Creater ,a.IsNotAccount,a.IsPublic from EM_ExpenseAccount a
left join EM_ExpenseAccount_Detail b on a.Id=b.ExpenseAccountId
where 1=1 ";
sql += GetWhere(sm, UserInfo, IsFromApprove);
var list = DapperHelper.QueryWithPage <ExpenseAccountListDTO>(sql, sm, " ModifyDate desc ", Page, PageSize);
return(list);
}
public List <ExpenseAccountExcelDTO> GetExcelListByDto(ExpenseAccountSM sm, AccountVM UserInfo)
{
var sql = @" select b.OccurDate,a.Name,a.EANumber,c.CateName,b.Remark,b.Money from EM_ExpenseAccount a
left join EM_ExpenseAccount_Detail b on a.Id=b.ExpenseAccountId
left join EM_Charge_Cate c on b.CateId=c.Id
where 1=1 ";
sql += GetWhere(sm, UserInfo, false);
sql += " order by a.ModifyDate desc ";
var list = DapperHelper.SqlQuery <ExpenseAccountExcelDTO>(sql, sm).ToList();
return(list);
}
public PagedResult <ExpenseAccountListDTO> GetLostAccountListByDto(ExpenseAccountSM sm, AccountVM UserInfo, int Page, int PageSize)
{
var sql = @" select distinct @CompanyId SearchCompanyId,a.EANumber, a.Id,a.ApproveStatus,a.ModifyDate,a.Name,a.SumMoney ,a.ApplyDate,a.Creater ,a.IsNotAccount,a.IsPublic from EM_ExpenseAccount a
left join EM_ExpenseAccount_Detail b on a.Id=b.ExpenseAccountId
where b.id is null ";
if (UserInfo.RoleType != (int)RoleType.Admin)
{
sql += string.Format(" and a.Creater='{0}'" + UserInfo.UserName);
}
var list = DapperHelper.QueryWithPage <ExpenseAccountListDTO>(sql, sm, " ModifyDate desc ", Page, PageSize);
return(list);
}
public async Task <ActionResult> Index(ExpenseAccountSM Sm, int Page = 1, int PageSize = 20)
{
var Dtos = expenseAccountRepo.GetListByDto(Sm, ViewHelp.UserInfo(), Page, PageSize);
var Vms = new PagedResult <ExpenseAccountListVM>()
{
CurrentPage = Dtos.CurrentPage,
PageSize = Dtos.PageSize,
RowCount = Dtos.RowCount,
Stats = Dtos.Stats
};
Vms.Results = Mapper.Map <IList <ExpenseAccountListDTO>, IList <ExpenseAccountListVM> >(Dtos.Results);
if (Request.IsAjaxRequest())
{
return(PartialView("_List", Vms));
}
InitSearchSelect();
return(View(Vms));
}
public async Task <ActionResult> ApproveIndex(ExpenseAccountSM Sm, int Page = 1, int PageSize = 20)
{
if (!Request.IsAjaxRequest())
{
Sm.ApproveStatus = (int)ExpenseAccountApproveStatus.WaitingApprove;
}
var Dtos = expenseAccountRepo.GetListByDto(Sm, ViewHelp.UserInfo(), Page, PageSize, true);
var Vms = new PagedResult <ExpenseAccountListVM>()
{
CurrentPage = Dtos.CurrentPage,
PageSize = Dtos.PageSize,
RowCount = Dtos.RowCount,
Stats = Dtos.Stats
};
Vms.Results = Mapper.Map <IList <ExpenseAccountListDTO>, IList <ExpenseAccountListVM> >(Dtos.Results);
if (Request.IsAjaxRequest())
{
return(PartialView("_ApproveList", Vms));
}
InitSearchSelect(true, Sm.ApproveStatus.Value);
return(View(Vms));
}
public async Task <ActionResult> CompanyLimitDetail(ExpenseAccountSM Sm, int Page = 1, int PageSize = 20)
{
DateTime sDate, eDate;
Sm.Year.GetDateByYear(out sDate, out eDate);
Sm.SDate = sDate;
Sm.EDate = eDate;
if (!Sm.CompanyId.HasValue)
{
var CompanyList = companyRepo.GetList(ViewHelp.GetRoleId());
Sm.CompanyId = CompanyList.FirstOrDefault().Key.ToInt();
}
if (!Sm.CateId.HasValue)
{
var CateList = changeCateRepo.GetList(ViewHelp.GetRoleType(), CateDropType.Search, ViewHelp.GetCateIds());
Sm.CateId = CateList.FirstOrDefault().Key.ToInt();
}
Sm.IsNotAccount = 0;
Sm.ApproveStatus = (int)ExpenseAccountApproveStatus.PassApproved;
var Dtos = expenseAccountRepo.GetListByDto(Sm, ViewHelp.UserInfo(), Page, PageSize);
var Vms = new PagedResult <ExpenseAccountListVM>()
{
CurrentPage = Dtos.CurrentPage,
PageSize = Dtos.PageSize,
RowCount = Dtos.RowCount,
Stats = Dtos.Stats
};
ViewBag.limit = companyLimitRepo.GetCompanyLimit(Sm.CompanyId.Value, Sm.CateId.Value, Sm.Year);
Vms.Results = Mapper.Map <IList <ExpenseAccountListDTO>, IList <ExpenseAccountListVM> >(Dtos.Results);
if (Request.IsAjaxRequest())
{
return(PartialView("_CompanyLimitDetailList", Vms));
}
CompanyLimitInitSearchSelect();
return(View(Vms));
}
