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.");
}
}