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);
}
