def export_to_excel(self, **kwargs):
work_book = Workbook(encoding="ascii")
center_alignment = Alignment()
center_alignment.horz = Alignment.HORZ_RIGHT
center_alignment.vert = Alignment.VERT_CENTER
border = Borders()
border.top = Borders.THIN
border.left = Borders.THIN
border.bottom = Borders.THIN
border.right = Borders.THIN
head_style = XFStyle()
head_pattern = Pattern()
head_pattern.pattern = Pattern.SOLID_PATTERN
head_pattern.pattern_fore_colour = Style.colour_map['gray25']
head_style.pattern = head_pattern
head_font = Font()
head_font.bold = True
head_style.font = head_font
head_style.alignment = center_alignment
head_style.borders = border
data_style = XFStyle()
data_style.alignment = center_alignment
data_style.borders = border
work_sheet = work_book.add_sheet("Sheet")
limit = 10000
count = self.get_data_scale(**kwargs)['count']
times = (count + limit - 1) // limit
add_header = False
for i in range(1):
kwargs["startIndex"] = i * limit + 1
kwargs['endIndex'] = (i + 1) * limit + 1
records = self.get_data(**kwargs)
column_names = []
y = 0
for data in records:
if not add_header:
x = 0
for column_name in data.keys():
work_sheet.write(y, x, column_name, head_style)
column_names.append(column_name)
x += 1
add_header = True
y += 1
x = 0
for column_name in column_names:
work_sheet.write(y, x, data[column_name], data_style)
x += 1
y += 1
x = 0
work_book.save(BASE_WIN_OUTPUT_PATH + "result.xls")
def getDefualtStyle(): fnt = Font() fnt.name = 'Arial' borders = Borders() borders.left = Borders.THIN borders.right = Borders.THIN borders.top = Borders.THIN borders.bottom = Borders.THIN #pattern = Pattern() #pattern.pattern = Style.pattern_map['solid'] ###pattern.pattern_back_colour = 0xBFBFBF #pattern.pattern_fore_colour = 0x37 alignment = Alignment() #alignment.horizontal = Alignment.HORZ_LEFT alignment.horizontal = Alignment.HORZ_RIGHT style = XFStyle() #~ style.num_format_str='0.000%' #~ style.num_format_str='0+' #~ style.font = fnt style.align = alignment #style.borders = borders #~ style.pattern = pattern return style
def getDefualtStyle(): fnt = Font() fnt.name = 'Arial' borders = Borders() borders.left = Borders.THIN borders.right = Borders.THIN borders.top = Borders.THIN borders.bottom = Borders.THIN #pattern = Pattern() #pattern.pattern = Style.pattern_map['solid'] ###pattern.pattern_back_colour = 0xBFBFBF #pattern.pattern_fore_colour = 0x37 alignment = Alignment() #alignment.horizontal = Alignment.HORZ_LEFT alignment.horizontal = Alignment.HORZ_RIGHT style = XFStyle() #~ style.num_format_str='0.000%' #~ style.num_format_str='0+' #~ style.font = fnt style.align = alignment style.borders = borders #~ style.pattern = pattern return style
def xls_headers(self, text, title):
u"""
"""
# headers style
header1 = easyxf()
header2 = easyxf()
# font
font1 = Font()
font1.name = 'Times New Roman'
font1.bold = True
# alignment
alig1 = Alignment()
alig1.horz = 2
# assign
header1.font = font1
header1.alignment = alig1
header2.font = font1
# headers 1st sheet
self.feuy1.write_merge(0, 0, 0, 1, text.get('titre') +
title,
header1)
self.feuy1.write(1, 0, text.get('nomfic'), header2)
self.feuy1.write(2, 0, text.get('mtcthem'), header2)
self.feuy1.write(3, 0, text.get('mtcgeo'), header2)
self.feuy1.write(4, 0, text.get('description'), header2)
self.feuy1.write(5, 0, text.get('cadre'), header2)
self.feuy1.write(6, 0, text.get('num_objets'), header2)
self.feuy1.write(7, 0, text.get('num_attrib'), header2)
self.feuy1.write(8, 0, text.get('date_crea'), header2)
self.feuy1.write(9, 0, text.get('date_actu'), header2)
self.feuy1.write(10, 0, text.get('source'), header2)
self.feuy1.write(11, 0, text.get('diffusion'), header2)
self.feuy1.write(12, 0, text.get('responsable'), header2)
self.feuy1.write(13, 0, text.get('ptcontact'), header2)
self.feuy1.write(14, 0, text.get('siteweb'), header2)
self.feuy1.write(15, 0, text.get('geometrie'), header2)
self.feuy1.write(16, 0, text.get('echelle'), header2)
self.feuy1.write(17, 0, text.get('precision'), header2)
self.feuy1.write(18, 0, text.get('srs'), header2)
self.feuy1.write(19, 0, text.get('emprise'), header2)
# headers 2nd sheet
self.feuy2.write(0, 0, text.get('numero'), header2)
self.feuy2.write(0, 1, text.get('nom'), header2)
self.feuy2.write(0, 2, text.get('type'), header2)
self.feuy2.write(0, 3, text.get('longueur'), header2)
self.feuy2.write(0, 4, text.get('precision'), header2)
self.feuy2.write(0, 5, text.get('description'), header2)
self.feuy2.write(0, 6, text.get('somme'), header2)
self.feuy2.write(0, 7, text.get('moyenne'), header2)
self.feuy2.write(0, 8, text.get('mediane'), header2)
self.feuy2.write(0, 9, text.get('min'), header2)
self.feuy2.write(0, 10, text.get('max'), header2)
self.feuy2.write(0, 11, text.get('ecartype'), header2)
# End of function
return self.feuy1, self.feuy2
def output(file_or_stream, sheetname, headers, values, encoding = 'utf8', footer_text = None, footer_link = None):
import xlwt
book = xlwt.Workbook(encoding)
sh = book.add_sheet(sheetname)
datestyle = XFStyle()
datestyle.num_format_str = 'DD/MM/YYYY'
timestyle = XFStyle()
timestyle.num_format_str = 'HH:MM:SS'
header_font = Font()
header_font.bold = True
al = Alignment()
al.horz = Alignment.HORZ_CENTER
header_style = XFStyle()
header_style.font = header_font
header_style.alignment = al
for i,header in enumerate(headers):
sh.write(0, i, header, header_style)
sh.set_panes_frozen(True) # frozen headings instead of split panes
sh.set_horz_split_pos(1) # in general, freeze after last heading row
sh.set_remove_splits(True) # if user does unfreeze, don't leave a split there
for j, row in enumerate(values):
for i, value in enumerate(row):
if value.__class__ == date:
sh.write(j+1, i, value, datestyle)
elif value.__class__ == time:
sh.write(j+1, i, value, timestyle)
else:
sh.write(j+1, i, value)
if footer_link and footer_text:
link_font = Font()
link_font.name = 'Verdana'
link_font.colour_index = 4
link_font.height = 20*8
al = Alignment()
al.horz = Alignment.HORZ_CENTER
al.vert = Alignment.VERT_BOTTOM
link_style = XFStyle()
link_style.font = link_font
link_style.alignment = al
row = len(values) + 1
sh.write_merge(row, row, 0, len(headers)-1, Formula('HYPERLINK("' + footer_link + '";"' + footer_text + '")'), link_style)
book.save(file_or_stream)
def queryExcel(filename, dategroup, response):
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 = 360
font1.name = u'仿宋'
style1.font = font1
algn = Alignment()
algn.horz = Alignment.HORZ_LEFT
style1.alignment = algn
style1.font = font1
style0 = xlwt.XFStyle()
algn0 = Alignment()
algn0.horz = Alignment.HORZ_CENTER
font = Font()
font.height = 320
font.bold = False
font.name = u'仿宋'
style0.alignment = algn0
style0.font = font
wb = xlwt.Workbook()
ws = wb.add_sheet(u"销售报表", cell_overwrite_ok=True)
rownum = 0
ws.write_merge(rownum, rownum, 0, 0, u'序号', style0)
ws.write_merge(rownum, rownum, 1, 1, u'品牌', style0)
ws.write_merge(rownum, rownum, 2, 2, u'型号', style0)
ws.write_merge(rownum, rownum, 3, 3, u'类型', style0)
ws.write_merge(rownum, rownum, 4, 4, u'数量', style0)
ws.write_merge(rownum, rownum, 5, 5, u'账户', style0)
ws.write_merge(rownum, rownum, 6, 6, u'姓名', style0)
ws.write_merge(rownum, rownum, 7, 7, u'主管', style0)
rownum += 1
datanum = 1
for data in dategroup:
ws.write_merge(rownum, rownum, 0, 7, u'日期：%s 厅台：%s 总计：%s 台' % (data['date'], data['officename'],data['totalnum']), style1)
rownum += 1
for i, row in enumerate(data['query']):
ws.write_merge(rownum, rownum, 0, 0, datanum, style0)
ws.write_merge(rownum, rownum, 1, 1, row['productbrandsname'], style0)
ws.write_merge(rownum, rownum, 2, 2, row['productname'], style0)
ws.write_merge(rownum, rownum, 3, 3, row['ordertypename'], style0)
ws.write_merge(rownum, rownum, 4, 4, row['ordernum'], style0)
ws.write_merge(rownum, rownum, 5, 5, row['username'], style0)
ws.write_merge(rownum, rownum, 6, 6, row['get_full_name'], style0)
ws.write_merge(rownum, rownum, 7, 7, row['managername'], style0)
datanum += 1
rownum += 1
for i in range(8):
ws.col(i).width = 256 * 20
wb.save(response)
def _get_header_styles(self):
s1 = XFStyle()
al = Alignment()
al.horz = Alignment.HORZ_CENTER
s1.alignment = al
s2 = XFStyle()
borders = Borders()
borders.bottom = 2
s2.borders = borders
return s1, s2
def excel_writer(filenames):
style2 = XFStyle()
style2.num_format_str = '####.##0'
al = Alignment()
al.horz = Alignment.HORZ_CENTER
al.vert = Alignment.VERT_BOTTOM
style2.alignment = al
style3 = XFStyle()
style3.num_format_str = '0.00%'
style3.alignment = al
wb = Workbook()
sheet1 = wb.add_sheet("Sheet 1")
index = 0
columns_list = ['c', 'e', 'f', 'g', 'h', 'i', 'k', 'l', 'm']
if True:
print(f"{bcolors.WARNING}PLEASE INPUT THE 9 COLUMN LETTERS YOU NEED SEPARATED WITH A COMMA{bcolors.ENDC}")
print(f"{bcolors.WARNING}Example: D,A,B,C,F,G,E,K,W{bcolors.ENDC}")
columns = input()
while invalid(columns):
print(f"{bcolors.FAIL}INVALID INPUT{bcolors.ENDC}")
print(f"{bcolors.WARNING}Example: D,A,B,C{bcolors.ENDC}")
columns = input()
columns_list = re.split(',', columns)
sheet1.write(index, letters_to_indexes(columns_list[0]), "Concurrent Users")
sheet1.write(index, letters_to_indexes(columns_list[1]), "Average Total Execution Time (sec)")
sheet1.write(index, letters_to_indexes(columns_list[2]), "90% Total Execution Time (sec)")
sheet1.write(index, letters_to_indexes(columns_list[3]), "Min Total Execution Time (sec)")
sheet1.write(index, letters_to_indexes(columns_list[4]), "Max Total Execution Time (sec)")
sheet1.write(index, letters_to_indexes(columns_list[5]), "Number of Calls")
sheet1.write(index, letters_to_indexes(columns_list[6]), "Error Rate (%)")
sheet1.write(index, letters_to_indexes(columns_list[7]), "Date")
sheet1.write(index, letters_to_indexes(columns_list[8]), "Start Time")
index = 1
for file in filenames:
print(file)
numtuple = getnumbers(file)
print(numtuple)
sheet1.write(index, letters_to_indexes(columns_list[0]), numtuple[6])
sheet1.write(index, letters_to_indexes(columns_list[1]), numtuple[0], style2)
sheet1.write(index, letters_to_indexes(columns_list[2]), numtuple[1], style2)
sheet1.write(index, letters_to_indexes(columns_list[3]), numtuple[2], style2)
sheet1.write(index, letters_to_indexes(columns_list[4]), numtuple[3], style2)
sheet1.write(index, letters_to_indexes(columns_list[5]), numtuple[4])
sheet1.write(index, letters_to_indexes(columns_list[6]), numtuple[5], style3)
sheet1.write(index, letters_to_indexes(columns_list[7]), str(date.today()))
sheet1.write(index, letters_to_indexes(columns_list[8]), "4:42:00")
index += 1
wb.save("example.xls")
def queryExcel(filename, dategroup, response):
'''
导出excel
'''
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 = 260
font1.name = u'仿宋'
style1.font = font1
algn = Alignment()
algn.horz = Alignment.HORZ_LEFT
style1.alignment = algn
style1.font = font1
style0 = xlwt.XFStyle()
algn0 = Alignment()
algn0.horz = Alignment.HORZ_CENTER
font = Font()
font.height = 220
font.bold = False
font.name = u'仿宋'
style0.alignment = algn0
style0.font = font
wb = xlwt.Workbook()
ws = wb.add_sheet(u"考试报表", cell_overwrite_ok=True)
rownum = 0
ws.write_merge(rownum, rownum, 0, 0, u'序号', style0)
ws.write_merge(rownum, rownum, 1, 1, u'员工ID', style0)
ws.write_merge(rownum, rownum, 2, 2, u'姓名', style0)
ws.write_merge(rownum, rownum, 3, 3, u'得分', style0)
ws.write_merge(rownum, rownum, 4, 4, u'主管', style0)
rownum += 1
datanum = 1
for data in dategroup:
ws.write_merge(rownum, rownum, 0, 4, u'考试：%s 参考人数：%s 旷考人数：%s' % (data['name'],data['totalnum'],data['untotalnum']), style1)
rownum += 1
for i, row in enumerate(data['query']):
ws.write_merge(rownum, rownum, 0, 0, datanum, style0)
ws.write_merge(rownum, rownum, 1, 1, row['username'], style0)
ws.write_merge(rownum, rownum, 2, 2, row['get_full_name'], style0)
ws.write_merge(rownum, rownum, 3, 3, row['score'], style0)
ws.write_merge(rownum, rownum, 4, 4, row['managername'], style0)
datanum += 1
rownum += 1
for i in range(5):
ws.col(i).width = 256 * 10
wb.save(response)
def _get_header_style(self):
# s1 = self._style_factory()
al = Alignment()
al.horz = Alignment.HORZ_CENTER
# s1.alignment = al
s2 = self._style_factory()
borders = Borders()
borders.bottom = 2
s2.borders = borders
s2.alignment = al
return s2
def _get_header_style(self):
# s1 = self._style_factory()
al = Alignment()
al.horz = Alignment.HORZ_CENTER
# s1.alignment = al
s2 = self._style_factory()
borders = Borders()
borders.bottom = 2
s2.borders = borders
s2.alignment = al
return s2
def def_style():
style = XFStyle()
font = Font() # 这部分设置字体
font.name = 'Times New Roman' # 或者换成外面传进来的参数，这样可以使一个函数定义所有style
# font.bold = 'True'
# font.height = 24
# font.size = 2000
# font.colour_index = 3
style.font = font
alignment = Alignment() # 这部分设置居中格式
alignment.horz = Alignment.HORZ_CENTER # 水平居中
alignment.vert = Alignment.VERT_CENTER # 垂直居中
style.alignment = alignment
return style
def css2excel(css):
#custom_css = 'font: name "%s", %s on'%(current_value['font-family'].split(",")[0], current_value['font-weight'].split(",")[0])
#export_sheet.write(row_idx, col_idx, current_value['data'], xlwt.easyxf('font: italic on; pattern: pattern solid, fore-colour grey25'))
xf_list = []
fnt = Font()
borders = Borders()
pattern = Pattern()
align = Alignment()
process_css = {
'font-family' : [fnt, "name" , lambda x : x.split(",")[0]],
'color' : [fnt, "colour_index", lambda x : excel_color_map.get(x,0)+8],
'font-weight' : [fnt, "bold", lambda x : x.upper() == 'BOLD'],
#'font-weight' : [fnt, "bold", lambda x : x == '700'],
'text-align' : [align, "horz", lambda x : {'left':align.HORZ_LEFT, 'right':align.HORZ_RIGHT, 'center':align.HORZ_CENTER, 'justified': align.HORZ_JUSTIFIED}[x]],
'background-color' : [pattern,"pattern_fore_colour", lambda x: excel_color_map.get(x,16)+8],
}
#TODO process_css -> css
for i in process_css.keys():
#print process_css[i][0] ,".",process_css[i][1], " => " , css[i] ," | ", process_css[i][2](css[i])
setattr(process_css[i][0], process_css[i][1], process_css[i][2](css[i]))
style = XFStyle()
style.font = fnt
borders.left = Borders.THIN
borders.right = Borders.THIN
borders.top = Borders.THIN
borders.bottom = Borders.THIN
style.borders = borders
style.pattern = pattern
style.pattern.pattern = 1
style.alignment = align
return style
class ReportStyles():
alignment = Alignment()
font = Font()
borders = Borders()
# col = Column()
def borders_light(self):
self.borders.left = Borders.THIN
self.borders.right = Borders.THIN
self.borders.top = Borders.THIN
self.borders.bottom = Borders.THIN
return self.borders
def align_hor_right(self):
self.alignment.horz = Alignment.HORZ_RIGHT
return self.alignment
def align_hor_left(self):
self.alignment.horz = Alignment.HORZ_LEFT
return self.alignment
def align_hor_center(self):
self.alignment.horz = Alignment.HORZ_CENTER
return self.alignment
def text_bold(self):
self.font.bold = True
return self.font
def setupFormat(self):
headFont = Font()
headFont.bold = True
alignmentSetup = Alignment()
alignmentSetup.wrap = True
borders = Borders()
borders.left = 1
borders.right = 1
borders.top = 1
borders.bottom = 1
self.header_style = XFStyle()
self.header_style.font = headFont
self.table_style = XFStyle()
self.table_style.borders = borders
self.table_style.alignment = alignmentSetup
def setupFormat(self):
headFont = Font()
headFont.bold = True
alignmentSetup = Alignment()
alignmentSetup.wrap = True
borders = Borders()
borders.left = 1
borders.right = 1
borders.top = 1
borders.bottom = 1
self.header_style = XFStyle()
self.header_style.font = headFont
self.table_style = XFStyle()
self.table_style.borders = borders
self.table_style.alignment = alignmentSetup
class Column(Cell):
title_fnt = Font()
title_fnt.name = 'Calibri'
title_fnt.bold = True
title_fnt.height = 12 * 20 # font size 12
obj_fnt = Font()
obj_fnt.name = 'Calibri'
obj_fnt.height = 12 * 20 # font size 12
title_aln = Alignment()
title_aln.horz = Alignment.HORZ_CENTER
title_aln.vert = Alignment.VERT_CENTER
title_aln.wrap = Alignment.WRAP_AT_RIGHT
title_ptn = Pattern()
title_ptn.pattern = Pattern.SOLID_PATTERN
title_ptn.pattern_fore_colour = 0x2C
def __init__(self,
text,
left_border=False,
right_border=False,
use_pattern=False,
width=0x0E00,
func=lambda obj: ""):
super(Column, self).__init__(text,
self.title_fnt,
self.title_aln,
Borders(),
self.title_ptn,
width=width)
self.obj_style = XFStyle()
self.obj_style.alignment = self.title_aln
self.obj_style.borders = Borders()
self.obj_style.borders.left = Borders.HAIR
self.obj_style.borders.right = Borders.HAIR
self.obj_style.borders.top = Borders.HAIR
self.obj_style.borders.bottom = Borders.HAIR
self.border.left = Borders.HAIR
self.border.right = Borders.HAIR
self.border.top = Borders.THICK
self.border.bottom = Borders.THICK
if left_border:
self.obj_style.borders.left = Borders.THICK
self.border.left = Borders.THICK
if right_border:
self.obj_style.borders.right = Borders.THICK
self.border.right = Borders.THICK
self.obj_style.font = self.obj_fnt
if use_pattern:
self.obj_style.pattern = self.pattern
self.func = func
def __init__(self):
self.style = XFStyle()
base_font = Font() # 设置基本字体
base_font.height = FONT_SIZE_UNIT * 12
self.style.font = base_font
alignment = Alignment() # 设置对齐
alignment.horz = Alignment.HORZ_CENTER
alignment.vert = Alignment.VERT_CENTER
self.style.alignment = alignment
borders = Borders() # 设置边框
borders.left = Borders.THIN
borders.right = Borders.THIN
borders.top = Borders.THIN
borders.bottom = Borders.THIN
borders.left_colour = COLOUR_BLACK
borders.right_colour = COLOUR_BLACK
borders.top_colour = COLOUR_BLACK
borders.bottom_colour = COLOUR_BLACK
self.style.borders = borders
self.style.num_format_str = '#,##0' # 设置数字格式
def __init__(self):
self.style = XFStyle()
base_font = Font() # 设置基本字体
base_font.height = FONT_SIZE_UNIT * 12
self.style.font = base_font
alignment = Alignment() # 设置对齐
alignment.horz = Alignment.HORZ_CENTER
alignment.vert = Alignment.VERT_CENTER
self.style.alignment = alignment
borders = Borders() # 设置边框
borders.left = Borders.THIN
borders.right = Borders.THIN
borders.top = Borders.THIN
borders.bottom = Borders.THIN
borders.left_colour = COLOUR_BLACK
borders.right_colour = COLOUR_BLACK
borders.top_colour = COLOUR_BLACK
borders.bottom_colour = COLOUR_BLACK
self.style.borders = borders
self.style.num_format_str = '#,##0' # 设置数字格式
def downloadTrue(request):
response = HttpResponse(mimetype=u'application/ms-excel')
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 = 250
font1.name = u'仿宋'
style1.font = font1
algn = Alignment()
algn.horz = Alignment.HORZ_LEFT
style1.alignment = algn
style1.font = font1
wb = xlwt.Workbook()
ws = wb.add_sheet(u"错误实名制列表", cell_overwrite_ok=True)
rownum = 0
ws.write_merge(rownum, rownum, 0, 0, u'手机号', style1)
ws.write_merge(rownum, rownum, 1, 1, u'姓名', style1)
ws.write_merge(rownum, rownum, 2, 2, u'身份证号', style1)
ws.write_merge(rownum, rownum, 3, 3, u'地址', style1)
ws.write_merge(rownum, rownum, 4, 4, u'错误原因', style1)
rownum += 1
for o in Truename.objects.filter(status=2).order_by('datetime'):
ws.write_merge(rownum, rownum, 0, 0, o.tel, style1)
ws.write_merge(rownum, rownum, 1, 1, o.name, style1)
ws.write_merge(rownum, rownum, 2, 2, o.number, style1)
ws.write_merge(rownum, rownum, 3, 3, o.address, style1)
ws.write_merge(rownum, rownum, 4, 4, o.help, style1)
rownum += 1
for i in range(5):
ws.col(i).width = 256 * 20
wb.save(response)
return response
def get_sheet_title_style(bg_color=0x39,
font_color=0x0,
font_size=300,
has_pattern=True,
horz_center=True):
fnt = Font()
fnt.name = 'Arial'
fnt.colour_index = font_color
fnt.bold = True
fnt.height = font_size
borders = Borders()
borders.left = 1
borders.right = 1
borders.top = 1
borders.bottom = 1
al = Alignment()
if horz_center:
al.horz = Alignment.HORZ_CENTER
else:
al.horz = Alignment.HORZ_LEFT
al.vert = Alignment.VERT_CENTER
pattern = None
if has_pattern:
pattern = Pattern()
pattern.pattern = 1
pattern.pattern_fore_colour = bg_color
pattern.pattern_back_colour = bg_color
style = XFStyle()
style.font = fnt
style.borders = borders
style.alignment = al
if pattern:
style.pattern = pattern
return style
def main():
lst = []
path = r'./Homework/03/'
with open(path + 'test.txt', 'r') as fpin:
title = fpin.readline().strip().replace('\n', '').split(',')
while True:
s = fpin.readline().strip().replace('\n', '').split(',')
if s == ['']:
break
lst.append(s)
fpin.close()
book = Workbook()
sheet1 = book.add_sheet('data')
style = XFStyle()
font = Font()
font.name = '宋体'
font.height = 280
style.font = font
alignment = Alignment()
alignment.horz = Alignment.HORZ_CENTER
alignment.vert = Alignment.VERT_CENTER
style.alignment = alignment
row = sheet1.row(0)
for i, j in zip(range(len(title)), title):
row.write(i, j, style=style)
for i, j in zip(range(1, len(lst) + 1), lst):
row = sheet1.row(i)
for x, y in zip(range(len(j)), j):
row.write(x, y, style=style)
book.save(path + 'data.xls')
def __init__(self, timesheets, year=datetime.date.today().year):
self.timesheets = timesheets.order_by('activity__person', 'project',
'year', 'month')
self.book = Workbook()
self.year = int(year)
font_header = Font()
font_header.bold = True
font_title = Font()
font_title.bold = True
grey_pattern = Pattern()
grey_pattern.pattern = Pattern.SOLID_PATTERN
grey_pattern.pattern_fore_colour = Style.colour_map['gray25']
self.header_style = XFStyle()
self.header_style.pattern = grey_pattern
self.header_style.font = font_header
self.header_title = XFStyle()
self.header_title.font = font_title
self.date_style = XFStyle()
self.date_style.num_format_str = 'M/D/YY'
self.title_style = XFStyle()
self.title_style.pattern = grey_pattern
self.title_style.font = font_title
self.title_style.alignment.horz = self.title_style.alignment.HORZ_CENTER
self.txt_centered_al = Alignment()
self.txt_centered_al.horz = Alignment.HORZ_CENTER
self.txt_centered = XFStyle()
self.txt_centered.alignment = self.txt_centered_al
self.header_date_style = XFStyle()
self.header_date_style.pattern = grey_pattern
self.header_date_style.font = font_header
self.header_date_style.alignment = self.txt_centered_al
def css2excel(css):
fnt = Font()
borders = Borders()
pattern = Pattern()
align = Alignment()
process_css = {
'font-family': [fnt, "name", lambda x: x.split(",")[0]],
'font-size': [fnt, "height", lambda x: get_font_height(x.upper())],
'color': [fnt, "colour_index", lambda x: match_color_index(x.upper())],
'font-weight': [fnt, "bold", lambda x: x == 'bold'],
'font-style': [fnt, "italic", lambda x: x == 'italic'],
'text-align':
[align, "horz", lambda x: get_horizontal_align(x, align)],
'vertical-align':
[align, "vert", lambda x: get_vertical_align(x, align)],
'background-color': [
pattern, "pattern_fore_colour",
lambda x: match_color_index(x.upper())
],
}
for i in css.keys():
if i in process_css.keys():
setattr(process_css[i][0], process_css[i][1],
process_css[i][2](css[i].strip()))
style = XFStyle()
style.font = fnt
borders.left = Borders.THIN
borders.right = Borders.THIN
borders.top = Borders.THIN
borders.bottom = Borders.THIN
style.borders = borders
style.pattern = pattern
style.pattern.pattern = 1
style.alignment = align
style.num_format_str = '#,##0.00'
return style
def account_budget(self,cr,uid,ids,context=None):
this=self.browse(cr,uid,ids[0])
year = this.year_id.name
year_id = this.year_id.id
month = this.month
if int(month) in [1,3,5,7,8,10,12]:
join_date=year +'-'+month+'-'+'31'
if int(month) in [4,6,9,11]:
join_date=year +'-'+month+'-'+'30'
if int(month) in [2]:
if int(year) % 4 == 0:
join_date=year +'-'+month+'-'+'29'
else:
join_date=year +'-'+month+'-'+'28'
if month == '1':
month_name = 'January'
elif month == '2':
month_name = 'February'
elif month == '3':
month_name = 'March'
elif month == '4':
month_name = 'April'
elif month == '5':
month_name = 'May'
elif month == '6':
month_name = 'June'
elif month == '7':
month_name = 'July'
elif month == '8':
month_name = 'August'
elif month == '9':
month_name = 'September'
elif month == '10':
month_name = 'October'
elif month == '11':
month_name = 'November'
elif month == '12':
month_name = 'December'
else:
raise osv.except_osv(_('Warning !'),_("Specify month correctly. "))
#Define the font attributes for header
fnt = Font()
fnt.name = 'Arial'
fnt.height= 275
#Define the font attributes for header
content_fnt = Font()
content_fnt.name ='Arial'
content_fnt.height =220
align_content = Alignment()
align_content.horz= Alignment.HORZ_CENTER
borders = Borders()
borders.left = 0x02
borders.right = 0x02
borders.top = 0x02
borders.bottom = 0x02
#The text should be centrally aligned
align = Alignment()
align.horz = Alignment.HORZ_CENTER
align.vert = Alignment.VERT_CENTER
#We set the backgroundcolour here
pattern = Pattern()
pattern.pattern = Pattern.SOLID_PATTERN
pattern.pattern_fore_colour = 0x1F
#apply the above settings to the row(0) header
style_header= XFStyle()
style_header.font= fnt
style_header.pattern= pattern
style_header.borders = borders
style_header.alignment=align
#Define the font attributes for header
fnt1 = Font()
fnt1.name = 'Arial'
fnt1.height= 275
#Define the font attributes for header
content_fnt1 = Font()
content_fnt1.name ='Arial'
content_fnt1.height =220
align_content1 = Alignment()
align_content1.horz= Alignment.HORZ_CENTER
borders1 = Borders()
borders1.left = 0x02
borders1.right = 0x02
borders1.top = 0x02
borders1.bottom = 0x02
#The text should be centrally aligned
align1 = Alignment()
align1.horz = Alignment.HORZ_CENTER
align1.vert = Alignment.VERT_CENTER
#We set the backgroundcolour here
pattern1 = Pattern()
pattern1.pattern = Pattern.SOLID_PATTERN
pattern1.pattern_fore_colour = 0x32
#apply the above settings to the row(0) header
style_header1= XFStyle()
style_header1.font= fnt1
style_header1.pattern= pattern1
style_header1.borders = borders1
style_header1.alignment=align1
#Define the font attributes for header
fnt2 = Font()
fnt2.name = 'Arial'
fnt2.height= 275
#Define the font attributes for header
content_fnt2 = Font()
content_fnt2.name ='Arial'
content_fnt2.height =220
align_content2 = Alignment()
align_content2.horz= Alignment.HORZ_CENTER
borders2 = Borders()
borders2.left = 0x02
borders2.right = 0x02
borders2.top = 0x02
borders2.bottom = 0x02
#The text should be centrally aligned
align2 = Alignment()
align2.horz = Alignment.HORZ_CENTER
align2.vert = Alignment.VERT_CENTER
#We set the backgroundcolour here
pattern2 = Pattern()
pattern2.pattern = Pattern.SOLID_PATTERN
pattern2.pattern_fore_colour = 0x0A
#apply the above settings to the row(0) header
style_header2= XFStyle()
style_header2.font= fnt2
style_header2.pattern= pattern2
style_header2.borders = borders2
style_header2.alignment=align2
style_content= XFStyle()
style_content.alignment = align_content
style_content.font = content_fnt
month_name = 'Payment ('+str(month_name)+')'
wb = Workbook()
ws = wb.add_sheet('Budget')
ws.row(0).height=500
ws.write(0,0,'Department Name',style_header)
ws.col(0).width = 8000
ws.write(0,1,'Department HoD',style_header)
ws.col(1).width = 8000
ws.write(0,2,'RO of HOD',style_header)
ws.col(2).width = 8000
ws.write(0,3,'Allocated Budget',style_header)
ws.col(3).width = 8000
ws.write(0,4,'Salary Amount',style_header)
ws.col(4).width = 5000
ws.write(0,5,month_name,style_header)
ws.col(5).width = 8000
ws.write(0,6,'Difference',style_header)
ws.col(6).width = 8000
ws.write(0,7,'No Of Emp',style_header)
ws.col(7).width = 8000
# ws.write(0,5,'O.T. Amount',style_header)
# ws.col(5).width = 4400
# ws.write(0,6,'Total Amount',style_header)
# ws.col(6).width = 4400
# ws.write(0,7,'Insentive Amount',style_header)
# ws.col(7).width = 4400
# ws.write(0,8,'Deduction Amount',style_header)
# ws.col(8).width = 5000
# ws.write(0,9,'Percentage',style_header)
# ws.col(9).width = 4400
emp_ids = []
emp_obj=self.pool.get('hr.employee')
pay_obj=self.pool.get('salary.payment.line')
if this.dept_id:
cr.execute("select emp.id from hr_employee as emp left join resource_resource " \
"as res on (emp.resource_id=res.id) left join hr_department as dept on " \
"(emp.department_id=dept.id) where emp.department_id = '"+str(this.dept_id.id)+"' and " \
"emp.department_id is not null and res.active=True and emp.joining_date <'"+str(join_date)+"' order by dept.dept_code, " \
"(substring(emp.sinid, '^[0-9]+'))::int ,substring(emp.sinid, '[^0-9_].*$')")
temp = cr.fetchall()
for data in temp:
if len(data)>0 and data[0] != None:
emp_ids.append(data[0])
else:
cr.execute("select emp.id from hr_employee as emp left join resource_resource " \
"as res on (emp.resource_id=res.id) left join hr_department as dept on (emp.department_id=dept.id) " \
"where res.active=True and emp.joining_date <'"+str(join_date)+"' order by dept.dept_code,(substring(emp.sinid, '^[0-9]+'))::int ,substring(emp.sinid, '[^0-9_].*$')")
temp = cr.fetchall()
for data in temp:
if len(data)>0 and data[0] != None:
emp_ids.append(data[0])
holiday_obj = self.pool.get('holiday.list')
if int(month) in [1,3,5,7,8,10,12]:
month = 31
if int(month) in [4,6,9,11]:
month = 30
if int(month) in [2]:
if int(year) % 4 == 0:
month = 29
else:
month = 28
off_day = working_day = 0
holiday_ids = holiday_obj.search(cr, uid, [('month','=',this.month),('year_id','=',year_id)])
for line in holiday_obj.browse(cr, uid, holiday_ids):
off_day = line.holiday
working_day = month - off_day
i=1
dept_dict = {}
grand = total = pay_total = pay_grand = budget = total_budget = 0.0
pay_data = False
flag = True
count1=0.0
for each in emp_obj.browse(cr, uid, emp_ids):
pay_ids = pay_obj.search(cr, uid, [('employee_id','=',each.id),('month','=',this.month),('year_id','=',this.year_id.id),('salary_type','=','Salary')])
if pay_ids:
pay_data = pay_obj.browse(cr, uid, pay_ids[0])
if dept_dict.has_key(str(each.department_id.id)):
if each.department_id:
salary = 0.0
if pay_data.employee_id.daily:
salary = pay_data.basic * working_day
else:
salary = pay_data.basic
total += salary
grand += salary
# if pay_data.employee_id.id == each.id:
# print"ssssssssssssssssssss minssssssssss",count1
# count1=count1+1
if pay_data and pay_data.employee_id.id == each.id:
pay_total += pay_data.total_amount or 0.0
pay_grand += pay_data.total_amount or 0.0
count1=count1+1
else:
pay_total += 0.0
pay_grand += 0.0
elif not each.department_id:
if flag:
i -= 1
ws.write(i,3, total)
ws.write(i,4, pay_total)
if budget:
diff = float(budget) - pay_total
else:
diff = pay_total
if budget and diff > 0:
ws.write(i,5, diff,style_header1)
elif budget and diff < 0:
ws.write(i,5, diff,style_header2)
else:
ws.write(i,5, diff,style_header2)
ws.write(i,6, count1)
count1=0.0
flag = False
i += 1
# salary = 0.0
# name = '[' + str(each.sinid) +'] '+ str(each.name)
#
# ws.write(i,0, 'X Department')
# ws.write(i,1,'X Reporting Officer')
# ws.write(i,2, name)
#
# if each.daily:
# salary = each.salary * working_day
# else:
# salary = each.salary
# ws.write(i,3, salary)
# if pay_data and pay_data.employee_id.id == each.id:
# ws.write(i,4,pay_data.total_amount or 0.0)
# else:
# ws.write(i,4, 0.0)
else:
dept_dict[str(each.department_id.id)] = ''
if i != 1:
i -= 1
ws.write(i,4, total)
ws.write(i,5, pay_total)
if budget:
diff = float(budget) - pay_total
else:
diff = pay_total
if budget and diff > 0:
ws.write(i,6, diff,style_header1)
elif budget and diff < 0:
ws.write(i,6, diff,style_header2)
else:
ws.write(i,6, diff,style_header2)
ws.write(i,7, count1)
count1=0.0
i += 1
if each.department_id:
total = pay_total = budget = 0.0
salary = 0.0
budget = each.department_id.dept_budget
dept = '[' + str(each.department_id.dept_code) +'] '+ str(each.department_id.name)
ws.write(i,0, dept)
ws.write(i,1, each.department_id.manager_id and each.department_id.manager_id.name or '')
ws.write(i,2, each.department_id.manager_id.parent_id and each.department_id.manager_id.parent_id.name or '')
ws.write(i,3, budget)
total_budget += budget
if pay_data.employee_id.daily:
salary = pay_data.basic * working_day
else:
salary = pay_data.basic
total += salary
grand += salary
# if pay_data.employee_id.id == each.id:
# print"saaaaaaaaaaaaaallllllllllllllllllllllllllll",count1
# count1=count1+1
if pay_data and pay_data.employee_id.id == each.id:
pay_total += pay_data.total_amount or 0.0
pay_grand += pay_data.total_amount or 0.0
count1=count1+1
else:
pay_total += 0.0
pay_grand += 0.0
i += 1
ws.write(i+3,1, 'Grand Total',style_header)
ws.write(i+3,3, total_budget,style_header)
ws.write(i+3,4, grand,style_header)
ws.write(i+3,5, pay_grand,style_header)
diff_grand = total_budget - pay_grand
ws.write(i+3,6, diff_grand,style_header1)
f = cStringIO.StringIO()
wb.save(f)
out=base64.encodestring(f.getvalue())
return self.write(cr, uid, ids, {'export_data':out, 'filename':'export.xls'}, context=context)
def detail_budget(self,cr,uid,ids,context=None):
this=self.browse(cr,uid,ids[0])
year = this.year_id.name
year_id = this.year_id.id
month = this.month
if month == '1':
month_name = 'January'
elif month == '2':
month_name = 'February'
elif month == '3':
month_name = 'March'
elif month == '4':
month_name = 'April'
elif month == '5':
month_name = 'May'
elif month == '6':
month_name = 'June'
elif month == '7':
month_name = 'July'
elif month == '8':
month_name = 'August'
elif month == '9':
month_name = 'September'
elif month == '10':
month_name = 'October'
elif month == '11':
month_name = 'November'
elif month == '12':
month_name = 'December'
else:
raise osv.except_osv(_('Warning !'),_("Specify month correctly. "))
if int(month) in [1,3,5,7,8,10,12]:
join_date=year +'-'+month+'-'+'31'
if int(month) in [4,6,9,11]:
join_date=year +'-'+month+'-'+'30'
if int(month) in [2]:
if int(year) % 4 == 0:
join_date=year +'-'+month+'-'+'29'
else:
join_date=year +'-'+month+'-'+'28'
#Define the font attributes for header
fnt = Font()
fnt.name = 'Arial'
fnt.height= 275
#Define the font attributes for header
content_fnt = Font()
content_fnt.name ='Arial'
content_fnt.height =220
align_content = Alignment()
align_content.horz= Alignment.HORZ_CENTER
borders = Borders()
borders.left = 0x02
borders.right = 0x02
borders.top = 0x02
borders.bottom = 0x02
#The text should be centrally aligned
align = Alignment()
align.horz = Alignment.HORZ_CENTER
align.vert = Alignment.VERT_CENTER
#We set the backgroundcolour here
pattern = Pattern()
pattern.pattern = Pattern.SOLID_PATTERN
pattern.pattern_fore_colour = 0x1F
#apply the above settings to the row(0) header
style_header= XFStyle()
style_header.font= fnt
style_header.pattern= pattern
style_header.borders = borders
style_header.alignment=align
#Define the font attributes for header
fnt1 = Font()
fnt1.name = 'Arial'
fnt1.height= 275
#Define the font attributes for header
content_fnt1 = Font()
content_fnt1.name ='Arial'
content_fnt1.height =220
align_content1 = Alignment()
align_content1.horz= Alignment.HORZ_CENTER
borders1 = Borders()
borders1.left = 0x02
borders1.right = 0x02
borders1.top = 0x02
borders1.bottom = 0x02
#The text should be centrally aligned
align1 = Alignment()
align1.horz = Alignment.HORZ_CENTER
align1.vert = Alignment.VERT_CENTER
#We set the backgroundcolour here
pattern1 = Pattern()
pattern1.pattern = Pattern.SOLID_PATTERN
pattern1.pattern_fore_colour = 0x32
#apply the above settings to the row(0) header
style_header1= XFStyle()
style_header1.font= fnt1
style_header1.pattern= pattern1
style_header1.borders = borders1
style_header1.alignment=align1
style_content= XFStyle()
style_content.alignment = align_content
style_content.font = content_fnt
month_name = 'Payment ('+str(month_name)+')'
wb = Workbook()
ws = wb.add_sheet('Budget')
ws.row(0).height=500
ws.write(0,0,'Department Name',style_header)
ws.col(0).width = 8000
ws.write(0,1,'Department HoD',style_header)
ws.col(1).width = 8000
ws.write(0,2,'RO of HOD',style_header)
ws.col(2).width = 8000
ws.write(0,3,'Employee Name',style_header)
ws.col(3).width = 9000
ws.write(0,4,'Designation',style_header)
ws.col(4).width = 8000
ws.write(0,5,'Working Days',style_header)
ws.col(5).width = 5000
ws.write(0,6,'Working Hours',style_header)
ws.col(6).width = 5000
ws.write(0,7,'Working Month',style_header)
ws.col(7).width = 5000
ws.write(0,8,'Salary Amount',style_header)
ws.col(8).width = 5000
ws.write(0,9,month_name,style_header)
ws.col(9).width = 8000
# ws.write(0,5,'O.T. Amount',style_header)
# ws.col(5).width = 4400
# ws.write(0,6,'Total Amount',style_header)
# ws.col(6).width = 4400
# ws.write(0,7,'Insentive Amount',style_header)
# ws.col(7).width = 4400
# ws.write(0,8,'Deduction Amount',style_header)
# ws.col(8).width = 5000
# ws.write(0,9,'Percentage',style_header)
# ws.col(9).width = 4400
emp_ids = []
emp_obj=self.pool.get('hr.employee')
pay_obj=self.pool.get('salary.payment.line')
if this.dept_id:
cr.execute("select emp.id from hr_employee as emp left join resource_resource "\
"as res on (emp.resource_id=res.id) where emp.department_id = "\
"'"+str(this.dept_id.id)+"' and emp.department_id is not null and "\
"res.active=True and emp.joining_date <'"+str(join_date)+"' order by emp.department_id, (substring(emp.sinid, '^[0-9]+'))::int ,substring(emp.sinid, '[^0-9_].*$')")
temp = cr.fetchall()
for data in temp:
if len(data)>0 and data[0] != None:
emp_ids.append(data[0])
else:
cr.execute("select emp.id from hr_employee as emp left join resource_resource "\
"as res on (emp.resource_id=res.id) where res.active=True and emp.joining_date < '"+str(join_date)+"' order by emp.department_id, "\
"(substring(emp.sinid, '^[0-9]+'))::int ,substring(emp.sinid, '[^0-9_].*$')")
temp = cr.fetchall()
for data in temp:
if len(data)>0 and data[0] != None:
emp_ids.append(data[0])
holiday_obj = self.pool.get('holiday.list')
if int(month) in [1,3,5,7,8,10,12]:
month = 31
if int(month) in [4,6,9,11]:
month = 30
if int(month) in [2]:
if int(year) % 4 == 0:
month = 29
else:
month = 28
off_day = working_day = 0
holiday_ids = holiday_obj.search(cr, uid, [('month','=',this.month),('year_id','=',year_id)])
for line in holiday_obj.browse(cr, uid, holiday_ids):
off_day = line.holiday
working_day = month - off_day
i=0
dept_dict = {}
grand = total = pay_total = pay_grand = budget = 0.0
pay_data = False
flag = True
for each in emp_obj.browse(cr, uid, emp_ids):
tolal_months = 0
if not each.joining_date:
continue
dt1 = datetime.strptime(each.joining_date,'%Y-%m-%d')
dt2 = datetime.strptime(time.strftime(DEFAULT_SERVER_DATE_FORMAT),'%Y-%m-%d')
start_month=dt1.month
end_months=(dt2.year-dt1.year)*12 + dt2.month+1
dates=[datetime(year=yr, month=mn, day=1) for (yr, mn) in (
((m - 1) / 12 + dt1.year, (m - 1) % 12 + 1) for m in range(start_month, end_months)
)]
for val in dates:
tolal_months += 1
pay_ids = pay_obj.search(cr, uid, [('employee_id','=',each.id),('month','=',this.month),('year_id','=',this.year_id.id),('salary_type','=','Salary')])
if pay_ids:
pay_data = pay_obj.browse(cr, uid, pay_ids[0])
i+=1
if dept_dict.has_key(str(each.department_id.id)):
if each.department_id:
salary = 0.0
dept = '[' + str(each.department_id.dept_code) +'] '+ str(each.department_id.name)
ws.write(i,0, dept)
ws.write(i,1, each.department_id.manager_id and each.department_id.manager_id.name or '')
ws.write(i,2, each.department_id.manager_id.parent_id and each.department_id.manager_id.parent_id.name or '')
name = '[' + str(each.sinid) +'] '+ str(each.name)
ws.write(i,3, name)
ws.write(i,4, each.designation_id and each.designation_id.name or '')
ws.write(i,5, pay_data.days or '0.0')
ws.write(i,6, pay_data.over_time)
ws.write(i,7, str(tolal_months) + ' Month')
if pay_data.employee_id.daily:
salary = pay_data.basic * working_day
else:
salary = pay_data.basic
ws.write(i,8, salary)
if pay_data and pay_data.employee_id.id == each.id:
ws.write(i,9,pay_data.total_amount or 0.0)
else:
ws.write(i,9, 0.0)
total += salary
grand += salary
if pay_data and pay_data.employee_id.id == each.id:
pay_total += pay_data.total_amount or 0.0
pay_grand += pay_data.total_amount or 0.0
else:
pay_total += 0.0
pay_grand += 0.0
elif not each.department_id:
salary = 0.0
if flag:
ws.write(i,0, 'Allocated Budget',style_header)
ws.write(i,1, 0.0,style_header)
ws.write(i,2, 'Total',style_header)
ws.write(i,3, '',style_header)
ws.write(i,4, '',style_header)
ws.write(i,5, '',style_header)
ws.write(i,6, '',style_header)
ws.write(i,7, '',style_header)
ws.write(i,8, total,style_header)
if pay_total:
ws.write(i,9,pay_total,style_header)
else:
ws.write(i,9, 0.0,style_header)
if budget:
diff = pay_total - float(budget)
else:
diff = pay_total
ws.write(i,10, diff,style_header1)
flag = False
i += 2
total = pay_total = budget = 0.0
name = '[' + str(each.sinid) +'] '+ str(each.name)
ws.write(i,0, 'X Department')
ws.write(i,1,'X Reporting Officer')
ws.write(i,2,'X Reporting Officer')
ws.write(i,3, name)
ws.write(i,4, each.designation_id and each.designation_id.name or '')
ws.write(i,5, pay_data.days or '0.0')
ws.write(i,6, pay_data.over_time)
ws.write(i,7, str(tolal_months) + ' Month')
if pay_data.employee_id.daily:
salary = pay_data.basic * working_day
else:
salary = pay_data.basic
ws.write(i,8, salary)
if pay_data and pay_data.employee_id.id == each.id:
ws.write(i,9,pay_data.total_amount or 0.0)
else:
ws.write(i,9, 0.0)
total += salary
grand += salary
if pay_data and pay_data.employee_id.id == each.id:
pay_total += pay_data.total_amount or 0.0
pay_grand += pay_data.total_amount or 0.0
else:
pay_total += 0.0
pay_grand += 0.0
else:
dept_dict[str(each.department_id.id)] = ''
if i != 1:
ws.write(i,0, 'Allocated Budget',style_header)
if budget:
ws.write(i,1, budget,style_header)
else:
budget = 0.0
ws.write(i,1,budget,style_header)
ws.write(i,2, 'Total',style_header)
ws.write(i,3, '',style_header)
ws.write(i,4, '',style_header)
ws.write(i,5, '',style_header)
ws.write(i,6, '',style_header)
ws.write(i,7, '',style_header)
ws.write(i,8, total,style_header)
if pay_total:
ws.write(i,9,pay_total,style_header)
else:
ws.write(i,9, 0.0,style_header)
if budget:
diff = pay_total - float(budget)
else:
diff = pay_total
ws.write(i,10, diff,style_header1)
i += 2
total = pay_total = budget = 0.0
if each.department_id:
salary = 0.0
budget = each.department_id.dept_budget
dept = '[' + str(each.department_id.dept_code) +'] '+ str(each.department_id.name)
ws.write(i,0, dept)
ws.write(i,1, each.department_id.manager_id and each.department_id.manager_id.name or '')
ws.write(i,2, each.department_id.manager_id.parent_id and each.department_id.manager_id.parent_id.name or '')
name = '[' + str(each.sinid) +'] '+ str(each.name)
ws.write(i,3, name)
ws.write(i,4, each.designation_id and each.designation_id.name or '')
ws.write(i,5, pay_data.days)
ws.write(i,6, pay_data.over_time)
ws.write(i,7, str(tolal_months) + ' Month')
if pay_data.employee_id.daily:
salary = pay_data.basic * working_day
else:
salary = pay_data.basic
ws.write(i,8, salary)
if pay_data and pay_data.employee_id.id == each.id:
ws.write(i,9,pay_data.total_amount or 0.0)
else:
ws.write(i,9, 0.0)
total += salary
grand += salary
if pay_data and pay_data.employee_id.id == each.id:
pay_total += pay_data.total_amount or 0.0
pay_grand += pay_data.total_amount or 0.0
else:
pay_total += 0.0
pay_grand += 0.0
i += 1
ws.write(i+1,7, 'Total',style_header)
ws.write(i+1,8, total,style_header)
ws.write(i+3,7, 'Grand Total',style_header)
ws.write(i+3,8, grand,style_header)
ws.write(i+1,9, pay_total,style_header)
ws.write(i+3,9, pay_grand,style_header)
diff_pay = pay_total - total
diff_grand = pay_grand - grand
ws.write(i+1,10, diff_pay,style_header1)
ws.write(i+3,10, diff_grand,style_header1)
f = cStringIO.StringIO()
wb.save(f)
out=base64.encodestring(f.getvalue())
return self.write(cr, uid, ids, {'export_data':out, 'filename':'export.xls'}, context=context)
def makeXLS(self, id_relatorio, id_promocao=None):
"""
cria o arquivo xls
a var index é utilizada para
controlar em qual linha entrara o conteudo
"""
timename = str(util.dtnow('%d/%m/%Y %H:%M%S')).replace("/", "")
timename = timename.replace(":", "")
timename = "_" + timename.replace(" ", "")
site = getDadosSite(id_site=self.id_site,
request=self.request)
base = site["base_app"]
if not base.endswith("/"):
base = base + "/"
if int(id_relatorio) == 1:
titulo = 'Relacao_usuario_participacao'
elif int(id_relatorio) == 2:
titulo = 'Relacao_participantes_contemplados'
elif int(id_relatorio) == 4:
titulo = 'Dados_usuarios_por_promocao'
else:
titulo = 'Relacao_participantes_promocao'
saveplace = ("{0}/ns{1}/arquivos/tmp/"
"{2}{3}.xls").format(str(settings.PATH_FILES),
str(self.id_site),
titulo,
timename)
saveurl = "{0}tmp/{1}{2}.xls".format(
base, titulo, timename)
book = Workbook()
font = Font()
font.bold = True
style = XFStyle()
style.font = font
style0 = XFStyle()
style0.font = font
alignment = Alignment()
alignment.horz = Alignment.HORZ_CENTER
style0.alignment = alignment
pattern = Pattern()
pattern.pattern = Pattern.SOLID_PATTERN
pattern.pattern_fore_colour = 22
style0.pattern = pattern
index = 0
sheet = book.add_sheet('promocoes')
# sheet
if int(id_relatorio) == 1 or int(id_relatorio) == 3:
promocoes = self._getConteudo()
if int(id_relatorio) == 1:
campos_s1 = [{"nome": "Nome"},
{"email": "email"},
{"cpf": "CPF"},
{"quantidade de participacoes": u"Quantidade de participações"},
{"status":"status"},
{"bloqueio":"bloqueado"}]
sheet.write_merge(0, 0, 0, 5, u"Relação usuário participação por promoção", style0)
else:
campos_s1 = [{"nome":"Nome"},
{"email":"email"},
{"cpf":"CPF"},
{"telefone":"Telefone"},
{"endereco":u"Endereço"},
{"data_hora":u"Hora participação"}]
sheet.write_merge(0, 0, 0, 5, u"Dados de usuários por promoção", style0)
total_geral = 0
count = 0
for i in promocoes:
count_participacoes = self._getCountParticipacoes(i['id_conteudo'])
count += count_participacoes
index += 1
titulo = i['titulo']
sheet.write_merge(index, index, 0, 5, titulo.decode("latin1"), style0)
index += 1
for j in range(len(campos_s1)):
sheet.write(index, j, campos_s1[j].values()[0], style)
sheet.col(j).width = 30 * 256
cont = 0
if int(id_relatorio) == 1:
for y in self.execSql("select_usuarios_promocoes_all",
id_conteudo=int(i['id_conteudo'])):
sheet.write(index + 1,
0,
self.dec(y['nome']))
sheet.write(index + 1,
1,
self.dec(y['email']))
sheet.write(index + 1,
2,
self.dec(y['cpf']))
sheet.write(index + 1,
3,
self.dec(y['total']))
sheet.write(index + 1,
4,
self.dec(y['status']))
if y['bloqueio']:
bloqueio = 'Sim'
else:
bloqueio = 'Não'
sheet.write(index + 1,
5,
self.dec(bloqueio))
index += 1
cont += 1
if int(id_relatorio) == 3:
for y in self.execSql("select_usuarios_promocoes_all2",
id_conteudo=int(i['id_conteudo'])):
endereco = "{0}, {1}, {2}, {3}, {4}". format(y['endereco'],
y['numero'],
y['bairro'],
y['estado'],
y['cep'])
sheet.write(index + 1,
0,
self.dec(y['nome']))
sheet.write(index + 1,
1,
self.dec(y['email']))
sheet.write(index + 1,
2,
self.dec(y['cpf']))
sheet.write(index + 1,
3,
self.dec(y['telefone']))
sheet.write(index + 1,
4,
self.dec(endereco))
sheet.write(index + 1,
5,
y['dhora_participacao'])
index += 1
cont += 1
if cont:
index += 2
sheet.write(index, 0, 'Total', style)
sheet.write(index,
1,
cont)
total_geral += cont
index += 1
sheet.write(index, 0, u'Total Participações', style)
sheet.write(index,
1,
count_participacoes)
else:
index += 2
sheet.write(index, 0, 'Total', style)
sheet.write(index,
1,
cont)
index += 2
index += 6
sheet.write_merge(index, index, 0, 1, "Total Geral", style0)
index += 1
sheet.write(index, 0, "Total de Participantes", style)
sheet.write(index, 1, self.dec(total_geral), style)
index += 1
sheet.write(index, 0, u"Total de Participações Geral", style)
sheet.write(index, 1, self.dec(count), style)
else:
if int(id_relatorio) == 4:
cont = 0
titulo_promo = self._getConteudo(id_promocao)['titulo']
sheet.write_merge(0, 0, 0, 5, u"Relação de partcipantes da promoção " + titulo_promo.decode('latin1'), style0)
campos_s1 = [{"nome":"Nome"},
{"email":"email"},
{"cpf":"CPF"},
{"telefone":"Telefone"},
{"endereco":u"Endereço"},
{"status":"Status"}
]
saveplace = ("{0}/ns{1}/arquivos/tmp/{2}{3}.xls").format(str(settings.PATH_FILES),
str(self.id_site),
unicode(titulo + '_' + titulo_promo, errors='ignore'),
timename)
saveurl = "{0}tmp/{1}{2}.xls".format(base, unicode(titulo + '_' + titulo_promo, errors='ignore'), timename)
index += 1
for j in range(len(campos_s1)):
sheet.write(index, j, campos_s1[j].values()[0], style)
sheet.col(j).width = 30 * 256
for i in self.execSql("select_usuarios_promocoes_all2",
id_conteudo=int(id_promocao)):
endereco = "{0}, {1}, {2}, {3}, {4}". format(i['endereco'],
i['numero'],
i['bairro'],
i['estado'],
i['cep'])
sheet.write(index + 1,
0,
self.dec(i['nome']))
sheet.write(index + 1,
1,
self.dec(i['email']))
sheet.write(index + 1,
2,
self.dec(i['cpf']))
sheet.write(index + 1,
3,
self.dec(i['telefone']))
sheet.write(index + 1,
4,
self.dec(endereco))
sheet.write(index + 1,
5,
self.dec(i['status']))
index += 1
cont += 1
else:
cont = 0
sheet.write_merge(0, 0, 0, 5, u"Relação de usuários contemplados", style0)
campos_s1 = [{"nome":"Nome"},
{"email":"email"},
{"cpf":"CPF"},
{"telefone":"Telefone"},
{"endereco":u"Endereço"},
{"Contemplações":u"Contemplações"}]
index += 1
for j in range(len(campos_s1)):
sheet.write(index, j, campos_s1[j].values()[0], style)
sheet.col(j).width = 30 * 256
for i in self.execSql("select_contemplados"):
endereco = "{0}, {1}, {2}, {3}, {4}". format(i['endereco'],
i['numero'],
i['bairro'],
i['estado'],
i['cep'])
sheet.write(index + 1,
0,
self.dec(i['nome']))
sheet.write(index + 1,
1,
self.dec(i['email']))
sheet.write(index + 1,
2,
self.dec(i['cpf']))
sheet.write(index + 1,
3,
self.dec(i['telefone']))
sheet.write(index + 1,
4,
self.dec(endereco))
sheet.write(index + 1,
5,
self.dec(i['total']))
index += 1
cont += 1
index += 6
sheet.write(index, 0, u"Total de usuários", style)
sheet.write(index, 1, self.dec(cont), style)
book.save(saveplace)
return saveurl
station = station.strip()
if station in stations:
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")
def generate_report(root, filename, attend_staff, absent_staff, meeting_direct, record_staff, reports, issues):
book = Workbook(encoding='utf-8')
sheet1 = book.add_sheet('Fota Security Knox Week Report')
line_counter = 0
today = datetime.date.today().strftime('%Y/%m/%d')
# header
head_str = [
'Meeting Name', 'SW 3 Group Part 5 Fota/ Security/Knox/Encription TG WeekReport',
'', '',
'Meeting Date', today,
'Meeting Address', '8F Meeting Room',
'', '',
'Attend Staff', attend_staff,
'Absent Staff', absent_staff,
'', '',
'Meeting Direct', meeting_direct,
'Record Staff', record_staff,
]
for i in range(len(head_str) / 2):
sheet1.row(i).write(0, head_str[2 * i])
sheet1.row(i).write(1, head_str[2 * i + 1])
line_counter = len(head_str) / 2 + 1
# font bold
font = Font()
font.bold = True
# pattern yellow
pattern_yellow = Pattern()
pattern_yellow.pattern = Pattern.SOLID_PATTERN
pattern_yellow.pattern_fore_colour = 0x0D # yellow
# pattern gray
pattern_gray = Pattern()
pattern_gray.pattern = Pattern.SOLID_PATTERN
pattern_gray.pattern_fore_colour = 0x17 # gray
# borders thin
borders = Borders()
borders.left = Borders.THIN
borders.right = Borders.THIN
borders.top = Borders.THIN
borders.bottom = Borders.THIN
# alignment horizontal center
alig_hc = Alignment()
# alig.horizontal = Alignment.HORZ_CENTER #no effect, why? f**k!!!
alig_hc.horz = Alignment.HORZ_CENTER
# title style
style_title = XFStyle()
style_title.font = font
style_title.pattern = pattern_yellow
style_title.alignment = alig_hc
sheet1.write_merge(
line_counter, line_counter, 0, 3, 'summary', style_title)
line_counter += 1
# table header style
sytle_tb_header = XFStyle()
sytle_tb_header.font = font
sytle_tb_header.pattern = pattern_gray
sytle_tb_header.borders = borders
sytle_tb_header.alignment = alig_hc
sheet1.row(line_counter).write(0, 'Member', sytle_tb_header)
sheet1.row(line_counter).write(1, 'Week Jobs', sytle_tb_header)
sheet1.row(line_counter).write(2, 'Risk', sytle_tb_header)
sheet1.row(line_counter).write(3, 'Next Week Plan', sytle_tb_header)
line_counter += 1
# content
# for i in range(line_counter,11+line_counter):
i = line_counter
for report in reports:
# alignment
alig = Alignment()
alig.horz = Alignment.HORZ_CENTER
alig.vert = Alignment.VERT_CENTER
alig.wrap = 1
# alignment2
alig2 = Alignment()
alig2.vert = Alignment.VERT_CENTER
alig2.wrap = 1
# borders
borders = Borders()
borders.left = Borders.THIN
borders.right = Borders.THIN
borders.top = Borders.THIN
borders.bottom = Borders.THIN
# colors
pattern = Pattern()
pattern.pattern = Pattern.SOLID_PATTERN
pattern.pattern_fore_colour = 0x2F
style_content = XFStyle()
style_content.alignment = alig
style_content.borders = borders
style_content2 = XFStyle()
style_content2.alignment = alig2
style_content2.borders = borders
style_content2.pattern = pattern
style_content3 = XFStyle()
style_content3.alignment = alig2
style_content3.borders = borders
sheet1.row(i).write(0, report.who, style_content)
sheet1.row(i).write(1, report.job, style_content2)
sheet1.row(i).write(2, report.risk, style_content2)
sheet1.row(i).write(3, report.plan, style_content3)
sheet1.row(i).height_mismatch = True
sheet1.row(i).height = 1500
i += 1
line_counter += len(reports)
line_counter += 1
sheet1.write_merge(
line_counter, line_counter, 0, 3, 'Main Issues List', style_title)
line_counter += 1
sheet1.row(line_counter).write(0, 'Items', sytle_tb_header)
sheet1.write_merge(
line_counter, line_counter, 1, 2, 'Deatail', sytle_tb_header)
sheet1.row(line_counter).write(3, 'Status', sytle_tb_header)
line_counter += 1
sheet1.col(0).width = 256 * 16
sheet1.col(1).width = 256 * 50
sheet1.col(2).width = 256 * 31
sheet1.col(3).width = 256 * 46
book.save(root + '/' + filename)
def exportToXls(self):
# opening file dialog
fileName = QFileDialog.getSaveFileName(self, "Save as", RES, "Microsoft Excel Spreadsheet (*.xls)")
if fileName.count() > 0:
try:
COLUMN_WIDTH = 3000
alignment = Alignment()
alignment.horizontal = Alignment.HORZ_CENTER
alignment.vertical = Alignment.VERT_CENTER
borders = Borders()
borders.left = Borders.THIN
borders.right = Borders.THIN
borders.top = Borders.THIN
borders.bottom = Borders.THIN
style = XFStyle()
style.alignment = alignment
style.borders = borders
font = Font()
font.bold = True
headerStyle = XFStyle()
headerStyle.font = font
separate = Borders()
separate.left = Borders.THIN
separate.right = Borders.DOUBLE
separate.top = Borders.THIN
separate.bottom = Borders.THIN
separateStyle = XFStyle()
separateStyle.borders = separate
book = Workbook(encoding="utf-8")
# modelling data
if self.exportStepByStep.isChecked():
dec_sheet = book.add_sheet("Data decomposition")
# decomposition data
if self.exportData.isChecked():
# initial data
column = 0
row = 0
dec_sheet.write(row, column, "Time series", headerStyle)
dec_sheet.col(column).width = COLUMN_WIDTH
row += 1
for item in self.parentWidget().currentDataSet[0]:
dec_sheet.write(row, column, item, separateStyle)
row += 1
# decomposition
for lvl in self.parentWidget().wCoefficients:
row = 0
column += 1
dec_sheet.write(row, column, "Level" + str(column - 1), headerStyle)
dec_sheet.col(column).width = COLUMN_WIDTH
row += 1
for item in lvl:
dec_sheet.write(row, column, item, style)
row += 1
# decomposition graphs
if self.exportGraph.isChecked():
pass
levels_sheet = book.add_sheet("Multiscale forecast")
# levels data
if self.exportData.isChecked():
column = 0
for lvl in self.parentWidget().processedWCoeffs:
row = 0
levels_sheet.write(row, column, "Level" + str(column), headerStyle)
levels_sheet.col(column).width = COLUMN_WIDTH
row += 1
for item in lvl:
levels_sheet.write(row, column, float(item), style)
row += 1
column += 1
if self.exportForecast.isChecked():
result_sheet = book.add_sheet("Results")
if self.exportData.isChecked():
# initial
column = 0
row = 0
result_sheet.write(row, column, "Initial data", headerStyle)
result_sheet.col(column).width = COLUMN_WIDTH
row += 1
for item in self.parentWidget().currentDataSet[0]:
result_sheet.write(row, column, item, separateStyle)
row += 1
# forecast
row = 0
column += 1
result_sheet.write(row, column, "Forecast", headerStyle)
result_sheet.col(column).width = COLUMN_WIDTH
row += 1
for item in self.parentWidget().resultingForecast:
result_sheet.write(row, column, item, style)
row += 1
if self.exportGraph.isChecked():
row = 0
column = 2
self.parentWidget().resultingGraph.saveFigure("forecast", format="bmp")
result_sheet.insert_bitmap(RES + TEMP + "forecast.bmp", row, column)
# saving xls
try:
book.save(unicode(fileName))
self.parentWidget().messageInfo.showInfo("Saved as " + unicode(fileName))
except Exception:
self.parentWidget().messageInfo.showInfo("Could not save as " + unicode(fileName), True)
except Exception, e:
self.parentWidget().messageInfo.showInfo("Not enough data.", True)
def pf_upload_report(self, cr, uid, ids, context=None):
f_name = ''
d_name = ''
wb = Workbook()
ws = wb.add_sheet('CONTRACTOR PF UPLOAD')
fnt1 = Font()
fnt1.name = 'Arial'
fnt1.height = 300
fnt1.bold = True
align_content1 = Alignment()
align_content1.horz = Alignment.HORZ_CENTER
borders1 = Borders()
borders1.left = 0x02
borders1.right = 0x02
borders1.top = 0x02
borders1.bottom = 0x02
align1 = Alignment()
align1.horz = Alignment.HORZ_CENTER
align1.vert = Alignment.VERT_CENTER
pattern1 = Pattern()
pattern1.pattern1 = Pattern.SOLID_PATTERN
pattern1.pattern1_fore_colour = 0x17
style_header1 = XFStyle()
style_header1.font = fnt1
style_header1.pattern = pattern1
style_header1.borders = borders1
style_header1.alignment = align1
fnt2 = Font()
fnt2.name = 'Arial'
fnt2.height = 250
fnt2.bold = False
align_content2 = Alignment()
align_content2.horz = Alignment.HORZ_CENTER
borders2 = Borders()
borders2.left = 0x02
borders2.right = 0x02
borders2.top = 0x02
borders2.bottom = 0x02
align2 = Alignment()
align2.horz = Alignment.HORZ_CENTER
align2.vert = Alignment.VERT_CENTER
pattern2 = Pattern()
pattern2.pattern2 = Pattern.SOLID_PATTERN
pattern2.pattern2_fore_colour = 0x09
style_header2 = XFStyle()
style_header2.font = fnt2
style_header2.pattern = pattern2
style_header2.borders = borders2
style_header2.alignment = align2
fnt3 = Font()
fnt3.name = 'Arial'
fnt3.height = 275
fnt3.bold = False
align_content3 = Alignment()
align_content3.horz = Alignment.HORZ_CENTER
borders3 = Borders()
borders3.left = 0x02
borders3.right = 0x02
borders3.top = 0x02
borders3.bottom = 0x02
align3 = Alignment()
align3.horz = Alignment.HORZ_CENTER
align3.vert = Alignment.VERT_CENTER
pattern3 = Pattern()
pattern3.pattern3 = Pattern.SOLID_PATTERN
pattern3.pattern3_fore_colour = 0x09
style_header3 = XFStyle()
style_header3.font = fnt3
style_header3.pattern = pattern3
style_header3.borders = borders3
style_header3.alignment = align3
fnt = Font()
fnt.name = 'Arial'
fnt.height = 275
content_fnt = Font()
content_fnt.name = 'Arial'
content_fnt.height = 150
align_content = Alignment()
align_content.horz = Alignment.HORZ_CENTER
borders = Borders()
borders.left = 0x02
borders.right = 0x02
borders.top = 0x02
borders.bottom = 0x02
align = Alignment()
align.horz = Alignment.HORZ_CENTER
align.vert = Alignment.VERT_CENTER
pattern = Pattern()
pattern.pattern = Pattern.SOLID_PATTERN
pattern.pattern_fore_colour = 0x16
style_header = XFStyle()
style_header.font = fnt
style_header.pattern = pattern
style_header.borders = borders
style_header.alignment = align
fnt5 = Font()
fnt5.name = 'Arial'
fnt5.height = 275
content_fnt5 = Font()
content_fnt5.name = 'Arial'
content_fnt5.height = 150
align_content5 = Alignment()
# align_content5.horz= Alignment.HORZ_JUSTIFIED
borders5 = Borders()
borders5.left = 0x02
borders5.right = 0x02
borders5.top = 0x02
borders5.bottom = 0x02
align5 = Alignment()
# align5.horz = Alignment.HORZ_JUSTIFIED
align5.vert = Alignment.VERT_JUSTIFIED
pattern5 = Pattern()
pattern5.pattern = Pattern.SOLID_PATTERN
pattern5.pattern_fore_colour = 0x16
style_header5 = XFStyle()
style_header5.font = fnt5
style_header5.pattern = pattern5
style_header5.borders = borders5
style_header5.alignment = align5
ws.row(0).height = 1500
ws.col(0).width = 6500
ws.col(1).width = 5000
ws.col(2).width = 10000
ws.col(3).width = 4000
ws.col(4).width = 4000
ws.col(5).width = 4500
ws.col(6).width = 4000
ws.col(7).width = 5500
ws.col(8).width = 4000
ws.col(9).width = 4500
ws.col(10).width = 4000
ws.col(11).width = 4000
ws.write(0, 0, 'Employee Pay Code', style_header)
ws.write(0, 1, 'UAN Number', style_header)
ws.write(0, 2, 'Member Name', style_header)
ws.write(0, 3, 'Gross Wages', style_header)
ws.write(0, 4, 'EPF Wages', style_header)
ws.write(0, 5, 'EPS Wages', style_header)
ws.write(0, 6, 'EDLI Wages', style_header)
ws.write(0, 7, 'EE Share', style_header)
ws.write(0, 8, 'EPS Contribution', style_header)
ws.write(0, 9, 'ER Share', style_header)
ws.write(0, 10, 'NCP Days', style_header)
ws.write(0, 11, 'Refund', style_header)
this = self.browse(cr, uid, ids[0], context=context)
month = this.month.month
company_id = this.company_id.id
year = this.month.year_id.id
year_name = this.month.year_id.name
partner_id = this.partner_id.id
emp_obj = self.pool.get('hr.employee')
# pf_no=' '
emp_name = ' '
epf_cont = 0
eps_cont = 0
calc_eps_epf = 0
diff_eps_epf = 0
full_date_month = []
total_epf_wages = 0
total_epf_cont = 0
total_eps_cont = 0
total_diff_calc = 0
total_diff_diff = 0
total_gross = 0
holiday_list = []
inact_list = []
inact_list1 = []
hr_list = []
i = 1
val_zero = 0
# epf_date_start=''
if len(str(month)) == 1:
start_date = str(year_name) + '-' + '0' + str(month) + '-' + '01'
month_val = '0' + str(month)
else:
start_date = str(year_name) + '-' + str(month) + '-' + '01'
month_val = str(month)
if int(this.month.month) in [1, 3, 5, 7, 8, 10, 12]:
month_tup = 31
if int(this.month.month) in [4, 6, 9, 11]:
month_tup = 30
if int(this.month.month) in [2]:
if int(this.month.year_id.name) % 4 == 0:
month_tup = 29
else:
month_tup = 28
month_val = int(month_val)
# month_tup = calendar.monthrange(year,month_val)
end_date = str(year_name) + '-' + str(month_val) + '-' + str(month_tup)
# month_val=int(month_val)
# month_tup = calendar.monthrange(year,month_val)
# end_date = str(year_name) + '-' + str(month_val) + '-' + str(month_tup[1])
for month_date in rrule.rrule(
rrule.DAILY,
dtstart=datetime.strptime(start_date, '%Y-%m-%d'),
until=datetime.strptime(end_date, '%Y-%m-%d')):
month_date = datetime.strftime(month_date, "%Y-%m-%d")
full_date_month.append(month_date)
if this.company_id:
list_ids = emp_obj.search(cr, uid,
[('active', '=', True),
('company_id', '=', company_id),
('epf_tick', '=', True),
('type', '=', 'Contractor')])
inact_ids = emp_obj.search(cr, uid,
[('active', '=', False),
('company_id', '=', company_id),
('epf_tick', '=', True),
('epf_end_date', '>=', start_date),
('epf_end_date', '<=', end_date),
('type', '=', 'Contractor')])
elif this.partner_id:
list_ids = emp_obj.search(cr, uid,
[('active', '=', True),
('partner_id', '=', partner_id),
('epf_tick', '=', True),
('type', '=', 'Contractor')])
inact_ids = emp_obj.search(cr, uid,
[('active', '=', False),
('partner_id', '=', partner_id),
('epf_tick', '=', True),
('epf_end_date', '>=', start_date),
('epf_end_date', '<=', end_date),
('type', '=', 'Contractor')])
if len(list_ids) == 0:
raise osv.except_osv(('Warning !'), ("Record Not Found !!!"))
if inact_ids:
for val in inact_ids:
inact_emp_browse = emp_obj.browse(cr, uid, val)
if inact_emp_browse:
inact_id = val
inact_emp_name = inact_emp_browse.name
inact_emp_sinid = inact_emp_browse.sinid
inact_days_amount = 0
tup = (inact_id, inact_emp_name, inact_days_amount,
inact_emp_sinid)
inact_list.append(tup)
inact_list1.append(val)
if len(list_ids) == 1:
query = "select spl.employee_id,spl.employee_name,spl.gross,spl.sinid,spl.epf,spl.epf1,spl.epf2,spl.days_amount,spl.other_salary_amount from salary_payment_line as spl where spl.employee_id = '" + str(
list_ids[0]) + "' and spl.month='" + str(
month) + "' and spl.year_id='" + str(
year) + "' and spl.epf <> 0.00 order by spl.sinid "
cr.execute(query)
temp = cr.fetchall()
query1 = "select spl.employee_id from salary_payment_line as spl where spl.employee_id = '" + str(
list_ids[0]) + "' and spl.month='" + str(
month) + "' and spl.year_id='" + str(
year) + "' and spl.epf <> 0.00 order by spl.sinid "
cr.execute(query1)
temp1 = cr.fetchall()
else:
query = "select spl.employee_id,spl.employee_name,spl.gross,spl.sinid,spl.epf,spl.epf1,spl.epf2,spl.days_amount,spl.other_salary_amount from salary_payment_line as spl where spl.employee_id in " + str(
tuple(list_ids)) + " and spl.month='" + str(
month) + "' and spl.year_id='" + str(
year) + "' and spl.epf <> 0.00 order by spl.sinid "
cr.execute(query)
temp = cr.fetchall()
query1 = "select spl.employee_id from salary_payment_line as spl where spl.employee_id in " + str(
tuple(list_ids)) + " and spl.month='" + str(
month) + "' and spl.year_id='" + str(
year) + "' and spl.epf <> 0.00 order by spl.sinid "
cr.execute(query1)
temp1 = cr.fetchall()
if not temp:
raise osv.except_osv(_('Warning !'), _("Record Not Found !!!"))
temp_add_value1 = temp1 + inact_list1
emp_list = emp_obj.search(cr, uid, [('id', 'not in', temp_add_value1),
('active', '=', True),
('company_id', '=', company_id),
('epf_tick', '=', True),
('doj', '<=', end_date),
('type', '=', 'Contractor')])
if emp_list:
for valll in emp_list:
emp_list_ids = emp_obj.browse(cr, uid, valll)
hr_id = valll
hr_name = emp_list_ids.name
hr_sinid = emp_list_ids.sinid
hr_days_amount = 0
tup = (hr_id, hr_name, hr_days_amount, hr_sinid)
hr_list.append(tup)
if temp:
temp_add_value = temp + inact_list + hr_list
temp_add_value = sorted(temp_add_value, key=lambda x: x[3])
for val in temp_add_value:
epf_cont = 0
eps_cont = 0
calc_eps_epf = 0
epf_gross_wages = 0
ws.row(i).height = 500
emp_browse = emp_obj.browse(cr, uid, val[0])
uan_number = emp_browse.uan
if val[2]:
gross = val[7] + val[8]
else:
gross = 0
if len(val) > 5:
epf_cont = val[4]
eps_cont = val[5]
calc_eps_epf = val[6]
epf_gross_wages = round(val[2], 0)
diff_eps_epf = (epf_cont - eps_cont)
date_of_join_epf = emp_browse.epf_start_date
date_of_exit_epf = emp_browse.epf_end_date
total_epf_wages += val[2]
total_epf_cont += epf_cont
total_eps_cont += eps_cont
total_diff_calc += calc_eps_epf
total_diff_diff += diff_eps_epf
total_gross += gross
ws.write(i, 0, (val[3]), style_header2)
ws.write(i, 1, uan_number, style_header2)
ws.write(i, 2, (val[1]), style_header2)
ws.write(i, 3, gross, style_header2)
ws.write(i, 4, epf_gross_wages, style_header2)
ws.write(i, 5, epf_gross_wages, style_header2)
ws.write(i, 6, epf_gross_wages, style_header2)
ws.write(i, 7, epf_cont, style_header2)
ws.write(i, 8, eps_cont, style_header2)
ws.write(i, 9, calc_eps_epf, style_header2)
ws.write(i, 10, val_zero, style_header2)
ws.write(i, 11, val_zero, style_header2)
i = i + 1
ws.row(i + 1).height = 500
ws.write(i + 1, 2, 'TOTAL', style_header1)
ws.write(i + 1, 3, total_gross, style_header1)
ws.write(i + 1, 4, total_epf_wages, style_header1)
ws.write(i + 1, 5, total_epf_wages, style_header1)
ws.write(i + 1, 6, total_epf_wages, style_header1)
ws.write(i + 1, 7, total_epf_cont, style_header1)
ws.write(i + 1, 8, total_eps_cont, style_header1)
ws.write(i + 1, 9, total_diff_calc, style_header1)
f = cStringIO.StringIO()
wb.save(f)
out = base64.encodestring(f.getvalue())
pf_upload_report = self.write(
cr,
uid,
ids, {
'export_data': out,
'filename': 'contractor_pf_upload.xls'
},
context=context)
return pf_upload_report
def 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)
# Copyright: (c) calebma 2016 # Licence: <your licence> #------------------------------------------------------------------------------- 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'
from xlwt import XFStyle, Alignment left = XFStyle() left_alignment = Alignment() left_alignment.horz = Alignment.HORZ_LEFT left_alignment.dire = Alignment.DIRECTION_RL left.alignment = left_alignment right = XFStyle() right_alignment = Alignment() right_alignment.horz = Alignment.HORZ_RIGHT right_alignment.dire = Alignment.DIRECTION_RL right.alignment = right_alignment center = XFStyle() center_alignment = Alignment() center_alignment.horz = Alignment.HORZ_CENTER center_alignment.direction = Alignment.DIRECTION_RL center.alignment = center_alignment
def test(theseargs):
parser = argparse.ArgumentParser(description='Download bulk information from Ofgem to produce an Excel spreadsheet')
parser.add_argument('--start', action='store', required=True, help='Period to start from (MMM-YYYY)')
parser.add_argument('--end', action='store', required=True, help='Period to finish on (MMM-YYYY)')
parser.add_argument('--scheme', action='store', default='RO', help='Scheme to get certificates for')
parser.add_argument('--filename', action='store', default='certificates.xls', help='Filename to export to')
parser.add_argument('--name', action='store', default=None, help='Part of a name of generation station (or name fragments for several stations, separated by commas)')
args = parser.parse_args(args=theseargs)
(start_year, start_month) = get_period(args.start)
(end_year, end_month) = get_period(args.end)
if not args.filename.endswith('.xls'):
args.filename += '.xls'
periods = []
for yy in range(start_year, end_year + 1):
mm = start_month if start_year == yy else 1
mm2 = end_month if end_year == yy else 12
for m in range(mm, mm2+1):
periods.append(date(yy,m,1).strftime("%b-%Y"))
station = args.name
if station is None:
stations = []
while (True):
station = input("Enter a station name (or blank to finish)")
if station.strip() == '':
break
if ',' in station:
for s in station.strip().split(','):
s = s.strip()
if s in stations:
continue
stations.append(s)
else:
station = station.strip()
if station in stations:
continue
stations.append(station)
else:
stations = station.strip().split(',')
if len(stations) == 0:
print("No stations to process. Exiting...")
sys.exit(0)
wb = Workbook()
ws = wb.add_sheet('Certificate Data', cell_overwrite_ok=True)
ws.write(PERIOD_START - 1,0,"Period")
for i in range(0, len(periods)):
ws.write(PERIOD_START + i, 0, periods[i])
al = Alignment()
al.horz = Alignment.HORZ_CENTER
al.vert = Alignment.VERT_CENTER
title_style = XFStyle()
title_style.alignment = al
print("\nTotal of %d stations to process\n" % len(stations))
for i in range(0, len(stations)):
s = stations[i]
col = 1 + 5 * i
print(" "+ s)
# add headers
ws.write(PERIOD_START - 1, col, "Installed Capacity")
ws.write(PERIOD_START - 1, col + 1, "RO Certificates")
ws.write(PERIOD_START - 1, col + 2, "RO Factor")
ws.write(PERIOD_START - 1, col + 3, "REGO Certificates")
ws.write(PERIOD_START - 1, col + 4, "REGO Factor")
capacity = {}
for scheme in ['RO','REGO']:
offset = 1 if scheme == 'RO' else 3
ss = StationSearch()
ss.filter_scheme(scheme)
ss.filter_name(s)
if not ss.get_data():
print("Unable to find any station with a name %s" % s)
continue
station = None
# if more than one generator has matched, use the first WIND one
if len(ss.stations) > 1:
print('Several stations found that match %s:' % s)
print(list(st.name for st in ss.stations))
print('The first wind station will be selected')
for st in ss.stations:
if 'wind' in st.technology.lower():
station = st
break
else:
station = ss.stations[0]
if station is None:
print("Unable to get station data for '%s'" % s)
continue
# Write name
ws.write_merge(PERIOD_START - 4, PERIOD_START - 4, col, col + 4,
station.name, title_style)
# add accreditation #
if scheme == 'RO':
ws.write_merge(PERIOD_START - 2, PERIOD_START - 2, col, col + 4,
'RO: ' + station.accreditation + ' [' + station.commission_dt.strftime("%d %b %Y") + ']',
title_style)
elif scheme == 'REGO':
ws.write_merge(PERIOD_START - 3, PERIOD_START - 3, col, col + 4,
'REGO: ' + station.accreditation + ' [' + station.commission_dt.strftime("%d %b %Y") + ']',
title_style)
cs = CertificateSearch()
cs.set_start_month(start_month)
cs.set_start_year(start_year)
cs.set_finish_month(end_month)
cs.set_finish_year(end_year)
cs.filter_accreditation(station.accreditation)
#cs.filter_scheme(scheme) # seems to work ok without this, and REGOs break with it
#cs.filter_status(['Issued','Redeemed','Expired']) # this doesn't work. And arguable whether expired should count
if not cs.get_data():
print("Unable to get any certificate data :-(")
continue
data = {}
for c in cs.certificates:
# print(c.as_string())
if c.status in ['Revoked','Retired','Expired']:
continue
row = periods.index(c.period) + PERIOD_START
if not c.period in capacity:
ws.write(row, col, c.capacity)
capacity[c.period] = True
data[c.period] = data.get(c.period,0) + c.certs
ws.write(row, col + offset + 1, c.factor)
for p,val in viewitems(data):
row = periods.index(p) + PERIOD_START
ws.write(row, col + offset, val)
print("\nComplete. Excel spreadsheet %s" % args.filename)
wb.save(args.filename)
def exportToXls(self):
# opening file dialog
fileName = QFileDialog.getSaveFileName(self,
'Save as', RES, 'Microsoft Excel Spreadsheet (*.xls)')
if fileName.count() > 0:
try:
COLUMN_WIDTH = 3000
alignment = Alignment()
alignment.horizontal = Alignment.HORZ_CENTER
alignment.vertical = Alignment.VERT_CENTER
borders = Borders()
borders.left = Borders.THIN
borders.right = Borders.THIN
borders.top = Borders.THIN
borders.bottom = Borders.THIN
style = XFStyle()
style.alignment = alignment
style.borders = borders
font = Font()
font.bold = True
headerStyle = XFStyle()
headerStyle.font = font
separate = Borders()
separate.left = Borders.THIN
separate.right = Borders.DOUBLE
separate.top = Borders.THIN
separate.bottom = Borders.THIN
separateStyle = XFStyle()
separateStyle.borders = separate
book = Workbook(encoding='utf-8')
# modelling data
dec_sheet = book.add_sheet('Data decomposition')
# decomposition data
# initial data
column = 0
row = 0
dec_sheet.write(row, column, 'Time series', headerStyle)
dec_sheet.col(column).width = COLUMN_WIDTH
row += 1
for item in self.data[0]:
dec_sheet.write(row, column, item, separateStyle)
row += 1
# decomposition
for lvl in self.wCoefficients:
row = 0
column += 1
dec_sheet.write(row, column, 'Level' + str(column - 1), headerStyle)
dec_sheet.col(column).width = COLUMN_WIDTH
row += 1
for item in lvl:
dec_sheet.write(row, column, item, style)
row += 1
# decomposition graphs
pass
levels_sheet = book.add_sheet('Multiscale forecast')
# levels data
column = 0
for lvl in self.forecast:
row = 0
levels_sheet.write(row, column, 'Level' + str(column), headerStyle)
levels_sheet.col(column).width = COLUMN_WIDTH
row += 1
for item in lvl[1]:
levels_sheet.write(row, column, float(item), style)
row += 1
column += 1
result_sheet = book.add_sheet('Results')
# initial
column = 0
row = 0
result_sheet.write(row, column, 'Initial data', headerStyle)
result_sheet.col(column).width = COLUMN_WIDTH
row += 1
for item in self.data[0]:
result_sheet.write(row, column, item, separateStyle)
row += 1
# forecast
row = 0
column += 1
result_sheet.write(row, column, 'Forecast', headerStyle)
result_sheet.col(column).width = COLUMN_WIDTH
row += 1
for item in self.inverseWT():
result_sheet.write(row, column, item, style)
row += 1
row = 0
column = 2
self.updateGraph()
self.plotResult.saveFigure('forecast', format='bmp')
result_sheet.insert_bitmap(RES + TEMP + 'forecast.bmp', row, column)
# saving xls
try:
book.save(unicode(fileName))
except Exception:
pass
except Exception, e:
pass
def toupiaoExcel(request):
subjectid = request.REQUEST.get('subjectid')
optionid = request.REQUEST.get('optionid')
subject = Subject.objects.get(pk=subjectid)
if subject.isNoName:
return HttpResponse(u'匿名投票不可导出')
filename=u'%s'%subject.title
if optionid:
option = Option.objects.get(pk=optionid)
if option.subject.pk!=subject.pk:
raise Http404
filename+=u'——%s'%option.content
response = HttpResponse(mimetype=u'application/ms-excel')
filenames = u'%s.xls'%filename
response['Content-Disposition'] = (u'attachment;filename=%s' % filenames).encode('utf-8')
import xlwt
from xlwt import Font, Alignment
style1 = xlwt.XFStyle()
font1 = Font()
font1.height = 260
font1.name = u'仿宋'
style1.font = font1
algn = Alignment()
algn.horz = Alignment.HORZ_LEFT
style1.alignment = algn
style1.font = font1
style0 = xlwt.XFStyle()
algn0 = Alignment()
algn0.horz = Alignment.HORZ_CENTER
font = Font()
font.height = 220
font.bold = False
font.name = u'仿宋'
style0.alignment = algn0
style0.font = font
wb = xlwt.Workbook()
ws = wb.add_sheet(u"人员名单", cell_overwrite_ok=True)
ws.header_str = filename
ws.footer_str =''
rownum = 0
num=1
query=Toupiao.objects.filter(subject=subject)
if optionid:
query=query.filter(options=option)
ws.write_merge(rownum,rownum,0,0,u'序号',style0)
ws.write_merge(rownum,rownum,1,1,u'部门',style0)
ws.write_merge(rownum,rownum,2,2,u'人员',style0)
rownum+=1
for toupiao in query:
ws.write_merge(rownum,rownum,0,0,num,style0)
ws.write_merge(rownum,rownum,1,1,unicode(getattr(toupiao.user.person,'depate',u'无')),style0)
ws.write_merge(rownum,rownum,2,2,toupiao.user.person.truename,style0)
rownum+=1
num+=1
else:
for opt in Option.objects.filter(subject=subject):
ws.write_merge(rownum,rownum,0,2,u'选项：%s'%opt.content,style1)
rownum+=1
ws.write_merge(rownum,rownum,0,0,u'序号',style0)
ws.write_merge(rownum,rownum,1,1,u'部门',style0)
ws.write_merge(rownum,rownum,2,2,u'人员',style0)
rownum+=1
for toupiao in query.filter(options=opt):
ws.write_merge(rownum,rownum,0,0,num,style0)
ws.write_merge(rownum,rownum,1,1,unicode(getattr(toupiao.user.person,'depate',u'无')),style0)
ws.write_merge(rownum,rownum,2,2,toupiao.user.person.truename,style0)
rownum+=1
num+=1
width=256*10
ws.col(0).width = width
ws.col(1).width = width*3
ws.col(2).width = width*2
wb.save(response)
return response
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
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)
add_station(s)
else:
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
def performance_register_report(self, cr, uid, ids, data, context=None):
obj = self.browse(cr, uid, ids)
emp_obj = self.pool.get('hr.employee')
f_name = ''
d_name = ''
wb = Workbook()
ws = wb.add_sheet('Contractor Payment Bonus')
total_salary = apr_salary = may_salary = june_salary = july_salary = aug_salary = sep_salary = oct_salary = nov_salary = dec_salary = jan_salary = feb_salary = mar_salary = 0
fnt1 = Font()
fnt1.name = 'Arial'
fnt1.height = 300
fnt1.bold = True
align_content1 = Alignment()
align_content1.horz = Alignment.HORZ_CENTER
borders1 = Borders()
borders1.left = 0x00
borders1.right = 0x00
borders1.top = 0x00
borders1.bottom = 0x00
align1 = Alignment()
align1.horz = Alignment.HORZ_CENTER
align1.vert = Alignment.VERT_CENTER
pattern1 = Pattern()
pattern1.pattern1 = Pattern.SOLID_PATTERN
pattern1.pattern1_fore_colour = 0x1F
style_header1 = XFStyle()
style_header1.font = fnt1
style_header1.pattern = pattern1
style_header1.borders = borders1
style_header1.alignment = align1
fnt2 = Font()
fnt2.name = 'Arial'
fnt2.height = 300
fnt2.bold = True
align_content2 = Alignment()
align_content2.horz = Alignment.HORZ_CENTER
borders2 = Borders()
borders2.left = 0x00
borders2.right = 0x00
borders2.top = 0x00
borders2.bottom = 0x00
align2 = Alignment()
align2.horz = Alignment.HORZ_CENTER
align2.vert = Alignment.VERT_CENTER
pattern2 = Pattern()
pattern2.pattern2 = Pattern.SOLID_PATTERN
pattern2.pattern2_fore_colour = 0x1F
style_header2 = XFStyle()
style_header2.font = fnt2
style_header2.pattern = pattern2
style_header2.borders = borders2
style_header2.alignment = align2
fnt3 = Font()
fnt3.name = 'Arial'
fnt3.height = 300
fnt3.bold = True
align_content3 = Alignment()
align_content3.horz = Alignment.HORZ_CENTER
borders3 = Borders()
borders3.left = 0x00
borders3.right = 0x00
borders3.top = 0x00
borders3.bottom = 0x00
align3 = Alignment()
align3.horz = Alignment.HORZ_CENTER
align3.vert = Alignment.VERT_CENTER
pattern3 = Pattern()
pattern3.pattern3 = Pattern.SOLID_PATTERN
pattern3.pattern3_fore_colour = 0x1F
style_header3 = XFStyle()
style_header3.font = fnt3
style_header3.pattern = pattern3
style_header3.borders = borders3
style_header3.alignment = align3
fnt = Font()
fnt.name = 'Arial'
fnt.height = 275
content_fnt = Font()
content_fnt.name = 'Arial'
content_fnt.height = 150
align_content = Alignment()
align_content.horz = Alignment.HORZ_CENTER
borders = Borders()
borders.left = 0x02
borders.right = 0x02
borders.top = 0x02
borders.bottom = 0x02
align = Alignment()
align.horz = Alignment.HORZ_CENTER
align.vert = Alignment.VERT_CENTER
pattern = Pattern()
pattern.pattern = Pattern.SOLID_PATTERN
pattern.pattern_fore_colour = 0x1F
style_header = XFStyle()
style_header.font = fnt
style_header.pattern = pattern
style_header.borders = borders
style_header.alignment = align
fnt5 = Font()
fnt5.name = 'Arial'
fnt5.height = 200
content_fnt5 = Font()
content_fnt5.name = 'Arial'
content_fnt5.height = 150
align_content5 = Alignment()
align_content5.horz = Alignment.HORZ_CENTER
borders5 = Borders()
borders5.left = 0x02
borders5.right = 0x02
borders5.top = 0x02
borders5.bottom = 0x02
align5 = Alignment()
align5.horz = Alignment.HORZ_CENTER
align5.vert = Alignment.VERT_CENTER
pattern5 = Pattern()
# pattern5.pattern = Pattern.SOLID_PATTERN
# pattern5.pattern_fore_colour = 0x1F
style_header5 = XFStyle()
style_header5.font = fnt5
style_header5.pattern = pattern5
style_header5.borders = borders5
style_header5.alignment = align5
if obj.partner_id:
get_name = obj.partner_id.name
elif obj.company_id:
get_name = obj.company_id.name + ' ' + obj.company_id.street + ' ' + ',' + obj.company_id.city + ' ' + '-' + obj.company_id.zip
else:
get_name = obj.employee_id.partner_id.name
ws.row(0).height = 500
ws.write_merge(0, 0, 0, 19, get_name, style_header1)
date1 = datetime.strptime(obj.from_date,
"%Y-%m-%d").timetuple().tm_year
date2 = datetime.strptime(obj.till_date,
"%Y-%m-%d").timetuple().tm_year
if date1 == date2:
d_name = 'BONUS' + ' - ' + str(date1)
else:
d_name = 'BONUS' + ' ' + str(date1) + ' ' + '-' + ' ' + str(date2)
ws.row(1).height = 500
ws.write_merge(1, 1, 0, 19, d_name, style_header2)
ws.col(0).width = 5000
ws.col(1).width = 7500
ws.col(2).width = 5000
ws.col(3).width = 3000
ws.col(4).width = 3000
ws.col(5).width = 3000
ws.col(6).width = 3000
ws.col(7).width = 3000
ws.col(8).width = 3000
ws.col(9).width = 3000
ws.col(10).width = 3000
ws.col(11).width = 3000
ws.col(12).width = 3000
ws.col(13).width = 3000
ws.col(14).width = 3000
ws.col(15).width = 3000
ws.col(16).width = 3000
ws.col(17).width = 3000
ws.col(18).width = 3000
ws.col(19).width = 3000
ws.col(20).width = 3000
ws.col(21).width = 3000
ws.col(22).width = 3000
ws.col(23).width = 3000
ws.col(24).width = 3000
ws.col(25).width = 3000
ws.col(26).width = 3000
ws.col(27).width = 3000
ws.col(28).width = 3000
ws.col(29).width = 4000
ws.row(2).height = 400
ws.write(2, 0, 'EMP. CODE', style_header)
ws.write(2, 1, 'NAME', style_header)
ws.write(2, 2, 'JOINING DATE', style_header)
ws.write_merge(2, 2, 3, 4, 'APRIL', style_header)
ws.write_merge(2, 2, 5, 6, 'MAY', style_header)
ws.write_merge(2, 2, 7, 8, 'JUNE', style_header)
ws.write_merge(2, 2, 9, 10, 'JULY', style_header)
ws.write_merge(2, 2, 11, 12, 'AUGUST', style_header)
ws.write_merge(2, 2, 13, 14, 'SEPTEMBER', style_header)
ws.write_merge(2, 2, 15, 16, 'OCTOBER', style_header)
ws.write_merge(2, 2, 17, 18, 'NOVEMBER', style_header)
ws.write_merge(2, 2, 19, 20, 'DECEMBER', style_header)
ws.write_merge(2, 2, 21, 22, 'JANUARY', style_header)
ws.write_merge(2, 2, 23, 24, 'FEBRUARY', style_header)
ws.write_merge(2, 2, 25, 26, 'MARCH', style_header)
ws.write_merge(2, 2, 27, 28, 'TOTAL', style_header)
ws.write(2, 29, 'BONUS', style_header)
ws.row(3).height = 400
ws.write(3, 0, '', style_header)
ws.write(3, 1, '', style_header)
ws.write(3, 2, '', style_header)
ws.write(3, 3, 'DAYS', style_header)
ws.write(3, 4, 'SALARY', style_header)
ws.write(3, 5, 'DAYS', style_header)
ws.write(3, 6, 'SALARY', style_header)
ws.write(3, 7, 'DAYS', style_header)
ws.write(3, 8, 'SALARY', style_header)
ws.write(3, 9, 'DAYS', style_header)
ws.write(3, 10, 'SALARY', style_header)
ws.write(3, 11, 'DAYS', style_header)
ws.write(3, 12, 'SALARY', style_header)
ws.write(3, 13, 'DAYS', style_header)
ws.write(3, 14, 'SALARY', style_header)
ws.write(3, 15, 'DAYS', style_header)
ws.write(3, 16, 'SALARY', style_header)
ws.write(3, 17, 'DAYS', style_header)
ws.write(3, 18, 'SALARY', style_header)
ws.write(3, 19, 'DAYS', style_header)
ws.write(3, 20, 'SALARY', style_header)
ws.write(3, 21, 'DAYS', style_header)
ws.write(3, 22, 'SALARY', style_header)
ws.write(3, 23, 'DAYS', style_header)
ws.write(3, 24, 'SALARY', style_header)
ws.write(3, 25, 'DAYS', style_header)
ws.write(3, 26, 'SALARY', style_header)
ws.write(3, 27, 'DAYS', style_header)
ws.write(3, 28, 'SALARY', style_header)
ws.write(3, 29, '', style_header)
if obj.partner_id:
list_ids = emp_obj.search(cr, uid,
[('partner_id', '=', obj.partner_id.id),
('active', '=', True),
('type', '=', 'Contractor')])
list_ids1 = emp_obj.search(cr, uid,
[('partner_id', '=', obj.partner_id.id),
('active', '=', False),
('type', '=', 'Contractor')])
list_ids = list_ids + list_ids1
elif obj.employee_id:
list_ids = emp_obj.search(cr, uid,
[('id', '=', obj.employee_id.id),
('active', '=', True),
('type', '=', 'Contractor')])
elif obj.company_id:
list_ids = emp_obj.search(cr, uid,
[('company_id', '=', obj.company_id.id),
('active', '=', True),
('type', '=', 'Contractor')])
list_ids1 = emp_obj.search(cr, uid,
[('company_id', '=', obj.company_id.id),
('active', '=', False),
('type', '=', 'Contractor')])
list_ids = list_ids + list_ids1
else:
raise osv.except_osv(
_('Warning !'),
_("Please Select Atleast Company Or Employee."))
if len(list_ids) == 1:
query ="select hr.sinid,rr.name,hr.doj,sum(pmbl.apr),sum(pmbl.may),sum(pmbl.june),sum(pmbl.july),sum(pmbl.aug),sum(pmbl.sep),sum(pmbl.oct),"\
"sum(pmbl.nov),sum(pmbl.dec),sum(pmbl.jan),sum(pmbl.feb),sum(pmbl.mar),sum(pmbl.total_day),sum(pmbl.bonus),sum(pmbl.apr_salary),"\
"sum(pmbl.may_salary),sum(pmbl.june_salary),sum(pmbl.july_salary),sum(pmbl.aug_salary),sum(pmbl.sep_salary),sum(pmbl.oct_salary),"\
"sum(pmbl.nov_salary),sum(pmbl.dec_salary),sum(pmbl.jan_salary),sum(pmbl.feb_salary),sum(pmbl.mar_salary),sum(pmbl.total_salary)"\
"from payment_management_bonus_line as pmbl left join hr_employee as hr on pmbl.employee_id = hr.id left join resource_resource as rr on hr.resource_id = rr.id "\
"where pmbl.employee_id = '"+str(list_ids[0])+"' and pmbl.bonus_from >= '"+str(obj.from_date)+"' and pmbl.bonus_till <= '"+str(obj.till_date)+"' group by hr.sinid,rr.name,hr.doj order by hr.sinid "
cr.execute(query)
temp = cr.fetchall()
else:
query ="select hr.sinid,rr.name,hr.doj,sum(pmbl.apr),sum(pmbl.may),sum(pmbl.june),sum(pmbl.july),sum(pmbl.aug),sum(pmbl.sep),sum(pmbl.oct),"\
"sum(pmbl.nov),sum(pmbl.dec),sum(pmbl.jan),sum(pmbl.feb),sum(pmbl.mar),sum(pmbl.total_day),sum(pmbl.bonus),sum(pmbl.apr_salary),"\
"sum(pmbl.may_salary),sum(pmbl.june_salary),sum(pmbl.july_salary),sum(pmbl.aug_salary),sum(pmbl.sep_salary),sum(pmbl.oct_salary),"\
"sum(pmbl.nov_salary),sum(pmbl.dec_salary),sum(pmbl.jan_salary),sum(pmbl.feb_salary),sum(pmbl.mar_salary),sum(pmbl.total_salary)"\
"from payment_management_bonus_line as pmbl left join hr_employee as hr on pmbl.employee_id = hr.id left join resource_resource as rr on hr.resource_id = rr.id "\
"where pmbl.employee_id in "+str(tuple(list_ids))+" and pmbl.bonus_from >= '"+str(obj.from_date)+"' and pmbl.bonus_till <= '"+str(obj.till_date)+"' group by hr.sinid,rr.name,hr.doj order by hr.sinid"
cr.execute(query)
temp = cr.fetchall()
if not temp:
raise osv.except_osv(_('Warning !'), _("Record Not Found !!!"))
columnno = 4
for val in temp:
doj = datetime.strptime(val[2], "%Y-%m-%d").strftime("%d-%m-%Y")
ws.write(columnno, 0, val[0], style_header5)
ws.write(columnno, 1, val[1], style_header5)
ws.write(columnno, 2, doj, style_header5)
ws.write(columnno, 3, val[3], style_header5)
ws.write(columnno, 4, val[17], style_header5)
ws.write(columnno, 5, val[4], style_header5)
ws.write(columnno, 6, val[18], style_header5)
ws.write(columnno, 7, val[5], style_header5)
ws.write(columnno, 8, val[19], style_header5)
ws.write(columnno, 9, val[6], style_header5)
ws.write(columnno, 10, val[20], style_header5)
ws.write(columnno, 11, val[7], style_header5)
ws.write(columnno, 12, val[21], style_header5)
ws.write(columnno, 13, val[8], style_header5)
ws.write(columnno, 14, val[22], style_header5)
ws.write(columnno, 15, val[9], style_header5)
ws.write(columnno, 16, val[23], style_header5)
ws.write(columnno, 17, val[10], style_header5)
ws.write(columnno, 18, val[24], style_header5)
ws.write(columnno, 19, val[11], style_header5)
ws.write(columnno, 20, val[25], style_header5)
ws.write(columnno, 21, val[12], style_header5)
ws.write(columnno, 22, val[26], style_header5)
ws.write(columnno, 23, val[13], style_header5)
ws.write(columnno, 24, val[27], style_header5)
ws.write(columnno, 25, val[14], style_header5)
ws.write(columnno, 26, val[28], style_header5)
ws.write(columnno, 27, val[15], style_header5)
ws.write(columnno, 28, val[29], style_header5)
ws.write(columnno, 29, val[16], style_header5)
columnno += 1
f = cStringIO.StringIO()
wb.save(f)
out = base64.encodestring(f.getvalue())
return self.write(cr,
uid,
ids, {
'export_data': out,
'filename': 'Contractor Payment Bonus.xls'
},
context=context)
def 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
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 salary_payment_report(self,cr,uid,ids,context=None):
#Define the font attributes for header
fnt = Font()
fnt.name = 'Arial'
fnt.size=16
fnt.style= 'Regular'
#Define the font attributes for header
content_fnt = Font()
content_fnt.name ='Arial'
content_fnt.size=16
content_fnt.style= 'Regular'
align_content = Alignment()
align_content.horz= Alignment.HORZ_CENTER
borders = Borders()
borders.left = 0x01
borders.right = 0x01
borders.top = 0x01
borders.bottom = 0x01
#The text should be centrally aligned
align = Alignment()
align.horz = Alignment.HORZ_CENTER
align.vert = Alignment.VERT_CENTER
#We set the backgroundcolour here
pattern = Pattern()
pattern.pattern = Pattern.SOLID_PATTERN
pattern.pattern_fore_colour = 0x1F
#apply the above settings to the row(0) header
style_header= XFStyle()
style_header.font= fnt
style_header.pattern= pattern
style_header.borders = borders
style_header.alignment=align
#Define the font attributes for header
fnt3 = Font()
fnt3.name = 'Arial'
fnt3.size=16
fnt3.style= 'Regular'
#Define the font attributes for header
content_fnt3 = Font()
content_fnt3.name ='Arial'
content_fnt3.size=16
content_fnt3.style= 'Regular'
align_content3 = Alignment()
align_content3.horz= Alignment.HORZ_CENTER
borders3 = Borders()
borders3.left = 0x01
borders3.right = 0x01
borders3.top = 0x01
borders3.bottom = 0x01
#The text should be centrally aligned
align3 = Alignment()
align3.horz = Alignment.HORZ_CENTER
align3.vert = Alignment.VERT_CENTER
#We set the backgroundcolour here
pattern3 = Pattern()
#apply the above settings to the row(0) header
style_header3= XFStyle()
style_header3.font= fnt3
style_header3.pattern= pattern3
style_header3.borders = borders3
style_header3.alignment=align3
#Define the font attributes for header
fnt4 = Font()
fnt4.name = 'Arial'
#Define the font attributes for header
content_fnt4 = Font()
content_fnt4.name ='Arial'
align_content4 = Alignment()
align_content4.horz= Alignment.HORZ_LEFT
borders4 = Borders()
borders4.left = 0x01
borders4.right = 0x01
borders4.top = 0x01
borders4.bottom = 0x01
#The text should be centrally aligned
align4 = Alignment()
align4.horz = Alignment.HORZ_LEFT
align4.vert = Alignment.VERT_CENTER
#We set the backgroundcolour here
pattern4 = Pattern()
#apply the above settings to the row(0) header
style_header4= XFStyle()
style_header4.font= fnt4
style_header4.pattern= pattern4
style_header4.borders = borders4
style_header4.alignment=align4
#Define the font attributes for header
fnt1 = Font()
fnt1.name = 'Arial'
fnt1.size=10
fnt1.Style= 'Regular'
#Define the font attributes for header
content_fnt1 = Font()
content_fnt1.name ='Arial'
content_fnt1.size=10
content_fnt1.Style= 'Regular'
align_content1 = Alignment()
align_content1.horz= Alignment.HORZ_RIGHT
borders1 = Borders()
borders1.left = 0x01
borders1.right = 0x01
borders1.top = 0x01
borders1.bottom = 0x01
#The text should be centrally aligned
align1 = Alignment()
align1.horz = Alignment.HORZ_RIGHT
align1.vert = Alignment.VERT_CENTER
#We set the backgroundcolour here
pattern1 = Pattern()
#apply the above settings to the row(0) header
style_header1= XFStyle()
style_header1.font= fnt1
style_header1.pattern= pattern1
style_header1.borders = borders1
style_header1.alignment=align1
#Define the font attributes for header
fnt2 = Font()
fnt2.name = 'Arial'
fnt2.size=16
fnt2.Style= 'Regular'
#Define the font attributes for header
content_fnt2 = Font()
content_fnt2.name ='Arial'
content_fnt2.size=16
content_fnt2.Style= 'Regular'
align_content2 = Alignment()
align_content2.horz= Alignment.HORZ_RIGHT
borders2 = Borders()
borders2.left = 0x01
borders2.right = 0x01
borders2.top = 0x01
borders2.bottom = 0x01
#The text should be centrally aligned
align2 = Alignment()
align2.horz = Alignment.HORZ_RIGHT
align2.vert = Alignment.VERT_CENTER
#We set the backgroundcolour here
pattern2 = Pattern()
#apply the above settings to the row(0) header
style_header2= XFStyle()
style_header2.font= fnt2
style_header2.pattern= pattern
style_header2.borders = borders2
style_header2.alignment=align2
wb = Workbook()
ws = wb.add_sheet('Salary Report')
this=self.browse(cr,uid,ids[0],context=context)
ws.row(0).height=300
ws.row(1).height=300
ws.row(2).height=300
# ws.col(0).width = 5000
# ws.col(0).width = 5000
# ws.col(0).width = 5000
# ws.col(0).width = 5000
ws.write_merge(0,0,0,5, 'SALARY REPORT',style_header)
ws.write_merge(1,1,0,5, ('DEPARTMENT : ',this.department_id.name),style_header)
ws.write_merge(2,2,0,1, 'Month',style_header)
ws.write_merge(2,2,2,3, 'Salary Amount',style_header)
ws.write_merge(2,2,4,5, 'Remark',style_header)
l=[]
lst=[]
for val in this.month_ids:
i = 3
query="""select hy.name,spl.month, sum(spl.total_amount) from salary_payment_line as spl left join holiday_year as hy on spl.year_id = hy.id where month = '"""+str(val.month)+"""'
and year_id = '"""+str(val.year_id.id)+"""' and curr_department = '"""+str(this.department_id.id)+"""' and salary_type='Salary'
group by spl.month, hy.name"""
cr.execute(query)
temp = cr.fetchall()
for year,month, total_amount in temp:
t=()
mon = int(month)
t = (year,mon, total_amount)
lst.append(t)
total = 0.0
lst.sort()
for year,month, total_amount in lst:
if month == 1:
month1 = 'January'
elif month == 2:
month1 = 'February'
elif month == 3:
month1 = 'March'
elif month == 4:
month1 = 'April'
elif month == 5:
month1 = 'May'
elif month == 6:
month1 = 'June'
elif month == 7:
month1 = 'July'
elif month == 8:
month1 = 'August'
elif month == 9:
month1 = 'September'
elif month == 10:
month1 = 'October'
elif month == 11:
month1 = 'November'
else:
month1 = 'December'
total += total_amount
ws.write_merge(i,i,0,1, (month1,' ',year),style_header4)
ws.write_merge(i,i,2,3, round(total_amount,2),style_header1)
ws.write_merge(i,i,4,5, ' ',style_header3)
i += 1
ws.write_merge(i,i,0,1, 'Total Amount',style_header2)
ws.write_merge(i,i,2,3, round(total,2),style_header2)
ws.write_merge(i,i,4,5, ' ',style_header)
if len(lst) < 1:
raise osv.except_osv(_('Warning!'),_('No Record found!'))
f = cStringIO.StringIO()
wb.save(f)
out=base64.encodestring(f.getvalue())
sal_report = self.write(cr, uid, ids, {'export_data':out, 'filename':'Salary Report.xls'}, context=context)
return sal_report
def 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 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)
def exportToExcel(self,objectProject):
book = Workbook();
sheet1 = book.add_sheet('Sheet 1')
sheet1.col(1).width = 256*20;
sheet1.col(2).width = 256*80;
sheet1.col(3).width = 256*10;
sheet1.col(4).width = 256*20;
default_book_style = book.default_style
default_book_style.font.height = 20 * 36 # 36pt
fnt = Font()
fnt.name = 'Arial'
fnt.colour_index = 4
fnt.bold = True
borders = Borders()
borders.left = Borders.THIN
borders.right = Borders.THIN
borders.top = Borders.THIN
borders.bottom = Borders.THIN
pattern = Pattern();
pattern.pattern = Pattern.SOLID_PATTERN
pattern.pattern_fore_colour = 23
algn1 = Alignment();
algn1.wrap = 1;
#algn1.horz = Alignment.HORZ_CENTER
#algn1.vert = Alignment.VERT_TOP
alignHeader = Alignment();
alignHeader.horz = Alignment.HORZ_CENTER;
alignTop = Alignment();
alignTop.vert = Alignment.VERT_TOP
print "export";
if( objectProject):
i=0;
print "start" ;
styleHead = XFStyle();
styleHead.font = fnt;
styleHead.borders = borders;
styleHead.pattern = pattern;
styleHead.alignment = alignHeader;
row1 = sheet1.row(i) ;
row1.write(0, ('risk id').decode('UTF8'),styleHead );
sheet1.write_merge(i, i, 1, 2, ('รายละเอียด').decode('UTF8') ,styleHead );
# row1.write(1, ('รายละเอียด').decode('UTF8'));
row1.write(3, ('วันที่รายงาน').decode('UTF8'), styleHead );
row1.write(4, ('หน่วยที่รายงาน').decode('UTF8'), styleHead );
i=i+1;
style1 = XFStyle();
style1.alignment = algn1;
#style0 = xlwt.easyxf('font: name Times New Roman size 20, color-index black, bold on')
for value in objectProject:
row1 = sheet1.row(i) ;
styleRowDetail = XFStyle();
styleRowDetail.borders = borders;
styleRowDetail.alignment = alignTop;
StyleRowDetailWrap = styleRowDetail ;
StyleRowDetailWrap.alignment = algn1;
styleDate = XFStyle()
styleDate.num_format_str = 'DD-MM-YYYY' ; #'D-MMM-YY';
styleDate.borders = borders;
row1.write(0, value.get('risk_management_id'),styleRowDetail );
#row1.write(1, value.get('risk_detail').decode('UTF8') , style1);
sheet1.write_merge(i, i, 1, 2, value.get('risk_detail').decode('UTF8') , StyleRowDetailWrap );
row1.write(3, value.get('report_date') ,styleDate);
row1.write(4, value.get('report').decode('UTF8') ,styleRowDetail );
i=i+1;
row1 = sheet1.row(i) ;
row1.write(0," " );
row1.write(1,('หน่วยที่เกี่ยวข้อง').decode('UTF8') ,styleHead );
sheet1.write_merge(i, i, 2, 3,('รายละเอียดการตอบ').decode('UTF8') , styleHead );
i=i+1;
for sub in value.get('response') :
row1 = sheet1.row(i) ;
row1.write(0," " );
row1.write(1,sub.get('risk_team').decode('UTF8') , styleRowDetail );
sheet1.write_merge(i, i, 2, 3,sub.get('result').decode('UTF8') , StyleRowDetailWrap );
i=i+1;
dirTempFile = gettempdir() + _os.sep + str('simple.xls');
print dirTempFile;
book.save(dirTempFile);
def 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)
q = Reservation.objects.filter(status=RESERVATION_APPROVED)
q = q.filter(Q(site=site))
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()
def get_attendance_report(self):
try:
import xlwt
except:
raise Warning(_('User Error'), _('Please Install xlwt Library.!'))
filename = 'Attendance Register.xls'
fp = BytesIO()
wb = xlwt.Workbook(encoding='utf-8')
worksheet = wb.add_sheet('PC_EXAM_ATTENDANCE_REGISTER')
current_obj = self
# -----------------------
# Excel Font Style & Sizes
# ----------------------
fnt1 = Font()
fnt1.name = 'TimesNewRoman'
fnt1.bold = True
fnt1.height = 16 * 0x14
fnt2 = Font()
fnt2.name = 'verdana'
fnt2.bold = True
fnt2.height = 12 * 0x14
fnt3 = Font()
fnt3.name = 'TimesNewRoman'
fnt3.bold = True
fnt3.height = 13 * 0x12
fnt4 = Font()
fnt4.name = 'TimesNewRoman'
fnt4.height = 16 * 0x14
# -----------------
# Excel Alignment
# ----------------
al3 = Alignment()
al3.horz = Alignment.HORZ_CENTER
al3.vert = Alignment.VERT_CENTER
al4 = Alignment()
al4.horz = Alignment.HORZ_LEFT
al4.vert = Alignment.VERT_CENTER
al5 = Alignment()
al5.horz = Alignment.HORZ_LEFT
al5.vert = Alignment.VERT_CENTER
# -----------------------
# Excel Style
# ----------------------
style1 = XFStyle()
style1.alignment = al3
style1.font = fnt1
style2 = XFStyle()
style2.font = fnt2
style2.alignment = al4
style3 = XFStyle()
style3.alignment = al3
style3.font = fnt3
style4 = XFStyle()
style4.alignment = al5
style4.font = fnt4
# -------------------------------------------------------------------------
# HEADER In Excel
# -------------------------------------------------------------------------
lst = []
fields = ['CIMA STUDENT NUMBER', 'SURNAME', 'NAME', 'SIGNATURE']
for i in [1, 2, 3, 4]:
first_col = worksheet.col(i)
first_col.width = 500 * 20
date_begin = str(
datetime.strptime(current_obj.date_begin, "%Y-%m-%d %H:%M:%S") +
timedelta(hours=2))
date_end = str(
datetime.strptime(current_obj.date_end, "%Y-%m-%d %H:%M:%S") +
timedelta(hours=2))
worksheet.merge(0, 16, 1, 4)
try:
worksheet.insert_bitmap(
'/opt/custom_modules/event_price_kt/images/charterquest.bmp',
0, 1, 9, 3)
except:
pass
worksheet.write_merge(17, 18, 1, 1, 'SUBJECT:', style1)
worksheet.write_merge(17, 18, 2, 4, current_obj.name, style1)
k = 19
pc_exam = True
if current_obj.pc_exam == False:
pc_exam = False
worksheet.write_merge(k, k + 1, 1, 1, 'LECTURER:', style1)
# if current_obj.main_speaker_id:
# worksheet.write_merge(k,k+1,2, 4,current_obj.main_speaker_id.name , style1)
# else:
# worksheet.write_merge(k,k+1,2, 4,"", style1)
k = k + 2
worksheet.write_merge(k, k + 1, 1, 1, 'DATE:', style1)
worksheet.write_merge(k, k + 1, 2, 4, (date_begin).split(' ')[0],
style1)
worksheet.write_merge(k + 2, k + 3, 1, 1, 'TIME:', style1)
worksheet.write_merge(k + 2, k + 3, 2, 4,
(date_begin)[11:16] + '-' + (date_end)[11:16],
style1)
if not pc_exam:
worksheet.write_merge(k + 4, k + 5, 1, 1, 'COURSE STUDY OPTION:',
style1)
# worksheet.write_merge(k+4,k+5,2, 4,current_obj.study.name, style1)
k = k + 4
else:
k = k + 2
worksheet.write_merge(k + 2, k + 3, 1, 1, 'CAMPUS:', style1)
worksheet.write_merge(k + 2, k + 3, 2, 4, current_obj.address_id.name,
style1)
worksheet.write_merge(
k + 4, k + 5, 1, 4,
"PLEASE SIGN ATTENDANCE REGISTER & ENTER CIMA STUDENT NUMBER",
style1)
i = 1
for field in fields:
worksheet.write_merge(k + 6, k + 7, i, i, field, style1)
i += 1
i = k + 8
for obj in current_obj.registration_ids:
partner_name = obj.partner_id.name
name = partner_name.split(' ')
if len(name) == 1:
name = partner_name.split(' ')
if obj.partner_id.prof_body_id:
worksheet.write_merge(i, i + 1, 1, 1,
obj.partner_id.prof_body_id, style4)
else:
worksheet.write_merge(i, i + 1, 1, 1, "", style4)
try:
worksheet.write_merge(i, i + 1, 2, 2, name[1], style4)
except:
worksheet.write_merge(i, i + 1, 2, 2, '', style4)
worksheet.write_merge(i, i + 1, 3, 3, name[0], style4)
worksheet.write_merge(i, i + 1, 4, 4, '', style4)
i += 2
wb.save(fp)
out = base64.encodestring(fp.getvalue())
final_arr_data = {}
final_arr_data['file_stream'] = out
final_arr_data['name'] = filename
pl_report_id = self.env['attendance.sheet.report'].create(
final_arr_data)
vals = {
'name': 'Attendance Sheet Report',
'datas': out,
'datas_fname': filename
}
return {
'res_id': pl_report_id.id,
'name': filename,
'view_type': 'form',
'view_mode': 'form',
'res_model': 'attendance.sheet.report',
'type': 'ir.actions.act_window',
}
def 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 create_excel(work_excel, withdraw_info=[]):
title_fnt = Font()
title_fnt.height = 0x0140
title_fnt.name = u'宋体'
title_fnt.bold = True
brd = Borders()
brd.bottom = 1
brd.top = 1
brd.left = 1
brd.right = 1
title_location = Alignment()
title_location.horz = Alignment.HORZ_CENTER
title_location.vert = Alignment.VERT_CENTER
title_style = XFStyle()
title_style.font = title_fnt
title_style.alignment = title_location
style = XFStyle()
style.font.height = 0x00E0
style.font.name = u'宋体'
style.font.bold = False
style.alignment.horz = Alignment.HORZ_CENTER
style.alignment.vert = Alignment.VERT_CENTER
content_title_style = deepcopy(style)
content_title_style.alignment.horz = Alignment.HORZ_LEFT
content_title_style.font.height = 0x00E0
content_style = deepcopy(style)
content_style.alignment.horz = Alignment.HORZ_LEFT
center_content_style = deepcopy(style)
center_content_style.alignment.horz = Alignment.HORZ_CENTER
center_content_style.borders = brd
style.borders = brd
content_style.borders = brd
merge_up_style = deepcopy(style)
merge_up_style.borders.bottom = 0
content_up_style = deepcopy(merge_up_style)
content_up_style.alignment.horz = Alignment.HORZ_CENTER
merge_down_style = deepcopy(style)
merge_down_style.borders.top = 0
content_down_style = deepcopy(merge_down_style)
content_down_style.alignment.horz = Alignment.HORZ_RIGHT
content_down_style.font.height = 0x00CA
for i, info in enumerate(withdraw_info):
width = i * 25
work_excel.write_merge(2 + width, 2 + width, 0, 16, u'商户提现付款申请单', title_style)
work_excel.write_merge(3 + width, 3 + width, 1, 5, info['supplier_type'], content_title_style)
work_excel.write_merge(3 + width, 3 + width, 6, 16, info['supplier'], content_title_style)
work_excel.write(4 + width, 0, u'收款单位名称', style)
work_excel.write_merge(4 + width, 4 + width, 1, 5, info['company'], content_style)
work_excel.write(4 + width, 6, u'申请提现帐号', style)
work_excel.write_merge(4 + width, 4 + width, 7, 16, info['applier'], center_content_style)
work_excel.write(5 + width, 0, u'开户银行', style)
work_excel.write_merge(5 + width, 5 + width, 1, 5, info['bank'], content_style)
work_excel.write(5 + width, 6, u'申请提现日期', style)
work_excel.write_merge(5 + width, 5 + width, 7, 16, info['apply_time'], center_content_style)
work_excel.write(6 + width, 0, u'银行帐号', style)
work_excel.write_merge(6 + width, 6 + width, 1, 5, info['account'], content_style)
work_excel.write(6 + width, 6, u'合同账期', style)
work_excel.write_merge(6 + width, 6 + width, 7, 16, info['payment_day_type'], center_content_style)
work_excel.write(7 + width, 0, u'付款用途', style)
work_excel.write_merge(7 + width, 7 + width, 1, 5, u'商户提现', content_style)
work_excel.write(7 + width, 6, u'付款日期', style)
work_excel.write_merge(7 + width, 7 + width, 7, 16, info['date'], center_content_style)
units = [u'千', u'百', u'十', u'万', u'千', u'百', u'十', u'元', u'角', u'分']
digit = list(str(info['money']))
digit.remove('.')
digit.reverse()
for j, unit in enumerate(units):
work_excel.write(8 + width, 7 + j, unit, content_style)
for index, value in enumerate(digit):
work_excel.write(9 + width, 16 - index, value, content_style)
work_excel.write(9 + width, 16 - index - 1, '￥', content_style)
work_excel.write_merge(10 + width, 11 + width, 0, 0, u'备注事项', style)
work_excel.write_merge(10 + width, 10 + width, 1, 6, '', content_style)
work_excel.write_merge(10 + width, 10 + width, 7, 16, '', style)
work_excel.write_merge(11 + width, 11 + width, 1, 16, info['print_info'], content_down_style)
work_excel.write(8 + width, 0, u'付款金额', merge_up_style)
work_excel.write(9 + width, 0, u'人民币（大写）', merge_down_style)
work_excel.write_merge(8 + width, 9 + width, 1, 6, info['china_money'], content_style)
work_excel.write_merge(13 + width, 13 + width, 0, 2, u'部门主管:', content_title_style)
work_excel.write_merge(13 + width, 13 + width, 3, 5, u'财务经理:', content_title_style)
work_excel.write_merge(13 + width, 13 + width, 6, 16, u'公司总经理:', content_title_style)
for row in range(2, 12):
work_excel.row(row + width).height_mismatch = 1
work_excel.row(row + width).height = 478
work_excel.row(2 + width).height_mismatch = 1
work_excel.row(2 + width).height = 1000
work_excel.col(0).width = 3871
work_excel.col(1).width = 1771
work_excel.col(2).width = 1509
work_excel.col(3).width = 2348
work_excel.col(4).width = 840
work_excel.col(5).width = 3241
work_excel.col(6).width = 3441
for col in range(7, 17):
work_excel.col(col).width = 709
def qiandaoXls(filename,qiandao,dategroup,mi,response):
import uuid
filename+=u'.xls'
response['Content-Disposition'] = (u'attachment;filename=%s'%filename).encode('utf-8')
import xlwt
from xlwt import Font,Alignment
style1=xlwt.XFStyle()
font1=Font()
font1.height=320
font1.name=u'仿宋'
style1.font=font1
algn=Alignment()
algn.horz=Alignment.HORZ_RIGHT
style1.alignment=algn
style0=xlwt.XFStyle()
algn0=Alignment()
algn0.horz=Alignment.HORZ_CENTER
algn0.vert=Alignment.VERT_CENTER
font=Font()
font.height=320
font.bold=False
font.name=u'仿宋'
style0.alignment=algn0
style0.font=font
style3=xlwt.XFStyle()
algn3=Alignment()
algn3.horz=Alignment.HORZ_CENTER
font3=Font()
font3.height=320
font3.bold=False
font1.name=u'仿宋'
style3.alignment=algn3
style3.font=font3
pattern = xlwt.Pattern()
pattern.pattern = xlwt.Pattern.SOLID_PATTERN
pattern.pattern_fore_colour =2
style3.pattern=pattern
wb=xlwt.Workbook()
style2=xlwt.XFStyle()
algn2=Alignment()
algn2.horz=Alignment.HORZ_LEFT
font2=Font()
font2.height=350
font2.bold=True
font1.name=u'仿宋'
style2.alignment=algn2
style2.font=font2
wb=xlwt.Workbook()
ws=wb.add_sheet(u"签到报表",cell_overwrite_ok=True)
rownum=2
usernum=1
ws.write_merge(0,1,0,0,u'序号',style2)
ws.write_merge(0,1,1,1,u'员工ID',style2)
ws.col(1).width=0x0d00 +3000
ws.write_merge(0,1,2,2,u'姓名',style2)
ws.col(2).width=0x0d00 +3000
for i,q in enumerate(qiandao):
ws.write_merge(0,0,5*i+3,5*i+6,q.name,style2)
ws.write_merge(1,1,5*i+3,5*i+3,u'厅台',style2)
ws.col(5*i+3).width=0x0d00 + 7000
ws.write_merge(1,1,5*i+4,5*i+4,u'地址',style2)
ws.col(5*i+4).width=0x0d00 + 9000
ws.write_merge(1,1,5*i+5,5*i+5,u'时间',style2)
ws.write_merge(1,1,5*i+6,5*i+6,u'位置判断',style2)
ws.col(6).width=0x0d00 +3000
ws.write_merge(1,1,5*i+7,5*i+7,u'时间判读',style2)
ws.col(7).width=0x0d00 +3000
for data in dategroup:
ws.write_merge(rownum,rownum,0,data['rowspan2'],u'日期：%s'%(data['date'],),style2)
rownum+=1
tempnum=0
for query in data['query']:
for i,rows in enumerate(query['qiandaolist']):
for j,row in enumerate(rows):
ws.write_merge(rownum+j,rownum+j,3+i*5+0,3+i*5+0,row['officename'],style0)
ws.write_merge(rownum+j,rownum+j,3+i*5+1,3+i*5+1,row['address'],style0)
ws.write_merge(rownum+j,rownum+j,3+i*5+2,3+i*5+2,row['dateTime'],style0)
if row['officegps']:
if row['gpsdistance']<mi:
ws.write_merge(rownum+j,rownum+j,3+i*5+3,3+i*5+3,u'合格',style0)
else:
ws.write_merge(rownum+j,rownum+j,3+i*5+3,3+i*5+3,u'不合格',style3)
else:
ws.write_merge(rownum+j,rownum+j,3+i*5+3,3+i*5+3,u'0',style0)
if row['time']:
ws.write_merge(rownum+j,rownum+j,3+i*5+4,3+i*5+4,u'合格',style0)
else:
ws.write_merge(rownum+j,rownum+j,3+i*5+4,3+i*5+4,u'不合格',style3)
if tempnum<j:
tempnum=j
ws.write_merge(rownum,rownum+tempnum,0,0,usernum,style0)
ws.write_merge(rownum,rownum+tempnum,1,1,query['user']['username'],style0)
ws.write_merge(rownum,rownum+tempnum,2,2,query['user']['get_full_name'],style0)
rownum+=tempnum+1
usernum+=1
wb.save(response)
def 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
# 操作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)
