def getClearSysMsgSql(start, end):
dbIndex = None
if start == end:
dbIndex = start
dbs = getDbs(dbIndex)
sqls = ''
for d in dbs:
companyId = str(d[1])
index = str(d[3])
dbName = "curato" + index
if not Tool.dbExist(dbName):
continue
tblName = dbName + '.' + companyId + '_message'
sql = '''
DELETE FROM %s
WHERE sender = 0 ;
'''
sql = sql % tblName
sqls += sql
return sqls
def getNewSql(start, end):
dbIndex = None
if start == end:
dbIndex = start
dbs = getDbs(dbIndex)
sqls = ''
for d in dbs:
companyId = str(d[1])
index = str(d[3])
dbName = "curato" + index
if not Tool.dbExist(dbName):
continue
tblName = dbName + '.' + companyId + '_schedule'
# sql = '''
# INSERT INTO t_level (company_id,level_name,sort,is_deleted) values
# ('%s','总经理级别','1','0'),
# ('%s','副总经理级别','2','0'),
# ('%s','总监级别','3','0'),
# ('%s','经理级别','4','0'),
# ('%s','主管级别','5','0'),
# ('%s','员工级别','6','0');
# ''' % ((companyId,)*6)
sql = '''
ALTER TABLE %s
ADD COLUMN `week_day` varchar(255) NULL COMMENT '工作日，周一到周日，数字表示' AFTER `rest`;
''' % tblName
sqls += sql
return sqls
def getExtraSql(start, end):
dbIndex = None
if start == end:
dbIndex = start
dbs = getDbs(dbIndex)
sqls = ''
for d in dbs:
companyId = str(d[1])
index = str(d[3])
dbName = "curato" + index
if not Tool.dbExist(dbName):
continue
tblName = dbName + '.' + companyId + '_report_read_info'
sql = '''
ALTER TABLE %s
MODIFY COLUMN `id` int(11) NOT NULL AUTO_INCREMENT FIRST ;
'''
sql = sql % tblName
sqls += sql
return sqls
def getClearSql(start, end):
dbIndex = None
if start == end:
dbIndex = start
dbs = getDbs(dbIndex)
sqls = ''
for d in dbs:
companyId = str(d[1])
index = str(d[3])
dbName = "curato" + index
if not Tool.dbExist(dbName):
continue
tblName = dbName + '.' + companyId + '_apply_config'
sql = '''
DELETE FROM %s;
INSERT INTO %s (type,company,apply_name,process_type,status,create_time) VALUES
(1,1, '请假申请',1, 1,NOW()),
(2,1, '加班申请',1, 1,NOW()),
(3,1, '外勤申请',1, 1,NOW()),
(4,1, '补签申请',1, 1,NOW()),
(5,1, '通用申请',1, 1,NOW());
'''
sql = sql % (tblName, tblName)
# 申请，排班，考勤，公告，日报
# apply, apply_info; schedule, default_schedule; attendance, attendance_record; notice; report;
prefix = dbName + '.' + companyId + '_'
to_be_clear = [
'apply', 'apply_info', 'schedule', 'default_schedule',
'attendance', 'attendance_record', 'notice', 'report'
]
clears = []
for i in to_be_clear:
clears.append(prefix + i)
clears = tuple(clears)
sql += '''
DELETE FROM %s;
DELETE FROM %s;
DELETE FROM %s;
DELETE FROM %s;
DELETE FROM %s;
DELETE FROM %s;
DELETE FROM %s;
DELETE FROM %s;
''' % clears
sqls += sql
return sqls
def op_update_dump(start=None, end=None):
print 'Generating update ..'
update_dump_start = time.time()
db = connection
cursor = db.cursor()
# cursor.execute("select * from t_database where dbname < 3 ")
cursor.execute("use curato_base")
dbIndex = None
if start == end:
dbIndex = start
if dbIndex:
s = "select * from t_database where dbname = '" + dbIndex + "' "
elif start and end:
s = "select * from t_database where dbname >= %s and dbname <= %s " % (
start, end)
else:
all = raw_input('Dump all ? (y/N) ')
if all != 'y':
sys.exit()
s = "select * from t_database"
cursor.execute(s)
data = cursor.fetchall()
# log_file = dir + 'update.log'
# h = open(log_file, 'w')
sqls = ""
for d in data:
t1 = time.time()
sn = str(d[1]) # companyId
dbName = "curato" + d[3] # db index
if not Tool.dbExist(dbName):
continue
s = ""
create_sql = getCreateSql(dbName, sn)
print 'create_sql..' + dbName + '-' + sn
modify_sql = getModifySql(dbName, sn)
print 'modify_sql..' + dbName + '-' + sn
s += create_sql
s += modify_sql
sqls += s
t2 = time.time()
t = t2 - t1
print "Generating %s Cost Time %s" % (dbName + '-' + sn, t)
# h.write("Generating %s Cost Time %s" % (dbName + '-' + sn, t))
f = update_file
fo = open(f, "w")
fo.write(sqls)
fo.close()
update_dump_end = time.time()
print "Generating Cost Time %s" % (update_dump_end - update_dump_start)
print 'Generating update done. file: %s' % update_file
return 1
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
