aboutsummaryrefslogtreecommitdiffstats
path: root/Software/Visual_Studio/Web/Tango.MachineService.Gateway/DTO/EnvironmentConfiguration.cs
blob: 0da9de5e59753501e18aca9f2bf693c450385516 (plain)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;

namespace Tango.MachineService.Gateway.DTO
{
    public class EnvironmentConfiguration
    {
        public String ID { get; set; }
        public String Name { get; set; }
        public String Description { get; set; }
        public String MachineServiceAddress { get; set; }
    }
}
eback */ .highlight .kc { color: #008800; font-weight: bold } /* Keyword.Constant */ .highlight .kd { color: #008800; font-weight: bold } /* Keyword.Declaration */ .highlight .kn { color: #008800; font-weight: bold } /* Keyword.Namespace */ .highlight .kp { color: #008800 } /* Keyword.Pseudo */ .highlight .kr { color: #008800; font-weight: bold } /* Keyword.Reserved */ .highlight .kt { color: #888888; font-weight: bold } /* Keyword.Type */ .highlight .m { color: #0000DD; font-weight: bold } /* Literal.Number */ .highlight .s { color: #dd2200; background-color: #fff0f0 } /* Literal.String */ .highlight .na { color: #336699 } /* Name.Attribute */ .highlight .nb { color: #003388 } /* Name.Builtin */ .highlight .nc { color: #bb0066; font-weight: bold } /* Name.Class */ .highlight .no { color: #003366; font-weight: bold } /* Name.Constant */ .highlight .nd { color: #555555 } /* Name.Decorator */ .highlight .ne { color: #bb0066; font-weight: bold } /* Name.Exception */ .highlight .nf { color: #0066bb; font-weight: bold } /* Name.Function */ .highlight .nl { color: #336699; font-style: italic } /* Name.Label */ .highlight .nn { color: #bb0066; font-weight: bold } /* Name.Namespace */ .highlight .py { color: #336699; font-weight: bold } /* Name.Property */ .highlight .nt { color: #bb0066; font-weight: bold } /* Name.Tag */ .highlight .nv { color: #336699 } /* Name.Variable */ .highlight .ow { color: #008800 } /* Operator.Word */ .highlight .w { color: #bbbbbb } /* Text.Whitespace */ .highlight .mb { color: #0000DD; font-weight: bold } /* Literal.Number.Bin */ .highlight .mf { color: #0000DD; font-weight: bold } /* Literal.Number.Float */ .highlight .mh { color: #0000DD; font-weight: bold } /* Literal.Number.Hex */ .highlight .mi { color: #0000DD; font-weight: bold } /* Literal.Number.Integer */ .highlight .mo { color: #0000DD; font-weight: bold } /* Literal.Number.Oct */ .highlight .sa { color: #dd2200; background-color: #fff0f0 } /* Literal.String.Affix */ .highlight .sb { color: #dd2200; background-color: #fff0f0 } /* Literal.String.Backtick */ .highlight .sc { color: #dd2200; background-color: #fff0f0 } /* Literal.String.Char */ .highlight .dl { color: #dd2200; background-color: #fff0f0 } /* Literal.String.Delimiter */ .highlight .sd { color: #dd2200; background-color: #fff0f0 } /* Literal.String.Doc */ .highlight .s2 { color: #dd2200; background-color: #fff0f0 } /* Literal.String.Double */ .highlight .se { color: #0044dd; background-color: #fff0f0 } /* Literal.String.Escape */ .highlight .sh { color: #dd2200; background-color: #fff0f0 } /* Literal.String.Heredoc */ .highlight .si { color: #3333bb; background-color: #fff0f0 } /* Literal.String.Interpol */ .highlight .sx { color: #22bb22; background-color: #f0fff0 } /* Literal.String.Other */ .highlight .sr { color: #008800; background-color: #fff0ff } /* Literal.String.Regex */ .highlight .s1 { color: #dd2200; background-color: #fff0f0 } /* Literal.String.Single */ .highlight .ss { color: #aa6600; background-color: #fff0f0 } /* Literal.String.Symbol */ .highlight .bp { color: #003388 } /* Name.Builtin.Pseudo */ .highlight .fm { color: #0066bb; font-weight: bold } /* Name.Function.Magic */ .highlight .vc { color: #336699 } /* Name.Variable.Class */ .highlight .vg { color: #dd7700 } /* Name.Variable.Global */ .highlight .vi { color: #3333bb } /* Name.Variable.Instance */ .highlight .vm { color: #336699 } /* Name.Variable.Magic */ .highlight .il { color: #0000DD; font-weight: bold } /* Literal.Number.Integer.Long */
using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Tango.BL;
using Tango.BL.Entities;
using Tango.Core;
using Tango.Core.DI;
using Tango.Core.ExtensionMethods;
using Tango.FSE.BL;
using Tango.FSE.Common.Connection;
using Tango.FSE.Common.SQL;
using Tango.PPC.Shared.SQL;
using Tango.Transport;

namespace Tango.FSE.UI.SQL
{
    public class DefaultRemoteSqlProvider : ExtendedObject, IRemoteSqlProvider
    {
        private List<String> protected_keywords = new List<string>()
        {
            "DROP",
            "DELETE",
            "ALTER",
            "CREATE"
        };

        private List<String> protected_tables = new List<string>()
        {
            nameof(DAL.Remote.DB.ACTION_LOGS),
            nameof(DAL.Remote.DB.ADDRESS),
            nameof(DAL.Remote.DB.APPLICATION_DISPLAY_PANEL_VERSIONS),
            nameof(DAL.Remote.DB.APPLICATION_FIRMWARE_VERSIONS),
            nameof(DAL.Remote.DB.APPLICATION_OS_VERSIONS),
            nameof(DAL.Remote.DB.CARTRIDGE_TYPES),
            nameof(DAL.Remote.DB.CCT),
            nameof(DAL.Remote.DB.COLOR_CATALOGS),
            nameof(DAL.Remote.DB.COLOR_CATALOGS_GROUPS),
            nameof(DAL.Remote.DB.COLOR_CATALOGS_ITEMS),
            nameof(DAL.Remote.DB.COLOR_CATALOGS_ITEMS_RECIPES),
            nameof(DAL.Remote.DB.COLOR_SPACES),
            nameof(DAL.Remote.DB.CONTACT),
            nameof(DAL.Remote.DB.CUSTOMER),
            nameof(DAL.Remote.DB.DISPENSER_TYPES),
            nameof(DAL.Remote.DB.EMBEDDED_FIRMWARE_VERSIONS),
            nameof(DAL.Remote.DB.EVENT_TYPES),
            nameof(DAL.Remote.DB.FIBER_SHAPES),
            nameof(DAL.Remote.DB.FIBER_SYNTHS),
            nameof(DAL.Remote.DB.FSE_VERSIONS),
            nameof(DAL.Remote.DB.HARDWARE_BLOWER_TYPES),
            nameof(DAL.Remote.DB.HARDWARE_BREAK_SENSOR_TYPES),
            nameof(DAL.Remote.DB.HARDWARE_DANCER_TYPES),
            nameof(DAL.Remote.DB.HARDWARE_MOTOR_TYPES),
            nameof(DAL.Remote.DB.HARDWARE_PID_CONTROL_TYPES),
            nameof(DAL.Remote.DB.HARDWARE_SPEED_SENSOR_TYPES),
            nameof(DAL.Remote.DB.HARDWARE_VERSIONS),
            nameof(DAL.Remote.DB.HARDWARE_WINDER_TYPES),
            nameof(DAL.Remote.DB.IDS_PACK_FORMULAS),
            nameof(DAL.Remote.DB.JOB_RUNS),
            nameof(DAL.Remote.DB.LINEAR_MASS_DENSITY_UNITS),
            nameof(DAL.Remote.DB.LIQUID_TYPES),
            nameof(DAL.Remote.DB.LIQUID_TYPES_RMLS),
            nameof(DAL.Remote.DB.MACHINES_EVENTS),
            nameof(DAL.Remote.DB.MACHINE_STUDIO_VERSIONS),
            nameof(DAL.Remote.DB.MACHINE_VERSIONS),
            nameof(DAL.Remote.DB.MEDIA_CONDITIONS),
            nameof(DAL.Remote.DB.MEDIA_MATERIALS),
            nameof(DAL.Remote.DB.MEDIA_PURPOSES),
            nameof(DAL.Remote.DB.MID_TANK_TYPES),
            nameof(DAL.Remote.DB.PERMISSION),
            nameof(DAL.Remote.DB.PROCESS_PARAMETERS_TABLES),
            nameof(DAL.Remote.DB.PROCESS_PARAMETERS_TABLES_GROUPS),
            nameof(DAL.Remote.DB.PUBLISHED_PROCEDURE_PROJECTS),
            nameof(DAL.Remote.DB.PUBLISHED_PROCEDURE_PROJECTS_VERSIONS),
            nameof(DAL.Remote.DB.RML),
            nameof(DAL.Remote.DB.RMLS_SPOOLS),
            nameof(DAL.Remote.DB.ROLE),
            nameof(DAL.Remote.DB.ROLES_PERMISSIONS),
            nameof(DAL.Remote.DB.SITE),
            nameof(DAL.Remote.DB.SITES_CATALOGS),
            nameof(DAL.Remote.DB.SITES_RMLS),
            nameof(DAL.Remote.DB.SPOOL_TYPES),
            nameof(DAL.Remote.DB.TANGO_UPDATES),
            nameof(DAL.Remote.DB.TANGO_VERSIONS),
            nameof(DAL.Remote.DB.TECH_CONTROLLERS),
            nameof(DAL.Remote.DB.TECH_DISPENSERS),
            nameof(DAL.Remote.DB.TECH_HEATERS),
            nameof(DAL.Remote.DB.TECH_IOS),
            nameof(DAL.Remote.DB.TECH_MONITORS),
            nameof(DAL.Remote.DB.TECH_VALVES),
            nameof(DAL.Remote.DB.USER),
            nameof(DAL.Remote.DB.USERS_ROLES),
            nameof(DAL.Remote.DB.WINDING_METHODS),
        };

        [TangoInject]
        private IMachineProvider MachineProvider { get; set; }

        [TangoInject]
        private FSEServicesContainer Services { get; set; }

        public async Task<RemoteSqlCommandResult> ExecuteSqlCommandAsync(RemoteSqlCommand command)
        {
            if (command.Mode == RemoteSqlCommandMode.Global || command.Mode == RemoteSqlCommandMode.Both)
            {
                var machines = await Services.MachinesService.GetAllMachines();
                ValidateSqlStatement(command.SQL, machines);
            }

            LogManager.Log($"Executing remote SQL command:\n{command.ToJsonString()}");

            RemoteSqlCommandResult result = new RemoteSqlCommandResult();

            if (command.Mode == RemoteSqlCommandMode.Local || command.Mode == RemoteSqlCommandMode.Both)
            {
                LogManager.Log("Executing remote SQL command against the remote machine database.");

                if (!MachineProvider.IsPPCAvailable)
                {
                    throw new InvalidOperationException("Could not execute the remote SQL command on the local machine's database.\nNo machine connected or connection type is not supported.");
                }

                try
                {
                    var response = await MachineProvider.MachineOperator.SendGenericRequest<ExecuteSqlRequest, ExecuteSqlResponse>(new ExecuteSqlRequest()
                    {
                        SQL = command.SQL
                    }, new TransportRequestConfig() { Timeout = TimeSpan.FromSeconds(command.Timeout) });

                    result.LocalAffectedRecords = response.AffectedRecords;
                    result.LocalDatSet = response.DataSet;
                }
                catch (Exception ex)
                {
                    LogManager.Log(ex, "Remote SQL command local execution failed.");
                    result.HasLocalError = true;
                    result.LocalError = ex.FlattenMessage();
                    return result;
                }
            }

            if (command.Mode == RemoteSqlCommandMode.Global || command.Mode == RemoteSqlCommandMode.Both)
            {
                LogManager.Log("Executing remote SQL command against the global database.");

                try
                {
                    using (ObservablesContext db = ObservablesContext.CreateDefault())
                    {
                        using (SqlConnection connection = new SqlConnection(db.Database.Connection.ConnectionString))
                        {
                            connection.AccessToken = ObservablesContext.GetActualDataSource().AccessToken;
                            connection.Open();

                            SqlCommand cmd = new SqlCommand(command.SQL, connection);
                            cmd.CommandTimeout = command.Timeout;

                            SqlDataReader reader = await cmd.ExecuteReaderAsync();
                            result.GlobalAffectedRecords = reader.RecordsAffected;
                            result.GlobalDataSet = await RemoteSqlDataSet.Load(reader);
                        }
                    }
                }
                catch (Exception ex)
                {
                    LogManager.Log(ex, "Remote SQL command remote execution failed.");
                    result.HasGlobalError = true;
                    result.GlobalError = ex.FlattenMessage();
                }
            }

            LogManager.Log($"Remote SQL command completed with result:\n{result.ToJsonString()}");

            return result;
        }

        private void ValidateSqlStatement(String sql, List<Machine> machines)
        {
            sql = sql.Trim().ToUpper();

            foreach (var keyword in protected_keywords)
            {
                if (sql.Contains(keyword))
                {
                    throw new InvalidOperationException($"SQL command containing '{keyword}' cannot be executed against the global database.");
                }
            }

            foreach (var table in protected_tables)
            {
                if ((sql.Contains("INSERT") || sql.Contains("UPDATE")) && sql.Contains(table))
                {
                    throw new InvalidOperationException($"SQL command containing INSERT or UPDATE statements cannot be used on table '{table}' when executing against the global database.");
                }
            }

            if (sql.Contains("INSERT") || sql.Contains("UPDATE"))
            {
                if (!sql.Contains(MachineProvider.Machine.SerialNumber.ToUpper()))
                {
                    throw new InvalidOperationException($"SQL command containing INSERT or UPDATE statements must contain the connected machine's serial number, when executing against the global database.");
                }

                if (machines.Where(x => x.SerialNumber != MachineProvider.Machine.SerialNumber).Any(x => sql.Contains(x.SerialNumber.ToUpper())))
                {
                    throw new InvalidOperationException($"SQL command containing INSERT or UPDATE statements cannot contain a serial number other than the connected machines' serial number, when executing against the global database");
                }
            }
        }

        public RemoteSqlCommandResult ExecuteSqlCommand(RemoteSqlCommand command)
        {
            return ExecuteSqlCommandAsync(command).Result;
        }
    }
}