/// <summary>
/// 必选区为空的产品
/// </summary>
private static void GetProductValIsNull()
{
var sql = @"select p11.ID,p11.SPUID,p11.SiteID from TB_Product p11 with(nolock)
left join
View_OptionContent v WITH(NOLOCK)
on v.SiteID=p11.SiteID and v.CurrentID=p11.ID and v.Type=2 and v.SiteID=9
WHERE p11.SiteID=9 and v.ID is null and (p11.State=1 or p11.State=0)
group by p11.ID,p11.SPUID,p11.SiteID";
var dt = SqlDbHelper.ExecuteDataTable(sql);
ExeclHelper execl = new ExeclHelper();
execl.DataTabletoExcel(dt, "D:\\必选区为空产品SiteID_9.xlsx");
Console.WriteLine("必选区为空产品SiteID_9");
}
/// <summary>
/// 供应商产品
/// </summary>
/// <returns></returns>
private static void GetProductSource()
{
var sql = @"select p.ID '产品ID',p.SPUID,pr.ProductNo '供应商编号',p.Images '产品首图（大图/原图）访问链接',(case when p.Stock>0 then '有' else '无' end) '是否有货' from TB_Product p with(nolock)
left join TB_ProductRelationShip pr with(nolock) on p.SiteID=pr.SiteID and p.ID=pr.PID
where p.SiteID=19 and (p.State=2 or p.State=1) and (pr.ProductNo like '%NTJF%' or pr.ProductNo like '%QXP%' or pr.ProductNo like '%NTGC%' or pr.ProductNo like '%HZ-DG%' or pr.ProductNo like '%TT-LX%' or pr.ProductNo like '%YPLT%')";
var dt = Framework.SqlDbHelper.ExecuteDataTable(sql);
for (var i = 0; i < dt.Rows.Count; i++)
{
var images = dt.Rows[i]["产品首图（大图/原图）访问链接"].ToString();
images = GetOneProductImg(images);
dt.Rows[i]["产品首图（大图/原图）访问链接"] = images;
}
ExeclHelper execl = new ExeclHelper();
execl.DataTabletoExcel(dt, "D:\\供应商HZ-DG在售产品导表.xlsx");
Console.WriteLine("供应商HZ-DG在售产品导表");
}
public static void GetData(int index)
{
DataTable newdt = new DataTable();
newdt.Columns.Add("品类ID", System.Type.GetType("System.String"));
newdt.Columns.Add("SPUID", System.Type.GetType("System.String"));
newdt.Columns.Add("SiteID", System.Type.GetType("System.String"));
newdt.Columns.Add("Color", System.Type.GetType("System.String"));
newdt.Columns.Add("Size", System.Type.GetType("System.String"));
int endindex = (index + 1) * (count / 20);
int startindex = index * (count / 20);
if (index == 19)
{
endindex = dt.Rows.Count;
}
for (int i = startindex; i < endindex; i++)
{
var remark2 = dt.Rows[i]["Remark2"] != DBNull.Value ? Convert.ToString(dt.Rows[i]["Remark2"]) : "";
string color = "";
string size = "";
int currencyInt = 1;
if (!string.IsNullOrEmpty(remark2))
{
remark2 = remark2.Replace("@@", "@");
remark2 = remark2.Trim('@');
remark2 = remark2.Replace("Material - Synthetic Wigs-Machine Weft", "");
if (remark2.Contains("@~"))
{
remark2 = remark2.Substring(0, remark2.IndexOf("@~"));
}
string temp_remark2 = ReversionShopCartRemark(remark2);
System.Collections.Generic.List <string> listcm = new System.Collections.Generic.List <string>();
string[] remark2s = remark2.Split(new char[] { '@' }, StringSplitOptions.RemoveEmptyEntries);
string[] tempRemark2s = temp_remark2.Split(new char[] { '@' }, StringSplitOptions.RemoveEmptyEntries);
if (remark2s.Length != tempRemark2s.Length)
{
temp_remark2 = remark2;
}
if (temp_remark2.Length > 0)
{
string itemString = "";
int itemint = 0;
string remark3 = temp_remark2.Replace("Material - Synthetic Wigs-Machine Weft", "");
string[] strs = remark3.Split(new char[] { '@' }, StringSplitOptions.RemoveEmptyEntries);
foreach (string item in strs)
{
itemString += item;
if (item.Contains("~") && !item.Contains("-"))
{
string[] customAttr = item.Split(new char[] { '~' }, StringSplitOptions.RemoveEmptyEntries);
if (customAttr.Length <= 0)
{
continue;
}
foreach (string customItem in customAttr)
{
string[] custom = customItem.Split(new char[] { '-' }, StringSplitOptions.RemoveEmptyEntries);
if (custom.Length < 2)
{
continue;
}
string key = custom[0];
string values = custom[1];
if (key.ToLower().Contains("color") && !string.IsNullOrEmpty(values))
{
values = TranslateColor(values.Trim(), currencyInt);
color = values;
}
itemint++;
}
}
else
{
string[] attr = item.Split(new char[] { '-' }, StringSplitOptions.RemoveEmptyEntries);
if (attr.Length < 2)
{
attr = item.Split(new char[] { ':' }, StringSplitOptions.RemoveEmptyEntries);
}
if (attr.Length < 2)
{
attr = item.Split(new char[] { '：' }, StringSplitOptions.RemoveEmptyEntries);
}
if (attr.Length < 2 && attr.Length == 1)
{
}
else
{
string key = attr[0];
string values = item.Substring(attr[0].Length + 1, item.Length - attr[0].Length - 1);
if (key.ToLower().Contains("size") && !string.IsNullOrEmpty(values))
{
size = values;
}
if (key.ToLower().Contains("color") && !string.IsNullOrEmpty(values))
{
color = values;
}
}
itemint++;
}
}
}
}
DataRow dr = newdt.NewRow();
dr["品类ID"] = dt.Rows[i]["品类ID"] != DBNull.Value ? dt.Rows[i]["品类ID"].ToString() : "";
dr["SPUID"] = dt.Rows[i]["SPUID"] != DBNull.Value ? dt.Rows[i]["SPUID"].ToString() : "";
dr["SiteID"] = dt.Rows[i]["SiteID"] != DBNull.Value ? dt.Rows[i]["SiteID"].ToString() : "";
dr["Color"] = color;
dr["Size"] = size;
newdt.Rows.Add(dr);
if (i % 1000 == 0)
{
Console.WriteLine("线程" + index + "_数据已完成：" + ((i - startindex) * 100 / Convert.ToDouble(endindex - startindex)).ToString("0.00") + "%");
}
}
Console.WriteLine("线程" + index + "_数据已完成,开始导出Execl文件");
ExeclHelper execl = new ExeclHelper();
execl.DataTabletoExcel(newdt, "D:\\SKU优化基础数据_" + index + ".xlsx");
Console.WriteLine("导出Execl完成:" + index);
}