def userIdToName(user_id):
q1 = 'SELECT name FROM user where id = %s'
c = readcon(q1, [user_id])
row = c.fetchone()
if row is not None:
return row[0]
return 0
def userNameToId(username):
q1 = 'SELECT id FROM user where name = %s'
c = readcon(q1, [username])
row = c.fetchone()
if row is not None:
return row[0]
return 0
def prIdToName( pr_id ): q1 = 'SELECT name FROM product where id = %s' c = readcon( q1, [ pr_id ] ) row = c.fetchone() if row is not None: return row[0] return 0
def prNameToId( pr1 ): q1 = 'SELECT id FROM product where name = %s' c = readcon( q1, [ pr1 ] ) row = c.fetchone() if row is not None: return row[0] return 0
def checkPassword(username, password1):
"this returns true of the password and user are good, false otherwise"
try:
pw1Bytes = password1.encode('utf-8')
# c, conn = connection()
# c.execute("SELECT hashword, closeDate, id FROM user2 WHERE loginName = %s ", (escape_string(username)) )
q1 = 'SELECT hashword, closeDate, id FROM user WHERE name = %s '
args = [username]
c = readcon(q1, args)
fo = c.fetchone()
if fo == None:
return [False, 0]
closedate = fo[1]
if closedate != None:
return [False, 0]
hwBytes = fo[0].encode('utf-8')
if bcrypt.hashpw(pw1Bytes, hwBytes) == hwBytes:
return [True, fo[2]]
return [False, 0]
except Exception as e:
print('oo 91 ' + (str(e)))
return [False, 0]
return [False, 0]
def addUser(username, password1, password2):
"add a new user"
if password1 != password2:
return ['logic ok', 'passwords do not match']
try:
# c, conn = connection()
# c.execute( 'select id from user2 where loginName = "' + username + '"' )
q1 = 'select id from user where name = %s '
args = [username]
c = readcon(q1, args)
row = c.fetchone()
if row != None:
return ['logic ok', 'user name is taken']
pw1Bytes = password1.encode('utf-8')
newHash = bcrypt.hashpw(pw1Bytes, bcrypt.gensalt())
dateNow = strftime("%Y-%m-%d %H:%M:%S")
q1 = 'INSERT INTO user ( name, hashword, createDate ) VALUES ( %s, %s, %s )'
args = [username, newHash, dateNow]
a2q(q1, args)
return ['logic ok', 'user added']
except Exception as e:
return 'oo 115 :' + (str(e))
def getPrCrId( idPr ):
print('zxc')
# return '10'
q1 = 'SELECT user_id FROM product where id = %s'
c = readcon( q1, [ idPr ] )
row = c.fetchone()
if row is not None:
return row[0]
return 0
def productInfo( productName ):
q1 = 'select detail, datetime from product where name = (%s) and status1 = "okay" '
args = [ productName ]
try:
c = readcon( q1, args )
row = c.fetchone()
if row is not None:
cr1pr1 = splitCr1Pr1( productName )
myList2 = { 'message':'okay', 'cr1':cr1pr1[0], 'pr1':cr1pr1[1], 'description':str(row[0]), 'dateTime':str(row[1]) }
return myList2
return { 'message':'not found' }
except Exception as e:
print( 'oo 64; ' + (str(e)) )
return { 'message':'error' }
def productExistsName( prName ): print ( 'pr exists name', prName ) try: q1 = "SELECT status1 FROM product WHERE name = %s " c = readcon( q1, [ prName ] ) fo = c.fetchone() if fo == None: return 'no product' if fo[0] == 'okay': return 'product is okay' return 'product is ' + str( fo[0] ) except Exception as e: print ( 'oo' + (str(e)) ) return 'error'
def getBalance( user_id, product_id, prCrId = None ):
print ( 'getBalance ', user_id, product_id, prCrId )
available = 0
inuse = 0
total = 0
inTrade = 0
if prCrId == None:
prCrId = getPrCrId( product_id )
exists = productExists( product_id )
if exists != 'product is okay':
print ( exists )
return { 'message':exists }
myList = {}
try:
q1 = 'select amount from score where user_id = %s and product_id = %s '
args = [ user_id, product_id ]
c = readcon( q1, args )
row = c.fetchone()
if row is not None:
amount = row[0]
if int( prCrId ) == int( user_id ):
available = maxLimit - amount
inuse = amount
else:
available = amount
else:
if int( prCrId ) == int( user_id ):
available = maxLimit
inuse = 0
myList[ 'message' ] = 'okay'
myList[ 'available' ] = available
myList[ 'in use' ] = inuse
except Exception as e:
print( 'oo 114; ' + (str(e)) )
return 'error'
return myList
def getLocation(username):
'returns current location'
q1 = '''
select userLocation.addStr from userLocation
join user on userLocation.user_id = user.id
where name = %s and userLocation.status1 = 'active'
'''
args = [username]
c = readcon(q1, args)
row = c.fetchone()
if row == None:
return ''
if row != None:
return row[0]
def userCreateDate(username):
try:
# c, conn = connection()
## c.execute("SELECT createDate FROM user2 WHERE loginName = %s ", username )
q1 = 'SELECT createDate FROM user WHERE name = %s '
args = [username]
c = readcon(q1, args)
rowa = c.fetchone()
if rowa == None:
return 'no user'
return rowa[0]
except Exception as e:
print('oo' + (str(e)))
return False
def userExists_id(user_id):
try:
q1 = 'SELECT closeDate FROM user WHERE id = %s '
args = [user_id]
c = readcon(q1, args)
rowa = c.fetchone()
if rowa == None:
return 'no user'
closedate = rowa[0]
if closedate != None:
return "user closed!"
return True
except Exception as e:
return 'oo' + (str(e))
return False
def updateScoresRow( newVar, userId, idPr ): if newVar == 0: # sales inTrade maybe ok q1 = 'delete from score where user_id = %s and product_id = %s' args = [ userId, idPr ] else: # newVarAvail > 0 q2 = 'select id from score where user_id = %s and product_id = %s' args2 = [ userId, idPr ] try: c = readcon( q2, args2 ) row = c.fetchone() if row is not None: q1 = 'update score set amount = %s where user_id = %s and product_id = %s' args = [ newVar, userId, idPr ] else: q1 = 'insert into score ( user_id, product_id, amount ) values ( %s, %s, %s )' args = [ userId, idPr, newVar ] except Exception as e: print( 'oo 223 ; ' + (str(e)) ) return 'error' a2q( q1, args )
def listUsers():
'return a list of users'
try:
# c, conn = connection()
# c.execute( 'SELECT loginName FROM users1', [] )
# c.execute( 'SELECT loginName FROM user2' )
q1 = 'SELECT name FROM user'
c = readcon(q1, [])
myRowCount = c.rowcount
myList = []
row = c.fetchone()
while row is not None:
myList.append(row[0])
row = c.fetchone()
return {'count': myRowCount, 'userList': myList}
except Exception as e:
print('oo ' + (str(e)))
return -1
def getProductsOtherList( user_id ): myList = [] q1 = 'select product_id from score where user_id = %s ' args = [ user_id ] try: c = readcon( q1, args ) row = c.fetchone() while row is not None: crpr = getPrCrId( row[0] ) # crpr = splitCr1Pr1( row[0] ) if int( crpr ) != int( user_id ): if int( crpr ) != 0: myList.append( row[0] ) row = c.fetchone() myList.append( row[0] ) row = c.fetchone() except Exception as e: print ( 'oo 65 ' + (str(e)) ) return 'error' return myList
def userExists(username):
try:
# c, conn = connection()
# c.execute("SELECT closeDate FROM user2 WHERE loginName = %s ", username )
q1 = 'SELECT closeDate FROM user WHERE name = %s '
args = [username]
c = readcon(q1, args)
rowa = c.fetchone()
if rowa == None:
return 'no user'
closedate = rowa[0]
if closedate != None:
return "user closed!"
return True
except Exception as e:
return 'oo' + (str(e))
return False
def getSendRecLog( startfrom, results, user1, user2, productList ):
print ( 'getSendRecLog', startfrom, results, user1, user2, productList )
q1 = '''
SELECT u1.name as userFrom, u2.name as userTo, product.name,
amount, sendSort, sendRecLog.dateTime
FROM sendRecLog
INNER JOIN user u1 ON user1_id = u1.id
INNER JOIN user u2 ON user2_id = u2.id
INNER JOIN product ON product_id = product.id
'''
q2 = '''
SELECT sendRecLog.id
FROM sendRecLog
INNER JOIN user u1 ON user1_id = u1.id
INNER JOIN user u2 ON user2_id = u2.id
INNER JOIN product ON product_id = product.id
'''
print ( productList )
argExts = []
args = []
if user1 != '':
argExts.append( ' ( u1.name = %s or u2.name = %s ) ' )
args.append( user1 )
args.append( user1 )
extStr = ' where '
count = 0
for x in argExts:
extStr += ' ' + x
count = count + 1
if count < len( argExts ):
extStr += ' and '
if count == len( argExts ):
print ( 'azaza', q1, extStr )
q1 += extStr
q2 += extStr
print ( 'exstr', extStr )
print ( 'q1 ', q1 )
print ( 'args ', args )
args2 = list(args)
q1 += ' order by sendRecLog.dateTime desc, sendRecLog.id desc'
q1 += ' limit %s, %s '
args.extend( [ int( startfrom ), int(results) ] )
try:
c = readcon( q2, args2 )
myRowCount2 = c.rowcount
print( 'myRowCount2', myRowCount2 )
myList = []
c = readcon( q1, args )
# myRowCount = c.rowcount
row = c.fetchone()
while row is not None:
row2 = {}
sentRecvd = 'received from'
if user1 == row[0]:
sentRecvd = 'sent to'
userToVar = row[1]
if row[4] != 'ordinary':
userToVar = row[4]
row2['user' ] = row[0]
row2['userTo' ] = userToVar
row2['product' ] = row[2]
row2['amount' ] = row[3]
row2['sentRecvd'] = sentRecvd
row2['sendSort'] = row[4]
row2['dateTime'] = str( row[5] )
myList.append( row2 )
row = c.fetchone()
return { 'rows':myList, 'allRows':myRowCount2, 'startfrom':startfrom, 'results':results, 'userTo':user2, 'products':productList }
except Exception as e:
print( 'oo 223 ; ' + (str(e)) )
return { 'qqqq': 'error'}