public ActionResult SelectFields(int categoryId, string mainTable)
{
SelectFieldsModel model = new SelectFieldsModel { CategoryId = categoryId, MainTable = mainTable };
DevFieldInfo field = new DevFieldInfo { TableName = mainTable };
model.Fields = DevBusiness.SearchFields(field, categoryId, false);
return View(model);
}
public static void AddFieldToCategory(DevFieldInfo fieldInfo, int categoryId)
{
DevFieldCategory fc = new DevFieldCategory
{
CategoryId = categoryId,
FieldId = fieldInfo.FieldId,
ControlIndex = fieldInfo.ControlIndex
};
using (var db = DbHelper.Create())
{
db.InsertEntity(fc);
}
}
public static List<DevFieldInfo> SearchFields(DevFieldInfo model, int excludedCategoryId = 0, bool like = true)
{
StringBuilder sb = new StringBuilder(@"SELECT top 200 * FROM DevFieldInfo WHERE Deleted<>1 ");
List<string> filters = new List<string>();
if (!string.IsNullOrEmpty(model.TableName))
{
filters.Add("TableName like @TableName");
if (like)
model.TableName = "%" + model.TableName + "%";
}
if (!string.IsNullOrEmpty(model.FieldName))
{
filters.Add("FieldName like @FieldName");
if (like)
model.FieldName = "%" + model.FieldName + "%";
}
if (!string.IsNullOrEmpty(model.FieldLabel))
{
filters.Add("FieldLabel like @FieldLabel");
if (like)
model.FieldLabel = "%" + model.FieldLabel + "%";
}
DynamicParameters dps = new DynamicParameters();
dps.AddDynamicParams(model);
if (excludedCategoryId > 0)
{
filters.Add("FieldId NOT IN (SELECT FieldId FROM DevFieldCategory WHERE CategoryId=@ExCategoryId AND Deleted<>1) ");
dps.Add("ExCategoryId", excludedCategoryId);
}
if (filters.Count > 0)
{
sb.Append(" AND ");
sb.Append(string.Join(" AND ", filters));
}
using (var db = DbHelper.Create())
{
return db.Query<DevFieldInfo>(sb.ToString(), dps).ToList();
}
}
public static DevFieldInfo SaveCategoryField(int categoryId, DevFieldInfo field)
{
if (field.FieldId > 0)
{
string sql = @"UPDATE DevFieldInfo SET FieldLabel=@FieldLabel, EntityProperty=@EntityProperty, ControlTypeId=@ControlTypeId WHERE FieldId=@FieldId ;
UPDATE DevFieldCategory SET ControlIndex=@ControlIndex WHERE FieldId=@FieldId AND CategoryId=@CategoryId";
using (var db = DbHelper.Create())
{
db.Execute(sql, new
{
CategoryId = categoryId,
FieldId = field.FieldId,
EntityProperty = field.EntityProperty,
ControlTypeId = field.ControlTypeId,
FieldLabel = field.FieldLabel,
ControlIndex = field.ControlIndex
});
}
}
else
{
string sql = @"SELECT * FROM DevFieldInfo
WHERE EntityProperty=@EntityProperty AND FieldLabel=@FieldLabel";
using (var db = DbHelper.Create())
{
var fld = db.Query<DevFieldInfo>(sql, field).FirstOrDefault();
if (fld != null)
{
field = fld;
}
else
{
db.InsertEntity(field);
}
AddFieldToCategory(field, categoryId);
}
}
return field;
}
public JsonResult SearchAllFields(int excludedCategoryId, bool fuzzy, DevFieldInfo field)
{
var fields = DevBusiness.SearchFields(field , excludedCategoryId, fuzzy);
return Json(fields);
}
public JsonResult SaveCategoryField(int categoryId, DevFieldInfo field)
{
DevBusiness.SaveCategoryField(categoryId, field);
return Json(field);
}
public void AddFieldToCategory(int categoryId, DevFieldInfo field)
{
DevBusiness.AddFieldToCategory(field, categoryId);
}