public static List <img> createMissingImageList(object[] errorText, int crawlID) { // If we weren't passed any data, don't waste any time if (errorText == null) { return(null); } List <img> icsList = new List <img>(); //string[] elements = new string[0]; string temp; string imageName; foreach (string[] elements in errorText) { // constraints if (elements[0].Length <= 0) { break; } if (elements[1].Contains("catalog.jsp")) { continue; } try { imageName = elements[0].Trim().Substring(elements[0].Trim().LastIndexOf('/') + 1, elements[0].Trim().IndexOf('?') - elements[0].Trim().LastIndexOf('/') - 1); temp = elements[0].Trim().Substring(elements[0].Trim().LastIndexOf("wid=") + 4, elements[0].Trim().Substring(elements[0].Trim().LastIndexOf("wid=")).IndexOf("&") - 4); // URL string url = elements[0]; // Product Id string productId; if (elements[1].Contains("product")) { productId = elements[1].Substring(elements[1].IndexOf("product/prd-") + 12); productId = productId.Substring(0, productId.IndexOf("/")); } else { productId = ""; } // Color string color = imageName.Substring(imageName.IndexOf("_") + 1); // Type string type; if (temp.Equals("30")) { type = "Swatch"; } else if (temp.Equals("1000")) { type = "Zoom"; } else if (temp.Equals("350")) { type = "Main"; } else if (temp.Equals("50")) { type = "Carousel"; } else if (temp.Equals("20")) { type = "Swatch-Col"; } else { type = "Other"; } // nav_from string nav_from = elements[1]; // Generate the img instance img curr = new img(url, productId, "NULL", type, imageName, nav_from, color); icsList.Add(curr); } catch (Exception ex) { logger.Error("PARSING ERROR - " + ex.Message); } } return(icsList); }
public static void addDepartmentNumbers(int crawlID) { List <img> icsList = new List <img>(); // populate the icsList with db data using (DataSet ds = Crawler.getMissingImageManifest(crawlID)) { List <string[]> storage = new List <string[]>(); foreach (DataTable table in ds.Tables) { foreach (DataRow dr in table.Rows) { icsList.Add(new img(dr[0].ToString(), dr[1].ToString(), dr[2].ToString(), dr[3].ToString(), dr[4].ToString(), dr[5].ToString(), dr[6].ToString())); } } } // hurr durr pointer List <img> icsListBackup = new List <img>(icsList.ToArray()); int count = 0; Dictionary <string, string> map = new Dictionary <string, string>(); // init database con = new OracleConnection(); con.ConnectionString = "Data Source=" + "(DESCRIPTION = " + "(ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.4)(PORT = 9101))" + "(CONNECT_DATA =" + "(SID = KOHLDBPA1)" + ")" + ");Persist Security Info=True;User ID=pkmdes2;Password=pkd2iu4ro"; // build the query string prodIds = ""; string collIds = ""; // chunk in 500s while (icsList.Count > 0) { prodIds = ""; for (int i = 0; i <= 500; i++) { if (icsList.Count != 0) { img curr = icsList[0]; //foreach (img curr in icsList) { // might as well count here count++; if (!curr.productId.Contains("c")) { prodIds += "'" + curr.productId + "'" + ", "; } else { collIds += "'" + curr.productId + "'" + ", "; } icsList.Remove(curr); } } // fix leading comma if (prodIds.Length != 0) { prodIds = prodIds.Substring(0, prodIds.Length - 2); } if (collIds.Length != 0) { collIds = collIds.Substring(0, collIds.Length - 2); } // Generate sql string sql = "SELECT product_id, max(dept_no) from atgprdcata.kls_sku sku"; sql += " inner join atgprdcata.dcs_prd_chldsku child on child.sku_id = sku.sku_id"; sql += " where product_id in (" + prodIds + ") group by product_id"; string csql = "select cpr.category_id, max(sku.dept_no) "; csql += "from atgprdcata.kls_sku sku, atgprdcata.dcs_prd_chldsku chs, atgprdcata.dcs_cat_chldprd cpr "; csql += "where sku.sku_id = chs.sku_id"; csql += " and cpr.child_prd_id = chs.product_id"; csql += " and cpr.child_prd_id = (select max(cpr2.child_prd_id)"; csql += " from atgprdcata.dcs_cat_chldprd cpr2"; csql += " where cpr2.category_id = cpr.category_id"; csql += " group by cpr2.category_id)"; csql += " and (cpr.category_id in ( " + collIds + " ))group by cpr.CATEGORY_ID"; // Run query OracleCommand command = new OracleCommand(sql, con); OracleCommand scommand = new OracleCommand(csql, con); OracleDataReader reader; try { // ProdIds con.Open(); reader = command.ExecuteReader(); while (reader.Read()) { try { map.Add(reader.GetString(0), reader.GetString(1)); } catch (Exception e) { // nothing to see here, duplicate map add } } // CollIds reader = scommand.ExecuteReader(); while (reader.Read()) { try { map.Add(reader.GetString(0), reader.GetString(1)); } catch (Exception e) { // nothing to see here, duplicate map add } } } catch (Exception ex) { // Shits broke yo logger.Error("Error executing dept num query"); logger.Error(ex.Message); } finally { con.Close(); } } // refer to backup... really should clean this up icsList = icsListBackup; // Now map them dept_id's foreach (var s in map) { for (int i = 0; i < icsList.Count; i++) { if (s.Key == icsList[i].productId) { icsList[i].dept = s.Value; } } } // update db ArrayList sqls = new ArrayList(); foreach (img ics in icsList) { if (ics.productId != "" && ics.dept != "") { string sql = String.Format("UPDATE crawlerDB.dbo.missingImageManifest SET dept = {0} WHERE crawlID = {1} AND url='{2}'", ics.dept, crawlID, ics.url); sqls.Add(sql); } } //string[] a = sqls.ToArray(typeof(string)) as string[]; crawlerWebService.Crawler.updateDeptNums(sqls.ToArray(typeof(string)) as string[]); // Output logger.Info(String.Format("{0}/{1} images processed", count, icsList.Count)); }