public bool Add_LoanDeductionRecon(EOD eod, short bankID)
{
try
{
OpenConnection();
cmd = new SqlCommand(string.Format("INSERT INTO tbl_LoanDeductionRecon (BankID, PagIBIGID, ActualTxn_Date, Processed_Date, Processed_Time, AcctNo, BranchCode, Transaction_Amount, PaymentRefNo, Remarks, ReferenceFile, DatePosted) VALUES (@BankID, @PagIBIGID, @ActualTxn_Date, @Processed_Date, @Processed_Time, @AcctNo, @BranchCode, @Transaction_Amount, @PaymentRefNo, @Remarks, @ReferenceFile, GETDATE())"), con);
cmd.Parameters.AddWithValue("BankID", bankID);
cmd.Parameters.AddWithValue("PagIBIGID", eod.PagIBIGID);
cmd.Parameters.AddWithValue("ActualTxn_Date", eod.ActualTxn_Date);
cmd.Parameters.AddWithValue("Processed_Date", eod.Processed_Date);
cmd.Parameters.AddWithValue("Processed_Time", eod.Processed_Time);
cmd.Parameters.AddWithValue("AcctNo", eod.AcctNo);
cmd.Parameters.AddWithValue("BranchCode", eod.BranchCode);
cmd.Parameters.AddWithValue("Transaction_Amount", eod.Transaction_Amount);
cmd.Parameters.AddWithValue("PaymentRefNo", eod.PaymentRefNo);
cmd.Parameters.AddWithValue("Remarks", eod.Remarks);
cmd.Parameters.AddWithValue("ReferenceFile", eod.ReferenceFile);
ExecuteNonQuery(CommandType.Text);
return(true);
}
catch (Exception ex)
{
strErrorMessage = ex.Message;
return(false);
}
}
public bool PopulateDataXLS(string filePath)
{
eodList.Clear();
EOD eod = null;
int totalRecord = 0;
try
{
//readXLS(filePath);
System.Data.DataTable dt = SourceData(filePath);
if (dt != null)
{
foreach (System.Data.DataRow rw in dt.Rows)
{
if (rw[7].ToString() == "")
{
}
else if (rw[7].ToString().Contains("Common"))
{
}
else
{
eod = new EOD(logger);
eod.PagIBIGID = rw[0].ToString();
eod.ActualTxn_Date = rw[1].ToString();
eod.Processed_Date = rw[2].ToString();
eod.Processed_Time = rw[3].ToString();
eod.AcctNo = rw[4].ToString();
eod.BranchCode = rw[5].ToString();
eod.Transaction_Amount = rw[6].ToString();
eod.PaymentRefNo = rw[7].ToString();
eod.Remarks = rw[8].ToString();
eod.ReferenceFile = Path.GetFileName(filePath);
eodList.Add(eod);
eod = null;
totalRecord += 1;
}
}
}
logger.Info(string.Format("File {0} Total record {1}", Path.GetFileName(filePath), totalRecord.ToString("N0")));
return(true);
}
catch (Exception ex)
{
logger.Error(ex.Message);
return(false);
}
finally
{
if (eod != null)
{
eod = null;
}
}
}
private static bool ProcessEODList()
{
EOD eod = new EOD(logger);
if (config.IsDownloadFromSFTP == 0)
{
logger.Info("IsDownloadFromSFTP is set to " + config.IsDownloadFromSFTP.ToString());
}
else
{
Console.WriteLine(DateTime.Now.ToString("MM/dd/yy hh:mm:ss ") + "Downloading files from sftp...");
if (!eod.DownloadFilesFromSftp(config))
{
return(false);
}
}
foreach (var file in Directory.GetFiles(config.DownloadFolder))
{
Console.WriteLine(DateTime.Now.ToString("MM/dd/yy hh:mm:ss ") + "Populating data...");
bool response = false;
if (config.BankID == (short)bankID.UBP)
{
response = eod.PopulateDataTXT(file);
}
else if (config.BankID == (short)bankID.AUB)
{
response = eod.PopulateDataXLS(file);
}
if (response)
{
Console.WriteLine(DateTime.Now.ToString("MM/dd/yy hh:mm:ss ") + "Uploading data...");
eod.Upload(config);
//housekeeping
string uploadedFolder = string.Format(@"{0}\{1}", config.DownloadFolder, "Uploaded");
if (!Directory.Exists(uploadedFolder))
{
Directory.CreateDirectory(uploadedFolder);
}
File.Move(file, string.Format(@"{0}\{1}", uploadedFolder, Path.GetFileName(file)));
}
}
eod = null;
return(true);
}
public bool Check_LoanDeductionIfExist(EOD eod)
{
try
{
OpenConnection();
cmd = new SqlCommand("SELECT COUNT(*) FROM tbl_LoanDeductionRecon WHERE PagIBIGID=@PagIBIGID AND PaymentRefNo=@PaymentRefNo AND ReferenceFile=@ReferenceFile", con);
cmd.Parameters.AddWithValue("PagIBIGID", eod.PagIBIGID);
cmd.Parameters.AddWithValue("PaymentRefNo", eod.PaymentRefNo);
cmd.Parameters.AddWithValue("ReferenceFile", eod.ReferenceFile);
_ExecuteScalar(CommandType.Text);
return(true);
}
catch (Exception ex)
{
strErrorMessage = ex.Message;
return(false);
}
}
public bool PopulateDataTXT(string filePath)
{
eodList.Clear();
EOD eod = null;
int totalRecord = 0;
bool IsValidHeaderFound = false;
try
{
using (StreamReader sr = new StreamReader(filePath))
{
while (!sr.EndOfStream)
{
string line = sr.ReadLine();
if (line.Trim() != "")
{
if (line.Trim().Contains("Total"))
{
}
else if (line.Trim().Contains("Pag-IBIG MID "))
{
IsValidHeaderFound = true;
}
else
{
if (IsValidHeaderFound)
{
eod = new EOD(logger);
eod.PagIBIGID = line.Substring(0, 12).Trim();
eod.ActualTxn_Date = line.Substring(40, 20).Trim();
eod.Processed_Date = line.Substring(20, 20).Trim();
eod.Processed_Time = DateTime.Now.ToString("hh:mm:ss");
eod.AcctNo = "";
eod.BranchCode = "";
eod.Remarks = "";
eod.PaymentRefNo = line.Substring(60, 20).Trim();
eod.Transaction_Amount = line.Substring(80).Trim();
eod.ReferenceFile = Path.GetFileName(filePath);
eodList.Add(eod);
eod = null;
totalRecord += 1;
}
}
}
}
sr.Dispose();
sr.Close();
}
logger.Info(string.Format("File {0} Total record {1}", Path.GetFileName(filePath), totalRecord.ToString("N0")));
return(true);
}
catch (Exception ex)
{
logger.Error(ex.Message);
return(false);
}
finally
{
if (eod != null)
{
eod = null;
}
}
}