public static List <string> GetGroupList(string main, string second) { List <string> group = new List <string>(); if (!string.IsNullOrEmpty(main)) { group.Add(PublicFunctionClass.GetTimePartSqlField(1, main)); if (!string.IsNullOrEmpty(second)) { group.Add(PublicFunctionClass.GetTimePartSqlField(1, second)); } } return(group); }
/// <summary> /// /// </summary> /// <param name="dataSource"></param> /// <param name="bRun">true ,have where</param> /// <returns></returns> public static string GetConfigSql(DataSource dataSource, bool bRun) { if (dataSource == null) { return(""); } string groupby = ""; #region group By if (dataSource.MainGroupBy.Length > 0) { groupby = PublicFunctionClass.GetTimePartSqlField(1, dataSource.MainGroupBy); if (dataSource.SecondaryGroupBy.Length > 0) { groupby += " ," + PublicFunctionClass.GetTimePartSqlField(1, dataSource.SecondaryGroupBy); } } #endregion string orderby = ""; #region oder By if (dataSource.SortList != null && dataSource.SortList.Count > 0) { foreach (var sc in dataSource.SortList) { sc.fieldName = PublicFunctionClass.GetTimePartSqlField(1, sc.fieldName); orderby += sc.ToString() + ","; } if (orderby.Length > 0) { orderby = orderby.Substring(0, orderby.Length - 1); } } #endregion string field = ""; #region Field if (dataSource.MainGroupBy.Length > 0) { //field += dataSource.MainGroupBy.ToLower() + ","; //if (field.IndexOf(dataSource.MainGroupBy.ToLower()) < 0)//group by 字段一定要在select后面 { field += PublicFunctionClass.GetTimePartSqlField(1, dataSource.MainGroupBy) + ","; } if (dataSource.SecondaryGroupBy.Length > 0) { field += PublicFunctionClass.GetTimePartSqlField(1, dataSource.SecondaryGroupBy) + ",";; } } for (int i = 0; i < dataSource.FormulaList.Count; i++) { string itemText = (string)dataSource.FormulaList[i]; //itemText = itemText.ToLower(); itemText = itemText.Trim(); if (dataSource.MainGroupBy.Length > 0) { if (dataSource.MainGroupBy.Equals(itemText, StringComparison.CurrentCultureIgnoreCase) || dataSource.SecondaryGroupBy.Equals(itemText, StringComparison.CurrentCultureIgnoreCase)) { continue; } } field += itemText + ","; } if (field.Length > 0) { field = field.Substring(0, field.Length - 1); } else//未选择字段 { return(""); } #endregion string tables = ""; #region Table int nTable = dataSource.SelectedTableList.Count; if (nTable < 1) { return(""); } string mainTable = (string)(dataSource.SelectedTableList[0]); tables = "[" + mainTable + "]"; if (nTable > 1) { if (dataSource.PmsJoinRelation.Count == 0) { for (int iTable = 1; iTable < nTable; iTable++) { string itemText = (string)dataSource.SelectedTableList[iTable]; itemText.ToLower(); { tables += " cross join [" + itemText + "]"; } } } else { string previewTable = mainTable;//记录上一个表 List <string> tableSelect = new List <string>(); tableSelect.Add(mainTable); while (previewTable.Length > 0) { bool bFind = false; foreach (TableJoinRelation tjr in dataSource.PmsJoinRelation) { if (tjr.mainTable.Equals(previewTable, StringComparison.CurrentCultureIgnoreCase)) { string joinType = ""; //4 inner join,1 left out join, 2 right out join //3 full out join,0 cross join if (tjr.joinType == 0) { joinType = " inner join "; } else if (tjr.joinType == 3) { joinType = " full outer join "; } else if (tjr.joinType == 2) { joinType = " right outer join "; } else if (tjr.joinType == 1) { joinType = " left outer join "; } tables += joinType + "[" + tjr.secondaryTable + "] on [" + tjr.mainTable; tables += "].[" + tjr.mainColumn + "]"; tables += tjr.compare + "[" + tjr.secondaryTable; tables += "].[" + tjr.secondaryColumn + "]"; previewTable = tjr.secondaryTable; if (previewTable.Equals(mainTable, StringComparison.CurrentCultureIgnoreCase))//怕形成死循环 { previewTable = ""; break; } tableSelect.Add(previewTable); bFind = true; break; } } if (bFind == false) { previewTable = ""; } } if (tableSelect.Count < dataSource.SelectedTableList.Count) { for (int it = 1; it < nTable; it++) { string tableNoSelect = (string)(dataSource.SelectedTableList[it]); bool bSelected = false; foreach (string tableHaveSelected in tableSelect) { if (tableNoSelect.Equals(tableHaveSelected, StringComparison.CurrentCultureIgnoreCase))//已选 { bSelected = true; break; } } if (bSelected == true) { continue; } else { tables += " cross join [" + tableNoSelect + "]"; } } } } } #endregion string having = ""; string where = ""; if (bRun) { #region Where if (dataSource.SelectedTableList != null && dataSource.SelectedTableList.Count == 1) { int ireturn = PMS.Libraries.ToolControls.PmsSheet.WhereLibrary.TreeViewDataAccess.GetSQLWhere(dataSource.WhereData.Clone(), dataSource.SelectedTableList[0], ref where, ref having); if (ireturn == 2) { return(where); } } else if (dataSource.SelectedTableList != null && dataSource.SelectedTableList.Count > 1) { int ireturn = PMS.Libraries.ToolControls.PmsSheet.WhereLibrary.TreeViewDataAccess.GetSQLWhere(dataSource.WhereData.Clone(), ref where, ref having); if (ireturn == 2) { return(where); } } #endregion } string returnSql = "select " + field + " from " + tables; if (where.Length > 0) { returnSql += " where " + where; } bool bGroup = false; if (groupby.Length > 0) { returnSql += " group by " + groupby; bGroup = true; } if (having.Length > 0 && bGroup) { returnSql += " having " + having; } if (orderby.Length > 0) { returnSql += " order by " + orderby; } return(returnSql); }