def processAllDocuments(self, table_name):
last_week_no = self.latest_week_no(table_name)
workbook = xlsxwriter.Workbook("/var/www/html/Lexis_Link_Monitoring/output/" + table_name + ".xlsx")
# workbook = xlsxwriter.Workbook(table_name+'.xlsx')
worksheet = workbook.add_worksheet()
worksheet.set_column(1, 1, 30)
worksheet.set_column(2, 2, 30)
worksheet.set_column(3, 3, 30)
worksheet.set_column(4, 4, 20)
worksheet.set_column(5, 5, 30)
worksheet.set_column(6, 6, 80)
cursor, conn = self.connection()
stmt = (
"select id,external_link_label,topic_tree_location,external_link_address,response_category,ping_date_time,redirect_url from %s where week_no = %s order by id asc"
% (table_name, last_week_no)
)
cursor.execute(stmt)
docIdLst = cursor.fetchall()
doc_lst = []
header_lst = [
"Sl. No",
"External Link Label",
"Topic Tree Location",
"External Link Address",
"Response Category",
"Ping Date Time",
"New URL (Redirect - Other only)",
]
row = 0
col = 0
ch_obj = html_entity_to_single_char.html_entity_to_single_char()
# bold = workbook.add_format({'bold': True})
format = workbook.add_format()
format.set_bg_color("#4e9bfa")
format.set_bold()
for header in header_lst:
worksheet.write(row, col, header, format)
col += 1
row += 1
for doc in docIdLst:
col = 0
for i in range(7):
value = ch_obj.convert(str(doc[i]))
worksheet.write_string(row, col, value)
col += 1
row += 1
workbook.close()
conn.close()
# outDict = {'data':doc_lst}
ret = "Lexis_Link_Monitoring/output/" + table_name + ".xlsx"
return ret
def processAllDocuments(self, table_name):
last_week_no = self.latest_week_no(table_name)
workbook = xlsxwriter.Workbook(
'/var/www/html/Lexis_Link_Monitoring/output/' + table_name +
'.xlsx')
#workbook = xlsxwriter.Workbook(table_name+'.xlsx')
worksheet = workbook.add_worksheet()
worksheet.set_column(1, 1, 30)
worksheet.set_column(2, 2, 30)
worksheet.set_column(3, 3, 30)
worksheet.set_column(4, 4, 20)
worksheet.set_column(5, 5, 30)
worksheet.set_column(6, 6, 80)
cursor, conn = self.connection()
stmt = 'select id,external_link_label,topic_tree_location,external_link_address,response_category,ping_date_time,redirect_url from %s where week_no = %s order by id asc' % (
table_name, last_week_no)
cursor.execute(stmt)
docIdLst = cursor.fetchall()
doc_lst = []
header_lst = [
'Sl. No', 'External Link Label', 'Topic Tree Location',
'External Link Address', 'Response Category', 'Ping Date Time',
'New URL (Redirect - Other only)'
]
row = 0
col = 0
ch_obj = html_entity_to_single_char.html_entity_to_single_char()
#bold = workbook.add_format({'bold': True})
format = workbook.add_format()
format.set_bg_color('#4e9bfa')
format.set_bold()
for header in header_lst:
worksheet.write(row, col, header, format)
col += 1
row += 1
for doc in docIdLst:
col = 0
for i in range(7):
value = ch_obj.convert(str(doc[i]))
worksheet.write_string(row, col, value)
col += 1
row += 1
workbook.close()
conn.close()
#outDict = {'data':doc_lst}
ret = 'Lexis_Link_Monitoring/output/' + table_name + '.xlsx'
return ret
def processAllDocuments(self):
workbook = xlsxwriter.Workbook('/var/www/html/Lexis_Link_Monitoring/output/result.xlsx')
table_lst = ['banking_finance','business_law','california_business_entity_selection_formation','combined_california_general_business_law','commercial_bankrtupcy','corporate_counsel','intellectual_property','labor_employment','m_a','ny_business_commercial','real_estate','securities_capital_markets','texas_business_commercial']
sheet_name_arr = ['B&F','Business Law','CA Bus Entity','Combined Bus Entity Selection','Bankruptcy','Corp Counsel','IP','L&E','M&A','NY B&C','Real Estate','S&CM','Texas B&C']
j = 0
for ind_table in table_lst:
table_name = ind_table
sheet_name = sheet_name_arr[j]
worksheet = workbook.add_worksheet(sheet_name)
worksheet.set_column(1, 1, 30)
worksheet.set_column(2, 2, 30)
worksheet.set_column(3, 3, 30)
worksheet.set_column(4, 4, 20)
worksheet.set_column(5, 5, 30)
worksheet.set_column(6, 6, 80)
j += 1
cursor, conn = self.connection()
stmt = 'select id,external_link_label,topic_tree_location,external_link_address,response_category,ping_date_time,redirect_url from %s order by id asc' %(table_name)
cursor.execute(stmt)
docIdLst = cursor.fetchall()
doc_lst = []
header_lst = ['Sl. No','External Link Label','Topic Tree Location','External Link Address','Response Category','Ping Date Time','New URL (Redirect - Other only)']
row = 0;
col = 0;
ch_obj = html_entity_to_single_char.html_entity_to_single_char()
#bold = workbook.add_format({'bold': True})
format = workbook.add_format()
format.set_bg_color('#4e9bfa')
format.set_bold()
for header in header_lst:
worksheet.write(row, col,header,format)
col += 1
row += 1
for doc in docIdLst:
col = 0
for i in range(7):
value = ch_obj.convert(str(doc[i]))
worksheet.write_string(row, col,value)
col += 1
row += 1
workbook.close()
conn.close()
ret = 'Lexis_Link_Monitoring/output/result.xlsx'
return ret
def processAllDocuments(self,table_name):
sheet_name_arr = {'banking_finance':'B&F','business_law':'Business Law','california_business_entity_selection_formation':'CA Bus Entity','combined_california_general_business_law':'Combined Bus Entity Selection','commercial_bankrtupcy':'Bankruptcy','corporate_counsel':'Corp Counsel','intellectual_property':'IP','labor_employment':'L&E','m_a':'M&A','ny_business_commercial':'NY B&C','real_estate':'Real Estate','securities_capital_markets':'S&CM','texas_business_commercial':'Texas B&C','LN_UK_Practice_Area':'LN UK Practice Area'}
workbook = xlsxwriter.Workbook('/var/www/html/Lexis_Link_Monitoring/output/'+table_name+'.xlsx')
#workbook = xlsxwriter.Workbook(table_name+'.xlsx')
worksheet = workbook.add_worksheet(sheet_name_arr[table_name])
worksheet.set_column(1, 1, 30)
worksheet.set_column(2, 2, 30)
worksheet.set_column(3, 3, 30)
worksheet.set_column(4, 4, 20)
worksheet.set_column(5, 5, 30)
worksheet.set_column(6, 6, 80)
cursor, conn = self.connection()
stmt = 'select excel_id,title,external_link_address,practice_area,content_type,location,update_date,page_type,response_category,ping_date_time,redirect_url from %s order by id asc' %(table_name)
cursor.execute(stmt)
docIdLst = cursor.fetchall()
doc_lst = []
header_lst = ['Id','Title','Address','Practice Area','Content Type','Location','Update Date','Page Type','Response Category','Ping Date Time','New URL (Redirect - Other only)']
row = 0;
col = 0;
ch_obj = html_entity_to_single_char.html_entity_to_single_char()
#bold = workbook.add_format({'bold': True})
format = workbook.add_format()
format.set_bg_color('#4e9bfa')
format.set_bold()
for header in header_lst:
worksheet.write(row, col,header,format)
col += 1
row += 1
format2 = workbook.add_format()
format2.set_bottom_color('')
format2.set_top_color('')
format2.set_left_color('')
format2.set_right_color('')
for doc in docIdLst:
col = 0
for i in range(11):
value = ch_obj.convert(str(doc[i]))
worksheet.write_string(row, col,value,format2)
col += 1
row += 1
workbook.close()
conn.close()
#outDict = {'data':doc_lst}
ret = 'Lexis_Link_Monitoring/output/'+table_name+'.xlsx'
return ret
def prepare_doc_excel(self,doc_lst):
workbook = xlsxwriter.Workbook('/var/www/html/Lexis_Link_Monitoring/output/result.xlsx')
table_lst = doc_lst
sheet_name_arr = {'banking_finance':'B&F','business_law':'Business Law','california_business_entity_selection_formation':'CA Bus Entity','combined_california_general_business_law':'Combined Bus Entity Selection','commercial_bankrtupcy':'Bankruptcy','corporate_counsel':'Corp Counsel','intellectual_property':'IP','labor_employment':'L&E','m_a':'M&A','ny_business_commercial':'NY B&C','real_estate':'Real Estate','securities_capital_markets':'S&CM','texas_business_commercial':'Texas B&C'}
for ind_table in table_lst:
table_name = ind_table
last_week_no = self.latest_week_no(table_name)
#sheet_name = sheet_name_arr[j]
worksheet = workbook.add_worksheet(sheet_name_arr[table_name])
worksheet.set_column(1, 1, 30)
worksheet.set_column(2, 2, 30)
worksheet.set_column(3, 3, 30)
worksheet.set_column(4, 4, 20)
worksheet.set_column(5, 5, 30)
worksheet.set_column(6, 6, 80)
cursor, conn = self.connection()
stmt = 'select id,external_link_label,topic_tree_location,external_link_address,response_category,ping_date_time,redirect_url from %s where week_no = %s order by id asc' %(table_name,last_week_no)
cursor.execute(stmt)
docIdLst = cursor.fetchall()
doc_lst = []
header_lst = ['Sl. No','External Link Label','Topic Tree Location','External Link Address','Response Category','Ping Date Time','New URL (Redirect - Other only)']
row = 0;
col = 0;
ch_obj = html_entity_to_single_char.html_entity_to_single_char()
#bold = workbook.add_format({'bold': True})
format = workbook.add_format()
format.set_bg_color('#4e9bfa')
format.set_bold()
for header in header_lst:
worksheet.write(row, col,header,format)
col += 1
row += 1
for doc in docIdLst:
col = 0
for i in range(7):
value = ch_obj.convert(str(doc[i]))
worksheet.write_string(row, col,value)
col += 1
row += 1
workbook.close()
conn.close()
return True
def processAllDocuments(self, table_name):
sheet_name_arr = {
'banking_finance': 'B&F',
'business_law': 'Business Law',
'california_business_entity_selection_formation': 'CA Bus Entity',
'combined_california_general_business_law':
'Combined Bus Entity Selection',
'commercial_bankrtupcy': 'Bankruptcy',
'corporate_counsel': 'Corp Counsel',
'intellectual_property': 'IP',
'labor_employment': 'L&E',
'm_a': 'M&A',
'ny_business_commercial': 'NY B&C',
'real_estate': 'Real Estate',
'securities_capital_markets': 'S&CM',
'texas_business_commercial': 'Texas B&C',
'LN_UK_Practice_Area': 'LN UK Practice Area'
}
workbook = xlsxwriter.Workbook(
'/var/www/html/Lexis_Link_Monitoring/output/' + table_name +
'.xlsx')
#workbook = xlsxwriter.Workbook(table_name+'.xlsx')
worksheet = workbook.add_worksheet(sheet_name_arr[table_name])
worksheet.set_column(1, 1, 30)
worksheet.set_column(2, 2, 30)
worksheet.set_column(3, 3, 30)
worksheet.set_column(4, 4, 20)
worksheet.set_column(5, 5, 30)
worksheet.set_column(6, 6, 80)
cursor, conn = self.connection()
stmt = 'select excel_id,title,external_link_address,practice_area,content_type,location,update_date,page_type,response_category,ping_date_time,redirect_url from %s order by id asc' % (
table_name)
cursor.execute(stmt)
docIdLst = cursor.fetchall()
doc_lst = []
header_lst = [
'Id', 'Title', 'Address', 'Practice Area', 'Content Type',
'Location', 'Update Date', 'Page Type', 'Response Category',
'Ping Date Time', 'New URL (Redirect - Other only)'
]
row = 0
col = 0
ch_obj = html_entity_to_single_char.html_entity_to_single_char()
#bold = workbook.add_format({'bold': True})
format = workbook.add_format()
format.set_bg_color('#4e9bfa')
format.set_bold()
for header in header_lst:
worksheet.write(row, col, header, format)
col += 1
row += 1
format2 = workbook.add_format()
format2.set_bottom_color('')
format2.set_top_color('')
format2.set_left_color('')
format2.set_right_color('')
for doc in docIdLst:
col = 0
for i in range(11):
value = ch_obj.convert(str(doc[i]))
worksheet.write_string(row, col, value, format2)
col += 1
row += 1
workbook.close()
conn.close()
#outDict = {'data':doc_lst}
ret = 'Lexis_Link_Monitoring/output/' + table_name + '.xlsx'
return ret
from openpyxl import load_workbook
import datetime
from openpyxl import Workbook
from openpyxl.comments import Comment
from openpyxl.styles import PatternFill, Border, Side, Alignment, Protection, Font, Color
import config
import re
import copy
import os
import modules.template_mgmt.model_api as ma
ma_obj = ma.model_api()
import html_entity_to_single_char as html_entity
html_entity_obj = html_entity.html_entity_to_single_char()
class excel:
def __init__(self):
pass
def get_clean_val(self, value_org):
value = str(value_org)
value = ''.join(value.split('%'))
value = ''.join(value.split(','))
value = ''.join(value.split('$'))
try:
value = int(value)
return value
except:
try:
value = float(value)
return value
def processAllDocuments(self):
workbook = xlsxwriter.Workbook('/var/www/html/Lexis_Link_Monitoring/output/report.xlsx')
#workbook = xlsxwriter.Workbook(table_name+'.xlsx')
worksheet = workbook.add_worksheet()
worksheet.set_column(1, 1, 30)
worksheet.set_column(2, 2, 10)
worksheet.set_column(3, 3, 10)
worksheet.set_column(4, 4, 10)
worksheet.set_column(5, 5, 10)
worksheet.set_column(6, 6, 10)
worksheet.set_column(7, 7, 10)
worksheet.set_column(8, 8, 10)
worksheet.set_column(9, 9, 10)
worksheet.set_column(10, 10, 10)
worksheet.set_column(11, 11, 10)
worksheet.set_column(12, 12, 10)
cursor, conn = self.connection()
table_lst = ['banking_finance','business_law','california_business_entity_selection_formation','combined_california_general_business_law','commercial_bankrtupcy','corporate_counsel','intellectual_property','labor_employment','m_a','ny_business_commercial','real_estate','securities_capital_markets','texas_business_commercial']
doc_lst = []
i = 1
for ind_table in table_lst:
last_week_no = self.latest_week_no(ind_table)
stmt1 = 'select count(*) from %s where week_no = %s' %(ind_table,last_week_no)
stmt2 = 'select count(*) from %s where response_category like "Success" and week_no = %s' %(ind_table,last_week_no)
stmt3 = 'select count(*) from %s where response_category like "Fail" and week_no = %s' %(ind_table,last_week_no)
stmt4 = 'select count(*) from %s where response_category like "Redirect (other)" and week_no = %s' %(ind_table,last_week_no)
stmt5 = 'select count(*) from %s where response_category like "Login (Lexis)" and week_no = %s' %(ind_table,last_week_no)
cursor.execute(stmt1)
docIdLst = cursor.fetchall()
for doc in docIdLst:
main_count = int(doc[0])
cursor.execute(stmt2)
docIdLst = cursor.fetchall()
for doc in docIdLst:
success_count = int(doc[0])
cursor.execute(stmt3)
docIdLst = cursor.fetchall()
for doc in docIdLst:
fail_count = int(doc[0])
cursor.execute(stmt4)
docIdLst = cursor.fetchall()
for doc in docIdLst:
redirect_count = int(doc[0])
cursor.execute(stmt5)
docIdLst = cursor.fetchall()
for doc in docIdLst:
redirect_login_lexis_count = int(doc[0])
success_per = float(success_count)/main_count * 100
fail_per = float(fail_count)/main_count * 100
redirect_per = float(redirect_count)/main_count * 100
login_lexis_per = float(redirect_login_lexis_count)/main_count * 100
doc_lst.append((i,ind_table,main_count,success_count,fail_count,redirect_count,0,redirect_login_lexis_count,str(int(math.ceil(success_per)))+'%',str(int(math.ceil(fail_per)))+'%',str(int(math.ceil(redirect_per)))+'%','0%',str(int(math.ceil(login_lexis_per)))+'%'))
i += 1
#conn.close()
header_lst = ['Sl. No','Practice Area','No. of links','Success','Fail','Redirect (other)','Redirect (login)','Login (Lexis)','Success %','Fail %','Redirect (other) %','Redirect (login) %','Login (Lexis) %']
row = 0;
col = 0;
ch_obj = html_entity_to_single_char.html_entity_to_single_char()
#bold = workbook.add_format({'bold': True})
format = workbook.add_format()
format.set_bg_color('#4e9bfa')
format.set_bold()
for header in header_lst:
worksheet.write(row, col,header,format)
col += 1
row += 1
for ind_doc in doc_lst:
col = 0
for i in range(13):
value = ind_doc[i]
worksheet.write(row, col,value)
col += 1
row += 1
merge_format = workbook.add_format({
'bold': True,
'border': 6,
'align': 'center',
'valign': 'vcenter',
'fg_color': '#D7E4BC',
})
worksheet.merge_range('A17:O17', '6 week trend as of 18-May-2015', merge_format)
merge_format2 = workbook.add_format({
'bold': True,
'border': 6,
'align': 'center',
'valign': 'vcenter',
'fg_color': '#D7E4BC',
})
worksheet.merge_range('C19:H19', 'Number of Links', merge_format2)
merge_format3 = workbook.add_format({
'bold': True,
'border': 6,
'align': 'center',
'valign': 'vcenter',
'fg_color': '#D7E4BC',
})
worksheet.merge_range('J19:O19', 'Failed Link % (shaded red if > 2.0%)', merge_format3)
header_lst2 = ['Sl. No','Practice Area','18-may-15','25-may-15','1-june-15','8-june-15','15-june-15','22-june-15','','18-may-15','25-may-15','1-june-15','8-june-15','15-june-15','22-june-15']
row = 19
col = 0
ch_obj = html_entity_to_single_char.html_entity_to_single_char()
format = workbook.add_format()
format.set_bg_color('#4e9bfa')
format.set_bold()
for header2 in header_lst2:
worksheet.write(row, col,header2,format)
col += 1
row += 1
table_lst = ['banking_finance','business_law','california_business_entity_selection_formation','combined_california_general_business_law','commercial_bankrtupcy','corporate_counsel','intellectual_property','labor_employment','m_a','ny_business_commercial','real_estate','securities_capital_markets','texas_business_commercial']
doc_lst = []
i = 1
for ind_table in table_lst:
ind_doc_lst = [i,ind_table,'','','','','','','','','','','','','']
week_lst = self.get_week_nos(ind_table)
pos = 2
for j in week_lst:
stmt1 = 'select count(*) from %s where week_no = %s' %(ind_table,j)
stmt2 = 'select count(*) from %s where response_category like "Fail" and week_no = %s' %(ind_table,j)
cursor.execute(stmt1)
docIdLst = cursor.fetchall()
for doc in docIdLst:
main_count = int(doc[0])
cursor.execute(stmt2)
docIdLst = cursor.fetchall()
for doc in docIdLst:
fail_count = int(doc[0])
fail_per = 0
if main_count:
fail_per = float(fail_count)/main_count * 100
ind_doc_lst[pos] = main_count
ind_doc_lst[int(pos)+7] = int(math.ceil(fail_per))
pos += 1
doc_lst.append(ind_doc_lst)
i += 1
conn.close()
format3 = workbook.add_format()
format3.set_bg_color('red')
for ind_doc in doc_lst:
col = 0
for i in range(15):
if i < 9:
value = ind_doc[i]
worksheet.write(row, col,value)
else:
if str(ind_doc[i]):
value = str(ind_doc[i])+'%'
if ind_doc[i]< 3:
worksheet.write(row, col,value)
else:
worksheet.write(row, col,value,format3)
else:
value = str(ind_doc[i])
worksheet.write(row, col,value)
col += 1
row += 1
workbook.close()
ret = 'Lexis_Link_Monitoring/output/report.xlsx'
return ret
sObj = Slt_normdata.Slt_normdata()
from db.webdatastore import webdatastore
lmdb_obj = webdatastore()
import slt_map_sectiontype as slt_map_sectiontype
Mobj = slt_map_sectiontype.Map_sectiontype()
import font_id_for_text_final as Font_id_text
StlyObj = Font_id_text.Font_id_text()
import xml_to_bbox_normid as cell_id_bbox
Xml_Cell_Obj = cell_id_bbox.cell_id_bbox()
output_path = '/var/www/html/fundamentals_intf/output/'
import sqLite.sqLiteApi as sqLiteApi
qObj = sqLiteApi.sqLiteApi()
sys.path.append('/root/muthu_translate/')
import socket_client_utils2 as socket_client_utils2
import html_entity_to_single_char as html_entity_to_single_char
cnobj = html_entity_to_single_char.html_entity_to_single_char()
#import goslate
#gs = goslate.Goslate()
from mtranslate import translate
def index_all_table(company_id):
from getCompanyName_machineId import getCN_MID
getCompanyName_machineId = getCN_MID()
company_name, machine_id = getCompanyName_machineId[company_id]
model_number = '1'
project_id, url_id = company_id.split('_')
all_doc_table_to_process = []
norm_res_list = sObj.slt_normresids(project_id, url_id)
db_file = os.path.join('/mnt/eMB_db/', company_name, model_number, 'company_report.db')
def processAllDocuments(self):
workbook = xlsxwriter.Workbook(
'/var/www/html/Lexis_Link_Monitoring/output/report.xlsx')
#workbook = xlsxwriter.Workbook(table_name+'.xlsx')
worksheet = workbook.add_worksheet()
worksheet.set_column(1, 1, 30)
worksheet.set_column(2, 2, 10)
worksheet.set_column(3, 3, 10)
worksheet.set_column(4, 4, 10)
worksheet.set_column(5, 5, 10)
worksheet.set_column(6, 6, 10)
worksheet.set_column(7, 7, 10)
worksheet.set_column(8, 8, 10)
worksheet.set_column(9, 9, 10)
worksheet.set_column(10, 10, 10)
worksheet.set_column(11, 11, 10)
worksheet.set_column(12, 12, 10)
cursor, conn = self.connection()
table_lst = [
'banking_finance', 'business_law',
'california_business_entity_selection_formation',
'combined_california_general_business_law',
'commercial_bankrtupcy', 'corporate_counsel',
'intellectual_property', 'labor_employment', 'm_a',
'ny_business_commercial', 'real_estate',
'securities_capital_markets', 'texas_business_commercial'
]
doc_lst = []
i = 1
for ind_table in table_lst:
last_week_no = self.latest_week_no(ind_table)
stmt1 = 'select count(*) from %s where week_no = %s' % (
ind_table, last_week_no)
stmt2 = 'select count(*) from %s where response_category like "Success" and week_no = %s' % (
ind_table, last_week_no)
stmt3 = 'select count(*) from %s where response_category like "Fail" and week_no = %s' % (
ind_table, last_week_no)
stmt4 = 'select count(*) from %s where response_category like "Redirect (other)" and week_no = %s' % (
ind_table, last_week_no)
stmt5 = 'select count(*) from %s where response_category like "Login (Lexis)" and week_no = %s' % (
ind_table, last_week_no)
cursor.execute(stmt1)
docIdLst = cursor.fetchall()
for doc in docIdLst:
main_count = int(doc[0])
cursor.execute(stmt2)
docIdLst = cursor.fetchall()
for doc in docIdLst:
success_count = int(doc[0])
cursor.execute(stmt3)
docIdLst = cursor.fetchall()
for doc in docIdLst:
fail_count = int(doc[0])
cursor.execute(stmt4)
docIdLst = cursor.fetchall()
for doc in docIdLst:
redirect_count = int(doc[0])
cursor.execute(stmt5)
docIdLst = cursor.fetchall()
for doc in docIdLst:
redirect_login_lexis_count = int(doc[0])
success_per = float(success_count) / main_count * 100
fail_per = float(fail_count) / main_count * 100
redirect_per = float(redirect_count) / main_count * 100
login_lexis_per = float(
redirect_login_lexis_count) / main_count * 100
doc_lst.append(
(i, ind_table, main_count, success_count, fail_count,
redirect_count, 0, redirect_login_lexis_count,
str(int(math.ceil(success_per))) + '%',
str(int(math.ceil(fail_per))) + '%',
str(int(math.ceil(redirect_per))) + '%', '0%',
str(int(math.ceil(login_lexis_per))) + '%'))
i += 1
#conn.close()
header_lst = [
'Sl. No', 'Practice Area', 'No. of links', 'Success', 'Fail',
'Redirect (other)', 'Redirect (login)', 'Login (Lexis)',
'Success %', 'Fail %', 'Redirect (other) %', 'Redirect (login) %',
'Login (Lexis) %'
]
row = 0
col = 0
ch_obj = html_entity_to_single_char.html_entity_to_single_char()
#bold = workbook.add_format({'bold': True})
format = workbook.add_format()
format.set_bg_color('#4e9bfa')
format.set_bold()
for header in header_lst:
worksheet.write(row, col, header, format)
col += 1
row += 1
for ind_doc in doc_lst:
col = 0
for i in range(13):
value = ind_doc[i]
worksheet.write(row, col, value)
col += 1
row += 1
merge_format = workbook.add_format({
'bold': True,
'border': 6,
'align': 'center',
'valign': 'vcenter',
'fg_color': '#D7E4BC',
})
worksheet.merge_range('A17:O17', '6 week trend as of 18-May-2015',
merge_format)
merge_format2 = workbook.add_format({
'bold': True,
'border': 6,
'align': 'center',
'valign': 'vcenter',
'fg_color': '#D7E4BC',
})
worksheet.merge_range('C19:H19', 'Number of Links', merge_format2)
merge_format3 = workbook.add_format({
'bold': True,
'border': 6,
'align': 'center',
'valign': 'vcenter',
'fg_color': '#D7E4BC',
})
worksheet.merge_range('J19:O19',
'Failed Link % (shaded red if > 2.0%)',
merge_format3)
header_lst2 = [
'Sl. No', 'Practice Area', '18-may-15', '25-may-15', '1-june-15',
'8-june-15', '15-june-15', '22-june-15', '', '18-may-15',
'25-may-15', '1-june-15', '8-june-15', '15-june-15', '22-june-15'
]
row = 19
col = 0
ch_obj = html_entity_to_single_char.html_entity_to_single_char()
format = workbook.add_format()
format.set_bg_color('#4e9bfa')
format.set_bold()
for header2 in header_lst2:
worksheet.write(row, col, header2, format)
col += 1
row += 1
table_lst = [
'banking_finance', 'business_law',
'california_business_entity_selection_formation',
'combined_california_general_business_law',
'commercial_bankrtupcy', 'corporate_counsel',
'intellectual_property', 'labor_employment', 'm_a',
'ny_business_commercial', 'real_estate',
'securities_capital_markets', 'texas_business_commercial'
]
doc_lst = []
i = 1
for ind_table in table_lst:
ind_doc_lst = [
i, ind_table, '', '', '', '', '', '', '', '', '', '', '', '',
''
]
week_lst = self.get_week_nos(ind_table)
pos = 2
for j in week_lst:
stmt1 = 'select count(*) from %s where week_no = %s' % (
ind_table, j)
stmt2 = 'select count(*) from %s where response_category like "Fail" and week_no = %s' % (
ind_table, j)
cursor.execute(stmt1)
docIdLst = cursor.fetchall()
for doc in docIdLst:
main_count = int(doc[0])
cursor.execute(stmt2)
docIdLst = cursor.fetchall()
for doc in docIdLst:
fail_count = int(doc[0])
fail_per = 0
if main_count:
fail_per = float(fail_count) / main_count * 100
ind_doc_lst[pos] = main_count
ind_doc_lst[int(pos) + 7] = int(math.ceil(fail_per))
pos += 1
doc_lst.append(ind_doc_lst)
i += 1
conn.close()
format3 = workbook.add_format()
format3.set_bg_color('red')
for ind_doc in doc_lst:
col = 0
for i in range(15):
if i < 9:
value = ind_doc[i]
worksheet.write(row, col, value)
else:
if str(ind_doc[i]):
value = str(ind_doc[i]) + '%'
if ind_doc[i] < 3:
worksheet.write(row, col, value)
else:
worksheet.write(row, col, value, format3)
else:
value = str(ind_doc[i])
worksheet.write(row, col, value)
col += 1
row += 1
workbook.close()
ret = 'Lexis_Link_Monitoring/output/report.xlsx'
return ret
