/// <summary>
/// 开始读取数据
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void btn_read_Click(object sender, EventArgs e)
{
if (string.IsNullOrWhiteSpace(txt_ConStr.Text.Trim()))
{
WriteMsg?.Invoke("数据库链接字符串不能为空");
}
else
{
try
{
IRepository repository = new MysqlRepository(txt_ConStr.Text.Trim());
List <TerminalDataSetEntity> terminalDataSetEntities = repository.GetList <TerminalDataSetEntity>("select * from base_terminaldataset").Result.ToList();
List <IndexManageEntity> indexManageEntities = repository.GetList <IndexManageEntity>("select * from base_indexmanage").Result.ToList();
List <IndexAssocationEntity> assocationEntities = repository.GetList <IndexAssocationEntity>("select * from base_indexassociation").Result.ToList();
File.WriteAllText(Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "Content/指标/基础指标数据.txt"), JsonConvert.SerializeObject(terminalDataSetEntities));
File.WriteAllText(Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "Content/指标/标题配置.txt"), JsonConvert.SerializeObject(indexManageEntities));
File.WriteAllText(Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "Content/指标/关联关系.txt"), JsonConvert.SerializeObject(assocationEntities));
WriteMsg?.Invoke("数据读取并存储完毕");
}
catch (Exception ex)
{
WriteMsg?.Invoke(ex.Message);
}
}
}
/// <summary>
/// 开始初始化数据
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void Btn_Begin_Click(object sender, EventArgs e)
{
if (_dbmodel == null && _repository == null)
{
WriteMsg?.Invoke("未选择目标数据库，请在主界面选择要初始化的数据库");
return;
}
WriteMsg?.Invoke("开始备份数据");
List <TerminalDataSetEntity> terminalDataSetEntities = _repository.GetList <TerminalDataSetEntity>("select * from base_terminaldataset").Result.ToList();
WriteMsg?.Invoke("base_terminaldataset数据读取完毕");
List <IndexManageEntity> indexManageEntities = _repository.GetList <IndexManageEntity>("select * from base_indexmanage").Result.ToList();
WriteMsg?.Invoke("base_indexmanage数据读取完毕");
List <IndexAssocationEntity> assocationEntities = _repository.GetList <IndexAssocationEntity>("select * from base_indexassociation").Result.ToList();
WriteMsg?.Invoke("base_indexassociation数据读取完毕");
if (!Directory.Exists(Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "Content/指标/备份")))
{
Directory.CreateDirectory(Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "Content/指标/备份"));
}
File.WriteAllText(Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "Content/指标/备份/基础指标数据.txt"), JsonConvert.SerializeObject(terminalDataSetEntities));
WriteMsg?.Invoke("base_terminaldataset数据备份完毕");
File.WriteAllText(Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "Content/指标/备份/标题配置.txt"), JsonConvert.SerializeObject(indexManageEntities));
WriteMsg?.Invoke("base_indexmanage数据备份完毕");
File.WriteAllText(Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "Content/指标/备份/关联关系.txt"), JsonConvert.SerializeObject(assocationEntities));
WriteMsg?.Invoke("base_indexassociation数据备份完毕");
WriteMsg?.Invoke("开始删除目标服务器里的数据");
}
/// <summary>
/// 读取指标与分类的关联关系
/// </summary>
/// <returns></returns>
private List <IndexAssocationEntity> ReadAssocation()
{
List <IndexAssocationEntity> data = new List <IndexAssocationEntity>();
string path = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "Content/指标/关联关系.xlsx");
if (!File.Exists(path))
{
WriteMsg?.Invoke("未找到“指标分类.xlsx文件”，请检查文件在不在程序根目录下");
WriteMsg?.Invoke("==用navicat执行下面sql语句导出Excel文件放到根目录下的Content/指标 里面==");
WriteMsg?.Invoke(@"select b.NATURE,a.* from base_indexassociation a
inner join base_department b
on a.deptid=b.DEPARTMENTID ");
WriteMsg?.Invoke("===========附件名称：关联关系.xlsx=============");
throw new ArgumentNullException("未找到附件");
}
var book = new Workbook(path);
var sheet = book.Worksheets[0];
for (int i = 1; i <= sheet.Cells.MaxDataRow; i++)
{
IndexAssocationEntity indexEntity = new IndexAssocationEntity()
{
Nature = sheet.Cells[i, 0].StringValue.Trim(),
Id = sheet.Cells[i, 1].StringValue.Trim(),
TitleId = sheet.Cells[i, 2].StringValue.Trim(),
DataSetId = sheet.Cells[i, 3].StringValue.Trim(),
DeptId = sheet.Cells[i, 4].StringValue.Trim(),
};
data.Add(indexEntity);
}
return(data);
}
/// <summary>
/// 获取数据库选择窗体的数据库信息
/// </summary>
/// <param name="db"></param>
private void GetDbInfo(DBModel db, IRepository repository)
{
_dbmodel = db;
_dbmodel.BuidConStr();
txt_checkDB.Text = _dbmodel.ConnStr;
_repository = repository;
WriteMsg?.Invoke("数据库选择完毕");
}
/// <summary>
/// 读取指标分类信息
/// </summary>
/// <returns></returns>
private List <IndexManageEntity> ReadIndex()
{
List <IndexManageEntity> data = new List <IndexManageEntity>();
string path = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "Content/指标/指标分类.xlsx");
if (!System.IO.File.Exists(path))
{
WriteMsg?.Invoke("未找到“指标分类.xlsx文件”，请检查文件在不在程序根目录下");
WriteMsg?.Invoke("==用navicat执行下面sql语句导出Excel文件放到根目录下的Content/指标 里面==");
WriteMsg?.Invoke(@"select b.NATURE,a.* from base_indexmanage a
left join base_department b
on a.deptid= b.DEPARTMENTID" );
WriteMsg?.Invoke("=============附件名称：指标分类.xlsx================");
throw new ArgumentNullException("未找到附件");
}
var book = new Workbook(path);
var sheet = book.Worksheets[0];
for (int i = 1; i <= sheet.Cells.MaxDataRow; i++)
{
IndexManageEntity indexEntity = new IndexManageEntity()
{
Nature = sheet.Cells[i, 0].StringValue.Trim(),
Id = sheet.Cells[i, 1].StringValue.Trim(),
DeptId = sheet.Cells[i, 2].StringValue.Trim(),
Title = sheet.Cells[i, 3].StringValue.Trim(),
DeptCode = sheet.Cells[i, 4].StringValue.Trim(),
DeptName = sheet.Cells[i, 5].StringValue.Trim(),
Sort = sheet.Cells[i, 6].IntValue,
IsShow = sheet.Cells[i, 7].IntValue,
CreateUserId = sheet.Cells[i, 8].StringValue.Trim(),
CreateDate = DateTime.Parse(sheet.Cells[i, 9].StringValue),
CreateUserName = sheet.Cells[i, 10].StringValue.Trim(),
ModifyUserId = sheet.Cells[i, 11].StringValue.Trim(),
ModifyDate = DateTime.Parse(sheet.Cells[i, 12].StringValue),
ModifyUserName = sheet.Cells[i, 13].StringValue.Trim(),
IndexType = sheet.Cells[i, 14].IntValue,
Templet = sheet.Cells[i, 15].IntValue,
};
data.Add(indexEntity);
}
return(data);
}
/// <summary>
/// 检验数据模板
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void BtnCheckData_Click(object sender, EventArgs e)
{
bool result = true;
List <TerminalDataSetEntity> terminalDataSetEntities;
List <IndexManageEntity> indexManageEntities;
List <IndexAssocationEntity> assocationEntities;
WriteMsg?.Invoke("开始检验");
if (File.Exists(Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "Content/指标/基础指标数据.txt")))
{
WriteMsg?.Invoke("基础指标数据.txt文件正常");
terminalDataSetEntities = ReadIndexList().Result;
if (terminalDataSetEntities != null && terminalDataSetEntities.Count > 0)
{
WriteMsg?.Invoke("基础指标数据.txt数据正常");
}
else
{
WriteMsg?.Invoke("基础指标数据.txt无数据");
result = false;
}
}
else
{
WriteMsg?.Invoke("基础指标数据.txt文件不存在");
} result = false;
if (File.Exists(Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "Content/指标/标题配置.txt")))
{
WriteMsg?.Invoke("标题配置.txt文件正常");
indexManageEntities = ReadIndexManageList().Result;
if (indexManageEntities != null && indexManageEntities.Count > 0)
{
WriteMsg?.Invoke("标题配置.txt数据正常");
}
else
{
WriteMsg?.Invoke("标题配置.txt无数据");
result = false;
}
}
else
{
WriteMsg?.Invoke("标题配置.txt文件不存在");
} result = false;
if (File.Exists(Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "Content/指标/关联关系.txt")))
{
WriteMsg?.Invoke("关联关系.txt文件正常");
assocationEntities = ReadAssocationList().Result;
if (assocationEntities != null && assocationEntities.Count > 0)
{
WriteMsg?.Invoke("关联关系.txt数据正常");
}
else
{
WriteMsg?.Invoke("关联关系.txt无数据");
result = false;
}
}
else
{
WriteMsg?.Invoke("关联关系.txt文件不存在");
} result = false;
if (result == false)
{
WriteMsg?.Invoke("数据检验完毕，数据有问题，忽略并执行初始化请点击“开始初始化”按钮，或点击“重新读取并存储数据”");
}
else
{
WriteMsg?.Invoke("数据检验完毕，点击“重新读取并存储数据”");
}
}
/// <summary>
/// 初始化指标
/// </summary>
public void InitIndexData()
{
//先获取所有的指标
List <DepartmentEntity> deptList = _service.GetList <DepartmentEntity>("select * from base_department").Result.ToList();
List <IndexManageEntity> indexManages = this.ReadIndex();
List <IndexAssocationEntity> assocationEntities = this.ReadAssocation();
List <string> indexSqlList = new List <string>();
List <string> asscocationSqlList = new List <string>();
Dictionary <string, string> dic = new Dictionary <string, string>(); //index Key 新的ID ，value 旧的Id
#region 指标
if (indexManages != null && indexManages.Count > 0)
{
indexManages.ForEach(index =>
{
List <DepartmentEntity> natureList = deptList.Where(x => x.Nature == index.Nature).ToList();
if (natureList != null && natureList.Count > 0)
{
natureList.ForEach(dept =>
{
string guidId = Guid.NewGuid().ToString();
string indexInsert = @"INSERT INTO BASE_INDEXMANAGE(ID, DEPTID, TITLE, DEPTCODE, DEPTNAME, SORT, ISSHOW, CREATEUSERID, CREATEDATE, CREATEUSERNAME, MODIFYUSERID, MODIFYDATE, MODIFYUSERNAME, INDEXTYPE, Templet)
VALUES ('" + guidId + "', '" + dept.DepartmentId + "', '" + index.Title + "', '" + dept.EnCode + "', '" + dept.FullName + "', " + index.Sort + ", " + index.IsShow + ", 'SYSTEM',{0} , 'Software', 'SYSTEM', {0}, 'Software', " + index.IndexType + ", " + index.Templet + ")";
if (_dbmodel.DBType == "Oracle")
{
indexInsert = string.Format(indexInsert, $"to_date('{DateTime.Now:yyyy-MM-dd HH:mm:ss}','yyyy-mm-dd hh24:mi:ss')");
}
else
{
indexInsert = string.Format(indexInsert, $"'{DateTime.Now:yyyy-MM-dd HH:mm-ss}'");
}
indexSqlList.Add(indexInsert);
dic.Add(guidId, index.Id);
});
}
});
}
#endregion 指标
#region 指标的关联关系
assocationEntities.ForEach(ass =>
{
string indexId = dic.FirstOrDefault(x => x.Value == ass.TitleId).Key;
List <DepartmentEntity> natureList = deptList.Where(x => x.Nature == ass.Nature).ToList();
if (natureList != null && natureList.Count > 0)
{
natureList.ForEach(dept =>
{
string insertSql = $"INSERT INTO base_indexassociation(Id, TitleId, DataSetId, DeptId) VALUES ('{Guid.NewGuid().ToString()}', '{indexId}', '{ass.DataSetId}', '{dept.DepartmentId}')";
asscocationSqlList.Add(insertSql);
});
}
});
#endregion 指标的关联关系
List <Task> taskList = new List <Task>();
WriteMsg?.Invoke($"开始执行指标配置 {indexSqlList.Count}条数据");
indexSqlList.ForEach(x =>
{
WriteMsg?.Invoke("执行sql语句：" + x);
var task = Task.Run(() => { _service.ExecuteNonQuery(x); });
taskList.Add(task);
});
WriteMsg?.Invoke($"开始执行关联关系 {asscocationSqlList.Count}条数据");
asscocationSqlList.ForEach(x =>
{
WriteMsg?.Invoke("执行sql语句：" + x);
var task = Task.Run(() => { _service.ExecuteNonQuery(x); });
taskList.Add(task);
});
WriteMsg?.Invoke("指标配置执行完毕");
}
