public int EmptyCart(int userID) { string query = @"UPDATE Orders SET OrderStatusID = 4 WHERE userID = @userID"; return(CoroNacessitiesDBContext.getConnection().Execute(query, new { userID = userID })); }
public int AddProductToCart(int userID, int orderStatusID, int productID, int noOfProducts) { string queryOrder = @"INSERT INTO [CoroNacessitiesDB].dbo.Orders (UserID, OrderStatusID) VALUES (@UserID, @OrderStatusID) SELECT SCOPE_IDENTITY() ;"; int orderID = CoroNacessitiesDBContext.getConnection().ExecuteScalar <int>(queryOrder, new { UserId = userID, OrderStatusID = orderStatusID }); string queryOrderItem = @"INSERT INTO [CoroNacessitiesDB].dbo.OrderItem (OrderID, ProductID, NoOfProducts) VALUES (@OrderID, @ProductID, @NoOfProducts)"; return(CoroNacessitiesDBContext.getConnection().Execute(queryOrderItem, new { OrderID = orderID, ProductID = productID, NoOfProducts = noOfProducts })); }
public int AddUser(RegisterModel user) { string queryAddress = @"INSERT INTO [CoroNacessitiesDB].dbo.Address (ComplexName, UnitNumber, StreetName, StreetNumber, Suburb, CityID) VALUES (@ComplexName, @UnitNumber, @StreetName,@StreetNumber , @Suburb, @CityID) SELECT SCOPE_IDENTITY() ;"; int addressID = CoroNacessitiesDBContext.getConnection().ExecuteScalar <int>(queryAddress, new { ComplexName = user.complexName, UnitNumber = user.unitNumber, StreetName = user.streetName, StreetNumber = user.streetNumber, Suburb = user.suburb, CityID = user.cityID }); string queryUser = @"INSERT INTO [CoroNacessitiesDB].dbo.Users (Username, Password, Name, Surname, Email, ContactNo , AddressID) VALUES (@Username, @Password, @Name, @Surname, @Email, @ContactNo , @AddressID)"; return(CoroNacessitiesDBContext.getConnection().Execute(queryUser, new { Username = user.username, Password = user.password, Name = user.name, Surname = user.surname, Email = user.email, ContactNo = user.contactNo, AddressID = addressID })); }
public ProductModel GetProduct(int productID) { string query = @"SELECT * FROM [CoroNacessitiesDB].dbo.Product WHERE ProductID = @ProductID"; ProductModel result = CoroNacessitiesDBContext.getConnection().QuerySingleOrDefault <ProductModel>(query, new { ProductID = productID }); result.typeModel = GetAllProductTypes()[result.typeID - 1]; return(result); }
public List <ProductModel> GetAllProducts() { string query = @"SELECT * FROM [CoroNacessitiesDB].dbo.Product"; List <ProductModel> result = CoroNacessitiesDBContext.getConnection().Query <ProductModel>(query).AsList(); foreach (ProductModel pm in result) { pm.typeModel = GetAllProductTypes()[pm.typeID - 1]; } return(result); }
public List <ProductModel> GetAllUserProducts(int userID) { string query = @"SELECT * FROM [CoroNacessitiesDB].dbo.Product AS p WHERE p.UserID = @UserID"; List <ProductModel> result = CoroNacessitiesDBContext.getConnection().Query <ProductModel>(query, new { UserID = userID }).AsList(); foreach (ProductModel pm in result) { pm.typeModel = GetAllProductTypes()[pm.typeID - 1]; } return(result); }
public List <ProductModel> GetAllProductsInOrder(int userID, string statusDescription) { string query = @"SELECT * FROM [CoroNacessitiesDB].dbo.Product INNER JOIN OrderItem ON Product.productId=OrderItem.ProductID INNER JOIN Orders ON OrderItem.OrderID=Orders.OrderID INNER JOIN OrderStatus ON Orders.OrderStatusID=OrderStatus.OrderStatusID WHERE Orders.UserID = @UserID AND OrderStatus.OrderStatusDescription = @StatusDescription"; return(CoroNacessitiesDBContext.getConnection().Query <ProductModel>(query, new { UserID = userID, statusDescription = statusDescription }).AsList()); }
public int UpdateOrderStatus(int orderStatusID, OrderStatusModel orderStatus) { string query = @"UPDATE [CoroNacessitiesDB].dbo.OrderStatus SET OrderStatusID = @OrderStatusID, OrderStatusDescription = @OrderStatusDescription WHERE OrderID = @OrderID"; return(CoroNacessitiesDBContext.getConnection().Execute(query, new { OrderStatusID = orderStatus.orderStatusID, OrderStatusDescription = orderStatus.orderStatusDescription })); }
public List <OrderStatusModel> GetAllOrdersStatuses() { string query = @"SELECT * FROM [CoroNacessitiesDB].dbo.OrderStatus"; return(CoroNacessitiesDBContext.getConnection().Query <OrderStatusModel>(query).AsList()); }
public int RemoveOrderStatus(int orderStatusID) { string query = @"DELETE FROM [CoroNacessitiesDB].dbo.OrderStatus WHERE OrderStatusID = @OrderStatusID"; return(CoroNacessitiesDBContext.getConnection().Execute(query, new { OrderStatusID = orderStatusID })); }
public OrderStatusModel GetOrderStatus(int orderStatusID) { string query = @"SELECT * FROM [CoroNacessitiesDB].dbo.OrderStatus WHERE OrderStatusID = @OrderStatusID"; return(CoroNacessitiesDBContext.getConnection().QuerySingleOrDefault <OrderStatusModel>(query, new { OrderStatusID = orderStatusID })); }
public string GetUserPassword(string username) { string query = @"SELECT Password FROM [dbo].[Users] where Username=@Username;"; return(CoroNacessitiesDBContext.getConnection().ExecuteScalar <string>(query, new { Username = username })); }
public int RemoveProduct(int productID) { string query = @"DELETE FROM [CoroNacessitiesDB].dbo.Product WHERE ProductID = @ProductID"; return(CoroNacessitiesDBContext.getConnection().Execute(query, new { ProductID = productID })); }
// Products public int AddProduct(ProductModel product) { string query = @"INSERT INTO [CoroNacessitiesDB].dbo.Product (UserID, ProductName, ProductDescription, Price, ProductStatusID, TypeID, ProductImage) VALUES (@UserID, @ProductName, @ProductDescription, @Price, @ProductStatusID, @TypeID, @ProductImage)"; return(CoroNacessitiesDBContext.getConnection().Execute(query, new { ProductName = product.productName, ProductDescription = product.productDescription, Price = product.price, UserID = product.userID, ProductStatusID = product.productStatusID, TypeID = product.typeID, ProductImage = product.productImage, })); }
public int UpdateAddress(AddressModel address) { string query = @"UPDATE [CoroNacessitiesDB].dbo.Address SET ComplexName=@ComplexName, UnitNumber = @UnitNumber, StreetName=@StreetName , StreetNumber=@StreetNumber ,CityID= @CityID WHERE AddressID = @AddressID"; return(CoroNacessitiesDBContext.getConnection().Execute(query, new { ComplexName = address.complexName, UnitNumber = address.unitNumber, StreetName = address.streetName, StreetNumber = address.streetNumber, CityID = address.cityID, AddressID = address.addressID })); }
public int UpdateOrder(int orderID, OrdersModel order) { string query = @"UPDATE [CoroNacessitiesDB].dbo.Orders SET OrderID = @OrderID, UserID = @UserID, OrderStatusID = @OrderStatusID WHERE OrderID = @OrderID"; return(CoroNacessitiesDBContext.getConnection().Execute(query, new { OrderID = order.orderID, UserID = order.userID, OrderStatusID = order.orderStatusID })); }
public List <OrdersModel> GetAllOrdersForUser(int userID) { string query = @"SELECT * FROM [CoroNacessitiesDB].dbo.Orders WHERE UserID = @UserID"; return(CoroNacessitiesDBContext.getConnection().Query <OrdersModel>(query, new { UserID = userID }).AsList()); }
// Orders public int AddOrder(OrdersModel order) { string query = @"INSERT INTO [CoroNacessitiesDB].dbo.Orders (UserID, OrderStatusID) VALUES (@UserID, @OrderStatusID); SELECT Orders.OrderID FROM [CoroNacessitiesDB].dbo.Orders WHERE Orders.UserID = @UserId AND Orders.OrderStatusID = @OrderStatusID SELECT SCOPE_IDENTITY()"; return(CoroNacessitiesDBContext.getConnection().Execute(query, new { UserID = order.userID, OrderStatusID = order.orderStatusID })); }
public List <OrderItemModel> GetAllOrderItems(int orderID) { string query = @"SELECT * FROM [CoroNacessitiesDB].dbo.Orders WHERE OrderID = @OrderID"; return(CoroNacessitiesDBContext.getConnection().Query <OrderItemModel>(query, new { OrderID = orderID }).AsList()); }
public int UpdateOrderItems(int orderItemID, OrderItemModel orderItem) { string query = @"UPDATE [CoroNacessitiesDB].dbo.OrderItem SET OrderItemID = @OrderItemID, OrderID = @OrderID, ProductID = @ProductID, NoOfProducts = @NoOfProducts WHERE OrderItemID = @OrderItemID"; return(CoroNacessitiesDBContext.getConnection().Execute(query, new { OrderItemID = orderItem.orderItemID, OrderID = orderItem.orderID, ProductID = orderItem.productID, NoOfProducts = orderItem.noOfProducts })); }
public int UpdateProduct(int id, ProductModel product) { string query = @"UPDATE [CoroNacessitiesDB].dbo.Product SET ProductName = @ProductName, ProductDescription = @ProductDescription, Price = @Price, UserID = @UserID, ProductStatusID = @ProductStatusID, TypeID = @TypeID, ProductImage = @ProductImage WHERE ProductID = @ProductID"; return(CoroNacessitiesDBContext.getConnection().Execute(query, new { ProductName = product.productName, ProductDescription = product.productDescription, Price = product.price, UserID = product.userID, ProductStatusID = product.productStatusID, TypeID = product.typeID, ProductImage = product.productImage, ProductID = id })); }
public AddressModel GetAddress(int addressID) { string query = @"SELECT * FROM [CoroNacessitiesDB].dbo.Address WHERE AddressID = @AddressID"; return(CoroNacessitiesDBContext.getConnection().QuerySingleOrDefault <AddressModel>(query, new { AddressID = addressID })); }
//Location Details public LocationModel GetLocation(string username) { string query = @"SELECT * FROM [dbo].[LocationView] where Username=@Username;"; return(CoroNacessitiesDBContext.getConnection().QuerySingleOrDefault <LocationModel>(query, new { Username = username })); }
public List <CityModel> GetAllCities() { string query = @"SELECT * FROM [CoroNacessitiesDB].dbo.City"; return(CoroNacessitiesDBContext.getConnection().Query <CityModel>(query).AsList()); }
public List <TypeModel> GetAllProductTypes() { string query = @"SELECT * FROM [CoroNacessitiesDB].dbo.ProductType ORDER BY TypeID ASC"; return(CoroNacessitiesDBContext.getConnection().Query <TypeModel>(query).AsList()); }
// Order Status public int AddOrderStatus(OrderStatusModel orderStatus) { string query = @"INSERT INTO [CoroNacessitiesDB].dbo.OrderStatus (OrderStatusID, OrderStatusDescription) VALUES (@OrderStatusID, @OrderStatusDescription)"; return(CoroNacessitiesDBContext.getConnection().ExecuteScalar <int>(query, new { OrderStatusID = orderStatus.orderStatusID, OrderStatusDescription = orderStatus.orderStatusDescription })); }
//User Details public UserModel GetUserByUsername(string username) { string query = @"SELECT * FROM [dbo].[Users] where Username=@Username;"; return(CoroNacessitiesDBContext.getConnection().QuerySingleOrDefault <UserModel>(query, new{ Username = username })); }
// Order Items public int AddOrderItems(OrderItemModel orderItem) { string query = @"INSERT INTO [CoroNacessitiesDB].dbo.OrderItem (OrderID, ProductID, NoOfProducts) VALUES (@OrderID, @ProductID, @NoOfProducts)"; return(CoroNacessitiesDBContext.getConnection().Execute(query, new { OrderID = orderItem.orderID, ProductID = orderItem.productID, NoOfProducts = orderItem.noOfProducts })); }
public UserModel GetUserById(int userId) { string query = @"SELECT * FROM [dbo].[Users] where UserID=@UserID;"; return(CoroNacessitiesDBContext.getConnection().QuerySingleOrDefault <UserModel>(query, new { UserID = userId })); }
public int UpdateUser(UserModel user) { string query = @"UPDATE [CoroNacessitiesDB].dbo.Users SET Password=@Password , Name = @Name, Surname=@Surname , Email=@Email ,ContactNo= @ContactNo, Rating=@Rating ,PayPalInfo = @PayPalInfo WHERE UserID = @UserID"; return(CoroNacessitiesDBContext.getConnection().Execute(query, new { UserID = user.userId, Password = user.password, Name = user.name, Surname = user.surname, Email = user.email, ContactNo = user.contactNo, Rating = 0, PayPalInfo = "" })); }