/* ====================================================================
   Licensed to the Apache Software Foundation (ASF) Under one or more
   contributor license agreements.  See the NOTICE file distributed with
   this work for Additional information regarding copyright ownership.
   The ASF licenses this file to You Under the Apache License, Version 2.0
   (the "License"); you may not use this file except in compliance with
   the License.  You may obtain a copy of the License at

       http://www.apache.org/licenses/LICENSE-2.0

   Unless required by applicable law or agreed to in writing, software
   distributed Under the License is distributed on an "AS Is" BASIS,
   WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
   See the License for the specific language governing permissions and
   limitations Under the License.
==================================================================== */



namespace HH.WMS.Utils.NPOI.SS.UserModel
{
    using System.Globalization;
    using System;
    using System.Text.RegularExpressions;
    using System.Text;

    /// <summary>
    /// Contains methods for dealing with Excel dates.
    /// @author  Michael Harhen
    /// @author  Glen Stampoultzis (glens at apache.org)
    /// @author  Dan Sherman (dsherman at Isisph.com)
    /// @author  Hack Kampbjorn (hak at 2mba.dk)
    /// @author  Alex Jacoby (ajacoby at gmail.com)
    /// @author  Pavel Krupets (pkrupets at palmtreebusiness dot com)
    /// </summary>
    public class DateUtil
    {
        private static int SECONDS_PER_MINUTE = 60;
        private static int MINUTES_PER_HOUR = 60;
        private static int HOURS_PER_DAY = 24;
        private static int SECONDS_PER_DAY = (HOURS_PER_DAY * MINUTES_PER_HOUR * SECONDS_PER_MINUTE);

        private static int BAD_DATE =
            -1;   // used to specify that date Is invalid
        private static long DAY_MILLISECONDS = 24 * 60 * 60 * 1000;
        private static char[] TIME_SEPARATOR_PATTERN = new char[] { ':' };

        /// <summary>
        /// Given a Calendar, return the number of days since 1899/12/31.
        /// </summary>
        /// <param name="cal">the date</param>
        /// <param name="use1904windowing">if set to <c>true</c> [use1904windowing].</param>
        /// <returns>number of days since 1899/12/31</returns>
        public static int AbsoluteDay(DateTime cal, bool use1904windowing)
        {
            int daynum = (cal - new DateTime(1899, 12, 31)).Days;
            if (cal > new DateTime(1900, 3, 1) && use1904windowing)
            {
                daynum++;
            }
            return daynum;
        }
        /// <summary>
        /// Given a Date, Converts it into a double representing its internal Excel representation,
        /// which Is the number of days since 1/1/1900. Fractional days represent hours, minutes, and seconds.
        /// </summary>
        /// <param name="date">Excel representation of Date (-1 if error - test for error by Checking for less than 0.1)</param>
        /// <returns>the Date</returns>
        public static double GetExcelDate(DateTime date)
        {
            return GetExcelDate(date, false);
        }
        /// <summary>
        /// Gets the excel date.
        /// </summary>
        /// <param name="year">The year.</param>
        /// <param name="month">The month.</param>
        /// <param name="day">The day.</param>
        /// <param name="hour">The hour.</param>
        /// <param name="minute">The minute.</param>
        /// <param name="second">The second.</param>
        /// <param name="use1904windowing">Should 1900 or 1904 date windowing be used?</param>
        /// <returns></returns>
        public static double GetExcelDate(int year, int month, int day, int hour, int minute, int second, bool use1904windowing)
        {
            if ((!use1904windowing && year < 1900)  //1900 date system must bigger than 1900
                || (use1904windowing && year < 1904))   //1904 date system must bigger than 1904
            {
                return BAD_DATE;
            }

            DateTime startdate;
            if (use1904windowing)
            {
                startdate = new DateTime(1904, 1, 1);
            }
            else
            {
                startdate = new DateTime(1900, 1, 1);
            }
            int nextyearmonth = 0;
            if (month > 12)
            {
                nextyearmonth = month - 12;
                month = 12;
            }
            int nextmonthday = 0;

            if ((month == 1 || month == 3 || month == 5 || month == 7 || month == 8 || month == 10 || month == 12))
            {
                //big month
                if (day > 31)
                {
                    nextmonthday = day - 31;
                    day = 31;
                }
            }
            else if ((month == 4 || month == 6 || month == 9 || month == 11))
            {
                //small month
                if (day > 30)
                {
                    nextmonthday = day - 30;
                    day = 30;
                }
            }
            else if (DateTime.IsLeapYear(year))
            {
                //Feb. with leap year
                if (day > 29)
                {
                    nextmonthday = day - 29;
                    day = 29;
                }
            }
            else
            {
                //Feb without leap year
                if (day > 28)
                {
                    nextmonthday = day - 28;
                    day = 28;
                }
            }

            if(day<=0)
            {
                nextmonthday = day - 1;
                day = 1;
            }
            
            DateTime date = new DateTime(year, month, day, hour, minute, second);
            date = date.AddMonths(nextyearmonth);
            date = date.AddDays(nextmonthday);
            double value = (date - startdate).TotalDays + 1;

            if (!use1904windowing && value >= 60)
            {
                value++;
            }
            else if (use1904windowing)
            {
                value--;
            }
            return value;
        }
        /// <summary>
        /// Given a Date, Converts it into a double representing its internal Excel representation,
        /// which Is the number of days since 1/1/1900. Fractional days represent hours, minutes, and seconds.
        /// </summary>
        /// <param name="date">The date.</param>
        /// <param name="use1904windowing">Should 1900 or 1904 date windowing be used?</param>
        /// <returns>Excel representation of Date (-1 if error - test for error by Checking for less than 0.1)</returns>
        public static double GetExcelDate(DateTime date, bool use1904windowing)
        {
            if ((!use1904windowing && date.Year < 1900)  //1900 date system must bigger than 1900
                || (use1904windowing && date.Year < 1904))   //1904 date system must bigger than 1904
            {
                return BAD_DATE;
            }

            DateTime startdate;
            if (use1904windowing)
            {
                startdate = new DateTime(1904, 1, 1);
            }
            else
            {
                startdate = new DateTime(1900, 1, 1);
            }

            double value = (date - startdate).TotalDays + 1;

            if (!use1904windowing && value >= 60)
            {
                value++;
            }
            else if (use1904windowing)
            {
                value--;
            }
            return value;
        }

        /// <summary>
        /// Given an Excel date with using 1900 date windowing, and
        /// Converts it to a Date.
        /// </summary>
        /// <param name="date">The Excel date.</param>
        /// <returns>Java representation of the date, or null if date Is not a valid Excel date</returns>
        /// <remarks>
        /// NOTE: If the default 
        /// <c>TimeZone</c>
        ///  in Java uses Daylight
        /// Saving Time then the conversion back to an Excel date may not give
        /// the same value, that Is the comparison
        /// <CODE>excelDate == GetExcelDate(GetJavaDate(excelDate,false))</CODE>
        /// Is not always true. For example if default timezone Is
        /// <c>Europe/Copenhagen</c>
        /// , on 2004-03-28 the minute after
        /// 01:59 CET Is 03:00 CEST, if the excel date represents a time between
        /// 02:00 and 03:00 then it Is Converted to past 03:00 summer time
        /// </remarks>        
        public static DateTime GetJavaDate(double date)
        {
            return GetJavaDate(date, false);
        }
        /**
         *  Given an Excel date with either 1900 or 1904 date windowing,
         *  Converts it to a Date.
         *
         *  NOTE: If the default <c>TimeZone</c> in Java uses Daylight
         *  Saving Time then the conversion back to an Excel date may not give
         *  the same value, that Is the comparison
         *  <CODE>excelDate == GetExcelDate(GetJavaDate(excelDate,false))</CODE>
         *  Is not always true. For example if default timezone Is
         *  <c>Europe/Copenhagen</c>, on 2004-03-28 the minute after
         *  01:59 CET Is 03:00 CEST, if the excel date represents a time between
         *  02:00 and 03:00 then it Is Converted to past 03:00 summer time
         *
         *  @param date  The Excel date.
         *  @param use1904windowing  true if date uses 1904 windowing,
         *   or false if using 1900 date windowing.
         *  @return Java representation of the date, or null if date Is not a valid Excel date
         *  @see TimeZone
         */
        public static DateTime GetJavaDate(double date, bool use1904windowing)
        {
            if (!IsValidExcelDate(date))
            {
                throw new ArgumentException(string.Format(CultureInfo.CurrentCulture, "Invalid Excel date double value: {0}", date));
            }
            int startYear = 1900;
            int dayAdjust = -1; // Excel thinks 2/29/1900 Is a valid date, which it Isn't
            int wholeDays = (int)Math.Floor(date);
            if (use1904windowing)
            {
                startYear = 1904;
                dayAdjust = 1; // 1904 date windowing uses 1/2/1904 as the first day
            }
            else if (wholeDays < 61)
            {
                // Date Is prior to 3/1/1900, so adjust because Excel thinks 2/29/1900 exists
                // If Excel date == 2/29/1900, will become 3/1/1900 in Java representation
                dayAdjust = 0;
            }
            DateTime startdate = new DateTime(startYear, 1, 1);
            startdate = startdate.AddDays(wholeDays + dayAdjust - 1);
            double millisecondsInDay = (int)((date - wholeDays) *
                                          DAY_MILLISECONDS + 0.5);
            return startdate.AddMilliseconds(millisecondsInDay);
        }
        /// <summary>
        /// Converts a string of format "HH:MM" or "HH:MM:SS" to its (Excel) numeric equivalent
        /// </summary>
        /// <param name="timeStr">The time STR.</param>
        /// <returns> a double between 0 and 1 representing the fraction of the day</returns>
        public static double ConvertTime(String timeStr)
        {
            try
            {
                return ConvertTimeInternal(timeStr);
            }
            catch (FormatException e)
            {
                String msg = "Bad time format '" + timeStr
                    + "' expected 'HH:MM' or 'HH:MM:SS' - " + e.Message;
                throw new ArgumentException(msg);
            }
        }
        /// <summary>
        /// Converts the time internal.
        /// </summary>
        /// <param name="timeStr">The time STR.</param>
        /// <returns></returns>
        private static double ConvertTimeInternal(String timeStr)
        {
            int len = timeStr.Length;
            if (len < 4 || len > 8)
            {
                throw new FormatException("Bad length");
            }
            String[] parts = timeStr.Split(TIME_SEPARATOR_PATTERN);

            String secStr;
            switch (parts.Length)
            {
                case 2: secStr = "00"; break;
                case 3: secStr = parts[2]; break;
                default:
                    throw new FormatException("Expected 2 or 3 fields but got (" + parts.Length + ")");
            }
            String hourStr = parts[0];
            String minStr = parts[1];
            int hours = ParseInt(hourStr, "hour", HOURS_PER_DAY);
            int minutes = ParseInt(minStr, "minute", MINUTES_PER_HOUR);
            int seconds = ParseInt(secStr, "second", SECONDS_PER_MINUTE);

            double totalSeconds = seconds + (minutes + (hours) * 60) * 60;
            return totalSeconds / (SECONDS_PER_DAY);
        }
        /// <summary>
        /// Given a format ID and its format String, will Check to see if the
        /// format represents a date format or not.
        /// Firstly, it will Check to see if the format ID corresponds to an
        /// internal excel date format (eg most US date formats)
        /// If not, it will Check to see if the format string only Contains
        /// date formatting Chars (ymd-/), which covers most
        /// non US date formats.
        /// </summary>
        /// <param name="formatIndex">The index of the format, eg from ExtendedFormatRecord.GetFormatIndex</param>
        /// <param name="formatString">The format string, eg from FormatRecord.GetFormatString</param>
        /// <returns>
        /// 	<c>true</c> if [is A date format] [the specified format index]; otherwise, <c>false</c>.
        /// </returns>
        public static bool IsADateFormat(int formatIndex, String formatString)
        {
            // First up, Is this an internal date format?
            if (IsInternalDateFormat(formatIndex))
            {
                return true;
            }

            // If we didn't Get a real string, it can't be
            if (formatString == null || formatString.Length == 0)
            {
                return false;
            }

            String fs = formatString;

            // Translate \- into just -, before matching
            fs = Regex.Replace(fs, "\\\\-", "-");
            // And \, into ,
            fs = Regex.Replace(fs, "\\\\,", ",");
            // And \. into .
            fs = Regex.Replace(fs,"\\\\.", ".");
            // And '\ ' into ' '
            fs = Regex.Replace(fs, "\\\\ ", " ");

            // If it end in ;@, that's some crazy dd/mm vs mm/dd
            //  switching stuff, which we can ignore
            fs = Regex.Replace(fs, ";@", "");
            StringBuilder sb = new StringBuilder(fs.Length);
            for (int i = 0; i < fs.Length; i++)
            {
                char c = fs[i];
                if (i < fs.Length - 1)
                {
                    char nc = fs[i + 1];
                    if (c == '\\')
                    {
                        switch (nc)
                        {
                            case '-':
                            case ',':
                            case '.':
                            case ' ':
                            case '\\':
                                // skip current '\' and continue to the next char
                                continue;
                        }
                    }
                    else if (c == ';' && nc == '@')
                    {
                        i++;
                        // skip ";@" duplets
                        continue;
                    }
                }
                sb.Append(c);
            }
            fs = sb.ToString();


            // short-circuit if it indicates elapsed time: [h], [m] or [s]
            if (Regex.IsMatch(fs, "^\\[([hH]+|[mM]+|[sS]+)\\]"))
            {
                return true;
            }

            // If it starts with [$-...], then could be a date, but
            //  who knows what that starting bit Is all about
            fs = Regex.Replace(fs, "^\\[\\$\\-.*?\\]", "");

            // If it starts with something like [Black] or [Yellow],
            //  then it could be a date
            fs = Regex.Replace(fs, "^\\[[a-zA-Z]+\\]", "");
            // You're allowed something like dd/mm/yy;[red]dd/mm/yy
            //  which would place dates before 1900/1904 in red
            // For now, only consider the first one
            if (fs.IndexOf(';') > 0 && fs.IndexOf(';') < fs.Length - 1)
            {
                fs = fs.Substring(0, fs.IndexOf(';'));
            }
            // Otherwise, Check it's only made up, in any case, of:
            //  y m d h s - / , . :
            // optionally followed by AM/PM
            if (Regex.IsMatch(fs, @"^[\[\]yYmMdDhHsS\-/,. :\""\\]+0*[ampAMP/]*$"))
            {
                return true;
            }

            return false;
        }
        /// <summary>
        /// Converts a string of format "YYYY/MM/DD" to its (Excel) numeric equivalent
        /// </summary>
        /// <param name="dateStr">The date STR.</param>
        /// <returns>a double representing the (integer) number of days since the start of the Excel epoch</returns>
        public static DateTime ParseYYYYMMDDDate(String dateStr)
        {
            try
            {
                return ParseYYYYMMDDDateInternal(dateStr);
            }
            catch (FormatException e)
            {
                String msg = "Bad time format " + dateStr
                    + " expected 'YYYY/MM/DD' - " + e.Message;
                throw new ArgumentException(msg);
            }
        }
        /// <summary>
        /// Parses the YYYYMMDD date internal.
        /// </summary>
        /// <param name="timeStr">The time string.</param>
        /// <returns></returns>
        private static DateTime ParseYYYYMMDDDateInternal(String timeStr)
        {
            if (timeStr.Length != 10)
            {
                throw new FormatException("Bad length");
            }

            String yearStr = timeStr.Substring(0, 4);
            String monthStr = timeStr.Substring(5, 2);
            String dayStr = timeStr.Substring(8, 2);
            int year = ParseInt(yearStr, "year", short.MinValue, short.MaxValue);
            int month = ParseInt(monthStr, "month", 1, 12);
            int day = ParseInt(dayStr, "day", 1, 31);

            DateTime cal = new DateTime(year, month, day, 0, 0, 0);
            return cal;
        }
        /// <summary>
        /// Parses the int.
        /// </summary>
        /// <param name="strVal">The string value.</param>
        /// <param name="fieldName">Name of the field.</param>
        /// <param name="rangeMax">The range max.</param>
        /// <returns></returns>
        private static int ParseInt(String strVal, String fieldName, int rangeMax)
        {
            return ParseInt(strVal, fieldName, 0, rangeMax - 1);
        }
        /// <summary>
        /// Parses the int.
        /// </summary>
        /// <param name="strVal">The STR val.</param>
        /// <param name="fieldName">Name of the field.</param>
        /// <param name="lowerLimit">The lower limit.</param>
        /// <param name="upperLimit">The upper limit.</param>
        /// <returns></returns>
        private static int ParseInt(String strVal, String fieldName, int lowerLimit, int upperLimit)
        {
            int result;
            try
            {
                result = int.Parse(strVal, CultureInfo.InvariantCulture);
            }
            catch (FormatException)
            {
                throw new FormatException("Bad int format '" + strVal + "' for " + fieldName + " field");
            }
            if (result < lowerLimit || result > upperLimit)
            {
                throw new FormatException(fieldName + " value (" + result
                        + ") is outside the allowable range(0.." + upperLimit + ")");
            }
            return result;
        }
        /// <summary>
        /// Given a format ID this will Check whether the format represents an internal excel date format or not.
        /// </summary>
        /// <param name="format">The format.</param>
        public static bool IsInternalDateFormat(int format)
        {
            bool retval = false;

            switch (format)
            {
                // Internal Date Formats as described on page 427 in
                // Microsoft Excel Dev's Kit...
                case 0x0e:
                case 0x0f:
                case 0x10:
                case 0x11:
                case 0x12:
                case 0x13:
                case 0x14:
                case 0x15:
                case 0x16:
                case 0x2d:
                case 0x2e:
                case 0x2f:
                    retval = true;
                    break;

                default:
                    retval = false;
                    break;
            }
            return retval;
        }

        /// <summary>
        /// Check if a cell Contains a date
        /// Since dates are stored internally in Excel as double values
        /// we infer it Is a date if it Is formatted as such.
        /// </summary>
        /// <param name="cell">The cell.</param>
        public static bool IsCellDateFormatted(ICell cell)
        {
            if (cell == null) return false;
            bool bDate = false;

            double d = cell.NumericCellValue;
            if (DateUtil.IsValidExcelDate(d))
            {
                ICellStyle style = cell.CellStyle;
                if (style == null)
                    return false;
                int i = style.DataFormat;
                String f = style.GetDataFormatString();
                bDate = IsADateFormat(i, f);
            }
            return bDate;
        }
        /// <summary>
        /// Check if a cell contains a date, Checking only for internal excel date formats.
        /// As Excel stores a great many of its dates in "non-internal" date formats, you will not normally want to use this method.
        /// </summary>
        /// <param name="cell">The cell.</param>
        public static bool IsCellInternalDateFormatted(ICell cell)
        {
            if (cell == null) return false;
            bool bDate = false;

            double d = cell.NumericCellValue;
            if (DateUtil.IsValidExcelDate(d))
            {
                ICellStyle style = cell.CellStyle;
                int i = style.DataFormat;
                bDate = IsInternalDateFormat(i);
            }
            return bDate;
        }


        /// <summary>
        /// Given a double, Checks if it Is a valid Excel date.
        /// </summary>
        /// <param name="value">the double value.</param>
        /// <returns>
        /// 	<c>true</c> if [is valid excel date] [the specified value]; otherwise, <c>false</c>.
        /// </returns>
        public static bool IsValidExcelDate(double value)
        {
            //return true;
            return value > -Double.Epsilon;
        }

    }
}