/// <summary> /// 根据类别加载文件名称 /// </summary> private void LoadFileNameByCategor(ComboBox comboBox) { string _tempKey = comboBox.Text.Split(' ')[0]; if (string.IsNullOrEmpty(_tempKey)) { string _tempKeyObj = GetValue(((DataRowView)comboBox.Items[comboBox.SelectedIndex]).Row.ItemArray[1]); if (!string.IsNullOrEmpty(_tempKeyObj)) { _tempKey = _tempKeyObj.Split(' ')[0]; } } object key = _tempKey; object value = comboBox.SelectedValue; object[] fileName = SQLiteHelper.ExecuteSingleColumnQuery($"SELECT fi_name FROM files_info WHERE fi_categor='{value}' AND fi_obj_id='{parentId}'"); txt_fileName.Items.Clear(); txt_fileName.Items.AddRange(fileName); txt_fileName.Text = GetValue(SQLiteHelper.ExecuteOnlyOneQuery($"SELECT dd_note FROM data_dictionary WHERE dd_id='{value}'")); int amount = SQLiteHelper.ExecuteCountQuery($"SELECT COUNT(fi_id) FROM files_info WHERE fi_categor='{value}' AND fi_obj_id='{parentId}'"); int _amount = comboBox.Items.Count; if (comboBox.SelectedIndex == _amount - 1) { string tempKey = ((DataRowView)comboBox.Items[0]).Row.ItemArray[1].ToString(); string _key = GetValue(tempKey).Substring(0, 1) + _amount.ToString().PadLeft(2, '0'); txt_fileCode.Text = _key + "-" + (amount + 1).ToString().PadLeft(2, '0'); } else { txt_fileCode.Text = key + "-" + (amount + 1).ToString().PadLeft(2, '0'); } }
/// <summary> /// 获取文件数/页数 /// </summary> /// <param name="boxId">盒ID</param> /// <param name="type">获取类型 /// <para>1:文件数</para> /// <para>2:页数</para> /// </param> private int GetFilePageCount(object boxId, int type) { if (type == 1) { return(SQLiteHelper.ExecuteCountQuery($"SELECT COUNT(fi_id) FROM files_info WHERE fi_box_id='{boxId}'")); } else { return(SQLiteHelper.ExecuteCountQuery($"SELECT SUM(fi_pages) FROM files_info WHERE fi_box_id='{boxId}'")); } }
private void LoadStateTip() { int totalFileAmount = SQLiteHelper.ExecuteCountQuery($"SELECT COUNT(bfi_id) FROM backup_files_info WHERE bfi_userid='{UserHelper.GetUser().UserId}' AND bfi_type=0"); int workedFileAmount = SQLiteHelper.ExecuteCountQuery($"SELECT COUNT(bfi_id) FROM backup_files_info WHERE bfi_userid='{UserHelper.GetUser().UserId}' AND bfi_state=1 AND bfi_type=0"); int disWorkedFileAmount = SQLiteHelper.ExecuteCountQuery($"SELECT COUNT(bfi_id) FROM backup_files_info WHERE bfi_userid='{UserHelper.GetUser().UserId}' AND bfi_state=0 AND bfi_type=0"); int gdFileAmount = SQLiteHelper.ExecuteCountQuery($"SELECT COUNT(bfi_id) FROM backup_files_info WHERE bfi_userid='{UserHelper.GetUser().UserId}' AND bfi_state_gd=1 AND bfi_type=0"); int disGdFileAmount = SQLiteHelper.ExecuteCountQuery($"SELECT COUNT(bfi_id) FROM backup_files_info WHERE bfi_userid='{UserHelper.GetUser().UserId}' AND bfi_state_gd=0 AND bfi_type=0"); string tipString = $"总文件数:{totalFileAmount},已处理:{workedFileAmount},未处理:{disWorkedFileAmount},已归档:{gdFileAmount},未归档:{disGdFileAmount}"; stateTip.Text = "当前加工统计:" + tipString; }
private void LoadParentInfo(object key, object id) { txt_Pname.Tag = key; txt_Pname.Text = GetValue(SQLiteHelper.ExecuteOnlyOneQuery($"SELECT dd_name FROM data_dictionary WHERE dd_id='{key}'")); txt_Sort.Value = SQLiteHelper.ExecuteCountQuery($"SELECT COUNT(*) FROM data_dictionary WHERE dd_pId='{key}'"); if (id != null) { object[] obj = SQLiteHelper.ExecuteRowsQuery($"SELECT dd_name, dd_note, dd_sort, dd_code FROM data_dictionary WHERE dd_id='{id}'"); txt_name.Tag = id; txt_name.Text = GetValue(obj[0]); txt_Intro.Text = GetValue(obj[1]); txt_Sort.Value = GetIntValue(obj[2]); txt_code.Text = GetValue(obj[3]); } }
private bool HasChild(ControlType type, object id) { int index = 0; if (type == ControlType.Plan_Project) { index = SQLiteHelper.ExecuteCountQuery($"SELECT COUNT(ti_id) FROM topic_info WHERE ti_obj_id='{id}'"); if (index == 0) { index = SQLiteHelper.ExecuteCountQuery($"SELECT COUNT(si_id) FROM subject_info WHERE si_obj_id='{id}'"); } } else if (type == ControlType.Plan_Topic || type == ControlType.Topic) { index = index = SQLiteHelper.ExecuteCountQuery($"SELECT COUNT(si_id) FROM subject_info WHERE si_obj_id='{id}'"); } return(index == 0 ? false : true); }
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");
/// <summary> /// 复制文件 /// </summary> private void CopyDataTableInstince(string dataBasePath, string rootFolder) { 数据采集档案管理系统___加工版.Tools.SQLiteBackupHelper helper = new 数据采集档案管理系统___加工版.Tools.SQLiteBackupHelper(dataBasePath); DataTable projectTable = helper.ExecuteQuery($"SELECT * FROM project_info"); int length = projectTable.Rows.Count; StringBuilder sqlString = new StringBuilder(); for (int i = 0; i < length; i++) { SetTip($"正在导入项目数据({i + 1}\\{length})"); DataRow row = projectTable.Rows[i]; sqlString.Append($"DELETE FROM project_info WHERE pi_id='{row["pi_id"]}';"); sqlString.Append("INSERT INTO project_info VALUES(" + $"'{row["pi_id"]}', '{row["pi_code"]}', '{row["pi_name"]}', '{row["pi_field"]}', '{row["pi_theme"]}', '{row["pi_funds"]}', '{GetFormatDate(row["pi_startdate"])}', '{GetFormatDate(row["pi_finishdate"])}', " + $"'{row["pi_year"]}', '{row["pi_unit"]}', '{row["pi_province"]}', '{row["pi_unit_user"]}', '{row["pi_project_user"]}', '{row["pi_contacts"]}', '{row["pi_contacts_phone"]}', '{row["pi_introduction"]}', '{row["pi_obj_id"]}');"); } SQLiteHelper.ExecuteNonQuery(sqlString.ToString()); sqlString = new StringBuilder(); DataTable topicTable = helper.ExecuteQuery($"SELECT * FROM topic_info"); length = topicTable.Rows.Count; for (int i = 0; i < length; i++) { SetTip($"正在导入课题数据({i + 1}\\{length})"); DataRow row = topicTable.Rows[i]; sqlString.Append($"DELETE FROM topic_info WHERE ti_id='{row["ti_id"]}';"); sqlString.Append("INSERT INTO topic_info VALUES(" + $"'{row["ti_id"]}', '{row["ti_code"]}', '{row["ti_name"]}', '{row["ti_field"]}', '{row["ti_theme"]}', '{row["ti_funds"]}', '{GetFormatDate(row["ti_startdate"])}', '{GetFormatDate(row["ti_finishdate"])}'," + $"'{row["ti_year"]}', '{row["ti_unit"]}', '{row["ti_province"]}', '{row["ti_unit_user"]}', '{row["ti_project_user"]}', '{row["ti_contacts"]}', '{row["ti_contacts_phone"]}', '{ row["ti_introduction"]}', '{row["ti_obj_id"]}');"); } SQLiteHelper.ExecuteNonQuery(sqlString.ToString()); sqlString = new StringBuilder(); DataTable subjectTable = helper.ExecuteQuery($"SELECT * FROM subject_info"); length = subjectTable.Rows.Count; for (int i = 0; i < length; i++) { SetTip($"正在导入子课题数据({i + 1}\\{length})"); DataRow row = subjectTable.Rows[i]; sqlString.Append($"DELETE FROM subject_info WHERE si_id='{row["si_id"]}';"); sqlString.Append("INSERT INTO subject_info VALUES(" + $"'{row["si_id"]}', '{row["si_code"]}', '{row["si_name"]}', '{row["si_field"]}', '{row["si_theme"]}', '{row["si_funds"]}', '{GetFormatDate(row["si_startdate"])}', '{GetFormatDate(row["si_finishdate"])}'," + $"'{row["si_year"]}', '{row["si_unit"]}', '{row["si_province"]}', '{row["si_unit_user"]}', '{row["si_project_user"]}', '{row["si_contacts"]}', '{row["si_contacts_phone"]}', '{row["si_introduction"]}', '{row["si_obj_id"]}');"); } SQLiteHelper.ExecuteNonQuery(sqlString.ToString()); sqlString = new StringBuilder(); DataTable fileTable = helper.ExecuteQuery($"SELECT * FROM files_info"); length = fileTable.Rows.Count; for (int i = 0; i < length; i++) { SetTip($"正在导入文件基础数据({i + 1}\\{length})"); DataRow row = fileTable.Rows[i]; string link = GetValue(row["fi_link"]).Trim(); object fileId = row["fi_file_id"]; //尝试转换文件的link路径-转换为当前服务器链接 if (!string.IsNullOrEmpty(link) && Directory.Exists(rootFolder)) { string newLink = string.Empty; string[] linkString = link.Split(';'); for (int j = 0; j < linkString.Length; j++) { if (!string.IsNullOrEmpty(linkString[j])) { string fileName = Path.GetFileName(linkString[j]); string filePath = GetFilePathByRootFolder(rootFolder, fileName); if (!string.IsNullOrEmpty(filePath)) { linkString[j] = filePath; string _filePath = Path.GetDirectoryName(linkString[j]); string _fileName = Path.GetFileName(linkString[j]); sqlString.Append($"UPDATE backup_files_info SET bfi_state=1 WHERE bfi_path='{_filePath}' AND bfi_name='{_fileName}';"); newLink += linkString[j] + ";"; } } } link = string.IsNullOrEmpty(newLink) ? string.Empty : newLink.Substring(0, newLink.Length - 1); } //更新文件备份表状态 if (!string.IsNullOrEmpty(link)) { string[] linkString = link.Split(';'); string newFileId = string.Empty; for (int j = 0; j < linkString.Length; j++) { if (!string.IsNullOrEmpty(linkString[j])) { string _filePath = Path.GetDirectoryName(link); string _fileName = Path.GetFileName(link); object _fileId = SQLiteHelper.ExecuteOnlyOneQuery($"SELECT bfi_id FROM backup_files_info WHERE bfi_path='{_filePath}' AND bfi_name='{_fileName}';"); if (_fileId != null) { newFileId += _fileId + ","; } } } fileId = string.IsNullOrEmpty(newFileId) ? string.Empty : newFileId.Substring(0, newFileId.Length - 1); } sqlString.Append($"DELETE FROM files_info WHERE fi_id='{row["fi_id"]}';"); sqlString.Append("INSERT INTO files_info(fi_id, fi_code, fi_stage, fi_categor, fi_categor_name, fi_name, fi_user, fi_type, fi_secret, fi_pages, fi_count, fi_create_date, fi_unit, fi_carrier, fi_format, fi_form, fi_link, fi_file_id, fi_status, fi_obj_id, fi_sort, fi_remark) VALUES(" + $"'{row["fi_id"]}', '{row["fi_code"]}', '{row["fi_stage"]}', '{row["fi_categor"]}', '{row["fi_categor_name"]}', '{row["fi_name"]}', '{row["fi_user"]}', '{row["fi_type"]}', '{row["fi_secret"]}', '{row["fi_pages"]}', '{row["fi_count"]}', " + $"'{GetFormatDate(row["fi_create_date"])}', '{row["fi_unit"]}', '{row["fi_carrier"]}', '{row["fi_format"]}', '{row["fi_form"]}', '{link}', '{fileId}', '{row["fi_status"]}', '{row["fi_obj_id"]}', '{row["fi_sort"]}', '{row["fi_remark"]}');"); } SQLiteHelper.ExecuteNonQuery(sqlString.ToString()); sqlString = new StringBuilder(); DataTable lostTable = helper.ExecuteQuery($"SELECT * FROM files_lost_info"); length = lostTable.Rows.Count; for (int i = 0; i < length; i++) { SetTip($"正在导入缺失文件数据({i + 1}\\{length})"); DataRow row = lostTable.Rows[i]; sqlString.Append($"DELETE FROM files_lost_info WHERE pfo_id='{row["pfo_id"]}';"); sqlString.Append($"INSERT INTO files_lost_info VALUES('{row["pfo_id"]}', '{row["pfo_categor"]}', '{row["pfo_name"]}', '{row["pfo_reason"]}', '{row["pfo_remark"]}', '{row["pfo_obj_id"]}');"); } SQLiteHelper.ExecuteNonQuery(sqlString.ToString()); sqlString = new StringBuilder(); DataTable boxTable = helper.ExecuteQuery($"SELECT * FROM files_box_info"); length = boxTable.Rows.Count; for (int i = 0; i < length; i++) { SetTip($"正在导入卷盒信息数据({i + 1}\\{length})"); DataRow row = boxTable.Rows[i]; sqlString.Append($"DELETE FROM files_box_info WHERE pb_id='{row["pb_id"]}';"); sqlString.Append($"INSERT INTO files_box_info(pb_id, pb_box_number, pb_gc_id, pb_files_id, pb_obj_id, pb_special_id) " + $"VALUES('{row["pb_id"]}', '{row["pb_box_number"]}', '{row["pb_gc_id"]}', '{row["pb_files_id"]}', '{row["pb_obj_id"]}', '{row["pb_special_id"]}');"); } SQLiteHelper.ExecuteNonQuery(sqlString.ToString()); string dicKey = "'46dadbc7-9985-4b56-9c33-b00f2c6d7702','00cd4c30-8f41-4c65-8230-31d97679c209','25343dbb-4c88-4066-a3e8-1e33c9c5613b','b7c4fae1-549a-46d2-a35d-e1a36ccb4b79'"; DataTable dicTable = helper.ExecuteQuery($"SELECT * FROM data_dictionary WHERE dd_pId IN ({dicKey})"); length = dicTable.Rows.Count; sqlString = new StringBuilder(); for (int i = 0; i < length; i++) { SetTip($"正在导入字典表数据({i + 1}\\{length})"); DataRow row = dicTable.Rows[i]; int index = SQLiteHelper.ExecuteCountQuery($"SELECT COUNT(dd_id) FROM data_dictionary WHERE dd_id='{row["dd_id"]}'"); if (index == 0) { sqlString.Append("INSERT INTO data_dictionary (dd_id, dd_name, dd_pId, dd_code, dd_note, dd_sort, level, extend_2, extend_3, extend_4, extend_5) " + $"VALUES ('{row["dd_id"]}', '{row["dd_name"]}', '{row["dd_pId"]}', '{row["dd_code"]}', '{row["dd_note"]}', '{row["dd_sort"]}', '{row["level"]}', '{row["extend_2"]}', '{row["extend_3"]}', '{row["extend_4"]}', '{row["extend_5"]}');"); } } SQLiteHelper.ExecuteNonQuery(sqlString.ToString()); }
private bool CheckDatas() { errorProvider1.Clear(); bool result = true; //文件类别 if (cbo_categor.SelectedIndex == -1 || cbo_categor.SelectedIndex == cbo_categor.Items.Count - 1) { string value = cbo_categor.Text.Trim(); if (string.IsNullOrEmpty(value) || value.StartsWith("-") || value.EndsWith("-") || !value.Contains("-")) { errorProvider1.SetError(cbo_categor, "提示:请输入文件类别名称。"); result = false; } } //页数 NumericUpDown pagesCell = num_page; if (pagesCell.Value == 0) { errorProvider1.SetError(pagesCell, "提示:页数不能为0。"); result = false; } //文件名 string nameValue = txt_fileName.Text.Trim(); if (string.IsNullOrEmpty(nameValue)) { errorProvider1.SetError(txt_fileName, "提示:文件名不能为空。"); result = false; } else if (Text.Contains("新增")) { int _count = SQLiteHelper.ExecuteCountQuery($"SELECT COUNT(fi_id) FROM files_info WHERE fi_name='{nameValue}' AND fi_obj_id='{parentId}'"); if (_count > 0) { errorProvider1.SetError(txt_fileName, "提示:文件名已存在,请重新输入。"); result = false; } } //编号 if (string.IsNullOrEmpty(txt_fileCode.Text.Trim())) { errorProvider1.SetError(txt_fileCode, "提示:编号不能为空。"); result = false; } //文件类型 int count = 0; foreach (RadioButton item in pal_type.Controls) { if (item.Checked) { count++; break; } } if (count == 0) { errorProvider1.SetError(pal_type, "提示:文件类型不能为空。"); result = false; } //存放单位 if (string.IsNullOrEmpty(txt_unit.Text.Trim())) { errorProvider1.SetError(txt_unit, "提示:存放单位不能为空。"); result = false; } string dateString = txt_Date.Text; if (!string.IsNullOrEmpty(dateString)) { bool flag = DateTime.TryParse(GetValue(dateString), out DateTime date); if (!flag) { errorProvider1.SetError(txt_Date, "提示:请输入格式为 yyyy-MM-dd 的有效日期。"); result = false; } } return(result); }
/// <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="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}'))")); }