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