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