/// <summary>
/// obtains the formmaster for a given TaxFormCode
/// </summary>
/// <param name="TaxFormCode"></param>
/// <param name="Url"></param>
/// <param name="Username"></param>
/// <param name="Drowssap"></param>
/// <returns></returns>
private static FormMaster GetFormMaster(string TaxFormCode, string Url, string Username, string Drowssap)
{
FormMaster formMaster = null;
string query = string.Empty;
try
{
query = string.Format("/api/FormMaster/%7Bid%7D?taxFormCode={0}", TaxFormCode);
// Create the http web request.
var request1 = (HttpWebRequest)WebRequest.Create(Url + query);
request1.Headers.Add(HttpRequestHeader.Authorization, "Basic " + Convert.ToBase64String(System.Text.ASCIIEncoding.ASCII.GetBytes(Username + ":" + Drowssap)));
// execute the request.
using (var response1 = (HttpWebResponse)request1.GetResponse())
{
if (response1 != null)
{
var responseStream1 = response1.GetResponseStream();
var streamReader1 = new System.IO.StreamReader(responseStream1, System.Text.Encoding.UTF8);
var responseString1 = streamReader1.ReadToEnd();
formMaster = responseString1.Length > 0 ? Newtonsoft.Json.JsonConvert.DeserializeObject <FormMaster>(responseString1) : null;
}
}
}
catch (Exception ex)
{
Console.WriteLine("8: GetFormMaster 1: Unable to find TaxFormCode [{0}] due to an unhandled exception:[{1}]", TaxFormCode, ex.Message);
}
try
{
if (formMaster == null)
{
query = string.Format("/api/FormMaster/%7Bid%7D?legacyReturnName={0}", TaxFormCode);
var request2 = (HttpWebRequest)WebRequest.Create(Url + query);
request2.Headers.Add(HttpRequestHeader.Authorization, "Basic " + Convert.ToBase64String(System.Text.ASCIIEncoding.ASCII.GetBytes(Username + ":" + Drowssap)));
using (var response2 = (HttpWebResponse)request2.GetResponse())
{
if (response2 != null)
{
var responseStream2 = response2.GetResponseStream();
var streamReader2 = new System.IO.StreamReader(responseStream2, System.Text.Encoding.UTF8);
var responseString2 = streamReader2.ReadToEnd();
formMaster = responseString2.Length > 0 ? Newtonsoft.Json.JsonConvert.DeserializeObject <FormMaster>(responseString2) : null;
}
}
}
}
catch (Exception ex)
{
Console.WriteLine("9: GetFormMaster 2: Unable to find LegacyReturnName [{0}] due to an unhandled exception:[{1}]", TaxFormCode, ex.Message);
}
return(formMaster);
}
private static Boolean UpdateFormMasterDORAddress(FormMaster formMaster, string url, string Username, string Drowssap)
{
string payload = string.Empty;
Boolean success = false;
try
{
// get post data.
payload = JsonConvert.SerializeObject(formMaster);
byte[] buf = Encoding.UTF8.GetBytes(payload);
// create the http web request
string query = "/api/FormMaster/" + formMaster.FormMasterId.ToString();
var request = (HttpWebRequest)WebRequest.Create(url + query);
request.Headers.Add(HttpRequestHeader.Authorization, "Basic " + Convert.ToBase64String(System.Text.ASCIIEncoding.ASCII.GetBytes(Username + ":" + Drowssap)));
request.Method = "PUT";
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();
FormMaster responseFormMaster = responseString.Length > 0 ? JsonConvert.DeserializeObject <FormMaster>(responseString) : null;
if (responseFormMaster != null)
{
success = true;
}
}
}
}
catch (Exception ex)
{
Console.WriteLine("10: UpdateFormMasterDORAddress: The following attempted update failed: [{0}]. The unhandled exception that occurred: [{1}]", payload, ex.Message);
}
return(success);
}
public static List <FormMaster> FillList(TaxFormCatalogContext context, string SQLCommand)
{
var list = new List <FormMaster>();
//Load the data into the SqlDataReader
using (var dataCommand = context.Database.GetDbConnection().CreateCommand())
{
dataCommand.CommandType = System.Data.CommandType.Text;
dataCommand.CommandText = SQLCommand;
if (dataCommand.Connection.State != System.Data.ConnectionState.Open)
{
dataCommand.Connection.Open();
}
var dataReader = dataCommand.ExecuteReader();
//Fill the list with the contents of the reader
while (dataReader.Read())
{
var obj = new FormMaster();
//Get the property information
var properties = typeof(FormMaster).GetProperties();
int i = 0;
foreach (var property in properties)
{
property.SetValue(obj, dataReader[i], null); // set the fields of T to the reader's value
i++;
}
list.Add(obj);
}
dataReader.Close();
}
return(list);
}
static void Main(string[] args)
{
Console.WriteLine("Beginning Processing");
List <InputData> dorAddresses = new List <InputData>();
FileStream ostrm;
StreamWriter writer;
TextWriter oldOut = Console.Out;
try
{
ostrm = new FileStream("./logout_" + DateTime.UtcNow.ToFileTimeUtc().ToString() + ".txt", FileMode.OpenOrCreate, FileAccess.Write);
writer = new StreamWriter(ostrm);
}
catch (Exception e)
{
Console.WriteLine("1: Cannot open logout_.txt for writing");
Console.WriteLine(e.Message);
return;
}
Console.SetOut(writer);
//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 dorAddress = new InputData();
int rowcolumn = 1;
List <String> firstRow = new List <String>();
foreach (Cell c in row.Elements <Cell>())
{
// skip blank columns
int cellColumnIndex = (int)GetColumnIndexFromName(GetColumnName(c.CellReference));
while (rowcolumn - 1 < cellColumnIndex)
{
rowcolumn++;
}
switch (rowcolumn)
{
case 1: dorAddress.TaxFormCode = ReadExcelCell(c, workbookPart); break;
case 2: dorAddress.LegacyReturnName = ReadExcelCell(c, workbookPart); break;
case 3: dorAddress.DORAddressMailto = ReadExcelCell(c, workbookPart); break;
case 4: dorAddress.DORAddress1 = ReadExcelCell(c, workbookPart); break;
case 5: dorAddress.DORAddress2 = ReadExcelCell(c, workbookPart); break;
case 6: dorAddress.DORAddressCity = ReadExcelCell(c, workbookPart); break;
case 7: dorAddress.DORAddressRegion = ReadExcelCell(c, workbookPart); break;
case 8: dorAddress.DORAddressPostalCode = ReadExcelCell(c, workbookPart); break;
case 9: dorAddress.Comments = ReadExcelCell(c, workbookPart); break;
}
rowcolumn++;
}
if (dorAddress.TaxFormCode != "TaxFormCode")
{
dorAddresses.Add(dorAddress);
}
}
}
}
int totalRecords = dorAddresses.Count;
int recordsUpdated = 0;
int recordsErrored = 0;
int recordsSkipped = 0;
// Create List of FormCellDependency records to add
foreach (InputData item in dorAddresses)
{
FormMaster currentFormMaster = GetFormMaster(item.TaxFormCode, args[1], args[2], args[3]);
if (currentFormMaster == null)
{
Console.WriteLine(string.Format("2: Unable to find a formmaster record for taxformcode [{0}].", item.TaxFormCode));
}
else
{
try
{
if (currentFormMaster.ModifiedDate < DateTime.Parse("2018-11-01"))
{
if (item.DORAddressMailto == item.DORAddress1)
{
currentFormMaster.DORAddressMailTo = item.DORAddressMailto;
currentFormMaster.DORAddress1 = item.DORAddress2;
currentFormMaster.DORAddress2 = null;
}
else if (item.DORAddress1 == item.DORAddress2)
{
currentFormMaster.DORAddressMailTo = item.DORAddressMailto;
currentFormMaster.DORAddress1 = item.DORAddress1;
currentFormMaster.DORAddress2 = null;
}
else
{
currentFormMaster.DORAddressMailTo = item.DORAddressMailto;
currentFormMaster.DORAddress1 = item.DORAddress1;
currentFormMaster.DORAddress2 = item.DORAddress2;
}
currentFormMaster.DORAddressCity = item.DORAddressCity;
currentFormMaster.DORAddressRegion = item.DORAddressRegion;
if (item.DORAddressPostalCode is null)
{
currentFormMaster.DORAddressPostalCode = item.DORAddressPostalCode;
}
else
{
if (item.DORAddressPostalCode.Length < 5)
{
currentFormMaster.DORAddressPostalCode = item.DORAddressPostalCode.PadLeft(5, '0');
}
else
{
currentFormMaster.DORAddressPostalCode = item.DORAddressPostalCode;
}
}
currentFormMaster.ModifiedDate = System.DateTime.UtcNow;
currentFormMaster.ModifiedUserId = 234;
bool success = UpdateFormMasterDORAddress(currentFormMaster, args[1], args[2], args[3]);
if (success)
{
Console.WriteLine(string.Format("3: UpdateFormMasterAddress: TaxFormCode [{0}] was successfully updated.", item.TaxFormCode));
recordsUpdated++;
}
else
{
Console.WriteLine(string.Format("4: UpdateFormMasterAddress: TaxFormCode [{0}] was not updated.", item.TaxFormCode));
recordsErrored++;
}
}
else
{
Console.WriteLine(string.Format("5: UpdateFormMasterAddress: TaxFormCode [{0}] was not updated because it was modified after 2018-11-01.", item.TaxFormCode));
recordsSkipped++;
}
}
catch (Exception ex)
{
Console.WriteLine(string.Format("6: UpdateFormMasterAddress: An error occurred [{0}] while processing TaxFormCode [{1}].", ex.Message, item.TaxFormCode));
recordsErrored++;
}
}
}
Console.WriteLine(string.Format("7: UpdateFormMasterDORAddresses: {0} records updated out of {1} total records. There were {2} records that resulted in an error. There were {3} records that were skipped because they had been modified after 2018-11-01.", recordsUpdated.ToString(), totalRecords.ToString(), recordsErrored.ToString(), recordsSkipped.ToString()));
Console.SetOut(oldOut);
writer.Close();
ostrm.Close();
Console.WriteLine("Complete");
Console.ReadKey();
}