Exemplo n.º 1
0
    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")
Exemplo n.º 2
0
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)
Exemplo n.º 3
0
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")
Exemplo n.º 4
0
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
Exemplo n.º 5
0
 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'  # 设置数字格式
Exemplo n.º 6
0
 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'  # 设置数字格式
Exemplo n.º 7
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
Exemplo n.º 8
0
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')
Exemplo n.º 9
0
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
Exemplo n.º 10
0
    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)
Exemplo n.º 12
0
    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)
Exemplo n.º 13
0
 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);
     
Exemplo n.º 14
0
 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)
Exemplo n.º 16
0
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)
Exemplo n.º 17
0
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)
Exemplo n.º 18
0
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)
Exemplo n.º 20
0
    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
Exemplo n.º 22
0
    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
Exemplo n.º 23
0
    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)
Exemplo n.º 24
0
# 操作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)
Exemplo n.º 25
0
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
Exemplo n.º 26
0
    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',
        }
Exemplo n.º 27
0
    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)
Exemplo n.º 29
0
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)
Exemplo n.º 30
0
 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
Exemplo n.º 32
0
    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)
Exemplo n.º 33
0
    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)
Exemplo n.º 34
0
    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
Exemplo n.º 35
0
                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
Exemplo n.º 37
0
    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
Exemplo n.º 38
0
                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")
Exemplo n.º 39
0
    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
Exemplo n.º 40
0
    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
Exemplo n.º 41
0
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'