public static void ExportData(TimeCycleModule timeCycle, List <EvalutationDataModule> data, Dictionary <int, BasicDataModule> basicModules, Dictionary <int, BasicFourModule> fourModules)
{
XWPFDocument doc = null;
FileStream fs = null;
try
{
doc = CreateCover(timeCycle);
//边距失效，重设
doc.Document.body.sectPr.pgBorders.top.space = 70;
doc.Document.body.sectPr.pgBorders.bottom.space = 70;
doc.Document.body.sectPr.pgBorders.left.space = 80;
doc.Document.body.sectPr.pgBorders.right.space = 80;
CreateData(doc, data, basicModules, fourModules);
using (fs = File.OpenWrite($"{timeCycle.UserName}-{timeCycle.Name}.docx"))
{
doc.Write(fs);
}
}
catch (Exception ex)
{
MessageBox.Show("导出失败", "提示", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
/// <summary>
/// 评价阶段改变事件
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void combo_timeCycle_SelectedIndexChanged(object sender, EventArgs e)
{
this.lbl_timePeriods.Text = string.Empty;
currentTime = timeModules[((ComboBox)sender).SelectedIndex];
lbl_timePeriods.Text = currentTime.StartTime.ToString("yyyy-MM-dd") + " / " + currentTime.EndTime.ToString("yyyy-MM-dd");
DataRefresh();
}
/// <summary>
/// 评价阶段改变事件
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void combo_timeCycle_SelectedIndexChanged(object sender, EventArgs e)
{
this.lbl_timePeriods.Text = string.Empty;
currentTime = timeModules[((ComboBox)sender).SelectedIndex];
lbl_timePeriods.Text = currentTime.StartTime.ToString("yyyy-MM-dd") + " / " + currentTime.EndTime.ToString("yyyy-MM-dd");
evalutationDatas = (List <EvalutationDataModule>)SqliteHelper.Select(TableName.EvalutationData, currentTime.ID);
DataRefresh();
}
/// <summary>
/// 导出当前周期数据为WORD
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void btn_exportWord_Click(object sender, EventArgs e)
{
TimeCycleModule currentTime = timeModules[combo_timeCycle.SelectedIndex];
if (currentTime == null || evalutationModules == null || evalutationModules.Count == 0)
{
return;
}
Dictionary <int, BasicFourModule> fourModules = ((List <BasicFourModule>)SqliteHelper.Select(TableName.BasicFour)).ToDictionary(key => key.ID, fourModule => fourModule);
WordHelper.ExportData(currentTime, evalutationModules.Values.ToList(), basicModules, fourModules);
MessageBox.Show("导出成功", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
/// <summary>
/// check timecycle exit
/// </summary>
/// <param name="tableName">table name</param>
/// <param name="colData">column check data</param>
/// <returns>true:exist, false:not exist</returns>
private static bool CheckTimeData(string tableName, TimeCycleModule data, int id)
{
cmd.CommandText = $"SELECT count(*) FROM '{tableName}' WHERE name='{data.Name}' AND start_time='{data.StartTime}' AND end_time='{data.EndTime}' AND user_name='{data.UserName}'";
if (id != -1)
{
cmd.CommandText += $" AND id!={id}";
}
if (Convert.ToInt32(cmd.ExecuteScalar()) > 0)
{
return(true);
}
return(false);
}
public static XWPFDocument CreateCover(TimeCycleModule timeCycle)
{
XWPFDocument doc1 = null;
using (FileStream stream = File.OpenRead("封面.docx"))
{
doc1 = new XWPFDocument(stream);
CreateConverText(doc1.Tables[0].Rows[0].GetCell(1), timeCycle.UserName, 14);
CreateConverText(doc1.Tables[0].Rows[1].GetCell(1), timeCycle.Name, 14);
CreateConverText(doc1.Tables[0].Rows[2].GetCell(1), timeCycle.LatestCommitTime.ToString("yyyy-MM-dd"), 14);
CreateConverText(doc1.Tables[1].Rows[0].GetCell(0), DateTime.Now.ToString("yyyy年MM月dd日"), 22);
}
return(doc1);
}
/// <summary>
/// 删除当前周期
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void btn_delete_Click(object sender, EventArgs e)
{
if (timeModules.Count == 0)
{
MessageBox.Show("没有可以删除的数据", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
return;
}
if (MessageBox.Show("删除后，关于此周期的数据将一并删除，确定要删除吗?", "提示", MessageBoxButtons.OKCancel, MessageBoxIcon.Question) == DialogResult.OK)
{
TimeCycleModule currentTime = timeModules[combo_timeCycle.SelectedIndex];
SqliteHelper.Delete(TableName.TimeCycle, currentTime.ID);
SqliteHelper.Delete(TableName.EvalutationData, currentTime.ID);
UserRefresh();
}
}
public static void ExportData(TimeCycleModule timeCycle, List <EvalutationDataModule> data, Dictionary <int, BasicDataModule> basicModules, Dictionary <int, BasicFourModule> fourModules)
{
IWorkbook workbook = null;
ISheet sheet = null;
FileStream fs = null;
try
{
workbook = new HSSFWorkbook();
sheet = workbook.CreateSheet("sheet1");
ICellStyle titleStyle = SetTitleStyle(workbook);
IRow row = sheet.CreateRow(0);
ICell cell = row.CreateCell(0);
cell.CellStyle = titleStyle;
cell.SetCellValue("用户");
cell = row.CreateCell(1);
cell.CellStyle = SetDataStyle(workbook);
cell.SetCellValue(timeCycle.UserName);
CreateTimeRows(timeCycle, sheet, workbook, titleStyle);
CreateDataTitleRows(sheet, workbook, titleStyle);
CreateDataRows(workbook, sheet, data, basicModules, fourModules);
sheet.SetColumnWidth(0, 35 * 256);
sheet.SetColumnWidth(1, 35 * 256);
sheet.SetColumnWidth(2, 35 * 256);
sheet.SetColumnWidth(3, 35 * 256);
sheet.SetColumnWidth(4, 35 * 256);
sheet.SetColumnWidth(5, 35 * 256);
sheet.SetColumnWidth(6, 35 * 256);
sheet.SetColumnWidth(7, 35 * 256);
sheet.SetColumnWidth(8, 35 * 256);
sheet.SetColumnWidth(9, 35 * 256);
using (fs = File.OpenWrite($"{timeCycle.UserName}-{timeCycle.Name}.xls"))
{
workbook.Write(fs);
}
}
catch (Exception ex)
{
MessageBox.Show("导出失败", "提示", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
/// <summary>
/// update time cycle
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void btn_update_Click(object sender, EventArgs e)
{
var row = dataGridView1.CurrentRow;
if (row == null)
{
MessageBox.Show("请选择一行数据", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
return;
}
int id = (int)row.Cells["ID"].Value;
TimeCycleModule preModule = null;
foreach (var item in cycleModules)
{
if (item.ID == id)
{
preModule = item;
break;
}
}
if (preModule == null)
{
return;
}
using (TimeCycleChange dialog = new TimeCycleChange(preModule))
{
dialog.ChangeTitle = "修改评价指标";
if (dialog.ShowDialog() == DialogResult.OK)
{
TimeCycleModule module = dialog.GetModule;
SqliteHelper.Update(TableName.TimeCycle, preModule.ID, module, out string msg);
if (string.IsNullOrEmpty(msg))
{
DataRefresh();
}
else
{
MessageBox.Show(msg, "提示", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
}
}
private static void CreateTimeRows(TimeCycleModule timeCycle, ISheet sheet, IWorkbook workbook, ICellStyle titleStyle)
{
ICellStyle cellStyle = SetDateStyle(workbook);
IRow row = sheet.CreateRow(2);
ICell cell = row.CreateCell(0);
cell.CellStyle = titleStyle;
cell.SetCellValue("评价阶段");
cell = row.CreateCell(1);
cell.CellStyle = titleStyle;
cell.SetCellValue("开始时间");
cell = row.CreateCell(2);
cell.CellStyle = titleStyle;
cell.SetCellValue("截止时间");
cell = row.CreateCell(3);
cell.CellStyle = titleStyle;
cell.SetCellValue("创建时间");
cell = row.CreateCell(4);
cell.CellStyle = titleStyle;
cell.SetCellValue("最近提交时间");
row = sheet.CreateRow(3);
cell = row.CreateCell(0);
cell.SetCellValue(timeCycle.Name);
cell = row.CreateCell(1);
cell.CellStyle = cellStyle;
cell.SetCellValue(timeCycle.StartTime);
cell = row.CreateCell(2);
cell.CellStyle = cellStyle;
cell.SetCellValue(timeCycle.EndTime);
cell = row.CreateCell(3);
cell.CellStyle = cellStyle;
cell.SetCellValue(timeCycle.CreateTime);
cell = row.CreateCell(4);
cell.CellStyle = cellStyle;
cell.SetCellValue(timeCycle.LatestCommitTime);
row = null;
cell = null;
cellStyle = null;
}
/// <summary>
/// 提交当前周期所有数据
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void btn_commit_Click(object sender, EventArgs e)
{
if (evalutationModules == null || evalutationModules.Count == 0)
{
return;
}
if (MessageBox.Show("提交后，改评价阶段将不能修改，确认提交？", "提示", MessageBoxButtons.OKCancel, MessageBoxIcon.Question) == DialogResult.OK)
{
SqliteHelper.Update(TableName.EvalutationData, evalutationModules);
TimeCycleModule currentTime = timeModules[combo_timeCycle.SelectedIndex];
currentTime.State = 1;
SqliteHelper.Update(TableName.TimeCycle, currentTime.ID, currentTime, out string msg);
if (string.IsNullOrEmpty(msg))
{
MessageBox.Show("提交成功，请在 往期评价 中查看", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
TimeCycleRefresh();
}
else
{
MessageBox.Show(msg, "提示", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
}
/// <summary>
/// add time cycle
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void btn_add_Click(object sender, EventArgs e)
{
using (TimeCycleChange dialog = new TimeCycleChange())
{
dialog.ChangeTitle = "新增评价阶段";
if (dialog.ShowDialog() == DialogResult.OK)
{
TimeCycleModule module = dialog.GetModule;
module.UserName = currentUser.UserName;
SqliteHelper.Insert(TableName.TimeCycle, module, out string msg);
if (!string.IsNullOrEmpty(msg))
{
MessageBox.Show(msg, "提示", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
List <TimeCycleModule> timeModule = (List <TimeCycleModule>)SqliteHelper.Select(TableName.TimeCycle, TimeCycleState.Local, module.Name, currentUser.UserName);
if (timeModule.Count > 0)
{
InitEvalutationData(timeModule[0].ID);
}
DataRefresh();
}
}
}
/// <summary>
/// insert data to table
/// </summary>
/// <param name="tableName">table name</param>
/// <param name="data">data</param>
/// <returns></returns>
public static void Insert(TableName tableName, object data, out string msg)
{
msg = string.Empty;
switch (tableName)
{
case TableName.User:
UserModule user = (UserModule)data;
if (CheckRowData(tableName.ToString(), user.UserName))
{
msg = "单位已存在";
}
else
{
cmd.CommandText = $"INSERT INTO '{tableName.ToString()}' VALUES('{user.UserName}','{user.PassWord}',{user.RoleId},'{user.ContractPerson}','{user.ContractTelPhone}','{user.CompanyAddress}')";
if (cmd.ExecuteNonQuery() <= 0)
{
msg = "新增单位失败";
}
}
break;
case TableName.BasicData:
BasicDataModule basicData = (BasicDataModule)data;
if (CheckRowData(tableName.ToString(), basicData.Name))
{
msg = "指标已存在";
}
else
{
cmd.CommandText = $"INSERT INTO {tableName.ToString()} (name, level, grade, parent_id) VALUES('{basicData.Name}', {basicData.Level}, {basicData.Grade}, {basicData.ParentId})";
if (cmd.ExecuteNonQuery() <= 0)
{
msg = "数据写入失败";
}
}
break;
case TableName.BasicFour:
BasicFourModule fourData = (BasicFourModule)data;
if (CheckRowData(tableName.ToString(), fourData.Name))
{
msg = "指标已存在";
}
else
{
string strCalModule = string.Empty;
for (int i = 0; i < fourData.CalModules.Length; i++)
{
if (i == 0)
{
strCalModule += (int)fourData.CalModules[i];
}
else
{
strCalModule += "," + (int)fourData.CalModules[i];
}
}
cmd.CommandText = $"INSERT INTO {tableName.ToString()} (name, level, parent_id, basic_score, basic_rule, basic_sub, basic_add, cal_module) VALUES('{fourData.Name}', {fourData.Level}, {fourData.ParentId}, {fourData.BasicScore}, '{fourData.BasicRule}', '{fourData.BasicSub}', '{fourData.BasicAdd}', '{strCalModule}')";
if (cmd.ExecuteNonQuery() <= 0)
{
msg = "数据写入失败";
}
}
break;
case TableName.TimeCycle:
TimeCycleModule timeData = (TimeCycleModule)data;
if (CheckTimeData(tableName.ToString(), timeData, -1))
{
msg = "评价阶段已存在";
}
else
{
cmd.CommandText = $"INSERT INTO {tableName.ToString()} (name, start_time, end_time, create_time, latest_commit_time, state, user_name) VALUES('{timeData.Name}', '{timeData.StartTime}','{timeData.EndTime}','{timeData.CreateTime}','{timeData.LatestCommitTime}', {timeData.State}, '{timeData.UserName}')";
if (cmd.ExecuteNonQuery() <= 0)
{
msg = "数据写入失败";
}
}
break;
case TableName.EvalutationData:
List <EvalutationDataModule> evalutationDatas = (List <EvalutationDataModule>)data;
using (SQLiteTransaction transaction = conn.BeginTransaction())
{
foreach (var item in evalutationDatas)
{
string dataSource = string.Empty;
if (item.DataSource != null)
{
dataSource = string.Join("|", item.DataSource);
}
cmd.CommandText = $"INSERT INTO {tableName.ToString()} (time_cycle, indicator_four, data_source, description, grade) VALUES({item.TimeCycle}, {item.IndicatorFour},'{dataSource}','{item.Description}',{item.Grade})";
cmd.ExecuteNonQuery();
}
transaction.Commit();
}
break;
default:
msg = "table is not exist";
break;
}
}
/// <summary>
/// 数据查询
/// </summary>
/// <param name="tableName"></param>
/// <returns></returns>
public static object Select(TableName tableName, params object[] para)
{
try
{
switch (tableName)
{
case TableName.SysRole:
cmd.CommandText = $"SELECT * FROM {tableName.ToString()}";
List <SysRoleModule> sysRoles = new List <SysRoleModule>();
using (SQLiteDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
SysRoleModule sysRole = new SysRoleModule();
sysRole.RoleId = int.Parse(reader["Role_Id"].ToString());
sysRole.RoleName = reader["Role_Name"].ToString();
sysRoles.Add(sysRole);
}
reader.Close();
}
return(sysRoles);
case TableName.User:
cmd.CommandText = $"SELECT * FROM User left join SysRole on User.Role_Id = SysRole.Role_Id";
if (para.Length == 1)
{
cmd.CommandText += $" WHERE User.name='{para[0].ToString()}'";
}
List <UserModule> users = new List <UserModule>();
using (SQLiteDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
UserModule user = new UserModule();
user.UserName = reader["name"].ToString();
user.RoleId = int.Parse(reader["Role_Id"].ToString());
user.RoleName = reader["Role_Name"].ToString();
user.ContractPerson = reader["Contract_Person"].ToString();
user.ContractTelPhone = reader["Contract_TelPhone"].ToString();
user.CompanyAddress = reader["Company_Address"].ToString();
users.Add(user);
}
reader.Close();
}
return(users);
case TableName.BasicData:
cmd.CommandText = $"SELECT * FROM {tableName.ToString()} WHERE 1=1";
if (para.Length > 0)
{
cmd.CommandText += $" AND level={(int)para[0]}";
}
using (SQLiteDataReader reader = cmd.ExecuteReader())
{
List <BasicDataModule> modules = new List <BasicDataModule>();
while (reader.Read())
{
BasicDataModule module = new BasicDataModule();
module.ID = int.Parse(reader["id"].ToString());
module.Name = reader["name"].ToString();
module.Level = int.Parse(reader["level"].ToString());
module.Grade = int.Parse(reader["grade"].ToString());
module.ParentId = int.Parse(reader["parent_id"].ToString());
modules.Add(module);
}
reader.Close();
return(modules);
}
case TableName.BasicFour:
cmd.CommandText = $"SELECT * FROM {tableName.ToString()}";
if (para.Length == 1)
{
cmd.CommandText += $" WHERE name='{para[0]}'";
}
using (SQLiteDataReader fourReader = cmd.ExecuteReader())
{
List <BasicFourModule> fourModules = new List <BasicFourModule>();
while (fourReader.Read())
{
BasicFourModule fourModule = new BasicFourModule();
fourModule.ID = int.Parse(fourReader["id"].ToString());
fourModule.Name = fourReader["name"].ToString();
fourModule.Level = int.Parse(fourReader["level"].ToString());
fourModule.ParentId = int.Parse(fourReader["parent_id"].ToString());
fourModule.BasicScore = int.Parse(fourReader["basic_score"].ToString());
fourModule.BasicRule = fourReader["basic_rule"].ToString();
fourModule.BasicAdd = fourReader["basic_sub"].ToString();
fourModule.BasicSub = fourReader["basic_add"].ToString();
string cals = fourReader["cal_module"].ToString();
string[] arrCals = cals.Split(",".ToArray());
fourModule.CalModules = new CalModule[arrCals.Length];
for (int i = 0; i < arrCals.Length; i++)
{
fourModule.CalModules[i] = (CalModule)int.Parse(arrCals[i]);
}
fourModule.StrCalModules = GetCalModuleStr(fourModule.CalModules);
fourModules.Add(fourModule);
}
fourReader.Close();
return(fourModules);
}
case TableName.TimeCycle:
cmd.CommandText = $"SELECT * FROM {tableName.ToString()} WHERE state={(int)para[0]}";
if (para.Length == 2)
{
cmd.CommandText += $" AND name='{(string)para[1]}'";
}
else if (para.Length == 3)
{
if (para[1] != null)
{
cmd.CommandText += $" AND name='{(string)para[1]}'";
}
cmd.CommandText += $" AND user_name='{(string)para[2]}'";
}
using (SQLiteDataReader timeReader = cmd.ExecuteReader())
{
List <TimeCycleModule> timeModules = new List <TimeCycleModule>();
while (timeReader.Read())
{
TimeCycleModule timeModule = new TimeCycleModule();
timeModule.ID = int.Parse(timeReader["id"].ToString());
timeModule.Name = timeReader["name"].ToString();
timeModule.StartTime = DateTime.Parse(timeReader["start_time"].ToString());
timeModule.EndTime = DateTime.Parse(timeReader["end_time"].ToString());
timeModule.CreateTime = DateTime.Parse(timeReader["create_time"].ToString());
timeModule.LatestCommitTime = DateTime.Parse(timeReader["latest_commit_time"].ToString());
timeModule.State = int.Parse(timeReader["state"].ToString());
timeModule.UserName = timeReader["user_name"].ToString();
timeModules.Add(timeModule);
}
timeReader.Close();
return(timeModules.OrderByDescending(p => p.StartTime).ToList());
}
case TableName.EvalutationData:
if ((int)para[0] == -1)
{
TimeCycleModule timeCycle = (TimeCycleModule)para[2];
cmd.CommandText = $"SELECT a.id, a.time_cycle, a.indicator_four, a.data_source, a.description, a.grade, b.name, b.parent_id, b.basic_score, b.basic_rule, b.basic_sub, b.basic_add, b.cal_module FROM EvalutationData as a left outer join BasicFour as b on a.indicator_four = b.id WHERE b.parent_id={(int)para[1]} AND a.time_cycle in (SELECT id FROM TimeCycle WHERE name='{timeCycle.Name}' AND start_time='{timeCycle.StartTime}' AND end_time='{timeCycle.EndTime}' AND state=1)";
}
else if ((int)para[0] == -2)
{
cmd.CommandText = $"SELECT a.id, a.time_cycle, a.indicator_four, a.data_source, a.description, a.grade, b.name, b.parent_id, b.basic_score, b.basic_rule, b.basic_sub, b.basic_add, b.cal_module FROM EvalutationData as a left outer join BasicFour as b on a.indicator_four = b.id WHERE b.parent_id={(int)para[1]} AND a.time_cycle={(int)para[2]}";
}
else
{
cmd.CommandText = $"SELECT a.id, a.time_cycle, a.indicator_four, a.data_source, a.description, a.grade, b.name, b.parent_id, b.basic_score, b.basic_rule, b.basic_sub, b.basic_add, b.cal_module FROM EvalutationData as a left outer join BasicFour as b on a.indicator_four = b.id WHERE time_cycle={(int)para[0]}";
}
using (SQLiteDataReader evalutationReader = cmd.ExecuteReader()) //reader is active exception
{
List <EvalutationDataModule> evalutationModules = new List <EvalutationDataModule>();
while (evalutationReader.Read())
{
EvalutationDataModule evalutationModule = new EvalutationDataModule();
evalutationModule.ID = int.Parse(evalutationReader["id"].ToString());
evalutationModule.TimeCycle = int.Parse(evalutationReader["time_cycle"].ToString());
evalutationModule.IndicatorFour = int.Parse(evalutationReader["indicator_four"].ToString());
evalutationModule.DataSource = evalutationReader["data_source"].ToString().Split("|".ToArray(), StringSplitOptions.RemoveEmptyEntries).ToArray();
evalutationModule.Description = evalutationReader["description"].ToString();
evalutationModule.Grade = int.Parse(evalutationReader["grade"].ToString());
evalutationModule.Name = evalutationReader["name"].ToString();
evalutationModule.ParentId = int.Parse(evalutationReader["parent_id"].ToString());
evalutationModule.BasicScore = int.Parse(evalutationReader["basic_score"].ToString());
evalutationModule.BasicRule = evalutationReader["basic_rule"].ToString();
evalutationModule.BasicAdd = evalutationReader["basic_sub"].ToString();
evalutationModule.BasicSub = evalutationReader["basic_add"].ToString();
string cals = evalutationReader["cal_module"].ToString();
string[] arrCals = cals.Split(",".ToArray());
evalutationModule.CalModules = new CalModule[arrCals.Length];
for (int i = 0; i < arrCals.Length; i++)
{
evalutationModule.CalModules[i] = (CalModule)int.Parse(arrCals[i]);
}
evalutationModule.StrCalModules = GetCalModuleStr(evalutationModule.CalModules);
evalutationModules.Add(evalutationModule);
}
evalutationReader.Close();
return(evalutationModules);
}
}
}
catch (Exception ex)
{
}
return(null);
}
/// <summary>
/// update data
/// </summary>
/// <param name="tableName"></param>
/// <param name="id"></param>
/// <param name="data"></param>
/// <returns></returns>
public static void Update(TableName tableName, int id, object data, out string msg)
{
msg = string.Empty;
switch (tableName)
{
case TableName.User:
UserModule user = (UserModule)data;
if (CheckRowDataUpdate(tableName, user.UserName, id))
{
msg = "单位名称已存在";
}
else
{
cmd.CommandText = $"UPDATE {tableName.ToString()} SET password='******',Role_Id={user.RoleId},Contract_Person='{user.ContractPerson}',Contract_TelPhone='{user.ContractTelPhone}',Company_Address='{user.CompanyAddress}' WHERE name='{user.UserName}'";
if (cmd.ExecuteNonQuery() <= 0)
{
msg = "更新失败";
}
}
break;
case TableName.BasicData:
BasicDataModule basicData = (BasicDataModule)data;
if (CheckRowDataUpdate(tableName, basicData.Name, id))
{
msg = "指标已存在";
}
else
{
cmd.CommandText = $"UPDATE {tableName.ToString()} SET name='{basicData.Name}', grade={basicData.Grade}, parent_id={basicData.ParentId} WHERE id={id}";
if (cmd.ExecuteNonQuery() <= 0)
{
msg = "更新失败";
}
}
break;
case TableName.BasicFour:
BasicFourModule fourData = (BasicFourModule)data;
if (CheckRowDataUpdate(tableName, fourData.Name, id))
{
msg = "指标已存在";
}
else
{
string strCalModule = string.Empty;
for (int i = 0; i < fourData.CalModules.Length; i++)
{
if (i == 0)
{
strCalModule += (int)fourData.CalModules[i];
}
else
{
strCalModule += "," + (int)fourData.CalModules[i];
}
}
cmd.CommandText = $"UPDATE {tableName.ToString()} SET name='{fourData.Name}', basic_score={fourData.BasicScore}, basic_rule='{fourData.BasicRule}', basic_sub='{fourData.BasicSub}', basic_add='{fourData.BasicAdd}', cal_module='{strCalModule}' WHERE id={id}";
if (cmd.ExecuteNonQuery() <= 0)
{
msg = "更新失败";
}
}
break;
case TableName.TimeCycle:
TimeCycleModule timeData = (TimeCycleModule)data;
if (CheckTimeData(tableName.ToString(), timeData, id))
{
msg = "评价阶段已存在";
}
else
{
cmd.CommandText = $"UPDATE {tableName.ToString()} SET name='{timeData.Name}', start_time='{timeData.StartTime}', end_time='{timeData.EndTime}', state={timeData.State} WHERE id={id}";
if (cmd.ExecuteNonQuery() <= 0)
{
msg = "更新失败";
}
}
break;
default:
break;
}
}
public static void ImportData(string path, out string msg)
{
msg = string.Empty;
try
{
FileStream fs = new FileStream(path, FileMode.Open, FileAccess.Read);
IWorkbook workbook = null;
if (Path.GetExtension(path).Equals(".xls"))
{
workbook = new HSSFWorkbook(fs);
}
else if (Path.GetExtension(path).Equals(".xlsx"))
{
workbook = new XSSFWorkbook(fs);
}
else
{
msg = "错误的文件类型";
return;
}
ISheet sheet = workbook.GetSheetAt(0);
if (sheet == null)
{
msg = "无可用工作簿";
return;
}
int rowCount = sheet.LastRowNum;
if (rowCount <= 6)
{
msg = "无有效评价数据";
return;
}
IRow row = null;
TimeCycleModule timeCycle = new TimeCycleModule();
int timeCycleId = -1;
List <EvalutationDataModule> evalutationData = new List <EvalutationDataModule>();
for (int i = 0; i <= rowCount; i++)
{
if (i == 0)//用户名
{
timeCycle.UserName = sheet.GetRow(i).GetCell(1).StringCellValue;
if (string.IsNullOrEmpty(timeCycle.UserName))
{
msg = "用户名为空";
return;
}
}
else if (i == 3)//评价阶段
{
row = sheet.GetRow(i);
timeCycle.Name = row.GetCell(0).StringCellValue;
timeCycle.StartTime = row.GetCell(1).DateCellValue;
timeCycle.EndTime = row.GetCell(2).DateCellValue;
timeCycle.CreateTime = row.GetCell(3).DateCellValue;
timeCycle.LatestCommitTime = row.GetCell(4).DateCellValue;
timeCycle.State = (int)TimeCycleState.Commit;
SqliteHelper.Insert(TableName.TimeCycle, timeCycle, out msg);
if (string.IsNullOrEmpty(msg))
{
timeCycleId = ((List <TimeCycleModule>)SqliteHelper.Select(TableName.TimeCycle, TimeCycleState.Commit, timeCycle.Name, timeCycle.UserName))[0].ID;
}
else
{
return;
}
}
else if (i > 6)
{
row = sheet.GetRow(i);
EvalutationDataModule evalutation = new EvalutationDataModule();
evalutation.TimeCycle = timeCycleId;
string currentFour = row.GetCell(3).StringCellValue;
currentFour = currentFour.Substring(0, currentFour.LastIndexOf("("));
List <BasicFourModule> fourModules = (List <BasicFourModule>)SqliteHelper.Select(TableName.BasicFour, currentFour);
if (fourModules.Count == 1)
{
evalutation.IndicatorFour = fourModules[0].ID;
}
else
{
msg = $"四级指标 {currentFour} 不存在";
return;
}
evalutation.Description = row.GetCell(7).StringCellValue;
evalutation.Grade = (int)row.GetCell(8).NumericCellValue;
evalutation.DataSource = row.GetCell(9).StringCellValue.Split("\r\n".ToArray(), StringSplitOptions.RemoveEmptyEntries);
evalutationData.Add(evalutation);
}
}
SqliteHelper.Insert(TableName.EvalutationData, evalutationData, out msg);
}
catch (Exception ex)
{
msg = "导入失败";
}
}