def export_to_excel(self, **kwargs): work_book = Workbook(encoding="ascii") center_alignment = Alignment() center_alignment.horz = Alignment.HORZ_RIGHT center_alignment.vert = Alignment.VERT_CENTER border = Borders() border.top = Borders.THIN border.left = Borders.THIN border.bottom = Borders.THIN border.right = Borders.THIN head_style = XFStyle() head_pattern = Pattern() head_pattern.pattern = Pattern.SOLID_PATTERN head_pattern.pattern_fore_colour = Style.colour_map['gray25'] head_style.pattern = head_pattern head_font = Font() head_font.bold = True head_style.font = head_font head_style.alignment = center_alignment head_style.borders = border data_style = XFStyle() data_style.alignment = center_alignment data_style.borders = border work_sheet = work_book.add_sheet("Sheet") limit = 10000 count = self.get_data_scale(**kwargs)['count'] times = (count + limit - 1) // limit add_header = False for i in range(1): kwargs["startIndex"] = i * limit + 1 kwargs['endIndex'] = (i + 1) * limit + 1 records = self.get_data(**kwargs) column_names = [] y = 0 for data in records: if not add_header: x = 0 for column_name in data.keys(): work_sheet.write(y, x, column_name, head_style) column_names.append(column_name) x += 1 add_header = True y += 1 x = 0 for column_name in column_names: work_sheet.write(y, x, data[column_name], data_style) x += 1 y += 1 x = 0 work_book.save(BASE_WIN_OUTPUT_PATH + "result.xls")
def output(file_or_stream, sheetname, headers, values, encoding = 'utf8', footer_text = None, footer_link = None): import xlwt book = xlwt.Workbook(encoding) sh = book.add_sheet(sheetname) datestyle = XFStyle() datestyle.num_format_str = 'DD/MM/YYYY' timestyle = XFStyle() timestyle.num_format_str = 'HH:MM:SS' header_font = Font() header_font.bold = True al = Alignment() al.horz = Alignment.HORZ_CENTER header_style = XFStyle() header_style.font = header_font header_style.alignment = al for i,header in enumerate(headers): sh.write(0, i, header, header_style) sh.set_panes_frozen(True) # frozen headings instead of split panes sh.set_horz_split_pos(1) # in general, freeze after last heading row sh.set_remove_splits(True) # if user does unfreeze, don't leave a split there for j, row in enumerate(values): for i, value in enumerate(row): if value.__class__ == date: sh.write(j+1, i, value, datestyle) elif value.__class__ == time: sh.write(j+1, i, value, timestyle) else: sh.write(j+1, i, value) if footer_link and footer_text: link_font = Font() link_font.name = 'Verdana' link_font.colour_index = 4 link_font.height = 20*8 al = Alignment() al.horz = Alignment.HORZ_CENTER al.vert = Alignment.VERT_BOTTOM link_style = XFStyle() link_style.font = link_font link_style.alignment = al row = len(values) + 1 sh.write_merge(row, row, 0, len(headers)-1, Formula('HYPERLINK("' + footer_link + '";"' + footer_text + '")'), link_style) book.save(file_or_stream)
def excel_writer(filenames): style2 = XFStyle() style2.num_format_str = '####.##0' al = Alignment() al.horz = Alignment.HORZ_CENTER al.vert = Alignment.VERT_BOTTOM style2.alignment = al style3 = XFStyle() style3.num_format_str = '0.00%' style3.alignment = al wb = Workbook() sheet1 = wb.add_sheet("Sheet 1") index = 0 columns_list = ['c', 'e', 'f', 'g', 'h', 'i', 'k', 'l', 'm'] if True: print(f"{bcolors.WARNING}PLEASE INPUT THE 9 COLUMN LETTERS YOU NEED SEPARATED WITH A COMMA{bcolors.ENDC}") print(f"{bcolors.WARNING}Example: D,A,B,C,F,G,E,K,W{bcolors.ENDC}") columns = input() while invalid(columns): print(f"{bcolors.FAIL}INVALID INPUT{bcolors.ENDC}") print(f"{bcolors.WARNING}Example: D,A,B,C{bcolors.ENDC}") columns = input() columns_list = re.split(',', columns) sheet1.write(index, letters_to_indexes(columns_list[0]), "Concurrent Users") sheet1.write(index, letters_to_indexes(columns_list[1]), "Average Total Execution Time (sec)") sheet1.write(index, letters_to_indexes(columns_list[2]), "90% Total Execution Time (sec)") sheet1.write(index, letters_to_indexes(columns_list[3]), "Min Total Execution Time (sec)") sheet1.write(index, letters_to_indexes(columns_list[4]), "Max Total Execution Time (sec)") sheet1.write(index, letters_to_indexes(columns_list[5]), "Number of Calls") sheet1.write(index, letters_to_indexes(columns_list[6]), "Error Rate (%)") sheet1.write(index, letters_to_indexes(columns_list[7]), "Date") sheet1.write(index, letters_to_indexes(columns_list[8]), "Start Time") index = 1 for file in filenames: print(file) numtuple = getnumbers(file) print(numtuple) sheet1.write(index, letters_to_indexes(columns_list[0]), numtuple[6]) sheet1.write(index, letters_to_indexes(columns_list[1]), numtuple[0], style2) sheet1.write(index, letters_to_indexes(columns_list[2]), numtuple[1], style2) sheet1.write(index, letters_to_indexes(columns_list[3]), numtuple[2], style2) sheet1.write(index, letters_to_indexes(columns_list[4]), numtuple[3], style2) sheet1.write(index, letters_to_indexes(columns_list[5]), numtuple[4]) sheet1.write(index, letters_to_indexes(columns_list[6]), numtuple[5], style3) sheet1.write(index, letters_to_indexes(columns_list[7]), str(date.today())) sheet1.write(index, letters_to_indexes(columns_list[8]), "4:42:00") index += 1 wb.save("example.xls")
def def_style(): style = XFStyle() font = Font() # 这部分设置字体 font.name = 'Times New Roman' # 或者换成外面传进来的参数,这样可以使一个函数定义所有style # font.bold = 'True' # font.height = 24 # font.size = 2000 # font.colour_index = 3 style.font = font alignment = Alignment() # 这部分设置居中格式 alignment.horz = Alignment.HORZ_CENTER # 水平居中 alignment.vert = Alignment.VERT_CENTER # 垂直居中 style.alignment = alignment return style
def __init__(self): self.style = XFStyle() base_font = Font() # 设置基本字体 base_font.height = FONT_SIZE_UNIT * 12 self.style.font = base_font alignment = Alignment() # 设置对齐 alignment.horz = Alignment.HORZ_CENTER alignment.vert = Alignment.VERT_CENTER self.style.alignment = alignment borders = Borders() # 设置边框 borders.left = Borders.THIN borders.right = Borders.THIN borders.top = Borders.THIN borders.bottom = Borders.THIN borders.left_colour = COLOUR_BLACK borders.right_colour = COLOUR_BLACK borders.top_colour = COLOUR_BLACK borders.bottom_colour = COLOUR_BLACK self.style.borders = borders self.style.num_format_str = '#,##0' # 设置数字格式
def get_sheet_title_style(bg_color=0x39, font_color=0x0, font_size=300, has_pattern=True, horz_center=True): fnt = Font() fnt.name = 'Arial' fnt.colour_index = font_color fnt.bold = True fnt.height = font_size borders = Borders() borders.left = 1 borders.right = 1 borders.top = 1 borders.bottom = 1 al = Alignment() if horz_center: al.horz = Alignment.HORZ_CENTER else: al.horz = Alignment.HORZ_LEFT al.vert = Alignment.VERT_CENTER pattern = None if has_pattern: pattern = Pattern() pattern.pattern = 1 pattern.pattern_fore_colour = bg_color pattern.pattern_back_colour = bg_color style = XFStyle() style.font = fnt style.borders = borders style.alignment = al if pattern: style.pattern = pattern return style
def main(): lst = [] path = r'./Homework/03/' with open(path + 'test.txt', 'r') as fpin: title = fpin.readline().strip().replace('\n', '').split(',') while True: s = fpin.readline().strip().replace('\n', '').split(',') if s == ['']: break lst.append(s) fpin.close() book = Workbook() sheet1 = book.add_sheet('data') style = XFStyle() font = Font() font.name = '宋体' font.height = 280 style.font = font alignment = Alignment() alignment.horz = Alignment.HORZ_CENTER alignment.vert = Alignment.VERT_CENTER style.alignment = alignment row = sheet1.row(0) for i, j in zip(range(len(title)), title): row.write(i, j, style=style) for i, j in zip(range(1, len(lst) + 1), lst): row = sheet1.row(i) for x, y in zip(range(len(j)), j): row.write(x, y, style=style) book.save(path + 'data.xls')
def create_excel(work_excel, withdraw_info=[]): title_fnt = Font() title_fnt.height = 0x0140 title_fnt.name = u'宋体' title_fnt.bold = True brd = Borders() brd.bottom = 1 brd.top = 1 brd.left = 1 brd.right = 1 title_location = Alignment() title_location.horz = Alignment.HORZ_CENTER title_location.vert = Alignment.VERT_CENTER title_style = XFStyle() title_style.font = title_fnt title_style.alignment = title_location style = XFStyle() style.font.height = 0x00E0 style.font.name = u'宋体' style.font.bold = False style.alignment.horz = Alignment.HORZ_CENTER style.alignment.vert = Alignment.VERT_CENTER content_title_style = deepcopy(style) content_title_style.alignment.horz = Alignment.HORZ_LEFT content_title_style.font.height = 0x00E0 content_style = deepcopy(style) content_style.alignment.horz = Alignment.HORZ_LEFT center_content_style = deepcopy(style) center_content_style.alignment.horz = Alignment.HORZ_CENTER center_content_style.borders = brd style.borders = brd content_style.borders = brd merge_up_style = deepcopy(style) merge_up_style.borders.bottom = 0 content_up_style = deepcopy(merge_up_style) content_up_style.alignment.horz = Alignment.HORZ_CENTER merge_down_style = deepcopy(style) merge_down_style.borders.top = 0 content_down_style = deepcopy(merge_down_style) content_down_style.alignment.horz = Alignment.HORZ_RIGHT content_down_style.font.height = 0x00CA for i, info in enumerate(withdraw_info): width = i * 25 work_excel.write_merge(2 + width, 2 + width, 0, 16, u'商户提现付款申请单', title_style) work_excel.write_merge(3 + width, 3 + width, 1, 5, info['supplier_type'], content_title_style) work_excel.write_merge(3 + width, 3 + width, 6, 16, info['supplier'], content_title_style) work_excel.write(4 + width, 0, u'收款单位名称', style) work_excel.write_merge(4 + width, 4 + width, 1, 5, info['company'], content_style) work_excel.write(4 + width, 6, u'申请提现帐号', style) work_excel.write_merge(4 + width, 4 + width, 7, 16, info['applier'], center_content_style) work_excel.write(5 + width, 0, u'开户银行', style) work_excel.write_merge(5 + width, 5 + width, 1, 5, info['bank'], content_style) work_excel.write(5 + width, 6, u'申请提现日期', style) work_excel.write_merge(5 + width, 5 + width, 7, 16, info['apply_time'], center_content_style) work_excel.write(6 + width, 0, u'银行帐号', style) work_excel.write_merge(6 + width, 6 + width, 1, 5, info['account'], content_style) work_excel.write(6 + width, 6, u'合同账期', style) work_excel.write_merge(6 + width, 6 + width, 7, 16, info['payment_day_type'], center_content_style) work_excel.write(7 + width, 0, u'付款用途', style) work_excel.write_merge(7 + width, 7 + width, 1, 5, u'商户提现', content_style) work_excel.write(7 + width, 6, u'付款日期', style) work_excel.write_merge(7 + width, 7 + width, 7, 16, info['date'], center_content_style) units = [u'千', u'百', u'十', u'万', u'千', u'百', u'十', u'元', u'角', u'分'] digit = list(str(info['money'])) digit.remove('.') digit.reverse() for j, unit in enumerate(units): work_excel.write(8 + width, 7 + j, unit, content_style) for index, value in enumerate(digit): work_excel.write(9 + width, 16 - index, value, content_style) work_excel.write(9 + width, 16 - index - 1, '¥', content_style) work_excel.write_merge(10 + width, 11 + width, 0, 0, u'备注事项', style) work_excel.write_merge(10 + width, 10 + width, 1, 6, '', content_style) work_excel.write_merge(10 + width, 10 + width, 7, 16, '', style) work_excel.write_merge(11 + width, 11 + width, 1, 16, info['print_info'], content_down_style) work_excel.write(8 + width, 0, u'付款金额', merge_up_style) work_excel.write(9 + width, 0, u'人民币(大写)', merge_down_style) work_excel.write_merge(8 + width, 9 + width, 1, 6, info['china_money'], content_style) work_excel.write_merge(13 + width, 13 + width, 0, 2, u'部门主管:', content_title_style) work_excel.write_merge(13 + width, 13 + width, 3, 5, u'财务经理:', content_title_style) work_excel.write_merge(13 + width, 13 + width, 6, 16, u'公司总经理:', content_title_style) for row in range(2, 12): work_excel.row(row + width).height_mismatch = 1 work_excel.row(row + width).height = 478 work_excel.row(2 + width).height_mismatch = 1 work_excel.row(2 + width).height = 1000 work_excel.col(0).width = 3871 work_excel.col(1).width = 1771 work_excel.col(2).width = 1509 work_excel.col(3).width = 2348 work_excel.col(4).width = 840 work_excel.col(5).width = 3241 work_excel.col(6).width = 3441 for col in range(7, 17): work_excel.col(col).width = 709
def write_sheet(self, data, sheet_name, print_to_screen=False): '''Write a very simple table to a new sheet in a spreadsheet, Optionally, print the table to the screen''' # most cells al = Alignment() al.horz = Alignment.HORZ_RIGHT al.vert = Alignment.VERT_CENTER font = Font() font.name = 'Arial' font.height = 9 * 20 # 9 pt style = XFStyle() style.font = font style.alignment = al # tops cells al = Alignment() al.horz = Alignment.HORZ_CENTER al.vert = Alignment.VERT_CENTER font = Font() font.name = 'Arial' font.bold = True font.height = 9 * 20 # 9 pt style_top = XFStyle() style_top.font = font style_top.alignment = al # left cells al = Alignment() al.horz = Alignment.HORZ_LEFT al.vert = Alignment.VERT_CENTER font = Font() font.name = 'Arial' font.bold = True font.italic = True font.height = 9 * 20 # 9 pt style_left = XFStyle() style_left.font = font style_left.alignment = al ws = self.add_sheet(sheet_name) for i, row in enumerate(data): for j, cell in enumerate(row): borders = Borders() if i == 0: borders.top = 1 borders.bottom = 2 if i == len(row) - 1: borders.bottom = 1 if j == 0: borders.left = 1 borders.right = 1 if j == len(row) - 1: borders.right = 1 if j == 0: _style = style_left elif i == 0: _style = style_top else: _style = style _style.borders = borders ws.write(i + 1, j + 1, cell, _style) if print_to_screen: print print_table(data, sheet_name, bold=True)
def report_get(self,cr,uid,ids,context=None): this=self.browse(cr,uid,ids[0]) year = this.year_id.name year_id = this.year_id.id month = this.month if month == '1': month_name = 'January' elif month == '2': month_name = 'February' elif month == '3': month_name = 'March' elif month == '4': month_name = 'April' elif month == '5': month_name = 'May' elif month == '6': month_name = 'June' elif month == '7': month_name = 'July' elif month == '8': month_name = 'August' elif month == '9': month_name = 'September' elif month == '10': month_name = 'October' elif month == '11': month_name = 'November' elif month == '12': month_name = 'December' else: raise osv.except_osv(_('Warning !'),_("Specify month correctly. ")) #Define the font attributes for header fnt = Font() fnt.name = 'Arial' fnt.height= 275 #Define the font attributes for header content_fnt = Font() content_fnt.name ='Arial' content_fnt.height =220 align_content = Alignment() align_content.horz= Alignment.HORZ_CENTER borders = Borders() borders.left = 0x02 borders.right = 0x02 borders.top = 0x02 borders.bottom = 0x02 #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 fnt1 = Font() fnt1.name = 'Arial' fnt1.height= 275 #Define the font attributes for header content_fnt1 = Font() content_fnt1.name ='Arial' content_fnt1.height =220 align_content1 = Alignment() align_content1.horz= Alignment.HORZ_CENTER borders1 = Borders() borders1.left = 0x02 borders1.right = 0x02 borders1.top = 0x02 borders1.bottom = 0x02 #The text should be centrally aligned align1 = Alignment() align1.horz = Alignment.HORZ_CENTER align1.vert = Alignment.VERT_CENTER #We set the backgroundcolour here pattern1 = Pattern() pattern1.pattern = Pattern.SOLID_PATTERN pattern1.pattern_fore_colour = 0x32 #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 style_content= XFStyle() style_content.alignment = align_content style_content.font = content_fnt month_name = 'Payment ('+str(month_name)+')' wb = Workbook() ws = wb.add_sheet('Budget') ws.row(0).height=500 ws.write(0,0,'Department Name',style_header) ws.col(0).width = 8000 ws.write(0,1,'Department HoD',style_header) ws.col(1).width = 8000 ws.write(0,2,'Employee Name',style_header) ws.col(2).width = 8000 ws.write(0,3,'Salary Amount',style_header) ws.col(3).width = 5000 ws.write(0,4,month_name,style_header) ws.col(4).width = 8000 # ws.write(0,5,'O.T. Amount',style_header) # ws.col(5).width = 4400 # ws.write(0,6,'Total Amount',style_header) # ws.col(6).width = 4400 # ws.write(0,7,'Insentive Amount',style_header) # ws.col(7).width = 4400 # ws.write(0,8,'Deduction Amount',style_header) # ws.col(8).width = 5000 # ws.write(0,9,'Percentage',style_header) # ws.col(9).width = 4400 emp_ids = [] emp_obj=self.pool.get('hr.employee') pay_obj=self.pool.get('salary.payment.line') if this.dept_id: cr.execute("select emp.id from hr_employee as emp left join resource_resource\ as res on (emp.resource_id=res.id) where emp.department_id = \ '"+str(this.dept_id.id)+"' and emp.department_id is not null and \ res.active=True order by emp.department_id") temp = cr.fetchall() for data in temp: if len(data)>0 and data[0] != None: emp_ids.append(data[0]) else: cr.execute("select emp.id from hr_employee as emp left join resource_resource \ as res on (emp.resource_id=res.id) where res.active=True order by emp.department_id") temp = cr.fetchall() for data in temp: if len(data)>0 and data[0] != None: emp_ids.append(data[0]) holiday_obj = self.pool.get('holiday.list') if int(month) in [1,3,5,7,8,10,12]: month = 31 if int(month) in [4,6,9,11]: month = 30 if int(month) in [2]: if int(year) % 4 == 0: month = 29 else: month = 28 off_day = working_day = 0 holiday_ids = holiday_obj.search(cr, uid, [('month','=',this.month),('year_id','=',year_id)]) for line in holiday_obj.browse(cr, uid, holiday_ids): off_day = line.holiday working_day = month - off_day i=0 dept_dict = {} grand = total = pay_total = pay_grand = budget = 0.0 pay_data = False flag = True for each in emp_obj.browse(cr, uid, emp_ids): pay_ids = pay_obj.search(cr, uid, [('employee_id','=',each.id),('month','=',this.month),('year_id','=',this.year_id.id)]) if pay_ids: pay_data = pay_obj.browse(cr, uid, pay_ids[0]) i+=1 if dept_dict.has_key(str(each.department_id.id)): if each.department_id: salary = 0.0 dept = '[' + str(each.department_id.dept_code) +'] '+ str(each.department_id.name) ws.write(i,0, dept) ws.write(i,1, each.department_id.manager_id and each.department_id.manager_id.name or '') name = '[' + str(each.sinid) +'] '+ str(each.name) ws.write(i,2, name) if each.daily: salary = each.salary * working_day else: salary = each.salary ws.write(i,3, salary) if pay_data and pay_data.employee_id.id == each.id: ws.write(i,4,pay_data.total_amount or 0.0) else: ws.write(i,4, 0.0) total += salary grand += salary if pay_data and pay_data.employee_id.id == each.id: pay_total += pay_data.total_amount or 0.0 pay_grand += pay_data.total_amount or 0.0 else: pay_total += 0.0 pay_grand += 0.0 elif not each.department_id: salary = 0.0 if flag: ws.write(i,0, 'Allocated Budget',style_header) ws.write(i,1, 0.0,style_header) ws.write(i,2, 'Total',style_header) ws.write(i,3, total,style_header) if pay_total: ws.write(i,4,pay_total,style_header) else: ws.write(i,4, 0.0,style_header) if budget: diff = pay_total - float(budget) else: diff = pay_total ws.write(i,5, diff,style_header1) flag = False i += 2 total = pay_total = budget = 0.0 name = '[' + str(each.sinid) +'] '+ str(each.name) ws.write(i,0, 'X Department') ws.write(i,1,'X Reporting Officer') ws.write(i,2, name) if each.daily: salary = each.salary * working_day else: salary = each.salary ws.write(i,3, salary) if pay_data and pay_data.employee_id.id == each.id: ws.write(i,4,pay_data.total_amount or 0.0) else: ws.write(i,4, 0.0) total += salary grand += salary if pay_data and pay_data.employee_id.id == each.id: pay_total += pay_data.total_amount or 0.0 pay_grand += pay_data.total_amount or 0.0 else: pay_total += 0.0 pay_grand += 0.0 else: dept_dict[str(each.department_id.id)] = '' if i != 1: ws.write(i,0, 'Allocated Budget',style_header) if budget: ws.write(i,1, budget,style_header) else: budget = 0.0 ws.write(i,1,budget,style_header) ws.write(i,2, 'Total',style_header) ws.write(i,3, total,style_header) if pay_total: ws.write(i,4,pay_total,style_header) else: ws.write(i,4, 0.0,style_header) if budget: diff = pay_total - float(budget) else: diff = pay_total ws.write(i,5, diff,style_header1) i += 2 total = pay_total = budget = 0.0 if each.department_id: salary = 0.0 budget = each.department_id.dept_budget dept = '[' + str(each.department_id.dept_code) +'] '+ str(each.department_id.name) ws.write(i,0, dept) ws.write(i,1, each.department_id.manager_id and each.department_id.manager_id.name or '') name = '[' + str(each.sinid) +'] '+ str(each.name) ws.write(i,2, name) if each.daily: salary = each.salary * working_day else: salary = each.salary ws.write(i,3, salary) if pay_data and pay_data.employee_id.id == each.id: ws.write(i,4,pay_data.total_amount or 0.0) else: ws.write(i,4, 0.0) total += salary grand += salary if pay_data and pay_data.employee_id.id == each.id: pay_total += pay_data.total_amount or 0.0 pay_grand += pay_data.total_amount or 0.0 else: pay_total += 0.0 pay_grand += 0.0 i += 1 ws.write(i+1,2, 'Total',style_header) ws.write(i+1,3, total,style_header) ws.write(i+3,2, 'Grand Total',style_header) ws.write(i+3,3, grand,style_header) ws.write(i+1,4, pay_total,style_header) ws.write(i+3,4, pay_grand,style_header) diff_pay = pay_total - total diff_grand = pay_grand - grand ws.write(i+1,5, diff_pay,style_header1) ws.write(i+3,5, diff_grand,style_header1) f = cStringIO.StringIO() wb.save(f) out=base64.encodestring(f.getvalue()) return self.write(cr, uid, ids, {'export_data':out, 'filename':'export.xls'}, context=context)
def detail_budget(self,cr,uid,ids,context=None): this=self.browse(cr,uid,ids[0]) year = this.year_id.name year_id = this.year_id.id month = this.month if month == '1': month_name = 'January' elif month == '2': month_name = 'February' elif month == '3': month_name = 'March' elif month == '4': month_name = 'April' elif month == '5': month_name = 'May' elif month == '6': month_name = 'June' elif month == '7': month_name = 'July' elif month == '8': month_name = 'August' elif month == '9': month_name = 'September' elif month == '10': month_name = 'October' elif month == '11': month_name = 'November' elif month == '12': month_name = 'December' else: raise osv.except_osv(_('Warning !'),_("Specify month correctly. ")) if int(month) in [1,3,5,7,8,10,12]: join_date=year +'-'+month+'-'+'31' if int(month) in [4,6,9,11]: join_date=year +'-'+month+'-'+'30' if int(month) in [2]: if int(year) % 4 == 0: join_date=year +'-'+month+'-'+'29' else: join_date=year +'-'+month+'-'+'28' #Define the font attributes for header fnt = Font() fnt.name = 'Arial' fnt.height= 275 #Define the font attributes for header content_fnt = Font() content_fnt.name ='Arial' content_fnt.height =220 align_content = Alignment() align_content.horz= Alignment.HORZ_CENTER borders = Borders() borders.left = 0x02 borders.right = 0x02 borders.top = 0x02 borders.bottom = 0x02 #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 fnt1 = Font() fnt1.name = 'Arial' fnt1.height= 275 #Define the font attributes for header content_fnt1 = Font() content_fnt1.name ='Arial' content_fnt1.height =220 align_content1 = Alignment() align_content1.horz= Alignment.HORZ_CENTER borders1 = Borders() borders1.left = 0x02 borders1.right = 0x02 borders1.top = 0x02 borders1.bottom = 0x02 #The text should be centrally aligned align1 = Alignment() align1.horz = Alignment.HORZ_CENTER align1.vert = Alignment.VERT_CENTER #We set the backgroundcolour here pattern1 = Pattern() pattern1.pattern = Pattern.SOLID_PATTERN pattern1.pattern_fore_colour = 0x32 #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 style_content= XFStyle() style_content.alignment = align_content style_content.font = content_fnt month_name = 'Payment ('+str(month_name)+')' wb = Workbook() ws = wb.add_sheet('Budget') ws.row(0).height=500 ws.write(0,0,'Department Name',style_header) ws.col(0).width = 8000 ws.write(0,1,'Department HoD',style_header) ws.col(1).width = 8000 ws.write(0,2,'RO of HOD',style_header) ws.col(2).width = 8000 ws.write(0,3,'Employee Name',style_header) ws.col(3).width = 9000 ws.write(0,4,'Designation',style_header) ws.col(4).width = 8000 ws.write(0,5,'Working Days',style_header) ws.col(5).width = 5000 ws.write(0,6,'Working Hours',style_header) ws.col(6).width = 5000 ws.write(0,7,'Working Month',style_header) ws.col(7).width = 5000 ws.write(0,8,'Salary Amount',style_header) ws.col(8).width = 5000 ws.write(0,9,month_name,style_header) ws.col(9).width = 8000 # ws.write(0,5,'O.T. Amount',style_header) # ws.col(5).width = 4400 # ws.write(0,6,'Total Amount',style_header) # ws.col(6).width = 4400 # ws.write(0,7,'Insentive Amount',style_header) # ws.col(7).width = 4400 # ws.write(0,8,'Deduction Amount',style_header) # ws.col(8).width = 5000 # ws.write(0,9,'Percentage',style_header) # ws.col(9).width = 4400 emp_ids = [] emp_obj=self.pool.get('hr.employee') pay_obj=self.pool.get('salary.payment.line') if this.dept_id: cr.execute("select emp.id from hr_employee as emp left join resource_resource "\ "as res on (emp.resource_id=res.id) where emp.department_id = "\ "'"+str(this.dept_id.id)+"' and emp.department_id is not null and "\ "res.active=True and emp.joining_date <'"+str(join_date)+"' order by emp.department_id, (substring(emp.sinid, '^[0-9]+'))::int ,substring(emp.sinid, '[^0-9_].*$')") temp = cr.fetchall() for data in temp: if len(data)>0 and data[0] != None: emp_ids.append(data[0]) else: cr.execute("select emp.id from hr_employee as emp left join resource_resource "\ "as res on (emp.resource_id=res.id) where res.active=True and emp.joining_date < '"+str(join_date)+"' order by emp.department_id, "\ "(substring(emp.sinid, '^[0-9]+'))::int ,substring(emp.sinid, '[^0-9_].*$')") temp = cr.fetchall() for data in temp: if len(data)>0 and data[0] != None: emp_ids.append(data[0]) holiday_obj = self.pool.get('holiday.list') if int(month) in [1,3,5,7,8,10,12]: month = 31 if int(month) in [4,6,9,11]: month = 30 if int(month) in [2]: if int(year) % 4 == 0: month = 29 else: month = 28 off_day = working_day = 0 holiday_ids = holiday_obj.search(cr, uid, [('month','=',this.month),('year_id','=',year_id)]) for line in holiday_obj.browse(cr, uid, holiday_ids): off_day = line.holiday working_day = month - off_day i=0 dept_dict = {} grand = total = pay_total = pay_grand = budget = 0.0 pay_data = False flag = True for each in emp_obj.browse(cr, uid, emp_ids): tolal_months = 0 if not each.joining_date: continue dt1 = datetime.strptime(each.joining_date,'%Y-%m-%d') dt2 = datetime.strptime(time.strftime(DEFAULT_SERVER_DATE_FORMAT),'%Y-%m-%d') start_month=dt1.month end_months=(dt2.year-dt1.year)*12 + dt2.month+1 dates=[datetime(year=yr, month=mn, day=1) for (yr, mn) in ( ((m - 1) / 12 + dt1.year, (m - 1) % 12 + 1) for m in range(start_month, end_months) )] for val in dates: tolal_months += 1 pay_ids = pay_obj.search(cr, uid, [('employee_id','=',each.id),('month','=',this.month),('year_id','=',this.year_id.id),('salary_type','=','Salary')]) if pay_ids: pay_data = pay_obj.browse(cr, uid, pay_ids[0]) i+=1 if dept_dict.has_key(str(each.department_id.id)): if each.department_id: salary = 0.0 dept = '[' + str(each.department_id.dept_code) +'] '+ str(each.department_id.name) ws.write(i,0, dept) ws.write(i,1, each.department_id.manager_id and each.department_id.manager_id.name or '') ws.write(i,2, each.department_id.manager_id.parent_id and each.department_id.manager_id.parent_id.name or '') name = '[' + str(each.sinid) +'] '+ str(each.name) ws.write(i,3, name) ws.write(i,4, each.designation_id and each.designation_id.name or '') ws.write(i,5, pay_data.days or '0.0') ws.write(i,6, pay_data.over_time) ws.write(i,7, str(tolal_months) + ' Month') if pay_data.employee_id.daily: salary = pay_data.basic * working_day else: salary = pay_data.basic ws.write(i,8, salary) if pay_data and pay_data.employee_id.id == each.id: ws.write(i,9,pay_data.total_amount or 0.0) else: ws.write(i,9, 0.0) total += salary grand += salary if pay_data and pay_data.employee_id.id == each.id: pay_total += pay_data.total_amount or 0.0 pay_grand += pay_data.total_amount or 0.0 else: pay_total += 0.0 pay_grand += 0.0 elif not each.department_id: salary = 0.0 if flag: ws.write(i,0, 'Allocated Budget',style_header) ws.write(i,1, 0.0,style_header) ws.write(i,2, 'Total',style_header) ws.write(i,3, '',style_header) ws.write(i,4, '',style_header) ws.write(i,5, '',style_header) ws.write(i,6, '',style_header) ws.write(i,7, '',style_header) ws.write(i,8, total,style_header) if pay_total: ws.write(i,9,pay_total,style_header) else: ws.write(i,9, 0.0,style_header) if budget: diff = pay_total - float(budget) else: diff = pay_total ws.write(i,10, diff,style_header1) flag = False i += 2 total = pay_total = budget = 0.0 name = '[' + str(each.sinid) +'] '+ str(each.name) ws.write(i,0, 'X Department') ws.write(i,1,'X Reporting Officer') ws.write(i,2,'X Reporting Officer') ws.write(i,3, name) ws.write(i,4, each.designation_id and each.designation_id.name or '') ws.write(i,5, pay_data.days or '0.0') ws.write(i,6, pay_data.over_time) ws.write(i,7, str(tolal_months) + ' Month') if pay_data.employee_id.daily: salary = pay_data.basic * working_day else: salary = pay_data.basic ws.write(i,8, salary) if pay_data and pay_data.employee_id.id == each.id: ws.write(i,9,pay_data.total_amount or 0.0) else: ws.write(i,9, 0.0) total += salary grand += salary if pay_data and pay_data.employee_id.id == each.id: pay_total += pay_data.total_amount or 0.0 pay_grand += pay_data.total_amount or 0.0 else: pay_total += 0.0 pay_grand += 0.0 else: dept_dict[str(each.department_id.id)] = '' if i != 1: ws.write(i,0, 'Allocated Budget',style_header) if budget: ws.write(i,1, budget,style_header) else: budget = 0.0 ws.write(i,1,budget,style_header) ws.write(i,2, 'Total',style_header) ws.write(i,3, '',style_header) ws.write(i,4, '',style_header) ws.write(i,5, '',style_header) ws.write(i,6, '',style_header) ws.write(i,7, '',style_header) ws.write(i,8, total,style_header) if pay_total: ws.write(i,9,pay_total,style_header) else: ws.write(i,9, 0.0,style_header) if budget: diff = pay_total - float(budget) else: diff = pay_total ws.write(i,10, diff,style_header1) i += 2 total = pay_total = budget = 0.0 if each.department_id: salary = 0.0 budget = each.department_id.dept_budget dept = '[' + str(each.department_id.dept_code) +'] '+ str(each.department_id.name) ws.write(i,0, dept) ws.write(i,1, each.department_id.manager_id and each.department_id.manager_id.name or '') ws.write(i,2, each.department_id.manager_id.parent_id and each.department_id.manager_id.parent_id.name or '') name = '[' + str(each.sinid) +'] '+ str(each.name) ws.write(i,3, name) ws.write(i,4, each.designation_id and each.designation_id.name or '') ws.write(i,5, pay_data.days) ws.write(i,6, pay_data.over_time) ws.write(i,7, str(tolal_months) + ' Month') if pay_data.employee_id.daily: salary = pay_data.basic * working_day else: salary = pay_data.basic ws.write(i,8, salary) if pay_data and pay_data.employee_id.id == each.id: ws.write(i,9,pay_data.total_amount or 0.0) else: ws.write(i,9, 0.0) total += salary grand += salary if pay_data and pay_data.employee_id.id == each.id: pay_total += pay_data.total_amount or 0.0 pay_grand += pay_data.total_amount or 0.0 else: pay_total += 0.0 pay_grand += 0.0 i += 1 ws.write(i+1,7, 'Total',style_header) ws.write(i+1,8, total,style_header) ws.write(i+3,7, 'Grand Total',style_header) ws.write(i+3,8, grand,style_header) ws.write(i+1,9, pay_total,style_header) ws.write(i+3,9, pay_grand,style_header) diff_pay = pay_total - total diff_grand = pay_grand - grand ws.write(i+1,10, diff_pay,style_header1) ws.write(i+3,10, diff_grand,style_header1) f = cStringIO.StringIO() wb.save(f) out=base64.encodestring(f.getvalue()) return self.write(cr, uid, ids, {'export_data':out, 'filename':'export.xls'}, context=context)
def exportToExcel(self,objectProject): book = Workbook(); sheet1 = book.add_sheet('Sheet 1') sheet1.col(1).width = 256*20; sheet1.col(2).width = 256*80; sheet1.col(3).width = 256*10; sheet1.col(4).width = 256*20; default_book_style = book.default_style default_book_style.font.height = 20 * 36 # 36pt fnt = Font() fnt.name = 'Arial' fnt.colour_index = 4 fnt.bold = True borders = Borders() borders.left = Borders.THIN borders.right = Borders.THIN borders.top = Borders.THIN borders.bottom = Borders.THIN pattern = Pattern(); pattern.pattern = Pattern.SOLID_PATTERN pattern.pattern_fore_colour = 23 algn1 = Alignment(); algn1.wrap = 1; #algn1.horz = Alignment.HORZ_CENTER #algn1.vert = Alignment.VERT_TOP alignHeader = Alignment(); alignHeader.horz = Alignment.HORZ_CENTER; alignTop = Alignment(); alignTop.vert = Alignment.VERT_TOP print "export"; if( objectProject): i=0; print "start" ; styleHead = XFStyle(); styleHead.font = fnt; styleHead.borders = borders; styleHead.pattern = pattern; styleHead.alignment = alignHeader; row1 = sheet1.row(i) ; row1.write(0, ('risk id').decode('UTF8'),styleHead ); sheet1.write_merge(i, i, 1, 2, ('รายละเอียด').decode('UTF8') ,styleHead ); # row1.write(1, ('รายละเอียด').decode('UTF8')); row1.write(3, ('วันที่รายงาน').decode('UTF8'), styleHead ); row1.write(4, ('หน่วยที่รายงาน').decode('UTF8'), styleHead ); i=i+1; style1 = XFStyle(); style1.alignment = algn1; #style0 = xlwt.easyxf('font: name Times New Roman size 20, color-index black, bold on') for value in objectProject: row1 = sheet1.row(i) ; styleRowDetail = XFStyle(); styleRowDetail.borders = borders; styleRowDetail.alignment = alignTop; StyleRowDetailWrap = styleRowDetail ; StyleRowDetailWrap.alignment = algn1; styleDate = XFStyle() styleDate.num_format_str = 'DD-MM-YYYY' ; #'D-MMM-YY'; styleDate.borders = borders; row1.write(0, value.get('risk_management_id'),styleRowDetail ); #row1.write(1, value.get('risk_detail').decode('UTF8') , style1); sheet1.write_merge(i, i, 1, 2, value.get('risk_detail').decode('UTF8') , StyleRowDetailWrap ); row1.write(3, value.get('report_date') ,styleDate); row1.write(4, value.get('report').decode('UTF8') ,styleRowDetail ); i=i+1; row1 = sheet1.row(i) ; row1.write(0," " ); row1.write(1,('หน่วยที่เกี่ยวข้อง').decode('UTF8') ,styleHead ); sheet1.write_merge(i, i, 2, 3,('รายละเอียดการตอบ').decode('UTF8') , styleHead ); i=i+1; for sub in value.get('response') : row1 = sheet1.row(i) ; row1.write(0," " ); row1.write(1,sub.get('risk_team').decode('UTF8') , styleRowDetail ); sheet1.write_merge(i, i, 2, 3,sub.get('result').decode('UTF8') , StyleRowDetailWrap ); i=i+1; dirTempFile = gettempdir() + _os.sep + str('simple.xls'); print dirTempFile; book.save(dirTempFile);
def exportReport1ToExcel(self,objectProject): book = Workbook(); sheet1 = book.add_sheet('Sheet 1'); sheet1.col(1).width = 256*20; sheet1.col(2).width = 256*80; sheet1.col(3).width = 256*10; sheet1.col(4).width = 256*10; sheet1.col(5).width = 256*20; sheet1.col(6).width = 256*20; borders = Borders() borders.left = Borders.THIN borders.right = Borders.THIN borders.top = Borders.THIN borders.bottom = Borders.THIN pattern = Pattern(); pattern.pattern = Pattern.SOLID_PATTERN pattern.pattern_fore_colour = 23 wrap = Alignment(); wrap.wrap = 1; wrap.vert = Alignment.VERT_TOP alignHeader = Alignment(); alignHeader.horz = Alignment.HORZ_CENTER; alignTop = Alignment(); alignTop.vert = Alignment.VERT_TOP fnt = Font() fnt.name = 'Arial' fnt.colour_index = 4 fnt.bold = True styleWrap = XFStyle(); styleWrap.alignment = wrap; styleHead = XFStyle(); styleHead.font = fnt; styleHead.borders = borders; styleHead.pattern = pattern; styleHead.alignment = alignHeader; styleRowDetail = XFStyle(); styleRowDetail.borders = borders; styleRowDetail.alignment = alignTop; styleDate = XFStyle() styleDate.num_format_str = 'DD-MM-YYYY' ; #'D-MMM-YY'; styleDate.borders = borders; styleDate.alignment = alignTop; StyleRowDetailWrap = styleRowDetail ; StyleRowDetailWrap.alignment = wrap; if( objectProject): i=0; row1 = sheet1.row(i) ; row1.write(0, ('ลำดับที่').decode('UTF8') ,styleHead); #sheet1.write_merge(i, i, 1, 2, ('รายละเอียด').decode('UTF8') ); row1.write(1, ('เลขความเสี่ยง').decode('UTF8'),styleHead ); row1.write(2, ('อุบัติการณ์/ภาวะไม่พึงประสงค์').decode('UTF8'),styleHead); row1.write(3, ('วันที่รายงาน').decode('UTF8'),styleHead ); row1.write(4, ('ความรุนแรง').decode('UTF8'),styleHead ); row1.write(5, ('ด้าน/โปรแกรม').decode('UTF8') ,styleHead); row1.write(6, ('หน่วยที่รายงาน').decode('UTF8') ,styleHead); i=i+1; for value in objectProject: row1 = sheet1.row(i) ; row1.write(0, value.get('row') ,styleRowDetail ); row1.write(1, str(value.get('risk_id')).decode('UTF8'),styleRowDetail ); row1.write(2, value.get('detail').decode('UTF8'),StyleRowDetailWrap ); row1.write(3, value.get('report_date') ,styleDate ); row1.write(4, value.get('level').decode('UTF8') ,styleRowDetail ); row1.write(5, value.get('pro').decode('UTF8') ,styleRowDetail ); row1.write(6, value.get('reporter').decode('UTF8') ,styleRowDetail ); i=i+1; row2 = sheet1.row(i) ; row2.write(2, ('รายละเอียด').decode('UTF8'),styleHead); row2.write(3, ('หน่วยที่ตอบ').decode('UTF8'),styleHead ); row2.write(4, ('ระยะเวลาตอบ').decode('UTF8'),styleHead ); i=i+1; for resp in value.get('responsible'): row2 = sheet1.row(i) ; row2.write(2, str(resp.get('detail')).decode('UTF8'),StyleRowDetailWrap); row2.write(3, str(resp.get('service_name')).decode('UTF8'),styleRowDetail ); row2.write(4, str(resp.get('report_date')).decode('UTF8'),styleRowDetail ); i=i+1; dirTempFile = gettempdir() + _os.sep + str('simpleReport1.xls'); book.save(dirTempFile); return dirTempFile;
def performance_register_report(self, cr, uid, ids, data, context=None): obj = self.browse(cr, uid, ids) emp_obj = self.pool.get('hr.employee') f_name = '' d_name = '' wb = Workbook() ws = wb.add_sheet('Payment Bonus') total_salary = apr_salary = may_salary = june_salary = july_salary = aug_salary = sep_salary = oct_salary = nov_salary = dec_salary = jan_salary = feb_salary = mar_salary = 0 fnt1 = Font() fnt1.name = 'Arial' fnt1.height = 300 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 fnt = Font() fnt.name = 'Arial' fnt.height = 275 content_fnt = Font() content_fnt.name = 'Arial' content_fnt.height = 150 align_content = Alignment() align_content.horz = Alignment.HORZ_CENTER borders = Borders() borders.left = 0x02 borders.right = 0x02 borders.top = 0x02 borders.bottom = 0x00 align = Alignment() align.horz = Alignment.HORZ_CENTER align.vert = Alignment.VERT_CENTER pattern = Pattern() pattern.pattern = Pattern.SOLID_PATTERN pattern.pattern_fore_colour = 0x1F style_header = XFStyle() style_header.font = fnt style_header.pattern = pattern style_header.borders = borders style_header.alignment = align fnt7 = Font() fnt7.name = 'Arial' fnt7.height = 275 content_fnt7 = Font() content_fnt7.name = 'Arial' content_fnt7.height = 150 align_content7 = Alignment() align_content7.horz = Alignment.HORZ_CENTER borders7 = Borders() borders7.left = 0x02 borders7.right = 0x02 borders7.top = 0x02 borders7.bottom = 0x02 align7 = Alignment() align7.horz = Alignment.HORZ_CENTER align7.vert = Alignment.VERT_CENTER pattern7 = Pattern() pattern7.pattern7 = Pattern.SOLID_PATTERN pattern7.pattern7_fore_colour = 0x1F style_header7 = XFStyle() style_header7.font = fnt style_header7.pattern = pattern style_header7.borders = borders style_header7.alignment = align fnt2 = Font() fnt2.name = 'Arial' fnt2.height = 275 content_fnt2 = Font() content_fnt2.name = 'Arial' content_fnt2.height = 150 fnt2.bold = True align_content2 = Alignment() align_content2.horz = Alignment.HORZ_CENTER borders2 = Borders() borders2.left = 0x00 borders2.right = 0x02 borders2.top = 0x02 borders2.bottom = 0x02 align2 = Alignment() align2.horz = Alignment.HORZ_CENTER align2.vert = Alignment.VERT_CENTER pattern2 = Pattern() pattern2.pattern2 = Pattern.SOLID_PATTERN pattern2.pattern2_fore_colour = 0x1F style_header2 = XFStyle() style_header2.font = fnt2 style_header2.pattern = pattern2 style_header2.borders = borders2 style_header2.alignment = align2 fnt3 = Font() fnt3.name = 'Arial' fnt3.height = 300 fnt3.bold = True align_content3 = Alignment() align_content3.horz = Alignment.HORZ_CENTER borders3 = Borders() borders3.left = 0x00 borders3.right = 0x02 borders3.top = 0x02 borders3.bottom = 0x02 align3 = Alignment() align3.horz = Alignment.HORZ_CENTER align3.vert = Alignment.VERT_CENTER pattern3 = Pattern() pattern3.pattern3 = Pattern.SOLID_PATTERN pattern3.pattern3_fore_colour = 0x1F style_header3 = XFStyle() style_header3.font = fnt3 style_header3.pattern = pattern3 style_header3.borders = borders3 style_header3.alignment = align3 fnt6 = Font() fnt6.name = 'Arial' fnt6.height = 275 content_fnt6 = Font() content_fnt6.name = 'Arial' content_fnt6.height = 150 align_content6 = Alignment() align_content6.horz = Alignment.HORZ_CENTER borders6 = Borders() borders6.left = 0x02 borders6.right = 0x02 # borders6.top = 0x00 # borders6.bottom = 0x00 align6 = Alignment() align6.horz = Alignment.HORZ_CENTER align6.vert = Alignment.VERT_CENTER pattern6 = Pattern() pattern6.pattern = Pattern.SOLID_PATTERN pattern6.pattern_fore_colour = 0x1F style_header6 = XFStyle() style_header6.font = fnt style_header6.pattern = pattern style_header6.borders = borders6 style_header6.alignment = align fnt5 = Font() fnt5.name = 'Arial' fnt5.height = 200 content_fnt5 = Font() content_fnt5.name = 'Arial' content_fnt5.height = 150 align_content5 = Alignment() align_content5.horz = Alignment.HORZ_CENTER borders5 = Borders() borders5.left = 0x02 borders5.right = 0x02 borders5.top = 0x02 borders5.bottom = 0x02 align5 = Alignment() align5.horz = Alignment.HORZ_CENTER align5.vert = Alignment.VERT_CENTER pattern5 = Pattern() # pattern5.pattern = Pattern.SOLID_PATTERN # pattern5.pattern_fore_colour = 0x1F style_header5 = XFStyle() style_header5.font = fnt5 style_header5.pattern = pattern5 style_header5.borders = borders5 style_header5.alignment = align5 if obj.company_id: get_name = obj.company_id.name + ' ' + obj.company_id.street + ' ' + ',' + obj.company_id.city + ' ' + '-' + obj.company_id.zip else: get_name = obj.employee_id.resource_id.company_id.name + ' ' + obj.employee_id.resource_id.company_id.street + ' ' + ',' + obj.employee_id.resource_id.company_id.city + ' ' + '-' + obj.employee_id.resource_id.company_id.zip date1 = datetime.strptime(obj.from_date, "%Y-%m-%d").timetuple().tm_year date2 = datetime.strptime(obj.till_date, "%Y-%m-%d").timetuple().tm_year if date1 == date2: d_name = 'BONUS PAID TO EMPLOYEES FOR THE ACCOUNTING YEAR ON THE' + ' - ' + str( date1) + ' ' + '[See Rule 4(b)]' else: d_name = 'BONUS PAID TO EMPLOYEES FOR THE ACCOUNTING YEAR ON THE' + ' ' + str( date1) + ' ' + '-' + ' ' + str(date2) + ' ' + '[See Rule 4(b)]' ws.row(0).height = 500 ws.row(1).height = 500 ws.write_merge(0, 0, 0, 2, 'FORM C', style_header1) ws.write_merge(0, 0, 3, 10, d_name, style_header1) ws.write_merge(1, 1, 0, 2, 'Name of the Establishment', style_header1) ws.write_merge(1, 1, 3, 10, get_name, style_header1) ws.write_merge(1, 1, 11, 19, 'No. of Working days in the Year', style_header1) ws.col(0).width = 5000 ws.col(1).width = 7500 ws.col(2).width = 5000 ws.col(3).width = 7000 ws.col(4).width = 5000 ws.col(5).width = 8000 ws.col(6).width = 6000 ws.col(7).width = 8000 ws.col(8).width = 8000 ws.col(9).width = 9000 ws.col(10).width = 8000 ws.col(11).width = 9000 ws.col(12).width = 7000 ws.col(13).width = 7000 ws.col(14).width = 5000 ws.col(15).width = 5000 ws.col(16).width = 7000 ws.col(17).width = 3000 ws.col(18).width = 3000 ws.col(19).width = 3000 ws.col(20).width = 3000 ws.col(21).width = 3000 ws.col(22).width = 3000 ws.col(23).width = 3000 ws.col(24).width = 3000 ws.col(25).width = 3000 ws.col(26).width = 3000 ws.col(27).width = 3000 ws.col(28).width = 3000 ws.col(29).width = 4000 ws.row(2).height = 400 ws.write(2, 0, 'EMP. CODE', style_header) ws.write(2, 1, 'NAME', style_header) ws.write(2, 2, 'JOINING DATE', style_header) ws.write(2, 3, 'Father Name', style_header) ws.write(2, 4, 'Designation', style_header) ws.write(2, 5, 'Whether he has ', style_header) ws.write(2, 6, 'No.of days', style_header) ws.write(2, 7, 'Total Salary or wages', style_header) ws.write(2, 8, 'Account of bonus payable', style_header) ws.write_merge(2, 2, 9, 12, 'Deduction', style_header7) ws.write(2, 13, 'Net amount payable', style_header) ws.write(2, 14, 'Amount actualy', style_header) ws.write(2, 15, 'Date on which ', style_header) ws.write(2, 16, 'Signature/ Thumb ', style_header) ws.write(2, 17, 'Remarks', style_header) ws.write_merge(2, 2, 18, 19, 'APRIL', style_header7) ws.write_merge(2, 2, 20, 21, 'MAY', style_header7) ws.write_merge(2, 2, 22, 23, 'JUNE', style_header7) ws.write_merge(2, 2, 24, 25, 'JULY', style_header7) ws.write_merge(2, 2, 26, 27, 'AUGUST', style_header7) ws.write_merge(2, 2, 28, 29, 'SEPTEMBER', style_header7) ws.write_merge(2, 2, 30, 31, 'OCTOBER', style_header7) ws.write_merge(2, 2, 32, 33, 'NOVEMBER', style_header7) ws.write_merge(2, 2, 34, 35, 'DECEMBER', style_header7) ws.write_merge(2, 2, 36, 37, 'JANUARY', style_header7) ws.write_merge(2, 2, 38, 39, 'FEBRUARY', style_header7) ws.write_merge(2, 2, 40, 41, 'MARCH', style_header7) ws.write_merge(2, 2, 42, 43, 'TOTAL', style_header7) ws.write(2, 44, 'BONUS', style_header) ws.row(3).height = 400 ws.write(3, 0, '', style_header6) ws.write(3, 1, '', style_header6) ws.write(3, 2, '', style_header6) ws.write(3, 3, '', style_header6) ws.write(3, 4, '', style_header6) ws.write(3, 5, 'completed 15 year of', style_header6) ws.write(3, 6, 'worked in the', style_header6) ws.write(3, 7, 'in respect of', style_header6) ws.write(3, 8, 'under section 10', style_header6) ws.write(3, 9, 'Puja bonus or other customary', style_header7) ws.write(3, 10, 'Interim bonus', style_header7) ws.write(3, 11, 'Deduction on account of financial', style_header7) ws.write(3, 12, 'Total sum deducted', style_header7) ws.write(3, 13, '(Col.8 minus Col.12)', style_header6) ws.write(3, 14, 'paid', style_header6) ws.write(3, 15, 'paid', style_header6) ws.write(3, 16, 'impression', style_header6) ws.write(3, 17, '', style_header6) ws.write(3, 18, '', style_header7) ws.write(3, 19, '', style_header7) ws.write(3, 20, '', style_header7) ws.write(3, 21, '', style_header7) ws.write(3, 22, '', style_header7) ws.write(3, 23, '', style_header7) ws.write(3, 24, '', style_header7) ws.write(3, 25, '', style_header7) ws.write(3, 26, '', style_header7) ws.write(3, 27, '', style_header7) ws.write(3, 28, '', style_header7) ws.write(3, 29, '', style_header7) ws.write(3, 30, '', style_header7) ws.write(3, 31, '', style_header7) ws.write(3, 32, '', style_header7) ws.write(3, 33, '', style_header7) ws.write(3, 34, '', style_header7) ws.write(3, 35, '', style_header7) ws.write(3, 36, '', style_header7) ws.write(3, 37, '', style_header7) ws.write(3, 38, '', style_header7) ws.write(3, 39, '', style_header7) ws.write(3, 40, '', style_header7) ws.write(3, 41, '', style_header7) ws.write(3, 42, '', style_header7) ws.write(3, 43, '', style_header7) ws.write(3, 44, '', style_header7) ws.row(4).height = 400 ws.write(4, 0, '', style_header6) ws.write(4, 1, '', style_header6) ws.write(4, 2, '', style_header6) ws.write(4, 3, '', style_header6) ws.write(4, 4, '', style_header6) ws.write(4, 5, 'age at the beginning', style_header6) ws.write(4, 6, 'Establishment', style_header6) ws.write(4, 7, 'the accounting year', style_header6) ws.write(4, 8, 'or section 11', style_header6) ws.write(4, 9, 'bonus paid during', style_header6) ws.write(4, 10, 'or bonus paid in ', style_header6) ws.write(4, 11, 'loss if any caused by', style_header6) ws.write(4, 12, '(Col.9,10 and 11)', style_header6) ws.write(4, 13, '', style_header6) ws.write(4, 14, '', style_header6) ws.write(4, 15, '', style_header6) ws.write(4, 16, 'of the employee', style_header6) ws.write(4, 17, '', style_header6) ws.write(4, 18, '', style_header6) ws.write(4, 19, '', style_header6) ws.write(4, 20, '', style_header6) ws.write(4, 21, '', style_header6) ws.write(4, 22, '', style_header6) ws.write(4, 23, '', style_header6) ws.write(4, 24, '', style_header6) ws.write(4, 25, '', style_header6) ws.write(4, 26, '', style_header6) ws.write(4, 27, '', style_header6) ws.write(4, 28, '', style_header6) ws.write(4, 29, '', style_header6) ws.write(4, 30, '', style_header6) ws.write(4, 31, '', style_header6) ws.write(4, 32, '', style_header6) ws.write(4, 33, '', style_header6) ws.write(4, 34, '', style_header6) ws.write(4, 35, '', style_header6) ws.write(4, 36, '', style_header6) ws.write(4, 37, '', style_header6) ws.write(4, 38, '', style_header6) ws.write(4, 39, '', style_header6) ws.write(4, 40, '', style_header6) ws.write(4, 41, '', style_header6) ws.write(4, 42, '', style_header6) ws.write(4, 43, '', style_header6) ws.write(4, 44, '', style_header6) ws.row(5).height = 400 ws.write(5, 0, '', style_header6) ws.write(5, 1, '', style_header6) ws.write(5, 2, '', style_header6) ws.write(5, 3, '', style_header6) ws.write(5, 4, '', style_header6) ws.write(5, 5, 'of the accounting year', style_header6) ws.write(5, 6, '', style_header6) ws.write(5, 7, '', style_header6) ws.write(5, 8, 'as the case may be', style_header6) ws.write(5, 9, 'the accounting year', style_header6) ws.write(5, 10, 'advance', style_header6) ws.write(5, 11, 'misconduct of the employee', style_header6) ws.write(5, 12, '', style_header6) ws.write(5, 13, '', style_header6) ws.write(5, 14, '', style_header6) ws.write(5, 15, '', style_header6) ws.write(5, 16, '', style_header6) ws.write(5, 17, '', style_header6) ws.write(5, 18, 'DAYS', style_header6) ws.write(5, 19, 'SALARY', style_header6) ws.write(5, 20, 'DAYS', style_header6) ws.write(5, 21, 'SALARY', style_header6) ws.write(5, 22, 'DAYS', style_header6) ws.write(5, 23, 'SALARY', style_header6) ws.write(5, 24, 'DAYS', style_header6) ws.write(5, 25, 'SALARY', style_header6) ws.write(5, 26, 'DAYS', style_header6) ws.write(5, 27, 'SALARY', style_header6) ws.write(5, 28, 'DAYS', style_header6) ws.write(5, 29, 'SALARY', style_header6) ws.write(5, 30, 'DAYS', style_header6) ws.write(5, 31, 'SALARY', style_header6) ws.write(5, 32, 'DAYS', style_header6) ws.write(5, 33, 'SALARY', style_header6) ws.write(5, 34, 'DAYS', style_header6) ws.write(5, 35, 'SALARY', style_header6) ws.write(5, 36, 'DAYS', style_header6) ws.write(5, 37, 'SALARY', style_header6) ws.write(5, 38, 'DAYS', style_header6) ws.write(5, 39, 'SALARY', style_header6) ws.write(5, 40, 'DAYS', style_header6) ws.write(5, 41, 'SALARY', style_header6) ws.write(5, 42, 'DAYS', style_header6) ws.write(5, 43, 'SALARY', style_header6) ws.write(5, 44, '', style_header6) if obj.company_id: list_ids = emp_obj.search(cr, uid, [('company_id', '=', obj.company_id.id), ('active', '=', True), ('type', '=', 'Employee')]) list_ids1 = emp_obj.search(cr, uid, [('company_id', '=', obj.company_id.id), ('active', '=', False), ('type', '=', 'Employee')]) list_ids = list_ids + list_ids1 elif obj.employee_id: list_ids = emp_obj.search(cr, uid, [('id', '=', obj.employee_id.id), ('active', '=', True), ('type', '=', 'Employee')]) else: raise osv.except_osv( _('Warning !'), _("Please Select Atleast Company Or Employee.")) if len(list_ids) == 1: query ="select hr.sinid,rr.name,hr.doj,job.name,hr.id,sum(pmbl.apr),sum(pmbl.may),sum(pmbl.june),sum(pmbl.july),sum(pmbl.aug),sum(pmbl.sep),sum(pmbl.oct),"\ "sum(pmbl.nov),sum(pmbl.dec),sum(pmbl.jan),sum(pmbl.feb),sum(pmbl.mar),sum(pmbl.total_day),sum(pmbl.bonus),sum(pmbl.apr_salary),"\ "sum(pmbl.may_salary),sum(pmbl.june_salary),sum(pmbl.july_salary),sum(pmbl.aug_salary),sum(pmbl.sep_salary),sum(pmbl.oct_salary),"\ "sum(pmbl.nov_salary),sum(pmbl.dec_salary),sum(pmbl.jan_salary),sum(pmbl.feb_salary),sum(pmbl.mar_salary),sum(pmbl.total_salary)"\ "from payment_management_bonus_line as pmbl left join hr_employee as hr on pmbl.employee_id = hr.id left join resource_resource as rr on hr.resource_id = rr.id left join hr_job as job on hr.job_id=job.id "\ "where pmbl.employee_id = '"+str(list_ids[0])+"' and pmbl.bonus_from >= '"+str(obj.from_date)+"' and pmbl.bonus_till <= '"+str(obj.till_date)+"' group by hr.sinid,rr.name,hr.doj,job.name,hr.id order by hr.sinid " cr.execute(query) temp = cr.fetchall() else: query ="select hr.sinid,rr.name,hr.doj,job.name,hr.id,sum(pmbl.apr),sum(pmbl.may),sum(pmbl.june),sum(pmbl.july),sum(pmbl.aug),sum(pmbl.sep),sum(pmbl.oct),"\ "sum(pmbl.nov),sum(pmbl.dec),sum(pmbl.jan),sum(pmbl.feb),sum(pmbl.mar),sum(pmbl.total_day),sum(pmbl.bonus),sum(pmbl.apr_salary),"\ "sum(pmbl.may_salary),sum(pmbl.june_salary),sum(pmbl.july_salary),sum(pmbl.aug_salary),sum(pmbl.sep_salary),sum(pmbl.oct_salary),"\ "sum(pmbl.nov_salary),sum(pmbl.dec_salary),sum(pmbl.jan_salary),sum(pmbl.feb_salary),sum(pmbl.mar_salary),sum(pmbl.total_salary)"\ "from payment_management_bonus_line as pmbl left join hr_employee as hr on pmbl.employee_id = hr.id left join resource_resource as rr on hr.resource_id = rr.id left join hr_job as job on hr.job_id=job.id "\ "where pmbl.employee_id in "+str(tuple(list_ids))+" and pmbl.bonus_from >= '"+str(obj.from_date)+"' and pmbl.bonus_till <= '"+str(obj.till_date)+"' group by hr.sinid,rr.name,hr.doj,job.name,hr.id order by hr.sinid" cr.execute(query) temp = cr.fetchall() if not temp: raise osv.except_osv(_('Warning !'), _("Record Not Found !!!")) columnno = 6 no = 6 for val in temp: father_name = '' if val[4]: father_qry = "select name from family where relation='Father' and employee_id='" + str( val[4]) + "' " cr.execute(father_qry) father_temp = cr.fetchall() if father_temp: father_name = father_temp[0][0] else: father_name = ' ' ws.row(no).height = 500 doj = datetime.strptime(val[2], "%Y-%m-%d").strftime("%d-%m-%Y") ws.write(columnno, 0, val[0], style_header5) ws.write(columnno, 1, val[1], style_header5) ws.write(columnno, 2, doj, style_header5) ws.write(columnno, 3, father_name, style_header5) ws.write(columnno, 4, val[3], style_header5) ws.write(columnno, 5, 'Yes', style_header5) ws.write(columnno, 6, '', style_header5) ws.write(columnno, 7, '', style_header5) ws.write(columnno, 8, '', style_header5) ws.write(columnno, 9, '', style_header5) ws.write(columnno, 10, '', style_header5) ws.write(columnno, 11, '', style_header5) ws.write(columnno, 12, '', style_header5) ws.write(columnno, 13, '', style_header5) ws.write(columnno, 14, '', style_header5) ws.write(columnno, 15, '', style_header5) ws.write(columnno, 16, '', style_header5) ws.write(columnno, 17, '', style_header5) ws.write(columnno, 18, val[5], style_header5) ws.write(columnno, 19, val[19], style_header5) ws.write(columnno, 20, val[6], style_header5) ws.write(columnno, 21, val[20], style_header5) ws.write(columnno, 22, val[7], style_header5) ws.write(columnno, 23, val[21], style_header5) ws.write(columnno, 24, val[8], style_header5) ws.write(columnno, 25, val[22], style_header5) ws.write(columnno, 26, val[9], style_header5) ws.write(columnno, 27, val[23], style_header5) ws.write(columnno, 28, val[10], style_header5) ws.write(columnno, 29, val[24], style_header5) ws.write(columnno, 30, val[11], style_header5) ws.write(columnno, 31, val[25], style_header5) ws.write(columnno, 32, val[12], style_header5) ws.write(columnno, 33, val[26], style_header5) ws.write(columnno, 34, val[13], style_header5) ws.write(columnno, 35, val[27], style_header5) ws.write(columnno, 36, val[14], style_header5) ws.write(columnno, 37, val[28], style_header5) ws.write(columnno, 38, val[15], style_header5) ws.write(columnno, 39, val[29], style_header5) ws.write(columnno, 40, val[16], style_header5) ws.write(columnno, 41, val[30], style_header5) ws.write(columnno, 42, val[17], style_header5) ws.write(columnno, 43, val[31], style_header5) ws.write(columnno, 44, val[18], style_header5) columnno += 1 no += 1 f = cStringIO.StringIO() wb.save(f) out = base64.encodestring(f.getvalue()) return self.write(cr, uid, ids, { 'export_data': out, 'filename': 'Payment Bonus.xls' }, context=context)
def qiandaoXls(filename,qiandao,dategroup,mi,response): import uuid filename+=u'.xls' response['Content-Disposition'] = (u'attachment;filename=%s'%filename).encode('utf-8') import xlwt from xlwt import Font,Alignment style1=xlwt.XFStyle() font1=Font() font1.height=320 font1.name=u'仿宋' style1.font=font1 algn=Alignment() algn.horz=Alignment.HORZ_RIGHT style1.alignment=algn style0=xlwt.XFStyle() algn0=Alignment() algn0.horz=Alignment.HORZ_CENTER algn0.vert=Alignment.VERT_CENTER font=Font() font.height=320 font.bold=False font.name=u'仿宋' style0.alignment=algn0 style0.font=font style3=xlwt.XFStyle() algn3=Alignment() algn3.horz=Alignment.HORZ_CENTER font3=Font() font3.height=320 font3.bold=False font1.name=u'仿宋' style3.alignment=algn3 style3.font=font3 pattern = xlwt.Pattern() pattern.pattern = xlwt.Pattern.SOLID_PATTERN pattern.pattern_fore_colour =2 style3.pattern=pattern wb=xlwt.Workbook() style2=xlwt.XFStyle() algn2=Alignment() algn2.horz=Alignment.HORZ_LEFT font2=Font() font2.height=350 font2.bold=True font1.name=u'仿宋' style2.alignment=algn2 style2.font=font2 wb=xlwt.Workbook() ws=wb.add_sheet(u"签到报表",cell_overwrite_ok=True) rownum=2 usernum=1 ws.write_merge(0,1,0,0,u'序号',style2) ws.write_merge(0,1,1,1,u'员工ID',style2) ws.col(1).width=0x0d00 +3000 ws.write_merge(0,1,2,2,u'姓名',style2) ws.col(2).width=0x0d00 +3000 for i,q in enumerate(qiandao): ws.write_merge(0,0,5*i+3,5*i+6,q.name,style2) ws.write_merge(1,1,5*i+3,5*i+3,u'厅台',style2) ws.col(5*i+3).width=0x0d00 + 7000 ws.write_merge(1,1,5*i+4,5*i+4,u'地址',style2) ws.col(5*i+4).width=0x0d00 + 9000 ws.write_merge(1,1,5*i+5,5*i+5,u'时间',style2) ws.write_merge(1,1,5*i+6,5*i+6,u'位置判断',style2) ws.col(6).width=0x0d00 +3000 ws.write_merge(1,1,5*i+7,5*i+7,u'时间判读',style2) ws.col(7).width=0x0d00 +3000 for data in dategroup: ws.write_merge(rownum,rownum,0,data['rowspan2'],u'日期:%s'%(data['date'],),style2) rownum+=1 tempnum=0 for query in data['query']: for i,rows in enumerate(query['qiandaolist']): for j,row in enumerate(rows): ws.write_merge(rownum+j,rownum+j,3+i*5+0,3+i*5+0,row['officename'],style0) ws.write_merge(rownum+j,rownum+j,3+i*5+1,3+i*5+1,row['address'],style0) ws.write_merge(rownum+j,rownum+j,3+i*5+2,3+i*5+2,row['dateTime'],style0) if row['officegps']: if row['gpsdistance']<mi: ws.write_merge(rownum+j,rownum+j,3+i*5+3,3+i*5+3,u'合格',style0) else: ws.write_merge(rownum+j,rownum+j,3+i*5+3,3+i*5+3,u'不合格',style3) else: ws.write_merge(rownum+j,rownum+j,3+i*5+3,3+i*5+3,u'0',style0) if row['time']: ws.write_merge(rownum+j,rownum+j,3+i*5+4,3+i*5+4,u'合格',style0) else: ws.write_merge(rownum+j,rownum+j,3+i*5+4,3+i*5+4,u'不合格',style3) if tempnum<j: tempnum=j ws.write_merge(rownum,rownum+tempnum,0,0,usernum,style0) ws.write_merge(rownum,rownum+tempnum,1,1,query['user']['username'],style0) ws.write_merge(rownum,rownum+tempnum,2,2,query['user']['get_full_name'],style0) rownum+=tempnum+1 usernum+=1 wb.save(response)
def test(theseargs): parser = argparse.ArgumentParser(description='Download bulk information from Ofgem to produce an Excel spreadsheet') parser.add_argument('--start', action='store', required=True, help='Period to start from (MMM-YYYY)') parser.add_argument('--end', action='store', required=True, help='Period to finish on (MMM-YYYY)') parser.add_argument('--scheme', action='store', default='RO', help='Scheme to get certificates for') parser.add_argument('--filename', action='store', default='certificates.xls', help='Filename to export to') parser.add_argument('--name', action='store', default=None, help='Part of a name of generation station (or name fragments for several stations, separated by commas)') args = parser.parse_args(args=theseargs) (start_year, start_month) = get_period(args.start) (end_year, end_month) = get_period(args.end) if not args.filename.endswith('.xls'): args.filename += '.xls' periods = [] for yy in range(start_year, end_year + 1): mm = start_month if start_year == yy else 1 mm2 = end_month if end_year == yy else 12 for m in range(mm, mm2+1): periods.append(date(yy,m,1).strftime("%b-%Y")) station = args.name if station is None: stations = [] while (True): station = input("Enter a station name (or blank to finish)") if station.strip() == '': break if ',' in station: for s in station.strip().split(','): s = s.strip() if s in stations: continue stations.append(s) else: station = station.strip() if station in stations: continue stations.append(station) else: stations = station.strip().split(',') if len(stations) == 0: print("No stations to process. Exiting...") sys.exit(0) wb = Workbook() ws = wb.add_sheet('Certificate Data', cell_overwrite_ok=True) ws.write(PERIOD_START - 1,0,"Period") for i in range(0, len(periods)): ws.write(PERIOD_START + i, 0, periods[i]) al = Alignment() al.horz = Alignment.HORZ_CENTER al.vert = Alignment.VERT_CENTER title_style = XFStyle() title_style.alignment = al print("\nTotal of %d stations to process\n" % len(stations)) for i in range(0, len(stations)): s = stations[i] col = 1 + 5 * i print(" "+ s) # add headers ws.write(PERIOD_START - 1, col, "Installed Capacity") ws.write(PERIOD_START - 1, col + 1, "RO Certificates") ws.write(PERIOD_START - 1, col + 2, "RO Factor") ws.write(PERIOD_START - 1, col + 3, "REGO Certificates") ws.write(PERIOD_START - 1, col + 4, "REGO Factor") capacity = {} for scheme in ['RO','REGO']: offset = 1 if scheme == 'RO' else 3 ss = StationSearch() ss.filter_scheme(scheme) ss.filter_name(s) if not ss.get_data(): print("Unable to find any station with a name %s" % s) continue station = None # if more than one generator has matched, use the first WIND one if len(ss.stations) > 1: print('Several stations found that match %s:' % s) print(list(st.name for st in ss.stations)) print('The first wind station will be selected') for st in ss.stations: if 'wind' in st.technology.lower(): station = st break else: station = ss.stations[0] if station is None: print("Unable to get station data for '%s'" % s) continue # Write name ws.write_merge(PERIOD_START - 4, PERIOD_START - 4, col, col + 4, station.name, title_style) # add accreditation # if scheme == 'RO': ws.write_merge(PERIOD_START - 2, PERIOD_START - 2, col, col + 4, 'RO: ' + station.accreditation + ' [' + station.commission_dt.strftime("%d %b %Y") + ']', title_style) elif scheme == 'REGO': ws.write_merge(PERIOD_START - 3, PERIOD_START - 3, col, col + 4, 'REGO: ' + station.accreditation + ' [' + station.commission_dt.strftime("%d %b %Y") + ']', title_style) cs = CertificateSearch() cs.set_start_month(start_month) cs.set_start_year(start_year) cs.set_finish_month(end_month) cs.set_finish_year(end_year) cs.filter_accreditation(station.accreditation) #cs.filter_scheme(scheme) # seems to work ok without this, and REGOs break with it #cs.filter_status(['Issued','Redeemed','Expired']) # this doesn't work. And arguable whether expired should count if not cs.get_data(): print("Unable to get any certificate data :-(") continue data = {} for c in cs.certificates: # print(c.as_string()) if c.status in ['Revoked','Retired','Expired']: continue row = periods.index(c.period) + PERIOD_START if not c.period in capacity: ws.write(row, col, c.capacity) capacity[c.period] = True data[c.period] = data.get(c.period,0) + c.certs ws.write(row, col + offset + 1, c.factor) for p,val in viewitems(data): row = periods.index(p) + PERIOD_START ws.write(row, col + offset, val) print("\nComplete. Excel spreadsheet %s" % args.filename) wb.save(args.filename)
def generate_leave_record_report_file(employees, start_date, end_date, prefix=''): queryset = get_reqs_in_period(start_date, end_date) template = open_workbook(settings.REPORT_TEMPLATE + r'leave record report.xls', formatting_info=True) wb = copy(template) lrws = wb.get_sheet(0) left_center = Alignment() left_center.horz = Alignment.HORZ_LEFT left_center.vert = Alignment.VERT_CENTER center_center = Alignment() center_center.horz = Alignment.HORZ_CENTER center_center.vert = Alignment.VERT_CENTER style = easyxf( 'font: name Arial;' 'borders: left thin, right thin, top thin, bottom thin;' ) style_bg = easyxf( 'font: name Arial;' 'borders: left thin, right thin, top thin, bottom thin;' ) style_bg.alignment = left_center style.alignment = left_center pattern1 = Pattern() pattern1.pattern = Pattern.SOLID_PATTERN pattern1.pattern_fore_colour = 0x34 pattern2 = Pattern() pattern2.pattern = Pattern.SOLID_PATTERN pattern2.pattern_fore_colour = 0x50 style_bg.pattern = pattern1 original_index = start_index = curr_index = 2 for employee in employees: name_rows = queryset.filter(employee=employee).count() if name_rows > 0: leavetypes = list(LeaveType.objects.all()) for leavetype in leavetypes: leaverequests = queryset.filter(employee=employee, leave_type=leavetype) leavetype_rows = len(leaverequests) #print 'leavetype_rows', leavetype_rows if leavetype_rows > 0: if style_bg.pattern == pattern1: style_bg.pattern = pattern2 else: style_bg.pattern = pattern1 duration = 0.0 for l in leaverequests: periods = l.period_set.all().filter( Q(start__range=(start_date, end_date))| Q(end__range=(start_date, end_date))) for p in periods: p_start = max(p.start, start_date) p_end = min(p.end, end_date) lrws.write( curr_index, 2, \ Period(leave_request=l, start=p_start, end=p_end).__unicode__(), \ style_bg) curr_index += 1 duration += duration_days(p_start, p_end) style_bg.alignment = center_center lrws.write_merge(start_index, curr_index - 1, 3, 3, duration, style_bg) style_bg.alignment = left_center lrws.write_merge(start_index, curr_index - 1, 1, 1, leavetype.name, style_bg) start_index = curr_index lrws.write_merge(original_index, curr_index - 1, 0, 0, employee.display_name, style) original_index = start_index = curr_index filename = settings.REPORT_FILES + prefix + 'leaverecordreport-%s-%s.xls' % (start_date.strftime('%Y_%m_%d'), end_date.strftime('%Y_%m_%d')) wb.save(filename) return filename
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)
def print_report_excel(self, cr, uid, ids, context=None): if context is None: context = {} asset_obj = self.pool.get('account.asset.asset') asset_categ_obj = self.pool.get('account.asset.category') company_obj = self.pool.get('res.company') year_obj = self.pool.get('account.fiscalyear') period_obj = self.pool.get('account.period') asset_depreciation_line_obj = self.pool.get( 'account.asset.depreciation.line') fnt = Font() fnt.name = 'Arial' fnt.height = 220 fnt1 = Font() fnt1.name = 'Arial' fnt1.height = 220 fnt1.bold = 'on' # Define the font attributes for header content_fnt = Font() content_fnt.name = 'Arial' content_fnt.height = 220 align_content = Alignment() align_content.horz = Alignment.HORZ_LEFT borders = Borders() borders.left = 0x02 borders.right = 0x02 borders.top = 0x02 borders.bottom = 0x02 # The text should be centrally aligned align = Alignment() align.horz = Alignment.HORZ_LEFT align.vert = Alignment.VERT_TOP align.wrap = Alignment.WRAP_AT_RIGHT # The text should be right aligned align1 = Alignment() align1.horz = Alignment.HORZ_RIGHT align1.vert = Alignment.VERT_TOP align1.wrap = Alignment.WRAP_AT_RIGHT # The content should be left aligned align2 = Alignment() align2.horz = Alignment.HORZ_LEFT align2.vert = Alignment.VERT_TOP align2.wrap = Alignment.WRAP_AT_RIGHT # The content should be right aligned align3 = Alignment() align3.horz = Alignment.HORZ_RIGHT align3.vert = Alignment.VERT_TOP align3.wrap = Alignment.WRAP_AT_RIGHT # We set the backgroundcolour here pattern = Pattern() pattern.pattern = Pattern.SOLID_PATTERN pattern.pattern_fore_colour = 0x1F # We set the backgroundcolour here pattern1 = Pattern() pattern1.pattern = Pattern.SOLID_PATTERN pattern1.pattern_fore_colour = 0x17 # We set the backgroundcolour here pattern2 = Pattern() pattern2.pattern = Pattern.SOLID_PATTERN pattern2.pattern_fore_colour = 0xFF # We set the backgroundcolour here pattern3 = Pattern() pattern3.pattern = Pattern.SOLID_PATTERN pattern3.pattern_fore_colour = 0xFF # apply the above settings to the row(0) header style_header = XFStyle() style_header.font = fnt1 style_header.pattern = pattern style_header.borders = borders style_header.alignment = align style_header_right = XFStyle() style_header_right.font = fnt1 style_header_right.pattern = pattern style_header_right.borders = borders style_header_right.alignment = align3 # apply the above settings to the row(1) header style_header1 = XFStyle() style_header1.font = fnt style_header1.pattern = pattern1 style_header1.borders = borders style_header1.alignment = align1 # apply the above settings to the content style_content_left = XFStyle() style_content_left.font = fnt style_content_left.pattern = pattern2 style_content_left.borders = borders style_content_left.alignment = align2 style_content_right = XFStyle() style_content_right.font = fnt style_content_right.pattern = pattern3 style_content_right.borders = borders style_content_right.alignment = align3 style_content = XFStyle() style_content.alignment = align_content style_content.font = content_fnt wb = Workbook() ws = wb.add_sheet('Sheet 1') ws.row(0).height = 500 ws.col(0).width = 6500 ws.col(1).width = 6500 ws.col(2).width = 6500 ws.col(3).width = 6500 ws.col(4).width = 6500 ws.col(5).width = 6500 ws.col(6).width = 6500 ws.col(7).width = 6500 ws.col(8).width = 6500 ws.col(9).width = 6500 ws.col(10).width = 6500 ws.col(11).width = 6500 ws.col(12).width = 6500 ws.col(13).width = 6500 style = xlwt.easyxf('font: bold on,height 240,color_index 0X36;' 'align: horiz center;') ws.write(0, 2, 'Asset Report', style) data = self.read(cr, uid, ids, [], context=context)[0] company = company_obj.browse(cr, uid, data['company_id'][0]) if data['fiscalyear_id']: year = year_obj.browse(cr, uid, data['fiscalyear_id'][0]).name else: year = '' filter = '' if data['filter'] == 'filter_date': filter = 'Dates' elif data['filter'] == 'filter_period': filter = 'Periods' else: filter = 'No Filters' from_date = False to_date = False ws.row(2).height = 500 ws.write(2, 0, 'Company Name', style_header) ws.write(2, 1, company.name, style_header) ws.row(3).height = 500 ws.write(3, 0, 'Report Run', style_header) ws.write(3, 1, time.strftime('%Y-%m-%d %H:%M:%S'), style_header) ws.row(4).height = 500 ws.write(4, 0, 'Fiscal Year', style_header) ws.write(4, 1, year, style_header) ws.row(5).height = 500 ws.write(5, 0, 'Filters', style_header) ws.write(5, 1, filter, style_header) ws.row(6).height = 500 if data['filter'] == 'filter_period': from_period_id = data['period_from'][0] to_period_id = data['period_to'][0] if from_period_id and to_period_id: from_date = period_obj.browse(cr, uid, from_period_id).date_start to_date = period_obj.browse(cr, uid, to_period_id).date_stop elif from_period_id and to_period_id: from_date = period_obj.browse(cr, uid, from_period_id).date_start to_date = period_obj.browse(cr, uid, from_period_id).date_stop ws.write(6, 0, 'Start Period', style_header) ws.write(6, 1, data['period_from'][1], style_header) ws.write(7, 0, 'End Period', style_header) ws.write(7, 1, data['period_to'][1], style_header) elif data['filter'] == 'filter_date': from_date = data['date_from'] to_date = data['date_to'] ws.write(6, 0, 'Start Date', style_header) ws.write( 6, 1, datetime.strptime(data['date_from'], '%Y-%m-%d').strftime('%m/%d/%Y'), style_header) ws.write(7, 0, 'End Date', style_header) ws.write( 7, 1, datetime.strptime(data['date_to'], '%Y-%m-%d').strftime('%m/%d/%Y'), style_header) row = 10 from_date = False to_date = False if data['filter'] == 'filter_period': from_period_id = data['period_from'][0] to_period_id = data['period_to'][0] if from_period_id and to_period_id: from_date = period_obj.browse(cr, uid, from_period_id).date_start to_date = period_obj.browse(cr, uid, to_period_id).date_stop elif from_period_id and to_period_id: from_date = period_obj.browse(cr, uid, from_period_id).date_start to_date = period_obj.browse(cr, uid, from_period_id).date_stop elif data['filter'] == 'filter_date': from_date = data['date_from'] to_date = data['date_to'] else: if data['fiscalyear_id']: from_date = year_obj.browse( cr, uid, data['fiscalyear_id'][0]).date_start to_date = year_obj.browse(cr, uid, data['fiscalyear_id'][0]).date_stop else: pass if data['asset_categ_ids']: categories_ids = data['asset_categ_ids'] else: categories_ids = asset_categ_obj.search(cr, uid, []) if data['company_id']: company_ids = [data['company_id'][0]] else: company_ids = company_obj.search(cr, uid, []) for categ_id in categories_ids: ws.row(row).height = 500 domain = [] if from_date and to_date: #domain.append(('purchase_date', '>=', from_date)) #domain.append(('purchase_date', '<=', to_date)) pass domain.append(('category_id', '=', categ_id)) domain.append(('company_id', 'in', company_ids)) domain.append(('state', '=', 'open')) asset_ids = asset_obj.search(cr, uid, domain) asset_categ = asset_categ_obj.browse(cr, uid, categ_id) count = 1 ws.row(row).height = 800 ws.write(row, 0, 'Asset Category:', style_header) ws.write(row, 1, asset_categ.name, style_header) row += 1 ws.row(row).height = 700 ws.write(row, 0, 'No', style_header) ws.write(row, 1, 'Asset Tag No', style_header) ws.write(row, 2, 'Asset Description', style_header) ws.write(row, 3, 'Location', style_header) ws.write(row, 4, 'Document Reference', style_header) ws.write(row, 5, 'Requisition Date', style_header) ws.write(row, 6, 'Requisition Value', style_header) ws.write(row, 7, 'Salvage Value', style_header) ws.write(row, 8, 'Depreciation Method', style_header) ws.write(row, 9, 'Number of Usage', style_header) ws.write(row, 10, 'B/F Accumulated Depreciation', style_header) ws.write(row, 11, 'Depreciation', style_header) ws.write(row, 12, 'Accumulated Depreciation', style_header) ws.write(row, 13, 'Net Book Value', style_header) row += 1 total_req_val = 0.0 total_sal_val = 0.0 total_bf_accum_depr = 0.0 total_next_depr = 0.0 total_accum_depr = 0.0 total_net_book = 0.0 for asset in asset_obj.browse(cr, uid, asset_ids): bf_accum_depr = 0.0 next_amount_depr = 0.0 accum_depr_val = 0.0 net_book_val = 0.0 method = '' if asset.method == 'linear': method = 'Linear' elif asset.method == 'degressive': method = 'Degressive' # location = '' # # if asset.move_id and asset.move_id.location_dest_id: # location = asset.move_id.location_dest_id.name ws.row(row).height = 500 ws.write(row, 0, count, style_content_left) ws.write(row, 1, asset.code or '', style_content_left) ws.write(row, 2, asset.product_desc or '', style_content_left) ws.write(row, 3, asset.asset_location or '', style_content_left) ws.write(row, 4, asset.picking_id.name or '', style_content_left) row_date = datetime.strptime( asset.purchase_date, '%Y-%m-%d').strftime('%m/%d/%Y') or '' ws.write(row, 5, row_date, style_content_left) ws.write(row, 6, asset.purchase_value or 0.0, style_content_right) ws.write(row, 7, asset.salvage_value or 0.0, style_content_right) ws.write(row, 8, method or '', style_content_left) ws.write(row, 9, asset.method_number or '', style_content_left) if from_date and to_date: period_id = period_obj.find(cr, uid, dt=from_date) bf_line_ids = asset_depreciation_line_obj.search( cr, uid, [('asset_id', '=', asset.id), ('effective_period_id', '=', period_id[0])]) if bf_line_ids: asset_bf_line = asset_depreciation_line_obj.browse( cr, uid, bf_line_ids[0]) bf_accum_depr = asset_bf_line.depreciated_value else: bf_line_ids = asset_depreciation_line_obj.search( cr, uid, [('asset_id', '=', asset.id), ('move_check', '=', True)], order='effective_date desc', limit=1) if bf_line_ids: asset_bf_line = asset_depreciation_line_obj.browse( cr, uid, bf_line_ids[0]) bf_accum_depr = asset_bf_line.depreciated_value #To find current depreciation value: Need to search depreciation line on depreciation board for respected "Amount already depreciated". # Here we will match "Amount already depreciated" (bf_accum_depr) in depreciation line and matched depreciation line's current depreciation will be taken. if from_date and to_date: period_id = period_obj.find(cr, uid, dt=from_date) bf_line_ids = asset_depreciation_line_obj.search( cr, uid, [('asset_id', '=', asset.id), ('effective_date', '>=', from_date), ('effective_date', '<=', to_date), ('move_check', '=', True)]) if bf_line_ids: for asset_bf_line in asset_depreciation_line_obj.browse( cr, uid, bf_line_ids): next_amount_depr += asset_bf_line.amount else: period_id = period_obj.find(cr, uid, dt=from_date) bf_line_ids = asset_depreciation_line_obj.search( cr, uid, [('asset_id', '=', asset.id), ('move_check', '=', True)]) if bf_line_ids: for asset_bf_line in asset_depreciation_line_obj.browse( cr, uid, bf_line_ids): next_amount_depr += asset_bf_line.amount ws.write(row, 10, bf_accum_depr, style_content_right) ws.write(row, 11, next_amount_depr, style_content_right) accum_depr_val = (bf_accum_depr + next_amount_depr) ws.write(row, 12, accum_depr_val, style_content_right) net_book_val = (asset.purchase_value - accum_depr_val) ws.write(row, 13, net_book_val, style_content_right) total_req_val += asset.purchase_value total_sal_val += asset.salvage_value total_bf_accum_depr += bf_accum_depr total_next_depr += next_amount_depr total_accum_depr += accum_depr_val total_net_book += net_book_val row += 1 count += 1 ws.row(row).height = 500 ws.write(row, 0, '', style_header_right) ws.write(row, 1, '', style_header_right) ws.write(row, 2, 'Total', style_header_right) ws.write(row, 3, '', style_header_right) ws.write(row, 4, '', style_header_right) ws.write(row, 5, '', style_header_right) ws.write(row, 6, total_req_val, style_header_right) ws.write(row, 7, total_sal_val, style_header_right) ws.write(row, 8, '', style_header_right) ws.write(row, 9, '', style_header_right) ws.write(row, 10, total_bf_accum_depr, style_header_right) ws.write(row, 11, total_next_depr, style_header_right) ws.write(row, 12, total_accum_depr, style_header_right) ws.write(row, 13, total_net_book, style_header_right) row += 3 f = cStringIO.StringIO() wb.save(f) out = base64.encodestring(f.getvalue()) return { 'name': 'Assets Register Reports', 'res_model': 'xls.report.wizard', 'type': 'ir.actions.act_window', 'view_type': 'form', 'view_mode': 'form', 'target': 'new', 'nodestroy': True, 'context': { 'data': out, 'name': 'Asset Register Report.xls' } }
#------------------------------------------------------------------------------- from xlwt import Alignment, XFStyle, Borders, Font # ************************************************************************************** # styles # # year break dashed border yearBreak = XFStyle() yearBorders = Borders() yearBorders.bottom = Borders.DASHED yearBreak.borders = yearBorders # center align alignCenter = Alignment() alignCenter.horz = Alignment.HORZ_CENTER alignCenter.vert = Alignment.VERT_CENTER # Top alignment, should be used for all cells topAlign = Alignment() topAlign.vert = Alignment.VERT_TOP # header centered and wrapped alignWrap = Alignment() alignWrap.horz = Alignment.HORZ_CENTER alignWrap.wrap = Alignment.WRAP_AT_RIGHT # normal font normFont = Font() normFont.name = 'Arial' # headers font
q = q.filter(Q(activity_time_to__gt=start) & Q(activity_time_from__lt=end)) if ignore is not None: if isinstance(ignore, Iterable): q = q.exclude(uid__in=ignore) else: q = q.exclude(uid=ignore) cnt = q.count() return cnt != 0 thick_border = easyxf('border: left thick, top thick, ' 'bottom thick, right thick') align_hc_vc = Alignment() align_hc_vc.horz = Alignment.HORZ_CENTER align_hc_vc.vert = Alignment.VERT_CENTER align_hc_vc.wrap = True align_hl_vt = Alignment() align_hl_vt.horz = Alignment.HORZ_LEFT align_hl_vt.vert = Alignment.VERT_TOP align_hl_vt.wrap = True align_hc_vb = Alignment() align_hc_vb.horz = Alignment.HORZ_CENTER align_hc_vb.vert = Alignment.VERT_BOTTOM align_hc_vb.wrap = True borders_thin = Borders() borders_thin.left = Borders.THIN borders_thin.right = Borders.THIN
def write_sheet(self, data, sheet_name, print_to_screen=False): '''Write a very simple table to a new sheet in a spreadsheet, Optionally, print the table to the screen''' # most cells al = Alignment() al.horz = Alignment.HORZ_RIGHT al.vert = Alignment.VERT_CENTER font = Font() font.name = 'Arial' font.height = 9 * 20 # 9 pt style = XFStyle() style.font = font style.alignment = al # tops cells al = Alignment() al.horz = Alignment.HORZ_CENTER al.vert = Alignment.VERT_CENTER font = Font() font.name = 'Arial' font.bold = True font.height = 9 * 20 # 9 pt style_top = XFStyle() style_top.font = font style_top.alignment = al # left cells al = Alignment() al.horz = Alignment.HORZ_LEFT al.vert = Alignment.VERT_CENTER font = Font() font.name = 'Arial' font.bold = True font.italic = True font.height = 9 * 20 # 9 pt style_left = XFStyle() style_left.font = font style_left.alignment = al ws = self.add_sheet(sheet_name) for i, row in enumerate(data): for j, cell in enumerate(row): borders = Borders() if i == 0: borders.top = 1 borders.bottom = 2 if i == len(row) - 1: borders.bottom = 1 if j == 0: borders.left = 1 borders.right = 1 if j == len(row) - 1: borders.right = 1 if j == 0: _style = style_left elif i == 0: _style = style_top else: _style = style _style.borders = borders # one of the libraries can get confused with plain strings if isinstance(cell, str): cell = unicode(cell, 'utf-8') ws.write(i + 1, j + 1, cell, _style) if print_to_screen: print print_table(data, sheet_name, bold=True)
# 操作Excel2003 或者更低 from xlwt import Workbook, Alignment, Borders, XFStyle, Formula import xlrd book = Workbook() sheet1 = book.add_sheet('first sheet') a1 = Alignment() a1.horz = Alignment.HORZ_CENTER a1.vert = Alignment.HORZ_CENTER borders = Borders() borders.bottom = Borders.THICK style = XFStyle() style.alignment = a1 style.borders = borders row = sheet1.row(0) row.write(0, 'test', style=style) row = sheet1.row(1) for i in range(5): row.write(i, i, style=style) row.write(5, Formula('SUM(A2:E2)'), style=style) book.save('./test2003.xls') book = xlrd.open_workbook('./test2003.xls') sheet1 = book.sheet_by_name('first sheet') row = sheet1.row(0) print(row[0].value) # 读不到公式,无解 print(sheet1.row(1)[5].value) # print(sheet1.cell(1, 5).value)
def get_attendance_report(self): try: import xlwt except: raise Warning(_('User Error'), _('Please Install xlwt Library.!')) filename = 'Attendance Register.xls' fp = BytesIO() wb = xlwt.Workbook(encoding='utf-8') worksheet = wb.add_sheet('PC_EXAM_ATTENDANCE_REGISTER') current_obj = self # ----------------------- # Excel Font Style & Sizes # ---------------------- fnt1 = Font() fnt1.name = 'TimesNewRoman' fnt1.bold = True fnt1.height = 16 * 0x14 fnt2 = Font() fnt2.name = 'verdana' fnt2.bold = True fnt2.height = 12 * 0x14 fnt3 = Font() fnt3.name = 'TimesNewRoman' fnt3.bold = True fnt3.height = 13 * 0x12 fnt4 = Font() fnt4.name = 'TimesNewRoman' fnt4.height = 16 * 0x14 # ----------------- # Excel Alignment # ---------------- al3 = Alignment() al3.horz = Alignment.HORZ_CENTER al3.vert = Alignment.VERT_CENTER al4 = Alignment() al4.horz = Alignment.HORZ_LEFT al4.vert = Alignment.VERT_CENTER al5 = Alignment() al5.horz = Alignment.HORZ_LEFT al5.vert = Alignment.VERT_CENTER # ----------------------- # Excel Style # ---------------------- style1 = XFStyle() style1.alignment = al3 style1.font = fnt1 style2 = XFStyle() style2.font = fnt2 style2.alignment = al4 style3 = XFStyle() style3.alignment = al3 style3.font = fnt3 style4 = XFStyle() style4.alignment = al5 style4.font = fnt4 # ------------------------------------------------------------------------- # HEADER In Excel # ------------------------------------------------------------------------- lst = [] fields = ['CIMA STUDENT NUMBER', 'SURNAME', 'NAME', 'SIGNATURE'] for i in [1, 2, 3, 4]: first_col = worksheet.col(i) first_col.width = 500 * 20 date_begin = str( datetime.strptime(current_obj.date_begin, "%Y-%m-%d %H:%M:%S") + timedelta(hours=2)) date_end = str( datetime.strptime(current_obj.date_end, "%Y-%m-%d %H:%M:%S") + timedelta(hours=2)) worksheet.merge(0, 16, 1, 4) try: worksheet.insert_bitmap( '/opt/custom_modules/event_price_kt/images/charterquest.bmp', 0, 1, 9, 3) except: pass worksheet.write_merge(17, 18, 1, 1, 'SUBJECT:', style1) worksheet.write_merge(17, 18, 2, 4, current_obj.name, style1) k = 19 pc_exam = True if current_obj.pc_exam == False: pc_exam = False worksheet.write_merge(k, k + 1, 1, 1, 'LECTURER:', style1) # if current_obj.main_speaker_id: # worksheet.write_merge(k,k+1,2, 4,current_obj.main_speaker_id.name , style1) # else: # worksheet.write_merge(k,k+1,2, 4,"", style1) k = k + 2 worksheet.write_merge(k, k + 1, 1, 1, 'DATE:', style1) worksheet.write_merge(k, k + 1, 2, 4, (date_begin).split(' ')[0], style1) worksheet.write_merge(k + 2, k + 3, 1, 1, 'TIME:', style1) worksheet.write_merge(k + 2, k + 3, 2, 4, (date_begin)[11:16] + '-' + (date_end)[11:16], style1) if not pc_exam: worksheet.write_merge(k + 4, k + 5, 1, 1, 'COURSE STUDY OPTION:', style1) # worksheet.write_merge(k+4,k+5,2, 4,current_obj.study.name, style1) k = k + 4 else: k = k + 2 worksheet.write_merge(k + 2, k + 3, 1, 1, 'CAMPUS:', style1) worksheet.write_merge(k + 2, k + 3, 2, 4, current_obj.address_id.name, style1) worksheet.write_merge( k + 4, k + 5, 1, 4, "PLEASE SIGN ATTENDANCE REGISTER & ENTER CIMA STUDENT NUMBER", style1) i = 1 for field in fields: worksheet.write_merge(k + 6, k + 7, i, i, field, style1) i += 1 i = k + 8 for obj in current_obj.registration_ids: partner_name = obj.partner_id.name name = partner_name.split(' ') if len(name) == 1: name = partner_name.split(' ') if obj.partner_id.prof_body_id: worksheet.write_merge(i, i + 1, 1, 1, obj.partner_id.prof_body_id, style4) else: worksheet.write_merge(i, i + 1, 1, 1, "", style4) try: worksheet.write_merge(i, i + 1, 2, 2, name[1], style4) except: worksheet.write_merge(i, i + 1, 2, 2, '', style4) worksheet.write_merge(i, i + 1, 3, 3, name[0], style4) worksheet.write_merge(i, i + 1, 4, 4, '', style4) i += 2 wb.save(fp) out = base64.encodestring(fp.getvalue()) final_arr_data = {} final_arr_data['file_stream'] = out final_arr_data['name'] = filename pl_report_id = self.env['attendance.sheet.report'].create( final_arr_data) vals = { 'name': 'Attendance Sheet Report', 'datas': out, 'datas_fname': filename } return { 'res_id': pl_report_id.id, 'name': filename, 'view_type': 'form', 'view_mode': 'form', 'res_model': 'attendance.sheet.report', 'type': 'ir.actions.act_window', }
def salary_deduction_category(self, cr, uid, ids, context=None): #============ ========= =============== =========================# fnt = Font() fnt.name = 'Ubuntu Medium' fnt.size = 16 fnt.style = 'Regular' #============ ======== ============= ============================# #Define the font attributes for header content_fnt = Font() content_fnt.name = 'Ubuntu Medium' 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 fnt1 = Font() fnt1.name = 'Ubuntu Medium' fnt1.size = 10 fnt1.style = 'Regular' content_fnt1 = Font() content_fnt1.name = 'Ubuntu Medium' content_fnt1.style = 'Regular' align_content1 = Alignment() align_content1.horz = Alignment.HORZ_LEFT borders1 = Borders() borders1.left = 0x1 borders1.right = 0x1 borders1.top = 0x1 borders1.bottom = 0x1 #The text should be centrally aligned align1 = Alignment() align1.horz = Alignment.HORZ_CENTER align1.vert = Alignment.VERT_TOP #We set the backgroundcolour here pattern1 = Pattern() pattern1.pattern1 = Pattern.SOLID_PATTERN pattern1.pattern_fore_colour = 0x16 #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 Content fnt3 = Font() fnt3.name = 'Arial' fnt3.size = '10' fnt3.style = 'Regular' content_fnt3 = Font() content_fnt3.name = 'Arial' content_fnt3.style = 'Regular' align_content3 = Alignment() align_content3.horz = Alignment.HORZ_LEFT borders3 = Borders() borders3.left = 0x0 borders3.right = 0x0 borders3.top = 0x0 borders3.bottom = 0x0 #The text should be centrally aligned align3 = Alignment() align3.horz = Alignment.HORZ_CENTER align3.vert = Alignment.VERT_TOP #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 fnt5 = Font() fnt5.name = 'Arial' fnt.size = 10 content_fnt5 = Font() content_fnt5.name = 'Arial' align_content5 = Alignment() # align_content5.horz= Alignment.HORZ_JUSTIFIED borders5 = Borders() borders5.left = 0x02 borders5.right = 0x02 borders5.top = 0x02 borders5.bottom = 0x02 align5 = Alignment() # align5.horz = Alignment.HORZ_JUSTIFIED align5.vert = Alignment.VERT_JUSTIFIED pattern5 = Pattern() pattern5.pattern = Pattern.SOLID_PATTERN pattern5.pattern_fore_colour = 0x16 style_header5 = XFStyle() style_header5.font = fnt5 style_header5.pattern = pattern5 style_header5.borders = borders5 style_header5.alignment = align5 fnt6 = Font() fnt6.name = 'Arial' fnt6.height = 300 fnt6.bold = True align_content6 = Alignment() align_content6.horz = Alignment.HORZ_CENTER borders6 = Borders() borders6.left = 0x02 borders6.right = 0x02 borders6.top = 0x02 borders6.bottom = 0x02 align6 = Alignment() align6.horz = Alignment.HORZ_CENTER align6.vert = Alignment.VERT_CENTER pattern6 = Pattern() pattern6.pattern6 = Pattern.SOLID_PATTERN pattern6.pattern6_fore_colour = 0x16 style_header6 = XFStyle() style_header6.font = fnt6 style_header6.pattern = pattern6 style_header6.borders = borders6 style_header6.alignment = align6 wb = Workbook() ws = wb.add_sheet('Category Wise Deduction') this = self.browse(cr, uid, ids[0], context=context) year = this.month.year_id.id month = this.month.month emp_id = this.employee_id.id company_id = this.company_id.id employee_type = this.employee_type employment_type = this.employment_type ws.row(0).height = 300 ws.row(1).height = 300 ws.row(2).height = 300 ws.col(1).width = 8000 ws.col(2).width = 6000 ws.col(3).width = 6000 ws.col(5).width = 4000 ws.col(6).width = 4000 ws.col(7).width = 4000 ws.col(8).width = 4000 ws.col(9).width = 5000 ws.col(10).width = 5000 ws.write_merge( 0, 0, 0, 11, 'COMPANY : ' + this.company_id.name + ' ' + this.company_id.street, style_header) ws.write_merge(1, 1, 0, 11, ('SALARY CHART FOR THE MONTH OF :', this.month.name), style_header) ws.write_merge(2, 2, 4, 5, 'Income', style_header1) ws.write_merge(2, 2, 6, 10, 'Deductions', style_header1) ws.write(2, 11, 'Net Amount', style_header1) ws.write(3, 0, 'PCard', style_header) ws.write(3, 1, 'Employee Name', style_header) ws.write(3, 2, 'Department Name', style_header) ws.write(3, 3, 'Designation Name', style_header) ws.write(3, 4, 'Total Salary', style_header) ws.write(3, 5, 'OT Amount', style_header) ws.write(3, 6, 'PF Deducted', style_header) ws.write(3, 7, 'TDS Deducted', style_header) ws.write(3, 8, 'ESI Deducted', style_header) ws.write(3, 9, 'Professional Tax', style_header) ws.write(3, 10, 'ADVANCE Deducted', style_header) ws.write(3, 11, 'Net Amount', style_header) i = 4 total_epf = 0.0 total_tds = 0.0 total_esi = 0.0 total_kharcha = 0.0 total_ot_salary = 0.0 total_a_gross = 0.0 total_grand_payment = 0.0 total_professional_tax = 0.0 list_val = [] list_ids = [] emp_obj = self.pool.get('hr.employee') if this.employee_id and this.employee_type: ws.write_merge(2, 2, 1, 2, ('Employee Type : ', this.employee_type), style_header1) list_ids = emp_obj.search(cr, uid, [('id', '=', emp_id), ('active', '=', True), ('employee_type', '=', employee_type), ('type', '=', 'Employee')]) elif this.employee_id and this.employment_type: ws.write_merge(2, 2, 1, 2, ('Employment Type : ', this.employment_type), style_header1) list_ids = emp_obj.search( cr, uid, [('id', '=', emp_id), ('active', '=', True), ('employment_type', '=', employment_type), ('type', '=', 'Employee')]) elif this.company_ids and this.employee_type: ws.write_merge(2, 2, 1, 2, ('Employee Type : ', this.employee_type), style_header1) for val in this.company_ids: list_val = emp_obj.search( cr, uid, [('active', '=', True), ('company_id', '=', val.id), ('employee_type', '=', employee_type), ('type', '=', 'Employee')]) list_ids = list_val + list_ids elif this.company_ids and this.employment_type: ws.write_merge(2, 2, 1, 2, ('Employment Type : ', this.employment_type), style_header1) for val in this.company_ids: list_val = emp_obj.search( cr, uid, [('active', '=', True), ('company_id', '=', val.id), ('employment_type', '=', employment_type), ('type', '=', 'Employee')]) list_ids = list_val + list_ids elif not this.employee_type and not this.employment_type: raise osv.except_osv( ('Warning !'), ("Please Select Employee or Employment Type !!!")) elif this.employee_type: ws.write_merge(2, 2, 1, 2, ('Employee Type : ', this.employee_type), style_header1) list_ids = emp_obj.search(cr, uid, [('active', '=', True), ('employee_type', '=', employee_type), ('type', '=', 'Employee')]) elif this.employment_type: ws.write_merge(2, 2, 1, 2, ('Employment Type : ', this.employment_type), style_header1) list_ids = emp_obj.search( cr, uid, [('active', '=', True), ('employment_type', '=', employment_type), ('type', '=', 'Employee')]) else: list_ids = emp_obj.search(cr, uid, [('active', '=', True), ('type', '=', 'Employee')]) if len(list_ids) == 0: raise osv.except_osv(('Warning !'), ("Record Not Found !!!")) if len(list_ids) == 1: query ="select spl.employee_name,spl.sinid,spl.department_name,spl.job_name,spl.epf,spl.tds,spl.esi,spl.kharcha,sum(spl.overtime_amount+spl.sun_overtime_amount),sum(spl.days_amount+spl.other_salary_amount),spl.grand_total,spl.pro_tax_amt"\ " from salary_payment_line as spl where spl.employee_id = '"+str(list_ids[0])+"' and spl.month='"+str(month)+"' and spl.year_id='"+str(year)+"' group by "\ " spl.employee_name,spl.sinid,spl.department_name,spl.job_name,spl.epf,spl.tds,spl.esi,spl.kharcha,spl.grand_total,spl.pro_tax_amt order by spl.sinid " cr.execute(query) temp = cr.fetchall() else: query ="select spl.employee_name,spl.sinid,spl.department_name,spl.job_name,spl.epf,spl.tds,spl.esi,spl.kharcha,sum(spl.overtime_amount+spl.sun_overtime_amount),sum(spl.days_amount+spl.other_salary_amount),spl.grand_total,spl.pro_tax_amt"\ " from salary_payment_line as spl where spl.employee_id in "+str(tuple(list_ids))+" and spl.month='"+str(month)+"' and spl.year_id='"+str(year)+"' group by " \ " spl.employee_name,spl.sinid,spl.department_name,spl.job_name,spl.epf,spl.tds,spl.esi,spl.kharcha,spl.grand_total,spl.pro_tax_amt order by spl.sinid" cr.execute(query) temp = cr.fetchall() if not temp: raise osv.except_osv(_('Warning !'), _("Record Not Found !!!")) for val in temp: total_epf += val[4] total_tds += val[5] total_esi += val[6] total_kharcha += val[7] total_ot_salary += val[8] total_a_gross += val[9] total_grand_payment += val[10] total_professional_tax += val[11] ws.write(i, 0, (val[1]), style_header3) ws.write(i, 1, (val[0]), style_header3) ws.write(i, 2, (val[2]), style_header3) ws.write(i, 3, (val[3]), style_header3) ws.write(i, 4, (val[9]), style_header3) ws.write(i, 5, (val[8]), style_header3) ws.write(i, 6, (val[4]), style_header3) ws.write(i, 7, (val[5]), style_header3) ws.write(i, 8, (val[6]), style_header3) ws.write(i, 9, (val[11]), style_header3) ws.write(i, 10, (val[7]), style_header3) ws.write(i, 11, (val[10]), style_header3) i = i + 1 ws.write(i, 3, 'TOTAL', style_header5) ws.write(i, 4, total_a_gross, style_header5) ws.write(i, 5, total_ot_salary, style_header5) ws.write(i, 6, total_epf, style_header5) ws.write(i, 7, total_tds, style_header5) ws.write(i, 8, total_esi, style_header5) ws.write(i, 9, total_professional_tax, style_header5) ws.write(i, 10, total_kharcha, style_header5) ws.write(i, 11, total_grand_payment, style_header5) f = cStringIO.StringIO() wb.save(f) out = base64.encodestring(f.getvalue()) ot_report = self.write(cr, uid, ids, { 'export_data': out, 'filename': 'Salary CHART.xls' }, context=context) return ot_report
def performance_register_report(self, cr, uid, ids, data, context=None): obj = self.browse(cr, uid, ids) emp_obj = self.pool.get('hr.employee') f_name = '' d_name = '' wb = Workbook() ws = wb.add_sheet('Contractor Payment Bonus') total_salary = apr_salary = may_salary = june_salary = july_salary = aug_salary = sep_salary = oct_salary = nov_salary = dec_salary = jan_salary = feb_salary = mar_salary = 0 fnt1 = Font() fnt1.name = 'Arial' fnt1.height = 300 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 fnt2 = Font() fnt2.name = 'Arial' fnt2.height = 300 fnt2.bold = True align_content2 = Alignment() align_content2.horz = Alignment.HORZ_CENTER borders2 = Borders() borders2.left = 0x00 borders2.right = 0x00 borders2.top = 0x00 borders2.bottom = 0x00 align2 = Alignment() align2.horz = Alignment.HORZ_CENTER align2.vert = Alignment.VERT_CENTER pattern2 = Pattern() pattern2.pattern2 = Pattern.SOLID_PATTERN pattern2.pattern2_fore_colour = 0x1F style_header2 = XFStyle() style_header2.font = fnt2 style_header2.pattern = pattern2 style_header2.borders = borders2 style_header2.alignment = align2 fnt3 = Font() fnt3.name = 'Arial' fnt3.height = 300 fnt3.bold = True align_content3 = Alignment() align_content3.horz = Alignment.HORZ_CENTER borders3 = Borders() borders3.left = 0x00 borders3.right = 0x00 borders3.top = 0x00 borders3.bottom = 0x00 align3 = Alignment() align3.horz = Alignment.HORZ_CENTER align3.vert = Alignment.VERT_CENTER pattern3 = Pattern() pattern3.pattern3 = Pattern.SOLID_PATTERN pattern3.pattern3_fore_colour = 0x1F style_header3 = XFStyle() style_header3.font = fnt3 style_header3.pattern = pattern3 style_header3.borders = borders3 style_header3.alignment = align3 fnt = Font() fnt.name = 'Arial' fnt.height = 275 content_fnt = Font() content_fnt.name = 'Arial' content_fnt.height = 150 align_content = Alignment() align_content.horz = Alignment.HORZ_CENTER borders = Borders() borders.left = 0x02 borders.right = 0x02 borders.top = 0x02 borders.bottom = 0x02 align = Alignment() align.horz = Alignment.HORZ_CENTER align.vert = Alignment.VERT_CENTER pattern = Pattern() pattern.pattern = Pattern.SOLID_PATTERN pattern.pattern_fore_colour = 0x1F style_header = XFStyle() style_header.font = fnt style_header.pattern = pattern style_header.borders = borders style_header.alignment = align fnt5 = Font() fnt5.name = 'Arial' fnt5.height = 200 content_fnt5 = Font() content_fnt5.name = 'Arial' content_fnt5.height = 150 align_content5 = Alignment() align_content5.horz = Alignment.HORZ_CENTER borders5 = Borders() borders5.left = 0x02 borders5.right = 0x02 borders5.top = 0x02 borders5.bottom = 0x02 align5 = Alignment() align5.horz = Alignment.HORZ_CENTER align5.vert = Alignment.VERT_CENTER pattern5 = Pattern() # pattern5.pattern = Pattern.SOLID_PATTERN # pattern5.pattern_fore_colour = 0x1F style_header5 = XFStyle() style_header5.font = fnt5 style_header5.pattern = pattern5 style_header5.borders = borders5 style_header5.alignment = align5 if obj.partner_id: get_name = obj.partner_id.name elif obj.company_id: get_name = obj.company_id.name + ' ' + obj.company_id.street + ' ' + ',' + obj.company_id.city + ' ' + '-' + obj.company_id.zip else: get_name = obj.employee_id.partner_id.name ws.row(0).height = 500 ws.write_merge(0, 0, 0, 19, get_name, style_header1) date1 = datetime.strptime(obj.from_date, "%Y-%m-%d").timetuple().tm_year date2 = datetime.strptime(obj.till_date, "%Y-%m-%d").timetuple().tm_year if date1 == date2: d_name = 'BONUS' + ' - ' + str(date1) else: d_name = 'BONUS' + ' ' + str(date1) + ' ' + '-' + ' ' + str(date2) ws.row(1).height = 500 ws.write_merge(1, 1, 0, 19, d_name, style_header2) ws.col(0).width = 5000 ws.col(1).width = 7500 ws.col(2).width = 5000 ws.col(3).width = 3000 ws.col(4).width = 3000 ws.col(5).width = 3000 ws.col(6).width = 3000 ws.col(7).width = 3000 ws.col(8).width = 3000 ws.col(9).width = 3000 ws.col(10).width = 3000 ws.col(11).width = 3000 ws.col(12).width = 3000 ws.col(13).width = 3000 ws.col(14).width = 3000 ws.col(15).width = 3000 ws.col(16).width = 3000 ws.col(17).width = 3000 ws.col(18).width = 3000 ws.col(19).width = 3000 ws.col(20).width = 3000 ws.col(21).width = 3000 ws.col(22).width = 3000 ws.col(23).width = 3000 ws.col(24).width = 3000 ws.col(25).width = 3000 ws.col(26).width = 3000 ws.col(27).width = 3000 ws.col(28).width = 3000 ws.col(29).width = 4000 ws.row(2).height = 400 ws.write(2, 0, 'EMP. CODE', style_header) ws.write(2, 1, 'NAME', style_header) ws.write(2, 2, 'JOINING DATE', style_header) ws.write_merge(2, 2, 3, 4, 'APRIL', style_header) ws.write_merge(2, 2, 5, 6, 'MAY', style_header) ws.write_merge(2, 2, 7, 8, 'JUNE', style_header) ws.write_merge(2, 2, 9, 10, 'JULY', style_header) ws.write_merge(2, 2, 11, 12, 'AUGUST', style_header) ws.write_merge(2, 2, 13, 14, 'SEPTEMBER', style_header) ws.write_merge(2, 2, 15, 16, 'OCTOBER', style_header) ws.write_merge(2, 2, 17, 18, 'NOVEMBER', style_header) ws.write_merge(2, 2, 19, 20, 'DECEMBER', style_header) ws.write_merge(2, 2, 21, 22, 'JANUARY', style_header) ws.write_merge(2, 2, 23, 24, 'FEBRUARY', style_header) ws.write_merge(2, 2, 25, 26, 'MARCH', style_header) ws.write_merge(2, 2, 27, 28, 'TOTAL', style_header) ws.write(2, 29, 'BONUS', style_header) ws.row(3).height = 400 ws.write(3, 0, '', style_header) ws.write(3, 1, '', style_header) ws.write(3, 2, '', style_header) ws.write(3, 3, 'DAYS', style_header) ws.write(3, 4, 'SALARY', style_header) ws.write(3, 5, 'DAYS', style_header) ws.write(3, 6, 'SALARY', style_header) ws.write(3, 7, 'DAYS', style_header) ws.write(3, 8, 'SALARY', style_header) ws.write(3, 9, 'DAYS', style_header) ws.write(3, 10, 'SALARY', style_header) ws.write(3, 11, 'DAYS', style_header) ws.write(3, 12, 'SALARY', style_header) ws.write(3, 13, 'DAYS', style_header) ws.write(3, 14, 'SALARY', style_header) ws.write(3, 15, 'DAYS', style_header) ws.write(3, 16, 'SALARY', style_header) ws.write(3, 17, 'DAYS', style_header) ws.write(3, 18, 'SALARY', style_header) ws.write(3, 19, 'DAYS', style_header) ws.write(3, 20, 'SALARY', style_header) ws.write(3, 21, 'DAYS', style_header) ws.write(3, 22, 'SALARY', style_header) ws.write(3, 23, 'DAYS', style_header) ws.write(3, 24, 'SALARY', style_header) ws.write(3, 25, 'DAYS', style_header) ws.write(3, 26, 'SALARY', style_header) ws.write(3, 27, 'DAYS', style_header) ws.write(3, 28, 'SALARY', style_header) ws.write(3, 29, '', style_header) if obj.partner_id: list_ids = emp_obj.search(cr, uid, [('partner_id', '=', obj.partner_id.id), ('active', '=', True), ('type', '=', 'Contractor')]) list_ids1 = emp_obj.search(cr, uid, [('partner_id', '=', obj.partner_id.id), ('active', '=', False), ('type', '=', 'Contractor')]) list_ids = list_ids + list_ids1 elif obj.employee_id: list_ids = emp_obj.search(cr, uid, [('id', '=', obj.employee_id.id), ('active', '=', True), ('type', '=', 'Contractor')]) elif obj.company_id: list_ids = emp_obj.search(cr, uid, [('company_id', '=', obj.company_id.id), ('active', '=', True), ('type', '=', 'Contractor')]) list_ids1 = emp_obj.search(cr, uid, [('company_id', '=', obj.company_id.id), ('active', '=', False), ('type', '=', 'Contractor')]) list_ids = list_ids + list_ids1 else: raise osv.except_osv( _('Warning !'), _("Please Select Atleast Company Or Employee.")) if len(list_ids) == 1: query ="select hr.sinid,rr.name,hr.doj,sum(pmbl.apr),sum(pmbl.may),sum(pmbl.june),sum(pmbl.july),sum(pmbl.aug),sum(pmbl.sep),sum(pmbl.oct),"\ "sum(pmbl.nov),sum(pmbl.dec),sum(pmbl.jan),sum(pmbl.feb),sum(pmbl.mar),sum(pmbl.total_day),sum(pmbl.bonus),sum(pmbl.apr_salary),"\ "sum(pmbl.may_salary),sum(pmbl.june_salary),sum(pmbl.july_salary),sum(pmbl.aug_salary),sum(pmbl.sep_salary),sum(pmbl.oct_salary),"\ "sum(pmbl.nov_salary),sum(pmbl.dec_salary),sum(pmbl.jan_salary),sum(pmbl.feb_salary),sum(pmbl.mar_salary),sum(pmbl.total_salary)"\ "from payment_management_bonus_line as pmbl left join hr_employee as hr on pmbl.employee_id = hr.id left join resource_resource as rr on hr.resource_id = rr.id "\ "where pmbl.employee_id = '"+str(list_ids[0])+"' and pmbl.bonus_from >= '"+str(obj.from_date)+"' and pmbl.bonus_till <= '"+str(obj.till_date)+"' group by hr.sinid,rr.name,hr.doj order by hr.sinid " cr.execute(query) temp = cr.fetchall() else: query ="select hr.sinid,rr.name,hr.doj,sum(pmbl.apr),sum(pmbl.may),sum(pmbl.june),sum(pmbl.july),sum(pmbl.aug),sum(pmbl.sep),sum(pmbl.oct),"\ "sum(pmbl.nov),sum(pmbl.dec),sum(pmbl.jan),sum(pmbl.feb),sum(pmbl.mar),sum(pmbl.total_day),sum(pmbl.bonus),sum(pmbl.apr_salary),"\ "sum(pmbl.may_salary),sum(pmbl.june_salary),sum(pmbl.july_salary),sum(pmbl.aug_salary),sum(pmbl.sep_salary),sum(pmbl.oct_salary),"\ "sum(pmbl.nov_salary),sum(pmbl.dec_salary),sum(pmbl.jan_salary),sum(pmbl.feb_salary),sum(pmbl.mar_salary),sum(pmbl.total_salary)"\ "from payment_management_bonus_line as pmbl left join hr_employee as hr on pmbl.employee_id = hr.id left join resource_resource as rr on hr.resource_id = rr.id "\ "where pmbl.employee_id in "+str(tuple(list_ids))+" and pmbl.bonus_from >= '"+str(obj.from_date)+"' and pmbl.bonus_till <= '"+str(obj.till_date)+"' group by hr.sinid,rr.name,hr.doj order by hr.sinid" cr.execute(query) temp = cr.fetchall() if not temp: raise osv.except_osv(_('Warning !'), _("Record Not Found !!!")) columnno = 4 for val in temp: doj = datetime.strptime(val[2], "%Y-%m-%d").strftime("%d-%m-%Y") ws.write(columnno, 0, val[0], style_header5) ws.write(columnno, 1, val[1], style_header5) ws.write(columnno, 2, doj, style_header5) ws.write(columnno, 3, val[3], style_header5) ws.write(columnno, 4, val[17], style_header5) ws.write(columnno, 5, val[4], style_header5) ws.write(columnno, 6, val[18], style_header5) ws.write(columnno, 7, val[5], style_header5) ws.write(columnno, 8, val[19], style_header5) ws.write(columnno, 9, val[6], style_header5) ws.write(columnno, 10, val[20], style_header5) ws.write(columnno, 11, val[7], style_header5) ws.write(columnno, 12, val[21], style_header5) ws.write(columnno, 13, val[8], style_header5) ws.write(columnno, 14, val[22], style_header5) ws.write(columnno, 15, val[9], style_header5) ws.write(columnno, 16, val[23], style_header5) ws.write(columnno, 17, val[10], style_header5) ws.write(columnno, 18, val[24], style_header5) ws.write(columnno, 19, val[11], style_header5) ws.write(columnno, 20, val[25], style_header5) ws.write(columnno, 21, val[12], style_header5) ws.write(columnno, 22, val[26], style_header5) ws.write(columnno, 23, val[13], style_header5) ws.write(columnno, 24, val[27], style_header5) ws.write(columnno, 25, val[14], style_header5) ws.write(columnno, 26, val[28], style_header5) ws.write(columnno, 27, val[15], style_header5) ws.write(columnno, 28, val[29], style_header5) ws.write(columnno, 29, val[16], style_header5) columnno += 1 f = cStringIO.StringIO() wb.save(f) out = base64.encodestring(f.getvalue()) return self.write(cr, uid, ids, { 'export_data': out, 'filename': 'Contractor Payment Bonus.xls' }, context=context)
def generate_report(root, filename, attend_staff, absent_staff, meeting_direct, record_staff, reports, issues): book = Workbook(encoding='utf-8') sheet1 = book.add_sheet('Fota Security Knox Week Report') line_counter = 0 today = datetime.date.today().strftime('%Y/%m/%d') # header head_str = [ 'Meeting Name', 'SW 3 Group Part 5 Fota/ Security/Knox/Encription TG WeekReport', '', '', 'Meeting Date', today, 'Meeting Address', '8F Meeting Room', '', '', 'Attend Staff', attend_staff, 'Absent Staff', absent_staff, '', '', 'Meeting Direct', meeting_direct, 'Record Staff', record_staff, ] for i in range(len(head_str) / 2): sheet1.row(i).write(0, head_str[2 * i]) sheet1.row(i).write(1, head_str[2 * i + 1]) line_counter = len(head_str) / 2 + 1 # font bold font = Font() font.bold = True # pattern yellow pattern_yellow = Pattern() pattern_yellow.pattern = Pattern.SOLID_PATTERN pattern_yellow.pattern_fore_colour = 0x0D # yellow # pattern gray pattern_gray = Pattern() pattern_gray.pattern = Pattern.SOLID_PATTERN pattern_gray.pattern_fore_colour = 0x17 # gray # borders thin borders = Borders() borders.left = Borders.THIN borders.right = Borders.THIN borders.top = Borders.THIN borders.bottom = Borders.THIN # alignment horizontal center alig_hc = Alignment() # alig.horizontal = Alignment.HORZ_CENTER #no effect, why? f**k!!! alig_hc.horz = Alignment.HORZ_CENTER # title style style_title = XFStyle() style_title.font = font style_title.pattern = pattern_yellow style_title.alignment = alig_hc sheet1.write_merge( line_counter, line_counter, 0, 3, 'summary', style_title) line_counter += 1 # table header style sytle_tb_header = XFStyle() sytle_tb_header.font = font sytle_tb_header.pattern = pattern_gray sytle_tb_header.borders = borders sytle_tb_header.alignment = alig_hc sheet1.row(line_counter).write(0, 'Member', sytle_tb_header) sheet1.row(line_counter).write(1, 'Week Jobs', sytle_tb_header) sheet1.row(line_counter).write(2, 'Risk', sytle_tb_header) sheet1.row(line_counter).write(3, 'Next Week Plan', sytle_tb_header) line_counter += 1 # content # for i in range(line_counter,11+line_counter): i = line_counter for report in reports: # alignment alig = Alignment() alig.horz = Alignment.HORZ_CENTER alig.vert = Alignment.VERT_CENTER alig.wrap = 1 # alignment2 alig2 = Alignment() alig2.vert = Alignment.VERT_CENTER alig2.wrap = 1 # borders borders = Borders() borders.left = Borders.THIN borders.right = Borders.THIN borders.top = Borders.THIN borders.bottom = Borders.THIN # colors pattern = Pattern() pattern.pattern = Pattern.SOLID_PATTERN pattern.pattern_fore_colour = 0x2F style_content = XFStyle() style_content.alignment = alig style_content.borders = borders style_content2 = XFStyle() style_content2.alignment = alig2 style_content2.borders = borders style_content2.pattern = pattern style_content3 = XFStyle() style_content3.alignment = alig2 style_content3.borders = borders sheet1.row(i).write(0, report.who, style_content) sheet1.row(i).write(1, report.job, style_content2) sheet1.row(i).write(2, report.risk, style_content2) sheet1.row(i).write(3, report.plan, style_content3) sheet1.row(i).height_mismatch = True sheet1.row(i).height = 1500 i += 1 line_counter += len(reports) line_counter += 1 sheet1.write_merge( line_counter, line_counter, 0, 3, 'Main Issues List', style_title) line_counter += 1 sheet1.row(line_counter).write(0, 'Items', sytle_tb_header) sheet1.write_merge( line_counter, line_counter, 1, 2, 'Deatail', sytle_tb_header) sheet1.row(line_counter).write(3, 'Status', sytle_tb_header) line_counter += 1 sheet1.col(0).width = 256 * 16 sheet1.col(1).width = 256 * 50 sheet1.col(2).width = 256 * 31 sheet1.col(3).width = 256 * 46 book.save(root + '/' + filename)
def exportReport5ToExcel(self,objectProject): book = Workbook(); sheet1 = book.add_sheet('Sheet 1'); sheet1.col(1).width = 256*80; sheet1.col(2).width = 256*10; sheet1.col(3).width = 256*20; borders = Borders() borders.left = Borders.THIN borders.right = Borders.THIN borders.top = Borders.THIN borders.bottom = Borders.THIN pattern = Pattern(); pattern.pattern = Pattern.SOLID_PATTERN pattern.pattern_fore_colour = 23 wrap = Alignment(); wrap.wrap = 1; wrap.vert = Alignment.VERT_TOP alignHeader = Alignment(); alignHeader.horz = Alignment.HORZ_CENTER; alignTop = Alignment(); alignTop.vert = Alignment.VERT_TOP fnt = Font() fnt.name = 'Arial' fnt.colour_index = 4 fnt.bold = True styleWrap = XFStyle(); styleWrap.alignment = wrap; styleHead = XFStyle(); styleHead.font = fnt; styleHead.borders = borders; styleHead.pattern = pattern; styleHead.alignment = alignHeader; styleRowDetail = XFStyle(); styleRowDetail.borders = borders; styleRowDetail.alignment = alignTop; styleDate = XFStyle() styleDate.num_format_str = 'DD-MM-YYYY' ; #'D-MMM-YY'; styleDate.borders = borders; styleDate.alignment = alignTop; StyleRowDetailWrap = styleRowDetail ; StyleRowDetailWrap.alignment = wrap; if( objectProject): i=0; row1 = sheet1.row(i) ; row1.write(0, ('risk id').decode('UTF8'),styleHead ); #sheet1.write_merge(i, i, 1, 2, ('รายละเอียด').decode('UTF8') ); row1.write(1, ('รายละเอียด').decode('UTF8'),styleHead); row1.write(2, ('วันที่รายงาน').decode('UTF8'),styleHead ); row1.write(3, ('หน่วยที่รายงาน').decode('UTF8') ,styleHead); i=i+1; for value in objectProject: row1 = sheet1.row(i) ; row1.write(0, value.get('risk_management_id') ,styleRowDetail ); row1.write(1, value.get('risk_detail').decode('UTF8'),StyleRowDetailWrap ); #sheet1.write_merge(i, i, 1, 2, value.get('risk_detail').decode('UTF8') , StyleRowDetailWrap ); row1.write(2, value.get('report_date') ,styleDate ); row1.write(3, value.get('report').decode('UTF8') ,styleRowDetail ); i=i+1; for sub in value.get('response') : row1 = sheet1.row(i) ; row1.write(0," " ); text = "(" + sub.get('risk_team').decode('UTF8') + " ) " + sub.get('result').decode('UTF8'); row1.write(1, text ,StyleRowDetailWrap ); i=i+1; dirTempFile = gettempdir() + _os.sep + str('simpleReport5.xls'); book.save(dirTempFile); return dirTempFile;
def print_report(self, cr, uid, ids, context=None): wb = Workbook() ws = wb.add_sheet('Earn Leave Report') fnt1 = Font() fnt1.name = 'Arial' fnt1.height = 300 fnt1.bold = True align_content1 = Alignment() align_content1.horz = Alignment.HORZ_CENTER borders1 = Borders() borders1.left = 0x02 borders1.right = 0x02 borders1.top = 0x02 borders1.bottom = 0x02 align1 = Alignment() align1.horz = Alignment.HORZ_CENTER align1.vert = Alignment.VERT_CENTER pattern1 = Pattern() pattern1.pattern1 = Pattern.SOLID_PATTERN pattern1.pattern1_fore_colour = 0x17 style_header1 = XFStyle() style_header1.font = fnt1 style_header1.pattern = pattern1 style_header1.borders = borders1 style_header1.alignment = align1 fnt2 = Font() fnt2.name = 'Arial' fnt2.height = 250 fnt2.bold = False align_content2 = Alignment() align_content2.horz = Alignment.HORZ_CENTER borders2 = Borders() borders2.left = 0x02 borders2.right = 0x02 borders2.top = 0x02 borders2.bottom = 0x02 align2 = Alignment() align2.horz = Alignment.HORZ_CENTER align2.vert = Alignment.VERT_CENTER pattern2 = Pattern() pattern2.pattern2 = Pattern.SOLID_PATTERN pattern2.pattern2_fore_colour = 0x09 style_header2 = XFStyle() style_header2.font = fnt2 style_header2.pattern = pattern2 style_header2.borders = borders2 style_header2.alignment = align2 fnt3 = Font() fnt3.name = 'Arial' fnt3.height = 275 fnt3.bold = False align_content3 = Alignment() align_content3.horz = Alignment.HORZ_CENTER borders3 = Borders() borders3.left = 0x02 borders3.right = 0x02 borders3.top = 0x02 borders3.bottom = 0x02 align3 = Alignment() align3.horz = Alignment.HORZ_CENTER align3.vert = Alignment.VERT_CENTER pattern3 = Pattern() pattern3.pattern3 = Pattern.SOLID_PATTERN pattern3.pattern3_fore_colour = 0x09 style_header3 = XFStyle() style_header3.font = fnt3 style_header3.pattern = pattern3 style_header3.borders = borders3 style_header3.alignment = align3 fnt = Font() fnt.name = 'Arial' fnt.height = 275 content_fnt = Font() content_fnt.name = 'Arial' content_fnt.height = 150 align_content = Alignment() align_content.horz = Alignment.HORZ_CENTER borders = Borders() borders.left = 0x02 borders.right = 0x02 borders.top = 0x02 borders.bottom = 0x02 align = Alignment() align.horz = Alignment.HORZ_CENTER align.vert = Alignment.VERT_CENTER pattern = Pattern() pattern.pattern = Pattern.SOLID_PATTERN pattern.pattern_fore_colour = 0x16 style_header = XFStyle() style_header.font = fnt style_header.pattern = pattern style_header.borders = borders style_header.alignment = align fnt5 = Font() fnt5.name = 'Arial' fnt5.height = 275 content_fnt5 = Font() content_fnt5.name = 'Arial' content_fnt5.height = 150 align_content5 = Alignment() borders5 = Borders() borders5.left = 0x02 borders5.right = 0x02 borders5.top = 0x02 borders5.bottom = 0x02 align5 = Alignment() align5.vert = Alignment.VERT_JUSTIFIED pattern5 = Pattern() pattern5.pattern = Pattern.SOLID_PATTERN pattern5.pattern_fore_colour = 0x16 style_header5 = XFStyle() style_header5.font = fnt5 style_header5.pattern = pattern5 style_header5.borders = borders5 style_header5.alignment = align5 ws.row(0).height = 1000 ws.col(0).width = 4000 ws.col(1).width = 5000 ws.col(2).width = 4000 ws.col(3).width = 5000 ws.col(4).width = 5000 ws.col(5).width = 6500 ws.col(6).width = 4000 ws.col(7).width = 4000 ws.col(8).width = 4000 ws.write(0, 0, 'Employee ID', style_header) ws.write(0, 1, 'Employee Code', style_header) ws.write(0, 2, 'Earn Date', style_header) ws.write(0, 3, 'Total Work Day', style_header) ws.write(0, 4, 'Allocation EL', style_header) ws.write(0, 5, 'Opening Earn Leave', style_header) ws.write(0, 6, 'Total EL Paid', style_header) ws.write(0, 7, 'Balance EL', style_header) ws.write(0, 8, 'Import EL', style_header) this = self.browse(cr, uid, ids[0], context=context) company_id = this.company_id.id employee_id = this.employee_id.id emp_obj = self.pool.get('hr.employee') year = this.month.year_id.id balance1 = 0 balance = 0 import_el = 0 i = 1 if this.company_id and this.employee_id: list_ids = emp_obj.search(cr, uid, [('company_id', '=', company_id), ('id', '=', employee_id), ('active', '=', True), ('type', '=', 'Employee')]) elif this.company_id: list_ids = emp_obj.search(cr, uid, [('company_id', '=', company_id), ('active', '=', True), ('type', '=', 'Employee')]) if len(list_ids) == 0: raise osv.except_osv(('Warning !'), ("Record Not Found !!!")) if len(list_ids) == 1: query = "select spl.sinid,sum(spl.work_day),hr.earn_date,sum(spl.earned_leave),hr.earn_open,hr.id from salary_payment_line as spl left join hr_employee as hr on spl.employee_id = hr.id left join resource_resource as rr on hr.resource_id = rr.id where spl.employee_id = '" + str( list_ids[0] ) + "' and spl.year_id='" + str( year ) + "' and rr.active = 'True' group by spl.sinid,hr.earn_date,hr.earn_open,hr.id order by spl.sinid" cr.execute(query) temp = cr.fetchall() else: query = "select spl.sinid,sum(spl.work_day),hr.earn_date,sum(spl.earned_leave),hr.earn_open,hr.id from salary_payment_line as spl left join hr_employee as hr on spl.employee_id = hr.id left join resource_resource as rr on hr.resource_id = rr.id where spl.employee_id in " + str( tuple(list_ids) ) + " and spl.year_id='" + str( year ) + "' and rr.active = 'True' group by spl.sinid,hr.earn_date,hr.earn_open,hr.id order by spl.sinid" cr.execute(query) temp = cr.fetchall() if not temp: raise osv.except_osv(_('Warning !'), _("Record Not Found !!!")) for val in temp: day = format((val[1] / 20), '.2f') b = str(day) c = b.split('.') d = c[0] e = c[1][0:2] if int(e) < 50: s = d + '.' + str(0) work_day = float(s) else: s = d + '.' + e t = float(s) work_day = math.ceil(t) if val[4] != None: balance1 = (work_day + val[4] - val[3]) import_el = work_day + val[4] else: balance1 = (work_day - val[3]) import_el = work_day if balance1 >= 30: balance = 30 else: balance = balance1 ws.row(i).height = 500 ws.write(i, 0, val[5], style_header2) ws.write(i, 1, val[0], style_header2) ws.write(i, 2, val[2], style_header2) ws.write(i, 3, val[1], style_header2) ws.write(i, 4, work_day, style_header2) ws.write(i, 5, val[4], style_header2) ws.write(i, 6, val[3], style_header2) ws.write(i, 7, balance, style_header2) ws.write(i, 8, import_el, style_header2) i = i + 1 f = cStringIO.StringIO() wb.save(f) out = base64.encodestring(f.getvalue()) pf_upload_report = self.write(cr, uid, ids, { 'export_data': out, 'filename': 'Earn Leave Report.xls' }, context=context) return pf_upload_report
def account_budget(self,cr,uid,ids,context=None): this=self.browse(cr,uid,ids[0]) year = this.year_id.name year_id = this.year_id.id month = this.month if int(month) in [1,3,5,7,8,10,12]: join_date=year +'-'+month+'-'+'31' if int(month) in [4,6,9,11]: join_date=year +'-'+month+'-'+'30' if int(month) in [2]: if int(year) % 4 == 0: join_date=year +'-'+month+'-'+'29' else: join_date=year +'-'+month+'-'+'28' if month == '1': month_name = 'January' elif month == '2': month_name = 'February' elif month == '3': month_name = 'March' elif month == '4': month_name = 'April' elif month == '5': month_name = 'May' elif month == '6': month_name = 'June' elif month == '7': month_name = 'July' elif month == '8': month_name = 'August' elif month == '9': month_name = 'September' elif month == '10': month_name = 'October' elif month == '11': month_name = 'November' elif month == '12': month_name = 'December' else: raise osv.except_osv(_('Warning !'),_("Specify month correctly. ")) #Define the font attributes for header fnt = Font() fnt.name = 'Arial' fnt.height= 275 #Define the font attributes for header content_fnt = Font() content_fnt.name ='Arial' content_fnt.height =220 align_content = Alignment() align_content.horz= Alignment.HORZ_CENTER borders = Borders() borders.left = 0x02 borders.right = 0x02 borders.top = 0x02 borders.bottom = 0x02 #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 fnt1 = Font() fnt1.name = 'Arial' fnt1.height= 275 #Define the font attributes for header content_fnt1 = Font() content_fnt1.name ='Arial' content_fnt1.height =220 align_content1 = Alignment() align_content1.horz= Alignment.HORZ_CENTER borders1 = Borders() borders1.left = 0x02 borders1.right = 0x02 borders1.top = 0x02 borders1.bottom = 0x02 #The text should be centrally aligned align1 = Alignment() align1.horz = Alignment.HORZ_CENTER align1.vert = Alignment.VERT_CENTER #We set the backgroundcolour here pattern1 = Pattern() pattern1.pattern = Pattern.SOLID_PATTERN pattern1.pattern_fore_colour = 0x32 #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.height= 275 #Define the font attributes for header content_fnt2 = Font() content_fnt2.name ='Arial' content_fnt2.height =220 align_content2 = Alignment() align_content2.horz= Alignment.HORZ_CENTER borders2 = Borders() borders2.left = 0x02 borders2.right = 0x02 borders2.top = 0x02 borders2.bottom = 0x02 #The text should be centrally aligned align2 = Alignment() align2.horz = Alignment.HORZ_CENTER align2.vert = Alignment.VERT_CENTER #We set the backgroundcolour here pattern2 = Pattern() pattern2.pattern = Pattern.SOLID_PATTERN pattern2.pattern_fore_colour = 0x0A #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 style_content= XFStyle() style_content.alignment = align_content style_content.font = content_fnt month_name = 'Payment ('+str(month_name)+')' wb = Workbook() ws = wb.add_sheet('Budget') ws.row(0).height=500 ws.write(0,0,'Department Name',style_header) ws.col(0).width = 8000 ws.write(0,1,'Department HoD',style_header) ws.col(1).width = 8000 ws.write(0,2,'RO of HOD',style_header) ws.col(2).width = 8000 ws.write(0,3,'Allocated Budget',style_header) ws.col(3).width = 8000 ws.write(0,4,'Salary Amount',style_header) ws.col(4).width = 5000 ws.write(0,5,month_name,style_header) ws.col(5).width = 8000 ws.write(0,6,'Difference',style_header) ws.col(6).width = 8000 ws.write(0,7,'No Of Emp',style_header) ws.col(7).width = 8000 # ws.write(0,5,'O.T. Amount',style_header) # ws.col(5).width = 4400 # ws.write(0,6,'Total Amount',style_header) # ws.col(6).width = 4400 # ws.write(0,7,'Insentive Amount',style_header) # ws.col(7).width = 4400 # ws.write(0,8,'Deduction Amount',style_header) # ws.col(8).width = 5000 # ws.write(0,9,'Percentage',style_header) # ws.col(9).width = 4400 emp_ids = [] emp_obj=self.pool.get('hr.employee') pay_obj=self.pool.get('salary.payment.line') if this.dept_id: cr.execute("select emp.id from hr_employee as emp left join resource_resource " \ "as res on (emp.resource_id=res.id) left join hr_department as dept on " \ "(emp.department_id=dept.id) where emp.department_id = '"+str(this.dept_id.id)+"' and " \ "emp.department_id is not null and res.active=True and emp.joining_date <'"+str(join_date)+"' order by dept.dept_code, " \ "(substring(emp.sinid, '^[0-9]+'))::int ,substring(emp.sinid, '[^0-9_].*$')") temp = cr.fetchall() for data in temp: if len(data)>0 and data[0] != None: emp_ids.append(data[0]) else: cr.execute("select emp.id from hr_employee as emp left join resource_resource " \ "as res on (emp.resource_id=res.id) left join hr_department as dept on (emp.department_id=dept.id) " \ "where res.active=True and emp.joining_date <'"+str(join_date)+"' order by dept.dept_code,(substring(emp.sinid, '^[0-9]+'))::int ,substring(emp.sinid, '[^0-9_].*$')") temp = cr.fetchall() for data in temp: if len(data)>0 and data[0] != None: emp_ids.append(data[0]) holiday_obj = self.pool.get('holiday.list') if int(month) in [1,3,5,7,8,10,12]: month = 31 if int(month) in [4,6,9,11]: month = 30 if int(month) in [2]: if int(year) % 4 == 0: month = 29 else: month = 28 off_day = working_day = 0 holiday_ids = holiday_obj.search(cr, uid, [('month','=',this.month),('year_id','=',year_id)]) for line in holiday_obj.browse(cr, uid, holiday_ids): off_day = line.holiday working_day = month - off_day i=1 dept_dict = {} grand = total = pay_total = pay_grand = budget = total_budget = 0.0 pay_data = False flag = True count1=0.0 for each in emp_obj.browse(cr, uid, emp_ids): pay_ids = pay_obj.search(cr, uid, [('employee_id','=',each.id),('month','=',this.month),('year_id','=',this.year_id.id),('salary_type','=','Salary')]) if pay_ids: pay_data = pay_obj.browse(cr, uid, pay_ids[0]) if dept_dict.has_key(str(each.department_id.id)): if each.department_id: salary = 0.0 if pay_data.employee_id.daily: salary = pay_data.basic * working_day else: salary = pay_data.basic total += salary grand += salary # if pay_data.employee_id.id == each.id: # print"ssssssssssssssssssss minssssssssss",count1 # count1=count1+1 if pay_data and pay_data.employee_id.id == each.id: pay_total += pay_data.total_amount or 0.0 pay_grand += pay_data.total_amount or 0.0 count1=count1+1 else: pay_total += 0.0 pay_grand += 0.0 elif not each.department_id: if flag: i -= 1 ws.write(i,3, total) ws.write(i,4, pay_total) if budget: diff = float(budget) - pay_total else: diff = pay_total if budget and diff > 0: ws.write(i,5, diff,style_header1) elif budget and diff < 0: ws.write(i,5, diff,style_header2) else: ws.write(i,5, diff,style_header2) ws.write(i,6, count1) count1=0.0 flag = False i += 1 # salary = 0.0 # name = '[' + str(each.sinid) +'] '+ str(each.name) # # ws.write(i,0, 'X Department') # ws.write(i,1,'X Reporting Officer') # ws.write(i,2, name) # # if each.daily: # salary = each.salary * working_day # else: # salary = each.salary # ws.write(i,3, salary) # if pay_data and pay_data.employee_id.id == each.id: # ws.write(i,4,pay_data.total_amount or 0.0) # else: # ws.write(i,4, 0.0) else: dept_dict[str(each.department_id.id)] = '' if i != 1: i -= 1 ws.write(i,4, total) ws.write(i,5, pay_total) if budget: diff = float(budget) - pay_total else: diff = pay_total if budget and diff > 0: ws.write(i,6, diff,style_header1) elif budget and diff < 0: ws.write(i,6, diff,style_header2) else: ws.write(i,6, diff,style_header2) ws.write(i,7, count1) count1=0.0 i += 1 if each.department_id: total = pay_total = budget = 0.0 salary = 0.0 budget = each.department_id.dept_budget dept = '[' + str(each.department_id.dept_code) +'] '+ str(each.department_id.name) ws.write(i,0, dept) ws.write(i,1, each.department_id.manager_id and each.department_id.manager_id.name or '') ws.write(i,2, each.department_id.manager_id.parent_id and each.department_id.manager_id.parent_id.name or '') ws.write(i,3, budget) total_budget += budget if pay_data.employee_id.daily: salary = pay_data.basic * working_day else: salary = pay_data.basic total += salary grand += salary # if pay_data.employee_id.id == each.id: # print"saaaaaaaaaaaaaallllllllllllllllllllllllllll",count1 # count1=count1+1 if pay_data and pay_data.employee_id.id == each.id: pay_total += pay_data.total_amount or 0.0 pay_grand += pay_data.total_amount or 0.0 count1=count1+1 else: pay_total += 0.0 pay_grand += 0.0 i += 1 ws.write(i+3,1, 'Grand Total',style_header) ws.write(i+3,3, total_budget,style_header) ws.write(i+3,4, grand,style_header) ws.write(i+3,5, pay_grand,style_header) diff_grand = total_budget - pay_grand ws.write(i+3,6, diff_grand,style_header1) f = cStringIO.StringIO() wb.save(f) out=base64.encodestring(f.getvalue()) return self.write(cr, uid, ids, {'export_data':out, 'filename':'export.xls'}, context=context)
def report_get(self, cr, uid, ids, context=None): this = self.browse(cr, uid, ids) #Define the font attributes for header fnt = Font() fnt.name = 'Arial' fnt.height = 275 #Define the font attributes for header content_fnt = Font() content_fnt.name = 'Arial' content_fnt.height = 220 align_content = Alignment() align_content.horz = Alignment.HORZ_LEFT borders = Borders() borders.left = 0x02 borders.right = 0x02 borders.top = 0x02 borders.bottom = 0x02 #The text should be centrally aligned align = Alignment() align.horz = Alignment.HORZ_LEFT align.vert = Alignment.VERT_TOP align.wrap = Alignment.WRAP_AT_RIGHT #The text should be right aligned align1 = Alignment() align1.horz = Alignment.HORZ_RIGHT align1.vert = Alignment.VERT_TOP align1.wrap = Alignment.WRAP_AT_RIGHT #The content should be left aligned align2 = Alignment() align2.horz = Alignment.HORZ_LEFT align2.vert = Alignment.VERT_TOP align2.wrap = Alignment.WRAP_AT_RIGHT #The content should be right aligned align3 = Alignment() align3.horz = Alignment.HORZ_RIGHT align3.vert = Alignment.VERT_TOP align3.wrap = Alignment.WRAP_AT_RIGHT #We set the backgroundcolour here pattern = Pattern() pattern.pattern = Pattern.SOLID_PATTERN pattern.pattern_fore_colour = 0x1F #We set the backgroundcolour here pattern1 = Pattern() pattern1.pattern = Pattern.SOLID_PATTERN pattern1.pattern_fore_colour = 0x17 #We set the backgroundcolour here pattern2 = Pattern() pattern2.pattern = Pattern.SOLID_PATTERN pattern2.pattern_fore_colour = 0xFF #We set the backgroundcolour here pattern3 = Pattern() pattern3.pattern = Pattern.SOLID_PATTERN pattern3.pattern_fore_colour = 0xFF #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 #apply the above settings to the row(1) header style_header1 = XFStyle() style_header1.font = fnt style_header1.pattern = pattern1 style_header1.borders = borders style_header1.alignment = align1 #apply the above settings to the content style_content_left = XFStyle() style_content_left.font = fnt style_content_left.pattern = pattern2 style_content_left.borders = borders style_content_left.alignment = align2 style_content_right = XFStyle() style_content_right.font = fnt style_content_right.pattern = pattern3 style_content_right.borders = borders style_content_right.alignment = align3 style_content = XFStyle() style_content.alignment = align_content style_content.font = content_fnt wb = Workbook() ws = wb.add_sheet("Sheet 1") ws.row(0).height = 3500 ws.write(0, 0, "(CO1)", style_header) ws.col(0).width = 2000 ws.write(0, 1, "Month & Year (CO2)", style_header) ws.col(1).width = 4500 ws.write(0, 2, "Buyer's TIN (CO3)", style_header) ws.col(2).width = 4500 ws.write(0, 3, "Buyer's Name (CO4)", style_header) ws.col(3).width = 10000 ws.write(0, 4, "Interstate Branch/Consignment Transfer (CO5)", style_header) ws.col(4).width = 4500 ws.write(0, 5, "Export Out Of India (CO6)", style_header) ws.col(5).width = 4500 ws.write(0, 6, "High Sea Sales (CO7)", style_header) ws.col(6).width = 4500 ws.write(0, 7, "ISS - Goods Type (CO8)", style_header) ws.col(7).width = 4500 ws.write(0, 8, "ISS - Form Type (I) (CO9)", style_header) ws.col(8).width = 4500 ws.write(0, 9, "ISS - Rate Of Tax (C1O)", style_header) ws.col(9).width = 4500 ws.write(0, 10, "ISS - Sales Price (Excluding CST) (C11)", style_header) ws.col(10).width = 4500 ws.write(0, 11, "ISS - Central Sales Tax (C12)", style_header) ws.col(11).width = 4500 ws.write(0, 12, "ISS - Total (C13)", style_header) ws.col(12).width = 4500 ws.write(0, 13, "Local Sale - Type Of Sale (C14)", style_header) ws.col(13).width = 4500 ws.write(0, 14, "Local Sale - Rate Of Tax (C15)", style_header) ws.col(14).width = 4500 ws.write(0, 15, "Local Sale - Sale Price (Excluding VAT) (C16)", style_header) ws.col(15).width = 4500 ws.write(0, 16, "Local Sale - OutPut Tax (C17)", style_header) ws.col(16).width = 4500 ws.write(0, 17, "Local Sale - Total (Including VAT) (C18)", style_header) ws.col(17).width = 4500 ws.row(1).height = 400 ws.write(1, 0, "0", style_header1) ws.write(1, 1, "0", style_header1) ws.write(1, 2, "0", style_header1) ws.write(1, 3, "", style_header1) ws.write(1, 4, "0.00", style_header1) ws.write(1, 5, "0.00", style_header1) ws.write(1, 6, "0.00", style_header1) ws.write(1, 7, "", style_header1) ws.write(1, 8, "", style_header1) ws.write(1, 9, "0.00", style_header1) ws.write(1, 10, "0.00", style_header1) ws.write(1, 11, "0.00", style_header1) ws.write(1, 12, "0.00", style_header1) ws.write(1, 13, "", style_header1) ws.write(1, 14, "0.00", style_header1) ws.write(1, 15, "0.00", style_header1) ws.write(1, 16, "0.00", style_header1) ws.write(1, 17, "0.00", style_header1) inv_obj = self.pool.get('account.invoice') line_obj = self.pool.get('account.invoice.line') row = 2 count = 1 value = 0.0 sum1 = sum2 = sum3 = sum4 = sum5 = sum6 = 0.0 period = [] for each in this: if each.start_period_id.name > each.end_period_id.name: raise osv.except_osv( _('Warning !'), _("Start period must be less than end period !")) cr.execute("select id from account_period where name between '" + str(each.start_period_id.name) + "' and '" + str(each.end_period_id.name) + "'") temp = cr.fetchall() for val in temp: if val: period.append(val[0]) inv_ids = inv_obj.search(cr, uid, [('type', '=', 'out_invoice'), ('state', 'not in', ['draft', 'cancel']), ('period_id', 'in', period)]) line_ids = line_obj.search(cr, uid, [('invoice_id', 'in', inv_ids)]) for line in line_obj.browse(cr, uid, line_ids): ws.row(row).height = 400 ws.write(row, 0, count, style_content_right) ws.write( row, 1, line.invoice_id.period_id and line.invoice_id.period_id.name or False, style_content_right) ws.write(row, 2, line.partner_id.tin_no or '', style_content_right) ws.write(row, 3, line.partner_id.name, style_content_left) for tax in line.invoice_line_tax_id: value = tax.amount * 100 if line.invoice_id.address_invoice_id and line.invoice_id.address_invoice_id.city and line.invoice_id.address_invoice_id.city.lower( ) == 'new delhi' or line.invoice_id.address_invoice_id and line.invoice_id.address_invoice_id.city and line.invoice_id.address_invoice_id.city.lower( ) == 'delhi' or line.invoice_id.address_invoice_id and line.invoice_id.address_invoice_id.state_id and line.invoice_id.address_invoice_id.state_id.name.lower( ) == 'new delhi' or line.invoice_id.address_invoice_id and line.invoice_id.address_invoice_id.state_id and line.invoice_id.address_invoice_id.state_id.name.lower( ) == 'delhi': ws.write(row, 14, round(value, 2), style_content_right) ws.write(row, 15, round(line.price_subtotal, 2), style_content_right) sum1 += round(line.price_subtotal, 2) ws.write(row, 16, round(line.price_subtotal * tax.amount, 2), style_content_right) sum2 += round(line.price_subtotal * tax.amount, 2) ws.write( row, 17, round( line.price_subtotal + (line.price_subtotal * tax.amount), 2), style_content_right) sum3 += round( line.price_subtotal + (line.price_subtotal * tax.amount), 2) else: ws.write(row, 9, round(value, 2), style_content_right) ws.write(row, 10, round(line.price_subtotal, 2), style_content_right) sum4 += round(line.price_subtotal, 2) ws.write(row, 11, round(line.price_subtotal * tax.amount, 2), style_content_right) sum5 += round(line.price_subtotal * tax.amount, 2) ws.write( row, 12, round( line.price_subtotal + (line.price_subtotal * tax.amount), 2), style_content_right) sum6 += round( line.price_subtotal + (line.price_subtotal * tax.amount), 2) row += 1 count += 1 row += 2 ws.row(row).height = 400 ws.write(row, 0, "", style_header1) ws.write(row, 1, "", style_header1) ws.write(row, 2, "", style_header1) ws.write(row, 3, "", style_header1) ws.write(row, 4, "", style_header1) ws.write(row, 5, "", style_header1) ws.write(row, 6, "", style_header1) ws.write(row, 7, "", style_header1) ws.write(row, 8, "", style_header1) ws.write(row, 9, "", style_header1) ws.write(row, 10, sum4, style_header1) ws.write(row, 11, sum5, style_header1) ws.write(row, 12, sum6, style_header1) ws.write(row, 13, "", style_header1) ws.write(row, 14, "", style_header1) ws.write(row, 15, sum1, style_header1) ws.write(row, 16, sum2, style_header1) ws.write(row, 17, sum3, style_header1) f = cStringIO.StringIO() wb.save(f) out = base64.encodestring(f.getvalue()) return self.write(cr, uid, ids, { 'data': out, 'name': 'MARAnnexure2B.xls' }, context=context)
def ot_register_report(self, cr, uid, ids, context=None): fnt = Font() fnt.name = 'Ubuntu Medium' fnt.size = 16 fnt.style = 'Regular' #Define the font attributes for header content_fnt = Font() content_fnt.name = 'Ubuntu Medium' 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 fnt1 = Font() fnt1.name = 'Ubuntu Medium' fnt1.size = 10 fnt1.style = 'Regular' content_fnt1 = Font() content_fnt1.name = 'Ubuntu Medium' content_fnt1.style = 'Regular' align_content1 = Alignment() align_content1.horz = Alignment.HORZ_LEFT borders1 = Borders() borders1.left = 0x1 borders1.right = 0x1 borders1.top = 0x1 borders1.bottom = 0x1 #The text should be centrally aligned align1 = Alignment() align1.horz = Alignment.HORZ_CENTER align1.vert = Alignment.VERT_TOP #We set the backgroundcolour here pattern1 = Pattern() pattern1.pattern1 = Pattern.SOLID_PATTERN pattern1.pattern1_fore_colour = 0x1F #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 Content fnt3 = Font() fnt3.name = 'Arial' fnt3.size = '10' fnt3.style = 'Regular' content_fnt3 = Font() content_fnt3.name = 'Arial' content_fnt3.style = 'Regular' align_content3 = Alignment() align_content3.horz = Alignment.HORZ_LEFT borders3 = Borders() borders3.left = 0x0 borders3.right = 0x0 borders3.top = 0x0 borders3.bottom = 0x0 #The text should be centrally aligned align3 = Alignment() align3.horz = Alignment.HORZ_CENTER align3.vert = Alignment.VERT_TOP #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 wb = Workbook() ws = wb.add_sheet('OT Register Report') this = self.browse(cr, uid, ids[0], context=context) year = this.month.year_id.id month = this.month.month emp_id = this.employee_id.id company_id = this.company_id.id ws.row(0).height = 300 ws.row(1).height = 300 ws.row(2).height = 300 ws.col(20).width = 6000 ws.write_merge( 0, 0, 4, 10, 'COMPANY : ' + this.company_id.name + ' ' + this.company_id.street, style_header) ws.write_merge(1, 1, 4, 10, ('SALARY REPORT FOR THE MONTH OF : ', this.month.name), style_header) ws.write(2, 0, 'PCard', style_header) ws.col(0).width = 4000 ws.write(2, 1, 'Employee Name', style_header) ws.col(1).width = 8000 ws.write(2, 2, 'Department Name', style_header) ws.col(2).width = 5000 ws.write(2, 3, 'Designation Name', style_header) ws.col(3).width = 5000 ws.write(2, 4, 'Total Working Day', style_header) ws.col(4).width = 5000 ws.write(2, 5, 'CL + EL', style_header) ws.col(5).width = 3000 ws.write(2, 6, 'Compensatory Leave', style_header) ws.col(6).width = 5000 ws.write(2, 7, 'Sick Leave', style_header) ws.col(7).width = 4000 ws.write(2, 8, 'Absent', style_header) ws.col(8).width = 4000 ws.write(2, 9, 'Shift Hours Worked', style_header) ws.col(9).width = 5000 ws.write(2, 10, 'Overtime Hours', style_header) ws.col(10).width = 5000 ws.write(2, 11, 'Total Working Hours', style_header) ws.col(11).width = 5000 ws.write(2, 12, 'Avg Weekly Working Hour', style_header) ws.col(12).width = 6000 ws.write(2, 13, 'Avg Daily Working Hour', style_header) ws.col(13).width = 6000 ws.write(2, 14, 'Total Monthly Gross', style_header) ws.col(14).width = 5000 ws.write(2, 15, 'Gross Salary Paid', style_header) ws.col(15).width = 5000 ws.write(2, 16, 'OT Salary', style_header) ws.col(16).width = 5000 ws.write(2, 17, 'Total Gross Salary', style_header) ws.col(17).width = 5000 i = 3 total_days = 0.0 total_cl_el = 0.0 total_compensatory = 0.0 total_sick = 0.0 total_absent = 0.0 total_shift_hours = 0.0 total_ot_hours = 0.0 total_working_hours = 0.0 total_weekly_working_hour = 0.0 total_daily_working_hour = 0.0 total_monthly_gross = 0.0 total_gross_salary_paid = 0.0 total_ot_salary = 0.0 total_gross_salary = 0.0 emp_obj = self.pool.get('hr.employee') if this.employee_id and this.company_id: list_ids = emp_obj.search(cr, uid, [('id', '=', emp_id), ('company_id', '=', company_id), ('type', '=', 'Employee'), ('active', '=', True)]) elif this.company_id: list_ids = emp_obj.search(cr, uid, [('company_id', '=', company_id), ('type', '=', 'Employee'), ('active', '=', True)]) list_ids1 = emp_obj.search(cr, uid, [('company_id', '=', company_id), ('type', '=', 'Employee'), ('active', '=', False)]) list_ids = list_ids + list_ids1 elif this.employee_id: list_ids = emp_obj.search(cr, uid, [('id', '=', emp_id), ('type', '=', 'Employee'), ('active', '=', True)]) if len(list_ids) == 1: query ="select spl.sinid,spl.employee_name,spl.department_name,spl.job_name,(spl.work_day+spl.factory_work),(spl.casual_leave+spl.earned_leave),spl.compensatory_leave,spl.sick_leave,(spl.month_days-(spl.work_day+spl.factory_work+spl.casual_leave+spl.earned_leave+spl.compensatory_leave+spl.sick_leave+spl.holiday_leave+spl.week_leave)),"\ "((spl.work_day+spl.factory_work) * 8),(spl.over_time + spl.sun_over_time),(((spl.work_day+spl.factory_work) * 8)+(spl.over_time + spl.sun_over_time)),"\ "spl.month_days,"\ "round(((((spl.work_day+spl.factory_work) * 8)+(spl.over_time + spl.sun_over_time))/(spl.work_day+spl.factory_work)),2) ,(spl.basic + spl.other_salary),(spl.days_amount+spl.other_salary_amount),spl.total_overtime_amount,"\ "(spl.days_amount+spl.other_salary_amount+spl.total_overtime_amount) from salary_payment_line as spl where spl.employee_id = '"+str(list_ids[0])+"' and spl.month='"+str(month)+"' and spl.year_id='"+str(year)+"' order by spl.sinid" cr.execute(query) temp = cr.fetchall() else: query ="select spl.sinid,spl.employee_name,spl.department_name,spl.job_name,(spl.work_day+spl.factory_work),(spl.casual_leave+spl.earned_leave),spl.compensatory_leave,spl.sick_leave,(spl.month_days-(spl.work_day+spl.factory_work+spl.casual_leave+spl.earned_leave+spl.compensatory_leave+spl.sick_leave+spl.holiday_leave+spl.week_leave)),"\ "((spl.work_day+spl.factory_work) * 8),(spl.over_time + spl.sun_over_time),(((spl.work_day+spl.factory_work) * 8)+(spl.over_time + spl.sun_over_time)),"\ "spl.month_days,"\ "round(((((spl.work_day+spl.factory_work) * 8)+(spl.over_time + spl.sun_over_time))/(spl.work_day+spl.factory_work)),2) ,(spl.basic + spl.other_salary),(spl.days_amount+spl.other_salary_amount),spl.total_overtime_amount,"\ "(spl.days_amount+spl.other_salary_amount+spl.total_overtime_amount) from salary_payment_line as spl where spl.employee_id in "+str(tuple(list_ids))+" and spl.month='"+str(month)+"' and spl.year_id='"+str(year)+"' order by spl.sinid" cr.execute(query) temp = cr.fetchall() if not temp: raise osv.except_osv(_('Warning !'), _("Record Not Found !!!")) for val in temp: if val[4] >= 7: working_hour = round((val[11] / (val[12] - val[8])) * 7, 2) else: working_hour = 0.00 total_days += val[4] total_cl_el += val[5] total_compensatory += val[6] total_sick += val[7] total_absent += val[8] total_shift_hours += val[9] total_ot_hours += val[10] total_working_hours += val[11] total_daily_working_hour += val[13] total_monthly_gross += val[14] total_gross_salary_paid += val[15] total_ot_salary += val[16] total_gross_salary += val[17] total_weekly_working_hour += working_hour ws.write(i, 0, val[0], style_header3) ws.write(i, 1, val[1], style_header3) ws.write(i, 2, val[2], style_header3) ws.write(i, 3, val[3], style_header3) ws.write(i, 4, val[4], style_header3) ws.write(i, 5, val[5], style_header3) ws.write(i, 6, val[6], style_header3) ws.write(i, 7, val[7], style_header3) ws.write(i, 8, val[8], style_header3) ws.write(i, 9, val[9], style_header3) ws.write(i, 10, val[10], style_header3) ws.write(i, 11, val[11], style_header3) ws.write(i, 12, working_hour, style_header3) ws.write(i, 13, val[13], style_header3) ws.write(i, 14, val[14], style_header3) ws.write(i, 15, val[15], style_header3) ws.write(i, 16, val[16], style_header3) ws.write(i, 17, val[17], style_header3) i = i + 1 ws.write(i, 0, '', style_header3) ws.write(i, 1, '', style_header3) ws.write(i, 2, '', style_header3) ws.write(i, 3, 'TOTAL', style_header1) ws.write(i, 4, total_days, style_header1) ws.write(i, 5, total_cl_el, style_header1) ws.write(i, 6, total_compensatory, style_header1) ws.write(i, 7, total_sick, style_header1) ws.write(i, 8, total_absent, style_header1) ws.write(i, 9, total_shift_hours, style_header1) ws.write(i, 10, total_ot_hours, style_header1) ws.write(i, 11, total_working_hours, style_header1) ws.write(i, 12, total_weekly_working_hour, style_header1) ws.write(i, 13, total_daily_working_hour, style_header1) ws.write(i, 14, total_monthly_gross, style_header1) ws.write(i, 15, total_gross_salary_paid, style_header1) ws.write(i, 16, total_ot_salary, style_header1) ws.write(i, 17, total_gross_salary, style_header1) f = cStringIO.StringIO() wb.save(f) out = base64.encodestring(f.getvalue()) ot_report = self.write(cr, uid, ids, { 'export_data': out, 'filename': 'OT Register Report.xls' }, context=context) return ot_report
add_station(station) if len(stations) == 0: print "No stations to process. Exiting..." sys.exit(0) wb = Workbook() ws = wb.add_sheet('Certificate Data', cell_overwrite_ok=True) ws.write(PERIOD_START - 1, 0, "Period") for i in range(0, len(periods)): ws.write(PERIOD_START + i, 0, periods[i]) al = Alignment() al.horz = Alignment.HORZ_CENTER al.vert = Alignment.VERT_CENTER title_style = XFStyle() title_style.alignment = al station_records = {} print "\nTotal of %d stations to process\n" % len(stations) for i in range(0, len(stations)): s = stations[i] col = 1 + 5 * i print " ", s # add headers # ws.write(PERIOD_START - 1, col, "Installed Capacity")
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 pf_upload_report(self, cr, uid, ids, context=None): # req = urllib2.Request('http://www.voidspace.org.uk') # response = urllib2.urlopen(req) # the_page = response.read() # print " html===========================",the_page f_name = '' d_name = '' wb = Workbook() ws = wb.add_sheet('PF UPLOAD') fnt1 = Font() fnt1.name = 'Arial' fnt1.height = 300 fnt1.bold = True align_content1 = Alignment() align_content1.horz = Alignment.HORZ_CENTER borders1 = Borders() borders1.left = 0x02 borders1.right = 0x02 borders1.top = 0x02 borders1.bottom = 0x02 align1 = Alignment() align1.horz = Alignment.HORZ_CENTER align1.vert = Alignment.VERT_CENTER pattern1 = Pattern() pattern1.pattern1 = Pattern.SOLID_PATTERN pattern1.pattern1_fore_colour = 0x17 style_header1 = XFStyle() style_header1.font = fnt1 style_header1.pattern = pattern1 style_header1.borders = borders1 style_header1.alignment = align1 fnt2 = Font() fnt2.name = 'Arial' fnt2.height = 250 fnt2.bold = False align_content2 = Alignment() align_content2.horz = Alignment.HORZ_CENTER borders2 = Borders() borders2.left = 0x02 borders2.right = 0x02 borders2.top = 0x02 borders2.bottom = 0x02 align2 = Alignment() align2.horz = Alignment.HORZ_CENTER align2.vert = Alignment.VERT_CENTER pattern2 = Pattern() pattern2.pattern2 = Pattern.SOLID_PATTERN pattern2.pattern2_fore_colour = 0x09 style_header2 = XFStyle() style_header2.font = fnt2 style_header2.pattern = pattern2 style_header2.borders = borders2 style_header2.alignment = align2 fnt3 = Font() fnt3.name = 'Arial' fnt3.height = 275 fnt3.bold = False align_content3 = Alignment() align_content3.horz = Alignment.HORZ_CENTER borders3 = Borders() borders3.left = 0x02 borders3.right = 0x02 borders3.top = 0x02 borders3.bottom = 0x02 align3 = Alignment() align3.horz = Alignment.HORZ_CENTER align3.vert = Alignment.VERT_CENTER pattern3 = Pattern() pattern3.pattern3 = Pattern.SOLID_PATTERN pattern3.pattern3_fore_colour = 0x09 style_header3 = XFStyle() style_header3.font = fnt3 style_header3.pattern = pattern3 style_header3.borders = borders3 style_header3.alignment = align3 fnt = Font() fnt.name = 'Arial' fnt.height = 275 content_fnt = Font() content_fnt.name = 'Arial' content_fnt.height = 150 align_content = Alignment() align_content.horz = Alignment.HORZ_CENTER borders = Borders() borders.left = 0x02 borders.right = 0x02 borders.top = 0x02 borders.bottom = 0x02 align = Alignment() align.horz = Alignment.HORZ_CENTER align.vert = Alignment.VERT_CENTER pattern = Pattern() pattern.pattern = Pattern.SOLID_PATTERN pattern.pattern_fore_colour = 0x16 style_header = XFStyle() style_header.font = fnt style_header.pattern = pattern style_header.borders = borders style_header.alignment = align fnt5 = Font() fnt5.name = 'Arial' fnt5.height = 275 content_fnt5 = Font() content_fnt5.name = 'Arial' content_fnt5.height = 150 align_content5 = Alignment() # align_content5.horz= Alignment.HORZ_JUSTIFIED borders5 = Borders() borders5.left = 0x02 borders5.right = 0x02 borders5.top = 0x02 borders5.bottom = 0x02 align5 = Alignment() # align5.horz = Alignment.HORZ_JUSTIFIED align5.vert = Alignment.VERT_JUSTIFIED pattern5 = Pattern() pattern5.pattern = Pattern.SOLID_PATTERN pattern5.pattern_fore_colour = 0x16 style_header5 = XFStyle() style_header5.font = fnt5 style_header5.pattern = pattern5 style_header5.borders = borders5 style_header5.alignment = align5 ws.row(0).height = 1500 ws.col(0).width = 6500 ws.col(1).width = 5000 ws.col(2).width = 10000 ws.col(3).width = 4000 ws.col(4).width = 4000 ws.col(5).width = 4500 ws.col(6).width = 4000 ws.col(7).width = 5500 ws.col(8).width = 4000 ws.col(9).width = 4500 ws.col(10).width = 4000 ws.col(11).width = 4000 # ws.col(12).width = 6500 # ws.col(13).width = 6500 # ws.col(14).width = 6500 # ws.col(15).width = 6500 # ws.col(16).width = 4500 # ws.col(17).width = 7500 # ws.col(18).width = 3000 # ws.col(19).width = 4000 # ws.col(20).width = 3000 # ws.col(21).width = 6500 # ws.col(22).width = 6500 # ws.col(23).width = 7500 # ws.col(24).width = 7500 # ws.col(25).width = 2500 # ws.col(26).width = 5000 # ws.col(27).width = 4000 ws.write(0, 0, 'Employee Pay Code', style_header) ws.write(0, 1, 'UAN Number', style_header) ws.write(0, 2, 'Member Name', style_header) ws.write(0, 3, 'Gross Wages', style_header) ws.write(0, 4, 'EPF Wages', style_header) ws.write(0, 5, 'EPS Wages', style_header) ws.write(0, 6, 'EDLI Wages', style_header) ws.write(0, 7, 'EE Share', style_header) ws.write(0, 8, 'EPS Contribution', style_header) ws.write(0, 9, 'ER Share', style_header) ws.write(0, 10, 'NCP Days', style_header) ws.write(0, 11, 'Refund', style_header) # ws.write(0,1,'Member ID',style_header) # ws.write(0,6,'EPF Contribution (EE Share) being remitted',style_header5) # ws.write(0,8,'EPS Contribution being remitted',style_header5) # ws.write(0,10,'Diff EPF and EPS Contribution (ER Share) being remitted',style_header5) # ws.write(0,15,'Arrear EPF Wages',style_header) # ws.write(0,16,'Arrear EPF EE Share',style_header) # ws.write(0,17,'Arrear EPF ER Share',style_header) # ws.write(0,18,'Arrear EPS',style_header) # ws.write(0,19,'Father / Husband Name',style_header) # ws.write(0,20,'Relationship with the Member',style_header5) # ws.write(0,21,'Date of Birth',style_header) # ws.write(0,22,'Gender',style_header) # ws.write(0,23,'Date of Joining EPF',style_header) # ws.write(0,24,'Date of Joining EPS',style_header) # ws.write(0,25,'Date of Exit From EPF',style_header) # ws.write(0,26,'Date of Exit From EPS',style_header) # ws.write(0,27,'Reason for Leaving',style_header5) this = self.browse(cr, uid, ids[0], context=context) month = this.month.month company_id = this.company_id.id year = this.month.year_id.id year_name = this.month.year_id.name emp_obj = self.pool.get('hr.employee') # pf_no=' ' emp_name = ' ' epf_cont = 0 eps_cont = 0 calc_eps_epf = 0 diff_eps_epf = 0 full_date_month = [] total_epf_wages = 0 total_epf_cont = 0 total_eps_cont = 0 total_diff_calc = 0 total_diff_diff = 0 total_gross = 0 holiday_list = [] inact_list = [] inact_list1 = [] hr_list = [] i = 1 val_zero = 0 # epf_date_start='' if len(str(month)) == 1: start_date = str(year_name) + '-' + '0' + str(month) + '-' + '01' month_val = '0' + str(month) else: start_date = str(year_name) + '-' + str(month) + '-' + '01' month_val = str(month) if int(this.month.month) in [1, 3, 5, 7, 8, 10, 12]: month_tup = 31 if int(this.month.month) in [4, 6, 9, 11]: month_tup = 30 if int(this.month.month) in [2]: if int(this.month.year_id.name) % 4 == 0: month_tup = 29 else: month_tup = 28 month_val = int(month_val) # month_tup = calendar.monthrange(year,month_val) end_date = str(year_name) + '-' + str(month_val) + '-' + str(month_tup) for month_date in rrule.rrule( rrule.DAILY, dtstart=datetime.strptime(start_date, '%Y-%m-%d'), until=datetime.strptime(end_date, '%Y-%m-%d')): month_date = datetime.strftime(month_date, "%Y-%m-%d") full_date_month.append(month_date) if this.company_id: list_ids = emp_obj.search(cr, uid, [('active', '=', True), ('company_id', '=', company_id), ('epf_tick', '=', True), ('type', '=', 'Employee')]) inact_ids = emp_obj.search(cr, uid, [('active', '=', False), ('company_id', '=', company_id), ('epf_tick', '=', True), ('epf_end_date', '>=', start_date), ('epf_end_date', '<=', end_date), ('type', '=', 'Employee')]) if len(list_ids) == 0: raise osv.except_osv(('Warning !'), ("Record Not Found !!!")) if inact_ids: for val in inact_ids: inact_emp_browse = emp_obj.browse(cr, uid, val) if inact_emp_browse: inact_id = val inact_emp_name = inact_emp_browse.name inact_emp_sinid = inact_emp_browse.sinid inact_days_amount = 0 tup = (inact_id, inact_emp_name, inact_days_amount, inact_emp_sinid) inact_list.append(tup) inact_list1.append(val) if len(list_ids) == 1: query = "select spl.employee_id,spl.employee_name,spl.gross,spl.sinid,spl.epf,spl.epf1,spl.epf2,spl.days_amount,spl.other_salary_amount from salary_payment_line as spl where spl.employee_id = '" + str( list_ids[0]) + "' and spl.month='" + str( month) + "' and spl.year_id='" + str( year) + "' and spl.epf <> 0.00 order by spl.sinid " cr.execute(query) temp = cr.fetchall() query1 = "select spl.employee_id from salary_payment_line as spl where spl.employee_id = '" + str( list_ids[0]) + "' and spl.month='" + str( month) + "' and spl.year_id='" + str( year) + "' and spl.epf <> 0.00 order by spl.sinid " cr.execute(query1) temp1 = cr.fetchall() else: query = "select spl.employee_id,spl.employee_name,spl.gross,spl.sinid,spl.epf,spl.epf1,spl.epf2,spl.days_amount,spl.other_salary_amount from salary_payment_line as spl where spl.employee_id in " + str( tuple(list_ids)) + " and spl.month='" + str( month) + "' and spl.year_id='" + str( year) + "' and spl.epf <> 0.00 order by spl.sinid " cr.execute(query) temp = cr.fetchall() query1 = "select spl.employee_id from salary_payment_line as spl where spl.employee_id in " + str( tuple(list_ids)) + " and spl.month='" + str( month) + "' and spl.year_id='" + str( year) + "' and spl.epf <> 0.00 order by spl.sinid " cr.execute(query1) temp1 = cr.fetchall() if not temp: raise osv.except_osv(_('Warning !'), _("Record Not Found !!!")) temp_add_value1 = temp1 + inact_list1 emp_list = emp_obj.search(cr, uid, [('id', 'not in', temp_add_value1), ('active', '=', True), ('company_id', '=', company_id), ('epf_tick', '=', True), ('doj', '<=', end_date), ('type', '=', 'Employee')]) if emp_list: for valll in emp_list: emp_list_ids = emp_obj.browse(cr, uid, valll) hr_id = valll hr_name = emp_list_ids.name hr_sinid = emp_list_ids.sinid hr_days_amount = 0 tup = (hr_id, hr_name, hr_days_amount, hr_sinid) hr_list.append(tup) if temp: temp_add_value = temp + inact_list + hr_list temp_add_value = sorted(temp_add_value, key=lambda x: x[3]) for val in temp_add_value: epf_cont = 0 eps_cont = 0 calc_eps_epf = 0 epf_gross_wages = 0 ws.row(i).height = 500 emp_browse = emp_obj.browse(cr, uid, val[0]) # emp_pf=emp_browse.pf_number uan_number = emp_browse.uan if val[2]: gross = val[7] + val[8] else: gross = 0 # if '/' in emp_pf : # emp_pf=emp_pf.upper().split('/') # emp_pf_len=len(emp_pf)-1 # if emp_pf : # pf_no=emp_pf[emp_pf_len] if len(val) > 5: epf_cont = val[4] eps_cont = val[5] calc_eps_epf = val[6] epf_gross_wages = round(val[2], 0) diff_eps_epf = (epf_cont - eps_cont) date_of_join_epf = emp_browse.epf_start_date date_of_exit_epf = emp_browse.epf_end_date # f_name='' # relation_member = '' # date_of_birth='' # gender= '' # epf_date_start='' # leave_reason=' ' # epf_end_date=' ' # if date_of_join_epf : # if date_of_join_epf in full_date_month : # epf_date_start=datetime.strptime(date_of_join_epf,'%Y-%m-%d') # epf_date_start=epf_date_start.strftime('%d-%m-%Y') # query1 = cr.execute("select name from family where employee_id='"+str(val[0])+"' and relation='Father' ") # temp1 = cr.fetchall() # if temp1 : # f_name = temp1[0][0] # relation_member='F' # date_of_birth=emp_browse.birthday # date_of_birth=datetime.strptime(date_of_birth,'%Y-%m-%d') # date_of_birth=date_of_birth.strftime('%d-%m-%Y') # gender=emp_browse.gender # gender=gender.upper()[0] # if date_of_exit_epf : # leave_reason=emp_browse.leaving_reason[0] # epf_end_date=datetime.strptime(date_of_exit_epf,'%Y-%m-%d') # epf_end_date=epf_end_date.strftime('%d-%m-%Y') # if date_of_join_epf and not date_of_exit_epf : # leave_reason='&' # epf_end_date='&' # if not date_of_join_epf and date_of_exit_epf : # epf_date_start='&' total_epf_wages += val[2] total_epf_cont += epf_cont total_eps_cont += eps_cont total_diff_calc += calc_eps_epf total_diff_diff += diff_eps_epf total_gross += gross ws.write(i, 0, (val[3]), style_header2) ws.write(i, 1, uan_number, style_header2) # ws.write(i,1,pf_no,style_header2) ws.write(i, 2, (val[1]), style_header2) ws.write(i, 3, gross, style_header2) ws.write(i, 4, epf_gross_wages, style_header2) ws.write(i, 5, epf_gross_wages, style_header2) ws.write(i, 6, epf_gross_wages, style_header2) ws.write(i, 7, epf_cont, style_header2) # ws.write(i,6,epf_cont,style_header2) ws.write(i, 8, eps_cont, style_header2) # ws.write(i,8,eps_cont,style_header2) ws.write(i, 9, calc_eps_epf, style_header2) # ws.write(i,10,diff_eps_epf,style_header2) ws.write(i, 10, val_zero, style_header2) ws.write(i, 11, val_zero, style_header2) # ws.write(i,15,val_zero,style_header2) # ws.write(i,16,val_zero,style_header2) # ws.write(i,17,val_zero,style_header2) # ws.write(i,18,val_zero,style_header2) # ws.write(i,19,f_name and f_name or '&',style_header2) # ws.write(i,20,relation_member and relation_member or '&',style_header2) # ws.write(i,21,date_of_birth and date_of_birth or '&',style_header2) # ws.write(i,22,gender and gender or '&',style_header2) # ws.write(i,23,epf_date_start and epf_date_start or '&',style_header2) # ws.write(i,24,epf_date_start and epf_date_start or '&',style_header2) # ws.write(i,25,epf_end_date,style_header2) # ws.write(i,26,epf_end_date,style_header2) # ws.write(i,27,leave_reason,style_header2) i = i + 1 ws.row(i + 1).height = 500 ws.write(i + 1, 2, 'TOTAL', style_header1) ws.write(i + 1, 3, total_gross, style_header1) ws.write(i + 1, 4, total_epf_wages, style_header1) ws.write(i + 1, 5, total_epf_wages, style_header1) ws.write(i + 1, 6, total_epf_wages, style_header1) ws.write(i + 1, 7, total_epf_cont, style_header1) ws.write(i + 1, 8, total_eps_cont, style_header1) ws.write(i + 1, 9, total_diff_calc, style_header1) f = cStringIO.StringIO() wb.save(f) out = base64.encodestring(f.getvalue()) pf_upload_report = self.write(cr, uid, ids, { 'export_data': out, 'filename': 'employee_pf_upload.xls' }, context=context) return pf_upload_report
continue stations.append(station) if len(stations) == 0: print "No stations to process. Exiting..." sys.exit(0) wb = Workbook() ws = wb.add_sheet('Certificate Data', cell_overwrite_ok=True) ws.write(PERIOD_START - 1,0,"Period") for i in range(0, len(periods)): ws.write(PERIOD_START + i, 0, periods[i]) al = Alignment() al.horz = Alignment.HORZ_CENTER al.vert = Alignment.VERT_CENTER title_style = XFStyle() title_style.alignment = al print "\nTotal of %d stations to process\n" % len(stations) for i in range(0, len(stations)): s = stations[i] col = 1 + 5 * i print " ", s # add headers ws.write(PERIOD_START - 1, col, "Installed Capacity") ws.write(PERIOD_START - 1, col + 1, "RO Certificates") ws.write(PERIOD_START - 1, col + 2, "RO Factor") ws.write(PERIOD_START - 1, col + 3, "REGO Certificates")
def pf_upload_report(self, cr, uid, ids, context=None): f_name = '' d_name = '' wb = Workbook() ws = wb.add_sheet('CONTRACTOR PF UPLOAD') fnt1 = Font() fnt1.name = 'Arial' fnt1.height = 300 fnt1.bold = True align_content1 = Alignment() align_content1.horz = Alignment.HORZ_CENTER borders1 = Borders() borders1.left = 0x02 borders1.right = 0x02 borders1.top = 0x02 borders1.bottom = 0x02 align1 = Alignment() align1.horz = Alignment.HORZ_CENTER align1.vert = Alignment.VERT_CENTER pattern1 = Pattern() pattern1.pattern1 = Pattern.SOLID_PATTERN pattern1.pattern1_fore_colour = 0x17 style_header1 = XFStyle() style_header1.font = fnt1 style_header1.pattern = pattern1 style_header1.borders = borders1 style_header1.alignment = align1 fnt2 = Font() fnt2.name = 'Arial' fnt2.height = 250 fnt2.bold = False align_content2 = Alignment() align_content2.horz = Alignment.HORZ_CENTER borders2 = Borders() borders2.left = 0x02 borders2.right = 0x02 borders2.top = 0x02 borders2.bottom = 0x02 align2 = Alignment() align2.horz = Alignment.HORZ_CENTER align2.vert = Alignment.VERT_CENTER pattern2 = Pattern() pattern2.pattern2 = Pattern.SOLID_PATTERN pattern2.pattern2_fore_colour = 0x09 style_header2 = XFStyle() style_header2.font = fnt2 style_header2.pattern = pattern2 style_header2.borders = borders2 style_header2.alignment = align2 fnt3 = Font() fnt3.name = 'Arial' fnt3.height = 275 fnt3.bold = False align_content3 = Alignment() align_content3.horz = Alignment.HORZ_CENTER borders3 = Borders() borders3.left = 0x02 borders3.right = 0x02 borders3.top = 0x02 borders3.bottom = 0x02 align3 = Alignment() align3.horz = Alignment.HORZ_CENTER align3.vert = Alignment.VERT_CENTER pattern3 = Pattern() pattern3.pattern3 = Pattern.SOLID_PATTERN pattern3.pattern3_fore_colour = 0x09 style_header3 = XFStyle() style_header3.font = fnt3 style_header3.pattern = pattern3 style_header3.borders = borders3 style_header3.alignment = align3 fnt = Font() fnt.name = 'Arial' fnt.height = 275 content_fnt = Font() content_fnt.name = 'Arial' content_fnt.height = 150 align_content = Alignment() align_content.horz = Alignment.HORZ_CENTER borders = Borders() borders.left = 0x02 borders.right = 0x02 borders.top = 0x02 borders.bottom = 0x02 align = Alignment() align.horz = Alignment.HORZ_CENTER align.vert = Alignment.VERT_CENTER pattern = Pattern() pattern.pattern = Pattern.SOLID_PATTERN pattern.pattern_fore_colour = 0x16 style_header = XFStyle() style_header.font = fnt style_header.pattern = pattern style_header.borders = borders style_header.alignment = align fnt5 = Font() fnt5.name = 'Arial' fnt5.height = 275 content_fnt5 = Font() content_fnt5.name = 'Arial' content_fnt5.height = 150 align_content5 = Alignment() # align_content5.horz= Alignment.HORZ_JUSTIFIED borders5 = Borders() borders5.left = 0x02 borders5.right = 0x02 borders5.top = 0x02 borders5.bottom = 0x02 align5 = Alignment() # align5.horz = Alignment.HORZ_JUSTIFIED align5.vert = Alignment.VERT_JUSTIFIED pattern5 = Pattern() pattern5.pattern = Pattern.SOLID_PATTERN pattern5.pattern_fore_colour = 0x16 style_header5 = XFStyle() style_header5.font = fnt5 style_header5.pattern = pattern5 style_header5.borders = borders5 style_header5.alignment = align5 ws.row(0).height = 1500 ws.col(0).width = 6500 ws.col(1).width = 5000 ws.col(2).width = 10000 ws.col(3).width = 4000 ws.col(4).width = 4000 ws.col(5).width = 4500 ws.col(6).width = 4000 ws.col(7).width = 5500 ws.col(8).width = 4000 ws.col(9).width = 4500 ws.col(10).width = 4000 ws.col(11).width = 4000 ws.write(0, 0, 'Employee Pay Code', style_header) ws.write(0, 1, 'UAN Number', style_header) ws.write(0, 2, 'Member Name', style_header) ws.write(0, 3, 'Gross Wages', style_header) ws.write(0, 4, 'EPF Wages', style_header) ws.write(0, 5, 'EPS Wages', style_header) ws.write(0, 6, 'EDLI Wages', style_header) ws.write(0, 7, 'EE Share', style_header) ws.write(0, 8, 'EPS Contribution', style_header) ws.write(0, 9, 'ER Share', style_header) ws.write(0, 10, 'NCP Days', style_header) ws.write(0, 11, 'Refund', style_header) this = self.browse(cr, uid, ids[0], context=context) month = this.month.month company_id = this.company_id.id year = this.month.year_id.id year_name = this.month.year_id.name partner_id = this.partner_id.id emp_obj = self.pool.get('hr.employee') # pf_no=' ' emp_name = ' ' epf_cont = 0 eps_cont = 0 calc_eps_epf = 0 diff_eps_epf = 0 full_date_month = [] total_epf_wages = 0 total_epf_cont = 0 total_eps_cont = 0 total_diff_calc = 0 total_diff_diff = 0 total_gross = 0 holiday_list = [] inact_list = [] inact_list1 = [] hr_list = [] i = 1 val_zero = 0 # epf_date_start='' if len(str(month)) == 1: start_date = str(year_name) + '-' + '0' + str(month) + '-' + '01' month_val = '0' + str(month) else: start_date = str(year_name) + '-' + str(month) + '-' + '01' month_val = str(month) if int(this.month.month) in [1, 3, 5, 7, 8, 10, 12]: month_tup = 31 if int(this.month.month) in [4, 6, 9, 11]: month_tup = 30 if int(this.month.month) in [2]: if int(this.month.year_id.name) % 4 == 0: month_tup = 29 else: month_tup = 28 month_val = int(month_val) # month_tup = calendar.monthrange(year,month_val) end_date = str(year_name) + '-' + str(month_val) + '-' + str(month_tup) # month_val=int(month_val) # month_tup = calendar.monthrange(year,month_val) # end_date = str(year_name) + '-' + str(month_val) + '-' + str(month_tup[1]) for month_date in rrule.rrule( rrule.DAILY, dtstart=datetime.strptime(start_date, '%Y-%m-%d'), until=datetime.strptime(end_date, '%Y-%m-%d')): month_date = datetime.strftime(month_date, "%Y-%m-%d") full_date_month.append(month_date) if this.company_id: list_ids = emp_obj.search(cr, uid, [('active', '=', True), ('company_id', '=', company_id), ('epf_tick', '=', True), ('type', '=', 'Contractor')]) inact_ids = emp_obj.search(cr, uid, [('active', '=', False), ('company_id', '=', company_id), ('epf_tick', '=', True), ('epf_end_date', '>=', start_date), ('epf_end_date', '<=', end_date), ('type', '=', 'Contractor')]) elif this.partner_id: list_ids = emp_obj.search(cr, uid, [('active', '=', True), ('partner_id', '=', partner_id), ('epf_tick', '=', True), ('type', '=', 'Contractor')]) inact_ids = emp_obj.search(cr, uid, [('active', '=', False), ('partner_id', '=', partner_id), ('epf_tick', '=', True), ('epf_end_date', '>=', start_date), ('epf_end_date', '<=', end_date), ('type', '=', 'Contractor')]) if len(list_ids) == 0: raise osv.except_osv(('Warning !'), ("Record Not Found !!!")) if inact_ids: for val in inact_ids: inact_emp_browse = emp_obj.browse(cr, uid, val) if inact_emp_browse: inact_id = val inact_emp_name = inact_emp_browse.name inact_emp_sinid = inact_emp_browse.sinid inact_days_amount = 0 tup = (inact_id, inact_emp_name, inact_days_amount, inact_emp_sinid) inact_list.append(tup) inact_list1.append(val) if len(list_ids) == 1: query = "select spl.employee_id,spl.employee_name,spl.gross,spl.sinid,spl.epf,spl.epf1,spl.epf2,spl.days_amount,spl.other_salary_amount from salary_payment_line as spl where spl.employee_id = '" + str( list_ids[0]) + "' and spl.month='" + str( month) + "' and spl.year_id='" + str( year) + "' and spl.epf <> 0.00 order by spl.sinid " cr.execute(query) temp = cr.fetchall() query1 = "select spl.employee_id from salary_payment_line as spl where spl.employee_id = '" + str( list_ids[0]) + "' and spl.month='" + str( month) + "' and spl.year_id='" + str( year) + "' and spl.epf <> 0.00 order by spl.sinid " cr.execute(query1) temp1 = cr.fetchall() else: query = "select spl.employee_id,spl.employee_name,spl.gross,spl.sinid,spl.epf,spl.epf1,spl.epf2,spl.days_amount,spl.other_salary_amount from salary_payment_line as spl where spl.employee_id in " + str( tuple(list_ids)) + " and spl.month='" + str( month) + "' and spl.year_id='" + str( year) + "' and spl.epf <> 0.00 order by spl.sinid " cr.execute(query) temp = cr.fetchall() query1 = "select spl.employee_id from salary_payment_line as spl where spl.employee_id in " + str( tuple(list_ids)) + " and spl.month='" + str( month) + "' and spl.year_id='" + str( year) + "' and spl.epf <> 0.00 order by spl.sinid " cr.execute(query1) temp1 = cr.fetchall() if not temp: raise osv.except_osv(_('Warning !'), _("Record Not Found !!!")) temp_add_value1 = temp1 + inact_list1 emp_list = emp_obj.search(cr, uid, [('id', 'not in', temp_add_value1), ('active', '=', True), ('company_id', '=', company_id), ('epf_tick', '=', True), ('doj', '<=', end_date), ('type', '=', 'Contractor')]) if emp_list: for valll in emp_list: emp_list_ids = emp_obj.browse(cr, uid, valll) hr_id = valll hr_name = emp_list_ids.name hr_sinid = emp_list_ids.sinid hr_days_amount = 0 tup = (hr_id, hr_name, hr_days_amount, hr_sinid) hr_list.append(tup) if temp: temp_add_value = temp + inact_list + hr_list temp_add_value = sorted(temp_add_value, key=lambda x: x[3]) for val in temp_add_value: epf_cont = 0 eps_cont = 0 calc_eps_epf = 0 epf_gross_wages = 0 ws.row(i).height = 500 emp_browse = emp_obj.browse(cr, uid, val[0]) uan_number = emp_browse.uan if val[2]: gross = val[7] + val[8] else: gross = 0 if len(val) > 5: epf_cont = val[4] eps_cont = val[5] calc_eps_epf = val[6] epf_gross_wages = round(val[2], 0) diff_eps_epf = (epf_cont - eps_cont) date_of_join_epf = emp_browse.epf_start_date date_of_exit_epf = emp_browse.epf_end_date total_epf_wages += val[2] total_epf_cont += epf_cont total_eps_cont += eps_cont total_diff_calc += calc_eps_epf total_diff_diff += diff_eps_epf total_gross += gross ws.write(i, 0, (val[3]), style_header2) ws.write(i, 1, uan_number, style_header2) ws.write(i, 2, (val[1]), style_header2) ws.write(i, 3, gross, style_header2) ws.write(i, 4, epf_gross_wages, style_header2) ws.write(i, 5, epf_gross_wages, style_header2) ws.write(i, 6, epf_gross_wages, style_header2) ws.write(i, 7, epf_cont, style_header2) ws.write(i, 8, eps_cont, style_header2) ws.write(i, 9, calc_eps_epf, style_header2) ws.write(i, 10, val_zero, style_header2) ws.write(i, 11, val_zero, style_header2) i = i + 1 ws.row(i + 1).height = 500 ws.write(i + 1, 2, 'TOTAL', style_header1) ws.write(i + 1, 3, total_gross, style_header1) ws.write(i + 1, 4, total_epf_wages, style_header1) ws.write(i + 1, 5, total_epf_wages, style_header1) ws.write(i + 1, 6, total_epf_wages, style_header1) ws.write(i + 1, 7, total_epf_cont, style_header1) ws.write(i + 1, 8, total_eps_cont, style_header1) ws.write(i + 1, 9, total_diff_calc, style_header1) f = cStringIO.StringIO() wb.save(f) out = base64.encodestring(f.getvalue()) pf_upload_report = self.write( cr, uid, ids, { 'export_data': out, 'filename': 'contractor_pf_upload.xls' }, context=context) return pf_upload_report
def styles(self): """ Style definitions for pivot tables (lazy property) @returns: dict of named XFStyle instances """ styles = self._styles if styles is None: from xlwt import Alignment, XFStyle # Alignments center = Alignment() center.horz = Alignment.HORZ_CENTER center.vert = Alignment.VERT_CENTER center.wrap = 1 centerleft = Alignment() centerleft.horz = Alignment.HORZ_LEFT centerleft.vert = Alignment.VERT_CENTER centerleft.wrap = 1 bottomcentered = Alignment() bottomcentered.horz = Alignment.HORZ_CENTER bottomcentered.vert = Alignment.VERT_BOTTOM bottomcentered.wrap = 1 bottomleft = Alignment() bottomleft.horz = Alignment.HORZ_LEFT bottomleft.vert = Alignment.VERT_BOTTOM bottomleft.wrap = 1 bottomright = Alignment() bottomright.horz = Alignment.HORZ_RIGHT bottomright.vert = Alignment.VERT_BOTTOM bottomright.wrap = 1 topleft = Alignment() topleft.horz = Alignment.HORZ_LEFT topleft.vert = Alignment.VERT_TOP topleft.wrap = 1 topright = Alignment() topright.horz = Alignment.HORZ_RIGHT topright.vert = Alignment.VERT_TOP topright.wrap = 1 # Styles twips = lambda pt: 20 * pt # Points to Twips def style(fontsize=10, bold=False, italic=False, align=None): """ XFStyle builder helper """ style = XFStyle() style.font.height = twips(fontsize) style.font.bold = bold style.font.italic = italic if align is not None: style.alignment = align return style self._styles = styles = { "default": style(align=topleft), "numeric": style(align=bottomright), "title": style(fontsize=14, bold=True, align=bottomleft), "subheader": style(fontsize=8, italic=True, align=bottomleft), "row_label": style(bold=True, align=topleft), "col_label": style(bold=True, align=bottomcentered), "fact_label": style(fontsize=13, bold=True, align=centerleft), "axis_title": style(fontsize=11, bold=True, align=center), "total": style(fontsize=11, bold=True, italic=True, align=topright), "total_left": style(fontsize=11, bold=True, italic=True, align=topleft), "total_right": style(fontsize=11, bold=True, italic=True, align=center), "grand_total": style(fontsize=12, bold=True, italic=True, align=topright), } return styles
from xlwt import Alignment, XFStyle, Borders, Font # ************************************************************************************** # generic styles # # default borders defaultBorders = Borders() defaultBorders.bottom = Borders.THIN defaultBorders.top = Borders.THIN defaultBorders.right = Borders.THIN defaultBorders.left = Borders.THIN # center align alignCenter = Alignment() alignCenter.horz = Alignment.HORZ_CENTER alignCenter.vert = Alignment.VERT_CENTER # Top alignment, should be used for all cells alignTop = Alignment() alignTop.vert = Alignment.VERT_TOP # header centered and wrapped alignWrap = Alignment() alignWrap.horz = Alignment.HORZ_CENTER alignWrap.wrap = Alignment.WRAP_AT_RIGHT # generic font, 11 point Calibri defaultFont = Font() defaultFont.height = 220 defaultFont.name = 'Arial'