def set_style(name, height, bg_Color=0, bold=False):
# 设置字体
style = xlwt.XFStyle()
font = xlwt.Font()
font.name = name
font.bold = bold
font.color_index = 4
font.height = height
style.font = font
# 设置边框
borders = xlwt.Borders()
# 细实线:1，小粗实线:2，细虚线:3，中细虚线:4，大粗实线:5，双线:6，细点虚线:7
# 大粗虚线:8，细点划线:9，粗点划线:10，细双点划线:11，粗双点划线:12，斜点划线:13
borders.left = 1
borders.right = 1
borders.top = 1
borders.bottom = 1
style.borders = borders
if bg_Color != 0:
pattern = Pattern()
pattern.pattern = Pattern.SOLID_PATTERN
pattern.pattern_fore_colour = bg_Color # 设置单元格背景色为黄色
style.pattern = pattern
return style
def export_to_excel(self, **kwargs):
work_book = Workbook(encoding="ascii")
center_alignment = Alignment()
center_alignment.horz = Alignment.HORZ_RIGHT
center_alignment.vert = Alignment.VERT_CENTER
border = Borders()
border.top = Borders.THIN
border.left = Borders.THIN
border.bottom = Borders.THIN
border.right = Borders.THIN
head_style = XFStyle()
head_pattern = Pattern()
head_pattern.pattern = Pattern.SOLID_PATTERN
head_pattern.pattern_fore_colour = Style.colour_map['gray25']
head_style.pattern = head_pattern
head_font = Font()
head_font.bold = True
head_style.font = head_font
head_style.alignment = center_alignment
head_style.borders = border
data_style = XFStyle()
data_style.alignment = center_alignment
data_style.borders = border
work_sheet = work_book.add_sheet("Sheet")
limit = 10000
count = self.get_data_scale(**kwargs)['count']
times = (count + limit - 1) // limit
add_header = False
for i in range(1):
kwargs["startIndex"] = i * limit + 1
kwargs['endIndex'] = (i + 1) * limit + 1
records = self.get_data(**kwargs)
column_names = []
y = 0
for data in records:
if not add_header:
x = 0
for column_name in data.keys():
work_sheet.write(y, x, column_name, head_style)
column_names.append(column_name)
x += 1
add_header = True
y += 1
x = 0
for column_name in column_names:
work_sheet.write(y, x, data[column_name], data_style)
x += 1
y += 1
x = 0
work_book.save(BASE_WIN_OUTPUT_PATH + "result.xls")
def setStyle(n):
pattern = Pattern()
pattern.pattern = Pattern.SOLID_PATTERN
pattern.pattern_fore_colour = n
style = XFStyle()
style.pattern = pattern
return style
def writeXls(self, file_name): #写入Excel表格
movie_list = [
'上映年份', '片名', '导演', '编剧', '主演', '类型', '制片国家地区', '语言', '又名', '上映日期',
'片长', 'IMDB链接/影片地址', '评分', '评价人数', '页面网址', '短评', '话题'
]
xls = ExcelWrite.Workbook()
sheet = xls.add_sheet("Sheet1") #写入表1
style = XFStyle()
pattern = Pattern() # 创建一个模式
pattern.pattern = Pattern.SOLID_PATTERN # 设置其模式为实型
pattern.pattern_fore_colour = 0x16 #设置其模式单元格背景色
# 设置单元格背景颜色 0 = Black, 1 = White, 2 = Red, 3 = Green, 4 = Blue, 5 = Yellow, 6 = Magenta, the list goes on...
style.pattern = pattern
for i in range(len(movie_list)): #写入首行信息，为表头，表示列名
sheet.write(0, i, movie_list[i], style)
sheet.col(i).width = 5240
actors_list = ['姓名', '性别', '星座', '年龄', '出生地', '职业', '简介']
sheet2 = xls.add_sheet("Sheet2")
style2 = XFStyle()
style2.pattern = pattern
for i in range(len(actors_list)): # 写入首行信息，为表头，表示列名
sheet2.write(0, i, actors_list[i], style2)
sheet2.col(i).width = 5140
try:
#连接数据库读取数据
conn = pymysql.connect(host='localhost',
user='******',
password='******',
db='mytest',
port=3306,
charset='utf8')
cur = conn.cursor()
sql = 'select * from movies'
cur.execute(sql)
row = 0
for movie_info in cur.fetchall(): #遍历数据库中每行信息，一行表示一部电影的所有信息
row = row + 1 #第0行为表头，不添加数据，因此从第一列开始写入
for i in range(len(movie_info) - 1): #对于一行信息进行遍历，分别存入每列
sheet.write(row, i, movie_info[i + 1])
sql = "select id,name,sex,star,date_format(from_days(to_days(now())-to_days(birthday)),'%Y')+0,place,job,message from actors"
cur.execute(sql)
row = 0
for actor_info in cur.fetchall(): # 遍历数据库中每行信息，一行表示一部电影的所有信息
row = row + 1 # 第0行为表头，不添加数据，因此从第一列开始写入
for i in range(len(actor_info) - 1): # 对于一行信息进行遍历，分别存入每列
sheet2.write(row, i, actor_info[i + 1])
xls.save(file_name) #写入完成，存储
cur.close()
conn.close()
wx.MessageBox('数据已导出到桌面！！！', caption="导出成功")
except:
wx.MessageBox('数据导出失败！！！', caption="导出失败")
def writeCycleInfo2File(self):
workbook = xlwt.Workbook()
# set header style
headerStyle = xlwt.XFStyle()
headerFont = xlwt.Font()
headerFont.bold = True
headerStyle.font = headerFont
headerPattern = Pattern()
headerPattern.pattern = Pattern.SOLID_PATTERN
headerPattern.pattern_fore_colour = 50 # green
headerStyle.pattern = headerPattern
# write run result to excel file
for threadKey in self.cycleInfo.keys():
sheet = workbook.add_sheet(threadKey, True)
headerText = ["cycleNumber", "liveIndividuals", "deadIndividuals", "newBorn", "newDeath", "newDeathFromFight", "newDeathFromNatural", "newDeathFromStarve", "breedTimes", "fightTimes", "fightSuccessTimes", "fightPeaceTimes", "fightFailureTimes", "FleeSuccessTimes", "defendTimes", "defendSuccessTimes", "defendPeaceTimes", "defendFailureTimes", "popAvgHungryLevel", "popAvgAge", "popAvgLifespan", "popAvgFightCapability", "popAvgAttackPossibility", "popAvgDefendPossibility", "popAvgTotalBreedingTimes"]
for i in range(0, len(headerText)):
sheet.write(0, i, headerText[i], headerStyle)
rowNum = 1
threadValue = self.cycleInfo[threadKey]
for cycleNumber in threadValue.keys():
cycleInfo = threadValue[cycleNumber]
sheet.write(rowNum, 0, cycleNumber)
sheet.write(rowNum, 1, cycleInfo.liveIndividuals)
sheet.write(rowNum, 2, cycleInfo.deadIndividuals)
sheet.write(rowNum, 3, cycleInfo.newBorn)
sheet.write(rowNum, 4, cycleInfo.newDeath)
sheet.write(rowNum, 5, cycleInfo.newDeathFromFight)
sheet.write(rowNum, 6, cycleInfo.newDeathFromNatural)
sheet.write(rowNum, 7, cycleInfo.newDeathFromStarve)
sheet.write(rowNum, 8, cycleInfo.breedTimes)
sheet.write(rowNum, 9, cycleInfo.fightTimes)
sheet.write(rowNum, 10, cycleInfo.fightSuccessTimes)
sheet.write(rowNum, 11, cycleInfo.fightPeaceTimes)
sheet.write(rowNum, 12, cycleInfo.fightFailureTimes)
sheet.write(rowNum, 13, cycleInfo.FleeSuccessTimes)
sheet.write(rowNum, 14, cycleInfo.defendTimes)
sheet.write(rowNum, 15, cycleInfo.defendSuccessTimes)
sheet.write(rowNum, 16, cycleInfo.defendPeaceTimes)
sheet.write(rowNum, 17, cycleInfo.defendFailureTimes)
sheet.write(rowNum, 18, cycleInfo.popAvgHungryLevel)
sheet.write(rowNum, 19, cycleInfo.popAvgAge)
sheet.write(rowNum, 20, cycleInfo.popAvgLifespan)
sheet.write(rowNum, 21, cycleInfo.popAvgFightCapability)
sheet.write(rowNum, 22, cycleInfo.popAvgAttackPossibility)
sheet.write(rowNum, 23, cycleInfo.popAvgDefendPossibility)
sheet.write(rowNum, 24, cycleInfo.popAvgTotalBreedingTimes)
rowNum += 1
workbook.save("EvolutionSimulationResult_" + time.strftime("%Y%m%d%H%M%S", time.localtime()) + ".xls")
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 export_to_excel(self):
if not os.path.isdir('./export_excel'): # 导出结果存储目录
os.makedirs('./export_excel')
save_path = os.path.join('./export_excel', self.select_table + '.xls')
head_list = ['学号', '姓名', '是否出勤', '出勤时间']
xls = ExcelWrite.Workbook() # 创建Excel控制对象
sheet = xls.add_sheet("Sheet1") # 创建被写入的表格sheet1
style = XFStyle()
pattern = Pattern() # 创建一个模式
pattern.pattern = Pattern.SOLID_PATTERN # 设置其模式为实型
pattern.pattern_fore_colour = 0x16 # 设置其模式单元格背景色
# 设置单元格背景颜色 0 = Black, 1 = White, 2 = Red, 3 = Green, 4 = Blue, 5 = Yellow, 6 = Magenta, the list goes on...
style.pattern = pattern
for col in range(len(head_list)): # 写入首行信息，为表头，表示列名
sheet.write(0, col, head_list[col], style)
sheet.col(col).width = 4240
try:
# 连接数据库读取数据
conn, cursor = DataManageUI.connect_to_sql()
sql = 'select * from `%s`' % self.select_table
cursor.execute(sql)
row = 0
stu_data = cursor.fetchall()
for stu_info in stu_data: # 遍历数据库中每行信息，一行表示一部电影的所有信息
stu_info = list(stu_info)
if stu_info[3]:
stu_info[3] = stu_info[3].strftime('%Y/%m/%d %H:%M:%S')
row = row + 1 # 第0行为表头，不添加数据，因此从第一列开始写入
for col in range(len(stu_info)): # 对于一行信息进行遍历，分别存入每列
sheet.write(row, col, stu_info[col])
xls.save(save_path) # 写入完成，存储
cursor.close()
conn.close()
text = 'Success!'
informativeText = '<b>课程{}签到表 导出成功! 目标路径：./export_excel</b>'.format(
self.select_table)
DataRecordUI.callDialog(QMessageBox.Information, text,
informativeText, QMessageBox.Ok)
except Exception as e:
print(e)
text = 'Error!'
informativeText = '<b>导出失败!</b>'
DataRecordUI.callDialog(QMessageBox.Critical, text,
informativeText, QMessageBox.Ok)
def init_sheet_catalog(sheet):
global config_inner_items
global config_multimedia_items
global config_glass_items
style = xlwt.XFStyle() # 创建一个样式对象，初始化样式
al = xlwt.Alignment()
al.horz = 0x02 # 设置水平居中
al.vert = 0x01 # 设置垂直居中
style.alignment = al
font = xlwt.Font() # 为样式创建字体
font.name = 'Times New Roman'
font.bold = True # 黑体
# font.underline = True # 下划线
# font.italic = True # 斜体字
style.font = font # 设定样式
pattern = Pattern() # 创建一个模式
pattern.pattern = Pattern.SOLID_PATTERN # 设置其模式为实型
pattern.pattern_fore_colour = 3
# 设置单元格背景颜色 0 = Black, 1 = White, 2 = Red, 3 = Green, 4 = Blue, 5 = Yellow, 6 = Magenta, the list goes on...
style.pattern = pattern
row_index = 2
# config_inner = config_data['内部配置']
# config_inner_items = list(config_inner.keys())
sheet.write_merge(row_index, row_index + len(config_inner_items) -1 , 0, 0+0, "内部配置", style)
for index in range(len(config_inner_items)):
sheet.write(row_index + index , 1, config_inner_items[index])
row_index = row_index + len(config_inner_items)
# config_multimedia = config_data['多媒体配置']
# config_multimedia_items = list(config_multimedia.keys())
sheet.write_merge(row_index, row_index + len(config_multimedia_items)-1 , 0, 0+0, "多媒体配置", style)
for index in range(len(config_multimedia_items)):
sheet.write(row_index + index , 1, config_multimedia_items[index])
row_index = row_index + len(config_multimedia_items)
# config_glass = config_data['玻璃/后视镜']
# config_glass_items = list(config_glass.keys())
sheet.write_merge(row_index ,row_index + len(config_glass_items)-1, 0, 0+0, "玻璃/后视镜", style)
for index in range(len(config_glass_items)):
sheet.write(row_index + index , 1, config_glass_items[index])
row_index = row_index + len(config_glass_items)
def estiloCeldasSeparadas():
fnt = Font()
fnt.bold=True
#fnt.colour_index=2
borders = Borders()
borders.left = Borders.MEDIUM
borders.right = Borders.MEDIUM
borders.top = Borders.MEDIUM
borders.bottom = Borders.MEDIUM
pattern = Pattern()
pattern.pattern = Pattern.SOLID_PATTERN
pattern.pattern_fore_colour = 0x18#0x99
style = XFStyle()
style.num_format_str='YYYY-MM-DD'
style.font = fnt
style.borders = borders
style.pattern = pattern
return style
def __init__(self, timesheets, year=datetime.date.today().year):
self.timesheets = timesheets.order_by('activity__person', 'project',
'year', 'month')
self.book = Workbook()
self.year = int(year)
font_header = Font()
font_header.bold = True
font_title = Font()
font_title.bold = True
grey_pattern = Pattern()
grey_pattern.pattern = Pattern.SOLID_PATTERN
grey_pattern.pattern_fore_colour = Style.colour_map['gray25']
self.header_style = XFStyle()
self.header_style.pattern = grey_pattern
self.header_style.font = font_header
self.header_title = XFStyle()
self.header_title.font = font_title
self.date_style = XFStyle()
self.date_style.num_format_str = 'M/D/YY'
self.title_style = XFStyle()
self.title_style.pattern = grey_pattern
self.title_style.font = font_title
self.title_style.alignment.horz = self.title_style.alignment.HORZ_CENTER
self.txt_centered_al = Alignment()
self.txt_centered_al.horz = Alignment.HORZ_CENTER
self.txt_centered = XFStyle()
self.txt_centered.alignment = self.txt_centered_al
self.header_date_style = XFStyle()
self.header_date_style.pattern = grey_pattern
self.header_date_style.font = font_header
self.header_date_style.alignment = self.txt_centered_al
def 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 creaHojaDeTraduccion(excel, nombreHoja="traduccion"):
hoja=excel.creaHoja(nombreHoja)
hoja.col(0).width=90*256
hoja.col(1).width=20*256
fnt = Font()
fnt.bold=True
#fnt.colour_index=2
borders = Borders()
borders.left = Borders.MEDIUM
borders.right = Borders.MEDIUM
borders.top = Borders.MEDIUM
borders.bottom = Borders.MEDIUM
pattern = Pattern()
pattern.pattern = Pattern.SOLID_PATTERN
pattern.pattern_fore_colour = 0x18#0x99
style = XFStyle()
style.num_format_str='YYYY-MM-DD'
style.font = fnt
style.borders = borders
style.pattern = pattern
hoja.write(0,0,"Original Text",style)
hoja.write(0,1,"Translation",style)
return hoja
def makeXLS(self, id_relatorio, id_promocao=None):
"""
cria o arquivo xls
a var index é utilizada para
controlar em qual linha entrara o conteudo
"""
timename = str(util.dtnow('%d/%m/%Y %H:%M%S')).replace("/", "")
timename = timename.replace(":", "")
timename = "_" + timename.replace(" ", "")
site = getDadosSite(id_site=self.id_site,
request=self.request)
base = site["base_app"]
if not base.endswith("/"):
base = base + "/"
if int(id_relatorio) == 1:
titulo = 'Relacao_usuario_participacao'
elif int(id_relatorio) == 2:
titulo = 'Relacao_participantes_contemplados'
elif int(id_relatorio) == 4:
titulo = 'Dados_usuarios_por_promocao'
else:
titulo = 'Relacao_participantes_promocao'
saveplace = ("{0}/ns{1}/arquivos/tmp/"
"{2}{3}.xls").format(str(settings.PATH_FILES),
str(self.id_site),
titulo,
timename)
saveurl = "{0}tmp/{1}{2}.xls".format(
base, titulo, timename)
book = Workbook()
font = Font()
font.bold = True
style = XFStyle()
style.font = font
style0 = XFStyle()
style0.font = font
alignment = Alignment()
alignment.horz = Alignment.HORZ_CENTER
style0.alignment = alignment
pattern = Pattern()
pattern.pattern = Pattern.SOLID_PATTERN
pattern.pattern_fore_colour = 22
style0.pattern = pattern
index = 0
sheet = book.add_sheet('promocoes')
# sheet
if int(id_relatorio) == 1 or int(id_relatorio) == 3:
promocoes = self._getConteudo()
if int(id_relatorio) == 1:
campos_s1 = [{"nome": "Nome"},
{"email": "email"},
{"cpf": "CPF"},
{"quantidade de participacoes": u"Quantidade de participações"},
{"status":"status"},
{"bloqueio":"bloqueado"}]
sheet.write_merge(0, 0, 0, 5, u"Relação usuário participação por promoção", style0)
else:
campos_s1 = [{"nome":"Nome"},
{"email":"email"},
{"cpf":"CPF"},
{"telefone":"Telefone"},
{"endereco":u"Endereço"},
{"data_hora":u"Hora participação"}]
sheet.write_merge(0, 0, 0, 5, u"Dados de usuários por promoção", style0)
total_geral = 0
count = 0
for i in promocoes:
count_participacoes = self._getCountParticipacoes(i['id_conteudo'])
count += count_participacoes
index += 1
titulo = i['titulo']
sheet.write_merge(index, index, 0, 5, titulo.decode("latin1"), style0)
index += 1
for j in range(len(campos_s1)):
sheet.write(index, j, campos_s1[j].values()[0], style)
sheet.col(j).width = 30 * 256
cont = 0
if int(id_relatorio) == 1:
for y in self.execSql("select_usuarios_promocoes_all",
id_conteudo=int(i['id_conteudo'])):
sheet.write(index + 1,
0,
self.dec(y['nome']))
sheet.write(index + 1,
1,
self.dec(y['email']))
sheet.write(index + 1,
2,
self.dec(y['cpf']))
sheet.write(index + 1,
3,
self.dec(y['total']))
sheet.write(index + 1,
4,
self.dec(y['status']))
if y['bloqueio']:
bloqueio = 'Sim'
else:
bloqueio = 'Não'
sheet.write(index + 1,
5,
self.dec(bloqueio))
index += 1
cont += 1
if int(id_relatorio) == 3:
for y in self.execSql("select_usuarios_promocoes_all2",
id_conteudo=int(i['id_conteudo'])):
endereco = "{0}, {1}, {2}, {3}, {4}". format(y['endereco'],
y['numero'],
y['bairro'],
y['estado'],
y['cep'])
sheet.write(index + 1,
0,
self.dec(y['nome']))
sheet.write(index + 1,
1,
self.dec(y['email']))
sheet.write(index + 1,
2,
self.dec(y['cpf']))
sheet.write(index + 1,
3,
self.dec(y['telefone']))
sheet.write(index + 1,
4,
self.dec(endereco))
sheet.write(index + 1,
5,
y['dhora_participacao'])
index += 1
cont += 1
if cont:
index += 2
sheet.write(index, 0, 'Total', style)
sheet.write(index,
1,
cont)
total_geral += cont
index += 1
sheet.write(index, 0, u'Total Participações', style)
sheet.write(index,
1,
count_participacoes)
else:
index += 2
sheet.write(index, 0, 'Total', style)
sheet.write(index,
1,
cont)
index += 2
index += 6
sheet.write_merge(index, index, 0, 1, "Total Geral", style0)
index += 1
sheet.write(index, 0, "Total de Participantes", style)
sheet.write(index, 1, self.dec(total_geral), style)
index += 1
sheet.write(index, 0, u"Total de Participações Geral", style)
sheet.write(index, 1, self.dec(count), style)
else:
if int(id_relatorio) == 4:
cont = 0
titulo_promo = self._getConteudo(id_promocao)['titulo']
sheet.write_merge(0, 0, 0, 5, u"Relação de partcipantes da promoção " + titulo_promo.decode('latin1'), style0)
campos_s1 = [{"nome":"Nome"},
{"email":"email"},
{"cpf":"CPF"},
{"telefone":"Telefone"},
{"endereco":u"Endereço"},
{"status":"Status"}
]
saveplace = ("{0}/ns{1}/arquivos/tmp/{2}{3}.xls").format(str(settings.PATH_FILES),
str(self.id_site),
unicode(titulo + '_' + titulo_promo, errors='ignore'),
timename)
saveurl = "{0}tmp/{1}{2}.xls".format(base, unicode(titulo + '_' + titulo_promo, errors='ignore'), timename)
index += 1
for j in range(len(campos_s1)):
sheet.write(index, j, campos_s1[j].values()[0], style)
sheet.col(j).width = 30 * 256
for i in self.execSql("select_usuarios_promocoes_all2",
id_conteudo=int(id_promocao)):
endereco = "{0}, {1}, {2}, {3}, {4}". format(i['endereco'],
i['numero'],
i['bairro'],
i['estado'],
i['cep'])
sheet.write(index + 1,
0,
self.dec(i['nome']))
sheet.write(index + 1,
1,
self.dec(i['email']))
sheet.write(index + 1,
2,
self.dec(i['cpf']))
sheet.write(index + 1,
3,
self.dec(i['telefone']))
sheet.write(index + 1,
4,
self.dec(endereco))
sheet.write(index + 1,
5,
self.dec(i['status']))
index += 1
cont += 1
else:
cont = 0
sheet.write_merge(0, 0, 0, 5, u"Relação de usuários contemplados", style0)
campos_s1 = [{"nome":"Nome"},
{"email":"email"},
{"cpf":"CPF"},
{"telefone":"Telefone"},
{"endereco":u"Endereço"},
{"Contemplações":u"Contemplações"}]
index += 1
for j in range(len(campos_s1)):
sheet.write(index, j, campos_s1[j].values()[0], style)
sheet.col(j).width = 30 * 256
for i in self.execSql("select_contemplados"):
endereco = "{0}, {1}, {2}, {3}, {4}". format(i['endereco'],
i['numero'],
i['bairro'],
i['estado'],
i['cep'])
sheet.write(index + 1,
0,
self.dec(i['nome']))
sheet.write(index + 1,
1,
self.dec(i['email']))
sheet.write(index + 1,
2,
self.dec(i['cpf']))
sheet.write(index + 1,
3,
self.dec(i['telefone']))
sheet.write(index + 1,
4,
self.dec(endereco))
sheet.write(index + 1,
5,
self.dec(i['total']))
index += 1
cont += 1
index += 6
sheet.write(index, 0, u"Total de usuários", style)
sheet.write(index, 1, self.dec(cont), style)
book.save(saveplace)
return saveurl
def salary_payment_report(self,cr,uid,ids,context=None):
#Define the font attributes for header
fnt = Font()
fnt.name = 'Arial'
fnt.size=16
fnt.style= 'Regular'
#Define the font attributes for header
content_fnt = Font()
content_fnt.name ='Arial'
content_fnt.size=16
content_fnt.style= 'Regular'
align_content = Alignment()
align_content.horz= Alignment.HORZ_CENTER
borders = Borders()
borders.left = 0x01
borders.right = 0x01
borders.top = 0x01
borders.bottom = 0x01
#The text should be centrally aligned
align = Alignment()
align.horz = Alignment.HORZ_CENTER
align.vert = Alignment.VERT_CENTER
#We set the backgroundcolour here
pattern = Pattern()
pattern.pattern = Pattern.SOLID_PATTERN
pattern.pattern_fore_colour = 0x1F
#apply the above settings to the row(0) header
style_header= XFStyle()
style_header.font= fnt
style_header.pattern= pattern
style_header.borders = borders
style_header.alignment=align
#Define the font attributes for header
fnt3 = Font()
fnt3.name = 'Arial'
fnt3.size=16
fnt3.style= 'Regular'
#Define the font attributes for header
content_fnt3 = Font()
content_fnt3.name ='Arial'
content_fnt3.size=16
content_fnt3.style= 'Regular'
align_content3 = Alignment()
align_content3.horz= Alignment.HORZ_CENTER
borders3 = Borders()
borders3.left = 0x01
borders3.right = 0x01
borders3.top = 0x01
borders3.bottom = 0x01
#The text should be centrally aligned
align3 = Alignment()
align3.horz = Alignment.HORZ_CENTER
align3.vert = Alignment.VERT_CENTER
#We set the backgroundcolour here
pattern3 = Pattern()
#apply the above settings to the row(0) header
style_header3= XFStyle()
style_header3.font= fnt3
style_header3.pattern= pattern3
style_header3.borders = borders3
style_header3.alignment=align3
#Define the font attributes for header
fnt4 = Font()
fnt4.name = 'Arial'
#Define the font attributes for header
content_fnt4 = Font()
content_fnt4.name ='Arial'
align_content4 = Alignment()
align_content4.horz= Alignment.HORZ_LEFT
borders4 = Borders()
borders4.left = 0x01
borders4.right = 0x01
borders4.top = 0x01
borders4.bottom = 0x01
#The text should be centrally aligned
align4 = Alignment()
align4.horz = Alignment.HORZ_LEFT
align4.vert = Alignment.VERT_CENTER
#We set the backgroundcolour here
pattern4 = Pattern()
#apply the above settings to the row(0) header
style_header4= XFStyle()
style_header4.font= fnt4
style_header4.pattern= pattern4
style_header4.borders = borders4
style_header4.alignment=align4
#Define the font attributes for header
fnt1 = Font()
fnt1.name = 'Arial'
fnt1.size=10
fnt1.Style= 'Regular'
#Define the font attributes for header
content_fnt1 = Font()
content_fnt1.name ='Arial'
content_fnt1.size=10
content_fnt1.Style= 'Regular'
align_content1 = Alignment()
align_content1.horz= Alignment.HORZ_RIGHT
borders1 = Borders()
borders1.left = 0x01
borders1.right = 0x01
borders1.top = 0x01
borders1.bottom = 0x01
#The text should be centrally aligned
align1 = Alignment()
align1.horz = Alignment.HORZ_RIGHT
align1.vert = Alignment.VERT_CENTER
#We set the backgroundcolour here
pattern1 = Pattern()
#apply the above settings to the row(0) header
style_header1= XFStyle()
style_header1.font= fnt1
style_header1.pattern= pattern1
style_header1.borders = borders1
style_header1.alignment=align1
#Define the font attributes for header
fnt2 = Font()
fnt2.name = 'Arial'
fnt2.size=16
fnt2.Style= 'Regular'
#Define the font attributes for header
content_fnt2 = Font()
content_fnt2.name ='Arial'
content_fnt2.size=16
content_fnt2.Style= 'Regular'
align_content2 = Alignment()
align_content2.horz= Alignment.HORZ_RIGHT
borders2 = Borders()
borders2.left = 0x01
borders2.right = 0x01
borders2.top = 0x01
borders2.bottom = 0x01
#The text should be centrally aligned
align2 = Alignment()
align2.horz = Alignment.HORZ_RIGHT
align2.vert = Alignment.VERT_CENTER
#We set the backgroundcolour here
pattern2 = Pattern()
#apply the above settings to the row(0) header
style_header2= XFStyle()
style_header2.font= fnt2
style_header2.pattern= pattern
style_header2.borders = borders2
style_header2.alignment=align2
wb = Workbook()
ws = wb.add_sheet('Salary Report')
this=self.browse(cr,uid,ids[0],context=context)
ws.row(0).height=300
ws.row(1).height=300
ws.row(2).height=300
# ws.col(0).width = 5000
# ws.col(0).width = 5000
# ws.col(0).width = 5000
# ws.col(0).width = 5000
ws.write_merge(0,0,0,5, 'SALARY REPORT',style_header)
ws.write_merge(1,1,0,5, ('DEPARTMENT : ',this.department_id.name),style_header)
ws.write_merge(2,2,0,1, 'Month',style_header)
ws.write_merge(2,2,2,3, 'Salary Amount',style_header)
ws.write_merge(2,2,4,5, 'Remark',style_header)
l=[]
lst=[]
for val in this.month_ids:
i = 3
query="""select hy.name,spl.month, sum(spl.total_amount) from salary_payment_line as spl left join holiday_year as hy on spl.year_id = hy.id where month = '"""+str(val.month)+"""'
and year_id = '"""+str(val.year_id.id)+"""' and curr_department = '"""+str(this.department_id.id)+"""' and salary_type='Salary'
group by spl.month, hy.name"""
cr.execute(query)
temp = cr.fetchall()
for year,month, total_amount in temp:
t=()
mon = int(month)
t = (year,mon, total_amount)
lst.append(t)
total = 0.0
lst.sort()
for year,month, total_amount in lst:
if month == 1:
month1 = 'January'
elif month == 2:
month1 = 'February'
elif month == 3:
month1 = 'March'
elif month == 4:
month1 = 'April'
elif month == 5:
month1 = 'May'
elif month == 6:
month1 = 'June'
elif month == 7:
month1 = 'July'
elif month == 8:
month1 = 'August'
elif month == 9:
month1 = 'September'
elif month == 10:
month1 = 'October'
elif month == 11:
month1 = 'November'
else:
month1 = 'December'
total += total_amount
ws.write_merge(i,i,0,1, (month1,' ',year),style_header4)
ws.write_merge(i,i,2,3, round(total_amount,2),style_header1)
ws.write_merge(i,i,4,5, ' ',style_header3)
i += 1
ws.write_merge(i,i,0,1, 'Total Amount',style_header2)
ws.write_merge(i,i,2,3, round(total,2),style_header2)
ws.write_merge(i,i,4,5, ' ',style_header)
if len(lst) < 1:
raise osv.except_osv(_('Warning!'),_('No Record found!'))
f = cStringIO.StringIO()
wb.save(f)
out=base64.encodestring(f.getvalue())
sal_report = self.write(cr, uid, ids, {'export_data':out, 'filename':'Salary Report.xls'}, context=context)
return sal_report
def writePopulationInfo2File(self):
workbook = xlwt.Workbook()
# set header style
headerStyle = xlwt.XFStyle()
headerFont = xlwt.Font()
headerFont.bold = True
headerStyle.font = headerFont
headerPattern = Pattern()
headerPattern.pattern = Pattern.SOLID_PATTERN
headerPattern.pattern_fore_colour = 50 # green
headerStyle.pattern = headerPattern
# write run result to excel file
for thread in self.dreamland.populationThreadPlayers:
sheet = workbook.add_sheet(thread.THREAD_NAME, True)
headerText = ["name", "gender", "generation", "gene", "parents", "age", "birthTime", "deathTime", "deathCause", "populationThreat", "hungryLevel", "lifeStatus", "coordinateX", "coordinateY", "slotCode", "isBusy", "fightTimes", "fleeSuccessTimes", "fleeFailureTimes", "breedTimes", "moveHistory", "lifespan", "fightCapability", "attackPossibility", "defendPossibility", "totalBreedingTimes"]
for i in range(0, len(headerText)):
sheet.write(0, i, headerText[i], headerStyle)
rowNum = 1
if thread.THREAD_TYPE == "Animal":
for ind in (thread.group + thread.dead):
sheet.write(rowNum, 0, ind.name)
sheet.write(rowNum, 1, ind.gender)
sheet.write(rowNum, 2, ind.generation)
sheet.write(rowNum, 3, Recorder.list2String(ind.gene.geneDigits))
sheet.write(rowNum, 4, ind.parents)
sheet.write(rowNum, 5, ind.age)
sheet.write(rowNum, 6, ind.birthTime)
sheet.write(rowNum, 7, ind.deathTime)
sheet.write(rowNum, 8, ind.deathCause)
sheet.write(rowNum, 9, ind.populationThreat)
sheet.write(rowNum, 10, ind.hungryLevel)
sheet.write(rowNum, 11, ind.lifeStatus)
sheet.write(rowNum, 12, ind.coordinateX)
sheet.write(rowNum, 13, ind.coordinateY)
sheet.write(rowNum, 14, ind.slotCode)
sheet.write(rowNum, 15, ind.isBusy)
sheet.write(rowNum, 16, ind.fightTimes)
sheet.write(rowNum, 17, ind.fleeSuccessTimes)
sheet.write(rowNum, 18, ind.fleeFailureTimes)
sheet.write(rowNum, 19, ind.breedTimes)
sheet.write(rowNum, 20, Recorder.dict2String(ind.moveHistory))
sheet.write(rowNum, 21, ind.lifespan)
sheet.write(rowNum, 22, ind.fightCapability)
sheet.write(rowNum, 23, ind.attackPossibility)
sheet.write(rowNum, 24, ind.defendPossibility)
sheet.write(rowNum, 25, ind.totalBreedingTimes)
rowNum += 1
elif thread.THREAD_TYPE == "Plant":
for ind in (thread.group + thread.dead):
sheet.write(rowNum, 0, ind.name)
sheet.write(rowNum, 5, ind.age)
sheet.write(rowNum, 6, ind.birthTime)
sheet.write(rowNum, 7, ind.deathTime)
sheet.write(rowNum, 8, ind.deathCause)
sheet.write(rowNum, 9, ind.populationThreat)
sheet.write(rowNum, 11, ind.lifeStatus)
sheet.write(rowNum, 12, ind.coordinateX)
sheet.write(rowNum, 13, ind.coordinateY)
sheet.write(rowNum, 14, ind.slotCode)
sheet.write(rowNum, 15, ind.isBusy)
sheet.write(rowNum, 16, ind.fightTimes)
sheet.write(rowNum, 21, ind.lifespan)
sheet.write(rowNum, 22, ind.fightCapability)
sheet.write(rowNum, 23, ind.attackPossibility)
sheet.write(rowNum, 24, ind.defendPossibility)
rowNum += 1
workbook.save("EvolutionSimulationIndividuals_" + time.strftime("%Y%m%d%H%M%S", time.localtime()) + ".xls")
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 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 exportReport5ToExcel(self,objectProject):
book = Workbook();
sheet1 = book.add_sheet('Sheet 1');
sheet1.col(1).width = 256*80;
sheet1.col(2).width = 256*10;
sheet1.col(3).width = 256*20;
borders = Borders()
borders.left = Borders.THIN
borders.right = Borders.THIN
borders.top = Borders.THIN
borders.bottom = Borders.THIN
pattern = Pattern();
pattern.pattern = Pattern.SOLID_PATTERN
pattern.pattern_fore_colour = 23
wrap = Alignment();
wrap.wrap = 1;
wrap.vert = Alignment.VERT_TOP
alignHeader = Alignment();
alignHeader.horz = Alignment.HORZ_CENTER;
alignTop = Alignment();
alignTop.vert = Alignment.VERT_TOP
fnt = Font()
fnt.name = 'Arial'
fnt.colour_index = 4
fnt.bold = True
styleWrap = XFStyle();
styleWrap.alignment = wrap;
styleHead = XFStyle();
styleHead.font = fnt;
styleHead.borders = borders;
styleHead.pattern = pattern;
styleHead.alignment = alignHeader;
styleRowDetail = XFStyle();
styleRowDetail.borders = borders;
styleRowDetail.alignment = alignTop;
styleDate = XFStyle()
styleDate.num_format_str = 'DD-MM-YYYY' ; #'D-MMM-YY';
styleDate.borders = borders;
styleDate.alignment = alignTop;
StyleRowDetailWrap = styleRowDetail ;
StyleRowDetailWrap.alignment = wrap;
if( objectProject):
i=0;
row1 = sheet1.row(i) ;
row1.write(0, ('risk id').decode('UTF8'),styleHead );
#sheet1.write_merge(i, i, 1, 2, ('รายละเอียด').decode('UTF8') );
row1.write(1, ('รายละเอียด').decode('UTF8'),styleHead);
row1.write(2, ('วันที่รายงาน').decode('UTF8'),styleHead );
row1.write(3, ('หน่วยที่รายงาน').decode('UTF8') ,styleHead);
i=i+1;
for value in objectProject:
row1 = sheet1.row(i) ;
row1.write(0, value.get('risk_management_id') ,styleRowDetail );
row1.write(1, value.get('risk_detail').decode('UTF8'),StyleRowDetailWrap );
#sheet1.write_merge(i, i, 1, 2, value.get('risk_detail').decode('UTF8') , StyleRowDetailWrap );
row1.write(2, value.get('report_date') ,styleDate );
row1.write(3, value.get('report').decode('UTF8') ,styleRowDetail );
i=i+1;
for sub in value.get('response') :
row1 = sheet1.row(i) ;
row1.write(0," " );
text = "(" + sub.get('risk_team').decode('UTF8') + " ) " + sub.get('result').decode('UTF8');
row1.write(1, text ,StyleRowDetailWrap );
i=i+1;
dirTempFile = gettempdir() + _os.sep + str('simpleReport5.xls');
book.save(dirTempFile);
return dirTempFile;
def 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 report_get(self, cr, uid, ids, context=None):
this = self.browse(cr, uid, ids)
#Define the font attributes for header
fnt = Font()
fnt.name = 'Arial'
fnt.height = 275
#Define the font attributes for header
content_fnt = Font()
content_fnt.name = 'Arial'
content_fnt.height = 220
align_content = Alignment()
align_content.horz = Alignment.HORZ_LEFT
borders = Borders()
borders.left = 0x02
borders.right = 0x02
borders.top = 0x02
borders.bottom = 0x02
#The text should be centrally aligned
align = Alignment()
align.horz = Alignment.HORZ_LEFT
align.vert = Alignment.VERT_TOP
align.wrap = Alignment.WRAP_AT_RIGHT
#The text should be right aligned
align1 = Alignment()
align1.horz = Alignment.HORZ_RIGHT
align1.vert = Alignment.VERT_TOP
align1.wrap = Alignment.WRAP_AT_RIGHT
#The content should be left aligned
align2 = Alignment()
align2.horz = Alignment.HORZ_LEFT
align2.vert = Alignment.VERT_TOP
align2.wrap = Alignment.WRAP_AT_RIGHT
#The content should be right aligned
align3 = Alignment()
align3.horz = Alignment.HORZ_RIGHT
align3.vert = Alignment.VERT_TOP
align3.wrap = Alignment.WRAP_AT_RIGHT
#We set the backgroundcolour here
pattern = Pattern()
pattern.pattern = Pattern.SOLID_PATTERN
pattern.pattern_fore_colour = 0x1F
#We set the backgroundcolour here
pattern1 = Pattern()
pattern1.pattern = Pattern.SOLID_PATTERN
pattern1.pattern_fore_colour = 0x17
#We set the backgroundcolour here
pattern2 = Pattern()
pattern2.pattern = Pattern.SOLID_PATTERN
pattern2.pattern_fore_colour = 0xFF
#We set the backgroundcolour here
pattern3 = Pattern()
pattern3.pattern = Pattern.SOLID_PATTERN
pattern3.pattern_fore_colour = 0xFF
#apply the above settings to the row(0) header
style_header = XFStyle()
style_header.font = fnt
style_header.pattern = pattern
style_header.borders = borders
style_header.alignment = align
#apply the above settings to the row(1) header
style_header1 = XFStyle()
style_header1.font = fnt
style_header1.pattern = pattern1
style_header1.borders = borders
style_header1.alignment = align1
#apply the above settings to the content
style_content_left = XFStyle()
style_content_left.font = fnt
style_content_left.pattern = pattern2
style_content_left.borders = borders
style_content_left.alignment = align2
style_content_right = XFStyle()
style_content_right.font = fnt
style_content_right.pattern = pattern3
style_content_right.borders = borders
style_content_right.alignment = align3
style_content = XFStyle()
style_content.alignment = align_content
style_content.font = content_fnt
wb = Workbook()
ws = wb.add_sheet("Sheet 1")
ws.row(0).height = 3500
ws.write(0, 0, "(CO1)", style_header)
ws.col(0).width = 2000
ws.write(0, 1, "Month & Year (CO2)", style_header)
ws.col(1).width = 4500
ws.write(0, 2, "Buyer's TIN (CO3)", style_header)
ws.col(2).width = 4500
ws.write(0, 3, "Buyer's Name (CO4)", style_header)
ws.col(3).width = 10000
ws.write(0, 4, "Interstate Branch/Consignment Transfer (CO5)",
style_header)
ws.col(4).width = 4500
ws.write(0, 5, "Export Out Of India (CO6)", style_header)
ws.col(5).width = 4500
ws.write(0, 6, "High Sea Sales (CO7)", style_header)
ws.col(6).width = 4500
ws.write(0, 7, "ISS - Goods Type (CO8)", style_header)
ws.col(7).width = 4500
ws.write(0, 8, "ISS - Form Type (I) (CO9)", style_header)
ws.col(8).width = 4500
ws.write(0, 9, "ISS - Rate Of Tax (C1O)", style_header)
ws.col(9).width = 4500
ws.write(0, 10, "ISS - Sales Price (Excluding CST) (C11)",
style_header)
ws.col(10).width = 4500
ws.write(0, 11, "ISS - Central Sales Tax (C12)", style_header)
ws.col(11).width = 4500
ws.write(0, 12, "ISS - Total (C13)", style_header)
ws.col(12).width = 4500
ws.write(0, 13, "Local Sale - Type Of Sale (C14)", style_header)
ws.col(13).width = 4500
ws.write(0, 14, "Local Sale - Rate Of Tax (C15)", style_header)
ws.col(14).width = 4500
ws.write(0, 15, "Local Sale - Sale Price (Excluding VAT) (C16)",
style_header)
ws.col(15).width = 4500
ws.write(0, 16, "Local Sale - OutPut Tax (C17)", style_header)
ws.col(16).width = 4500
ws.write(0, 17, "Local Sale - Total (Including VAT) (C18)",
style_header)
ws.col(17).width = 4500
ws.row(1).height = 400
ws.write(1, 0, "0", style_header1)
ws.write(1, 1, "0", style_header1)
ws.write(1, 2, "0", style_header1)
ws.write(1, 3, "", style_header1)
ws.write(1, 4, "0.00", style_header1)
ws.write(1, 5, "0.00", style_header1)
ws.write(1, 6, "0.00", style_header1)
ws.write(1, 7, "", style_header1)
ws.write(1, 8, "", style_header1)
ws.write(1, 9, "0.00", style_header1)
ws.write(1, 10, "0.00", style_header1)
ws.write(1, 11, "0.00", style_header1)
ws.write(1, 12, "0.00", style_header1)
ws.write(1, 13, "", style_header1)
ws.write(1, 14, "0.00", style_header1)
ws.write(1, 15, "0.00", style_header1)
ws.write(1, 16, "0.00", style_header1)
ws.write(1, 17, "0.00", style_header1)
inv_obj = self.pool.get('account.invoice')
line_obj = self.pool.get('account.invoice.line')
row = 2
count = 1
value = 0.0
sum1 = sum2 = sum3 = sum4 = sum5 = sum6 = 0.0
period = []
for each in this:
if each.start_period_id.name > each.end_period_id.name:
raise osv.except_osv(
_('Warning !'),
_("Start period must be less than end period !"))
cr.execute("select id from account_period where name between '" +
str(each.start_period_id.name) + "' and '" +
str(each.end_period_id.name) + "'")
temp = cr.fetchall()
for val in temp:
if val:
period.append(val[0])
inv_ids = inv_obj.search(cr, uid,
[('type', '=', 'out_invoice'),
('state', 'not in', ['draft', 'cancel']),
('period_id', 'in', period)])
line_ids = line_obj.search(cr, uid,
[('invoice_id', 'in', inv_ids)])
for line in line_obj.browse(cr, uid, line_ids):
ws.row(row).height = 400
ws.write(row, 0, count, style_content_right)
ws.write(
row, 1, line.invoice_id.period_id
and line.invoice_id.period_id.name or False,
style_content_right)
ws.write(row, 2, line.partner_id.tin_no or '',
style_content_right)
ws.write(row, 3, line.partner_id.name, style_content_left)
for tax in line.invoice_line_tax_id:
value = tax.amount * 100
if line.invoice_id.address_invoice_id and line.invoice_id.address_invoice_id.city and line.invoice_id.address_invoice_id.city.lower(
) == 'new delhi' or line.invoice_id.address_invoice_id and line.invoice_id.address_invoice_id.city and line.invoice_id.address_invoice_id.city.lower(
) == 'delhi' or line.invoice_id.address_invoice_id and line.invoice_id.address_invoice_id.state_id and line.invoice_id.address_invoice_id.state_id.name.lower(
) == 'new delhi' or line.invoice_id.address_invoice_id and line.invoice_id.address_invoice_id.state_id and line.invoice_id.address_invoice_id.state_id.name.lower(
) == 'delhi':
ws.write(row, 14, round(value, 2), style_content_right)
ws.write(row, 15, round(line.price_subtotal, 2),
style_content_right)
sum1 += round(line.price_subtotal, 2)
ws.write(row, 16, round(line.price_subtotal * tax.amount,
2), style_content_right)
sum2 += round(line.price_subtotal * tax.amount, 2)
ws.write(
row, 17,
round(
line.price_subtotal +
(line.price_subtotal * tax.amount), 2),
style_content_right)
sum3 += round(
line.price_subtotal +
(line.price_subtotal * tax.amount), 2)
else:
ws.write(row, 9, round(value, 2), style_content_right)
ws.write(row, 10, round(line.price_subtotal, 2),
style_content_right)
sum4 += round(line.price_subtotal, 2)
ws.write(row, 11, round(line.price_subtotal * tax.amount,
2), style_content_right)
sum5 += round(line.price_subtotal * tax.amount, 2)
ws.write(
row, 12,
round(
line.price_subtotal +
(line.price_subtotal * tax.amount), 2),
style_content_right)
sum6 += round(
line.price_subtotal +
(line.price_subtotal * tax.amount), 2)
row += 1
count += 1
row += 2
ws.row(row).height = 400
ws.write(row, 0, "", style_header1)
ws.write(row, 1, "", style_header1)
ws.write(row, 2, "", style_header1)
ws.write(row, 3, "", style_header1)
ws.write(row, 4, "", style_header1)
ws.write(row, 5, "", style_header1)
ws.write(row, 6, "", style_header1)
ws.write(row, 7, "", style_header1)
ws.write(row, 8, "", style_header1)
ws.write(row, 9, "", style_header1)
ws.write(row, 10, sum4, style_header1)
ws.write(row, 11, sum5, style_header1)
ws.write(row, 12, sum6, style_header1)
ws.write(row, 13, "", style_header1)
ws.write(row, 14, "", style_header1)
ws.write(row, 15, sum1, style_header1)
ws.write(row, 16, sum2, style_header1)
ws.write(row, 17, sum3, style_header1)
f = cStringIO.StringIO()
wb.save(f)
out = base64.encodestring(f.getvalue())
return self.write(cr,
uid,
ids, {
'data': out,
'name': 'MARAnnexure2B.xls'
},
context=context)
def model_run(self, model_context):
from decimal import Decimal
from xlwt import Font, Borders, XFStyle, Pattern, Workbook
from camelot.view.import_utils import (RowData, ColumnMapping,
ColumnSelectionAdmin)
from camelot.view.utils import (local_date_format,
local_datetime_format,
local_time_format)
from camelot.view import action_steps
#
# Select the columns that need to be exported
#
admin = model_context.admin
all_fields = admin.get_all_fields_and_attributes()
field_choices = [(f, entity_fa['name'])
for f, entity_fa in all_fields.items()]
row_data = RowData(1, [None] * len(all_fields))
mapping = ColumnMapping(len(all_fields), [row_data], admin,
[field for field, _fa in admin.get_columns()])
mapping_admin = ColumnSelectionAdmin(len(all_fields), admin,
field_choices)
yield action_steps.ChangeObject(mapping, mapping_admin)
columns = []
for i in range(len(all_fields)):
field = getattr(mapping, 'column_%i_field' % i)
if field != None:
columns.append((field, all_fields[field]))
#
# setup worksheet
#
yield action_steps.UpdateProgress(text=_('Create worksheet'))
workbook = Workbook()
worksheet = workbook.add_sheet('Sheet1')
#
# keep a global cache of styles, since the number of styles that
# can be used is limited.
#
styles = dict()
freeze = lambda d: tuple(sorted(d.iteritems()))
def get_style(
font_specs=dict(),
border_specs=dict(),
pattern=None,
num_format_str=None,
):
style_key = (freeze(font_specs), freeze(border_specs), pattern,
num_format_str)
try:
return styles[style_key]
except KeyError:
style = XFStyle()
style.font = Font()
for key, value in font_specs.items():
setattr(style.font, key, value)
style.borders = Borders()
for key, value in border_specs.items():
setattr(style.borders, key, value)
if pattern:
style.pattern = pattern
if num_format_str:
style.num_format_str = num_format_str
styles[style_key] = style
return style
#
# write style
#
title_style = get_style(
dict(font_name=self.font_name, bold=True, height=240))
worksheet.write(0, 0, admin.get_verbose_name_plural(), title_style)
#
# create some patterns and formats
#
date_format = local_date_format()
datetime_format = local_datetime_format()
time_format = local_time_format()
header_pattern = Pattern()
header_pattern.pattern = Pattern.SOLID_PATTERN
header_pattern.pattern_fore_colour = 0x16
#
# write headers
#
field_names = []
for i, (name, field_attributes) in enumerate(columns):
verbose_name = unicode(field_attributes.get('name', name))
field_names.append(name)
font_specs = dict(font_name=self.font_name, bold=True, height=200)
border_specs = dict(top=0x01)
name = unicode(name)
if i == 0:
border_specs['left'] = 0x01
elif i == len(columns) - 1:
border_specs['right'] = 0x01
header_style = get_style(font_specs, border_specs, header_pattern)
worksheet.write(2, i, verbose_name, header_style)
if len(name) < 8:
worksheet.col(i).width = 8 * 375
else:
worksheet.col(i).width = len(verbose_name) * 375
#
# write data
#
offset = 3
for j, obj in enumerate(model_context.get_collection(yield_per=100)):
dynamic_attributes = admin.get_dynamic_field_attributes(
obj, field_names)
row = offset + j
if j % 100 == 0:
yield action_steps.UpdateProgress(
j, model_context.collection_count)
for i, ((_name, attributes), delta_attributes) in enumerate(
zip(columns, dynamic_attributes)):
attributes.update(delta_attributes)
value = attributes['getter'](obj)
format_string = '0'
if value != None:
if isinstance(value, Decimal):
value = float(str(value))
if isinstance(value, (unicode, str)):
if attributes.get('translate_content', False) == True:
value = ugettext(value)
# handle fields of type code
elif isinstance(value, list):
value = u'.'.join(value)
elif isinstance(value, float):
precision = attributes.get('precision', 2)
format_string = '0.' + '0' * precision
elif isinstance(value, int):
format_string = '0'
elif isinstance(value, datetime.date):
format_string = date_format
elif isinstance(value, datetime.datetime):
format_string = datetime_format
elif isinstance(value, datetime.time):
format_string = time_format
else:
value = unicode(value)
else:
# empty cells should be filled as well, to get the
# borders right
value = ''
font_specs = dict(font_name=self.font_name, height=200)
border_specs = dict()
if i == 0:
border_specs['left'] = 0x01
elif i == len(columns) - 1:
border_specs['right'] = 0x01
if (row - offset + 1) == model_context.collection_count:
border_specs['bottom'] = 0x01
style = get_style(font_specs, border_specs, None,
format_string)
worksheet.write(row, i, value, style)
min_width = len(unicode(value)) * 300
worksheet.col(i).width = max(min_width, worksheet.col(i).width)
yield action_steps.UpdateProgress(text=_('Saving file'))
filename = action_steps.OpenFile.create_temporary_file('.xls')
workbook.save(filename)
yield action_steps.UpdateProgress(text=_('Opening file'))
yield action_steps.OpenFile(filename)
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 generate_report(root, filename, attend_staff, absent_staff, meeting_direct, record_staff, reports, issues):
book = Workbook(encoding='utf-8')
sheet1 = book.add_sheet('Fota Security Knox Week Report')
line_counter = 0
today = datetime.date.today().strftime('%Y/%m/%d')
# header
head_str = [
'Meeting Name', 'SW 3 Group Part 5 Fota/ Security/Knox/Encription TG WeekReport',
'', '',
'Meeting Date', today,
'Meeting Address', '8F Meeting Room',
'', '',
'Attend Staff', attend_staff,
'Absent Staff', absent_staff,
'', '',
'Meeting Direct', meeting_direct,
'Record Staff', record_staff,
]
for i in range(len(head_str) / 2):
sheet1.row(i).write(0, head_str[2 * i])
sheet1.row(i).write(1, head_str[2 * i + 1])
line_counter = len(head_str) / 2 + 1
# font bold
font = Font()
font.bold = True
# pattern yellow
pattern_yellow = Pattern()
pattern_yellow.pattern = Pattern.SOLID_PATTERN
pattern_yellow.pattern_fore_colour = 0x0D # yellow
# pattern gray
pattern_gray = Pattern()
pattern_gray.pattern = Pattern.SOLID_PATTERN
pattern_gray.pattern_fore_colour = 0x17 # gray
# borders thin
borders = Borders()
borders.left = Borders.THIN
borders.right = Borders.THIN
borders.top = Borders.THIN
borders.bottom = Borders.THIN
# alignment horizontal center
alig_hc = Alignment()
# alig.horizontal = Alignment.HORZ_CENTER #no effect, why? f**k!!!
alig_hc.horz = Alignment.HORZ_CENTER
# title style
style_title = XFStyle()
style_title.font = font
style_title.pattern = pattern_yellow
style_title.alignment = alig_hc
sheet1.write_merge(
line_counter, line_counter, 0, 3, 'summary', style_title)
line_counter += 1
# table header style
sytle_tb_header = XFStyle()
sytle_tb_header.font = font
sytle_tb_header.pattern = pattern_gray
sytle_tb_header.borders = borders
sytle_tb_header.alignment = alig_hc
sheet1.row(line_counter).write(0, 'Member', sytle_tb_header)
sheet1.row(line_counter).write(1, 'Week Jobs', sytle_tb_header)
sheet1.row(line_counter).write(2, 'Risk', sytle_tb_header)
sheet1.row(line_counter).write(3, 'Next Week Plan', sytle_tb_header)
line_counter += 1
# content
# for i in range(line_counter,11+line_counter):
i = line_counter
for report in reports:
# alignment
alig = Alignment()
alig.horz = Alignment.HORZ_CENTER
alig.vert = Alignment.VERT_CENTER
alig.wrap = 1
# alignment2
alig2 = Alignment()
alig2.vert = Alignment.VERT_CENTER
alig2.wrap = 1
# borders
borders = Borders()
borders.left = Borders.THIN
borders.right = Borders.THIN
borders.top = Borders.THIN
borders.bottom = Borders.THIN
# colors
pattern = Pattern()
pattern.pattern = Pattern.SOLID_PATTERN
pattern.pattern_fore_colour = 0x2F
style_content = XFStyle()
style_content.alignment = alig
style_content.borders = borders
style_content2 = XFStyle()
style_content2.alignment = alig2
style_content2.borders = borders
style_content2.pattern = pattern
style_content3 = XFStyle()
style_content3.alignment = alig2
style_content3.borders = borders
sheet1.row(i).write(0, report.who, style_content)
sheet1.row(i).write(1, report.job, style_content2)
sheet1.row(i).write(2, report.risk, style_content2)
sheet1.row(i).write(3, report.plan, style_content3)
sheet1.row(i).height_mismatch = True
sheet1.row(i).height = 1500
i += 1
line_counter += len(reports)
line_counter += 1
sheet1.write_merge(
line_counter, line_counter, 0, 3, 'Main Issues List', style_title)
line_counter += 1
sheet1.row(line_counter).write(0, 'Items', sytle_tb_header)
sheet1.write_merge(
line_counter, line_counter, 1, 2, 'Deatail', sytle_tb_header)
sheet1.row(line_counter).write(3, 'Status', sytle_tb_header)
line_counter += 1
sheet1.col(0).width = 256 * 16
sheet1.col(1).width = 256 * 50
sheet1.col(2).width = 256 * 31
sheet1.col(3).width = 256 * 46
book.save(root + '/' + filename)
def bg_colour(self, colour):
pattern = Pattern()
pattern.pattern = Pattern.SOLID_PATTERN
pattern.pattern_fore_colour = colour
self.style.pattern = pattern
return self
def countNum():
category = {}
categoryNum = {
u'单选题-容易': 20,
u'单选题-中等': 15,
u'单选题-困难': 15,
u'多选题-容易': 12,
u'多选题-中等': 9,
u'多选题-困难': 9,
u'真假题-容易': 8,
u'真假题-中等': 6,
u'真假题-困难': 6,
}
result = {}
work_book = xlrd.open_workbook('../data/rubrics.xls')
sheet = work_book.sheet_by_index(0)
for row in range(3, sheet.nrows):
# 科目
subject = sheet.cell(row, 3).value
# 题目类型
type = sheet.cell(row, 2).value
# 难度
level = sheet.cell(row, 4).value
rubric_type = subject + '-' + type + '-' + level
if category.has_key(rubric_type):
num = category.get(rubric_type)
num = num + 1
category.update({rubric_type: num})
else:
category[rubric_type] = 1
for k in category:
# print k + ' : ' + str(category[k])
for j in categoryNum:
if j in k:
need = categoryNum[j] - category[k]
if need <= 0:
need = 0
result[k] = str(category[k]) + '|' + str(need) + '|' + str(
categoryNum[j])
# 科目
subjects = (u'鉴定', u'性能鉴定', u'综合业务部', u'制样', u'化验', u'煤炭检测', u'水尺计重',
u'煤炭采样', u'采样')
types = (
u'单选题',
u'多选题',
u'真假题',
)
levels = (u'容易', u'中等', u'困难')
work_book = xlwt.Workbook(encoding='ascii')
work_sheet = work_book.add_sheet('sheet1')
style = xlwt.XFStyle()
pattern = Pattern() # 创建一个模式
pattern.pattern = Pattern.SOLID_PATTERN # 设置其模式为实型
pattern.pattern_fore_colour = 5
# 设置单元格背景颜色 0 = Black, 1 = White, 2 = Red, 3 = Green, 4 = Blue, 5 = Yellow, 6 = Magenta, the list goes on...
style.pattern = pattern
work_sheet.write(0, 0, label=u'试题类型')
work_sheet.write(0, 1, label=u'当前题库中存在的试题个数')
work_sheet.write(0, 2, label=u'总共需要的试题个数')
work_sheet.write(0, 3, label=u'需要补充的试题个数')
index = 1
total = 0
for subject in subjects:
for type in types:
for level in levels:
stl = subject + '-' + type + '-' + level
if result.has_key(stl):
print "*********"
print stl
print result.get(stl)
data = (result.get(stl)).split('|')
work_sheet.write(index, 0, stl)
total = total + int(data[0])
work_sheet.write(index, 1, data[0])
work_sheet.write(index, 2, data[2])
work_sheet.write(index, 3, data[1])
index = index + 1
else:
for j in categoryNum:
if j in stl:
for q in categoryNum:
if q in j:
print stl
print categoryNum[q]
print '不存在'
work_sheet.write(index, 0, label=stl)
work_sheet.write(index, 1, label=0)
work_sheet.write(index,
2,
label=categoryNum[q])
work_sheet.write(index,
3,
label=categoryNum[q])
index = index + 1
work_book.save('out.xls')
print total
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 ot_register_report(self, cr, uid, ids, context=None):
fnt = Font()
fnt.name = 'Ubuntu Medium'
fnt.size = 16
fnt.style = 'Regular'
#Define the font attributes for header
content_fnt = Font()
content_fnt.name = 'Ubuntu Medium'
content_fnt.size = 16
content_fnt.style = 'Regular'
align_content = Alignment()
align_content.horz = Alignment.HORZ_CENTER
borders = Borders()
borders.left = 0x01
borders.right = 0x01
borders.top = 0x01
borders.bottom = 0x01
#The text should be centrally aligned
align = Alignment()
align.horz = Alignment.HORZ_CENTER
align.vert = Alignment.VERT_CENTER
#We set the backgroundcolour here
pattern = Pattern()
pattern.pattern = Pattern.SOLID_PATTERN
pattern.pattern_fore_colour = 0x1F
#apply the above settings to the row(0) header
style_header = XFStyle()
style_header.font = fnt
style_header.pattern = pattern
style_header.borders = borders
style_header.alignment = align
#Define the font attributes for header
fnt1 = Font()
fnt1.name = 'Ubuntu Medium'
fnt1.size = 10
fnt1.style = 'Regular'
content_fnt1 = Font()
content_fnt1.name = 'Ubuntu Medium'
content_fnt1.style = 'Regular'
align_content1 = Alignment()
align_content1.horz = Alignment.HORZ_LEFT
borders1 = Borders()
borders1.left = 0x1
borders1.right = 0x1
borders1.top = 0x1
borders1.bottom = 0x1
#The text should be centrally aligned
align1 = Alignment()
align1.horz = Alignment.HORZ_CENTER
align1.vert = Alignment.VERT_TOP
#We set the backgroundcolour here
pattern1 = Pattern()
pattern1.pattern1 = Pattern.SOLID_PATTERN
pattern1.pattern1_fore_colour = 0x1F
#apply the above settings to the row(0) header
style_header1 = XFStyle()
style_header1.font = fnt1
style_header1.pattern = pattern1
style_header1.borders = borders1
style_header1.alignment = align1
#Define the font attributes for Content
fnt3 = Font()
fnt3.name = 'Arial'
fnt3.size = '10'
fnt3.style = 'Regular'
content_fnt3 = Font()
content_fnt3.name = 'Arial'
content_fnt3.style = 'Regular'
align_content3 = Alignment()
align_content3.horz = Alignment.HORZ_LEFT
borders3 = Borders()
borders3.left = 0x0
borders3.right = 0x0
borders3.top = 0x0
borders3.bottom = 0x0
#The text should be centrally aligned
align3 = Alignment()
align3.horz = Alignment.HORZ_CENTER
align3.vert = Alignment.VERT_TOP
#We set the backgroundcolour here
pattern3 = Pattern()
#apply the above settings to the row(0) header
style_header3 = XFStyle()
style_header3.font = fnt3
style_header3.pattern = pattern3
style_header3.borders = borders3
style_header3.alignment = align3
wb = Workbook()
ws = wb.add_sheet('OT Register Report')
this = self.browse(cr, uid, ids[0], context=context)
year = this.month.year_id.id
month = this.month.month
emp_id = this.employee_id.id
company_id = this.company_id.id
ws.row(0).height = 300
ws.row(1).height = 300
ws.row(2).height = 300
ws.col(20).width = 6000
ws.write_merge(
0, 0, 4, 10, 'COMPANY : ' + this.company_id.name + ' ' +
this.company_id.street, style_header)
ws.write_merge(1, 1, 4, 10,
('SALARY REPORT FOR THE MONTH OF : ', this.month.name),
style_header)
ws.write(2, 0, 'PCard', style_header)
ws.col(0).width = 4000
ws.write(2, 1, 'Employee Name', style_header)
ws.col(1).width = 8000
ws.write(2, 2, 'Department Name', style_header)
ws.col(2).width = 5000
ws.write(2, 3, 'Designation Name', style_header)
ws.col(3).width = 5000
ws.write(2, 4, 'Total Working Day', style_header)
ws.col(4).width = 5000
ws.write(2, 5, 'CL + EL', style_header)
ws.col(5).width = 3000
ws.write(2, 6, 'Compensatory Leave', style_header)
ws.col(6).width = 5000
ws.write(2, 7, 'Sick Leave', style_header)
ws.col(7).width = 4000
ws.write(2, 8, 'Absent', style_header)
ws.col(8).width = 4000
ws.write(2, 9, 'Shift Hours Worked', style_header)
ws.col(9).width = 5000
ws.write(2, 10, 'Overtime Hours', style_header)
ws.col(10).width = 5000
ws.write(2, 11, 'Total Working Hours', style_header)
ws.col(11).width = 5000
ws.write(2, 12, 'Avg Weekly Working Hour', style_header)
ws.col(12).width = 6000
ws.write(2, 13, 'Avg Daily Working Hour', style_header)
ws.col(13).width = 6000
ws.write(2, 14, 'Total Monthly Gross', style_header)
ws.col(14).width = 5000
ws.write(2, 15, 'Gross Salary Paid', style_header)
ws.col(15).width = 5000
ws.write(2, 16, 'OT Salary', style_header)
ws.col(16).width = 5000
ws.write(2, 17, 'Total Gross Salary', style_header)
ws.col(17).width = 5000
i = 3
total_days = 0.0
total_cl_el = 0.0
total_compensatory = 0.0
total_sick = 0.0
total_absent = 0.0
total_shift_hours = 0.0
total_ot_hours = 0.0
total_working_hours = 0.0
total_weekly_working_hour = 0.0
total_daily_working_hour = 0.0
total_monthly_gross = 0.0
total_gross_salary_paid = 0.0
total_ot_salary = 0.0
total_gross_salary = 0.0
emp_obj = self.pool.get('hr.employee')
if this.employee_id and this.company_id:
list_ids = emp_obj.search(cr, uid,
[('id', '=', emp_id),
('company_id', '=', company_id),
('type', '=', 'Employee'),
('active', '=', True)])
elif this.company_id:
list_ids = emp_obj.search(cr, uid,
[('company_id', '=', company_id),
('type', '=', 'Employee'),
('active', '=', True)])
list_ids1 = emp_obj.search(cr, uid,
[('company_id', '=', company_id),
('type', '=', 'Employee'),
('active', '=', False)])
list_ids = list_ids + list_ids1
elif this.employee_id:
list_ids = emp_obj.search(cr, uid, [('id', '=', emp_id),
('type', '=', 'Employee'),
('active', '=', True)])
if len(list_ids) == 1:
query ="select spl.sinid,spl.employee_name,spl.department_name,spl.job_name,(spl.work_day+spl.factory_work),(spl.casual_leave+spl.earned_leave),spl.compensatory_leave,spl.sick_leave,(spl.month_days-(spl.work_day+spl.factory_work+spl.casual_leave+spl.earned_leave+spl.compensatory_leave+spl.sick_leave+spl.holiday_leave+spl.week_leave)),"\
"((spl.work_day+spl.factory_work) * 8),(spl.over_time + spl.sun_over_time),(((spl.work_day+spl.factory_work) * 8)+(spl.over_time + spl.sun_over_time)),"\
"spl.month_days,"\
"round(((((spl.work_day+spl.factory_work) * 8)+(spl.over_time + spl.sun_over_time))/(spl.work_day+spl.factory_work)),2) ,(spl.basic + spl.other_salary),(spl.days_amount+spl.other_salary_amount),spl.total_overtime_amount,"\
"(spl.days_amount+spl.other_salary_amount+spl.total_overtime_amount) from salary_payment_line as spl where spl.employee_id = '"+str(list_ids[0])+"' and spl.month='"+str(month)+"' and spl.year_id='"+str(year)+"' order by spl.sinid"
cr.execute(query)
temp = cr.fetchall()
else:
query ="select spl.sinid,spl.employee_name,spl.department_name,spl.job_name,(spl.work_day+spl.factory_work),(spl.casual_leave+spl.earned_leave),spl.compensatory_leave,spl.sick_leave,(spl.month_days-(spl.work_day+spl.factory_work+spl.casual_leave+spl.earned_leave+spl.compensatory_leave+spl.sick_leave+spl.holiday_leave+spl.week_leave)),"\
"((spl.work_day+spl.factory_work) * 8),(spl.over_time + spl.sun_over_time),(((spl.work_day+spl.factory_work) * 8)+(spl.over_time + spl.sun_over_time)),"\
"spl.month_days,"\
"round(((((spl.work_day+spl.factory_work) * 8)+(spl.over_time + spl.sun_over_time))/(spl.work_day+spl.factory_work)),2) ,(spl.basic + spl.other_salary),(spl.days_amount+spl.other_salary_amount),spl.total_overtime_amount,"\
"(spl.days_amount+spl.other_salary_amount+spl.total_overtime_amount) from salary_payment_line as spl where spl.employee_id in "+str(tuple(list_ids))+" and spl.month='"+str(month)+"' and spl.year_id='"+str(year)+"' order by spl.sinid"
cr.execute(query)
temp = cr.fetchall()
if not temp:
raise osv.except_osv(_('Warning !'), _("Record Not Found !!!"))
for val in temp:
if val[4] >= 7:
working_hour = round((val[11] / (val[12] - val[8])) * 7, 2)
else:
working_hour = 0.00
total_days += val[4]
total_cl_el += val[5]
total_compensatory += val[6]
total_sick += val[7]
total_absent += val[8]
total_shift_hours += val[9]
total_ot_hours += val[10]
total_working_hours += val[11]
total_daily_working_hour += val[13]
total_monthly_gross += val[14]
total_gross_salary_paid += val[15]
total_ot_salary += val[16]
total_gross_salary += val[17]
total_weekly_working_hour += working_hour
ws.write(i, 0, val[0], style_header3)
ws.write(i, 1, val[1], style_header3)
ws.write(i, 2, val[2], style_header3)
ws.write(i, 3, val[3], style_header3)
ws.write(i, 4, val[4], style_header3)
ws.write(i, 5, val[5], style_header3)
ws.write(i, 6, val[6], style_header3)
ws.write(i, 7, val[7], style_header3)
ws.write(i, 8, val[8], style_header3)
ws.write(i, 9, val[9], style_header3)
ws.write(i, 10, val[10], style_header3)
ws.write(i, 11, val[11], style_header3)
ws.write(i, 12, working_hour, style_header3)
ws.write(i, 13, val[13], style_header3)
ws.write(i, 14, val[14], style_header3)
ws.write(i, 15, val[15], style_header3)
ws.write(i, 16, val[16], style_header3)
ws.write(i, 17, val[17], style_header3)
i = i + 1
ws.write(i, 0, '', style_header3)
ws.write(i, 1, '', style_header3)
ws.write(i, 2, '', style_header3)
ws.write(i, 3, 'TOTAL', style_header1)
ws.write(i, 4, total_days, style_header1)
ws.write(i, 5, total_cl_el, style_header1)
ws.write(i, 6, total_compensatory, style_header1)
ws.write(i, 7, total_sick, style_header1)
ws.write(i, 8, total_absent, style_header1)
ws.write(i, 9, total_shift_hours, style_header1)
ws.write(i, 10, total_ot_hours, style_header1)
ws.write(i, 11, total_working_hours, style_header1)
ws.write(i, 12, total_weekly_working_hour, style_header1)
ws.write(i, 13, total_daily_working_hour, style_header1)
ws.write(i, 14, total_monthly_gross, style_header1)
ws.write(i, 15, total_gross_salary_paid, style_header1)
ws.write(i, 16, total_ot_salary, style_header1)
ws.write(i, 17, total_gross_salary, style_header1)
f = cStringIO.StringIO()
wb.save(f)
out = base64.encodestring(f.getvalue())
ot_report = self.write(cr,
uid,
ids, {
'export_data': out,
'filename': 'OT Register Report.xls'
},
context=context)
return ot_report
def exportReport1ToExcel(self,objectProject):
book = Workbook();
sheet1 = book.add_sheet('Sheet 1');
sheet1.col(1).width = 256*20;
sheet1.col(2).width = 256*80;
sheet1.col(3).width = 256*10;
sheet1.col(4).width = 256*10;
sheet1.col(5).width = 256*20;
sheet1.col(6).width = 256*20;
borders = Borders()
borders.left = Borders.THIN
borders.right = Borders.THIN
borders.top = Borders.THIN
borders.bottom = Borders.THIN
pattern = Pattern();
pattern.pattern = Pattern.SOLID_PATTERN
pattern.pattern_fore_colour = 23
wrap = Alignment();
wrap.wrap = 1;
wrap.vert = Alignment.VERT_TOP
alignHeader = Alignment();
alignHeader.horz = Alignment.HORZ_CENTER;
alignTop = Alignment();
alignTop.vert = Alignment.VERT_TOP
fnt = Font()
fnt.name = 'Arial'
fnt.colour_index = 4
fnt.bold = True
styleWrap = XFStyle();
styleWrap.alignment = wrap;
styleHead = XFStyle();
styleHead.font = fnt;
styleHead.borders = borders;
styleHead.pattern = pattern;
styleHead.alignment = alignHeader;
styleRowDetail = XFStyle();
styleRowDetail.borders = borders;
styleRowDetail.alignment = alignTop;
styleDate = XFStyle()
styleDate.num_format_str = 'DD-MM-YYYY' ; #'D-MMM-YY';
styleDate.borders = borders;
styleDate.alignment = alignTop;
StyleRowDetailWrap = styleRowDetail ;
StyleRowDetailWrap.alignment = wrap;
if( objectProject):
i=0;
row1 = sheet1.row(i) ;
row1.write(0, ('ลำดับที่').decode('UTF8') ,styleHead);
#sheet1.write_merge(i, i, 1, 2, ('รายละเอียด').decode('UTF8') );
row1.write(1, ('เลขความเสี่ยง').decode('UTF8'),styleHead );
row1.write(2, ('อุบัติการณ์/ภาวะไม่พึงประสงค์').decode('UTF8'),styleHead);
row1.write(3, ('วันที่รายงาน').decode('UTF8'),styleHead );
row1.write(4, ('ความรุนแรง').decode('UTF8'),styleHead );
row1.write(5, ('ด้าน/โปรแกรม').decode('UTF8') ,styleHead);
row1.write(6, ('หน่วยที่รายงาน').decode('UTF8') ,styleHead);
i=i+1;
for value in objectProject:
row1 = sheet1.row(i) ;
row1.write(0, value.get('row') ,styleRowDetail );
row1.write(1, str(value.get('risk_id')).decode('UTF8'),styleRowDetail );
row1.write(2, value.get('detail').decode('UTF8'),StyleRowDetailWrap );
row1.write(3, value.get('report_date') ,styleDate );
row1.write(4, value.get('level').decode('UTF8') ,styleRowDetail );
row1.write(5, value.get('pro').decode('UTF8') ,styleRowDetail );
row1.write(6, value.get('reporter').decode('UTF8') ,styleRowDetail );
i=i+1;
row2 = sheet1.row(i) ;
row2.write(2, ('รายละเอียด').decode('UTF8'),styleHead);
row2.write(3, ('หน่วยที่ตอบ').decode('UTF8'),styleHead );
row2.write(4, ('ระยะเวลาตอบ').decode('UTF8'),styleHead );
i=i+1;
for resp in value.get('responsible'):
row2 = sheet1.row(i) ;
row2.write(2, str(resp.get('detail')).decode('UTF8'),StyleRowDetailWrap);
row2.write(3, str(resp.get('service_name')).decode('UTF8'),styleRowDetail );
row2.write(4, str(resp.get('report_date')).decode('UTF8'),styleRowDetail );
i=i+1;
dirTempFile = gettempdir() + _os.sep + str('simpleReport1.xls');
book.save(dirTempFile);
return dirTempFile;
def 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 exportToExcel(self,objectProject):
book = Workbook();
sheet1 = book.add_sheet('Sheet 1')
sheet1.col(1).width = 256*20;
sheet1.col(2).width = 256*80;
sheet1.col(3).width = 256*10;
sheet1.col(4).width = 256*20;
default_book_style = book.default_style
default_book_style.font.height = 20 * 36 # 36pt
fnt = Font()
fnt.name = 'Arial'
fnt.colour_index = 4
fnt.bold = True
borders = Borders()
borders.left = Borders.THIN
borders.right = Borders.THIN
borders.top = Borders.THIN
borders.bottom = Borders.THIN
pattern = Pattern();
pattern.pattern = Pattern.SOLID_PATTERN
pattern.pattern_fore_colour = 23
algn1 = Alignment();
algn1.wrap = 1;
#algn1.horz = Alignment.HORZ_CENTER
#algn1.vert = Alignment.VERT_TOP
alignHeader = Alignment();
alignHeader.horz = Alignment.HORZ_CENTER;
alignTop = Alignment();
alignTop.vert = Alignment.VERT_TOP
print "export";
if( objectProject):
i=0;
print "start" ;
styleHead = XFStyle();
styleHead.font = fnt;
styleHead.borders = borders;
styleHead.pattern = pattern;
styleHead.alignment = alignHeader;
row1 = sheet1.row(i) ;
row1.write(0, ('risk id').decode('UTF8'),styleHead );
sheet1.write_merge(i, i, 1, 2, ('รายละเอียด').decode('UTF8') ,styleHead );
# row1.write(1, ('รายละเอียด').decode('UTF8'));
row1.write(3, ('วันที่รายงาน').decode('UTF8'), styleHead );
row1.write(4, ('หน่วยที่รายงาน').decode('UTF8'), styleHead );
i=i+1;
style1 = XFStyle();
style1.alignment = algn1;
#style0 = xlwt.easyxf('font: name Times New Roman size 20, color-index black, bold on')
for value in objectProject:
row1 = sheet1.row(i) ;
styleRowDetail = XFStyle();
styleRowDetail.borders = borders;
styleRowDetail.alignment = alignTop;
StyleRowDetailWrap = styleRowDetail ;
StyleRowDetailWrap.alignment = algn1;
styleDate = XFStyle()
styleDate.num_format_str = 'DD-MM-YYYY' ; #'D-MMM-YY';
styleDate.borders = borders;
row1.write(0, value.get('risk_management_id'),styleRowDetail );
#row1.write(1, value.get('risk_detail').decode('UTF8') , style1);
sheet1.write_merge(i, i, 1, 2, value.get('risk_detail').decode('UTF8') , StyleRowDetailWrap );
row1.write(3, value.get('report_date') ,styleDate);
row1.write(4, value.get('report').decode('UTF8') ,styleRowDetail );
i=i+1;
row1 = sheet1.row(i) ;
row1.write(0," " );
row1.write(1,('หน่วยที่เกี่ยวข้อง').decode('UTF8') ,styleHead );
sheet1.write_merge(i, i, 2, 3,('รายละเอียดการตอบ').decode('UTF8') , styleHead );
i=i+1;
for sub in value.get('response') :
row1 = sheet1.row(i) ;
row1.write(0," " );
row1.write(1,sub.get('risk_team').decode('UTF8') , styleRowDetail );
sheet1.write_merge(i, i, 2, 3,sub.get('result').decode('UTF8') , StyleRowDetailWrap );
i=i+1;
dirTempFile = gettempdir() + _os.sep + str('simple.xls');
print dirTempFile;
book.save(dirTempFile);
def model_run( self, model_context ):
from decimal import Decimal
from xlwt import Font, Borders, XFStyle, Pattern, Workbook
from camelot.view.utils import ( local_date_format,
local_datetime_format,
local_time_format )
from camelot.view import action_steps
#
# setup worksheet
#
yield action_steps.UpdateProgress( text = _('Create worksheet') )
admin = model_context.admin
workbook = Workbook()
worksheet = workbook.add_sheet('Sheet1')
#
# keep a global cache of styles, since the number of styles that
# can be used is limited.
#
styles = dict()
freeze = lambda d:tuple(sorted(d.iteritems()))
def get_style( font_specs=dict(),
border_specs = dict(),
pattern = None,
num_format_str = None, ):
style_key = ( freeze(font_specs),
freeze(border_specs),
pattern,
num_format_str )
try:
return styles[style_key]
except KeyError:
style = XFStyle()
style.font = Font()
for key, value in font_specs.items():
setattr( style.font, key, value )
style.borders = Borders()
for key, value in border_specs.items():
setattr( style.borders, key, value )
if pattern:
style.pattern = pattern
if num_format_str:
style.num_format_str = num_format_str
styles[ style_key ] = style
return style
#
# write style
#
title_style = get_style( dict( font_name = self.font_name,
bold = True,
height = 240 ) )
worksheet.write( 0, 0, admin.get_verbose_name_plural(), title_style )
#
# create some patterns and formats
#
date_format = local_date_format()
datetime_format = local_datetime_format()
time_format = local_time_format()
header_pattern = Pattern()
header_pattern.pattern = Pattern.SOLID_PATTERN
header_pattern.pattern_fore_colour = 0x16
#
# write headers
#
columns = admin.get_columns()
field_names = []
for i, (name, field_attributes) in enumerate( columns ):
verbose_name = unicode( field_attributes.get( 'name', name ) )
field_names.append( name )
font_specs = dict( font_name = self.font_name,
bold = True,
height = 200 )
border_specs = dict( top = 0x01 )
name = unicode( name )
if i == 0:
border_specs[ 'left' ] = 0x01
elif i == len( columns ) - 1:
border_specs[ 'right' ] = 0x01
header_style = get_style( font_specs, border_specs, header_pattern )
worksheet.write( 2, i, verbose_name, header_style)
if len( name ) < 8:
worksheet.col( i ).width = 8 * 375
else:
worksheet.col( i ).width = len( verbose_name ) * 375
#
# write data
#
offset = 3
for j, obj in enumerate( model_context.get_collection( yield_per = 100 ) ):
dynamic_attributes = admin.get_dynamic_field_attributes( obj,
field_names )
row = offset + j
if j % 100 == 0:
yield action_steps.UpdateProgress( j, model_context.collection_count )
for i, ((_name, attributes), delta_attributes) in enumerate( zip( columns, dynamic_attributes ) ):
attributes.update( delta_attributes )
value = attributes['getter']( obj )
format_string = '0'
if value != None:
if isinstance( value, Decimal ):
value = float( str( value ) )
if isinstance( value, (unicode, str) ):
if attributes.get( 'translate_content', False ) == True:
value = ugettext( value )
# handle fields of type code
elif isinstance( value, list ):
value = u'.'.join(value)
elif isinstance( value, float ):
precision = attributes.get( 'precision', 2 )
format_string = '0.' + '0'*precision
elif isinstance( value, int ):
format_string = '0'
elif isinstance( value, datetime.date ):
format_string = date_format
elif isinstance( value, datetime.datetime ):
format_string = datetime_format
elif isinstance( value, datetime.time ):
format_string = time_format
else:
value = unicode( value )
else:
# empty cells should be filled as well, to get the
# borders right
value = ''
font_specs = dict( font_name = self.font_name, height = 200 )
border_specs = dict()
if i == 0:
border_specs[ 'left' ] = 0x01
elif i == len( columns ) - 1:
border_specs[ 'right' ] = 0x01
if (row - offset + 1) == model_context.collection_count:
border_specs[ 'bottom' ] = 0x01
style = get_style( font_specs,
border_specs,
None,
format_string )
worksheet.write( row, i, value, style )
min_width = len( unicode( value ) ) * 300
worksheet.col( i ).width = max( min_width, worksheet.col( i ).width )
yield action_steps.UpdateProgress( text = _('Saving file') )
filename = action_steps.OpenFile.create_temporary_file( '.xls' )
workbook.save( filename )
yield action_steps.UpdateProgress( text = _('Opening file') )
yield action_steps.OpenFile( 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 transData(res, pars):
m_f = int(pars["f"])
max_refer = 4
k_1 = [
"1.显示cookie唯一数&总数",
"1.显示ip唯一数&总数",
"1.点击cookie唯一数&总数",
"1.点击ip唯一数&总数",
"1.一个cookie对应各3个不同ip数的个数",
"1.一个cookie对应各4个不同ip数的个数",
"1.一个cookie对应各大于4个不同ip数的个数",
]
k_2 = ["2.显示点击重合"]
k_3 = ["3.点击频次高的cookie所对应的显示数", "3.显示频次高的cookie所对应的点击数"] #
k_4 = ["4.refer显示数&点击数"]
data = []
(imp_cookie_dis, imp_cookie_all) = transNULL(res[k_1[0]][0].split("\t"))
(imp_ip_dis, imp_ip_all) = transNULL(res[k_1[1]][0].split("\t"))
(clk_cookie_dis, clk_cookie_all) = transNULL(res[k_1[2]][0].split("\t"))
(clk_ip_dis, clk_ip_all) = transNULL(res[k_1[3]][0].split("\t"))
(one_cookie_3_ip_dis, one_cookie_3_ip_all) = transNULL(res[k_1[4]][0].split("\t"))
(one_cookie_4_ip_dis, one_cookie_4_ip_all) = transNULL(res[k_1[5]][0].split("\t"))
(one_cookie_b_4_ip_dis, one_cookie_b_4_ip_all) = transNULL(res[k_1[6]][0].split("\t"))
(imp_join_clk_cookie_dis, imp_join_clk_cookie_clk) = transNULL(res[k_2[0]][0].split("\t"))
(tb_imp_f_list, tb_imp_f_sum, sum_imp_str) = transDataList_imp(res[k_3[0]], m_f, imp_cookie_dis, imp_cookie_all)
(tb_clk_f_list, tb_clk_f_sum, sum_clk_str) = transDataList_clk(res[k_3[1]], m_f, imp_cookie_dis, imp_cookie_all)
(tb_referer_list, sum_referer_str) = transDataList_referer(res[k_4[0]], max_refer, imp_cookie_all, clk_cookie_all)
# 排查3
data.append(transCellData("A5", imp_ip_all)) # IP显示总数
data.append(transCellData("B5", imp_cookie_all)) # cookie显示总数
data.append(transCellData_ext("C5", imp_ip_all, imp_cookie_all, 1)) # 显示差值
data.append(transCellData("D5", clk_ip_all)) # IP点击总数
data.append(transCellData("E5", clk_cookie_all)) # cookie点击总数
data.append(transCellData_ext("F5", clk_ip_all, clk_cookie_all, 1)) # 点击差值
# 排查3 判断
conclusion_str = ""
if imp_ip_all == imp_cookie_all and clk_ip_all == clk_cookie_all:
conclusion_str = "正常"
else:
conclusion_str = "异常"
conclusion_str = uniCode(conclusion_str)
ts = transCellData_summary("D2", conclusion_str)
##ts[1].borders.right = Borders.THICK ?
ts[1].alignment.vert = Alignment.VERT_CENTER
data.append(ts)
data.append(transCellData("A10", imp_ip_dis)) # IP唯一显示数
data.append(transCellData("B10", imp_cookie_dis)) # cookie唯一显示数
data.append(transCellData_ext("C10", imp_cookie_dis, imp_ip_dis, 1)) # 显示差异比例
data.append(transCellData("D10", clk_ip_dis)) # IP唯一点击数
data.append(transCellData("E10", clk_cookie_dis)) # cookie唯一点击数
data.append(transCellData_ext("F10", clk_cookie_dis, clk_ip_dis, 1)) # 点击差异比例
ts = transCellData_ext("A12", one_cookie_3_ip_dis, 0, imp_cookie_dis)
ts[1].num_format_str = "0.00%"
data.append(ts) # 一个cookie对应3个ip的cookie占比
ts = transCellData_ext("B12", one_cookie_3_ip_all, 0, imp_cookie_all)
ts[1].num_format_str = "0.00%"
data.append(ts) # 其显示占比
ts = transCellData_ext("C12", one_cookie_4_ip_dis, 0, imp_cookie_dis)
ts[1].num_format_str = "0.00%"
data.append(ts) # 一个cookie对应4个ip的cookie占比
ts = transCellData_ext("D12", one_cookie_4_ip_all, 0, imp_cookie_all)
ts[1].num_format_str = "0.00%"
data.append(ts) # 其显示占比
ts = transCellData_ext("E12", one_cookie_b_4_ip_dis, 0, imp_cookie_dis)
ts[1].num_format_str = "0.00%"
data.append(ts) # 一个cookie对应4个ip以上的cookie占比
ts = transCellData_ext("F12", one_cookie_b_4_ip_all, 0, imp_cookie_all)
ts[1].num_format_str = "0.00%"
data.append(ts) # 其显示占比
data.append(transCellData("A19", clk_cookie_all)) # 点击总数
data.append(transCellData("B19", imp_cookie_all)) # 显示cookie总数(唯一)
data.append(transCellData("C19", clk_cookie_all)) # 点击cookie总数(唯一)
data.append(transCellData_ext("D19", clk_cookie_all, imp_cookie_all, 1)) # cookie重合数1
data.append(transCellData("E19", imp_join_clk_cookie_clk)) # cookie重合数2
data.append(transCellData_ext("F19", clk_cookie_all, imp_join_clk_cookie_clk, 1)) # 无显示cookie
ts = transCellData_ext("G19", clk_cookie_dis, imp_join_clk_cookie_dis, clk_cookie_dis)
ts[1].num_format_str = "0.00%"
data.append(ts) # 无显示cookie占比
ts = transCellData_ext("H19", clk_cookie_all, imp_join_clk_cookie_clk, clk_cookie_all)
ts[1].num_format_str = "0.00%"
data.append(ts) # 点击数占总体
intindex = 27
for tt in tb_imp_f_list:
ts = transCellData("A%d" % intindex, tt[0])
if tt[0] < 0:
ts[0][2] = len(tb_imp_f_list) - 1
ts[1].num_format_str = "0+"
data.append(ts)
data.append(transCellData("B%d" % intindex, tt[1]))
ts = transCellData_ext("C%d" % intindex, tt[2], 0, 1)
ts[1].num_format_str = "0.00%"
data.append(ts)
data.append(transCellData("D%d" % intindex, tt[3]))
ts = transCellData_ext("E%d" % intindex, tt[4], 0, 1)
ts[1].num_format_str = "0.00%"
data.append(ts)
data.append(transCellData("F%d" % intindex, tt[5]))
data.append(transCellData("G%d" % intindex, tt[6]))
intindex += 1
intindex = 37
pattern = Pattern()
pattern.pattern = Style.pattern_map["solid"]
###pattern.pattern_back_colour = 0xBFBFBF
pattern.pattern_fore_colour = 0x37
ts = transCellData("B%d" % intindex, tb_imp_f_sum[0][1])
ts[1].pattern = pattern
data.append(ts)
ts = transCellData_ext("C%d" % intindex, tb_imp_f_sum[0][2], 0, 1)
ts[1].num_format_str = "0.00%"
ts[1].pattern = pattern
data.append(ts)
ts = transCellData("D%d" % intindex, tb_imp_f_sum[0][3])
ts[1].pattern = pattern
data.append(ts)
ts = transCellData_ext("E%d" % intindex, tb_imp_f_sum[0][4], 0, 1)
ts[1].num_format_str = "0.00%"
ts[1].pattern = pattern
data.append(ts)
ts = transCellData("F%d" % intindex, tb_imp_f_sum[0][5])
ts[1].pattern = pattern
data.append(ts)
ts = transCellData("G%d" % intindex, tb_imp_f_sum[0][6])
ts[1].pattern = pattern
data.append(ts)
intindex = 39
for tt in tb_clk_f_list:
ts = transCellData("A%d" % intindex, tt[0])
if tt[0] < 0:
ts[0][2] = len(tb_imp_f_list) - 1
ts[1].num_format_str = "0+"
data.append(ts)
data.append(transCellData("B%d" % intindex, tt[1]))
ts = transCellData_ext("C%d" % intindex, tt[2], 0, 1)
ts[1].num_format_str = "0.00%"
data.append(ts)
data.append(transCellData("D%d" % intindex, tt[3]))
ts = transCellData_ext("E%d" % intindex, tt[4], 0, 1)
ts[1].num_format_str = "0.00%"
data.append(ts)
data.append(transCellData("F%d" % intindex, tt[5]))
data.append(transCellData("G%d" % intindex, tt[6]))
intindex += 1
intindex = 49
ts = transCellData("B%d" % intindex, tb_clk_f_sum[0][1])
ts[1].pattern = pattern
data.append(ts)
ts = transCellData_ext("C%d" % intindex, tb_clk_f_sum[0][2], 0, 1)
ts[1].num_format_str = "0.00%"
ts[1].pattern = pattern
data.append(ts)
ts = transCellData("D%d" % intindex, tb_clk_f_sum[0][3])
ts[1].pattern = pattern
data.append(ts)
ts = transCellData_ext("E%d" % intindex, tb_clk_f_sum[0][4], 0, 1)
ts[1].num_format_str = "0.00%"
ts[1].pattern = pattern
data.append(ts)
ts = transCellData("F%d" % intindex, tb_clk_f_sum[0][5])
ts[1].pattern = pattern
data.append(ts)
ts = transCellData("G%d" % intindex, tb_clk_f_sum[0][6])
ts[1].pattern = pattern
data.append(ts)
intindex = 53
for tt in tb_referer_list:
data.append(transCellData_str("B%d" % intindex, tt[0]))
ts = transCellData_ext("E%d" % intindex, tt[1], 0, 1)
ts[1].num_format_str = "0.00%"
data.append(ts)
ts = transCellData_ext("G%d" % intindex, tt[2], 0, 1)
ts[1].num_format_str = "0.00%"
data.append(ts)
ts = transCellData_ext("I%d" % intindex, tt[3], 0, 1)
ts[1].num_format_str = "0.00%"
data.append(ts)
intindex += 1
##################summary####################
test_str = "有%d个url产生了%s的显示及%s的点击，点击率约%s" % (max_refer, sum_referer_str[0], sum_referer_str[1], sum_referer_str[2])
test_str = uniCode(test_str)
ts = transCellData_summary("D52", test_str)
data.append(ts)
test_str = "%d频次以上Cookie占比%s；\n%d频次以上显示数占比%s" % (m_f, sum_imp_str[0], m_f, sum_imp_str[1])
test_str = uniCode(test_str)
ts = transCellData_summary("D24", test_str)
ts[1].borders.right = Borders.NO_LINE
ts[1].borders.bottom = Borders.NO_LINE
ts[1].alignment.wrap = 1
data.append(ts)
test_str = "点击%d次及以上的Cookie占比%s；\n点击%d次及以上的点击数占比%s" % (m_f, sum_clk_str[0], m_f, sum_clk_str[1])
test_str = uniCode(test_str)
ts = transCellData_summary("D25", test_str)
ts[1].borders.right = Borders.NO_LINE
ts[1].borders.top = Borders.NO_LINE
ts[1].alignment.wrap = 1
data.append(ts)
if float(clk_cookie_dis) == 0:
test_str = "没有点击Cookie"
else:
if clk_cookie_dis != 0:
val1_ = 100 * (float(clk_cookie_dis) - float(imp_join_clk_cookie_dis)) / float(clk_cookie_dis)
else:
val1_ = 0
if clk_cookie_all != 0:
val2_ = 100 * (float(clk_cookie_all) - float(imp_join_clk_cookie_clk)) / float(clk_cookie_all)
else:
val2_ = 0
test_str = "%s的点击Cookie没有显示；点击数占总比%s" % ("%0.2f" % val1_ + "%", "%0.2f" % val2_ + "%")
test_str = uniCode(test_str)
ts = transCellData_summary("D17", test_str)
##ts[1].borders.right = Borders.THICK ?
ts[1].alignment.vert = Alignment.VERT_CENTER
data.append(ts)
val1_ = 100 * sumPercent([one_cookie_3_ip_dis, one_cookie_4_ip_dis, one_cookie_b_4_ip_dis], imp_cookie_dis)
val2_ = 100 * sumPercent([one_cookie_3_ip_all, one_cookie_4_ip_all, one_cookie_b_4_ip_all], imp_cookie_all)
test_str = "1个Cookie对应2个以上IP的Cookie占比%s;\n显示占总体%s" % ("%0.2f" % val1_ + "%", "%0.2f" % val2_ + "%")
test_str = uniCode(test_str)
ts = transCellData_summary("D8", test_str)
ts[1].alignment.wrap = 1
data.append(ts)
return data
def archivo_excel(titulo, archivo_logo = 'unprg.bmp', label_resumen = [], datos_resumen = [], heads = [], color = 0x9ACD32, registros = [], nombre_archivo = 'descargar.xls'):
book = Workbook()
sheet1 = book.add_sheet('Hoja 01')
#estilos de celda titulo
fnt_titulo = Font()
fnt_titulo.name = 'Arial'
fnt_titulo.bold = True
style_titulo = XFStyle()
style_titulo.font = fnt_titulo
#estilos de celda etiqueta resumen
fnt_etiqueta_resumen = Font()
fnt_etiqueta_resumen.name = 'Arial'
fnt_etiqueta_resumen.bold = True
style_etiqueta_resumen = XFStyle()
style_etiqueta_resumen.font = fnt_etiqueta_resumen
#estilos de celda datos resumen
fnt_dato_resumen = Font()
fnt_dato_resumen.name = 'Arial'
style_dato_resumen = XFStyle()
style_dato_resumen.font = fnt_dato_resumen
#estilos de celda heads
fnt_heads = Font()
fnt_heads.name = 'Arial'
fnt_heads.bold = True
borders_heads = Borders()
borders_heads.left = Borders.THIN
borders_heads.right = Borders.THIN
borders_heads.top = Borders.THIN
borders_heads.bottom = Borders.THIN
pattern_heads = Pattern()
pattern_heads.pattern = Pattern.SOLID_PATTERN
pattern_heads.pattern_fore_colour = color
style_heads = XFStyle()
style_heads.font = fnt_heads
style_heads.borders = borders_heads
style_heads.pattern = pattern_heads
#estilos de celda registros
fnt_registros = Font()
fnt_registros.name = 'Arial'
borders_registros = Borders()
borders_registros.left = Borders.THIN
borders_registros.right = Borders.THIN
borders_registros.top = Borders.THIN
borders_registros.bottom = Borders.THIN
style_registros = XFStyle()
style_registros.font = fnt_registros
style_registros.borders = borders_registros
sheet1.insert_bitmap(settings.MEDIA_ROOT + 'archivos_excel/%s' % archivo_logo, 1, 0)
#escribir el titulo
sheet1.write(10,0,titulo, style_titulo)
row = 12
col = 0
#escribir las etiquetas del resumen
for etiqueta in label_resumen:
sheet1.write(row, col, etiqueta, style_etiqueta_resumen)
row+=1
row = 12
col = 1
#escribir los datos del resumen
for dato in datos_resumen:
sheet1.write(row, col, dato, style_dato_resumen)
row+=1
row+=1
col = 0
#escribimos los encabezados
for head in heads:
sheet1.write(row,col,head,style_heads)
col+=1
row+=1
col=1
n = 1
#recorremos la lista y escribimos los datos
for fila in registros:
sheet1.write(row,0,n,style_registros)
for dato in fila:
sheet1.write(row,col,dato,style_registros)
col+=1
col=1
row+=1
n+=1
#book.save(settings.MEDIA_ROOT + 'archivos_excel/%s' % nombre_archivo)
book.save(TemporaryFile())
def crawl_one_simple(sheet, rule, idx, url, new_rows, brand_id):
try:
# 样式
style = XFStyle()
pattern = Pattern()
pattern.pattern = Pattern.SOLID_PATTERN
pattern.pattern_fore_colour = xlwt.Style.colour_map[
'yellow'] # 设置单元格背景色为黄色
style.pattern = pattern
# 首先检查数据库，提取出url中的id
parts = url.split('?', 1)
prefix = parts[0]
found_one = ""
if len(parts) > 1:
parts = parts[1].split('&')
for one in parts:
if one.startswith('id='):
found_one = one
break
if len(found_one) > 0:
rows = list(
CrawlProduct.objects.filter(
Q(url__contains=found_one)
| Q(tmall_id__iexact=found_one)))
if len(rows) > 0:
# 已有商品
row = rows[0]
sheet.write(idx, 0, url)
sheet.write(idx, 1, row.sku)
sheet.write(idx, 2, row.title)
sheet.write(idx, 3, row.subtitle)
sheet.write(idx, 4, row.colors)
return
# 新商品
r = requests.get(url)
sheet.write(idx, 0, url)
if int(r.status_code) == 200:
content = etree.HTML(r.text)
# 标题
nodes = content.xpath(rule[0])
h1 = ""
for one in nodes:
one = one.strip()
if len(one) > 0:
h1 = h1 + one
sheet.write(idx, 2, h1)
# 副标题
nodes = content.xpath(rule[1])
h2 = ""
for one in nodes:
one = one.strip()
if len(one) > 0:
h2 = h2 + one
sheet.write(idx, 3, h2)
# 款号
nodes = content.xpath(rule[2])
sku = ''
for one in nodes:
one = one.strip()
if len(one) > 0:
for key in keywords:
one = one.replace(key, "")
sku = sku + one.strip()
sheet.write(idx, 1, sku, style=style)
# 颜色
# colors=content.xpath("//div[@class='tb-sku']//ul[contains(@data-property,'颜色')]/li/@title")
colors = content.xpath(rule[3])
_color = ""
if colors:
_color = ",".join(colors)
sheet.write(idx, 4, _color)
# 尺码
# sizes
new_rows.append(
CrawlProduct(brand_id=brand_id,
sku=sku,
title=h1,
subtitle=h2,
colors=_color,
url=url,
status=CrawlStatus.ONLINE))
print("%s,%s,%s,%s,%s" % (url, sku, h1, h2, _color))
else:
sts_code = int(r.status_code)
if sts_code >= 300:
new_rows.append(
CrawlProduct(brand_id=brand_id,
sku='',
title='',
subtitle='',
colors='',
url=url,
status=CrawlStatus.TODO))
# except RequestException as e:
except Exception as e:
traceback.print_exc()
# 当时爬取失败
new_rows.append(
CrawlProduct(brand_id=brand_id,
sku='',
title='',
subtitle='',
colors='',
url=url,
status=CrawlStatus.TODO))
def archivo_excel1(titulo, archivo_logo = 'unprg.bmp', label_resumen = [], datos_resumen = [], heads = [], color = 0x9ACD32, registros = [], nombre_archivo = 'descargar.xls'):
import StringIO
output = StringIO.StringIO()
from django.http import HttpResponse
book = Workbook()
sheet1 = book.add_sheet('Hoja 01')
#estilos de celda titulo
fnt_titulo = Font()
fnt_titulo.name = 'Arial'
fnt_titulo.bold = True
style_titulo = XFStyle()
style_titulo.font = fnt_titulo
#estilos de celda etiqueta resumen
fnt_etiqueta_resumen = Font()
fnt_etiqueta_resumen.name = 'Arial'
fnt_etiqueta_resumen.bold = True
style_etiqueta_resumen = XFStyle()
style_etiqueta_resumen.font = fnt_etiqueta_resumen
#estilos de celda datos resumen
fnt_dato_resumen = Font()
fnt_dato_resumen.name = 'Arial'
style_dato_resumen = XFStyle()
style_dato_resumen.font = fnt_dato_resumen
#estilos de celda heads
fnt_heads = Font()
fnt_heads.name = 'Arial'
fnt_heads.bold = True
borders_heads = Borders()
borders_heads.left = Borders.THIN
borders_heads.right = Borders.THIN
borders_heads.top = Borders.THIN
borders_heads.bottom = Borders.THIN
pattern_heads = Pattern()
pattern_heads.pattern = Pattern.SOLID_PATTERN
pattern_heads.pattern_fore_colour = color
style_heads = XFStyle()
style_heads.font = fnt_heads
style_heads.borders = borders_heads
style_heads.pattern = pattern_heads
#estilos de celda registros
fnt_registros = Font()
fnt_registros.name = 'Arial'
borders_registros = Borders()
borders_registros.left = Borders.THIN
borders_registros.right = Borders.THIN
borders_registros.top = Borders.THIN
borders_registros.bottom = Borders.THIN
style_registros = XFStyle()
style_registros.font = fnt_registros
style_registros.borders = borders_registros
sheet1.insert_bitmap(settings.MEDIA_ROOT + 'archivos_excel/%s' % archivo_logo, 1, 0)
#escribir el titulo
sheet1.write(10,0,titulo, style_titulo)
row = 12
col = 0
#escribir las etiquetas del resumen
for etiqueta in label_resumen:
sheet1.write(row, col, etiqueta, style_etiqueta_resumen)
row+=1
row = 12
col = 1
#escribir los datos del resumen
for dato in datos_resumen:
sheet1.write(row, col, dato, style_dato_resumen)
row+=1
row+=1
col = 0
#escribimos los encabezados
for head in heads:
sheet1.write(row,col,head,style_heads)
col+=1
row+=1
col=1
n = 1
#recorremos la lista y escribimos los datos
for fila in registros:
sheet1.write(row,0,n,style_registros)
for dato in fila:
sheet1.write(row,col,dato,style_registros)
col+=1
col=1
row+=1
n+=1
book.save(settings.MEDIA_ROOT + 'archivos_excel/%s' % nombre_archivo)
book.save(output)
output.seek(0)
response = HttpResponse(content=output.getvalue(),mimetype='application/vnd.ms-excel')
response['Content-Disposition'] = 'attachment; filename=libros_excel.xls'
return response
toprightCellStyle.font = headerFont
toprightCellStyle.borders = brdTopRight
leftCellStyle.borders = brdLeft
leftCellStyle.font = cellFont
rightCellStyle.borders = brdRight
rightCellStyle.font = cellFont
bottomCellStyle.borders = brdBottom
bottomCellStyle.font = cellFont
pat1 = Pattern()
pat1.pattern = Pattern.SOLID_PATTERN
pat1.pattern_fore_colour = 0x16
headerStyle.pattern = pat1
topleftCellStyle.pattern = pat1
toprightCellStyle.pattern = pat1
def open_data_with_excel(title, headerList, dataList):
import sys
import tempfile
_xls_fd, xls_fn = tempfile.mkstemp(suffix='.xls')
write_data_to_excel(xls_fn, title, headerList, dataList)
from PyQt4 import QtGui, QtCore
if not 'win' in sys.platform:
QtGui.QDesktopServices.openUrl(QtCore.QUrl('file://%s' % xls_fn))
else:
import pythoncom
import win32com.client
def generate_leave_record_report_file(employees, start_date, end_date, prefix=''):
queryset = get_reqs_in_period(start_date, end_date)
template = open_workbook(settings.REPORT_TEMPLATE + r'leave record report.xls', formatting_info=True)
wb = copy(template)
lrws = wb.get_sheet(0)
left_center = Alignment()
left_center.horz = Alignment.HORZ_LEFT
left_center.vert = Alignment.VERT_CENTER
center_center = Alignment()
center_center.horz = Alignment.HORZ_CENTER
center_center.vert = Alignment.VERT_CENTER
style = easyxf(
'font: name Arial;'
'borders: left thin, right thin, top thin, bottom thin;'
)
style_bg = easyxf(
'font: name Arial;'
'borders: left thin, right thin, top thin, bottom thin;'
)
style_bg.alignment = left_center
style.alignment = left_center
pattern1 = Pattern()
pattern1.pattern = Pattern.SOLID_PATTERN
pattern1.pattern_fore_colour = 0x34
pattern2 = Pattern()
pattern2.pattern = Pattern.SOLID_PATTERN
pattern2.pattern_fore_colour = 0x50
style_bg.pattern = pattern1
original_index = start_index = curr_index = 2
for employee in employees:
name_rows = queryset.filter(employee=employee).count()
if name_rows > 0:
leavetypes = list(LeaveType.objects.all())
for leavetype in leavetypes:
leaverequests = queryset.filter(employee=employee, leave_type=leavetype)
leavetype_rows = len(leaverequests)
#print 'leavetype_rows', leavetype_rows
if leavetype_rows > 0:
if style_bg.pattern == pattern1:
style_bg.pattern = pattern2
else:
style_bg.pattern = pattern1
duration = 0.0
for l in leaverequests:
periods = l.period_set.all().filter(
Q(start__range=(start_date, end_date))|
Q(end__range=(start_date, end_date)))
for p in periods:
p_start = max(p.start, start_date)
p_end = min(p.end, end_date)
lrws.write(
curr_index, 2, \
Period(leave_request=l, start=p_start, end=p_end).__unicode__(), \
style_bg)
curr_index += 1
duration += duration_days(p_start, p_end)
style_bg.alignment = center_center
lrws.write_merge(start_index, curr_index - 1, 3, 3, duration, style_bg)
style_bg.alignment = left_center
lrws.write_merge(start_index, curr_index - 1, 1, 1, leavetype.name, style_bg)
start_index = curr_index
lrws.write_merge(original_index, curr_index - 1, 0, 0, employee.display_name, style)
original_index = start_index = curr_index
filename = settings.REPORT_FILES + prefix + 'leaverecordreport-%s-%s.xls' % (start_date.strftime('%Y_%m_%d'), end_date.strftime('%Y_%m_%d'))
wb.save(filename)
return filename
if os.path.exists(filename):
os.remove(filename)
print(time.strftime("%Y-%m-%d", time.localtime(time.time()))) #打印读取到当前系统时间
wbk = Workbook(encoding='utf-8')
sheet = wbk.add_sheet('new sheet 1',
cell_overwrite_ok=True) #第二参数用于确认同一个cell单元是否可以重设值。
style = XFStyle() #赋值style为XFStyle()，初始化样式
Line_data = ('测试表') #创建一个Line_data列表，并将其值赋为测试表
for i in range(0x00, 0xff): # 设置单元格背景颜色
pattern = Pattern() # 创建一个模式
pattern.pattern = Pattern.SOLID_PATTERN # 设置其模式为实型
pattern.pattern_fore_colour = i
# 设置单元格背景颜色 0 = Black, 1 = White, 2 = Red, 3 = Green, 4 = Blue, 5 = Yellow, 6 = Magenta, the list goes on...
style.pattern = pattern # 将赋值好的模式参数导入Style
sheet.write_merge(i, i, 0, 2, Line_data,
style) #以合并单元格形式写入数据，即将数据写入以第1/2/3列合并德单元格内
for i in range(0x00, 0xff): # 设置单元格内字体样式
fnt = Font() # 创建一个文本格式，包括字体、字号和颜色样式特性
fnt.name = '微软雅黑' # 设置其字体为微软雅黑, 'SimSun' # 指定“宋体”
fnt.colour_index = i # 设置其字体颜色
fnt.bold = True
style.font = fnt #将赋值好的模式参数导入Style
sheet.write_merge(i, i, 3, 5, Line_data,
style) #以合并单元格形式写入数据，即将数据写入以第4/5/6列合并德单元格内
for i in range(0, 0x53): # 设置单元格下框线样式
from datetime import date
from xlwt import Workbook, XFStyle, Borders, Pattern, Font
fnt = Font()
fnt.name = 'Arial'
borders = Borders()
borders.left = Borders.THICK
borders.right = Borders.THICK
borders.top = Borders.THICK
borders.bottom = Borders.THICK
pattern = Pattern()
pattern.pattern = Pattern.SOLID_PATTERN
pattern.pattern_fore_colour = 0x0A
style = XFStyle()
style.num_format_str='YYYY-MM-DD'
style.font = fnt
style.borders = borders
style.pattern = pattern
book = Workbook()
sheet = book.add_sheet('A Date')
sheet.write(1,1,date(2009,3,18),style)
book.save('date.xls')
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 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 createSpreadsheet(recordset, dst_dir_path, dst_file_name):
dst_dir_path = os.path.realpath(dst_dir_path)
logger.debug("opening new workbook at %s" %
(os.path.join(dst_dir_path, dst_file_name)))
if not os.path.exists(dst_dir_path):
logger.error("Destination path %s does not exist. Error: %s" %
(dst_dir_path))
raise Exception("Destination path %s does not exist." % (dst_dir_path))
# Start some Excel magic
wb = Workbook()
ws0 = wb.add_sheet('Forecast')
ws0.set_panes_frozen(True)
ws0.set_horz_split_pos(1)
logger.debug("Workbook created with name 'Forecast'")
#0 = Black, 1 = White, 2 = Red, 3 = Green, 4 = Blue, 5 = Yellow, 6 = Magenta, 7 = Cyan, 16 = Maroon, 17 = Dark Green, 18 = Dark Blue, 19 = Dark Yellow , almost brown), 20 = Dark Magenta, 21 = Teal, 22 = Light Gray, 23 = Dark Gray
#Create the default font
defaultFont = Font()
defaultFont.name = 'Arial'
defaultFont.bold = False
defaultFont.size = 10
#create the default Pattern
defaultPattern = Pattern()
#create the default style
defaultStyle = XFStyle()
defaultStyle.font = defaultFont
#Create the failure font
failureFont = Font()
failureFont.name = 'Arial'
failureFont.bold = True
failureFont.size = 10
failureFont.colour = 'red'
#create the failure Pattern
failurePattern = Pattern()
failurePattern.pattern = Pattern.SOLID_PATTERN
failurePattern.pattern_fore_colour = 2
#Create the failure style
#failureStyle = XFStyle()
#easyxf( 'font: colour red, bold True, size 10, name Arial;')
#failureStyle.font = failureFont
#failureStyle.pattern = failurePattern
failureStyle = easyxf('font: bold 1, name Arial , height 200, color red;')
#create the failure Pattern
runningPattern = Pattern()
runningPattern.pattern = Pattern.SOLID_PATTERN
runningPattern.pattern_fore_colour = 3
#Create a running style
runningStyle = XFStyle()
runningStyle.font = defaultFont
runningStyle.pattern = runningPattern
# Grey background for the header row
headerPattern = Pattern()
headerPattern.pattern = Pattern.SOLID_PATTERN
headerPattern.pattern_fore_colour = 22
# Bold Fonts for the header row
headerFont = Font()
headerFont.name = 'Arial'
headerFont.bold = True
headerFont.size = 10
# style and write field labels
headerStyle = XFStyle()
headerStyle.font = headerFont
headerStyle.pattern = headerPattern
logger.debug("Writing data to worksheet.")
row_number = 1
col_width_dict = dict()
for i in range(8):
col_width_dict[i] = 0
for record in recordset:
currentStyle = defaultStyle
# if str(record[4]).upper() == "FAILURE":
# logger.debug( "Status %s is in FAILURE" % ( str(record[5] ) ) )
# currentStyle = failureStyle
# elif str(record[4] ).upper() == "RUNNING":
# logger.debug( "Status %s is in RUNNING" % ( str(record[5] ) ) )
# currentStyle = runningStyle
column = 0
for field in record: #i.e. for each field in the record
logger.debug("Writing data field %s to worksheet" % (str(field)))
if field:
ws0.write(
row_number, column, str(field),
currentStyle) #write excel cell from the cursor at row 1
else:
ws0.write(
row_number, column, "",
currentStyle) #write excel cell from the cursor at row 1
if len(str(field)) > col_width_dict[column]:
#only redefine the column width if we need it to be bigger
col_width_dict[column] = len(str(field))
ws0.col(column).width = len(str(field)) * 256
ws0.col(column).width = col_width_dict[column] * 256
column = column + 1 #increment the column to get the next field
row_number += 1
logger.debug("Writing header row to worksheet.")
ws0.write(0, 0, 'DAY', headerStyle)
ws0.col(0).width = 10 * 256
ws0.write(0, 1, 'JOB NAME', headerStyle)
ws0.write(0, 2, 'UC', headerStyle)
ws0.col(2).width = 17 * 256
ws0.write(0, 3, 'START TIME', headerStyle)
ws0.write(0, 4, 'PERIODICALLY', headerStyle)
ws0.col(4).width = 16 * 256
ws0.write(0, 5, 'STATUS', headerStyle)
ws0.col(5).width = 11 * 256
logger.debug("Writing excel file %s" %
(os.path.join(dst_dir_path, dst_file_name)))
wb.save(os.path.join(dst_dir_path, dst_file_name))
return 0
def transData(res,pars):
m_f=int(pars['f'])
max_refer=4
k_1=['1.显示cookie唯一数&总数','1.显示ip唯一数&总数','1.点击cookie唯一数&总数','1.点击ip唯一数&总数','1.一个cookie对应各3个不同ip数的个数','1.一个cookie对应各4个不同ip数的个数','1.一个cookie对应各大于4个不同ip数的个数']
k_2=['2.显示点击重合']
k_3=['3.点击频次高的cookie所对应的显示数','3.显示频次高的cookie所对应的点击数']#
k_4=['4.refer显示数&点击数']
data=[]
(imp_cookie_dis,imp_cookie_all)=transNULL(res[k_1[0]][0].split('\t'))
(imp_ip_dis,imp_ip_all)=transNULL(res[k_1[1]][0].split('\t'))
(clk_cookie_dis,clk_cookie_all)=transNULL(res[k_1[2]][0].split('\t'))
(clk_ip_dis,clk_ip_all)=transNULL(res[k_1[3]][0].split('\t'))
(one_cookie_3_ip_dis,one_cookie_3_ip_all)=transNULL(res[k_1[4]][0].split('\t'))
(one_cookie_4_ip_dis,one_cookie_4_ip_all)=transNULL(res[k_1[5]][0].split('\t'))
(one_cookie_b_4_ip_dis,one_cookie_b_4_ip_all)=transNULL(res[k_1[6]][0].split('\t'))
(imp_join_clk_cookie_dis,imp_join_clk_cookie_clk)=transNULL(res[k_2[0]][0].split('\t'))
(tb_imp_f_list,tb_imp_f_sum,sum_imp_str)=transDataList_imp(res[k_3[0]],m_f,imp_cookie_dis,imp_cookie_all)
(tb_clk_f_list,tb_clk_f_sum,sum_clk_str)=transDataList_clk(res[k_3[1]],m_f,imp_cookie_dis,imp_cookie_all)
(tb_referer_list,sum_referer_str)=transDataList_referer(res[k_4[0]],max_refer,imp_cookie_all,clk_cookie_all)
#排查3
data.append(transCellData('A5',imp_ip_all))#IP显示总数
data.append(transCellData('B5',imp_cookie_all))#cookie显示总数
data.append(transCellData_ext('C5',imp_ip_all,imp_cookie_all,1))#显示差值
data.append(transCellData('D5',clk_ip_all))#IP点击总数
data.append(transCellData('E5',clk_cookie_all))#cookie点击总数
data.append(transCellData_ext('F5',clk_ip_all,clk_cookie_all,1))#点击差值
#排查3 判断
conclusion_str=''
if imp_ip_all==imp_cookie_all and clk_ip_all==clk_cookie_all:
conclusion_str='正常'
else:
conclusion_str='异常'
conclusion_str=uniCode(conclusion_str)
ts=transCellData_summary('D2',conclusion_str)
##ts[1].borders.right = Borders.THICK ?
ts[1].alignment.vert = Alignment.VERT_CENTER
data.append(ts)
data.append(transCellData('A10',imp_ip_dis))#IP唯一显示数
data.append(transCellData('B10',imp_cookie_dis))#cookie唯一显示数
data.append(transCellData_ext('C10',imp_cookie_dis,imp_ip_dis,1))#显示差异比例
data.append(transCellData('D10',clk_ip_dis))#IP唯一点击数
data.append(transCellData('E10',clk_cookie_dis))#cookie唯一点击数
data.append(transCellData_ext('F10',clk_cookie_dis,clk_ip_dis,1))#点击差异比例
ts=transCellData_ext('A12',one_cookie_3_ip_dis,0,imp_cookie_dis)
ts[1].num_format_str='0.00%'
data.append(ts)#一个cookie对应3个ip的cookie占比
ts=transCellData_ext('B12',one_cookie_3_ip_all,0,imp_cookie_all)
ts[1].num_format_str='0.00%'
data.append(ts)#其显示占比
ts=transCellData_ext('C12',one_cookie_4_ip_dis,0,imp_cookie_dis)
ts[1].num_format_str='0.00%'
data.append(ts)#一个cookie对应4个ip的cookie占比
ts=transCellData_ext('D12',one_cookie_4_ip_all,0,imp_cookie_all)
ts[1].num_format_str='0.00%'
data.append(ts)#其显示占比
ts=transCellData_ext('E12',one_cookie_b_4_ip_dis,0,imp_cookie_dis)
ts[1].num_format_str='0.00%'
data.append(ts)#一个cookie对应4个ip以上的cookie占比
ts=transCellData_ext('F12',one_cookie_b_4_ip_all,0,imp_cookie_all)
ts[1].num_format_str='0.00%'
data.append(ts)#其显示占比
data.append(transCellData('A19',clk_cookie_all))#点击总数
data.append(transCellData('B19',imp_cookie_all))#显示cookie总数(唯一)
data.append(transCellData('C19',clk_cookie_all))#点击cookie总数(唯一)
data.append(transCellData_ext('D19',clk_cookie_all,imp_cookie_all,1 ))#cookie重合数1
data.append(transCellData('E19',imp_join_clk_cookie_clk))#cookie重合数2
data.append(transCellData_ext('F19',clk_cookie_all,imp_join_clk_cookie_clk,1)) #无显示cookie
ts=transCellData_ext('G19',clk_cookie_dis,imp_join_clk_cookie_dis,clk_cookie_dis)
ts[1].num_format_str='0.00%'
data.append(ts) #无显示cookie占比
ts=transCellData_ext('H19',clk_cookie_all,imp_join_clk_cookie_clk,clk_cookie_all)
ts[1].num_format_str='0.00%'
data.append(ts) #点击数占总体
intindex=27
for tt in tb_imp_f_list:
ts=transCellData('A%d'%intindex,tt[0])
if tt[0]<0:
ts[0][2]= len(tb_imp_f_list)-1
ts[1].num_format_str='0+'
data.append(ts)
data.append(transCellData('B%d'%intindex,tt[1]))
ts=transCellData_ext('C%d'%intindex,tt[2],0,1)
ts[1].num_format_str='0.00%'
data.append(ts)
data.append(transCellData('D%d'%intindex,tt[3]))
ts=transCellData_ext('E%d'%intindex,tt[4],0,1)
ts[1].num_format_str='0.00%'
data.append(ts)
data.append(transCellData('F%d'%intindex,tt[5]))
data.append(transCellData('G%d'%intindex,tt[6]))
intindex+=1
intindex=37
pattern = Pattern()
pattern.pattern = Style.pattern_map['solid']
###pattern.pattern_back_colour = 0xBFBFBF
pattern.pattern_fore_colour = 0x37
ts=transCellData('B%d'%intindex,tb_imp_f_sum[0][1])
ts[1].pattern=pattern
data.append(ts)
ts=transCellData_ext('C%d'%intindex,tb_imp_f_sum[0][2],0,1)
ts[1].num_format_str='0.00%'
ts[1].pattern=pattern
data.append(ts)
ts=transCellData('D%d'%intindex,tb_imp_f_sum[0][3])
ts[1].pattern=pattern
data.append(ts)
ts=transCellData_ext('E%d'%intindex,tb_imp_f_sum[0][4],0,1)
ts[1].num_format_str='0.00%'
ts[1].pattern=pattern
data.append(ts)
ts=transCellData('F%d'%intindex,tb_imp_f_sum[0][5])
ts[1].pattern=pattern
data.append(ts)
ts=transCellData('G%d'%intindex,tb_imp_f_sum[0][6])
ts[1].pattern=pattern
data.append(ts)
intindex=39
for tt in tb_clk_f_list:
ts=transCellData('A%d'%intindex,tt[0])
if tt[0]<0:
ts[0][2]= len(tb_imp_f_list)-1
ts[1].num_format_str='0+'
data.append(ts)
data.append(transCellData('B%d'%intindex,tt[1]))
ts=transCellData_ext('C%d'%intindex,tt[2],0,1)
ts[1].num_format_str='0.00%'
data.append(ts)
data.append(transCellData('D%d'%intindex,tt[3]))
ts=transCellData_ext('E%d'%intindex,tt[4],0,1)
ts[1].num_format_str='0.00%'
data.append(ts)
data.append(transCellData('F%d'%intindex,tt[5]))
data.append(transCellData('G%d'%intindex,tt[6]))
intindex+=1
intindex=49
ts=transCellData('B%d'%intindex,tb_clk_f_sum[0][1])
ts[1].pattern=pattern
data.append(ts)
ts=transCellData_ext('C%d'%intindex,tb_clk_f_sum[0][2],0,1)
ts[1].num_format_str='0.00%'
ts[1].pattern=pattern
data.append(ts)
ts=transCellData('D%d'%intindex,tb_clk_f_sum[0][3])
ts[1].pattern=pattern
data.append(ts)
ts=transCellData_ext('E%d'%intindex,tb_clk_f_sum[0][4],0,1)
ts[1].num_format_str='0.00%'
ts[1].pattern=pattern
data.append(ts)
ts=transCellData('F%d'%intindex,tb_clk_f_sum[0][5])
ts[1].pattern=pattern
data.append(ts)
ts=transCellData('G%d'%intindex,tb_clk_f_sum[0][6])
ts[1].pattern=pattern
data.append(ts)
intindex=53
for tt in tb_referer_list:
data.append(transCellData_str('B%d'%intindex,tt[0]))
ts=transCellData_ext('E%d'%intindex,tt[1],0,1)
ts[1].num_format_str='0.00%'
data.append(ts)
ts=transCellData_ext('G%d'%intindex,tt[2],0,1)
ts[1].num_format_str='0.00%'
data.append(ts)
ts=transCellData_ext('I%d'%intindex,tt[3],0,1)
ts[1].num_format_str='0.00%'
data.append(ts)
intindex+=1
##################summary####################
test_str='有%d个url产生了%s的显示及%s的点击，点击率约%s'%(max_refer,sum_referer_str[0],sum_referer_str[1],sum_referer_str[2])
test_str=uniCode(test_str)
ts=transCellData_summary('D52',test_str)
data.append(ts)
test_str='%d频次以上Cookie占比%s；\n%d频次以上显示数占比%s'%(m_f,sum_imp_str[0],m_f,sum_imp_str[1])
test_str=uniCode(test_str)
ts=transCellData_summary('D24',test_str)
ts[1].borders.right = Borders.NO_LINE
ts[1].borders.bottom = Borders.NO_LINE
ts[1].alignment.wrap = 1
data.append(ts)
test_str='点击%d次及以上的Cookie占比%s；\n点击%d次及以上的点击数占比%s'%(m_f,sum_clk_str[0],m_f,sum_clk_str[1])
test_str=uniCode(test_str)
ts=transCellData_summary('D25',test_str)
ts[1].borders.right = Borders.NO_LINE
ts[1].borders.top = Borders.NO_LINE
ts[1].alignment.wrap = 1
data.append(ts)
if (float(clk_cookie_dis)==0):
test_str='没有点击Cookie'
else:
if clk_cookie_dis!=0:
val1_=100*(float(clk_cookie_dis)-float(imp_join_clk_cookie_dis))/float(clk_cookie_dis)
else:
val1_=0
if clk_cookie_all!=0:
val2_=100*(float(clk_cookie_all)-float(imp_join_clk_cookie_clk))/float(clk_cookie_all)
else:
val2_=0
test_str='%s的点击Cookie没有显示；点击数占总比%s'%('%0.2f'%val1_+'%','%0.2f'%val2_+'%')
test_str=uniCode(test_str)
ts=transCellData_summary('D17',test_str)
##ts[1].borders.right = Borders.THICK ?
ts[1].alignment.vert = Alignment.VERT_CENTER
data.append(ts)
val1_=100*sumPercent([one_cookie_3_ip_dis ,one_cookie_4_ip_dis ,one_cookie_b_4_ip_dis ],imp_cookie_dis)
val2_=100*sumPercent([one_cookie_3_ip_all, one_cookie_4_ip_all, one_cookie_b_4_ip_all], imp_cookie_all)
test_str='1个Cookie对应2个以上IP的Cookie占比%s;\n显示占总体%s'%('%0.2f'%val1_+'%','%0.2f'%val2_+'%')
test_str=uniCode(test_str)
ts=transCellData_summary('D8',test_str)
ts[1].alignment.wrap = 1
data.append(ts)
return data
cc = '0%s' % (stime[1], )
else:
cc = stime[1]
str = "%s-%s" % (cc, dd)
j = j + 1
for date_index in range(TIME_SPAN):
val = VVV1.cell(MAX_TASK_NUM, date_index + 2).value
if str == val:
style = XFStyle()
pattern = Pattern()
pattern.pattern = Pattern.SOLID_PATTERN
if V1.cell(row_index, 12).value == 'Y': # 已完成
if V1.cell(row_index, 17).value == 'Y': # 已超期
pattern.pattern_fore_colour = 0x0B
style.pattern = pattern
VV1.write(j + 3, date_index + 2, '√', style)
else:
pattern.pattern_fore_colour = 0x0F
style.pattern = pattern
VV1.write(j + 3, date_index + 2, '√', style)
else: # 没完成
if V1.cell(row_index, 17).value == 'Y': # 已超期
pattern.pattern_fore_colour = 0x0A
style.pattern = pattern
VV1.write(j + 3, date_index + 2, '√', style)
else:
pattern.pattern_fore_colour = 0x0FFB
VV1.write(j + 3, date_index + 2, '√')