public IHttpActionResult GetExternalDB()
        {
            try
            {
                if (GetRoles == string.Empty || User.IsInRole(GetRoles))
                {
                    string afTbl = TableOperations <AdditionalField> .GetTableName();

                    string afvTbl = TableOperations <AdditionalFieldValue> .GetTableName();

                    using (AdoDataConnection connection = new AdoDataConnection(Connection))
                    {
                        string query = $@"SELECT MIN(UpdatedOn) AS lastUpdate, {afTbl}.ExternalDB AS name  
                                                    FROM 
                                                    {afTbl} LEFT JOIN {afvTbl} ON {afTbl}.ID = {afvTbl}.AdditionalFieldID
                                                    WHERE 
                                                        {afTbl}.ParentTable = 'DER'
                                                        AND {afTbl}.ExternalDB IS NOT NULL AND {afTbl}.ExternalDB <> ''
                                                    GROUP BY {afTbl}.ExternalDB";

                        DataTable table = connection.RetrieveData(query);

                        return(Ok(table));
                    }
                }
                else
                {
                    return(Unauthorized());
                }
            }
            catch (Exception ex)
            {
                return(InternalServerError(ex));
            }
        }
Ejemplo n.º 2
0
        public virtual IHttpActionResult GetOne(string id)
        {
            if (GetAuthCheck())
            {
                try
                {
                    T            result     = null;
                    PropertyInfo primaryKey = typeof(T).GetProperty(PrimaryKeyField);
                    if (primaryKey.PropertyType == typeof(int))
                    {
                        result = QueryRecordWhere(PrimaryKeyField + " = {0}", int.Parse(id));
                    }
                    else if (primaryKey.PropertyType == typeof(Guid))
                    {
                        result = QueryRecordWhere(PrimaryKeyField + " = {0}", Guid.Parse(id));
                    }
                    else
                    {
                        result = QueryRecordWhere(PrimaryKeyField + " = {0}", id);
                    }

                    if (result == null)
                    {
                        string tableName = TableOperations <T> .GetTableName();

                        return(BadRequest(string.Format(PrimaryKeyField + " provided does not exist in '{0}'.", tableName)));
                    }
                    else
                    {
                        return(Ok(result));
                    }
                }
                catch (Exception ex)
                {
                    return(InternalServerError(ex));
                }
            }
            else
            {
                return(Unauthorized());
            }
        }
        public IHttpActionResult GetUsersForTSC(int tscid)
        {
            if (GetRoles != string.Empty && !User.IsInRole(GetRoles))
            {
                return(Unauthorized());
            }
            try
            {
                using (AdoDataConnection connection = new AdoDataConnection(Connection))
                {
                    string uaTableName = TableOperations <UserAccount> .GetTableName();

                    string aufvTableName = TableOperations <AdditionalUserFieldValue> .GetTableName();

                    string aufTableName = TableOperations <AdditionalUserField> .GetTableName();

                    string sql = $@"
                        SELECT
	                        UA.*
                        FROM
	                        {uaTableName} as UA JOIN
	                        {aufvTableName} as AUFV ON UA.ID = AUFV.UserAccountID JOIN
	                        {aufTableName} as AUF ON AUFV.AdditionalUserFieldID = AUF.ID
                        WHERE
	                        AUF.FieldName = 'TSC' AND AUFV.Value = {{0}}
                    ";


                    DataTable table = connection.RetrieveData(sql, tscid);
                    return(Ok(table));
                }
            }
            catch (Exception ex)
            {
                return(InternalServerError(ex));
            }
        }
Ejemplo n.º 4
0
        public IHttpActionResult GetAssetsForLocation(int locationID)
        {
            if (GetRoles == string.Empty || User.IsInRole(GetRoles))
            {
                using (AdoDataConnection connection = new AdoDataConnection(Connection))
                {
                    string assetTableName = TableOperations <Asset> .GetTableName();

                    string assetTypeTableName = TableOperations <AssetTypes> .GetTableName();

                    string assetLocationTableName = TableOperations <AssetLocation> .GetTableName();

                    try
                    {
                        DataTable result = connection.RetrieveData($@"
                        SELECT 
	                        a.*,
	                        at.Name as AssetType
                        FROM
	                        {assetTableName} as a JOIN 
	                        {assetTypeTableName} as at ON a.AssetTypeID = at.ID JOIN
	                        {assetLocationTableName} as al ON a.ID = al.AssetID
                        WHERE
                            al.LocationID = {{0}}", locationID);
                        return(Ok(result));
                    }
                    catch (Exception ex)
                    {
                        return(InternalServerError(ex));
                    }
                }
            }
            else
            {
                return(Unauthorized());
            }
        }
Ejemplo n.º 5
0
        /// <summary>
        /// Gets the <see cref="DataTable"/> with the SearchResults as specified in <see cref="PostData"/>.
        /// </summary>
        /// <returns>A <see cref="DataTable"/>.</returns>
        protected virtual DataTable GetSearchResults(PostData postData)
        {
            string whereClause = BuildWhereClause(postData.Searches);

            object[] param = new object[] { };
            if (RootQueryRestriction != null)
            {
                if (whereClause == "")
                {
                    whereClause = $" WHERE {RootQueryRestriction.FilterExpression}";
                }
                else
                {
                    whereClause = whereClause + " AND " + RootQueryRestriction.FilterExpression;
                }

                param = RootQueryRestriction.Parameters.ToArray();
            }

            using (AdoDataConnection connection = new AdoDataConnection(Connection))
            {
                string tableName = TableOperations <T> .GetTableName();

                string sql = "";

                string limit;

                if (Take == null)
                {
                    limit = "";
                }
                else
                {
                    limit = $"TOP {(int)Take}";
                }

                if (SearchSettings == null && CustomView == String.Empty)
                {
                    sql = $@" SELECT {limit} * FROM {tableName} FullTbl {whereClause}
                        ORDER BY { postData.OrderBy} {(postData.Ascending ? "ASC" : "DESC")} ";
                }
                else if (SearchSettings == null)
                {
                    sql = $@" SELECT {limit} * FROM({CustomView}) FullTbl 
                        {whereClause}
                        ORDER BY {postData.OrderBy} {(postData.Ascending ? "ASC" : "DESC")}";
                }
                else
                {
                    string pivotCollums = "(" + String.Join(",", postData.Searches.Where(item => item.isPivotColumn).Select(search => "'" + search.FieldName + "'")) + ")";

                    if (pivotCollums == "()")
                    {
                        pivotCollums = "('')";
                    }

                    string collumnCondition = SearchSettings.Condition;
                    if (collumnCondition != String.Empty)
                    {
                        collumnCondition = $"({collumnCondition}) AND ";
                    }
                    collumnCondition = collumnCondition + $"{SearchSettings.FieldKeyField} IN {pivotCollums}";

                    string searchSettingConditions = SearchSettings.Condition;
                    if (searchSettingConditions != String.Empty)
                    {
                        searchSettingConditions = "(" + searchSettingConditions + ")";
                    }

                    string joinCondition = $"af.FieldName IN {pivotCollums} AND ";
                    joinCondition = joinCondition + searchSettingConditions;
                    if (SearchSettings.Condition != String.Empty)
                    {
                        joinCondition = $"{joinCondition} AND ";
                    }
                    joinCondition = joinCondition + $"SRC.{PrimaryKeyField} = AF.{SearchSettings.PrimaryKeyField}";

                    string sqlPivotColumns = $@"
                        SELECT '[AFV_' + [Key] + ']'
                            FROM (Select DISTINCT {SearchSettings.FieldKeyField} AS [Key] FROM {SearchSettings.AdditionalFieldTable} AS AF WHERE {collumnCondition}  ) AS [Fields]";
                    sqlPivotColumns = string.Join(",", connection.RetrieveData(sqlPivotColumns).Select().Select(r => r[0].ToString()));
                    string tblSelect  = $@"
                        (SELECT 
                            SRC.*,
                            'AFV_' + AF.{SearchSettings.FieldKeyField} AS AFFieldKey,
                            AF.{SearchSettings.ValueField} AS AFValue
                        FROM  {(string.IsNullOrEmpty(CustomView) ? tableName : $"({CustomView})")} SRC LEFT JOIN
                            {SearchSettings.AdditionalFieldTable} AF ON {joinCondition}
Ejemplo n.º 6
0
        public IHttpActionResult GetMetersUsingSearchableList([FromBody] PostData searches)
        {
            if (!AllowSearch || (GetRoles != string.Empty && !User.IsInRole(GetRoles)))
            {
                return(Unauthorized());
            }

            try
            {
                string whereClause = BuildWhereClause(searches.Searches);


                using (AdoDataConnection connection = new AdoDataConnection(Connection))
                {
                    string addtionalFieldTableName = TableOperations <AdditionalField> .GetTableName();

                    string addtionalFieldValueTableName = TableOperations <AdditionalFieldValue> .GetTableName();

                    string meterTableName = TableOperations <Meter> .GetTableName();

                    string locationTableName = TableOperations <Location> .GetTableName();

                    string assetTableName = TableOperations <Asset> .GetTableName();

                    string assetLocationTableName = TableOperations <AssetLocation> .GetTableName();

                    string view = $@"
                    SELECT
                        DISTINCT
                        l.ID,
	                    l.LocationKey,
	                    l.Name,
	                    COUNT(DISTINCT m.ID) as Meters,
	                    COUNT(DISTINCT al.AssetID) as Assets
                    FROM
                        {locationTableName} as l LEFT JOIN
                        {meterTableName} as m ON l.ID = m.LocationID LEFT JOIN
                        {assetLocationTableName} as al ON l.ID = al.LocationID LEFT JOIN
                        {assetTableName} as a ON al.AssetID = a.ID
                    GROUP BY
                        l.ID,
	                    l.LocationKey,
	                    l.Name
                    ";

                    string sql = "";

                    sql = $@"
                        DECLARE @PivotColumns NVARCHAR(MAX) = N''

                        SELECT @PivotColumns = @PivotColumns + '[AFV_' + t.FieldName + '],'
                            FROM (Select DISTINCT FieldName FROM {addtionalFieldTableName} WHERE ParentTable = 'Location') AS t


                        DECLARE @SQLStatement NVARCHAR(MAX) = N''
                        
                        IF @PivotColumns != ''
                            SET @SQLStatement = N'
                            SELECT * INTO #Tbl FROM (
                            SELECT 
                                M.*,
                                (CONCAT(''AFV_'',af.FieldName)) AS FieldName,
	                            afv.Value
                            FROM ({view.Replace("'", "''")}) M LEFT JOIN 
                                {addtionalFieldTableName} af on af.ParentTable = ''Location'' LEFT JOIN
	                            {addtionalFieldValueTableName} afv ON m.ID = afv.ParentTableID AND af.ID = afv.AdditionalFieldID
                            ) as T PIVOT (
                                Max(T.Value) FOR T.FieldName IN ('+ SUBSTRING(@PivotColumns,0, LEN(@PivotColumns)) + ')) AS PVT
                            {whereClause.Replace("'", "''")}
                            ORDER BY { searches.OrderBy} {(searches.Ascending ? "ASC" : "DESC")};

                            DECLARE @NoNPivotColumns NVARCHAR(MAX) = N''''
                                SELECT @NoNPivotColumns = @NoNPivotColumns + ''[''+ name + ''],''
                                    FROM tempdb.sys.columns WHERE  object_id = Object_id(''tempdb..#Tbl'') AND name NOT LIKE ''AFV%''; 
		                    DECLARE @CleanSQL NVARCHAR(MAX) = N''SELECT '' + SUBSTRING(@NoNPivotColumns,0, LEN(@NoNPivotColumns)) + ''FROM #Tbl''

		                    exec sp_executesql @CleanSQL
                        '

                        ELSE 
                            SET @SQLStatement = 'SELECT * FROM ({view.Replace("'", "''")}) T {whereClause.Replace("'", "''")} ORDER BY { searches.OrderBy} {(searches.Ascending ? "ASC" : "DESC")}'
                        exec sp_executesql @SQLStatement";

                    DataTable table = connection.RetrieveData(sql, "");

                    return(Ok(JsonConvert.SerializeObject(table)));
                }
            }
            catch (Exception ex)
            {
                return(InternalServerError(ex));
            }
        }