/// <summary>
/// Order 的设置
/// </summary>
/// <param name="CurrentDataFilter"></param>
/// <param name="SqlContent"></param>
private static void SetQueryOrder(DataFilter CurrentDataFilter, 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 < CurrentDataFilter.QueryFieldList.Count; i++)
{
if (CurrentDataFilter.QueryFieldList[i].ColName.ToLower() == Sortfld[0].ToLower())
{
//无参数时候，默认是升序[Can't Modify]QueryFieldList是一个结构体
DataFilter.QueryFieldItem queryfld = CurrentDataFilter.QueryFieldList[i];
if (Sortfld.Length == 1)
{
queryfld.sortType = DataFilter.SortType.Ascending;
}
else
{
queryfld.sortType = Sortfld[1].ToLower().StartsWith("d")
? DataFilter.SortType.Descending
: DataFilter.SortType.Ascending;
}
CurrentDataFilter.QueryFieldList[i] = queryfld;
break;
}
}
}
}
/// <summary>
/// Aggregation用的$project和$order
/// </summary>
/// <returns></returns>
public BsonDocument GetAggregation()
{
var Aggregation = new BsonDocument();
var project = new BsonDocument();
var sort = new BsonDocument();
foreach (DataFilter.QueryFieldItem item in mQueryFieldList)
{
DataFilter.QueryFieldItem ctl = ((ctlFieldInfo)Controls.Find(item.ColName, true)[0]).QueryFieldItem;
if (ctl.ColName == MongoDbHelper.KEY_ID)
{
if (!ctl.IsShow)
{
project.Add(new BsonElement(MongoDbHelper.KEY_ID, 0));
}
}
else
{
if (ctl.IsShow)
{
project.Add(string.IsNullOrEmpty(ctl.ProjectName)
? new BsonElement(ctl.ColName, 1)
: new BsonElement(ctl.ProjectName, "$" + ctl.ColName));
}
}
switch (ctl.sortType)
{
case DataFilter.SortType.NoSort:
break;
case DataFilter.SortType.Ascending:
sort.Add(new BsonElement(ctl.ColName, 1));
break;
case DataFilter.SortType.Descending:
sort.Add(new BsonElement(ctl.ColName, -1));
break;
default:
break;
}
}
//Note The $sort cannot begin sorting documents until previous operators in the pipeline have returned all output.
//如果先$project，再$sort的话，全字段输出
if (sort.ElementCount > 0)
{
Aggregation.Add(new BsonElement("$sort", sort));
}
Aggregation.Add(new BsonElement("$project", project));
return(Aggregation);
}
/// <summary>
/// 获取Group的ID
/// </summary>
/// <returns></returns>
public BsonDocument GetAggregationGroup()
{
var Aggregation = new BsonDocument();
var project = new BsonDocument();
foreach (DataFilter.QueryFieldItem item in mQueryFieldList)
{
DataFilter.QueryFieldItem ctl = ((ctlFieldInfo)Controls.Find(item.ColName, true)[0]).QueryFieldItem;
if (ctl.IsShow)
{
project.Add(string.IsNullOrEmpty(ctl.ProjectName)
? new BsonElement(ctl.ColName, ctl.ColName)
: new BsonElement(ctl.ProjectName, "$" + ctl.ColName));
}
}
Aggregation.Add("_id", project);
return(Aggregation);
}
/// <summary>
/// GroupID
/// </summary>
/// <returns></returns>
internal BsonDocument getGroupID()
{
// { _id : { author: '$author', pageViews: '$pageViews', posted: '$posted' } }
var id = new BsonDocument();
var member = new BsonDocument();
foreach (DataFilter.QueryFieldItem item in mQueryFieldList)
{
DataFilter.QueryFieldItem ctl = ((ctlFieldInfo)Controls.Find(item.ColName, true)[0]).QueryFieldItem;
if (ctl.IsShow && ctl.ColName != "_id")
{
member.Add(string.IsNullOrEmpty(ctl.ProjectName)
? new BsonElement(ctl.ColName, "$" + ctl.ColName)
: new BsonElement(ctl.ProjectName, "$" + ctl.ColName));
}
}
id.Add("_id", member);
return(id);
}
/// <summary>
/// </summary>
/// <param name="mIsShow"></param>
public void InitByCurrentCollection(bool mIsShow)
{
List <String> ColumnList = MongoDbHelper.GetCollectionSchame(SystemManager.GetCurrentCollection());
var FieldList = new List <DataFilter.QueryFieldItem>();
foreach (String item in ColumnList)
{
//输出配置的初始化
var queryFieldItem = new DataFilter.QueryFieldItem();
queryFieldItem.ColName = item;
queryFieldItem.IsShow = mIsShow;
queryFieldItem.sortType = DataFilter.SortType.NoSort;
if (queryFieldItem.ColName == MongoDbHelper.KEY_ID)
{
queryFieldItem.IsShow = true;
}
FieldList.Add(queryFieldItem);
}
mQueryFieldList = FieldList;
SetFieldList();
}
/// <summary>
/// </summary>
/// <param name="mIsShow"></param>
public void InitByCurrentCollection(bool mIsShow)
{
var columnList =
MongoHelper.GetCollectionSchame(RuntimeMongoDbContext.GetCurrentCollection());
var fieldList = new List <DataFilter.QueryFieldItem>();
foreach (var item in columnList)
{
//输出配置的初始化
var queryFieldItem = new DataFilter.QueryFieldItem();
queryFieldItem.ColName = item;
queryFieldItem.IsShow = mIsShow;
queryFieldItem.SortType = DataFilter.SortType.NoSort;
if (queryFieldItem.ColName == ConstMgr.KeyId)
{
queryFieldItem.IsShow = true;
}
fieldList.Add(queryFieldItem);
}
_mQueryFieldList = fieldList;
SetFieldList();
}
/// <summary>
/// 输出配置字典
/// </summary>
private void frmQuery_Load(object sender, EventArgs e)
{
ColumnList = MongoDBHelper.GetCollectionSchame(_mongoCol);
foreach (String item in ColumnList)
{
//输出配置的初始化
DataFilter.QueryFieldItem queryFieldItem = new DataFilter.QueryFieldItem();
queryFieldItem.ColName = item;
if (!CurrentDataViewInfo.IsUseFilter)
{
queryFieldItem.IsShow = true;
queryFieldItem.sortType = DataFilter.SortType.NoSort;
}
else
{
DataFilter.QueryFieldItem find = CurrentDataViewInfo.mDataFilter.QueryFieldList.Find
(
(x) => { return(x.ColName == item); }
);
if (find.ColName != String.Empty)
{
queryFieldItem.IsShow = find.IsShow;
queryFieldItem.sortType = find.sortType;
}
}
if (queryFieldItem.ColName == MongoDBHelper.KEY_ID)
{
queryFieldItem.IsShow = true;
}
//动态加载控件
ctlFieldInfo ctrItem = new ctlFieldInfo();
ctrItem.Name = item;
ctrItem.Location = _conditionPos;
ctrItem.QueryFieldItem = queryFieldItem;
tabFieldInfo.Controls.Add(ctrItem);
//纵向位置的累加
_conditionPos.Y += ctrItem.Height;
}
_conditionPos = new Point(5, 20);
ctlQueryCondition firstQueryCtl = new ctlQueryCondition();
firstQueryCtl.Init(ColumnList);
firstQueryCtl.Location = _conditionPos;
firstQueryCtl.Name = "Condition" + _conditionCount.ToString();
panFilter.Controls.Add(firstQueryCtl);
if (CurrentDataViewInfo.mDataFilter.QueryConditionList.Count > 0)
{
PutQueryToUI(CurrentDataViewInfo.mDataFilter);
}
if (!SystemManager.IsUseDefaultLanguage)
{
this.Text = SystemManager.mStringResource.GetText(MagicMongoDBTool.Module.StringResource.TextType.Query_Title);
tabFieldInfo.Text = SystemManager.mStringResource.GetText(MagicMongoDBTool.Module.StringResource.TextType.Query_FieldInfo);
tabCondition.Text = SystemManager.mStringResource.GetText(MagicMongoDBTool.Module.StringResource.TextType.Query_Filter);
cmdAddCondition.Text = SystemManager.mStringResource.GetText(MagicMongoDBTool.Module.StringResource.TextType.Query_Filter_AddCondition);
cmdLoad.Text = SystemManager.mStringResource.GetText(MagicMongoDBTool.Module.StringResource.TextType.Query_Action_Load);
cmdSave.Text = SystemManager.mStringResource.GetText(MagicMongoDBTool.Module.StringResource.TextType.Common_Save);
cmdOK.Text = SystemManager.mStringResource.GetText(MagicMongoDBTool.Module.StringResource.TextType.Common_OK);
cmdCancel.Text = SystemManager.mStringResource.GetText(StringResource.TextType.Common_Cancel);
}
}
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);
}
}
//http://www.mongodb.org/display/DOCS/SQL+to+Mongo+Mapping+Chart(旧网址)
//http://docs.mongodb.org/manual/reference/sql-comparison/
/// <summary>
/// Convert Query Sql To DataFilter
/// </summary>
/// <param name="Sql"></param>
/// <returns></returns>
public static DataFilter ConvertQuerySql(String Sql)
{
var 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 = { "select", "from", "where", "group", "order" };
//From - > CollectionName
GetKeyContent(FromStartIndex, SqlToken, KeyWords);
MongoCollection mongoCol;
//if ((strFrom != String.Empty) & SystemManager.GetCurrentDataBase().CollectionExists(strFrom))
//{
// mongoCol = SystemManager.GetCurrentDataBase().GetCollection(strFrom);
//}
//else
//{
// return null;
//}
//From:由于当前肯定是从固定表里面取数据
mongoCol = SystemManager.GetCurrentCollection();
//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)
{
var 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()))
{
var 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);
}
