public static List <Item> GetItems(int[] itemIds) { using (var context = Exigo.Sql()) { return(context.Query <Item>(@" SELECT i.ItemID ,i.ItemCode ,i.ItemTypeID ,ISNULL(il.ItemDescription, i.ItemDescription) as ItemDescription ,ISNULL(il.ShortDetail, i.ShortDetail) as 'ShortDetail1' ,ISNULL(il.ShortDetail2, i.ShortDetail2) as 'ShortDetail2' ,ISNULL(il.ShortDetail3, i.ShortDetail3) as 'ShortDetail3' ,ISNULL(il.ShortDetail4, i.ShortDetail4) as 'ShortDetail4' ,ISNULL(il.LongDetail, i.LongDetail) as 'LongDetail1' ,ISNULL(il.LongDetail2, i.LongDetail2) as 'LongDetail2' ,ISNULL(il.LongDetail3, i.LongDetail3) as 'LongDetail3' ,ISNULL(il.LongDetail4, i.LongDetail4) as 'LongDetail4' ,i.TinyImageName as 'TinyImageUrl' ,i.SmallImageName as 'SmallImageUrl' ,i.LargeImageName as 'LargeImageUrl' FROM Items i LEFT JOIN ItemLanguages il ON il.ItemID = i.ItemID AND il.LanguageID = @languageID WHERE i.ItemID in @ids ", new { ids = itemIds, languageID = Exigo.GetSelectedLanguageID() }).ToList()); } }
public static CustomerType GetCustomerType(int customerTypeID) { var customerType = new CustomerType(); using (var context = Exigo.Sql()) { customerType = context.Query <CustomerType>(@" SELECT CustomerTypeID ,CustomerTypeDescription ,PriceTypeID FROM CustomerTypes WHERE CustomerTypeID = @CustomerTypeID ", new { CustomerTypeID = customerTypeID }).FirstOrDefault(); } if (customerType == null) { return(null); } return(customerType); }
public static CustomerSubscription GetCustomerSubscription(int customerID, int subscriptionID) { var subscription = new CustomerSubscription(); using (var context = Exigo.Sql()) { subscription = context.Query <CustomerSubscription>(@" SELECT cs.SubscriptionID , cs.CustomerID , cs.IsActive , cs.StartDate , cs.ExpireDate FROM CustomerSubscriptions cs LEFT JOIN Subscriptions s ON cs.SubscriptionID = s.SubscriptionID WHERE cs.CustomerID = @CustomerID AND cs.SubscriptionID = @SubscriptionID ", new { SubscriptionID = subscriptionID, CustomerID = customerID }).FirstOrDefault(); } if (subscription == null) { return(null); } return(subscription); }
/// <summary> /// Getting the Items /// </summary> /// <param name="itemCodes"></param> /// <param name="Configuration"></param> /// <param name="priceTypeiD"></param> /// <returns></returns> public static IEnumerable <Product> GetEligibleItems(string[] itemCodes, IOrderConfiguration Configuration, int priceTypeID) { List <Common.Api.ExigoWebService.ItemResponse> responseItems = new List <Common.Api.ExigoWebService.ItemResponse>(); using (var context = Exigo.Sql()) { int webCategoryID = itemCodes.Length == 0 ? Configuration.CategoryID : 0; int webID = 1; bool returnLongDetail = false; string Codes = itemCodes.Length == 0 ? "" : String.Join(",", itemCodes); string sqlProcedure = string.Format("GetPersonalOrderItems {0},'{1}',{2},{3},{4},'{5}',{6}" , priceTypeID, Configuration.CurrencyCode, Configuration.WarehouseID, webCategoryID, webID, Codes, returnLongDetail); responseItems = context.Query <Common.Api.ExigoWebService.ItemResponse>(sqlProcedure).ToList(); } //group by item-code and select only first allow only those items which Available in stock responseItems = responseItems.GroupBy(x => x.ItemCode).Select(x => x.First()).ToList(); // allow only those items which Available in stock responseItems = responseItems.Where(x => x.InventoryStatus.ToString().Equals(Common.Api.ExigoWebService.InventoryStatusType.Available.ToString())).ToList(); if (responseItems.Count() == 0) { return(new List <Product>()); } var products = responseItems.Select(item => new Product(item)).ToList(); products.ForEach(c => c.PriceTypeID = priceTypeID); return(products); }
public static bool IsValidAutoOrderID(int customerID, int autoOrderID, bool includeCancelledAutoOrders = false) { // Get the autoorder //var context = Exigo.OData(); //var query = context.AutoOrders // .Where(c => c.CustomerID == customerID) // .Where(c => c.AutoOrderID == autoOrderID); AutoOrder autoOrder = new AutoOrder(); using (var Context = Exigo.Sql()) { string SPGetCustomerAutoOrders = string.Format("GetCustomerAutoOrders {0},{1}", customerID, autoOrderID); autoOrder = Context.Query <AutoOrder>(SPGetCustomerAutoOrders).FirstOrDefault(); } // Only pull active auto orders if applicable //if (!includeCancelledAutoOrders) query = query.Where(c => c.AutoOrderStatusID == 1); bool isAutoOrdercancelled = true; if (!includeCancelledAutoOrders) { isAutoOrdercancelled = autoOrder.AutoOrderStatusID == 1; } if (!isAutoOrdercancelled) { autoOrder = new AutoOrder(); } // var autoOrder = query.FirstOrDefault(); return(autoOrder != null); }
// Validators public static bool IsCustomerInUniLevelDownline(int topCustomerID, int customerID) { var results = new List <dynamic>(); using (var context = Exigo.Sql()) { results = context.Query <dynamic>(@" SELECT d.CustomerID FROM UnilevelDownLine d WHERE d.DownlineCustomerID = @topcustomerID AND d.CustomerID = @customerID ", new { topcustomerid = topCustomerID, customerid = customerID }).ToList(); } return(results.Count() != 0); }
public static Language GetLanguageByCustomerID(int customerID) { var defaultCultureCode = GlobalSettings.Markets.AvailableMarkets.FirstOrDefault(c => c.IsDefault).CultureCode; var defaultLanguage = Exigo.GetLanguages().FirstOrDefault(c => c.CultureCode == defaultCultureCode).LanguageID; var languageID = defaultLanguage; // Get the user's language preference based on their saved preference if (HttpContext.Current.Request.IsAuthenticated) { using (var sqlContext = Exigo.Sql()) { languageID = sqlContext.Query <int>(@"select top 1 LanguageID from Customers where CustomerID = @customerID", new { customerID = customerID }).FirstOrDefault(); } } var language = Exigo.GetLanguageByID(languageID); // If we couldn't find the user's preferred language, return the first one we find. if (language == null) { language = Exigo.GetLanguages().FirstOrDefault(); } // Return the language return(language); }
public static Period GetCurrentPeriod(int periodTypeID) { var cachekey = GlobalSettings.Exigo.Api.CompanyKey + "CurrentPeriod_" + periodTypeID.ToString(); var p = (Period)HttpRuntime.Cache[cachekey]; if (HttpRuntime.Cache[cachekey] == null) { var period = new Period(); using (var context = Exigo.Sql()) { period = context.Query <Period>(@" SELECT p.PeriodTypeID , p.PeriodID , p.PeriodDescription , p.StartDate , p.EndDate , p.AcceptedDate FROM Periods p WHERE p.PeriodTypeID = @PeriodTypeID AND @CurrentDate between p.StartDate and dateadd(day, 1, p.EndDate) ORDER BY p.AcceptedDate desc, p.EndDate desc ", new { PeriodTypeID = periodTypeID, CurrentDate = DateTime.Now.ToCST() }).FirstOrDefault(); } HttpRuntime.Cache[cachekey] = (Period)period; } return((Period)HttpRuntime.Cache[cachekey]); }
public static IEnumerable <CustomerWallItem> GetCustomerRecentActivity(GetCustomerRecentActivityRequest request) { List <CustomerWallItem> wallItems; using (var context = Exigo.Sql()) { wallItems = context.Query <CustomerWallItem>(@" SELECT CustomerWallItemID ,CustomerID ,EntryDate ,Text ,Field1 ,Field2 ,Field3 FROM CustomerWall WHERE CustomerID = @CustomerID ", new { CustomerID = request.CustomerID }).ToList(); } if (request.StartDate != null) { wallItems = wallItems.Where(c => c.EntryDate >= request.StartDate).ToList(); } return(wallItems); }
public static GetDownlineUpcomingPromotionsResponse GetDownlineUpcomingPromotions(GetDownlineUpcomingPromotionsRequest request) { var response = new GetDownlineUpcomingPromotionsResponse(); // Determine if we passed a KendoGridRequest. // If we did, use the page and pagesize from the request instead, as it takes priority due to it's unique implications. var whereClause = string.Empty; if (request.KendoGridRequest != null) { request.Page = request.KendoGridRequest.Page; request.RowCount = request.KendoGridRequest.PageSize; request.TotalRowCount = request.KendoGridRequest.Total; whereClause = request.KendoGridRequest.SqlWhereClause; whereClause = whereClause.Replace("RankScore", "Score"); whereClause = whereClause.Replace("CustomerID", "c.CustomerID"); } var results = new List <CustomerRankScore>(); //calling store Procedure "GetDownlineUpcomingPromotions" int periodTypeID = request.PeriodTypeID; int downlineCustomerID = request.DownlineCustomerID; string strRankID = (request.RankID != null) ? "AND PaidRankID = " + request.RankID + @"" : "0"; int skip = request.Skip; int take = request.Take; string sortingOrder = " ORDER BY "; //need to append defualt sorting in query sortingOrder += KendoUtilities.GetSqlOrderByClause((request.KendoGridRequest != null) ? request.KendoGridRequest.SortObjects : new List <SortObject>(), new SortObject("TotalScore", "DESC"), new SortObject("c.CreatedDate", "ASC")); using (var sqlcontext = Exigo.Sql()) { sqlcontext.Open(); string sqlProcedure = string.Format(@"GetDownlineUpcomingPromotions {0},{1},'{2}','{3}','{4}',{5},{6}", periodTypeID, downlineCustomerID, strRankID, whereClause.Replace("'", "''"), sortingOrder, skip, take); results = sqlcontext.Query <CustomerRankScore, Rank, CustomerRankScore>(sqlProcedure, (customer, rank) => { customer.Rank = rank; return(customer); }, splitOn: "RankID").ToList(); sqlcontext.Close(); response.CustomerRankScores = results; } if (request.KendoGridRequest != null && response.CustomerRankScores != null && response.CustomerRankScores.Count() > 0) { response.TotalCount = response.CustomerRankScores.FirstOrDefault().TotalRows; } else { response.TotalCount = response.CustomerRankScores.Count(); } return(response); }
public static IEnumerable <Rank> GetRanks() { var ranks = new List <Rank>(); using (var context = Exigo.Sql()) { ranks = context.Query <Rank>(@" SELECT r.RankID ,r.RankDescription FROM Ranks r ").OrderBy(c => c.RankID).ToList(); } //Ensure that rank 0 exists if (ranks.Where(c => c.RankID == 0).FirstOrDefault() == null) { ranks.Insert(0, new Rank() { RankID = 0, RankDescription = "" }); } foreach (var rank in ranks) { yield return(rank); } }
public static CustomerPointAccount GetCustomerPointAccount(int customerID, int pointAccountID) { var pointAccount = new CustomerPointAccount(); using (var context = Exigo.Sql()) { pointAccount = context.Query <CustomerPointAccount>(@" SELECT cpa.PointAccountID , cpa.CustomerID , cpa.PointBalance , pa.PointAccountDescription , pa.CurrencyCode FROM CustomerPointAccounts cpa LEFT JOIN PointAccounts pa ON cpa.PointAccountID = pa.PointAccountID WHERE cpa.CustomerID = @CustomerID AND cpa.PointAccountID = @PointAccountID ", new { CustomerID = customerID, PointAccountID = pointAccountID }).FirstOrDefault(); } if (pointAccount == null) { return(null); } return(pointAccount); }
public static IEnumerable <CustomerPointAccount> GetCustomerPointAccounts(int customerID) { var pointAccounts = new List <CustomerPointAccount>(); using (var context = Exigo.Sql()) { pointAccounts = context.Query <CustomerPointAccount>(@" SELECT cpa.PointAccountID , cpa.CustomerID , cpa.PointBalance , pa.PointAccountDescription , pa.CurrencyCode FROM CustomerPointAccounts cpa LEFT JOIN PointAccounts pa ON cpa.PointAccountID = pa.PointAccountID WHERE cpa.CustomerID = @CustomerID ", new { CustomerID = customerID }).ToList(); } if (pointAccounts == null) { return(null); } return(pointAccounts); }
public static TreeNode GetBottomBinaryTreeNode(GetBottomTreeNodeRequest request) { var bottomNode = new TreeNode(); using (var context = Exigo.Sql()) { bottomNode = context.Query <TreeNode>(@" With tree (CustomerID, ParentID, placement, nestedlevel) as ( Select CustomerID, ParentID, placement, nestedlevel from BinaryTree where customerid = @customerID union all select u.CustomerID, u.ParentID, u.placement, u.nestedlevel from BinaryTree u inner join tree t on t.customerid = u.ParentID and u.placement = @placementid ) select top 1 CustomerID From tree order by nestedlevel desc option (maxrecursion 0) ", new { customerID = request.TopCustomerID, placementid = request.PlacementID }).FirstOrDefault(); } return(bottomNode); }
public static void DeleteCustomerAutoOrder(int customerID, int autoOrderID) { // Get the autoorder //var context = Exigo.OData(); //var autoOrder = context.AutoOrders // .Where(c => c.CustomerID == customerID) // .Where(c => c.AutoOrderID == autoOrderID); AutoOrder autoOrder = new AutoOrder(); using (var Context = Exigo.Sql()) { string SPGetCustomerAutoOrders = string.Format("GetCustomerAutoOrders {0},{1}", customerID, autoOrderID); autoOrder = Context.Query <AutoOrder>(SPGetCustomerAutoOrders).FirstOrDefault(); } if (autoOrder == null) { return; } // Cancel the autoorder var response = Exigo.WebService().ChangeAutoOrderStatus(new ChangeAutoOrderStatusRequest { AutoOrderID = autoOrderID, AutoOrderStatus = AutoOrderStatusType.Deleted }); }
public static List <Charity> GetCharities() { using (var context = Exigo.Sql()) { var SqlProcedure = string.Format("GetCharities"); return(context.Query <Charity>(SqlProcedure).ToList()); } }
public static string GetSavedCartByCartID(int cartID) { using (var contextsql = Exigo.Sql()) { var sql = string.Format(@"Exec GetSpecificSavedCart {0}", cartID); return(contextsql.Query <SavedCart>(sql).FirstOrDefault().PropertyBag); } }
public static Order GetCustomerOrdersDetail(int orderId) { using (var contextsql = Exigo.Sql()) { var sql = string.Format(@"Exec GetOrdersDetail {0}", orderId); return(contextsql.Query <Order>(sql).FirstOrDefault()); } }
public static List <GiftPromo> GetGiftPromos() { using (var context = Exigo.Sql()) { var SqlProcedure = string.Format("GetGiftPromos"); return(context.Query <GiftPromo>(SqlProcedure).ToList()); } }
public static List <AnnouncementBanner> GetAnnouncementBanners() { using (var context = Exigo.Sql()) { var SqlProcedure = string.Format("GetAnnouncementBanners"); return(context.Query <AnnouncementBanner>(SqlProcedure).ToList()); } }
private static List <OrderDetail> GetOrderDetails(int orderID) { using (var contextsql = Exigo.Sql()) { var sql = string.Format(@"Exec GetOrderDetails {0}", orderID); return(contextsql.Query <OrderDetail>(sql).ToList()); } }
public static IEnumerable <Product> GetProducts(GetItemsRequest request) { // If we don't have what we need to make this call, stop here. if (request.Configuration == null) { throw new InvalidRequestException("ExigoService.GetItems() requires an OrderConfiguration."); } if (request.Configuration.CategoryID == 0 && request.CategoryID == null && request.ItemCodes.Length == 0) { throw new InvalidRequestException("ExigoService.GetItems() requires either a CategoryID or a collection of item codes."); } ; // Build the request. //var getItemsrequest = new Common.Api.ExigoWebService.GetItemsRequest //{ // ItemCodes = request.ItemCodes, // CurrencyCode = request.Configuration.CurrencyCode, // PriceType = request.Configuration.PriceTypeID, // LanguageID = 0, // WarehouseID = request.Configuration.WarehouseID, // WebID = 1, // WebCategoryID = (request.ItemCodes.Length == 0 ? // request.Configuration.CategoryID as Nullable<int> : // null) //}; //var api = Exigo.WebService(); // Execute request and get the response. //var response = api.GetItems(getItemsrequest); //proc List <Common.Api.ExigoWebService.ItemResponse> responseItems = new List <Common.Api.ExigoWebService.ItemResponse>(); using (var context = Exigo.Sql()) { int webCategoryID = request.ItemCodes.Length == 0 ? request.Configuration.CategoryID : 0; int webID = 1; bool returnLongDetail = false; string itemCodes = request.ItemCodes.Length == 0 ? "" : request.ItemCodes.FirstOrDefault(); string sqlProcedure = string.Format("GetPersonalOrderItems {0},'{1}',{2},{3},{4},'{5}',{6}" , request.Configuration.PriceTypeID, request.Configuration.CurrencyCode, request.Configuration.WarehouseID, webCategoryID, webID, itemCodes, returnLongDetail); responseItems = context.Query <Common.Api.ExigoWebService.ItemResponse>(sqlProcedure).ToList(); } // Project items into products. var products = responseItems.Select(item => new Product(item)).ToList(); //end proc //foreach (var itemResponse in response.Items) //{ // yield return new Product(itemResponse); //} return(products); }
public AvatarResponse GetCustomerAvatarResponse(int customerID, string filename) { var response = new AvatarResponse(); var path = $"/customers/{customerID.ToString()}/avatars"; byte[] bytes; using (var conn = Exigo.Sql()) { conn.Open(); var cmd = new SqlCommand(@" SELECT TOP 1 ImageData FROM ImageFiles WHERE Path=@FilePath AND Name=@FileName " , conn); cmd.Parameters.Add("@FilePath", System.Data.SqlDbType.NVarChar, 500).Value = path; cmd.Parameters.Add("@FileName", System.Data.SqlDbType.NVarChar, 500).Value = filename; bytes = (byte[])cmd.ExecuteScalar(); } response.Bytes = bytes; var extension = Path.GetExtension(filename).ToLower(); string contentType = "image/jpeg"; switch (extension) { case ".gif": contentType = "image/gif"; break; case ".jpeg": contentType = "image/png"; break; case ".bmp": contentType = "image/bmp"; break; case ".png": contentType = "image/png"; break; case ".jpg": default: contentType = "image/jpeg"; break; } response.FileType = contentType; return(response); }
public static IEnumerable <T> GetUniLevelUplineTree <T>(GetUplineRequest request) where T : ITreeNode { // Get the nodes var nodes = new List <T>(); using (var context = Exigo.Sql()) { nodes = context.Query <T>(@" SELECT up.CustomerID, ParentCustomerID = up.SponsorID, up.Level FROM UniLevelUpline up WHERE up.UplineCustomerID = @bottomcustomerid ORDER BY Level DESC", new { bottomcustomerid = request.BottomCustomerID, topcustomerid = request.TopCustomerID }).ToList(); } // Filter out the nodes that don't belong var isFound = false; var filteredNodes = new List <T>(); foreach (var node in nodes) { filteredNodes.Add(node); if (node.CustomerID == request.TopCustomerID) { isFound = true; break; } } if (!isFound) { return(new List <T>()); } // Re-order the nodes (by level ascending) nodes = filteredNodes.OrderBy(c => c.Level).ToList(); // Set the levels var nodeCount = nodes.Count - 1; foreach (var node in nodes) { node.Level = node.Level + nodeCount; } return(nodes); }
public static bool InsertPromoCode(PromoCode promoCode) { using (var context = Exigo.Sql()) { var SqlProcedure = string.Format("InsertPromoCodes '{0}','{1}','{2}',{3},{4},'{5}',{6},{7}", promoCode.Code, promoCode.StartDate, promoCode.EndDate, promoCode.DiscountType, 30, promoCode.Email, promoCode.UsedCount, promoCode.EligibleCount); var styleAmbassadorRewardSettings = context.Query <PromoCode>(SqlProcedure).FirstOrDefault(); return(true); } }
public static bool AddToCharity(Charity Charity) { using (var context = Exigo.Sql()) { var SqlProcedure = string.Format("InsertCharity '{0}','{1}','{2}','{3}','{4}','{5}'", Charity.EIN, Charity.Name.Replace("'", "''"), Charity.City.Replace("'", "''"), Charity.State, Charity.Country, Charity.Deductibility); var styleAmbassadorRewardSettings = context.Query <PromoCode>(SqlProcedure).FirstOrDefault(); return(true); } }
public static bool AddToGiftPromos(GiftPromo product) { using (var context = Exigo.Sql()) { var SqlProcedure = string.Format("InsertGiftPromo '{0}',{1},{2},'{3}','{4}','{5}',{6},'{7}'", product.ItemCode, product.DiscountPrecent, product.SalesThreshold, product.StartDate, product.EndDate, product.Description.Replace("'", "''"), product.DiscountType, product.CreatedDate); var styleAmbassadorRewardSettings = context.Query <PromoCode>(SqlProcedure).FirstOrDefault(); return(true); } }
///this method is not calling any where public static ItemCategory GetItemCategory(int itemCategoryID) { //var context = Exigo.OData(); // Get the nodes var categories = new List <ItemCategory>(); var rowcount = 50; var lastResultCount = rowcount; var callsMade = 0; IEnumerable <Common.Api.ExigoOData.WebCategory> results = null; while (lastResultCount == rowcount) { // Get the data //var results = context.WebCategories // .Where(c => c.WebID == 1) // .OrderBy(c => c.ParentID) // .OrderBy(c => c.SortOrder) // .Skip(callsMade * rowcount) // .Take(rowcount) // .Select(c => c) // .ToList(); using (var Context = Exigo.Sql()) { Context.Open(); //Getting items list by item code string sqlProcedure = string.Format("Getwebcategory {0}", itemCategoryID); results = Context.Query <Common.Api.ExigoOData.WebCategory>(sqlProcedure).ToList().Skip(callsMade * rowcount).Take(rowcount); categories = results.Cast <ItemCategory>().ToList(); Context.Close(); } //results.ForEach(c => //{ // categories.Add((ItemCategory)c); //}); callsMade++; lastResultCount = results.Count(); } // Recursively populate the children var category = categories.Where(c => c.ItemCategoryID == itemCategoryID).FirstOrDefault(); if (category == null) { return(null); } category.Subcategories = GetItemCategorySubcategories(category, categories); return(category); }
public static IEnumerable <TimeZone> GetTimeZonesByCountryCode(string countryCode) { List <TimeZone> TimeZones = new List <TimeZone>(); using (var context = Exigo.Sql()) { string sqlProcedure = string.Format("Exec GetTimeZonesByCountryCode {0}", countryCode); TimeZones = context.Query <TimeZone>(sqlProcedure).ToList(); } return(TimeZones); }
public static IEnumerable <Country> GetCountries() { List <Country> records = new List <Country>(); using (var context = Exigo.Sql()) { string sqlProcedure = string.Format("Exec GetCountry"); records = context.Query <Country>(sqlProcedure).ToList(); } return(records); }