/// <summary>
/// 根据ID获取子课题数
/// </summary>
/// <param name="tid">课题ID</param>
private int GetSubjectAmount(object tid) => SQLiteHelper.ExecuteCountQuery($"SELECT COUNT(si_id) FROM subject_info WHERE si_obj_id='{tid}'");
/// <summary>
/// 文件列表
/// </summary>
/// <param name="id">所属ID</param>
private void LoadFileList(object id, object parentObjName)
{
dgv_ShowData.Rows.Clear();
dgv_ShowData.Columns.Clear();
dgv_ShowData.Columns.AddRange(new DataGridViewColumn[]
{
new DataGridViewTextBoxColumn()
{
Name = "id", HeaderText = "序号", FillWeight = 3
},
new DataGridViewTextBoxColumn()
{
Name = "stage", HeaderText = "阶段", FillWeight = 5
},
new DataGridViewTextBoxColumn()
{
Name = "categor", HeaderText = "类别", FillWeight = 3
},
new DataGridViewTextBoxColumn()
{
Name = "name", HeaderText = "名称", FillWeight = 8
},
new DataGridViewTextBoxColumn()
{
Name = "user", HeaderText = "责任者", FillWeight = 3
},
new DataGridViewTextBoxColumn()
{
Name = "type", HeaderText = "类型", FillWeight = 3
},
new DataGridViewTextBoxColumn()
{
Name = "secret", HeaderText = "密级", FillWeight = 3
},
new DataGridViewTextBoxColumn()
{
Name = "pages", HeaderText = "页数", FillWeight = 3
},
new DataGridViewTextBoxColumn()
{
Name = "date", HeaderText = "形成日期", FillWeight = 5
},
new DataGridViewTextBoxColumn()
{
Name = "unit", HeaderText = "存放单位", FillWeight = 7
}
});
DataTable table = SQLiteHelper.ExecuteQuery($"SELECT * FROM files_info WHERE fi_obj_id='{id}'");
for (int i = 0; i < table.Rows.Count; i++)
{
int index = dgv_ShowData.Rows.Add();
dgv_ShowData.Rows[index].Tag = table.Rows[i]["fi_id"];
dgv_ShowData.Rows[index].Cells["id"].Value = i + 1;
dgv_ShowData.Rows[index].Cells["stage"].Value = SQLiteHelper.GetValueByKey(table.Rows[i]["fi_stage"]);
dgv_ShowData.Rows[index].Cells["categor"].Value = SQLiteHelper.GetValueByKey(table.Rows[i]["fi_categor"]);
dgv_ShowData.Rows[index].Cells["name"].Value = table.Rows[i]["fi_name"];
dgv_ShowData.Rows[index].Cells["user"].Value = table.Rows[i]["fi_user"];
dgv_ShowData.Rows[index].Cells["type"].Value = SQLiteHelper.GetValueByKey(table.Rows[i]["fi_type"]);
dgv_ShowData.Rows[index].Cells["secret"].Value = SQLiteHelper.GetValueByKey(table.Rows[i]["fi_secret"]);
dgv_ShowData.Rows[index].Cells["pages"].Value = table.Rows[i]["fi_pages"];
dgv_ShowData.Rows[index].Cells["date"].Value = GetDateTime(table.Rows[i]["fi_create_date"], "yyyy-MM-dd");
dgv_ShowData.Rows[index].Cells["unit"].Value = table.Rows[i]["fi_unit"];
}
dgv_ShowData.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.DisplayedCells;
dgv_ShowData.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.Fill;
DataGridViewStyleHelper.SetAlignWithCenter(dgv_ShowData, new string[] { "pages" });
btn_Query.Tag = "FILE";
lbl_position.Text += $" >> {parentObjName} >> 文件列表";
}
/// <summary>
/// 项目列表
/// </summary>
/// <param name="spid">专项ID</param>
private void LoadProjectList(object spid)
{
dgv_ShowData.Rows.Clear();
dgv_ShowData.Columns.Clear();
dgv_ShowData.Columns.AddRange(new DataGridViewColumn[]
{
new DataGridViewTextBoxColumn()
{
Name = "pro_id", HeaderText = "序号", FillWeight = 3
},
new DataGridViewTextBoxColumn()
{
Name = "pro_code", HeaderText = "编号", FillWeight = 8
},
new DataGridViewTextBoxColumn()
{
Name = "pro_name", HeaderText = "名称", FillWeight = 10
},
new DataGridViewTextBoxColumn()
{
Name = "pro_unit", HeaderText = "承担单位", FillWeight = 8
},
new DataGridViewTextBoxColumn()
{
Name = "pro_user", HeaderText = "负责人", FillWeight = 5
},
new DataGridViewTextBoxColumn()
{
Name = "pro_phone", HeaderText = "手机", FillWeight = 4
},
new DataGridViewLinkColumn()
{
Name = "pro_totalFileAmount", HeaderText = "总文件数", FillWeight = 3
},
new DataGridViewLinkColumn()
{
Name = "pro_lostFileAmount", HeaderText = "必备文件缺失数", FillWeight = 5
},
new DataGridViewLinkColumn()
{
Name = "pro_topicAmount", HeaderText = "课题/子课题数", FillWeight = 4
},
});
DataTable projectTable = SQLiteHelper.ExecuteQuery($"SELECT * FROM project_info WHERE pi_obj_id='{spid}'");
for (int i = 0; i < projectTable.Rows.Count; i++)
{
int index = dgv_ShowData.Rows.Add();
dgv_ShowData.Rows[index].Tag = projectTable.Rows[i]["pi_id"];
dgv_ShowData.Rows[index].Cells["pro_id"].Value = index + 1;
dgv_ShowData.Rows[index].Cells["pro_code"].Value = projectTable.Rows[i]["pi_code"];
dgv_ShowData.Rows[index].Cells["pro_name"].Value = projectTable.Rows[i]["pi_name"];
dgv_ShowData.Rows[index].Cells["pro_unit"].Value = projectTable.Rows[i]["pi_unit"];
dgv_ShowData.Rows[index].Cells["pro_user"].Value = projectTable.Rows[i]["pi_contacts"];
dgv_ShowData.Rows[index].Cells["pro_phone"].Value = projectTable.Rows[i]["pi_contacts_phone"];
dgv_ShowData.Rows[index].Cells["pro_totalFileAmount"].Value = GetFileAmount(projectTable.Rows[i]["pi_id"]);
dgv_ShowData.Rows[index].Cells["pro_lostFileAmount"].Value = GetLostFileAmount(projectTable.Rows[i]["pi_id"]);
dgv_ShowData.Rows[index].Cells["pro_topicAmount"].Value = GetTopicAmount(projectTable.Rows[i]["pi_id"]);
}
DataTable topicTable = SQLiteHelper.ExecuteQuery($"SELECT * FROM topic_info WHERE ti_obj_id='{spid}'");
for (int i = 0; i < topicTable.Rows.Count; i++)
{
int index = dgv_ShowData.Rows.Add();
dgv_ShowData.Rows[index].Tag = topicTable.Rows[i]["ti_id"];
dgv_ShowData.Rows[index].Cells["pro_id"].Value = index + 1;
dgv_ShowData.Rows[index].Cells["pro_code"].Value = topicTable.Rows[i]["ti_code"];
dgv_ShowData.Rows[index].Cells["pro_name"].Value = topicTable.Rows[i]["ti_name"];
dgv_ShowData.Rows[index].Cells["pro_unit"].Value = topicTable.Rows[i]["ti_unit"];
dgv_ShowData.Rows[index].Cells["pro_user"].Value = topicTable.Rows[i]["ti_contacts"];
dgv_ShowData.Rows[index].Cells["pro_phone"].Value = topicTable.Rows[i]["ti_contacts_phone"];
dgv_ShowData.Rows[index].Cells["pro_totalFileAmount"].Value = GetFileAmount(topicTable.Rows[i]["ti_id"]);
dgv_ShowData.Rows[index].Cells["pro_lostFileAmount"].Value = GetLostFileAmount(topicTable.Rows[i]["ti_id"]);
dgv_ShowData.Rows[index].Cells["pro_topicAmount"].Value = GetSubjectAmount(topicTable.Rows[i]["ti_id"]);
}
dgv_ShowData.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.DisplayedCells;
dgv_ShowData.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.Fill;
DataGridViewStyleHelper.SetAlignWithCenter(dgv_ShowData, new string[] { "pro_id", "pro_totalFileAmount", "pro_topicAmount", "pro_lostFileAmount" });
btn_Query.Tag = "PROJECT";
lbl_position.Text = $"{UserHelper.GetUser().SpecialName}";
}
/// <summary>
/// 课题列表
/// </summary>
/// <param name="pid">项目ID</param>
private bool LoadTopicList(object pid, object parentObjName)
{
dgv_ShowData.Rows.Clear();
dgv_ShowData.Columns.Clear();
dgv_ShowData.Columns.AddRange(new DataGridViewColumn[]
{
new DataGridViewTextBoxColumn()
{
Name = "top_id", HeaderText = "序号", FillWeight = 3
},
new DataGridViewTextBoxColumn()
{
Name = "top_code", HeaderText = "编号", FillWeight = 8
},
new DataGridViewTextBoxColumn()
{
Name = "top_name", HeaderText = "名称", FillWeight = 10
},
new DataGridViewTextBoxColumn()
{
Name = "top_unit", HeaderText = "承担单位", FillWeight = 8
},
new DataGridViewTextBoxColumn()
{
Name = "top_user", HeaderText = "负责人", FillWeight = 5
},
new DataGridViewTextBoxColumn()
{
Name = "top_phone", HeaderText = "手机", FillWeight = 4
},
new DataGridViewLinkColumn()
{
Name = "top_totalFileAmount", HeaderText = "总文件数", FillWeight = 3
},
new DataGridViewLinkColumn()
{
Name = "top_lostFileAmount", HeaderText = "必备文件缺失数", FillWeight = 5
},
new DataGridViewLinkColumn()
{
Name = "top_subjectAmount", HeaderText = "子课题数", FillWeight = 4
},
});
DataTable topicTable = SQLiteHelper.ExecuteQuery($"SELECT * FROM topic_info WHERE ti_obj_id='{pid}'");
for (int i = 0; i < topicTable.Rows.Count; i++)
{
int index = dgv_ShowData.Rows.Add();
dgv_ShowData.Rows[index].Tag = topicTable.Rows[i]["ti_id"];
dgv_ShowData.Rows[index].Cells["top_id"].Value = i + 1;
dgv_ShowData.Rows[index].Cells["top_code"].Value = topicTable.Rows[i]["ti_code"];
dgv_ShowData.Rows[index].Cells["top_name"].Value = topicTable.Rows[i]["ti_name"];
dgv_ShowData.Rows[index].Cells["top_unit"].Value = topicTable.Rows[i]["ti_unit"];
dgv_ShowData.Rows[index].Cells["top_user"].Value = topicTable.Rows[i]["ti_contacts"];
dgv_ShowData.Rows[index].Cells["top_phone"].Value = topicTable.Rows[i]["ti_contacts_phone"];
dgv_ShowData.Rows[index].Cells["top_totalFileAmount"].Value = GetFileAmount(topicTable.Rows[i]["ti_id"]);
dgv_ShowData.Rows[index].Cells["top_lostFileAmount"].Value = GetLostFileAmount(topicTable.Rows[i]["ti_id"]);
dgv_ShowData.Rows[index].Cells["top_subjectAmount"].Value = GetSubjectAmount(topicTable.Rows[i]["ti_id"]);
}
dgv_ShowData.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.DisplayedCells;
dgv_ShowData.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.Fill;
DataGridViewStyleHelper.SetAlignWithCenter(dgv_ShowData, new string[] { "top_id", "top_totalFileAmount", "top_subjectAmount", "top_lostFileAmount" });
btn_Query.Tag = "TOPIC";
lbl_position.Text = parentObjName != null ? $"{UserHelper.GetUser().SpecialName} >> {parentObjName}" : lbl_position.Text;
return(topicTable.Rows.Count == 0 ? false : true);
}
/// <summary>
/// 获取指定课题下的文件数
/// </summary>
/// <param name="pid">指定ID</param>
private int GetFileAmount(object pid) => SQLiteHelper.ExecuteCountQuery($"SELECT COUNT(fi_id) FROM files_info WHERE fi_obj_id='{pid}'");
/// <summary>
/// 获取指定课题下的子课题数
/// </summary>
/// <param name="pid">指定ID</param>
private int GetTopicAmount(object pid) => SQLiteHelper.ExecuteCountQuery($"SELECT COUNT(ti_id) FROM topic_info WHERE ti_obj_id='{pid}'");
/// <summary>
/// 获取指定ID下缺失文件数
/// </summary>
private int GetLostFileAmount(object id)
{
return(SQLiteHelper.ExecuteCountQuery("SELECT COUNT(name) FROM (SELECT dd_name name FROM data_dictionary WHERE dd_pId in (" +
"SELECT dd_id FROM data_dictionary WHERE dd_pId = (SELECT dd_id FROM data_dictionary WHERE dd_code = 'dic_file_jd')) AND extend_2=1 " +
$"AND name NOT IN(SELECT dd.dd_name FROM files_info fi LEFT JOIN data_dictionary dd ON fi.fi_categor = dd.dd_id WHERE fi.fi_obj_id= '{id}'))"));
}
/// <summary>
/// 根据编码获取其下的数据表格
/// </summary>
/// <param name="parentCode"></param>
/// <returns></returns>
public static DataTable GetTableByCode(object parentCode)
{
object parentId = SQLiteHelper.ExecuteOnlyOneQuery($"SELECT dd_id FROM data_dictionary WHERE dd_code = '{parentCode}'");
return(SQLiteHelper.ExecuteQuery($"SELECT * FROM data_dictionary WHERE dd_pId='{parentId}' ORDER BY dd_sort"));
}
/// <summary>
/// 根据编码获取其下的数据的id和name属性
/// </summary>
/// <param name="parentCode">父节点Code</param>
public static List <object[]> GetValuesByCode(object parentCode)
{
object parentId = SQLiteHelper.ExecuteOnlyOneQuery($"SELECT dd_id FROM data_dictionary WHERE dd_code = '{parentCode}'");
return(SQLiteHelper.ExecuteColumnsQuery($"SELECT dd_id, dd_name FROM data_dictionary WHERE dd_pId='{parentId}' ORDER BY dd_sort", 2));
}
