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();
}
}
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 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 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 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 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 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 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 cmbSupplierName_SelectedIndexChanged(object sender, EventArgs e)
{
DBConnector conn = new DBConnector(Classes.DBMethod.GetConnectionString());
RosieEntity.Connector = conn;
Supplier supplier = Supplier.Retrieve(DBMethod.GetSelectedItemID(cmbSupplierName));
txtName.Text = supplier.Name;
txtSupplierFirstName.Text = supplier.ContactFirstName;
txtSupplierLastName.Text = supplier.ContactLasttName;
txtPhone.Text = supplier.Phone;
txtEmail.Text = supplier.Email;
txtCity.Text = supplier.City;
AuntRosieApplication.Classes.ListItem itm2 = new AuntRosieApplication.Classes.ListItem();
itm2.id = supplier.Province;
itm2.name = Classes.DBMethod.GetProvinceName(itm2.id);
cmbProvinces.SelectedItem = itm2;
cmbProvinces.Text = Classes.DBMethod.GetProvinceName(itm2.id);
txtStreet.Text = supplier.Street;
txtPostalCode.Text = supplier.PostalCode;
}
private void FillHours()
{
SqlConnection dbConnection = new SqlConnection(DBMethod.GetConnectionString());
// Create new SQL command
SqlCommand command = new SqlCommand("SELECT SUM(Hours) AS exp1 " +
" FROM(SELECT tblEmployeeHours.EmployeeID, tblEmployeeHours.EventID, tblEmployeeHours.Hours," +
" tblEmployeeHours.IsPaid, tblEvent.EventID AS Expr1, tblEvent.EventName, tblEvent.LocationID, tblEvent.EventDate, " +
" tblEvent.EventType FROM tblEmployeeHours INNER JOIN tblEvent ON tblEmployeeHours.EventID = tblEvent.EventID " +
" WHERE(tblEvent.EventID =" + DBMethod.GetSelectedItemID(cmbEventName) + ")) AS derivedtbl_1" +
" GROUP BY EventID HAVING(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)
{
lblHour.Text = "0.0";
}
if (dt != null)
{
foreach (DataRow row in dt.Rows)
{
lblHour.Text = (double.Parse(row[0].ToString()) * HoursWages).ToString("C");
}
}
}
catch (Exception ex)
{
}
}
private void addSale()
{
if (grdSale.Rows.Count == 0)
{
Sale newSale = new Sale();
newSale.PaymentMethod = DBMethod.GetSelectedItemID(cmbPaymentMethod);
newSale.SaleDate = DateTime.Today;
if (chkGuest.Checked)
{
newSale.CustomerId = 0;
}
else
{
newSale.CustomerId = long.Parse(DBMethod.GetSelectedItemID(cmbCustomerName));
}
DBConnector conn1 = new DBConnector(Classes.DBMethod.GetConnectionString());
RosieEntity.Connector = conn1;
newSale.Create();
SaleID = Sale.RetrieveMax();
}
try
{
addNewSaleProduct();
}
catch (Exception ex)
{
for (int i = 0; i < grdSale.Rows.Count; i++)
{
if (grdSale.Rows[i].Cells[0].Value.ToString() == DBMethod.GetSelectedItemID(cmbProduct))
{
DelSaleProduct(grdSale.Rows[i].Cells[0].Value.ToString(),
grdSale.Rows[i].Cells[3].Value.ToString());
addNewSaleProduct();
}
}
}
fillSaleGrid();
}
private void cmbfullEmp_SelectedIndexChanged(object sender, EventArgs e)
{
if (cmbfullEmp.SelectedItem != null)
{
try
{
DBConnector conn = new DBConnector(Classes.DBMethod.GetConnectionString());
RosieEntity.Connector = conn;
Employee employee = Employee.Retrieve(DBMethod.GetSelectedItemID(cmbfullEmp));
double sal = employee.Salary;
lblEmploymentDate.Text = employee.EmploymentDate.ToShortDateString();
btnAddFull.Enabled = true;
if (isValidFullPayroll())
{
if (employee.EmploymentDate.Year == int.Parse(cmbYear.SelectedItem.ToString()))
{
if (employee.EmploymentDate.Month > int.Parse(DBMethod.GetSelectedItemID(cmbMonth)))
{
MessageBox.Show("The employee has no payroll , The employment date is after this month",
"Month Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
btnAddFull.Enabled = false;
}
}
else if (employee.EmploymentDate.Year > int.Parse(cmbYear.SelectedItem.ToString()))
{
MessageBox.Show("The employee has no payroll , The employment date is after this month",
"Year Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
btnAddFull.Enabled = false;
}
}
double amount = (sal / 26);
lblFullAmount.Text = String.Format("{0:0.0}", amount.ToString("N"));
}
catch (Exception ex)
{
MessageBox.Show(ex.Message.ToString());
}
}
}
private void cmbProduct_SelectedIndexChanged(object sender, EventArgs e)
{
try
{
DBConnector conn = new DBConnector(Classes.DBMethod.GetConnectionString());
RosieEntity.Connector = conn;
EventProduct product = EventProduct.Retrieve(short.Parse(DBMethod.GetSelectedItemID(cmbProduct)));
ndpQuanitity.Maximum = product.Quantity - product.SoldQuantity;
//
conn = new DBConnector(Classes.DBMethod.GetConnectionString());
RosieEntity.Connector = conn;
Production production = Production.Retrieve(product.ProductionId);
//
conn = new DBConnector(Classes.DBMethod.GetConnectionString());
RosieEntity.Connector = conn;
ProductItem productItem = ProductItem.Retrieve(production.ProductItemID);
lblItemPrice.Text = productItem.Price.ToString("C");
ndpQuanitity.Value = 1;
}
catch (Exception ex)
{ }
}
private void cmbName_SelectedIndexChanged(object sender, EventArgs e)
{
try
{
DBConnector conn = new DBConnector(Classes.DBMethod.GetConnectionString());
RosieEntity.Connector = conn;
Employee employee = Employee.Retrieve(DBMethod.GetSelectedItemID(cmbName));
txtFirstName.Text = employee.FirstName;
txtLastName.Text = employee.LastName;
txtPhone.Text = employee.Phone;
txtEmail.Text = employee.Email;
txtCity.Text = employee.City;
AuntRosieApplication.Classes.ListItem itm2 = new AuntRosieApplication.Classes.ListItem();
itm2.id = employee.Province;
itm2.name = Classes.DBMethod.GetProvinceName(itm2.id);
cmbProvinces.SelectedItem = itm2;
cmbProvinces.Text = Classes.DBMethod.GetProvinceName(itm2.id);
txtStreet.Text = employee.Street;
txtPostalCode.Text = employee.PostalCode;
if (employee.Type == "F")
{
radFullTime.Checked = true;
txtsalary.Text = employee.Salary.ToString();
dtpEmploymentDate.Value = employee.EmploymentDate.Date;
}
else
{
txtsalary.Text = "";
dtpEmploymentDate.Value = DateTime.Today.Date;
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message.ToString());
}
}
private void btnAddFull_Click(object sender, EventArgs e)
{
if (isValidFullPayroll())
{
try
{
EmployeeFullPay empPayt = new EmployeeFullPay();
empPayt.EmployeeID = (long)Convert.ToDouble(DBMethod.GetSelectedItemID(cmbfullEmp));
empPayt.ThisPaymentMethod = DBMethod.GetSelectedItemID(cmbPaymentMethod);
empPayt.MonthNo = int.Parse(DBMethod.GetSelectedItemID(cmbMonth));
if (radFirst.Checked)
{
empPayt.BiWeekNo = 1;
}
else
{
empPayt.BiWeekNo = 1;
}
empPayt.YearNo = int.Parse(cmbYear.SelectedItem.ToString());
empPayt.Amount = Convert.ToDouble(lblFullAmount.Text.Trim());
empPayt.PaymentDate = DateTime.Today.Date;
DBConnector conn = new DBConnector(Classes.DBMethod.GetConnectionString());
RosieEntity.Connector = conn;
empPayt.Create();
FillPayGrid(sqlText);
clearData();
}
catch (Exception ex)
{
//MessageBox.Show(ex.Message);
MessageBox.Show("The Employee's has been pied for the selected Bi-week", "Information", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
}
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 cmbName_SelectedIndexChanged(object sender, EventArgs e)
{
try
{
string wherSQLCmmand = " and tblIngredientInventory.IngredientID= " + DBMethod.GetSelectedItemID(cmbName) + " ";
ReportSql = SelectSQLCmmand + wherSQLCmmand + OrderSQLCmmand;
FillGridStock(ReportSql);
chkAllIngrdient.Checked = false;
GetQuantity();
}
catch (Exception ex)
{
}
}
private void chkExpierd_CheckedChanged(object sender, EventArgs e)
{
if (chkExpierd.Checked)
{
string whereExpirySQLSelect = " and tblIngredientInventory.ExpiryDate < '" + DateTime.Today.Date.ToShortDateString() + "' ";
if (chkAllTypies.Checked)
{
// MessageBox.Show(SelectSQLCmmand + whereExpirySQLSelect + OrderSQLCmmand);
ReportSql = SelectSQLCmmand + whereExpirySQLSelect + OrderSQLCmmand;
FillGridStock(ReportSql);
expierdWhere = whereExpirySQLSelect;
}
else
{
if (chkAllIngrdient.Checked)
{
string wherSQLCmmand = " and tblIngredientInventory.IngredientID= " + DBMethod.GetSelectedItemID(cmbType) + " ";
ReportSql = SelectSQLCmmand + wherSQLCmmand + whereExpirySQLSelect + OrderSQLCmmand;
FillGridStock(ReportSql);
expierdWhere = wherSQLCmmand + whereExpirySQLSelect;
GetQuantity();
}
else
{
string wherSQLCmmand = " and tblIngredient.IngredientTypeID= " + DBMethod.GetSelectedItemID(cmbName) + " ";
FillGridStock(SelectSQLCmmand + wherSQLCmmand + whereExpirySQLSelect + OrderSQLCmmand);
expierdWhere = wherSQLCmmand + whereExpirySQLSelect;
GetQuantityType();
}
}
if (grdStock.Rows.Count == 0)
{
MessageBox.Show("No Expired items in the inventory", "Information", MessageBoxButtons.OK, MessageBoxIcon.Information);
btnDestroy.Enabled = false;
}
else
{
btnDestroy.Enabled = true;
btnDestroy.Focus();
}
}
else
{
if (chkAllTypies.Checked)
{
// MessageBox.Show(SelectSQLCmmand + whereExpirySQLSelect + OrderSQLCmmand);
ReportSql = SelectSQLCmmand + OrderSQLCmmand;
FillGridStock(ReportSql);
}
else
{
if (chkAllIngrdient.Checked)
{
string wherSQLCmmand = " and tblIngredientInventory.IngredientID= " + DBMethod.GetSelectedItemID(cmbName) + " ";
ReportSql = SelectSQLCmmand + wherSQLCmmand + OrderSQLCmmand;
FillGridStock(ReportSql);
GetQuantity();
}
else
{
string wherSQLCmmand = " and tblIngredient.IngredientTypeID= " + DBMethod.GetSelectedItemID(cmbType) + " ";
ReportSql = SelectSQLCmmand + wherSQLCmmand + OrderSQLCmmand;
FillGridStock(ReportSql);
GetQuantityType();
}
}
}
}
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 cmbPartEmp_SelectedIndexChanged(object sender, EventArgs e)
{
if (cmbPartEmp.SelectedItem != null)
{
txtwage.Text = "";
lblHour.Text = "";
lblPartAmount.Text = "";
if (lblDays.Text.Length > 0)
{
txtwage.Focus();
SqlConnection dbConnection = new SqlConnection(DBMethod.GetConnectionString());
// Create new SQL command
SqlCommand command = new SqlCommand("SELECT SUM(Hours) AS exp1 " +
" FROM(SELECT tblEmployeeHours.EmployeeID, tblEmployeeHours.EventID, tblEmployeeHours.Hours," +
" tblEmployeeHours.IsPaid, tblEvent.EventID AS Expr1, tblEvent.EventName, tblEvent.LocationID, tblEvent.EventDate, " +
" tblEvent.EventType FROM tblEmployeeHours INNER JOIN tblEvent ON tblEmployeeHours.EventID = tblEvent.EventID " +
" WHERE(tblEvent.EventDate BETWEEN '" + dtpFormDate.Value.Date.ToShortDateString() + "' AND '" +
dtpToDate.Value.Date.ToShortDateString() + "' )) AS derivedtbl_1" +
" GROUP BY EmployeeID HAVING(EmployeeID = " + DBMethod.GetSelectedItemID(cmbPartEmp) + ")", 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);
btnAddPart.Enabled = true;
if (dt.Rows.Count == 0)
{
btnAddPart.Enabled = false;
lblHour.Text = "0.0";
}
if (dt != null)
{
foreach (DataRow row in dt.Rows)
{
lblHour.Text = String.Format("{0:0.0}", row[0].ToString());
}
}
}
catch (Exception ex)
{
// MessageBox.Show(ex.Message);
}
finally
{
dbConnection.Close();
}
}
else
{
MessageBox.Show("From Date should be before To Date ", "ERROR", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
if (lblHour.Text.Trim() == "0")
{
pnlPartAddPayroll.Enabled = false;
}
else
{
pnlPartAddPayroll.Enabled = true;
}
}
}
private void cmbType_SelectedIndexChanged(object sender, EventArgs e)
{
cmbName.Items.Clear();
Classes.DBMethod.FillCombBox(AuntRosieEntities.Ingredient.GetAllIngredintsByType
(Classes.DBMethod.GetConnectionString(), DBMethod.GetSelectedItemID(cmbType)), cmbName);
cmbName.Enabled = true;
cmbName.Focus();
}
private void cmbPaymentMethod_SelectedIndexChanged(object sender, EventArgs e)
{
sqlWherePayMethod = "(PaymentMethod='" + DBMethod.GetSelectedItemID(cmbPaymentMethod) + "')";
}
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 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";
}
