import pandas as pd
from data.datasource import mysqldb,mongodb
import time
from dateutil.relativedelta import relativedelta   
import datetime

'''
model instructions : established a dhb obj which cotains attrubutes of dhb model

Params Constructor:
    _init__(self,features=None,sql=None,start_time_period=None,end_time_period=None)

API :
    .features - default features
    .start_time_period
    .end_time_period
    .dhb_features_extract() extract dhb features
    .dhb_comparasion(start_time_period = self.start_time_period, end_time_period = self.end_time_period, applied_type = None,applied_from = None)

'''


class dhb:
    # features as Series format
    #features = pd.read_excel()
    
    sql  = '''
    select dhb_last_30_and_60_days_dun_call_avg_duration,
    dhb_last_30_and_60_days_dun_call_duration_above60,
    dhb_last_30_and_60_days_dun_call_duration_below15,
    dhb_last_30_and_60_days_dun_call_duration_between15_and_30,
    dhb_last_30_and_60_days_dun_call_in_duration,
    dhb_last_30_and_60_days_dun_call_in_times,
    dhb_last_30_and_60_days_dun_call_out_duration,
    dhb_last_30_and_60_days_dun_call_out_times,
    dhb_last_30_and_60_days_dun_call_tel_total_nums,
    dhb_last_30_and_60_days_dun_call_total_duration,
    dhb_last_30_and_60_days_dun_call_total_times,
    dhb_last_30_and_60_days_ntdun_call_avg_duration,
    dhb_last_30_and_60_days_ntdun_call_duration_above60,
    dhb_last_30_and_60_days_ntdun_call_duration_below15,
    dhb_last_30_and_60_days_ntdun_call_duration_between15_and_30,
    dhb_last_30_and_60_days_ntdun_call_in_duration,
    dhb_last_30_and_60_days_ntdun_call_in_times,
    dhb_last_30_and_60_days_ntdun_call_out_duration,
    dhb_last_30_and_60_days_ntdun_call_out_times,
    dhb_last_30_and_60_days_ntdun_call_tel_total_nums,
    dhb_last_30_and_60_days_ntdun_call_total_duration,
    dhb_last_30_and_60_days_ntdun_call_total_times,
    dhb_last_30_days_dun_call_avg_duration,
    dhb_last_30_days_dun_call_duration_above60,
    dhb_last_30_days_dun_call_duration_below15,
    dhb_last_30_days_dun_call_duration_between15_and_30,
    dhb_last_30_days_dun_call_in_duration,
    dhb_last_30_days_dun_call_in_times,
    dhb_last_30_days_dun_call_out_duration,
    dhb_last_30_days_dun_call_out_times,
    dhb_last_30_days_dun_call_tel_total_nums,
    dhb_last_30_days_dun_call_total_duration,
    dhb_last_30_days_dun_call_total_times,
    dhb_last_30_days_ntdun_call_avg_duration,
    dhb_last_30_days_ntdun_call_duration_above60,
    dhb_last_30_days_ntdun_call_duration_below15,
    dhb_last_30_days_ntdun_call_duration_between15_and_30,
    dhb_last_30_days_ntdun_call_in_duration,
    dhb_last_30_days_ntdun_call_in_times,
    dhb_last_30_days_ntdun_call_out_duration,
    dhb_last_30_days_ntdun_call_out_times,
    dhb_last_30_days_ntdun_call_tel_total_nums,
    dhb_last_30_days_ntdun_call_total_duration,
    dhb_last_30_days_ntdun_call_total_times,
    dhb_last_60_and_90_days_dun_call_avg_duration,
    dhb_last_60_and_90_days_dun_call_duration_above60,
    dhb_last_60_and_90_days_dun_call_duration_below15,
    dhb_last_60_and_90_days_dun_call_duration_between15_and_30,
    dhb_last_60_and_90_days_dun_call_in_duration,
    dhb_last_60_and_90_days_dun_call_in_times,
    dhb_last_60_and_90_days_dun_call_out_duration,
    dhb_last_60_and_90_days_dun_call_out_times,
    dhb_last_60_and_90_days_dun_call_tel_total_nums,
    dhb_last_60_and_90_days_dun_call_total_duration,
    dhb_last_60_and_90_days_dun_call_total_times,
    dhb_last_60_and_90_days_ntdun_call_avg_duration,
    dhb_last_60_and_90_days_ntdun_call_duration_above60,
    dhb_last_60_and_90_days_ntdun_call_duration_below15,
    dhb_last_60_and_90_days_ntdun_call_duration_between15_and_30,
    dhb_last_60_and_90_days_ntdun_call_in_duration,
    dhb_last_60_and_90_days_ntdun_call_in_times,
    dhb_last_60_and_90_days_ntdun_call_out_duration,
    dhb_last_60_and_90_days_ntdun_call_out_times,
    dhb_last_60_and_90_days_ntdun_call_tel_total_nums,
    dhb_last_60_and_90_days_ntdun_call_total_duration,
    dhb_last_60_and_90_days_ntdun_call_total_times,
    dhb_last_three_weeks_dun_call_avg_duration,
    dhb_last_three_weeks_dun_call_duration_above60,
    dhb_last_three_weeks_dun_call_duration_below15,
    dhb_last_three_weeks_dun_call_duration_between15_and_30,
    dhb_last_three_weeks_dun_call_in_duration,
    dhb_last_three_weeks_dun_call_in_times,
    dhb_last_three_weeks_dun_call_out_duration,
    dhb_last_three_weeks_dun_call_out_times,
    dhb_last_three_weeks_dun_call_tel_total_nums,
    dhb_last_three_weeks_dun_call_total_duration,
    dhb_last_three_weeks_dun_call_total_times,
    dhb_last_three_weeks_ntdun_call_avg_duration,
    dhb_last_three_weeks_ntdun_call_duration_above60,
    dhb_last_three_weeks_ntdun_call_duration_below15,
    dhb_last_three_weeks_ntdun_call_duration_between15_and_30,
    dhb_last_three_weeks_ntdun_call_in_duration,
    dhb_last_three_weeks_ntdun_call_in_times,
    dhb_last_three_weeks_ntdun_call_out_duration,
    dhb_last_three_weeks_ntdun_call_out_times,
    dhb_last_three_weeks_ntdun_call_tel_total_nums,
    dhb_last_three_weeks_ntdun_call_total_duration,
    dhb_last_three_weeks_ntdun_call_total_times,
    dhb_last_two_weeks_dun_call_avg_duration,
    dhb_last_two_weeks_dun_call_duration_above60,
    dhb_last_two_weeks_dun_call_duration_below15,
    dhb_last_two_weeks_dun_call_duration_between15_and_30,
    dhb_last_two_weeks_dun_call_in_duration,
    dhb_last_two_weeks_dun_call_in_times,
    dhb_last_two_weeks_dun_call_out_duration,
    dhb_last_two_weeks_dun_call_out_times,
    dhb_last_two_weeks_dun_call_tel_total_nums,
    dhb_last_two_weeks_dun_call_total_duration,
    dhb_last_two_weeks_dun_call_total_times,
    dhb_last_two_weeks_ntdun_call_avg_duration,
    dhb_last_two_weeks_ntdun_call_duration_above60,
    dhb_last_two_weeks_ntdun_call_duration_below15,
    dhb_last_two_weeks_ntdun_call_duration_between15_and_30,
    dhb_last_two_weeks_ntdun_call_in_duration,
    dhb_last_two_weeks_ntdun_call_in_times,
    dhb_last_two_weeks_ntdun_call_out_duration,
    dhb_last_two_weeks_ntdun_call_out_times,
    dhb_last_two_weeks_ntdun_call_tel_total_nums,
    dhb_last_two_weeks_ntdun_call_total_duration,
    dhb_last_two_weeks_ntdun_call_total_times,
    dhb_last_week_dun_call_avg_duration,
    dhb_last_week_dun_call_duration_above60,
    dhb_last_week_dun_call_duration_below15,
    dhb_last_week_dun_call_duration_between15_and_30,
    dhb_last_week_dun_call_in_duration, dhb_last_week_dun_call_in_times,
    dhb_last_week_dun_call_out_duration,
    dhb_last_week_dun_call_out_times,
    dhb_last_week_dun_call_tel_total_nums,
    dhb_last_week_dun_call_total_duration,
    dhb_last_week_dun_call_total_times,
    dhb_last_week_ntdun_call_avg_duration,
    dhb_last_week_ntdun_call_duration_above60,
    dhb_last_week_ntdun_call_duration_below15,
    dhb_last_week_ntdun_call_duration_between15_and_30,
    dhb_last_week_ntdun_call_in_duration,
    dhb_last_week_ntdun_call_in_times,
    dhb_last_week_ntdun_call_out_duration,
    dhb_last_week_ntdun_call_out_times,
    dhb_last_week_ntdun_call_tel_total_nums,
    dhb_last_week_ntdun_call_total_duration,
    dhb_last_week_ntdun_call_total_times,
    dhb_overview_dun_call_avg_duration,
    dhb_overview_dun_call_duration_above60,
    dhb_overview_dun_call_duration_below15,
    dhb_overview_dun_call_duration_between15_and_30,
    dhb_overview_dun_call_in_duration, dhb_overview_dun_call_in_times,
    dhb_overview_dun_call_out_duration, dhb_overview_dun_call_out_times,
    dhb_overview_dun_call_tel_total_nums,
    dhb_overview_dun_call_total_duration,
    dhb_overview_dun_call_total_times, dhb_overview_dun_first_call_time,
    dhb_overview_dun_last_call_time,
    dhb_overview_ntdun_call_avg_duration,
    dhb_overview_ntdun_call_duration_above60,
    dhb_overview_ntdun_call_duration_below15,
    dhb_overview_ntdun_call_duration_between15_and_30,
    dhb_overview_ntdun_call_in_duration,
    dhb_overview_ntdun_call_in_times,
    dhb_overview_ntdun_call_out_duration,
    dhb_overview_ntdun_call_out_times,
    dhb_overview_ntdun_call_tel_total_nums,
    dhb_overview_ntdun_call_total_duration,
    dhb_overview_ntdun_call_total_times,
    dhb_overview_ntdun_first_call_time,
    dhb_overview_ntdun_last_call_time,applied_at,applied_from,applied_type,if(passdue_day>15,1,0) as target
    from risk_analysis
    where applied_at >= '@start_time_period' and applied_at < '@end_time_period'
    and transacted = 1
    and dhb_flag =1
    and datediff(now(),deadline) > 15
'''
    

    
    
    
    
    
#    sql = '''
#    
#    '''
    start_time_period = (datetime.date.today() - relativedelta(months=+7)).strftime("%Y-%m-%d 00:00:00")
    end_time_period = (datetime.date.today() - relativedelta(days=+17)).strftime("%Y-%m-%d 00:00:00")
    
    def __init__(self,features=None,sql=None,start_time_period=None,end_time_period=None):
        try:
            # if the para was not Series
            if(type(features) != pd.core.series.Series):
                self.features =pd.Series(features)
            else:
                self.features = features
        except Exception as e:
            print("'features' parameter type Error, it should be list or Series")
            raise
            
        if sql != None:
            self.sql = sql
        if start_time_period != None:
            self.start_time_period = start_time_period
        if end_time_period != None:
            self.end_time_period = end_time_period


    def dhb_features_extract(self):
        
        value_map = {
        "近3天":1,
        "近4-5天":2,
        "近6-7天":3,
        "近8-15天":4,
        "近16-30天":5,
        "近31-60天":6,
        "近61-90天":7,
        "近91-120天":8,
        "近121-150天":9,
        "近151-180天":10,
        "180天前":11,
        "无":0  
        }
          
        #print(self.sql.replace('@start_time_period',self.start_time_period).replace('@end_time_period',self.end_time_period))
        # use risk_analysis to extract data
        print('-----get dhb features from risk_analysis---',datetime.datetime.now())
        cnt=self.cnt_samples()
        print('-----samples number is %d ' % cnt['cnt'][0])
        res = []
        tmp=pd.read_sql(
            self.sql.replace('@start_time_period', self.start_time_period).replace('@end_time_period',
                                                                                   self.end_time_period),
            mysqldb.engine_risk_analysis, chunksize=10000)
        for tt in tmp:
            res.append(tt)
        dhb_loan=pd.concat(res)
        cols=["dhb_overview_dun_first_call_time", "dhb_overview_dun_last_call_time","dhb_overview_ntdun_first_call_time", "dhb_overview_ntdun_last_call_time"]
        dhb_loan[cols] = dhb_loan[cols].applymap(lambda x : value_map[x])
        
        dhb_loan.loc[dhb_loan.dhb_last_60_and_90_days_ntdun_call_avg_duration >= 42,"dhb_last_60_and_90_days_ntdun_call_avg_duration"] = 42
        dhb_loan.loc[dhb_loan.dhb_overview_ntdun_call_duration_above60 >= 25,"dhb_overview_ntdun_call_duration_above60"] = 25
        dhb_loan.loc[dhb_loan.dhb_last_30_and_60_days_ntdun_call_total_duration>= 800,"dhb_last_30_and_60_days_ntdun_call_total_duration"] = 800
        dhb_loan.loc[dhb_loan.dhb_last_30_and_60_days_dun_call_in_duration >= 1600,"dhb_last_30_and_60_days_dun_call_in_duration"] = 1600
        dhb_loan.loc[dhb_loan.dhb_last_30_days_ntdun_call_total_duration>= 2500,"dhb_last_30_days_ntdun_call_total_duration"] = 2500
        dhb_loan.loc[dhb_loan.dhb_last_30_days_ntdun_call_tel_total_nums>= 25,"dhb_last_30_days_ntdun_call_tel_total_nums"] = 25
        dhb_loan.loc[dhb_loan.dhb_last_30_days_dun_call_in_duration >= 1000,"dhb_last_30_days_dun_call_in_duration"] = 1000
        dhb_loan.loc[dhb_loan.dhb_overview_ntdun_call_total_duration >= 3000,"dhb_overview_ntdun_call_total_duration"] = 3000
        dhb_loan.loc[dhb_loan.dhb_overview_ntdun_call_in_times>= 25,"dhb_overview_ntdun_call_in_times"] = 25
        dhb_loan.loc[dhb_loan.dhb_last_60_and_90_days_ntdun_call_in_duration>= 1000,"dhb_last_60_and_90_days_ntdun_call_in_duration"] = 1000
        dhb_loan.loc[dhb_loan.dhb_overview_dun_call_tel_total_nums>= 22,"dhb_overview_dun_call_tel_total_nums"] = 22
        dhb_loan.loc[dhb_loan.dhb_last_30_days_dun_call_total_duration>= 1100,"dhb_last_30_days_dun_call_total_duration"] = 1100
        dhb_loan.loc[dhb_loan.dhb_last_two_weeks_ntdun_call_in_duration>= 300,"dhb_last_two_weeks_ntdun_call_in_duration"] = 300
        
        dhb_loan.to_csv("./dhb_loan_sample——"+str(datetime.date.today())+".csv")
        print( time.strftime('%Y.%m.%d %H:%M:%S',time.localtime(time.time())) +"提取了dhb "+ self.start_time_period + "to" + self.end_time_period + "时段样本")
        return dhb_loan

    '''
    instructions : build a comparasion

    Params :
        df - test dataset which was given
        score - score column
        target - label
        start_time_period - 
        end_time_period - 
        applied_tpye - 
        applied_from - 

    Returns :
        auc comparasion
        liftchart plot 
    '''
    # def dhb_comparasion(df,score = 'model_exec_data_source#dhb' ,target = 'target', start_time_period = self.start_time_period, end_time_period = self.end_time_period, applied_type = None, applied_from = None):
    #     df_mongo = mongodb.pymongodb(start_time_period, end_time_period, limit, "{'order_id':1,'model_exec_data_source#dhb':1}")
    #     df = pd.merge(df,df_mongo,how='left',left_on='order_no',right_on='order_id')

    #     df['bins'] = df.qcut(df['target'], q = 10, percision = 6, dupulicates='drop')
    #     df.groupby
    #     return 1
    def cnt_samples(self):
        sql = '''
            select count(1) as cnt
            from risk_analysis
            where applied_at >= '%s' and applied_at < '%s'
            and transacted = 1
            and dhb_flag =1
            and datediff(now(),deadline) > 15
        ''' % (self.start_time_period,self.end_time_period)
        return pd.read_sql(sql,mysqldb.engine_risk_analysis)






    
        

        
