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	teichmann	229	#!/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()