private static string CreateQuery(ExcelQuery.ExcelQuery query)
{
const string pattern = @"SELECT ROW_NUMBER() OVER(ORDER BY {0} {1}) AS RowNumber,* FROM {3} WHERE 1=1 {2} ";
var sb = new StringBuilder();
string cond = GetSearchItemCondition(query);
sb.AppendFormat(pattern, Query.EncloseInBracket(query.SortOrderItem), query.SortOrderType, cond, query.ViewName);
return sb.ToString();
}
public static string GetExcelQuery(ExcelQuery.ExcelQuery query, string baseQuery)
{
string outPutHeader = string.Empty;
string outPutBody = string.Empty;
string withBody = "WITH cte AS (" + baseQuery + ")";
foreach (ExcelQueryColumn col in query.ExcelColumns)
{
outPutHeader += "'" + col.ExcelColumn + "' AS 'th',";
outPutBody += string.Format(" {0} AS 'td',", col.DataBaseColumn);
}
outPutHeader = outPutHeader.TrimEnd(',');
outPutBody = outPutBody.TrimEnd(',');
outPutHeader = "SELECT" + outPutHeader + " FOR XML RAW('tr'),ELEMENTS,TYPE";
outPutHeader = "SELECT (" + outPutHeader + ") FOR XML RAW('thead'),ELEMENTS,TYPE";
outPutBody = "SELECT " + outPutBody + " FROM cte FOR XML RAW('tr'),ELEMENTS,TYPE ";
outPutBody = "SELECT (" + outPutBody + ") FOR XML RAW('tbody'),ELEMENTS,TYPE";
string outPutQuery;
if (query.CompressInDataBase && !string.IsNullOrEmpty(query.CompressFunctionName))
{
outPutQuery = withBody + " SELECT ref.fn_compress(CONVERT (varbinary(max),(SELECT '1' border,(" +
outPutHeader + "), (" + outPutBody + ") FOR XML RAW ('table'),TYPE)))";
}
else
{
outPutQuery = withBody + " SELECT (" + outPutHeader + "), (" + outPutBody +
") FOR XML PATH ('table'),TYPE";
}
return outPutQuery;
}
public static string GetExcelQuery(ExcelQuery.ExcelQuery query)
{
string baseQuery = CreateQuery(query);
return GetExcelQuery(query, baseQuery);
}
