public static IEnumerable <T> GetView <T>(string catalog, T poco, string tableName, string keyName, long page,
List <Filter> filters, bool byOffice,
int officeId, bool showall,
long pageSize)
{
Sql sql = Sql.Builder.Append("SELECT * FROM " + Sanitizer.SanitizeIdentifierName(tableName)).Append("WHERE 1 = 1");
if (byOffice)
{
sql.Append("AND office_id IN (SELECT * FROM office.get_office_ids(@0))", officeId);
}
AddFilters(ref sql, poco, filters);
if (!string.IsNullOrWhiteSpace(keyName))
{
sql.OrderBy(keyName);
}
else
{
sql.Append("ORDER BY 1");
}
if (!showall)
{
long offset = (page - 1) * pageSize;
sql.Append("LIMIT @0", pageSize);
sql.Append("OFFSET @0", offset);
}
return(Factory.Get <T>(catalog, sql));
}
/// <summary>
/// Creates item selector html anchor which bascially is an extender of the control.
/// The extender is an html image button which, when clicked, will open
/// a popup window which allows selection, filtering, search, etc. on the target table.
/// </summary>
/// <param name="associatedControlId">ClientID of the DropDownList control to wich this control is associated to.</param>
/// <param name="table"></param>
/// <param name="itemSelectorPath"></param>
/// <param name="tableSchema"></param>
/// <param name="tableName"></param>
/// <param name="tableColumn"></param>
/// <param name="displayViews"></param>
/// <returns></returns>
private static HtmlAnchor GetItemSelector(string associatedControlId, DataTable table, string itemSelectorPath, string tableSchema, string tableName, string tableColumn, string displayViews)
{
if (table.Rows.Count.Equals(0) || string.IsNullOrWhiteSpace(displayViews))
{
return(null);
}
using (var itemSelectorAnchor = new HtmlAnchor())
{
//string relation = string.Empty;
//Get the expression value of display view from comma seprated list of expressions.
//The expression must be a valid fully qualified table or view name.
string viewRelation = GetExpressionValue(displayViews, tableSchema, tableName, tableColumn);
string schema = viewRelation.Split('.').First();
string view = viewRelation.Split('.').Last();
//Sanitize the schema and the view
schema = Sanitizer.SanitizeIdentifierName(schema);
view = Sanitizer.SanitizeIdentifierName(view);
if (string.IsNullOrWhiteSpace(schema) || string.IsNullOrWhiteSpace(view))
{
return(null);
}
itemSelectorAnchor.Attributes["class"] = ConfigurationHelper.GetScrudParameter("ItemSelectorAnchorCssClass");
itemSelectorAnchor.HRef = itemSelectorPath + "?Schema=" + schema + "&View=" + view + "&AssociatedControlId=" + associatedControlId;
return(itemSelectorAnchor);
}
}
private static DataTable GetTable(string tableSchema, string tableName, string tableColumn, string displayViews,
bool useDisplayViewsAsParent)
{
if (useDisplayViewsAsParent)
{
//Get the expression value of display view from comma seprated list of expressions.
//The expression must be a valid fully qualified table or view name.
string viewRelation = GetExpressionValue(displayViews, tableSchema, tableName, tableColumn);
string schema = viewRelation.Split('.').First();
string view = viewRelation.Split('.').Last();
//Sanitize the schema and the view
schema = Sanitizer.SanitizeIdentifierName(schema);
view = Sanitizer.SanitizeIdentifierName(view);
if (string.IsNullOrWhiteSpace(schema) || string.IsNullOrWhiteSpace(view))
{
return(FormHelper.GetTable(tableSchema, tableName, "1"));
}
return(FormHelper.GetTable(schema, view, "1"));
}
return(FormHelper.GetTable(tableSchema, tableName, "1"));
}
private static DataTable GetTable(string catalog, string targetSchema, string targetTableName,
string targetColumn, string currentTableSchema, string currentTableName, string currentColumn,
string displayViews, bool useDisplayViewsAsParent, bool useLocalColumnInDisplayViews)
{
if (useDisplayViewsAsParent)
{
string viewRelation = GetExpressionValue(catalog, displayViews, targetSchema, targetTableName,
targetColumn, currentTableSchema, currentTableName, currentColumn, useLocalColumnInDisplayViews);
string schema = viewRelation.Split('.').First();
string view = viewRelation.Split('.').Last();
//Sanitize the schema and the view
schema = Sanitizer.SanitizeIdentifierName(schema);
view = Sanitizer.SanitizeIdentifierName(view);
if (string.IsNullOrWhiteSpace(schema) || string.IsNullOrWhiteSpace(view))
{
return(FormHelper.GetTable(catalog, targetSchema, targetTableName, "1"));
}
return(FormHelper.GetTable(catalog, schema, view, "1"));
}
return(FormHelper.GetTable(catalog, targetSchema, targetTableName, "1"));
}
public static IEnumerable <T> ForDownloadTemplate <T>(string catalog, T poco, string tableName, string keyName, bool byOffice,
int officeId, bool includeData)
{
Sql sql = Sql.Builder.Append("SELECT * FROM " + Sanitizer.SanitizeIdentifierName(tableName)).Where("1 = 1");
if (!includeData)
{
sql.Append("AND 1 = 0");
}
if (byOffice)
{
sql.Append("AND office_id IN (SELECT * FROM office.get_office_ids(@0))", officeId);
}
if (!string.IsNullOrWhiteSpace(keyName))
{
sql.OrderBy(keyName);
}
else
{
sql.Append("ORDER BY 1");
}
return(Factory.Get <T>(catalog, sql));
}
public static T GetViewForEdit <T>(string catalog, string tableName, string primaryKey, object primaryKeyValue)
{
string sql = "SELECT * FROM " + Sanitizer.SanitizeIdentifierName(tableName);
sql += " WHERE " + Sanitizer.SanitizeIdentifierName(primaryKey) + "=@0;";
return(Factory.Get <T>(catalog, sql, primaryKeyValue).FirstOrDefault());
}
public static void Delete(string catalog, string tableName, string primaryKey, object primaryKeyValue)
{
string sql = "DELETE FROM " + Sanitizer.SanitizeIdentifierName(tableName);
sql += " WHERE " + Sanitizer.SanitizeIdentifierName(primaryKey) + "=@0;";
Factory.NonQuery(catalog, sql, primaryKeyValue);
}
public async Task UpdateAsync(Dictionary <string, object> item, object primaryKeyValue, List <CustomField> customFields)
{
if (string.IsNullOrWhiteSpace(this.Database))
{
return;
}
if (!this.SkipValidation)
{
if (!this.Validated)
{
await this.ValidateAsync(AccessTypeEnum.Edit, this.LoginId, this.Database, false).ConfigureAwait(false);
}
if (!this.HasAccess)
{
Log.Information($"Access to edit entity \"{this.FullyQualifiedObjectName}\" with Primary Key {this.PrimaryKey} was denied to the user with Login ID {this.LoginId}.");
throw new UnauthorizedException(Resources.AccessIsDenied);
}
}
item["AuditUserId"] = this.UserId;
item["AuditTs"] = DateTimeOffset.UtcNow;
item["Deleted"] = false;
using (var db = DbProvider.GetDatabase(this.Database))
{
var sql = new Sql("UPDATE " + this.FullyQualifiedObjectName + " SET");
int index = 0;
foreach (var prop in item.Where(x => !x.Key.Equals(this.IdentityColumn.Or(string.Empty).ToPascalCase())))
{
if (index > 0)
{
sql.Append(",");
}
sql.Append(Sanitizer.SanitizeIdentifierName(prop.Key.ToUnderscoreLowerCase()) + "=@0", prop.Value);
index++;
}
sql.Where(this.PrimaryKey + "=@0", primaryKeyValue);
try
{
await db.NonQueryAsync(sql).ConfigureAwait(false);
await this.AddCustomFieldsAsync(primaryKeyValue, customFields).ConfigureAwait(false);
}
catch (DbException ex)
{
Log.Error(ex.Message);
throw new DataAccessException(this.Database, ex.Message, ex);
}
}
}
public async Task <object> AddAsync(Dictionary <string, object> item, List <CustomField> customFields,
bool skipPrimaryKey)
{
if (string.IsNullOrWhiteSpace(this.Database))
{
return(null);
}
if (!this.SkipValidation)
{
if (!this.Validated)
{
await this.ValidateAsync(AccessTypeEnum.Create, this.LoginId, this.Database, false).ConfigureAwait(false);
}
if (!this.HasAccess)
{
Log.Information(
$"Access to add entity \"{this.FullyQualifiedObjectName}\" was denied to the user with Login ID {this.LoginId}. {item}");
throw new UnauthorizedException("Access is denied.");
}
}
item = this.Crypt(item);
item["audit_user_id"] = this.UserId;
item["audit_ts"] = DateTimeOffset.UtcNow;
item["deleted"] = false;
using (var db = DbProvider.GetDatabase(this.Database))
{
string columns = string.Join
(",",
skipPrimaryKey
? item.Where(x => !x.Key.ToUnderscoreLowerCase().Equals(this.PrimaryKey))
.Select(x => Sanitizer.SanitizeIdentifierName(x.Key).ToUnderscoreLowerCase())
: item.Select(x => Sanitizer.SanitizeIdentifierName(x.Key).ToUnderscoreLowerCase()));
string parameters = string.Join(",",
Enumerable.Range(0, skipPrimaryKey ? item.Count - 1 : item.Count).Select(x => "@" + x));
var arguments = skipPrimaryKey
? item.Where(x => !x.Key.ToUnderscoreLowerCase().Equals(this.PrimaryKey))
.Select(x => x.Value).ToArray()
: item.Select(x => x.Value).ToArray();
var sql = new Sql("INSERT INTO " + this.FullyQualifiedObjectName + "(" + columns + ")");
sql.Append("SELECT " + parameters, arguments);
sql.Append(FrapidDbServer.AddReturnInsertedKey(this.Database, this.PrimaryKey));
var primaryKeyValue = await db.ScalarAsync <object>(sql).ConfigureAwait(false);
await this.AddCustomFieldsAsync(primaryKeyValue, customFields).ConfigureAwait(false);
return(primaryKeyValue);
}
}
public async Task CreateDbAsync(string tenant)
{
string sql = "CREATE DATABASE {0} WITH ENCODING='UTF8' TEMPLATE=template0 LC_COLLATE='C' LC_CTYPE='C';";
sql = string.Format(CultureInfo.InvariantCulture, sql, Sanitizer.SanitizeIdentifierName(tenant.ToLower()));
string database = Factory.GetMetaDatabase(tenant);
string connectionString = FrapidDbServer.GetSuperUserConnectionString(tenant, database);
await Factory.ExecuteAsync(connectionString, tenant, sql).ConfigureAwait(false);
}
public void CreateDb(string tenant)
{
string sql = "CREATE DATABASE {0} WITH ENCODING='UTF8' TEMPLATE=template0 LC_COLLATE='C' LC_CTYPE='C';";
sql = string.Format(CultureInfo.InvariantCulture, sql, Sanitizer.SanitizeIdentifierName(tenant.ToLower()));
string database = Factory.MetaDatabase;
string connectionString = FrapidDbServer.GetSuperUserConnectionString(database);
Factory.Execute(connectionString, sql);
}
public void CreateDb(string tenant)
{
string sql = "CREATE DATABASE [{0}];";
sql = string.Format(CultureInfo.InvariantCulture, sql, Sanitizer.SanitizeIdentifierName(tenant.ToLower()));
string database = Factory.MetaDatabase;
string connectionString = FrapidDbServer.GetSuperUserConnectionString(database);
Factory.Execute(connectionString, sql);
}
private void InitializeRelation()
{
string relation = Helpers.Expression.GetExpressionValue(this.Config.DisplayViews,
this.FieldConfig.ParentTableSchema, this.FieldConfig.ParentTable, this.FieldConfig.ParentTableColumn);
this.Schema = relation.Split('.').First();
this.View = relation.Split('.').Last();
//Sanitize the schema and the view
this.Schema = Sanitizer.SanitizeIdentifierName(this.Schema);
this.View = Sanitizer.SanitizeIdentifierName(this.View);
}
public async Task UpdateAsync(Dictionary <string, object> item, object primaryKeyValue,
List <CustomField> customFields)
{
if (string.IsNullOrWhiteSpace(this.Database))
{
return;
}
if (!this.SkipValidation)
{
if (!this.Validated)
{
await this.ValidateAsync(AccessTypeEnum.Edit, this.LoginId, this.Database, false).ConfigureAwait(false);
}
if (!this.HasAccess)
{
Log.Information(
$"Access to edit entity \"{this.FullyQualifiedObjectName}\" with Primary Key {this.PrimaryKey} was denied to the user with Login ID {this.LoginId}.");
throw new UnauthorizedException("Access is denied.");
}
}
item = this.Crypt(item);
item["audit_user_id"] = this.UserId;
item["audit_ts"] = DateTimeOffset.UtcNow;
item["deleted"] = false;
using (var db = DbProvider.GetDatabase(this.Database))
{
var sql = new Sql("UPDATE " + this.FullyQualifiedObjectName + " SET");
int index = 0;
foreach (var prop in item.Where(x => !x.Key.Equals(this.PrimaryKey)))
{
if (index > 0)
{
sql.Append(",");
}
sql.Append(Sanitizer.SanitizeIdentifierName(prop.Key) + "=@0", prop.Value);
index++;
}
sql.Where(this.PrimaryKey + "=@0", primaryKeyValue);
await db.NonQueryAsync(sql).ConfigureAwait(false);
await this.AddCustomFieldsAsync(primaryKeyValue, customFields).ConfigureAwait(false);
}
}
private string GetLookupFieldByConvention()
{
string candidateKey = Inflector.MakeSingular(this.GetTableByConvention());
if (!string.IsNullOrWhiteSpace(candidateKey))
{
candidateKey += "_code";
}
candidateKey = candidateKey?.Replace("_code_code", "_code") ?? "";
return(Sanitizer.SanitizeIdentifierName(candidateKey));
}
private string GetCandidateKeyByConvention()
{
string candidateKey = Inflector.MakeSingular(this.GetTableByConvention());
if (!string.IsNullOrWhiteSpace(candidateKey))
{
candidateKey += "_id";
}
candidateKey = candidateKey ?? "";
return(Sanitizer.SanitizeIdentifierName(candidateKey));
}
private string GetLookupField()
{
string candidateKey = Inflector.MakeSingular(this._ObjectName);
if (!string.IsNullOrWhiteSpace(candidateKey))
{
candidateKey += "_code";
}
candidateKey = candidateKey ?? "";
return(Sanitizer.SanitizeIdentifierName(candidateKey));
}
private bool CreateDb()
{
string sql = "CREATE DATABASE {0} WITH ENCODING='UTF8' TEMPLATE=template0 LC_COLLATE='C' LC_CTYPE='C';";
sql = string.Format(CultureInfo.InvariantCulture, sql, Sanitizer.SanitizeIdentifierName(this.Catalog.ToLower()));
string catalog = Factory.MetaDatabase;
string connectionString = ConnectionString.GetSuperUserConnectionString(catalog);
using (var command = new NpgsqlCommand(sql))
{
return(DbOperation.ExecuteNonQuery(this.Catalog, command, connectionString));
}
}
/// <summary>
/// Creates item selector html anchor which basically is an extender of the control. The
/// extender is an html image button which, when clicked, will open a popup window which
/// allows selection, filtering, search, etc. on the target table.
/// </summary>
/// <param name="catalog"></param>
/// <param name="associatedControlId">ClientID of the DropDownList control to which this control is associated to.</param>
/// <param name="itemSelectorPath">Item Selector Target Url</param>
/// <param name="targetTableSchema">Target Table Schema</param>
/// <param name="targetTableName">Target Table</param>
/// <param name="targetTableColumn"></param>
/// <param name="currentColumn"></param>
/// <param name="displayViews">Scrud DisplayView Expressions</param>
/// <param name="resourceClassName">The resource class name containing localization.</param>
/// <param name="columnNameLocalized">Localized name of the column to which item selector is bound to.</param>
/// <param name="currentSchema"></param>
/// <param name="currentTable"></param>
/// <param name="useLocalColumnInDisplayViews"></param>
/// <returns></returns>
private static HtmlAnchor GetItemSelector(string catalog, string associatedControlId, string itemSelectorPath,
string targetTableSchema, string targetTableName, string targetTableColumn, string currentSchema,
string currentTable, string currentColumn, string displayViews, string resourceClassName,
string columnNameLocalized, bool useLocalColumnInDisplayViews)
{
if (string.IsNullOrWhiteSpace(displayViews))
{
return(null);
}
using (HtmlAnchor itemSelectorAnchor = new HtmlAnchor())
{
//string relation = string.Empty;
//Get the expression value of display view from comma separated list of expressions.
//The expression must be a valid fully qualified table or view name.
string viewRelation = GetExpressionValue(catalog, displayViews, targetTableSchema, targetTableName,
targetTableColumn, currentSchema, currentTable, currentColumn, useLocalColumnInDisplayViews);
string schema = viewRelation.Split('.').First();
string view = viewRelation.Split('.').Last();
var currentPage = HttpContext.Current.Request.Url.AbsolutePath;
//Sanitize the schema and the view
schema = Sanitizer.SanitizeIdentifierName(schema);
view = Sanitizer.SanitizeIdentifierName(view);
if (string.IsNullOrWhiteSpace(schema) || string.IsNullOrWhiteSpace(view))
{
return(null);
}
itemSelectorAnchor.Attributes["class"] = DbConfig.GetScrudParameter(catalog,
"ItemSelectorAnchorCssClass");
itemSelectorAnchor.Attributes.Add("role", "item-selector");
itemSelectorAnchor.Attributes.Add("tabindex", "10000");
itemSelectorAnchor.Attributes.Add("data-title", columnNameLocalized);
itemSelectorAnchor.HRef = itemSelectorPath + "?Schema=" + schema + "&View=" + view +
"&AssociatedControlId=" + associatedControlId + "&ResourceClassName=" +
resourceClassName + "&OverridePath=" + currentPage;
return(itemSelectorAnchor);
}
}
public ViewRepository(string schemaName, string tableName, string database, long loginId, int userId)
{
this._ObjectNamespace = Sanitizer.SanitizeIdentifierName(schemaName);
this._ObjectName = Sanitizer.SanitizeIdentifierName(tableName.Replace("-", "_"));
this.LoginId = AppUsers.GetCurrent().LoginId;
this.OfficeId = AppUsers.GetCurrent().OfficeId;
this.UserId = AppUsers.GetCurrent().UserId;
this.Database = database;
this.LoginId = loginId;
this.UserId = userId;
if (!string.IsNullOrWhiteSpace(this._ObjectNamespace) && !string.IsNullOrWhiteSpace(this._ObjectName))
{
this.FullyQualifiedObjectName = this._ObjectNamespace + "." + this._ObjectName;
this.PrimaryKey = this.GetCandidateKeyByConvention();
this.NameColumn = this.GetNameColumnByConvention();
this.IsValid = true;
}
}
/// <summary>
/// Creates item selector html anchor which basically is an extender of the control. The
/// extender is an html image button which, when clicked, will open a popup window which
/// allows selection, filtering, search, etc. on the target table.
/// </summary>
/// <param name="associatedControlId">ClientID of the DropDownList control to which this control is associated to.</param>
/// <param name="itemSelectorPath">Item Selector Target Url</param>
/// <param name="tableSchema">Target Table Schema</param>
/// <param name="tableName">Target Table</param>
/// <param name="tableColumn"></param>
/// <param name="displayViews">Scrud DisplayView Expressions</param>
/// <param name="assembly">Target Assembly Name which contains localization resources of the table.</param>
/// <param name="resourceClassName">The resource class name containing localization.</param>
/// <param name="columnNameLocalized">Localized name of the column to which item selector is bound to.</param>
/// <returns></returns>
private static HtmlAnchor GetItemSelector(string associatedControlId, string itemSelectorPath, string tableSchema, string tableName, string tableColumn, string displayViews, Assembly assembly, string resourceClassName, string columnNameLocalized)
{
if (string.IsNullOrWhiteSpace(displayViews))
{
return(null);
}
using (HtmlAnchor itemSelectorAnchor = new HtmlAnchor())
{
//string relation = string.Empty;
//Get the expression value of display view from comma separated list of expressions.
//The expression must be a valid fully qualified table or view name.
string viewRelation = GetExpressionValue(displayViews, tableSchema, tableName, tableColumn);
string schema = viewRelation.Split('.').First();
string view = viewRelation.Split('.').Last();
//Sanitize the schema and the view
schema = Sanitizer.SanitizeIdentifierName(schema);
view = Sanitizer.SanitizeIdentifierName(view);
if (string.IsNullOrWhiteSpace(schema) || string.IsNullOrWhiteSpace(view))
{
return(null);
}
itemSelectorAnchor.Attributes["class"] = ConfigurationHelper.GetScrudParameter("ItemSelectorAnchorCssClass");
itemSelectorAnchor.Attributes.Add("role", "item-selector");
itemSelectorAnchor.Attributes.Add("tabindex", "10000");
itemSelectorAnchor.Attributes.Add("data-title", columnNameLocalized);
itemSelectorAnchor.HRef = itemSelectorPath + "?Schema=" + schema + "&View=" + view +
"&AssociatedControlId=" + associatedControlId + "&Assembly=" +
assembly.GetName().Name + "&ResourceClassName=" + resourceClassName;
return(itemSelectorAnchor);
}
}
public FormRepository(string schemaName, string tableName, string database, long loginId, int userId)
{
var me = AppUsers.GetCurrentAsync().Result;
this._ObjectNamespace = Sanitizer.SanitizeIdentifierName(schemaName);
this._ObjectName = Sanitizer.SanitizeIdentifierName(tableName.Replace("-", "_"));
this.LoginId = me.LoginId;
this.OfficeId = me.OfficeId;
this.UserId = me.UserId;
this.Database = database;
this.LoginId = loginId;
this.UserId = userId;
if (!string.IsNullOrWhiteSpace(this._ObjectNamespace) &&
!string.IsNullOrWhiteSpace(this._ObjectName))
{
this.FullyQualifiedObjectName = this._ObjectNamespace + "." + this._ObjectName;
this.PrimaryKey = this.GetCandidateKey();
this.LookupField = this.GetLookupField();
this.NameColumn = this.GetNameColumn();
this.IsValid = true;
}
}
public async Task <List <object> > BulkImportAsync(List <Dictionary <string, object> > items)
{
if (!this.SkipValidation)
{
if (!this.Validated)
{
await this.ValidateAsync(AccessTypeEnum.ImportData, this.LoginId, this.Database, false).ConfigureAwait(false);
}
if (!this.HasAccess)
{
Log.Information(
$"Access to import entity \"{this.FullyQualifiedObjectName}\" was denied to the user with Login ID {this.LoginId}.");
throw new UnauthorizedException("Access is denied.");
}
}
var result = new List <object>();
int line = 0;
using (var db = DbProvider.GetDatabase(this.Database))
{
try
{
await db.BeginTransactionAsync().ConfigureAwait(false);
items = this.Crypt(items);
foreach (var item in items)
{
line++;
item["audit_user_id"] = this.UserId;
item["audit_ts"] = DateTimeOffset.UtcNow;
item["deleted"] = false;
var primaryKeyValue = item[this.PrimaryKey];
if (primaryKeyValue != null)
{
result.Add(primaryKeyValue);
var sql = new Sql("UPDATE " + this.FullyQualifiedObjectName + " SET");
int index = 0;
foreach (var prop in item.Where(x => !x.Key.Equals(this.PrimaryKey)))
{
if (index > 0)
{
sql.Append(",");
}
sql.Append(Sanitizer.SanitizeIdentifierName(prop.Key) + "=@0", prop.Value);
index++;
}
sql.Where(this.PrimaryKey + "=@0", primaryKeyValue);
await db.NonQueryAsync(sql).ConfigureAwait(false);
}
else
{
string columns = string.Join(",",
item.Where(x => !x.Key.Equals(this.PrimaryKey))
.Select(x => Sanitizer.SanitizeIdentifierName(x.Key)));
string parameters = string.Join(",",
Enumerable.Range(0, item.Count - 1).Select(x => "@" + x));
var arguments =
item.Where(x => !x.Key.Equals(this.PrimaryKey)).Select(x => x.Value).ToArray();
var sql = new Sql("INSERT INTO " + this.FullyQualifiedObjectName + "(" + columns + ")");
sql.Append("SELECT " + parameters, arguments);
sql.Append(FrapidDbServer.AddReturnInsertedKey(this.Database, this.PrimaryKey));
result.Add(await db.ScalarAsync <object>(sql).ConfigureAwait(false));
}
}
db.CommitTransaction();
return(result);
}
catch (Exception ex)
{
db.RollbackTransaction();
string errorMessage = $"Error on line {line}. {ex.Message} ";
throw new DataAccessException(errorMessage, ex);
}
}
}
public string AddReturnInsertedKey(string primaryKeyName)
{
return($"RETURNING {Sanitizer.SanitizeIdentifierName(primaryKeyName)}");
}
public static void AddFilters <T>(ref Sql sql, T poco, List <Filter> filters)
{
if (filters == null || filters.Count().Equals(0))
{
return;
}
foreach (Filter filter in filters)
{
if (string.IsNullOrWhiteSpace(filter.ColumnName))
{
if (!string.IsNullOrWhiteSpace(filter.PropertyName))
{
filter.ColumnName = PocoHelper.GetColumnName(poco, filter.PropertyName);
}
}
string column = Sanitizer.SanitizeIdentifierName(filter.ColumnName);
if (string.IsNullOrWhiteSpace(column) || !PocoHelper.HasColumn(poco, filter.ColumnName))
{
continue;
}
var statement = filter.FilterStatement;
if (statement == null || statement.ToUpperInvariant() != "OR")
{
statement = "AND";
}
statement += " ";
switch ((FilterCondition)filter.FilterCondition)
{
case FilterCondition.IsEqualTo:
sql.Append(statement + Sanitizer.SanitizeIdentifierName(column) + " = @0", filter.FilterValue);
break;
case FilterCondition.IsNotEqualTo:
sql.Append(statement + Sanitizer.SanitizeIdentifierName(column) + " != @0", filter.FilterValue);
break;
case FilterCondition.IsLessThan:
sql.Append(statement + Sanitizer.SanitizeIdentifierName(column) + " < @0", filter.FilterValue);
break;
case FilterCondition.IsLessThanEqualTo:
sql.Append(statement + Sanitizer.SanitizeIdentifierName(column) + " <= @0", filter.FilterValue);
break;
case FilterCondition.IsGreaterThan:
sql.Append(statement + Sanitizer.SanitizeIdentifierName(column) + " > @0", filter.FilterValue);
break;
case FilterCondition.IsGreaterThanEqualTo:
sql.Append(statement + Sanitizer.SanitizeIdentifierName(column) + " >= @0", filter.FilterValue);
break;
case FilterCondition.IsBetween:
sql.Append(statement + Sanitizer.SanitizeIdentifierName(column) + " BETWEEN @0 AND @1", filter.FilterValue, filter.FilterAndValue);
break;
case FilterCondition.IsNotBetween:
sql.Append(statement + Sanitizer.SanitizeIdentifierName(column) + " NOT BETWEEN @0 AND @1", filter.FilterValue, filter.FilterAndValue);
break;
case FilterCondition.IsLike:
sql.Append(statement + " lower(" + Sanitizer.SanitizeIdentifierName(column) + ") LIKE @0",
"%" + filter.FilterValue.ToLower(CultureManager.GetCurrent()) + "%");
break;
case FilterCondition.IsNotLike:
sql.Append(statement + " lower(" + Sanitizer.SanitizeIdentifierName(column) + ") NOT LIKE @0",
"%" + filter.FilterValue.ToLower(CultureManager.GetCurrent()) + "%");
break;
}
}
}
public static long GetTotalPages <T>(string catalog, T poco, string tableName, List <Filter> filters,
bool byOffice, int officeId,
bool showall, long pageSize)
{
Sql sql = Sql.Builder.Append("SELECT ceiling(COUNT(*) / @0::numeric) FROM " + Sanitizer.SanitizeIdentifierName(tableName), pageSize);
sql.Append("WHERE 1 = 1");
if (byOffice)
{
sql.Append("AND office_id IN (SELECT * FROM office.get_office_ids(@0))", officeId);
}
AddFilters(ref sql, poco, filters);
return(Factory.Scalar <long>(catalog, sql));
}
