private void LoadFileInfo(object pid)
{
DataTable table = SQLiteHelper.ExecuteQuery($"SELECT * FROM files_info WHERE fi_obj_id='{pid}'");
list.AddRange(GetArray(table));
}
private void btn_Export_Click(object sender, EventArgs e)
{
object KEY = "SAVE_PATH";
object value = SQLiteHelper.ExecuteOnlyOneQuery($"SELECT dd_name FROM data_dictionary WHERE dd_code='{KEY}'");
if (value != null)
{
/* ----复制文件----*/
pro_Show.Maximum = GetTotalFileAmountBySpiId(UserHelper.GetUser().UserSpecialId);
int count = pro_Show.Maximum, okcount = 0, nocount = 0;
string rootFolder = value + "\\" + SQLiteHelper.ExecuteOnlyOneQuery($"SELECT spi_name FROM special_info WHERE spi_id='{UserHelper.GetUser().UserSpecialId}'");
if (!Directory.Exists(rootFolder))
{
Directory.CreateDirectory(rootFolder);
}
//专项下的文件
CopyFile(ref okcount, ref nocount, rootFolder, GetFileLinkByObjId(UserHelper.GetUser().UserSpecialId));
//专项下的项目
List <object[]> list2 = SQLiteHelper.ExecuteColumnsQuery($"SELECT pi_id, pi_name FROM project_info WHERE pi_obj_id='{UserHelper.GetUser().UserSpecialId}'", 2);
for (int i = 0; i < list2.Count; i++)
{
string _rootFolder = rootFolder + "\\" + list2[i][1];
if (!Directory.Exists(_rootFolder))
{
Directory.CreateDirectory(_rootFolder);
}
//项目下的文件
CopyFile(ref okcount, ref nocount, _rootFolder, GetFileLinkByObjId(list2[i][0]));
//项目下的课题
List <object[]> list5 = SQLiteHelper.ExecuteColumnsQuery($"SELECT ti_id, ti_name FROM topic_info WHERE ti_obj_id='{list2[i][0]}'", 2);
for (int j = 0; j < list5.Count; j++)
{
string _rootFolder2 = _rootFolder + "\\" + list5[j][1];
if (!Directory.Exists(_rootFolder2))
{
Directory.CreateDirectory(_rootFolder2);
}
//课题下的文件
CopyFile(ref okcount, ref nocount, _rootFolder2, GetFileLinkByObjId(list5[j][0]));
//课题下的子课题
List <object[]> list6 = SQLiteHelper.ExecuteColumnsQuery($"SELECT si_id, si_name FROM subject_info WHERE si_obj_id='{list5[j][0]}'", 2);
for (int k = 0; k < list6.Count; k++)
{
string _rootFolder3 = _rootFolder2 + "\\" + list6[k][1];
if (!Directory.Exists(_rootFolder3))
{
Directory.CreateDirectory(_rootFolder3);
}
CopyFile(ref okcount, ref nocount, _rootFolder3, GetFileLinkByObjId(list6[k][0]));
}
}
//项目下的子课题
List <object[]> list7 = SQLiteHelper.ExecuteColumnsQuery($"SELECT si_id, si_name FROM subject_info WHERE si_obj_id='{list2[i][0]}'", 2);
for (int j = 0; j < list7.Count; j++)
{
string _rootFolder2 = _rootFolder + "\\" + list7[j][1];
if (!Directory.Exists(_rootFolder2))
{
Directory.CreateDirectory(_rootFolder2);
}
CopyFile(ref okcount, ref nocount, _rootFolder2, GetFileLinkByObjId(list7[j][0]));
}
}
//专项下的课题
List <object[]> list4 = SQLiteHelper.ExecuteColumnsQuery($"SELECT ti_id, ti_name FROM topic_info WHERE ti_obj_id='{UserHelper.GetUser().UserSpecialId}'", 2);
for (int i = 0; i < list4.Count; i++)
{
string _rootFolder = rootFolder + "\\" + list4[i][1];
if (!Directory.Exists(_rootFolder))
{
Directory.CreateDirectory(_rootFolder);
}
//课题下的文件
CopyFile(ref okcount, ref nocount, _rootFolder, GetFileLinkByObjId(list4[i][0]));
//课题下的子课题
List <object[]> list6 = SQLiteHelper.ExecuteColumnsQuery($"SELECT si_id, si_name FROM subject_info WHERE si_obj_id='{list4[i][0]}'", 2);
for (int k = 0; k < list6.Count; k++)
{
string _rootFolder3 = _rootFolder + "\\" + list6[k][1];
if (!Directory.Exists(_rootFolder3))
{
Directory.CreateDirectory(_rootFolder3);
}
CopyFile(ref okcount, ref nocount, _rootFolder3, GetFileLinkByObjId(list6[k][0]));
}
}
MessageBox.Show($"备份完成，共计{count}个文件，成功{okcount}个，失败{nocount}个。", "操作成功", MessageBoxButtons.OK, MessageBoxIcon.Asterisk);
/* ----合成文件清单----*/
string filePath = value + "\\重大专项项目（课题）档案交接清单";
MicrosoftWordHelper.WriteDocument(filePath, list);
if (MessageBox.Show("文件清单合成完毕, 是否需要现在打开?", "温馨提示", MessageBoxButtons.YesNo, MessageBoxIcon.Asterisk) == DialogResult.Yes)
{
LoadDocumnet(filePath);
}
Close();
}
else
{
MessageBox.Show("尚未指定文件存放路径。", "提示", MessageBoxButtons.OK, MessageBoxIcon.Asterisk);
}
}
private int GetCount(object objId) => SQLiteHelper.ExecuteCountQuery($"SELECT COUNT(fi_id) FROM files_info WHERE fi_obj_id='{objId}' AND fi_link IS NOT NULL");
private static List <object[]> GetFileLinkByObjId(object objId) => SQLiteHelper.ExecuteColumnsQuery($"SELECT fi_link FROM files_info WHERE fi_obj_id='{objId}'", 1);
private void NodeMouseClick(object sender, TreeNodeMouseClickEventArgs e)
{
if (e.Button == MouseButtons.Left)
{
if (e.Node.Tag != null)
{
dgv_DataList.Rows.Clear();
object objId = e.Node.Name;
ControlType type = (ControlType)e.Node.Tag;
if (type == ControlType.Plan)
{
int rowNumber = 1;
DataTable projectTable = SQLiteHelper.ExecuteQuery($"SELECT * FROM project_info WHERE pi_obj_id='{e.Node.Name}'");
for (int i = 0; i < projectTable.Rows.Count; i++)
{
int rid = dgv_DataList.Rows.Add();
dgv_DataList.Rows[rid].Tag = ControlType.Plan_Project;
dgv_DataList.Rows[rid].Cells["id"].Tag = projectTable.Rows[i]["pi_id"];
dgv_DataList.Rows[rid].Cells["id"].Value = rowNumber++;
dgv_DataList.Rows[rid].Cells["code"].Value = projectTable.Rows[i]["pi_code"];
dgv_DataList.Rows[rid].Cells["name"].Value = projectTable.Rows[i]["pi_name"];
dgv_DataList.Rows[rid].Cells["unit"].Value = projectTable.Rows[i]["pi_unit"];
dgv_DataList.Rows[rid].Cells["user"].Value = projectTable.Rows[i]["pi_unit_user"];
dgv_DataList.Rows[rid].Cells["phone"].Value = projectTable.Rows[i]["pi_contacts_phone"];
dgv_DataList.Rows[rid].Cells["files"].Value = GetFileAmount(projectTable.Rows[i]["pi_id"], true);
dgv_DataList.Rows[rid].Cells["eles"].Value = GetFileAmount(projectTable.Rows[i]["pi_id"], false);
}
DataTable topicTable = SQLiteHelper.ExecuteQuery($"SELECT * FROM topic_info WHERE ti_obj_id='{e.Node.Name}'");
for (int i = 0; i < topicTable.Rows.Count; i++)
{
int rid = dgv_DataList.Rows.Add();
dgv_DataList.Rows[rid].Tag = ControlType.Plan_Topic;
dgv_DataList.Rows[rid].Cells["id"].Tag = topicTable.Rows[i]["ti_id"];
dgv_DataList.Rows[rid].Cells["id"].Value = rowNumber++;
dgv_DataList.Rows[rid].Cells["code"].Value = topicTable.Rows[i]["ti_code"];
dgv_DataList.Rows[rid].Cells["name"].Value = topicTable.Rows[i]["ti_name"];
dgv_DataList.Rows[rid].Cells["unit"].Value = topicTable.Rows[i]["ti_unit"];
dgv_DataList.Rows[rid].Cells["user"].Value = topicTable.Rows[i]["ti_unit_user"];
dgv_DataList.Rows[rid].Cells["phone"].Value = topicTable.Rows[i]["ti_contacts_phone"];
dgv_DataList.Rows[rid].Cells["files"].Value = GetFileAmount(topicTable.Rows[i]["ti_id"], true);
dgv_DataList.Rows[rid].Cells["eles"].Value = GetFileAmount(topicTable.Rows[i]["ti_id"], false);
}
}
else if (type == ControlType.Plan_Project)
{
DataTable subjectTable = SQLiteHelper.ExecuteQuery($"SELECT * FROM subject_info WHERE si_obj_id='{e.Node.Name}'");
int rowNumber = 1;
for (int i = 0; i < subjectTable.Rows.Count; i++)
{
int rid = dgv_DataList.Rows.Add();
dgv_DataList.Rows[rid].Tag = ControlType.Plan_Topic_Subject;
dgv_DataList.Rows[rid].Cells["id"].Tag = subjectTable.Rows[i]["si_id"];
dgv_DataList.Rows[rid].Cells["id"].Value = rowNumber++;
dgv_DataList.Rows[rid].Cells["code"].Value = subjectTable.Rows[i]["si_code"];
dgv_DataList.Rows[rid].Cells["name"].Value = subjectTable.Rows[i]["si_name"];
dgv_DataList.Rows[rid].Cells["unit"].Value = subjectTable.Rows[i]["si_unit"];
dgv_DataList.Rows[rid].Cells["user"].Value = subjectTable.Rows[i]["si_unit_user"];
dgv_DataList.Rows[rid].Cells["phone"].Value = subjectTable.Rows[i]["si_contacts_phone"];
dgv_DataList.Rows[rid].Cells["files"].Value = GetFileAmount(subjectTable.Rows[i]["si_id"], true);
dgv_DataList.Rows[rid].Cells["eles"].Value = GetFileAmount(subjectTable.Rows[i]["si_id"], false);
}
DataTable topicTable = SQLiteHelper.ExecuteQuery($"SELECT * FROM topic_info WHERE ti_obj_id='{e.Node.Name}'");
for (int i = 0; i < topicTable.Rows.Count; i++)
{
int rid = dgv_DataList.Rows.Add();
dgv_DataList.Rows[rid].Tag = ControlType.Plan_Topic;
dgv_DataList.Rows[rid].Cells["id"].Tag = topicTable.Rows[i]["ti_id"];
dgv_DataList.Rows[rid].Cells["id"].Value = rowNumber++;
dgv_DataList.Rows[rid].Cells["code"].Value = topicTable.Rows[i]["ti_code"];
dgv_DataList.Rows[rid].Cells["name"].Value = topicTable.Rows[i]["ti_name"];
dgv_DataList.Rows[rid].Cells["unit"].Value = topicTable.Rows[i]["ti_unit"];
dgv_DataList.Rows[rid].Cells["user"].Value = topicTable.Rows[i]["ti_unit_user"];
dgv_DataList.Rows[rid].Cells["phone"].Value = topicTable.Rows[i]["ti_contacts_phone"];
dgv_DataList.Rows[rid].Cells["files"].Value = GetFileAmount(topicTable.Rows[i]["ti_id"], true);
dgv_DataList.Rows[rid].Cells["eles"].Value = GetFileAmount(topicTable.Rows[i]["ti_id"], false);
}
}
else if (type == ControlType.Plan_Topic)
{
DataTable subjectTable = SQLiteHelper.ExecuteQuery($"SELECT * FROM subject_info WHERE si_obj_id='{e.Node.Name}'");
for (int i = 0; i < subjectTable.Rows.Count; i++)
{
int rid = dgv_DataList.Rows.Add();
dgv_DataList.Rows[rid].Tag = ControlType.Plan_Topic_Subject;
dgv_DataList.Rows[rid].Cells["id"].Tag = subjectTable.Rows[i]["si_id"];
dgv_DataList.Rows[rid].Cells["id"].Value = i + 1;
dgv_DataList.Rows[rid].Cells["code"].Value = subjectTable.Rows[i]["si_code"];
dgv_DataList.Rows[rid].Cells["name"].Value = subjectTable.Rows[i]["si_name"];
dgv_DataList.Rows[rid].Cells["unit"].Value = subjectTable.Rows[i]["si_unit"];
dgv_DataList.Rows[rid].Cells["user"].Value = subjectTable.Rows[i]["si_unit_user"];
dgv_DataList.Rows[rid].Cells["phone"].Value = subjectTable.Rows[i]["si_contacts_phone"];
dgv_DataList.Rows[rid].Cells["files"].Value = GetFileAmount(subjectTable.Rows[i]["si_id"], true);
dgv_DataList.Rows[rid].Cells["eles"].Value = GetFileAmount(subjectTable.Rows[i]["si_id"], false);
}
}
}
}
else if (e.Button == MouseButtons.Right)
{
TreeNode node = tv_DataTree.GetNodeAt(e.X, e.Y);
if (node != null && node.Tag != null)
{
tv_DataTree.SelectedNode = node;
cms_TreeView.Show(MousePosition);
ControlType type = (ControlType)node.Tag;
if (type == ControlType.Plan)
{
tsm_Project.Visible = true;
tsm_Topic.Visible = true;
tsm_Subject.Visible = false;
tsm_Add.Visible = true;
}
else if (type == ControlType.Plan_Project)
{
tsm_Project.Visible = false;
tsm_Topic.Visible = true;
tsm_Subject.Visible = true;
tsm_Add.Visible = true;
}
else if (type == ControlType.Plan_Topic)
{
tsm_Project.Visible = false;
tsm_Topic.Visible = false;
tsm_Subject.Visible = true;
tsm_Add.Visible = true;
}
else if (type == ControlType.Plan_Topic_Subject)
{
cms_TreeView.Close();
}
}
}
}
/// <summary>
/// 加载目录树
/// </summary>
/// <param name="specialId">专项ID</param>
public void LoadTreeList(object specialId)
{
tv_DataTree.Nodes[0].Nodes.Clear();
TreeNode rootNode = null;
//【计划】
DataRow row = SQLiteHelper.ExecuteSingleRowQuery($"SELECT spi_id, spi_code, spi_name FROM special_info WHERE spi_id='{specialId ?? rootId}'");
if (row != null)
{
rootNode = new TreeNode()
{
Name = GetValue(row["spi_id"]),
Text = GetValue(row["spi_name"]),
Tag = ControlType.Plan
};
//【项目】
DataTable projectTable = SQLiteHelper.ExecuteQuery($"SELECT pi_id, pi_code, pi_name FROM project_info WHERE pi_obj_id='{rootNode.Name}' GROUP BY pi_code");
for (int i = 0; i < projectTable.Rows.Count; i++)
{
TreeNode treeNode = new TreeNode()
{
Name = GetValue(projectTable.Rows[i]["pi_id"]),
Text = GetValue(projectTable.Rows[i]["pi_code"]),
Tag = ControlType.Plan_Project
};
rootNode.Nodes.Add(treeNode);
//【课题】
DataTable table2 = SQLiteHelper.ExecuteQuery($"SELECT ti_id, ti_code, ti_name FROM topic_info WHERE ti_obj_id='{treeNode.Name}' GROUP BY ti_code");
for (int j = 0; j < table2.Rows.Count; j++)
{
TreeNode treeNode2 = new TreeNode()
{
Name = GetValue(table2.Rows[j]["ti_id"]),
Text = GetValue(table2.Rows[j]["ti_code"]),
Tag = ControlType.Plan_Topic
};
treeNode.Nodes.Add(treeNode2);
//【子课题】
DataTable table3 = SQLiteHelper.ExecuteQuery($"SELECT si_id, si_code, si_name FROM subject_info WHERE si_obj_id='{treeNode2.Name}' GROUP BY si_code");
for (int k = 0; k < table3.Rows.Count; k++)
{
treeNode2.Nodes.Add(new TreeNode()
{
Name = GetValue(table3.Rows[k]["si_id"]),
Text = GetValue(table3.Rows[k]["si_code"]),
Tag = ControlType.Plan_Topic_Subject
});
}
}
//【子课题】
DataTable table4 = SQLiteHelper.ExecuteQuery($"SELECT si_id, si_code, si_name FROM subject_info WHERE si_obj_id='{treeNode.Name}' GROUP BY si_code");
for (int k = 0; k < table4.Rows.Count; k++)
{
treeNode.Nodes.Add(new TreeNode()
{
Name = GetValue(table4.Rows[k]["si_id"]),
Text = GetValue(table4.Rows[k]["si_code"]),
Tag = ControlType.Plan_Topic_Subject
});
}
}
//【课题】
DataTable topicTable = SQLiteHelper.ExecuteQuery($"SELECT ti_id, ti_code, ti_name FROM topic_info WHERE ti_obj_id='{rootNode.Name}' GROUP BY ti_code");
for (int j = 0; j < topicTable.Rows.Count; j++)
{
TreeNode treeNode = new TreeNode()
{
Name = GetValue(topicTable.Rows[j]["ti_id"]),
Text = GetValue(topicTable.Rows[j]["ti_code"]),
Tag = ControlType.Plan_Topic
};
rootNode.Nodes.Add(treeNode);
//【子课题】
DataTable table3 = SQLiteHelper.ExecuteQuery($"SELECT si_id, si_code, si_name FROM subject_info WHERE si_obj_id='{treeNode.Name}' GROUP BY si_code");
for (int k = 0; k < table3.Rows.Count; k++)
{
treeNode.Nodes.Add(new TreeNode()
{
Name = GetValue(table3.Rows[k]["si_id"]),
Text = GetValue(table3.Rows[k]["si_code"]),
Tag = ControlType.Plan_Topic_Subject
});
}
}
tv_DataTree.Nodes[0].Nodes.Add(rootNode);
}
tv_DataTree.ExpandAll();
}
private void btn_Back_Click(object sender, EventArgs e)
{
key = lastKey;
lastKey = SQLiteHelper.ExecuteOnlyOneQuery($"SELECT dd_pId FROM data_dictionary WHERE dd_id='{key}'") ?? lastKey;
LoadDataListInstince(key);
}
