private void LOAD_DAY_MENU(string menu_date)
{
try
{
CONNECTION.open_connection();
using (MySqlDataAdapter adp = new MySqlDataAdapter("SELECT id,item_stock_id,i.barcode,meal_type,i.item_name FROM daily_menu dm JOIN stock s ON dm.item_stock_id=s.stock_id JOIN item i ON s.item_code=i.item_id WHERE menu_date=@menu_date", CONNECTION.CON))
{
adp.SelectCommand.Parameters.Clear();
adp.SelectCommand.Parameters.AddWithValue("@menu_date", menu_date);
DataTable tbl = new DataTable();
adp.Fill(tbl);
DGV_MENU.DataSource = null;
if (tbl.Rows.Count > 0)
{
DGV_MENU.AutoGenerateColumns = false;
DGV_MENU.DataSource = tbl;
}
}
}
catch (Exception EX)
{
MSGBOX mdg = new MSGBOX(MessageAlertHeder.Error(), EX.Message, MessageAlertImage.Error());
mdg.ShowDialog();
}
}
private void UPDATE_CLEAN_SERVICE()
{
try
{
CONNECTION.open_connection();
using (MySqlCommand DA = new MySqlCommand("UPDATE room SET current_status=@current_status WHERE room_id=@room_id", CONNECTION.CON))
{
DA.Parameters.Clear();
DA.Parameters.AddWithValue("@room_id", DGV_LIST.SelectedRows[0].Cells[1].Value.ToString());
DA.Parameters.AddWithValue("@current_status", "AVAILABLE");
DA.ExecuteNonQuery();
}
using (MySqlCommand DA = new MySqlCommand("UPDATE recerved_rooms SET house_keeping=@house_keeping WHERE reservation_no=@reservation_id AND room_id=@room_id", CONNECTION.CON))
{
DA.Parameters.Clear();
DA.Parameters.AddWithValue("@reservation_id", DGV_LIST.SelectedRows[0].Cells[0].Value.ToString());
DA.Parameters.AddWithValue("@house_keeping", "Yes");
DA.Parameters.AddWithValue("@room_id", DGV_LIST.SelectedRows[0].Cells[1].Value.ToString());
DA.ExecuteNonQuery();
MSGBOX mdg = new MSGBOX(MessageAlertHeder.Success(), "SELECTED ROOM CLEAN!", MessageAlertImage.Success());
mdg.ShowDialog();
}
}
catch (Exception EX)
{
MSGBOX mdg = new MSGBOX(MessageAlertHeder.Error(), EX.Message, MessageAlertImage.Error());
mdg.ShowDialog();
}
}
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 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_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 void LOAD_SUPPLIER_lIST()
{
try
{
CONNECTION.open_connection();
using (MySqlDataAdapter DA = new MySqlDataAdapter("SELECT supplier_id, supplier_name, company_name, address, contact1, contact2, note FROM supplier ", CONNECTION.CON))
{
DataTable DT = new DataTable();
DA.Fill(DT);
if (DT.Rows.Count > 0)
{
DGV_ITEM.DataSource = DT;
DGV_ITEM.AutoGenerateColumns = false;
}
else
{
DGV_ITEM.DataSource = null;
}
}
}
catch (Exception EX)
{
MSGBOX mdg = new MSGBOX(MessageAlertHeder.Error(), EX.Message, MessageAlertImage.Error());
mdg.ShowDialog();
}
}
private void LOAD_GRC()
{
try
{
String RES_NO = REPORT.RESERVATION_NO;
ReportDocument cryRpt = new ReportDocument();
cryRpt.Load(Application.StartupPath + "\\reports\\GRC.rpt");
ParameterFieldDefinitions crParameterFieldDefinitions;
ParameterFieldDefinition crParameterFieldDefinition;
ParameterValues crParameterValues = new ParameterValues();
ParameterDiscreteValue crParameterDiscreteValue = new ParameterDiscreteValue();
crParameterFieldDefinitions = cryRpt.DataDefinition.ParameterFields;
crParameterDiscreteValue.Value = RES_NO;
crParameterFieldDefinition = crParameterFieldDefinitions["RES"];
crParameterValues = crParameterFieldDefinition.CurrentValues;
crParameterValues.Clear();
crParameterValues.Add(crParameterDiscreteValue);
crParameterFieldDefinition.ApplyCurrentValues(crParameterValues);
crystalReportViewer1.ReportSource = cryRpt;
crystalReportViewer1.Refresh();
}
catch (Exception EX)
{
MSGBOX mdg = new MSGBOX(MessageAlertHeder.Error(), EX.Message, MessageAlertImage.Error());
mdg.ShowDialog();
}
}
private void LOAD_ITEM_lIST()
{
try
{
CONNECTION.open_connection();
using (MySqlDataAdapter DA = new MySqlDataAdapter("SELECT item.item_id , item.barcode , category.caegory_name , item.item_name , item.item_status, item.item_category,item_type.type,item.item_type_id FROM item INNER JOIN category ON (item.item_category = category.categry_id) INNER JOIN item_type ON (item.item_type_id=item_type.id)", CONNECTION.CON))
{
DataTable DT = new DataTable();
DA.Fill(DT);
if (DT.Rows.Count > 0)
{
DGV_ITEM.DataSource = DT;
DGV_ITEM.AutoGenerateColumns = false;
}
else
{
DGV_ITEM.DataSource = null;
}
}
}
catch (Exception EX)
{
MSGBOX mdg = new MSGBOX(MessageAlertHeder.Error(), EX.Message, MessageAlertImage.Error());
mdg.ShowDialog();
}
}
private void LOAD_ITEMS_lIST_SEARCH_BY_NAME(System.Windows.Forms.TextBox txt_cat_name)
{
try
{
CONNECTION.open_connection();
using (MySqlDataAdapter DA = new MySqlDataAdapter("SELECT item.item_id , item.barcode , category.caegory_name , item.item_name , item.item_status, item.item_category,item_type.type,item.item_type_id FROM item INNER JOIN category ON (item.item_category = category.categry_id) INNER JOIN item_type ON (item.item_type_id=item_type.id) WHERE item.item_name LIKE @item_name ", CONNECTION.CON))
{
DA.SelectCommand.Parameters.Clear();
DA.SelectCommand.Parameters.AddWithValue("@item_name", "%" + TXT_ITEMNAME.Text + "%");
DataTable DT = new DataTable();
DA.Fill(DT);
if (DT.Rows.Count > 0)
{
DGV_ITEM.DataSource = DT;
DGV_ITEM.AutoGenerateColumns = false;
}
else
{
DGV_ITEM.DataSource = null;
}
}
}
catch (Exception EX)
{
MSGBOX mdg = new MSGBOX(MessageAlertHeder.Error(), EX.Message, MessageAlertImage.Error());
mdg.ShowDialog();
}
}
private void LOAD_DAY_ORDERS_BY_STATUS()
{
try
{
CONNECTION.open_connection();
string query = "";
if (CMB_TYPE.SelectedIndex == 1)
{
query = "SELECT ko.order_no AS order_no,CONCAT_WS(' ',g.first_name,g.last_name) AS guest_name,IFNULL(ko.reservation_no,'-') AS reservation_no,IFNULL(rm.room_name,'-') AS room_name,IFNULL(rt.table_no,'-') AS table_no,ko.total_price AS total_price,ko.special_note FROM kot_order ko LEFT JOIN reservation r ON ko.reservation_no=r.reservation_id LEFT JOIN guest g ON r.guest_id=g.guest_id LEFT JOIN room rm ON ko.room_id=rm.room_id LEFT JOIN resturant_table rt ON ko.table_id=rt.table_id WHERE ko.added_date=CURDATE() AND ko.order_status=@order_status AND ko.order_status<>'Canceled' AND ko.room_id IS NOT NULL AND ko.table_id IS NULL";
}
else if (CMB_TYPE.SelectedIndex == 2)
{
query = "SELECT ko.order_no AS order_no,CONCAT_WS(' ',g.first_name,g.last_name) AS guest_name,IFNULL(ko.reservation_no,'-') AS reservation_no,IFNULL(rm.room_name,'-') AS room_name,IFNULL(rt.table_no,'-') AS table_no,ko.total_price AS total_price,ko.special_note FROM kot_order ko LEFT JOIN reservation r ON ko.reservation_no=r.reservation_id LEFT JOIN guest g ON r.guest_id=g.guest_id LEFT JOIN room rm ON ko.room_id=rm.room_id LEFT JOIN resturant_table rt ON ko.table_id=rt.table_id WHERE ko.added_date=CURDATE() AND ko.order_status=@order_status AND ko.order_status<>'Canceled' AND ko.table_id IS NOT NULL";
}
using (MySqlDataAdapter adp = new MySqlDataAdapter(query, CONNECTION.CON))
{
adp.SelectCommand.Parameters.Clear();
adp.SelectCommand.Parameters.AddWithValue("@order_status", CMB_STATUS.Text);
DataTable tbl = new DataTable();
adp.Fill(tbl);
DGV_KOT.DataSource = null;
if (tbl.Rows.Count > 0)
{
DGV_KOT.AutoGenerateColumns = false;
DGV_KOT.DataSource = tbl;
}
}
}
catch (Exception EX)
{
MSGBOX mdg = new MSGBOX(MessageAlertHeder.Error(), EX.Message, MessageAlertImage.Error());
mdg.ShowDialog();
}
}
public static double GetTotalTaxPercentage()
{
try
{
using (MySqlDataAdapter adp = new MySqlDataAdapter("SELECT IFNULL(SUM(precentage),0.00) FROM tax_details", CONNECTION.CON))
{
DataTable tbl = new DataTable();
adp.Fill(tbl);
if (tbl.Rows.Count > 0)
{
return(tbl.Rows[0].Field <double>(0));
}
else
{
return(0);
}
}
}
catch (Exception ex)
{
MSGBOX mdg = new MSGBOX(MessageAlertHeder.Error(), ex.Message, MessageAlertImage.Error());
mdg.ShowDialog();
return(0);
}
}
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 void BTN_PRINT_ORDER_Click(object sender, EventArgs e)
{
if (DGV_KOT.SelectedRows.Count > 0)
{
string result = MARK_ORDER_IN_PREPERATION();
if (result == "done")
{
PD_KOT_BILL.PrintPage += new PrintPageEventHandler(this.PD_KOT_BILL_PrintPage);
//PrintDialog printdlg = new PrintDialog();
//PrintPreviewDialog printPrvDlg = new PrintPreviewDialog();
//printPrvDlg.Document = PD_KOT_BILL;
//printPrvDlg.ShowDialog();
try
{
PD_KOT_BILL.Print();
}
catch (Exception ex)
{
MSGBOX mdg = new MSGBOX(MessageAlertHeder.Error(), ex.Message, MessageAlertImage.Error());
mdg.ShowDialog();
}
}
}
}
private void LOAD_ITEMS(int category_id)
{
try
{
CONNECTION.open_connection();
using (MySqlDataAdapter adp = new MySqlDataAdapter("SELECT s.stock_id,i.barcode,i.item_name,s.sales_price,s.item_code FROM stock s JOIN item i ON s.item_code=item_id WHERE i.item_category=@item_category AND i.item_status='ENABLE'", CONNECTION.CON))
{
adp.SelectCommand.Parameters.Clear();
adp.SelectCommand.Parameters.AddWithValue("@item_category", category_id);
DataTable tbl = new DataTable();
adp.Fill(tbl);
LST_ITEMS.Items.Clear();
if (tbl.Rows.Count > 0)
{
foreach (DataRow DR in tbl.Rows)
{
ListViewItem lt = new ListViewItem(DR["barcode"].ToString());
lt.SubItems.Add(DR["item_name"].ToString());
lt.SubItems.Add(DR["sales_price"].ToString());
lt.SubItems.Add(DR["item_code"].ToString());
lt.SubItems.Add(DR["stock_id"].ToString());
LST_ITEMS.Items.Add(lt);
}
}
}
}
catch (Exception EX)
{
MSGBOX mdg = new MSGBOX(MessageAlertHeder.Error(), EX.Message, MessageAlertImage.Error());
mdg.ShowDialog();
}
}
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 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_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 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 FILTER_ORDER_BY_ID()
{
try
{
CONNECTION.open_connection();
using (MySqlDataAdapter adp = new MySqlDataAdapter("SELECT ko.order_no AS order_no,CONCAT_WS(' ',g.first_name,g.last_name) AS guest_name,IFNULL(ko.reservation_no,'-') AS reservation_no,IFNULL(rm.room_name,'-') AS room_name,IFNULL(rt.table_no,'-') AS table_no,ko.total_price AS total_price,ko.special_note FROM kot_order ko LEFT JOIN guest g ON ko.guest_id=g.guest_id LEFT JOIN room rm ON ko.room_id=rm.room_id LEFT JOIN resturant_table rt ON ko.table_id=rt.table_id WHERE ko.added_date=CURDATE() AND ko.order_no LIKE @order_no AND ko.order_status<>'Canceled' ORDER BY ko.order_no", CONNECTION.CON))
{
adp.SelectCommand.Parameters.Clear();
adp.SelectCommand.Parameters.AddWithValue("@order_no", "%" + TXT_ORDER_NO.Text + "%");
DataTable tbl = new DataTable();
adp.Fill(tbl);
DGV_KOT.DataSource = null;
if (tbl.Rows.Count > 0)
{
DGV_KOT.AutoGenerateColumns = false;
DGV_KOT.DataSource = tbl;
}
}
}
catch (Exception EX)
{
MSGBOX mdg = new MSGBOX(MessageAlertHeder.Error(), EX.Message, MessageAlertImage.Error());
mdg.ShowDialog();
}
}
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 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_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_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 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 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 CHECK_ID_IS_AVAILABLE()
{
try
{
CONNECTION.open_connection();
using (MySqlDataAdapter da = new MySqlDataAdapter("SELECT guest_id, id_no, first_name, last_name, mobile_no, gender, passport_no, address, email, country_id FROM guest WHERE id_no=@id_no", CONNECTION.CON))
{
da.SelectCommand.Parameters.Clear();
da.SelectCommand.Parameters.AddWithValue("@id_no", TXT_ID_NUMBER.Text);
DataTable DT = new DataTable();
da.Fill(DT);
if (DT.Rows.Count > 0)
{
String guest_id = DT.Rows[0][0].ToString();
String id_no = DT.Rows[0][1].ToString();
String first_name = DT.Rows[0][2].ToString();
String last_name = DT.Rows[0][3].ToString();
String mobile_no = DT.Rows[0][4].ToString();
String gender = DT.Rows[0][5].ToString();
String passport_no = DT.Rows[0][6].ToString();
MessageBox.Show("THIS GUEST ALREADY REGISTERED" + Environment.NewLine + "GUEST ID: " + guest_id + Environment.NewLine + "NIC: " + id_no + Environment.NewLine + "FIRST NAME: " + first_name + Environment.NewLine + "LAST NAME: " + last_name + Environment.NewLine + "MOBILE NO: " + mobile_no, "ALERT", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
}
catch (Exception ex)
{
MSGBOX mdg = new MSGBOX(MessageAlertHeder.Error(), ex.Message, MessageAlertImage.Error());
mdg.ShowDialog();
}
}
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();
}
}
public static void FILL_COMBOBOX(hyflexComboBox CMB_CATEGORY, string QRY, string DisplayMember, string ValueMember, int SelectedIndex)
{
try
{
CONNECTION.open_connection();
using (MySqlDataAdapter DA = new MySqlDataAdapter(QRY, CONNECTION.CON))
{
DataTable DT = new DataTable();
DA.Fill(DT);
if (DT.Rows.Count > 0)
{
CMB_CATEGORY.DataSource = DT;
CMB_CATEGORY.DisplayMember = DisplayMember;
CMB_CATEGORY.ValueMember = ValueMember;
CMB_CATEGORY.SelectedIndex = SelectedIndex;
}
else
{
CMB_CATEGORY.DataSource = null;
}
}
}
catch (Exception EX)
{
MSGBOX mdg = new MSGBOX(MessageAlertHeder.Error(), EX.Message, MessageAlertImage.Error());
mdg.ShowDialog();
}
}
private void LOAD_BILL_LIST_BY_ROOM()
{
try
{
CONNECTION.open_connection();
using (MySqlDataAdapter DA = new MySqlDataAdapter("SELECT reservation.reservation_id, guest.guest_id, guest.first_name, guest.last_name, reservation.no_of_rooms, reservation.arrival_date, reservation.depature_date, reservation.no_of_nights, room.room_name FROM reservation INNER JOIN guest ON ( reservation.guest_id = guest.guest_id ) INNER JOIN recerved_rooms ON ( recerved_rooms.reservation_no = reservation.reservation_id ) INNER JOIN room ON ( recerved_rooms.room_id = room.room_id ) WHERE reservation.status = 'checked out' AND recerved_rooms.room_id=@room_id GROUP BY reservation.reservation_id", CONNECTION.CON))
{
DA.SelectCommand.Parameters.Clear();
DA.SelectCommand.Parameters.AddWithValue("@room_id", CMB_ROOM_NAME.SelectedValue);
DataTable DT = new DataTable();
DA.Fill(DT);
if (DT.Rows.Count > 0)
{
DGV_BILL_LIST.DataSource = DT;
DGV_BILL_LIST.AutoGenerateColumns = false;
}
else
{
DGV_BILL_LIST.DataSource = null;
}
}
}
catch (Exception EX)
{
MSGBOX mdg = new MSGBOX(MessageAlertHeder.Error(), EX.ToString(), MessageAlertImage.Error());
mdg.ShowDialog();
}
}