Ejemplo n.º 1
0
 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();
Ejemplo n.º 2
0
    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
Ejemplo n.º 3
0
 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
Ejemplo n.º 4
0
 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();
Ejemplo n.º 5
0
 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();
Ejemplo n.º 6
0
 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
Ejemplo n.º 7
0
 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 ;
Ejemplo n.º 8
0
 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;    
     
Ejemplo n.º 9
0
 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 ;
Ejemplo n.º 10
0
 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
Ejemplo n.º 11
0
 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 ;
Ejemplo n.º 12
0
 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
Ejemplo n.º 13
0
 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;
Ejemplo n.º 14
0
 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
Ejemplo n.º 15
0
 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
Ejemplo n.º 16
0
 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();
Ejemplo n.º 17
0
 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();
Ejemplo n.º 18
0
 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 ; 
Ejemplo n.º 19
0
 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 ; 
Ejemplo n.º 20
0
 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
Ejemplo n.º 21
0
 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
Ejemplo n.º 22
0
    def listReport2Indicator(cls, year=None):
        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
                        }

                    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

        return listGroupHash
Ejemplo n.º 23
0
    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
Ejemplo n.º 24
0
    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
Ejemplo n.º 25
0
 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;
Ejemplo n.º 26
0
    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;