meidu2021/daju/target/classes/cn/com/lzt/timertask/BigDataTableBackupTask.java

package cn.com.lzt.timertask;

import java.text.SimpleDateFormat;
import java.util.Date;

import org.apache.log4j.Logger;
import org.jeecgframework.core.constant.Globals;
import org.jeecgframework.core.util.ApplicationContextUtil;
import org.jeecgframework.core.util.DataUtils;
import org.jeecgframework.web.system.service.SystemService;
import org.quartz.JobExecutionContext;
import org.quartz.JobExecutionException;
import org.springframework.stereotype.Service;

import com.xcgl.utils.XcglDateUtils;

@Service("bigDataTableBackupTask")
public class BigDataTableBackupTask implements org.quartz.Job {

	private static final Logger logger = Logger.getLogger(BigDataTableBackupTask.class);
	
	@Override
	public void execute(JobExecutionContext arg0) throws JobExecutionException {
		long start = System.currentTimeMillis();
		logger.error("===================大数据量表备份任务开始===================");
		try {
			logger.error("===================大数据量表备份任务开始"+DataUtils.datetimeFormat.format(new Date()));
			//外包服务合同生效
			SystemService service = ApplicationContextUtil.getContext().getBean(SystemService.class);
			String attendance_totalSQL = "INSERT INTO t_bus_user_attendance_total_h(id, user_id, user_code, pid, departid, in_postid, workingdays, yearmonth, datum_attendance_date, actual_attendance_date, time_difference_total, adjust_time, owe_time, overtime_total_time, leave_total_time, adjust_record, attendance_status, early_leave_amount, late_amount, absenteeism_amount_orig, absenteeism_amount, no_punch_card_amount, paid_leave, thing_leave, sick_leave, marriage_leave, funeral_leave, maternity_leave, accompany_maternity_leave, national_duty_duration, timeout_overtime_duration_orig, timeout_overtime_duration, national_overtime_duration, duty_duration, special_overtime, day_shift_subsidy, night_shift_subsidy, day_shift, regular_shift, fixed_value, extra_overtime, confirm_status, confirm_date, confirmor, ifmoved, create_name, create_by, create_date, update_name, update_by, update_date, sys_org_code, sys_company_code, bpm_status, json_obj,exchangetime) \n" + 
					"select id, user_id, user_code, pid, departid, in_postid, workingdays, yearmonth, datum_attendance_date, actual_attendance_date, time_difference_total, adjust_time, owe_time, overtime_total_time, leave_total_time, adjust_record, attendance_status, early_leave_amount, late_amount, absenteeism_amount_orig, absenteeism_amount, no_punch_card_amount, paid_leave, thing_leave, sick_leave, marriage_leave, funeral_leave, maternity_leave, accompany_maternity_leave, national_duty_duration, timeout_overtime_duration_orig, timeout_overtime_duration, national_overtime_duration, duty_duration, special_overtime, day_shift_subsidy, night_shift_subsidy, day_shift, regular_shift, fixed_value, extra_overtime, confirm_status, confirm_date, confirmor, ifmoved, create_name, create_by, create_date, update_name, update_by, update_date, sys_org_code, sys_company_code, bpm_status, json_obj,exchangetime \n" + 
					"from t_bus_user_attendance_total \n" 
					+" where yearmonth < ?;";
			String attendance_detailSQL = "INSERT INTO t_bus_user_attendance_detail_h(id, attendance_id, status, attendance_date, retreat_date, duty_id, datum_date, time_difference, fine_tuning_type, time_fine_tuning, adjust_reason, borrowstatus, borrowspid, borrowsdepartid, attendance_mins, timeout_overtime_duration, national_overtime_duration, special_overtime, day_pay, actualattendance_date, json_obj, ymd_date) \n" + 
					"select id, attendance_id, status, attendance_date, retreat_date, duty_id, datum_date, time_difference, fine_tuning_type, time_fine_tuning, adjust_reason, borrowstatus, borrowspid, borrowsdepartid, attendance_mins, timeout_overtime_duration, national_overtime_duration, special_overtime, day_pay, actualattendance_date, json_obj, ymd_date\n" + 
					"from t_bus_user_attendance_detail \n" + 
					"where left(ymd_date,7) < ?";
			String attendance_tempSQL = "INSERT INTO t_bus_attendance_temp_h(id, create_name, create_by, create_date, update_name, update_by, update_date, sys_org_code, sys_company_code, bpm_status, user_id, user_code, realname, belong_unitid, attendance_date, datum_stime, datum_etime, sign_date, newest_punchcard_date, attendance_status, arrange_duty_id, attendance_datetime, retreat_datetime, alldatetime, lateminute, leaveearlyminute)\n" + 
					"select id, create_name, create_by, create_date, update_name, update_by, update_date, sys_org_code, sys_company_code, bpm_status, user_id, user_code, realname, belong_unitid, attendance_date, datum_stime, datum_etime, sign_date, newest_punchcard_date, attendance_status, arrange_duty_id, attendance_datetime, retreat_datetime, alldatetime, lateminute, leaveearlyminute\n" + 
					"from t_bus_attendance_temp \n" + 
					"where left(attendance_date,7) < ?";
			String dingdingSQL = "INSERT INTO t_bus_ding_attendanceinfo_h(id, user_code, belong_unitid, attendance_date, create_by, create_name, sys_company_code, ding_uniqueid, update_by, sys_org_code, bpm_status, create_date, update_name, update_date, attendance_time, user_id, realname, is_dispose)\n" + 
					"select id, user_code, belong_unitid, attendance_date, create_by, create_name, sys_company_code, ding_uniqueid, update_by, sys_org_code, bpm_status, create_date, update_name, update_date, attendance_time, user_id, realname, is_dispose\n" + 
					"from t_bus_ding_attendanceinfo\n" + 
					"where left(attendance_date,7) < ?";
			
			String arrange_general = "INSERT INTO t_bus_projarrange_general_h(id, yearmonth, belong_unitid, people_quantity, arrange_duty_status, reporter_id, create_name, create_by, create_date, update_name, update_by, update_date, sys_org_code, sys_company_code) \n" + 
					"select id, yearmonth, belong_unitid, people_quantity, arrange_duty_status, reporter_id, create_name, create_by, create_date, update_name, update_by, update_date, sys_org_code, sys_company_code\n" + 
					"from t_bus_projarrange_general \n" + 
					"where yearmonth < ?";
			String arrange_detail = "INSERT INTO t_bus_projarrange_detail_h(id, projarrange_generalid, pjt_postid, userid, working_days, timeout_overtime_hours, country_overtime_hours, onduty_days, remark, create_name, create_by, create_date, update_name, update_by, update_date, sys_org_code, sys_company_code, yearmonth, pid)\n" + 
					"select id, projarrange_generalid, pjt_postid, userid, working_days, timeout_overtime_hours, country_overtime_hours, onduty_days, remark, create_name, create_by, create_date, update_name, update_by, update_date, sys_org_code, sys_company_code, yearmonth, pid \n" + 
					"from t_bus_projarrange_detail \n" + 
					"where yearmonth <  ?";
			String arrange_user = "INSERT INTO t_bus_user_arrange_detail_h(id, calendarid, shiftid, projarrange_detailid, create_name, create_by, create_date, update_name, update_by, update_date, sys_org_code, sys_company_code) \n" + 
					"select id, calendarid, shiftid, projarrange_detailid, create_name, create_by, create_date, update_name, update_by, update_date, sys_org_code, sys_company_code\n" + 
					"from t_bus_user_arrange_detail\n" + 
					"where projarrange_detailid in (select id from t_bus_projarrange_detail where  yearmonth < ?)";
			
			String attendance_totalSQL_Del = "delete " + 
					"from t_bus_user_attendance_total \n" 
					+" where yearmonth < ?;";
			String attendance_detailSQL_Del = "delete " + 
					"from t_bus_user_attendance_detail \n" + 
					"where left(ymd_date,7) < ?";
			String attendance_tempSQL_Del = "delete " + 
					"from t_bus_attendance_temp \n" + 
					"where left(attendance_date,7) < ?";
			String dingdingSQL_Del = "delete " + 
					"from t_bus_ding_attendanceinfo\n" + 
					"where left(attendance_date,7) < ?";
			String arrange_user_Del = "delete " + 
					"from t_bus_user_arrange_detail \n" 
					+" where projarrange_detailid in (select id from t_bus_projarrange_detail where  yearmonth < ?)";
			String arrange_detail_Del = "delete " + 
					"from t_bus_projarrange_detail \n" 
					+" where yearmonth < ?";
			String arrange_general_Del = "delete " + 
					"from t_bus_projarrange_general \n" 
					+" where yearmonth < ?";
			//例如：7月份备份5月份之前的数据，保留5、6月份2个月
			String backupMonth = XcglDateUtils.addDateMonth(new Date(), -2,new SimpleDateFormat("yyyy-MM"));
			Integer count = 0;
			//考勤数据
			count = service.executeSql(attendance_totalSQL, backupMonth);
			logger.error("备份"+backupMonth+"前数据，表：attendance_total，数据"+count+"条");
			count = service.executeSql(attendance_detailSQL, backupMonth);
			logger.error("备份"+backupMonth+"前数据，表：attendance_detail，数据"+count+"条");
			count = service.executeSql(attendance_tempSQL, backupMonth);
			logger.error("备份"+backupMonth+"前数据，表：attendance_temp，数据"+count+"条");
			//钉钉数据
			count = service.executeSql(dingdingSQL, backupMonth);
			logger.error("备份"+backupMonth+"前数据，表：ding_attendanceinfo，数据"+count+"条");
			//排班主子孙表
			count = service.executeSql(arrange_general, backupMonth);
			logger.error("备份"+backupMonth+"前数据，表：arrange_general，数据"+count+"条");
			count = service.executeSql(arrange_detail, backupMonth);
			logger.error("备份"+backupMonth+"前数据，表：arrange_detail，数据"+count+"条");
			count = service.executeSql(arrange_user, backupMonth);
			logger.error("备份"+backupMonth+"前数据，表：arrange_user，数据"+count+"条");
			//删除原表数据
			count = service.executeSql(dingdingSQL_Del, backupMonth);
			logger.error("删除"+backupMonth+"前数据，表：ding_attendanceinfo，数据"+count+"条");
			count = service.executeSql(attendance_tempSQL_Del, backupMonth);
			logger.error("删除"+backupMonth+"前数据，表：attendance_temp，数据"+count+"条");
			count = service.executeSql(attendance_detailSQL_Del, backupMonth);
			logger.error("删除"+backupMonth+"前数据，表：attendance_detail，数据"+count+"条");
			count = service.executeSql(attendance_totalSQL_Del, backupMonth);
			logger.error("删除"+backupMonth+"前数据，表：attendance_total，数据"+count+"条");
			count = service.executeSql(arrange_user_Del, backupMonth);
			logger.error("删除"+backupMonth+"前数据，表：arrange_user，数据"+count+"条");
			count = service.executeSql(arrange_detail_Del, backupMonth);
			logger.error("删除"+backupMonth+"前数据，表：arrange_detail，数据"+count+"条");
			count = service.executeSql(arrange_general_Del, backupMonth);
			logger.error("删除"+backupMonth+"前数据，表：arrange_general，数据"+count+"条");
			logger.error("===================大数据量表备份任务结束"+DataUtils.datetimeFormat.format(new Date()));
		} catch (Exception e) {
			e.printStackTrace();
		}
		logger.error("===================大数据量表备份任务结束===================");
		long end = System.currentTimeMillis();
		long times = end - start;
		logger.error("总耗时"+times+"毫秒");
	}
}