Beispiel #1
0
 def download_attachment(self):
     '''
     下载附件
     :return:
     '''
     all_rec = request.env['metro_park_base.time_table_data'].search_read(
         [])
     one_row = [
         '序号', '所属时刻表', '车次', '出场场段', '入场场段', '计划回库时间', '计划出库时间', '高峰时段'
     ]
     field_list = [
         'sequence', 'time_table_id', 'train_no', 'out_location',
         'back_location', 'plan_in_time', 'plan_out_time', 'high_time_train'
     ]
     # 新建一个excel文件
     file = xlwt.Workbook()
     table = file.add_sheet('sheet', cell_overwrite_ok=True)
     for data_i, head_sheet in enumerate(one_row):
         table.write(0, data_i, head_sheet)
     for i, data in enumerate(all_rec):
         for j in range(len(field_list)):
             table.write(i + 1, j, data.get(field_list[j], ''))
     fp = io.BytesIO()
     file.save(fp)
     fp.seek(0)
     data = fp.read()
     fp.close()
     name = '运行图附件.xls'
     response = request.make_response(data)
     response.headers['Content-Type'] = 'application/vnd.ms-excel'
     response.headers["Content-Disposition"] = "attachment; filename={}". \
         format(name.encode().decode('latin-1'))
     return response
Beispiel #2
0
 def consignment_stock_report_print(self):
     self.file_name = 'filename.xls'
     workbook = xlwt.Workbook()
     worksheet = workbook.add_sheet('Sheet 1')
     # style = xlwt.easyxf('font: bold True, name Arial;')
     worksheet.write_merge(
         0, 1, 0, 3, 'your data that you want to show into excelsheet')
     fp = BytesIO()
     workbook.save(fp)
     self.file_name = 'Long roll Report.xls'
     record_id = self.env['stock.consignment.report'].create(
         {
             'excel_file': base64.encodestring(fp.getvalue()),
             'file_name': self.file_name
         }, )
     fp.close()
     return {
         'view_mode': 'form',
         'res_id': record_id.id,
         'res_model': 'stock.consignment.report',
         'view_type': 'form',
         'type': 'ir.actions.act_window',
         'context': self.env.context,
         'target': 'new',
     }
Beispiel #3
0
    def download_cvi_by_userlist(self, model, id, **kw):
        dlcv_obj = request.env[model].browse(int(id))
        where_clause = generate_domain_date_and_department(dlcv_obj,
                                                           theo_sql=True)
        sql_cmd = '''select cvi.user_id,sum(diemtc),u.login,p.name from cvi inner join res_users as u on cvi.user_id = u.id inner join res_partner as p on u.partner_id = p.id %s group by cvi.user_id ,u.login,p.name'''
        sql_cmd = sql_cmd % (
            (' where ' + where_clause) if where_clause else '')
        request.env.cr.execute(sql_cmd)
        rsul = request.env.cr.fetchall()
        workbook = xlwt.Workbook()
        worksheet = workbook.add_sheet('Sheet 1')
        normal_style = xlwt.easyxf("font:  name Times New Roman, height 240")
        worksheet.write(0, 0, 'STT', normal_style)
        worksheet.write(0, 1, u'Tên', normal_style)
        worksheet.write(0, 2, u'Điểm', normal_style)
        row_index = 1
        stt = 1
        for u_id, diem, login, name in rsul:
            worksheet.write(row_index, 0, stt, normal_style)
            worksheet.write(row_index, 1, login, normal_style)
            worksheet.write(row_index, 2, diem, normal_style)
            row_index += 1
            stt += 1

        response = request.make_response(
            None,
            headers=[('Content-Type', 'application/vnd.ms-excel'),
                     ('Content-Disposition',
                      'attachment; filename=table_cv_%s_%s.xls;' %
                      (request.env.user.name,
                       datetime.datetime.now().strftime('%d_%m_%H_%M')))],
        )
        workbook.save(response.stream)
        return response
Beispiel #4
0
 def download_time_table(self, time_table_id, **kwargs):
     '''
     下载运行图
     :param kwargs:
     :param time_table_id:
     :return:
     '''
     records = request.env['metro_park_base.time_table_data'].search_read([
         ('time_table_id', '=', time_table_id)
     ])
     if not records:
         return
     one_row = [
         '序号', '回库车次', '车号', '换轨时间', '接车股道', '变更股道', '洗车', '检修计划', '备注'
     ]
     field_list = ['dev_name', 'dev_no', 'miles', 'unit']
     # 新建一个excel文件
     file = xlwt.Workbook()
     table = file.add_sheet('sheet', cell_overwrite_ok=True)
     for data_i, head_sheet in enumerate(one_row):
         table.write(0, data_i, head_sheet)
     for i, data in enumerate(records):
         for j in range(len(field_list)):
             table.write(i + 1, j, data.get(field_list[j], ''))
     fp = io.BytesIO()
     file.save(fp)
     fp.seek(0)
     data = fp.read()
     fp.close()
     name = '运行图模版.xls'
     response = request.make_response(data)
     response.headers['Content-Type'] = 'application/vnd.ms-excel'
     response.headers["Content-Disposition"] = "attachment; filename={}". \
         format(name.encode().decode('latin-1'))
     return response
Beispiel #5
0
def write_all_row(fixups,dl_obj,set_cols_width,wb=None,ws_name=None,font_height=12):
    normal_style = xlwt.easyxf(generate_easyxf(height=font_height))
    needdata = {}
    if not ws_name:
        ws_name = u'First'
    if not wb:
        wb = xlwt.Workbook()
    ws = wb.add_sheet(ws_name)#cell_overwrite_ok=True
    if set_cols_width:
        for col,width in enumerate(set_cols_width):
            ws.col(col).width =  width
    fixups = OrderedDict(fixups)
    instance_dict = {}
    needdata['instance_dict'] = instance_dict
    for f_name,field_attr in fixups.items():
        a_field_dict = {}
        xrange = field_attr.get('range')
        offset = field_attr.get('offset',1)
        if callable(offset):
            offset_kargs = field_attr.get('offset_kargs',{})
            offset = offset(needdata,**offset_kargs)
        style = field_attr.get('style',normal_style)
        if xrange[0]=='auto':
            row = needdata['cr'] + offset
            xrange[0] = row
            if xrange[1] == 'auto':
                xrange[1] = row
        else:
            row = xrange[0]
        val = field_attr.get('val')
        val_func = field_attr.get('val_func')
        if val_func:
            val_kargs =  field_attr.get('val_kargs',{})
            val = val_func(ws,f_name,fixups,needdata,row,dl_obj,**val_kargs)
        func = field_attr.get('func')
        instance_dict[f_name]=a_field_dict
        a_field_dict['begin_row'] = row
        if func:
            kargs = field_attr.get('kargs',{})
            nrow = func(ws, f_name, fixups, needdata, row, dl_obj, **kargs)
            if nrow:
                cr_new = row + nrow  
                needdata['cr'] = cr_new
            a_field_dict['end_row'] = needdata['cr']
        else:
            a_field_dict['val'] = val
            if val != None:
                if len(xrange) ==2:
                    ws.write(xrange[0], xrange[1], val, style)
                elif len(xrange)==4:
                    ws.write_merge(xrange[0], xrange[1],xrange[2], xrange[3], val, style)
                needdata['cr'] = xrange[0]
        height =  field_attr.get('height',400)
        if height != None:
            ws.row(row).height_mismatch = True
            ws.row(row).height = height
    return wb
Beispiel #6
0
    def generate_hr_employee_report(self):
        workbook = xlwt.Workbook()
        worksheet = workbook.add_sheet("Employees")
        style = xlwt.easyxf("font: bold True;"
                            "align: vert centre, horiz center;"
                            "border: right 2, left 2, top 2, bottom 2;")
        style_date = xlwt.XFStyle()
        style_date.num_format_str = DF

        row, col = 0, 0
        self.add_header_label(worksheet, style, row, col)

        row = 1
        i = 0
        for emp in self.employee_ids:
            col = 0
            i += 1
            s_no = i
            name = emp.name
            id_number = emp.identification_id if emp.identification_id else ""
            operating_unit = emp.operating_unit_id.name if emp.operating_unit_id else ""
            department = emp.department_id.name if emp.department_id else ""
            job_position = emp.job_id.name if emp.job_id else ""
            line_manager = emp.line_manager.name if emp.line_manager else ""
            senior_manager = emp.senior_manager.name if emp.senior_manager else ""
            phone = emp.work_phone if emp.work_phone else ""

            worksheet.write(row, col, s_no)
            worksheet.write(row, col + 1, name)
            worksheet.write(row, col + 2, id_number)
            worksheet.write(row, col + 3, operating_unit)
            worksheet.write(row, col + 4, department)
            worksheet.write(row, col + 5, job_position)
            worksheet.write(row, col + 6, line_manager)
            worksheet.write(row, col + 7, senior_manager)
            worksheet.write(row, col + 8, phone)
            row += 1
        fp = io.BytesIO()
        workbook.save(fp)
        filename = "employees_report.xls"
        export_id = self.env["hr.employee.report"].create({
            "excel_file":
            base64.encodestring(fp.getvalue()),
            "file_name":
            filename
        })
        fp.close()
        return {
            "view_mode": "form",
            "res_id": export_id.id,
            "res_model": "hr.employee.report",
            "view_type": "form",
            "type": "ir.actions.act_window",
            "context": self._context,
            "target": "new",
        }
Beispiel #7
0
    def from_data(self, fields, rows):
        if len(rows) > 65535:
            raise UserError(
                _('There are too many rows (%s rows, limit: 65535) to export as Excel 97-2003 (.xls) format. Consider splitting the export.'
                  ) % len(rows))

        workbook = xlwt.Workbook()
        worksheet = workbook.add_sheet('Sheet 1')

        for i, fieldname in enumerate(fields):
            worksheet.write(0, i, fieldname)
            worksheet.col(i).width = 8000  # around 220 pixels

        base_style = xlwt.easyxf('align: wrap yes')
        date_style = xlwt.easyxf('align: wrap yes',
                                 num_format_str='YYYY-MM-DD')
        datetime_style = xlwt.easyxf('align: wrap yes',
                                     num_format_str='YYYY-MM-DD HH:mm:SS')

        for row_index, row in enumerate(rows):
            for cell_index, cell_value in enumerate(row):
                cell_style = base_style

                if isinstance(cell_value, bytes) and not isinstance(
                        cell_value, pycompat.string_types):
                    # because xls uses raw export, we can get a bytes object
                    # here. xlwt does not support bytes values in Python 3 ->
                    # assume this is base64 and decode to a string, if this
                    # fails note that you can't export
                    try:
                        cell_value = pycompat.to_text(cell_value)
                    except UnicodeDecodeError:
                        raise UserError(
                            _("Binary fields can not be exported to Excel unless their content is base64-encoded. That does not seem to be the case for %s."
                              ) % fields[cell_index])

                if isinstance(cell_value, pycompat.string_types):
                    cell_value = re.sub("\r", " ",
                                        pycompat.to_text(cell_value))
                    # Excel supports a maximum of 32767 characters in each cell:
                    cell_value = cell_value[:32767]
                elif isinstance(cell_value, datetime.datetime):
                    cell_style = datetime_style
                elif isinstance(cell_value, datetime.date):
                    cell_style = date_style
                worksheet.write(row_index + 1, cell_index, cell_value,
                                cell_style)

        fp = io.BytesIO()
        workbook.save(fp)
        fp.seek(0)
        data = fp.read()
        fp.close()
        return data
Beispiel #8
0
    def action_print_stock_report(self):
        ctx = dict(self.env.context) or {}
        workbook = xlwt.Workbook()
        worksheet = workbook.add_sheet('Inventory')
        column_heading_style = xlwt.easyxf('font:height 200;font:bold True;')

        row = 2
        for wizard in self:
            report_head = 'Stock Take Report'
            if wizard.inventory_date:
                report_head += ' (' + wizard.inventory_date + ')'
            worksheet.write_merge(
                0, 0, 0, 2, report_head,
                xlwt.easyxf(
                    'font:height 300; align: vertical center; align: horiz center;pattern: pattern solid, fore_color black; font: color white; font:bold True;'
                    "borders: top thin,bottom thin"))
            worksheet.write(1, 0, _('Internal Reference'),
                            column_heading_style)
            worksheet.write(1, 1, _('Product'), column_heading_style)
            worksheet.write(1, 2, _('Quantity On Hand'), column_heading_style)
            worksheet.col(0).width = 5000
            worksheet.col(1).width = 10000
            worksheet.col(2).width = 5000
            worksheet.row(0).height = 500

            ctx.update({'to_date': wizard.inventory_date})
            product_objs = self.env['product.product'].with_context(
                ctx).search([], order='name')
            for product in product_objs:
                if product.qty_available > 0:
                    if product.default_code:
                        worksheet.write(row, 0, product.default_code)
                    worksheet.write(row, 1, product.name)
                    worksheet.write(row, 2, product.qty_available)
                    row += 1

            fp = io.BytesIO()
            workbook.save(fp)
            excel_file = base64.encodestring(fp.getvalue())
            wizard.stock_report_file = excel_file
            wizard.file_name = 'Stock Take Report.xls'
            wizard.inventory_printed = True
            fp.close()
            return {
                'view_mode': 'form',
                'res_id': wizard.id,
                'res_model': 'print.stock.report',
                'view_type': 'form',
                'type': 'ir.actions.act_window',
                'context': self.env.context,
                'target': 'new',
            }
Beispiel #9
0
    def from_data(self, fields, rows, listType=None):
        if len(rows) > 65535:
            raise UserError(
                _('导出的行数过多 (%s 行, 上限为: 65535行) , 请分多次导出') % len(rows))

        workbook = xlwt.Workbook()
        worksheet = workbook.add_sheet('Sheet 1')
        header_style = xlwt.easyxf(
            'font:bold True;align: vert center, horiz center;')
        for i, fieldname in enumerate(fields):
            worksheet.write(0, i, fieldname, header_style)
            worksheet.col(i).width = self.setColumnWidth(
                fieldname)  # around 220 pixels

        base_style = xlwt.easyxf('align: wrap yes')
        date_style = xlwt.easyxf('align: wrap yes',
                                 num_format_str='YYYY-MM-DD')
        datetime_style = xlwt.easyxf('align: wrap yes',
                                     num_format_str='YYYY-MM-DD HH:mm:SS')

        for row_index, row in enumerate(rows):
            for cell_index, cell_value in enumerate(row):
                cell_style = base_style
                if isinstance(cell_value, bytes) and not isinstance(
                        cell_value, pycompat.string_types):
                    try:
                        cell_value = pycompat.to_text(cell_value)
                    except UnicodeDecodeError:
                        raise UserError(
                            _("Binary fields can not be exported to Excel unless their content is base64-encoded. That does not seem to be the case for %s."
                              ) % fields[cell_index])

                if isinstance(cell_value, pycompat.string_types):
                    cell_value = re.sub("\r", " ",
                                        pycompat.to_text(cell_value))
                    # Excel supports a maximum of 32767 characters in each cell:
                    cell_value = cell_value[:32767]
                elif isinstance(cell_value, datetime.datetime):
                    cell_style = datetime_style
                elif isinstance(cell_value, datetime.date):
                    cell_style = date_style
                worksheet.write(row_index + 1, cell_index, cell_value,
                                cell_style)

        fp = io.BytesIO()
        workbook.save(fp)
        fp.seek(0)
        data = fp.read()
        fp.close()
        return data
Beispiel #10
0
    def download_list_intern(self, **kwargs):

        invoices = request.env['intern.invoice'].search([])

        workbook = xlwt.Workbook()
        worksheet = workbook.add_sheet('Sheet 1')
        base_style = xlwt.easyxf('align: wrap yes')
        date_style = xlwt.easyxf('align: wrap yes',
                                 num_format_str='YYYY-MM-DD')

        worksheet.write(0, 0, u'Tên', base_style)
        worksheet.write(0, 1, u'CMND/Thẻ CC', base_style)
        worksheet.write(0, 2, u'Đơn hàng', base_style)
        worksheet.write(0, 3, u'Ngày lập hồ sơ', base_style)
        worksheet.write(0, 4, u'Ngày sinh', base_style)
        worksheet.write(0, 5, u'Tuổi', base_style)

        counter = 1
        for invoice in invoices:
            for intern in invoice.interns_pass:
                if intern.cmnd_or_tcc != None and invoice.date_create_letter_promotion_short != None:
                    worksheet.write(counter, 0, u'%s' % intern.name,
                                    base_style)
                    worksheet.write(counter, 1, intern.cmnd_or_tcc, base_style)
                    worksheet.write(counter, 2, u'%s' % invoice.name,
                                    base_style)
                    worksheet.write(counter, 3,
                                    invoice.date_create_letter_promotion_short,
                                    date_style)
                    worksheet.write(counter, 4, intern.date_of_birth_short,
                                    date_style)
                    worksheet.write(
                        counter, 5, u'%s' % str(
                            intern_utils.get_age_jp(
                                invoice.date_create_letter_promotion_short,
                                intern.day, intern.month, intern.year)),
                        base_style)
                    counter = counter + 1

        fp = StringIO()
        workbook.save(fp)
        fp.seek(0)
        data = fp.read()
        fp.close()
        return request.make_response(
            data,
            headers=[('Content-Disposition', content_disposition('test.xls')),
                     ('Content-Type', 'application/vnd.ms-excel')])
Beispiel #11
0
def download_cvi(dlcv_obj):
    num2alpha = dict(zip(range(0, 26), string.ascii_uppercase))
    normal_border_style_not_border = xlwt.easyxf("font:  name Times New Roman, height 240")
    normal_border_style = xlwt.easyxf("font:  name Times New Roman, height 240 ;borders: left thin,right thin, top thin, bottom thin")
    bold_style = xlwt.easyxf("font: bold on")
    department_ids = dlcv_obj.department_ids
    export_department_ids = filter_department_ids(department_ids)
    user_ids = request.env['res.users'].search([('department_id','in',export_department_ids)])
    workbook = xlwt.Workbook()
    
    offset_column = 0
    ROW_TRUNG_TAM=0
    ROW_SUM = ROW_TRUNG_TAM + 3
    KEY_COL = offset_column + 3
    VAL_COL = offset_column + 4
    ROW_TITLE = ROW_TRUNG_TAM + 5
    
    FIELDNAME_FIELDATTR = [
             ('stt',{'is_not_model_field':True,'string':u'STT'}),
             ('ngay_bat_dau',{'func':convert_date_odoo_to_str_vn_date,'width':get_width(10)}),
             ('code',{}),('tvcv_id_name',{'width':get_width(40)}),('noi_dung',{'width':get_width(40)}),
             ('diem_tvi',{}),('so_luong',{}),('so_lan',{}),
             ('diemtc',{'sum':True, 'row_sum':ROW_SUM+1,  'col_sum':VAL_COL}),
             ('diemld',{'sum':True,'row_sum':ROW_SUM, 'col_sum':VAL_COL}),
                        ]
    domain = []
    domain_date = generate_domain_date_and_department(dlcv_obj)
    print '**domain_date**'
    print '**user_ids**',user_ids
    for user_id in user_ids:
        domain_user = [('user_id','=',user_id.id),('loai_record','=',u'Công Việc')]
        domain = expression.AND([domain_user, domain_date])
        worksheet = workbook.add_sheet(user_id.name,cell_overwrite_ok=True)
        add_header_TrungTamHaTangMang(worksheet,user_id,ROW_TRUNG_TAM,offset_column,normal_border_style_not_border,bold_style,ROW_SUM,KEY_COL,VAL_COL)
        cvi_fields = request.env['cvi']._fields
        add_title(FIELDNAME_FIELDATTR, cvi_fields, offset_column, worksheet, ROW_TITLE)
        row_index = ROW_TITLE + 1
        stt = 1
        person_records = request.env['cvi'].search(domain,order='ngay_bat_dau')
        for r in person_records:#request.env['cvi'].search([]):
            add_1_cvi_for_1_person(worksheet,FIELDNAME_FIELDATTR, r,offset_column, stt, row_index, normal_border_style)
            row_index +=1
            stt +=  1
        add_sum_info(worksheet,FIELDNAME_FIELDATTR,offset_column,num2alpha,ROW_TITLE,ROW_SUM,VAL_COL,row_index)
    return workbook
Beispiel #12
0
    def export_xls(self, data, token):
        data = json.loads(data)
        title = data.get('title', 'export')
        workbook = xlwt.Workbook()
        worksheet = workbook.add_sheet(title)

        self._write_worksheet(worksheet, data)

        response = request.make_response(
            None,
            headers=[
                ('Content-Type', 'application/vnd.ms-excel'),
                ('Content-Disposition', 'attachment; filename="%s - %s.xls"' %
                 (title, datetime.now()))
            ],
            cookies={'fileToken': token})
        workbook.save(response.stream)

        return response
Beispiel #13
0
    def from_data(self, fields, rows):
        if len(rows) > 65535:
            raise UserError(
                _('There are too many rows (%s rows, limit: 65535) to export as Excel 97-2003 (.xls) format. Consider splitting the export.'
                  ) % len(rows))

        workbook = xlwt.Workbook()

        worksheet = workbook.add_sheet('Sheet 1')

        for i, fieldname in enumerate(fields):
            worksheet.write(0, i, fieldname)
            worksheet.col(i).width = 8000  # around 220 pixels

        base_style = xlwt.easyxf('align: wrap yes')
        date_style = xlwt.easyxf('align: wrap yes',
                                 num_format_str='YYYY-MM-DD')
        datetime_style = xlwt.easyxf('align: wrap yes',
                                     num_format_str='YYYY-MM-DD HH:mm:SS')

        for row_index, row in enumerate(rows):
            for cell_index, cell_value in enumerate(row):
                cell_style = base_style
                if isinstance(cell_value, basestring):
                    cell_value = re.sub("\r", " ", cell_value)
                elif isinstance(cell_value, datetime.datetime):
                    cell_style = datetime_style
                elif isinstance(cell_value, datetime.date):
                    cell_style = date_style
                worksheet.write(row_index + 1, cell_index, cell_value,
                                cell_style)

        fp = StringIO()
        workbook.save(fp)
        fp.seek(0)
        data = fp.read()
        fp.close()
        return data
Beispiel #14
0
    def export_xls(self, data, token):
        jdata = json.loads(data)
        nbr_measures = jdata['nbr_measures']
        workbook = xlwt.Workbook()
        worksheet = workbook.add_sheet(jdata['title'])
        header_bold = xlwt.easyxf(
            "font: bold on; pattern: pattern solid, fore_colour gray25;")
        header_plain = xlwt.easyxf(
            "pattern: pattern solid, fore_colour gray25;")
        bold = xlwt.easyxf("font: bold on;")

        # Step 1: writing headers
        headers = jdata['headers']

        # x,y: current coordinates
        # carry: queue containing cell information when a cell has a >= 2 height
        #      and the drawing code needs to add empty cells below
        x, y, carry = 1, 0, deque()
        for i, header_row in enumerate(headers):
            worksheet.write(i, 0, '', header_plain)
            for header in header_row:
                while (carry and carry[0]['x'] == x):
                    cell = carry.popleft()
                    for i in range(nbr_measures):
                        worksheet.write(y, x + i, '', header_plain)
                    if cell['height'] > 1:
                        carry.append({'x': x, 'height': cell['height'] - 1})
                    x = x + nbr_measures
                style = header_plain if 'expanded' in header else header_bold
                for i in range(header['width']):
                    worksheet.write(y, x + i,
                                    header['title'] if i == 0 else '', style)
                if header['height'] > 1:
                    carry.append({'x': x, 'height': header['height'] - 1})
                x = x + header['width']
            while (carry and carry[0]['x'] == x):
                cell = carry.popleft()
                for i in range(nbr_measures):
                    worksheet.write(y, x + i, '', header_plain)
                if cell['height'] > 1:
                    carry.append({'x': x, 'height': cell['height'] - 1})
                x = x + nbr_measures
            x, y = 1, y + 1

        # Step 2: measure row
        if nbr_measures > 1:
            worksheet.write(y, 0, '', header_plain)
            for measure in jdata['measure_row']:
                style = header_bold if measure['is_bold'] else header_plain
                worksheet.write(y, x, measure['measure'], style)
                x = x + 1
            y = y + 1

        # Step 3: writing data
        x = 0
        for row in jdata['rows']:
            worksheet.write(y, x, row['indent'] * '     ' + row['title'],
                            header_plain)
            for cell in row['values']:
                x = x + 1
                if cell.get('is_bold', False):
                    worksheet.write(y, x, cell['value'], bold)
                else:
                    worksheet.write(y, x, cell['value'])
            x, y = 0, y + 1

        response = request.make_response(
            None,
            headers=[('Content-Type', 'application/vnd.ms-excel'),
                     ('Content-Disposition', 'attachment; filename=table.xls;')
                     ],
            cookies={'fileToken': token})
        workbook.save(response.stream)

        return response
	def export_xls(self, filename, sale_ids, date_from, date_to, is_admin, **kw):
		is_admin = ast.literal_eval(is_admin)

		workbook = xlwt.Workbook()
		worksheet = workbook.add_sheet('Sales Report')

		sale_order_ids = False

		sales = request.env['sale.order'].sudo().search([('id','in',ast.literal_eval(sale_ids))])
		if sales:
			sale_order_ids = sales
		
		if not sales:
			# sale_order_ids = request.env['sale.order'].search([('id','in',ast.literal_eval(sale_ids))])
			sales_date = request.env['sale.order'].sudo().search([('date_order','>=',date_from),('date_order','<=',date_to)],order='date_order desc')
			if sales_date:
				sale_order_ids = sales_date
		

		# STYLES
		style_header_bold = xlwt.easyxf("font: bold on;font: name Calibri;align: wrap no")
		style_header_right = xlwt.easyxf("font: name Calibri;align: horiz right, wrap no")
		style_table_header_bold = xlwt.easyxf("font: bold on;font: name Calibri;align: horiz centre, vert centre, wrap on;borders: top thin, bottom thin, right thin;")
		style_table_row = xlwt.easyxf("font: name Calibri;align: horiz left, wrap no;borders: top thin, bottom thin, right thin;")
		style_table_row_amount = xlwt.easyxf("font: name Calibri;align: horiz right, wrap no;borders: top thin, bottom thin, right thin;", num_format_str="#,##0.00")
		style_table_total = xlwt.easyxf("pattern: pattern solid, fore_colour pale_blue;font: bold on;font: name Calibri;align: horiz left, wrap no;borders: top thin, bottom medium, right thin;")
		style_table_total_value = xlwt.easyxf("pattern: pattern solid, fore_colour pale_blue;font: bold on;font: name Calibri;align: horiz right, wrap no;borders: top thin, bottom medium, right thin;", num_format_str="#,##0.00")
		style_end_report = xlwt.easyxf("font: bold on;font: name Calibri;align: horiz left, wrap no;")
		worksheet.col(0).width = 250*12
		worksheet.col(1).width = 500*12
		worksheet.col(2).width = 250*12
		worksheet.col(3).width = 250*12
		worksheet.col(4).width = 250*12
		worksheet.col(5).width = 250*12
		worksheet.col(6).width = 250*12
		worksheet.col(7).width = 250*12
		worksheet.col(8).width = 250*12
		worksheet.col(9).width = 250*12
		worksheet.col(10).width = 250*12
		worksheet.col(11).width = 250*12
		worksheet.col(12).width = 250*12
		worksheet.col(13).width = 250*12
		worksheet.col(14).width = 250*12
		worksheet.col(15).width = 250*12
		worksheet.col(16).width = 250*12
		worksheet.col(17).width = 250*12

		# TEMPLATE HEADERS

		# TABLE HEADER
		worksheet.write(0, 0, 'Sales Agent', style_table_header_bold) # HEADER
		worksheet.write(0, 1, "Client's Name", style_table_header_bold) # HEADER
		worksheet.write(0, 2, 'City', style_table_header_bold) # HEADER
		worksheet.write(0, 3, 'Area', style_table_header_bold) # HEADER
		worksheet.write(0, 4, 'Invoice Date', style_table_header_bold) # HEADER
		worksheet.write(0, 5, 'Invoice No.', style_table_header_bold) # HEADER
		# worksheet.write(0, 6, 'Category', style_table_header_bold) # HEADER
		worksheet.write(0, 6, 'Item Code', style_table_header_bold) # HEADER
		worksheet.write(0, 7, 'Description', style_table_header_bold) # HEADER
		worksheet.write(0, 8, 'Color', style_table_header_bold) # HEADER
		worksheet.write(0, 9, 'Brand', style_table_header_bold) # HEADER
		worksheet.write(0, 10, 'Car Brand', style_table_header_bold) # HEADER
		worksheet.write(0, 11, 'Model', style_table_header_bold) # HEADER
		worksheet.write(0, 12, 'Car Model Group 1', style_table_header_bold) # HEADER
		worksheet.write(0, 13, 'Car Model Group 2', style_table_header_bold) # HEADER
		worksheet.write(0, 14, 'Car Model Group 3', style_table_header_bold) # HEADER
		worksheet.write(0, 15, 'Car Type', style_table_header_bold) # HEADER
		worksheet.write(0, 16, 'Fit', style_table_header_bold) # HEADER
		worksheet.write(0, 17, 'Parent Category', style_table_header_bold) # HEADER
		worksheet.write(0, 18, 'Category', style_table_header_bold) # HEADER
		worksheet.write(0, 19, 'Qty', style_table_header_bold) # HEADER
		worksheet.write(0, 20, 'Unit Price', style_table_header_bold) # HEADER
		worksheet.write(0, 21, 'Amount', style_table_header_bold) # HEADER
		worksheet.write(0, 22, 'Discount (%)', style_table_header_bold) # HEADER
		worksheet.write(0, 23, 'Net Sales', style_table_header_bold) # HEADER
		if is_admin == True:
			_logger.info("TRUE")
			worksheet.write(0, 24, 'Foreign Cost', style_table_header_bold) # HEADER
			worksheet.write(0, 25, 'Exchange Rate', style_table_header_bold) # HEADER
			worksheet.write(0, 26, 'Local Cost', style_table_header_bold) # HEADER
			worksheet.write(0, 27, 'Remarks', style_table_header_bold) # HEADER
		else:
			_logger.info("FALSE")
			worksheet.write(0, 24, 'Remarks', style_table_header_bold) # HEADER

		row_count = 1

		for sale in sale_order_ids:
			for line in sale.order_line:
				invoice_id = ''
				# for invoice in line.invoice_lines:
				# 	invoice_date = str(invoice.date)
				# 	invoice_no = str(invoice.name)
				# invoice_id = line.mapped('invoice_lines').mapped('invoice_id')
				for invoice in line.invoice_lines:
					invoice_id = invoice.invoice_id.id

				invoice_date = False
				invoice_no = ''
				if invoice_id:
					invoice = request.env['account.invoice'].sudo().search([('id','=',invoice_id)])
					invoice_date = invoice.date_invoice
					invoice_no = invoice.number


				worksheet.write(row_count, 0, sale.user_id.name or '', style_table_row) 
				worksheet.write(row_count, 1, sale.partner_id.name  or '', style_table_row) 
				worksheet.write(row_count, 2, sale.partner_id.city  or '', style_table_row) 
				worksheet.write(row_count, 3, sale.partner_id.partner_area_id.name  or '', style_table_row) 
				worksheet.write(row_count, 4, invoice_date or '', style_table_row) 
				worksheet.write(row_count, 5, invoice_no or '', style_table_row) 
				# worksheet.write(row_count, 6, line.product_id.categ_id.name  or '', style_table_row) 
				worksheet.write(row_count, 6, line.product_id.default_code  or '', style_table_row) 
				worksheet.write(row_count, 7, line.product_id.name  or '', style_table_row) 
				worksheet.write(row_count, 8, line.product_id.color_type  or '', style_table_row) 
				worksheet.write(row_count, 9, line.product_id.brand.name  or '', style_table_row) 
				worksheet.write(row_count, 10, line.product_id.brand_id.name  or '', style_table_row)
				worksheet.write(row_count, 11, line.product_model  or '', style_table_row)  
				worksheet.write(row_count, 12, line.product_id.carmodel_group1_id.name  or '', style_table_row)
				worksheet.write(row_count, 13, line.product_id.carmodel_group2_id.name  or '', style_table_row)
				worksheet.write(row_count, 14, line.product_id.carmodel_group3_id.name  or '', style_table_row)
				worksheet.write(row_count, 15, line.product_id.cartype_id.name  or '', style_table_row)
				worksheet.write(row_count, 16, line.product_id.fit  or '', style_table_row)
				worksheet.write(row_count, 17, line.product_id.categ_id.parent_id.name  or '', style_table_row)
				worksheet.write(row_count, 18, line.product_id.categ_id.name  or '', style_table_row)
				
				worksheet.write(row_count, 19, line.product_uom_qty  or '', style_table_row) 
				worksheet.write(row_count, 20, line.price_unit  or '', style_table_row) 
				worksheet.write(row_count, 21, line.price_total  or '', style_table_row) 
				# worksheet.write(row_count, 15, line.price_reduce  or '', style_table_row)
				worksheet.write(row_count, 22, line.discount  or '', style_table_row) 
				worksheet.write(row_count, 23, line.price_subtotal  or '', style_table_row)
				if is_admin == True:
					worksheet.write(row_count, 24, line.product_id.foreign_cost  or '', style_table_row) 
					worksheet.write(row_count, 25, line.product_id.exchange_rate  or '', style_table_row) 
					worksheet.write(row_count, 26, line.product_id.local_cost  or '', style_table_row) 
					worksheet.write(row_count, 27, sale.note  or '', style_table_row) 
				else:
					worksheet.write(row_count, 24, sale.note  or '', style_table_row) 
				row_count +=1

		response = request.make_response(None,
			headers=[('Content-Type', 'application/vnd.ms-excel'),
					('Content-Disposition', 'attachment; filename=%s;'%(filename)
					)])

		workbook.save(response.stream)

		return response
    def export_xls(self, filename, title, company_id, date_from, date_to,
                   account_id, **kw):
        company = request.env['res.company'].search([('id', '=', company_id)])
        # journal = request.env['account.journal'].search([('id', '=', journal_id)])
        account_ewt = request.env['account.account'].search(
            [('name', '=', 'Withholding Tax Expanded')], limit=1)
        # account_vendor_bill = request.env['account.invoice'].search([('journal_id.id', '=', journal_id),('state','in',('open','paid')),('date','>=',date_from),('date','<=',date_to)])
        account_vendor_bill = request.env['account.move.line'].search([
            ('account_id.id', '=', account_id), ('date', '>=', date_from),
            ('date', '<=', date_to)
        ])
        date_processed = date.today().strftime('%m-%d-%Y')
        from_report_month = datetime.strptime(date_from, '%Y-%m-%d')
        to_report_month = datetime.strptime(date_to, '%Y-%m-%d')
        user_id = request.env.user.name

        workbook = xlwt.Workbook()
        worksheet = workbook.add_sheet(title)

        # STYLES
        style_header_bold = xlwt.easyxf(
            "font: bold on;font: name Calibri;align: wrap no")
        style_header_right = xlwt.easyxf(
            "font: name Calibri;align: horiz right, wrap no")
        style_table_header_bold = xlwt.easyxf(
            "font: bold on;font: name Calibri;align: horiz centre, vert centre, wrap on;borders: top thin, bottom thin, right thin;"
        )
        style_table_row = xlwt.easyxf(
            "font: name Calibri;align: horiz left, wrap no;borders: top thin, bottom thin, right thin;"
        )
        style_table_row_amount = xlwt.easyxf(
            "font: name Calibri;align: horiz right, wrap no;borders: top thin, bottom thin, right thin;",
            num_format_str="#,##0.00")
        style_table_total = xlwt.easyxf(
            "pattern: pattern solid, fore_colour pale_blue;font: bold on;font: name Calibri;align: horiz left, wrap no;borders: top thin, bottom medium, right thin;"
        )
        style_table_total_value = xlwt.easyxf(
            "pattern: pattern solid, fore_colour pale_blue;font: bold on;font: name Calibri;align: horiz right, wrap no;borders: top thin, bottom medium, right thin;",
            num_format_str="#,##0.00")
        worksheet.col(0).width = 350 * 12
        worksheet.col(1).width = 350 * 12
        worksheet.col(2).width = 350 * 12
        worksheet.col(3).width = 500 * 12
        worksheet.col(4).width = 500 * 12
        worksheet.col(5).width = 350 * 12
        worksheet.col(7).width = 350 * 12
        worksheet.col(6).width = 350 * 12
        worksheet.col(8).width = 350 * 12
        worksheet.col(9).width = 350 * 12
        worksheet.col(10).width = 350 * 12
        worksheet.col(11).width = 350 * 12
        worksheet.col(12).width = 350 * 12
        worksheet.col(13).width = 350 * 12
        worksheet.col(14).width = 350 * 12
        worksheet.col(15).width = 350 * 12
        worksheet.col(16).width = 350 * 12
        worksheet.col(17).width = 350 * 12
        worksheet.col(18).width = 350 * 12
        worksheet.col(19).width = 350 * 12
        worksheet.col(20).width = 350 * 12

        # TEMPLATE HEADERS
        worksheet.write(0, 0, company.name, style_header_bold)  # Company Name
        worksheet.write(
            1, 0, '%s %s %s %s %s %s' %
            (company.street, company.street2, company.city,
             company.state_id.name, company.zip, company.country_id.name),
            style_header_bold)  # Company Address
        worksheet.write(2, 0, 'TIN %s' % (company.vat),
                        style_header_bold)  # Company TIN

        worksheet.write(4, 0, title, style_header_bold)  # Report Title
        worksheet.write(5, 0,
                        '%s to %s' % (from_report_month.strftime('%B %d, %Y'),
                                      to_report_month.strftime('%B %d, %Y')),
                        style_header_bold)  # Report Date

        # TABLE HEADER
        worksheet.write_merge(7, 8, 0, 0, 'REFERENCE DATE',
                              style_table_header_bold)  # HEADER
        worksheet.write_merge(7, 8, 1, 1, 'VOUCHER DATE',
                              style_table_header_bold)  # HEADER
        worksheet.write_merge(7, 8, 2, 2, 'JOURNAL TYPE',
                              style_table_header_bold)  # HEADER
        worksheet.write_merge(7, 8, 3, 3, 'REFERENCE NO.',
                              style_table_header_bold)  # HEADER
        worksheet.write_merge(7, 8, 4, 4, 'SUPPLIER NAME',
                              style_table_header_bold)  # HEADER
        worksheet.write_merge(7, 8, 5, 5, 'REGISTERED ADDRESS',
                              style_table_header_bold)  # HEADER
        worksheet.write_merge(7, 8, 6, 6, 'TIN',
                              style_table_header_bold)  # HEADER

        worksheet.write_merge(7, 7, 7, 8, 'SOURCE DOCUMENT',
                              style_table_header_bold)  # HEADER
        worksheet.write(8, 7, 'TYPE', style_table_header_bold)  # HEADER
        worksheet.write(8, 8, 'NUMBER', style_table_header_bold)  # HEADER
        worksheet.write_merge(7, 8, 9, 9, 'GROSS AMOUNT',
                              style_table_header_bold)  # HEADER
        worksheet.write_merge(7, 8, 10, 10, 'NON-VAT / EXEMPT',
                              style_table_header_bold)  # HEADER

        worksheet.write_merge(7, 8, 11, 11, 'NET OF VAT',
                              style_table_header_bold)  # HEADER
        worksheet.write_merge(7, 8, 12, 12, 'INPUT TAX (12%)',
                              style_table_header_bold)  # HEADER
        worksheet.write_merge(7, 8, 13, 13, 'INPUT TAX ALLOWED',
                              style_table_header_bold)  # HEADER
        worksheet.write_merge(7, 8, 14, 14, 'ACCOUNT TITLE',
                              style_table_header_bold)  # HEADER
        worksheet.write_merge(7, 8, 15, 15, 'PARTICULARS',
                              style_table_header_bold)  # HEADER

        worksheet.write_merge(7, 8, 16, 16, 'EXPENSE AMOUNT',
                              style_table_header_bold)  # HEADER
        worksheet.write_merge(7, 7, 17, 19, 'EXPANDED WITHOLDING TAX',
                              style_table_header_bold)  # HEADER
        worksheet.write(8, 17, 'ATC', style_table_header_bold)  # HEADER
        worksheet.write(8, 18, 'EWT RATE', style_table_header_bold)  # HEADER
        worksheet.write(8, 19, 'AMOUNT', style_table_header_bold)  # HEADER
        worksheet.write_merge(7, 8, 20, 20, 'EWT ABSORBED BY COMPANY',
                              style_table_header_bold)  # HEADER

        # TABLE ROW LINES
        # table_row_start = 9
        row_count = 9
        transaction_count = 0

        for account in account_vendor_bill:
            source_type = ''
            source_num = ''
            amount_income = 0
            amount_tax = 0
            account_title = ''
            input_tax_amount = 0

            # GET SOURCES
            if account.invoice_id:
                source_num = account.invoice_id.number
                if account.invoice_id.type == 'out_invoice':
                    source_type = 'Customer Invoice'
                elif account.invoice_id.type == 'in_invoice':
                    source_type = 'Vendor Bill'
                elif account.invoice_id.type == 'out_refund':
                    source_type = 'Customer Credit Note'
                else:
                    source_type = 'Vendor Credit Note'
            else:
                # GET PAYMENT
                # VENDOR PAYMENT
                if 'SUPP.OUT' in account.name:
                    payment = request.env['account.payment'].search(
                        [('name', '=', account.name)], limit=1)
                    if payment:
                        source_type = 'O.R'
                        source_num = payment.name
                if 'CUST.IN' in account.name:
                    payment = request.env['account.payment'].search(
                        [('name', '=', account.name)], limit=1)
                    if payment:
                        source_type = 'O.R'
                        source_num = payment.name
                        if payment.collection_receipt_id:
                            source_type = 'C.R'
                        if payment.acknowledgement_receipt_id:
                            source_type = 'C.R'
                        # if not payment.collection_receipt_id and not payment.acknowledgement_receipt_id or payment.collection_receipt_id and payment.acknowledgement_receipt_id:
                        #     source_type = 'O.R'

            # GET TAXES AMOUNT
            for tax in account.invoice_id.tax_line_ids:
                if tax.account_id == account.account_id:
                    amount_income = tax.base
                    amount_tax = tax.amount_total
                else:
                    # if tax.amount == 12:
                    input_tax_amount = tax.amount_total

            if amount_tax <= 0:
                if account.debit > 0:
                    amount_tax = account.debit
                else:
                    amount_tax = account.credit

            if account.move_id:
                for move in account.move_id.line_ids:
                    for tax in move.tax_ids:
                        if tax.account_id.id == account_ewt.id:
                            account_title = move.account_id.name

            worksheet.write(row_count, 0, '', style_table_row)
            worksheet.write(row_count, 1, account.date, style_table_row)
            worksheet.write(row_count, 2, account.move_id.journal_id.name,
                            style_table_row)
            worksheet.write(row_count, 3, account.move_id.name,
                            style_table_row)
            worksheet.write(row_count, 4, account.partner_id.name,
                            style_table_row)
            worksheet.write(
                row_count, 5, '%s %s %s %s %s %s' %
                (account.partner_id.street or '', account.partner_id.street2
                 or '', account.partner_id.city or '',
                 account.partner_id.state_id.name or '', account.partner_id.zip
                 or '', account.partner_id.country_id.name or ''),
                style_table_row)
            worksheet.write(row_count, 6, account.partner_id.vat or '',
                            style_table_row)

            worksheet.write(row_count, 7, source_type, style_table_row)
            worksheet.write(row_count, 8, source_num, style_table_row)
            worksheet.write(row_count, 9, account.invoice_id.amount_total,
                            style_table_row_amount)
            worksheet.write(row_count, 10, account.invoice_id.vat_exempt_sales,
                            style_table_row_amount)

            worksheet.write(row_count, 11, account.invoice_id.vat_sales,
                            style_table_row_amount)
            worksheet.write(row_count, 12, input_tax_amount,
                            style_table_row_amount)
            worksheet.write(row_count, 13, '', style_table_row)
            worksheet.write(row_count, 14, account_title, style_table_row)
            worksheet.write(row_count, 15, account.invoice_id.x_description
                            or '', style_table_row_amount)

            worksheet.write(row_count, 16, account.invoice_id.amount_untaxed,
                            style_table_row_amount)
            worksheet.write(row_count, 17,
                            account.tax_line_id.ewt_structure_id.name or '',
                            style_table_row)
            worksheet.write(row_count, 18, account.tax_line_id.amount,
                            style_table_row_amount)
            worksheet.write(row_count, 19, amount_tax, style_table_row_amount)
            worksheet.write(row_count, 20, '', style_table_row)

            row_count += 1
            transaction_count += 1

        table_total_start = row_count

        # TABLE TOTALS
        # worksheet.write_merge(table_total_start, table_total_start, 0, 7, 'TOTAL', style_table_total)
        # worksheet.write(table_total_start, 8, '-', style_table_total_value)
        # worksheet.write(table_total_start, 9, '', style_table_total_value)
        # worksheet.write(table_total_start, 10, '-', style_table_total_value)
        # worksheet.write(table_total_start, 11, '-', style_table_total_value)
        # worksheet.write(table_total_start, 12, '', style_table_total_value)
        # worksheet.write(table_total_start, 13, '', style_table_total_value)
        # worksheet.write(table_total_start, 14, '-', style_table_total_value)
        # worksheet.write(table_total_start, 15, '', style_table_total_value)
        # worksheet.write(table_total_start, 16, '', style_table_total_value)
        # worksheet.write(table_total_start, 17, '-', style_table_total_value)
        # worksheet.write(table_total_start, 18, '-', style_table_total_value)

        worksheet.write(0, 20, 'No. of Transaction: %s' % (transaction_count),
                        style_header_right)
        worksheet.write(1, 20, 'Date Processed: %s' % (date_processed),
                        style_header_right)
        worksheet.write(2, 20, 'Processed By: %s' % (user_id),
                        style_header_right)
        # worksheet.write(3, 18, '%s'%(account_invoice_payable), style_header_right)

        response = request.make_response(
            None,
            headers=[('Content-Type', 'application/vnd.ms-excel'),
                     ('Content-Disposition',
                      'attachment; filename=%s;' % (filename))])

        workbook.save(response.stream)

        return response
    def gen_excel_file(self):

        fmt_bold_left = xlwt.easyxf('font: bold on')
        fmt_bold_right = xlwt.easyxf('font: bold on; align: horiz right')
        fmt_right = xlwt.easyxf('align: horiz right')
        fmt_currency = xlwt.easyxf(num_format_str="#,##0.00")
        fmt_integer = xlwt.easyxf(num_format_str="#,##0")

        for d in self:
            wb = xlwt.Workbook()
            ws = wb.add_sheet(str(d.year))

            ws.col(0).width = 256 * 20
            ws.col(1).width = 256 * 20
            for i in range(2, 5):
                ws.col(i).width = 256 * 10
            ws.col(6).width = 256 * 20
            ws.col(7).width = 256 * 20

            ri = 0
            ci = 0
            ws.write(ri, 0, "SAVING INTEREST COMPUTATION", fmt_bold_left)
            #           ws.write(ri, 18, d.company_id.name, fmt_bold_left)

            ri += 1
            ws.write(ri, 0, "Year:", fmt_bold_left)
            ws.write(ri, 1, d.year)
            ri += 1
            ws.write(ri, 0, "Rate %:", fmt_bold_left)
            ws.write(ri, 1, d.rate, xlwt.easyxf(num_format_str="#,##0.00000"))

            headers = [
                ["Member", fmt_bold_left],
                ["Account", fmt_bold_left],
                ["Q1 Average", fmt_bold_right],
                ["Q2 Average", fmt_bold_right],
                ["Q3 Average", fmt_bold_right],
                ["Q4 Average", fmt_bold_right],
                ["Total", fmt_bold_right],
                ["Int", fmt_bold_right],
            ]

            ri += 3
            ci = 0
            for h in headers:
                ws.write(ri, ci, h[0], h[1])
                ci += 1

            for ln in d.line_ids:
                ri += 1
                ws.write(ri, 0, ln.member_id.name)
                ws.write(ri, 1, ln.account_id.name)
                ws.write(ri, 2, ln.q1_average, fmt_currency)
                ws.write(ri, 3, ln.q2_average, fmt_currency)
                ws.write(ri, 4, ln.q3_average, fmt_currency)
                ws.write(ri, 5, ln.q4_average, fmt_currency)
                ws.write(ri, 6, ln.q_average_total, fmt_currency)
                ws.write(ri, 7, ln.interest_amount, fmt_currency)

            outputStream = StringIO()
            wb.save(outputStream)
            d.excel_data = base64.encodestring(outputStream.getvalue())
            outputStream.close()
        return
Beispiel #18
0
    def generate_excel_report(self):
        workbook = xlwt.Workbook(encoding='utf-8')
        worksheet = workbook.add_sheet('Sales Report')
        # worksheet.filter_column(0, 'Region == East')
        #
        # worksheet.filter_column('A', 'x > 2000')
        lst_search = [('state', 'in', ['paid', 'invoiced', 'done'])]
        start_date = self.start_date
        end_date = self.end_date
        if start_date:
            lst_search.append(('date_order', '>=', start_date))
        if end_date:
            lst_search.append(('date_order', '<=', end_date))
        if self.partner_id:
            lst_search.append(('partner_id', '=', self.partner_id.id))
        if self.user_id:
            lst_search.append(('cashier_name', '=', self.user_id.id))
        orders = self.env['pos.order'].search(lst_search,
                                              order='date_order asc')
        bold = xlwt.easyxf(
            "pattern: pattern solid, fore-colour light_turquoise;"
            "align: wrap on, horiz center, vert center;font: bold on;"
            "borders: left thin, right thin, top thin, bottom thin;"
            "font: name Times New Roman, color black;",
            num_format_str='#,##0.00')
        grey = xlwt.easyxf(
            "pattern: pattern solid, fore-colour grey25;"
            "align: wrap on, horiz center, vert center;"
            "borders: left thin, right thin, top thin, bottom thin;"
            "font: name Times New Roman, color black;",
            num_format_str='#,##0.00')
        grey_red = xlwt.easyxf(
            "pattern: pattern solid, fore-colour grey25;"
            "align: wrap on, horiz center, vert center;"
            "borders: left thin, right thin, top thin, bottom thin;"
            "font: name Times New Roman, color red;",
            num_format_str='#,##0.00')
        normal = xlwt.easyxf(
            "align: wrap on, horiz center, vert center;"
            "borders: left thin, right thin, top thin, bottom thin;"
            "font: name Times New Roman;",
            num_format_str='#,##0.00')
        normal_red = xlwt.easyxf(
            "align: wrap on, horiz center, vert center;"
            "borders: left thin, right thin, top thin, bottom thin;"
            "font: name Times New Roman, color red;",
            num_format_str='#,##0.00')
        normal_right = xlwt.easyxf(
            "align: wrap on, horiz right, vert center;"
            "borders: left thin, right thin, top thin, bottom thin;"
            "font: name Times New Roman;",
            num_format_str='#,##0.00')
        r = 0
        c = 0
        if self.detailed:
            data_list = []
            cash = card = total = 0
            output_header = [
                'DATE', 'CUSTOMER', 'CONTACT NO.', 'ORDER NO.',
                'SERVICES / RETAIL', 'AMOUNT', 'CATEGORY', 'SOLD STAFF',
                'SERVED STAFF', 'CASH PAYMENT', 'CARD PAYMENT', 'NOTES'
            ]
            for item in output_header:
                worksheet.write(r, c, item, bold)
                col = worksheet.col(c)
                if c == 1:
                    col.width = 900 * 6
                else:
                    col.width = 900 * 4
                worksheet.row(r).height_mismatch = True
                worksheet.row(r).height = 220 * 2
                c += 1
            for ord in orders:
                data = []
                date_order = ''
                if ord.date_order:
                    date_order = datetime.strptime(ord.date_order,
                                                   '%Y-%m-%d %H:%M:%S').date()
                    date_order = date_order.strftime('%d/%m/%Y')
                data.append(date_order)
                if ord.partner_id:
                    data.append(ord.partner_id.name)
                    data.append(ord.partner_id.phone)
                else:
                    data.append('')
                    data.append('')
                data.append(ord.name)
                data.append('')
                data.append(ord.amount_total)
                data.append('')
                data.append('')
                data.append('')
                data.append(ord.cash_amt)
                data.append(ord.credit_amt)
                if ord.note:
                    data.append(ord.note)
                else:
                    data.append('')
                cash += ord.cash_amt
                card += ord.credit_amt
                total += ord.amount_total
                data_list.append(data)
                r += 1
                c = 0
                for item in data:
                    if ord.negative_entry:
                        worksheet.write(r, c, item, grey_red)
                    else:
                        worksheet.write(r, c, item, grey)
                    c += 1
                for line in ord.lines:
                    r += 1
                    c = 4
                    worksheet.write_merge(r, r, 0, 4, line.product_id.name,
                                          normal_right)
                    c += 1
                    worksheet.write(r, c, line.price_subtotal_incl, normal)
                    c += 1
                    if line.product_id.pos_categ_id:
                        worksheet.write(r, c,
                                        line.product_id.pos_categ_id.name,
                                        normal)
                    else:
                        worksheet.write(r, c, '', normal)
                    c += 1
                    if line.sold_staff_id:
                        worksheet.write(r, c, line.sold_staff_id.name, normal)
                    else:
                        worksheet.write(r, c, '', normal)
                    c += 1
                    if line.staff_assigned_id:
                        worksheet.write(r, c, line.staff_assigned_id.name,
                                        normal)
                    else:
                        worksheet.write(r, c, '', normal)
                    c += 1
                    worksheet.write_merge(r, r, c, c + 2, '', normal_right)
            r += 1
            worksheet.write_merge(r, r, 0, 4, '', bold)
            c = 5
            worksheet.write(r, c, total, bold)
            c += 1
            worksheet.write_merge(r, r, c, c + 2, '', bold)
            c += 3
            worksheet.write(r, c, cash, bold)
            c += 1
            worksheet.write(r, c, card, bold)
            c += 1
            worksheet.write(r, c, '', bold)
        else:
            data_list = []
            cash = card = total = 0
            output_header = [
                'DATE', 'CUSTOMER', 'CONTACT NO.', 'ORDER NO.', 'AMOUNT',
                'CASH PAYMENT', 'CARD PAYMENT', 'NOTES'
            ]
            for item in output_header:
                worksheet.write(r, c, item, bold)
                col = worksheet.col(c)
                if c == 1:
                    col.width = 900 * 6
                else:
                    col.width = 900 * 4
                worksheet.row(r).height_mismatch = True
                worksheet.row(r).height = 220 * 2
                c += 1
            for ord in orders:
                data = []
                date_order = ''
                if ord.date_order:
                    date_order = datetime.strptime(ord.date_order,
                                                   '%Y-%m-%d %H:%M:%S').date()
                    date_order = date_order.strftime('%d/%m/%Y')
                data.append(date_order)
                if ord.partner_id:
                    data.append(ord.partner_id.name)
                    data.append(ord.partner_id.phone)
                else:
                    data.append('')
                    data.append('')
                data.append(ord.name)
                data.append(ord.amount_total)
                data.append(ord.cash_amt)
                data.append(ord.credit_amt)
                if ord.note:
                    data.append(ord.note)
                else:
                    data.append('')
                cash += ord.cash_amt
                card += ord.credit_amt
                total += ord.amount_total
                data_list.append(data)
                r += 1
                c = 0
                for item in data:
                    if ord.negative_entry:
                        worksheet.write(r, c, item, normal_red)
                    else:
                        worksheet.write(r, c, item, normal)
                    c += 1
            r += 1
            worksheet.write_merge(r, r, 0, 3, '', bold)
            c = 4
            worksheet.write(r, c, total, bold)
            c += 1
            worksheet.write(r, c, cash, bold)
            c += 1
            worksheet.write(r, c, card, bold)
            c += 1
            worksheet.write(r, c, '', bold)
            c += 1
        # worksheet.autofilter(0, 0, 1, 1)
        # worksheet.filter_column('A', 'x > 2000')
        # worksheet.filter_column('A')

        # worksheet.autofilter()
        buf = io.BytesIO()
        workbook.save(buf)
        out = base64.encodestring(buf.getvalue())
        name = "Sales_report" + ".xls"
        self.write({'state': 'get', 'data': out, 'name': name})
        return {
            'type': 'ir.actions.act_window',
            'res_model': 'summary.wizard',
            'view_mode': 'form',
            'view_type': 'form',
            'res_id': self.id,
            'views': [(False, 'form')],
            'target': 'new',
        }
Beispiel #19
0
    def action_print_leave_summary(self):
        workbook = xlwt.Workbook()
        amount_tot = 0
        hr_holiday_objs_list = []
        column_heading_style = easyxf(
            'font:height 200;font:bold True;align: horiz left;')
        worksheet = workbook.add_sheet('Leave Summary')
        worksheet.write(
            2, 2, self.from_date.strftime('%d-%m-%Y'),
            easyxf('font:height 200;font:bold True;align: horiz center;'))
        worksheet.write(
            2, 3, 'To',
            easyxf('font:height 200;font:bold True;align: horiz center;'))
        worksheet.write(
            2, 4, self.to_date.strftime('%d-%m-%Y'),
            easyxf('font:height 200;font:bold True;align: horiz center;'))
        worksheet.write(4, 0, _('Date'), column_heading_style)
        worksheet.write(4, 1, _('No of Days'), column_heading_style)
        worksheet.write(4, 2, _('Employee'), column_heading_style)
        worksheet.write(4, 3, _('Department'), column_heading_style)
        worksheet.write(4, 4, _('Leave Type'), column_heading_style)
        worksheet.write(4, 5, _('Description'), column_heading_style)
        worksheet.write(4, 6, _('End Date'), column_heading_style)

        worksheet.col(0).width = 3000
        worksheet.col(1).width = 3000
        worksheet.col(2).width = 5000
        worksheet.col(3).width = 6500
        worksheet.col(4).width = 5000
        worksheet.col(5).width = 6500
        worksheet.col(6).width = 3000

        String = self.from_date.strftime(
            '%d-%m-%Y') + ' ' + ' To ' + ' ' + self.to_date.strftime(
                '%d-%m-%Y')
        worksheet2 = workbook.add_sheet('Employee Wise Leave Summary')
        worksheet2.write_merge(
            2, 2, 0, 1, String,
            easyxf('font:height 200; align: horiz center;font:bold True;'))
        worksheet2.write(4, 0, _('Employee'), column_heading_style)
        worksheet2.write(4, 1, _('No of Leaves'), column_heading_style)
        worksheet2.col(0).width = 5000
        worksheet2.col(1).width = 5000
        row = 5
        employee_row = 5

        dict = {}

        for wizard in self:
            employee_leave_data = {}
            heading = 'Leave Summary Report'
            worksheet.write_merge(
                0, 0, 0, 6, heading,
                easyxf(
                    'font:height 210; align: horiz center;pattern: pattern solid, fore_color black; font: color white; font:bold True;'
                    "borders: top thin,bottom thin"))
            heading = 'Employee Wise Leave Summary'
            worksheet2.write_merge(
                0, 0, 0, 1, heading,
                easyxf(
                    'font:height 210; align: horiz center;pattern: pattern solid, fore_color black; font: color white; font:bold True;'
                    "borders: top thin,bottom thin"))
            if wizard.department_id:
                hr_holiday_objs = self.env['hr.leave'].search([
                    ('request_date_from', '>=', wizard.from_date),
                    ('request_date_to', '<=', wizard.to_date),
                    ('department_id', '=', wizard.department_id.id),
                    ('state', '=', 'validate')
                ])

            else:
                hr_holiday_objs = self.env['hr.leave'].search([
                    ('request_date_from', '>=', wizard.from_date),
                    ('request_date_to', '<=', wizard.to_date),
                    ('state', '=', 'validate')
                ])

            for obj in hr_holiday_objs:
                date_from = datetime.datetime.strptime(
                    obj.request_date_from.strftime('%Y-%m-%d %H:%M:%S'),
                    '%Y-%m-%d %H:%M:%S').strftime('%d-%m-%Y')
                hr_holiday_objs_list.append({
                    'id': obj,
                    'date': date_from,
                    'emp': obj.employee_id.name
                })
            hr_holiday_objs_list = sorted(hr_holiday_objs_list,
                                          key=itemgetter('emp'))
            hr_holiday_objs_list = sorted(hr_holiday_objs_list,
                                          key=itemgetter('date'),
                                          reverse=True)

            for dict in hr_holiday_objs_list:
                for id in dict['id']:
                    leave_date_from = datetime.datetime.strptime(
                        id.request_date_from.strftime('%Y-%m-%d %H:%M:%S'),
                        '%Y-%m-%d %H:%M:%S').strftime('%d-%m-%Y')
                    leave_date_to = datetime.datetime.strptime(
                        id.request_date_to.strftime('%Y-%m-%d %H:%M:%S'),
                        '%Y-%m-%d %H:%M:%S').strftime('%d-%m-%Y')
                    worksheet.write(
                        row, 0, leave_date_from,
                        easyxf('font:height 200;align: horiz left;'))
                    worksheet.write(
                        row, 1, id.number_of_days,
                        easyxf('font:height 200;align: horiz right;'))
                    worksheet.write(
                        row, 2, id.employee_id.name,
                        easyxf('font:height 200;align: horiz left;'))
                    worksheet.write(
                        row, 3, id.department_id.name or '',
                        easyxf('font:height 200;align: horiz left;'))
                    worksheet.write(
                        row, 4, id.holiday_status_id.name,
                        easyxf('font:height 200;align: horiz left;'))
                    worksheet.write(
                        row, 5, id.name or '',
                        easyxf('font:height 200;align: horiz left;'))
                    worksheet.write(
                        row, 6, leave_date_to,
                        easyxf('font:height 200;align: horiz left;'))

                    if id.employee_id.name not in employee_leave_data:
                        employee_leave_data.update(
                            {id.employee_id.name: id.number_of_days})
                    else:
                        leave_data = employee_leave_data[
                            id.employee_id.name] + id.number_of_days
                        employee_leave_data.update(
                            {id.employee_id.name: leave_data})
                    row += 1

            for employee in sorted(employee_leave_data):
                worksheet2.write(employee_row, 0, employee)
                worksheet2.write(employee_row, 1,
                                 employee_leave_data[employee])
                employee_row += 1

        fp = io.BytesIO()
        workbook.save(fp)
        excel_file = base64.encodestring(fp.getvalue())
        wizard.leave_summary_file = excel_file
        wizard.file_name = 'Leave Summary Report.xls'
        wizard.leave_report_printed = True
        fp.close()
        return {
            'view_mode': 'form',
            'res_id': wizard.id,
            'res_model': 'leave.summary.report',
            'view_type': 'form',
            'type': 'ir.actions.act_window',
            'context': self.env.context,
            'target': 'new',
        }
Beispiel #20
0
def download_model(dl_obj,
                   Export_Para=None,
                   workbook=None,
                   append_domain=None,
                   sheet_name=None,
                   worksheet=None,
                   ROW_TITLE=0,
                   return_more_thing_for_bcn=False,
                   write_before_title=None,
                   kargs_write_before_title=None,
                   no_gray=False,
                   is_set_width=True,
                   dl_model_para=None,
                   OFFSET_COLUMN=0,
                   write_title_even_not_recs=True,
                   write_title_even_not_recs_for_title=True,
                   font_height=12):
    font_height = dl_obj.font_height  # or font_height or 12
    #     global dl_obj_global
    #     dl_obj_global = dl_obj
    exported_model = Export_Para['exported_model']
    max_header_char_number = Export_Para.get('max_header_char_number')
    FIELDNAME_FIELDATTR = Export_Para['FIELDNAME_FIELDATTR']
    FIELDNAME_FIELDATTR = recursive_OrderedDict(FIELDNAME_FIELDATTR)
    #     print ('**FIELDNAME_FIELDATTR***',FIELDNAME_FIELDATTR)
    gen_domain = Export_Para.get('gen_domain')

    # đưa wb,ws_name  ;  đưa ws ; ko đưa chi cả
    if not worksheet:
        if workbook == None:
            workbook = xlwt.Workbook()
        if sheet_name == None:
            sheet_name = u'Sheet 1'
        worksheet = workbook.add_sheet(sheet_name)  # cell_overwrite_ok=True

    needdata = {'a_instance_dict': {'stt_not_model': {'val': 0}}}
    needdata['dl_obj'] = dl_obj
    model_fields = request.env[exported_model]._fields

    #     add_title(worksheet, FIELDNAME_FIELDATTR, model_fields, ROW_TITLE=ROW_TITLE, offset_column=OFFSET_COLUMN)
    if gen_domain:
        domain = gen_domain(dl_obj)
    else:
        domain = []
    if append_domain:
        domain.extend(append_domain)
    order = Export_Para.get('search_para', {'order': 'id asc'})
    recs = request.env[exported_model].search(domain, **order)

    n_row_title = 0
    if (recs or write_title_even_not_recs) and write_before_title:
        ROW_TITLE += 1
        n_row_title += 1
        write_before_title(kargs_write_before_title)

    center_vert_border_style = xlwt.easyxf(
        generate_easyxf(height=font_height,
                        borders='left thin, right thin, top thin, bottom thin',
                        vert='center'))

    row_index = ROW_TITLE
    if recs:
        for r in recs:  #request.env['cvi'].search([]):
            row_index += 1
            add_1_row(worksheet,
                      r,
                      FIELDNAME_FIELDATTR,
                      row_index,
                      offset_column=OFFSET_COLUMN,
                      needdata=needdata,
                      save_ndata=True,
                      dl_model_para=dl_model_para,
                      center_vert_border_style=center_vert_border_style)
        n_row_recs = row_index - (ROW_TITLE + 1) + 1
    else:
        n_row_recs = 0
    if recs or write_title_even_not_recs_for_title:
        add_title(worksheet,
                  FIELDNAME_FIELDATTR,
                  model_fields,
                  ROW_TITLE=ROW_TITLE,
                  offset_column=OFFSET_COLUMN,
                  no_gray=no_gray,
                  is_set_width=is_set_width,
                  dl_model_para=dl_model_para,
                  font_height=font_height,
                  max_header_char_number=max_header_char_number)
        n_row_title += 1
    if return_more_thing_for_bcn:
        return n_row_recs + n_row_title
    return workbook
Beispiel #21
0
    def generate_report(self):
        filename = 'Payslip Report.xls'
        workbook = xlwt.Workbook(encoding="UTF-8")
        worksheet = workbook.add_sheet('Payslip Report')
        style = xlwt.easyxf(
            'font:height 200, bold True, name Arial;align: horiz left;')
        style2 = xlwt.easyxf(
            'font:height 200, bold False, name Calibri; align: horiz center; borders: left thin, right thin, top thin, bottom thin;'
        )
        style_main_header = xlwt.easyxf(
            'font: height 230, bold True, color black;\
                                                   borders: left thin, right thin, top thin, bottom thin;\
                                                   pattern: pattern solid, fore_color white; align:  horizontal center, wrap on, vertical center;'
        )
        style_main_header_data = xlwt.easyxf(
            'font: height 230, bold True, underline on, color black;\
                                                        borders: left 1, right 1, top 1, bottom 1;\
                                                           pattern: pattern solid, fore_color white; align:  horizontal center, wrap on, vertical center;'
        )
        style_main_bottom = xlwt.easyxf(
            'font: height 230, bold True, color black;\
                                                           borders: left thin, right thin, top thin, bottom thin;\
                                                           pattern: pattern solid, fore_color white; align:  horizontal right, wrap on, vertical center;'
        )
        worksheet.row(0).height = 320
        worksheet.col(0).width = 4000
        worksheet.col(1).width = 5000
        worksheet.col(2).width = 5000
        worksheet.col(3).width = 5000
        worksheet.col(4).width = 5000
        worksheet.col(5).width = 5000
        worksheet.col(6).width = 5000
        worksheet.col(7).width = 5000
        worksheet.col(8).width = 5000
        worksheet.col(9).width = 5000
        worksheet.col(10).width = 5000
        worksheet.col(11).width = 5000
        worksheet.col(12).width = 5000
        borders = xlwt.Borders()
        borders.bottom = xlwt.Borders.MEDIUM
        border_style = xlwt.XFStyle()  # Create Style
        border_style.borders = borders

        header_data = 'PAYROLL FOR THE MONTH OF ' + str(
            self.month) + ' ' + str(date.today().year)
        worksheet.write_merge(0, 0, 3, 5, 'C R A TRADING (L.L.C)',
                              style_main_header_data)
        worksheet.write_merge(1, 1, 3, 5, '', style_main_header_data)
        worksheet.write_merge(2, 2, 3, 5, header_data, style_main_header_data)

        worksheet.write_merge(6, 7, 0, 0, 'SI.No', style_main_header)
        worksheet.write_merge(6, 7, 1, 1, 'NAME OF THE EMPLOYEE',
                              style_main_header)
        worksheet.write_merge(6, 7, 2, 2, 'WORK PERMIT NO (8 DIGIT NO)',
                              style_main_header)
        worksheet.write_merge(6, 7, 3, 3, 'PERSONAL NO (14 DIGIT NO)',
                              style_main_header)
        worksheet.write_merge(6, 7, 4, 4, 'BANK NAME', style_main_header)
        worksheet.write_merge(6, 7, 5, 5, 'IBAN /RATIBI CARD NUMBER',
                              style_main_header)
        worksheet.write_merge(6, 7, 6, 6, 'NO OF DAYS ABSENT',
                              style_main_header)
        worksheet.write_merge(6, 6, 7, 10, 'Employees Net Salary',
                              style_main_header)
        worksheet.write_merge(7, 7, 7, 7, 'FIXED PORTION', style_main_header)
        worksheet.write_merge(7, 7, 8, 8, 'VARIABLE', style_main_header)
        worksheet.write_merge(7, 7, 9, 9, 'OVER TIME', style_main_header)
        worksheet.write_merge(7, 7, 10, 10, 'Total Payment', style_main_header)
        # worksheet.write_merge(6, 7, 11, 11, '', style_main_header)
        # worksheet.write_merge(6, 7, 12, 12, '', style_main_header)

        row = 7
        column = 0
        payslip = self.env['hr.payslip'].search([])
        fixed_portion_sum = 0
        variable_sum = 0
        over_time_sum = 0
        total_sum = 0
        count = 0
        for data in payslip:
            absent_days = 0
            for day in data.worked_days_line_ids:
                if day.work_entry_type_id.name != "Attendance":
                    absent_days = absent_days + day.number_of_days
            if data.date_from.strftime("%b") == self.month:
                row += 1
                count += 1
                worksheet.write(row, column, count, style2)
                worksheet.write(row, column + 1, data.employee_id.name or '',
                                style2)
                worksheet.write(row, column + 2, data.employee_id.permit_no
                                or '', style2)
                worksheet.write(row, column + 3, data.employee_id.phone or '',
                                style2)
                worksheet.write(
                    row, column + 4, data.employee_id.bank_account_id.bank_name
                    or '', style2)
                worksheet.write(row, column + 5,
                                data.employee_id.iban_card_number or '',
                                style2)
                worksheet.write(row, column + 6, absent_days or '', style2)
                fixed_portion_sum = fixed_portion_sum + data.contract_id.fixed_portion
                worksheet.write(row, column + 7, data.contract_id.fixed_portion
                                or '', style2)
                variable_sum = variable_sum + data.contract_id.variable
                worksheet.write(row, column + 8, data.contract_id.variable
                                or '', style2)
                over_time_sum = over_time_sum + (data.over_time * 20)
                worksheet.write(row, column + 9, data.over_time * 20 or '',
                                style2)
                total_sum = total_sum + data.contract_id.fixed_portion + data.contract_id.variable + (
                    data.over_time * 20)
                worksheet.write(
                    row, column + 10, data.contract_id.fixed_portion +
                    data.contract_id.variable + (data.over_time * 20) or '',
                    style2)
                # worksheet.write(row, column + 3, data.permit_no or '', style2)
                # worksheet.write(row, column + 3, data.permit_no or '', style2)
        row += 1
        worksheet.write_merge(row, row, 0, 6, 'Total in Dirhms',
                              style_main_bottom)
        worksheet.write(row, 7, fixed_portion_sum, style_main_header)
        worksheet.write(row, 8, variable_sum, style_main_header)
        worksheet.write(row, 9, over_time_sum, style_main_header)
        worksheet.write(row, 10, total_sum, style_main_header)

        fp = BytesIO()
        workbook.save(fp)
        export_id = self.env['payslip.xls'].create({
            'report_excel_file':
            base64.encodestring(fp.getvalue()),
            'report_file_name':
            filename
        })
        fp.close()
        return {
            'view_mode': 'form',
            'res_id': export_id.id,
            'res_model': 'payslip.xls',
            'view_type': 'form',
            'type': 'ir.actions.act_window',
            'context': self._context,
            'target': 'new',
        }
    def print_reglas_salariales_report(self):
        domain = [('state', '=', 'done')]
        if self.date_from:
            domain.append(('date_from', '>=', self.date_from))

        if self.date_to:
            domain.append(('date_to', '<=', self.date_to))
        if self.employee_id:
            domain.append(('employee_id', '=', self.employee_id.id))
        if not self.employee_id and self.department_id:
            employees = self.env['hr.employee'].search([
                ('department_id', '=', self.department_id.id)
            ])
            domain.append(('employee_id', 'in', employees.ids))

        payslips = self.env['hr.payslip'].search(domain)
        rules = self.rule_ids
        payslip_lines = payslips.mapped('line_ids').filtered(
            lambda x: x.salary_rule_id.id in rules.ids
        )  #.sorted(key=lambda x: x.slip_id.employee_id)

        workbook = xlwt.Workbook()
        bold = xlwt.easyxf("font: bold on;")

        worksheet = workbook.add_sheet('Nomina')

        from_to_date = 'De  %s A %s' % (self.date_from or '', self.date_to
                                        or '')
        concepto = 'Concepto:  %s' % (self.date_from)

        worksheet.write_merge(1, 1, 0, 4, 'Reporte de acumulados de conceptos',
                              bold)
        worksheet.write_merge(2, 2, 0, 4, from_to_date, bold)
        #worksheet.write_merge(3, 3, 0, 4, concepto, bold)

        worksheet.write(4, 0, 'Empleado', bold)
        col = 3
        rule_index = {}
        for rule in rules:
            worksheet.write(4, col, rule.name, bold)
            rule_index.update({rule.id: col})
            col += 1
        #employees = defaultdict(dict)
        #employee_payslip = defaultdict(set)
        employees = {}
        for line in payslip_lines:
            if line.slip_id.employee_id not in employees:
                employees[line.slip_id.employee_id] = {line.slip_id: []}
            if line.slip_id not in employees[line.slip_id.employee_id]:
                employees[line.slip_id.employee_id].update({line.slip_id: []})
            employees[line.slip_id.employee_id][line.slip_id].append(line)

            #employees[line.slip_id.employee_id].add(line)

            #employee_payslip[line.slip_id.employee_id].add(line.slip_id)

        row = 5
        tipo_nomina = {'O': 'Nómina ordinaria', 'E': 'Nómina extraordinaria'}
        for employee, payslips in employees.items():
            worksheet.write(row, 0, employee.name)
            row += 1
            worksheet.write(row, 1, 'Fecha de la nomina', bold)
            worksheet.write(row, 2, 'Tipo', bold)
            row += 1
            total_by_rule = defaultdict(lambda: 0.0)
            for payslip, lines in payslips.items():
                #for line in lines:
                worksheet.write(row, 1, payslip.date_from)
                worksheet.write(row, 2,
                                tipo_nomina.get(payslip.tipo_nomina, ''))
                for line in lines:
                    worksheet.write(row,
                                    rule_index.get(line.salary_rule_id.id),
                                    line.total)
                    total_by_rule[line.salary_rule_id.id] += line.total
                row += 1
            worksheet.write(row, 2, 'Total', bold)
            for rule_id, total in total_by_rule.items():
                worksheet.write(row, rule_index.get(rule_id), total)
            row += 1

        fp = io.BytesIO()
        workbook.save(fp)
        fp.seek(0)
        data = fp.read()
        fp.close()

        self.write({'file_data': base64.b64encode(data)})
        action = {
            'name': 'Payslips',
            'type': 'ir.actions.act_url',
            'url':
            "/web/content/?model=" + self._name + "&id=" + str(self.id) +
            "&field=file_data&download=true&filename=Reglas_salariales.xls",
            'target': 'self',
        }
        return action
Beispiel #23
0
    def export_products(self):
        filename = 'products_%s.xls' % (
            datetime.today().strftime("%Y_%m_%d_%H_%M_%S"))
        workbook = xlwt.Workbook()
        bold = xlwt.easyxf("font: bold on;")

        quant_obj = self.env['stock.quant']
        products = self.env['product.product'].search([])
        company_id = self.env.user.company_id.id

        #worksheet = workbook.add_worksheet('Products')
        worksheet = workbook.add_sheet('Products')

        headers = [
            'id', 'categ_id/name', 'pos_categ_id/name', 'available_in_pos',
            'name', 'barcode', 'default_code', 'unit_of_measurement', 'type',
            'route_ids/id', 'purchase_ok', 'sale_ok', 'standard_price',
            'lst_price', 'seller_ids/name/name', 'image_medium'
        ]
        warehouse_ids = []
        product_obj = self.env['product.product']
        product_ids = products.ids
        product_inventory_by_wh = {}
        for warehouse in self.env['stock.warehouse'].search([
            ('company_id', '=', company_id)
        ]):
            headers.append(warehouse.code)
            #For faster quantity calculation, used quary.
            domain_quant_loc, domain_move_in_loc, domain_move_out_loc = product_obj._get_domain_locations_new(
                [warehouse.view_location_id.id])
            domain_quant = [('product_id', 'in', product_ids)
                            ] + domain_quant_loc
            query = quant_obj._where_calc(domain_quant)
            from_clause, where_clause, where_clause_params = query.get_sql()
            where_str = where_clause and (" WHERE %s" % where_clause) or ''

            query_str = 'SELECT product_id, sum(quantity) as quantity FROM ' + from_clause + where_str + ' group by product_id'
            self._cr.execute(query_str, where_clause_params)
            res = dict(self._cr.fetchall())
            product_inventory_by_wh.update({warehouse.id: res})
            warehouse_ids.append(warehouse.id)

        product_xml_ids = dict(self.__ensure_xml_id_custom(products))
        sellers_mapping_dict = {}
        for i, header in enumerate(headers):
            worksheet.write(0, i, header, bold)
            worksheet.col(i).width = 8000  # around 220 pixels

        def splittor(rs):
            """ Splits the self recordset in batches of 1000 (to avoid
            entire-recordset-prefetch-effects) & removes the previous batch
            from the cache after it's been iterated in full
            """
            for idx in range(0, len(rs), 1000):
                sub = rs[idx:idx + 1000]
                for rec in sub:
                    yield rec
                rs.invalidate_cache(ids=sub.ids)

        row_index = 1
        pos_installed = hasattr(self.env['product.product'],
                                'available_in_pos')
        for product in splittor(products):
            if product.route_ids:
                xml_ids = [
                    xid for _, xid in self.__ensure_xml_id_custom(
                        product.route_ids)
                ]
                route_ids = ','.join(xml_ids) or False
            else:
                route_ids = ''
            i = 0
            worksheet.write(row_index, i, product_xml_ids.get(product.id))
            i += 1
            worksheet.write(row_index, i, product.categ_id.complete_name)
            i += 1
            #########
            if pos_installed:
                worksheet.write(
                    row_index, i, product.pos_categ_id.complete_categ_name
                    or None)
                i += 1
                worksheet.write(row_index, i,
                                1 if product.available_in_pos else 0)
                i += 1
            else:
                worksheet.write(row_index, i, None)
                i += 1
                worksheet.write(row_index, i, None)
                i += 1
            #########
            worksheet.write(row_index, i, product.name)
            i += 1
            worksheet.write(row_index, i, product.barcode or '')
            i += 1
            worksheet.write(row_index, i, product.default_code or '')
            i += 1
            worksheet.write(row_index, i, product.uom_id.name)
            i += 1
            worksheet.write(row_index, i, product.type)
            i += 1
            worksheet.write(row_index, i, route_ids)
            i += 1
            worksheet.write(row_index, i, product.purchase_ok)
            i += 1
            worksheet.write(row_index, i, product.sale_ok)
            i += 1
            worksheet.write(row_index, i, product.standard_price)
            i += 1
            worksheet.write(row_index, i, product.lst_price)
            i += 1
            seller_xml_ids = []
            for seller in product.seller_ids.mapped('name'):
                if seller.id not in sellers_mapping_dict:
                    xml_rec = self.__ensure_xml_id_custom(seller)
                    if xml_rec:
                        sellers_mapping_dict.update(
                            {seller.id: xml_rec and xml_rec[0][1] or False})
                seller_xml_ids.append(
                    sellers_mapping_dict.get(seller.id) or '')

            worksheet.write(row_index, i, ','.join(seller_xml_ids))
            i += 1
            worksheet.write(row_index, i, None)
            i += 1
            for warehouse_id in warehouse_ids:
                worksheet.write(
                    row_index, i,
                    product_inventory_by_wh[warehouse_id].get(product.id, 0.0))
                #worksheet.write(row_index, i, product.with_context(warehouse=warehouse_id).qty_available)
                i += 1
            row_index += 1
#         workbook.close()
#         fp.seek(0)
#         data = fp.read()
#         fp.close()

        fp = io.BytesIO()
        workbook.save(fp)
        fp.seek(0)
        data = fp.read()
        fp.close()

        self.write({'file_data': base64.b64encode(data)})
        return {
            'type':
            'ir.actions.act_url',
            'url':
            '/web/binary/savefile_custom?model=%s&field=file_data&id=%s&file_name=%s&content_type="application/vnd.ms-excel"'
            % (self._name, self.id, filename),
            'target':
            'self',
        }
Beispiel #24
0
    def _action_imprimir_excel(self, ):

        workbook = xlwt.Workbook()
        column_heading_style = easyxf('font:height 200;font:bold True;')
        worksheet = workbook.add_sheet('Kardex report')

        #Ponemos los primeros encabezados
        worksheet.write(0, 0, _('Kardex report product'), column_heading_style)

        query_rorte = """
        select Max(id) as id  from kardex_productos_mov 
    """
        self.env.cr.execute(query_rorte, )
        tr = self.env.cr.dictfetchall()
        for tr_t in tr:
            todo_reporte = self.env['kardex.productos.mov'].search([
                ('id', '=', int(tr_t['id']))
            ])
            tf = 0
            for todfact in todo_reporte:
                worksheet.write(1, 0, "Date from:", column_heading_style)
                worksheet.write(1, 1, todfact.date_from)
                worksheet.write(2, 0, "Date to:", column_heading_style)
                worksheet.write(2, 1, todfact.date_to)

                worksheet.write(1, 2, "Product:", column_heading_style)
                worksheet.write(1, 3, todfact.product.name)
                worksheet.write(2, 2, "Current Company:", column_heading_style)
                worksheet.write(2, 3, todfact.company.name)
                worksheet.write(1, 4, "Location:", column_heading_style)
                ubi_hijo = todfact.ubicacion.name
                ubi_pad = todfact.ubicacion.location_id.name
                worksheet.write(1, 5, str(ubi_pad) + "/" + str(ubi_hijo))

                #Ponemos los primeros encabezados del detalle
        worksheet.write(4, 0, _('Date'), column_heading_style)
        worksheet.write(4, 1, _('Concept'), column_heading_style)
        worksheet.write(4, 2, _('U_In'), column_heading_style)
        worksheet.write(4, 3, _('U_Out'), column_heading_style)
        worksheet.write(4, 4, _('U_balance'), column_heading_style)
        worksheet.write(4, 5, _("Costo_Uni"), column_heading_style)
        worksheet.write(4, 6, _('V_In'), column_heading_style)
        worksheet.write(4, 7, _('V_Out'), column_heading_style)
        worksheet.write(4, 8, _('V_balance'), column_heading_style)
        worksheet.write(4, 9, _('Costo_Prom'), column_heading_style)
        worksheet.write(4, 10, _('Origin'), column_heading_style)
        worksheet.write(4, 11, _('Pickin'), column_heading_style)
        worksheet.write(4, 12, _('Invoice'), column_heading_style)
        worksheet.write(4, 13, _('Inventory'), column_heading_style)

        heading = "Product Kardex Detail"
        # worksheet.write_merge(5, 0, 5,13, heading, easyxf('font:height 200; align: horiz center;pattern: pattern solid, fore_color black; font: color white; font:bold True;' "borders: top thin,bottom thin"))
        # Se tiene que hacer de ultimo para saber cuanto mide todo

        # se recorre el reporte

        todo_reporte = self.env['kardex.productos.mov.detalle'].search([
            ('obj_kardex_mostrarmovi', '=', int(tr_t['id']))
        ])
        tf = 0
        for todfact in todo_reporte:

            tf += 1
            ini = 5
            worksheet.write(tf + ini, 0, todfact.date)
            worksheet.write(tf + ini, 1, todfact.concepto)
            worksheet.write(tf + ini, 2, todfact.u_entrada)
            worksheet.write(tf + ini, 3, todfact.u_salida)
            worksheet.write(tf + ini, 4, todfact.u_saldo)
            worksheet.write(tf + ini, 5, todfact.costo_unit)
            worksheet.write(tf + ini, 6, todfact.v_entrada)
            worksheet.write(tf + ini, 7, todfact.v_salida)
            worksheet.write(tf + ini, 8, todfact.v_saldo)
            worksheet.write(tf + ini, 9, todfact.costo_prom)
            worksheet.write(tf + ini, 10, todfact.origin)
            worksheet.write(tf + ini, 11, todfact.picking_id.name)
            worksheet.write(tf + ini, 12, todfact.account_invoice.number)
            worksheet.write(tf + ini, 13, todfact.inventario.name)

        fp = io.BytesIO()
        workbook.save(fp)
        excel_file = base64.encodestring(fp.getvalue())

        self.excel_binary = excel_file
        nombre_tabla = "Kardex Report.xls"
        self.file_name = nombre_tabla
        fp.close()
    def action_print_payment_summary(self, ):
        workbook = xlwt.Workbook()
        column_heading_style = easyxf('font:height 200;font:bold True;')
        worksheet = workbook.add_sheet('Payment Summary')

        worksheet.write(1, 0, _('Invoice Number'), column_heading_style)
        worksheet.write(1, 1, _('Customer'), column_heading_style)
        worksheet.write(1, 2, _('Invoice Date'), column_heading_style)
        worksheet.write(1, 3, _('Invoice Amount'), column_heading_style)
        worksheet.write(1, 4, _('Invoice Currency'), column_heading_style)
        worksheet.write(1, 5, _('Paid Amount'), column_heading_style)
        worksheet.col(0).width = 5000
        worksheet.col(1).width = 5000
        worksheet.col(2).width = 5000
        worksheet.col(3).width = 5000
        worksheet.col(4).width = 5000
        worksheet.col(5).height = 5000

        worksheet2 = workbook.add_sheet('Customer wise Payment Summary')
        worksheet2.write(1, 0, _('Customer'), column_heading_style)
        worksheet2.write(1, 1, _('Paid Amount'), column_heading_style)
        worksheet2.col(0).width = 5000
        worksheet2.col(1).width = 5000
        row = 2
        customer_row = 2
        for wizard in self:
            heading = 'Payment Summary Report (' + str(
                wizard.currency_id.name) + ')'
            worksheet.write_merge(
                0, 0, 0, 5, heading,
                easyxf(
                    'font:height 200; align: horiz center;pattern: pattern solid, fore_color black; font: color white; font:bold True;'
                    "borders: top thin,bottom thin"))
            customer_payment_data = {}
            invoice_objs = self.env['account.invoice'].search([
                ('date_invoice', '>=', wizard.from_date),
                ('date_invoice', '<=', wizard.to_date),
                ('payment_ids', '!=', False)
            ])
            for invoice in invoice_objs:
                worksheet.write(row, 0, invoice.number)
                worksheet.write(row, 1, invoice.partner_id.name)
                worksheet.write(row, 2, invoice.date_invoice)
                worksheet.write(row, 3, invoice.amount_total)
                worksheet.write(row, 4, invoice.currency_id.symbol)
                paid_amount = 0
                for payment in invoice.payment_ids:
                    if payment.state != 'draft' and payment.currency_id == wizard.currency_id:
                        paid_amount += payment.amount
                worksheet.write(row, 5, paid_amount)
                if invoice.partner_id.name not in customer_payment_data:
                    customer_payment_data.update(
                        {invoice.partner_id.name: paid_amount})
                else:
                    paid_amount_data = customer_payment_data[
                        invoice.partner_id.name] + paid_amount
                    customer_payment_data.update(
                        {invoice.partner_id.name: paid_amount_data})
                row += 1
            for customer in customer_payment_data:
                worksheet2.write(customer_row, 0, customer)
                worksheet2.write(customer_row, 1,
                                 customer_payment_data[customer])
                customer_row += 1
#             worksheet.write(row, 5, invoice.symbol)

            fp = StringIO()
            workbook.save(fp)
            excel_file = base64.encodestring(fp.getvalue())
            wizard.payment_summary_file = excel_file
            wizard.file_name = 'Payment Summary Report.xls'
            wizard.payment_report_printed = True
            fp.close()
            return {
                'view_mode': 'form',
                'res_id': wizard.id,
                'res_model': 'print.payment.summary',
                'view_type': 'form',
                'type': 'ir.actions.act_window',
                'context': self.env.context,
                'target': 'new',
            }
Beispiel #26
0
    def generate_backlog_excel_report(self):
        workbook = xlwt.Workbook(encoding='utf-8')
        worksheet = workbook.add_sheet('Claim Report')
        print("\n\n\n\n Workbook Created", workbook)
        # Add a font format to use to highlight cells.
        #         bold = workbook.add_format({'bold':True,'size': 10})
        #         normal = workbook.add_format({'size': 10})
        #          workbook = xlsxwriter.Workbook('/tmp/abc.xlsx')
        #         ('/tmp/%s.xlsx'%self.from_date)
        #         worksheet = workbook.add_worksheet()

        bold = xlwt.easyxf("font: bold on;")
        normal = xlwt.easyxf()
        r = 0
        c = 0

        data_list = []
        output_header = [
            'Patient Name', 'MEM ID', 'Date of Birth', 'Nationality',
            'Country of Residence', 'Sex', 'Mobile', 'Invoice Number',
            'Healthcare Professional', 'Healthcare Professional ID',
            'Healthcare Professional Type', 'Episode Number', 'Doctor',
            'Appointment Start', 'Appointment End', 'State', 'Treatment',
            'Treatment Code', 'Treatment price', 'Treatment group discount(%)',
            'Co-payment', 'Amount paid by patient'
        ]
        for item in output_header:
            worksheet.write(r, c, item, bold)
            c += 1

        inv_data = self.env['account.invoice'].search \
            ([('date_invoice', '>=', self.from_date), ('date_invoice', '<=', self.to_date), ('patient','!=',False)])
        #         data = []
        for inv in inv_data:
            #             data = []
            #             data.append(inv.patient.name.name)
            #             if inv.patient.name.ref:
            #                 data.append(inv.patient.name.ref)
            #             else:
            #                 data.append('')
            #             if inv.patient.dob:
            #                 data.append(inv.patient.dob)
            #             else:
            #                 data.append('')
            #             if inv.patient.nationality_id:
            #                 data.append(inv.patient.nationality_id.name)
            #             else:
            #                 data.append('')
            #             if inv.patient.name.country_id:
            #                 data.append(inv.patient.name.country_id.name)
            #             else:
            #                 data.append('')
            #             if inv.patient.sex:
            #                 if inv.patient.sex == 'm':
            #                     data.append('Male')
            #                 else:
            #                     data.append('Female')
            #             else:
            #                 data.append('')
            #             if inv.patient.mobile:
            #                 data.append(inv.patient.mobile)
            #             else:
            #                 data.append('')
            #             if inv.number:
            #                 data.append(inv.number)
            #             else:
            #                 data.append('')
            #             if inv.dentist:
            #                 data.append(inv.dentist.name.name)
            #             else:
            #                 data.append('')
            #             if inv.dentist.code:
            #                 data.append(inv.dentist.code)
            #             else:
            #                 data.append('')
            #             if inv.dentist.speciality:
            #                 data.append(inv.dentist.speciality.name)
            #             else:
            #                 data.append('')
            if inv.patient.apt_id and inv.insurance_company == self.company:
                for apt in inv.patient.apt_id:
                    data = []
                    data.append(inv.patient.name.name)
                    if inv.patient.name.ref:
                        data.append(inv.patient.name.ref)
                    else:
                        data.append('')
                    if inv.patient.dob:
                        data.append(inv.patient.dob)
                    else:
                        data.append('')
                    if inv.patient.nationality_id:
                        data.append(inv.patient.nationality_id.name)
                    else:
                        data.append('')
                    if inv.patient.name.country_id:
                        data.append(inv.patient.name.country_id.name)
                    else:
                        data.append('')
                    if inv.patient.sex:
                        if inv.patient.sex == 'm':
                            data.append('Male')
                        else:
                            data.append('Female')
                    else:
                        data.append('')
                    if inv.patient.mobile:
                        data.append(inv.patient.mobile)
                    else:
                        data.append('')
                    if inv.number:
                        data.append(inv.number)
                    else:
                        data.append('')
                    if inv.dentist:
                        data.append(inv.dentist.name.name)
                    else:
                        data.append('')
                    if inv.dentist.code:
                        data.append(inv.dentist.code)
                    else:
                        data.append('')
                    if inv.dentist.speciality:
                        data.append(inv.dentist.speciality.name)
                    else:
                        data.append('')
                    if apt:
                        data.append(apt.name)
                        if apt.doctor:
                            data.append(apt.doctor.name.name)
                        else:
                            data.append(' ')
                        if apt.appointment_sdate:
                            data.append(apt.appointment_sdate)
                        else:
                            data.append(' ')
                        if apt.appointment_edate:
                            data.append(apt.appointment_edate)
                        else:
                            data.append(' ')
                        if apt.state:
                            data.append(apt.state)
                        else:
                            data.append('')
                    else:
                        data.append('')
                        data.append('')
                        data.append('')
                        data.append('')
                        data.append('')
                    if inv.invoice_line_ids:
                        product = []
                        p_code = []
                        for line in inv.invoice_line_ids:
                            product.append(line.product_id.name)
                            if line.product_id.default_code:
                                p_code.append(str(
                                    line.product_id.default_code))
                            else:
                                p_code.append('')

                            data.append(line.product_id.name or '')
                            data.append(line.product_id.default_code or '')
                            data.append(line.price_unit)
                            data.append(line.discount_amt)
                            data.append(line.amt_paid_by_patient)
                            data.append(line.price_subtotal)

                        if product:
                            product = ','.join(product)
                        else:
                            product = ''
                        if p_code:
                            p_code = ','.join(p_code)
                        else:
                            p_code = ''
#                         data.append(product)
#                         data.append(p_code)

                    else:
                        data.append('')

                    data_list.append(data)

#         print("\n\n\n\n\n",data)
#         data_list = [['1','2'],['1','GFHC'],['22','adygb']]
        r += 1
        for data in data_list:
            c = 0
            for item in data:
                worksheet.write(r, c, item, normal)
                c += 1
            r += 1

        buf = io.BytesIO()
        workbook.save(buf)
        out = base64.encodestring(buf.getvalue())

        #         workbook.close()
        #         data = base64.b64encode(open('/tmp/abc.xlsx','rb').read())
        #         self.file = data
        #         self.file_name = 'demo.xlsx'
        #
        #         return {
        #                 'type': 'ir.actions.report.xml',
        #                 'report_type': 'controller',
        #                 'report_file': '/web/content/dental.claim.wizard/%s/data/%s.csv?download=true' % (self.id, 'file'),
        #             }

        name = "claim_report.xls"
        self.write({'state': 'get', 'data': out, 'name': name})
        return {
            'type': 'ir.actions.act_window',
            'res_model': 'dental.claim.wizard',
            'view_mode': 'form',
            'view_type': 'form',
            'res_id': self.id,
            'views': [(False, 'form')],
            'target': 'new',
        }
Beispiel #27
0
    def generate_excel(self):
        filename = 'Sale Invoice Export.xls'
        workbook = xlwt.Workbook(encoding="UTF-8")
        worksheet = workbook.add_sheet('Sale Invoice Header')
        worksheet1 = workbook.add_sheet('Sale Invoice Detail')
        worksheet2 = workbook.add_sheet('Payment Term')
        style = xlwt.easyxf(
            'font:height 200, bold True, name Arial;align: horiz center;')
        date_format = xlwt.XFStyle()
        date_format.num_format_str = 'dd/mm/yyyy'

        worksheet.col(0).width = 4000
        worksheet.col(1).width = 4000
        worksheet.col(2).width = 4000
        worksheet.col(3).width = 4000
        worksheet.col(4).width = 4000
        worksheet.col(5).width = 4000
        worksheet.col(6).width = 4000
        worksheet.col(7).width = 3000
        worksheet.col(8).width = 3000
        worksheet.col(9).width = 3000

        worksheet1.col(0).width = 4000
        worksheet1.col(1).width = 6000
        worksheet1.col(2).width = 4000
        worksheet1.col(3).width = 4000
        worksheet1.col(4).width = 4000
        worksheet1.col(5).width = 4000
        worksheet1.col(6).width = 4000
        worksheet1.col(7).width = 3000
        worksheet1.col(8).width = 3000
        worksheet1.col(9).width = 3000

        worksheet2.col(0).width = 4000
        worksheet2.col(1).width = 6000
        worksheet2.col(2).width = 4000
        worksheet2.col(3).width = 4000
        worksheet2.col(4).width = 8000

        worksheet.write(0, 0, "Customer", style)
        worksheet.write(0, 1, "Invoice date", style)
        worksheet.write(0, 2, "Invoice Number", style)
        worksheet.write(0, 3, "Site/Company", style)
        worksheet.write(0, 4, "Sales Person", style)
        worksheet.write(0, 5, "Due date", style)
        worksheet.write(0, 6, "Source Document", style)
        worksheet.write(0, 7, "Tax Ex Amt", style)
        worksheet.write(0, 8, "Tax", style)
        worksheet.write(0, 9, "Total", style)

        row0 = 1
        column0 = 0
        worksheet.write(row0, column0, self.partner_id.name)
        worksheet.write(row0, column0 + 1, self.date_invoice, date_format)
        worksheet.write(row0, column0 + 2, self.number)
        worksheet.write(row0, column0 + 3, self.company_id.name)
        worksheet.write(row0, column0 + 4, self.user_id.name)
        worksheet.write(row0, column0 + 5, self.date_due, date_format)
        worksheet.write(row0, column0 + 6, self.origin)
        worksheet.write(row0, column0 + 7, self.amount_total)
        worksheet.write(row0, column0 + 8, self.amount_tax)
        worksheet.write(row0, column0 + 9, self.amount_total)

        worksheet1.write(0, 0, 'Customer:', style)
        worksheet1.write(0, 1, 'Invoice Date:', style)
        worksheet1.write(0, 2, 'Site/Compnay:', style)
        worksheet1.write(0, 3, 'Invoice Number:', style)
        worksheet1.write(0, 4, "Product", style)
        worksheet1.write(0, 5, "Product description", style)
        worksheet1.write(0, 6, "Account", style)
        worksheet1.write(0, 7, "Qty", style)
        worksheet1.write(0, 8, "Tax Ex Amt", style)
        worksheet1.write(0, 9, "Tax", style)
        worksheet1.write(0, 10, "Total", style)
        row1 = 1
        column1 = 0
        for invoice in self.invoice_line_ids:
            worksheet1.write(row1, column1, self.partner_id.name)
            worksheet1.write(row1, column1 + 1, self.date_invoice, date_format)
            worksheet1.write(row1, column1 + 2, self.company_id.name)
            worksheet1.write(row1, column1 + 3, self.number)
            if invoice.product_id:
                worksheet1.write(row1, column1 + 4, invoice.product_id.name)
            if invoice.name:
                worksheet1.write(row1, column1 + 5, invoice.name)
            if invoice.account_id:
                worksheet1.write(row1, column1 + 6, invoice.account_id.name)
            if invoice.quantity:
                worksheet1.write(row1, column1 + 7, invoice.quantity)
            if invoice.price_subtotal:
                worksheet1.write(row1, column1 + 8, invoice.price_subtotal)
            if invoice.price_total:
                worksheet1.write(row1, column1 + 9,
                                 invoice.price_total - invoice.price_subtotal)
            if invoice.price_total:
                worksheet1.write(row1, column1 + 10, invoice.price_total)
            row1 += 1

        worksheet2.write(0, 0, "Customer", style)
        worksheet2.write(0, 1, "Invoice date", style)
        worksheet2.write(0, 2, "Invoice Number", style)
        worksheet2.write(0, 3, "Due date", style)
        worksheet2.write(0, 4, "Payment Terms", style)

        row2 = 1
        column2 = 0
        worksheet2.write(row2, column2, self.partner_id.name)
        worksheet2.write(row2, column2 + 1, self.date_invoice, date_format)
        worksheet2.write(row2, column2 + 2, self.number)
        worksheet2.write(row2, column2 + 3, self.date_due, date_format)
        worksheet2.write(row2, column2 + 4, self.payment_term_id.name)

        fp = BytesIO()
        workbook.save(fp)
        export_id = self.env['sale.invoice.excel.report.wiz'].create({
            'report_excel_file':
            base64.encodestring(fp.getvalue()),
            'report_file_name':
            filename
        })
        fp.close()
        return {
            'view_mode': 'form',
            'res_id': export_id.id,
            'res_model': 'sale.invoice.excel.report.wiz',
            'view_type': 'form',
            'type': 'ir.actions.act_window',
            'context': self._context,
            'target': 'new',
        }
Beispiel #28
0
    def set_export_student(self):
        # Nom du fichier de sortie
        filename = str(self.file_name) + '.xls'

        # Style du tableau Excel
        workbook = xlwt.Workbook()
        worksheet = workbook.add_sheet('Export élèves')
        font = xlwt.Font()
        font.bold = True
        for_left = xlwt.easyxf(
            "font: bold 1, color black; borders: top double, bottom double, left double, right double; align: horiz left"
        )
        for_left_not_bold = xlwt.easyxf("font: color black; align: horiz left")
        for_center_bold = xlwt.easyxf(
            "font: bold 1, color black; align: horiz center")
        style = xlwt.easyxf(
            'font:height 400, bold True, name Arial; align: horiz center, vert center;borders: top medium,right medium,bottom medium,left medium'
        )

        alignment = xlwt.Alignment()  # Create Alignment
        alignment.horz = xlwt.Alignment.HORZ_RIGHT
        style = xlwt.easyxf('align: wrap yes')
        style.num_format_str = '0.00'

        worksheet.row(0).height = 500
        worksheet.col(0).width = 4000
        worksheet.col(1).width = 4000
        borders = xlwt.Borders()
        borders.bottom = xlwt.Borders.MEDIUM
        border_style = xlwt.XFStyle()  # Create Style
        border_style.borders = borders

        # Traitement des données pour export
        row = 0
        for student in self.student_ids.sorted(
                key=lambda x: x.partner_id.name):
            column = 0
            # Nom et prénom de ou des enfants selectionnés obligatoire à la génération du fichier
            if row == 0:
                worksheet.write(row, column, 'Enfant - Nom et prénom',
                                for_left)
            worksheet.write(row + 1, column, student.partner_id.name,
                            for_left_not_bold)
            column += 1
            # Informations des élèves selectionnés
            if self.student_contact_information:
                school_birthdate = datetime.strptime(
                    student.school_birthdate, "%Y-%m-%d").strftime("%d/%m/%Y")
                if row == 0:
                    column_libelle = column
                    libelles = ('Enfant - Adresse',
                                'Enfant - Date de naissance',
                                'Enfant - Lieu de naissance')
                    for libelle in libelles:
                        worksheet.write(row, column_libelle, libelle, for_left)
                        column_libelle += 1
                values_list = (student.partner_id.street, school_birthdate,
                               student.school_place_of_birth.name)
                for value_list in values_list:
                    if not value_list:
                        value_list = ""
                    worksheet.write(row + 1, column, value_list,
                                    for_left_not_bold)
                    column += 1

            # Informations sur les inscriptions scolaires des élèves selectionnés
            if self.student_school_information:
                school_registration = datetime.strptime(
                    student.school_registration,
                    "%Y-%m-%d").strftime("%d/%m/%Y")
                school_end_date = datetime.strptime(
                    student.school_end_date, "%Y-%m-%d").strftime("%d/%m/%Y")
                if row == 0:
                    column_libelle = column
                    libelles = ('École - Début', 'École - Fin',
                                'Établissement', 'Niveau', 'Onde',
                                'Resp. - Nom et prénom', 'Resp. - Tél. 1',
                                'Resp. - Tél. 2')
                    for libelle in libelles:
                        worksheet.write(row, column_libelle, libelle, for_left)
                        column_libelle += 1
                values_list = (school_registration, school_end_date,
                               student.school_name_id.name,
                               student.school_level_id.name,
                               student.school_send_onde,
                               student.school_responsible_partner.name,
                               student.school_responsible_partner.phone,
                               student.school_responsible_partner.mobile)
                for value_list in values_list:
                    if not value_list:
                        value_list = ""
                    worksheet.write(row + 1, column, value_list,
                                    for_left_not_bold)
                    column += 1

            # Informations détaillées sur les inscriptions scolaires des élèves selectionnés
            if self.student_school_information_details:
                if row == 0:
                    column_libelle = column
                    libelles = ('École - Commentaire', 'École - Dérogation',
                                'Dérogation - Motif', 'Dérogation - Statut')
                    for libelle in libelles:
                        worksheet.write(row, column_libelle, libelle, for_left)
                        column_libelle += 1
                values_list = (student.school_text, student.school_derogation,
                               student.school_derogation_reason,
                               student.school_derogation_state)
                for value_list in values_list:
                    if not value_list:
                        value_list = ""
                    worksheet.write(row + 1, column, value_list,
                                    for_left_not_bold)
                    column += 1

            # Informations sur le responsable - inscription scolaire
            if self.responsible_school_contact_information:
                if row == 0:
                    column_libelle = column
                    libelles = ('Resp. - Adresse', 'Resp. - Email')
                    for libelle in libelles:
                        worksheet.write(row, column_libelle, libelle, for_left)
                        column_libelle += 1
                values_list = (student.school_responsible_partner.street,
                               student.school_responsible_partner.email)
                for value_list in values_list:
                    if not value_list:
                        value_list = ""
                    worksheet.write(row + 1, column, value_list,
                                    for_left_not_bold)
                    column += 1

            # Informations sur les inscriptions restauration scolaire des élèves selectionnés
            if self.student_school_catering_information:
                if student.half_pension:
                    half_pension_begin_date = datetime.strptime(
                        student.half_pension_begin_date,
                        "%Y-%m-%d").strftime("%d/%m/%Y")
                    half_pension_end_date = datetime.strptime(
                        student.half_pension_end_date,
                        "%Y-%m-%d").strftime("%d/%m/%Y")
                else:
                    half_pension_begin_date = ""
                    half_pension_end_date = ""
                if row == 0:
                    column_libelle = column
                    libelles = ('Rest. - Inscrit', 'Rest. - Lieu',
                                'Rest.- Début', 'Rest. - Fin',
                                'Spécifications', 'Resp. - Nom et prénom',
                                'Resp. - Tél. 1', 'Res. - Tél. 2')
                    for libelle in libelles:
                        worksheet.write(row, column_libelle, libelle, for_left)
                        column_libelle += 1
                values_list = (student.half_pension,
                               student.half_pension_id.name,
                               half_pension_begin_date, half_pension_end_date,
                               student.half_pension_specification,
                               student.half_pension_responsible_partner.name,
                               student.half_pension_responsible_partner.phone,
                               student.half_pension_responsible_partner.mobile)
                for value_list in values_list:
                    if not value_list:
                        value_list = ""
                    worksheet.write(row + 1, column, value_list,
                                    for_left_not_bold)
                    column += 1

            # Informations détaillées sur les inscriptions restauration scolaires des élèves selectionnés
            if self.student_school_catering_information_details:
                if row == 0:
                    column_libelle = column
                    libelles = ('Lundi', 'Mardi', 'Jeudi', 'Vendredi',
                                'Occasionnelle', 'Rest.-  Commentaires')
                    for libelle in libelles:
                        worksheet.write(row, column_libelle, libelle, for_left)
                        column_libelle += 1
                values_list = (student.half_pension_monday,
                               student.half_pension_tuesday,
                               student.half_pension_thursday,
                               student.half_pension_friday,
                               student.half_pension_occasional,
                               student.half_pension_text)
                for value_list in values_list:
                    if not value_list:
                        value_list = ""
                    worksheet.write(row + 1, column, value_list,
                                    for_left_not_bold)
                    column += 1

            # Informations sur le responsable - restauration scolaire
            if self.responsible_school_catering_contact_information:
                if row == 0:
                    column_libelle = column
                    libelles = ('Resp. Rest. - Adresse', 'Resp. Rest. - Email')
                    for libelle in libelles:
                        worksheet.write(row, column_libelle, libelle, for_left)
                        column_libelle += 1
                values_list = (student.half_pension_responsible_partner.street,
                               student.half_pension_responsible_partner.email)
                for value_list in values_list:
                    if not value_list:
                        value_list = ""
                    worksheet.write(row + 1, column, value_list,
                                    for_left_not_bold)
                    column += 1

            # Informations sur les inscriptions garderie des élèves selectionnés
            if self.student_nursery_information:
                if student.nursery_morning or student.nursery_evening:
                    nursery_begin_date = datetime.strptime(
                        student.nursery_begin_date,
                        "%Y-%m-%d").strftime("%d/%m/%Y")
                    nursery_end_date = datetime.strptime(
                        student.nursery_end_date,
                        "%Y-%m-%d").strftime("%d/%m/%Y")
                else:
                    nursery_begin_date = ""
                    nursery_end_date = ""
                if row == 0:
                    column_libelle = column
                    libelles = ('Gard. - Lieu', 'Gard. - Début', 'Gard. - Fin',
                                'Resp. - Nom et prénom', 'Resp. - Tél. 1',
                                'Resp. - Tél. 2')
                    for libelle in libelles:
                        worksheet.write(row, column_libelle, libelle, for_left)
                        column_libelle += 1
                values_list = (student.nursery_name_id.name,
                               nursery_begin_date, nursery_end_date,
                               student.nursery_responsible_partner.name,
                               student.nursery_responsible_partner.phone,
                               student.nursery_responsible_partner.mobile)
                for value_list in values_list:
                    if not value_list:
                        value_list = ""
                    worksheet.write(row + 1, column, value_list,
                                    for_left_not_bold)
                    column += 1

            # Informations détaillées sur les inscriptions garderie des élèves selectionnés
            if self.student_nursery_information_details:
                if row == 0:
                    column_libelle = column
                    worksheet.write(row, column_libelle,
                                    'Gard. - Commentaires', for_left)
                    column_libelle += 1
                nursery_text = student.nursery_text
                if not nursery_text:
                    nursery_text = ""
                worksheet.write(row + 1, column, nursery_text,
                                for_left_not_bold)
                column += 1

            # Informations sur le responsable - garderie
            if self.responsible_nursery_contact_information:
                if row == 0:
                    column_libelle = column
                    libelles = ('Resp. Gard. - Adresse', 'Resp. Gard. - Email')
                    for libelle in libelles:
                        worksheet.write(row, column_libelle, libelle, for_left)
                        column_libelle += 1
                values_list = (student.nursery_responsible_partner.street,
                               student.nursery_responsible_partner.email)
                for value_list in values_list:
                    if not value_list:
                        value_list = ""
                    worksheet.write(row + 1, column, value_list,
                                    for_left_not_bold)
                    column += 1

            row += 1

        fp = io.BytesIO()
        # Sauvegarde des fichiers apres écriture (write)
        workbook.save(fp)
        # Stockage sous forme binaire du fichier dans la base Odoo (table export.student)
        export_student_id = self.env['export.student'].create({
            'excel_file':
            base64.encodestring(fp.getvalue()),
            'file_name':
            filename
        })
        fp.close()

        # Retourne le même formulaire avec le fichier Excel récupéré
        return {
            'view_mode': 'form',
            'res_id': export_student_id.id,
            'res_model': 'export.student',
            'view_type': 'form',
            'type': 'ir.actions.act_window',
            'context': self._context,
            'target': 'new',
        }
Beispiel #29
0
 def new_workbook(self):
     workbook = xlwt.Workbook()
     return workbook
Beispiel #30
0
    def _print_exp_report(self, data):
        res = {}
        period_length = data['form']['period_length']
        if period_length <= 0:
            raise except_orm(_('User Error!'),
                             _('You must set a period length greater than 0.'))
        if not data['form']['date_from']:
            raise except_orm(_('User Error!'), _('You must set a start date.'))

        start = datetime.strptime(data['form']['date_from'], "%Y-%m-%d")
        for i in range(7)[::-1]:
            stop = start - relativedelta(days=period_length)
            res[str(i)] = {
                'name': (i != 0 and (str(
                    (7 - (i + 1)) * period_length) + '-' + str(
                        (7 - i) * period_length))
                         or ('+' + str(6 * period_length))),
                'stop':
                start.strftime('%Y-%m-%d'),
                'start': (i != 0 and stop.strftime('%Y-%m-%d') or False),
            }
            start = stop - relativedelta(days=1)
        data['form'].update(res)
        import base64
        filename = 'Inventory Ageing Report.xls'
        workbook = xlwt.Workbook()
        worksheet = workbook.add_sheet('Inventory Ageing Report')

        header_style = easyxf(
            'font:height 200;pattern: pattern solid, fore_color black; align: horiz center;font: color white; font:bold True;'
            "borders: top thin,left thin,right thin,bottom thin")
        text_left = easyxf('font:height 200; align: horiz left;'
                           "borders: top thin,bottom thin")
        text_center = easyxf('font:height 200; align: horiz center;'
                             "borders: top thin,bottom thin")
        text_right = easyxf('font:height 200; align: horiz right;'
                            "borders: top thin,bottom thin")

        first_col = worksheet.col(0)
        second_col = worksheet.col(1)
        third_col = worksheet.col(2)
        four_col = worksheet.col(3)

        five_col = worksheet.col(4)
        six_col = worksheet.col(5)
        seven_col = worksheet.col(6)
        eight_col = worksheet.col(7)
        nine_col = worksheet.col(8)
        ten_col = worksheet.col(9)
        eleven_col = worksheet.col(10)

        first_col.width = 130 * 30
        second_col.width = 150 * 30
        third_col.width = 180 * 30
        four_col.width = 130 * 30
        five_col.width = 150 * 30
        six_col.width = 130 * 30
        seven_col.width = 130 * 30
        eight_col.width = 130 * 30
        nine_col.width = 130 * 30
        ten_col.width = 130 * 30
        eleven_col.width = 130 * 30

        worksheet.write_merge(
            0, 1, 0, 3, 'Inventory Ageing Report',
            easyxf('font:height 400; align: horiz center;font:bold True;'
                   "borders: top thin,bottom thin , left thin, right thin"))
        date_from = data['form']['date_from'] or ' '
        if date_from:
            date = datetime.strptime(date_from.split(' ')[0], '%Y-%m-%d')
            date_from = date.strftime('%d-%m-%Y')
            worksheet.write(2, 4, 'Start Date' + '-' + str(date_from))
        worksheet.write(5, 0, 'Product', header_style)
        worksheet.write(5, 1, data['form']['6']['name'], header_style)
        worksheet.write(5, 2, data['form']['5']['name'], header_style)
        worksheet.write(5, 3, data['form']['4']['name'], header_style)
        worksheet.write(5, 4, data['form']['3']['name'], header_style)
        worksheet.write(5, 5, data['form']['2']['name'], header_style)
        worksheet.write(5, 6, data['form']['1']['name'], header_style)
        worksheet.write(5, 7, data['form']['0']['name'], header_style)
        line = self.get_lines(data['form'])
        if line:
            i = 6
            p = 0
            for product in line:
                if product.get('onhand_qty',
                               0) and product.get('onhand_qty', 0) > 0 and (
                                   product['0'] > 0 or product['1'] > 0
                                   or product['2'] > 0 or product['3'] > 0
                                   or product['4'] > 0 or product['5'] > 0
                                   or product['6'] > 0):
                    p += 1
                    worksheet.write(i, 0, product['pname'])

                if product.get('onhand_qty', 0) != 0 and product.get(
                        'onhand_qty',
                        0) > 0 and (product['0'] > 0 or product['1'] > 0
                                    or product['2'] > 0 or product['3'] > 0
                                    or product['4'] > 0 or product['5'] > 0
                                    or product['6'] > 0):
                    p += 1
                    worksheet.write(i, 1, product['6'])

                if product.get('onhand_qty', 0) != 0 and product.get(
                        'onhand_qty',
                        0) > 0 and (product['0'] > 0 or product['1'] > 0
                                    or product['2'] > 0 or product['3'] > 0
                                    or product['4'] > 0 or product['5'] > 0
                                    or product['6'] > 0):
                    p += 1
                    worksheet.write(i, 2, product['5'])

                if product.get('onhand_qty', 0) != 0 and product.get(
                        'onhand_qty',
                        0) > 0 and (product['0'] > 0 or product['1'] > 0
                                    or product['2'] > 0 or product['3'] > 0
                                    or product['4'] > 0 or product['5'] > 0
                                    or product['6'] > 0):
                    p += 1
                    worksheet.write(i, 3, product['4'])

                if product.get('onhand_qty', 0) != 0 and product.get(
                        'onhand_qty',
                        0) > 0 and (product['0'] > 0 or product['1'] > 0
                                    or product['2'] > 0 or product['3'] > 0
                                    or product['4'] > 0
                                    ) or product['5'] > 0 or product['6'] > 0:
                    p += 1
                    worksheet.write(i, 4, product['3'])

                if product.get('onhand_qty', 0) != 0 and product.get(
                        'onhand_qty',
                        0) > 0 and (product['0'] > 0 or product['1'] > 0
                                    or product['2'] > 0 or product['3'] > 0
                                    or product['4'] > 0 or product['5'] > 0
                                    or product['6'] > 0):
                    p += 1
                    worksheet.write(i, 5, product['2'])

                if product.get('onhand_qty', 0) != 0 and product.get(
                        'onhand_qty',
                        0) > 0 and (product['0'] > 0 or product['1'] > 0
                                    or product['2'] > 0 or product['3'] > 0
                                    or product['4'] > 0 or product['5'] > 0
                                    or product['6'] > 0):
                    p += 1
                    worksheet.write(i, 6, product['1'])
                if product.get('onhand_qty', 0) != 0 and product.get(
                        'onhand_qty',
                        0) > 0 and (product['0'] > 0 or product['1'] > 0
                                    or product['2'] > 0 or product['3'] > 0
                                    or product['4'] > 0 or product['5'] > 0
                                    or product['6'] > 0):
                    p += 1
                    worksheet.write(i, 7, product['0'])
                if p > 0:
                    i += 1
                p = 0

        fp = BytesIO()
        workbook.save(fp)
        export_id = self.env['inventory.age.dow'].create({
            'excel_file':
            base64.encodestring(fp.getvalue()),
            'file_name':
            filename
        })
        fp.close()

        return {
            'view_mode': 'form',
            'res_id': export_id.id,
            'res_model': 'inventory.age.dow',
            'view_type': 'form',
            'type': 'ir.actions.act_window',
            'context': self._context,
            'target': 'new',
        }