public static bool CheckPOSIfLocked()
{
DataTable dt = DataBaseHelper.GetDB(string.Format(@"SELECT * FROM Reports WHERE transdate = '{0}' AND `readtype` = 3", DateTime.Now.ToString("yyyy-MM-dd")));
if (dt.Rows.Count > 0)
return true;
else
return false;
}
public static decimal GetOldGrandTotal(int type)
{
DataTable dt = DataBaseHelper.GetDB(string.Format(@"SELECT COALESCE(MAX(newgrandtotal),0) as oldt FROM Reports WHERE readtype = '{0}'", type));
if (dt.Rows.Count == 0 || dt == null)
return 0;
else
return Convert.ToDecimal(dt.Rows[0]["oldt"]);
}
public static string GetOR(bool min, DateTime date)
{
DataTable dt = DataBaseHelper.GetDB(string.Format(@"SELECT COALESCE({0}(invoicenumber),0) as InvNumber FROM TransactionHead WHERE transdate = '{1}'", min ? "MIN":"MAX", date.ToString("yyyy-MM-dd")));
if (dt.Rows.Count == 0 || dt == null)
return "0";
else
return dt.Rows[0]["InvNumber"].ToString();
}
public static int GetNextReadCount(int type)
{
DataTable dt = DataBaseHelper.GetDB(string.Format(@"SELECT COALESCE(MAX(readcount),0) as rcount FROM Reports WHERE readtype = '{0}'", type));
if (dt.Rows.Count == 0)
return 0;
else
return Convert.ToInt32(dt.Rows[0]["rcount"]) + 1;
}
public static string GetCurrentInvoice()
{
DataTable dt = DataBaseHelper.GetDB("SELECT invoicenumber FROM TransactionHead ORDER BY invoicenumber DESC LIMIT 1");
if (dt.Rows.Count == 0)
return "";
else
{
return dt.Rows[0]["invoicenumber"].ToString();
}
}
public static DateTime GetLastZReadDate()
{
DataTable dt = DataBaseHelper.GetDB(string.Format(@"SELECT MIN(date) as date FROM Reports WHERE readtype = 3"));
if (dt.Rows.Count == 0 || dt == null)
return DataHandler.GetMinimumSaleDate();
else
try
{
return Convert.ToDateTime(dt.Rows[0]["date"]);
}
catch { return DataHandler.GetMinimumSaleDate(); }
}
public static DateTime GetMinimumSaleDate()
{
DataTable dt = DataBaseHelper.GetDB(string.Format(@"SELECT MIN(date) as date FROM TransactionHead"));
if (dt.Rows.Count == 0 || dt == null)
return DateTime.Now;
else
try
{
return Convert.ToDateTime(dt.Rows[0]["date"]);
}
catch { return DateTime.Now; }
}
public static string GetNextTransNumber()
{
DataTable dt = DataBaseHelper.GetDB("SELECT transactionnumber FROM TransactionHead ORDER BY transactionnumber DESC LIMIT 1");
if (dt.Rows.Count == 0)
return "1";
else
{
string test = dt.Rows[0]["transactionnumber"].ToString();
long transnumbraw = Convert.ToInt64(test);
return (transnumbraw + 1).ToString();
}
}
public static string GetNextBarcode()
{
DataTable dt = DataBaseHelper.GetDB("SELECT barcode FROM Product ORDER BY barcode DESC LIMIT 1");
if (dt.Rows.Count == 0)
return "100000000001";
else
{
string test = dt.Rows[0]["barcode"].ToString();
long barcoderaw = Convert.ToInt64(test);
return (barcoderaw + 1).ToString();
}
}
public static string GetNextID(string table)
{
DataTable dt = DataBaseHelper.GetDB(string.Format(@"SELECT id FROM {0} ORDER BY id DESC LIMIT 1", table));
if (dt.Rows.Count == 0)
return "1";
else
{
string test = dt.Rows[0]["id"].ToString();
long idraw = Convert.ToInt64(test);
return (idraw + 1).ToString();
}
}
public static Transaction GetTransactionByInvoice(string invoice)
{
Transaction trans = new Transaction();
try
{
DataTable dt = DataBaseHelper.GetDB(string.Format(@"SELECT * FROM TransactionHead WHERE invoicenumber = '{0}' LIMIT 1", invoice));
if (dt.Rows.Count == 0)
return null;
else
{
trans = new Transaction
{
ID = Convert.ToInt64(dt.Rows[0]["id"]),
InvoiceNumber = dt.Rows[0]["invoicenumber"].ToString(),
TransactionNo = dt.Rows[0]["transactionnumber"].ToString(),
Sales = Convert.ToInt32(dt.Rows[0]["sales"].ToString()) == 1 ? true : false,
Date = Convert.ToDateTime(dt.Rows[0]["date"].ToString()),
TenderAmount = Convert.ToDecimal(dt.Rows[0]["tenderamount"])
};
DataTable dtdetail = DataBaseHelper.GetDB(string.Format(@"SELECT * From TransactionDetail WHERE headid = '{0}'", trans.ID));
if (dt.Rows.Count == 0)
return null;
else
{
foreach(DataRow dr in dtdetail.Rows)
{
string prodid = dr["productid"].ToString();
decimal price = Convert.ToDecimal(dr["price"]);
decimal qty = Convert.ToDecimal(dr["quantity"]);
Product prod = new Product();
prod.SetProductByID(prodid);
prod.Qty = qty;
prod.Price = price;
trans.productlist.Add(prod);
}
}
}
}
catch { return null; }
return trans;
}
private static bool Generate(int type, DateTime date)
{
try
{
Report report = new Report();
report.Date = date;
report.Type = type;
report.MinOR = DataHandler.GetOR(true, date);
report.MaxOR = DataHandler.GetOR(false, date);
report.OldGrandTotal = DataHandler.GetOldGrandTotal(type);
DataTable dt = DataBaseHelper.GetDB(string.Format(@"SELECT
SUM(TD.price * TD.quantity) AS sales,
COUNT(*) AS itemcount,
TH.transdate
FROM TransactionHead TH
LEFT JOIN TransactionDetail TD ON TD.headid = TH.id
WHERE
transdate = '{0}'
AND sales = 1 GROUP By TH.transdate", date.ToString("yyyy-MM-dd")));
DataTable dtvoid = DataBaseHelper.GetDB(string.Format(@"SELECT
SUM(TD.price * TD.quantity) AS voidsales,
COUNT(*) AS itemcount,
TH.transdate
FROM TransactionHead TH
LEFT JOIN TransactionDetail TD ON TD.headid = TH.id
WHERE
transdate = '{0}'
AND sales = 0 GROUP By TH.transdate", date.ToString("yyyy-MM-dd")));
if (dt.Rows.Count == 0)
{
report.VatableSales = 0;
report.NewGrandTotal = report.OldGrandTotal;
report.Vat = 0;
report.TransCount = 0;
report.SalesTransCount = 0;
report.VatableSales = 0;
report.SalesItemQty = 0;
}
else
{
report.VatableSales = Convert.ToDecimal(dt.Rows[0]["Sales"]);
report.NewGrandTotal = report.OldGrandTotal + report.VatableSales;
report.Vat = (report.VatableSales / 1.12M) * 0.12M;
report.TransCount = 0;
report.SalesItemQty = Convert.ToDecimal(dt.Rows[0]["itemcount"]);
DataTable dtcounts = DataBaseHelper.GetDB(string.Format(@"SELECT COUNT(*) AS cnt FROM TransactionHead WHERE sales = 1 and transdate = '{0}'", date.ToString("yyyy-MM-dd")));
report.SalesTransCount = Convert.ToDecimal(dtcounts.Rows[0]["cnt"]);
}
if (dtvoid.Rows.Count == 0)
{
report.VoidAmount = 0;
report.VoidTransCount = 0;
report.VoidItemQty = 0;
}
else
{
report.VoidAmount = Convert.ToDecimal(dtvoid.Rows[0]["voidsales"]);
report.VoidItemQty = Convert.ToDecimal(dtvoid.Rows[0]["itemcount"]);
DataTable dtcountsvoid = DataBaseHelper.GetDB(string.Format(@"SELECT COUNT(*) AS cnt FROM TransactionHead WHERE sales = 0 and transdate = '{0}'", date.ToString("yyyy-MM-dd")));
report.VoidTransCount = Convert.ToDecimal(dtcountsvoid.Rows[0]["cnt"]);
}
report.GrossAmount = report.VoidAmount + report.VatableSales;
report.TransCount = report.SalesTransCount + report.VoidTransCount;
DataBaseHelper.SetDB(@"DELETE FROM Reports WHERE readtype = '" + report.Type + "' AND transdate = '" + report.Date.ToString("yyyy-MM-dd") + "'");
report.ReadCount = DataHandler.GetNextReadCount(report.Type);
HardwareHelper.PrintReport(null, null, null, report);
return(DataHandler.SaveReport(report));
}
catch
{
return(false);
}
}