#!/usr/bin/env python

import MySQLdb
import sys

devdb_host = "10.10.1.1"
sdb_host = "10.10.10.1"

devdb = MySQLdb.connect(devdb_host, "x", "xx!", "mydb")
sdb = MySQLdb.connect(sdb_host, "x", "xx!", "mydb")

sdb_cursor = sdb.cursor()
devdb_cursor = devdb.cursor()


userId = "00011379"


delete_all = "delete a, b from base a left join detail b on a.baseSeq = b.baseSeq where a.userId = '" + userId + "'"

select_base_where = "where recognitionDate>='20150601' and userId = '" + userId + "'"
select_base_count = "select count(1) from base " + select_base_where
sdb_cursor.execute(select_base_count)
rowCount = sdb_cursor.fetchone()[0]
print "target base count = "+str(rowCount)


select_base = "select * from base " + select_base_where
select_base_detail = "select * from base_detail where baseSeq = " 

insert_mbase = "insert into mbase (a, b, c) VALUES ('{0}', {1}, {2}, '{3}', {4}, '{5}', {6}, {7}, {8}, '{9}', '{10}', '{11}', null, null, 0)"
insert_detail = "insert into finance.mbase_detail (a, b, c) VALUES ({0}, {1}, {2}, {3}, {4}, {5}, {6}, {7}, {8}, null, null)"


# DELETE
devdb_cursor.execute(delete_all)
devdb.commit()


# SELECT BASE
sdb_cursor.execute(select_base)

for r in sdb_cursor.fetchall():

    # INSERT BASE
    insert_query = insert_mbase.format(r[1], r[2], r[3], r[4], r[5], r[6], r[7], r[8], r[9], r[10], r[11], r[12])
    devdb_cursor.execute(insert_query)

    devdb_cursor.execute("select last_insert_id()");
    baseSeq = devdb_cursor.fetchone()[0]

    # SELECT DETAILS
    sdb_cursor.execute(select_base_detail + str(r[0]))

    # INSERT DETAILS
    for pbd in sdb_cursor.fetchall():
        query = insert_detail.format(baseSeq, pbd[2], pbd[3], pbd[4], pbd[5], pbd[6], pbd[7], pbd[8], pbd[9])
        devdb_cursor.execute(query)

    devdb.commit()
    sys.stdout.write(str(baseSeq)+",")

print "----"
print "Success."

devdb.close()
sdb.close()