/// <summary>
/// initialize evalutation data
/// </summary>
/// <param name="id">time cycle id</param>
private void InitEvalutationData(int id)
{
List <BasicFourModule> fours = (List <BasicFourModule>)SqliteHelper.Select(TableName.BasicFour);
if (fours.Count == 0)
{
return;
}
List <EvalutationDataModule> datas = new List <EvalutationDataModule>();
foreach (var four in fours)
{
EvalutationDataModule dataModule = new EvalutationDataModule();
dataModule.TimeCycle = id;
dataModule.IndicatorFour = four.ID;
dataModule.DataSource = null;
dataModule.Description = string.Empty;
dataModule.Grade = 0;
datas.Add(dataModule);
}
if (datas.Count > 0)
{
SqliteHelper.Insert(TableName.EvalutationData, datas, out string msg);
if (!string.IsNullOrEmpty(msg))
{
MessageBox.Show(msg, "提示", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
}
}
/// <summary>
/// 更新备注展示内容
/// </summary>
/// <param name="evalutationData"></param>
private void RefreshRemark(EvalutationDataModule evalutationData)
{
ClearLabel();
lbl_tblNameData.Text = evalutationData.Name;
lbl_tblBasicData.Text = evalutationData.BasicRule;
lbl_tblSubData.Text = evalutationData.BasicSub;
lbl_tblAddData.Text = evalutationData.BasicAdd;
lbl_tblGradeData.Text = evalutationData.Grade.ToString();
lbl_tblDescriptionData.Text = evalutationData.Description;
CreateLabelLink(evalutationData.DataSource);
}
private void Init(EvalutationDataModule module, string user, string time)
{
lbl_user_data.Text = user;
lbl_time_data.Text = time;
lbl_four_data.Text = module.Name;
lbl_basicRule_data.Text = module.BasicRule;
lbl_add_data.Text = module.BasicAdd;
lbl_sub_data.Text = module.BasicSub;
lbl_strCalModules_data.Text = module.StrCalModules;
lbl_description_data.Text = module.Description;
lbl_grade_data.Text = module.Grade.ToString();
}
/// <summary>
/// 备注按钮点击事件
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void dataGridView1_CellContentClick(object sender, DataGridViewCellEventArgs e)
{
if (e.RowIndex == -1)
{
return;
}
if (((DataGridView)sender).CurrentCell.OwningColumn.Name == "Operate")
{
int id = (int)((DataGridView)sender).CurrentRow.Cells["ID"].Value;
EvalutationDataModule preModule = evalutationModules[id];
using (RemarkEdit dialog = new RemarkEdit(preModule))
{
if (dialog.ShowDialog() == DialogResult.OK)
{
evalutationModules[id] = dialog.CurrentModule;
RefreshRemark(dialog.CurrentModule);
}
}
}
}
/// <summary>
/// 单元格点击事件
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void dataGridView1_CellContentClick(object sender, DataGridViewCellEventArgs e)
{
if (e.RowIndex == -1)
{
return;
}
int columnIndex = ((DataGridView)sender).CurrentCell.ColumnIndex;
int count = ((DataGridView)sender).ColumnCount;
if (columnIndex == 0 || columnIndex >= count - 2)
{
return; //排除行头和最后两行
}
EvalutationDataModule currentData = (EvalutationDataModule)((DataGridView)sender).CurrentCell.Tag;
using (StatisticsDetails dialog = new StatisticsDetails(currentData, dataGridView1.Columns[columnIndex].HeaderText, currentTime.Name))
{
dialog.ShowDialog();
}
}
public RemarkEdit(EvalutationDataModule module)
{
InitializeComponent();
CurrentModule = module;
Init();
}
/// <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);
}
public StatisticsDetails(EvalutationDataModule module, string user, string time)
{
InitializeComponent();
Init(module, user, time);
}
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 = "导入失败";
}
}