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