/// <summary>
/// 获取产品服务的过滤条件
/// </summary>
/// <param name="item"></param>
/// <returns></returns>
private string GetProductServerTimeSQL(DbAccess.T_QCRM_QueryWhere item)
{
var subWhere = new StringBuilder("select distinct CustomerCode from T_QCRM_CustomerProduct where ");
var fieldCode = Utils.ConfigUtils.Instance.GetServerTimeList().Where(w => w.VirtualFieldCode == item.FieldCode).FirstOrDefault().FileCode;
string dateSql = GetDateExpression(fieldCode, item.DateType.Value, item.DateNum.Value, item.StaticDateValue);
subWhere.Append(dateSql);
string sql = string.Format("TB_Customer_CustomerCode in ({0})", subWhere.ToString());
return(sql);
}
private string GetWhereExpression(DbAccess.T_QCRM_QueryWhere item)
{
var whereSQL = string.Empty;
#region 计算值
string fieldValue = string.Empty;
if (item.FieldQueryType == FieldQueryType.QueryType_02) //用户
{
#region 用户 1 自己 ,2指定编码
if (item.UserCodeType.Value == 1)
{
fieldValue = curUser.CurrentUserCode;
}
else if (item.UserCodeType.Value == 2)
{
fieldValue = item.UserCode;
}
#endregion
}
else
{
fieldValue = item.FieldValue;
}
#endregion
if (item.Expression == "in" || item.Expression == "notin")
{
string isNot = item.Expression == "notin" ? "not" : "";
if (item.FieldValue.IndexOf(",") > 0) // 多值情况
{
string sql = string.Empty;
item.FieldValue.StringHelper().SplitToList().ForEach(val =>
{
if (sql.Length > 0)
{
sql += " or ";
}
sql += string.Format("{0} {1} like '%{2}%'", item.FieldCode, isNot, val);
});
whereSQL += string.Format("({0})", sql);
}
else
{
whereSQL += string.Format(" {0} {1} like '%{2}%'", item.FieldCode, isNot, fieldValue);
}
}
else
{
//常规表达式
if (item.FieldValueType == "text")
{
fieldValue = string.Format("'{0}'", fieldValue);
}
whereSQL += string.Format(" {0} {1} {2}", item.FieldCode, item.Expression, fieldValue);
}
return(whereSQL);
}
public ActionResult SaveWhereSetting()
{
var result = QWF.Framework.Web.ResultWebData.Default();
var curUser = QWF.Framework.Web.UserContext.GetCurrentInfo();
var fieldQueryType = QWF.CRM.Utils.FieldQueryType.QueryType_00;// 字段查询类型;
var whereId = this.Request["whereId"].SafeConvert().ToInt32(0);
var categoryId = this.Request["categoryId"].SafeConvert().ToInt32(0);
var fieldCode = Request["fieldCode"].SafeConvert().ToStr();
var expression = Request["expression"].SafeConvert().ToStr();
var fieldValue = this.Request["fieldValue"].SafeConvert().ToStr();
var fieldType = this.Request["fieldType"].SafeConvert().ToStr();
var staticDateValue = Request["staticDate"].SafeConvert().ToStr();
var dateNum = Request["dateNum"].SafeConvert().ToInt32(0);
var dateType = Request["dateType"].SafeConvert().ToInt32(0);
var userCode = Request["sysUserCode"].SafeConvert().ToStr();
var isUserField = Request["isUserField"].SafeConvert().ToBool();
var userCodeType = 0; //默认自己；
if (categoryId == 0)
{
throw new UIValidateException("查询分类ID不正确,ID=0。");
}
if (fieldCode.Length == 0)
{
throw new UIValidateException("请选择字段。");
}
if (expression.Length == 0 && fieldType != "datetime" && !isUserField)
{
throw new UIValidateException("请选择表达式。");
}
if (fieldType == "datetime")
{
if (dateType < 10 && staticDateValue.Length == 0)
{
throw new UIValidateException("请填写固定日期。");
}
if (dateType >= 50 && dateNum == 0)
{
throw new UIValidateException("请填写天数。");
}
fieldQueryType = QWF.CRM.Utils.FieldQueryType.QueryType_01;
}
else if (isUserField)
{
if (userCode.Length == 0)
{
throw new UIValidateException("请选择系统用户。");
}
fieldQueryType = QWF.CRM.Utils.FieldQueryType.QueryType_02;
userCodeType = (userCode == "myself") ? 1 : 2;
expression = "=";
}
else
{
if (fieldValue.Length == 0)
{
throw new UIValidateException("请填写值。");
}
}
DbAccess.T_QCRM_QueryWhere dbModel = null;
using (var db = DbAccess.DbCRMContext.Create())
{
if (whereId == 0)
{
dbModel = new DbAccess.T_QCRM_QueryWhere();
}
else
{
//编辑
dbModel = db.T_QCRM_QueryWhere.Where(w => w.Id == whereId).FirstOrDefault();
if (dbModel == null)
{
throw new UIValidateException("ID=【{0}】的记录不存在。", whereId);
}
dbModel.UpdateTime = DateTime.Now;
dbModel.UpdateUser = curUser.CurrentUserCode;
}
//公共参数
dbModel.QueryCategoryId = categoryId;
dbModel.FieldCode = fieldCode;
dbModel.Expression = expression;
dbModel.FieldValue = fieldValue;
dbModel.FieldValueType = fieldType;
dbModel.StaticDateValue = staticDateValue;
dbModel.DateNum = dateNum;
dbModel.DateType = dateType;
dbModel.CreateTime = DateTime.Now;
dbModel.CreateUser = curUser.CurrentUserCode;
dbModel.FieldQueryType = fieldQueryType;
dbModel.UserCodeType = userCodeType;
dbModel.UserCode = userCode;
if (whereId == 0)
{
//新增
db.T_QCRM_QueryWhere.Add(dbModel);
}
db.SaveChanges();
}
return(this.Content(Newtonsoft.Json.JsonConvert.SerializeObject(result)));
}
