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;
}
}
