public static List <classes.Item> getAllItems() { List <classes.Item> items = new List <classes.Item>(); try { SQLiteDataReader dr = executeReader("select * from _item where itemDeleted=0"); if (dr != null) { classes.Item tmpItem = null; while (dr.Read()) { tmpItem = new classes.Item(); tmpItem.init(ref dr); if (tmpItem.inited) { items.Add(tmpItem); } tmpItem = null; } } } catch (Exception ex) { throw new Exception("Unable to retrieve stock items from database."); } return(items); }
public static List <classes.Item> getStockReport() { List <classes.Item> items = new List <classes.Item>(); try { List <classes.Item> allItems = getAllItems(); foreach (classes.Item item in allItems) { double purchased = 0, saled = 0; if (getItemReport(item.id, ref purchased, ref saled)) { classes.Item newItem = item; newItem.itemPPU = purchased; newItem.discount = saled; newItem.qty = purchased - saled; items.Add(newItem); } } } catch (Exception ex) { throw new Exception("Unable retrieve stock report. [" + ex.Message + "]"); } return(items); }
public static List <classes.Item> getItem_TransactionDetails(long itemId) { List <classes.Item> items = new List <classes.Item>(); try { SQLiteDataReader dr = executeReader("select tranId, tranTime, tranType, clientName, dataQty, dataDiscount, dataPPU from (select tranId, tranTime, tranType, clientName from _transaction, _client where tranClientId=clientId)a, _transaction_data where dataTranId=tranId and dataItemId=@itemId order by tranTime desc, tranId desc", new SQLiteParameter[] { new SQLiteParameter("@itemId", itemId) }); if (dr != null) { classes.Item tmpItem = null; while (dr.Read()) { tmpItem = new classes.Item(); try { tmpItem.id = long.Parse(dr["tranId"].ToString()); tmpItem.itemName = dr["clientName"].ToString(); tmpItem.itemCode = dr["tranType"].ToString(); tmpItem.qty = double.Parse(dr["dataQty"].ToString()); switch (tmpItem.itemCode) { case "PURCHASE": tmpItem.itemCode = "IN"; break; case "SALE": tmpItem.itemCode = "OUT"; tmpItem.qty = 0 - tmpItem.qty; break; default: tmpItem.itemCode = tmpItem.itemCode + ""; break; } tmpItem.itemPPU = double.Parse(dr["dataPPU"].ToString()); tmpItem.extraData = (DateTime?)dr["tranTime"]; tmpItem.inited = true; } catch (Exception) { tmpItem = null; } finally { if (tmpItem != null && tmpItem.inited) { items.Add(tmpItem); } } } } } catch (Exception ex) { throw new Exception("Unable to retrieve stock report items from database."); } return(items); }
public static double getAvailableItem(long itemId) { try { List <classes.Item> items = getStockReport(); classes.Item item = items.Find(x => (x.id == itemId)); if (item != null) { return(item.qty); } } catch (Exception ex) { throw new Exception("Unable to retrieve available items from database. [" + ex.Message + "]"); } return(0); }
public static classes.Item findItem(String code, String name) { classes.Item item = null; try { SQLiteDataReader dr = Job.Database.executeReader("select * from _item where itemName LIKE '" + name + "' or itemCode LIKE '" + code + "' limit 1"); if (dr != null) { if (dr.Read()) { item = new classes.Item(); item.init(ref dr); return(item.inited ? item : null); } } } catch (Exception ex) { throw new Exception("Unable to find item data by code/name."); } return(item); }
private static void PDoc_PrintPage(object sender, System.Drawing.Printing.PrintPageEventArgs e) { long tranId = 0; string client_name = "", client_address = ""; double total_price = 0, total_discount = 0; DateTime? tran_datetime = default(DateTime?); List <classes.Item> items = new List <classes.Item>(); bool print = false; SQLiteDataReader dr = Database.executeReader("select * from _transaction where tranId=@id", new SQLiteParameter[] { new SQLiteParameter("@id", printTranID) }); if (dr.Read()) { tranId = long.Parse(dr["tranId"].ToString()); long clientId = long.Parse(dr["tranClientId"].ToString()); total_price = double.Parse(dr["tranTotalPrice"].ToString()); try { total_discount = double.Parse(dr["tranTotalDiscount"].ToString()); } catch (Exception) { } try { tran_datetime = (DateTime?)dr["tranTime"]; } catch (Exception) { } if (clientId == 0) { } else { SQLiteDataReader dr2 = Database.executeReader("select * from _client where clientId=@id", new SQLiteParameter[] { new SQLiteParameter("@id", clientId) }); if (dr2.Read()) { client_name = dr2["clientName"].ToString(); client_address = dr2["clientAddress"].ToString(); } dr2.Close(); } SQLiteDataReader dr1 = Database.executeReader("select * from _transaction_data,_item where dataItemId=itemId and dataTranId=@id", new SQLiteParameter[] { new SQLiteParameter("@id", printTranID) }); while (dr1.Read()) { classes.Item item = new classes.Item(); item.itemName = dr1["itemName"].ToString(); item.qty = double.Parse(dr1["dataQty"].ToString()); item.itemPPU = (item.qty * double.Parse(dr1["dataPPU"].ToString())) - double.Parse(dr1["dataDiscount"].ToString()); items.Add(item); } print = true; } dr.Close(); if (print) { Font baseFont = new Font(FontFamily.GenericSansSerif, 16); SolidBrush blackBrush = new SolidBrush(Color.Black); SolidBrush grayBrush = new SolidBrush(Color.Gray); Graphics g = e.Graphics; float x = 10, y = 10; g.DrawString(Properties.Settings.Default.appOwner, baseFont, blackBrush, x, y); y += baseFont.Height + 5; baseFont = new Font(FontFamily.GenericSansSerif, 14); g.DrawString("Shope No.4, Om Complex", baseFont, grayBrush, x, y); y += baseFont.Height + 5; g.DrawString("Keriya Bypass Chowkdi", baseFont, grayBrush, x, y); y += baseFont.Height + 5; g.DrawString("Amreli - 365601", baseFont, grayBrush, x, y); //y += baseFont.Height + 5; baseFont = new Font(FontFamily.GenericSansSerif, 14); //g.DrawString("", baseFont, blackBrush, x, y); g.DrawString("INVOICE NUMBER", baseFont, blackBrush, 400, 10); g.DrawString("#" + tranId, new Font(baseFont.FontFamily, 10), blackBrush, 400, 10 + baseFont.Height + 5); y += baseFont.Height + 15; g.DrawLine(new Pen(blackBrush, 10), x, y, e.PageBounds.Width - 10, y); y += baseFont.Height; baseFont = new Font(FontFamily.GenericSansSerif, 10); g.DrawString("Bill To:", new Font(baseFont, FontStyle.Bold), blackBrush, x, y); x += g.MeasureString("Bill To: ", baseFont).Width + 5; g.DrawString(client_name, baseFont, blackBrush, x, y); if (tran_datetime.HasValue) { string dateTimeString = tran_datetime.Value.ToString("dd-MM-yyyy hh:mm tt"); float widthOfDTS = g.MeasureString(dateTimeString, baseFont).Width; g.DrawString(dateTimeString, new Font(baseFont, FontStyle.Italic), blackBrush, e.PageBounds.Width - widthOfDTS - 20, y); } int gap1 = 10, gap2 = 250, gap3 = 200; y += (baseFont.Height * 2); x = gap1; g.DrawString("Items", new Font(baseFont, FontStyle.Bold), blackBrush, x, y); x += gap2; g.DrawString("Qty x Rate", new Font(baseFont, FontStyle.Bold), blackBrush, x, y); x += gap3; g.DrawString("Amount", new Font(baseFont, FontStyle.Bold), blackBrush, x, y); y += baseFont.Height + 5; x = gap1; g.DrawLine(new Pen(blackBrush, 1), x, y, e.PageBounds.Width - 10, y); y += baseFont.Height; foreach (classes.Item item in items) { x = gap1; g.DrawString(item.itemName, baseFont, blackBrush, x, y); x += gap2; double itemRate = item.itemPPU / item.qty; g.DrawString(item.qty.ToString("0.00") + " x " + itemRate.ToString("0.00"), baseFont, blackBrush, x, y); x += gap3; g.DrawString(item.itemPPU.ToString("0.00"), baseFont, blackBrush, x, y); y += baseFont.Height + 7; g.DrawLine(new Pen(new SolidBrush(Color.DimGray), 1), 10, y, e.PageBounds.Width - 10, y); y += 7; } x = gap1; //g.DrawLine(new Pen(blackBrush, 5), x, y, e.PageBounds.Width - 10, y); y += baseFont.Height + 10; x = gap2 + gap1; g.DrawString("Total: ", baseFont, blackBrush, x, y); x = gap1 + gap2 + gap3; g.DrawString((total_discount + total_price).ToString("0.00"), baseFont, blackBrush, x, y); y += baseFont.Height + 5; g.DrawLine(new Pen(new SolidBrush(Color.DimGray), 1), x - gap3, y, e.PageBounds.Width - 10, y); y += 5; x = gap1 + gap2; g.DrawString("Discount: ", baseFont, blackBrush, x, y); x = gap1 + gap2 + gap3; g.DrawString(total_discount.ToString("0.00"), baseFont, blackBrush, x, y); y += baseFont.Height + 5; g.DrawLine(new Pen(new SolidBrush(Color.DimGray), 1), x - gap3, y, e.PageBounds.Width - 10, y); y += 5; x = gap1 + gap2; g.DrawString("Final Amount: ", baseFont, blackBrush, x, y); x = gap1 + gap2 + gap3; g.DrawString(total_price.ToString("0.00"), baseFont, blackBrush, x, y); y += baseFont.Height + 5; g.DrawLine(new Pen(new SolidBrush(Color.DimGray), 1), x - gap3, y, e.PageBounds.Width - 10, y); y += 5; x = 10; y = e.PageBounds.Height - baseFont.Height * 4; g.DrawLine(new Pen(new SolidBrush(Color.DimGray), 1), x, y, e.PageBounds.Width - 10, y); y += 15; g.DrawString("Chintan Patel", new Font(baseFont, FontStyle.Bold), blackBrush, x, y); g.DrawString("Vijay Patel", new Font(baseFont, FontStyle.Bold), blackBrush, e.PageBounds.Width - 15 - g.MeasureString("Vijay Patel", baseFont).Width, y); y += baseFont.Height + 5; g.DrawString("+91-9408453132", baseFont, blackBrush, x, y); g.DrawString("+91-9824966001", baseFont, blackBrush, e.PageBounds.Width - 10 - g.MeasureString("+91-9824966001", baseFont).Width, y); //total_price.ToString("0.00") //total_discount.ToString("0.00") //double vat = total_price * Properties.Settings.Default.appSettingVAT / 100; //g.DrawString("VAT: " + vat.ToString("0.00"), baseFont, blackBrush, x, y); //vat += total_price; } }