public ResponseObjectPackage<Dictionary> GetDictionaryViewModel(int dictionaryID)
{
DictionaryModel dict = this.GetDictionaryByID(dictionaryID).GetDataOrExceptionIfError();
List<DictionaryField> fields = new DictionaryFieldsLogic().GetDictionaryFieldsViewModel(dictionaryID).GetDataOrExceptionIfError();
Dictionary dictionary = new Dictionary(dict, fields);
return new ResponseObjectPackage<Dictionary>() { resultData = dictionary };
}
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<EventActionParameter>> GetActionParametersByActionsList(RequestObjectPackage<List<EventAction>> request, IDbConnection connectionID)
{
List<int> obj = (request.requestData ?? new List<EventAction>() {}).Select(e => e.ID).ToList();
obj.Add(-1);
Dictionary<string, string> mappingDictionary = new Dictionary<string, string>()
{
{"ID","ID"},
{"actionID","ACTION_ID"},
{"actionParameterTypeID","ACTION_PARAMETER_TYPE_ID"},
{"controlID","CONTROL_ID"},
{"name","NAME"},
{"domainValueTypeID","DOMAIN_VALUE_TYPE_ID"},
};
string sql = string.Format(
"select ap.ID, ap.ACTION_ID, ap.ACTION_PARAMETER_TYPE_ID, ap.CONTROL_ID, apt.NAME, apt.DOMAIN_VALUE_TYPE_ID " + Environment.NewLine +
"from ACTION_PARAMETERS ap " + Environment.NewLine +
"inner join ACTION_PARAMETER_TYPES apt on apt.ID = ap.ACTION_PARAMETER_TYPE_ID " + Environment.NewLine +
"where ap.ACTION_ID in ({0})",
string.Join(", ", obj)
);
List<EventActionParameter> list = DBOrmUtils.OpenSqlList<EventActionParameter>(sql, mappingDictionary, connectionID);
return new ResponseObjectPackage<List<EventActionParameter>>() { resultData = list };
}
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<List<Dictionary<string, object>>> ExecuteGridpanelQuery(int queryID, Dictionary<int, object> dictionary)
{
List<List<QueryOutParameter>> preResult = this.ExecuteQuery(queryID, dictionary).GetDataOrExceptionIfError();
List<Dictionary<string, object>> list = new List<Dictionary<string, object>>();
foreach (List<QueryOutParameter> row in preResult)
{
list.Add(row.ToDictionary(e => e.ID.ToString(), e => e.value.value));
}
return new ResponseObjectPackage<List<Dictionary<string, object>>>() { resultData = list };
}
public JsonResult SaveDictionaryData(Dictionary<string, string> row, int dictionaryID)
{
try
{
Dictionary<int, string> dict = row.ToDictionary(e => Int32.Parse(e.Key), e => e.Value);
ResponsePackage result = new DictionariesDataLogic().SaveDictionaryData(dict, dictionaryID);
return Json(result);
}
catch (Exception ex)
{
return this.HandleException(ex);
}
}
public ResponseObjectPackage<List<ComboboxModel>> ExecuteComboboxQuery(int queryID, Dictionary<int, object> dictionary, int queryMappingKeyID, int queryMappingValueID)
{
List<List<QueryOutParameter>> preResult = this.ExecuteQuery(queryID, dictionary).GetDataOrExceptionIfError();
List<ComboboxModel> list = new List<ComboboxModel>();
foreach (List<QueryOutParameter> row in preResult)
{
int? key = (int?)row.Where(e => e.ID == queryMappingKeyID).Select(e => ValueTypesConverter.Convert(e.value, 3).value).FirstOrDefault();
string value = (string)row.Where(e => e.ID == queryMappingValueID).Select(e => ValueTypesConverter.Convert(e.value, 1).value).FirstOrDefault();
if (key == null)
{
throw new Exception("Некорректно задан запрос, ключ не имеет значения! QueryID = " + queryID);
}
ComboboxModel model = new ComboboxModel() { key = key.Value, value = value };
list.Add(model);
}
return new ResponseObjectPackage<List<ComboboxModel>>() { resultData = list };
}
public DictionaryValue(Dictionary dictionary_, Dictionary<int, string> row)
{
this.dictionary = dictionary_;
this.fields = new List<DictionaryFieldValue>();
foreach (DictionaryField field in dictionary_.fields)
{
DictionaryFieldValue item = null;
if (row.ContainsKey(field.ID))
{
item = new DictionaryFieldValue(field, row[field.ID]);
}
else
{
item = new DictionaryFieldValue(field, null, false);
}
this.fields.Add(item);
}
}
public ResponseObjectPackage<List<ActionTypeProperty>> GetActionTypePropertiesByActionsList(RequestObjectPackage<List<EventAction>> request, IDbConnection connectionID)
{
List<int> obj = (request.requestData ?? new List<EventAction>()).Select(e => e.actionTypeID).ToList();
obj.Add(-1);
Dictionary<string, string> mappingDictionary = new Dictionary<string, string>()
{
{"ID","ID"},
{"value","VALUE"},
{"actionTypeID","ACTION_TYPE_ID"},
{"actionKindPropertyID","ACTION_KIND_PROPERTY_ID"},
{"name","NAME"},
};
string sql = string.Format(
"select p.ID, p.\"VALUE\", p.ACTION_TYPE_ID, p.ACTION_KIND_PROPERTY_ID, akp.NAME " + Environment.NewLine +
"from ACTION_TYPE_PROPERTIES p " + Environment.NewLine +
"inner join ACTION_KIND_PROPERTIES akp on akp.ID = p.ACTION_KIND_PROPERTY_ID " + Environment.NewLine +
"where p.ACTION_TYPE_ID in({0})",
string.Join(", ", obj)
);
List<ActionTypeProperty> list = DBOrmUtils.OpenSqlList<ActionTypeProperty>(sql, mappingDictionary, connectionID);
return new ResponseObjectPackage<List<ActionTypeProperty>>() { resultData = list };
}
public ResponseObjectPackage<List<EventAction>> GetActionsByEventsList(RequestObjectPackage<List<EventModel>> request, IDbConnection connectionID)
{
List<int> obj = (request.requestData ?? new List<EventModel>()).Select(e => e.ID).ToList();
obj.Add(-1);
Dictionary<string, string> mappingDictionary = new Dictionary<string, string>()
{
{"ID","ID"},
{"eventID","EVENT_ID"},
{"orderNumber","ORDER_NUMBER"},
{"actionTypeID","ACTION_TYPE_ID"},
{"actionKindID","ACTION_KIND_ID"},
};
string sql = string.Format(
"select a.ID, a.EVENT_ID, a.ORDER_NUMBER, a.ACTION_TYPE_ID, t.ACTION_KIND_ID " + Environment.NewLine +
"from ACTIONS a " + Environment.NewLine +
"inner join ACTION_TYPES t on t.ID = a.ACTION_TYPE_ID " + Environment.NewLine +
"where a.EVENT_ID in({0});",
string.Join(", ", obj)
);
List<EventAction> list = DBOrmUtils.OpenSqlList<EventAction>(sql, mappingDictionary, connectionID);
return new ResponseObjectPackage<List<EventAction>>() { resultData = list };
}
public ResponsePackage SaveDictionaryData(Dictionary<int, string> row, int dictionaryID)
{
DictionaryValue dictVal = this.FillDictionaryValue(row, dictionaryID);
return this.SaveDictionaryData(dictVal, dictionaryID);
}
private DictionaryValue FillDictionaryValue(Dictionary<int, string> row, int dictionaryID)
{
Dictionary dictionary = new DictionariesLogic().GetDictionaryViewModel(dictionaryID).GetDataOrExceptionIfError();
DictionaryValue dictVal = new DictionaryValue(dictionary, row);
return dictVal;
}