public virtual void Render(string format, PCAxis.Query.SavedQuery query, PCAxis.Paxiom.PXModel model, bool safe, string fileExtension, string mimeType)
{
if (!safe)
{
model.Meta.Notes.Add(new Note(LocalizationManager.GetLocalizedString("PxWebSavedQueryUnsafeMessage"), NoteType.Table, true));
}
var info = PCAxis.Web.Controls.CommandBar.Plugin.CommandBarPluginManager.GetFileType(format);
PCAxis.Web.Core.ISerializerCreator creator = Activator.CreateInstance(Type.GetType(info.Creator)) as PCAxis.Web.Core.ISerializerCreator;
if (!mimeType.Contains("json")) // json always use UTF8
{
HttpContext.Current.Response.Charset = model.Meta.CodePage;
}
HttpContext.Current.Response.ContentType = mimeType;
HttpContext.Current.Response.AppendHeader("Content-Disposition", "attachment; filename=" + model.Meta.Matrix + "." + fileExtension);
var serializer = creator.Create(format);
using (MemoryStream ms = new MemoryStream())
{
serializer.Serialize(model, ms);
ms.Position = 0;
ms.WriteTo(HttpContext.Current.Response.OutputStream);
}
}
public override string Save(SavedQuery sq, Dictionary <string, string> parameters = null, int?id = null)
{
string name = "";
if (parameters != null && parameters.ContainsKey(SAVE_PARAMETER_FILENAME) && !string.IsNullOrWhiteSpace(parameters[SAVE_PARAMETER_FILENAME]))
{
name = parameters[SAVE_PARAMETER_FILENAME];
}
else
{
name = Guid.NewGuid().ToString();
}
string pathName = name;
if (!pathName.EndsWith(".pxsq"))
{
pathName = pathName + ".pxsq";
}
if (parameters != null && parameters.ContainsKey(SAVE_PARAMETER_PATH) && !string.IsNullOrWhiteSpace(parameters[SAVE_PARAMETER_PATH]))
{
pathName = System.IO.Path.Combine(parameters[SAVE_PARAMETER_PATH], pathName);
}
if (SaveSavedQuery(pathName, sq))
{
AddToCache(pathName, sq);
return(name);
}
return(null);
}
public override void Render(string format, PCAxis.Query.SavedQuery query, PCAxis.Paxiom.PXModel model, bool safe)
{
if (query.Output.Params["layout"] == ChartTypes.CHART_COLUMNLINE)
{
ChartManager.Settings.IsColumnLine = true;
}
ChartManager.Settings.ChartType = ChartSettings.ConvertToChartType(query.Output.Params["layout"], System.Windows.Forms.DataVisualization.Charting.SeriesChartType.Column);
ChartManager.Settings.UseSettingTitle = true;
//Custom chart title only works for the language that was selected when the saved query was created.
if (query.Sources[0].Language.ToLower() == model.Meta.CurrentLanguage.ToLower())
{
ChartManager.Settings.Title = CheckParameter(query, "chart_title") ? query.Output.Params["chart_title"] : ChartManager.Settings.Title;
}
else
{
ChartManager.Settings.Title = model.Meta.Title;
}
ChartManager.Settings.Width = CheckParameter(query, "chart_width") ? int.Parse(query.Output.Params["chart_width"]) : ChartManager.Settings.Width;
ChartManager.Settings.Height = CheckParameter(query, "chart_height") ? int.Parse(query.Output.Params["chart_height"]) : ChartManager.Settings.Height;
ChartManager.Settings.LineThickness = CheckParameter(query, "chart_linethickness") ? int.Parse(query.Output.Params["chart_linethickness"]) : ChartManager.Settings.LineThickness;
ChartManager.Settings.TimeSortOrder = CheckParameter(query, "chart_timesortorder") ? (ChartSettings.SortType)Enum.Parse(typeof(ChartSettings.SortType), query.Output.Params["chart_timesortorder"], true) : ChartManager.Settings.TimeSortOrder;
ChartManager.Settings.LabelOrientation = CheckParameter(query, "chart_labelorientation") ? (ChartSettings.OrientationType)Enum.Parse(typeof(ChartSettings.OrientationType), query.Output.Params["chart_labelorientation"], true) : ChartManager.Settings.LabelOrientation;
ChartManager.Settings.Guidelines = CheckParameter(query, "chart_guidelines") ? (ChartSettings.GuidelinesType)Enum.Parse(typeof(ChartSettings.GuidelinesType), query.Output.Params["chart_guidelines"], true) : ChartManager.Settings.Guidelines;
ChartManager.Settings.ShowLegend = CheckParameter(query, "chart_showlegend") ? bool.Parse(query.Output.Params["chart_showlegend"]) : ChartManager.Settings.ShowLegend;
ChartManager.Settings.LegendHeight = CheckParameter(query, "chart_legendheight") ? int.Parse(query.Output.Params["chart_legendheight"]) : ChartManager.Settings.LegendHeight;
base.Render(format, query, model, safe, GetFileExtension(format), GetMimeType(format));
}
public int Save(PCAxis.Query.SavedQuery query, int?id)
{
query.Stats = null;
string pxsjson = JsonConvert.SerializeObject(query);
using (var conn = new Microsoft.Data.SqlClient.SqlConnection(_connectionString))
{
conn.Open();
var cmd = new Microsoft.Data.SqlClient.SqlCommand(
@"insert into
SavedQueryMeta
(
DataSourceType,
DatabaseId,
DataSourceId,
[Status],
StatusUse,
StatusChange,
OwnerId,
MyDescription,
CreatedDate,
SavedQueryFormat,
SavedQueryStorage,
QueryText,
Runs,
Fails
)
values
(
@databaseType,
@databaseId,
@mainTable,
'A',
'P',
'P',
'Anonymous',
@title,
@creationDate,
'PXSJSON',
'D',
@query,
0,
0
);
SELECT @@IDENTITY AS 'Identity';", conn);
cmd.Parameters.AddWithValue("databaseType", query.Sources[0].Type);
cmd.Parameters.AddWithValue("databaseId", query.Sources[0].DatabaseId);
cmd.Parameters.AddWithValue("mainTable", GetMaintable(query.Sources[0]));
cmd.Parameters.AddWithValue("title", "");
cmd.Parameters.AddWithValue("creationDate", DateTime.Now);
cmd.Parameters.AddWithValue("query", pxsjson);
int newid = Convert.ToInt32(cmd.ExecuteScalar());
return(newid);
}
return(-1);
}
/// <summary>
/// Load a saved Query from disk
/// </summary>
/// <param name="path">Path to the saved query</param>
/// <returns></returns>
private static SavedQuery LoadSavedQuery(string path)
{
if (File.Exists(path))
{
string query = File.ReadAllText(path);
SavedQuery sq = JsonHelper.Deserialize <SavedQuery>(query) as SavedQuery;
return(sq);
}
return(null);
}
public override string Save(SavedQuery query, Dictionary <string, string> parameters = null, int?id = null)
{
if (id == null)
{
return(_da.Save(query).ToString());
}
else
{
return(_da.Save(query, id.Value).ToString());
}
}
private void AddToCache(string name, SavedQuery sq)
{
_cache.Set(name, new SavedQueryCacheBucket()
{
Query = sq
},
new CacheItemPolicy()
{
RemovedCallback = new CacheEntryRemovedCallback(CacheRemovedCallback),
SlidingExpiration = new TimeSpan(0, _cacheTime, 0)
});
}
/// <summary>
/// Check that the parameter exists and that it has a value
/// </summary>
/// <param name="query">Query object</param>
/// <param name="key">Key for the parameter</param>
/// <returns>True if the parameter exists and has a value, else false</returns>
protected bool CheckParameter(PCAxis.Query.SavedQuery query, string key)
{
if (query.Output.Params.ContainsKey(key))
{
if (query.Output.Params[key] != null)
{
return(true);
}
}
return(false);
}
public override bool MarkAsRunned(string name)
{
SavedQuery sq = Load(name);
if (sq != null)
{
sq.Stats.LastExecuted = DateTime.Now;
sq.Stats.RunCounter++;
var bucket = _cache[name] as SavedQueryCacheBucket;
bucket.IsModified = true;
return(true);
}
return(false);
}
public override SavedQuery Load(string name)
{
if (_cache.Contains(name))
{
var bucket = _cache[name] as SavedQueryCacheBucket;
return(bucket.Query);
}
SavedQuery sq = LoadSavedQuery(name);
AddToCache(name, sq);
return(sq);
}
public override void Render(string format, PCAxis.Query.SavedQuery query, PCAxis.Paxiom.PXModel model, bool safe)
{
ChartManager.Settings.UseSettingTitle = true;
ChartManager.Settings.Title = CheckParameter(query, "chart_title") ? query.Output.Params["chart_title"] : ChartManager.Settings.Title;
ChartManager.Settings.Width = CheckParameter(query, "chart_width") ? int.Parse(query.Output.Params["chart_width"]) : ChartManager.Settings.Width;
ChartManager.Settings.Height = CheckParameter(query, "chart_height") ? int.Parse(query.Output.Params["chart_height"]) : ChartManager.Settings.Height;
ChartManager.Settings.LineThickness = CheckParameter(query, "chart_linethickness") ? int.Parse(query.Output.Params["chart_linethickness"]) : ChartManager.Settings.LineThickness;
ChartManager.Settings.TimeSortOrder = CheckParameter(query, "chart_timesortorder") ? (ChartSettings.SortType)Enum.Parse(typeof(ChartSettings.SortType), query.Output.Params["chart_timesortorder"], true) : ChartManager.Settings.TimeSortOrder;
ChartManager.Settings.LabelOrientation = CheckParameter(query, "chart_labelorientation") ? (ChartSettings.OrientationType)Enum.Parse(typeof(ChartSettings.OrientationType), query.Output.Params["chart_labelorientation"], true) : ChartManager.Settings.LabelOrientation;
ChartManager.Settings.Guidelines = CheckParameter(query, "chart_guidelines") ? (ChartSettings.GuidelinesType)Enum.Parse(typeof(ChartSettings.GuidelinesType), query.Output.Params["chart_guidelines"], true) : ChartManager.Settings.Guidelines;
ChartManager.Settings.ShowLegend = CheckParameter(query, "chart_showlegend") ? bool.Parse(query.Output.Params["chart_showlegend"]) : ChartManager.Settings.ShowLegend;
ChartManager.Settings.LegendHeight = CheckParameter(query, "chart_legendheight") ? int.Parse(query.Output.Params["chart_legendheight"]) : ChartManager.Settings.LegendHeight;
RenderToScreen(query, model, "chartViewColumn", "Chart.aspx", safe);
}
public PCAxis.Query.SavedQuery Load(int id)
{
using (var conn = new Microsoft.Data.SqlClient.SqlConnection(_connectionString))
{
conn.Open();
var cmd = new Microsoft.Data.SqlClient.SqlCommand("select QueryText from SavedQueryMeta where QueryId = @queryId", conn);
cmd.Parameters.AddWithValue("queryId", id);
string query = cmd.ExecuteScalar() as string;
PCAxis.Query.SavedQuery sq = JsonHelper.Deserialize <PCAxis.Query.SavedQuery>(query) as PCAxis.Query.SavedQuery;
return(sq);
}
return(null);
}
public override void Render(string format, PCAxis.Query.SavedQuery query, PCAxis.Paxiom.PXModel model, bool safe)
{
ChartManager.Settings.ChartType = ChartSettings.ConvertToChartType(query.Output.Params["layout"], System.Windows.Forms.DataVisualization.Charting.SeriesChartType.Column);
ChartManager.Settings.UseSettingTitle = true;
ChartManager.Settings.Title = CheckParameter(query, "chart_title") ? query.Output.Params["chart_title"] : ChartManager.Settings.Title;
ChartManager.Settings.Width = CheckParameter(query, "chart_width") ? int.Parse(query.Output.Params["chart_width"]) : ChartManager.Settings.Width;
ChartManager.Settings.Height = CheckParameter(query, "chart_height") ? int.Parse(query.Output.Params["chart_height"]) : ChartManager.Settings.Height;
ChartManager.Settings.LineThickness = CheckParameter(query, "chart_linethickness") ? int.Parse(query.Output.Params["chart_linethickness"]) : ChartManager.Settings.LineThickness;
ChartManager.Settings.TimeSortOrder = CheckParameter(query, "chart_timesortorder") ? (ChartSettings.SortType)Enum.Parse(typeof(ChartSettings.SortType), query.Output.Params["chart_timesortorder"], true) : ChartManager.Settings.TimeSortOrder;
ChartManager.Settings.LabelOrientation = CheckParameter(query, "chart_labelorientation") ? (ChartSettings.OrientationType)Enum.Parse(typeof(ChartSettings.OrientationType), query.Output.Params["chart_labelorientation"], true) : ChartManager.Settings.LabelOrientation;
ChartManager.Settings.Guidelines = CheckParameter(query, "chart_guidelines") ? (ChartSettings.GuidelinesType)Enum.Parse(typeof(ChartSettings.GuidelinesType), query.Output.Params["chart_guidelines"], true) : ChartManager.Settings.Guidelines;
ChartManager.Settings.ShowLegend = CheckParameter(query, "chart_showlegend") ? bool.Parse(query.Output.Params["chart_showlegend"]) : ChartManager.Settings.ShowLegend;
ChartManager.Settings.LegendHeight = CheckParameter(query, "chart_legendheight") ? int.Parse(query.Output.Params["chart_legendheight"]) : ChartManager.Settings.LegendHeight;
base.Render(format, query, model, safe, GetFileExtension(format), GetMimeType(format));
}
public PCAxis.Query.SavedQuery Load(int id)
{
using (var conn = new OracleConnection(_connectionString))
{
conn.Open();
var cmd = new OracleCommand("select QueryText from " + _savedQueryTableOwner + ".SavedQueryMeta where QueryId = :queryId", conn);
cmd.Parameters.Add("queryId", id);
string query = cmd.ExecuteScalar() as string;
PCAxis.Query.SavedQuery sq = JsonHelper.Deserialize <PCAxis.Query.SavedQuery>(query) as PCAxis.Query.SavedQuery;
return(sq);
}
return(null);
}
/// <summary>
/// Runs all operations in the saved query and return the final model
/// </summary>
/// <param name="sq"></param>
/// <param name="model"></param>
/// <returns></returns>
public static PXModel RunWorkflow(SavedQuery sq, PXModel model)
{
PXModel m = model;
foreach (var step in sq.Workflow)
{
var ser = OperationsTracker.CreateSerializer(step.Type);
var op = ser.CreateOperation();
var m2 = op.Execute(m, ser.Deserialize(step));
if (m != m2)
{
m.Dispose();
}
m = m2;
}
return(m);
}
private void SerializeResult(PCAxis.Query.SavedQuery sq, PXModel model, HttpContext context)
{
var info = PCAxis.Web.Controls.CommandBar.Plugin.CommandBarPluginManager.GetFileType(sq.Output.Type);
PCAxis.Web.Core.ISerializerCreator creator = Activator.CreateInstance(Type.GetType(info.Creator)) as PCAxis.Web.Core.ISerializerCreator;
context.Response.ContentType = info.MimeType;
context.Response.AppendHeader("Content-Disposition", "attachment; filename=" + model.Meta.Matrix + "." + info.FileExtension);
var serializer = creator.Create(sq.Output.Type);
using (MemoryStream ms = new MemoryStream())
{
serializer.Serialize(model, ms);
ms.Position = 0;
ms.WriteTo(context.Response.OutputStream);
}
}
private string CreateUrlForScreenRendering(PCAxis.Query.SavedQuery sq, PXModel model)
{
if (sq.Sources.Count < 1)
{
throw new Exception("No source specified"); //TODO fix message
}
var src = sq.Sources[0];
if (string.Compare(src.SourceIdType, "path") != 0)
{
throw new Exception("Incompatible source type"); //TODO fix
}
string path = src.Source;
var tableName = QueryHelper.GetTableName(src);
path = path.Substring(0, path.Length - (tableName.Length + 1));
if (string.Compare(sq.Sources[0].Type, "CNMM") == 0)
{
if (!path.StartsWith("START"))
{
path = "START__" + path;
}
}
path = path.Replace(@"/", PxPathHandler.NODE_DIVIDER);
List <LinkManager.LinkItem> linkItems = new List <LinkManager.LinkItem>();
linkItems.Add(new LinkManager.LinkItem(PxUrl.TABLE_KEY, tableName));
linkItems.Add(new LinkManager.LinkItem(PxUrl.PATH_KEY, path.ToString()));
linkItems.Add(new LinkManager.LinkItem(PxUrl.DB_KEY, src.DatabaseId));
linkItems.Add(new LinkManager.LinkItem(PxUrl.LANGUAGE_KEY, src.Language));
linkItems.Add(new LinkManager.LinkItem(PxUrl.LAYOUT_KEY, "tableViewLayout1"));
var url = LinkManager.CreateLink("Table.aspx", false, linkItems.ToArray());
PCAxis.Web.Core.Management.LocalizationManager.ChangeLanguage(src.Language);
PCAxis.Web.Core.Management.PaxiomManager.PaxiomModel = model;
return(url);
}
public override void Render(string format, PCAxis.Query.SavedQuery query, PCAxis.Paxiom.PXModel model, bool safe)
{
ChartManager.Settings.UseSettingTitle = true;
//Custom chart title only works for the language that was selected when the saved query was created.
if (query.Sources[0].Language.ToLower() == model.Meta.CurrentLanguage.ToLower())
{
ChartManager.Settings.Title = CheckParameter(query, "chart_title") ? query.Output.Params["chart_title"] : ChartManager.Settings.Title;
}
else
{
ChartManager.Settings.Title = model.Meta.Title;
}
ChartManager.Settings.Width = CheckParameter(query, "chart_width") ? int.Parse(query.Output.Params["chart_width"]) : ChartManager.Settings.Width;
ChartManager.Settings.Height = CheckParameter(query, "chart_height") ? int.Parse(query.Output.Params["chart_height"]) : ChartManager.Settings.Height;
ChartManager.Settings.LineThickness = CheckParameter(query, "chart_linethickness") ? int.Parse(query.Output.Params["chart_linethickness"]) : ChartManager.Settings.LineThickness;
ChartManager.Settings.TimeSortOrder = CheckParameter(query, "chart_timesortorder") ? (ChartSettings.SortType)Enum.Parse(typeof(ChartSettings.SortType), query.Output.Params["chart_timesortorder"], true) : ChartManager.Settings.TimeSortOrder;
ChartManager.Settings.LabelOrientation = CheckParameter(query, "chart_labelorientation") ? (ChartSettings.OrientationType)Enum.Parse(typeof(ChartSettings.OrientationType), query.Output.Params["chart_labelorientation"], true) : ChartManager.Settings.LabelOrientation;
ChartManager.Settings.Guidelines = CheckParameter(query, "chart_guidelines") ? (ChartSettings.GuidelinesType)Enum.Parse(typeof(ChartSettings.GuidelinesType), query.Output.Params["chart_guidelines"], true) : ChartManager.Settings.Guidelines;
ChartManager.Settings.ShowLegend = CheckParameter(query, "chart_showlegend") ? bool.Parse(query.Output.Params["chart_showlegend"]) : ChartManager.Settings.ShowLegend;
ChartManager.Settings.LegendHeight = CheckParameter(query, "chart_legendheight") ? int.Parse(query.Output.Params["chart_legendheight"]) : ChartManager.Settings.LegendHeight;
RenderToScreen(query, model, "chartViewColumn", "Chart.aspx", safe);
}
public virtual void Render(string format, PCAxis.Query.SavedQuery query, PCAxis.Paxiom.PXModel model, bool safe)
{
//if (!safe)
//{
// model.Meta.Notes.Add(new Note(LocalizationManager.GetLocalizedString("PxWebSavedQueryUnsafeMessage"), NoteType.Table, true));
//}
var info = PCAxis.Web.Controls.CommandBar.Plugin.CommandBarPluginManager.GetFileType(format);
//PCAxis.Web.Core.ISerializerCreator creator = Activator.CreateInstance(Type.GetType(info.Creator)) as PCAxis.Web.Core.ISerializerCreator;
//HttpContext.Current.Response.ContentType = info.MimeType;
//HttpContext.Current.Response.AppendHeader("Content-Disposition", "attachment; filename=" + model.Meta.Matrix + "." + info.FileExtension);
//var serializer = creator.Create(query.Output.Type);
//using (MemoryStream ms = new MemoryStream())
//{
// serializer.Serialize(model, ms);
// ms.Position = 0;
// ms.WriteTo(HttpContext.Current.Response.OutputStream);
//}
Render(format, query, model, safe, info.FileExtension, info.MimeType);
}
/// <summary>
/// Save query
/// </summary>
/// <param name="path">Path + file name</param>
/// <param name="sq">SavedQuery object</param>
/// <returns></returns>
private static bool SaveSavedQuery(string path, SavedQuery sq)
{
try
{
//string path = HostingEnvironment.MapPath(@"~/App_Data/queries/" + name);
using (FileStream fs = File.Open(path, FileMode.Create, FileAccess.ReadWrite, FileShare.Read))
using (StreamWriter sw = new StreamWriter(fs))
using (JsonWriter jw = new JsonTextWriter(sw))
{
jw.Formatting = Formatting.Indented;
JsonSerializer serializer = new JsonSerializer();
serializer.Serialize(jw, sq);
}
}
catch (Exception)
{
//TODO Logg error
return(false);
}
return(true);
}
public override void Render(string format, PCAxis.Query.SavedQuery query, PCAxis.Paxiom.PXModel model, bool safe)
{
RenderToScreen(query, model, "informationView", "InformationPresentation.aspx", safe);
}
private PXModel LoadData(PCAxis.Query.SavedQuery sq)
{
//Only loads the first table source otherwise redirects to a page
if (sq.Sources.Count != 1)
{
//TODO redirect to error page incopatable query for PX-Web
}
TableSource src = sq.Sources[0];
IPXModelBuilder builder = null;
if (src.Type == "CNMM")
{
if (RouteInstance.RouteExtender == null)
{
DatabaseInfo db = PXWeb.Settings.Current.General.Databases.GetCnmmDatabase(src.DatabaseId);
if (db == null)
{
//TODO Redirect database does not exist
return(null);
}
var tableName = QueryHelper.GetTableName(src);
builder = PxContext.CreatePaxiomBuilder(src.DatabaseId, tableName);
}
else
{
DatabaseInfo db = PXWeb.Settings.Current.General.Databases.GetCnmmDatabase(RouteInstance.RouteExtender.GetDatabase());
var tableName = QueryHelper.GetTableName(src);
builder = PxContext.CreatePaxiomBuilder(RouteInstance.RouteExtender.Db.Database.id, tableName);
}
}
else if (src.Type == "PX")
{
DatabaseInfo db = PXWeb.Settings.Current.General.Databases.GetPxDatabase(src.DatabaseId);
if (db == null)
{
}
else
{
if (!db.HasLanguage(src.Language))
{
//TODO Redirect that the database is missing
return(null);
}
if (src.Source.StartsWith("-/"))
{
src.Source = src.DatabaseId + src.Source.Substring(1);
}
builder = PxContext.CreatePaxiomBuilder(src.DatabaseId, src.Source);
}
}
else
{
//TODO redirect to error page incompatible datasource type
return(null);
}
builder.SetPreferredLanguage(src.Language);
//If languefe set in reques we must read all langauges to be able to run workflow operations
if (_language != null)
{
builder.ReadAllLanguages = true;
}
builder.BuildForSelection();
var model = builder.Model;
List <PCAxis.Paxiom.Selection> sel = new List <PCAxis.Paxiom.Selection>();
foreach (var variable in model.Meta.Variables)
{
var query = src.Quieries.FirstOrDefault(q => q.Code == variable.Code);
PCAxis.Paxiom.Selection s = null;
if (query == null)
{
//Selects all values for the variable if it can't be eliminated
s = new PCAxis.Paxiom.Selection(variable.Code);
if (variable.IsContentVariable || !variable.Elimination)
{
s.ValueCodes.AddRange(variable.Values.Select(v => v.Code).ToArray());
}
}
else
{
if (PCAxis.Query.QueryHelper.IsAggregation(query.Selection.Filter))
{
s = QueryHelper.SelectAggregation(variable, query, builder);
}
else if (query.Selection.Filter.StartsWith("vs:", StringComparison.InvariantCultureIgnoreCase))
{
s = QueryHelper.SelectValueSet(variable, query, builder);
}
else
{
switch (query.Selection.Filter)
{
case "item":
s = QueryHelper.SelectItem(variable, query);
break;
case "top":
s = QueryHelper.SelectTop(variable, query);
break;
case "from":
s = QueryHelper.SelectFrom(variable, query);
break;
case "all":
s = QueryHelper.SelectAll(variable, query);
break;
default:
//TODO unsupported filter
break;
}
}
}
if (s != null)
{
sel.Add(s);
}
}
var selection = sel.ToArray();
//TODO fixa till
//if (sq.Output.Type == "SCREEN")
PCAxis.Query.TableQuery tbl = new PCAxis.Query.TableQuery(builder.Model, selection);
PaxiomManager.QueryModel = tbl;
BuildModelForPresentation(builder, selection);
return(builder.Model);
}
public void ProcessRequest(HttpContext context)
{
// Negotiate with the request limiter (if enabled)
if (_requestLimiter != null)
{
if (!_requestLimiter.ClientLimitOK(context.Request))
{
// Deny request
// see 409 - http://www.w3.org/Protocols/rfc2616/rfc2616-sec10.html
context.Response.AppendHeader("Retry-After", _requestLimiter.LimiterTimeSpan.ToString());
throw new HttpException(429, "429 - Too many requests in too short timeframe. Please try again later.");
}
}
// Enable CORS and preflight requests for saved queries
// Preflight requests should also be allowed in the API (SSDHandler).
if (Settings.Current.Features.SavedQuery.EnableCORS)
{
// Enable CORS (Cross-Origin-Resource-Sharing)
context.Response.AppendHeader("Access-Control-Allow-Origin", "*");
// Handle Preflight requests
if (context.Request.HttpMethod == "OPTIONS")
{
context.Response.AppendHeader("Access-Control-Allow-Methods", "GET");
return;
}
}
string queryName;
var routeData = context.Items["RouteData"] as RouteData;
if (routeData.Values["QueryName"] != null)
{
queryName = ValidationManager.GetValue(routeData.Values["QueryName"].ToString());
}
else
{
//No query supplied goto error page.
//TODO just to shut the compiler up
queryName = "";
//TODO redirect
throw new Exception("No query supplied");
}
// ----- Handle changed output format -----
_format = GetChangedOutputFormat(routeData);
// ----- Handle changed language -----
HandleChangedLanguage();
//Load saved query
PCAxis.Query.SavedQuery sq = null;
PXModel model = null;
bool safe = true;
try
{
if (PCAxis.Query.SavedQueryManager.StorageType == PCAxis.Query.SavedQueryStorageType.File)
{
string path = System.Web.Hosting.HostingEnvironment.MapPath(@"~/App_Data/queries/");
if (!queryName.ToLower().EndsWith(".pxsq"))
{
queryName = queryName + ".pxsq";
}
string[] allfiles = Directory.GetFiles(path, queryName, SearchOption.AllDirectories);
if (allfiles.Length == 0)
{
throw new HttpException(404, "HTTP/1.1 404 Not Found ");
}
queryName = allfiles[0];
}
//Check if the database is active.
//It should not be possible to run a saved query if the database is not active
sq = PCAxis.Query.SavedQueryManager.Current.Load(queryName);
IEnumerable <string> db;
TableSource src = sq.Sources[0];
if (src.Type.ToLower() == "cnmm")
{
db = PXWeb.Settings.Current.General.Databases.CnmmDatabases;
}
else
{
db = PXWeb.Settings.Current.General.Databases.PxDatabases;
}
bool activeDatabase = false;
foreach (var item in db)
{
if (item.ToLower() == src.DatabaseId.ToLower())
{
activeDatabase = true;
break;
}
}
if (!activeDatabase)
{
throw new SystemException();
}
//Validate that the user has the rights to access the table
string tableName = QueryHelper.GetTableName(src);
//if (!AuthorizationUtil.IsAuthorized(src.DatabaseId, null, src.Source))
if (!AuthorizationUtil.IsAuthorized(src.DatabaseId, null, tableName)) //TODO: Should be dbid, menu and selection. Only works for SCB right now... (2018-11-14)
{
List <LinkManager.LinkItem> linkItems = new List <LinkManager.LinkItem>();
linkItems.Add(new LinkManager.LinkItem()
{
Key = PxUrl.LANGUAGE_KEY, Value = src.Language
});
linkItems.Add(new LinkManager.LinkItem()
{
Key = PxUrl.DB_KEY, Value = src.DatabaseId
});
linkItems.Add(new LinkManager.LinkItem()
{
Key = "msg", Value = "UnauthorizedTable"
});
string url = LinkManager.CreateLink("~/Menu.aspx", linkItems.ToArray());
HttpContext.Current.Response.Redirect(url, false);
HttpContext.Current.ApplicationInstance.CompleteRequest();
return;
}
if (string.IsNullOrWhiteSpace(_format))
{
//Output format is not changed - use output format in the saved query
_format = sq.Output.Type;
}
// "Pre-flight" request from MS Office application
var userAgent = context.Request.Headers["User-Agent"];
//if (userAgent.ToLower().Contains("ms-office") && sq.Output.Type == PxUrl.VIEW_TABLE_IDENTIFIER)
if (userAgent != null && userAgent.ToLower().Contains("ms-office"))
{
context.Response.Write("<html><body>ms office return</body></html>");
HttpContext.Current.ApplicationInstance.CompleteRequest();
//context.Response.End();
return;
}
//We need to store to be able to run workflow due to variables are referenced with name and not ids
_originaleSavedQuerylanguage = sq.Sources[0].Language;
// Check from saved query output type is on screen. If so createCopy shall be true, else false
bool createCopy = CreateCopyOfCachedPaxiom(_format);
// Create cache key
string cacheKey = "";
if (_language != null)
{
cacheKey = string.Format("{0}_{1}", queryName, _language);
}
else
{
cacheKey = string.Format("{0}_{1}", queryName, _originaleSavedQuerylanguage);
}
// Handle redirects to the selection page in a special way. The model object will only contain metadata and no data
if (_format.Equals(PxUrl.PAGE_SELECT))
{
cacheKey = string.Format("{0}_{1}", cacheKey, PxUrl.PAGE_SELECT);
}
// Try to get model from cache
model = PXWeb.Management.SavedQueryPaxiomCache.Current.Fetch(cacheKey, createCopy);
PaxiomManager.QueryModel = PXWeb.Management.SavedQueryPaxiomCache.Current.FetchQueryModel(cacheKey, createCopy);
if (model == null || PaxiomManager.QueryModel == null)
{
DateTime timeStamp = DateTime.Now;
// Model not found in cache - load it manually
model = LoadData(sq);
//Check if we need to change langauge to be able to run workflow due to variables are referenced with name and not ids
if (!string.IsNullOrEmpty(_language) && _language != _originaleSavedQuerylanguage)
{
model.Meta.SetLanguage(_originaleSavedQuerylanguage);
}
// No need to run workflow if we are redirecting to the selection page
if (!_format.Equals(PxUrl.PAGE_SELECT))
{
model = QueryHelper.RunWorkflow(sq, model);
}
//Set back to requested langauge after workflow operations
if (!string.IsNullOrEmpty(_language) && _language != _originaleSavedQuerylanguage)
{
if (model.Meta.HasLanguage(_language))
{
model.Meta.SetLanguage(_language);
}
}
// Store model in cache
PXWeb.Management.SavedQueryPaxiomCache.Current.Store(cacheKey, model, timeStamp);
PXWeb.Management.SavedQueryPaxiomCache.Current.StoreQueryModel(cacheKey, PaxiomManager.QueryModel, timeStamp);
}
if (!sq.Safe)
{
safe = !CheckForUnsafeOperations(sq.Workflow);
}
}
catch (Exception ex)
{
if ((PCAxis.Query.SavedQueryManager.StorageType == PCAxis.Query.SavedQueryStorageType.File && System.IO.File.Exists(queryName)) ||
(PCAxis.Query.SavedQueryManager.StorageType == PCAxis.Query.SavedQueryStorageType.Database))
{
PCAxis.Query.SavedQueryManager.Current.MarkAsFailed(queryName);
}
throw new HttpException(404, "HTTP/1.1 404 Not Found");
//throw ex;
}
sq.LoadedQueryName = queryName;
PCAxis.Query.SavedQueryManager.Current.MarkAsRunned(queryName);
// Tell the selection page that it sholud clear the PxModel
if (_format.Equals(PxUrl.PAGE_SELECT))
{
HttpContext.Current.Session.Add("SelectionClearPxModel", true);
}
ViewSerializerCreator.GetSerializer(_format).Render(_format, sq, model, safe);
}
public override void Render(string format, PCAxis.Query.SavedQuery query, PCAxis.Paxiom.PXModel model, bool safe)
{
RenderToScreen(query, model, "tableViewSorted", "DataSort.aspx", safe);
}
public override void Render(string format, PCAxis.Query.SavedQuery query, PXModel model, bool safe)
{
TableManager.Settings.ZeroOption = CheckParameter(query, "table_zerooption") ? (PCAxis.Paxiom.ZeroOptionType)Enum.Parse(typeof(PCAxis.Paxiom.ZeroOptionType), query.Output.Params["table_zerooption"], true) : ZeroOptionType.ShowAll;
RenderToScreen(query, model, "tableViewLayout1", "Table.aspx", safe);
}
public int Save(PCAxis.Query.SavedQuery query, int?id = null)
{
query.Stats = null;
string pxsjson = JsonConvert.SerializeObject(query);
using (var conn = new OracleConnection(_connectionString))
{
string insertSQL = @"BEGIN
insert into
{3}.SavedQueryMeta
(
{0}
DataSourceType,
DatabaseId,
DataSourceId,
""STATUS"",
StatusUse,
StatusChange,
OwnerId,
MyDescription,
CreatedDate,
SavedQueryFormat,
SavedQueryStorage,
QueryText,
Runs,
Fails
)
values
(
{1}
:databaseType,
:databaseId,
:mainTable,
'A',
'P',
'P',
'Anonymous',
:title,
sysdate,
'PXSJSON',
'D',
:query,
0,
0
) {2};
END;";
string queryIdPartCol = "";
string queryIdPartValue = "";
string returningPart = "returning queryid into :identity";
if (id != null)
{
queryIdPartCol = "QueryId, ";
queryIdPartValue = ":queryId, ";
returningPart = "";
}
insertSQL = string.Format(insertSQL, queryIdPartCol, queryIdPartValue, returningPart, _savedQueryTableOwner);
conn.Open();
var cmd = new OracleCommand(insertSQL, conn);
cmd.BindByName = true;
cmd.Parameters.Add("databaseType", query.Sources[0].Type);
cmd.Parameters.Add("databaseId", query.Sources[0].DatabaseId);
cmd.Parameters.Add("mainTable", GetMaintable(query.Sources[0]));
cmd.Parameters.Add("title", " ");
cmd.Parameters.Add("query", OracleDbType.Clob, pxsjson, System.Data.ParameterDirection.Input);
cmd.Parameters.Add("identity", OracleDbType.Int16, System.Data.ParameterDirection.ReturnValue);
if (id != null)
{
cmd.Parameters.Add("queryId", id.Value);
}
cmd.ExecuteNonQuery();
if (id == null)
{
int newId = int.Parse(cmd.Parameters["identity"].Value.ToString());
return(newId);
}
else
{
return(id.Value);
}
}
return(-1);
}
public virtual string Save(SavedQuery query, Dictionary <string, string> parameters = null, int?id = null)
{
return(null);
}
public override void Render(string format, PCAxis.Query.SavedQuery query, PXModel model, bool safe)
{
RenderToScreen(query, model, "", "Selection.aspx", safe);
}
protected void RenderToScreen(PCAxis.Query.SavedQuery query, PXModel model, string defaultLayout, string page, bool safe)
{
if (query.Sources.Count < 1)
{
throw new Exception("No source specified"); //TODO fix message
}
var src = query.Sources[0];
if (string.Compare(src.SourceIdType, "path") != 0)
{
throw new Exception("Incompatible source type"); //TODO fix
}
string layout;
if (query.Output.Params.ContainsKey("layout"))
{
layout = query.Output.Params["layout"] ?? defaultLayout;
}
else
{
layout = defaultLayout;
}
string path = src.Source;
var tableName = GetTableName(src);
path = path.Substring(0, path.Length - (tableName.Length + 1));
if (string.Compare(query.Sources[0].Type, "CNMM") == 0)
{
if (!path.StartsWith("START"))
{
path = "START__" + path;
}
}
path = path.Replace(@"/", PxPathHandler.NODE_DIVIDER);
string url = null;
if (RouteInstance.RouteExtender == null)
{
List <LinkManager.LinkItem> linkItems = new List <LinkManager.LinkItem>();
linkItems.Add(new LinkManager.LinkItem(PxUrl.TABLE_KEY, tableName));
linkItems.Add(new LinkManager.LinkItem(PxUrl.PATH_KEY, path.ToString()));
linkItems.Add(new LinkManager.LinkItem(PxUrl.DB_KEY, src.DatabaseId));
linkItems.Add(new LinkManager.LinkItem(PxUrl.LANGUAGE_KEY, model.Meta.CurrentLanguage));
linkItems.Add(new LinkManager.LinkItem(PxUrl.LAYOUT_KEY, layout));
url = LinkManager.CreateLink(page, false, linkItems.ToArray());
}
else
{
string tableId = RouteInstance.RouteExtender.GetTableIdByName(tableName);
url = page == "Selection.aspx" ? RouteInstance.RouteExtender.GetSelectionRedirectUrl(tableId) : RouteInstance.RouteExtender.GetPresentationRedirectUrl(tableId, layout);
}
//info about loaded saved query in query string
if (Settings.Current.Features.SavedQuery.ShowPeriodAndId)
{
var tvar = model.Meta.Variables.FirstOrDefault(v => v.IsTime);
if (tvar != null)
{
if (!string.IsNullOrEmpty(query.LoadedQueryName))
{
url += "?loadedQueryId=" + System.IO.Path.GetFileNameWithoutExtension(query.LoadedQueryName);
}
string timeType = "item"; //default
string timeValue = null;
var timeQueryItem = query.Sources[0].Quieries.FirstOrDefault(x => x.Code == tvar.Code);
if (timeQueryItem != null)
{
if (timeQueryItem.Selection.Filter == "top")
{
timeType = "top";
timeValue = timeQueryItem.Selection.Values[0];
}
else if (timeQueryItem.Selection.Filter == "from")
{
timeType = "from";
timeValue = timeQueryItem.Selection.Values[0];
}
url += "&timeType=" + timeType;
if (timeValue != null)
{
url += "&timeValue=" + timeValue;
}
}
}
}
PCAxis.Web.Core.Management.LocalizationManager.ChangeLanguage(model.Meta.CurrentLanguage);
PCAxis.Web.Core.Management.PaxiomManager.PaxiomModel = model;
PaxiomManager.OperationsTracker = new OperationsTracker(query.Workflow.ToArray());
if (!safe)
{
PaxiomManager.OperationsTracker.IsUnsafe = true;
}
PaxiomManager.OperationsTracker.IsTimeDependent = query.TimeDependent;
HttpContext.Current.Response.Redirect(url);
}
public abstract void Render(string format, PCAxis.Query.SavedQuery query, PCAxis.Paxiom.PXModel model, bool safe);