Hi Snippsat
I uninstalled the Python 3.6 and installed the Python 3.7:
Python 3.7.1 (v3.7.1:260ec2c36a, Oct 20 2018, 14:05:16) [MSC v.1915 32 bit (Intel)] on win32
Type "help", "copyright", "credits" or "license" for more information.
I again did the pyinstall by using the below command :
pyinstaller.exe --onefile --windowed --name benefitenrollmentcompare benefitenrollmentcomparecoa.py
And tried to run the exe from cmd and got the below error:
File "benefitenrollmentcompare.exe", line 1
SyntaxError: Non-UTF-8 code starting with '\x90' in file benefitenrollmentcompare.exe on line 1, but no encoding declared; see
http://python.org/dev/peps/pep-0263/ for details.
Below is the python script code:
#!/usr/local/bin/python
# coding: utf-8 -*-
import os, sys
import pandas
import sqlite3
from tkinter import *
import tkinter
import tkinter.messagebox
import csv
from xlsxwriter.workbook import Workbook
import urllib
def proces():
conn = sqlite3.connect("bndb.db")
c = conn.cursor()
c.execute('''CREATE TABLE if not exists BEN_CONV_COMP_LEGACY_DATA
(PERSON_NUMBER text,
PARTICIPANT_LAST_NAME text,
PARTICIPANT_FIRST_NAME text,
PGM_NAME text,
PLAN_TYPE text,
PLAN_NAME text,
OPTION_NAME text,
EFFECTIVE_START_DATE text,
EFFECTIVE_END_DATE text,
ENRT_CVG_THRU_DT text,
RT_STRT_DT text,
RT_END_DT text,
TX_TYP_CD text,
CMCD_RT_VAL text,
--EMPLOYEE_PRETAX text,
--EMPLOYEE_AFTERTAX text,
--IMPUTED_INCOME text,
COVERAGE_AMOUNT text,
SUSPENDED_ELECTIONS text,
PAYROLL_NAME text,
ORGNL_ENRT_DT text,
COVERAGE_STRT_DT text,
COVERAGE_END_DT text,
LIFE_EVT_NAME text,
LIFE_EVT_OCCURED_DT text,
ASSIGNMENT_STATUS text,
OTHER_PLAN_NAME text)''')
c.execute('''delete from BEN_CONV_COMP_LEGACY_DATA''')
c.execute('''CREATE TABLE if not exists BEN_CONV_COMP_CLOUD_DATA
(PERSON_NUMBER text,
PARTICIPANT_LAST_NAME text,
PARTICIPANT_FIRST_NAME text,
PGM_NAME text,
PLAN_TYPE text,
PLAN_NAME text,
OPTION_NAME text,
TX_TYP_CD text,
RATE_VALUE text,
COVERAGE_AMOUNT text,
SUSPENDED_ELECTIONS text,
PAYROLL_NAME text,
ORIGINAL_COVERAGE_STRT_DT text,
COVERAGE_STRT_DT text,
COVERAGE_END_DT text,
LIFE_EVT_NAME text,
LIFE_EVT_OCCURED_DT text,
ASSIGNMENT_STATUS text,
OTHER_PLAN_NAME)''')
c.execute('''delete from BEN_CONV_COMP_CLOUD_DATA''')
c.execute('''CREATE TABLE if not exists BEN_CONV_CLOUD_PERSON_DATA(
PERSON_NUMBER,
PER_EFFECTIVE_START_DATE,
PER_EFFECTIVE_END_DATE,
LAST_NAME,
FIRST_NAME,
DATE_OF_BIRTH,
DATE_OF_DEATH,
USER_PERSON_TYPE,
ORIGINAL_DATE_OF_HIRE,
HIRE_DATE,
TERMINATION_DATE,
LEGAL_ENTITY_ID,
LEGAL_EMPLOYER,
PAYROLL_RELATIONSHIP_NUMBER,
PPRD_START_DATE,
PPRD_END_DATE,
PAYROLL_NAME,
ASSIGNMENT_NUMBER,
ASG_EFFECTIVE_START_DATE,
ASG_EFFECTIVE_END_DATE,
ASSIGNMENT_STATUS_TYPE,
EMPLOYMENT_CATEGORY,
BARGAINING_UNIT,
PEOPLE_GROUP,
BENEFIT_BATCH_STATUS,
SALARY,
BENEFIT_RELATION_NAME,
LOCATION,
LOCATION_COUNTRY,
LOCATION_COUNTY,
LOCATION_STATE
)''')
c.execute('''delete from BEN_CONV_CLOUD_PERSON_DATA''')
c.execute('''CREATE TABLE if not exists STATIC_CROSS_REF
(COUNTRY_CODE text,
REF_TYPE text,
LEGACY text,
CLOUD text)''')
c.execute('''delete from STATIC_CROSS_REF''')
df1 = pandas.read_csv("Legacy_Benefits_Employee.csv")
df2 = pandas.read_csv("Cloud_Benefits_Employee.csv")
df7 = pandas.read_csv("SCR.csv")
df15 = pandas.read_csv("ALL Person Report.csv")#,encoding='latin-1')
df1.to_sql("BEN_CONV_COMP_LEGACY_DATA", conn, if_exists='append', index=False)
df2.to_sql("BEN_CONV_COMP_CLOUD_DATA", conn, if_exists='append', index=False)
df7.to_sql("STATIC_CROSS_REF", conn, if_exists='append', index=False)
df15.to_sql("BEN_CONV_CLOUD_PERSON_DATA", conn, if_exists='append', index=False)
tkinter.messagebox.showwarning("Information","Please note that upload to staging table is complete, Compare is in progress.")
c.execute('''select count(distinct person_number) from BEN_CONV_COMP_LEGACY_DATA''')
(total_unique_person_num_legacy,) = c.fetchone()
Entry.insert(E1,0,total_unique_person_num_legacy)
print(total_unique_person_num_legacy)
c.execute('''select distinct pgm_name from BEN_CONV_COMP_LEGACY_DATA''')
legacy_pgm_name = c.fetchall()
Entry.insert(E2,0,legacy_pgm_name)
print(legacy_pgm_name)
c.execute('''select count(distinct person_number) from BEN_CONV_COMP_CLOUD_DATA''')
(total_unique_person_num_cloud,) = c.fetchone()
Entry.insert(E3,0,total_unique_person_num_cloud)
print(total_unique_person_num_cloud)
c.execute('''select distinct pgm_name from BEN_CONV_COMP_CLOUD_DATA''')
cloud_pgm_name = c.fetchall()
Entry.insert(E4,0,cloud_pgm_name)
print(legacy_pgm_name)
c.execute('''select count(distinct cloud.person_number) total_person_
from BEN_CONV_COMP_CLOUD_DATA cloud, BEN_CONV_COMP_LEGACY_DATA legacy
where legacy.person_number=cloud.person_number''')
unique_common_emp_count = c.fetchall()
Entry.insert(E5,0,unique_common_emp_count)
print(unique_common_emp_count)
#Missing Plan Type in Cloud
sql_statement16= '''
select legacy2.* from BEN_CONV_COMP_LEGACY_DATA legacy2 ,( select legacy.person_number,legacy.PGM_NAME PROGRAM_NAME,legacy.plan_type legacy_plan_type from BEN_CONV_COMP_LEGACY_DATA legacy, STATIC_CROSS_REF SCR
where SCR.REF_TYPE = 'PLAN_TYPE'
AND LEGACY.PLAN_TYPE = SCR.LEGACY
EXCEPT
select cloud.person_number,cloud.PGM_NAME PROGRAM_NAME,scr.legacy legacy_plan_type
from BEN_CONV_COMP_CLOUD_DATA CLOUD, STATIC_CROSS_REF SCR
where 1=1
AND SCR.REF_TYPE = 'PLAN_TYPE'
and SCR.CLOUD = CLOUD.PLAN_TYPE) det
where legacy2.person_number=det.person_number
and legacy2.pgm_name=det.PROGRAM_NAME
and legacy2.plan_type=det.legacy_plan_type'''
df16 = pandas.read_sql_query(sql_statement16,conn)
#Missing Plan Type in Legacy
sql_statement17= '''
select distinct cloud2.person_number,cloud2.pgm_name,cloud2.plan_type,cloud2.plan_name,
cloud2.option_name,cloud2.coverage_amount,
CLOUD2.ORIGINAL_COVERAGE_STRT_DT cloud_orig_enrt_date,
CLOUD2.COVERAGE_STRT_dT cloud_cvg_start_date
from BEN_CONV_COMP_CLOUD_DATA cloud2 ,( select cloud.person_number,cloud.PGM_NAME PROGRAM_NAME,scr.legacy missing_legacy_plan_type,CLOUD.plan_type cloud_plan_type
from BEN_CONV_COMP_CLOUD_DATA CLOUD, STATIC_CROSS_REF SCR
where 1=1
AND SCR.REF_TYPE = 'PLAN_TYPE'
and SCR.CLOUD = CLOUD.PLAN_TYPE
EXCEPT
select legacy.person_number,legacy.PGM_NAME PROGRAM_NAME,legacy.plan_type missing_legacy_plan_type,SCR.CLOUD cloud_plan_type from BEN_CONV_COMP_LEGACY_DATA legacy, STATIC_CROSS_REF SCR
where SCR.REF_TYPE = 'PLAN_TYPE'
AND LEGACY.PLAN_TYPE = SCR.LEGACY) det
where cloud2.person_number=det.person_number
and cloud2.pgm_name=det.PROGRAM_NAME
and cloud2.plan_type=det.cloud_plan_type
;'''
df17 = pandas.read_sql_query(sql_statement17,conn)
#To validate Plans
sql_statement1= '''select cloud.person_number,CLOUD.PGM_NAME PROGRAM_NAME, CLOUD.PLAN_NAME cloud_plan , LEGACY.PLAN_NAME legacy_plan_name,CLOUD.OPTION_NAME CLOUD_OPTION_NAME,
LEGACY.OPTION_NAME LEGACY_OPTION_NAME , CLOUD.ORIGINAL_COVERAGE_STRT_DT cloud_orig_enrt_date,LEGACY.ORGNL_ENRT_DT legacy_orig_enrt_date,
CLOUD.COVERAGE_STRT_dT cloud_cvg_start_date , LEGACY.COVERAGE_STRT_dT legacy_cvg_start_date,
( select BENEFIT_BATCH_STATUS from BEN_CONV_CLOUD_PERSON_DATA hr where hr.PERSON_NUMBER=cloud.person_number) benefit_conversion_status
FROM BEN_CONV_COMP_CLOUD_DATA CLOUD, BEN_CONV_COMP_LEGACY_DATA LEGACY,
STATIC_CROSS_REF SCR,STATIC_CROSS_REF SCR2
WHERE CLOUD.PERSON_NUMBER=LEGACY.PERSON_NUMBER
AND CLOUD.PGM_NAME=LEGACY.PGM_NAME
AND SCR.CLOUD=CLOUD.PLAN_NAME
AND SCR.REF_TYPE='PLAN_NAME'
AND SCR2.REF_TYPE = 'PLAN_TYPE'
AND CLOUD.PLAN_TYPE = SCR2.CLOUD
AND LEGACY.PLAN_TYPE = SCR2.LEGACY
AND SCR.LEGACY <> LEGACY.PLAN_NAME'''
df3 = pandas.read_sql_query(sql_statement1,conn)
#Validate Option
sql_statement2= '''select cloud.person_number,CLOUD.PGM_NAME PROGRAM_NAME, CLOUD.PLAN_NAME cloud_plan , LEGACY.PLAN_NAME legacy_plan_name,CLOUD.OPTION_NAME CLOUD_OPTION_NAME,
LEGACY.OPTION_NAME LEGACY_OPTION_NAME ,CLOUD.ORIGINAL_COVERAGE_STRT_DT cloud_orig_enrt_date,LEGACY.ORGNL_ENRT_DT legacy_orig_enrt_date,
CLOUD.COVERAGE_STRT_dT cloud_cvg_start_date , LEGACY.COVERAGE_STRT_dT legacy_cvg_start_date,
( select BENEFIT_BATCH_STATUS from BEN_CONV_CLOUD_PERSON_DATA hr where hr.PERSON_NUMBER=cloud.person_number) benefit_conversion_status
FROM BEN_CONV_COMP_CLOUD_DATA CLOUD, BEN_CONV_COMP_LEGACY_DATA LEGACY, STATIC_CROSS_REF SCR,
STATIC_CROSS_REF SCr2,STATIC_CROSS_REF SCR3
WHERE CLOUD.PERSON_NUMBER=LEGACY.PERSON_NUMBER
AND CLOUD.PGM_NAME=LEGACY.PGM_NAME
AND SCR.CLOUD=CLOUD.PLAN_NAME
AND SCR.REF_TYPE='PLAN_NAME'
AND SCR3.REF_TYPE='PLAN_TYPE'
AND CLOUD.PLAN_TYPE=SCR3.CLOUD
AND LEGACY.PLAN_TYPE=SCR3.LEGACY
and scr2.cloud=cloud.option_name
AND SCR2.REF_TYPE='OPTION_NAME'
AND SCR2.LEGACY <> LEGACY.OPTION_NAME'''
df4 = pandas.read_sql_query(sql_statement2,conn)
#Emps_Missing_In_Cloud
sql_statement3= '''SELECT person_number,pgm_name,plan_type, plan_name, option_name,ORGNL_ENRT_DT,COVERAGE_STRT_dT,reason1,
person_number reason2,
CASE
WHEN salary = 0 THEN 'Salary is null'
ELSE 'Salary Exist'
END
reason3,
CASE
WHEN payroll_name = 'NA' THEN 'Payroll is null'
ELSE 'Payroll Exist'
END
reason4,
CASE
WHEN benefit_batch_status = 'ERROR' THEN 'Benefit Conversion Error'
ELSE null
END
reason5
FROM
(
SELECT legacy.person_number,NULL reason1,hr.person_number,
ifnull(hr.salary,0) salary,
ifnull(hr.payroll_name,'NA') payroll_name,
ifnull(hr.BENEFIT_BATCH_STATUS,'NA') benefit_batch_status,
legacy.*
FROM
ben_conv_comp_legacy_data legacy,
ben_conv_cloud_person_data hr
WHERE
legacy.person_number NOT IN (
SELECT
person_number
FROM
ben_conv_comp_cloud_data
)
AND hr.person_number = legacy.person_number
AND hr.termination_date IS NULL
) a
UNION ALL
SELECT
legacy.person_number,
legacy.pgm_name,
legacy.plan_type,
legacy.plan_name,
legacy.option_name,LEGACY.ORGNL_ENRT_DT, LEGACY.COVERAGE_STRT_dT,
'Person is Terminated' reason1,
NULL reason2,
NULL reason3,
NULL reason4,
null reason5
FROM
ben_conv_comp_legacy_data legacy,
ben_conv_cloud_person_data hr
WHERE
legacy.person_number NOT IN (
SELECT
person_number
FROM
ben_conv_comp_cloud_data
)
AND hr.person_number = legacy.person_number
AND hr.termination_date IS NOT NULL
UNION ALL
SELECT
legacy.person_number,
legacy.pgm_name,
legacy.plan_type,
legacy.plan_name,
legacy.option_name,LEGACY.ORGNL_ENRT_DT, LEGACY.COVERAGE_STRT_dt,
'Person does not exist in HR' reason1,
NULL reason2,
NULL reason3,
NULL reason4,
null reason5
FROM
ben_conv_comp_legacy_data legacy
WHERE
legacy.person_number NOT IN (
SELECT
person_number
FROM
ben_conv_comp_cloud_data
)
AND legacy.person_number NOT IN (
SELECT
hr.person_number
FROM
ben_conv_cloud_person_data hr
);'''
df5 = pandas.read_sql_query(sql_statement3,conn)
#Emps_Missing_In_Legacy
sql_statement4= '''select * from BEN_CONV_COMP_cloud_DATA
where person_number not in (
select person_number from BEN_CONV_COMP_LEGACY_DATA );'''
df6 = pandas.read_sql_query(sql_statement4,conn)
sql_statement5= '''select * from BEN_CONV_COMP_cloud_DATA
where person_number not in (
select person_number from BEN_CONV_COMP_LEGACY_DATA );'''
df10 = pandas.read_sql_query(sql_statement5,conn)
# VALDIATE EMPLOYER RATE
sql_statement6= '''
SELECT distinct
CLOUD.PERSON_NUMBER,
CLOUD.PGM_NAME PROGRAM_NAME,
CLOUD.PLAN_NAME CLOUD_PLAN,
LEGACY.PLAN_NAME LEGACY_PLAN_NAME,
CLOUD.OPTION_NAME CLOUD_OPTION_NAME,
LEGACY.OPTION_NAME LEGACY_OPTION_NAME,
CLOUD.RATE_VALUE CLOUD_EMPLOYER_RATE,
LEGACY.CMCD_RT_VAL LEGACY_EMPLOYER_RATE ,
CLOUD.COVERAGE_AMOUNT CLOUD_COVERAGE_AMOUNT,
LEGACY.COVERAGE_AMOUNT LEGACY_COVERAGE_AMOUNT
FROM
BEN_CONV_COMP_CLOUD_DATA CLOUD,
BEN_CONV_COMP_LEGACY_DATA LEGACY,
STATIC_CROSS_REF SCR,
STATIC_CROSS_REF SCR2
WHERE
CLOUD.PERSON_NUMBER = LEGACY.PERSON_NUMBER
AND CLOUD.PGM_NAME = LEGACY.PGM_NAME
AND SCR.CLOUD = CLOUD.PLAN_NAME
AND SCR.REF_TYPE = 'PLAN_NAME'
AND SCR2.REF_TYPE = 'PLAN_TYPE'
AND CLOUD.PLAN_TYPE = SCR2.CLOUD
AND LEGACY.PLAN_TYPE = SCR2.LEGACY
AND SCR.LEGACY = LEGACY.PLAN_NAME
AND CLOUD.OPTION_NAME IS NULL
AND LEGACY.OPTION_NAME IS NULL
AND CLOUD.TX_TYP_CD = 'NOTAPPLICABLE'
AND LEGACY.TX_TYP_CD='NOTAPPLICABLE'
AND (CLOUD.RATE_VALUE - LEGACY.CMCD_RT_VAL)<>0
AND ROUND( (CLOUD.RATE_VALUE - LEGACY.CMCD_RT_VAL),2) <>.01
AND ROUND( (CLOUD.RATE_VALUE - LEGACY.CMCD_RT_VAL),2) <>-.01
UNION ALL
SELECT distinct
CLOUD.PERSON_NUMBER,
CLOUD.PGM_NAME PROGRAM_NAME,
CLOUD.PLAN_NAME CLOUD_PLAN,
LEGACY.PLAN_NAME LEGACY_PLAN_NAME,
CLOUD.OPTION_NAME CLOUD_OPTION_NAME,
LEGACY.OPTION_NAME LEGACY_OPTION_NAME,
--ROUND( ( (CLOUD.RATE_VALUE * 26) / 12),2)
CLOUD.RATE_VALUE CLOUD_EMPLOYER_RATE,
LEGACY.CMCD_RT_VAL LEGACY_EMPLOYER_RATE,
CLOUD.COVERAGE_AMOUNT CLOUD_COVERAGE_AMOUNT,
LEGACY.COVERAGE_AMOUNT LEGACY_COVERAGE_AMOUNT
FROM
BEN_CONV_COMP_CLOUD_DATA CLOUD,
BEN_CONV_COMP_LEGACY_DATA LEGACY,
STATIC_CROSS_REF SCR,
STATIC_CROSS_REF SCR2,
STATIC_CROSS_REF SCR3
WHERE
CLOUD.PERSON_NUMBER = LEGACY.PERSON_NUMBER
AND CLOUD.PGM_NAME = LEGACY.PGM_NAME
AND CLOUD.PLAN_TYPE = LEGACY.PLAN_TYPE
AND SCR.CLOUD = CLOUD.PLAN_NAME
AND SCR.REF_TYPE = 'PLAN_NAME'
AND SCR3.REF_TYPE = 'PLAN_TYPE'
AND CLOUD.PLAN_TYPE = SCR3.CLOUD
AND LEGACY.PLAN_TYPE = SCR3.LEGACY
AND SCR2.CLOUD = CLOUD.OPTION_NAME
AND SCR2.REF_TYPE = 'OPTION_NAME'
AND SCR2.LEGACY = LEGACY.OPTION_NAME
AND CLOUD.TX_TYP_CD = 'NOTAPPLICABLE'
AND LEGACY.TX_TYP_CD='NOTAPPLICABLE'
AND (CLOUD.RATE_VALUE - LEGACY.CMCD_RT_VAL)<>0
AND ROUND( (CLOUD.RATE_VALUE - LEGACY.CMCD_RT_VAL),2) <>.01
AND ROUND( (CLOUD.RATE_VALUE - LEGACY.CMCD_RT_VAL),2) <>-.01'''
df11 = pandas.read_sql_query(sql_statement6,conn)
# VALDIATE Employee Pre Tax RATE
sql_statement7= '''
SELECT distinct
CLOUD.PERSON_NUMBER,
CLOUD.PGM_NAME PROGRAM_NAME,
CLOUD.PLAN_NAME CLOUD_PLAN,
LEGACY.PLAN_NAME LEGACY_PLAN_NAME,
CLOUD.OPTION_NAME CLOUD_OPTION_NAME,
LEGACY.OPTION_NAME LEGACY_OPTION_NAME,
--ROUND( ( (CLOUD.RATE_VALUE * 26) / 12),2)
CLOUD.RATE_VALUE CLOUD_EMP_PRETAX_RATE,
LEGACY.CMCD_RT_VAL LEGACY_EMP_PRETAX_RATE ,
CLOUD.COVERAGE_AMOUNT CLOUD_COVERAGE_AMOUNT,
LEGACY.COVERAGE_AMOUNT LEGACY_COVERAGE_AMOUNT
FROM
BEN_CONV_COMP_CLOUD_DATA CLOUD,
BEN_CONV_COMP_LEGACY_DATA LEGACY,
STATIC_CROSS_REF SCR,
STATIC_CROSS_REF SCR2
WHERE
CLOUD.PERSON_NUMBER = LEGACY.PERSON_NUMBER
AND CLOUD.PGM_NAME = LEGACY.PGM_NAME
AND SCR.CLOUD = CLOUD.PLAN_NAME
AND SCR.REF_TYPE = 'PLAN_NAME'
AND SCR2.REF_TYPE = 'PLAN_TYPE'
AND CLOUD.PLAN_TYPE = SCR2.CLOUD
AND LEGACY.PLAN_TYPE = SCR2.LEGACY
AND SCR.LEGACY = LEGACY.PLAN_NAME
AND CLOUD.OPTION_NAME IS NULL
AND LEGACY.OPTION_NAME IS NULL
AND CLOUD.TX_TYP_CD = 'PRETAX'
AND LEGACY.TX_TYP_CD ='PRETAX'
AND ( CLOUD.RATE_VALUE - LEGACY.CMCD_RT_VAL) <>0
AND ROUND( (CLOUD.RATE_VALUE - LEGACY.CMCD_RT_VAL),2) <>.01
AND ROUND( (CLOUD.RATE_VALUE - LEGACY.CMCD_RT_VAL),2) <>-.01
UNION ALL
SELECT distinct
CLOUD.PERSON_NUMBER,
CLOUD.PGM_NAME PROGRAM_NAME,
CLOUD.PLAN_NAME CLOUD_PLAN,
LEGACY.PLAN_NAME LEGACY_PLAN_NAME,
CLOUD.OPTION_NAME CLOUD_OPTION_NAME,
LEGACY.OPTION_NAME LEGACY_OPTION_NAME,
--ROUND( ( (CLOUD.RATE_VALUE * 26) / 12),2)
CLOUD.RATE_VALUE CLOUD_EMP_PRETAX_RATE,
LEGACY.CMCD_RT_VAL LEGACY_EMP_PRETAX_RATE,
CLOUD.COVERAGE_AMOUNT CLOUD_COVERAGE_AMOUNT,
LEGACY.COVERAGE_AMOUNT LEGACY_COVERAGE_AMOUNT
FROM
BEN_CONV_COMP_CLOUD_DATA CLOUD,
BEN_CONV_COMP_LEGACY_DATA LEGACY,
STATIC_CROSS_REF SCR,
STATIC_CROSS_REF SCR2,
STATIC_CROSS_REF SCR3
WHERE
CLOUD.PERSON_NUMBER = LEGACY.PERSON_NUMBER
AND CLOUD.PGM_NAME = LEGACY.PGM_NAME
AND CLOUD.PLAN_TYPE = LEGACY.PLAN_TYPE
AND SCR.CLOUD = CLOUD.PLAN_NAME
AND SCR.REF_TYPE = 'PLAN_NAME'
AND SCR3.REF_TYPE = 'PLAN_TYPE'
AND CLOUD.PLAN_TYPE = SCR3.CLOUD
AND LEGACY.PLAN_TYPE = SCR3.LEGACY
AND SCR2.CLOUD = CLOUD.OPTION_NAME
AND SCR2.REF_TYPE = 'OPTION_NAME'
AND SCR2.LEGACY = LEGACY.OPTION_NAME
AND CLOUD.TX_TYP_CD = 'PRETAX'
AND LEGACY.TX_TYP_CD ='PRETAX'
AND (CLOUD.RATE_VALUE - LEGACY.CMCD_RT_VAL)<>0
AND ROUND( (CLOUD.RATE_VALUE- LEGACY.CMCD_RT_VAL),2) <>.01
AND ROUND( (CLOUD.RATE_VALUE - LEGACY.CMCD_RT_VAL),2) <>-.01'''
df12 = pandas.read_sql_query(sql_statement7,conn)
# VALDIATE Employee After Tax RATE
sql_statement8= '''
SELECT distinct
CLOUD.PERSON_NUMBER,
CLOUD.PGM_NAME PROGRAM_NAME,
CLOUD.PLAN_NAME CLOUD_PLAN,
LEGACY.PLAN_NAME LEGACY_PLAN_NAME,
CLOUD.OPTION_NAME CLOUD_OPTION_NAME,
LEGACY.OPTION_NAME LEGACY_OPTION_NAME,
--ROUND( ( (CLOUD.RATE_VALUE * 26) / 12),2)
CLOUD.RATE_VALUE CLOUD_EMP_AFTERTAX_RATE,
LEGACY.CMCD_RT_VAL LEGACY_EMP_AFTERTAX_RATE ,
CLOUD.COVERAGE_AMOUNT CLOUD_COVERAGE_AMOUNT,
LEGACY.COVERAGE_AMOUNT LEGACY_COVERAGE_AMOUNT
FROM
BEN_CONV_COMP_CLOUD_DATA CLOUD,
BEN_CONV_COMP_LEGACY_DATA LEGACY,
STATIC_CROSS_REF SCR,
STATIC_CROSS_REF SCR2
WHERE
CLOUD.PERSON_NUMBER = LEGACY.PERSON_NUMBER
AND CLOUD.PGM_NAME = LEGACY.PGM_NAME
AND SCR.CLOUD = CLOUD.PLAN_NAME
AND SCR.REF_TYPE = 'PLAN_NAME'
AND SCR2.REF_TYPE = 'PLAN_TYPE'
AND CLOUD.PLAN_TYPE = SCR2.CLOUD
AND LEGACY.PLAN_TYPE = SCR2.LEGACY
AND SCR.LEGACY = LEGACY.PLAN_NAME
AND CLOUD.OPTION_NAME IS NULL
AND LEGACY.OPTION_NAME IS NULL
AND CLOUD.TX_TYP_CD = 'AFTERTAX'
AND LEGACY.TX_TYP_CD = 'AFTERTAX'
AND (CLOUD.RATE_VALUE - LEGACY.CMCD_RT_VAL)<>0
AND round( (CLOUD.RATE_VALUE - LEGACY.CMCD_RT_VAL),2) <>.01
AND round( (CLOUD.RATE_VALUE - LEGACY.CMCD_RT_VAL),2) <>-.01
UNION ALL
SELECT distinct
CLOUD.PERSON_NUMBER,
CLOUD.PGM_NAME PROGRAM_NAME,
CLOUD.PLAN_NAME CLOUD_PLAN,
LEGACY.PLAN_NAME LEGACY_PLAN_NAME,
CLOUD.OPTION_NAME CLOUD_OPTION_NAME,
LEGACY.OPTION_NAME LEGACY_OPTION_NAME,
--ROUND( ( (CLOUD.RATE_VALUE * 26) / 12),2)
CLOUD.RATE_VALUE CLOUD_EMP_AFTERTAX_RATE,
LEGACY.CMCD_RT_VAL LEGACY_EMP_AFTERTAX_RATE,
CLOUD.COVERAGE_AMOUNT CLOUD_COVERAGE_AMOUNT,
LEGACY.COVERAGE_AMOUNT LEGACY_COVERAGE_AMOUNT
FROM
BEN_CONV_COMP_CLOUD_DATA CLOUD,
BEN_CONV_COMP_LEGACY_DATA LEGACY,
STATIC_CROSS_REF SCR,
STATIC_CROSS_REF SCR2,
STATIC_CROSS_REF SCR3
WHERE
CLOUD.PERSON_NUMBER = LEGACY.PERSON_NUMBER
AND CLOUD.PGM_NAME = LEGACY.PGM_NAME
AND CLOUD.PLAN_TYPE = LEGACY.PLAN_TYPE
AND SCR.CLOUD = CLOUD.PLAN_NAME
AND SCR.REF_TYPE = 'PLAN_NAME'
AND SCR3.REF_TYPE = 'PLAN_TYPE'
AND CLOUD.PLAN_TYPE = SCR3.CLOUD
AND LEGACY.PLAN_TYPE = SCR3.LEGACY
AND SCR2.CLOUD = CLOUD.OPTION_NAME
AND SCR2.REF_TYPE = 'OPTION_NAME'
AND SCR2.LEGACY = LEGACY.OPTION_NAME
AND CLOUD.TX_TYP_CD = 'AFTERTAX'
AND LEGACY.TX_TYP_CD = 'AFTERTAX'
AND (CLOUD.RATE_VALUE - LEGACY.CMCD_RT_VAL)<>0
AND round((CLOUD.RATE_VALUE - LEGACY.CMCD_RT_VAL),2) <>.01
AND round((CLOUD.RATE_VALUE - LEGACY.CMCD_RT_VAL),2) <>-.01'''
df13 = pandas.read_sql_query(sql_statement8,conn)
# VALDIATE Covergae Amount
sql_statement9= '''SELECT distinct
CLOUD.PERSON_NUMBER,
CLOUD.PGM_NAME PROGRAM_NAME,
CLOUD.PLAN_NAME CLOUD_PLAN,
LEGACY.PLAN_NAME LEGACY_PLAN_NAME,
CLOUD.OPTION_NAME CLOUD_OPTION_NAME,
LEGACY.OPTION_NAME LEGACY_OPTION_NAME,
CLOUD.RATE_VALUE CLOUD_EMPLOYER_RATE,
LEGACY.CMCD_RT_VAL LEGACY_EMPLOYER_RATE ,
CLOUD.COVERAGE_AMOUNT CLOUD_COVERAGE_AMOUNT,
LEGACY.COVERAGE_AMOUNT LEGACY_COVERAGE_AMOUNT,CLOUD.ORIGINAL_COVERAGE_STRT_DT cloud_orig_enrt_date,LEGACY.ORGNL_ENRT_DT legacy_orig_enrt_date,
CLOUD.COVERAGE_STRT_dT cloud_cvg_start_date , LEGACY.COVERAGE_STRT_dT legacy_cvg_start_date
FROM
BEN_CONV_COMP_CLOUD_DATA CLOUD,
BEN_CONV_COMP_LEGACY_DATA LEGACY,
STATIC_CROSS_REF SCR,
STATIC_CROSS_REF SCR2
WHERE
CLOUD.PERSON_NUMBER = LEGACY.PERSON_NUMBER
AND CLOUD.PGM_NAME = LEGACY.PGM_NAME
AND SCR.CLOUD = CLOUD.PLAN_NAME
AND SCR.REF_TYPE = 'PLAN_NAME'
AND SCR2.REF_TYPE = 'PLAN_TYPE'
AND CLOUD.PLAN_TYPE = SCR2.CLOUD
AND LEGACY.PLAN_TYPE = SCR2.LEGACY
AND SCR.LEGACY = LEGACY.PLAN_NAME
AND CLOUD.OPTION_NAME IS NULL
AND LEGACY.OPTION_NAME IS NULL
--AND CLOUD.TX_TYP_CD = 'NOTAPPLICABLE'
AND ifnull(CLOUD.coverage_amount,0) - ifnull(LEGACY.coverage_amount,0)<>0
UNION ALL
SELECT
CLOUD.PERSON_NUMBER,
CLOUD.PGM_NAME PROGRAM_NAME,
CLOUD.PLAN_NAME CLOUD_PLAN,
LEGACY.PLAN_NAME LEGACY_PLAN_NAME,
CLOUD.OPTION_NAME CLOUD_OPTION_NAME,
LEGACY.OPTION_NAME LEGACY_OPTION_NAME,
--ROUND( ( (CLOUD.RATE_VALUE * 26) / 12),2)
CLOUD.RATE_VALUE CLOUD_EMPLOYER_RATE,
LEGACY.CMCD_RT_VAL LEGACY_EMPLOYER_RATE,
CLOUD.COVERAGE_AMOUNT CLOUD_COVERAGE_AMOUNT,
LEGACY.COVERAGE_AMOUNT LEGACY_COVERAGE_AMOUNT,CLOUD.ORIGINAL_COVERAGE_STRT_DT,LEGACY.ORGNL_ENRT_DT, CLOUD.COVERAGE_STRT_dT, LEGACY.COVERAGE_STRT_dT
FROM
BEN_CONV_COMP_CLOUD_DATA CLOUD,
BEN_CONV_COMP_LEGACY_DATA LEGACY,
STATIC_CROSS_REF SCR,
STATIC_CROSS_REF SCR2,
STATIC_CROSS_REF SCR3
WHERE
CLOUD.PERSON_NUMBER = LEGACY.PERSON_NUMBER
AND CLOUD.PGM_NAME = LEGACY.PGM_NAME
AND CLOUD.PLAN_TYPE = LEGACY.PLAN_TYPE
AND SCR.CLOUD = CLOUD.PLAN_NAME
AND SCR.REF_TYPE = 'PLAN_NAME'
AND SCR3.REF_TYPE = 'PLAN_TYPE'
AND CLOUD.PLAN_TYPE = SCR3.CLOUD
AND LEGACY.PLAN_TYPE = SCR3.LEGACY
AND SCR2.CLOUD = CLOUD.OPTION_NAME
AND SCR2.REF_TYPE = 'OPTION_NAME'
AND SCR2.LEGACY = LEGACY.OPTION_NAME
--AND CLOUD.TX_TYP_CD = 'NOTAPPLICABLE'
AND ifnull(CLOUD.coverage_amount,0) - ifnull(LEGACY.coverage_amount,0)<>0'''
df14 = pandas.read_sql_query(sql_statement9,conn)
with pandas.ExcelWriter("Enrollments.xlsx") as writer1:
df3.to_excel(writer1, sheet_name="Plan_Mismatch",index=False)
df4.to_excel(writer1, sheet_name="Option_Mismatch",index=False)
df5.to_excel(writer1, sheet_name="Emps_Missing_In_Cloud",index=False)
df6.to_excel(writer1, sheet_name="Emps_Missing_In_Legacy",index=False)
df11.to_excel(writer1, sheet_name="Employer Rate Mismatch",index=False)
df12.to_excel(writer1, sheet_name="EE Pretax Rate Mismatch",index=False)
df13.to_excel(writer1, sheet_name="EE Aftertax Rate Mismatch",index=False)
df14.to_excel(writer1, sheet_name="Covergae Amount Mismatch",index=False)
df16.to_excel(writer1, sheet_name="Plan Type missing in Cloud",index=False)
df17.to_excel(writer1, sheet_name="Plan Type missing in Legacy",index=False)
tkinter.messagebox.showwarning("Information","Benefit_Cloud_vs_Legacy_Enrollments.xlsx file generated successfully")
conn.commit()
conn.close()
top = tkinter.Tk()
top.title('Compare Tool')
top.iconbitmap('myicon.ico')
top.geometry("800x500")
top.configure(background='silver')
L1 = Label(top, text="Welcome to Tool!", fg="black",bg = "light green").grid(row=1,column=2,padx=20, pady=20)
L2 = Label(top,justify=CENTER, text="Total Unique Emps in Legacy", fg="black",bg = "light green").grid(row=2,column=0,sticky=E)
L3 = Label(top,justify=CENTER, text="Legacy Program Name", fg="black",bg = "light green").grid(row=3,column=0,sticky=E)
L4 = Label(top,justify=CENTER, text="Total Unique Emp in Cloud", fg="black",bg = "light green").grid(row=4,column=0,sticky=E)
L5 = Label(top,justify=CENTER, text="Cloud Program Name", fg="black",bg = "light green").grid(row=5,column=0,sticky=E)
L6 = Label(top,justify=CENTER, text="Total Common Emps in Cloud and Legacy", fg="black",bg = "light green").grid(row=6,column=0,sticky=E)
E1 = Entry(top, bd =5)
E1.grid(row=2,column=2)
E2 = Entry(top, bd =5)
E2.grid(row=3,column=2)
E3 = Entry(top, bd =5)
E3.grid(row=4,column=2)
E4 = Entry(top, bd =5)
E4.grid(row=5,column=2)
E5 = Entry(top, bd =5)
E5.grid(row=6,column=2)
B=Button(top, justify=CENTER,text ="Click here to Upload files and Compare",command = proces,bg = "grey").grid(row=10,column=2,sticky=E,padx=20, pady=20)
top.mainloop()Thanks again for all your help!
Thanks
Gaurav
