GA-ERP-SYS-Auto-WEN/D2201.py

import concurrent.futures
import requests
from concurrent.futures import ThreadPoolExecutor, as_completed

from sqlalchemy import Column,String,create_engine
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base
import pyodbc

import pandas as pd

import csv



import requests
import json
import rsa
import binascii
import json
import csv
import pandas as pd
import datetime
import random
import sys
import time
from bs4 import BeautifulSoup
import re
from PyQt5.QtCore import QThread, pyqtSignal    
from PyQt5.QtWidgets import (QApplication, QDialog,
                             QProgressBar, QPushButton,QLabel)
from PyQt5 import QtWidgets
from PyQt5.QtCore import *
from sqlalchemy import Column,String,create_engine
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base
import pyodbc
import os
import logging
import traceback


import atexit 
FORMAT = '%(asctime)s %(levelname)s: %(message)s'
logging.basicConfig(level=logging.INFO, filename='myLog.log', filemode='w', format=FORMAT)


Base = declarative_base()
class Logdata(Base):
        __tablename__ = 'LOGDB'

        資料庫 = Column(String(20), primary_key=True)
        IP位置 = Column(String(20))
        帳號 = Column(String(20))
        密碼 = Column(String(20))
class User(Base):

        __tablename__ = '客戶資料表'

        客戶 = Column(String(20), primary_key=True)
        資料庫 = Column(String(20))
        IP位置 = Column(String(20))
        帳號 = Column(String(20))
        密碼 = Column(String(20))
class DateF(Base):
        __tablename__ =  '查詢時間'
    
        開始=Column(String(20), primary_key=True)
        結束=Column(String(20))
        是否全導=Column(String(20))
class Someoneupdate(Base):
    __tablename__ = '自動導入判斷'
    正在自動導入中= Column(String(20), primary_key=True)
    
def loadCSV():
    try:
        with open('000.csv', newline='') as csvfile:

            rows = csv.reader(csvfile)
            csvvrow=[]
            for row in rows:
                csvvrow.append(row)
        coustomtxt=(csvvrow[1][0])
        return coustomtxt    
    except:
        traceback.print_exc()     # 捕捉異常，並將異常傳播資訊輸出控制檯
        traceback.print_exc(file=open('log.txt', 'a')) 
        logging.debug('debug message',exc_info=True)
        logging.info('info message',exc_info=True)
        logging.warning('warning message',exc_info=True)
        logging.error('error message',exc_info=True)
        logging.critical('critical message',exc_info=True)
        app1 = QtWidgets.QApplication([])

        error_dialog = QtWidgets.QErrorMessage()
        error_dialog.setWindowTitle('ERROR')
        error_dialog.showMessage('無法取得000.csv')

        app1.exec_()     
def connectEngine(coustomtxt,engine):
    try:
        DB_session = sessionmaker(engine)
        db_session = DB_session()
        comDbUserData=db_session.query(User).filter(User.客戶.like(coustomtxt)).all()

        UserDbName=comDbUserData[0].資料庫
        UserIPName=comDbUserData[0].IP位置
        UserAdName=comDbUserData[0].帳號
        UserPdName=comDbUserData[0].密碼

        engine3 = create_engine('mssql+pyodbc://'+UserAdName+':'+UserPdName+'@'+UserIPName+'/'+UserDbName+'?driver=SQL+Server+Native+Client+11.0')
        return engine3  
    except:
        traceback.print_exc()     # 捕捉異常，並將異常傳播資訊輸出控制檯
        traceback.print_exc(file=open('log.txt', 'a')) 
        logging.debug('debug message',exc_info=True)
        logging.info('info message',exc_info=True)
        logging.warning('warning message',exc_info=True)
        logging.error('error message',exc_info=True)
        logging.critical('critical message',exc_info=True)
        app1 = QtWidgets.QApplication([])
        error_dialog = QtWidgets.QErrorMessage()
        error_dialog.setWindowTitle('ERROR')
        error_dialog.showMessage('資料連線失敗')
        app1.exec_()        
def DateDataLoad(engine3,db_session2):
    try:
            #engine3=connectEngine()

            Datefind=db_session2.query(DateF).all() 
            startdatekey=Datefind[0].開始
            enddatekey=Datefind[0].結束
            allpart=Datefind[0].是否全導
            return startdatekey,enddatekey,allpart
    except:
        traceback.print_exc()     # 捕捉異常，並將異常傳播資訊輸出控制檯
        traceback.print_exc(file=open('log.txt', 'a')) 
        logging.debug('debug message',exc_info=True)
        logging.info('info message',exc_info=True)
        logging.warning('warning message',exc_info=True)
        logging.error('error message',exc_info=True)
        logging.critical('critical message',exc_info=True)
        app1 = QtWidgets.QApplication([])
        error_dialog = QtWidgets.QErrorMessage()
        error_dialog.setWindowTitle('ERROR')
        error_dialog.showMessage('導入日期資料失敗')
        app1.exec_()
def LoadCustomData(engine3,allpart):
    try:

        if (allpart=="0"):
            text3="SELECT TOP(1000)  網址, 帳號, 密碼 FROM  商客基本資料表"
        else:
            text3="SELECT TOP(1000)  網址, 帳號, 密碼 FROM  商客資料暫存檔"

        loopper2=pd.read_sql(text3,engine3) 

        contforloop=len(loopper2)
        loopper2=loopper2.values
        print(loopper2)
        return contforloop,loopper2
    except:
        traceback.print_exc()     # 捕捉異常，並將異常傳播資訊輸出控制檯
        traceback.print_exc(file=open('log.txt', 'a')) 
        logging.debug('debug message',exc_info=True)
        logging.info('info message',exc_info=True)
        logging.warning('warning message',exc_info=True)
        logging.error('error message',exc_info=True)
        logging.critical('critical message',exc_info=True)
        app1 = QtWidgets.QApplication([])
        error_dialog = QtWidgets.QErrorMessage()
        error_dialog.setWindowTitle('ERROR')
        error_dialog.showMessage('無基本資料')
        app1.exec_() 
def CreateLogEngine():
    try:
        DB_session_log = sessionmaker(engine)
        db_session_log= DB_session_log()
        Log_database=db_session_log.query(Logdata).all()

        LogDbName=Log_database[0].資料庫
        LogIPName=Log_database[0].IP位置
        LogAdName=Log_database[0].帳號
        LogPdName=Log_database[0].密碼

        log_Engine=create_engine('mssql+pyodbc://'+LogAdName+':'+LogPdName+'@'+LogIPName+'/'+LogDbName+'?driver=SQL+Server+Native+Client+11.0')
        return log_Engine
    except:
        traceback.print_exc()     # 捕捉異常，並將異常傳播資訊輸出控制檯
        traceback.print_exc(file=open('log.txt', 'a')) 
        logging.debug('debug message',exc_info=True)
        logging.info('info message',exc_info=True)
        logging.warning('warning message',exc_info=True)
        logging.error('error message',exc_info=True)
        logging.critical('critical message',exc_info=True)
        app1 = QtWidgets.QApplication([])
        error_dialog = QtWidgets.QErrorMessage()
        error_dialog.setWindowTitle('ERROR')
        error_dialog.showMessage('記錄連線失敗')
        app1.exec_()
def LongFirstLogin(urlkey,adminkey,passkey):
    try:
        print(urlkey)
        pubkey="B0C1D0E12B47DA93A24C7422D433170A4D5B126A27CED6F3087652723562889803E2280041F02E6B24A251C928EBE5BA12501D466D63A43AD5D88A5809A09271F14FD220C0DAE272CAAF9F1CF09DAF52005272529071723C0CB87B5A6392860CA2E72B23A4652910DB87BAC31D89E4DD23B3C56AAE685D9A14C4CC89862AAA27"
        rsaPublickey = int(pubkey,16)
        key = rsa.PublicKey(rsaPublickey,65537)
        message='{\"Account\":\"'+adminkey+'\",\"Password\":\"'+passkey+'\"}'
        message=message.encode('utf8')
        #passwd = rsa.encrypt(message,key)
        passwd = binascii.b2a_hex(rsa.encrypt(message,key)).decode()
        Loginurl="http://"+urlkey+"/api/shared/login"

        headers1 = {
        'Content-Length': '256',
        'Content-Type': 'application/x-www-form-urlencoded'
        }
    
        response = requests.request("POST", Loginurl, headers=headers1, data=passwd, timeout=10)

        return response
    except:
        response="無法取得資料"
        return response
def LongSecondLogin(LFLresponse):
    try:
        LFLresponseJson=json.loads(LFLresponse.text)
        print(LFLresponseJson)
        SecondLoginUrl=LFLresponseJson['Redirect']
        SecondLoginData=LFLresponseJson['Data']
    
        headers2 = {
        'Content-Length': '514',
        'Content-Type': 'application/x-www-form-urlencoded'
        }
        print(SecondLoginUrl)#debug
        GatewayUrl="http:"+SecondLoginUrl
        print(GatewayUrl)
        LoginGatewayPostData="+="+SecondLoginData
        response2 = requests.request("POST", GatewayUrl, headers=headers2, data=LoginGatewayPostData, timeout=10)
        return SecondLoginUrl,SecondLoginData,response2
    except:
        SecondLoginUrl=""
        SecondLoginData=""
        response2=""
        return SecondLoginUrl,SecondLoginData,response2
        
def LongGetData(urlkey, adminkey, passkey, startDatekey, endDatekey):
    try:
        LFLresponse=LongFirstLogin(urlkey,adminkey,passkey)
        if LFLresponse=="無法取得資料":
                fordebugger="無法取得資料"
                allll=pd.DataFrame()
                return allll,fordebugger,urlkey,adminkey
        if LFLresponse.status_code==200:
            SecondLoginUrl,SecondLoginData,LSLresponse=LongSecondLogin(LFLresponse)
            if LSLresponse=="":
                fordebugger="無法取得資料"
                allll=pd.DataFrame()
                return allll,fordebugger,urlkey,adminkey
            else:
                cookie=LSLresponse.cookies
                cookie=dict(cookie)['ASP.NET_SessionId']

    

                #cookie=cookie['ASP.NET_SessionId']
                print(cookie)
                GetDataUrl="http:"+SecondLoginUrl[:-21]+"/api/AdminD02Report1/MainQuery"#-21=-(/Command/LoginGateway)
                cookstring="ASP.NET_SessionId="+cookie
                headers88 = {    
                'X-Requested-With': 'XMLHttpRequest',
                'User-Agent': 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/91.0.4472.77 Safari/537.36',
                'Content-Type': 'application/x-www-form-urlencoded',
                'Cookie': cookstring,
                }
                
                              
                getData= "Account=&GameType%5B%5D=35&GameType%5B%5D=22&GameType%5B%5D=11&GameType%5B%5D=12&GameType%5B%5D=13&GroupType%5B%5D=118&GroupType%5B%5D=109&GroupType%5B%5D=108&GroupType%5B%5D=102&GroupType%5B%5D=160&GroupType%5B%5D=159&GroupType%5B%5D=158&GroupType%5B%5D=157&GroupType%5B%5D=156&GroupType%5B%5D=155&GroupType%5B%5D=154&GroupType%5B%5D=153&GroupType%5B%5D=107&GroupType%5B%5D=106&GroupType%5B%5D=105&GroupType%5B%5D=104&GroupType%5B%5D=103&GroupType%5B%5D=101&StartDate="+startDatekey+"&EndDate="+endDatekey+"&IsNotShowNow=true"#FPK375#jnn688

                        #getData= "Account=&GameType%5B%5D=22&GameType%5B%5D=11&GameType%5B%5D=12&GameType%5B%5D=13&GroupType%5B%5D=118&GroupType%5B%5D=109&GroupType%5B%5D=108&GroupType%5B%5D=102&GroupType%5B%5D=160&GroupType%5B%5D=159&GroupType%5B%5D=158&GroupType%5B%5D=157&GroupType%5B%5D=156&GroupType%5B%5D=155&GroupType%5B%5D=154&GroupType%5B%5D=153&GroupType%5B%5D=107&GroupType%5B%5D=106&GroupType%5B%5D=105&GroupType%5B%5D=104&GroupType%5B%5D=103&GroupType%5B%5D=101&StartDate="+startDatekey+"&EndDate="+endDatekey+"&IsNotShowNow=true"#FPK375#jnn688

                response3 = requests.request("POST", GetDataUrl, headers=headers88, data=getData, timeout=10)
                print(response3)

                if response3.status_code==200:
                    jsondata=json.loads(response3.text)
                    #print(jsondata)
                    if response3.text=="null":
                        getData= "Account=&GameType%5B%5D=22&GameType%5B%5D=11&GameType%5B%5D=12&GameType%5B%5D=13&GroupType%5B%5D=118&GroupType%5B%5D=109&GroupType%5B%5D=108&GroupType%5B%5D=102&GroupType%5B%5D=160&GroupType%5B%5D=159&GroupType%5B%5D=158&GroupType%5B%5D=157&GroupType%5B%5D=156&GroupType%5B%5D=155&GroupType%5B%5D=154&GroupType%5B%5D=153&GroupType%5B%5D=107&GroupType%5B%5D=106&GroupType%5B%5D=105&GroupType%5B%5D=104&GroupType%5B%5D=103&GroupType%5B%5D=101&StartDate="+startDatekey+"&EndDate="+endDatekey+"&IsNotShowNow=true"#FPK375#jnn688
                        response3 = requests.request("POST", GetDataUrl, headers=headers88, data=getData, timeout=10)
                    
                        if response3.text=="null":
                            #getData= "Account=&GameType%5B%5D=22&GameType%5B%5D=11&GameType%5B%5D=12&GameType%5B%5D=13&GroupType%5B%5D=118&GroupType%5B%5D=109&GroupType%5B%5D=108&GroupType%5B%5D=102&GroupType%5B%5D=160&GroupType%5B%5D=159&GroupType%5B%5D=158&GroupType%5B%5D=157&GroupType%5B%5D=156&GroupType%5B%5D=155&GroupType%5B%5D=154&GroupType%5B%5D=153&GroupType%5B%5D=107&GroupType%5B%5D=106&GroupType%5B%5D=105&GroupType%5B%5D=104&GroupType%5B%5D=103&GroupType%5B%5D=101&StartDate="+startDatekey+"&EndDate="+endDatekey+"&IsNotShowNow=true"#FPK375#jnn688
                            getData= "Account=&GameType%5B%5D=11&GameType%5B%5D=12&GameType%5B%5D=13&GroupType%5B%5D=109&GroupType%5B%5D=108&GroupType%5B%5D=102&GroupType%5B%5D=160&GroupType%5B%5D=159&GroupType%5B%5D=158&GroupType%5B%5D=157&GroupType%5B%5D=156&GroupType%5B%5D=155&GroupType%5B%5D=154&GroupType%5B%5D=153&GroupType%5B%5D=107&GroupType%5B%5D=106&GroupType%5B%5D=105&GroupType%5B%5D=104&GroupType%5B%5D=103&GroupType%5B%5D=101&StartDate="+startDatekey+"&EndDate="+endDatekey+"&IsNotShowNow=true"#FPK375#jnn688
                            response3 = requests.request("POST", GetDataUrl, headers=headers88, data=getData, timeout=10)
                            #responsetxt=response3.text
                            #print(responsetxt)
                        
                            if response3.text=="null":
                                getData="Account=&GameType%5B%5D=22&GameType%5B%5D=13&GroupType%5B%5D=160&GroupType%5B%5D=159&GroupType%5B%5D=158&GroupType%5B%5D=157&GroupType%5B%5D=156&GroupType%5B%5D=155&GroupType%5B%5D=154&GroupType%5B%5D=153&GroupType%5B%5D=118&GroupType%5B%5D=107&GroupType%5B%5D=106&GroupType%5B%5D=105&GroupType%5B%5D=104&GroupType%5B%5D=103&GroupType%5B%5D=101&StartDate="+startDatekey+"&EndDate="+endDatekey+"&IsNotShowNow=true"#KPC365
                                response3 = requests.request("POST", GetDataUrl, headers=headers88, data=getData, timeout=10)
                                if response3.text=="null":
                                    getData="Account=&GameType%5B%5D=13&GroupType%5B%5D=160&GroupType%5B%5D=159&GroupType%5B%5D=158&GroupType%5B%5D=157&GroupType%5B%5D=156&GroupType%5B%5D=155&GroupType%5B%5D=154&GroupType%5B%5D=153&GroupType%5B%5D=107&GroupType%5B%5D=106&GroupType%5B%5D=105&GroupType%5B%5D=104&GroupType%5B%5D=103&GroupType%5B%5D=101&StartDate="+startDatekey+"&EndDate="+endDatekey+"&IsNotShowNow=true"   #UJK555
                                    response3 = requests.request("POST", GetDataUrl, headers=headers88, data=getData, timeout=10)
                    jsondata=json.loads(response3.text)
            
                    #print(jsondata['DataList'][0]['Account'])
                    #print(len(jsondata['DataList']))
        
                    allll=[]
                    if len(jsondata['DataList'])>0:
                        for i in range(len(jsondata['DataList'])):
                            allll.append([])
                            allll[i].append(jsondata['DataList'][i]['NickName']+"("+jsondata['DataList'][i]['Account']+")")
                            #
                            allll[i].append(jsondata['DataList'][i]['TotBet'])
                            allll[i].append(jsondata['DataList'][i]['TotBetRebate'])
                            allll[i].append(jsondata['DataList'][i]['TotBetWinLose'])
                            allll[i].append(jsondata['DataList'][i]['SubtotalTotWinLose'])   
                            for j in range(len(jsondata['DataList'][i]['ContributionPaid'])):
                                allll[i].append(jsondata['DataList'][i]['ContributionPaid'][j]['TotPaid'])
                            if len(jsondata['DataList'][i]['ContributionPaid'])<6:
                                ReAdd=6-len(jsondata['DataList'][i]['ContributionPaid'])
                                for z in range(ReAdd):
                                    allll[i].append("")
                            allll[i].append(jsondata['DataList'][i]['SubtotalTotDutyWinLose'])
                            allll[i].append(jsondata['DataList'][i]['SubtotalRebateWinLoseDifference'])
                            allll[i].append(jsondata['DataList'][i]['TotPaid'])
                            allll[i].append(jsondata['DataList'][i]['SubtotalSelfTotWinLose'])
                            allll[i].append(jsondata['DataList'][i]['PaidContributor'])
                            allll[i].append(jsondata['DataList'][i]['TotContribution'])
                            allll[i].append(jsondata['DataList'][i]['SubtotalContribution'])
                            allll[i].append(jsondata['DataList'][i]['TotControl'])
                            allll[i].append(jsondata['DataList'][i]['TotControlDuty'])
                        print(allll)
        
                        allll=pd.DataFrame(allll)
                        allll.columns =["名稱","總量","退水","中獎","輸贏","會員","代理","總代理","股東","大股東","總監","佔成輸贏","水倍差","上繳金額","自已總輸贏","上繳貢獻額","貢獻額","貢獻度","調退實佔","上繳"]
                        fordebugger="OK"
                    else:
                        allll=pd.DataFrame()
                        fordebugger="查詢期間無資料"
                    WEBURLTXT=[]
                    ADMINTXT=[]
                    StartDATETXT=[]
                    EndDATETXT=[]
                    TIMERAND=[]
                    nowTime=datetime.datetime.now().strftime("%m%d%H%M%S")
                    randomNum=random.randint(0,10000);
                    if randomNum<10:
                        randomNum=str(0)+str(0)+str(0)+str(randomNum)
                    elif randomNum>10 and randomNum<100:
                        randomNum=str(0)+str(0)+str(randomNum)
                    elif randomNum>100 and randomNum<1000:
                        randomNum=str(0)+str(randomNum)
                    uniqueNum=str(nowTime)+str(randomNum)
                    for i in range(len(allll)):
                        WEBURLTXT.append(urlkey)
                        ADMINTXT.append(adminkey)
                        StartDATETXT.append(startDatekey)
                        EndDATETXT.append(endDatekey)
                        TIMERAND.append(uniqueNum)
    
                    allll.insert(0,"開始日期",StartDATETXT)
                    allll.insert(1,"結束日期",EndDATETXT)
                    allll.insert(2,"網址",WEBURLTXT)
                    allll.insert(3,"帳戶",ADMINTXT)
                    allll.insert(4,"隨機碼",TIMERAND)
                    print(allll)
        
                    refind=pd.DataFrame(columns =["開始日期","結束日期","網址","帳戶","名稱","總量","退水","中獎","輸贏","佔成輸贏","水倍差","上繳金額","自已總輸贏","上繳貢獻額","貢獻額","貢獻度","下注明細","玩法明細","調盤退水","調退實佔","上繳","會員","代理","總代理","股東","大股東","總監","隨機碼"])
                    allll=pd.concat([refind,allll],ignore_index = True)
                    end = time.time()
                    print("執行時間：%f 秒" % (end - start))
        
                    return allll,fordebugger,urlkey,adminkey
                if response3.status_code!=200:
                    fordebugger="無法取得資料"
                    allll=pd.DataFrame()
                    return allll,fordebugger,urlkey,adminkey
        
        if LFLresponse.status_code!=200:
            allll,fordebugger,urlkey,adminkey=ShortGetData(urlkey, adminkey, passkey, startDatekey, endDatekey)
            #print(allll)
            return allll,fordebugger,urlkey,adminkey
    except:
        fordebugger="無法取得資料"
        allll=pd.DataFrame()
        return allll,fordebugger,urlkey,adminkey


def ShortGetData(urlkey, adminkey, passkey, startDatekey, endDatekey):
    try:
        sloginUrl = "https://"+urlkey+"/pub/SuperGateway.php?c=900%2C"+adminkey+"%2C"+passkey

        response = requests.request("POST", sloginUrl, timeout=10)
        #jsondata=json.loads(response.text)
        #SIDKey=rrr[2]
        print(response.status_code)
        if response.status_code==200:
            if len(response.text)>3:
                SIDKey=response.text.split(',')[2]
                if len(SIDKey)<5:
                    SIDKey=response.text.split(',')[3]
                print(SIDKey)
                AMKey=SIDKey.split('_')[0]
                print(AMKey)
    
                LevelUrl="https://"+urlkey+"/hp/headmenu.php?si="+SIDKey
                LevelUrlresponse = requests.request("GET", LevelUrl, timeout=10)
    
                soup=BeautifulSoup(LevelUrlresponse.text,"lxml")
                pattern = re.compile(r'var lv(.*?)=(.*?);',re.MULTILINE|re.DOTALL ) 
                script = soup.find("script", text=pattern) 
                if   len(pattern.search(script.string).group())==11:
                    Level=pattern.search(script.string).group(2)[1:]
        
                elif  len(pattern.search(script.string).group())==9:
                    Level=pattern.search(script.string).group(2)
                print(pattern.search(script.string).group())       
                print(Level)
                #print(pattern.search(script.string).group(1))
                #Level=pattern.search(script.string).group(1)
                GetUrl="https://"+urlkey+"/func/report/rpt_allplus.php"
                # Level="2"
                payload='sacco=&data1='+startDatekey+'&data2='+endDatekey+'&checka=yes&check0=yes&check1=yes&check2=yes&check3=yes&check4=yes&check5=yes&check6=yes&check7=yes&check8=yes&check9=yes&check10=yes&check11=yes&cas1=yes&cas2=yes&cas3=yes&cas4=yes&am='+AMKey+'&l='+Level+'&sid='+SIDKey+'&master=%20&Submit=%25E7%25A2%25BA%25E5%25AE%259A'
                GetHeaders = {
                'Content-Type': 'application/x-www-form-urlencoded'
                }
                GetResponse = requests.request("POST", GetUrl, headers=GetHeaders, data=payload, timeout=10)

                val=pd.DataFrame(pd.read_html(GetResponse.text)[2])[:-1]
               
                if len(val)>0:
                    val.columns = val.loc[0].values.tolist()
                    val=val.drop([0])
                print(val)
                WEBURLTXT=[]
                ADMINTXT=[]
                StartDATETXT=[]
                EndDATETXT=[]
                TIMERAND=[]
                nowTime=datetime.datetime.now().strftime("%m%d%H%M%S")
                randomNum=random.randint(0,10000);
                if randomNum<10:
                    randomNum=str(0)+str(0)+str(0)+str(randomNum)
                elif randomNum>10 and randomNum<100:
                    randomNum=str(0)+str(0)+str(randomNum)
                elif randomNum>100 and randomNum<1000:
                    randomNum=str(0)+str(randomNum)
                uniqueNum=str(nowTime)+str(randomNum)
                for i in range(len(val)):
                    WEBURLTXT.append(urlkey)
                    ADMINTXT.append(adminkey)
                    StartDATETXT.append(startDatekey)
                    EndDATETXT.append(endDatekey)
                    TIMERAND.append(uniqueNum)
     
                val.insert(0,"開始日期",StartDATETXT)
                val.insert(1,"結束日期",EndDATETXT)
        
                val.insert(2,"網址",WEBURLTXT)
                val.insert(3,"帳戶",ADMINTXT)
                val.insert(4,"隨機碼",TIMERAND)
                refind=pd.DataFrame(columns =["開始日期","結束日期","網址","帳戶","客戶","總量","中獎","輸贏","客戶小計","代理小計","總代理小計","股東小計","大股東小計","總監小計","個人所得","水差","下注明細","查看對帳","隨機碼"])

 
                val=pd.concat([refind,val],ignore_index = True)

                if len(val)>0:
                    val.columns =["開始日期","結束日期","網址","帳戶","客戶s","總量s","中獎s","輸贏s","客戶小計s","代理小計s","總代理小計s","股東小計s","大股東小計s","總監小計s","個人所得s","水差s","下注明細s","查看對帳s","隨機碼"]
                    fordebugger="OK"
                   
                    return val,fordebugger,urlkey,adminkey
                else:
                    val=pd.DataFrame()
                 
                    fordebugger="查詢期間無資料"
                    return val,fordebugger,urlkey,adminkey
            else:        
                val=pd.DataFrame()
                fordebugger="無法取得資料"
                return val,fordebugger,urlkey,adminkey
        
        if response.status_code!=200:
            val=pd.DataFrame()
            fordebugger="無法取得資料"
            return val,fordebugger,urlkey,adminkey
    except:
            val=pd.DataFrame()
            fordebugger="無法取得資料"
            return val,fordebugger,urlkey,adminkey


class External(QThread):
    """
    Runs a counter thread.
    """

    countChanged = pyqtSignal(int)
    urlChanged=pyqtSignal(str)
    def run(self):
        try:
            #----------------------------------------------------------------------------------------------- 

            with ThreadPoolExecutor(max_workers=10) as executor:
                futures = []
                test=[]
                z=0
                x=len(UrlList)
                for urlkey,adminkey,passkey in zip(UrlList,AdminList,PasswordList):
                        future=executor.submit(LongGetData,urlkey,adminkey,passkey, startDatekey, endDatekey)
                        
                        futures.append(future)
                        

                for future in as_completed(futures):
                    test.append(list(future.result()))
                    MDF,MDC,MURL,ADMINNNNNN=list(future.result())
                    MDCURL=MURL+"=>"+MDC
                    percentvalues=((z+1)/x)*100
                    self.countChanged.emit(percentvalues)
                    self.urlChanged.emit(MDCURL)
                    z=z+1                
            #-----------------------------------------------------------------------------------------------        
            ALLDataFrameList=[]    
            DebugCodeList=[]    
            ALLDataFrameList.append(refindlong)
            ALLDataFrameList.append(refindshort)
            ALLDataFrameList=pd.concat(ALLDataFrameList)
            ALLDataFrameList0,DebugCodeList,UrlDebugList,ADMINKEYLIST=zip(*test)
            ALLDataFrameList0=pd.concat(ALLDataFrameList0)

 
            neewDataFrmoe=pd.concat([ALLDataFrameList,ALLDataFrameList0])
            print(neewDataFrmoe)

            #-------------------------------------------------------------------------
            LOGGER=[]
            y=0
            for UrlDebugListlog,DebugCodeListlog,ADMINKEYLISTlog in zip(UrlDebugList,DebugCodeList,ADMINKEYLIST):
                if DebugCodeListlog=="OK":
                    print("OK")
                else:
                    LOGGER.append([])
                    randomNum=random.randint(0,9)
                    nowTime=datetime.datetime.now().strftime("%Y-%m-%d-%H:%M:%S")
                    nowTime=str(nowTime)+"---"+str(randomNum)
                    LOGGER[y].append(coustomtxt)
                    LOGGER[y].append(startDatekey+"-"+endDatekey)       
                    LOGGER[y].append(UrlDebugListlog)
                    LOGGER[y].append(DebugCodeListlog)
                    LOGGER[y].append(nowTime)
                    LOGGER[y].append(ADMINKEYLISTlog)
                    y=y+1
            
            LOGGER=pd.DataFrame(LOGGER)
            if len(LOGGER)>0:
                LOGGER.columns =["客戶","查詢區間","網址","狀態","查詢時間","帳號"] 
            else:
                print("NO LOG")
            print(LOGGER)
            end = time.time()
            self.urlChanged.emit("已執行"+str(end - start)+"秒    處理中...")
            #------------------------------------------------------------------------
            neewDataFrmoe.to_sql('流水帳', con=engine3, if_exists='append')
            #neewDataFrmoe.to_csv("M0114UI.csv",encoding="utf_8_sig")#for debug
            LOGGER.to_sql('LOG', con=engine3, if_exists='append')
           


            closedatalodibg=LoadingYesorNo()
            end = time.time()
            print("執行時間：%f 秒" % (end - start))
            self.urlChanged.emit("已執行"+str(end - start)+"秒    處理完成")
            print("DONE.....................100%")
            #window.close()
            app.quit()
            
        except Exception:
                logging.debug('debug message',exc_info=True)
                logging.info('info message',exc_info=True)
                logging.warning('warning message',exc_info=True)
                logging.error('error message',exc_info=True)
                logging.critical('critical message',exc_info=True)
                
                app1 = QtWidgets.QApplication([])
                error_dialog = QtWidgets.QErrorMessage()
                error_dialog.setWindowTitle('ERROR')
                error_dialog.showMessage('連線資料錯誤')
                app1.exec_()            
        finally:
            closedatalodibg=LoadingYesorNo()   
class Actions(QDialog):

    """
    Simple dialog that consists of a Progress Bar and a Button.
    Clicking on the button results in the start of a timer and
    updates the progress bar..
    """
    def __init__(self):
        super().__init__()
        self.initUI()
        
    def initUI(self):
        self.setWindowTitle('D0122')
        self.setWindowFlags(Qt.WindowStaysOnTopHint)
        self.progress = QProgressBar(self)
        self.progress.setGeometry(20, 20, 300, 25)
        self.progress.setMaximum(100)
        self.label=QLabel(self)
        #self.label.setFont(QtGui.QFont('Arial', 12))
        self.label.setGeometry(20, 50, 211, 31)
      #  self.label.setTextFormat(QtCore.Qt.AutoText)
        self.label.setText("- 自動導入中請勿關閉視窗 -        ")
      #  self.button = QPushButton('Start', self)
       # self.button.move(0, 30)
        self.label2=QLabel(self)
        self.label2.setGeometry(20, 70, 211, 31)
        self.show()

    #    self.button.clicked.connect(self.onButtonClick)
        
   # def onButtonClick(self):
        self.calc = External()
        self.calc.countChanged.connect(self.onCountChanged)
        self.calc.urlChanged.connect(self.onUrlChanged)
        self.calc.start()

    def onCountChanged(self, value):
        self.progress.setValue(value)
    def onUrlChanged(self, urlstr):
        self.label2.setText(urlstr)

class Actions2(QDialog):

    """
    Simple dialog that consists of a Progress Bar and a Button.
    Clicking on the button results in the start of a timer and
    updates the progress bar.
    """
    def __init__(self):
        super().__init__()
        self.initUI()
        
    def initUI(self):
        self.setWindowTitle('連線中...')
        self.label=QLabel(self)
        #self.label.setFont(QtGui.QFont('Arial', 12))
        self.label.setGeometry(20, 20,120,30)
        #self.label.setTextFormat(QtCore.Qt.AutoText)
        self.label.setText("- 取得連線 請按開始 -")
        self.button = QPushButton('開始', self)
        self.button.move(50, 80)
        self.show()
        self.button.clicked.connect(self.onButtonClick)
    def onButtonClick(self):
        self.close()



try:
    #app1 = QApplication(sys.argv)
    #window2 = Actions2()
    FIle="A.txt"
    FIle2="B.txt"
    def LoadingYesorNo():
                    SUPDA=db_session2.query(Someoneupdate).filter_by(正在自動導入中='YES').first()
                    SUPDAc=db_session2.query(Someoneupdate).filter_by(正在自動導入中='YES').count()
                    print(SUPDAc)
                    if (SUPDAc==1):
                        SUPDA.正在自動導入中='NO'
                        db_session2.commit()    
    engine = create_engine('mssql+pyodbc://B70340:Lee0911274990@106.1.48.106,4567/GA-ERP-SYS?driver=SQL+Server+Native+Client+11.0')
    coustomtxt=loadCSV()
    print (coustomtxt)
    engine3=connectEngine(coustomtxt,engine)  
    DB_session2 = sessionmaker(engine3)
    db_session2= DB_session2()
    log_Engine=CreateLogEngine()
    while True:
        if os.path.isfile(FIle)==True:
            os.remove(FIle)
            startDatekey,endDatekey,allpart=DateDataLoad(engine3,db_session2)
            contforloop,loopper2=LoadCustomData(engine3,allpart)  
            UrlList,AdminList,PasswordList=zip(*loopper2)
            print(UrlList,AdminList,PasswordList)
            start = time.time()
            refindlong=pd.DataFrame(columns =["開始日期","結束日期","網址","帳戶","名稱","總量","退水","中獎","輸贏","佔成輸贏","水倍差","上繳金額","自已總輸贏","上繳貢獻額","貢獻額","貢獻度","下注明細","玩法明細","調盤退水","調退實佔","上繳","會員","代理","總代理","股東","大股東","總監"])
            refindshort=pd.DataFrame(columns =["開始日期","結束日期","網址","帳戶","客戶s","總量s","中獎s","輸贏s","客戶小計s","代理小計s","總代理小計s","股東小計s","大股東小計s","總監小計s","個人所得s","水差s","下注明細s","查看對帳s","隨機碼"])
            #window2.close()
            #app1.quit()
            #app1.exec_()    
            print("uuuuuuuu")
            app = QtWidgets.QApplication(sys.argv)
            print("oooooooo")
            window = Actions()        
            app.exec_()
            
            
            del app
            del window
            #sys.exit(app.exec_())
            
            continue
        if os.path.isfile(FIle2)==True:
            os.remove(FIle2)
            break
        time.sleep(1)      
    #sys.exit()  

except:
    traceback.print_exc()     # 捕捉異常，並將異常傳播資訊輸出控制檯
    traceback.print_exc(file=open('log.txt', 'a')) 
    logging.debug('debug message',exc_info=True)
    logging.info('info message',exc_info=True)
    logging.warning('warning message',exc_info=True)
    logging.error('error message',exc_info=True)
    logging.critical('critical message',exc_info=True)
    app2 = QtWidgets.QApplication([])

    error_dialog = QtWidgets.QErrorMessage()
    error_dialog.setWindowTitle('ERROR')
    error_dialog.showMessage('網路DRIVER錯誤')

    app2.exec_() 

finally:
    closedatalodibg=LoadingYesorNo()