class RootController(TGController):
def __init__(self):
self.util = Utility()
@expose('computer.templates.computer.index')
def index(self):
reload(sys)
sys.setdefaultencoding("utf-8")
print "Index maintenance"
"""Handle the front-page."""
set_lang("th")
session['lang'] = "th"
session.save()
userid = ""
sectionid = ""
level = "1"
#Admin; 0 user;
if request.identity:
userid = request.identity['repoze.who.userid']
section = app_model.UserRiskSection.getByUserName(userid)
if (section):
sectionid = section.risk_section_id
section = app_model.RiskSection.listBySectionbyId(sectionid)
if (section):
userid = section.description
level = "0"
print "section : " + str(sectionid)
else:
#redirect('/computer/add');
pass
log.info("computer")
#print "user : " + str(userid);
return dict(page='computer',
user=str(userid),
sectionid=str(sectionid),
level=level)
@expose('json')
def listComputerTypes(self, **kw):
self.listLevel = app_model.ComputerTypes.listAll()
self.listDataRiskLevel = []
# self.listDataRiskLevel.append({'id':'0','name':'*'});
if (self.listLevel):
for value in self.listLevel:
self.listDataRiskLevel.append({
'id': value.computer_types_id,
'name': value.description
})
return dict(root=self.listDataRiskLevel,
total=str(len(self.listDataRiskLevel)))
@expose('json')
def listCardType(self, **kw):
self.listLevel = app_model.CardTypes.listAll()
self.listDataRiskLevel = []
# self.listDataRiskLevel.append({'id':'0','name':'*'});
if (self.listLevel):
for value in self.listLevel:
self.listDataRiskLevel.append({
'id': value.card_types_id,
'name': value.description
})
return dict(root=self.listDataRiskLevel,
total=str(len(self.listDataRiskLevel)))
@expose('json')
def saveComputer(self, **kw):
computers_id = self.util.isValue(kw.get('computers_id'))
computer_name = self.util.isValue(kw.get('computer_name'))
risk_section_id = self.util.isValue(kw.get('risk_section_id'))
computer_types_id = self.util.isValue(kw.get('computer_types_id'))
description = self.util.isValue(kw.get('description'))
location = self.util.isValue(kw.get('location'))
active = self.util.isValue(kw.get('active'))
return dict(root='')
def __init__(self):
self.util = Utility()
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;
class RootController(TGController):
def __init__(self):
self.util = Utility()
@expose('indicator.templates.indicator.index')
def index(self):
reload(sys)
sys.setdefaultencoding("utf-8")
set_lang("th")
session['lang'] = "th"
session.save()
sectionid = ""
userid = ""
level = "1"
#Admin; 0 user;
if request.identity:
userid = request.identity['repoze.who.userid']
section = app_model.UserRiskSection.getByUserName(userid)
if (section):
sectionid = section.risk_section_id
section = app_model.RiskSection.listBySectionbyId(sectionid)
if (section):
userid = section.description
level = "0"
print "section : " + str(sectionid)
redirect('/indicator/add')
log.info("indicator")
dates = datetime.now()
dyear = dates.strftime("%Y")
dmonth = dates.strftime("%m")
year = str(int(dyear) + 543)
month = str(dmonth)
return dict(page='indicator',
user=str(userid),
sectionid=str(sectionid),
level=level,
year=year,
month=month)
@expose('indicator.templates.indicator.add')
def add(self):
reload(sys)
sys.setdefaultencoding("utf-8")
set_lang("th")
session['lang'] = "th"
session.save()
sectionid = ""
userid = ""
level = "1"
#Admin; 0 user;
if request.identity:
userid = request.identity['repoze.who.userid']
section = app_model.UserRiskSection.getByUserName(userid)
if (section):
sectionid = section.risk_section_id
section = app_model.RiskSection.listBySectionbyId(sectionid)
if (section):
userid = section.description
level = "0"
print "section : " + str(sectionid)
else:
redirect('/indicator/')
log.info("indicator")
dates = datetime.now()
dyear = dates.strftime("%Y")
dmonth = dates.strftime("%m")
#if(dmonth >= 10):
# dyear = int(dyear) + 1;
year = str(int(dyear) + 543)
month = str(dmonth)
#log.info("year : " + str(year) );
#log.info("month : " + str(month) );
return dict(page='indicator',
user=str(userid),
sectionid=str(sectionid),
level=level,
year=year,
month=month)
def saveLogView(self):
self.ip = request.environ.get("X_FORWARDED_FOR",
request.environ["REMOTE_ADDR"])
self.userid = ''
if request.identity:
self.userid = request.identity['repoze.who.userid']
self.logview = app_model.LogviewReport()
self.logview.saveLogview(str(self.userid), str(self.ip), '2')
@expose('indicator.templates.indicator.report1')
def report1(self, **kw):
reload(sys)
sys.setdefaultencoding("utf-8")
set_lang("th")
session['lang'] = "th"
session.save()
year = self.util.isValue(kw.get('year'))
print "year : %s" % year
self.team_id = self.util.numValue(kw.get('team_id'))
log.info("team id " + str(self.team_id))
disabledSelect = False
level = "1"
userid = None
sectionTeamId = 0
sectionid = 0
if request.identity:
userid = request.identity['repoze.who.userid']
section = app_model.UserRiskSection.getByUserName(userid)
if (section):
sectionid = section.risk_section_id
self.team_id = sectionid
disabledSelect = True
else:
userid = None
log.info("team id " + str(self.team_id))
if year is None:
year = 2558
listYear = self.util.getRangeYear(year)
self.teamName = ""
self.listTeam = app_model.RiskSection.listAll()
self.listTeamCrom = []
for temp in self.listTeam:
if (int(self.team_id) == int(temp.risk_section_id)):
self.teamName = temp.description
self.listTeamCrom.append({
"risk_team_id": temp.risk_section_id,
"description": temp.description,
"selected": True
})
else:
self.listTeamCrom.append({
"risk_team_id": temp.risk_section_id,
"description": temp.description,
"selected": False
})
listHash = app_model.IndicatorsService.listReport1Indicator(
self.team_id, year)
#log_view_report
self.saveLogView()
return dict(page='indicator',
listteam=self.listTeamCrom,
util=self.util,
year=year,
listYear=listYear,
teamName=self.teamName,
disabledSelect=disabledSelect,
listHash=listHash)
#return dict(page='risk',util=self.util,year=year,month=month,listMonth=listMonth,listYear = listYear,section=section,pro_clinic=pro_clinic ,pro_physic=pro_physic,priority10=priority10,priority10inphysic = priority10InPhysic,startDate = startDate,stopDate= stopDate);
@expose('indicator.templates.indicator.report2')
def report2(self, **kw):
reload(sys)
sys.setdefaultencoding("utf-8")
set_lang("th")
session['lang'] = "th"
session.save()
year = self.util.isValue(kw.get('year'))
disabledSelect = False
sectionTeamId = 0
log.info(year)
if year is None:
year = 2558
listYear = self.util.getRangeYear(year)
listHash = app_model.IndicatorsService.listReport2Indicator(year)
#log_view_report
self.saveLogView()
return dict(page='indicator',
year=year,
listYear=listYear,
listHash=listHash)
#return dict(page='risk',util=self.util,year=year,month=month,listMonth=listMonth,listYear = listYear,section=section,pro_clinic=pro_clinic ,pro_physic=pro_physic,priority10=priority10,priority10inphysic = priority10InPhysic,startDate = startDate,stopDate= stopDate);
@expose('json')
def updateIndicator(self, **kw):
#print kw;
df = loads(request.body, encoding=request.charset)
data = df['root']
years_id = session.get('years_id')
months_id = session.get('months_id')
for value in data:
print value['indicator_value']
#years_id = value['years_id'];
#months_id = value['months_id'];
indicators_service = str(
self.util.valueNull(value['indicators_service_id']))
indicators_detail_id = value['indicators_detail_id']
risk_section_id = value['risk_section_id']
indicator_value = value['indicator_value']
years_id = value['years_id']
if (len(indicators_service) == 0):
indicatorsService = app_model.IndicatorsService()
indicatorsService.indicators_detail_id = indicators_detail_id
indicatorsService.years_id = years_id
indicatorsService.months_id = months_id
indicatorsService.risk_section_id = risk_section_id
indicatorsService.indicator_value = indicator_value
log.info("save service indicators:")
indicatorsService.save()
else:
indicatorsService = app_model.IndicatorsService.getById(
indicators_service)
indicatorsService.indicator_value = indicator_value
log.info("update service indicators:")
#print request.POST['root'].getall();
#print request.GET.get['indicator_value'];
return dict(success=True, message="update success")
@expose()
def updateIndicator1(self, **kw):
print kw
#print request.headers;
#print request.body ;
#print request.GET.get['indicator_value'];
#return dict(success= True,message="update success");
response.headers['Content-type'] = "application/json"
return {
"success": True,
'message': "update success"
}
@expose('json')
def listIndicatorBySection(self, **kw):
reload(sys)
sys.setdefaultencoding("utf-8")
set_lang("th")
session['lang'] = "th"
session.save()
sectionid = self.util.isValue(kw.get('riskSection'))
years_id = self.util.isValue(kw.get('year'))
months_id = self.util.isValue(kw.get('month'))
start_year = self.util.isValue(kw.get('year'))
print sectionid
print years_id
print months_id
dates = datetime.now()
dyear = dates.strftime("%Y")
dmonth = dates.strftime("%m")
if (years_id is None):
years_id = str(int(dyear) + 543)
if (months_id is None):
months_id = str(dmonth)
session['months_id'] = months_id
session['years_id'] = years_id
session.save()
#sectionid = 2;
#years_id = 2556;
#months_id = 11;
#if ( int(months_id) >9 ) :
# #years_id = int(years_id) + 1;
# years_id = int(years_id) - 1;
#log.info(str(years_id));
self.List = app_model.IndicatorsService.listIndicatorBySection(
sectionid, years_id, start_year, months_id)
return dict(root=self.List, total=str(len(self.List)))
@expose('json')
def listSection(self, **kw):
section_id = self.util.isValue(kw.get('section_id'))
self.list = []
if (section_id is None):
self.listType = app_model.RiskSection.listAll()
self.list.append({
'id': '0',
'name': '*'
})
else:
self.listType = app_model.RiskSection.listBySection(section_id)
if (self.listType):
for value in self.listType:
self.list.append({
'id': value.risk_section_id,
'name': value.description
})
return dict(root=self.list, total=str(len(self.list)))
@expose('json')
def listMonths(self, **kw):
log.info("list month")
self.listValue = app_model.Months.listAll()
self.List = []
if (self.listValue):
for value in self.listValue:
self.List.append({
'id': value.months_id,
'name': value.month
})
return dict(root=self.List, total=str(len(self.List)))
@expose('json')
def listYears(self, **kw):
self.listValue = app_model.Years.listAll()
self.List = []
# self.List.append({'id':'0','name':'*'});
if (self.listValue):
for value in self.listValue:
self.List.append({
'id': value.years_id,
'name': value.year
})
return dict(root=self.List, total=str(len(self.List)))
def __init__(self):
self.util = Utility()
self.defaultyear = 2557
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;
class AdminRiskManageController(AdminController):
#allow_only = has_permission('manage');
#allow_only = predicates.has_permission('manage',msg='Only administrators can access.') #.not_anonymous() #
def __init__(self):
self.util = Utility()
self.defaultyear = 2557
@expose('risk.templates.risk.admin.index')
def index(self, **kw):
reload(sys)
sys.setdefaultencoding("utf-8")
if not request.identity:
login_counter = request.environ.get('repoze.who.logins', 0) + 1
redirect('/login',
params=dict(came_from='/', __logins=login_counter))
return dict(page='admin')
@expose('risk.templates.risk.admin.sectionmanage')
def sectionmanage(self, **kw):
reload(sys)
sys.setdefaultencoding("utf-8")
if not request.identity:
login_counter = request.environ.get('repoze.who.logins', 0) + 1
redirect('/login',
params=dict(came_from='/', __logins=login_counter))
return dict(page='admin')
@expose('risk.templates.risk.admin.sectionteams')
def sectionteams(self, **kw):
reload(sys)
sys.setdefaultencoding("utf-8")
if not request.identity:
login_counter = request.environ.get('repoze.who.logins', 0) + 1
redirect('/login',
params=dict(came_from='/', __logins=login_counter))
return dict(page='admin')
@expose('risk.templates.risk.admin.programs')
def programs(self, **kw):
reload(sys)
sys.setdefaultencoding("utf-8")
if not request.identity:
login_counter = request.environ.get('repoze.who.logins', 0) + 1
redirect('/login',
params=dict(came_from='/', __logins=login_counter))
return dict(page='admin')
@expose('risk.templates.risk.admin.risklevel')
def riskLevel(self, **kw):
reload(sys)
sys.setdefaultencoding("utf-8")
if not request.identity:
login_counter = request.environ.get('repoze.who.logins', 0) + 1
redirect('/login',
params=dict(came_from='/', __logins=login_counter))
return dict(page='admin')
@expose('risk.templates.risk.admin.usermanage')
def usermanage(self, **kw):
reload(sys)
sys.setdefaultencoding("utf-8")
if not request.identity:
login_counter = request.environ.get('repoze.who.logins', 0) + 1
redirect('/login',
params=dict(came_from='/', __logins=login_counter))
return dict(page='admin')
@expose('json')
def saveSection(self, **kw):
reload(sys)
sys.setdefaultencoding("utf-8")
self.message = "sucess"
self.success = True
try:
log.info("create section")
log.info(kw)
self.section_id = self.util.isValue(kw.get('risk_section_id'))
self.section_name = self.util.isValue(kw.get('detail_section'))
if (self.section_id is not None):
log.info("update section")
section = app_model.RiskSection.listBySectionbyId(
self.section_id)
section.description = self.section_name
else:
section = app_model.RiskSection()
log.info("add section")
section.description = self.section_name
section.save()
print self.section_name
except Exception, exception:
log.info("error : " + str(exception))
print exception
self.message = "fail"
self.success = False
log.info("create risk")
log.info(kw)
return dict(success=self.success, message=self.message)
def __init__(self):
self.util = Utility()
self.projectToExcel = ProjectToExcel()
class RootController(TGController):
def __init__(self):
self.util = Utility()
self.projectToExcel = ProjectToExcel()
@expose('project.templates.project.index')
def index(self):
print "Index maintenance"
"""Handle the front-page."""
set_lang("th")
session['lang'] = "th"
session.save()
userid = ""
if request.identity:
userid = request.identity['repoze.who.userid']
else:
redirect('/project/summary')
log.info("project")
#print "user : "******"Index maintenance"
"""Handle the front-page."""
set_lang("th")
session['lang'] = "th"
session.save()
userid = ""
if request.identity:
userid = request.identity['repoze.who.userid']
#print "user : "******"application/ms-excel")
def ExportProjectToExcel(self, **kw):
# print kw;
fiscalyear = self.util.isValue(kw.get('fiscalyear'))
division = self.util.isValue(kw.get('division'))
section = self.util.isValue(kw.get('section'))
status = self.util.isValue(kw.get('status'))
projectType = self.util.isValue(kw.get('projectType'))
self.listType = app_model.Project.search(fiscalyear, division, section,
status, projectType)
self.list = []
#self.list.append({'id':'0','name':'*'});
if (self.listType):
for value in self.listType:
allBudget = value.budget + value.budget_other + value.maintenance_funds_budget
self.list.append({
'project_id': value.project_id,
'project_name': value.project_name,
'project_budget': value.budget,
'user_name': value.owner,
'project_status': value.project_status.project_status_name,
'department': value.department.description,
'division': value.division.description,
'section': value.section.description,
'project_type': value.project_type.project_type_name,
'projectType': value.project_type.project_type_id,
'division_id': value.division.division_id,
'section_id': value.section.section_id,
'detail': value.detail,
'start_date': value.start_date,
'stop_date': value.stop_date,
'project_status_id':
value.project_status.project_status_id,
'fiscal_year': value.fiscal_year,
'budget_other': value.budget_other,
'budget_other_from': value.budget_other_from,
'maintenance_funds_budget': value.maintenance_funds_budget,
'plantype_id': value.plantype_id,
'plantype': value.plantype.plantype_name,
'allBudget': allBudget
})
pathfile = self.projectToExcel.exportToExcel(self.list)
print pathfile
response.content_type = 'application/ms-excel'
response.headers[
"Content-Disposition"] = "attachment;filename=project.xls"
file = open(pathfile, 'rb')
read_data = file.read()
return read_data
# return dict(root = self.list,total=str(len(self.list)));
@expose('json')
def listProjectType(self, **kw):
self.listType = app_model.ProjectType.listAll()
self.list = []
self.list.append({
'id': '0',
'name': '*'
})
if (self.listType):
for value in self.listType:
self.list.append({
'id': value.project_type_id,
'name': value.project_type_name
})
return dict(root=self.list, total=str(len(self.list)))
@expose('json')
def deleteProject(self, **kw):
print "deleteProject"
#print kw;
self.success = True
self.message = "success"
projectId = self.util.isValue(kw.get('project_id'))
try:
if (projectId):
self.project = app_model.Project.getById(projectId)
if (self.project):
self.project.remove()
else:
log.info("error : remove : " + str(projectId))
self.message = "fail"
self.success = False
else:
log.info("error : remove : " + str(projectId))
self.message = "fail"
self.success = False
except Exception, exception:
log.info("error : " + str(exception))
self.message = "fail"
self.success = False
return dict(success=self.success, message=self.message)
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 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