public static SpreadSheetDocument CreateDocument2()
{
SpreadSheetDocument retVal = new SpreadSheetDocument();
retVal.Template.Rows.Add(new Row("R1","Затрата 1"));
retVal.Template.Rows.Add(new Row("R2","Затрата 2"));
Block block = new Block("B1", "Блок Затрат");
block.ChildRows.Add(new Row("B1-1","Item 1"));
block.ChildRows.Add(new Row("B1-2","Item 2"));
block.ChildRows.Add(new Row("B1-3","Item 3"));
retVal.Template.Rows.Add(block);
retVal.Template.Rows.Add(new Row("R3","Затрата 3"));
// retVal.AddCell("2005-Q2-06", "R1", 100);
// retVal.AddCell("2007-Q2-06", "R1", -100);
retVal.AddCell("2006-Q1-01", "R1", 1);
retVal.AddCell("2006-Q1-02", "R1", 2);
retVal.AddCell("2006-Q1-03", "R1", 3);
retVal.AddCell("2006-Q2-04", "R1", 4);
retVal.AddCell("2006-Q2-05", "R1", 5);
retVal.AddCell("2006-Q2-06", "R1", 6);
retVal.AddCell("2006-Q3-07", "R1", 7);
retVal.AddCell("2006-Q3-08", "R1", 8);
retVal.AddCell("2006-Q3-09", "R1", 9);
retVal.AddCell("2006-Q4-10", "R1", 10);
retVal.AddCell("2006-Q4-11", "R1", 11);
retVal.AddCell("2006-Q4-12", "R1", 12);
retVal.AddCell("2006-Q1-01", "R3", 1.01);
retVal.AddCell("2006-Q1-02", "R3", 2.01);
retVal.AddCell("2006-Q1-03", "R3", 3.01);
retVal.AddCell("2006-Q4-10", "R3", 10.01);
retVal.AddCell("2006-Q4-11", "R3", 11.01);
retVal.AddCell("2006-Q4-12", "R3", 12.01);
return retVal;
}
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);
}
/// <summary>
/// Loads the rows from XML.
/// </summary>
/// <param name="rowsNode">The rows node.</param>
protected static void LoadRowsFromXmlNode(XmlNode rowsNode, ArrayList rows)
{
foreach(XmlNode rowNode in rowsNode.ChildNodes)
{
switch(rowNode.Name)
{
case "Row":
XmlElement rowElement = (XmlElement)rowNode;
string RowName = rowElement.GetAttribute("Name");
string RowId = rowElement.GetAttribute("Id");
string RowReadOnly = rowElement.GetAttribute("ReadOnly");
string RowExpression = rowElement.GetAttribute("Expression");
string RowFormat = rowElement.GetAttribute("Format");
string RowVisibility = rowElement.GetAttribute("Visibility");
RowReadOnly = RowReadOnly.Trim().ToUpper();
if(FindRowById(rows, RowId)==null)
{
Row newRow = new Row(RowId,
RowName,
(RowReadOnly=="1" || RowReadOnly=="TRUE"),
RowExpression,
RowFormat);
if(RowVisibility!=string.Empty)
newRow.Visibility = (RowVisibility)Enum.Parse(typeof(RowVisibility), RowVisibility, true);
rows.Add(newRow);
}
break;
case "Block":
XmlElement blockElement = (XmlElement)rowNode;
string BlockName = blockElement.GetAttribute("Name");
string BlockId = blockElement.GetAttribute("Id");
string BlockCanAddRow = blockElement.GetAttribute("CanAddRow");
string BlockReadOnly = blockElement.GetAttribute("ReadOnly");
string BlockExpression = blockElement.GetAttribute("Expression");
string BlockFormat = blockElement.GetAttribute("Format");
string BlockNewRowDefaultName = blockElement.GetAttribute("NewRowDefaultName");
BlockCanAddRow = BlockCanAddRow.Trim().ToUpper();
BlockReadOnly = BlockReadOnly.Trim().ToUpper();
Block currentBlock = (Block)FindRowById(rows, BlockId);
if(currentBlock==null)
{
currentBlock = new Block(BlockId,
BlockName,
(BlockCanAddRow=="1" || BlockCanAddRow=="TRUE"),
(BlockReadOnly=="1" || BlockReadOnly=="TRUE"),
BlockExpression,
BlockFormat);
currentBlock.NewRowDefaultName = BlockNewRowDefaultName;
rows.Add(currentBlock);
}
LoadRowsFromXmlNode(rowNode, currentBlock.ChildRows);
break;
}
}
}