import subprocess,json,re,pymysql,datetime,requests

ini_path = "/var/www/conteenew_python_apps/bid/"

ini_srcs = [
    {'srcName':'강남구청','scriptName':'bid_gangnam.py','status':'active'},
    {'srcName':'나라장터','scriptName':'bid_naramarket.py','status':'active'},
    {'srcName':'ebiz4u','scriptName':'bid_ebiz4u.py','status':'active'},
    {'srcName':'s2b','scriptName':'bid_s2b.py','status':'active'},
    {'srcName':'스타빌','scriptName':'bid_starbill.py','status':'inactive'}
]

bid_db = pymysql.connect(
    user='teen',
    passwd='tkatjdehd1!',
    host='localhost',
    db='bid',
    charset='utf8'
)

timeNow = datetime.datetime.now()

print('Start at ' + timeNow.strftime('%Y-%m-%d %H:%M:%S') + '=========================================')

cursor_chkoutdated = bid_db.cursor(pymysql.cursors.DictCursor)
chkoutdated_sql = 'UPDATE `bid_data` SET `bid_status` = 5 where `bid_applyDateTo` <= "' + timeNow.strftime('%Y-%m-%d %H:%M:%S') + '"'
cursor_chkoutdated.execute(chkoutdated_sql)
bid_db.commit()
print(chkoutdated_sql)

term_cursor = bid_db.cursor(pymysql.cursors.DictCursor)
sql = "select * from bid_crwlTerms"
term_cursor.execute(sql)
termdb = term_cursor.fetchall()
terms = []
for i in range(len(termdb)):
    term = termdb[i]['term']
    terms.append(term)

command = []
for i in range(len(ini_srcs)):
    if ini_srcs[i]['status'] == "active":
        for j in range(len(terms)):
            cmd = {}
            cmd['cmd'] = ini_path + ini_srcs[i]['scriptName']
            cmd['term_opt'] = '"' + terms[j] + '"'
            command.append(cmd)

res = []
for i in range(len(command)):
    cmd = 'python3 ' + command[i]['cmd'] + ' ' + command[i]['term_opt']
    print(cmd)
    data = subprocess.check_output(cmd,stderr=subprocess.STDOUT,shell=True,encoding='utf-8')
    data = re.sub("\'",'\"',data)
    data = json.loads(data)
    if len(data) == 0: pass
    elif data[0]['type'] == "검색/등록된 정보가 없습니다.": pass
    elif data[0]['type'] == "검색된 데이터가 없습니다.": pass
    else:
        for j in range(len(data)):
            res.append(data[j])

for i in range(len(res)):
    cursor_count = bid_db.cursor(pymysql.cursors.DictCursor)
    count_sql = 'select count(*) AS COUNT from `bid_data` where bid_url="' + res[i]['detailurl'] + '"'
    cursor_count.execute(count_sql)
    counts = cursor_count.fetchall()
    dup = counts[0]['COUNT']

    if res[i]['src'] == "강남구청":
        timeEnd = datetime.datetime.strptime(res[i]['applyDateTo'], "%Y-%m-%d")
    elif res[i]['src'] == "나라장터":
        if res[i]['applyDateTo'] == "-":
            timeEnd = datetime.datetime.strptime("9999/12/31 11:59", "%Y/%m/%d %H:%M")
        elif res[i]['applyDateTo'] == "":
            timeEnd = datetime.datetime.strptime("1999/12/31 11:59", "%Y/%m/%d %H:%M")
        elif res[i]['applyDateTo'] == "공개이전":
            timeEnd = datetime.datetime.strptime("9999/12/31 11:59", "%Y/%m/%d %H:%M")
        else:
            timeEnd = datetime.datetime.strptime(res[i]['applyDateTo'], "%Y/%m/%d %H:%M")
    elif res[i]['src'] == "이비즈포유":
        timeEnd = datetime.datetime.strptime(res[i]['applyDateTo'], "%Y-%m-%d %H:%M")
    elif res[i]['src'] == "s2b":
        timeEnd = datetime.datetime.strptime(res[i]['applyDateTo'], "%Y-%m-%d %H:%M")
    else: pass

    if dup == 0:
        if timeNow < timeEnd:
            cursor_insert = bid_db.cursor(pymysql.cursors.DictCursor)
            value = "'','" + res[i]['src'] + "','" + res[i]['term'] + "','" + \
                    res[i]['type'] + "','" + res[i]['budget'] + "','" + \
                    res[i]['commitDate'] + "','" + res[i]['dept'] + "','" + \
                    res[i]['name'] + "','" + res[i]['detailurl'] + "','" + \
                    res[i]['applyDateFrom'] + "','" + str(timeEnd) + \
                    "',NULL,0,NOW(),NULL,NULL"
            insert_sql = "INSERT INTO `bid_data` VALUES(" + value + ")"
            cursor_insert.execute(insert_sql)
            bid_db.commit()
            print(insert_sql)
    else:
        cursor_check = bid_db.cursor(pymysql.cursors.DictCursor)
        check_sql = 'select * from bid_data where `bid_url`="' + res[i]['detailurl'] + '"'
        cursor_check.execute(check_sql)
        getdata = cursor_check.fetchall()
        if datetime.datetime.strptime(getdata[0]['bid_applyDateTo'], '%Y-%m-%d %H:%M:%S') < timeNow:
            cursor_update = bid_db.cursor(pymysql.cursors.DictCursor)
            update_sql = "UPDATE `bid_data` SET `bid_status` = 5 where idx=" + str(getdata[0]['idx'])
            cursor_update.execute(update_sql)
            bid_db.commit()
            print(update_sql)
        elif (timeEnd != getdata[0]['bid_applyDateTo']) or (res[i]['name'] != getdata[0]['bid_name']):
            cursor_update = bid_db.cursor(pymysql.cursors.DictCursor)
            update_sql = 'UPDATE `bid_data` SET `bid_applyDateTo` = "' + str(timeEnd) + \
                         '", `bid_name` ="' + res[i]['name'] + \
                         '" where idx=' + str(getdata[0]['idx'])
            cursor_update.execute(update_sql)
            bid_db.commit()
            print(update_sql)
        else:
            print("pass")

bid_db.close()

#mailcheckurl = 'http://office.conteenew.com/functions/mail/bid_alert.php'
#resp = requests.get(mailcheckurl)
#resp.raise_for_status()
#html = resp.text

print('END' + ' =========================================')