private void UnprocessedRec(string billNumber, string paymentRefNo)
{
try
{
OracleSQLHelper.ExecuteNonQuery("AddRecord", CommandType.Text, "insert into SADADTransactions_Unprocessed(UniqueIdentifier,FileName,BillingAcct,Amount,ProcDate,PmtRefNo) select UniqueIdentifier,FileName,BillingAcct,Amount,ProcDate,PmtRefNo from SADADTransactions_Temp where BillingAcct='" + billNumber + "' and PmtRefNo='" + paymentRefNo + "'");
}
catch (Exception ex)
{
Logger.WriteLog(ex.Message, ex.StackTrace, "UnprocessedRec");
}
}
protected DataTable Search(string billNumber, string paymentRefNo)
{
DataTable table = new DataTable();
try
{
if (!string.IsNullOrWhiteSpace(billNumber))
{
DataSet data = new DataSet();
data = OracleSQLHelper.ExecuteDataSet("Search", CommandType.Text, "select PROD,ACCTNO,KEY_CARD,AGR_ID,NIN,CUSTOMER_NO,CUSTOMER_NAME,STAGE,MIN_DUE,OUTSTANDING_DUES,MIN_DUE from SADAD_POS_TRAN_VIEW where SADAD_KEY='" + billNumber + "'");
if (data != null)
{
if (data.Tables.Count > 0)
{
if (data.Tables[0].Rows.Count > 0)
{
table = data.Tables[0];
}
else
{
UnprocessedRec(billNumber, paymentRefNo);
Logger.TrackLogs("Bill Account '" + billNumber + "' not exists", "Search in 'SADAD_POS_TRAN_VIEW' View");
}
}
else
{
UnprocessedRec(billNumber, paymentRefNo);
Logger.TrackLogs("Bill Account '" + billNumber + "' not exists", "Search in 'SADAD_POS_TRAN_VIEW' View");
}
}
else
{
UnprocessedRec(billNumber, paymentRefNo);
Logger.TrackLogs("Bill Account '" + billNumber + "' not exists", "Search in 'SADAD_POS_TRAN_VIEW' View");
}
}
}
catch (Exception ex)
{
Logger.WriteLog(ex.Message, ex.StackTrace, "Search");
}
return(table);
}
private void OnElapsedTime(object source, ElapsedEventArgs e)
{
try
{
#region Declare Variables
DataSet dataSet = new DataSet();
DataTable table = new DataTable();
string CreditCardNo = string.Empty;
string customerNo = string.Empty;
string customerName = string.Empty;
#endregion
int deletedRows = OracleSQLHelper.ExecuteNonQuery("DeleteTemp", CommandType.Text, "delete from SADADTransactions_Temp");
if (deletedRows > -1)
{
int _rows = OracleSQLHelper.ExecuteNonQuery("AddRecords", CommandType.Text, "insert into SADADTransactions_Temp (PMNTID,UniqueIdentifier,FileName,BillingAcct,Amount,ProcDate,PmtRefNo,TRX_TYPE) select PMNTID,UniqueIdentifier,FileName,BillingAcct,Amount,ProcDate,PmtRefNo,TRX_TYPE from SADADTransactions where SADADTransactions.Selected='N' and SADADTransactions.Approved='N' ");
if (_rows > 0)
{
dataSet = OracleSQLHelper.ExecuteDataSet("ReturnRecords", CommandType.Text, "select PMNTID,UniqueIdentifier,FileName,BillingAcct,Amount,ProcDate,PmtRefNo,TRX_TYPE from SADADTransactions_Temp ");
OracleSQLHelper.ExecuteNonQuery("UpdateRecords", CommandType.Text, "update SADADTransactions set Selected='Y' where(SADADTransactions.PMNTID,SADADTransactions.BillingAcct, SADADTransactions.PmtRefNo) in (select SADADTransactions_Temp.PMNTID,SADADTransactions_Temp.BillingAcct, SADADTransactions_Temp.PmtRefNo from SADADTransactions_Temp) ");
object returnValue = null;
bool isExists = false;
if (dataSet != null)
{
if (dataSet.Tables.Count > 0)
{
if (dataSet.Tables[0].Rows.Count > 0)
{
foreach (DataRow dRow in dataSet.Tables[0].Rows)
{
returnValue = null;
isExists = false;
returnValue = OracleSQLHelper.ExecuteScalar("ExistsRecord", CommandType.Text, "select 1 as Result from SADADTransactions where BILLINGACCT='" + dRow["BillingAcct"].ToString() + "' and FILENAME='" + dRow["FileName"].ToString() + "' and AMOUNT='" + dRow["Amount"].ToString() + "' and PMNTID='" + dRow["PMNTID"].ToString() + "' and APPROVED='Y'");
if (returnValue != null)
{
if (returnValue.ToString() == "1")
{
isExists = true;
}
}
if (isExists == false)
{
if (dRow["TRX_TYPE"].ToString() == "SAD")
{
#region SADAD Upload Data to NI and Bank Charge Entry
table = Search(dRow["BillingAcct"].ToString(), dRow["PmtRefNo"].ToString());
if (table != null)
{
if (table.Rows.Count > 0)
{
foreach (DataRow row in table.Rows)
{
CreditCardNo = string.Empty;
if (row["PROD"].ToString() == "LOAN")
{
#region Loans - Call SADAD Data Upload & Bank Charge Entry Procedure
SADADDataUpload(ulong.Parse(row["AGR_ID"].ToString()), decimal.Parse(dRow["Amount"].ToString()), dRow["FileName"].ToString(), dRow["BillingAcct"].ToString(), dRow["PmtRefNo"].ToString());
BankChargeEntry("LOAN", "SAD", decimal.Parse(dRow["Amount"].ToString()), "", row["CUSTOMER_NO"].ToString(), row["CUSTOMER_NAME"].ToString(), dRow["BillingAcct"].ToString(), dRow["PmtRefNo"].ToString());
#endregion
}
else if (row["PROD"].ToString() == "CARD")
{
#region Cards - Call NI Service & Bank Charge Entry Procedure
if (!string.IsNullOrWhiteSpace(row["KEY_CARD"].ToString()))
{
CreditCardNo = EncryptDecryptEngine.DecryptStringRijndael(row["KEY_CARD"].ToString().Trim());
if (!string.IsNullOrWhiteSpace(CreditCardNo))
{
if (CreditCardNo.StartsWith("000"))
{
CreditCardNo = CreditCardNo.Trim().Substring(3);
}
NICardPaymentAPI(CreditCardNo, "SADAD", dRow["Amount"].ToString(), "Customer No : " + row["CUSTOMER_NO"].ToString(), dRow["BillingAcct"].ToString(), dRow["PmtRefNo"].ToString());
BankChargeEntry("CARD", "SAD", decimal.Parse(dRow["Amount"].ToString()), "", row["CUSTOMER_NO"].ToString(), row["CUSTOMER_NAME"].ToString(), dRow["BillingAcct"].ToString(), dRow["PmtRefNo"].ToString());
}
else
{
UnprocessedRec(dRow["BillingAcct"].ToString(), dRow["PmtRefNo"].ToString());
Logger.TrackLogs("Bill Account '" + dRow["BillingAcct"].ToString(), "Invalid Credit Card Number or Empty");
}
}
else
{
UnprocessedRec(dRow["BillingAcct"].ToString(), dRow["PmtRefNo"].ToString());
Logger.TrackLogs("Bill Account '" + dRow["BillingAcct"].ToString(), "Invalid Key Card or Empty");
}
#endregion
}
}
}
else
{
UnprocessedRec(dRow["BillingAcct"].ToString(), dRow["PmtRefNo"].ToString());
Logger.TrackLogs("Bill Account '" + dRow["BillingAcct"].ToString() + "' not exists", "Search in 'SADAD_POS_TRAN_VIEW' View");
}
}
else
{
UnprocessedRec(dRow["BillingAcct"].ToString(), dRow["PmtRefNo"].ToString());
Logger.TrackLogs("Bill Account '" + dRow["BillingAcct"].ToString() + "' not exists", "Search in 'SADAD_POS_TRAN_VIEW' View");
}
#endregion
}
else
{
#region POS Upload Data to NI and Bank Charge Entry
customerName = string.Empty;
customerNo = string.Empty;
CreditCardNo = string.Empty;
if (dRow["PmtRefNo"] != null)
{
if (dRow["PmtRefNo"].ToString().Trim().Contains("LOAN_"))
{
POSDataUpload(ulong.Parse(dRow["BillingAcct"].ToString()), Convert.ToDecimal(dRow["AMOUNT"]), "POS PAYMENT FOR AGGREMAENT " + dRow["PMNTID"].ToString(), dRow["PmtRefNo"].ToString().Trim());
if (dRow["PmtRefNo"].ToString().Trim().Length > 5)
{
customerNo = dRow["FileName"].ToString().Trim().Substring(0, dRow["FileName"].ToString().Trim().IndexOf("---"));
customerName = dRow["FileName"].ToString().Trim().Substring(dRow["FileName"].ToString().Trim().IndexOf("---") + 3);
BankChargeEntry("LOAN", "POS", decimal.Parse(dRow["Amount"].ToString()), dRow["PmtRefNo"].ToString().Trim().Substring(dRow["PmtRefNo"].ToString().Trim().IndexOf("_") + 1), customerNo, customerName, dRow["BillingAcct"].ToString(), dRow["PmtRefNo"].ToString());
}
}
else
{
if (!string.IsNullOrWhiteSpace(dRow["UniqueIdentifier"].ToString()))
{
Logger.WriteLog("UniqueIdentifier", dRow["UniqueIdentifier"].ToString(), "OnElapsedTime");
CreditCardNo = EncryptDecryptEngine.DecryptStringRijndael(dRow["UniqueIdentifier"].ToString().Trim());
if (!string.IsNullOrWhiteSpace(CreditCardNo))
{
if (CreditCardNo.StartsWith("000"))
{
CreditCardNo = CreditCardNo.Trim().Substring(3);
}
customerNo = dRow["FileName"].ToString().Trim().Substring(0, dRow["FileName"].ToString().Trim().IndexOf("---"));
customerName = dRow["FileName"].ToString().Trim().Substring(dRow["FileName"].ToString().Trim().IndexOf("---") + 3);
NICardPaymentAPI(CreditCardNo, "POS", dRow["Amount"].ToString(), "Customer No : " + customerNo, dRow["BillingAcct"].ToString(), dRow["PmtRefNo"].ToString());
BankChargeEntry("CARD", "POS", decimal.Parse(dRow["Amount"].ToString()), dRow["PmtRefNo"].ToString().Trim().Substring(dRow["PmtRefNo"].ToString().Trim().IndexOf("_") + 1), customerNo, customerName, dRow["BillingAcct"].ToString(), dRow["PmtRefNo"].ToString());
}
else
{
UnprocessedRec(dRow["BillingAcct"].ToString(), dRow["PmtRefNo"].ToString());
Logger.TrackLogs("Invalid CreditCardNo or Empty", "NICardPaymentAPI");
}
}
else
{
UnprocessedRec(dRow["BillingAcct"].ToString(), dRow["PmtRefNo"].ToString());
Logger.TrackLogs("Invalid UniqueIdentifier or Empty", "NICardPaymentAPI");
}
}
}
#endregion
}
}
}
OracleSQLHelper.ExecuteNonQuery("UpdateRecords", CommandType.Text, "update SADADTransactions set Approved='Y' where(SADADTransactions.PMNTID,SADADTransactions.BillingAcct, SADADTransactions.PmtRefNo) in (select SADADTransactions_Temp.PMNTID,SADADTransactions_Temp.BillingAcct, SADADTransactions_Temp.PmtRefNo from SADADTransactions_Temp) ");
OracleSQLHelper.ExecuteNonQuery("AddRecords", CommandType.Text, "insert into SADADTransactions_Hist (UniqueIdentifier,FileName,BillingAcct,Amount,ProcDate,PmtRefNo,TRX_TYPE) select UniqueIdentifier,FileName,BillingAcct,Amount,ProcDate,PmtRefNo,TRX_TYPE from SADADTransactions_Temp");
}
}
}
}
}
}
catch (Exception ex)
{
Logger.WriteLog(ex.Message, ex.StackTrace, "OnElapsedTime");
}
}