private void LOAD_ROOM_lIST()
{
try
{
CONNECTION.open_connection();
using (MySqlDataAdapter DA = new MySqlDataAdapter("SELECT r.room_id,r.room_name,r.room_package_id,rp.package_name,r.room_floor,r.description,r.room_type,r.current_status FROM room r JOIN room_packages rp ON r.room_package_id=rp.room_package_id", CONNECTION.CON))
{
DataTable DT = new DataTable();
DA.Fill(DT);
if (DT.Rows.Count > 0)
{
DGV__ROOMS.DataSource = DT;
DGV__ROOMS.AutoGenerateColumns = false;
}
else
{
DGV__ROOMS.DataSource = null;
}
}
}
catch (Exception EX)
{
MSGBOX mdg = new MSGBOX(MessageAlertHeder.Error(), EX.Message, MessageAlertImage.Error());
mdg.ShowDialog();
}
finally
{
CONNECTION.close_connection();
}
}
private void SAVE_NEW_AGENT()
{
try
{
CONNECTION.open_connection();
using (MySqlCommand da = new MySqlCommand("INSERT INTO agent ( agent_name) VALUES ( @agent_name)", CONNECTION.CON))
{
da.Parameters.Clear();
da.Parameters.AddWithValue("@agent_name", TXT_NEW_AGENT_NAME.Text);
if (da.ExecuteNonQuery() > 0)
{
string QRY2 = "SELECT agent_id, agent_name FROM agent";
CLS_METHODS.FILL_COMBOBOX(CMB_AGENT, QRY2, "agent_name", "agent_id", -1);
CMB_AGENT.Text = TXT_NEW_AGENT_NAME.Text;
PNL_QUICK_ADD_AGENT.Visible = false;
}
}
}
catch (Exception ex)
{
MSGBOX mdg = new MSGBOX(MessageAlertHeder.Error(), ex.Message, MessageAlertImage.Error());
mdg.ShowDialog();
}
finally
{
CONNECTION.close_connection();
}
}
private string UPDATE_ROOM_DETAILS()
{
try
{
CONNECTION.open_connection();
using (MySqlCommand cmd = new MySqlCommand("UPDATE room SET room_name=@room_name,room_package_id=@room_package_id,room_floor=@room_floor,description=@description,room_type=@room_type WHERE room_id=@room_id", CONNECTION.CON))
{
cmd.Parameters.Clear();
cmd.Parameters.AddWithValue("@room_name", TXT_ROOM_NO.Text);
cmd.Parameters.AddWithValue("@room_package_id", CMB_ROOM_PKG.SelectedValue);
cmd.Parameters.AddWithValue("@room_floor", CMB_FLOOR.Text);
cmd.Parameters.AddWithValue("@description", TXT_DESCRIPTION.Text);
cmd.Parameters.AddWithValue("@room_type", CMB_ROOM_TYPE.Text);
cmd.Parameters.AddWithValue("@room_id", room_id);
if (cmd.ExecuteNonQuery() > 0)
{
return("done");
}
else
{
return("error");
}
}
}
catch (Exception ex)
{
MSGBOX mdg = new MSGBOX(MessageAlertHeder.Error(), ex.Message, MessageAlertImage.Error());
mdg.ShowDialog();
return("error");
}
finally
{
CONNECTION.close_connection();
}
}
private void LOAD_SERVICE_lIST()
{
try
{
CONNECTION.open_connection();
using (MySqlDataAdapter DA = new MySqlDataAdapter("SELECT id,service_name,service_price,description,CASE (service_status) WHEN '1' THEN 'ACTIVE' WHEN '0' THEN 'DEACTIVE' END AS service_status FROM additional_service_list", CONNECTION.CON))
{
DataTable DT = new DataTable();
DA.Fill(DT);
if (DT.Rows.Count > 0)
{
DGV_SV.DataSource = DT;
DGV_SV.AutoGenerateColumns = false;
}
else
{
DGV_SV.DataSource = null;
}
}
}
catch (Exception EX)
{
MSGBOX mdg = new MSGBOX(MessageAlertHeder.Error(), EX.Message, MessageAlertImage.Error());
mdg.ShowDialog();
}
finally
{
CONNECTION.close_connection();
}
}
private void Updatecategory()
{
try
{
CONNECTION.open_connection();
using (MySqlCommand da = new MySqlCommand("UPDATE category SET caegory_name=@caegory_name WHERE categry_id=@categry_id", CONNECTION.CON))
{
da.Parameters.Clear();
da.Parameters.AddWithValue("@categry_id", lbl_category.Text);
da.Parameters.AddWithValue("@caegory_name", txt_category_name.Text);
if (da.ExecuteNonQuery() > 0)
{
MSGBOX mdg = new MSGBOX(MessageAlertHeder.Success(), "CATEGORY UPDATED SUCCESFULLY!", MessageAlertImage.Success());
mdg.ShowDialog();
}
else
{
MSGBOX mdg = new MSGBOX(MessageAlertHeder.Alert(), "CATEGORY UPDATED SUCCESFULLY!", MessageAlertImage.Alert());
mdg.ShowDialog();
}
}
}
catch (Exception ex)
{
MSGBOX mdg = new MSGBOX(MessageAlertHeder.Error(), ex.Message, MessageAlertImage.Error());
mdg.ShowDialog();
}
finally
{
CONNECTION.close_connection();
}
}
private void CMB_SUPPLIER_SelectedIndexChanged_1(object sender, EventArgs e)
{
if (CMB_SUPPLIER.SelectedIndex >= 0)
{
try
{
CONNECTION.open_connection();
using (MySqlDataAdapter adp = new MySqlDataAdapter("SELECT IFNULL(SUM(due),0.00) FROM supplier_account WHERE supplier_id=@supplier_id", CONNECTION.CON))
{
adp.SelectCommand.Parameters.Clear();
adp.SelectCommand.Parameters.AddWithValue("@supplier_id", CMB_SUPPLIER.SelectedValue);
DataTable tbl = new DataTable();
adp.Fill(tbl);
if (tbl.Rows.Count > 0)
{
LBL_PREVIOUS_DUES.Text = tbl.Rows[0].Field <double>(0).ToString("F2");
}
else
{
LBL_PREVIOUS_DUES.Text = "0.00";
}
}
}
catch (Exception)
{
LBL_PREVIOUS_DUES.Text = "0.00";
}
finally
{
CONNECTION.close_connection();
}
LBL_TOTAL_PAYABLE.Text = (Convert.ToDouble(LBL_TOT_GRN.Text) + Convert.ToDouble(LBL_PREVIOUS_DUES.Text)).ToString("F2");
}
}
private String CHECK_GUEST_DATA()
{
try
{
CONNECTION.open_connection();
using (MySqlDataAdapter da = new MySqlDataAdapter("SELECT id_no,first_name,last_name,guest_id FROM guest WHERE id_no=@id_no", CONNECTION.CON))
{
da.SelectCommand.Parameters.Clear();
da.SelectCommand.Parameters.AddWithValue("@id_no", TXT_NIC_NO.Text);
DataTable DT = new DataTable();
da.Fill(DT);
if (DT.Rows.Count > 0)
{
return(DT.Rows[0][3].ToString());
}
else
{
return("N");
}
}
}
catch (Exception ex)
{
return(ex.ToString());
}
finally
{
CONNECTION.close_connection();
}
}
//private async Task lOADgRIDVIEW()
//{
// loadingCircle1.Visible = true;
// loadingCircle1.Size = new Size(75, 75);
// loadingCircle1.Active = true;
// await Task.Run(() => {
// LOADCATEGORYlIST();
// });
// loadingCircle1.Active = false;
// loadingCircle1.Visible = false;
// loadingCircle1.Size = new Size(1, 1);
//}
//private async Task SAVECATEGORY()
//{
// loadingCircle1.Visible = true;
// loadingCircle1.Size = new Size(75, 75);
// loadingCircle1.Active = true;
// btn_save.Enabled = false;
// await Task.Run(() => {
// res = saveCategory();
// });
// loadingCircle1.Active = false;
// loadingCircle1.Visible = false;
// loadingCircle1.Size = new Size(1, 1);
// btn_save.Enabled = true;
// BTN_NEW.Focus();
// txt_category_name.Clear();
// if (res == "1")
// {
// lbl_category.Text = GETMAXID();
// MSGBOX mdg = new MSGBOX(MessageAlertHeder.Warning(), "CATEGORY ADDED SUCCESFULLY!", MessageAlertImage.Warning());
// mdg.ShowDialog();
// }
// else if (res == "0")
// {
// MSGBOX mdg = new MSGBOX(MessageAlertHeder.Warning(), "CATEGORY ADDED FAILED!", MessageAlertImage.Warning());
// mdg.ShowDialog();
// }
// else
// {
// MSGBOX mdg = new MSGBOX(MessageAlertHeder.Alert(), saveCategory(), MessageAlertImage.Alert());
// mdg.ShowDialog();
// }
//}
//private async Task UPDATE_CATEGORY_NAME()
//{
// loadingCircle1.Visible = true;
// loadingCircle1.Size = new Size(75, 75);
// loadingCircle1.Active = true;
// btn_save.Enabled = false;
// await Task.Run(() => {
// res = updatecategory();
// // LOADCATEGORYlIST();
// });
// loadingCircle1.Active = false;
// loadingCircle1.Visible = false;
// loadingCircle1.Size = new Size(1, 1);
// btn_save.Enabled = true;
// BTN_NEW.Focus();
// txt_category_name.Clear();
// lbl_category.Text = "N/A";
// btn_save.Text = "SAVE";
// if (res == "1")
// {
// lbl_category.Text = GETMAXID();
// MSGBOX mdg = new MSGBOX(MessageAlertHeder.Warning(), "CATEGORY UPDATED SUCCESFULLY!", MessageAlertImage.Warning());
// mdg.ShowDialog();
// }
// else if (res == "0")
// {
// MSGBOX mdg = new MSGBOX(MessageAlertHeder.Warning(), "CATEGORY UPDATED FAILED!", MessageAlertImage.Warning());
// mdg.ShowDialog();
// }
// else
// {
// MSGBOX mdg = new MSGBOX(MessageAlertHeder.Alert(), saveCategory(), MessageAlertImage.Alert());
// mdg.ShowDialog();
// }
//}
private void saveCategory()
{
try
{
CONNECTION.open_connection();
using (MySqlCommand da = new MySqlCommand("INSERT INTO category(caegory_name) VALUES(@caegory_name)", CONNECTION.CON))
{
da.Parameters.Clear();
da.Parameters.AddWithValue("@caegory_name", txt_category_name.Text);
if (da.ExecuteNonQuery() > 0)
{
lbl_category.Text = GETMAXID();
MSGBOX mdg = new MSGBOX(MessageAlertHeder.Success(), "CATEGORY ADDED SUCCESFULLY!", MessageAlertImage.Success());
mdg.ShowDialog();
}
else
{
MSGBOX mdg = new MSGBOX(MessageAlertHeder.Alert(), "CATEGORY ADDED FAILED!", MessageAlertImage.Alert());
mdg.ShowDialog();
}
}
}
catch (Exception ex)
{
MSGBOX mdg = new MSGBOX(MessageAlertHeder.Error(), ex.Message, MessageAlertImage.Error());
mdg.ShowDialog();
}
finally
{
CONNECTION.close_connection();
}
}
private void BTN_REMOVE_Click(object sender, EventArgs e)
{
CONNECTION.open_connection();
try
{
foreach (DataGridViewRow dr in DGV_ADD_SERVICE.SelectedRows)
{
using (MySqlCommand deleteservice = new MySqlCommand("DELETE FROM additional_service WHERE id=@id", CONNECTION.CON))
{
deleteservice.Parameters.AddWithValue("@id", dr.Cells[0].Value.ToString());
int n1 = deleteservice.ExecuteNonQuery();
if (n1 == 1)
{
DISPLAY_ADDED_SERVICES(Reservation_id, Room_id);
MSGBOX mdg = new MSGBOX(MessageAlertHeder.Success(), "Removed Successfully", MessageAlertImage.Success());
mdg.ShowDialog();
}
}
}
}
catch (Exception ex)
{
MSGBOX mdg = new MSGBOX(MessageAlertHeder.Error(), ex.Message, MessageAlertImage.Error());
mdg.ShowDialog();
}
finally
{
CONNECTION.close_connection();
}
}
private void DISPLAY_ADDED_SERVICES(string reserv_id, string room_id)
{
CONNECTION.open_connection();
try
{
using (MySqlDataAdapter da = new MySqlDataAdapter("SELECT additional_service.id,additional_service.additional_serivice_id, additional_service_list.service_name, additional_service.additional_serivice_qty, additional_service.additional_serivice_price FROM additional_service, additional_service_list WHERE additional_service_list.id = additional_service.additional_serivice_id AND reservation_id = @rid AND additional_service.room_id = @roomid", CONNECTION.CON))
{
da.SelectCommand.Parameters.AddWithValue("@rid", reserv_id);
da.SelectCommand.Parameters.AddWithValue("@roomid", Room_id);
DataTable get_added_services = new DataTable();
da.Fill(get_added_services);
DGV_ADD_SERVICE.Rows.Clear();
if (get_added_services.Rows.Count > 0)
{
double total = 0;
foreach (DataRow dr in get_added_services.Rows)
{
DGV_ADD_SERVICE.Rows.Add(dr["id"].ToString(), dr["additional_serivice_id"].ToString(), dr["service_name"].ToString(), dr["additional_serivice_qty"].ToString(), dr["additional_serivice_price"].ToString());
total += Convert.ToDouble(dr["additional_serivice_price"].ToString());
}
LBL_TOTAL.Text = string.Format("{0:#,##0.00}", total);
}
}
}
catch (Exception ex)
{
MSGBOX mdg = new MSGBOX(MessageAlertHeder.Error(), ex.Message, MessageAlertImage.Error());
mdg.ShowDialog();
}
finally
{
CONNECTION.close_connection();
}
}
private void CMB_SERVICES_SelectedIndexChanged(object sender, EventArgs e)
{
CONNECTION.open_connection();
try
{
using (MySqlDataAdapter da = new MySqlDataAdapter("SELECT * FROM additional_service_list WHERE id=@id", CONNECTION.CON))
{
da.SelectCommand.Parameters.AddWithValue("@id", CMB_SERVICES.SelectedValue);
DataTable getservices = new DataTable();
da.Fill(getservices);
if (getservices.Rows.Count > 0)
{
double price = ((Convert.ToDouble(getservices.Rows[0]["service_price"].ToString()) * CLS_TAX.GetTotalTaxPercentage()) / 100.0) + Convert.ToDouble(getservices.Rows[0]["service_price"].ToString());
double subtotal = Convert.ToDouble(TXT_QTY.Text) * price;
TXT_SERVICE_CHARGE.Text = string.Format("{0:#,##0.00}", price);
TXT_SUBTOTAL.Text = string.Format("{0:#,##0.00}", subtotal);
}
}
}
catch (Exception ex)
{
MSGBOX mdg = new MSGBOX(MessageAlertHeder.Error(), ex.Message, MessageAlertImage.Error());
mdg.ShowDialog();
}
finally
{
CONNECTION.close_connection();
}
}
private string UPDATE_MEAL_PLAN()
{
try
{
CONNECTION.open_connection();
using (MySqlCommand cmd = new MySqlCommand("UPDATE meal_types SET type=@type,description=@description,adult_meal_price=@adult_meal_price,child_meal_price=@child_meal_price WHERE meal_type_id=@meal_type_id", CONNECTION.CON))
{
cmd.Parameters.Clear();
cmd.Parameters.AddWithValue("@meal_type_id", Convert.ToInt32(LBL_MP_ID.Text));
cmd.Parameters.AddWithValue("@type", TXT_MP_NAME.Text);
cmd.Parameters.AddWithValue("@description", TXT_DESCRIPTION.Text);
cmd.Parameters.AddWithValue("@adult_meal_price", Convert.ToDouble(TXT_ADULT_PRICE.Text));
cmd.Parameters.AddWithValue("@child_meal_price", Convert.ToDouble(TXT_CHILD_PRICE.Text));
if (cmd.ExecuteNonQuery() > 0)
{
return("done");
}
else
{
return("error");
}
}
}
catch (Exception ex)
{
MSGBOX mdg = new MSGBOX(MessageAlertHeder.Error(), ex.Message, MessageAlertImage.Error());
mdg.ShowDialog();
return("error");
}
finally
{
CONNECTION.close_connection();
}
}
private void LOAD_ROOM_TYPES()
{
try
{
CONNECTION.open_connection();
using (MySqlDataAdapter adp = new MySqlDataAdapter("SELECT room_package_id,package_name FROM room_packages WHERE package_status='1'", CONNECTION.CON))
{
DataTable tbl = new DataTable();
adp.Fill(tbl);
CMB_ROOM_PKG.DataSource = null;
if (tbl.Rows.Count > 0)
{
CMB_ROOM_PKG.DataSource = tbl;
CMB_ROOM_PKG.DisplayMember = "package_name";
CMB_ROOM_PKG.ValueMember = "room_package_id";
CMB_ROOM_PKG.SelectedIndex = -1;
}
}
}
catch (Exception EX)
{
MSGBOX mdg = new MSGBOX(MessageAlertHeder.Error(), EX.Message, MessageAlertImage.Error());
mdg.ShowDialog();
}
finally
{
CONNECTION.close_connection();
}
}
private void LOADGUESTS()
{
try
{
CONNECTION.open_connection();
using (MySqlDataAdapter adp = new MySqlDataAdapter("SELECT guest_id,CONCAT_WS(' ',first_name,last_name) AS guest_name FROM guest ORDER BY guest_id", CONNECTION.CON))
{
DataTable tbl = new DataTable();
adp.Fill(tbl);
if (tbl.Rows.Count > 0)
{
CMB_GUEST.DataSource = tbl;
CMB_GUEST.DisplayMember = "guest_name";
CMB_GUEST.ValueMember = "guest_id";
}
}
}
catch (Exception EX)
{
MSGBOX mdg = new MSGBOX(MessageAlertHeder.Error(), EX.Message, MessageAlertImage.Error());
mdg.ShowDialog();
}
finally
{
CONNECTION.close_connection();
}
}
private void LOAD_OTHER_CATEGORY_LIST()
{
try
{
CONNECTION.open_connection();
using (MySqlDataAdapter adp = new MySqlDataAdapter("SELECT id,trans_category_name,trans_category_type,desctiption,category_status FROM other_transaction_category", CONNECTION.CON))
{
DataTable tbl = new DataTable();
adp.Fill(tbl);
DGV_TRANS_CAT.DataSource = null;
if (tbl.Rows.Count > 0)
{
DGV_TRANS_CAT.AutoGenerateColumns = false;
DGV_TRANS_CAT.DataSource = tbl;
}
}
}
catch (Exception EX)
{
MSGBOX mdg = new MSGBOX(MessageAlertHeder.Error(), EX.Message, MessageAlertImage.Error());
mdg.ShowDialog();
}
finally
{
CONNECTION.close_connection();
}
}
private void LOAD_FLOOR_LIST()
{
try
{
CONNECTION.open_connection();
using (MySqlDataAdapter adp = new MySqlDataAdapter("SELECT id,floor_no FROM floor", CONNECTION.CON))
{
DataTable tbl = new DataTable();
adp.Fill(tbl);
CMB_FLOOR.DataSource = null;
if (tbl.Rows.Count > 0)
{
CMB_FLOOR.DataSource = tbl;
CMB_FLOOR.DisplayMember = "floor_no";
CMB_FLOOR.ValueMember = "id";
CMB_FLOOR.SelectedIndex = -1;
}
}
}
catch (Exception EX)
{
MSGBOX mdg = new MSGBOX(MessageAlertHeder.Error(), EX.Message, MessageAlertImage.Error());
mdg.ShowDialog();
}
finally
{
CONNECTION.close_connection();
}
}
private string login()
{
Cursor.Current = Cursors.WaitCursor;
try
{
CONNECTION.open_connection();
using (MySqlDataAdapter da = new MySqlDataAdapter("SELECT id,user_id,user_name,user_type,last_login_datetime FROM user_login WHERE user_name=@user_name AND PASSWORD=@password", CONNECTION.CON))
{
da.SelectCommand.Parameters.Clear();
da.SelectCommand.Parameters.AddWithValue("@user_name", txt_uname.Text);
da.SelectCommand.Parameters.AddWithValue("@password", CLS_ENCRYPTION.EncryptPlainTextToCipherText(txt_pass.Text));
DataTable dt = new DataTable();
da.Fill(dt);
if (dt.Rows.Count > 0)
{
return("1");
}
else
{
return("0");
}
}
}
catch (Exception ex)
{
Cursor.Current = Cursors.Default;
return(ex.Message);
}
finally
{
CONNECTION.close_connection();
Cursor.Current = Cursors.Default;
}
}
private string UPDATE_TRANSACTION_CATEGORY()
{
try
{
CONNECTION.open_connection();
using (MySqlCommand cmd = new MySqlCommand("UPDATE other_transaction_category SET trans_category_name=@trans_category_name,trans_category_type=@trans_category_type,desctiption=@desctiption WHERE id=@id", CONNECTION.CON))
{
cmd.Parameters.Clear();
cmd.Parameters.AddWithValue("@trans_category_name", TXT_TRANS_CAT.Text);
cmd.Parameters.AddWithValue("@trans_category_type", CMB_TRANS_TYPE.Text);
cmd.Parameters.AddWithValue("@desctiption", TXT_DESCRIPTION.Text);
cmd.Parameters.AddWithValue("@id", CATEGORY_ID);
if (cmd.ExecuteNonQuery() > 0)
{
return("done");
}
else
{
MSGBOX mdg = new MSGBOX(MessageAlertHeder.Error(), "SYSTEM ERROR. PLEASE CONTACT SYSTEM ADMIN", MessageAlertImage.Error());
mdg.ShowDialog();
return("error");
}
}
}
catch (Exception EX)
{
MSGBOX mdg = new MSGBOX(MessageAlertHeder.Error(), EX.Message, MessageAlertImage.Error());
mdg.ShowDialog();
return("error");
}
finally
{
CONNECTION.close_connection();
}
}
private void LOAD_PACKAGE_LIST()
{
try
{
CONNECTION.open_connection();
using (MySqlDataAdapter DA = new MySqlDataAdapter("SELECT room_package_id,package_name,description,`condition`,room_package_price,package_color,CASE (package_status) WHEN '1' THEN 'ACTIVE' WHEN '0' THEN 'DEACTIVE' END AS package_status FROM room_packages", CONNECTION.CON))
{
DataTable DT = new DataTable();
DA.Fill(DT);
if (DT.Rows.Count > 0)
{
DGV_PKG.DataSource = DT;
DGV_PKG.AutoGenerateColumns = false;
}
else
{
DGV_PKG.DataSource = null;
}
}
}
catch (Exception EX)
{
MSGBOX mdg = new MSGBOX(MessageAlertHeder.Error(), EX.Message, MessageAlertImage.Error());
mdg.ShowDialog();
}
finally
{
CONNECTION.close_connection();
}
}
private string UPDATE_PACKAGE()
{
try
{
CONNECTION.open_connection();
using (MySqlCommand cmd = new MySqlCommand("UPDATE room_packages SET package_name=@package_name,description=@description,`condition`=@condition,room_package_price=@room_package_price,package_color=@package_color WHERE room_package_id=@room_package_id", CONNECTION.CON))
{
cmd.Parameters.Clear();
cmd.Parameters.AddWithValue("@room_package_id", Convert.ToInt32(LBL_PKG_ID.Text));
cmd.Parameters.AddWithValue("@package_name", TXT_PKG_NAME.Text);
cmd.Parameters.AddWithValue("@description", TXT_DESCRIPTION.Text);
cmd.Parameters.AddWithValue("@condition", CMB_ROOM_TYPE.Text);
cmd.Parameters.AddWithValue("@room_package_price", Convert.ToDouble(TXT_PKG_RATE.Text));
cmd.Parameters.AddWithValue("@package_color", LBL_COLOR.Text);
if (cmd.ExecuteNonQuery() > 0)
{
return("done");
}
else
{
return("error");
}
}
}
catch (Exception ex)
{
MSGBOX mdg = new MSGBOX(MessageAlertHeder.Error(), ex.Message, MessageAlertImage.Error());
mdg.ShowDialog();
return("error");
}
finally
{
CONNECTION.close_connection();
}
}
private void LOAD_MEAP_PLAN_lIST()
{
try
{
CONNECTION.open_connection();
using (MySqlDataAdapter DA = new MySqlDataAdapter("SELECT meal_type_id,`type`,description,adult_meal_price,child_meal_price,CASE (meal_plan_status) WHEN '1' THEN 'ACTIVE' WHEN '0' THEN 'DEACTIVE' END AS meal_plan_status FROM meal_types", CONNECTION.CON))
{
DataTable DT = new DataTable();
DA.Fill(DT);
if (DT.Rows.Count > 0)
{
DGV_MP.DataSource = DT;
DGV_MP.AutoGenerateColumns = false;
}
else
{
DGV_MP.DataSource = null;
}
}
}
catch (Exception EX)
{
MSGBOX mdg = new MSGBOX(MessageAlertHeder.Error(), EX.Message, MessageAlertImage.Error());
mdg.ShowDialog();
}
finally
{
CONNECTION.close_connection();
}
}
private string MARK_ORDER_AS_SERVED()
{
try
{
CONNECTION.open_connection();
using (MySqlCommand cmd = new MySqlCommand("UPDATE kot_order SET order_status='Served' WHERE order_no=@order_no", CONNECTION.CON))
{
cmd.Parameters.Clear();
cmd.Parameters.AddWithValue("@order_no", DGV_KOT.SelectedRows[0].Cells["Column1"].Value.ToString());
if (cmd.ExecuteNonQuery() > 0)
{
return("done");
}
else
{
MSGBOX mdg = new MSGBOX(MessageAlertHeder.Error(), "SYSTEM ERROR. PLEASE CONTACT SYSTEM ADMIN", MessageAlertImage.Error());
mdg.ShowDialog();
return("error");
}
}
}
catch (Exception ex)
{
MSGBOX mdg = new MSGBOX(MessageAlertHeder.Error(), ex.Message, MessageAlertImage.Error());
mdg.ShowDialog();
return("error");
}
finally
{
CONNECTION.close_connection();
}
}
private string UPDATE_SERVICE_DETAILS()
{
try
{
CONNECTION.open_connection();
using (MySqlCommand cmd = new MySqlCommand("UPDATE additional_service_list SET service_name=@service_name,service_price=@service_price,description=@description WHERE id=@id", CONNECTION.CON))
{
cmd.Parameters.Clear();
cmd.Parameters.AddWithValue("@id", Convert.ToInt32(LBL_SV_ID.Text));
cmd.Parameters.AddWithValue("@service_name", TXT_SV_NAME.Text);
cmd.Parameters.AddWithValue("@service_price", Convert.ToDouble(TXT_SV_PRICE.Text));
cmd.Parameters.AddWithValue("@description", TXT_DESCRIPTION.Text);
if (cmd.ExecuteNonQuery() > 0)
{
return("done");
}
else
{
return("error");
}
}
}
catch (Exception ex)
{
MSGBOX mdg = new MSGBOX(MessageAlertHeder.Error(), ex.Message, MessageAlertImage.Error());
mdg.ShowDialog();
return("error");
}
finally
{
CONNECTION.close_connection();
}
}
private string SAVE_MEAL_PLAN()
{
try
{
if (TXT_DESCRIPTION.Text == string.Empty)
{
TXT_DESCRIPTION.Text = "N/A";
}
CONNECTION.open_connection();
using (MySqlCommand cmd = new MySqlCommand("INSERT INTO meal_types(type,description,adult_meal_price,child_meal_price) VALUES(@type,@description,@adult_meal_price,@child_meal_price)", CONNECTION.CON))
{
cmd.Parameters.Clear();
cmd.Parameters.AddWithValue("@type", TXT_MP_NAME.Text);
cmd.Parameters.AddWithValue("@description", TXT_DESCRIPTION.Text);
cmd.Parameters.AddWithValue("@adult_meal_price", Convert.ToDouble(TXT_ADULT_PRICE.Text));
cmd.Parameters.AddWithValue("@child_meal_price", Convert.ToDouble(TXT_CHILD_PRICE.Text));
if (cmd.ExecuteNonQuery() > 0)
{
int meal_plan_id = 0;
using (MySqlDataAdapter adp = new MySqlDataAdapter("SELECT MAX(meal_type_id) FROM meal_types", CONNECTION.CON))
{
DataTable tbl = new DataTable();
adp.Fill(tbl);
if (tbl.Rows.Count > 0)
{
meal_plan_id = tbl.Rows[0].Field <Int32>(0);
}
}
LBL_MP_ID.Text = meal_plan_id.ToString();
return("done");
}
else
{
return("error");
}
}
}
catch (Exception ex)
{
if (ex.Message.Contains("Duplicate entry"))
{
MSGBOX mdg = new MSGBOX(MessageAlertHeder.Warning(), "MEAL PLAN NAME ALREADY EXSIST!", MessageAlertImage.Warning());
mdg.ShowDialog();
}
else
{
MSGBOX mdg = new MSGBOX(MessageAlertHeder.Error(), ex.Message, MessageAlertImage.Error());
mdg.ShowDialog();
}
return("error");
}
finally
{
CONNECTION.close_connection();
}
}
private void txt_search_TextChanged(object sender, EventArgs e)
{
CONNECTION.open_connection();
try
{
if (txt_search.Text.Length > 1)
{
using (MySqlDataAdapter da = new MySqlDataAdapter("SELECT reservation.status,reservation.reservation_id, guest.first_name, guest.last_name, reservation.arrival_date, reservation.depature_Date, room.room_name,room.room_id FROM reservation, guest, recerved_rooms, room WHERE reservation.guest_id = guest.guest_id AND recerved_rooms.reservation_no = reservation.reservation_id AND room.room_id = recerved_rooms.room_id AND STATUS = 'CHECKED IN' AND(reservation_id LIKE @search OR guest.first_name LIKE @search OR guest.last_name LIKE @search OR room.room_name LIKE @search)", CONNECTION.CON))
{
da.SelectCommand.Parameters.AddWithValue("@search", "%" + txt_search.Text + "%");
DataTable getreserv = new DataTable();
da.Fill(getreserv);
lst_load_reserv.Items.Clear();
if (getreserv.Rows.Count > 0)
{
foreach (DataRow dr in getreserv.Rows)
{
ListViewItem lt = new ListViewItem(dr["reservation_id"].ToString());
lt.SubItems.Add(dr["room_name"].ToString());
lt.SubItems.Add(dr["first_name"].ToString());
lt.SubItems.Add(dr["last_name"].ToString());
lt.SubItems.Add(Convert.ToDateTime(dr["arrival_date"].ToString()).ToString("yyyy-MM-dd"));
lt.SubItems.Add(Convert.ToDateTime(dr["depature_Date"].ToString()).ToString("yyyy-MM-dd"));
lt.SubItems.Add(dr["status"].ToString());
lt.SubItems.Add(dr["room_id"].ToString());
lst_load_reserv.Items.Add(lt);
}
lst_load_reserv.Height = 140;
lst_load_reserv.Visible = true;
}
else
{
lst_load_reserv.Visible = false;
lst_load_reserv.Height = 0;
}
}
}
else
{
lst_load_reserv.Visible = false;
lst_load_reserv.Height = 0;
}
}
catch (Exception ex)
{
MSGBOX mdg = new MSGBOX(MessageAlertHeder.Error(), ex.Message, MessageAlertImage.Error());
mdg.ShowDialog();
}
finally
{
CONNECTION.close_connection();
}
}
private string ADD_OPEN_FOOD()
{
MySqlCommand cmd = CONNECTION.CON.CreateCommand();
MySqlTransaction myTrans;
myTrans = CONNECTION.CON.BeginTransaction();
cmd.Transaction = myTrans;
cmd.Connection = CONNECTION.CON;
try
{
cmd.CommandText = "INSERT INTO item (barcode,item_category,item_name,item_type_id,item_status,qty_handle) VALUES(@barcode,@item_category,@item_name,@item_type_id,@item_status,@qty_handle)";
cmd.Parameters.Clear();
cmd.Parameters.AddWithValue("@barcode", "N/A");
cmd.Parameters.AddWithValue("@item_category", 7);
cmd.Parameters.AddWithValue("@item_name", TXT_ITEM_NAME.Text);
cmd.Parameters.AddWithValue("@item_type_id", 1);
cmd.Parameters.AddWithValue("@item_status", "ENABLE");
cmd.Parameters.AddWithValue("@qty_handle", "0");
cmd.ExecuteNonQuery();
ADDED_ITEM_ID = GETMAXID();
cmd.CommandText = "UPDATE item SET barcode=@barcode WHERE item_id=@item_id";
cmd.Parameters.Clear();
cmd.Parameters.AddWithValue("@item_id", ADDED_ITEM_ID);
cmd.Parameters.AddWithValue("@barcode", ADDED_ITEM_ID);
cmd.ExecuteNonQuery();
cmd.CommandText = "INSERT INTO stock(item_code,qty,cost_price,sales_price) VALUES(@item_code,@qty,@cost_price,@sales_price)";
cmd.Parameters.Clear();
cmd.Parameters.AddWithValue("@item_code", ADDED_ITEM_ID);
cmd.Parameters.AddWithValue("@qty", 0.00);
cmd.Parameters.AddWithValue("@cost_price", Convert.ToDouble(TXT_COST.Text));
cmd.Parameters.AddWithValue("@sales_price", Convert.ToDouble(TXT_SALES_PRICE.Text));
cmd.ExecuteNonQuery();
myTrans.Commit();
return("done");
}
catch (Exception EX)
{
myTrans.Rollback();
MSGBOX mdg = new MSGBOX(MessageAlertHeder.Error(), EX.Message, MessageAlertImage.Error());
mdg.ShowDialog();
return("error");
}
finally
{
CONNECTION.close_connection();
}
}
private string SAVE_ROOM()
{
try
{
if (TXT_DESCRIPTION.Text == string.Empty)
{
TXT_DESCRIPTION.Text = "N/A";
}
CONNECTION.open_connection();
using (MySqlCommand cmd = new MySqlCommand("INSERT INTO room(room_name,room_package_id,room_floor,description,room_type) VALUES(@room_name,@room_package_id,@room_floor,@description,@room_type)", CONNECTION.CON))
{
cmd.Parameters.Clear();
cmd.Parameters.AddWithValue("@room_name", TXT_ROOM_NO.Text);
cmd.Parameters.AddWithValue("@room_package_id", CMB_ROOM_PKG.SelectedValue);
cmd.Parameters.AddWithValue("@room_floor", CMB_FLOOR.Text);
cmd.Parameters.AddWithValue("@description", TXT_DESCRIPTION.Text);
cmd.Parameters.AddWithValue("@room_type", CMB_ROOM_TYPE.Text);
if (cmd.ExecuteNonQuery() > 0)
{
return("done");
}
else
{
return("error");
}
}
}
catch (Exception ex)
{
if (ex.Message.Contains("Duplicate entry"))
{
MSGBOX mdg = new MSGBOX(MessageAlertHeder.Warning(), "ROOM NAME ALREADY EXSIST!", MessageAlertImage.Warning());
mdg.ShowDialog();
}
else
{
MSGBOX mdg = new MSGBOX(MessageAlertHeder.Error(), ex.Message, MessageAlertImage.Error());
mdg.ShowDialog();
}
return("error");
}
finally
{
CONNECTION.close_connection();
}
}
private void SAVE_ROOM_PACKAGE()
{
try
{
CONNECTION.open_connection();
using (MySqlCommand cmd = new MySqlCommand("INSERT INTO room_packages(package_name,description,`condition`,room_package_price,package_color) VALUES(@package_name,@description,@condition,@room_package_price,@package_color)", CONNECTION.CON))
{
cmd.Parameters.Clear();
cmd.Parameters.AddWithValue("@package_name", TXT_PKG_NAME.Text);
cmd.Parameters.AddWithValue("@description", TXT_DESCRIPTION.Text);
cmd.Parameters.AddWithValue("@condition", CMB_ROOM_TYPE.Text);
cmd.Parameters.AddWithValue("@room_package_price", Convert.ToDouble(TXT_PKG_RATE.Text));
cmd.Parameters.AddWithValue("@package_color", LBL_COLOR.Text);
if (cmd.ExecuteNonQuery() > 0)
{
int room_pkg_no = 0;
using (MySqlDataAdapter adp = new MySqlDataAdapter("SELECT MAX(room_package_id) FROM room_packages", CONNECTION.CON))
{
DataTable tbl = new DataTable();
adp.Fill(tbl);
if (tbl.Rows.Count > 0)
{
room_pkg_no = tbl.Rows[0].Field <Int32>(0);
}
}
LBL_PKG_ID.Text = room_pkg_no.ToString();
MSGBOX mdg = new MSGBOX(MessageAlertHeder.Success(), "PACKAGE ADDED SUCCESSFULLY!", MessageAlertImage.Success());
mdg.ShowDialog();
DisableControls();
}
else
{
MSGBOX mdg = new MSGBOX(MessageAlertHeder.Alert(), "FAILED TO ADD ROOM PACKAGE!", MessageAlertImage.Alert());
mdg.ShowDialog();
}
}
}
catch (Exception ex)
{
MSGBOX mdg = new MSGBOX(MessageAlertHeder.Error(), ex.Message, MessageAlertImage.Error());
mdg.ShowDialog();
}
finally
{
CONNECTION.close_connection();
}
}
private void BTN_ADD_SERVICE_Click(object sender, EventArgs e)
{
CONNECTION.open_connection();
try
{
if (CMB_SERVICES.SelectedIndex == -1)
{
MSGBOX mdg = new MSGBOX(MessageAlertHeder.Warning(), "Select a Service", MessageAlertImage.Warning());
mdg.ShowDialog();
CMB_SERVICES.DroppedDown = true;
}
else if (TXT_QTY.Text == "0.00")
{
MSGBOX mdg = new MSGBOX(MessageAlertHeder.Warning(), "Enter Quantity", MessageAlertImage.Warning());
mdg.ShowDialog();
TXT_QTY.Focus();
}
else
{
using (MySqlCommand addservice = new MySqlCommand("INSERT INTO additional_service(reservation_id,additional_serivice_id,additional_serivice_qty,additional_serivice_price,room_id) VALUES(@rid,@serviceid,@qty,@price,@roomid)", CONNECTION.CON))
{
addservice.Parameters.AddWithValue("@rid", Reservation_id);
addservice.Parameters.AddWithValue("@serviceid", CMB_SERVICES.SelectedValue);
addservice.Parameters.AddWithValue("@qty", TXT_QTY.Text);
addservice.Parameters.AddWithValue("@price", Convert.ToDouble(TXT_SUBTOTAL.Text));
addservice.Parameters.AddWithValue("@roomid", Room_id);
int n1 = addservice.ExecuteNonQuery();
if (n1 == 1)
{
DISPLAY_ADDED_SERVICES(Reservation_id, Room_id);
MSGBOX mdg = new MSGBOX(MessageAlertHeder.Success(), "Service Added Successfully", MessageAlertImage.Success());
mdg.ShowDialog();
}
}
}
}
catch (Exception ex)
{
MSGBOX mdg = new MSGBOX(MessageAlertHeder.Error(), ex.Message, MessageAlertImage.Error());
mdg.ShowDialog();
}
finally
{
CONNECTION.close_connection();
}
}
private void AddAgent()
{
CONNECTION.open_connection();
try
{
using (MySqlCommand addagent = new MySqlCommand("INSERT INTO agent(agent_name,address,contact_no,company_name,company_contact_no,commison_rate,description,added_by,added_date,added_time,agent_status) VALUES(@name,@address,@contact_no,@company_name,@copmany_contact_no,@commison_rate,@description,@added_by,@added_date,@added_time,@agent_status)", CONNECTION.CON))
{
addagent.Parameters.AddWithValue("@name", TXT_AGENT_NAME.Text);
addagent.Parameters.AddWithValue("@address", TXT_ADDRESS.Text);
addagent.Parameters.AddWithValue("@contact_no", TXT_CONTACT_NO.Text);
addagent.Parameters.AddWithValue("@company_name", TXT_COMPANY_NAME.Text);
addagent.Parameters.AddWithValue("@copmany_contact_no", TXT_COMPANY_CONTACT_NO.Text);
addagent.Parameters.AddWithValue("@commison_rate", TXT_COM_RATE.Text);
addagent.Parameters.AddWithValue("@description", TXT_DESCRIPTION.Text);
addagent.Parameters.AddWithValue("@added_by", CLS_CURRENT_LOGGER.LOGGED_IN_USERID);
addagent.Parameters.AddWithValue("@added_date", DateTime.Now.ToString("yyyy-MM-dd"));
addagent.Parameters.AddWithValue("@added_time", DateTime.Now.ToString("HH:mm:ss"));
addagent.Parameters.AddWithValue("@agent_status", "1");
int n1 = addagent.ExecuteNonQuery();
if (n1 == 1)
{
MySqlDataAdapter da = new MySqlDataAdapter("SELECT LAST_INSERT_ID()", CONNECTION.CON);
DataTable getlastid = new DataTable();
da.Fill(getlastid);
if (getlastid.Rows.Count > 0)
{
LBL_AGENT_ID.Text = getlastid.Rows[0][0].ToString();
}
LoadGrid();
MSGBOX mdg = new MSGBOX(MessageAlertHeder.Success(), "Saved Successfully", MessageAlertImage.Success());
mdg.ShowDialog();
EnableControls(false);
}
}
}
catch (Exception ex)
{
MSGBOX mdg = new MSGBOX(MessageAlertHeder.Error(), ex.Message, MessageAlertImage.Error());
mdg.ShowDialog();
}
finally
{
CONNECTION.close_connection();
}
}