public async Task <Transaction> ParseObject(object[] inputValues)
{
object[] values = cleanInput(inputValues);
Marine Certifier = new Marine {
EDIPI = int.Parse(values[5].ToString()), Rank = values[6].ToString(), LastName = values[7].ToString(), FirstName = values[8].ToString()
};
Marine Preparer = new Marine {
EDIPI = int.Parse(values[9].ToString()), Rank = values[10].ToString(), LastName = values[11].ToString(), FirstName = values[12].ToString()
};
Marine Member = new Marine {
EDIPI = int.Parse(values[13].ToString()), Rank = values[14].ToString(), LastName = values[15].ToString(), FirstName = values[16].ToString()
};
Transaction transaction = new Transaction
{
TTC = int.Parse(values[0].ToString()),
TTS = int.Parse(values[1].ToString()),
DiaryNumber = int.Parse(values[17].ToString()),
EnglishStatement = values[2].ToString(),
HistoryStatement = values[3].ToString(),
TransactionErrorCode = values[4].ToString(),
DiaryUploadLocation = values[18].ToString(),
Preparer = Preparer,
Certifier = Certifier,
Member = Member
};
transaction.ErrorDescription = await GetErrorDescription(transaction);
return(transaction);
}
public async Task <IList <MarineDiaries> > GetMemberDiaires(Marine marine)
{
string CommandText = $@"SELECT EDIPI, DiaryNumber FROM MemberDiaries WHERE EDIPI = {marine.EDIPI};";
UnitDiary diary = new UnitDiary {
Year = AppSettings.Year, Aruc = AppSettings.Aruc
};
return(await GetLinkedDiaries(CommandText, diary));
}
public async Task <IList <Documentation> > GetMarineDocuments(Marine marine)
{
string commandText = $@"SELECT D.ID, M.EDIPI, M.LastName, M.FirstName, M.MI, M.Rank, D.DocType, D.UploadedOn, D.UploadLocation, D.UploadedBy
FROM Documentation AS D
JOIN Marines AS M ON D.Marine = M.EDIPI
WHERE D.Marine = '{marine.EDIPI}' ORDER BY M.LastName ASC;";
return(await GetDocuments(commandText));
}
public async Task <IList <Transaction> > GetTransactions(Marine marine)
{
string commandText = $@"SELECT TTC, TTS, EnglishStatement, HistoryStatement, TransactionErrorCode, Certifier, CertifierRank, CertifierLastName, CertifierFirstName, Preparer, PreparerRank, PreparerLastName, PreparerFirstName, Member, MemberRank, MemberLastName, MemberFirstName, DiaryNumber, UploadLocation, ARUC, DiaryYear, Branch, DocumentRequired, DocumentMissing, DocumentAttached, UpdateDate, BatchNumber
FROM Transactions
WHERE Member = '{marine.EDIPI}' ORDER BY Member ASC;";
IList <MarineDiaries> marineDiaries = await new MarineDiaries().GetMemberDiaires(marine);
IList <Transaction> transactions = new List <Transaction>();
foreach (var diary in marineDiaries)
{
Transaction transaction = new Transaction {
ARUC = diary.Aruc, DiaryNumber = diary.DiaryNumber, DiaryYear = diary.Year
};
IList <Transaction> temp = await GetTransactionsAsync(commandText, transaction);
foreach (var item in temp)
{
transactions.Add(item);
}
}
return(transactions);
}
private async Task <IList <Documentation> > GetDocuments(string cmdText)
{
IList <Documentation> Documents = new List <Documentation>();
SQLiteConnection connection = await new Database().Connect();
try
{
using (SQLiteCommand cmd = new SQLiteCommand(connection))
{
cmd.CommandText = cmdText;
using (SQLiteDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
object[] values = cleanInput(reader, 10);
Marine marine = new Marine {
EDIPI = int.Parse(values[1].ToString()), LastName = values[2].ToString(), FirstName = values[3].ToString(), MI = values[4].ToString(), Rank = values[5].ToString()
};
Documents.Add(new Documentation
{
ID = reader.GetInt32(0),
DocType = reader.GetString(6),
UploadedOn = reader.GetDateTime(7),
UploadLocation = reader.GetString(8),
UploadedBy = reader.GetString(9),
Marine = marine
});
}
reader.Close();
}
}
}
catch (Exception ex)
{
MessageBox.Show("An Error ocurred reading the Database: " + ex.Message.ToString(), "Database Read Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
return(Documents);
}
public async Task <CSVCollection> read(string filePath)
{
CSVCollection collection = new CSVCollection();
Hashtable MemberTable = new Hashtable();
Hashtable CertifierTable = new Hashtable();
Hashtable PreparerTable = new Hashtable();
Hashtable DiaryTable = new Hashtable();
Hashtable MarineTable = new Hashtable();
Hashtable YearTable = new Hashtable();
Hashtable ArucTable = new Hashtable();
Hashtable MemberDiary = new Hashtable();
Hashtable PrepDiary = new Hashtable();
Hashtable CertDiary = new Hashtable();
IList <Marine> CurrentMarines = await new Marine().GetMarines();
IList <int> CurrentYears = await new Database().GetYears();
IList <int> CurrentArucs = await new Database().GetArucs();
foreach (var marine in CurrentMarines)
{
MarineTable.Add(marine.EDIPI, marine.LastName);
}
foreach (var aruc in CurrentArucs)
{
foreach (var year in CurrentYears)
{
AppSettings.Aruc = aruc;
AppSettings.Year = year;
UnitDiary newDiary = new UnitDiary {
Aruc = aruc, Year = year
};
IList <UnitDiary> unitDiaries = await newDiary.GetAll();
foreach (var diary in unitDiaries)
{
DiaryTable.Add($@"{diary.Number}{diary.Year}{diary.Aruc}", diary.Date);
}
}
}
CurrentMarines.Clear();
if (filePath != null)
{
try
{
int rowNumber = 1;
using (StreamReader reader = new StreamReader(filePath))
{
string CSVHeader = reader.ReadLine();
IList <Marine> Marines = new List <Marine>();
IList <Marine> Members = new List <Marine>();
IList <MarineDiaries> MemberDiaries = new List <MarineDiaries>();
IList <Marine> Preparers = new List <Marine>();
IList <MarineDiaries> PreparerDiaries = new List <MarineDiaries>();
IList <Marine> Certifiers = new List <Marine>();
IList <MarineDiaries> CertifierDiaries = new List <MarineDiaries>();
IList <Transaction> Transactions = new List <Transaction>();
IList <UnitDiary> Diaries = new List <UnitDiary>();
IList <int> Years = new List <int>();
IList <int> Arucs = new List <int>();
while (!reader.EndOfStream)
{
var line = reader.ReadLine();
string[] values = line.Split('\t');
int year = int.Parse(values[2]);
int aruc = int.Parse(values[27]);
Console.WriteLine($"Reading Row Number {rowNumber}");
rowNumber++;
UnitDiary diary = new UnitDiary();
diary.Aruc = aruc;
if (values[4] != string.Empty)
{
diary.Date = DateTime.Parse(values[4]);
}
if (values[1] != string.Empty)
{
diary.CycleNumber = int.Parse(values[1]);
}
if (values[2] != string.Empty)
{
diary.Year = int.Parse(values[2]);
}
if (values[3] != string.Empty)
{
diary.Number = int.Parse(values[3]);
}
if (values[0] != string.Empty)
{
diary.CycleDate = DateTime.Parse(values[0]);
}
if (values[15] != string.Empty)
{
diary.Branch = values[15];
}
Marine Preparer = new Marine();
if (values[5] != string.Empty)
{
Preparer.EDIPI = int.Parse(values[5]);
}
if (values[6] != string.Empty)
{
Preparer.Rank = values[6];
}
if (values[7] != string.Empty)
{
Preparer.LastName = values[7];
}
if (values[8] != string.Empty)
{
Preparer.FirstName = values[8];
}
if (values[9] != string.Empty)
{
Preparer.MI = values[9];
}
Marine Certifier = new Marine();
if (values[10] != string.Empty)
{
Certifier.EDIPI = int.Parse(values[10]);
}
if (values[11] != string.Empty)
{
Certifier.Rank = values[11];
}
if (values[12] != string.Empty)
{
Certifier.LastName = values[12];
}
if (values[13] != string.Empty)
{
Certifier.FirstName = values[13];
}
if (values[14] != string.Empty)
{
Certifier.MI = values[14];
}
Marine Member = new Marine();
if (values[16] != string.Empty)
{
Member.EDIPI = int.Parse(values[16]);
}
if (values[17] != string.Empty)
{
Member.Rank = values[17];
}
if (values[18] != string.Empty)
{
Member.LastName = values[18];
}
if (values[19] != string.Empty)
{
Member.FirstName = values[19];
}
if (values[20] != string.Empty)
{
Member.MI = values[20];
}
if (values[21] != string.Empty)
{
Member.PRUC = int.Parse(values[21]);
}
Transaction transaction = new Transaction();
if (values[22] != string.Empty)
{
try
{
transaction.TTC = int.Parse(values[22]);
}
catch
{
transaction.TTC = 000;
}
}
if (values[23] != string.Empty)
{
transaction.TTS = int.Parse(values[23]);
}
if (values[3] != string.Empty)
{
transaction.DiaryNumber = int.Parse(values[3]);
}
if (values[24] != string.Empty)
{
transaction.TransactionErrorCode = values[24];
}
if (values[25] != string.Empty)
{
transaction.EnglishStatement = values[25].TrimEnd(' ');
}
if (values[26] != string.Empty)
{
transaction.HistoryStatement = values[26].TrimEnd(' ');
}
if (values[28] != string.Empty)
{
transaction.DocumentRequired = values[28].TrimEnd(' ');
}
transaction.ARUC = aruc;
transaction.DiaryYear = year;
transaction.Member = Member;
transaction.Preparer = Preparer;
transaction.Certifier = Certifier;
Transactions.Add(transaction);
diary.Certifier = $@"{Certifier.Rank} {Certifier.LastName}";
if (!YearTable.ContainsKey(year))
{
Years.Add(year);
YearTable.Add(year, year);
}
if (!ArucTable.ContainsKey(aruc))
{
Arucs.Add(aruc);
ArucTable.Add(aruc, aruc);
}
if (!MemberTable.ContainsKey(Member.EDIPI))
{
Members.Add(Member);
MemberTable.Add(Member.EDIPI, Member.LastName);
if (!MarineTable.ContainsKey(Member.EDIPI))
{
Marines.Add(Member);
MarineTable.Add(Member.EDIPI, Member.LastName);
}
}
if (!MemberDiary.ContainsKey($"{Member.EDIPI}{diary.Number}{aruc}"))
{
MemberDiaries.Add(new MarineDiaries {
EDIPI = Member.EDIPI, DiaryNumber = diary.Number, Aruc = aruc, Year = year
});
MemberDiary.Add($"{Member.EDIPI}{diary.Number}{aruc}", diary.Number);
}
if (!CertifierTable.ContainsKey(Certifier.EDIPI))
{
Certifiers.Add(Certifier);
CertifierTable.Add(Certifier.EDIPI, Certifier.LastName);
if (!MarineTable.ContainsKey(Certifier.EDIPI))
{
Marines.Add(Certifier);
MarineTable.Add(Certifier.EDIPI, Certifier.LastName);
}
}
if (!CertDiary.ContainsKey($"{Certifier.EDIPI}{diary.Number}{aruc}"))
{
CertifierDiaries.Add(new MarineDiaries {
EDIPI = Certifier.EDIPI, DiaryNumber = diary.Number, Aruc = aruc, Year = year
});
CertDiary.Add($"{Certifier.EDIPI}{diary.Number}{aruc}", diary.Number);
}
if (!PreparerTable.ContainsKey(Preparer.EDIPI))
{
Preparers.Add(Preparer);
PreparerTable.Add(Preparer.EDIPI, Preparer.LastName);
if (!MarineTable.ContainsKey(Preparer.EDIPI))
{
Marines.Add(Preparer);
MarineTable.Add(Preparer.EDIPI, Preparer.LastName);
}
}
if (!PrepDiary.ContainsKey($"{Preparer.EDIPI}{diary.Number}{aruc}"))
{
PreparerDiaries.Add(new MarineDiaries {
EDIPI = Preparer.EDIPI, DiaryNumber = diary.Number, Aruc = aruc, Year = year
});
PrepDiary.Add($"{Preparer.EDIPI}{diary.Number}{aruc}", diary.Number);
}
if (!DiaryTable.ContainsKey($@"{diary.Number}{diary.Year}{diary.Aruc}"))
{
Diaries.Add(diary);
DiaryTable.Add($@"{diary.Number}{diary.Year}{diary.Aruc}", diary.Date);
}
}
collection.Years = Years;
collection.Arucs = Arucs;
collection.Certifiers = Certifiers;
collection.CertifierDiaries = CertifierDiaries;
collection.Preparers = Preparers;
collection.PreparerDiaries = PreparerDiaries;
collection.Members = Members;
collection.MemberDiaries = MemberDiaries;
collection.Diaries = Diaries;
collection.Marines = Marines;
collection.Transactions = Transactions;
}
}
catch (Exception ex)
{
MessageBox.Show("An error occured while reading the CSV: Error Text: " + ex.Message.ToString(), "Read Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
return(collection);
}
else
{
return(null);
}
}
private async Task <IList <ESRTransaction> > GetTransactionsAsync(string cmdText)
{
IList <ESRTransaction> transactions = new List <ESRTransaction>();
SQLiteConnection connection = await new Database(new ESRTransaction()).Connect();
try
{
using (SQLiteCommand cmd = new SQLiteCommand(connection))
{
cmd.CommandText = cmdText;
using (SQLiteDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
object[] values = cleanInput(reader, 36);
bool missing = false;
bool attached = false;
int batchNumber = 0;
DateTime now = DateTime.Now;
bool.TryParse(values[23].ToString(), out missing);
bool.TryParse(values[24].ToString(), out attached);
DateTime.TryParse(values[25].ToString(), out now);
int.TryParse(values[26].ToString(), out batchNumber);
Marine Certifier = new Marine {
EDIPI = int.Parse(values[5].ToString()), Rank = values[6].ToString(), LastName = values[7].ToString(), FirstName = values[8].ToString()
};
Marine Preparer = new Marine {
EDIPI = int.Parse(values[9].ToString()), Rank = values[10].ToString(), LastName = values[11].ToString(), FirstName = values[12].ToString()
};
Marine Member = new Marine {
EDIPI = int.Parse(values[13].ToString()), Rank = values[14].ToString(), LastName = values[15].ToString(), FirstName = values[16].ToString()
};
transactions.Add(new ESRTransaction
{
Transaction = new Transaction
{
TTC = int.Parse(values[0].ToString()),
TTS = int.Parse(values[1].ToString()),
DiaryNumber = int.Parse(values[17].ToString()),
EnglishStatement = values[2].ToString(),
HistoryStatement = values[3].ToString(),
TransactionErrorCode = values[4].ToString(),
UploadLocation = values[18].ToString(),
Preparer = Preparer,
Certifier = Certifier,
Member = Member,
ARUC = int.Parse(values[19].ToString()),
DiaryYear = int.Parse(values[20].ToString()),
Branch = values[21].ToString(),
DocumentRequired = values[22].ToString(),
DiaryUploadLocation = values[35].ToString(),
DocumentMissing = missing,
DocumentAttached = attached,
UpdateDate = now,
BatchNumber = values[26].ToString()
},
BatchID = int.Parse(values[27].ToString()),
IsRejected = bool.Parse(values[28].ToString()),
ConfirmDate = DateTime.Parse(values[29].ToString()),
RejectDate = DateTime.Parse(values[30].ToString()),
NeedsConfirmed = bool.Parse(values[31].ToString()),
Complete = bool.Parse(values[32].ToString()),
ID = int.Parse(values[33].ToString()),
ApproverDate = DateTime.Parse(values[34].ToString())
});
}
reader.Close();
}
}
}
catch (Exception ex)
{
MessageBox.Show("An Error ocurred reading the ESR Transaction Database: " + ex.Message.ToString(), "Database Read Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
return(transactions);
}