using System;
using System.Collections;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;

namespace MySQLCLR
{
    public partial class Utils
    {
  
        #region FormatString
        //http://fotiasqlclr.codeplex.com/SourceControl/changeset/view/99885#830436
        //http://www.fotia.co.uk/Blog/2009/05/indispensable-sql-clr-functions.html 
        //
        [SqlFunction(DataAccess = DataAccessKind.None, IsDeterministic = true)]
        public static SqlString Format1(SqlString format, object arg0)
        {
            return format.IsNull ? SqlString.Null : string.Format(format.Value, SqlToNetType(arg0));
        }

        [SqlFunction(DataAccess = DataAccessKind.None, IsDeterministic = true)]
        public static SqlString Format2(SqlString format, object arg0, object arg1)
        {
            return format.IsNull ? SqlString.Null : string.Format(format.Value, SqlToNetType(arg0), SqlToNetType(arg1));
        }

        [SqlFunction(DataAccess = DataAccessKind.None, IsDeterministic = true)]
        public static SqlString Format3(SqlString format, object arg0, object arg1, object arg2)
        {
            return format.IsNull ? SqlString.Null : string.Format(format.Value, SqlToNetType(arg0), SqlToNetType(arg1), SqlToNetType(arg2));
        }

        [SqlFunction(DataAccess = DataAccessKind.None, IsDeterministic = true)]
        public static SqlString Format4(SqlString format, object arg0, object arg1, object arg2, object arg3)
        {
            return format.IsNull ? SqlString.Null : string.Format(format.Value, SqlToNetType(arg0), SqlToNetType(arg1), SqlToNetType(arg2), SqlToNetType(arg3));
        }
         
        private static object SqlToNetType(object sqlValue)
        {
            var nullabe = sqlValue as INullable;
            if ((nullabe == null) || (nullabe.IsNull))
                return null;

            if (sqlValue is SqlString)
                return ((SqlString)sqlValue).Value;
            if (sqlValue is SqlInt16)
                return ((SqlInt16)sqlValue).Value;
            if (sqlValue is SqlInt32)
                return ((SqlInt32)sqlValue).Value;
            if (sqlValue is SqlInt64)
                return ((SqlInt64)sqlValue).Value;
            if (sqlValue is SqlSingle)
                return ((SqlSingle)sqlValue).Value;
            if (sqlValue is SqlDouble)
                return ((SqlDouble)sqlValue).Value;
            if (sqlValue is SqlDateTime)
                return ((SqlDateTime)sqlValue).Value;
            if (sqlValue is SqlMoney)
                return ((SqlMoney)sqlValue).Value;
            if (sqlValue is SqlDecimal)
                return ((SqlDecimal)sqlValue).Value;
            if (sqlValue is SqlBoolean)
                return ((SqlBoolean)sqlValue).Value;
            if (sqlValue is SqlByte)
                return ((SqlByte)sqlValue).Value;
            if (sqlValue is SqlGuid)
                return ((SqlGuid)sqlValue).Value;
            if (sqlValue is SqlXml)
                return ((SqlXml)sqlValue).Value;
            return sqlValue;
        }
        #endregion

        #region Datetime
        [SqlFunction(DataAccess = DataAccessKind.None, IsDeterministic = true)]
        public static SqlDateTime RoundDateTime(SqlDateTime Value, SqlString IntervalType, SqlInt32 IntervalValue)
        {
            if (Value.IsNull || IntervalType.IsNull || IntervalType.IsNull)
                return new SqlDateTime();

            String ITyp  = IntervalType.Value.ToUpper();
            DateTime fdy; //first day of year            
            DateTime curDay;
            Int32 diffDays1;
            Int32 diffDays2;

            switch (ITyp)
            {
                case "SE":
                    return new DateTime(Value.Value.Year,  Value.Value.Month, Value.Value.Day, Value.Value.Hour, Value.Value.Minute, Value.Value.Second / IntervalValue.Value * IntervalValue.Value );
                case "MI":
                    return new DateTime(Value.Value.Year, Value.Value.Month, Value.Value.Day, Value.Value.Hour, Value.Value.Minute / IntervalValue.Value * IntervalValue.Value, 0);
                case "HO":
                    return new DateTime(Value.Value.Year, Value.Value.Month, Value.Value.Day, Value.Value.Hour / IntervalValue.Value * IntervalValue.Value, 0, 0);
               
                case "DA":
                case "DA1":
                    fdy = new DateTime(Value.Value.Year, 1, 1);
                    curDay = new DateTime(Value.Value.Year, Value.Value.Month, Value.Value.Day);
                    diffDays1 = (Int32)curDay.Subtract(fdy).TotalDays;
                    diffDays2 = diffDays1 /  IntervalValue.Value * IntervalValue.Value;
                    return fdy.AddDays(diffDays2);

                case "DA2":                   
                    fdy = new DateTime(Value.Value.Year, 1, 1);
                    DateTime mo1wo;
                    if (fdy.DayOfWeek==DayOfWeek.Sunday)
                        mo1wo=fdy.AddDays(-6);
                    else
                       mo1wo = fdy.AddDays(-1*((int)fdy.DayOfWeek - 1));
 
                    curDay = new DateTime(Value.Value.Year, Value.Value.Month, Value.Value.Day);
                    diffDays1 = (Int32)curDay.Subtract(mo1wo).TotalDays;
                    diffDays2 = diffDays1 / IntervalValue.Value * IntervalValue.Value;
                    return mo1wo.AddDays(diffDays2);          
                              
                case "MO":
                    return new DateTime(Value.Value.Year, (Value.Value.Month-1) / IntervalValue.Value * IntervalValue.Value+1, 1, 0, 0, 0);
                case "YE":
                    return new DateTime(Value.Value.Year / IntervalValue.Value * IntervalValue.Value, 1, 1, 0, 0, 0);
              
                default:
                    return new SqlDateTime();
                   
            }                      
        }

        #endregion

        [SqlFunction(Name = "fn_Num", DataAccess = DataAccessKind.None, FillRowMethodName = "GetTableRowFnNum", IsDeterministic = true)]
        public static IEnumerable fn_Num(SqlInt64 ValueFrom, SqlInt64 ValueTo)
        {
            List<Int64> ret = new List<Int64>();
            if ( !ValueFrom.IsNull && !ValueTo.IsNull )
            {
             Int64 i;
             for (i = ValueFrom.Value; i <= ValueTo.Value ; i++) ret.Add(i); 
            }
                      
            return ret;
        }

        public static void GetTableRowFnNum(object o, out Int64 Value)
        {
            Value = (Int64)o;            
        }

    }
      
}

CREATE ASSEMBLY SqlUtils1
FROM '......\SqlUtils1.dll' 
WITH PERMISSION_SET = SAFE
GO

CREATE FUNCTION fn_num(@from bigint, @to bigint)
RETURNS TABLE(N bigint)
AS EXTERNAL NAME SqlUtils1.[MySQLCLR.Utils].fn_Num;
GO  

CREATE FUNCTION fn_Format1(@format nvarchar(4000), @arg0 sql_variant) RETURNS NVARCHAR(4000)
AS EXTERNAL NAME SqlUtils1.[MySQLCLR.Utils].Format1
go
CREATE FUNCTION fn_Format2(@format nvarchar(4000), @arg0 sql_variant, @arg1 sql_variant) RETURNS NVARCHAR(4000)
AS EXTERNAL NAME SqlUtils1.[MySQLCLR.Utils].Format2
go
CREATE FUNCTION fn_Format3(@format nvarchar(4000), @arg0 sql_variant, @arg1 sql_variant, @arg2 sql_variant) RETURNS NVARCHAR(4000)
AS EXTERNAL NAME SqlUtils1.[MySQLCLR.Utils].Format3
go
CREATE FUNCTION fn_Format4(@format nvarchar(4000), @arg0 sql_variant, @arg1 sql_variant, @arg2 sql_variant, @arg3 sql_variant) RETURNS NVARCHAR(4000)
AS EXTERNAL NAME SqlUtils1.[MySQLCLR.Utils].Format4
go

CREATE FUNCTION fn_RoundDateTime(@DateTime datetime, @IntervalType nvarchar(3), @IntervalValue int) RETURNS DATETIME
AS EXTERNAL NAME SqlUtils1.[MySQLCLR.Utils].RoundDateTime
go