public List <FuelPurchasesLedger> GetStocksPurchasesLedgers(Stations station, DateTime start, DateTime stop, string filter)
{
var ledger = new List <FuelPurchasesLedger>();
SqlServerConnection conn = new SqlServerConnection();
string q = conn.GetQueryString(filter, "CAST(qty*price AS NVARCHAR)+'-'+Category+'-'+Items+'-'+SuppInv+'-'+ISNULL(Names,'CASH')", "date_ BETWEEN '" + start.Date + "' AND '" + stop.Date + "'");
if (station != null)
{
q += " AND st=" + station.Id;
}
SqlDataReader dr = conn.SqlServerConnect("SELECT id, date_, qty, ISNULL(price,0)price, tax, Category, Items, SuppInv, Supp, Names, st_idnt, st_code, st_name FROM vStocksLedger INNER JOIN Stations ON st=st_idnt " + q + " ORDER BY date_, SuppInv, Items");
if (dr.HasRows)
{
while (dr.Read())
{
FuelPurchasesLedger item = new FuelPurchasesLedger {
Id = Convert.ToInt64(dr[0]),
Date = Convert.ToDateTime(dr[1]).ToString("dd-MMM"),
Ltrs = Convert.ToDouble(dr[2]),
Price = Convert.ToDouble(dr[3]),
Rate = Convert.ToDouble(dr[4]),
Category = dr[5].ToString(),
Description = dr[6].ToString(),
Invoice = dr[7].ToString(),
Supplier = new Suppliers {
Id = Convert.ToInt64(dr[8]),
Name = dr[9].ToString()
},
Station = new Stations {
Id = Convert.ToInt64(dr[10]),
Code = dr[11].ToString(),
Name = dr[12].ToString()
}
};
item.Total = item.Ltrs * item.Price;
if (item.Rate.Equals(0))
{
item.Zero = item.Total;
}
else
{
item.Vats = (item.Rate / (item.Rate * 100)) * item.Total;
}
item.Excl = item.Total - item.Vats - item.Zero;
ledger.Add(item);
}
}
return(ledger);
}
public List <FuelPurchasesLedger> GetFuelPurchasesLedgers(long stid, DateTime start, DateTime stop, string filter)
{
var ledgers = new List <FuelPurchasesLedger>();
SqlServerConnection conn = new SqlServerConnection();
SqlDataReader dr = conn.SqlServerConnect("SELECT id, dt, qty, price, descr, invs, total, tax_amount, pdo1, pdo2, pdon, st_name, sb_brand FROM vPurchasesLedger INNER JOIN Stations ON st_idnt=st INNER JOIN StationsBrand ON sb_idnt=st_brand " + conn.GetQueryString(filter, "CAST(qty AS NVARCHAR)+'-'+descr+'-'+invs+'-'+CAST(total AS NVARCHAR)+'-'+CAST(pdo1 AS NVARCHAR)+'-'+CAST(pdo2 AS NVARCHAR)", "st=" + stid + " AND dt BETWEEN '" + start.Date + "' AND '" + stop.Date + "'") + " ORDER BY dt, descr, id");
if (dr.HasRows)
{
while (dr.Read())
{
FuelPurchasesLedger item = new FuelPurchasesLedger();
item.Station.Id = stid;
item.Id = Convert.ToInt64(dr[0]);
item.Date = Convert.ToDateTime(dr[1]).ToString("dd-MMM");
item.Ltrs = Convert.ToDouble(dr[2]);
item.Price = Convert.ToDouble(dr[3]);
item.Description = dr[4].ToString();
item.Invoice = dr[5].ToString();
item.Total = Convert.ToDouble(dr[6]);
item.Vats = Convert.ToDouble(dr[7]);
item.PayCard = Convert.ToDouble(dr[8]);
item.PayAmts = Convert.ToDouble(dr[9]);
if (!String.IsNullOrEmpty(dr[10].ToString()))
{
item.PayDate = Convert.ToDateTime(dr[10].ToString()).ToString("dd-MMM");
}
item.Station.Name = dr[11].ToString().ToUpper();
item.Station.Brand.Name = dr[12].ToString().ToUpper();
item.Excl = (item.Vats / 0.08);
item.Zero = item.Total - item.Vats - item.Excl;
ledgers.Add(item);
}
}
return(ledgers);
}
