#! /usr/bin/python
# FXN LIBRARY
import MySQLdb
import time
import csv
import string

MySQLdb.paramstyle
from datetime import datetime

## csv parameters
filename = "patients.csv"
reader = csv.reader(open(filename,"U"), delimiter=',')

## MySQL parameters 
connMRS = MySQLdb.connect (host="localhost",
        port=3306,
        user="root",
        passwd = "--------",
        db="openmrs")

## id number of concept class
class_id = '19'
location = '1'


for row in reader:
	cursoromrs1 = connMRS.cursor()
	cursoromrs2 = connMRS.cursor()
	cursoromrs3 = connMRS.cursor()
	cursoromrs4 = connMRS.cursor()
	cursoromrs5 = connMRS.cursor()
	cursoromrs6 = connMRS.cursor()
	cursoromrs7 = connMRS.cursor()
	cursoromrs8 = connMRS.cursor()
	cursoromrs9 = connMRS.cursor()
	cursoromrs10 = connMRS.cursor()
	cursoromrs11 = connMRS.cursor()
	
	oldclinicid = row[0]
	oldclinicid2 = row[2]
	oldclinicid3 = row[3]
	newclinicid = row[1]
	first_name = row[4]
	last_name = row[5]
	birthyear = row[6]
	gender = row[7]
	telephone = row[8]
	nationality = row[9]
	asylum_seeker = row[10]
	visa_num = row[11]
	asylum_num = row[12]
	family_visa = row[13]
	family_asylum = row[14]
	omrs_person_id = row[15]
#	rel_parent = row[16]
#	rel_family1 = row[17]
#	rel_family2 = row[18]
#	rel_family3 = row[19]
#	rel_family4 = row[20]
#	rel_family5 = row[21]
#	misc_id = row[22]	
#	hospital_id = row[23]
	prisoner_id = row[24]

	## Querries that insert data into OpenMRS database

	birthdate = birthyear+"-01-01"
	
	ins_person_stmnt = """INSERT INTO person(gender,birthdate,creator,date_created, uuid) VALUES(%s, %s,'1',NOW(),uuid())"""
	cursoromrs1.execute(ins_person_stmnt, (gender, birthdate))
#	personid = int(cursoromrs1.lastrowid)
	cursoromrs1.execute("commit")
	cursoromrs1.close()

## patient name

	ins_person_name_stmnt = """INSERT INTO person_name(preferred, person_id, given_name, family_name,creator,date_created,uuid) VALUES('1', %s, %s, %s,'1',NOW(), uuid())"""
	cursoromrs2.execute(ins_person_name_stmnt,(omrs_person_id, first_name.strip(), last_name.strip(),))
	cursoromrs2.execute("commit")
	cursoromrs2.close()


## All the attributes follow here:
## Telephone attribute

	if len(telephone) > 0:
		attribute_value = telephone
		attribute_type = '8'

		ins_person_attribute_stmnt = """INSERT INTO person_attribute(person_id, value, person_attribute_type_id, creator, date_created, voided, uuid) VALUES(%s, %s, %s, '1', NOW(), '0', uuid())"""
		cursoromrs3.execute(ins_person_attribute_stmnt,(omrs_person_id, attribute_value, attribute_type))
		cursoromrs3.execute("commit")
		cursoromrs3.close()

## nationality attribute

	if len(nationality) > 0:
		attribute_value = nationality
		attribute_type = '3'
	
		ins_person_attribute_stmnt = """INSERT INTO person_attribute(person_id, value, person_attribute_type_id, creator, date_created, voided, uuid) VALUES(%s, %s, %s, '1', NOW(), '0', uuid())"""
		cursoromrs4.execute(ins_person_attribute_stmnt,(omrs_person_id, attribute_value, attribute_type))
		cursoromrs4.execute("commit")
		cursoromrs4.close()

## asylum_seeker attribute

	if len(asylum_seeker) > 0:
		attribute_value = asylum_seeker
		attribute_type = '12'

		ins_person_attribute_stmnt = """INSERT INTO person_attribute(person_id, value, person_attribute_type_id, creator, date_created, voided, uuid) VALUES(%s, %s, %s, '1', NOW(), '0', uuid())"""
		cursoromrs5.execute(ins_person_attribute_stmnt,(omrs_person_id, attribute_value, attribute_type))
		cursoromrs5.execute("commit")
		cursoromrs5.close()

## family visa number attribute

	if len(family_visa) > 0:
		attribute_value = family_visa
		attribute_type = '17'
	
		ins_person_attribute_stmnt = """INSERT INTO person_attribute(person_id, value, person_attribute_type_id, creator, date_created, voided, uuid) VALUES(%s, %s, %s, '1', NOW(), '0', uuid())"""
		cursoromrs4.execute(ins_person_attribute_stmnt,(omrs_person_id, attribute_value, attribute_type))
		cursoromrs4.execute("commit")
		cursoromrs4.close()

## family asylum number attribute

	if len(family_asylum) > 0:
		attribute_value = family_asylum
		attribute_type = '18'

		ins_person_attribute_stmnt = """INSERT INTO person_attribute(person_id, value, person_attribute_type_id, creator, date_created, voided, uuid) VALUES(%s, %s, %s, '1', NOW(), '0', uuid())"""
		cursoromrs5.execute(ins_person_attribute_stmnt,(omrs_person_id, attribute_value, attribute_type))
		cursoromrs5.execute("commit")
		cursoromrs5.close()

## create patient

	ins_patient_stmnt = """INSERT INTO patient(patient_id, creator, date_created, voided) VALUES(%s, '1', NOW(), '0')"""
	cursoromrs6.execute(ins_patient_stmnt,(omrs_person_id))
	patientid = int(cursoromrs6.lastrowid)
	cursoromrs6.execute("commit")
	cursoromrs6.close()


## The following section associates all of the ID numbers with the patient
## Old Clinic ID 1
	
	id_type = '2'
	id = oldclinicid
	preferred = '0'
	if len(id) > 0:
		ins_patient_id_stmnt = """INSERT INTO patient_identifier(patient_id, identifier, identifier_type, preferred, location_id, creator, date_created, voided, uuid) VALUES(%s, %s, %s, %s, %s, '1', NOW(), '0', uuid())"""
		cursoromrs7.execute(ins_patient_id_stmnt,(patientid, id, id_type, preferred, location))
		cursoromrs7.execute("commit")
		cursoromrs7.close()
		
## Old Clinic ID 2
	
	id_type = '2'
	id = oldclinicid2
	preferred = '0'
	if len(id) > 0:
		ins_patient_id_stmnt = """INSERT INTO patient_identifier(patient_id, identifier, identifier_type, preferred, location_id, creator, date_created, voided, uuid) VALUES(%s, %s, %s, %s, %s, '1', NOW(), '0', uuid())"""
		cursoromrs7.execute(ins_patient_id_stmnt,(patientid, id, id_type, preferred, location))
		cursoromrs7.execute("commit")
		cursoromrs7.close()
		
## Old Clinic ID 3
	
	id_type = '2'
	id = oldclinicid3
	preferred = '0'
	if len(id) > 0:
		ins_patient_id_stmnt = """INSERT INTO patient_identifier(patient_id, identifier, identifier_type, preferred, location_id, creator, date_created, voided, uuid) VALUES(%s, %s, %s, %s, %s, '1', NOW(), '0', uuid())"""
		cursoromrs7.execute(ins_patient_id_stmnt,(patientid, id, id_type, preferred, location))
		cursoromrs7.execute("commit")
		cursoromrs7.close()

## New Clinic ID
	
	id_type = '1'
	id = newclinicid
	preferred = '1'
	if len(id) > 0:
		ins_patient_id_stmnt = """INSERT INTO patient_identifier(patient_id, identifier, identifier_type, preferred, location_id, creator, date_created, voided, uuid) VALUES(%s, %s, %s, %s, %s, '1', NOW(), '0', uuid())"""
		cursoromrs7.execute(ins_patient_id_stmnt,(patientid, id, id_type, preferred, location))
		cursoromrs7.execute("commit")
		cursoromrs7.close()

## VISA number

	id_type = '3'
	id = visa_num
	preferred = '0' 
	if len(id) > 0:

		ins_patient_id_stmnt = """INSERT INTO patient_identifier(patient_id, identifier, identifier_type, preferred, location_id, creator, date_created, voided, uuid) VALUES(%s, %s, %s, %s, %s, '1', NOW(), '0', uuid())"""
		cursoromrs8.execute(ins_patient_id_stmnt,(patientid, id, id_type, preferred, location))
		cursoromrs8.execute("commit")
		cursoromrs8.close()

## Asylum number

	id_type = '7'
	id = asylum_num
	preferred = '0' 
	if len(id) > 0:
	
		ins_patient_id_stmnt = """INSERT INTO patient_identifier(patient_id, identifier, identifier_type, preferred, location_id, creator, date_created, voided, uuid) VALUES(%s, %s, %s, %s, %s, '1', NOW(), '0', uuid())"""
		cursoromrs9.execute(ins_patient_id_stmnt,(patientid, id, id_type, preferred, location))
		cursoromrs9.execute("commit")
		cursoromrs9.close()

## Hospital ID

#	id_type = '4'
#	id = hospital_id
#	preferred = '0' 
#	if len(id) > 0:
#	
#		ins_patient_id_stmnt = """INSERT INTO patient_identifier(patient_id, identifier, identifier_type, preferred, location_id, creator, date_created, voided, uuid) VALUES(%s, %s, %s, %s, %s, '1', NOW(), '0', uuid())"""
#		cursoromrs10.execute(ins_patient_id_stmnt,(patientid, id, id_type, preferred, location))
#		cursoromrs10.execute("commit")
#		cursoromrs10.close()

## Prisoner ID

	id_type = '5'
	id = prisoner_id
	preferred = '0' 
	if len(id) > 0:
	
		ins_patient_id_stmnt = """INSERT INTO patient_identifier(patient_id, identifier, identifier_type, preferred, location_id, creator, date_created, voided, uuid) VALUES(%s, %s, %s, %s, %s, '1', NOW(), '0', uuid())"""
		cursoromrs11.execute(ins_patient_id_stmnt,(patientid, id, id_type, preferred, location))
		cursoromrs11.execute("commit")
		cursoromrs11.close()


## Misc/incorrect format ID

#	id_type = '8'
#	id = prisoner_id
#	preferred = '0' 
#	if len(id) > 0:
#	
#		ins_patient_id_stmnt = """INSERT INTO patient_identifier(patient_id, identifier, identifier_type, preferred, location_id, creator, date_created, voided, uuid) VALUES(%s, %s, %s, %s, %s, '1', NOW(), '0', uuid())"""
#		cursoromrs11.execute(ins_patient_id_stmnt,(patientid, id, id_type, preferred, location))
#		cursoromrs11.execute("commit")
#		cursoromrs11.close()