def getModifySql(dbName, sn):
sn = str(sn)
# attendance
tblName = sn + "_attendance"
sql = ""
alter = " alter table `" + tblName + "` "
addCols = [
('rule_id',
"ADD COLUMN `rule_id` int NULL COMMENT '考勤规则id' AFTER `user_id`"),
('apply_id',
"ADD COLUMN `apply_id` varchar(200) DEFAULT NULL COMMENT '对考勤有影响的申请id集合' AFTER `cross_day`"
),
('rule_work_time',
"ADD COLUMN `rule_work_time` smallint(6) NULL COMMENT '自由班工作时间' AFTER `apply_id`"
),
('record_limit_time',
"ADD COLUMN `record_limit_time` time NULL COMMENT '自由班最晚打卡时间' AFTER `rule_work_time`"
),
('s_record_time',
"ADD COLUMN `s_record_time` smallint(6) NULL COMMENT '上班前有效打卡时间' AFTER `record_limit_time`"
),
('late_least_time',
"ADD COLUMN `late_least_time` smallint(6) NULL COMMENT '迟到起算时间' AFTER `s_record_time`"
),
('late_max_time',
"ADD COLUMN `late_max_time` smallint(6) NULL COMMENT '迟到上限时间（超过即旷工）' AFTER `late_least_time`"
),
('leave_least_time',
"ADD COLUMN `leave_least_time` smallint(6) NULL COMMENT '早退起算时间' AFTER `late_max_time`"
),
('leave_max_time',
"ADD COLUMN `leave_max_time` smallint(6) NULL COMMENT '早退上限时间（超过即旷工）' AFTER `leave_least_time`"
),
('ot_least_time',
"ADD COLUMN `ot_least_time` smallint(6) NULL COMMENT '加班起算时间' AFTER `leave_max_time`"
),
('attendance_apply',
"ADD COLUMN `attendance_apply` varchar(255) DEFAULT '1,2,3,4' COMMENT '关联申请' AFTER `ot_least_time`"
),
('record_way',
"ADD COLUMN `record_way` varchar(255) DEFAULT '1,2,3' COMMENT '允许打卡方式' AFTER `attendance_apply`"
),
('rule_type',
"ADD COLUMN `rule_type` tinyint(4) DEFAULT 1 COMMENT '班制类型。1固定2多班3自由' AFTER `record_way`"
),
]
for tup in addCols:
if not Tool.colExist(dbName, tblName, tup[0]):
sql += alter + tup[1] + "; "
modify = '''
%s MODIFY COLUMN `schedule_id` int(11) NULL COMMENT '班次id' AFTER `rule_id`;
%s MODIFY COLUMN `type` int(11) NULL DEFAULT 0 COMMENT '考勤类型（加班、出差）' AFTER `attendance_status`;
%s MODIFY COLUMN `work_time` smallint(6) NULL COMMENT '应上班的分钟数' AFTER `update_time`;
%s MODIFY COLUMN `cross_day` tinyint(1) NULL DEFAULT 0 COMMENT '是否跨天' AFTER `overtime_type`;
'''
sql += (modify % ((alter, ) * 4))
# schedule
tblName = sn + "_schedule"
alter = " alter table `" + tblName + "` "
addCols = [
('rest',
"ADD COLUMN `rest` tinyint(1) NULL DEFAULT 1 COMMENT '是否有休息时间，1是0否' AFTER `cross_day`"
),
]
for tup in addCols:
if not Tool.colExist(dbName, tblName, tup[0]):
sql += alter + tup[1] + '; '
modify = '''
%s MODIFY COLUMN `department_id` int(11) NULL COMMENT '部门id' AFTER `id`;
%s MODIFY COLUMN `schedule_name` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL COMMENT '班次名称' AFTER `department_id`;
%s MODIFY COLUMN `s_rest_time` time NULL COMMENT '午休开始时间' AFTER `e_time`;
%s MODIFY COLUMN `e_rest_time` time NULL COMMENT '午休结束时间' AFTER `s_rest_time`;
%s MODIFY COLUMN `creator` int(11) NULL COMMENT '创建者id' AFTER `e_rest_time`;
%s MODIFY COLUMN `create_time` datetime NULL COMMENT '创建时间' AFTER `creator`;
%s MODIFY COLUMN `work_time` smallint(6) NULL DEFAULT 0 COMMENT '上班时间（分钟数）' AFTER `deleted`;
%s MODIFY COLUMN `cross_day` tinyint(1) NULL DEFAULT 0 COMMENT '是否跨天' AFTER `work_time`;
'''
sql += (modify % ((alter, ) * 8))
# attendance_record
tblName = sn + "_attendance_record"
if (not Tool.colExist(dbName, tblName, 'record_type')):
s = " ALTER TABLE `%s` ADD COLUMN `record_type` tinyint(1) NULL DEFAULT 1 COMMENT '打卡方式。1人脸2二维码3外勤签到4补签' AFTER `time`;"
sql += (s % tblName)
# report
tblName = sn + "_report"
alter = " alter table " + tblName + " "
addCols = [
('report_type',
"ADD COLUMN `report_type` tinyint(4) NULL COMMENT '汇报类型。1日报2周报' AFTER `type`"
),
('work_plan',
"ADD COLUMN `work_plan` varchar(5000) NULL COMMENT '下周工作计划' AFTER `content`"
),
('files',
"ADD COLUMN `files` varchar(255) NULL COMMENT '图片' AFTER `work_plan`"
),
('report_end_date',
"ADD COLUMN `report_end_date` date NULL COMMENT '汇报结束日期' AFTER `report_date`"
),
]
for tup in addCols:
if not Tool.colExist(dbName, tblName, tup[0]):
sql += alter + tup[1] + "; "
# user
tblName = sn + "_user"
alter = " alter table " + tblName + " "
addCols = [
('report_to',
"ADD COLUMN `report_to` int(11) NULL DEFAULT 1 COMMENT '汇报对象' AFTER `range` ;"
),
]
for tup in addCols:
if not Tool.colExist(dbName, tblName, tup[0]):
sql += alter + tup[1] + '; '
# apply_config
tblName = sn + "_apply_config"
alter = " alter table `" + tblName + "` "
addCols = [
('intel_approver',
"ADD COLUMN `intel_approver` varchar(255) NULL COMMENT '智能申请审批职位id' AFTER `approver`"
),
('company',
"ADD COLUMN `company` tinyint(1) NULL DEFAULT 0 COMMENT '是否全公司适用' AFTER `department_id`"
),
('hand_sign',
"ADD COLUMN `hand_sign` tinyint(1) NULL DEFAULT 1 COMMENT '是否启用手签' AFTER `approver`"
),
('process_type',
"ADD COLUMN `process_type` tinyint(1) NULL DEFAULT 1 COMMENT '审批流程类别。1员工2智能（职位）' AFTER `hand_sign`"
),
]
for tup in addCols:
if not Tool.colExist(dbName, tblName, tup[0]):
sql += alter + tup[1] + '; '
dropCols = [
('apply_enname', "drop column `apply_enname`"),
('url', "drop column `url`"),
]
for tup in dropCols:
if Tool.colExist(dbName, tblName, tup[0]):
sql += alter + tup[1] + ';'
modify = '''
%s MODIFY COLUMN `department_id` varchar(100) NULL DEFAULT NULL COMMENT '可使用此申请的部门id' AFTER `type`;
%s MODIFY COLUMN `approver` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '申请审批人id' AFTER `apply_name`;
'''
sql += modify % ((alter, ) * 2)
# apply
tblName = sn + "_apply"
alter = " alter table `" + tblName + "` "
addCols = [
('carbon_copy',
"ADD COLUMN `carbon_copy` varchar(1000) NULL COMMENT '抄送人员id' AFTER `approver`"
),
('files',
"ADD COLUMN `files` varchar(255) NULL COMMENT '附带文件（图片）' AFTER `content`"
),
('day_count',
"ADD COLUMN `day_count` int NULL DEFAULT NULL COMMENT '天数' AFTER `files`"
),
('hour_count',
"ADD COLUMN `hour_count` int NULL DEFAULT NULL COMMENT '小时数' AFTER `day_count`"
),
('process_type',
"ADD COLUMN `process_type` tinyint(1) NULL DEFAULT 1 COMMENT '审批流程列表。1员工审批2智能审批（职位）' AFTER `approver`"
),
('hand_sign',
"ADD COLUMN `hand_sign` tinyint(1) NULL COMMENT '是否开启手签1是0否' AFTER `gps_location`"
),
('overtime_type',
"ADD COLUMN `overtime_type` tinyint(1) NULL DEFAULT 0 COMMENT '加班类型，是否法定假日' AFTER `e_time`"
),
]
for tup in addCols:
if not Tool.colExist(dbName, tblName, tup[0]):
sql += alter + tup[1] + '; '
modify = '''
%s MODIFY COLUMN `content` text CHARACTER SET utf8 COLLATE utf8_general_ci NULL COMMENT '内容' AFTER `title`;
'''
sql += modify % alter
# apply_info
tblName = sn + "_apply_info"
alter = " alter table `" + tblName + "` "
addCols = [
('role_id',
"ADD COLUMN `role_id` int NULL COMMENT '职位id。智能审批流程时用' AFTER `accepter`"
),
('remind_time',
"ADD COLUMN `remind_time` datetime NULL COMMENT '上一次催办时间' AFTER `read`"
),
]
for tup in addCols:
if not Tool.colExist(dbName, tblName, tup[0]):
sql += alter + tup[1] + '; '
modify = '''
%s MODIFY COLUMN `accepter` int(11) NULL COMMENT '申请接收人id（审批人及自己）' AFTER `apply_id`;
'''
sql += modify % alter
# notice
tblName = sn + "_notice"
alter = " alter table `" + tblName + "` "
addCols = [
('send_all',
"ADD COLUMN `send_all` tinyint(4) NULL COMMENT '是否全公司发送：0，否；1，是；' AFTER `status`"
),
('department_id',
"ADD COLUMN `department_id` varchar(1000) NULL COMMENT '公司接收部门ID集合，以|区分' AFTER `send_all`"
),
('user_id',
"ADD COLUMN `user_id` varchar(1000) NULL COMMENT '公司接收用户ID集合，以|区分' AFTER `department_id`"
),
('attachments',
"ADD COLUMN `attachments` varchar(255) NULL COMMENT '公告的附件或图片集合，以；；区分' AFTER `user_id`"
),
]
for tup in addCols:
if not Tool.colExist(dbName, tblName, tup[0]):
sql += alter + tup[1] + '; '
modify = '''
%s MODIFY COLUMN `content` varchar(1000) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '公告内容' AFTER `title`;
%s MODIFY COLUMN `e_date` date NULL COMMENT '失效时间' AFTER `s_date`;
'''
sql += modify % ((alter, ) * 2)
# display
tblName = sn + "_display"
s = " ALTER TABLE `%s` MODIFY COLUMN `sort` integer(5) NOT NULL COMMENT '排序' AFTER `add_time`;"
sql += (s % tblName)
return sql
