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 export_training_xls(self, training_id, access_token):
appraisal_data = request.env['training.excel'].browse(training_id)
print(appraisal_data, '-----')
book = xlwt.Workbook(encoding='utf-8')
sheet1 = book.add_sheet("PySheet1")
style = xlwt.XFStyle()
style_header = xlwt.XFStyle()
style_right = xlwt.XFStyle()
style_right_bold = xlwt.XFStyle()
style_left = xlwt.XFStyle()
font = xlwt.Font()
font.bold = True
style.font = font
style_header.font = font
style_right_bold.font = font
# background color
pattern = xlwt.Pattern()
pattern.pattern = xlwt.Pattern.SOLID_PATTERN
pattern.pattern_fore_colour = xlwt.Style.colour_map['pale_blue']
style.pattern = pattern
style.num_format_str = '0.0'
# alignment
alignment = xlwt.Alignment()
alignment.horz = xlwt.Alignment.HORZ_LEFT
style.alignment = alignment
style.alignment.wrap = 100
borders = xlwt.Borders()
borders.left = xlwt.Borders.THIN
borders.right = xlwt.Borders.THIN
borders.top = xlwt.Borders.THIN
borders.bottom = xlwt.Borders.THIN
borders.left_colour = 0x00
borders.right_colour = 0x00
borders.top_colour = 0x00
borders.bottom_colour = 0x00
pattern1 = xlwt.Pattern()
pattern1.pattern1 = xlwt.Pattern.SOLID_PATTERN
pattern1.pattern_fore_colour = xlwt.Style.colour_map['pale_blue']
style_header.pattern1 = pattern1
style_header.alignment.wrap = 12
# alignment
alignment1 = xlwt.Alignment()
alignment1.horz = xlwt.Alignment.HORZ_CENTER
style_header.alignment = alignment1
alignment2 = xlwt.Alignment()
alignment2.horz = xlwt.Alignment.HORZ_RIGHT
style_right.alignment = alignment2
style_right.num_format_str = '0.0'
style_right.alignment.wrap = 12
alignment3 = xlwt.Alignment()
alignment3.horz = xlwt.Alignment.HORZ_LEFT
style_left.alignment = alignment3
style_left.num_format_str = '0.0'
style_left.alignment.wrap = 100
alignment4 = xlwt.Alignment()
alignment4.horz = xlwt.Alignment.HORZ_RIGHT
style_right_bold.alignment = alignment2
style_right_bold.num_format_str = '0.0'
style_right_bold.alignment.wrap = 12
pattern4 = xlwt.Pattern()
pattern4.pattern = xlwt.Pattern.SOLID_PATTERN
# style_right_bold.pattern = pattern4
sub_style = xlwt.easyxf('pattern: pattern solid, fore_colour gray80;'
'font: colour white, bold True;')
data_style = xlwt.easyxf('pattern: pattern solid, fore_colour white;'
'font: colour black, bold True;')
total_style = xlwt.easyxf('pattern: pattern solid, fore_colour yellow;'
'font: colour black, bold True;')
style.borders = borders
style_left.borders = borders
style_header.borders = borders
style_right.borders = borders
style_right_bold.borders = borders
row = 0
col = 0
sheet1.write_merge(2, 2, 0, 1, 'From Date:', sub_style)
sheet1.write_merge(2, 2, 2, 5, appraisal_data.from_date, style)
sheet1.write_merge(3, 3, 0, 1, 'To Date:', sub_style)
sheet1.write_merge(3, 3, 2, 5, appraisal_data.to_date, style)
# sheet1.write_merge(4, 4, 0, 1,'Location:', sub_style)
sheet1.write_merge(6, 6, 6, 9, 'Training Data', style)
sheet1.write(9, 0, 'Sr. No.', style_header)
sheet1.write_merge(9, 9, 1, 1, 'From Date', style_header)
sheet1.write_merge(9, 9, 2, 2, 'To Date', style_header)
sheet1.write_merge(9, 9, 3, 6, 'Training Name', style_header)
sheet1.write_merge(9, 9, 7, 9, 'Employee', style_header)
sheet1.write_merge(9, 9, 10, 10, 'Employee Code', style_header)
sheet1.write_merge(9, 9, 11, 11, 'Department', style_header)
sheet1.write_merge(9, 9, 12, 12, 'Designation', style_header)
sheet1.write_merge(9, 9, 13, 13, 'Reporting To', style_header)
# sheet1.write_merge(9, 9, 14, 14, 'Financial Year', style_header)
count = 0
sr_no = []
row = 10
for each in appraisal_data.training_one2many:
for x in each.training_id:
print(x.from_date, x.to_date, '000000')
for emp in x.employee:
count += 1
sr_no.append(count)
sheet1.write(row, col, count, style_header)
sheet1.write_merge(row, row, 1, 1, x.from_date, style_left)
sheet1.write_merge(row, row, 2, 2, x.to_date, style_left)
sheet1.write_merge(row, row, 3, 6, x.name, style_left)
sheet1.write_merge(row, row, 7, 9, emp.name, style_left)
sheet1.write_merge(row, row, 10, 10, emp.emp_code,
style_left)
sheet1.write_merge(row, row, 11, 11,
emp.department_id.name, style_right)
sheet1.write_merge(row, row, 12, 12, emp.job_id.name,
style_right)
sheet1.write_merge(row, row, 13, 13, emp.parent_id.name,
style_right)
row += 1
filename = 'TrainingDetails.xls'
response = request.make_response(
None,
headers=[('Content-Type', 'application/vnd.ms-excel'),
('Content-Disposition', content_disposition(filename))])
book.save(response.stream)
return response
def location_wise_report(self):
filename = ('Location wise Report') + '.xls'
workbook = xlwt.Workbook(encoding="UTF-8")
worksheet = workbook.add_sheet('Location Wise')
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")
GREEN_TABLE_HEADER = xlwt.easyxf(
'font: bold 1, name Tahoma, height 250;'
'align: vertical center, horizontal center, wrap on;'
'borders: top double, bottom double, left double, right double;')
loc_header = xlwt.easyxf(
'font: bold 1, name Tahoma, height 200;'
'align: vertical center, horizontal center, wrap on;'
'borders: top double, bottom double, left double, right double;')
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 = 400
worksheet.col(0).width = 8000
worksheet.col(1).width = 6000
worksheet.col(2).width = 3500
worksheet.col(3).width = 8000
borders = xlwt.Borders()
borders.bottom = xlwt.Borders.MEDIUM
border_style = xlwt.XFStyle() # Create Style
border_style.borders = borders
row = 2
worksheet.write(row, 0, 'Product Name' or '', for_left)
worksheet.write(row, 1, 'Product Category' or '', for_left)
worksheet.write(row, 2, 'Current Stock' or '', for_left)
worksheet.write(row, 3, 'Location' or '', for_left)
if self.caterogy_ids and self.select_location == 'location' and self.location_id:
product_ids = self.env['stock.quant'].search(
[('product_id.active', '=', True),
('product_id.categ_id', 'in', self.caterogy_ids.ids),
('location_id', '=', self.location_id.id)],
order='id desc')
title = self.location_id.name + 'Location'
elif self.caterogy_ids and self.select_location == 'all' and not self.location_id:
product_ids = self.env['stock.quant'].search(
[('product_id.active', '=', True),
('product_id.categ_id', 'in', self.caterogy_ids.ids)],
order='id desc')
title = "Stock Report Location Wise"
title = title
worksheet.write_merge(0, 1, 0, 3, title, GREEN_TABLE_HEADER)
# Group By of Location
if product_ids:
if self.select_location == 'all':
row = 0
order_data = self.env['stock.quant'].read_group(
[('id', 'in', product_ids.ids)], ['location_id'],
['location_id'],
lazy=False)
pr_list = []
for ref in order_data:
if ref['location_id']:
pr_list.append(ref['location_id'])
for order in pr_list:
row += 2
quant_id = self.env['stock.quant'].browse(order)
location_name = quant_id[1].id
worksheet.write_merge(row + 1, row + 1, 0, 3,
str(location_name), loc_header)
row += 1
for rec in product_ids:
if order[0] == rec.location_id.id:
row = row + 1
worksheet.write(row, 0, rec.product_id.display_name
or '', for_left_not_bold)
worksheet.write(row, 1,
rec.product_id.categ_id.name or '',
for_left_not_bold)
worksheet.write(row, 2, rec.quantity or '',
for_left_not_bold)
worksheet.write(
row, 3,
str(rec.location_id.location_id.name) + '/' +
str(rec.location_id.name) or '',
for_left_not_bold)
else:
row = 3
for rec in product_ids:
worksheet.write(row, 0, rec.product_id.display_name or '',
for_left_not_bold)
worksheet.write(row, 1, rec.product_id.categ_id.name or '',
for_left_not_bold)
worksheet.write(row, 2, rec.quantity or '',
for_left_not_bold)
worksheet.write(
row, 3,
str(rec.location_id.location_id.name) + '/' +
str(rec.location_id.name) or '', for_left_not_bold)
row += 1
else:
raise UserError(_('No Record Founds.'))
fp = io.BytesIO()
workbook.save(fp)
location_wise_id = self.env[
'location.wise.report.excel.extended'].create({
'excel_file':
base64.encodestring(fp.getvalue()),
'file_name':
filename
})
fp.close()
return {
'view_mode': 'form',
'res_id': location_wise_id.id,
'res_model': 'location.wise.report.excel.extended',
'view_type': 'form',
'type': 'ir.actions.act_window',
'context': self._context,
'target': 'new',
}
def generate_tax_report(self):
workbook = xlwt.Workbook()
header_format = xlwt.easyxf(
'font: bold True; pattern: pattern solid, fore_colour gray25; align: wrap on, vert centre, horiz center;'
)
style = xlwt.XFStyle()
data_header = xlwt.easyxf(
'font: bold True; pattern: pattern solid, fore_colour gray25; align: wrap off, vert centre, horiz center;'
)
font = xlwt.Font()
font.name = 'Arial'
style.font = font
style_total = xlwt.XFStyle()
font1 = xlwt.Font()
font1.name = 'Arial'
font1.bold = True
style_total.font = font1
borders = xlwt.Borders()
borders.left = 1
borders.right = 1
borders.top = 1
borders.bottom = 1
style_total.borders = borders
in_domain = [('account_id', '=', 104),
('move_id.state', '!=', 'cancel')]
out_domain = [('type', 'in', ['out_invoice', 'out_refund']),
('state', '!=', 'cancel')]
if self.date_from:
in_domain.append(('date', '>=', self.date_from))
out_domain.append(('invoice_date', '>=', self.date_from))
if self.date_to:
in_domain.append(('date', '<=', self.date_to))
out_domain.append(('invoice_date', '<=', self.date_to))
#Input Tax
in_invoices = self.env['account.move.line'].search(in_domain)
worksheet = workbook.add_sheet("Input Tax")
worksheet.write_merge(0, 0, 3, 6, 'Tax Report', header_format)
worksheet.write(2, 1, 'Date from', header_format)
worksheet.write(2, 2, str(self.date_from), header_format)
worksheet.write(2, 7, 'Date to', header_format)
worksheet.write(2, 8, str(self.date_to), header_format)
row = 5
col = 0
worksheet.write(row, col, u'Vendor Number', data_header)
worksheet.write(row, col + 1, u'Vendor Name', data_header)
worksheet.write(row, col + 2,
u'Vat Registration Number / الرقم الضريبي للمورد',
data_header)
worksheet.write(row, col + 3, u'Invoice number / رقم الفاتورة',
data_header)
worksheet.write(row, col + 4, u'Invoice Date/ تاريخ الفاتورة',
data_header)
worksheet.write(row, col + 5, u'VAT', data_header)
worksheet.write(row, col + 6, u'Net', data_header)
worksheet.write(row, col + 7, u'Total', data_header)
worksheet.write(row, col + 8, u'Tax %', data_header)
row = 6
col = 0
total_untaxed_amount_inv = total_tax_amount_inv = total_inv_amount_inv = discount_inv = gross_inv = 0.0
total_untaxed_amount_out = total_tax_amount_out = total_inv_amount_out = discount_out = gross_out = 0.0
for invoice in in_invoices:
totals_amounts = 0.00
supplier_num = invoice.partner_id.vendor_id if invoice.partner_id.vendor_id else ""
if invoice.expense_id:
supplier_name = invoice.expense_id.name
supplier_tax_id = invoice.expense_id.vat_number if invoice.expense_id.vat_number else ""
elif invoice.partner_id:
supplier_name = invoice.partner_id.name
supplier_tax_id = invoice.partner_id.vat
else:
supplier_name = ""
supplier_tax_id = ""
# supplier_name = invoice.partner_id.name if invoice.partner_id else ""
# supplier_tax_id = invoice.partner_id.vat if invoice.partner_id.vat else ""
bill_no = invoice.move_id.name
invoice_date = invoice.date
tax_amount = invoice.debit if invoice.debit > 0 else -invoice.credit
for rec in invoice.move_id.line_ids:
totals_amounts += rec.debit
total_amount = totals_amounts
without_tax_amount = total_amount - tax_amount
tax_percent = 15
total_untaxed_amount_inv += without_tax_amount
total_tax_amount_inv += tax_amount
total_inv_amount_inv += total_amount
worksheet.write(row, col, supplier_num, style)
worksheet.write(row, col + 1, supplier_name, style)
worksheet.write(row, col + 2, str(supplier_tax_id), style)
worksheet.write(row, col + 3, str(bill_no), style)
worksheet.write(row, col + 4, str(invoice_date), style)
worksheet.write(row, col + 5, "{:,.2f}".format(tax_amount), style)
worksheet.write(row, col + 6, "{:,.2f}".format(without_tax_amount),
style)
worksheet.write(row, col + 7, "{:,.2f}".format(total_amount),
style)
worksheet.write(row, col + 8, tax_percent, style)
row += 1
row += 1
worksheet.write(row, col + 5, "{:,.2f}".format(total_tax_amount_inv),
style_total)
worksheet.write(row, col + 6,
"{:,.2f}".format(total_untaxed_amount_inv),
style_total)
worksheet.write(row, col + 7, "{:,.2f}".format(total_inv_amount_inv),
style_total)
row += 1
# Output tax
out_invoices = self.env['account.move'].search(out_domain)
worksheet1 = workbook.add_sheet("Output Tax")
worksheet1.write_merge(0, 0, 3, 6, 'Tax Report', header_format)
worksheet1.write(2, 1, 'Date from', header_format)
worksheet1.write(2, 2, str(self.date_from), header_format)
worksheet1.write(2, 7, 'Date to', header_format)
worksheet1.write(2, 8, str(self.date_to), header_format)
row = 4
col = 0
worksheet1.write(row, col, u'Customer Name', data_header)
worksheet1.write(row, col + 1, u'Customer Tax ID', data_header)
worksheet1.write(row, col + 2, u'Invoice number', data_header)
worksheet1.write(row, col + 3, u'Invoice Date', data_header)
worksheet1.write(row, col + 4, u'VAT', data_header)
worksheet1.write(row, col + 5, u'Net', data_header)
worksheet1.write(row, col + 6, u'Total', data_header)
worksheet1.write(row, col + 7, u'Tax %', data_header)
row = 5
col = 0
for invoice in out_invoices:
supplier_name = invoice.partner_id.name
supplier_tax_id = invoice.partner_id.vat if invoice.partner_id.vat else ""
bill_no = invoice.name
invoice_date = invoice.invoice_date
without_tax_amount = invoice.amount_untaxed if invoice.type == 'out_invoice' else -1 * invoice.amount_untaxed
tax_amount = invoice.amount_tax if invoice.type == 'out_invoice' else -1 * invoice.amount_tax
total_amount = invoice.amount_total if invoice.type == 'out_invoice' else -1 * invoice.amount_total
tax_percent = round((invoice.amount_tax / without_tax_amount) *
100, 2) if without_tax_amount > 0 else 0.0
total_untaxed_amount_out += without_tax_amount
total_tax_amount_out += tax_amount
total_inv_amount_out += total_amount
worksheet1.write(row, col, supplier_name, style)
worksheet1.write(row, col + 1, str(supplier_tax_id), style)
worksheet1.write(row, col + 2, str(bill_no), style)
worksheet1.write(row, col + 3, str(invoice_date), style)
worksheet1.write(row, col + 4, "{:,.2f}".format(tax_amount), style)
worksheet1.write(row, col + 5,
"{:,.2f}".format(without_tax_amount), style)
worksheet1.write(row, col + 6, "{:,.2f}".format(total_amount),
style)
worksheet1.write(row, col + 7, tax_percent, style)
row += 1
row += 1
worksheet1.write(row, col + 4, "{:,.2f}".format(total_tax_amount_out),
style_total)
worksheet1.write(row, col + 5,
"{:,.2f}".format(total_untaxed_amount_out),
style_total)
worksheet1.write(row, col + 6, "{:,.2f}".format(total_inv_amount_out),
style_total)
row += 1
fp = io.BytesIO()
workbook.save(fp)
filename = 'tax_report.xls'
export_id = self.env['tax.report.wizard'].create({
'excel_file':
base64.encodestring(fp.getvalue()),
'file_name':
filename
})
fp.close()
return {
'view_mode': 'form',
'res_id': export_id.id,
'res_model': 'tax.report.wizard',
'view_type': 'form',
'type': 'ir.actions.act_window',
'context': self._context,
'target': 'new',
}
def confirmation_due_export_xls(self, report_id, access_token):
report_data = request.env['employee.confirmation.report'].browse(report_id)
book = xlwt.Workbook(encoding='utf-8')
sheet1 = book.add_sheet("Confirmation Due Report")
style = xlwt.XFStyle()
style_header = xlwt.XFStyle()
style_right = xlwt.XFStyle()
style_right_bold = xlwt.XFStyle()
style_left = xlwt.XFStyle()
font = xlwt.Font()
font.bold = True
style.font = font
style_header.font = font
# style_header.num_format_str = '0.00'
style_right_bold.font = font
# background color
pattern = xlwt.Pattern()
pattern.pattern = xlwt.Pattern.SOLID_PATTERN
pattern.pattern_fore_colour = xlwt.Style.colour_map['pale_blue']
style.pattern = pattern
# alignment
alignment = xlwt.Alignment()
alignment.horz = xlwt.Alignment.HORZ_LEFT
style.alignment = alignment
style.alignment.wrap = 100
borders = xlwt.Borders()
borders.left = xlwt.Borders.THIN
borders.right = xlwt.Borders.THIN
borders.top = xlwt.Borders.THIN
borders.bottom = xlwt.Borders.THIN
borders.left_colour = 0x00
borders.right_colour = 0x00
borders.top_colour = 0x00
borders.bottom_colour = 0x00
pattern1 = xlwt.Pattern()
pattern1.pattern1 = xlwt.Pattern.SOLID_PATTERN
pattern1.pattern_fore_colour = xlwt.Style.colour_map['pale_blue']
style_header.pattern1 = pattern1
style_header.alignment.wrap = 12
# alignment
alignment1 = xlwt.Alignment()
alignment1.horz = xlwt.Alignment.HORZ_CENTER
style_header.alignment = alignment1
alignment2 = xlwt.Alignment()
alignment2.horz = xlwt.Alignment.HORZ_RIGHT
style_right.alignment = alignment2
style_right.num_format_str = '0.00'
style_right.alignment.wrap = 12
alignment3 = xlwt.Alignment()
alignment3.horz = xlwt.Alignment.HORZ_LEFT
style_left.alignment = alignment3
style_left.num_format_str = '0.0'
style_left.alignment.wrap = 100
alignment4 = xlwt.Alignment()
alignment4.horz = xlwt.Alignment.HORZ_RIGHT
style_right_bold.alignment = alignment2
style_right_bold.num_format_str = '0.0'
style_right_bold.alignment.wrap = 12
pattern4 = xlwt.Pattern()
pattern4.pattern = xlwt.Pattern.SOLID_PATTERN
# style_right_bold.pattern = pattern4
sub_style = xlwt.easyxf('pattern: pattern solid, fore_colour gray80;'
'font: colour white, bold True;')
data_style = xlwt.easyxf('pattern: pattern solid, fore_colour white;'
'font: colour black, bold True;')
total_style = xlwt.easyxf('pattern: pattern solid, fore_colour yellow;'
'font: colour black, bold True;')
style.borders = borders
style_left.borders =borders
style_header.borders = borders
style_right.borders = borders
style_right_bold.borders = borders
row = 0
col = 0
sheet1.write_merge(2, 2, 0, 1, 'Orient Technologies PVT LTD', sub_style)
sheet1.write_merge(2, 2, 2, 5, 'Employee Confirmation Due Report', style)
sheet1.write_merge(3, 3, 0, 1, 'Date:', sub_style)
sheet1.write_merge(3, 3, 2, 5, str(datetime.now().date().strftime("%d/%m/%Y")), style)
# sheet1.write_merge(9, 9, 0, 13, 'Appraisal Due Report', style_header)
sheet1.write(5, 0, 'Sr. No.', style_header)
sheet1.write_merge(5, 5, 1, 5, 'Employee Name', style_header)
sheet1.write_merge(5, 5, 6, 6, 'Employee Code', style_header)
sheet1.write_merge(5, 5, 7, 7, 'Reporting To', style_header)
sheet1.write_merge(5, 5, 8, 8, 'Location', style_header)
sheet1.write_merge(5, 5, 9, 9, 'Department', style_header)
sheet1.write_merge(5, 5, 10, 10, 'Joining Date', style_header)
sheet1.write_merge(5, 5, 11, 11, 'Confirmation Date', style_header)
count=1
sr_no=[]
row=7
for x in report_data.due_report_lines:
sheet1.write(row,col, count, style_header)
sheet1.write_merge(row,row, 1, 5, x.emp_id.name, style_left)
sheet1.write_merge(row,row, 6, 6, x.emp_code, style_left)
sheet1.write_merge(row,row, 7, 7, x.emp_id.parent_id.name, style_right)
sheet1.write_merge(row,row, 8, 8, x.emp_id.site_master_id.name, style_right)
sheet1.write_merge(row,row, 9, 9, x.emp_id.department_id.name, style_right)
sheet1.write_merge(row,row, 10, 10, datetime.strptime(str(x.joining_date), "%Y-%m-%d").strftime("%d-%m-%Y"), style_right)
sheet1.write_merge(row,row, 11, 11, datetime.strptime(str(x.confirmation_date), "%Y-%m-%d").strftime("%d-%m-%Y"), style_right)
row+=1
count+=1
filename = 'Confirmation_Due_Report.xls'
response = request.make_response(None,
headers=[('Content-Type', 'application/vnd.ms-excel'),
('Content-Disposition', content_disposition(filename))])
book.save(response.stream)
return response
def bank_statement_history_excel(self):
bank_statement_line_ids = self._context.get('active_ids')
bank_statement_obj = self.env['account.bank.statement']
i = 0
sheetName = 1
workbook = xlwt.Workbook()
worksheet = []
for l in range(0, len(bank_statement_line_ids)):
worksheet.append(l)
for bank_statement in bank_statement_obj.browse(
bank_statement_line_ids):
n = 9
c = 1
style1 = xlwt.easyxf('pattern: pattern solid, fore_colour gray25;'
'font:bold True')
filename = 'bank_statement_report.xls'
style = xlwt.XFStyle()
tall_style = xlwt.easyxf('font:height 720;') # 36pt
font = xlwt.Font()
font.name = 'Times New Roman'
font.bold = True
font.height = 250
currency = xlwt.easyxf(
'font: height 180; align: wrap yes, horiz right',
num_format_str='#,##0.00')
if bank_statement.name:
worksheet[i] = workbook.add_sheet(str(bank_statement.name))
else:
worksheet[i] = workbook.add_sheet(str(sheetName))
sheetName += 1
formato_fecha = xlwt.easyxf(num_format_str='DD-MM-YY')
worksheet[i].write_merge(0, 0, 1, 3, "ANALISIS DE CUENTAS", style)
worksheet[i].col(0).width = 1000
worksheet[i].col(1).width = 5000
worksheet[i].col(2).width = 6500
worksheet[i].col(3).width = 6500
worksheet[i].col(4).width = 4500
worksheet[i].col(5).width = 4000
worksheet[i].col(6).width = 4000
fecha = datetime.strptime(bank_statement.date, '%Y-%m-%d')
ano = fecha.year
mes = fecha.strftime('%m')
worksheet[i].write(1, 1, 'Fecha', style1)
worksheet[i].write(1, 2, bank_statement.date or '', formato_fecha)
worksheet[i].write(2, 1, 'Cuenta', style1)
worksheet[i].write(
2, 2,
bank_statement.journal_id.default_credit_account_id.code +
' ' + bank_statement.journal_id.default_credit_account_id.name
or '')
worksheet[i].write(3, 1, 'Descripción:', style1)
worksheet[i].write(3, 2, bank_statement.journal_id.name, style)
worksheet[i].write(4, 1, 'Mes:', style1)
worksheet[i].write(4, 2, fecha.month, style)
worksheet[i].write(5, 1, 'Año:', style1)
worksheet[i].write(5, 2, ano, style)
worksheet[i].write(8, 1, 'Fecha', style1)
worksheet[i].write(8, 2, 'Referencia', style1)
worksheet[i].write(8, 3, 'Glosa Comprobante', style1)
worksheet[i].write(8, 4, 'Nº Doc', style1)
worksheet[i].write(8, 5, 'Monto', style1)
worksheet[i].write(8, 6, 'Mayor', style1)
worksheet[i].write(9, 6, bank_statement.balance_end_real, currency)
# query = """SELECT * FROM account_move_line where (to_char(date_maturity,'mm')= %s) and (to_char(date_maturity,'yyyy')= '%s') and account_id = %s and statement_id != %s""",(mes, ano,bank_statement.journal_id.default_credit_account_id.id,bank_statement.id)
#_logger.warning(query)
# self.env.cr.execute("""SELECT * FROM account_move_line where
#(to_char(date,'mm')= %s)
#and (to_char(date,'yyyy')= '%s') and account_id = %s and (statement_id is null or statement_id != %s)""",(mes, ano,bank_statement.journal_id.default_credit_account_id.id,bank_statement.id))
self.env.cr.execute(
"""SELECT * FROM account_move_line where (date<= %s) and account_id = %s and statement_id is null order by date""",
(bank_statement.date,
bank_statement.journal_id.default_credit_account_id.id))
line_list = [o for o in self.env.cr.dictfetchall()]
suma = 0
for rec in line_list:
worksheet[i].write(n, c, rec['date'], formato_fecha)
worksheet[i].write(n, c + 1, rec['name'], style)
worksheet[i].write(n, c + 2, rec['ref'], style)
worksheet[i].write(n, c + 3, rec['document_number'], style)
worksheet[i].write(n, c + 4, rec['balance'], currency)
suma = suma + float(rec['balance'])
n = n + 1
worksheet[i].write(n + 1, 2, 'Saldo Según Cartola', style)
worksheet[i].write(n + 1, 6,
suma + bank_statement.balance_end_real,
currency)
fp = io.BytesIO()
workbook.save(fp)
export_id = self.env['bank.statement.excel'].create({
'excel_file':
base64.encodestring(fp.getvalue()),
'file_name':
filename
})
fp.close()
return {
'view_mode': 'form',
'res_id': export_id.id,
'res_model': 'bank.statement.excel',
'view_type': 'form',
'type': 'ir.actions.act_window',
'context': self._context,
'target': 'new',
}
return True
def export_conveyance(self, conveyance_id, access_token):
conveyance_data = request.env['conveyance.export'].browse(
conveyance_id)
print(conveyance_data.financial_year.id)
book = xlwt.Workbook(encoding='utf-8')
sheet1 = book.add_sheet("Conveyance Applied Report",
cell_overwrite_ok=True)
style = xlwt.XFStyle()
style1 = xlwt.XFStyle()
style2 = xlwt.XFStyle()
style_header = xlwt.XFStyle()
style_right = xlwt.XFStyle()
style_right_bold = xlwt.XFStyle()
style_left = xlwt.XFStyle()
font = xlwt.Font()
font.bold = True
style.font = font
style_header.font = font
style_right_bold.font = font
# background color
pattern = xlwt.Pattern()
pattern.pattern = xlwt.Pattern.SOLID_PATTERN
pattern.pattern_fore_colour = xlwt.Style.colour_map['pale_blue']
style.pattern = pattern
style.num_format_str = '0.0'
style1.num_format_str = '0.00'
# alignment
alignment = xlwt.Alignment()
alignment.horz = xlwt.Alignment.HORZ_LEFT
style.alignment = alignment
style.alignment.wrap = 100
style2.alignment = alignment
style2.alignment.wrap = 100
borders = xlwt.Borders()
borders.left = xlwt.Borders.THIN
borders.right = xlwt.Borders.THIN
borders.top = xlwt.Borders.THIN
borders.bottom = xlwt.Borders.THIN
borders.left_colour = 0x00
borders.right_colour = 0x00
borders.top_colour = 0x00
borders.bottom_colour = 0x00
pattern1 = xlwt.Pattern()
pattern1.pattern1 = xlwt.Pattern.SOLID_PATTERN
pattern1.pattern_fore_colour = xlwt.Style.colour_map['pale_blue']
style_header.pattern1 = pattern1
style_header.alignment.wrap = 12
# alignment
alignment1 = xlwt.Alignment()
alignment1.horz = xlwt.Alignment.HORZ_CENTER
style_header.alignment = alignment1
alignment2 = xlwt.Alignment()
alignment2.horz = xlwt.Alignment.HORZ_RIGHT
style_right.alignment = alignment2
style_right.num_format_str = '0.0'
style_right.alignment.wrap = 12
alignment3 = xlwt.Alignment()
alignment3.horz = xlwt.Alignment.HORZ_LEFT
style_left.alignment = alignment3
style_left.num_format_str = '0.0'
style_left.alignment.wrap = 100
alignment4 = xlwt.Alignment()
alignment4.horz = xlwt.Alignment.HORZ_RIGHT
style_right_bold.alignment = alignment2
style_right_bold.num_format_str = '0.0'
style_right_bold.alignment.wrap = 12
alignment5 = xlwt.Alignment()
alignment5.horz = xlwt.Alignment.HORZ_RIGHT
style1.alignment = alignment5
style1.num_format_str = '0.00'
style1.alignment.wrap = 12
pattern4 = xlwt.Pattern()
pattern4.pattern = xlwt.Pattern.SOLID_PATTERN
# style_right_bold.pattern = pattern4
sub_style = xlwt.easyxf('pattern: pattern solid, fore_colour gray80;'
'font: colour white, bold True;')
data_style = xlwt.easyxf('pattern: pattern solid, fore_colour white;'
'font: colour black, bold True;')
total_style = xlwt.easyxf('pattern: pattern solid, fore_colour yellow;'
'font: colour black, bold True;')
style.borders = borders
style_left.borders = borders
style_header.borders = borders
style_right.borders = borders
style_right_bold.borders = borders
sheet1.write(1, 0, 'SR. NO.', style_header)
sheet1.write(1, 1, 'EMPLOYEE CODE', style_header)
sheet1.write(1, 2, 'NAME', style_header)
sheet1.write(1, 3, 'GRADE', style_header)
sheet1.write(1, 4, 'A/C NO', style_header)
sheet1.write(1, 5, 'DEPARTMENT', style_header)
sheet1.write(1, 6, 'BRANCH NAME', style_header)
sheet1.write(1, 7, 'Fixed Conyence (Monthly)', style_header)
sheet1.write(1, 8, 'Total Conveyance for 19-20 as per DOJ',
style_header)
sheet1.write(1, 9, 'April 2019', style_header)
sheet1.write(1, 10, 'May 2019', style_header)
sheet1.write(1, 11, 'June 2019', style_header)
sheet1.write(1, 12, 'July 2019', style_header)
sheet1.write(1, 13, 'August 2019', style_header)
sheet1.write(1, 14, 'September 2019', style_header)
sheet1.write(1, 15, 'October 2019', style_header)
sheet1.write(1, 16, 'November 2019', style_header)
sheet1.write(1, 17, 'December 2019', style_header)
sheet1.write(1, 18, 'January 2020', style_header)
sheet1.write(1, 19, 'February 2020', style_header)
sheet1.write(1, 20, 'March 2020', style_header)
sheet1.write(1, 21, 'Total Applied Conveyance (19-20)', style_header)
employee_list = []
employee_code = []
employee_name = []
employee_grade = []
account_no = []
department = []
branch_name = []
conveyance_lines_data = request.env[
'conveyance.reimbursement.lines'].search([
('year', '=', conveyance_data.financial_year.id)
])
if conveyance_lines_data:
for x in conveyance_lines_data:
if x.employee.id not in employee_list and x.employee.emp_code != 0:
employee_list.append(x.employee.id)
employee_code.append(x.employee.emp_code)
employee_name.append(x.employee.name)
employee_grade.append(x.employee.grade_id.name)
account_no.append(x.employee.bank_account_number)
department.append(x.employee.department_id.name)
branch_name.append(x.employee.site_master_id.name)
if employee_list:
row = 2
col = 0
count = 1
for m, n, o, p, q, r, s in zip(employee_list, employee_code,
employee_name, employee_grade,
account_no, department,
branch_name):
if employee_code != 0:
total_applied_conv = 0.0
conveyance_lines_browse = request.env[
'conveyance.reimbursement.lines'].search([
('year', '=', conveyance_data.financial_year.id),
('employee', '=', m)
])
sheet1.write(row, col, count, style2)
sheet1.write(row, col + 1, n, style2)
sheet1.write(row, col + 2, o, style1)
sheet1.write(row, col + 3, p, style1)
sheet1.write(row, col + 4, q, style1)
sheet1.write(row, col + 5, r, style1)
sheet1.write(row, col + 6, s, style1)
search_rec = request.env[
'conveyance.reimbursement.import'].search([
('year_id', '=',
conveyance_data.financial_year.id),
('employee', '=', m)
])
if search_rec:
sheet1.write(row, col + 7,
search_rec.monthly_conveyance, style1)
sheet1.write(row, col + 8,
search_rec.authorized_amount, style1)
for rec in conveyance_lines_browse:
if rec.month_sel == '4':
total_applied_conv += rec.applied_amount
sheet1.write(row, col + 9, rec.applied_amount,
style1)
if rec.month_sel == '5':
total_applied_conv += rec.applied_amount
sheet1.write(row, col + 10, rec.applied_amount,
style1)
if rec.month_sel == '6':
total_applied_conv += rec.applied_amount
sheet1.write(row, col + 11, rec.applied_amount,
style1)
if rec.month_sel == '7':
total_applied_conv += rec.applied_amount
sheet1.write(row, col + 12, rec.applied_amount,
style1)
if rec.month_sel == '8':
total_applied_conv += rec.applied_amount
sheet1.write(row, col + 13, rec.applied_amount,
style1)
if rec.month_sel == '9':
total_applied_conv += rec.applied_amount
sheet1.write(row, col + 14, rec.applied_amount,
style1)
if rec.month_sel == '10':
total_applied_conv += rec.applied_amount
sheet1.write(row, col + 15, rec.applied_amount,
style1)
if rec.month_sel == '11':
total_applied_conv += rec.applied_amount
sheet1.write(row, col + 16, rec.applied_amount,
style1)
if rec.month_sel == '12':
total_applied_conv += rec.applied_amount
sheet1.write(row, col + 17, rec.applied_amount,
style1)
if rec.month_sel == '1':
total_applied_conv += rec.applied_amount
sheet1.write(row, col + 18, rec.applied_amount,
style1)
if rec.month_sel == '2':
total_applied_conv += rec.applied_amount
sheet1.write(row, col + 19, rec.applied_amount,
style1)
if rec.month_sel == '3':
total_applied_conv += rec.applied_amount
sheet1.write(row, col + 20, rec.applied_amount,
style1)
sheet1.write(row, col + 21, total_applied_conv, style1)
row += 1
count += 1
filename = 'Conveyance_Applied_Report_%s.xls' % (datetime.now().date())
response = request.make_response(
None,
headers=[('Content-Type', 'application/vnd.ms-excel'),
('Content-Disposition', content_disposition(filename))])
book.save(response.stream)
return response
def attendance_summaryreport_xls(self, appraisal_id, access_token):
appraisal_data = request.env['attendance.reports'].browse(appraisal_id)
book = xlwt.Workbook(encoding='utf-8')
sheet1 = book.add_sheet("Attendance Data")
style = xlwt.XFStyle()
style_header = xlwt.XFStyle()
style_right = xlwt.XFStyle()
style_right_bold = xlwt.XFStyle()
style_left = xlwt.XFStyle()
font = xlwt.Font()
font.bold = True
style.font = font
style_header.font = font
style_right_bold.font = font
# background color
pattern = xlwt.Pattern()
pattern.pattern = xlwt.Pattern.SOLID_PATTERN
pattern.pattern_fore_colour = xlwt.Style.colour_map['pale_blue']
style.pattern = pattern
# style.num_format_str = '0.0'
# alignment
alignment = xlwt.Alignment()
alignment.horz = xlwt.Alignment.HORZ_LEFT
style.alignment = alignment
style.alignment.wrap = 100
borders = xlwt.Borders()
borders.left = xlwt.Borders.THIN
borders.right = xlwt.Borders.THIN
borders.top = xlwt.Borders.THIN
borders.bottom = xlwt.Borders.THIN
borders.left_colour = 0x00
borders.right_colour = 0x00
borders.top_colour = 0x00
borders.bottom_colour = 0x00
pattern1 = xlwt.Pattern()
pattern1.pattern1 = xlwt.Pattern.SOLID_PATTERN
pattern1.pattern_fore_colour = xlwt.Style.colour_map['pale_blue']
style_header.pattern1 = pattern1
style_header.alignment.wrap = 12
# alignment
alignment1 = xlwt.Alignment()
alignment1.horz = xlwt.Alignment.HORZ_CENTER
style_header.alignment = alignment1
alignment2 = xlwt.Alignment()
alignment2.horz = xlwt.Alignment.HORZ_RIGHT
style_right.alignment = alignment2
# style_right.num_format_str = '0.0'
style_right.alignment.wrap = 12
alignment3 = xlwt.Alignment()
alignment3.horz = xlwt.Alignment.HORZ_LEFT
style_left.alignment = alignment3
# style_left.num_format_str = '0.0'
style_left.alignment.wrap = 100
alignment4 = xlwt.Alignment()
alignment4.horz = xlwt.Alignment.HORZ_RIGHT
style_right_bold.alignment = alignment2
# style_right_bold.num_format_str = '0.0'
style_right_bold.alignment.wrap = 12
pattern4 = xlwt.Pattern()
pattern4.pattern = xlwt.Pattern.SOLID_PATTERN
# style_right_bold.pattern = pattern4
sub_style = xlwt.easyxf('pattern: pattern solid, fore_colour gray80;'
'font: colour white, bold True;')
data_style = xlwt.easyxf('pattern: pattern solid, fore_colour white;'
'font: colour black, bold True;')
total_style = xlwt.easyxf('pattern: pattern solid, fore_colour yellow;'
'font: colour black, bold True;')
style.borders = borders
style_left.borders = borders
style_header.borders = borders
style_right.borders = borders
style_right_bold.borders = borders
sheet1.write_merge(2, 2, 0, 1, 'From Date:', sub_style)
sheet1.write_merge(2, 2, 2, 5, appraisal_data.from_date, style)
sheet1.write_merge(3, 3, 0, 1, 'To Date:', sub_style)
sheet1.write_merge(3, 3, 2, 5, appraisal_data.to_date, style)
# sheet1.write_merge(4, 4, 0, 1,'Application Year:', sub_style)
# sheet1.write_merge(4, 4, 2, 5, appraisal_data.application_year.name, style)
print(appraisal_data.from_date)
month_sel = appraisal_data.month_sel
year_sel = appraisal_data.year_sel.name
if not appraisal_data.time_period:
raise ValidationError(_('Kindly select Period!!'))
if not month_sel:
raise ValidationError(_('Kindly select Month!!'))
if not year_sel:
raise ValidationError(_('Kindly select Year!!'))
all_dates = monthrange(int(year_sel), int(month_sel))
all_dates = list(all_dates)
sheet1.write_merge(6, 6, 0, 25, 'Attendance Data', style_header)
sheet1.write(8, 0, 'Sr. No.', style_header)
sheet1.write_merge(8, 8, 1, 1, 'Emp Code', style_header)
sheet1.write_merge(8, 8, 2, 5, 'Employee Name', style_header)
col = 6
row = 8
for dt in range(1, all_dates[1] + 1):
sheet1.write_merge(row, row, col, col, str(dt), style_header)
col += 1
count = 0
sr_no = []
row = 9
# row = 0
col = 0
from_date = appraisal_data.from_date
to_date = appraisal_data.to_date
site_master_id = appraisal_data.site_master_id.id
department_id = appraisal_data.department_id.id
employee_id = appraisal_data.employee_id.id
print("11111111111111111", from_date, to_date)
if site_master_id and not department_id:
emp_records = request.env['hr.employee'].search(
[('site_master_id', '=', site_master_id)], order='emp_code')
print(emp_records, 'emp_records')
row1 = 9
for emp in emp_records:
# if emp.emp_code in (1,7047):
count += 1
sheet1.write(row, col, count, style_header)
sheet1.write(row, col + 1, emp.emp_code, style_right)
sheet1.write_merge(row, row, 2, 5, emp.name, style_right)
row += 1
col1 = 6
for dt in range(1, all_dates[1] + 1):
# print (dt)
att_date = str(year_sel) + '-' + str(
month_sel) + '-' + str(dt)
# # print (att_date,'att_date')
attendance_recs = request.env['hr.attendance'].search(
[('attendance_date', '=', str(att_date)),
('employee_code', '=', emp.emp_code)],
order='attendance_date',
limit=1)
if attendance_recs:
employee_status = attendance_recs.employee_status
if employee_status == 'half_day_p_ab':
employee_status = 'Half P + Half AB'
if employee_status == 'half_day_sl':
employee_status = 'Half Day SL/CL + Half Day P'
if employee_status == 'half_day_pl':
employee_status = 'Half Day PL + Half Day P'
sheet1.write(row1, col1, employee_status, style_right)
else:
sheet1.write(row1, col1, 'AB', style_right)
col1 += 1
row1 += 1
filename = 'Attendance_Summary_Report.xls' #%(appraisal_data.review_cycle)
response = request.make_response(
None,
headers=[('Content-Type', 'application/vnd.ms-excel'),
('Content-Disposition', content_disposition(filename))])
book.save(response.stream)
return response
def attendance_month_exportreport_xls(self, att_id, access_token):
att_data = request.env['attendance.reports'].browse(att_id)
book = xlwt.Workbook(encoding='utf-8')
sheet1 = book.add_sheet("Attendance Data")
style = xlwt.XFStyle()
style_header = xlwt.XFStyle()
style_right = xlwt.XFStyle()
style_right_bold = xlwt.XFStyle()
style_left = xlwt.XFStyle()
font = xlwt.Font()
font.bold = True
style.font = font
style_header.font = font
style_right_bold.font = font
# background color
pattern = xlwt.Pattern()
pattern.pattern = xlwt.Pattern.SOLID_PATTERN
pattern.pattern_fore_colour = xlwt.Style.colour_map['pale_blue']
style.pattern = pattern
# style.num_format_str = '0.0'
# alignment
alignment = xlwt.Alignment()
alignment.horz = xlwt.Alignment.HORZ_LEFT
style.alignment = alignment
style.alignment.wrap = 100
borders = xlwt.Borders()
borders.left = xlwt.Borders.THIN
borders.right = xlwt.Borders.THIN
borders.top = xlwt.Borders.THIN
borders.bottom = xlwt.Borders.THIN
borders.left_colour = 0x00
borders.right_colour = 0x00
borders.top_colour = 0x00
borders.bottom_colour = 0x00
pattern1 = xlwt.Pattern()
pattern1.pattern1 = xlwt.Pattern.SOLID_PATTERN
pattern1.pattern_fore_colour = xlwt.Style.colour_map['pale_blue']
style_header.pattern1 = pattern1
style_header.alignment.wrap = 12
# alignment
alignment1 = xlwt.Alignment()
alignment1.horz = xlwt.Alignment.HORZ_CENTER
style_header.alignment = alignment1
alignment2 = xlwt.Alignment()
alignment2.horz = xlwt.Alignment.HORZ_RIGHT
style_right.alignment = alignment2
# style_right.num_format_str = '0.0'
style_right.alignment.wrap = 12
alignment3 = xlwt.Alignment()
alignment3.horz = xlwt.Alignment.HORZ_LEFT
style_left.alignment = alignment3
# style_left.num_format_str = '0.0'
style_left.alignment.wrap = 100
alignment4 = xlwt.Alignment()
alignment4.horz = xlwt.Alignment.HORZ_RIGHT
style_right_bold.alignment = alignment2
# style_right_bold.num_format_str = '0.0'
style_right_bold.alignment.wrap = 12
pattern4 = xlwt.Pattern()
pattern4.pattern = xlwt.Pattern.SOLID_PATTERN
# style_right_bold.pattern = pattern4
sub_style = xlwt.easyxf('pattern: pattern solid, fore_colour gray80;'
'font: colour white, bold True;')
data_style = xlwt.easyxf('pattern: pattern solid, fore_colour white;'
'font: colour black, bold True;')
total_style = xlwt.easyxf('pattern: pattern solid, fore_colour yellow;'
'font: colour black, bold True;')
style.borders = borders
style_left.borders = borders
style_header.borders = borders
style_right.borders = borders
style_right_bold.borders = borders
row = 0
col = 0
from_date = att_data.from_date
to_date = att_data.to_date
site_master_id = att_data.site_master_id.id
department_id = att_data.department_id.id
employee_id = att_data.employee_id.id
print("hhhh", from_date, to_date)
sheet1.write_merge(2, 2, 0, 1, 'From Date:', sub_style)
sheet1.write_merge(2, 2, 2, 5, att_data.from_date, style)
sheet1.write_merge(3, 3, 0, 1, 'To Date:', sub_style)
sheet1.write_merge(3, 3, 2, 5, att_data.to_date, style)
sheet1.write_merge(6, 6, 0, 21, 'Attendance Data', style_header)
sheet1.write(8, 0, 'Sr. No.', style_header)
sheet1.write_merge(8, 8, 1, 1, 'Employee Code', style_header)
sheet1.write_merge(8, 8, 2, 4, 'Employee Name', style_header)
sheet1.write_merge(8, 8, 5, 6, 'Designation', style_header)
sheet1.write_merge(8, 8, 7, 8, 'Site', style_header)
sheet1.write_merge(8, 8, 9, 9, 'January', style_header)
sheet1.write_merge(8, 8, 10, 10, 'February', style_header)
sheet1.write_merge(8, 8, 11, 11, 'March', style_header)
sheet1.write_merge(8, 8, 12, 12, 'April', style_header)
sheet1.write_merge(8, 8, 13, 13, 'May', style_header)
sheet1.write_merge(8, 8, 14, 14, 'June', style_header)
sheet1.write_merge(8, 8, 15, 15, 'July', style_header)
sheet1.write_merge(8, 8, 16, 16, 'August', style_header)
sheet1.write_merge(8, 8, 17, 17, 'September', style_header)
sheet1.write_merge(8, 8, 18, 18, 'October', style_header)
sheet1.write_merge(8, 8, 19, 19, 'November', style_header)
sheet1.write_merge(8, 8, 20, 20, 'December', style_header)
sheet1.write_merge(8, 8, 21, 21, 'Total', style_header)
count = 0
sr_no = []
row = 10
emp_id = []
if site_master_id and not department_id and not employee_id:
emp_recs = request.env['hr.employee'].search([
('active', '=', 't'), ('site_master_id', '=', site_master_id)
])
print(emp_recs, 'emp_recs')
for emp in emp_recs:
print("------------------------------------------------")
attendance_rec = request.env['hr.attendance'].search(
[('attendance_date', '>=', from_date),
('attendance_date', '<=', to_date),
('employee_id', '=', emp.id),
('employee_status', 'in',
('P', 'half_day_p_ab', 'half_day_sl', 'half_day_pl'))],
order='employee_code, attendance_date')
print(attendance_rec, 'attendance')
emp_name = emp.name
emp_code = emp.emp_code
job_id = emp.job_id.name
site_id = emp.site_master_id.name
month_count = []
if attendance_rec:
for x in attendance_rec:
print(x.employee_id.name, 'Name')
attendance_date = x.attendance_date
# print(attendance_date,'attendance_date')
month = datetime.strptime(str(attendance_date),
"%Y-%m-%d").month
# print(month,'month')
month_count.append(month)
print(month_count, 'month_count')
jan = month_count.count(1)
feb = month_count.count(2)
mar = month_count.count(3)
apr = month_count.count(4)
may = month_count.count(5)
jun = month_count.count(6)
jul = month_count.count(7)
aug = month_count.count(8)
sep = month_count.count(9)
octo = month_count.count(10)
nov = month_count.count(11)
dec = month_count.count(12)
total = jan + feb + mar + apr + may + jun + jul + aug + sep + octo + nov + dec
print(jan, feb, mar, apr, may, jun, jul, aug, sep, octo,
nov, dec, '----------work days count')
count += 1
sr_no.append(count)
sheet1.write(row, col, count, style_header)
sheet1.write_merge(row, row, 1, 1, emp_code, style_right)
sheet1.write_merge(row, row, 2, 4, emp_name, style_right)
sheet1.write_merge(row, row, 5, 6, job_id, style_right)
sheet1.write_merge(row, row, 7, 8, site_id, style_right)
sheet1.write_merge(row, row, 9, 9, jan, style_right)
sheet1.write_merge(row, row, 10, 10, feb, style_right)
sheet1.write_merge(row, row, 11, 11, mar, style_right)
sheet1.write_merge(row, row, 12, 12, apr, style_right)
sheet1.write_merge(row, row, 13, 13, may, style_right)
sheet1.write_merge(row, row, 14, 14, jun, style_right)
sheet1.write_merge(row, row, 15, 15, jul, style_right)
sheet1.write_merge(row, row, 16, 16, aug, style_right)
sheet1.write_merge(row, row, 17, 17, sep, style_right)
sheet1.write_merge(row, row, 18, 18, octo, style_right)
sheet1.write_merge(row, row, 19, 19, nov, style_right)
sheet1.write_merge(row, row, 20, 20, dec, style_right)
sheet1.write_merge(row, row, 21, 21, total, style_right)
row += 1
elif employee_id and not site_master_id and not department_id:
emp_recs = request.env['hr.employee'].search([('active', '=', 't'),
('id', '=',
employee_id)])
print(emp_recs, 'emp_recs')
month_count = []
for emp in emp_recs:
attendance_rec = request.env['hr.attendance'].search(
[('attendance_date', '>=', from_date),
('attendance_date', '<=', to_date),
('employee_id', '=', emp.id)],
order='employee_code, attendance_date')
print(attendance_rec, 'attendance')
emp_id = emp.id
job_id = emp.job_id.id
site_id = emp.site_master_id.id
for x in attendance_rec:
if x.employee_status in ('P', 'half_day_p_ab',
'half_day_sl', 'half_day_pl'):
attendance_date = x.attendance_date
# print(attendance_date,'attendance_date')
month = datetime.strptime(str(attendance_date),
"%Y-%m-%d").month
# print(month,'month')
month_count.append(month)
print(month_count, 'month_count')
jan = month_count.count(1)
feb = month_count.count(2)
mar = month_count.count(3)
apr = month_count.count(4)
may = month_count.count(5)
jun = month_count.count(6)
jul = month_count.count(7)
aug = month_count.count(8)
sep = month_count.count(9)
octo = month_count.count(10)
nov = month_count.count(11)
dec = month_count.count(12)
total = jan + feb + mar + apr + may + jun + jul + aug + sep + octo + nov + dec
print(jan, feb, mar, apr, may, jun, jul, aug, sep, octo, nov,
dec, '----------')
count += 1
sr_no.append(count)
sheet1.write(row, col, count, style_header)
sheet1.write_merge(row, row, 1, 1, emp.emp_code, style_right)
sheet1.write_merge(row, row, 2, 4, emp.name, style_right)
sheet1.write_merge(row, row, 5, 6, emp.job_id.name,
style_right)
sheet1.write_merge(row, row, 7, 8, emp.site_master_id.name,
style_right)
sheet1.write_merge(row, row, 9, 9, jan, style_right)
sheet1.write_merge(row, row, 10, 10, feb, style_right)
sheet1.write_merge(row, row, 11, 11, mar, style_right)
sheet1.write_merge(row, row, 12, 12, apr, style_right)
sheet1.write_merge(row, row, 13, 13, may, style_right)
sheet1.write_merge(row, row, 14, 14, jun, style_right)
sheet1.write_merge(row, row, 15, 15, jul, style_right)
sheet1.write_merge(row, row, 16, 16, aug, style_right)
sheet1.write_merge(row, row, 17, 17, sep, style_right)
sheet1.write_merge(row, row, 18, 18, octo, style_right)
sheet1.write_merge(row, row, 19, 19, nov, style_right)
sheet1.write_merge(row, row, 20, 20, dec, style_right)
sheet1.write_merge(row, row, 21, 21, total, style_right)
row += 1
elif site_master_id and employee_id and not department_id:
emp_recs = request.env['hr.employee'].search([
('active', '=', 't'), ('id', '=', employee_id),
('site_master_id', '=', site_master_id)
])
print(emp_recs, 'emp_recs')
month_count = []
for emp in emp_recs:
print("------------------------------------------------")
attendance_rec = request.env['hr.attendance'].search(
[('attendance_date', '>=', from_date),
('attendance_date', '<=', to_date),
('employee_id', '=', emp.id),
('employee_status', 'in',
('P', 'half_day_p_ab', 'half_day_sl', 'half_day_pl'))],
order='employee_code, attendance_date')
print(attendance_rec, 'attendance')
emp_name = emp.name
emp_code = emp.emp_code
job_id = emp.job_id.name
site_id = emp.site_master_id.name
month_count = []
if attendance_rec:
for x in attendance_rec:
print(x.employee_id.name, 'Name')
attendance_date = x.attendance_date
# print(attendance_date,'attendance_date')
month = datetime.strptime(str(attendance_date),
"%Y-%m-%d").month
# print(month,'month')
month_count.append(month)
print(month_count, 'month_count')
jan = month_count.count(1)
feb = month_count.count(2)
mar = month_count.count(3)
apr = month_count.count(4)
may = month_count.count(5)
jun = month_count.count(6)
jul = month_count.count(7)
aug = month_count.count(8)
sep = month_count.count(9)
octo = month_count.count(10)
nov = month_count.count(11)
dec = month_count.count(12)
total = jan + feb + mar + apr + may + jun + jul + aug + sep + octo + nov + dec
print(jan, feb, mar, apr, may, jun, jul, aug, sep, octo,
nov, dec, '----------work days count')
count += 1
sr_no.append(count)
sheet1.write(row, col, count, style_header)
sheet1.write_merge(row, row, 1, 1, emp_code, style_right)
sheet1.write_merge(row, row, 2, 4, emp_name, style_right)
sheet1.write_merge(row, row, 5, 6, job_id, style_right)
sheet1.write_merge(row, row, 7, 8, site_id, style_right)
sheet1.write_merge(row, row, 9, 9, jan, style_right)
sheet1.write_merge(row, row, 10, 10, feb, style_right)
sheet1.write_merge(row, row, 11, 11, mar, style_right)
sheet1.write_merge(row, row, 12, 12, apr, style_right)
sheet1.write_merge(row, row, 13, 13, may, style_right)
sheet1.write_merge(row, row, 14, 14, jun, style_right)
sheet1.write_merge(row, row, 15, 15, jul, style_right)
sheet1.write_merge(row, row, 16, 16, aug, style_right)
sheet1.write_merge(row, row, 17, 17, sep, style_right)
sheet1.write_merge(row, row, 18, 18, octo, style_right)
sheet1.write_merge(row, row, 19, 19, nov, style_right)
sheet1.write_merge(row, row, 20, 20, dec, style_right)
sheet1.write_merge(row, row, 21, 21, total, style_right)
row += 1
filename = 'AttendanceExport.xls'
response = request.make_response(
None,
headers=[('Content-Type', 'application/vnd.ms-excel'),
('Content-Disposition', content_disposition(filename))])
book.save(response.stream)
return response
def attendance_export_xls(self, appraisal_id, access_token):
appraisal_data = request.env['hr.attendance.export'].browse(
appraisal_id)
book = xlwt.Workbook(encoding='utf-8')
sheet1 = book.add_sheet("Attendance Data")
style = xlwt.XFStyle()
style_header = xlwt.XFStyle()
style_right = xlwt.XFStyle()
style_right_bold = xlwt.XFStyle()
style_left = xlwt.XFStyle()
font = xlwt.Font()
font.bold = True
style.font = font
style_header.font = font
style_right_bold.font = font
# background color
pattern = xlwt.Pattern()
pattern.pattern = xlwt.Pattern.SOLID_PATTERN
pattern.pattern_fore_colour = xlwt.Style.colour_map['pale_blue']
style.pattern = pattern
# style.num_format_str = '0.0'
# alignment
alignment = xlwt.Alignment()
alignment.horz = xlwt.Alignment.HORZ_LEFT
style.alignment = alignment
style.alignment.wrap = 100
borders = xlwt.Borders()
borders.left = xlwt.Borders.THIN
borders.right = xlwt.Borders.THIN
borders.top = xlwt.Borders.THIN
borders.bottom = xlwt.Borders.THIN
borders.left_colour = 0x00
borders.right_colour = 0x00
borders.top_colour = 0x00
borders.bottom_colour = 0x00
pattern1 = xlwt.Pattern()
pattern1.pattern1 = xlwt.Pattern.SOLID_PATTERN
pattern1.pattern_fore_colour = xlwt.Style.colour_map['pale_blue']
style_header.pattern1 = pattern1
style_header.alignment.wrap = 12
# alignment
alignment1 = xlwt.Alignment()
alignment1.horz = xlwt.Alignment.HORZ_CENTER
style_header.alignment = alignment1
alignment2 = xlwt.Alignment()
alignment2.horz = xlwt.Alignment.HORZ_RIGHT
style_right.alignment = alignment2
# style_right.num_format_str = '0.0'
style_right.alignment.wrap = 12
alignment3 = xlwt.Alignment()
alignment3.horz = xlwt.Alignment.HORZ_LEFT
style_left.alignment = alignment3
# style_left.num_format_str = '0.0'
style_left.alignment.wrap = 100
alignment4 = xlwt.Alignment()
alignment4.horz = xlwt.Alignment.HORZ_RIGHT
style_right_bold.alignment = alignment2
# style_right_bold.num_format_str = '0.0'
style_right_bold.alignment.wrap = 12
pattern4 = xlwt.Pattern()
pattern4.pattern = xlwt.Pattern.SOLID_PATTERN
# style_right_bold.pattern = pattern4
sub_style = xlwt.easyxf('pattern: pattern solid, fore_colour gray80;'
'font: colour white, bold True;')
data_style = xlwt.easyxf('pattern: pattern solid, fore_colour white;'
'font: colour black, bold True;')
total_style = xlwt.easyxf('pattern: pattern solid, fore_colour yellow;'
'font: colour black, bold True;')
style.borders = borders
style_left.borders = borders
style_header.borders = borders
style_right.borders = borders
style_right_bold.borders = borders
row = 0
col = 0
from_date = appraisal_data.from_date
to_date = appraisal_data.to_date
print("11111111111111111", from_date, to_date)
sheet1.write_merge(2, 2, 0, 1, 'From Date:', sub_style)
sheet1.write_merge(2, 2, 2, 5, appraisal_data.from_date, style)
sheet1.write_merge(3, 3, 0, 1, 'To Date:', sub_style)
sheet1.write_merge(3, 3, 2, 5, appraisal_data.to_date, style)
# sheet1.write_merge(4, 4, 0, 1,'Application Year:', sub_style)
# sheet1.write_merge(4, 4, 2, 5, appraisal_data.application_year.name, style)
attendance_rec = request.env['hr.attendance'].search(
[('attendance_date', '>', from_date),
('attendance_date', '<=', to_date)],
order='employee_code, attendance_date')
# print(attendance_rec,'attendance')
sheet1.write_merge(6, 6, 0, 25, 'Attendance Data', style_header)
sheet1.write(8, 0, 'Sr. No.', style_header)
sheet1.write_merge(8, 8, 1, 1, 'Employee Code', style_header)
sheet1.write_merge(8, 8, 2, 4, 'Employee Name', style_header)
sheet1.write_merge(8, 8, 5, 6, 'Department', style_header)
sheet1.write_merge(8, 8, 7, 8, 'Designation', style_header)
sheet1.write_merge(8, 8, 9, 9, 'Site', style_header)
sheet1.write_merge(8, 8, 10, 10, 'Shift', style_header)
sheet1.write_merge(8, 8, 11, 11, 'Date', style_header)
sheet1.write_merge(8, 8, 12, 12, 'In Time', style_header)
sheet1.write_merge(8, 8, 13, 13, 'Out Time', style_header)
sheet1.write_merge(8, 8, 14, 14, 'Worked Hours', style_header)
sheet1.write_merge(8, 8, 15, 15, 'Status', style_header)
count = 0
sr_no = []
row = 10
for x in attendance_rec:
count += 1
sr_no.append(count)
sheet1.write(row, col, count, style_header)
sheet1.write_merge(row, row, 1, 1, x.employee_id.emp_code,
style_right)
sheet1.write_merge(row, row, 2, 4, x.employee_id.name, style_right)
sheet1.write_merge(row, row, 5, 6,
x.employee_id.department_id.name, style_right)
sheet1.write_merge(row, row, 7, 8, x.employee_id.job_id.name,
style_right)
sheet1.write_merge(row, row, 9, 9,
x.employee_id.site_master_id.name, style_right)
sheet1.write_merge(row, row, 10, 10, x.shift.name, style_right)
sheet1.write_merge(row, row, 11, 11, x.attendance_date,
style_right)
sheet1.write_merge(row, row, 12, 12, x.in_time, style_right)
sheet1.write_merge(row, row, 13, 13, x.out_time, style_right)
sheet1.write_merge(row, row, 14, 14, x.worked_hours, style_right)
sheet1.write_merge(row, row, 15, 15, x.employee_status,
style_right)
row += 1
filename = 'AttendanceExport.xls' #%(appraisal_data.review_cycle)
response = request.make_response(
None,
headers=[('Content-Type', 'application/vnd.ms-excel'),
('Content-Disposition', content_disposition(filename))])
book.save(response.stream)
return response
def attendance_exportreport_xls(self, appraisal_id, access_token):
att_data = request.env['attendance.reports'].browse(appraisal_id)
book = xlwt.Workbook(encoding='utf-8')
sheet1 = book.add_sheet("Attendance Data")
style = xlwt.XFStyle()
style_header = xlwt.XFStyle()
style_right = xlwt.XFStyle()
style_right_bold = xlwt.XFStyle()
style_left = xlwt.XFStyle()
font = xlwt.Font()
font.bold = True
style.font = font
style_header.font = font
style_right_bold.font = font
# background color
pattern = xlwt.Pattern()
pattern.pattern = xlwt.Pattern.SOLID_PATTERN
pattern.pattern_fore_colour = xlwt.Style.colour_map['pale_blue']
style.pattern = pattern
# style.num_format_str = '0.0'
# alignment
alignment = xlwt.Alignment()
alignment.horz = xlwt.Alignment.HORZ_LEFT
style.alignment = alignment
style.alignment.wrap = 100
borders = xlwt.Borders()
borders.left = xlwt.Borders.THIN
borders.right = xlwt.Borders.THIN
borders.top = xlwt.Borders.THIN
borders.bottom = xlwt.Borders.THIN
borders.left_colour = 0x00
borders.right_colour = 0x00
borders.top_colour = 0x00
borders.bottom_colour = 0x00
pattern1 = xlwt.Pattern()
pattern1.pattern1 = xlwt.Pattern.SOLID_PATTERN
pattern1.pattern_fore_colour = xlwt.Style.colour_map['pale_blue']
style_header.pattern1 = pattern1
style_header.alignment.wrap = 12
# alignment
alignment1 = xlwt.Alignment()
alignment1.horz = xlwt.Alignment.HORZ_CENTER
style_header.alignment = alignment1
alignment2 = xlwt.Alignment()
alignment2.horz = xlwt.Alignment.HORZ_RIGHT
style_right.alignment = alignment2
style_right_bold.alignment = alignment2
# style_right.num_format_str = '0'
style_right.alignment.wrap = 12
style_right_bold.alignment.wrap = 12
alignment3 = xlwt.Alignment()
alignment3.horz = xlwt.Alignment.HORZ_LEFT
style_left.alignment = alignment3
# style_left.num_format_str = '0.0'
style_left.alignment.wrap = 100
# alignment4 = xlwt.Alignment()
# alignment4.horz = xlwt.Alignment.HORZ_RIGHT
# style_right_bold.alignment = alignment2
# style_right_bold.num_format_str = '0.0'
# style_right_bold.alignment.wrap = 12
pattern4 = xlwt.Pattern()
pattern4.pattern = xlwt.Pattern.SOLID_PATTERN
# style_right_bold.pattern = pattern4
sub_style = xlwt.easyxf('pattern: pattern solid, fore_colour gray80;'
'font: colour white, bold True;')
data_style = xlwt.easyxf('pattern: pattern solid, fore_colour white;'
'font: colour black, bold True;')
total_style = xlwt.easyxf('pattern: pattern solid, fore_colour yellow;'
'font: colour black, bold True;')
style.borders = borders
style_left.borders = borders
style_header.borders = borders
style_right.borders = borders
style_right_bold.borders = borders
style_right_bold = xlwt.easyxf(
'pattern: pattern solid, fore_colour yellow;'
'font: colour black, bold False;'
'align: horiz right;')
row = 0
col = 0
from_date = att_data.from_date
to_date = att_data.to_date
site_master_id = att_data.site_master_id.id
department_id = att_data.department_id.id
employee_id = att_data.employee_id.id
print("hhhh", from_date, to_date)
sheet1.write_merge(2, 2, 0, 1, 'From Date:', sub_style)
sheet1.write_merge(2, 2, 2, 5, att_data.from_date, style)
sheet1.write_merge(3, 3, 0, 1, 'To Date:', sub_style)
sheet1.write_merge(3, 3, 2, 5, att_data.to_date, style)
# sheet1.write_merge(4, 4, 0, 1,'Application Year:', sub_style)
# sheet1.write_merge(4, 4, 2, 5, appraisal_data.application_year.name, style)
attendance_rec = request.env['hr.attendance'].search(
[('attendance_date', '>=', from_date),
('attendance_date', '<=', to_date)],
order='employee_code, attendance_date')
# print(attendance_rec,'attendance')
sheet1.write_merge(6, 6, 0, 15, 'Attendance Data', style_header)
sheet1.write(8, 0, 'Sr. No.', style_header)
sheet1.write_merge(8, 8, 1, 1, 'Employee Code', style_header)
sheet1.write_merge(8, 8, 2, 4, 'Employee Name', style_header)
sheet1.write_merge(8, 8, 5, 6, 'Department', style_header)
sheet1.write_merge(8, 8, 7, 8, 'Designation', style_header)
sheet1.write_merge(8, 8, 9, 9, 'Site', style_header)
sheet1.write_merge(8, 8, 10, 10, 'Shift', style_header)
sheet1.write_merge(8, 8, 11, 11, 'Date', style_header)
sheet1.write_merge(8, 8, 12, 12, 'In Time', style_header)
sheet1.write_merge(8, 8, 13, 13, 'Out Time', style_header)
sheet1.write_merge(8, 8, 14, 14, 'Worked Hours', style_header)
sheet1.write_merge(8, 8, 15, 15, 'Early Leaving', style_header)
sheet1.write_merge(8, 8, 16, 16, 'Late Coming', style_header)
sheet1.write_merge(8, 8, 17, 17, 'Extra Worked Hours', style_header)
sheet1.write_merge(8, 8, 18, 18, 'Status', style_header)
count = 0
sr_no = []
row = 10
for x in attendance_rec:
if x.worked_hours:
if x.worked_hours >= 9.0:
extra_worked_hours = x.worked_hours - 9.0
extra_hours = float("{0:.2f}".format(extra_worked_hours))
extra_hours = str(extra_hours).replace('.', ':')
print(extra_hours, 'extra_hours')
worked_hours = str(x.worked_hours).replace('.', ':')
else:
extra_hours = '00:00'
worked_hours = str(x.worked_hours).replace('.', ':')
else:
extra_hours = '00:00'
worked_hours = str(x.worked_hours).replace('.', ':')
if x.approve_check == True:
final_style = style_right_bold
else:
final_style = style_right
if not site_master_id and not department_id and not employee_id:
# print("plainnnnnnnnnnnnnnnnnnn")
count += 1
sr_no.append(count)
sheet1.write(row, col, count, style_header)
sheet1.write_merge(row, row, 1, 1, x.employee_id.emp_code,
final_style)
sheet1.write_merge(row, row, 2, 4, x.employee_id.name,
final_style)
sheet1.write_merge(row, row, 5, 6,
x.employee_id.department_id.name,
final_style)
sheet1.write_merge(row, row, 7, 8, x.employee_id.job_id.name,
final_style)
sheet1.write_merge(row, row, 9, 9,
x.employee_id.site_master_id.name,
final_style)
sheet1.write_merge(row, row, 10, 10, x.shift.name, final_style)
sheet1.write_merge(row, row, 11, 11, x.attendance_date,
final_style)
sheet1.write_merge(row, row, 12, 12, x.in_time, final_style)
sheet1.write_merge(row, row, 13, 13, x.out_time, final_style)
sheet1.write_merge(row, row, 14, 14, worked_hours, final_style)
sheet1.write_merge(row, row, 15, 15, x.early_leaving,
final_style)
sheet1.write_merge(row, row, 16, 16, x.late_coming,
final_style)
sheet1.write_merge(row, row, 17, 17, extra_hours, final_style)
sheet1.write_merge(row, row, 18, 18, x.employee_status,
final_style)
row += 1
elif site_master_id and not department_id and not employee_id:
# print("site_master_id")
if x.employee_id.site_master_id.id == site_master_id:
print(x.employee_id.id, 'emp id')
count += 1
sr_no.append(count)
sheet1.write(row, col, count, style_header)
sheet1.write_merge(row, row, 1, 1, x.employee_id.emp_code,
final_style)
sheet1.write_merge(row, row, 2, 4, x.employee_id.name,
final_style)
sheet1.write_merge(row, row, 5, 6,
x.employee_id.department_id.name,
final_style)
sheet1.write_merge(row, row, 7, 8,
x.employee_id.job_id.name, final_style)
sheet1.write_merge(row, row, 9, 9,
x.employee_id.site_master_id.name,
final_style)
sheet1.write_merge(row, row, 10, 10, x.shift.name,
final_style)
sheet1.write_merge(row, row, 11, 11, x.attendance_date,
final_style)
sheet1.write_merge(row, row, 12, 12, x.in_time,
final_style)
sheet1.write_merge(row, row, 13, 13, x.out_time,
final_style)
sheet1.write_merge(row, row, 14, 14, worked_hours,
final_style)
sheet1.write_merge(row, row, 15, 15, x.early_leaving,
final_style)
sheet1.write_merge(row, row, 16, 16, x.late_coming,
final_style)
sheet1.write_merge(row, row, 17, 17, extra_hours,
final_style)
sheet1.write_merge(row, row, 18, 18, x.employee_status,
final_style)
row += 1
elif department_id and not site_master_id and not employee_id:
# print("department_id")
if x.employee_id.department_id.id == department_id:
print(x.employee_id.id, 'emp id')
count += 1
sr_no.append(count)
sheet1.write(row, col, count, style_header)
sheet1.write_merge(row, row, 1, 1, x.employee_id.emp_code,
final_style)
sheet1.write_merge(row, row, 2, 4, x.employee_id.name,
final_style)
sheet1.write_merge(row, row, 5, 6,
x.employee_id.department_id.name,
final_style)
sheet1.write_merge(row, row, 7, 8,
x.employee_id.job_id.name, final_style)
sheet1.write_merge(row, row, 9, 9,
x.employee_id.site_master_id.name,
final_style)
sheet1.write_merge(row, row, 10, 10, x.shift.name,
final_style)
sheet1.write_merge(row, row, 11, 11, x.attendance_date,
final_style)
sheet1.write_merge(row, row, 12, 12, x.in_time,
final_style)
sheet1.write_merge(row, row, 13, 13, x.out_time,
final_style)
sheet1.write_merge(row, row, 14, 14, worked_hours,
final_style)
sheet1.write_merge(row, row, 15, 15, x.early_leaving,
final_style)
sheet1.write_merge(row, row, 16, 16, x.late_coming,
final_style)
sheet1.write_merge(row, row, 17, 17, extra_hours,
final_style)
sheet1.write_merge(row, row, 18, 18, x.employee_status,
final_style)
row += 1
elif site_master_id and department_id and not employee_id:
# print("both")
if x.employee_id.site_master_id.id == site_master_id and x.employee_id.department_id.id == department_id:
print(x.employee_id.id, 'emp id')
count += 1
sr_no.append(count)
sheet1.write(row, col, count, style_header)
sheet1.write_merge(row, row, 1, 1, x.employee_id.emp_code,
final_style)
sheet1.write_merge(row, row, 2, 4, x.employee_id.name,
final_style)
sheet1.write_merge(row, row, 5, 6,
x.employee_id.department_id.name,
final_style)
sheet1.write_merge(row, row, 7, 8,
x.employee_id.job_id.name, final_style)
sheet1.write_merge(row, row, 9, 9,
x.employee_id.site_master_id.name,
final_style)
sheet1.write_merge(row, row, 10, 10, x.shift.name,
final_style)
sheet1.write_merge(row, row, 11, 11, x.attendance_date,
final_style)
sheet1.write_merge(row, row, 12, 12, x.in_time,
final_style)
sheet1.write_merge(row, row, 13, 13, x.out_time,
final_style)
sheet1.write_merge(row, row, 14, 14, worked_hours,
final_style)
sheet1.write_merge(row, row, 15, 15, x.early_leaving,
final_style)
sheet1.write_merge(row, row, 16, 16, x.late_coming,
final_style)
sheet1.write_merge(row, row, 17, 17, extra_hours,
final_style)
sheet1.write_merge(row, row, 18, 18, x.employee_status,
final_style)
row += 1
elif employee_id and not site_master_id and not department_id:
if x.employee_id.id == employee_id:
print(x.employee_id.id, 'emp id')
count += 1
sr_no.append(count)
sheet1.write(row, col, count, style_header)
sheet1.write_merge(row, row, 1, 1, x.employee_id.emp_code,
final_style)
sheet1.write_merge(row, row, 2, 4, x.employee_id.name,
final_style)
sheet1.write_merge(row, row, 5, 6,
x.employee_id.department_id.name,
final_style)
sheet1.write_merge(row, row, 7, 8,
x.employee_id.job_id.name, final_style)
sheet1.write_merge(row, row, 9, 9,
x.employee_id.site_master_id.name,
final_style)
sheet1.write_merge(row, row, 10, 10, x.shift.name,
final_style)
sheet1.write_merge(row, row, 11, 11, x.attendance_date,
final_style)
sheet1.write_merge(row, row, 12, 12, x.in_time,
final_style)
sheet1.write_merge(row, row, 13, 13, x.out_time,
final_style)
sheet1.write_merge(row, row, 14, 14, worked_hours,
final_style)
sheet1.write_merge(row, row, 15, 15, x.early_leaving,
final_style)
sheet1.write_merge(row, row, 16, 16, x.late_coming,
final_style)
sheet1.write_merge(row, row, 17, 17, extra_hours,
final_style)
sheet1.write_merge(row, row, 18, 18, x.employee_status,
final_style)
row += 1
filename = 'AttendanceExport.xls' #%(appraisal_data.review_cycle)
response = request.make_response(
None,
headers=[('Content-Type', 'application/vnd.ms-excel'),
('Content-Disposition', content_disposition(filename))])
book.save(response.stream)
return response
def exit_report_xls(self, exit_id, access_token):
exit_data = request.env['exit.reports'].browse(exit_id)
book = xlwt.Workbook(encoding='utf-8')
sheet1 = book.add_sheet("Exit Report")
style = xlwt.XFStyle()
style_header = xlwt.XFStyle()
style_right = xlwt.XFStyle()
style_right_bold = xlwt.XFStyle()
style_left = xlwt.XFStyle()
font = xlwt.Font()
font.bold = True
style.font = font
style_header.font = font
style_right_bold.font = font
# background color
pattern = xlwt.Pattern()
pattern.pattern = xlwt.Pattern.SOLID_PATTERN
pattern.pattern_fore_colour = xlwt.Style.colour_map['pale_blue']
style.pattern = pattern
# style.num_format_str = '0.0'
# alignment
alignment = xlwt.Alignment()
alignment.horz = xlwt.Alignment.HORZ_LEFT
style.alignment = alignment
style.alignment.wrap = 100
borders = xlwt.Borders()
borders.left = xlwt.Borders.THIN
borders.right = xlwt.Borders.THIN
borders.top = xlwt.Borders.THIN
borders.bottom = xlwt.Borders.THIN
borders.left_colour = 0x00
borders.right_colour = 0x00
borders.top_colour = 0x00
borders.bottom_colour = 0x00
pattern1 = xlwt.Pattern()
pattern1.pattern1 = xlwt.Pattern.SOLID_PATTERN
pattern1.pattern_fore_colour = xlwt.Style.colour_map['pale_blue']
style_header.pattern1 = pattern1
style_header.alignment.wrap = 12
# alignment
alignment1 = xlwt.Alignment()
alignment1.horz = xlwt.Alignment.HORZ_CENTER
style_header.alignment = alignment1
alignment2 = xlwt.Alignment()
alignment2.horz = xlwt.Alignment.HORZ_RIGHT
style_right.alignment = alignment2
# style_right.num_format_str = '0.0'
style_right.alignment.wrap = 12
alignment3 = xlwt.Alignment()
alignment3.horz = xlwt.Alignment.HORZ_LEFT
style_left.alignment = alignment3
# style_left.num_format_str = '0.0'
style_left.alignment.wrap = 100
alignment4 = xlwt.Alignment()
alignment4.horz = xlwt.Alignment.HORZ_RIGHT
style_right_bold.alignment = alignment2
# style_right_bold.num_format_str = '0.0'
style_right_bold.alignment.wrap = 12
pattern4 = xlwt.Pattern()
pattern4.pattern = xlwt.Pattern.SOLID_PATTERN
# style_right_bold.pattern = pattern4
sub_style = xlwt.easyxf('pattern: pattern solid, fore_colour gray80;'
'font: colour white, bold True;')
data_style = xlwt.easyxf('pattern: pattern solid, fore_colour white;'
'font: colour black, bold True;')
total_style = xlwt.easyxf('pattern: pattern solid, fore_colour yellow;'
'font: colour black, bold True;')
style.borders = borders
style_left.borders = borders
style_header.borders = borders
style_right.borders = borders
style_right_bold.borders = borders
row = 0
col = 0
# sheet1.write_merge(2, 2, 0, 1, 'Review Cycle:', sub_style)
# sheet1.write_merge(2, 2, 2, 5, appraisal_data.review_cycle, style)
# sheet1.write_merge(3, 3, 0, 1, 'Financial Year:', sub_style)
# sheet1.write_merge(3, 3, 2, 5, appraisal_data.financial_year.name, style)
# sheet1.write_merge(4, 4, 0, 1,'Application Year:', sub_style)
# sheet1.write_merge(4, 4, 2, 5, appraisal_data.application_year.name, style)
sheet1.write_merge(7, 7, 0, 13, 'Exit Report', style_header)
sheet1.write(9, 0, 'Sr. No.', style_header)
sheet1.write_merge(9, 9, 1, 2, 'Reporting Code', style_header)
sheet1.write_merge(9, 9, 3, 4, 'Reporting Name', style_header)
sheet1.write_merge(9, 9, 5, 6, 'Employee Code', style_header)
sheet1.write_merge(9, 9, 7, 8, 'Employee Name', style_header)
sheet1.write_merge(9, 9, 9, 10, 'Department', style_header)
sheet1.write_merge(9, 9, 11, 12, 'Designation', style_header)
sheet1.write_merge(9, 9, 13, 14, 'Joining Date', style_header)
sheet1.write_merge(9, 9, 15, 16, 'Resignation Date', style_header)
sheet1.write_merge(9, 9, 17, 18, 'Last Working Date', style_header)
sheet1.write_merge(9, 9, 19, 22, 'Reason of Resignation', style_header)
sheet1.write_merge(9, 9, 23, 24, 'Notice Period', style_header)
sheet1.write_merge(9, 9, 25, 26, 'Site', style_header)
sheet1.write_merge(9, 9, 27, 28, 'State', style_header)
count = 0
sr_no = []
row = 10
search_rec = request.env['hr.resignation'].search([
('approved_relieving_date', '>=', exit_data.from_date),
('approved_relieving_date', '<=', exit_data.to_date)
])
for x in search_rec:
if x.state:
state = ''
if x.state == 'draft':
state = 'Pending'
elif x.state == 'confirm':
state = 'Manager Approval'
elif x.state == 'approved':
state = 'HR Approval'
elif x.state == 'resignation_accepted':
state = 'Resignation Accepted'
elif x.state == 'cancel':
state = 'Cancel'
elif x.state == 'rejected':
state = 'Rejected'
else:
state = 'Resignation Revoked'
count += 1
sheet1.write(row, col, count, style_header)
sheet1.write_merge(row, row, 1, 2,
x.reporting_manager_id.emp_code, style_left)
sheet1.write_merge(row, row, 3, 4, x.reporting_manager_id.name,
style_left)
sheet1.write_merge(row, row, 5, 6, x.employee_id.emp_code,
style_right)
sheet1.write_merge(row, row, 7, 8, x.employee_id.name,
style_right)
sheet1.write_merge(row, row, 9, 10, x.department_id.name,
style_right)
sheet1.write_merge(row, row, 11, 12, x.employee_id.job_id.name,
style_right)
sheet1.write_merge(row, row, 13, 14, x.joined_date,
style_right)
sheet1.write_merge(row, row, 15, 16, x.expected_relieving_date,
style_right)
sheet1.write_merge(row, row, 17, 18, x.approved_relieving_date,
style_right)
sheet1.write_merge(row, row, 19, 22, x.reason_resignation.name,
style_right)
sheet1.write_merge(row, row, 23, 24, x.notice_period,
style_right)
sheet1.write_merge(row, row, 25, 26,
x.employee_id.site_master_id.name,
style_right)
sheet1.write_merge(row, row, 27, 28, state, style_right)
row += 1
filename = 'Exit_Report.xls' #%(appraisal_data.review_cycle)
response = request.make_response(
None,
headers=[('Content-Type', 'application/vnd.ms-excel'),
('Content-Disposition', content_disposition(filename))])
book.save(response.stream)
return response