import time,datetime
import requests,json
from selenium import webdriver
from libs.op_keyboard import op_keyboard
from xlrd import open_workbook
from xlutils.copy import copy
from selenium.webdriver.support.select import Select
from selenium.webdriver.chrome.options import Options
from libs.DealResource import *
from libs.MysqlDB import *
op = op_keyboard()
from openpyxl import load_workbook

class LoanSystem():
    '''借贷系统后台的相关操作'''
    def __init__(self):
        os.system("taskkill /f /t /im 360chrome.exe") #初始化关闭360极速浏览器
        os.system("taskkill /f /t /im chromedriver.exe") #初始化关闭360极速浏览器
        # self.driver = webdriver.Chrome()
        __browser_url = read_resource("${__browser_url}")
        chrome_options = Options()
        chrome_options.binary_location = __browser_url
        # 保留浏览器默认配置，第一次安装浏览器的缓存位置
        chrome_options.add_argument(r"user-data-dir=%s" % read_resource("${add_argument}"))
        self.driver = webdriver.Chrome(chrome_options=chrome_options)
        self.driver.maximize_window()
        self.driver.implicitly_wait(20)
        self.driver.get(read_resource("${urlpath_loan}") + "/login.html")
    def loanlogin(self):
        '''登录借款系统'''
        self.driver.find_element_by_id("loginAccount").send_keys(read_resource("${loanusername}"))
        self.driver.find_element_by_id("loginPassword").send_keys(read_resource("${loanuserpassword}"))
        self.driver.find_element_by_xpath("//button[text()='登 录']").click()
        time.sleep(2)
    def IntoEntry(self,phone,idcard,name,rate,month,money,jkname):
        '''借款系统进件'''
        db = MysqlDB()
        phe = read_resource("${theborrowerphone}")
        cif_no = db.select_return_A_data("SELECT cif_no FROM qtw_invest_db.p2p_user_reg_info WHERE cif_account = '%s'" % phe)
        db = MysqlDB()
        debtamount = db.select_return_A_data("SELECT SUM(t.`apply_amt`/100) FROM p2p_pact_issue t WHERE t.`cif_no`='%s' AND t.`bid_state` IN ('3','6')" % cif_no)[0]
        if debtamount == None:
            print("已借款金额0元")
        else:
            print("已借款金额%s" % debtamount)
            if 200000 - float(debtamount) < float(month):
                raise Exception("您当前的借款账户已经借款%s元，最大借款总额20万")
        self.driver.find_element_by_xpath('//div[@class="navbar-custom-menu"]//li[2]').click()              #点击【借款管理】一级目录
        time.sleep(1)
        self.driver.find_element_by_xpath('//section[@class="sidebar"]//span[text()="借款管理"]').click()   #点击【借款管理】二级目录
        time.sleep(1)
        self.driver.find_element_by_xpath('//a[@class="tlevel" and @menu-id="45"]').click()                 #【借款管理】点击【进件管理】
        time.sleep(1)
        #处理excel
        current_path = os.path.abspath(__file__)
        resoursepath = os.path.join(os.path.abspath(os.path.dirname(current_path) + os.path.sep + ".."),'resourse\\loansysinto.xls')
        resoursepath = resoursepath.replace("\\", "/")
        print(resoursepath)
        #D:/PyWorkSpace/rob_frame/qutouwang/data_testenv/loansysinto.xlsx
        #D:/PyWorkSpace/QTWselenium/resourse/loansysinto.xlsx

        rb = open_workbook(resoursepath)
        # 通过sheet_by_index()获取的sheet没有write()方法
        rs = rb.sheet_by_index(0)
        wb = copy(rb)
        ws = wb.get_sheet(0)
        ws.write(1, 1,idcard)
        ws.write(1, 2,jkname)
        ws.write(1, 9,name)
        ws.write(1, 22,phone)
        ws.write(1, 5,rate)
        ws.write(1, 6,month)
        ws.write(1, 3,money)
        wb.save(resoursepath)
        self.driver.find_element_by_xpath('//*[@id="exportFile"]').send_keys(resoursepath)  # 【导入】进件
        time.sleep(2)
        #关闭alert
        web_alert = self.driver.switch_to.alert
        web_alert.accept()
        time.sleep(1)
        #根据姓名查找,保存进件编号
        self.driver.find_element_by_xpath('//*[@id="searchForm"]/div[1]/div[2]/div/input').send_keys(read_resource("${theborrowername}"))
        element = self.driver.find_element_by_xpath('//*[@id="searchBtn"]')
        self.driver.execute_script("$(arguments[0]).click()", element)
        time.sleep(2)
        entry_code = self.driver.find_element_by_xpath('//*[@id="returnPlanTable"]/tbody/tr/td[1]').text
        print('进件编号:' + entry_code)
        write_resourse("${entry_code}",entry_code)         #将进件编号写进resourse

    def editdebtCode(self):
        '''借贷系统—进件管理—编辑'''
        # 借款管理主目录
        entrycode = read_resource("${entry_code}") #读取进件编号
        self.driver.find_element_by_xpath('//div[@class="navbar-custom-menu"]//li[2]').click() # 点击借款管理一级目录
        time.sleep(1)
        self.driver.find_element_by_xpath('//section[@class="sidebar"]//span[text()="借款管理"]').click()   #点击借款管理二级目录
        time.sleep(1)
        #进件管理
        self.driver.find_element_by_xpath('//a[@class="tlevel" and @menu-id="45"]').click()
        time.sleep(1)
        self.driver.find_element_by_xpath('//*[@id="searchForm"]/div[1]/div[1]/div/input').send_keys(entrycode)
        self.driver.find_element_by_xpath('//button[@id="searchBtn"]').click()
        time.sleep(2)
        self.driver.find_element_by_xpath('//*[@id="returnPlanTable"]/tbody/tr/td[12]/span').click()   #编辑
        time.sleep(1)
        #编辑页面
        self.driver.find_element_by_xpath('//*[@id="sele125"]').send_keys("借款人能够履行合同，能够按时足额偿还借款人借款本息。还款意愿良好，收入、稳定性等各方面状况良好")
        self.driver.find_element_by_xpath('//*[@id="sele1"]').send_keys("收入良好")
        self.driver.find_element_by_xpath('//*[@id="sele2"]').send_keys("收入稳定无异常")
        self.driver.find_element_by_xpath('//*[@id="sele3"]').send_keys("信用资质良好，无贷款")
        self.driver.find_element_by_xpath('//*[@id="sele4"]').send_keys("借款人收入偿还")
        self.driver.find_element_by_xpath('//*[@id="sele5"]').send_keys("抵押房产变现")
        #编辑页面上传图片
        time.sleep(1)
        current_path = os.path.abspath(__file__)
        path = os.path.abspath(os.path.dirname(current_path) + os.path.sep + "..")
        self.driver.find_element_by_xpath('//*[@id="excelFile"]').send_keys(path+"\\resourse\\id.png")
        time.sleep(1)
        self.driver.find_element_by_xpath('//*[@id="excelFile2"]').send_keys(path+"\\resourse\\card.png")
        time.sleep(1)
        self.driver.find_element_by_xpath('//*[@id="excelFile3"]').send_keys(path+"\\resourse\\zx.png")
        time.sleep(1)
        #提交审核
        self.driver.find_element_by_xpath('//*[@id="submit_listEdite"]').click()
        #alert弹窗
        time.sleep(2)
        a1 = self.driver.switch_to.alert  # 通过switch_to.alert切换到alert
        a1.accept()
        time.sleep(1)
        print("借款系统—进件列表，编辑成功")

    def chechdebtCode(self):
        '''借款系统—审核列表'''
        entrycode = read_resource("${entry_code}") #读取进件编号
        self.driver.find_element_by_xpath('//div[@class="navbar-custom-menu"]//li[2]').click() # 点击借款管理一级目录
        time.sleep(1)
        self.driver.find_element_by_xpath('//section[@class="sidebar"]//span[text()="借款管理"]').click()   #点击借款管理二级目录
        time.sleep(1)
        #审核管理
        self.driver.find_element_by_xpath('//a[text()=" 审核列表"]').click()
        time.sleep(1)
        self.driver.find_element_by_xpath('//input[@class="form-control input-sm"]').send_keys(entrycode)  #进件编号查询
        self.driver.find_element_by_xpath('//button[@id="searchBtn"]').click()
        time.sleep(2)
        self.driver.find_element_by_xpath('//span[@class="btn btn-primary btn-sm fa fa-pencil-square-o item-update editEntry"]').click() #查询结果点击审核
        time.sleep(1)
        self.driver.find_element_by_xpath('//*[@id="audit_list_EditeYes"]').click()  #提交审核
        time.sleep(2)
        # 选择垫付机构
        e = self.driver.find_element_by_id("cifName")
        s = Select(e)
        s.select_by_value(read_resource("${thepaymentname}"))
        time.sleep(1)
        self.driver.find_element_by_xpath('//*[@id="doUpdateUser3"]').click()
        time.sleep(1)
        print("借款系统—审核列表,审核成功")
    def loan_fk(self):
        '''满标后借贷系统放款'''
        entrycode = read_resource("${entry_code}")  #读取进件编号
        self.driver.find_element_by_id("7").click()
        time.sleep(1)
        self.driver.find_element_by_xpath('//*[@class="slevel"]').click()   #点击财务管理-放款列表
        time.sleep(1)
        self.driver.find_element_by_xpath('//*[@data-gh-route="make_list"]').click()
        time.sleep(2)
        # 【借款端后台-放款】点击财务管理-放款列表-查看满标的借款编号
        self.driver.find_element_by_xpath('//*[@id="searchForm"]/div[1]/div[1]/div/input').send_keys(entrycode)
        element = self.driver.find_element_by_xpath('//*[@id="searchBtn"]')
        self.driver.execute_script("$(arguments[0]).click()", element)
        # 【借款端后台-放款】点击财务管理-放款列表-查看满标的借款编号-点击放款
        time.sleep(2)
        self.driver.find_element_by_xpath('//*[@id="returnPlanTable"]/tbody/tr/td[13]/span[1]').click()
        time.sleep(2)
        # 【借款端后台-放款】点击财务管理-放款列表-查看满标的借款编号-点击放款-alert弹窗
        a1 = self.driver.switch_to.alert  # 通过switch_to.alert切换到alert
        time.sleep(1)
        print(a1.text)  # text属性输出alert的文本
        a1.accept()  # alert“确认”
        time.sleep(1)
        for i in range(50):
            bd = MysqlDB()
            loan_status = bd.select_return_A_data("SELECT loan_status FROM qtw_loan_db.p2p_loan_return_main_info WHERE loan_no = '%s';"%read_resource("${entry_code}"))[0]
            if loan_status == "00":
                print("借款系统，放款成功")
                break
            time.sleep(5)

    def overdueoneday(self,term_num):
        '''逾期一天'''
        entrycode = read_resource("${entry_code}")  # 读取进件编号
        debtCode = entrycode
        print("==========逾期1天申请垫付===========")
        should_date = time.strftime('%Y-%m-%d', time.localtime())   #读取当天时间
        should_ymd = should_date.split("-")
        should_date_1 = self.getday(y=int(should_ymd[0]), m=int(should_ymd[1]), d=int(should_ymd[2]), n=-1)  #时间更改为1天前
        #更改数据，使还款计划的第term_num期逾期
        db = MysqlDB()
        db.sql_update('UPDATE qtw_loan_db.p2p_borrower_return_plan SET should_date = "%s" WHERE debtcode = "%s" AND term_num = "%s"'%(should_date_1,debtCode,term_num))
        print("==================第一步，逾期申请==================")
        # 根据债权信息查询【申请列表】oid
        for i in range(60):
            db = MysqlDB()
            oid = db.select_return_A_data('SELECT oid FROM qtw_loan_db.p2p_exceed_time_return WHERE debtcode = "%s" AND term_num = "%s"' %(debtCode,term_num))
            if oid != None:
                break
            time.sleep(5)

    def applyReport(self):
        '''申请垫付'''
        # 借贷系统登录，申请垫付
        time.sleep(2)
        entrycode = read_resource("${entry_code}")  # 读取进件编号
        self.driver.find_element_by_xpath('//*[@id="8"]').click()  # 点击还款管理一级目录
        time.sleep(1)
        self.driver.find_element_by_xpath('//section[@class="sidebar"]//span[text()="还款管理"]').click()  # 点击还款管理二级目录
        time.sleep(1)
        # 审核管理
        self.driver.find_element_by_xpath('//*[@id="sidebarMenu"]/li[2]/ul/li[3]/a').click()
        time.sleep(1)
        self.driver.find_element_by_xpath('//*[@id="searchForm"]/div[1]/div[2]/div/input').send_keys(entrycode)
        element = self.driver.find_element_by_xpath('//*[@id="searchBtn"]')
        self.driver.execute_script("$(arguments[0]).click()", element)
        time.sleep(1)
        self.driver.find_element_by_xpath('//*[@id="returnPlanTable"]/tbody/tr/td[17]/a').click()      #点击申请
        # alert弹窗
        a1 = self.driver.switch_to.alert  # 通过switch_to.alert切换到alert
        time.sleep(2)
        a1.accept()
        time.sleep(2)
        #DD操作
        op.click(740, 700)  # 密码输入框
        for i in read_resource("${thepaymentpassword}"):
            op.dd(i)
        op.click(740, 860)  # 确认按钮
        time.sleep(10)
    def padPay(self):
        '''执行垫付'''
        entrycode = read_resource("${entry_code}")  # 读取进件编号
        self.driver.find_element_by_xpath('//*[@id="8"]').click()  # 点击还款管理一级目录
        time.sleep(2)
        self.driver.find_element_by_xpath('//section[@class="sidebar"]//span[text()="还款管理"]').click()  # 点击还款管理二级目录
        time.sleep(2)
        self.driver.find_element_by_xpath('//*[@id="sidebarMenu"]/li[2]/ul/li[4]/a').click()
        time.sleep(2)
        self.driver.find_element_by_xpath('//*[@id="searchForm"]/div[1]/div[2]/div/input').send_keys(entrycode)
        element = self.driver.find_element_by_xpath('//*[@id="searchBtn"]')
        self.driver.execute_script("$(arguments[0]).click()", element)
        time.sleep(2)
        self.driver.find_element_by_xpath('//*[@id="returnPlanTable"]/tbody/tr/td[17]/span').click()  # 点击申请
        # alert弹窗
        a1 = self.driver.switch_to.alert  # 通过switch_to.alert切换到alert
        time.sleep(2)
        a1.accept()
        time.sleep(2)
        for i in range(10):
            db = MysqlDB()
            status = db.select_return_A_data("SELECT status FROM qtw_loan_db.p2p_exceed_time_return WHERE debtcode = '%s'" % entrycode)[0]
            if status == '03':
                print("垫付成功")
                break
            time.sleep(30)


    def overduemanyday(self,term_num,days):
        print("==================借款人逾期8天，生成罚息数据==================")
        # 时间更改为8天前
        entrycode = read_resource("${entry_code}")  # 读取进件编号
        debtCode = entrycode
        should_date = time.strftime('%Y-%m-%d', time.localtime())
        should_ymd = should_date.split("-")
        should_date_8 = self.getday(y=int(should_ymd[0]), m=int(should_ymd[1]), d=int(should_ymd[2]), n=int("-"+str(days)))
        print("第二期还款日期向今天前推8天的时间%s" % should_date_8)
        # 更改数据，使还款计划的第二期逾期8天
        db = MysqlDB()
        db.sql_update('UPDATE qtw_loan_db.p2p_borrower_return_plan SET should_date = "%s" WHERE debtcode = "%s" AND term_num = "%s"' % (should_date_8, debtCode, term_num))
        db = MysqlDB()
        db.sql_update("UPDATE qtw_loan_db.p2p_exceed_time_return SET expect_back_date = '%s',exceed_date = '%s' WHERE debtcode = '%s' AND term_num = %s;" % (should_date_8,should_date,debtCode, term_num))
        punish_amt = 0
        if days >=8:
            for i in range(100):
                # 查询罚息的值
                db = MysqlDB()
                punish_amt = db.select_return_A_data('SELECT punish_amt FROM qtw_loan_db.p2p_exceed_time_return WHERE debtcode = "%s" AND term_num = "%s"' % (debtCode,term_num))
                punish_amt = str(punish_amt[0])
                if int(punish_amt) > 0:
                    print("逾期之后生成罚息，耗时%s分钟"%(i//2))
                    break
                time.sleep(5)
            if int(punish_amt) == 0:
                    raise Exception("逾期之后没有生成罚息")
        else:
            time.sleep(30)
        db = MysqlDB()
        db.sql_update('UPDATE qtw_loan_db.p2p_loan_return_main_info SET next_return_date = "%s" WHERE loan_no = "%s"' % (should_date_8, debtCode))
        time.sleep(3)
        ###展示还款按钮
         #

    def getday(self,y=2017,m=8,d=15,n=0):
        the_date = datetime.datetime(y,m,d)
        result_date = the_date + datetime.timedelta(days=n)
        d = result_date.strftime('%Y-%m-%d')
        return d


    def close(self):
        self.driver.close()


if __name__=="__main__":
    #登录借款系统
    # a  = LoanSystem()
    # a.loanlogin()
    # # 进件
    # a.IntoEntry()
    # # a.loan_fk()
    # a.padPay()
    # entrycode = "jj2019090600005"
    cif_no = "19090468418923"
    db = MysqlDB()
    debtamount = db.select_return_A_data("SELECT SUM(t.`apply_amt`/100) FROM p2p_pact_issue t WHERE t.`cif_no`='%s' AND t.`bid_state` IN ('3','6')" % cif_no)[0]
    print(debtamount == None)
    print("已借款金额%s" % debtamount)
    # # #审核
    # # entry = 'jj2019013100003'
    # # a.check(entry)
    # f = LoanSystem()  # 登录借贷系统
    # f.loan_fk()  # 放款
    # f.close()