def __init__(self):
self.util = Utility()
def __init__(self):
self.util = Utility()
self.defaultyear = 2557
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 __init__(self):
self.util = Utility()
self.projectToExcel = ProjectToExcel()
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