public static IEnumerable <Statistics> CountTotalProducts(IMSEntities db) { string objs = @" select sum(QuantityRemaining) as 'TotalProducts' from Stock"; var result = db.Database.SqlQuery <Statistics>(objs); return(result); }
public static IEnumerable <Statistics> CountCustomers(IMSEntities db) { string objs = @" select count(UserAccountID) as 'TotalCustomers' from UserAccounts where usertypeid = 3"; var result = db.Database.SqlQuery <Statistics>(objs); return(result); }
public static IEnumerable <Statistics> CountSuppliers(IMSEntities db) { string objs = @" select count(supplierid) as 'TotalSuppliers' from Suppliers"; var result = db.Database.SqlQuery <Statistics>(objs); return(result); }
public static IEnumerable <ProductList> GetItemsOutOfStock(IMSEntities db) { string objs = @" select p.ProductName, s.QuantityRemaining from stock s inner join Product p on s.productid=p.ProductID where s.QuantityRemaining = 0"; var result = db.Database.SqlQuery <ProductList>(objs); return(result); }
public static IEnumerable <Statistics> Sales(IMSEntities db) { string objs = @" select sum(BillingAmount) as 'TotalSales' from Sales s where s.DateCreated > (CAST(CAST(GETDATE() AS DATE) AS DATETIME))"; var result = db.Database.SqlQuery <Statistics>(objs); return(result); }
public static IEnumerable <ProductCount> Count4(IMSEntities db) { string objs = @" select sum(s.QuantityRemaining) as 'Total' from Product p inner join stock s on p.productid = s.ProductID inner join ProductType pt on p.ProductTypeID = pt.ProductTypeID where pt.ProductTypeID = 4"; var result = db.Database.SqlQuery <ProductCount>(objs); return(result); }
public static IEnumerable <PurchaseInfo> InactiveCustomerList(IMSEntities db) { string objs = @" select distinct ua.UserAccountID, ua.Email, ud.FirstName, ud.LastName, ps.DateCreated as 'LastPurchase' from ProductSales ps inner join Sales s on ps.SalesID = s.SalesID inner join UserAccounts ua on s.UserAccountID = ua.UserAccountID inner join UserDetails ud on ua.UserAccountID = ud.UserAccountID where ps.DateCreated <= (GETDATE()-31)"; var result = db.Database.SqlQuery <PurchaseInfo>(objs); return(result); }
public static IEnumerable <ProductList> GetItemsNotSold31Days(IMSEntities db) { string objs = @" select distinct p.ProductID, p.ProductName, p.Description, pt.ProductTypeName, ps.DateCreated as 'LastSold' from ProductSales ps inner join product p on ps.ProductID = p.ProductID inner join ProductType pt on p.ProductTypeID = pt.ProductTypeID inner join stock s on ps.ProductID = s.ProductID where ps.DateCreated <= (GETDATE() - 31) AND s.QuantityRemaining != 0"; var result = db.Database.SqlQuery <ProductList>(objs); return(result); }
public static IEnumerable <ProductDetails> GetProductDetails(IMSEntities db, int?ProductID) { SqlParameter proParam = new SqlParameter("ProductID", ProductID); string objs = @" select p.ProductID, p.ProductName, pt.ProductTypeName, p.PricePerItem, p.Description, p.DateCreated, s.QuantityRemaining from product p inner join stock s on p.ProductID = s.ProductID inner join producttype pt on p.ProductTypeID = pt.ProductTypeID where p.ProductID = @ProductID"; object[] parameters = new object[] { proParam }; var result = db.Database.SqlQuery <ProductDetails>(objs, parameters); return(result); }
public static IEnumerable <ProductList> GetItemsStocked(IMSEntities db, int?ProductTypeID) { SqlParameter proTypeParam = new SqlParameter("ProductTypeID", ProductTypeID); string objs = @" select s.ProductId, p.ProductName, s.QuantityRemaining from ProductType pt inner join Product p on pt.ProductTypeID = p.ProductTypeID inner join stock s on p.ProductID = s.ProductID where pt.ProductTypeID = @ProductTypeID"; object[] parameters = new object[] { proTypeParam }; var result = db.Database.SqlQuery <ProductList>(objs, parameters); return(result); }
public static IEnumerable <Billing> GetBill(IMSEntities db, int?SalesID) { SqlParameter proParam = new SqlParameter("SalesID", SalesID); string objs = @" select p.ProductName, p.PricePerItem, s.BillingDate, s.BillingAmount, ps.Quantity, s.SalesID from product p inner join productsales ps on p.ProductID = ps.ProductID inner join sales s on ps.SalesID = s.SalesID where ps.SalesID = @SalesID"; object[] parameters = new object[] { proParam }; var result = db.Database.SqlQuery <Billing>(objs, parameters); return(result); }
public static IEnumerable <PurchaseInfo> GetPurchaseInfo(IMSEntities db, int?UserAccountID) { SqlParameter uaIDParam = new SqlParameter("UserAccountID", UserAccountID); string objs = @" select ua.UserAccountID, ud.firstname, ud.LastName, p.ProductName, ps.quantity, s.billingdate from UserDetails ud inner join UserAccounts ua on ud.UserAccountID=ua.UserAccountID inner join sales s on ua.UserAccountID=s.UserAccountID inner join ProductSales ps on ps.SalesID=s.SalesID inner join Product p on ps.ProductID=p.ProductID where s.BillingDate>= (GETDATE()-31) and ua.useraccountid = @UserAccountID"; object[] parameters = new object[] { uaIDParam }; var result = db.Database.SqlQuery <PurchaseInfo>(objs, parameters); return(result); }