private void dgvStaffMembers_CellContentClick(object sender, DataGridViewCellEventArgs e)
{
try
{
if (dgvStaffMembers.Columns[e.ColumnIndex].Name == "Remove" && string.IsNullOrEmpty(dgvStaffMembers.Columns[e.ColumnIndex].HeaderText))
{
DialogResult dr = MessageBox.Show("Do you really want to delete?", "Staff", MessageBoxButtons.YesNo, MessageBoxIcon.Question);
if (dr == DialogResult.Yes)
{
Params = new ArrayList();
Params.Add(dgvStaffMembers.Rows[dgvStaffMembers.CurrentCell.RowIndex].Cells["Id"].Value.ToString());
sql = new SQLCache(Params);
int status = dbHelper.ExecuteNonQuery(sql.GetSQL("Delete_Staff"));
if (status > 0)
{
MessageBox.Show("Staff removed successfully", "Staff", MessageBoxButtons.OK, MessageBoxIcon.Information);
FillDataGrid();
ResetValues();
}
}
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message.ToString(), "Staff NIC", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
private void GetItemCode()
{
try
{
Params = new ArrayList();
Params.Add(SelectItemId());
sql = new SQLCache(Params);
Object value = dbHelper.ExecuteScalar(sql.GetSQL("GetItemCode"));
if (value != null)
{
if (value != null && !string.IsNullOrEmpty(value.ToString()))
{
string ItemCode = value.ToString();
int Code = Convert.ToInt32(ItemCode.Substring(ItemCode.Length - 3));
Code += 1;
txtItemCode.Text = ItemCode.Substring(0, 4) + Code.ToString().PadLeft(3, '0');
}
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message.ToString(), "Billing", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
private void BindItemInformation()
{
int ItemID = Convert.ToInt32(cmbItemName.SelectedValue.ToString());
Params = new ArrayList();
Params.Add(ItemID);
sql = new SQLCache(Params);
SqlDataReader rdr = dbHelper.ExecuteReader(sql.GetSQL("GetItemOptionsByOptionId"));
if (rdr != null)
{
rdr.Read();
if (!rdr["ItemCode"].Equals(DBNull.Value) && !string.IsNullOrEmpty(rdr["ItemCode"].ToString()))
{
txtItemCode.Text = rdr["ItemCode"].ToString();
}
if (!rdr["PricePerPiece"].Equals(DBNull.Value) && !string.IsNullOrEmpty(rdr["PricePerPiece"].ToString()))
{
txtPrice.Text = rdr["PricePerPiece"].ToString();
}
}
}
private void GenerateTransactionId()
{
Params = new ArrayList();
Params.Add(SelectItemId());
sql = new SQLCache(Params);
string transID = dbHelper.ExecuteScalar(sql.GetSQL("GetTransactionIdByBillType"));
int counter = 0;
if (string.IsNullOrEmpty(transID))
{
transID = GetCode(BillType) + DateTime.Now.Year + "-" + "0001";
}
else
{
if (Convert.ToInt32(transID.Substring(9, 4)) == DateTime.Now.Year)
{
counter = Convert.ToInt32(transID.Substring(14)) + 1;
transID = transID.Substring(0, 9) + DateTime.Now.Year + "-" + counter.ToString().PadLeft(4, '0');
}
else
{
transID = GetCode(BillType) + DateTime.Now.Year + "-" + "0001";
}
}
txtTransaction.Text = transID;
}
private void btnSave_Click(object sender, EventArgs e)
{
try
{
if (!IsValidated())
{
return;
}
int status = 0;
Params = new ArrayList();
if (mode == Enums.Mode.Add)
{
Params.Add(SelectItemId());
}
else if (mode == Enums.Mode.Edit)
{
Params.Add(txtItemOptionId.Text);
}
Params.Add(txtItemCode.Text);
Params.Add(txtBillItem.Text);
Params.Add(txtPrice.Text);
sql = new SQLCache(Params);
if (mode == Enums.Mode.Edit)
{
status = dbHelper.ExecuteNonQuery(sql.GetSQL("Update_BillItemOption"));
}
else if (mode == Enums.Mode.Add)
{
status = dbHelper.ExecuteNonQuery(sql.GetSQL("Add_BillItemOption"));
}
if (status > 0)
{
ResetValues();
MessageBox.Show("Record updated successfully", "Billing", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message.ToString(), "Billing", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
private void FillDataGrid()
{
Params = new ArrayList();
sql = new SQLCache(Params);
DataTable dt = dbHelper.ExecuteDataTable(sql.GetSQL("GetStaffMembers"));
dgvStaffMembers.DataSource = dt;
dgvStaffMembers.Visible = true;
dgvStaffMembers.ClearSelection();
}
private void btnSave_Click(object sender, EventArgs e)
{
try
{
Params = new ArrayList();
Params.Add(txtName.Text);
Params.Add(txtFName.Text);
Params.Add(Convert.ToDateTime(dtpDOB.Text));
Params.Add(txtCellNo.Text);
Params.Add(txtDesignation.Text);
Params.Add(txtCNIC.Text);
Params.Add(txtAddress.Text);
MemoryStream stream = new MemoryStream();
pbImage.Image.Save(stream, System.Drawing.Imaging.ImageFormat.Png);
byte[] pic = stream.ToArray();
Params.Add(pic);
Params.Add(true);
int status = 0;
if (mode == Enums.Mode.Edit)
{
Params.Add(Convert.ToInt32(dgvStaffMembers.Rows[dgvStaffMembers.CurrentCell.RowIndex].Cells["Id"].Value));
status = dbHelper.ExecuteNonQueryForImage(sql.GetSQL("Update_StaffNIC"), Params);
}
else
{
status = dbHelper.ExecuteNonQueryForImage(sql.GetSQL("Insert_StaffNIC"), Params);
}
if (status > 0)
{
MessageBox.Show("Record inserted successfully", "Staff NIC", MessageBoxButtons.OK, MessageBoxIcon.Information);
ResetValues();
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message.ToString(), "Staff NIC", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
private void FillDataGrid()
{
Params = new ArrayList();
Params.Add(SelectItemId());
sql = new SQLCache(Params);
//DataTable dt = dbHelper.ExecuteDataTable(sql.GetSQL("GetBillDetail"));
dtSearch = dbHelper.ExecuteDataTable(sql.GetSQL("GetBillDetail"));
//dgBillSummary.DataSource = dt;
dgBillSummary.DataSource = dtSearch;
//mode = Enums.Mode.Edit;
dgBillSummary.Focus();
dgBillSummary.ClearSelection();
}
private void BindBillerInformationByRegNo()
{
//if (string.IsNullOrEmpty(txtRegNo.Text))
//{
// errorProvider.SetError(txtRegNo, "Please enter registration number");
// return;
//}
//errorProvider.SetError(txtRegNo, String.Empty);
//errorProvider.Clear();
Params = new ArrayList();
Params.Add(txtRegNo.Text);
sql = new SQLCache(Params);
SqlDataReader rdr = dbHelper.ExecuteReader(sql.GetSQL("GetMemberInfoByRegNo"));
if (rdr != null)
{
rdr.Read();
if (!rdr["MemberName"].Equals(DBNull.Value) && !string.IsNullOrEmpty(rdr["MemberName"].ToString()))
{
txtName.Text = rdr["MemberName"].ToString();
}
if (!rdr["FatherName"].Equals(DBNull.Value) && !string.IsNullOrEmpty(rdr["FatherName"].ToString()))
{
txtFatherName.Text = rdr["FatherName"].ToString();
}
if (!rdr["Mobile"].Equals(DBNull.Value) && !string.IsNullOrEmpty(rdr["Mobile"].ToString()))
{
txtCellNo.Text = rdr["Mobile"].ToString();
}
if (!rdr["Sign"].Equals(DBNull.Value) && !string.IsNullOrEmpty(rdr["Sign"].ToString()))
{
txtOrakh.Text = rdr["Sign"].ToString();
}
if (!rdr["ResidentAddress"].Equals(DBNull.Value) && !string.IsNullOrEmpty(rdr["ResidentAddress"].ToString()))
{
txtAddress.Text = rdr["ResidentAddress"].ToString();
}
if (!rdr["CardNo"].Equals(DBNull.Value) && !string.IsNullOrEmpty(rdr["CardNo"].ToString()))
{
txtCardNo.Text = rdr["CardNo"].ToString();
}
}
}
private void BillingItemOptions_Load(object sender, EventArgs e)
{
grpBoxBilling.Visible = false;
dgvItemOptions.Visible = true;
btnBack.Enabled = false;
btnEdit.Enabled = false;
GetItemCode();
Params = new ArrayList();
Params.Add(SelectItemId());
sql = new SQLCache(Params);
DataTable dt = dbHelper.ExecuteDataTable(sql.GetSQL("GetBillItemOptions"));
dgvItemOptions.DataSource = dt;
}
private void PopulateItemOptions()
{
Params = new ArrayList();
Params.Add(SelectItemId());
sql = new SQLCache(Params);
DataTable dt = dbHelper.ExecuteDataTable(sql.GetSQL("GetBillItemOptions"));
if (dt.Rows.Count > 0)
{
DataRow row = dt.NewRow();
row["Name"] = "Please select Item Option";
dt.Rows.InsertAt(row, 0);
cmbItemName.DisplayMember = "Name";
cmbItemName.ValueMember = "ID";
cmbItemName.DataSource = dt;
}
}
private void txtSlipNo_KeyPress(object sender, KeyPressEventArgs e)
{
if (e.KeyChar == (char)Keys.Enter)
{
txtAdvDated.Text = string.Empty;
txtAdvAmount.Text = string.Empty;
txtNetAmount.Text = string.Empty;
try
{
Params = new ArrayList();
Params.Add(txtSlipNo.Text);
Params.Add(txtCardNo.Text);
sql = new SQLCache(Params);
SqlDataReader rdr = dbHelper.ExecuteReader(sql.GetSQL("GetAdvanceAmountForHallBooking"));
if (rdr != null)
{
rdr.Read();
txtAdvAmount.Text = Convert.ToDouble(rdr["Amount"]).ToString("0.00");
txtAdvDated.Text = Convert.ToDateTime(rdr["Date"]).ToString("dd/MM/yyyy");
if (Convert.ToDouble(txtGrandTotal.Text) > 0 && Convert.ToDouble(txtAdvAmount.Text) > 0)
{
txtNetAmount.Text = Convert.ToString(Convert.ToDouble(txtGrandTotal.Text) + (Convert.ToDouble(txtLease.Text)) - Convert.ToDouble(txtAdvAmount.Text));
}
}
else
{
MessageBox.Show("Invalid Slip No or Family Card No", "Bill", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message.ToString(), "Billing", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
}
private void dgvStaffMembers_CellClick(object sender, DataGridViewCellEventArgs e)
{
try
{
if (dgvStaffMembers.Columns[e.ColumnIndex].Name == "Remove" && string.IsNullOrEmpty(dgvStaffMembers.Columns[e.ColumnIndex].HeaderText))
{
return;
}
btnEdit.Enabled = true;
btnViewAll.Enabled = true;
Params = new ArrayList();
Params.Add(dgvStaffMembers.Rows[dgvStaffMembers.CurrentCell.RowIndex].Cells["Id"].Value.ToString());
sql = new SQLCache(Params);
SqlDataReader rdr = dbHelper.ExecuteReader(sql.GetSQL("GetStaffMemberInformation"));
if (rdr != null)
{
rdr.Read();
byte[] byteImg = new byte[0];
txtName.Text = rdr["Name"].ToString();
txtFName.Text = rdr["FatherName"].ToString();
dtpDOB.Text = rdr["DOB"].ToString();
txtDesignation.Text = rdr["Designation"].ToString();
txtCellNo.Text = rdr["ContactNo"].ToString();
txtCNIC.Text = rdr["CNIC"].ToString();
txtAddress.Text = rdr["Address"].ToString();
//pbImage = Convert.ToInt32(rdr["BillId"].ToString());
byteImg = (byte[])rdr["Image"];
MemoryStream stream = new MemoryStream(byteImg);
pbImage.Image = Image.FromStream(stream);
rdr.Close();
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message.ToString(), "Staff NIC", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
private void dgBillSummary_CellClick(object sender, DataGridViewCellEventArgs e)
{
try
{
if (DBLayer.User_Right(UserID, SecurityLevelID, "[Modify]"))
{
btnEdit.Enabled = true;
}
else
{
btnEdit.Enabled = false;
}
Params = new ArrayList();
Params.Add(SelectItemId());
Params.Add(dgBillSummary.Rows[dgBillSummary.CurrentCell.RowIndex].Cells[1].Value.ToString());
sql = new SQLCache(Params);
SqlDataReader rdr = dbHelper.ExecuteReader(sql.GetSQL("GetBillForBinding"));
int BillId = 0;
if (rdr != null)
{
rdr.Read();
txtTransaction.Text = rdr["TransactionNo"].ToString();
txtRegNo.Text = rdr["RegNo"].ToString();
dtpDate.Text = rdr["EntryDate"].ToString();
txtTotalItems.Text = rdr["TotalItems"].ToString();
txtTotalQuantity.Text = rdr["TotalQuantity"].ToString();
txtGrandTotal.Text = rdr["GrandTotal"].ToString();
txtCardNo.Text = rdr["CardNo"].ToString();
BillId = Convert.ToInt32(rdr["BillId"].ToString());
hdnBillId.Text = rdr["BillId"].ToString();
txtAdvAmount.Text = rdr["AdvanceAmount"].ToString();
txtSlipNo.Text = rdr["AdvSlipNo"].ToString();
txtAdvDated.Text = rdr["AdvanceDate"].ToString();
cmbEvent.SelectedValue = rdr["EventID"].ToString();
chkIsPaid.Checked = Convert.ToBoolean(rdr["IsPaid"].ToString());
if (!string.IsNullOrEmpty(txtAdvAmount.Text) && Convert.ToDouble(txtAdvAmount.Text) > 0)
{
txtNetAmount.Text = Convert.ToString(Convert.ToDouble(txtGrandTotal.Text) + (Convert.ToDouble(txtLease.Text)) - Convert.ToDouble(txtAdvAmount.Text));
}
else
{
txtNetAmount.Text = Convert.ToString(Convert.ToDouble(txtGrandTotal.Text) + Convert.ToDouble(txtLease.Text));
}
if (rdr["LeaseRenewalFund"] != DBNull.Value && !string.IsNullOrEmpty(rdr["LeaseRenewalFund"].ToString()))
{
txtLease.Text = Convert.ToString(Convert.ToDouble(rdr["LeaseRenewalFund"].ToString()));
}
else
{
txtLease.Text = "0";
}
if (!string.IsNullOrEmpty(txtLease.Text) && Convert.ToDouble(txtLease.Text) > 0 && !chkLease.Checked)
{
chkLease.Checked = true;
}
else
{
chkLease.Checked = false;
}
if (BillType == Enums.BillType.Coffin.ToString())
{
txtDeceasedName.Text = rdr["Name"].ToString();
txtRelName.Text = rdr["RelativeName"].ToString();
txtDeceasedOrakh.Text = rdr["Orakh"].ToString();
txtDeceasedAge.Text = rdr["Age"].ToString();
txtReasonOfDeath.Text = rdr["ReasonOfDeath"].ToString();
dtpTimeOfDeath.Text = rdr["TimeOfDeath"].ToString();
txtApplicantName.Text = rdr["ApplicantName"].ToString();
txtApplicantPhone.Text = rdr["CellNo"].ToString();
}
else
{
txtName.Text = rdr["Name"].ToString();
txtFatherName.Text = rdr["RelativeName"].ToString();
txtOrakh.Text = rdr["Orakh"].ToString();
txtCellNo.Text = rdr["CellNo"].ToString();
txtAddress.Text = rdr["RAddress"].ToString();
}
rdr.Close();
Params = new ArrayList();
Params.Add(BillId);
sql = new SQLCache(Params);
DataTable dt = dbHelper.ExecuteDataTable(sql.GetSQL("GetBillDetailForBinding"));
//dgvItemOptions.DataSource = dt;
int counter = 0;
this.dgvItemOptions.Rows.Clear();
foreach (DataRow row in dt.Rows)
{
string itemCode = row["ItemCode"].ToString();
string description = row["Description"].ToString();
double quantity = Convert.ToDouble(row["Quantity"].ToString());
double price = Convert.ToDouble(row["Price"].ToString());
double totalPrice = Convert.ToDouble(Convert.ToDouble(row["Price"].ToString()) * Convert.ToDouble(row["Quantity"].ToString()));
counter = counter + 1;
this.dgvItemOptions.Rows.Add(counter.ToString(), itemCode, description, quantity, price, totalPrice, Properties.Resources.DeleteRed);
}
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message.ToString(), "Billing", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
private void btnSave_Click(object sender, EventArgs e)
{
try
{
if (chkLease.Checked == false)
{
DialogResult dr = MessageBox.Show("Would you like to apply Lease Renewal Fund on this bill ? ", "Bill", MessageBoxButtons.YesNo, MessageBoxIcon.Question);
if (dr == DialogResult.Yes)
{
return;
}
}
Params = new ArrayList();
string query = string.Empty;
Params.Add(SelectItemId());
Params.Add(txtTransaction.Text);
Params.Add(txtRegNo.Text);
Params.Add(Convert.ToDateTime(dtpDate.Value.ToString("dd/MM/yyyy")));
Params.Add(txtTotalItems.Text);
Params.Add(txtTotalQuantity.Text);
Params.Add(txtGrandTotal.Text);
if (chkIsPaid.Checked == true)
{
Params.Add(1);
}
else
{
Params.Add(0);
}
if (BillType == Enums.BillType.Coffin.ToString())
{
Params.Add(txtCardNo.Text);
Params.Add(txtDeceasedName.Text);
Params.Add(txtRelName.Text);
Params.Add(txtDeceasedOrakh.Text);
Params.Add(txtApplicantPhone.Text);
Params.Add(txtDeceasedAge.Text);
Params.Add(txtReasonOfDeath.Text);
Params.Add(dtpTimeOfDeath.Text);
Params.Add(txtApplicantName.Text);
sql = new SQLCache(Params);
query = sql.GetSQL("Insert_tblBill_Coffin");
}
else
{
Params.Add(txtCardNo.Text);
Params.Add(txtName.Text);
Params.Add(txtFatherName.Text);
Params.Add(txtOrakh.Text);
Params.Add(txtCellNo.Text);
Params.Add(txtAddress.Text);
Params.Add(string.IsNullOrEmpty(txtAdvAmount.Text) ? "0" : txtAdvAmount.Text);
Params.Add(string.IsNullOrEmpty(txtSlipNo.Text) ? "0" : txtSlipNo.Text);
Params.Add(txtAdvDated.Text);
if (cmbEvent.SelectedValue == null)
{
Params.Add(0);
}
else
{
Params.Add(Int32.Parse(cmbEvent.SelectedValue.ToString()));
}
if (isBillEdit)
{
Params.Add(hdnBillId.Text);
Params.Add(string.IsNullOrEmpty(txtLease.Text) ? "0" : txtLease.Text);
sql = new SQLCache(Params);
query = sql.GetSQL("Update_tblBill");
query = query + sql.GetSQL("Update_tblBillDetail");
}
else
{
Params.Add(string.IsNullOrEmpty(txtLease.Text) ? "0" : txtLease.Text);
sql = new SQLCache(Params);
query = sql.GetSQL("Insert_tblBill");
}
}
foreach (DataGridViewRow row in dgvItemOptions.Rows)
{
Params = new ArrayList();
Params.Add(row.Cells["ItemCode"].Value);
Params.Add(row.Cells["OptionText"].Value);
Params.Add(row.Cells["Quantity"].Value);
Params.Add(row.Cells["PricePerPiece"].Value);
Params.Add(row.Cells["Total"].Value);
if (isBillEdit)
{
Params.Add(hdnBillId.Text);
}
sql = new SQLCache(Params);
query = query + sql.GetSQL("Insert_tblBillDetail");
}
query = query + sql.GetSQL("Insert_tblBillDetailEndRegion");
int status = dbHelper.ExecuteNonQuery(query);
if (status > 0)
{
MessageBox.Show("Record updated successfully", "Billing", MessageBoxButtons.OK, MessageBoxIcon.Information);
ResetAfterSubmission();
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message.ToString(), "Billing", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}