Ejemplo n.º 1
0
        public object TestQuery(string _connectionFile, string _queryString, Guid _portletID, string _expandedColumns, string _columnLabels, string _queryTimeout, string _testHostId)
        {
            var portlet = Jenzabar.Common.ObjectFactoryWrapper.GetInstance <IPortletFacade>().FindByGuid(_portletID);
            var literalStringReplacer = Jenzabar.Common.ObjectFactoryWrapper.GetInstance <ILiteralStringReplacer>();

            if (portlet.AccessCheck("CanAdminQueries"))
            {
                try
                {
                    var odbcConn = _connectionFile.Contains(".config") ? new CUS.OdbcConnectionClass3.OdbcConnectionClass3("~/ClientConfig/" + _connectionFile) : new CUS.OdbcConnectionClass3.OdbcConnectionClass3(_connectionFile);

                    odbcConn.ConnectionTest();
                    try
                    {
                        var qs = new QuerySafe();
                        if (qs.IsQuerySafeEnough(_queryString, portlet))
                        {
                            if (!String.IsNullOrEmpty(_testHostId))
                            {
                                literalStringReplacer.Extend("@@HostID", _testHostId);
                            }

                            var fqs = literalStringReplacer.Process(_queryString, portlet);

                            Exception exError = null;
                            DataTable dt;
                            var       qt = 0;
                            if (Int32.TryParse(_queryTimeout, out qt) && qt > 0)
                            {
                                dt = odbcConn.ConnectToERP(fqs, ref exError, qt);
                            }
                            else
                            {
                                dt = odbcConn.ConnectToERP(fqs, ref exError);
                            }
                            if (exError != null)
                            {
                                return(new
                                {
                                    success = false,
                                    message = "Query Test Failed. " + exError.Message + " " + exError.StackTrace
                                });
                            }
                            if (dt == null)
                            {
                                return(new
                                {
                                    success = true,
                                    message = "Query test was successful, but no results were returned."
                                });
                            }
                            var expandedColumns = new List <String>();
                            var columnLabels    = new List <String>();

                            if (_expandedColumns.Trim().Length > 0)
                            {
                                if (_expandedColumns.Contains(','))
                                {
                                    expandedColumns.AddRange(_expandedColumns.Split(',').Select(column => column.Trim()));
                                }
                                else
                                {
                                    expandedColumns.Add(_expandedColumns.Trim());
                                }
                            }

                            if (_columnLabels.Trim().Length > 0)
                            {
                                if (_columnLabels.Contains(','))
                                {
                                    columnLabels.AddRange(_columnLabels.Split(',').Select(label => label.Trim()));
                                }
                                else
                                {
                                    columnLabels.Add(_columnLabels.Trim());
                                }
                            }

                            var jsdtc = new JSDataTableConverter(dt, expandedColumns.ToArray(), columnLabels.ToArray());

                            var data = jsdtc.GetJsDataTable();

                            return(new
                            {
                                success = true,
                                message = "Query test was successful. " + dt.Rows.Count.ToString() + " rows returned. ",
                                data.data,
                                data.columns
                            });
                        }

                        return(new
                        {
                            success = false,
                            message = "You do not have permissions to create advanced queries that use Update, Delete, Insert, or Execute."
                        });
                    }
                    catch (Exception ex)
                    {
                        return(new
                        {
                            success = false,
                            message = "Query Failed. Test your query using an external tool and paste your corrected version into place. <br>Error:<br>" + ex.Message
                        });
                    }
                }
                catch
                {
                    return(new
                    {
                        success = false,
                        message = "Connection failed before query was executed."
                    });
                }
            }

            return(new
            {
                success = false,
                message = "You do not have permissions to create queries."
            });
        }
Ejemplo n.º 2
0
        public object TestQuery(string _connectionFile, string _queryString, Guid _portletID, string _expandedColumns, string _columnLabels, string queryTimeout)
        {
            CUS.OdbcConnectionClass3.OdbcConnectionClass3 odbcConn;
            Portlet portlet = Jenzabar.Common.ObjectFactoryWrapper.GetInstance <IPortletFacade>().FindByGuid(_portletID);

            if (portlet.AccessCheck("CanAdminQueries"))
            {
                try
                {
                    if (_connectionFile.Contains(".config"))
                    {
                        odbcConn = new CUS.OdbcConnectionClass3.OdbcConnectionClass3("~/ClientConfig/" + _connectionFile);
                    }
                    else
                    {
                        odbcConn = new CUS.OdbcConnectionClass3.OdbcConnectionClass3(_connectionFile);
                    }

                    odbcConn.ConnectionTest();

                    Exception exError = null;
                    try
                    {
                        QuerySafe QS = new QuerySafe();
                        if (QS.IsQuerySafeEnough(_queryString, portlet))
                        {
                            FillQueryString FQS = new FillQueryString(_queryString);

                            DataTable dt = new DataTable();
                            int       qt = 0;
                            if (Int32.TryParse(queryTimeout, out qt) && qt > 0)
                            {
                                dt = odbcConn.ConnectToERP(FQS.FilledQueryString, ref exError, qt);
                            }
                            else
                            {
                                dt = odbcConn.ConnectToERP(FQS.FilledQueryString, ref exError);
                            }

                            List <String> expandedColumns = new List <String>();
                            List <String> columnLabels    = new List <String>();

                            if (_expandedColumns.Trim().Length > 0)
                            {
                                if (_expandedColumns.Contains(','))
                                {
                                    foreach (String column in _expandedColumns.Split(','))
                                    {
                                        expandedColumns.Add(column.Trim());
                                    }
                                }
                                else
                                {
                                    expandedColumns.Add(_expandedColumns.Trim());
                                }
                            }

                            if (_columnLabels.Trim().Length > 0)
                            {
                                if (_columnLabels.Contains(','))
                                {
                                    foreach (String label in _columnLabels.Split(','))
                                    {
                                        columnLabels.Add(label.Trim());
                                    }
                                }
                                else
                                {
                                    columnLabels.Add(_columnLabels.Trim());
                                }
                            }

                            JSDataTableConverter jsdtc = new JSDataTableConverter(dt, expandedColumns.ToArray(), columnLabels.ToArray());

                            JSDataTable data = jsdtc.GetJsDataTable();

                            if (exError != null)
                            {
                                throw exError;
                            }
                            return(new
                            {
                                success = true,
                                message = "Query test was successful. " + dt.Rows.Count.ToString() + " rows returned. ",
                                data = data.data,
                                columns = data.columns,
                                a = expandedColumns.ToArray(),
                                b = columnLabels.ToArray()
                            });
                        }
                        else
                        {
                            return(new
                            {
                                success = false,
                                message = "You do not have permissions to create advanced queries that use Update, Delete, Insert, or Execute."
                            });
                        }
                    }
                    catch (Exception ex)
                    {
                        return(new
                        {
                            success = false,
                            message = "Query Failed. Test your query using an external tool and paste your corrected version into place. <br>Error:<br>" + ex.Message
                        });
                    }
                }
                catch
                {
                    return(new
                    {
                        success = false,
                        message = "Connection failed before query was executed."
                    });
                }
            }
            else
            {
                return(new
                {
                    success = false,
                    message = "You do not have permissions to create queries."
                });
            }
        }