import sqlite3
import decimal
import os
import sys
import csv
import win32com.client 
import fnmatch
import re
import datetime
import re

def xlsx_to_arr(xlsx_file, worksheet=1, row=0, col=0):
	arr = []
	xl = win32com.client.Dispatch('Excel.Application')

	wb = xl.Workbooks.Open(xlsx_file)
	ws =  wb.Worksheets(worksheet)
	rng = ws.UsedRange

	for i in range(rng.Row, rng.Rows.Count+1):
		row_arr = []
		for j in range(rng.Column, rng.Columns.Count+1):
			row_arr.append(str(ws.Cells(i, j))) 
			if j == col:
				break
		arr.append(row_arr)
		if i == row:
			break

	wb.Close(False)
	
	header = arr[0]
	return ','.join(header), arr[1:]

def csv_to_arr(csv_file):
	arr = []
	with open(csv_file, 'rU') as f:
		reader = csv.reader(f)
		arr = list(reader)
	header = ','.join(arr[0])
	arr = arr[1:]
	#print header
	return header, arr

def adapt_decimal(d):
	return str(d)

def convert_decimal(s):
	return decimal.Decimal(s)

def db_cur(source=":memory:"):
	# Register the adapter
	sqlite3.register_adapter(decimal.Decimal, adapt_decimal)

	# Register the converter
	sqlite3.register_converter("DECTEXT", convert_decimal)
	conn = sqlite3.connect(source, detect_types=sqlite3.PARSE_DECLTYPES)
	conn.row_factory = sqlite3.Row
	cur = conn.cursor()

	return conn, cur

def question_marks(st):
	question_marks = '?'
	for i in range(0, len(st.split(','))-1):
		question_marks = question_marks + ",?"
	return question_marks

def arr_to_xlsx(filename, header, arr):
	xl = win32com.client.Dispatch('Excel.Application')
	wb = xl.Workbooks.Add()
	ws = wb.Worksheets(1)

	for i, cell in enumerate(header.split(',')):
		ws.Cells(1,i+1).Value = cell

	for i, row in enumerate(arr):
		for j, cell in enumerate(row):
			if str(cell)[0] == '=':
				ws.Cells(i+2,j+1).Formula = cell
			else:
				ws.Cells(i+2,j+1).Value = cell
	
	ws.Range("G:Y").NumberFormat = "#,##0"
	ws.Range("I:I").NumberFormat = "#,##0.00"
	ws.Range("D:D").NumberFormat = "d/m/yyyy"
	ws.Columns.AutoFit()
	ws.Columns("J:S").Hidden = True
	ws.Columns("A:A").Hidden = True

	ws.Range("$A$1:$Z$2000").FormatConditions.Add(win32com.client.constants.xlExpression, "", '=AND($I1=0,$H1<>0,$C1<>"") ') 
	ws.Range("$A$1:$Z$2000").FormatConditions(1).Interior.ColorIndex = 6
	ws.Range("$A$1:$Z$2000").FormatConditions(1).StopIfTrue = False

	xl.DisplayAlerts = False
	wb.SaveAs(filename)
	xl.DisplayAlerts = True
	wb.Close(True)

	return

def arr_to_csv(file_name, header, data_arr):
	f1 = open(file_name, "w")
	f1.write(header+'\n')

	for data_row in data_arr:
		line = '' 
		for ele in data_row:
			line = line + str(ele) + ',' 

		line = line[:-1] + '\n'
		f1.write(line)    

	f1.close()
	return

def create_tbl(cur, tbl_name, header, arr = None, index_arr = None):
	cur.execute("""select count(*) FROM sqlite_master WHERE type='table' AND name = '%s' """ % (tbl_name))
	tbl_exists = cur.fetchone() 
	if tbl_exists[0] == 0:
		cur.execute("CREATE TABLE " + tbl_name + " (" + header.replace("id,", "id PRIMARY KEY,") + " );")
		if index_arr is not None:
			for index in index_arr:
				cur.execute("CREATE INDEX " + tbl_name + "_" + index + " ON " + tbl_name + " (" + index + ");")
		
	if arr is not None:
		cur.executemany("INSERT INTO " + tbl_name + " VALUES ("+question_marks(header)+")", arr)
	return 

def main(argv):
	t_date = "" if len(sys.argv) == 1 else sys.argv[1]
	pnl_header = "id,portfolio,instrument,reporting_date,conversion_factor,currency,daily_pos,net_pos,close_price,buy_qty_d,buy_amt_d,sell_qty_d,sell_amt_d,fee_d,buy_qty_y,buy_amt_y,sell_qty_y,sell_amt_y,fee_y,upld,rpld,tpld,uply,rply,tply"
	trd_files = []

	pnl_file = ""
	last_file = ""

	trd_filename = "Trade_Record_YYYYMMDD.csv"
	ins_filename = "Instrument_Pos_YYYYMMDD.csv"
	pnl_filename = "pnl_YYYYMMDD.xlsx"

	pnl_dir = "S:\\pnl"
	input_dir = os.path.dirname(os.path.abspath(__file__))

	cur_pnl_dt = ""
	last_trd_dt = ""

	regex_dt = "[^_]+[0-9]"

	conn, cur = db_cur()

	if t_date != "":
		if os.path.exists(os.path.join(pnl_dir, pnl_filename.replace("YYYYMMDD", t_date))):
			os.remove(os.path.join(pnl_dir, pnl_filename.replace("YYYYMMDD", t_date)))

	for f in os.listdir(pnl_dir):
		if fnmatch.fnmatch(f, pnl_filename.replace("YYYYMMDD", "*")):
			if f > pnl_file:
				pnl_file = f 

	create_tbl(cur, "pnl", pnl_header)
	if pnl_file == "":
		cur_pnl_dt = "00000000"
		create_tbl(cur, "pos", pnl_header)
	else:
		cur_pnl_dt = re.search(regex_dt, pnl_file).group(0) 
		pnl_header, pnl_arr = xlsx_to_arr(os.path.join(pnl_dir,pnl_file), 1 )
		create_tbl(cur, "pos", pnl_header, pnl_arr)
	
	if t_date == "":
		for f in os.listdir(input_dir):
			if fnmatch.fnmatch(f, trd_filename.replace("YYYYMMDD", "*")) and f > trd_filename.replace("YYYYMMDD", cur_pnl_dt):
				trd_files.append(f)
	else:
		cur_pnl_dt = t_date
		for f in os.listdir(input_dir):
			if fnmatch.fnmatch(f, trd_filename.replace("YYYYMMDD", "*")) and f == trd_filename.replace("YYYYMMDD", cur_pnl_dt):
				trd_files.append(f)

	trd_files.sort()
	last_trd_dt = re.search(regex_dt, trd_files[-1]).group(0) 

	for trd_file in trd_files:
		trd_header, trd_arr = csv_to_arr(os.path.join(input_dir, trd_file))
		create_tbl(cur, "trd", trd_header, trd_arr)

	pnl_file = pnl_filename.replace("YYYYMMDD", last_trd_dt)
	ins_file = ins_filename.replace("YYYYMMDD", last_trd_dt)
	ins_header, ins_arr = csv_to_arr(os.path.join(os.path.dirname(os.path.abspath(__file__)), ins_file))
	create_tbl(cur, "ins", ins_header, ins_arr)

	# Long Short Model
	cur.execute("""insert or replace into pos (id,portfolio,instrument,reporting_date,conversion_factor,currency,close_price,daily_pos,net_pos,buy_qty_d,buy_amt_d,sell_qty_d,sell_amt_d,fee_d,buy_qty_y,buy_amt_y,sell_qty_y,sell_amt_y,fee_y)
		select REPLACE((trd.portfolio||'_'||trd.instrument),' ','_'), trd.portfolio, trd.instrument, trd.reporting_date, 
			case when cast(ins.conversion_factor as integer) > 0 then ins.conversion_factor else 1 end, 
			ins.currency, ins.market_price, sum(trd.quantity), pos.net_pos,
			sum(case when cast(trd.quantity as integer) > 0 then trd.quantity else 0 end), 
			sum(case when cast(trd.quantity as integer) > 0 then trd.quantity*trd.price else 0 end),
			sum(case when cast(trd.quantity as integer) < 0 then trd.quantity else 0 end), 
			sum(case when cast(trd.quantity as integer) < 0 then trd.quantity*trd.price else 0 end),
			sum(trd.fee), pos.buy_qty_y, pos.buy_amt_y, pos.sell_qty_y, pos.sell_amt_y, pos.fee_y
		from trd 
		left join ins on trd.portfolio = ins.portfolio and trd.instrument = ins.instrument
		left join pos on pos.portfolio = pos.portfolio and trd.instrument = pos.instrument
		group by trd.portfolio, trd.instrument """)

	# Add daily records to yearly records
	# upld =IF(G2=0,0,(I2-IF(G2>0,K2/J2,M2/L2))*G2)*E2
	# rpld =IF(J2*L2=0,0,MIN(ABS(J2),ABS(L2))*(M2/L2-K2/J2))*E2-N2
	# uply =IF(H2=0,0,(I2-IF(H2>0,P2/O2,R2/Q2))*H2)*E2
	# rply =IF(O2*Q2=0,0,MIN(ABS(O2),ABS(Q2))*(R2/Q2-P2/O2))*E2-S2

	cur.execute("""insert into pnl (id,portfolio,instrument,reporting_date,conversion_factor,currency,close_price,daily_pos,net_pos,buy_qty_d,buy_amt_d,sell_qty_d,sell_amt_d,fee_d,buy_qty_y,buy_amt_y,sell_qty_y,sell_amt_y,fee_y) 
		select id,portfolio,instrument,reporting_date,conversion_factor,currency,close_price,daily_pos,net_pos,buy_qty_d,buy_amt_d,sell_qty_d,sell_amt_d,fee_d,buy_qty_y,buy_amt_y,sell_qty_y,sell_amt_y,fee_y from pos""")

	cur.execute("""update pnl set buy_qty_y=ifnull(buy_qty_y,0)+buy_qty_d, buy_amt_y=ifnull(buy_amt_y,0)+buy_amt_d, 
		sell_qty_y=ifnull(sell_qty_y,0)+sell_qty_d, sell_amt_y=ifnull(sell_amt_y,0)+sell_amt_d, net_pos=ifnull(net_pos,0)+daily_pos,
		upld='=IF(G'||(rowid+1)||'=0,0,(I'||(rowid+1)||'-IF(G'||(rowid+1)||'>0,K'||(rowid+1)||'/J'||(rowid+1)||',M'||(rowid+1)||'/L'||(rowid+1)||'))*G'||(rowid+1)||')*E'||(rowid+1),
		rpld='=IF(J'||(rowid+1)||'*L'||(rowid+1)||'=0,0,MIN(ABS(J'||(rowid+1)||'),ABS(L'||(rowid+1)||'))*(M'||(rowid+1)||'/L'||(rowid+1)||'-K'||(rowid+1)||'/J'||(rowid+1)||'))*E'||(rowid+1)||'-N'||(rowid+1),
		uply='=IF(H'||(rowid+1)||'=0,0,(I'||(rowid+1)||'-IF(H'||(rowid+1)||'>0,P'||(rowid+1)||'/O'||(rowid+1)||',R'||(rowid+1)||'/Q'||(rowid+1)||'))*H'||(rowid+1)||')*E'||(rowid+1),
		rply='=IF(O'||(rowid+1)||'*Q'||(rowid+1)||'=0,0,MIN(ABS(O'||(rowid+1)||'),ABS(Q'||(rowid+1)||'))*(R'||(rowid+1)||'/Q'||(rowid+1)||'-P'||(rowid+1)||'/O'||(rowid+1)||'))*E'||(rowid+1)||'-S'||(rowid+1),
		fee_y=ifnull(fee_y,0)+fee_d, tpld='=T'||(rowid+1)||'+U'||(rowid+1), tply='=W'||(rowid+1)||'+X'||(rowid+1)
		""")
	
	cur.execute("""update pnl set buy_qty_d=0, buy_amt_d=0, sell_qty_d=0, sell_amt_d=0, daily_pos=0, fee_d=0
		where reporting_date < (select max(reporting_date) from pnl)""")

	cur.execute("select * from pnl order by rowid")
	pnl_arr = cur.fetchall()
	for row in pnl_arr:
		print row
	arr_to_xlsx(os.path.join(os.path.dirname(os.path.abspath(__file__)), pnl_file), pnl_header, pnl_arr)

	conn.commit()
	conn.close()
	return

# trd_filename = "Trade_Record_YYYYMMDD.csv"
# regex_dt = "[^_]+[0-9]"
# input_dir = "D:\\Temp\\Trade_Records"
# name_arr = []
# for f in os.listdir(input_dir):
# 	if fnmatch.fnmatch(f, trd_filename.replace("YYYYMMDD", "*")):
# 		name_arr.append(re.search(regex_dt, f).group(0) )

# name_arr.sort()
# for name in name_arr:
# 	print name

main(sys.argv)

