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); }