public void ImportDataFile(ClassDataFile file)
{
try
{
string newFileLoc = this.UpdateTemplate(file);
Application app = new Application();
Package package = null;
//Load the SSIS Package which will be executed
package = app.LoadPackage(newFileLoc, null);
//Pass the varibles into SSIS Package
//Execute the SSIS Package and store the Execution Result
Microsoft.SqlServer.Dts.Runtime.DTSExecResult results = package.Execute();
//Check the results for Failure and Success
if (results == Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure)
{
string err = "";
foreach (Microsoft.SqlServer.Dts.Runtime.DtsError local_DtsError in package.Errors)
{
string error = local_DtsError.Description.ToString();
err = err + error;
}
}
}
catch (Exception ex)
{
throw ex;
}
}
public static string VerifyFileIntact(ClassDataFile datFile, ClassAuditEntriesDataTable auditEntries = null)
{
string message = "Not successful. No Audit entry found for this file";
//• Audit Type depends on if the auditEntries table has been supplied or not
if (auditEntries == null)
{
if (datFile.AmountOfLines == datFile.IntendedAmountOfLines)
message = "Self Check Successful";
else
message = "Self Check Not Successful. File is not complete:CDR MISMATCH";
}
else
{
foreach (DataRow dr in auditEntries.entriesTable.Rows)
{
if (dr["FileName"].ToString() == datFile.FileName.Substring(0, 8))
{
if (Convert.ToInt16(dr["CDR_Count"]) == datFile.AmountOfLines)
{
message = "AuditFile Check Successful";
break;
}
else
{
message = "AuditFile Check Not Successful. File is not complete:CDR MISMATCH";
break;
}
}
}
}
return message;
}
//Methods
public void AddNewRow(ClassDataFile dataFile, ref ClassImportInfoDataTable infoTable)
{
DataRow newRow = infoTable.infoTable.NewRow();
newRow["File_Name"] = dataFile.FileName;
newRow["Self_Check_Result"] = "";
newRow["AuditFile_Check_Result"] = "Not Checked Yet";
infoTable.infoTable.Rows.Add(newRow);
infoTable.infoTable.AcceptChanges();
}
public static List<ClassDataFile> CompileFileList(string pickUpLocation, ref ClassImportInfoDataTable infoTable, bool useAuditFile)
{
List<ClassDataFile> fileList = new List<ClassDataFile>();
String[] filePaths = Directory.GetFiles(pickUpLocation);
ClassDataFile[] datFiles = new ClassDataFile[filePaths.Count()];
int count = 0;
if (useAuditFile)
{
string auditFileLocation = Properties.Settings.Default.AuditFileLocation;
ClassAuditFile auditFile = new ClassAuditFile(auditFileLocation);
DataTables.ClassAuditEntriesDataTable auditEntriesTable = new DataTables.ClassAuditEntriesDataTable();
auditEntriesTable = DataTables.ClassAuditEntriesDataTable.FillEntriesTable(auditFile);
if (auditEntriesTable.entriesTable.Rows.Count >= 1)
{
foreach (String fileLoc in filePaths)
{
datFiles[count] = new ClassDataFile(filePaths[count]);
infoTable.AddNewRow(datFiles[count], ref infoTable);
string message = string.Empty;
message = ClassAuditing.SelfCheck(datFiles[count]);
infoTable.UpdateRow(datFiles[count], "Self_Check_Result", message, ref infoTable);
message = ClassAuditing.AuditFileCheck(datFiles[count], auditEntriesTable);
infoTable.UpdateRow(datFiles[count], "AuditFile_Check_Result", message, ref infoTable);
//• ONLY ADD FILE TO LIST IF AUDIT PASSED
if ((datFiles[count].PassedSelfCheck == true) && (datFiles[count].PassedAuditFileCheck == true))
fileList.Add(datFiles[count]);
count++;
}
}
}
else
{
foreach (String fileLoc in filePaths)
{
datFiles[count] = new ClassDataFile(filePaths[count]);
infoTable.AddNewRow(datFiles[count], ref infoTable);
string message = string.Empty;
message = ClassAuditing.SelfCheck(datFiles[count]);
infoTable.UpdateRow(datFiles[count], "Self_Check_Result", message, ref infoTable);
//• ONLY ADD FILE TO LIST IF AUDIT PASSED
if (datFiles[count].PassedSelfCheck == true)
fileList.Add(datFiles[count]);
count++;
}
}
return fileList;
}
public static string SelfCheck(ClassDataFile datFile)
{
string message;
if (datFile.AmountOfLines == datFile.IntendedAmountOfLines)
{
message = "Self Check Successful";
datFile.PassedSelfCheck = true;
}
else
{
message = "Self Check Not Successful. File is not complete:CDR MISMATCH";
datFile.PassedSelfCheck = false;
}
return message;
}
public static void UpdateInCompleteAudits(DataTable table, ClassAuditEntriesDataTable entriesTable, ref ClassImportInfoDataTable infoTable, string connectionString)
{
string message;
foreach (DataRow dataRow in table.Rows)
{
ClassDataFile datFile = new ClassDataFile(dataRow);
infoTable.AddNewRow(datFile, ref infoTable);
message = ClassGatherInfo.VerifyFileIntact(datFile, entriesTable);
message = AuditFileCheck(datFile, entriesTable);
infoTable.UpdateRow(datFile, "AuditFile_Check_Result", message, ref infoTable);
//• ONLY UPDATE THE ENTRY IF AUDIT PASSED
if (datFile.PassedAuditFileCheck)
ClassSQLAccess.UpdateIncompleteAudit(datFile, connectionString);
}
}
public static void MoveFiles(ClassDataFile[] files, string outputLoaction)
{
foreach (ClassDataFile file in files)
{
string yearNumber = file.Year;
if (Directory.Exists(outputLoaction))
{
if (Directory.Exists(outputLoaction + "\\" + file.MonthName + " " + yearNumber + "\\"))
{
if (Directory.Exists(outputLoaction + "\\" + file.MonthName + " " + yearNumber + "\\" + file.Day))
{
File.Copy(file.Location, outputLoaction + "\\" + file.MonthName + " " + yearNumber + "\\" + file.Day + "\\" + file.FileName);
file.NewLocation = outputLoaction + "\\" + file.MonthName + " " + yearNumber + "\\" + file.Day + "\\" + file.FileName;
}
else
{
Directory.CreateDirectory(outputLoaction + "\\" + file.MonthName + " " + yearNumber + "\\" + file.Day + "\\");
File.Copy(file.Location, outputLoaction + " \\" + file.MonthName + " " + yearNumber + "\\" + file.Day + "\\" + file.FileName);
file.NewLocation = outputLoaction + "\\" + file.MonthName + " " + yearNumber + "\\" + file.Day + "\\" + file.FileName;
}
}
else
{
Directory.CreateDirectory(outputLoaction + "\\" + file.MonthName + " " + yearNumber + "\\" + file.Day + "\\");
File.Copy(file.Location, outputLoaction + "\\" + file.MonthName + " " + yearNumber + "\\" + file.Day + "\\" + file.FileName);
file.NewLocation = outputLoaction + "\\" + file.MonthName + " " + yearNumber + "\\" + file.Day + "\\" + file.FileName;
}
}
else
{
Directory.CreateDirectory(outputLoaction + "\\" + file.MonthName + " " + yearNumber + "\\" + file.Day + "\\");
File.Copy(file.Location, outputLoaction + "\\" + file.MonthName + " " + yearNumber + "\\" + file.Day + "\\" + file.FileName);
file.NewLocation = outputLoaction + "\\" + file.MonthName + " " + yearNumber + "\\" + file.Day + "\\" + file.FileName;
}
File.Delete(file.Location);
}
}
public static string AuditFileCheck(ClassDataFile datFile, ClassAuditEntriesDataTable auditEntries)
{
string message = "Not successful. No Audit entry found for this file";
foreach (DataRow dr in auditEntries.entriesTable.Rows)
{
if (dr["FileName"].ToString() == datFile.FileName.Substring(0, 8))
{
if (Convert.ToInt16(dr["CDR_Count"]) == datFile.AmountOfLines)
{
message = "AuditFile Check Successful";
datFile.PassedAuditFileCheck = true;
datFile.AuditFileEntryAmount = Convert.ToInt16(dr["CDR_Count"]);
break;
}
else
{
message = "AuditFile Check Not Successful. File is not complete:CDR MISMATCH";
datFile.PassedAuditFileCheck = false;
break;
}
}
}
return message;
}
public static bool UpdateIncompleteAudit(ClassDataFile datFile, string connectionString)
{
SqlConnection sqlConnection = new SqlConnection();
SqlCommand sqlCommand = new SqlCommand();
sqlConnection.ConnectionString = connectionString;
sqlCommand.Connection = sqlConnection;
sqlCommand.CommandText = BuildUpdateInCompleteAuditsQuery(datFile);
sqlConnection.Open();
int count = sqlCommand.ExecuteNonQuery();
sqlConnection.Close();
bool _result = (count >= 1) ? true : false;
return _result;
}
private string UpdateTemplate(ClassDataFile file)
{
string line;
using (StreamReader sr = new StreamReader(this.templateLocation))
{
line = sr.ReadToEnd();
sr.Close();
}
line = this.TagReplace(file, line);
string path;
path = file.NewLocation.Replace(file.FileName, this.templateName);
System.IO.File.WriteAllText(path, line);
return path;
}
public string TagReplace(ClassDataFile file, string line)
{
line = line.Replace("{ConnectionString}", this.connectionString);
line = line.Replace("{FileLocation}", file.NewLocation);
line = line.Replace("{FileName}", file.FileType + file.Day + file.Month + file.FileNumber);
return line;
}
public static ClassDataFile ReturnDataFileByNameAndActualAmount(string fileName, int actualAmount, List<ClassDataFile> dataFileList)
{
ClassDataFile selectedDataFile = new ClassDataFile();
foreach(ClassDataFile currentFile in dataFileList)
{
if ((currentFile.FileName == fileName) && (currentFile.AmountOfLines == actualAmount))
selectedDataFile = currentFile;
}
return selectedDataFile;
}
public static ClassDataFile ReturnDataFileByID(int ID, List<ClassDataFile> dataFileList)
{
ClassDataFile selectedDataFile = new ClassDataFile();
foreach(ClassDataFile currentFile in dataFileList)
{
if (currentFile.MTN_APN_Data_File_ID == ID)
selectedDataFile = currentFile;
}
return selectedDataFile;
}
private static string BuildUpdateInCompleteAuditsQuery(ClassDataFile datFile)
{
StringBuilder sqlQuery = new StringBuilder();
sqlQuery.AppendLine("USE [APN_DATA]");
sqlQuery.AppendLine("");
sqlQuery.AppendLine("UPDATE [MTN_APN_Data_File]");
sqlQuery.AppendLine("SET Audit_File_CDR_Amount = " + datFile.AuditFileEntryAmount.ToString());
sqlQuery.AppendLine("WHERE");
sqlQuery.AppendLine(" ID =" + datFile.MTN_APN_Data_File_ID.ToString());
return sqlQuery.ToString();
}
public void UpdateRow(ClassDataFile dataFile, string resultType, string resultMessage, ref ClassImportInfoDataTable infoTable)
{
DataRow row = infoTable.infoTable.Select("File_Name = '" + dataFile.FileName + "'").FirstOrDefault();
row[resultType] = resultMessage.ToString();
infoTable.infoTable.AcceptChanges();
}
public static DataTable InsertNewFileId(ClassDataFile[] dataFiles, string connectionString)
{
DataTable pTable = new DataTable();
int fileCount = 1;
DataTable resultTable = new DataTable();
SqlConnection sqlConnection = new SqlConnection();
SqlCommand sqlCommand = new SqlCommand();
sqlConnection.ConnectionString = connectionString;
StringBuilder sqlQuery = new StringBuilder();
sqlQuery.AppendLine("USE [APN_DATA]");
sqlQuery.AppendLine("");
sqlQuery.AppendLine("INSERT INTO ");
sqlQuery.AppendLine(" MTN_APN_Data_File(File_Name,Date_Uploaded,Actual_CDR_Amount, Self_Check_CDR_Amount)");
sqlQuery.AppendLine(" VALUES");
foreach (ClassDataFile newDataFile in dataFiles)
{
sqlQuery.AppendLine("('" + newDataFile.FileName + "',GETDATE()," + newDataFile.AmountOfLines.ToString() + "," + newDataFile.IntendedAmountOfLines.ToString() + "),");
if (fileCount == (dataFiles.Count()))
sqlQuery = sqlQuery.Remove(sqlQuery.Length - 3, 1);
else
fileCount++;
}
sqlQuery.AppendLine("");
sqlQuery.AppendLine("SELECT TOP " + fileCount + "");
sqlQuery.AppendLine(" [ID],");
sqlQuery.AppendLine(" [FILE_NAME],");
sqlQuery.AppendLine(" [Actual_CDR_Amount],");
sqlQuery.AppendLine(" [Self_Check_CDR_Amount]");
sqlQuery.AppendLine("FROM");
sqlQuery.AppendLine(" MTN_APN_Data_File");
sqlQuery.AppendLine("ORDER BY");
sqlQuery.AppendLine(" ID DESC");
sqlCommand.CommandText = sqlQuery.ToString();
sqlCommand.Connection = sqlConnection;
using (SqlDataAdapter sqlAdaptor = new SqlDataAdapter(sqlCommand.CommandText, sqlConnection))
{
sqlAdaptor.Fill(pTable);
}
sqlConnection.Close();
return pTable;
}
public static void CreateNewMonthDir(ClassDataFile dataFile, string newLoc)
{
Directory.CreateDirectory(newLoc + "\\" + dataFile.Month);
}
