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);
        }
Пример #6
0
        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);
        }
Пример #7
0
        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);
        }
Пример #9
0
        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);
        }
Пример #12
0
        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);
        }