private void DelPayroll(string id)
{
SqlConnection dbConnection = new SqlConnection(DBMethod.GetConnectionString());
// Create new SQL command
SqlCommand command = new SqlCommand(" Delete from tblPayroll where EmployeeId=" + id +
"and PaymentDate='" + payDate + "'", dbConnection);
SqlDataAdapter adapter = new SqlDataAdapter(command);
// Declare a DataTable object that will hold the return value
// Try to connect to the database, and use the adapter to fill the table
try
{
dbConnection.Open();
command.ExecuteNonQuery();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
dbConnection.Close();
}
updateHourIsPaid("0");
}
private void updateHourIsPaid(String isPaid)
{
SqlConnection dbConnection = new SqlConnection(DBMethod.GetConnectionString());
// Create new SQL command
SqlCommand command = new SqlCommand("update tblEmployeeHours set isPaid = " + isPaid +
" where EventID = (select EventId from tblEvent" +
" where EventDate between '" + dtpFormDate.Value.Date.ToShortDateString() +
"' and '" + dtpToDate.Value.Date.ToShortDateString() + "'); ", dbConnection);
SqlDataAdapter adapter = new SqlDataAdapter(command);
// Declare a DataTable object that will hold the return value
// Try to connect to the database, and use the adapter to fill the table
try
{
dbConnection.Open();
command.ExecuteNonQuery();
}
catch (Exception ex)
{
// MessageBox.Show("del->" + ex.Message);
}
finally
{
dbConnection.Close();
}
}
private void btnNewIngredintSave_Click(object sender, EventArgs e)
{
Ingredient newIngredient = new Ingredient();
if (IsValidIngredent())
{
newIngredient.Name = txtNewIngredintName.Text.Trim();
newIngredient.Unit = cmbUnit.Text.Trim();
newIngredient.IngredientTypeId = (short)Convert.ToDouble(DBMethod.GetSelectedItemID(cmbNewtype));
newIngredient.StoringNote = txtstoringNote.Text;
newIngredient.Unit = cmbUnit.Text.Trim();
DBConnector conn = new DBConnector(Classes.DBMethod.GetConnectionString());
RosieEntity.Connector = conn;
newIngredient.Create();
pnlNewIngredint.Visible = false;
pnlMain.Enabled = true;
cmbType.Items.Clear();
Classes.DBMethod.FillCombBox(AuntRosieEntities.IngredientType.GetAllIngredintType
(Classes.DBMethod.GetConnectionString()), cmbType);
AuntRosieApplication.Classes.ListItem itm = new AuntRosieApplication.Classes.ListItem();
Object obj = cmbType.Items[cmbNewtype.SelectedIndex];
itm = (Classes.ListItem)obj;
cmbType.SelectedItem = itm;
cmbName.SelectedItem = cmbName.Items[cmbName.Items.Count - 1];
}
}
private bool GetValidationStatus()
{
bool isValid = true;
if (txtFirstName.Text.Trim().Length < 3)
{
errSale.SetError(txtFirstName, "Customer Contact first name should be 3 letters at least");
isValid = false;
}
if (txtLastName.Text.Trim().Length < 3)
{
errSale.SetError(txtLastName, "Customer Contact last name should be 3 letters at least");
isValid = false;
}
if (!DBMethod.IsValidEmail(txtEmail.Text.Trim()))
{
errSale.SetError(txtEmail, "Entred Customer Email is not a valid email address");
isValid = false;
}
if (txtPhone.Text.Trim().Length != 14)
{
errSale.SetError(txtPhone, "Entred Phone is not a valid phone number");
isValid = false;
}
return(isValid);
}
private void btnSave_Click(object sender, EventArgs e)
{
if (isValidEntery())
{
try
{
InventoryIngredient InsertInventoryIngredient = new InventoryIngredient();
InsertInventoryIngredient.IngredientID = (long)Convert.ToDouble(DBMethod.GetSelectedItemID(cmbName));
InsertInventoryIngredient.SupplierId = (long)Convert.ToDouble(DBMethod.GetSelectedItemID(cmbSupplier));
InsertInventoryIngredient.ThisPaymentMethod = DBMethod.GetSelectedItemID(cmbPaymentMethod);
InsertInventoryIngredient.PurchaseDate = dtpPurchaseDate.Value;
InsertInventoryIngredient.ExpiryDate = dtpExpiryDate.Value;
InsertInventoryIngredient.Quantity = Convert.ToDouble(txtQuantity.Text.Trim());
InsertInventoryIngredient.Cost = Convert.ToDouble(txtCost.Text.Trim());
InsertInventoryIngredient.UnitPrice = Convert.ToDouble(txtCost.Text.Trim()) / Convert.ToDouble(txtQuantity.Text.Trim());
DBConnector conn = new DBConnector(Classes.DBMethod.GetConnectionString());
RosieEntity.Connector = conn;
InsertInventoryIngredient.Create();
clearData();
MessageBox.Show("The ingredient quantity has successfully stocked into the inventory", "Confirmation", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
catch (Exception ex)
{
//MessageBox.Show(ex.Message);
MessageBox.Show("Sorry! An internal error has happened", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
}
private void newIngredient()
{
Ingredient newIngredient = new Ingredient();
if (IsValidIngredent())
{
try
{
newIngredient.Name = txtNewIngredintName.Text.Trim();
newIngredient.IngredientTypeId = (short)Convert.ToDouble(DBMethod.GetSelectedItemID(cmbType));
newIngredient.StoringNote = txtstoringNote.Text;
newIngredient.Unit = cmbUnit.Text.Trim();
DBConnector conn = new DBConnector(Classes.DBMethod.GetConnectionString());
RosieEntity.Connector = conn;
newIngredient.Create();
pnlMain.Enabled = true;
MessageBox.Show("New ingredient has been inserted successfully", "Confirmation", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
catch (Exception ex)
{
// MessageBox.Show(ex.Message);
MessageBox.Show("Sorry! An internal error has happened", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
}
private void DelExpenses(string expId)
{
SqlConnection dbConnection = new SqlConnection(DBMethod.GetConnectionString());
// Create new SQL command
SqlCommand command = new SqlCommand("Delete from [tblMiscellaneousExpense] where " +
" ExpenseID=" + expId, dbConnection);
SqlDataAdapter adapter = new SqlDataAdapter(command);
// Declare a DataTable object that will hold the return value
// Try to connect to the database, and use the adapter to fill the table
try
{
dbConnection.Open();
command.ExecuteNonQuery();
}
catch (Exception ex)
{
MessageBox.Show("del" + ex.Message);
}
finally
{
dbConnection.Close();
}
}
private void btnAddPart_Click(object sender, EventArgs e)
{
if (isValidPartPayroll())
{
try
{ EmployeePay empPayt = new EmployeePay();
empPayt.EmployeeID = (long)Convert.ToDouble(DBMethod.GetSelectedItemID(cmbPartEmp));
empPayt.ThisPaymentMethod = DBMethod.GetSelectedItemID(cmbPaymentMethod);
empPayt.FromDate = dtpFormDate.Value;
empPayt.ToDate = dtpToDate.Value;
empPayt.Amount = Convert.ToDouble(lblPartAmount.Text.Trim());
empPayt.PaymentDate = DateTime.Today.Date;
DBConnector conn = new DBConnector(Classes.DBMethod.GetConnectionString());
RosieEntity.Connector = conn;
empPayt.Create();
FillPayGrid(sqlText);
updateHourIsPaid("1");
clearData(); }
catch (Exception ex)
{
//MessageBox.Show(ex.Message);
MessageBox.Show("You can't add this payroll record, The Employee's payroll has been registered ", "ERROR", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
}
private void btnAddFull_Click(object sender, EventArgs e)
{
if (isValidExpense())
{
AuntRosieEntities.Expense newExpenses = new AuntRosieEntities.Expense();
newExpenses.PaymentDate = DateTime.Parse(dtpFormDate.Value.Date.ToShortDateString());
newExpenses.PayValue = double.Parse(txtVal.Text.Trim().ToString());
newExpenses.ThisPaymentMethod = DBMethod.GetSelectedItemID(cmbPaymentMethod);
newExpenses.PayType = DBMethod.GetSelectedItemID(cmbExpensesType);
newExpenses.Note = txtNote.Text.Trim().ToString();
DBConnector conn = new DBConnector(Classes.DBMethod.GetConnectionString());
RosieEntity.Connector = conn;
try
{ newExpenses.Create();
ExpenseID = Expense.RetrieveMax(); }
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
if (chkIsEvent.Checked)
{
AuntRosieEntities.EventExpense newEventExpenses = new AuntRosieEntities.EventExpense();
newEventExpenses.ExpensID = ExpenseID;
newEventExpenses.EventID = long.Parse(DBMethod.GetSelectedItemID(cmbEventName));
conn = new DBConnector(Classes.DBMethod.GetConnectionString());
RosieEntity.Connector = conn;
newEventExpenses.Create();
}
clearData();
fillExpenseGrid();
}
}
public List <TResult> InvokeStoredProcedure <TParam, TResult>() where TParam : SPBase
{
string spName = DBMethod.GetSPName <TParam>();
List <TResult> list = Database.SqlQuery <TResult>(ParseSql(spName, null)).ToList();
return(list);
}
/// <summary>
/// Exec a SP querey that will return elements of the given type.
/// </summary>
/// <typeparam name="T1">the return elements of the given type</typeparam>
/// <typeparam name="T2">the SP type</typeparam>
/// <param name="sp">the sp entity</param>
/// <returns></returns>
/// <remarks></remarks>
public List <TResult> InvokeStoredProcedure <TParam, TResult>(TParam sp) where TParam : SPBase
{
string spName = DBMethod.GetSPName <TParam>();
SqlParameter[] paras = DBMethod.GetSPParameters(sp);
List <TResult> list = Database.SqlQuery <TResult>(ParseSql(spName, paras), paras).ToList();
return(list);
}
private void DelSaleProduct(string eventProductId, string salQuantity)
{
SqlConnection dbConnection = new SqlConnection(DBMethod.GetConnectionString());
// Create new SQL command
SqlCommand command = new SqlCommand("Delete from tblSaleProducts where EventProductId=" + eventProductId +
" and SaleID=" + SaleID, dbConnection);
SqlDataAdapter adapter = new SqlDataAdapter(command);
// Declare a DataTable object that will hold the return value
// Try to connect to the database, and use the adapter to fill the table
try
{
dbConnection.Open();
command.ExecuteNonQuery();
}
catch (Exception ex)
{
MessageBox.Show("del" + ex.Message);
}
finally
{
dbConnection.Close();
}
///
dbConnection = new SqlConnection(DBMethod.GetConnectionString());
// Create new SQL command
command = new SqlCommand(" Update [tblEventProduct] set Quantity= Quantity+ " + salQuantity + "," +
" soldQuantity= soldQuantity-" + salQuantity + " where EventProductID=" + eventProductId, dbConnection);
adapter = new SqlDataAdapter(command);
// Declare a DataTable object that will hold the return value
// Try to connect to the database, and use the adapter to fill the table
try
{
dbConnection.Open();
command.ExecuteNonQuery();
}
catch (Exception ex)
{
MessageBox.Show("update" + ex.Message);
}
finally
{
dbConnection.Close();
}
fillSaleGrid();
}
/// <summary>
/// Exec a SP without return
/// </summary>
/// <typeparam name="T">elements of the given type</typeparam>
/// <param name="sp">the sp entity</param>
/// <remarks></remarks>
public void InvokeStoredProcedureNonQuery <T>(T sp) where T : SPBase
{
CheckSPArgument(sp);
string spName = DBMethod.GetSPName <T>();
SqlParameter[] paras = DBMethod.GetSPParameters(sp);
this.Database.ExecuteSqlCommand(ParseSql(spName, paras), paras);
// set the return output value
DBMethod.SetSPModelValueFormParameters(sp, paras);
}
private void cmbEventName_SelectedIndexChanged(object sender, EventArgs e)
{
if (cmbEventName.SelectedItem != null)
{
DBConnector conn = new DBConnector(Classes.DBMethod.GetConnectionString());
RosieEntity.Connector = conn;
RosieEvent rosieEvent = RosieEvent.Retrieve(long.Parse(DBMethod.GetSelectedItemID(cmbEventName)));
EventLocation locEvent = EventLocation.Retrieve(rosieEvent.LocationId);
lblEventLocation.Text += locEvent.Address.ToString();
}
}
private void FillPayGrid(string sql)
{
string connectionString = DBMethod.GetConnectionString();
SqlConnection connection = new SqlConnection(connectionString);
SqlDataAdapter dataadapter = new SqlDataAdapter(sql, connection);
DataSet ds = new DataSet();
connection.Open();
dataadapter.Fill(ds, "FullPayroll");
connection.Close();
grdPayroll.DataSource = ds;
grdPayroll.DataMember = "FullPayroll";
}
private void FillGridStock(string sql)
{
string connectionString = DBMethod.GetConnectionString();
SqlConnection connection = new SqlConnection(connectionString);
SqlDataAdapter dataadapter = new SqlDataAdapter(sql, connection);
DataSet ds = new DataSet();
connection.Open();
dataadapter.Fill(ds, "Stock_query");
connection.Close();
grdStock.DataSource = ds;
grdStock.DataMember = "Stock_query";
}
private void GetPaymentDate()
{
SqlConnection dbConnection = new SqlConnection(DBMethod.GetConnectionString());
// Create new SQL command
SqlCommand command = new SqlCommand("SELECT DISTINCT PaymentDate AS Expr1 FROM [tblFullTimePayroll]", dbConnection);
SqlDataAdapter adapter = new SqlDataAdapter(command);
// Declare a DataTable object that will hold the return value
DataTable dt = new DataTable();
// Try to connect to the database, and use the adapter to fill the table
try
{
dbConnection.Open();
adapter.Fill(dt);
if (dt.Rows.Count == 0)
{
MessageBox.Show("No previous payroll are registered", "Information", MessageBoxButtons.OK, MessageBoxIcon.Information);
radNew.Checked = true;
radExists.Checked = false;
}
else
{
cmbPaymentDate.Enabled = true;
cmbPaymentDate.Focus();
}
if (dt != null)
{
foreach (DataRow row in dt.Rows)
{
DateTime date = DateTime.Parse(row[0].ToString());
AuntRosieApplication.Classes.ListItem itm = new AuntRosieApplication.Classes.ListItem();
itm.name = date.Date.ToShortDateString();
itm.id = date.Date.ToShortDateString();
cmbPaymentDate.Items.Add((Object)itm);
}
}
}
catch (Exception ex)
{
}
finally
{
dbConnection.Close();
}
}
private bool GetValidationStatus()
{
bool isValid = true;
if (txtName.Text.Trim().Length < 3)
{
errNmae.SetError(txtName, "Supplier name should be 3 letters at least");
isValid = false;
}
if (txtSupplierFirstName.Text.Trim().Length < 3)
{
errFname.SetError(txtSupplierFirstName, "Supplier Contact first name should be 3 letters at least");
isValid = false;
}
if (txtSupplierLastName.Text.Trim().Length < 3)
{
errLname.SetError(txtSupplierLastName, "Supplier Contact last name should be 3 letters at least");
isValid = false;
}
if (!DBMethod.IsValidEmail(txtEmail.Text.Trim()))
{
errNmae.SetError(txtEmail, "Entred Supplier Email is not a valid email address");
isValid = false;
}
if (txtPhone.Text.Trim().Length != 14)
{
errPhone.SetError(txtPhone, "Entred Phone is not a valid phone number");
isValid = false;
}
if (txtCity.Text.Trim().Length < 3)
{
errLname.SetError(txtCity, " City should be 3 letters at least");
isValid = false;
}
if (cmbProvinces.Text.Trim().Length == 0)
{
errProvince.SetError(cmbProvinces, " Province could not be empty");
isValid = false;
}
if (txtPostalCode.Text.Trim().Length != 6)
{
errPostal.SetError(txtPostalCode, "Entred Postalcode is not a valid postalcode");
isValid = false;
}
return(isValid);
}
private void btnClear_Click(object sender, EventArgs e)
{
SqlConnection dbConnection = new SqlConnection(DBMethod.GetConnectionString());
// Create new SQL command
SqlCommand command = new SqlCommand("Delete from tblSaleProducts where SaleID=" + SaleID, dbConnection);
SqlDataAdapter adapter = new SqlDataAdapter(command);
try
{
dbConnection.Open();
command.ExecuteNonQuery();
}
catch (Exception ex)
{
MessageBox.Show("del" + ex.Message);
}
finally
{
dbConnection.Close();
}
dbConnection = new SqlConnection(DBMethod.GetConnectionString());
// Create new SQL command
command = new SqlCommand("Delete from tblSale where SaleID=" + SaleID, dbConnection);
adapter = new SqlDataAdapter(command);
try
{
dbConnection.Open();
command.ExecuteNonQuery();
}
catch (Exception ex)
{
MessageBox.Show("del" + ex.Message);
}
finally
{
dbConnection.Close();
}
fillSaleGrid();
clearData();
}
private void cmbName_SelectedIndexChanged(object sender, EventArgs e)
{
try
{
DBConnector conn = new DBConnector(Classes.DBMethod.GetConnectionString());
RosieEntity.Connector = conn;
Ingredient ingredent = Ingredient.Retrieve((long)Convert.ToDouble(DBMethod.GetSelectedItemID(cmbName)));
txtNewIngredintName.Text = ingredent.Name;
txtstoringNote.Text = ingredent.StoringNote;
}catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
private void chkAllIngrdient_CheckedChanged(object sender, EventArgs e)
{
if (chkAllIngrdient.Checked)
{
cmbName.Items.Clear();
Classes.DBMethod.FillCombBox(AuntRosieEntities.Ingredient.GetAllIngredintsByType
(Classes.DBMethod.GetConnectionString(), DBMethod.GetSelectedItemID(cmbType)), cmbName);
string wherSQLCmmand = " and tblIngredient.IngredientTypeID= " + DBMethod.GetSelectedItemID(cmbType) + " ";
ReportSql = SelectSQLCmmand + wherSQLCmmand + OrderSQLCmmand;
FillGridStock(ReportSql);
cmbName.Enabled = true;
chkAllIngrdient.Enabled = true;
GetQuantityType();
LblQuantity.Text = "";
}
}
private void btnDestroy_Click(object sender, EventArgs e)
{
string updateSQLText = "Update [tblIngredientInventory] Set tblIngredientInventory.Quantity=0 " +
"where (tblIngredientInventory.Quantity > 0) " + expierdWhere;
if (InventoryIngredient.RidOutExpierd(DBMethod.GetConnectionString(), updateSQLText))
{
MessageBox.Show("Selected expired items have been rid out", "Confirmation"
, MessageBoxButtons.OK, MessageBoxIcon.Information);
chkExpierd_CheckedChanged(sender, e);
}
else
{
MessageBox.Show("Sorry! An internal error has happened", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
private void clearData()
{
cmbPaymentMethod.Enabled = true;
lblTotalNumberOfItem.Text = "";
LblTotalPrice.Text = "";
lblTax.Text = "";
lblTotalVallue.Text = "";
ndpQuanitity.Value = 1;
FillProduct(DBMethod.GetSelectedItemID(cmbEventName));
cmbProduct.SelectedItem = null;
cmbCustomerName.SelectedItem = null;
lblItemPrice.Text = "";
cmbPaymentMethod.SelectedItem = null;
SaleID = -1;
fillSaleGrid();
}
private void calcRevenue()
{
SqlConnection dbConnection = new SqlConnection(DBMethod.GetConnectionString());
// Create new SQL command
string sql = "SELECT SUM(tblEventProduct.soldQuantity * tblProductItem.Price) AS SalesPrice " +
" FROM tblEventProduct INNER JOIN " +
" tblProduction ON tblEventProduct.ProductionID = tblProduction.ProductionID INNER JOIN " +
" tblProductItem ON tblProduction.ProductItemID = tblProductItem.ProductItemID INNER JOIN " +
" tblEvent ON tblEventProduct.EventID = tblEvent.EventID";
if (!chkAlltime.Checked)
{
sql = sql + " WHERE(tblEvent.EventDate BETWEEN'" + dtpFormDate.Value.Date.ToShortDateString() + "' AND '" + dtpToDate.Value.Date.ToShortDateString() + "')";
}
SqlCommand command = new SqlCommand(sql, dbConnection);
SqlDataAdapter adapter = new SqlDataAdapter(command);
// Declare a DataTable object that will hold the return value
DataTable dt = new DataTable();
// Try to connect to the database, and use the adapter to fill the table
try
{
dbConnection.Open();
adapter.Fill(dt);
if (dt.Rows.Count == 0)
{
lblITotRevenue.Text = "$0.00";
}
if (dt != null)
{
foreach (DataRow row in dt.Rows)
{
lblITotRevenue.Text = double.Parse(row[0].ToString()).ToString("C");
}
}
}
catch (Exception ex)
{
}
}
private void GetQuantityType()
{
SqlConnection dbConnection = new SqlConnection(DBMethod.GetConnectionString());
// Create new SQL command
SqlCommand command = new SqlCommand("SELECT SUM(tblIngredientInventory.Quantity) AS Expr1, tblIngredient.Unit, tblIngredientType.IngredientTypeID" +
" FROM tblIngredientInventory INNER JOIN " +
" tblIngredient ON tblIngredientInventory.IngredientID = tblIngredient.IngredientID INNER JOIN " +
" tblIngredientType ON tblIngredient.IngredientTypeID = tblIngredientType.IngredientTypeID " +
"GROUP BY tblIngredient.Unit, tblIngredientType.IngredientTypeID" +
" HAVING(tblIngredientType.IngredientTypeID = " + DBMethod.GetSelectedItemID(cmbType) + ") ", dbConnection);
SqlDataAdapter adapter = new SqlDataAdapter(command);
// Declare a DataTable object that will hold the return value
DataTable dt = new DataTable();
// Try to connect to the database, and use the adapter to fill the table
try
{
dbConnection.Open();
adapter.Fill(dt);
if (dt.Rows.Count == 0)
{
lblQuantityType.Text = "0.00";
}
if (dt != null)
{
foreach (DataRow row in dt.Rows)
{
lblQuantityType.Text = row[0].ToString() + " " + row[1].ToString();
}
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
dbConnection.Close();
}
}
private void calcExp()
{
string sql = "SELECT SUM(Amount) AS Expr1 FROM (SELECT PaymentDate, Amount FROM tblFullTimePayroll " +
" UNION ALL SELECT PaymentDate, Amount FROM tblPayroll " +
" UNION ALL SELECT ExpensePayDate, ExpenseValue FROM tblMiscellaneousExpense " +
" UNION ALL SELECT PurchaseDate, Cost FROM tblIngredientInventory) AS derivedtbl_1 ";
if (!chkAlltime.Checked)
{
sql += " WHERE(PaymentDate BETWEEN'" + dtpFormDate.Value.Date.ToShortDateString() + "' AND '" + dtpToDate.Value.Date.ToShortDateString() + "')";
}
SqlConnection dbConnection = new SqlConnection(DBMethod.GetConnectionString());
// Create new SQL command
SqlCommand command = new SqlCommand(sql, dbConnection);
SqlDataAdapter adapter = new SqlDataAdapter(command);
// Declare a DataTable object that will hold the return value
DataTable dt = new DataTable();
// Try to connect to the database, and use the adapter to fill the table
try
{
dbConnection.Open();
adapter.Fill(dt);
if (dt.Rows.Count == 0)
{
lblITotExpenses.Text = "$0.00";
}
if (dt != null)
{
foreach (DataRow row in dt.Rows)
{
lblITotExpenses.Text = double.Parse(row[0].ToString()).ToString("C");
}
}
}
catch (Exception ex)
{
}
}
private void FillPayGridPart()
{
string sql = " SELECT tblPayroll.PaymentDate, tblPayroll.Amount, tblEmployee.EmployeeID, tblPayroll.FromDate, tblPayroll.ToDate," +
" CASE WHEN PaymentMethod = 'r' THEN 'Credit' ELSE CASE WHEN PaymentMethod = 'c' THEN 'Cash' ELSE CASE WHEN PaymentMethod = 'd' THEN 'Debit' END END END AS PaymentMethod" +
" FROM tblEmployee INNER JOIN " +
" tblPayroll ON tblEmployee.EmployeeID = tblPayroll.EmployeeID WHERE tblPayroll.EmployeeID = " + DBMethod.GetSelectedItemID(cmbEmployeeName);;
string connectionString = DBMethod.GetConnectionString();
SqlConnection connection = new SqlConnection(connectionString);
SqlDataAdapter dataadapter = new SqlDataAdapter(sql, connection);
DataSet ds = new DataSet();
connection.Open();
dataadapter.Fill(ds, "PayrollPart");
connection.Close();
grdPayrollPart.DataSource = ds;
grdPayrollPart.DataMember = "PayrollPart";
}
private void calcSales()
{
SqlConnection dbConnection = new SqlConnection(DBMethod.GetConnectionString());
// Create new SQL command
SqlCommand command = new SqlCommand("SELECT SUM(tblEventProduct.Quantity* tblProductItem.Price) AS SalesPrice, tblEventProduct.EventID" +
" FROM tblEventProduct INNER JOIN " +
" tblProduction ON tblEventProduct.ProductionID = tblProduction.ProductionID INNER JOIN " +
" tblProductItem ON tblProduction.ProductItemID = tblProductItem.ProductItemID " +
" GROUP BY tblEventProduct.EventID " +
" HAVING(tblEventProduct.EventID = " + DBMethod.GetSelectedItemID(cmbEventName) + ")", dbConnection);
SqlDataAdapter adapter = new SqlDataAdapter(command);
// Declare a DataTable object that will hold the return value
DataTable dt = new DataTable();
// Try to connect to the database, and use the adapter to fill the table
try
{
dbConnection.Open();
adapter.Fill(dt);
if (dt.Rows.Count == 0)
{
lblRevenue.Text = "$0.00";
lblIngrdeintCost.Text = "$0.00";
}
if (dt != null)
{
foreach (DataRow row in dt.Rows)
{
lblRevenue.Text = double.Parse(row[0].ToString()).ToString("C");
lblIngrdeintCost.Text = (double.Parse(row[0].ToString()) * 0.28).ToString("C");
}
}
}
catch (Exception ex)
{
}
}
private void addNewSaleProduct()
{
SaleProduct newSaleProduct = new SaleProduct();
newSaleProduct.SaleId = SaleID;
newSaleProduct.SaleQuantity = int.Parse(ndpQuanitity.Value.ToString());
newSaleProduct.SalePrice = Double.Parse(lblItemPrice.Text.Substring(1));
newSaleProduct.EventProductId = long.Parse(DBMethod.GetSelectedItemID(cmbProduct));
DBConnector conn = new DBConnector(Classes.DBMethod.GetConnectionString());
RosieEntity.Connector = conn;
newSaleProduct.Create();
//
EventProduct eventProduct = new EventProduct();
conn = new DBConnector(Classes.DBMethod.GetConnectionString());
RosieEntity.Connector = conn;
eventProduct.Id = long.Parse(DBMethod.GetSelectedItemID(cmbProduct));
eventProduct.UpdateQuantity(null, ndpQuanitity.Value.ToString());
}
private void FillPayGridFull()
{
string sql = "SELECT EmployeeID, PaymentDate, Month, Amount, Year, " +
" CASE WHEN tblFullTimePayroll.PaymentMethod = 'r' THEN 'Credit' ELSE CASE WHEN tblFullTimePayroll.PaymentMethod = 'c' THEN 'Cash' ELSE CASE WHEN tblFullTimePayroll.PaymentMethod = 'd' THEN 'debit' END END END," +
" CASE WHEN BiWeekNo = 1 THEN 'First 2 weeks' ELSE CASE WHEN BiWeekNo = 2 THEN 'Last 2 Weeks' END END" +
" AS BiWeekNo FROM tblFullTimePayroll where EmployeeID = " + DBMethod.GetSelectedItemID(cmbEmployeeName);
string connectionString = DBMethod.GetConnectionString();
SqlConnection connection = new SqlConnection(connectionString);
SqlDataAdapter dataadapter = new SqlDataAdapter(sql, connection);
DataSet ds = new DataSet();
connection.Open();
dataadapter.Fill(ds, "PayrollFull");
connection.Close();
grdPayrollFull.DataSource = ds;
grdPayrollFull.DataMember = "PayrollFull";
}
