def download_tuantra(self, id, **kw): workbook = xlwt.Workbook() worksheet = workbook.add_sheet('Sheet 1', cell_overwrite_ok=True) ALIGN_BORDER_dict = { 'align': { 'horiz': 'left', 'vert': 'centre', 'wrap': 'yes' }, "borders": { 'left': 'thin', 'right': 'thin', 'top': 'thin', 'bottom': 'thin' } } title_format_dict = deepcopy(ALIGN_BORDER_dict) title_format_dict['align']['horiz'] = 'centre' title_format_dict['font'] = {"bold": "on"} title_format_txt = adict_flat(title_format_dict) title_format_style = xlwt.easyxf(title_format_txt) normal_txt = adict_flat(ALIGN_BORDER_dict) normal_style = xlwt.easyxf(normal_txt) worksheet.write_merge(0, 0, 0, 15, u"BÁO CÁO TUẦN TRA NGÀY 20/09/2017", title_format_style) worksheet.write_merge(1, 2, 0, 0, u"STT", title_format_style) worksheet.write_merge(1, 2, 1, 1, u"Trạm", title_format_style) worksheet.write_merge(1, 2, 2, 2, u"Hướng Tuyến", title_format_style) worksheet.write_merge(1, 2, 3, 3, u"TTV – GSV", title_format_style) worksheet.write_merge(1, 2, 4, 4, u"GPS", title_format_style) worksheet.write_merge(1, 1, 5, 6, u"LƯỢT ĐI", title_format_style) worksheet.write_merge(2, 2, 5, 5, u"GIỜ ĐI", title_format_style) worksheet.write_merge(2, 2, 6, 6, u"GIỜ ĐẾN", title_format_style) worksheet.write_merge(1, 1, 7, 8, u"LƯỢT VỀ", title_format_style) worksheet.write_merge(2, 2, 7, 7, u"GIỜ ĐI", title_format_style) worksheet.write_merge(2, 2, 8, 8, u"GIỜ ĐẾN", title_format_style) worksheet.write_merge(1, 1, 9, 10, u"SỐ ĐIỆN THOẠI", title_format_style) worksheet.write_merge(2, 2, 9, 9, u"ĐẦU TUYẾN", title_format_style) worksheet.write_merge(2, 2, 10, 10, u"CUỐI TUYẾN", title_format_style) worksheet.write_merge(1, 2, 11, 11, u"nội dung", title_format_style) worksheet.write_merge(1, 1, 12, 14, u"KẾ HOẠCH NGÀY HÔM SAU", title_format_style) worksheet.write_merge(2, 2, 12, 12, u"Tuần tra", title_format_style) worksheet.write_merge(2, 2, 13, 13, u"Giám sát", title_format_style) worksheet.write_merge(2, 2, 14, 14, u"Bảo dưỡng – Xử Lý", title_format_style) worksheet.write_merge(1, 2, 15, 15, u"ghi chú", title_format_style) worksheet.write_merge(1, 2, 16, 16, u"kiến nghị", title_format_style) fp = StringIO() workbook.save(fp) fp.seek(0) data = fp.read() fp.close() return request.make_response( data, #self.from_data(columns_headers, rows), headers=[('Content-Disposition', 'attachment; filename="bao_cao_tuan_tra_cq.xls"'), ('Content-Type', 'application/octet-stream')], #cookies={'fileToken': token} )
def get_xls(self, token): workbook = xlwt.Workbook() worksheet = workbook.add_sheet(self.header_text or 'Sheet-1') field_model = self.env['ir.model.fields'] for val in self: model = val.model_name.model model_obj = self.env[model] field_sel = [] lable_sel = [] for field_name in val.field_name: field_sel.append(field_name.name) lable_sel.append(field_name.field_description) if self.group_by and self.group_by_field.name not in field_sel: field_sel.append(self.group_by_field.name) # lable_sel.append(self.group_by_field.field_description) if not len(field_sel): fld = field_model.search([('model_id', '=', val.model_name.id), ('ttype', '!=', 'binary')]) if len(fld): for f in field_model.browse(fld): field_sel.append(f.name) else: raise UserError(_('No column found to Export')) domain = [] for d_line in val.domain_lines: temp = () d_val = str(d_line.value) or False if d_val in ('false', 'False'): d_val = False if d_val in ('true', 'True'): d_val = True temp = (str(d_line.field_name.name), str(d_line.operator), d_val) domain.append(temp) limit = val.limit_rec or None order_field = val.order_on_field and val.order_on_field.name or None if order_field and val.order_type: order = order_field + ' desc' elif order_field: order = order_field else: order = None # if self.group_by: # try: # grp_recs= model_obj.read_group(domain, field_sel, self.group_by_field.name, offset=val.set_offset, limit = limit, order = order ) # except: # grp_recs= model_obj.read_group(domain, field_sel, self.group_by_field.name, offset=val.set_offset, limit = limit, orderby = order ) # print"----------------------------------- grp_recs ----------------------",grp_recs # # if not field_sel: # if grp_recs: # field_sel = grp_recs[0].keys() # else: # raise UserError(_('No record found to Export')) # # result = [] # # result.append(field_sel) # # for rec in grp_recs: # value = '' # temp = [] # for key in field_sel: # print"-------------------------- grp_key --------------------", key # v = rec.get(key) # if v: # if type(v) == tuple: # if val.m2m_value: # value = v[1] # else: # value = v[0] # else: # value = str(v) # else: # value = v # temp.append(value) # result.append(temp) # # else: try: recs = model_obj.search_read(domain, field_sel, offset=val.set_offset, limit=limit, order=order) # print"-----------------------------------recs-----------------------",recs except: mod_ids = model_obj.search(domain, offset=val.set_offset, limit=limit, order=order) recs = model_obj.read(mod_ids, field_sel) if not field_sel: if recs: field_sel = recs[0].keys() else: raise UserError(_('No record found to Export')) result = [] # result.append(field_sel) if self.group_by: dict_grp = {} # a = self.group_by_field.name # print "========================== recs ==================", recs for rec in recs: value = '' temp = [] key1 = rec.get(self.group_by_field.name) # print "========================== rec ==================", rec # print "========================== a ===================", self.group_by_field.name # print if not dict_grp.get(key1): dict_grp.update({key1: [rec]}) else: dict_grp.update({key1: dict_grp[key1] + [rec]}) # print "========================== dict ===================", dict_grp for key in field_sel: # print"-------------------------- key --------------------",key v = rec.get(key) if v: if type(v) == tuple: if val.m2m_value: value = v[1] else: value = v[0] else: value = str(v) else: value = v temp.append(value) # dict_grp.update({a: temp}) # print "-------------- dict ----------",dict_grp result.append(temp) # print "dodddddddddddddddddddddd",dict_grp else: for rec in recs: value = '' temp = [] for key in field_sel: # print"-------------------------- key --------------------", key v = rec.get(key) # print"------------------------- rec ----------------------",rec # print"------------------------- v ----------------------",v if v: if type(v) == tuple: if val.m2m_value: value = v[1] else: value = v[0] else: value = str(v) else: value = v temp.append(value) # print"----------- temp ------------------",temp result.append(temp) # print"----------- result ------------------",result style_table_header = xlwt.easyxf( 'font: bold on; align: wrap on, vert centre, horiz center; pattern: pattern solid, fore_colour gray25;' ) style_col_header = xlwt.easyxf( 'font: bold on; align: wrap on, vert centre, horiz center; pattern: pattern solid, fore_colour gray25;' ) style_grp_header = xlwt.easyxf( 'font: bold on; align: wrap on, vert centre; pattern: pattern solid, fore_colour gray50;' ) data_style = xlwt.easyxf( 'align: wrap on, vert centre, horiz center;') # datetime_style = xlwt.easyxf('align: wrap yes', num_format_str='YYYY-MM-DD HH:mm:SS') # Write Header Cell # header_cell = len(field_sel) / 2 # if self.group_by: # print"============== before =================",field_sel # field_sel.pop() # print"============== after =================",field_sel worksheet.write_merge(1, 2, 1, len(field_sel) - 2, self.header_text, style_table_header) # worksheet.write(0, header_cell,self.header_text,style_table_header) # if (len(field_sel) % 2) == 0: # worksheet.write_merge(1, 2, header_cell-1, header_cell, self.header_text, style_table_header) # else: # worksheet.write_merge(1, 2, header_cell, header_cell,self.header_text,style_table_header) # Write Table Header Row for i, field_name in enumerate(lable_sel): worksheet.write_merge(4, 5, i, i, field_name, style_col_header) # worksheet.write(3, i, field_name,style_col_header) worksheet.col(i).width = 5000 # around 220 pixels # print"--------------- lable_sel------------------------",lable_sel # Write Table Data Row if self.group_by: # print"------------ group_by--------------------" row_no = 7 row = 6 for grp in dict_grp: # print"------------------- grp ---------------",dict_grp # print"------------------- grp ---------------",grp # print"------------------- grp ---------------",grp[1] worksheet.write_merge(row, row, 0, 5, grp[1], style_grp_header) row += 1 for data in dict_grp[grp]: # print"--------------data----------------",data # print"----------------",data.values # worksheet.write(row, i, data[i], data_style) i = 0 # for key, value in data.iteritems(): for key in field_sel: v = data.get(key) if v: if type(v) == tuple: if val.m2m_value: value = v[1] else: value = v[0] else: value = str(v) else: value = v # print"-00000000000000 vvvvvvvvvvvvvv 000000000000000000",v # print"-000000000000000 value value value 0000000000000-",value worksheet.write(row, i, value, data_style) i += 1 row += 1 row += 1 # for data in result: # # print"--------------- data in result------------------------", data # # print"--------------- self.group_by_field ------------------------",self.group_by_field.id # # for i, vals in enumerate(data): # worksheet.write(row_no, i, vals, data_style) # worksheet.col(i).width = 5000 # around 220 pixels # # print"--------------- field vals------------------------",vals # row_no += 1 else: # print"------------ NOT group_by--------------------" row_no = 6 for data in result: # print"--------------- data in result------------------------", data for i, vals in enumerate(data): worksheet.write(row_no, i, vals, data_style) worksheet.col(i).width = 5000 # around 220 pixels # print"--------------- field vals------------------------",vals row_no += 1 # Same as above # row =3; c_nbr =0 # for data in result: # c_nbr =0 # print"++++++++++++++++++++++++ data +++++++++++++++++++++++++++++",i,data # for vals in data: # worksheet.write(row, c_nbr, vals) # worksheet.col(c_nbr).width = 8000 # around 220 pixels # c_nbr += 1 # row += 1 file_name = str(val.model_name.name) + '.xls' fp = StringIO.StringIO() workbook.save(fp) fp.seek(0) data = fp.read() fp.close() out = base64.encodestring(data) self.write({'filedata': out, 'filename': file_name}) return { 'name': 'Dynamic Report', 'res_model': 'dynamic.xls.report', 'type': 'ir.actions.act_window', 'view_type': 'form', 'view_mode': 'form', 'target': 'new', 'view_id': self.env.ref( 'gt_dynamic_global_export_excel_report.wizard_dynamic_xls_report_download' ).id, 'res_id': self.id }
def download_document(self, id, **kw): sitetype = kw['sitetype'] if 'mode_1900' in kw: mode_1900 = True else: mode_1900 = False workbook = xlwt.Workbook() worksheet = workbook.add_sheet('Sheet 1', cell_overwrite_ok=True) ALIGN_BORDER_dict = { 'align': { 'horiz': 'left', 'vert': 'centre', 'wrap': 'yes' }, "borders": { 'left': 'thin', 'right': 'thin', 'top': 'thin', 'bottom': 'thin' } } title_format_dict = deepcopy(ALIGN_BORDER_dict) title_format_dict['align']['horiz'] = 'centre' title_format_dict['font'] = {"bold": "on"} title_format_txt = adict_flat(title_format_dict) title_format_style = xlwt.easyxf(title_format_txt) normal_txt = adict_flat(ALIGN_BORDER_dict) normal_style = xlwt.easyxf(normal_txt) date_style = xlwt.easyxf(normal_txt, num_format_str='DD/MM/YYYY') worksheet.write_merge(0, 0, 0, 4, u"Danh sách Update thông tin đối tượng", title_format_style) worksheet.write(1, 0, u"STT", title_format_style) worksheet.write(1, 1, u"Mã đối tượng", title_format_style) worksheet.write(1, 2, u"Thuộc Tính", title_format_style) worksheet.write(1, 3, u"Giá trị cập nhật", title_format_style) worksheet.write(1, 4, u"Ghi chú", title_format_style) row_index = 1 worksheet.col(1).width = int(20 * 260) worksheet.col(2).width = int(25 * 260) worksheet.col(3).width = int(20 * 260) if mode_1900: if sitetype == '3G': env = 'nodeb' elif sitetype == '4G': env = 'enodeb' elif sitetype == '2G': env = 'bts' for i in request.env[env].search([('ngay_bao_duong', '=', False)]): row_index += 1 worksheet.write(row_index, 1, i.ma_tram, normal_style) worksheet.write(row_index, 2, u'Thời gian bảo dưỡng', normal_style) worksheet.write(row_index, 3, datetime.date(1900, 1, 1), date_style) worksheet.write(row_index, 4, u'', normal_style) row_index += 1 worksheet.write(row_index, 1, i.ma_tram, normal_style) worksheet.write(row_index, 2, u'Đơn vị thực hiện', normal_style) worksheet.write(row_index, 3, u'Đài VT TGG', normal_style) worksheet.write(row_index, 4, u'', normal_style) else: import_tuan_id = id model_class = request.env['importbdtuan'] import_tuan = model_class.browse(int(import_tuan_id)) lineimports = import_tuan.lineimports loop = lineimports for line in loop: if import_tuan.tuan_export and line.week_number != import_tuan.tuan_export: continue if sitetype == '2G': ma_doi_tuong = line.bts_id.ma_tram elif sitetype == '3G': ma_doi_tuong = line.nodeb_id.ma_tram date_bd = fields.Datetime.from_string(line.date) if ma_doi_tuong: row_index += 1 worksheet.write(row_index, 1, ma_doi_tuong, normal_style) worksheet.write(row_index, 2, u'Thời gian bảo dưỡng', normal_style) worksheet.write(row_index, 3, date_bd, date_style) worksheet.write(row_index, 4, u'', normal_style) fp = StringIO() workbook.save(fp) fp.seek(0) data = fp.read() fp.close() return request.make_response( data, #self.from_data(columns_headers, rows), headers=[('Content-Disposition', 'attachment; filename="import_rnas_%s.xls"' % sitetype), ('Content-Type', 'application/octet-stream')], #cookies={'fileToken': token} )
def attendance_exportreport_xls(self, appraisal_id, access_token): att_data = request.env['attendance.reports'].browse(appraisal_id) book = xlwt.Workbook(encoding='utf-8') sheet1 = book.add_sheet("Attendance Data") style = xlwt.XFStyle() style_header = xlwt.XFStyle() style_right = xlwt.XFStyle() style_right_bold = xlwt.XFStyle() style_left = xlwt.XFStyle() font = xlwt.Font() font.bold = True style.font = font style_header.font = font style_right_bold.font = font # background color pattern = xlwt.Pattern() pattern.pattern = xlwt.Pattern.SOLID_PATTERN pattern.pattern_fore_colour = xlwt.Style.colour_map['pale_blue'] style.pattern = pattern # style.num_format_str = '0.0' # alignment alignment = xlwt.Alignment() alignment.horz = xlwt.Alignment.HORZ_LEFT style.alignment = alignment style.alignment.wrap = 100 borders = xlwt.Borders() borders.left = xlwt.Borders.THIN borders.right = xlwt.Borders.THIN borders.top = xlwt.Borders.THIN borders.bottom = xlwt.Borders.THIN borders.left_colour = 0x00 borders.right_colour = 0x00 borders.top_colour = 0x00 borders.bottom_colour = 0x00 pattern1 = xlwt.Pattern() pattern1.pattern1 = xlwt.Pattern.SOLID_PATTERN pattern1.pattern_fore_colour = xlwt.Style.colour_map['pale_blue'] style_header.pattern1 = pattern1 style_header.alignment.wrap = 12 # alignment alignment1 = xlwt.Alignment() alignment1.horz = xlwt.Alignment.HORZ_CENTER style_header.alignment = alignment1 alignment2 = xlwt.Alignment() alignment2.horz = xlwt.Alignment.HORZ_RIGHT style_right.alignment = alignment2 style_right_bold.alignment = alignment2 # style_right.num_format_str = '0' style_right.alignment.wrap = 12 style_right_bold.alignment.wrap = 12 alignment3 = xlwt.Alignment() alignment3.horz = xlwt.Alignment.HORZ_LEFT style_left.alignment = alignment3 # style_left.num_format_str = '0.0' style_left.alignment.wrap = 100 # alignment4 = xlwt.Alignment() # alignment4.horz = xlwt.Alignment.HORZ_RIGHT # style_right_bold.alignment = alignment2 # style_right_bold.num_format_str = '0.0' # style_right_bold.alignment.wrap = 12 pattern4 = xlwt.Pattern() pattern4.pattern = xlwt.Pattern.SOLID_PATTERN # style_right_bold.pattern = pattern4 sub_style = xlwt.easyxf('pattern: pattern solid, fore_colour gray80;' 'font: colour white, bold True;') data_style = xlwt.easyxf('pattern: pattern solid, fore_colour white;' 'font: colour black, bold True;') total_style = xlwt.easyxf('pattern: pattern solid, fore_colour yellow;' 'font: colour black, bold True;') style.borders = borders style_left.borders = borders style_header.borders = borders style_right.borders = borders style_right_bold.borders = borders style_right_bold = xlwt.easyxf( 'pattern: pattern solid, fore_colour yellow;' 'font: colour black, bold False;' 'align: horiz right;') row = 0 col = 0 from_date = att_data.from_date to_date = att_data.to_date site_master_id = att_data.site_master_id.id department_id = att_data.department_id.id employee_id = att_data.employee_id.id print("hhhh", from_date, to_date) sheet1.write_merge(2, 2, 0, 1, 'From Date:', sub_style) sheet1.write_merge(2, 2, 2, 5, att_data.from_date, style) sheet1.write_merge(3, 3, 0, 1, 'To Date:', sub_style) sheet1.write_merge(3, 3, 2, 5, att_data.to_date, style) # sheet1.write_merge(4, 4, 0, 1,'Application Year:', sub_style) # sheet1.write_merge(4, 4, 2, 5, appraisal_data.application_year.name, style) attendance_rec = request.env['hr.attendance'].search( [('attendance_date', '>=', from_date), ('attendance_date', '<=', to_date)], order='employee_code, attendance_date') # print(attendance_rec,'attendance') sheet1.write_merge(6, 6, 0, 15, 'Attendance Data', style_header) sheet1.write(8, 0, 'Sr. No.', style_header) sheet1.write_merge(8, 8, 1, 1, 'Employee Code', style_header) sheet1.write_merge(8, 8, 2, 4, 'Employee Name', style_header) sheet1.write_merge(8, 8, 5, 6, 'Department', style_header) sheet1.write_merge(8, 8, 7, 8, 'Designation', style_header) sheet1.write_merge(8, 8, 9, 9, 'Site', style_header) sheet1.write_merge(8, 8, 10, 10, 'Shift', style_header) sheet1.write_merge(8, 8, 11, 11, 'Date', style_header) sheet1.write_merge(8, 8, 12, 12, 'In Time', style_header) sheet1.write_merge(8, 8, 13, 13, 'Out Time', style_header) sheet1.write_merge(8, 8, 14, 14, 'Worked Hours', style_header) sheet1.write_merge(8, 8, 15, 15, 'Early Leaving', style_header) sheet1.write_merge(8, 8, 16, 16, 'Late Coming', style_header) sheet1.write_merge(8, 8, 17, 17, 'Extra Worked Hours', style_header) sheet1.write_merge(8, 8, 18, 18, 'Status', style_header) count = 0 sr_no = [] row = 10 for x in attendance_rec: if x.worked_hours: if x.worked_hours >= 9.0: extra_worked_hours = x.worked_hours - 9.0 extra_hours = float("{0:.2f}".format(extra_worked_hours)) extra_hours = str(extra_hours).replace('.', ':') print(extra_hours, 'extra_hours') worked_hours = str(x.worked_hours).replace('.', ':') else: extra_hours = '00:00' worked_hours = str(x.worked_hours).replace('.', ':') else: extra_hours = '00:00' worked_hours = str(x.worked_hours).replace('.', ':') if x.approve_check == True: final_style = style_right_bold else: final_style = style_right if not site_master_id and not department_id and not employee_id: # print("plainnnnnnnnnnnnnnnnnnn") count += 1 sr_no.append(count) sheet1.write(row, col, count, style_header) sheet1.write_merge(row, row, 1, 1, x.employee_id.emp_code, final_style) sheet1.write_merge(row, row, 2, 4, x.employee_id.name, final_style) sheet1.write_merge(row, row, 5, 6, x.employee_id.department_id.name, final_style) sheet1.write_merge(row, row, 7, 8, x.employee_id.job_id.name, final_style) sheet1.write_merge(row, row, 9, 9, x.employee_id.site_master_id.name, final_style) sheet1.write_merge(row, row, 10, 10, x.shift.name, final_style) sheet1.write_merge(row, row, 11, 11, x.attendance_date, final_style) sheet1.write_merge(row, row, 12, 12, x.in_time, final_style) sheet1.write_merge(row, row, 13, 13, x.out_time, final_style) sheet1.write_merge(row, row, 14, 14, worked_hours, final_style) sheet1.write_merge(row, row, 15, 15, x.early_leaving, final_style) sheet1.write_merge(row, row, 16, 16, x.late_coming, final_style) sheet1.write_merge(row, row, 17, 17, extra_hours, final_style) sheet1.write_merge(row, row, 18, 18, x.employee_status, final_style) row += 1 elif site_master_id and not department_id and not employee_id: # print("site_master_id") if x.employee_id.site_master_id.id == site_master_id: print(x.employee_id.id, 'emp id') count += 1 sr_no.append(count) sheet1.write(row, col, count, style_header) sheet1.write_merge(row, row, 1, 1, x.employee_id.emp_code, final_style) sheet1.write_merge(row, row, 2, 4, x.employee_id.name, final_style) sheet1.write_merge(row, row, 5, 6, x.employee_id.department_id.name, final_style) sheet1.write_merge(row, row, 7, 8, x.employee_id.job_id.name, final_style) sheet1.write_merge(row, row, 9, 9, x.employee_id.site_master_id.name, final_style) sheet1.write_merge(row, row, 10, 10, x.shift.name, final_style) sheet1.write_merge(row, row, 11, 11, x.attendance_date, final_style) sheet1.write_merge(row, row, 12, 12, x.in_time, final_style) sheet1.write_merge(row, row, 13, 13, x.out_time, final_style) sheet1.write_merge(row, row, 14, 14, worked_hours, final_style) sheet1.write_merge(row, row, 15, 15, x.early_leaving, final_style) sheet1.write_merge(row, row, 16, 16, x.late_coming, final_style) sheet1.write_merge(row, row, 17, 17, extra_hours, final_style) sheet1.write_merge(row, row, 18, 18, x.employee_status, final_style) row += 1 elif department_id and not site_master_id and not employee_id: # print("department_id") if x.employee_id.department_id.id == department_id: print(x.employee_id.id, 'emp id') count += 1 sr_no.append(count) sheet1.write(row, col, count, style_header) sheet1.write_merge(row, row, 1, 1, x.employee_id.emp_code, final_style) sheet1.write_merge(row, row, 2, 4, x.employee_id.name, final_style) sheet1.write_merge(row, row, 5, 6, x.employee_id.department_id.name, final_style) sheet1.write_merge(row, row, 7, 8, x.employee_id.job_id.name, final_style) sheet1.write_merge(row, row, 9, 9, x.employee_id.site_master_id.name, final_style) sheet1.write_merge(row, row, 10, 10, x.shift.name, final_style) sheet1.write_merge(row, row, 11, 11, x.attendance_date, final_style) sheet1.write_merge(row, row, 12, 12, x.in_time, final_style) sheet1.write_merge(row, row, 13, 13, x.out_time, final_style) sheet1.write_merge(row, row, 14, 14, worked_hours, final_style) sheet1.write_merge(row, row, 15, 15, x.early_leaving, final_style) sheet1.write_merge(row, row, 16, 16, x.late_coming, final_style) sheet1.write_merge(row, row, 17, 17, extra_hours, final_style) sheet1.write_merge(row, row, 18, 18, x.employee_status, final_style) row += 1 elif site_master_id and department_id and not employee_id: # print("both") if x.employee_id.site_master_id.id == site_master_id and x.employee_id.department_id.id == department_id: print(x.employee_id.id, 'emp id') count += 1 sr_no.append(count) sheet1.write(row, col, count, style_header) sheet1.write_merge(row, row, 1, 1, x.employee_id.emp_code, final_style) sheet1.write_merge(row, row, 2, 4, x.employee_id.name, final_style) sheet1.write_merge(row, row, 5, 6, x.employee_id.department_id.name, final_style) sheet1.write_merge(row, row, 7, 8, x.employee_id.job_id.name, final_style) sheet1.write_merge(row, row, 9, 9, x.employee_id.site_master_id.name, final_style) sheet1.write_merge(row, row, 10, 10, x.shift.name, final_style) sheet1.write_merge(row, row, 11, 11, x.attendance_date, final_style) sheet1.write_merge(row, row, 12, 12, x.in_time, final_style) sheet1.write_merge(row, row, 13, 13, x.out_time, final_style) sheet1.write_merge(row, row, 14, 14, worked_hours, final_style) sheet1.write_merge(row, row, 15, 15, x.early_leaving, final_style) sheet1.write_merge(row, row, 16, 16, x.late_coming, final_style) sheet1.write_merge(row, row, 17, 17, extra_hours, final_style) sheet1.write_merge(row, row, 18, 18, x.employee_status, final_style) row += 1 elif employee_id and not site_master_id and not department_id: if x.employee_id.id == employee_id: print(x.employee_id.id, 'emp id') count += 1 sr_no.append(count) sheet1.write(row, col, count, style_header) sheet1.write_merge(row, row, 1, 1, x.employee_id.emp_code, final_style) sheet1.write_merge(row, row, 2, 4, x.employee_id.name, final_style) sheet1.write_merge(row, row, 5, 6, x.employee_id.department_id.name, final_style) sheet1.write_merge(row, row, 7, 8, x.employee_id.job_id.name, final_style) sheet1.write_merge(row, row, 9, 9, x.employee_id.site_master_id.name, final_style) sheet1.write_merge(row, row, 10, 10, x.shift.name, final_style) sheet1.write_merge(row, row, 11, 11, x.attendance_date, final_style) sheet1.write_merge(row, row, 12, 12, x.in_time, final_style) sheet1.write_merge(row, row, 13, 13, x.out_time, final_style) sheet1.write_merge(row, row, 14, 14, worked_hours, final_style) sheet1.write_merge(row, row, 15, 15, x.early_leaving, final_style) sheet1.write_merge(row, row, 16, 16, x.late_coming, final_style) sheet1.write_merge(row, row, 17, 17, extra_hours, final_style) sheet1.write_merge(row, row, 18, 18, x.employee_status, final_style) row += 1 filename = 'AttendanceExport.xls' #%(appraisal_data.review_cycle) response = request.make_response( None, headers=[('Content-Type', 'application/vnd.ms-excel'), ('Content-Disposition', content_disposition(filename))]) book.save(response.stream) return response
def attendance_month_exportreport_xls(self, att_id, access_token): att_data = request.env['attendance.reports'].browse(att_id) book = xlwt.Workbook(encoding='utf-8') sheet1 = book.add_sheet("Attendance Data") style = xlwt.XFStyle() style_header = xlwt.XFStyle() style_right = xlwt.XFStyle() style_right_bold = xlwt.XFStyle() style_left = xlwt.XFStyle() font = xlwt.Font() font.bold = True style.font = font style_header.font = font style_right_bold.font = font # background color pattern = xlwt.Pattern() pattern.pattern = xlwt.Pattern.SOLID_PATTERN pattern.pattern_fore_colour = xlwt.Style.colour_map['pale_blue'] style.pattern = pattern # style.num_format_str = '0.0' # alignment alignment = xlwt.Alignment() alignment.horz = xlwt.Alignment.HORZ_LEFT style.alignment = alignment style.alignment.wrap = 100 borders = xlwt.Borders() borders.left = xlwt.Borders.THIN borders.right = xlwt.Borders.THIN borders.top = xlwt.Borders.THIN borders.bottom = xlwt.Borders.THIN borders.left_colour = 0x00 borders.right_colour = 0x00 borders.top_colour = 0x00 borders.bottom_colour = 0x00 pattern1 = xlwt.Pattern() pattern1.pattern1 = xlwt.Pattern.SOLID_PATTERN pattern1.pattern_fore_colour = xlwt.Style.colour_map['pale_blue'] style_header.pattern1 = pattern1 style_header.alignment.wrap = 12 # alignment alignment1 = xlwt.Alignment() alignment1.horz = xlwt.Alignment.HORZ_CENTER style_header.alignment = alignment1 alignment2 = xlwt.Alignment() alignment2.horz = xlwt.Alignment.HORZ_RIGHT style_right.alignment = alignment2 # style_right.num_format_str = '0.0' style_right.alignment.wrap = 12 alignment3 = xlwt.Alignment() alignment3.horz = xlwt.Alignment.HORZ_LEFT style_left.alignment = alignment3 # style_left.num_format_str = '0.0' style_left.alignment.wrap = 100 alignment4 = xlwt.Alignment() alignment4.horz = xlwt.Alignment.HORZ_RIGHT style_right_bold.alignment = alignment2 # style_right_bold.num_format_str = '0.0' style_right_bold.alignment.wrap = 12 pattern4 = xlwt.Pattern() pattern4.pattern = xlwt.Pattern.SOLID_PATTERN # style_right_bold.pattern = pattern4 sub_style = xlwt.easyxf('pattern: pattern solid, fore_colour gray80;' 'font: colour white, bold True;') data_style = xlwt.easyxf('pattern: pattern solid, fore_colour white;' 'font: colour black, bold True;') total_style = xlwt.easyxf('pattern: pattern solid, fore_colour yellow;' 'font: colour black, bold True;') style.borders = borders style_left.borders = borders style_header.borders = borders style_right.borders = borders style_right_bold.borders = borders row = 0 col = 0 from_date = att_data.from_date to_date = att_data.to_date site_master_id = att_data.site_master_id.id department_id = att_data.department_id.id employee_id = att_data.employee_id.id print("hhhh", from_date, to_date) sheet1.write_merge(2, 2, 0, 1, 'From Date:', sub_style) sheet1.write_merge(2, 2, 2, 5, att_data.from_date, style) sheet1.write_merge(3, 3, 0, 1, 'To Date:', sub_style) sheet1.write_merge(3, 3, 2, 5, att_data.to_date, style) sheet1.write_merge(6, 6, 0, 21, 'Attendance Data', style_header) sheet1.write(8, 0, 'Sr. No.', style_header) sheet1.write_merge(8, 8, 1, 1, 'Employee Code', style_header) sheet1.write_merge(8, 8, 2, 4, 'Employee Name', style_header) sheet1.write_merge(8, 8, 5, 6, 'Designation', style_header) sheet1.write_merge(8, 8, 7, 8, 'Site', style_header) sheet1.write_merge(8, 8, 9, 9, 'January', style_header) sheet1.write_merge(8, 8, 10, 10, 'February', style_header) sheet1.write_merge(8, 8, 11, 11, 'March', style_header) sheet1.write_merge(8, 8, 12, 12, 'April', style_header) sheet1.write_merge(8, 8, 13, 13, 'May', style_header) sheet1.write_merge(8, 8, 14, 14, 'June', style_header) sheet1.write_merge(8, 8, 15, 15, 'July', style_header) sheet1.write_merge(8, 8, 16, 16, 'August', style_header) sheet1.write_merge(8, 8, 17, 17, 'September', style_header) sheet1.write_merge(8, 8, 18, 18, 'October', style_header) sheet1.write_merge(8, 8, 19, 19, 'November', style_header) sheet1.write_merge(8, 8, 20, 20, 'December', style_header) sheet1.write_merge(8, 8, 21, 21, 'Total', style_header) count = 0 sr_no = [] row = 10 emp_id = [] if site_master_id and not department_id and not employee_id: emp_recs = request.env['hr.employee'].search([ ('active', '=', 't'), ('site_master_id', '=', site_master_id) ]) print(emp_recs, 'emp_recs') for emp in emp_recs: print("------------------------------------------------") attendance_rec = request.env['hr.attendance'].search( [('attendance_date', '>=', from_date), ('attendance_date', '<=', to_date), ('employee_id', '=', emp.id), ('employee_status', 'in', ('P', 'half_day_p_ab', 'half_day_sl', 'half_day_pl'))], order='employee_code, attendance_date') print(attendance_rec, 'attendance') emp_name = emp.name emp_code = emp.emp_code job_id = emp.job_id.name site_id = emp.site_master_id.name month_count = [] if attendance_rec: for x in attendance_rec: print(x.employee_id.name, 'Name') attendance_date = x.attendance_date # print(attendance_date,'attendance_date') month = datetime.strptime(str(attendance_date), "%Y-%m-%d").month # print(month,'month') month_count.append(month) print(month_count, 'month_count') jan = month_count.count(1) feb = month_count.count(2) mar = month_count.count(3) apr = month_count.count(4) may = month_count.count(5) jun = month_count.count(6) jul = month_count.count(7) aug = month_count.count(8) sep = month_count.count(9) octo = month_count.count(10) nov = month_count.count(11) dec = month_count.count(12) total = jan + feb + mar + apr + may + jun + jul + aug + sep + octo + nov + dec print(jan, feb, mar, apr, may, jun, jul, aug, sep, octo, nov, dec, '----------work days count') count += 1 sr_no.append(count) sheet1.write(row, col, count, style_header) sheet1.write_merge(row, row, 1, 1, emp_code, style_right) sheet1.write_merge(row, row, 2, 4, emp_name, style_right) sheet1.write_merge(row, row, 5, 6, job_id, style_right) sheet1.write_merge(row, row, 7, 8, site_id, style_right) sheet1.write_merge(row, row, 9, 9, jan, style_right) sheet1.write_merge(row, row, 10, 10, feb, style_right) sheet1.write_merge(row, row, 11, 11, mar, style_right) sheet1.write_merge(row, row, 12, 12, apr, style_right) sheet1.write_merge(row, row, 13, 13, may, style_right) sheet1.write_merge(row, row, 14, 14, jun, style_right) sheet1.write_merge(row, row, 15, 15, jul, style_right) sheet1.write_merge(row, row, 16, 16, aug, style_right) sheet1.write_merge(row, row, 17, 17, sep, style_right) sheet1.write_merge(row, row, 18, 18, octo, style_right) sheet1.write_merge(row, row, 19, 19, nov, style_right) sheet1.write_merge(row, row, 20, 20, dec, style_right) sheet1.write_merge(row, row, 21, 21, total, style_right) row += 1 elif employee_id and not site_master_id and not department_id: emp_recs = request.env['hr.employee'].search([('active', '=', 't'), ('id', '=', employee_id)]) print(emp_recs, 'emp_recs') month_count = [] for emp in emp_recs: attendance_rec = request.env['hr.attendance'].search( [('attendance_date', '>=', from_date), ('attendance_date', '<=', to_date), ('employee_id', '=', emp.id)], order='employee_code, attendance_date') print(attendance_rec, 'attendance') emp_id = emp.id job_id = emp.job_id.id site_id = emp.site_master_id.id for x in attendance_rec: if x.employee_status in ('P', 'half_day_p_ab', 'half_day_sl', 'half_day_pl'): attendance_date = x.attendance_date # print(attendance_date,'attendance_date') month = datetime.strptime(str(attendance_date), "%Y-%m-%d").month # print(month,'month') month_count.append(month) print(month_count, 'month_count') jan = month_count.count(1) feb = month_count.count(2) mar = month_count.count(3) apr = month_count.count(4) may = month_count.count(5) jun = month_count.count(6) jul = month_count.count(7) aug = month_count.count(8) sep = month_count.count(9) octo = month_count.count(10) nov = month_count.count(11) dec = month_count.count(12) total = jan + feb + mar + apr + may + jun + jul + aug + sep + octo + nov + dec print(jan, feb, mar, apr, may, jun, jul, aug, sep, octo, nov, dec, '----------') count += 1 sr_no.append(count) sheet1.write(row, col, count, style_header) sheet1.write_merge(row, row, 1, 1, emp.emp_code, style_right) sheet1.write_merge(row, row, 2, 4, emp.name, style_right) sheet1.write_merge(row, row, 5, 6, emp.job_id.name, style_right) sheet1.write_merge(row, row, 7, 8, emp.site_master_id.name, style_right) sheet1.write_merge(row, row, 9, 9, jan, style_right) sheet1.write_merge(row, row, 10, 10, feb, style_right) sheet1.write_merge(row, row, 11, 11, mar, style_right) sheet1.write_merge(row, row, 12, 12, apr, style_right) sheet1.write_merge(row, row, 13, 13, may, style_right) sheet1.write_merge(row, row, 14, 14, jun, style_right) sheet1.write_merge(row, row, 15, 15, jul, style_right) sheet1.write_merge(row, row, 16, 16, aug, style_right) sheet1.write_merge(row, row, 17, 17, sep, style_right) sheet1.write_merge(row, row, 18, 18, octo, style_right) sheet1.write_merge(row, row, 19, 19, nov, style_right) sheet1.write_merge(row, row, 20, 20, dec, style_right) sheet1.write_merge(row, row, 21, 21, total, style_right) row += 1 elif site_master_id and employee_id and not department_id: emp_recs = request.env['hr.employee'].search([ ('active', '=', 't'), ('id', '=', employee_id), ('site_master_id', '=', site_master_id) ]) print(emp_recs, 'emp_recs') month_count = [] for emp in emp_recs: print("------------------------------------------------") attendance_rec = request.env['hr.attendance'].search( [('attendance_date', '>=', from_date), ('attendance_date', '<=', to_date), ('employee_id', '=', emp.id), ('employee_status', 'in', ('P', 'half_day_p_ab', 'half_day_sl', 'half_day_pl'))], order='employee_code, attendance_date') print(attendance_rec, 'attendance') emp_name = emp.name emp_code = emp.emp_code job_id = emp.job_id.name site_id = emp.site_master_id.name month_count = [] if attendance_rec: for x in attendance_rec: print(x.employee_id.name, 'Name') attendance_date = x.attendance_date # print(attendance_date,'attendance_date') month = datetime.strptime(str(attendance_date), "%Y-%m-%d").month # print(month,'month') month_count.append(month) print(month_count, 'month_count') jan = month_count.count(1) feb = month_count.count(2) mar = month_count.count(3) apr = month_count.count(4) may = month_count.count(5) jun = month_count.count(6) jul = month_count.count(7) aug = month_count.count(8) sep = month_count.count(9) octo = month_count.count(10) nov = month_count.count(11) dec = month_count.count(12) total = jan + feb + mar + apr + may + jun + jul + aug + sep + octo + nov + dec print(jan, feb, mar, apr, may, jun, jul, aug, sep, octo, nov, dec, '----------work days count') count += 1 sr_no.append(count) sheet1.write(row, col, count, style_header) sheet1.write_merge(row, row, 1, 1, emp_code, style_right) sheet1.write_merge(row, row, 2, 4, emp_name, style_right) sheet1.write_merge(row, row, 5, 6, job_id, style_right) sheet1.write_merge(row, row, 7, 8, site_id, style_right) sheet1.write_merge(row, row, 9, 9, jan, style_right) sheet1.write_merge(row, row, 10, 10, feb, style_right) sheet1.write_merge(row, row, 11, 11, mar, style_right) sheet1.write_merge(row, row, 12, 12, apr, style_right) sheet1.write_merge(row, row, 13, 13, may, style_right) sheet1.write_merge(row, row, 14, 14, jun, style_right) sheet1.write_merge(row, row, 15, 15, jul, style_right) sheet1.write_merge(row, row, 16, 16, aug, style_right) sheet1.write_merge(row, row, 17, 17, sep, style_right) sheet1.write_merge(row, row, 18, 18, octo, style_right) sheet1.write_merge(row, row, 19, 19, nov, style_right) sheet1.write_merge(row, row, 20, 20, dec, style_right) sheet1.write_merge(row, row, 21, 21, total, style_right) row += 1 filename = 'AttendanceExport.xls' response = request.make_response( None, headers=[('Content-Type', 'application/vnd.ms-excel'), ('Content-Disposition', content_disposition(filename))]) book.save(response.stream) return response
def exit_report_xls(self, exit_id, access_token): exit_data = request.env['exit.reports'].browse(exit_id) book = xlwt.Workbook(encoding='utf-8') sheet1 = book.add_sheet("Exit Report") style = xlwt.XFStyle() style_header = xlwt.XFStyle() style_right = xlwt.XFStyle() style_right_bold = xlwt.XFStyle() style_left = xlwt.XFStyle() font = xlwt.Font() font.bold = True style.font = font style_header.font = font style_right_bold.font = font # background color pattern = xlwt.Pattern() pattern.pattern = xlwt.Pattern.SOLID_PATTERN pattern.pattern_fore_colour = xlwt.Style.colour_map['pale_blue'] style.pattern = pattern # style.num_format_str = '0.0' # alignment alignment = xlwt.Alignment() alignment.horz = xlwt.Alignment.HORZ_LEFT style.alignment = alignment style.alignment.wrap = 100 borders = xlwt.Borders() borders.left = xlwt.Borders.THIN borders.right = xlwt.Borders.THIN borders.top = xlwt.Borders.THIN borders.bottom = xlwt.Borders.THIN borders.left_colour = 0x00 borders.right_colour = 0x00 borders.top_colour = 0x00 borders.bottom_colour = 0x00 pattern1 = xlwt.Pattern() pattern1.pattern1 = xlwt.Pattern.SOLID_PATTERN pattern1.pattern_fore_colour = xlwt.Style.colour_map['pale_blue'] style_header.pattern1 = pattern1 style_header.alignment.wrap = 12 # alignment alignment1 = xlwt.Alignment() alignment1.horz = xlwt.Alignment.HORZ_CENTER style_header.alignment = alignment1 alignment2 = xlwt.Alignment() alignment2.horz = xlwt.Alignment.HORZ_RIGHT style_right.alignment = alignment2 # style_right.num_format_str = '0.0' style_right.alignment.wrap = 12 alignment3 = xlwt.Alignment() alignment3.horz = xlwt.Alignment.HORZ_LEFT style_left.alignment = alignment3 # style_left.num_format_str = '0.0' style_left.alignment.wrap = 100 alignment4 = xlwt.Alignment() alignment4.horz = xlwt.Alignment.HORZ_RIGHT style_right_bold.alignment = alignment2 # style_right_bold.num_format_str = '0.0' style_right_bold.alignment.wrap = 12 pattern4 = xlwt.Pattern() pattern4.pattern = xlwt.Pattern.SOLID_PATTERN # style_right_bold.pattern = pattern4 sub_style = xlwt.easyxf('pattern: pattern solid, fore_colour gray80;' 'font: colour white, bold True;') data_style = xlwt.easyxf('pattern: pattern solid, fore_colour white;' 'font: colour black, bold True;') total_style = xlwt.easyxf('pattern: pattern solid, fore_colour yellow;' 'font: colour black, bold True;') style.borders = borders style_left.borders = borders style_header.borders = borders style_right.borders = borders style_right_bold.borders = borders row = 0 col = 0 # sheet1.write_merge(2, 2, 0, 1, 'Review Cycle:', sub_style) # sheet1.write_merge(2, 2, 2, 5, appraisal_data.review_cycle, style) # sheet1.write_merge(3, 3, 0, 1, 'Financial Year:', sub_style) # sheet1.write_merge(3, 3, 2, 5, appraisal_data.financial_year.name, style) # sheet1.write_merge(4, 4, 0, 1,'Application Year:', sub_style) # sheet1.write_merge(4, 4, 2, 5, appraisal_data.application_year.name, style) sheet1.write_merge(7, 7, 0, 13, 'Exit Report', style_header) sheet1.write(9, 0, 'Sr. No.', style_header) sheet1.write_merge(9, 9, 1, 2, 'Reporting Code', style_header) sheet1.write_merge(9, 9, 3, 4, 'Reporting Name', style_header) sheet1.write_merge(9, 9, 5, 6, 'Employee Code', style_header) sheet1.write_merge(9, 9, 7, 8, 'Employee Name', style_header) sheet1.write_merge(9, 9, 9, 10, 'Department', style_header) sheet1.write_merge(9, 9, 11, 12, 'Designation', style_header) sheet1.write_merge(9, 9, 13, 14, 'Joining Date', style_header) sheet1.write_merge(9, 9, 15, 16, 'Resignation Date', style_header) sheet1.write_merge(9, 9, 17, 18, 'Last Working Date', style_header) sheet1.write_merge(9, 9, 19, 22, 'Reason of Resignation', style_header) sheet1.write_merge(9, 9, 23, 24, 'Notice Period', style_header) sheet1.write_merge(9, 9, 25, 26, 'Site', style_header) sheet1.write_merge(9, 9, 27, 28, 'State', style_header) count = 0 sr_no = [] row = 10 search_rec = request.env['hr.resignation'].search([ ('approved_relieving_date', '>=', exit_data.from_date), ('approved_relieving_date', '<=', exit_data.to_date) ]) for x in search_rec: if x.state: state = '' if x.state == 'draft': state = 'Pending' elif x.state == 'confirm': state = 'Manager Approval' elif x.state == 'approved': state = 'HR Approval' elif x.state == 'resignation_accepted': state = 'Resignation Accepted' elif x.state == 'cancel': state = 'Cancel' elif x.state == 'rejected': state = 'Rejected' else: state = 'Resignation Revoked' count += 1 sheet1.write(row, col, count, style_header) sheet1.write_merge(row, row, 1, 2, x.reporting_manager_id.emp_code, style_left) sheet1.write_merge(row, row, 3, 4, x.reporting_manager_id.name, style_left) sheet1.write_merge(row, row, 5, 6, x.employee_id.emp_code, style_right) sheet1.write_merge(row, row, 7, 8, x.employee_id.name, style_right) sheet1.write_merge(row, row, 9, 10, x.department_id.name, style_right) sheet1.write_merge(row, row, 11, 12, x.employee_id.job_id.name, style_right) sheet1.write_merge(row, row, 13, 14, x.joined_date, style_right) sheet1.write_merge(row, row, 15, 16, x.expected_relieving_date, style_right) sheet1.write_merge(row, row, 17, 18, x.approved_relieving_date, style_right) sheet1.write_merge(row, row, 19, 22, x.reason_resignation.name, style_right) sheet1.write_merge(row, row, 23, 24, x.notice_period, style_right) sheet1.write_merge(row, row, 25, 26, x.employee_id.site_master_id.name, style_right) sheet1.write_merge(row, row, 27, 28, state, style_right) row += 1 filename = 'Exit_Report.xls' #%(appraisal_data.review_cycle) response = request.make_response( None, headers=[('Content-Type', 'application/vnd.ms-excel'), ('Content-Disposition', content_disposition(filename))]) book.save(response.stream) return response
def export_purchase_order_in_excel(self): active_ids = self.env.context.get('active_ids') order_ids = self.env['purchase.order'].search([('id', 'in', active_ids) ]) workbook = xlwt.Workbook(encoding="UTF-8") filename = 'Pedido_compra' + '.xls' for order_id in order_ids: currency_id = order_id.currency_id worksheet = workbook.add_sheet(order_id.name) worksheet.col(0).width = 7000 worksheet.col(1).width = 4500 worksheet.col(2).width = 12000 worksheet.col(3).width = 4500 worksheet.col(4).width = 2000 worksheet.col(5).width = 3000 worksheet.col(6).width = 4500 worksheet.row(2).height = 400 bold = xlwt.easyxf("font: bold 1;") align_right = xlwt.easyxf("align: horiz right;") style = xlwt.easyxf('font: bold 1,height 240;') if order_id.state == 'draft': worksheet.write(2, 2, 'Request for Quotation', style) else: worksheet.write(2, 2, 'Purchase Order', style) worksheet.write(2, 3, order_id.name) worksheet.write(4, 0, 'Vendor:', bold) partner = order_id.partner_id row = 4 worksheet.write(row, 1, partner.name) if partner.street: row += 1 worksheet.write(row, 1, partner.street) if partner.street2: row += 1 worksheet.write(row, 1, partner.street2) if partner.city or partner.state_id or partner.zip: address = '' if partner.city: address = partner.city + ' ' if partner.state_id: address = address + partner.state_id.name + ' ' if partner.zip: address = address + partner.zip row += 1 worksheet.write(row, 1, address) if partner.country_id: row += 1 worksheet.write(row, 1, partner.country_id.name) worksheet.write(4, 4, 'Order Date:', bold) worksheet.write_merge( 4, 4, 5, 6, order_id.date_order.strftime(DEFAULT_SERVER_DATETIME_FORMAT)) worksheet.write(5, 4, 'Your Order Reference', bold) if order_id.partner_ref: worksheet.write(5, 5, order_id.partner_ref) row += 3 worksheet.write(row, 0, 'Product', bold) worksheet.write(row, 1, 'Code', bold) worksheet.write(row, 2, 'Supp. Code and Name', bold) worksheet.write(row, 3, 'EAN.', bold) worksheet.write(row, 4, 'Uds.', bold) #worksheet.write(row, 5, 'Rec. Qty', bold) worksheet.write(row, 5, 'Unit Price', bold) #worksheet.write(row, 7, 'Taxes', bold) worksheet.write(row, 6, 'Total', bold) for line in order_id.order_line: row += 1 worksheet.write(row, 0, line.product_id.name) worksheet.write(row, 1, line.product_id.default_code) worksheet.write(row, 2, line.name) #worksheet.write(row, 3, line.date_planned.strftime(DEFAULT_SERVER_DATETIME_FORMAT)) worksheet.write(row, 3, line.product_id.barcode or "") worksheet.write(row, 4, line.product_qty) #worksheet.write(row, 5, line.qty_received) worksheet.write( row, 5, formatLang(self.env, line.price_unit, currency_obj=currency_id), align_right) #worksheet.write(row, 7, ', '.join(map(lambda x: x.name, line.taxes_id))) worksheet.write( row, 6, formatLang(self.env, line.price_subtotal, currency_obj=currency_id), align_right) row += 3 worksheet.write(row, 5, 'Total Without Taxes', bold) worksheet.write( row, 6, formatLang(self.env, order_id.amount_untaxed, currency_obj=currency_id), align_right) row += 1 worksheet.write(row, 5, 'Taxes', bold) worksheet.write( row, 6, formatLang(self.env, order_id.amount_tax, currency_obj=currency_id), align_right) row += 1 worksheet.write(row, 5, 'Total', bold) worksheet.write( row, 6, formatLang(self.env, order_id.amount_total, currency_obj=currency_id), align_right) row += 2 worksheet.write(row, 0, order_id.notes) fp = BytesIO() workbook.save(fp) po_excel_report_id = self.env['purchase.order.excel.report'].create({ 'excel_file': base64.encodestring(fp.getvalue()), 'file_name': filename }) fp.close() return { 'type': 'ir.actions.act_url', 'url': '/web/binary/download_po_excel_report/%s' % (po_excel_report_id.id), 'target': 'new', }
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
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
def get_width(num_characters, font_height=12): return int((1 + num_characters) * 256 * font_height / 12) # font_height = request.env['ir.config_parameter'].sudo().get_param('tonkho.' + 'font_height') # if not font_height: # HEIGHT = 12 # else: # HEIGHT = font_height # normal_style = xlwt.easyxf("font: name Times New Roman, height 240") HEIGHT = 12 # Border: header_bold_style = xlwt.easyxf( generate_easyxf(height=HEIGHT, bold=True, vert='center', horiz='center', borders='left thin, right thin, top thin, bottom thin', pattern='pattern solid, fore_colour gray25')) header_bold_style_no_gray = xlwt.easyxf( generate_easyxf(bold=True, vert='center', horiz='center', borders='left thin, right thin, top thin, bottom thin')) center_vert_border_style = xlwt.easyxf( generate_easyxf(height=HEIGHT, borders='left thin, right thin, top thin, bottom thin', vert='center')) wrap_center_vert_border_style = xlwt.easyxf( generate_easyxf(height=HEIGHT, vert='center',
def add_title( worksheet, FIELDNAME_FIELDATTR, model_fields, ROW_TITLE=0, offset_column=0, is_set_width=True, no_gray=False, # is_auto_width = True, # for_len_adj = False dl_model_para=None, font_height=12, max_header_char_number=0): header_bold_style = xlwt.easyxf( generate_easyxf(height=font_height, bold=True, vert='center', horiz='center', borders='left thin, right thin, top thin, bottom thin', pattern='pattern solid, fore_colour gray25')) header_bold_style_no_gray = xlwt.easyxf( generate_easyxf( height=font_height, bold=True, vert='center', horiz='center', borders='left thin, right thin, top thin, bottom thin')) writen_column_number = 0 column_index = offset_column for f_name, FIELDATTR in FIELDNAME_FIELDATTR.items(): is_not_model_field = FIELDATTR.get('is_not_model_field') skip_field = FIELDATTR.get('skip_field') if callable(skip_field): skip_field = skip_field(dl_model_para) if skip_field: continue split = FIELDATTR.get('split') write_to_excel = FIELDATTR.get('write_to_excel', True) if is_not_model_field: header_string = FIELDATTR.get('string') or f_name else: header_string = FIELDATTR.get('string') if not header_string: field = model_fields[f_name] header_string = field.string else: if callable(header_string): header_string = header_string(dl_model_para) if write_to_excel: if no_gray: title_style = header_bold_style_no_gray else: title_style = header_bold_style worksheet.write(ROW_TITLE, column_index, header_string, title_style) writen_column_number += 1 if is_set_width: char_number = FIELDATTR.get('width') if not char_number: char_number = FIELDATTR.get('max_len_field_val', 0) if max_header_char_number and char_number > max_header_char_number: char_number = max_header_char_number # width = get_width(char_number) #or width # header_string_width = get_width(len(header_string) + 2,font_height) header_char_number = len(header_string) if header_char_number > char_number: char_number = header_char_number worksheet.col(column_index).width = get_width( char_number + 4, font_height) column_index += 1 else: pass if split: writen_column_number_child = add_title(worksheet, split, model_fields, ROW_TITLE=ROW_TITLE, offset_column=column_index, no_gray=no_gray) print("writen_column_number_child", writen_column_number_child) column_index += writen_column_number_child writen_column_number += writen_column_number_child return writen_column_number
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, 'No.Empleado', bold) worksheet.write(4, 1, 'Empleado', bold) col = 4 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.no_empleado) worksheet.write(row, 1, employee.name) row += 1 worksheet.write(row, 2, 'Fecha de la nomina', bold) worksheet.write(row, 3, 'Tipo', bold) row += 1 total_by_rule = defaultdict(lambda: 0.0) for payslip, lines in payslips.items(): #for line in lines: worksheet.write(row, 2, payslip.date_from) worksheet.write(row, 3, 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, 3, '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
def write_table(self, worksheet, datas, row, group_iter, active_measures, groupby, parent=None): header_plain = xlwt.easyxf( "pattern: pattern solid, fore_colour light_green;") header_bold = xlwt.easyxf( "font: bold on; pattern: pattern solid, fore_colour gray25;") bold = xlwt.easyxf("font: bold on;") for i, data in enumerate(datas[group_iter]): grouped = True if group_iter == 0: grouped = True else: for j in range(group_iter - 1): if data[groupby[j]] != parent[groupby[j]]: grouped = False break if grouped: tab = '' # for x in range(group_iter): # tab+=' ' # if group_iter== 0: # # worksheet.write(row, 0, u'STT', header_plain) # # worksheet.write(row, 1, u'%s'%first_groupby,header_plain) # _logger.info("AAAA") # else: # worksheet.write(row, 0, u'%d'%row) # if type(data[groupby[group_iter-1]]) == tuple: # worksheet.write(row, 1, u'%s' % (tab + data[groupby[group_iter - 1]][1])) # elif not data[groupby[group_iter-1]]: # worksheet.write(row, 1, tab) # else: # worksheet.write(row,1,u'%s'%(tab+data[groupby[group_iter-1]])) worksheet.write(row, 0, u'%d' % row) if type(data[groupby[group_iter - 1]]) == tuple: worksheet.write( row, group_iter, u'%s' % (tab + data[groupby[group_iter - 1]][1])) elif not data[groupby[group_iter - 1]]: worksheet.write(row, group_iter, tab + u'Không xác định') else: worksheet.write( row, group_iter, u'%s' % (tab + data[groupby[group_iter - 1]])) if len(datas) > group_iter + 1: for j, header_row in enumerate(active_measures): if type(data[header_row]) is list: # worksheet.write(row, j + 2,u'%s'%(data[header_row][0])) worksheet.write(row, j + len(groupby) + 1, '') elif type(data[header_row]) is not unicode: worksheet.write(row, j + len(groupby) + 1, u'%s' % (data[header_row]), bold) row = row + 1 row = self.write_table(worksheet, datas, row, group_iter + 1, active_measures, groupby, data) else: for j, header_row in enumerate(active_measures): if type(data[header_row]) is list: worksheet.write( row, j + len(groupby) + 1, u'%s' % (',').join(data[header_row])) elif type(data[header_row]) is int or type( data[header_row]) is float: worksheet.write(row, j + len(groupby) + 1, u'%d' % (data[header_row])) elif type(data[header_row] ) is bool and data[header_row] is False: worksheet.write(row, j + len(groupby) + 1, '') else: worksheet.write(row, j + len(groupby) + 1, u'%s' % (data[header_row])) row = row + 1 return row
def method_direct_trigger(self): # reload(sys) # sys.setdefaultencoding('utf-8') if not self._context: self._context = {'lang': u'vi_VN'} if self.report: _logger.info("DO NOTHING %s" % self.report.context.replace( "u\\'", '"').replace("'", '"').replace('u"', '"')) context = json.loads( self.report.context.replace("u\\'", '"').replace("'", '"').replace( 'u"', '"')) active_measures = None if 'measures' in context: active_measures = context['measures'] elif 'pivot_measures' in context: active_measures = context['pivot_measures'] if active_measures != None: fields_model = self.env[self.report.model_id].fields_get() groupby = None if 'group_by' in context: groupby = context['group_by'] elif 'pivot_row_groupby' in context: groupby = context['pivot_row_groupby'] col_groupby = None if 'col_group_by' in context: col_groupby = context['col_group_by'] elif 'pivot_column_groupby' in context: col_groupby = context['pivot_column_groupby'] fields = list(active_measures) if groupby is not None: fields.extend( x.split(':')[0] for x in groupby if x not in fields) if col_groupby is not None: fields.extend( x.split(':')[0] for x in col_groupby if x not in fields) datas = [] tmp_groupbys = [] tmp_domain = self.report.domain if 'time.str' in tmp_domain: times = re.findall(r'time.strftime\("[%,\-,\w]*"\)', tmp_domain) for time in times: tmp = time.replace('time.strftime(','').replace('%Y','%d'%datetime.now().year)\ .replace('%m','%d'%datetime.now().month).replace(")",'') tmp_domain = tmp_domain.replace(time, tmp) domains = ast.literal_eval(tmp_domain) # for domain in domains: # if type(domain) is str: # datas.append(self.env[self.report.model_id].read_group( domains, fields, tmp_groupbys, lazy=False)) for group in groupby: tmp_groupbys.append(group) datas.append(self.env[self.report.model_id].read_group( domains, fields, tmp_groupbys, lazy=False)) workbook = xlwt.Workbook() worksheet = workbook.add_sheet(self.report.name) header_plain = xlwt.easyxf( "pattern: pattern solid, fore_colour light_green;") footer_plain = xlwt.easyxf( "pattern: pattern solid, fore_colour gray25;") worksheet.write(0, 0, u'STT', header_plain) for i, group in enumerate(groupby): worksheet.write(0, i + 1, u'%s' % fields_model[group]['string'], header_plain) for i, header_row in enumerate(active_measures): worksheet.write( 0, i + len(groupby) + 1, u'%s' % (fields_model[header_row]['string']), header_plain) # row_counter = 1 # worksheet.write(row_counter,0,'Tổng') # for i in # for i,header_row in enumerate(active_measures): # worksheet.write(1, i+1, 'Tổng') row = self.write_table(worksheet, datas, 1, 1, active_measures, groupby, None) # workbook.save("trial.xls") #Write footer total if datas and len(datas) > 0 and len(datas[0]) > 0: worksheet.write(row, 1, u'Tổng', footer_plain) for i, group in enumerate(groupby): if i > 0: worksheet.write(row, i + 1, '', footer_plain) for j, header_row in enumerate(active_measures): if type(datas[0][0][header_row]) is int: worksheet.write(row, j + len(groupby) + 1, '%d' % datas[0][0][header_row], footer_plain) else: worksheet.write(row, j + len(groupby) + 1, '', footer_plain) # if type(datas[0][0][header_row]) is list: # # else: # worksheet.write(row, j + len(groupby)+1,u'%s'%(datas[0][0][header_row])) byteIo = BytesIO() workbook.save(byteIo) record = self.env['ir.attachment'].create({ 'name': self.name, 'type': 'binary', 'datas': base64.b64encode(byteIo.getvalue()), 'datas_fname': '%s.xls' % self.name, 'res_model': 'account.invoice', 'mimetype': 'application/vnd.ms-excel' }) self.send_mail(record)
def export_products(self): # if xlsxwriter==None: # raise Warning(_("Unable to load Python module \"{modname}\" \n Install it by command : sudo pip install xlsxwriter").format(modname='xlsxwriter')) #fp = StringIO() #workbook = xlsxwriter.Workbook(fp, {'in_memory': True}) filename = 'products_%s.xls' % ( datetime.today().strftime("%Y_%m_%d_%H_%M_%S")) workbook = xlwt.Workbook() bold = xlwt.easyxf("font: bold on;") # header_format_without_color = workbook.add_format({ # 'border': 1, # 'bold': True, # 'text_wrap': True, # 'valign': 'vcenter', # 'indent': 1, # }) quant_obj = self.env['stock.quant'] products = self.env['product.product'].sudo().search( ['|', ('active', '=', True), ('active', '=', False)]) company_id = self.env.user.company_id.id #worksheet = workbook.add_worksheet('Products') worksheet = workbook.add_sheet('Products') headers = [ 'id', 'create_date', 'Archive', 'invoice_policy', 'purchase_method', 'categ_id/name', 'pos_categ_id/name', 'available_in_pos', 'name', 'barcode', 'default_code', 'unit_of_measurement', 'uom_po_id', 'l10n_mx_edi_code_sat_id', 'supplier_taxes_id', 'taxes_id', '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 = '' if product.supplier_taxes_id: # xml_ids = [xid for _, xid in self.__ensure_xml_id_custom(product.supplier_taxes_id)] # supplier_taxes_ids = ','.join(xml_ids) or False supplier_taxes_ids = ','.join( [tax.name for tax in product.supplier_taxes_id]) else: supplier_taxes_ids = '' if product.taxes_id: # xml_ids = [xid for _, xid in self.__ensure_xml_id_custom(product.taxes_id)] # customer_taxes_ids = ','.join(xml_ids) or False customer_taxes_ids = ','.join( [tax.name for tax in product.taxes_id]) else: customer_taxes_ids = '' if product.l10n_mx_edi_code_sat_id: xml_ids = [ xid for _, xid in self.__ensure_xml_id_custom( product.l10n_mx_edi_code_sat_id) ] l10n_mx_edi_code_sat_id = ','.join(xml_ids) or False else: l10n_mx_edi_code_sat_id = '' i = 0 worksheet.write(row_index, i, product_xml_ids.get(product.id)) i += 1 worksheet.write(row_index, i, product.create_date) i += 1 if product.active: worksheet.write(row_index, i, 0) else: worksheet.write(row_index, i, 1) i += 1 worksheet.write(row_index, i, product.invoice_policy) i += 1 worksheet.write(row_index, i, product.purchase_method) i += 1 worksheet.write(row_index, i, product.categ_id.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.uom_po_id.name) i += 1 worksheet.write(row_index, i, product.l10n_mx_edi_code_sat_id.code) i += 1 worksheet.write(row_index, i, supplier_taxes_ids) i += 1 worksheet.write(row_index, i, customer_taxes_ids) 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) 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', }
def action_warehouse_stock_report(self): invoice_obj1 = self.env['check.stock'].search([]) if invoice_obj1: invoice_obj1[-1].write({ 'location_id': self.location_id.id, }) if not invoice_obj1: invoice_obj1.create({ 'location_id': self.location_id.id, }) invoice_obj = self.env['check.stock'].search([])[-1] 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 lines = [] line_ids = [] ctot_val = 0.0 mtot_val = 0.0 cost_tot_val = 0.0 mrp_tot_val = 0.0 for result in invoice_obj: report_head = 'Warehouse Stock Report' # if result.inventory_date: # report_head += ' (' + result.inventory_date + ')' worksheet.write_merge( 0, 0, 0, 7, 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, _('Description'), column_heading_style) worksheet.write(1, 3, _('Quantity'), column_heading_style) worksheet.write(1, 4, _('Cost'), column_heading_style) worksheet.write(1, 5, _('Cost Valuation'), column_heading_style) worksheet.write(1, 6, _('MRP'), column_heading_style) worksheet.write(1, 7, _('MRP Valuation'), column_heading_style) worksheet.col(0).width = 5000 worksheet.col(1).width = 10000 worksheet.col(2).width = 5000 worksheet.col(5).width = 5000 worksheet.col(7).width = 5000 worksheet.row(0).height = 500 worksheet.col(4).width = 5000 vals = { 'product_name': '', 'description': '', 'cost': 0.0, 'quantity': 0.0, 'price': 0.0, 'default_code': 0.0, 'ctot_amount': 0.0, 'mtot_amount': 0.0, } # ctx.update({'to_date': wizard.inventory_date}) # raise UserError(str(self.start_date)) if self.wstart_date: domain = [('in_date', '<=', result.end_date), ('in_date', '>=', self.wstart_date), ('location_id', '=', result.location_id.id)] else: domain = [('in_date', '<=', result.end_date), ('location_id', '=', result.location_id.id)] # product_objs = self.env['product.product'].with_context(ctx).search([], order='name') product_objs = self.env['stock.quant'].search(domain) for product in product_objs: vals = { 'product_name': product.product_id.name, 'description': product.product_id.description, 'default_code': product.product_id.default_code, 'quantity': product.quantity, 'cost': product.product_id.standard_price, 'price': product.product_id.list_price, } lines.append(vals) # 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 line1 = sorted(lines, key=operator.itemgetter('default_code')) for key, value in itertools.groupby( line1, key=operator.itemgetter('default_code')): quantity = 0 for item in list(value): quantity += int(item['quantity']) ctot_amount = int(item['quantity']) * int(item['cost']) ctot_val += int(item['cost']) cost_tot_val += ctot_amount mtot_amount = int(item['quantity']) * int(item['price']) mtot_val += int(item['price']) mrp_tot_val += mtot_amount line_ids.append({ 'ctot_amount': ctot_amount, 'mtot_amount': mtot_amount, 'default_code': item['default_code'], 'description': item['description'], 'price': item['price'], 'cost': item['cost'], 'quantity': quantity, 'product_name': item['product_name'] }) for result in line_ids: row += 1 worksheet.write(row, 0, result['default_code']) worksheet.write(row, 1, result['product_name']) worksheet.write(row, 2, result['description']) worksheet.write(row, 3, result['quantity']) worksheet.write(row, 4, result['cost']) worksheet.write(row, 5, result['ctot_amount']) worksheet.write(row, 6, result['price']) worksheet.write(row, 7, result['mtot_amount']) row += 5 worksheet.write(row, 1, _('Total Valuation'), column_heading_style) worksheet.write(row, 4, ctot_val) worksheet.write(row, 5, cost_tot_val) worksheet.write(row, 6, mtot_val) worksheet.write(row, 7, mrp_tot_val) for wizard in self: # report_head = 'Warehouse Stock Report' fp = io.BytesIO() workbook.save(fp) excel_file = base64.encodestring(fp.getvalue()) wizard.stock_report_file = excel_file wizard.file_name = 'Warehouse Stock Report.xls' wizard.inventory_printed = True fp.close() return { 'view_mode': 'form', 'res_id': wizard.id, 'res_model': 'warehouse.stock.report', 'view_type': 'form', 'type': 'ir.actions.act_window', 'context': self.env.context, 'target': 'new', }
def export_xls(self, filename, title, company_id, date_from, date_to, account_id, **kw): company = request.env['res.company'].search([('id', '=', company_id)]) account = request.env['account.account'].search([('id', '=', account_id)]) account_move_lines = request.env['account.move.line'].sudo().search( [('account_id.id', '=', account_id), ('date', '>=', date_from), ('date', '<=', date_to)], order='partner_id asc') date_processed = date.today().strftime('%m-%d-%Y') report_month = datetime.strptime(date_from, '%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") 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 = 750 * 12 worksheet.col(3).width = 750 * 12 worksheet.col(4).width = 300 * 12 worksheet.col(5).width = 800 * 12 worksheet.col(6).width = 350 * 12 worksheet.col(7).width = 300 * 12 worksheet.col(8).width = 350 * 12 # TEMPLATE HEADERS worksheet.write(0, 0, 'BIR FORM 1601E - SCHEDULE I', style_header_bold) # BIR FORM INFO worksheet.write(1, 0, title, style_header_bold) # TITLE worksheet.write(2, 0, 'FOR THE MONTH OF %s' % (report_month.strftime('%B, %Y')), style_header_bold) # Report Date # worksheet.write(3, 0, account_id, style_header_bold) # Report Date # worksheet.write(4, 0, account_move_lines, style_header_bold) # Report Date worksheet.write(5, 0, 'TIN: %s' % (company.vat), style_header_bold) # Company Name worksheet.write(6, 0, "WITHHOLDING AGENT'S NAME: %s" % (company.name), style_header_bold) # Company TIN # TABLE HEADER worksheet.write(9, 0, 'SEQ NO', style_table_header_bold) # HEADER worksheet.write(9, 1, 'TAXPAYER IDENTIIFICATION NUMBER', style_table_header_bold) # HEADER worksheet.write(9, 2, 'CORPORATION (Registered Name)', style_table_header_bold) # HEADER worksheet.write(9, 3, 'INDIVIDUAL (Last Name, First Name, Middle Name)', style_table_header_bold) # HEADER worksheet.write(9, 4, 'ATC CODE', style_table_header_bold) # HEADER worksheet.write(9, 5, 'NATURE OF PAYMENT', style_table_header_bold) # HEADER worksheet.write(9, 6, 'AMOUNT OF INCOME PAYMENT', style_table_header_bold) # HEADER worksheet.write(9, 7, 'TAX RATE', style_table_header_bold) # HEADER worksheet.write(9, 8, 'AMOUNT OF TAX WITHHELD', style_table_header_bold) # HEADER # TABLE ROW LINES row_count = 10 seq_count = 1 registered_name = '' customer_name = '' amount_income = 0 amount_tax = 0 total_amount_tax = 0 payment_nature = '' for account in account_move_lines: if account.partner_id.company_type == 'company': registered_name = account.partner_id.name customer_name = '' else: registered_name = account.partner_id.parent_id.name customer_name = account.partner_id.name if not account.partner_id.parent_id: registered_name = account.partner_id.name customer_name = '' 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 if amount_tax <= 0: if account.debit > 0: amount_tax = account.debit else: amount_tax = account.credit # GET NATURE OF PAYMENT if account.invoice_id: payment_nature = account.invoice_id.x_description worksheet.write(row_count, 0, seq_count, style_table_row) worksheet.write(row_count, 1, account.partner_id.vat or '', style_table_row) worksheet.write(row_count, 2, registered_name, style_table_row) worksheet.write(row_count, 3, customer_name, style_table_row) worksheet.write(row_count, 4, account.tax_line_id.ewt_structure_id.name or '', style_table_row) worksheet.write(row_count, 5, payment_nature, style_table_row) worksheet.write(row_count, 6, amount_income, style_table_row_amount) worksheet.write(row_count, 7, account.tax_line_id.amount, style_table_row_amount) worksheet.write(row_count, 8, amount_tax, style_table_row_amount) row_count += 1 seq_count += 1 total_amount_tax += amount_tax table_total_start = row_count end_report = table_total_start + 2 # TABLE TOTALS worksheet.write_merge(table_total_start, table_total_start, 0, 7, 'GRAND TOTAL', style_table_total) worksheet.write(table_total_start, 8, total_amount_tax, style_table_total_value) worksheet.write(end_report, 0, 'END OF REPORT', style_end_report) 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, **kw): company = request.env['res.company'].search([('id', '=', company_id)]) # account_ewt = request.env['account.account'].search([('name','=','Withholding Tax Expanded')], limit=1) account_type = request.env['account.account.type'].search([ ('name', '=', 'Fixed Assets') ]) account_ids = request.env['account.account'].search([ ('user_type_id', '=', account_type.id) ]) account_asset = request.env['account.asset.asset'].search([ ('state', '!=', 'draft') ]) date_processed = date.today().strftime('%m-%d-%Y') 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(6).width = 350 * 12 worksheet.col(7).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 worksheet.col(21).width = 350 * 12 worksheet.col(22).width = 350 * 12 worksheet.col(23).width = 350 * 12 worksheet.col(24).width = 350 * 12 worksheet.col(25).width = 350 * 12 worksheet.col(26).width = 350 * 12 worksheet.col(27).width = 350 * 12 worksheet.col(28).width = 350 * 12 worksheet.col(29).width = 350 * 12 worksheet.col(30).width = 350 * 12 worksheet.col(31).width = 350 * 12 worksheet.col(32).width = 350 * 12 worksheet.col(33).width = 350 * 12 worksheet.col(34).width = 350 * 12 worksheet.col(35).width = 350 * 12 worksheet.col(36).width = 350 * 12 worksheet.col(37).width = 350 * 12 worksheet.col(38).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 or '', company.street2 or '', company.city or '', company.state_id.name or '', company.zip or '', company.country_id.name or ''), style_header_bold) # Company Address worksheet.write(2, 0, company.vat, style_header_bold) # Company TIN worksheet.write(4, 0, title, style_header_bold) # Report Title worksheet.write(5, 0, 'As of %s' % (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, 'JOURNAL TYPE', style_table_header_bold) # HEADER worksheet.write_merge(7, 8, 2, 2, 'REFERENCE NO.', style_table_header_bold) # HEADER worksheet.write_merge(7, 8, 3, 3, 'SUPPLIER NAME', style_table_header_bold) # HEADER worksheet.write_merge(7, 8, 4, 4, 'REGISTERED ADDRESS', style_table_header_bold) # HEADER worksheet.write_merge(7, 8, 5, 5, 'TIN', style_table_header_bold) # HEADER worksheet.write_merge(7, 7, 6, 7, 'SOURCE DOCUMENT', style_table_header_bold) # HEADER worksheet.write(8, 6, 'TYPE', style_table_header_bold) # HEADER worksheet.write(8, 7, 'NUMBER', style_table_header_bold) # HEADER worksheet.write_merge(7, 8, 8, 8, 'GROSS AMOUNT', style_table_header_bold) # HEADER worksheet.write_merge(7, 8, 9, 9, 'NON-VAT / EXEMPT', style_table_header_bold) # HEADER worksheet.write_merge(7, 8, 10, 10, 'NET OF VAT', style_table_header_bold) # HEADER worksheet.write_merge(7, 8, 11, 11, 'INPUT TAX (12%)', style_table_header_bold) # HEADER worksheet.write_merge(7, 8, 12, 12, 'INPUT TAX ALLOWED', style_table_header_bold) # HEADER worksheet.write_merge(7, 8, 13, 13, 'PARTICULARS', style_table_header_bold) # HEADER worksheet.write_merge(7, 8, 14, 14, 'ASSET AMOUNT', style_table_header_bold) # HEADER worksheet.write_merge(7, 7, 15, 17, 'EXPANDED WITHOLDING TAX', style_table_header_bold) # HEADER worksheet.write(8, 15, 'ATC', style_table_header_bold) # HEADER worksheet.write(8, 16, 'EWT RATE', style_table_header_bold) # HEADER worksheet.write(8, 17, 'AMOUNT', style_table_header_bold) # HEADER worksheet.write_merge(7, 8, 18, 18, 'EWT ABSORBED BY COMPANY', style_table_header_bold) # HEADER worksheet.write_merge(7, 8, 19, 19, 'ESTIMATED LIFE', style_table_header_bold) # HEADER worksheet.write_merge(7, 8, 20, 20, 'MONTHLY DEPRECIATION', style_table_header_bold) # HEADER worksheet.write_merge(7, 8, 21, 21, 'JAN', style_table_header_bold) # HEADER worksheet.write_merge(7, 8, 22, 22, 'FEB', style_table_header_bold) # HEADER worksheet.write_merge(7, 8, 23, 23, 'MAR', style_table_header_bold) # HEADER worksheet.write_merge(7, 8, 24, 24, 'APR', style_table_header_bold) # HEADER worksheet.write_merge(7, 8, 25, 25, 'MAY', style_table_header_bold) # HEADER worksheet.write_merge(7, 8, 26, 26, 'JUN', style_table_header_bold) # HEADER worksheet.write_merge(7, 8, 27, 27, 'JUL', style_table_header_bold) # HEADER worksheet.write_merge(7, 8, 28, 28, 'AUG', style_table_header_bold) # HEADER worksheet.write_merge(7, 8, 29, 29, 'SEP', style_table_header_bold) # HEADER worksheet.write_merge(7, 8, 30, 30, 'OCT', style_table_header_bold) # HEADER worksheet.write_merge(7, 8, 31, 31, 'NOV', style_table_header_bold) # HEADER worksheet.write_merge(7, 8, 32, 32, 'DEC', style_table_header_bold) # HEADER worksheet.write_merge(7, 8, 33, 33, 'TOTAL', style_table_header_bold) # HEADER worksheet.write_merge(7, 7, 34, 36, 'ACCUMULATED DEPRECIATION', style_table_header_bold) # HEADER worksheet.write(8, 34, 'BEGINNING', style_table_header_bold) # HEADER worksheet.write(8, 35, 'CURRENT', style_table_header_bold) # HEADER worksheet.write(8, 36, 'TOTAL', style_table_header_bold) # HEADER worksheet.write_merge(7, 8, 37, 37, 'BOOK VALUE', style_table_header_bold) # HEADER worksheet.write_merge(7, 8, 38, 38, 'REMARKS', style_table_header_bold) # HEADER # TABLE ROW LINES # table_row_start = 9 row_count = 9 transaction_count = 0 # total_gross_amount = 0 # total_non_vat_amount = 0 # total_net_vat_amount = 0 # total_input_tax_amount = 0 # total_ap_amount = 0 # total_ewt_tax_amount = 0 for account in account_asset: source_type = '' source_no = '' if account.invoice_id: source_type = 'Vendor Bill' source_no = account.invoice_id.name ewt_tax_base = 0 ewt_atc = '' ewt_rate = 0 ewt_tax_amount = 0 input_tax_amount = 0 estimated_life = 0 if account.category_id.method_number and account.category_id.method_period: account.method_number * account.method_period monthly_value = 0 beginning_value = 0 current_value = 0 total_value = 0 jan_value = 0 feb_value = 0 mar_value = 0 apr_value = 0 may_value = 0 jun_value = 0 jul_value = 0 aug_value = 0 sep_value = 0 oct_value = 0 nov_value = 0 dec_value = 0 for line in account.depreciation_line_ids: month_today = date.today().strftime('%m') if line.sequence == 1: monthly_value = line.amount beginning_value = line.depreciated_value if month_today == '01': jan_value += line.amount if month_today == '02': feb_value += line.amount if month_today == '03': mar_value += line.amount if month_today == '04': apr_value += line.amount if month_today == '05': may_value += line.amount if month_today == '06': jun_value += line.amount if month_today == '07': jul_value += line.amount if month_today == '08': aug_value += line.amount if month_today == '09': sep_value += line.amount if month_today == '10': oct_value += line.amount if month_today == '11': nov += line.amount if month_today == '12': dec_value += line.amount worksheet.write(row_count, 0, account.date, style_table_row) worksheet.write(row_count, 1, account.category_id.journal_id.name, style_table_row) worksheet.write(row_count, 2, '', style_table_row) worksheet.write(row_count, 3, account.partner_id.name or '', style_table_row) worksheet.write( row_count, 4, '%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, 5, account.partner_id.vat or '', style_table_row) worksheet.write(row_count, 6, source_type, style_table_row) worksheet.write(row_count, 7, source_no, style_table_row) worksheet.write(row_count, 8, account.value, style_table_row_amount) worksheet.write(row_count, 9, '', style_table_row_amount) worksheet.write(row_count, 10, '', style_table_row_amount) worksheet.write(row_count, 11, input_tax_amount, style_table_row_amount) worksheet.write(row_count, 12, '', style_table_row_amount) worksheet.write(row_count, 13, '', style_table_row) worksheet.write(row_count, 14, account.value, style_table_row_amount) worksheet.write(row_count, 15, ewt_atc or '', style_table_row) worksheet.write(row_count, 16, ewt_rate, style_table_row_amount) worksheet.write(row_count, 17, ewt_tax_amount, style_table_row_amount) worksheet.write(row_count, 18, '', style_table_row_amount) worksheet.write(row_count, 19, estimated_life, style_table_row_amount) worksheet.write(row_count, 20, monthly_value, style_table_row_amount) worksheet.write(row_count, 21, jan_value, style_table_row_amount) worksheet.write(row_count, 22, feb_value, style_table_row_amount) worksheet.write(row_count, 23, mar_value, style_table_row_amount) worksheet.write(row_count, 24, apr_value, style_table_row_amount) worksheet.write(row_count, 25, mar_value, style_table_row_amount) worksheet.write(row_count, 26, jun_value, style_table_row_amount) worksheet.write(row_count, 27, jul_value, style_table_row_amount) worksheet.write(row_count, 28, aug_value, style_table_row_amount) worksheet.write(row_count, 29, sep_value, style_table_row_amount) worksheet.write(row_count, 30, oct_value, style_table_row_amount) worksheet.write(row_count, 31, nov_value, style_table_row_amount) worksheet.write(row_count, 32, dec_value, style_table_row_amount) worksheet.write(row_count, 33, account.value_residual, style_table_row_amount) worksheet.write(row_count, 34, beginning_value, style_table_row_amount) worksheet.write(row_count, 35, current_value, style_table_row_amount) worksheet.write(row_count, 36, total_value, style_table_row_amount) worksheet.write(row_count, 37, total_value, style_table_row_amount) worksheet.write(row_count, 38, '', style_table_row_amount) row_count += 1 transaction_count += 1 # total_gross_amount += account.amount_total # total_non_vat_amount += account.vat_exempt_sales # total_net_vat_amount += account.vat_sales # total_input_tax_amount += input_tax_amount # total_ap_amount += account.amount_total # total_ewt_tax_amount += ewt_tax_amount table_total_start = row_count # for account_id in account_ids: # accounts_asset = request.env['account.move.line'].search([('account_id', '=', account_id.id)]) # for account in accounts_asset: # ewt_tax_base = 0 # ewt_atc = '' # ewt_rate = 0 # ewt_tax_amount = 0 # input_tax_amount = 0 # has_ewt = False # for tax in account.tax_line_ids: # if tax.account_id.id == account_ewt.id: # has_ewt = True # ewt_tax_base = tax.base # ewt_atc = tax.tax_id.ewt_structure_id.name # ewt_rate = tax.tax_id.amount # ewt_tax_amount = tax.amount_total # else: # if tax.amount == 12.00: # input_tax_amount = tax.amount_total # worksheet.write(row_count, 0, account.date, style_table_row) # worksheet.write(row_count, 1, account.journal_id.name, style_table_row) # worksheet.write(row_count, 2, account.name, style_table_row) # worksheet.write(row_count, 3, account.partner_id.name, style_table_row) # worksheet.write(row_count, 4, '%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, 5, account.partner_id.vat or '', style_table_row) # worksheet.write(row_count, 6, '', style_table_row) # worksheet.write(row_count, 7, account.origin or '', style_table_row) # worksheet.write(row_count, 8, account.amount_total, style_table_row_amount) # worksheet.write(row_count, 9, account.vat_exempt_sales, style_table_row_amount) # worksheet.write(row_count, 10, account.vat_sales, style_table_row_amount) # worksheet.write(row_count, 11, input_tax_amount, style_table_row_amount) # worksheet.write(row_count, 12, '', style_table_row_amount) # worksheet.write(row_count, 13, account.x_description or '', style_table_row) # worksheet.write(row_count, 14, account.amount_total, style_table_row_amount) # worksheet.write(row_count, 15, ewt_atc or '', style_table_row) # worksheet.write(row_count, 16, ewt_rate, style_table_row_amount) # worksheet.write(row_count, 17, ewt_tax_amount, style_table_row_amount) # worksheet.write(row_count, 18, '', style_table_row_amount) # row_count +=1 # transaction_count +=1 # total_gross_amount += account.amount_total # total_non_vat_amount += account.vat_exempt_sales # total_net_vat_amount += account.vat_sales # total_input_tax_amount += input_tax_amount # total_ap_amount += account.amount_total # total_ewt_tax_amount += ewt_tax_amount # 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(table_total_start, 19, '', style_table_total_value) worksheet.write(table_total_start, 20, '', style_table_total_value) worksheet.write(table_total_start, 21, '', style_table_total_value) worksheet.write(table_total_start, 22, '', style_table_total_value) worksheet.write(table_total_start, 23, '', style_table_total_value) worksheet.write(table_total_start, 24, '', style_table_total_value) worksheet.write(table_total_start, 25, '', style_table_total_value) worksheet.write(table_total_start, 26, '', style_table_total_value) worksheet.write(table_total_start, 27, '', style_table_total_value) worksheet.write(table_total_start, 28, '', style_table_total_value) worksheet.write(table_total_start, 29, '', style_table_total_value) worksheet.write(table_total_start, 30, '', style_table_total_value) worksheet.write(table_total_start, 31, '', style_table_total_value) worksheet.write(table_total_start, 32, '', style_table_total_value) worksheet.write(table_total_start, 33, '', style_table_total_value) worksheet.write(table_total_start, 34, '', style_table_total_value) worksheet.write(table_total_start, 35, '', style_table_total_value) worksheet.write(table_total_start, 36, '', style_table_total_value) worksheet.write(table_total_start, 37, '', style_table_total_value) worksheet.write(table_total_start, 38, '', style_table_total_value) # worksheet.write(0, 18, 'No. of Transaction: %s'%(transaction_count), style_header_right) # worksheet.write(1, 18, 'Date Processed: %s'%(date_processed), style_header_right) # worksheet.write(2, 18, 'Processed By: %s'%(user_id), 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 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'] * ' ' + ustr(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 print_report(self): records = self.env['account.asset.asset'].search([ ('state', '!=', 'draft'), ('date', '>=', self.date_from), ('date', '<=', self.date_to) ]) prev_records = self.env['account.asset.asset'].search([ ('state', '!=', 'draft'), ('date', '<', self.date_from) ]) tot_records = records + prev_records last_date_from = fields.Date.to_string( fields.Date.from_string(self.date_from) + relativedelta(years=-1)) last_date_to = fields.Date.to_string( fields.Date.from_string(self.date_to) + relativedelta(years=-1)) if not tot_records: raise ValidationError(_('There are no record Found!')) # accounts = records.mapped('category_id').mapped('account_asset_id') assets = records + prev_records fieldss = [ '', 'Gross Value', 'Gross Value if new asset this year', 'Sold or Disposed this year', 'Plus/minus transfers(always 0.00)', 'Depreciation since start still last year', 'Residual end of this year', 'Residual still last year', 'Depreciation this year', 'Depreciation this year(always 0.00)' ] workbook = xlwt.Workbook() worksheet = workbook.add_sheet('Sheet 1') raw = 0 base_style = xlwt.easyxf('align: wrap yes') for field in fieldss: worksheet.write(0, raw, field, base_style) raw += 1 col = 1 for asset in assets: raw = 0 for field in fieldss: if field == '': worksheet.write(col, raw, asset.name, base_style) elif field == 'Gross Value': value = sum( asset.filtered(lambda rec: rec.state == 'open' and rec. date < self.date_from).mapped('value')) worksheet.write(col, raw, value, base_style) elif field == 'Gross Value if new asset this year': value = sum( asset.filtered(lambda rec: rec.state == 'open' and rec. date >= self.date_from and rec.date <= self.date_to).mapped('value')) worksheet.write(col, raw, value, base_style) elif field == 'Sold or Disposed this year': value = sum( asset.filtered( lambda rec: rec.state == 'close' and rec.date >= self.date_from and rec.date <= self.date_to). mapped('depreciation_line_ids').mapped('amount')) worksheet.write(col, raw, value, base_style) elif field == 'Plus/minus transfers(always 0.00)': worksheet.write(col, raw, 0.0, base_style) elif field == 'Depreciation since start still last year': depreciation_lines = asset.filtered( lambda rec: rec.state == 'open' and rec.date < self. date_to).mapped('depreciation_line_ids') value = sum( depreciation_lines.filtered( lambda rec: rec.depreciation_date <= self.date_to). mapped('amount')) worksheet.write(col, raw, value, base_style) elif field == 'Residual end of this year': depreciation_lines = asset.filtered( lambda rec: rec.state == 'open' and rec.date >= self. date_from and rec.date <= self.date_to).mapped( 'depreciation_line_ids') value = sum( depreciation_lines.filtered( lambda rec: rec.depreciation_date <= self.date_to and fields.Datetime.from_string( rec.depreciation_date).month == self.env.user. company_id.fiscalyear_last_month).mapped( 'remaining_value')) worksheet.write(col, raw, value, base_style) elif field == 'Residual still last year': depreciation_lines = asset.filtered( lambda rec: rec.state == 'open' and rec.date < self. date_from).mapped('depreciation_line_ids') value = sum( depreciation_lines.filtered( lambda rec: rec.depreciation_date >= last_date_from and rec.depreciation_date <= last_date_to and fields.Datetime.from_string(rec.depreciation_date). month == self.env.user.company_id. fiscalyear_last_month).mapped('remaining_value')) worksheet.write(col, raw, value, base_style) elif field == 'Depreciation this year': depreciation_lines = asset.filtered( lambda rec: rec.state == 'open' and rec.date >= self. date_from and rec.date <= self.date_to).mapped( 'depreciation_line_ids') value = sum( depreciation_lines.filtered( lambda rec: rec.depreciation_date >= self.date_from and rec.depreciation_date <= self.date_to).mapped( 'amount')) worksheet.write(col, raw, value, base_style) elif field == 'Depreciation this year(always 0.00)': worksheet.write(col, raw, 0.0, base_style) raw += 1 col += 1 fp = BytesIO() workbook.save(fp) fp.seek(0) data = base64.encodestring(fp.read()) fp.close() name = "Asset_Detail_Report.xls" self.write({"name": name, 'data': data}) return { 'context': self.env.context, 'view_type': 'form', 'view_mode': 'form', 'res_model': 'asset.detail.report', 'res_id': self.id, 'view_id': self.env.ref('aok_account.view_asset_detail_report_download').id, 'type': 'ir.actions.act_window', 'target': 'new', }
def print_report(self): records = self.env['account.asset.asset'].search([('state', '!=', 'draft'), ('date', '>=', self.date_from), ('date', '<=', self.date_to)]) prev_records = self.env['account.asset.asset'].search([('state', '!=', 'draft'), ('date', '<', self.date_from)]) tot_records = records + prev_records last_date_from = fields.Date.to_string(fields.Date.from_string(self.date_from) + relativedelta(years=-1)) last_date_to = fields.Date.to_string(fields.Date.from_string(self.date_to) + relativedelta(years=-1)) if not tot_records: raise ValidationError(_('There are no record Found!')) accounts = records.mapped('category_id').mapped('account_asset_id') fieldss = ['', 'Column 1', 'Column 2', 'Column 3', 'Column 4', 'Column 5', 'Column 6', 'Column 7', 'Column 8', 'Column 9'] workbook = xlwt.Workbook() worksheet = workbook.add_sheet('Sheet 1') raw = 0 base_style = xlwt.easyxf('align: wrap yes') for field in fieldss: worksheet.write(0, raw, field, base_style) raw += 1 col = 1 for account in accounts: raw = 0 for field in fieldss: if field == '': worksheet.write(col, raw, account.name, base_style) elif field == 'Column 1': value = sum(prev_records.filtered(lambda rec: rec.state == 'open').mapped('value')) worksheet.write(col, raw, value, base_style) elif field == 'Column 2': value = sum(records.filtered(lambda rec: rec.state == 'open').mapped('value')) worksheet.write(col, raw, value, base_style) elif field == 'Column 3': value = sum(records.filtered(lambda rec: rec.state == 'close').mapped('depreciation_line_ids').mapped('amount')) worksheet.write(col, raw, value, base_style) elif field == 'Column 4': worksheet.write(col, raw, 0.0, base_style) elif field == 'Column 5': res = self.env['account.asset.asset'].search([('state', '!=', 'draft'), ('date', '<', self.date_to)]) depreciation_lines = res.filtered(lambda rec: rec.state == 'open').mapped('depreciation_line_ids') value = sum(depreciation_lines.filtered(lambda rec: rec.depreciation_date <= self.date_to).mapped('amount')) worksheet.write(col, raw, value, base_style) elif field == 'Column 6': depreciation_lines = records.filtered(lambda rec: rec.state == 'open').mapped('depreciation_line_ids') value = sum(depreciation_lines.filtered(lambda rec: rec.depreciation_date <= self.date_to and fields.Datetime.from_string(rec.depreciation_date).month == self.env.user.company_id.fiscalyear_last_month).mapped('remaining_value')) worksheet.write(col, raw, value, base_style) elif field == 'Column 7': depreciation_lines = prev_records.filtered(lambda rec: rec.state == 'open').mapped('depreciation_line_ids') value = sum(depreciation_lines.filtered(lambda rec: rec.depreciation_date >= last_date_from and rec.depreciation_date <= last_date_to and fields.Datetime.from_string(rec.depreciation_date).month == self.env.user.company_id.fiscalyear_last_month).mapped('remaining_value')) worksheet.write(col, raw, value, base_style) elif field == 'Column 8': depreciation_lines = records.filtered(lambda rec: rec.state == 'open').mapped('depreciation_line_ids') value = sum(depreciation_lines.filtered(lambda rec: rec.depreciation_date >= self.date_from and rec.depreciation_date <= self.date_to).mapped('amount')) worksheet.write(col, raw, value, base_style) elif field == 'Column 9': worksheet.write(col, raw, 0.0, base_style) raw += 1 col += 1 fp = BytesIO() workbook.save(fp) fp.seek(0) data = base64.encodestring(fp.read()) fp.close() name = "Asset_Summary_Report.xls" self.write({"name": name, 'data': data}) return { 'context': self.env.context, 'view_type': 'form', 'view_mode': 'form', 'res_model': 'asset.summary.report', 'res_id': self.id, 'view_id': self.env.ref('aok_account.view_asset_summary_report_download').id, 'type': 'ir.actions.act_window', 'target': 'new', }
def from_data_xls(self, rows): tz = pytz.timezone(request.env['res.users'].sudo().browse( SUPERUSER_ID).partner_id.tz) or pytz.utc workbook = xlwt.Workbook() worksheet = workbook.add_sheet('Sheet 1') for i, fieldname in enumerate(self.heaser_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, cv_key in enumerate(self.search_fields): # _logger.info('cv_key:%s' % cv_key) cell_value = row.get(cv_key) if cell_value and cv_key in ['order_user', 'contact_usr']: cell_value = "".join(x if i != 1 else '*' for i, x in enumerate(cell_value)) if cell_value and cv_key in ['checkin_time']: cell_value = datetime.datetime.strftime( datetime.datetime.strptime( cell_value, DTF).replace(tzinfo=pytz.utc).astimezone(tz), '%Y/%m/%d %H:%M') # _logger.info('cell_value:%s' % cell_value) 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 elif isinstance(cell_value, bool): cell_value = '' worksheet.write(row_index + 1, cell_index, cell_value, cell_style) # 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) worksheet.col(0).width = 256 * 16 worksheet.col(1).width = 256 * 16 worksheet.col(2).width = 256 * 12 worksheet.col(3).width = 256 * 30 worksheet.col(4).width = 256 * 12 worksheet.col(5).width = 256 * 16 worksheet.col(6).width = 256 * 6 worksheet.col(7).width = 256 * 12 worksheet.col(8).width = 256 * 16 worksheet.col(9).width = 256 * 26 fp = StringIO() workbook.save(fp) fp.seek(0) data = fp.read() fp.close() return data
def attendance_export_xls(self, appraisal_id, access_token): appraisal_data = request.env['hr.attendance.export'].browse( appraisal_id) book = xlwt.Workbook(encoding='utf-8') sheet1 = book.add_sheet("Attendance Data") style = xlwt.XFStyle() style_header = xlwt.XFStyle() style_right = xlwt.XFStyle() style_right_bold = xlwt.XFStyle() style_left = xlwt.XFStyle() font = xlwt.Font() font.bold = True style.font = font style_header.font = font style_right_bold.font = font # background color pattern = xlwt.Pattern() pattern.pattern = xlwt.Pattern.SOLID_PATTERN pattern.pattern_fore_colour = xlwt.Style.colour_map['pale_blue'] style.pattern = pattern # style.num_format_str = '0.0' # alignment alignment = xlwt.Alignment() alignment.horz = xlwt.Alignment.HORZ_LEFT style.alignment = alignment style.alignment.wrap = 100 borders = xlwt.Borders() borders.left = xlwt.Borders.THIN borders.right = xlwt.Borders.THIN borders.top = xlwt.Borders.THIN borders.bottom = xlwt.Borders.THIN borders.left_colour = 0x00 borders.right_colour = 0x00 borders.top_colour = 0x00 borders.bottom_colour = 0x00 pattern1 = xlwt.Pattern() pattern1.pattern1 = xlwt.Pattern.SOLID_PATTERN pattern1.pattern_fore_colour = xlwt.Style.colour_map['pale_blue'] style_header.pattern1 = pattern1 style_header.alignment.wrap = 12 # alignment alignment1 = xlwt.Alignment() alignment1.horz = xlwt.Alignment.HORZ_CENTER style_header.alignment = alignment1 alignment2 = xlwt.Alignment() alignment2.horz = xlwt.Alignment.HORZ_RIGHT style_right.alignment = alignment2 # style_right.num_format_str = '0.0' style_right.alignment.wrap = 12 alignment3 = xlwt.Alignment() alignment3.horz = xlwt.Alignment.HORZ_LEFT style_left.alignment = alignment3 # style_left.num_format_str = '0.0' style_left.alignment.wrap = 100 alignment4 = xlwt.Alignment() alignment4.horz = xlwt.Alignment.HORZ_RIGHT style_right_bold.alignment = alignment2 # style_right_bold.num_format_str = '0.0' style_right_bold.alignment.wrap = 12 pattern4 = xlwt.Pattern() pattern4.pattern = xlwt.Pattern.SOLID_PATTERN # style_right_bold.pattern = pattern4 sub_style = xlwt.easyxf('pattern: pattern solid, fore_colour gray80;' 'font: colour white, bold True;') data_style = xlwt.easyxf('pattern: pattern solid, fore_colour white;' 'font: colour black, bold True;') total_style = xlwt.easyxf('pattern: pattern solid, fore_colour yellow;' 'font: colour black, bold True;') style.borders = borders style_left.borders = borders style_header.borders = borders style_right.borders = borders style_right_bold.borders = borders row = 0 col = 0 from_date = appraisal_data.from_date to_date = appraisal_data.to_date print("11111111111111111", from_date, to_date) sheet1.write_merge(2, 2, 0, 1, 'From Date:', sub_style) sheet1.write_merge(2, 2, 2, 5, appraisal_data.from_date, style) sheet1.write_merge(3, 3, 0, 1, 'To Date:', sub_style) sheet1.write_merge(3, 3, 2, 5, appraisal_data.to_date, style) # sheet1.write_merge(4, 4, 0, 1,'Application Year:', sub_style) # sheet1.write_merge(4, 4, 2, 5, appraisal_data.application_year.name, style) attendance_rec = request.env['hr.attendance'].search( [('attendance_date', '>', from_date), ('attendance_date', '<=', to_date)], order='employee_code, attendance_date') # print(attendance_rec,'attendance') sheet1.write_merge(6, 6, 0, 25, 'Attendance Data', style_header) sheet1.write(8, 0, 'Sr. No.', style_header) sheet1.write_merge(8, 8, 1, 1, 'Employee Code', style_header) sheet1.write_merge(8, 8, 2, 4, 'Employee Name', style_header) sheet1.write_merge(8, 8, 5, 6, 'Department', style_header) sheet1.write_merge(8, 8, 7, 8, 'Designation', style_header) sheet1.write_merge(8, 8, 9, 9, 'Site', style_header) sheet1.write_merge(8, 8, 10, 10, 'Shift', style_header) sheet1.write_merge(8, 8, 11, 11, 'Date', style_header) sheet1.write_merge(8, 8, 12, 12, 'In Time', style_header) sheet1.write_merge(8, 8, 13, 13, 'Out Time', style_header) sheet1.write_merge(8, 8, 14, 14, 'Worked Hours', style_header) sheet1.write_merge(8, 8, 15, 15, 'Status', style_header) count = 0 sr_no = [] row = 10 for x in attendance_rec: count += 1 sr_no.append(count) sheet1.write(row, col, count, style_header) sheet1.write_merge(row, row, 1, 1, x.employee_id.emp_code, style_right) sheet1.write_merge(row, row, 2, 4, x.employee_id.name, style_right) sheet1.write_merge(row, row, 5, 6, x.employee_id.department_id.name, style_right) sheet1.write_merge(row, row, 7, 8, x.employee_id.job_id.name, style_right) sheet1.write_merge(row, row, 9, 9, x.employee_id.site_master_id.name, style_right) sheet1.write_merge(row, row, 10, 10, x.shift.name, style_right) sheet1.write_merge(row, row, 11, 11, x.attendance_date, style_right) sheet1.write_merge(row, row, 12, 12, x.in_time, style_right) sheet1.write_merge(row, row, 13, 13, x.out_time, style_right) sheet1.write_merge(row, row, 14, 14, x.worked_hours, style_right) sheet1.write_merge(row, row, 15, 15, x.employee_status, style_right) row += 1 filename = 'AttendanceExport.xls' #%(appraisal_data.review_cycle) response = request.make_response( None, headers=[('Content-Type', 'application/vnd.ms-excel'), ('Content-Disposition', content_disposition(filename))]) book.save(response.stream) return response
def stock_report(self): workbook = xlwt.Workbook(encoding='utf-8') worksheet = workbook.add_sheet('Stock Info') 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') 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') normal = xlwt.easyxf("align: horiz center, vert center;" "borders: left thin, right thin, top thin, bottom thin;" "font: name Times New Roman;", num_format_str='#,##0') r = 0 c = 0 output_header = ['Barcode', 'Item Name', 'Purchased Qty', 'Sold Qty', 'Consumed Qty', 'Adjusted Qty', 'Current Stock', 'Unit Cost', 'Inventory Value'] for item in output_header: worksheet.write(r, c, item, bold) col = worksheet.col(c) if c == 2: col.width = 1200 * 9 if c == 1: col.width = 900 * 8 else: col.width = 900 * 4 worksheet.row(r).height_mismatch = True worksheet.row(r).height = 220 * 2 c += 1 quant_obj = self.env['stock.quant'] move_obj = self.env['stock.move'] dom = [('state', '=', 'done'), ] if self.start_date: dom.append(('date', '>=', self.start_date)) if self.end_date: dom.append(('date', '<=', self.end_date)) moves = move_obj.search(dom).ids if self.pdt_ids: for pdt in self.pdt_ids: count = pdt.with_context(location=self.location_id.id) if self.show_no_stock or count.qty_available > 0: data = [] if pdt.barcode: data.append(pdt.barcode) else: data.append('') data.append(pdt.name) move_ids = move_obj.search([('product_id', '=', pdt.id), ('id', 'in', moves), ('location_id.usage', '=', 'supplier'), ('location_dest_id.usage', '=', 'internal')]) purchase_count = sum(move.product_uom_qty for move in move_ids) sale_ids = move_obj.search([('product_id', '=', pdt.id), ('id', 'in', moves), ('location_id.usage', '=', 'internal'), ('location_dest_id.usage', '=', 'customer')]) sale_count = sum(move.product_uom_qty for move in sale_ids) consu_ids = move_obj.search([('product_id', '=', pdt.id), ('id', 'in', moves), ('consumed', '=', True), ('location_id.usage', '=', 'inventory'), ('location_dest_id.usage', '=', 'internal')]) consu_count = sum(move.product_uom_qty for move in consu_ids) consum_ids = move_obj.search([('product_id', '=', pdt.id), ('id', 'in', moves), ('consumed', '=', True), ('location_id.usage', '=', 'internal'), ('location_dest_id.usage', '=', 'inventory')]) consum_count = sum(move.product_uom_qty for move in consum_ids) adjust_ids = move_obj.search([('product_id', '=', pdt.id), ('id', 'in', moves), ('consumed', '=', False), ('location_id.usage', '=', 'inventory'), ('location_dest_id.usage', '=', 'internal')]) adjust_count = sum(move.product_uom_qty for move in adjust_ids) adjus_ids = move_obj.search([('product_id', '=', pdt.id), ('id', 'in', moves), ('consumed', '=', False), ('location_id.usage', '=', 'internal'), ('location_dest_id.usage', '=', 'inventory')]) adjus_count = sum(move.product_uom_qty for move in adjus_ids) data.append(purchase_count) data.append(sale_count) data.append(consum_count - consu_count) data.append(adjust_count - adjus_count) data.append(count.qty_available) data.append(pdt.standard_price) data.append(count.qty_available * pdt.standard_price) r += 1 c = 0 for item in data: if self.show_detailed: worksheet.write(r, c, item, grey) else: worksheet.write(r, c, item, normal) c += 1 if self.show_detailed: qry = 'select sum(stock_quant.qty), stock_quant.lot_id, p_o.name, p_o.life_date from stock_quant JOIN stock_production_lot AS p_o ON stock_quant.lot_id = p_o.id where stock_quant.product_id=%s and stock_quant.location_id=%s group by stock_quant.lot_id, p_o.name, p_o.life_date' params = (pdt.id, self.location_id.id) self.env.cr.execute(qry, params) result = self.env.cr.dictfetchall() for item in result: r += 1 c = 0 if item['lot_id']: worksheet.write_merge(r, r, c, c + 4, item['name'], normal) c += 5 else: worksheet.write_merge(r, r, c, c + 4, '', normal) c += 5 date_order = '' if item['life_date']: date_order = datetime.strptime(item['life_date'], '%Y-%m-%d %H:%M:%S').date() date_order = date_order.strftime('%d/%m/%Y') data.append(date_order) worksheet.write(r, c, date_order, normal) c += 1 worksheet.write(r, c, item['sum'], normal) c += 1 worksheet.write(r, c, '', normal) c += 1 worksheet.write(r, c, '', normal) c += 1 else: for pdt in self.env['product.product'].search([('type', '!=', 'service')]): count = pdt.with_context(location=self.location_id.id) if self.show_no_stock or count.qty_available > 0: data = [] if pdt.barcode: data.append(pdt.barcode) else: data.append('') data.append(pdt.name) move_ids = move_obj.search([('product_id', '=', pdt.id), ('id', 'in', moves), ('location_id.usage', '=', 'supplier'), ('location_dest_id.usage', '=', 'internal')]) purchase_count = sum(move.product_uom_qty for move in move_ids) sale_ids = move_obj.search([('product_id', '=', pdt.id), ('id', 'in', moves), ('location_id.usage', '=', 'internal'), ('location_dest_id.usage', '=', 'customer')]) sale_count = sum(move.product_uom_qty for move in sale_ids) consu_ids = move_obj.search([('product_id', '=', pdt.id), ('id', 'in', moves), ('consumed', '=', True), ('location_id.usage', '=', 'inventory'), ('location_dest_id.usage', '=', 'internal')]) consu_count = sum(move.product_uom_qty for move in consu_ids) consum_ids = move_obj.search([('product_id', '=', pdt.id), ('id', 'in', moves), ('consumed', '=', True), ('location_id.usage', '=', 'internal'), ('location_dest_id.usage', '=', 'inventory')]) consum_count = sum(move.product_uom_qty for move in consum_ids) adjust_ids = move_obj.search([('product_id', '=', pdt.id), ('id', 'in', moves), ('consumed', '=', False), ('location_id.usage', '=', 'inventory'), ('location_dest_id.usage', '=', 'internal')]) adjust_count = sum(move.product_uom_qty for move in adjust_ids) adjus_ids = move_obj.search([('product_id', '=', pdt.id), ('id', 'in', moves), ('consumed', '=', False), ('location_id.usage', '=', 'internal'), ('location_dest_id.usage', '=', 'inventory')]) adjus_count = sum(move.product_uom_qty for move in adjus_ids) data.append(purchase_count) data.append(sale_count) data.append(consum_count - consu_count) data.append(adjust_count - adjus_count) data.append(count.qty_available) data.append(pdt.standard_price) data.append(count.qty_available * pdt.standard_price) r += 1 c = 0 for item in data: if self.show_detailed: worksheet.write(r, c, item, grey) else: worksheet.write(r, c, item, normal) c += 1 if self.show_detailed: qry = 'select sum(stock_quant.qty), stock_quant.lot_id, p_o.name, p_o.life_date from stock_quant JOIN stock_production_lot AS p_o ON stock_quant.lot_id = p_o.id where stock_quant.product_id=%s and stock_quant.location_id=%s group by stock_quant.lot_id, p_o.name, p_o.life_date' params = (pdt.id, self.location_id.id) self.env.cr.execute(qry, params) result = self.env.cr.dictfetchall() for item in result: r += 1 c = 0 if item['lot_id']: worksheet.write_merge(r, r, c, c + 4, item['name'], normal) c += 5 else: worksheet.write_merge(r, r, c, c + 4, '', normal) c += 5 date_order = '' if item['life_date']: date_order = datetime.strptime(item['life_date'], '%Y-%m-%d %H:%M:%S').date() date_order = date_order.strftime('%d/%m/%Y') data.append(date_order) worksheet.write(r, c, date_order, normal) c += 1 worksheet.write(r, c, item['sum'], normal) c += 1 worksheet.write(r, c, '', normal) c += 1 worksheet.write(r, c, '', normal) c += 1 buf = io.BytesIO() workbook.save(buf) out = base64.encodestring(buf.getvalue()) name = "Stock_report" + ".xls" self.write({'state': 'get', 'data': out, 'name': name}) return { 'type': 'ir.actions.act_window', 'res_model': 'salon.stock.report', 'view_mode': 'form', 'view_type': 'form', 'res_id': self.id, 'views': [(False, 'form')], 'target': 'new', }
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, title, company_id, date_from, date_to, journal_id, **kw): company = request.env['res.company'].search([('id', '=', company_id)]) journal = request.env['account.journal'].search([('id', '=', journal_id)]) account_invoice = request.env['account.invoice'].search([ ('journal_id.id', '=', journal_id), ('type', '=', 'out_invoice'), ('state', 'in', ('open', 'paid')), ('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 = 500 * 12 worksheet.col(1).width = 500 * 12 worksheet.col(2).width = 500 * 12 worksheet.col(3).width = 500 * 12 worksheet.col(4).width = 500 * 12 worksheet.col(5).width = 350 * 12 worksheet.col(6).width = 350 * 12 worksheet.col(7).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 worksheet.col(21).width = 350 * 12 worksheet.col(22).width = 350 * 12 worksheet.col(23).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, 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, 'BRANCH', style_table_header_bold) # HEADER worksheet.write_merge(7, 8, 1, 1, 'REFERENCE DATE', style_table_header_bold) # HEADER worksheet.write_merge(7, 8, 2, 2, 'NAME OF CLIENT', style_table_header_bold) # HEADER worksheet.write_merge(7, 8, 3, 3, 'ADDRESS', style_table_header_bold) # HEADER worksheet.write_merge(7, 8, 4, 4, 'TIN', style_table_header_bold) # HEADER worksheet.write_merge(7, 7, 5, 8, 'REFERENCE', style_table_header_bold) # HEADER worksheet.write(8, 5, 'SALES INVOICE', style_table_header_bold) # HEADER worksheet.write(8, 6, 'OFFICIAL RECEIPTS', style_table_header_bold) # HEADER worksheet.write(8, 7, 'DELIVERY RECEIPTS', style_table_header_bold) # HEADER worksheet.write(8, 8, 'OTHERS', style_table_header_bold) # HEADER worksheet.write_merge(7, 8, 9, 9, 'AMOUNT', style_table_header_bold) # HEADER worksheet.write_merge(7, 7, 10, 11, 'DOMESTIC', style_table_header_bold) # HEADER worksheet.write(8, 10, 'GOODS', style_table_header_bold) # HEADER worksheet.write(8, 11, 'SERVICES', style_table_header_bold) # HEADER worksheet.write_merge(7, 8, 12, 12, 'SALES TO GOVERNMENT', style_table_header_bold) # HEADER worksheet.write_merge(7, 8, 13, 13, 'VATABLE', style_table_header_bold) # HEADER worksheet.write_merge(7, 8, 14, 14, 'ZERO RATED', style_table_header_bold) # HEADER worksheet.write_merge(7, 8, 15, 15, 'EXEMPT', style_table_header_bold) # HEADER worksheet.write_merge(7, 8, 16, 16, 'TOTAL TAXABLE SALES', style_table_header_bold) # HEADER worksheet.write_merge(7, 8, 17, 17, 'OUTPUT TAX (12%)', style_table_header_bold) # HEADER worksheet.write_merge(7, 8, 18, 18, 'TOTAL INVOICE AMOUNT', style_table_header_bold) # HEADER worksheet.write_merge(7, 8, 19, 19, '%7 STANDARD INPUT VAT', style_table_header_bold) # HEADER worksheet.write_merge(7, 8, 20, 20, 'BIR FORM 2306', style_table_header_bold) # HEADER worksheet.write_merge(7, 8, 21, 21, 'DATE RECEIVED', style_table_header_bold) # HEADER worksheet.write_merge(7, 8, 22, 22, 'BIR FORM 2307', style_table_header_bold) # HEADER worksheet.write_merge(7, 8, 23, 23, 'DATE RECEIVED', style_table_header_bold) # HEADER # TABLE ROW LINES # table_row_start = 9 row_count = 9 transaction_count = 0 for account in account_invoice: official_receipt = '' delivery_receipt = '' for payment in account.payment_ids: if official_receipt == '': official_receipt += payment.name else: official_receipt += ', ' + payment.name if account.origin: sale_order = request.env['sale.order'].search( [('name', '=', account.origin)], limit=1) if sale_order: if sale_order.picking_ids: for pick in sale_order.picking_ids: if delivery_receipt == '': delivery_receipt += pick.name else: delivery_receipt += ', ' + pick.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.partner_id.name, style_table_row) worksheet.write( row_count, 3, '%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, 4, account.partner_id.vat or '', style_table_row) worksheet.write(row_count, 5, account.number, style_table_row) worksheet.write(row_count, 6, official_receipt, style_table_row) worksheet.write(row_count, 7, delivery_receipt, style_table_row) worksheet.write(row_count, 8, '', style_table_row) worksheet.write(row_count, 9, account.amount_total, style_table_row_amount) worksheet.write(row_count, 10, account.amount_goods, style_table_row_amount) worksheet.write(row_count, 11, account.amount_services, style_table_row_amount) worksheet.write(row_count, 12, '', style_table_row_amount) worksheet.write(row_count, 13, account.vat_sales, style_table_row_amount) worksheet.write(row_count, 14, account.zero_rated_sales, style_table_row_amount) worksheet.write(row_count, 15, account.vat_exempt_sales, style_table_row_amount) worksheet.write(row_count, 16, account.vat_sales, style_table_row_amount) worksheet.write(row_count, 17, account.amount_tax, style_table_row_amount) worksheet.write(row_count, 18, account.amount_total, style_table_row_amount) worksheet.write(row_count, 19, '', style_table_row) worksheet.write(row_count, 20, '', style_table_row) worksheet.write(row_count, 21, '', style_table_row) worksheet.write(row_count, 22, '', style_table_row) worksheet.write(row_count, 23, '', style_table_row) row_count += 1 transaction_count += 1 table_total_start = row_count # TABLE TOTALS worksheet.write(table_total_start, 0, '', style_table_total) worksheet.write(table_total_start, 1, '', style_table_total) worksheet.write(table_total_start, 2, '', style_table_total) worksheet.write(table_total_start, 3, '', style_table_total) worksheet.write(table_total_start, 4, '', style_table_total) worksheet.write(table_total_start, 5, '', style_table_total) worksheet.write(table_total_start, 6, '', style_table_total) worksheet.write(table_total_start, 7, '', 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(table_total_start, 19, '', style_table_total_value) worksheet.write(table_total_start, 20, '', style_table_total_value) worksheet.write(table_total_start, 21, '', style_table_total_value) worksheet.write(table_total_start, 22, '', style_table_total_value) worksheet.write(table_total_start, 23, '', style_table_total_value) response = request.make_response( None, headers=[('Content-Type', 'application/vnd.ms-excel'), ('Content-Disposition', 'attachment; filename=%s;' % (filename))]) workbook.save(response.stream) return response