SELECT
qry_CsltList.[Cslt]
,qry_CsltList.[Name]
,qry_CsltList.[Status]
,qry_CsltList.[Position]
,qry_CsltList.[TermDate]
,qry_CsltList.[SupportAL]
,qry_CsltList.[BusCrdt]
,qry_CsltList.[MgmtAsst]
FROM qry_CsltList
WHERE (qry_CsltList.[EYear]) = ''' + str(supportalyear) + '''
ORDER BY
qry_CsltList.[Cslt]
'''
dfpc = dbq.df_select(driver, db_file, sql)
dfpc['SupportAL'] = dfpc['SupportAL'].values.astype(np.int64)
#----------- get current year rate for practice credit ------
sql = '''
SELECT PracticeCreditRate.AL AS [SupportAL]
,PracticeCreditRate.LumpSum
,PracticeCreditRate.BusCrdtRate
,PracticeCreditRate.BusCrdtQty
,PracticeCreditRate.MgmtAsstRate
,PracticeCreditRate.MgmtAsstQty
FROM PracticeCreditRate
WHERE ([PracticeCreditRate].[Period]) = #''' + endday.strftime("%m/%d/%Y") + '''#
'''
dfpcrate = dbq.df_select(driver, db_file, sql)
# dfpcrate['SupportAL'] = dfpcrate['SupportAL'].values.astype(np.int64)
,qry_SMATranswALAll.[Product IGSI Symbol]
,qry_SMATranswALAll.[Event Activity Description]
,qry_SMATranswALAll.[Product Description]
,qry_SMATranswALAll.[Notes]
FROM qry_SMATranswALAll
WHERE ((qry_SMATranswALAll.CycDate) = # ''' + str(endday) + ''' #)
ORDER BY
qry_SMATranswALAll.[CycDate]
,qry_SMATranswALAll.[Cslt]
,qry_SMATranswALAll.[Client Number]
,qry_SMATranswALAll.[Account Number];
'''
#dftrans = pd.read_sql_query(sql,conn)
#conn.close()
dftrans = dbq.df_select(driver, db_file, sql)
#-----------------------------------------------------------------------
#----------- get Sales Bonus rate ------------
driver = r"{Microsoft Access Driver (*.mdb, *.accdb)};"
db_file = r"F:\Files For\West Wang\Rates.accdb;"
#user = "******"
#password = ""
#odbc_conn_str = r"DRIVER={};DBQ={};".format(driver, db_file)
#conn = pyodbc.connect(odbc_conn_str)
#--------- get New Business rate based on AL for advancing AL ----------
sql = '''SELECT DISTINCT NewBusinessRate.Rate AS NBRate FROM NewBusinessRate WHERE NewBusinessRate.NBYear = ''' + str(endday.year)
#NBRatedf = pd.read_sql_query(sql,conn)
#dftrans['NBRate'] = NBRatedf.at[(0, 'NBRate')]
dfnbrate = dbq.df_select(driver, db_file, sql)
,COUNT(qry_ActiveDD.LKG_CSLT_SMPL_DTE) AS [NofCycle]
FROM qry_ActiveDD
WHERE (qry_ActiveDD.LKG_CSLT_SMPL_DTE) BETWEEN #''' + startday.strftime(
"%m/%d/%Y") + '''# AND #''' + endday.strftime("%m/%d/%Y") + '''#
GROUP BY
qry_ActiveDD.LKG_CSLT_NUM
,qry_ActiveDD.[CStatus]
,qry_ActiveDD.[CTermDate]
,qry_ActiveDD.[CTermReason]
,qry_ActiveDD.[CPosition]
,qry_ActiveDD.LKG_CSLT_LANGUAGE
ORDER BY
qry_ActiveDD.LKG_CSLT_NUM
'''
dfdd = dbq.df_select(driver, db_file, sql)
dds = ','.join([
'%s' % x
for x in dfdd.loc[dfdd['EndDate'] != endday.strftime("%m/%d/%Y"), 'DD']
]) #get all cslts left the role during the year
sql = '''
SELECT
qry_CsltTerm.LKG_CSLT_NUM AS [DD]
,MAX(qry_CsltTerm.LKG_CSLT_SMPL_DTE) AS [EndDate]
FROM qry_CsltTerm
WHERE ((qry_CsltTerm.LKG_CSLT_SMPL_DTE) BETWEEN #''' + startday.strftime(
"%m/%d/%Y") + '''# AND #''' + endday.strftime("%m/%d/%Y") + '''#)
AND qry_CsltTerm.LKG_CSLT_NUM IN (%s)
GROUP BY
'F:\\3-Compensation Programs\\IIROC Compensation\\SMA, FBA Compensation\\SMA.accdb',
'M:\\bak\\SMA' + str(date.today().strftime("%m%d%Y")) + '.accdb')
#-------------------------------------
#----------- get SMA daily transactions and AL information ------------
driver = r"{Microsoft Access Driver (*.mdb, *.accdb)};"
db_file = r"F:\\3-Compensation Programs\\IIROC Compensation\\SMA, FBA Compensation\\SMA.accdb;"
#user = "******"
#password = ""
#odbc_conn_str = r"DRIVER={};DBQ={};".format(driver, db_file)
#conn = pyodbc.connect(odbc_conn_str)
#--------------------------------------------------------------------
#--------- check latest cycle date in database ----------
sql = '''SELECT Max(tbl_SMA.CycDate) AS [CycDate] FROM tbl_SMA; '''
#Cdatedf = pd.read_sql_query(sql,conn)
dfcdate = dbq.df_select(driver, db_file, sql)
latestcycledate = dfcdate.at[(0, 'CycDate')]
print('In database, the latest cycle end date is ' + str(latestcycledate))
if latestcycledate > endday:
print(
'It seems that the cycle date in database is later than your cycle date, which means the transactions may already be entered into database. Please type "1", if you want to proceed:'
)
if input() != '1':
sys.exit("The process is stopped")
#-----------------------------------------------------------------------
filesdir = 'F:\\3-Compensation Programs\\IIROC Compensation\\' + endday.strftime(
"%Y%m%d")
labels = [
,qry_CsltList.[CurrentStatus]
,qry_CsltList.[CurrentPosition]
,qry_CsltList.[TermDate]
,qry_CsltList.[SupportAL]
,qry_CsltList.[NewBus]
,qry_CsltList.[MgmtAsst]
FROM qry_CsltList
WHERE (qry_CsltList.[EYear]) = ''' + str(
supportalyear
) + ''' AND qry_CsltList.LKG_CSLT_SMPL_DTE = #''' + endday.strftime(
"%m/%d/%Y") + '''#
ORDER BY
qry_CsltList.[Cslt]
'''
dfresult = dbq.df_select(driver, db_file, sql)
dfresult['SupportAL'] = dfresult['SupportAL'].values.astype(np.int64)
#----------- get current year rate for benefit credit ------
sql = '''
SELECT BenefitCreditRate.AL AS [SupportAL]
,BenefitCreditRate.LumpSum
,BenefitCreditRate.NewBusRate
,BenefitCreditRate.NewBusQty
,BenefitCreditRate.MgmtAsstRate
,BenefitCreditRate.MgmtAsstQty
FROM BenefitCreditRate
WHERE (BenefitCreditRate.[Period]) = #''' + endday.strftime(
"%m/%d/%Y") + '''#
'''
,qry_CsltList.[CurrentStatus]
,qry_CsltList.[CurrentPosition]
,qry_CsltList.[TermDate]
,qry_CsltList.[SupportAL]
,qry_CsltList.[NewBus]
,qry_CsltList.[MgmtAsst]
FROM qry_CsltList
WHERE (qry_CsltList.[EYear]) = ''' + str(
supportalyear
) + ''' AND qry_CsltList.LKG_CSLT_SMPL_DTE = #''' + endday.strftime(
"%m/%d/%Y") + '''#
ORDER BY
qry_CsltList.[Cslt]
'''
dfresult = dbq.df_select(driver, db_file, sql)
dfresult['SupportAL'] = dfresult['SupportAL'].values.astype(np.int64)
#----------- get current year rate for benefit credit ------
sql = '''
SELECT BenefitCreditRate.AL AS [SupportAL]
,BenefitCreditRate.LumpSum
,BenefitCreditRate.NewBusRate
,BenefitCreditRate.NewBusQty
,BenefitCreditRate.MgmtAsstRate
,BenefitCreditRate.MgmtAsstQty
FROM BenefitCreditRate
WHERE (BenefitCreditRate.[Period]) = #''' + endday.strftime(
"%m/%d/%Y") + '''#
'''
getcycledate = datetime.datetime.strptime(raw_input(), '%m/%d/%Y')
endday = getcycledate
#print 'Cycle start date is ' + str(startday)
print 'Cycle end date is ' + str(endday)
#-------------- get ARB Paid related to Asst and Adj -----------------
sql = '''
SELECT
tARBPaid.TransDesc
,tARBPaid.Cslt
,tARBPaid.Amt AS [Amt]
FROM tARBPaid
WHERE tARBPaid.Period = #''' + endday.strftime("%m/%d/%Y") + '''#;
'''
dfarbpaid = dbq.df_select(driver, db_file, sql)
#print dfarbpaid.loc[dfarbpaid['Cslt']==129]
dfinsurable = dfarbpaid.loc[
dfarbpaid['TransDesc'].str.contains('ASSOC|ADJ', case=False, na=False),
['Cslt', 'Amt']]
#print dfinsurable.loc[dfinsurable['Cslt']==129]
#-------------- get ARB Payable -----------------
sql = '''
SELECT
tARBPayable.Cslt
,tARBPayable.Amt AS [Amt]
FROM tARBPayable
WHERE tARBPayable.CDate = #''' + endday.strftime("%m/%d/%Y") + '''#;
'''