public static async Task <int> InsertUserFilterRuleJobDetailAsync(UserFilterRuleJobDetail detail)
{
string sql = @"
INSERT Configuration..tbl_UserFilterRuleJobDetail
( JobId ,
TableName ,
SearchKey ,
SearchValue ,
JoinType ,
BasicAttribute ,
SecondAttribute ,
CompareType ,
CreateDateTime ,
LastUpdateDateTime,
BatchID
)
VALUES ( @JobId , -- JobId - int
@TableName , -- TableName - nvarchar(100)
@SearchKey , -- SearchKey - nvarchar(200)
@SearchValue , -- SearchValue - nvarchar(500)
@JoinType , -- JoinType - nvarchar(100)
@BasicAttribute , -- BasicAttribute - nvarchar(100)
@SecondAttribute , -- SecondAttribute - nvarchar(100)
@CompareType , -- CompareType - nvarchar(100)
GETDATE() , -- CreateDateTime - datetime
GETDATE() , -- LastUpdateDateTime - datetime
@BatchID
);SELECT @@IDENTITY; ";
using (var dbhelper = DbHelper.CreateDbHelper(false))
{
using (var cmd = new SqlCommand(sql))
{
cmd.CommandType = CommandType.Text;
var props = detail.GetType().GetProperties();
foreach (PropertyInfo prop in props)
{
object value = prop.GetValue(detail);
if (prop.Name.Equals("PKID", StringComparison.OrdinalIgnoreCase) ||
prop.PropertyType == typeof(DateTime))
{
continue;
}
if (prop.PropertyType.IsEnum)
{
cmd.Parameters.Add(new SqlParameter(string.Concat("@", prop.Name), value.ToString()));
}
else
{
cmd.Parameters.Add(new SqlParameter(string.Concat("@", prop.Name), value));
}
}
var result = await dbhelper.ExecuteScalarAsync(cmd);
return(Convert.IsDBNull(result) ? 0 : Convert.ToInt32(result));
}
}
}
public static async Task <bool> InsertUserFilterRuleDetailFromWebAsync(string firstCategory,
string secondCategory, JoinType jointype, string tableName, NameValueCollection formdata, int jobid)
{
var basedetail = new UserFilterRuleJobDetail()
{
TableName = tableName,
BasicAttribute = firstCategory,
SecondAttribute = secondCategory,
JoinType = jointype,
JobId = jobid,
BatchID = Guid.NewGuid().ToString()
};
if (formdata != null && formdata.AllKeys.Any())
{
List <string> skipkeys = new List <string>();
//选日期时间或据今日
if (formdata.AllKeys.Any(x => x.EndsWith("_selecttype")))
{
string key = formdata.AllKeys.First(x => x.EndsWith("_selecttype"));
string selecttype = formdata[key];
string colname = key.Replace("_selecttype", "");
skipkeys.Add(key);
if (string.Equals(selecttype, "date"))
{
skipkeys.Add(colname + "_from_date");
}
else
{
skipkeys.Add(colname + "_start_date");
skipkeys.Add(colname + "_end_date");
}
}
//订单统计字段
if (formdata.AllKeys.Any(x => string.Equals("countcoltype", x)))
{
skipkeys.Add("countcoltype");
skipkeys.Add("countcoltype_start");
skipkeys.Add("countcoltype_end");
var detail = basedetail.DeepCopy();
detail.SearchKey = formdata["countcoltype"];
detail.SearchValue = formdata["countcoltype_start"];
detail.CompareType = CompareType.GreaterOrEqual;
await Repository.UserFilterRule.InsertUserFilterRuleJobDetailAsync(detail);
detail.SearchValue = formdata["countcoltype_end"];
detail.CompareType = CompareType.LessOrEqual;
await Repository.UserFilterRule.InsertUserFilterRuleJobDetailAsync(detail);
}
if (formdata.AllKeys.Any(x => x.EndsWith("_start_time")) || formdata.AllKeys.Any(x => x.EndsWith("_end_time")))
{
skipkeys.AddRange(formdata.AllKeys.Where(x => x.EndsWith("_start_time")));
skipkeys.AddRange(formdata.AllKeys.Where(x => x.EndsWith("_end_time")));
}
foreach (var datakey in formdata.AllKeys)
{
if (skipkeys.Contains(datakey))
{
continue;
}
var datevalue = formdata[datakey];
var detail = basedetail.DeepCopy();
if (datakey.EndsWith("start_date") || datakey.EndsWith("end_date"))
{
detail.CompareType = datakey.EndsWith("start_date")
? CompareType.GreaterOrEqual
: CompareType.LessOrEqual;
var colname = datakey.Replace("_start_date", "").Replace("_end_date", "");
if (formdata.AllKeys.Any(x => x == colname + "_start_time"))
{
skipkeys.Add(colname + "_start_time");
datevalue = datevalue + " " + formdata[colname + "_start_time"];
}
else if (formdata.AllKeys.Any(x => x == colname + "_end_time"))
{
skipkeys.Add(colname + "_end_time");
datevalue = datevalue + " " + formdata[colname + "_end_time"];
}
detail.SearchKey = colname;
detail.SearchValue = datevalue;
}
else if (datakey.EndsWith("_from_date"))
{
var colname = datakey.Replace("_from_date", "");
detail.SearchKey = colname;
detail.SearchValue = datevalue;
detail.CompareType = CompareType.DateFromToday;
}
else if (datakey.EndsWith("_start") || datakey.EndsWith("_end"))
{
detail.CompareType = datakey.EndsWith("_start")
? CompareType.GreaterOrEqual
: CompareType.LessOrEqual;
detail.SearchKey = datakey.Replace("_start", "").Replace("_end", "");
detail.SearchValue = formdata[datakey];
}
else
{
detail.SearchKey = datakey;
detail.SearchValue = formdata[datakey];
detail.CompareType = CompareType.Equal;
}
var result = await Repository.UserFilterRule.InsertUserFilterRuleJobDetailAsync(detail);
}
}
return(true);
}
