private static Int32 UpdateFormCellDepencencies(List <FormCellDependency> dependencyList, string url, string Username, string Drowssap, out int numberUpdated, out int numberErrored)
{
int numberInserted = 0;
numberUpdated = 0;
numberErrored = 0;
foreach (FormCellDependency currentDependency in dependencyList)
{
string payload = string.Empty;
try
{
// get post data.
payload = JsonConvert.SerializeObject(currentDependency);
byte[] buf = Encoding.UTF8.GetBytes(payload);
// create the http web request
string query = currentDependency.FormCellDependencyId > 0 ? string.Format("/api/FormCellDependency/{0}", currentDependency.FormCellDependencyId.ToString()) : "/api/FormCellDependency";
var request = (HttpWebRequest)WebRequest.Create(url + query);
request.Headers.Add(HttpRequestHeader.Authorization, "Basic " + Convert.ToBase64String(System.Text.ASCIIEncoding.ASCII.GetBytes(Username + ":" + Drowssap)));
request.Method = currentDependency.FormCellDependencyId > 0 ? "PUT" : "POST";
request.ContentLength = buf.Length;
request.ContentType = "application/json; charset=utf-8";
request.GetRequestStream().Write(buf, 0, buf.Length);
// get response and deserialize it.
using (var response = (HttpWebResponse)request.GetResponse())
{
if (response != null)
{
var responseStream = response.GetResponseStream();
var streamReader = new System.IO.StreamReader(responseStream, Encoding.UTF8);
var responseString = streamReader.ReadToEnd();
FormCellDependency responseDependency = responseString.Length > 0 ? JsonConvert.DeserializeObject <FormCellDependency>(responseString) : null;
if (currentDependency.FormCellDependencyId > 0)
{
if (responseDependency.FormCellDependencyId > 0)
{
numberUpdated++;
}
}
else
{
if (responseDependency.FormCellDependencyId > 0)
{
numberInserted++;
}
}
}
}
}
catch (Exception ex)
{
Console.WriteLine("UpdateFormCellDepencencies: The following attempted insert/update failed: [{0}]. The unhandled exception that occurred: [{1}]", payload, ex.Message);
numberErrored++;
}
}
return(numberInserted);
}
/// <summary>
///
/// </summary>
/// <param name="args"></param>
/// <remarks>args[0] = File path and name</remarks>
/// <remarks>args[1] = url for Tax Form Catalog</remarks>
/// <remarks>args[2] = user id for Tax Form Catalog</remarks>
/// <remarks>args[3] = password for Tax Form Catalog</remarks>
static void Main(string[] args)
{
List <FilingQuestion> filingQuestions = new List <FilingQuestion>(); // = null
if (args[0].Equals("d"))
{
List <FilingQuestion> filingQuestions = GetFilingQuestions(args[1], args[2], args[3]);
Int64 deletedCount = 0;
foreach (FilingQuestion fq in filingQuestions)
{
FilingQuestionDelete deleteResult = DeleteFilingQuestion(fq.FilingQuestionId, args[1], args[2], args[3]);
if (deleteResult.deleted)
{
deletedCount++;
}
else
{
Console.WriteLine(string.Format("Unable to delete FilingQuestion [{0}].", fq.FilingQuestionId.ToString()));
}
}
Console.WriteLine(string.Format("The application deleted [{0}] FilingQuestion records.", deletedCount.ToString()));
}
else
{
//Populate object with source file
using (FileStream fs = new FileStream(args[0], FileMode.Open, FileAccess.Read, FileShare.ReadWrite))
{
using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Open(fs, false))
{
WorkbookPart workbookPart = spreadsheetDocument.WorkbookPart;
WorksheetPart worksheetPart = workbookPart.WorksheetParts.First();
SheetData sheetData = worksheetPart.Worksheet.Elements <SheetData>().First();
foreach (Row row in sheetData.Elements <Row>())
{
InputData dependency = new InputData();
InputData2 newDependency = new InputData2();
int rowcolumn = 1;
int tempMonths = -1;
List <String> firstRow = new List <String>();
foreach (Cell c in row.Elements <Cell>())
{
switch (rowcolumn)
{
/*
* case 1: dependency.TaxFormCode = ReadExcelCell(c, workbookPart); break;
* case 2: dependency.CurrentPeriod = ReadExcelCell(c, workbookPart); break;
* case 3: dependency.Months = int.TryParse(ReadExcelCell(c, workbookPart), out tempMonths) ? tempMonths : 0; break;
* case 4: dependency.OriginCellName = ReadExcelCell(c, workbookPart); break;
* case 5: dependency.DestinationCellName = ReadExcelCell(c, workbookPart); break;
* case 6: dependency.CityCountyCode = ReadExcelCell(c, workbookPart); break;
* case 7: dependency.TaxType = ReadExcelCell(c, workbookPart); break;
* case 8: dependency.Status = ReadExcelCell(c, workbookPart); break;
* case 9: dependency.Notes = ReadExcelCell(c, workbookPart); break;
*/
//New Format
case 1: newDependency.TaxFormCode = ReadExcelCell(c, workbookPart); break;
case 2: newDependency.SummaryLabel = ReadExcelCell(c, workbookPart); break;
case 3: newDependency.CurrentPeriod = ReadExcelCell(c, workbookPart); break;
case 4: newDependency.Months = int.TryParse(ReadExcelCell(c, workbookPart), out tempMonths) ? tempMonths : 0; break;
case 5: newDependency.Status = ReadExcelCell(c, workbookPart); break;
}
rowcolumn++;
}
/*
* if (dependency.TaxFormCode != "Form")
* {
* dependencies.Add(dependency);
* }
*/
if (newDependency.TaxFormCode != "Form")
{
newDependencies.Add(newDependency);
}
}
}
}
List <FormCellDependency> recordsToAdd = new List <FormCellDependency>();
int totalRecords = dependencies.Count;
string prevTaxFormCode = string.Empty;
long formMasterId = 0;
List <FormCellDependency> existingDependencies = GetFormCellDependencies(updateFormMasterId, args[1], args[2], args[3]);
List <Annotation> formAnnotations = new List <Annotation>();
List <SummaryLabel> summaryLabels = GetSummaryLabels(args[1], args[2], args[3]);
// Create List of FormCellDependency records to add
//foreach (InputData item in dependencies)
foreach (InputData2 item in newDependencies)
{
if (!item.TaxFormCode.Equals(prevTaxFormCode))
{
formMasterId = GetFormMasterId(item.TaxFormCode, args[1], args[2], args[3]);
prevTaxFormCode = item.TaxFormCode;
if (formMasterId > 0)
{
//formAnnotations = GetAnnotations(formMasterId, args[1], args[2], args[3]);
}
else
{
Console.WriteLine(string.Format("Unable to find a formmaster record for taxformcode [{0}].", item.TaxFormCode));
}
}
if (formMasterId > 0)
{
try
{
//var annotation = formAnnotations.Any() ? formAnnotations.Where(a => a.AnnotationName == item.OriginCellName).FirstOrDefault() : null;
//if (annotation != null)
//{
var summaryLabel = summaryLabels.Any() ? summaryLabels.Where(a => a.SummaryLabelCode == item.SummaryLabel).FirstOrDefault() : null;
//FormCellDependency dependency = existingDependencies.Any() ? existingDependencies.Where(d => d.AnnotationId == annotation.AnnotationId && d.FormMasterOptionId == formMasterId).FirstOrDefault() : null;
FormCellDependency dependency = existingDependencies.Any() ? existingDependencies.Where(d => d.SummaryLabelId == summaryLabel.SummaryLabelId && !d.AnnotationId.HasValue && d.FormMasterOptionId == formMasterId).FirstOrDefault() : null;
FormCellDependency dependencyToAdd = new FormCellDependency();
dependencyToAdd.FormCellDependencyId = dependency == null ? -1 : dependency.FormCellDependencyId;
dependencyToAdd.FormMasterId = updateFormMasterId;
dependencyToAdd.FormMasterOptionId = formMasterId;
dependencyToAdd.AnnotationId = null; //annotation.AnnotationId;
dependencyToAdd.SummaryLabelId = summaryLabel.SummaryLabelId; //null; // needs to be added if we plan to use this for other forms
dependencyToAdd.IncludeCurrentPeriod = item.CurrentPeriod.ToUpper() == "YES" ? true : false;
dependencyToAdd.MonthsAgo = item.Months;
dependencyToAdd.PriorYearEndFiscal = false; // needs to be added if we plan to use this for other forms
dependencyToAdd.CreatedDate = System.DateTime.UtcNow;
dependencyToAdd.CreatedUserId = 0;
dependencyToAdd.ModifiedDate = System.DateTime.UtcNow;
dependencyToAdd.ModifiedUserId = 0;
recordsToAdd.Add(dependencyToAdd);
//}
//else
//{
// Console.WriteLine(string.Format("AddFormCellDependencyFromExcel: Unable to find field [{0}] on tax form [{1}].", item.OriginCellName, item.TaxFormCode));
//}
}
catch (Exception ex)
{
//Console.WriteLine(string.Format("AddFormCellDependencyFromExcel: An error occurred [{0}] while processing TaxFormCode [{1}] and field [{2}].", ex.Message, item.TaxFormCode, item.OriginCellName));
Console.WriteLine(string.Format("AddFormCellDependencyFromExcel: An error occurred [{0}] while processing TaxFormCode [{1}] and summary label [{2}].", ex.Message, item.TaxFormCode, item.SummaryLabel));
}
}
}
// insert records to FormCellDependency
int recordsInserted = 0;
int recordsUpdated = 0;
int recordsErrored = 0;
if (recordsToAdd.Count > 0)
{
recordsInserted = UpdateFormCellDepencencies(recordsToAdd, args[1], args[2], args[3], out recordsUpdated, out recordsErrored);
}
Console.WriteLine(string.Format("AddFormCellDependencyFromExcel: {0} records inserted and {1} records updated out of {2} total records. There were {3} records that resulted in an error. ", recordsInserted.ToString(), recordsUpdated.ToString(), totalRecords.ToString(), recordsErrored.ToString()));
}
}
