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_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();
}
}
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 BTN_OK_Click_1(object sender, EventArgs e)
{
if (BTN_OK.ButtonText == "SAVE")
{
SAVE_ORDER = true;
if (TXT_SP_NOTE.Text == string.Empty)
{
SP_NOTE = "-";
}
else
{
SP_NOTE = TXT_SP_NOTE.Text;
}
}
else
{
string result = UPDATE_NOTE();
if (result == "done")
{
MSGBOX mdg = new MSGBOX(MessageAlertHeder.Success(), "ORDER NOTE UPDATED SUCCESSFULLY", MessageAlertImage.Success());
mdg.ShowDialog();
this.Close();
}
}
this.Close();
}
private void BTN_SERVED_Click(object sender, EventArgs e)
{
if (DGV_KOT.SelectedRows.Count > 0)
{
string result = MARK_ORDER_AS_SERVED();
if (result == "done")
{
MSGBOX mdg = new MSGBOX(MessageAlertHeder.Success(), "ORDER STATUS UPDATED AS SERVED", MessageAlertImage.Success());
mdg.ShowDialog();
}
}
}
private string CANCEL_ORDER(string order_no)
{
MySqlCommand cmd = new MySqlCommand();
MySqlTransaction myTrans = null;
try
{
CONNECTION.open_connection();
cmd.Connection = CONNECTION.CON;
myTrans = CONNECTION.CON.BeginTransaction(IsolationLevel.ReadCommitted);
cmd.Transaction = myTrans;
cmd.CommandText = "UPDATE kot_order SET order_status=@order_status WHERE order_no=@order_no";
cmd.Parameters.Clear();
cmd.Parameters.AddWithValue("@order_no", order_no);
cmd.Parameters.AddWithValue("@order_status", "Canceled");
cmd.ExecuteNonQuery();
using (MySqlDataAdapter adp = new MySqlDataAdapter("SELECT koi.item_stock_id,koi.order_qty,koi.unit_price,koi.total_price,i.qty_handle FROM kot_order_item koi JOIN stock s ON s.stock_id=koi.item_stock_id JOIN item i ON s.item_code=i.item_id WHERE koi.order_no=@order_no", CONNECTION.CON))
{
adp.SelectCommand.Parameters.Clear();
adp.SelectCommand.Parameters.AddWithValue("@order_no", order_no);
DataTable tbl = new DataTable();
adp.Fill(tbl);
if (tbl.Rows.Count > 0)
{
foreach (DataRow row in tbl.Rows)
{
if (row.Field <string>(4) == "1")
{
cmd.CommandText = "UPDATE stock SET qty=qty+@order_qty WHERE stock_id=@stock_id";
cmd.Parameters.Clear();
cmd.Parameters.AddWithValue("@order_qty", row.Field <double>(1));
cmd.Parameters.AddWithValue("@stock_id", row.Field <Int32>(0));
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");
}
}
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 void BTN_OK_Click_1(object sender, EventArgs e)
{
if (CMB_GUEST.SelectedIndex >= 0)
{
SELECTED_GUEST_ID = CMB_GUEST.SelectedValue.ToString();
SELECTED_GUEST_NAME = CMB_GUEST.Text;
this.Close();
}
else
{
MSGBOX mdg = new MSGBOX(MessageAlertHeder.Error(), "PLEASE SELECT GUSEST", MessageAlertImage.Error());
mdg.ShowDialog();
}
}
private void BTN_OK_Click_1(object sender, EventArgs e)
{
if (TXT_ITEM_NAME.Text == string.Empty)
{
MSGBOX mdg = new MSGBOX(MessageAlertHeder.Warning(), "PLEASE ENTER ITEM NAME", MessageAlertImage.Warning());
mdg.ShowDialog();
TXT_ITEM_NAME.Focus();
}
else
{
string result = ADD_OPEN_FOOD();
if (result == "done")
{
MSGBOX mdg = new MSGBOX(MessageAlertHeder.Success(), "OPEN FOOD ADDED SUCCESSFULLY", MessageAlertImage.Success());
mdg.ShowDialog();
this.Close();
}
}
}
private void BTN_CANCEL_ORDER_Click(object sender, EventArgs e)
{
if (DGV_KOT.SelectedRows.Count > 0)
{
DialogResult dialogResult = MessageBox.Show("Are you sure, Do you want to cancel the order ?", "Cancel Order", MessageBoxButtons.YesNo);
if (dialogResult == DialogResult.Yes)
{
string result = CANCEL_ORDER(DGV_KOT.SelectedRows[0].Cells["Column1"].Value.ToString());
if (result == "done")
{
MSGBOX mdg = new MSGBOX(MessageAlertHeder.Success(), "ORDER CANCELED SUCCESSFULLY", MessageAlertImage.Success());
mdg.ShowDialog();
}
}
}
else
{
MSGBOX mdg = new MSGBOX(MessageAlertHeder.Warning(), "PLEASE SELECT ORDER TO CANCEL", MessageAlertImage.Warning());
mdg.ShowDialog();
}
}
private string UPDATE_NOTE()
{
try
{
CONNECTION.open_connection();
using (MySqlCommand cmd = new MySqlCommand("UPDATE kot_order SET special_note=@special_note WHERE order_no=@order_no", CONNECTION.CON))
{
cmd.Parameters.Clear();
cmd.Parameters.AddWithValue("@order_no", LBL_ORDER_NO.Text);
if (TXT_SP_NOTE.Text != string.Empty)
{
cmd.Parameters.AddWithValue("@special_note", TXT_SP_NOTE.Text);
}
else
{
cmd.Parameters.AddWithValue("@special_note", "-");
}
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 ADD_NEW_GUEST()
{
try
{
string guest_id = CLS_GENERATE_ID.GEN_NEXT_GUEST_NO();
CONNECTION.open_connection();
using (MySqlCommand cmd = new MySqlCommand("INSERT INTO guest(guest_id,id_no,first_name,last_name) VALUES(@guest_id,@id_no,@first_name,@last_name)", CONNECTION.CON))
{
cmd.Parameters.Clear();
cmd.Parameters.AddWithValue("@guest_id", guest_id);
cmd.Parameters.AddWithValue("@id_no", TXT_NIC_NO.Text);
cmd.Parameters.AddWithValue("@first_name", TXT_FIRST_NAME.Text);
cmd.Parameters.AddWithValue("@last_name", TXT_LAST_NAME.Text);
if (cmd.ExecuteNonQuery() > 0)
{
added_guest_id = guest_id;
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();
}
}