public static customers FindCustomer(string connectionString, Func <customers, bool> condFunc)
{
using (var context = new AutoCareDataContext(connectionString))
{
return(context.Customers.FirstOrDefault(condFunc));
}
}
public static CKASINS FindOrderFromASIN(string connectionString, string ASIN, string manuf_sku)
{
using (var context = new AutoCareDataContext(connectionString))
{
bool bTryMFGId = false;
var response = context.Database.SqlQuery <CKASINS>($"SELECT CA.ItemId,CA.VariantId,P.catalogid,CA.ResourceCode FROM CK_ASINS CA LEFT JOIN `3dc_products` P ON P.SKU = CA.SKU_UPC and (CA.ForceBlock<>'Y' or CA.ForceBlock is null) and not (P.categoriesaaa is null or P.categoriesaaa='_Test_Hidden') WHERE asin_no='{ASIN}'").ToList();
if (response.Count == 0)
{
bTryMFGId = true;
}
else if (response[0].ItemId == null || response[0].catalogid == null)
{
bTryMFGId = true;
}
if (bTryMFGId)
{
// Sam: Try to get item from manuf_sku if SKU does not map
response = context.Database.SqlQuery <CKASINS>($"SELECT CA.ItemId,CA.VariantId,P.catalogid,CA.ResourceCode FROM CK_ASINS CA LEFT JOIN `3dc_products` P ON P.mfgid = CA.ItemID and (CA.ForceBlock<>'Y' or CA.ForceBlock is null) and not (P.categoriesaaa is null or P.categoriesaaa='_Test_Hidden') WHERE asin_no='{ASIN}'").ToList();
if (response.Count == 0)
{
return(null);
}
}
// var dealerPrice = context.Products.Join((context.CKVaraints.Where(I => I.SKU == sku && I.Blocked.ToLower() == "no").Join(context.ACGDealerItemPrice, C=> C.ItemID, A=> A.ItemID, (variant, price) => price)), i => i.mfgid, j => j.ItemID, (i, j) => j).FirstOrDefault();
return(response[0]); //
}
}
public static List <string> GetDistinctDealerItem(string connectionString)
{
using (var context = new AutoCareDataContext(connectionString))
{
return(context.ACGDealerItemPrice.Select(I => I.DealerCode).Distinct().ToList());
}
}
public static void SaveBaseVehicleAppData(string connectionString)
{
using (var context = new AutoCareDataContext(connectionString))
{
var appData = context.CKDownloadedVariant.OrderBy(I => I.ProductID).ThenBy(I => I.VariantID).ThenBy(I => I.SubModel).ToList();
foreach (var a in appData)
{
context.CKDownloadedVariant.Attach(a);
var baseVehicleId =
CallService(
$"http://api.coverking.com/getData.asmx/GetSubmodel?Productid={a.ProductID}&Year={a.From_Year}&Make={a.Make_Descr}&Model={a.Model_Descr}",
"//ns:Submodel_table/ns:Basevehicle");
a.BaseVehicleID = baseVehicleId;
if (baseVehicleId != null)
{
var recID =
CallService(
$"http://api.coverking.com/getData.asmx/Get_options_gen?Basevehicle={baseVehicleId}&Productid={a.ProductID}&Submodel=&Customer_IP=223.223.154.114&DealerID=ACG92",
"//ns:ArrayOfOptions/ns:Options/ns:recid");
a.RecID = recID;
context.Entry(a).Property(I => I.RecID).IsModified = true;
}
context.Entry(a).Property(I => I.BaseVehicleID).IsModified = true;
}
context.SaveChanges();
}
}
public static CKVariant FindCKVariant(string connectionString, Func <CKVariant, bool> condFunc)
{
using (var context = new AutoCareDataContext(connectionString))
{
return(context.CKVaraints.FirstOrDefault(condFunc));
}
}
public static void UpdateCustomer(ConfigurationData config, int qbId, long customerId)
{
using (var context = new AutoCareDataContext(config.ConnectionString))
{
context.Database.ExecuteSqlCommand(
$"UPDATE customers SET qb_customer_id = {qbId} WHERE customer_id = {customerId}");
context.SaveChanges();
}
}
public static void UpdateProduct(ConfigurationData config, int qbId, string sku)
{
using (var context = new AutoCareDataContext(config.ConnectionString))
{
context.Database.ExecuteSqlCommand(
$"UPDATE `3dc_products` SET qb_product_id = {qbId} WHERE SKU = '{sku}'");
context.SaveChanges();
}
}
public static List <products> GetProducts(ConfigurationData config, Func <products, bool> condFunc = null)
{
using (var context = new AutoCareDataContext(config.ConnectionString))
{
if (condFunc != null)
{
return(context.Products.Where(condFunc).ToList());
}
return(context.Products.ToList());
}
}
public static List <customers> GetAll(ConfigurationData config, Func <customers, bool> condFunc = null)
{
using (var context = new AutoCareDataContext(config.ConnectionString))
{
if (condFunc == null)
{
return(context.Customers.OrderBy(I => I.customer_id).ToList());
}
return(context.Customers.Where(condFunc).OrderBy(I => I.customer_id).ToList());
}
}
private static void AddProducts(string connectionString, List <Product> products)
{
List <products> productDB = GetProducts(products);
using (var context = new AutoCareDataContext(connectionString))
{
foreach (var prod in productDB)
{
context.Products.AddOrUpdate(prod);
}
context.SaveChanges();
}
}
public static void SaveJFWOrders(string connectionString, List <jfw_orders> orders, string filename)
{
using (var context = new AutoCareDataContext(connectionString))
{
foreach (var order in orders)
{
Console.WriteLine(string.Format(" Storing JFW PO to JFW table {0} ", order.PO));
order.Filename = filename;
context.JFWOrders.Add(order);
}
context.SaveChanges();
}
}
public static Dictionary <string, order_tracking> GetOrderTracking(string connectionString)
{
using (var context = new AutoCareDataContext(connectionString))
{
return(context.OrderTracking.Where(I => I.processed == 0).Join(
context.Orders.Where(J => J.billemail == "*****@*****.**"),
tracking => tracking.po_no, order => order.orderno, (tracking, orders) => new
{
Key = orders.po_no,
Value = tracking
}).ToDictionary(a => a.Key, a => a.Value));
}
}
public static Tuple <products, DealerItemPrice> FindOrderFromSKU(string connectionString, string sku)
{
using (var context = new AutoCareDataContext(connectionString))
{
var product = context.Products.Join(context.CKVaraints.Where(I => I.SKU == sku && I.Blocked.ToLower() == "no"), i => i.mfgid, j => j.ItemID, (i, j) => i).FirstOrDefault();
// var dealerPrice = context.Products.Join((context.CKVaraints.Where(I => I.SKU == sku && I.Blocked.ToLower() == "no").Join(context.ACGDealerItemPrice, C=> C.ItemID, A=> A.ItemID, (variant, price) => price)), i => i.mfgid, j => j.ItemID, (i, j) => j).FirstOrDefault();
DealerItemPrice dealerPrice = null;
if (null != product)
{
dealerPrice = context.ACGDealerItemPrice.FirstOrDefault(I => I.ItemID == product.SKU); // Sam 01/05/18
}
return(Tuple.Create(product, dealerPrice));
}
}
public static void UpdateOrderStatus(string connectionString, List <order_tracking> trackingList)
{
using (var context = new AutoCareDataContext(connectionString))
{
foreach (var tracking in trackingList)
{
context.Entry(tracking).State = EntityState.Modified;
context.OrderTracking.Attach(entity: tracking);
tracking.processed = 1;
context.Entry(tracking).Property(I => I.processed).IsModified = true;
}
context.SaveChanges();
}
}
public static void Save(string connectionString, IEnumerable <IEnumerable <DownloadVariant> > downloadedVariant)
{
using (var context = new AutoCareDataContext(connectionString))
{
context.Configuration.AutoDetectChangesEnabled = false;
context.Configuration.ValidateOnSaveEnabled = false;
context.Database.ExecuteSqlCommand("TRUNCATE ck_app_data;");
foreach (var items in downloadedVariant)
{
context.CKDownloadedVariant.AddRange(items);
}
context.SaveChanges();
context.Configuration.AutoDetectChangesEnabled = true;
context.Configuration.ValidateOnSaveEnabled = true;
}
}
public static void SaveItemVariantImages(string connectionString)
{
using (var context = new AutoCareDataContext(connectionString))
{
var products = context.CKProductMaster.ToList();
foreach (var p in products)
{
XmlNamespaceManager ns;
var results = CallService($"http://api.coverking.com/getData.asmx/Material_category_img_list?prod={p.product_id}&mcat=&DealerID=ACG92", out ns);
XmlNodeList xnl = results.SelectNodes("/ns:ArrayOfMaterial_category_img/ns:Material_category_img/ns:Swatches/ns:swatch", ns);
foreach (XmlNode node in xnl)
{
string materialId = node["MaterialID"].InnerText;
string imageURL = node["Image_URL"].InnerText;
}
}
}
}
public static void AddCustomers(string connectionString, List <Customer> customers, List <CustomerGroup> customerGroups)
{
var customerAndGroup = GetCustomers(customers, customerGroups);
var customerDB = customerAndGroup.Item1;
var customerGroupDB = customerAndGroup.Item2;
using (var context = new AutoCareDataContext(connectionString))
{
foreach (var cust in customerDB)
{
context.Customers.AddOrUpdate(cust);
}
foreach (var custGrp in customerGroupDB)
{
context.CustomerGroups.AddOrUpdate(custGrp);
}
context.SaveChanges();
}
}
public static void SaveCKVariant(string connectionString, List <TempCKVariant> variants)
{
var group_variant = variants.GroupBy(i => i.SKU).Select(j => j.First()).ToList();
using (var context = new AutoCareDataContext(connectionString))
{
context.Configuration.AutoDetectChangesEnabled = false;
context.Configuration.ValidateOnSaveEnabled = false;
foreach (var ck_var in group_variant)
{
var this_set = context.TempCKVariants.FirstOrDefault(i => i.SKU == ck_var.SKU);
if (this_set == null)
{
context.TempCKVariants.AddOrUpdate(ck_var);
}
}
context.SaveChanges();
context.Configuration.AutoDetectChangesEnabled = true;
context.Configuration.ValidateOnSaveEnabled = true;
}
}
public static void Save(string connectionString, IEnumerable <IEnumerable <AmazonVariant> > amazonVariants)
{
using (var context = new AutoCareDataContext(connectionString))
{
context.Configuration.AutoDetectChangesEnabled = false;
context.Configuration.ValidateOnSaveEnabled = false;
context.Database.ExecuteSqlCommand("TRUNCATE ck_amazon_variant;");
foreach (var item in amazonVariants)
{
var groupItem = item.GroupBy(i => i.ASIN).Select(j => j.First()).ToList();
context.CKAmazonVariant.AddRange(groupItem);
context.SaveChanges();
}
context.Configuration.AutoDetectChangesEnabled = true;
context.Configuration.ValidateOnSaveEnabled = true;
var data = context.Database.SqlQuery <string>(
"SELECT ASIN FROM car.ck_temp_amazon_variant GROUP BY ASIN HAVING COUNT(1) >1").ToList();
if (data.Count > 0)
{
foreach (var d in data)
{
var dupObjs = context.CKAmazonVariant.Where(I => I.ASIN == d).ToList();
foreach (var dup in dupObjs)
{
var item = context.CKDownloadedItem.FirstOrDefault(I => I.ItemID == dup.NavItem);
if (item == null)
{
context.CKAmazonVariant.Remove(dup);
}
}
//context.CKAmazonVariant.Remove()
}
}
context.SaveChanges();
}
}
public static void SaveOrderTracking(string connectionString, List <order_tracking> trackings)
{
DateTime thisDateMinus2 = DateTime.Now.AddDays(-20);
StringBuilder sb = new StringBuilder("");
using (var context = new AutoCareDataContext(connectionString))
{
context.Configuration.AutoDetectChangesEnabled = false;
context.Configuration.ValidateOnSaveEnabled = false;
var query = trackings.GroupBy(x => x.tracking_no)
.Select(y => y.FirstOrDefault());
foreach (var tracking in query)
{
var existingEntry =
context.OrderTracking.FirstOrDefault(I => I.po_no == tracking.po_no &&
I.tracking_no == tracking.tracking_no && I.SKU == tracking.SKU);
// Sam: changed on Sep 20 - only change New tracking entries.
// Sam: Changed on Sep 28 - only update if tracking data is within last 2 days
/*if (existingEntry != null)
* {
* tracking.processed = existingEntry.processed;
* }
* context.OrderTracking.AddOrUpdate(tracking);
*/
if (existingEntry == null && tracking.ship_date >= thisDateMinus2)
{
// context.OrderTracking.AddOrUpdate(tracking);
sb.AppendLine(string.Format("INSERT INTO `order_tracking` (`po_no`,`order_no`,`order_date`,`SKU`,`ship_address`,`ship_date`,`tracking_no`,`ship_agent`,`ship_service`,`processed`) values (" +
"'{0}','{1}','{2} 00:00:00','{3}','{4}','{5} 00:00:00','{6}','{7}','{8}',0);", tracking.po_no, tracking.order_no, tracking.order_date.ToString("yyyy-MM-dd"), tracking.SKU,
tracking.ship_address, tracking.ship_date.ToString("yyyy-MM-dd"), tracking.tracking_no, tracking.ship_agent, tracking.ship_service));
}
}
context.SaveChanges();
context.Configuration.AutoDetectChangesEnabled = true;
context.Configuration.ValidateOnSaveEnabled = true;
}
}
public static void SaveProductOnJobber(string connectionString)
{
using (var context = new AutoCareDataContext(connectionString))
{
var products = context.CKProductMaster.ToList();
foreach (var p in products)
{
var jobbers = context.CKDownloadedItem.Where(I => I.ItemID.StartsWith(p.product_id) && I.Product_Family_ID == p.product_family_id).ToList();
if (jobbers.Count > 0)
{
foreach (var jobber in jobbers)
{
context.CKDownloadedItem.Attach(jobber);
jobber.ProductID = p.product_id;
jobber.MaterialID = jobber.ItemID.Replace(p.product_id, "");
context.Entry(jobber).Property(I => I.ProductID).IsModified = true;
context.Entry(jobber).Property(I => I.MaterialID).IsModified = true;
}
}
}
context.SaveChanges();
}
}
public static List <FeedModel> GetASINForAmazonFeed(string connectionString, string feedType, ref int numTotalRecords) // ref long numTotalRecords
{
/* First check Channel_Sales_Helper_Details to see if any record is left from previous update attempt. If so, take top 100 from there.
* If not, insert new set of values
* */
int numRecords = 5000;
//if (feedType == "Product")
// numRecords = 5000;
string sqlQuery01 = string.Format(@"select FeedType, SKU, ASIN,SalePrice, InventoryQty,HandlingTime from channel_sales_helper_details
where FeedType='{0}' and IsUpdated=false order by ASIN Limit 0,{1}", feedType, numRecords);
string sqlQuery02 = string.Format(@"select Count(*) cnt from channel_sales_helper_details
where FeedType='{0}' and IsUpdated=false", feedType);
List <FeedModel> li = null;
using (var context = new AutoCareDataContext(connectionString))
{
var recTotal = context.Database.SqlQuery <int>(sqlQuery02).ToArray();
numTotalRecords = recTotal[0];
// = Int32.Parse(<FeedModel>(sqlQuery02).ToString()) ;
li = context.Database.SqlQuery <FeedModel>(sqlQuery01).ToList();
if (li.Count > 0)
{
return(li);
}
}
// Does not work because of mySQL datetime format - WHY ?
//using (var context = new AutoCareDataContext(connectionString))
//{
// var objDetail = context.Feed_helper_details.Where(d=> d.FeedType==feedType && d.IsUpdated==true);
// context.Feed_helper_details.RemoveRange(objDetail);
// context.SaveChanges();
//}
string strUpdateFilter = "";
string sqlQuery = "";
string strThisMySqlDate = DateTime.Now.ToString("yyyy-MM-dd hh:mm:ss");
if (feedType == "Product")
{
sqlQuery =
$@"insert into channel_sales_helper_details (ChannelName, ASIN, FeedType, SKU, SalePrice, HandlingTime, InventoryQty, IsUpdated, LastRequest, LastUpdate)
select 'Amazon', b.asin_no,'Product',b.SKU_UPC,a.SalePrice,a.HandlingTime,a.InventoryQty,false,'{strThisMySqlDate}',null
from Channel_Sales_Helper a join CK_ASINS b on a.ProductPriceCat = b.ProductPriceCat
where a.ASIN = '' and b.ActiveInAmazon = 0 and (b.ForceBlock <> 'Y' or b.ForceBlock is null) and AddProducts = 1 ; ";
}
else
{
if (feedType == "Inventory")
{
strUpdateFilter = "a.UpdateInventoryOrHandling=1";
}
else if (feedType == "Price")
{
strUpdateFilter = "a.UpdatePrice=1";
}
sqlQuery =
$@"insert into channel_sales_helper_details (ChannelName, ASIN, FeedType, SKU, SalePrice, HandlingTime, InventoryQty, IsUpdated, LastRequest, LastUpdate)
select 'Amazon', b.asin_no,'{feedType}',b.SKU_UPC,a.SalePrice, a.HandlingTime,a.InventoryQty,false,'{strThisMySqlDate}',null
from Channel_Sales_Helper a join CK_ASINS b on a.ProductPriceCat = b.ProductPriceCat
where a.ASIN = '' and b.ActiveInAmazon = 1 and (b.ForceBlock <> 'Y' or b.ForceBlock is null) and {strUpdateFilter}; ";
sqlQuery +=
$@"insert into channel_sales_helper_details (ChannelName, ASIN, FeedType, SKU, SalePrice, HandlingTime, InventoryQty, IsUpdated, LastRequest, LastUpdate)
select 'Amazon', b.asin_no,'{feedType}',b.SKU_UPC,a.SalePrice, a.HandlingTime,a.InventoryQty,false,'{strThisMySqlDate}',null
from Channel_Sales_Helper a join CK_ASINS b on a.ASIN = b.asin_no
where a.ASIN <> '' and b.ActiveInAmazon = 1 and (b.ForceBlock <> 'Y' or b.ForceBlock is null) and {strUpdateFilter} ";
}
// Delete all previous successful update records from channel_sales_helper_details
using (MySqlConnection conn = new MySqlConnection(connectionString))
{
MySqlCommand cmd = conn.CreateCommand();
cmd.CommandText =
string.Format("delete from channel_sales_helper_details where FeedType='{0}' and IsUpdated=true", feedType);
MySqlCommand cmd2 = conn.CreateCommand();
cmd2.CommandText = sqlQuery;
conn.Open();
cmd.ExecuteNonQuery();
cmd2.ExecuteNonQuery();
conn.Close();
}
using (var context = new AutoCareDataContext(connectionString))
{
var recTotal = context.Database.SqlQuery <int>(sqlQuery02).ToArray();
numTotalRecords = recTotal[0];
li = context.Database.SqlQuery <FeedModel>(sqlQuery01).ToList();
}
return(li);
}
