def getTotalIdsString(user_id: int,
devid: int,
cross: str = '',
extendType: int = 0) -> dict:
sql_tasks_permission_string = db.buildSqlPermissionfilter(user_id=user_id,
devid=devid,
cache=False)
cross = utils.clearGlobalIds(cross)
add_fields = '' # when extendType==0
add_condition = ''
state_filter = ' t.state=20 '
if extendType == 1: # get exact tasks from database
state_filter = ' true '
add_fields = " GROUP_CONCAT(tgs.name SEPARATOR ',') as tags,t.*,"
add_condition = '''
left join tasks_tags as tt on tt.taskid=t.id
left join tags as tgs on tt.tagid=tgs.id
'''
if len(cross) > 0:
cross = ' and t.globalid in (' + ("'" + "','".join(cross.split(',')) +
"'") + ') '
# building sql request
sql = '''
select ''' + add_fields + ''' t.globalid as fval, t.update_time as ftime, t.`serial` as fserial from tasks as t
''' + add_condition + '''
where ''' + state_filter + cross + ''' and
(
''' + sql_tasks_permission_string + '''
)
group by t.id
order by t.update_time,t.`serial`
'''
result = {'info': {}, 'time': 0, 'serial': 0, 'count': 0, 'db': []}
try:
mydb.execute(sql)
except Exception as ex:
utils.log(utils.clearUserLogin(str(ex)), 'error', 'mobile')
return None
rows = mydb.fetchall()
ids_arr = []
ser_arr = []
upd_arr = []
count = 0
max_time = 0
serial = 0
for row in rows:
tserial = int(row['fserial'])
if row['ftime'] is None:
row['ftime'] = 0
tupdate = int(row['ftime'])
if (extendType == 0) or (extendType == 2):
ids_arr.append(row['fval'])
if extendType == 2:
ser_arr.append(str(tserial))
upd_arr.append(str(tupdate))
count = count + 1
serial = serial + tserial
if tupdate > max_time:
max_time = tupdate
if extendType == 1:
row.pop('fval', None)
row.pop('ftime', None)
row.pop('fserial', None)
if row['tags'] is None or (len(row['tags']) < 1):
row.pop('tags', None)
result['db'].append(row)
if (extendType == 0) or (extendType == 2):
result['info']['ids'] = ','.join(ids_arr)
if extendType == 2:
result['info']['serials'] = ','.join(ser_arr)
result['info']['updates'] = ','.join(upd_arr)
result['time'] = max_time
result['count'] = count
result['serial'] = serial
return result
def log(message: str, tag: str = ' info'):
utils.log(message, tag, 'mobile')
def elog(message: str, tag: str = 'error'):
utils.log(message, tag, 'mobile')
os.path.abspath(inspect.getfile(inspect.currentframe())))
sys.path.insert(0, os.path.dirname(os.path.dirname(currentdir)))
from _common.api._settings import mydb
from _common.api import auth
from _common.api import headers
from _common.api import utils
from _common.api import translation
headers.jsonAPI()
sql = 'delete from tasks where devid in (select id from devices where uid=' + str(
auth.user_id) + ')'
try:
mydb.execute(sql)
except Exception as ex:
utils.log(utils.clearUserLogin(str(ex)), 'error', 'mobile')
headers.errorResponse('Can not remove tasks')
sql = 'delete from sync_devices where src in (select id from devices where uid=' + str(
auth.user_id) + ')'
try:
mydb.execute(sql)
except Exception as ex:
utils.log(utils.clearUserLogin(str(ex)), 'error', 'mobile')
headers.errorResponse('Can not remove source sync devices')
sql = 'delete from sync_devices where dst in (select id from devices where uid=' + str(
auth.user_id) + ')'
try:
mydb.execute(sql)
except Exception as ex:
if usr['fail_login_counter'] is None:
usr['fail_login_counter'] = 0
timestamp_int = int(time.time() * 1000)
if (abs(timestamp_int - int(usr['fail_login_timestamp'])) < 60 * 1000) and (int(usr['fail_login_counter']) > 5):
auth.credentials = auth.buildCredentials(0, '', '', 0, 0)
headers.jsonAPI(False)
time.sleep(1)
headers.errorResponse('@str.wait_1_min', '@str.attention', 403)
timestamp_string = str(timestamp_int)
if usr['password'] != jsonpost['password'] or int(usr['state']) < 1:
mydb.execute(
'update users set fail_login_counter=(fail_login_counter+1),fail_login_timestamp=' + timestamp_string + ' where id=' + str(
usr['id']))
wrongCred() # auth fail
auth.user_id = int(usr['id']) # before! buildCredentials call
if auth.isMobile:
badExit() # using this form from mobile app APIs is not permitted
else:
mydb.execute('update users set fail_login_counter=0,fail_login_timestamp=0,lastlogin='******' where id=' + str(auth.user_id))
auth.credentials = auth.buildCredentials(
auth.user_id, usr['login'], usr['password'], jsonpost['remember'], auth.user_some_state)
headers.jsonAPI(False) # New cookie always there
utils.log(usr['login'] + ' Logged in', 'auth')
headers.goodResponse({'accepted': True})
def sql_request(sql: str):
try:
mydb.execute(sql)
except Exception as ex:
utils.log(utils.clearUserLogin(str(ex)), 'error')
headers.errorResponse('SQL error')
def saveTask(data: dict) -> int:
# do all necessary checks and convert types
data = utils.replace_keys(data, tasks_keymap)
required = {'devid', 'title', 'desc', 'type'}
if not (required.issubset(data.keys())):
return -1
# Convert all values only to Integers and Strings.
# Other primitive types except float - it's a big lying
int_fields = {
'id', 'devid', 'type', 'alarm_type', 'state', 'priority', 'ordr',
'start_time', 'done_time', 'duration_time', 'repeat_type',
'repeat_value', 'defered_interval', 'year', 'month', 'day', 'hour',
'minute', 'timezone', 'utc_flag', 'serial'
}
for key in data:
value = data[key]
if (key in int_fields):
if value is None:
data[key] = 0
else:
if not (isinstance(value, int)):
try:
data[key] = int(value)
except Exception:
return -2
else:
if not (isinstance(value, str)):
if value is None:
data[key] = ''
else:
try:
data[key] = str(value)
except Exception:
return -3
data['title'] = data['title'][:350]
if data['devid'] < 1:
return -4
if data['type'] == 0: # timer
required = {
'alarm_type', 'start_time', 'repeat_type', 'repeat_value',
'defered_interval', 'year', 'month', 'day', 'hour', 'minute',
'timezone', 'utc_flag'
}
if not (required.issubset(data.keys())):
return -5
elif data['type'] == 1: # for the whole day
required = {
'start_time', 'repeat_type', 'repeat_value', 'year', 'month',
'day', 'timezone'
}
if not (required.issubset(data.keys())):
return -6
elif data['type'] == 2: # notes
required = {'state', 'priority'}
if not (required.issubset(data.keys())):
return -7
elif data['type'] == 3: # geo based reminders
required = {'start_time', 'repeat_type', 'repeat_value', 'locations'}
if not (required.issubset(data.keys())):
return -8
else:
return -9 # not supported task type
timestamplong = int(time.time() * 1000)
timestampstr = str(int(timestamplong))
gid_generator = str(int(timestamplong) - 1592000000000)
if ('id' not in data) or (data['id'] is
None) or (data['id'] < 1): # new record in tasks
data['id'] = 0
if ('globalid' not in data) or (data['globalid'] is None) or len(
data['globalid']) < 5:
data['globalid'] = ''
if (data['id'] == 0) and len(data['globalid']) == 0: # 1-1
data['globalid'] = gid_generator + utils.rand_string(6) +\
str(data['type'] + str(data['devid']))
elif (data['id'] != 0) and len(data['globalid']) == 0: # 0-1
data['globalid'] = getGlobalFromId(data['id'])
if len(data['globalid']) == 0:
data['globalid'] = gid_generator + utils.rand_string(6) +\
str(data['type'] + str(data['devid']))
elif (data['id'] == 0) and len(data['globalid']) != 0: # 1-0
data['id'] = getIdFromGlobal(data['globalid'])
elif (data['id'] != 0) and len(data['globalid']) != 0: # 0-0
pass # may be check that globalid is correct with id
else:
return -100 # not possible
if (data['id'] == 0) and (('created' not in data) or
(data['created'] is None) or
(int(data['created']) < 10)):
data['created'] = timestampstr # dont change this later never!
# internal update time field
data['srv_update_time'] = timestampstr
# always update time after any changes
if ('update_time' not in data) or (data['update_time'] is None):
data['update_time'] = timestampstr
# always change serial after any updates ;-)
if ('serial' not in data) or (data['serial'] is None):
data['serial'] = random.randint(1, 50000)
tags = data.pop('tags', None)
temp_global_id = data['globalid'] # store value before unset
temp_dev_id = data['devid']
data['update_devid'] = data['devid']
if (data['id'] > 0): # dont change this values!
data.pop('created', None) # dont change this values!
data.pop('globalid', None) # dont change this values!
data.pop('devid', None) # dont change this values!
if ('locations' in data) and not (data['locations'] is None):
data['locations'] = str(data['locations'])[:2048]
sql = ''
if (data['id'] > 0):
sql = 'update tasks set ' +\
__build_update(data) + ' where id=' + str(data['id'])
data['globalid'] = temp_global_id
data['devid'] = temp_dev_id
try:
mydb.execute(sql)
except Exception as ex:
utils.log(utils.clearUserLogin(str(ex)), 'error')
return -11
else:
sql = 'insert into tasks ' + __build_insert(data)
data['globalid'] = temp_global_id
data['devid'] = temp_dev_id
try:
mydb.execute(sql)
except Exception as ex:
utils.log(utils.clearUserLogin(str(ex)), 'error')
return -12
data['id'] = mydb_connection.insert_id()
tags_db_ids = []
tags_db_ids.append('0')
if not (tags is None):
tags_arr = str(tags).split(',')
if len(tags_arr) > 0:
for tag in tags_arr:
if (tag is not None) and (len(tag) > 0):
tags_db_ids.append(str(setTaskTag(data['id'], tag)))
sql = 'delete from tasks_tags where taskid=' + str(
data['id']) + ' and tagid not in (' + ','.join(tags_db_ids) + ')'
try:
mydb.execute(sql)
except Exception:
pass
return data['id']