def download_attachment(self):
'''
下载附件
:return:
'''
all_rec = request.env['metro_park_base.time_table_data'].search_read(
[])
one_row = [
'序号', '所属时刻表', '车次', '出场场段', '入场场段', '计划回库时间', '计划出库时间', '高峰时段'
]
field_list = [
'sequence', 'time_table_id', 'train_no', 'out_location',
'back_location', 'plan_in_time', 'plan_out_time', 'high_time_train'
]
# 新建一个excel文件
file = xlwt.Workbook()
table = file.add_sheet('sheet', cell_overwrite_ok=True)
for data_i, head_sheet in enumerate(one_row):
table.write(0, data_i, head_sheet)
for i, data in enumerate(all_rec):
for j in range(len(field_list)):
table.write(i + 1, j, data.get(field_list[j], ''))
fp = io.BytesIO()
file.save(fp)
fp.seek(0)
data = fp.read()
fp.close()
name = '运行图附件.xls'
response = request.make_response(data)
response.headers['Content-Type'] = 'application/vnd.ms-excel'
response.headers["Content-Disposition"] = "attachment; filename={}". \
format(name.encode().decode('latin-1'))
return response
def consignment_stock_report_print(self):
self.file_name = 'filename.xls'
workbook = xlwt.Workbook()
worksheet = workbook.add_sheet('Sheet 1')
# style = xlwt.easyxf('font: bold True, name Arial;')
worksheet.write_merge(
0, 1, 0, 3, 'your data that you want to show into excelsheet')
fp = BytesIO()
workbook.save(fp)
self.file_name = 'Long roll Report.xls'
record_id = self.env['stock.consignment.report'].create(
{
'excel_file': base64.encodestring(fp.getvalue()),
'file_name': self.file_name
}, )
fp.close()
return {
'view_mode': 'form',
'res_id': record_id.id,
'res_model': 'stock.consignment.report',
'view_type': 'form',
'type': 'ir.actions.act_window',
'context': self.env.context,
'target': 'new',
}
def download_cvi_by_userlist(self, model, id, **kw):
dlcv_obj = request.env[model].browse(int(id))
where_clause = generate_domain_date_and_department(dlcv_obj,
theo_sql=True)
sql_cmd = '''select cvi.user_id,sum(diemtc),u.login,p.name from cvi inner join res_users as u on cvi.user_id = u.id inner join res_partner as p on u.partner_id = p.id %s group by cvi.user_id ,u.login,p.name'''
sql_cmd = sql_cmd % (
(' where ' + where_clause) if where_clause else '')
request.env.cr.execute(sql_cmd)
rsul = request.env.cr.fetchall()
workbook = xlwt.Workbook()
worksheet = workbook.add_sheet('Sheet 1')
normal_style = xlwt.easyxf("font: name Times New Roman, height 240")
worksheet.write(0, 0, 'STT', normal_style)
worksheet.write(0, 1, u'Tên', normal_style)
worksheet.write(0, 2, u'Điểm', normal_style)
row_index = 1
stt = 1
for u_id, diem, login, name in rsul:
worksheet.write(row_index, 0, stt, normal_style)
worksheet.write(row_index, 1, login, normal_style)
worksheet.write(row_index, 2, diem, normal_style)
row_index += 1
stt += 1
response = request.make_response(
None,
headers=[('Content-Type', 'application/vnd.ms-excel'),
('Content-Disposition',
'attachment; filename=table_cv_%s_%s.xls;' %
(request.env.user.name,
datetime.datetime.now().strftime('%d_%m_%H_%M')))],
)
workbook.save(response.stream)
return response
def download_time_table(self, time_table_id, **kwargs):
'''
下载运行图
:param kwargs:
:param time_table_id:
:return:
'''
records = request.env['metro_park_base.time_table_data'].search_read([
('time_table_id', '=', time_table_id)
])
if not records:
return
one_row = [
'序号', '回库车次', '车号', '换轨时间', '接车股道', '变更股道', '洗车', '检修计划', '备注'
]
field_list = ['dev_name', 'dev_no', 'miles', 'unit']
# 新建一个excel文件
file = xlwt.Workbook()
table = file.add_sheet('sheet', cell_overwrite_ok=True)
for data_i, head_sheet in enumerate(one_row):
table.write(0, data_i, head_sheet)
for i, data in enumerate(records):
for j in range(len(field_list)):
table.write(i + 1, j, data.get(field_list[j], ''))
fp = io.BytesIO()
file.save(fp)
fp.seek(0)
data = fp.read()
fp.close()
name = '运行图模版.xls'
response = request.make_response(data)
response.headers['Content-Type'] = 'application/vnd.ms-excel'
response.headers["Content-Disposition"] = "attachment; filename={}". \
format(name.encode().decode('latin-1'))
return response
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 generate_hr_employee_report(self):
workbook = xlwt.Workbook()
worksheet = workbook.add_sheet("Employees")
style = xlwt.easyxf("font: bold True;"
"align: vert centre, horiz center;"
"border: right 2, left 2, top 2, bottom 2;")
style_date = xlwt.XFStyle()
style_date.num_format_str = DF
row, col = 0, 0
self.add_header_label(worksheet, style, row, col)
row = 1
i = 0
for emp in self.employee_ids:
col = 0
i += 1
s_no = i
name = emp.name
id_number = emp.identification_id if emp.identification_id else ""
operating_unit = emp.operating_unit_id.name if emp.operating_unit_id else ""
department = emp.department_id.name if emp.department_id else ""
job_position = emp.job_id.name if emp.job_id else ""
line_manager = emp.line_manager.name if emp.line_manager else ""
senior_manager = emp.senior_manager.name if emp.senior_manager else ""
phone = emp.work_phone if emp.work_phone else ""
worksheet.write(row, col, s_no)
worksheet.write(row, col + 1, name)
worksheet.write(row, col + 2, id_number)
worksheet.write(row, col + 3, operating_unit)
worksheet.write(row, col + 4, department)
worksheet.write(row, col + 5, job_position)
worksheet.write(row, col + 6, line_manager)
worksheet.write(row, col + 7, senior_manager)
worksheet.write(row, col + 8, phone)
row += 1
fp = io.BytesIO()
workbook.save(fp)
filename = "employees_report.xls"
export_id = self.env["hr.employee.report"].create({
"excel_file":
base64.encodestring(fp.getvalue()),
"file_name":
filename
})
fp.close()
return {
"view_mode": "form",
"res_id": export_id.id,
"res_model": "hr.employee.report",
"view_type": "form",
"type": "ir.actions.act_window",
"context": self._context,
"target": "new",
}
def from_data(self, fields, rows):
if len(rows) > 65535:
raise UserError(
_('There are too many rows (%s rows, limit: 65535) to export as Excel 97-2003 (.xls) format. Consider splitting the export.'
) % len(rows))
workbook = xlwt.Workbook()
worksheet = workbook.add_sheet('Sheet 1')
for i, fieldname in enumerate(fields):
worksheet.write(0, i, fieldname)
worksheet.col(i).width = 8000 # around 220 pixels
base_style = xlwt.easyxf('align: wrap yes')
date_style = xlwt.easyxf('align: wrap yes',
num_format_str='YYYY-MM-DD')
datetime_style = xlwt.easyxf('align: wrap yes',
num_format_str='YYYY-MM-DD HH:mm:SS')
for row_index, row in enumerate(rows):
for cell_index, cell_value in enumerate(row):
cell_style = base_style
if isinstance(cell_value, bytes) and not isinstance(
cell_value, pycompat.string_types):
# because xls uses raw export, we can get a bytes object
# here. xlwt does not support bytes values in Python 3 ->
# assume this is base64 and decode to a string, if this
# fails note that you can't export
try:
cell_value = pycompat.to_text(cell_value)
except UnicodeDecodeError:
raise UserError(
_("Binary fields can not be exported to Excel unless their content is base64-encoded. That does not seem to be the case for %s."
) % fields[cell_index])
if isinstance(cell_value, pycompat.string_types):
cell_value = re.sub("\r", " ",
pycompat.to_text(cell_value))
# Excel supports a maximum of 32767 characters in each cell:
cell_value = cell_value[:32767]
elif isinstance(cell_value, datetime.datetime):
cell_style = datetime_style
elif isinstance(cell_value, datetime.date):
cell_style = date_style
worksheet.write(row_index + 1, cell_index, cell_value,
cell_style)
fp = io.BytesIO()
workbook.save(fp)
fp.seek(0)
data = fp.read()
fp.close()
return data
def action_print_stock_report(self):
ctx = dict(self.env.context) or {}
workbook = xlwt.Workbook()
worksheet = workbook.add_sheet('Inventory')
column_heading_style = xlwt.easyxf('font:height 200;font:bold True;')
row = 2
for wizard in self:
report_head = 'Stock Take Report'
if wizard.inventory_date:
report_head += ' (' + wizard.inventory_date + ')'
worksheet.write_merge(
0, 0, 0, 2, report_head,
xlwt.easyxf(
'font:height 300; align: vertical center; align: horiz center;pattern: pattern solid, fore_color black; font: color white; font:bold True;'
"borders: top thin,bottom thin"))
worksheet.write(1, 0, _('Internal Reference'),
column_heading_style)
worksheet.write(1, 1, _('Product'), column_heading_style)
worksheet.write(1, 2, _('Quantity On Hand'), column_heading_style)
worksheet.col(0).width = 5000
worksheet.col(1).width = 10000
worksheet.col(2).width = 5000
worksheet.row(0).height = 500
ctx.update({'to_date': wizard.inventory_date})
product_objs = self.env['product.product'].with_context(
ctx).search([], order='name')
for product in product_objs:
if product.qty_available > 0:
if product.default_code:
worksheet.write(row, 0, product.default_code)
worksheet.write(row, 1, product.name)
worksheet.write(row, 2, product.qty_available)
row += 1
fp = io.BytesIO()
workbook.save(fp)
excel_file = base64.encodestring(fp.getvalue())
wizard.stock_report_file = excel_file
wizard.file_name = 'Stock Take Report.xls'
wizard.inventory_printed = True
fp.close()
return {
'view_mode': 'form',
'res_id': wizard.id,
'res_model': 'print.stock.report',
'view_type': 'form',
'type': 'ir.actions.act_window',
'context': self.env.context,
'target': 'new',
}
def from_data(self, fields, rows, listType=None):
if len(rows) > 65535:
raise UserError(
_('导出的行数过多 (%s 行, 上限为: 65535行) , 请分多次导出') % len(rows))
workbook = xlwt.Workbook()
worksheet = workbook.add_sheet('Sheet 1')
header_style = xlwt.easyxf(
'font:bold True;align: vert center, horiz center;')
for i, fieldname in enumerate(fields):
worksheet.write(0, i, fieldname, header_style)
worksheet.col(i).width = self.setColumnWidth(
fieldname) # around 220 pixels
base_style = xlwt.easyxf('align: wrap yes')
date_style = xlwt.easyxf('align: wrap yes',
num_format_str='YYYY-MM-DD')
datetime_style = xlwt.easyxf('align: wrap yes',
num_format_str='YYYY-MM-DD HH:mm:SS')
for row_index, row in enumerate(rows):
for cell_index, cell_value in enumerate(row):
cell_style = base_style
if isinstance(cell_value, bytes) and not isinstance(
cell_value, pycompat.string_types):
try:
cell_value = pycompat.to_text(cell_value)
except UnicodeDecodeError:
raise UserError(
_("Binary fields can not be exported to Excel unless their content is base64-encoded. That does not seem to be the case for %s."
) % fields[cell_index])
if isinstance(cell_value, pycompat.string_types):
cell_value = re.sub("\r", " ",
pycompat.to_text(cell_value))
# Excel supports a maximum of 32767 characters in each cell:
cell_value = cell_value[:32767]
elif isinstance(cell_value, datetime.datetime):
cell_style = datetime_style
elif isinstance(cell_value, datetime.date):
cell_style = date_style
worksheet.write(row_index + 1, cell_index, cell_value,
cell_style)
fp = io.BytesIO()
workbook.save(fp)
fp.seek(0)
data = fp.read()
fp.close()
return data
def download_list_intern(self, **kwargs):
invoices = request.env['intern.invoice'].search([])
workbook = xlwt.Workbook()
worksheet = workbook.add_sheet('Sheet 1')
base_style = xlwt.easyxf('align: wrap yes')
date_style = xlwt.easyxf('align: wrap yes',
num_format_str='YYYY-MM-DD')
worksheet.write(0, 0, u'Tên', base_style)
worksheet.write(0, 1, u'CMND/Thẻ CC', base_style)
worksheet.write(0, 2, u'Đơn hàng', base_style)
worksheet.write(0, 3, u'Ngày lập hồ sơ', base_style)
worksheet.write(0, 4, u'Ngày sinh', base_style)
worksheet.write(0, 5, u'Tuổi', base_style)
counter = 1
for invoice in invoices:
for intern in invoice.interns_pass:
if intern.cmnd_or_tcc != None and invoice.date_create_letter_promotion_short != None:
worksheet.write(counter, 0, u'%s' % intern.name,
base_style)
worksheet.write(counter, 1, intern.cmnd_or_tcc, base_style)
worksheet.write(counter, 2, u'%s' % invoice.name,
base_style)
worksheet.write(counter, 3,
invoice.date_create_letter_promotion_short,
date_style)
worksheet.write(counter, 4, intern.date_of_birth_short,
date_style)
worksheet.write(
counter, 5, u'%s' % str(
intern_utils.get_age_jp(
invoice.date_create_letter_promotion_short,
intern.day, intern.month, intern.year)),
base_style)
counter = counter + 1
fp = StringIO()
workbook.save(fp)
fp.seek(0)
data = fp.read()
fp.close()
return request.make_response(
data,
headers=[('Content-Disposition', content_disposition('test.xls')),
('Content-Type', 'application/vnd.ms-excel')])
def download_cvi(dlcv_obj):
num2alpha = dict(zip(range(0, 26), string.ascii_uppercase))
normal_border_style_not_border = xlwt.easyxf("font: name Times New Roman, height 240")
normal_border_style = xlwt.easyxf("font: name Times New Roman, height 240 ;borders: left thin,right thin, top thin, bottom thin")
bold_style = xlwt.easyxf("font: bold on")
department_ids = dlcv_obj.department_ids
export_department_ids = filter_department_ids(department_ids)
user_ids = request.env['res.users'].search([('department_id','in',export_department_ids)])
workbook = xlwt.Workbook()
offset_column = 0
ROW_TRUNG_TAM=0
ROW_SUM = ROW_TRUNG_TAM + 3
KEY_COL = offset_column + 3
VAL_COL = offset_column + 4
ROW_TITLE = ROW_TRUNG_TAM + 5
FIELDNAME_FIELDATTR = [
('stt',{'is_not_model_field':True,'string':u'STT'}),
('ngay_bat_dau',{'func':convert_date_odoo_to_str_vn_date,'width':get_width(10)}),
('code',{}),('tvcv_id_name',{'width':get_width(40)}),('noi_dung',{'width':get_width(40)}),
('diem_tvi',{}),('so_luong',{}),('so_lan',{}),
('diemtc',{'sum':True, 'row_sum':ROW_SUM+1, 'col_sum':VAL_COL}),
('diemld',{'sum':True,'row_sum':ROW_SUM, 'col_sum':VAL_COL}),
]
domain = []
domain_date = generate_domain_date_and_department(dlcv_obj)
print '**domain_date**'
print '**user_ids**',user_ids
for user_id in user_ids:
domain_user = [('user_id','=',user_id.id),('loai_record','=',u'Công Việc')]
domain = expression.AND([domain_user, domain_date])
worksheet = workbook.add_sheet(user_id.name,cell_overwrite_ok=True)
add_header_TrungTamHaTangMang(worksheet,user_id,ROW_TRUNG_TAM,offset_column,normal_border_style_not_border,bold_style,ROW_SUM,KEY_COL,VAL_COL)
cvi_fields = request.env['cvi']._fields
add_title(FIELDNAME_FIELDATTR, cvi_fields, offset_column, worksheet, ROW_TITLE)
row_index = ROW_TITLE + 1
stt = 1
person_records = request.env['cvi'].search(domain,order='ngay_bat_dau')
for r in person_records:#request.env['cvi'].search([]):
add_1_cvi_for_1_person(worksheet,FIELDNAME_FIELDATTR, r,offset_column, stt, row_index, normal_border_style)
row_index +=1
stt += 1
add_sum_info(worksheet,FIELDNAME_FIELDATTR,offset_column,num2alpha,ROW_TITLE,ROW_SUM,VAL_COL,row_index)
return workbook
def export_xls(self, data, token):
data = json.loads(data)
title = data.get('title', 'export')
workbook = xlwt.Workbook()
worksheet = workbook.add_sheet(title)
self._write_worksheet(worksheet, data)
response = request.make_response(
None,
headers=[
('Content-Type', 'application/vnd.ms-excel'),
('Content-Disposition', 'attachment; filename="%s - %s.xls"' %
(title, datetime.now()))
],
cookies={'fileToken': token})
workbook.save(response.stream)
return response
def from_data(self, fields, rows):
if len(rows) > 65535:
raise UserError(
_('There are too many rows (%s rows, limit: 65535) to export as Excel 97-2003 (.xls) format. Consider splitting the export.'
) % len(rows))
workbook = xlwt.Workbook()
worksheet = workbook.add_sheet('Sheet 1')
for i, fieldname in enumerate(fields):
worksheet.write(0, i, fieldname)
worksheet.col(i).width = 8000 # around 220 pixels
base_style = xlwt.easyxf('align: wrap yes')
date_style = xlwt.easyxf('align: wrap yes',
num_format_str='YYYY-MM-DD')
datetime_style = xlwt.easyxf('align: wrap yes',
num_format_str='YYYY-MM-DD HH:mm:SS')
for row_index, row in enumerate(rows):
for cell_index, cell_value in enumerate(row):
cell_style = base_style
if isinstance(cell_value, basestring):
cell_value = re.sub("\r", " ", cell_value)
elif isinstance(cell_value, datetime.datetime):
cell_style = datetime_style
elif isinstance(cell_value, datetime.date):
cell_style = date_style
worksheet.write(row_index + 1, cell_index, cell_value,
cell_style)
fp = StringIO()
workbook.save(fp)
fp.seek(0)
data = fp.read()
fp.close()
return data
def export_xls(self, data, token):
jdata = json.loads(data)
nbr_measures = jdata['nbr_measures']
workbook = xlwt.Workbook()
worksheet = workbook.add_sheet(jdata['title'])
header_bold = xlwt.easyxf(
"font: bold on; pattern: pattern solid, fore_colour gray25;")
header_plain = xlwt.easyxf(
"pattern: pattern solid, fore_colour gray25;")
bold = xlwt.easyxf("font: bold on;")
# Step 1: writing headers
headers = jdata['headers']
# x,y: current coordinates
# carry: queue containing cell information when a cell has a >= 2 height
# and the drawing code needs to add empty cells below
x, y, carry = 1, 0, deque()
for i, header_row in enumerate(headers):
worksheet.write(i, 0, '', header_plain)
for header in header_row:
while (carry and carry[0]['x'] == x):
cell = carry.popleft()
for i in range(nbr_measures):
worksheet.write(y, x + i, '', header_plain)
if cell['height'] > 1:
carry.append({'x': x, 'height': cell['height'] - 1})
x = x + nbr_measures
style = header_plain if 'expanded' in header else header_bold
for i in range(header['width']):
worksheet.write(y, x + i,
header['title'] if i == 0 else '', style)
if header['height'] > 1:
carry.append({'x': x, 'height': header['height'] - 1})
x = x + header['width']
while (carry and carry[0]['x'] == x):
cell = carry.popleft()
for i in range(nbr_measures):
worksheet.write(y, x + i, '', header_plain)
if cell['height'] > 1:
carry.append({'x': x, 'height': cell['height'] - 1})
x = x + nbr_measures
x, y = 1, y + 1
# Step 2: measure row
if nbr_measures > 1:
worksheet.write(y, 0, '', header_plain)
for measure in jdata['measure_row']:
style = header_bold if measure['is_bold'] else header_plain
worksheet.write(y, x, measure['measure'], style)
x = x + 1
y = y + 1
# Step 3: writing data
x = 0
for row in jdata['rows']:
worksheet.write(y, x, row['indent'] * ' ' + row['title'],
header_plain)
for cell in row['values']:
x = x + 1
if cell.get('is_bold', False):
worksheet.write(y, x, cell['value'], bold)
else:
worksheet.write(y, x, cell['value'])
x, y = 0, y + 1
response = request.make_response(
None,
headers=[('Content-Type', 'application/vnd.ms-excel'),
('Content-Disposition', 'attachment; filename=table.xls;')
],
cookies={'fileToken': token})
workbook.save(response.stream)
return response
def export_xls(self, filename, sale_ids, date_from, date_to, is_admin, **kw):
is_admin = ast.literal_eval(is_admin)
workbook = xlwt.Workbook()
worksheet = workbook.add_sheet('Sales Report')
sale_order_ids = False
sales = request.env['sale.order'].sudo().search([('id','in',ast.literal_eval(sale_ids))])
if sales:
sale_order_ids = sales
if not sales:
# sale_order_ids = request.env['sale.order'].search([('id','in',ast.literal_eval(sale_ids))])
sales_date = request.env['sale.order'].sudo().search([('date_order','>=',date_from),('date_order','<=',date_to)],order='date_order desc')
if sales_date:
sale_order_ids = sales_date
# STYLES
style_header_bold = xlwt.easyxf("font: bold on;font: name Calibri;align: wrap no")
style_header_right = xlwt.easyxf("font: name Calibri;align: horiz right, wrap no")
style_table_header_bold = xlwt.easyxf("font: bold on;font: name Calibri;align: horiz centre, vert centre, wrap on;borders: top thin, bottom thin, right thin;")
style_table_row = xlwt.easyxf("font: name Calibri;align: horiz left, wrap no;borders: top thin, bottom thin, right thin;")
style_table_row_amount = xlwt.easyxf("font: name Calibri;align: horiz right, wrap no;borders: top thin, bottom thin, right thin;", num_format_str="#,##0.00")
style_table_total = xlwt.easyxf("pattern: pattern solid, fore_colour pale_blue;font: bold on;font: name Calibri;align: horiz left, wrap no;borders: top thin, bottom medium, right thin;")
style_table_total_value = xlwt.easyxf("pattern: pattern solid, fore_colour pale_blue;font: bold on;font: name Calibri;align: horiz right, wrap no;borders: top thin, bottom medium, right thin;", num_format_str="#,##0.00")
style_end_report = xlwt.easyxf("font: bold on;font: name Calibri;align: horiz left, wrap no;")
worksheet.col(0).width = 250*12
worksheet.col(1).width = 500*12
worksheet.col(2).width = 250*12
worksheet.col(3).width = 250*12
worksheet.col(4).width = 250*12
worksheet.col(5).width = 250*12
worksheet.col(6).width = 250*12
worksheet.col(7).width = 250*12
worksheet.col(8).width = 250*12
worksheet.col(9).width = 250*12
worksheet.col(10).width = 250*12
worksheet.col(11).width = 250*12
worksheet.col(12).width = 250*12
worksheet.col(13).width = 250*12
worksheet.col(14).width = 250*12
worksheet.col(15).width = 250*12
worksheet.col(16).width = 250*12
worksheet.col(17).width = 250*12
# TEMPLATE HEADERS
# TABLE HEADER
worksheet.write(0, 0, 'Sales Agent', style_table_header_bold) # HEADER
worksheet.write(0, 1, "Client's Name", style_table_header_bold) # HEADER
worksheet.write(0, 2, 'City', style_table_header_bold) # HEADER
worksheet.write(0, 3, 'Area', style_table_header_bold) # HEADER
worksheet.write(0, 4, 'Invoice Date', style_table_header_bold) # HEADER
worksheet.write(0, 5, 'Invoice No.', style_table_header_bold) # HEADER
# worksheet.write(0, 6, 'Category', style_table_header_bold) # HEADER
worksheet.write(0, 6, 'Item Code', style_table_header_bold) # HEADER
worksheet.write(0, 7, 'Description', style_table_header_bold) # HEADER
worksheet.write(0, 8, 'Color', style_table_header_bold) # HEADER
worksheet.write(0, 9, 'Brand', style_table_header_bold) # HEADER
worksheet.write(0, 10, 'Car Brand', style_table_header_bold) # HEADER
worksheet.write(0, 11, 'Model', style_table_header_bold) # HEADER
worksheet.write(0, 12, 'Car Model Group 1', style_table_header_bold) # HEADER
worksheet.write(0, 13, 'Car Model Group 2', style_table_header_bold) # HEADER
worksheet.write(0, 14, 'Car Model Group 3', style_table_header_bold) # HEADER
worksheet.write(0, 15, 'Car Type', style_table_header_bold) # HEADER
worksheet.write(0, 16, 'Fit', style_table_header_bold) # HEADER
worksheet.write(0, 17, 'Parent Category', style_table_header_bold) # HEADER
worksheet.write(0, 18, 'Category', style_table_header_bold) # HEADER
worksheet.write(0, 19, 'Qty', style_table_header_bold) # HEADER
worksheet.write(0, 20, 'Unit Price', style_table_header_bold) # HEADER
worksheet.write(0, 21, 'Amount', style_table_header_bold) # HEADER
worksheet.write(0, 22, 'Discount (%)', style_table_header_bold) # HEADER
worksheet.write(0, 23, 'Net Sales', style_table_header_bold) # HEADER
if is_admin == True:
_logger.info("TRUE")
worksheet.write(0, 24, 'Foreign Cost', style_table_header_bold) # HEADER
worksheet.write(0, 25, 'Exchange Rate', style_table_header_bold) # HEADER
worksheet.write(0, 26, 'Local Cost', style_table_header_bold) # HEADER
worksheet.write(0, 27, 'Remarks', style_table_header_bold) # HEADER
else:
_logger.info("FALSE")
worksheet.write(0, 24, 'Remarks', style_table_header_bold) # HEADER
row_count = 1
for sale in sale_order_ids:
for line in sale.order_line:
invoice_id = ''
# for invoice in line.invoice_lines:
# invoice_date = str(invoice.date)
# invoice_no = str(invoice.name)
# invoice_id = line.mapped('invoice_lines').mapped('invoice_id')
for invoice in line.invoice_lines:
invoice_id = invoice.invoice_id.id
invoice_date = False
invoice_no = ''
if invoice_id:
invoice = request.env['account.invoice'].sudo().search([('id','=',invoice_id)])
invoice_date = invoice.date_invoice
invoice_no = invoice.number
worksheet.write(row_count, 0, sale.user_id.name or '', style_table_row)
worksheet.write(row_count, 1, sale.partner_id.name or '', style_table_row)
worksheet.write(row_count, 2, sale.partner_id.city or '', style_table_row)
worksheet.write(row_count, 3, sale.partner_id.partner_area_id.name or '', style_table_row)
worksheet.write(row_count, 4, invoice_date or '', style_table_row)
worksheet.write(row_count, 5, invoice_no or '', style_table_row)
# worksheet.write(row_count, 6, line.product_id.categ_id.name or '', style_table_row)
worksheet.write(row_count, 6, line.product_id.default_code or '', style_table_row)
worksheet.write(row_count, 7, line.product_id.name or '', style_table_row)
worksheet.write(row_count, 8, line.product_id.color_type or '', style_table_row)
worksheet.write(row_count, 9, line.product_id.brand.name or '', style_table_row)
worksheet.write(row_count, 10, line.product_id.brand_id.name or '', style_table_row)
worksheet.write(row_count, 11, line.product_model or '', style_table_row)
worksheet.write(row_count, 12, line.product_id.carmodel_group1_id.name or '', style_table_row)
worksheet.write(row_count, 13, line.product_id.carmodel_group2_id.name or '', style_table_row)
worksheet.write(row_count, 14, line.product_id.carmodel_group3_id.name or '', style_table_row)
worksheet.write(row_count, 15, line.product_id.cartype_id.name or '', style_table_row)
worksheet.write(row_count, 16, line.product_id.fit or '', style_table_row)
worksheet.write(row_count, 17, line.product_id.categ_id.parent_id.name or '', style_table_row)
worksheet.write(row_count, 18, line.product_id.categ_id.name or '', style_table_row)
worksheet.write(row_count, 19, line.product_uom_qty or '', style_table_row)
worksheet.write(row_count, 20, line.price_unit or '', style_table_row)
worksheet.write(row_count, 21, line.price_total or '', style_table_row)
# worksheet.write(row_count, 15, line.price_reduce or '', style_table_row)
worksheet.write(row_count, 22, line.discount or '', style_table_row)
worksheet.write(row_count, 23, line.price_subtotal or '', style_table_row)
if is_admin == True:
worksheet.write(row_count, 24, line.product_id.foreign_cost or '', style_table_row)
worksheet.write(row_count, 25, line.product_id.exchange_rate or '', style_table_row)
worksheet.write(row_count, 26, line.product_id.local_cost or '', style_table_row)
worksheet.write(row_count, 27, sale.note or '', style_table_row)
else:
worksheet.write(row_count, 24, sale.note or '', style_table_row)
row_count +=1
response = request.make_response(None,
headers=[('Content-Type', 'application/vnd.ms-excel'),
('Content-Disposition', 'attachment; filename=%s;'%(filename)
)])
workbook.save(response.stream)
return response
def export_xls(self, filename, title, company_id, date_from, date_to,
account_id, **kw):
company = request.env['res.company'].search([('id', '=', company_id)])
# journal = request.env['account.journal'].search([('id', '=', journal_id)])
account_ewt = request.env['account.account'].search(
[('name', '=', 'Withholding Tax Expanded')], limit=1)
# account_vendor_bill = request.env['account.invoice'].search([('journal_id.id', '=', journal_id),('state','in',('open','paid')),('date','>=',date_from),('date','<=',date_to)])
account_vendor_bill = request.env['account.move.line'].search([
('account_id.id', '=', account_id), ('date', '>=', date_from),
('date', '<=', date_to)
])
date_processed = date.today().strftime('%m-%d-%Y')
from_report_month = datetime.strptime(date_from, '%Y-%m-%d')
to_report_month = datetime.strptime(date_to, '%Y-%m-%d')
user_id = request.env.user.name
workbook = xlwt.Workbook()
worksheet = workbook.add_sheet(title)
# STYLES
style_header_bold = xlwt.easyxf(
"font: bold on;font: name Calibri;align: wrap no")
style_header_right = xlwt.easyxf(
"font: name Calibri;align: horiz right, wrap no")
style_table_header_bold = xlwt.easyxf(
"font: bold on;font: name Calibri;align: horiz centre, vert centre, wrap on;borders: top thin, bottom thin, right thin;"
)
style_table_row = xlwt.easyxf(
"font: name Calibri;align: horiz left, wrap no;borders: top thin, bottom thin, right thin;"
)
style_table_row_amount = xlwt.easyxf(
"font: name Calibri;align: horiz right, wrap no;borders: top thin, bottom thin, right thin;",
num_format_str="#,##0.00")
style_table_total = xlwt.easyxf(
"pattern: pattern solid, fore_colour pale_blue;font: bold on;font: name Calibri;align: horiz left, wrap no;borders: top thin, bottom medium, right thin;"
)
style_table_total_value = xlwt.easyxf(
"pattern: pattern solid, fore_colour pale_blue;font: bold on;font: name Calibri;align: horiz right, wrap no;borders: top thin, bottom medium, right thin;",
num_format_str="#,##0.00")
worksheet.col(0).width = 350 * 12
worksheet.col(1).width = 350 * 12
worksheet.col(2).width = 350 * 12
worksheet.col(3).width = 500 * 12
worksheet.col(4).width = 500 * 12
worksheet.col(5).width = 350 * 12
worksheet.col(7).width = 350 * 12
worksheet.col(6).width = 350 * 12
worksheet.col(8).width = 350 * 12
worksheet.col(9).width = 350 * 12
worksheet.col(10).width = 350 * 12
worksheet.col(11).width = 350 * 12
worksheet.col(12).width = 350 * 12
worksheet.col(13).width = 350 * 12
worksheet.col(14).width = 350 * 12
worksheet.col(15).width = 350 * 12
worksheet.col(16).width = 350 * 12
worksheet.col(17).width = 350 * 12
worksheet.col(18).width = 350 * 12
worksheet.col(19).width = 350 * 12
worksheet.col(20).width = 350 * 12
# TEMPLATE HEADERS
worksheet.write(0, 0, company.name, style_header_bold) # Company Name
worksheet.write(
1, 0, '%s %s %s %s %s %s' %
(company.street, company.street2, company.city,
company.state_id.name, company.zip, company.country_id.name),
style_header_bold) # Company Address
worksheet.write(2, 0, 'TIN %s' % (company.vat),
style_header_bold) # Company TIN
worksheet.write(4, 0, title, style_header_bold) # Report Title
worksheet.write(5, 0,
'%s to %s' % (from_report_month.strftime('%B %d, %Y'),
to_report_month.strftime('%B %d, %Y')),
style_header_bold) # Report Date
# TABLE HEADER
worksheet.write_merge(7, 8, 0, 0, 'REFERENCE DATE',
style_table_header_bold) # HEADER
worksheet.write_merge(7, 8, 1, 1, 'VOUCHER DATE',
style_table_header_bold) # HEADER
worksheet.write_merge(7, 8, 2, 2, 'JOURNAL TYPE',
style_table_header_bold) # HEADER
worksheet.write_merge(7, 8, 3, 3, 'REFERENCE NO.',
style_table_header_bold) # HEADER
worksheet.write_merge(7, 8, 4, 4, 'SUPPLIER NAME',
style_table_header_bold) # HEADER
worksheet.write_merge(7, 8, 5, 5, 'REGISTERED ADDRESS',
style_table_header_bold) # HEADER
worksheet.write_merge(7, 8, 6, 6, 'TIN',
style_table_header_bold) # HEADER
worksheet.write_merge(7, 7, 7, 8, 'SOURCE DOCUMENT',
style_table_header_bold) # HEADER
worksheet.write(8, 7, 'TYPE', style_table_header_bold) # HEADER
worksheet.write(8, 8, 'NUMBER', style_table_header_bold) # HEADER
worksheet.write_merge(7, 8, 9, 9, 'GROSS AMOUNT',
style_table_header_bold) # HEADER
worksheet.write_merge(7, 8, 10, 10, 'NON-VAT / EXEMPT',
style_table_header_bold) # HEADER
worksheet.write_merge(7, 8, 11, 11, 'NET OF VAT',
style_table_header_bold) # HEADER
worksheet.write_merge(7, 8, 12, 12, 'INPUT TAX (12%)',
style_table_header_bold) # HEADER
worksheet.write_merge(7, 8, 13, 13, 'INPUT TAX ALLOWED',
style_table_header_bold) # HEADER
worksheet.write_merge(7, 8, 14, 14, 'ACCOUNT TITLE',
style_table_header_bold) # HEADER
worksheet.write_merge(7, 8, 15, 15, 'PARTICULARS',
style_table_header_bold) # HEADER
worksheet.write_merge(7, 8, 16, 16, 'EXPENSE AMOUNT',
style_table_header_bold) # HEADER
worksheet.write_merge(7, 7, 17, 19, 'EXPANDED WITHOLDING TAX',
style_table_header_bold) # HEADER
worksheet.write(8, 17, 'ATC', style_table_header_bold) # HEADER
worksheet.write(8, 18, 'EWT RATE', style_table_header_bold) # HEADER
worksheet.write(8, 19, 'AMOUNT', style_table_header_bold) # HEADER
worksheet.write_merge(7, 8, 20, 20, 'EWT ABSORBED BY COMPANY',
style_table_header_bold) # HEADER
# TABLE ROW LINES
# table_row_start = 9
row_count = 9
transaction_count = 0
for account in account_vendor_bill:
source_type = ''
source_num = ''
amount_income = 0
amount_tax = 0
account_title = ''
input_tax_amount = 0
# GET SOURCES
if account.invoice_id:
source_num = account.invoice_id.number
if account.invoice_id.type == 'out_invoice':
source_type = 'Customer Invoice'
elif account.invoice_id.type == 'in_invoice':
source_type = 'Vendor Bill'
elif account.invoice_id.type == 'out_refund':
source_type = 'Customer Credit Note'
else:
source_type = 'Vendor Credit Note'
else:
# GET PAYMENT
# VENDOR PAYMENT
if 'SUPP.OUT' in account.name:
payment = request.env['account.payment'].search(
[('name', '=', account.name)], limit=1)
if payment:
source_type = 'O.R'
source_num = payment.name
if 'CUST.IN' in account.name:
payment = request.env['account.payment'].search(
[('name', '=', account.name)], limit=1)
if payment:
source_type = 'O.R'
source_num = payment.name
if payment.collection_receipt_id:
source_type = 'C.R'
if payment.acknowledgement_receipt_id:
source_type = 'C.R'
# if not payment.collection_receipt_id and not payment.acknowledgement_receipt_id or payment.collection_receipt_id and payment.acknowledgement_receipt_id:
# source_type = 'O.R'
# GET TAXES AMOUNT
for tax in account.invoice_id.tax_line_ids:
if tax.account_id == account.account_id:
amount_income = tax.base
amount_tax = tax.amount_total
else:
# if tax.amount == 12:
input_tax_amount = tax.amount_total
if amount_tax <= 0:
if account.debit > 0:
amount_tax = account.debit
else:
amount_tax = account.credit
if account.move_id:
for move in account.move_id.line_ids:
for tax in move.tax_ids:
if tax.account_id.id == account_ewt.id:
account_title = move.account_id.name
worksheet.write(row_count, 0, '', style_table_row)
worksheet.write(row_count, 1, account.date, style_table_row)
worksheet.write(row_count, 2, account.move_id.journal_id.name,
style_table_row)
worksheet.write(row_count, 3, account.move_id.name,
style_table_row)
worksheet.write(row_count, 4, account.partner_id.name,
style_table_row)
worksheet.write(
row_count, 5, '%s %s %s %s %s %s' %
(account.partner_id.street or '', account.partner_id.street2
or '', account.partner_id.city or '',
account.partner_id.state_id.name or '', account.partner_id.zip
or '', account.partner_id.country_id.name or ''),
style_table_row)
worksheet.write(row_count, 6, account.partner_id.vat or '',
style_table_row)
worksheet.write(row_count, 7, source_type, style_table_row)
worksheet.write(row_count, 8, source_num, style_table_row)
worksheet.write(row_count, 9, account.invoice_id.amount_total,
style_table_row_amount)
worksheet.write(row_count, 10, account.invoice_id.vat_exempt_sales,
style_table_row_amount)
worksheet.write(row_count, 11, account.invoice_id.vat_sales,
style_table_row_amount)
worksheet.write(row_count, 12, input_tax_amount,
style_table_row_amount)
worksheet.write(row_count, 13, '', style_table_row)
worksheet.write(row_count, 14, account_title, style_table_row)
worksheet.write(row_count, 15, account.invoice_id.x_description
or '', style_table_row_amount)
worksheet.write(row_count, 16, account.invoice_id.amount_untaxed,
style_table_row_amount)
worksheet.write(row_count, 17,
account.tax_line_id.ewt_structure_id.name or '',
style_table_row)
worksheet.write(row_count, 18, account.tax_line_id.amount,
style_table_row_amount)
worksheet.write(row_count, 19, amount_tax, style_table_row_amount)
worksheet.write(row_count, 20, '', style_table_row)
row_count += 1
transaction_count += 1
table_total_start = row_count
# TABLE TOTALS
# worksheet.write_merge(table_total_start, table_total_start, 0, 7, 'TOTAL', style_table_total)
# worksheet.write(table_total_start, 8, '-', style_table_total_value)
# worksheet.write(table_total_start, 9, '', style_table_total_value)
# worksheet.write(table_total_start, 10, '-', style_table_total_value)
# worksheet.write(table_total_start, 11, '-', style_table_total_value)
# worksheet.write(table_total_start, 12, '', style_table_total_value)
# worksheet.write(table_total_start, 13, '', style_table_total_value)
# worksheet.write(table_total_start, 14, '-', style_table_total_value)
# worksheet.write(table_total_start, 15, '', style_table_total_value)
# worksheet.write(table_total_start, 16, '', style_table_total_value)
# worksheet.write(table_total_start, 17, '-', style_table_total_value)
# worksheet.write(table_total_start, 18, '-', style_table_total_value)
worksheet.write(0, 20, 'No. of Transaction: %s' % (transaction_count),
style_header_right)
worksheet.write(1, 20, 'Date Processed: %s' % (date_processed),
style_header_right)
worksheet.write(2, 20, 'Processed By: %s' % (user_id),
style_header_right)
# worksheet.write(3, 18, '%s'%(account_invoice_payable), style_header_right)
response = request.make_response(
None,
headers=[('Content-Type', 'application/vnd.ms-excel'),
('Content-Disposition',
'attachment; filename=%s;' % (filename))])
workbook.save(response.stream)
return response
def gen_excel_file(self):
fmt_bold_left = xlwt.easyxf('font: bold on')
fmt_bold_right = xlwt.easyxf('font: bold on; align: horiz right')
fmt_right = xlwt.easyxf('align: horiz right')
fmt_currency = xlwt.easyxf(num_format_str="#,##0.00")
fmt_integer = xlwt.easyxf(num_format_str="#,##0")
for d in self:
wb = xlwt.Workbook()
ws = wb.add_sheet(str(d.year))
ws.col(0).width = 256 * 20
ws.col(1).width = 256 * 20
for i in range(2, 5):
ws.col(i).width = 256 * 10
ws.col(6).width = 256 * 20
ws.col(7).width = 256 * 20
ri = 0
ci = 0
ws.write(ri, 0, "SAVING INTEREST COMPUTATION", fmt_bold_left)
# ws.write(ri, 18, d.company_id.name, fmt_bold_left)
ri += 1
ws.write(ri, 0, "Year:", fmt_bold_left)
ws.write(ri, 1, d.year)
ri += 1
ws.write(ri, 0, "Rate %:", fmt_bold_left)
ws.write(ri, 1, d.rate, xlwt.easyxf(num_format_str="#,##0.00000"))
headers = [
["Member", fmt_bold_left],
["Account", fmt_bold_left],
["Q1 Average", fmt_bold_right],
["Q2 Average", fmt_bold_right],
["Q3 Average", fmt_bold_right],
["Q4 Average", fmt_bold_right],
["Total", fmt_bold_right],
["Int", fmt_bold_right],
]
ri += 3
ci = 0
for h in headers:
ws.write(ri, ci, h[0], h[1])
ci += 1
for ln in d.line_ids:
ri += 1
ws.write(ri, 0, ln.member_id.name)
ws.write(ri, 1, ln.account_id.name)
ws.write(ri, 2, ln.q1_average, fmt_currency)
ws.write(ri, 3, ln.q2_average, fmt_currency)
ws.write(ri, 4, ln.q3_average, fmt_currency)
ws.write(ri, 5, ln.q4_average, fmt_currency)
ws.write(ri, 6, ln.q_average_total, fmt_currency)
ws.write(ri, 7, ln.interest_amount, fmt_currency)
outputStream = StringIO()
wb.save(outputStream)
d.excel_data = base64.encodestring(outputStream.getvalue())
outputStream.close()
return
def generate_excel_report(self):
workbook = xlwt.Workbook(encoding='utf-8')
worksheet = workbook.add_sheet('Sales Report')
# worksheet.filter_column(0, 'Region == East')
#
# worksheet.filter_column('A', 'x > 2000')
lst_search = [('state', 'in', ['paid', 'invoiced', 'done'])]
start_date = self.start_date
end_date = self.end_date
if start_date:
lst_search.append(('date_order', '>=', start_date))
if end_date:
lst_search.append(('date_order', '<=', end_date))
if self.partner_id:
lst_search.append(('partner_id', '=', self.partner_id.id))
if self.user_id:
lst_search.append(('cashier_name', '=', self.user_id.id))
orders = self.env['pos.order'].search(lst_search,
order='date_order asc')
bold = xlwt.easyxf(
"pattern: pattern solid, fore-colour light_turquoise;"
"align: wrap on, horiz center, vert center;font: bold on;"
"borders: left thin, right thin, top thin, bottom thin;"
"font: name Times New Roman, color black;",
num_format_str='#,##0.00')
grey = xlwt.easyxf(
"pattern: pattern solid, fore-colour grey25;"
"align: wrap on, horiz center, vert center;"
"borders: left thin, right thin, top thin, bottom thin;"
"font: name Times New Roman, color black;",
num_format_str='#,##0.00')
grey_red = xlwt.easyxf(
"pattern: pattern solid, fore-colour grey25;"
"align: wrap on, horiz center, vert center;"
"borders: left thin, right thin, top thin, bottom thin;"
"font: name Times New Roman, color red;",
num_format_str='#,##0.00')
normal = xlwt.easyxf(
"align: wrap on, horiz center, vert center;"
"borders: left thin, right thin, top thin, bottom thin;"
"font: name Times New Roman;",
num_format_str='#,##0.00')
normal_red = xlwt.easyxf(
"align: wrap on, horiz center, vert center;"
"borders: left thin, right thin, top thin, bottom thin;"
"font: name Times New Roman, color red;",
num_format_str='#,##0.00')
normal_right = xlwt.easyxf(
"align: wrap on, horiz right, vert center;"
"borders: left thin, right thin, top thin, bottom thin;"
"font: name Times New Roman;",
num_format_str='#,##0.00')
r = 0
c = 0
if self.detailed:
data_list = []
cash = card = total = 0
output_header = [
'DATE', 'CUSTOMER', 'CONTACT NO.', 'ORDER NO.',
'SERVICES / RETAIL', 'AMOUNT', 'CATEGORY', 'SOLD STAFF',
'SERVED STAFF', 'CASH PAYMENT', 'CARD PAYMENT', 'NOTES'
]
for item in output_header:
worksheet.write(r, c, item, bold)
col = worksheet.col(c)
if c == 1:
col.width = 900 * 6
else:
col.width = 900 * 4
worksheet.row(r).height_mismatch = True
worksheet.row(r).height = 220 * 2
c += 1
for ord in orders:
data = []
date_order = ''
if ord.date_order:
date_order = datetime.strptime(ord.date_order,
'%Y-%m-%d %H:%M:%S').date()
date_order = date_order.strftime('%d/%m/%Y')
data.append(date_order)
if ord.partner_id:
data.append(ord.partner_id.name)
data.append(ord.partner_id.phone)
else:
data.append('')
data.append('')
data.append(ord.name)
data.append('')
data.append(ord.amount_total)
data.append('')
data.append('')
data.append('')
data.append(ord.cash_amt)
data.append(ord.credit_amt)
if ord.note:
data.append(ord.note)
else:
data.append('')
cash += ord.cash_amt
card += ord.credit_amt
total += ord.amount_total
data_list.append(data)
r += 1
c = 0
for item in data:
if ord.negative_entry:
worksheet.write(r, c, item, grey_red)
else:
worksheet.write(r, c, item, grey)
c += 1
for line in ord.lines:
r += 1
c = 4
worksheet.write_merge(r, r, 0, 4, line.product_id.name,
normal_right)
c += 1
worksheet.write(r, c, line.price_subtotal_incl, normal)
c += 1
if line.product_id.pos_categ_id:
worksheet.write(r, c,
line.product_id.pos_categ_id.name,
normal)
else:
worksheet.write(r, c, '', normal)
c += 1
if line.sold_staff_id:
worksheet.write(r, c, line.sold_staff_id.name, normal)
else:
worksheet.write(r, c, '', normal)
c += 1
if line.staff_assigned_id:
worksheet.write(r, c, line.staff_assigned_id.name,
normal)
else:
worksheet.write(r, c, '', normal)
c += 1
worksheet.write_merge(r, r, c, c + 2, '', normal_right)
r += 1
worksheet.write_merge(r, r, 0, 4, '', bold)
c = 5
worksheet.write(r, c, total, bold)
c += 1
worksheet.write_merge(r, r, c, c + 2, '', bold)
c += 3
worksheet.write(r, c, cash, bold)
c += 1
worksheet.write(r, c, card, bold)
c += 1
worksheet.write(r, c, '', bold)
else:
data_list = []
cash = card = total = 0
output_header = [
'DATE', 'CUSTOMER', 'CONTACT NO.', 'ORDER NO.', 'AMOUNT',
'CASH PAYMENT', 'CARD PAYMENT', 'NOTES'
]
for item in output_header:
worksheet.write(r, c, item, bold)
col = worksheet.col(c)
if c == 1:
col.width = 900 * 6
else:
col.width = 900 * 4
worksheet.row(r).height_mismatch = True
worksheet.row(r).height = 220 * 2
c += 1
for ord in orders:
data = []
date_order = ''
if ord.date_order:
date_order = datetime.strptime(ord.date_order,
'%Y-%m-%d %H:%M:%S').date()
date_order = date_order.strftime('%d/%m/%Y')
data.append(date_order)
if ord.partner_id:
data.append(ord.partner_id.name)
data.append(ord.partner_id.phone)
else:
data.append('')
data.append('')
data.append(ord.name)
data.append(ord.amount_total)
data.append(ord.cash_amt)
data.append(ord.credit_amt)
if ord.note:
data.append(ord.note)
else:
data.append('')
cash += ord.cash_amt
card += ord.credit_amt
total += ord.amount_total
data_list.append(data)
r += 1
c = 0
for item in data:
if ord.negative_entry:
worksheet.write(r, c, item, normal_red)
else:
worksheet.write(r, c, item, normal)
c += 1
r += 1
worksheet.write_merge(r, r, 0, 3, '', bold)
c = 4
worksheet.write(r, c, total, bold)
c += 1
worksheet.write(r, c, cash, bold)
c += 1
worksheet.write(r, c, card, bold)
c += 1
worksheet.write(r, c, '', bold)
c += 1
# worksheet.autofilter(0, 0, 1, 1)
# worksheet.filter_column('A', 'x > 2000')
# worksheet.filter_column('A')
# worksheet.autofilter()
buf = io.BytesIO()
workbook.save(buf)
out = base64.encodestring(buf.getvalue())
name = "Sales_report" + ".xls"
self.write({'state': 'get', 'data': out, 'name': name})
return {
'type': 'ir.actions.act_window',
'res_model': 'summary.wizard',
'view_mode': 'form',
'view_type': 'form',
'res_id': self.id,
'views': [(False, 'form')],
'target': 'new',
}
def action_print_leave_summary(self):
workbook = xlwt.Workbook()
amount_tot = 0
hr_holiday_objs_list = []
column_heading_style = easyxf(
'font:height 200;font:bold True;align: horiz left;')
worksheet = workbook.add_sheet('Leave Summary')
worksheet.write(
2, 2, self.from_date.strftime('%d-%m-%Y'),
easyxf('font:height 200;font:bold True;align: horiz center;'))
worksheet.write(
2, 3, 'To',
easyxf('font:height 200;font:bold True;align: horiz center;'))
worksheet.write(
2, 4, self.to_date.strftime('%d-%m-%Y'),
easyxf('font:height 200;font:bold True;align: horiz center;'))
worksheet.write(4, 0, _('Date'), column_heading_style)
worksheet.write(4, 1, _('No of Days'), column_heading_style)
worksheet.write(4, 2, _('Employee'), column_heading_style)
worksheet.write(4, 3, _('Department'), column_heading_style)
worksheet.write(4, 4, _('Leave Type'), column_heading_style)
worksheet.write(4, 5, _('Description'), column_heading_style)
worksheet.write(4, 6, _('End Date'), column_heading_style)
worksheet.col(0).width = 3000
worksheet.col(1).width = 3000
worksheet.col(2).width = 5000
worksheet.col(3).width = 6500
worksheet.col(4).width = 5000
worksheet.col(5).width = 6500
worksheet.col(6).width = 3000
String = self.from_date.strftime(
'%d-%m-%Y') + ' ' + ' To ' + ' ' + self.to_date.strftime(
'%d-%m-%Y')
worksheet2 = workbook.add_sheet('Employee Wise Leave Summary')
worksheet2.write_merge(
2, 2, 0, 1, String,
easyxf('font:height 200; align: horiz center;font:bold True;'))
worksheet2.write(4, 0, _('Employee'), column_heading_style)
worksheet2.write(4, 1, _('No of Leaves'), column_heading_style)
worksheet2.col(0).width = 5000
worksheet2.col(1).width = 5000
row = 5
employee_row = 5
dict = {}
for wizard in self:
employee_leave_data = {}
heading = 'Leave Summary Report'
worksheet.write_merge(
0, 0, 0, 6, heading,
easyxf(
'font:height 210; align: horiz center;pattern: pattern solid, fore_color black; font: color white; font:bold True;'
"borders: top thin,bottom thin"))
heading = 'Employee Wise Leave Summary'
worksheet2.write_merge(
0, 0, 0, 1, heading,
easyxf(
'font:height 210; align: horiz center;pattern: pattern solid, fore_color black; font: color white; font:bold True;'
"borders: top thin,bottom thin"))
if wizard.department_id:
hr_holiday_objs = self.env['hr.leave'].search([
('request_date_from', '>=', wizard.from_date),
('request_date_to', '<=', wizard.to_date),
('department_id', '=', wizard.department_id.id),
('state', '=', 'validate')
])
else:
hr_holiday_objs = self.env['hr.leave'].search([
('request_date_from', '>=', wizard.from_date),
('request_date_to', '<=', wizard.to_date),
('state', '=', 'validate')
])
for obj in hr_holiday_objs:
date_from = datetime.datetime.strptime(
obj.request_date_from.strftime('%Y-%m-%d %H:%M:%S'),
'%Y-%m-%d %H:%M:%S').strftime('%d-%m-%Y')
hr_holiday_objs_list.append({
'id': obj,
'date': date_from,
'emp': obj.employee_id.name
})
hr_holiday_objs_list = sorted(hr_holiday_objs_list,
key=itemgetter('emp'))
hr_holiday_objs_list = sorted(hr_holiday_objs_list,
key=itemgetter('date'),
reverse=True)
for dict in hr_holiday_objs_list:
for id in dict['id']:
leave_date_from = datetime.datetime.strptime(
id.request_date_from.strftime('%Y-%m-%d %H:%M:%S'),
'%Y-%m-%d %H:%M:%S').strftime('%d-%m-%Y')
leave_date_to = datetime.datetime.strptime(
id.request_date_to.strftime('%Y-%m-%d %H:%M:%S'),
'%Y-%m-%d %H:%M:%S').strftime('%d-%m-%Y')
worksheet.write(
row, 0, leave_date_from,
easyxf('font:height 200;align: horiz left;'))
worksheet.write(
row, 1, id.number_of_days,
easyxf('font:height 200;align: horiz right;'))
worksheet.write(
row, 2, id.employee_id.name,
easyxf('font:height 200;align: horiz left;'))
worksheet.write(
row, 3, id.department_id.name or '',
easyxf('font:height 200;align: horiz left;'))
worksheet.write(
row, 4, id.holiday_status_id.name,
easyxf('font:height 200;align: horiz left;'))
worksheet.write(
row, 5, id.name or '',
easyxf('font:height 200;align: horiz left;'))
worksheet.write(
row, 6, leave_date_to,
easyxf('font:height 200;align: horiz left;'))
if id.employee_id.name not in employee_leave_data:
employee_leave_data.update(
{id.employee_id.name: id.number_of_days})
else:
leave_data = employee_leave_data[
id.employee_id.name] + id.number_of_days
employee_leave_data.update(
{id.employee_id.name: leave_data})
row += 1
for employee in sorted(employee_leave_data):
worksheet2.write(employee_row, 0, employee)
worksheet2.write(employee_row, 1,
employee_leave_data[employee])
employee_row += 1
fp = io.BytesIO()
workbook.save(fp)
excel_file = base64.encodestring(fp.getvalue())
wizard.leave_summary_file = excel_file
wizard.file_name = 'Leave Summary Report.xls'
wizard.leave_report_printed = True
fp.close()
return {
'view_mode': 'form',
'res_id': wizard.id,
'res_model': 'leave.summary.report',
'view_type': 'form',
'type': 'ir.actions.act_window',
'context': self.env.context,
'target': 'new',
}
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 generate_report(self):
filename = 'Payslip Report.xls'
workbook = xlwt.Workbook(encoding="UTF-8")
worksheet = workbook.add_sheet('Payslip Report')
style = xlwt.easyxf(
'font:height 200, bold True, name Arial;align: horiz left;')
style2 = xlwt.easyxf(
'font:height 200, bold False, name Calibri; align: horiz center; borders: left thin, right thin, top thin, bottom thin;'
)
style_main_header = xlwt.easyxf(
'font: height 230, bold True, color black;\
borders: left thin, right thin, top thin, bottom thin;\
pattern: pattern solid, fore_color white; align: horizontal center, wrap on, vertical center;'
)
style_main_header_data = xlwt.easyxf(
'font: height 230, bold True, underline on, color black;\
borders: left 1, right 1, top 1, bottom 1;\
pattern: pattern solid, fore_color white; align: horizontal center, wrap on, vertical center;'
)
style_main_bottom = xlwt.easyxf(
'font: height 230, bold True, color black;\
borders: left thin, right thin, top thin, bottom thin;\
pattern: pattern solid, fore_color white; align: horizontal right, wrap on, vertical center;'
)
worksheet.row(0).height = 320
worksheet.col(0).width = 4000
worksheet.col(1).width = 5000
worksheet.col(2).width = 5000
worksheet.col(3).width = 5000
worksheet.col(4).width = 5000
worksheet.col(5).width = 5000
worksheet.col(6).width = 5000
worksheet.col(7).width = 5000
worksheet.col(8).width = 5000
worksheet.col(9).width = 5000
worksheet.col(10).width = 5000
worksheet.col(11).width = 5000
worksheet.col(12).width = 5000
borders = xlwt.Borders()
borders.bottom = xlwt.Borders.MEDIUM
border_style = xlwt.XFStyle() # Create Style
border_style.borders = borders
header_data = 'PAYROLL FOR THE MONTH OF ' + str(
self.month) + ' ' + str(date.today().year)
worksheet.write_merge(0, 0, 3, 5, 'C R A TRADING (L.L.C)',
style_main_header_data)
worksheet.write_merge(1, 1, 3, 5, '', style_main_header_data)
worksheet.write_merge(2, 2, 3, 5, header_data, style_main_header_data)
worksheet.write_merge(6, 7, 0, 0, 'SI.No', style_main_header)
worksheet.write_merge(6, 7, 1, 1, 'NAME OF THE EMPLOYEE',
style_main_header)
worksheet.write_merge(6, 7, 2, 2, 'WORK PERMIT NO (8 DIGIT NO)',
style_main_header)
worksheet.write_merge(6, 7, 3, 3, 'PERSONAL NO (14 DIGIT NO)',
style_main_header)
worksheet.write_merge(6, 7, 4, 4, 'BANK NAME', style_main_header)
worksheet.write_merge(6, 7, 5, 5, 'IBAN /RATIBI CARD NUMBER',
style_main_header)
worksheet.write_merge(6, 7, 6, 6, 'NO OF DAYS ABSENT',
style_main_header)
worksheet.write_merge(6, 6, 7, 10, 'Employees Net Salary',
style_main_header)
worksheet.write_merge(7, 7, 7, 7, 'FIXED PORTION', style_main_header)
worksheet.write_merge(7, 7, 8, 8, 'VARIABLE', style_main_header)
worksheet.write_merge(7, 7, 9, 9, 'OVER TIME', style_main_header)
worksheet.write_merge(7, 7, 10, 10, 'Total Payment', style_main_header)
# worksheet.write_merge(6, 7, 11, 11, '', style_main_header)
# worksheet.write_merge(6, 7, 12, 12, '', style_main_header)
row = 7
column = 0
payslip = self.env['hr.payslip'].search([])
fixed_portion_sum = 0
variable_sum = 0
over_time_sum = 0
total_sum = 0
count = 0
for data in payslip:
absent_days = 0
for day in data.worked_days_line_ids:
if day.work_entry_type_id.name != "Attendance":
absent_days = absent_days + day.number_of_days
if data.date_from.strftime("%b") == self.month:
row += 1
count += 1
worksheet.write(row, column, count, style2)
worksheet.write(row, column + 1, data.employee_id.name or '',
style2)
worksheet.write(row, column + 2, data.employee_id.permit_no
or '', style2)
worksheet.write(row, column + 3, data.employee_id.phone or '',
style2)
worksheet.write(
row, column + 4, data.employee_id.bank_account_id.bank_name
or '', style2)
worksheet.write(row, column + 5,
data.employee_id.iban_card_number or '',
style2)
worksheet.write(row, column + 6, absent_days or '', style2)
fixed_portion_sum = fixed_portion_sum + data.contract_id.fixed_portion
worksheet.write(row, column + 7, data.contract_id.fixed_portion
or '', style2)
variable_sum = variable_sum + data.contract_id.variable
worksheet.write(row, column + 8, data.contract_id.variable
or '', style2)
over_time_sum = over_time_sum + (data.over_time * 20)
worksheet.write(row, column + 9, data.over_time * 20 or '',
style2)
total_sum = total_sum + data.contract_id.fixed_portion + data.contract_id.variable + (
data.over_time * 20)
worksheet.write(
row, column + 10, data.contract_id.fixed_portion +
data.contract_id.variable + (data.over_time * 20) or '',
style2)
# worksheet.write(row, column + 3, data.permit_no or '', style2)
# worksheet.write(row, column + 3, data.permit_no or '', style2)
row += 1
worksheet.write_merge(row, row, 0, 6, 'Total in Dirhms',
style_main_bottom)
worksheet.write(row, 7, fixed_portion_sum, style_main_header)
worksheet.write(row, 8, variable_sum, style_main_header)
worksheet.write(row, 9, over_time_sum, style_main_header)
worksheet.write(row, 10, total_sum, style_main_header)
fp = BytesIO()
workbook.save(fp)
export_id = self.env['payslip.xls'].create({
'report_excel_file':
base64.encodestring(fp.getvalue()),
'report_file_name':
filename
})
fp.close()
return {
'view_mode': 'form',
'res_id': export_id.id,
'res_model': 'payslip.xls',
'view_type': 'form',
'type': 'ir.actions.act_window',
'context': self._context,
'target': 'new',
}
def print_reglas_salariales_report(self):
domain = [('state', '=', 'done')]
if self.date_from:
domain.append(('date_from', '>=', self.date_from))
if self.date_to:
domain.append(('date_to', '<=', self.date_to))
if self.employee_id:
domain.append(('employee_id', '=', self.employee_id.id))
if not self.employee_id and self.department_id:
employees = self.env['hr.employee'].search([
('department_id', '=', self.department_id.id)
])
domain.append(('employee_id', 'in', employees.ids))
payslips = self.env['hr.payslip'].search(domain)
rules = self.rule_ids
payslip_lines = payslips.mapped('line_ids').filtered(
lambda x: x.salary_rule_id.id in rules.ids
) #.sorted(key=lambda x: x.slip_id.employee_id)
workbook = xlwt.Workbook()
bold = xlwt.easyxf("font: bold on;")
worksheet = workbook.add_sheet('Nomina')
from_to_date = 'De %s A %s' % (self.date_from or '', self.date_to
or '')
concepto = 'Concepto: %s' % (self.date_from)
worksheet.write_merge(1, 1, 0, 4, 'Reporte de acumulados de conceptos',
bold)
worksheet.write_merge(2, 2, 0, 4, from_to_date, bold)
#worksheet.write_merge(3, 3, 0, 4, concepto, bold)
worksheet.write(4, 0, 'Empleado', bold)
col = 3
rule_index = {}
for rule in rules:
worksheet.write(4, col, rule.name, bold)
rule_index.update({rule.id: col})
col += 1
#employees = defaultdict(dict)
#employee_payslip = defaultdict(set)
employees = {}
for line in payslip_lines:
if line.slip_id.employee_id not in employees:
employees[line.slip_id.employee_id] = {line.slip_id: []}
if line.slip_id not in employees[line.slip_id.employee_id]:
employees[line.slip_id.employee_id].update({line.slip_id: []})
employees[line.slip_id.employee_id][line.slip_id].append(line)
#employees[line.slip_id.employee_id].add(line)
#employee_payslip[line.slip_id.employee_id].add(line.slip_id)
row = 5
tipo_nomina = {'O': 'Nómina ordinaria', 'E': 'Nómina extraordinaria'}
for employee, payslips in employees.items():
worksheet.write(row, 0, employee.name)
row += 1
worksheet.write(row, 1, 'Fecha de la nomina', bold)
worksheet.write(row, 2, 'Tipo', bold)
row += 1
total_by_rule = defaultdict(lambda: 0.0)
for payslip, lines in payslips.items():
#for line in lines:
worksheet.write(row, 1, payslip.date_from)
worksheet.write(row, 2,
tipo_nomina.get(payslip.tipo_nomina, ''))
for line in lines:
worksheet.write(row,
rule_index.get(line.salary_rule_id.id),
line.total)
total_by_rule[line.salary_rule_id.id] += line.total
row += 1
worksheet.write(row, 2, 'Total', bold)
for rule_id, total in total_by_rule.items():
worksheet.write(row, rule_index.get(rule_id), total)
row += 1
fp = io.BytesIO()
workbook.save(fp)
fp.seek(0)
data = fp.read()
fp.close()
self.write({'file_data': base64.b64encode(data)})
action = {
'name': 'Payslips',
'type': 'ir.actions.act_url',
'url':
"/web/content/?model=" + self._name + "&id=" + str(self.id) +
"&field=file_data&download=true&filename=Reglas_salariales.xls",
'target': 'self',
}
return action
def export_products(self):
filename = 'products_%s.xls' % (
datetime.today().strftime("%Y_%m_%d_%H_%M_%S"))
workbook = xlwt.Workbook()
bold = xlwt.easyxf("font: bold on;")
quant_obj = self.env['stock.quant']
products = self.env['product.product'].search([])
company_id = self.env.user.company_id.id
#worksheet = workbook.add_worksheet('Products')
worksheet = workbook.add_sheet('Products')
headers = [
'id', 'categ_id/name', 'pos_categ_id/name', 'available_in_pos',
'name', 'barcode', 'default_code', 'unit_of_measurement', 'type',
'route_ids/id', 'purchase_ok', 'sale_ok', 'standard_price',
'lst_price', 'seller_ids/name/name', 'image_medium'
]
warehouse_ids = []
product_obj = self.env['product.product']
product_ids = products.ids
product_inventory_by_wh = {}
for warehouse in self.env['stock.warehouse'].search([
('company_id', '=', company_id)
]):
headers.append(warehouse.code)
#For faster quantity calculation, used quary.
domain_quant_loc, domain_move_in_loc, domain_move_out_loc = product_obj._get_domain_locations_new(
[warehouse.view_location_id.id])
domain_quant = [('product_id', 'in', product_ids)
] + domain_quant_loc
query = quant_obj._where_calc(domain_quant)
from_clause, where_clause, where_clause_params = query.get_sql()
where_str = where_clause and (" WHERE %s" % where_clause) or ''
query_str = 'SELECT product_id, sum(quantity) as quantity FROM ' + from_clause + where_str + ' group by product_id'
self._cr.execute(query_str, where_clause_params)
res = dict(self._cr.fetchall())
product_inventory_by_wh.update({warehouse.id: res})
warehouse_ids.append(warehouse.id)
product_xml_ids = dict(self.__ensure_xml_id_custom(products))
sellers_mapping_dict = {}
for i, header in enumerate(headers):
worksheet.write(0, i, header, bold)
worksheet.col(i).width = 8000 # around 220 pixels
def splittor(rs):
""" Splits the self recordset in batches of 1000 (to avoid
entire-recordset-prefetch-effects) & removes the previous batch
from the cache after it's been iterated in full
"""
for idx in range(0, len(rs), 1000):
sub = rs[idx:idx + 1000]
for rec in sub:
yield rec
rs.invalidate_cache(ids=sub.ids)
row_index = 1
pos_installed = hasattr(self.env['product.product'],
'available_in_pos')
for product in splittor(products):
if product.route_ids:
xml_ids = [
xid for _, xid in self.__ensure_xml_id_custom(
product.route_ids)
]
route_ids = ','.join(xml_ids) or False
else:
route_ids = ''
i = 0
worksheet.write(row_index, i, product_xml_ids.get(product.id))
i += 1
worksheet.write(row_index, i, product.categ_id.complete_name)
i += 1
#########
if pos_installed:
worksheet.write(
row_index, i, product.pos_categ_id.complete_categ_name
or None)
i += 1
worksheet.write(row_index, i,
1 if product.available_in_pos else 0)
i += 1
else:
worksheet.write(row_index, i, None)
i += 1
worksheet.write(row_index, i, None)
i += 1
#########
worksheet.write(row_index, i, product.name)
i += 1
worksheet.write(row_index, i, product.barcode or '')
i += 1
worksheet.write(row_index, i, product.default_code or '')
i += 1
worksheet.write(row_index, i, product.uom_id.name)
i += 1
worksheet.write(row_index, i, product.type)
i += 1
worksheet.write(row_index, i, route_ids)
i += 1
worksheet.write(row_index, i, product.purchase_ok)
i += 1
worksheet.write(row_index, i, product.sale_ok)
i += 1
worksheet.write(row_index, i, product.standard_price)
i += 1
worksheet.write(row_index, i, product.lst_price)
i += 1
seller_xml_ids = []
for seller in product.seller_ids.mapped('name'):
if seller.id not in sellers_mapping_dict:
xml_rec = self.__ensure_xml_id_custom(seller)
if xml_rec:
sellers_mapping_dict.update(
{seller.id: xml_rec and xml_rec[0][1] or False})
seller_xml_ids.append(
sellers_mapping_dict.get(seller.id) or '')
worksheet.write(row_index, i, ','.join(seller_xml_ids))
i += 1
worksheet.write(row_index, i, None)
i += 1
for warehouse_id in warehouse_ids:
worksheet.write(
row_index, i,
product_inventory_by_wh[warehouse_id].get(product.id, 0.0))
#worksheet.write(row_index, i, product.with_context(warehouse=warehouse_id).qty_available)
i += 1
row_index += 1
# workbook.close()
# fp.seek(0)
# data = fp.read()
# fp.close()
fp = io.BytesIO()
workbook.save(fp)
fp.seek(0)
data = fp.read()
fp.close()
self.write({'file_data': base64.b64encode(data)})
return {
'type':
'ir.actions.act_url',
'url':
'/web/binary/savefile_custom?model=%s&field=file_data&id=%s&file_name=%s&content_type="application/vnd.ms-excel"'
% (self._name, self.id, filename),
'target':
'self',
}
def _action_imprimir_excel(self, ):
workbook = xlwt.Workbook()
column_heading_style = easyxf('font:height 200;font:bold True;')
worksheet = workbook.add_sheet('Kardex report')
#Ponemos los primeros encabezados
worksheet.write(0, 0, _('Kardex report product'), column_heading_style)
query_rorte = """
select Max(id) as id from kardex_productos_mov
"""
self.env.cr.execute(query_rorte, )
tr = self.env.cr.dictfetchall()
for tr_t in tr:
todo_reporte = self.env['kardex.productos.mov'].search([
('id', '=', int(tr_t['id']))
])
tf = 0
for todfact in todo_reporte:
worksheet.write(1, 0, "Date from:", column_heading_style)
worksheet.write(1, 1, todfact.date_from)
worksheet.write(2, 0, "Date to:", column_heading_style)
worksheet.write(2, 1, todfact.date_to)
worksheet.write(1, 2, "Product:", column_heading_style)
worksheet.write(1, 3, todfact.product.name)
worksheet.write(2, 2, "Current Company:", column_heading_style)
worksheet.write(2, 3, todfact.company.name)
worksheet.write(1, 4, "Location:", column_heading_style)
ubi_hijo = todfact.ubicacion.name
ubi_pad = todfact.ubicacion.location_id.name
worksheet.write(1, 5, str(ubi_pad) + "/" + str(ubi_hijo))
#Ponemos los primeros encabezados del detalle
worksheet.write(4, 0, _('Date'), column_heading_style)
worksheet.write(4, 1, _('Concept'), column_heading_style)
worksheet.write(4, 2, _('U_In'), column_heading_style)
worksheet.write(4, 3, _('U_Out'), column_heading_style)
worksheet.write(4, 4, _('U_balance'), column_heading_style)
worksheet.write(4, 5, _("Costo_Uni"), column_heading_style)
worksheet.write(4, 6, _('V_In'), column_heading_style)
worksheet.write(4, 7, _('V_Out'), column_heading_style)
worksheet.write(4, 8, _('V_balance'), column_heading_style)
worksheet.write(4, 9, _('Costo_Prom'), column_heading_style)
worksheet.write(4, 10, _('Origin'), column_heading_style)
worksheet.write(4, 11, _('Pickin'), column_heading_style)
worksheet.write(4, 12, _('Invoice'), column_heading_style)
worksheet.write(4, 13, _('Inventory'), column_heading_style)
heading = "Product Kardex Detail"
# worksheet.write_merge(5, 0, 5,13, heading, easyxf('font:height 200; align: horiz center;pattern: pattern solid, fore_color black; font: color white; font:bold True;' "borders: top thin,bottom thin"))
# Se tiene que hacer de ultimo para saber cuanto mide todo
# se recorre el reporte
todo_reporte = self.env['kardex.productos.mov.detalle'].search([
('obj_kardex_mostrarmovi', '=', int(tr_t['id']))
])
tf = 0
for todfact in todo_reporte:
tf += 1
ini = 5
worksheet.write(tf + ini, 0, todfact.date)
worksheet.write(tf + ini, 1, todfact.concepto)
worksheet.write(tf + ini, 2, todfact.u_entrada)
worksheet.write(tf + ini, 3, todfact.u_salida)
worksheet.write(tf + ini, 4, todfact.u_saldo)
worksheet.write(tf + ini, 5, todfact.costo_unit)
worksheet.write(tf + ini, 6, todfact.v_entrada)
worksheet.write(tf + ini, 7, todfact.v_salida)
worksheet.write(tf + ini, 8, todfact.v_saldo)
worksheet.write(tf + ini, 9, todfact.costo_prom)
worksheet.write(tf + ini, 10, todfact.origin)
worksheet.write(tf + ini, 11, todfact.picking_id.name)
worksheet.write(tf + ini, 12, todfact.account_invoice.number)
worksheet.write(tf + ini, 13, todfact.inventario.name)
fp = io.BytesIO()
workbook.save(fp)
excel_file = base64.encodestring(fp.getvalue())
self.excel_binary = excel_file
nombre_tabla = "Kardex Report.xls"
self.file_name = nombre_tabla
fp.close()
def action_print_payment_summary(self, ):
workbook = xlwt.Workbook()
column_heading_style = easyxf('font:height 200;font:bold True;')
worksheet = workbook.add_sheet('Payment Summary')
worksheet.write(1, 0, _('Invoice Number'), column_heading_style)
worksheet.write(1, 1, _('Customer'), column_heading_style)
worksheet.write(1, 2, _('Invoice Date'), column_heading_style)
worksheet.write(1, 3, _('Invoice Amount'), column_heading_style)
worksheet.write(1, 4, _('Invoice Currency'), column_heading_style)
worksheet.write(1, 5, _('Paid Amount'), column_heading_style)
worksheet.col(0).width = 5000
worksheet.col(1).width = 5000
worksheet.col(2).width = 5000
worksheet.col(3).width = 5000
worksheet.col(4).width = 5000
worksheet.col(5).height = 5000
worksheet2 = workbook.add_sheet('Customer wise Payment Summary')
worksheet2.write(1, 0, _('Customer'), column_heading_style)
worksheet2.write(1, 1, _('Paid Amount'), column_heading_style)
worksheet2.col(0).width = 5000
worksheet2.col(1).width = 5000
row = 2
customer_row = 2
for wizard in self:
heading = 'Payment Summary Report (' + str(
wizard.currency_id.name) + ')'
worksheet.write_merge(
0, 0, 0, 5, heading,
easyxf(
'font:height 200; align: horiz center;pattern: pattern solid, fore_color black; font: color white; font:bold True;'
"borders: top thin,bottom thin"))
customer_payment_data = {}
invoice_objs = self.env['account.invoice'].search([
('date_invoice', '>=', wizard.from_date),
('date_invoice', '<=', wizard.to_date),
('payment_ids', '!=', False)
])
for invoice in invoice_objs:
worksheet.write(row, 0, invoice.number)
worksheet.write(row, 1, invoice.partner_id.name)
worksheet.write(row, 2, invoice.date_invoice)
worksheet.write(row, 3, invoice.amount_total)
worksheet.write(row, 4, invoice.currency_id.symbol)
paid_amount = 0
for payment in invoice.payment_ids:
if payment.state != 'draft' and payment.currency_id == wizard.currency_id:
paid_amount += payment.amount
worksheet.write(row, 5, paid_amount)
if invoice.partner_id.name not in customer_payment_data:
customer_payment_data.update(
{invoice.partner_id.name: paid_amount})
else:
paid_amount_data = customer_payment_data[
invoice.partner_id.name] + paid_amount
customer_payment_data.update(
{invoice.partner_id.name: paid_amount_data})
row += 1
for customer in customer_payment_data:
worksheet2.write(customer_row, 0, customer)
worksheet2.write(customer_row, 1,
customer_payment_data[customer])
customer_row += 1
# worksheet.write(row, 5, invoice.symbol)
fp = StringIO()
workbook.save(fp)
excel_file = base64.encodestring(fp.getvalue())
wizard.payment_summary_file = excel_file
wizard.file_name = 'Payment Summary Report.xls'
wizard.payment_report_printed = True
fp.close()
return {
'view_mode': 'form',
'res_id': wizard.id,
'res_model': 'print.payment.summary',
'view_type': 'form',
'type': 'ir.actions.act_window',
'context': self.env.context,
'target': 'new',
}
def generate_backlog_excel_report(self):
workbook = xlwt.Workbook(encoding='utf-8')
worksheet = workbook.add_sheet('Claim Report')
print("\n\n\n\n Workbook Created", workbook)
# Add a font format to use to highlight cells.
# bold = workbook.add_format({'bold':True,'size': 10})
# normal = workbook.add_format({'size': 10})
# workbook = xlsxwriter.Workbook('/tmp/abc.xlsx')
# ('/tmp/%s.xlsx'%self.from_date)
# worksheet = workbook.add_worksheet()
bold = xlwt.easyxf("font: bold on;")
normal = xlwt.easyxf()
r = 0
c = 0
data_list = []
output_header = [
'Patient Name', 'MEM ID', 'Date of Birth', 'Nationality',
'Country of Residence', 'Sex', 'Mobile', 'Invoice Number',
'Healthcare Professional', 'Healthcare Professional ID',
'Healthcare Professional Type', 'Episode Number', 'Doctor',
'Appointment Start', 'Appointment End', 'State', 'Treatment',
'Treatment Code', 'Treatment price', 'Treatment group discount(%)',
'Co-payment', 'Amount paid by patient'
]
for item in output_header:
worksheet.write(r, c, item, bold)
c += 1
inv_data = self.env['account.invoice'].search \
([('date_invoice', '>=', self.from_date), ('date_invoice', '<=', self.to_date), ('patient','!=',False)])
# data = []
for inv in inv_data:
# data = []
# data.append(inv.patient.name.name)
# if inv.patient.name.ref:
# data.append(inv.patient.name.ref)
# else:
# data.append('')
# if inv.patient.dob:
# data.append(inv.patient.dob)
# else:
# data.append('')
# if inv.patient.nationality_id:
# data.append(inv.patient.nationality_id.name)
# else:
# data.append('')
# if inv.patient.name.country_id:
# data.append(inv.patient.name.country_id.name)
# else:
# data.append('')
# if inv.patient.sex:
# if inv.patient.sex == 'm':
# data.append('Male')
# else:
# data.append('Female')
# else:
# data.append('')
# if inv.patient.mobile:
# data.append(inv.patient.mobile)
# else:
# data.append('')
# if inv.number:
# data.append(inv.number)
# else:
# data.append('')
# if inv.dentist:
# data.append(inv.dentist.name.name)
# else:
# data.append('')
# if inv.dentist.code:
# data.append(inv.dentist.code)
# else:
# data.append('')
# if inv.dentist.speciality:
# data.append(inv.dentist.speciality.name)
# else:
# data.append('')
if inv.patient.apt_id and inv.insurance_company == self.company:
for apt in inv.patient.apt_id:
data = []
data.append(inv.patient.name.name)
if inv.patient.name.ref:
data.append(inv.patient.name.ref)
else:
data.append('')
if inv.patient.dob:
data.append(inv.patient.dob)
else:
data.append('')
if inv.patient.nationality_id:
data.append(inv.patient.nationality_id.name)
else:
data.append('')
if inv.patient.name.country_id:
data.append(inv.patient.name.country_id.name)
else:
data.append('')
if inv.patient.sex:
if inv.patient.sex == 'm':
data.append('Male')
else:
data.append('Female')
else:
data.append('')
if inv.patient.mobile:
data.append(inv.patient.mobile)
else:
data.append('')
if inv.number:
data.append(inv.number)
else:
data.append('')
if inv.dentist:
data.append(inv.dentist.name.name)
else:
data.append('')
if inv.dentist.code:
data.append(inv.dentist.code)
else:
data.append('')
if inv.dentist.speciality:
data.append(inv.dentist.speciality.name)
else:
data.append('')
if apt:
data.append(apt.name)
if apt.doctor:
data.append(apt.doctor.name.name)
else:
data.append(' ')
if apt.appointment_sdate:
data.append(apt.appointment_sdate)
else:
data.append(' ')
if apt.appointment_edate:
data.append(apt.appointment_edate)
else:
data.append(' ')
if apt.state:
data.append(apt.state)
else:
data.append('')
else:
data.append('')
data.append('')
data.append('')
data.append('')
data.append('')
if inv.invoice_line_ids:
product = []
p_code = []
for line in inv.invoice_line_ids:
product.append(line.product_id.name)
if line.product_id.default_code:
p_code.append(str(
line.product_id.default_code))
else:
p_code.append('')
data.append(line.product_id.name or '')
data.append(line.product_id.default_code or '')
data.append(line.price_unit)
data.append(line.discount_amt)
data.append(line.amt_paid_by_patient)
data.append(line.price_subtotal)
if product:
product = ','.join(product)
else:
product = ''
if p_code:
p_code = ','.join(p_code)
else:
p_code = ''
# data.append(product)
# data.append(p_code)
else:
data.append('')
data_list.append(data)
# print("\n\n\n\n\n",data)
# data_list = [['1','2'],['1','GFHC'],['22','adygb']]
r += 1
for data in data_list:
c = 0
for item in data:
worksheet.write(r, c, item, normal)
c += 1
r += 1
buf = io.BytesIO()
workbook.save(buf)
out = base64.encodestring(buf.getvalue())
# workbook.close()
# data = base64.b64encode(open('/tmp/abc.xlsx','rb').read())
# self.file = data
# self.file_name = 'demo.xlsx'
#
# return {
# 'type': 'ir.actions.report.xml',
# 'report_type': 'controller',
# 'report_file': '/web/content/dental.claim.wizard/%s/data/%s.csv?download=true' % (self.id, 'file'),
# }
name = "claim_report.xls"
self.write({'state': 'get', 'data': out, 'name': name})
return {
'type': 'ir.actions.act_window',
'res_model': 'dental.claim.wizard',
'view_mode': 'form',
'view_type': 'form',
'res_id': self.id,
'views': [(False, 'form')],
'target': 'new',
}
def generate_excel(self):
filename = 'Sale Invoice Export.xls'
workbook = xlwt.Workbook(encoding="UTF-8")
worksheet = workbook.add_sheet('Sale Invoice Header')
worksheet1 = workbook.add_sheet('Sale Invoice Detail')
worksheet2 = workbook.add_sheet('Payment Term')
style = xlwt.easyxf(
'font:height 200, bold True, name Arial;align: horiz center;')
date_format = xlwt.XFStyle()
date_format.num_format_str = 'dd/mm/yyyy'
worksheet.col(0).width = 4000
worksheet.col(1).width = 4000
worksheet.col(2).width = 4000
worksheet.col(3).width = 4000
worksheet.col(4).width = 4000
worksheet.col(5).width = 4000
worksheet.col(6).width = 4000
worksheet.col(7).width = 3000
worksheet.col(8).width = 3000
worksheet.col(9).width = 3000
worksheet1.col(0).width = 4000
worksheet1.col(1).width = 6000
worksheet1.col(2).width = 4000
worksheet1.col(3).width = 4000
worksheet1.col(4).width = 4000
worksheet1.col(5).width = 4000
worksheet1.col(6).width = 4000
worksheet1.col(7).width = 3000
worksheet1.col(8).width = 3000
worksheet1.col(9).width = 3000
worksheet2.col(0).width = 4000
worksheet2.col(1).width = 6000
worksheet2.col(2).width = 4000
worksheet2.col(3).width = 4000
worksheet2.col(4).width = 8000
worksheet.write(0, 0, "Customer", style)
worksheet.write(0, 1, "Invoice date", style)
worksheet.write(0, 2, "Invoice Number", style)
worksheet.write(0, 3, "Site/Company", style)
worksheet.write(0, 4, "Sales Person", style)
worksheet.write(0, 5, "Due date", style)
worksheet.write(0, 6, "Source Document", style)
worksheet.write(0, 7, "Tax Ex Amt", style)
worksheet.write(0, 8, "Tax", style)
worksheet.write(0, 9, "Total", style)
row0 = 1
column0 = 0
worksheet.write(row0, column0, self.partner_id.name)
worksheet.write(row0, column0 + 1, self.date_invoice, date_format)
worksheet.write(row0, column0 + 2, self.number)
worksheet.write(row0, column0 + 3, self.company_id.name)
worksheet.write(row0, column0 + 4, self.user_id.name)
worksheet.write(row0, column0 + 5, self.date_due, date_format)
worksheet.write(row0, column0 + 6, self.origin)
worksheet.write(row0, column0 + 7, self.amount_total)
worksheet.write(row0, column0 + 8, self.amount_tax)
worksheet.write(row0, column0 + 9, self.amount_total)
worksheet1.write(0, 0, 'Customer:', style)
worksheet1.write(0, 1, 'Invoice Date:', style)
worksheet1.write(0, 2, 'Site/Compnay:', style)
worksheet1.write(0, 3, 'Invoice Number:', style)
worksheet1.write(0, 4, "Product", style)
worksheet1.write(0, 5, "Product description", style)
worksheet1.write(0, 6, "Account", style)
worksheet1.write(0, 7, "Qty", style)
worksheet1.write(0, 8, "Tax Ex Amt", style)
worksheet1.write(0, 9, "Tax", style)
worksheet1.write(0, 10, "Total", style)
row1 = 1
column1 = 0
for invoice in self.invoice_line_ids:
worksheet1.write(row1, column1, self.partner_id.name)
worksheet1.write(row1, column1 + 1, self.date_invoice, date_format)
worksheet1.write(row1, column1 + 2, self.company_id.name)
worksheet1.write(row1, column1 + 3, self.number)
if invoice.product_id:
worksheet1.write(row1, column1 + 4, invoice.product_id.name)
if invoice.name:
worksheet1.write(row1, column1 + 5, invoice.name)
if invoice.account_id:
worksheet1.write(row1, column1 + 6, invoice.account_id.name)
if invoice.quantity:
worksheet1.write(row1, column1 + 7, invoice.quantity)
if invoice.price_subtotal:
worksheet1.write(row1, column1 + 8, invoice.price_subtotal)
if invoice.price_total:
worksheet1.write(row1, column1 + 9,
invoice.price_total - invoice.price_subtotal)
if invoice.price_total:
worksheet1.write(row1, column1 + 10, invoice.price_total)
row1 += 1
worksheet2.write(0, 0, "Customer", style)
worksheet2.write(0, 1, "Invoice date", style)
worksheet2.write(0, 2, "Invoice Number", style)
worksheet2.write(0, 3, "Due date", style)
worksheet2.write(0, 4, "Payment Terms", style)
row2 = 1
column2 = 0
worksheet2.write(row2, column2, self.partner_id.name)
worksheet2.write(row2, column2 + 1, self.date_invoice, date_format)
worksheet2.write(row2, column2 + 2, self.number)
worksheet2.write(row2, column2 + 3, self.date_due, date_format)
worksheet2.write(row2, column2 + 4, self.payment_term_id.name)
fp = BytesIO()
workbook.save(fp)
export_id = self.env['sale.invoice.excel.report.wiz'].create({
'report_excel_file':
base64.encodestring(fp.getvalue()),
'report_file_name':
filename
})
fp.close()
return {
'view_mode': 'form',
'res_id': export_id.id,
'res_model': 'sale.invoice.excel.report.wiz',
'view_type': 'form',
'type': 'ir.actions.act_window',
'context': self._context,
'target': 'new',
}
def set_export_student(self):
# Nom du fichier de sortie
filename = str(self.file_name) + '.xls'
# Style du tableau Excel
workbook = xlwt.Workbook()
worksheet = workbook.add_sheet('Export élèves')
font = xlwt.Font()
font.bold = True
for_left = xlwt.easyxf(
"font: bold 1, color black; borders: top double, bottom double, left double, right double; align: horiz left"
)
for_left_not_bold = xlwt.easyxf("font: color black; align: horiz left")
for_center_bold = xlwt.easyxf(
"font: bold 1, color black; align: horiz center")
style = xlwt.easyxf(
'font:height 400, bold True, name Arial; align: horiz center, vert center;borders: top medium,right medium,bottom medium,left medium'
)
alignment = xlwt.Alignment() # Create Alignment
alignment.horz = xlwt.Alignment.HORZ_RIGHT
style = xlwt.easyxf('align: wrap yes')
style.num_format_str = '0.00'
worksheet.row(0).height = 500
worksheet.col(0).width = 4000
worksheet.col(1).width = 4000
borders = xlwt.Borders()
borders.bottom = xlwt.Borders.MEDIUM
border_style = xlwt.XFStyle() # Create Style
border_style.borders = borders
# Traitement des données pour export
row = 0
for student in self.student_ids.sorted(
key=lambda x: x.partner_id.name):
column = 0
# Nom et prénom de ou des enfants selectionnés obligatoire à la génération du fichier
if row == 0:
worksheet.write(row, column, 'Enfant - Nom et prénom',
for_left)
worksheet.write(row + 1, column, student.partner_id.name,
for_left_not_bold)
column += 1
# Informations des élèves selectionnés
if self.student_contact_information:
school_birthdate = datetime.strptime(
student.school_birthdate, "%Y-%m-%d").strftime("%d/%m/%Y")
if row == 0:
column_libelle = column
libelles = ('Enfant - Adresse',
'Enfant - Date de naissance',
'Enfant - Lieu de naissance')
for libelle in libelles:
worksheet.write(row, column_libelle, libelle, for_left)
column_libelle += 1
values_list = (student.partner_id.street, school_birthdate,
student.school_place_of_birth.name)
for value_list in values_list:
if not value_list:
value_list = ""
worksheet.write(row + 1, column, value_list,
for_left_not_bold)
column += 1
# Informations sur les inscriptions scolaires des élèves selectionnés
if self.student_school_information:
school_registration = datetime.strptime(
student.school_registration,
"%Y-%m-%d").strftime("%d/%m/%Y")
school_end_date = datetime.strptime(
student.school_end_date, "%Y-%m-%d").strftime("%d/%m/%Y")
if row == 0:
column_libelle = column
libelles = ('École - Début', 'École - Fin',
'Établissement', 'Niveau', 'Onde',
'Resp. - Nom et prénom', 'Resp. - Tél. 1',
'Resp. - Tél. 2')
for libelle in libelles:
worksheet.write(row, column_libelle, libelle, for_left)
column_libelle += 1
values_list = (school_registration, school_end_date,
student.school_name_id.name,
student.school_level_id.name,
student.school_send_onde,
student.school_responsible_partner.name,
student.school_responsible_partner.phone,
student.school_responsible_partner.mobile)
for value_list in values_list:
if not value_list:
value_list = ""
worksheet.write(row + 1, column, value_list,
for_left_not_bold)
column += 1
# Informations détaillées sur les inscriptions scolaires des élèves selectionnés
if self.student_school_information_details:
if row == 0:
column_libelle = column
libelles = ('École - Commentaire', 'École - Dérogation',
'Dérogation - Motif', 'Dérogation - Statut')
for libelle in libelles:
worksheet.write(row, column_libelle, libelle, for_left)
column_libelle += 1
values_list = (student.school_text, student.school_derogation,
student.school_derogation_reason,
student.school_derogation_state)
for value_list in values_list:
if not value_list:
value_list = ""
worksheet.write(row + 1, column, value_list,
for_left_not_bold)
column += 1
# Informations sur le responsable - inscription scolaire
if self.responsible_school_contact_information:
if row == 0:
column_libelle = column
libelles = ('Resp. - Adresse', 'Resp. - Email')
for libelle in libelles:
worksheet.write(row, column_libelle, libelle, for_left)
column_libelle += 1
values_list = (student.school_responsible_partner.street,
student.school_responsible_partner.email)
for value_list in values_list:
if not value_list:
value_list = ""
worksheet.write(row + 1, column, value_list,
for_left_not_bold)
column += 1
# Informations sur les inscriptions restauration scolaire des élèves selectionnés
if self.student_school_catering_information:
if student.half_pension:
half_pension_begin_date = datetime.strptime(
student.half_pension_begin_date,
"%Y-%m-%d").strftime("%d/%m/%Y")
half_pension_end_date = datetime.strptime(
student.half_pension_end_date,
"%Y-%m-%d").strftime("%d/%m/%Y")
else:
half_pension_begin_date = ""
half_pension_end_date = ""
if row == 0:
column_libelle = column
libelles = ('Rest. - Inscrit', 'Rest. - Lieu',
'Rest.- Début', 'Rest. - Fin',
'Spécifications', 'Resp. - Nom et prénom',
'Resp. - Tél. 1', 'Res. - Tél. 2')
for libelle in libelles:
worksheet.write(row, column_libelle, libelle, for_left)
column_libelle += 1
values_list = (student.half_pension,
student.half_pension_id.name,
half_pension_begin_date, half_pension_end_date,
student.half_pension_specification,
student.half_pension_responsible_partner.name,
student.half_pension_responsible_partner.phone,
student.half_pension_responsible_partner.mobile)
for value_list in values_list:
if not value_list:
value_list = ""
worksheet.write(row + 1, column, value_list,
for_left_not_bold)
column += 1
# Informations détaillées sur les inscriptions restauration scolaires des élèves selectionnés
if self.student_school_catering_information_details:
if row == 0:
column_libelle = column
libelles = ('Lundi', 'Mardi', 'Jeudi', 'Vendredi',
'Occasionnelle', 'Rest.- Commentaires')
for libelle in libelles:
worksheet.write(row, column_libelle, libelle, for_left)
column_libelle += 1
values_list = (student.half_pension_monday,
student.half_pension_tuesday,
student.half_pension_thursday,
student.half_pension_friday,
student.half_pension_occasional,
student.half_pension_text)
for value_list in values_list:
if not value_list:
value_list = ""
worksheet.write(row + 1, column, value_list,
for_left_not_bold)
column += 1
# Informations sur le responsable - restauration scolaire
if self.responsible_school_catering_contact_information:
if row == 0:
column_libelle = column
libelles = ('Resp. Rest. - Adresse', 'Resp. Rest. - Email')
for libelle in libelles:
worksheet.write(row, column_libelle, libelle, for_left)
column_libelle += 1
values_list = (student.half_pension_responsible_partner.street,
student.half_pension_responsible_partner.email)
for value_list in values_list:
if not value_list:
value_list = ""
worksheet.write(row + 1, column, value_list,
for_left_not_bold)
column += 1
# Informations sur les inscriptions garderie des élèves selectionnés
if self.student_nursery_information:
if student.nursery_morning or student.nursery_evening:
nursery_begin_date = datetime.strptime(
student.nursery_begin_date,
"%Y-%m-%d").strftime("%d/%m/%Y")
nursery_end_date = datetime.strptime(
student.nursery_end_date,
"%Y-%m-%d").strftime("%d/%m/%Y")
else:
nursery_begin_date = ""
nursery_end_date = ""
if row == 0:
column_libelle = column
libelles = ('Gard. - Lieu', 'Gard. - Début', 'Gard. - Fin',
'Resp. - Nom et prénom', 'Resp. - Tél. 1',
'Resp. - Tél. 2')
for libelle in libelles:
worksheet.write(row, column_libelle, libelle, for_left)
column_libelle += 1
values_list = (student.nursery_name_id.name,
nursery_begin_date, nursery_end_date,
student.nursery_responsible_partner.name,
student.nursery_responsible_partner.phone,
student.nursery_responsible_partner.mobile)
for value_list in values_list:
if not value_list:
value_list = ""
worksheet.write(row + 1, column, value_list,
for_left_not_bold)
column += 1
# Informations détaillées sur les inscriptions garderie des élèves selectionnés
if self.student_nursery_information_details:
if row == 0:
column_libelle = column
worksheet.write(row, column_libelle,
'Gard. - Commentaires', for_left)
column_libelle += 1
nursery_text = student.nursery_text
if not nursery_text:
nursery_text = ""
worksheet.write(row + 1, column, nursery_text,
for_left_not_bold)
column += 1
# Informations sur le responsable - garderie
if self.responsible_nursery_contact_information:
if row == 0:
column_libelle = column
libelles = ('Resp. Gard. - Adresse', 'Resp. Gard. - Email')
for libelle in libelles:
worksheet.write(row, column_libelle, libelle, for_left)
column_libelle += 1
values_list = (student.nursery_responsible_partner.street,
student.nursery_responsible_partner.email)
for value_list in values_list:
if not value_list:
value_list = ""
worksheet.write(row + 1, column, value_list,
for_left_not_bold)
column += 1
row += 1
fp = io.BytesIO()
# Sauvegarde des fichiers apres écriture (write)
workbook.save(fp)
# Stockage sous forme binaire du fichier dans la base Odoo (table export.student)
export_student_id = self.env['export.student'].create({
'excel_file':
base64.encodestring(fp.getvalue()),
'file_name':
filename
})
fp.close()
# Retourne le même formulaire avec le fichier Excel récupéré
return {
'view_mode': 'form',
'res_id': export_student_id.id,
'res_model': 'export.student',
'view_type': 'form',
'type': 'ir.actions.act_window',
'context': self._context,
'target': 'new',
}
def new_workbook(self):
workbook = xlwt.Workbook()
return workbook
def _print_exp_report(self, data):
res = {}
period_length = data['form']['period_length']
if period_length <= 0:
raise except_orm(_('User Error!'),
_('You must set a period length greater than 0.'))
if not data['form']['date_from']:
raise except_orm(_('User Error!'), _('You must set a start date.'))
start = datetime.strptime(data['form']['date_from'], "%Y-%m-%d")
for i in range(7)[::-1]:
stop = start - relativedelta(days=period_length)
res[str(i)] = {
'name': (i != 0 and (str(
(7 - (i + 1)) * period_length) + '-' + str(
(7 - i) * period_length))
or ('+' + str(6 * period_length))),
'stop':
start.strftime('%Y-%m-%d'),
'start': (i != 0 and stop.strftime('%Y-%m-%d') or False),
}
start = stop - relativedelta(days=1)
data['form'].update(res)
import base64
filename = 'Inventory Ageing Report.xls'
workbook = xlwt.Workbook()
worksheet = workbook.add_sheet('Inventory Ageing Report')
header_style = easyxf(
'font:height 200;pattern: pattern solid, fore_color black; align: horiz center;font: color white; font:bold True;'
"borders: top thin,left thin,right thin,bottom thin")
text_left = easyxf('font:height 200; align: horiz left;'
"borders: top thin,bottom thin")
text_center = easyxf('font:height 200; align: horiz center;'
"borders: top thin,bottom thin")
text_right = easyxf('font:height 200; align: horiz right;'
"borders: top thin,bottom thin")
first_col = worksheet.col(0)
second_col = worksheet.col(1)
third_col = worksheet.col(2)
four_col = worksheet.col(3)
five_col = worksheet.col(4)
six_col = worksheet.col(5)
seven_col = worksheet.col(6)
eight_col = worksheet.col(7)
nine_col = worksheet.col(8)
ten_col = worksheet.col(9)
eleven_col = worksheet.col(10)
first_col.width = 130 * 30
second_col.width = 150 * 30
third_col.width = 180 * 30
four_col.width = 130 * 30
five_col.width = 150 * 30
six_col.width = 130 * 30
seven_col.width = 130 * 30
eight_col.width = 130 * 30
nine_col.width = 130 * 30
ten_col.width = 130 * 30
eleven_col.width = 130 * 30
worksheet.write_merge(
0, 1, 0, 3, 'Inventory Ageing Report',
easyxf('font:height 400; align: horiz center;font:bold True;'
"borders: top thin,bottom thin , left thin, right thin"))
date_from = data['form']['date_from'] or ' '
if date_from:
date = datetime.strptime(date_from.split(' ')[0], '%Y-%m-%d')
date_from = date.strftime('%d-%m-%Y')
worksheet.write(2, 4, 'Start Date' + '-' + str(date_from))
worksheet.write(5, 0, 'Product', header_style)
worksheet.write(5, 1, data['form']['6']['name'], header_style)
worksheet.write(5, 2, data['form']['5']['name'], header_style)
worksheet.write(5, 3, data['form']['4']['name'], header_style)
worksheet.write(5, 4, data['form']['3']['name'], header_style)
worksheet.write(5, 5, data['form']['2']['name'], header_style)
worksheet.write(5, 6, data['form']['1']['name'], header_style)
worksheet.write(5, 7, data['form']['0']['name'], header_style)
line = self.get_lines(data['form'])
if line:
i = 6
p = 0
for product in line:
if product.get('onhand_qty',
0) and product.get('onhand_qty', 0) > 0 and (
product['0'] > 0 or product['1'] > 0
or product['2'] > 0 or product['3'] > 0
or product['4'] > 0 or product['5'] > 0
or product['6'] > 0):
p += 1
worksheet.write(i, 0, product['pname'])
if product.get('onhand_qty', 0) != 0 and product.get(
'onhand_qty',
0) > 0 and (product['0'] > 0 or product['1'] > 0
or product['2'] > 0 or product['3'] > 0
or product['4'] > 0 or product['5'] > 0
or product['6'] > 0):
p += 1
worksheet.write(i, 1, product['6'])
if product.get('onhand_qty', 0) != 0 and product.get(
'onhand_qty',
0) > 0 and (product['0'] > 0 or product['1'] > 0
or product['2'] > 0 or product['3'] > 0
or product['4'] > 0 or product['5'] > 0
or product['6'] > 0):
p += 1
worksheet.write(i, 2, product['5'])
if product.get('onhand_qty', 0) != 0 and product.get(
'onhand_qty',
0) > 0 and (product['0'] > 0 or product['1'] > 0
or product['2'] > 0 or product['3'] > 0
or product['4'] > 0 or product['5'] > 0
or product['6'] > 0):
p += 1
worksheet.write(i, 3, product['4'])
if product.get('onhand_qty', 0) != 0 and product.get(
'onhand_qty',
0) > 0 and (product['0'] > 0 or product['1'] > 0
or product['2'] > 0 or product['3'] > 0
or product['4'] > 0
) or product['5'] > 0 or product['6'] > 0:
p += 1
worksheet.write(i, 4, product['3'])
if product.get('onhand_qty', 0) != 0 and product.get(
'onhand_qty',
0) > 0 and (product['0'] > 0 or product['1'] > 0
or product['2'] > 0 or product['3'] > 0
or product['4'] > 0 or product['5'] > 0
or product['6'] > 0):
p += 1
worksheet.write(i, 5, product['2'])
if product.get('onhand_qty', 0) != 0 and product.get(
'onhand_qty',
0) > 0 and (product['0'] > 0 or product['1'] > 0
or product['2'] > 0 or product['3'] > 0
or product['4'] > 0 or product['5'] > 0
or product['6'] > 0):
p += 1
worksheet.write(i, 6, product['1'])
if product.get('onhand_qty', 0) != 0 and product.get(
'onhand_qty',
0) > 0 and (product['0'] > 0 or product['1'] > 0
or product['2'] > 0 or product['3'] > 0
or product['4'] > 0 or product['5'] > 0
or product['6'] > 0):
p += 1
worksheet.write(i, 7, product['0'])
if p > 0:
i += 1
p = 0
fp = BytesIO()
workbook.save(fp)
export_id = self.env['inventory.age.dow'].create({
'excel_file':
base64.encodestring(fp.getvalue()),
'file_name':
filename
})
fp.close()
return {
'view_mode': 'form',
'res_id': export_id.id,
'res_model': 'inventory.age.dow',
'view_type': 'form',
'type': 'ir.actions.act_window',
'context': self._context,
'target': 'new',
}