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