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);
}
// InsertSalesDataListDB - overloaded method
// will update data based on the changes using the dataadapter
// need to pass in a datatable, then pass in the sqldataadapter
public int InsertSalesDataListDB(DataTable dt, SqlDataAdapter da)
{
int rowsAffected = 0;
DBAccess db = new DBAccess();
SqlConnection sql = db.CreateFuelDBConnection();
SqlCommand sqlComm = db.SetFuelDBCommandTypeStoredProcedure(sql, "uspUpdateDepartmentSales");
sqlComm.Parameters.AddWithValue("@DeptID", SqlDbType.Int);
sqlComm.Parameters["@DeptID"].SourceColumn = "SalesDeptID";
sqlComm.Parameters.AddWithValue("@Quantity", SqlDbType.Int);
sqlComm.Parameters["@Quantity"].SourceColumn = "SalesCount";
sqlComm.Parameters.AddWithValue("@TotalDollars", SqlDbType.Int);
sqlComm.Parameters["@TotalDollars"].SourceColumn = "Amount";
sqlComm.Parameters.AddWithValue("@SalesDateId", SqlDbType.Int);
sqlComm.Parameters["@SalesDateId"].SourceColumn = "DateId";
da.UpdateCommand = sqlComm;
//da.Update(dt);
sqlComm = db.SetFuelDBCommandTypeStoredProcedure(sql, "uspDeleteDeptSalesByDateIDAndSalesDeptID");
sqlComm.Parameters.AddWithValue("@SalesDeptId", SqlDbType.Int);
sqlComm.Parameters["@SalesDeptId"].SourceColumn = "SalesDeptID";
sqlComm.Parameters.AddWithValue("@SalesDateID", SqlDbType.Int);
sqlComm.Parameters["@SalesDateID"].SourceColumn = "DateId";
da.DeleteCommand = sqlComm;
sqlComm = db.SetFuelDBCommandTypeStoredProcedure(sql, "uspInsertDepartmentSales");
sqlComm.Parameters.AddWithValue("@DeptId", SqlDbType.Int);
sqlComm.Parameters["@DeptId"].SourceColumn = "SalesDeptID";
sqlComm.Parameters.AddWithValue("@Quantity", SqlDbType.Int);
sqlComm.Parameters["@Quantity"].SourceColumn = "SalesCount";
sqlComm.Parameters.AddWithValue("@TotalDollars", SqlDbType.Decimal);
sqlComm.Parameters["@TotalDollars"].SourceColumn = "Amount";
sqlComm.Parameters.AddWithValue("@SalesDateId", SqlDbType.Int);
sqlComm.Parameters["@SalesDateId"].SourceColumn = "DateId";
da.InsertCommand = sqlComm;
rowsAffected = da.Update(dt);
return(rowsAffected);
}
// Create overloaded methods to return expenses data by expenseid
// *** When entering an expenseId it will only return and populate one class
public ExpensesData(int expensesId)
{ //*********************UNFINISHED CLASS****************
// this will populate an Expenses Class
DBAccess db = new FuelCenterDB.DBAccess();
SqlConnection sqlConnect = db.CreateFuelDBConnection();
string sqlSelect = "";
SqlCommand sqlCommand = db.SetFuelDBCommandTypeStoredProcedure(sqlConnect, sqlSelect);
SqlDataAdapter da = new SqlDataAdapter();
da.SelectCommand = sqlCommand;
}
public string GetSalesDeptDataByDeptID(int deptID)
{
string deptName = "";
DeptID = deptID;
string storedProcedureName = "uspGetSalesDeptData";
DBAccess dbAccess = new DBAccess();
SqlConnection sqlConnect = dbAccess.CreateFuelDBConnection();
SqlCommand sqlCommand = dbAccess.SetFuelDBCommandTypeStoredProcedure(sqlConnect, storedProcedureName);
return(deptName);
}
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);
}
{ // Class to retrieve and execute database calls for Expenses processes
// GetExpensesDataListAll: for use to populate comboboxes and datasets for use when requiring additional data ids such as userids, or expensetypeid.
// since this data will not change regularly, just make one call and get all teh data and put into a dataset.
public DataSet GetExpensesDataListAll()
{
DataSet dsExpenseLists = new DataSet();
DBAccess db = new DBAccess();
SqlConnection sqlConnect = db.CreateFuelDBConnection();
SqlCommand sqlCommand = db.SetFuelDBCommandTypeStoredProcedure(sqlConnect, "uspGetExpenseListsAll");
SqlDataAdapter sqlDA = new SqlDataAdapter();
sqlDA.SelectCommand = sqlCommand;
sqlDA.Fill(dsExpenseLists);
dsExpenseLists.Tables[0].TableName = "ExpenseType";
dsExpenseLists.Tables[1].TableName = "Vendor";
dsExpenseLists.Tables[2].TableName = "UserName";
dsExpenseLists.Tables[3].TableName = "TenderType";
return(dsExpenseLists);
}
public DataTable GetDepartmentSalesDataByDateID(int dateid)
{
DBAccess dbAccess = new DBAccess();
SqlConnection sqlConn = dbAccess.CreateFuelDBConnection();
string spName = "uspGetSalesDeptData";
SqlCommand sqlComm = dbAccess.SetFuelDBCommandTypeStoredProcedure(sqlConn, spName);
sqlComm.Parameters.Add(new SqlParameter("@SalesDateID", dateid));
daSql.SelectCommand = sqlComm;
//daSql.SelectCommand = sqlComm;
DataTable dataTable = new DataTable();
daSql.Fill(dataTable);
return(dataTable);
}
//GetUserData - Returns list of Users' Info
public DataSet GetExpensesListByDateId(int dateId)
{
DataSet dsExpenses = new DataSet();
dsExpenses.Tables[0].TableName = "ExpensesList";
DBAccess db = new FuelCenterDB.DBAccess();
SqlConnection sqlConnect = db.CreateFuelDBConnection();
SqlCommand sqlCommand = db.SetFuelDBCommandTypeStoredProcedure(sqlConnect, "uspGetExpensesListByDateID");
sqlCommand.Parameters.AddWithValue("@DateId", null).Value = dateId;
SqlDataAdapter sqlDA = new SqlDataAdapter();
sqlDA.SelectCommand = sqlCommand;
sqlDA.Fill(dsExpenses.Tables["ExpensesList"]);
return(dsExpenses);
}
// 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);
}
//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);
}
public int UpdateExpenseDataGridList(DataTable expensesDataSet)
{
int rowsAffected = 0;
DBAccess db = new DBAccess();
SqlConnection sqlConnect = db.CreateFuelDBConnection();
SqlCommand sqlCommand = db.SetFuelDBCommandTypeStoredProcedure(sqlConnect, "");
try
{
SqlDataAdapter sqlDA = new SqlDataAdapter();
sqlCommand = db.SetFuelDBCommandTypeStoredProcedure(sqlConnect, "uspDeleteExpenseRecordsByExpenseID");
sqlCommand.Parameters.AddWithValue("@ExpensesId", SqlDbType.Int);
sqlCommand.Parameters["@ExpensesId"].SourceColumn = "ExpensesId";
sqlDA.DeleteCommand = sqlCommand;
sqlCommand = db.SetFuelDBCommandTypeStoredProcedure(sqlConnect, "uspUpdateExpensesList");
sqlCommand.Parameters.AddWithValue("@ExpensesId", SqlDbType.Int);
sqlCommand.Parameters["@ExpensesId"].SourceColumn = "ExpensesId";
sqlCommand.Parameters.AddWithValue("@VendorId", SqlDbType.Int);
sqlCommand.Parameters["@VendorId"].SourceColumn = "VendorId";
sqlCommand.Parameters.AddWithValue("@ExpenseTypeId", SqlDbType.Int);
sqlCommand.Parameters["@ExpenseTypeId"].SourceColumn = "ExpenseTypeId";
sqlCommand.Parameters.AddWithValue("@Description", SqlDbType.VarChar);
sqlCommand.Parameters["@Description"].SourceColumn = "Description";
sqlCommand.Parameters.AddWithValue("@Amount", SqlDbType.Decimal);
sqlCommand.Parameters["@Amount"].SourceColumn = "Amount";
sqlCommand.Parameters.AddWithValue("@UserId", SqlDbType.Int);
sqlCommand.Parameters["@UserId"].SourceColumn = "UserId";
sqlCommand.Parameters.AddWithValue("@Cleared", SqlDbType.Bit);
sqlCommand.Parameters["@Cleared"].SourceColumn = "Cleared";
sqlCommand.Parameters.AddWithValue("@CheckNo", SqlDbType.VarChar);
sqlCommand.Parameters["@CheckNo"].SourceColumn = "CheckNo";
sqlCommand.Parameters.AddWithValue("@CheckNotes", SqlDbType.VarChar);
sqlCommand.Parameters["@CheckNotes"].SourceColumn = "CheckNotes";
sqlCommand.Parameters.AddWithValue("@DateId", SqlDbType.Int);
sqlCommand.Parameters["@DateId"].SourceColumn = "DateId";
sqlDA.UpdateCommand = sqlCommand;
sqlCommand = db.SetFuelDBCommandTypeStoredProcedure(sqlConnect, "uspInsertExpensesData");
//sqlCommand.Parameters.AddWithValue("@ExpensesId", SqlDbType.Int);
//sqlCommand.Parameters["@ExpensesId"].SourceColumn = expensesDataSet.Tables["ExpensesDataList"].Columns["ExpensesId"].ColumnName;
sqlCommand.Parameters.AddWithValue("@VendorId", SqlDbType.Int);
sqlCommand.Parameters["@VendorId"].SourceColumn = expensesDataSet.Columns["VendorId"].ColumnName;
sqlCommand.Parameters.AddWithValue("@ExpenseTypeId", SqlDbType.Int);
sqlCommand.Parameters["@ExpenseTypeId"].SourceColumn = expensesDataSet.Columns["ExpenseTypeId"].ColumnName;
sqlCommand.Parameters.AddWithValue("@TenderTypeId", SqlDbType.VarChar);
sqlCommand.Parameters["@TenderTypeId"].SourceColumn = expensesDataSet.Columns["TenderTypeId"].ColumnName;
sqlCommand.Parameters.AddWithValue("@Description", SqlDbType.VarChar);
sqlCommand.Parameters["@Description"].SourceColumn = expensesDataSet.Columns["Description"].ColumnName;
sqlCommand.Parameters.AddWithValue("@Amount", SqlDbType.Decimal);
sqlCommand.Parameters["@Amount"].SourceColumn = expensesDataSet.Columns["Amount"].ColumnName;
sqlCommand.Parameters.AddWithValue("@UserId", SqlDbType.Int);
sqlCommand.Parameters["@UserId"].SourceColumn = expensesDataSet.Columns["UserId"].ColumnName;
sqlCommand.Parameters.AddWithValue("@Cleared", SqlDbType.Bit);
sqlCommand.Parameters["@Cleared"].SourceColumn = expensesDataSet.Columns["Cleared"].ColumnName;
sqlCommand.Parameters.AddWithValue("@CheckNo", SqlDbType.VarChar);
sqlCommand.Parameters["@CheckNo"].SourceColumn = expensesDataSet.Columns["CheckNo"].ColumnName;
sqlCommand.Parameters.AddWithValue("@CheckNotes", SqlDbType.VarChar);
sqlCommand.Parameters["@CheckNotes"].SourceColumn = expensesDataSet.Columns["CheckNotes"].ColumnName;
sqlCommand.Parameters.AddWithValue("@DateId", SqlDbType.Int);
sqlCommand.Parameters["@DateId"].SourceColumn = expensesDataSet.Columns["DateId"].ColumnName;
sqlDA.InsertCommand = sqlCommand;
if (expensesDataSet == null)
{
}
//MessageBox.Show("IT IS NULL");
else
{
sqlDA.Update(expensesDataSet);
}
}
catch (SqlException ex)
{
//MessageBox.Show(ex.Message);
// ErrorMessage.ErrorMessage = ex.ErrorMessage;
//ErrorMessage.ClassName = this.ToString();
}
catch (ArgumentNullException ex)
{
ErrorMessage.Message = ex.Message;
ErrorMessage.ClassName = this.ToString();
}
catch (Exception ex)
{
ErrorMessage.Message = ex.Message;
ErrorMessage.ClassName = this.ToString();
}
return(rowsAffected);
}