Ejemplo n.º 1
0
 public static customers FindCustomer(string connectionString, Func <customers, bool> condFunc)
 {
     using (var context = new AutoCareDataContext(connectionString))
     {
         return(context.Customers.FirstOrDefault(condFunc));
     }
 }
Ejemplo n.º 2
0
        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]); //
            }
        }
Ejemplo n.º 3
0
 public static List <string> GetDistinctDealerItem(string connectionString)
 {
     using (var context = new AutoCareDataContext(connectionString))
     {
         return(context.ACGDealerItemPrice.Select(I => I.DealerCode).Distinct().ToList());
     }
 }
Ejemplo n.º 4
0
        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();
            }
        }
Ejemplo n.º 5
0
 public static CKVariant FindCKVariant(string connectionString, Func <CKVariant, bool> condFunc)
 {
     using (var context = new AutoCareDataContext(connectionString))
     {
         return(context.CKVaraints.FirstOrDefault(condFunc));
     }
 }
Ejemplo n.º 6
0
 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();
     }
 }
Ejemplo n.º 7
0
 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();
     }
 }
Ejemplo n.º 8
0
 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());
     }
 }
Ejemplo n.º 9
0
 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());
     }
 }
Ejemplo n.º 10
0
        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();
            }
        }
Ejemplo n.º 11
0
 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();
     }
 }
Ejemplo n.º 12
0
 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));
     }
 }
Ejemplo n.º 13
0
 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));
     }
 }
Ejemplo n.º 14
0
 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();
     }
 }
Ejemplo n.º 15
0
 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;
     }
 }
Ejemplo n.º 16
0
 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;
             }
         }
     }
 }
Ejemplo n.º 17
0
        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();
            }
        }
Ejemplo n.º 18
0
        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;
            }
        }
Ejemplo n.º 19
0
        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();
            }
        }
Ejemplo n.º 20
0
        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;
            }
        }
Ejemplo n.º 21
0
 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();
     }
 }
Ejemplo n.º 22
0
        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);
        }