public frmGroup(DataFilter mDataFilter, Boolean IsUseFilter) { InitializeComponent(); if (mDataFilter.QueryConditionList.Count <= 0 || !IsUseFilter) return; Text += "[With DataView Filter]"; foreach (DataFilter.QueryConditionInputItem item in mDataFilter.QueryConditionList) { GroupConditionList.Add(item); } }
/// <summary> /// 将条件转成UI /// </summary> /// <param name="NewDataFilter"></param> public void PutQueryToUI(DataFilter NewDataFilter) { String strErrMsg = String.Empty; var ShowColumnList = new List<String>(); foreach (String item in ColumnList) { ShowColumnList.Add(item); } //清除所有的控件 List<DataFilter.QueryFieldItem> FieldList = NewDataFilter.QueryFieldList; foreach (DataFilter.QueryFieldItem queryFieldItem in NewDataFilter.QueryFieldList) { //动态加载控件 if (!ColumnList.Contains(queryFieldItem.ColName)) { strErrMsg += "Display Field [" + queryFieldItem.ColName + "] is not exist in current collection any more" + Environment.NewLine; } else { ShowColumnList.Remove(queryFieldItem.ColName); } } foreach (String item in ShowColumnList) { strErrMsg += "New Field" + item + "Is Append" + Environment.NewLine; //输出配置的初始化 FieldList.Add(new DataFilter.QueryFieldItem(item)); } Controls.Clear(); _conditionPos = new Point(5, 0); _conditionCount = 0; foreach (DataFilter.QueryConditionInputItem queryConditionItem in NewDataFilter.QueryConditionList) { var newCondition = new ctlQueryCondition(); newCondition.Init(ColumnList); _conditionPos.Y += newCondition.Height; newCondition.Location = _conditionPos; newCondition.ConditionItem = queryConditionItem; _conditionCount++; newCondition.Name = "Condition" + _conditionCount; Controls.Add(newCondition); if (!ColumnList.Contains(queryConditionItem.ColName)) { strErrMsg += queryConditionItem.ColName + "Query Condition Field is not exist in collection any more" + Environment.NewLine; } } if (strErrMsg != String.Empty) { MyMessageBox.ShowMessage("Load Exception", "A Exception is happened when loading", strErrMsg, true); } }
public frmGroup(DataFilter mDataFilter, Boolean IsUseFilter) { InitializeComponent(); if (mDataFilter.QueryConditionList.Count > 0 && IsUseFilter) { this.Text += "[With DataView Filter]"; foreach (var item in mDataFilter.QueryConditionList) { GroupConditionList.Add(item); } } }
public frmDistinct(DataFilter mDataFilter, Boolean IsUseFilter) { InitializeComponent(); if (mDataFilter.QueryConditionList.Count <= 0 || !IsUseFilter) return; Text += "[With DataView Filter]"; //直接使用 DistinctConditionList = mDataFilter.QueryConditionList //DistinctConditionList是引用类型,在LoadQuery的时候,会改变mDataFilter.QueryConditionList的值 //进而改变DataViewInfo在TabView上的值 foreach (DataFilter.QueryConditionInputItem item in mDataFilter.QueryConditionList) { DistinctConditionList.Add(item); } }
/// <summary> /// Group /// </summary> /// <param name="sender"></param> /// <param name="e"></param> private void groupToolStripMenuItem_Click(object sender, EventArgs e) { DataFilter Query = new DataFilter(); String ColPath = SystemManager.SelectTagData; Boolean IsUseFilter = false; if (ViewInfoList.ContainsKey(ColPath)) { Query = ViewInfoList[ColPath].mDataFilter; IsUseFilter = ViewInfoList[ColPath].IsUseFilter; } SystemManager.OpenForm(new frmGroup(Query, IsUseFilter)); }
/// <summary> /// Count /// </summary> /// <param name="sender"></param> /// <param name="e"></param> private void countToolStripMenuItem_Click(object sender, EventArgs e) { DataFilter Query = new DataFilter(); String ColPath = SystemManager.SelectTagData; Boolean IsUseFilter = false; if (ViewInfoList.ContainsKey(ColPath)) { Query = ViewInfoList[ColPath].mDataFilter; IsUseFilter = ViewInfoList[ColPath].IsUseFilter; } if (Query.QueryConditionList.Count == 0 || !IsUseFilter) { MyMessageBox.ShowEasyMessage("Count", "Count Result : " + SystemManager.GetCurrentCollection().Count().ToString()); } else { MongoDB.Driver.IMongoQuery mQuery = MongoDBHelper.GetQuery(Query.QueryConditionList); MyMessageBox.ShowMessage("Count", "Count[With DataView Filter]:" + SystemManager.GetCurrentCollection().Count(mQuery).ToString(), mQuery.ToString(), true); } }
/// <summary> /// 将And和Or组里面的最基本条件转化为一个IMongoQuery /// </summary> /// <param name="oprGrp"></param> /// <param name="strOPR"></param> /// <returns></returns> private static IMongoQuery GetQuery(DataFilter.QueryConditionInputItem item) { IMongoQuery query; BsonValue queryvalue = item.Value.GetBsonValue(); switch (item.Compare) { case DataFilter.CompareEnum.EQ: query = Query.EQ(item.ColName, queryvalue); break; case DataFilter.CompareEnum.GT: query = Query.GT(item.ColName, queryvalue); break; case DataFilter.CompareEnum.GTE: query = Query.GTE(item.ColName, queryvalue); break; case DataFilter.CompareEnum.LT: query = Query.LT(item.ColName, queryvalue); break; case DataFilter.CompareEnum.LTE: query = Query.LTE(item.ColName, queryvalue); break; case DataFilter.CompareEnum.NE: query = Query.NE(item.ColName, queryvalue); break; default: query = Query.EQ(item.ColName, queryvalue); break; } return query; }
private void PutQueryToUI(DataFilter NewDataFilter) { String strErrMsg = String.Empty; List<String> ShowColumnList = new List<String>(); foreach (String item in ColumnList) { ShowColumnList.Add(item); } //清除所有的控件 tabFieldInfo.Controls.Clear(); foreach (DataFilter.QueryFieldItem queryFieldItem in NewDataFilter.QueryFieldList) { //动态加载控件 if (!ColumnList.Contains(queryFieldItem.ColName)) { strErrMsg += queryFieldItem.ColName + "Display Field is not exist in current collection any more" + System.Environment.NewLine; } else { ctlFieldInfo ctrItem = new ctlFieldInfo(); ctrItem.Name = queryFieldItem.ColName; ctrItem.Location = _conditionPos; ctrItem.QueryFieldItem = queryFieldItem; tabFieldInfo.Controls.Add(ctrItem); //纵向位置的累加 _conditionPos.Y += ctrItem.Height; ShowColumnList.Remove(queryFieldItem.ColName); } } //新增字段 _conditionPos = new Point(5, 0); foreach (String item in ShowColumnList) { strErrMsg += "New Field" + item + "Is Append" + System.Environment.NewLine; //输出配置的初始化 DataFilter.QueryFieldItem queryFieldItem = new DataFilter.QueryFieldItem(); queryFieldItem.ColName = item; queryFieldItem.IsShow = true; queryFieldItem.sortType = DataFilter.SortType.NoSort; //动态加载控件 ctlFieldInfo ctrItem = new ctlFieldInfo(); ctrItem.Name = item; _conditionPos.Y += ctrItem.Height; ctrItem.Location = _conditionPos; ctrItem.QueryFieldItem = queryFieldItem; tabFieldInfo.Controls.Add(ctrItem); } panFilter.Controls.Clear(); _conditionPos = new Point(5, 0); _conditionCount = 0; foreach (DataFilter.QueryConditionInputItem queryConditionItem in NewDataFilter.QueryConditionList) { ctlQueryCondition newCondition = new ctlQueryCondition(); newCondition.Init(ColumnList); _conditionPos.Y += newCondition.Height; newCondition.Location = _conditionPos; newCondition.ConditionItem = queryConditionItem; _conditionCount++; newCondition.Name = "Condition" + _conditionCount.ToString(); panFilter.Controls.Add(newCondition); if (!ColumnList.Contains(queryConditionItem.ColName)) { strErrMsg += queryConditionItem.ColName + "Query Condition Field is not exist in collection any more" + System.Environment.NewLine; } } if (strErrMsg != String.Empty) { MyMessageBox.ShowMessage("Load Exception", "A Exception is happened when loading", strErrMsg, true); } }
/// <summary> /// Order 的设置 /// </summary> /// <param name="Curr"></param> /// <param name="SqlContent"></param> private static void SetQueryOrder(DataFilter Curr, String SqlContent) { //如果获得了内容,应该是这个样子的 By A ASC,B DES //1.删除By By A ASC,B DES -> A Asc,B Des SqlContent = SqlContent.Substring(3); //2.通过逗号分隔列表 //A Asc , B Des -> A Asc // B Des String[] SortFieldLst = SqlContent.Split(",".ToCharArray()); //3.分出 Field 和 Order foreach (String SortField in SortFieldLst) { String[] Sortfld = SortField.Trim().Split(" ".ToCharArray()); for (int i = 0; i < Curr.QueryFieldList.Count; i++) { if (Curr.QueryFieldList[i].ColName.ToLower() == Sortfld[0].ToLower()) { //无参数时候,默认是升序[Can't Modify]QueryFieldList是一个结构体 DataFilter.QueryFieldItem queryfld = Curr.QueryFieldList[i]; if (Sortfld.Length == 1) { queryfld.sortType = DataFilter.SortType.Ascending; } else { if (Sortfld[1].ToLower().StartsWith("d")) { queryfld.sortType = DataFilter.SortType.Descending; } else { queryfld.sortType = DataFilter.SortType.Ascending; } } Curr.QueryFieldList[i] = queryfld; break; } } } }
public static DataFilter ConvertQuerySql(String Sql) { DataFilter rtnQuery = new DataFilter(); Sql = Sql.Trim(); //引号中的空格用 代替,引号以外的东西小写 Sql = Regular(Sql); //先将字符串里面的空格统一成单个空格 //Select A,B From C -> //Select A,B From C while (Sql.Contains(" ")) { Sql = Sql.Replace(" ", " "); } //找出Select ,From , Group String[] SqlToken = Sql.Split(" ".ToCharArray()); int SelectStartIndex = -1; int FromStartIndex = -1; int WhereStartIndex = -1; int GroupByStartIndex = -1; int OrderByStartIndex = -1; for (int i = 0; i < SqlToken.Length; i++) { switch (SqlToken[i].ToLower()) { case "select": SelectStartIndex = i; break; case "from": FromStartIndex = i; break; case "where": WhereStartIndex = i; break; case "group": GroupByStartIndex = i; break; case "order": OrderByStartIndex = i; break; default: break; } } String[] KeyWords = new string[] { "select", "from", "where", "group", "order" }; //From 设定 必须项 //From - > CollectionName String strFrom = GetKeyContent(FromStartIndex, SqlToken, KeyWords); MongoCollection mongoCol; if ((strFrom != String.Empty) & SystemManager.GetCurrentDataBase().CollectionExists(strFrom)) { mongoCol = SystemManager.GetCurrentDataBase().GetCollection(strFrom); } else { return null; } //Select 设定 必须项 //Select - > FieldList String strSelect = GetKeyContent(SelectStartIndex, SqlToken, KeyWords); if (strSelect == String.Empty) { return null; } List<String> ColumnNameLst = MongoDBHelper.GetCollectionSchame(mongoCol); if (strSelect == "*") { //Select * foreach (String item in ColumnNameLst) { DataFilter.QueryFieldItem field = new DataFilter.QueryFieldItem(); field.ColName = item; field.IsShow = true; field.sortType = DataFilter.SortType.NoSort; rtnQuery.QueryFieldList.Add(field); } } else { //Select A,B,C foreach (String item in strSelect.Split(",".ToCharArray())) { DataFilter.QueryFieldItem field = new DataFilter.QueryFieldItem(); field.ColName = item; field.IsShow = true; field.sortType = DataFilter.SortType.NoSort; rtnQuery.QueryFieldList.Add(field); } } //Where 设定,可选项 String strWhere = GetKeyContent(WhereStartIndex, SqlToken, KeyWords); if (strWhere != String.Empty) { rtnQuery.QueryConditionList = SetQueryCondition(strWhere, ColumnNameLst); } //Order 设定,可选项 String strOrder = GetKeyContent(OrderByStartIndex, SqlToken, KeyWords); if (strOrder != String.Empty) { SetQueryOrder(rtnQuery, strOrder); } //Group 设定,可选项 String strGroup = GetKeyContent(GroupByStartIndex, SqlToken, KeyWords); if (strGroup != String.Empty) { //TODO:Group } return rtnQuery; }
/// <summary> /// 导出到Excel /// </summary> /// <param name="sender"></param> /// <param name="e"></param> private void ExportToFileToolStripMenuItem_Click(object sender, EventArgs e) { DataFilter Query = new DataFilter(); String ColPath = SystemManager.SelectTagData; if (ViewInfoList.ContainsKey(ColPath)) { // SystemManager.OpenForm(new frmExport(ViewInfoList[ColPath]), true, true); } else { //从菜单中选择,直接导出所有的数据 SystemManager.OpenForm(new frmExport(), true, true); } }
/// <summary> /// Distinct /// </summary> /// <param name="sender"></param> /// <param name="e"></param> private void distinctToolStripMenuItem_Click(object sender, EventArgs e) { var Query = new DataFilter(); String ColPath = SystemManager.SelectTagData; Boolean IsUseFilter = false; if (_viewInfoList.ContainsKey(ColPath)) { Query = _viewInfoList[ColPath].mDataFilter; IsUseFilter = _viewInfoList[ColPath].IsUseFilter; } SystemManager.OpenForm(new frmDistinct(Query, IsUseFilter), true, true); }
//http://www.mongodb.org/display/DOCS/SQL+to+Mongo+Mapping+Chart /// <summary> /// Convert Query Sql To DataFilter /// </summary> /// <param name="Sql"></param> /// <returns></returns> public static DataFilter ConvertQuerySql(String Sql) { DataFilter rtnQuery = new DataFilter(); Sql = Sql.Trim(); //引号中的空格用 代替,引号以外的东西小写 Sql = Regular(Sql); //先将字符串里面的空格统一成单个空格 //Select A,B From C -> //Select A,B From C while (Sql.Contains(" ")) { Sql = Sql.Replace(" ", " "); } //找出Select ,From , Group String[] SqlToken = Sql.Split(" ".ToCharArray()); int SelectStartIndex = -1; int FromStartIndex = -1; int WhereStartIndex = -1; int GroupByStartIndex = -1; int OrderByStartIndex = -1; for (int i = 0; i < SqlToken.Length; i++) { switch (SqlToken[i].ToLower()) { case "select": SelectStartIndex = i; break; case "from": FromStartIndex = i; break; case "where": WhereStartIndex = i; break; case "group": GroupByStartIndex = i; break; case "order": OrderByStartIndex = i; break; default: break; } } String[] KeyWords = new String[] { "select", "from", "where", "group", "order" }; //From 设定 必须项 //From - > CollectionName String strFrom = GetKeyContent(FromStartIndex, SqlToken, KeyWords); MongoCollection mongoCol; if ((strFrom != String.Empty) & SystemManager.GetCurrentDataBase().CollectionExists(strFrom)) { mongoCol = SystemManager.GetCurrentDataBase().GetCollection(strFrom); } else { return(null); } //Select 设定 必须项 //Select - > FieldList String strSelect = GetKeyContent(SelectStartIndex, SqlToken, KeyWords); if (strSelect == String.Empty) { return(null); } List <String> ColumnNameLst = MongoDBHelper.GetCollectionSchame(mongoCol); if (strSelect == "*") { //Select * foreach (String item in ColumnNameLst) { DataFilter.QueryFieldItem field = new DataFilter.QueryFieldItem(); field.ColName = item; field.IsShow = true; field.sortType = DataFilter.SortType.NoSort; rtnQuery.QueryFieldList.Add(field); } } else { //Select A,B,C foreach (String item in strSelect.Split(",".ToCharArray())) { DataFilter.QueryFieldItem field = new DataFilter.QueryFieldItem(); field.ColName = item; field.IsShow = true; field.sortType = DataFilter.SortType.NoSort; rtnQuery.QueryFieldList.Add(field); } } //Where 设定,可选项 String strWhere = GetKeyContent(WhereStartIndex, SqlToken, KeyWords); if (strWhere != String.Empty) { rtnQuery.QueryConditionList = SetQueryCondition(strWhere, ColumnNameLst); } //Order 设定,可选项 String strOrder = GetKeyContent(OrderByStartIndex, SqlToken, KeyWords); if (strOrder != String.Empty) { SetQueryOrder(rtnQuery, strOrder); } //Group 设定,可选项 String strGroup = GetKeyContent(GroupByStartIndex, SqlToken, KeyWords); if (strGroup != String.Empty) { //TODO:Group } return(rtnQuery); }