def serviceShowByChildDevCenter(cls,childcenter=None,fiscalyear=None ): sql = text("""select dental_child_dev_center_kpi.dental_child_dev_center_kpi_id, dental_child_dev_center_kpi.detail, service.child_dev_center_id, service.dental_child_dev_center_service_id, service.fiscal_year, IFNULL(service.`value`,0) as value from dental_child_dev_center_kpi LEFT JOIN ( select dental_child_dev_center_service.child_dev_center_id, dental_child_dev_center_service.dental_child_dev_center_service_id, dental_child_dev_center_service.dental_child_dev_center_kpi_id, dental_child_dev_center_service.fiscal_year, dental_child_dev_center_service.`value` FROM child_dev_center LEFT JOIN dental_child_dev_center_service on dental_child_dev_center_service.child_dev_center_id = child_dev_center.child_dev_center_id WHERE dental_child_dev_center_service.fiscal_year=:fiscal_year and child_dev_center.child_dev_center_id=:childcenter ) service on dental_child_dev_center_kpi.dental_child_dev_center_kpi_id = service.dental_child_dev_center_kpi_id """ , bindparams=[bindparam('childcenter', str(childcenter)),bindparam('fiscal_year', str(fiscalyear)) ] ); #log.info(sql); result = DBSession.execute(sql ); return result ; #DBSession.query(cls).filter(sql).all();
def listIndicatorBySection(cls, sectionid=None, year=None, start_year=None, month=None): list = [] if (sectionid): sql = text("""select id.indicators_detail_id, id.detail, ty.target, rs.risk_section_id, rs.description, ids.indicators_service_id, ids.indicator_value, ids.months_id, ty.years_id from indicators_detail id LEFT JOIN target_year ty on id.indicators_detail_id = ty.indicators_detail_id LEFT JOIN map_indicators_section mis on id.indicators_detail_id = mis.indicators_detail_id LEFT JOIN risk_section rs on rs.risk_section_id = mis.risk_section_id LEFT OUTER JOIN indicators_service ids on (ids.indicators_detail_id = id.indicators_detail_id and ids.risk_section_id =rs.risk_section_id and ids.years_id = ty.years_id and ids.months_id = """ + str(month) + """ ) where ty.active = 1 and ty.years_id = """ + str(year) + """ and rs.risk_section_id = """ + str(sectionid) + """ """) result = DBSession.execute(sql) for row in result: list.append({ 'id': row['indicators_detail_id'], 'indicators_detail_id': row['indicators_detail_id'], 'indicators_service_id': row['indicators_service_id'], 'detail': row['detail'], 'target': row['target'], 'risk_section_id': row['risk_section_id'], 'indicator_value': row['indicator_value'], 'months_id': row['months_id'], 'years_id': row['years_id'] }) return list
def serviceShowByClub(cls,seniorclub=None,fiscalyear=None ): sql = text("""SELECT dental_senior_club_kpi.dental_senior_club_kpi_id, dental_senior_club_kpi.detail, dental_senior_club_kpi.`range`, senior_service.dental_senior_club_service_id, senior_service.fiscal_year, senior_service.senior_club_id, IFNULL( senior_service.`value`, 0) as value FROM dental_senior_club_kpi LEFT JOIN ( SELECT senior_club.senior_club_id, senior_club.senior_club_name, dental_senior_club_service.fiscal_year, dental_senior_club_service.`value`, dental_senior_club_service.dental_senior_club_service_id, dental_senior_club_service.dental_senior_club_kpi_id from senior_club LEFT JOIN dental_senior_club_service on senior_club.senior_club_id = dental_senior_club_service.senior_club_id WHERE dental_senior_club_service.fiscal_year=:fiscal_year and senior_club.senior_club_id=:seniorclub ) senior_service on dental_senior_club_kpi.dental_senior_club_kpi_id = senior_service.dental_senior_club_kpi_id """ , bindparams=[bindparam('seniorclub', str(seniorclub)),bindparam('fiscal_year', str(fiscalyear)) ] ); #log.info(sql); result = DBSession.execute(sql ); return result ; #DBSession.query(cls).filter(sql).all();
def serviceShowBySchoolandGroupKPI(cls,school=None,fiscalyear=None,groupKPI=None): sql = text("""SELECT dental_school_kpi.dental_school_kpi_id, dental_school_kpi.dental_school_kpi_group_id, dental_school_kpi.detail, dental_school_kpi.`range`, service.dental_school_service_id, service.fiscal_year, service.school_id, IFNULL(service.`value`,0) as value from dental_school_kpi LEFT JOIN ( select dental_school_service.dental_school_service_id, dental_school_service.fiscal_year, dental_school_service.school_id, dental_school_service.`value` , dental_school_service.dental_school_kpi_id from school JOIN dental_school_service on school.school_id = dental_school_service.school_id WHERE school.school_id=:schoolid and dental_school_service.fiscal_year=:fiscal_year ) service on service.dental_school_kpi_id = dental_school_kpi.dental_school_kpi_id WHERE dental_school_kpi.dental_school_kpi_group_id=:groupkpi """ , bindparams=[bindparam('schoolid', str(school)),bindparam('fiscal_year', str(fiscalyear)) ,bindparam('groupkpi', str(groupKPI)) ] ); #log.info(sql); result = DBSession.execute(sql ); return result ; #DBSession.query(cls).filter(sql).all();
def serviceShowByClub(cls, seniorclub=None, fiscalyear=None): sql = text("""SELECT dental_senior_club_kpi.dental_senior_club_kpi_id, dental_senior_club_kpi.detail, dental_senior_club_kpi.`range`, senior_service.dental_senior_club_service_id, senior_service.fiscal_year, senior_service.senior_club_id, IFNULL( senior_service.`value`, 0) as value FROM dental_senior_club_kpi LEFT JOIN ( SELECT senior_club.senior_club_id, senior_club.senior_club_name, dental_senior_club_service.fiscal_year, dental_senior_club_service.`value`, dental_senior_club_service.dental_senior_club_service_id, dental_senior_club_service.dental_senior_club_kpi_id from senior_club LEFT JOIN dental_senior_club_service on senior_club.senior_club_id = dental_senior_club_service.senior_club_id WHERE dental_senior_club_service.fiscal_year=:fiscal_year and senior_club.senior_club_id=:seniorclub ) senior_service on dental_senior_club_kpi.dental_senior_club_kpi_id = senior_service.dental_senior_club_kpi_id """, bindparams=[ bindparam('seniorclub', str(seniorclub)), bindparam('fiscal_year', str(fiscalyear)) ]) #log.info(sql); result = DBSession.execute(sql) return result
def serviceShowByChildDevCenter(cls, childcenter=None, fiscalyear=None): sql = text("""select dental_child_dev_center_kpi.dental_child_dev_center_kpi_id, dental_child_dev_center_kpi.detail, service.child_dev_center_id, service.dental_child_dev_center_service_id, service.fiscal_year, IFNULL(service.`value`,0) as value from dental_child_dev_center_kpi LEFT JOIN ( select dental_child_dev_center_service.child_dev_center_id, dental_child_dev_center_service.dental_child_dev_center_service_id, dental_child_dev_center_service.dental_child_dev_center_kpi_id, dental_child_dev_center_service.fiscal_year, dental_child_dev_center_service.`value` FROM child_dev_center LEFT JOIN dental_child_dev_center_service on dental_child_dev_center_service.child_dev_center_id = child_dev_center.child_dev_center_id WHERE dental_child_dev_center_service.fiscal_year=:fiscal_year and child_dev_center.child_dev_center_id=:childcenter ) service on dental_child_dev_center_kpi.dental_child_dev_center_kpi_id = service.dental_child_dev_center_kpi_id """, bindparams=[ bindparam('childcenter', str(childcenter)), bindparam('fiscal_year', str(fiscalyear)) ]) #log.info(sql); result = DBSession.execute(sql) return result
def listGroupUser(self,**kw): groups = DBSession.query(Group).all(); self.listgroup = []; for group in groups: self.listgroup.append({'id' : group.group_id , 'name' : group.group_name }); return dict(root = self.listgroup,total=str(len(self.listgroup)));
def showProgramClinicReport(cls,startDate, stopDate,book_type,bookSearch,offset=0,limit=15): query = DBSession.query(cls).filter( and_( cls.activate == '1', and_( cls.book_detail.like(bookSearch) ,and_( cls.book_type_id == book_type ,cls.book_recive.between(startDate, stopDate) ) )) ); count = query.count(); list = query.order_by(desc(cls.book_number)).limit(limit).offset(offset).all(); #DBSession.query(cls).filter(between(cls.book_recive, startDate, stopDate)).all(); print count ; return count, list;
def search(cls, fiscalyear=None, group=None, subgroup=None): sql = "1=1 " if (fiscalyear): sql = sql + " and fiscal_year = " + fiscalyear if (group): sql = sql + " and revenue_list_id = " + group if (subgroup): sql = sql + " and revenue_sub_list_id = " + subgroup log.info(sql) return DBSession.query(cls).filter(sql).all()
def search(cls,fiscalyear = None, group= None, subgroup= None): sql = "1=1 "; if (fiscalyear): sql = sql + " and fiscal_year = " + fiscalyear; if(group): sql = sql + " and revenue_list_id = " + group; if(subgroup): sql = sql + " and revenue_sub_list_id = " + subgroup; log.info(sql); return DBSession.query(cls).filter(sql).all();
def search(cls,fiscalyear = None, group= None, subgroup= None): sql = "1=1 "; if (fiscalyear): sql = sql + " and fiscal_year = " + fiscalyear; if(group): sql = sql + " and expenses_list_id = " + group; if(subgroup): sql = sql + " and expenses_sub_list_id = " + subgroup; log.info(sql); return DBSession.query(cls).filter(sql).all();
def serviceShowAllKPIBySenior(cls,fiscalyear=None): sql = text (""" SELECT senior_club.senior_club_id, senior_club.senior_club_name, (SELECT value from dental_senior_club_service where fiscal_year =:fiscal_year1 and dental_senior_club_kpi_id = 2 and senior_club_id = senior_club.senior_club_id ) as kpi1, (SELECT value from dental_senior_club_service where fiscal_year =:fiscal_year2 and dental_senior_club_kpi_id = 3 and senior_club_id = senior_club.senior_club_id ) as kpi2, (SELECT value from dental_senior_club_service where fiscal_year =:fiscal_year3 and dental_senior_club_kpi_id = 4 and senior_club_id = senior_club.senior_club_id ) as kpi3 FROM senior_club """, bindparams=[bindparam('fiscal_year1', str(fiscalyear)),bindparam('fiscal_year2', str(fiscalyear)),bindparam('fiscal_year3', str(fiscalyear)) ]); result = DBSession.execute(sql ); return result ;
def serviceShoAllKPIByChildDevCenter(cls,fiscalyear=None): sql = text(""" select child_dev.child_dev_center_id, child_dev.child_dev_name, ( select value from dental_child_dev_center_service where fiscal_year =:fiscal_year1 and dental_child_dev_center_kpi_id =1 and dental_child_dev_center_service.child_dev_center_id = child_dev.child_dev_center_id) as kpi1, ( select value from dental_child_dev_center_service where fiscal_year =:fiscal_year2 and dental_child_dev_center_kpi_id =2 and dental_child_dev_center_service.child_dev_center_id = child_dev.child_dev_center_id) as kpi2 from child_dev_center as child_dev ORDER BY child_dev.child_dev_center_id """ , bindparams=[bindparam('fiscal_year1', str(fiscalyear)) ,bindparam('fiscal_year2', str(fiscalyear)) ]); result = DBSession.execute(sql ); return result ;
def querySummary(cls,fiscalyear=None,division=None,section=None,status=None,projectType=None,start=0,limit=25): sql = "where 1=1 "; if (fiscalyear): sql = sql + " and project.fiscal_year = " + fiscalyear; if(projectType): sql = sql + " and project_type_id = " + projectType; if(division): sql = sql + " and project.division_id = " + division; if(section): sql = sql + " and project.section_id = " + section; if(status): sql = sql + " and project.project_status_id = " + status ; sql = text("""select d.description as division , sum( d.allbudget) as allbudget, sum( d.budget) as budget, sum( d.maintenance_funds_budget) as maintenance_funds_budget, sum( d.budget_other_from) as budget_other_from FROM ( select division.description, IFNULL(project.budget, 0) + IFNULL(project.maintenance_funds_budget,0) +IFNULL(project.budget_other_from ,0) as allbudget, IFNULL(project.budget, 0) as budget, IFNULL(project.maintenance_funds_budget,0) as maintenance_funds_budget, IFNULL(project.budget_other_from ,0) as budget_other_from from division left join project on division.division_id = project.division_id """ + sql + """ ) d group by d.description """); result = DBSession.execute(sql); list = []; for row in result: list.append({ 'division':row['division'], 'allBudget' :row['allbudget'] , 'budget' :row['budget'] , 'maintenance_funds_budget' :row['maintenance_funds_budget'] , 'budget_other_from' :row['budget_other_from'], u'งบประมาณ' :row['budget'] , u'งบประมาณอื่น' :row['budget_other_from'] , u'เงินบำรุง' :row['maintenance_funds_budget'] }); return list;
def listUser(self,**kw): #users = DBSession.query(User).all(); groups = DBSession.query(Group).all(); self.list = []; for group in groups: for guser in group.users: self.list.append({'id' : guser.user_id , 'name' : guser.user_name, 'display' : guser.display_name, 'email' : guser.email_address, 'group_id' : group.group_id, 'group' : group.group_name }); return dict(root = self.list,total=str(len(self.list)));
def search(cls,fiscalyear=None,division=None,section=None,status=None,projectType=None,start=0,limit=25): sql = "1=1 "; if (fiscalyear): sql = sql + " and fiscal_year = " + fiscalyear; if(projectType): sql = sql + " and project_type_id = " + projectType; if(division): sql = sql + " and division_id = " + division; if(section): sql = sql + " and section_id = " + section; if(status): sql = sql + " and project_status_id = " + status ; log.info(sql); return DBSession.query(cls).filter(sql).all();
def serviceShoAllKPIBySchool(cls,fiscalyear=None): sql = text(""" select school.school_id, school.school_name, ( SELECT value from dental_school_service where fiscal_year=:fiscal_year1 and dental_school_kpi_id = 2 and dental_school_service.school_id = school.school_id) as kpi1, ( SELECT value from dental_school_service where fiscal_year=:fiscal_year2 and dental_school_kpi_id = 3 and dental_school_service.school_id = school.school_id) as kpi2, ( SELECT value from dental_school_service where fiscal_year=:fiscal_year3 and dental_school_kpi_id = 4 and dental_school_service.school_id = school.school_id) as kpi3, ( SELECT value from dental_school_service where fiscal_year=:fiscal_year4 and dental_school_kpi_id = 9 and dental_school_service.school_id = school.school_id) as kpi4 from school WHERE school.high_school = 0 ORDER BY school.school_id """, bindparams=[ bindparam('fiscal_year1', str(fiscalyear)),bindparam('fiscal_year2', str(fiscalyear)),bindparam('fiscal_year3', str(fiscalyear)),bindparam('fiscal_year4', str(fiscalyear)) ]); result = DBSession.execute(sql ); return result ;
def serviceShoAllKPIByChildDevCenter(cls, fiscalyear=None): sql = text(""" select child_dev.child_dev_center_id, child_dev.child_dev_name, ( select value from dental_child_dev_center_service where fiscal_year =:fiscal_year1 and dental_child_dev_center_kpi_id =1 and dental_child_dev_center_service.child_dev_center_id = child_dev.child_dev_center_id) as kpi1, ( select value from dental_child_dev_center_service where fiscal_year =:fiscal_year2 and dental_child_dev_center_kpi_id =2 and dental_child_dev_center_service.child_dev_center_id = child_dev.child_dev_center_id) as kpi2 from child_dev_center as child_dev ORDER BY child_dev.child_dev_center_id """, bindparams=[ bindparam('fiscal_year1', str(fiscalyear)), bindparam('fiscal_year2', str(fiscalyear)) ]) result = DBSession.execute(sql) return result
def serviceShowAllKPIBySenior(cls, fiscalyear=None): sql = text(""" SELECT senior_club.senior_club_id, senior_club.senior_club_name, (SELECT value from dental_senior_club_service where fiscal_year =:fiscal_year1 and dental_senior_club_kpi_id = 2 and senior_club_id = senior_club.senior_club_id ) as kpi1, (SELECT value from dental_senior_club_service where fiscal_year =:fiscal_year2 and dental_senior_club_kpi_id = 3 and senior_club_id = senior_club.senior_club_id ) as kpi2, (SELECT value from dental_senior_club_service where fiscal_year =:fiscal_year3 and dental_senior_club_kpi_id = 4 and senior_club_id = senior_club.senior_club_id ) as kpi3 FROM senior_club """, bindparams=[ bindparam('fiscal_year1', str(fiscalyear)), bindparam('fiscal_year2', str(fiscalyear)), bindparam('fiscal_year3', str(fiscalyear)) ]) result = DBSession.execute(sql) return result
def setUp(self): """Setup test fixture for each model test method.""" try: new_attrs = {} new_attrs.update(self.attrs) new_attrs.update(self.do_get_dependencies()) self.obj = self.klass(**new_attrs) DBSession.add(self.obj) DBSession.flush() return self.obj except: DBSession.rollback() raise
def listIndicatorBySection(cls,sectionid=None,year=None,start_year=None,month=None): list =[]; if(sectionid ): sql = text("""select id.indicators_detail_id, id.detail, ty.target, rs.risk_section_id, rs.description, ids.indicators_service_id, ids.indicator_value, ids.months_id, ty.years_id from indicators_detail id LEFT JOIN target_year ty on id.indicators_detail_id = ty.indicators_detail_id LEFT JOIN map_indicators_section mis on id.indicators_detail_id = mis.indicators_detail_id LEFT JOIN risk_section rs on rs.risk_section_id = mis.risk_section_id LEFT OUTER JOIN indicators_service ids on (ids.indicators_detail_id = id.indicators_detail_id and ids.risk_section_id =rs.risk_section_id and ids.years_id = ty.years_id and ids.months_id = """ + str(month) +""" ) where ty.active = 1 and ty.years_id = """ + str(year) + """ and rs.risk_section_id = """ + str(sectionid) + """ """); result = DBSession.execute(sql); for row in result: list.append({ 'id' : row['indicators_detail_id'] , 'indicators_detail_id' : row['indicators_detail_id'] , 'indicators_service_id' : row['indicators_service_id'], 'detail' : row['detail'] , 'target' : row['target'] , 'risk_section_id' : row['risk_section_id'] , 'indicator_value' : row['indicator_value'] , 'months_id' : row['months_id'] , 'years_id' : row['years_id'] }); return list;
def setUp(self): """Prepare model test fixture.""" try: new_attrs = {} new_attrs.update(self.attrs) new_attrs.update(self.do_get_dependencies()) self.obj = self.klass(**new_attrs) DBSession.add(self.obj) DBSession.flush() return self.obj except: DBSession.rollback() raise
def queryGroupByFiscalYear(cls, year, revenue_list='%'): sql = text("""select main.revenue_list_name, main.revenue_sub_list_name, IFNULL(revenue_year.estimate,0) as estimate, IFNULL(revenue_year.fiscal_year,:show_year ) as fiscal_year, main.revenue_list_id, main.revenue_sub_list_id from ( select revenue_list.revenue_list_id, revenue_list.revenue_list_name, revenue_sub_list.revenue_sub_list_id, revenue_sub_list.revenue_sub_list_name from revenue_sub_list inner join revenue_list on revenue_sub_list.revenue_list_id = revenue_list.revenue_list_id ) as main left join ( SELECT revenue.estimate, revenue.fiscal_year, revenue.revenue_list_id, revenue.revenue_sub_list_id from revenue where revenue.fiscal_year=:fiscal_year ) revenue_year on ( main.revenue_list_id = revenue_year.revenue_list_id and main.revenue_sub_list_id = revenue_year.revenue_sub_list_id) where main.revenue_list_id like :revenue_list_id ORDER BY main.revenue_list_id, main.revenue_sub_list_id """, bindparams=[ bindparam('show_year', str(year)), bindparam('fiscal_year', str(year)), bindparam('revenue_list_id', str(revenue_list)) ]) #log.info(sql); result = DBSession.execute(sql) return result
def queryGraphByFiscalYear(cls, year, revenue_list='%'): sql = text(""" select main.revenue_list_name , IFNULL(SUM(revenue_year.estimate) ,0) sum_estimate from ( select revenue_list.revenue_list_id, revenue_list.revenue_list_name, revenue_sub_list.revenue_sub_list_id, revenue_sub_list.revenue_sub_list_name from revenue_sub_list inner join revenue_list on revenue_sub_list.revenue_list_id = revenue_list.revenue_list_id ) as main left join ( SELECT revenue.estimate, revenue.fiscal_year, revenue.revenue_list_id, revenue.revenue_sub_list_id from revenue where revenue.fiscal_year=:fiscal_year ) revenue_year on ( main.revenue_list_id = revenue_year.revenue_list_id and main.revenue_sub_list_id = revenue_year.revenue_sub_list_id) where main.revenue_list_id like :revenue_list_id GROUP BY main.revenue_list_name ORDER BY main.revenue_list_id, main.revenue_sub_list_id """, bindparams=[ bindparam('fiscal_year', str(year)), bindparam('revenue_list_id', str(revenue_list)) ]) result = DBSession.execute(sql) return result
def showProgramClinicReport(cls, startDate, stopDate, book_type, bookSearch, offset=0, limit=15): query = DBSession.query(cls).filter( and_( cls.activate == '1', and_( cls.book_detail.like(bookSearch), and_(cls.book_type_id == book_type, cls.book_recive.between(startDate, stopDate))))) count = query.count() list = query.order_by(desc( cls.book_number)).limit(limit).offset(offset).all() #DBSession.query(cls).filter(between(cls.book_recive, startDate, stopDate)).all(); print count return count, list
def search(cls, fiscalyear=None, division=None, section=None, status=None, projectType=None, start=0, limit=25): sql = "1=1 " if (fiscalyear): sql = sql + " and fiscal_year = " + fiscalyear if (projectType): sql = sql + " and project_type_id = " + projectType if (division): sql = sql + " and division_id = " + division if (section): sql = sql + " and section_id = " + section if (status): sql = sql + " and project_status_id = " + status log.info(sql) return DBSession.query(cls).filter(sql).all()
def queryGroupByFiscalYear(cls,year,revenue_list= '%'): sql = text("""select main.revenue_list_name, main.revenue_sub_list_name, IFNULL(revenue_year.estimate,0) as estimate, IFNULL(revenue_year.fiscal_year,:show_year ) as fiscal_year, main.revenue_list_id, main.revenue_sub_list_id from ( select revenue_list.revenue_list_id, revenue_list.revenue_list_name, revenue_sub_list.revenue_sub_list_id, revenue_sub_list.revenue_sub_list_name from revenue_sub_list inner join revenue_list on revenue_sub_list.revenue_list_id = revenue_list.revenue_list_id ) as main left join ( SELECT revenue.estimate, revenue.fiscal_year, revenue.revenue_list_id, revenue.revenue_sub_list_id from revenue where revenue.fiscal_year=:fiscal_year ) revenue_year on ( main.revenue_list_id = revenue_year.revenue_list_id and main.revenue_sub_list_id = revenue_year.revenue_sub_list_id) where main.revenue_list_id like :revenue_list_id ORDER BY main.revenue_list_id, main.revenue_sub_list_id """ , bindparams=[bindparam('show_year', str(year)),bindparam('fiscal_year', str(year)), bindparam('revenue_list_id',str(revenue_list))] ); #log.info(sql); result = DBSession.execute(sql ); return result ; #DBSession.query(cls).filter(sql).all();
def queryGroupByFiscalYear(cls,year,expenses_list= '%'): sql = text("""select main.expenses_list_name, main.expenses_sub_list_name, IFNULL(expenses_year.estimate,0) as estimate, IFNULL(expenses_year.fiscal_year,:show_year ) as fiscal_year, main.expenses_list_id, main.expenses_sub_list_id from ( select expenses_list.expenses_list_id, expenses_list.expenses_list_name, expenses_sub_list.expenses_sub_list_id, expenses_sub_list.expenses_sub_list_name from expenses_sub_list inner join expenses_list on expenses_sub_list.expenses_list_id = expenses_list.expenses_list_id ) as main left join ( SELECT expenses.estimate, expenses.fiscal_year, expenses.expenses_list_id, expenses.expenses_sub_list_id from expenses where expenses.fiscal_year=:fiscal_year ) expenses_year on ( main.expenses_list_id = expenses_year.expenses_list_id and main.expenses_sub_list_id = expenses_year.expenses_sub_list_id) where main.expenses_list_id like :expenses_list_id ORDER BY main.expenses_list_id, main.expenses_sub_list_id """ , bindparams=[bindparam('show_year', str(year)),bindparam('fiscal_year', str(year)), bindparam('expenses_list_id',str(expenses_list))] ); #log.info(sql); result = DBSession.execute(sql ); return result ; #DBSession.query(cls).filter(sql).all();
def serviceShoAllKPIBySchool(cls, fiscalyear=None): sql = text(""" select school.school_id, school.school_name, ( SELECT value from dental_school_service where fiscal_year=:fiscal_year1 and dental_school_kpi_id = 2 and dental_school_service.school_id = school.school_id) as kpi1, ( SELECT value from dental_school_service where fiscal_year=:fiscal_year2 and dental_school_kpi_id = 3 and dental_school_service.school_id = school.school_id) as kpi2, ( SELECT value from dental_school_service where fiscal_year=:fiscal_year3 and dental_school_kpi_id = 4 and dental_school_service.school_id = school.school_id) as kpi3, ( SELECT value from dental_school_service where fiscal_year=:fiscal_year4 and dental_school_kpi_id = 9 and dental_school_service.school_id = school.school_id) as kpi4 from school WHERE school.high_school = 0 ORDER BY school.school_id """, bindparams=[ bindparam('fiscal_year1', str(fiscalyear)), bindparam('fiscal_year2', str(fiscalyear)), bindparam('fiscal_year3', str(fiscalyear)), bindparam('fiscal_year4', str(fiscalyear)) ]) result = DBSession.execute(sql) return result
def queryGraphByFiscalYear(cls,year,revenue_list='%'): sql = text(""" select main.revenue_list_name , IFNULL(SUM(revenue_year.estimate) ,0) sum_estimate from ( select revenue_list.revenue_list_id, revenue_list.revenue_list_name, revenue_sub_list.revenue_sub_list_id, revenue_sub_list.revenue_sub_list_name from revenue_sub_list inner join revenue_list on revenue_sub_list.revenue_list_id = revenue_list.revenue_list_id ) as main left join ( SELECT revenue.estimate, revenue.fiscal_year, revenue.revenue_list_id, revenue.revenue_sub_list_id from revenue where revenue.fiscal_year=:fiscal_year ) revenue_year on ( main.revenue_list_id = revenue_year.revenue_list_id and main.revenue_sub_list_id = revenue_year.revenue_sub_list_id) where main.revenue_list_id like :revenue_list_id GROUP BY main.revenue_list_name ORDER BY main.revenue_list_id, main.revenue_sub_list_id """ , bindparams=[ bindparam('fiscal_year', str(year)), bindparam('revenue_list_id',str(revenue_list))]); result = DBSession.execute(sql ); return result ;
def queryGraphByFiscalYear(cls,year,expenses_list='%'): sql = text(""" select main.expenses_list_name , IFNULL(SUM(expenses_year.estimate) ,0) sum_estimate from ( select expenses_list.expenses_list_id, expenses_list.expenses_list_name, expenses_sub_list.expenses_sub_list_id, expenses_sub_list.expenses_sub_list_name from expenses_sub_list inner join expenses_list on expenses_sub_list.expenses_list_id = expenses_list.expenses_list_id ) as main left join ( SELECT expenses.estimate, expenses.fiscal_year, expenses.expenses_list_id, expenses.expenses_sub_list_id from expenses where expenses.fiscal_year=:fiscal_year ) expenses_year on ( main.expenses_list_id = expenses_year.expenses_list_id and main.expenses_sub_list_id = expenses_year.expenses_sub_list_id) where main.expenses_list_id like :expenses_list_id GROUP BY main.expenses_list_name ORDER BY main.expenses_list_id, main.expenses_sub_list_id """ , bindparams=[ bindparam('fiscal_year', str(year)), bindparam('expenses_list_id',str(expenses_list))]); result = DBSession.execute(sql ); return result ;
def serviceShowBySchoolandGroupKPI(cls, school=None, fiscalyear=None, groupKPI=None): sql = text("""SELECT dental_school_kpi.dental_school_kpi_id, dental_school_kpi.dental_school_kpi_group_id, dental_school_kpi.detail, dental_school_kpi.`range`, service.dental_school_service_id, service.fiscal_year, service.school_id, IFNULL(service.`value`,0) as value from dental_school_kpi LEFT JOIN ( select dental_school_service.dental_school_service_id, dental_school_service.fiscal_year, dental_school_service.school_id, dental_school_service.`value` , dental_school_service.dental_school_kpi_id from school JOIN dental_school_service on school.school_id = dental_school_service.school_id WHERE school.school_id=:schoolid and dental_school_service.fiscal_year=:fiscal_year ) service on service.dental_school_kpi_id = dental_school_kpi.dental_school_kpi_id WHERE dental_school_kpi.dental_school_kpi_group_id=:groupkpi """, bindparams=[ bindparam('schoolid', str(school)), bindparam('fiscal_year', str(fiscalyear)), bindparam('groupkpi', str(groupKPI)) ]) #log.info(sql); result = DBSession.execute(sql) return result
def getById(cls,id): return DBSession.query(cls) .get(str(id));
def listAll(cls): return DBSession.query(cls).all()
def listAll(cls): return DBSession.query(cls).order_by(cls.project_status_id).all()
def listReport2Indicator(cls ,year=None ): #listHash = {}; listGroupHash = {}; sql = text(""" select ind_type.indicators_type_id, ind_type.indicators_type_name, ind.indicators_detail_id, ind.indicators_group_id, ing.indicators_group_name, ind.detail, ty.target, mis.risk_section_id, rs.description as section, case when mis.risk_section_id in (3,4) THEN (select IFNULL( IF (IsNumeric(ins2.indicator_value)= 1,ROUND(ROUND(sum(ins2.indicator_value),2)/2,2),IsSpace(ins2.indicator_value)),'-' ) from indicators_service ins2 where ins2.months_id = 10 and ins2.years_id = (ty.years_id) and ins2.indicators_detail_id = ind.indicators_detail_id ) else (select IFNULL( IF (IsNumeric(ins2.indicator_value)= 1,ROUND(sum(ins2.indicator_value),2),IsSpace(ins2.indicator_value)),'-' ) from indicators_service ins2 where ins2.months_id = 10 and ins2.years_id = (ty.years_id) and ins2.indicators_detail_id = ind.indicators_detail_id ) end as "1", case when mis.risk_section_id in (3,4) THEN (select IFNULL( IF (IsNumeric(ins2.indicator_value)= 1,ROUND(ROUND(sum(ins2.indicator_value),2)/2,2),IsSpace(ins2.indicator_value)),'-' ) from indicators_service ins2 where ins2.months_id = 11 and ins2.years_id = (ty.years_id) and ins2.indicators_detail_id = ind.indicators_detail_id ) else (select IFNULL( IF (IsNumeric(ins2.indicator_value)= 1,ROUND(sum(ins2.indicator_value),2),IsSpace(ins2.indicator_value)),'-' ) from indicators_service ins2 where ins2.months_id = 11 and ins2.years_id = (ty.years_id) and ins2.indicators_detail_id = ind.indicators_detail_id ) end as "2", case when mis.risk_section_id in (3,4) THEN (select IFNULL( IF (IsNumeric(ins2.indicator_value)= 1,ROUND(ROUND(sum(ins2.indicator_value),2)/2,2),IsSpace(ins2.indicator_value)),'-' ) from indicators_service ins2 where ins2.months_id = 12 and ins2.years_id = (ty.years_id) and ins2.indicators_detail_id = ind.indicators_detail_id ) else (select IFNULL( IF (IsNumeric(ins2.indicator_value)= 1,ROUND(sum(ins2.indicator_value),2),IsSpace(ins2.indicator_value)),'-' ) from indicators_service ins2 where ins2.months_id = 12 and ins2.years_id = (ty.years_id) and ins2.indicators_detail_id = ind.indicators_detail_id ) end as "3", case when mis.risk_section_id in (3,4) THEN (select IFNULL( IF (IsNumeric(ins2.indicator_value)= 1,ROUND(ROUND(sum(ins2.indicator_value),2)/2,2),IsSpace(ins2.indicator_value)),'-' ) from indicators_service ins2 where ins2.months_id = 1 and ins2.years_id = ty.years_id and ins2.indicators_detail_id = ind.indicators_detail_id ) else (select IFNULL( IF (IsNumeric(ins2.indicator_value)= 1,ROUND(sum(ins2.indicator_value),2),IsSpace(ins2.indicator_value)),'-' ) from indicators_service ins2 where ins2.months_id = 1 and ins2.years_id = ty.years_id and ins2.indicators_detail_id = ind.indicators_detail_id ) end as "4", case when mis.risk_section_id in (3,4) THEN (select IFNULL( IF (IsNumeric(ins2.indicator_value)= 1,ROUND(ROUND(sum(ins2.indicator_value),2)/2,2),IsSpace(ins2.indicator_value)),'-' ) from indicators_service ins2 where ins2.months_id = 2 and ins2.years_id = ty.years_id and ins2.indicators_detail_id = ind.indicators_detail_id ) else (select IFNULL( IF (IsNumeric(ins2.indicator_value)= 1,ROUND(sum(ins2.indicator_value),2),IsSpace(ins2.indicator_value)),'-' ) from indicators_service ins2 where ins2.months_id = 2 and ins2.years_id = ty.years_id and ins2.indicators_detail_id = ind.indicators_detail_id ) end as "5", case when mis.risk_section_id in (3,4) THEN (select IFNULL( IF (IsNumeric(ins2.indicator_value)= 1,ROUND(ROUND(sum(ins2.indicator_value),2)/2,2),IsSpace(ins2.indicator_value)),'-' ) from indicators_service ins2 where ins2.months_id = 3 and ins2.years_id = ty.years_id and ins2.indicators_detail_id = ind.indicators_detail_id ) else (select IFNULL( IF (IsNumeric(ins2.indicator_value)= 1,ROUND(sum(ins2.indicator_value),2),IsSpace(ins2.indicator_value)),'-' ) from indicators_service ins2 where ins2.months_id = 3 and ins2.years_id = ty.years_id and ins2.indicators_detail_id = ind.indicators_detail_id ) end as "6", case when mis.risk_section_id in (3,4) THEN (select IFNULL( IF (IsNumeric(ins2.indicator_value)= 1,ROUND(ROUND(sum(ins2.indicator_value),2)/2,2),IsSpace(ins2.indicator_value)),'-' ) from indicators_service ins2 where ins2.months_id = 4 and ins2.years_id = ty.years_id and ins2.indicators_detail_id = ind.indicators_detail_id ) else (select IFNULL( IF (IsNumeric(ins2.indicator_value)= 1,ROUND(sum(ins2.indicator_value),2),IsSpace(ins2.indicator_value)),'-' ) from indicators_service ins2 where ins2.months_id = 4 and ins2.years_id = ty.years_id and ins2.indicators_detail_id = ind.indicators_detail_id ) end as "7", case when mis.risk_section_id in (3,4) THEN (select IFNULL( IF (IsNumeric(ins2.indicator_value)= 1, ROUND(ROUND(sum(ins2.indicator_value),2)/2,2),IsSpace(ins2.indicator_value)),'-' ) from indicators_service ins2 where ins2.months_id = 5 and ins2.years_id = ty.years_id and ins2.indicators_detail_id = ind.indicators_detail_id ) else (select IFNULL( IF (IsNumeric(ins2.indicator_value)= 1, ROUND(sum(ins2.indicator_value),2),IsSpace(ins2.indicator_value)) ,'-' ) from indicators_service ins2 where ins2.months_id = 5 and ins2.years_id = ty.years_id and ins2.indicators_detail_id = ind.indicators_detail_id ) end as "8", case when mis.risk_section_id in (3,4) THEN (select IFNULL( IF (IsNumeric(ins2.indicator_value)= 1,ROUND(ROUND(sum(ins2.indicator_value),2)/2,2),IsSpace(ins2.indicator_value)),'-' ) from indicators_service ins2 where ins2.months_id = 6 and ins2.years_id = ty.years_id and ins2.indicators_detail_id = ind.indicators_detail_id ) else (select IFNULL( IF (IsNumeric(ins2.indicator_value)= 1,ROUND(sum(ins2.indicator_value),2),IsSpace(ins2.indicator_value)),'-' ) from indicators_service ins2 where ins2.months_id = 6 and ins2.years_id = ty.years_id and ins2.indicators_detail_id = ind.indicators_detail_id ) end as "9", case when mis.risk_section_id in (3,4) THEN (select IFNULL( IF (IsNumeric(ins2.indicator_value)= 1,ROUND(ROUND(sum(ins2.indicator_value),2)/2,2),IsSpace(ins2.indicator_value)),'-' ) from indicators_service ins2 where ins2.months_id = 7 and ins2.years_id = ty.years_id and ins2.indicators_detail_id = ind.indicators_detail_id ) else (select IFNULL( IF (IsNumeric(ins2.indicator_value)= 1,ROUND(sum(ins2.indicator_value),2),IsSpace(ins2.indicator_value)),'-' ) from indicators_service ins2 where ins2.months_id = 7 and ins2.years_id = ty.years_id and ins2.indicators_detail_id = ind.indicators_detail_id ) end as "10", case when mis.risk_section_id in (3,4) THEN (select IFNULL( IF (IsNumeric(ins2.indicator_value)= 1,ROUND(ROUND(sum(ins2.indicator_value),2)/2,2),IsSpace(ins2.indicator_value)),'-' ) from indicators_service ins2 where ins2.months_id = 8 and ins2.years_id = ty.years_id and ins2.indicators_detail_id = ind.indicators_detail_id ) else (select IFNULL( IF (IsNumeric(ins2.indicator_value)= 1,ROUND(sum(ins2.indicator_value),2),IsSpace(ins2.indicator_value)),'-' ) from indicators_service ins2 where ins2.months_id = 8 and ins2.years_id = ty.years_id and ins2.indicators_detail_id = ind.indicators_detail_id ) end as "11", case when mis.risk_section_id in (3,4) THEN (select IFNULL( IF (IsNumeric(ins2.indicator_value)= 1,ROUND(ROUND(sum(ins2.indicator_value),2)/2,2),IsSpace(ins2.indicator_value)),'-' ) from indicators_service ins2 where ins2.months_id = 9 and ins2.years_id = ty.years_id and ins2.indicators_detail_id = ind.indicators_detail_id ) else (select IFNULL( IF (IsNumeric(ins2.indicator_value)= 1,ROUND(sum(ins2.indicator_value),2),IsSpace(ins2.indicator_value)),'-' ) from indicators_service ins2 where ins2.months_id = 9 and ins2.years_id = ty.years_id and ins2.indicators_detail_id = ind.indicators_detail_id ) end as "12" from indicators_detail ind LEFT JOIN target_year ty on ind.indicators_detail_id = ty.indicators_detail_id LEFT JOIN indicators_group ing on ing.indicators_group_id = ind.indicators_group_id LEFT JOIN indicators_type ind_type on ind_type.indicators_type_id = ing.indicators_type_id LEFT JOIN map_indicators_section mis on mis.indicators_detail_id = ind.indicators_detail_id LEFT JOIN risk_section rs on rs.risk_section_id = mis.risk_section_id where ind.active =1 and ing.active =1 and ty.active = 1 and ty.years_id = """ + str(year) +""" """); result = DBSession.execute(sql); ulit = Utility(); for row in result: rsGroupType = listGroupHash.get(row['indicators_type_id']); a='-' ;b= '-' ;c= '-' ;d= '-' ;e= '-' ;f= '-' ;g= '-' ;h= '-' ;i= '-' ;j= '-' ;k= '-' ;l= '-' ; a = ulit.numValue(row['1']); b = ulit.numValue(row['2']); c = ulit.numValue(row['3']); d = ulit.numValue(row['4']); e = ulit.numValue(row['5']); f = ulit.numValue(row['6']); g = ulit.numValue(row['7']); h = ulit.numValue(row['8']); i = ulit.numValue(row['9']); j = ulit.numValue(row['10']); k = ulit.numValue(row['11']); l = ulit.numValue(row['12']); if(rsGroupType is None): indictor = { 'indicators_detail_id' : row['indicators_detail_id'], 'detail' : row['detail'], 'target' : row['target'], '1' : a,'2' : b,'3' : c,'4' : d,'5' : e,'6' : f, '7' : g,'8' : h,'9' : i,'10' : j,'11' : k,'12' : l }; indicators = {}; indicators[row['indicators_detail_id'] ] = indictor; indicators0 = {}; indicators0[row['indicators_group_id']] = {'id': row['indicators_group_id'], 'name' : row['indicators_group_name'] , 'indicators' : indicators }; listGroupHash[row['indicators_type_id']] = {'id': row['indicators_group_id'], 'name' : row['indicators_type_name'] , 'indicators_group' : indicators0 }; else: group_type = rsGroupType['id']; indicators0 = rsGroupType.get('indicators_group'); if(indicators0 is None): pass; else: group = indicators0.get(row['indicators_group_id']); if(group is None): indictor = { 'indicators_detail_id' : row['indicators_detail_id'], 'detail' : row['detail'], 'target' : row['target'], '1' : a,'2' : b,'3' : c,'4' : d,'5' : e,'6' : f, '7' : g,'8' : h,'9' : i,'10' : j,'11' : k,'12' : l }; indicators = {}; indicators[row['indicators_detail_id'] ] = indictor; indicators0[ row['indicators_group_id'] ] = {'id': row['indicators_group_id'], 'name' : row['indicators_group_name'] , 'indicators' : indicators }; pass; else: indicators_group = group['id']; indicators = group['indicators']; indictor1 = { 'indicators_detail_id' : row['indicators_detail_id'], 'detail' : row['detail'], 'target' : row['target'], '1' : a,'2' : b,'3' : c,'4' : d,'5' : e,'6' : f, '7' : g,'8' : h,'9' : i,'10' : j,'11' : k,'12' : l }; indicators[ row['indicators_detail_id'] ] = indictor1; group['indicators'] = indicators; indicators0[row['indicators_group_id']] = group; pass; return listGroupHash;
def listAll(cls): return DBSession.query(cls).order_by(cls.fiscal_year).all();
def remove(self): DBSession.delete(self); DBSession.flush() ;
def listByGroup(cls,group): return DBSession.query(cls).filter( cls.expenses_list_id==str(group) ).order_by(cls.expenses_list_id ).all();
def listAll(cls): return DBSession.query(cls).filter( cls.active == 1 ).order_by(cls.years_id).all();
def listReport1Indicator(cls,sectionid=None ,year=None,month = None): listHash = {}; if(sectionid ): sql = text(""" select ind.indicators_detail_id, ind.indicators_group_id, ing.indicators_group_name, ind.detail, ty.target, mis.risk_section_id, rs.description as section, (select IFNULL( IF (IsNumeric(ins2.indicator_value)= 1,ROUND(ROUND(sum(ins2.indicator_value),2),2),IsSpace(ins2.indicator_value)),'-' ) from indicators_service ins2 where ins2.months_id = 10 and ins2.years_id = (ty.years_id) and ins2.indicators_detail_id = ind.indicators_detail_id and ins2.risk_section_id = rs.risk_section_id ) as "1", (select IFNULL( IF (IsNumeric(ins2.indicator_value)= 1,ROUND(ROUND(sum(ins2.indicator_value),2),2),IsSpace(ins2.indicator_value)),'-' ) from indicators_service ins2 where ins2.months_id = 11 and ins2.years_id = (ty.years_id) and ins2.indicators_detail_id = ind.indicators_detail_id and ins2.risk_section_id = rs.risk_section_id ) as "2", (select IFNULL( IF (IsNumeric(ins2.indicator_value)= 1,ROUND(ROUND(sum(ins2.indicator_value),2),2),IsSpace(ins2.indicator_value)),'-' ) from indicators_service ins2 where ins2.months_id = 12 and ins2.years_id = (ty.years_id) and ins2.indicators_detail_id = ind.indicators_detail_id and ins2.risk_section_id = rs.risk_section_id ) as "3", (select IFNULL( IF (IsNumeric(ins2.indicator_value)= 1,ROUND(ROUND(sum(ins2.indicator_value),2),2),IsSpace(ins2.indicator_value)),'-' ) from indicators_service ins2 where ins2.months_id = 1 and ins2.years_id = (ty.years_id) and ins2.indicators_detail_id = ind.indicators_detail_id and ins2.risk_section_id = rs.risk_section_id ) as "4", (select IFNULL( IF (IsNumeric(ins2.indicator_value)= 1,ROUND(ROUND(sum(ins2.indicator_value),2),2),IsSpace(ins2.indicator_value)),'-' ) from indicators_service ins2 where ins2.months_id = 2 and ins2.years_id = (ty.years_id) and ins2.indicators_detail_id = ind.indicators_detail_id and ins2.risk_section_id = rs.risk_section_id ) as "5", (select IFNULL( IF (IsNumeric(ins2.indicator_value)= 1,ROUND(ROUND(sum(ins2.indicator_value),2),2),IsSpace(ins2.indicator_value)),'-' ) from indicators_service ins2 where ins2.months_id = 3 and ins2.years_id = (ty.years_id) and ins2.indicators_detail_id = ind.indicators_detail_id and ins2.risk_section_id = rs.risk_section_id ) as "6", (select IFNULL( IF (IsNumeric(ins2.indicator_value)= 1,ROUND(ROUND(sum(ins2.indicator_value),2),2),IsSpace(ins2.indicator_value)),'-' ) from indicators_service ins2 where ins2.months_id = 4 and ins2.years_id = (ty.years_id) and ins2.indicators_detail_id = ind.indicators_detail_id and ins2.risk_section_id = rs.risk_section_id ) as "7", (select IFNULL( IF (IsNumeric(ins2.indicator_value)= 1,ROUND(ROUND(sum(ins2.indicator_value),2),2),IsSpace(ins2.indicator_value)),'-' ) from indicators_service ins2 where ins2.months_id = 5 and ins2.years_id = (ty.years_id) and ins2.indicators_detail_id = ind.indicators_detail_id and ins2.risk_section_id = rs.risk_section_id ) as "8", (select IFNULL( IF (IsNumeric(ins2.indicator_value)= 1,ROUND(ROUND(sum(ins2.indicator_value),2),2),IsSpace(ins2.indicator_value)),'-' ) from indicators_service ins2 where ins2.months_id = 6 and ins2.years_id = (ty.years_id) and ins2.indicators_detail_id = ind.indicators_detail_id and ins2.risk_section_id = rs.risk_section_id ) as "9", (select IFNULL( IF (IsNumeric(ins2.indicator_value)= 1,ROUND(ROUND(sum(ins2.indicator_value),2),2),IsSpace(ins2.indicator_value)),'-' ) from indicators_service ins2 where ins2.months_id = 7 and ins2.years_id = (ty.years_id) and ins2.indicators_detail_id = ind.indicators_detail_id and ins2.risk_section_id = rs.risk_section_id ) as "10", (select IFNULL( IF (IsNumeric(ins2.indicator_value)= 1,ROUND(ROUND(sum(ins2.indicator_value),2),2),IsSpace(ins2.indicator_value)),'-' ) from indicators_service ins2 where ins2.months_id = 8 and ins2.years_id = (ty.years_id) and ins2.indicators_detail_id = ind.indicators_detail_id and ins2.risk_section_id = rs.risk_section_id ) as "11", (select IFNULL( IF (IsNumeric(ins2.indicator_value)= 1,ROUND(ROUND(sum(ins2.indicator_value),2),2),IsSpace(ins2.indicator_value)),'-' ) from indicators_service ins2 where ins2.months_id = 9 and ins2.years_id = (ty.years_id) and ins2.indicators_detail_id = ind.indicators_detail_id and ins2.risk_section_id = rs.risk_section_id ) as "12" from indicators_detail ind LEFT JOIN target_year ty on ind.indicators_detail_id = ty.indicators_detail_id LEFT JOIN indicators_group ing on ing.indicators_group_id = ind.indicators_group_id LEFT JOIN map_indicators_section mis on mis.indicators_detail_id = ind.indicators_detail_id LEFT JOIN risk_section rs on rs.risk_section_id = mis.risk_section_id where -- mis.risk_section_id = 11 and ind.active =1 and ing.active =1 and ty.active = 1 and rs.risk_section_id = """ + str( sectionid) + """ and ty.years_id = """ + str(year) +""" """); result = DBSession.execute(sql); ulit = Utility(); for row in result: rs = listHash.get(row['indicators_group_id']); #a=0;b=0;c=0;d=0;e=0;f=0;g=0;h=0;i=0;j=0;k=0;l=0; a='-' ;b= '-' ;c= '-' ;d= '-' ;e= '-' ;f= '-' ;g= '-' ;h= '-' ;i= '-' ;j= '-' ;k= '-' ;l= '-' ; a = ulit.numValue(row['1']); b = ulit.numValue(row['2']); c = row['3']; #ulit.numValue(row['3']); d = ulit.numValue(row['4']); e = ulit.numValue(row['5']); f = ulit.numValue(row['6']); g = ulit.numValue(row['7']); h = ulit.numValue(row['8']); i = ulit.numValue(row['9']); j = ulit.numValue(row['10']); k = ulit.numValue(row['11']); l = ulit.numValue(row['12']); if(rs is None): indictor = { 'indicators_detail_id' : row['indicators_detail_id'], 'detail' : row['detail'], 'target' : row['target'], '1' : a,'2' : b,'3' : c,'4' : d,'5' : e,'6' : f, '7' : g,'8' : h,'9' : i,'10' : j,'11' : k,'12' : l }; indicators = {}; indicators[row['indicators_detail_id'] ] = indictor; listHash[row['indicators_group_id']] = {'id': row['indicators_group_id'], 'name' : row['indicators_group_name'] , 'indicators' : indicators }; else: indicators_group_id = rs['id'] ; indicators1 = rs.get ('indicators'); indictor1 = { 'indicators_detail_id' : row['indicators_detail_id'], 'detail' : row['detail'], 'target' : row['target'], '1' : a,'2' : b,'3' : c,'4' : d,'5' : e,'6' : f, '7' : g,'8' : h,'9' : i,'10' : j,'11' : k,'12' : l }; indicators1[ row['indicators_detail_id'] ] = indictor1; rs['indicators'] = indicators1; listHash[row['indicators_group_id']] = rs ; #indictor1 = indicators1.get(row['indicators_detail_id'] ); return listHash;
def listAll(cls): return DBSession.query(cls).order_by(cls.school_class_id).all()
def listPrimarySchool(cls): return DBSession.query(cls).filter(cls.high_school == str(0)).order_by( cls.school_id).all()
def save(self): DBSession.add(self); DBSession.flush() ;
def update(self): DBSession.update(self);
def save(self): DBSession.add(self) DBSession.flush()
def listAll(cls): return DBSession.query(cls).order_by(cls.description).all();
def remove(self): DBSession.delete(self) DBSession.flush()
def listAll(cls): return DBSession.query(cls).order_by(cls.school_class_id).all();
def listAll(cls): return DBSession.query(cls).filter( cls.expenses_list_id != '0' ).order_by(cls.expenses_list_id).all();
def listPrimarySchool(cls): return DBSession.query(cls).filter( cls.high_school==str(0) ).order_by(cls.school_id).all();
def listAll(cls): return DBSession.query(cls).order_by(cls.years_id).all();
def listAll(cls): return DBSession.query(cls).order_by(cls.expenses_sub_list_id).all();
def querySummary(cls, fiscalyear=None, division=None, section=None, status=None, projectType=None, start=0, limit=25): sql = "where 1=1 " if (fiscalyear): sql = sql + " and project.fiscal_year = " + fiscalyear if (projectType): sql = sql + " and project_type_id = " + projectType if (division): sql = sql + " and project.division_id = " + division if (section): sql = sql + " and project.section_id = " + section if (status): sql = sql + " and project.project_status_id = " + status sql = text("""select d.description as division , sum( d.allbudget) as allbudget, sum( d.budget) as budget, sum( d.maintenance_funds_budget) as maintenance_funds_budget, sum( d.budget_other_from) as budget_other_from FROM ( select division.description, IFNULL(project.budget, 0) + IFNULL(project.maintenance_funds_budget,0) +IFNULL(project.budget_other_from ,0) as allbudget, IFNULL(project.budget, 0) as budget, IFNULL(project.maintenance_funds_budget,0) as maintenance_funds_budget, IFNULL(project.budget_other_from ,0) as budget_other_from from division left join project on division.division_id = project.division_id """ + sql + """ ) d group by d.description """) result = DBSession.execute(sql) list = [] for row in result: list.append({ 'division': row['division'], 'allBudget': row['allbudget'], 'budget': row['budget'], 'maintenance_funds_budget': row['maintenance_funds_budget'], 'budget_other_from': row['budget_other_from'], u'งบประมาณ': row['budget'], u'งบประมาณอื่น': row['budget_other_from'], u'เงินบำรุง': row['maintenance_funds_budget'] }) return list
def getById(cls, id): return DBSession.query(cls).get(str(id))
def listAll(cls): return DBSession.query(cls).order_by(cls.book_type_id).all();
def update(self): DBSession.update(self)
def getByBookId(cls,id): return DBSession.query(cls).filter(cls.book_id == id).all();
def listAll(cls): return DBSession.query(cls).order_by(cls.description).all()
def listAll(cls): return DBSession.query(cls).order_by(cls.dental_child_dev_center_service_id).all();