#!/usr/bin/python

import sys, os, cmd, sqlite3, datetime, shlex,re
from getopt import getopt
from os.path import expanduser
import readline, imp
ops,args = getopt(sys.argv[1:],'c:d:')
ops=dict(ops)
confpath = ops.get('-c',expanduser('~/.climrc'))
if not os.access(confpath,os.F_OK):
    print "config file don't exists, creating...",
    cf=open(confpath,'w')
    cf.write("""
# clim configuration

dbpath = '~/.clim.db'

acc='00'
max_lines=40

fmts={
'prod_code':'\033[34;1m%%(%s)-10.10s\033[0m',
'prod_code_full':'\033[34;1m%%(%s)s\033[0m',
'acc_id':'\033[35;1m%%(%s)-10.10s\033[0m',
'prod_name':'\033[36m%%(%s)-16s\033[0m',
'note':'\033[36m%%(%s)-16s\033[0m',
'amount':'\033[37m%%(%s)8.2f\033[0m',
'date':'\033[0m%%(%s)s',
'header':'\033[34;1m',
'param':'\033[35;1m%%(%s)s\033[0m',
}
    """)
    cf.close()
    print "Done"
config = imp.load_source('config',confpath)


clim_db=expanduser(ops.get('-d',config.dbpath))
spln='\033[34m--------------------------------------\033[0m'

class myerr(Exception): pass

def check(cond, msg):
    if not cond:
        raise myerr(msg)

class App(cmd.Cmd):
    prompt="$>"
    def __init__(self):
        cmd.Cmd.__init__(self)
        dt=datetime.date.today().isoformat()
        self.opt={
                'date': dt,
                'date_from':dt,
                'date_to':dt,
                'acc': config.acc,
                'max_lines': config.max_lines
                }
        db_exists=os.access(clim_db,os.F_OK)
        self.con=sqlite3.connect(clim_db)
        if not db_exists:
            self.create_db()
        self.fmts=config.fmts
        self.setPrompt()
    def setPrompt(self):
        self.prompt="\033[31m%(date)s:%(acc)s> \033[0m" % self.opt
    def _fx(self,m):
        m=m.group(1)
        if ':' in m:
            fn,dt = m.split(':')
        else:
            dt,fn=m,m
        return self.fmts[dt] % fn
    def ffmt(self,fmt):
        pat=re.compile(r"\$\(([a-z_:]+)\)")
        return re.sub(pat,self._fx,fmt).replace('[[',self.fmts['header']).replace(']]','\033[0m')
    def create_db(self):
        script="""
        CREATE TABLE prod(
        prod_id integer primary key,
        prod_code text,
        prod_name text, 
        dt int, 
        prod_group text);

        CREATE UNIQUE INDEX unique_prod_code on prod(prod_code);

        CREATE UNIQUE INDEX unique_prod_name on prod(prod_name);

        CREATE TABLE op(
        op_id integer primary key,
        acc_id text,
        prod_code text,
        op_date text,
        amount float,
        note text,
        dt int);

        CREATE INDEX op_op_date on op(op_date);
        """
        print "Initializing database"
        for sql in script.split('\n\n'):
            print sql
            self.execute(sql)
    def gen_completions(self,text):
        s=readline.get_line_buffer()
        grp=s and (s[-1]!=' ') and s.split()[-1] or ''
        pp=grp.rfind('.')
        if pp==-1:
            prod_group=''
        else:
            prod_group=grp[:pp+1]
        sql="""select prod_code from prod where prod_group='%s' and prod_code like '%s%%'""" % (prod_group,grp)
        grplen=len(prod_group)
        res=[]
        for f in self.select(sql):
            s=f[0][grplen:]
            if s[-1]!='.':
                s+=' '
            res.append(s)
        #return [f[0][grplen:] for f in self.select(sql)]
        return res
    def complete(self,text,state):
        if state==0:
            self.completions=self.gen_completions(text)+[None]
        return self.completions[state]
    def select(self,sql):
        cur=self.con.cursor()
        cur.execute(sql)
        return cur.fetchall()
    def select_d(self,sql):
        cur=self.con.cursor()
        cur.execute(sql)
        fields=[f[0] for f in cur.description]
        return [dict(zip(fields,r)) for r in cur]
    def execute(self,sql):
        cur=self.con.cursor()
        cur.execute(sql)
        self.con.commit()
    def dest(self,lst):
        if len(lst)>int(self.opt['max_lines']) and self.interactive:
            return os.popen('less','w')
        else:
            return None
    def _cmd(self,cmd,c,usage):
        try:
            cl=shlex.split(c)
            check(len(cl),usage)
            cc=cl[0]
            meth=getattr(self,'_%s_%s' % (cmd, cc))
            meth(cl[1:])
        except AttributeError:
            print usage
        except myerr, e:
            print e
        return 0

    def do_prod(self,c):
        self._cmd('prod',c,"Usage: prod ( ls | add | del ) <args>")
    do_p=do_prod
    def do_op(self,c):
        self._cmd('op',c,"Usage: op ( ls | add | del ) <args>")
    do_o=do_op
    def do_rep(self,c):
        self._cmd('rep',c,"Usage: rep ( prod | acc )")
    do_r=do_rep

    def _rep_prod(self,args):
        prod_group=args and args[0] or ''
        dt,ct = 0.0, 0.0
        sql = """select p.prod_code as prod_code, p.prod_name as prod_name, 
            sum(o.amount*o.dt) as dt,
            sum(o.amount*(1-o.dt)) as ct
            from op o
              join prod p on (o.prod_code like p.prod_code || '%%')
            where o.op_date between '%(date_from)s' and '%(date_to)s' and acc_id='%(acc)s' and p.prod_group='%(prod_group)s'
            group by p.prod_code, p.prod_name
            order by p.prod_code, p.prod_name
            """ % {
                    'date_from':self.opt['date_from'],
                    'date_to':self.opt['date_to'],
                    'acc':self.opt['acc'],
                    'prod_group':prod_group
                   }
        data=self.select_d(sql)
        dest=self.dest(data)
        fmt=self.ffmt("$(prod_code)  $(dt:amount)  $(ct:amount)  %(per)4.1f%%  $(prod_name)")
        try:
            print >>dest, self.ffmt("[[ Report by product. Period from $(date_from:param)[[ to $(date_to:param)]]") % self.opt
            if prod_group:
                print >>dest, self.ffmt("[[ Product group: $(group:param)]]") % {'group':prod_group}
            print >>dest, spln
            tot=sum([dd['ct'] for dd in data])
            for dd in data:
                if prod_group:
                    dd['prod_code']=dd['prod_code'][len(prod_group):]
                dt+=dd['dt']
                ct+=dd['ct']
                dd['per']=dd['ct']*100/tot
                print >>dest, fmt % dd
            print >>dest, spln
            print >>dest, self.ffmt("[[Totals:]]     %8.2f  %8.2f") % (dt,ct)
        except IOError:
            pass
               
    def _rep_acc(self,args):
        rb, dt,ct = 0.0, 0.0, 0.0
        sql = """
            select acc_id, sum(rest) as rest, sum(dt) as dt, sum(ct) as ct, sum(rest+dt-ct) as re
            from (
                select acc_id, 
                0 as rest,
                amount*dt as dt,
                amount*(1-dt) as ct
                from op 
                where op_date between '%(date_from)s' and '%(date_to)s'
                union all
                select acc_id,
                amount*(2*dt-1) as rest,
                0 as dt,
                0 as ct
                from op
                where op_date <'%(date_from)s') s
            group by acc_id
            order by acc_id
            """ % self.opt 
        data=self.select_d(sql)
        dest=self.dest(data)
        try:
            print >>dest, self.ffmt("[[Report by accounts. Period from $(date_from:param) [[to $(date_to:param)]]") % self.opt
            print >>dest, spln
            fmt=self.ffmt("$(acc_id)  $(rest:amount)  $(dt:amount)  $(ct:amount)  $(re:amount)")
            for dd in data:
                rb+=dd['rest']
                dt+=dd['dt']
                ct+=dd['ct']
                print >>dest, fmt % dd
            print >>dest, spln
            print >>dest, self.ffmt("[[Totals:]]     %8.2f  %8.2f  %8.2f  %8.2f") % (rb,dt,ct,rb+dt-ct)
        except IOError:
            pass


    def _prod_ls(self,args):
        prod_group=args and args[0] or ''
        data=self.select_d("select prod_code,prod_name,dt from prod where prod_group='%s' order by prod_code" % prod_group)
        dest=self.dest(data)
        fmt=self.ffmt("$(prod_code)  %(dt)d  $(prod_name)")
        try:
            print >>dest, self.ffmt("[[Product list. Group: $(group:param)]]") % {'group':prod_group}
            print >>dest, spln
            for r in self.select_d("select prod_code,prod_name,dt from prod where prod_group='%s' order by prod_code" % prod_group):
                r['prod_code']=r['prod_code'][len(prod_group):]
                print >>dest, fmt % r
        except IOError:
            pass
    def _prod_add(self,args):
        usage="Usage: prod add [-d] <prod_code> <prod_name>"
        ops,args = getopt(args,'d')
        check(len(args)==2, usage)
        ops=dict(ops)
        if '-d' in ops:
            dt=1
        else:
            dt=0
        prod_code=args[0]
        group=prod_code.rstrip('.')
        pp=group.rfind('.')
        if pp==-1:
            group=''
        else:
            group=group[:pp+1]
        chk=self.select("select prod_code from prod where prod_code='%s'" % group)
        check(chk or (group==''), "Product group %s don't exists" % group)
        args={'prod_code':prod_code,'prod_group':group,'prod_name':args[1],'dt':dt}
        self.execute("insert into prod(prod_code,prod_group,prod_name,dt) values('%(prod_code)s','%(prod_group)s','%(prod_name)s', %(dt)d)" % args)
        print "Product %s added" % args['prod_name']
    def _prod_dump(self,args):
        sql="""select prod_code,prod_name,dt from prod order by prod_code"""
        for r in self.select_d(sql):
            r['dtopt']=r['dt']==1 and '-d' or ''
            print "prod add %(dtopt)s %(prod_code)s '%(prod_name)s'" % r
    def _prod_del(self,args):
        usage="prod del <prod_code>"
        check(len(args)==1, usage)
        self.execute("delete from prod where prod_code='%s'" % args[0])
        print "Product %s deleted." % args[0]
    def _op_ls(self,args):
        fmt=self.ffmt('%(op_id)4d  $(op_date:date)  $(acc_id)  $(amount)  $(prod_code:prod_code_full)  $(note)')
        prod_code=args and args[0]+'%' or '%'
        dt, ct = 0.0, 0.0
        sql="""select op_id,acc_id,prod_code,op_date,amount,dt,note 
            from op 
            where op_date between '%s' and '%s' and prod_code like '%s'
            order by op_date,acc_id,op_id
            """ % (self.opt['date_from'], self.opt['date_to'], prod_code)
        data=self.select_d(sql)
        dest=self.dest(data)
        try:
            print >>dest, self.ffmt("[[Operations]]")
            print >>dest, spln
            for d in data:
                amount=float(d['amount'])
                fdt=d['dt']
                dt+=amount*fdt
                ct+=amount*(1-fdt)
                print >>dest, fmt % d
            print >>dest, spln
            print >>dest, self.ffmt("[[Totals: dt=]]%.2f  [[ct=]]%.2f") %(dt,ct)
        except IOError:
            pass
    def _op_dump(self,args):
        sql="""select op_date,acc_id,prod_code,amount,note
            from op
            where op_date between '%(date_from)s' and '%(date_to)s'
            """ % self.opt
        for r in self.select_d(sql):
            print "op add -d %(op_date)s -a %(acc_id)s %(prod_code)s %(amount).2f '%(note)s'" % r
    def _op_add(self,args):
        usage="Usage: op add [-d <op_date>] [-a acc] <prod_code> <amount> [note]"
        ops,args = getopt(args,'a:d:')
        check(len(args)>1,usage)
        ops=dict(ops)
        prod=self.select("select prod_code,dt from prod where prod_code='%s'" % args[0])
        check(prod,'No such product code: %s' % args[0])
        prod_code,dt = prod[0]
        if len(args)>2:
            note=args[2]
        else:
            note=''
        self.execute("""insert into op(acc_id,prod_code,op_date,amount,dt,note) 
            values ('%(acc_id)s','%(prod_code)s','%(op_date)s',%(amount)s,%(dt)d,'%(note)s')
            """ % {
                    'acc_id': ops.get('-a',self.opt['acc']),
                    'op_date': ops.get('-d',self.opt['date']),
                    'prod_code': prod_code,
                    'amount': args[1],
                    'dt':dt,
                    'note': note
                  })
    def _op_del(self,args):
        usage="Usage: op del <prod_code>"
        check(len(args)==1, usage)
        self.execute("delete from op where op_id='%s'" % args[0])
        print "Operation %s deleted." % args[0]
    def do_show(self,c):
        usage="Usage: show [<option_name>]"
        cl=c.split()
        opts=self.opt.keys()
        if cl:
            opts=cl
        for o in opts:
            if o[0]!='_':
                if o in self.opt:
                    print "%s = %s" % (o, self.opt[o])
                else:
                    print "Unknown option:%s" % o
    def do_set(self,c):
        usage="Usage: set <option> <value>"
        cl=shlex.split(c)
        if len(cl)==2:
            # set option value
            if cl[0] in self.opt:
                self.opt[cl[0]]=cl[1]
            else:
                print "Unknown option: %s" % cl[0]
        else:
            print usage
        self.setPrompt()

    def do_quit(self,c):
        return 1
    do_q=do_quit

def run():
    app=App()
    app.interactive=sys.stdin.isatty()
    if app.interactive:
        readline.parse_and_bind("tab: complete")
        readline.set_completer_delims(' .')
        readline.set_completer(app.complete)
        app.cmdloop()
    else:
        for ln in sys.stdin:
            app.onecmd(ln)

if __name__=='__main__':
    run()