示例#1
0
        public static async Task <bool> UserExists(this IdapadDataAccess dataAccess, string userName)
        {
            User user = await dataAccess.QueryFirstOrDefaultAsync <User>
                            ("select * from Appuser where username = @username", new { userName });

            return((user != null) ? true : false);
        }
示例#2
0
        public static async Task <User> Register(this IdapadDataAccess dataAccess, UserRegister userRegister)
        {
            byte[] passwordHash, passwordSalt;

            CreatePasswordHash(userRegister.Password, out passwordHash, out passwordSalt);

            var userToRegister = new  UserToRegister
            {
                FirstName    = userRegister.FirstName,
                LastName     = userRegister.LastName,
                UserName     = userRegister.UserName.ToLower(),
                EmailAddress = userRegister.EmailAddress,
                PasswordHash = passwordHash,
                PasswordSalt = passwordSalt
            };

            var id = await dataAccess.ExecuteScalarAsync <int>(
                @"INSERT Appuser(FirstName, LastName, UserName, EmailAddress, PasswordHash, PasswordSalt)" +
                " output inserted.Id VALUES(@FirstName, @LastName, @UserName, @EmailAddress, @PasswordHash, @PasswordSalt)",
                userToRegister);

            var user = new User();

            user.Id           = id;
            user.FirstName    = userToRegister.FirstName;
            user.LastName     = userToRegister.LastName;
            user.UserName     = userToRegister.UserName;
            user.EmailAddress = userToRegister.EmailAddress;
            return(user);
        }
示例#3
0
 public static Task <User> GetUserByUserNameAsync(this IdapadDataAccess dataAccess, string userName)
 {
     return(dataAccess.QueryFirstOrDefaultAsync <User>
                ("select a.Id, a.FirstName, a.LastName, a.UserName, a.EmailAddress " +
                "from Appuser a " +
                "where a.UserName = @UserName", new { userName }));
 }
示例#4
0
 public static async Task <int> LinkFirmToUserAsync(this IdapadDataAccess dataAccess,
                                                    FirmUser firmUser)
 {
     return(await dataAccess.ExecuteScalarAsync <int>(
                "Insert FirmUsers (FirmId, UserId)  output inserted.Id values" +
                "(@FirmId, @UserId)", firmUser));
 }
示例#5
0
        public static async Task <User> Login(this IdapadDataAccess dataAccess, UserRegister userRegister)
        {
            var userName = userRegister.UserName.ToLower();

            UserToRegister userToRegister = await dataAccess.QueryFirstOrDefaultAsync <UserToRegister>
                                                ("select a.*,fu.FirmId, f.Name FirmName from Appuser a " +
                                                "left join FirmUsers fu On a.Id = fu.UserId  " +
                                                "left join Firm f On f.Id = fu.firmId  " +
                                                "where a.UserName = @UserName", new { userName });

            if (userToRegister == null)
            {
                return(null);
            }
            if (!VerifyPasswordHash(userRegister.Password, userToRegister.PasswordHash, userToRegister.PasswordSalt))
            {
                return(null);
            }

            var user = new User();

            user.Id           = userToRegister.Id;
            user.FirstName    = userToRegister.FirstName;
            user.LastName     = userToRegister.LastName;
            user.UserName     = userToRegister.UserName;
            user.EmailAddress = userToRegister.EmailAddress;
            user.FirmName     = userToRegister.FirmName;
            user.FirmId       = userToRegister.FirmId;
            return(user);
        }
示例#6
0
        public static async Task <IEnumerable <ProductType> > GetProductTypesAsync(this IdapadDataAccess dataAccess)
        {
            string sql = "SELECT  [Id], [Name] FROM dbo.ProductTypes";

            var productTypes = await dataAccess.QueryAsync <ProductType>(sql);

            return(productTypes);
        }
示例#7
0
        private static async Task <int> DoestheFirmExists(this IdapadDataAccess dataAccess,
                                                          Firm firm)
        {
            int firmId = await dataAccess.QueryFirstOrDefaultAsync <int>(
                "select * from Firm where Name = @Name", firm);

            return(firmId);
        }
示例#8
0
        private static async Task <int> DoestheFirmProductExists(this IdapadDataAccess dataAccess,
                                                                 FirmProduct firmProduct)
        {
            int firmProductId = await dataAccess.QueryFirstOrDefaultAsync <int>(
                "select Id from FirmProducts where ProductId = @ProductId and FirmId = @FirmId", firmProduct);

            return(firmProductId);
        }
示例#9
0
        public static async Task <bool> RemoveFirmProductAsync(this IdapadDataAccess dataAccess,
                                                               int Id)
        {
            await dataAccess.ExecuteScalarAsync <bool>(
                "Delete FirmProducts WHERE Id = " + Id);

            return(true);
        }
示例#10
0
 public static Task <User> GetUserAsync(this IdapadDataAccess dataAccess, int id)
 {
     return(dataAccess.QueryFirstOrDefaultAsync <User>
                ("select a.Id, a.FirstName, a.LastName, a.UserName, a.EmailAddress, " +
                "f.Name FirmName, f.Type FirmType from Appuser a " +
                "left join FirmUsers fu On a.Id = fu.UserId  " +
                "inner join Firm f On f.Id = fu.firmId  " +
                "where a.Id = @id", new { id }));
 }
示例#11
0
 public static Task <User> GetFirmUserByUserNameAsync(this IdapadDataAccess dataAccess, string userName)
 {
     return(dataAccess.QueryFirstOrDefaultAsync <User>
                ("select a.Id, a.FirstName, a.LastName, a.UserName, a.EmailAddress, " +
                "f.Name FirmName, f.Type FirmType from Appuser a " +
                "left join FirmUsers fu On a.Id = fu.UserId  " +
                "left join Firm f On f.Id = fu.firmId  " +
                "where a.UserName = @UserName", new { userName }));
 }
示例#12
0
        public static async Task <PagedResults <Product> > GetProductByAsync(this IdapadDataAccess dataAccess,
                                                                             int page, int pageSize, string sort, int?brandId, int?typeId, string search)
        {
            string sql = "SELECT  p.[Id], p.[Name], p.[Description], p.[Price], p.[PictureUrl]," +
                         "b.[Name] ProductBrand, t.[Name] ProductType " +
                         "FROM Products p " +
                         "INNER JOIN ProductBrands b on p.ProductBrandId = b.id " +
                         "INNER JOIN ProductTypes t on p.ProductTypeId = t.id " +
                         "WHERE P.ProductBrandId = isnull(@Brandid, P.ProductBrandId) " +
                         "AND P.ProductTypeId = isnull(@Typeid, P.ProductTypeId) " +
                         "AND p.[Name] like  '%' + isnull(@Search, P.Name) + '%' " +
                         "ORDER BY " +
                         "case when @Sort = 'priceAsc' THEN p.Price END ASC, " +
                         "case when @Sort = 'priceDesc' THEN p.Price END DESC, " +
                         "case when isnull(@Sort, 'name') = 'name' then p.[Name] end ASC, " +
                         "case when @Sort = 'namedesc' then p.[Name] end DESC " +
                         "OFFSET @Offset ROWS " +
                         "FETCH NEXT @PageSize ROWS ONLY";

            string countSql = "SELECT count(*) " +
                              "FROM Products p " +
                              "INNER JOIN ProductBrands b on p.ProductBrandId = b.id " +
                              "INNER JOIN ProductTypes t on p.ProductTypeId = t.id " +
                              "WHERE P.ProductBrandId = isnull(@Brandid, P.ProductBrandId) " +
                              "AND P.ProductTypeId = isnull(@Typeid, P.ProductTypeId) " +
                              "AND p.[Name] like  '%' + isnull(@Search, P.Name) + '%'";

            var results = new PagedResults <Product>();

            var TCparameters = new
            {
                BrandId = brandId,
                TypeId  = typeId,
                Search  = search
            };

            var TotalCount = await dataAccess.QueryFirstOrDefaultAsync <int>(countSql, TCparameters);

            var parameters = new
            {
                Offset   = (page - 1) * pageSize,
                PageSize = pageSize,
                Sort     = sort,
                BrandId  = brandId,
                TypeId   = typeId,
                Search   = search
            };

            var products = await dataAccess.QueryAsync <Product>(
                sql, parameters);


            results.Items      = products;
            results.TotalCount = TotalCount;

            return(results);
        }
示例#13
0
        private static async Task <int> DoestheAddressExists(this IdapadDataAccess dataAccess,
                                                             FirmAddress firmaddress)
        {
            int firmAddressId = await dataAccess.QueryFirstOrDefaultAsync <int>(
                "select Id from FirmAddress where " +
                "StreetAddress = @StreetAddress " +
                "AND isnull(AptAddress, 'AptAddress') = ISNULL(@AptAddress, isnull(AptAddress, 'AptAddress')) " +
                "AND City = @City AND State =@State AND ZipCode= @ZipCode", firmaddress);

            return(firmAddressId);
        }
示例#14
0
        public static async Task <int> AddFirmProductAsync(this IdapadDataAccess dataAccess,
                                                           FirmProduct firmProduct)
        {
            var firmProductId = await DoestheFirmProductExists(dataAccess, firmProduct);

            if (firmProductId < 1)
            {
                return(await dataAccess.ExecuteScalarAsync <int>(
                           "Insert FirmProducts (ProductId, FirmId)  output inserted.Id values(@ProductId, @FirmId)", firmProduct));
            }
            return(firmProductId);
        }
示例#15
0
        public static async Task <int> CreateFirmAsync(this IdapadDataAccess dataAccess,
                                                       Firm firm)
        {
            var firmId = await DoestheFirmExists(dataAccess, firm);

            if (firmId < 1)
            {
                return(await dataAccess.ExecuteScalarAsync <int>(
                           "Insert Firm (Name, Type, AddressId)  output inserted.Id values(@Name, @Type, @AddressId)", firm));
            }
            return(firmId);
        }
示例#16
0
        public static async Task <IEnumerable <FirmOrder> > GetOrderByIdAsync(this IdapadDataAccess dataAccess, string email, int id)
        {
            string SQL = "SELECT o.[BuyerEmail], o.[OrderDate], o.[DeliveryMethodId],o.[ShipAddressId],  " +
                         "o.[Subtotal],o.[Status], o.[PaymentInterestId], i.[FirmProductId], i.[Price], " +
                         "i.[Quantity], i.[SizeId], i.[OrderId] FROM [dbo].[Orders] o " +
                         "INNER JOIN [dbo].[OrderItems] i ON o.Id = i.OrderId " +
                         " WHERE o.BuyerEmail = '" + email + "'" +
                         " AND o.id = " + id;

            var productItemOrdered = await dataAccess.QueryAsync <FirmOrder>(SQL);

            return(productItemOrdered);
        }
示例#17
0
        public static async Task <int> CreateFirmAddressAsync(this IdapadDataAccess dataAccess,
                                                              FirmAddress firmaddress)
        {
            var firmAddressId = await DoestheAddressExists(dataAccess, firmaddress);

            if (firmAddressId < 1)
            {
                return(await dataAccess.ExecuteScalarAsync <int>(
                           "Insert FirmAddress (TypeId, StreetAddress, AptAddress, City, State, ZipCode)  output inserted.Id values" +
                           "(1, @StreetAddress, @AptAddress, @City, @State, @ZipCode)", firmaddress));
            }

            return(firmAddressId);
        }
示例#18
0
        public static async Task <Product> GetProductByIdAsync(this IdapadDataAccess dataAccess, int id)
        {
            string sql = "SELECT  p.[Id], p.[Name], p.[PictureUrl], p.[Price], p.[Description], " +
                         "b.[Name] ProductBrand, t.[Name] ProductType " +
                         "FROM[dbo].[Products] p INNER JOIN ProductBrands b on p.ProductBrandId = b.id " +
                         "INNER JOIN ProductTypes t on p.ProductTypeId = t.id " +
                         "WHERE p.Id = @Id  ";

            var parameters = new { Id = id };

            var product = await dataAccess.QueryFirstOrDefaultAsync <Product>(
                sql, parameters, null, (int?)CommandType.StoredProcedure);

            return(product);
        }
示例#19
0
        public static async Task <int> CreateOrderAsync(this IdapadDataAccess dataAccess, RedisDataAccess basketAccess,
                                                        FirmOrder order, string Email, int DeliveryMethodId, int AddressId)
        {
            var basket = await basketAccess.GetBasketAsync(order.BasketId);

            decimal orderSubTotal = 0;
            var     orderCreate   = new
            {
                BuyerEmail       = Email,
                OrderDate        = order.OrderDate,
                DeliveryMethodId = DeliveryMethodId,
                ShipAddressId    = AddressId,
                Status           = order.Status
            };

            var orderid = await dataAccess.ExecuteScalarAsync <int>(
                "Insert Orders (BuyerEmail, OrderDate, DeliveryMethodId, ShipAddressId, Status) " +
                "output inserted.Id " +
                "values(@BuyerEmail, @OrderDate, @DeliveryMethodId, @ShipAddressId, @Status)", orderCreate);


            var items = new List <FirmOrderItem>();

            foreach (var item in basket.Items)
            {
                //var productItem = await dataAccess.GetFirmProductItemAsync(item.Id);
                var orderItemCreate = new
                {
                    FirmProductId = item.Id,
                    Price         = item.Price,
                    Quantity      = item.Quantity,
                    orderid       = orderid,
                };

                var orderitemid = await dataAccess.ExecuteScalarAsync <int>(
                    "INSERT INTO[dbo].[OrderItems]([FirmProductId],[Price],[Quantity],[OrderId]) " +
                    " VALUES( @FirmProductId, @Price, @Quantity, @OrderId)", orderItemCreate);

                orderSubTotal = orderSubTotal + (decimal)(item.Quantity * item.Price);
            }
            await dataAccess.ExecuteAsync(
                "UPDATE Orders SET SubTotal = " + orderSubTotal +
                " WHERE Id = " + orderid);

            return(orderid);
        }
示例#20
0
        public static async Task <List <FirmOrder> > GetOrderAsync(this IdapadDataAccess dataAccess, string email)
        {
            string SQL = "SELECT o.Id, o.[BuyerEmail], o.[OrderDate], o.[DeliveryMethodId], o.[ShipAddressId], " +
                         "o.[Subtotal], o.[Status], o.[PaymentInterestId], " +
                         "i.[FirmProductId], i.[FirmProductId], p.Id ProductId, p.[Name] ProductName, " +
                         "p.[Description] ProductDescription, p.PictureUrl ProductPictureUrl, " +
                         "i.[Price], i.[Quantity], i.[SizeId], i.[OrderId], i.Id OrderItemId, " +
                         "d.ShortName DelShortName, d.[DeliveryTime], d.[Description] DelDescription, d.[Price] DelPrice, " +
                         "a.[StreetAddress], a.[AptAddress], a.[City], a.[State], a.[ZipCode] " +
                         "FROM [dbo].[Orders] o " +
                         "INNER JOIN [dbo].[OrderItems] i ON o.Id = i.OrderId " +
                         "INNER JOIN [dbo].[FirmProducts] fp ON i.FirmProductId = fp.Id " +
                         "INNER JOIN [dbo].[Products] p ON fp.ProductId = p.Id " +
                         "INNER JOIN [dbo].[Firm] f ON i.FirmProductId = f.Id " +
                         "INNER JOIN [dbo].[DeliveryMethod] d on o.[DeliveryMethodId] = d.Id " +
                         "INNER JOIN FirmAddress a on a.Id = o.Id " +
                         "WHERE o.BuyerEmail = '" + email + "'";

            var ordersflat = await dataAccess.QueryAsync <OrderFlat>(SQL);

            List <FirmOrder> orders = new List <FirmOrder>();

            //
            HashSet <int> orderIds = new HashSet <int>();

            foreach (var item in ordersflat)
            {
                if (!orderIds.Contains(item.Id))
                {
                    var order = new FirmOrder();
                    order.Id         = item.Id;
                    order.BuyerEmail = item.BuyerEmail;
                    order.OrderDate  = item.OrderDate;
                    order.Subtotal   = item.Subtotal;


                    FirmAddress shipaddress = new FirmAddress();
                    shipaddress.StreetAddress = item.StreetAddress;
                    shipaddress.AptAddress    = item.AptAddress;
                    shipaddress.City          = item.City;
                    shipaddress.State         = item.State;
                    shipaddress.ZipCode       = item.ZipCode;
                    order.ShipToAddress       = shipaddress;

                    OrderDelivery delivery = new OrderDelivery();
                    delivery.DeliveryTime = item.DeliveryTime;
                    delivery.ShortName    = item.DelShortName;
                    delivery.Description  = item.DelDescription;
                    delivery.Price        = item.DelPrice;
                    order.DeliveryMethod  = delivery;

                    order.OrderItems = new List <FirmOrderItem>();

                    foreach (var orderitem in ordersflat.Where(oi => oi.OrderId == order.Id))
                    {
                        FirmOrderItem firmorderItem = new FirmOrderItem();

                        firmorderItem.OrderId  = orderitem.OrderId;
                        firmorderItem.Id       = orderitem.OrderItemId;
                        firmorderItem.Price    = orderitem.Price;
                        firmorderItem.Quantity = orderitem.Quantity;

                        FirmProductItem firmProductItem = new FirmProductItem();
                        firmProductItem.ProductId   = orderitem.FirmProductId;
                        firmProductItem.ProductName = orderitem.ProductName;
                        firmProductItem.PictureUrl  = orderitem.ProductPictureUrl;
                        firmorderItem.ItemOrdered   = firmProductItem;

                        order.OrderItems.Add(firmorderItem);
                    }

                    orderIds.Add(order.Id);
                    orders.Add(order);
                }
            }
            return(orders);
        }
示例#21
0
        public static async Task <IEnumerable <OrderDelivery> > GetDeliveryMethodsAsync(this IdapadDataAccess dataAccess)
        {
            var sql = "SELECT[Id],[ShortName],[DeliveryTime],[Description],[Price] " +
                      " FROM[dbo].[DeliveryMethod] ";


            var deliveryMethod = await dataAccess.QueryAsync <OrderDelivery>(sql);

            return(deliveryMethod);
        }
示例#22
0
        public static async Task <IEnumerable <FirmProduct> > GetFirmProductItemAsync(this IdapadDataAccess dataAccess, int id)
        {
            string sql = "SELECT  f.[Id], p.[Name] FROM dbo.FirmProduct f " +
                         "INNER JOIN dbo.Product p on f.ProductId = p.Id " +
                         "WHERE f.Id = " + id;

            var firmproduct = await dataAccess.QueryAsync <FirmProduct>(sql);

            return(firmproduct);
        }