StudentDataMigration

__author__ = 'insan'
EMPTY_STRING = ''
CURRENT_SESSION = 1

import MySQLdb
import configparser
import datetime
import traceback
import re
import logging


logFormatter = logging.Formatter("%(asctime)s [%(levelname)-5.5s]  %(message)s")
logger = logging.getLogger()
logger.setLevel(logging.INFO)

fileHandler = logging.FileHandler("student_migration.log")
fileHandler.setFormatter(logFormatter)

logger.addHandler(fileHandler)

# consoleHandler = logging.StreamHandler()
# consoleHandler.setFormatter(logFormatter)
# logger.addHandler(consoleHandler)


def validate_amount(amount, no_of_student):
    amount = re.sub("[^0-9]", "", str(amount))
    if str(amount) == '':
        return 0

    try:
        amount = int(str(amount))
    except Exception:
        #traceback.print_exc()
        amount = 0
    if amount < 0:
        return 0
    if amount % 25 != 0:
        amount = amount - (amount%25)
    if amount < 50:
        amount = 0
    if amount > 1350:
        amount = 1350
    if no_of_student == 1 and amount > 375:
        amount = 375
    if no_of_student == 2 and amount > 750:
        amount = 750
    if no_of_student == 3 and amount > 1050:
        amount = 1050
    if no_of_student == 4 and amount > 1350:
        amount = 1350

    return amount


def validate_std_cnt(no_of_student, amount):
    try:
        no_of_student = int(str(no_of_student))
    except Exception as e:
        #print e
        no_of_student = 0
    try:
        amount = int(amount)
    except Exception as e:
        #print e
        amount = 0

    if no_of_student == '' or no_of_student == None:
        no_of_student = 0

    if amount == 0:
        no_of_student = 1
    if amount > 375:
        no_of_student = 2
    if amount > 750:
        no_of_student = 3
    if amount > 1050:
        no_of_student = 4
    if no_of_student > 4:
        no_of_student = 4

    return no_of_student


def get_data_from_source(src_db_con, school_code_list_string):
    cursor = src_db_con.cursor()
    query = """SELECT
        dsrd.id parent_id, dsrd.amount, ifnull(dsrd.no_of_student, 1), dsrd.school_code school_code
    FROM
        q2_disbursement_sheet_raw_data_report_prime dsrd
        WHERE dsrd.school_code IN (""" + school_code_list_string + """)
    """

    cursor.execute(query)
    result = cursor.fetchall()

    return result


def get_parent_dsSheet_map(dest_db_con, dsSheet_id_list):
    map = {}
    cursor = dest_db_con.cursor()
    # cursor.execute("SET SESSION range_optimizer_max_mem_size = 16688608")
    cursor.execute("""
      SELECT dsSheet_id, parent_id from dsSheet_parent_map
      WHERE dsSheet_id in (%s)
      """ % dsSheet_id_list
    )
    result = cursor.fetchall()
    for row in result:
        map[str(row[0])] = str(row[1])

    return map


def get_dsSheetId_list_from_schoolCode(src_db_con, school_code_list_string):
    cursor = src_db_con.cursor()
    # cursor.execute("SET SESSION group_concat_max_len = 1000000")
    cursor.execute("""
        SELECT
            id
        FROM
            q2_disbursement_sheet_raw_data_report_prime
        WHERE
            school_code IN (""" + school_code_list_string + """)
    """)

    result = cursor.fetchall()
    id_list = []

    for row in result:
        id_list.append(str(row[0]))

    if len(id_list) > 0:
        return ",".join(id_list)
    else:
        return None


def get_studentwise_amount_list(no_of_student, amount):
    studentwise_amount_list = []
    if no_of_student == 1 or no_of_student == 0:
        studentwise_amount_list.append(amount)
        return studentwise_amount_list

    if amount == 0:
        studentwise_amount_list = get_studentwise_amount_list(no_of_student - 1, amount)
        studentwise_amount_list.append(0)
    elif amount <= no_of_student * 375 and no_of_student * 300 < amount:
        studentwise_amount_list = get_studentwise_amount_list(no_of_student - 1, amount - 375)
        studentwise_amount_list.append(375)
    elif amount <= no_of_student * 300 and no_of_student * 150 < amount:
        studentwise_amount_list = get_studentwise_amount_list(no_of_student - 1, amount - 300)
        studentwise_amount_list.append(300)
    elif amount <= no_of_student * 150 and amount > no_of_student * 100:
        studentwise_amount_list = get_studentwise_amount_list(no_of_student - 1, amount - 150)
        studentwise_amount_list.append(150)
    else:
        studentwise_amount_list = get_studentwise_amount_list(no_of_student - 1, amount - 25)
        studentwise_amount_list.append(50)

    for num in studentwise_amount_list:
        if num < 0:
            pass
    return studentwise_amount_list


def get_school_id_by_code(school_code, dest_db_con):
    cursor = dest_db_con.cursor()
    cursor.execute("SELECT id FROM school WHERE code=%s" % school_code)
    result = cursor.fetchone()
    if result is not None:
        return result[0]
    else:
        return None


def insert_student_data(src_db_con, dest_db_con, school_code_list_string):
    dest_cursor = dest_db_con.cursor()
    #next_student_id = get_next_student_id(dest_cursor)
    dsSheetId_stdCnt_schoolId_list = get_data_from_source(src_db_con, school_code_list_string)
    dsSheet_id_list = get_dsSheetId_list_from_schoolCode(src_db_con, school_code_list_string)

    print "DsSheet entry in this thana " + str(len(dsSheetId_stdCnt_schoolId_list))

    if dsSheet_id_list is None:
        return

    parent_dsSheet_map = get_parent_dsSheet_map(dest_db_con, dsSheet_id_list)

    for tupol in dsSheetId_stdCnt_schoolId_list:
        dsShhet_id = str(tupol[0])
        no_of_student = validate_std_cnt(no_of_student=tupol[2], amount=tupol[1])
        amount = validate_amount(amount=tupol[1], no_of_student=no_of_student)

        student_wise_amount_list = get_studentwise_amount_list(no_of_student, amount)

        school_id = get_school_id_by_code(tupol[3], dest_db_con)

        if school_id is not None:
            for i in range(no_of_student):
                student_insert_query = ''
                student_stipend_query = ''
                if dsShhet_id in parent_dsSheet_map:
                    student_insert_query = """
                        INSERT INTO student (name, parent_id, school_id)
                        VALUES ('%s', %s, %s)
                        """ % (EMPTY_STRING, parent_dsSheet_map[dsShhet_id], school_id)
                    dest_cursor.execute(student_insert_query)

                    student_id = get_next_student_id(dest_cursor)
                    student_stipend_query = """
                        INSERT INTO student_stipend (amount, session_id, student_id)
                        VALUES (%s, %s,%s)
                    """ % (student_wise_amount_list[i], CURRENT_SESSION, student_id)
                    dest_cursor.execute(student_stipend_query)

                else:
                    logger.error("No map found for ds id %s" % dsShhet_id)
        else:
            logger.error("No school found for sc: %s in dest db" % tupol[3])

    dest_db_con.commit()


def get_next_student_id(cursor):
    # cursor.execute("""
    # select id from student
    #     order by id desc
    #     limit 1
    # """)
    # next_id = cursor.fetchall()
    # if len(next_id) == 0 or len(next_id[0]) == 0:
    #     next_id = 1
    # else:
    #     next_id = next_id[0][0] + 1
    #
    # return next_id
    return cursor.lastrowid


# def get_next_student_stipend_id(cursor):
#     cursor.execute("""
#     select id from student_stipend
#         order by id desc
#         limit 1
#     """)
#     next_id = cursor.fetchall()
#     if len(next_id) == 0 or len(next_id[0]) == 0:
#         next_id = 1
#     else:
#         next_id = next_id[0][0] + 1
#
#     return next_id


# def get_thana_list(live_db_conn):
#     cursor = live_db_conn.cursor()
#     cursor.execute("""
#         SELECT id from thana
#          WHERE 1
#          AND id > 0
#          AND id <= 600
#         ORDER BY id
#     """)
#     thana_id_list = []
#     result = cursor.fetchall()
#     for row in result:
#         thana_id_list.append(row[0])
#
#     return thana_id_list


def get_school_code_list_string(thana_id, live_db_con):
    cursor = live_db_con.cursor()
    cursor.execute("""
    SELECT
        school_code
    FROM
        school
    WHERE
        thana_id = """ + str(thana_id) + """ AND school_code REGEXP '^[0-9]+$'
    """)
    result = cursor.fetchall()

    sc_list = []
    for row in result:
        sc_list.append(row[0])

    try:
        return "'"+"','".join(sc_list)+"'"
    except Exception as e:
        traceback.print_stack()
        return None


def get_reader():
    config = configparser.ConfigParser()
    config.sections()
    config.read('config.ini')

    return config


def get_src_connection():
    config = get_reader()
    section = config.default_section

    return MySQLdb.connect(config.get(section, 'src_ip'),
                           config.get(section, 'src_uname'),
                           config.get(section, 'src_pword'),
                           config.get(section, 'src_db'))


def get_dest_connection():
    config = get_reader()
    section = config.default_section

    return MySQLdb.connect(config.get(section, 'dest_ip'),
                           config.get(section, 'dest_uname'),
                           config.get(section, 'dest_pword'),
                           config.get(section, 'dest_db'))


def get_live_db_connection():
    config = get_reader()
    section = config.default_section

    return MySQLdb.connect(config.get(section, 'live_ip'),
                           config.get(section, 'live_uname'),
                           config.get(section, 'live_pword'),
                           config.get(section, 'live_db'))


def run():
    # configure source database connection
    src_db_con = get_src_connection()
    # configure destination database connection
    dest_db_con = get_dest_connection()
    # live db configure
    live_db_con = get_live_db_connection()

    thana_id_list = [x for x in range(401, 510)]
    count = 1

    # total = len(thana_id_list)
    for thana_id in thana_id_list:
        logger.info("Processing thana: %d" % thana_id)
        start_time = datetime.datetime.now()
        school_code_list_string = get_school_code_list_string(thana_id, live_db_con)
        # school_code_list_string = get_school_code_list_string(thana_id, live_db_con)
        if school_code_list_string is not None:
            insert_student_data(src_db_con, dest_db_con, school_code_list_string)
        end_time = datetime.datetime.now()
        logger.info("Processing done for thana: %d" % thana_id)
        print "Processing done for thana: %d" % thana_id
        print "Time taken: " + str(end_time - start_time)
        count += 1

run()