/// <summary>
/// 删除表单
/// </summary>
/// <param name="formCode"></param>
public void DeleteForm()
{
if (!this.IsOrderForm())
{
throw new UIValidateException("删除表单错误：用户【{0}】对【{1}】无操作权限!", this.curUser.CurrentUserName, this.DbForm.Name);
}
using (var db = DbAccess.DbCRMContext.Create())
{
var from = (from a in db.T_QCRM_Form
join b in db.T_QCRM_Tables on a.MainTable equals b.Code
join c in db.T_QCRM_Fields on b.Code equals c.TableCode
where c.PK == 1 && a.Code == this.FormCode
select new { a.MainTable, FieldCode = c.Code, PkName = c.TableCode + "_" + c.Code, c.Name }).ToList().FirstOrDefault();
var pkValue = this.httpContext.Request[from.PkName].SafeConvert().ToStr();
var where = string.Format("{0} = '{1}'", from.FieldCode, pkValue);
//安全参数
if (SafeDictionary != null && SafeDictionary.Count() > 0)
{
foreach (var dic in SafeDictionary)
{
where += string.Format(" and {0} = '{1}'", dic.Key, dic.Value);
}
}
string sql = string.Format("delete from {0} where {1} ", from.MainTable, where);
ADO.ADO_Helper.Create().ExecSQL(sql);
}
}
/// <summary>
/// 保存表单
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="resultPkName">新增保存，需要返回的自增ID或唯一ID的字段名称（目前不支持自增ID的返回）</param>
/// <returns></returns>
public T SaveForm <T>(string resultPkName)
{
T result = default(T);
if (!this.IsOrderForm())
{
throw new UIValidateException("保存表单错误：用户【{0}】对【{1}】无操作权限!", this.curUser.CurrentUserName, this.DbForm.Name);
}
using (var db = QWF.CRM.DbAccess.DbCRMContext.Create())
{
var inputs = (from a in db.T_QCRM_FormInput.AsNoTracking()
join b in db.T_QCRM_Fields.AsNoTracking() on new { TableCode = a.TableCode, FieldCode = a.FieldCode } equals new { TableCode = b.TableCode, FieldCode = b.Code }
join c in db.T_QCRM_Tables.AsNoTracking() on b.TableCode equals c.Code
where a.FormCode == this.FormCode && b.Deleted == 0 && c.Deleted == 0
select new
{
a.FormCode,
a.TableCode,
a.FieldCode,
a.InputType,
a.InputName,
a.DefaultValue,
a.DefaultValueType,
b.FieldType,
b.IsNotNull,
b.OnlyKey,
b.PK,
a.Name,
c.DeleteType,
c.DeleteField,
c.DeleteFlag
}).ToList();
#region 获取_并验证_参数
Dictionary <string, string> fields = new Dictionary <string, string>();
foreach (var input in inputs)
{
var value = input.InputType == "system" ? GetSystemInputValue(input.DefaultValueType, input.DefaultValue, this.DbForm.ActionType) : httpContext.Request[input.InputName].SafeConvert().ToStr();
if (input.FieldType == "decimal" || input.FieldType == "int")
{
fields.Add(input.FieldCode, value);
}
else
{
fields.Add(input.FieldCode, "'" + value + "'");
}
//必填
if (input.IsNotNull == 1 && value.StrValidatorHelper().StrIsNullOrEmpty())
{
throw new UIValidateException("【{0}】为必填的数据，请重新填写或选择。", input.Name);
}
//唯一验证,值不为空并不是PK键
if (input.OnlyKey == 1 && !value.StrValidatorHelper().StrIsNullOrEmpty() && input.PK != 1)
{
var checkSQL = string.Empty;
if (this.DbForm.ActionType == "create")
{
//新增
checkSQL = string.Format("SELECT COUNT(*) FROM {0} WHERE {1} = {2} ",
input.TableCode, input.FieldCode, fields[input.FieldCode]);
}
else if (this.DbForm.ActionType == "update")
{
//找到主键
var pk = db.T_QCRM_Fields.Where(w => w.TableCode == input.TableCode && w.PK == 1).FirstOrDefault();
if (pk == null)
{
throw new UIValidateException("表【{0}】没有配置主键。", input.TableCode);
}
if (pkValue.StrValidatorHelper().StrIsNullOrEmpty())
{
throw new UIValidateException("主键的值为空，请检查参数。");
}
string pkWhere = string.Empty;
if (pkValue.StrValidatorHelper().IsNumeric())
{
pkWhere = string.Format(" and {0}!= {1}", pk.Code, pkValue);
}
else
{
pkWhere = string.Format(" and {0}!= '{1}'", pk.Code, pkValue);
}
checkSQL = string.Format("SELECT COUNT(*) FROM {0} WHERE {1} = {2} {3}",
input.TableCode, input.FieldCode, fields[input.FieldCode], pkWhere);
}
if (input.DeleteType == 1) //逻辑删除
{
var deleteFlag = input.DeleteFlag;
if (!deleteFlag.StrValidatorHelper().IsNumeric())
{
deleteFlag = "'" + deleteFlag + "'";
}
checkSQL += string.Format(" and {0} = {1}", input.DeleteField, deleteFlag);
}
var iCount = db.Database.SqlQuery <int>(checkSQL).First();
if (iCount > 0)
{
throw new UIValidateException("数据唯一性检查失败,内容有重复，<br/> 数据项:【{0}】</br> 内容: 【{1}】。", input.Name, value);
}
}
}
#endregion
string sql = string.Empty;
if (this.DbForm.ActionType == "create")
{
string param = string.Empty;
string values = string.Empty;
foreach (var field in fields)
{
if (field.Value.Replace("'", "").Length > 0)
{
if (param.Length > 0)
{
param += ",";
values += ",";
}
param += field.Key;
values += field.Value;
}
}
sql = string.Format("insert into {0} ({1}) values ({2})", this.DbForm.MainTable, param, values);
if (resultPkName != null)
{
result = (T)(object)fields[resultPkName];
}
}
else if (this.DbForm.ActionType == "update")
{
var pk = (from a in db.T_QCRM_Form
join b in db.T_QCRM_Tables on a.MainTable equals b.Code
join c in db.T_QCRM_Fields on b.Code equals c.TableCode
where c.PK == 1 && b.Deleted == 0 && c.Deleted == 0 && a.Code == this.FormCode
select new { c.Code, c.Name }).ToList().FirstOrDefault();
// 不从客户端出入PKname，安全考虑
if (pk == null)
{
throw new UIValidateException("没有配置主键!");
}
var pkName = pk.Code;
//update
string updateParams = string.Empty;
foreach (var field in fields)
{
//不能更新主键
if (field.Key != pk.Code)
{
if (updateParams.Length > 0)
{
updateParams += ",";
}
updateParams += field.Key + "=" + field.Value;
}
}
var where = string.Format("{0} = '{1}'", pkName, pkValue.Trim());
//安全参数
if (SafeDictionary != null && SafeDictionary.Count() > 0)
{
foreach (var dic in SafeDictionary)
{
where += string.Format(" and {0} = '{1}'", dic.Key, dic.Value);
}
}
sql = string.Format("update {0} set {1} where {2}", this.DbForm.MainTable, updateParams, where);
}
//执行SQL;
QWF.CRM.ADO.ADO_Helper.Create().ExecSQL(sql);
return(result);
}
}
