tools/anonym/anoymncsv.py

#!/usr/bin/env python
#
# (c) 2008 by Intevation GmbH
# author: Sascha L. Teichmann ([email protected])
#

import sys
import os
import codecs
#import getopt
import psycopg2        as dbapi
import psycopg2.extras as extras

dbapi.extensions.register_type(dbapi.extensions.UNICODE)

SEP = "$"

DBNAME   = "ka_inte00002_db"
HOST     = "laodameia"
PORT     = 5434
USER     = "postgres"
PASSWORD = ""

ANON_ON = \
"""SELECT wert FROM ka_konfiguration_tbl
   WHERE bez = 'anon_transfer'"""

NUM_EINV = \
"""SELECT count(*) FROM master_tbl_anonym_view
   WHERE einverstaendniserklaerung = 1"""

SELECT_MASTER_TBL = \
"""SELECT * FROM master_tbl_anonym_view
   WHERE einverstaendniserklaerung = 1"""

RG_VIEWS = (
    "rg_kompetenzfestellung_tbl_anonym_view",
    "rg_angebote_berufliche_qualifizierung_tbl_anonym_view",
    "rg_angebote_berufsvorbereitung_tbl_anonym_view",
    "rg_angebote_bildenden_bereich_tbl_anonym_view",
    "rg_angebote_lebensbewaeltigung_tbl_anonym_view")

SELECT_RG = \
"""SELECT * FROM %s WHERE master_id = %d"""

BLACK_LIST = set(['id', 'master_id', 'uuid_id', 'bearbeiter_id'])

def empty(x):
    if x is None: return ""
    return x

class RepeatGroup:

    def __init__(self, columns):
        self.columns = columns

    def headerFields(self):
        return [a[0] for a in self.columns if a[0] not in BLACK_LIST]

    def valueFields(self):
        return [empty(a[2]) for a in self.columns if a[0] not in BLACK_LIST]

    def numFields(self):
        s = 0
        for a in self.columns:
            if a[0] not in BLACK_LIST:
                s += 1
        return s

class Case(RepeatGroup):
    def __init__(self, columns):
        RepeatGroup.__init__(self, columns)
        self.rgs = []

    def fetchRepeatGroups(self, cur):
        master_id = self.columns[0][2]
        print >> sys.stderr, "master: %d" % master_id
        for view in RG_VIEWS:
            cur.execute(SELECT_RG % (view, int(master_id)))
            rs = []
            while True:
                row = cur.fetchone()
                if not row: break
                cs = row._index.items() # XXX: hackish!
                cs.sort(lambda a, b: cmp(a[1], b[1]))
                cs = [(a[0], a[1], row[a[1]]) for a in cs]
                rs.append(RepeatGroup(cs))
            print >> sys.stderr, "\t%d %s" % (len(rs), view)
            self.rgs.append(rs)

class Cases:
    def __init__(self):
        self.cases = []

    def appendCase(self, case):
        self.cases.append(case)

    def fetchRepeatGroups(self, cur):
        for case in self.cases:
            case.fetchRepeatGroups(cur)

    def maxRepeatGroups(self):
        mc = [0] * len(RG_VIEWS)

        for rgs in [c.rgs for c in self.cases]:
            for i in range(len(mc)):
                mc[i] = max(mc[i], len(rgs[i]))

        for l in mc:
            print >> sys.stderr, "max len: %d" % l

        self.maxRGs = mc

    def noneEmptyRepeatGroup(self, i):
        for case in self.cases:
            rgs = case.rgs
            if len(rgs[i]):
                return rgs[i][0]
        return None

    def generateHeader(self, out):
        if not self.cases:
            return ""
        case = self.cases[0]
        headers = case.headerFields()
        for i in range(len(RG_VIEWS)):
            rg = self.noneEmptyRepeatGroup(i)
            if rg is None:
                continue
            hs = rg.headerFields() 
            for j in range(self.maxRGs[i]):
                if j > 0:
                    headers.extend([ "%s_%d" % (x, j) for x in hs])
                else:
                    headers.extend(hs)
        print >> out, "#%s" % SEP.join(headers)

    def dumpFields(self, out):
        fills = []
        for i in range(len(RG_VIEWS)):
            rg = self.noneEmptyRepeatGroup(i)
            if rg is None: fills.append(0)
            else:          fills.append(rg.numFields())

        for case in self.cases:
            line = case.valueFields()
            for m, rg, fill in zip(self.maxRGs, case.rgs, fills):
                count = 0
                for r in rg:
                    line.extend(r.valueFields())
                    count += 1
                while count < m:
                    line.extend([''] * fill)
                    count += 1
            print >> out, "%s" % SEP.join(map(lambda x: u"%s" % x, line))


def main():
    Writer = codecs.getwriter("latin-1")
    out = Writer(sys.stdout)
    con, cur = None, None
    try:
        PASSWORD = raw_input("> ")
        con = dbapi.connect(
            database = DBNAME,
            host     = HOST,
            port     = PORT,
            user     = USER,
            password = PASSWORD)

        cur = con.cursor(cursor_factory=extras.DictCursor)
        cur.execute(ANON_ON)
        row = cur.next()

        if row:
            if row[0] != 'on':
                print >> sys.stderr, "Anonymer Transfer nicht gestattet"
                return
        else:
            print >> sys.stderr, "Anonymer Transfer nicht gestattet"
            return

        cur.execute(NUM_EINV)
        row = cur.next()

        if  not row:
            print >> sys.stderr, "Keine  Akten gefunden"
            return

        print >> sys.stderr, "Uebermittelbare Akten: %d" % row[0]

        cur.execute(SELECT_MASTER_TBL)

        cases = Cases()

        while True:
            row = cur.fetchone()
            if not row: break
            columns = row._index.items() # XXX: hackish!
            columns.sort(lambda a, b: cmp(a[1], b[1]))
            columns = [(a[0], a[1], row[a[1]]) for a in columns]
            cases.appendCase(Case(columns))

        cases.fetchRepeatGroups(cur)
        cases.maxRepeatGroups()
        cases.generateHeader(out)
        cases.dumpFields(out)

    finally:
        if cur:
            try: cur.close()
            except: pass
        if con:
            try: con.close()
            except: pass


if __name__ == "__main__":
    main()
1	#!/usr/bin/env python
2	#
3	# (c) 2008 by Intevation GmbH
4	# author: Sascha L. Teichmann ([email protected])
5	#
6
7	import sys
8	import os
9	import codecs
10	#import getopt
11	import psycopg2 as dbapi
12	import psycopg2.extras as extras
13
14	dbapi.extensions.register_type(dbapi.extensions.UNICODE)
15
16	SEP = "$"
17
18	DBNAME = "ka_inte00002_db"
19	HOST = "laodameia"
20	PORT = 5434
21	USER = "postgres"
22	PASSWORD = ""
23
24	ANON_ON = \
25	"""SELECT wert FROM ka_konfiguration_tbl
26	WHERE bez = 'anon_transfer'"""
27
28	NUM_EINV = \
29	"""SELECT count(*) FROM master_tbl_anonym_view
30	WHERE einverstaendniserklaerung = 1"""
31
32	SELECT_MASTER_TBL = \
33	"""SELECT * FROM master_tbl_anonym_view
34	WHERE einverstaendniserklaerung = 1"""
35
36	RG_VIEWS = (
37	"rg_kompetenzfestellung_tbl_anonym_view",
38	"rg_angebote_berufliche_qualifizierung_tbl_anonym_view",
39	"rg_angebote_berufsvorbereitung_tbl_anonym_view",
40	"rg_angebote_bildenden_bereich_tbl_anonym_view",
41	"rg_angebote_lebensbewaeltigung_tbl_anonym_view")
42
43	SELECT_RG = \
44	"""SELECT * FROM %s WHERE master_id = %d"""
45
46	BLACK_LIST = set(['id', 'master_id', 'uuid_id', 'bearbeiter_id'])
47
48	def empty(x):
49	if x is None: return ""
50	return x
51
52	class RepeatGroup:
53
54	def __init__(self, columns):
55	self.columns = columns
56
57	def headerFields(self):
58	return [a[0] for a in self.columns if a[0] not in BLACK_LIST]
59
60	def valueFields(self):
61	return [empty(a[2]) for a in self.columns if a[0] not in BLACK_LIST]
62
63	def numFields(self):
64	s = 0
65	for a in self.columns:
66	if a[0] not in BLACK_LIST:
67	s += 1
68	return s
69
70	class Case(RepeatGroup):
71	def __init__(self, columns):
72	RepeatGroup.__init__(self, columns)
73	self.rgs = []
74
75	def fetchRepeatGroups(self, cur):
76	master_id = self.columns[0][2]
77	print >> sys.stderr, "master: %d" % master_id
78	for view in RG_VIEWS:
79	cur.execute(SELECT_RG % (view, int(master_id)))
80	rs = []
81	while True:
82	row = cur.fetchone()
83	if not row: break
84	cs = row._index.items() # XXX: hackish!
85	cs.sort(lambda a, b: cmp(a[1], b[1]))
86	cs = [(a[0], a[1], row[a[1]]) for a in cs]
87	rs.append(RepeatGroup(cs))
88	print >> sys.stderr, "\t%d %s" % (len(rs), view)
89	self.rgs.append(rs)
90
91	class Cases:
92	def __init__(self):
93	self.cases = []
94
95	def appendCase(self, case):
96	self.cases.append(case)
97
98	def fetchRepeatGroups(self, cur):
99	for case in self.cases:
100	case.fetchRepeatGroups(cur)
101
102	def maxRepeatGroups(self):
103	mc = [0] * len(RG_VIEWS)
104
105	for rgs in [c.rgs for c in self.cases]:
106	for i in range(len(mc)):
107	mc[i] = max(mc[i], len(rgs[i]))
108
109	for l in mc:
110	print >> sys.stderr, "max len: %d" % l
111
112	self.maxRGs = mc
113
114	def noneEmptyRepeatGroup(self, i):
115	for case in self.cases:
116	rgs = case.rgs
117	if len(rgs[i]):
118	return rgs[i][0]
119	return None
120
121	def generateHeader(self, out):
122	if not self.cases:
123	return ""
124	case = self.cases[0]
125	headers = case.headerFields()
126	for i in range(len(RG_VIEWS)):
127	rg = self.noneEmptyRepeatGroup(i)
128	if rg is None:
129	continue
130	hs = rg.headerFields()
131	for j in range(self.maxRGs[i]):
132	if j > 0:
133	headers.extend([ "%s_%d" % (x, j) for x in hs])
134	else:
135	headers.extend(hs)
136	print >> out, "#%s" % SEP.join(headers)
137
138	def dumpFields(self, out):
139	fills = []
140	for i in range(len(RG_VIEWS)):
141	rg = self.noneEmptyRepeatGroup(i)
142	if rg is None: fills.append(0)
143	else: fills.append(rg.numFields())
144
145	for case in self.cases:
146	line = case.valueFields()
147	for m, rg, fill in zip(self.maxRGs, case.rgs, fills):
148	count = 0
149	for r in rg:
150	line.extend(r.valueFields())
151	count += 1
152	while count < m:
153	line.extend([''] * fill)
154	count += 1
155	print >> out, "%s" % SEP.join(map(lambda x: u"%s" % x, line))
156
157
158	def main():
159	Writer = codecs.getwriter("latin-1")
160	out = Writer(sys.stdout)
161	con, cur = None, None
162	try:
163	PASSWORD = raw_input("> ")
164	con = dbapi.connect(
165	database = DBNAME,
166	host = HOST,
167	port = PORT,
168	user = USER,
169	password = PASSWORD)
170
171	cur = con.cursor(cursor_factory=extras.DictCursor)
172	cur.execute(ANON_ON)
173	row = cur.next()
174
175	if row:
176	if row[0] != 'on':
177	print >> sys.stderr, "Anonymer Transfer nicht gestattet"
178	return
179	else:
180	print >> sys.stderr, "Anonymer Transfer nicht gestattet"
181	return
182
183	cur.execute(NUM_EINV)
184	row = cur.next()
185
186	if not row:
187	print >> sys.stderr, "Keine Akten gefunden"
188	return
189
190	print >> sys.stderr, "Uebermittelbare Akten: %d" % row[0]
191
192	cur.execute(SELECT_MASTER_TBL)
193
194	cases = Cases()
195
196	while True:
197	row = cur.fetchone()
198	if not row: break
199	columns = row._index.items() # XXX: hackish!
200	columns.sort(lambda a, b: cmp(a[1], b[1]))
201	columns = [(a[0], a[1], row[a[1]]) for a in columns]
202	cases.appendCase(Case(columns))
203
204	cases.fetchRepeatGroups(cur)
205	cases.maxRepeatGroups()
206	cases.generateHeader(out)
207	cases.dumpFields(out)
208
209	finally:
210	if cur:
211	try: cur.close()
212	except: pass
213	if con:
214	try: con.close()
215	except: pass
216
217
218	if __name__ == "__main__":
219	main()