def get_list_comment(cursor, params):
sel = "Contract"
if params.get(Comments.Type.value, None) == Comments.Profile.value:
sel = "Profile"
cursor.execute("select text, create_date from Comment where \
id_list_comment=(select id_list_comments from {} where id={})"
.format(sel, params[Profile.ID.value]))
row = cursor.fetchall()
status, obj = Object(), Object()
status.status = Status.Ok.value
status.message = EventComments.Success.value
obj.id_comments = {}
for i in row:
comm = Object()
comm.text = i[0]
comm.date = i[1]
#cursor.execute("select a.id, a.name, b.photo from Profile as a left join Photo as b on a.id_photo = b.id \
# where a.id_list_comments=(select id_list_comment from Comment where id={})".format(i[2]))
#prof = cursor.fetchone()
#comm.id_prof = prof[0]
#comm.name = prof[1]
#comm.photo = prof[2]
obj.id_comments[len(obj.id_comments)] = comm
status.object = obj
return status.toJSON()
def registration(cursor, params):
cursor.execute("select * from Authorization_info where login='******'".format(
params[User.Login.value]))
row = cursor.fetchone()
obj = Object()
status = Object()
if row is None:
status.status = Status.Ok.value
status.message = EventRegistration.SuccessRegistration.value
cursor.execute(
"insert into Authorization_info (login, password, phone_number) values('{}', '{}', '{}')"
.format(params[User.Login.value], params[User.Password.value],
params[User.Phone.value]))
cursor.execute("select max(id) from Authorization_info")
row = cursor.fetchone()
id_auth = row[0]
cursor.execute("insert into List_Comments (empty) values(null)")
cursor.execute("select max(id) from List_Comments")
row = cursor.fetchone()
id_lcomment = row[0]
cursor.execute(
"insert into Photo (id_list_photos, photo) values(null, '{}')".
format(defaultPhotoBase64))
cursor.execute("select max(id) from Photo")
id_photo = cursor.fetchone()[0]
cursor.execute(
"insert into Profile (id_authorization_info, id_list_comments, id_photo, name, about) \
values({}, {}, {}, null, null)".format(
id_auth, id_lcomment, id_photo))
cursor.execute("select max(id) from Profile;")
row = cursor.fetchone()
id_prof = row[0]
if params.get(User.IsWitcher.value, None) == 1:
cursor.execute(
"insert into Witcher (id_profile) values({})".format(id_prof))
obj.is_witcher = True
else:
cursor.execute(
"insert into Client (id_profile) values({})".format(id_prof))
obj.is_witcher = False
status.object = obj
else:
status.status = Status.Error.value
status.message = EventRegistration.LoginExist.value
return status.toJSON()
def create_advert(cursor, params):
cursor.execute(
"select * from Client where id_profile=(select id_profile from Token_Table where token='{}')"
.format(params[User.Token.value]))
row = cursor.fetchone()
obj = Object()
status = Object()
if row is not None:
id_client = row[0]
cursor.execute("insert into List_Comments (empty) values(null)")
cursor.execute("select max(id) from List_Comments")
id_lcomment = cursor.fetchone()[0]
cursor.execute("insert into List_Photos (empty) values(null)")
cursor.execute("select max(id) from List_Photos")
id_lphoto = cursor.fetchone()[0]
if params.get(Advert.Photo.value, None) is not None:
lst = params[Advert.Photo.value]
for photo in lst:
cursor.execute(
"insert into Photo (id_list_photos, photo) values({}, '{}')"
.format(id_lphoto, photo))
cur_time = int(time.time())
cursor.execute(
"insert into Contract (id_witcher, id_client, id_list_comments, id_task_located, \
id_list_photos, text, bounty, status, last_update_status, last_update, header) \
values(null, {}, {}, {}, {}, N'{}', {}, {}, {}, {}, N'{}')"
.format(id_client, id_lcomment, params[Advert.TaskLocated.value],
id_lphoto, params[Advert.Text.value],
params[Advert.Bounty.value], 0, cur_time, cur_time,
params[Advert.Header.value]))
status.status = Status.Ok.value
status.message = EventAdvert.Success.value
cursor.execute("select max(id) from Contract")
obj.id_advert = cursor.fetchone()[0]
else:
status.status = Status.Error.value
status.message = EventAdvert.WitcherCreator.value
status.object = obj
return status.toJSON()
def authorization(cursor, params):
cursor.execute("select * from Authorization_info where login='******'".format(params[User.Login.value]))
row = cursor.fetchone()
status = Object()
obj = Object()
status.status = Status.Error.value
if row is None:
status.message = EventAuth.LoginNotExist.value
elif row[2] == params[User.Password.value]:
id_auth = row[0]
obj.phone_number = '+' + row[3][1:]
status.message = EventAuth.SuccessAuthorizaion.value
status.status = Status.Ok.value
#tok = params[User.Login.value] + str(time.time())
#obj.token = md5(tok.encode('utf-8')).hexdigest()
cursor.execute("select id from Profile where id_authorization_info={}".format(id_auth))
row = cursor.fetchone()
obj.id_profile = row[0]
#cursor.execute("insert into Token_Table (token, last_update, id_profile) values('{}', {}, {})"
# .format(obj.token, int(time.time()), obj.id_profile))
# Вставка fcm_tokena
cursor.execute("select * from FCM_Token where id_profile = {}".format(obj.id_profile))
fcm = cursor.fetchone()
if fcm is not None:
cursor.execute("delete FCM_Token where id_profile = {}".format(obj.id_profile))
cursor.execute("insert into FCM_Token (id_profile, fcm_token) values( {}, '{}')"
.format(obj.id_profile, params[User.FCM_Token.value]))
cursor.execute("select * from Witcher where id_profile={}".format(obj.id_profile))
row = cursor.fetchone()
if row is not None:
obj.type = 0
else:
obj.type = 1
status.object = obj
else:
status.message = EventAuth.PasswordIncorrect.value
return status.toJSON()
def authorization(cursor, params):
cursor.execute("select * from Authorization_info where login='******'".format(params[User.Login.value]))
row = cursor.fetchone()
status = Object()
obj = Object()
status.status = Status.Error.value
if row is None:
obj.message = EventAuth.LoginNotExist.value
elif row[2] == params[User.Password.value]:
obj.message = EventAuth.SuccessAuthorizaion.value
status.status = Status.Ok.value
tok = params[User.Login.value] + time.time().__str__()
obj.token = md5(tok.encode('utf-8')).hexdigest()
cursor.execute("insert into Token_Table (token, last_update) values('{}', {})".format(obj.token, time.time().__int__()))
else:
obj.message = EventAuth.PasswordIncorrect.value
status.object = obj
return status.toJSON()
def get_profile_desired_contract(cursor, params):
cursor.execute(
"select d.id, d.name from Contract as a inner join Desired_Contract as b on a.id=b.id_contract \
inner join Witcher as c on b.id_witcher=c.id inner \
join Profile as d on c.id_profile=d.id where a.id={}".
format(params[Advert.ID.value]))
rows = cursor.fetchall()
status = Object()
obj = Object()
status.status = Status.Ok.value
status.message = EventAdvert.Success.value
obj.witchers = Object()
obj.witchers.witcher = {}
obj.witchers.count = len(rows)
for prof in rows:
profile = Object()
profile.id = prof[0]
profile.name = prof[1]
obj.witchers.witcher[len(obj.witchers.witcher)] = profile
status.object = obj
return status.toJSON()
def get_advert(cursor, params):
cursor.execute("select * from Contract where id={}".format(
params[Advert.ID.value]))
status = Object()
obj = Object()
row = cursor.fetchone()
obj.id = row[0]
obj.text = row[6]
obj.bounty = row[7]
obj.status = row[8]
obj.last_update_status = row[9]
obj.last_update = row[10]
obj.header = row[11]
id_witcher = row[1]
id_client = row[2]
id_list_comments = row[3]
id_task_located = row[4]
id_list_photos = row[5]
if id_witcher is not None:
cursor.execute(
"select id, name from Profile where id=(select id_profile from Witcher where id={})"
.format(id_witcher))
row = cursor.fetchone()
witcher = Object()
witcher.id = row[0]
witcher.name = row[1]
status.witcher = witcher
cursor.execute(
"select id, name, id_photo from Profile where id=(select id_profile from Client where id={})"
.format(id_client))
row = cursor.fetchone()
client = Object()
client.id = row[0]
client.name = row[1]
if row[2] is not None:
cursor.execute("select photo from Photo where id={}".format(row[2]))
client.photo = cursor.fetchone()[0]
status.client = client
cursor.execute(
"select a.name, b.name from Town as a inner join Kingdom as b on a.id_kingdom=b.id where a.id={}"
.format(id_task_located))
row = cursor.fetchone()
obj.town = row[0]
obj.kingdom = row[1]
cursor.execute(
"select b.photo from Contract as a inner join Photo as b on a.id_list_photos = b.id_list_photos where \
a.id_list_photos={}".format(id_list_photos))
row = cursor.fetchall()
obj.photoContract = Object()
obj.photoContract.photo = {}
obj.photoContract.count = len(row)
for i in range(len(row)):
ph = Object()
ph.photo = row[i][0]
obj.photoContract.photo[len(obj.photoContract.photo)] = ph
status.object = obj
status.status = Status.Ok.value
status.message = EventAdvert.Success.value
return status.toJSON()
def get_list_contracts(cursor, params):
req = "select * from Contract"
status = Object()
obj = Object()
status.status = Status.Ok.value
filtr = params.get(Params.Filter.Name)
if filtr is not None:
req += " where"
if filtr == Params.Filter.Bounty:
min = params.get(Params.Min)
max = params.get(Params.Max)
if min is None or max is None:
status.status = Status.Error.value
obj.value = "ERROR:{}{}".format(EventGetListContracts.MinParamMiss if min is None else "",
EventGetListContracts.MaxParamMiss if max is None else "").value
else:
req += " Bounty > {} and Bounty < {}".format(min, max)
elif filtr == Params.Filter.Locate:
town = params.get(Params.Town)
kingdom = params.get(Params.Kingdom)
if town is None and kingdom is None:
status.status = Status.Error.value
obj.value = EventGetListContracts.FilterLocateErr.value
else:
req += " Contract.id_task_located in (select id from Town where"
if town is not None:
req += " Town.name = '{}'".format(town)
if kingdom is not None:
req += " and"
else:
req += ')'
if kingdom is not None:
req += " Town.id_kingdom in (select id from Kingdom where Kingdom.name = '{}'))".format(kingdom)
sort = params.get(Params.Sort.Name)
if sort is not None:
req += " order by"
if sort == Params.Sort.Alph:
req += " text"
elif sort == Params.Sort.Locate:
req += " id_task_located"
elif sort == Params.Sort.LastUpdate:
req += " last_update"
else:
status.status = Status.Error.value
obj.value = EventGetListContracts.SortErr.value
sort_type = params.get(Params.SortType.Name)
if sort_type is not None and sort_type == Params.SortType.Desc:
req += " desc"
if status.status == Status.Ok.value:
cursor.execute(req)
contracts = cursor.fetchall()
cursor.execute("select name from sys.columns where object_id = OBJECT_ID('dbo.Contract')")
headers = cursor.fetchall()
obj.contracts = {}
for i in range(len(headers)):
head = str(headers[i])[2:-4]
obj.contracts[head] = []
for j in range(len(contracts)):
obj.contracts[head].append(contracts[j][i])
status.object = obj
return status.toJSON()
def list_contract(cursor, params, **kwargs):
obj = Object()
status = Object()
status.status = Status.Error.value
obj.message = "Неопознанная ошибка в методу Get List Contracts"
req = 'select * from Contract'
req += " inner join \
(select town, kingdom, idT from ((select [Town].name as town, [Town].id_kingdom, \
[Town].id as idT from [Town] ) as T\
inner join \
(select [Kingdom].name as kingdom, [Kingdom].id as idK From [Kingdom]) as K on T.id_kingdom = K.idK) )\
as TK on id_task_located = TK.idT"
if kwargs.get('id_witcher') is not None:
req += ' inner join Desired_Contract on Desired_Contract.id_contract = Contract.id \
where Desired_Contract.id_witcher={}'.format(
kwargs.get('id_witcher'))
elif kwargs.get('id_client') is not None:
req += ' where id_client={}'.format(kwargs.get('id_client'))
filtr = params.get(Params.Filter.Name)
if filtr is not None:
req += " and "
if filtr == Params.Filter.Bounty:
min = params.get(Params.Min)
max = params.get(Params.Max)
if min is None or max is None:
status.status = Status.Error.value
obj.value = "ERROR:{}{}".format(
EventGetListContracts.MinParamMiss if min is None else "",
EventGetListContracts.MaxParamMiss if max is None else "")
else:
req += " Bounty > {} and Bounty < {}".format(min, max)
elif filtr == Params.Filter.Locate:
town = params.get(Params.Town)
kingdom = params.get(Params.Kingdom)
if town is None and kingdom is None:
status.status = Status.Error.value
obj.value = EventGetListContracts.FilterLocateErr.value
else:
if town is not None:
req += " town = '{}' {} ".format(
town, "and " if kingdom is not None else "")
if kingdom is not None:
req += " kingdom = '{}' ".format(kingdom)
stat = params.get(Params.Status)
if stat is not None and (kwargs.get('id_witcher') is not None
or kwargs.get('id_client') is not None):
req += " and status={}".format(stat)
sort = params.get(Params.Sort.Name)
if sort is not None:
sort_type_desc = params.get(Params.SortType.Name) is not None \
and params.get(Params.SortType.Name) == Params.SortType.Desc
req += " order by"
if sort == Params.Sort.Alph:
req += " header"
elif sort == Params.Sort.Locate:
req += " kingdom {}, town".format("desc" if sort_type_desc else "")
elif sort == Params.Sort.LastUpdate:
req += " last_update"
else:
status.status = Status.Error.value
obj.value = EventGetListContracts.SortErr.value
if sort_type_desc:
req += " desc"
cursor.execute(req)
row = cursor.fetchall()
headers = tuple(cursor.description)
obj.contracts = {}
for N, i in enumerate(row):
line = Object()
for n, head in enumerate(headers):
setattr(line, head[0], i[n])
obj.contracts[N] = line
status.object = obj
status.status = Status.Ok.value
obj.message = EventGetListContracts.SuccessGetListContracts.value
return status.toJSON()
def get_profile(cursor, params):
status = Object()
obj = Object()
status.status = Status.Error.value
id_profile = params.get(Profile.ID.value, None)
if id_profile is None:
cursor.execute(
"select id_profile from Token_Table where token='{}'".format(
params[User.Token.value]))
row = cursor.fetchone()
if row is None:
return status.toJSON()
else:
id_profile = row[0]
cursor.execute(
"select a.id, a.name, a.about, b.photo from Profile as a left \
join Photo as b on a.id_photo = b.id where a.id={}".format(
id_profile))
row = cursor.fetchone()
if row is not None:
status.status = Status.Ok.value
obj.id = row[0]
obj.name = row[1]
obj.about = row[2]
obj.photo = row[3]
cursor.execute("select id from Witcher where id_profile={}".format(
obj.id))
row = cursor.fetchone()
if row is not None:
obj.type = Profile.Witcher.value
cursor.execute(
"select c.id, c.id_witcher, c.id_client, c.header, c.status, c.last_update, \
c.last_update_status from Profile as a inner join Witcher as b on a.id=b.id_profile \
inner join Contract as c on c.id_witcher=b.id where b.id={}"
.format(row[0]))
row = cursor.fetchall()
obj.history = Object()
obj.history.count = len(row)
obj.history.contract = {}
for line in row:
hist = Object()
hist.id_contract = line[0]
hist.id_witcher = line[1]
hist.id_client = line[2]
hist.header = line[3]
hist.status = line[4]
hist.last_update = line[5]
hist.last_update_status = line[6]
obj.history.contract[len(obj.history.contract)] = hist
else:
obj.type = Profile.Client.value
cursor.execute("select id from Client where id_profile={}".format(
obj.id))
row = cursor.fetchone()
cursor.execute(
"select c.id, c.id_witcher, c.id_client, c.header, c.status, c.last_update, c.last_update_status \
from Profile as a inner join Client as b on a.id=b.id_profile \
inner join Contract as c on c.id_witcher=b.id where b.id={}".
format(row[0]))
row = cursor.fetchall()
obj.history = Object()
obj.history.count = len(row)
obj.history.contract = {}
for line in row:
hist = Object()
hist.id_contract = line[0]
hist.id_witcher = line[1]
hist.id_client = line[2]
hist.header = line[3]
hist.status = line[4]
hist.last_update = line[5]
hist.last_update_status = line[6]
obj.history.contract[len(obj.history.contract)] = hist
status.object = obj
return status.toJSON()