//POPULATE LIST VIEW
private void populate(MySqlDataReader data)
{
//ImageList
ImageList imgList = new ImageList();
//CLEAR listview_Category items
listView1.Items.Clear();
//set listview category items as Large icons
listView1.View = View.LargeIcon;
//ADD image list into Listview
listView1.LargeImageList = populateImageList();
int i = 0;
//ADD image into imagelist and Listview
while (data.Read())
{
ListViewItem category = new ListViewItem();
category.ImageIndex = i;
//set Category name
category.Text = data.GetString(1);
category.Tag = data.GetString(0);
//set font list items styles
category.Font = new System.Drawing.Font("Courier New", 13, System.Drawing.FontStyle.Bold);
category.ForeColor = System.Drawing.Color.FromArgb(144, 30, 36);
category.SubItems.Add(data.GetString(0));
//ADD category items into Listview
listView1.Items.Add(category);
i++;
}
con.conClose();
}
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 void label16_Click(object sender, EventArgs e)
{
SessionData.SetNewOrderId(long.Parse(txt_selected_orderid.Text));
Form_auth frmauth = new Form_auth();
frmauth.ShowDialog();
if (managerPassword)
{
//canceled order
db con = new db();
string query = "";
query = "UPDATE `orders` SET `online`='0' WHERE id = '" + SessionData.newOrderId + "'";
con.MysqlQuery(query);
con.NonQueryEx();
con.conClose();
dataGridView2.Rows.Clear();
dataGridView2.Refresh();
loadOrderDetails();
}
else
{
MessageBox.Show("Authentication Faild!");
}
}
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;
}
//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 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();
}
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 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);
}
}
//create new order
private void createOrder(int orderType)
{
int orderstatus;
if (orderType.Equals(1))
{
orderstatus = 0;
}
else
{
if (paymentDone)
{
orderstatus = 1;
}
else
{
orderstatus = 0;
}
}
DateTime localDate = DateTime.Now;
int noOfGuest = SessionData.guest;
double cartPrice = SessionData.cartTotal;
con = new db();
string q = "insert into orders (created,guest,order_type,discount,service_charge,active,user_id) values('" + localDate + "','" + noOfGuest + "','Default','" + SessionData.discount + "','" + SessionData.serviceCharge + "','" + orderstatus + "','" + SessionData.userid + "')";
con.MysqlQuery(q);
con.NonQueryEx();
//SET ORDER ID
SessionData.SetNewOrderId(con.cmd.LastInsertedId);
con.conClose();
}
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 saveCardDetails()
{
db con = new db();
con.MysqlQuery("INSERT INTO paymentdetails (cardname,cardno,cardtype,amount,orders_id) VALUES('" + SessionData.cardname + "','" + SessionData.cardlastdigits + "','" + SessionData.cardtype + "','" + SessionData.lastbillamout + "','" + SessionData.newOrderId + "');");
con.NonQueryEx();
con.conClose();
}
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);
}
}
//create login log
void userLog(string user, string user_event)
{
con = new db();
string q = "insert into user_log (user_id,event) values('" + user + "','" + user_event + "')";
con.MysqlQuery(q);
con.NonQueryEx();
con.conClose();
}
private void updateOrderDiscount()
{
db con = new db();
string query = "";
query = "UPDATE `orders` SET `discount`='" + SessionData.discount + "' WHERE id = '" + SessionData.newOrderId + "'";
con.MysqlQuery(query);
con.NonQueryEx();
con.conClose();
}
private void updateGuestTabelDetails()
{
db con = new db();
string query = "";
query = "UPDATE `orders` SET `tabel`='" + SessionData.tabel + "' WHERE id = '" + SessionData.newOrderId + "'";
con.MysqlQuery(query);
con.NonQueryEx();
con.conClose();
}
void updateOrderPrintStatus(int id)
{
db con = new db();
string query = "";
query = "UPDATE `order_details` SET `print_status`= '1' WHERE order_id = '" + id + "' AND print_status = 0";
con.MysqlQuery(query);
con.NonQueryEx();
con.conClose();
}
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());
}
//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;
}
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());
}
void voidOrderItems(int itemid)
{
// MessageBox.Show(itemid.ToString());
db con = new db();
string query = "";
query = "UPDATE `order_details` SET `online`='0' WHERE id = '" + itemid + "'";
con.MysqlQuery(query);
con.NonQueryEx();
con.conClose();
reOpenOrder();
}
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();
}
//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());
}
//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;
}
//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;
}
//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;
}
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();
}
//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;
}
//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;
}
//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());
}