public ActionResult GenerateQuery()
{
string groupName = Request["listName"];
StringBuilder strResult = new StringBuilder();
strResult.Append("<table style='width:100%' id='query-" + groupName + "'><tr>");
FieldGroup fieldGroup = new FieldGroup(groupName, "Search");
FieldGroupDetailCollection fieldDetails = fieldGroup.GetDefaultFields();
int detailCount = fieldDetails.Count < 3 ? 3 : fieldDetails.Count;
for (int i = 0; i < detailCount; i++)
{
if (i > 1 && (i % 3) == 0)
{
strResult.Append("</tr><tr>");
}
if (i >= fieldDetails.Count)
{
strResult.Append("<td style='width:10%' align='right'> </td><td style='width:23%'></td>");
}
else
{
strResult.AppendFormat("<td style='width:10%;{1}' align='right'> {0} </td><td style='width:23%'>", fieldDetails[i].DisplayName, fieldDetails[i].FieldName == "Version" ? "color:red" : "");
switch (fieldDetails[i].DataType)
{
case BLL.DataModels.FieldInfo.DATATYPE_DATETIME:
case BLL.DataModels.FieldInfo.DATATYPE_DATE:
strResult.Append(UIManager.GenerateQueryDateRange(fieldDetails[i]));
break;
case BLL.DataModels.FieldInfo.DATATYPE_INT:
case BLL.DataModels.FieldInfo.DATATYPE_FLOAT:
case BLL.DataModels.FieldInfo.DATATYPE_DOUBLE:
strResult.Append(UIManager.GenerateQueryNumberBox(fieldDetails[i]));
break;
case BLL.DataModels.FieldInfo.DATATYPE_LIST:
strResult.Append(UIManager.GenerateQueryList(fieldDetails[i]));
break;
case BLL.DataModels.FieldInfo.DATATYPE_LIST_SQL:
strResult.Append(UIManager.GenerateQueryListSql(fieldDetails[i]));
break;
case BLL.DataModels.FieldInfo.DATATYPE_ACTIVITY:
strResult.Append(UIManager.GenerateQueryActivity(fieldDetails[i]));
break;
case "ext":
strResult.Append(UIManager.GenerateQueryExt(fieldDetails[i]));
break;
default:
strResult.Append(UIManager.GenerateQueryTextBox(fieldDetails[i]));
break;
}
strResult.Append("</td>");
}
}
strResult.AppendFormat("</tr></table><input type='hidden' id='searchGroup' name='searchGroup' value='{0}' />", fieldGroup.GroupName);
return Content(strResult.ToString());
}
public string GetGridData()
{
FieldGroup fieldGroup = new FieldGroup(Request["searchGroup"]);
FieldGroupDetailCollection fields = fieldGroup.GetDefaultFields();
List<TableFormatString> formatString = new List<TableFormatString>();
foreach (FieldGroupDetail field in fields)
{
if (!String.IsNullOrEmpty(field.Format))
{
formatString.Add(new TableFormatString(field.FieldName, field.Format));
}
}
string[] versions = Request["Version"].Split(';', ',');
foreach (string vs in versions)
{
formatString.Add(new TableFormatString(vs, "{0:F10}"));
}
string strSql = GetQuerySql(Request);
string strWhere = "";
List<SqlParameter> listParames = new List<SqlParameter>();
string searchGroupName = Request.QueryString["searchGroup"];
if (!String.IsNullOrEmpty(searchGroupName))
{
strWhere += GridManager.GetWhereSql(Request, searchGroupName, listParames);
}
strSql += " WHERE 1=1" + strWhere;
GridData gridData = GridManager.GetGridData(Request, strSql, listParames.ToArray());
return gridData.ToJson(formatString.ToArray());
}
public string GetGridData()
{
FieldGroup fieldGroup = new FieldGroup(Request["groupName"]);
string pageType = Request["pageType"];
string[] extSqlColumns = String.IsNullOrEmpty(Request["extSqlColumns"]) ? null : Request["extSqlColumns"].Split(',');
FieldGroupDetailCollection fields = fieldGroup.GetDefaultFields();
List<TableFormatString> formatString = new List<TableFormatString>();
foreach (FieldGroupDetail field in fields)
{
if (!String.IsNullOrEmpty(field.Format))
{
formatString.Add(new TableFormatString(field.FieldName, field.Format));
}
}
string strSql = GridManager.GetQuerySql(fieldGroup.SourceName, fields, extSqlColumns);
string strWhere;
List<SqlParameter> listParames;
GenerateStrWhere(pageType, out strWhere, out listParames);
strSql += " WHERE 1=1" + strWhere;
GridData gridData = GridManager.GetGridData(Request, strSql, listParames.ToArray());
return gridData.ToJson(formatString.ToArray());
}
public FileResult DownloadExcel()
{
string groupName = Request.QueryString["groupName"];
string pageType = Request.QueryString["pageType"];
FieldGroup fieldGroup = new FieldGroup(groupName);
FieldGroupDetailCollection fields = fieldGroup.GetDefaultFields();
string strSql = "select ";
string strWhere = " where 1=1 ";
List<SqlParameter> parameters = new List<SqlParameter>();
foreach (FieldGroupDetail field in fields)
{
strSql += String.Format("[{0}] AS [{1}],", field.FieldName, field.DisplayName);
}
strSql = strSql.TrimEnd(',') + String.Format(" FROM {0} AS t", fieldGroup.SourceName);
string searchGroupName = Request.QueryString["searchGroup"];
if (!String.IsNullOrEmpty(searchGroupName))
{
strWhere += GridManager.GetWhereSql(Request, searchGroupName, parameters);
}
strSql = strSql + strWhere;
DataSet ds = DbHelperSQL.Query(strSql, parameters.ToArray());
RenderType rt = Request.QueryString["renderType"] == "2" ? RenderType.Vertical : RenderType.Horizontal;
string tempFile = ExcelHelper.DataSetToExcel(ds, rt);
return File(tempFile, "application/ms-excel", pageType + DateTime.Now.ToString("yyyyMMddHHmmssffff") + ".xlsx");
}
public string GetUserListFields(string ID)
{
string strJson = "[";
FieldGroup fieldGroup = new FieldGroup(ID);
FieldGroupDetailCollection fgdc = fieldGroup.GetFieldsByUser(AccessControl.CurrentLogonUser.Uid);
if (fgdc.Count == 0)
{
fgdc = fieldGroup.GetDefaultFields();
}
foreach (FieldGroupDetail fgd in fgdc)
{
strJson += "{\"id\":\"" + fgd.ID + "\",\"text\":\"" + fgd.DisplayName.Replace("<br />", " ") + "\", \"iconCls\":\"icon-ok\"},";
}
strJson = strJson.TrimEnd(',');
strJson += "]";
return strJson;
}
public string GetGridData()
{
FieldGroup fieldGroup = new FieldGroup(Request["groupName"]);
TableParams tableParams = new TableParams(Request["groupName"]);
string[] extSqlColumns = String.IsNullOrEmpty(Request["extSqlColumns"]) ? null : Request["extSqlColumns"].Split(',');
FieldGroupDetailCollection fields = fieldGroup.GetDefaultFields();
List<TableFormatString> formatString = new List<TableFormatString>();
foreach (FieldGroupDetail field in fields)
{
if (!String.IsNullOrEmpty(field.Format))
{
formatString.Add(new TableFormatString(field.FieldName, field.Format));
}
}
formatString.Add(new TableFormatString("ExpiryDate", "{0:d-MMM-yyyy HH:mm:ss}"));
string strSql = GridManager.GetQuerySql(fieldGroup.SourceName, fields, extSqlColumns);
string strWhere = "";
List<SqlParameter> listParames = new List<SqlParameter>();
string searchGroupName = Request.QueryString["searchGroup"];
if (!String.IsNullOrEmpty(searchGroupName))
{
strWhere += GridManager.GetWhereSql(Request, searchGroupName, listParames);
}
if (tableParams.TableType == TableParams.TableType_PriceMaster)
{
if (Request["historyPage"] == "yes")
{
strSql += " WHERE ExpiryDate < GETDATE() " + strWhere;
}
else
{
strSql += " WHERE EffectiveDate < GETDATE() AND ExpiryDate > GETDATE() " + strWhere;
}
}
else
{
strSql += " WHERE 1=1" + strWhere;
}
GridData gridData = GridManager.GetGridData(Request, strSql, listParames.ToArray());
return gridData.ToJson(formatString.ToArray());
}
public FileResult DownloadExcel()
{
FieldGroup fieldGroup = new FieldGroup(Request.QueryString["excelList"]);
TableParams tableParams = new TableParams(fieldGroup.GroupName);
FieldGroupDetailCollection fields = fieldGroup.GetDefaultFields();
string strSql = "SELECT ";
string strWhere = " WHERE 1=1 ";
List<SqlParameter> listParames = new List<SqlParameter>();
if (tableParams.TableType == TableParams.TableType_PriceMaster)
{
strSql += "ID,";
if (Request["historyPage"] == "yes")
{
strSql += "ExpiryDate AS [Expiry Date], ";
strWhere += " AND ExpiryDate < GETDATE() ";
}
else
{
strWhere += " AND EffectiveDate < GETDATE() AND ExpiryDate > GETDATE() ";
}
}
foreach (FieldGroupDetail field in fields)
{
if (field.DataType == FieldInfo.DATATYPE_SUMMARY)
{
strSql += String.Format("({0}) AS [{1}],", field.KeyValueSource, field.DisplayName);
}
else
{
strSql += String.Format("[{0}] AS [{1}],", field.FieldName, field.DisplayName);
}
}
if (tableParams.TableType == TableParams.TableType_PriceMaster && Request["historyPage"] == "yes")
{
strSql += "CreatorName AS Creator,";
}
strSql = strSql.TrimEnd(',') + String.Format(" FROM {0} AS t", fieldGroup.SourceName);
string searchGroupName = Request.QueryString["searchGroup"];
if (!String.IsNullOrEmpty(searchGroupName))
{
strWhere += GridManager.GetWhereSql(Request, searchGroupName, listParames);
}
strSql = strSql + strWhere;
DataSet ds = DbHelperSQL.Query(strSql, listParames.ToArray());
RenderType rt = Request.QueryString["renderType"] == "2" ? RenderType.Vertical : RenderType.Horizontal;
string tempFile = ExcelHelper.DataSetToExcel(ds, rt);
return File(tempFile, "application/ms-excel", tableParams.DisplayName.Replace(" ", "_") + ".xlsx");
}