/// <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)); }