def __init__(self, id):
if (id == 'anaveriler'):
self.model = ModelAnaveriler
elif (id == 'aktarimlar'):
self.model = ModelAktarimlar
else:
self.model = None
self.id = id
self.conn = Connect()
self.session = self.conn.session()
class Auth():
def __init__(self):
self.conn = Connect()
self.session = self.conn.session()
def __del__(self):
self.session.close()
def getCids(self, uid):
try:
model = AuthModel
data = self.session.query(model).filter_by(uid=uid)
data = data.order_by(model.cid_name)
dict = []
for row in data:
dict.append({'cid': row.cid, 'name': row.cid_name})
# print('dict: ', dict)
_json = jsonify(dict)
if (len(dict) == 0):
return Response([])
else:
return _json
except Exception as err:
return Response("*** Error! *** ViewAuth->getCids Exception!! ",
err)
def login(self, uid, pwd):
try:
model = AuthLoginModel
data = self.session.query(model).filter_by(uid=uid, pwd=pwd)
dict = []
# JWT Token
for row in data:
payload = {"uid": row.uid, "admin": "false"}
secretKey = ConfigJWT.SECRETKEY
signature = jwt.encode(payload, secretKey,
algorithm='HS256').decode('utf-8')
dict.append({'token': signature})
_json = jsonify(dict)
if (len(dict) == 0):
return Response([])
else:
return _json
except Exception as err:
return Response("*** Error! *** Login Exception!! ", err)
class DB():
def __init__(self):
self.conn = Connect()
self.session = self.conn.session()
self.engine = self.conn.engine
def __del__(self):
self.session.close()
def createBase(self):
try:
Base.metadata.create_all(self.engine)
except exc.SQLAlchemyError:
print("DB Error ", exc)
def getChartData(params):
conn = Connect()
session = conn.session()
name = params.get('name')
type = params.get('type')
# pie ve bar -> cid, name, value döner.. cid-> name :string, value: int değeri dönerken; bar-> name ve value [array] değerleri döner.
if (type == "pie" and name in ['kv', 'profil', 'birim', 'bolum', 'surec', 'kurumlar_data', 'sistemler_data', 'ortamlar_data', 'ulkeler_data']):
return pieData(session, params)
elif (type == "bar"):
return barData(session, params)
elif (type == "geo"):
return geoData(session, params)
elif (type == "tree" and name in ['profil', 'birim', 'bolum', 'surec']):
return treeData(session, params)
elif (type == "org"):
return orgData(session, params)
else:
return Response('CHART ID FOR MODEL NOT FOUND!')
session.close()
class ProjelerApi():
def __init__(self, yil, grup):
self.conn = Connect()
self.session = self.conn.session()
self.yil = yil
self.grup = grup
def __del__(self):
self.session.close()
def message(self):
try:
dict = []
data = self.session.query(Projeler.id, Projeler.yil, Projeler.grup, Projeler.baslik, Projeler.aciklama, Projeler.sonuc, Projeler.birim, Projeler.durum, Projeler.zamandamgasi)
if (self.grup is not None):
data = data.filter(Projeler.yil==self.yil, Projeler.grup==self.grup)
else:
data = data.filter(Projeler.yil==self.yil)
data = data.order_by(Projeler.durum, Projeler.grup)
for row in data:
dict.append({'id':row.id, 'yil':row.yil,'grup':row.grup, 'baslik':row.baslik, 'aciklama':row.aciklama, 'sonuc':row.sonuc, 'birim':row.birim, 'durum':row.durum, 'zamandamgasi':row.zamandamgasi})
_json = jsonify({"projeler":dict})
if (len(dict) == 0):
return Response("NO DATA found!")
else:
return _json
except Exception as e:
return Response("sa query error! ",e)
# if __name__ == "__main__":
# e = ProjelerApi()
# e.message()
# print("Base created successfully..")
class ProfillerApi():
def __init__(self):
self.conn = Connect()
self.session = self.conn.session()
def __del__(self):
self.session.close()
def message(self):
try:
dict = []
data = self.session.query(Profiller.id, Profiller.name,
Profiller.timestamp)
data = data.order_by(Profiller.name)
for row in data:
dict.append({
'id': row.id,
'name': row.name,
'timestamp': row.timestamp
})
# _json = jsonify({"Profiller":dict})
_json = jsonify(dict)
if (len(dict) == 0):
return Response("NO DATA found!")
else:
return _json
except Exception as e:
return Response("sa query error! ", e)
# if __name__ == "__main__":
# e = ProfillerApi()
# e.message()
# print("Base created successfully..")
class Gfox():
def __init__(self):
self.conn = Connect()
self.session = self.conn.session()
def __del__(self):
self.session.close()
# GET query results
def getCidName(self, cid):
try:
sql = "select name from t_cid where pidm={0} limit 1".format(cid)
result = self.session.execute(sql)
name = ""
for item in result:
name = item.name
return name
except Exception:
return "Error!"
def isDpoUser(self, uid):
try:
filter = {"uid": uid}
sql = "select 1 from auth where uid={uid!r} and (dpo=true or admin=true) limit 1".format(
**filter)
result = self.session.execute(sql)
dpo = False
for exist in result:
dpo = True
return dpo
except Exception as err:
print("Exception Error: ", err)
return Response("!!! PieChart API Error !!!", err)
class GetDokumanlar():
def __init__(self):
self.conn = Connect()
self.session = self.conn.session()
def __del__(self):
self.session.close()
def message(self):
try:
dict = []
sql = """
select ss.id, b.name birim, d.name dokuman, y.name yayin, ss.timestamp
from ss_dokumanlar ss, birimler b, dokumanlar d, ss_yayindurumu y
where ss.birim_id = b.id and ss.dokuman_id = d.id and ss.yayin_id = y.id
order by ss.id
"""
data = self.session.execute(sql)
for row in data:
dict.append({
'id': row.id,
'birim': row.birim,
'dokuman': row.dokuman,
'yayin': row.yayin,
'timestamp': row.timestamp
})
_json = jsonify(dict)
if (len(dict) == 0):
return Response([])
else:
return _json
except Exception as e:
return Response("sa query error! ", e)
class GetToplamaKanallari():
def __init__(self):
self.conn = Connect()
self.session = self.conn.session()
def __del__(self):
self.session.close()
def message(self):
try:
dict = []
sql = """
select ss.id, b.name birim, k.name kanal, ss.timestamp
from ss_toplama_kanallari ss, birimler b, kanallar k
where ss.birim_id = b.id and ss.kanal_id = k.id
order by ss.id
"""
data = self.session.execute(sql)
for row in data:
dict.append({
'id': row.id,
'birim': row.birim,
'kanal': row.kanal,
'timestamp': row.timestamp
})
_json = jsonify(dict)
if (len(dict) == 0):
return Response([])
else:
return _json
except Exception as e:
return Response("sa query error! ", e)
class GetKullanilanSistemler():
def __init__(self):
self.conn = Connect()
self.session = self.conn.session()
def __del__(self):
self.session.close()
def message(self):
try:
dict = []
sql = """
select ss.id, b.name birim, s.name sistem, ss.timestamp
from ss_kullanilan_sistemler ss, birimler b, sistemler s
where ss.birim_id = b.id and ss.sistem_id = s.id
order by ss.id
"""
data = self.session.execute(sql)
for row in data:
dict.append({
'id': row.id,
'birim': row.birim,
'sistem': row.sistem,
'timestamp': row.timestamp
})
_json = jsonify(dict)
if (len(dict) == 0):
return Response([])
else:
return _json
except Exception as e:
return Response("sa query error! ", e)
class GetKurumlar():
def __init__(self):
self.conn = Connect()
self.session = self.conn.session()
def __del__(self):
self.session.close()
def message(self):
try:
dict = []
sql = """
select ss.id, b.name birim, k.name kurum, ss.timestamp
from ss_kurumlar ss, birimler b, kurumlar k
where ss.birim_id = b.id and ss.kurum_id = k.id
order by ss.id
"""
data = self.session.execute(sql)
for row in data:
dict.append({
'id': row.id,
'birim': row.birim,
'kurum': row.kurum,
'timestamp': row.timestamp
})
_json = jsonify(dict)
if (len(dict) == 0):
return Response([])
else:
return _json
except Exception as e:
return Response("DB SQL Exception! ", e)
class Envanter():
def __init__(self, id):
if (id == 'anaveriler'):
self.model = ModelAnaveriler
elif (id == 'aktarimlar'):
self.model = ModelAktarimlar
else:
self.model = None
self.id = id
self.conn = Connect()
self.session = self.conn.session()
def __del__(self):
self.session.close()
# GET query results
def get(self, params):
try:
cid = params.get('cid')
uid = params.get('uid')
dpoMode = isDpoUser(uid)
if dpoMode:
result = self.session.query(self.model).filter_by(
cid=cid).order_by(self.model.pidm.desc())
print("uid: ", uid, " as dpo user connected..")
else:
result = self.session.query(self.model).filter_by(
cid=cid, uid=uid).order_by(self.model.pidm.desc())
print("uid: ", uid, " as regular user connected..")
print("FrameworkEnvanter query succesfull...")
if (self.id == "anaveriler"):
dataf = [
'birim', 'bolum', 'surec', 'kv_data', 'profil', 'sure',
'ortamlar_data', 'tedbirler_data', 'kanallar_data',
'sistemler_data', 'dayanaklar_data', 'isleme_amaclari_data'
]
elif (self.id == "aktarimlar"):
dataf = [
'birim', 'bolum', 'surec', 'kv_data', 'kurumlar_data',
'paylasim_amaclari_data', 'dayanaklar_data',
'profiller_data', 'paylasim_sekilleri_data',
'ulkeler_data', 'aciklama', 'bilgiveren'
]
else:
dataf = None
return '', 202
dict = []
for row in result:
myRow = {}
# pidm, cid, uid, timestamp için..
instance = inspect(row)
for key, item in instance.attrs.items():
if (key not in ['data', 'timestamp']):
myRow[key] = item.value
# json data alanı için
for k in dataf:
# react frameworkte tanımlanan alan veritabanında varmı yok mu kontrolü..
if (k in row.data):
myRow[k] = row.data[k]
else:
if ("_data" in k):
myRow[k] = []
else:
myRow[k] = ""
dict.append(myRow)
_json = jsonify(dict)
if (len(dict) == 0):
# böyle [] yapmazsan react tarafında data.map funciton not found hatası alırsın!!
return Response([])
else:
return _json
except Exception as err:
return Response("!!! Query ERROR !!!", err)
def update(self, params):
try:
pidm = params.get('pidm')
cid = params.get('cid')
uid = params.get(
'uid'
) # başkasının yaptığı kaydı update etmez yoksa.. kaldırdım aşağıdaki filtreden
row = self.session.query(self.model).filter_by(pidm=pidm, cid=cid)
row.update(params)
self.session.commit()
print("*** UPDATE successfully ***")
return '', 204
except Exception as err:
print("!!! UPDATE error !!! ", err)
return '', 404
def add(self, params):
try:
record = []
record.append(params)
for row in record:
self.session.add(self.model(**row))
self.session.commit()
print("*** Record ADD successfully *** ")
return '', 204
except Exception as err:
print("!!! Record ADD ERROR !!! ", err)
return '', 404
def delete(self, params):
try:
pidm = params.get('pidm')
cid = params.get('cid')
# uid = params.get('uid') # başkasının yaptığı kaydı update etmez yoksa.. kaldırdım aşağıdaki filtreden
row = self.session.query(self.model).filter_by(pidm=pidm,
cid=cid).one()
self.session.delete(row)
self.session.commit()
print("*** Record DELETED successfully ***")
return '', 204
except Exception as err:
print("!!! ERROR ON DELETE !!! ", err)
return '', 404
class Export():
def __init__(self):
self.conn = Connect()
self.session = self.conn.session()
def __del__(self):
self.session.close()
def createSheetTanimlar(self, wb, model, title_, cid_):
try:
if (model == ModelProfiller):
ws = wb.active
ws.title = title_
else:
ws = wb.create_sheet(title=title_)
# TITLE
ws['A1'] = "Kodu"
# ws['A1'].font = Font(bold=True)
ws['B1'] = title_
if (model == ModelSistemler):
ws['C1'] = "Dahili"
data = self.session.query(
model.pidm, model.name, model.local).filter_by(cid=cid_)
elif (model == ModelUlkeler):
ws['C1'] = "Tel Kodu"
ws['D1'] = "Güvenli Ülke"
data = self.session.query(
model.pidm, model.name, model.phone_area, model.secure).filter_by(cid=cid_)
else:
data = self.session.query(
model.pidm, model.name).filter_by(cid=cid_)
data = data.order_by(model.name)
i = 2
for row in data:
ws['A' + str(i)] = row.pidm
ws['B' + str(i)] = row.name
if (model == ModelSistemler):
ws['C' + str(i)] = row.local
elif (model == ModelUlkeler):
ws['C' + str(i)] = row.phone_area
ws['D' + str(i)] = row.secure
i = i + 1
return ws
except Exception:
print("export.py > createSheetTanimlar Exception!")
return None
def createSheetKVProfil(self, wb, cid_):
try:
ws = wb.create_sheet("KV Profiller")
# TITLE
ws['A1'] = "Kodu"
ws['B1'] = "Profil"
ws['C1'] = "Birim"
ws['D1'] = "Veri Kategorileri"
ws['A1'].font = Font(bold=True)
ws['B1'].font = Font(bold=True)
ws['C1'].font = Font(bold=True)
ws['D1'].font = Font(bold=True)
# model=ViewKVProfilModel
model = ModelAnaveriler
data = self.session.query(
model.pidm, model.profil_name, model.birim_name, model.data).filter_by(cid=cid_)
data = data.order_by(model.profil_name, model.birim_name)
i = 2
for row in data:
ws['A' + str(i)] = row.pidm
ws['B' + str(i)] = row.profil_name
ws['C' + str(i)] = row.birim_name
ws['D' + str(i)] = json.dumps(row.data)
i = i + 1
return ws
except Exception:
print("export.py > createSheetKVProfil Exception!")
return None
def createSheetKVPaylasim(self, wb, cid_):
try:
ws = wb.create_sheet("KV Paylaşımları")
# TITLE
ws['A1'] = "Kodu"
ws['B1'] = "Birim"
ws['C1'] = "Veri Kategorisi"
ws['D1'] = "Paylaşılan Kurum"
ws['E1'] = "İşleme Amaçları"
ws['F1'] = "Paylaşım Amaçları"
ws['G1'] = "Paylaşım Şekilleri"
ws['A1'].font = Font(bold=True)
ws['B1'].font = Font(bold=True)
ws['C1'].font = Font(bold=True)
ws['D1'].font = Font(bold=True)
ws['E1'].font = Font(bold=True)
ws['F1'].font = Font(bold=True)
ws['G1'].font = Font(bold=True)
# model=ViewKVPaylasimModel
model = ModelViewAktarimlar
data = self.session.query(model.pidm, model.birim_name, model.kv_name, model.kurum_name,
model.islemeamaclari_data, model.paylasimamaclari_data, model.paylasimsekilleri_data).filter_by(cid=cid_)
data = data.order_by(
model.birim_name, model.kv_name, model.kurum_name)
i = 2
for row in data:
ws['A' + str(i)] = row.pidm
ws['B' + str(i)] = row.birim_name
ws['C' + str(i)] = row.kv_name
ws['D' + str(i)] = row.kurum_name
ws['E' + str(i)] = json.dumps(row.islemeamaclari_data)
ws['F' + str(i)] = json.dumps(row.paylasimamaclari_data)
ws['G' + str(i)] = json.dumps(row.paylasimsekilleri_data)
i = i + 1
return ws
except Exception:
print("export.py > createSheetKVPaylasim Exception!")
return None
def createSheetKVAnaveri(self, wb, cid_):
try:
ws = wb.create_sheet("KV Ana Verileri")
# TITLE
ws['A1'] = "Kodu"
ws['B1'] = "Birim"
ws['C1'] = "Veri Kategorisi"
ws['D1'] = "Sure"
ws['E1'] = "Güvenli Ülkeler"
ws['F1'] = "Kanallar"
ws['G1'] = "Dokumanlar"
ws['H1'] = "Sistemler"
ws['I1'] = "Dayanaklar"
ws['J1'] = "Ortamlar"
ws['K1'] = "Tedbirler"
ws['A1'].font = Font(bold=True)
ws['B1'].font = Font(bold=True)
ws['C1'].font = Font(bold=True)
ws['D1'].font = Font(bold=True)
ws['E1'].font = Font(bold=True)
ws['F1'].font = Font(bold=True)
ws['G1'].font = Font(bold=True)
ws['H1'].font = Font(bold=True)
ws['I1'].font = Font(bold=True)
ws['J1'].font = Font(bold=True)
ws['K1'].font = Font(bold=True)
# model=ViewKVAnaveriModel
model = ModelViewAnaveriler
data = self.session.query(model).filter_by(cid=cid_)
data = data.order_by(model.birim_name, model.kv_name)
i = 2
for row in data:
ws['A' + str(i)] = row.pidm
ws['B' + str(i)] = row.birim_name
ws['C' + str(i)] = row.kv_name
ws['D' + str(i)] = row.sure_name
ws['E' + str(i)] = json.dumps(row.ulkeler_data)
ws['F' + str(i)] = json.dumps(row.kanallar_data)
ws['G' + str(i)] = json.dumps(row.dokumanlar_data)
ws['H' + str(i)] = json.dumps(row.sistemler_data)
ws['I' + str(i)] = json.dumps(row.dayanaklar_data)
ws['J' + str(i)] = json.dumps(row.ortamlar_data)
ws['K' + str(i)] = json.dumps(row.tedbirler_data)
i = i + 1
return ws
except Exception:
print("export.py > createSheetKVPAnaveri Exception!")
return None
def downloadExcel(self, cid_):
try:
wb = Workbook()
ws = self.createSheetTanimlar(
wb, ModelProfiller, 'Profiller', cid_)
ws = self.createSheetTanimlar(wb, ModelBirimler, 'Birimler', cid_)
ws = self.createSheetTanimlar(
wb, ModelKV, 'Veri Kategorileri', cid_)
ws = self.createSheetTanimlar(
wb, ModelIslemeAmaclari, 'İşleme Amaçları', cid_)
ws = self.createSheetTanimlar(wb, ModelKanallar, 'Kanallar', cid_)
ws = self.createSheetTanimlar(
wb, ModelSistemler, 'Sistemler', cid_)
ws = self.createSheetTanimlar(wb, ModelOrtamlar, 'Ortamlar', cid_)
ws = self.createSheetTanimlar(wb, ModelSureler, 'Sureler', cid_)
ws = self.createSheetTanimlar(wb, ModelKurumlar, 'Kurumlar', cid_)
ws = self.createSheetTanimlar(
wb, ModelDayanaklar, 'Dayanaklar', cid_)
ws = self.createSheetTanimlar(
wb, ModelPaylasimAmaclari, 'PaylasimAmaclari', cid_)
ws = self.createSheetTanimlar(
wb, ModelPaylasimSekilleri, 'PaylasimSekilleri', cid_)
ws = self.createSheetTanimlar(wb, ModelUlkeler, 'Ulkeler', cid_)
ws = self.createSheetKVProfil(wb, cid_)
ws = self.createSheetKVPaylasim(wb, cid_)
ws = self.createSheetKVAnaveri(wb, cid_)
# Make header bold
# for cell in ws["1:1"]:
# cell.font = Font(bold=True)
out = BytesIO()
wb.save(out)
out.seek(0)
return send_file(out, mimetype='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',
attachment_filename='gfox.xlsx', as_attachment=True)
except Exception:
return None
def __init__(self, yil, grup):
self.conn = Connect()
self.session = self.conn.session()
self.yil = yil
self.grup = grup
class Framework():
def __init__(self):
self.conn = Connect()
self.session = self.conn.session()
def __del__(self):
self.session.close()
# GET query results
def get(self, model, params):
try:
cid = params.get('cid')
uid = params.get('uid')
data = self.session.query(model).filter_by(cid=cid, uid=uid)
dict = []
for row in data:
myRow = {}
instance = inspect(row)
for key, item in instance.attrs.items():
isDataField = "_data" in key
isExcludedField = (key in ['cid', 'uid', 'timestamp'])
if isDataField:
tableName = "t_" + key.replace('_data', '')
myRow[key] = self.createDict(tableName, item.value,
cid)
elif not isExcludedField:
myRow[key] = item.value
dict.append(myRow)
_json = jsonify(dict)
if (len(dict) == 0):
# böyle [] yapmazsan react tarafında data.map funciton not found hatası alırsın!!
return Response([])
else:
return _json
except Exception as err:
return Response("!!! Get Query ERROR !!!", err)
def add(self, model, params):
try:
data = []
data.append(params)
for row in data:
self.session.add(model(**row))
self.session.commit()
print("*** Record ADD successfully *** ")
return '', 204
except Exception as err:
print("!!! Record ADD ERROR !!! ", err)
return '', 404
def update(self, model, params):
try:
pidm = params.get('pidm')
cid = params.get('cid')
uid = params.get('uid')
row = self.session.query(model).filter_by(pidm=pidm,
cid=cid,
uid=uid)
row.update(params)
self.session.commit()
print("*** UPDATE successfully ***")
return '', 204
except Exception as err:
print("!!! UPDATE error !!! ", err)
return '', 404
def delete(self, model, params):
try:
pidm = params.get('pidm')
cid = params.get('cid')
uid = params.get('uid')
row = self.session.query(model).filter_by(pidm=pidm,
cid=cid,
uid=uid).one()
self.session.delete(row)
self.session.commit()
print("*** Record DELETE successfully ***")
return '', 204
except Exception as err:
print("!!! Record DELETE ERROR !!! ", err)
return '', 404
# verbis > ekranlarında multiple veriye sahip hücrelere kaynaktan dönen pidmları [{pidm, name}] olarak dönmek için
def createDict(self, tableName, data, cid):
# create dict[pidm, name] from data[pidm]
try:
dict = []
items = array('i', data) # array as integer 'i'
for item in items:
table_pidm = item
table_name = self.getPidmName(tableName, table_pidm, cid)
dict.append({'pidm': table_pidm, 'name': table_name})
if (len(dict) == 0):
return []
else:
return dict
except Exception:
return []
# for converting json pidm -> names
def getPidmName(self, tableName, pidm, cid):
try:
sql = """
select {0}.name
from {0}
where {0}.pidm={1} and {0}.cid={2}
limit 1
""".format(tableName, pidm, cid)
data = self.session.execute(sql)
name = ""
for row in data:
name = row.name
return name
except Exception:
return "Error!"
# dropdown için..
def getOptions(self, model, params):
try:
cid = params.get('cid')
# yeni yöntemde kurum kontrolü yapmadan tanımlardan herşeyi getir.
if (cid is None):
data = self.session.query(model).all()
else:
data = self.session.query(model).filter(model.cid.in_(
[cid, 1])).all()
dict = []
for row in data:
dict.append({'pidm': row.pidm, 'name': row.name})
_json = jsonify(dict)
# print('dict: ', dict)
if (len(dict) == 0):
# böyle [] yapmazsan react tarafında data.map funciton not found hatası alırsın!!
return Response([])
else:
return _json
except Exception as err:
return Response("!!! Get Options ERROR !!!", err)
class SSCommon():
def __init__(self, modelClass):
self.conn = Connect()
self.session = self.conn.session()
self.model = modelClass
def __del__(self):
self.session.close()
def getSS(self, cid):
try:
dictBirimler = []
dictRelatedItems = []
sql = """
select pidm birim_pidm, name birim_name
from birimler
where cid=%d
""" % (cid)
data = self.session.execute(sql)
for row in data:
dictRelatedItems = self.getSSRelatedItems(row.birim_pidm, cid)
dictBirimler.append({
'birim_pidm': row.birim_pidm,
'birim_name': row.birim_name,
'related_items': dictRelatedItems
})
_json = jsonify(dictBirimler)
if (len(dictBirimler) == 0):
return Response([])
else:
return _json
except Exception as e:
return Response("DB SQL Exception! ", e)
def getSSRelatedItems(self, birim_pidm, cid): #Paylaşılan Kurumlar
try:
dictRelatedItems = []
# related_item_pidm ve name react tarafında tek olarak yazılan ortak common component içindir.
sqlKurumlar = """
select pidm,
related_item_pidm,
related_item_name
from view_sskurumlar
where birim_pidm=%d and cid=%d
""" % (birim_pidm, cid)
# sqlKurumlar = """
# select ss_kurumlar.pidm,
# (select kurumlar.pidm from kurumlar where kurumlar.pidm = ss_kurumlar.kurum_pidm limit 1) related_item_pidm,
# (select kurumlar.name from kurumlar where kurumlar.pidm = ss_kurumlar.kurum_pidm limit 1) related_item_name,
# from ss_kurumlar
# where ss_kurumlar.birim_pidm=%d and ss_kurumlar.cid=%d
# """%(birim_pidm, cid)
# Kullanılan Sistmler
sqlSistemler = """
select pidm,
related_item_pidm,
related_item_name
from view_sssistemler
where birim_pidm=%d and cid=%d
""" % (birim_pidm, cid)
# Toplama Kanalları
sqlKanallar = """
select pidm,
related_item_pidm,
related_item_name
from view_kanallar
where birim_pidm=%d and cid=%d
""" % (birim_pidm, cid)
if (self.model == SSKurumlarModel):
sql = sqlKurumlar
elif (self.model == SSSistemlerModel):
sql = sqlSistemler
elif (self.model == SSKanallarModel):
sql = sqlKanallar
else:
sql = ""
data = self.session.execute(sql)
# en baştaki pidm, ilgil tablodaki unique keydir.. react tarafında silmeyi yakalamak için..
for row in data:
dictRelatedItems.append({
'pidm':
row.pidm,
'related_item_pidm':
row.related_item_pidm,
'related_item_name':
row.related_item_name
})
return dictRelatedItems
except Exception as e:
return Response("DB SQL Exception! ", e)
def add(self):
try:
self.session.add(self.model)
self.session.commit()
print("ADD Successfully")
return '', 204
except Exception as e:
return Response("SSCommon DB Add Exception! ", e)
def delete(self):
try:
_pidm = int(self.model.pidm)
_cid = int(self.model.cid)
row = self.session.query(self.model.__class__).filter_by(
pidm=_pidm, cid=_cid).one()
self.session.delete(row)
self.session.commit()
print("DEL Successfully")
return '', 204
except Exception as err:
print("DB Error on deleting ", err)
return '', 404
def __init__(self, modelClass):
self.conn = Connect()
self.session = self.conn.session()
# !!!modelClass() parentezle yaparsan class create edilir, bu koşulda olmaz...
self.model = modelClass
class KVTalepler():
def __init__(self,modelClass):
self.conn = Connect()
self.session = self.conn.session()
self.model = modelClass
def __del__(self):
self.session.close()
def get(self):
try:
dict = []
sql = """
select base.pidm, base.isim, base.profiller_data, base.tckno, base.dogumtarihi,
base.eposta, base.tel, base.incelemedurumu,
(select islem_durumlari.name from islem_durumlari where islem_durumlari.pidm = base.islemdurumu) islemdurumu,
base.sureuzatma, base.kurumu, base.bilgitalebi, base.timestamp
from kv_talepler base
order by base.timestamp
"""
data = self.session.execute(sql)
for row in data:
profillerData = self.createDict('profiller', row.profiller_data ) #create as json[{pidm, name}]
dict.append({
'pidm':row.pidm,
'isim':row.isim ,
'profiller_data':profillerData,
'tckno':row.tckno,
'dogumtarihi':row.dogumtarihi,
'eposta':row.eposta,
'tel':row.tel,
'incelemedurumu':row.incelemedurumu,
'islemdurumu':row.islemdurumu,
'sureuzatma':row.sureuzatma,
'kurumu':row.kurumu,
'bilgitalebi':row.bilgitalebi,
'timestamp':row.timestamp
})
_json = jsonify(dict)
if (len(dict) == 0):
return Response([])
else:
return _json
except Exception as e:
return Response("KVTalepler().get() -> DB SQL Exception! ",e)
def add(self):
try:
self.session.add(self.model)
self.session.commit()
print("ADD Successfully")
return '', 204
except Exception as e:
return Response("Verbis>KVTalepler>DB Add Exception! ",e)
# verbis > ekranlarında multiple veriye sahip hücrelere kaynaktan dönen pidmları [{pidm, name}] olarak dönmek için
def createDict( self, tableName, data ):
#create dict[pidm, name] from data[pidm]
try:
dict = []
items = array('i', data)
for item in items:
table_pidm = item
table_name = self.getPidmName(tableName, table_pidm )
dict.append({'pidm':table_pidm, 'name':table_name})
if (len(dict) == 0):
return []
else:
return dict
except Exception:
return []
# for converting json pidm -> names
def getPidmName(self, tableName, pidm):
try:
sql = """
select {0}.name
from {0}
where {0}.pidm={1}
limit 1
""".format(tableName, pidm)
data = self.session.execute(sql)
name = ""
for row in data:
name = row.name
return name
except Exception:
return "Error!"
def __init__(self):
self.conn = Connect()
self.session = self.conn.session()
self.engine = self.conn.engine
class KVBase():
def __init__(self,model):
self.conn = Connect()
self.session = self.conn.session()
self.model = model
def __del__(self):
self.session.close()
def add(self):
try:
self.session.add(self.model)
self.session.commit()
print("ADD Successfully")
return '', 204
except Exception as e:
return Response("Verbis>KVPaylasim>DB Add Exception! ",e)
def update(self):
try:
pidm_ = int(self.model.pidm)
newData = self.model.data
row = self.session.query( self.model.__class__).filter_by(pidm=pidm_).one()
row.data =newData
self.session.commit()
print("Update Successfully")
return '', 204
except Exception as err:
print("DB Error on update ", err)
return '', 404
#Delete entire row
def delete(self):
try:
pidm_ = int(self.model.pidm)
row = self.session.query(
self.model.__class__).filter_by(pidm=pidm_).one()
self.session.delete(row)
self.session.commit()
print("deleted successfully")
return '', 204
except Exception as err:
print("DB Error on deleting ", err)
return '', 404
# for converting json pidm -> names
def getPidmName(self, tableName, pidm, cid):
try:
sql = """
select {0}.name
from {0}
where {0}.pidm={1} and {0}.cid={2}
limit 1
""".format(tableName, pidm, cid)
data = self.session.execute(sql)
name = ""
for row in data:
name = row.name
return name
except Exception:
return "Error!"
# verbis > ekranlarında multiple veriye sahip hücrelere kaynaktan dönen pidmları [{pidm, name}] olarak dönmek için
def createDict( self, tableName, data, cid ):
#create dict[pidm, name] from data[pidm]
try:
dict = []
items = array('i', data)
for item in items:
table_pidm = item
table_name = self.getPidmName(tableName, table_pidm ,cid)
dict.append({'pidm':table_pidm, 'name':table_name})
if (len(dict) == 0):
return []
else:
return dict
except Exception:
return []
class Auth():
def __init__(self):
self.conn = Connect()
self.session = self.conn.session()
def __del__(self):
self.session.close()
def isAdmin(self, uid):
try:
admin = False
data = self.session.query(ModelAuth).filter_by(uid=uid).limit(1)
for row in data:
admin = row.admin
return admin
except Exception as err:
return Response("*** Error! *** ViewAuth->getCids Exception!! ",
err)
def getCids(self, uid):
try:
dict = []
if self.isAdmin(uid):
model = ModelCid
data = self.session.query(model).filter().order_by(model.name)
for row in data:
dict.append({'cid': row.pidm, 'name': row.name})
else:
model = ModelViewAuth
data = self.session.query(model).filter_by(
username=uid).order_by(model.cid_name)
for row in data:
dict.append({'cid': row.cid, 'name': row.cid_name})
_json = jsonify(dict)
# print('dict: ', dict)
if (len(dict) == 0):
return Response([])
else:
return _json
except Exception as err:
return Response("*** Error! *** ViewAuth->getCids Exception!! ",
err)
def login(self, params):
try:
username = params.get("username")
password = params.get("password")
query = {
"username": username,
"password": password,
"enabled": True
}
data = self.session.query(ModelAuth).filter_by(**query).limit(1)
print("username: "******"%m/%d/%Y, %H:%M:%S") # unique key, her seferinde farklı token üretmesi için
exp = datetime.datetime.utcnow() + datetime.timedelta(
seconds=5) # 8 saat expiration koyduk...
payload = {
"uid": row.username,
"dpo": row.dpo,
"admin": row.admin,
"exp": exp
}
secretKey = ConfigJWT.SECRETKEY
# imza yaratılıyor... 3 parçadan oluşuyor.. xxxxx.yyyy.zzzzz -> yyyy kısmı payload cryptolu değil...
# zzzz kısmı xxxxx ve yyyyy nin karışımından üretilen token kısmı...
# client tarafında bunu kontrol edebilirsin..
token = jwt.encode(payload, secretKey,
algorithm='HS256').decode('utf-8')
dict.append({'token': token})
print('token created succesfully...')
_json = jsonify(dict)
if (len(dict) == 0):
print("Autherization failed...")
else:
print("Autherization successfully...", _json)
return _json # veya Response([]) boş dönmek için
except Exception as err:
return Response("*** Error! *** Login Exception!! ", err)
def getAccounts(self, params):
try:
dict = []
model = ModelViewAuth
query = {"cid": params.get('cid')}
data = self.session.query(model).filter_by(**query).order_by(
model.username)
for row in data:
dict.append({
'rownumber': row.rownumber,
'pidm': row.pidm,
'username': row.username,
'password': row.password,
'admin': row.admin,
'dpo': row.dpo,
'enabled': row.enabled
})
_json = jsonify(dict)
if (len(dict) == 0):
return Response([])
else:
return _json
except Exception as err:
return Response("*** Error! *** ViewAuth->getCids Exception!! ",
err)
def update(self, params):
try:
cid = params.get('cid')
uid = params.get('uid')
pidm = params.get('pidm')
username = params.get('username')
password = params.get('password')
enabled = params.get('enabled')
query = {"pidm": pidm}
data = self.session.query(ModelAuth).filter_by(**query)
values = {
"username": username,
"password": password,
"enabled": enabled,
"uid": uid
}
data.update(values)
self.session.commit()
print("*** UPDATE successfully ***")
return '', 204
except Exception as err:
print("!!! AUTH UPDATE error !!! ", err)
return '', 404
def add(self, params):
try:
cid_data = params.get('cid_data')
uid = params.get('uid')
username = params.get('username')
password = params.get('password')
enabled = params.get('enabled')
data = {
"username": username,
"password": password,
"enabled": enabled,
"cid_data": cid_data,
"uid": uid
}
print('data: ', data)
# tek satır kayıt json datası oluştur {...}
record = []
record.append(data)
print('record:', record)
for row in record:
self.session.add(ModelAuth(**row))
self.session.commit()
print("*** ADD successfully ***")
return '', 204
except Exception as err:
print("!!! AUTH ADD error !!! ", err)
return '', 404
def delete(self, params):
try:
pidm = params.get('pidm')
query = {"pidm": pidm}
row = self.session.query(ModelAuth).filter_by(pidm=pidm).one()
self.session.delete(row)
self.session.commit()
print("*** AUTH Record DELETED successfully ***")
return '', 204
except Exception as err:
print("!!! ERROR ON DELETE !!! ", err)
return '', 404
class SSDokumanlar():
def __init__(self, modelClass):
self.conn = Connect()
self.session = self.conn.session()
self.model = modelClass
def __del__(self):
self.session.close()
def getSS(self, cid):
try:
dict = []
dictDetay = [] # Dokumanlar
sql = """
select pidm birim_pidm, name birim_name
from birimler
where cid=%d
""" % (cid)
data = self.session.execute(sql)
for row in data:
dictDetay = self.getSSDetay(row.birim_pidm, cid)
dict.append({
'birim_pidm': row.birim_pidm,
'birim_name': row.birim_name,
'dokumanlar': dictDetay
})
_json = jsonify(dict)
if (len(dict) == 0):
return Response([])
else:
return _json
except Exception as e:
return Response("DB SQL Exception! ", e)
def getSSDetay(self, birim_pidm, cid): #Dokumanlar
try:
dict = []
sql = """
select pidm,
dokuman_name,
yayin_name
from view_ssdokumanlar
where birim_pidm=%d and cid=%d
""" % (birim_pidm, cid)
data = self.session.execute(sql)
for row in data:
# dict.append({'pidm':row.pidm, 'birim':row.birim, 'kurum':row.kurum,'timestamp':row.timestamp})
dict.append({
'pidm': row.pidm,
'dokuman_name': row.dokuman_name,
'yayin_name': row.yayin_name
})
return dict
except Exception as e:
return Response("DB SQL Exception! ", e)
def add(self):
try:
self.session.add(self.model)
self.session.commit()
print("Add Successfully")
return '', 204
except Exception as e:
return Response("SSDokumanlar DB Add Exception! ", e)
def delete(self):
try:
_pidm = int(self.model.pidm)
_cid = int(self.model.cid)
row = self.session.query(self.model.__class__).filter_by(
pidm=_pidm, cid=_cid).one()
self.session.delete(row)
self.session.commit()
return '', 204
except Exception as err:
print("DB Error on deleting ", err)
return '', 404
class Tanimlar():
def __init__(self, modelClass):
self.conn = Connect()
self.session = self.conn.session()
# !!!modelClass() parentezle yaparsan class create edilir, bu koşulda olmaz...
self.model = modelClass
def __del__(self):
self.session.close()
def get(self, cid_):
try:
dict = []
if (self.model == Sistemler):
data = self.session.query(
self.model.pidm, self.model.name, self.model.local,
self.model.timestamp).filter_by(cid=cid_)
elif (self.model == Ulkeler):
data = self.session.query(
self.model.pidm, self.model.name, self.model.phone_area,
self.model.secure,
self.model.timestamp).filter_by(cid=cid_)
elif (self.model == YayinDurumlari
): # myComponent > CreateYayindurumlariOptions için
data = self.session.query(self.model.pidm, self.model.name,
self.model.timestamp)
else:
data = self.session.query(
self.model.pidm, self.model.name,
self.model.timestamp).filter_by(cid=cid_)
data = data.order_by(self.model.name)
for row in data:
if (self.model == Sistemler):
dict.append({
'pidm': row.pidm,
'name': row.name,
'local': row.local,
'timestamp': row.timestamp
})
elif (self.model == Ulkeler):
dict.append({
'pidm': row.pidm,
'name': row.name,
'phone_area': row.phone_area,
'secure': row.secure,
'timestamp': row.timestamp
})
else:
dict.append({
'pidm': row.pidm,
'name': row.name,
'timestamp': row.timestamp
})
# _json = jsonify({"Tanimlar":dict})
_json = jsonify(dict)
if (len(dict) == 0):
return Response(
[]
) #böyle [] yapmazsan react tarafında data.map funciton not found hatası alırsın!!
else:
return _json
except Exception as err:
return Response("sa query error! ", err)
def add(self):
self.session.add(self.model)
self.session.commit()
print("Add Successfully")
return '', 204
# Add delete için response gerekmediğinden: The HTTP 204: No Content success status response code indicates that the request has succeeded, but that the client doesn't need to go away from its current page. A 204 response is cacheable by default. An ETag header is included in such a response
def delete(self):
try:
_pidm = int(self.model.pidm)
_cid = int(self.model.cid)
row = self.session.query(self.model.__class__).filter_by(
pidm=_pidm, cid=_cid).one()
self.session.delete(row)
self.session.commit()
print(row.name + " deleted successfully")
return '', 204
except Exception as err:
print("DB Error on deleting ", err)
return '', 404
def __init__(self,modelClass):
self.conn = Connect()
self.session = self.conn.session()
self.model = modelClass
class ProjelerApi():
def __init__(self, yil, grup):
self.conn = Connect()
self.session = self.conn.session()
self.yil = yil
self.grup = grup
def __del__(self):
self.session.close()
def message(self):
try:
dict = {}
dictYil = {}
dictGrup = []
_yillar = self.session.query(
Projeler.yil).filter(Projeler.yil == self.yil).order_by(
Projeler.yil.desc()).group_by(Projeler.yil).all()
for _yil in _yillar:
_gruplar = self.session.query(Projeler.grup).filter(
Projeler.yil == _yil.yil,
Projeler.grup == self.grup).order_by(
Projeler.grup.desc()).group_by(Projeler.grup).all()
for _grup in _gruplar:
result = self.session.query(
Projeler.id, Projeler.yil, Projeler.grup,
Projeler.baslik, Projeler.aciklama, Projeler.sonuc,
Projeler.birim, Projeler.durum).filter(
Projeler.grup == _grup.grup).order_by(
Projeler.durum)
for row in result:
dictGrup.append({
'id': row.id,
'yil': row.yil,
'grup': row.grup,
'baslik': row.baslik,
'aciklama': row.aciklama,
'sonuc': row.sonuc,
'birim': row.birim,
'durum': row.durum
})
dictYil.update({
_grup.grup: dictGrup
}) #objede update, arrayde append fonksyion kullan...
dictGrup = []
dict.update({_yil.yil: dictYil})
dictYil = []
_json = jsonify({"projeler": dict})
if (len(dict) == 0):
return Response("No record found!")
else:
return _json
except Exception as e:
return Response("sa query error! ", e)
# if __name__ == "__main__":
# e = ProjelerApi()
# e.message()
# print("Base created successfully..")
