"""
@autor:Yue
@date::14:04
@file:dealData.py
@project:appQutoInfo

"""
from blueprints.settings import  testConfig,qa3Config

import pymysql,re,json,base64
from urllib.parse import urlencode,unquote
import html

def delDatabase(config,sql,db=None):
    dbhost=config.host
    dbport=config.port
    dbusername=config.user
    dbpw=config.pw
    dbtype=config.type
    dbcharset=config.charset
    db=db or config.db
    try:
        if dbtype=="mysql":
            db=pymysql.connect(host=dbhost,port=dbport,charset=dbcharset,user=dbusername,password=dbpw,db=db)
            cursor= db.cursor()

            #try:
            sql=html.unescape(sql)

            # except:
            #     pass
            cursor.execute(sql)
            if 'update' in sql or 'insert' in sql:
                db.commit()

            date=cursor.fetchall()
            temp=()
            #print("==", date, temp, len(date), sql)
            if len(date)>0:
                for i in date:
                    temp+=i
                date=temp

            #print("==",date,temp,len(date),sql)
            tt=[]

            if date:
                #date=[bytes(i).decode() for i in date if isinstance(i,bytes) ]

                for i in date:

                    i=bytes(i).decode() if isinstance(i,bytes) else i
                    if isinstance(i,str):
                        if i=='0.00' or i=='0.0':
                            i='0'
                        i=i.replace('"',"'")
                    tt.append(i)
                #print("===",tt)
                return tt
            #print("===="*10,date,sql,dbport)
            return list(date) if date else [0]
        raise Exception("暂时不支持mysql以外的数据库处理")
    except Exception as e:
        print("数据库执行出现错误,db:{0}>>sql:{1}".format(db,sql),e)


def dealParamSql(sqll,colum=None,recolum=None,revalue=None):
    '''

    :param sqll: sql语句
    :param colum: sql中的字段
    :param recolum: 根据recolum，找到revalue对应的值
    :param revalue: 替换值
    :return:
    通过keydata配置文件，找到revalue，再根据recolum，找到值。然后通过colum将%s替换成上一步骤的值
    eg：
    sqll="select * from aa where a =%s"
    colum=a
    recolum=b
    revalue=b=2

    return :
    select * from aa where a =2
    '''

    c=re.findall(r"%s",sqll)
    sql=sqll.strip()
    #print(sqll,'-colum:', colum,'-recolum:', recolum,'-', revalue)
    #print("++++",recolum,revalue,type(revalue))
    if isinstance(revalue,str):
        if '&' in revalue:
            revalue=re.findall(r"{recolum}=(.+?)&".format(recolum=recolum),revalue)

        else:
            #print("2"*10)
            revalue=re.findall(r"{recolum}=(.*)".format(recolum=recolum),revalue)
            #print("2" * 10,revalue)


    #print("****",colum,revalue,c)

    if len(c)>0 and len(revalue)>0:
        #替代的部分数据可能存在空格，所以需要做判断

        revalue=revalue[0]
        #print(revalue,colum)
        if sql.find("{0}=%s".format(colum))>=0:
            #print('1========',revalue,sql)
            return sql.replace("{0}=%s".format(colum),"{0}={1}".format(colum,revalue),1)

        elif sql.find("{0} =%s".format(colum))>=0:
            #print('2========', recolum, revalue, sql)
            return sql.replace("{0} =%s".format(colum), "{0}={1}".format(colum, revalue),1)

        elif sql.find("{0}= %s".format(colum))>=0:
            #print('3========', recolum, revalue, sql)
            return sql.replace("{0}= %s".format(colum), "{0}={1}".format(colum, revalue),1)

        elif sql.find("{0} = %s".format(colum))>=0:
            #print('4========', recolum, revalue, sql)
            return sql.replace("{0} = %s".format(colum), "{0}={1}".format(colum, revalue),1)
        elif sql.find("{0}='%s'".format(colum))>=0:

            #print('5========', colum,revalue, sql)

            return sql.replace("{0}='%s'".format(colum),"{0}={1}".format(colum,revalue),1)
        else:
            pass
            #raise Exception("tool.delDatabase替换失败，sql中不存在{0}".format(colum))

    return sql



if __name__ == '__main__':
    # d=delDatabase(qaConfig,"select * from xyqb_user.user_detail   where phone_no='13683240127'",db="xyqb")
    # print(d)
    #sql="select p.`pay_amount`,r.`loan_application_history_id`,r.`repayment_plan_id`,a.`merchant_id`,p.`ext_data`,p.`apply_at`,p.`business_flow_no`, b.`phone_no`,d.`registered_from`,c.id,b.`id_no`,b.`name`,r.`term_no` from xyqb.pay_order p inner join xyqb.repayment_record  r inner join payment_center.`repay_order` a inner join xyqb_user.user_detail b inner join xyqb.repayment_order c inner join xyqb_user.user d on p.`pay_order_no`=r.`repayment_plan_id` and a.`business_flow_no`=p.`business_flow_no` and p.`user_id`=b.user_id=d.id and r.`loan_application_history_id`=c.`loan_application_history_id` order by rand() limit 1;",


    #sql='select * from xyqb.quota_credit limit 1;'
    #print(dealParamSql(sql,'phone_no','phone','1'))

    t=delDatabase(qa3Config,'select  concat(sum(if(b.repayment_status =3,a.principal,0))) from xyqb.repayment_record a join xyqb.repayment_plan b on a.loan_application_history_id=b.loan_application_history_id and a.repayment_plan_id=b.id where a.loan_application_history_id=17;')

    print(t)









