public ResponseObjectPackage<List<ControlQueryMappingModel>> GetControlQueryMappingByFormID(int formID)
{
RequestPackage request = new RequestPackage() { requestID = formID };
List<ControlQueryMappingModel> mappings = new DBUtils().RunSqlAction(ControlQueryMappingRepository.GetByFormID, request).GetDataOrExceptionIfError();
ResponseObjectPackage<List<ControlQueryMappingModel>> response = new ResponseObjectPackage<List<ControlQueryMappingModel>>() { resultData = mappings };
return response;
}
/// <summary>
/// Получить список групп типов контролов
/// </summary>
/// <param name="package"></param>
/// <returns></returns>
public ResponseObjectPackage<List<ControlTypeGroup>> GetControlTypeGroupList(RequestPackage package)
{
ResponseObjectPackage<List<ControlTypeGroup>> res = new DBUtils(new FireBirdConnectionFactory()).RunSqlAction(new FormEditorDataCRUD().GetControlTypeGroupList, package);
res.resultData.Add(new ControlTypeGroup() { ID = ALL_GROUP_ID, name = ALL_GROUP_NAME });
res.resultData.Sort((x, y) => x.name.CompareTo(y.name));
return res;
}
/// <summary>
/// Проверить возможность удаления компонента
/// </summary>
/// <param name="request">бъект-оболочка RequestPackage, содержащая в поле requestID ID компонента</param>
/// <param name="connectionID">Объект подключения к базе данных</param>
/// <returns>Объект-оболочка ResponsePackagе</returns>
public ResponsePackage CheckDeleteControl(RequestPackage request, IDbConnection connectionID, IDbTransaction transactionID)
{
string sql = string.Format(
" select id from CONTROL_QUERY_MAPPING where control_id = {0} " +
" union " +
" select id from FORM_IN_PARAMETERS where control_id = {0} " +
" union " +
" select id from FORM_OUT_PARAMETERS where control_id = {0} " +
" union " +
" select id from ACTION_PARAMETERS where control_id = {0} " +
" union " +
" select id from QUERY_QUERY_IN_PARAMETER where control_id = {0} ",
request.requestID
);
ResponseTablePackage res = DBUtils.OpenSQL(sql, connectionID, transactionID);
res.ThrowExceptionIfError();
if (res.resultData.Rows.Count > 0)
{
return new ResponsePackage() { resultCode = -1, resultMessage = "Удаление компонента невозможно. Проверьте все ссылки на него." };
}
else
{
return new ResponsePackage() { resultCode = 0, resultMessage = "Удаление возможно." };
}
}
public ResponseObjectPackage<List<List<QueryOutParameter>>> ExecuteQuery(int queryID, Dictionary<int, object> dictionary)
{
Query query = this.GetQueryViewModel(queryID).GetDataOrExceptionIfError();
string sql = query.sqlText;
foreach (QueryInParameter inParam in query.inParameters)
{
object value = dictionary[inParam.ID];
if (value == null)
{
throw new Exception("Для запроса заданы не все параметры! Проверьте мета-описания формы!");
}
inParam.value = ValueTypesConverter.Initialize(value.ToString(), inParam.domainValueTypeID, true);
sql = sql.Replace("{" + inParam.name + "}", inParam.value.ToSQL());
}
RequestPackage request = new RequestPackage() { requestString = sql };
List<Dictionary<string, object>> result = new DBUtils().RunSqlAction(new DynamicCRUD().GetDictionaryData, request).GetDataOrExceptionIfError();
List<List<QueryOutParameter>> resultOut = new List<List<QueryOutParameter>>();
foreach (Dictionary<string, object> row in result)
{
List<QueryOutParameter> rowOut = new List<QueryOutParameter>();
foreach (QueryOutParameter param in query.outParameters)
{
param.name = param.name.ToUpper();
if (!row.ContainsKey(param.name))
{
throw new Exception("Для запроса заданы не все параметры! Проверьте мета-описания формы!");
}
string value = row[param.name] == null ? null : row[param.name].ToString();
rowOut.Add(new QueryOutParameter(param, ValueTypesConverter.Initialize(value, param.domainValueTypeID, true)));
}
resultOut.Add(rowOut);
}
return new ResponseObjectPackage<List<List<QueryOutParameter>>>() { resultData = resultOut };
}
public ResponseObjectPackage<Dictionary<int, object>> GetDictionaryObjectByID(int dictionaryID, int pkValue)
{
Dictionary dictionary = new DictionariesLogic().GetDictionaryViewModel(dictionaryID).GetDataOrExceptionIfError();
string sql = string.Format(
"select {0} " + Environment.NewLine +
"from {1} " + Environment.NewLine +
"where {2} = {3}",
string.Join(", ", dictionary.fields.Select(e => e.columnName)),
dictionary.tableName,
dictionary.GetPrimaryKey().columnName,
pkValue
);
RequestPackage request = new RequestPackage() { requestString = sql };
List<Dictionary<string, object>> response = new DBUtils().RunSqlAction(new DynamicCRUD().GetDictionaryData, request).GetDataOrExceptionIfError();
if (response.Count > 1 || response.Count == 0)
{
throw new Exception("Запрос данных полей из словаря возвратил не одну строку! dictionaryID = " + dictionaryID + " pkValue = " + pkValue);
}
Dictionary<int, object> result = new Dictionary<int, object>();
foreach (DictionaryField field in dictionary.fields)
{
result.Add(field.ID, response[0][field.columnName]);
}
return new ResponseObjectPackage<Dictionary<int, object>>() { resultData = result };
}
public ResponseObjectPackage<List<Dictionary<string, object>>> GetDictionaryData(RequestPackage package, IDbConnection connectionID)
{
string sql = package.requestString;
ResponseTablePackage res = DBUtils.OpenSQL(sql, connectionID);
res.ThrowExceptionIfError();
List<Dictionary<string, object>> list = res.GetDataOrExceptionIfError().ToListOfDictionaries();
return new ResponseObjectPackage<List<Dictionary<string, object>>>() { resultData = list };
}
public ResponsePackage UpdateDictionaryData(RequestPackage package, IDbConnection connectionID)
{
string sql = package.requestString;
bool returningID = package.requestID == 1;
ResponseTablePackage res = DBUtils.ExecuteSQL(sql, connectionID, returningID);
res.ThrowExceptionIfError();
return res;
}
/// <summary>
/// Функция удаления группы типов контролов
/// </summary>
/// <param name="request">Объект-оболочка RequestPackage, содержащая в поле requestID ID группы типов контролов</param>
/// <param name="connectionID">Объект подключения к базе данных</param>
/// <returns>Объект-оболочка ResponsePackagе</returns>
public ResponsePackage DeleteControlTypeGroupByID(RequestPackage request, IDbConnection connectionID)
{
string sql = string.Format(
" delete from control_type_groups where ID = {0} ",
request.requestID
);
DBUtils.ExecuteSQL(sql, connectionID).ThrowExceptionIfError();
return new ResponsePackage();
}
/// <summary>
/// Функция удаления свойства контрола по его ID
/// </summary>
/// <param name="request">Объект-оболочка RequestPackage, содержащая в поле requestID ID компонента</param>
/// <param name="connectionID">Объект подключения к базе данных</param>
/// <returns>Объект-оболочка ResponsePackagе</returns>
public ResponsePackage DeletePropertiesByControlID(RequestPackage request, IDbConnection connectionID, IDbTransaction transactionID)
{
string sql = string.Format(
" delete from control_properties where control_id = {0} ",
request.requestID
);
DBUtils.ExecuteSQL(sql, connectionID, false, transactionID).ThrowExceptionIfError();
return new ResponsePackage();
}
public static ResponseObjectPackage<List<DomainValueTypeModel>> GetAll(RequestPackage package, IDbConnection connectionID)
{
string sql = string.Format(
"select ID, NAME " + Environment.NewLine +
"from DOMAIN_VALUE_TYPES;"
);
List<DomainValueTypeModel> list = DBOrmUtils.OpenSqlList<DomainValueTypeModel>(sql, DomainValueTypesRepository.mappingDictionary, connectionID);
return new ResponseObjectPackage<List<DomainValueTypeModel>>() { resultData = list };
}
public ResponseObjectPackage<List<DictionaryForeignKeyModel>> GetDictionaryForeignKeysByDictionaryIDSource(int dictionaryID)
{
RequestPackage request = new RequestPackage()
{
requestID = dictionaryID
};
ResponseObjectPackage<List<DictionaryForeignKeyModel>> response = new DBUtils().RunSqlAction(DictionaryForeignKeysRepository.GetByDictionaryIDSource, request);
return response;
}
/// <summary>
/// Функция удаления типа свойства для типа контрола по его ID
/// </summary>
/// <param name="request">Объект-оболочка RequestPackage, содержащая в поле requestID ID типа свойства</param>
/// <param name="connectionID">Объект подключения к базе данных</param>
/// <returns>Объект-оболочка ResponsePackagе</returns>
public ResponsePackage DeletePropertyTypeByID(RequestPackage request, IDbConnection connectionID)
{
string sql = string.Format(
" delete from control_type_property_type where id = {0} ",
request.requestID
);
DBUtils.ExecuteSQL(sql, connectionID).ThrowExceptionIfError();
return new ResponsePackage();
}
public ResponseObjectPackage<List<FormInParameterModel>> GetFormInParametersByFormID(int formID)
{
RequestPackage request = new RequestPackage()
{
requestID = formID
};
ResponseObjectPackage<List<FormInParameterModel>> response = new DBUtils().RunSqlAction(FormInParametersRepository.GetByFormID, request);
return response;
}
/// <summary>
/// Функция получения списка типов свойств компонентов формы по его ID
/// </summary>
/// <param name="request">>Объект-оболочка RequestPackage</param>
/// <param name="connectionID">Объект подключения к базе данных</param>
/// <returns>Объект-оболочка ResponseObjectPackagе, содержащая в поле resultData список типов свойств</returns>
public ResponseObjectPackage<List<ControlPropertyTypeModel>> GetPropertyTypeList(RequestPackage request, IDbConnection connectionID)
{
string sql = string.Format(
" select p.* " +
" from control_property_types p "
);
List<ControlPropertyTypeModel> list = DBOrmUtils.OpenSqlList<ControlPropertyTypeModel>(sql, ControlPropertyTypeRepository.propertyTypeMapping, connectionID);
return new ResponseObjectPackage<List<ControlPropertyTypeModel>>() { resultData = list };
}
/// <summary>
/// Функция удаления формы
/// </summary>
/// <param name="request">Объект-оболочка RequestPackage c ID формы в requestID</param>
/// <param name="connectionID">Объект подключения к базе данных</param>
/// <returns>Объект-оболочка ResponsePackage</returns>
public ResponsePackage DeleteFormByID(RequestPackage request, IDbConnection connectionID)
{
string sql = string.Format(
" delete from forms where id = {0} ",
request.requestID
);
DBUtils.ExecuteSQL(sql, connectionID).ThrowExceptionIfError();
return new ResponsePackage();
}
public ResponsePackage DeleteForeignKey(int foreignKeyID)
{
RequestPackage request = new RequestPackage()
{
requestID = foreignKeyID
};
ResponsePackage response = new DBUtils().RunSqlAction(DictionaryForeignKeysRepository.DeleteForeignKey, request);
response.ThrowExceptionIfError();
return response;
}
public ResponsePackage DeleteDictionaryField(int dictionaryFieldID)
{
RequestPackage fieldRequest = new RequestPackage()
{
requestID = dictionaryFieldID
};
ResponsePackage response = new DBUtils().RunSqlAction(new DictionaryTypesAdministrator().DeleteDictionaryField, fieldRequest);
response.ThrowExceptionIfError();
return response;
}
public static ResponsePackage Delete(RequestPackage request, IDbConnection connectionID, IDbTransaction transactionID)
{
int ID = request.requestID;
string sql = string.Format(
"delete from EVENT_TYPES" +
"where ID = {0}",
ID
);
DBUtils.ExecuteSQL(sql, connectionID, false, transactionID).ThrowExceptionIfError();
return new ResponsePackage();
}
public ResponseObjectPackage<List<DictionaryFieldAdminModel>> GetDictionaryTypesAdministratorData(int dictionaryID)
{
RequestPackage request = new RequestPackage()
{
requestID = dictionaryID
};
ResponseObjectPackage<List<DictionaryFieldAdminModel>> response = new DBUtils().RunSqlAction(new DictionaryTypesAdministrator().GetDictionaryTypesAdministratorData, request);
response.ThrowExceptionIfError();
return response;
}
public ResponsePackage DeleteDictionaryGroup(int dictionaryGroupID)
{
RequestPackage request = new RequestPackage()
{
requestID = dictionaryGroupID
};
ResponsePackage response = new DBUtils().RunSqlAction(DictionaryGroupsRepository.DeleteDictionaryGroup, request);
response.ThrowExceptionIfError();
return response;
}
public static ResponsePackage DeletePrimaryKey(RequestPackage request, IDbConnection connectionID, IDbTransaction transactionID)
{
int id = request.requestID;
string sql = string.Format(
"delete from DICTIONARY_PRIMARY_KEYS " + Environment.NewLine +
"where ID = {0} ",
id
);
DBUtils.ExecuteSQL(sql, connectionID, false, transactionID).ThrowExceptionIfError();
return new ResponsePackage();
}
/// <summary>
/// Функция получения списка свойств компонента по его ID
/// </summary>
/// <param name="request">Объект-оболочка RequestPackage, содержащая в поле requestID id компонента</param>
/// <returns>Объект-оболочка ResponseObjectPackagе, содержащая в поле resultData список свойств компонента</returns>
public ResponseObjectPackage<List<ControlPropertyViewModel>> GetPropertiesByControlID(RequestPackage request)
{
ResponseObjectPackage<List<ControlPropertyViewModel>> response = new DBUtils().RunSqlAction(new ControlPropertiesRepository().GetPropertiesByControlID, request);
response.ThrowExceptionIfError();
// Свойству id ставим значение ID компонента
response.resultData.Find(x => x.property.ToLower() == "id").value = request.requestID.ToString();
foreach (ControlPropertyViewModel c in response.resultData)
{
c._value = c.GetRightValue();
}
return response;
}
/// <summary>
/// Функция получения списка типов свойств по его ID типа компонента
/// </summary>
/// <param name="request">Объект-оболочка RequestPackage, содержащая в поле requestID id типа компонента</param>
/// <param name="connectionID">Объект подключения к базе данных</param>
/// <returns>Объект-оболочка ResponseObjectPackagе, содержащая в поле resultData список типов свойств</returns>
public ResponseObjectPackage<List<PropertyTypeListEntity>> GetPropertyTypeListByControlType(RequestPackage request, IDbConnection connectionID)
{
string sql = string.Format(
" select p.*, t.name as property, t.logic_value_type_id " +
" from control_type_property_type p " +
" left join control_property_types t on p.control_property_type_id = t.id " +
" where p.CONTROL_TYPE_ID = {0}",
request.requestID
);
List<PropertyTypeListEntity> list = DBOrmUtils.OpenSqlList<PropertyTypeListEntity>(sql, ControlTypePropertyTypeRepository.propertyTypeMapping, connectionID);
return new ResponseObjectPackage<List<PropertyTypeListEntity>>() { resultData = list };
}
/// <summary>
/// Получить тип запроса с параметрами
/// </summary>
/// <returns></returns>
public JsonResult GetFullQueryType(int ID)
{
try
{
RequestPackage req = new RequestPackage() { requestID = ID };
ResponseObjectPackage<FullQueryType> response = new QueryEditorLogic().GetFullQueryType(req).GetSelfOrExceptionIfError();
return Json(response);
}
catch (Exception ex)
{
return this.HandleException(ex);
}
}
/// <summary>
/// Получить список входных параметров типов запросов
/// </summary>
/// <param name="ID"></param>
/// <returns></returns>
public JsonResult GetQueryInParamsList(int ID)
{
try
{
RequestPackage req = new RequestPackage() { requestID = ID };
ResponseObjectPackage<List<QueryInParameterModel>> response = new QueryEditorLogic().GetQueryInParamsList(req).GetSelfOrExceptionIfError();
return Json(response);
}
catch (Exception ex)
{
return this.HandleException(ex);
}
}
public JsonResult GetFormsList()
{
try
{
RequestPackage request = new RequestPackage();
List<FormListEntity> response = new FormsLogic().GetFormList(request).GetDataOrExceptionIfError();
return Json(response);
}
catch (Exception ex)
{
return this.HandleException(ex);
}
}
public JsonResult GetControlTypeList()
{
try
{
RequestPackage request = new RequestPackage();
ResponseObjectPackage<List<ControlTypeListEntity>> response = new FormEditorLogic().GetControlTypeList(request).GetSelfOrExceptionIfError();
return Json(response);
}
catch (Exception ex)
{
return this.HandleException(ex);
}
}
public JsonResult GetPropertiesByFormID(int id)
{
try
{
RequestPackage request = new RequestPackage() { requestID = id };
List<ControlPropertyViewModel> response = new ControlPropertyLogic().GetPropertiesByFormID(request).GetDataOrExceptionIfError();
return Json(response);
}
catch (Exception ex)
{
return this.HandleException(ex);
}
}
/// <summary>
/// Получить тип запроса с параметрами
/// </summary>
/// <param name="package"></param>
/// <returns></returns>
public ResponseObjectPackage<FullQueryType> GetFullQueryType(RequestPackage package)
{
QueryTypeModel queryType = this.GetQueryType(package).GetDataOrExceptionIfError();
List<QueryInParameterModel> inParams = this.GetQueryInParamsList(package).GetDataOrExceptionIfError();
List<QueryOutParameterModel> outParams = this.GetQueryOutParamsList(package).GetDataOrExceptionIfError();
FullQueryType result = new FullQueryType()
{
queryType = queryType,
inParams = inParams,
outParams = outParams
};
return new ResponseObjectPackage<FullQueryType>() { resultData = result };
}
/// <summary>
/// Функция получения списка свойств компонентов формы по ее ID
/// </summary>
/// <param name="request">Объект-оболочка RequestPackage, содержащая в поле requestID ID формы</param>
/// <returns>Объект-оболочка ResponseObjectPackagе, содержащая в поле resultData список свойств компонентов формы</returns>
public ResponseObjectPackage<List<ControlPropertyViewModel>> GetPropertiesByFormID(RequestPackage request)
{
ResponseObjectPackage<List<ControlPropertyViewModel>> response = new DBUtils().RunSqlAction(new ControlPropertiesRepository().GetPropertiesByFormID, request);
response.ThrowExceptionIfError();
foreach (ControlPropertyViewModel c in response.resultData)
{
c._value = c.GetRightValue();
}
foreach (ControlPropertyViewModel c in response.resultData.FindAll(x => x.property.ToLower() == "id"))
{
c._value = c.controlID;
}
return response;
}
