private void simpleButton1_Click(object sender, System.EventArgs e)
{
object sourceFile = objectName;
object destDiskCode = treeView1.SelectedNode.Tag;
object destFolder = treeView1.SelectedNode.Text;
string queryString = $"确定将光盘({trcCode})下的文件[{sourceFile}]移动到光盘({destDiskCode})的文件夹[{destFolder}]下吗？";
DialogResult dialogResult = DevExpress.XtraEditors.XtraMessageBox.Show(queryString, "确认提示", MessageBoxButtons.OKCancel, MessageBoxIcon.Question);
if (dialogResult == DialogResult.OK)
{
string sourceFilePath = ToolHelper.GetValue(SqlHelper.ExecuteOnlyOneQuery($"SELECT bfi_path+'\\'+bfi_name FROM backup_files_info WHERE bfi_id='{objectId}';"));
string destFolderId = treeView1.SelectedNode.Name;
string destFolderPath = ToolHelper.GetValue(SqlHelper.ExecuteOnlyOneQuery($"SELECT bfi_path+'\\'+bfi_name FROM backup_files_info WHERE bfi_id='{destFolderId}';"));
if (File.Exists(sourceFilePath))
{
string updateSQL = $"UPDATE backup_files_info SET bfi_path='{destFolderPath}', bfi_pid='{destFolderId}', bfi_date='{DateTime.Now}', bfi_userid='{UserHelper.GetUser().UserKey}' WHERE bfi_id='{objectId}'";
SqlHelper.ExecuteNonQuery(updateSQL);
FileInfo file = new FileInfo(sourceFilePath);
if (!Directory.Exists(destFolderPath))
{
Directory.CreateDirectory(destFolderPath);
}
string newDestFilePath = destFolderPath + "\\" + Path.GetFileName(sourceFilePath);
file.CopyTo(newDestFilePath, true);
}
DevExpress.XtraEditors.XtraMessageBox.Show("操作成功");
DialogResult = DialogResult.OK;
Close();
}
}
public static void AddWorkLog(WorkLogType logType, int amount, object batchCode, int segment, object objectId, object userid)
{
if (string.IsNullOrEmpty(ToolHelper.GetValue(batchCode)))
{
return;
}
string date = ToolHelper.GetDateValue(DateTime.Now, "yyyy-MM-dd");
int type = (int)logType;
string existQuery = $"SELECT wl_id FROM work_log WHERE wl_user_id='{userid}' AND wl_datetime='{date}' AND wl_batch_code='{batchCode}' AND wl_type='{type}' AND wl_segment={segment}";
object result = SqlHelper.ExecuteOnlyOneQuery(existQuery);
if (result != null)
{
string updateSQL = $"UPDATE work_log SET wl_amount += {amount} WHERE wl_id='{result}'";
SqlHelper.ExecuteNonQuery(updateSQL);
}
else
{
//新增记录
string insertSQL = "INSERT INTO work_log(wl_id, wl_type, wl_batch_code, wl_amount, wl_datetime, wl_user_id, wl_segment, wl_object_id) VALUES" +
$"('{Guid.NewGuid().ToString()}', '{type}', '{batchCode}', '{amount}', '{date}', '{userid}', {segment}, '{objectId}')";
SqlHelper.ExecuteNonQuery(insertSQL);
}
}
/// <summary>
/// 生成树节点
/// </summary>
/// <param name="parentId">父级节点ID</param>
/// <param name="parentNode">父级节点</param>
/// <param name="isShowAll">是否显示已加工节点</param>
private void InitialTree(object parentId, TreeNode parentNode, bool isShowAll)
{
List <object[]> list = SqlHelper.ExecuteColumnsQuery($"SELECT bfi_id, bfi_name, bfi_path, bfi_state, bfi_type FROM backup_files_info WHERE bfi_pid='{parentId}' ORDER BY bfi_type, bfi_name", 5);
for (int i = 0; i < list.Count; i++)
{
int state = ToolHelper.GetIntValue(list[i][3], 0);
if (state != 1 || isShowAll)
{
int imageIndex = GetFileIconIndex(state, ToolHelper.GetValue(list[i][1]), list[i][4]);
TreeNode treeNode = new TreeNode()
{
Name = ToolHelper.GetValue(list[i][0]),
Text = ToolHelper.GetValue(list[i][1]),
Tag = ToolHelper.GetValue(list[i][2]),
ImageIndex = imageIndex,
SelectedImageIndex = imageIndex,
ToolTipText = ToolHelper.GetValue(list[i][4]),
StateImageKey = state.ToString(),
};
parentNode.Nodes.Add(treeNode);
InitialTree(treeNode.Name, treeNode, isShowAll);
}
}
}
private static string GetTrueValue(object value)
{
return(ToolHelper.GetValue(value)
.Replace(",", "，")
.Replace("\n", string.Empty)
.Replace("\t", string.Empty)
.Replace("\r", string.Empty));
}
/// <param name="boxId">盒主键</param>
public Frm_BorrowEditBox(object boxId, object borrowId, bool isLog)
{
string queryCon = string.Empty;
if (isLog)
{
queryCon = $"AND bl.bl_id='{borrowId}'";
}
BOX_ID = boxId;
InitializeComponent();
cbo_FileType.Items.AddRange(new object[] { "原件", "复印件", "电子" });
DataRow row = SqlHelper.ExecuteSingleRowQuery("SELECT * FROM processing_box " +
"LEFT JOIN(" +
" SELECT pi_id, pi_code, pi_name FROM project_info UNION ALL " +
" SELECT ti_id, ti_code, ti_name FROM topic_info UNION ALL " +
" SELECT imp_id, imp_code, imp_name FROM imp_info UNION ALL " +
" SELECT imp_id, imp_code, imp_name FROM imp_dev_info UNION ALL " +
" SELECT si_id, si_code, si_name FROM subject_info)A ON A.pi_id = pb_obj_id " +
$"LEFT JOIN borrow_log bl ON bl.bl_file_id = pb_id {queryCon} WHERE pb_id = '{boxId}'");
if (row != null)
{
lbl_Code.Tag = string.IsNullOrEmpty(ToolHelper.GetValue(row["bl_id"])) ? null : row["bl_id"];
lbl_pCode.Text = ToolHelper.GetValue(row["pi_code"]);
lbl_pName.Text = ToolHelper.GetValue(row["pi_name"]);
lbl_pGC.Text = ToolHelper.GetValue(row["pb_gc_id"]);
lbl_pBoxId.Text = ToolHelper.GetValue(row["pb_box_number"]);
if (isLog)
{
txt_Unit.Text = ToolHelper.GetValue(row["bl_user_unit"]);
txt_User.Text = ToolHelper.GetValue(row["bl_user"]);
txt_Phone.Text = ToolHelper.GetValue(row["bl_user_phone"]);
txt_Borrow_Date.Text = ToolHelper.GetDateValue(row["bl_date"], "yyyy-MM-dd HH:mm");
txt_Borrow_Term.Text = ToolHelper.GetValue(row["bl_term"]);
cbo_FileType.SelectedIndex = ToolHelper.GetIntValue(row["bl_form"], -1);
txt_Should_Return_Date.Text = ToolHelper.GetValue(row["bl_should_return_term"]);
txt_Real_Return_Date.Text = ToolHelper.GetValue(row["bl_real_return_term"]);
lbl_Code.Text = ToolHelper.GetValue(row["bl_code"]);
lbl_LogUser.Text = ToolHelper.GetValue(row["bl_log_user"]);
txt_Remark.Text = ToolHelper.GetValue(row["bl_remark"]);
string value = ToolHelper.GetValue(row["bl_id"]);
if (!string.IsNullOrEmpty(value))
{
lbl_Code.Tag = value;
int bstate = ToolHelper.GetIntValue(row["bl_return_state"], 0);
if (bstate != 0)
{
btn_Sure.Enabled = false;
}
else
{
btn_Sure.Text = "确认归还";
}
}
}
}
}
/// <summary>
/// 获取时间对象的DATE格式对象，若转换失败则返回当前时间
/// </summary>
internal static DateTime GetDateValue(object value)
{
string dateStr = ToolHelper.GetValue(value);
if (DateTime.TryParse(dateStr, out DateTime date))
{
return(date);
}
return(new DateTime());
}
/// <summary>
/// 查询唯一结果的SQL(无结果返回null)
/// </summary>
public static object ExecuteOnlyOneQuery(string querySql)
{
SqlCommand sqlCommand = new SqlCommand(querySql, GetConnect());
object result = sqlCommand.ExecuteScalar();
if (result != null)
{
if (string.IsNullOrEmpty(ToolHelper.GetValue(result)))
{
result = null;
}
}
return(result);
}
private void Btn_Sure_Click(object sender, EventArgs e)
{
SelectedFileId = new string[lsv_Selected.Items.Count];
SelectedFileName = new string[lsv_Selected.Items.Count];
for (int i = 0; i < SelectedFileId.Length; i++)
{
SelectedFileId[i] = lsv_Selected.Items[i].Name;
SelectedFileName[i] = ToolHelper.GetValue(lsv_Selected.Items[i].Tag);
}
if (SelectedFileId.Length > 0)
{
DialogResult = DialogResult.OK;
}
Close();
}
/// <summary>
/// 加载来源单位列表
/// </summary>
private void LoadCompanySource()
{
string querySql = "SELECT dd_id, dd_name FROM data_dictionary WHERE dd_pId=" +
"(SELECT dd_id FROM data_dictionary WHERE dd_code = 'dic_key_company_source') ORDER BY dd_sort";
DataTable table = SqlHelper.ExecuteQuery(querySql);
for (int i = 0; i < table.Rows.Count; i++)
{
AccordionControlElement element = new AccordionControlElement()
{
Style = ElementStyle.Item,
Name = ToolHelper.GetValue(table.Rows[i]["dd_id"]),
Text = ToolHelper.GetValue(table.Rows[i]["dd_name"]),
};
acg_Register.Elements.Add(element);
}
}
/// <summary>
/// 获取文件相关链接列表
/// </summary>
private object[] GetFileLink()
{
List <object> list = new List <object>();
foreach (DataGridViewRow row in dgv_link.Rows)
{
foreach (DataGridViewCell cell in row.Cells)
{
string value = ToolHelper.GetValue(cell.Value);
if (!string.IsNullOrEmpty(value))
{
list.Add(value);
}
}
}
return(list.ToArray());
}
/// <summary>
/// 获取完整的封面HTML模板页
/// </summary>
/// <param name="bj">边距mm数</param>
private string GetCoverHtmlString(object docName, string fmString, object bj, object GCNumber)
{
fmString = fmString.Replace("20mm", $"{bj}");
if (string.IsNullOrEmpty(ToolHelper.GetValue(parentObjectName)))
{
fmString = fmString.Replace("id=\"ajmc\">", $"id=\"ajmc\">{docName}");
}
else
{
fmString = fmString.Replace("id=\"ajmc\">", $"id=\"ajmc\">{parentObjectName}");
fmString = fmString.Replace("id=\"ktmc\">", $"id=\"ktmc\">{docName}");
}
fmString = fmString.Replace("id=\"bzdw\">", $"id=\"bzdw\">{unitName}");
fmString = fmString.Replace("id=\"bzrq\">", $"id=\"bzrq\">{ToolHelper.GetDateValue(ljDate, "yyyy-MM-dd")}");
fmString = fmString.Replace("id=\"bgqx\">", $"id=\"bgqx\">永久");
fmString = fmString.Replace("id=\"gch\">", $"id=\"gch\">{GCNumber}");
return(fmString);
}
private void LoadDiskList(object trpID, object trcCode)
{
object batchCode = SqlHelper.ExecuteOnlyOneQuery($"SELECT trp_name+'['+ trp_code+']' FROM transfer_registration_pc WHERE trp_id='{trpID}';");
TreeNode batchNode = new TreeNode(ToolHelper.GetValue(batchCode))
{
ImageIndex = 0
};
treeView1.Nodes.Add(batchNode);
string querySQL = $"SELECT A.bfi_id, B.trc_code, B.trc_name FROM backup_files_info A LEFT JOIN transfer_registraion_cd B ON A.bfi_trcid = B.trc_id " +
$"WHERE B.trp_id ='{trpID}' AND A.bfi_type = -1 ORDER BY b.trc_code";
DataTable table = SqlHelper.ExecuteQuery(querySQL);
foreach (DataRow row in table.Rows)
{
TreeNode diskNode = new TreeNode()
{
Name = ToolHelper.GetValue(row["bfi_id"]),
Text = $"{row["trc_name"]}[{row["trc_code"]}]",
ImageIndex = 1,
Tag = row["trc_code"]
};
batchNode.Nodes.Add(diskNode);
string _querySQL = $"SELECT bfi_id, bfi_name FROM backup_files_info WHERE bfi_pid ='{row["bfi_id"]}' AND bfi_type = 1 ORDER BY bfi_path";
DataTable _table = SqlHelper.ExecuteQuery(_querySQL);
foreach (DataRow _row in _table.Rows)
{
TreeNode folderNode = new TreeNode()
{
Name = ToolHelper.GetValue(_row["bfi_id"]),
Text = $"{_row["bfi_name"]}",
ImageIndex = 1,
Tag = row["trc_code"]
};
diskNode.Nodes.Add(folderNode);
}
}
if (treeView1.Nodes.Count > 0)
{
treeView1.ExpandAll();
}
}
private void Frm_QueryDetail_Load(object sender, System.EventArgs e)
{
txt_Project_Code.Text = ToolHelper.GetValue(row["pi_code"]);
txt_Project_Name.Text = ToolHelper.GetValue(row["pi_name"]);
txt_Project_Field.Text = ToolHelper.GetValue(row["pi_field"]);
txt_Project_Theme.Text = ToolHelper.GetValue(row["pb_theme"]);
txt_Project_StartTime.Text = ToolHelper.GetValue(row["pi_start_datetime"]);
txt_Project_EndTime.Text = ToolHelper.GetValue(row["pi_end_datetime"]);
txt_Project_Unit.Text = ToolHelper.GetValue(row["pi_unit"]);
txt_Project_Province.Text = ToolHelper.GetValue(row["pi_province"]);
txt_Project_ProUser.Text = ToolHelper.GetValue(row["pi_prouser"]);
txt_Project_Funds.Text = ToolHelper.GetValue(row["pi_funds"]);
txt_Project_Year.Text = ToolHelper.GetValue(row["pi_year"]);
txt_Project_UnitUser.Text = ToolHelper.GetValue(row["pi_uniter"]);
txt_Project_Intro.Text = ToolHelper.GetValue(row["pi_intro"]);
txt_Project_Intro.SelectionStart = 0;
Btn_Close.Focus();
}
//查询
private void UG_btnSearch(object sender, EventArgs e)
{
string key = userGroup_SearchKey.Text;
if (!string.IsNullOrEmpty(key))
{
view.ClearSelection();
foreach (DataGridViewRow row in view.Rows)
{
foreach (DataGridViewCell cell in row.Cells)
{
if (ToolHelper.GetValue(cell.Value).Contains(key))
{
cell.Selected = true;
return;
}
}
}
}
}
/// <summary>
/// 判断指定文件夹节点下的所有文件是否全部已加工，如果是，则移除此文件夹
/// </summary>
private bool ClearHasWordedWithFolder(TreeNode node)
{
bool result = true;
bool flag = true;
for (int i = 0; i < node.Nodes.Count; i++)
{
TreeNode item = node.Nodes[i];
int type = Convert.ToInt32(item.ToolTipText);//0:文件 1:文件夹
if (type == 0)
{
flag = false;
}
else if (type == 1)
{
result = ClearHasWordedWithFolder(item);
}
}
if (result)
{
foreach (TreeNode item in node.Nodes)
{
int type = Convert.ToInt32(item.ToolTipText); //0:文件 1:文件夹
int state = item.ImageIndex; //3:已加工
if (type == 0 && state != 3)
{
result = false;
break;
}
}
}
if (result && flag)
{
if (!string.IsNullOrEmpty(ToolHelper.GetValue(node.Tag)))//批次名称永不消逝
{
node.Remove();
}
}
return(result);
}
/// <summary>
/// 加载根节点树（调用树节点方法）
/// </summary>
/// <param name="isShowAll">是否显示已加工节点</param>
private void LoadRootTree(bool isShowAll)
{
object batchCode = SqlHelper.ExecuteOnlyOneQuery($"SELECT bfi_name FROM backup_files_info WHERE bfi_id='{rootId[0]}'");
tv_file.Nodes.Clear();
//一级节点【批次编号】
TreeNode batchNode = new TreeNode()
{
Text = ToolHelper.GetValue(batchCode),
ToolTipText = "-1",
};
tv_file.Nodes.Add(batchNode);
for (int i = 0; i < rootId.Length; i++)
{
object[] objs = SqlHelper.ExecuteRowsQuery("SELECT bfi_id, bfi_name, bfi_path, bfi_type, trc_code FROM backup_files_info a " +
$"LEFT JOIN transfer_registraion_cd b on a.bfi_trcid = b.trc_id WHERE bfi_id='{rootId[i]}'");
//二级节点【光盘编号】
string rootFileName = ToolHelper.GetValue(objs[4]);
TreeNode diskNode = new TreeNode()
{
Text = rootFileName,
ToolTipText = ToolHelper.GetValue(objs[3]),
};
batchNode.Nodes.Add(diskNode);
InitialTree(rootId[i], diskNode, isShowAll);
}
if (tv_file.Nodes.Count > 0)
{
tv_file.Nodes[0].Expand();
if (!chk_ShowAll.Checked)
{
ClearHasWordedWithFolder(tv_file.Nodes[0]);
}
if (IsMoveMode)
{
tv_file.ExpandAll();
}
}
}
/// <summary>
/// 根据指定批次ID获取其补录的其他批次根节点ID
/// </summary>
/// <param name="batchId">待查找批次ID</param>
/// <param name="type">查找类型<para>0：计划</para><para>1：专项</para></param>
public static object[] GetOtherBatchRootIds(object batchId, int type)
{
object otherBatchIds = ExecuteOnlyOneQuery($"SELECT br_auxiliary_id FROM batch_relevance WHERE br_main_id='{batchId}'");
string otherBatchIdString = ToolHelper.GetFullStringBySplit(ToolHelper.GetValue(otherBatchIds), ',', ",", "'");
if (otherBatchIdString.Length > 0)
{
if (type == 0)
{
string querySql = $"SELECT pi_id FROM project_info WHERE pi_categor = 1 AND pi_obj_id IN ({otherBatchIdString})";
return(ExecuteSingleColumnQuery(querySql));
}
else
{
string querySql = "SELECT idi.imp_id FROM imp_info ii " +
"INNER JOIN imp_dev_info idi ON idi.imp_obj_id = ii.imp_id " +
$"WHERE ii.imp_obj_id IN ({otherBatchIdString})";
return(ExecuteSingleColumnQuery(querySql));
}
}
return(null);
}
/// <summary>
/// 根据字典表的主键获取值(默认返回空字符串)
/// </summary>
/// <param name="objectId">字典表主键</param>
/// <param name="fieldName">待显示的字段名</param>
public static string GetValueByKey(object objectId, object fieldName)
{
object value = ExecuteOnlyOneQuery($"SELECT {fieldName} FROM data_dictionary WHERE dd_id='{objectId}'");
return(ToolHelper.GetValue(value));
}
/// <summary>
/// 根据用户ID获取用户姓名
/// </summary>
public static string GetUserNameById(object userId)
{
object value = SqlHelper.ExecuteOnlyOneQuery($"SELECT real_name FROM user_list WHERE ul_id='{userId}'");
return(ToolHelper.GetValue(value));
}
/// <summary>
/// 向指定Word中写入指定文本
/// </summary>
/// <param name="filePath">Word 所在路径</param>
/// <param name="list">所需写入的内容</param>
public static void WriteDocument(ref string filePath, List <DataRow> list)
{
Microsoft.Office.Interop.Word.Application app = null;
Document doc = null;
try
{
//构造数据
List <EntityObject> datas = new List <EntityObject>();
for (int i = 0; i < list.Count; i++)
{
string code = SqlHelper.GetValueByKey(list[i]["pfl_categor"], "dd_name");
string name = ToolHelper.GetValue(list[i]["pfl_filename"]);
string user = ToolHelper.GetValue(list[i]["pfl_user"]);
string carrier = SqlHelper.GetValueByKey(list[i]["pfl_carrier"], "dd_name");
int pages = Convert.ToInt32(list[i]["pfl_page_amount"]);
int number = Convert.ToInt32(list[i]["pfl_amount"]);
DateTime date = Convert.ToDateTime(list[i]["pfl_complete_date"]);
datas.Add(new EntityObject {
Code = code, Name = name, User = user, Type = carrier, PageSize = pages, FileAmount = number, Date = date
});
}
int rows = datas.Count() + 1; //表格行数加1是为了标题栏
int cols = 7; //表格列数
object oMissing = Missing.Value;
app = new Microsoft.Office.Interop.Word.Application(); //创建word应用程序
doc = app.Documents.Add(); //添加一个word文档
app.Selection.PageSetup.LeftMargin = 50f;
app.Selection.PageSetup.RightMargin = 50f;
app.Selection.PageSetup.PageWidth = 800f; //页面宽度
//标题
app.Selection.Font.Bold = 700;
app.Selection.Font.Size = 18;
app.Selection.Range.ParagraphFormat.Alignment = WdParagraphAlignment.wdAlignParagraphCenter;
app.Selection.Text = "项目（课题）档案交接清单";
//换行添加表格
object line = WdUnits.wdLine;
app.Selection.MoveDown(ref line, oMissing, oMissing);
app.Selection.TypeParagraph();//换行
app.Selection.Range.ParagraphFormat.Alignment = WdParagraphAlignment.wdAlignParagraphLeft;
Range range = app.Selection.Range;
Table table = app.Selection.Tables.Add(range, rows, cols, ref oMissing, ref oMissing);
//设置表格的字体大小粗细
table.Range.Font.Size = 10;
table.Range.Font.Bold = 0;
table.Borders.OutsideLineStyle = WdLineStyle.wdLineStyleSingle;
table.Borders.InsideLineStyle = WdLineStyle.wdLineStyleSingle;
//设置表格标题
int rowIndex = 1;
table.Rows[rowIndex].Range.ParagraphFormat.Alignment = WdParagraphAlignment.wdAlignParagraphCenter;
table.Rows[rowIndex].Range.Font.Bold = 100;
table.Rows[rowIndex].Height = 30f;
table.Cell(rowIndex, 1).Range.Text = "项目（课题）档案材料名称";
table.Cell(rowIndex, 2).Range.Text = "责任者";
table.Cell(rowIndex, 3).Range.Text = "载体类型";
table.Cell(rowIndex, 4).Range.Text = "页数";
table.Cell(rowIndex, 5).Range.Text = "文件数";
table.Cell(rowIndex, 6).Range.Text = "日期";
table.Cell(rowIndex, 7).Range.Text = "备注";
table.Columns[1].Width = 200f;
table.Columns[2].Width = table.Columns[4].Width = table.Columns[5].Width = 50f;
//循环数据创建数据行
foreach (EntityObject eo in datas)
{
rowIndex++;
table.Cell(rowIndex, 1).Range.Text = eo.Name;
table.Cell(rowIndex, 2).Range.Text = eo.User;
table.Cell(rowIndex, 3).Range.Text = eo.Type;
table.Cell(rowIndex, 4).Range.Text = eo.PageSize.ToString();
table.Cell(rowIndex, 5).Range.Text = eo.FileAmount.ToString();
table.Cell(rowIndex, 6).Range.Text = eo.Date.ToString("yyyy-MM-dd");
table.Cell(rowIndex, 7).Range.Text = eo.Remark;
}
app.Selection.EndKey(WdUnits.wdStory, oMissing); //将光标移动到文档末尾
app.Selection.Font.Bold = 0;
app.Selection.Font.Size = 11;
//底部署名
doc.Content.InsertAfter("\n移交单位（盖章）： 接收单位（盖章）：\n");
doc.Content.InsertAfter("移交人： 接收人：\n");
doc.Content.InsertAfter("交接时间： 年 月 日");
//导出到文件
filePath += DateTime.Now.ToString("yyyyMMddHHmm") + ".doc";
doc.SaveAs(filePath,
oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing,
oMissing, oMissing, oMissing, oMissing, oMissing, oMissing);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
if (doc != null)
{
doc.Close();//关闭文档
}
if (app != null)
{
app.Quit();//退出应用程序
}
}
}
