def salary_payment_report(self,cr,uid,ids,context=None):
#Define the font attributes for header
fnt = Font()
fnt.name = 'Arial'
fnt.size=16
fnt.style= 'Regular'
#Define the font attributes for header
content_fnt = Font()
content_fnt.name ='Arial'
content_fnt.size=16
content_fnt.style= 'Regular'
align_content = Alignment()
align_content.horz= Alignment.HORZ_CENTER
borders = Borders()
borders.left = 0x01
borders.right = 0x01
borders.top = 0x01
borders.bottom = 0x01
#The text should be centrally aligned
align = Alignment()
align.horz = Alignment.HORZ_CENTER
align.vert = Alignment.VERT_CENTER
#We set the backgroundcolour here
pattern = Pattern()
pattern.pattern = Pattern.SOLID_PATTERN
pattern.pattern_fore_colour = 0x1F
#apply the above settings to the row(0) header
style_header= XFStyle()
style_header.font= fnt
style_header.pattern= pattern
style_header.borders = borders
style_header.alignment=align
#Define the font attributes for header
fnt3 = Font()
fnt3.name = 'Arial'
fnt3.size=16
fnt3.style= 'Regular'
#Define the font attributes for header
content_fnt3 = Font()
content_fnt3.name ='Arial'
content_fnt3.size=16
content_fnt3.style= 'Regular'
align_content3 = Alignment()
align_content3.horz= Alignment.HORZ_CENTER
borders3 = Borders()
borders3.left = 0x01
borders3.right = 0x01
borders3.top = 0x01
borders3.bottom = 0x01
#The text should be centrally aligned
align3 = Alignment()
align3.horz = Alignment.HORZ_CENTER
align3.vert = Alignment.VERT_CENTER
#We set the backgroundcolour here
pattern3 = Pattern()
#apply the above settings to the row(0) header
style_header3= XFStyle()
style_header3.font= fnt3
style_header3.pattern= pattern3
style_header3.borders = borders3
style_header3.alignment=align3
#Define the font attributes for header
fnt4 = Font()
fnt4.name = 'Arial'
#Define the font attributes for header
content_fnt4 = Font()
content_fnt4.name ='Arial'
align_content4 = Alignment()
align_content4.horz= Alignment.HORZ_LEFT
borders4 = Borders()
borders4.left = 0x01
borders4.right = 0x01
borders4.top = 0x01
borders4.bottom = 0x01
#The text should be centrally aligned
align4 = Alignment()
align4.horz = Alignment.HORZ_LEFT
align4.vert = Alignment.VERT_CENTER
#We set the backgroundcolour here
pattern4 = Pattern()
#apply the above settings to the row(0) header
style_header4= XFStyle()
style_header4.font= fnt4
style_header4.pattern= pattern4
style_header4.borders = borders4
style_header4.alignment=align4
#Define the font attributes for header
fnt1 = Font()
fnt1.name = 'Arial'
fnt1.size=10
fnt1.Style= 'Regular'
#Define the font attributes for header
content_fnt1 = Font()
content_fnt1.name ='Arial'
content_fnt1.size=10
content_fnt1.Style= 'Regular'
align_content1 = Alignment()
align_content1.horz= Alignment.HORZ_RIGHT
borders1 = Borders()
borders1.left = 0x01
borders1.right = 0x01
borders1.top = 0x01
borders1.bottom = 0x01
#The text should be centrally aligned
align1 = Alignment()
align1.horz = Alignment.HORZ_RIGHT
align1.vert = Alignment.VERT_CENTER
#We set the backgroundcolour here
pattern1 = Pattern()
#apply the above settings to the row(0) header
style_header1= XFStyle()
style_header1.font= fnt1
style_header1.pattern= pattern1
style_header1.borders = borders1
style_header1.alignment=align1
#Define the font attributes for header
fnt2 = Font()
fnt2.name = 'Arial'
fnt2.size=16
fnt2.Style= 'Regular'
#Define the font attributes for header
content_fnt2 = Font()
content_fnt2.name ='Arial'
content_fnt2.size=16
content_fnt2.Style= 'Regular'
align_content2 = Alignment()
align_content2.horz= Alignment.HORZ_RIGHT
borders2 = Borders()
borders2.left = 0x01
borders2.right = 0x01
borders2.top = 0x01
borders2.bottom = 0x01
#The text should be centrally aligned
align2 = Alignment()
align2.horz = Alignment.HORZ_RIGHT
align2.vert = Alignment.VERT_CENTER
#We set the backgroundcolour here
pattern2 = Pattern()
#apply the above settings to the row(0) header
style_header2= XFStyle()
style_header2.font= fnt2
style_header2.pattern= pattern
style_header2.borders = borders2
style_header2.alignment=align2
wb = Workbook()
ws = wb.add_sheet('Salary Report')
this=self.browse(cr,uid,ids[0],context=context)
ws.row(0).height=300
ws.row(1).height=300
ws.row(2).height=300
# ws.col(0).width = 5000
# ws.col(0).width = 5000
# ws.col(0).width = 5000
# ws.col(0).width = 5000
ws.write_merge(0,0,0,5, 'SALARY REPORT',style_header)
ws.write_merge(1,1,0,5, ('DEPARTMENT : ',this.department_id.name),style_header)
ws.write_merge(2,2,0,1, 'Month',style_header)
ws.write_merge(2,2,2,3, 'Salary Amount',style_header)
ws.write_merge(2,2,4,5, 'Remark',style_header)
l=[]
lst=[]
for val in this.month_ids:
i = 3
query="""select hy.name,spl.month, sum(spl.total_amount) from salary_payment_line as spl left join holiday_year as hy on spl.year_id = hy.id where month = '"""+str(val.month)+"""'
and year_id = '"""+str(val.year_id.id)+"""' and curr_department = '"""+str(this.department_id.id)+"""' and salary_type='Salary'
group by spl.month, hy.name"""
cr.execute(query)
temp = cr.fetchall()
for year,month, total_amount in temp:
t=()
mon = int(month)
t = (year,mon, total_amount)
lst.append(t)
total = 0.0
lst.sort()
for year,month, total_amount in lst:
if month == 1:
month1 = 'January'
elif month == 2:
month1 = 'February'
elif month == 3:
month1 = 'March'
elif month == 4:
month1 = 'April'
elif month == 5:
month1 = 'May'
elif month == 6:
month1 = 'June'
elif month == 7:
month1 = 'July'
elif month == 8:
month1 = 'August'
elif month == 9:
month1 = 'September'
elif month == 10:
month1 = 'October'
elif month == 11:
month1 = 'November'
else:
month1 = 'December'
total += total_amount
ws.write_merge(i,i,0,1, (month1,' ',year),style_header4)
ws.write_merge(i,i,2,3, round(total_amount,2),style_header1)
ws.write_merge(i,i,4,5, ' ',style_header3)
i += 1
ws.write_merge(i,i,0,1, 'Total Amount',style_header2)
ws.write_merge(i,i,2,3, round(total,2),style_header2)
ws.write_merge(i,i,4,5, ' ',style_header)
if len(lst) < 1:
raise osv.except_osv(_('Warning!'),_('No Record found!'))
f = cStringIO.StringIO()
wb.save(f)
out=base64.encodestring(f.getvalue())
sal_report = self.write(cr, uid, ids, {'export_data':out, 'filename':'Salary Report.xls'}, context=context)
return sal_report
def neem_trainee_stipend_register_report(self, cr, uid, ids, context=None):
#Define the font attributes for header
fnt = Font()
fnt.name = 'Ubuntu Medium'
fnt.size = 15
fnt.Style = 'Regular'
content_fnt = Font()
content_fnt.name = 'Ubuntu Medium'
content_fnt.size = 15
content_fnt.Style = 'Regular'
align_content = Alignment()
align_content.horz = Alignment.HORZ_CENTER
borders = Borders()
#The text should be centrally aligned
align = Alignment()
align.horz = Alignment.HORZ_CENTER
align.vert = Alignment.VERT_TOP
#We set the backgroundcolour here
pattern = Pattern()
#apply the above settings to the row(0) header
style_header = XFStyle()
style_header.font = fnt
style_header.pattern = pattern
style_header.borders = borders
style_header.alignment = align
#Define the font attributes for header
fnt1 = Font()
fnt1.name = 'Arial'
fnt1.size = 15
fnt1.bold = True
align_content1 = Alignment()
align_content1.horz = Alignment.HORZ_CENTER
borders1 = Borders()
borders1.left = 0x00
borders1.right = 0x00
borders1.top = 0x00
borders1.bottom = 0x00
align1 = Alignment()
align1.horz = Alignment.HORZ_CENTER
align1.vert = Alignment.VERT_CENTER
pattern1 = Pattern()
pattern1.pattern1 = Pattern.SOLID_PATTERN
pattern1.pattern1_fore_colour = 0x1F
style_header1 = XFStyle()
style_header1.font = fnt1
style_header1.pattern = pattern1
style_header1.borders = borders1
style_header1.alignment = align1
#Define the font attributes for header
fnt2 = Font()
fnt2.name = 'Ubuntu Medium'
fnt2.size = 10
fnt2.style = 'Regular'
content_fnt2 = Font()
content_fnt2.name = 'Ubuntu Medium'
content_fnt2.style = 'Regular'
align_content2 = Alignment()
align_content2.horz = Alignment.HORZ_LEFT
borders2 = Borders()
borders2.left = 0x0
borders2.right = 0x0
borders2.top = 0x0
borders2.bottom = 0x0
#The text should be centrally aligned
align2 = Alignment()
align2.horz = Alignment.HORZ_CENTER
align2.vert = Alignment.VERT_TOP
#We set the backgroundcolour here
pattern2 = Pattern()
#apply the above settings to the row(0) header
style_header2 = XFStyle()
style_header2.font = fnt2
style_header2.pattern = pattern2
style_header2.borders = borders2
style_header2.alignment = align2
wb = Workbook()
ws = wb.add_sheet('Neem Trainee Stipend Register')
# style_pass = xlwt.easyxf('pattern: pattern solid, Font.name:Arial ,Bold: True, fore_colour black;')
# ws.row(0).height=500
ws.col(0).width = 1800
ws.col(1).width = 6000
ws.col(2).width = 6000
ws.col(3).width = 6000
ws.col(4).width = 6000
ws.col(5).width = 7200
ws.col(6).width = 6000
ws.col(7).width = 7200
ws.col(8).width = 6000
ws.col(9).width = 6000
ws.col(10).width = 2200
ws.col(11).width = 2200
ws.col(12).width = 2200
ws.col(13).width = 2200
ws.col(14).width = 2200
ws.col(15).width = 2200
ws.col(16).width = 2200
ws.col(17).width = 2500
ws.col(18).width = 2500
ws.col(19).width = 3200
ws.col(20).width = 2500
ws.col(21).width = 2500
ws.col(22).width = 2500
ws.col(23).width = 3500
i = 3
this = self.browse(cr, uid, ids)
from_date = this.from_date
from_date1 = datetime.strptime(from_date, "%Y-%m-%d")
from_date1 = from_date1.strftime('%d-%m-%Y')
till_date = this.till_date
till_date1 = datetime.strptime(till_date, "%Y-%m-%d")
till_date1 = till_date1.strftime('%d-%m-%Y')
partner_id = this.partner_id
emp_id = this.employee_id
ws.write_merge(
0, 1, 8, 12,
'NEEM TRAINEE STIPEND REGISTER FOR' + this.month_id.name +
' ( ' + from_date1 + ' to ' + till_date1 + ' ) ', style_header)
ws.write(i, 0, 'S.No.', style_header1)
ws.write(i, 1, 'Emp Code', style_header1)
ws.write(i, 2, 'Punch Code', style_header1)
ws.write(i, 3, 'Emp Name', style_header1)
ws.write(i, 4, 'Department', style_header1)
ws.write(i, 5, 'Designation', style_header1)
ws.write(i, 6, 'DOJ', style_header1)
ws.write(i, 7, 'Bank Name', style_header1)
ws.write(i, 8, 'Bank Acc. Number', style_header1)
ws.write(i, 9, 'IFS Code', style_header1)
ws.write(i, 10, 'Stipend \n Rate', style_header1)
ws.write(i, 11, 'Total \n Days', style_header1)
ws.write(i, 12, 'Working \n Days', style_header1)
ws.write(i, 13, 'Casual \n Leave', style_header1)
ws.write(i, 14, 'Earn \n Leave', style_header1)
ws.write(i, 15, 'Holiday', style_header1)
ws.write(i, 16, 'Absent \n Days', style_header1)
ws.write(i, 17, 'Payable \n Days', style_header1)
ws.write(i, 18, 'Stipend \n Payable', style_header1)
ws.write(i, 19, 'Performance \n Incentive', style_header1)
ws.write(i, 20, 'Total \n Earnings', style_header1)
ws.write(i, 21, 'Deduction \n If Any', style_header1)
ws.write(i, 22, 'Net \n Payable', style_header1)
ws.write(i, 23, 'Signature \n Thumb \n Impression', style_header1)
ws.row(23).height = 500
ws.row(22).height = 500
ws.row(21).height = 500
ws.row(20).height = 500
ws.row(19).height = 500
ws.row(18).height = 500
ws.row(17).height = 500
ws.row(16).height = 500
ws.row(15).height = 500
ws.row(14).height = 500
ws.row(13).height = 500
ws.row(12).height = 500
ws.row(11).height = 500
ws.row(10).height = 500
ws.row(9).height = 500
ws.row(8).height = 500
ws.row(7).height = 500
ws.row(6).height = 500
ws.row(4).height = 500
ws.row(4).height = 500
ws.row(3).height = 500
ws.row(2).height = 500
ws.row(1).height = 500
ws.row(0).height = 500
i += 1
emp_obj = self.pool.get('hr.employee')
if this.employee_id:
list_ids = emp_obj.search(cr, uid,
[('partner_id', '=', partner_id.id),
('id', '=', emp_id.id),
('active', '=', True),
('doj', '<=', till_date),
('employment_type', '=', 'Trainee')])
else:
list_ids = emp_obj.search(cr, uid,
[('partner_id', '=', partner_id.id),
('active', '=', True),
('doj', '<=', till_date),
('employment_type', '=', 'Trainee')])
if not list_ids:
raise osv.except_osv(_('Warning !'), _("No Record Found !!!"))
i1 = 0
for emp in list_ids:
i1 += 1
bank_name = ''
acc_no = ''
ifs_code = ''
month_days = work_day = casual_leave = earned_leave = holiday = absent_days = days = stipend_pay = other_earns = total_amt = deduction = net_pay = 0.0
emp_browse = emp_obj.browse(cr, uid, emp)
sinid = emp_browse.sinid
name = emp_browse.name
dept = emp_browse.department_id.name
desg = emp_browse.job_id.name
doj = datetime.strptime(emp_browse.doj, "%Y-%m-%d")
doj = doj.strftime('%d-%m-%Y')
salary = emp_browse.total_salary
punch_code = emp_browse.paycode
query1 = "select bank_name,id_no,ifsc_code from verification where employee_id='"+str(emp)+"' " \
"and proof_id = 'Bank_ Account_ No' "
cr.execute(query1)
temp1 = cr.fetchall()
bank_name = ''
if temp1:
bank_name = temp1[0][0]
acc_no = temp1[0][1]
ifs_code = self.pool.get('res.bank').browse(
cr, uid, temp1[0][2]).bic
if bank_name:
paid_msg = 'Paid In Bank'
else:
paid_msg = ''
ws.write(i, 0, i1, style_header)
ws.write(i, 1, sinid, style_header)
ws.write(i, 2, punch_code, style_header)
ws.write(i, 3, name, style_header)
ws.write(i, 4, dept, style_header)
ws.write(i, 5, desg, style_header)
ws.write(i, 6, doj, style_header)
ws.write(i, 7, bank_name, style_header)
ws.write(i, 8, acc_no, style_header)
ws.write(i, 9, ifs_code, style_header)
ws.write(i, 10, salary, style_header)
sal_line_search = self.pool.get('salary.payment.line').search(
cr, uid, [('employee_id', '=', emp),
('month', '=', this.month_id.month),
('year_id', '=', this.month_id.year_id.id)])
if sal_line_search:
sal_line = self.pool.get('salary.payment.line').browse(
cr, uid, sal_line_search[0])
month_days = sal_line.month_days
work_day = sal_line.work_day + sal_line.factory_work
casual_leave = sal_line.casual_leave
earned_leave = sal_line.earned_leave
holiday = sal_line.week_leave + sal_line.holiday_leave
absent_days = sal_line.month_days - sal_line.days
days = sal_line.days
stipend_pay = sal_line.days_amount + sal_line.other_salary_amount
other_earns = sal_line.overtime_amount + sal_line.sun_overtime_amount
total_amt = stipend_pay + other_earns
deduction = sal_line.kharcha + sal_line.loan
net_pay = total_amt - deduction
ws.write(i, 11, month_days, style_header)
ws.write(i, 12, work_day, style_header)
ws.write(i, 13, casual_leave, style_header)
ws.write(i, 14, earned_leave, style_header)
ws.write(i, 15, holiday, style_header)
ws.write(i, 16, absent_days, style_header)
ws.write(i, 17, days, style_header)
ws.write(i, 18, stipend_pay, style_header)
ws.write(i, 19, other_earns, style_header)
ws.write(i, 20, total_amt, style_header)
ws.write(i, 21, deduction, style_header)
ws.write(i, 22, net_pay, style_header)
ws.write(i, 23, paid_msg, style_header)
ws.row(i).height = 500
i += 1
f = cStringIO.StringIO()
wb.save(f)
out = base64.encodestring(f.getvalue())
return self.write(cr,
uid,
ids, {
'export_data': out,
'filename': 'Neem Trainee Stipend Register.xls'
},
context=context)
