def find_relations(query): branch = g.tran.query(func.row_to_json(text('branches.*'))).select_from(db.Branches) \ .filter_by(_deleted='infinity', _id=db.Operations.branch_id).as_scalar().label('branch') purpose = g.tran.query(func.row_to_json(text('costcenterpurposes.*'))).select_from(db.CostCenterPurposes) \ .filter_by(_deleted='infinity', _id=db.Operations.purpose_id).as_scalar().label('purpose') document = g.tran.query(func.row_to_json(text('document.*'))).select_from(db.Document) \ .filter_by(_deleted='infinity', _id=db.Operations.document_id).as_scalar().label('document') product = g.tran.query(func.row_to_json(text('products.*'))).select_from(db.Products) \ .filter_by(_deleted='infinity', _id=db.Operations.product_id).as_scalar().label('product') unit = g.tran.query(func.row_to_json(text('units.*'))).select_from(db.Units) \ .filter_by(_deleted='infinity', _id=db.Operations.unit_id).as_scalar().label('unit') currency = g.tran.query(func.row_to_json(text('currencies.*'))).select_from(db.Currencies) \ .filter_by(_deleted='infinity', _id=db.Operations.currency_id).as_scalar().label('currency') operation_status = g.tran.query(func.row_to_json(text('enums.*'))).select_from(db.Enums) \ .filter_by(_deleted='infinity', name='operation_status') \ .filter(db.Enums.data['key'].cast(TEXT) == cast(db.Operations.operation_status, TEXT)) \ .as_scalar().label('operation_status_value') user = g.tran.query(func.json_build_object( "id", db.User.id, "username", db.User.username, "email", db.User.email, "rec_date", db.User.rec_date, "data", db.User.data, "role", db.User.role)).select_from(db.User).filter_by(id=db.UserCompany.user_id) \ .as_scalar().label('user') employee = g.tran.query(func.json_build_object('_id', db.UserCompany._id, '_rev', db.UserCompany._rev, '_created', db.UserCompany._created, 'user_id', db.UserCompany.user_id, 'company_id', db.UserCompany.company_id, 'entry_user_id', db.UserCompany.entry_user_id, 'branches_id', db.UserCompany.branches_id, 'roles_id', db.UserCompany.roles_id, 'access', db.UserCompany.access, 'user', user)).select_from(db.UserCompany) \ .filter_by(_deleted='infinity', company_id=db.Operations.company_id, user_id=db.Operations.employee_id)\ .as_scalar().label('employee') contractor = g.tran.query(func.row_to_json(text('contractors.*'))).select_from(db.Contractors) \ .filter_by(_deleted='infinity', _id=db.Operations.contractor_id).as_scalar().label('contractor') branch2 = g.tran.query(func.row_to_json(text('branches.*'))).select_from(db.Branches) \ .filter_by(_deleted='infinity', _id=db.Operations.from_branch_id).as_scalar().label('branch2') entry_user = g.tran.query(func.json_build_object( "id", db.User.id, "username", db.User.username, "email", db.User.email, "rec_date", db.User.rec_date, "data", db.User.data, "role", db.User.role)).select_from(db.User).filter_by(id=db.Operations.entry_user_id)\ .as_scalar().label('entry_user') query = query.add_columns(branch, purpose, document, product, unit, currency, contractor, branch2, entry_user, employee, operation_status) return query
def test_function_against_subq_record(self, axy_table, connection): """ SELECT row_to_json(anon_1) AS row_to_json_1 FROM (SELECT a.id AS id, a.x AS x, a.y AS y FROM a) AS anon_1 """ stmt = select( func.row_to_json(axy_table.select().subquery().table_valued())) eq_( connection.execute(stmt).scalars().all(), [ { "id": 1, "x": 5, "y": 4 }, { "id": 2, "x": 15, "y": 3 }, { "id": 3, "x": 7, "y": 9 }, ], )
def _get_permissions_query(self, session, identifier): """ select domain, json_agg(parts) as permissions from (select domain, row_to_json(r) as parts from (select domain, action, array_agg(distinct target) as target from (select (case when domain is null then '*' else domain end) as domain, (case when target is null then '*' else target end) as target, array_agg(distinct (case when action is null then '*' else action end)) as action from permission group by domain, target ) x group by domain, action) r) parts group by domain; """ thedomain = case([(Domain.name == None, "*")], else_=Domain.name) theaction = case([(Action.name == None, "*")], else_=Action.name) theresource = case([(Resource.name == None, "*")], else_=Resource.name) action_agg = func.array_agg(theaction.distinct()) stmt1 = ( session.query( Permission.domain_id, thedomain.label("domain"), Permission.resource_id, theresource.label("resource"), action_agg.label("action"), ) .select_from(User) .join(role_membership_table, User.pk_id == role_membership_table.c.user_id) .join(role_permission_table, role_membership_table.c.role_id == role_permission_table.c.role_id) .join(Permission, role_permission_table.c.permission_id == Permission.pk_id) .outerjoin(Domain, Permission.domain_id == Domain.pk_id) .outerjoin(Action, Permission.action_id == Action.pk_id) .outerjoin(Resource, Permission.resource_id == Resource.pk_id) .filter(User.identifier == identifier) .group_by(Permission.domain_id, Domain.name, Permission.resource_id, Resource.name) ).subquery() stmt2 = ( session.query(stmt1.c.domain, stmt1.c.action, func.array_agg(stmt1.c.resource.distinct()).label("resource")) .select_from(stmt1) .group_by(stmt1.c.domain, stmt1.c.action) ).subquery() stmt3 = ( session.query(stmt2.c.domain, func.row_to_json(as_row(stmt2)).label("parts")).select_from(stmt2) ).subquery() final = ( session.query(stmt3.c.domain, cast(func.json_agg(stmt3.c.parts), Text)) .select_from(stmt3) .group_by(stmt3.c.domain) ) return final
def get(self, bag): query = g.tran.query(db.Companies) \ .filter_by(_deleted='infinity', _id=bag['id']) doc_vars = vars(db.Companies) for var in doc_vars: if isinstance(doc_vars[var], InstrumentedAttribute): query = query.add_column(doc_vars[var]) if 'with_related' in bag and bag['with_related'] is True: company_status_value = g.tran.query( func.row_to_json(text('enums.*'))).select_from(db.Enums) \ .filter_by(_deleted='infinity', name='company_status') \ .filter(db.Enums.data['key'].cast(TEXT) == cast(db.Companies.company_status, TEXT)) \ .as_scalar().label('company_status_value') company_type_value = g.tran.query( func.row_to_json(text('enums.*'))).select_from(db.Enums) \ .filter_by(_deleted='infinity', name='company_type') \ .filter(db.Enums.data['key'].cast(TEXT) == cast(db.Companies.company_type, TEXT)) \ .as_scalar().label('company_type_value') entry_user = g.tran.query(func.json_build_object( "id", db.User.id, "username", db.User.username, "email", db.User.email, "rec_date", db.User.rec_date, "data", db.User.data, "role", db.User.role)).select_from(db.User).filter_by( id=db.Companies.entry_user_id) \ .as_scalar().label('entry_user') typeofownership = g.tran.query(func.row_to_json(text('typeofownership.*'))).select_from(db.Typeofownership) \ .filter_by(_deleted='infinity', _id=db.Companies.typeofownership_id).as_scalar() \ .label('typeofownership') dircountry = g.tran.query(func.row_to_json(text('dircountry.*'))).select_from(db.DirCountry) \ .filter_by(_deleted='infinity', _id=db.Companies.dircountry_id).as_scalar() \ .label('dircountry') dircoate = g.tran.query(func.row_to_json(text('dircoate.*'))).select_from(db.DirCoate) \ .filter_by(_deleted='infinity', _id=db.Companies.dircoate_id).as_scalar() \ .label('dircoate') roles = g.tran.query(func.jsonb_agg(func.row_to_json(text('roles.*')))).select_from(db.Roles) \ .filter_by(_deleted='infinity') \ .filter(type_coerce(db.Companies.roles_id).has_any(array([db.Roles._id]))) \ .as_scalar().label('roles') company_users = g.tran.query(db.Companyemployees.user_id).filter_by(_deleted='infinity', company_id=bag['id']).all() company_users = [] for user_id in company_users: user = g.tran.query(func.json_build_object( "id", db.User.id, "username", db.User.username, "email", db.User.email, "rec_date", db.User.rec_date, "data", db.User.data, "role", db.User.role)).select_from(db.User) \ .filter_by(id=user_id).first() company_users.append(user) query = query.add_columns(company_status_value, company_type_value, entry_user, company_users, typeofownership, dircountry, roles, dircoate) company = query.one() return {'doc': orm_to_json(company)}
def connection_block(field: ASTNode, parent_name: typing.Optional[str]) -> Alias: return_type = field.return_type sqla_model = return_type.sqla_model block_name = slugify_path(field.path) if parent_name is None: join_conditions = [True] else: join_conditions = to_join_clause(field, parent_name) filter_conditions = to_conditions_clause(field) limit = to_limit(field) has_total = check_has_total(field) is_page_after = "after" in field.args is_page_before = "before" in field.args totalCount_alias = field.get_subfield_alias(["totalCount"]) edges_alias = field.get_subfield_alias(["edges"]) node_alias = field.get_subfield_alias(["edges", "node"]) cursor_alias = field.get_subfield_alias(["edges", "cursor"]) pageInfo_alias = field.get_subfield_alias(["pageInfo"]) hasNextPage_alias = field.get_subfield_alias(["pageInfo", "hasNextPage"]) hasPreviousPage_alias = field.get_subfield_alias( ["pageInfo", "hasPreviousPage"]) startCursor_alias = field.get_subfield_alias(["pageInfo", "startCursor"]) endCursor_alias = field.get_subfield_alias(["pageInfo", "endCursor"]) # Apply Filters core_model = sqla_model.__table__ core_model_ref = ( select(core_model.c).select_from(core_model).where( and_( # Join clause *join_conditions, # Conditions *filter_conditions, ))).alias(block_name) new_edge_node_selects = [] new_relation_selects = [] for subfield in get_edge_node_fields(field): # Does anything other than NodeID go here? if subfield.return_type == ID: # elem = select([to_node_id_sql(sqla_model, core_model_ref)]).label(subfield.alias) elem = to_node_id_sql(sqla_model, core_model_ref).label(subfield.alias) new_edge_node_selects.append(elem) elif isinstance(subfield.return_type, (ScalarType, CompositeType, EnumType)): col_name = field_name_to_column(sqla_model, subfield.name).name elem = core_model_ref.c[col_name].label(subfield.alias) new_edge_node_selects.append(elem) else: elem = build_relationship(subfield, block_name) new_relation_selects.append(elem) # Setup Pagination args = field.args after_cursor = args.get("after", None) before_cursor = args.get("before", None) first = args.get("first", None) last = args.get("last", None) if first is not None and last is not None: raise ValueError('only one of "first" and "last" may be provided') pkey_cols = get_primary_key_columns(sqla_model) if after_cursor or before_cursor: local_table_name = get_table_name(field.return_type.sqla_model) cursor_table_name = before_cursor.table_name if before_cursor else after_cursor.table_name cursor_values = before_cursor.values if before_cursor else after_cursor.values if after_cursor is not None and before_cursor is not None: raise ValueError( 'only one of "before" and "after" may be provided') if after_cursor is not None and last is not None: raise ValueError( '"after" is not compatible with "last". Use "first"') if before_cursor is not None and first is not None: raise ValueError( '"before" is not compatible with "first". Use "last"') if cursor_table_name != local_table_name: raise ValueError("Invalid cursor for entity type") pagination_clause = tuple_( *[core_model_ref.c[col.name] for col in pkey_cols]).op( ">" if after_cursor is not None else "<")( tuple_(*[cursor_values[col.name] for col in pkey_cols])) else: pagination_clause = True order_clause = [ asc(core_model_ref.c[col.name]) for col in get_primary_key_columns(sqla_model) ] reverse_order_clause = [ desc(core_model_ref.c[col.name]) for col in get_primary_key_columns(sqla_model) ] total_block = (select([func.count(ONE).label("total_count")]).select_from( core_model_ref.alias()).where(has_total)).alias(block_name + "_total") node_id_sql = to_node_id_sql(sqla_model, core_model_ref) cursor_sql = to_cursor_sql(sqla_model, core_model_ref) # Select the right stuff p1_block = ( select([ *new_edge_node_selects, *new_relation_selects, # For internal Use node_id_sql.label("_nodeId"), cursor_sql.label("_cursor"), # For internal Use func.row_number().over().label("_row_num"), ]).select_from(core_model_ref).where(pagination_clause).order_by( *(reverse_order_clause if (is_page_before or last is not None) else order_clause), *order_clause).limit(cast(limit + 1, Integer()))).alias(block_name + "_p1") # Drop maybe extra row p2_block = (select([ *p1_block.c, (func.max(p1_block.c._row_num).over() > limit).label("_has_next_page") ]).select_from(p1_block).limit(limit)).alias(block_name + "_p2") ordering = (desc(literal_column("_row_num")) if (is_page_before or last is not None) else asc( literal_column("_row_num"))) p3_block = (select(p2_block.c).select_from(p2_block).order_by(ordering) ).alias(block_name + "_p3") final = (select([ func.jsonb_build_object( literal_string(totalCount_alias), func.coalesce(func.min(total_block.c.total_count), ZERO) if has_total else None, literal_string(pageInfo_alias), func.jsonb_build_object( literal_string(hasNextPage_alias), func.coalesce( func.array_agg(p3_block.c._has_next_page)[ONE], FALSE), literal_string(hasPreviousPage_alias), TRUE if is_page_after else FALSE, literal_string(startCursor_alias), func.array_agg(p3_block.c._nodeId)[ONE], literal_string(endCursor_alias), func.array_agg(p3_block.c._nodeId)[func.array_upper( func.array_agg(p3_block.c._nodeId), ONE)], ), literal_string(edges_alias), func.coalesce( func.jsonb_agg( func.jsonb_build_object( literal_string(cursor_alias), p3_block.c._nodeId, literal_string(node_alias), func.cast( func.row_to_json(literal_column(p3_block.name)), JSONB()), )), func.cast(literal("[]"), JSONB()), ), ).label("ret_json") ]).select_from(p3_block).select_from( total_block if has_total else select([1]).alias())).alias() return final
def test_function_against_row_constructor(self, connection): stmt = select(func.row_to_json(func.row(1, "foo"))) eq_(connection.scalar(stmt), {"f1": 1, "f2": "foo"})
def get(bag): advert = g.tran.query(db.Advert) \ .filter_by(_deleted='infinity', _id=bag['id']).one() advert = orm_to_json(advert) advert_lots = g.tran.query(db.Advert_lot).filter_by(_deleted='infinity') \ .filter(db.Advert_lot.advert_id == advert["_id"]) \ .order_by(db.Advert_lot._created.desc()).all() advert['company'] = g.tran.query(db.Companies).filter_by(_deleted='infinity') \ .filter(db.Companies._id == advert["company_id"]).first() advert['dirsection'] = g.tran.query(db.DirSection).filter_by(_deleted='infinity') \ .filter(db.DirSection._id == advert["dirsection_id"]).first() if advert.get("dirprocurement_id", ''): advert['dirprocurement'] = g.tran.query(db.DirProcurement).filter_by(_deleted='infinity') \ .filter(db.DirProcurement._id == advert["dirprocurement_id"]).first() lot_budget = 0 debt_data = [] companies_appl = [] lind = 0 for lot in advert_lots: lot_budget += lot.budget lot.specifications = [] lot.dictionaries = [] lot.applications = [] lind = lind + 1 lot.ind = lind dircategory = g.tran.query( db.DirCategory).filter_by(id=lot.dircategory_id).all() lot.dircategory = dircategory lot.unit_price = lot.data['unit_price'] if lot.data.get( 'unit_price', '') else 0 if lot.dirunits_id: lot.dirunit = g.tran.query(db.DirUnits) \ .filter_by(_deleted='infinity', _id=lot.dirunits_id).first() lot_dictionaries = g.tran.query(db.Advert_lot_dictionaries) \ .filter_by(_deleted='infinity').filter(db.Advert_lot_dictionaries.advert_lot_id == lot._id).all() for lot_dict in lot_dictionaries: dict = {} table = getattr(db, lot_dict.dirname) if hasattr( db, lot_dict.dirname) else None dirvalue = g.tran.query(table).filter_by( _deleted='infinity').filter( table._id == lot_dict.dictionary_id).first() dir = next(d for d in tables if d['table'] == lot_dict.dirname) dict['dirname'] = lot_dict.dirname dict['name'] = dir['name'] if dir else '' # dict['displayName'] = dir['name'] if dir else '' dict['value'] = dirvalue.name dict['values'] = [orm_to_json(dirvalue)] lot.dictionaries.append(dict) lot_specifications = g.tran.query(db.Advert_lot_specification) \ .filter_by(_deleted='infinity').filter(db.Advert_lot_specification.advert_lot_id == lot._id).all() for prodspec in lot_specifications: specs = {} property = g.tran.query(db.SpecificationProperty) \ .filter_by(id=prodspec.specification_property_id).first() value = g.tran.query(db.SpecificationPropertyValue) \ .filter_by(id=prodspec.specification_property_value_id).first() specs['property'] = property specs['value'] = value lot.specifications.append(specs) if advert['status'] in ['Evaluation', 'Results']: lot_applications = g.tran.query(db.Application, db.Companies, db.Product, (g.tran.query(db.DirCountry._id).select_from(db.ProductDict) .join(db.DirCountry, and_(db.ProductDict.dictionary_id == db.DirCountry._id, db.DirCountry._deleted == INFINITY)) .filter(and_(db.ProductDict._deleted == INFINITY, db.ProductDict.dirname == 'DirCountry', db.ProductDict.product_id == db.Application.company_product_id)) ).label('country'), (g.tran.query(db.DirBrand._id).select_from(db.ProductDict) .join(db.DirBrand, and_(db.ProductDict.dictionary_id == db.DirBrand._id, db.DirBrand._deleted == INFINITY)) .filter(and_(db.ProductDict._deleted == INFINITY, db.ProductDict.dirname == 'DirBrand', db.ProductDict.product_id == db.Application.company_product_id)) ).label('brand') ) \ .outerjoin(db.Companies, and_(db.Application.company_id == db.Companies._id, db.Companies._deleted == INFINITY)) \ .outerjoin(db.Product, and_(db.Application.company_product_id == db.Product._id, db.Product._deleted == INFINITY)) \ .filter(and_(db.Application.advert_lot_id == lot._id, db.Application._deleted == INFINITY, db.Application.status == 'Published')) \ .order_by(db.Application.total, db.Application._created) \ .all() is_first = True if 'dirprocurement' in advert and \ advert['dirprocurement'].with_concession and \ advert['dirprocurement'].with_concession is True: applications_list = [] brand_label = '' country_label = '' for appl, company, product, country, brand in lot_applications: dircountry = g.tran.query(db.DirCountry).filter_by( _deleted='infinity', _id=country).first() if g.lang == "ru": country_label = dircountry.name elif g.lang == "en": country_label = dircountry.name_en if dircountry.name_en and dircountry.name_en != 'null' \ else dircountry.name elif g.lang == "kg": country_label = dircountry.name_kg if dircountry.name_kg and dircountry.name_kg != 'null' \ else dircountry.name dirbrand = g.tran.query(db.DirBrand).filter_by( _deleted='infinity', _id=brand).first() if g.lang == "ru": brand_label = dirbrand.name elif g.lang == "en": brand_label = dirbrand.name_en if dirbrand.name_en and dirbrand.name_en != 'null' \ else dirbrand.name elif g.lang == "kg": brand_label = dirbrand.name_kg if dirbrand.name_kg and dirbrand.name_kg != 'null' \ else dirbrand.name ap = orm_to_json(appl) ap['show_reason'] = True if ap['reason'] else False ap['lot_id'] = lot._id ap['company'] = company.name if company else '' ap['country'] = country_label ap['brand'] = brand_label ap['total_concession'] = ap['total'] * ((100 - advert['concession']) / 100) \ if company.resident_state == "resident" and product.local == True else ap['total'] if lot.status != 'Canceled': applications_list.append(ap) lot.applications = sorted( applications_list, key=lambda k: ((datetime.datetime.strptime(k["_created"][:10], '%Y-%m-%d' )), k['total_concession'])) debt_data = company_debt_check(debt_data, company, advert["_id"]) else: brand_label = '' country_label = '' for appl, company, product, country, brand in lot_applications: dircountry = g.tran.query(db.DirCountry).filter_by( _deleted='infinity', _id=country).first() debt_data = company_debt_check(debt_data, company, advert["_id"]) if g.lang == "ru": country_label = dircountry.name elif g.lang == "en": country_label = dircountry.name_en if dircountry.name_en and dircountry.name_en != 'null' \ else dircountry.name elif g.lang == "kg": country_label = dircountry.name_kg if dircountry.name_kg and dircountry.name_kg != 'null' \ else dircountry.name dirbrand = g.tran.query(db.DirBrand).filter_by( _deleted='infinity', _id=brand).first() if g.lang == "ru": brand_label = dirbrand.name elif g.lang == "en": brand_label = dirbrand.name_en if dirbrand.name_en and dirbrand.name_en != 'null' \ else dirbrand.name elif g.lang == "kg": brand_label = dirbrand.name_kg if dirbrand.name_kg and dirbrand.name_kg != 'null' \ else dirbrand.name ap = orm_to_json(appl) ap['show_reason'] = True if ap['reason'] else False ap['lot_id'] = lot._id ap['company'] = company.name if company else '' ap['country'] = country_label ap['brand'] = brand_label lot.applications.append(ap) if advert['status'] in ['Results']: if lot.status in ['Canceled'] and g.lang == "ru": lot.company = 'Не состоялась' lot.total = 'Не состоялась' if lot.status in ['Canceled'] and g.lang == "en": lot.company = 'Did not take place' lot.total = 'Did not take place' if lot.status in ['Canceled'] and g.lang == "kg": lot.company = 'өткөрүлгөн жок' lot.total = 'өткөрүлгөн жок' else: application = g.tran.query(db.Application) \ .filter_by(_deleted='infinity', advert_lot_id=lot._id, selected=True).all() if application: app2, company = g.tran.query(db.Application, db.Companies) \ .join(db.Companies, and_(db.Application.company_id == db.Companies._id, db.Companies._deleted == INFINITY)) \ .filter(and_(db.Application.advert_lot_id == lot._id, db.Application._deleted == INFINITY)) \ .filter(db.Application.selected == True).first() lot.company = company.name if company else '' lot.total = app2.total if app2 else '' if 'with_related' in bag and bag['with_related'] is True: advert_status_value = g.tran.query( func.row_to_json(text('enums.*'))).select_from(db.Enums) \ .filter_by(_deleted='infinity', name='advert_status') \ .filter(db.Enums.data['key'].cast(TEXT) == cast(db.Advert.status, TEXT)) \ .as_scalar().label('advert_status_value') advert = advert.add_columns(advert_status_value) comm_members = advert['data'].get('comm_members', []) if comm_members: for cm in comm_members: u = g.tran.query(db.User).select_from(db.Companyemployees) \ .filter(db.Companyemployees.id == cm['id']).first() if u: cm[FULL_NAME] = u.fullname or '' cm[POSITION] = u.position or '' cm[EMAIL] = u.email or '' cm['company'] = u.data.get('comm_member', {}).get('company', '') advert['lots'] = advert_lots advert['debt_data'] = debt_data advert['budget'] = lot_budget return {'doc': advert}
def view_listing(bag): product = g.tran.query(db.Products._id).filter_by(_deleted='infinity') if g.company.company_type == 'agent': insurance_company = g.tran.query(db.Companies).filter_by(_deleted='infinity') \ .filter(db.Companies.agents_id.contains(type_coerce([g.company._id], JSONB))).first() product = product.filter( or_(db.Products.company_id == insurance_company._id, db.Products.company_id == g.company._id)) else: product = g.tran.query(db.Products._id).filter_by( company_id=g.company._id, _deleted='infinity') for col in db.Products.__table__.c: product = product.add_column(col) company = g.tran.query(func.jsonb_agg( func.jsonb_build_object( '_id', db.Companies._id, 'name', db.Companies.name ) )).select_from(db.Companies) \ .filter_by(_deleted='infinity').filter(db.Companies._id == db.Products.company_id).as_scalar() \ .label('company') factor = g.tran.query(func.jsonb_agg(func.row_to_json(text('factors.*')))) \ .select_from(db.Factors) \ .filter_by(_deleted='infinity').filter(db.Factors._id == db.ProductsFactor.factor_id).as_scalar() \ .label('factor') productsfactor = g.tran.query(func.jsonb_agg( func.jsonb_build_object( '_id', db.ProductsFactor._id, 'product_id', db.ProductsFactor.product_id, 'factor_id', db.ProductsFactor.factor_id, 'values', db.ProductsFactor.values, 'coefficient', db.ProductsFactor.coefficient, 'coefficient_type', db.ProductsFactor.coefficient_type, 'is_default', db.ProductsFactor.is_default, 'factor', factor ) )).select_from(db.ProductsFactor) \ .filter_by(_deleted='infinity').filter(db.ProductsFactor.product_id == db.Products._id) basefactors = g.tran.query(func.jsonb_agg(func.row_to_json(text('productsbasefactor.*')))) \ .select_from(db.ProductsBaseFactor) \ .filter_by(_deleted='infinity').filter(db.ProductsBaseFactor.product_id == db.Products._id).as_scalar() \ .label('basefactors') productsfactor = productsfactor.as_scalar() \ .label('factors') product = product.add_columns(productsfactor, basefactors, company) product = orm_to_json(product.first()) countries = orm_to_json( g.tran.query(db.Countries).filter_by(_deleted='infinity').all()) currencies = orm_to_json( g.tran.query(db.Currencies).filter_by(_deleted='infinity').all()) return { 'product': product, 'countries': countries, 'currencies': currencies }
def listing(bag): table_name = bag["type"] table = getattr(db, table_name) if hasattr(db, table_name) else None if table is None or not issubclass(table, (db.Base, db.CouchSync)): raise CbsException(TABLE_NOT_FOUND) query = g.tran.query(table._id).filter_by(_deleted='infinity') doc_vars = vars(table) for var in doc_vars: if isinstance(doc_vars[var], InstrumentedAttribute): query = query.add_column(doc_vars[var]) if table == db.Menus: menus = [] if hasattr(g, 'user'): if g.user.role == 10: if 'all' in bag: menus = g.tran.query( db.Menus).filter_by(_deleted='infinity').all() else: menus = g.tran.query(db.Menus).filter_by(_deleted='infinity') \ .filter(and_(db.Menus.role == 10, db.Menus.active)).all() return {"docs": menus, "count": len(menus)} if g.user.role < 10 and g.user.role != 1: menus_id = [] roles = g.tran.query(db.Roles).filter_by(_deleted='infinity') \ .filter(db.Roles.roletype == g.user.roleType['roleType']).all() if roles: for role in roles: menus_id.extend(role.menus_id) menus = g.tran.query(db.Menus).filter_by(_deleted='infinity') \ .filter(and_(db.Menus._id.in_(menus_id), db.Menus.active)).all() return {"docs": menus, "count": len(menus)} elif g.user.role == 1: menus_id = [] roles = g.tran.query(db.Roles).filter_by(_deleted='infinity') \ .filter(db.Roles._id.in_(g.user.roles_id if g.user.roles_id is not None else [])).all() if roles: for role in roles: menus_id.extend(role.menus_id) menus = g.tran.query(db.Menus).filter_by(_deleted='infinity') \ .filter(and_(db.Menus._id.in_(menus_id), db.Menus.active)).all() return {"docs": menus, "count": len(menus)} else: menus = g.tran.query(db.Menus).filter_by(_deleted='infinity') \ .filter(and_(db.Menus.role == 0, db.Menus.active)).all() return {"docs": menus, "count": len(menus)} if table == db.Companies: if hasattr(g, 'user') and g.user.role != 1 and g.user.role != 10: comps_id = [] user_empl = g.tran.query(db.Companyemployees).filter( db.Companyemployees.user_id == g.user.id).all() if user_empl: for uc in user_empl: comps_id.append(uc.company_id) user_company = g.tran.query(db.Companies).filter_by( _deleted='infinity', user_id=g.user.id).all() if user_company: for co in user_company: comps_id.append(co._id) query = query.filter(db.Companies._id.in_(comps_id)) if 'current' in bag and bag['current'] is True and hasattr( g, 'company'): query = query.filter(db.Companies._id == g.company._id) if g.client != '1': query = query.filter(db.Companies.company_type == 'supplier') if table == db.Company_product: if hasattr(g, 'company'): query = query.filter( db.Company_product.company_id == g.company._id) if table == db.Companybank: if hasattr(g, 'company'): query = query.filter(db.Companybank.company_id == g.company._id) if table == db.Companyqualification: if hasattr(g, 'company'): query = query.filter( db.Companyqualification.company_id == g.company._id) if table == db.Companydocument: if hasattr(g, 'company'): query = query.filter( db.Companydocument.company_id == g.company._id) if table == db.DirSection: if "local" in bag and bag["local"] is True: products = g.tran.query(db.Product).filter_by(_deleted='infinity') \ .filter(db.Product.local == bag["local"]).all() spec_ids = [] spec_in_ids = [] for product in products: specification_ids = [] prodspecs = g.tran.query(db.ProductSpec).filter_by( _deleted='infinity', product_id=product._id).all() for prodspec in prodspecs: if prodspec.specification_id not in specification_ids: specification_ids.append(prodspec.specification_id) sii = Set(spec_in_ids) si = Set(specification_ids) ds = sii.symmetric_difference(si) if len(ds) > 0: spec_ids.extend(specification_ids) dirsections = g.tran.query(db.DirSection) \ .filter_by(_deleted='infinity').all() dir_id = [] for dirsection in dirsections: ds = Set(dirsection.dircategories_id) sids = Set(spec_ids) drs = ds.intersection(sids) if len(drs) > 0: dir_id.append(dirsection._id) query = query.filter(db.DirSection._id.in_(dir_id)) elif "local" in bag and bag["local"] is False: company_products = g.tran.query(db.Company_product).filter_by( _deleted='infinity', status='active').all() spec_ids = [] spec_in_ids = [] for product in company_products: specification_ids = [] prodspecs = g.tran.query(db.ProductSpec) \ .filter_by(_deleted='infinity', product_id=product.product_id).all() for prodspec in prodspecs: if prodspec.specification_id not in specification_ids: specification_ids.append(prodspec.specification_id) sii = Set(spec_in_ids) si = Set(specification_ids) ds = sii.symmetric_difference(si) if len(ds) > 0: spec_ids.extend(specification_ids) dirsections = g.tran.query(db.DirSection) \ .filter_by(_deleted='infinity').all() dir_id = [] for dirsection in dirsections: ds = Set(dirsection.dircategories_id) sids = Set(spec_ids) drs = ds.intersection(sids) if len(drs) > 0: dir_id.append(dirsection._id) query = query.filter(db.DirSection._id.in_(dir_id)) if table == db.Typeofownership: if 'filter' in bag and 'type_owner' in bag['filter']: query = query.filter( db.Typeofownership.type_owner == bag["filter"]["type_owner"]) del bag["filter"]["type_owner"] if table == db.DirCountry: query = query.order_by(db.DirCountry.data['index'].asc()) if "filter" in bag: if "data" in bag["filter"] and isinstance(bag["filter"]["data"], dict): query = query.filter( table.data.contains(type_coerce(bag["filter"]["data"], JSONB))) del bag["filter"]["data"] query = query.filter_by(**bag["filter"]) if "order_by" in bag: query = query.order_by(*bag["order_by"]) count = query.count() if "limit" in bag: query = query.limit(bag["limit"]) if "offset" in bag: query = query.offset(bag["offset"]) if "with_roles" in bag and bag["with_roles"] is True: if table == db.Companies: roles = g.tran.query(func.json_build_object( "id", db.Roles._id, "name", db.Roles.name, "data", db.Roles.data)).select_from(db.Roles) \ .filter_by(_deleted='infinity') \ .filter(type_coerce(db.Companies.roles_id, JSONB) .contains(type_coerce(func.jsonb_build_array(db.Roles._id), JSONB))).as_scalar().label( 'roles') query = query.add_columns(roles) if "with_related" in bag and bag["with_related"] is True: if table == db.Companies: companybank = g.tran.query(func.jsonb_agg(func.row_to_json(text('companybank.*')))) \ .select_from(db.Companybank) \ .filter_by(_deleted='infinity').filter(db.Companybank.company_id == db.Companies._id).as_scalar() \ .label('companybank') companuqualifications = g.tran.query(func.jsonb_agg(func.row_to_json(text('companyqualification.*')))) \ .select_from(db.Companyqualification) \ .filter_by(_deleted='infinity').filter( db.Companyqualification.company_id == db.Companies._id).as_scalar() \ .label('companuqualifications') roles = g.tran.query(func.json_build_object( "id", db.Roles._id, "name", db.Roles.name)).select_from(db.Roles) \ .filter_by(_deleted='infinity') \ .filter(type_coerce(db.Companies.roles_id, JSONB) .contains(type_coerce(func.jsonb_build_array(db.Roles._id), JSONB))).as_scalar().label( 'roles') query = query.add_columns(companybank, companuqualifications, roles) elif table == db.Message: comment = g.tran.query(func.jsonb_agg(func.row_to_json(text('comments.*')))) \ .select_from(db.Comments) \ .filter_by(_deleted='infinity').filter(db.Comments.message_id == db.Message._id).as_scalar() \ .label('comment') query = query.add_columns(comment) elif table == db.Companyemployees: user = g.tran.query(func.json_build_object( "id", db.User.id, "fullname", db.User.username, "email", db.User.email, "rec_date", db.User.rec_date, "data", db.User.data, "role", db.User.role)).select_from(db.User) \ .filter_by(id=db.Companyemployees.user_id) \ .as_scalar().label('user') company = g.tran.query(func.json_build_object( "id", db.Companies._id, "name", db.Companies.name, "inn", db.Companies.inn)).select_from(db.Companies) \ .filter_by(_deleted='infinity', _id=db.Companyemployees.company_id).as_scalar().label('company') roles = g.tran.query(func.json_build_object( "id", db.DirPosition._id, "name", db.DirPosition.name)).select_from(db.DirPosition) \ .filter_by(_deleted='infinity') \ .filter(type_coerce(db.Companyemployees.roles_id, JSONB) .contains(type_coerce(func.jsonb_build_array(db.DirPosition._id), JSONB))).as_scalar().label( 'roles') query = query.add_columns(user, company, roles) result = orm_to_json(query.all()) if "with_related" in bag and bag["with_related"] is True: result = find_relations(result, table_name) if table == db.Companies: for r in result: if 'inn' in r and r['inn'] in ['00609201310130', '01209201710029']: r['_created'] = '2020-07-13 00:00:00' r['end_date'] = '2021-01-13 00:00:00' elif 'inn' in r and r['inn'] in [ '02301201710287', '02202201310102', '01207201610238' ]: r['_created'] = '2020-07-22 00:00:00' r['end_date'] = '2021-01-22 00:00:00' return { "docs": result, "count": count, "current_date": datetime.datetime.today() }
def listing(bag): table_name = bag["type"] table = getattr(db, table_name) if hasattr(db, table_name) else None if table is None or not issubclass(table, (db.Base, db.CouchSync)): raise CbsException(TABLE_NOT_FOUND) query = g.tran.query(table._id).filter_by(_deleted='infinity') doc_vars = vars(table) for var in doc_vars: if isinstance(doc_vars[var], InstrumentedAttribute): query = query.add_column(doc_vars[var]) if table == db.Menus: if not is_admin(): menus_id = [] roles = g.tran.query(db.Roles).filter_by(_deleted='infinity') \ .filter(db.Roles._id.in_(g.user.roles_id if g.user.roles_id is not None else [])).all() for role in roles: menus_id.extend(role.menus_id) query = query.filter(db.Menus._id.in_(menus_id)) if table == db.Journals: if not is_admin(): query = g.tran.query( db.Journals).filter(db.Journals.entry_user_id == g.user.id) if table == db.Queue: dissov = g.tran.query(db.Dissov).filter_by(_deleted='infinity')\ .filter(db.Dissov.secretary == g.user.id).first() query = query.filter(table.dissov_id == dissov._id) if table == db.New: if "sectionsofpublication_id" in bag: query = query.filter(db.New.sectionsofpublication_id == bag["sectionsofpublication_id"]) del bag["sectionsofpublication_id"] if table == db.Dcomposition: if "dissov_id" in bag: query = query.filter(db.Dcomposition.dissov_id == bag["dissov_id"]) del bag["dissov_id"] if table == db.Theme: if 'user_id' in bag: query = query.filter(table.entry_user_id == bag["user_id"]) del bag["user_id"] if 'search' in bag: query = query.filter( or_(func.concat(table.name, ' ')).ilike(u"%{0}%".format(bag['search']))) del bag["search"] if "filter" in bag: if 'filter' in bag and 'user_id' in bag['filter']: query = query.filter(table.user_id == bag["filter"]["user_id"]) del bag["filter"]["user_id"] if 'filter' in bag and 'academicdegree_id' in bag['filter']: query = query.filter( table.academicdegree_id == bag["filter"]["academicdegree_id"]) del bag["filter"]["academicdegree_id"] if 'filter' in bag and 'branchesofscience_id' in bag['filter']: query = query.filter(table.branchesofscience_id == bag["filter"] ["branchesofscience_id"]) del bag["filter"]["branchesofscience_id"] if 'filter' in bag and 'specialty_id' in bag['filter']: query = query.filter( table.specialty_id == bag["filter"]["specialty_id"]) del bag["filter"]["specialty_id"] if "data" in bag["filter"] and isinstance(bag["filter"]["data"], dict): query = query.filter( table.data.contains(type_coerce(bag["filter"]["data"], JSONB))) del bag["filter"]["data"] query = query.filter_by(**bag["filter"]) if 'search' in bag: query = query.filter( or_(func.concat(table.name_ru, ' ', table.name_kg, '')).ilike(u"%{}%".format(bag['search']))) del bag["search"] if 'user_id' in bag: query = query.filter(table.user_id == bag["user_id"]) del bag["user_id"] if "order_by" in bag: query = query.order_by(*bag["order_by"]) elif bag.get(IDS) and hasattr(table, "_id"): query = query.filter(table._id.in_(bag.get(IDS))) count = query.count() if "limit" in bag: query = query.limit(bag["limit"]) if "offset" in bag: query = query.offset(bag["offset"]) if "with_related" in bag and bag["with_related"] is True: if table == db.Documents: theme = g.tran.query(func.jsonb_agg(func.row_to_json(text('theme.*')))) \ .select_from(db.Theme) \ .filter_by(_deleted='infinity').filter( db.Theme._id == db.Documents.theme_id).as_scalar() \ .label('theme') query = query.add_columns(theme) if table == db.Userdegree: degreespeciality = g.tran.query(func.jsonb_agg(func.row_to_json(text('degreespeciality.*')))) \ .select_from(db.Degreespeciality) \ .filter_by(_deleted='infinity').filter(db.Degreespeciality.userdegree_id == db.Userdegree._id).as_scalar() \ .label('degreespeciality') query = query.add_columns(degreespeciality) if table == db.Dissov: programs = g.tran.query(func.jsonb_agg(func.row_to_json(text('dspecialty.*'))))\ .select_from(db.Dspecialty)\ .filter_by(_deleted='infinity').filter(db.Dspecialty.dissov_id == db.Dissov._id).as_scalar()\ .label('programs') compositions = g.tran.query(func.jsonb_agg(func.row_to_json(text('dcomposition.*')))) \ .select_from(db.Dcomposition) \ .filter_by(_deleted='infinity').filter(db.Dcomposition.dissov_id == db.Dissov._id).as_scalar() \ .label('compositions') query = query.add_columns(programs, compositions) if table == db.DisApplication: application_status = g.tran.query( func.row_to_json(text('enums.*'))).select_from(db.Enums) \ .filter_by(_deleted='infinity', name='status') \ .filter(db.Enums.data['key'].cast(TEXT) == cast(db.DisApplication.status, TEXT)) \ .as_scalar().label('application_status') theme = g.tran.query(func.jsonb_agg(func.row_to_json(text('theme.*')))) \ .select_from(db.Theme) \ .filter_by(_deleted='infinity').filter( db.Documents._id == db.DisApplication.document_id, db.Theme._id == db.Documents.theme_id).as_scalar() \ .label('theme') remark = g.tran.query(func.jsonb_agg(func.row_to_json(text('disremarks.*')))) \ .select_from(db.DisRemarks) \ .filter_by(_deleted='infinity').filter( db.DisRemarks.dissov_id == db.DisApplication.dissov_id).as_scalar() \ .label('remark') query = query.add_columns(application_status, theme, remark) result = orm_to_json(query.all()) if "with_related" in bag and bag["with_related"] is True: result = find_relations(result, table_name) return {"docs": result, "count": count}
def listing(bag): query = g.tran.query(db.Document._id).filter_by(_deleted='infinity', company_id=g.session['company_id']) doc_vars = vars(db.Document) for var in doc_vars: if isinstance(doc_vars[var], InstrumentedAttribute): query = query.add_column(doc_vars[var]) query = query.filter( or_(db.Document.data.contains(type_coerce({"user_id": g.user.id}, JSONB)), db.Document.data.contains(type_coerce({"executor_id": g.user.id}, JSONB)), type_coerce(db.Document.approval['roles_id'], JSONB).has_any(array(g.user.roles_id)) if len( g.user.roles_id) > 0 else None)) if "own" in bag and bag["own"] is True: query = query.filter(db.Document.data['user_id'] == g.user.id) else: query = query.filter(db.Document.document_status != 'draft') if "filter" in bag: if "data" in bag["filter"] and isinstance(bag["filter"]["data"], dict): query = query.filter(db.Document.data.contains(type_coerce(bag["filter"]["data"], JSONB))) del bag["filter"]["data"] query = query.filter_by(**bag["filter"]) if "order_by" in bag: query = query.order_by(*bag["order_by"]) else: query = query.order_by(db.Document._created.desc()) count = query.count() if "limit" in bag: query = query.limit(bag["limit"]) if "offset" in bag: query = query.offset(bag["offset"]) if 'with_related' in bag and bag['with_related'] is True: document_status_value = g.tran.query( func.row_to_json(text('enums.*'))).select_from(db.Enums) \ .filter_by(_deleted='infinity', name='document_status') \ .filter(db.Enums.data['key'].cast(TEXT) == cast(db.Document.document_status, TEXT)) \ .as_scalar().label('document_status_value') document_type_value = g.tran.query( func.row_to_json(text('enums.*'))).select_from(db.Enums) \ .filter_by(_deleted='infinity', name='document_type') \ .filter(db.Enums.data['key'].cast(TEXT) == cast(db.Document.document_type, TEXT)) \ .as_scalar().label('document_type_value') roles = g.tran.query(func.jsonb_agg(func.row_to_json(text('roles.*')))).select_from(db.Roles) \ .filter_by(_deleted='infinity')\ .filter(type_coerce(db.Document.approval['roles_id'], JSONB).has_any(array([db.Roles._id])))\ .as_scalar().label('roles') entry_user = g.tran.query(func.json_build_object( "id", db.User.id, "username", db.User.username, "email", db.User.email, "rec_date", db.User.rec_date, "data", db.User.data, "role", db.User.role)).select_from(db.User).filter_by( id=db.Document.entry_user_id) \ .as_scalar().label('entry_user') query = query.add_columns(document_status_value, document_type_value, entry_user, roles) return {'docs': orm_to_json(query.all()), 'count': count}
def find_relations(query): branch = g.tran.query(func.row_to_json(text('branches.*'))).select_from(db.Branches) \ .filter_by(_deleted='infinity', _id=db.Payments.branch_id).as_scalar().label('branch') document = g.tran.query(func.row_to_json(text('document.*'))).select_from(db.Document) \ .filter_by(_deleted='infinity', _id=db.Payments.document_id).as_scalar().label('document') currency = g.tran.query(func.row_to_json(text('currencies.*'))).select_from(db.Currencies) \ .filter_by(_deleted='infinity', _id=db.Payments.currency_id).as_scalar().label('currency') payment_direction = g.tran.query(func.row_to_json(text('enums.*'))).select_from(db.Enums) \ .filter_by(_deleted='infinity', name='payment_direction') \ .filter(db.Enums.data['key'].cast(TEXT) == cast(db.Payments.payment_direction, TEXT)) \ .as_scalar().label('payment_direction_value') payment_type = g.tran.query(func.row_to_json(text('enums.*'))).select_from(db.Enums) \ .filter_by(_deleted='infinity', name='payment_type') \ .filter(db.Enums.data['key'].cast(TEXT) == cast(db.Payments.payment_type, TEXT)) \ .as_scalar().label('payment_type_value') payment_status = g.tran.query(func.row_to_json(text('enums.*'))).select_from(db.Enums) \ .filter_by(_deleted='infinity', name='payment_status') \ .filter(db.Enums.data['key'].cast(TEXT) == cast(db.Payments.payment_status, TEXT)) \ .as_scalar().label('payment_status_value') user = g.tran.query(func.json_build_object( "id", db.User.id, "username", db.User.username, "email", db.User.email, "rec_date", db.User.rec_date, "data", db.User.data, "role", db.User.role)).select_from(db.User).filter_by(id=db.UserCompany.user_id) \ .as_scalar().label('user') employee = g.tran.query(func.json_build_object('_id', db.UserCompany._id, '_rev', db.UserCompany._rev, '_created', db.UserCompany._created, 'user_id', db.UserCompany.user_id, 'company_id', db.UserCompany.company_id, 'entry_user_id', db.UserCompany.entry_user_id, 'branches_id', db.UserCompany.branches_id, 'roles_id', db.UserCompany.roles_id, 'access', db.UserCompany.access, 'user', user)).select_from(db.UserCompany) \ .filter_by(_deleted='infinity', company_id=db.Payments.company_id, user_id=db.Payments.data['employee_id'] .cast(INTEGER))\ .as_scalar().label('employee') contractor = g.tran.query(func.row_to_json(text('contractors.*'))).select_from(db.Contractors) \ .filter_by(_deleted='infinity', _id=db.Payments.data['contractor_id'].cast(TEXT)) \ .as_scalar().label('contractor') branch2 = g.tran.query(func.row_to_json(text('branches.*'))).select_from(db.Branches) \ .filter_by(_deleted='infinity', _id=db.Payments.data['branch_id'].cast(TEXT)).as_scalar().label('branch2') entry_user = g.tran.query(func.json_build_object( "id", db.User.id, "username", db.User.username, "email", db.User.email, "rec_date", db.User.rec_date, "data", db.User.data, "role", db.User.role)).select_from(db.User).filter_by(id=db.Payments.entry_user_id)\ .as_scalar().label('entry_user') query = query.add_columns(branch, document, currency, payment_direction, payment_type, payment_status, contractor, branch2, entry_user, employee) return query