public static bool IsWebAliasAvailable(int customerID, string webalias)
{
try
{
// Get the current webalias to see if it matches what we passed. If so, it's still valid.
var currentWebAlias = ExigoDAL.GetCustomerSite(customerID).WebAlias;
if (webalias.Equals(currentWebAlias, StringComparison.InvariantCultureIgnoreCase))
{
return(true);
}
// Validate the web alias
// cannot use SQL due to delay in update to replicated database
var customerSite = ExigoDAL.WebService().GetCustomerSite(new GetCustomerSiteRequest()
{
WebAlias = webalias
});
return(customerSite == null);
}
catch (Exception ex)
{
// the GetCustomerSite throws an exception if no matching web alias is found
// return the web alias is available in this case so return true
Log.Error(ex, "Error Getting Replicated Site: {Message}", ex.Message);
return(true);
}
}
public static bool IsCustomerInlDownline(int topCustomerID, int customerID, string downlineTree = "default")
{
var results = new List <dynamic>();
string treeToUse = string.Empty;
if (downlineTree == "default")
{
treeToUse = GlobalSettings.Exigo.UseBinary ? "BinaryDownline" : "UnilevelDownline";
}
else
{
treeToUse = "EnrollerDownline";
}
using (var context = ExigoDAL.Sql())
{
results = context.Query($@"
SELECT
d.CustomerID
FROM
{treeToUse} d
WHERE
d.DownlineCustomerID = @topcustomerID
AND d.CustomerID = @customerID
", new
{
topcustomerid = topCustomerID,
customerid = customerID
}).ToList();
}
return(results.Count() != 0);
}
public static void DeleteCustomerBankAccount(int customerID, ExigoService.BankAccountType type)
{
// If this is a new credit card, don't delete it - we have nothing to delete
if (type == ExigoService.BankAccountType.New)
{
return;
}
// Save the a blank copy of the bank account
// Save the bank account
var request = new SetAccountCheckingRequest
{
CustomerID = customerID,
BankName = string.Empty,
BankAccountNumber = string.Empty,
BankRoutingNumber = string.Empty,
BankAccountType = Common.Api.ExigoWebService.BankAccountType.CheckingPersonal,
NameOnAccount = string.Empty,
BillingAddress = string.Empty,
BillingCity = string.Empty,
BillingState = string.Empty,
BillingZip = string.Empty,
BillingCountry = string.Empty
};
var response = ExigoDAL.WebService().SetAccountChecking(request);
}
public static BankAccount SetCustomerBankAccount(int customerID, BankAccount account, ExigoService.BankAccountType type)
{
// New bank accounts
if (type == ExigoService.BankAccountType.New)
{
return(SaveNewCustomerBankAccount(customerID, account));
}
// Save the bank account
var request = new SetAccountCheckingRequest
{
CustomerID = customerID,
BankName = account.BankName,
BankAccountNumber = account.AccountNumber,
BankRoutingNumber = account.RoutingNumber,
BankAccountType = Common.Api.ExigoWebService.BankAccountType.CheckingPersonal,
NameOnAccount = account.NameOnAccount,
BillingAddress = account.BillingAddress.AddressDisplay,
BillingCity = account.BillingAddress.City,
BillingState = account.BillingAddress.State,
BillingZip = account.BillingAddress.Zip,
BillingCountry = account.BillingAddress.Country
};
var response = ExigoDAL.WebService().SetAccountChecking(request);
return(account);
}
public static void DeleteCustomerCreditCard(int customerID, CreditCardType type)
{
// If this is a new credit card, don't delete it - we have nothing to delete
if (type == CreditCardType.New)
{
return;
}
// Save the a blank copy of the credit card
// Passing a blank token will do the trick
var request = new SetAccountCreditCardTokenRequest
{
CustomerID = customerID,
CreditCardAccountType = (type == CreditCardType.Primary) ? AccountCreditCardType.Primary : AccountCreditCardType.Secondary,
CreditCardToken = string.Empty,
ExpirationMonth = 1,
ExpirationYear = DateTime.Now.Year + 1,
BillingName = string.Empty,
BillingAddress = string.Empty,
BillingCity = string.Empty,
BillingState = string.Empty,
BillingZip = string.Empty,
BillingCountry = string.Empty
};
var response = ExigoDAL.WebService().SetAccountCreditCardToken(request);
}
/// <summary>
/// Creates a Calendar
/// </summary>
/// <param name="request">Request encompassing, the CustomerID to create a Calendar for, the Description of the Calendar, and more related information.</param>
/// <returns>The Newly Created Calendar</returns>
public static Calendar CreateCalendar(CreateCalendarRequest request)
{
// Create the calendar
var calendar = new Calendar()
{
CalendarID = Guid.NewGuid(),
CustomerID = request.CustomerID,
Description = request.Description,
CalendarTypeID = 1,
CreatedDate = DateTime.Now
};
// Establish a SQL Connection
using (var ctx = ExigoDAL.Sql())
{
// Save the Calendar to the DB
ctx.Execute(@"
INSERT INTO ExigoWebContext.Calendars (CalendarID, CustomerID, Description, CalendarTypeID, CreatedDate) VALUES (@calendarID, @customerID, @description, @calendarTypeID, @createdDate)",
new
{
calendarID = calendar.CalendarID,
customerID = calendar.CustomerID,
description = calendar.Description,
calendarTypeID = calendar.CalendarTypeID,
createdDate = calendar.CreatedDate
});
}
// Return the saved calendar
return(calendar);
}
public static void PlaceUniLevelCustomer(PlaceUniLevelCustomerRequest request)
{
// Create our request
var apiRequest = new PlaceUniLevelNodeRequest()
{
CustomerID = request.CustomerID,
ToSponsorID = request.ToSponsorID,
Reason = request.Reason,
OptionalFindAvailable = request.FindNextAvailablePlacement
};
// Handle the optional filters
if (request.Placement != null)
{
apiRequest.OptionalPlacement = (int)request.Placement;
}
if (request.BuildTypeID != null)
{
apiRequest.OptionalUnilevelBuildTypeID = (int)request.BuildTypeID;
}
// Place the node
ExigoDAL.WebService().PlaceUniLevelNode(apiRequest);
// Update the customer's field to indicate they have been moved.
ExigoDAL.WebService().UpdateCustomer(new UpdateCustomerRequest
{
CustomerID = request.CustomerID,
Field1 = DateTime.Now.ToCST().ToString()
});
}
public static CustomerSubscription GetCustomerSubscription(int customerID, int subscriptionID)
{
var subscription = new CustomerSubscription();
using (var context = ExigoDAL.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);
}
public static bool IsWebaliasAvailable(string webalias)
{
var webaliasAvailable = false;
// must use try catch as API returns an exception if no customer site with the provided webalias is found
try
{
// cannot use SQL due to delay in update to replicated database
var customerSite = ExigoDAL.WebService().GetCustomerSite(new GetCustomerSiteRequest()
{
WebAlias = webalias
});
if (customerSite == null)
{
webaliasAvailable = true;
}
;
}
catch (Exception ex)
{
webaliasAvailable = true;
Log.Error(ex, "Error Getting Replicated Site: {Message}", ex.Message);
}
return(webaliasAvailable);
}
public static CustomerPointAccount GetCustomerPointAccount(int customerID, int pointAccountID)
{
var pointAccount = new CustomerPointAccount();
using (var context = ExigoDAL.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 = ExigoDAL.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 CommissionCard GetDashboardCommission(int customerID, int periodTypeID)
{
var commissionRunID = GetLastCommissionRunID(periodTypeID);
dynamic card;
using (var context = ExigoDAL.Sql())
{
card = context.Query <CommissionCard>(@"
SELECT c.CommissionRunID
,c.CurrencyCode
,c.Total
,cr.CommissionRunDescription
FROM Commissions c
LEFT JOIN CommissionRuns cr
ON c.CommissionRunID = cr.CommissionRunID
WHERE c.CustomerID = customerID
AND c.CommissionRunID = @commissionRunID
ORDER BY cr.CommissionRunID DESC
", new
{
customerID,
commissionRunID
}).FirstOrDefault();
if (card == null)
{
card = new CommissionCard();
}
}
return(card);
}
// Validators
public static bool IsCustomerInUniLevelDownline(int topCustomerID, int customerID)
{
var results = new List <dynamic>();
using (var context = ExigoDAL.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 IEnumerable <DynamicKitCategoryItem> GetAllDynamicKitCategoryItems()
{
var cacheKey = $"GetAllDynamicKitCategoryItems";
if (!MemoryCache.Default.Contains(cacheKey))
{
using (var context = ExigoDAL.Sql())
{
var categoryItems = context.Query <DynamicKitCategoryItem>(@"
SELECT
idkcim.DynamicKitCategoryID
, i.ItemCode
FROM
ItemDynamicKitCategoryItemMembers AS idkcim
LEFT JOIN Items AS i
ON i.ItemID = idkcim.ItemID
", new {
}).ToList();
MemoryCache.Default.Add(cacheKey, categoryItems, DateTime.Now.AddMinutes(GlobalSettings.Caching.CacheTimeouts.Short));
}
}
// get the cache
var data = MemoryCache.Default.Get(cacheKey) as List <DynamicKitCategoryItem>;
if (data == null)
{
yield break;
}
foreach (var catItem in data)
{
yield return(catItem);
}
}
public static CustomerType GetCustomerType(int customerTypeID)
{
var customerType = new CustomerType();
using (var context = ExigoDAL.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 IEnumerable <CustomerWallItem> GetCustomerRecentActivity(GetCustomerRecentActivityRequest request)
{
List <CustomerWallItem> wallItems;
using (var context = ExigoDAL.Sql())
{
wallItems = context.Query <CustomerWallItem>(@"
SELECT CustomerWallItemID
,CustomerID
,EntryDate
,Text
,Field1
,Field2
,Field3
FROM CustomerWall
WHERE CustomerID = @CustomerID
ORDER BY EntryDate DESC
", new
{
CustomerID = request.CustomerID
}).ToList();
}
if (request.StartDate != null)
{
wallItems = wallItems.Where(c => c.EntryDate >= request.StartDate).ToList();
}
return(wallItems);
}
public static List <WebCategoryItem> GetWebCategoryItems(int WebCategoryID, int?webID = null)
{
var _webID = webID ?? GlobalSettings.Items.WebID;
var cacheKey = $"GetWebCategoryItems_{WebCategoryID}_{_webID}";
if (!MemoryCache.Default.Contains(cacheKey))
{
using (var context = ExigoDAL.Sql())
{
var webCategories = context.Query <WebCategoryItem>(@"
SELECT
wci.WebID
, wci.WebCategoryID
, i.ItemCode
, wci.SortOrder
FROM
WebCategoryItems AS wci
LEFT JOIN Items AS i
ON i.ItemID = wci.ItemID
WHERE
wci.WebID = @webID
AND wci.WebCategoryID = @webCategoryID
", new
{
webID = _webID,
webCategoryID = WebCategoryID
}).ToList();
MemoryCache.Default.Add(cacheKey, webCategories, DateTime.Now.AddMinutes(GlobalSettings.Caching.CacheTimeouts.Long));
}
}
var data = MemoryCache.Default.Get(cacheKey) as List <WebCategoryItem>;
return(data);
}
public static void SetCustomerPrimaryAddress(int customerID, AddressType type)
{
if (type == AddressType.Main || type == AddressType.New)
{
return;
}
var addressesOnFile = ExigoDAL.GetCustomerAddresses(customerID)
.Where(c => c.IsComplete);
var oldPrimaryAddress = addressesOnFile
.Where(c => c.AddressType == AddressType.Main)
.FirstOrDefault();
var newPrimaryAddress = addressesOnFile
.Where(c => c.AddressType == type)
.FirstOrDefault();
if (oldPrimaryAddress == null || newPrimaryAddress == null)
{
return;
}
// Swap the addresses
ExigoDAL.SetCustomerAddressOnFile(customerID, (Address)newPrimaryAddress, AddressType.Main);
ExigoDAL.SetCustomerAddressOnFile(customerID, (Address)oldPrimaryAddress, type);
}
public static bool IsValidAutoOrderID(int customerID, int autoOrderID, bool showOnlyActiveAutoOrders = false)
{
var includeCancelled = "";
if (showOnlyActiveAutoOrders)
{
includeCancelled = "AND a.AutoOrderStatusID = 0";
}
dynamic autoOrder;
using (var context = ExigoDAL.Sql())
{
autoOrder = context.Query <dynamic>(@"
SELECT
a.AutoOrderID
FROM
AutoOrders a
WHERE
a.CustomerID = @customerid
AND a.AutoOrderID = @autoorderid
" + includeCancelled, new
{
customerid = customerID,
autoorderid = autoOrderID
}).FirstOrDefault();
}
return(autoOrder != null);
}
public static Address SaveNewCustomerAddress(int customerID, Address address)
{
var addressesOnFile = ExigoDAL.GetCustomerAddresses(customerID).Where(c => c.IsComplete);
// Do any of the addresses on file match the one we are using?
// If not, save this address to the next available slot
if (!addressesOnFile.Any(c => c.Equals(address)))
{
var saveAddress = false;
var request = new UpdateCustomerRequest();
request.CustomerID = customerID;
// Main address
if (!addressesOnFile.Any(c => c.AddressType == AddressType.Main))
{
saveAddress = true;
address.AddressType = AddressType.Main;
request.MainAddress1 = address.Address1;
request.MainAddress2 = address.Address2;
request.MainCity = address.City;
request.MainState = address.State;
request.MainZip = address.Zip;
request.MainCountry = address.Country;
}
// Mailing address
else if (!addressesOnFile.Any(c => c.AddressType == AddressType.Mailing))
{
saveAddress = true;
address.AddressType = AddressType.Mailing;
request.MailAddress1 = address.Address1;
request.MailAddress2 = address.Address2;
request.MailCity = address.City;
request.MailState = address.State;
request.MailZip = address.Zip;
request.MailCountry = address.Country;
}
// Other address
else
{
saveAddress = true;
address.AddressType = AddressType.Other;
request.OtherAddress1 = address.Address1;
request.OtherAddress2 = address.Address2;
request.OtherCity = address.City;
request.OtherState = address.State;
request.OtherZip = address.Zip;
request.OtherCountry = address.Country;
}
if (saveAddress)
{
ExigoDAL.WebService().UpdateCustomer(request);
ExigoDAL.PurgeCustomer(customerID);
}
}
return(address);
}
public static Address SetCustomerAddressOnFile(int customerID, Address address, AddressType type)
{
var saveAddress = false;
var request = new UpdateCustomerRequest();
request.CustomerID = customerID;
// Attempt to validate the user's entered address if US address
address = GlobalUtilities.ValidateAddress(address) as Address;
// New Addresses
if (type == AddressType.New)
{
return(ExigoDAL.SaveNewCustomerAddress(customerID, address));
}
// Main address
if (type == AddressType.Main)
{
saveAddress = true;
request.MainAddress1 = address.Address1;
request.MainAddress2 = address.Address2 ?? string.Empty;
request.MainCity = address.City;
request.MainState = address.State;
request.MainZip = address.Zip;
request.MainCountry = address.Country;
}
// Mailing address
if (type == AddressType.Mailing)
{
saveAddress = true;
request.MailAddress1 = address.Address1;
request.MailAddress2 = address.Address2 ?? string.Empty;
request.MailCity = address.City;
request.MailState = address.State;
request.MailZip = address.Zip;
request.MailCountry = address.Country;
}
// Other address
if (type == AddressType.Other)
{
saveAddress = true;
request.OtherAddress1 = address.Address1;
request.OtherAddress2 = address.Address2 ?? string.Empty;
request.OtherCity = address.City;
request.OtherState = address.State;
request.OtherZip = address.Zip;
request.OtherCountry = address.Country;
}
if (saveAddress)
{
ExigoDAL.WebService().UpdateCustomer(request);
ExigoDAL.PurgeCustomer(customerID);
}
return(address);
}
/// <summary>
/// Gets and caches all Web categories for a specific WebID
/// </summary>
/// <param name="webID">Override for Default WebID. Defaults to GlobalSettings.Items.WebID if none is provided.</param>
/// <returns></returns>
private static List <WebCategory> GetAllWebCategories(int?webID)
{
var _webID = webID ?? GlobalSettings.Items.WebID;
var cacheKey = $"GetAllWebCategories_{_webID}";
if (!MemoryCache.Default.Contains(cacheKey))
{
using (var context = ExigoDAL.Sql())
{
var webCategories = context.Query <WebCategory>(@"
SELECT
WebID
, WebCategoryID
, ParentID
, WebCategoryDescription
, NestedLevel
, SortOrder
FROM
WebCategories
WHERE
WebID = @webID
", new
{
webID = _webID
}).ToList();
MemoryCache.Default.Add(cacheKey, webCategories, DateTime.Now.AddMinutes(GlobalSettings.Caching.CacheTimeouts.Long));
}
}
var data = MemoryCache.Default.Get(cacheKey) as List <WebCategory>;
return(data);
}
public static TreeNode GetBottomBinaryTreeNode(GetBottomTreeNodeRequest request)
{
var bottomNode = new TreeNode();
using (var context = ExigoDAL.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 List <CustomerSocialNetwork> GetCustomerSocialNetwork(int customerID)
{
List <CustomerSocialNetwork> customerSocialNetworks = new List <CustomerSocialNetwork>();
using (var context = ExigoDAL.Sql())
{
customerSocialNetworks = context.Query <CustomerSocialNetwork>(@"
SELECT csc.SocialNetworkID,
csc.Url,
sc.SocialNetworkDescription
FROM customersocialNetworks csc
inner join SocialNetworks sc on csc.SocialNetworkID=sc.SocialNetworkID
WHERE csc.CustomerID = @customerID
", new
{
CustomerID = customerID
}).ToList();
}
if (customerSocialNetworks == null)
{
return(new List <CustomerSocialNetwork>());
}
return(customerSocialNetworks);
}
public static List <Order> GetTrackingUrl(List <Order> orders)
{
var shipCarriers = new List <ShipCarriers>();
var shipMethods = orders.Select(v => v.ShipMethodID).Distinct().ToList();
var trackingUrls = new List <string>();
using (var context = ExigoDAL.Sql())
{
shipCarriers = context.Query <ShipCarriers>(@"
SELECT
s.ShipMethodID
, sc.TrackingUrl
FROM ShipMethods AS s
INNER JOIN ShipCarriers AS sc
ON sc.ShipCarrierID = s.ShipCarrierID
WHERE s.ShipMethodID IN @shipmethodids
", new
{
shipmethodids = shipMethods
}).ToList();
}
foreach (var order in orders)
{
order.TrackingURL = shipCarriers.Where(v => order.ShipMethodID == v.ShipMethodID).FirstOrDefault().TrackingUrl;
}
return(orders);
}
public static void OptOutCustomer(int customerID)
{
ExigoDAL.WebService().UpdateCustomer(new UpdateCustomerRequest
{
CustomerID = customerID,
SubscribeToBroadcasts = false
});
}
public static void SetCustomerPreferredLanguage(int customerID, int languageID)
{
ExigoDAL.WebService().UpdateCustomer(new UpdateCustomerRequest
{
CustomerID = customerID,
LanguageID = languageID
});
}
public static void SetCustomerBinaryPlacementPreference(int customerID, BinaryPlacementType placementType)
{
ExigoDAL.WebService().SetBinaryPreference(new SetBinaryPreferenceRequest
{
CustomerID = customerID,
PlacementType = placementType
});
}
public static void CancelOrder(int orderID)
{
ExigoDAL.WebService().ChangeOrderStatus(new ChangeOrderStatusRequest
{
OrderID = orderID,
OrderStatus = OrderStatusType.Canceled
});
}
private static List <MasterSortItem> GetMasterItemSortOrder(int?webID = null)
{
var _webID = webID ?? GlobalSettings.Items.WebID;
var cacheKey = $"GetMasterItemSortOrder{_webID}";
if (!MemoryCache.Default.Contains(cacheKey))
{
using (var context = ExigoDAL.Sql())
{
var sortItems = context.Query <MasterSortItem>(@"
;WITH WebCat AS (
SELECT
WebCategoryID
, ParentID
, CatSortHirearchy = CAST(1000 + SortOrder AS VARCHAR(255))
, WebID
FROM
WebCategories
WHERE
ParentID IS NULL
AND WebID = @webID
UNION ALL
SELECT
child.WebCategoryID
, child.ParentID
, CatSortHirearchy = CAST(CatSortHirearchy + '.' + CAST(1000 + child.SortOrder AS VARCHAR(255)) AS VARCHAR(255))
, child.WebID
FROM
WebCat AS parent
INNER JOIN WebCategories AS child
ON child.ParentID = parent.WebCategoryID
AND child.WebID = parent.WebID
)
SELECT
MasterSortID = ROW_NUMBER() over (order by wc.CatSortHirearchy, wci.SortOrder)
, wc.WebCategoryID
, i.ItemCode
FROM WebCat AS wc
LEFT JOIN WebCategoryItems AS wci
ON wc.WebCategoryID = wci.WebCategoryID
AND wc.WebID = wci.WebID
LEFT JOIN Items AS i
on wci.ItemID = i.ItemID
" , new
{
webID = webID
}).ToList();
MemoryCache.Default.Add(cacheKey, sortItems, DateTime.Now.AddMinutes(GlobalSettings.Caching.CacheTimeouts.Short));
}
}
var data = (List <MasterSortItem>)MemoryCache.Default.Get(cacheKey);
return(data);
}
