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
from   types import StringTypes

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

SEP = "\t"

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