protected WhereConditon ListWhereSql(ListCondition condition)
{
WhereConditon wcd = new WhereConditon();
wcd.DBPara = new Dictionary <string, object>();
EntryModel model = JsonHelper.Deserialize <EntryModel>(condition.ListModelString);
if (model == null)
{
return(wcd);
}
if (DataConvert.ToString(model.AssetsNo) != "")
{
wcd.Sql += @" and T1.assetsNo like '%'+@assetsNo+'%'";
wcd.DBPara.Add("assetsNo", model.AssetsNo);
}
if (DataConvert.ToString(model.AssetsName) != "")
{
wcd.Sql += @" and T1.assetsName like '%'+@assetsName+'%'";
wcd.DBPara.Add("assetsName", model.AssetsName);
}
if (DataConvert.ToString(model.BorrowDate1) != "")
{
wcd.Sql += @" and T1.borrowDate>=@borrowDate1";
wcd.DBPara.Add("borrowDate1", DataConvert.ToString(model.BorrowDate1) + " 00:00:00");
}
if (DataConvert.ToString(model.BorrowDate2) != "")
{
wcd.Sql += @" and T1.borrowDate<=@borrowDate2";
wcd.DBPara.Add("borrowDate2", DataConvert.ToString(model.BorrowDate2) + " 23:59:59");
}
return(wcd);
}
protected override WhereConditon ListWhereSql(ListCondition condition)
{
WhereConditon wcd = new WhereConditon();
wcd.DBPara = new Dictionary <string, object>();
ApproveListCondition acondition = condition as ApproveListCondition;
if (DataConvert.ToString(acondition.ListMode) != "")
{
wcd.DBPara.Add("approver", acondition.Approver);
}
ListModel model = JsonHelper.Deserialize <ListModel>(acondition.ListModelString);
if (model == null)
{
return(wcd);
}
if (DataConvert.ToString(model.AssetsMaintainNo) != "")
{
wcd.Sql += @" and AssetsMaintain.assetsMaintainNo like '%'+@assetsMaintainNo+'%'";
wcd.DBPara.Add("assetsMaintainNo", model.AssetsMaintainNo);
}
if (DataConvert.ToString(model.AssetsMaintainName) != "")
{
wcd.Sql += @" and AssetsMaintain.assetsMaintainName like '%'+@assetsMaintainName+'%'";
wcd.DBPara.Add("assetsMaintainName", model.AssetsMaintainName);
}
return(wcd);
}
protected WhereConditon ListWhereSql(ListCondition condition)
{
WhereConditon wcd = new WhereConditon();
wcd.DBPara = new Dictionary <string, object>();
EntryModel model = JsonHelper.Deserialize <EntryModel>(condition.ListModelString);
if (model == null)
{
return(wcd);
}
if (DataConvert.ToString(model.AssetsMergeNo) != "")
{
wcd.Sql += @" and AssetsMerge.assetsMergeNo like '%'+@assetsMergeNo+'%'";
wcd.DBPara.Add("assetsMergeNo", model.AssetsMergeNo);
}
if (DataConvert.ToString(model.AssetsMergeName) != "")
{
wcd.Sql += @" and AssetsMerge.assetsMergeName like '%'+@assetsMergeName+'%'";
wcd.DBPara.Add("assetsMergeName", model.AssetsMergeName);
}
if (DataConvert.ToString(model.AssetsNo) != "")
{
wcd.Sql += @" and A1.assetsNo like '%'+@assetsNo+'%'";
wcd.DBPara.Add("assetsNo", model.AssetsNo);
}
if (DataConvert.ToString(model.AssetsName) != "")
{
wcd.Sql += @" and A1.assetsName like '%'+@assetsName+'%'";
wcd.DBPara.Add("assetsName", model.AssetsName);
}
return(wcd);
}
protected override string ListSql(ListCondition condition)
{
string subViewSql = string.Format(@"select AppUser.userId userId,
AppUser.userNo userNo,
AppUser.userName userName,
AppUser.groupIdDisplay groupId ,
AppUser.departmentDisplay departmentId,
(select departmentName from AppDepartment C where D.companyId=C.departmentId ) companyId,
AppPost.postName postId,
(select CodeTable.codeName from CodeTable where AppUser.sex=CodeTable.codeNo and CodeTable.codeType='Sex' and CodeTable.languageVer='{0}' ) sex,
AppUser.tel tel,
AppUser.email email,
AppUser.address address,
(select CodeTable.codeName from CodeTable where AppUser.isFixed=CodeTable.codeNo and CodeTable.codeType='BoolVal' and CodeTable.languageVer='{0}' ) isFixed,
U1.userName createId,
AppUser.createTime,
U2.userName updateId,
AppUser.updateTime updateTime,
AppUser.updatePro updatePro
from AppUser left join AppGroup on AppUser.groupId=AppGroup.groupId
left join AppDepartment D on AppUser.departmentId=D.departmentId
left join AppPost on AppUser.postId=AppPost.postId
left join AppUser U1 on AppUser.createId=U1.userId
left join AppUser U2 on AppUser.updateId=U2.userId
where 1=1 ", AppMember.AppLanguage.ToString());
return(subViewSql);
}
public virtual DataTable GetReportGridDataTable(ListCondition condition, bool needPaging)
{
int rowSize = condition.PageIndex * condition.PageRowNum; //子查询返回行数的尺寸
string sql = string.Format(@"select Assets.assetsId assetsId,
Assets.assetsNo assetsNo,
Assets.assetsName assetsName,
AssetsMaintain.assetsMaintainNo assetsMaintainNo,
AssetsMaintain.assetsMaintainName assetsMaintainName,
convert(nvarchar(100), AssetsMaintainDetail.maintainDate ,23) maintainDate,
AssetsMaintainDetail.maintainCompany maintainCompany,
AssetsMaintainDetail.maintainAmount maintainAmount,
AssetsMaintainDetail.maintainActualCompany maintainActualCompany,
AssetsMaintainDetail.maintainActualAmount maintainActualAmount,
convert(nvarchar(100),AssetsMaintainDetail.maintainActualDate ,23) maintainActualDate,
AssetsMaintainDetail.remark Remark
from AssetsMaintainDetail,Assets,AssetsMaintain
where AssetsMaintainDetail.assetsId=Assets.assetsId
and AssetsMaintainDetail.assetsMaintainId=AssetsMaintain.assetsMaintainId
and (AssetsMaintainDetail.approveState='E' or AssetsMaintainDetail.approveState is null or AssetsMaintainDetail.approveState='')
{0} {1} ", ListWhereSql(condition).Sql,
" order by AssetsMaintain.assetsMaintainNo,Assets.assetsNo ");
DataTable dtGrid = AppMember.DbHelper.GetDataSet(sql, ListWhereSql(condition).DBPara).Tables[0];
return(dtGrid);
}
public virtual DataTable GetReportGridDataTable(ListCondition condition, bool needPaging)
{
string sql = string.Format(@"select row_number() over(order by AssetsCheck.assetsCheckNo,Assets.assetsNo) as rownumber,
Assets.assetsId assetsId,
Assets.assetsNo assetsNo,
Assets.assetsName assetsName,
AssetsCheck.assetsCheckNo assetsCheckNo,
AssetsCheck.assetsCheckName assetsCheckName,
(select userName from AppUser where AssetsCheck.checkPeople=AppUser.userId) checkPeople,
convert(nvarchar(100), AssetsCheckDetail.checkDate ,23) checkDate,
convert(nvarchar(100), AssetsCheckDetail.actualCheckDate ,23) actualCheckDate,
(select storeSiteName from StoreSite where StoreSite.storeSiteId=AssetsCheckDetail.storeSiteId) storeSiteId,
(select storeSiteName from StoreSite where StoreSite.storeSiteId=AssetsCheckDetail.actualStoreSiteId) actualStoreSiteId,
(select CodeTable.codeName from CodeTable where AssetsCheckDetail.checkResult=CodeTable.codeNo and CodeTable.codeType='CheckResult' and CodeTable.languageVer='{0}' ) checkResult,
AssetsCheckDetail.isFinished isFinished,
AssetsCheckDetail.remark Remark
from AssetsCheckDetail,Assets,AssetsCheck
where AssetsCheckDetail.assetsId=Assets.assetsId
and AssetsCheckDetail.assetsCheckId=AssetsCheck.assetsCheckId
and (AssetsCheckDetail.approveState='E' or AssetsCheckDetail.approveState is null or AssetsCheckDetail.approveState='')
{1} ", AppMember.AppLanguage.ToString(), ListWhereSql(condition).Sql);
if (needPaging)
{
sql = string.Format("select top {0} * from ({1}) A where rownumber > {2} order by assetsCheckNo,assetsNo ", condition.PageRowNum, sql, (condition.PageIndex - 1) * condition.PageRowNum);
}
else
{
sql += " order by AssetsCheck.assetsCheckNo,Assets.assetsNo ";
}
DataTable dtGrid = AppMember.DbHelper.GetDataSet(sql, ListWhereSql(condition).DBPara).Tables[0];
return(dtGrid);
}
public ActionResult ExcelPrint(string formvar)
{
//ListCondition condition = new ListCondition();
//condition.SortField = DataConvert.ToString(Request.Params["sidx"]);
//condition.SortType = DataConvert.ToString(Request.Params["sord"]);
//condition.PageIndex = DataConvert.ToInt32(Request.Params["page"]);
//condition.PageRowNum = DataConvert.ToInt32(Request.Params["rows"]);
//condition.ListModelString = formvar;
//DataTable dt = Repository.GetReportGridDataTable(condition);
//QueryEntryViewModel model = JsonHelper.Deserialize<QueryEntryViewModel>(formvar);
//ExcelHelper.PrintExcel(dt, EntryGridLayout(model.FormMode), "main");
//return Content("1", "text/html");
ListCondition condition = new ListCondition();
condition.SortField = DataConvert.ToString(Request.Params["sidx"]);
condition.SortType = DataConvert.ToString(Request.Params["sord"]);
condition.PageIndex = DataConvert.ToInt32(Request.Params["page"]);
condition.PageRowNum = DataConvert.ToInt32(Request.Params["rows"]);
condition.ListModelString = formvar;
DataTable dt = Repository.GetReportGridDataTable(condition, false);
QueryEntryViewModel model = JsonHelper.Deserialize <QueryEntryViewModel>(formvar);
model.EntryGridData = dt;
model.EntryGridLayout = EntryGridLayout(model.FormMode);
model.EntryGridId = "EntryGrid";
model.FormId = "PrintForm";
model.GridHeight = 375;
return(View("ReportPrint", model));
}
private static IResultAccessor <T> CreateCondition(Func <T, int, bool> condition, IListItemSetterManager <T> manager)
{
var c = new ListCondition <T>(condition);
manager.Add(c);
return(c);
}
protected override WhereConditon ListWhereSql(ListCondition condition)
{
WhereConditon wcd = new WhereConditon();
wcd.DBPara = new Dictionary <string, object>();
if (condition.SysUser.UserNo != "sa" && condition.SysUser.IsHeaderOffice != "Y")
{
wcd.Sql += @" and D.companyId=@companyId";
wcd.DBPara.Add("companyId", condition.SysUser.CompanyId);
}
ListModel model = JsonHelper.Deserialize <ListModel>(condition.ListModelString);
if (model == null)
{
return(wcd);
}
if (DataConvert.ToString(model.StoreSiteNo) != "")
{
wcd.Sql += @" and StoreSite.storeSiteNo like '%'+@storeSiteNo+'%'";
wcd.DBPara.Add("storeSiteNo", model.StoreSiteNo);
}
if (DataConvert.ToString(model.StoreSiteName) != "")
{
wcd.Sql += @" and StoreSite.storeSiteName like '%'+@storeSiteName+'%'";
wcd.DBPara.Add("storeSiteName", model.StoreSiteName);
}
return(wcd);
}
private IResultAccessor <T> CreateAddInsertCondition(Func <T, int, bool> condition, IListItemSetterManager <T> manager)
{
var c = new ListCondition <T>(condition);
// Go through all of the existing items to see if this condition
// has already been met. Otherwise the caller could potentially
// wait forever.
bool conditionNotMet = true;
for (int index = 0; index < list.Count; index++)
{
if (c.TrySetResult(list[index], index))
{
conditionNotMet = false;
}
}
// The condition was not met by any of the items in the list.
// Add the condition to the manager to be monitored.
if (conditionNotMet)
{
manager.Add(c);
}
return(c);
}
protected override string ListSql(ListCondition condition)
{
ApproveListCondition acondition = condition as ApproveListCondition;
string lsql = " where 1=1";
if (DataConvert.ToString(acondition.ListMode) != "")
{
if (DataConvert.ToString(acondition.ListMode) == "approve")
{
lsql = @",AppApprove
where AppApprove.tableName='AssetsSplit' and AppApprove.approveState='O'
and AssetsSplit.AssetsSplitId=AppApprove.refId and AppApprove.approver=@approver and isValid='Y'";
}
else if (DataConvert.ToString(acondition.ListMode) == "reapply")
{
lsql = @" where AssetsSplit.createId=@approver and AssetsSplit.approveState='R' ";
}
}
string subViewSql = string.Format(@"select AssetsSplit.AssetsSplitId AssetsSplitId,
AssetsSplit.AssetsSplitNo AssetsSplitNo,
AssetsSplit.AssetsSplitName AssetsSplitName,
(select userName from AppUser where AssetsSplit.createId=AppUser.userId) createId,
AssetsSplit.createTime createTime ,
(select userName from AppUser where AssetsSplit.updateId=AppUser.userId) updateId,
AssetsSplit.updateTime updateTime ,
AssetsSplit.updatePro updatePro
from AssetsSplit {0} ", lsql);
return subViewSql;
}
protected override WhereConditon ListWhereSql(ListCondition condition)
{
WhereConditon wcd = new WhereConditon();
wcd.DBPara = new Dictionary <string, object>();
ListModel model = JsonHelper.Deserialize <ListModel>(condition.ListModelString);
if (model == null)
{
return(wcd);
}
if (DataConvert.ToString(model.UnitNo) != "")
{
wcd.Sql += @" and Unit.unitNo like '%'+@unitNo+'%'";
wcd.DBPara.Add("unitNo", model.UnitNo);
}
if (DataConvert.ToString(model.UnitName) != "")
{
wcd.Sql += @" and Unit.unitName like '%'+@unitName+'%'";
wcd.DBPara.Add("unitName", model.UnitName);
}
if (DataConvert.ToString(model.UnitType) != "")
{
wcd.Sql += @" and Unit.unitType =@unitType ";
wcd.DBPara.Add("unitType", model.UnitType);
}
return(wcd);
}
public virtual DataTable GetReportGridDataTable(ListCondition condition, bool needPaging)
{
int rowSize = condition.PageIndex * condition.PageRowNum; //子查询返回行数的尺寸
string sql = string.Format(@"select AssetsMergeDetail.newAssetsId,
A1.assetsNo newAssetsNo,
A1.assetsName newAssetsName,
A1.assetsValue newAssetsValue,
AssetsMerge.assetsMergeNo,
AssetsMerge.assetsMergeName,
A2.assetsNo originalAssetsNo,
A2.assetsName originalAssetsName,
A2.assetsValue originalAssetsValue,
AssetsMergeDetail.originalAssetsId,
AssetsMergeDetail.remark
from AssetsMergeDetail,
AssetsMerge ,
Assets A1,
Assets A2
where AssetsMergeDetail.assetsMergeId=AssetsMerge.assetsMergeId
and AssetsMergeDetail.newAssetsId=A1.assetsId
and AssetsMergeDetail.originalAssetsId=A2.assetsId
and (AssetsMergeDetail.approveState='E' or AssetsMergeDetail.approveState is null)
{0} {1}", ListWhereSql(condition).Sql, " order by AssetsMerge.assetsMergeNo,A1.assetsNo ");
DataTable dtGrid = AppMember.DbHelper.GetDataSet(sql, ListWhereSql(condition).DBPara).Tables[0];
return(dtGrid);
}
public virtual JsonResult EntryGridData(string formVar)
{
ListCondition condition = new ListCondition();
condition.SortField = DataConvert.ToString(Request.Form["sidx"]);
condition.SortType = DataConvert.ToString(Request.Params["sord"]);
condition.PageIndex = DataConvert.ToInt32(Request.Params["page"]);
condition.PageRowNum = DataConvert.ToInt32(Request.Params["rows"]);
condition.ListModelString = formVar;
var rows = new object[0];
int cnt = 0;
if (DataConvert.ToString(formVar) != "")
{
cnt = Repository.GetReportGridDataTable(condition, false).Rows.Count;
condition.TotalRowNum = cnt;
QueryEntryViewModel model = JsonHelper.Deserialize <QueryEntryViewModel>(formVar);
DataTable dt = Repository.GetReportGridDataTable(condition, true);
rows = DataTable2Object.Data(dt, EntryGridLayout(model.FormMode).GridLayouts);
}
double aa = (double)cnt / condition.PageRowNum;
double pageCnt = Math.Ceiling(aa);
var result = new JsonResult();
result.JsonRequestBehavior = JsonRequestBehavior.AllowGet;
result.Data = new { page = 1, records = cnt, total = pageCnt, rows = rows };
return(result);
}
public virtual DataTable GetReportGridDataTable(ListCondition condition, bool needPaging)
{
string sql = string.Format(@"select row_number() over(order by Assets.assetsNo) as rownumber,
Assets.assetsId assetsId,
Assets.assetsNo assetsNo,
Assets.assetsName assetsName,
(select assetsClassName from AssetsClass where Assets.assetsClassId=AssetsClass.assetsClassId) assetsClassId,
(select departmentName from AppDepartment where Assets.departmentId=AppDepartment.departmentId) departmentId,
(select storeSiteName from StoreSite where Assets.storeSiteId=StoreSite.storeSiteId) storeSiteId,
Assets.durableYears durableYears,
convert(nvarchar(100), Assets.purchaseDate ,23) purchaseDate,
convert(nvarchar(100), DATEADD(yy,Assets.durableYears,Assets.purchaseDate) ,23) endDate,
(select CodeTable.codeName from CodeTable where Assets.assetsState=CodeTable.codeNo and CodeTable.codeType='AssetsState' and CodeTable.languageVer='{0}' ) assetsState
from Assets where 1=1 {1} ", AppMember.AppLanguage.ToString(), ListWhereSql(condition).Sql);
if (needPaging)
{
sql = string.Format("select top {0} * from ({1}) A where rownumber > {2} order by assetsNo ", condition.PageRowNum, sql, (condition.PageIndex - 1) * condition.PageRowNum);
}
else
{
sql += " order by Assets.assetsNo";
}
DataTable dtGrid = AppMember.DbHelper.GetDataSet(sql, ListWhereSql(condition).DBPara).Tables[0];
return(dtGrid);
}
/// <summary>
///
/// </summary>
/// <param name="paras"></param>
/// <param name="isInnerOrder">内部的排序用相反排序，外部正常排序</param>
/// <returns></returns>
private string OrderBy(ListCondition condition, int layer, string defaultSort)
{
string sql = defaultSort;
string sortField = condition.SortField;
string sortType = condition.SortType;
if (sortField != "")
{
if (SafeSql.ProcessSqlStr(sortField, 0) && SafeSql.ProcessSqlStr(sortType, 0))
{
if (layer == 2)
{
if (sortType == "asc")
{
sql = string.Format(" order by {0} desc ", sortField);
}
else
{
sql = string.Format(" order by {0} ", sortField);
}
}
else
{
sql = string.Format(" order by {0} {1} ", sortField, sortType);
}
}
}
return(sql);
}
public virtual DataTable GetGridDataTable(ListCondition condition)
{
int topNum = 0; //当前页显示的条数
if (condition.PageIndex * condition.PageRowNum > condition.TotalRowNum)
{
topNum = condition.TotalRowNum - (condition.PageIndex - 1) * condition.PageRowNum;
}
else
{
topNum = condition.PageRowNum;
}
int rowSize = condition.PageIndex * condition.PageRowNum; //当前视图需要的尺寸
string sql = string.Format(@" select * from
(select top {0} * from
(select top {1} * from ({2} {3} )P {4})T
{5})F {6} ", DataConvert.ToString(topNum),
rowSize,
ListSql(condition),
ListWhereSql(condition).Sql,
OrderBy(condition, 1, string.Format(@"order by {0} desc ", DefaulteGridSortField)),
OrderBy(condition, 2, string.Format(@"order by {0} ", DefaulteGridSortField)),
OrderBy(condition, 3, string.Format(@"order by {0} desc", DefaulteGridSortField)));
DataTable dtGrid = AppMember.DbHelper.GetDataSet(sql, ListWhereSql(condition).DBPara).Tables[0];
return(dtGrid);
}
protected virtual DataTable GetStoreSiteTable(ListCondition condition)
{
string sql = string.Format(@"select MonthRecord.*,
StoreSite.storeSitePath,
StoreSite.storeSiteName
from MonthRecord,StoreSite
where MonthRecord.storeSiteId=StoreSite.storeSiteId {0}
order by StoreSite.storeSiteNo ", ListWhereSql(condition).Sql);
DataTable dtAll = AppMember.DbHelper.GetDataSet(sql, ListWhereSql(condition).DBPara).Tables[0];
string sqlGroup = string.Format(@"select distinct StoreSite.storeSiteId,
StoreSite.storeSiteNo,
StoreSite.storeSiteName ,
StoreSite.storeSitePath
from MonthRecord,StoreSite
where MonthRecord.storeSiteId=StoreSite.storeSiteId {0}
order by StoreSite.storeSiteNo ", ListWhereSql(condition).Sql);
DataTable dtGroup = AppMember.DbHelper.GetDataSet(sqlGroup, ListWhereSql(condition).DBPara).Tables[0];
string sqlView = @"select MonthRecord.* ,
StoreSite.storeSiteName
from MonthRecord,StoreSite
where MonthRecord.storeSiteId=StoreSite.storeSiteId and 1<>1 ";
DataTable dtView = AppMember.DbHelper.GetDataSet(sqlView).Tables[0];
SetDataView(dtView, dtAll, dtGroup, "storeSite");
return(dtView);
}
protected virtual DataTable GetDepartmentTable(ListCondition condition)
{
string sql = string.Format(@"select MonthRecord.*,
AppDepartment.departmentPath,
AppDepartment.departmentName
from MonthRecord,AppDepartment
where MonthRecord.departmentId=AppDepartment.departmentId {0}
order by AppDepartment.departmentNo ", ListWhereSql(condition).Sql);
DataTable dtAll = AppMember.DbHelper.GetDataSet(sql, ListWhereSql(condition).DBPara).Tables[0];
string sqlGroup = string.Format(@"select distinct AppDepartment.departmentId,
AppDepartment.departmentNo,
AppDepartment.departmentName ,
AppDepartment.departmentPath
from MonthRecord,AppDepartment
where MonthRecord.departmentId=AppDepartment.departmentId {0}
order by AppDepartment.departmentNo ", ListWhereSql(condition).Sql);
DataTable dtGroup = AppMember.DbHelper.GetDataSet(sqlGroup, ListWhereSql(condition).DBPara).Tables[0];
string sqlView = @"select MonthRecord.* ,
AppDepartment.departmentName
from MonthRecord,AppDepartment
where MonthRecord.departmentId=AppDepartment.departmentId and 1<>1 ";
DataTable dtView = AppMember.DbHelper.GetDataSet(sqlView).Tables[0];
SetDataView(dtView, dtAll, dtGroup, "department");
return(dtView);
}
protected virtual DataTable GetAssetsClassTable(ListCondition condition)
{
string sql = string.Format(@"select MonthRecord.*,
AssetsClass.assetsClassPath,
AssetsClass.assetsClassName
from MonthRecord,AssetsClass
where MonthRecord.assetsClassId=AssetsClass.assetsClassId {0}
order by AssetsClass.assetsClassNo ", ListWhereSql(condition).Sql);
DataTable dtAll = AppMember.DbHelper.GetDataSet(sql, ListWhereSql(condition).DBPara).Tables[0];
string sqlGroup = string.Format(@"select distinct AssetsClass.assetsClassId,
AssetsClass.assetsClassNo,
AssetsClass.assetsClassName ,
AssetsClass.assetsClassPath
from MonthRecord,AssetsClass
where MonthRecord.assetsClassId=AssetsClass.assetsClassId {0}
order by AssetsClass.assetsClassNo ", ListWhereSql(condition).Sql);
DataTable dtGroup = AppMember.DbHelper.GetDataSet(sqlGroup, ListWhereSql(condition).DBPara).Tables[0];
string sqlView = @"select MonthRecord.* ,
AssetsClass.assetsClassName
from MonthRecord,AssetsClass
where MonthRecord.assetsClassId=AssetsClass.assetsClassId and 1<>1 ";
DataTable dtView = AppMember.DbHelper.GetDataSet(sqlView).Tables[0];
SetDataView(dtView, dtAll, dtGroup, "assetsClass");
return(dtView);
}
public virtual DataTable GetReportGridDataTable(ListCondition condition)
{
int rowSize = condition.PageIndex * condition.PageRowNum; //子查询返回行数的尺寸
string sql = string.Format(@"select T1.*,
T2.assetsReturnNo,
T2.assetsReturnName,
T2.returnPeople,
T2.returnDate
from
(select AssetsBorrowDetail.assetsId,
Assets.assetsNo,
Assets.assetsName,
AssetsBorrow.assetsBorrowNo,
AssetsBorrow.assetsBorrowName,
AssetsBorrowDetail.borrowPeople,
AssetsBorrowDetail.borrowDepartmentId,
AssetsBorrowDetail.borrowDate
from AssetsBorrow,AssetsBorrowDetail,Assets
where AssetsBorrow.assetsBorrowId=AssetsBorrowDetail.assetsBorrowId
and AssetsBorrowDetail.assetsId=Assets.assetsId) T1
left join
(select AssetsReturnDetail.assetsId,
AssetsReturn.assetsReturnNo,
AssetsReturn.assetsReturnName,
AssetsReturnDetail.returnPeople,
AssetsReturnDetail.returnDate
from AssetsReturn,AssetsReturnDetail
where AssetsReturn.assetsReturnId=AssetsReturnDetail.assetsReturnId) T2
on T1.assetsId=T2.assetsId {0} ", ListWhereSql(condition).Sql);
DataTable dtGrid = AppMember.DbHelper.GetDataSet(sql, ListWhereSql(condition).DBPara).Tables[0];
return(dtGrid);
}
public virtual DataTable GetReportGridDataTable(ListCondition condition, bool needPaging)
{
string sql = string.Format(@"select row_number() over(order by AssetsScrap.assetsScrapNo,Assets.assetsNo) as rownumber,
Assets.assetsId assetsId,
Assets.assetsNo assetsNo,
Assets.assetsName assetsName,
AssetsScrap.assetsScrapNo assetsScrapNo,
AssetsScrap.assetsScrapName assetsScrapName,
(select assetsClassName from AssetsClass where Assets.assetsClassId=AssetsClass.assetsClassId) assetsClassId,
(select assetsTypeName from AssetsType where Assets.assetsTypeId=AssetsType.assetsTypeId) assetsTypeId,
(select departmentName from AppDepartment where Assets.departmentId=AppDepartment.departmentId) departmentId,
(select scrapTypeName from ScrapType where AssetsScrapDetail.scrapTypeId=ScrapType.scrapTypeId) ScrapTypeId,
convert(nvarchar(100), AssetsScrapDetail.scrapDate,23) ScrapDate,
AssetsScrapDetail.remark Remark
from AssetsScrapDetail,Assets,AssetsScrap
where AssetsScrapDetail.assetsId=Assets.assetsId
and AssetsScrapDetail.assetsScrapId=AssetsScrap.assetsScrapId
and (AssetsScrapDetail.approveState='E' or AssetsScrapDetail.approveState is null or AssetsScrapDetail.approveState='')
{0} ", ListWhereSql(condition).Sql);
if (needPaging)
{
sql = string.Format("select top {0} * from ({1}) A where rownumber > {2} order by assetsScrapNo ,assetsNo", condition.PageRowNum, sql, (condition.PageIndex - 1) * condition.PageRowNum);
}
else
{
sql += " order by AssetsScrap.assetsScrapNo ,Assets.assetsNo";
}
DataTable dtGrid = AppMember.DbHelper.GetDataSet(sql, ListWhereSql(condition).DBPara).Tables[0];
return(dtGrid);
}
public virtual int GetGridDataCount(ListCondition condition)
{
// string sql = string.Format(@"select count(*) cnt
// from {0} where 1=1 {1}", MasterTable,ListWhereSql(condition).Sql);
string sql = ListSql(condition) + ListWhereSql(condition).Sql;
DataTable dtGrid = AppMember.DbHelper.GetDataSet(sql, ListWhereSql(condition).DBPara).Tables[0];
return(dtGrid.Rows.Count);
}
public virtual DataTable GetAllGridDataTable(ListCondition condition)
{
string sql = string.Format(@" {0} {1} {2} ",
ListSql(condition),
ListWhereSql(condition).Sql,
OrderBy(condition, 1, string.Format(@"order by {0} ", DefaulteGridSortField)));
DataTable dtGrid = AppMember.DbHelper.GetDataSet(sql, ListWhereSql(condition).DBPara).Tables[0];
return(dtGrid);
}
protected override string ListSql(ListCondition condition)
{
ApproveListCondition acondition = condition as ApproveListCondition;
string subViewSql = @"select distinct AssetsTransfer.assetsTransferId assetsTransferId,
AssetsTransfer.assetsTransferNo assetsTransferNo,
AssetsTransfer.assetsTransferName assetsTransferName,
convert(nvarchar(100), (select top 1 transferDate from AssetsTransferDetail where assetsTransferId=AssetsTransfer.assetsTransferId),23) transferDate,
AssetsTransfer.approveState ApproveState_val,
(select top 1 codename from CodeTable where codetype='ApproveState' and AssetsTransfer.approveState=codeno) approveState,
(select top 1 AssetsClass.assetsClassName from AssetsTransferDetail,Assets,AssetsClass where assetsTransferId=AssetsTransfer.assetsTransferId and Assets.assetsId=AssetsTransferDetail.assetsId and Assets.assetsClassId=AssetsClass.assetsClassId) assetsClassId,
(select top 1 Assets.assetsNo from AssetsTransferDetail,Assets where assetsTransferId=AssetsTransfer.assetsTransferId and Assets.assetsId=AssetsTransferDetail.assetsId) assetsNo,
(select top 1 Assets.assetsName from AssetsTransferDetail,Assets where assetsTransferId=AssetsTransfer.assetsTransferId and Assets.assetsId=AssetsTransferDetail.assetsId) assetsName,
(select top 1 Assets.spec from AssetsTransferDetail,Assets where assetsTransferId=AssetsTransfer.assetsTransferId and Assets.assetsId=AssetsTransferDetail.assetsId) spec,
(select top 1 Assets.assetsQty from AssetsTransferDetail,Assets where assetsTransferId=AssetsTransfer.assetsTransferId and Assets.assetsId=AssetsTransferDetail.assetsId) assetsQty,
(select userName from AppUser where AssetsTransfer.createId=AppUser.userId) createId,
AssetsTransfer.createTime createTime ,
(select userName from AppUser where AssetsTransfer.updateId=AppUser.userId) updateId,
AssetsTransfer.updateTime updateTime ,
AssetsTransfer.updatePro updatePro
from AssetsTransfer ";
string lsql = " where 1=1";
ListModel model = JsonHelper.Deserialize <ListModel>(acondition.ListModelString);
if (model == null || (model != null && !model.QueryAllApproveRecord))
{
if (DataConvert.ToString(acondition.ListMode) != "")
{
if (DataConvert.ToString(acondition.ListMode) == "approve")
{
lsql = @",AppApprove
where AppApprove.tableName='AssetsTransfer' and AppApprove.approveState in ('O','B')
and AssetsTransfer.assetsTransferId=AppApprove.refId and AppApprove.approver=@approver and isValid='Y'";
}
else if (DataConvert.ToString(acondition.ListMode) == "reapply")
{
lsql = @" ,AppApprove
where AppApprove.tableName='AssetsTransfer' and AppApprove.approveState in ('B')
and AssetsTransfer.assetsTransferId=AppApprove.refId and AppApprove.approver=@approver and isValid='Y'";
}
}
}
else
{
lsql = @",AppApprove
where AppApprove.tableName='AssetsTransfer'
and AssetsTransfer.assetsTransferId=AppApprove.refId and AppApprove.approver=@approver and (isValid='Y' or Approvetime is not null ) ";
}
subViewSql = subViewSql + lsql;
subViewSql = string.Format(" select * from ({0}) M where 1=1 ", subViewSql);
return(subViewSql);
}
protected WhereConditon ListWhereSql(ListCondition condition)
{
WhereConditon wcd = new WhereConditon();
wcd.DBPara = new Dictionary <string, object>();
EntryModel model = JsonHelper.Deserialize <EntryModel>(condition.ListModelString);
if (model == null)
{
return(wcd);
}
if (DataConvert.ToString(model.AssetsNo) != "")
{
wcd.Sql += @" and Assets.assetsNo like '%'+@assetsNo+'%'";
wcd.DBPara.Add("assetsNo", model.AssetsNo);
}
if (DataConvert.ToString(model.AssetsName) != "")
{
wcd.Sql += @" and Assets.assetsName like '%'+@assetsName+'%'";
wcd.DBPara.Add("assetsName", model.AssetsName);
}
if (DataConvert.ToString(model.AssetsClassId) != "")
{
wcd.Sql += @" and Assets.assetsClassId=@assetsClassId";
wcd.DBPara.Add("assetsClassId", model.AssetsClassId);
}
if (DataConvert.ToString(model.DepartmentId) != "")
{
wcd.Sql += @" and Assets.departmentId=@departmentId";
wcd.DBPara.Add("departmentId", model.DepartmentId);
}
if (DataConvert.ToString(model.StoreSiteId) != "")
{
wcd.Sql += @" and Assets.storeSiteId=@storeSiteId";
wcd.DBPara.Add("storeSiteId", model.StoreSiteId);
}
if (DataConvert.ToString(model.AssetsState) != "")
{
wcd.Sql += @" and Assets.assetsState=@assetsState";
wcd.DBPara.Add("assetsState", model.AssetsState);
}
if (DataConvert.ToString(model.DepreciationEndDate1) != "")
{
wcd.Sql += @" and DATEADD(yy,Assets.durableYears,Assets.purchaseDate)>=@depreciationEndDate1 ";
wcd.DBPara.Add("depreciationEndDate1", DataConvert.ToString(model.DepreciationEndDate1) + " 00:00:00");
}
if (DataConvert.ToString(model.DepreciationEndDate2) != "")
{
wcd.Sql += @" and DATEADD(yy,Assets.durableYears,Assets.purchaseDate)<=@depreciationEndDate2 ";
wcd.DBPara.Add("depreciationEndDate2", DataConvert.ToString(model.DepreciationEndDate2) + " 23:59:59");
}
return(wcd);
}
protected WhereConditon ListWhereSql(ListCondition condition)
{
WhereConditon wcd = new WhereConditon();
wcd.DBPara = new Dictionary <string, object>();
EntryModel model = JsonHelper.Deserialize <EntryModel>(condition.ListModelString);
if (model == null)
{
return(wcd);
}
if (DataConvert.ToString(model.AssetsScrapNo) != "")
{
wcd.Sql += @" and AssetsScrap.assetsScrapNo like '%'+@assetsScrapNo+'%'";
wcd.DBPara.Add("assetsScrapNo", model.AssetsScrapNo);
}
if (DataConvert.ToString(model.AssetsScrapName) != "")
{
wcd.Sql += @" and AssetsScrap.assetsScrapName like '%'+@assetsScrapName+'%'";
wcd.DBPara.Add("assetsScrapName", model.AssetsScrapName);
}
if (DataConvert.ToString(model.AssetsNo) != "")
{
wcd.Sql += @" and Assets.assetsNo like '%'+@assetsNo+'%'";
wcd.DBPara.Add("assetsNo", model.AssetsNo);
}
if (DataConvert.ToString(model.AssetsName) != "")
{
wcd.Sql += @" and Assets.assetsName like '%'+@assetsName+'%'";
wcd.DBPara.Add("assetsName", model.AssetsName);
}
if (DataConvert.ToString(model.AssetsTypeId) != "")
{
wcd.Sql += @" and Assets.assetsTypeId like '%'+@assetsTypeId+'%'";
wcd.DBPara.Add("assetsTypeId", model.AssetsTypeId);
}
if (DataConvert.ToString(model.ScrapTypeId) != "")
{
wcd.Sql += @" and AssetsScrapDetail.scrapTypeId like '%'+@scrapTypeId+'%'";
wcd.DBPara.Add("scrapTypeId", model.ScrapTypeId);
}
if (DataConvert.ToString(model.AssetsClassId) != "")
{
wcd.Sql += @" and Assets.assetsClassId like '%'+@assetsClassId+'%'";
wcd.DBPara.Add("assetsClassId", model.AssetsClassId);
}
if (DataConvert.ToString(model.DepartmentId) != "")
{
wcd.Sql += @" and Assets.departmentId like '%'+@departmentId+'%'";
wcd.DBPara.Add("departmentId", model.DepartmentId);
}
return(wcd);
}
public virtual DataTable GetReportGridDataTable(ListCondition condition, bool needPaging)
{
int rowSize = condition.PageIndex * condition.PageRowNum; //子查询返回行数的尺寸
string sql = string.Format(@"select top 500 LogDate,
UserId,
Logger,
Message
from AppLog where 1=1 {0} order by LogDate desc ", ListWhereSql(condition).Sql,
" order by AssetsScrap.assetsScrapNo ,Assets.assetsNo");
DataTable dtGrid = AppMember.DbHelper.GetDataSet(sql, ListWhereSql(condition).DBPara).Tables[0];
return(dtGrid);
}
public override (IQueryable <Continent> entity, ExecutionState executionState, string message) List(
QueryOptions <Continent> queryOptions = null,
ListCondition listCondition = ListCondition.Normal)
{
try
{
IQueryable <Continent> continent = ReadOnlyContext.Set <Continent>().Include(x => x.TableMappingDatas).ThenInclude(x => x.TableConfiguration).AsQueryable();
return(entity : continent, executionState : (continent != null) ? ExecutionState.Success : ExecutionState.Failure, message : (continent != null) ? "Country found." : "Country not found.");
}
catch (Exception ex)
{
return(entity: null, executionState: ExecutionState.Failure, message : ex.Message);
}
}
public virtual DataTable GetReportGridDataTable(ListCondition condition)
{
int rowSize = condition.PageIndex * condition.PageRowNum; //子查询返回行数的尺寸
string sql = string.Format(@"select MonthAssetsClassRecord.*,AssetsClass.assetsClassPath,AssetsClass.assetsClassName
from MonthAssetsClassRecord,AssetsClass
where MonthAssetsClassRecord.assetsClassId=AssetsClass.assetsClassId {0}
order by AssetsClass.assetsClassNo ", ListWhereSql(condition).Sql);
DataTable dt = AppMember.DbHelper.GetDataSet(sql, ListWhereSql(condition).DBPara).Tables[0];
string sqlRpt = string.Format(@"select distinct AssetsClass.assetsClassId, AssetsClass.assetsClassNo,
AssetsClass.assetsClassName ,AssetsClass.assetsClassPath
from MonthAssetsClassRecord,AssetsClass
where MonthAssetsClassRecord.assetsClassId=AssetsClass.assetsClassId {0}
order by AssetsClass.assetsClassNo ", ListWhereSql(condition).Sql);
DataTable dtRpt = AppMember.DbHelper.GetDataSet(sqlRpt, ListWhereSql(condition).DBPara).Tables[0];
string sqlView = @"select * from MonthAssetsClassRecord where 1<>1 ";
DataTable dtView = AppMember.DbHelper.GetDataSet(sqlView).Tables[0];
foreach (DataRow dr in dtRpt.Rows)
{
DataRow drHeader = dtView.NewRow();
drHeader["assetsClassId"] = DataConvert.ToString(dr["assetsClassId"]);
drHeader["assetsClassName"] = DataConvert.ToString(dr["assetsClassName"]);
if (DataConvert.ToString(dr["assetsClassPath"]) == "" ||
DataConvert.ToString(dr["assetsClassPath"]) == DataConvert.ToString(dr["assetsClassId"]))
{
drHeader["beginPeriodNum"] = DataConvert.ToInt32(dt.Compute("sum(beginPeriodNum)", "assetsClassId ='" + DataConvert.ToString(dr["assetsClassId"]) + "'"));
drHeader["beginPeriodAmt"] = DataConvert.ToDouble(dt.Compute("sum(beginPeriodAmt)", "assetsClassId ='" + DataConvert.ToString(dr["assetsClassId"]) + "'"));
drHeader["currentPeriodAddNum"] = DataConvert.ToInt32(dt.Compute("sum(currentPeriodAddNum)", "assetsClassId ='" + DataConvert.ToString(dr["assetsClassId"]) + "'"));
drHeader["currentPeriodAddAmt"] = DataConvert.ToDouble(dt.Compute("sum(currentPeriodAddAmt)", "assetsClassId ='" + DataConvert.ToString(dr["assetsClassId"]) + "'"));
drHeader["currentPeriodReduceNum"] = DataConvert.ToInt32(dt.Compute("sum(currentPeriodReduceNum)", "assetsClassId ='" + DataConvert.ToString(dr["assetsClassId"]) + "'"));
drHeader["currentPeriodReduceAmt"] = DataConvert.ToDouble(dt.Compute("sum(currentPeriodReduceAmt)", "assetsClassId ='" + DataConvert.ToString(dr["assetsClassId"]) + "'"));
drHeader["endPeriodNum"] = DataConvert.ToInt32(dt.Compute("sum(endPeriodNum)", "assetsClassId ='" + DataConvert.ToString(dr["assetsClassId"]) + "'"));
drHeader["endPeriodAmt"] = DataConvert.ToDouble(dt.Compute("sum(endPeriodAmt)", "assetsClassId ='" + DataConvert.ToString(dr["assetsClassId"]) + "'"));
}
else
{
drHeader["beginPeriodNum"] = DataConvert.ToInt32(dt.Compute("sum(beginPeriodNum)", "assetsClassPath like '%" + DataConvert.ToString(dr["assetsClassId"]) + "%'"));
drHeader["beginPeriodAmt"] = DataConvert.ToDouble(dt.Compute("sum(beginPeriodAmt)", "assetsClassPath like '%" + DataConvert.ToString(dr["assetsClassId"]) + "%'"));
drHeader["currentPeriodAddNum"] = DataConvert.ToInt32(dt.Compute("sum(currentPeriodAddNum)", "assetsClassPath like '%" + DataConvert.ToString(dr["assetsClassId"]) + "%'"));
drHeader["currentPeriodAddAmt"] = DataConvert.ToDouble(dt.Compute("sum(currentPeriodAddAmt)", "assetsClassPath like '%" + DataConvert.ToString(dr["assetsClassId"]) + "%'"));
drHeader["currentPeriodReduceNum"] = DataConvert.ToInt32(dt.Compute("sum(currentPeriodReduceNum)", "assetsClassPath like '%" + DataConvert.ToString(dr["assetsClassId"]) + "%'"));
drHeader["currentPeriodReduceAmt"] = DataConvert.ToDouble(dt.Compute("sum(currentPeriodReduceAmt)", "assetsClassPath like '%" + DataConvert.ToString(dr["assetsClassId"]) + "%'"));
drHeader["endPeriodNum"] = DataConvert.ToInt32(dt.Compute("sum(endPeriodNum)", "assetsClassPath like '%" + DataConvert.ToString(dr["assetsClassId"]) + "%'"));
drHeader["endPeriodAmt"] = DataConvert.ToDouble(dt.Compute("sum(endPeriodAmt)", "assetsClassPath like '%" + DataConvert.ToString(dr["assetsClassId"]) + "%'"));
}
dtView.Rows.Add(drHeader);
}
return(dtView);
}
public StringCondition(StringQueryField Field, ListCondition Condition, IEnumerable<string> Values)
{
if (Values == null)
{
queryPart = "";
return;
}
string values = string.Join(", ", Values.Select(val => $"'{val.Replace("'", "''")}'"));
if (string.IsNullOrWhiteSpace(values))
{
queryPart = "";
}
else
{
queryPart = $"{Field.GetQueryPart()} {(Condition == ListCondition.In ? "in" : "not in")} ({values})";
}
}