public static string GetColumnByDate(SpreadSheetDocumentType DocumentType, DateTime Date)
{
switch (DocumentType)
{
case SpreadSheetDocumentType.WeekYear:
int YW = Iso8601WeekNumber.GetYearWeekNumber(Date);
return(string.Format("{0:0000}-W{1:00}", YW / 100, YW % 100));
case SpreadSheetDocumentType.MonthQuarterYear:
if (Date.Month >= 1 && Date.Month < 4)
{
return(string.Format("{0:0000}-Q1-{1:00}", Date.Year, Date.Month));
}
else if (Date.Month >= 4 && Date.Month < 7)
{
return(string.Format("{0:0000}-Q2-{1:00}", Date.Year, Date.Month));
}
else if (Date.Month >= 7 && Date.Month < 10)
{
return(string.Format("{0:0000}-Q3-{1:00}", Date.Year, Date.Month));
}
else
{
return(string.Format("{0:0000}-Q4-{1:00}", Date.Year, Date.Month));
}
case SpreadSheetDocumentType.QuarterYear:
if (Date.Month >= 1 && Date.Month < 4)
{
return(string.Format("{0:0000}-Q1", Date.Year));
}
else if (Date.Month >= 4 && Date.Month < 7)
{
return(string.Format("{0:0000}-Q2", Date.Year));
}
else if (Date.Month >= 7 && Date.Month < 10)
{
return(string.Format("{0:0000}-Q3", Date.Year));
}
else
{
return(string.Format("{0:0000}-Q4", Date.Year));
}
case SpreadSheetDocumentType.Year:
return(string.Format("{0:0000}-T", Date.Year));
case SpreadSheetDocumentType.Total:
return("TT");
}
throw new NotSupportedException("Not supported document type.");
}
/// <summary>
/// Activates the specified project id.
/// </summary>
/// <param name="ProjectId">The project id.</param>
/// <param name="DocumentType">Type of the document.</param>
/// <param name="BaseTemplateName">Name of the base template.</param>
public static void Activate(int ProjectId, SpreadSheetDocumentType DocumentType, string BaseTemplateName)
{
using (DbTransaction tran = DbTransaction.Begin())
{
if (!IsActive(ProjectId))
{
ProjectSpreadSheetRow newItem = new ProjectSpreadSheetRow();
newItem.ProjectId = ProjectId;
newItem.DocumentType = (int)DocumentType;
newItem.BaseTemplateName = BaseTemplateName;
newItem.Update();
}
tran.Commit();
}
}
/// <summary>
/// Initializes a new instance of the <see cref="SpreadSheetDocument"/> class.
/// </summary>
/// <param name="DocumentType">Type of the document.</param>
public SpreadSheetDocument(SpreadSheetDocumentType DocumentType)
{
_type = DocumentType;
}
private void GenerateScript()
{
ArrayList ProjectIds = new ArrayList();
switch (ProjectListType)
{
case "All":
using (IDataReader reader = Project.GetListProjects())
{
while (reader.Read())
{
ProjectIds.Add(int.Parse(reader["ProjectId"].ToString()));
}
}
break;
case "Portfolio":
DataTable dt = Project.GetListProjectGroupedByPortfolio(int.Parse(ProjectListData), 0, 0);
if (dt != null && dt.Rows.Count > 0)
{
foreach (DataRow dr in dt.Rows)
{
int prid = int.Parse(dr["ProjectId"].ToString());
if (prid > 0)
{
ProjectIds.Add(prid);
}
}
}
break;
case "Custom":
if (ProjectListData != null && ProjectListData.Length > 0)
{
string[] Ids = ProjectListData.Split(';');
if (Ids != null && Ids.Length > 0)
{
for (int i = 0; i < Ids.Length; i++)
{
ProjectIds.Add(int.Parse(Ids[i]));
}
}
}
break;
default:
break;
}
SpreadSheetDocumentType type = SpreadSheetDocumentType.Total;
switch (FinanceType)
{
case "1":
type = SpreadSheetDocumentType.WeekYear;
break;
case "2":
type = SpreadSheetDocumentType.MonthQuarterYear;
break;
case "3":
type = SpreadSheetDocumentType.QuarterYear;
break;
case "4":
type = SpreadSheetDocumentType.Year;
break;
case "5":
type = SpreadSheetDocumentType.Total;
break;
}
SpreadSheetView view = null;
if (!Reverse)
{
view = ProjectSpreadSheet.CompareProjects(ProjectIds, type, BasePlan1, FromYear, ToYear);
}
else
{
view = ProjectSpreadSheet.CompareProjectsReverse(ProjectIds, type, BasePlan1, FromYear, ToYear);
}
SpreadSheetView view2;
if (BasePlan2 != -2)
{
if (!Reverse)
{
view2 = ProjectSpreadSheet.CompareProjects(ProjectIds, type, BasePlan2, FromYear, ToYear);
}
else
{
view2 = ProjectSpreadSheet.CompareProjectsReverse(ProjectIds, type, BasePlan2, FromYear, ToYear);
}
}
int colCount = view.Columns.Length;
//int maxWidth = 950;
StringBuilder sb = new StringBuilder();
sb.AppendLine("<script type='text/javascript'>");
sb.AppendLine("//<![CDATA[");
sb.AppendLine("if (mygrid2 == null)");
sb.AppendLine(" mygrid2 = new dhtmlXGridObject('gridbox');");
//sb.Append("");
#region Bind: Header Caption
if (view.Document.DocumentType != SpreadSheetDocumentType.Total && view.Document.DocumentType != SpreadSheetDocumentType.Year)
{
sb.AppendFormat("mygrid2.setHeader(\"{0}", LocRM.GetString("tProjects"));
int YearCounter = ToYear - FromYear + 1;
int ColSpanRate = -1;
#region Year
if (view.Document.DocumentType == SpreadSheetDocumentType.QuarterYear)
{
ColSpanRate = 5;
}
else if (view.Document.DocumentType == SpreadSheetDocumentType.MonthQuarterYear)
{
ColSpanRate = 17;
}
else if (view.Document.DocumentType == SpreadSheetDocumentType.WeekYear)
{
ColSpanRate = 53;
}
int RowSpanRate = (view.Columns.Length - YearCounter) / (YearCounter * 4);
//int RowSpanRate = 45;
int yearCount = 0;
for (int i = 0; i < view.Columns.Length - 1; i++)
{
if (view.Document.DocumentType == SpreadSheetDocumentType.MonthQuarterYear)
{
if (i % (ColSpanRate) == 0)
{
sb.AppendFormat(",{0}", FromYear + yearCount);
}
else if (((i - yearCount + 1) % (ColSpanRate - 1) == 0) && (i - yearCount + 1 != 0))
{
sb.AppendFormat(",{0}", LocRM.GetString("tYear"));
}
else
{
sb.Append(",#cspan");
}
}
else
{
if (i % (ColSpanRate) == 0)
{
sb.AppendFormat(",{0}", FromYear + yearCount);
}
else if (((i - yearCount + 1) % (ColSpanRate - 1) == 0) && (i - yearCount + 1 != 0))
{
sb.AppendFormat(",{0}", LocRM.GetString("tYear"));
}
else
{
sb.Append(",#cspan");
}
}
if (i % (ColSpanRate) == 0)
{
yearCount++;
}
}
//}
sb.AppendFormat(",{0}\");", LocRM.GetString("tTotalSum"));
#endregion
#region Quartals
if (view.Document.DocumentType == SpreadSheetDocumentType.MonthQuarterYear)
{
sb.Append("mygrid2.attachHeader([\"#rspan\"");
for (int i = 0; i < view.Columns.Length - 1; i++)
{
int counter = i % ColSpanRate;
if (counter % (RowSpanRate) == 0 && counter != 16)
{
sb.AppendFormat(",\"{0} {1}\"", LocRM.GetString("tQuarterYear"), /*(((i + 1) / RowSpanRate) % 4) + 1*/ (counter / 4) + 1);
}
else
if ((counter + 1) % RowSpanRate == 0)
{
sb.AppendFormat(", \"{0}\"", LocRM.GetString("tQuarterTotal"));
}
else
if (counter == 16)
{
sb.Append(",\"#rspan\"");
}
else
{
sb.Append(",\"#cspan\"");
}
}
sb.Append(",\"#rspan\"]);");
}
#endregion
#region BindServerData
sb.AppendFormat("mygrid2.attachHeader([\"#rspan\", \"{0}\"", ((Column)view.Columns[0]).Name);
int _counter = 0;
for (int i = 1; i < view.Columns.Length - 1; i++)
{
if (view.Document.DocumentType == SpreadSheetDocumentType.MonthQuarterYear)
{
if (i % ColSpanRate == 0)
{
_counter++;
}
if ((i + 1 - _counter) % RowSpanRate == 0)
{
sb.Append(",\"#rspan\"");
}
else
if ((i + 1) % ColSpanRate == 0)
{
sb.Append(",\"#rspan\"");
}
else
{
sb.AppendFormat(",\" {0}\"", ((Column)view.Columns[i]).Name);
}
}
else
{
if ((i + 1) % ColSpanRate == 0)
{
sb.Append(",\"#rspan\"");
}
else
{
sb.AppendFormat(",\" {0}\"", ((Column)view.Columns[i]).Name);
}
}
}
sb.Append(",\"#rspan\"]);");
#endregion
}
else
{
//for scales: Year, Project Total
sb.AppendFormat("mygrid2.setHeader(\"{0}", LocRM.GetString("tProjects"));
for (int i = 0; i < view.Columns.Length; i++)
{
sb.AppendFormat(",{0}", ((Column)view.Columns[i]).Name);
}
sb.Append("\");");
}
#endregion
#region Bind: Header Width
sb.AppendFormat("mygrid2.setImagePath('{0}');", ResolveUrl("~/Layouts/Images/dhtmlGrid/imgs"));
sb.Append("mygrid2.setInitWidths(\"100");
for (int i = 0; i < view.Columns.Length; i++)
{
sb.AppendFormat(",{0}", 60 /*(int)(maxWidth / view.Columns.Length)*/);
}
sb.Append("\");");
#endregion
#region Bind: Header Align
sb.Append("mygrid2.setColAlign(\"left");
for (int i = 0; i < view.Columns.Length; i++)
{
sb.Append(",right");
}
sb.Append("\");");
#endregion
#region Bind: Cols CanEdit
sb.Append("mygrid2.setColTypes(\"tree");
for (int i = 0; i < colCount; i++)
{
sb.Append(",ro");
}
sb.Append("\");");
#endregion
#region Bind: Cols Color
sb.Append("mygrid2.setColumnColor(\"#E1ECFC");
for (int i = 1; i < colCount; i++)
{
sb.Append(",white");
}
sb.Append("\");");
#endregion
sb.AppendLine("mygrid2.enableKeyboardSupport(false);mygrid2.setOnLoadingEnd('EnableApplyButton');mygrid2.enableEditEvents(false,false,false);mygrid2.init();");
if (ddBasePlan2.SelectedValue != "-2")
{
sb.AppendLine("qstring2='&compare=1';");
sb.AppendFormat("var xmlurl='{0}?ProjectFinanceCompare=1'+qstring2;", ResolveUrl("~/Modules/XmlForTreeView.aspx"));
sb.AppendLine("mygrid2.loadXML(xmlurl);");
sb.AppendLine("compareMode=1;");
}
else
{
sb.AppendFormat("var xmlurl='{0}?ProjectFinanceCompare=1';", ResolveUrl("~/Modules/XmlForTreeView.aspx"));
sb.AppendLine("mygrid2.loadXML(xmlurl);");
sb.AppendLine("compareMode=0;");
}
sb.AppendLine("//]]>");
sb.AppendLine("</script>");
this.Page.ClientScript.RegisterStartupScript(this.GetType(), Guid.NewGuid().ToString(), sb.ToString());
}
/// <summary>
/// Initializes a new instance of the <see cref="SpreadSheetDocument"/> class.
/// </summary>
/// <param name="DocumentType">Type of the document.</param>
public SpreadSheetDocument(SpreadSheetDocumentType DocumentType)
{
_type = DocumentType;
}
protected void Page_Load(object sender, System.EventArgs e)
{
XmlDocument doc = new XmlDocument();
UserLightPropertyCollection pc = Security.CurrentUser.Properties;
ArrayList PrIds = LoadProjectList();
SpreadSheetDocumentType type = SpreadSheetDocumentType.WeekYear;
switch (FinanceType)
{
case "1":
type = SpreadSheetDocumentType.WeekYear;
break;
case "2":
type = SpreadSheetDocumentType.MonthQuarterYear;
break;
case "3":
type = SpreadSheetDocumentType.QuarterYear;
break;
case "4":
type = SpreadSheetDocumentType.Year;
break;
case "5":
type = SpreadSheetDocumentType.Total;
break;
}
SpreadSheetView view = null;
if (!Reverse)
{
view = ProjectSpreadSheet.CompareProjects(PrIds, type, BasePlan1,
FromYear, ToYear);
}
else
{
view = ProjectSpreadSheet.CompareProjectsReverse(PrIds, type, BasePlan1,
FromYear, ToYear);
}
Response.ContentType = "text/xml";
if (BasePlan2 == -2)
{
doc = ProjectSpreadSheet.CreateViewDocForAnalysis(view);
}
else
{
SpreadSheetView view2 = null;
if (!Reverse)
{
view2 = ProjectSpreadSheet.CompareProjects(PrIds, type,
BasePlan2, FromYear, ToYear);
}
else
{
view2 = ProjectSpreadSheet.CompareProjectsReverse(PrIds, type,
BasePlan2, FromYear, ToYear);
}
doc = ProjectSpreadSheet.CreateViewCompareDocForAnalysis(view, view2);
}
if (doc != null)
{
HttpContext.Current.Response.Clear();
HttpContext.Current.Response.Charset = "utf-8";
HttpContext.Current.Response.AddHeader("Content-Type", "application/octet-stream");
HttpContext.Current.Response.AddHeader("content-disposition", "attachment; filename=BusinessScores.xls");
string Header = "<html><head><meta http-equiv=\"Content-Type\" content=\"application/octet-stream; charset=utf-8\"></head><body><table>";
string Footer = "</table></body></html>";
#region BindTableBody
string body = string.Empty;
string sbody = string.Empty;
string childrows = string.Empty;;
XmlNode ParentNode = doc.DocumentElement;
foreach (XmlNode node in ParentNode.ChildNodes)
{
sbody = "<tr>";
childrows = "<tr>";
foreach (XmlNode node2 in node.ChildNodes)
{
foreach (XmlNode cnode in node2.ChildNodes)
{
if (node2.Name == "row")
{
if (BasePlan2 != -2)
{
childrows += "<td style='mso-number-format:\\@'>";
}
else
{
childrows += "<td>";
}
childrows += cnode.InnerText;
childrows += "</td>";
}
else
{
if (node2.Name == "cell")
{
if (BasePlan2 != -2)
{
sbody += "<td style='border-top:1px solid black; mso-number-format:\\@'><b>";
}
else
{
sbody += "<td style='border-top:1px solid black'><b>";
}
sbody += node2.InnerText;
sbody += "<b></td>";
}
}
}
childrows += "</tr>";
}
sbody += "</tr>";
sbody += childrows;
body += sbody;
}
#endregion
#region BindTableHeader
string header1 = string.Empty;
string header2 = string.Empty;
string header3 = string.Empty;
if (view.Document.DocumentType != SpreadSheetDocumentType.Total && view.Document.DocumentType != SpreadSheetDocumentType.Year)
{
int YearCounter = ToYear - FromYear + 1;
int ColSpanRate = -1;
#region Year
if (view.Document.DocumentType == SpreadSheetDocumentType.QuarterYear)
{
ColSpanRate = 5;
header1 += "<tr><td rowspan='2' style='border:1px solid black' align='center' >" + LocRM.GetString("tProjects") + "</td>";
header2 += "<tr>";
}
else
if (view.Document.DocumentType == SpreadSheetDocumentType.MonthQuarterYear)
{
ColSpanRate = 17;
header1 += "<tr><td rowspan='3' style='border:1px solid black' align='center' >" + LocRM.GetString("tProjects") + "</td>";
header2 += "<tr>";
header3 += "<tr>";
}
else
if (view.Document.DocumentType == SpreadSheetDocumentType.WeekYear)
{
ColSpanRate = 53;
header1 += "<tr><td rowspan='2' style='border:1px solid black' align='center' >" + LocRM.GetString("tProjects") + "</td>";
header2 += "<tr>";
}
int RowSpanRate = (view.Columns.Length - YearCounter) / (YearCounter * 4);
//int RowSpanRate = 45;
int yearCount = 0;
int tmp = 0;
int tmp2 = 0;
for (int i = 0; i < view.Columns.Length - 1; i++)
{
if (view.Document.DocumentType == SpreadSheetDocumentType.MonthQuarterYear)
{
if (i % (ColSpanRate) == 0)
{
tmp = FromYear + yearCount;
tmp2 = ColSpanRate - 1;
header1 += "<td colspan='" + tmp2.ToString() + "' style='border:1px solid black' align='center' >" + tmp.ToString() + "</td>";
}
else
if (((i - yearCount + 1) % (ColSpanRate - 1) == 0) && (i - yearCount + 1 != 0))
{
header1 += "<td rowspan='3' style='border:1px solid black' align='center' >" + LocRM.GetString("tYear") + "</td>";
}
}
else //Week/Year or Quarter/Year
{
if (i % (ColSpanRate) == 0)
{
tmp = FromYear + yearCount;
tmp2 = ColSpanRate - 1;
header1 += "<td colspan='" + tmp2.ToString() + "' style='border:1px solid black' align='center' >" + tmp.ToString() + "</td>";
}
else
if (((i - yearCount + 1) % (ColSpanRate - 1) == 0) && (i - yearCount + 1 != 0))
{
header1 += "<td rowspan='2' style='border:1px solid black' align='center' >" + LocRM.GetString("tYear") + "</td>";
}
}
if (i % (ColSpanRate) == 0)
{
yearCount++;
}
}
if (view.Document.DocumentType == SpreadSheetDocumentType.MonthQuarterYear)
{
header1 += "<td rowspan='3' style='border:1px solid black' align='center' >" + LocRM.GetString("tTotalSum") + "</td></tr>";
}
else
{
header1 += "<td rowspan='2' style='border:1px solid black' align='center' >" + LocRM.GetString("tTotalSum") + "</td></tr>";
}
#endregion
#region Quartals
if (view.Document.DocumentType == SpreadSheetDocumentType.MonthQuarterYear)
{
for (int i = 0; i < view.Columns.Length - 1; i++)
{
int counter = i % ColSpanRate;
if (counter % (RowSpanRate) == 0 && counter != 16)
{
tmp = (counter / 4) + 1;
header2 += "<td colspan='3' style='border:1px solid black' align='center' >" + LocRM.GetString("tQuarterYear") + " " + tmp.ToString() + "</td>";
}
else
if ((counter + 1) % RowSpanRate == 0)
{
header2 += "<td rowspan='2' style='border:1px solid black' align='center' >" + LocRM.GetString("tQuarterTotal") + "</td>";
}
else
if (counter == 16)
{
//header2+="<td></td>";
}
}
header2 += "</tr>";
}
#endregion
#region BindServerData
//sb.AppendFormat("mygrid2.attachHeader([\"#rspan\", \"{0}\"",((Column)view.Columns[0]).Name);
int _counter = 0;
//header3+="<td>"+((Column)view.Columns[0]).Name+"</td>";
for (int i = 0; i < view.Columns.Length - 1; i++)
{
if (view.Document.DocumentType == SpreadSheetDocumentType.MonthQuarterYear)
{
if (i % ColSpanRate == 0 && i > 0)
{
_counter++;
}
if ((i + 1 - _counter) % RowSpanRate == 0 && i > 0)
{
}
else
if ((i + 1) % ColSpanRate == 0 && i > 0)
{
}
else
{
header3 += "<td style='border:1px solid black' align='center' >" + ((Column)view.Columns[i]).Name + "</td>";
}
}
else
{
if ((i + 1) % ColSpanRate == 0)
{
}
else
{
header3 += "<td style='border:1px solid black' align='center' >" + ((Column)view.Columns[i]).Name + "</td>";
}
}
}
header3 += "</tr>";
#endregion
body = body.Insert(0, header1 + header2 + header3);
}
else
{
header1 = "<tr>";
header1 += "<td style='border:1px solid black' align='center' >" + LocRM.GetString("tProjects") + "</td>";
for (int i = 0; i < view.Columns.Length; i++)
{
header1 += "<td style='border:1px solid black' align='center' >" + ((Column)view.Columns[i]).Name + "</td>";
}
header1 += "</tr>";
body = body.Insert(0, header1);
}
#endregion
HttpContext.Current.Response.Write(String.Concat(Header, body, Footer));
HttpContext.Current.Response.End();
}
}
public static SpreadSheetView CompareProjects(ArrayList ProjectIdList,
SpreadSheetDocumentType DocumentType,
int Index,
int FromYear, int ToYear)
{
if (!(Security.IsUserInGroup(InternalSecureGroups.PowerProjectManager) ||
Security.IsUserInGroup(InternalSecureGroups.ExecutiveManager)))
throw new AccessDeniedException();
SpreadSheetDocument document = new SpreadSheetDocument(DocumentType);
// Step 1. Create Template
#region Create Template
SpreadSheetTemplate template = document.Template;
BusinessScore[] businessScoreList = BusinessScore.List();
foreach (int projectId in ProjectIdList)
{
if (!IsActive(projectId))
continue;
string projectName = string.Empty;
using (IDataReader reader = Project.GetProject(projectId, false))
{
if (reader.Read())
{
projectName = (string)reader["Title"];
}
}
Block projectBlock = new Block(string.Format("Prj{0:00000}", projectId),
projectName, false, true, string.Empty, string.Empty);
foreach (BusinessScore score in businessScoreList)
{
Row scoreRow = new Row(string.Format("{0}_{1}", projectBlock.Id, score.Key),
score.Name, true, string.Empty, string.Empty);
projectBlock.ChildRows.Add(scoreRow);
}
string expression = projectBlock.Expression;
template.Rows.Add(projectBlock);
}
#endregion
// Step 2. Load Document
#region Load Document
Hashtable hashBusinessScoreKeyById = new Hashtable();
Hashtable hashBusinessScoreIdByKey = new Hashtable();
// Load hashBusinessScoreKeyById
foreach (BusinessScore bs in businessScoreList)
{
hashBusinessScoreKeyById.Add(bs.BusinessScoreId, bs.Key);
hashBusinessScoreIdByKey.Add(bs.Key, bs.BusinessScoreId);
}
if (Index >= 0)
{
foreach (BusinessScoreDataRow dataRow in BusinessScoreDataRow.List(Index))
{
if (ProjectIdList.Contains(dataRow.ProjectId) &&
hashBusinessScoreKeyById.ContainsKey(dataRow.BusinessScoreId))
{
string ColumnId = SpreadSheetView.GetColumnByDate(DocumentType, dataRow.Date);
string RowId = string.Format("Prj{0:00000}_{1}", dataRow.ProjectId, hashBusinessScoreKeyById[dataRow.BusinessScoreId]);
Cell cell = document.GetCell(ColumnId, RowId);
if (cell == null)
{
cell = document.AddCell(ColumnId, RowId, CellType.Common, 0);
}
cell.Value += dataRow.Value;
}
}
}
else
{
foreach (int ProjectId in ProjectIdList)
{
foreach (ActualFinances finance in ActualFinances.List(ProjectId, ObjectTypes.Project))
{
SpreadSheetView projectFactView = LoadView(ProjectId, -1, FromYear, ToYear);
if (projectFactView != null)
{
foreach (string key in hashBusinessScoreIdByKey.Keys)
{
string srcColumnId = SpreadSheetView.GetColumnByDate(projectFactView.Document.DocumentType, finance.Date);
int srcColumnIndex = projectFactView.GetColumnIndex(srcColumnId);
int srcRowIndex = projectFactView.GetRowIndex(key);
if (srcColumnIndex != -1 && srcRowIndex != -1)
{
Cell srcCell = projectFactView.GetCell(srcColumnIndex, srcRowIndex);
if (srcCell != null)
{
string ColumnId = SpreadSheetView.GetColumnByDate(DocumentType, finance.Date);
string RowId = string.Format("Prj{0:00000}_{1}", ProjectId, key);
Cell destCell = document.GetCell(ColumnId, RowId);
if (destCell == null)
{
destCell = document.AddCell(ColumnId, RowId, CellType.Common, 0);
destCell.Value += srcCell.Value;
}
else
{
if (srcCell.Type != CellType.AutoCalc)
{
destCell.Value += srcCell.Value;
}
}
}
}
}
}
}
/*foreach(ActualFinances finance in ActualFinances.List(ProjectId, ObjectTypes.Project))
{
if(hashBusinessScoreIdByKey.ContainsKey(finance.RowId))
{
string ColumnId = SpreadSheetView.GetColumnByDate(DocumentType,finance.Date);
string RowId = string.Format("Prj{0:00000}_{1}", ProjectId , finance.RowId);
Cell cell = document.GetCell(ColumnId, RowId);
if(cell==null)
{
cell = document.AddCell(ColumnId, RowId, CellType.Common, 0);
}
cell.Value += finance.Value;
}
}*/
}
}
#endregion
// Step 3. Create View And Return
return new SpreadSheetView(document, FromYear, ToYear);
}
private void BindFinanceTable()
{
XmlDocument doc = new XmlDocument();
ArrayList PrIds = LoadProjectList();
SpreadSheetDocumentType type = SpreadSheetDocumentType.WeekYear;
switch (FinanceType)
{
case "1":
type = SpreadSheetDocumentType.WeekYear;
break;
case "2":
type = SpreadSheetDocumentType.MonthQuarterYear;
break;
case "3":
type = SpreadSheetDocumentType.QuarterYear;
break;
case "4":
type = SpreadSheetDocumentType.Year;
break;
case "5":
type = SpreadSheetDocumentType.Total;
break;
}
SpreadSheetView view = null;
if (!Reverse)
{
view = ProjectSpreadSheet.CompareProjects(PrIds, type, BasePlan1,
FromYear, ToYear);
}
else
{
view = ProjectSpreadSheet.CompareProjectsReverse(PrIds, type, BasePlan1,
FromYear, ToYear);
}
if (BasePlan2 == -2)
{
doc = ProjectSpreadSheet.CreateViewDocForAnalysis(view);
}
else
{
SpreadSheetView view2 = null;
if (!Reverse)
{
view2 = ProjectSpreadSheet.CompareProjects(PrIds, type,
BasePlan2, FromYear, ToYear);
}
else
{
view2 = ProjectSpreadSheet.CompareProjectsReverse(PrIds, type,
BasePlan2, FromYear, ToYear);
}
doc = ProjectSpreadSheet.CreateViewCompareDocForAnalysis(view, view2);
}
if (doc != null)
{
#region BindTableHeader
HtmlTableRow rheader1 = new HtmlTableRow();
HtmlTableRow rheader2 = new HtmlTableRow();
HtmlTableRow rheader3 = new HtmlTableRow();
if (view.Document.DocumentType != SpreadSheetDocumentType.Total && view.Document.DocumentType != SpreadSheetDocumentType.Year)
{
int YearCounter = ToYear - FromYear + 1;
int ColSpanRate = -1;
#region Year
if (view.Document.DocumentType == SpreadSheetDocumentType.QuarterYear)
{
ColSpanRate = 5;
HtmlTableCell c1 = new HtmlTableCell();
c1.RowSpan = 2;
c1.InnerText = LocRM.GetString("tProjects");
rheader1.Cells.Add(c1);
}
else
if (view.Document.DocumentType == SpreadSheetDocumentType.MonthQuarterYear)
{
ColSpanRate = 17;
HtmlTableCell c1 = new HtmlTableCell();
c1.RowSpan = 3;
c1.InnerText = LocRM.GetString("tProjects");
rheader1.Cells.Add(c1);
}
else
if (view.Document.DocumentType == SpreadSheetDocumentType.WeekYear)
{
ColSpanRate = 53;
HtmlTableCell c1 = new HtmlTableCell();
c1.RowSpan = 2;
c1.InnerText = LocRM.GetString("tProjects");
rheader1.Cells.Add(c1);
}
int RowSpanRate = (view.Columns.Length - YearCounter) / (YearCounter * 4);
int yearCount = 0;
int tmp = 0;
int tmp2 = 0;
for (int i = 0; i < view.Columns.Length - 1; i++)
{
if (view.Document.DocumentType == SpreadSheetDocumentType.MonthQuarterYear)
{
if (i % (ColSpanRate) == 0)
{
tmp = FromYear + yearCount;
tmp2 = ColSpanRate - 1;
HtmlTableCell c1 = new HtmlTableCell();
c1.Align = "center";
c1.ColSpan = tmp2;
c1.InnerHtml = tmp.ToString();
rheader1.Cells.Add(c1);
}
else
if (((i - yearCount + 1) % (ColSpanRate - 1) == 0) && (i - yearCount + 1 != 0))
{
HtmlTableCell c1 = new HtmlTableCell();
c1.RowSpan = 3;
c1.Align = "center";
c1.InnerHtml = LocRM.GetString("tYear");
rheader1.Cells.Add(c1);
}
}
else //Week/Year or Quarter/Year
{
if (i % (ColSpanRate) == 0)
{
tmp = FromYear + yearCount;
tmp2 = ColSpanRate - 1;
HtmlTableCell c1 = new HtmlTableCell();
c1.ColSpan = tmp2;
c1.Align = "center";
c1.InnerHtml = tmp.ToString();
rheader1.Cells.Add(c1);
}
else
if (((i - yearCount + 1) % (ColSpanRate - 1) == 0) && (i - yearCount + 1 != 0))
{
HtmlTableCell c1 = new HtmlTableCell();
c1.RowSpan = 2;
c1.InnerHtml = LocRM.GetString("tYear");
c1.Align = "center";
rheader1.Cells.Add(c1);
}
}
if (i % (ColSpanRate) == 0)
{
yearCount++;
}
}
if (view.Document.DocumentType == SpreadSheetDocumentType.MonthQuarterYear)
{
HtmlTableCell c1 = new HtmlTableCell();
c1.RowSpan = 3;
c1.InnerHtml = LocRM.GetString("tTotalSum");
c1.Align = "center";
rheader1.Cells.Add(c1);
}
else
{
HtmlTableCell c1 = new HtmlTableCell();
c1.RowSpan = 2;
c1.InnerHtml = LocRM.GetString("tTotalSum");
c1.Align = "center";
rheader1.Cells.Add(c1);
}
#endregion
#region Quartals
if (view.Document.DocumentType == SpreadSheetDocumentType.MonthQuarterYear)
{
for (int i = 0; i < view.Columns.Length - 1; i++)
{
int counter = i % ColSpanRate;
if (counter % (RowSpanRate) == 0 && counter != 16)
{
tmp = (counter / 4) + 1;
HtmlTableCell c1 = new HtmlTableCell();
c1.ColSpan = 3;
c1.Align = "center";
c1.InnerHtml = LocRM.GetString("tQuarterYear") + " " + tmp.ToString();
rheader2.Cells.Add(c1);
}
else
if ((counter + 1) % RowSpanRate == 0)
{
HtmlTableCell c1 = new HtmlTableCell();
c1.RowSpan = 2;
c1.InnerHtml = LocRM.GetString("tQuarterTotal");
c1.Align = "center";
rheader2.Cells.Add(c1);
}
else
if (counter == 16)
{
}
}
}
#endregion
#region BindServerData
int _counter = 0;
for (int i = 0; i < view.Columns.Length - 1; i++)
{
if (view.Document.DocumentType == SpreadSheetDocumentType.MonthQuarterYear)
{
if (i % ColSpanRate == 0 && i > 0)
{
_counter++;
}
if ((i + 1 - _counter) % RowSpanRate == 0 && i > 0)
{
}
else
if ((i + 1) % ColSpanRate == 0 && i > 0)
{
}
else
{
HtmlTableCell c1 = new HtmlTableCell();
c1.InnerHtml = ((Column)view.Columns[i]).Name;
c1.Align = "center";
rheader3.Cells.Add(c1);
}
}
else
{
if ((i + 1) % ColSpanRate == 0)
{
}
else
{
HtmlTableCell c1 = new HtmlTableCell();
c1.InnerHtml = ((Column)view.Columns[i]).Name;
c1.Align = "center";
rheader3.Cells.Add(c1);
}
}
}
#endregion
rheader1.Attributes.Add("class", "gridheader");
tGrid.Rows.Add(rheader1);
rheader2.Attributes.Add("class", "gridheader");
if (rheader2.Cells.Count > 0)
{
tGrid.Rows.Add(rheader2);
}
rheader3.Attributes.Add("class", "gridheader");
if (rheader3.Cells.Count > 0)
{
tGrid.Rows.Add(rheader3);
}
}
else
{
rheader1 = new HtmlTableRow();
HtmlTableCell c1 = new HtmlTableCell();
c1.InnerHtml = LocRM.GetString("tProjects");
rheader1.Cells.Add(c1);
for (int i = 0; i < view.Columns.Length; i++)
{
c1 = new HtmlTableCell();
c1.InnerHtml = ((Column)view.Columns[i]).Name;
c1.Align = "center";
rheader1.Cells.Add(c1);
}
rheader1.Attributes.Add("class", "gridheader");
tGrid.Rows.Add(rheader1);
}
#endregion
#region BindTableBody
XmlNode ParentNode = doc.DocumentElement;
foreach (XmlNode node in ParentNode.ChildNodes)
{
HtmlTableRow rbody = new HtmlTableRow();
ArrayList alcr = new ArrayList();
foreach (XmlNode node2 in node.ChildNodes)
{
HtmlTableRow crbody = new HtmlTableRow();
foreach (XmlNode cnode in node2.ChildNodes)
{
if (node2.Name == "row")
{
HtmlTableCell c1 = new HtmlTableCell();
c1.Align = "right";
c1.InnerHtml = cnode.InnerText;
crbody.Cells.Add(c1);
}
else
{
if (node2.Name == "cell")
{
HtmlTableCell c2 = new HtmlTableCell();
c2.InnerHtml = "<b>" + node2.InnerText + "</b>";
rbody.Cells.Add(c2);
}
}
}
alcr.Add(crbody);
crbody = new HtmlTableRow();
}
if (rbody.Cells.Count > 0)
{
tGrid.Rows.Add(rbody);
}
for (int i = 0; i < alcr.Count; i++)
{
if (((HtmlTableRow)alcr[i]).Cells.Count > 0)
{
tGrid.Rows.Add((HtmlTableRow)alcr[i]);
}
}
for (int i = 0; i < tGrid.Rows.Count; i++)
{
if (tGrid.Rows[i].Cells.Count > 0)
{
tGrid.Rows[i].Cells[0].Attributes.Add("class", "gridheader");
}
}
}
#endregion
}
}
public static string GetColumnByDate(SpreadSheetDocumentType DocumentType, DateTime Date)
{
switch(DocumentType)
{
case SpreadSheetDocumentType.WeekYear:
int YW = Iso8601WeekNumber.GetYearWeekNumber(Date);
return string.Format("{0:0000}-W{1:00}", YW / 100, YW%100);
case SpreadSheetDocumentType.MonthQuarterYear:
if(Date.Month>=1 && Date.Month<4)
return string.Format("{0:0000}-Q1-{1:00}", Date.Year, Date.Month);
else if(Date.Month>=4 && Date.Month<7)
return string.Format("{0:0000}-Q2-{1:00}", Date.Year, Date.Month);
else if(Date.Month>=7 && Date.Month<10)
return string.Format("{0:0000}-Q3-{1:00}", Date.Year, Date.Month);
else
return string.Format("{0:0000}-Q4-{1:00}", Date.Year, Date.Month);
case SpreadSheetDocumentType.QuarterYear:
if(Date.Month>=1 && Date.Month<4)
return string.Format("{0:0000}-Q1", Date.Year);
else if(Date.Month>=4 && Date.Month<7)
return string.Format("{0:0000}-Q2", Date.Year);
else if(Date.Month>=7 && Date.Month<10)
return string.Format("{0:0000}-Q3", Date.Year);
else
return string.Format("{0:0000}-Q4", Date.Year);
case SpreadSheetDocumentType.Year:
return string.Format("{0:0000}-T", Date.Year);
case SpreadSheetDocumentType.Total:
return "TT";
}
throw new NotSupportedException("Not supported document type.");
}
