#!/usr/bin/python
import re
import glob
import string
import os
import pg
from time import strftime
import sys
import getopt
import shutil

def check_duplicate(accountid,conn,cli):
    qry = "select count(*) as cnt from \"Gnp\" where account_id = '%s' and dialed_no = '%s'" % (accountid,cli)
    for gnp_row in conn.query(qry).dictresult():
    	if gnp_row['cnt'] >=1:
        	return 'T'
		print 'T'
	else:
		return 'F'
		print 'F'

def add_cli(accountid,cli,owner,conn,actiondate,endcli,currentfile):
	if endcli==None:
		if actiondate==strftime("%Y%m%d") or actiondate==None:
			qry = "INSERT INTO \"Gnp\" (account_id,dialed_no,owner,comments) VALUES ('%s','%s','%s','%s');" % (accountid,cli,owner,strftime("%a, %d %b %Y %H:%M:%S"))
			print qry
			conn.query(qry)
		else:
			print "actiondate missmatch"

def del_cli(accountid,cli,owner,conn,actiondate,endcli,currentfile):
	if endcli==None:
		if actiondate==strftime("%Y%m%d") or actiondate==None:
			qry = "delete from \"Gnp\" where account_id like '%s' and dialed_no like '%s';" % (accountid,cli)
			print qry
			conn.query(qry)
			message=strftime("%d %b %Y %H:%M:%S") + ': Deleting ' + cli
	
def write_apa_file(activity,cli,vendor,track,provision_code,provision_status,currentfile):
	logfile=basedir + '/Reports/' + currentfile + '.apa'
	print logfile
	log=open(logfile,'a')
	pos_start=log.tell()

	#Activity Start 1 End 1 Type Char
	log.write(activity)
	pos=log.tell()

	#cli Start 2 End 21 Type Num
	pos=log.tell()
	pos_new=pos_start+21-len(cli)
	while pos < pos_new:
		log.write('0')
		pos=log.tell()
	log.write(cli)

	#cli-type Start 22 End 22 Type Num
	pos=log.tell()
	pos_new=pos_start+22-1
	while pos < pos_new:
		log.write('0')
		pos=log.tell()
	log.write('1')

	#Account Number Start 23 End 37 Type Char
	log.write(vendor)
	pos=log.tell()
	pos_new=pos_start+37-len(vendor)
	while pos < pos_new:
		log.write(' ')
		pos=log.tell()

	#Action Date Start 38 End 45 Type Num
	pos=log.tell()
	print strftime("%Y%m%d")
	print len(strftime("%Y%m%d"))
	pos_new=pos_start+45-len(strftime("%Y%m%d"))
	while pos < pos_new:
		log.write('0')
		pos=log.tell()
	log.write(strftime("%Y%m%d"))

	#Time Start 46 End 51 Type Num
	pos=log.tell()
	pos_new=pos_start+51-len(strftime("%H%M%S"))
	while pos < pos_new:
		log.write('0')
		pos=log.tell()
	log.write(strftime("%H%M%S"))

	#Target PSTN Start 52 End 71 Type Num
	pos=log.tell()
	pos_new=pos_start+71
	while pos < pos_new:
		log.write('0')
		pos=log.tell()

	#PAL Flag Start 72 End 74 Type Char
	pos=log.tell()
	pos_new=pos_start+74
	while pos < pos_new:
		log.write(' ')
		pos=log.tell()

	#PAL PSTN Start 75 End 94 Type Num
	pos=log.tell()
	pos_new=pos_start+94
	while pos < pos_new:
		log.write('0')
		pos=log.tell()

	#Customer Tracking Code Start 95 End 109 Type Char
	pos=log.tell()
	pos_new=pos_start+109-len(track)
	while pos < pos_new:
		log.write(' ')
		pos=log.tell()
	log.write(track)

	#Provision Code Start 110 End 112 Type Num
	pos=log.tell()
	pos_new=pos_start+112-len(provision_code)
	while pos < pos_new:
		log.write('0')
		pos=log.tell()
	log.write(provision_code)

	#Provision Status Start 113 End 362 Type Char
	log.write(provision_status)
	pos=log.tell()
	pos_new=pos_start+362-len(provision_status)
	while pos < pos_new:
		log.write(' ')
		pos=log.tell()

	log.write('\n')
	log.close()

def verify_action(duplicate,action,accountid,cli,actiondate,endcli,vendor,track,currentfile):
   if endcli==None:
	   if actiondate==strftime("%Y%m%d") or actiondate==None:
		qry = "select comments as result from \"Gnp\" where account_id = '%s' and dialed_no = '%s'" % (accountid,cli)
		qry2 = "select count(*) as cnt from \"Gnp\" where account_id = '%s' and dialed_no = '%s'" % (accountid,cli)
		if duplicate=="T":
			comments=conn.query(qry).dictresult()[0]['result']
		elif duplicate=="0":
			if conn.query(qry2).dictresult()[0]['cnt'] > 0:
				comments=conn.query(qry).dictresult()[0]['result']
				print comments
		cnt=conn.query(qry2).dictresult()[0]['cnt']
		if action=="add":
			if duplicate=="T":
				provision_status="CLI is already provisioned, date of provisioning was " + comments + ", tracking number was " + track + ", filename " + currentfile + ".cps"
				provision_code="2"
				print provision_status
				activity='A'
				write_apa_file(activity,cli,vendor,track,provision_code,provision_status,currentfile)
			else:
				provision_status="CLI successfully added on " + comments + ", tracking number was " + track + ", filename " + currentfile + ".cps"
				provision_code="1"
				print provision_status
				activity='A'
				write_apa_file(activity,cli,vendor,track,provision_code,provision_status,currentfile)
		if action=="del":
			if duplicate=="F":
				provision_status="CLI can't be deleted because it is not provisioned" + ", tracking number was " + track + ", filename " + currentfile + ".cps"
				provision_code="2"
				print provision_status
				activity='D'
				write_apa_file(activity,cli,vendor,track,provision_code,provision_status,currentfile)
			else:
				provision_status="CLI successfully deleted" + ", tracking number was " + track + ", filename " + currentfile + ".cps"
				provision_code="1"
				print provision_status
				activity='D'
				write_apa_file(activity,cli,vendor,track,provision_code,provision_status,currentfile)
	   else:
		if action=="del":
			provision_status="Action Date not supported, please submit on appropriate Date"
			provision_code="1"
			activity='D'
			write_apa_file(activity,cli,vendor,track,provision_code,provision_status,currentfile)
		else:
			provision_status="Action Date not supported, please submit on appropriate Date"
			provision_code="1"
			activity='A'
			write_apa_file(activity,cli,vendor,track,provision_code,provision_status,currentfile)

   else:
	if action=="del":
		provision_status="CLI Range not supported, please submit single CLIs"
		provision_code="1"
		activity='D'
		write_apa_file(activity,cli,vendor,track,provision_code,provision_status,currentfile)
	else:
		provision_status="CLI Range not supported, please submit single CLIs"
		provision_code="1"
		activity='A'
		write_apa_file(activity,cli,vendor,track,provision_code,provision_status,currentfile)

def query_vendor(vendor,conn):
    qry = "select a.id as accountid, g.id as groupid, c.id as customerid, c.customer_name as customername FROM \"Customer\" c, \"Account\" a, \"Group\" g WHERE a.group_id = g.id and g.customer_id = c.id and c.customer_number || a.account_number = '%s';" % (vendor)
    credentials=conn.query(qry).dictresult()
    return credentials[0]['accountid'], credentials[0]['customername']

def parseline(line,conn,currentfile):
	endcli=None
	actiondate=None
	linefeed=re.compile("\n")
	line=linefeed.sub("",line)
	fields=line.split(",")
	for field in fields:
		if field[0:3]=="001":
			vendor=field[4:-1]
			details=query_vendor(vendor,conn)
			accountid=details[0]
			owner=details[1]
		
		if field[0:3]=="009":
			cli=field[4:-1]
			print "cli=" + cli

		if field[0:3]=="024":
                	track=field[4:-1]
	                print "trackingno=" + track

		if field[0:3]=="007":
			if field[4:-1]=="001":
				action="add"
				print "action=" + action
			elif  field[4:-1]=="008":
				action="del"
				print "action=" + action

		if field[0:3]=="008":
                	actiondate=field[4:-1]
	                print "actiondate=" + actiondate

		if field[0:3]=="010":
                	endcli=field[4:-1]
	                print "endcli=" + endcli

	print accountid
	if check_duplicate(accountid,conn,cli) == 'T':
		if action=="del":
			del_cli(accountid,cli,owner,conn,actiondate,endcli,currentfile)
			duplicate='0'
			verify_action(duplicate,action,accountid,cli,actiondate,endcli,vendor,track,currentfile)
		elif action=="add":
			duplicate='T'
			verify_action(duplicate,action,accountid,cli,actiondate,endcli,vendor,track,currentfile)
		else:
			print "Somethings fishy with the file, the Request Type is wrong"
	else:
		if action=="add":		
			add_cli(accountid,cli,owner,conn,actiondate,endcli,currentfile)	
			duplicate='0'
			verify_action(duplicate,action,accountid,cli,actiondate,endcli,vendor,track,currentfile)
		elif action=="del":
			duplicate='F'
			verify_action(duplicate,action,accountid,cli,actiondate,endcli,vendor,track,currentfile)
		else:
			print "Somethings fishy with the file, the Request Type is wrong"

conn = pg.connect("test_db","127.0.0.1",5432,None,None,"postgresuser","123password")

args = getopt.getopt(sys.argv[1:], 'customer')
customer=args[1][0]
basedir = "/root/python_tut/cps/" + customer[0:-3] + "/" + customer[12:]
workdir = basedir + "/loaded"
tempdir = basedir + "/loading"

for file in glob.glob(tempdir + '/CLIs*.cps'):
	(base, extension) = os.path.splitext(file)
	filenew=workdir+file[len(tempdir):]
	print filenew
	os.rename(file, filenew)

print workdir
cdr_files = glob.glob(workdir + '/CLIs*.cps')
for nameFile in cdr_files:
    file=open(nameFile, 'r')
    print 'import file: ' + nameFile
    pathlength=len(workdir)
    currentfile=nameFile[len(workdir):-4]
    nr = 0
    exist = 'F'
    for line in file:
	#parseline(line,conn,currentfile)
        nr += 1