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