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