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);
}