private static string GetQueryName(EQueryType action, Type type)
{
string actionName = string.Empty;
string objectName = string.Empty;
switch (action)
{
case EQueryType.Insert:
actionName = "Insert";
break;
case EQueryType.Delete:
actionName = "Delete";
break;
case EQueryType.Update:
actionName = "Update";
break;
case EQueryType.Get:
actionName = "Get";
break;
case EQueryType.List:
actionName = "List";
break;
}
objectName = type.Name.Replace("Repository", string.Empty);
return($"{actionName}{objectName}");
}
internal static string FIELDS(EQueryType queryType, QueryConditions conditions)
{
string query = string.Empty;
switch (queryType)
{
case EQueryType.GENERAL:
query = @"
SELECT WR.*
,US.""NAME"" AS ""OWNER""
,EX.""CODIGO"" AS ""EXPEDIENT""
,COALESCE(WC.""NAME"", '') AS ""CATEGORY_NAME""" ;
break;
case EQueryType.CLUSTERED:
query = @"
SELECT " + (long)queryType + @" AS ""QUERY_TYPE""
,DATE_TRUNC('" + conditions.Step.ToString() + @"', WR.""DATE"") AS ""STEP""
,SUM(WR.""TOTAL"") AS ""TOTAL""" ;
break;
}
return(query);
}
private XmlElement AppendMode(EQueryType querytype, bool allowed)
{
XmlElement eWord = XmlHelper.CreateAndAppendElement(m_AllowedQueryTypes, m_XPathQueryType);
eWord.SetAttribute(m_XPathAttributeName, querytype.ToString());
eWord.SetAttribute(m_XPathAttributeValue, XmlConvert.ToString(allowed));
return(eWord);
}
/// <summary>
/// Constructor for the query.
/// </summary>
/// <param name="uniqueIdentifier">The unique identifier for this query.</param>
/// <param name="query">The string of the query to execute.</param>
/// <param name="type">The type of the query.</param>
/// <param name="callbacks">The callback for when execution of the query is finished.</param>
/// <param name="shouldCache">If the query should be cached or not after executing.</param>
/// <param name="parameters">The parameters for the query.</param>
public Query(object uniqueIdentifier, string query, EQueryType type, bool shouldCache = false,
IEnumerable <MySqlParameter> parameters = null, IEnumerable <QueryCallback> callbacks = null)
{
UniqueIdentifier = uniqueIdentifier?.ToString() ?? query;
QueryString = query;
Type = type;
ShouldCache = shouldCache;
Parameters = parameters ?? new List <MySqlParameter>();
Callbacks = callbacks ?? new List <QueryCallback>();
}
/// <summary>
/// Add anykind of supported incoming registry
/// </summary>
/// <typeparam name="T">Type of object to add</typeparam>
/// <param name="query">The query for register.</param>
/// <param name="queryType">The type of register.</param>
public void AddRegistry <T>(T query, EQueryType queryType)
{
if (query.GetType() == typeof(IList <string>))
{
// Build the query
}
else if (query.GetType() == typeof(string))
{
// Execute if query is single
}
}
private void SetAllowed(EQueryType queryType, bool allowed)
{
XmlNode node = m_AllowedQueryTypes.SelectSingleNode(String.Format(m_XPathFilter, queryType.ToString()));
if (node == null)
{
AppendMode(queryType, allowed);
}
else
{
node.Value = XmlConvert.ToString(allowed);
}
}
private bool IsAllowed(EQueryType queryType)
{
bool allowed = false;
XmlNode node = m_AllowedQueryTypes.SelectSingleNode(String.Format(m_XPathFilter, queryType.ToString()));
if (node == null)
{
allowed = true;
}
else
{
allowed = XmlConvert.ToBoolean(node.Value);
}
return(allowed);
}
public static string Get(EQueryType action, Type type)
{
string query = GetQueryName(action, type);
using (var stream = Assembly.GetExecutingAssembly().GetManifestResourceStream($"Mori.Infra.Data.SqlServer.Queries.{query}.sql"))
{
if (stream == null)
{
throw new Exception($"Query não localizada: {query}");
}
using (var reader = new StreamReader(stream))
{
return(reader.ReadToEnd());
}
}
}
internal static string SELECT_FIELDS(EQueryType queryType, QueryConditions conditions)
{
string query = @"
SELECT " + (long)queryType + @" AS ""QUERY_TYPE""";
switch (queryType)
{
case EQueryType.GENERAL:
query += @"
,CU.*" ;
break;
}
return(query);
}
public static ProductCalculationViewModel Create(EQueryType type)
{
switch (type)
{
case EQueryType.None:
return new StartCalculationViewModel() { QueryType = type } ;
case EQueryType.RequestPostage:
case EQueryType.RequestValue:
case EQueryType.RequestString:
return new RequestValueViewModel() { QueryType = type };
case EQueryType.SelectIndex:
case EQueryType.ShowMenu:
case EQueryType.ShowDisplay:
case EQueryType.SelectValue:
default:
return new ProductCalculationViewModel() { QueryType = type };
}
}
internal static string SELECT_FIELDS(EQueryType queryType, QueryConditions conditions)
{
string query = @"
SELECT " + (long)queryType + @" AS ""QUERY_TYPE""";
switch (queryType)
{
case EQueryType.GENERAL:
query += @"
,TL.*" ;
break;
case EQueryType.CLUSTERED:
query += @"
,DATE_TRUNC('" + conditions.Step.ToString() + @"', TL.""DATE"") AS ""STEP""
,SUM(TL.""TOTAL"") AS ""TOTAL""" ;
break;
}
return(query);
}
protected DataParameters GetDataParameters(string itemName, EQueryType queryType, int rowCount = 3, int page = 0, QueryFilterCollection queryFilterCollection = null, IEnumerable <SortingName> sortingNames = null)
{
var result = new DataParameters()
{
ItemName = itemName,
PageIndex = page,
QueryType = queryType,
RecordsCount = rowCount
};
if (sortingNames != null)
{
result.SortingNames.AddRange(sortingNames);
}
if (queryFilterCollection != null)
{
result.FilterCollection.Groups.AddRange(queryFilterCollection.Groups);
result.FilterCollection.QueryFilters.AddRange(queryFilterCollection.QueryFilters);
result.FilterCollection.GroupingOperator = queryFilterCollection.GroupingOperator;
}
return(result);
}
public Query(string query, EQueryType type, bool shouldCache) : this(uniqueIdentifier : null, query, type,
shouldCache)
{
}
public Query(object identifier, string query, EQueryType type) : this(uniqueIdentifier : identifier, query, type)
{
}
public Query(object identifier, string query, EQueryType type, params MySqlParameter[] parameters) : this(
uniqueIdentifier : identifier, query, type, false, parameters)
{
}
public Query(object identifier, string query, EQueryType type, bool shouldCache,
IEnumerable <QueryCallback> callbacks, params MySqlParameter[] parameters) : this(identifier, query, type,
shouldCache, parameters, callbacks)
{
}
/// <summary>
/// Sets the specified type.
/// </summary>
/// <param name="type">The type.</param>
private void Set(EQueryType type)
{
XmlConfigHelper.Set(m_dictionary, m_xpath + m_xpathQueryType, (int)type);
}
/// <summary>
/// Checks the query options.
/// </summary>
/// <param name="type">The type.</param>
/// <returns></returns>
private bool CheckQueryOptions(EQueryType type)
{
return XmlConfigHelper.Check(m_dictionary, m_xpath + m_xpathQueryOptions, (int)type);
}
public object ExecuteQuery(EQueryType queryType, string query, params MySqlParameter[] parameters)
{
object result = null;
MySqlDataReader reader = null;
using (var connection = CreateConnection())
{
try
{
var command = connection.CreateCommand();
command.CommandText = query;
foreach (var parameter in parameters)
{
command.Parameters.Add(parameter);
}
connection.Open();
switch (queryType)
{
case EQueryType.Reader:
var readerResult = new List <Row>();
reader = command.ExecuteReader();
while (reader.Read())
{
try
{
var values = new Dictionary <string, object>();
for (var i = 0; i < reader.FieldCount; i++)
{
var columnName = reader.GetName(i);
values.Add(columnName, reader[columnName]);
}
readerResult.Add(new Row {
Values = values
});
}
catch (Exception ex)
{
Logger.LogError(
$"The following query threw an error during reader execution:\nQuery: \"{query}\"\nError: {ex.Message}");
}
}
result = readerResult;
break;
case EQueryType.Scalar:
result = command.ExecuteScalar();
break;
case EQueryType.NonQuery:
result = command.ExecuteNonQuery();
break;
default:
throw new ArgumentOutOfRangeException(nameof(queryType), queryType, null);
}
}
catch (Exception ex)
{
Logger.LogError("[RFGarage] DbError: " + ex);
}
finally
{
reader?.Close();
connection.Close();
}
}
return(result);
}
public Query(string query, EQueryType type, IEnumerable <QueryCallback> callbacks,
params MySqlParameter[] parameters) : this(null, query, type, false, parameters, callbacks)
{
}
public Query(object identifier, string query, EQueryType type, bool shouldCache) : this(identifier, query, type,
shouldCache, new List <MySqlParameter>())
{
}
/// <summary>
/// Unsets the query options.
/// </summary>
/// <param name="type">The type.</param>
private void UnsetQueryOptions(EQueryType type)
{
XmlConfigHelper.Unset(m_dictionary, m_xpath + m_xpathQueryOptions, (int)type);
}
public Query(string query, EQueryType type, params MySqlParameter[] parameters) : this(uniqueIdentifier : null,
query, type, false, parameters)
{
}
public Query(string query, EQueryType type) : this(uniqueIdentifier : null, query, type)
{
}
public Query(object identifier, string query, EQueryType type, bool shouldCache,
params MySqlParameter[] parameters) : this(uniqueIdentifier : identifier, query, type, shouldCache,
parameters)
{
}
public Query(object identifier, string query, EQueryType type, bool shouldCache,
IEnumerable <MySqlParameter> parameters, params QueryCallback[] callbacks) : this(
uniqueIdentifier : identifier, query, type, shouldCache, parameters, callbacks)
{
}
protected ConfigurationParameters GetConfigurationParameters(string itemName, EQueryType queryType)
{
var result = new ConfigurationParameters()
{
ItemName = itemName,
QueryType = queryType,
};
return(result);
}
private XmlElement AppendMode(EQueryType querytype, bool allowed)
{
XmlElement eWord = XmlHelper.CreateAndAppendElement(m_AllowedQueryTypes, m_XPathQueryType);
eWord.SetAttribute(m_XPathAttributeName, querytype.ToString());
eWord.SetAttribute(m_XPathAttributeValue, XmlConvert.ToString(allowed));
return eWord;
}
private bool IsAllowed(EQueryType queryType)
{
bool allowed = false;
XmlNode node = m_AllowedQueryTypes.SelectSingleNode(String.Format(m_XPathFilter, queryType.ToString()));
if (node == null)
allowed = true;
else
allowed = XmlConvert.ToBoolean(node.Value);
return allowed;
}
/// <summary>
/// Sets the specified type.
/// </summary>
/// <param name="type">The type.</param>
private void Set(EQueryType type)
{
XmlConfigHelper.Set(m_dictionary, m_xpath + m_xpathQueryType, (int)type);
}
private void SetAllowed(EQueryType queryType, bool allowed)
{
XmlNode node = m_AllowedQueryTypes.SelectSingleNode(String.Format(m_XPathFilter, queryType.ToString()));
if (node == null)
{
AppendMode(queryType, allowed);
}
else
{
node.Value = XmlConvert.ToString(allowed);
}
}
/// <summary>
/// Unsets the query options.
/// </summary>
/// <param name="type">The type.</param>
private void UnsetQueryOptions(EQueryType type)
{
XmlConfigHelper.Unset(m_dictionary, m_xpath + m_xpathQueryOptions, (int)type);
}
internal static string SELECT_FIELDS(EQueryType queryType, QueryConditions conditions)
{
string query;
if (conditions.Groups == null)
{
query = @"
SELECT
WO.*
,COALESCE(WR.""CODE"", '') AS ""WORK_REPORT_ID""
,COALESCE(WC.""NAME"", '') AS ""CATEGORY""
,COALESCE(WC.""OID"", 0) AS ""OID_CATEGORY""
,COALESCE(EX.""CODIGO"", '') AS ""EXPEDIENT_ID""";
}
else
{
query = @"
SELECT
(MAX(WO.""OID"")::varchar || '00' || MAX(WO.""ENTITY_TYPE"")::varchar)::bigint AS ""OID""
,MAX(WO.""OID_RESOURCE"") AS ""OID_RESOURCE""
,MAX(WO.""OID_WORK_REPORT"") AS ""OID_WORK_REPORT""
,MAX(WO.""ENTITY_TYPE"") AS ""ENTITY_TYPE""
,1 AS ""AMOUNT""
,MAX(WO.""FROM"") AS ""FROM""
,MAX(WO.""TILL"") AS ""TILL""
,SUM(WO.""HOURS"" * WO.""AMOUNT"") AS ""HOURS""
,AVG(WO.""COST"") AS ""COST""
,SUM(WO.""EXTRA_COST"") AS ""EXTRA_COST""
,SUM(WO.""TOTAL"") AS ""TOTAL""
,'' AS ""COMMENTS""
,MAX(WR.""CODE"") AS ""WORK_REPORT_ID""
,COALESCE(MAX(WC.""OID""), 0) AS ""OID_CATEGORY""
,COALESCE(MAX(WC.""NAME""), '') AS ""CATEGORY""
,COALESCE(MAX(EX.""CODIGO""), '') AS ""EXPEDIENT_ID""";
}
switch (queryType)
{
case EQueryType.ALL:
query += @"
,'' AS ""RESOURCE_CODE""
,'' AS ""RESOURCE""" ;
break;
case EQueryType.STAFF:
if (conditions.Groups == null)
{
query += @"
,RS.""CODIGO"" AS ""RESOURCE_CODE""
,RS.""APELLIDOS"" || ', ' || RS.""NOMBRE"" AS ""RESOURCE""" ;
}
else
{
query += @"
,MAX(RS.""CODIGO"") AS ""RESOURCE_CODE""
,MAX(RS.""APELLIDOS"") || ', ' || MAX(RS.""NOMBRE"") AS ""RESOURCE""" ;
}
break;
case EQueryType.TOOLS:
if (conditions.Groups == null)
{
query += @"
,RS.""CODE"" AS ""RESOURCE_CODE""
,RS.""NAME"" AS ""RESOURCE""" ;
}
else
{
query += @"
,MAX(RS.""CODE"") AS ""RESOURCE_CODE""
,MAX(RS.""NAME"") AS ""RESOURCE""" ;
}
break;
case EQueryType.DELIVERIES:
if (conditions.Groups == null)
{
query += @"
,RS.""CODIGO"" AS ""RESOURCE_CODE""
,'ALBARAN DE OBRA' AS ""RESOURCE""" ;
}
else
{
query += @"
,MAX(RS.""CODIGO"") AS ""RESOURCE_CODE""
,'ALBARAN DE OBRA' AS ""RESOURCE""" ;
}
break;
case EQueryType.CLUSTERED:
query = @"
SELECT " + (long)queryType + @" AS ""QUERY_TYPE""
,DATE_TRUNC('" + conditions.Step.ToString() + @"', WO.""DATE"") AS ""STEP""
,SUM(WO.""TOTAL"") AS ""TOTAL""" ;
break;
}
return(query);
}
public Query(string query, EQueryType type, params QueryCallback[] callbacks) : this(uniqueIdentifier : null,
query, type, false, null, callbacks)
{
}
public Query(object identifier, string query, EQueryType type, bool shouldCache,
params QueryCallback[] callbacks) : this(uniqueIdentifier : identifier, query, type, shouldCache, null,
callbacks)
{
}
/// <summary>
/// Checks the query options.
/// </summary>
/// <param name="type">The type.</param>
/// <returns></returns>
private bool CheckQueryOptions(EQueryType type)
{
return(XmlConfigHelper.Check(m_dictionary, m_xpath + m_xpathQueryOptions, (int)type));
}
public Query(string query, EQueryType type, IEnumerable <MySqlParameter> parameters,
params QueryCallback[] callbacks) : this(uniqueIdentifier : null, query, type, false, parameters, callbacks)
{
}
