public static FileInfoModel GetSourceFile(int id) { EcommerceCMSEntities db = new EcommerceCMSEntities(); SupplierSource ss = db.SupplierSources.SingleOrDefault(s => s.Id == id); FileInfoModel fi = new FileInfoModel(); fi.Url = ss.FileSample != null?string.Format("{0}/data/sourcefiles/{1}", HttpContext.Current.Request.Url.AbsoluteUri, ss.FileSample) : null; fi.FileFields = ss.CSVColumns != null?ss.CSVColumns.Split(',').ToList() : null; fi.DBFields = ss.DBColumns != null?ss.DBColumns.Split(',').ToList() : null; return(fi); }
public static List <ProductModel> GetProductsFromWebsiteCategory(int webId, int categoryId) { List <ProductModel> prods = new List <ProductModel>(); EcommerceCMSEntities db = new EcommerceCMSEntities(); //get website connection info List <Website> webs = new List <Website>(); if (webId > 0) { webs.Add(db.Websites.SingleOrDefault(w => w.Id == webId)); } else { webs = db.Websites.ToList(); } foreach (Website web in webs) { string connStr = string.Format("server={0};database={1};uid={2};pwd={3};", web.ServerName, web.DatabaseName, web.Username, web.Password); //get products using (SqlConnection conn = new SqlConnection(connStr)) { conn.Open(); SqlCommand comm = new SqlCommand(string.Format("select p.id, p.sku, p.name, p.shortdescription, p.price, p.productcost, logo = '{0}/content/images/thumbs/' + RIGHT('0000000' + CAST(p1.Id AS VARCHAR(7)), 7) + '_' + p1.seofilename + '.jpeg' from product p inner join Product_Category_Mapping pcm on pcm.productid = p.id inner join category c on c.id = pcm.categoryid inner join product_picture_mapping ppm on ppm.pictureid = p.id inner join picture p1 on p1.id = ppm.productid where c.id = {1}", web.Url, categoryId), conn); SqlDataReader reader = comm.ExecuteReader(); while (reader.Read()) { prods.Add(new ProductModel() { Id = int.Parse(reader["id"].ToString()), Sku = reader["sku"].ToString(), Name = reader["name"].ToString(), Description = reader["shortdescription"].ToString(), ImgUrl = reader["logo"].ToString(), Wholesale = decimal.Parse(reader["productcost"].ToString()), Retail = decimal.Parse(reader["price"].ToString()) }); } } } return(prods); }
public static List <CategoryModel> GetCategoriesFromWebsite(int webId, int parentCatId) { List <CategoryModel> cats = new List <CategoryModel>(); EcommerceCMSEntities db = new EcommerceCMSEntities(); //get website connection info List <Website> webs = new List <Website>(); if (webId > 0) { webs.Add(db.Websites.SingleOrDefault(w => w.Id == webId)); } else { webs = db.Websites.ToList(); } foreach (Website web in webs) { string connStr = string.Format("server={0};database={1};uid={2};pwd={3};", web.ServerName, web.DatabaseName, web.Username, web.Password); //get categories using (SqlConnection conn = new SqlConnection(connStr)) { conn.Open(); SqlCommand comm = new SqlCommand(string.Format("select c1.id, c1.name, subcount = (select count(*) from category c2 where c1.id = c2.parentcategoryid), subcats = Stuff((Select ' <span class=\"btn btn-sm btn-info spanMngProds2\" data-parlevel=\"2\" data-parid=\"' + cast(c1.id as nvarchar(10)) + '\" data-parname=\"' + c1.name + '\" data-level=\"3\" data-id=\"' + cast(c3.id as nvarchar(10)) + '\" data-name=\"' + c3.name + '\">' + lower(c3.name) + '</span>' from category c3 where c3.parentcategoryid = c1.id for XML PATH('')),1,1,''), logo = '{0}/content/images/thumbs/' + RIGHT('0000000'+CAST(p1.Id AS VARCHAR(7)),7) + '_' + p1.seofilename + '.jpeg', productCount = (select count(pcm1.id) from Product_Category_Mapping pcm1 where pcm1.categoryid = c1.id) from category c1 inner join Picture p1 on p1.id = c1.pictureId where c1.parentcategoryid = {1}", web.Url, parentCatId), conn); SqlDataReader reader = comm.ExecuteReader(); while (reader.Read()) { cats.Add(new CategoryModel() { Id = int.Parse(reader["id"].ToString()), Name = reader["name"].ToString(), SubCount = int.Parse(reader["subcount"].ToString()), SubCats = reader["subcats"].ToString().Replace("<", "<").Replace(">", ">"), Logo = reader["logo"].ToString(), ProductCount = int.Parse(reader["productCount"].ToString()) }); } } } return(cats); }