import os
os.environ['NLS_LANG'] = 'AMERICAN_AMERICA.AL32UTF8'
from dataSource.source import *
from utils.Utils import *


def getContactjson(companyname,contact_person,mobile_no,phone_no,mail):
    if mobile_no!="" or phone_no!="":
        _dict = dict()
        _dict["company"] = companyname
        _dict["contact_person"] = contact_person
        _dict["mobile_no"] = mobile_no
        _dict["phone_no"] = phone_no
        _dict["mail"] = mail
        _dict["level"] = 40
        return re.sub('\'','',json.dumps([_dict],ensure_ascii=False))
    return json.dumps([])

mobile_pattern = re.compile("^1\d{10}$")
def recog_likeType(phone):
    if re.search(mobile_pattern,phone) is not None:
        return "mobile"
    else:
        return "phone"

def transform_contact():
    conn_target = getConnection_testmysql()
    conn_source = getConnection_oracle()

    cursor_target = conn_target.cursor()
    cursor_source = conn_source.cursor()

    # sql = "select companyname,contact,landline,mobilephone from bxkc.HUI_CONG_CONPANYINFO"
    # cursor_source.execute(sql)
    #
    # rows = cursor_source.fetchmany(10)
    # _count = len(rows)
    # while rows:
    #     list_json = []
    #     for row in rows:
    #         company = row[0]
    #         contact_person = row[1]
    #         phone_no = ""
    #         if row[2]!='86':
    #             phone_no = row[2]
    #         mobile_no = ""
    #         if row[3] is not None and re.search("\*",row[3]) is None:
    #             mobile_no = row[3]
    #         if phone_no!="" or mobile_no!="":
    #             list_json.append(getContactjson(company,contact_person,mobile_no,phone_no,""))
    #     sql = "insert into bxkc.company_contact_json(json_contact) values ('"+"'),('".join(list_json)+"')"
    #     cursor_target.execute(sql)
    #     rows = cursor_source.fetchmany(50000)
    #     _count += len(rows)
    #     print(_count)

    # sql = "select company_name,company_contact_person,company_contact_num,company_mail from bxkc.bxkc_gongyingshang_info"
    # cursor_source.execute(sql)
    #
    # rows = cursor_source.fetchmany(10)
    # _count = len(rows)
    # while rows:
    #     list_json = []
    #     for row in rows:
    #         company = row[0]
    #         contact_person = row[1]
    #         phone_no = ""
    #         mobile_no = ""
    #         company_contact_num = row[2]
    #         company_mail = row[3]
    #         if company_contact_num is not None:
    #             if recog_likeType(company_contact_num)=="mobile":
    #                 mobile_no = company_contact_num
    #             else:
    #                 phone_no = company_contact_num
    #         if company_mail is not None:
    #             mail = company_mail
    #         else:
    #             mail = ''
    #         if phone_no!="" or mobile_no!="":
    #             list_json.append(getContactjson(company,contact_person,mobile_no,phone_no,mail))
    #     sql = "insert into bxkc.company_contact_json(json_contact) values ('"+"'),('".join(list_json)+"')"
    #     cursor_target.execute(sql)
    #     rows = cursor_source.fetchmany(50000)
    #     _count += len(rows)
    #     print(_count)

    sql = "select company_name,contact_person,mobile_no from bxkc.bxkc_enterprise_listing_info"
    cursor_source.execute(sql)

    rows = cursor_source.fetchmany(10)
    _count = len(rows)
    while rows:
        list_json = []
        for row in rows:
            company = row[0]
            contact_person = row[1]
            phone_no = ""
            mobile_no = ""
            company_contact_num = row[2]
            if company_contact_num is not None:
                if recog_likeType(company_contact_num)=="mobile":
                    mobile_no = company_contact_num
                else:
                    phone_no = company_contact_num
            mail = ''
            if phone_no!="" or mobile_no!="":
                list_json.append(getContactjson(company,contact_person,mobile_no,phone_no,mail))
        sql = "insert into bxkc.company_contact_json(json_contact) values ('"+"'),('".join(list_json)+"')"
        cursor_target.execute(sql)
        rows = cursor_source.fetchmany(50000)
        _count += len(rows)
        print(_count)

    conn_target.commit()

if __name__=="__main__":
    transform_contact()