addons/survey/report/survey_analysis_report.py

   1 # -*- encoding: utf-8 -*-
   2 ##############################################################################
   3 #
   4 #    OpenERP, Open Source Management Solution
   5 #    Copyright (C) 2004-2009 Tiny SPRL (<http://tiny.be>). All Rights Reserved
   6 #    $Id$
   7 #
   8 #    This program is free software: you can redistribute it and/or modify
   9 #    it under the terms of the GNU General Public License as published by
  10 #    the Free Software Foundation, either version 3 of the License, or
  11 #    (at your option) any later version.
  12 #
  13 #    This program is distributed in the hope that it will be useful,
  14 #    but WITHOUT ANY WARRANTY; without even the implied warranty of
  15 #    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
  16 #    GNU General Public License for more details.
  17 #
  18 #    You should have received a copy of the GNU General Public License
  19 #    along with this program.  If not, see <http://www.gnu.org/licenses/>.
  20 #
  21 ##############################################################################
  22
  23 import pooler
  24 from report.interface import report_rml
  25 from tools import to_xml
  26 import tools
  27
  28 class survey_analysis(report_rml):
  29     def create(self, cr, uid, ids, datas, context):
  30         rml ="""<document filename="Survey Analysis Report.pdf">
  31                 <template pageSize="(595.0,842.0)" title="Test" author="Martin Simon" allowSplitting="20">
  32                     <pageTemplate id="first">
  33                       <frame id="first" x1="57.0" y1="57.0" width="481" height="728"/>
  34                 </pageTemplate>
  35                   </template>
  36                   <stylesheet>
  37                     <blockTableStyle id="Table1">
  38                       <blockAlignment value="LEFT"/>
  39                       <blockValign value="TOP"/>
  40                       <lineStyle kind="LINEBELOW" colorName="#e6e6e6"/>
  41                     </blockTableStyle>
  42                     <blockTableStyle id="Table2">
  43                       <blockAlignment value="LEFT"/>
  44                       <blockValign value="TOP"/>
  45                     </blockTableStyle>
  46                     <blockTableStyle id="Table3">
  47                       <blockAlignment value="LEFT"/>
  48                       <lineStyle kind="LINEBELOW" colorName="#e6e6e6" start="1,0" stop="2,-1"/>
  49                       <blockValign value="TOP"/>
  50                     </blockTableStyle>
  51                     <blockTableStyle id="Table4">
  52                       <blockAlignment value="LEFT"/>
  53                       <blockValign value="TOP"/>
  54                       <lineStyle kind="LINEBELOW" colorName="#000000" start="0,-1" stop="1,-1"/>
  55                     </blockTableStyle>
  56                     <blockTableStyle id="Table5">
  57                       <blockAlignment value="LEFT"/>
  58                       <blockValign value="TOP"/>
  59                       <lineStyle kind="LINEBELOW" colorName="#8f8f8f" start="0,-1" stop="1,-1"/>
  60                     </blockTableStyle>
  61                     <blockTableStyle id="Table_heading">
  62                       <blockAlignment value="LEFT"/>
  63                       <blockValign value="TOP"/>
  64                       <lineStyle kind="LINEBEFORE" colorName="#e6e6e6" start="0,0" stop="-1,-1"/>
  65                       <lineStyle kind="LINEAFTER" colorName="#e6e6e6" start="0,0" stop="-1,-1"/>
  66                       <lineStyle kind="LINEBELOW" colorName="#e6e6e6" start="0,0" stop="-1,-1"/>
  67                       <lineStyle kind="LINEABOVE" colorName="#e6e6e6" start="0,0" stop="-1,-1"/>
  68                     </blockTableStyle>
  69                     <blockTableStyle id="Table_head_2">
  70                       <blockAlignment value="LEFT"/>
  71                       <blockValign value="TOP"/>
  72                       <lineStyle kind="LINEBEFORE" colorName="#e6e6e6" start="0,0" stop="-1,-1"/>
  73                       <lineStyle kind="LINEAFTER" colorName="#e6e6e6" start="0,0" stop="-1,-1"/>
  74                       <lineStyle kind="LINEBELOW" colorName="#e6e6e6" start="0,0" stop="-1,-1"/>
  75                       <lineStyle kind="LINEABOVE" colorName="#e6e6e6" start="0,0" stop="-1,-1"/>
  76                     </blockTableStyle>
  77                     <initialize>
  78                       <paraStyle name="all" alignment="justify"/>
  79                     </initialize>
  80                     <paraStyle name="answer_right" alignment="RIGHT" fontName="helvetica" fontSize="09.0" leftIndent="2.0"/>
  81                     <paraStyle name="Standard1" fontName="helvetica-bold" alignment="RIGHT" fontSize="09.0"/>
  82                     <paraStyle name="Standard" alignment="LEFT" fontName="Helvetica-Bold" fontSize="11.0"/>
  83                     <paraStyle name="header1" fontName="Helvetica" fontSize="11.0"/>
  84                     <paraStyle name="response" fontName="Helvetica-oblique" fontSize="9.5"/>
  85                     <paraStyle name="page" fontName="helvetica" fontSize="11.0" leftIndent="0.0"/>
  86                     <paraStyle name="question" fontName="helvetica-boldoblique" fontSize="10.0" leftIndent="3.0"/>
  87                     <paraStyle name="answer_bold" fontName="Helvetica-Bold" fontSize="09.0" leftIndent="2.0"/>
  88                     <paraStyle name="answer" fontName="helvetica" fontSize="09.0" leftIndent="2.0"/>
  89                     <paraStyle name="Title" fontName="helvetica" fontSize="20.0" leading="15" spaceBefore="6.0" spaceAfter="6.0" alignment="CENTER"/>
  90                     <paraStyle name="terp_tblheader_General_Centre" fontName="Helvetica-Bold" fontSize="9.0" leading="10" alignment="CENTER" spaceBefore="6.0" spaceAfter="6.0"/>
  91                     <paraStyle name="terp_default_Centre_8" fontName="Helvetica" fontSize="9.0" leading="10" alignment="CENTER" spaceBefore="0.0" spaceAfter="0.0"/>
  92                     <paraStyle name="P2" fontName="Helvetica" fontSize="14.0" leading="15" spaceBefore="6.0" spaceAfter="6.0"/>
  93                   </stylesheet>
  94                   <images/>
  95                   <story>
  96                     <para style="Title"><u>Response Summary</u></para>
  97                     <para style="Standard"><font></font></para>
  98                     <para style="P2">
  99                       <font color="white"> </font>
 100                     </para>"""
 101         surv_obj = pooler.get_pool(cr.dbname).get('survey')
 102         for survey in surv_obj.browse(cr, uid, ids):
 103             rml += """<blockTable colWidths="280.0,100.0,120.0" style="Table_heading">
 104                           <tr>
 105                             <td>
 106                               <para style="terp_tblheader_General_Centre">Survey Title </para>
 107                             </td>
 108                             <td>
 109                               <para style="terp_tblheader_General_Centre">Total Started Survey </para>
 110                             </td>
 111                             <td>
 112                               <para style="terp_tblheader_General_Centre">Total Completed Survey </para>
 113                             </td>
 114                           </tr>
 115                           </blockTable>
 116                           <blockTable colWidths="280.0,100.0,120.0" style="Table_head_2">
 117                           <tr>
 118                             <td>
 119                               <para style="terp_default_Centre_8">""" + to_xml(tools.ustr(survey.title)) + """</para>
 120                             </td>
 121                             <td>
 122                               <para style="terp_default_Centre_8">""" + str(survey.tot_start_survey) + """</para>
 123                             </td>
 124                             <td>
 125                               <para style="terp_default_Centre_8">""" + str(survey.tot_comp_survey) + """</para>
 126                             </td>
 127                           </tr>
 128                         </blockTable>"""
 129             for page in survey.page_ids:
 130                 rml += """ <blockTable colWidths="500" style="Table4">
 131 #                              <tr>
 132 #                                <td><para style="page">Page :- """ + to_xml(tools.ustr(page.title)) + """</para></td>
 133 #                              </tr>
 134 #                           </blockTable>"""
 135                 for que in page.question_ids:
 136                     rml +="""<blockTable colWidths="500" style="Table5">
 137                               <tr>
 138                                 <td><para style="question">Que : """  + to_xml(tools.ustr(que.question)) + """</para></td>
 139                               </tr>
 140                              </blockTable>"""
 141                     cols_widhts = []
 142                     if que.type in ['matrix_of_choices_only_one_ans','matrix_of_choices_only_multi_ans']:
 143                         cols_widhts.append(200)
 144                         for col in range(0, len(que.column_heading_ids) + 1):
 145                             cols_widhts.append(float(300 / (len(que.column_heading_ids) + 1)))
 146                         colWidths = ",".join(map(tools.ustr, cols_widhts))
 147                         matrix_ans = ['',]
 148                         for col in que.column_heading_ids:
 149                             if col.title not in matrix_ans:
 150                                 matrix_ans.append(col.title)
 151                         rml+="""<blockTable colWidths=" """ + colWidths + """ " style="Table1"><tr>"""
 152                         for mat_col in matrix_ans:
 153                             rml+="""<td><para style="response">""" + to_xml(tools.ustr(mat_col)) + """</para></td>"""
 154                         rml+="""<td><para style="response">Response Count</para></td>
 155                                 </tr>"""
 156                         last_col = cols_widhts[-1]
 157                         for ans in que.answer_choice_ids:
 158                             rml+="""<tr><td><para style="answer">""" + to_xml(tools.ustr(ans.answer)) + """</para></td>"""
 159                             cr.execute("select count(id) from survey_response_answer sra where sra.answer_id = %d"%(ans.id))
 160                             tot_res = cr.fetchone()[0]
 161                             cr.execute("select count(id) ,sra.answer from survey_response_answer sra where sra.answer_id = %d group by sra.answer" % ans.id)
 162                             calc_res = cr.dictfetchall()
 163                             for mat_col in range(1, len(matrix_ans)):
 164                                 percantage = 0
 165                                 cal_count = 0
 166                                 for cal in calc_res:
 167                                     if cal['answer'] == matrix_ans[mat_col]:
 168                                         cal_count = cal['count']
 169                                 if tot_res:
 170                                     percantage = float(cal_count)*100 / tot_res
 171                                 if percantage:
 172                                     rml += """<td color="#FFF435"><para style="answer_bold">""" + tools.ustr(percantage) +"% (" + tools.ustr(cal_count) + """)</para></td>"""
 173                                 else:
 174                                     rml += """<td color="#FFF435"><para style="answer">""" + tools.ustr(percantage) +"% (" + tools.ustr(cal_count) + """)</para></td>"""
 175                             rml+="""<td><para style="response">""" + tools.ustr(tot_res) + """</para></td>
 176                                 </tr>"""
 177                         rml+="""</blockTable>"""
 178                         if que.is_comment_require:
 179                             cr.execute("select count(id) from survey_response_line where question_id = %d and comment != ''"% que.id)
 180                             tot_res = cr.fetchone()[0]
 181                             rml+="""<blockTable colWidths=" """+ str(500 - last_col) +"," + str(last_col) + """ " style="Table1"><tr><td><para style="answer_right">""" + to_xml(tools.ustr(que.comment_label)) + """</para></td>
 182                                     <td><para style="answer">""" + tools.ustr(tot_res) + """</para></td></tr></blockTable>"""
 183
 184                     elif que.type in['multiple_choice_only_one_ans', 'multiple_choice_multiple_ans', 'multiple_textboxes','date_and_time','date']:
 185                         rml +="""<blockTable colWidths="280.0,120,100.0" style="Table1">"""
 186                         rml += """ <tr>
 187                              <td> <para style="Standard"> </para></td>
 188                              <td> <para style="response">Response Percentage </para></td>
 189                              <td> <para style="response">Response Count</para></td>
 190                          </tr>"""
 191                         for ans in que.answer_choice_ids:
 192                             rml+="""<tr><td><para style="answer">""" + to_xml(tools.ustr(ans.answer)) + """</para></td>
 193                                     <td><para style="answer">""" + tools.ustr(ans.average) + """%</para></td>
 194                                     <td><para style="answer">""" + tools.ustr(ans.response) + """</para></td></tr>"""
 195                         rml+="""</blockTable>"""
 196                         if que.is_comment_require:
 197                             if que.make_comment_field:
 198                                 cr.execute("select count(id) from survey_response_line where question_id = %d and comment != ''"% que.id)
 199                                 tot_res = cr.fetchone()[0]
 200                                 tot_avg = 0.00
 201                                 if que.tot_resp:
 202                                     tot_avg = round(float(tot_res * 100)/ que.tot_resp,2)
 203                                 rml+="""<blockTable colWidths="280.0,120,100.0" style="Table1"><tr><td><para style="answer">""" +to_xml(tools.ustr(que.comment_label)) + """</para></td>
 204                                         <td><para style="answer">""" + str(tot_avg) + """%</para></td>
 205                                         <td><para style="answer">""" + tools.ustr(tot_res) + """</para></td></tr></blockTable>"""
 206                             else:
 207                                 cr.execute("select count(id) from survey_response_line where question_id = %d and comment != ''"% que.id)
 208                                 tot_res = cr.fetchone()[0]
 209                                 rml+="""<blockTable colWidths="400.0,100.0" style="Table1"><tr><td><para style="answer_right">""" + to_xml(tools.ustr(que.comment_label)) + """</para></td>
 210                                         <td><para style="answer">""" + tools.ustr(tot_res) + """</para></td></tr></blockTable>"""
 211
 212                     elif que.type in['single_textbox']:
 213                         cr.execute("select count(id) from survey_response_line where question_id = %d and single_text!=''" % que.id)
 214                         rml +="""<blockTable colWidths="400.0,100.0" style="Table1">
 215                              <tr>
 216                                  <td> <para style="Standard"> </para></td>
 217                                  <td> <para style="response">Response Count</para></td>
 218                              </tr>
 219                             <tr><td><para style="answer"></para></td>
 220                                 <td><para style="answer">""" + tools.ustr(cr.fetchone()[0]) + """ </para></td></tr>
 221                             </blockTable>"""
 222                     elif que.type in['comment']:
 223                         cr.execute("select count(id) from survey_response_line where question_id = %d and comment !=''" % que.id)
 224                         rml +="""<blockTable colWidths="400.0,100.0" style="Table1">
 225                              <tr>
 226                                  <td> <para style="Standard"> </para></td>
 227                                  <td> <para style="response">Response Count</para></td>
 228                              </tr>
 229                             <tr><td><para style="answer"></para></td>
 230                                 <td><para style="answer">""" + tools.ustr(cr.fetchone()[0]) + """ </para></td></tr>
 231                             </blockTable>"""
 232                     elif que.type in['rating_scale']:
 233                         cols_widhts.append(200)
 234                         for col in range(0,len(que.column_heading_ids) + 2):
 235                             cols_widhts.append(float(300 / (len(que.column_heading_ids) + 2)))
 236                         colWidths = ",".join(map(tools.ustr, cols_widhts))
 237                         matrix_ans = ['',]
 238                         for col in que.column_heading_ids:
 239                             if col.title not in matrix_ans:
 240                                 matrix_ans.append(col.title)
 241                         rml+="""<blockTable colWidths=" """ + colWidths + """ " style="Table1"><tr>"""
 242                         for mat_col in matrix_ans:
 243                             rml+="""<td><para style="response">""" + to_xml(tools.ustr(mat_col)) + """</para></td>"""
 244                         rml+="""<td><para style="response">Rating Average</para></td>
 245                                 <td><para style="response">Response Count</para></td>
 246                                 </tr>"""
 247                         for ans in que.answer_choice_ids:
 248                             rml+="""<tr><td><para style="answer">""" + to_xml(tools.ustr(ans.answer)) + """</para></td>"""
 249                             res_count = 0
 250                             rating_weight_sum = 0
 251                             for mat_col in range(1, len(matrix_ans)):
 252                                 cr.execute("select count(sra.answer_id) from survey_response_line sr, survey_response_answer sra\
 253                                      where sr.id = sra.response_id and  sra.answer_id = %d and sra.answer ='%s'" % (ans.id,matrix_ans[mat_col]))
 254                                 tot_res = cr.fetchone()[0]
 255                                 cr.execute("select count(sra.answer_id),sqc.rating_weight from survey_response_line sr, survey_response_answer sra ,\
 256                                         survey_question_column_heading sqc where sr.id = sra.response_id and \
 257                                         sqc.question_id = sr.question_id  and sra.answer_id = %d and sqc.title ='%s'\
 258                                         group by sra.answer_id,sqc.rating_weight" % (ans.id,matrix_ans[mat_col]))
 259                                 col_weight =  cr.fetchone()
 260                                 if not col_weight :
 261                                     col_weight= (0,0)
 262                                 res_count = col_weight[0]
 263                                 if tot_res:
 264                                     rating_weight_sum += col_weight[1] * tot_res
 265                                     tot_per = round((float(tot_res) * 100) / int(res_count), 2)
 266                                 else:
 267                                     tot_res = 0
 268                                     tot_per = 0.0
 269                                 if tot_res:
 270                                     rml += """<td><para style="answer_bold">""" + tools.ustr(tot_per) + "%(" + tools.ustr(tot_res) + """)</para></td>"""
 271                                 else:
 272                                     rml += """<td><para style="answer">""" + tools.ustr(tot_per)+"%(" + tools.ustr(tot_res) + """)</para></td>"""
 273                             percantage = 0.00
 274                             if res_count:
 275                                 percantage = round((float(rating_weight_sum)/res_count), 2)
 276                             rml+="""<td><para style="answer">""" + tools.ustr(percantage) + """</para></td>
 277                                 <td><para style="answer">""" + tools.ustr(res_count) + """</para></td></tr>"""
 278                         rml+="""</blockTable>"""
 279                     elif que.type in['matrix_of_drop_down_menus']:
 280                         for column in que.column_heading_ids:
 281                             rml += """<blockTable colWidths="500" style="Table1"><tr>
 282                                 <td><para style="answer">""" + to_xml(tools.ustr(column.title)) + """</para></td></tr></blockTable>"""
 283                             menu_choices = column.menu_choice.split('\n')
 284                             cols_widhts = []
 285                             cols_widhts.append(200)
 286                             for col in range(0, len(menu_choices) + 1):
 287                                 cols_widhts.append(float(300 / (len(menu_choices) + 1)))
 288                             colWidths = ",".join(map(tools.ustr, cols_widhts))
 289                             rml +="""<blockTable colWidths=" """ + colWidths + """ " style="Table1"><tr>
 290                                 <td><para style="response"></para></td>"""
 291                             for menu in menu_choices:
 292                                 rml += """<td><para style="response">""" + to_xml(tools.ustr(menu)) + """</para></td>"""
 293                             rml += """<td><para style="response">Response Count</para></td></tr>"""
 294                             cr.execute("select count(id), sra.answer_id from survey_response_answer sra \
 295                                      where sra.answer='%s' group by sra.answer_id "  % (column.title))
 296                             res_count = cr.dictfetchall()
 297                             cr.execute("select count(sra.id),sra.value_choice, sra.answer_id, sra.answer from survey_response_answer sra \
 298                                  where sra.answer='%s'  group by sra.value_choice ,sra.answer_id, sra.answer" % (column.title))
 299                             calc_percantage = cr.dictfetchall()
 300                             for ans in que.answer_choice_ids:
 301                                 rml+="""<tr><td><para style="answer_right">""" + to_xml(tools.ustr(ans.answer)) + """</para></td>"""
 302                                 for mat_col in range(0, len(menu_choices)):
 303                                     calc = 0
 304                                     response = 0
 305                                     for res in res_count:
 306                                         if res['answer_id'] == ans.id: response = res['count']
 307                                     for per in calc_percantage:
 308                                         if ans.id == per['answer_id'] and menu_choices[mat_col] == per['value_choice']:
 309                                             calc = per['count']
 310                                     percantage = 0.00
 311                                     if calc and response:
 312                                         percantage = (float(calc)* 100) / response
 313                                     if calc:
 314                                         rml+="""<td><para style="answer_bold">""" +tools.ustr(percantage)+"% (" +  tools.ustr(calc) + """)</para></td>"""
 315                                     else:
 316                                         rml+="""<td><para style="answer">""" +tools.ustr(percantage)+"% (" +  tools.ustr(calc) + """)</para></td>"""
 317                                 response = 0
 318                                 for res in res_count:
 319                                     if res['answer_id'] == ans.id: response = res['count']
 320                                 rml += """<td><para style="response">""" + tools.ustr(response) + """</para></td></tr>"""
 321                             rml += """</blockTable>"""
 322                     elif que.type in['numerical_textboxes']:
 323                         rml +="""<blockTable colWidths="240.0,20,100.0,70,70.0" style="Table1">
 324                              <tr>
 325                              <td> <para style="Standard"> </para></td>
 326                              <td> <para style="Standard"> </para></td>
 327                              <td> <para style="response">Response Average</para></td>
 328                              <td> <para style="response">Response Total</para></td>
 329                              <td> <para style="response">Response Count</para></td>
 330                          </tr>"""
 331                         for ans in que.answer_choice_ids:
 332                             cr.execute("select answer from survey_response_answer where answer_id=%d group by answer" % ans.id)
 333                             tot_res = cr.dictfetchall()
 334                             total = 0
 335                             for  tot in tot_res:
 336                                 total += int(tot['answer'])
 337                             per = 0.00
 338                             if len(tot_res):
 339                                 per = round((float(total) / len(tot_res)),2)
 340                             rml+="""<tr><td><para style="answer">""" + to_xml(tools.ustr(ans.answer)) + """</para></td>
 341                                     <td> <para style="Standard"> </para></td>
 342                                     <td> <para style="answer">""" + tools.ustr(per) +"""</para></td>
 343                                     <td><para style="answer">""" + tools.ustr(total) + """</para></td>
 344                                     <td><para style="answer">""" + tools.ustr(len(tot_res)) + """</para></td></tr>"""
 345                         rml+="""</blockTable>"""
 346
 347                     rml +="""<blockTable colWidths="300,100,100.0" style="Table3">
 348                         <tr>
 349                               <td><para style="Standard1"></para></td>
 350                               <td><para style="Standard1">Answered Question</para></td>
 351                               <td><para style="Standard1">""" + tools.ustr(que.tot_resp) + """</para></td>
 352                         </tr>
 353                         <tr>
 354                             <td><para style="Standard1"></para></td>
 355                             <td><para style="Standard1">Skipped Question</para></td>
 356                             <td><para style="Standard1">""" + tools.ustr(survey.tot_start_survey - que.tot_resp) + """</para></td>
 357                         </tr>
 358                         </blockTable>"""
 359             rml += """<pageBreak/>"""
 360         rml += """</story></document>"""
 361         report_type = datas.get('report_type', 'pdf')
 362         create_doc = self.generators[report_type]
 363         pdf = create_doc(rml, title=self.title)
 364         return (pdf, report_type)
 365
 366 survey_analysis('report.survey.analysis', 'survey','','')
 367
 368 # vim:expandtab:smartindent:tabstop=4:softtabstop=4:shiftwidth=4: