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