Exemplo n.º 1
0
    def results(self):
        report_id = self.request.matchdict.get('report_id')
        rep = Report.load(report_id)
        page = self.request.GET.get('page', 1)
        limit = self.request.GET.get('rows', 100) # get how many rows we want to have into the grid
        sidx = self.request.GET.get('sidx', None)  # get index row - i.e. user click to sort
        sord = self.request.GET.get('sord', 'asc')  # get the direction

        rpt_start_dt = self.request.GET.get('rpt_start_dt') if self.request.GET.get('rpt_start_dt') else util.str_today()
        rpt_end_dt = self.request.GET.get('rpt_end_dt') if self.request.GET.get('rpt_end_dt') else util.str_today()
        rpt_campaign_id = self.request.GET.get('rpt_campaign_id') if 'rpt_campaign_id' in self.request.GET else ''
        rpt_company_id = self.request.GET.get('rpt_company_id') if 'rpt_company_id' in self.request.GET else ''
        rpt_user_id = self.request.GET.get('rpt_user_id') if 'rpt_user_id' in self.request.GET else ''
        rpt_product_id = self.request.GET.get('rpt_product_id') if 'rpt_product_id' in self.request.GET else ''
        rpt_vendor_id = self.request.GET.get('rpt_vendor_id') if 'rpt_vendor_id' in self.request.GET else ''
        rpt_p0 = self.request.GET.get('rpt_p0') if 'rpt_p0' in self.request.GET else ''
        rpt_p1 = self.request.GET.get('rpt_p1') if 'rpt_p1' in self.request.GET else ''
        rpt_p2 = self.request.GET.get('rpt_p2') if 'rpt_p2' in self.request.GET else ''

        sql = rep.sql.format(enterprise_id=self.enterprise_id,
                             vendor_id=self.request.ctx.user.vendor_id,
                             rpt_start_dt=rpt_start_dt,
                             rpt_end_dt=rpt_end_dt,
                             rpt_campaign_id=rpt_campaign_id,
                             rpt_company_id=rpt_company_id,
                             rpt_user_id=rpt_user_id,
                             rpt_product_id=rpt_product_id,
                             rpt_vendor_id=rpt_vendor_id,
                             rpt_p0=rpt_p0,
                             rpt_p1=rpt_p1,
                             rpt_p2=rpt_p2)

        count = db.get_value('select count(0) from (%s) x' % sql)
        total_pages = 0
        if count > 0:
            total_pages = math.ceil(int(count)/int(limit))
            total_pages = total_pages if total_pages > 1 else 1

        page = min(page, total_pages)

        start = max(int(limit)*int(page)-int(limit), 0)  # // do not put $limit*($page - 1)
        limit = util.nvl(limit, 'all')
        results = db.get_list(sql +
                              (' ORDER BY %s %s ' % (sidx, sord) if sidx else '') +
                              ' LIMIT {limit} offset {start}'.format(limit=limit,
                                                                     start=start))
        response = {
            'page': page,
            'total': int(total_pages),
            'records': int(count)}

        rows = []
        for res_row in results:
            rows.append({'id': str(res_row[0]),
                         'cell': list([unicode(util.nvl(i)) for i in res_row])})

        response['rows'] = rows
        return json.dumps(response)