Select With Local Datetime.sql « Scripts « DB « Software - Tango - Twine softwares

about summary refs log tree commit diff stats

path: root/Software/DB/Scripts/Select With Local Datetime.sql

blob: f0c22ea91422ca6eea044f4b7f87b8ddc52e1c82 (plain)

1
2

SELECT CONVERT(datetime, SWITCHOFFSET(START_DATE, DATEPART(TZOFFSET, 
START_DATE AT TIME ZONE 'Israel Standard Time'))) as LOCAL_TIME, * FROM JOB_RUNS ORDER BY START_DATE DESC

#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.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Tango.Core;
using Tango.DAL.Remote.DB;
using Tango.Settings;
using Tango.Synchronization;
using Tango.Synchronization.Conversion;

namespace Tango.SQLiteGenerator.CLI
{
    public enum Config
    {
        Default,
        CopyAll,
    }

    class Program
    {
        static void Main(string[] args)
        {
            Console.Title = "Tango SQLite Generator";

#if DEBUG
            args = new string[]
            {
                    Path.GetFullPath( "..\\..\\..\\DB\\Tango.db"),
                    "CopyAll"
            };
#endif

            String sqlitePath = args[0];
            Config conf = args.Length > 1 ? (Config)Enum.Parse(typeof(Config), args[1]) : Config.Default;

            sqlitePath = Path.GetFullPath(sqlitePath);

            Console.WriteLine("Generating " + sqlitePath + "...");

            bool completed = false;

            String connectionString = SettingsManager.Default.GetOrCreate<CoreSettings>().DataBaseSource;

            Console.WriteLine("Connecting to " + connectionString + "...");

            connectionString = String.Format("Data Source={0};Initial Catalog=Tango;Integrated Security=SSPI;", connectionString);

            List<SYNC_CONFIGURATIONS> sync_configurations = new List<SYNC_CONFIGURATIONS>();

            using (RemoteDB db = RemoteDB.CreateDefault())
            {
                sync_configurations = db.SYNC_CONFIGURATIONS.ToList();
            }

            SqlConversionHandler handler = new SqlConversionHandler(delegate (bool done, bool success, int percent, string msg)
            {
                Console.WriteLine(String.Format("{0}  [%{1}]", msg, percent));

                if (done)
                {
                    if (success)
                    {
                        Console.WriteLine("SQLite database has been generated successfully on " + sqlitePath);
                        Environment.Exit(0);
                    }
                    else
                    {
                        Console.WriteLine("SQLite database generation failed!");
                        Environment.Exit(-1);
                    }

                    completed = true;
                }
            });

            SqlTableSelectionHandler selectionHandler = new SqlTableSelectionHandler(delegate (List<TableSchema> schema)
            {
                List<TableSchema> updated = schema.Where(x => x.TableName.ToLower() != "sysdiagrams").ToList();

                if (conf == Config.Default)
                {
                    updated
                    .Where(table => sync_configurations.Where(config => (SyncConfiguration)config.SYNC_TYPE == SyncConfiguration.OverwriteLocal).ToList()
                    .Exists(config => config.TABLE_NAME == table.TableName)).ToList().ForEach(x => x.CopyData = true);
                }
                else if (conf == Config.CopyAll)
                {
                    updated.ForEach(x => x.CopyData = true);
                }

                return updated;
            });

            SqlServerToSQLiteConverter converter = new SqlServerToSQLiteConverter();
            converter.GenerateForeignKeys = true;
            converter.GenerateIndexes = true;

            converter.ConvertSqlServerToSQLiteDatabase(connectionString, sqlitePath, null, handler, selectionHandler, null, false, false);

            while (!completed)
            {
                Console.ReadLine();
            }
        }
    }
}