public ActionResult List(string ID)
{
TableParams tp = new TableParams(ID);
ViewBag.Category = new FieldCategory(ID);
ViewBag.TableParams = tp;
ViewBag.Title = "<li class='active'>" + tp.DisplayName + "</li>";
return View();
}
public ActionResult History(string ID)
{
TableParams tp = new TableParams(ID);
ViewBag.Category = new FieldCategory(ID);
ViewBag.TableParams = tp;
ViewBag.Title = "<li>History</li> <li class='active'>" + tp.DisplayName + "</li>";
ViewBag.History = "yes";
return View("List");
}
public FileResult DownloadTemplate(string ID)
{
TableParams tp = new TableParams(ID);
string tempFile = ExcelHelper.ExportSCMasterTemplate(ID);
return File(tempFile, "application/ms-excel", tp.DisplayName.Replace(" ", "_") + ".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 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");
}