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
