def getExtraFieldsForRelation(uid_temp, uid):
db = con.connectMySQL()
cur = db.cursor()
response = client.get_decision_type_details(uid_temp)
extraFields = response['extraFields']
recursiveExtraFieldsForRelation(db, cur, '', extraFields, uid)
db.commit()
db.close()
def importingTypes(client):
response = client.get_decision_types()
db = con.connectMySQL()
cur = db.cursor()
d_types = response["decisionTypes"]
for d_type in d_types:
fields = ['uid', 'label']
SQLcommand = "insert into type(uid,label) VALUES (%s,%s)"
actuallInsertion(fields, SQLcommand, cur, db, d_type)
db.commit()
db.close()
def importingDictionaryItems(client):
response = client.get_dictionary('THK')
db = con.connectMySQL()
cur = db.cursor()
items = response["items"]
for item in items:
fields = ['uid', 'label']
SQLcommand = "insert into dictionary_item(uid,label) VALUES (%s,%s)"
actuallInsertion(fields, SQLcommand, cur, db, item)
db.commit()
db.close()
def relationInsertIntoDecisionUnit(value):
db = con.connectMySQL()
cur = db.cursor()
for unit in value['unitIds']:
# print value['ada'],
# print value['versionId'],
# print thematicCatergory
fields = ['ada', 'versionId', 'unitId']
value['unitId'] = unit
SQLcommand = "insert into decision_unit(decision_ada, decision_version, unit_id) VALUES (%s,%s,%s)"
actuallInsertion(fields, SQLcommand, cur, db, value)
db.commit()
db.close()
def relationInsertIntoDecisionDictionaryItem(value):
db = con.connectMySQL()
cur = db.cursor()
for thematicCatergory in value['thematicCategoryIds']:
# print value['ada'],
# print value['versionId'],
# print thematicCatergory
fields = ['ada', 'versionId', 'thematicCategoryId']
value['thematicCategoryId'] = thematicCatergory
SQLcommand = "insert into decision_dictionary_item(decision_ada, decision_version, them_cat_id) VALUES (%s,%s,%s)"
actuallInsertion(fields, SQLcommand, cur, db, value)
db.commit()
db.close()
def relationInsertIntoUnitDictionaryItem(value):
db = con.connectMySQL()
cur = db.cursor()
for domain in value['unitDomains']:
# print value['ada'],
# print value['versionId'],
# print thematicCatergory
fields = ['uid', 'domain']
value['domain'] = domain
SQLcommand = "insert into unit_dictionary_item(unit_id,item_id) VALUES (%s,%s)"
actuallInsertion(fields, SQLcommand, cur, db, value)
db.commit()
db.close()
def importingOrganization(client):
db = con.connectMySQL()
cur = db.cursor()
response = client.get_organization('6114')
organization = response
fields = [
'uid', 'label', 'odeManagerEmail', 'status', 'vatNumber', 'website'
]
# SQLcommand = "insert into organization('organization_id','abbreviation','category_id','fek_issue_id','fek_number','fek_year','label','latin','ode_manager_email','status','supervisor_id','vat_number','website') VALUES ("+values+")"
SQLcommand = "insert into organization(uid,label,ode_manager_email,status,vat_number,website) VALUES (%s,%s,%s,%s,%s,%s)"
# print SQLcommand
actuallInsertion(fields, SQLcommand, cur, db, organization)
db.commit()
db.close()
def importingDecisions(client, current_page):
q = "submissionTimestamp:[DT(2006-03-01T00:00:00) TO DT(2014-11-11T23:59:59)] AND (organizationUid:6114)"
response = client.get_advanced_search_results(q, current_page, query_size)
db = con.connectMySQL()
cur = db.cursor()
# db = 1
# cur = 1
decisions = response["decisions"]
for decision in decisions:
fields = [
'ada', 'versionId', 'correctedVersionId', 'issueDate',
'protocolNumber', 'subject', 'decisionTypeId'
]
g_id = findGrailsId(db, cur, 'type',
decision['decisionTypeId'].encode('utf-8'))
decision['decisionTypeId'] = g_id
SQLcommand = "insert into decision(ada, version_id, corrected_version_id, issue_date, protocol_number, subject, type_id) VALUES (%s,%s,%s,%s,%s,%s,%s)"
actuallInsertion(fields, SQLcommand, cur, db, decision)
dec_id = findDecisionId(db, cur, decision['versionId'].encode('utf-8'),
decision['ada'].encode('utf-8'))
thematicCategoryIds = decision['thematicCategoryIds']
for thematic in thematicCategoryIds:
value = {}
value['decisionId'] = dec_id
value['thematicId'] = findGrailsId(db, cur, 'dictionary_item',
thematic.encode('utf-8'))
fields = ['decisionId', 'thematicId']
SQLcommand = "insert into decision_dictionary_item(decision_thematic_cat_id,dictionary_item_id) VALUES (%s,%s)"
actuallInsertion(fields, SQLcommand, cur, db, value)
extraFields = decision['extraFieldValues']
importingRecursiveExtraFields(db, cur, '', extraFields, dec_id)
for unit in decision['unitIds']:
value = {}
value['decisionId'] = dec_id
value['unit'] = findGrailsId(db, cur, 'unit', unit.encode('utf-8'))
fields = ['decisionId', 'unit']
SQLcommand = "insert into decision_unit(decision_units_id,unit_id) VALUES (%s,%s)"
actuallInsertion(fields, SQLcommand, cur, db, value)
for signer in decision['signerIds']:
value = {}
value['decisionId'] = dec_id
value['signer'] = findGrailsId(db, cur, 'signer',
signer.encode('utf-8'))
fields = ['decisionId', 'signer']
SQLcommand = "insert into decision_signer(decision_signers_id,signer_id) VALUES (%s,%s)"
actuallInsertion(fields, SQLcommand, cur, db, value)
# var = raw_input("Click to continue...")
db.commit()
db.close()
def fillingSignerUnitRelation(client):
db = con.connectMySQL()
cur = db.cursor()
cur.execute("SELECT uid FROM signer")
for row in cur.fetchall():
response = client.get_signer(row[0])
units = response['units']
for unit in units:
unit['uid'] = findGrailsId(db, cur, 'unit', unit['uid'])
fields = ['signerId', 'uid', 'positionLabel']
unit['signerId'] = findGrailsId(db, cur, 'signer', row[0])
SQLcommand = "insert into signer_unit(signer_id,unit_id,position) VALUES (%s,%s,%s)"
actuallInsertion(fields, SQLcommand, cur, db, unit)
db.commit()
db.close()
def printPositions(response):
'''Return all positions from diavgeia
Arguments
response: a json response returned from OpendataClient
'''
db = con.connectMySQL()
cur = db.cursor()
positions = response["positions"]
for position in positions:
print "Position " + position["uid"]
print "\tLabel: " + position["label"]
insertIntoPositions(db, cur, position)
db.commit()
db.close()
def printOneOrg(response, client):
'''Return all organizations from diavgeia
Arguments
response: a json response returned from OpendataClient
client: OpendataClient instance
'''
db = con.connectMySQL()
cur = db.cursor()
organization = response
print organization['uid']
insertIntoOrganizations(db, cur, organization)
printUnits(client, organization["uid"], db, cur)
db.commit()
db.close()
def importingUnits(client):
db = con.connectMySQL()
cur = db.cursor()
units = client.get_organization_units('6114', 'all')['units']
g_id = findGrailsId(db, cur, 'organization', '6114')
for unit in units:
fields = ['uid', 'label', 'active', 'myParentId']
SQLcommand = "insert into unit(uid,label,active,parent_id) VALUES (%s,%s,%s,%s)"
unit['myParentId'] = g_id
actuallInsertion(fields, SQLcommand, cur, db, unit)
SQLcommand = "insert into unit(uid,label,parent_id) VALUES ('6114','ΔΗΜΟΣ ΘΕΣΣΑΛΟΝΙΚΗΣ',{0})".format(
g_id)
try:
cur.execute(SQLcommand)
except Exception, e:
print e
def importingSigners(client):
response = client.get_organization_signers_all('6114')
db = con.connectMySQL()
cur = db.cursor()
signers = response['signers']
g_id = findGrailsId(db, cur, 'organization', '6114')
for signer in signers:
fields = [
'uid', 'active', 'activeFrom', 'activeUntil', 'firstName',
'lastName', 'myOrgId'
]
SQLcommand = "insert into signer(uid,active,active_from,active_until,first_name,last_name,org_id) VALUES (%s,%s,%s,%s,%s,%s,%s)"
signer['myOrgId'] = g_id
actuallInsertion(fields, SQLcommand, cur, db, signer)
db.commit()
db.close()
def printAllDictionaries(response, client):
'''Print all the dictionaries' labels
Arguments:
response: a json response returned from OpendataClient
client: OpendataClient instance
'''
db = con.connectMySQL()
cur = db.cursor()
allDictionaries = response["dictionaries"]
for i in allDictionaries:
print "Dictionary: " + i['uid']
# print '\t'+i["label"]
insertIntoDictionaries(db, cur, i)
printDictionaryDetails(client, i['uid'], db, cur)
db.commit()
db.close()
def printSigners(response, uid):
'''Print signers for a specific organization
Arguments:
client: OpendataClient instance
uid: The organization's uid
'''
db = con.connectMySQL()
cur = db.cursor()
signers = response['signers']
for signer in signers:
# print "\t\tSigner "+signer['uid']
# for detail in signer:
# print "\t\t\t"+detail+': ',
# print signer[detail]
insertIntoSigners(db, cur, signer, uid)
db.commit()
db.close()
def printDecisions(response):
'''Print the urls of all decisions
Arguments:
response: a json response returned from OpendataClient
'''
db = con.connectMySQL()
cur = db.cursor()
# db = 1
# cursor = 1
decisions = response["decisions"]
for decision in decisions:
insertIntoDecisions(db, cur, decision)
# for i in decision:
# print i,":",decision[i]
db.commit()
db.close()
def fillingDecisionsRelationships(client):
db = con.connectMySQL()
cur = db.cursor()
cur.execute("SELECT ada,version_id FROM decision")
for row in cur.fetchall():
response = client.get_decision(row[0].encode('utf-8'))
thematicCategoryIds = response['thematicCategoryIds']
for thematic in thematicCategoryIds:
value = {}
value['decisionAda'] = row[0]
value['versionId'] = row[1]
value['thematic'] = thematic
fields = ['decisionAda', 'versionId', 'thematic']
SQLcommand = "insert into decision_dictionary_item(decision_ada,decision_version_id,dictionary_item_id) VALUES (%s,%s,%s)"
actuallInsertion(fields, SQLcommand, cur, db, value)
extraFields = response['extraFieldValues']
importingRecursiveExtraFields(db, cur, '', extraFields)
db.commit()
db.close()
def printTypes(response, client):
'''Print all the types' labels
Arguments:
response: a json response returned from OpendataClient
'''
db = con.connectMySQL()
cur = db.cursor()
d_types = response["decisionTypes"]
for d_type in d_types:
print "Type: " + d_type['uid']
for key in d_type:
if (isinstance(d_type[key], (bool))):
print "\t" + key + ": " + str(d_type[key])
elif (d_type[key] != None and key != 'uid'):
print "\t" + key + ": " + d_type[key]
insertIntoTypes(db, cur, d_type)
printTypesDetails(client, d_type['uid'].encode('utf-8'), d_type['uid'],
db, cur)
db.commit()
db.close()
def printOrganizations(response, client):
'''Return all organizations from diavgeia
Arguments
response: a json response returned from OpendataClient
client: OpendataClient instance
'''
db = con.connectMySQL()
cur = db.cursor()
organizations = response["organizations"]
for organization in organizations:
print "Organization " + organization["uid"]
# for key in organization:
# print organization[key]!=None
# print key
# if (organization[key]!=None and key!='uid' and key!='organizationDomains'):
# print "\t"+key+": "+organization[key]
insertIntoOrganizations(db, cur, organization)
printUnits(client, organization["uid"], db, cur)
db.commit()
db.close()
def importingGeo():
db = con.connectMySQL()
cur = db.cursor()
getGEO(cur)
db.commit()
db.close()