public static string GetFieldNameType(string REPORT_CODE, string SQL_FROM)
{
DBManagerFactory dbManagerFactory = new DBManagerFactory();
IOBFunctions iOBFunctions = dbManagerFactory.GetDBManager(REPORT_CODE);
return(iOBFunctions.GetFieldNameType(SQL_FROM));
}
internal static string CheckGroupBySelectValidation(string REPORT_CODE, string SQL_FROM, string QB_GB_SELECT_CLAUSE)
{
DBManagerFactory dbManagerFactory = new DBManagerFactory();
IOBFunctions iOBFunctions = dbManagerFactory.GetDBManager(REPORT_CODE);
return(iOBFunctions.CheckGroupBySelectValidation(REPORT_CODE, SQL_FROM, QB_GB_SELECT_CLAUSE));
}
public static string GetGroupByGridData(string REPORT_CODE, string FIELD_CAPS, string SQL_SELECT,
string SQL_FROM, string SQL_WHERE, string SQL_ORDER_BY, string SQL_ORDER_DIR, string START_ROW,
string PAGE_SIZE, string SQL_GROUP_BY, string MULTI_SELECT, string QB_GB_SELECT_CLAUSE, string GIS_THEME_LAYER)
{
string reportData = string.Empty;
try
{
DBManagerFactory dbManagerFactory = new DBManagerFactory();
IOBFunctions iOBFunctions = dbManagerFactory.GetDBManager(REPORT_CODE);
StringBuilder stringBuilder = new StringBuilder();
stringBuilder.Append("{");
int rowCount = iOBFunctions.GetGroupByGridRowCount(SQL_GROUP_BY, SQL_WHERE, SQL_FROM);
stringBuilder.Append("\"rowCount\":\"" + rowCount + "\",");
stringBuilder.Append(PrepareJsonForGroupGrid(SQL_SELECT, SQL_GROUP_BY, SQL_FROM, SQL_WHERE, START_ROW, iOBFunctions, PAGE_SIZE, SQL_ORDER_BY, SQL_ORDER_DIR, QB_GB_SELECT_CLAUSE, GIS_THEME_LAYER, REPORT_CODE, MULTI_SELECT));
stringBuilder.Append("}");
reportData = stringBuilder.ToString();
}
catch (Exception ex)
{
LogWriter.WriteLog(ex.Message);
reportData = ex.StackTrace;
}
return(reportData);
}
public static string GetNormalGridData(string REPORT_CODE, string FIELD_CAPS, string SQL_SELECT,
string SQL_FROM, string SQL_WHERE, string SQL_ORDER_BY, string SQL_ORDER_DIR, string START_ROW,
string PAGE_SIZE, string SQL_MANDATORY, string MULTI_SELECT,
string FUNCTION_LIST)
{
string reportData = string.Empty;
try
{
DBManagerFactory dbManagerFactory = new DBManagerFactory();
IOBFunctions iOBFunctions = dbManagerFactory.GetDBManager(REPORT_CODE);
StringBuilder stringBuilder = new StringBuilder();
stringBuilder.Append("{");
int rowCount = iOBFunctions.GetNormalGridRowCount(SQL_FROM, SQL_WHERE);
stringBuilder.Append("\"rowCount\":\"" + rowCount + "\",");
stringBuilder = PrepareJsonForNormalGrid(stringBuilder, iOBFunctions, SQL_FROM, SQL_WHERE, SQL_SELECT, START_ROW, PAGE_SIZE, SQL_MANDATORY, MULTI_SELECT, FUNCTION_LIST, SQL_ORDER_BY, SQL_ORDER_DIR);
stringBuilder.Append("}");
reportData = stringBuilder.ToString();
}
catch (Exception ex)
{
LogWriter.WriteLog(ex.Message);
reportData = ex.StackTrace;
}
return(reportData);
}
public static string ValidateWhereClause(string REPORT_CODE, string SQL_FROM, string WHERE_CLAUSE)
{
StringBuilder sb = new StringBuilder();
//WHERE_CLAUSE = GetRectifiedWhereClause(WHERE_CLAUSE);
DBManagerFactory dbManagerFactory = new DBManagerFactory();
IOBFunctions iOBFunctions = dbManagerFactory.GetDBManager(REPORT_CODE);
return(iOBFunctions.ValidateWhereClause(SQL_FROM, WHERE_CLAUSE));
}
public static string CheckCustomFieldValidation(string REPORT_CODE, string SQL_FROM, string customFields)
{
StringBuilder sb = new StringBuilder();
DBManagerFactory dbManagerFactory = new DBManagerFactory();
IOBFunctions iOBFunctions = dbManagerFactory.GetDBManager(REPORT_CODE);
return(iOBFunctions.CheckCustomFieldValidation(REPORT_CODE, SQL_FROM, customFields));
}
protected void Page_Load(object sender, EventArgs e)
{
string table = Request.QueryString["table"].Replace("@@@", "\"");
string fields = Request.QueryString["fields"];
string whereclause = Request.QueryString["whereclause"];
string groupby = Request.QueryString["groupby"];
string orderby = Request.QueryString["orderby"];
string reportcode = Request.QueryString["reportcode"];
string reportExtension = Request.QueryString["repExtension"].ToLower();
string selectSql = "";
if (table != "" && fields != "")
{
selectSql = " select " + fields + " from " + table;
if (whereclause != "")
{
selectSql = selectSql + " where " + whereclause + " ";
}
if (groupby.ToUpper() != "NONE" && groupby != "")
{
selectSql = selectSql + " order by " + groupby;
}
if (orderby != "")
{
if (selectSql.ToLower().Contains("order by"))
{
selectSql = selectSql + " , " + orderby;
}
else
{
selectSql = selectSql + " order by " + orderby;
}
}
}
HIT.OB.STD.Core.BLL.DBManagerFactory Factory = new HIT.OB.STD.Core.BLL.DBManagerFactory();
HIT.OB.STD.Core.DAL.IOBFunctions obFunctions = Factory.GetDBManager(reportcode);
DataTable dt = obFunctions.GetDataTable(selectSql);
if (reportExtension.Equals("xls"))
{
ExportExcel(dt, reportcode);
}
else if (reportExtension.Equals("csv"))
{
ExportCSV(dt, reportcode);
}
}
public static string CheckCustomFieldValidation(string REPORT_CODE, string SQL_FROM, string customFields, string gruopBy)
{
StringBuilder sb = new StringBuilder();
DBManagerFactory dbManagerFactory = new DBManagerFactory();
IOBFunctions iOBFunctions = dbManagerFactory.GetDBManager(REPORT_CODE);
return(iOBFunctions.CheckCustomFieldValidation(REPORT_CODE, SQL_FROM, customFields, gruopBy));
//SELECT file_date ,file_date||'eeeeeee' as name,500 as nr FROM va_item_hit_v GROUP BY file_date
//sb.ToString().Split(
}
public static string GetFieldValues(string REPORT_CODE, string SQL_FROM, string FIELD_NAME)
{
StringBuilder sb = new StringBuilder();
DBManagerFactory dbManagerFactory = new DBManagerFactory();
IOBFunctions iOBFunctions = dbManagerFactory.GetDBManager(REPORT_CODE);
ArrayList filedvalues = iOBFunctions.GetFieldValues(SQL_FROM, FIELD_NAME);
foreach (object value in filedvalues)
{
sb.Append(value.ToString() + "###");
}
return(sb.ToString().Trim('#'));
}
/// <summary>
/// executes a sql & retuns datatable
/// </summary>
/// <param name="sql"></param>
/// <returns>DataTable</returns>
public DataTable GetDataTable(string sql, string reportCode)
{
//sql = sql.Replace("''", "\"");
DataTable oDataTable = new DataTable();
HIT.OB.STD.Core.BLL.DBManagerFactory dbManagerFactory = new HIT.OB.STD.Core.BLL.DBManagerFactory();
HIT.OB.STD.Core.DAL.IOBFunctions dbManager = dbManagerFactory.GetDBManager(reportCode);
try
{
if (!string.IsNullOrEmpty(sql))
{
oDataTable = dbManager.GetDataTable(sql);
}
}
catch (Exception oEx)
{
throw oEx;
}
return(oDataTable);
}
protected void Page_Load(object sender, EventArgs e)
{
string selectSql = string.Empty;
string docName = string.Empty;
string reportcode = Request.QueryString["reportcode"];
string reportExtension = Request.QueryString["repExtension"].ToLower();
string artCode = string.Empty;
string revision = string.Empty;
if (Request.QueryString["listName"] != null)
{
artCode = Request.QueryString["artCode"];
string listName = Request.QueryString["listName"];
artCode = artCode.Split(':')[1].Trim();
docName = artCode + "_" + listName;
string sqlFrom = Request.QueryString["sqlFrom"];
string whereClause = Request.QueryString["whereClause"].Replace("-", "%");
selectSql = WrappingManager.GetListQueryByListName(sqlFrom, whereClause, reportcode, listName);
}
else
{
string table = Request.QueryString["table"].Replace("@@@", "\"");
string fields = Request.QueryString["fields"];
string whereclause = Request.QueryString["whereclause"].Replace("-", "%");
string groupby = Request.QueryString["groupby"];
string orderby = Request.QueryString["orderby"];
docName = reportcode;
if (table != "" && fields != "")
{
selectSql = " select " + fields + " from " + table;
if (whereclause != "")
{
selectSql = selectSql + " where " + whereclause + " ";
}
if (groupby.ToUpper() != "NONE" && groupby != "")
{
selectSql = selectSql + " order by " + groupby;
}
if (orderby != "")
{
if (selectSql.ToLower().Contains("order by"))
{
selectSql = selectSql + " , " + orderby;
}
else
{
selectSql = selectSql + " order by " + orderby;
}
}
}
}
HIT.OB.STD.Core.BLL.DBManagerFactory Factory = new HIT.OB.STD.Core.BLL.DBManagerFactory();
HIT.OB.STD.Core.DAL.IOBFunctions obFunctions = Factory.GetDBManager(reportcode);
DataTable dt = obFunctions.GetDataTable(selectSql);
if (reportExtension.Equals("xls"))
{
ExportExcel(dt, docName);
}
else if (reportExtension.Equals("csv"))
{
ExportCSV(dt, docName);
}
}
