高级查询，自定义查询条件，自定义要显示的查询结果。
来源：互联网 发布：java开发是什么 编辑：程序博客网 时间：2024/05/13 10:39
效果展示：
1.“查询模块“、”条件“的数据绑定。
在界面拖一个GridControl，ColumnEdit选择repositoryItemComboBox，FieldName设置ITEM_CLASS_CODE。
查询模块和条件的值存入XML文件。
/// <summary> /// 读取配置文件的列表信息 /// </summary> /// <param name="fileName"></param> public static DataTable LoadConfig(string fileName) { System.Xml.XmlDocument doc = new System.Xml.XmlDocument(); doc.Load(fileName); System.Xml.XmlElement root = (System.Xml.XmlElement)doc.SelectSingleNode("config"); DataTable nodeTbl = new DataTable(); nodeTbl.Columns.Add("ITEM_CLASS_CODE", typeof(string)); nodeTbl.Columns.Add("ITEM_CODE", typeof(string)); nodeTbl.Columns.Add("ITEM_NAME", typeof(string)); nodeTbl.Columns.Add("CANINQUIRE", typeof(string)); nodeTbl.Columns.Add("CANSHOW", typeof(string)); foreach (System.Xml.XmlNode node in root.ChildNodes) { if (node.GetType() == typeof(System.Xml.XmlElement)) { DataRow parentRow = nodeTbl.NewRow(); parentRow["ITEM_CLASS_CODE"] = 0; parentRow["ITEM_CODE"] = node.Attributes["value"].Value; parentRow["ITEM_NAME"] = node.Attributes["name"].Value; parentRow["CANINQUIRE"] = node.Attributes["canInquire"].Value; parentRow["CANSHOW"] = node.Attributes["canShow"].Value; nodeTbl.Rows.Add(parentRow); string itemClassCode = string.Empty; itemClassCode = parentRow["ITEM_CODE"].ToString(); foreach (System.Xml.XmlNode childNode in node.ChildNodes) { if (childNode.GetType() == typeof(System.Xml.XmlElement)) { DataRow childRow = nodeTbl.NewRow(); childRow["ITEM_CLASS_CODE"] = itemClassCode; childRow["ITEM_CODE"] = childNode.Attributes["value"].Value; childRow["ITEM_NAME"] = childNode.Attributes["name"].Value; childRow["CANINQUIRE"] = childNode.Attributes["canInquire"].Value; childRow["CANSHOW"] = childNode.Attributes["canShow"].Value; nodeTbl.Rows.Add(childRow); } } } } return nodeTbl; }DataTable _configInfo = XrfDataSel.LoadConfig(@"xml\InquiryConfig.xml");
/// <summary> /// /// </summary> /// <param name="configInfo"></param> public void LoadCtrlCustomInquire() { DataRow[] rows = null; foreach (DataRow row in _configInfo.Rows) { rows = _configInfo.Select("ITEM_CLASS_CODE='" + 0 + "'"); } if (rows.Length > 0) { for (int i = 0; i < rows.Length; i++) { // 添加查询模块 repositoryItemComboBox1.Items.Add(rows[i]["ITEM_NAME"]); } } _temp.Columns.Add("ITEM_CLASS_CODE"); _temp.Columns.Add("ITEM_CODE"); _temp.Columns.Add("SIGN"); _temp.Columns.Add("VALUE"); _temp.Rows.Add(); gridControl1.DataSource = _temp; }由于条件根据不同的模块有不同的树级别，条件的ColumnEdit选择repositoryItemPopupContainerEdit1，FieldName设置ITEM_CODE。PopupControl选择popupContainerControl1
/// <summary> /// 根据模块名称绑定条件 /// </summary> /// <param name="nodes">模块名称</param> private void AddConditionNodes(string nodes) { //treeList1.DataSource = null; if (nodes == string.Empty || nodes == "请选择") { treeList1.DataSource = null; } else if (nodes == "患病情况") { DataTable illListInfo = SetTabColumnsName(); treeList1.DataSource = illListInfo; } else if (nodes == "生化检查") { DataTable nodeTbl = AddLaboratoryNodes(); treeList1.DataSource = nodeTbl; } else if (nodes == "膳食调查") { DataTable nutritionNodes = AddNutiitionNodes(); treeList1.DataSource = nutritionNodes; } else { AddNodes(nodes); } }/// <summary> /// 绑定查询子项目 /// </summary> private void AddNodes(string nodes) { // 获取编号 DataRow[] rows = _configInfo.Select("ITEM_NAME='" + nodes + "'"); string itemCode = string.Empty; foreach (DataRow row in rows) { if (rows.Length > 0) { itemCode = rows[0]["ITEM_CODE"].ToString(); } } // 根据编号查找，为表basicInfo赋值 DataTable basicInfo = _configInfo.Clone(); rows = _configInfo.Select("ITEM_CLASS_CODE ='" + itemCode + "'"); foreach (DataRow row in rows) { basicInfo.ImportRow(row); } // 将basicInfo表绑定treeList1 treeList1.DataSource = basicInfo; }
2.查询
思路： 将查询条件存入一张表，每一行循环查询一次。
由于是多表查询，这里是在基本信息查询结果的条件下，根据每张表的病人ID号和门诊日期来添加列，即所有查询结果表的拼接。
private void btnInquire_Click(object sender, EventArgs e) { base.StartTransation(); try { // 1.获取查询条件 DataTable tempCode = new DataTable(); tempCode = _temp.Copy(); foreach (DataRow row in tempCode.Rows) { string itemClassCode = row["ITEM_CLASS_CODE"].ToString(); string itemCode = row["ITEM_CODE"].ToString(); foreach (DataRow rows in _configInfo.Rows) { if (itemClassCode == rows["ITEM_NAME"].ToString()) { row["ITEM_CLASS_CODE"] = rows["ITEM_CODE"].ToString(); } if (itemCode == rows["ITEM_NAME"].ToString()) { row["ITEM_CODE"] = rows["ITEM_CODE"].ToString(); } } if (itemClassCode == "生化检查") { DataTable nodeTbl = AddLaboratoryNodes(); foreach (DataRow rows in nodeTbl.Rows) { if (itemCode == rows["ITEM_NAME"].ToString()) { row["ITEM_CODE"] = rows["ITEM_CODE"].ToString(); } } } } DataTable inquireTbl = tempCode; //DataTable inquireTbl = _tempCode; // 2.整理查询条件 按模块排序 inquireTbl = CopyDataTable(inquireTbl, string.Empty, "ITEM_CLASS_CODE, ITEM_CODE"); // 3.循环遍历查询条件，留下符合条件的记录 DataTable result = new DataTable(); string basicCondition = string.Empty; string phyCondition = string.Empty; string biochCondition = string.Empty; string disCondition = string.Empty; string illCondition = string.Empty; #region foreach (DataRow row in inquireTbl.Rows) { if (row["ITEM_CLASS_CODE"].ToString() == "001") { if (row["ITEM_CODE"].ToString() == "DIAGNOSIS") { // 患病情况 illCondition = strSQLCondition(illCondition, row); } else { // 基本信息条件 basicCondition = strSQLCondition(basicCondition, row); } } else if (row["ITEM_CLASS_CODE"].ToString() == "002") { // 体格检查 phyCondition = strSQLCondition(phyCondition, row); } else if (row["ITEM_CLASS_CODE"].ToString() == "003") { // 生化检查 biochCondition = strSQLCondition(biochCondition, row); } else if (row["ITEM_CLASS_CODE"].ToString() == "004") { // 膳食调查 disCondition = strSQLCondition(disCondition, row); } } #endregion // 基本信息条件 DataTable basicInfo = null; if (basicCondition != string.Empty) { basicInfo = CommunityHelperM.GetPersonAndVisit(" AND " + basicCondition); } else { basicInfo = CommunityHelperM.GetPersonAndVisit(basicCondition); } if (basicInfo != null) { foreach (DataColumn column in basicInfo.Columns) { if (!result.Columns.Contains(column.ColumnName)) { result.Columns.Add(column.ColumnName, typeof(string)); } } foreach (DataRow row in basicInfo.Rows) { DataRow newRow = result.NewRow(); foreach (DataColumn column in basicInfo.Columns) { newRow[column.ColumnName] = row[column]; } result.Rows.Add(newRow); } } // 体格检查 DataTable physicalInfo = null; if (phyCondition != string.Empty) { physicalInfo = CommunityHelperM.GetPhysicalExam(" AND " + phyCondition); } else { physicalInfo = CommunityHelperM.GetPhysicalExam(phyCondition); } if (physicalInfo != null) { foreach (DataColumn column in physicalInfo.Columns) { if (!result.Columns.Contains(column.ColumnName)) { result.Columns.Add(column.ColumnName, typeof(string)); } } for (int rowIndex = result.Rows.Count - 1; rowIndex >= 0; rowIndex--) { DataRow resultRow = result.Rows[rowIndex]; DataRow row = null; DataRow[] drs = physicalInfo.Select("PATIENT_ID='" + resultRow["PERSON_ID"].ToString() + "' AND LOG_DATE='" + resultRow["IN_HOS_DATE"].ToString() + "'"); if (drs.Length > 0) { row = drs[0]; foreach (DataColumn column in physicalInfo.Columns) { resultRow[column.ColumnName] = row[column]; } } else { if (phyCondition != string.Empty) { result.Rows.RemoveAt(rowIndex); } } } } // 生化检查 DataTable biochInfo = null; if (biochCondition != string.Empty) { biochInfo = CommunityHelperM.GetLaboratoryResult(" AND " + biochCondition); } else { biochInfo = CommunityHelperM.GetLaboratoryResult(biochCondition); } if (biochInfo != null) { for (int rowIndex = result.Rows.Count - 1; rowIndex >= 0; rowIndex--) { DataRow resultRow = result.Rows[rowIndex]; DataRow row = null; DataRow[] drs = biochInfo.Select("PERSON_ID='" + resultRow["PERSON_ID"].ToString() + "' AND CHECK_DATE='" + resultRow["IN_HOS_DATE"].ToString() + "'"); if (drs.Length > 0) { row = drs[0]; if (!result.Columns.Contains(row["ITEM_NAME"].ToString())) { result.Columns.Add(row["ITEM_NAME"].ToString(), typeof(string)); } resultRow[row["ITEM_NAME"].ToString()] = row["RESULT"]; } else { if (biochCondition != string.Empty) { result.Rows.RemoveAt(rowIndex); } } } } // 膳食调查 DataTable disInfo = null; if (disCondition != string.Empty) { disInfo = CommunityHelperInquiry.GetCtrlDietarySurvey(" AND " + disCondition); } else { disInfo = CommunityHelperInquiry.GetCtrlDietarySurvey(disCondition); } if (disInfo != null) { foreach (DataColumn column in disInfo.Columns) { if (!result.Columns.Contains(column.ColumnName)) { result.Columns.Add(column.ColumnName, typeof(string)); } } for (int rowIndex = result.Rows.Count - 1; rowIndex >= 0; rowIndex--) { DataRow resultRow = result.Rows[rowIndex]; DataRow row = null; DataRow[] drs = disInfo.Select("PERSON_ID='" + resultRow["PERSON_ID"].ToString() + "' AND ILL_MENU_DATE='" + resultRow["IN_HOS_DATE"].ToString() + "'"); if (drs.Length > 0) { row = drs[0]; foreach (DataColumn column in disInfo.Columns) { resultRow[column.ColumnName] = row[column]; } } else { if (disCondition != string.Empty) { result.Rows.RemoveAt(rowIndex); } } } } // 患病情况 DataTable illInfo = null; if (illCondition != string.Empty) { illInfo = CommunityHelperM.GetDiagnosisMgt(" AND " + illCondition); } else { illInfo = CommunityHelperM.GetDiagnosisMgt(illCondition); } if (illInfo != null) { foreach (DataColumn column in illInfo.Columns) { if (!result.Columns.Contains(column.ColumnName)) { result.Columns.Add(column.ColumnName, typeof(string)); } } for (int rowIndex = result.Rows.Count - 1; rowIndex >= 0; rowIndex--) { DataRow resultRow = result.Rows[rowIndex]; DataRow row = null; DataRow[] drs = illInfo.Select("PERSON_ID='" + resultRow["PERSON_ID"].ToString() + "' AND DIAGNOSIS_DATE='" + resultRow["IN_HOS_DATE"].ToString() + "'"); if (drs.Length > 0) { row = drs[0]; foreach (DataColumn column in illInfo.Columns) { resultRow[column.ColumnName] = row[column]; } } else { if (illCondition != string.Empty) { result.Rows.RemoveAt(rowIndex); } } } } // 列名汉化、列显示 for (int i = result.Columns.Count - 1; i >= 0; i--) { string columnCode = result.Columns[i].ColumnName; DataRow[] codeRowTemp = XrfDataSel.LoadConfig(@"xml\InquiryConfig.xml").Select(" ITEM_CODE = '" + columnCode + "'"); if (codeRowTemp.Length > 0) { if (codeRowTemp[0]["CANSHOW"].ToString() == "1") { string columnsName = codeRowTemp[0]["ITEM_NAME"].ToString(); if (!result.Columns.Contains(columnsName)) { result.Columns[i].ColumnName = columnsName; } else { } } else { if (result.Columns.Contains(columnCode)) { result.Columns.Remove(result.Columns[columnCode]); } } } else { if (result.Columns.Contains(columnCode)) { result.Columns.Remove(result.Columns[columnCode]); } } } DataTable source = new DataTable(); foreach (DataRow row in _configInfo.Rows) { string columnName = row["ITEM_NAME"].ToString(); if (result.Columns.Contains(columnName) && !source.Columns.Contains(columnName)) { DataColumn column = result.Columns[columnName]; source.Columns.Add(columnName, column.DataType); } } int rowIndexNum=0; foreach (DataRow row in result.Rows) { source.ImportRow(row); if (source.Rows[rowIndexNum]["性别"].ToString() == "0") { source.Rows[rowIndexNum]["性别"] = "男"; } else { source.Rows[rowIndexNum]["性别"] = "女"; } rowIndexNum++; } gridView2.Columns.Clear(); gridControl2.DataSource = source; } catch (Exception ex) { MessageHelper.Show(ex.Message); } base.EndTransation();}
条件与值关系判断：
/// <summary> /// 返回条件字符串 /// </summary> /// <param name="strCondition"></param> /// <param name="row"></param> /// <returns></returns> private string strSQLCondition(string strCondition, DataRow row) { if (strCondition != string.Empty) strCondition += " AND "; string values = "'" + row["VALUE"].ToString() + "'"; if (row["ITEM_CLASS_CODE"].ToString() == "003") { strCondition += " ITEM_CODE ='" + row["ITEM_CODE"].ToString() + "' AND RESULT "; } else if (row["ITEM_CLASS_CODE"].ToString() == "004") { strCondition += " CAST(in1." + row["ITEM_CODE"].ToString() + " AS DECIMAL)"; //values = row["VALUE"].ToString() ; } else { strCondition += row["ITEM_CODE"].ToString(); } // 患病情况 if (row["ITEM_CODE"].ToString() != "DIAGNOSIS") { values = row["VALUE"].ToString(); } // 性别 if (row["ITEM_CODE"].ToString() == "SEX") { if (row["VALUE"].ToString() == "男") { values = "'0'"; } else { values = "'1'"; } } switch (row["SIGN"].ToString()) { case "大于": strCondition += ">" + row["VALUE"].ToString(); break; case "小于": strCondition += "<" + row["VALUE"].ToString(); break; case "大于等于": strCondition += ">=" + row["VALUE"].ToString(); break; case "小于等于": strCondition += "<=" + row["VALUE"].ToString(); break; case "等于": strCondition += "=" + values; break; case "包含": strCondition += " LIKE "; strCondition += "'%" + row["VALUE"].ToString() + "%'"; break; } return strCondition; }
3.点击“添加条件”能自动添加一行。点击“删除条件”能删除当前选中行。
_temp.Rows.Add();
if (_temp.Rows.Count > 1)
{
_temp.Rows.RemoveAt(_selectedIndex);
}
4.显示设置
思路： 点击显示设置时，判断字段CANSHOW的值，来确定勾选CheckedListBox的哪些项。
获取选中的项
设置时，每个CheckedListBox项循环判断是否选中，选中则设置XML中的CANSHOW为1，否则为0
/// <summary> /// 绑定ListBox项以及勾选内容 /// </summary> /// <param name="chkLis"></param> /// <param name="itemClassCode"></param> private void LoadCheckList(CheckedListBoxControl chkLis, string itemClassCode) { // 基本信息表 DataRow[] chkRows = XrfDataSel.LoadConfig(@"xml\InquiryConfig.xml").Select("ITEM_CLASS_CODE='" + itemClassCode + "'"); DataTable chkInfo = XrfDataSel.LoadConfig(@"xml\InquiryConfig.xml").Clone(); foreach (DataRow row in chkRows) { chkInfo.Rows.Add(row.ItemArray); } chkLis.DisplayMember = "ITEM_NAME"; chkLis.ValueMember = "ITEM_CODE"; chkLis.DataSource = chkInfo; for (int itemIndex = 0; itemIndex < chkLis.ItemCount; itemIndex++) { if (chkInfo.Rows[itemIndex]["CANSHOW"].ToString() == "1") { chkLis.SetItemChecked(itemIndex, true); } else { chkLis.SetItemChecked(itemIndex, false); } } }
/// <summary> /// 获取选中项 /// </summary> /// <param name="chkLis"></param> /// <returns></returns> private string[] SetNodesShow(CheckedListBoxControl chkLis) { string[] drs = new string[chkLis.ItemCount]; int i = 0; foreach (DataRowView item in chkLis.CheckedItems) { drs[i] = item.Row["ITEM_CODE"].ToString(); i++; } return drs; }/// <summary> /// 保存显示设置 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> private void simpleButton4_Click(object sender, EventArgs e) { string[] temp = null; string[] basic = SetNodesShow(checkedListBoxControl1); string[] phy = SetNodesShow(checkedListBoxControl2); string[] bioch = SetNodesShow(checkedListBoxControl3); string[] dis = SetNodesShow(checkedListBoxControl4); string fileName = @"xml\InquiryConfig.xml"; System.Xml.XmlDocument doc = new System.Xml.XmlDocument(); doc.Load(fileName); System.Xml.XmlElement root = (System.Xml.XmlElement)doc.SelectSingleNode("config"); foreach (System.Xml.XmlNode node in root.ChildNodes) { if (node.GetType() == typeof(System.Xml.XmlElement)) { if (node.Attributes["value"].Value == "001") { temp = basic; } if (node.Attributes["value"].Value == "002") { temp = phy; } if (node.Attributes["value"].Value == "003") { temp = bioch; } if (node.Attributes["value"].Value == "004") { temp = dis; } foreach (System.Xml.XmlNode childNode in node.ChildNodes) { if (childNode.GetType() == typeof(System.Xml.XmlElement)) { childNode.Attributes["canShow"].Value = "0"; for (int j = 0; j < temp.Length; j++) { if (temp[j] != null) { if (temp[j].ToString() == childNode.Attributes["value"].Value) { childNode.Attributes["canShow"].Value = "1"; } } } } } } } doc.Save(fileName); popupContainerEdit1.ClosePopup(); MessageHelper.Show("设置成功"); }
5.数据导出
思路： 把查询结果的整个表导出即可。
#region //数据导出 private void OpenFile(string fileName) { if (XtraMessageBox.Show("您是否要打开此文件?", "Export To...", MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.Yes) { try { System.Diagnostics.Process process = new System.Diagnostics.Process(); process.StartInfo.FileName = fileName; process.StartInfo.Verb = "Open"; process.StartInfo.WindowStyle = System.Diagnostics.ProcessWindowStyle.Normal; process.Start(); } catch { DevExpress.XtraEditors.XtraMessageBox.Show(this, "Cannot find an application on your system suitable for openning the file with exported data.", Application.ProductName, MessageBoxButtons.OK, MessageBoxIcon.Error); } } progressBarControl1.Position = 0; } private void ExportTo(IExportProvider provider) { Cursor currentCursor = Cursor.Current; Cursor.Current = Cursors.WaitCursor; this.FindForm().Refresh(); BaseExportLink link = gridView2.CreateExportLink(provider); (link as GridViewExportLink).ExpandAll = false; link.Progress += new DevExpress.XtraGrid.Export.ProgressEventHandler(Export_Progress); link.ExportTo(true); provider.Dispose(); link.Progress -= new DevExpress.XtraGrid.Export.ProgressEventHandler(Export_Progress); Cursor.Current = currentCursor; } private void ExportToEx(String filename, string ext, BaseView exportView) { Cursor currentCursor = Cursor.Current; Cursor.Current = Cursors.WaitCursor; DevExpress.XtraPrinting.IPrintingSystem ps = DevExpress.XtraPrinting.PrintHelper.GetCurrentPS(); ps.AfterChange += new DevExpress.XtraPrinting.ChangeEventHandler(Export_ProgressEx); if (ext == "xls") exportView.ExportToXls(filename); if (ext == "xlsx") exportView.ExportToXlsx(filename); ps.AfterChange -= new DevExpress.XtraPrinting.ChangeEventHandler(Export_ProgressEx); Cursor.Current = currentCursor; } void Export_Progress(object sender, DevExpress.XtraGrid.Export.ProgressEventArgs e) { if (e.Phase == DevExpress.XtraGrid.Export.ExportPhase.Link) { SetPosition(e.Position); } } void Export_ProgressEx(object sender, DevExpress.XtraPrinting.ChangeEventArgs e) { if (e.EventName == DevExpress.XtraPrinting.SR.ProgressPositionChanged) { SetPosition((int)e.ValueOf(DevExpress.XtraPrinting.SR.ProgressPosition)); } } void SetPosition(int pos) { progressBarControl1.Position = pos; this.Update(); } private string ShowSaveFileDialog(string title, string filter) { SaveFileDialog dlg = new SaveFileDialog(); string name = Application.ProductName; int n = name.LastIndexOf(".") + 1; if (n > 0) name = name.Substring(n, name.Length - n); dlg.Title = "Export To " + title; dlg.FileName = name; dlg.Filter = filter; if (dlg.ShowDialog() == DialogResult.OK) return dlg.FileName; return ""; } #endregion/// <summary> /// 数据导出 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> private void simpleButton5_Click_1(object sender, EventArgs e) { #region // string fileName = string.Empty; string strExt = string.Empty; fileName = ShowSaveFileDialog("Microsoft Excel Document", "Microsoft Excel|*.xls"); strExt = "xls"; if (fileName == string.Empty) return; panelControl2.Visible = true; if (strExt.Equals("xml")) { ExportTo(new ExportXmlProvider(fileName)); OpenFile(fileName); } else { ExportToEx(fileName, strExt, gridView2); OpenFile(fileName); } panelControl2.Visible = false; #endregion }
0 0
- 高级查询，自定义查询条件，自定义要显示的查询结果。
- 自定义select查询结果
- hibernate 自定义查询结果
- 自定义Toast显示归属地查询结果
- Activiti5 自定义查询语句 TaskQuery查询 条件查询 like查询
- 数据库多条件自定义查询
- YII中自定义查询条件
- 条件查询结果显示下面iframe页面
- 用DropDownList提供查询条件，GridView显示查询结果
- 二次开发初尝试 自定义 查询条件
- 自定义 Azure Table storage 查询过滤条件
- 【CAS】自定义多条件查询配置
- struts2_条件查询、删除、自定义异常
- SQLServer 2012自定义函数，返回查询结果
- Hibernate实现dao自定义sql查询结果
- mysql 查询结果 自定义列名
- OpenJweb 1.6中自定义组合查询条件的实现方式
- 关于动态实现自定义查询条件的方案
- Android游戏开发
- SSH框架问题——listener（监听器）的配置报错：javax.naming.NameNotFoundException: Name com…………
- ibatis 技巧（一）
- java中this$0的含义及用法
- 找工作面试备忘录
- 高级查询，自定义查询条件，自定义要显示的查询结果。
- 探索Kinect Studio V2[1433]
- Codeforces Round #240 (Div. 2)
- NSURLReques缓存策略
- 面试题-在字符串中删除特定的字符[算法]
- eclipse下，device显示unknown的解决方法
- 清明出游散记
- [LeetCode] Binary Tree Inorder Traversa
- OpenGL学习
