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); }