public static void DeleteEntry(BudgetDB budget, string catOrVend, int toDeleteID)
{
if (catOrVend == "Category")
{
Table <Category> catTable = budget.GetCategoryTable();
IQueryable <Category> modifyCategoryQuery =
from cats in catTable
where cats.CategoryID == toDeleteID
select cats;
catTable.DeleteOnSubmit(modifyCategoryQuery.Single());
}
else if (catOrVend == "Vendor")
{
Table <Vendor> vendorTable = budget.GetVendorTable();
IQueryable <Vendor> modifyVendorQuery =
from vendors in vendorTable
where vendors.VendorID == toDeleteID
select vendors;
vendorTable.DeleteOnSubmit(modifyVendorQuery.Single());
}
else
{
MessageBox.Show("Error! Invalid Transaction Type.", "BudgetApp", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
public static String GetOrsCode(String ors_allotment)
{
BudgetDB db = new BudgetDB();
var allotment = db.allotments.Where(p => p.ID.ToString() == ors_allotment).FirstOrDefault();
return(allotment.Code2 ?? "");
}
public int SaveBudget(BudgetDB objDB, SqlTransaction objTrans)
{
int ID = 0;
DataAccessLayer objDataLayer = new DataAccessLayer();
SqlCommand objCmd = new SqlCommand();
try
{
objCmd.CommandText = "usp_SaveBudget";
objCmd.CommandType = CommandType.StoredProcedure;
objCmd.Parameters.AddWithValue("@FinancialYear", objDB.FinancialYear);
objCmd.Parameters.AddWithValue("@QuarterID", objDB.QuarterID);
objCmd.Parameters.AddWithValue("@Budget", objDB.Budget);
objCmd.Parameters.AddWithValue("@ModelGroupID", objDB.ModelGroupID);
objCmd.Parameters.AddWithValue("@ModelCategoryID", objDB.ModelCategoryID);
objCmd.Parameters.AddWithValue("@ModelClutchID", objDB.ModelClutchID);
objCmd.Parameters.AddWithValue("@ModelSpecialID", objDB.ModelSpecialID);
objCmd.Parameters.AddWithValue("@ID", SqlDbType.Int);
objCmd.Parameters["@ID"].Direction = ParameterDirection.Output;
objCmd.Transaction = objTrans;
objCmd.Connection = objTrans.Connection;
objDataLayer.Command = objCmd;
objDataLayer.ExecQuery();
ID = Convert.ToInt32(objCmd.Parameters["@ID"].Value);
return ID;
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// EditSelection class constructor.
/// </summary>
/// <param name="budget">BudgetDB object: the budget DB for the budget currently accessed by the program.</param>
/// <param name="catOrVend">String: text declaring the type.</param>
public EditSelection(BudgetDB budget, string catOrVend)
{
InitializeComponent();
_catOrVend = catOrVend;
_budget = budget;
//TODO: update the text in the form to represent Vendor or Category.
PrepFieldsAndFillDGV(_catOrVend);
}
//Constructor
public TransactionManager(bool isModification, int transID, BudgetDB budget)
{
InitializeComponent();
_budget = budget;
_transID = transID;
//Prep the fields.
PrepTransactionManagerFields(isModification);
}
public DisplayCategoriesManager(BudgetDB budget)
{
InitializeComponent();
_budget = budget;
//Fill display lists.
_displayedCategories = _budget.GetCategoryListByDisplay(true);
_notDisplayedCategories = _budget.GetCategoryListByDisplay(false);
//Load the display list boxes
RefreshDisplayListBoxes();
}
/// <summary>
/// Initiates the load existing budget database process.
/// </summary>
private void loadBudgetToolStripMenuItem_Click(object sender, EventArgs e)
{
//Request backup file pathway from the user.
MessageBox.Show("Select the backup budget file you would like to open.");
//TODO: Need to increase validation. User could select a .bak file that's not actually for this program.
//Validate user selection
if (openFileDialog1.ShowDialog() == DialogResult.OK && Path.GetExtension(openFileDialog1.FileName) == ".bak")
{
//Check if a budget object is currently loaded.
if (budget != null)
{
//If a budget object currently exists, save it.
SaveCurrentBudget(budget);
//Delete the current DB.
DeleteDB(_connString);
//Clear the budget object.
budget = null;
}
//Load the budget file selected by the user.
LoadBudget(openFileDialog1.FileName);
budget = new BudgetDB(openFileDialog1.FileName, _connString);
//If no accounts exist, start a loop requiring the user to enter an account.
if (budget.GetAccountCount() == 0)
{
while (!InitialAccountCreation())
{
//Keep looping here until the user creates an account.
}
}
//Fill the dgvs
FillDGVS();
//Save to the .bak file.
Utils.SaveCurrentBudget(budget);
}
else //Inform the user to open a correct file.
{
MessageBox.Show("Please select an appropriate file to open.");
}
}
/// <summary>
/// Tests for existence of currently open budget. If a budget is found to be open, creates a backup of the budget at the stored save location. Should be called after any DB interaction.
/// </summary>
/// <param name="currentBudget">Budget: the current budget object.</param>
///
public static void SaveCurrentBudget(BudgetDB currentBudget)
{
var file = new FileInfo(currentBudget.GetBackupLocation());
//Check for existence of the .bak file.
if (File.Exists(currentBudget.GetBackupLocation()))
{
//If exists, check if the file is currently accessed by another program (common if recently saved in a syncing drive such as GoogleDrive).
while (IsFileLocked(file))
{
//wait for file to be available.
//TODO: provide some indication to the user as to why the program is pausing.
}
try
{
//Save the database to a .bak file.
SaveBak(currentBudget.GetConnectionString(), currentBudget.GetBackupLocation());
}
catch (Exception ex)
{
MessageBox.Show($"Error backing up currently open budget. Application will close. {ex}", "BudgetApp", MessageBoxButtons.OK, MessageBoxIcon.Information);
//TODO: refine this exit.
Application.Exit();
}
}
else //If DB has yet to be backedup, then the file will not already exist and cannot be in use. Go straight to saving.
{
try
{
//Save the database to a .bak file.
SaveBak(currentBudget.GetConnectionString(), currentBudget.GetBackupLocation());
}
catch (Exception ex)
{
MessageBox.Show($"Error backing up currently open budget. Application will close. {ex}", "BudgetApp", MessageBoxButtons.OK, MessageBoxIcon.Information);
//TODO: refine this exit.
Application.Exit();
}
}
}
public int SaveBudget(BudgetUI objUI, System.Data.SqlClient.SqlTransaction objTrans)
{
int ID = 0;
bool flagTransation = true;
BudgetDB objDB = new BudgetDB();
objDB.FinancialYear = objUI.FinancialYear;
objDB.QuarterID = objUI.QuarterID;
objDB.Budget = objUI.Budget;
objDB.ModelGroupID = objUI.ModelGroupID;
objDB.ModelCategoryID = objUI.ModelCategoryID;
objDB.ModelClutchID = objUI.ModelClutchID;
objDB.ModelSpecialID = objUI.ModelSpecialID;
DataAccessLayer objDataAccess = new DataAccessLayer();
try
{
if (objTrans == null)
{
flagTransation = false;
objDataAccess.GetConnection.Open();
SqlTransaction objTransaction = objDataAccess.GetConnection.BeginTransaction();
objTrans = objTransaction;
}
BudgetManager objManager = new BudgetManager();
ID = objManager.SaveBudget(objDB, objTrans);
if (!flagTransation)
objTrans.Commit();
return ID;
}
catch (Exception ex)
{
if (!flagTransation)
objTrans.Rollback();
throw ex;
}
finally
{
objDataAccess.GetConnection.Close();
}
}
/// <summary>
/// Initiates new budget database process.
/// </summary>
private void newToolStripMenuItem_Click(object sender, EventArgs e)
{
//Request storage location pathway from user.
MessageBox.Show("Name your new backup file and select a storage location. This can be used for syncing across devices if saved in a syncing folder such as Google Drive.");
//Validate user filename
if (saveFileDialog1.ShowDialog() == DialogResult.OK && Path.GetExtension(saveFileDialog1.FileName) == ".bak")
{
//Check if a budget is currently open.
if (budget != null)
{
//If a different budget is currently open, save it.
SaveCurrentBudget(budget);
//Delete the current DB
DeleteDB(_connString);
//Clear the budget object
budget = null;
}
//Creat a new budget DB and set the budget object = to a new BudgetDB object.
NewBudget(_connString);
budget = new BudgetDB(saveFileDialog1.FileName, _connString);
//Have user enter at least 1 account.
while (!InitialAccountCreation())
{
//Keep looping here until the user creates an account.
//There is no escape. Resistance is futile.
}
//Fill the dgvs
FillDGVS();
//Save to the .bak file.
Utils.SaveCurrentBudget(budget);
}
else //Inform the user to enter appropriate file extension.
{
MessageBox.Show("Please use the requested file extension (bak).");
}
}
public static void InsertORSNo(Int32 id)
{
BudgetDB db = new BudgetDB();
try
{
var this_ors = db.ors.Where(p => p.ID == id).FirstOrDefault();
if (this_ors.Row == 0)
{
var last_ors = db.ors.Where(p => p.allotment == this_ors.allotment && p.ID != this_ors.ID && p.Row != 0).OrderByDescending(p => p.Row).FirstOrDefault();
if (last_ors == null)
{
this_ors.Row = 1;
}
else
{
Int32 lastRow = last_ors.Row;
this_ors.Row = ++lastRow;
}
db.SaveChanges();
}
}
catch { }
}
//Constructor
public AccountManager(BudgetDB budget)
{
InitializeComponent();
_budget = budget;
LoadAccountsDGV();
}