public List <TransactionDataStruct> GetTransactionData(CashupStruct cashupStruct, int venusTraceNumber, out int lastTransactionTrace, int fkTerminalKey) { var transactionList = new List <TransactionDataStruct>(); var td = new TransactionDataStruct(); try { _dbConnection.Open(); var ssql = string.Format(@"SELECT * FROM ABM_vwGetVenusTransactions WHERE Batch = {0} and fkTerminalID = {2} AND TransactionType = {1} AND Amount > 0 AND CardPaymentInfo IS NULL", cashupStruct.Batch, cashupStruct.TransactionType, fkTerminalKey); _sc = new SqlCommand(ssql, _dbConnection); _sdr = _sc.ExecuteReader(); while (_sdr.Read()) { td.VendSource = string.IsNullOrEmpty(Convert.ToString(_sdr["CardPaymentInfo"])) ? 0 : 1; td.AccountNumber = Convert.ToString(_sdr["AccountNumber"]); td.Batch = Convert.ToInt32(_sdr["Batch"]); td.CashAmount = Convert.ToDecimal(_sdr["Amount"]); td.TerminalCode = Convert.ToString(_sdr["TerminalId"]); td.TransactionDateTime = Convert.ToDateTime(_sdr["TransactionDateTime"]); td.CashierId = Convert.ToString(_sdr["TellerID"]); td.LocationName = Convert.ToString(_sdr["LocationName"]); td.TransactionId = Convert.ToInt32(_sdr["TransactionId"]); td.UnitId = Convert.ToString(_sdr["UnitID"]); td.VendSource = Convert.ToInt32(_sdr["VendSource"]); venusTraceNumber++; td.VenusTrace = venusTraceNumber; if (td.VenusTrace > 999999) { DeleteVenusTraces(); td.VenusTrace = 1; venusTraceNumber = 1; } transactionList.Add(td); } } catch (Exception ex) { throw new Exception("DatabaseInteraction - ExecuteVenusStoredProcedure - " + ex.Message); } finally { if (_dbConnection != null) { _dbConnection.Close(); } } lastTransactionTrace = td.VenusTrace; return(transactionList); }
public bool SaveCashup(CashupStruct cu, string venusFileName) { try { _dbConnection.Open(); string ssql = @"INSERT INTO VenusCashups " + "(VenusFileName" + ",TerminalCode" + ",Location" + ",Batch" + ",CashupTotal" + ",Reference" + ",SealNumber" + ",CashupDateTime)" + "VALUES ('" + venusFileName + "'" + ",'" + cu.TerminalCode + "'" + ",'" + cu.Location + "'" + ",'" + cu.Batch + "'" + ",'" + cu.CashTotal + "'" + ",'" + cu.ReferenceNumber + "'" + ",'" + cu.SealNumber + "'" + ",'" + cu.CashupDateTime + "')"; _sc = new SqlCommand(ssql, _dbConnection); _sc.ExecuteNonQuery(); } catch (Exception ex) { throw new Exception("DatabaseInteraction - SaveVenusTrace - " + ex.Message); } finally { if (_dbConnection != null) { _dbConnection.Close(); } } return(true); }
public List <TransactionDataStruct> ExecuteVenusTransactionsStoredProcedure(string storedProcedureName, CashupStruct cu, int venusTraceNumber, out int lastTransactionTrace) { List <TransactionDataStruct> transactionList = new List <TransactionDataStruct>(); TransactionDataStruct td = new TransactionDataStruct(); try { _dbConnection.Open(); _sc = new SqlCommand(storedProcedureName, _dbConnection) { CommandType = System.Data.CommandType.StoredProcedure }; _sc.Parameters.Add(new SqlParameter("@TerminalCode", cu.TerminalCode)); _sc.Parameters.Add(new SqlParameter("@BatchNumber", cu.Batch)); _sc.Parameters.Add(new SqlParameter("@TransactionType", cu.TransactionType)); _sc.Parameters.Add(new SqlParameter("@CashupDateTime", cu.CashupDateTime)); _sdr = _sc.ExecuteReader(); while (_sdr.Read()) { td.VendSource = Convert.ToInt32(_sdr["VendSource"]); td.AccountNumber = Convert.ToString(_sdr["AccountNumber"]); td.Batch = Convert.ToInt32(_sdr["Batch"]); td.CashAmount = Convert.ToDecimal(_sdr["Amount"]); td.TerminalCode = cu.TerminalCode; td.TransactionDateTime = Convert.ToDateTime(_sdr["TransactionDateTime"]); td.CashierId = Convert.ToString(_sdr["TellerID"]); td.LocationName = Convert.ToString(_sdr["LocationName"]); td.UnitId = cu.Vendor == "CashPower" ? Convert.ToString(_sdr["UnitID"]) : string.Empty; td.VendSource = Convert.ToInt32(_sdr["VendSource"]); venusTraceNumber++; td.VenusTrace = venusTraceNumber; if (td.VenusTrace > 999999) { DeleteVenusTraces(); td.VenusTrace = 1; venusTraceNumber = 1; } transactionList.Add(td); } } catch (Exception ex) { throw new Exception("DatabaseInteraction - ExecuteVenusStoredProcedure - " + ex.Message); } finally { if (_dbConnection != null) { _dbConnection.Close(); } } lastTransactionTrace = td.VenusTrace; return(transactionList); }
public List <CashupStruct> ExecuteVenusCashupStoredProcedure(string storedProcedureName, string sqlparam, out List <string> strList) { var cashupList = new List <CashupStruct>(); var cashups = new List <CashupStruct>(); var cu = new CashupStruct(); try { _dbConnection.Open(); _sc = new SqlCommand(storedProcedureName, _dbConnection) { CommandType = System.Data.CommandType.StoredProcedure }; _sc.Parameters.Add(new SqlParameter("@TransactionType", sqlparam)); _sdr = _sc.ExecuteReader(); int i = 1; while (_sdr.Read()) { if (i > 500) { break; } if (sqlparam == "7") { if ((Convert.ToInt64(_sdr["fkTerminalKey"]) == 10) || (Convert.ToInt64(_sdr["fkTerminalKey"]) == 101)) { cu.Batch = Convert.ToInt32(_sdr["Batch"]); cu.CashTotal = Convert.ToInt32(_sdr["CashTotal"]); cu.FkTerminalsId = Convert.ToInt32(_sdr["fkTerminalKey"]); cu.CashupDateTime = Convert.ToDateTime(_sdr["CashupDateTime"]); cu.CashupsId = Convert.ToInt32(_sdr["CashupsID"]); cu.Location = Convert.ToString(_sdr["LocationName"]); cu.TerminalCode = Convert.ToString(_sdr["TerminalID"]); cu.SealNumber = Convert.ToString(_sdr["SealNumber"]); cu.ReferenceNumber = Convert.ToString(_sdr["BankReference"]); cu.TransactionType = Convert.ToInt32(_sdr["TransactionType"]); if (cu.TransactionType == 1) { cu.Vendor = "Ims"; } else if (cu.TransactionType == 3) { cu.Vendor = "CashPower"; } else if (cu.TransactionType == 7) { cu.Vendor = "JHBBP"; } if (cu.SealNumber != "0") { cashupList.Add(cu); } } } else { cu.Batch = Convert.ToInt32(_sdr["Batch"]); cu.CashTotal = Convert.ToInt32(_sdr["CashTotal"]); cu.FkTerminalsId = Convert.ToInt64(_sdr["fkTerminalKey"]); cu.CashupDateTime = Convert.ToDateTime(_sdr["CashupDateTime"]); cu.Location = Convert.ToString(_sdr["LocationName"]); cu.TerminalCode = Convert.ToString(_sdr["TerminalID"]); cu.SealNumber = Convert.ToString(_sdr["SealNumber"]); cu.ReferenceNumber = Convert.ToString(_sdr["BankReference"]); cu.TransactionType = Convert.ToInt32(_sdr["TransactionType"]); cu.CashupsId = Convert.ToInt32(_sdr["CashupsID"]); if (cu.TransactionType == 1) { cu.Vendor = "Ims"; } if (cu.TransactionType == 10) { cu.Vendor = "Actaris"; } else if (cu.TransactionType == 3) { cu.Vendor = "CashPower"; } else if (cu.TransactionType == 7) { cu.Vendor = "JHBBP"; } else if (cu.TransactionType == 2) { cu.Vendor = "CityPowerCP"; } else if (cu.TransactionType == 6) { cu.Vendor = "Grintek"; } else if (cu.TransactionType == 5) { cu.Vendor = "Syntell"; } else if (cu.TransactionType == 11) { cu.Vendor = "BelaBela"; } else if (cu.TransactionType == 15) { cu.Vendor = "KwaDukuza"; } else if (cu.TransactionType == 12) { cu.Vendor = "Airtime"; } else if (cu.TransactionType == 9) { cu.Vendor = "MadibengBP"; } else if (cu.TransactionType == 22) { cu.Vendor = "CigiCell"; } if (cu.SealNumber != "0") { cashupList.Add(cu); } if ((cu.SealNumber == "0") && (cu.TransactionType == 2)) { cashupList.Add(cu); } } i++; } } catch (Exception ex) { throw new Exception("DatabaseInteraction - ExecuteVenusStoredProcedure - " + ex.Message); } finally { if (_dbConnection != null) { _dbConnection.Close(); } } strList = new List <string>(); strList.Add("TerminalCode,FkTerminalKey,CashupDateTime,Batch,CashTotal,Total,TransactionType"); foreach (CashupStruct cs in cashupList) { decimal tot = 0; try { tot = TotalCashAmountByBatch(cs.FkTerminalsId, cs.Batch, cs.TransactionType); } catch { } if ((tot == cs.CashTotal) && (tot != 0)) { cashups.Add(cs); } else { strList.Add(cs.TerminalCode + "," + cs.FkTerminalsId + "," + cs.CashupDateTime + "," + cs.Batch + "," + cs.CashTotal + "," + tot + "," + cs.TransactionType); } } if (strList.Count > 1) { string errorLogPath = _settingFile + @"LogFiles\ErrorLog" + cu.Vendor + DateTime.Now.ToString("yyyyMMdd") + ".csv"; File.WriteAllLines(errorLogPath, strList.ToArray()); } return(cashups); }