-
Notifications
You must be signed in to change notification settings - Fork 0
/
query.js
132 lines (118 loc) · 48.6 KB
/
query.js
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
const bankAccountQuery = "|search source=\"*/population/bank/account/*\" index=data_quality \n" +
"| search sum_info_id=# \n" +
"| stats sum(total_count) AS Total_Count,sum(eval(if((acct_type_id==25),total_count,0))) AS Total_CD_Count, sum(eval(if((acct_type_id >2 OR acct_type_id != NULL),total_count,0))) as Total_acct_type_id_Count,sum(eval(if((acct_type_id==3),total_count,0))) AS Total_checking_Count,sum(eval(if((acct_type_id==25 OR acct_type_id==8 OR acct_type_id==72),total_count,0))) AS Total_acct_id_count,sum(account_name_count) AS Total_account_name_count,sum(account_holder_count) AS Total_account_holder_count, sum(account_nickname_at_src_site_count) AS Total_account_nickname_at_src_site_count, sum(account_number_count) AS Total_account_number_count, sum(account_open_date_count) AS Total_account_open_date_count, sum(account_holder_rate) AS Total_account_holder_rate_count, sum(available_balance_count) AS Total_available_balance_count, sum(current_balance_count) AS Total_current_balance_count, sum(routing_number_count) AS Total_routing_number_count, sum(interest_rate_count) AS Total_interest_rate_count, sum(as_of_date_count) AS Total_as_of_date_count,sum(eval(if((acct_type_id==25 OR acct_type_id==8 OR acct_type_id==72),maturity_date_count,0))) AS Total_maturity_date_count, sum(interest_earned_ytd_count) AS Total_interest_earned_ytd_count, sum(prev_yr_interest_count) AS Total_prev_yr_interest_count,sum(eval(if((acct_type_id==25),term_count,0))) AS Total_term_count,sum(eval(if((acct_type_id==3),overdraft_protection_count,0))) AS Total_overdraft_protection_count, ,sum(site_account_status_id_count) AS Total_site_account_status_id_count, sum(eval(if((acct_type_id==25),maturity_amount_count,0))) AS Total_maturity_amount_count,sum(annual_percent_yield_count) AS Total_annual_percent_yield_count by sum_info_id, acct_type_id \n" +
"| eval ACCOUNT_NAME=((Total_account_name_count/Total_Count)*100), ACCOUNT_HOLDER=((Total_account_holder_count/Total_Count)*100), ACCOUNT_TYPE_ID=((Total_acct_type_id_Count/Total_Count)*100), ACCOUNT_NICKNAME_AT_SRC_SITE=((Total_account_nickname_at_src_site_count/Total_Count)*100), ACCOUNT_NUMBER=((Total_account_number_count/Total_Count)*100), ACCOUNT_OPEN_DATE=((Total_account_open_date_count/Total_Count)*100), AVAILABLE_BALANCE=((Total_available_balance_count/Total_Count)*100), CURRENT_BALANCE=((Total_current_balance_count/Total_Count)*100), ROUTING_NUMBER=((Total_routing_number_count/Total_Count)*100), INTEREST_RATE=((Total_interest_rate_count/Total_Count)*100), AS_OF_DATE=((Total_as_of_date_count/Total_Count)*100), MATURITY_DATE=((Total_maturity_date_count/Total_acct_id_count)*100), INTEREST_EARNED_YTD=((Total_interest_earned_ytd_count/Total_Count)*100), PREV_YR_INTEREST=((Total_prev_yr_interest_count/Total_Count)*100), TERM=((Total_term_count/Total_CD_Count)*100), OVERDRAFT_PROTECTION=((Total_overdraft_protection_count/Total_checking_Count)*100), SITE_ACCOUNT_STATUS_ID=((Total_site_account_status_id_count/Total_Count)*100), MATURITY_AMOUNT=((Total_maturity_amount_count/Total_CD_Count)*100), ANNUAL_PERCENT_YIELD=((Total_annual_percent_yield_count/Total_Count)*100) \n" +
"| fields acct_type_id, sum_info_id, ACCOUNT_NAME, ACCOUNT_TYPE_ID, ACCOUNT_HOLDER, ACCOUNT_NICKNAME_AT_SRC_SITE, ACCOUNT_NUMBER, ACCOUNT_OPEN_DATE, AVAILABLE_BALANCE, CURRENT_BALANCE, ROUTING_NUMBER, INTEREST_RATE, AS_OF_DATE, MATURITY_DATE, INTEREST_EARNED_YTD, PREV_YR_INTEREST, TERM, OVERDRAFT_PROTECTION, SITE_ACCOUNT_STATUS_ID, MATURITY_AMOUNT,ANNUAL_PERCENT_YIELD";
const bankTransactionQuery = "|search source=\"*/population/bank/transaction/*\" index=data_quality \n" +
"| search sum_info_id=# \n" +
"| stats sum(total_count) AS Total_Count, sum(plain_text_description_count) AS Total_plain_text_description_count, sum(trans_date_count) AS Total_trans_date_count, sum(transaction_amount_count) AS Total_amount_count, sum(transaction_base_type_id_count) AS Total_transaction_base_type_id_count, sum(transaction_type_id_count) AS Total_transaction_type_id_count, sum(mc_code_count) AS Total_mc_code_count, sum(transaction_category_count) AS Total_transaction_category_count, sum(merchant_id_count) AS Total_merchant_id_count, sum(running_balance_count) AS Total_running_balance_count,sum(post_date_count) AS Total_post_date_count,sum(check_number_count) AS Total_check_number_count by sum_info_id \n" +
"| eval PLAIN_TEXT_DESCRIPTION=((Total_plain_text_description_count/Total_Count)*100), TRANS_DATE=((Total_trans_date_count/Total_Count)*100), TRANSACTION_AMOUNT=((Total_amount_count/Total_Count)*100),TRANSACTION_BASE_TYPE_ID=((Total_transaction_base_type_id_count/Total_Count)*100), TRANSACTION_TYPE_ID=((Total_transaction_type_id_count/Total_Count)*100), MC_CODE=((Total_mc_code_count/Total_Count)*100), TRANSACTION_CATEGORY=((Total_transaction_category_count/Total_Count)*100), MERCHANT_ID=((Total_merchant_id_count/Total_Count)*100), RUNNING_BALANCE=((Total_running_balance_count/Total_Count)*100), POST_DATE=((Total_post_date_count/Total_Count)*100), CHECK_NUMBER=((Total_check_number_count/Total_Count)*100) \n" +
"| fields sum_info_id, PLAIN_TEXT_DESCRIPTION, TRANS_DATE,POST_DATE, TRANSACTION_AMOUNT,RUNNING_BALANCE, TRANSACTION_BASE_TYPE_ID, TRANSACTION_TYPE_ID, MC_CODE, TRANSACTION_CATEGORY, MERCHANT_ID,CHECK_NUMBER";
const cardAccountQuery = "|search source=\"*/population/card/account/*\" index=data_quality \n" +
"| search sum_info_id=# \n" +
"| stats sum(total_count) AS Total_Count , sum(eval(if((acct_type_id >2 OR acct_type_id != NULL),total_count,0))) as Total_acct_type_id_Count, sum(account_holder_count) AS Total_account_holder_count, sum(account_number_count) AS Total_account_number_count, sum(account_name_count) AS Total_account_name_count, sum(running_balance_count) AS Total_running_balance_count, sum(available_credit_count) AS Total_available_credit_count, sum(total_credit_line_count) AS Total_total_credit_line_count, sum(account_classification_count) AS Total_account_classification_count, sum(due_date_count) AS Total_due_date_count, sum(amount_due_count) AS Total_amount_due_count, sum(ending_balance_count) AS Total_ending_balance_count, sum(min_payment_count) AS Total_min_payment_count, sum(apr_count) AS Total_apr_count, sum(cash_apr_count) AS Total_cash_apr_count, sum(total_cash_limit_count) AS Total_total_cash_limit_count, sum(reward_balance_count) AS Total_reward_balance_count, sum(last_payment_date_count) AS Total_last_payment_date_count, sum(last_payment_count) AS Total_last_payment_count, sum(account_nickname_at_src_site_count) AS Total_account_nickname_at_src_site_count, sum(site_account_status_id_count) AS Total_site_account_status_id_count, sum(account_open_date_count) AS Total_account_open_date_count, sum(brand_name_count) AS Total_brand_name_count, sum(card_type_id_count) AS Total_card_type_id_count, sum(company_count) AS Total_company_count, sum(new_charges_count) AS Total_new_charges_count, sum(payments_count) AS Total_payments_count, sum(adjustments_count) AS Total_adjustments_count, sum(pending_charges_count) AS Total_pending_charges_count, sum(interest_paid_this_period_count) AS Total_interest_paid_this_period_count, sum(interest_paid_ytd_count) AS Total_interest_paid_ytd_count, sum(link_count) AS Total_link_count, sum(card_account_type_count) AS Total_card_account_type_count,sum(available_cash_count) as Total_avaialble_cash_count by sum_info_id, acct_type_id \n" +
"| eval ACCOUNT_HOLDER=((Total_account_holder_count/Total_Count)*100), ACCOUNT_NUMBER=((Total_account_number_count/Total_Count)*100), ACCOUNT_TYPE_ID=((Total_acct_type_id_Count/Total_Count)*100), ACCOUNT_NAME=((Total_account_name_count/Total_Count)*100), RUNNING_BALANCE=((Total_running_balance_count/Total_Count)*100), AVAILABLE_CREDIT=((Total_available_credit_count/Total_Count)*100), TOTAL_CREDIT_LINE=((Total_total_credit_line_count/Total_Count)*100), ACCOUNT_CLASSIFICATION=((Total_account_classification_count/Total_Count)*100), DUE_DATE=((Total_due_date_count/Total_Count)*100), AMOUNT_DUE=((Total_amount_due_count/Total_Count)*100), ENDING_BALANCE=((Total_ending_balance_count/Total_Count)*100), MIN_PAYMENT=((Total_min_payment_count/Total_Count)*100), APR=((Total_apr_count/Total_Count)*100), CASH_APR=((Total_cash_apr_count/Total_Count)*100), TOTAL_CASH_LIMIT=((Total_total_cash_limit_count/Total_Count)*100), REWARD_BALANCE=((Total_reward_balance_count/Total_Count)*100), LAST_PAYMENT_DATE=((Total_last_payment_date_count/Total_Count)*100), LAST_PAYMENT=((Total_last_payment_count/Total_Count)*100), ACCOUNT_NICKNAME_AT_SRC_SITE=((Total_account_nickname_at_src_site_count/Total_Count)*100), SITE_ACCOUNT_STATUS_ID=((Total_site_account_status_id_count/Total_Count)*100), ACCOUNT_OPEN_DATE=((Total_account_open_date_count/Total_Count)*100), BRAND_NAME=((Total_brand_name_count/Total_Count)*100), CARD_TYPE_ID=((Total_card_type_id_count/Total_Count)*100), COMPANY=((Total_company_count/Total_Count)*100), NEW_CHARGES=((Total_new_charges_count/Total_Count)*100), PAYMENTS=((Total_payments_count/Total_Count)*100), ADJUSTMENTS=((Total_adjustments_count/Total_Count)*100), PENDING_CHARGES=((Total_pending_charges_count/Total_Count)*100), INTEREST_PAID_THIS_PERIOD=((Total_interest_paid_this_period_count/Total_Count)*100), INTEREST_PAID_YTD=((Total_interest_paid_ytd_count/Total_Count)*100), LINK=((Total_link_count/Total_Count)*100), CARD_ACCOUNT_TYPE=((Total_card_account_type_count/Total_Count)*100),AVAILABLE_CASH=((Total_avaialble_cash_count/Total_Count)*100) \n" +
"| fields acct_type_id, sum_info_id, ACCOUNT_HOLDER, ACCOUNT_TYPE_ID, ACCOUNT_NUMBER, ACCOUNT_NAME, RUNNING_BALANCE, AVAILABLE_CREDIT, TOTAL_CREDIT_LINE, ACCOUNT_CLASSIFICATION, DUE_DATE, AMOUNT_DUE, ENDING_BALANCE, MIN_PAYMENT, APR, CASH_APR, TOTAL_CASH_LIMIT, REWARD_BALANCE, LAST_PAYMENT_DATE, LAST_PAYMENT, ACCOUNT_NICKNAME_AT_SRC_SITE, SITE_ACCOUNT_STATUS_ID, ACCOUNT_OPEN_DATE, BRAND_NAME, CARD_TYPE_ID, COMPANY, NEW_CHARGES, PAYMENTS, ADJUSTMENTS, PENDING_CHARGES, INTEREST_PAID_THIS_PERIOD, INTEREST_PAID_YTD, LINK, CARD_ACCOUNT_TYPE,AVAILABLE_CASH";
const cardStatementQuery = "|search source=\"*/population/card/statement/*\" index=data_quality \n" +
"| search sum_info_id=# \n" +
"| stats sum(total_count) AS Total_Count , sum(bill_date_count) AS Total_bill_date_count, sum(due_date_count) AS Total_due_date_count, sum(due_amount_count) AS Total_due_amount_count, sum(ending_balance_count) AS Total_ending_balance_count, sum(min_payment_count) AS Total_min_payment_count, sum(apr_count) AS Total_apr_count, sum(cash_apr_count) AS Total_cash_apr_count, sum(available_cash_count) AS Total_available_cash_count, sum(total_cash_limit_count) AS Total_total_cash_limit_count, sum(available_credit_count) AS Total_available_credit_count, sum(total_credit_line_count) AS Total_total_credit_line_count, sum(cash_advance_count) AS Total_cash_advance_count, sum(prev_due_count) AS Total_prev_due_count, sum(new_charges_count) AS Total_new_charges_count, sum(finance_charges_count) AS Total_finance_charges_count, sum(bill_period_start_date_count) AS Total_bill_period_start_date_count, sum(bill_period_end_date_count) AS Total_bill_period_end_date_count, sum(last_pay_date_count) AS Total_last_pay_date_count, sum(last_payment_count) AS Total_last_payment_count, sum(payments_count) AS Total_payments_count, sum(adjustments_count) AS Total_adjustments_count, sum(interest_paid_this_period_count) AS Total_interest_paid_this_period_count, sum(interest_paid_ytd_count) AS Total_interest_paid_ytd_count, sum(late_charges_count) AS Total_late_charges_count by sum_info_id \n" +
"| eval BILL_DATE=((Total_bill_date_count/Total_Count)*100), DUE_DATE=((Total_due_date_count/Total_Count)*100), DUE_AMOUNT=((Total_due_amount_count/Total_Count)*100), ENDING_BALANCE=((Total_ending_balance_count/Total_Count)*100), MIN_PAYMENT=((Total_min_payment_count/Total_Count)*100), APR=((Total_apr_count/Total_Count)*100), CASH_APR=((Total_cash_apr_count/Total_Count)*100), AVAILABLE_CASH=((Total_available_cash_count/Total_Count)*100), TOTAL_CASH_LIMIT=((Total_total_cash_limit_count/Total_Count)*100), AVAILABLE_CREDIT=((Total_available_credit_count/Total_Count)*100), TOTAL_CREDIT_LINE=((Total_total_credit_line_count/Total_Count)*100), CASH_ADVANCE=((Total_cash_advance_count/Total_Count)*100), PREV_DUE=((Total_prev_due_count/Total_Count)*100), NEW_CHARGES=((Total_new_charges_count/Total_Count)*100), FINANCE_CHARGES=((Total_finance_charges_count/Total_Count)*100), BILL_PERIOD_START_DATE=((Total_bill_period_start_date_count/Total_Count)*100), BILL_PERIOD_END_DATE=((Total_bill_period_end_date_count/Total_Count)*100), LAST_PAY_DATE=((Total_last_pay_date_count/Total_Count)*100), LAST_PAYMENT=((Total_last_payment_count/Total_Count)*100), PAYMENTS=((Total_payments_count/Total_Count)*100), ADJUSTMENTS=((Total_adjustments_count/Total_Count)*100), INTEREST_PAID_THIS_PERIOD=((Total_interest_paid_this_period_count/Total_Count)*100), INTEREST_PAID_YTD=((Total_interest_paid_ytd_count/Total_Count)*100), LATE_CHARGES=((Total_late_charges_count/Total_Count)*100) \n" +
"| fields sum_info_id, BILL_DATE, DUE_DATE, DUE_AMOUNT, ENDING_BALANCE, MIN_PAYMENT, APR, CASH_APR, AVAILABLE_CASH, TOTAL_CASH_LIMIT, AVAILABLE_CREDIT, TOTAL_CREDIT_LINE, CASH_ADVANCE, PREV_DUE, NEW_CHARGES, FINANCE_CHARGES, BILL_PERIOD_START_DATE, BILL_PERIOD_END_DATE, LAST_PAY_DATE, LAST_PAYMENT, PAYMENTS, ADJUSTMENTS, INTEREST_PAID_THIS_PERIOD, INTEREST_PAID_YTD, LATE_CHARGES";
const cardTransactionQuery = "|search source=\"*/population/card/transaction/*\" index=data_quality \n" +
"| search sum_info_id=# \n" +
"| stats sum(total_count) AS Total_Count , sum(plain_text_description_count) AS Total_plain_text_description_count, sum(trans_date_count) AS Total_trans_date_count, sum(post_date_count) AS Total_post_date_count, sum(transaction_amount_count) AS Total_transaction_amount_count, sum(transaction_base_type_id_count) AS Total_transaction_base_type_id_count, sum(transaction_type_id_count) AS Total_transaction_type_id_count, sum(mc_code_count) AS Total_mc_code_count, sum(source_transaction_type_count) AS Total_source_transaction_type_count, sum(date_count) AS Total_date_count, sum(running_balance_count) AS Total_running_balance_count, sum(transaction_category_count) AS Total_transaction_category_count, sum(merchant_id_count) AS Total_merchant_id_count, by sum_info_id \n" +
"| eval PLAIN_TEXT_DESCRIPTION=((Total_plain_text_description_count/Total_Count)*100), TRANS_DATE=((Total_trans_date_count/Total_Count)*100), POST_DATE=((Total_post_date_count/Total_Count)*100), TRANSACTION_AMOUNT=((Total_transaction_amount_count/Total_Count)*100), TRANSACTION_BASE_TYPE_ID=((Total_transaction_base_type_id_count/Total_Count)*100), TRANSACTION_TYPE_ID=((Total_transaction_type_id_count/Total_Count)*100), MC_CODE=((Total_mc_code_count/Total_Count)*100), SOURCE_TRANSACTION_TYPE=((Total_source_transaction_type_count/Total_Count)*100), DATE=((Total_date_count/Total_Count)*100), RUNNING_BALANCE=((Total_running_balance_count/Total_Count)*100), TRANSACTION_CATEGORY=((Total_transaction_category_count/Total_Count)*100), MERCHANT_ID=((Total_merchant_id_count/Total_Count)*100) \n" +
"| fields sum_info_id, PLAIN_TEXT_DESCRIPTION, TRANS_DATE, POST_DATE, TRANSACTION_AMOUNT, TRANSACTION_BASE_TYPE_ID, TRANSACTION_TYPE_ID, MC_CODE, SOURCE_TRANSACTION_TYPE, DATE, RUNNING_BALANCE, TRANSACTION_CATEGORY, MERCHANT_ID";
const investmentAccountQuery = "|search source=\"*/population/investment/account/*\" index=data_quality \n" +
"| search sum_info_id=# \n" +
"| stats sum(total_count) AS Total_Count, sum(eval(if((acct_type_id==9 OR acct_type_id==54 OR acct_type_id==61 OR acct_type_id==156 OR acct_type_id==84),total_count,0))) as Total_ESOP_Account,sum(eval(if((acct_type_id==12 OR acct_type_id==115),total_count,0))) as Total_Annuity_Account,sum(eval(if((acct_type_id >2),total_count,0))) as Total_acct_type_id_Count, sum(account_holder_count) AS Total_account_holder_count, sum(account_number_count) AS Total_account_number_count, sum(account_name_count) AS Total_account_name_count, sum(total_balance_count) AS Total_total_balance_count, sum(asof_date_count) AS Total_asof_date_count, \n" +
" sum(eval(if((acct_type_id==5 OR acct_type_id==6 OR acct_type_id==26 OR acct_type_id==27 OR acct_type_id==76 OR acct_type_id==157 OR acct_type_id==109 OR acct_type_id==110 OR acct_type_id==118 OR acct_type_id==119),cash_count,0))) AS Total_cash_count,sum(eval(if((acct_type_id==5 OR acct_type_id==6 OR acct_type_id==26 OR acct_type_id==27 OR acct_type_id==76 OR acct_type_id==157 OR acct_type_id==109 OR acct_type_id==110 OR acct_type_id==118 OR acct_type_id==119),margin_balance_count,0))) AS Total_margin_balance_count, sum(eval(if((acct_type_id==5 OR acct_type_id==6 OR acct_type_id==26 OR acct_type_id==27 OR acct_type_id==76 OR acct_type_id==157 OR acct_type_id==109 OR acct_type_id==110 OR acct_type_id==118 OR acct_type_id==119),money_market_balance_count,0))) AS Total_money_market_balance_count, sum(plan_name_count) AS Total_plan_name_count,sum(plan_number_count) AS Total_plan_number_count, sum(eval(if((acct_type_id==9 OR acct_type_id==54 OR acct_type_id==74 OR acct_type_id==61 OR acct_type_id==153 OR acct_type_id==75 OR acct_type_id==156 OR acct_type_id==159 OR acct_type_id==84),total_vested_balance_count,0))) AS Total_total_vested_balance_count, sum(eval(if((acct_type_id==9 OR acct_type_id==54 OR acct_type_id==74 OR acct_type_id==61 OR acct_type_id==153 OR acct_type_id==75 OR acct_type_id==156 OR acct_type_id==159 OR acct_type_id==84),total_unvested_balance_count,0))) AS Total_total_unvested_balance_count, sum(eval(if((acct_type_id==12 OR acct_type_id==115),annuity_balance_count,0))) AS Total_annuity_balance_count, sum(total_realized_gain_count) AS Total_total_realized_gain_count, sum(total_invested_amount_count) AS Total_total_invested_amount_count, sum(eval(if((acct_type_id==9 OR acct_type_id==112),loan_401k_count,0))) AS Total_loan_401k_count, sum(eval(if((acct_type_id==12 OR acct_type_id==115),annuity_death_benefit_count,0))) AS Total_annuity_death_benefit_count, sum(account_nickname_at_src_site_count) AS Total_account_nickname_at_src_site_count, sum(site_account_status_id_count) AS Total_site_account_status_id_count, \n" +
" sum(account_open_date_count) AS Total_account_open_date_count, sum(plan_option_type_id_count) AS Total_plan_option_type_id_count, sum(investment_account_type_count) AS Total_investment_account_type_count, sum(account_classification_count) AS Total_account_classification_count, \n" +
" sum(acc_num_or_acc_name_count) AS TOTAL_ACC_NUM_OR_ACC_NAME_COUNT, sum(eval(if((acct_type_id==9 OR acct_type_id==112),total_count,0))) as Total_401K_Count,\n" +
" sum(eval(if((acct_type_id==5 OR acct_type_id==6 OR acct_type_id==26 OR acct_type_id==27 OR acct_type_id==76 OR acct_type_id==157 OR acct_type_id==109 OR acct_type_id==110 OR acct_type_id==118 OR acct_type_id==119),total_count,0))) as Total_Brokerage_Count by sum_info_id \n" +
"| eval ACCOUNT_HOLDER=((Total_account_holder_count/Total_Count)*100), ACCOUNT_TYPE_ID=((Total_acct_type_id_Count/Total_Count)*100), ACCOUNT_NUMBER=((Total_account_number_count/Total_Count)*100), ACCOUNT_NAME=((Total_account_name_count/Total_Count)*100),TOTAL_BALANCE=((Total_total_balance_count/Total_Count)*100),ASOF_DATE=((Total_asof_date_count/Total_Count)*100),CASH=((Total_cash_count/Total_Brokerage_Count)*100), MARGIN_BALANCE=((Total_margin_balance_count/Total_Brokerage_Count)*100), MONEY_MARKET_BALANCE=((Total_money_market_balance_count/Total_Brokerage_Count)*100), PLAN_NAME=((Total_plan_name_count/Total_Count)*100), PLAN_NUMBER=((Total_plan_number_count/Total_Count)*100), TOTAL_VESTED_BALANCE=((Total_total_vested_balance_count/Total_ESOP_Account)*100), TOTAL_UNVESTED_BALANCE=((Total_total_unvested_balance_count/Total_ESOP_Account)*100),ANNUITY_BALANCE=((Total_annuity_balance_count/Total_Annuity_Account)*100), TOTAL_REALIZED_GAIN=((Total_total_realized_gain_count/Total_Count)*100), TOTAL_INVESTED_AMOUNT=((Total_total_invested_amount_count/Total_Count)*100), LOAN_401K=((Total_loan_401k_count/Total_401K_Count)*100), ANNUITY_DEATH_BENEFIT=((Total_annuity_death_benefit_count/Total_Annuity_Account)*100), ACCOUNT_NICKNAME_AT_SRC_SITE=((Total_account_nickname_at_src_site_count/Total_Count)*100),SITE_ACCOUNT_STATUS_ID=((Total_site_account_status_id_count/Total_Count)*100), ACCOUNT_OPEN_DATE=((Total_account_open_date_count/Total_Count)*100), PLAN_OPTION_TYPE_ID=((Total_plan_option_type_id_count/Total_Count)*100), INVESTMENT_ACCOUNT_TYPE=((Total_investment_account_type_count/Total_Count)*100), ACCOUNT_CLASSIFICATION=((Total_account_classification_count/Total_Count)*100), ACC_NUM_OR_ACC_NAME=((TOTAL_ACC_NUM_OR_ACC_NAME_COUNT/Total_Count)*100) \n" +
"| fields sum_info_id, ACCOUNT_HOLDER, ACCOUNT_TYPE_ID, ACCOUNT_NUMBER, ACCOUNT_NAME, TOTAL_BALANCE, ASOF_DATE, CASH, MARGIN_BALANCE, MONEY_MARKET_BALANCE, PLAN_NAME, PLAN_NUMBER, TOTAL_VESTED_BALANCE, TOTAL_UNVESTED_BALANCE, ANNUITY_BALANCE, TOTAL_REALIZED_GAIN, TOTAL_INVESTED_AMOUNT, LOAN_401K, ANNUITY_DEATH_BENEFIT, ACCOUNT_NICKNAME_AT_SRC_SITE, SITE_ACCOUNT_STATUS_ID, ACCOUNT_OPEN_DATE, PLAN_OPTION_TYPE_ID, INVESTMENT_ACCOUNT_TYPE, ACCOUNT_CLASSIFICATION, ACC_NUM_OR_ACC_NAME";
const investmentHoldingQuery = "|search source=\"*/population/investment/holding/*\" index=data_quality \n" +
"| search sum_info_id=#\n" +
"| stats sum(total_count) AS Total_Count,sum(eval(if((holding_type_id==13),total_count,0))) AS Total_ESOP_Count,sum(eval(if((holding_type_id !=3 AND holding_type_id !=4),total_count,0))) AS Total_Count_Without_CD_BOND, sum(description_count) AS Total_description_count, sum(price_count) AS Total_price_count, sum(eval(if((holding_type_id !=13),quantity_count,0))) AS Total_quantity_count, sum(value_count) AS Total_value_count, sum(eval(if((holding_type_id !=3 AND holding_type_id !=4),symbol_count,0))) AS Total_symbol_count, sum(cusip_number_count) AS Total_cusip_number_count, sum(cost_basis_count) AS Total_cost_basis_count, sum(sedol_count) AS Total_sedol_count, sum(isin_count) AS Total_isin_count, sum(domicile_count) AS Total_domicile_count, sum(daily_change_count) AS Total_daily_change_count, sum(employer_contribution_count) AS Total_employer_contribution_count, sum(employee_contribution_count) AS Total_employee_contribution_count, sum(stock_exchange_id_count) AS Total_stock_exchange_id_count, sum(linked_bank_account_number_count) AS Total_linked_bank_account_number_count, sum(percent_allocation_count) AS Total_percent_allocation_count, sum(percentage_change_count) AS Total_percentage_change_count, sum(price_as_of_date_count) AS Total_price_as_of_date_count, sum(realized_gain_count) AS Total_realized_gain_count, sum(asset_class_id_count) AS Total_asset_class_id_count, sum(fi_asset_class_count) AS Total_fi_asset_class_count, sum(par_value_count) AS Total_par_value_count, sum(eval(if((holding_type_id==3 OR holding_type_id==4),maturity_date_count,0))) AS Total_maturity_date_count, sum(eval(if((holding_type_id==3),coupon_rate_count,0))) AS Total_coupon_rate_count, sum(coupon_freq_id_count) AS Total_coupon_freq_id_count, sum(next_coupon_date_count) AS Total_next_coupon_date_count, sum(call_price_count) AS Total_call_price_count, sum(bond_class_id_count) AS Total_bond_class_id_count, sum(call_type_id_count) AS Total_call_type_id_count, sum(bond_type_id_count) AS Total_bond_type_id_count, sum(eval(if((holding_type_id==4),term_count,0))) AS Total_term_count, sum(face_value_count) AS Total_face_value_count, sum(eval(if((holding_type_id==4),interest_rate_count,0))) AS Total_interest_rate_count, sum(days_remaining_count) AS Total_days_remaining_count, sum(eval(if((holding_type_id==5 OR holding_type_id==13),expiration_date_count,0))) AS Total_expiration_date_count, sum(eval(if((holding_type_id==5 OR holding_type_id==13),strike_price_count,0))) AS Total_strike_price_count, sum(eval(if((holding_type_id==5),option_type_id_count,0))) AS Total_option_type_id_count, sum(company_name_count) AS Total_company_name_count, sum(stock_option_type_id_count) AS Total_stock_option_type_id_count, sum(eval(if((holding_type_id==13),grant_date_count,0))) AS Total_grant_date_count, sum(eval(if((holding_type_id==13),grant_number_count,0))) AS Total_grant_number_count, sum(vesting_date_count) AS Total_vesting_date_count, sum(eval(if((holding_type_id==13),vested_quantity_count,0))) AS Total_vested_quantity_count, sum(eval(if((holding_type_id==13),vested_value_count,0))) AS Total_vested_value_count, sum(eval(if((holding_type_id==13),unvested_value_count,0))) AS Total_unvested_value_count, sum(eval(if((holding_type_id==13),unvested_quantity_count,0))) AS Total_unvested_quantity_count, sum(eval(if((holding_type_id==13),vested_shares_ex_count,0))) AS Total_vested_shares_ex_count, sum(total_potential_value_count) AS Total_total_potential_value_count, sum(mutual_fund_type_id_count) AS Total_mutual_fund_type_id_count, sum(eval(if((holding_type_id==10 OR holding_type_id==11),contract_quantity_count,0))) AS Total_contract_quantity_count, sum(adjustment_factor_count) AS Total_adjustment_factor_count, sum(plan_name_count) AS Total_plan_name_count, sum(plan_number_count) AS Total_plan_number_count, sum(lot_size_count) AS Total_lot_size_count, sum(plan_option_type_id_count) AS Total_plan_option_type_id_count, sum(securities_id_count) AS Total_securities_id_count, sum(is_short_count) AS Total_is_short_count, sum(holding_type_count) AS Total_holding_type_count, sum(eval(if((holding_type_id==3),total_count,0))) AS Total_Bond_Count, sum(eval(if((holding_type_id ==3 OR holding_type_id ==4),total_count,0))) AS Total_Count_With_CD_BOND, sum(eval(if((holding_type_id ==10 OR holding_type_id ==11),total_count,0))) AS Total_Contract_Count,sum(eval(if((holding_type_id==4),total_count,0))) AS Total_CD_Count,sum(eval(if((holding_type_id==5),total_count,0))) AS Total_Option_Count,sum(eval(if((holding_type_id==5 OR holding_type_id==13),total_count,0))) AS Total_Count_With_Option_ESOP,sum(eval(if((holding_type_id!=13),total_count,0))) AS Total_Without_ESOP_Count by sum_info_id \n" +
"| eval DESCRIPTION=((Total_description_count/Total_Count)*100), PRICE=((Total_price_count/Total_Count)*100), QUANTITY=((Total_quantity_count/Total_Without_ESOP_Count)*100), VALUE=((Total_value_count/Total_Count)*100), SYMBOL=((Total_symbol_count/Total_Count_Without_CD_BOND)*100), CUSIP_NUMBER=((Total_cusip_number_count/Total_Count)*100), COST_BASIS=((Total_cost_basis_count/Total_Count)*100), SEDOL=((Total_sedol_count/Total_Count)*100), ISIN=((Total_isin_count/Total_Count)*100), DOMICILE=((Total_domicile_count/Total_Count)*100), DAILY_CHANGE=((Total_daily_change_count/Total_Count)*100), EMPLOYER_CONTRIBUTION=((Total_employer_contribution_count/Total_Count)*100), EMPLOYEE_CONTRIBUTION=((Total_employee_contribution_count/Total_Count)*100), STOCK_EXCHANGE_ID=((Total_stock_exchange_id_count/Total_Count)*100), LINKED_BANK_ACCOUNT_NUMBER=((Total_linked_bank_account_number_count/Total_Count)*100), PERCENT_ALLOCATION=((Total_percent_allocation_count/Total_Count)*100), PERCENTAGE_CHANGE=((Total_percentage_change_count/Total_Count)*100), PRICE_AS_OF_DATE=((Total_price_as_of_date_count/Total_Count)*100), REALIZED_GAIN=((Total_realized_gain_count/Total_Count)*100), ASSET_CLASS_ID=((Total_asset_class_id_count/Total_Count)*100), FI_ASSET_CLASS=((Total_fi_asset_class_count/Total_Count)*100), PAR_VALUE=((Total_par_value_count/Total_Count)*100), MATURITY_DATE=((Total_maturity_date_count/Total_Count_With_CD_BOND)*100), COUPON_RATE=((Total_coupon_rate_count/Total_Bond_Count)*100), COUPON_FREQ_ID=((Total_coupon_freq_id_count/Total_Count)*100), NEXT_COUPON_DATE=((Total_next_coupon_date_count/Total_Count)*100), CALL_PRICE=((Total_call_price_count/Total_Count)*100), BOND_CLASS_ID=((Total_bond_class_id_count/Total_Count)*100), CALL_TYPE_ID=((Total_call_type_id_count/Total_Count)*100), BOND_TYPE_ID=((Total_bond_type_id_count/Total_Count)*100), TERM=((Total_term_count/Total_CD_Count)*100), FACE_VALUE=((Total_face_value_count/Total_Count)*100), INTEREST_RATE=((Total_interest_rate_count/Total_CD_Count)*100), DAYS_REMAINING=((Total_days_remaining_count/Total_Count)*100), EXPIRATION_DATE=((Total_expiration_date_count/Total_Count_With_Option_ESOP)*100), STRIKE_PRICE=((Total_strike_price_count/Total_Count_With_Option_ESOP)*100), OPTION_TYPE_ID=((Total_option_type_id_count/Total_Option_Count)*100), COMPANY_NAME=((Total_company_name_count/Total_Count)*100), STOCK_OPTION_TYPE_ID=((Total_stock_option_type_id_count/Total_Count)*100), GRANT_DATE=((Total_grant_date_count/Total_ESOP_Count)*100), GRANT_NUMBER=((Total_grant_number_count/Total_ESOP_Count)*100), VESTING_DATE=((Total_vesting_date_count/Total_ESOP_Count)*100), VESTED_QUANTITY=((Total_vested_quantity_count/Total_ESOP_Count)*100), VESTED_VALUE=((Total_vested_value_count/Total_ESOP_Count)*100), UNVESTED_VALUE=((Total_unvested_value_count/Total_ESOP_Count)*100), UNVESTED_QUANTITY=((Total_unvested_quantity_count/Total_ESOP_Count)*100), VESTED_SHARES_EX=((Total_vested_shares_ex_count/Total_ESOP_Count)*100), TOTAL_POTENTIAL_VALUE=((Total_total_potential_value_count/Total_Count)*100), MUTUAL_FUND_TYPE_ID=((Total_mutual_fund_type_id_count/Total_Count)*100), CONTRACT_QUANTITY=((Total_contract_quantity_count/Total_Contract_Count)*100), ADJUSTMENT_FACTOR=((Total_adjustment_factor_count/Total_Count)*100), PLAN_NAME=((Total_plan_name_count/Total_Count)*100), PLAN_NUMBER=((Total_plan_number_count/Total_Count)*100), LOT_SIZE=((Total_lot_size_count/Total_Count)*100), PLAN_OPTION_TYPE_ID=((Total_plan_option_type_id_count/Total_Count)*100), SECURITIES_ID=((Total_securities_id_count/Total_Count)*100), IS_SHORT=((Total_is_short_count/Total_Count)*100), HOLDING_TYPE=((Total_holding_type_count/Total_Count)*100) \n" +
"| fields sum_info_id, DESCRIPTION, PRICE, QUANTITY, VALUE, SYMBOL, CUSIP_NUMBER, COST_BASIS, SEDOL, ISIN, DOMICILE, VESTED_VALUE,VESTED_QUANTITY,UNVESTED_VALUE,UNVESTED_QUANTITY, DAILY_CHANGE, EMPLOYER_CONTRIBUTION, EMPLOYEE_CONTRIBUTION, STOCK_EXCHANGE_ID, LINKED_BANK_ACCOUNT_NUMBER, PERCENT_ALLOCATION, PERCENTAGE_CHANGE, PRICE_AS_OF_DATE, REALIZED_GAIN, ASSET_CLASS_ID, FI_ASSET_CLASS, PAR_VALUE, MATURITY_DATE, COUPON_RATE, COUPON_FREQ_ID, NEXT_COUPON_DATE, CALL_PRICE, BOND_CLASS_ID, CALL_TYPE_ID, BOND_TYPE_ID, TERM, HOLDING_TYPE,FACE_VALUE,INTEREST_RATE,DAYS_REMAINING,HOLDING_TYPE,EXPIRATION_DATE,STRIKE_PRICE,OPTION_TYPE_ID,STOCK_OPTION_TYPE_ID,GRANT_DATE,GRANT_NUMBER,VESTING_DATE,VESTED_SHARES_EX,TOTAL_POTENTIAL_VALUE,PLAN_NUMBER,PLAN_OPTION_TYPE_ID,COMPANY_NAME,IS_SHORT,LOT_SIZE,SECURITIES_ID,PLAN_NAME,ADJUSTMENT_FACTOR,CONTRACT_QUANTITY,MUTUAL_FUND_TYPE_ID";
const investmentTransactionQuery = "|search source=\"*/population/investment/transaction/*\" index=data_quality \n" +
"| search sum_info_id=# \n" +
"| stats sum(total_count) AS Total_Count, sum(plain_text_description_count) AS Total_plain_text_description_count, sum(trans_date_count) AS Total_trans_date_count, sum(amount_count) AS Total_amount_count, sum(cusip_number_count) AS Total_cusip_number_count, sum(symbol_count) AS Total_symbol_count, sum(quantity_count) AS Total_quantity_count, sum(price_count) AS Total_price_count, sum(commission_count) AS Total_commission_count, sum(transaction_base_type_id_count) AS Total_transaction_base_type_id_count, sum(transaction_posting_order_count) AS Total_transaction_posting_order_count, sum(trade_date_count) AS Total_trade_date_count, sum(settle_date_count) AS Total_settle_date_count, sum(transaction_type_id_count) AS Total_transaction_type_id_count, sum(gross_amount_count) AS Total_gross_amount_count, sum(sec_fee_count) AS Total_sec_fee_count, sum(order_handling_fee_count) AS Total_order_handling_fee_count, sum(stamp_duty_count) AS Total_stamp_duty_count, sum(service_tax_count) AS Total_service_tax_count, sum(other_fees_count) AS Total_other_fees_count, sum(mc_code_count) AS Total_mc_code_count, sum(description_count) AS Total_description_count, sum(transaction_category_count) AS Total_transaction_category_count, sum(merchant_id_count) AS Total_merchant_id_count, sum(isin_count) AS Total_isin_count, sum(sedol_count) AS Total_sedol_count, by sum_info_id \n" +
"| eval PLAIN_TEXT_DESCRIPTION=((Total_plain_text_description_count/Total_Count)*100), TRANS_DATE=((Total_trans_date_count/Total_Count)*100), AMOUNT=((Total_amount_count/Total_Count)*100), CUSIP_NUMBER=((Total_cusip_number_count/Total_Count)*100), SYMBOL=((Total_symbol_count/Total_Count)*100), QUANTITY=((Total_quantity_count/Total_Count)*100), PRICE=((Total_price_count/Total_Count)*100), COMMISSION=((Total_commission_count/Total_Count)*100), TRANSACTION_BASE_TYPE_ID=((Total_transaction_base_type_id_count/Total_Count)*100), TRANSACTION_POSTING_ORDER=((Total_transaction_posting_order_count/Total_Count)*100), TRADE_DATE=((Total_trade_date_count/Total_Count)*100), SETTLE_DATE=((Total_settle_date_count/Total_Count)*100), TRANSACTION_TYPE_ID=((Total_transaction_type_id_count/Total_Count)*100), GROSS_AMOUNT=((Total_gross_amount_count/Total_Count)*100), SEC_FEE=((Total_sec_fee_count/Total_Count)*100), ORDER_HANDLING_FEE=((Total_order_handling_fee_count/Total_Count)*100), STAMP_DUTY=((Total_stamp_duty_count/Total_Count)*100), SERVICE_TAX=((Total_service_tax_count/Total_Count)*100), OTHER_FEES=((Total_other_fees_count/Total_Count)*100), MC_CODE=((Total_mc_code_count/Total_Count)*100), DESCRIPTION=((Total_description_count/Total_Count)*100), TRANSACTION_CATEGORY=((Total_transaction_category_count/Total_Count)*100), MERCHANT_ID=((Total_merchant_id_count/Total_Count)*100), ISIN=((Total_isin_count/Total_Count)*100), SEDOL=((Total_sedol_count/Total_Count)*100) \n" +
"| fields sum_info_id, PLAIN_TEXT_DESCRIPTION, TRANS_DATE, AMOUNT, CUSIP_NUMBER, SYMBOL, QUANTITY, PRICE, COMMISSION, TRANSACTION_BASE_TYPE_ID, TRANSACTION_POSTING_ORDER, TRADE_DATE, SETTLE_DATE, TRANSACTION_TYPE_ID, GROSS_AMOUNT, SEC_FEE, ORDER_HANDLING_FEE, STAMP_DUTY, SERVICE_TAX, OTHER_FEES, MC_CODE, DESCRIPTION, TRANSACTION_CATEGORY, MERCHANT_ID, ISIN, SEDOL";
const loanAccountQuery = "|search source=\"*/population/loan/account/*\" index=data_quality \n" +
"| search sum_info_id=# \n" +
"| stats sum(total_count) AS Total_Count,sum(account_name_count) AS Total_account_name_count,sum(account_holder_count) AS Total_account_holder_count, sum(account_nickname_at_src_site_count) AS Total_account_nickname_at_src_site_count, sum(account_number_count) AS Total_account_number_count,sum(account_open_date_count) AS Total_account_open_date_count, sum(account_close_date_count) AS Total_account_close_date_count, sum(account_holder_rate) AS Total_account_holder_rate_count, sum(amount_due_count) AS Total_amount_due_count, sum(eval(if((loan_type_id==7 OR loan_type_id==6),available_credit_count,0))) AS Total_available_credit_count, sum(available_loan_count) AS Total_available_loan_count, sum(eval(if((loan_type_id==7 OR loan_type_id==6),credit_limit_count,0))) AS Total_credit_limit_count, sum(description_count) AS Total_description_count, sum(as_of_date_count) AS Total_as_of_date_count,sum(due_date_count) AS Total_due_date_count, sum(eval(if((loan_type_id==3),escrow_balance_count,0))) AS Total_escrow_balance_count, sum(first_payment_date_count) AS Total_first_payment_date_count,sum(gurantor_count) AS Total_gurantor_count,sum(insurance_paid_last_year_count) AS Total_insurance_paid_last_year_count, ,sum(interest_paid_last_year_count) AS Total_interest_paid_last_year_count, sum(interest_paid_ytd_count) AS Total_interest_paid_ytd_count,sum(interest_rate_count) AS Total_interest_rate_count, sum(last_payment_amount_count) AS Total_last_payment_amount_count, sum(last_payment_date_count) AS Total_last_payment_date_count, sum(loan_term_count) AS Total_loan_term_count, sum(loan_type_count) AS Total_loan_type_count, sum(maturity_date_count) AS Total_maturity_date_count, sum(eval(if((loan_type_id==7 OR loan_type_id==6),min_payment_count,0))) AS Total_min_payment_count, sum(principle_balance_count) AS Total_principle_balance_count, sum(eval(if((loan_type_id==8 OR loan_type_id==9 OR loan_type_id==5 OR loan_type_id==4),recurring_payment_count,0))) AS Total_recurring_payment_count, sum(eval(if((loan_type_id==7 OR loan_type_id==6),total_count,0))) AS Total_loc_heloc_count, sum(eval(if((loan_type_id==3),total_count,0))) AS Total_mortgage_count, sum(eval(if((loan_type_id==8 OR loan_type_id==9 OR loan_type_id==5 OR loan_type_id==4),total_count,0))) AS Total_Auto_Student_Personal_Installment_count by sum_info_id \n" +
"| eval ACCOUNT_NAME=((Total_account_name_count/Total_Count)*100), ACCOUNT_HOLDER=((Total_account_holder_count/Total_Count)*100), ACCOUNT_NICKNAME_AT_SRC_SITE=((Total_account_nickname_at_src_site_count/Total_Count)*100), ACCOUNT_NUMBER=((Total_account_number_count/Total_Count)*100), ACCOUNT_OPEN_DATE=((Total_account_open_date_count/Total_Count)*100), ACCOUNT_CLOSE_DATE=((Total_account_close_date_count/Total_Count)*100), ACCOUNT_HOLDER_RATE=((Total_account_holder_rate_count/Total_Count)*100), AMOUNT_DUE=((Total_amount_due_count/Total_Count)*100), AVAILABLE_CREDIT=((Total_available_credit_count/Total_loc_heloc_count)*100), AVAILABLE_LOAN=((Total_available_loan_count/Total_Count)*100), CREDIT_LIMIT=((Total_credit_limit_count/Total_loc_heloc_count)*100), INTEREST_RATE=((Total_interest_rate_count/Total_Count)*100), AS_OF_DATE=((Total_as_of_date_count/Total_Count)*100), MATURITY_DATE=((Total_maturity_date_count/Total_acct_id_count)*100), DESCRIPTION=((Total_description_count/Total_Count)*100), DUE_DATE=((Total_due_date_count/Total_Count)*100),LOAN_TERM=((Total_loan_term_count/Total_Count)*100), ESCROW_BALANCE=((Total_escrow_balance_count/Total_mortgage_count)*100), FIRST_PAYMENT_DATE=((Total_first_payment_date_count/Total_Count)*100), GURANTOR=((Total_gurantor_count/Total_Count)*100), INSURANCE_PAID_LAST_YR=((Total_insurance_paid_last_year_count/Total_Count)*100), INTEREST_PAID_LAST_YR=((Total_interest_paid_last_year_count/Total_Count)*100), INTEREST_PAID_YTD=((Total_interest_paid_ytd_count/Total_Count)*100), LAST_PAYMENT_AMOUNT=((Total_last_payment_amount_count/Total_Count)*100), LAST_PAYMENT_DATE=((Total_last_payment_date_count/Total_Count)*100), LOAN_TYPE=((Total_loan_type_count/Total_Count)*100), MIN_PAYMENT=((Total_min_payment_count/Total_loc_heloc_count)*100), PRINCIPLE_BALANCE=((Total_principle_balance_count/Total_Count)*100),RECURRING_PAYMENT=((Total_recurring_payment_count/Total_Auto_Student_Personal_Installment_count)*100) \n" +
"| fields sum_info_id, ACCOUNT_NAME, ACCOUNT_HOLDER, ACCOUNT_NICKNAME_AT_SRC_SITE, ACCOUNT_NUMBER, ACCOUNT_OPEN_DATE, ACCOUNT_CLOSE_DATE, ACCOUNT_HOLDER_RATE, AMOUNT_DUE, AVAILABLE_CREDIT, AVAILABLE_LOAN, CREDIT_LIMIT, INTEREST_RATE, AS_OF_DATE, MATURITY_DATE, DESCRIPTION, DUE_DATE, LOAN_TERM, ESCROW_BALANCE, FIRST_PAYMENT_DATE, GURANTOR,INSURANCE_PAID_LAST_YR, INTEREST_PAID_LAST_YR, INTEREST_PAID_YTD, LAST_PAYMENT_AMOUNT, LAST_PAYMENT_DATE, LOAN_TYPE, MIN_PAYMENT, PRINCIPLE_BALANCE, RECURRING_PAYMENT";
const loanStatementQuery = "|search source=\"*/population/loan/statement/*\" index=data_quality \n" +
"| search sum_info_id=# \n" +
"| stats sum(total_count) AS Total_Count , sum(bill_date_count) AS Total_bill_date_count, sum(due_date_count) AS Total_due_date_count, sum(due_amount_count) AS Total_due_amount_count, sum(current_balance_count) AS Total_current_balance_count, sum(insurance_paid_ytd_count) AS Total_insurance_paid_ytd_count, sum(interest_amount_count) AS Total_interest_amount_count, sum(escrow_count) AS Total_escrow_count, sum(interest_paid_last_year_count) AS Total_interest_paid_last_year_count, sum(interest_paid_ytd_count) AS Total_interest_paid_ytd_count, sum(latecharges_due_count) AS Total_latecharges_due_count, sum(past_due_count) AS Total_past_due_count, sum(principal_amount_count) AS Total_principal_amount_count by sum_info_id \n" +
"| eval BILL_DATE=((Total_bill_date_count/Total_Count)*100), DUE_DATE=((Total_due_date_count/Total_Count)*100), DUE_AMOUNT=((Total_due_amount_count/Total_Count)*100), CURRENT_BALANCE=((Total_current_balance_count/Total_Count)*100), INSURANCE_PAID_YTD=((Total_insurance_paid_ytd_count/Total_Count)*100), INTEREST_AMOUNT=((Total_interest_amount_count/Total_Count)*100), ESCROW=((Total_escrow_count/Total_Count)*100), INTEREST_PAID_LAST_YEAR=((Total_interest_paid_last_year_count/Total_Count)*100), LATECHARGES_DUE=((Total_latecharges_due_count/Total_Count)*100), PAST_DUE=((Total_past_due_count/Total_Count)*100), PRINCIPAL_AMOUNT=((Total_principal_amount_count/Total_Count)*100), INTEREST_PAID_YTD=((Total_interest_paid_ytd_count/Total_Count)*100) \n" +
"| fields sum_info_id, BILL_DATE, DUE_DATE, DUE_AMOUNT, CURRENT_BALANCE, INSURANCE_PAID_YTD, INTEREST_AMOUNT, ESCROW, INTEREST_PAID_LAST_YEAR, LATECHARGES_DUE, PAST_DUE, PRINCIPAL_AMOUNT, INTEREST_PAID_YTD";
const loanTransactionQuery = "|search source=\"*/population/loan/transaction/*\" index=data_quality \n" +
"| search sum_info_id=# \n" +
"| stats sum(total_count) AS Total_Count, sum(plain_text_description_count) AS Total_plain_text_description_count, sum(trans_date_count) AS Total_trans_date_count, sum(transaction_type_id_count) AS Total_transaction_type_id_count, sum(mc_code_count) AS Total_mc_code_count, sum(transaction_category_count) AS Total_transaction_category_count, sum(merchant_id_count) AS Total_merchant_id_count, sum(running_balance_count) AS Total_running_balance_count,sum(post_date_count) AS Total_post_date_count,sum(amount_count) AS Total_amount_count,sum(interest_count) AS Total_interest_count,sum(principal_count) AS Total_principal_count,sum(transaction_posting_order_count) AS Total_transaction_posting_order_count by sum_info_id \n" +
"| eval PLAIN_TEXT_DESCRIPTION=((Total_plain_text_description_count/Total_Count)*100), TRANS_DATE=((Total_trans_date_count/Total_Count)*100),TRANSACTION_AMOUNT=((Total_amount_count/Total_Count)*100),TRANSACTION_TYPE_ID=((Total_transaction_type_id_count/Total_Count)*100), MC_CODE=((Total_mc_code_count/Total_Count)*100), TRANSACTION_CATEGORY=((Total_transaction_category_count/Total_Count)*100), MERCHANT_ID=((Total_merchant_id_count/Total_Count)*100), RUNNING_BALANCE=((Total_running_balance_count/Total_Count)*100), POST_DATE=((Total_post_date_count/Total_Count)*100), INTEREST=((Total_interest_count/Total_Count)*100), PRINCIPAL=((Total_principal_count/Total_Count)*100), TRANSACTION_POSTING_ORDER=((Total_transaction_posting_order_count/Total_Count)*100) \n" +
"| fields sum_info_id, PLAIN_TEXT_DESCRIPTION, TRANS_DATE,POST_DATE, TRANSACTION_AMOUNT,RUNNING_BALANCE, TRANSACTION_TYPE_ID, MC_CODE, TRANSACTION_CATEGORY, MERCHANT_ID,INTEREST,PRINCIPAL,TRANSACTION_POSTING_ORDER";
const insuranceAccountQuery = "|search source=\"*/population/insurance/account/*\" index=data_quality \n" +
"| search sum_info_id=# \n" +
"| stats sum(total_count) AS Total_Count,sum(eval(if((insurance_type_id==6),total_count,0))) AS Total_Annuities_Balance_count,\n" +
" sum(eval(if((insurance_type_id==5),total_count,0))) AS Total_Life_Insurance_count,\n" +
" sum(eval(if((insurance_type_id==2 OR insurance_type_id==4),total_count,0))) AS Total_Auto_Home_Insurance_count,\n" +
" sum(eval(if((insurance_type_id==3 OR insurance_type_id==2),total_count,0))) AS Total_Health_Auto_Insurance_count,\n" +
" sum(eval(if((insurance_type_id==5 OR insurance_type_id==6),total_count,0))) AS Total_Annuities_Life_Insurance_count,\n" +
" sum(account_holder_count) AS Total_account_holder_count,sum(account_name_count) AS Total_account_name_count,sum(account_number_count) AS Total_account_number_count,\n" +
" sum(account_open_date_count) AS Total_account_open_date_count,sum(eval(if((insurance_type_id==6),annuity_count,0))) AS Total_Annuity_count,sum(as_of_count) AS Total_AsOf_count,\n" +
" sum(cash_value_count) AS Total_Cash_Value_count,sum(eval(if((insurance_type_id==5 OR insurance_type_id==6),death_benefit_count,0))) AS Total_Death_Benefit_count,\n" +
" sum(eval(if((insurance_type_id==3 OR insurance_type_id==2),deductible_count,0))) AS Total_Deductible_count,\n" +
" sum(dividend_count) AS Total_Dividend_count,sum(expiration_date_count) AS Total_Expiration_Date_count,\n" +
" sum(eval(if((insurance_type_id==5),face_amount_count,0))) AS Total_Face_Amount_count,\n" +
" sum(eval(if((insurance_type_id==5),group_name_count,0))) AS Total_Group_Name_count,sum(eval(if((insurance_type_id==5),group_number_count,0))) AS Total_Group_Number_count,\n" +
" sum(installment_amount_count) AS Total_Installment_Amount_count,\n" +
" sum(last_payment_date_count) AS Total_Last_Payment_Date_count,sum(minimum_amount_due_count) AS Total_Minimum_Amount_Due_count, sum(policy_term_count) AS Total_Policy_Term_count,\n" +
" sum(premium_amount_count) AS Total_Premium_Amount_count,sum(eval(if((insurance_type_id==2 OR insurance_type_id==4),remaining_balance_count,0))) AS Total_Remaining_Balance_count,\n" +
" sum(surrender_charge_count) AS Total_Surrender_Charge_count by sum_info_id \n" +
"| eval ACCOUNT_HOLDER=((Total_account_holder_count/Total_Count)*100),\n" +
" ACCOUNT_NUMBER=((Total_account_number_count/Total_Count)*100), ACCOUNT_NAME=((Total_account_name_count/Total_Count)*100), AS_OF=((Total_AsOf_count/Total_Count)*100), \n" +
" ANNUITY=((Total_Annuity_count/Total_Annuities_Balance_count)*100), ACCOUNT_OPEN_DATE=((Total_account_open_date_count/Total_Count)*100), \n" +
" CASH_VALUE=((Total_Cash_Value_count/Total_Count)*100), DEATH_BENEFIT_AMOUNT=((Total_Death_Benefit_count/Total_Annuities_Life_Insurance_count)*100),\n" +
" DEDUCTIBLE=((Total_Deductible_count/Total_Health_Auto_Insurance_count)*100), DIVIDEND=((Total_Dividend_count/Total_Count)*100),\n" +
" EXPIRATION_DATE=((Total_Expiration_Date_count/Total_Count)*100),FACE_AMOUNT=((Total_Face_Amount_count/Total_Life_Insurance_count)*100),\n" +
" GROUP_NAME=((Total_Group_Name_count/Total_Life_Insurance_count)*100),GROUP_NUMBER=((Total_Group_Number_count/Total_Life_Insurance_count)*100),\n" +
" INSTALLMENT_AMOUNT=((Total_Installment_Amount_count/Total_Count)*100),LAST_PAYMENT_DATE=((Total_Last_Payment_Date_count/Total_Count)*100),\n" +
" MINIMUM_AMOUNT_DUE=((Total_Minimum_Amount_Due_count/Total_Count)*100),POLICY_TERM=((Total_Policy_Term_count/Total_Count)*100),\n" +
" PREMIUM_AMOUNT=((Total_Premium_Amount_count/Total_Count)*100),REMAINING_BALANCE=((Total_Remaining_Balance_count/Total_Auto_Home_Insurance_count)*100), \n" +
" SURRENDER_CHARGE=((Total_Surrender_Charge_count/Total_Count)*100) \n" +
"| fields sum_info_id, ACCOUNT_HOLDER,ACCOUNT_NUMBER,ACCOUNT_NAME,ACCOUNT_OPEN_DATE,AS_OF,ANNUITY,\n" +
" CASH_VALUE,DEATH_BENEFIT_AMOUNT,DEDUCTIBLE,DIVIDEND,EXPIRATION_DATE,FACE_AMOUNT,GROUP_NAME,GROUP_NUMBER,INSTALLMENT_AMOUNT,LAST_PAYMENT_DATE,MINIMUM_AMOUNT_DUE,\n" +
" POLICY_TERM,PREMIUM_AMOUNT,REMAINING_BALANCE,SURRENDER_CHARGE";
const insuranceStatementQuery = "|search source=\"*/population/insurance/statement/*\" index=data_quality \n" +
"| search sum_info_id=# \n" +
"| stats sum(total_count) AS Total_Count , sum(bill_date_count) AS Total_Bill_Date_Count, sum(due_date_count) AS Total_Due_Date_Count, sum(due_amount_count) AS Total_Due_Amount_Count by sum_info_id \n" +
"| eval BILL_DATE=((Total_Bill_Date_Count/Total_Count)*100), DUE_DATE=((Total_Due_Date_Count/Total_Count)*100), DUE_AMOUNT=((Total_Due_Amount_Count/Total_Count)*100) \n" +
"| fields sum_info_id, BILL_DATE, DUE_DATE, DUE_AMOUNT";
const insuranceTransactionQuery = "|search source=\"*/population/insurance/transaction/*\" index=data_quality \n" +
"| search sum_info_id=# \n" +
"| stats sum(total_count) AS Total_Count, sum(amount_count) AS Total_Amount_Count, sum(date_count) AS Total_Date_Count, sum(merchant_id_count) AS Total_MerchantID_Count, sum(plain_text_description_count) AS Total_Plain_Text_Description_Count, sum(post_date_count) AS Total_Post_Date_Count, sum(running_balance_count) AS Total_Running_Balance_Count, sum(trans_date_count) AS Total_Trans_Date_Count, sum(transaction_category_count) AS Total_Trans_Category_Count, sum(transaction_id_count) AS Total_TransID_Count, sum(transaction_status_id) AS Total_Trans_Status_Count, sum(transaction_type_id_count) AS Total_Trans_Type_ID_Count by sum_info_id \n" +
"| eval AMOUNT=((Total_Amount_Count/Total_Count)*100), DATE=((Total_Date_Count/Total_Count)*100),MERCHANT_ID=((Total_MerchantID_Count/Total_Count)*100),PLAIN_TEXT_DESCRIPTION=((Total_Plain_Text_Description_Count/Total_Count)*100),POST_DATE=((Total_Post_Date_Count/Total_Count)*100),RUNNING_BALANCE=((Total_Running_Balance_Count/Total_Count)*100),TRANS_DATE=((Total_Trans_Date_Count/Total_Count)*100),TRANSACTION_CATEGORY=((Total_Trans_Category_Count/Total_Count)*100),TRANSACTION_ID=((Total_TransID_Count/Total_Count)*100),TRANSACTION_STATUS_ID=((Total_Trans_Status_Count/Total_Count)*100),TRANSACTION_TYPE_ID=((Total_Trans_Type_ID_Count/Total_Count)*100) \n" +
"| fields sum_info_id,\n" +
" AMOUNT,DATE,MERCHANT_ID,PLAIN_TEXT_DESCRIPTION,POST_DATE,RUNNING_BALANCE,TRANS_DATE,TRANSACTION_CATEGORY,TRANSACTION_ID,TRANSACTION_STATUS_ID,TRANSACTION_TYPE_ID";
module.exports = {
bankAccountQuery,
bankTransactionQuery,
cardAccountQuery,
cardStatementQuery,
cardTransactionQuery,
loanAccountQuery,
loanStatementQuery,
loanTransactionQuery,
investmentAccountQuery,
investmentHoldingQuery,
investmentTransactionQuery,
insuranceAccountQuery,
insuranceStatementQuery,
insuranceTransactionQuery
};