/// <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); } }