public DataSet GetExpensesListByDate(int dateId)
{
DataSet dsExpenses = new DataSet();
DBAccess db = new FuelCenterDB.DBAccess();
SqlConnection sqlConnect = db.CreateFuelDBConnection();
string sql = "uspGetExpensesListByDateId";
SqlCommand sqlCommand = db.SetFuelDBCommandTypeStoredProcedure(sqlConnect, sql);
try
{
sqlConnect.Open();
SqlDataAdapter da = new SqlDataAdapter();
da.SelectCommand = sqlCommand;
da.SelectCommand.Parameters.AddWithValue("@DateID", (int)dateId);
da.Fill(dsExpenses);
dsExpenses.Tables[0].TableName = "ExpensesDataList";
}
catch (SqlException ex)
{
Message = ex.Message;
}
finally
{
db.CloseConnection(ref sqlConnect);
}
return(dsExpenses);
}
// Method will return bool based on whether there is already data in teh Sales Department table
public bool DoesSalesDeptDataExist(int salesDate)
{
bool dataExists = false;
DBAccess dbAccess = new DBAccess();
SqlConnection sqlConn = dbAccess.CreateFuelDBConnection();
string spName = "uspGetDepartmentSalesData";
SqlCommand sqlComm = dbAccess.SetFuelDBCommandTypeStoredProcedure(sqlConn, spName);
try
{
sqlConn.Open();
sqlComm.Parameters.Add("@SalesDateID");
int sqlReader = sqlComm.ExecuteNonQuery(); // executenonquery returns an integer
if (sqlReader > 0)
{
dataExists = true;
}
}
catch (SqlException ex)
{
Message = ex.Message;
}
catch (Exception ex)
{
Message = ex.Message;
}
finally
{ dbAccess.CloseConnection(ref sqlConn); }
return(dataExists);
}
public bool UpdateGasSalesChanges(ArrayList arrFuelData, int salesDateId)
{
bool isSuccess = false;
//string sql = "";
DBAccess fueldb = new DBAccess();
SqlConnection sqlConn = new SqlConnection();
sqlConn = fueldb.CreateFuelDBConnection();
SqlCommand sqlCommand = fueldb.SetFuelDBCommandTypeStoredProcedure(sqlConn, "uspInsertGasSalesData");
try
{
sqlConn.Open();
foreach (FuelData fd in arrFuelData)
{
sqlCommand.Parameters.AddWithValue("@GasTypeId", SqlDbType.Int);
sqlCommand.Parameters["@GasTypeId"].Value = fd.FuelType;
sqlCommand.Parameters.AddWithValue("@GallonsSold", SqlDbType.Decimal);
sqlCommand.Parameters["@GallonsSold"].Value = fd.SoldAmount;
sqlCommand.Parameters.AddWithValue("@Price", SqlDbType.Decimal);
sqlCommand.Parameters["@Price"].Value = fd.Price;
sqlCommand.Parameters.AddWithValue("TotalDollarAmount", SqlDbType.Decimal);
sqlCommand.Parameters["@TotalDollarAmount"].Value = fd.SoldAmount;
sqlCommand.Parameters.AddWithValue("@CostOfGas", SqlDbType.Decimal);
sqlCommand.Parameters["@CostOfGas"].Value = fd.CostOfGas;
sqlCommand.Parameters.AddWithValue("@MobilPrice", SqlDbType.Decimal);
sqlCommand.Parameters["@MobilPrice"].Value = 0.00m;
sqlCommand.Parameters.AddWithValue("@QTPrice", SqlDbType.Decimal);
sqlCommand.Parameters["@QTPrice"].Value = 0.00m;
sqlCommand.Parameters.AddWithValue("@ChevronPrice", SqlDbType.Decimal);
sqlCommand.Parameters["@ChevronPrice"].Value = 0.00m;
sqlCommand.ExecuteNonQuery();
}
}
catch (Exception ex)
{
ExceptionsMessages = new FuelCenterDB.Exceptions(ex);
}
finally
{
fueldb.CloseConnection(ref sqlConn);
}
return(isSuccess);
}
public int GetSalesDateIDBySalesDate(DateTime businessdate)
{ // will retrieve sales date id from the given business date.
// however, it will not be a method available to an instantiated class.
// the stored procedure in this will create a new sales date and sales date id if one does not exist.
int salesdateid = 0;
string sql = "uspGetOrInsertSalesDateID";
DBAccess fuelDB = new DBAccess();
SqlConnection sqlConn = fuelDB.CreateFuelDBConnection();
SqlCommand sqlComm = fuelDB.SetFuelDBCommandTypeStoredProcedure(sqlConn, sql);
sqlComm.Parameters.AddWithValue("@SalesDate", SqlDbType.DateTime);
sqlComm.Parameters["@SalesDate"].Value = businessdate;
// when having an OUTPUT in the stored procedure, you still need to specify the OUTPUT parameter
// Since we are using executescalar because we are not returning a recordset, we need to specify the OUTPUT parameter
// So we create an output parameter. This parameter can be accesssed after the executenonquery has occured.
SqlParameter returnSalesDateId = new SqlParameter("@SalesDateId", SqlDbType.Int);
returnSalesDateId.Direction = ParameterDirection.Output;
sqlComm.Parameters.Add(returnSalesDateId);
try
{
sqlConn.Open();
sqlComm.ExecuteScalar();
//int counter = 0;
// the stored procedure should always return a sales date because it will either select or create a new salesdate record.
salesdateid = (int)returnSalesDateId.Value;
}
catch (SqlException ex)
{
salesdateid = -1; // error exists
MessageErrorDB = ex.Message;
}
catch (Exception ex)
{
salesdateid = -1; // error exists
MessageError = ex.Message;
}
finally
{ fuelDB.CloseConnection(ref sqlConn); }
return(salesdateid);
}
// InsertsalesDataListDB: will either update modified data, or insert new data.
// Requires a List<SalesDeptData>
// returns total number of rows affected.
public int InsertSalesDataListDB(List <SalesDepartmentData> listSalesData)
{ // this will update a list of SalesData objects from teh salesdata datagridview. Instead of using a data adapter, a list will be sent to this
// method, and will iterate through the list and update each SalesData object seperately.
int counter = 0; // counter- will count how many records were updated.
DBAccess db = new DBAccess();
SqlConnection sqlConn = db.CreateFuelDBConnection();
//SqlCommand sqlComm = db.SetFuelDBCommandTypeStoredProcedure(sqlConn, "uspInsertDepartmentSales");
string spName = "";
try
{
sqlConn.Open();
foreach (SalesDepartmentData sales in listSalesData)
{
if (DoesSalesDeptDataExist(sales.SalesDateId))
{// if there are records in the db then just do an update of those recoreds
spName = "uspUpdateDepartmentsales";
}
else
{// if there are no records then do an insert.
spName = "uspInsertDepartmentSales";
}
SqlCommand sqlComm = db.SetFuelDBCommandTypeStoredProcedure(sqlConn, spName);
sqlComm.Parameters.Add(new SqlParameter("@DeptID", sales.DeptID));
sqlComm.Parameters.Add(new SqlParameter("@Quantity", sales.Quantity));
sqlComm.Parameters.Add(new SqlParameter("@Amount", sales.TotalDollars));
sqlComm.Parameters.Add(new SqlParameter("@SalesDateID", sales.SalesDateId));
counter++;
}
}
catch (SqlException ex)
{
Message = ex.Message;
}
catch (Exception ex)
{
Message = ex.Message;
}
finally
{ db.CloseConnection(ref sqlConn); }
return(counter);
}
public DateTime?GetSalesDateByDateID(int salesdateid)
{ // will retrieve sales date from the given business date id.
// however, it will not be a method available to an instantiated class.
DateTime?salesdate = null;
string sql = @"Select Top 1 SalesDate from [Bookkeeping].[SalesDate] where SalesDateID = " + salesdateid + ";";
DBAccess fuelDB = new DBAccess();
SqlConnection sqlConn = fuelDB.CreateFuelDBConnection();
SqlCommand sqlComm = fuelDB.SetFuelDBCommandTypeText(sqlConn, sql);
try
{
sqlConn.Open();
SqlDataReader sqlReader = sqlComm.ExecuteReader();
//int counter = 0;
if (sqlReader.HasRows == true)
{
while (sqlReader.Read())
{
salesdate = (DateTime)sqlReader["SalesDate"];
}
sqlReader.Close();
}
else
{
return(salesdate);
}
}
catch (SqlException ex)
{
//salesdateid = -1; // error exists
MessageErrorDB = ex.Message;
}
catch (Exception ex)
{
// error exists
MessageError = ex.Message;
}
finally
{ fuelDB.CloseConnection(ref sqlConn); }
return(salesdate);
}
public ArrayList GetGasSalesByDateId(int salesDateId)
{
ArrayList gasRecordsArray = new ArrayList();
string sqlText = @"Select GasTypeID, GallonsSold, Price, TotalDollarAmount, CostOfGas from Bookkeeping.GasSales where DateID = " + salesDateId + ";";
//ArrayList gasRecordsArray = new ArrayList();
DBAccess sqlGasSales = new DBAccess();
SqlConnection sqlConn = sqlGasSales.CreateFuelDBConnection();
SqlCommand sqlCommand = sqlGasSales.SetFuelDBCommandTypeText(sqlConn, sqlText);
try
{
sqlConn.Open();
SqlDataReader sqlreader = sqlCommand.ExecuteReader();
FuelData gasSalesValues = new FuelData();
if (sqlreader.HasRows == true)
{
while (sqlreader.Read())
{
//sqlvalues += sqlreader["GallonsSold"].ToString() + ", ";
gasRecordsArray.Add(new FuelData()
{
FuelType = (int)sqlreader["GasTypeId"],
SoldAmount = (decimal)sqlreader["GallonsSold"],
Price = (decimal)sqlreader["Price"],
DollarAmount = (decimal)sqlreader["TotalDollarAmount"],
CostOfGas = (decimal)sqlreader["CostOfGas"]
});
}
}
}
catch (Exception ex)
{
ExceptionsMessages = new FuelCenterDB.Exceptions(ex);
}
finally { sqlGasSales.CloseConnection(ref sqlConn); }
return(gasRecordsArray);
}
//GetTenderType - Returns dict for how a customer paid
public Dictionary <int, string> GetTenderType()
{
Dictionary <int, string> dictTenderType = new Dictionary <int, string>();
DBAccess db = new DBAccess();
SqlConnection sqlConnect = db.CreateFuelDBConnection();
SqlCommand sqlCommand = db.SetFuelDBCommandTypeStoredProcedure(sqlConnect, "uspGetTenderTypeList");
try
{
using (sqlConnect)
{
sqlConnect.Open();
SqlDataReader sqlReader = sqlCommand.ExecuteReader();
if (sqlReader.HasRows == true)
{
while (sqlReader.Read())
{
dictTenderType.Add((int)sqlReader["TenderTypeId"], (string)sqlReader["TenderType"]);
}
}
else
{
dictTenderType.Add(0, "");
}
sqlReader.Close();
}
}
catch (SqlException ex)
{ Message = ex.Message; }
catch (Exception ex)
{ Message = ex.Message; }
finally
{ db.CloseConnection(ref sqlConnect); }
return(dictTenderType);
}