protected virtual PropertyColumnMap ExcludeColumnsForUpdate(PropertyColumnMap mapping)
{
return(new PropertyColumnMap(
mapping
.Where(x => x.Key != Schema.Columns.KeyProperty)
.ToDictionary(x => x.Key, x => x.Value)));
}
protected virtual string GetInsertStatement(PropertyColumnMap mapping)
{
return(FormatSqlStatement(
$@"
INSERT INTO { Schema.TableName }
{ GetColumnValueList(GetColumnParameterMap(mapping), insert: true) }
"));
}
protected virtual string GetOrderByClause(PropertyColumnMap mapping)
{
if (string.IsNullOrWhiteSpace(Schema.DefaultOrderBy.propertyName))
{
return(string.Empty);
}
return($"ORDER BY { mapping[Schema.DefaultOrderBy.propertyName].QualifiedColumnName(Schema) } { (Schema.DefaultOrderBy.direction == OrderDirection.Ascending ? "ASC" : "DESC") }");
}
protected virtual Dictionary <string, string> GetColumnParameterMap(PropertyColumnMap mapping)
{
return(ExcludeComputedColumnsForUpdateAndInserts(mapping)
.ToDictionary(
x => x.Value.ColumnName,
x => GetTypeCastForDynamicParameter(
FormatDynamicParameter(x.Key),
x.Value.NonStandardDbType)));
}
protected virtual string GetSelectClause(PropertyColumnMap mapping)
{
return
($@"
SELECT
{ GetColumnList(mapping.Values.Select(v => v.CustomSqlExpression ?? v.QualifiedColumnName(Schema))) }
FROM { Schema.TableName } { Schema.TableAlias }
{ string.Join(Environment.NewLine, Schema.Joins.Select(j => j.JoinExpression)) }
");
}
protected virtual string GetSelectClause(PropertyColumnMap mapping, QueryParameters queryParams)
{
return
($@"
SELECT
{ GetColumnList(mapping.Values.Select(v => v.OnSelect?.Invoke(queryParams) ?? v.QualifiedColumnNameForSelect(Schema))) }
FROM { Schema.TableName } { Schema.TableAlias }
{ string.Join(Environment.NewLine, Schema.Joins.Select(j => j.JoinExpression)) }
");
}
protected virtual string GetWhereClause(PropertyColumnMap mapping, Predicate predicate, QueryParameters queryParams)
{
var sqlWhere = predicate?.Accept(
new PostgresPredicateVisitor(
Schema,
queryParams));
return(sqlWhere != null
? $"WHERE { sqlWhere }"
: string.Empty);
}
protected virtual string GetUpdateStatement(PropertyColumnMap mapping)
{
var columnsParameterMap = GetColumnParameterMap(mapping);
var updateColumnsParameterMap = GetColumnParameterMap(ExcludeColumnsForUpdate(mapping));
var keyColumn = Schema.Columns.Mapping[Schema.Columns.KeyProperty].ColumnName;
return(FormatSqlStatement(
$@"
UPDATE { Schema.TableName } { Schema.TableAlias }
SET
{ GetColumnValueList(updateColumnsParameterMap, insert: false) }
WHERE { keyColumn } = { columnsParameterMap[keyColumn] }
"));
}
public static void MapToProperties(ListViewItem aItem, MapedObject aMap, bool ruleBased)
{
PropertyColumnMap aPair = new PropertyColumnMap();
if (!ruleBased)
{
aPair.ColumnName = aItem.SubItems[0].Text;
}
else
{
aPair.ColumnName = aItem.SubItems[1].Text;
}
aPair.RuleBased = ruleBased;
aPair.PropertyName = aItem.SubItems[1].Text;
aPair.DataType = aItem.SubItems[2].Text;
aPair.IsNullable = bool.Parse(aItem.SubItems[4].Text);
aMap.PropMaps.Add(aPair);
}
protected virtual string GetUpsertStatement(PropertyColumnMap mapping, OnConflict onConflict, QueryParameters queryParameters)
{
var insertColumnExpressions = GetInsertStatement(mapping, queryParameters);
var updateColumnsMapping = ExcludeColumnsForUpdate(mapping);
var updateColumnExpressions = GetColumnValueList(
GetColumnParameterMap(updateColumnsMapping, queryParameters)
.ToDictionary(x => x.Key, x => $"EXCLUDED.{x.Key}"), insert: false);
var keyColumn = Schema.Columns.Mapping[Schema.Columns.KeyProperty].ColumnName;
var sql = insertColumnExpressions;
var onConflictExpression = $"ON CONFLICT({ keyColumn })";
switch (onConflict)
{
case OnConflict.Update:
sql += $@"
{ onConflictExpression }
DO UPDATE
SET
{ updateColumnExpressions }
";
break;
case OnConflict.DoNothing:
sql += $@"
{ onConflictExpression }
DO NOTHING";
break;
default:
break;
}
return(FormatSqlStatement(sql));
}
public void UpdateData()
{
if (User.Length > 0)
{
IEnumerable <Aircraft> rgac = new UserAircraft(User).GetAircraftForUser();
AircraftForUser.Clear();
foreach (Aircraft ac in rgac)
{
AircraftForUser[ac.AircraftID] = ac;
}
IEnumerable <LogbookEntryDisplay> rgle = LogbookEntryDisplay.GetFlightsForQuery(LogbookEntryDisplay.QueryCommand(Restriction), User, "Date", SortDirection.Descending, false, false);
gvFlightLogs.DataSource = rgle;
// See whether or not to show catclassoverride column
bool fShowAltCatClass = false;
foreach (LogbookEntryDisplay le in rgle)
{
fShowAltCatClass |= le.IsOverridden;
}
if (!fShowAltCatClass)
{
foreach (DataControlField dcf in gvFlightLogs.Columns)
{
if (dcf.HeaderText.CompareCurrentCultureIgnoreCase("Alternate Cat/Class") == 0)
{
gvFlightLogs.Columns.Remove(dcf);
break;
}
}
}
// Generate the set of properties used by the user
int cColumns = gvFlightLogs.Columns.Count;
PropertyColumnMap.Clear();
HashSet <CustomPropertyType> hscpt = new HashSet <CustomPropertyType>();
foreach (LogbookEntryBase le in rgle)
{
foreach (CustomFlightProperty cfp in le.CustomProperties)
{
if (!hscpt.Contains(cfp.PropertyType))
{
hscpt.Add(cfp.PropertyType);
}
}
}
// Now sort that alphabetically and add them
List <CustomPropertyType> lst = new List <CustomPropertyType>(hscpt);
lst.Sort((cpt1, cpt2) => { return(cpt1.Title.CompareCurrentCultureIgnoreCase(cpt2.Title)); });
foreach (CustomPropertyType cpt in lst)
{
PropertyColumnMap[cpt.PropTypeID] = cColumns++;
BoundField bf = new BoundField()
{
HeaderText = cpt.Title,
HtmlEncode = false,
DataField = string.Empty,
DataFormatString = string.Empty
};
gvFlightLogs.Columns.Add(bf);
}
if (OrderString != null && OrderString.Length > 0)
{
char[] delimit = { ',' };
string[] rgszCols = OrderString.Split(delimit);
ArrayList alCols = new ArrayList();
// identify the requested front columns
foreach (string szcol in rgszCols)
{
if (int.TryParse(szcol, NumberStyles.Integer, CultureInfo.InvariantCulture, out int col))
{
if (col < gvFlightLogs.Columns.Count)
{
alCols.Add(col);
}
}
}
int[] rgCols = (int[])alCols.ToArray(typeof(int));
// pull those columns to the left; this creates a duplicate column and shifts everything right by one...
int iCol = 0;
for (iCol = 0; iCol < rgCols.Length; iCol++)
{
gvFlightLogs.Columns.Insert(iCol, gvFlightLogs.Columns[rgCols[iCol] + iCol]);
}
// And then remove the duplicates, from right to left
Array.Sort(rgCols);
for (int i = rgCols.Length - 1; i >= 0; i--)
{
gvFlightLogs.Columns.RemoveAt(rgCols[i] + iCol);
}
}
gvFlightLogs.DataBind();
}
}
protected virtual PropertyColumnMap ExcludeComputedColumnsForUpdateAndInserts(PropertyColumnMap mapping)
{
return(new PropertyColumnMap(
mapping
.Where(x => x.Value.CustomSqlExpression == null)
.ToDictionary(x => x.Key, x => x.Value)));
}
public ByConventionSchemaColumns(string keyProperty, PropertyColumnMap mapping)
{
KeyProperty = keyProperty;
Mapping = mapping;
}
private void AddToMapList(PropertyColumnMap aMap)
{
var newPair = new ListViewItem(new string[] { aMap.ColumnName, aMap.PropertyName, aMap.DataType, aMap.IsNullable.ToString(), aMap.IsNullable.ToString() });
lstMappedPairs.Items.Add(newPair);
}