private int CheckUserAvailabel()
{
con = new db();
int status = 0;
SessionData.SetMd5PasswordToConvert(textBox2.Text);
string md5pass = SessionData.md5Password;
DataTable users;
DataTable shift;
int count;
string query = "SELECT id,username,user_type FROM users WHERE username='******' AND password = '******' AND users.user_type IN('C','A')";
con.MysqlQuery(query);
users = con.QueryEx();
count = users.Rows.Count;
if (count > 0)
{
//SELECT shift.id FROM `shift` WHERE shift.users_id = 2 AND shift.shift_end > "" ORDER BY shift.id DESC
SessionData.SetUserAuth(true);
SessionData.SetUserId(users.Rows[0][0].ToString());
SessionData.setauthType(users.Rows[0][2].ToString());
SessionData.setUser(users.Rows[0][1].ToString());
SessionData.SetTillOpenBalance(50000);
SessionData.SetTillOpenTime(DateTime.Now.ToString("HH:mm:ss"));
//SessionData.SetDefaultPrinter("CASHIER");
SessionData.SetDefaultPrinter("POS-80Series");
string shiftQuery = "SELECT shift.id,shift.users_id,users.username,shift.shift_no FROM `shift` JOIN users ON users.id = shift.users_id WHERE shift.shift_end IS NULL ORDER BY shift.id DESC LIMIT 1";
con.MysqlQuery(shiftQuery);
shift = con.QueryEx();
con.conClose();
//set shift
if (shift.Rows.Count > 0)
{
SessionData.SetUserShiftId(int.Parse(shift.Rows[0][0].ToString()));
SessionData.SetUserShiftNo(int.Parse(shift.Rows[0][3].ToString()));
}
if (shift.Rows.Count > 0 && shift.Rows[0][1].ToString() != SessionData.userid)
{
errormsg = "Previous Shift (" + shift.Rows[0][2].ToString() + ") was not Sign Out properly. could not start a new shift for (" + SessionData.user + "). please signout last shift.";
status = 2;
}
else
{
status = 1;
}
return(status);
}
else
{
return(status);
}
}
void createShift()
{
con = new db();
string shiftend = "";
DataTable result;
int shifNo = 0;
//find shif end or not
string queryForCheckShift = "select id,shift_end,shift_start from shift where shift_date = '" + DateTime.Now.ToString("yyyy-M-d") + "'";
con.MysqlQuery(queryForCheckShift);
result = con.QueryEx();
if (result.Rows.Count > 0)
{
for (int i = 0; i < result.Rows.Count; i++)
{
DataRow dr = result.Rows[i];
shiftend = dr["shift_end"].ToString();
}
}
if (result.Rows.Count == 0 || shiftend != "")
{
//set shift No
if (shiftend != "")
{
shifNo = result.Rows.Count + 1;
}
else
{
shifNo = 1;
}
//create a new shift
string q = "insert into shift (users_id,shift_date,shift_start,shift_no) values('" + SessionData.userid + "','" + DateTime.Now.ToString("yyyy-M-d") + "','" + DateTime.Now.ToString("yyyyMMddHHmmss") + "','" + shifNo + "')";
con.MysqlQuery(q);
long shiftid = con.NonQueryEx();
SessionData.SetUserShiftId(int.Parse(shiftid.ToString()));
DataTable shiftno;
string queryshiftno = "SELECT shift.shift_no FROM shift WHERE shift.id ='" + SessionData.shiftId + "' ";
con.MysqlQuery(queryshiftno);
shiftno = con.QueryEx();
SessionData.SetUserShiftNo(int.Parse(shiftno.Rows[0][0].ToString()));
}
con.conClose();
}
private void button1_Click(object sender, EventArgs e)
{
if (textBox1.Text != "")
{
SessionData.SetMd5PasswordToConvert(textBox1.Text);
string pass = SessionData.md5Password;
DataTable order;
con = new db();
string query = "SELECT * FROM `users` WHERE users.username='******' AND users.password = '******' AND users.user_type= 'A'";
con.MysqlQuery(query);
order = con.QueryEx();
con.conClose();
if (order.Rows.Count > 0)
{
tries = 0;
this.Close();
}
else
{
panel1.Visible = true;
tries++;
label1.Text = tries.ToString();
}
}
}
private void button1_Click(object sender, EventArgs e)
{
SessionData.SetTabelDetails(comboBox1.Text);
db con = new db();
DataTable activeTables;
con.MysqlQuery("SELECT COUNT(orders.id) AS activeOrders FROM `orders` WHERE orders.active =1 and orders.tabel = '" + comboBox1.Text + "' AND date(orders.created) = CURDATE()");
activeTables = con.QueryEx();
if (int.Parse(activeTables.Rows[0][0].ToString()) != 0)
{
//error message table not available
string msg = "Table " + comboBox1.Text + " is not available. please close the order first! or select another table.";
FormMessage frmmsg = new FormMessage(msg);
frmmsg.ShowDialog();
}
else
{
string query = "";
query = "UPDATE `orders` SET `tabel`= '" + SessionData.tabel + "' WHERE id = '" + _orderid + "'";
con.MysqlQuery(query);
con.NonQueryEx();
}
con.conClose();
this.Close();
}
//INSERT ORDER DETAILS
private void gridviewDataIntoDb()
{
db con = new db();
DataTable shift;
//get shift
string queryshift = "SELECT shift.id,shift.users_id,users.username,shift.shift_no FROM `shift` JOIN users ON users.id = shift.users_id WHERE shift.shift_end IS NULL ORDER BY shift.id DESC LIMIT 1";
con.MysqlQuery(queryshift);
shift = con.QueryEx();
for (int i = 0; i < dataGridView_cart.Rows.Count; i++)
{
//inset only new items
if (dataGridView_cart.Rows[i].Cells["newitem"].Value.Equals(1))
{
con.MysqlQuery("INSERT INTO order_details (order_id,product_id,size,qty,unit_price,kot_status,subtotal,item_type,shift_id,shift_no) VALUES('" + SessionData.newOrderId + "','" + dataGridView_cart.Rows[i].Cells["itemcode"].Value.ToString() + "','" + dataGridView_cart.Rows[i].Cells["size"].Value.ToString() + "','" + dataGridView_cart.Rows[i].Cells["qty"].Value.ToString() + "','" + double.Parse(dataGridView_cart.Rows[i].Cells["price"].Value.ToString()) + "','" + 0 + "','" + double.Parse(dataGridView_cart.Rows[i].Cells["subtotal"].Value.ToString()) + "','" + dataGridView_cart.Rows[i].Cells["item_type"].Value.ToString() + "','" + SessionData.shiftId + "','" + SessionData.shiftno + "');");
con.NonQueryEx();
}
}
con.conClose();
//update tabel details
updateGuestTabelDetails();
updateOrderDiscount();
}
private bool checkAuthUser(string user, string pass)
{
SessionData.SetMd5PasswordToConvert(textBox2.Text);
string md5pass = SessionData.md5Password;
DataTable order;
con = new db();
string query = "SELECT * FROM `users` WHERE users.password = '******' AND users.user_type= 'A'";
con.MysqlQuery(query);
order = con.QueryEx();
con.conClose();
string currentCellValue = string.Empty;
foreach (DataRow dr in order.Rows)
{
currentCellValue = dr["id"].ToString();
}
Form_display.authid = currentCellValue;
if (order.Rows.Count > 0)
{
return(true);
}
else
{
return(false);
}
}
private void button12_Click(object sender, EventArgs e)
{
if (textBox1.Text != "" && comboBox1.Text != "")
{
//check tabel is open or closed
//SELECT COUNT(orders.id) AS activeOrders FROM `orders` WHERE orders.active =1 and orders.tabel = 'G1' AND date(orders.created) = '2017-08-23'
db con = new db();
DataTable activeTables;
con.MysqlQuery("SELECT COUNT(orders.id) AS activeOrders FROM `orders` WHERE orders.active =1 and orders.tabel = '" + comboBox1.Text + "' AND date(orders.created) = CURDATE()");
activeTables = con.QueryEx();
con.conClose();
if (int.Parse(activeTables.Rows[0][0].ToString()) != 0)
{
//error message table not available
string msg = "Table " + comboBox1.Text + " is not available. please close the order first! or select another table.";
FormMessage frmmsg = new FormMessage(msg);
frmmsg.ShowDialog();
}
else
{
Form_display.tabelStatus = true;
SessionData.SetTabelDetails(comboBox1.Text);
SessionData.SetGuest(int.Parse(textBox1.Text));
this.Close();
}
}
}
void reOpenOrder()
{
resetAll();
dataGridView_cart.Rows.Clear();
dataGridView_cart.Refresh();
DataTable order;
con = new db();
con.MysqlQuery("SELECT orders.tabel,orders.discount,order_details.*,products.name,products.id AS itemcode FROM order_details JOIN products ON products.id=order_details.product_id JOIN orders ON orders.id = order_details.order_id WHERE order_details.order_id = '" + SessionData.newOrderId + "' AND order_details.online=1");
order = con.QueryEx();
con.conClose();
for (int i = 0; i < order.Rows.Count; i++)
{
DataRow dr = order.Rows[i];
dataGridView_cart.Rows.Add(
dr["id"].ToString(),
dr["itemcode"].ToString(),
dr["name"].ToString(),
dr["size"].ToString(),
dr["qty"].ToString(),
String.Format("{0:n}", double.Parse(dr["unit_price"].ToString())),
String.Format("{0:n}", calRowPrice(double.Parse(dr["unit_price"].ToString()), int.Parse(dr["qty"].ToString()))),
dr["item_type"].ToString(),
0 //newly added item or reopened item
);
SessionData.SetTabelDetails(dr["tabel"].ToString());
SessionData.SetDiscount(double.Parse(dr["discount"].ToString()));
SessionData.SetSeviceCharge(10);
SessionData.SetCartTotal(double.Parse(dr["unit_price"].ToString()) * int.Parse(dr["qty"].ToString()));
SessionData.SetCartItemCount(1);
}
updatemode = true;
}
void searchTabel()
{
db con = new db();
comboBox1.AutoCompleteMode = AutoCompleteMode.SuggestAppend;
comboBox1.AutoCompleteSource = AutoCompleteSource.CustomSource;
DataTable products;
con.MysqlQuery("select * from table_no");
products = con.QueryEx();
if (products.Rows.Count > 0)
{
Dictionary <string, string> comboSource = new Dictionary <string, string>();
for (int i = 0; i < products.Rows.Count; i++)
{
DataRow dr = products.Rows[i];
comboSource.Add(dr["id"].ToString(), dr["table_name"].ToString());
}
AutoCompleteStringCollection DataCollection = new AutoCompleteStringCollection();
comboBox1.DataSource = new BindingSource(comboSource, null);
comboBox1.DisplayMember = "Value";
comboBox1.ValueMember = "Key";
comboBox1.AutoCompleteSource = AutoCompleteSource.ListItems;
}
con.conClose();
comboBox1.Text = "";
}
private DataTable getProduct(string productID, string size)
{
//MessageBox.Show("SELECT products.*,product_sizes.price,product_sizes.size FROM `products` JOIN product_sizes ON product_sizes.products_id= products.id WHERE product_sizes.products_id='" + productID + "' AND product_sizes.size='" + size + "' AND products.online=1 ");
DataTable result;
con = new db();
con.MysqlQuery("SELECT products.*,products.id AS itemcode FROM `products` WHERE products.id='" + productID + "' AND products.online=1 ");
result = con.QueryEx();
con.conClose();
return(result);
}
void getTotalServicecharge()
{
DataTable result;
con = new db();
string query = "SELECT (IF(SUM(serviceCharge)IS NULL,0,SUM(serviceCharge))) AS total_service_charge FROM (SELECT (SUM(order_details.subtotal) * orders.service_charge/100) AS serviceCharge FROM `order_details` JOIN orders WHERE order_details.order_id = orders.id AND date(order_details.added) = date(CURDATE()) AND order_details.online=1 GROUP BY order_details.order_id ) T";
con.MysqlQuery(query);
result = con.QueryEx();
con.conClose();
_totalServiceCharge = double.Parse(result.Rows[0][0].ToString());
}
void getTotaldiscount()
{
DataTable result;
con = new db();
string query = "SELECT (IF(SUM(discount_price)IS NULL,0,SUM(discount_price))) AS total_discount_price FROM (SELECT (SUM(order_details.subtotal) * orders.discount/100) AS discount_price FROM `order_details` JOIN orders WHERE order_details.order_id = orders.id AND date(order_details.added) = date(CURDATE()) GROUP BY order_details.order_id ) T";
con.MysqlQuery(query);
result = con.QueryEx();
con.conClose();
_totalDiscount = double.Parse(result.Rows[0][0].ToString());
}
//get category sale
void getCategorySale()
{
DataTable result;
con = new db();
string query = "SELECT SUM(order_details.subtotal) AS sale,categories.name, COUNT(order_details.product_id) AS itemcount FROM order_details JOIN products ON products.id = order_details.product_id JOIN categories ON categories.id = products.category_id WHERE date(order_details.added) = CURDATE() AND order_details.online = 1 GROUP BY categories.id";
con.MysqlQuery(query);
result = con.QueryEx();
con.conClose();
_categorySale = result;
}
//card wise sale
void getCardwiseSale()
{
DataTable result;
con = new db();
string query = "SELECT COUNT(order_details.id) AS itemcount,(SUM(order_details.subtotal)) AS cardsale, (IF(paymentdetails.cardtype='','CASH',paymentdetails.cardtype)) AS cardtype FROM order_details JOIN orders ON orders.id=order_details.order_id JOIN paymentdetails ON paymentdetails.orders_id = order_details.order_id WHERE date(order_details.added) = CURDATE() AND order_details.online = 1 GROUP BY paymentdetails.cardtype ";
con.MysqlQuery(query);
result = con.QueryEx();
con.conClose();
_cardWiseSale = result;
}
//zreport data
//today total sale
void getTotalSale()
{
DataTable result;
con = new db();
string query = "SELECT (IF(SUM(order_details.subtotal)>0,FORMAT(SUM(order_details.subtotal),2),FORMAT(0,2))) AS totalsale FROM `order_details` WHERE date(order_details.added) = date(CURDATE()) AND order_details.online=1";
con.MysqlQuery(query);
result = con.QueryEx();
con.conClose();
_totalSale = double.Parse(result.Rows[0][0].ToString());
}
//cash sale shift
void getCashSaleShift()
{
DataTable result;
con = new db();
string query = "SELECT DISTINCT order_details.shift_no, (SELECT group_concat(DISTINCT order_details.order_id) FROM order_details) as order_id, (SELECT SUM(od.subtotal) FROM order_details od LEFT JOIN paymentdetails p ON p.orders_id = od.order_id WHERE date(od.added) = date(CURDATE()) AND od.online = 1 AND od.shift_no = order_details.shift_no AND p.orders_id IS NULL) AS cardsale FROM order_details LEFT JOIN paymentdetails ON paymentdetails.orders_id = order_details.order_id WHERE paymentdetails.orders_id IS NULL AND order_details.online = 1 AND date(order_details.added) = date(CURDATE())";
con.MysqlQuery(query);
result = con.QueryEx();
con.conClose();
_cashSaleShift = result;
}
//get void items
void getVoidItems()
{
DataTable result;
con = new db();
string query = "SELECT products.name,order_details.qty,order_details.subtotal,order_details.product_id FROM `order_details` JOIN products ON products.id = order_details.product_id WHERE date(order_details.added) = date(CURDATE()) AND order_details.online = 0 ";
con.MysqlQuery(query);
result = con.QueryEx();
con.conClose();
_voidItems = result;
}
void printVoidReceipt()
{
long orderId = SessionData.newOrderId;
DataTable result;
con = new db();
string query = "SELECT order_details.*,products.name AS itemname FROM order_details JOIN products ON products.id = order_details.product_id WHERE order_details.order_id = '" + orderId + "'";
con.MysqlQuery(query);
result = con.QueryEx();
con.conClose();
}
//get total discount and servicecharge
//card sale shift
void getCardSaleShift()
{
DataTable result;
con = new db();
//string query = "SELECT DISTINCT order_details.shift_no, (SELECT group_concat(DISTINCT order_details.order_id) FROM order_details) as order_id, (SELECT SUM(od.subtotal) FROM order_details od JOIN paymentdetails p WHERE date(od.added) = date(CURDATE()) AND od.online = 1 AND od.shift_no = order_details.shift_no AND p.orders_id = od.order_id) AS cardsale FROM order_details JOIN paymentdetails WHERE order_details.online = 1 AND paymentdetails.orders_id = order_details.order_id AND date(order_details.added) = date(CURDATE())";
string query = "SELECT DISTINCT order_details.shift_no, (SELECT group_concat(DISTINCT order_details.order_id) FROM order_details) as order_id, (SELECT SUM(od.subtotal+(od.subtotal*orders.service_charge/100)-(od.subtotal*orders.discount/100)) FROM order_details od JOIN paymentdetails p JOIN orders ON orders.id=od.order_id WHERE date(od.added) = date(CURDATE()) AND od.online = 1 AND od.shift_no = order_details.shift_no AND p.orders_id = od.order_id) AS cardsale FROM order_details JOIN paymentdetails WHERE order_details.online = 1 AND paymentdetails.orders_id = order_details.order_id AND date(order_details.added) = date(CURDATE())";
con.MysqlQuery(query);
result = con.QueryEx();
con.conClose();
_cardSaleShift = result;
}
//get category sale
void getCategorySale()
{
DataTable result;
con = new db();
// string query = "SELECT SUM(order_details.subtotal) AS sale,categories.name, COUNT(order_details.product_id) AS itemcount FROM order_details JOIN products ON products.id = order_details.product_id JOIN categories ON categories.id = products.category_id WHERE date(order_details.added) = CURDATE() AND order_details.online = 1 GROUP BY categories.id";
string query = "SELECT categories.id,(SUM(order_details.subtotal) + (SUM(order_details.subtotal)*orders.service_charge/100) ) - ( ( SUM(order_details.subtotal) + (SUM(order_details.subtotal)*orders.service_charge/100) ) *orders.discount/100) AS sale,categories.name, COUNT(order_details.product_id) AS itemcount FROM order_details JOIN products ON products.id = order_details.product_id JOIN categories ON categories.id = products.category_id JOIN orders ON orders.id=order_details.order_id WHERE date(order_details.added) = CURDATE() AND order_details.online = 1 AND orders.active =0 GROUP BY categories.id ";
con.MysqlQuery(query);
result = con.QueryEx();
con.conClose();
_categorySale = result;
}
//zreport data
//today total sale
void getTotalSale()
{
DataTable result;
con = new db();
//string query = "SELECT (IF(SUM(order_details.subtotal)>0,FORMAT(SUM(order_details.subtotal),2),FORMAT(0,2))) AS totalsale FROM `order_details` WHERE date(order_details.added) = date(CURDATE()) AND order_details.online=1";
string query = "SELECT (IF(SUM(order_details.subtotal)>0,FORMAT( SUM((order_details.subtotal + (order_details.subtotal*orders.service_charge/100))-((order_details.subtotal + (order_details.subtotal*orders.service_charge/100))*orders.discount/100)) ,2),FORMAT(0,2))) AS totalsale FROM `order_details` JOIN orders ON orders.id = order_details.order_id WHERE date(order_details.added) = date(CURDATE()) AND order_details.online=1 AND orders.active = 0";
con.MysqlQuery(query);
result = con.QueryEx();
con.conClose();
_totalSale = double.Parse(result.Rows[0][0].ToString());
}
void getTotaldiscount()
{
DataTable result;
con = new db();
//string query = "SELECT (IF(SUM(discount_price)IS NULL,0,SUM(discount_price))) AS total_discount_price FROM (SELECT (SUM(order_details.subtotal) * orders.discount/100) AS discount_price FROM `order_details` JOIN orders WHERE order_details.order_id = orders.id AND date(order_details.added) = date(CURDATE()) GROUP BY order_details.order_id ) T";// service charge calculation changed
//string query = "SELECT (IF(SUM(discount_price)IS NULL,0,SUM(discount_price))) AS total_discount_price FROM (SELECT (SUM((order_details.subtotal)+ (order_details.subtotal*orders.service_charge/100)) * orders.discount/100) AS discount_price FROM `order_details` JOIN orders WHERE order_details.order_id = orders.id AND date(order_details.added) = date(CURDATE()) GROUP BY order_details.order_id ) T";
string query = "SELECT sum((((select sum(order_details.subtotal) from order_details where order_details.order_id = orders.id AND online=1)+((select sum(order_details.subtotal) from order_details where order_details.order_id = orders.id AND online=1)*orders.service_charge/100))*orders.discount/100)) as discount from orders where online=1 AND active=0 AND date(orders.created)= date(CURDATE())";
con.MysqlQuery(query);
result = con.QueryEx();
con.conClose();
_totalDiscount = double.Parse(result.Rows[0][0].ToString());
}
//cash sale shift
void getCashSaleShift()
{
DataTable result;
con = new db();
//string query = "SELECT DISTINCT order_details.shift_no, (SELECT group_concat(DISTINCT order_details.order_id) FROM order_details) as order_id, (SELECT SUM(od.subtotal) FROM order_details od LEFT JOIN paymentdetails p ON p.orders_id = od.order_id WHERE date(od.added) = date(CURDATE()) AND od.online = 1 AND od.shift_no = order_details.shift_no AND p.orders_id IS NULL) AS cardsale FROM order_details LEFT JOIN paymentdetails ON paymentdetails.orders_id = order_details.order_id WHERE paymentdetails.orders_id IS NULL AND order_details.online = 1 AND date(order_details.added) = date(CURDATE())";
//string query = "SELECT DISTINCT order_details.shift_no, (SELECT group_concat(DISTINCT order_details.order_id) FROM order_details) as order_id, (SELECT SUM(od.subtotal+(od.subtotal*orders.service_charge/100)-(od.subtotal*orders.discount/100)) FROM order_details od LEFT JOIN paymentdetails p ON p.orders_id = od.order_id JOIN orders ON orders.id = od.order_id WHERE date(od.added) = date(CURDATE()) AND od.online = 1 AND od.shift_no = order_details.shift_no AND p.orders_id IS NULL) AS cardsale FROM order_details LEFT JOIN paymentdetails ON paymentdetails.orders_id = order_details.order_id WHERE paymentdetails.orders_id IS NULL AND order_details.online = 1 AND date(order_details.added) = date(CURDATE())"; // service charge calculation chnanged
string query = "SELECT DISTINCT order_details.shift_no, (SELECT group_concat(DISTINCT order_details.order_id) FROM order_details) as order_id, ( SELECT SUM( (od.subtotal+(od.subtotal*orders.service_charge/100))- ((od.subtotal+(od.subtotal*orders.service_charge/100))*orders.discount/100 ) ) FROM order_details od LEFT JOIN paymentdetails p ON p.orders_id = od.order_id JOIN orders ON orders.id = od.order_id WHERE orders.active = 0 AND date(od.added) = date(CURDATE()) AND od.online = 1 AND od.shift_no = order_details.shift_no AND p.orders_id IS NULL) AS cardsale FROM order_details LEFT JOIN paymentdetails ON paymentdetails.orders_id = order_details.order_id WHERE paymentdetails.orders_id IS NULL AND order_details.online = 1 AND date(order_details.added) = date(CURDATE())";
con.MysqlQuery(query);
result = con.QueryEx();
con.conClose();
_cashSaleShift = result;
}
private void switch_user_Click(object sender, EventArgs e)
{
//process logout'
DataTable shift;
con = new db();
string shiftQuery = "SELECT shift.id,shift.users_id,users.username FROM `shift` JOIN users ON users.id = shift.users_id WHERE shift.shift_end IS NULL ORDER BY shift.id DESC LIMIT 1";
con.MysqlQuery(shiftQuery);
shift = con.QueryEx();
con.conClose();
if (shift.Rows.Count > 0)
{
closeShift(int.Parse(shift.Rows[0][0].ToString()));
}
}
private void loadOrderDetails()
{
DataTable orders;
con = new db();
con.MysqlQuery("SELECT orders.id,orders.tabel,users.username,(SELECT COUNT(order_details.id) FROM order_details WHERE order_details.order_id=orders.id AND order_details.item_type=1) kotorders,(SELECT COUNT(order_details.id) FROM order_details WHERE order_details.order_id=orders.id AND order_details.item_type=2) botorders,(SELECT COUNT(order_details.id) FROM order_details WHERE order_details.order_id=orders.id AND order_details.online=0) canceledorders,(SELECT COUNT(order_details.id) FROM order_details WHERE order_details.order_id=orders.id) AS itemcount ,(SELECT SUM(order_details.subtotal) FROM order_details WHERE order_details.order_id=orders.id) AS due FROM orders JOIN order_details ON order_details.order_id = orders.id JOIN users ON users.id=orders.user_id WHERE (SELECT SUM(order_details.subtotal) FROM order_details WHERE order_details.order_id=orders.id)- (SELECT SUM(order_details.subtotal) FROM order_details WHERE order_details.order_id=orders.id)*orders.discount/100 + (SELECT SUM(order_details.subtotal) FROM order_details WHERE order_details.order_id=orders.id)*orders.service_charge/100 - orders.paid > 0 AND (SELECT COUNT(order_details.id) FROM order_details WHERE order_details.order_id=orders.id AND order_details.online=1) >0 GROUP BY orders.id ORDER BY orders.id DESC");
orders = con.QueryEx();
label24.Text = orders.Rows.Count.ToString();//orders count
int kotorders = 0;
int botorders = 0;
int cancelorders = 0;
string gtabel;
if (orders.Rows.Count > 0)
{
for (int i = 0; i < orders.Rows.Count; i++)
{
DataRow dr = orders.Rows[i];
if (dr["tabel"].ToString() != "")
{
gtabel = dr["tabel"].ToString();
}
else
{
gtabel = "NOT DEFINED";
}
dataGridView2.Rows.Add(
dr["id"].ToString(),
gtabel,
dr["itemcount"].ToString(),
String.Format("{0:n}", double.Parse(dr["due"].ToString())),
dr["username"].ToString()
);
kotorders += int.Parse(dr["kotorders"].ToString());
botorders += int.Parse(dr["botorders"].ToString());
cancelorders += int.Parse(dr["canceledorders"].ToString());
}
label28.Text = kotorders.ToString();
label27.Text = botorders.ToString();
label33.Text = cancelorders.ToString();
}
}
//total cash
void getCashSale()
{
DataTable result;
con = new db();
string query = "SELECT DISTINCT order_details.shift_no, (SELECT group_concat(DISTINCT order_details.order_id) FROM order_details) as order_id, (SELECT SUM(od.subtotal) FROM order_details od LEFT JOIN paymentdetails p ON p.orders_id = od.order_id WHERE date(od.added) = date(CURDATE()) AND od.online = 1 AND od.shift_no = order_details.shift_no AND p.orders_id IS NULL) AS cardsale FROM order_details LEFT JOIN paymentdetails ON paymentdetails.orders_id = order_details.order_id WHERE paymentdetails.orders_id IS NULL AND order_details.online = 1 AND date(order_details.added) = date(CURDATE())";
con.MysqlQuery(query);
result = con.QueryEx();
con.conClose();
if (result != null)
{
foreach (DataRow cash in result.Rows)
{
_totalcashsale = _totalcashsale + double.Parse(cash["cardsale"].ToString());
}
}
}
void processBotOrders(int orderid, string tabel, string std)
{
DataTable botorders;
con = new db();
string query = "select order_details.*,products.name from order_details join products on products.id = order_details.product_id where order_details.item_type = '2' and order_details.order_id ='" + orderid + "' AND order_details.print_status=0";
con.MysqlQuery(query);
botorders = con.QueryEx();
SessionData.SetStward(std);
if (botorders.Rows.Count > 0)
{
//print bot orders
Botprint botprint = new Botprint(orderid, botorders, tabel);
botprint.print(botprinter);//BOT PRINTER
// botprint.print("BOT");//BOT PRINTER
updateOrderPrintStatus(orderid);
}
con.conClose();
}
public Form_ChangeGuestTable(long orderid)
{
InitializeComponent();
_orderid = orderid;
searchTabel();
DataTable order;
label4.Text = _orderid.ToString();
db con = new db();
con.MysqlQuery("select tabel from orders where id = '" + _orderid + "'");
order = con.QueryEx();
con.conClose();
if (order.Rows.Count > 0)
{
label3.Text = order.Rows[0][0].ToString();
}
}
//get guest count
void getGuestCount()
{
DataTable result;
con = new db();
string query = "SELECT (IF(SUM(orders.guest) IS NULL,0,SUM(orders.guest))) AS guestcount FROM order_details JOIN orders ON orders.id = order_details.order_id WHERE date(order_details.added) = date(CURDATE())";
con.MysqlQuery(query);
result = con.QueryEx();
con.conClose();
if (result == null)
{
_guestCount = 0;
}
else
{
_guestCount = int.Parse(result.Rows[0][0].ToString());
}
}
//CREATE IMAGELITS
// Create and return imagelist for both categories and products
private ImageList populateImageList()
{
string table = "categories";
string query = "SELECT * FROM " + table + " WHERE online = 1";
if (!populateFolder.Equals("categories"))
{
table = "products";
query = "SELECT * FROM " + table + " WHERE online = 1 AND category_id = '" + category_id + "'";
}
else
{
table = "categories";
query = "SELECT * FROM " + table + " WHERE online = 1";
}
con = new db();
//ImageList
ImageList imgList = new ImageList();
imgList.Images.Clear();
DataTable iamges;
con.MysqlQuery(query);
iamges = con.QueryEx();
imgList.ImageSize = new Size(180, 180);
imgList.ColorDepth = ColorDepth.Depth32Bit;
foreach (DataRow row in iamges.Rows) // Loop over the rows.
{
try
{
imgList.Images.Add(Image.FromFile(@"c:/xampp/htdocs/mpos/images/" + populateFolder + "/" + row["image"].ToString()));
}
catch (Exception w)
{
MessageBox.Show(w.Message);
}
}
con.conClose();
return(imgList);
}
