protected virtual BangoCommand GetChildNodesCommandTemplate1(int parent_id, bool count = false, string tableAlias = null, string sort_by = null)
{
string alias = DbServiceUtility.GetTableAliasForTable(tableAlias),
columnAlias = DbServiceUtility.GetTableAliasForColumn(tableAlias);
BangoCommand cmd = new BangoCommand(MyroCommandTypes.SqlBuilder);
if (count)
{
cmd.Template = cmd.SqlBuilder.AddTemplate(string.Format(@"
SELECT count(1) total_records
FROM {0} {1}
/**where**/ "
, _model.GetTableName(), alias));
}
else
{
cmd.Template = cmd.SqlBuilder.AddTemplate(string.Format(@"
SELECT
{3}id, {3}code, {3}name_np, {3}name_en, {3}parent_id, {3}code || ' - ' || {3}name_np || ' - ' || {3}name_en as text, true as leaf
FROM {0} {2}
/**where**/
/**orderby**/"
, _model.GetTableName(), string.Empty, alias, columnAlias));
}
return(cmd);
}
public virtual BangoCommand GetSearchCommand(SearchScenario scenario, DbConnect con, BangoCommand cmd, DynamicDictionary data_param, string selectClause, string orderByClause, int page = -1, int pageSize = 20, bool count = false, string tableAlias = null, string scenarioOthers = null)
{
TableDetailAttribute tableDetail = _model.GetTableDetail();
//clear the params whic are empty or null
List <string> keys = new List <string>(data_param.KeyList.Cast <String>());
foreach (string key in keys)
{
object value = data_param.GetValue(key);
if (value == null || data_param.GetValueAsString(key).Length == 0)
{
data_param.Remove(key);
}
}
//BangoCommand cmd = GetSearchCommandTemplate(selectClause, count, tableAlias);
//cmd.Sql.AppendLine("FROM " + model.GetTableName());
IDbExpression dbExp = App.Container.GetInstance <IDbExpression>();
if (data_param.GetCount() == 0)
{
return(cmd);
}
string append = DbServiceUtility.GetTableAliasForColumn(tableAlias);
if (!(scenario == SearchScenario.TreeNode && count == false))
{
//check & adding delete flag check sql
DbServiceUtility.BindDeleteParameter(cmd, _model, tableAlias);
if (CheckClientID)
{
DbServiceUtility.BindClientIdParameter(cmd, _model, tableAlias, DisplayMasterDataFromSystem);
}
//add remaining default search criteria
cmd = BeforeBindingParameter(scenario, con, cmd, data_param, count, tableAlias);
cmd = DbServiceUtility.BindParameters(cmd, _model, data_param, tableAlias);
cmd = AfterBindingParameter(scenario, con, cmd, data_param, count, tableAlias);
//check & adding order by clause
if (count == false)
{
cmd = BeforeBindingOrderBy(scenario, con, cmd, data_param, count, tableAlias);
cmd = DbServiceUtility.BindOrderBy(cmd, orderByClause);
cmd = AfterBindingOrderBy(scenario, con, cmd, data_param, count, tableAlias);
cmd = DbServiceUtility.BindPagination(cmd, page, pageSize);
}
}
return(cmd);
}
public virtual BangoCommand GetChildNodesCommand(DbConnect con, DynamicDictionary data_param, int?parent_id, string sort_by = null)
{
sort_by = sort_by == null ? string.Empty : sort_by;
if (sort_by.Length == 0 && TableDetail.OrderByField.Length > 0)
{
sort_by = DbServiceUtility.GetTableAliasForColumn(TableDetail.Alias) + TableDetail.OrderByField;
}
BangoCommand cmd = GetTreeNodeCommandTemplate(Convert.ToInt32(parent_id), false, TableDetail.Alias, sort_by);
return(GetSearchCommand(SearchScenario.ChildNodes, con, cmd, data_param, GetAllFields()
, sort_by, -1, -1, false, TableDetail.Alias));
}
public virtual BangoCommand GetTreeNodeCommandTemplate(int start_node_id, bool count = false, string tableAlias = null, string sort_by = null)
{
string alias = DbServiceUtility.GetTableAliasForTable(tableAlias),
columnAlias = DbServiceUtility.GetTableAliasForColumn(tableAlias);
BangoCommand cmd = new BangoCommand(MyroCommandTypes.SqlBuilder);
string start_condition = string.Empty;
TModel model = new TModel();
string secondary_condition = string.Empty;
PropertyInfo delFlag = Models.ModelService.GetDeleteFieldProperty(model);
if (delFlag != null)
{
start_condition = "c." + delFlag.Name + " = false";
}
PropertyInfo field_client_id = model.GetType().GetProperty("client_id");
if (field_client_id != null)
{
start_condition += string.Format(" AND (c.client_id = 1 OR c.client_id = {0})", SessionData.client_id);
}
secondary_condition = start_condition;
if (start_node_id == 0)
{
start_condition += string.Format(" AND COALESCE(c.parent_id,0) = {0}", start_node_id);
}
else
{
start_condition += string.Format(" AND c.id = {0}", start_node_id);
}
if (count)
{
cmd.Template = cmd.SqlBuilder.AddTemplate(string.Format(@"
SELECT count(1) total_records
FROM {0} {1}
/**where**/ "
, _model.GetTableName(), alias));
}
else
{
cmd.Template = cmd.SqlBuilder.AddTemplate(string.Format(@"
WITH RECURSIVE nodes(id, code, name_np, name_en, parent_id
, node_path, parent_node_path, node_level) as(
SELECT
{3}id, {3}code, {3}name_np, {3}name_en, {3}parent_id
--, null::text parent_code, null::text parent_name_np, null::text parent_name_en
--, c.parent_id, p.code parent_code, p.name_np parent_name_np, p.name_en parent_name_en
, ARRAY[{3}id], ARRAY[{3}id], 1
FROM {0} {2}
WHERE
{1}
UNION ALL
SELECT
{3}id, {3}code, {3}name_np, {3}name_en, {3}parent_id
--, p.code parent_code, p.name_np parent_name_np, p.name_en parent_name_en
, node_path || c.id, node_path, nd.node_level + 1
FROM {0} {2}
JOIN {0} p ON c.parent_id = p.id
JOIN nodes nd ON p.id = nd.id
WHERE
{4}
)
SELECT array_to_string(node_path , '-') node_path, array_to_string(parent_node_path , '-') parent_node_path, id node_id, code, name_np
, name_en, parent_id, node_level , code || ' - ' || name_np || ' - ' || name_en as text
FROM nodes
order by node_path
/**where**/
/**orderby**/"
, _model.GetTableName(), start_condition, alias, columnAlias, secondary_condition));
}
return(cmd);
}