public void Execute()
{
//BackUpTables();
progress = 0;
var tasks = new List <Task>();
DataArchivingViewModel.Instance().ShowMessage($"正在执行归档...请等待...");
foreach (var table in DataArchivingViewModel.Instance().Tables)
{
//Thread.Sleep(2000);
tasks.Add(Task.Factory.StartNew(() =>
{
string archiveTable = $"{table}_{DateTime.Now.Year.ToString()}";
if (!TableIsExists(archiveTable))
{
CreateTable(table, archiveTable);
}
CompareColumns(table, archiveTable);
DataArchive(table, archiveTable);
}));
}
Task.WaitAll(tasks.ToArray());
DataArchivingViewModel.Instance().ShowMessage($"数据归档已全部完成", 100);
}
/// <summary>
/// 执行归档
/// </summary>
/// <param name="context"></param>
public void Execute(IJobExecutionContext context)
{
ExecuteDataArchive dataArchive = new ExecuteDataArchive();
if (!EnvironmentInfo.IsAutoArchive)
{
dataArchive.ExecuteEx();
return;
}
DataArchivingViewModel.Instance().ShowMessage("定时任务执行数据归档...", 0);
dataArchive.Execute();
}
/// <summary>
/// 只执行比对表结构的任务
/// </summary>
public void ExecuteEx()
{
foreach (var table in DataArchivingViewModel.Instance().Tables)
{
string archiveTable = $"{table}_{DateTime.Now.Year.ToString()}";
if (!TableIsExists(archiveTable))
{
CreateTable(table, archiveTable);
}
CompareColumns(table, archiveTable);
}
}
private string GetTimeField(string tableName)
{
var table = DataArchivingViewModel.Instance().ArchiveTables.FirstOrDefault(x => tableName.Contains(x.TableName));
if (table == null)
{
return("");
}
else
{
return(table.DateField);
}
}
/// <summary>
/// 开始归档
/// </summary>
private void DataArchive(ArchiveTable table, string archiveTableName)
{
string bllTableName = table.TableName;
try
{
using (MySqlConnection conn = new MySqlConnection(EnvironmentInfo.ConnectionString))
{
conn.Open();
MySqlTransaction transaction = conn.BeginTransaction();
MySqlCommand cmd = conn.CreateCommand();
cmd.CommandTimeout = int.MaxValue;//超时时间设置60分钟
cmd.Transaction = transaction;
try
{
DateTime archiveDate = DateTime.Now.Date.AddMonths(-EnvironmentInfo.AutoArchiveMonth);
string sql = $"insert into `{archiveTableName}` select * from `{bllTableName}` where {GetTimeField(bllTableName)} < '{archiveDate.ToString("yyyy-MM-dd HH:mm:ss")}'";
if (!string.IsNullOrEmpty(table.Where))
{
sql += $" {table.Where}";
}
cmd.CommandText = sql;
LogHelper.CommLogger.Info(sql);
int x = cmd.ExecuteNonQuery();
string script = $"delete from `{bllTableName}` where {GetTimeField(bllTableName)} < '{archiveDate.ToString("yyyy-MM-dd HH:mm:ss")}'";
if (!string.IsNullOrEmpty(table.Where))
{
sql += $" {table.Where}";
}
cmd.CommandText = script;
LogHelper.CommLogger.Info(script);
int y = cmd.ExecuteNonQuery();
transaction.Commit();
}
catch (Exception ex)
{
transaction.Rollback();
LogHelper.CommLogger.Error("数据归档遇到些问题，事务回滚：" + ex.ToString());
}
}
progress += 20;
DataArchivingViewModel.Instance().ShowMessage($"表{bllTableName}归档完成...请继续等待...", progress);
}
catch (Exception ex)
{
LogHelper.CommLogger.Error("数据归档遇到些问题：" + ex.ToString());
}
}
public DataArchiving()
{
InitializeComponent();
viewModel = DataArchivingViewModel.Instance();
DataContext = viewModel;
archiveMonths.Add(3, "归档3个月前的记录");
archiveMonths.Add(4, "归档4个月前的记录");
archiveMonths.Add(5, "归档5个月前的记录");
archiveMonths.Add(6, "归档6个月前的记录");
archiveMonths.Add(7, "归档7个月前的记录");
archiveMonths.Add(8, "归档8个月前的记录");
archiveMonths.Add(9, "归档9个月前的记录");
archiveMonths.Add(10, "归档10个月前的记录");
archiveMonths.Add(11, "归档11个月前的记录");
archiveMonths.Add(12, "归档12个月前的记录");
}
/// <summary>
/// 开始归档
/// </summary>
private void DataArchive(string bllTableName, string archiveTableName)
{
try
{
using (MySqlConnection conn = new MySqlConnection(EnvironmentInfo.ConnectionString))
{
conn.Open();
MySqlTransaction transaction = conn.BeginTransaction();
MySqlCommand cmd = conn.CreateCommand();
cmd.CommandTimeout = 3600;//超时时间设置60分钟
cmd.Transaction = transaction;
try
{
string sql = $"insert into `{archiveTableName}` select * from `{bllTableName}` where DATE_ADD({GetTimeField(bllTableName)},INTERVAL {EnvironmentInfo.AutoArchiveMonth} Month) < now()";
if (bllTableName == "box_enter_record")
{
sql += " and wasgone=1";
}
cmd.CommandText = sql;
int x = cmd.ExecuteNonQuery();
string script = $"delete from `{bllTableName}` where DATE_ADD({GetTimeField(bllTableName)},INTERVAL {EnvironmentInfo.AutoArchiveMonth} Month) < now()";
if (bllTableName == "box_enter_record")
{
script += " and wasgone=1";
}
cmd.CommandText = script;
int y = cmd.ExecuteNonQuery();
transaction.Commit();
}
catch (Exception ex)
{
transaction.Rollback();
DataArchivingViewModel.Instance().ShowMessage("数据归档遇到些问题");
}
}
progress += 20;
DataArchivingViewModel.Instance().ShowMessage($"表{bllTableName}归档完成...请继续等待...", progress);
}
catch (Exception ex)
{
DataArchivingViewModel.Instance().ShowMessage("数据归档遇到些问题");
}
}
public void Execute()
{
//BackUpTables();
progress = 0;
var tasks = new List <Task>();
DataArchivingViewModel.Instance().ShowMessage($"正在执行归档...请等待...");
foreach (var table in DataArchivingViewModel.Instance().ArchiveTables)
{
//Thread.Sleep(2000);
tasks.Add(Task.Factory.StartNew(() =>
{
string archiveTable = $"{table.TableName}_{DateTime.Now.Year.ToString()}";//2021
if (!TableIsExists(archiveTable))
{
CreateTable(table.TableName, archiveTable);
}
CompareColumns(table.TableName, archiveTable);
string archiveTableV2 = $"{table.TableName}_{(DateTime.Now.Year - 1).ToString()}";//2020
if (!TableIsExists(archiveTableV2))
{
CreateTable(table.TableName, archiveTableV2);
}
CompareColumns(table.TableName, archiveTableV2);
//将当前业务表2020年的数据归档到2020年的表
DataArchiveYear(table, archiveTableV2, DateTime.Now.Year - 1);
//将2021年的归档表中2020年的数据归档到2020年的表
DataArchiveYear(new ArchiveTable()
{
TableName = archiveTable
}, archiveTableV2, DateTime.Now.Year - 1);
//将当前业务表中2021年的数据归档到2021年的表
DataArchive(table, archiveTable);
}));
}
Task.WaitAll(tasks.ToArray());
DataArchivingViewModel.Instance().ShowMessage($"数据归档已全部完成", 100);
}
private void BackUpTables()
{
DataArchivingViewModel.Instance().ShowMessage($"正在执行备份...请等待...");
string BaseDirectoryPath = AppDomain.CurrentDomain.BaseDirectory;
string tables = "box_enter_record box_out_record box_bill boxdoor_door_record business_discount";
string fileName = DateTime.Now.ToString("yyyyMMddHHmmss") + "_archive.sql";
string filePath = Path.Combine(EnvironmentInfo.TaskBackUpPath, fileName);
if (!Directory.Exists(EnvironmentInfo.TaskBackUpPath))
{
Directory.CreateDirectory(EnvironmentInfo.TaskBackUpPath);
}
string mysqlcmd = $"mysqldump --default-character-set=utf8 --single-transaction -h{EnvironmentInfo.DbConnEntity.Ip} -u{EnvironmentInfo.DbConnEntity.UserName} -p{EnvironmentInfo.DbConnEntity.Password} -P{EnvironmentInfo.DbConnEntity.Port} -B {EnvironmentInfo.DbConnEntity.DbName} --tables {tables} > \"{filePath}\"";
List <string> cmds = new List <string>();
cmds.Add(BaseDirectoryPath.Substring(0, 2));
cmds.Add("cd " + BaseDirectoryPath);
cmds.Add(mysqlcmd);
ProcessHelper.ExecuteCommand(cmds);
ZipHelper.ZipFile(filePath, filePath.Replace(".sql", ".zip"));
File.Delete(filePath);
}
