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