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());
}
//
// GET: /GridData/
public string GetData(string ID)
{
FieldGroup fieldGroup = new FieldGroup(ID);
FieldGroupDetailCollection fields = fieldGroup.GetFieldsByUser(AccessControl.CurrentLogonUser.Uid);
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, "RFQID", "StatusID");
if (fieldGroup.SourceName == "V_Supplier")
{
strSql = GridManager.GetQuerySql(fieldGroup.SourceName, fields, "ID");
}
GridData gridData = GridManager.GetGridData(Request, strSql, fieldGroup.Authority);
return gridData.ToJson(formatString.ToArray());
}
public static string GetExportDataForVVI(HttpRequestBase request, FieldGroup fieldGroup)
{
DataSet ds = new DataSet();
FieldGroupDetailCollection fields = fieldGroup.GetFieldsByUser(AccessControl.CurrentLogonUser.Uid);
// FieldGroupDetailCollection mainFields = new FieldGroupDetailCollection();
FieldGroupDetailCollection allFields = new FieldGroupDetailCollection();
// Dictionary<string, FieldGroupDetailCollection> subFields = new Dictionary<string, FieldGroupDetailCollection>();
string strSql = GridManager.GetQuerySql(fieldGroup.SourceName, fieldGroup.GetFieldsByUser(AccessControl.CurrentLogonUser.Uid));
FieldGroupDetailCollection relationFields = new FieldGroupDetailCollection();
if (!String.IsNullOrEmpty(fieldGroup.SubRelationFields))
{
string[] rfa = fieldGroup.SubRelationFields.Split(',');
if (rfa != null && rfa.Length > 0)
{
foreach (string rf in rfa)
{
FieldGroupDetail rfd = fields.Find(t => String.Compare(t.FieldName, rf.Trim(), true) == 0);
if (rfd != null)
{
relationFields.Add(rfd);
}
}
}
}
foreach (FieldGroupDetail f in fields)
{
allFields.Add(f);
}
List<SqlParameter> lstParams = new List<SqlParameter>();
string strWhere = GridManager.GetWhereSql(request, fieldGroup.GroupName, allFields, lstParams);
if (fieldGroup.Authority)
{
strWhere += GridManager.GetWhereSqlPermission(lstParams);
}
StringBuilder mainSql = new StringBuilder("SELECT ");
foreach (FieldGroupDetail field in allFields)
{
mainSql.AppendFormat("[{0}] AS [{1}],", field.FieldName, field.DisplayName);
}
mainSql.Remove(mainSql.Length - 1, 1);
mainSql.AppendFormat(" FROM {0} AS t", fieldGroup.SourceName);
mainSql.Append(" WHERE 1=1 ").Append(strWhere);
DataTable mainTable = DbHelperSQL.Query(mainSql.ToString(), lstParams.ToArray()).Tables[0].Copy();
mainTable.TableName = "Primary";
ds.Tables.Add(mainTable);
//StringBuilder subSql = new StringBuilder();
//foreach (KeyValuePair<string, FieldGroupDetailCollection> kv in subFields)
//{
// subSql.Clear();
// subSql.Append("SELECT ");
// foreach (FieldGroupDetail field in relationFields)
// {
// subSql.AppendFormat("t1.[{0}] AS [{1}],", field.FieldName, field.DisplayName);
// }
// foreach (FieldGroupDetail field in kv.Value)
// {
// subSql.AppendFormat("t2.[{0}] AS [{1}],", field.FieldName, field.DisplayName);
// }
// subSql.Remove(subSql.Length - 1, 1);
// subSql.AppendFormat(" FROM {0} t1, SGP_SubData t2 WHERE t1.{1}=t2.EntityID AND EntityName = '{2}' {3}", fieldGroup.SourceName, fieldGroup.SourceKey, kv.Key, strWhere);
// DataTable subTable = DbHelperSQL.Query(subSql.ToString(), lstParams.ToArray()).Tables[0].Copy();
// subTable.TableName = kv.Key;
// ds.Tables.Add(subTable);
//}
RenderType rt = request.QueryString["renderType"] == "2" ? RenderType.Vertical : RenderType.Horizontal;
return ExcelHelper.DataSetToExcel(ds, rt);
}
public static List<FieldGroup> GetExcelGroups()
{
string strSql = @"SELECT GroupName FROM SYS_FieldGroup WHERE GroupType = '" + GROUP_TYPE_EXCEL + "' ORDER BY Sort";
DataTable dt = DbHelperSQL.Query(strSql).Tables[0];
List<FieldGroup> list = new List<FieldGroup>();
foreach (DataRow dr in dt.Rows)
{
FieldGroup fg = new FieldGroup(dr["GroupName"].ToString());
list.Add(fg);
}
return list;
}
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 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 static GridColumns GetGridFields(string listName, string uid)
{
FieldGroup fieldGroup = new FieldGroup(listName);
FieldGroupDetailCollection fieldDetails = fieldGroup.GetFieldsByUser(uid);
GridColumns gridColumns = new GridColumns();
List<string> colNames = new List<string>();
List<GridColumnModel> colModels = new List<GridColumnModel>();
foreach (FieldGroupDetail field in fieldDetails)
{
colNames.Add(field.DisplayName);
GridColumnModel model = new GridColumnModel();
model.name = field.FieldName;
model.index = field.FieldName;
model.width = field.Width_Detail > 0 ? field.Width_Detail : field.Width;
model.align = field.Align;
colModels.Add(model);
}
return new GridColumns
{
colNames = colNames,
colModel = colModels
};
}
public static GridData GetGridData(HttpRequestBase request, string strSql, FieldGroup fieldGroup)
{
string strWhere = "";
List<SqlParameter> listParames = new List<SqlParameter>();
string searchGroupName = request.QueryString["searchGroup"];
if (!String.IsNullOrEmpty(searchGroupName))
{
strWhere += GetWhereSql(request, searchGroupName, listParames);
}
if (fieldGroup.Authority)
{
strWhere += GetWhereSqlList(fieldGroup.GroupName, listParames);
}
if (strWhere != "")
{
strSql += " WHERE 1= 1" + strWhere;
}
return GetGridData(request, strSql, listParames.ToArray());
}
public JsonResult SaveListFields(FieldsSettingModel postModel)
{
string errMessage = "";
try
{
FieldGroup fg = new FieldGroup(postModel.ListName);
fg.SaveUserFieldGroup(AccessControl.CurrentLogonUser.Uid, postModel);
}
catch (Exception ex)
{
errMessage = ex.Message;
}
var result = new
{
success = (errMessage == "" ? true : false),
errMessage = errMessage
};
return Json(result);
}
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 GetAllFPCCategoryAndFields(string ID)
{
List<FieldCategory> list = FieldCategory.GetCategorys(FieldCategory.Category_TYPE_FPC, FieldCategory.Category_TYPE_WORKFLOW);
FieldGroup fieldGroup = new FieldGroup(ID);
FieldGroupDetailCollection fgdc = fieldGroup.GetFieldsByUser(AccessControl.CurrentLogonUser.Uid);
string strJson = "[";
foreach (FieldCategory fc in list)
{
string strCategory = "";
string strFields = "";
FieldInfoCollecton fic = fc.Fields;
foreach (FieldInfo fi in fic)
{
if (fgdc[fi.FieldName] == null && fi.Enable == 1 && String.IsNullOrEmpty(fi.SubDataType))
{
strFields += "{\"id\":\"" + fi.ID + "\",\"text\":\"" + fi.DisplayName.Replace("<br />", " ") + "\", \"iconCls\":\"icon-ok\"},";
}
}
strFields = strFields.TrimEnd(',');
if (!String.IsNullOrEmpty(strFields))
{
strCategory = "{\"id\":\"" + fc.CategoryName + "\", \"IsCategory\":true, \"text\":\"" + fc.CategoryName + "\", \"iconCls\":\"icon-ok\", \"children\":[" + strFields + "]},";
}
strJson += strCategory;
}
strJson = strJson.TrimEnd(',');
strJson += "]";
return strJson;
}
public string GetGridData()
{
FieldGroup fieldGroup = new FieldGroup(Request["groupName"]);
string[] extSqlColumns = String.IsNullOrEmpty(Request["extSqlColumns"]) ? null : Request["extSqlColumns"].Split(',');
FieldGroupDetailCollection fields = fieldGroup.GetFieldsByUser(AccessControl.CurrentLogonUser.Uid);
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);
GridData gridData;
//Filter data for SupplierPricingView
if (AccessControl.IsVendor() &&
(Request["groupName"].ToString().ToUpper() == "SUPPLIERRFQGRID" ||
Request["groupName"].ToString().ToUpper() == "VENDORRFQREPORTGRID"))
{
gridData = GridManager.GetGridData(Request, strSql, fieldGroup.Authority, fields);
}
else
{
if (Request["groupName"].ToString().ToUpper() == "CUSTOMERPROFILEGRID")
{
gridData = GridManager.GetGridData(Request, strSql, fieldGroup);
}
else
{
gridData = GridManager.GetGridData(Request, strSql, fieldGroup.Authority);
}
}
return gridData.ToJson(formatString.ToArray());
}
public static string GetExportData(HttpRequestBase request, FieldGroup fieldGroup)
{
DataSet ds = new DataSet();
FieldGroupDetailCollection fields = fieldGroup.GetFieldsByUser(AccessControl.CurrentLogonUser.Uid);
FieldGroupDetailCollection mainFields = new FieldGroupDetailCollection();
Dictionary<string, FieldGroupDetailCollection> subFields = new Dictionary<string, FieldGroupDetailCollection>();
string strSql = GridManager.GetQuerySql(fieldGroup.SourceName, fieldGroup.GetFieldsByUser(AccessControl.CurrentLogonUser.Uid));
FieldGroupDetailCollection relationFields = new FieldGroupDetailCollection();
if (!String.IsNullOrEmpty(fieldGroup.SubRelationFields))
{
string[] rfa = fieldGroup.SubRelationFields.Split(',');
if (rfa != null && rfa.Length > 0)
{
foreach (string rf in rfa)
{
FieldGroupDetail rfd = fields.Find(t => String.Compare(t.FieldName, rf.Trim(), true) == 0);
if (rfd != null)
{
relationFields.Add(rfd);
}
}
}
}
foreach (FieldGroupDetail f in fields)
{
if (String.IsNullOrEmpty(f.SubDataType))
{
mainFields.Add(f);
}
else
{
if (!subFields.ContainsKey(f.SubDataType))
{
subFields.Add(f.SubDataType, new FieldGroupDetailCollection());
}
subFields[f.SubDataType].Add(f);
}
}
List<SqlParameter> lstParams = new List<SqlParameter>();
string strWhere = GridManager.GetWhereSql(request, fieldGroup.GroupName, mainFields, lstParams);
if (fieldGroup.Authority)
{
strWhere += GridManager.GetWhereSqlPermission(lstParams);
}
StringBuilder mainSql = new StringBuilder("SELECT ");
foreach (FieldGroupDetail field in mainFields)
{
mainSql.AppendFormat("[{0}] AS [{1}],", field.FieldName, field.DisplayName);
}
mainSql.Remove(mainSql.Length - 1, 1);
mainSql.AppendFormat(" FROM {0} AS t", fieldGroup.SourceName);
mainSql.Append(" WHERE 1=1 ").Append(strWhere);
if (AccessControl.IsVendor() &&
(fieldGroup.GroupName.ToUpper() == "VENDORRFQREPORTGRID" ||
fieldGroup.GroupName.ToUpper() == "SUPPLIERRFQGRID"))
{
string uId = AccessControl.CurrentLogonUser.Uid;
mainSql.Append(" AND RIGHT(NVARCHAR1, CHARINDEX('-', REVERSE(NVARCHAR1)) - 1) = @UserId");
lstParams.Add(new SqlParameter("@UserId", uId));
}
DataTable mainTable = DbHelperSQL.Query(mainSql.ToString(), lstParams.ToArray()).Tables[0].Copy();
mainTable.TableName = "Primary";
ds.Tables.Add(mainTable);
StringBuilder subSql = new StringBuilder();
foreach (KeyValuePair<string, FieldGroupDetailCollection> kv in subFields)
{
subSql.Clear();
subSql.Append("SELECT ");
foreach (FieldGroupDetail field in relationFields)
{
subSql.AppendFormat("t1.[{0}] AS [{1}],", field.FieldName, field.DisplayName);
}
foreach (FieldGroupDetail field in kv.Value)
{
subSql.AppendFormat("t2.[{0}] AS [{1}],", field.FieldName, field.DisplayName);
}
subSql.Remove(subSql.Length - 1, 1);
subSql.AppendFormat(" FROM {0} t1, SGP_SubData t2 WHERE t1.{1}=t2.EntityID AND EntityName = '{2}' {3}", fieldGroup.SourceName, fieldGroup.SourceKey, kv.Key, strWhere);
DataTable subTable = DbHelperSQL.Query(subSql.ToString(), lstParams.ToArray()).Tables[0].Copy();
subTable.TableName = kv.Key;
ds.Tables.Add(subTable);
}
RenderType rt = request.QueryString["renderType"] == "2" ? RenderType.Vertical : RenderType.Horizontal;
return ExcelHelper.DataSetToExcel(ds, rt);
}
public static string GetWhereSql(HttpRequestBase request, string searchGroupName, List<SqlParameter> listParames)
{
FieldGroup searchGroup = new FieldGroup(searchGroupName);
FieldGroupDetailCollection fields = searchGroup.GetFieldsByUser(AccessControl.CurrentLogonUser.Uid);
return GetWhereSql(request, searchGroupName, fields, listParames);
}
public static string GenerateQuery(string groupName)
{
StringBuilder strResult = new StringBuilder();
strResult.Append("<table style='width:100%' id='query-" + groupName + "'><tr>");
FieldGroup fieldGroup = new FieldGroup(groupName);
FieldGroupDetailCollection fieldDetails = fieldGroup.GetFieldsByUser(AccessControl.CurrentLogonUser.Uid);
for (int i = 0; i < fieldDetails.Count; i++)
{
if (i > 1 && (i % 4) == 0)
{
strResult.Append("</tr><tr>");
}
strResult.AppendFormat("<td style='width:8%' align='right'> {0} </td><td style='width:17%'>", fieldDetails[i].DisplayName);
switch (fieldDetails[i].DataType)
{
case BLL.DataModels.FieldInfo.DATATYPE_DATETIME:
strResult.Append(GenerateQueryDateRange(fieldDetails[i]));
break;
case BLL.DataModels.FieldInfo.DATATYPE_INT:
case BLL.DataModels.FieldInfo.DATATYPE_FLOAT:
case BLL.DataModels.FieldInfo.DATATYPE_DOUBLE:
strResult.Append(GenerateQueryNumberBox(fieldDetails[i]));
break;
case BLL.DataModels.FieldInfo.DATATYPE_LIST:
strResult.Append(GenerateQueryList(fieldDetails[i]));
break;
case BLL.DataModels.FieldInfo.DATATYPE_ACTIVITY:
strResult.Append(GenerateQueryActivity(fieldDetails[i]));
break;
case "ext":
strResult.Append(GenerateQueryExt(fieldDetails[i]));
break;
default:
strResult.Append(GenerateQueryTextBox(fieldDetails[i]));
break;
}
strResult.Append("</td>");
}
strResult.AppendFormat("</tr></table><input type='hidden' id='searchGroup' name='searchGroup' value='{0}' />", fieldGroup.GroupName);
return strResult.ToString();
}
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");
}