public CASInfo GetCASByCASNO(string CAS_NO)
{
CASInfo _CASInfo = new CASInfo();
string strsql = string.Format("SELECT * FROM ChemCloud_CAS with(nolock) where CAS='{0}'", CAS_NO);
DataTable dt = DbHelperSQLCAS.QueryDataTable(strsql);
if (dt != null && dt.Rows.Count > 0)
{
_CASInfo = GetEntity <CASInfo>(dt);
}
if (_CASInfo == null)
{
CASInfo info = new CASInfo();
info.Pub_CID = 0;
return(info);;
}
else
{
return(_CASInfo);
}
}
/// <summary>
/// 分页获取数据列表
/// </summary>
public DataSet GetListByPage(string strWhere, string orderby, int startIndex, int endIndex)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("SELECT * FROM ( ");
strSql.Append(" SELECT ROW_NUMBER() OVER (");
if (!string.IsNullOrEmpty(orderby.Trim()))
{
strSql.Append("order by T." + orderby);
}
else
{
strSql.Append("order by T.Pub_CID");
}
strSql.Append(")AS Row,T.PUB_CID,T.Record_Title,T.CAS,T.Molecular_Formula,T.Molecular_Weight,T.[2D_Structure] as Dataurl,T.CHINESE from ChemCloud_CAS T ");
if (!string.IsNullOrEmpty(strWhere.Trim()))
{
strSql.Append(" WHERE " + strWhere);
}
strSql.Append(" ) TT");
strSql.AppendFormat(" WHERE TT.Row between {0} and {1}", startIndex, endIndex);
return(DbHelperSQLCAS.Query(strSql.ToString()));
}
public JsonResult SearchResultCount(string keywords, string islikevalue, string exp_keywords = "")
{
#region SQL屏蔽'
if (keywords.Contains("'"))
{
keywords = keywords.Replace("'", "");
}
if (exp_keywords.Contains("'"))
{
exp_keywords = exp_keywords.Replace("'", "");
}
#endregion
string strsql = string.Format("SELECT COUNT(0) AS COUNT FROM ChemCloud_CAS where ");
if (!string.IsNullOrEmpty(exp_keywords))
{
strsql += string.Format(" InChI_Key ='{0}'", exp_keywords);
}
else
{
if (islikevalue == "0")
{
if (Regex.IsMatch(keywords, @"[0-9?-]") && !Regex.IsMatch(keywords, @"[A-Za-z]+") && !Regex.IsMatch(keywords, @"[\u4E00-\u9FA5]"))
{
/*cas#查询 */
if (keywords.Contains("~"))
{
string sqlsace = "(";
string[] array = keywords.Split('~');
for (int i = 0; i < array.Length; i++)
{
if (!string.IsNullOrEmpty(array[i]))
{
sqlsace = sqlsace + "'" + array[i] + "',";
}
}
sqlsace = sqlsace.Substring(0, sqlsace.Length - 1);
sqlsace = sqlsace + ")";
strsql += string.Format(" CAS in {0} ", sqlsace);
}
else
{
strsql += string.Format(" CAS = '{0}' ", keywords);
}
}
else if (Regex.IsMatch(keywords, @"[\u4E00-\u9FA5]"))
{
strsql += string.Format(" (CHINESE = '{0}' OR CHINESE_ALIAS = '{0}') ", keywords);
}
else
{
strsql += string.Format(" (Record_Title = '{0}' OR Molecular_Formula ='{0}' OR INCHI_KEY ='{0}') ", keywords);
}
}
else
{
if (Regex.IsMatch(keywords, @"[0-9?-]") && !Regex.IsMatch(keywords, @"[A-Za-z]+") && !Regex.IsMatch(keywords, @"[\u4E00-\u9FA5]"))
{
strsql += string.Format(" CAS like '{0}%' ", keywords);
}
else if (Regex.IsMatch(keywords, @"[\u4E00-\u9FA5]"))
{
strsql += string.Format(" (CHINESE like '{0}%' OR CHINESE_ALIAS like '{0}%') ", keywords);
}
else
{
strsql += string.Format(" (Record_Title like '{0}%' OR Molecular_Formula like '{0}%' OR INCHI_KEY like '{0}%') ", keywords);
}
}
}
DataSet ds = DbHelperSQLCAS.Query(strsql);
if (ds == null)
{
return(Json("0"));
}
else
{
return(Json(ds.Tables[0].Rows[0]["COUNT"].ToString()));
}
}
public JsonResult NewImportProducts(long shopid, string filename)
{
/*供应商产品批量导入*/
if (!string.IsNullOrWhiteSpace(filename))
{
/*计数统计*/
int ErrorCount = 0, SubErrorCount = 0, RepeatCount = 0, SuccessCount = 0, NullCount = 0;
/*读取excel*/
DataTable dt = GetExcelData(filename);
dt.Rows.RemoveAt(0); //移除第一行
if (dt != null && dt.Rows.Count > 0)
{
for (int i = 0; i < dt.Rows.Count; i++)
{
/*必填项 ,如果存在空的值，就跳过*/
if (dt.Rows[i]["SupplierProductId"] == null ||
dt.Rows[i]["EnglishDescription"] == null ||
dt.Rows[i]["PackSize"] == null ||
dt.Rows[i]["RegulatedProduct"] == null ||
dt.Rows[i]["RegulatoryCountry"] == null ||
dt.Rows[i]["RMB_CN"] == null)
{
NullCount = NullCount + 1;
continue;
}
/*产品货号唯一*/
if ((ServiceHelper.Create <IProductService>().IsExitsProductCode(shopid, dt.Rows[i]["SupplierProductId"].ToString())))
{
RepeatCount = RepeatCount + 1;
continue;
}
try
{
ProductInfo Product = new ProductInfo();
Product.ShopId = shopid; /*供应商编号*/
Product.CategoryId = 80; /*产品分类*/
Product.CategoryPath = "2|76|80";
Product.ProductCode = dt.Rows[i]["SupplierProductId"] == null ? "" : dt.Rows[i]["SupplierProductId"].ToString(); /*产品货号*/
Product.EProductName = dt.Rows[i]["EnglishDescription"] == null ? "" : dt.Rows[i]["EnglishDescription"].ToString(); /*英文描述*/
Product.ProductName = dt.Rows[i]["ChineseDescription"] == null ? "" : dt.Rows[i]["ChineseDescription"].ToString(); /*中文名*/
Product.ShortDescription = dt.Rows[i]["RegulatedProduct"].ToString(); /*是否属于管制品(Y/N)*/
Product.RelatedProducts = dt.Rows[i]["RegulatoryCountry"].ToString(); /*管制国家*/
Product.Purity = dt.Rows[i]["Purity"] == null ? "" : dt.Rows[i]["Purity"].ToString();
Product.PackagingLevel = dt.Rows[i]["PackingGroup"] == null ? "" : dt.Rows[i]["PackingGroup"].ToString();
Product.Packaging = dt.Rows[i]["PackSize"] == null ? "" : dt.Rows[i]["PackSize"].ToString();
Product.MeasureUnit = dt.Rows[i]["SKU"] == null ? "" : dt.Rows[i]["SKU"].ToString();
string count = dt.Rows[i]["TotalStockAmount"] == null ? "0" : dt.Rows[i]["TotalStockAmount"].ToString();
if (string.IsNullOrEmpty(count))
{
Product.SaleCounts = 0;
}
else
{
Product.SaleCounts = long.Parse(count);
}
//Formula --分子式 MolecularFormula
Product.MolecularFormula = dt.Rows[i]["Formula"] == null ? "" : dt.Rows[i]["Formula"].ToString();
//MolecularWeight --分子量 MolecularWeight
Product.MolecularWeight = dt.Rows[i]["MolecularWeight"] == null ? "" : dt.Rows[i]["MolecularWeight"].ToString();
//PhysicalForm --物理状态 Shape
Product.Shape = dt.Rows[i]["PhysicalForm"] == null ? "" : dt.Rows[i]["PhysicalForm"].ToString();
//Solubility --溶解性 SyntheticRoute
Product.SyntheticRoute = dt.Rows[i]["Solubility"] == null ? "" : dt.Rows[i]["Solubility"].ToString();
//Density --密度 Density
Product.Density = dt.Rows[i]["Density"] == null ? "" : dt.Rows[i]["Density"].ToString();
//MeltingPoint --熔点 FusingPoint
Product.FusingPoint = dt.Rows[i]["MeltingPoint"] == null ? "" : dt.Rows[i]["MeltingPoint"].ToString();
//BoilingPoint --沸点 BoilingPoint
Product.BoilingPoint = dt.Rows[i]["BoilingPoint"] == null ? "" : dt.Rows[i]["BoilingPoint"].ToString();
//FlashPoint --闪点 FlashPoint
Product.FlashPoint = dt.Rows[i]["FlashPoint"] == null ? "" : dt.Rows[i]["FlashPoint"].ToString();
//RefractiveIndex --折射率 RefractiveIndex
Product.RefractiveIndex = dt.Rows[i]["RefractiveIndex"] == null ? "" : dt.Rows[i]["RefractiveIndex"].ToString();
//StorageConditions --存储条件 StorageConditions
Product.StorageConditions = dt.Rows[i]["StorageConditions"] == null ? "" : dt.Rows[i]["StorageConditions"].ToString();
//EINECS --EINECS编码 RETCS
Product.RETCS = dt.Rows[i]["EINECS"] == null ? "" : dt.Rows[i]["EINECS"].ToString();
//HazardClass --危险等级 DangerLevel
Product.DangerLevel = dt.Rows[i]["HazardClass"] == null ? "" : dt.Rows[i]["HazardClass"].ToString();
//HSCode --海关编码 HSCODE
Product.HSCODE = dt.Rows[i]["HSCode"] == null ? "" : dt.Rows[i]["HSCode"].ToString();
//ShelfLife --保质期 NMRSpectrum
Product.NMRSpectrum = dt.Rows[i]["ShelfLife"] == null ? "" : dt.Rows[i]["ShelfLife"].ToString();
//MSDSFile --MSDS文件 MSDS
Product.MSDS = dt.Rows[i]["MSDSFile"] == null ? "" : dt.Rows[i]["MSDSFile"].ToString();
//CoAFile --质检文件 RefuseReason
Product.RefuseReason = dt.Rows[i]["CoAFile"] == null ? "" : dt.Rows[i]["CoAFile"].ToString();
/*cas#*/
if (dt.Rows[i]["CAS#"] != null && !string.IsNullOrWhiteSpace(dt.Rows[i]["CAS#"].ToString()))
{
Product.CASNo = dt.Rows[i]["CAS#"].ToString();
/*Pub_CID*/
int newId = 0;
/*根据cas#查询chemcloud_cas表数据*/
string pub_cid = DbHelperSQLCAS.ExecuteScalar("SELECT PUB_CID FROM ChemCloud_CAS with(nolock) WHERE CAS='" + Product.CASNo + "'");
if (!string.IsNullOrEmpty(pub_cid))
{
Product.Pub_CID = int.Parse(pub_cid);
}
else
{
/*cas库不存在，插入cas库*/
newId = DbHelperSQLCAS.GetMaxID("PUB_CID", "ChemCloud_CAS");
if (newId >= 200000000)
{
newId = newId + 1;
}
else
{
newId = 200000000;
}
Product.Pub_CID = newId;
string strsql = string.Format(@"
INSERT INTO dbo.ChemCloud_CAS(PUB_CID,CAS,CHINESE,Record_Title,CHINESE_ALIAS,Record_Description,Molecular_Formula,Molecular_Weight,
Density,Boiling_Point,Flash_Point,Vapor_Pressure,Exact_Mass,LogP,Physical_Description,Melting_Point,Solubility,Storage_Conditions,Safety_and_Hazards,HS_CODE,[2D_Structure])
VALUES('" + newId + "','" + Product.CASNo + "','" + Product.ProductName + "','" + Product.EProductName + "','"
+ Product.Alias + "','" + Product.Ealias + "','" + Product.MolecularFormula
+ "','" + Product.MolecularWeight + "','" + Product.Density + "','" + Product.BoilingPoint
+ "','" + Product.FlashPoint + "','" + Product.VapourPressure + "','" + Product.PASNo + "','"
+ Product.LogP + "','" + Product.Shape + "','" + Product.FusingPoint + "','"
+ Product.RefractiveIndex + "','" + Product.StorageConditions + "','" + Product.SafetyInstructions + "','" + Product.HSCODE + "','')");
int result = DbHelperSQLCAS.ExecuteSql(strsql);
}
}
else
{
/*Pub_CID*/
int newId = 0;
/*根据英文查询chemcloud_cas表数据*/
string pub_cid = DbHelperSQLCAS.ExecuteScalar("SELECT PUB_CID FROM ChemCloud_CAS with(nolock) WHERE Record_Title='" + Product.EProductName + "'");
if (!string.IsNullOrEmpty(pub_cid))
{
Product.Pub_CID = int.Parse(pub_cid);
}
else
{
/*cas库不存在，插入cas库*/
newId = DbHelperSQLCAS.GetMaxID("PUB_CID", "ChemCloud_CAS");
if (newId >= 200000000)
{
newId = newId + 1;
}
else
{
newId = 200000000;
}
Product.Pub_CID = newId;
string strsql = string.Format(@"
INSERT INTO dbo.ChemCloud_CAS(PUB_CID,CAS,CHINESE,Record_Title,CHINESE_ALIAS,Record_Description,Molecular_Formula,Molecular_Weight,
Density,Boiling_Point,Flash_Point,Vapor_Pressure,Exact_Mass,LogP,Physical_Description,Melting_Point,Solubility,Storage_Conditions,Safety_and_Hazards,HS_CODE,[2D_Structure])
VALUES('" + newId + "','" + Product.CASNo + "','" + Product.ProductName + "','" + Product.EProductName + "','"
+ Product.Alias + "','" + Product.Ealias + "','" + Product.MolecularFormula
+ "','" + Product.MolecularWeight + "','" + Product.Density + "','" + Product.BoilingPoint
+ "','" + Product.FlashPoint + "','" + Product.VapourPressure + "','" + Product.PASNo + "','"
+ Product.LogP + "','" + Product.Shape + "','" + Product.FusingPoint + "','"
+ Product.RefractiveIndex + "','" + Product.StorageConditions + "','" + Product.SafetyInstructions + "','" + Product.HSCODE + "','')");
int result = DbHelperSQLCAS.ExecuteSql(strsql);
}
}
Product.FreightTemplateId = 138;
Product.AddedDate = DateTime.Now; //产品日期
Product.Plat_Code = DateTime.Now.ToString("yyyyMMddHHmmssffff").ToString(); //平台货号
ServiceHelper.Create <IProductService>().AddProduct(Product); /*保存产品*/
/*查询已经成功添加的产品*/
ProductInfo productInfo =
ServiceHelper.Create <IProductService>().GetProductByCondition(
shopid, dt.Rows[i]["SupplierProductId"].ToString());
if (productInfo == null)
{
NullCount = NullCount + 1;
}
else
{
/*产品报价*/
try
{
/*RMB_CN*/
if (dt.Rows[i]["RMB_CN"] != null)
{
ProductSpec _ProductSpec = new ProductSpec();
_ProductSpec.ProductId = productInfo.Id;
_ProductSpec.Packaging = dt.Rows[i]["PackSize"] == null ? "" : dt.Rows[i]["PackSize"].ToString();
_ProductSpec.Purity = dt.Rows[i]["Purity"] == null ? "" : dt.Rows[i]["Purity"].ToString();
string price = dt.Rows[i]["RMB_CN"] == null ? "" : dt.Rows[i]["RMB_CN"].ToString();
if (string.IsNullOrEmpty(price))
{
_ProductSpec.Price = 0;
}
else
{
_ProductSpec.Price = Convert.ToDecimal(price);
}
_ProductSpec.SpecLevel = dt.Rows[i]["PackingGroup"] == null ? "" : dt.Rows[i]["PackingGroup"].ToString();
_ProductSpec.CoinType = 1;
ServiceHelper.Create <IProductService>().AddProductSpecs(_ProductSpec);
}
/*USD_USPrice*/
if (dt.Rows[i]["USD_USPrice"] != null)
{
ProductSpec _ProductSpec = new ProductSpec();
_ProductSpec.ProductId = productInfo.Id;
_ProductSpec.Packaging = dt.Rows[i]["PackSize"] == null ? "" : dt.Rows[i]["PackSize"].ToString();
_ProductSpec.Purity = dt.Rows[i]["Purity"] == null ? "" : dt.Rows[i]["Purity"].ToString();
string price = dt.Rows[i]["USD_USPrice"] == null ? "" : dt.Rows[i]["USD_USPrice"].ToString();
if (string.IsNullOrEmpty(price))
{
_ProductSpec.Price = 0;
}
else
{
_ProductSpec.Price = Convert.ToDecimal(price);
}
_ProductSpec.SpecLevel = dt.Rows[i]["PackingGroup"] == null ? "" : dt.Rows[i]["PackingGroup"].ToString();
_ProductSpec.CoinType = 2;
ServiceHelper.Create <IProductService>().AddProductSpecs(_ProductSpec);
}
}
catch
{
SubErrorCount = SubErrorCount + 1;
}
}
}
catch
{
ErrorCount = ErrorCount + 1;
}
if (ErrorCount == 0 && SubErrorCount == 0 && NullCount == 0 && RepeatCount == 0)
{
SuccessCount = SuccessCount + 1;
}
}
}
if (ErrorCount > 0 && SubErrorCount > 0)
{
return(Json(new
{
success = true,
message = 2,
ErrorCount = ErrorCount,
SubErrorCount = SubErrorCount,
SuccessCount = SuccessCount
}));//5商品已经导入," + "有" + ErrorCount + "条数据有误," + "有" + SubErrorCount + "条等级相关数据有错误。
}
else if (ErrorCount > 0)
{
return(Json(new
{
success = true,
message = 3,
ErrorCount = ErrorCount,
SuccessCount = SuccessCount
}));//"4商品已经导入," + "有" + ErrorCount + "条数据有误。"
}
else if (SubErrorCount > 0)
{
return(Json(new
{
success = true,
message = 4,
SubErrorCount = SubErrorCount,
SuccessCount = SuccessCount
}));//"3商品已经导入," + "有" + SubErrorCount + "条等级相关数据有错误。"
}
else if (RepeatCount > 0)
{
return(Json(new
{
success = true,
message = 1,
RepeatCount = RepeatCount,
SuccessCount = SuccessCount
}));
}
else if (NullCount > 0)
{
return(Json(new
{
success = true,
message = 7,
NullCount = NullCount,
SuccessCount = SuccessCount
}));
}
else
{
return(Json(new
{
success = true,
message = 5,
SuccessCount = SuccessCount
}));//"1所有数据成功导入商品！"
}
}
else
{
return(Json(new
{
success = false,
message = 6
}));//"2没有找到文件！"
}
}
