protected static StateFunding ReadRow(Excel.Range row)
{
StateFunding result = new StateFunding();
try
{
var cells = (System.Array)row.Cells.Value;
result.recordNumber = cells.GetValue(1, 1) as string;
result.jurisdiction = cells.GetValue(1, 2) as string;
result.yearFunded = cells.GetValue(1, 3).ToString();
result.recipient = cells.GetValue(1, 4) as string;
result.amount = cells.GetValue(1, 5).ToString();
result.ministryAgency = cells.GetValue(1, 6) as string;
result.city = cells.GetValue(1, 7) as string;
result.jurisdictionFederal = cells.GetValue(1, 8) as string;
result.source = cells.GetValue(1, 9) as string;
result.program = cells.GetValue(1, 10) as string;
result.masterProgram = cells.GetValue(1, 11) as string;
result.project = cells.GetValue(1, 12) as string;
result.recipientOriginal = cells.GetValue(1, 13) as string;
result.movementAboriginal = cells.GetValue(1, 14) as string;
result.movementEnvironment = cells.GetValue(1, 15) as string;
result.movementRights = cells.GetValue(1, 16) as string;
result.movementWomen = cells.GetValue(1, 17) as string;
result.movementOther = cells.GetValue(1, 18) as string;
result.movementABgovt = cells.GetValue(1, 19) as string;
result.notes = cells.GetValue(1, 20) as string;
// calculate inflation
int year = int.Parse(result.yearFunded);
double average = 1.0 + decimal.ToDouble(AverageInflation[year]) / 100.0;
double inflatedAmount = double.Parse(result.amount) * Math.Pow(average, (double)(MAX_YEAR - year));
result.amountInflation = string.Format("{0:0.00}", inflatedAmount);
}
catch (Exception ex)
{
Console.WriteLine("Error reading data:" + ex.Message);
Console.WriteLine(ex.StackTrace);
return(null);
}
return(result);
}
public static XElement AddAggregations(string msGuid, Excel.Worksheet worksheet /*CsvReader csv*/, string currDir, XElement metadataSet)
{
XDocument doc = new XDocument(new XDeclaration("1.0", "utf-8", "yes"));
XElement ingestion = new XElement("ingestion");
ingestion.Add(new XAttribute("overwrite", "false"));
doc.Add(ingestion);
XAttribute xmlLang = new XAttribute(XNamespace.Xml + "lang", "en");
XElement aggregations = new XElement("aggregations");
int countAggregation = 1;
int fileCount = 1;
Excel.Range range = worksheet.UsedRange;
int rowsCount = range.Rows.Count;
string inflationField = "amount" + MAX_YEAR + "Inflation";
for (int i = 2; i <= rowsCount; ++i)
{
StateFunding sf = ReadRow(worksheet.get_Range("A" + i, "T" + i));
try
{
XElement item = new XElement("item");
aggregations.Add(item);
string now = DateTime.Now.ToShortDateString();
item.Add(new XAttribute("created", now));
item.Add(new XAttribute("updated", now));
item.Add(new XAttribute("model-type", "Catfish.Core.Models.CFItem, Catfish.Core, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null"));
item.Add(new XAttribute("IsRequired", "false"));
item.Add(new XAttribute("guid", Guid.NewGuid().ToString()));
item.Add(new XAttribute("entity-type", EntityTypeName));
XElement metadata = new XElement("metadata");
item.Add(metadata);
XElement ms = new XElement(metadataSet);
metadata.Add(ms);
ms.SetAttributeValue("created", now);
ms.SetAttributeValue("updated", now);
XElement fields = ms.Element("fields");
Action <XElement, string> setValue = (field, value) =>
{
XElement valueElement = field.Element("value");
if (valueElement == null)
{
valueElement = new XElement("value");
field.Add(valueElement);
}
XElement textValue = valueElement.Element("text");
if (textValue == null)
{
textValue = new XElement("text");
valueElement.Add(textValue);
textValue.Add(xmlLang);
}
textValue.Value = value ?? "";
};
foreach (XElement field in fields.Elements())
{
field.SetAttributeValue("updated", now);
string name = field.Element("name").Element("text").Value;
if (name == "jurisdiction")
{
setValue(field, sf.jurisdiction);
}
else if (name == "yearFunded")
{
setValue(field, sf.yearFunded);
}
else if (name == "recipient")
{
setValue(field, sf.recipient);
}
else if (name == "amount")
{
//TODO: add the inflation amount
setValue(field, sf.amount);
}
else if (name == "ministryAgency")
{
setValue(field, sf.ministryAgency);
}
else if (name == "city")
{
setValue(field, sf.city);
}
else if (name == "jurisdictionFederal")
{
setValue(field, sf.jurisdictionFederal);
}
else if (name == "source")
{
setValue(field, sf.source);
}
else if (name == "program")
{
setValue(field, sf.program);
}
else if (name == "masterProgram")
{
setValue(field, sf.masterProgram);
}
else if (name == "project")
{
setValue(field, sf.project);
}
else if (name == "recipientOriginal")
{
setValue(field, sf.recipientOriginal);
}
else if (name == "notes")
{
setValue(field, sf.notes);
}
else if (name == inflationField)
{
setValue(field, sf.amountInflation);
}
else if (name == "movement")
{
var options = field.Element("options").Elements();
foreach (var option in options)
{
string optionName = option.Element("text").Value;
if (optionName == "Aboriginal Peoples")
{
option.SetAttributeValue("selected", sf.movementAboriginal == "1");
}
else if (optionName == "Environment")
{
option.SetAttributeValue("selected", sf.movementEnvironment == "1");
}
else if (optionName == "Human Rights")
{
option.SetAttributeValue("selected", sf.movementRights == "1");
}
else if (optionName == "Women")
{
option.SetAttributeValue("selected", sf.movementWomen == "1");
}
else if (optionName == "Other")
{
option.SetAttributeValue("selected", sf.movementOther == "1");
}
else if (optionName == "Aboriginal Government")
{
option.SetAttributeValue("selected", sf.movementABgovt == "1");
}
}
}
}
XElement access = XElement.Parse(ACCESS);
item.Add(access);
}
catch (Exception ex)
{
if (sf != null)
{
Console.WriteLine(string.Format("An error occured while reading enty {0}: {1}", sf.recordNumber, ex.Message));
}
else
{
Console.WriteLine(string.Format("An error occured reading line {0} of the excel file:", i, ex.Message));
}
Console.WriteLine(ex.StackTrace);
}
if (countAggregation == TotAggregations) //save the file for every 10k items
{
ingestion.Add(aggregations);
doc.Save(currDir + "\\SFundingIngestion-Aggregation-" + fileCount + ".xml");
countAggregation = 1;
aggregations.RemoveAll();
ingestion.RemoveAll();
fileCount++;
}
else
{
countAggregation++;
}
}
//write the reminding of the file
ingestion.Add(aggregations);
doc.Save(currDir + "\\SFundingIngestion-Aggregation-" + fileCount + ".xml");
//countAggregation = 1;
// aggregations.RemoveAll();
// ingestion.RemoveAll();
return(aggregations);
}
