aboutsummaryrefslogtreecommitdiffstats
path: root/openbsc/tools/26c3-guru-import.py
blob: b61a1f714389ea41e3b122d1c41e008df653bc2b (plain)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
#!/usr/bin/python2.5

# Based loosely on hlrsync.py from Jan Luebbe
# (C) 2009 Daniel Willmann
# All Rights Reserved
#
# 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.
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
# GNU General Public License for more details.
#
# You should have received a copy of the GNU General Public License along
# with this program; if not, write to the Free Software Foundation, Inc.,
# 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA.

from __future__ import with_statement

import urllib
from pysqlite2 import dbapi2 as sqlite3
import sys

hlr = sqlite3.connect(sys.argv[1])
web = urllib.urlopen(sys.argv[2]).read()

# switch to autocommit
hlr.isolation_level = None

hlr.row_factory = sqlite3.Row

web = web.split("\n")

# Remove last empty newline
# List of extension - imei/imsi tuples from GURU2
web_tuple = [ (int(i.split(" ")[0]), int(i.split(" ")[1])) for i in web if len(i) > 0 ]

for x in web_tuple:
	exten = x[0]
	imxi = x[1]

	# Enforce numering plan of 26c3
	if exten < 9100 or exten > 9999:
		continue

	# Test if it is an IMSI and hasn't yet been authorized
	subscr = hlr.execute("""
		SELECT * FROM Subscriber WHERE imsi=="%015u"
	""" % (imxi) ).fetchall()

	#and authorized==0

	# Not an IMSI
	if len(subscr) == 0:
		equip = hlr.execute("""
			SELECT id,imei FROM Equipment WHERE imei LIKE "%014u%%"
		""" % (imxi/10) ).fetchall();
		#print equip

		if len(equip) == 0:
			continue

		subscrid = hlr.execute("""
			SELECT * FROM EquipmentWatch WHERE equipment_id=%u ORDER BY created LIMIT 1
		""" % (int(equip[0]['id'])) ).fetchall();

		#print subscrid

		if len(subscrid) == 0:
			continue

		subscr = hlr.execute("""
			SELECT * FROM Subscriber WHERE id==%u
		""" % subscrid[0]['subscriber_id']).fetchall();
		# and authorized==0

	if len(subscr) == 0:
		continue

	subscr = subscr[0]
	# Now we have an unauthorized subscriber for the imXi
	print exten, imxi
	print subscr

	hlr.execute("""UPDATE Subscriber SET authorized = 1,extension="%s" \
	    WHERE id = %u
	""" % (str(exten), subscr['id']) );

hlr.close()