def getAuthSql(start, end):
dbIndex = None
if start == end:
dbIndex = start
data = getDbs(dbIndex)
db = connection
cursor = db.cursor()
sqlss = ''
# per row for company
for d in data:
companyId = str(d[1])
dbIndex = str(d[3])
dbName = "curato" + dbIndex
if not Tool.dbExist(dbName):
continue
sqls = "use %s;" % dbName
tblName = companyId + "_staff_role_auth"
createTbl = '''
drop table if EXISTS `%s`;
CREATE TABLE `%s` (
`id` int(5) UNSIGNED NOT NULL AUTO_INCREMENT ,
`role_id` int(5) NULL ,
`auth` text NULL ,
PRIMARY KEY (`id`)
)
;
'''
createTbl = createTbl % (tblName, tblName)
sqls += createTbl
oldTbl = companyId + "_user_role_auth"
if Tool.tblExist(dbName, oldTbl):
authSql = "select * from %s " % oldTbl
cursor.execute("use %s" % dbName)
cursor.execute(authSql)
res = cursor.fetchall()
insertSql = "insert into %s (role_id, auth) values " % tblName
insertCount = 0
for a in res:
authStr = a[4]
roleId = a[1]
authStr = authStr.strip(',')
auth = convertAuth(authStr)
if len(auth) > 0:
auth = ','.join(str(n) for n in auth)
else:
auth = ''
insertCount += 1
insertSql += " ('%s', '%s')," % (roleId, auth)
if insertCount:
insertSql = insertSql.rstrip(',')
insertSql += ';'
else:
insertSql = ''
sqls += insertSql
sqlss += sqls
return sqlss
