/// <summary>
/// Fill grid
/// </summary>
private void FillGrid()
{
DbCommunicator db = DbCommunicator.CreateInstance(_connectionString);
try
{
log.Info("Get data to fill grid");
db.OpenConnection();
dataSet.Clear();
dataSet = db.ExecuteQuery(DBQuery.GET_TASK_DATA, "task");
dataGridView.DataSource = dataSet.Tables["task"];
}
catch (Exception ex)
{
log.Error("Exception message is : " + ex.Message);
throw ex;
}
finally
{
if (null != db)
{
db.CloseConnection();
}
}
}
public bool LoginAction(User u)
{
DbCommunicator dc = new DbCommunicator();
string query = $"SELECT * FROM Users WHERE UserName = '******' AND Password = '******'";
SqlDataReader data = dc.GetData(query);
while (data.Read())
{
u.Role = data.GetString(2);
if (u.Role.Equals("admin"))
{
new AdminForm().Show();
}
else if (u.Role.Equals("Salesman"))
{
SalesForm.user = u;
new SalesForm().Show();
}
dc.DbConnection.Close();
return(true);
}
u.ErrorMessage = "Login credentials do not match";
return(false);
}
public bool AddSalesman(User u)
{
DbCommunicator dc = new DbCommunicator();
string query = $"SELECT* FROM Users WHERE UserName = '******'";
SqlDataReader data = dc.GetData(query);
while (data.Read())
{
u.ErrorMessage = "This user is already exist";
dc.DbConnection.Close();
return(false);
}
dc.DbConnection.Close();
query = $"INSERT INTO Users VALUES ('{u.UserName}','{u.Password}','{u.Role}')";
int row = dc.WriteData(query);
dc.DbConnection.Close();
if (row > 0)
{
return(true);
}
else
{
return(false);
}
}
public bool SaveSales(List <SaleModel> sales)
{
int row = 0;
foreach (SaleModel ss in sales)
{
DbCommunicator dc1 = new DbCommunicator();
string query = $"INSERT INTO Sales VALUES (" +
$"{ss.PrductData.Id}, '{ss.Salesman.UserName}'," +
$"{ss.SalesPrice}, {ss.Profit}," +
$"'{ss.Date.Date}', {ss.Quantity})";
row += dc1.WriteData(query);
dc1.DbConnection.Close();
DbCommunicator dc2 = new DbCommunicator();
query = "UPDATE Products SET CurrentStock = " + (ss.PrductData.CurrentStock - ss.Quantity) + " WHERE ID = '" + ss.PrductData.Id + "'";
row += dc2.WriteData(query);
dc2.DbConnection.Close();
}
if (row > 0)
{
return(true);
}
else
{
return(false);
}
}
/// <summary>
/// Add new task.
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void AddTaskEventHanlder(object sender, EventArgs e)
{
DbCommunicator db = DbCommunicator.CreateInstance(_connectionString);
try
{
log.Info("Adding new task");
// Fields validation in add task form.
bool validationStatus = FieldValidation();
if (true == validationStatus)
{
log.Info("Successful validation");
db.OpenConnection();
int isReminder = (true == radioYes.Checked) ? 1 : 0;
string query = String.Format(DBQuery.INSERT_TASK, txtTitle.Text, txtItemDesc.Text,
DateTime.Parse(clderStartTime.Text).ToString("yyyy-MM-dd HH:mm:ss"),
DateTime.Parse(cldrEndTime.Text).ToString("yyyy-MM-dd HH:mm:ss"),
cmbStatus.SelectedItem,
isReminder,
txtNotificationTime.Text,
_name
);
log.Info("Query to add new task is : " + query);
int numberOfRows = db.ExecuteNonQuery(query);
if (0 < numberOfRows)
{
log.Info("New task added successfully with title " + txtTitle.Text);
MessageBox.Show("Task added successfully. You will be notified " +
txtNotificationTime.Text + " min(s) before the task",
this.Text, MessageBoxButtons.OK, MessageBoxIcon.Information);
UpdateGridDataEvent();
}
else
{
log.Info("Failed to add new task.");
MessageBox.Show("Failed to add todo task record in database.",
this.Text, MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
}
else
{
return;
}
}
catch (Exception ex)
{
log.Error("Exception message is : " + ex.Message);
MessageBox.Show("Failed to add todo task record in database.",
this.Text, MessageBoxButtons.OK, MessageBoxIcon.Error);
}
finally {
if (null != db)
{
db.CloseConnection();
}
}
}
/// <summary>
/// Update task event handler
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void UpdateTaskEventHanlder(object sender, EventArgs e)
{
DbCommunicator db = DbCommunicator.CreateInstance(_connectionString);
try
{
log.Info("Updating task ");
// Fields validation in add task form.
bool validationStatus = FieldValidation();
if (true == validationStatus)
{
db.OpenConnection();
int isReminder = (true == radioYes.Checked) ? 1 : 0;
string query = String.Format(DBQuery.UPDATE_TASK, txtTitle.Text, txtItemDesc.Text,
clderStartTime.Text,
cldrEndTime.Text,
cmbStatus.SelectedItem,
isReminder,
txtNotificationTime.Text,
_taskId);
log.Info("Query to update task is : " + query);
int numberOfRows = db.ExecuteNonQuery(query);
if (0 < numberOfRows)
{
MessageBox.Show("Task updated successfully. You will be notified " +
txtNotificationTime.Text + " min(s) before the task",
this.Text, MessageBoxButtons.OK, MessageBoxIcon.Information);
log.Info("Task updated successfully.");
// Fire event to upgrade grid data.
UpdateGridDataEvent();
}
else
{
log.Info("Failed to update task.");
MessageBox.Show("Failed to update todo task record in database.",
this.Text, MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
}
else
{
return;
}
}
catch (Exception ex)
{
MessageBox.Show("Failed to update todo task record in database.",
this.Text, MessageBoxButtons.OK, MessageBoxIcon.Error);
log.Info("Exception message is : " + ex.Message);
}
finally
{
if (null != db)
{
db.CloseConnection();
}
}
}
public bool UpdateProduct(Product p)
{
DbCommunicator dc = new DbCommunicator();
string query = $"UPDATE Products SET CurrentStock = {p.CurrentStock}, BuyingCost = {p.BuyingCost}, Price = {p.Price}, RestockDate = '{p.RestockDate}'" +
$" WHERE Id = {p.Id}";
int row = dc.WriteData(query);
dc.DbConnection.Close();
if (row > 0)
{
return(true);
}
else
{
return(false);
}
}
public bool AddProduct(Product pp)
{
DbCommunicator dc = new DbCommunicator();
string query = $"INSERT INTO Products VALUES (" +
$"'{pp.ProductName}', {pp.CurrentStock}," +
$"'{pp.Description}', {pp.BuyingCost}," +
$"{pp.Price}, '{pp.RestockDate}')";
int row = dc.WriteData(query);
if (row > 0)
{
return(true);
}
else
{
return(false);
}
}
public ReportModel GetReport(DateTime dt)
{
ReportModel rp = new ReportModel();
string query = $"SELECT Profit, SalesPrice FROM Sales WHERE Date = '{dt.Date}'";
DbCommunicator dc = new DbCommunicator();
SqlDataReader data = dc.GetData(query);
double profit = 0, total = 0;
while (data.Read())
{
profit += data.GetDouble(0);
total += data.GetDouble(1);
}
dc.DbConnection.Close();
rp.Profit = profit.ToString();
rp.Total = total.ToString();
return(rp);
}
/// <summary>
/// click event handler for register button.
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void btnRegister_Click(object sender, EventArgs e)
{
DbCommunicator db = DbCommunicator.CreateInstance(_connectionString);
try
{
log.Info("Clicked on Register button on New User form.");
//Validate Field on Add user form.
bool isValid = FieldsValidator();
if (true == isValid)
{
db.OpenConnection();
string query = String.Format(DBQuery.INSERT_USER, txtNewUsername.Text, txtNewPassword.Text, txtEmail.Text);
int numberofRows = db.ExecuteNonQuery(query);
if (0 < numberofRows)
{
log.Info("New user added successfully.");
MessageBox.Show(txtNewUsername.Text + " user added successfully.", this.Text, MessageBoxButtons.OK, MessageBoxIcon.Information);
}
else
{
log.Error("Failed to add new user");
MessageBox.Show("Failed to insert new user", this.Text, MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
else
{
return;
}
}
catch (Exception ex)
{
MessageBox.Show("Someting went wrong...", this.Text, MessageBoxButtons.OK, MessageBoxIcon.Error);
log.Error("Exception message is : " + ex.Message);
}
finally {
if (null != db)
{
db.CloseConnection();
}
}
}
public List <Product> GetAllProducts()
{
DbCommunicator dc = new DbCommunicator();
string query = "SELECT * FROM Products";
SqlDataReader data = dc.GetData(query);
List <Product> ppList = new List <Product>();
while (data.Read())
{
ppList.Add(new Product()
{
Id = data.GetInt32(0),
ProductName = data.GetString(1),
CurrentStock = data.GetInt32(2),
Description = data.GetString(3),
BuyingCost = data.GetDouble(4),
Price = data.GetDouble(5)
});
}
dc.DbConnection.Close();
return(ppList);
}
/// <summary>
/// Check login status
/// </summary>
public void Login()
{
DbCommunicator db = DbCommunicator.CreateInstance(_connectionString);
try
{
log.Info("Checking login for user.");
db.OpenConnection();
string query = String.Format(DBQuery.GET_USER_COUNT, txtUserName.Text, txtPassword.Text);
int numberOfRows = db.ExecuteScalar(query);
if (0 < numberOfRows)
{
log.Info("Successful login for user" + txtUserName.Text);
// On login success open form main.
LoginSuccessEvent();
}
else
{
log.Error("Login failed for " + txtUserName.Text);
MessageBox.Show("Invalid credentails. \nPlease check username/password.",
this.Text, MessageBoxButtons.OK, MessageBoxIcon.Error);
Application.Exit();
}
}
catch (Exception ex)
{
MessageBox.Show("Something went wrong",
this.Text, MessageBoxButtons.OK, MessageBoxIcon.Error);
log.Error("Exception message is : " + ex.Message);
Application.Exit();
}
finally {
if (null != db)
{
db.CloseConnection();
}
}
}
