/// <summary>
/// Creates a DataTable from a PXModel instance. Joins the columns for series name if several.
/// This overload includes conversion of data for a specific variable/value to negative numbers for usage with population chart.
/// </summary>
/// <param name="px"></param>
/// <param name="variableName">Variable for which data for a specific value is converted to negative numbers. If null, value can only be present for one variable.</param>
/// <param name="valueTextOrValue">Value for which data is converted to negative numbers.</param>
/// <returns></returns>
public static DataTable AsDataTableCompressed(this PXModel px, string variableName, string valueTextOrValue)
{
var dt = px.AsDataTable(true, false);
if (valueTextOrValue != null)
{
var columnsWithValue =
dt.Columns.OfType <DataColumn>()
.Where(
column =>
dt.Rows.OfType <DataRow>()
.Select(row => row.Field <object>(column))
.Any(o => o is string && o.ToString() == valueTextOrValue)
)
.ToArray();
if (columnsWithValue.Count() != 1)
{
throw
variableName == null
? new PxExtendExceptions.ConvertToNegativeValuesException("Supplied value must be present for one variable og variable must be suplied.")
: new PxExtendExceptions.ConvertToNegativeValuesException("Supplied variable/value-combination must be present.");
}
variableName = variableName ?? columnsWithValue.Single().ColumnName;
}
var table = new DataTable();
table.Columns.AddRange(
new[]
{
new DataColumn("Series"),
new DataColumn("Label"),
new DataColumn("DATA", typeof(double)),
new DataColumn("CODES"),
new DataColumn("TEXT")
}
);
foreach (DataRow row in dt.Rows)
{
double d;
object data;
if (Double.TryParse(row.ItemArray.Take(row.ItemArray.Length - 2).Last().ToString(), out d))
{
if (variableName == null)
{
data = d;
}
else
{
if (d < 0)
{
throw new PxExtendExceptions.ConvertToNegativeValuesException("Negative values not allowed, when conversion enabled.");
}
data =
row.Field <string>(variableName) == valueTextOrValue
? d * -1
: d;
}
}
else
{
data = DBNull.Value;
}
var r = table.NewRow();
r[0] = row.ItemArray.Length < 5 ? "Series 1" : String.Join(", ", row.ItemArray.Take(row.ItemArray.Length - 4).Select(x => x.ToString()).ToArray());
r[1] = row.ItemArray.Length < 5 ? row.ItemArray.First() : row.ItemArray[row.ItemArray.Length - 4];
r[2] = data;
r[3] = row.ItemArray.Take(row.ItemArray.Length - 1).Last();
r[4] = row.ItemArray.Last();
table.Rows.Add(r);
}
return(table);
}
/// <summary>
/// Creates a DstChart from a PXModel instance.
/// </summary>
/// <param name="px">PXModel instance to create chart from.</param>
public DstChart(PXModel px)
: this(px, null)
{
}
/// <summary>
/// Creates a DataTable from a PXModel instance. Joins the columns for series name if several.
/// </summary>
/// <param name="px"></param>
/// <returns></returns>
public static DataTable AsDataTableCompressed(this PXModel px)
{
return(AsDataTableCompressed(px, null, null));
}
void updateImage()
{
#if !DEBUG
try
{
#endif
Text = (!fileName.Equals(String.Empty) ? fileName + " - " : "") + "Test af PxChart BETA";
chartSizeLabel.Text = "Str.: " + pxChart.Width + ", " + pxChart.Height;
if (button2.Text == "Dst&Chart")
{
dstChart.AddPxData(
px,
c =>
{
//c.PieLabelStyle = "Outside";
((DstChart)c).PXMeta = px != null ? px.Meta : null;
//c.AllowDecimalInterval = false;
//c.MaleValueNameForPopulationPyramid = chartType.SelectedIndex == 34 ? "mænd" : null;
c.MaleValueNameForPopulationPyramid = chartType.SelectedIndex == 34 ? px.Meta.Variables.First(x => x.Values.Count == 2).Values.First().Value : null;
c.ChartTypes = chartTypes.Concat(new SeriesChartType[] { (SeriesChartType)chartType.SelectedIndex }).ToArray();
c.AutoMoveMostValuesVariableToX = autoPivot.Checked;
c.OverrideTitle = showTitle.Checked ? null : "";
c.HideLegend = !showLegend.Checked;
//((DstChart)c).ColorCollectionsForChart =
// new List<Color[]>()
// {
// new Color[] { Color.Bisque, Color.Beige, Color.Blue, Color.Brown }
// };
if (showCopyright.Checked)
{
c.InfoText = "© Danmarks Statistik";
//if (px != null && px.Meta.Matrix != "")
// c.InfoText += ", kilde: http://www.statistikbanken.dk/" + px.Meta.Matrix.ToLower();
}
else
{
c.InfoText = "";
}
//c.AutoMoveMostValuesVariableToX = false;
//c.HideLegend = false;
//c.LegendHeightInPixels = 40;
c.AdjustAxes = adjust.Checked;
//c.SortDataPoints = sort.Checked ? PxChart.SortType.Descending : PxChart.SortType.None;
c.SortDataPoints = (PxChart.SortType)Enum.Parse(typeof(PxChart.SortType), (listBox1.SelectedItem ?? "None").ToString());
//c.DecimalPlaces = 5;
//c.AllowDecimalInterval = false;
try
{
c.Font.Family = new FontFamily("Arial");
c.Font.SizeRegular = 13;
//c.Font.SizeSmall = 23;
}
catch
{ }
}
);
//if (adjust.Checked)
// dstChart.adjustAxes();
//if (sort.Checked)
// dstChart.Sort();
//dstChart.ChartAreas["Main"].AxisX.LabelStyle.Angle = 45;
//dstChart.ChartAreas["Main"].AxisX.IsLabelAutoFit = false;
//dstChart.ChartAreas.First().AxisY.LabelStyle.Format = "{0:#,##0.##}";
//dstChart.AntiAliasing = AntiAliasingStyles.All;
//dstChart.Units = "Mio. kr.";
//dstChart.ChartAreas.First().AxisY.LabelStyle.Format = "N";
//dstChart.ChartAreas.First().AxisX.LabelStyle.Format = "N";
//dstChart.ChartAreas.First().AxisX.IsLabelAutoFit = true;
}
if (button2.Text == "Px&Chart")
{
pxChart.AddPxData(
px,
c =>
{
//c.AllowDecimalInterval = false;
//c.MaleValueNameForPopulationPyramid = chartType.SelectedIndex == 34 ? "mænd" : null;
c.MaleValueNameForPopulationPyramid = chartType.SelectedIndex == 34 ? px.Meta.Variables.First(x => x.Values.Count == 2).Values.First().Value : null;
c.ChartTypes = chartTypes.Concat(new SeriesChartType[] { (SeriesChartType)chartType.SelectedIndex }).ToArray();
c.AutoMoveMostValuesVariableToX = autoPivot.Checked;
c.InfoText = showCopyright.Checked ? "© Danmarks Statistik" : "";
c.AdjustAxes = adjust.Checked;
//c.SortDataPoints = sort.Checked ? PxChart.SortType.Descending : PxChart.SortType.None;
c.SortDataPoints = (PxChart.SortType)Enum.Parse(typeof(PxChart.SortType), (listBox1.SelectedItem ?? "None").ToString());
}
);
//if (adjust.Checked)
// pxChart.adjustAxes();
//if (sort.Checked)
// pxChart.Sort();
}
if (button2.Text == "Ms&Chart")
{
msChart.Series.Clear();
if (autoPivot.Checked)
{
px = px.PivotSpecificVariableToAloneInHead(px.MostValuesVariable().Name);
}
msChart.DataBindCrossTable(
px.AsDataTableCompressed().AsEnumerable(),
"Series",
"Label",
"DATA",
""
);
foreach (Series s in msChart.Series)
{
s.ChartType = (SeriesChartType)chartType.SelectedIndex;
}
}
dstChart.Visible = button2.Text == "Dst&Chart";
pxChart.Visible = button2.Text == "Px&Chart";
msChart.Visible = button2.Text == "Ms&Chart";
infoLabel.Hide();
//Activate();
#if !DEBUG
}
catch (Exception e)
{
infoLabel.Text = fileName != null ? e.Message : "Åbn en px-fil for at vise en graf.\nKlik på Åbn-knappen (Alt + Å).";
infoLabel.Show();
}
#endif
}
private void Form1_Load(object sender, EventArgs e)
{
//Thread.CurrentThread.CurrentUICulture = new CultureInfo("en-GB");
Width -= infoLabel.Width - 584;
Height -= infoLabel.Height - 324;
CultureInfo ci = new CultureInfo("da-DK");
ci.NumberFormat.NumberGroupSeparator = " ";
ci.NumberFormat.CurrencyDecimalSeparator = ",";
Thread.CurrentThread.CurrentUICulture = ci;
for (int i = 0; i < 34; i++)
{
chartType.Items.Add(((SeriesChartType)i).ToString());
}
chartType.Items.Add("Population");
//dstChart.Font.Family = new FontFamily("Arial Narrow");
#if DEBUG
string type = "Line";
fileName = @"h:\118943.px"; //Forkert akse
fileName = @"h:\2012912131220111204936FOLK1.px"; //Meget lang under-overskrift
//fileName = @"h:\pyramid10.px"; //Befolkningspyramide, 10-års-aldersgrupper
//fileName = @"h:\pyramid5.px"; //Befolkningspyramide, 5-års-aldersgrupper
//fileName = @"h:\pyramid1.px"; //Befolkningspyramide, 1-års-aldersgrupper
//fileName = @"h:\134509.px"; //tegnsæt
//fileName = @"h:\152976.px"; //akser
//fileName = @"h:\155160.px";
fileName = @"h:\140203.px"; //Dobbelt enhed
fileName = @"h:\126275.px"; type = "Bar"; //for stort interval for bar
fileName = @"h:\152976.px"; type = "Line"; //Engelske tider
fileName = @"h:\pyramid10test.px"; type = "Population"; //Befolkningspyramide, 10-års-aldersgrupper
fileName = @"h:\146698.px"; type = "Line"; //For meget luft i siderne
fileName = @"h:\126275.px"; type = "Bar";
fileName = @"h:\legendtime2.px"; type = "Column"; //Skal beholde tid i underoverskrift
fileName = @"h:\2012102152456111965792FOLK1.px"; type = "Population"; //Mange aldre
fileName = @"h:\pyramid10test.px"; type = "Population"; //Befolkningspyramide, 10-års-aldersgrupper
fileName = @"h:\134509.px"; type = "Bar"; //PXModel læser forkert tegnsæt
fileName = @"h:\146467.px"; type = "Pie"; //Pie med uheldigt placerede tekster
fileName = @"h:\162006.px"; type = "Pie"; //Mangler legend
fileName = @"h:\146751.px"; type = "Bar"; //Tegnsæt-problemer
fileName = @"h:\150729.px"; type = "Column"; //Aksetitel skæres i visse højder
fileName = @"h:\14679432589377428.px"; type = "Pie"; //Kan ikke danne pie
fileName = @"h:\10535235322713474.px"; type = "Pie"; //Pie mangler akseenhed
fileName = @"h:\11886255520046261.px"; type = "Bar"; //Mangler en legend-tekst
fileName = @"h:\legendtime.px"; type = "Column"; //Skal have fjernet tid i underoverskrift
fileName = @"h:\20121022135543112768999FOLK1.px"; type = "Column"; //Mange 1-værdi-variable
fileName = @"h:\10535235322713474.px"; type = "Bar"; //Meget lang akseenhed
fileName = @"h:\14469936119071536.px"; type = "Line"; //Meget lang værditekst
fileName = @"h:\15058160770215484.px"; type = "Pie"; //Lang værditekst
//fileName = @"h:\2012102317022112833663FOLK1.px"; type = "Column";
fileName = @"h:\8966747670373370.px"; type = "Line"; //Viser for stort dataområde
fileName = @"h:\010_khi_tau_101_en.px"; type = "Line"; //Tid opdelt i to variable (Baumgartner)
fileName = @"h:\16386346604344427.px"; type = "Column"; //Ikke plads til akse-labels
fileName = @"h:\2012125143921114954423FOLK1.px"; type = "Column"; //Test af variabel på X
fileName = @"h:\2012126133738114999498UHV1.px"; type = "Line"; //Test af variabel på x
fileName = @"h:\16329652362087594.px"; type = "Line"; //Labels afkortes i bredder ml. ca. 577 og 596.
fileName = @"h:\15597160537048749.px"; type = "Line";
fileName = @"h:\pyramid5.px"; type = "Population"; //Befolkningspyramide, 5-års-aldersgrupper
fileName = @"h:\167653.px"; type = "Column";
//Hele vises uden halve, hvis halve findes.
//Y-akse 2 har kun top og bund værdi
px = PxExtend.CreatePxModel(fileName);
px =
PxExtend.CreatePxModel(
new FileInfo(fileName),
true,
new Selection[]
{
//new Selection("alder").AddValueCode("TOT")
},
null,
null
);
//DstChart testChart =
// new DstChart(
// px,
// c =>
// {
// c.Width = 563;
// c.Height = 337;
// c.AdjustAxes = true;
// c.ChartType = SeriesChartType.Bar;
// //c.MaleValueNameForPopulationPyramid = "mænd";
// }
// );
//testChart.SaveImage(@"h:\test.png", ChartImageFormat.Png);
//Process p = new Process() { StartInfo = new ProcessStartInfo(@"h:\test.png") };
//p.Start();
//Environment.Exit(0);
//px.Save(@"h:\1.px");
//px = px.Eliminate("tid");
//px.Save(@"h:\2.px");
//Environment.Exit(0);
//button2.Text = "Px&Chart";
chartType.SelectedIndex = chartType.Items.IndexOf(type);
editPanel.Enabled = true;
//updateImage();
#else
chartType.SelectedIndex = chartType.Items.IndexOf("Column");
#endif
updateChartTypesLabel();
}
public void Serialize(PXModel model, Stream stream)
{
//using (var writer = new StreamWriter(stream, Encoding.UTF8))
//{
var writer = new StreamWriter(stream, new UTF8Encoding(false));
var tableResponse = new TableResponse();
PXModel pivotedModel = RearrangeValues(model);
var formatter = new DataFormatter(pivotedModel);
formatter.DecimalSeparator = ".";
formatter.ThousandSeparator = "";
var lastUpdatedContentsVariable = new Value();
if (model.Meta.ContentVariable != null && model.Meta.ContentVariable.Values.Count > 0)
{
lastUpdatedContentsVariable = model.Meta.ContentVariable.Values.OrderByDescending(x => x.ContentInfo.LastUpdated).FirstOrDefault();
}
// Add Metadata
tableResponse.Metadata.Add(new TableResponseMetadata
{
Infofile = pivotedModel.Meta.InfoFile,
Source = pivotedModel.Meta.Source,
Label = pivotedModel.Meta.Title,
Updated = model.Meta.ContentVariable != null && model.Meta.ContentVariable.Values.Count > 0 ? lastUpdatedContentsVariable.ContentInfo.LastUpdated.PxDateStringToDateTime().ToString() : model.Meta.CreationDate.PxDateStringToDateTime().ToString()
});
// Add stub
tableResponse.Columns.AddRange(pivotedModel.Meta.Stub.Select(s => new TableResponseColumn
{
Code = s.Code,
Text = s.Name,
Type = s.IsTime ? "t" : "d",
Comment = s.HasNotes() ? s.Notes.GetAllNotes() : null
}));
if (pivotedModel.Meta.ContentVariable != null)
{
// Add heading
tableResponse.Columns.AddRange(pivotedModel.Meta.ContentVariable.Values.Select(val => new TableResponseColumn
{
Code = val.Code,
Text = val.Text,
Type = "c",
Comment = val.HasNotes() ? val.Notes.GetAllNotes() : null
}));
}
else
{
tableResponse.Columns.Add(new TableResponseColumn
{
Code = pivotedModel.Meta.Contents,
Text = pivotedModel.Meta.Contents,
Type = "c"
});
}
// Add comments
foreach (var variable in pivotedModel.Meta.Stub)
{
foreach (var value in variable.Values)
{
if (value.HasNotes())
{
tableResponse.Comments.Add(new TableResponseComment
{
Comment = value.Notes.GetAllNotes(),
Value = value.Code,
Variable = variable.Code
});
}
}
}
int row = 0;
Build(pivotedModel, formatter, 0, ref row, tableResponse, new List <string>());
// Write to output stream
writer.Write(tableResponse.ToJSON(false));
writer.Flush();
// } End using
}
public void Serialize(PXModel model, string path)
{
Serialize(model, new FileStream(path, FileMode.Create));
}
public override void Render(string format, SavedQuery query, PXModel model, bool safe)
{
RenderToScreen(query, model, "", "Selection.aspx", safe);
}
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
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)
{
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);
}
/// <summary>
/// Create a DstChart from this PXModel instance.
/// </summary>
/// <param name="px"></param>
/// <returns></returns>
public static DstChart AsDstChart(this PXModel px)
{
return(px.AsDstChart(null, null, 0, 0));
}