#!/usr/bin/python
import sys
import os
import string
import reaper
import MySQLdb
"""
def swap(A, B):
	temp = A
	A = B
	B = A

fp = open('ColXCvi.txt')
fout = open('ColXCvi2.txt', "wb")
line = fp.readline()
i = 0
while line:
	line2 = map(string.strip, string.split(line.strip()))
	print len(line2)
	if i == 0:
		line2 = line2[:7] + map(lambda X:'ColXCvi' + X, line2[7:])
	X = line2[1]
	line2[1] = line2[2]
	line2[2] = X
	line2 = line2[:5] + line2[7:]
	fout.write(string.join(line2[1:], "\t") + "\n")
	line = fp.readline()
	i += 1
	
fout.close()
fp.close()
"""
"""
try:
	#import getpass
	#passwd = getpass.getpass('Please enter mysql password here : ')
	con = MySQLdb.Connect(db='db_webqtl',user='webqtlupd',passwd='webqtl', host="opteron")
	cursor = con.cursor()
	print "You have successfully connected to mysql.\n"
except:
	print "You entered incorrect password.\n"
	sys.exit(0)

for geno in ("ColXBur.geno", "ColXCvi.geno"):
	a = reaper.Dataset()
	a.read(geno)
	#print a.prgy, len(a.prgy)
	for item in a.prgy:
		cursor.execute("insert into Strain(Name, SpeciesId) values(%s, 3)", item)
		
cursor.close()

try:
	#import getpass
	#passwd = getpass.getpass('Please enter mysql password here : ')
	con = MySQLdb.Connect(db='db_webqtl',user='webqtlupd',passwd='webqtl', host="opteron")
	cursor = con.cursor()
	print "You have successfully connected to mysql.\n"
except:
	print "You entered incorrect password.\n"
	sys.exit(0)
cursor.execute("select max(Id)+1 from Geno")
maxId = cursor.fetchone()[0]

for geno in ("ColXBur.geno", "ColXCvi.geno"):
	a = reaper.Dataset()
	a.read(geno)
	#print a.prgy, len(a.prgy)
	for chr in a:
		for locus in chr:
			print geno, locus.name, chr.name, locus.Mb
			try:
				cursor.execute("insert into Geno(Id, Name, Chr, MB_UCSC, chr_num, Source, Source2) values(%s, %s, %s, %s, %s, %s, %s)", (maxId, locus.name, chr.name, locus.Mb, chr.name, "Institute for Agronomical Research", "Institute for Agronomical Research"))
				maxId += 1
			except:
				pass
cursor.close()
mysql> select max(Id) from Data;
+----------+
| max(Id)  |
+----------+
| 22163234 |
+----------+
1 row in set (0.00 sec)

mysql> select max(DataId) from GenoXRef;
+-------------+
| max(DataId) |
+-------------+
|    16098047 |
+-------------+
1 row in set (0.04 sec)

mysql> select * from Strain order by Id desc limit 5;
+------+------------+-----------+--------+
| Id   | Name       | SpeciesId | Symbol |
+------+------------+-----------+--------+
| 1828 | ColXCvi499 |         3 | NULL   |
| 1827 | ColXCvi497 |         3 | NULL   |
| 1826 | ColXCvi496 |         3 | NULL   |
| 1825 | ColXCvi495 |         3 | NULL   |
| 1824 | ColXCvi494 |         3 | NULL   |
+------+------------+-----------+--------+

"""

"""
try:
	#import getpass
	#passwd = getpass.getpass('Please enter mysql password here : ')
	con = MySQLdb.Connect(db='db_webqtl',user='webqtlupd',passwd='webqtl', host="opteron")
	cursor = con.cursor()
	print "You have successfully connected to mysql.\n"
except:
	print "You entered incorrect password.\n"
	sys.exit(0)

freezeId = 14
for geno in ("ColXBur.geno", "ColXCvi.geno"):
	a = reaper.Dataset()
	a.read(geno)
	strainIds = []
	for strain in a.prgy:
		cursor.execute("select Id from Strain where Name = '%s' and SpeciesId=3" % strain)
		strainIds.append(cursor.fetchone()[0])
	for chr in a:
		for locus in chr:
			cursor.execute("select max(Id)+1 from Data")
			dataId = cursor.fetchone()[0]
			cursor.execute("select Id from Geno where Name = '%s'" % locus.name)
			GenoId = cursor.fetchone()[0]
			#print geno, locus.name, chr.name, locus.Mb, dataId, GenoId
			for i, item in enumerate(locus.genotype):
				cursor.execute("insert into Data values(%s, %s, %s)" ,(dataId, strainIds[i], item))
			cursor.execute("insert into GenoXRef values(%s, %s, %s)" , (freezeId, GenoId, dataId))
	freezeId -= 1
cursor.close()
"""

try:
	#import getpass
	#passwd = getpass.getpass('Please enter mysql password here : ')
	con = MySQLdb.Connect(db='db_webqtl',user='webqtlupd',passwd='webqtl', host="opteron")
	cursor = con.cursor()
	print "You have successfully connected to mysql.\n"
except:
	print "You entered incorrect password.\n"
	sys.exit(0)

freezeId = 14
for geno in ("ColXBur.geno", "ColXCvi.geno"):
	values = [-1, 1, 0, 0]
	if geno == "ColXBur.geno":
		strains = ["Col-0", "Bur-0", "ColXBurF1", "BurXColF1"]
	else:
		strains = ["Col-0", "Cvi", "ColXCviF1", "CviXColF1"]
	strainIds = []
	for strain in strains:
		cursor.execute("select Id from Strain where Name = '%s' and SpeciesId=3" % strain)
		strainIds.append(cursor.fetchone()[0])
	print strainIds
	cursor.execute("select DataId from GenoXRef where GenoFreezeId = %d" % freezeId)
	results = cursor.fetchall()
	for dataId in results:
		for i, strainId in enumerate(strainIds):
			#print "insert into Data values(%s, %s, %s)" % (dataId[0], strainId, values[i])
			cursor.execute("insert into Data values(%s, %s, %s)" ,(dataId[0], strainId, values[i]))
	freezeId -= 1
cursor.close()