/// <summary>
/// 根据关键字查询相关度后，返回包含相关度的BusinessInfo
/// </summary>
/// <param name="keywordList">关键字List</param>
/// <returns>包含相关度的BusinessInfo</returns>
public List <Model.BusinessInfoEx> SearchKeyWordEx(List <string> keywordList)
{
List <Model.BusinessInfoEx> result = new List <BusinessInfoEx>();
StringBuilder sb = new StringBuilder();
sb.Append("select business.*,(m.degree/");
sb.Append((keywordList.Count).ToString());
sb.Append(")");
sb.Append(" as dependcy from business,(select v.BusinessID,v.BusinessTitle,(");
for (int i = 0; i < keywordList.Count; i++)
{
sb.Append("CASE WHEN LOCATE(");
sb.Append("'" + keywordList[i] + "'");
sb.Append(",v.BusinessTitle)>0");
sb.Append(" then 1 else 0 end");
sb.Append("+");
sb.Append("CASE WHEN LOCATE(");
sb.Append("'" + keywordList[i] + "'");
sb.Append(",v.Content)>0");
sb.Append(" then 1 else 0 end");
if (i != keywordList.Count - 1)
{
sb.Append("+");
}
}
sb.Append(") as degree");
sb.Append(
" from view_querykeyword as v) as m where business.BusinessID = m.BusinessID and degree>=2 Order by m.degree desc");
/*select business.*,(m.degree/2) as dependcy from business,(
* select v.BusinessID,v.BusinessTitle,
* (case WHEN LOCATE('地理信息',v.BusinessTitle)>0 then 1 ELSE 0 end +
* CASE WHEN LOCATE('软件',v.BusinessTitle)>0 then 1 else 0 END) as degree from view_querykeyword as v) as m
* where business.BusinessID = m.BusinessID order by dependcy desc*/
//用于参考
string SQL_SearchKeyWord = sb.ToString();
DataTable dt = DbHelperMySQL.GetDataTable(SQL_SearchKeyWord);
for (int i = 0; i < dt.Rows.Count; i++)
{
if (Convert.ToDouble(dt.Rows[i]["dependcy"]) > 0)
{
Model.BusinessInfoEx info = new Model.BusinessInfoEx();
info.GUID = dt.Rows[i]["BusinessID"].ToString();
info.Title = dt.Rows[i]["BusinessTitle"].ToString();
info.ComName = dt.Rows[i]["ReleaseCom"].ToString();
info.Location = dt.Rows[i]["ReleaseLocation"].ToString();
info.ReleaseTime = DateTime.Parse(dt.Rows[i]["ReleaseTime"].ToString());
info.DetileURL = dt.Rows[i]["DetileURL"].ToString();
info.Source = dt.Rows[i]["Source"].ToString();
info.Money = dt.Rows[i]["Money"].ToString();
info.Degree = Convert.ToDouble(dt.Rows[i]["dependcy"]);
result.Add(info);
}
}
return(result);
}
/// <summary>
/// 根据关键字查询内容并返回BusinessInfo
/// </summary>
/// <param name="keywordList"></param>
/// <returns></returns>
public List <Model.BusinessInfo> SearchKeyWord(List <string> keywordList)
{
if (keywordList.Count == 0)
{
//应该提取到配置项
keywordList = new List <string>()
{
"地理", "信息系统", "GIS", "软件", "地灾", "测绘", "国土", "资源"
}
}
;
StringBuilder sb = new StringBuilder();
sb.Append("Select * From business where business.BusinessID in(");
sb.Append("select BusinessID from View_QueryKeyWord where");
for (int i = 0; i < keywordList.Count; i++)
{
sb.Append(" View_QueryKeyWord.BusinessTitle");
sb.Append(" Like ");
sb.Append(string.Format("'%{0}%'", keywordList[i]));
sb.Append(" Or ");
}
for (int i = 0; i < keywordList.Count; i++)
{
sb.Append(" View_QueryKeyWord.Content");
sb.Append(" Like ");
sb.Append(string.Format("'%{0}%'", keywordList[i]));
if (i != (keywordList.Count - 1))
{
sb.Append(" Or ");
}
}
sb.Append(");");
string SQL_SearchKeyWord = sb.ToString();
List <Model.BusinessInfo> result = new List <Model.BusinessInfo>();
DataTable dt = DbHelperMySQL.GetDataTable(SQL_SearchKeyWord);
for (int i = 0; i < dt.Rows.Count; i++)
{
Model.BusinessInfo info = new Model.BusinessInfo();
info.GUID = dt.Rows[i]["BusinessID"].ToString();
info.Title = dt.Rows[i]["BusinessTitle"].ToString();
info.ComName = dt.Rows[i]["ReleaseCom"].ToString();
info.Location = dt.Rows[i]["ReleaseLocation"].ToString();
info.ReleaseTime = DateTime.Parse(dt.Rows[i]["ReleaseTime"].ToString());
info.DetileURL = dt.Rows[i]["DetileURL"].ToString();
info.Source = dt.Rows[i]["Source"].ToString();
result.Add(info);
}
return(result);
}
public void SetDoAction()
{
//根据客服分组一次执行
//type=0，客户部所属的客户；转为公客
string strSQLDo = string.Empty; //执行操作SQL
string strSQL = string.Format(@"select distinct BeSeats_id,BeSeats_uid,BeSeats from crm_customer where factory_id=1000");
DataTable dt = DbHelperMySQL.GetDataTable(strSQL);
string strFactoryId = "1000";
string isEmpId = ConfigurationManager.AppSettings["empId"].ToString().Trim();
switch (isEmpId.Trim())
{
case "-1": //不执行
break;
case "0": //执行全部
foreach (DataRow item in dt.Rows)
{
//客服Id
string strEmpId = item["BeSeats_id"].ToString();
strSQLDo = string.Format(@"UPDATE crm_customer c INNER JOIN
(select CustomerId from crm_set_grabcustomer_flow as f where f.factory_id={0} and f.type=0 and BeSeats_empId={1} ) s ON s.CustomerId=c.id and c.factory_id ={0}
set c.privatecustomer_Id=51,c.privatecustomer='公客';", strFactoryId, strEmpId);
int rows = DbHelperMySQL.ExecuteSql(strSQLDo.ToString());
}
break;
default: //执行当前员工
foreach (DataRow item in dt.Rows)
{
if (item["BeSeats_id"].ToString().Trim() == isEmpId)
{
//客服Id
string strEmpId = item["BeSeats_id"].ToString();
strSQLDo = string.Format(@"UPDATE crm_customer c INNER JOIN
(select CustomerId from crm_set_grabcustomer_flow as f where f.factory_id={0} and f.type=0 and BeSeats_empId={1} ) s ON s.CustomerId=c.id and c.factory_id ={0}
set c.privatecustomer_Id=51,c.privatecustomer='公客';", strFactoryId, strEmpId);
int rows = DbHelperMySQL.ExecuteSql(strSQLDo.ToString());
break;
}
}
break;
}
}