public List <Manufacturer> FindManufacturers(string manufacturerString)
{
List <Manufacturer> manufacturers = new List <Manufacturer>();
if (manufacturerString != null)
{
int?manufacturerId = null;
int tryParseManufacturerId;
if (int.TryParse(manufacturerString, out tryParseManufacturerId))
{
manufacturerId = tryParseManufacturerId;
}
using (AmazonMWSdbDataContext dc = m_amazonMWSdbDataContext.CreateReadInstance())
{
manufacturers.AddRange(
manufacturerId.HasValue
? dc.Manufacturers.Where(w => w.ManufacturerId == manufacturerId.Value).ToList()
: dc.Manufacturers.Where(w => w.Name.Contains(manufacturerString)).ToList());
}
}
return(manufacturers);
}
public string InsertFeePreviewData(IEnumerable <FeePreviewDto> feePreviewDtos)
{
DateTime today = DateTime.UtcNow.Date;
List <FeePreviewData> feePreviewDatas = feePreviewDtos
.Select(s => s.Map())
.ToList();
List <FeePreviewData> enumeratedFeePreviewDatas = feePreviewDatas.Select(s =>
{
s.Date = today;
return(s);
}).ToList();
using (AmazonMWSdbDataContext dc = m_amazonMWSdbDataContext.CreateWriteInstance())
{
List <FeePreviewData> duplicateData = dc.FeePreviewDatas
.Where(w => w.Date == today)
.ToList();
if (!duplicateData.Any())
{
// Batch Inventory insertion so as to avoid a SqlTimeoutException.
foreach (IEnumerable <FeePreviewData> batchedFeePreview in enumeratedFeePreviewDatas.Batch(c_defaultBatchAmount))
{
dc.FeePreviewDatas.InsertAllOnSubmit(batchedFeePreview);
dc.SubmitChanges();
}
}
}
return(string.Format("Distinct ASINs with Fee Preview: {0}", enumeratedFeePreviewDatas.Select(s => s.asin).Distinct().Count()));
}
public Models.Product GetProduct(long productId)
{
using (AmazonMWSdbDataContext dc = m_amazonMWSdbDataContext.CreateReadInstance())
{
return(dc.Products
.FirstOrDefault(f => f.ProductId == productId)
.MapProductToModel());
}
}
public Models.Product GetProduct(string asin)
{
using (AmazonMWSdbDataContext dc = m_amazonMWSdbDataContext.CreateReadInstance())
{
return(dc.Products
.FirstOrDefault(f => f.ASIN == asin)
.MapProductToModel());
}
}
public void InsertManufacturer(string manufacturerName)
{
using (AmazonMWSdbDataContext dc = m_amazonMWSdbDataContext.CreateWriteInstance())
{
dc.Manufacturers.InsertOnSubmit(new Manufacturer {
Name = manufacturerName
});
dc.SubmitChanges();
}
}
public PriceHistory GetMostRecentAsinPrice(string asin)
{
using (AmazonMWSdbDataContext dc = m_amazonMWSdbDataContext.CreateReadInstance())
{
return(dc.PriceHistories
.Where(w => w.ASIN == asin)
.OrderByDescending(w => w.TimeStamp)
.FirstOrDefault());
}
}
public void InsertPriceHistory(List <PriceHistory> priceHistory)
{
using (AmazonMWSdbDataContext dc = m_amazonMWSdbDataContext.CreateWriteInstance())
{
foreach (IEnumerable <PriceHistory> batchedPriceHistory in priceHistory.Batch(c_defaultBatchAmount))
{
dc.PriceHistories.InsertAllOnSubmit(batchedPriceHistory);
dc.SubmitChanges();
}
}
}
public List <Models.Product> GetProductsForManufacturer(int manufacturerId)
{
using (AmazonMWSdbDataContext dc = m_amazonMWSdbDataContext.CreateReadInstance())
{
return(dc.Products
.Where(w => w.ManufacturerId == manufacturerId)
.ToList()
.Select(s => s.MapProductToModel())
.ToList());
}
}
public void InsertListingOffersLog(List <ListingOffersLog> listingOffersLogs)
{
using (AmazonMWSdbDataContext dc = m_amazonMWSdbDataContext.CreateWriteInstance())
{
foreach (IEnumerable <ListingOffersLog> batchedListingOffersLogs in listingOffersLogs.Batch(c_defaultBatchAmount))
{
dc.ListingOffersLogs.InsertAllOnSubmit(batchedListingOffersLogs);
dc.SubmitChanges();
}
}
}
public decimal?GetCostOfProductForAsin(string asin)
{
using (AmazonMWSdbDataContext dc = m_amazonMWSdbDataContext.CreateReadInstance())
{
Product item = dc.Products.FirstOrDefault(w => w.ASIN == asin);
if (item == null)
{
return(null);
}
return(item.Cost);
}
}
public string InsertUnsuppressedInventoryData(IEnumerable <UnsuppressedInventoryDto> unsuppressedInventoryDtos)
{
DateTime today = DateTime.UtcNow.Date;
List <UnsuppressedInventoryData> unsuppressedInventoryDatas = unsuppressedInventoryDtos
.Select(s => s.Map())
.ToList();
List <UnsuppressedInventoryData> enumeratedUnsuppressedInventoryDatas = unsuppressedInventoryDatas
.Select(s =>
{
s.Date = today;
return(s);
})
.ToList();
using (AmazonMWSdbDataContext dc = m_amazonMWSdbDataContext.CreateWriteInstance())
{
List <UnsuppressedInventoryData> duplicateData = dc.UnsuppressedInventoryDatas
.Where(w => w.Date == today)
.ToList();
if (!duplicateData.Any())
{
// Batch Inventory insertion so as to avoid a SqlTimeoutException.
foreach (IEnumerable <UnsuppressedInventoryData> batchedInventory in enumeratedUnsuppressedInventoryDatas.Batch(c_defaultBatchAmount))
{
dc.UnsuppressedInventoryDatas.InsertAllOnSubmit(batchedInventory);
dc.SubmitChanges();
}
}
List <long?> fulfillableItems = enumeratedUnsuppressedInventoryDatas
.GroupBy(g => g.asin)
.Select(s => s.First().afn_fulfillable_quantity)
.ToList();
return(string.Format("ASINs With Fulfillable Units: {0} \n Units Fulfillable: {1}", fulfillableItems.Count(w => w > 0), fulfillableItems.Sum()));
}
}
public List <FeePreview> GetFeePreviewForAsin(string asin)
{
using (AmazonMWSdbDataContext dc = m_amazonMWSdbDataContext.CreateReadInstance())
{
return(dc.FeePreviewDatas
.Where(w => w.asin == asin)
.GroupBy(g => g.asin)
.Select(s => s.OrderByDescending(o => o.Date).First())
.GroupBy(g => g.asin)
.Select(s => s.OrderBy(o => o.your_price).First())
.Select(s => new FeePreview
{
ASIN = s.asin,
SKU = s.sku,
Name = s.product_name,
EstimatedOrderHandlingFeePerOrder = s.estimated_order_handling_fee_per_order,
EstimatedPickPackFeePerUnit = s.estimated_pick_pack_fee_per_unit,
EstimatedWeightHandlingFeePerUnit = s.estimated_weight_handling_fee_per_unit
})
.ToList());
}
}
public void InsertProducts(IEnumerable <Models.Product> items, int manufacturerId)
{
DateTime createdDate = DateTime.UtcNow;
List <Product> enumeratedProducts = items
.Select(s => s.MapProductToData())
.Select(s =>
{
// Set certain variables no matter the input.
s.ManufacturerId = manufacturerId;
s.CreatedDate = createdDate;
s.ModifiedDate = createdDate;
return(s);
})
.ToList();
using (AmazonMWSdbDataContext dc = m_amazonMWSdbDataContext.CreateWriteInstance())
{
dc.Products.InsertAllOnSubmit(enumeratedProducts);
dc.SubmitChanges();
}
}
public AmazonMWSdbDataContext CreateReadInstance()
{
AmazonMWSdbDataContext dc = new AmazonMWSdbDataContext(Settings.Default.amazonmwsConnectionString);
return(dc);
}
public Models.Product EditProduct(Models.Product product)
{
DateTime modifiedDate = DateTime.UtcNow;
Product dataProduct = product.MapProductToData();
Product productToReturn;
using (AmazonMWSdbDataContext dc = m_amazonMWSdbDataContext.CreateWriteInstance())
{
Product databaseProduct = null;
// Don't look up the product if we are attempting to create it.
if (product.ProductId.HasValue)
{
databaseProduct = dc.Products.SingleOrDefault(f => f.ProductId == product.ProductId);
}
if (databaseProduct != null)
{
// ModifiedDate is updated, CreatedDate is not changed.
databaseProduct.ModifiedDate = modifiedDate;
databaseProduct.UPC = dataProduct.UPC != null?dataProduct.UPC.Trim() : dataProduct.UPC;
databaseProduct.Name = dataProduct.Name != null?dataProduct.Name.Trim() : dataProduct.Name;
databaseProduct.Cost = dataProduct.Cost;
databaseProduct.PromotionCost = dataProduct.PromotionCost;
databaseProduct.MAPPrice = dataProduct.MAPPrice;
databaseProduct.MinPrice = dataProduct.MinPrice;
databaseProduct.MaxPrice = dataProduct.MaxPrice;
databaseProduct.BreakevenPrice = dataProduct.BreakevenPrice;
databaseProduct.OverrideCost = dataProduct.OverrideCost;
databaseProduct.QuantityInCase = dataProduct.QuantityInCase;
databaseProduct.ASIN = dataProduct.ASIN != null?dataProduct.ASIN.Trim() : dataProduct.ASIN;
databaseProduct.SKU = dataProduct.SKU != null?dataProduct.SKU.Trim() : dataProduct.SKU;
databaseProduct.ItemNumber = dataProduct.ItemNumber != null?dataProduct.ItemNumber.Trim() : dataProduct.ItemNumber;
databaseProduct.ManufacturerId = dataProduct.ManufacturerId;
databaseProduct.Length = dataProduct.Length;
databaseProduct.Width = dataProduct.Width;
databaseProduct.Height = dataProduct.Height;
databaseProduct.Weight = dataProduct.Weight;
databaseProduct.IsMAP = dataProduct.IsMAP;
databaseProduct.IsDiscontinued = dataProduct.IsDiscontinued;
databaseProduct.Notes = dataProduct.Notes;
productToReturn = databaseProduct;
}
else
{
DateTime today = DateTime.UtcNow;
dataProduct.CreatedDate = today;
dataProduct.ModifiedDate = today;
// If the product doesn't already exist in the database, create it.
dc.Products.InsertOnSubmit(dataProduct);
productToReturn = dataProduct;
}
dc.SubmitChanges();
}
return(productToReturn.MapProductToModel());
}
public AmazonMWSdbService()
{
m_amazonMWSdbDataContext = new AmazonMWSdbDataContext();
}
public string InsertAllOrdersData(IEnumerable <AllOrderDto> allOrdersDtos)
{
List <AllOrdersData> allOrdersDatas = allOrdersDtos.Select(s => s.Map()).ToList();
List <AllOrdersData> groupedAllOrdersData = allOrdersDatas
.GroupBy(g => new { g.amazon_order_id, g.sku })
.Select(s => new { value = s.First(), ExtendedPriceSum = s.Sum(z => z.item_price), QuantitySum = s.Sum(z => z.quantity) })
.Select(s => new AllOrdersData
{
amazon_order_id = s.value.amazon_order_id,
sku = s.value.sku,
asin = s.value.asin,
product_name = s.value.product_name,
merchant_order_id = s.value.merchant_order_id,
purchase_date = s.value.purchase_date,
last_updated_date = s.value.last_updated_date,
order_status = s.value.order_status,
fulfillment_channel = s.value.fulfillment_channel,
sales_channel = s.value.sales_channel,
order_channel = s.value.order_channel,
url = s.value.url,
ship_service_level = s.value.ship_service_level,
item_status = s.value.item_status,
quantity = s.QuantitySum, // Use the sum of the quantity
currency = s.value.currency,
item_price = s.ExtendedPriceSum, // Use the sum of the item_price
item_tax = s.value.item_tax,
shipping_price = s.value.shipping_price,
shipping_tax = s.value.shipping_tax,
gift_wrap_price = s.value.gift_wrap_price,
gift_wrap_tax = s.value.gift_wrap_tax,
item_promotion_discount = s.value.item_promotion_discount,
ship_promotion_discount = s.value.ship_promotion_discount,
ship_city = s.value.ship_city,
ship_state = s.value.ship_state,
ship_postal_code = s.value.ship_postal_code,
ship_country = s.value.ship_country,
promotion_ids = s.value.promotion_ids
}).ToList();
using (AmazonMWSdbDataContext dc = m_amazonMWSdbDataContext.CreateWriteInstance())
{
List <AllOrdersData> failedToInsertOrders = new List <AllOrdersData>();
HashSet <AllOrdersData> last30DaysAllOrdersDatas = new HashSet <AllOrdersData>(dc.AllOrdersDatas.Where(w => w.purchase_date > DateTime.UtcNow.Date.AddDays(-30)), new AllOrdersDataComparer());
// Use the AllOrdersDataComparer in the last30DaysAllOrdersDatas HashSet, for use by the .Contains() function.
List <AllOrdersData> newOrdersToInsert = groupedAllOrdersData.Where(w => !last30DaysAllOrdersDatas.Contains(w)).ToList();
long newOrdersCount = newOrdersToInsert.Count;
foreach (AllOrdersData newOrderToInsert in newOrdersToInsert)
{
try
{
dc.AllOrdersDatas.InsertOnSubmit(newOrderToInsert);
dc.SubmitChanges();
}
catch (Exception)
{
failedToInsertOrders.Add(newOrderToInsert);
}
}
// We may fail to insert orders if an order changes it's status after 30 days.
failedToInsertOrders.ForEach(f =>
{
if (!dc.AllOrdersDatas.Any(a => a.amazon_order_id == f.amazon_order_id && a.sku == f.sku))
{
dc.AllOrdersDatas.InsertOnSubmit(f);
dc.SubmitChanges();
}
else
{
newOrdersCount--;
}
});
List <AllOrdersData> pendingOrders = dc.AllOrdersDatas
.Where(w => w.order_status == "Pending")
.ToList();
long updatedOrdersCount = 0;
pendingOrders.ForEach(f =>
{
AllOrdersData selectedOrder = groupedAllOrdersData.FirstOrDefault(z => z.amazon_order_id == f.amazon_order_id && z.sku == f.sku && z.order_status != "Pending");
if (selectedOrder != null)
{
updatedOrdersCount++;
dc.AllOrdersDatas.DeleteOnSubmit(f);
dc.AllOrdersDatas.InsertOnSubmit(selectedOrder);
dc.SubmitChanges();
}
});
// Retrieve the pending orders again since we may have new pending orders, as well as updating orders
pendingOrders = dc.AllOrdersDatas
.Where(w => w.order_status == "Pending")
.ToList();
return(string.Format("New Orders: {0} \n Updated orders: {1} \n Orders pending: {2}", newOrdersCount, updatedOrdersCount, pendingOrders.Count));
}
}