/// <summary>
/// 批量删除
/// </summary>
/// <param name="delList"></param>
/// <param name="configType"></param>
/// <returns></returns>
public bool DeleteSearchWord(List <SearchWordConvertMapDb> delList, SearchWordConfigType configType)
{
try
{
bool Action(SqlConnection connection) => DalSearchWordConvert.DeleteSearchWord(connection, delList, configType);
return(_dbManager.Execute(Action));
}
catch (Exception e)
{
Monitor.ExceptionMonitor.AddNewMonitor("删除搜索关键词配置异常", e, "删除异常", MonitorLevel.Critial);
return(false);
}
}
/// <summary>
/// 获取数据库列表
/// </summary>
/// <param name="configType"></param>
/// <returns></returns>
public List <SearchWordConvertMapDb> GetAllSearchWord(SearchWordConfigType configType)
{
try
{
List <SearchWordConvertMapDb> Action(SqlConnection connection) => DalSearchWordConvert.GetAllSearchWord(connection, configType);
return(_dbManagerReadOnly.Execute(Action));
}
catch (Exception e)
{
Monitor.ExceptionMonitor.AddNewMonitor("检索搜索关键词信息异常", e, "检索异常", MonitorLevel.Critial);
return(new List <SearchWordConvertMapDb>());
}
}
public static bool BulkSaveSearchWordInfo(SqlConnection conn, DataTable tb, SearchWordConfigType configType)
{
using (var bulk = new SqlBulkCopy(conn))
{
bulk.BatchSize = tb.Rows.Count;
if (configType == SearchWordConfigType.Config)
{
bulk.DestinationTableName = "tbl_SearchWordConvertMap";
bulk.ColumnMappings.Add("TargetWord", "TargetWord");
bulk.ColumnMappings.Add("SourceWord", "SourceWord");
bulk.ColumnMappings.Add("CreateTime", "CreateTime");
bulk.ColumnMappings.Add("Tag", "Tag");
bulk.ColumnMappings.Add("UpdateBy", "UpdateBy");
bulk.ColumnMappings.Add("UpdateTime", "UpdateTime");
bulk.WriteToServer(tb);
}
else if (configType == SearchWordConfigType.VehicleType)
{
bulk.DestinationTableName = "SearchWordConvertMap_Vehicle";
bulk.ColumnMappings.Add("TargetWord", "TargetWord");
bulk.ColumnMappings.Add("SourceWord", "SourceWord");
bulk.ColumnMappings.Add("CreateTime", "CreateDateTime");
bulk.ColumnMappings.Add("Type", "Type");
bulk.ColumnMappings.Add("VehicleID", "VehicleID");
bulk.ColumnMappings.Add("Sort", "Sort");
bulk.ColumnMappings.Add("TireSize", "TireSize");
bulk.ColumnMappings.Add("SpecialTireSize", "SpecialTireSize");
bulk.ColumnMappings.Add("VehicleName", "VehicleName");
bulk.WriteToServer(tb);
}
else if (configType == SearchWordConfigType.NewWord)
{
bulk.DestinationTableName = "SearchNewWordConvertMap";
bulk.ColumnMappings.Add("TargetWord", "KeyWord");
bulk.ColumnMappings.Add("CreateTime", "CreateDateTime");
bulk.WriteToServer(tb);
}
}
return(true);
}
public SearchWordMgr Create(SearchWordConfigType configType)
{
switch (configType)
{
// 同义词配置
case SearchWordConfigType.Config:
return(new SearchWordConfigHandler());
// 新词配置
case SearchWordConfigType.NewWord:
return(new SearchWordNewWordHandler());
// 车型配置
case SearchWordConfigType.VehicleType:
return(new SearchWordVehicleTypeHandler());
default:
return(null);
}
}
public static bool UpdateSearchWord(SqlConnection conn, List <SearchWordConvertMapDb> list,
SearchWordConfigType configType)
{
string tablename = "";
if (configType == SearchWordConfigType.Config)
{
tablename = "Tuhu_productcatalog.dbo.tbl_SearchWordConvertMap";
}
else if (configType == SearchWordConfigType.VehicleType)
{
tablename = "Tuhu_productcatalog.dbo.SearchWordConvertMap_Vehicle";
}
else if (configType == SearchWordConfigType.NewWord)
{
tablename = "Tuhu_productcatalog.dbo.SearchNewWordConvertMap";
}
if (!string.IsNullOrEmpty(tablename))
{
var sqlParams = new List <SqlParameter>();
var strSql = new StringBuilder();
foreach (var item in list)
{
strSql.AppendFormat($@"IF EXISTS ( SELECT COUNT(0)
FROM {tablename} WITH ( NOLOCK )
WHERE PKID = {item.PKID}
OR (UpdateBy = '{item.UpdateBy}' OR ISNULL(UpdateBy, '') = '') )
UPDATE c
SET c.UpdateBy = '{item.UpdateBy}'
FROM {tablename} c
WHERE PKID = {item.PKID};");
}
if (strSql.Length > 0)
{
SqlHelper.ExecuteNonQuery(conn, CommandType.Text, strSql.ToString(), sqlParams.ToArray());
}
return(true);
}
return(false);
}
public static bool DeleteSearchWord(SqlConnection conn, List <SearchWordConvertMapDb> delList, SearchWordConfigType configType)
{
string tablename = "";
if (configType == SearchWordConfigType.Config)
{
tablename = "tbl_SearchWordConvertMap";
}
else if (configType == SearchWordConfigType.VehicleType)
{
tablename = "SearchWordConvertMap_Vehicle";
}
else if (configType == SearchWordConfigType.NewWord)
{
tablename = "SearchNewWordConvertMap";
}
if (!string.IsNullOrEmpty(tablename))
{
var sqlParams = new List <SqlParameter>();
var strSql = new StringBuilder();
for (var i = 0; i < delList.Count; i++)
{
strSql.AppendFormat($" DELETE FROM {tablename} Where PKID = {delList[i].PKID} ; ");
}
if (strSql.Length > 0)
{
SqlHelper.ExecuteNonQuery(conn, CommandType.Text, strSql.ToString(), sqlParams.ToArray());
}
return(true);
}
return(false);
}
public static List <SearchWordConvertMapDb> GetAllSearchWord(SqlConnection conn, SearchWordConfigType configType)
{
string sql = "";
if (configType == SearchWordConfigType.Config)
{
sql = @"
SELECT ss.PKID,
ss.TargetWord,
ss.SourceWord,
ss.CreateTime,
ss.Tag,
ss.UpdateBy
FROM Tuhu_productcatalog.dbo.tbl_SearchWordConvertMap AS ss WITH (NOLOCK)
ORDER BY UpdateTime DESC";
}
else if (configType == SearchWordConfigType.VehicleType)
{
sql = @"SELECT ss.PKID ,
ss.TargetWord ,
ss.SourceWord ,
ss.CreateDateTime AS CreateTime ,
0 AS Tag ,
ss.VehicleID ,
ss.Sort ,
ss.TireSize ,
ss.SpecialTireSize,
ss.VehicleName
FROM Tuhu_productcatalog..SearchWordConvertMap_Vehicle AS ss WITH ( NOLOCK ); ";
}
else if (configType == SearchWordConfigType.NewWord)
{
sql = @"SELECT PKID ,
KeyWord AS SourceWord ,
'' AS TargetWord ,
CreateDateTime AS CreateTime ,
0 AS Tag
FROM Tuhu_productcatalog..SearchNewWordConvertMap WITH ( NOLOCK ); ";
}
if (!string.IsNullOrEmpty(sql))
{
var resultList = SqlHelper.ExecuteDataTable(conn, CommandType.Text, sql).ConvertTo <SearchWordConvertMapDb>().ToList();
return(resultList);
}
return(new List <SearchWordConvertMapDb>());
}
public bool UpdateSearchWord(List <SearchWordConvertMapDb> list, SearchWordConfigType configType)
{
return(_searchHandler.UpdateSearchWord(list, configType));
}
public bool ImportExcelInfoToDb(DataTable tb, SearchWordConfigType configType)
{
return(_searchHandler.ImportExcelInfoToDb(tb, configType));
}
public bool DeleteSearchWord(List <SearchWordConvertMapDb> deleteList, SearchWordConfigType configType)
{
return(_searchHandler.DeleteSearchWord(deleteList, configType));
}
public List <SearchWordConvertMapDb> GetAllSearchWord(SearchWordConfigType configType)
{
return(_searchHandler.GetAllSearchWord(configType));
}
/// <summary>
/// 导入Excel数据到数据库
/// </summary>
/// <param name="tb"></param>
/// <param name="configType"></param>
/// <returns></returns>
public bool ImportExcelInfoToDb(DataTable tb, SearchWordConfigType configType)
{
if (tb == null || tb.Rows.Count <= 0)
{
return(true);
}
var isSuccess = true;
try
{
var dt = new DataTable();
dt.Columns.Add("UpdateBy", typeof(string));
dt.Columns.Add("TargetWord", typeof(string));
dt.Columns.Add("SourceWord", typeof(string));
dt.Columns.Add("CreateTime", typeof(DateTime));
dt.Columns.Add("UpdateTime", typeof(DateTime));
switch (configType)
{
case SearchWordConfigType.Config:
dt.Columns.Add("Tag", typeof(int));
break;
case SearchWordConfigType.VehicleType:
dt.Columns.Add("Type", typeof(string));
dt.Columns.Add("VehicleID", typeof(string));
dt.Columns.Add("Sort", typeof(int));
dt.Columns.Add("TireSize", typeof(string));
dt.Columns.Add("SpecialTireSize", typeof(string));
dt.Columns.Add("VehicleName", typeof(string));
break;
}
for (var i = 0; i < tb.Rows.Count; i++)
{
var targetWord = tb.Rows[i][0].ToString();
var sourceWord = tb.Rows[i][1].ToString();
if (string.IsNullOrEmpty(targetWord) || string.IsNullOrEmpty(sourceWord))
{
continue;
}
var dr = dt.NewRow();
dr["TargetWord"] = targetWord;
dr["SourceWord"] = sourceWord;
dr["CreateTime"] = DateTime.Now;
if (configType == SearchWordConfigType.Config)
{
dr["Tag"] = tb.Rows[i][3];
dr["UpdateBy"] = tb.Rows[i][4];
dr["UpdateTime"] = DateTime.Now;
}
if (configType == SearchWordConfigType.VehicleType)
{
int sort;
int.TryParse(tb.Rows[i][4].ToString(), out sort);
dr["Type"] = "二级车型";
dr["VehicleID"] = tb.Rows[i][3];
dr["Sort"] = sort == 0 ? int.MaxValue : sort;
dr["TireSize"] = tb.Rows[i][5];
dr["SpecialTireSize"] = tb.Rows[i][6];
dr["VehicleName"] = tb.Rows[i][7];
}
dt.Rows.Add(dr);
}
bool Action(SqlConnection connection) => DalSearchWordConvert.BulkSaveSearchWordInfo(connection, dt, configType);
_dbManager.Execute(Action);
}
catch (Exception e)
{
isSuccess = false;
Monitor.ExceptionMonitor.AddNewMonitor("导入Excel配置信息异常", e, "上传异常", MonitorLevel.Critial);
}
return(isSuccess);
}
