using System;
using System.Collections.Generic;
using System.Collections.ObjectModel;
using System.ComponentModel;
using System.Linq;
using System.Text;
using System.Threading;
using System.Threading.Tasks;
using System.Drawing;
using Google.Protobuf;
using Tango.BL.Entities;
using Tango.BL.Enumerations;
using Tango.PMR.Stubs;
using Tango.PMR.Diagnostics;
using Tango.FSE.Common.Connection;
using Tango.FSE.Common.Diagnostics;
using Tango.FSE.Procedures;

namespace Tango.FSE.Procedures.Examples.Sql
{
    #region Example
    //Additional name spaces that are required for working with remote SQL.
    using Tango.FSE.Common.SQL;
    using Tango.PPC.Shared.SQL;

    public class Program
    {
        public void OnExecute(IProcedureContext context)
        {

            //Get the remote SQL provider from the Tango IOC container.
            IRemoteSqlProvider sql = context.GetService<IRemoteSqlProvider>();

            //Create the SQL command and direct it to affect both the local machine's database and the global Twine's database.
            RemoteSqlCommand command = new RemoteSqlCommand();
            command.SQL = "UPDATE MACHINES SET IS_DEMO = 0 WHERE SERIAL_NUMBER = '" + context.ConnectedMachine.SerialNumber + "'";
            command.Mode = RemoteSqlCommandMode.Both;

            //Execute the command...
            RemoteSqlCommandResult result = sql.ExecuteSqlCommand(command);

            context.WriteLine("Command executed.");
            context.WriteLine("Local Affected Rows " + result.LocalAffectedRecords);
            context.WriteLine("Global Affected Rows " + result.GlobalAffectedRecords);

            context.WriteLine("");

            //Query the machine's database for all the jobs names and length.
            command.SQL = "SELECT NAME,LAST_RUN FROM JOBS";
            command.Mode = RemoteSqlCommandMode.Local;

            result = sql.ExecuteSqlCommand(command);

            //Check for errors.
            if (!result.HasLocalError)
            {
                //Write the formatted results set to the console.
                context.WriteLine(result.LocalDatSet.ToTableString());
                context.WriteLine("");

                //Iterate over the data set rows and get values.
                foreach (RemoteSqlRow row in result.LocalDatSet.Rows)
                {
                    String name = row.Get<String>("NAME");
                    String lastRun = row.Get<String>("LAST_RUN");

                    context.WriteLine(String.Format("NAME {0}, LAST_RUN {1}", name, lastRun));
                }
            }
            else
            {
                //Write the error to console.
                context.WriteLine(result.LocalError);
            }
        }
    }
    #endregion
}