hh-office /

The default branch has multiple heads

Full commit
#!/usr/bin/env python
''' export_claims -- export claims in CSV format

import csv
from itertools import groupby
from operator import itemgetter
import wsgiref.handlers
import datetime

import MySQLdb

from ocap import DBOpts, dbopts
import hhtcb

PLAIN = [('Content-Type', 'text/plain')]

def cgi_main():
    app = ReportApp(DBOpts(hhtcb.xataface_config()),

def _test_main(ini='conf.ini'):
    import sys

    outfn, visits = sys.argv[1], sys.argv[2:]
    host, user, password, name = dbopts(hhtcb.xataface_config())

    content, pages = format_claims(MySQLdb.connect(host=host, user=user,
                                                   passwd=password, db=name),
                                   map(int, visits))
    outfp = open(outfn, 'w')
    for part in content:
    print pages

class ReportApp(object):
    def __init__(self, dbo, datesrc):
        self._dbo = dbo
        self._datesrc = datesrc

    def __call__(self, env, start_response):
            host, user, password, name = self._dbo.webapp_login(env)
        except KeyError:
            start_response('400 bad request', PLAIN)
            return ['missing key parameter ']
        except OSError:
            start_response('401 unauthorized', PLAIN)
            return ['report key does not match.']

        conn = MySQLdb.connect(host=host, user=user, passwd=password, db=name)

        start_response('200 ok',
                       [('content-type', 'text/plain'),
                         'attachment; filename=claims-%s.csv'

        content, pages = format_claims(conn)
        return content

def format_claims(conn, visit_ids):
    cursor = conn.cursor(MySQLdb.cursors.DictCursor)
    cursor.execute(QUERY % dict(
            visit_ids=', '.join([str(i) for i in visit_ids])))

    pages = []
    buf = ListWriter()
    out = csv.DictWriter(buf, COLUMNS, quoting=csv.QUOTE_ALL)
    out.writerow(dict(zip(COLUMNS, COLUMNS)))

    for client_id, records in by_page(groupby(cursor.fetchall(),
        claim = records[0]

        tot = claim['28-TotalCharge']
        for idx in range(1, len(records)):
            for k, v in records[idx].items():
                if k.startswith('24.'):
                    kk = k.replace('.1.', '.%d.' % (idx + 1))
                    claim[kk] = v
                if k == '24.1.f-Charges':
                    tot += v

        claim['28-TotalCharge'] = tot
        # is there ever an amount paid?
        claim['30-BalanceDue'] = tot

        #import pprint
        visit_ids = [r['visit_id'] for r in records]
        del claim['client_id']
        del claim['visit_id']

    return, pages

def by_page(record_groups, pg_size):
    for k, group in record_groups:
        gl = list(group)
        offset = 0
        while offset < len(gl):
            yield k, gl[offset:offset + pg_size]
            offset += pg_size

class ListWriter(object):
    def __init__(self): = []

    def write(self, txt):

QUERY = r'''
select client_id, visit_id
     , as `Insurance Company Name`
     , co.address `Insurance Company Address 1`
     , co.city_st_zip `Insurance Company Address 2`
     , ins.payer_type `1-InsuredPlanName`
     , ins.id_number `1a-InsuredIDNo`
     , as `2-PatientName`
     , date_format(c.DOB, '%%m/%%d/%%Y') as `3-PatientDOB`
     , ins.patient_sex `3-PatientGender`
     , case when upper(ins.patient_rel) = 'SELF'
       else ins.insured_name end `4-InsuredName`
     , c.address `5-PatientAddress`
     , `5-PatientCity`
     , c.state `5-PatientState`
     , `5-PatientZip`
     , `5-PatientPhone`
     , upper(ins.patient_rel) `6-PatientRel`
     , case when upper(ins.patient_rel) = 'SELF'
       then c.address
       else ins.insured_address end `7-InsuredAddr`
     , case when upper(ins.patient_rel) = 'SELF'
       else ins.insured_city end `7-InsAddCity`
     , case when upper(ins.patient_rel) = 'SELF'
       then c.state
       else ins.insured_state end `7-InsAddState`
     , case when upper(ins.patient_rel) = 'SELF'
       else ins.insured_zip end `7-InsAddZip`
     , case when upper(ins.patient_rel) = 'SELF'
       else ins.insured_phone end `7-InsAddPhone`
     , ins.patient_status `8-MaritalStatus`
     , ins.patient_status2 `8-Employed?`
     , 'NO' as `10a-CondEmployment`
     , 'NO' as `10b-CondAutoAccident`
     , 'NO' as `10c-CondOtherAccident`
     , ins.insured_policy `11-InsuredGroupNo`
     , date_format(case when upper(ins.patient_rel) = 'SELF'
                   then c.dob
                   else ins.insured_dob end, '%%m/%%d/%%Y') `11a-InsuredsDOB`
     , case when upper(ins.patient_rel) = 'SELF'
       then ins.patient_sex
       else ins.insured_sex end `11a-InsuredsGender`
     , 'Signature on file' `12-PatientSign`
     , date_format(current_date, '%%m/%%d/%%Y') `12-Date`
     , 'Signature on file' as `13-AuthSign`
     , 'NO' as `20-OutsideLab`
     , '0.00' as `20-Charges`
     , ins.dx1 `21.1-Diagnosis`
     , ins.dx2 `21.2-Diagnosis`
     , ins.approval `23-PriorAuth`

     , date_format(s.session_date, '%%m/%%d/%%Y') `24.1.a-DOSFrom`
     , date_format(s.session_date, '%%m/%%d/%%Y') `24.1.a-DOSTo`
     , v.cpt as `24.1.d-CPT`
     , '11' as `24.1.b-Place`
     , 1 as `24.1.e-Code`
     , p.price `24.1.f-Charges`
     , 1 as `24.1.g-Units`
     , bp.npi `24.1.j-ProvNPI`

     , bp.tax_id `25-TaxID`
     , 'SSN' as `25-SSN/EIN`
     , concat(upper(substr(, 1, 3)), '.',
              upper(substr(, instr(, ',') + 2, 3)), '.',
              convert(, char)) as `26-PatientAcctNo`
     , 'Y' as `27-AcceptAssign`
     , p.price as `28-TotalCharge`
     , 0 `29-AmountPaid`
     , p.price as `30-BalanceDue`
     , as `31-PhysicianSignature`
     , date_format(current_date, '%%m/%%d/%%Y') `31-Date`
     , `33-ClinicName`
     , bp.address as `33-ClinicAddressLine1`
     , bp.city_st_zip as `33-ClinicCityStateZip`
     , bp.npi as `33-a-NPI`
from Insurance ins
join Client c on ins.Client_id =
join Carrier co on ins.Carrier_id =
join Visit v on v.Client_id =
join `Procedure` p on p.cpt = v.cpt
join `Session` s on v.Session_id =
join `Group` g on s.Group_id =
join Therapist as bp on bp.tax_id is not null
where v.bill_date is null and v.check_date is null
and in (%(visit_ids)s)
order by,, s.session_date,

COLUMNS = [literal.strip()[1:-1] for literal in '''
            "Insurance Company Name"
            "Insurance Company Name 2"
            "Insurance Company Address 1"
            "Insurance Company Address 2"

if __name__ == '__main__':
    from os import environ
    if 'SCRIPT_NAME' in environ: