public static void UpdateRecord(FileInloadStats filestats) { try { using (var destinationConnection = new SqlConnection(GlobalVariables.DbConnection)) { destinationConnection.Open(); var sql = "update FileInloadStats set TaxAssessorRecordCount={0}, TaxAssessorSussessful={1}, ForeclosureRecordCount={2}, ForeclosureSussessful={3}, RecorderRecordCount={4}, RecorderSussessful={5}, AVMRecordCount={6}, AVMSussessful={7}, AVMLoaded={8}, FileProcessed={9} where FileInloadId={10}"; sql = String.Format(sql, filestats.TaxAssessorRecordCount, filestats.TaxAssessorSussessful ? 1 : 0, filestats.ForeclosureRecordCount, filestats.ForeclosureSussessful ? 1 : 0, filestats.RecorderRecordCount, filestats.RecorderSussessful ? 1 : 0, filestats.AVMRecordCount, filestats.AVMSussessful ? 1 : 0, filestats.AVMLoaded ? 1 : 0, filestats.FileProcessed ? 1 : 0, filestats.FileInloadId); var command = new SqlCommand(sql, destinationConnection); command.ExecuteNonQuery(); destinationConnection.Close(); } } catch (Exception ex) { } }
public static List <FileInloadStats> FilesToProcess() { try { using (var destinationConnection = new SqlConnection(GlobalVariables.DbConnection)) { destinationConnection.Open(); var sql = "select FileInloadId, FileName, FileRemoteDeleted, TaxAssessorRecordCount, TaxAssessorSussessful, ForeclosureRecordCount, ForeclosureSussessful, RecorderRecordCount, RecorderSussessful, AVMRecordCount, AVMSussessful, AVMLoaded, FileProcessed, CreatedOn from FileInloadStats where FileProcessed = 0 OR TaxAssessorSussessful = 0 OR ForeclosureSussessful = 0 OR RecorderSussessful = 0 OR AVMLoaded = 0 Order By FileInloadId"; var command = new SqlCommand(sql, destinationConnection); var outputList = new List <FileInloadStats>(); using (var reader = command.ExecuteReader()) { while (reader.Read()) { var r = new FileInloadStats() { FileInloadId = reader.GetInt32(0), FileName = reader.GetString(1), FileRemoteDeleted = reader.GetBoolean(2), TaxAssessorRecordCount = reader.GetInt32(3), TaxAssessorSussessful = reader.GetBoolean(4), ForeclosureRecordCount = reader.GetInt32(5), ForeclosureSussessful = reader.GetBoolean(6), RecorderRecordCount = reader.GetInt32(7), RecorderSussessful = reader.GetBoolean(8), AVMRecordCount = reader.GetInt32(9), AVMSussessful = reader.GetBoolean(10), AVMLoaded = reader.GetBoolean(11), FileProcessed = reader.GetBoolean(12), CreatedOn = reader.GetDateTime(13) }; outputList.Add(r); } } destinationConnection.Close(); return(outputList); } } catch (Exception ex) { return(null); } }
public static bool InsertRecord(FileInloadStats filestats) { try { Console.WriteLine("Insert Routine started time - " + DateTime.Now); using (var destinationConnection = new SqlConnection(GlobalVariables.DbConnection)) { destinationConnection.Open(); var sql = "insert into FileInloadStats (FileName, FileRemoteDeleted, TaxAssessorRecordCount, TaxAssessorSussessful, ForeclosureRecordCount, ForeclosureSussessful, RecorderRecordCount, RecorderSussessful, AVMRecordCount, AVMSussessful, AVMLoaded, FileProcessed, CreatedOn) values ('{0}',{1},{2},{3},{4},{5},{6},{7},{8},{9},{10},'{11}','{12}')"; sql = String.Format(sql, filestats.FileName, filestats.FileRemoteDeleted ? 1 : 0, filestats.TaxAssessorRecordCount, filestats.TaxAssessorSussessful ? 1 : 0, filestats.ForeclosureRecordCount, filestats.ForeclosureSussessful ? 1 : 0, filestats.RecorderRecordCount, filestats.RecorderSussessful ? 1 : 0, filestats.AVMRecordCount, filestats.AVMSussessful ? 1 : 0, filestats.AVMLoaded ? 1 : 0, filestats.FileProcessed ? 1 : 0, DateTime.Now); var command = new SqlCommand(sql, destinationConnection); command.ExecuteNonQuery(); destinationConnection.Close(); } Console.WriteLine("Insert Routine end time - " + DateTime.Now); return(true); } catch (Exception ex) { return(false); } }
public static bool CheckForFiles() { string remoteDirectory = "/" + Properties.Settings.Default.FTPDownloadDir + "/"; string localDirectory = Properties.Settings.Default.RootFolderExt; bool bReturn = false; try { // Setup session options SessionOptions sessionOptions = new SessionOptions { Protocol = Protocol.Sftp, HostName = Properties.Settings.Default.FTPAddress, UserName = Properties.Settings.Default.FTPUserName, Password = Properties.Settings.Default.FTPPassword, SshHostKeyFingerprint = Properties.Settings.Default.SshHostKeyFingerprint }; using (WinSCP.Session session = new WinSCP.Session()) { // Connect session.Open(sessionOptions); //List files TransferOptions transferOptions = new TransferOptions(); transferOptions.TransferMode = TransferMode.Binary; RemoteDirectoryInfo files = session.ListDirectory(remoteDirectory); foreach (RemoteFileInfo file in files.Files) { if (file.Name.ToLower().Contains(".zip")) { GlobalServices.SetText("frmMain", "lFTPStatus", "Checking " + file.Name); if (!FileInloadStats.CheckRecordExists(file.Name)) { long total = file.Length > 0 ? file.Length : 1; int i = 0; GlobalServices.SetText("frmMain", "lFTPStatus", "Checking " + file.Name); //GlobalServices.SetProgressBar("frmMain", "pbProgress", 0, Convert.ToInt32(total), 0); GlobalServices.SetText("frmMain", "lFTPStatus", "Downloading File: " + file.Name); Console.WriteLine("Starting Download of file - " + file.Name + " Size - " + file.Length.ToString() + " Time - " + DateTime.Now.ToString()); // Download files TransferOperationResult transferOperationResult = session.GetFiles(session.EscapeFileMask(remoteDirectory + file.Name), localDirectory); // Check and throw if there are any errors with the transfer operation. transferOperationResult.Check(); if (transferOperationResult.IsSuccess) { var newFile = new FileInloadStats() { FileName = file.Name }; FileInloadStats.InsertRecord(newFile); Console.WriteLine("Completed Download of file - " + file.Name + " Size - " + file.Length.ToString() + " Time - " + DateTime.Now.ToString()); } } } } bReturn = true; } } catch (Exception e) { bReturn = false; return(bReturn); } return(bReturn); }
static void Main() { try { //if(args== null) { } txtBatchSize = Properties.Settings.Default.BatchSize; txtNotifyAfter = Properties.Settings.Default.NotifyAfter; txtRootFolder = Properties.Settings.Default.RootFolderExt; txtTaxAssessorFile = Properties.Settings.Default.TaxAssessorPrefix; txtForeclosureFile = Properties.Settings.Default.ForeclosurePrefix; txtRecorderFile = Properties.Settings.Default.RecorderPrefix; txtAVMFile = Properties.Settings.Default.AVMPrefix; txtTaxAssessorTable = Properties.Settings.Default.TaxAssessorTable; txtForeclosureTable = Properties.Settings.Default.ForeclosureTable; txtRecorderTable = Properties.Settings.Default.RecorderTable; txtAVMTable = Properties.Settings.Default.AVMTable; txtSMTPAPIKey = Properties.Settings.Default.SMTPAPIKey; cbStopAfterFile = false; var d = new DirectoryInfo(txtRootFolder); var fArray = d.GetFiles("*.zip"); foreach (FileInfo f in fArray) { lbFileList.Add(f.Name); } lbNotProcessed.Clear(); var filesToProcess = FileInloadStats.FilesToProcess(); if (filesToProcess != null) { foreach (var f in filesToProcess) { if (!f.FileProcessed) { lbNotProcessed.Add(f.FileName + " - ALL"); } if (!f.TaxAssessorSussessful) { lbNotProcessed.Add(f.FileName + " - TaxAssessor"); } if (!f.ForeclosureSussessful) { lbNotProcessed.Add(f.FileName + " - Foreclosure"); } if (!f.RecorderSussessful) { lbNotProcessed.Add(f.FileName + " - Recorder"); } if (!f.AVMLoaded) { lbNotProcessed.Add(f.FileName + " - AVM"); } } } var FilesToProcess = FTPData.CheckForFiles(); if (FilesToProcess) { ProcessFilesNow(); } }catch (Exception ex) { EventLog.WriteEntry("Application", ex.Message, System.Diagnostics.EventLogEntryType.Error); } }
private static void ProcessFilesNow() { //Process All Files that haven't already been processed var filesToProcess = FileInloadStats.FilesToProcess(); foreach (var file in filesToProcess) { DateTime dOVerallStart = DateTime.Now; try { //check that the file actually exists if (File.Exists(Settings.Default.RootFolderExt + "\\" + file.FileName)) { var sEmailBody = new StringBuilder(); //track time to process lock (GlobalVariables.locker) { GlobalVariables.sGlobalErrors = ""; GlobalVariables.iBatchSize = int.Parse(txtBatchSize); GlobalVariables.iNotifyAfter = int.Parse(txtNotifyAfter); GlobalVariables.iTaxAssessorRecords = 0; GlobalVariables.iForeclosureRecords = 0; GlobalVariables.iRecorderRecords = 0; GlobalVariables.iAvmRecords = 0; GlobalVariables.iNumberOfErrors = 0; GlobalVariables.sTaxAssessorErrors = ""; GlobalVariables.sForeclosureErrors = ""; GlobalVariables.sRecorderErrors = ""; GlobalVariables.sAvmErrors = ""; GlobalVariables.bTaxAssessorProcessedOk = false; GlobalVariables.bForeclosureProcessedOk = false; GlobalVariables.bRecorderProcessedOk = false; GlobalVariables.bAvmProcessedOk = false; } //first, unzip the file GlobalServices.SetText("frmMain", "lStatus", "Extracting: " + file.FileName); var s = Unzip.Extract(Settings.Default.RootFolderExt + "\\" + file.FileName); if (s.Length > 0) { GlobalServices.SetText("frmMain", "lStatus", "Extracting: " + " Truncating Import Tables"); LoadData.TruncateTaxAssessorImport(); LoadData.TruncateForeclosureImport(); LoadData.TruncateRecorderImport(); LoadData.DropTaxAssessorImportPrimaryKey(); GlobalServices.SetText("frmMain", "lStatus", "Processing Files"); if (!file.TaxAssessorSussessful) { GlobalVariables.bTaxAssessorProcessedOk = LoadTaxAssesssorFile(); //TEMPORARY DISABLE 0N 10/2 TO SEE PERFORMANCE OF MERGE STATEMENTIN BATCHES ProcessTaxAssessorImport(file.FileInloadId); WorkCompleted("TaxAssessor"); file.TaxAssessorRecordCount = GlobalVariables.iTaxAssessorRecords; file.TaxAssessorSussessful = GlobalVariables.bTaxAssessorProcessedOk; sEmailBody.AppendLine("Tax Assessor Record Count: " + file.TaxAssessorRecordCount.ToString("N0") + " (" + file.TaxAssessorSussessful.ToString() + ")"); if (!string.IsNullOrEmpty(GlobalVariables.sTaxAssessorErrors)) { sEmailBody.AppendLine("Tax Assessor ERROR: " + GlobalVariables.sTaxAssessorErrors); } } else { //clean up extracted files that are not processed lock (GlobalVariables.locker) { var d = new DirectoryInfo(txtRootFolder); var fArray = d.GetFiles(txtTaxAssessorFile + "*.txt"); foreach (FileInfo f in fArray) { if (File.Exists(txtRootFolder + "\\" + f.Name)) { File.Delete(txtRootFolder + "\\" + f.Name); } } } sEmailBody.AppendLine("Tax Assessor: NOT PROCESSED"); } if (!file.ForeclosureSussessful) { GlobalVariables.bForeclosureProcessedOk = LoadForeclosureFile(); ProcessForeclosureImport(file.FileInloadId); WorkCompleted("Foreclosure"); file.ForeclosureRecordCount = GlobalVariables.iForeclosureRecords; file.ForeclosureSussessful = GlobalVariables.bForeclosureProcessedOk; sEmailBody.AppendLine("Foreclosure Record Count: " + file.ForeclosureRecordCount.ToString("N0") + " (" + file.ForeclosureSussessful.ToString() + ")"); if (!string.IsNullOrEmpty(GlobalVariables.sForeclosureErrors)) { sEmailBody.AppendLine("Foreclosure ERROR: " + GlobalVariables.sForeclosureErrors); } } else { //clean up extracted files that are not processed lock (GlobalVariables.locker) { var d = new DirectoryInfo(txtRootFolder); var fArray = d.GetFiles(txtForeclosureFile + "*.txt"); foreach (FileInfo f in fArray) { if (File.Exists(txtRootFolder + "\\" + f.Name)) { File.Delete(txtRootFolder + "\\" + f.Name); } } } sEmailBody.AppendLine("Foreclosure: NOT PROCESSED"); } if (!file.RecorderSussessful) { GlobalVariables.bRecorderProcessedOk = LoadRecorderFile(); ProcessRecorderImport(file.FileInloadId); WorkCompleted("Recorder"); file.RecorderRecordCount = GlobalVariables.iRecorderRecords; file.RecorderSussessful = GlobalVariables.bRecorderProcessedOk; sEmailBody.AppendLine("Recorder Record Count: " + file.RecorderRecordCount.ToString("N0") + " (" + file.RecorderSussessful.ToString() + ")"); if (!string.IsNullOrEmpty(GlobalVariables.sRecorderErrors)) { sEmailBody.AppendLine("Recorder ERROR: " + GlobalVariables.sRecorderErrors); } } else { //clean up extracted files that are not processed lock (GlobalVariables.locker) { var d = new DirectoryInfo(txtRootFolder); var fArray = d.GetFiles(txtRecorderFile + "*.txt"); foreach (FileInfo f in fArray) { if (File.Exists(txtRootFolder + "\\" + f.Name)) { File.Delete(txtRootFolder + "\\" + f.Name); } } } sEmailBody.AppendLine("Recorder: NOT PROCESSED"); } if (!file.AVMLoaded) { GlobalVariables.bAvmProcessedOk = LoadAvmFile(); //Bo Making Change here to push processing to a database function. This system will only load the avm import table starting 01/17/2016 //if (GlobalVariables.iAvmRecords > 0) ProcessAVMImport(file.FileInloadId); //in this case, we only want to process the import table b/c the Stored Proc will truncate the main table. Only do it if we had incoming data WorkCompleted("AVM"); file.AVMRecordCount = GlobalVariables.iAvmRecords; file.AVMLoaded = GlobalVariables.bAvmProcessedOk; file.AVMSussessful = false; //forcing false here to database will pickup and process sEmailBody.AppendLine("AVM Record Count: " + file.AVMRecordCount.ToString("N0") + " (" + file.AVMLoaded.ToString() + ")"); if (!string.IsNullOrEmpty(GlobalVariables.sAvmErrors)) { sEmailBody.AppendLine("AVM ERROR: " + GlobalVariables.sAvmErrors); } } else { //clean up extracted files that are not processed lock (GlobalVariables.locker) { var d = new DirectoryInfo(txtRootFolder); var fArray = d.GetFiles(txtAVMFile + "*.txt"); foreach (FileInfo f in fArray) { if (File.Exists(txtRootFolder + "\\" + f.Name)) { File.Delete(txtRootFolder + "\\" + f.Name); } } } sEmailBody.AppendLine("AVM: NOT PROCESSED"); } } File.Move(Settings.Default.RootFolderExt + "\\" + file.FileName, Settings.Default.RootFolderExt + "\\Zips\\" + file.FileName); //Finished. //update record to processed with status file.FileProcessed = true; FileInloadStats.UpdateRecord(file); DateTime dOVerallEnd = DateTime.Now; TimeSpan span = dOVerallEnd - dOVerallStart; double totalMinutes = span.TotalMinutes; if (GlobalVariables.iNumberOfErrors > 0) { sEmailBody.AppendLine("ERRORS DID HAPPEN - CHECK ALL LOG FILES"); } sEmailBody.AppendLine("==================================="); sEmailBody.AppendLine(file.FileName + " processed in " + totalMinutes.ToString("N2") + " minutes."); string sBody = sEmailBody.ToString(); string sSubject = file.FileName + " processed in " + totalMinutes.ToString("N2") + " minutes."; if (GlobalVariables.iNumberOfErrors > 0) { sSubject = file.FileName + " HAS ERRORS - processed in " + totalMinutes.ToString("N2") + " minutes."; } lock (GlobalVariables.locker) { GlobalServices.AppendText("frmMain", "txtTaxAssessor", "# Errors: " + GlobalVariables.iNumberOfErrors.ToString()); GlobalServices.AppendText("frmMain", "txtTaxAssessor", "About to send email"); } GlobalServices.SendFileStats(sSubject, sBody); lock (GlobalVariables.locker) { GlobalServices.AppendText("frmMain", "txtTaxAssessor", "Email Sent"); } } else { var sEmailBody = new StringBuilder(); sEmailBody.AppendLine(file.FileName + " MISSING ZIP FILE"); string sBody = sEmailBody.ToString(); string sSubject = file.FileName + " MISSING ZIP FILE"; GlobalServices.SendFileStats(sSubject, sBody); } } catch (Exception ex) { DateTime dOVerallEnd = DateTime.Now; TimeSpan span = dOVerallEnd - dOVerallStart; double totalMinutes = span.TotalMinutes; //send email that file is done //Send Email about Processed File var sEmailBody = new StringBuilder(); sEmailBody.AppendLine(file.FileName + " processed in " + totalMinutes.ToString("N2") + " minutes."); sEmailBody.AppendLine("==================================="); sEmailBody.AppendLine("Tax Assessor Record Count: " + file.TaxAssessorRecordCount.ToString("N0") + " (" + file.TaxAssessorSussessful.ToString() + ")"); sEmailBody.AppendLine("Foreclosure Record Count: " + file.ForeclosureRecordCount.ToString("N0") + " (" + file.ForeclosureSussessful.ToString() + ")"); sEmailBody.AppendLine("Recorder Record Count: " + file.RecorderRecordCount.ToString("N0") + " (" + file.RecorderSussessful.ToString() + ")"); sEmailBody.AppendLine("AVM Record Count: " + file.AVMRecordCount.ToString("N0") + " (" + file.AVMLoaded.ToString() + ")"); sEmailBody.AppendLine("==================================="); sEmailBody.AppendLine(ex.Message); string sBody = sEmailBody.ToString(); string sSubject = file.FileName + " ERRORED in " + totalMinutes.ToString("N2") + " minutes."; GlobalServices.SendFileStats(sSubject, sBody); lock (GlobalVariables.locker) { GlobalServices.SetText("frmMain", "txtErrorFound", ex.Message); } } } //Check for Dups table to be cleaned up. This will keep notifying of dups in the dups table until extract and truncated. var iDupsRecordCount = FileInloadStats.GetTaxAssessorDupsCount(); if (iDupsRecordCount != 0) { GlobalServices.SendFileStats("TAXASSESSOR DUPS FOUND", "There are " + iDupsRecordCount.ToString() + " duplicate in the TaxAssessorDups table. Investigate Now."); } iDupsRecordCount = FileInloadStats.GetForeClosureDupsCount(); if (iDupsRecordCount != 0) { GlobalServices.SendFileStats("FORECLOSURE DUPS FOUND", "There are " + iDupsRecordCount.ToString() + " duplicate in the ForeclosureDups table. Investigate Now."); } var iNotProcessedCount = FileInloadStats.GetTaxAssessorNotProcessedCount(); if (iNotProcessedCount != 0) { GlobalServices.SendFileStats("ProcessingBatchesTA BATCHES NOT PROCESSED", "There are " + iNotProcessedCount.ToString() + " rows not procssed in the ProcessingBatchesTA table. Investigate Now."); } iNotProcessedCount = FileInloadStats.GetForeclosureNotProcessedCount(); if (iNotProcessedCount != 0) { GlobalServices.SendFileStats("ProcessingBatchesFC BATCHES NOT PROCESSED", "There are " + iNotProcessedCount.ToString() + " rows not procssed in the ProcessingBatchesFC table. Investigate Now."); } iNotProcessedCount = FileInloadStats.GetRecorderNotProcessedCount(); if (iNotProcessedCount != 0) { GlobalServices.SendFileStats("ProcessingBatchesRC BATCHES NOT PROCESSED", "There are " + iNotProcessedCount.ToString() + " rows not procssed in the ProcessingBatchesRC table. Investigate Now."); } }