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