# -*- coding:latin1 -*-
# 
#  Copyright (C) 2015-2019  Nico Latzer nl@mnet-online.de
#
#  This program is free software; you can redistribute it and/or modify
#  it under the terms of the GNU General Public License as published by
#  the Free Software Foundation; either version 2 of the License, or
#  (at your option) any later version.
#
#  $Id: client2.py,v 1.19 2019/11/24 06:27:40 nl Exp nl $

#borrowed from gnuvet
SY_LIST=('vacc','test','enc','pulm','abdom')

SEXES=('m','f','fn','mn','n','u')
SPEC_LIST=('Canine', 'Feline', 'Equine','Rabbit', 'Fish', 'Amphibian', 'Reptile', \
        'Spider', 'other')

VACC_PLAN=dict(
    Canine=('DHPPi', 'DHPPi+L', 'Kennel Cough', 'Parvo',\
            'EPf4','Alu4','Alu3 combi'),
    Equine=('EHV', 'EqFlu', 'EqFlu+Tet', 'Myxo', 'RHD', 'Tetanus'),
    Feline=('EqFlu', 'FeLV', 'RCP+FeLV',\
            'Alu3 combi',)
    )
def getVaccPlan(spec ):
    return VACC_PLAN.get( spec ,[] )

#
#
#
import Tkinter
import Pmw


conn=None
def query(sql, *args ):
    c = conn.cursor()
    print sql,args
    c.execute(sql, args )
    return c

from mx.DateTime import today, DateTimeFrom, DateFrom
from mx.DateTime.ISO import ParseDate


fmtPaym='%4u %s %7.2f %s'
fmtPat='%4u %-10s %-8s %s %s'
#fmtAppt='%s %s %s'

#from pycocumalib
import broker
from pycocumalib import broadcaster
from pycocumalib.CalendarWidget import CalendarWidget

from PmwContribD.UserPrefs import UserPrefs
from PmwContribD.PrefsDialog import PrefsDialog
class gnuvet(UserPrefs):
    order=('fmtDate',#'BankAccountValidator',#'SkontoDays', 'MinRemindableAmount',
            #'preferredBIC',
            'cmdForPdfViewer','DmsPath', #'contactsProvider','ledger','ci',
           'lastCustWorkedOn','inclDeceasedPatients',
           #'guiPaneWidths'
           )
    allowedValues = {
            #@@translate description
        'fmtDate':('string','%d.%m.%Y','Preferred Date Formatting (GUI only)'),
        'BankAccountValidator'   : ('string', 'http://localhost:9032/Business/BLZChecker',
                        'XML-RPC Connection String to Validator Service'),
        'SkontoDays':('integer',7,'days within Skonto is deducted'),
        'MinRemindableAmount':('integer',5, 'dont remind Amount below EUR'),
        'cmdForPdfViewer':('string','open %s','howto start your pdf viewer with given document'),
        'DmsPath':('string','silo', 'Absolute or relative Path to Your Documents (pdf)' ),
        'TemplatePath':('string','templ','Absolute or relative Path to Your Templates' ),
        'lastCustWorkedOn':('integer',1,'Customer code last worked on' ),
        #'preferredBIC':('string','GENODEF1S05','preferred Routing via BIC'),
        'guiPaneWidths':('string','400,30,30','Pane widths in Pixels'),
        'contactsProvider':('string','addressbook.vcf','Path to Addressbook vcf-File'),
        'ledger':('string','files/bs001.dtj', 'Doubletalk Ledger dtj-File'),
        'ci':('string','files/items.csv', 'Charge Item Catalog csv-File'),
        'inclDeceasedPatients':('boolean',1,'If deceased Patients should be included in Search'),
        }
def startWithUserPrefs():
    up = gnuvet()
    broker.Register('Prefs',up)
    return up


DATE_WIDTH=12
STD_BUTTONS=('OK','Abbrechen')

from FT import isnt_BD
class ApptDlg(Pmw.Dialog):
    def __init__(self, *args, **kw):
        Pmw.Dialog.__init__(self, *args, **kw)
        self.buildInterface( self.interior() )
    def buildInterface(self, p ):
        self.fn=Pmw.EntryField(p, labelpos='w', label_text='Kunde' , entry_state='disabled')
        self.fn.pack(anchor='w')
        self.date=Pmw.Counter(p, labelpos='w',label_text='Datum',
                        datatype = {'counter' : 'date', 'format' : 'ymd', 'yyyy' : 1,'separator' : '-' },
                        #entryfield_command = self.fillApptList,
                              entry_width=DATE_WIDTH )
        self.date.pack(anchor='w')
        self.hms=Pmw.Counter(p, labelpos='w',label_text='Uhrzeit', 
                                 datatype = {'counter' : 'time', 'time24' : 1}, increment=15*60, entry_width=10)
        self.hms.pack(anchor='w')
        self.notes=Pmw.EntryField(p, labelpos='w', label_text='Zweck', entry_width=32 )
        self.notes.pack(anchor='w')
        self.dur=Pmw.OptionMenu(p,labelpos='w',label_text='Dauer', items=(15,30,45,90) )
        self.dur.pack(anchor='w')
    def setAppt(self, id ):
        with conn:
            a, = query("select a.*, cli.fn from appt a join cli on ( a.cli=cli.id) where a.id=?", id ).fetchall()
        d = DateTimeFrom( a['appdt'] )
        self.date.setvalue( d.date )
        self.hms.setvalue( d.strftime('%H:%M:00') )
        self.notes.setvalue( a['notes'] )
        self.dur.setvalue( a['dur'] )
        self.fn.setvalue( a['fn'] )
    

class ApptCalDlg(Pmw.Dialog):
    def __init__(self, *args, **kw):
        Pmw.Dialog.__init__(self, *args, **kw)
        self.buildInterface( self.interior() )
    def buildInterface(self, p ):
        self.cal = CalendarWidget(p, selectcommand=self.fillApptListForDate )
        self.cal.pack(anchor='w')
        self.date=Pmw.Counter(p, labelpos='w',label_text='Datum',
                        datatype = {'counter' : 'date', 'format' : 'ymd', 'yyyy' : 1,'separator' : '-' },
                        #entryfield_command = self.fillApptList,
                              entry_width=DATE_WIDTH )
        #self.date.pack(anchor='w')
        
        self.hms=Pmw.Counter(p, labelpos='w',label_text='Uhrzeit', 
                                 datatype = {'counter' : 'time', 'time24' : 1}, increment=15*60, entry_width=10)
        self.hms.pack(anchor='w')
        self.notes=Pmw.EntryField(p, labelpos='w', label_text='Zweck', entry_width=32 )
        self.notes.pack(anchor='w')
        self.dur=Pmw.OptionMenu(p,labelpos='w',label_text='Dauer', items=(15,30,45,90) )
        self.dur.pack(anchor='w')
        Tkinter.Button(p, text='eintragen', command=self.addAppt).pack(fill='x',padx=8)
        Tkinter.Button(p, text=u'vergangene ohne Abrechnung löschen', command=self.removeApptWithoutEnc).pack(fill='x',padx=8)
        
        act = Pmw.ButtonBox(p, orient='vertical')
        act.add('e', command=self.editAppt)
        act.add('x', command=self.cancelAppt)
        act.pack(side='right')
        self.appts=Pmw.ScrolledListBox(p, labelpos='n', label_text='vorh. Termine',
                                       dblclickcommand= self.editAppt )
        self.appts.pack(expand=1,fill='both',padx=8,pady=8)

    def addAppt(self, *args ):
        d = DateTimeFrom( '%s %s' % (self.date.getvalue(), self.hms.getvalue() ) )
        with conn:
            query("insert into appt (cli,appdt,notes, dur) values (?,?,?, ?)",
                   self.cli_id, d.strftime('%Y-%m-%d %H:%M:00') , self.notes.getvalue(),
                  int(self.dur.getvalue() ) )
        self.fillApptList()    

    def editAppt(self, *args ):
        selidx, = self.appts.curselection()
        a = self._appts[ int(selidx) ]
        dlg = ApptDlg(self.interior(), title='Termin verschieben %u' % a['id'], buttons=STD_BUTTONS)
        dlg.setAppt( a['id'] )
        ret= dlg.activate()
        if ret=='OK':
            d = DateTimeFrom( '%s %s' % (dlg.date.getvalue(), dlg.hms.getvalue() ) )
            query("update appt set appdt=?, notes=?,dur=? where id=?",
                  d.strftime('%Y-%m-%d %H:%M:00'), dlg.notes.getvalue().strip(), int( dlg.dur.getvalue() ),
                  a['id'] )
            self.fillApptList()
            
    def cancelAppt(self, *args ):
        selidx, = self.appts.curselection()
        a = self._appts[ int(selidx) ]
        with conn:
            query("delete from appt where id =?", a['id'] )
        self.fillApptList()
    def removeApptWithoutEnc(self, *args ):
        with conn:
            cnt=query("""select count(*) from appt where cli=? and cli||date(appdt) not  in (
                             select cli||encdate from v_cli_enc
                    ) and date(appdt) < CURRENT_DATE """, self.cli_id).fetchone()
        if cnt[0]==0:
            return
        dlg = Pmw.MessageDialog(self.interior(), title=u'Bestätigung erfolrderlich', buttons=STD_BUTTONS,
                                message_text='%u Termine ohne Abrechnung löschen?'  % cnt[0] )
        ret = dlg.activate()
        if ret == 'OK':
            with conn:
                query("""delete from appt where cli=? and cli||date(appdt) not   in (
                             select cli||encdate from v_cli_enc 
                    ) and date(appdt) < CURRENT_DATE """, self.cli_id )
            self.fillApptList()
            
    def fillApptList(self, *args ):
        d = ParseDate( self.date.getvalue()  )
        with conn:
            tmp=query("""select a.*, cli.fn from appt a
                   join cli on ( a.cli=cli.id) where date(a.appdt)=? or cli = ?
                   order by a.appdt,a.id""",
                  d.date, self.cli_id ).fetchall()
        self.cal.setmarks( dict([ (DateTimeFrom(a['appdt']).date,a['fn']) for a in tmp if a['cli']==self.cli_id ]) ) #initial show all appt dates of cli

        self._appts = [ a for a in tmp if DateTimeFrom(a['appdt']).date == d.date ]
        self.appts.setlist([ (DateTimeFrom(a['appdt']).strftime('%H:%M') , a['fn'], a['notes'] or '' ) for a in self._appts
                             ]) #show all appt of current date
        #@@self.hms.setvalue( max([ DateTimeFrom(a['appdt']).strftime('%H:%M:00') for a in self._appts  ]) )
        
        self.appts.configure(label_text=d.strftime('Termine am %a %d.%m.') )
        
    def fillApptListForDate(self, d ): #callback from pycocuma widget
        self.date.setvalue(d) #already ISO-formatted
        self.fillApptList()
        
import os.path
from invgen import produce
class Client:
    def __init__(self, p ):
        self.master = p

        f = Tkinter.Frame(p)
        Tkinter.Button(f,text='Prefs', command=self.showPrefsDlg).pack(side='left', padx=8)
        self.appts=Pmw.OptionMenu(f, labelpos='w',label_text='Warteliste', command=self.switchCli)
        #self.appts.pack(side='left')
        
        Tkinter.Button(f, text='Neukunde', command=self.addCli ).pack(side='right')
        self.chooser = Pmw.OptionMenu(f ,command=self.switchCli )
        self.chooser.pack(side='right')
        self.flt=Pmw.EntryField(f, labelpos='w',label_text='Filter', entry_width=8 )
        self.flt.component('entry').bind('<Return>', self.fillCli)
        self.flt.pack(side='right')
        f.pack()
        
        self.buildInterf(p)

        self._registerAtBroadcaster()
        
    def _registerAtBroadcaster(self ):
        #broadcaster.Register( self.shouldRefillPaym, source='paym', title='paym added' )
        broadcaster.Register( self.shouldRefillPats, source='pat', title='pat modified' )
        broadcaster.Register( self.shouldRefillPending, source='emr', title='emr added' )
    def shouldRefillPaym(self, *args ):
        kw= broadcaster.CurrentData()
        if kw['cli'] == self._cli['id']:
            self.fillPayms()
    def shouldRefillPats(self, *args):
        kw= broadcaster.CurrentData()
        if kw['cli'] ==  self._cli['id']:
            self.fillPats()
    def shouldRefillPending(self, *args ):
        kw= broadcaster.CurrentData()
        if kw['cli'] ==  self._cli['id']:
            self.fillPending()
            
    def showPrefsDlg(self,*args):
        up=broker.Request('Prefs')
        pd=PrefsDialog(userPrefs=up)
        pd.show() #needs a patch in PrefsDialog, dont call super.show
        pd.activate()
    
    def addCli(self, *args ):
        dlg = Pmw.PromptDialog(self.master, title='Kunde anlegen', buttons=STD_BUTTONS,
                               label_text = 'Name',entryfield_labelpos = 'w')
        ret = dlg.activate()
        if ret=='OK':
            with conn:
                query("insert into cli ( fn) values (?)", dlg.getvalue() )
            self.fillCli()
            self.chooser.invoke(-1)
            
    def fillCli(self, *args ):
        flt = self.flt.getvalue()
        with conn:
            tmp=query("""select id,fn from v_fillcli where coalesce(pattern,'') like '%'||?||'%' """, flt ).fetchmany(20)
            #no need for lower(flt) as sqlite makes like case-insenstitive
        self.chooser.setitems( [(c[0],c[1]) for c in tmp])
        if len(tmp)==1:
            self.chooser.invoke(0)
    
    def switchCli(self, sel, *args ):
        self.setClient( sel[0] )
    def _buildHeader(self, p):
        f = Tkinter.Frame(p)
        g = Pmw.Group(f, tag_text='Abrechnung')
        self.outst=Pmw.EntryField(g.interior(), labelpos='w',label_text='OP', entry_state='disabled')
        self.outst.pack(anchor='w')
        self.insur=Pmw.EntryField(g.interior(),labelpos='w',label_text='Vers.' )
        self.insur.pack(anchor='w')

        self.pending=Pmw.EntryField(g.interior(), labelpos='w', label_text='noch abzurechnen', entry_state='disabled' )
        self.pending.pack(anchor='w')
        self.btnClose=Tkinter.Button( g.interior(), text='Rechnung schliessen', command=self.closeForLiab)
        self.btnClose.pack()
        
        g.pack(side='right', padx=6)
        self.fn=Pmw.EntryField(f, labelpos='w', label_text='Name')
        self.fn.pack(anchor='w')
        self.extUID=Pmw.EntryField(f, labelpos='w',label_text='UID')
        self.extUID.pack(anchor='w')
        self.po=Pmw.EntryField(f,labelpos='w',label_text='Anschrift')
        self.po.pack(anchor='w')
        self.contact=Pmw.EntryField(f,labelpos='w',label_text='Kontakt',entry_width=28)
        self.contact.pack(anchor='w')
        
        f.pack()
        
        act = Pmw.ButtonBox(p)
        act.add('Speichern', command=self.saveClient )
        act.add('Termin vereinb', command=self.addAppt )
        act.pack()
    def addAppt(self, *args ):
        dlg = ApptCalDlg(  self.master, title='Termin neu %s' % self._cli['fn'], buttons=('OK',) )
        dlg.cli_id = self._cli['id']
        dlg.date.setvalue( today().date )
        dlg.hms.setvalue( '09:00:00')
        #dlg.removeApptWithoutEnc()
        dlg.fillApptList()
        ret = dlg.activate()
                  
    def buildInterf(self, p ):
        self._buildHeader(p)
        nb = Pmw.NoteBook(p )

        p0 = nb.add('Patient')
        act= Pmw.ButtonBox(p0)
        act.add('Neu', command=self.addPat )
        act.add(u'übernehmen', command=self.takePat )
        
        act.pack()
        self.pats=Pmw.ScrolledListBox(p0, dblclickcommand=self.showPat )
        self.pats.pack(expand=1, fill='both')
        
        p1=nb.add('Kundenkonto')
        act = Pmw.ButtonBox(p1)
        act.add('bar ez',command=self.addPaym )
        act.add('Storno',command=self.addReversalTo)
        act.pack()
        self.payms = Pmw.ScrolledListBox( p1, dblclickcommand=self.showDoc )
        self.payms.pack(expand=1, fill='both')
        nb.pack(expand=1, fill='both',padx=8,pady=8)

    def addPat(self, *args):
        dlg = Pmw.PromptDialog(self.master, title="Neuer Patient", buttons=STD_BUTTONS,
                               label_text = 'Name',entryfield_labelpos = 'w')
        ret = dlg.activate()
        if ret=='OK':
            with conn:
                query("insert into pat ( n,specie,gender,dob, cli) values (?,'Canine','m','2001-01-01',?)",dlg.getvalue(),  self._cli['id'] )
            self.fillPats()
    def takePat(self, *args):
        with conn:
            pat_list = query("select pat.id,n, specie, dob, deceased,cli.fn from pat join cli on (pat.cli=cli.id) where cli <> ? /* and deceased is null */",  self._cli['id'] ).fetchall()
        dlg = Pmw.SelectionDialog(self.master, title=u"Patient übernehmen", buttons=STD_BUTTONS,
                                  scrolledlist_items= [ fmtPat % (p[0],'%s /%s' % (p[1],p[5]),p[2],p[3],p[4] or '' ) for p in pat_list ] )
        self.inclDeceased = v = Tkinter.IntVar()
        v.set(1)
        self.cbIncl = Tkinter.Checkbutton(dlg.interior(), text='inkl. verstorbene', variable=v , state='disabled' )
        self.cbIncl.pack()
        ret=dlg.activate()
        if ret=='OK':
            selidx, = dlg.curselection()
            p=pat_list[ int(selidx) ]

            assert p['cli'] <> self._cli['id'], Exception("already yours")
            #first make sure no non-invoiced emrs left
            with conn:
                query("begin transaction") #this is necessary as otherwise a read-only transaction would be started
                non_inv_list=query("select encdate from emr where pat=? and inv is null", p[0]).fetchall()
                assert len(non_inv_list)==0, Exception("pending lines")
            
                c=query("update pat set cli=? where id=? and cli=?",  self._cli['id'], p['id'], p['cli'] )
                if c.rowcount>0:
                    query("insert into emr (encdate,pat,details) values (CURRENT_DATE,?,?) ", p['id'], u"patient übernommen von %u" % p['cli'] )

            self.fillPats()
        
    def fillPats(self, *args ):
        with conn:
            self._pats = tmp = query("select id,n,specie,gender,dob, deceased from pat where cli=?",  self._cli['id'] ).fetchall()
        self.pats.setlist( [ fmtPat%(p[0],p[1],p[2],p[4],p[5] or '' ) for p in tmp ])
    def showPat(self, *args ):
        selidx, = self.pats.curselection()
        p = self._pats[ int(selidx) ]
        v= Patient( Tkinter.Toplevel() )
        v.sif.configure( cmdForDisplay = up.get('cmdForPdfViewer') )
        v.setPat(p['id'] )
    def addPaym(self, *args):
        dlg  = Pmw.Dialog(self.master, title='Zahlung eintragen', buttons=STD_BUTTONS)
        dlg.vdate=Pmw.EntryField(dlg.interior(), labelpos='w',label_text='Wertstellung', entry_width=DATE_WIDTH ) #20191104
        dlg.vdate.pack(anchor='w')
        dlg.vdate.setvalue( today().date )
        dlg.am=Pmw.EntryField(dlg.interior(), labelpos='w',label_text='Betrag (EUR)')
        dlg.am.pack(anchor='w')
        am = float( self.outst.getvalue() )
        dlg.am.setvalue( -am ) #ready to balance outst
        dlg.details=Pmw.EntryField(dlg.interior(), labelpos='w',label_text='Details')
        dlg.details.pack(anchor='w')
        ret=dlg.activate()
        if ret=='OK':
            with conn:
                query("insert into cb ( valdate,amount, details, cli ) values (?,?,?,?)",
                  ParseDate(dlg.vdate.getvalue()).date,float(dlg.am.getvalue()), dlg.details.getvalue(), self._cli['id'] )
            self.fillPayms()
    def addReversalTo(self, *args ):
        try:
            selidx,  = self.payms.curselection()
        except ValueError:
            print "please select one"
            return
        p = self._payms[ int(selidx) ]
        with conn:
            query("insert into cb ( valdate,amount,details,cli) select CURRENT_DATE,-amount,'Reversal von ' || id,cli from cb where id = ?", p['id'] )
        self.fillPayms()
        
    def setClient(self, id ):
        with conn:
            it, = query("select * from cli where id=?", id ).fetchall()
        self.fn.setvalue( it['fn'] )
        self.extUID.setvalue( it['uid'] or '' )
        self.po.setvalue( it['po'] or '')
        self.contact.setvalue( it['contact'] or '')
        self.insur.setvalue( it['insur'] or '')
        self._cli = it
        #broker.Register('cli',it)
        if up.get('lastCustWorkedOn') <> it['id']:
            up['lastCustWorkedOn']=it['id']
        self.fillPayms()
        self.fillPending()
        self.fillPats()
    
    def fillPayms(self ):
        with conn:
            self._payms = tmp = query("select id,valdate,amount,details,cli from cb where cli=? order by valdate,id" ,  self._cli['id'] ).fetchall()
        self.payms.setlist([ fmtPaym % ( p[0], p[1], p[2], p[3] or '') for p in tmp ] )
        self.payms.see( len(tmp) )
        self.outst.setvalue( '%.2f' % sum([ p['amount'] for p in tmp ]) )
    def saveClient(self ):
        with conn:
            query("update cli set fn=?,po=?,contact=?,insur=?, uid=? where id=?", self.fn.getvalue(),
              self.po.getvalue().strip(), self.contact.getvalue(), self.insur.getvalue(),
              self.extUID.getvalue(),  self._cli['id'] )
    def showDoc(self, *args ):
        selidx, = self.payms.curselection()
        p = self._payms[ int(selidx) ]
        fn = getInvFilename( p )
        
        cmd=up.get('cmdForPdfViewer')
        if os.path.exists(fn):
            os.system(cmd % fn )

    def fillPending(self ):
        with conn:
            pending, = query("select sum(total_incl) from v_pending_liab where cli=? ", self._cli['id'] ).fetchone()
            print pending
        if pending is None:
            s,am='disabled',0
        else:
            s,am='normal',pending
        self.pending.setvalue( '%.2f' % am )
        self.btnClose.configure(state=s)
    def closeForLiab(self ):
        dlg=Pmw.MessageDialog(self.master, title=u'Bestätigung erforderlich', buttons=STD_BUTTONS,
                              #iconpos='w',icon_bitmap='question',
                              message_text='jetzt abrechnen?' )
        ret=dlg.activate()
        if ret <> 'OK':
            return
        
        inv=None
        with conn:
            c= query("""insert into cb ( cli, valdate, amount, details)  select cli,stmt_date,total_incl,details  from v_pending_liab  where cli = ? limit 1 """ , self._cli['id'] )
            if c.rowcount >0:
                inv = query("select id,valdate as stmt_date,amount,details,cli from cb where id=?", c.lastrowid ).fetchone()
                query("update emr set inv=? where pat in ( select id from pat where cli=?) and inv is null and encdate<=? ",  c.lastrowid, self._cli['id'],inv['stmt_date'] )
                
                

        if inv:
            self.fillPending()
            
            dlg = Pmw.MessageDialog(self.master,title = 'Info',
                message_text = 'Abgerechnet mit %.2f und als Forderung %u eingetragen.' % (inv['amount'],inv['id']) )
            dlg.activate()
            
            #produce invoice pdf
            fn=getInvFilename( inv )
            with conn:
                c,=query("select fn,po,2 as toc from cli where id =?", inv['cli']).fetchall()
                inv_lines = query("""select ci.code,ci.descr, ci.price as price_excl,ci.vat, emr.factor as cnt from emr
                                join ci on ( emr.lofch=ci.code  ) 
                            where emr.inv=? """, inv['id'] ).fetchall()
            produce(c, inv,inv_lines, fn )
            #show invoice
            os.system(up.get('cmdForPdfViewer') % fn )

            self.fillPayms()
            
            

def getInvFilename(inv):
    return '%s/%u_%u.pdf' % (up.get('DmsPath'), inv['cli'],inv['id'] )
    
fmtCI='%-8s %-34s %6.2f'
class AddEMRDialog(Pmw.Dialog):
    def __init__(self, *args, **kw):
        Pmw.Dialog.__init__(self, *args, **kw)
        self.buildInterf( self.interior() )
    def buildInterf(self,  p):
        self.encdate=Pmw.EntryField(p, labelpos='w',label_text='Beh.datum')
        self.encdate.pack(anchor='w')
        self.sympt=Pmw.OptionMenu(p, labelpos='w',label_text='Symptom',
                                 items=SY_LIST )
        self.sympt.pack(anchor='w')
        self.details=Pmw.EntryField(p, labelpos='w',label_text='Details', entry_width=40)
        self.details.pack(anchor='w')
        self.lofch=Pmw.EntryField(p, labelpos='w',label_text='Abrechnung')
        self.lofch.pack(anchor='w')
        self.factor=Pmw.Counter(p,labelpos='w',label_text='Faktor', datatype='real', increment=0.1, entry_width=4 )
        self.factor.pack(anchor='w')
        self.factor.setvalue( 1.2)
        
        g = Pmw.Group(p, tag_text='Katalog')
        self.flt=Pmw.EntryField(g.interior(), labelpos='w', label_text='Filter' )
        self.flt.pack()
        self.flt.component('entry').bind('<Return>', self.fillItems )
        self.cilist = Pmw.ScrolledListBox(g.interior(), dblclickcommand=self.setLofch )
        self.cilist.pack(expand=1, fill='both')
        Tkinter.Button(g.interior(), text='Auswahl als Abrechnungsziffer', command=self.setLofch).pack()
        g.pack(expand=1, fill='both')
    def setRec(self, id ):
        with conn:
            rec, = query("select id,encdate,sympt,details,lofch,factor, pat from emr where id=?", id).fetchall()
        self.encdate.setvalue( rec['encdate'] )
        self.sympt.setvalue( rec['sympt'] or '')
        self.details.setvalue( rec['details'] or '')
        self.lofch.setvalue( rec['lofch'] or '')
        self.factor.setvalue( rec['factor'] ) #%.2f
        self._rec=rec
    def fillItems(self, *args ):
        with conn:
            self._cilist = query("select code,descr,price  from ci where code|| descr like '%'||?||'%' ", self.flt.getvalue() ).fetchall()
        self.cilist.setlist( [ fmtCI%(ci['code'],ci['descr'], ci['price']) for ci in self._cilist ] )
    
    def setLofch(self, *args ):
        selidx, = self.cilist.curselection()
        ci = self._cilist[ int(selidx) ]
        self.lofch.setvalue( ci[0] ) #code
    def retrValues(self ):
        return dict(
            encdate=ParseDate( self.encdate.getvalue() ).date,
            sympt = self.sympt.getvalue(),
            details= self.details.getvalue().strip(),
            lofch = self.lofch.getvalue(),
            factor= float( self.factor.getvalue()  ),
            )
    
def fmtAge(days ):
    y,remd=  divmod(days, 365 )
    return '%ua %um' % ( y,remd//30 )
fmtEmr='%s %-12s %-40s %-15s %s'

from ImageSF import ScrolledImageFrame
class Patient:
    def __init__(self, p ):
        self.master=p
        self.buildInterf(p)
    def _buildHeader(self, p ):
        self.n=Pmw.EntryField(p, labelpos='w',label_text='Name')
        self.n.pack(anchor='w')
        self.cli=Pmw.EntryField(p,labelpos='w',label_text='Halter', entry_state='disabled')
        self.cli.pack(anchor='w')
        self.specie=Pmw.OptionMenu(p, labelpos='w',label_text='Art', items=SPEC_LIST )
        self.specie.pack(anchor='w')

        self.vacc=Pmw.RadioSelect(p, labelpos='nw',label_text='Impfungen',orient='vertical',pady=0,
                                  buttontype='checkbutton' )
        self.vacc.pack(anchor='w')
        
        self.gender=Pmw.OptionMenu(p, labelpos='w',label_text='Sex', items=SEXES )
        self.gender.pack(anchor='w')
        self.dob=Pmw.EntryField(p, labelpos='w',label_text='geb.', entry_width=DATE_WIDTH )
        self.dob.pack(anchor='w')
        self.rnr=Pmw.EntryField(p,labelpos='w',label_text='Reg.-nr.' )
        self.rnr.pack(anchor='w')
        self.deceased=Pmw.EntryField(p, labelpos='w',label_text='gest.', entry_width=DATE_WIDTH )
        self.deceased.pack(anchor='w')
        self.age=Pmw.EntryField(p,labelpos='w',label_text='Alter', entry_state='disabled')
        self.age.pack(anchor='w')
        Tkinter.Button(p, text='Speichern',command=self.savePat ).pack()
    def buildInterf(self, p ):
        f = Tkinter.Frame(p)
        self._buildHeader(f)
        f.pack(side='left',anchor='n')

        nb = Pmw.NoteBook(p)
        p1 = nb.add('EMR')
        
        self.sif = ScrolledImageFrame(p, labelpos='nw', label_text='Bilder',
                                      thumbsize=(192,192),
                                      #vertflex='shrink',
                                       #@@dblclickcommand=self.showImage
                                      )
        self.sif.pack(side='bottom',pady=6, padx=6)
        
        self.recs=Pmw.ScrolledListBox(p1 , dblclickcommand=self.editRec )
        self.recs.pack(expand=1, fill='both')
        #self.pending=Pmw.EntryField(p1, labelpos='w', label_text='noch nicht abgerechneter Betrag (EUR netto)', entry_state='disabled' )
        #self.pending.pack(side='left')
        
        act = Pmw.ButtonBox(p1 )
        act.add('Leistung dazu', command=self.addRec )
        act.add('x', command=self.delRec )
        act.pack()
        
        nb.pack(expand=1, fill='both')
    
    def editRec(self, *args ):
        selidx,=self.recs.curselection()
        rec = self._recs[ int(selidx)]
        if rec['inv']:
            return
        #assert not rec['inv'], Exception("bereits abgerechnet")
        dlg = AddEMRDialog(self.master, title=u'Leistung ändern', buttons=STD_BUTTONS )
        dlg.setRec(rec['id'])
        dlg.flt.setvalue( self._pat['specie'] ) #pre-search with specie
        dlg.fillItems()
        ret = dlg.activate()
        if ret=='OK':
            kw = dlg.retrValues()
            with conn:
                query("""update emr set encdate=?,sympt=?,details=?,lofch=?,factor=? where id=? and inv is null """,
                      kw['encdate'] ,kw['sympt'], kw['details'], kw['lofch'], kw['factor'],
                      rec['id']
                      )
            broadcaster.Broadcast('emr', 'emr added', {'cli':self._pat['cli'] } )
            self.fillRecs()
            
    def addRec(self, *args ):
        dlg = AddEMRDialog(self.master, title='Leistung eintragen', buttons=STD_BUTTONS )
        dlg.encdate.setvalue( today().date )
        dlg.sympt.setvalue('enc')
        dlg.lofch.setvalue('enc')
        dlg.flt.setvalue( self._pat['specie'] ) #pre-search with specie
        dlg.fillItems()
        ret = dlg.activate()
        if ret=='OK':
            kw = dlg.retrValues()
            with conn:
                query("insert into emr ( encdate,sympt,details,lofch,factor, pat) values (?,?,?,?,?,?) ", 
                kw['encdate'],kw['sympt'],kw['details'], kw['lofch'],kw['factor'],
                  self._pat['id'] )
            broadcaster.Broadcast('emr', 'emr added', {'cli':self._pat['cli'] } )
            self.fillRecs()
            
    def delRec(self, *args ):
        selidx,=self.recs.curselection()
        rec = self._recs[ int(selidx)]
        assert not rec['inv'], Exception("bereits abgerechnet")
        with conn:
            query("delete from emr where id=? and inv is null", rec['id'] )
        broadcaster.Broadcast('emr', 'emr added', {'cli':self._pat['cli'] } )
        self.fillRecs()
        
    def fillRecs(self, *args):
        with conn:
            self._recs = tmp = query("""select emr.id,encdate,sympt,details, lofch,ci.price, inv from emr
                    left outer join ci on ( emr.lofch = ci.code )
                    where pat=? order by encdate,emr.id""", self._pat['id'] ).fetchall()
        self.recs.setlist([ fmtEmr %(r['encdate'], r['sympt'] or'', r['details'] or '' , r['lofch'] or '', r['inv'] or '') for r in tmp ])
        self.recs.see(len(tmp))
        
        #now fill vacc list
        found=[]
        for r in filter(lambda rec:rec['sympt']=='vacc',tmp):
            if not r['details']: continue #avoid IndexError below if details left blank
            vacc= r['details'].split()[0]
            if vacc=='Kennel':
                vacc='Kennel Cough'
            if vacc not in found: #and encdate >= -6mons, based on deceased|today
                found.append(vacc)
        self.vacc.setvalue( found )

        #color uninvoiced
        lb = self.recs.component('listbox')
        for i,r in enumerate(tmp):
            if not r['inv'] :
                lb.itemconfig(i, fg='blue')
            else:
                lb.itemconfig(i, fg='black')
                
    def fillImages(self ):
        self.sif.configure(label_text='silo/%u' % self._pat['id'] )
        if os.path.exists('silo/%s' % self._pat['id'] ):
            self.sif.setlist( 'silo/%s' % self._pat['id'] )
        #else:
        #    self.sif.pack_forget()
        
    def setPat(self, id ):
        with conn:
            it, = query("select pat.id,n,specie,gender,dob,regid,deceased,age(dob,deceased) as age,cli,tn, cli.fn from pat join cli on ( pat.cli=cli.id) where pat.id =?", id ).fetchall()
        #( strftime('%s',coalesce(deceased,CURRENT_DATE)) - strftime('%s',dob) ) /86400
        self.n.setvalue( it['n'] )
        self.cli.setvalue( it['fn'] )
        self.specie.setvalue( it['specie'] )
        self.vacc.deleteall()
        for v in getVaccPlan( it['specie']):
            self.vacc.add(v, state='disabled')
        
        self.gender.setvalue( it['gender'] )
        self.dob.setvalue( it['dob'] or '')
        self.rnr.setvalue( it['regid'] or '')
        self.deceased.setvalue( it['deceased'] or '')
        self.age.setvalue( fmtAge(it['age']) )
        self._pat=it
        self.fillRecs()
        self.fillImages()
    def savePat(self,*args):
        dob = ParseDate( self.dob.getvalue() ).date
        d = None
        if self.deceased.getvalue() :
            d = ParseDate( self.deceased.getvalue() ).date
        if d:
            assert d >= dob, Exception("before dob")
        with conn:
            c=query("update pat set n=?, specie=?, gender=?, dob=?,regid=?, deceased=? where (id=? and tn=?)",
              self.n.getvalue(), self.specie.getvalue(), self.gender.getvalue(), dob ,
              self.rnr.getvalue(), d,
              self._pat['id'], self._pat['tn'] )
            assert c.rowcount==1, Exception("meanwhile modified")
            #refetch record for subsequent updates
            #self._pat, = query("select id,n,specie,gender,dob,regid,deceased,age(dob,deceased) as age,cli,tn from pat where id =?", self._pat['id']).fetchall()
        broadcaster.Broadcast('pat','pat modified', {'cli':self._pat['cli'] } )
        self.setPat( self._pat['id'])
        
            
def age(a,b):
    if b:
        b = ParseDate(b)
    else:
        b=today()
    d = b - ParseDate(a)
    return d.day
def q(s):
    return 1+ ( ParseDate(s).month  -1)//3

    
            
if __name__=='__main__':
    import sqlite3
    conn=sqlite3.connect('data/gnub.db') #,isolation_level=None)
    conn.row_factory=sqlite3.Row
    conn.create_function('age',2,age )
    #conn.create_function('q',1,q )#quartal from given date

    up=startWithUserPrefs()
    
    root = Tkinter.Tk()
    root.title('gnuvetf $Revision: 1.19 $')
    Pmw.initialise(root, fontScheme='default')
    root.option_add('*Listbox*font', ('Courier',10) )
    v = Client(root)
    #v.fillAppts()
    v.fillCli()
    #v.chooser.invoke(-1)
    try:
        lastid = up.get('lastCustWorkedOn')
        v.setClient( lastid )
        #sync chooser
        tmp,=query("""select id,fn from v_fillcli where id=? """, lastid ).fetchall()
        v.chooser.setvalue((tmp[0],tmp[1]))
        #so it might be that the chooser doesnot hold the last visited cust,
        #but it is displayed
    except Exception as exc:
        pass
    root.mainloop()
    
    conn.close()