public List<AssetReportDetailInfo> RetrieveAssetReportDetailInfoPaging(AssetRunTimeSearch info, int pageIndex, int pageSize, out int count) { try { var sqlCommand = new StringBuilder(@" select a.setupid AS Billid,a.assetno from assetsetupdetail a inner join assetsetupinfo b on a.setupid=b.setupid inner join asset c on a.assetno=c.assetno"); #region (ϵͳ)�豸���� count = 0; if(string.IsNullOrEmpty(info.Assetcategoryid)){return new List<AssetReportDetailInfo>();} sqlCommand.AppendLine(@" where c.ASSETCATEGORYID = :Assetcategoryid "); this.Database.AddInParameter(":Assetcategoryid", DbType.AnsiString, info.Assetcategoryid); //if(!string.IsNullOrEmpty(info.Storagetitle)) //{ sqlCommand.AppendLine(" and b.storagetitle = :Storagetitle AND b.storageid = :Storageid"); this.Database.AddInParameter(":Storagetitle", DbType.AnsiString, info.Storagetitle); this.Database.AddInParameter(":Storageid", DbType.AnsiString, info.Storageid); //} #endregion #region ʵ��������� if (info.StartActualDate.HasValue) { this.Database.AddInParameter(":StartActualDate", info.StartActualDate.Value.Date); sqlCommand.AppendLine(@" AND b.ACTUALSETUPDATE >= :StartActualDate"); } if (info.EndActualDate.HasValue) { this.Database.AddInParameter(":EndActualDate", info.EndActualDate.Value.Date.AddDays(1).AddSeconds(-1)); sqlCommand.AppendLine(@" AND b.ACTUALSETUPDATE <= :EndActualDate"); } #endregion sqlCommand.AppendLine(@" ORDER BY Billid DESC"); return this.ExecuteReaderPaging<AssetReportDetailInfo>(sqlCommand.ToString(), pageIndex, pageSize, out count); } finally { this.Database.ClearParameter(); } }
protected void LoadData(int pageIndex) { var search = new AssetRunTimeSearch(); search.Assetcategoryid = PageUtility.GetQueryStringValue("Assetcategoryid"); search.Storagetitle = Server.UrlDecode(PageUtility.GetQueryStringValue("Storagetitle")); //Storagetitle; search.Storageid = Server.UrlDecode(PageUtility.GetQueryStringValue("Storageid"));//StorageId; if (!string.IsNullOrEmpty(PageUtility.GetQueryStringValue("StartActualDate"))) { search.StartActualDate = DateTime.Parse(PageUtility.GetQueryStringValue("StartActualDate")); } if (!string.IsNullOrEmpty(PageUtility.GetQueryStringValue("EndActualDate"))) { search.EndActualDate = DateTime.Parse(PageUtility.GetQueryStringValue("EndActualDate")); } int recordCount = 0; var list = AssetmaintainService.RetrieveAssetReportDetailInfoPaging(search, pageIndex, pcData.PageSize, out recordCount); gvDetails.DataSource = list; gvDetails.DataBind(); pcData.RecordCount = recordCount; pcData.CurrentIndex = pageIndex; }
public List<AssetRunTimeReport> RetrieveAssetRunTimeReport(AssetRunTimeSearch info) { try { var sqlCommand = new StringBuilder(@" select b.storagetitle,b.storageid, c.assetcategoryid,count(c.assetno) as Currentcount from ASSETMAINTAINDETAIL a inner join ASSETMAINTAIN b on a.ASSETMAINTAINID=b.ASSETMAINTAINID inner join asset c on a.assetno=c.assetno"); #region ��Ŀ��ID��ֹ�˾ID) if (info.Storagetitle == Vstorageaddress.RootCompany) { sqlCommand.AppendLine(@" where b.storagetitle = :Storagetitle "); this.Database.AddInParameter(":Storagetitle", DbType.AnsiString, Vstorageaddress.Subcompany); } else if (info.Storagetitle == Vstorageaddress.Project) { sqlCommand.AppendLine(@" where b.storagetitle = :Storagetitle AND b.storageid = :Storageid"); this.Database.AddInParameter(":Storagetitle", DbType.AnsiString, Vstorageaddress.Project); this.Database.AddInParameter(":Storageid", DbType.AnsiString, info.Storageid); } else if (info.Storagetitle == Vstorageaddress.Subcompany) { this.Database.AddInParameter(":Storagetitle", DbType.AnsiString, Vstorageaddress.Subcompany); this.Database.AddInParameter(":Storageid", DbType.AnsiString, info.Storageid); sqlCommand.AppendLine(@" where ((b.storagetitle = :Storagetitle AND b.storageid = :Storageid )"); if (info.ProjectIds.Count > 0) { this.Database.AddInParameter(":Storagetitle0", DbType.AnsiString, Vstorageaddress.Project); this.Database.AddInParameter(":Storageid0", DbType.AnsiString, info.ProjectIds[0]); sqlCommand.AppendLine(@" OR (b.storagetitle = :Storagetitle0 AND (b.storageid = :Storageid0 "); for (int i = 1; i < info.ProjectIds.Count; i++) { this.Database.AddInParameter(":Storageid" + i.ToString(), info.ProjectIds[i]); sqlCommand.AppendLine(@" OR b.storageid=:Storageid" + i.ToString()); } sqlCommand.Append(")"); sqlCommand.Append(")"); } sqlCommand.Append(")"); } else { return new List<AssetRunTimeReport>(); } #endregion #region ʵ��������� if (info.StartActualDate.HasValue) { this.Database.AddInParameter(":StartActualDate", info.StartActualDate.Value.Date); sqlCommand.AppendLine(@" AND b.ACTUALMAINTAINDATE >= :StartActualDate"); } if (info.EndActualDate.HasValue) { this.Database.AddInParameter(":EndActualDate", info.EndActualDate.Value.Date.AddDays(1).AddSeconds(-1)); sqlCommand.AppendLine(@" AND b.ACTUALMAINTAINDATE <= :EndActualDate"); } #endregion sqlCommand.AppendLine(@" group by b.storagetitle,b.storageid, c.assetcategoryid"); return this.Database.ExecuteToList<AssetRunTimeReport>(sqlCommand.ToString()); } finally { this.Database.ClearParameter(); } }
protected void LoadCurrentData() { var search = new AssetRunTimeSearch(); search.Storagetitle = ucSelectStorageAddress.Storagetitle; search.Storageid = ucSelectStorageAddress.StorageId; if (search.Storagetitle == Vstorageaddress.Subcompany) { var currentProjects = VStorageAddress.Where(p => p.Subcompanyid == ucSelectStorageAddress.StorageId && p.Storagetitle == Vstorageaddress.Project).ToList(); search.ProjectIds.AddRange(currentProjects.Select(p => p.Storageid)); } if (ucStartDate.DateValue.HasValue) { search.StartActualDate = ucStartDate.DateValue.Value; } if (ucEndDate.DateValue.HasValue) { search.EndActualDate = ucEndDate.DateValue.Value; } var currentReportData = AssetmoveService.RetrieveAssetRunTimeReport(search); var content = new StringBuilder(); content.AppendLine(@"<table style=""width: 100%; padding-top: 0px;"" cellspacing=""0px"" cellpadding=""0px"""); content.AppendLine(@" align=""center"" class=""gridtable"">"); #region RowHead1 content.AppendLine(@"<tr class=""thbg"" style=""padding: 0 0 0 0"">"); content.AppendLine(@"<th rowspan=""2"" class=""thbg2"" style=""border-left:1px solid #AFD2E8; border-right:1px solid #AFD2E8;height:52px; background:url(images/thbg2.jpg) 0 0 repeat-x; backgroud-color:#BADFF9; color:#2370B0; font-weight:bold; text-align:center; padding:0 0 0 0;"">"); content.AppendLine(" 序号"); content.AppendLine("</th>"); content.AppendLine(@"<th rowspan=""2"" class=""thbg2"" style=""border-left:1px solid #AFD2E8; width:200px; border-right:1px solid #AFD2E8;background:url(images/thbg2.jpg) 0 0 repeat-x;"">"); content.AppendLine("分公司/项目体"); content.AppendLine("</th>"); var categories = AssetCategories.Where(p => string.IsNullOrEmpty(p.Assetparentcategoryid) || p.Assetparentcategoryid == Assetcategory.FixedAssetCategory).ToList(); foreach (var assetcategory in categories) { content.AppendFormat(@"<th colspan=""{0}"" style=""border-left:1px solid #AFD2E8; border-right:1px solid #AFD2E8;"">", AssetCategories.Where(p => p.Assetparentcategoryid == assetcategory.Assetcategoryid).Count()); content.Append(assetcategory.Assetcategoryname); content.AppendLine(" </th>"); } content.AppendLine(" </tr>"); #endregion RowHead1 #region RowHead2 content.AppendLine(@"<tr class=""thbg"" style=""padding: 0 0 0 0"">"); foreach (var assetcategory in categories) { var subCategories = AssetCategories.Where(p => p.Assetparentcategoryid == assetcategory.Assetcategoryid).ToList(); foreach (var subCategory in subCategories) { content.AppendFormat("<th>{0}</th>", subCategory.Assetcategoryname).AppendLine(); } } content.AppendLine("</tr>"); #endregion RowHead2 #region RowBody bool isEven = true; if (search.Storagetitle == Vstorageaddress.RootCompany) { var currentSubCompanies = VStorageAddress.Where(p => p.Storagetitle == Vstorageaddress.Subcompany).ToList(); for (int i = 0; i < currentSubCompanies.Count; i++) { var currentSubCompany = currentSubCompanies[i]; if (currentSubCompany.Storagename.Contains("总")) { continue; } content.AppendFormat(@"<tr {0} style=""padding: 0 0 0 0"">", isEven ? @"class=""even""" : string.Empty).AppendLine(); isEven = !isEven; content.AppendFormat("<td>{0}</td>", i + 2);//序号 content.AppendFormat("<td>{0}</td>", currentSubCompany.Storagename);//分公司 foreach (var assetcategory in categories) { var subCategories = AssetCategories.Where(p => p.Assetparentcategoryid == assetcategory.Assetcategoryid).ToList(); foreach (var subCategory in subCategories) { var currentInfo = currentReportData.Where(p => p.Assetcategoryid == subCategory.Assetcategoryid && p.Storagetitle == Vstorageaddress.Subcompany && p.Storageid == currentSubCompany.Storageid).FirstOrDefault(); if (currentInfo == null) { content.AppendFormat("<td>{0}</td>", 0); } else { content.AppendFormat(@"<td><a href=""javascript:ShowTopDialogFrame('明细', 'Report_AssetMove_Detail.aspx?Assetcategoryid={0}&Storagetitle={1}&Storageid={2}&StartActualDate={3}&EndActualDate={4}','',900,450);"">{5}</a></td>", currentInfo.Assetcategoryid, Server.UrlEncode(currentInfo.Storagetitle), Server.UrlEncode(currentInfo.Storageid), search.StartActualDate.HasValue ? Server.UrlEncode(search.StartActualDate.Value.ToString(UiConst.DateFormat)) : string.Empty, search.EndActualDate.HasValue ? Server.UrlEncode(search.EndActualDate.Value.ToString(UiConst.DateFormat)) : string.Empty, currentInfo.Currentcount).AppendLine(); } } } content.AppendLine("</tr>"); } } else if (search.Storagetitle == Vstorageaddress.Subcompany) { #region 分公司 content.AppendFormat(@"<tr {0} style=""padding: 0 0 0 0"">", isEven ? @"class=""even""" : string.Empty).AppendLine(); isEven = !isEven; content.Append("<td>1</td>");//序号 content.AppendFormat("<td>{0}</td>", ucSelectStorageAddress.Storagename);//分公司 foreach (var assetcategory in categories) { var subCategories = AssetCategories.Where(p => p.Assetparentcategoryid == assetcategory.Assetcategoryid).ToList(); foreach (var subCategory in subCategories) { var currentInfo = currentReportData.Where(p => p.Assetcategoryid == subCategory.Assetcategoryid && p.Storagetitle == Vstorageaddress.Subcompany && p.Storageid == search.Storageid).FirstOrDefault(); if (currentInfo == null) { content.AppendFormat("<td>{0}</td>", 0); } else { content.AppendFormat(@"<td><a href=""javascript:ShowTopDialogFrame('明细', 'Report_AssetMove_Detail.aspx?Assetcategoryid={0}&Storagetitle={1}&Storageid={2}&StartActualDate={3}&EndActualDate={4}','',900,450);"">{5}</a></td>", currentInfo.Assetcategoryid, Server.UrlEncode(currentInfo.Storagetitle), Server.UrlEncode(currentInfo.Storageid), search.StartActualDate.HasValue ? Server.UrlEncode(search.StartActualDate.Value.ToString(UiConst.DateFormat)) : string.Empty, search.EndActualDate.HasValue ? Server.UrlEncode(search.EndActualDate.Value.ToString(UiConst.DateFormat)) : string.Empty, currentInfo.Currentcount).AppendLine(); } } } content.AppendLine("</tr>"); #endregion var currentProjects = VStorageAddress.Where(p => p.Subcompanyid == ucSelectStorageAddress.StorageId && p.Storagetitle == Vstorageaddress.Project).ToList(); for (int i = 0; i < currentProjects.Count; i++) { var currentProject = currentProjects[i]; content.AppendFormat(@"<tr {0} style=""padding: 0 0 0 0"">", isEven ? @"class=""even""" : string.Empty).AppendLine(); isEven = !isEven; content.AppendFormat("<td>{0}</td>", i + 2);//序号 content.AppendFormat("<td>{0}</td>", currentProject.Storagename);//项目体 foreach (var assetcategory in categories) { var subCategories = AssetCategories.Where(p => p.Assetparentcategoryid == assetcategory.Assetcategoryid).ToList(); foreach (var subCategory in subCategories) { var currentInfo = currentReportData.Where(p => p.Assetcategoryid == subCategory.Assetcategoryid && p.Storagetitle == Vstorageaddress.Project && p.Storageid == currentProject.Storageid).FirstOrDefault(); if (currentInfo == null) { content.AppendFormat("<td>{0}</td>", 0); } else { content.AppendFormat(@"<td><a href=""javascript:ShowTopDialogFrame('明细', 'Report_AssetMove_Detail.aspx?Assetcategoryid={0}&Storagetitle={1}&Storageid={2}&StartActualDate={3}&EndActualDate={4}','',900,450);"">{5}</a></td>", currentInfo.Assetcategoryid, Server.UrlEncode(currentInfo.Storagetitle), Server.UrlEncode(currentInfo.Storageid), search.StartActualDate.HasValue ? Server.UrlEncode(search.StartActualDate.Value.ToString(UiConst.DateFormat)) : string.Empty, search.EndActualDate.HasValue ? Server.UrlEncode(search.EndActualDate.Value.ToString(UiConst.DateFormat)) : string.Empty, currentInfo.Currentcount).AppendLine(); } } } content.AppendLine("</tr>"); } } else if (search.Storagetitle == Vstorageaddress.Project) { content.AppendFormat(@"<tr {0} style=""padding: 0 0 0 0"">", isEven ? @"class=""even""" : string.Empty).AppendLine(); isEven = !isEven; content.Append("<td>1</td>");//序号 content.AppendFormat("<td>{0}</td>", ucSelectStorageAddress.Storagename);//分公司/项目体 foreach (var assetcategory in categories) { var subCategories = AssetCategories.Where(p => p.Assetparentcategoryid == assetcategory.Assetcategoryid).ToList(); foreach (var subCategory in subCategories) { var currentInfo = currentReportData.Where(p => p.Assetcategoryid == subCategory.Assetcategoryid). FirstOrDefault(); if (currentInfo == null) { content.AppendFormat("<td>{0}</td>", 0); } else { content.AppendFormat(@"<td><a href=""javascript:ShowTopDialogFrame('明细', 'Report_AssetMove_Detail.aspx?Assetcategoryid={0}&Storagetitle={1}&Storageid={2}&StartActualDate={3}&EndActualDate={4}','',900,450);"">{5}</a></td>", currentInfo.Assetcategoryid, Server.UrlEncode(currentInfo.Storagetitle), Server.UrlEncode(currentInfo.Storageid), search.StartActualDate.HasValue ? Server.UrlEncode(search.StartActualDate.Value.ToString(UiConst.DateFormat)) : string.Empty, search.EndActualDate.HasValue ? Server.UrlEncode(search.EndActualDate.Value.ToString(UiConst.DateFormat)) : string.Empty, currentInfo.Currentcount).AppendLine(); } } } content.AppendLine("</tr>"); } #endregion #region 合计信息 content.AppendFormat(@"<tr {0} style=""padding: 0 0 0 0"">", isEven ? @"class=""even""" : string.Empty).AppendLine(); content.Append(@"<td colspan=""2"">合计</td>");//序号 foreach (var assetcategory in categories) { var subCategories = AssetCategories.Where(p => p.Assetparentcategoryid == assetcategory.Assetcategoryid).ToList(); foreach (var subCategory in subCategories) { var currentCategorycount = currentReportData.Where(p => p.Assetcategoryid == subCategory.Assetcategoryid).Sum(p => p.Currentcount); content.AppendFormat("<td>{0}</td>", currentCategorycount); } } content.AppendLine("</tr>"); #endregion content.AppendLine(" </table>"); litContent.Text = content.ToString(); }
public List<Asset> RetrieveAssetRegisterReportDetailInfoPaging(AssetRunTimeSearch info, int pageIndex, int pageSize, out int count) { return Management.RetrieveAssetRegisterReportDetailInfoPaging(info, pageIndex, pageSize, out count); }
public List<AssetRunTimeReport> RetrieveAssetRunTimeReport(AssetRunTimeSearch info) { return Management.RetrieveAssetRunTimeReport(info); }
public List<Asset> RetrieveAssetRegisterReportDetailInfoPaging(AssetRunTimeSearch info, int pageIndex, int pageSize, out int count) { try { var sqlCommand = new StringBuilder(@" select c.* from asset c"); #region (ϵͳ)�豸���� count = 0; if (string.IsNullOrEmpty(info.Assetcategoryid)) { return new List<Asset>(); } sqlCommand.AppendLine(@" where c.ASSETCATEGORYID = :Assetcategoryid and c.STORAGEFLAG = :Storagetitle AND c.STORAGE = :Storageid"); this.Database.AddInParameter(":Assetcategoryid", DbType.AnsiString, info.Assetcategoryid); this.Database.AddInParameter(":Storagetitle", DbType.AnsiString, info.Storagetitle); this.Database.AddInParameter(":Storageid", DbType.AnsiString, info.Storageid); #endregion //#region ʵ��������� //if (info.StartActualDate.HasValue) //{ // this.Database.AddInParameter(":StartActualDate", info.StartActualDate.Value.Date); // sqlCommand.AppendLine(@" AND b.ACTUALMOVEDATE >= :StartActualDate"); //} //if (info.EndActualDate.HasValue) //{ // this.Database.AddInParameter(":EndActualDate", info.EndActualDate.Value.Date.AddDays(1).AddSeconds(-1)); // sqlCommand.AppendLine(@" AND b.ACTUALMOVEDATE <= :EndActualDate"); //} //#endregion sqlCommand.AppendLine(@" ORDER BY assetno DESC"); return this.ExecuteReaderPaging<Asset>(sqlCommand.ToString(), pageIndex, pageSize, out count); } finally { this.Database.ClearParameter(); } }