public List<Assetrunplan> RetrieveAssetrunplanByCondition(AssetrunplanSearch info)
{
return Management.RetrieveAssetrunplanByCondition(info);
}
protected string LoadCurrentData(string plandatecycle)
{
var search = new AssetrunplanSearch();
search.Storageflag = ucSelectStorageAddress.Storagetitle;
search.Storage = ucSelectStorageAddress.StorageId;
search.Plandatecycle = plandatecycle;
if (search.Storageflag == Vstorageaddress.Subcompany)
{
var currentProjects = VStorageAddress.Where(p => p.Subcompanyid == ucSelectStorageAddress.StorageId && p.Storagetitle == Vstorageaddress.Project).ToList();
search.ProjectIds.AddRange(currentProjects.Select(p => p.Storageid));
}
var currentReportData = AssetrunplanService.RetrieveAssetrunplanByCondition(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.Storageflag == 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.Storageflag == Vstorageaddress.Subcompany
&& p.Storage == currentSubCompany.Storageid).FirstOrDefault();
if (currentInfo == null)
{
content.AppendFormat("<td>{0}</td>", 0);
}
else
{
content.AppendFormat(@"<td>{0}</td>",
currentInfo.Assetcount).AppendLine();
}
}
}
content.AppendLine("</tr>");
}
}
else if (search.Storageflag == 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.Storageflag == Vstorageaddress.Subcompany
&& p.Storage == search.Storage).FirstOrDefault();
if (currentInfo == null)
{
content.AppendFormat("<td>{0}</td>", 0);
}
else
{
content.AppendFormat(@"<td>{0}</td>",
currentInfo.Assetcount).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.Storageflag == Vstorageaddress.Project
&& p.Storage == currentProject.Storageid).FirstOrDefault();
if (currentInfo == null)
{
content.AppendFormat("<td>{0}</td>", 0);
}
else
{
content.AppendFormat(@"<td>{0}</td>",
currentInfo.Assetcount).AppendLine();
}
}
}
content.AppendLine("</tr>");
}
}
else if (search.Storageflag == 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>{0}</td>",
currentInfo.Assetcount).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.Assetcount);
content.AppendFormat("<td>{0}</td>", currentCategorycount);
}
}
content.AppendLine("</tr>");
#endregion
content.AppendLine(" </table>");
return content.ToString();
}
public List<Assetrunplan> RetrieveAssetrunplanByCondition(AssetrunplanSearch search)
{
try
{
if (string.IsNullOrEmpty(search.Plandatecycle) || string.IsNullOrEmpty(search.Storageflag) || string.IsNullOrEmpty(search.Storage))
{
return new List<Assetrunplan>();
}
var sqlCommand = new StringBuilder(@" SELECT ""ASSETRUNPLAN"".""PLANID"",""ASSETRUNPLAN"".""ASSETPARENTCATEGORYID"",""ASSETRUNPLAN"".""ASSETCATEGORYID""
,""ASSETRUNPLAN"".""STORAGEFLAG"",""ASSETRUNPLAN"".""STORAGE""
,""ASSETRUNPLAN"".""PLANDATECYCLE"",""ASSETRUNPLAN"".""CREATEDDATE"",""ASSETRUNPLAN"".""STARTDATE""
,""ASSETRUNPLAN"".""ENDDATE"",""ASSETRUNPLAN"".""PLANCATEGORY"",ASSETCOUNT
,f.ASSETCATEGORYNAME AS Assetparentcategoryname,e.ASSETCATEGORYNAME AS Assetsubcategoryname
FROM ""ASSETRUNPLAN""
INNER JOIN ASSETCATEGORY e on e.ASSETCATEGORYID=ASSETRUNPLAN.ASSETCATEGORYID
INNER JOIN ASSETCATEGORY f on f.ASSETCATEGORYID=e.ASSETPARENTCATEGORYID
");
var condition = new List<string>();
#region ʱ��Σ��磺�ܼƻ���20120723-20120729��
this.Database.AddInParameter(":Plandatecycle", search.Plandatecycle);
condition.Add(@" ""ASSETRUNPLAN"".""PLANDATECYCLE"" = :Plandatecycle");
#endregion
#region ��Ŀ��ID��ֹ�˾ID
var subsqlcondition = new StringBuilder();
if (search.Storageflag == Vstorageaddress.RootCompany)
{
subsqlcondition.AppendLine(@" ASSETRUNPLAN.STORAGEFLAG = :STORAGEFLAG ");
this.Database.AddInParameter(":STORAGEFLAG", DbType.AnsiString, Vstorageaddress.Subcompany);
}
else if (search.Storageflag == Vstorageaddress.Project)
{
subsqlcondition.AppendLine(@" ASSETRUNPLAN.STORAGEFLAG = :STORAGEFLAG AND ASSETRUNPLAN.STORAGE = :Storage");
this.Database.AddInParameter(":STORAGEFLAG", DbType.AnsiString, Vstorageaddress.Project);
this.Database.AddInParameter(":Storage", DbType.AnsiString, search.Storage);
}
else if (search.Storageflag == Vstorageaddress.Subcompany)
{
this.Database.AddInParameter(":STORAGEFLAG", DbType.AnsiString, Vstorageaddress.Subcompany);
this.Database.AddInParameter(":Storage", DbType.AnsiString, search.Storage);
subsqlcondition.AppendLine(@" ((ASSETRUNPLAN.STORAGEFLAG = :STORAGEFLAG AND ASSETRUNPLAN.STORAGE = :Storage )");
if (search.ProjectIds.Count > 0)
{
this.Database.AddInParameter(":Storagetitle0", DbType.AnsiString, Vstorageaddress.Project);
this.Database.AddInParameter(":Storage0", DbType.AnsiString, search.ProjectIds[0]);
subsqlcondition.AppendLine(@" OR (ASSETRUNPLAN.STORAGEFLAG = :Storagetitle0 AND (ASSETRUNPLAN.STORAGE = :Storage0 ");
for (int i = 1; i < search.ProjectIds.Count; i++)
{
this.Database.AddInParameter(":Storage" + i.ToString(), search.ProjectIds[i]);
subsqlcondition.AppendLine(@" OR ASSETRUNPLAN.STORAGE=:Storage" + i.ToString());
}
subsqlcondition.Append(")");
subsqlcondition.Append(")");
}
subsqlcondition.Append(")");
}
else
{
return new List<Assetrunplan>();
}
if(subsqlcondition.Length > 0)
condition.Add(subsqlcondition.ToString());
#endregion
if (condition.Count > 0)
{
for (int i = 0; i < condition.Count; i++)
{
sqlCommand.Append(i == 0 ? " WHERE " : " AND ").Append(condition[i]);
}
}
sqlCommand.AppendLine(@" ORDER BY ""ASSETRUNPLAN"".""PLANID"" DESC");
return this.Database.ExecuteToList<Assetrunplan>(sqlCommand.ToString());
}
finally
{
this.Database.ClearParameter();
}
}