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 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 RUNN():
# ghi file excel
fnt = Font()
fnt.name = "Times New Roman"
fnt.height = 280
borders = Borders()
borders.left = Borders.THIN
borders.right = Borders.THIN
borders.top = Borders.THIN
borders.bottom = Borders.THIN
style = XFStyle()
style.font = fnt
style.borders = borders
wb = Workbook()
sheet1 = wb.add_sheet("Điểm")
col = 0
row = 0
sheet1.write(row, col, "STT", style)
sheet1.write(row, col + 1, "TÊN BÀI LÀM", style)
sheet1.write(row, col + 2, "ĐIỂM", style)
row += 1
diem = []
st = 1
for i in t:
if i == name_form: continue
else:
try:
t1 = docx.Document(i)
sheet1.write(row, col, st, style)
sheet1.write(row, col + 1, i[:-5], style)
sheet1.write(row, col + 2, round(10 * so_sanh(t1), 2), style)
row += 1
st += 1
except Exception: pass
wb.save("KetQua.xls")
def get_styles(): style = easyxf( 'font: name Arial;' ) style_s = easyxf( 'font: name Arial;' 'borders: left thin, right thin, top medium, bottom thin;' ) style_m = easyxf( 'font: name Arial, bold True;' 'borders: left thin, right thin, top thin, bottom thin;' ) font = Font() font.bold = 1 font.height = 320 style.font = font style.alignment.horz = Alignment.HORZ_LEFT style.alignment.vert = Alignment.VERT_CENTER style_s.font.height = 200 style_s.font.bold = 1 style_s.alignment.horz = Alignment.HORZ_CENTER style_s.alignment.vert = Alignment.VERT_CENTER style_s.alignment.wrap = True return style, style_s, style_m
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 _getStyle(self, org_style, org_font):
from xlwt import XFStyle, Font, Borders, Pattern
# font
font = Font()
font.name = org_font.name
font.height = org_font.height
font.italic = org_font.italic
font.struck_out = org_font.struck_out
font.outline = org_font.outline
font.shadow = org_font.shadow
font.colour_index = org_font.colour_index
font.bold = org_font.bold
font._weight = org_font.weight
font.escapement = org_font.escapement_type
font.underline = org_font.underline_type
font.family = org_font.family
font.charset = org_font.character_set
# border
borders = Borders()
borders.left = Borders.THIN #org_style.xf.border.left_line_style
borders.right = Borders.THIN #org_style.xf.border.right_line_style
borders.top = Borders.THIN #org_style.xf.border.top_line_style
borders.bottom = Borders.THIN #org_style.xf.border.bottom_line_style
#borders.diag = self.NO_LINE
borders.left_colour = org_style.xf.border.left_colour_index
borders.right_colour = org_style.xf.border.right_colour_index
borders.top_colour = org_style.xf.border.top_colour_index
borders.bottom_colour = org_style.xf.border.bottom_colour_index
#borders.diag_colour = org_style.xf.border.left_colour_index
#borders.need_diag1 = self.NO_NEED_DIAG1
#borders.need_diag2 = self.NO_NEED_DIAG2
# Pattern
pattern = Pattern()
pattern.pattern = org_style.xf.background.fill_pattern
pattern.pattern_fore_colour = org_style.xf.background.pattern_colour_index
pattern.pattern_back_colour = org_style.xf.background.background_colour_index
#
style = XFStyle()
style.borders = borders
style.font = font
style.pattern = pattern
return style
def create_act(self):
template = open_workbook(self.template, formatting_info=1)
new_file = copy(template)
sheet = new_file.get_sheet(0)
for cell in cells_conf:
print('Coздаем ячейку {}'.format(cell['name']))
style = XFStyle()
font = Font()
style.alignment.horz = cell['align_horz']
font.name = cell['fontname']
font.bold = cell['fontbold']
font.height = cell['fontsize']
style.font = font
name = cell['name']
sheet.write(cell['row'], cell['col'], self._get_str(name), style)
new_file.save(self.path)
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 in_ket_qua():
# tao file ketqua.xls de ghi ket qua
fnt = Font()
fnt.name = "Times New Roman"
fnt.height = 280
borders = Borders()
borders.left = Borders.THIN
borders.right = Borders.THIN
borders.top = Borders.THIN
borders.bottom = Borders.THIN
style = XFStyle()
style.font = fnt
style.borders = borders
wb = Workbook()
sheet1 = wb.add_sheet("Điểm")
col = 0
row = 0
sheet1.write(row, col, "STT", style)
sheet1.write(row, col + 1, "TÊN BÀI LÀM", style)
sheet1.write(row, col + 2, "ĐIỂM", style)
row += 1
st = 1
# Tinh ket qua cua tat ca cac file bai lam va in ket qua ra file KetQua.xls
for name in list_file_word:
sheet1.write(row, col, st, style)
sheet1.write(row, col + 1, name[:-5], style)
sheet1.write(row, col + 2, tinhKetQua(name), style)
row += 1
st += 1
print(name, "___ĐÃ CHẤM XONG___")
# Xoa bo cac file anh sau khi da xu li xong
for name in glob.glob("*.png"):os.remove(name)
# Luu ket qua ra file
os.chdir(local)
# shutil.rmtree('Temp')
wb.save("KetQua.xls")
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 salvar(trelica_objeto):
borders = Borders()
bord = 2
borders.left = bord
borders.right = bord
borders.top = bord
borders.bottom = bord
bord_lateral = Borders()
bord = 2
bord_lateral.left = bord
bord_lateral.right = bord
## FONTES
fonte_titulo = Font()
fonte_titulo.name = 'Times New Roman'
fonte_titulo.bold = True
fonte_titulo.height = 240
fonte_titulo.borders = bord_lateral
fonte_normal = Font()
fonte_normal.name = 'Times New Roman'
fonte_normal.borders = bord_lateral
fonte_normal_problema = Font()
fonte_normal_problema.name = 'Times New Roman'
fonte_normal_problema.borders = bord_lateral
fonte_normal_problema.colour_index = 2 #5=amarelo | 4=azul | 3=verde | 2=vermelho
fonte_destaque = Font()
fonte_destaque.name = 'Arial'
fonte_destaque.colour_index = 4 # azul
## ESTILOS
estilo_titulo = XFStyle()
estilo_titulo.borders = borders
estilo_titulo.font = fonte_titulo
estilo_titulo.alignment.horz = 2
estilo_sub_titulo = XFStyle()
estilo_sub_titulo.borders = borders
estilo_sub_titulo.alignment.horz = 2
estilo_sub_titulo.font = fonte_destaque
estilo_normal = XFStyle()
estilo_normal.font = fonte_normal
estilo_normal.num_format_str = "#,##0.00"
estilo_normal.alignment.horz = 2
estilo_normal_esquerda = XFStyle()
estilo_normal_esquerda.font = fonte_normal
estilo_normal_tabela = XFStyle()
estilo_normal_tabela.font = fonte_normal
estilo_normal_tabela.borders = borders
estilo_normal_tabela.alignment.horz = 2
estilo_normal_tabela.num_format_str = "#,##0.00"
estilo_normal_tabela_red = XFStyle()
estilo_normal_tabela_red.font = fonte_normal_problema
estilo_normal_tabela_red.borders = borders
estilo_normal_tabela_red.alignment.horz = 2
estilo_normal_tabela_red.num_format_str = "#,##0.00"
estilo_destaque = XFStyle()
estilo_destaque.font = fonte_destaque
# estilo_destaque.alignment.horz = 2
estilo_destaque.alignment.vertical = 2
estilo_destaque_tabela = XFStyle()
estilo_destaque_tabela.font = fonte_destaque
estilo_destaque_tabela.borders = borders
estilo_destaque_tabela.alignment.horz = 2
estilo_id_tabela = estilo_normal_tabela
estilo_id_tabela.alignment.num_format_str = "#0"
text_file = filedialog.asksaveasfile(mode='w',
defaultextension=".xls",
filetypes=[('excel files', '.xls')])
wb = Workbook()
sheet = wb.add_sheet('Treliceca')
sheet.col(2).width = 0x0d00 + 22
sheet.col(4).width = 0x0d00 + 40
sheet.col(7).width = 0x0d00 + 15
sheet.col(8).width = 0x0d00 + 15
vt = trelica_objeto.vt
banzo_reto = trelica_objeto.banzo_reto
# padrão de viga treliçada
viga = 'Viga de Cobertura Treliçada com banzos paralelos'
if vt == 1:
viga = 'Viga de Transição Treliçada'
if banzo_reto == 1:
viga = 'Viga de Cobertura Treliçada com banzo inferior reto'
# combinações de carregamentos atuando na treliça
carregamento_grav = trelica_objeto.carregamentos[0]
carregamento_cv = trelica_objeto.carregamentos[1]
vaos = ''
for ponto in trelica_objeto.pontos_vao:
vaos += ' ' + str(ponto[0]) + ','
# pesos calculados da treliça
peso = trelica_objeto.peso
peso_dobrado = trelica_objeto.peso_dobrado
peso_soldado = trelica_objeto.peso_soldado
peso_linear = trelica_objeto.peso_linear
peso_miscelanias = trelica_objeto.peso_miscelanias
# pecas = trelica_objeto.pecas
h_viga = trelica_objeto.h_viga
# lista com as barras da treliça, separadas por tipo
banzos_inferiores = []
banzos_superiores = []
diagonais = []
montantes = []
for barra in trelica_objeto.barras_objetos:
if barra.tipo == 'diagonal':
diagonais.append(barra)
elif barra.tipo == 'montante':
montantes.append(barra)
elif barra.tipo == 'banzo-superior':
banzos_superiores.append(barra)
elif barra.tipo == 'banzo-inferior':
banzos_inferiores.append(barra)
lista_barras = [banzos_inferiores, banzos_superiores, montantes, diagonais]
linha = 0
# inserindo o titulo = viga
sheet.write_merge(linha, linha, 1, 11, viga, estilo_titulo)
linha += 1
sheet.write_merge(linha, linha, 1, 11, 'Dados da Viga Treliçada',
estilo_sub_titulo)
linha += 1
sheet.write_merge(linha, linha, 8, 11, 'Combinações [kg/m]',
estilo_destaque_tabela)
sheet.write(linha, 1, 'Vãos[m]=', estilo_destaque)
sheet.write_merge(linha, linha, 2, 4, vaos, estilo_normal)
linha += 1
sheet.write(linha, 1, 'Altura [m]', estilo_destaque)
sheet.write(linha, 2, h_viga, estilo_normal)
sheet.write(linha, 4, 'Soldado [kg]', estilo_destaque)
sheet.write(linha, 5, peso_soldado, estilo_normal)
sheet.write_merge(linha, linha, 8, 9, 'Grav. ', estilo_destaque_tabela)
sheet.write_merge(linha, linha, 10, 11, 'Vento', estilo_destaque_tabela)
linha += 1
sheet.write(linha, 1, 'Peso [kg]', estilo_destaque)
sheet.write(linha, 2, peso, estilo_normal)
sheet.write(linha, 4, 'Dobrado [kg]', estilo_destaque)
sheet.write(linha, 5, peso_dobrado, estilo_normal)
sheet.write_merge(linha, linha, 8, 9, '1.25xCP + 1.5xSC + 1.4xSU',
estilo_normal_tabela)
sheet.write_merge(linha, linha, 10, 11, '1.0xCP + 1.4xCV',
estilo_normal_tabela)
linha += 1
sheet.write(linha, 1, 'Y [kg/m]', estilo_destaque)
sheet.write(linha, 2, peso_linear, estilo_normal)
sheet.write(linha, 4, 'Miscelanias [kg]', estilo_destaque)
sheet.write(linha, 5, peso_miscelanias, estilo_normal)
sheet.write_merge(linha, linha, 8, 9, carregamento_grav,
estilo_normal_tabela)
sheet.write_merge(linha, linha, 10, 11, carregamento_cv,
estilo_normal_tabela)
linha += 2
# Barras da treliça
sheet.write_merge(linha, linha, 1, 11, 'Barras da Treliça',
estilo_destaque_tabela)
linha += 1
sheet.write(linha, 1, 'id', estilo_destaque_tabela)
sheet.write(linha, 2, ' ', estilo_destaque_tabela)
sheet.write(linha, 3, 'tipo ', estilo_destaque_tabela)
sheet.write(linha, 4, 'seção ', estilo_destaque_tabela)
sheet.write(linha, 5, 'Peso [kg]', estilo_destaque_tabela)
sheet.write(linha, 6, 'l [m]', estilo_destaque_tabela)
sheet.write(linha, 7, 'Comp [kgf]', estilo_destaque_tabela)
sheet.write(linha, 8, 'Tração [kgf]', estilo_destaque_tabela)
sheet.write(linha, 9, 'Ratio ', estilo_destaque_tabela)
sheet.write(linha, 10, 'compr.', estilo_destaque_tabela)
sheet.write(linha, 11, 'tração', estilo_destaque_tabela)
linha += 1
for lista in lista_barras:
for barra in lista:
estilo = estilo_normal_tabela
if barra.ratio > 1:
estilo = estilo_normal_tabela_red
else:
estilo = estilo_normal_tabela
sheet.write(linha, 1, barra.id, estilo)
sheet.write(linha, 2, barra.tipo, estilo)
sheet.write(linha, 3, barra.section.tipo, estilo)
sheet.write(linha, 4, stringSecaoBarra(barra), estilo)
sheet.write(linha, 5, barra.peso, estilo)
sheet.write(linha, 6, barra.comprimento(), estilo)
sheet.write(linha, 7, barra.compressao, estilo)
sheet.write(linha, 8, barra.tracao, estilo)
sheet.write(linha, 9, barra.ratio, estilo)
sheet.write(linha, 10, barra.ratio_compressao, estilo)
sheet.write(linha, 11, barra.ratio_tracao, estilo)
linha += 1
linha = 1
sheet.write(linha, 15, 'Reações nos Apoios = [h1, v1, v2, v3, ..., vn] ',
estilo_normal_esquerda)
linha += 1
sheet.write(linha, 15, 'Apoios numerados conforme desenho do canvas',
estilo_normal_esquerda)
linha += 1
sheet.write(linha, 15, 'Numeração da esquerda, para direita',
estilo_normal_esquerda)
linha += 1
sheet.write(linha, 15, 'F', estilo_destaque_tabela)
sheet.write_merge(linha, linha, 16, 17, 'kgf', estilo_destaque_tabela)
linha += 1
sheet.write(linha, 15, ' ', estilo_destaque_tabela)
sheet.write(linha, 16, 'grav', estilo_destaque_tabela)
sheet.write(linha, 17, 'vento', estilo_destaque_tabela)
linha += 1
reacoes_grav = trelica_objeto.reacoes_grav
reacoes_cv = trelica_objeto.reacoes_cv
qtd_reacoes = len(reacoes_grav)
sheet.write(linha, 15, 'Fx 1', estilo_destaque_tabela)
sheet.write(linha, 16, reacoes_grav[0], estilo_normal_tabela)
sheet.write(linha, 17, reacoes_cv[0], estilo_normal_tabela)
linha += 1
for i in range(1, qtd_reacoes):
sheet.write(linha, 15, 'Fy {:.0f}'.format(i), estilo_destaque_tabela)
sheet.write(linha, 16, reacoes_grav[i], estilo_normal_tabela)
sheet.write(linha, 17, reacoes_cv[i], estilo_normal_tabela)
linha += 1
wb.save(text_file.name)
def crear_xls(self):
# item=self.tree.selection()[0]
# var=self.tree.item(item, option="text")
# print(var)
# Workbook asing
self.filename = filedialog.asksaveasfilename(
initialdir="/",
title="Guardar venta",
filetypes=(("Archivo xls", "*.xls"), ("all files", "*.*")))
if len(self.filename) > 0:
first_book = Workbook()
# Sheets definition
ws1 = first_book.add_sheet('venta')
header_font = Font()
body_font = Font()
# Header font preferences
header_font.name = 'Times New Roman'
header_font.height = 20 * 10
header_font.bold = True
# Body font preferences
body_font.name = 'Arial'
body_font.italic = True
# Header Cells style definition
header_style = XFStyle()
header_style.font = header_font
borders = Borders()
borders.left = 1
borders.right = 1
borders.top = 1
borders.bottom = 1
header_style.borders = borders
# body cell name style definition
body_style = XFStyle()
body_style.font = body_font
print(ws1.col(0).width)
ws1.write(0, 0, '##', header_style)
ws1.write(0, 1, 'DESCRIPCION', header_style)
ws1.write(0, 2, 'PRECIO', header_style)
ws1.write(0, 3, 'CANTIDAD', header_style)
ws1.write(0, 4, 'SUBTOTAL', header_style)
ws1.col(0).width = 2962 * 2
ws1.col(1).width = 2962 * 4
ws1.col(2).width = 2962 * 1
ws1.col(3).width = 2962 * 1
ws1.col(4).width = 2962 * 1
tabla_venta = self.tree2.get_children()
for item, conteo in zip(tabla_venta, range(len(tabla_venta))):
ws1.write(conteo + 1, 0, self.tree2.item(item)['text'])
ws1.write(conteo + 1, 1,
self.tree2.item(item)['values'][0], body_style)
ws1.write(conteo + 1, 2,
self.tree2.item(item)['values'][1], body_style)
ws1.write(conteo + 1, 3,
self.tree2.item(item)['values'][2], body_style)
ws1.write(conteo + 1, 4,
self.tree2.item(item)['values'][3], body_style)
# print(self.tree2.item(item)["values"])
# print(self.tree2.item(item)['text'])
# Saving file
first_book.save(self.filename)
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 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 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 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 produce_summary(self, cr, uid, travel, context=None):
number_format = _('#,##0.00 [$$-C0C];-#,##0.00 [$$-C0C]')
total_fnt = Font()
total_fnt.name = 'Calibri'
total_fnt.bold = True
total_fnt.height = 16 * 20 # font size 12
total_cell_l_style = XFStyle()
total_cell_l_style.alignment = Column.title_aln
total_cell_l_style.borders = Borders()
total_cell_l_style.borders.left = Borders.THICK
total_cell_l_style.borders.right = Borders.HAIR
total_cell_l_style.borders.top = Borders.THICK
total_cell_l_style.borders.bottom = Borders.THICK
total_cell_l_style.pattern = Column.title_ptn
total_cell_l_style.num_format_str = number_format
total_cell_l_style.font = Column.obj_fnt
total_cell_r_style = XFStyle()
total_cell_r_style.alignment = Column.title_aln
total_cell_r_style.borders = Borders()
total_cell_r_style.borders.left = Borders.HAIR
total_cell_r_style.borders.right = Borders.THICK
total_cell_r_style.borders.top = Borders.THICK
total_cell_r_style.borders.bottom = Borders.THICK
total_cell_r_style.pattern = Column.title_ptn
total_cell_r_style.num_format_str = number_format
total_cell_r_style.font = Column.obj_fnt
sub_total_cell_label = Cell(_(u'SOUS-TOTAL'), Column.title_fnt,
Column.title_aln,
total_cell_l_style.borders,
Column.title_ptn)
total_cell_label = Cell(_(u'TOTAL'), total_fnt, Column.title_aln,
total_cell_r_style.borders, Column.title_ptn,
number_format)
journeys = [i for i in travel.journey_ids]
w = Workbook()
ws = w.add_sheet(_('Travel Summary'))
ws.row(2).height = 0x0280
ws.row(3 + len(journeys)).height = 0x0140
ws.row(4 + len(journeys)).height = 0x0180
row = 0
row += 2
# Write headers
for i, col in enumerate(self._excel_columns):
ws.col(i).width = col.width
ws.write(row, i, col.text, col.style)
row += 1
for i, obj in enumerate(journeys):
ws.write(row + i, 0, i + 1, self._excel_columns[0].obj_style)
for j in xrange(1, len(self._excel_columns)):
ws.write(row + i, j, self._excel_columns[j].func(obj),
self._excel_columns[j].obj_style)
row += len(journeys)
rate_index = next(i for i, x in enumerate(self._excel_columns)
if x.text == _('TICKET RATE')) - 1
cost_index = next(i for i, x in enumerate(self._excel_columns)
if x.text == _('COSTS')) - 1
total_index = next(i for i, x in enumerate(self._excel_columns)
if x.text == _('TOTAL')) - 1
# Sub total label
ws.write_merge(row, row, 0, rate_index, sub_total_cell_label.text,
sub_total_cell_label.style)
# Sub totals
ws.write(
row, rate_index + 1,
Formula("SUM(%s%d:%s%d)" %
(chr(66 + rate_index), 4, chr(66 + rate_index), row)),
total_cell_l_style)
ws.write(
row, rate_index + 2,
Formula("SUM(%s%d:%s%d)" %
(chr(66 + cost_index), 4, chr(66 + cost_index), row)),
total_cell_r_style)
total_top_underline_style = XFStyle()
total_top_underline_style.borders = Borders()
total_top_underline_style.borders.top = Borders.THICK
total_top_underline_style.font = Column.obj_fnt
# Draw a line above total to close box
ws.write(row, total_index + 1, "", total_top_underline_style)
row += 1
# Total label
ws.write_merge(row, row, 0, rate_index, total_cell_label.text,
total_cell_label.style)
# Total
ws.write_merge(
row, row, rate_index + 1, total_index,
Formula("%s%d+%s%d" %
(chr(66 + rate_index), row, chr(66 + cost_index), row)),
total_cell_label.style)
return w
def report_get(self,cr,uid,ids,context=None):
this=self.browse(cr,uid,ids[0])
year = this.year_id.name
year_id = this.year_id.id
month = this.month
if month == '1':
month_name = 'January'
elif month == '2':
month_name = 'February'
elif month == '3':
month_name = 'March'
elif month == '4':
month_name = 'April'
elif month == '5':
month_name = 'May'
elif month == '6':
month_name = 'June'
elif month == '7':
month_name = 'July'
elif month == '8':
month_name = 'August'
elif month == '9':
month_name = 'September'
elif month == '10':
month_name = 'October'
elif month == '11':
month_name = 'November'
elif month == '12':
month_name = 'December'
else:
raise osv.except_osv(_('Warning !'),_("Specify month correctly. "))
#Define the font attributes for header
fnt = Font()
fnt.name = 'Arial'
fnt.height= 275
#Define the font attributes for header
content_fnt = Font()
content_fnt.name ='Arial'
content_fnt.height =220
align_content = Alignment()
align_content.horz= Alignment.HORZ_CENTER
borders = Borders()
borders.left = 0x02
borders.right = 0x02
borders.top = 0x02
borders.bottom = 0x02
#The text should be centrally aligned
align = Alignment()
align.horz = Alignment.HORZ_CENTER
align.vert = Alignment.VERT_CENTER
#We set the backgroundcolour here
pattern = Pattern()
pattern.pattern = Pattern.SOLID_PATTERN
pattern.pattern_fore_colour = 0x1F
#apply the above settings to the row(0) header
style_header= XFStyle()
style_header.font= fnt
style_header.pattern= pattern
style_header.borders = borders
style_header.alignment=align
#Define the font attributes for header
fnt1 = Font()
fnt1.name = 'Arial'
fnt1.height= 275
#Define the font attributes for header
content_fnt1 = Font()
content_fnt1.name ='Arial'
content_fnt1.height =220
align_content1 = Alignment()
align_content1.horz= Alignment.HORZ_CENTER
borders1 = Borders()
borders1.left = 0x02
borders1.right = 0x02
borders1.top = 0x02
borders1.bottom = 0x02
#The text should be centrally aligned
align1 = Alignment()
align1.horz = Alignment.HORZ_CENTER
align1.vert = Alignment.VERT_CENTER
#We set the backgroundcolour here
pattern1 = Pattern()
pattern1.pattern = Pattern.SOLID_PATTERN
pattern1.pattern_fore_colour = 0x32
#apply the above settings to the row(0) header
style_header1= XFStyle()
style_header1.font= fnt1
style_header1.pattern= pattern1
style_header1.borders = borders1
style_header1.alignment=align1
style_content= XFStyle()
style_content.alignment = align_content
style_content.font = content_fnt
month_name = 'Payment ('+str(month_name)+')'
wb = Workbook()
ws = wb.add_sheet('Budget')
ws.row(0).height=500
ws.write(0,0,'Department Name',style_header)
ws.col(0).width = 8000
ws.write(0,1,'Department HoD',style_header)
ws.col(1).width = 8000
ws.write(0,2,'Employee Name',style_header)
ws.col(2).width = 8000
ws.write(0,3,'Salary Amount',style_header)
ws.col(3).width = 5000
ws.write(0,4,month_name,style_header)
ws.col(4).width = 8000
# ws.write(0,5,'O.T. Amount',style_header)
# ws.col(5).width = 4400
# ws.write(0,6,'Total Amount',style_header)
# ws.col(6).width = 4400
# ws.write(0,7,'Insentive Amount',style_header)
# ws.col(7).width = 4400
# ws.write(0,8,'Deduction Amount',style_header)
# ws.col(8).width = 5000
# ws.write(0,9,'Percentage',style_header)
# ws.col(9).width = 4400
emp_ids = []
emp_obj=self.pool.get('hr.employee')
pay_obj=self.pool.get('salary.payment.line')
if this.dept_id:
cr.execute("select emp.id from hr_employee as emp left join resource_resource\
as res on (emp.resource_id=res.id) where emp.department_id = \
'"+str(this.dept_id.id)+"' and emp.department_id is not null and \
res.active=True order by emp.department_id")
temp = cr.fetchall()
for data in temp:
if len(data)>0 and data[0] != None:
emp_ids.append(data[0])
else:
cr.execute("select emp.id from hr_employee as emp left join resource_resource \
as res on (emp.resource_id=res.id) where res.active=True order by emp.department_id")
temp = cr.fetchall()
for data in temp:
if len(data)>0 and data[0] != None:
emp_ids.append(data[0])
holiday_obj = self.pool.get('holiday.list')
if int(month) in [1,3,5,7,8,10,12]:
month = 31
if int(month) in [4,6,9,11]:
month = 30
if int(month) in [2]:
if int(year) % 4 == 0:
month = 29
else:
month = 28
off_day = working_day = 0
holiday_ids = holiday_obj.search(cr, uid, [('month','=',this.month),('year_id','=',year_id)])
for line in holiday_obj.browse(cr, uid, holiday_ids):
off_day = line.holiday
working_day = month - off_day
i=0
dept_dict = {}
grand = total = pay_total = pay_grand = budget = 0.0
pay_data = False
flag = True
for each in emp_obj.browse(cr, uid, emp_ids):
pay_ids = pay_obj.search(cr, uid, [('employee_id','=',each.id),('month','=',this.month),('year_id','=',this.year_id.id)])
if pay_ids:
pay_data = pay_obj.browse(cr, uid, pay_ids[0])
i+=1
if dept_dict.has_key(str(each.department_id.id)):
if each.department_id:
salary = 0.0
dept = '[' + str(each.department_id.dept_code) +'] '+ str(each.department_id.name)
ws.write(i,0, dept)
ws.write(i,1, each.department_id.manager_id and each.department_id.manager_id.name or '')
name = '[' + str(each.sinid) +'] '+ str(each.name)
ws.write(i,2, name)
if each.daily:
salary = each.salary * working_day
else:
salary = each.salary
ws.write(i,3, salary)
if pay_data and pay_data.employee_id.id == each.id:
ws.write(i,4,pay_data.total_amount or 0.0)
else:
ws.write(i,4, 0.0)
total += salary
grand += salary
if pay_data and pay_data.employee_id.id == each.id:
pay_total += pay_data.total_amount or 0.0
pay_grand += pay_data.total_amount or 0.0
else:
pay_total += 0.0
pay_grand += 0.0
elif not each.department_id:
salary = 0.0
if flag:
ws.write(i,0, 'Allocated Budget',style_header)
ws.write(i,1, 0.0,style_header)
ws.write(i,2, 'Total',style_header)
ws.write(i,3, total,style_header)
if pay_total:
ws.write(i,4,pay_total,style_header)
else:
ws.write(i,4, 0.0,style_header)
if budget:
diff = pay_total - float(budget)
else:
diff = pay_total
ws.write(i,5, diff,style_header1)
flag = False
i += 2
total = pay_total = budget = 0.0
name = '[' + str(each.sinid) +'] '+ str(each.name)
ws.write(i,0, 'X Department')
ws.write(i,1,'X Reporting Officer')
ws.write(i,2, name)
if each.daily:
salary = each.salary * working_day
else:
salary = each.salary
ws.write(i,3, salary)
if pay_data and pay_data.employee_id.id == each.id:
ws.write(i,4,pay_data.total_amount or 0.0)
else:
ws.write(i,4, 0.0)
total += salary
grand += salary
if pay_data and pay_data.employee_id.id == each.id:
pay_total += pay_data.total_amount or 0.0
pay_grand += pay_data.total_amount or 0.0
else:
pay_total += 0.0
pay_grand += 0.0
else:
dept_dict[str(each.department_id.id)] = ''
if i != 1:
ws.write(i,0, 'Allocated Budget',style_header)
if budget:
ws.write(i,1, budget,style_header)
else:
budget = 0.0
ws.write(i,1,budget,style_header)
ws.write(i,2, 'Total',style_header)
ws.write(i,3, total,style_header)
if pay_total:
ws.write(i,4,pay_total,style_header)
else:
ws.write(i,4, 0.0,style_header)
if budget:
diff = pay_total - float(budget)
else:
diff = pay_total
ws.write(i,5, diff,style_header1)
i += 2
total = pay_total = budget = 0.0
if each.department_id:
salary = 0.0
budget = each.department_id.dept_budget
dept = '[' + str(each.department_id.dept_code) +'] '+ str(each.department_id.name)
ws.write(i,0, dept)
ws.write(i,1, each.department_id.manager_id and each.department_id.manager_id.name or '')
name = '[' + str(each.sinid) +'] '+ str(each.name)
ws.write(i,2, name)
if each.daily:
salary = each.salary * working_day
else:
salary = each.salary
ws.write(i,3, salary)
if pay_data and pay_data.employee_id.id == each.id:
ws.write(i,4,pay_data.total_amount or 0.0)
else:
ws.write(i,4, 0.0)
total += salary
grand += salary
if pay_data and pay_data.employee_id.id == each.id:
pay_total += pay_data.total_amount or 0.0
pay_grand += pay_data.total_amount or 0.0
else:
pay_total += 0.0
pay_grand += 0.0
i += 1
ws.write(i+1,2, 'Total',style_header)
ws.write(i+1,3, total,style_header)
ws.write(i+3,2, 'Grand Total',style_header)
ws.write(i+3,3, grand,style_header)
ws.write(i+1,4, pay_total,style_header)
ws.write(i+3,4, pay_grand,style_header)
diff_pay = pay_total - total
diff_grand = pay_grand - grand
ws.write(i+1,5, diff_pay,style_header1)
ws.write(i+3,5, diff_grand,style_header1)
f = cStringIO.StringIO()
wb.save(f)
out=base64.encodestring(f.getvalue())
return self.write(cr, uid, ids, {'export_data':out, 'filename':'export.xls'}, context=context)
def report_get(self, cr, uid, ids, context=None):
this = self.browse(cr, uid, ids)
#Define the font attributes for header
fnt = Font()
fnt.name = 'Arial'
fnt.height = 275
#Define the font attributes for header
content_fnt = Font()
content_fnt.name = 'Arial'
content_fnt.height = 220
align_content = Alignment()
align_content.horz = Alignment.HORZ_LEFT
borders = Borders()
borders.left = 0x02
borders.right = 0x02
borders.top = 0x02
borders.bottom = 0x02
#The text should be centrally aligned
align = Alignment()
align.horz = Alignment.HORZ_LEFT
align.vert = Alignment.VERT_TOP
align.wrap = Alignment.WRAP_AT_RIGHT
#The text should be right aligned
align1 = Alignment()
align1.horz = Alignment.HORZ_RIGHT
align1.vert = Alignment.VERT_TOP
align1.wrap = Alignment.WRAP_AT_RIGHT
#The content should be left aligned
align2 = Alignment()
align2.horz = Alignment.HORZ_LEFT
align2.vert = Alignment.VERT_TOP
align2.wrap = Alignment.WRAP_AT_RIGHT
#The content should be right aligned
align3 = Alignment()
align3.horz = Alignment.HORZ_RIGHT
align3.vert = Alignment.VERT_TOP
align3.wrap = Alignment.WRAP_AT_RIGHT
#We set the backgroundcolour here
pattern = Pattern()
pattern.pattern = Pattern.SOLID_PATTERN
pattern.pattern_fore_colour = 0x1F
#We set the backgroundcolour here
pattern1 = Pattern()
pattern1.pattern = Pattern.SOLID_PATTERN
pattern1.pattern_fore_colour = 0x17
#We set the backgroundcolour here
pattern2 = Pattern()
pattern2.pattern = Pattern.SOLID_PATTERN
pattern2.pattern_fore_colour = 0xFF
#We set the backgroundcolour here
pattern3 = Pattern()
pattern3.pattern = Pattern.SOLID_PATTERN
pattern3.pattern_fore_colour = 0xFF
#apply the above settings to the row(0) header
style_header = XFStyle()
style_header.font = fnt
style_header.pattern = pattern
style_header.borders = borders
style_header.alignment = align
#apply the above settings to the row(1) header
style_header1 = XFStyle()
style_header1.font = fnt
style_header1.pattern = pattern1
style_header1.borders = borders
style_header1.alignment = align1
#apply the above settings to the content
style_content_left = XFStyle()
style_content_left.font = fnt
style_content_left.pattern = pattern2
style_content_left.borders = borders
style_content_left.alignment = align2
style_content_right = XFStyle()
style_content_right.font = fnt
style_content_right.pattern = pattern3
style_content_right.borders = borders
style_content_right.alignment = align3
style_content = XFStyle()
style_content.alignment = align_content
style_content.font = content_fnt
wb = Workbook()
ws = wb.add_sheet("Sheet 1")
ws.row(0).height = 3500
ws.write(0, 0, "(CO1)", style_header)
ws.col(0).width = 2000
ws.write(0, 1, "Month & Year (CO2)", style_header)
ws.col(1).width = 4500
ws.write(0, 2, "Buyer's TIN (CO3)", style_header)
ws.col(2).width = 4500
ws.write(0, 3, "Buyer's Name (CO4)", style_header)
ws.col(3).width = 10000
ws.write(0, 4, "Interstate Branch/Consignment Transfer (CO5)",
style_header)
ws.col(4).width = 4500
ws.write(0, 5, "Export Out Of India (CO6)", style_header)
ws.col(5).width = 4500
ws.write(0, 6, "High Sea Sales (CO7)", style_header)
ws.col(6).width = 4500
ws.write(0, 7, "ISS - Goods Type (CO8)", style_header)
ws.col(7).width = 4500
ws.write(0, 8, "ISS - Form Type (I) (CO9)", style_header)
ws.col(8).width = 4500
ws.write(0, 9, "ISS - Rate Of Tax (C1O)", style_header)
ws.col(9).width = 4500
ws.write(0, 10, "ISS - Sales Price (Excluding CST) (C11)",
style_header)
ws.col(10).width = 4500
ws.write(0, 11, "ISS - Central Sales Tax (C12)", style_header)
ws.col(11).width = 4500
ws.write(0, 12, "ISS - Total (C13)", style_header)
ws.col(12).width = 4500
ws.write(0, 13, "Local Sale - Type Of Sale (C14)", style_header)
ws.col(13).width = 4500
ws.write(0, 14, "Local Sale - Rate Of Tax (C15)", style_header)
ws.col(14).width = 4500
ws.write(0, 15, "Local Sale - Sale Price (Excluding VAT) (C16)",
style_header)
ws.col(15).width = 4500
ws.write(0, 16, "Local Sale - OutPut Tax (C17)", style_header)
ws.col(16).width = 4500
ws.write(0, 17, "Local Sale - Total (Including VAT) (C18)",
style_header)
ws.col(17).width = 4500
ws.row(1).height = 400
ws.write(1, 0, "0", style_header1)
ws.write(1, 1, "0", style_header1)
ws.write(1, 2, "0", style_header1)
ws.write(1, 3, "", style_header1)
ws.write(1, 4, "0.00", style_header1)
ws.write(1, 5, "0.00", style_header1)
ws.write(1, 6, "0.00", style_header1)
ws.write(1, 7, "", style_header1)
ws.write(1, 8, "", style_header1)
ws.write(1, 9, "0.00", style_header1)
ws.write(1, 10, "0.00", style_header1)
ws.write(1, 11, "0.00", style_header1)
ws.write(1, 12, "0.00", style_header1)
ws.write(1, 13, "", style_header1)
ws.write(1, 14, "0.00", style_header1)
ws.write(1, 15, "0.00", style_header1)
ws.write(1, 16, "0.00", style_header1)
ws.write(1, 17, "0.00", style_header1)
inv_obj = self.pool.get('account.invoice')
line_obj = self.pool.get('account.invoice.line')
row = 2
count = 1
value = 0.0
sum1 = sum2 = sum3 = sum4 = sum5 = sum6 = 0.0
period = []
for each in this:
if each.start_period_id.name > each.end_period_id.name:
raise osv.except_osv(
_('Warning !'),
_("Start period must be less than end period !"))
cr.execute("select id from account_period where name between '" +
str(each.start_period_id.name) + "' and '" +
str(each.end_period_id.name) + "'")
temp = cr.fetchall()
for val in temp:
if val:
period.append(val[0])
inv_ids = inv_obj.search(cr, uid,
[('type', '=', 'out_invoice'),
('state', 'not in', ['draft', 'cancel']),
('period_id', 'in', period)])
line_ids = line_obj.search(cr, uid,
[('invoice_id', 'in', inv_ids)])
for line in line_obj.browse(cr, uid, line_ids):
ws.row(row).height = 400
ws.write(row, 0, count, style_content_right)
ws.write(
row, 1, line.invoice_id.period_id
and line.invoice_id.period_id.name or False,
style_content_right)
ws.write(row, 2, line.partner_id.tin_no or '',
style_content_right)
ws.write(row, 3, line.partner_id.name, style_content_left)
for tax in line.invoice_line_tax_id:
value = tax.amount * 100
if line.invoice_id.address_invoice_id and line.invoice_id.address_invoice_id.city and line.invoice_id.address_invoice_id.city.lower(
) == 'new delhi' or line.invoice_id.address_invoice_id and line.invoice_id.address_invoice_id.city and line.invoice_id.address_invoice_id.city.lower(
) == 'delhi' or line.invoice_id.address_invoice_id and line.invoice_id.address_invoice_id.state_id and line.invoice_id.address_invoice_id.state_id.name.lower(
) == 'new delhi' or line.invoice_id.address_invoice_id and line.invoice_id.address_invoice_id.state_id and line.invoice_id.address_invoice_id.state_id.name.lower(
) == 'delhi':
ws.write(row, 14, round(value, 2), style_content_right)
ws.write(row, 15, round(line.price_subtotal, 2),
style_content_right)
sum1 += round(line.price_subtotal, 2)
ws.write(row, 16, round(line.price_subtotal * tax.amount,
2), style_content_right)
sum2 += round(line.price_subtotal * tax.amount, 2)
ws.write(
row, 17,
round(
line.price_subtotal +
(line.price_subtotal * tax.amount), 2),
style_content_right)
sum3 += round(
line.price_subtotal +
(line.price_subtotal * tax.amount), 2)
else:
ws.write(row, 9, round(value, 2), style_content_right)
ws.write(row, 10, round(line.price_subtotal, 2),
style_content_right)
sum4 += round(line.price_subtotal, 2)
ws.write(row, 11, round(line.price_subtotal * tax.amount,
2), style_content_right)
sum5 += round(line.price_subtotal * tax.amount, 2)
ws.write(
row, 12,
round(
line.price_subtotal +
(line.price_subtotal * tax.amount), 2),
style_content_right)
sum6 += round(
line.price_subtotal +
(line.price_subtotal * tax.amount), 2)
row += 1
count += 1
row += 2
ws.row(row).height = 400
ws.write(row, 0, "", style_header1)
ws.write(row, 1, "", style_header1)
ws.write(row, 2, "", style_header1)
ws.write(row, 3, "", style_header1)
ws.write(row, 4, "", style_header1)
ws.write(row, 5, "", style_header1)
ws.write(row, 6, "", style_header1)
ws.write(row, 7, "", style_header1)
ws.write(row, 8, "", style_header1)
ws.write(row, 9, "", style_header1)
ws.write(row, 10, sum4, style_header1)
ws.write(row, 11, sum5, style_header1)
ws.write(row, 12, sum6, style_header1)
ws.write(row, 13, "", style_header1)
ws.write(row, 14, "", style_header1)
ws.write(row, 15, sum1, style_header1)
ws.write(row, 16, sum2, style_header1)
ws.write(row, 17, sum3, style_header1)
f = cStringIO.StringIO()
wb.save(f)
out = base64.encodestring(f.getvalue())
return self.write(cr,
uid,
ids, {
'data': out,
'name': 'MARAnnexure2B.xls'
},
context=context)
title_bold = settings.EXPORT_EXCEL_TITLE_BOLD
if hasattr(settings, 'EXPORT_EXCEL_HEADER_BOLD'):
header_bold = settings.EXPORT_EXCEL_HEADER_BOLD
if hasattr(settings, 'EXPORT_EXCEL_CELL_BOLD'):
cell_bold = settings.EXPORT_EXCEL_CELL_BOLD
titleFont.bold = title_bold # Setting title font to bold
headerFont.bold = header_bold # Setting column header font to bold
cellFont.bold = cell_bold # Setting cell font to bold
titlesize = 240 # 12*20 = 240 Font Size
fontsize = 200# 10*20 = 200 Font Size
if hasattr(settings, 'EXPORT_EXCEL_TITLE_SIZE'):
titlesize = settings.EXPORT_EXCEL_TITLE_SIZE
if hasattr(settings, 'EXPORT_EXCEL_FONT_SIZE'):
fontsize = settings.EXPORT_EXCEL_FONT_SIZE
titleFont.height = titlesize
headerFont.height = fontsize
cellFont.height = fontsize
brdLeft = Borders() # Defining border which is around header
brdLeft.left = 0x01
brdRight = Borders() # Defining border which is around header
brdRight.right = 0x01
brdTop = Borders() # Defining border which is around header
brdTop.top = 0x01
brdBottom = Borders() # Defining border which is around header
brdBottom.bottom = 0x01
def output(self):
# from xlrd import open_workbook
from xlwt import Workbook, XFStyle, Borders, Alignment, Font, Pattern, Style, easyxf
# from xlutils.copy import copy
"""
rb = open_workbook(r"Templates\template.xls")
wb = copy(rb)
s = wb.get_sheet(0)
s.write(0, 0, 'A1')
wb.save(r"C:\Temp\nephro-planner\new.xls")
"""
book = Workbook(encoding="utf-8")
sheet = book.add_sheet(r"Feuille1")
style_title = XFStyle()
font_title = Font()
font_title.name = "Comic Sans MS"
font_title.height = 280
style_title.font = font_title
style_title.alignment.horz = Alignment.HORZ_CENTER
style_title.alignment.vert = Alignment.VERT_CENTER
style_cell_bottom = XFStyle()
borders_cell_bottom = Borders()
borders_cell_bottom.bottom = Borders.MEDIUM
style_cell_bottom.borders = borders_cell_bottom
style_header = XFStyle()
font_header = Font()
font_header.bold = 1
font_header.name = "Arial Narrow"
font_header.height = 240
style_header.font = font_header
style_header.alignment.horz = Alignment.HORZ_CENTER
style_header.alignment.vert = Alignment.VERT_CENTER
borders_header = Borders()
borders_header.top = Borders.MEDIUM
borders_header.left = Borders.MEDIUM
borders_header.bottom = Borders.MEDIUM
borders_header.right = Borders.MEDIUM
style_header.borders = borders_header
style_sub_header = XFStyle()
font_sub_header = Font()
font_sub_header.name = "Arial Narrow"
font_sub_header.height = 240
style_sub_header.font = font_sub_header
style_sub_header.alignment.horz = Alignment.HORZ_CENTER
style_sub_header.alignment.vert = Alignment.VERT_CENTER
borders_sub_header = Borders()
borders_sub_header.top = Borders.MEDIUM
borders_sub_header.left = Borders.MEDIUM
borders_sub_header.bottom = Borders.MEDIUM
borders_sub_header.right = Borders.MEDIUM
style_sub_header.borders = borders_sub_header
style_date = XFStyle()
font_date = Font()
font_date.name = "Arial Narrow"
font_date.height = 240
style_date.font = font_date
style_date.alignment.horz = Alignment.HORZ_RIGHT
style_date.alignment.vert = Alignment.VERT_CENTER
borders_date = Borders()
borders_date.left = Borders.MEDIUM
style_date.borders = borders_date
style_date_status = XFStyle()
font_date_status = Font()
font_date_status.name = "Arial Narrow"
font_date_status.height = 240
style_date_status.font = font_date_status
style_date_status.alignment.horz = Alignment.HORZ_LEFT
style_date_status.alignment.vert = Alignment.VERT_CENTER
borders_date_status = Borders()
borders_date_status.right = Borders.MEDIUM
style_date_status.borders = borders_date_status
style_cell_normal = XFStyle()
font_cell_normal = Font()
font_cell_normal.name = "Arial Narrow"
font_cell_normal.height = 220
style_cell_normal.font = font_cell_normal
style_cell_normal.alignment.horz = Alignment.HORZ_CENTER
style_cell_normal.alignment.vert = Alignment.VERT_CENTER
style_cell_right = XFStyle()
font_cell_right = Font()
font_cell_right.name = "Arial Narrow"
font_cell_right.height = 220
style_cell_right.font = font_cell_right
style_cell_right.alignment.horz = Alignment.HORZ_CENTER
style_cell_right.alignment.vert = Alignment.VERT_CENTER
borders_cell_right = Borders()
borders_cell_right.right = Borders.MEDIUM
style_cell_right.borders = borders_cell_right
style_cell_top = XFStyle()
borders_cell_top = Borders()
borders_cell_top.top = Borders.MEDIUM
style_cell_top.borders = borders_cell_top
column_offset = 1
date_column_offset = 2
row_offset = 1
table_width = 1 + 3 * len(Database.team())
# build titles
sheet.write_merge(row_offset, row_offset, column_offset, table_width + 1, "POLE MEDECINE INTERNE", style_title)
sheet.write_merge(
row_offset + 1,
row_offset + 1,
column_offset,
table_width + 1,
"Service NEPHROLOGIE – HEMODIALYSE",
style_title,
)
sheet.write_merge(
row_offset + 2,
row_offset + 2,
column_offset,
table_width + 1,
"Planning de {0} {1}".format(self.human_readable_months[self.month - 1], self.year),
style_title,
)
# patch date columns top borders
sheet.write(row_offset + 5, column_offset, "", style_cell_bottom)
sheet.write(row_offset + 5, column_offset + 1, "", style_cell_bottom)
# build header and sub header
for x in Database.team():
sheet.write_merge(
row_offset + 4,
row_offset + 4,
column_offset + date_column_offset + 3 * (x.id - 1),
column_offset + date_column_offset + 3 * x.id - 1,
x.name,
style_header,
)
sheet.write(row_offset + 5, column_offset + date_column_offset + 3 * (x.id - 1), "M", style_sub_header)
sheet.write(row_offset + 5, column_offset + date_column_offset + 3 * (x.id - 1) + 1, "AM", style_sub_header)
sheet.write(row_offset + 5, column_offset + date_column_offset + 3 * (x.id - 1) + 2, "N", style_sub_header)
"""
i = 40
for x in sorted(Style.colour_map):
style = XFStyle()
pattern = Pattern()
pattern.pattern = Pattern.SOLID_PATTERN
pattern.pattern_fore_colour = Style.colour_map[x]
style.pattern = pattern
sheet.write(i, 1, x, style)
i += 1
"""
pattern_pale_blue = Pattern()
pattern_pale_blue.pattern = Pattern.SOLID_PATTERN
pattern_pale_blue.pattern_fore_colour = Style.colour_map["pale_blue"]
pattern_light_yellow = Pattern()
pattern_light_yellow.pattern = Pattern.SOLID_PATTERN
pattern_light_yellow.pattern_fore_colour = Style.colour_map["light_yellow"]
pattern_ice_blue = Pattern()
pattern_ice_blue.pattern = Pattern.SOLID_PATTERN
pattern_ice_blue.pattern_fore_colour = Style.colour_map["ice_blue"]
pattern_light_green = Pattern()
pattern_light_green.pattern = Pattern.SOLID_PATTERN
pattern_light_green.pattern_fore_colour = Style.colour_map["light_green"]
pattern_ivory = Pattern()
pattern_ivory.pattern = Pattern.SOLID_PATTERN
pattern_ivory.pattern_fore_colour = Style.colour_map["ivory"]
pattern_tan = Pattern()
pattern_tan.pattern = Pattern.SOLID_PATTERN
pattern_tan.pattern_fore_colour = Style.colour_map["tan"]
pattern_gold = Pattern()
pattern_gold.pattern = Pattern.SOLID_PATTERN
pattern_gold.pattern_fore_colour = Style.colour_map["gold"]
def __cell_colouration__(style, current_activity):
if current_activity is Activity.CONSULTATION:
style.pattern = pattern_pale_blue
elif current_activity is Activity.DIALYSIS:
style.pattern = pattern_ice_blue
elif current_activity is Activity.NEPHROLOGY:
style.pattern = pattern_light_green
elif current_activity is Activity.OTHERS:
style.pattern = pattern_tan
elif current_activity is Activity.OBLIGATION:
style.pattern = pattern_ivory
elif current_activity is Activity.OBLIGATION_WEEKEND:
style.pattern = pattern_light_yellow
elif current_activity is Activity.OBLIGATION_HOLIDAY:
style.pattern = pattern_gold
else:
style.pattern = Pattern()
return style
last_day = calendar.monthrange(self.year, self.month)[1]
for x in range(1, last_day + 1):
current_date = date(self.year, self.month, x)
current_daily_planning = self.daily_plannings[current_date]
# build date and date status columns
sheet.write(
row_offset + 5 + x,
column_offset,
"{0}. {1}".format(self.human_readable_days[current_daily_planning.weekday][0:3].lower(), x),
style_date,
)
if current_daily_planning.weekday in [5, 6]:
sheet.write(row_offset + 5 + x, column_offset + 1, "WK", style_date_status)
elif not current_daily_planning.is_working_day:
sheet.write(row_offset + 5 + x, column_offset + 1, "Férié", style_date_status)
else:
sheet.write(row_offset + 5 + x, column_offset + 1, "", style_date_status)
"""
easyxf(
'font: bold 1, name Tahoma, height 160;'
'align: vertical center, horizontal center, wrap on;'
'borders: left thin, right thin, top thin, bottom thin;'
'pattern: pattern solid, pattern_fore_colour green, pattern_back_colour green'
)
"""
# fill in month planning
for y in Database.team():
current_activity = current_daily_planning.__get_activity__(TimeSlot.FIRST_SHIFT, y)
sheet.write(
row_offset + 5 + x,
column_offset + date_column_offset + 3 * (y.id - 1),
self.human_readable_activities[current_activity] if current_activity else "",
__cell_colouration__(style_cell_normal, current_activity),
)
current_activity = current_daily_planning.__get_activity__(TimeSlot.SECOND_SHIFT, y)
sheet.write(
row_offset + 5 + x,
column_offset + date_column_offset + 1 + 3 * (y.id - 1),
self.human_readable_activities[current_activity] if current_activity else "",
__cell_colouration__(style_cell_normal, current_activity),
)
current_activity = current_daily_planning.__get_activity__(TimeSlot.THIRD_SHIFT, y)
sheet.write(
row_offset + 5 + x,
column_offset + date_column_offset + 2 + 3 * (y.id - 1),
self.human_readable_activities[current_activity] if current_activity else "",
__cell_colouration__(style_cell_right, current_activity),
)
# patch table bottom border
for x in range(0, table_width + 1):
sheet.write(row_offset + 5 + (last_day + 1), column_offset + x, "", style_cell_top)
book.save(r"C:\Temp\nephro-planner\new.xls")
def print_report(self, cr, uid, ids, context=None):
wb = Workbook()
ws = wb.add_sheet('Earn Leave Report')
fnt1 = Font()
fnt1.name = 'Arial'
fnt1.height = 300
fnt1.bold = True
align_content1 = Alignment()
align_content1.horz = Alignment.HORZ_CENTER
borders1 = Borders()
borders1.left = 0x02
borders1.right = 0x02
borders1.top = 0x02
borders1.bottom = 0x02
align1 = Alignment()
align1.horz = Alignment.HORZ_CENTER
align1.vert = Alignment.VERT_CENTER
pattern1 = Pattern()
pattern1.pattern1 = Pattern.SOLID_PATTERN
pattern1.pattern1_fore_colour = 0x17
style_header1 = XFStyle()
style_header1.font = fnt1
style_header1.pattern = pattern1
style_header1.borders = borders1
style_header1.alignment = align1
fnt2 = Font()
fnt2.name = 'Arial'
fnt2.height = 250
fnt2.bold = False
align_content2 = Alignment()
align_content2.horz = Alignment.HORZ_CENTER
borders2 = Borders()
borders2.left = 0x02
borders2.right = 0x02
borders2.top = 0x02
borders2.bottom = 0x02
align2 = Alignment()
align2.horz = Alignment.HORZ_CENTER
align2.vert = Alignment.VERT_CENTER
pattern2 = Pattern()
pattern2.pattern2 = Pattern.SOLID_PATTERN
pattern2.pattern2_fore_colour = 0x09
style_header2 = XFStyle()
style_header2.font = fnt2
style_header2.pattern = pattern2
style_header2.borders = borders2
style_header2.alignment = align2
fnt3 = Font()
fnt3.name = 'Arial'
fnt3.height = 275
fnt3.bold = False
align_content3 = Alignment()
align_content3.horz = Alignment.HORZ_CENTER
borders3 = Borders()
borders3.left = 0x02
borders3.right = 0x02
borders3.top = 0x02
borders3.bottom = 0x02
align3 = Alignment()
align3.horz = Alignment.HORZ_CENTER
align3.vert = Alignment.VERT_CENTER
pattern3 = Pattern()
pattern3.pattern3 = Pattern.SOLID_PATTERN
pattern3.pattern3_fore_colour = 0x09
style_header3 = XFStyle()
style_header3.font = fnt3
style_header3.pattern = pattern3
style_header3.borders = borders3
style_header3.alignment = align3
fnt = Font()
fnt.name = 'Arial'
fnt.height = 275
content_fnt = Font()
content_fnt.name = 'Arial'
content_fnt.height = 150
align_content = Alignment()
align_content.horz = Alignment.HORZ_CENTER
borders = Borders()
borders.left = 0x02
borders.right = 0x02
borders.top = 0x02
borders.bottom = 0x02
align = Alignment()
align.horz = Alignment.HORZ_CENTER
align.vert = Alignment.VERT_CENTER
pattern = Pattern()
pattern.pattern = Pattern.SOLID_PATTERN
pattern.pattern_fore_colour = 0x16
style_header = XFStyle()
style_header.font = fnt
style_header.pattern = pattern
style_header.borders = borders
style_header.alignment = align
fnt5 = Font()
fnt5.name = 'Arial'
fnt5.height = 275
content_fnt5 = Font()
content_fnt5.name = 'Arial'
content_fnt5.height = 150
align_content5 = Alignment()
borders5 = Borders()
borders5.left = 0x02
borders5.right = 0x02
borders5.top = 0x02
borders5.bottom = 0x02
align5 = Alignment()
align5.vert = Alignment.VERT_JUSTIFIED
pattern5 = Pattern()
pattern5.pattern = Pattern.SOLID_PATTERN
pattern5.pattern_fore_colour = 0x16
style_header5 = XFStyle()
style_header5.font = fnt5
style_header5.pattern = pattern5
style_header5.borders = borders5
style_header5.alignment = align5
ws.row(0).height = 1000
ws.col(0).width = 4000
ws.col(1).width = 5000
ws.col(2).width = 4000
ws.col(3).width = 5000
ws.col(4).width = 5000
ws.col(5).width = 6500
ws.col(6).width = 4000
ws.col(7).width = 4000
ws.col(8).width = 4000
ws.write(0, 0, 'Employee ID', style_header)
ws.write(0, 1, 'Employee Code', style_header)
ws.write(0, 2, 'Earn Date', style_header)
ws.write(0, 3, 'Total Work Day', style_header)
ws.write(0, 4, 'Allocation EL', style_header)
ws.write(0, 5, 'Opening Earn Leave', style_header)
ws.write(0, 6, 'Total EL Paid', style_header)
ws.write(0, 7, 'Balance EL', style_header)
ws.write(0, 8, 'Import EL', style_header)
this = self.browse(cr, uid, ids[0], context=context)
company_id = this.company_id.id
employee_id = this.employee_id.id
emp_obj = self.pool.get('hr.employee')
year = this.month.year_id.id
balance1 = 0
balance = 0
import_el = 0
i = 1
if this.company_id and this.employee_id:
list_ids = emp_obj.search(cr, uid,
[('company_id', '=', company_id),
('id', '=', employee_id),
('active', '=', True),
('type', '=', 'Employee')])
elif this.company_id:
list_ids = emp_obj.search(cr, uid,
[('company_id', '=', company_id),
('active', '=', True),
('type', '=', 'Employee')])
if len(list_ids) == 0:
raise osv.except_osv(('Warning !'), ("Record Not Found !!!"))
if len(list_ids) == 1:
query = "select spl.sinid,sum(spl.work_day),hr.earn_date,sum(spl.earned_leave),hr.earn_open,hr.id from salary_payment_line as spl left join hr_employee as hr on spl.employee_id = hr.id left join resource_resource as rr on hr.resource_id = rr.id where spl.employee_id = '" + str(
list_ids[0]
) + "' and spl.year_id='" + str(
year
) + "' and rr.active = 'True' group by spl.sinid,hr.earn_date,hr.earn_open,hr.id order by spl.sinid"
cr.execute(query)
temp = cr.fetchall()
else:
query = "select spl.sinid,sum(spl.work_day),hr.earn_date,sum(spl.earned_leave),hr.earn_open,hr.id from salary_payment_line as spl left join hr_employee as hr on spl.employee_id = hr.id left join resource_resource as rr on hr.resource_id = rr.id where spl.employee_id in " + str(
tuple(list_ids)
) + " and spl.year_id='" + str(
year
) + "' and rr.active = 'True' group by spl.sinid,hr.earn_date,hr.earn_open,hr.id order by spl.sinid"
cr.execute(query)
temp = cr.fetchall()
if not temp:
raise osv.except_osv(_('Warning !'), _("Record Not Found !!!"))
for val in temp:
day = format((val[1] / 20), '.2f')
b = str(day)
c = b.split('.')
d = c[0]
e = c[1][0:2]
if int(e) < 50:
s = d + '.' + str(0)
work_day = float(s)
else:
s = d + '.' + e
t = float(s)
work_day = math.ceil(t)
if val[4] != None:
balance1 = (work_day + val[4] - val[3])
import_el = work_day + val[4]
else:
balance1 = (work_day - val[3])
import_el = work_day
if balance1 >= 30:
balance = 30
else:
balance = balance1
ws.row(i).height = 500
ws.write(i, 0, val[5], style_header2)
ws.write(i, 1, val[0], style_header2)
ws.write(i, 2, val[2], style_header2)
ws.write(i, 3, val[1], style_header2)
ws.write(i, 4, work_day, style_header2)
ws.write(i, 5, val[4], style_header2)
ws.write(i, 6, val[3], style_header2)
ws.write(i, 7, balance, style_header2)
ws.write(i, 8, import_el, style_header2)
i = i + 1
f = cStringIO.StringIO()
wb.save(f)
out = base64.encodestring(f.getvalue())
pf_upload_report = self.write(cr,
uid,
ids, {
'export_data': out,
'filename': 'Earn Leave Report.xls'
},
context=context)
return pf_upload_report
def salary_deduction_category(self, cr, uid, ids, context=None):
#============ ========= =============== =========================#
fnt = Font()
fnt.name = 'Ubuntu Medium'
fnt.size = 16
fnt.style = 'Regular'
#============ ======== ============= ============================#
#Define the font attributes for header
content_fnt = Font()
content_fnt.name = 'Ubuntu Medium'
content_fnt.size = 16
content_fnt.style = 'Regular'
align_content = Alignment()
align_content.horz = Alignment.HORZ_CENTER
borders = Borders()
borders.left = 0x01
borders.right = 0x01
borders.top = 0x01
borders.bottom = 0x01
#==============================================================#
#The text should be centrally aligned
align = Alignment()
align.horz = Alignment.HORZ_CENTER
align.vert = Alignment.VERT_CENTER
#We set the backgroundcolour here
pattern = Pattern()
pattern.pattern = Pattern.SOLID_PATTERN
pattern.pattern_fore_colour = 0x1F
#apply the above settings to the row(0) header
style_header = XFStyle()
style_header.font = fnt
style_header.pattern = pattern
style_header.borders = borders
style_header.alignment = align
#Define the font attributes for header
fnt1 = Font()
fnt1.name = 'Ubuntu Medium'
fnt1.size = 10
fnt1.style = 'Regular'
content_fnt1 = Font()
content_fnt1.name = 'Ubuntu Medium'
content_fnt1.style = 'Regular'
align_content1 = Alignment()
align_content1.horz = Alignment.HORZ_LEFT
borders1 = Borders()
borders1.left = 0x1
borders1.right = 0x1
borders1.top = 0x1
borders1.bottom = 0x1
#The text should be centrally aligned
align1 = Alignment()
align1.horz = Alignment.HORZ_CENTER
align1.vert = Alignment.VERT_TOP
#We set the backgroundcolour here
pattern1 = Pattern()
pattern1.pattern1 = Pattern.SOLID_PATTERN
pattern1.pattern_fore_colour = 0x16
#apply the above settings to the row(0) header
style_header1 = XFStyle()
style_header1.font = fnt1
style_header1.pattern = pattern1
style_header1.borders = borders1
style_header1.alignment = align1
#Define the font attributes for Content
fnt3 = Font()
fnt3.name = 'Arial'
fnt3.size = '10'
fnt3.style = 'Regular'
content_fnt3 = Font()
content_fnt3.name = 'Arial'
content_fnt3.style = 'Regular'
align_content3 = Alignment()
align_content3.horz = Alignment.HORZ_LEFT
borders3 = Borders()
borders3.left = 0x0
borders3.right = 0x0
borders3.top = 0x0
borders3.bottom = 0x0
#The text should be centrally aligned
align3 = Alignment()
align3.horz = Alignment.HORZ_CENTER
align3.vert = Alignment.VERT_TOP
#We set the backgroundcolour here
pattern3 = Pattern()
#apply the above settings to the row(0) header
style_header3 = XFStyle()
style_header3.font = fnt3
style_header3.pattern = pattern3
style_header3.borders = borders3
style_header3.alignment = align3
fnt5 = Font()
fnt5.name = 'Arial'
fnt.size = 10
content_fnt5 = Font()
content_fnt5.name = 'Arial'
align_content5 = Alignment()
# align_content5.horz= Alignment.HORZ_JUSTIFIED
borders5 = Borders()
borders5.left = 0x02
borders5.right = 0x02
borders5.top = 0x02
borders5.bottom = 0x02
align5 = Alignment()
# align5.horz = Alignment.HORZ_JUSTIFIED
align5.vert = Alignment.VERT_JUSTIFIED
pattern5 = Pattern()
pattern5.pattern = Pattern.SOLID_PATTERN
pattern5.pattern_fore_colour = 0x16
style_header5 = XFStyle()
style_header5.font = fnt5
style_header5.pattern = pattern5
style_header5.borders = borders5
style_header5.alignment = align5
fnt6 = Font()
fnt6.name = 'Arial'
fnt6.height = 300
fnt6.bold = True
align_content6 = Alignment()
align_content6.horz = Alignment.HORZ_CENTER
borders6 = Borders()
borders6.left = 0x02
borders6.right = 0x02
borders6.top = 0x02
borders6.bottom = 0x02
align6 = Alignment()
align6.horz = Alignment.HORZ_CENTER
align6.vert = Alignment.VERT_CENTER
pattern6 = Pattern()
pattern6.pattern6 = Pattern.SOLID_PATTERN
pattern6.pattern6_fore_colour = 0x16
style_header6 = XFStyle()
style_header6.font = fnt6
style_header6.pattern = pattern6
style_header6.borders = borders6
style_header6.alignment = align6
wb = Workbook()
ws = wb.add_sheet('Category Wise Deduction')
this = self.browse(cr, uid, ids[0], context=context)
year = this.month.year_id.id
month = this.month.month
emp_id = this.employee_id.id
company_id = this.company_id.id
employee_type = this.employee_type
employment_type = this.employment_type
ws.row(0).height = 300
ws.row(1).height = 300
ws.row(2).height = 300
ws.col(1).width = 8000
ws.col(2).width = 6000
ws.col(3).width = 6000
ws.col(5).width = 4000
ws.col(6).width = 4000
ws.col(7).width = 4000
ws.col(8).width = 4000
ws.col(9).width = 5000
ws.col(10).width = 5000
ws.write_merge(
0, 0, 0, 11, 'COMPANY : ' + this.company_id.name + ' ' +
this.company_id.street, style_header)
ws.write_merge(1, 1, 0, 11,
('SALARY CHART FOR THE MONTH OF :', this.month.name),
style_header)
ws.write_merge(2, 2, 4, 5, 'Income', style_header1)
ws.write_merge(2, 2, 6, 10, 'Deductions', style_header1)
ws.write(2, 11, 'Net Amount', style_header1)
ws.write(3, 0, 'PCard', style_header)
ws.write(3, 1, 'Employee Name', style_header)
ws.write(3, 2, 'Department Name', style_header)
ws.write(3, 3, 'Designation Name', style_header)
ws.write(3, 4, 'Total Salary', style_header)
ws.write(3, 5, 'OT Amount', style_header)
ws.write(3, 6, 'PF Deducted', style_header)
ws.write(3, 7, 'TDS Deducted', style_header)
ws.write(3, 8, 'ESI Deducted', style_header)
ws.write(3, 9, 'Professional Tax', style_header)
ws.write(3, 10, 'ADVANCE Deducted', style_header)
ws.write(3, 11, 'Net Amount', style_header)
i = 4
total_epf = 0.0
total_tds = 0.0
total_esi = 0.0
total_kharcha = 0.0
total_ot_salary = 0.0
total_a_gross = 0.0
total_grand_payment = 0.0
total_professional_tax = 0.0
list_val = []
list_ids = []
emp_obj = self.pool.get('hr.employee')
if this.employee_id and this.employee_type:
ws.write_merge(2, 2, 1, 2,
('Employee Type : ', this.employee_type),
style_header1)
list_ids = emp_obj.search(cr, uid,
[('id', '=', emp_id),
('active', '=', True),
('employee_type', '=', employee_type),
('type', '=', 'Employee')])
elif this.employee_id and this.employment_type:
ws.write_merge(2, 2, 1, 2,
('Employment Type : ', this.employment_type),
style_header1)
list_ids = emp_obj.search(
cr, uid, [('id', '=', emp_id), ('active', '=', True),
('employment_type', '=', employment_type),
('type', '=', 'Employee')])
elif this.company_ids and this.employee_type:
ws.write_merge(2, 2, 1, 2,
('Employee Type : ', this.employee_type),
style_header1)
for val in this.company_ids:
list_val = emp_obj.search(
cr, uid, [('active', '=', True),
('company_id', '=', val.id),
('employee_type', '=', employee_type),
('type', '=', 'Employee')])
list_ids = list_val + list_ids
elif this.company_ids and this.employment_type:
ws.write_merge(2, 2, 1, 2,
('Employment Type : ', this.employment_type),
style_header1)
for val in this.company_ids:
list_val = emp_obj.search(
cr, uid, [('active', '=', True),
('company_id', '=', val.id),
('employment_type', '=', employment_type),
('type', '=', 'Employee')])
list_ids = list_val + list_ids
elif not this.employee_type and not this.employment_type:
raise osv.except_osv(
('Warning !'),
("Please Select Employee or Employment Type !!!"))
elif this.employee_type:
ws.write_merge(2, 2, 1, 2,
('Employee Type : ', this.employee_type),
style_header1)
list_ids = emp_obj.search(cr, uid,
[('active', '=', True),
('employee_type', '=', employee_type),
('type', '=', 'Employee')])
elif this.employment_type:
ws.write_merge(2, 2, 1, 2,
('Employment Type : ', this.employment_type),
style_header1)
list_ids = emp_obj.search(
cr, uid, [('active', '=', True),
('employment_type', '=', employment_type),
('type', '=', 'Employee')])
else:
list_ids = emp_obj.search(cr, uid, [('active', '=', True),
('type', '=', 'Employee')])
if len(list_ids) == 0:
raise osv.except_osv(('Warning !'), ("Record Not Found !!!"))
if len(list_ids) == 1:
query ="select spl.employee_name,spl.sinid,spl.department_name,spl.job_name,spl.epf,spl.tds,spl.esi,spl.kharcha,sum(spl.overtime_amount+spl.sun_overtime_amount),sum(spl.days_amount+spl.other_salary_amount),spl.grand_total,spl.pro_tax_amt"\
" from salary_payment_line as spl where spl.employee_id = '"+str(list_ids[0])+"' and spl.month='"+str(month)+"' and spl.year_id='"+str(year)+"' group by "\
" spl.employee_name,spl.sinid,spl.department_name,spl.job_name,spl.epf,spl.tds,spl.esi,spl.kharcha,spl.grand_total,spl.pro_tax_amt order by spl.sinid "
cr.execute(query)
temp = cr.fetchall()
else:
query ="select spl.employee_name,spl.sinid,spl.department_name,spl.job_name,spl.epf,spl.tds,spl.esi,spl.kharcha,sum(spl.overtime_amount+spl.sun_overtime_amount),sum(spl.days_amount+spl.other_salary_amount),spl.grand_total,spl.pro_tax_amt"\
" from salary_payment_line as spl where spl.employee_id in "+str(tuple(list_ids))+" and spl.month='"+str(month)+"' and spl.year_id='"+str(year)+"' group by " \
" spl.employee_name,spl.sinid,spl.department_name,spl.job_name,spl.epf,spl.tds,spl.esi,spl.kharcha,spl.grand_total,spl.pro_tax_amt order by spl.sinid"
cr.execute(query)
temp = cr.fetchall()
if not temp:
raise osv.except_osv(_('Warning !'), _("Record Not Found !!!"))
for val in temp:
total_epf += val[4]
total_tds += val[5]
total_esi += val[6]
total_kharcha += val[7]
total_ot_salary += val[8]
total_a_gross += val[9]
total_grand_payment += val[10]
total_professional_tax += val[11]
ws.write(i, 0, (val[1]), style_header3)
ws.write(i, 1, (val[0]), style_header3)
ws.write(i, 2, (val[2]), style_header3)
ws.write(i, 3, (val[3]), style_header3)
ws.write(i, 4, (val[9]), style_header3)
ws.write(i, 5, (val[8]), style_header3)
ws.write(i, 6, (val[4]), style_header3)
ws.write(i, 7, (val[5]), style_header3)
ws.write(i, 8, (val[6]), style_header3)
ws.write(i, 9, (val[11]), style_header3)
ws.write(i, 10, (val[7]), style_header3)
ws.write(i, 11, (val[10]), style_header3)
i = i + 1
ws.write(i, 3, 'TOTAL', style_header5)
ws.write(i, 4, total_a_gross, style_header5)
ws.write(i, 5, total_ot_salary, style_header5)
ws.write(i, 6, total_epf, style_header5)
ws.write(i, 7, total_tds, style_header5)
ws.write(i, 8, total_esi, style_header5)
ws.write(i, 9, total_professional_tax, style_header5)
ws.write(i, 10, total_kharcha, style_header5)
ws.write(i, 11, total_grand_payment, style_header5)
f = cStringIO.StringIO()
wb.save(f)
out = base64.encodestring(f.getvalue())
ot_report = self.write(cr,
uid,
ids, {
'export_data': out,
'filename': 'Salary CHART.xls'
},
context=context)
return ot_report
def produce_summary(self, cr, uid, travel, context=None):
number_format = _('#,##0.00 [$$-C0C];-#,##0.00 [$$-C0C]')
total_fnt = Font()
total_fnt.name = 'Calibri'
total_fnt.bold = True
total_fnt.height = 16 * 20 # font size 12
total_cell_l_style = XFStyle()
total_cell_l_style.alignment = Column.title_aln
total_cell_l_style.borders = Borders()
total_cell_l_style.borders.left = Borders.THICK
total_cell_l_style.borders.right = Borders.HAIR
total_cell_l_style.borders.top = Borders.THICK
total_cell_l_style.borders.bottom = Borders.THICK
total_cell_l_style.pattern = Column.title_ptn
total_cell_l_style.num_format_str = number_format
total_cell_l_style.font = Column.obj_fnt
total_cell_r_style = XFStyle()
total_cell_r_style.alignment = Column.title_aln
total_cell_r_style.borders = Borders()
total_cell_r_style.borders.left = Borders.HAIR
total_cell_r_style.borders.right = Borders.THICK
total_cell_r_style.borders.top = Borders.THICK
total_cell_r_style.borders.bottom = Borders.THICK
total_cell_r_style.pattern = Column.title_ptn
total_cell_r_style.num_format_str = number_format
total_cell_r_style.font = Column.obj_fnt
sub_total_cell_label = Cell(
_(u'SOUS-TOTAL'), Column.title_fnt, Column.title_aln,
total_cell_l_style.borders, Column.title_ptn)
total_cell_label = Cell(
_(u'TOTAL'), total_fnt, Column.title_aln,
total_cell_r_style.borders, Column.title_ptn, number_format)
journeys = [j for i in travel.passenger_ids for j in i.journey_ids]
w = Workbook()
ws = w.add_sheet(_('Travel Summary'))
ws.row(2).height = 0x0280
ws.row(3 + len(journeys)).height = 0x0140
ws.row(4 + len(journeys)).height = 0x0180
row = 0
row += 2
_excel_columns = self.get_excel_columns(context)
# Write headers
for i, col in enumerate(_excel_columns):
ws.col(i).width = col.width
ws.write(row, i, col.text, col.style)
row += 1
for i, obj in enumerate(journeys):
ws.write(row + i, 0, i + 1, _excel_columns[0].obj_style)
for j in xrange(1, len(_excel_columns)):
ws.write(row + i, j,
_excel_columns[j].func(obj),
_excel_columns[j].obj_style)
row += len(journeys)
rate_index = [i for i, x in enumerate(_excel_columns)
if x.text == _('TICKET RATE')][0] - 1
cost_index = [i for i, x in enumerate(_excel_columns)
if x.text == _('COSTS')][0] - 1
total_index = [i for i, x in enumerate(_excel_columns)
if x.text == _('TOTAL')][0] - 1
# Sub total label
ws.write_merge(row, row, 0, rate_index,
sub_total_cell_label.text,
sub_total_cell_label.style)
# Sub totals
ws.write(row, rate_index + 1,
Formula("SUM(%s%d:%s%d)" % (chr(66 + rate_index), 4,
chr(66 + rate_index), row)),
total_cell_l_style)
ws.write(row, rate_index + 2,
Formula("SUM(%s%d:%s%d)" % (chr(66 + cost_index), 4,
chr(66 + cost_index), row)),
total_cell_r_style)
total_top_underline_style = XFStyle()
total_top_underline_style.borders = Borders()
total_top_underline_style.borders.top = Borders.THICK
total_top_underline_style.font = Column.obj_fnt
# Draw a line above total to close box
ws.write(row, total_index + 1, "", total_top_underline_style)
row += 1
# Total label
ws.write_merge(row, row, 0, rate_index,
total_cell_label.text,
total_cell_label.style)
# Total
ws.write_merge(row, row, rate_index + 1, total_index,
Formula("%s%d+%s%d" % (chr(66 + rate_index), row,
chr(66 + cost_index), row)),
total_cell_label.style)
return w
def xx(list):
n = 5
num = 1
oldWb = xlrd.open_workbook('D:\\分红\\模板.xls',
formatting_info=True) # 先打开已存在的表
newWb = copy(oldWb) # 复制
sheet6 = newWb.get_sheet(0)
# 新建样式
style1 = XFStyle()
style2 = XFStyle()
style3 = XFStyle()
style4 = XFStyle()
style5 = XFStyle()
# 设置边框1
borders1 = xlwt.Borders()
borders1.left = xlwt.Borders.THIN
borders1.right = xlwt.Borders.THIN
borders1.top = xlwt.Borders.THIN
borders1.bottom = xlwt.Borders.THIN
# 设置边框2
borders2 = xlwt.Borders()
borders2.left = xlwt.Borders.THIN
borders2.right = xlwt.Borders.HAIR
borders2.top = xlwt.Borders.HAIR
# 设置边框3
borders3 = xlwt.Borders()
borders3.left = xlwt.Borders.HAIR
borders3.right = xlwt.Borders.HAIR
borders3.top = xlwt.Borders.HAIR
# 设置边框4
borders4 = xlwt.Borders()
borders4.left = xlwt.Borders.HAIR
borders4.right = xlwt.Borders.THIN
borders4.top = xlwt.Borders.HAIR
# 设置边框5
borders5 = xlwt.Borders()
borders5.top = xlwt.Borders.THIN
# 设置字体格式fnt
fnt = Font()
fnt.name = u'微软雅黑'
fnt.height = 220
# 设置对其al（居中）
al = xlwt.Alignment()
al.horz = 0x02 # 设置水平居中
al.vert = 0x01 # 设置垂直居中
al.wrap = 1
style1.alignment = al
style1.font = fnt
style1.borders = borders1
style2.alignment = al
style2.font = fnt
style2.borders = borders2
style3.alignment = al
style3.font = fnt
style3.borders = borders3
style4.alignment = al
style4.font = fnt
style4.borders = borders4
style5.borders = borders5
#写入第一个sheet数据
for i in range(1, len(list) + 1):
sheet6.write(2, i, list[i - 1], style1)
#写入第二个sheet数据
for r1 in range(2, row1):
card4 = sheet1.cell(r1, 2).value
if card4 == list[0]:
card6 = sheet1.cell(r1, 4).value
name4 = sheet1.cell(r1, 5).value
pgv = sheet1.cell(r1, 6).value
db = sheet1.cell(r1, 7).value
sheet6.write(n, 1, num, style2)
sheet6.write(n, 2, card6, style3)
sheet6.write(n, 3, name4, style3)
sheet6.write(n, 4, pgv, style3)
sheet6.write(n, 5, db, style4)
num = num + 1
n = n + 1
sheet6.write(n, 1, '', style5)
sheet6.write(n, 2, '', style5)
sheet6.write(n, 3, '', style5)
sheet6.write(n, 4, '', style5)
sheet6.write(n, 5, '', style5)
file_name = 'D:\\分红\\fh1\\' + name3 + '.xls'
newWb.save(file_name) # 保存
from xlrd import open_workbook
from xlutils.copy import copy
from xlwt import XFStyle, Font
template = open_workbook('act.xls', formatting_info=1)
new_file = copy(template)
style_1 = XFStyle()
font = Font()
# насройка объектов стиля и шрифта
style_1.alignment.horz = 2
font.name = 'Times New Roman'
font.bold = 'true'
font.height = 240
style_1.font = font
sheet = new_file.get_sheet(0)
sheet.write(10, 1, 'Акт №10 от 10 июля 2020 г.', style_1)
new_file.save('new_act.xls')
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 performance_register_report(self, cr, uid, ids, data, context=None):
obj = self.browse(cr, uid, ids)
emp_obj = self.pool.get('hr.employee')
f_name = ''
d_name = ''
wb = Workbook()
ws = wb.add_sheet('Payment Bonus')
total_salary = apr_salary = may_salary = june_salary = july_salary = aug_salary = sep_salary = oct_salary = nov_salary = dec_salary = jan_salary = feb_salary = mar_salary = 0
fnt1 = Font()
fnt1.name = 'Arial'
fnt1.height = 300
fnt1.bold = True
align_content1 = Alignment()
align_content1.horz = Alignment.HORZ_CENTER
borders1 = Borders()
borders1.left = 0x00
borders1.right = 0x00
borders1.top = 0x00
borders1.bottom = 0x00
align1 = Alignment()
align1.horz = Alignment.HORZ_CENTER
align1.vert = Alignment.VERT_CENTER
pattern1 = Pattern()
pattern1.pattern1 = Pattern.SOLID_PATTERN
pattern1.pattern1_fore_colour = 0x1F
style_header1 = XFStyle()
style_header1.font = fnt1
style_header1.pattern = pattern1
style_header1.borders = borders1
style_header1.alignment = align1
fnt = Font()
fnt.name = 'Arial'
fnt.height = 275
content_fnt = Font()
content_fnt.name = 'Arial'
content_fnt.height = 150
align_content = Alignment()
align_content.horz = Alignment.HORZ_CENTER
borders = Borders()
borders.left = 0x02
borders.right = 0x02
borders.top = 0x02
borders.bottom = 0x00
align = Alignment()
align.horz = Alignment.HORZ_CENTER
align.vert = Alignment.VERT_CENTER
pattern = Pattern()
pattern.pattern = Pattern.SOLID_PATTERN
pattern.pattern_fore_colour = 0x1F
style_header = XFStyle()
style_header.font = fnt
style_header.pattern = pattern
style_header.borders = borders
style_header.alignment = align
fnt7 = Font()
fnt7.name = 'Arial'
fnt7.height = 275
content_fnt7 = Font()
content_fnt7.name = 'Arial'
content_fnt7.height = 150
align_content7 = Alignment()
align_content7.horz = Alignment.HORZ_CENTER
borders7 = Borders()
borders7.left = 0x02
borders7.right = 0x02
borders7.top = 0x02
borders7.bottom = 0x02
align7 = Alignment()
align7.horz = Alignment.HORZ_CENTER
align7.vert = Alignment.VERT_CENTER
pattern7 = Pattern()
pattern7.pattern7 = Pattern.SOLID_PATTERN
pattern7.pattern7_fore_colour = 0x1F
style_header7 = XFStyle()
style_header7.font = fnt
style_header7.pattern = pattern
style_header7.borders = borders
style_header7.alignment = align
fnt2 = Font()
fnt2.name = 'Arial'
fnt2.height = 275
content_fnt2 = Font()
content_fnt2.name = 'Arial'
content_fnt2.height = 150
fnt2.bold = True
align_content2 = Alignment()
align_content2.horz = Alignment.HORZ_CENTER
borders2 = Borders()
borders2.left = 0x00
borders2.right = 0x02
borders2.top = 0x02
borders2.bottom = 0x02
align2 = Alignment()
align2.horz = Alignment.HORZ_CENTER
align2.vert = Alignment.VERT_CENTER
pattern2 = Pattern()
pattern2.pattern2 = Pattern.SOLID_PATTERN
pattern2.pattern2_fore_colour = 0x1F
style_header2 = XFStyle()
style_header2.font = fnt2
style_header2.pattern = pattern2
style_header2.borders = borders2
style_header2.alignment = align2
fnt3 = Font()
fnt3.name = 'Arial'
fnt3.height = 300
fnt3.bold = True
align_content3 = Alignment()
align_content3.horz = Alignment.HORZ_CENTER
borders3 = Borders()
borders3.left = 0x00
borders3.right = 0x02
borders3.top = 0x02
borders3.bottom = 0x02
align3 = Alignment()
align3.horz = Alignment.HORZ_CENTER
align3.vert = Alignment.VERT_CENTER
pattern3 = Pattern()
pattern3.pattern3 = Pattern.SOLID_PATTERN
pattern3.pattern3_fore_colour = 0x1F
style_header3 = XFStyle()
style_header3.font = fnt3
style_header3.pattern = pattern3
style_header3.borders = borders3
style_header3.alignment = align3
fnt6 = Font()
fnt6.name = 'Arial'
fnt6.height = 275
content_fnt6 = Font()
content_fnt6.name = 'Arial'
content_fnt6.height = 150
align_content6 = Alignment()
align_content6.horz = Alignment.HORZ_CENTER
borders6 = Borders()
borders6.left = 0x02
borders6.right = 0x02
# borders6.top = 0x00
# borders6.bottom = 0x00
align6 = Alignment()
align6.horz = Alignment.HORZ_CENTER
align6.vert = Alignment.VERT_CENTER
pattern6 = Pattern()
pattern6.pattern = Pattern.SOLID_PATTERN
pattern6.pattern_fore_colour = 0x1F
style_header6 = XFStyle()
style_header6.font = fnt
style_header6.pattern = pattern
style_header6.borders = borders6
style_header6.alignment = align
fnt5 = Font()
fnt5.name = 'Arial'
fnt5.height = 200
content_fnt5 = Font()
content_fnt5.name = 'Arial'
content_fnt5.height = 150
align_content5 = Alignment()
align_content5.horz = Alignment.HORZ_CENTER
borders5 = Borders()
borders5.left = 0x02
borders5.right = 0x02
borders5.top = 0x02
borders5.bottom = 0x02
align5 = Alignment()
align5.horz = Alignment.HORZ_CENTER
align5.vert = Alignment.VERT_CENTER
pattern5 = Pattern()
# pattern5.pattern = Pattern.SOLID_PATTERN
# pattern5.pattern_fore_colour = 0x1F
style_header5 = XFStyle()
style_header5.font = fnt5
style_header5.pattern = pattern5
style_header5.borders = borders5
style_header5.alignment = align5
if obj.company_id:
get_name = obj.company_id.name + ' ' + obj.company_id.street + ' ' + ',' + obj.company_id.city + ' ' + '-' + obj.company_id.zip
else:
get_name = obj.employee_id.resource_id.company_id.name + ' ' + obj.employee_id.resource_id.company_id.street + ' ' + ',' + obj.employee_id.resource_id.company_id.city + ' ' + '-' + obj.employee_id.resource_id.company_id.zip
date1 = datetime.strptime(obj.from_date,
"%Y-%m-%d").timetuple().tm_year
date2 = datetime.strptime(obj.till_date,
"%Y-%m-%d").timetuple().tm_year
if date1 == date2:
d_name = 'BONUS PAID TO EMPLOYEES FOR THE ACCOUNTING YEAR ON THE' + ' - ' + str(
date1) + ' ' + '[See Rule 4(b)]'
else:
d_name = 'BONUS PAID TO EMPLOYEES FOR THE ACCOUNTING YEAR ON THE' + ' ' + str(
date1) + ' ' + '-' + ' ' + str(date2) + ' ' + '[See Rule 4(b)]'
ws.row(0).height = 500
ws.row(1).height = 500
ws.write_merge(0, 0, 0, 2, 'FORM C', style_header1)
ws.write_merge(0, 0, 3, 10, d_name, style_header1)
ws.write_merge(1, 1, 0, 2, 'Name of the Establishment', style_header1)
ws.write_merge(1, 1, 3, 10, get_name, style_header1)
ws.write_merge(1, 1, 11, 19, 'No. of Working days in the Year',
style_header1)
ws.col(0).width = 5000
ws.col(1).width = 7500
ws.col(2).width = 5000
ws.col(3).width = 7000
ws.col(4).width = 5000
ws.col(5).width = 8000
ws.col(6).width = 6000
ws.col(7).width = 8000
ws.col(8).width = 8000
ws.col(9).width = 9000
ws.col(10).width = 8000
ws.col(11).width = 9000
ws.col(12).width = 7000
ws.col(13).width = 7000
ws.col(14).width = 5000
ws.col(15).width = 5000
ws.col(16).width = 7000
ws.col(17).width = 3000
ws.col(18).width = 3000
ws.col(19).width = 3000
ws.col(20).width = 3000
ws.col(21).width = 3000
ws.col(22).width = 3000
ws.col(23).width = 3000
ws.col(24).width = 3000
ws.col(25).width = 3000
ws.col(26).width = 3000
ws.col(27).width = 3000
ws.col(28).width = 3000
ws.col(29).width = 4000
ws.row(2).height = 400
ws.write(2, 0, 'EMP. CODE', style_header)
ws.write(2, 1, 'NAME', style_header)
ws.write(2, 2, 'JOINING DATE', style_header)
ws.write(2, 3, 'Father Name', style_header)
ws.write(2, 4, 'Designation', style_header)
ws.write(2, 5, 'Whether he has ', style_header)
ws.write(2, 6, 'No.of days', style_header)
ws.write(2, 7, 'Total Salary or wages', style_header)
ws.write(2, 8, 'Account of bonus payable', style_header)
ws.write_merge(2, 2, 9, 12, 'Deduction', style_header7)
ws.write(2, 13, 'Net amount payable', style_header)
ws.write(2, 14, 'Amount actualy', style_header)
ws.write(2, 15, 'Date on which ', style_header)
ws.write(2, 16, 'Signature/ Thumb ', style_header)
ws.write(2, 17, 'Remarks', style_header)
ws.write_merge(2, 2, 18, 19, 'APRIL', style_header7)
ws.write_merge(2, 2, 20, 21, 'MAY', style_header7)
ws.write_merge(2, 2, 22, 23, 'JUNE', style_header7)
ws.write_merge(2, 2, 24, 25, 'JULY', style_header7)
ws.write_merge(2, 2, 26, 27, 'AUGUST', style_header7)
ws.write_merge(2, 2, 28, 29, 'SEPTEMBER', style_header7)
ws.write_merge(2, 2, 30, 31, 'OCTOBER', style_header7)
ws.write_merge(2, 2, 32, 33, 'NOVEMBER', style_header7)
ws.write_merge(2, 2, 34, 35, 'DECEMBER', style_header7)
ws.write_merge(2, 2, 36, 37, 'JANUARY', style_header7)
ws.write_merge(2, 2, 38, 39, 'FEBRUARY', style_header7)
ws.write_merge(2, 2, 40, 41, 'MARCH', style_header7)
ws.write_merge(2, 2, 42, 43, 'TOTAL', style_header7)
ws.write(2, 44, 'BONUS', style_header)
ws.row(3).height = 400
ws.write(3, 0, '', style_header6)
ws.write(3, 1, '', style_header6)
ws.write(3, 2, '', style_header6)
ws.write(3, 3, '', style_header6)
ws.write(3, 4, '', style_header6)
ws.write(3, 5, 'completed 15 year of', style_header6)
ws.write(3, 6, 'worked in the', style_header6)
ws.write(3, 7, 'in respect of', style_header6)
ws.write(3, 8, 'under section 10', style_header6)
ws.write(3, 9, 'Puja bonus or other customary', style_header7)
ws.write(3, 10, 'Interim bonus', style_header7)
ws.write(3, 11, 'Deduction on account of financial', style_header7)
ws.write(3, 12, 'Total sum deducted', style_header7)
ws.write(3, 13, '(Col.8 minus Col.12)', style_header6)
ws.write(3, 14, 'paid', style_header6)
ws.write(3, 15, 'paid', style_header6)
ws.write(3, 16, 'impression', style_header6)
ws.write(3, 17, '', style_header6)
ws.write(3, 18, '', style_header7)
ws.write(3, 19, '', style_header7)
ws.write(3, 20, '', style_header7)
ws.write(3, 21, '', style_header7)
ws.write(3, 22, '', style_header7)
ws.write(3, 23, '', style_header7)
ws.write(3, 24, '', style_header7)
ws.write(3, 25, '', style_header7)
ws.write(3, 26, '', style_header7)
ws.write(3, 27, '', style_header7)
ws.write(3, 28, '', style_header7)
ws.write(3, 29, '', style_header7)
ws.write(3, 30, '', style_header7)
ws.write(3, 31, '', style_header7)
ws.write(3, 32, '', style_header7)
ws.write(3, 33, '', style_header7)
ws.write(3, 34, '', style_header7)
ws.write(3, 35, '', style_header7)
ws.write(3, 36, '', style_header7)
ws.write(3, 37, '', style_header7)
ws.write(3, 38, '', style_header7)
ws.write(3, 39, '', style_header7)
ws.write(3, 40, '', style_header7)
ws.write(3, 41, '', style_header7)
ws.write(3, 42, '', style_header7)
ws.write(3, 43, '', style_header7)
ws.write(3, 44, '', style_header7)
ws.row(4).height = 400
ws.write(4, 0, '', style_header6)
ws.write(4, 1, '', style_header6)
ws.write(4, 2, '', style_header6)
ws.write(4, 3, '', style_header6)
ws.write(4, 4, '', style_header6)
ws.write(4, 5, 'age at the beginning', style_header6)
ws.write(4, 6, 'Establishment', style_header6)
ws.write(4, 7, 'the accounting year', style_header6)
ws.write(4, 8, 'or section 11', style_header6)
ws.write(4, 9, 'bonus paid during', style_header6)
ws.write(4, 10, 'or bonus paid in ', style_header6)
ws.write(4, 11, 'loss if any caused by', style_header6)
ws.write(4, 12, '(Col.9,10 and 11)', style_header6)
ws.write(4, 13, '', style_header6)
ws.write(4, 14, '', style_header6)
ws.write(4, 15, '', style_header6)
ws.write(4, 16, 'of the employee', style_header6)
ws.write(4, 17, '', style_header6)
ws.write(4, 18, '', style_header6)
ws.write(4, 19, '', style_header6)
ws.write(4, 20, '', style_header6)
ws.write(4, 21, '', style_header6)
ws.write(4, 22, '', style_header6)
ws.write(4, 23, '', style_header6)
ws.write(4, 24, '', style_header6)
ws.write(4, 25, '', style_header6)
ws.write(4, 26, '', style_header6)
ws.write(4, 27, '', style_header6)
ws.write(4, 28, '', style_header6)
ws.write(4, 29, '', style_header6)
ws.write(4, 30, '', style_header6)
ws.write(4, 31, '', style_header6)
ws.write(4, 32, '', style_header6)
ws.write(4, 33, '', style_header6)
ws.write(4, 34, '', style_header6)
ws.write(4, 35, '', style_header6)
ws.write(4, 36, '', style_header6)
ws.write(4, 37, '', style_header6)
ws.write(4, 38, '', style_header6)
ws.write(4, 39, '', style_header6)
ws.write(4, 40, '', style_header6)
ws.write(4, 41, '', style_header6)
ws.write(4, 42, '', style_header6)
ws.write(4, 43, '', style_header6)
ws.write(4, 44, '', style_header6)
ws.row(5).height = 400
ws.write(5, 0, '', style_header6)
ws.write(5, 1, '', style_header6)
ws.write(5, 2, '', style_header6)
ws.write(5, 3, '', style_header6)
ws.write(5, 4, '', style_header6)
ws.write(5, 5, 'of the accounting year', style_header6)
ws.write(5, 6, '', style_header6)
ws.write(5, 7, '', style_header6)
ws.write(5, 8, 'as the case may be', style_header6)
ws.write(5, 9, 'the accounting year', style_header6)
ws.write(5, 10, 'advance', style_header6)
ws.write(5, 11, 'misconduct of the employee', style_header6)
ws.write(5, 12, '', style_header6)
ws.write(5, 13, '', style_header6)
ws.write(5, 14, '', style_header6)
ws.write(5, 15, '', style_header6)
ws.write(5, 16, '', style_header6)
ws.write(5, 17, '', style_header6)
ws.write(5, 18, 'DAYS', style_header6)
ws.write(5, 19, 'SALARY', style_header6)
ws.write(5, 20, 'DAYS', style_header6)
ws.write(5, 21, 'SALARY', style_header6)
ws.write(5, 22, 'DAYS', style_header6)
ws.write(5, 23, 'SALARY', style_header6)
ws.write(5, 24, 'DAYS', style_header6)
ws.write(5, 25, 'SALARY', style_header6)
ws.write(5, 26, 'DAYS', style_header6)
ws.write(5, 27, 'SALARY', style_header6)
ws.write(5, 28, 'DAYS', style_header6)
ws.write(5, 29, 'SALARY', style_header6)
ws.write(5, 30, 'DAYS', style_header6)
ws.write(5, 31, 'SALARY', style_header6)
ws.write(5, 32, 'DAYS', style_header6)
ws.write(5, 33, 'SALARY', style_header6)
ws.write(5, 34, 'DAYS', style_header6)
ws.write(5, 35, 'SALARY', style_header6)
ws.write(5, 36, 'DAYS', style_header6)
ws.write(5, 37, 'SALARY', style_header6)
ws.write(5, 38, 'DAYS', style_header6)
ws.write(5, 39, 'SALARY', style_header6)
ws.write(5, 40, 'DAYS', style_header6)
ws.write(5, 41, 'SALARY', style_header6)
ws.write(5, 42, 'DAYS', style_header6)
ws.write(5, 43, 'SALARY', style_header6)
ws.write(5, 44, '', style_header6)
if obj.company_id:
list_ids = emp_obj.search(cr, uid,
[('company_id', '=', obj.company_id.id),
('active', '=', True),
('type', '=', 'Employee')])
list_ids1 = emp_obj.search(cr, uid,
[('company_id', '=', obj.company_id.id),
('active', '=', False),
('type', '=', 'Employee')])
list_ids = list_ids + list_ids1
elif obj.employee_id:
list_ids = emp_obj.search(cr, uid,
[('id', '=', obj.employee_id.id),
('active', '=', True),
('type', '=', 'Employee')])
else:
raise osv.except_osv(
_('Warning !'),
_("Please Select Atleast Company Or Employee."))
if len(list_ids) == 1:
query ="select hr.sinid,rr.name,hr.doj,job.name,hr.id,sum(pmbl.apr),sum(pmbl.may),sum(pmbl.june),sum(pmbl.july),sum(pmbl.aug),sum(pmbl.sep),sum(pmbl.oct),"\
"sum(pmbl.nov),sum(pmbl.dec),sum(pmbl.jan),sum(pmbl.feb),sum(pmbl.mar),sum(pmbl.total_day),sum(pmbl.bonus),sum(pmbl.apr_salary),"\
"sum(pmbl.may_salary),sum(pmbl.june_salary),sum(pmbl.july_salary),sum(pmbl.aug_salary),sum(pmbl.sep_salary),sum(pmbl.oct_salary),"\
"sum(pmbl.nov_salary),sum(pmbl.dec_salary),sum(pmbl.jan_salary),sum(pmbl.feb_salary),sum(pmbl.mar_salary),sum(pmbl.total_salary)"\
"from payment_management_bonus_line as pmbl left join hr_employee as hr on pmbl.employee_id = hr.id left join resource_resource as rr on hr.resource_id = rr.id left join hr_job as job on hr.job_id=job.id "\
"where pmbl.employee_id = '"+str(list_ids[0])+"' and pmbl.bonus_from >= '"+str(obj.from_date)+"' and pmbl.bonus_till <= '"+str(obj.till_date)+"' group by hr.sinid,rr.name,hr.doj,job.name,hr.id order by hr.sinid "
cr.execute(query)
temp = cr.fetchall()
else:
query ="select hr.sinid,rr.name,hr.doj,job.name,hr.id,sum(pmbl.apr),sum(pmbl.may),sum(pmbl.june),sum(pmbl.july),sum(pmbl.aug),sum(pmbl.sep),sum(pmbl.oct),"\
"sum(pmbl.nov),sum(pmbl.dec),sum(pmbl.jan),sum(pmbl.feb),sum(pmbl.mar),sum(pmbl.total_day),sum(pmbl.bonus),sum(pmbl.apr_salary),"\
"sum(pmbl.may_salary),sum(pmbl.june_salary),sum(pmbl.july_salary),sum(pmbl.aug_salary),sum(pmbl.sep_salary),sum(pmbl.oct_salary),"\
"sum(pmbl.nov_salary),sum(pmbl.dec_salary),sum(pmbl.jan_salary),sum(pmbl.feb_salary),sum(pmbl.mar_salary),sum(pmbl.total_salary)"\
"from payment_management_bonus_line as pmbl left join hr_employee as hr on pmbl.employee_id = hr.id left join resource_resource as rr on hr.resource_id = rr.id left join hr_job as job on hr.job_id=job.id "\
"where pmbl.employee_id in "+str(tuple(list_ids))+" and pmbl.bonus_from >= '"+str(obj.from_date)+"' and pmbl.bonus_till <= '"+str(obj.till_date)+"' group by hr.sinid,rr.name,hr.doj,job.name,hr.id order by hr.sinid"
cr.execute(query)
temp = cr.fetchall()
if not temp:
raise osv.except_osv(_('Warning !'), _("Record Not Found !!!"))
columnno = 6
no = 6
for val in temp:
father_name = ''
if val[4]:
father_qry = "select name from family where relation='Father' and employee_id='" + str(
val[4]) + "' "
cr.execute(father_qry)
father_temp = cr.fetchall()
if father_temp:
father_name = father_temp[0][0]
else:
father_name = ' '
ws.row(no).height = 500
doj = datetime.strptime(val[2], "%Y-%m-%d").strftime("%d-%m-%Y")
ws.write(columnno, 0, val[0], style_header5)
ws.write(columnno, 1, val[1], style_header5)
ws.write(columnno, 2, doj, style_header5)
ws.write(columnno, 3, father_name, style_header5)
ws.write(columnno, 4, val[3], style_header5)
ws.write(columnno, 5, 'Yes', style_header5)
ws.write(columnno, 6, '', style_header5)
ws.write(columnno, 7, '', style_header5)
ws.write(columnno, 8, '', style_header5)
ws.write(columnno, 9, '', style_header5)
ws.write(columnno, 10, '', style_header5)
ws.write(columnno, 11, '', style_header5)
ws.write(columnno, 12, '', style_header5)
ws.write(columnno, 13, '', style_header5)
ws.write(columnno, 14, '', style_header5)
ws.write(columnno, 15, '', style_header5)
ws.write(columnno, 16, '', style_header5)
ws.write(columnno, 17, '', style_header5)
ws.write(columnno, 18, val[5], style_header5)
ws.write(columnno, 19, val[19], style_header5)
ws.write(columnno, 20, val[6], style_header5)
ws.write(columnno, 21, val[20], style_header5)
ws.write(columnno, 22, val[7], style_header5)
ws.write(columnno, 23, val[21], style_header5)
ws.write(columnno, 24, val[8], style_header5)
ws.write(columnno, 25, val[22], style_header5)
ws.write(columnno, 26, val[9], style_header5)
ws.write(columnno, 27, val[23], style_header5)
ws.write(columnno, 28, val[10], style_header5)
ws.write(columnno, 29, val[24], style_header5)
ws.write(columnno, 30, val[11], style_header5)
ws.write(columnno, 31, val[25], style_header5)
ws.write(columnno, 32, val[12], style_header5)
ws.write(columnno, 33, val[26], style_header5)
ws.write(columnno, 34, val[13], style_header5)
ws.write(columnno, 35, val[27], style_header5)
ws.write(columnno, 36, val[14], style_header5)
ws.write(columnno, 37, val[28], style_header5)
ws.write(columnno, 38, val[15], style_header5)
ws.write(columnno, 39, val[29], style_header5)
ws.write(columnno, 40, val[16], style_header5)
ws.write(columnno, 41, val[30], style_header5)
ws.write(columnno, 42, val[17], style_header5)
ws.write(columnno, 43, val[31], style_header5)
ws.write(columnno, 44, val[18], style_header5)
columnno += 1
no += 1
f = cStringIO.StringIO()
wb.save(f)
out = base64.encodestring(f.getvalue())
return self.write(cr,
uid,
ids, {
'export_data': out,
'filename': 'Payment Bonus.xls'
},
context=context)
def CreateExcelSpreadsheet(table, output_excel, use_alias=True):
"""Exports table to excel
Required:
table -- input table
output_excel -- output excel table (.xlsx, .xls)
Optional:
use_alias -- use field alias name for column headers. Default is True
"""
# build field dict
fieldNames = [(f.name, f.aliasName) for f in arcpy.ListFields(table) if f.type != 'Geometry']
fields = [f[1] if use_alias in ('true', True) else f[0] for f in fieldNames]
widths = {i: arial10.fitwidth(f) + 1024 for i,f in enumerate(fields)}
# get field values *Changed from type dict to list
with arcpy.da.SearchCursor(table, [f[0] for f in fieldNames]) as rows:
values = [r for r in rows]
# Create spreadsheet
wb = Workbook()
ws = wb.add_sheet('Sheet 1')
cols = len(fields)
rows = len(values) + 1
# set styles
#***************************************************************************
borders = Borders()
borders.left = Borders.THIN
borders.right = Borders.THIN
borders.top = Borders.THIN
borders.bottom = Borders.THIN
style = XFStyle()
style.borders = borders
# headers
fntHeaders = Font()
fntHeaders.bold = True
fntHeaders.height = 220
styleHeaders = XFStyle()
styleHeaders.font = fntHeaders
styleHeaders.borders = borders
# for date fields
styleDate = XFStyle()
styleDate.borders = borders
styleDate.num_format_str = 'MM/DD/YYYY'
#***************************************************************************
# write headers and freeze panes
for ci,field in enumerate(fields):
ws.write(0, ci, field, styleHeaders)
# freeze headers
ws.set_panes_frozen(True)
ws.set_horz_split_pos(1)
# fill in values
start = 1
for vals in values:
for i, value in enumerate(vals):
ws.write(start, i, value, styleDate if isinstance(value, datetime.datetime) else style)
v_width = arial10.fitwidth(str(value).strip())
if v_width > widths[i]:
widths[i] = v_width
start += 1
if not start % 1000:
ws.flush_row_data()
# autofit column widths
for ci,width in widths.iteritems():
ws.col(ci).width = int(width + 256) # just a little more padding
# save workbook
wb.save(output_excel)
del wb
out_url = '/'.join([PATH_URL, os.path.basename(out_file)])
arcpy.SetParameter(2, out_url)
arcpy.AddMessage(out_url)
return out_url
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 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 write_sheet(self, data, sheet_name, print_to_screen=False):
'''Write a very simple table to a new sheet in a spreadsheet,
Optionally, print the table to the screen'''
# most cells
al = Alignment()
al.horz = Alignment.HORZ_RIGHT
al.vert = Alignment.VERT_CENTER
font = Font()
font.name = 'Arial'
font.height = 9 * 20 # 9 pt
style = XFStyle()
style.font = font
style.alignment = al
# tops cells
al = Alignment()
al.horz = Alignment.HORZ_CENTER
al.vert = Alignment.VERT_CENTER
font = Font()
font.name = 'Arial'
font.bold = True
font.height = 9 * 20 # 9 pt
style_top = XFStyle()
style_top.font = font
style_top.alignment = al
# left cells
al = Alignment()
al.horz = Alignment.HORZ_LEFT
al.vert = Alignment.VERT_CENTER
font = Font()
font.name = 'Arial'
font.bold = True
font.italic = True
font.height = 9 * 20 # 9 pt
style_left = XFStyle()
style_left.font = font
style_left.alignment = al
ws = self.add_sheet(sheet_name)
for i, row in enumerate(data):
for j, cell in enumerate(row):
borders = Borders()
if i == 0:
borders.top = 1
borders.bottom = 2
if i == len(row) - 1:
borders.bottom = 1
if j == 0:
borders.left = 1
borders.right = 1
if j == len(row) - 1:
borders.right = 1
if j == 0:
_style = style_left
elif i == 0:
_style = style_top
else:
_style = style
_style.borders = borders
ws.write(i + 1, j + 1, cell, _style)
if print_to_screen:
print print_table(data, sheet_name, bold=True)
def 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 print_report_excel(self, cr, uid, ids, context=None):
if context is None:
context = {}
asset_obj = self.pool.get('account.asset.asset')
asset_categ_obj = self.pool.get('account.asset.category')
company_obj = self.pool.get('res.company')
year_obj = self.pool.get('account.fiscalyear')
period_obj = self.pool.get('account.period')
asset_depreciation_line_obj = self.pool.get(
'account.asset.depreciation.line')
fnt = Font()
fnt.name = 'Arial'
fnt.height = 220
fnt1 = Font()
fnt1.name = 'Arial'
fnt1.height = 220
fnt1.bold = 'on'
# Define the font attributes for header
content_fnt = Font()
content_fnt.name = 'Arial'
content_fnt.height = 220
align_content = Alignment()
align_content.horz = Alignment.HORZ_LEFT
borders = Borders()
borders.left = 0x02
borders.right = 0x02
borders.top = 0x02
borders.bottom = 0x02
# The text should be centrally aligned
align = Alignment()
align.horz = Alignment.HORZ_LEFT
align.vert = Alignment.VERT_TOP
align.wrap = Alignment.WRAP_AT_RIGHT
# The text should be right aligned
align1 = Alignment()
align1.horz = Alignment.HORZ_RIGHT
align1.vert = Alignment.VERT_TOP
align1.wrap = Alignment.WRAP_AT_RIGHT
# The content should be left aligned
align2 = Alignment()
align2.horz = Alignment.HORZ_LEFT
align2.vert = Alignment.VERT_TOP
align2.wrap = Alignment.WRAP_AT_RIGHT
# The content should be right aligned
align3 = Alignment()
align3.horz = Alignment.HORZ_RIGHT
align3.vert = Alignment.VERT_TOP
align3.wrap = Alignment.WRAP_AT_RIGHT
# We set the backgroundcolour here
pattern = Pattern()
pattern.pattern = Pattern.SOLID_PATTERN
pattern.pattern_fore_colour = 0x1F
# We set the backgroundcolour here
pattern1 = Pattern()
pattern1.pattern = Pattern.SOLID_PATTERN
pattern1.pattern_fore_colour = 0x17
# We set the backgroundcolour here
pattern2 = Pattern()
pattern2.pattern = Pattern.SOLID_PATTERN
pattern2.pattern_fore_colour = 0xFF
# We set the backgroundcolour here
pattern3 = Pattern()
pattern3.pattern = Pattern.SOLID_PATTERN
pattern3.pattern_fore_colour = 0xFF
# apply the above settings to the row(0) header
style_header = XFStyle()
style_header.font = fnt1
style_header.pattern = pattern
style_header.borders = borders
style_header.alignment = align
style_header_right = XFStyle()
style_header_right.font = fnt1
style_header_right.pattern = pattern
style_header_right.borders = borders
style_header_right.alignment = align3
# apply the above settings to the row(1) header
style_header1 = XFStyle()
style_header1.font = fnt
style_header1.pattern = pattern1
style_header1.borders = borders
style_header1.alignment = align1
# apply the above settings to the content
style_content_left = XFStyle()
style_content_left.font = fnt
style_content_left.pattern = pattern2
style_content_left.borders = borders
style_content_left.alignment = align2
style_content_right = XFStyle()
style_content_right.font = fnt
style_content_right.pattern = pattern3
style_content_right.borders = borders
style_content_right.alignment = align3
style_content = XFStyle()
style_content.alignment = align_content
style_content.font = content_fnt
wb = Workbook()
ws = wb.add_sheet('Sheet 1')
ws.row(0).height = 500
ws.col(0).width = 6500
ws.col(1).width = 6500
ws.col(2).width = 6500
ws.col(3).width = 6500
ws.col(4).width = 6500
ws.col(5).width = 6500
ws.col(6).width = 6500
ws.col(7).width = 6500
ws.col(8).width = 6500
ws.col(9).width = 6500
ws.col(10).width = 6500
ws.col(11).width = 6500
ws.col(12).width = 6500
ws.col(13).width = 6500
style = xlwt.easyxf('font: bold on,height 240,color_index 0X36;'
'align: horiz center;')
ws.write(0, 2, 'Asset Report', style)
data = self.read(cr, uid, ids, [], context=context)[0]
company = company_obj.browse(cr, uid, data['company_id'][0])
if data['fiscalyear_id']:
year = year_obj.browse(cr, uid, data['fiscalyear_id'][0]).name
else:
year = ''
filter = ''
if data['filter'] == 'filter_date':
filter = 'Dates'
elif data['filter'] == 'filter_period':
filter = 'Periods'
else:
filter = 'No Filters'
from_date = False
to_date = False
ws.row(2).height = 500
ws.write(2, 0, 'Company Name', style_header)
ws.write(2, 1, company.name, style_header)
ws.row(3).height = 500
ws.write(3, 0, 'Report Run', style_header)
ws.write(3, 1, time.strftime('%Y-%m-%d %H:%M:%S'), style_header)
ws.row(4).height = 500
ws.write(4, 0, 'Fiscal Year', style_header)
ws.write(4, 1, year, style_header)
ws.row(5).height = 500
ws.write(5, 0, 'Filters', style_header)
ws.write(5, 1, filter, style_header)
ws.row(6).height = 500
if data['filter'] == 'filter_period':
from_period_id = data['period_from'][0]
to_period_id = data['period_to'][0]
if from_period_id and to_period_id:
from_date = period_obj.browse(cr, uid,
from_period_id).date_start
to_date = period_obj.browse(cr, uid, to_period_id).date_stop
elif from_period_id and to_period_id:
from_date = period_obj.browse(cr, uid,
from_period_id).date_start
to_date = period_obj.browse(cr, uid, from_period_id).date_stop
ws.write(6, 0, 'Start Period', style_header)
ws.write(6, 1, data['period_from'][1], style_header)
ws.write(7, 0, 'End Period', style_header)
ws.write(7, 1, data['period_to'][1], style_header)
elif data['filter'] == 'filter_date':
from_date = data['date_from']
to_date = data['date_to']
ws.write(6, 0, 'Start Date', style_header)
ws.write(
6, 1,
datetime.strptime(data['date_from'],
'%Y-%m-%d').strftime('%m/%d/%Y'),
style_header)
ws.write(7, 0, 'End Date', style_header)
ws.write(
7, 1,
datetime.strptime(data['date_to'],
'%Y-%m-%d').strftime('%m/%d/%Y'),
style_header)
row = 10
from_date = False
to_date = False
if data['filter'] == 'filter_period':
from_period_id = data['period_from'][0]
to_period_id = data['period_to'][0]
if from_period_id and to_period_id:
from_date = period_obj.browse(cr, uid,
from_period_id).date_start
to_date = period_obj.browse(cr, uid, to_period_id).date_stop
elif from_period_id and to_period_id:
from_date = period_obj.browse(cr, uid,
from_period_id).date_start
to_date = period_obj.browse(cr, uid, from_period_id).date_stop
elif data['filter'] == 'filter_date':
from_date = data['date_from']
to_date = data['date_to']
else:
if data['fiscalyear_id']:
from_date = year_obj.browse(
cr, uid, data['fiscalyear_id'][0]).date_start
to_date = year_obj.browse(cr, uid,
data['fiscalyear_id'][0]).date_stop
else:
pass
if data['asset_categ_ids']:
categories_ids = data['asset_categ_ids']
else:
categories_ids = asset_categ_obj.search(cr, uid, [])
if data['company_id']:
company_ids = [data['company_id'][0]]
else:
company_ids = company_obj.search(cr, uid, [])
for categ_id in categories_ids:
ws.row(row).height = 500
domain = []
if from_date and to_date:
#domain.append(('purchase_date', '>=', from_date))
#domain.append(('purchase_date', '<=', to_date))
pass
domain.append(('category_id', '=', categ_id))
domain.append(('company_id', 'in', company_ids))
domain.append(('state', '=', 'open'))
asset_ids = asset_obj.search(cr, uid, domain)
asset_categ = asset_categ_obj.browse(cr, uid, categ_id)
count = 1
ws.row(row).height = 800
ws.write(row, 0, 'Asset Category:', style_header)
ws.write(row, 1, asset_categ.name, style_header)
row += 1
ws.row(row).height = 700
ws.write(row, 0, 'No', style_header)
ws.write(row, 1, 'Asset Tag No', style_header)
ws.write(row, 2, 'Asset Description', style_header)
ws.write(row, 3, 'Location', style_header)
ws.write(row, 4, 'Document Reference', style_header)
ws.write(row, 5, 'Requisition Date', style_header)
ws.write(row, 6, 'Requisition Value', style_header)
ws.write(row, 7, 'Salvage Value', style_header)
ws.write(row, 8, 'Depreciation Method', style_header)
ws.write(row, 9, 'Number of Usage', style_header)
ws.write(row, 10, 'B/F Accumulated Depreciation', style_header)
ws.write(row, 11, 'Depreciation', style_header)
ws.write(row, 12, 'Accumulated Depreciation', style_header)
ws.write(row, 13, 'Net Book Value', style_header)
row += 1
total_req_val = 0.0
total_sal_val = 0.0
total_bf_accum_depr = 0.0
total_next_depr = 0.0
total_accum_depr = 0.0
total_net_book = 0.0
for asset in asset_obj.browse(cr, uid, asset_ids):
bf_accum_depr = 0.0
next_amount_depr = 0.0
accum_depr_val = 0.0
net_book_val = 0.0
method = ''
if asset.method == 'linear':
method = 'Linear'
elif asset.method == 'degressive':
method = 'Degressive'
# location = ''
#
# if asset.move_id and asset.move_id.location_dest_id:
# location = asset.move_id.location_dest_id.name
ws.row(row).height = 500
ws.write(row, 0, count, style_content_left)
ws.write(row, 1, asset.code or '', style_content_left)
ws.write(row, 2, asset.product_desc or '', style_content_left)
ws.write(row, 3, asset.asset_location or '',
style_content_left)
ws.write(row, 4, asset.picking_id.name or '',
style_content_left)
row_date = datetime.strptime(
asset.purchase_date, '%Y-%m-%d').strftime('%m/%d/%Y') or ''
ws.write(row, 5, row_date, style_content_left)
ws.write(row, 6, asset.purchase_value or 0.0,
style_content_right)
ws.write(row, 7, asset.salvage_value or 0.0,
style_content_right)
ws.write(row, 8, method or '', style_content_left)
ws.write(row, 9, asset.method_number or '', style_content_left)
if from_date and to_date:
period_id = period_obj.find(cr, uid, dt=from_date)
bf_line_ids = asset_depreciation_line_obj.search(
cr, uid, [('asset_id', '=', asset.id),
('effective_period_id', '=', period_id[0])])
if bf_line_ids:
asset_bf_line = asset_depreciation_line_obj.browse(
cr, uid, bf_line_ids[0])
bf_accum_depr = asset_bf_line.depreciated_value
else:
bf_line_ids = asset_depreciation_line_obj.search(
cr,
uid, [('asset_id', '=', asset.id),
('move_check', '=', True)],
order='effective_date desc',
limit=1)
if bf_line_ids:
asset_bf_line = asset_depreciation_line_obj.browse(
cr, uid, bf_line_ids[0])
bf_accum_depr = asset_bf_line.depreciated_value
#To find current depreciation value: Need to search depreciation line on depreciation board for respected "Amount already depreciated".
# Here we will match "Amount already depreciated" (bf_accum_depr) in depreciation line and matched depreciation line's current depreciation will be taken.
if from_date and to_date:
period_id = period_obj.find(cr, uid, dt=from_date)
bf_line_ids = asset_depreciation_line_obj.search(
cr, uid, [('asset_id', '=', asset.id),
('effective_date', '>=', from_date),
('effective_date', '<=', to_date),
('move_check', '=', True)])
if bf_line_ids:
for asset_bf_line in asset_depreciation_line_obj.browse(
cr, uid, bf_line_ids):
next_amount_depr += asset_bf_line.amount
else:
period_id = period_obj.find(cr, uid, dt=from_date)
bf_line_ids = asset_depreciation_line_obj.search(
cr, uid, [('asset_id', '=', asset.id),
('move_check', '=', True)])
if bf_line_ids:
for asset_bf_line in asset_depreciation_line_obj.browse(
cr, uid, bf_line_ids):
next_amount_depr += asset_bf_line.amount
ws.write(row, 10, bf_accum_depr, style_content_right)
ws.write(row, 11, next_amount_depr, style_content_right)
accum_depr_val = (bf_accum_depr + next_amount_depr)
ws.write(row, 12, accum_depr_val, style_content_right)
net_book_val = (asset.purchase_value - accum_depr_val)
ws.write(row, 13, net_book_val, style_content_right)
total_req_val += asset.purchase_value
total_sal_val += asset.salvage_value
total_bf_accum_depr += bf_accum_depr
total_next_depr += next_amount_depr
total_accum_depr += accum_depr_val
total_net_book += net_book_val
row += 1
count += 1
ws.row(row).height = 500
ws.write(row, 0, '', style_header_right)
ws.write(row, 1, '', style_header_right)
ws.write(row, 2, 'Total', style_header_right)
ws.write(row, 3, '', style_header_right)
ws.write(row, 4, '', style_header_right)
ws.write(row, 5, '', style_header_right)
ws.write(row, 6, total_req_val, style_header_right)
ws.write(row, 7, total_sal_val, style_header_right)
ws.write(row, 8, '', style_header_right)
ws.write(row, 9, '', style_header_right)
ws.write(row, 10, total_bf_accum_depr, style_header_right)
ws.write(row, 11, total_next_depr, style_header_right)
ws.write(row, 12, total_accum_depr, style_header_right)
ws.write(row, 13, total_net_book, style_header_right)
row += 3
f = cStringIO.StringIO()
wb.save(f)
out = base64.encodestring(f.getvalue())
return {
'name': 'Assets Register Reports',
'res_model': 'xls.report.wizard',
'type': 'ir.actions.act_window',
'view_type': 'form',
'view_mode': 'form',
'target': 'new',
'nodestroy': True,
'context': {
'data': out,
'name': 'Asset Register Report.xls'
}
}
# Top alignment, should be used for all cells topAlign = Alignment() topAlign.vert = Alignment.VERT_TOP # header centered and wrapped alignWrap = Alignment() alignWrap.horz = Alignment.HORZ_CENTER alignWrap.wrap = Alignment.WRAP_AT_RIGHT # normal font normFont = Font() normFont.name = 'Arial' # headers font fntHeaders = Font() fntHeaders.height = 200 fntHeaders.name = 'Arial' # header styles styleHeaders = XFStyle() styleHeaders.font = fntHeaders styleHeaders.alignment.wrap = True styleHeaders.alignment = alignCenter # headers with boarder styleHeadersWithBorder = XFStyle() styleHeadersWithBorder.font = fntHeaders styleHeadersWithBorder.alignment.wrap = True styleHeadersWithBorder.alignment = alignWrap styleHeadersWithBorder.borders = yearBorders
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 output(self):
# from xlrd import open_workbook
from xlwt import Workbook, XFStyle, Borders, Alignment, Font, Pattern, Style, easyxf
# from xlutils.copy import copy
'''
rb = open_workbook(r"Templates\template.xls")
wb = copy(rb)
s = wb.get_sheet(0)
s.write(0, 0, 'A1')
wb.save(r"C:\Temp\nephro-planner\new.xls")
'''
book = Workbook(encoding="utf-8")
sheet = book.add_sheet(r"Feuille1")
style_title = XFStyle()
font_title = Font()
font_title.name = "Comic Sans MS"
font_title.height = 280
style_title.font = font_title
style_title.alignment.horz = Alignment.HORZ_CENTER
style_title.alignment.vert = Alignment.VERT_CENTER
style_cell_bottom = XFStyle()
borders_cell_bottom = Borders()
borders_cell_bottom.bottom = Borders.MEDIUM
style_cell_bottom.borders = borders_cell_bottom
style_header = XFStyle()
font_header = Font()
font_header.bold = 1
font_header.name = "Arial Narrow"
font_header.height = 240
style_header.font = font_header
style_header.alignment.horz = Alignment.HORZ_CENTER
style_header.alignment.vert = Alignment.VERT_CENTER
borders_header = Borders()
borders_header.top = Borders.MEDIUM
borders_header.left = Borders.MEDIUM
borders_header.bottom = Borders.MEDIUM
borders_header.right = Borders.MEDIUM
style_header.borders = borders_header
style_sub_header = XFStyle()
font_sub_header = Font()
font_sub_header.name = "Arial Narrow"
font_sub_header.height = 240
style_sub_header.font = font_sub_header
style_sub_header.alignment.horz = Alignment.HORZ_CENTER
style_sub_header.alignment.vert = Alignment.VERT_CENTER
borders_sub_header = Borders()
borders_sub_header.top = Borders.MEDIUM
borders_sub_header.left = Borders.MEDIUM
borders_sub_header.bottom = Borders.MEDIUM
borders_sub_header.right = Borders.MEDIUM
style_sub_header.borders = borders_sub_header
style_date = XFStyle()
font_date = Font()
font_date.name = "Arial Narrow"
font_date.height = 240
style_date.font = font_date
style_date.alignment.horz = Alignment.HORZ_RIGHT
style_date.alignment.vert = Alignment.VERT_CENTER
borders_date = Borders()
borders_date.left = Borders.MEDIUM
style_date.borders = borders_date
style_date_status = XFStyle()
font_date_status = Font()
font_date_status.name = "Arial Narrow"
font_date_status.height = 240
style_date_status.font = font_date_status
style_date_status.alignment.horz = Alignment.HORZ_LEFT
style_date_status.alignment.vert = Alignment.VERT_CENTER
borders_date_status = Borders()
borders_date_status.right = Borders.MEDIUM
style_date_status.borders = borders_date_status
style_cell_normal = XFStyle()
font_cell_normal = Font()
font_cell_normal.name = "Arial Narrow"
font_cell_normal.height = 220
style_cell_normal.font = font_cell_normal
style_cell_normal.alignment.horz = Alignment.HORZ_CENTER
style_cell_normal.alignment.vert = Alignment.VERT_CENTER
style_cell_right = XFStyle()
font_cell_right = Font()
font_cell_right.name = "Arial Narrow"
font_cell_right.height = 220
style_cell_right.font = font_cell_right
style_cell_right.alignment.horz = Alignment.HORZ_CENTER
style_cell_right.alignment.vert = Alignment.VERT_CENTER
borders_cell_right = Borders()
borders_cell_right.right = Borders.MEDIUM
style_cell_right.borders = borders_cell_right
style_cell_top = XFStyle()
borders_cell_top = Borders()
borders_cell_top.top = Borders.MEDIUM
style_cell_top.borders = borders_cell_top
column_offset = 1
date_column_offset = 2
row_offset = 1
table_width = 1 + 3 * len(Database.team())
# build titles
sheet.write_merge(row_offset, row_offset, column_offset,
table_width + 1, "POLE MEDECINE INTERNE",
style_title)
sheet.write_merge(row_offset + 1, row_offset + 1, column_offset,
table_width + 1, "Service NEPHROLOGIE – HEMODIALYSE",
style_title)
sheet.write_merge(
row_offset + 2, row_offset + 2, column_offset, table_width + 1,
"Planning de {0} {1}".format(
self.human_readable_months[self.month - 1],
self.year), style_title)
# patch date columns top borders
sheet.write(row_offset + 5, column_offset, "", style_cell_bottom)
sheet.write(row_offset + 5, column_offset + 1, "", style_cell_bottom)
# build header and sub header
for x in Database.team():
sheet.write_merge(
row_offset + 4, row_offset + 4,
column_offset + date_column_offset + 3 * (x.id - 1),
column_offset + date_column_offset + 3 * x.id - 1, x.name,
style_header)
sheet.write(row_offset + 5,
column_offset + date_column_offset + 3 * (x.id - 1),
"M", style_sub_header)
sheet.write(
row_offset + 5,
column_offset + date_column_offset + 3 * (x.id - 1) + 1, "AM",
style_sub_header)
sheet.write(
row_offset + 5,
column_offset + date_column_offset + 3 * (x.id - 1) + 2, "N",
style_sub_header)
'''
i = 40
for x in sorted(Style.colour_map):
style = XFStyle()
pattern = Pattern()
pattern.pattern = Pattern.SOLID_PATTERN
pattern.pattern_fore_colour = Style.colour_map[x]
style.pattern = pattern
sheet.write(i, 1, x, style)
i += 1
'''
pattern_pale_blue = Pattern()
pattern_pale_blue.pattern = Pattern.SOLID_PATTERN
pattern_pale_blue.pattern_fore_colour = Style.colour_map['pale_blue']
pattern_light_yellow = Pattern()
pattern_light_yellow.pattern = Pattern.SOLID_PATTERN
pattern_light_yellow.pattern_fore_colour = Style.colour_map[
'light_yellow']
pattern_ice_blue = Pattern()
pattern_ice_blue.pattern = Pattern.SOLID_PATTERN
pattern_ice_blue.pattern_fore_colour = Style.colour_map['ice_blue']
pattern_light_green = Pattern()
pattern_light_green.pattern = Pattern.SOLID_PATTERN
pattern_light_green.pattern_fore_colour = Style.colour_map[
'light_green']
pattern_ivory = Pattern()
pattern_ivory.pattern = Pattern.SOLID_PATTERN
pattern_ivory.pattern_fore_colour = Style.colour_map['ivory']
pattern_tan = Pattern()
pattern_tan.pattern = Pattern.SOLID_PATTERN
pattern_tan.pattern_fore_colour = Style.colour_map['tan']
pattern_gold = Pattern()
pattern_gold.pattern = Pattern.SOLID_PATTERN
pattern_gold.pattern_fore_colour = Style.colour_map['gold']
def __cell_colouration__(style, current_activity):
if current_activity is Activity.CONSULTATION:
style.pattern = pattern_pale_blue
elif current_activity is Activity.DIALYSIS:
style.pattern = pattern_ice_blue
elif current_activity is Activity.NEPHROLOGY:
style.pattern = pattern_light_green
elif current_activity is Activity.OTHERS:
style.pattern = pattern_tan
elif current_activity is Activity.OBLIGATION:
style.pattern = pattern_ivory
elif current_activity is Activity.OBLIGATION_WEEKEND:
style.pattern = pattern_light_yellow
elif current_activity is Activity.OBLIGATION_HOLIDAY:
style.pattern = pattern_gold
else:
style.pattern = Pattern()
return style
last_day = calendar.monthrange(self.year, self.month)[1]
for x in range(1, last_day + 1):
current_date = date(self.year, self.month, x)
current_daily_planning = self.daily_plannings[current_date]
# build date and date status columns
sheet.write(
row_offset + 5 + x, column_offset, "{0}. {1}".format(
self.human_readable_days[current_daily_planning.weekday]
[0:3].lower(), x), style_date)
if current_daily_planning.weekday in [5, 6]:
sheet.write(row_offset + 5 + x, column_offset + 1, "WK",
style_date_status)
elif not current_daily_planning.is_working_day:
sheet.write(row_offset + 5 + x, column_offset + 1, "Férié",
style_date_status)
else:
sheet.write(row_offset + 5 + x, column_offset + 1, "",
style_date_status)
'''
easyxf(
'font: bold 1, name Tahoma, height 160;'
'align: vertical center, horizontal center, wrap on;'
'borders: left thin, right thin, top thin, bottom thin;'
'pattern: pattern solid, pattern_fore_colour green, pattern_back_colour green'
)
'''
# fill in month planning
for y in Database.team():
current_activity = current_daily_planning.__get_activity__(
TimeSlot.FIRST_SHIFT, y)
sheet.write(
row_offset + 5 + x,
column_offset + date_column_offset + 3 * (y.id - 1),
self.human_readable_activities[current_activity]
if current_activity else "",
__cell_colouration__(style_cell_normal, current_activity))
current_activity = current_daily_planning.__get_activity__(
TimeSlot.SECOND_SHIFT, y)
sheet.write(
row_offset + 5 + x,
column_offset + date_column_offset + 1 + 3 * (y.id - 1),
self.human_readable_activities[current_activity]
if current_activity else "",
__cell_colouration__(style_cell_normal, current_activity))
current_activity = current_daily_planning.__get_activity__(
TimeSlot.THIRD_SHIFT, y)
sheet.write(
row_offset + 5 + x,
column_offset + date_column_offset + 2 + 3 * (y.id - 1),
self.human_readable_activities[current_activity]
if current_activity else "",
__cell_colouration__(style_cell_right, current_activity))
# patch table bottom border
for x in range(0, table_width + 1):
sheet.write(row_offset + 5 + (last_day + 1), column_offset + x, "",
style_cell_top)
book.save(r"C:\Temp\nephro-planner\new.xls")
def create_excel(work_excel, withdraw_info=[]):
title_fnt = Font()
title_fnt.height = 0x0140
title_fnt.name = u'宋体'
title_fnt.bold = True
brd = Borders()
brd.bottom = 1
brd.top = 1
brd.left = 1
brd.right = 1
title_location = Alignment()
title_location.horz = Alignment.HORZ_CENTER
title_location.vert = Alignment.VERT_CENTER
title_style = XFStyle()
title_style.font = title_fnt
title_style.alignment = title_location
style = XFStyle()
style.font.height = 0x00E0
style.font.name = u'宋体'
style.font.bold = False
style.alignment.horz = Alignment.HORZ_CENTER
style.alignment.vert = Alignment.VERT_CENTER
content_title_style = deepcopy(style)
content_title_style.alignment.horz = Alignment.HORZ_LEFT
content_title_style.font.height = 0x00E0
content_style = deepcopy(style)
content_style.alignment.horz = Alignment.HORZ_LEFT
center_content_style = deepcopy(style)
center_content_style.alignment.horz = Alignment.HORZ_CENTER
center_content_style.borders = brd
style.borders = brd
content_style.borders = brd
merge_up_style = deepcopy(style)
merge_up_style.borders.bottom = 0
content_up_style = deepcopy(merge_up_style)
content_up_style.alignment.horz = Alignment.HORZ_CENTER
merge_down_style = deepcopy(style)
merge_down_style.borders.top = 0
content_down_style = deepcopy(merge_down_style)
content_down_style.alignment.horz = Alignment.HORZ_RIGHT
content_down_style.font.height = 0x00CA
for i, info in enumerate(withdraw_info):
width = i * 25
work_excel.write_merge(2 + width, 2 + width, 0, 16, u'商户提现付款申请单', title_style)
work_excel.write_merge(3 + width, 3 + width, 1, 5, info['supplier_type'], content_title_style)
work_excel.write_merge(3 + width, 3 + width, 6, 16, info['supplier'], content_title_style)
work_excel.write(4 + width, 0, u'收款单位名称', style)
work_excel.write_merge(4 + width, 4 + width, 1, 5, info['company'], content_style)
work_excel.write(4 + width, 6, u'申请提现帐号', style)
work_excel.write_merge(4 + width, 4 + width, 7, 16, info['applier'], center_content_style)
work_excel.write(5 + width, 0, u'开户银行', style)
work_excel.write_merge(5 + width, 5 + width, 1, 5, info['bank'], content_style)
work_excel.write(5 + width, 6, u'申请提现日期', style)
work_excel.write_merge(5 + width, 5 + width, 7, 16, info['apply_time'], center_content_style)
work_excel.write(6 + width, 0, u'银行帐号', style)
work_excel.write_merge(6 + width, 6 + width, 1, 5, info['account'], content_style)
work_excel.write(6 + width, 6, u'合同账期', style)
work_excel.write_merge(6 + width, 6 + width, 7, 16, info['payment_day_type'], center_content_style)
work_excel.write(7 + width, 0, u'付款用途', style)
work_excel.write_merge(7 + width, 7 + width, 1, 5, u'商户提现', content_style)
work_excel.write(7 + width, 6, u'付款日期', style)
work_excel.write_merge(7 + width, 7 + width, 7, 16, info['date'], center_content_style)
units = [u'千', u'百', u'十', u'万', u'千', u'百', u'十', u'元', u'角', u'分']
digit = list(str(info['money']))
digit.remove('.')
digit.reverse()
for j, unit in enumerate(units):
work_excel.write(8 + width, 7 + j, unit, content_style)
for index, value in enumerate(digit):
work_excel.write(9 + width, 16 - index, value, content_style)
work_excel.write(9 + width, 16 - index - 1, '￥', content_style)
work_excel.write_merge(10 + width, 11 + width, 0, 0, u'备注事项', style)
work_excel.write_merge(10 + width, 10 + width, 1, 6, '', content_style)
work_excel.write_merge(10 + width, 10 + width, 7, 16, '', style)
work_excel.write_merge(11 + width, 11 + width, 1, 16, info['print_info'], content_down_style)
work_excel.write(8 + width, 0, u'付款金额', merge_up_style)
work_excel.write(9 + width, 0, u'人民币（大写）', merge_down_style)
work_excel.write_merge(8 + width, 9 + width, 1, 6, info['china_money'], content_style)
work_excel.write_merge(13 + width, 13 + width, 0, 2, u'部门主管:', content_title_style)
work_excel.write_merge(13 + width, 13 + width, 3, 5, u'财务经理:', content_title_style)
work_excel.write_merge(13 + width, 13 + width, 6, 16, u'公司总经理:', content_title_style)
for row in range(2, 12):
work_excel.row(row + width).height_mismatch = 1
work_excel.row(row + width).height = 478
work_excel.row(2 + width).height_mismatch = 1
work_excel.row(2 + width).height = 1000
work_excel.col(0).width = 3871
work_excel.col(1).width = 1771
work_excel.col(2).width = 1509
work_excel.col(3).width = 2348
work_excel.col(4).width = 840
work_excel.col(5).width = 3241
work_excel.col(6).width = 3441
for col in range(7, 17):
work_excel.col(col).width = 709
alignCenter = Alignment() alignCenter.horz = Alignment.HORZ_CENTER alignCenter.vert = Alignment.VERT_CENTER # Top alignment, should be used for all cells alignTop = Alignment() alignTop.vert = Alignment.VERT_TOP # header centered and wrapped alignWrap = Alignment() alignWrap.horz = Alignment.HORZ_CENTER alignWrap.wrap = Alignment.WRAP_AT_RIGHT # generic font, 11 point Calibri defaultFont = Font() defaultFont.height = 220 defaultFont.name = 'Arial' # default heades font headerFont = Font() headerFont.height = 220 headerFont.name = 'Arial' headerFont.bold = True # header styles defaultStyleHeaders = XFStyle() defaultStyleHeaders.font = headerFont defaultStyleHeaders.alignment.wrap = True defaultStyleHeaders.alignment = alignCenter defaultStyleHeaders.font = headerFont
