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