YahooApi.WebService | C# Online Compiler

YahooApi.WebService by messidagod

using Microsoft.AspNetCore.Mvc;
using NodaTime;
using System;
using Stl.Fusion.Authentication;
using System.Collections.Generic;
using System.Threading;
using System.Globalization;
using System.IO;
using System.Net.Http;
using System.Net.Http.Headers;
using System.Text.RegularExpressions;
using System.Xml;
using System.Xml.Linq;
using System.Xml.Schema;
using System.Xml.Serialization;
using System.Threading.Tasks;
using Microsoft.VisualBasic;
using Microsoft.VisualBasic.CompilerServices;
using System.Runtime.CompilerServices;
using HttpPostAttribute = Microsoft.AspNetCore.Mvc.HttpPostAttribute;

namespace Shakely.YahooApi.Server.Services;
public class WebService : ApiControllerAttribute, IWebService
{
    private readonly string _xml;

    public XmlDocument Empty
    {
        get
        {
            return GetEmptyDoc();
        }
    }

    private const string APPDBCONTEXT = "AppDbContext";
    public const int BUFFER_LENGTH = 1024;
    private readonly ILogger _log;
    private readonly IAuth _auth;
    private readonly IAuthBackend _authBackend;
    private readonly DataStore _dataStore;
    private readonly string _symbol;
    private readonly DataUtil _dataUtil;
    private LocalDate StartDate { get; set; } = new LocalDate(DateTime.Now.Year, DateTime.Now.Month, DateTime.Now.Day);

    internal static CultureInfo DefaultCulture { get; set; } = new CultureInfo("en-US");

    /// <summary>
    /// Provides web services.
    /// </summary>
    /// <param name="auth"></param>
    /// <param name="authBackend"></param>
    /// <param name="forismaticClient"></param>
    /// <param name="services"></param>
    /// <param name="log"></param>
    public WebService(
        IAuth auth,
        IAuthBackend authBackend,
        ILogger<WebService>? log = null)
        : base()
    {
        _log = log ??= NullLogger<WebService>.Instance;
        _auth = auth;
        _authBackend = authBackend;
        _dataStore = new("Vic.ai");
        _dataUtil = new();
    }

    [XmlAttribute("noNamespaceSchemaLocation", Namespace = XmlSchema.InstanceNamespace)]
    public string YSITRANALLXSD = @"C:\Voyager\Thirdparty\708web_10954646\XSD\YsiTranAll.xsd";

    public const string vbCrLf = "\r\n";

    [HttpGet]
    public async Task<string> GetRequestXml(string functionName, string database, string? propertyId  = default, string? vendorId = default)
    {
        DataStore dataStore = new();
        string baseXml = "<?xml version=\"1.0\" encoding=\"utf-8\"?>" +
                      "<soap12:Envelope xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\"" +
                         " xmlns:xsd=\"http://www.w3.org/2001/XMLSchema\"" +
                         " xmlns:soap12=\"http://www.w3.org/2003/05/soap-envelope\">" +
                      "</soap12:Envelope>";

        XDocument xml = XDocument.Parse(baseXml);
        XElement envelope = xml.Root;
        XNamespace soap12Ns = envelope.GetNamespaceOfPrefix("soap12");

        vendorId = vendorId is null ? "": vendorId;
        propertyId = propertyId is null ? "" : propertyId;

        var b = "http://tempuri.org/YSI.Interfaces.WebServices/";
        string UserSuffix = "Name";
        string type = "ysiItfVendorInvoicing";

        if (functionName == "RunScript")
        {
            b = "YSI.WebServices.SysSqlScript";
            type = "";
        }
        
        UserSuffix = functionName == "RunScript" ? "Code" : "Name";
        var UserNameValue = database == "Vic.ai" ? _dataStore.UserName : "messi";
        XElement body = new(soap12Ns + "Body");

        envelope.Add(body);

        XNamespace ns = $"{b}{type}";
        XElement profile = new(ns + functionName);
        body.Add(profile);

        var Server = functionName == "RunScript" ? "Server" : "ServerName";

        Log(Server);

        profile.Add(new object[] {
            new XElement(ns + $"User{UserSuffix}", UserNameValue),
            new XElement(ns + "Password", database == "hiid_demo70" ? dataStore.Password : _dataStore.Password),
            new XElement(ns + Server, database == "hiid_demo70" ? dataStore.ServerName : _dataStore.ServerName),
            new XElement(ns + "Database",  database == "hiid_demo70" ? dataStore.Database : _dataStore.Database),
            new XElement(ns + "Platform", _dataStore.Platform),
        });
 
        if (functionName != "RunScript")
        {
            profile.Add(new object[] {
            new XElement(ns + "InterfaceEntity", _dataStore.InterfaceEntity),
            new XElement(ns + "InterfaceLicense", File.ReadAllText(_dataStore.LicenseFilePath).Replace(vbCrLf, "")),
            });
        }

        if (functionName == "RunScript")
        {
            profile.Add(new object[] {
            new XElement(ns + "ScriptFile", "rs_sql_Get_Table_Contents.txt"),
            new XElement(ns + "URL", $"&objname=pmuser&WhereClause=uname='{UserNameValue}'"),
            });
        }

        if (functionName == "GetPayables" || functionName == "GetPayables2")
        {
            profile.Add(new object[]
            {
                new XElement(ns + "YardiPropertyId", propertyId),
                new XElement(ns + "VendorId", vendorId),
                new XElement(ns + "InvoicePostMonthFrom", DateTime.Now.AddYears(-5)),
                new XElement(ns + "InvoicePostMonthTo", DateTime.Now),
                new XElement(ns + "InvoiceFromDate", DateTime.Now.AddYears(-5)),
                new XElement(ns + "InvoiceToDate", DateTime.Now),
                new XElement(ns + "UnpaidOnly", false),
                new XElement(ns + "CheckFromDate", DateTime.Now.AddYears(-5)),
                new XElement(ns + "CheckToDate", DateTime.Now),
                new XElement(ns + "InvoiceCreationFromDate",  DateTime.Now.AddYears(-5)),
                new XElement(ns + "InvoiceCreationToDate", DateTime.Now),
                new XElement(ns + "No3rdPartyInv", false),
            });
        }

        if (functionName == "GetPayables2")
        {
            profile.Add(new object[]
            {
                new XElement(ns + "PayableId", "300330869"),
            });
        }

        if (functionName == "ExportChartOfAccounts")
        {
            profile.Add(new object[]
            {
                new XElement(ns + "PropertyId", propertyId)
            });
        }

        if (functionName == "ReviewPayableBatch")
        {
            profile.Add(new object[]
            {
                new XElement(ns + "BatchId", "")
            });
        }

        if (functionName == "GetPayables2")
        {
            profile.Add(new object[]
            {
                new XElement(ns + "BatchId", "")
            });
        }


        if (functionName == "GetPayableByBatchId")
        {
            profile.Add(new object[]
            {
                new XElement(ns + "BatchId", "1100011137")
            });
        }


        if (functionName == "GetVendors_Login" || functionName == "GetVendor_Login")
        {
            profile.Add(new object[]
            {
                new XElement(ns + "YardiPropertyId", propertyId),
                new XElement(ns + "VendorId", vendorId),
            });
        }

        Log("Request xml:");
        Log(xml.ToString(), ConsoleColor.Green);
        return xml.ToString();
    }

    [HttpPost]
    public virtual async Task<string> PostAsync(string functionName, string database = default, string? propertyId = default, string? vendorId  = default, string baseAddress = "https://www.yardipcv.com/8223tp7s7dev/webservices/itfvendorinvoicing.asmx?op=", CancellationToken cancellationToken = default)
    {
        using var http = new HttpClient();
        string xml;
        var b = "http://tempuri.org/YSI.Interfaces.WebServices/";
        string type = "ysiItfVendorInvoicing";

        if (database == "hiid_demo70")
        {
            baseAddress = Regex.Replace(baseAddress, @"(www\.yardipcv\.com\/8223tp7s7dev)", "localhost/VoyagerDev");
            baseAddress = baseAddress.Replace("https", "http");
        }

        if (functionName == "RunScript")
        {
            baseAddress = baseAddress.Replace("itfvendorinvoicing", "SysSqlScript");
            b = "http://tempuri.org/YSI.WebServices.SysSqlScript/";
            type = "RunScript";
        }

        var url = http.BaseAddress = new Uri($"{baseAddress}{functionName}");
        http.DefaultRequestHeaders.Accept.Add(new MediaTypeWithQualityHeaderValue("application/soap+xml"));

        var reqxml = await GetRequestXml(functionName, database, propertyId, vendorId);
        HttpRequestMessage request = new(HttpMethod.Post, url)
        {
            Content = new StringContent(reqxml, Encoding.UTF8, "application/soap+xml")
        };

        HttpResponseMessage response = await http.SendAsync(request);
        //await http.SendAsync(request).ContinueWith(responseTask => { Log($"Response: {responseTask.Result}"); });

        var responseMessage = await response.Content.ReadAsStreamAsync();
        using (System.IO.StreamReader reader = new(responseMessage, Encoding.UTF8))
        {
            xml = await reader.ReadToEndAsync();
        }

        XDocument doc = XDocument.Parse(xml);

        //Log("Post response: ");
        //Log(doc.ToString(), ConsoleColor.Green);
        return doc.ToString();
    }

    [HttpGet]
    public virtual async Task<string> GetAsync(string functionName, string database, string baseAddress = "https://www.yardipcv.com/8223tp7s7dev/webservices/itfvendorinvoicing.asmx?op=", CancellationToken cancellationToken = default)
    {
        using var http = new HttpClient();
        string xml;
        int start = 0;
        int end = 0;
        string result = string.Empty;
        var b = "http://tempuri.org/YSI.Interfaces.WebServices/";
        string type = "ysiItfVendorInvoicing";

        if (database == "hiid_demo70")
        {
            baseAddress = Regex.Replace(baseAddress, @"(www\.yardipcv\.com\/8223tp7s7dev)", "localhost/VoyagerDev");
            baseAddress = baseAddress.Replace("https", "http");
        }

        if (functionName == "RunScript")
        {
            baseAddress = baseAddress.Replace("itfvendorinvoicing", "SysSqlScript");
            b = "http://tempuri.org/YSI.WebServices.SysSqlScript/";
            type = "RunScript";
        }

        var url = http.BaseAddress = new Uri($"{baseAddress}{functionName}");
       

        http.DefaultRequestHeaders.Accept.Add(new MediaTypeWithQualityHeaderValue("application/soap+xml"));
        var req = await GetRequestXml(functionName, database);
        using HttpRequestMessage request = new(HttpMethod.Get, url)
        {
            Content = new StringContent(req, Encoding.UTF8, "application/soap+xml")
        };

        HttpResponseMessage response = await http.SendAsync(request, cancellationToken);

        var responseMessage = await response.Content.ReadAsStreamAsync(cancellationToken);
        using (System.IO.StreamReader reader = new(responseMessage, Encoding.UTF8))
        {
            xml = await reader.ReadToEndAsync();
        }

        var endText = "</soap12:Envelope>";
        if (functionName == "RunScript")
            type = "SysSqlScript";

        try
        {
            var text = $"\"{b}{type}/{functionName}\"";

            result = xml.ToString().Replace("\r\n\r\n", "\r\n").Replace("&gt;", ">").Replace("&lt;", "<").Trim();

            start = result.IndexOf(text) >= 0 ? result.IndexOf(text) : 0;

            if (!result.Contains(text))
                result += text;

            if (!result.Contains(endText))
                result += endText;
            var end2 = result.IndexOf(endText);
            end = result.Substring(start, result.Length - result.IndexOf(endText)).IndexOf(endText);

            return result[start..end2];
        }
        catch (Exception e)
        {
            Log(e.Message, ConsoleColor.DarkRed);
            http.Dispose();
            return result.ToString();
        }
    }

    private static XmlDocument GetEmptyDoc()
    {
        XmlDocument doc = new();
        XmlDeclaration xmlDeclaration = doc.CreateXmlDeclaration("1.0", "UTF-8", null);
        XmlElement root = doc.DocumentElement;
        doc.InsertBefore(xmlDeclaration, root);

        XmlElement element1 = doc.CreateElement(string.Empty, "body", string.Empty);
        doc.AppendChild(element1);

        XmlElement element2 = doc.CreateElement(string.Empty, "level1", string.Empty);
        element1.AppendChild(element2);

        XmlElement element3 = doc.CreateElement(string.Empty, "level2", string.Empty);
        XmlText text1 = doc.CreateTextNode("text");
        element3.AppendChild(text1);
        element2.AppendChild(element3);

        XmlElement element4 = doc.CreateElement(string.Empty, "level2", string.Empty);
        XmlText text2 = doc.CreateTextNode("other text");
        element4.AppendChild(text2);
        element2.AppendChild(element4);

        return doc;
    }

    private readonly DateTime _startTime = DateTime.UtcNow;
    public virtual async Task<DateTime> GetTime(CancellationToken cancellationToken = default)
    {
        var time = DateTime.Now;
        if (time.Second % 10 == 0)
            // This delay is here solely to let you see ServerTime page in
            // in "Loading" / "Updating" state.
            await Task.Delay(TimeSpan.FromSeconds(10), cancellationToken);
        return time;
    }
}

    public interface IWebService
    {
        Task<string> PostAsync(string functionName, string database, string? vendorId = default, string? propertyId = default, string baseAddress = "https://www.yardipcv.com/8223tp7s7dev/webservices/itfvendorinvoicing.asmx?op=", CancellationToken cancellationToken = default);
        Task<string> GetAsync(string functionName, string database, string baseAddress = "https://www.yardipcv.com/8223tp7s7dev/webservices/itfvendorinvoicing.asmx?op=", CancellationToken cancellationToken = default);
        Task<DateTime> GetTime(CancellationToken cancellationToken = default);
        Task<string> GetRequestXml(string functionName, string database, string? propertyId  = default, string? vendorId = default);
    }	

/// <summary>
///     <para>
///     Provides access to the database.  Using the methods in this class, you can run queries; execute updates, inserts
///     and deletes; execute stored procedures; begin, rollback, or commit a transaction.
///     </para>
/// </summary>
[Serializable]
public class DataStore
{
    private string AppSettings => GetAppSettingsFile();
    public string Connection { get; protected set; }
    public IDictionary<string, string> Connections { get; protected set; } = new Dictionary<string, string>();
    public string UserName { get; set; }
    public string UserCode { get; set; }
    public string Password { get; set; }
    public string ServerName { get; set; }
    public string Database { get; set; }
    public string Platform { get { return "SQL SERVER"; } }
    public string InterfaceEntity { get; set; }
    public string LicenseFilePath { get; set; } = @"D:\license\V100105956VP.lic";

    public const string APPDBCONTEXT = "AppDbContext";
    private string GetAppSettingsFile()
    {
        // var assembly = Path.GetDirectoryName(Path.Combine(Assembly.GetExecutingAssembly().Location, "$../../../"));
        // var result = Path.GetFullPath(assembly + $"/appsettings.json");
        return @"D:\license\appsettings.json";
    }


    /// <summary>
    /// Gets the connection string from appsettings.json file in Server project.
    /// </summary>
    /// <param name="database"></param>
    public DataStore(string database = "AppDbContext")
    {
        database = database == "hiid_demo70" ? "AppDbContext" : database;
        Connection = GetConnectionString(database);

        if (database != "AppDbContext")
        {
            UserName = GetField("UserName").Replace(":", "").Trim();
            UserCode = GetField("UserCode").Replace(":", "").Trim();
            Password = GetField("Password").Replace(":", "").Trim();
            ServerName = GetField("ServerName").Replace(":", "").Trim();
            Database = GetField("Database").Replace(":", "").Trim();
            InterfaceEntity = GetField("InterfaceEntity").Replace(":", "").Trim();
        }
        else if (database == "AppDbContext")
        {
            var connArray = Connection.Split(";");

            UserName = connArray[1].Replace("User ID=", "");
            UserCode = connArray[1].Replace("User ID=", "");
            Password = connArray[2].Replace("Password=", "");
            ServerName = "MESSIDAGOD";
            Database = "hiid_demo70";
        }
    }


    private string GetConnectionString(string database)
    {
        string outputValue;
        List<string> list = new();
        string connection = string.Empty;
        string jsonString = File.ReadAllText(GetAppSettingsFile());
        string pattern = $@"(\""{database}\"":)[\s]";
        RegexOptions options = RegexOptions.Singleline | RegexOptions.Multiline;

        foreach (Match m in Regex.Matches(jsonString, pattern, options))
        {
            var result = jsonString.Substring(m.Index, jsonString.IndexOf("},") + database.Length + pattern.Length).Split("\r\n")[0].ToString() + "$$";
            var final = result.Split("$$").First().Replace(",", "").Replace(database, "").Replace(":", "").Replace("\"", "").Trim();

            if (Environment.MachineName == "MESSIDAGOD")
            {
                final = final.Replace("DESKTOP-CMLV9G9", "MESSIDAGOD");
            }

            connection = final;
        }
        return connection;
    }

    private string GetField(string value)
    {
        string outputValue;
        string connection = string.Empty;
        foreach (var thisLine in File.ReadLines(AppSettings))
        {
            if (thisLine.Contains(value))
            {
                outputValue = thisLine.Replace(value, "").Trim().Replace(nameof(value), "").Replace("\"", "").Replace("\\\\", "\\");

                if (outputValue[outputValue.Length - 1] == (char)44)
                    connection = outputValue[0..^1];
                else
                    connection = outputValue;
            }
        }
        return connection;
    }

    /// <summary>
    ///     Execute1RowSQL throws this exception if more than 1 row is found.
    /// </summary>
    [Serializable]
    public class MultipleRowsFoundException : ApplicationException
    {
        public MultipleRowsFoundException()
            : base("Multiple rows found.")
        {
        }

        public MultipleRowsFoundException(string Message)
            : base(Message)
        {
        }

        public MultipleRowsFoundException(string Message, Exception Inner)
            : base(Message, Inner)
        {
        }
    }

    /// <summary>
    ///     Thrown when a Rollback is executed.
    /// </summary>
    [Serializable]
    public class TransactionRollbackException : ApplicationException
    {
        public TransactionRollbackException()
            : base("A Transaction Rollback has been executed.  Your changes have not been posted.")
        {
        }

        public TransactionRollbackException(string Message)
            : base(Message)
        {
        }

        public TransactionRollbackException(string Message, Exception Inner)
            : base(Message, Inner)
        {
        }
    }

    /// <summary>
    ///     ExecuteNonQuery throws this exception if someone attempts to insert a row that violates
    ///     a code field unique contraint.
    /// </summary>
    [Serializable]
    public class DuplicateCodeException : ApplicationException
    {
        public DuplicateCodeException()
            : base("Code duplicates one already in use.")
        {
        }
    }

    [Serializable]
    public class StoredProcedureNoRowException : ApplicationException
    {
        public StoredProcedureNoRowException(string Message)
            : base(Message)
        {
        }
    }

    [Serializable]
    public class StoredProcedureExecutionException : ApplicationException
    {
        public StoredProcedureExecutionException(string Message, Exception Inner)
            : base(Message, Inner)
        {
        }

        public StoredProcedureExecutionException(string Message)
            : base(Message)
        {
        }
    }
}

public class DataUtil {

	public DataUtil() { }

	/// <summary>
	///     Given a DataItem object, will return an Object that is formttted for use in a SQL
	///     statement. This function will call the datatype specific function.  For instance, if you 
	///     pass in a Date, then it will call toSQLDate.  
	/// </summary>
	public object ToSQL(object Value)
	{
		if (Value == null || (object)Value.GetType() == typeof(DBNull))
			return "NULL";
		string name = Value.GetType().Name;
		if (Operators.CompareString(name, typeof(bool).Name, TextCompare: false) == 0)
			return ToBoolean(Convert.ToBoolean(RuntimeHelpers.GetObjectValue(Value)));
		if (Operators.CompareString(name, typeof(DateTime).Name, TextCompare: false) == 0)
			return ToSQLDate(Convert.ToDateTime(RuntimeHelpers.GetObjectValue(Value)));
		if (Operators.CompareString(name, typeof(DateTime).Name, TextCompare: false) == 0)
			return ToSQLDate(Convert.ToDateTime(RuntimeHelpers.GetObjectValue(Value)));
		if (Operators.CompareString(name, typeof(string).Name, TextCompare: false) == 0)
		{
			if (Operators.CompareString(Convert.ToString(RuntimeHelpers.GetObjectValue(Value)).ToUpper().Trim(), "NULL", TextCompare: false) == 0)
				return "NULL";
			return ToSQLString(Convert.ToString(RuntimeHelpers.GetObjectValue(Value)));
		}
		if (Operators.CompareString(name, typeof(short).Name, TextCompare: false) == 0 || Operators.CompareString(name, typeof(int).Name, TextCompare: false) == 0 || Operators.CompareString(name, typeof(long).Name, TextCompare: false) == 0 || Operators.CompareString(name, typeof(double).Name, TextCompare: false) == 0 || Operators.CompareString(name, typeof(float).Name, TextCompare: false) == 0 || Operators.CompareString(name, typeof(int).Name, TextCompare: false) == 0 || Operators.CompareString(name, typeof(short).Name, TextCompare: false) == 0 || Operators.CompareString(name, typeof(long).Name, TextCompare: false) == 0 || Operators.CompareString(name, typeof(decimal).Name, TextCompare: false) == 0)
			return Value;
		if (Value.GetType().IsEnum)
			return Convert.ToInt32(RuntimeHelpers.GetObjectValue(Value));
		throw new ApplicationException($"Invalid type in DataUtil.toSQL : {Value.GetType().Name}");
	}

	/// <summary>
	/// Appends _v1 or _v2 or _v3 etc. to file name if the file already exists.
	/// </summary>
	/// <param name="fullpath"></param>
	/// <returns>full path of the file name</returns>
	public static string AddVersionToFileName(string fullpath)
	{
		string suffix = "_v";
		int i = 1;
		bool Exists = false;
		while (!Exists)
		{
			string version = $"{suffix}{i}";
			string newFieFull = Path.Combine(Path.GetDirectoryName(fullpath), Path.GetFileNameWithoutExtension(fullpath)) + version + Path.GetExtension(fullpath);
			if (!File.Exists(newFieFull))
			{
				fullpath = newFieFull;
				Exists = !Exists;
			}
			i++;
		}

		return fullpath;
	}

 

	/// <summary>
	///     Returns 0 for false, -1 for true.
	/// </summary>
	public static int ToBoolean(bool Value)
	{
		if (Value)
			return -1;
		return 0;
	}

	/// <summary>
	///     Returns a string value formatted for use in a SQL.  
	///     Given a string of Macy's, will return 'Macy''s'.  Note that this function 
	///     add's the ' characters around the string.  Also note that this function does 
	///     not trim the string.
	/// </summary>
	public string ToSQLString(string Input)
	{
		if (Input == null)
			return "''";
		return string.Format("N'{0}'", Input.Replace("'", "''"));
	}

	/// <summary>
	///     Returns a string value formatted for use in an in clause of a SQL statement.  
	///     Given a string array of:
	///     Input(0) = "My"
	///     Input(1) = "Values"
	///     will return "('My', 'Values')".  
	///     Note that this function 
	///     add's the ' characters around the string.  Also note that this function does 
	///     not trim the string.
	/// </summary>
	public string ToSQLString(string[] Input)
	{
		string Value = "(";
		int upperBound = Input.GetUpperBound(0);
		checked
		{
			for (int i = 0; i <= upperBound; i++)
			{
				Value = Value + ToSQLString(Input[i]) + ",";
			}
			return string.Concat(Value.AsSpan(0, Value.Length - 1), ")");
		}
	}

	/// <summary>
	///     Formats a date for use in a SQL statement.  If a NULL
	///     date is passed in (i.e. myDate.ToOADate = 0), then this function returns "NULL".
	///     Given a date of January 15, 2003 will return either:
	///         SQL Server: "convert(datetime, '01/15/2003', 101)"
	/// </summary>
	public string ToSQLDate(DateTime myDate)
	{
		try
		{
			if (myDate.ToOADate() == 0.0)
				return "NULL";
			if (IsNullDate(myDate))
				return "NULL";
		}
		catch (OverflowException ex2)
		{
			ProjectData.SetProjectError(ex2);
			OverflowException ex = ex2;
			throw new ApplicationException($"Invalid date : {myDate}.", ex);
		}
		if (myDate.TimeOfDay.Ticks > 0)
			return ToSQLDateTime(myDate);

		return string.Format("convert(datetime, '{0}', 101)", Strings.Format(myDate, "MM/dd/yyyy"));
	}

	/// <summary>
	///     Formats a date/time for use in a SQL statement.  If a NULL
	///     date is passed in (i.e. myDate.ToOADate = 0), then this function returns "NULL".
	///     Given a date of January 15, 2003 will return either:
	///         Oracle: "to_date('15-jan-2003 08:00:00:am', 'dd-mon-yyyy hh:mi:ss:am')" 
	///         SQL Server: "convert(datetime, '01/15/2003 08:00:00:am', 1)"
	/// </summary>
	public string ToSQLDateTime(DateTime myDate)
	{
		if (myDate.ToOADate() == 0.0)
			return "NULL";
		if (IsNullDate(myDate))
			return "NULL";
		if (myDate.Year < 1899)
			myDate = new DateTime(1899, 1, 1, myDate.Hour, myDate.Minute, myDate.Second);

		return string.Format("convert(datetime, '{0}', 101)", Strings.Format(myDate, "MM/dd/yyyy hh:mm:sstt"));
	}

	/// <summary>
	/// 
	/// </summary>
	/// <param name="Value"></param>
	/// <returns>boolean</returns>
	public bool IsNullDate(DateTime Value)
	{
		if (DateTime.Compare(Value, DateTime.MinValue) == 0)
			return true;
		if (Value.Equals(DateTime.MinValue))
			return true;
		if (DateTime.Compare(Value, default(DateTime)) == 0)
			return true;
		return false;
	}

}
    public class Logging
    {
		public static void Log(dynamic message, ConsoleColor color = ConsoleColor.Cyan)
		{
			if (message == null)
				return;
			Console.ForegroundColor = color;
			Console.Write(message);
			Console.WriteLine();
			Console.ResetColor();
		}
	}

​x
 
using Microsoft.AspNetCore.Mvc;
using NodaTime;
using System;
using Stl.Fusion.Authentication;
using System.Collections.Generic;
using System.Threading;
using System.Globalization;
using System.IO;
using System.Net.Http;
using System.Net.Http.Headers;
using System.Text.RegularExpressions;
using System.Xml;
using System.Xml.Linq;
using System.Xml.Schema;
using System.Xml.Serialization;
using System.Threading.Tasks;
using Microsoft.VisualBasic;
using Microsoft.VisualBasic.CompilerServices;
using System.Runtime.CompilerServices;
using HttpPostAttribute = Microsoft.AspNetCore.Mvc.HttpPostAttribute;
​
namespace Shakely.YahooApi.Server.Services;
public class WebService : ApiControllerAttribute, IWebService
{
    private readonly string _xml;
​
    public XmlDocument Empty
    {
        get
        {
            return GetEmptyDoc();
        }
    }
​
    private const string APPDBCONTEXT = "AppDbContext";
    public const int BUFFER_LENGTH = 1024;
    private readonly ILogger _log;
    private readonly IAuth _auth;
    private readonly IAuthBackend _authBackend;
    private readonly DataStore _dataStore;
    private readonly string _symbol;
    private readonly DataUtil _dataUtil;
    private LocalDate StartDate { get; set; } = new LocalDate(DateTime.Now.Year, DateTime.Now.Month, DateTime.Now.Day);
​
    internal static CultureInfo DefaultCulture { get; set; } = new CultureInfo("en-US");
​
    /// <summary>
    /// Provides web services.
    /// </summary>
    /// <param name="auth"></param>
    /// <param name="authBackend"></param>
    /// <param name="forismaticClient"></param>
    /// <param name="services"></param>
    /// <param name="log"></param>
    public WebService(
        IAuth auth,
        IAuthBackend authBackend,
        ILogger<WebService>? log = null)
        : base()
    {
        _log = log ??= NullLogger<WebService>.Instance;
        _auth = auth;
        _authBackend = authBackend;
        _dataStore = new("Vic.ai");
        _dataUtil = new();
    }
​
    [XmlAttribute("noNamespaceSchemaLocation", Namespace = XmlSchema.InstanceNamespace)]
    public string YSITRANALLXSD = @"C:\Voyager\Thirdparty\708web_10954646\XSD\YsiTranAll.xsd";
​
    public const string vbCrLf = "\r\n";
​
    [HttpGet]
    public async Task<string> GetRequestXml(string functionName, string database, string? propertyId  = default, string? vendorId = default)
    {
        DataStore dataStore = new();
        string baseXml = "<?xml version=\"1.0\" encoding=\"utf-8\"?>" +
                      "<soap12:Envelope xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\"" +
                         " xmlns:xsd=\"http://www.w3.org/2001/XMLSchema\"" +
                         " xmlns:soap12=\"http://www.w3.org/2003/05/soap-envelope\">" +
                      "</soap12:Envelope>";
​
        XDocument xml = XDocument.Parse(baseXml);
        XElement envelope = xml.Root;
        XNamespace soap12Ns = envelope.GetNamespaceOfPrefix("soap12");
​
        vendorId = vendorId is null ? "": vendorId;
        propertyId = propertyId is null ? "" : propertyId;
​
        var b = "http://tempuri.org/YSI.Interfaces.WebServices/";
        string UserSuffix = "Name";
        string type = "ysiItfVendorInvoicing";
​
        if (functionName == "RunScript")
        {
            b = "YSI.WebServices.SysSqlScript";
            type = "";
        }
        
        UserSuffix = functionName == "RunScript" ? "Code" : "Name";
        var UserNameValue = database == "Vic.ai" ? _dataStore.UserName : "messi";
        XElement body = new(soap12Ns + "Body");
​
        envelope.Add(body);
​
        XNamespace ns = $"{b}{type}";
        XElement profile = new(ns + functionName);
        body.Add(profile);
​
        var Server = functionName == "RunScript" ? "Server" : "ServerName";
​
        Log(Server);
​
        profile.Add(new object[] {
            new XElement(ns + $"User{UserSuffix}", UserNameValue),
            new XElement(ns + "Password", database == "hiid_demo70" ? dataStore.Password : _dataStore.Password),
            new XElement(ns + Server, database == "hiid_demo70" ? dataStore.ServerName : _dataStore.ServerName),
            new XElement(ns + "Database",  database == "hiid_demo70" ? dataStore.Database : _dataStore.Database),
            new XElement(ns + "Platform", _dataStore.Platform),
        });
 
        if (functionName != "RunScript")
        {
            profile.Add(new object[] {
            new XElement(ns + "InterfaceEntity", _dataStore.InterfaceEntity),
            new XElement(ns + "InterfaceLicense", File.ReadAllText(_dataStore.LicenseFilePath).Replace(vbCrLf, "")),
            });
        }
​
        if (functionName == "RunScript")
        {
            profile.Add(new object[] {
            new XElement(ns + "ScriptFile", "rs_sql_Get_Table_Contents.txt"),
            new XElement(ns + "URL", $"&objname=pmuser&WhereClause=uname='{UserNameValue}'"),
            });
        }
​
        if (functionName == "GetPayables" || functionName == "GetPayables2")
        {
            profile.Add(new object[]
            {
                new XElement(ns + "YardiPropertyId", propertyId),
                new XElement(ns + "VendorId", vendorId),
                new XElement(ns + "InvoicePostMonthFrom", DateTime.Now.AddYears(-5)),
                new XElement(ns + "InvoicePostMonthTo", DateTime.Now),
                new XElement(ns + "InvoiceFromDate", DateTime.Now.AddYears(-5)),
                new XElement(ns + "InvoiceToDate", DateTime.Now),
                new XElement(ns + "UnpaidOnly", false),
                new XElement(ns + "CheckFromDate", DateTime.Now.AddYears(-5)),
                new XElement(ns + "CheckToDate", DateTime.Now),
                new XElement(ns + "InvoiceCreationFromDate",  DateTime.Now.AddYears(-5)),
                new XElement(ns + "InvoiceCreationToDate", DateTime.Now),
                new XElement(ns + "No3rdPartyInv", false),
            });
        }
​
        if (functionName == "GetPayables2")
        {
            profile.Add(new object[]
            {
                new XElement(ns + "PayableId", "300330869"),
            });
        }
​
        if (functionName == "ExportChartOfAccounts")
        {
            profile.Add(new object[]
            {
                new XElement(ns + "PropertyId", propertyId)
            });
        }
​
        if (functionName == "ReviewPayableBatch")
        {
            profile.Add(new object[]
            {
                new XElement(ns + "BatchId", "")
            });
        }
​
        if (functionName == "GetPayables2")
        {
            profile.Add(new object[]
            {
                new XElement(ns + "BatchId", "")
            });
        }
​
​
        if (functionName == "GetPayableByBatchId")
        {
            profile.Add(new object[]
            {
                new XElement(ns + "BatchId", "1100011137")
            });
        }
​
​
        if (functionName == "GetVendors_Login" || functionName == "GetVendor_Login")
        {
            profile.Add(new object[]
            {
                new XElement(ns + "YardiPropertyId", propertyId),
                new XElement(ns + "VendorId", vendorId),
            });
        }
​
        Log("Request xml:");
        Log(xml.ToString(), ConsoleColor.Green);
        return xml.ToString();
    }
​
    [HttpPost]
    public virtual async Task<string> PostAsync(string functionName, string database = default, string? propertyId = default, string? vendorId  = default, string baseAddress = "https://www.yardipcv.com/8223tp7s7dev/webservices/itfvendorinvoicing.asmx?op=", CancellationToken cancellationToken = default)
    {
        using var http = new HttpClient();
        string xml;
        var b = "http://tempuri.org/YSI.Interfaces.WebServices/";
        string type = "ysiItfVendorInvoicing";
​
        if (database == "hiid_demo70")
        {
            baseAddress = Regex.Replace(baseAddress, @"(www\.yardipcv\.com\/8223tp7s7dev)", "localhost/VoyagerDev");
            baseAddress = baseAddress.Replace("https", "http");
        }
​
        if (functionName == "RunScript")
        {
            baseAddress = baseAddress.Replace("itfvendorinvoicing", "SysSqlScript");
            b = "http://tempuri.org/YSI.WebServices.SysSqlScript/";
            type = "RunScript";
        }
​
        var url = http.BaseAddress = new Uri($"{baseAddress}{functionName}");
        http.DefaultRequestHeaders.Accept.Add(new MediaTypeWithQualityHeaderValue("application/soap+xml"));
​
        var reqxml = await GetRequestXml(functionName, database, propertyId, vendorId);
        HttpRequestMessage request = new(HttpMethod.Post, url)
        {
            Content = new StringContent(reqxml, Encoding.UTF8, "application/soap+xml")
        };
​
        HttpResponseMessage response = await http.SendAsync(request);
        //await http.SendAsync(request).ContinueWith(responseTask => { Log($"Response: {responseTask.Result}"); });
​
        var responseMessage = await response.Content.ReadAsStreamAsync();
        using (System.IO.StreamReader reader = new(responseMessage, Encoding.UTF8))
        {
            xml = await reader.ReadToEndAsync();
        }
​
        XDocument doc = XDocument.Parse(xml);
​
        //Log("Post response: ");
        //Log(doc.ToString(), ConsoleColor.Green);
        return doc.ToString();
    }
​
    [HttpGet]
    public virtual async Task<string> GetAsync(string functionName, string database, string baseAddress = "https://www.yardipcv.com/8223tp7s7dev/webservices/itfvendorinvoicing.asmx?op=", CancellationToken cancellationToken = default)
    {
        using var http = new HttpClient();
        string xml;
        int start = 0;
        int end = 0;
        string result = string.Empty;
        var b = "http://tempuri.org/YSI.Interfaces.WebServices/";
        string type = "ysiItfVendorInvoicing";
​
        if (database == "hiid_demo70")
        {
            baseAddress = Regex.Replace(baseAddress, @"(www\.yardipcv\.com\/8223tp7s7dev)", "localhost/VoyagerDev");
            baseAddress = baseAddress.Replace("https", "http");
        }
​
        if (functionName == "RunScript")
        {
            baseAddress = baseAddress.Replace("itfvendorinvoicing", "SysSqlScript");
            b = "http://tempuri.org/YSI.WebServices.SysSqlScript/";
            type = "RunScript";
        }
​
        var url = http.BaseAddress = new Uri($"{baseAddress}{functionName}");
       
​
        http.DefaultRequestHeaders.Accept.Add(new MediaTypeWithQualityHeaderValue("application/soap+xml"));
        var req = await GetRequestXml(functionName, database);
        using HttpRequestMessage request = new(HttpMethod.Get, url)
        {
            Content = new StringContent(req, Encoding.UTF8, "application/soap+xml")
        };
​
        HttpResponseMessage response = await http.SendAsync(request, cancellationToken);
​
        var responseMessage = await response.Content.ReadAsStreamAsync(cancellationToken);
        using (System.IO.StreamReader reader = new(responseMessage, Encoding.UTF8))
        {
            xml = await reader.ReadToEndAsync();
        }
​
        var endText = "</soap12:Envelope>";
        if (functionName == "RunScript")
            type = "SysSqlScript";
​
        try
        {
            var text = $"\"{b}{type}/{functionName}\"";
​
            result = xml.ToString().Replace("\r\n\r\n", "\r\n").Replace("&gt;", ">").Replace("&lt;", "<").Trim();
​
            start = result.IndexOf(text) >= 0 ? result.IndexOf(text) : 0;
​
            if (!result.Contains(text))
                result += text;
​
            if (!result.Contains(endText))
                result += endText;
            var end2 = result.IndexOf(endText);
            end = result.Substring(start, result.Length - result.IndexOf(endText)).IndexOf(endText);
​
            return result[start..end2];
        }
        catch (Exception e)
        {
            Log(e.Message, ConsoleColor.DarkRed);
            http.Dispose();
            return result.ToString();
        }
    }
​
    private static XmlDocument GetEmptyDoc()
    {
        XmlDocument doc = new();
        XmlDeclaration xmlDeclaration = doc.CreateXmlDeclaration("1.0", "UTF-8", null);
        XmlElement root = doc.DocumentElement;
        doc.InsertBefore(xmlDeclaration, root);
​
        XmlElement element1 = doc.CreateElement(string.Empty, "body", string.Empty);
        doc.AppendChild(element1);
​
        XmlElement element2 = doc.CreateElement(string.Empty, "level1", string.Empty);
        element1.AppendChild(element2);
​
        XmlElement element3 = doc.CreateElement(string.Empty, "level2", string.Empty);
        XmlText text1 = doc.CreateTextNode("text");
        element3.AppendChild(text1);
        element2.AppendChild(element3);
​
        XmlElement element4 = doc.CreateElement(string.Empty, "level2", string.Empty);
        XmlText text2 = doc.CreateTextNode("other text");
        element4.AppendChild(text2);
        element2.AppendChild(element4);
​
        return doc;
    }
​
    private readonly DateTime _startTime = DateTime.UtcNow;
    public virtual async Task<DateTime> GetTime(CancellationToken cancellationToken = default)
    {
        var time = DateTime.Now;
        if (time.Second % 10 == 0)
            // This delay is here solely to let you see ServerTime page in
            // in "Loading" / "Updating" state.
            await Task.Delay(TimeSpan.FromSeconds(10), cancellationToken);
        return time;
    }
}
​
    public interface IWebService
    {
        Task<string> PostAsync(string functionName, string database, string? vendorId = default, string? propertyId = default, string baseAddress = "https://www.yardipcv.com/8223tp7s7dev/webservices/itfvendorinvoicing.asmx?op=", CancellationToken cancellationToken = default);
        Task<string> GetAsync(string functionName, string database, string baseAddress = "https://www.yardipcv.com/8223tp7s7dev/webservices/itfvendorinvoicing.asmx?op=", CancellationToken cancellationToken = default);
        Task<DateTime> GetTime(CancellationToken cancellationToken = default);
        Task<string> GetRequestXml(string functionName, string database, string? propertyId  = default, string? vendorId = default);
    }   
​
/// <summary>
///     <para>
///     Provides access to the database.  Using the methods in this class, you can run queries; execute updates, inserts
///     and deletes; execute stored procedures; begin, rollback, or commit a transaction.
///     </para>
/// </summary>
[Serializable]
public class DataStore
{
    private string AppSettings => GetAppSettingsFile();
    public string Connection { get; protected set; }
    public IDictionary<string, string> Connections { get; protected set; } = new Dictionary<string, string>();
    public string UserName { get; set; }
    public string UserCode { get; set; }
    public string Password { get; set; }
    public string ServerName { get; set; }
    public string Database { get; set; }
    public string Platform { get { return "SQL SERVER"; } }
    public string InterfaceEntity { get; set; }
    public string LicenseFilePath { get; set; } = @"D:\license\V100105956VP.lic";
​
    public const string APPDBCONTEXT = "AppDbContext";
    private string GetAppSettingsFile()
    {
        // var assembly = Path.GetDirectoryName(Path.Combine(Assembly.GetExecutingAssembly().Location, "$../../../"));
        // var result = Path.GetFullPath(assembly + $"/appsettings.json");
        return @"D:\license\appsettings.json";
    }
​
​
    /// <summary>
    /// Gets the connection string from appsettings.json file in Server project.
    /// </summary>
    /// <param name="database"></param>
    public DataStore(string database = "AppDbContext")
    {
        database = database == "hiid_demo70" ? "AppDbContext" : database;
        Connection = GetConnectionString(database);
​
        if (database != "AppDbContext")
        {
            UserName = GetField("UserName").Replace(":", "").Trim();
            UserCode = GetField("UserCode").Replace(":", "").Trim();
            Password = GetField("Password").Replace(":", "").Trim();
            ServerName = GetField("ServerName").Replace(":", "").Trim();
            Database = GetField("Database").Replace(":", "").Trim();
            InterfaceEntity = GetField("InterfaceEntity").Replace(":", "").Trim();
        }
        else if (database == "AppDbContext")
        {
            var connArray = Connection.Split(";");
​
            UserName = connArray[1].Replace("User ID=", "");
            UserCode = connArray[1].Replace("User ID=", "");
            Password = connArray[2].Replace("Password=", "");
            ServerName = "MESSIDAGOD";
            Database = "hiid_demo70";
        }
    }
​
​
    private string GetConnectionString(string database)
    {
        string outputValue;
        List<string> list = new();
        string connection = string.Empty;
        string jsonString = File.ReadAllText(GetAppSettingsFile());
        string pattern = $@"(\""{database}\"":)[\s]";
        RegexOptions options = RegexOptions.Singleline | RegexOptions.Multiline;
​
        foreach (Match m in Regex.Matches(jsonString, pattern, options))
        {
            var result = jsonString.Substring(m.Index, jsonString.IndexOf("},") + database.Length + pattern.Length).Split("\r\n")[0].ToString() + "$$";
            var final = result.Split("$$").First().Replace(",", "").Replace(database, "").Replace(":", "").Replace("\"", "").Trim();
​
            if (Environment.MachineName == "MESSIDAGOD")
            {
                final = final.Replace("DESKTOP-CMLV9G9", "MESSIDAGOD");
            }
​
            connection = final;
        }
        return connection;
    }
​
    private string GetField(string value)
    {
        string outputValue;
        string connection = string.Empty;
        foreach (var thisLine in File.ReadLines(AppSettings))
        {
            if (thisLine.Contains(value))
            {
                outputValue = thisLine.Replace(value, "").Trim().Replace(nameof(value), "").Replace("\"", "").Replace("\\\\", "\\");
​
                if (outputValue[outputValue.Length - 1] == (char)44)
                    connection = outputValue[0..^1];
                else
                    connection = outputValue;
            }
        }
        return connection;
    }
​
    /// <summary>
    ///     Execute1RowSQL throws this exception if more than 1 row is found.
    /// </summary>
    [Serializable]
    public class MultipleRowsFoundException : ApplicationException
    {
        public MultipleRowsFoundException()
            : base("Multiple rows found.")
        {
        }
​
        public MultipleRowsFoundException(string Message)
            : base(Message)
        {
        }
​
        public MultipleRowsFoundException(string Message, Exception Inner)
            : base(Message, Inner)
        {
        }
    }
​
    /// <summary>
    ///     Thrown when a Rollback is executed.
    /// </summary>
    [Serializable]
    public class TransactionRollbackException : ApplicationException
    {
        public TransactionRollbackException()
            : base("A Transaction Rollback has been executed.  Your changes have not been posted.")
        {
        }
​
        public TransactionRollbackException(string Message)
            : base(Message)
        {
        }
​
        public TransactionRollbackException(string Message, Exception Inner)
            : base(Message, Inner)
        {
        }
    }
​
    /// <summary>
    ///     ExecuteNonQuery throws this exception if someone attempts to insert a row that violates
    ///     a code field unique contraint.
    /// </summary>
    [Serializable]
    public class DuplicateCodeException : ApplicationException
    {
        public DuplicateCodeException()
            : base("Code duplicates one already in use.")
        {
        }
    }
​
    [Serializable]
    public class StoredProcedureNoRowException : ApplicationException
    {
        public StoredProcedureNoRowException(string Message)
            : base(Message)
        {
        }
    }
​
    [Serializable]
    public class StoredProcedureExecutionException : ApplicationException
    {
        public StoredProcedureExecutionException(string Message, Exception Inner)
            : base(Message, Inner)
        {
        }
​
        public StoredProcedureExecutionException(string Message)
            : base(Message)
        {
        }
    }
}
​
public class DataUtil {
​
    public DataUtil() { }
​
    /// <summary>
    ///     Given a DataItem object, will return an Object that is formttted for use in a SQL
    ///     statement. This function will call the datatype specific function.  For instance, if you 
    ///     pass in a Date, then it will call toSQLDate.  
    /// </summary>
    public object ToSQL(object Value)
    {
        if (Value == null || (object)Value.GetType() == typeof(DBNull))
            return "NULL";
        string name = Value.GetType().Name;
        if (Operators.CompareString(name, typeof(bool).Name, TextCompare: false) == 0)
            return ToBoolean(Convert.ToBoolean(RuntimeHelpers.GetObjectValue(Value)));
        if (Operators.CompareString(name, typeof(DateTime).Name, TextCompare: false) == 0)
            return ToSQLDate(Convert.ToDateTime(RuntimeHelpers.GetObjectValue(Value)));
        if (Operators.CompareString(name, typeof(DateTime).Name, TextCompare: false) == 0)
            return ToSQLDate(Convert.ToDateTime(RuntimeHelpers.GetObjectValue(Value)));
        if (Operators.CompareString(name, typeof(string).Name, TextCompare: false) == 0)
        {
            if (Operators.CompareString(Convert.ToString(RuntimeHelpers.GetObjectValue(Value)).ToUpper().Trim(), "NULL", TextCompare: false) == 0)
                return "NULL";
            return ToSQLString(Convert.ToString(RuntimeHelpers.GetObjectValue(Value)));
        }
        if (Operators.CompareString(name, typeof(short).Name, TextCompare: false) == 0 || Operators.CompareString(name, typeof(int).Name, TextCompare: false) == 0 || Operators.CompareString(name, typeof(long).Name, TextCompare: false) == 0 || Operators.CompareString(name, typeof(double).Name, TextCompare: false) == 0 || Operators.CompareString(name, typeof(float).Name, TextCompare: false) == 0 || Operators.CompareString(name, typeof(int).Name, TextCompare: false) == 0 || Operators.CompareString(name, typeof(short).Name, TextCompare: false) == 0 || Operators.CompareString(name, typeof(long).Name, TextCompare: false) == 0 || Operators.CompareString(name, typeof(decimal).Name, TextCompare: false) == 0)
            return Value;
        if (Value.GetType().IsEnum)
            return Convert.ToInt32(RuntimeHelpers.GetObjectValue(Value));
        throw new ApplicationException($"Invalid type in DataUtil.toSQL : {Value.GetType().Name}");
    }
​
    /// <summary>
    /// Appends _v1 or _v2 or _v3 etc. to file name if the file already exists.
    /// </summary>
    /// <param name="fullpath"></param>
    /// <returns>full path of the file name</returns>
    public static string AddVersionToFileName(string fullpath)
    {
        string suffix = "_v";
        int i = 1;
        bool Exists = false;
        while (!Exists)
        {
            string version = $"{suffix}{i}";
            string newFieFull = Path.Combine(Path.GetDirectoryName(fullpath), Path.GetFileNameWithoutExtension(fullpath)) + version + Path.GetExtension(fullpath);
            if (!File.Exists(newFieFull))
            {
                fullpath = newFieFull;
                Exists = !Exists;
            }
            i++;
        }
​
        return fullpath;
    }
​
 
​
    /// <summary>
    ///     Returns 0 for false, -1 for true.
    /// </summary>
    public static int ToBoolean(bool Value)
    {
        if (Value)
            return -1;
        return 0;
    }
​
    /// <summary>
    ///     Returns a string value formatted for use in a SQL.  
    ///     Given a string of Macy's, will return 'Macy''s'.  Note that this function 
    ///     add's the ' characters around the string.  Also note that this function does 
    ///     not trim the string.
    /// </summary>
    public string ToSQLString(string Input)
    {
        if (Input == null)
            return "''";
        return string.Format("N'{0}'", Input.Replace("'", "''"));
    }
​
    /// <summary>
    ///     Returns a string value formatted for use in an in clause of a SQL statement.  
    ///     Given a string array of:
    ///     Input(0) = "My"
    ///     Input(1) = "Values"
    ///     will return "('My', 'Values')".  
    ///     Note that this function 
    ///     add's the ' characters around the string.  Also note that this function does 
    ///     not trim the string.
    /// </summary>
    public string ToSQLString(string[] Input)
    {
        string Value = "(";
        int upperBound = Input.GetUpperBound(0);
        checked
        {
            for (int i = 0; i <= upperBound; i++)
            {
                Value = Value + ToSQLString(Input[i]) + ",";
            }
            return string.Concat(Value.AsSpan(0, Value.Length - 1), ")");
        }
    }
​
    /// <summary>
    ///     Formats a date for use in a SQL statement.  If a NULL
    ///     date is passed in (i.e. myDate.ToOADate = 0), then this function returns "NULL".
    ///     Given a date of January 15, 2003 will return either:
    ///         SQL Server: "convert(datetime, '01/15/2003', 101)"
    /// </summary>
    public string ToSQLDate(DateTime myDate)
    {
        try
        {
            if (myDate.ToOADate() == 0.0)
                return "NULL";
            if (IsNullDate(myDate))
                return "NULL";
        }
        catch (OverflowException ex2)
        {
            ProjectData.SetProjectError(ex2);
            OverflowException ex = ex2;
            throw new ApplicationException($"Invalid date : {myDate}.", ex);
        }
        if (myDate.TimeOfDay.Ticks > 0)
            return ToSQLDateTime(myDate);
​
        return string.Format("convert(datetime, '{0}', 101)", Strings.Format(myDate, "MM/dd/yyyy"));
    }
​
    /// <summary>
    ///     Formats a date/time for use in a SQL statement.  If a NULL
    ///     date is passed in (i.e. myDate.ToOADate = 0), then this function returns "NULL".
    ///     Given a date of January 15, 2003 will return either:
    ///         Oracle: "to_date('15-jan-2003 08:00:00:am', 'dd-mon-yyyy hh:mi:ss:am')" 
    ///         SQL Server: "convert(datetime, '01/15/2003 08:00:00:am', 1)"
    /// </summary>
    public string ToSQLDateTime(DateTime myDate)
    {
        if (myDate.ToOADate() == 0.0)
            return "NULL";
        if (IsNullDate(myDate))
            return "NULL";
        if (myDate.Year < 1899)
            myDate = new DateTime(1899, 1, 1, myDate.Hour, myDate.Minute, myDate.Second);
​
        return string.Format("convert(datetime, '{0}', 101)", Strings.Format(myDate, "MM/dd/yyyy hh:mm:sstt"));
    }
​
    /// <summary>
    /// 
    /// </summary>
    /// <param name="Value"></param>
    /// <returns>boolean</returns>
    public bool IsNullDate(DateTime Value)
    {
        if (DateTime.Compare(Value, DateTime.MinValue) == 0)
            return true;
        if (Value.Equals(DateTime.MinValue))
            return true;
        if (DateTime.Compare(Value, default(DateTime)) == 0)
            return true;
        return false;
    }
​
}
    public class Logging
    {
        public static void Log(dynamic message, ConsoleColor color = ConsoleColor.Cyan)
        {
            if (message == null)
                return;
            Console.ForegroundColor = color;
            Console.Write(message);
            Console.WriteLine();
            Console.ResetColor();
        }
    }

smooth
True love

Cached Result
Last Run:	10:31:04 pm
Compile:	0.006s
Execute:	0.05s
Memory:	2.62Mb
CPU:	0.056s

View IL Code