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='AssetsBorrow' and AppApprove.approveState='O'
and AssetsBorrow.assetsBorrowId=AppApprove.refId and AppApprove.approver=@approver and isValid='Y'";
}
else if (DataConvert.ToString(acondition.ListMode) == "reapply")
{
lsql = @" where AssetsBorrow.createId=@approver and AssetsBorrow.approveState='R' ";
}
}
string subViewSql = string.Format(@"select AssetsBorrow.assetsBorrowId assetsBorrowId,
AssetsBorrow.assetsBorrowNo assetsBorrowNo,
AssetsBorrow.assetsBorrowName assetsBorrowName,
(select userName from AppUser where AssetsBorrow.createId=AppUser.userId) createId,
AssetsBorrow.createTime createTime ,
(select userName from AppUser where AssetsBorrow.updateId=AppUser.userId) updateId,
AssetsBorrow.updateTime updateTime ,
AssetsBorrow.updatePro updatePro
from AssetsBorrow {0} ", lsql);
return(subViewSql);
}
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.AssetsBorrowNo) != "")
{
wcd.Sql += @" and AssetsBorrow.assetsBorrowNo like '%'+@assetsBorrowNo+'%'";
wcd.DBPara.Add("assetsBorrowNo", model.AssetsBorrowNo);
}
if (DataConvert.ToString(model.AssetsBorrowName) != "")
{
wcd.Sql += @" and AssetsBorrow.assetsBorrowName like '%'+@assetsBorrowName+'%'";
wcd.DBPara.Add("assetsBorrowName", model.AssetsBorrowName);
}
return(wcd);
}
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 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.AssetsScrapNo) != "")
{
wcd.Sql += @" and M.assetsScrapNo like '%'+@assetsScrapNo+'%'";
wcd.DBPara.Add("assetsScrapNo", model.AssetsScrapNo);
}
if (DataConvert.ToString(model.AssetsScrapName) != "")
{
wcd.Sql += @" and M.assetsScrapName like '%'+@assetsScrapName+'%'";
wcd.DBPara.Add("assetsScrapName", model.AssetsScrapName);
}
if (DataConvert.ToString(model.ScrapDate1) != "")
{
wcd.Sql += @" and exists(select 1 from AssetsScrapDetail where AssetsScrapDetail.assetsScrapId=M.assetsScrapId
and AssetsScrapDetail.scrapDate>=@scrapDate1)";
wcd.DBPara.Add("scrapDate1", DataConvert.ToString(model.ScrapDate1) + " 00:00:00");
}
if (DataConvert.ToString(model.ScrapDate2) != "")
{
wcd.Sql += @" and exists(select 1 from AssetsScrapDetail where AssetsScrapDetail.assetsScrapId=M.assetsScrapId
and AssetsScrapDetail.scrapDate<=@scrapDate2)";
wcd.DBPara.Add("scrapDate2", DataConvert.ToString(model.ScrapDate2) + " 23:59:59");
}
if (DataConvert.ToString(model.ApproveState) != "")
{
wcd.Sql += @" and M.ApproveState_val=@ApproveState";
wcd.DBPara.Add("ApproveState", model.ApproveState);
}
return(wcd);
}
public JsonResult GridData(string listMode, string filterString, string selectMode)
{
try
{
ApproveListCondition condition = new ApproveListCondition();
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.ListMode = listMode;
condition.SelectMode = selectMode;
condition.FilterString = filterString;
//if (Request.Form.AllKeys.Contains("isQuery") && DataConvert.ToString(Request.Form["isQuery"]) == "true")
// condition.PageIndex = 1;
if (Request.Form.AllKeys.Contains("formVar"))
{
condition.ListModelString = DataConvert.ToString(Request.Form["formVar"]);
}
UserInfo sysUser = CacheInit.GetUserInfo(HttpContext);
condition.SysUser = sysUser;
condition.Approver = sysUser.UserId;
int cnt = ListRepository.GetGridDataCount(condition);
condition.TotalRowNum = cnt;
DataTable dt = ListRepository.GetGridDataTable(condition);
var rows = DataTable2Object.Data(dt, GridLayout(listMode, selectMode).GridLayouts);
double aa = (double)cnt / condition.PageRowNum;
double pageCnt = Math.Ceiling(aa);
var result = new JsonResult();
result.JsonRequestBehavior = JsonRequestBehavior.AllowGet;
result.Data = new { page = condition.PageIndex, records = cnt, total = pageCnt, rows = rows };
return(result);
}
catch (Exception ex)
{
AppLog.WriteLog(AppMember.AppText["SystemUser"], LogType.Error, "ApproveMasterController.GridData", ControllerName + "[Message]:" + ex.Message + " [StackTrace]:" + ex.StackTrace);
return(new JsonResult());
}
}
public ActionResult Export(string formvar)
{
try
{
ApproveListCondition condition = new ApproveListCondition();
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;
UserInfo sysUser = CacheInit.GetUserInfo(HttpContext);
condition.SysUser = sysUser;
condition.Approver = sysUser.UserId;
DataTable dt = ListRepository.GetAllGridDataTable(condition);
StringBuilder sbHtml = ExcelHelper.CreateExcel(dt, GridLayout("", ""));
byte[] fileContents = Encoding.UTF8.GetBytes(sbHtml.ToString());
return(File(fileContents, "application/ms-excel", IdGenerator.GetMaxId(ExportFileName) + ".xls"));
}
catch (Exception ex)
{
AppLog.WriteLog(AppMember.AppText["SystemUser"], LogType.Error, "ApproveMasterController.Export", ControllerName + "[Message]:" + ex.Message + " [StackTrace]:" + ex.StackTrace);
return(Content(ControllerName + "[Message]:" + ex.Message + " [StackTrace]:" + ex.StackTrace, "text/html"));
}
}
protected override string ListSql(ListCondition condition)
{
ApproveListCondition acondition = condition as ApproveListCondition;
int rowSize = acondition.PageIndex * acondition.PageRowNum; //子查询返回行数的尺寸
string lsql = " where 1=1";
if (DataConvert.ToString(acondition.ListMode) != "")
{
if (DataConvert.ToString(acondition.ListMode) == "approve")
{
lsql = @",AppApprove
where AppApprove.tableName='AssetsCheck' and AppApprove.approveState='O'
and AssetsCheck.AssetsCheckId=AppApprove.refId and AppApprove.approver=@approver and isValid='Y'";
}
else if (DataConvert.ToString(acondition.ListMode) == "reapply")
{
lsql = @" where AssetsCheck.createId=@approver and AssetsCheck.approveState='R' ";
}
else if (DataConvert.ToString(acondition.ListMode) == "actual")
{
lsql = @" where AssetsCheck.createId=@approver and (AssetsCheck.approveState='E' or AssetsCheck.approveState is null) ";
}
}
string subViewSql = string.Format(@"select top {0} AssetsCheck.AssetsCheckId AssetsCheckId,
AssetsCheck.AssetsCheckNo AssetsCheckNo,
AssetsCheck.AssetsCheckName AssetsCheckName,
(select top 1 codename from CodeTable where codetype='ApproveState' and AssetsCheck.approveState=codeno) approveState,
(select userName from AppUser where AssetsCheck.createId=AppUser.userId) createId,
AssetsCheck.createTime createTime ,
(select userName from AppUser where AssetsCheck.updateId=AppUser.userId) updateId,
AssetsCheck.updateTime updateTime ,
AssetsCheck.updatePro updatePro
from AssetsCheck {2} {3}", DataConvert.ToString(rowSize), AppMember.AppLanguage.ToString(), lsql, ListWhereSql(acondition).Sql);
return(subViewSql);
}
