public static SelectQuery GroupedJoin(this SelectQuery query, Table joinTable, string joinField, string foreignColumnTableAlias, string foreignColumnField, List <TableJoin> groupedJoins, JoinType joinType = JoinType.Join, string extra = null)
{
var column = new JoinColumn(foreignColumnTableAlias, foreignColumnField);
query.GroupedJoin(joinTable, joinField, column, groupedJoins, joinType, extra);
return(query);
}
public static List <TableJoin> Join(this List <TableJoin> list, Table joinTable, string joinField, Table foreignTable, string foreignColumnField, JoinType joinType = JoinType.Join, string extra = null)
{
var column = new JoinColumn(foreignTable.Alias, foreignColumnField);
list.Join(joinTable, joinField, column, joinType, extra);
return(list);
}
public static SelectQuery Join(this SelectQuery query, Table joinTable, string joinField, Table foreignTable, string foreignColumnField, JoinType joinType = JoinType.Join, string extra = null)
{
var column = new JoinColumn(foreignTable.Alias, foreignColumnField);
query.Join(joinTable, joinField, column, joinType, extra);
return(query);
}
private BaseEntity validateEntity(BaseEntity entity)
{
List <PropertyInfo> props = EntityUtil.getDeclaredField(entity.GetType());
foreach (PropertyInfo prop in props)
{
Attribute attr = prop.GetCustomAttribute(typeof(JoinColumn));
if (attr != null)
{
JoinColumn joinColumn = (JoinColumn)attr;
string joinColumnName = joinColumn.Name;
object propValue = prop.GetValue(entity);
PropertyInfo idFieldOfJoinColumn = EntityUtil.getIdField(propValue.GetType());
object id = idFieldOfJoinColumn.GetValue(propValue);
PropertyInfo entity_fk = EntityUtil.getSingleDeclaredField(entity.GetType(), joinColumnName);
entity_fk.SetValue(entity, id);
prop.SetValue(entity, null);
}
}
return(entity);
}
public void ToStringTest()
{
EntityName tableName = "tableName";
ItemName columnName = "columnName";
JoinColumn joinColumn = new JoinColumn(tableName, columnName);
Assert.AreEqual($"{tableName}.{columnName}", joinColumn.ToString());
}
public void Constructor()
{
EntityName tableName = "tableName";
ItemName columnName = "columnName";
JoinColumn joinColumn = new JoinColumn(tableName, columnName);
Assert.AreEqual(tableName, joinColumn.TableName);
Assert.AreEqual(columnName, joinColumn.ColumnName);
}
public void Equatable()
{
EntityName tableName = "tableName";
ItemName columnName = "columnName";
JoinColumn joinColumn1 = new JoinColumn(tableName, columnName);
JoinColumn joinColumn2 = new JoinColumn(tableName, columnName);
Assert.AreEqual(joinColumn1, joinColumn2);
Assert.IsTrue(joinColumn1 == joinColumn2);
}
private static String generateOrderSql(Type entityClass, String orderType, String orderBy)
{
// set order by
PropertyInfo orderByPropertyInfo = EntityUtil.getSingleDeclaredField(entityClass, orderBy);
if (orderByPropertyInfo == null)
{
return(null);
}
String columnName = orderBy;
String tableName = entityClass.Name;
Attribute joinColumnAttr = orderByPropertyInfo.GetCustomAttribute(typeof(JoinColumn));
if (joinColumnAttr != null)
{
Type PropertyInfoClass = orderByPropertyInfo.PropertyType;
tableName = (PropertyInfoClass).Name;
try
{
JoinColumn formPropertyInfo = (JoinColumn)joinColumnAttr;
PropertyInfo PropertyInfoPropertyInfo = PropertyInfoClass.GetProperty(formPropertyInfo.Converter);
columnName = formPropertyInfo.Converter;// getColumnName(PropertyInfoPropertyInfo);
if (null == columnName)
{
return(null);
}
}
catch (Exception e)
{
return(null);
}
}
else
{
columnName = orderBy;
}
String orderPropertyInfo = "[" + tableName + "].[" + columnName + "]";
return(" ORDER BY " + orderPropertyInfo + " " + orderType);
}
public static List <TableJoin> Join(this List <TableJoin> list, Table table, string joinField, JoinColumn foreignColumn, JoinType joinType = JoinType.Join, string extra = null)
{
var join = new TableJoin()
{
JoinType = joinType,
JoinTable = table,
JoinField = new Field(joinField),
ForeignColumn = foreignColumn,
Extra = extra
};
list.Add(join);
return(list);
}
public static SelectQuery Join(this SelectQuery query, Table table, string joinField, JoinColumn foreignColumn, JoinType joinType = JoinType.Join, string extra = null)
{
var join = new TableJoin()
{
JoinType = joinType,
JoinTable = table,
JoinField = new Field(joinField),
ForeignColumn = foreignColumn,
Extra = extra
};
query.TableJoins.Add(join);
return(query);
}
public static SelectQuery LeftJoin(this SelectQuery query, Table table, string joinField, JoinColumn foreignColumn, string extra = null)
{
query.Join(table, joinField, foreignColumn, JoinType.LeftJoin, extra);
return(query);
}
public void Constructor_NullColumnName()
{
EntityName tableName = "tableName";
JoinColumn joinColumn = new JoinColumn(tableName, null);
}
public void Constructor_NullTableName()
{
ItemName columnName = "columnName";
JoinColumn joinColumn = new JoinColumn(null, columnName);
}
private static String createFilterSQL(Type entityClass, Dictionary <String, Object> filter,
bool exacts)
{
String tableName = entityClass.Name;
List <String> filters = new List <String>();
List <PropertyInfo> PropertyInfos = EntityUtil.getDeclaredField(entityClass);
foreach (String rawKey in filter.Keys)
{
String key = rawKey;
if (filter[rawKey] == null)
{
continue;
}
bool itemExacts = exacts;
bool itemContains = exacts == false;
if (rawKey.EndsWith("[EXACTS]"))
{
itemExacts = true;
itemContains = false;
key = rawKey.Split("[EXACTS]".ToCharArray())[0];
}
char charz = ',';
String[] multiKey = key.Split(charz);
bool isMultiKey = multiKey.Length > 1;
if (isMultiKey)
{
key = multiKey[0];
}
String columnName = key;
// check if date
bool dayFilter = key.EndsWith("-day");
bool monthFilter = key.EndsWith("-month");
bool yearFilter = key.EndsWith("-year");
if (dayFilter || monthFilter || yearFilter)
{
String PropertyInfoName = key;
String mode = "DAY";
String sqlitem = " $MODE([$TABLE_NAME].[$COLUMN_NAME]) = $VALUE ";
if (dayFilter)
{
PropertyInfoName = key.Replace("-day", "");
mode = "DAY";
}
else if (monthFilter)
{
PropertyInfoName = key.Replace("-month", "");
mode = "MONTH";
}
else if (yearFilter)
{
PropertyInfoName = key.Replace("-year", "");
mode = "YEAR";
}
PropertyInfo prop = getPropertyInfoByName(PropertyInfoName, PropertyInfos);
if (prop == null)
{
continue;
}
columnName = prop.Name;
sqlitem = sqlitem.Replace("$TABLE_NAME", tableName).Replace("$MODE", mode)
.Replace("$COLUMN_NAME", columnName).Replace("$VALUE", filter[key].ToString());
filters.Add(sqlitem);
continue;
}
PropertyInfo prop2 = getPropertyInfoByName(key, PropertyInfos);
if (prop2 == null)
{
continue;
}
if (prop2.GetCustomAttribute(typeof(Column)) != null)
{
columnName = prop2.Name;
}
String sqlItem = " [" + tableName + "].[" + columnName + "] ";
if (prop2.GetCustomAttribute(typeof(JoinColumn)) != null || isMultiKey)
{
Type PropertyInfoClass = prop2.PropertyType;
JoinColumn joinCol = (JoinColumn)prop2.GetCustomAttribute(typeof(JoinColumn));
String joinTableName = PropertyInfoClass.Name;
try
{
String referencePropertyInfoName = joinCol.Converter;
if (isMultiKey)
{
referencePropertyInfoName = multiKey[1];
}
PropertyInfo PropertyInfoPropertyInfo = EntityUtil.getSingleDeclaredField(PropertyInfoClass, referencePropertyInfoName);
String PropertyInfoColumnName = getColumnName(PropertyInfoPropertyInfo);
if (PropertyInfoColumnName == null || PropertyInfoColumnName.Equals(""))
{
PropertyInfoColumnName = key;
}
sqlItem = " [" + joinTableName + "].[" + PropertyInfoColumnName + "] ";
}
catch (Exception e)
{
continue;
}
}
// rollback key to original key
/*
* if (isMultiKey) { key = String.join(",", multiKey); if
* (rawKey.EndsWith("[EXACTS]")) { key+="[EXACTS]"; } }
*/
if (itemContains)
{
sqlItem += " LIKE '%" + filter[rawKey] + "%' ";
}
else if (itemExacts)
{
sqlItem += " = '" + filter[rawKey] + "' ";
}
filters.Add(sqlItem);
}
if (filters == null || filters.Count == 0)
{
return("");
}
return(" WHERE " + String.Join(" AND ", filters));
}
