Scrum for Trello data extractor

ScrumForTrelloDataExtractor.cs

/// <summary>
/// This script will connect to the Trello API and the Google Spreadsheet in use by Scrum For Trello
/// It will output current sprint data, estimates vs remaining
///    burndown chart for development board
///     https://trello.com/1/members/me/boards?fields=name;
/// </summary>

/// INSTRUCTIONS - (after pasting in the google oauth token & refresh token)
/// update trello
/// 1. ENSURE THE TRELLO BOARD HAS SYNCED TO THE SPREADSHEET
/// 2. run
/// 3. on the trello board - clear items from the DONE DONE column
public bool IsSlackPostEnabled = false;

public string GoogleOAuthAccessToken = "     "; // first exception paste token here from google (after accepting)
public string GoogleRefreshToken = " 1AGoCpnOMMOYJ3Er8hVKyICAfyNhOBcb1xe7u_u-to4"; // after running it again, in the output console copy the value OAuth Access Token

public const string TrelloToken = "1c323aa8792d493f6b3a9be910f29a1df7362c76ae50d95480346597ab99f683"; // every 30 days trello oauth token expires
public const string GoogleSpreadSheetUrl = @"https://docs.google.com/spreadsheets/d/1k95abhxhnKA_VAtMqczz1OxH0hyTpeBBK4Z3e-pB6cY/edit#gid=0";

public IList<string> ExcludeLists = new List<string>() { "Backlog", "V2", "Tech-Debt", "Archived" };

public string BoardId = "5982c43f60768e5dd1fa6a8c"; //55a8cdfd9536d1d4a332691f

const string DoneDone = "Done Done";

// from trello directly
public static Trello Trello { get; set; }
public static Board Board { get; set; }
public static IEnumerable<TrelloNet.Label> Labels { get; set; }
public static IEnumerable<TrelloNet.List> TrelloLists = new List<TrelloNet.List>();
public static IList<TrelloNet.Card> Cards = new List<TrelloNet.Card>();

// google spreadsheet (synced via plus for trello)
public IEnumerable<SpreadSheetItem> Spreadsheet { get; set; }
public IEnumerable<Card> SprintCards { get; set; }
public IEnumerable<Card> DoneCards { get; set; }

public decimal Inaccuracy = 0;
public decimal Velocity = 0;
public decimal Remaining = 0;

void Main()
{
	// extraction use google spreadsheet to get all card estimates
	Cards = ExtractTrello().ToList();
	Debug.Assert(Cards.Any());
	Spreadsheet = GetGoogleSpreadsheet();

	var firstEstimates = Spreadsheet
		.Where(x => x.ListName == DoneDone)
		.Select(x => new { Card = x.Card, FirstEstimate = GetFirstEstimate(x.Card) })
		.Distinct()
		.Dump("First Estimates");

	Velocity = firstEstimates.Sum(x => x.FirstEstimate);

	Spreadsheet
	.Where(x => x.ListName != DoneDone)
	.Select(x => new { Card = x.Card, Remaining = GetRemainingPoints(x.Card) })
	.Distinct()
	.Dump("Remaining points per card");

	Remaining = Spreadsheet
	.Where(a => !ExcludeLists.Contains(a.ListName))
	.Where(x => x.ListName != DoneDone)
	.Select(x => new { Card = x.Card, Remaining = GetRemainingPoints(x.Card) })
	.Distinct()
	.Sum(x => x.Remaining);

//    var cardsPerList = Spreadsheet
//    .Where(a => !ExcludeLists.Contains(a.ListName))
//    .Where(x => x.ListName != DoneDone)
//    .Select(x => new { Card = x.Card, ListName = x.ListName });
//
//    var listCount = from p in cardsPerList
//                 group p by new { p.ListName, p.Card } into g
//                 select new { ListName = g.Key.ListName,  Total = g.Count() };
//    listCount.OrderByDescending(x => x.Total).Dump("Cards per list");
    
    Spreadsheet
        .Where(a => !ExcludeLists.Contains(a.ListName))
        .Where(x => x.ListName != DoneDone)
        .Select(x => new { Card = x.Card, Remaining = GetRemainingPoints(x.Card) })
        .Distinct()
        .Sum(x => x.Remaining);

    var estimateDifference = Spreadsheet
			.Where(a => !ExcludeLists.Contains(a.ListName))
			.Select(x => new { Card = x.Card, Difference = GetEstimateDifference(x.Card) })
			.OrderByDescending(x=>x.Difference)
			.Distinct()
			.Dump("Estimate Difference");
			
	//Inaccuracy	= firstEstimates.Sum(x=>x.FirstEstimate) / estimateDifference.Sum(x=>x.Difference);

	Breakdown();

	// stats
	Message.AppendLine($"sprint velocity {Velocity} points (total first estimates in done column)");
	//Message.AppendLine($"estimation inaccuracy   {Math.Abs(Inaccuracy).ToString("P2")} (change between first estimate and final estimate)");
	Message.AppendLine($"total cards {Spreadsheet.Where(x=>!ExcludeLists.Contains(x.ListName)).Select(y => y.Card).Distinct().Count()}");
	Message.AppendLine($"done cards   {Spreadsheet.Where(x => x.ListName == DoneDone).Select(y => y.Card).Distinct().Count()}");
	Message.AppendLine($"remaining points {Remaining}");
	PostToSlack("Sprint stats");

	Spreadsheet.Dump("Spreadsheet");

	SaveStatsToFile();
	SaveToDisk(Spreadsheet.ToList());

}

#region Methods

private void Breakdown()
{
	var kvpAllCardsList = new List<KeyValuePair<string, decimal>>();

	foreach (var card in Cards)
	{
		foreach (var l in card.Labels)
		{
			var kvp = new KeyValuePair<string, decimal>(l.Name, GetRemainingPoints(card.Name));
			kvpAllCardsList.Add(kvp);
		}
	}

	kvpAllCardsList
	.GroupBy(x => x.Key)
	.Select(x => new { Name = x.Key, Total = x.Count() })
	.OrderBy(x => x.Name)
	.ThenByDescending(x => x.Total)
	.Dump("Total Label counts");

	kvpAllCardsList
	.GroupBy(x => x.Key)
	.Select(x => new { Name = x.Key, Total = x.Sum(y => y.Value) })
	.OrderByDescending(x => x.Total)
	.Dump("Total Label summed");


	var inclusive = Spreadsheet.Where(x => x.ListName != DoneDone);
	
	var kvpRemainingCardsList = new List<KeyValuePair<string, decimal>>();

	foreach (var card in Cards.Where(x=>inclusive.Select(y=>y.Card).Contains(x.Name)))
	{
		foreach (var l in card.Labels)
		{
			var kvp = new KeyValuePair<string, decimal>(l.Name, GetRemainingPoints(card.Name));
			kvpRemainingCardsList.Add(kvp);
		}
	}

	kvpRemainingCardsList
	.GroupBy(x => x.Key)
	.Select(x => new { Name = x.Key, Total = x.Count() })
	.OrderBy(x => x.Name)
	.ThenByDescending(x => x.Total)
	.Dump("Remaining Label counts");

	kvpRemainingCardsList
	.GroupBy(x => x.Key)
	.Select(x => new { Name = x.Key, Total = x.Sum(y => y.Value) })
	.OrderByDescending(x => x.Total)
	.Dump("Remaining Label summed");
}

private IEnumerable<Card> ExtractTrello(bool filterColumns = true)
{
	Trello = new Trello("7b17eb1ed849a91c051da9c924f93cfb");
	var url = Trello.GetAuthorizationUrl("userstorydataloader", Scope.ReadWrite);
	var cards = new List<Card>();
	try
	{
		string token = TrelloToken;
		Trello.Authorize(token);
		Board = Trello.Boards.WithId(BoardId);

		TrelloLists = Trello.Lists.ForBoard(Board);
		foreach (var list in TrelloLists)
		{
			cards.AddRange(Trello.Cards.ForList(list, CardFilter.Open));
		}
	}
	catch
	{
		Console.WriteLine("Trello token expired");
		Process.Start(url.AbsoluteUri);  // paste token below
		throw;
	}

	return cards;
}

private IList<SpreadSheetItem> GetGoogleSpreadsheet()
{
	var list = new List<SpreadSheetItem>();

	////////////////////////////////////////////////////////////////////////////
	// STEP 1: Configure how to perform OAuth 2.0
	////////////////////////////////////////////////////////////////////////////

	// TODO: Update the following information with that obtained from
	// https://code.google.com/apis/console. After registering
	// your application, these will be provided for you.

	string CLIENT_ID = "677552916440-b3v9e9kcoook1luvrsequet2r29rt5fn.apps.googleusercontent.com";

	// This is the OAuth 2.0 Client Secret retrieved
	// above.  Be sure to store this value securely.  Leaking this
	// value would enable others to act on behalf of your application!
	string CLIENT_SECRET = "BgshShGv9lSzE2ZRY0PRka-1";

	// Space separated list of scopes for which to request access.
	string SCOPE = "https://spreadsheets.google.com/feeds https://docs.google.com/feeds";

	// This is the Redirect URI for installed applications.
	// If you are building a web application, you have to set your
	// Redirect URI at https://code.google.com/apis/console.
	string REDIRECT_URI = "urn:ietf:wg:oauth:2.0:oob";

	////////////////////////////////////////////////////////////////////////////
	// STEP 2: Set up the OAuth 2.0 object
	////////////////////////////////////////////////////////////////////////////

	// OAuth2Parameters holds all the parameters related to OAuth 2.0.
	OAuth2Parameters parameters = new OAuth2Parameters();

	// Set your OAuth 2.0 Client Id (which you can register at
	// https://code.google.com/apis/console).
	parameters.ClientId = CLIENT_ID;

	// Set your OAuth 2.0 Client Secret, which can be obtained at
	// https://code.google.com/apis/console.
	parameters.ClientSecret = CLIENT_SECRET;

	// Set your Redirect URI, which can be registered at
	// https://code.google.com/apis/console.
	parameters.RedirectUri = REDIRECT_URI;

	////////////////////////////////////////////////////////////////////////////
	// STEP 3: Get the Authorization URL
	////////////////////////////////////////////////////////////////////////////

	// Set the scope for this particular service.
	parameters.Scope = SCOPE;

	string authorizationUrl = OAuthUtil.CreateOAuth2AuthorizationUrl(parameters);

	/// step 1 - paste access token here (if the token has expired)
	parameters.AccessCode = GoogleOAuthAccessToken;

	/// step 2 - paste refresh token here
	parameters.RefreshToken = GoogleRefreshToken;
	///////////////////////////////////////////////////////////////////////////
	// STEP 4: Get the Access Token
	////////////////////////////////////////////////////////////////////////////

	// Once the user authorizes with Google, the request token can be exchanged
	// for a long-lived access token.  If you are building a browser-based
	// application, you should parse the incoming request token from the url and
	// set it in OAuthParameters before calling GetAccessToken().

	try
	{
		OAuthUtil.RefreshAccessToken(parameters);
	}
	catch (Exception)
	{
		Process.Start(authorizationUrl);
		OAuthUtil.GetAccessToken(parameters);
		string accessToken = parameters.AccessToken;
		Console.WriteLine("OAuth Access Token: " + accessToken);

	}


	////////////////////////////////////////////////////////////////////////////
	// STEP 5: Make an OAuth authorized request to Google
	////////////////////////////////////////////////////////////////////////////

	// Initialize the variables needed to make the request
	GOAuth2RequestFactory requestFactory = new GOAuth2RequestFactory(null, "emerald-griffin-130607", parameters);
	SpreadsheetsService service = new SpreadsheetsService("emerald-griffin-130607");
	service.RequestFactory = requestFactory;

	//  var service = AuthenticateGoogleSpreadsheets();

	// Instantiate a SpreadsheetQuery object to retrieve spreadsheets.
	SpreadsheetQuery query = new SpreadsheetQuery();

	// Make a request to the API and get all spreadsheets.
	SpreadsheetFeed feed = service.Query(query);
	var trelloSheet = (SpreadsheetEntry)feed.Entries.First(x => x.Title.Text == "Plus for Trello sync spreadsheet");

	// Make a request to the API to fetch information about all
	// worksheets in the spreadsheet.
	WorksheetFeed wsFeed = trelloSheet.Worksheets;
	var sheetDataList = new List<SheetData>();
	// Iterate through each worksheet in the spreadsheet.
	foreach (WorksheetEntry entry in wsFeed.Entries)
	{

		// Fetch the cell feed of the worksheet.
		CellQuery cq = new CellQuery(entry.CellFeedLink);
		CellFeed cf = service.Query(cq);

		// Iterate through each cell, updating its value if necessary.
		foreach (CellEntry cellEntry in cf.Entries)
		{
			// In this case, cellEntry.Value is null - and almost everything else in the object is null too.
			var sd = new SheetData();
			sd.CellEntry = cellEntry;
			sd.Column = cellEntry.Column;
			sd.Row = cellEntry.Row;
			if (!string.IsNullOrEmpty(cellEntry.Cell.Value))
				sheetDataList.Add(sd);
		}
	}

	uint maxRow = sheetDataList.Max(x => x.Row);
	uint maxColumn = sheetDataList.Max(x => x.Column);

	for (uint row = 2; row <= maxRow; row++) // first row is headers
	{
		var item = new SpreadSheetItem();
		var strDate = sheetDataList.SingleOrDefault(a => a.Row == row && a.Column == 1).CellEntry.Value;
		item.Order = GetOrder(strDate); // this includes an ID - parse it off for ordering
		item.Date = GetDate(strDate); // dito
		item.Board = Convert.ToString(sheetDataList.SingleOrDefault(a => a.Row == row && a.Column == 2).CellEntry.Value);
		item.Card = Convert.ToString(sheetDataList.SingleOrDefault(a => a.Row == row && a.Column == 3).CellEntry.Value);
		item.SpentHours = Convert.ToDecimal(sheetDataList.SingleOrDefault(a => a.Row == row && a.Column == 4).CellEntry.Value);
		item.EstimatedHours = Convert.ToDecimal(sheetDataList.SingleOrDefault(a => a.Row == row && a.Column == 5).CellEntry.Value);
		item.Who = Convert.ToString(sheetDataList.SingleOrDefault(a => a.Row == row && a.Column == 6).CellEntry.Value);
		item.Url = Convert.ToString(sheetDataList.SingleOrDefault(a => a.Row == row && a.Column == 7).CellEntry.Value);
		item.IdTrello = Convert.ToString(sheetDataList.SingleOrDefault(a => a.Row == row && a.Column == 11).CellEntry.Value);

		try
		{
			item.ListName = TrelloLists.FirstOrDefault(a => a.Id == Cards.FirstOrDefault(x => x.Name == item.Card).IdList).Name;
		}
		catch
		{
			item.ListName = "Archived";
		}

		item.Labels = Cards.FirstOrDefault(x => x.Name == item.Card)?.Labels.Select(x => x.Name);

		list.Add(item);
	}

	return list;
}

private decimal GetFirstEstimate(string cardName)
{
	var items = Spreadsheet.Where(a => a.Card == cardName).OrderBy(b => b.Date);
	decimal total = 0;
	foreach (var item in items.Where(x => x.SpentHours == 0).Take(1))
	{
		total += Math.Abs(item.EstimatedHours);
	}

    if (total == 0)
    {
        if (items.Any(x => x.EstimatedHours > 0))
        {
            total = items.Max(x => x.EstimatedHours);
        }
        else
        {
            total = items.Max(x => x.SpentHours);
        }
    }
	return total;
}

private decimal GetRemainingPoints(string cardName)
{
	var items = Spreadsheet.Where(a => a.Card == cardName).OrderBy(b => b.Date);
	decimal total = 0;
	foreach (var item in items.Where(x => x.EstimatedHours > 0).Take(1))
	{
		total += Math.Abs(item.EstimatedHours);
	}
	return total;
}

private decimal GetEstimateDifference(string cardName)
{
	var items = Spreadsheet.Where(a => a.Card == cardName).OrderBy(b => b.Date);
 	var max = items.Max(x=>x.EstimatedHours);
	var min = items.Min(x=>x.EstimatedHours);
	return max - min;
}

private string FindFolder()
{
	string folder = @"D:\work\Dropbox (FairGo 3.0)\development\SprintStats";
	if (!Directory.Exists(folder))
	{
		folder = @"d:\Dropbox (FairGo 3.0)\development\SprintStats";
	}

	if (!Directory.Exists(folder))
	{
		throw new DirectoryNotFoundException(folder);
	}

	return folder;
}

private void SaveStatsToFile()
{
    if (!IsSlackPostEnabled) return;
	string folder = FindFolder();
	string filename = "stats.log";
	string fullName = Path.Combine(folder, filename);

	var log = new StringBuilder();
	log.AppendLine("---------------------------------------------------------");
	log.AppendLine("Created: " + DateTime.Now.ToShortDateString());
	log.Append(Message.ToString());
	log.AppendLine("---------------------------------------------------------");
	log.AppendLine(" ");

	System.IO.File.AppendAllText(fullName, log.ToString());
}

private void SaveToDisk(IList<SpreadSheetItem> items)
{
	string folder = FindFolder();
	string filename = string.Concat("burndown_", DateTime.Now.ToString("yyyyMMdd"), ".csv");
	string fullName = Path.Combine(folder, filename);
	if (File.Exists(fullName)) File.Delete(fullName);
	Util.WriteCsv(items, fullName);
}

/// an order ID is appended to the data - 05/17/2016 12:35:91     91 is not seconds its an ID
private int GetOrder(string dateString)
{
	var trimmedId = dateString.Substring(17);
	return Int16.Parse(trimmedId);
}

/// an order ID is appended to the data - 05/17/2016 12:35:91     91 is not seconds
private DateTime GetDate(string dateString)
{
	var trimmedDateString = dateString.Substring(0, 16);
	DateTime dt = DateTime.Now;
	CultureInfo enUS = new CultureInfo("en-US");
	DateTimeRoutines.ParsedDateTime pdt;

	var formatStrings = new string[] { "MM/dd/yyyy hh:mm:ss tt", "yyyy-MM-dd hh:mm:ss", "" };
	if (DateTime.TryParseExact(trimmedDateString, formatStrings, enUS, DateTimeStyles.None, out dt))
	{
		//Console.WriteLine(".net datetime " + dt.ToString());
		return dt;
	}
	else if (DateTimeRoutines.TryParseDateTime(trimmedDateString, DateTimeRoutines.DateTimeFormat.USA_DATE, out pdt))
	{
		//Console.WriteLine("date time parsed as " + pdt.DateTime.ToString());
		return pdt.DateTime;
	}
	else
	{
		throw new InvalidDataException(dateString);
	}
}

#endregion

#region Slack poster 

public StringBuilder Message = new StringBuilder();
private void PostToSlack(string title)
{
	if (IsSlackPostEnabled)
	{
		string urlWithAccessToken = "https://hooks.slack.com/services/T06EV35NK/B0H260AE5/xxxxxxxxxxxx";
		SlackClient client = new SlackClient(urlWithAccessToken);
		var slackMessage = new SlackMessage();
		slackMessage.AsUser("itnotifications");
		Console.WriteLine(Message.ToString());
		string msg = Message.ToString();
		if (!string.IsNullOrEmpty(title) && !string.IsNullOrEmpty(msg))
		{
			slackMessage.Field(title, Message.ToString(), false);
			slackMessage.FallBack(title + "  " + Message.ToString());
			client.Send(slackMessage);
		}
	}
	else
	{
		Console.WriteLine();
		Console.WriteLine("--------------------------");
		Console.WriteLine(title);
		Console.WriteLine("--------------------------");
		Console.WriteLine();
		Console.WriteLine(Message.ToString());
		Console.WriteLine();
	}
}
#endregion

#region  Classes

public class SpreadSheetItem
{
	public DateTime Date { get; set; }

	public string Board { get; set; }

	public string Card { get; set; }

	public string ListName { get; set; }

	public IEnumerable<string> Labels { get; set; }

	public decimal SpentHours { get; set; }

	public decimal EstimatedHours { get; set; }

	public string Who { get; set; }

	public string Url { get; set; }

	public string IdTrello { get; set; }

	public int Order { get; set; }
}

public class SheetData
{
	public uint Column { get; set; }
	public uint Row { get; set; }
	public CellEntry CellEntry { get; set; }
}

#endregion

#region date time parser

/// <summary>
/// Miscellaneous and parsing methods for DateTime
/// </summary>
public static class DateTimeRoutines
{
	#region miscellaneous methods

	/// <summary>
	/// Amount of seconds elapsed between 1970-01-01 00:00:00 and the date-time.
	/// </summary>
	/// <param name="date_time">date-time</param>
	/// <returns>seconds</returns>
	public static uint GetSecondsSinceUnixEpoch(this DateTime date_time)
	{
		TimeSpan t = date_time - new DateTime(1970, 1, 1);
		int ss = (int)t.TotalSeconds;
		if (ss < 0)
			return 0;
		return (uint)ss;
	}

	#endregion

	#region parsing definitions

	/// <summary>
	/// Defines a substring where date-time was found and result of conversion
	/// </summary>
	public class ParsedDateTime
	{
		/// <summary>
		/// Index of first char of a date substring found in the string
		/// </summary>
		readonly public int IndexOfDate = -1;
		/// <summary>
		/// Length a date substring found in the string
		/// </summary>
		readonly public int LengthOfDate = -1;
		/// <summary>
		/// Index of first char of a time substring found in the string
		/// </summary>
		readonly public int IndexOfTime = -1;
		/// <summary>
		/// Length of a time substring found in the string
		/// </summary>
		readonly public int LengthOfTime = -1;
		/// <summary>
		/// DateTime found in the string
		/// </summary>
		readonly public DateTime DateTime;
		/// <summary>
		/// True if a date was found within the string
		/// </summary>
		readonly public bool IsDateFound;
		/// <summary>
		/// True if a time was found within the string
		/// </summary>
		readonly public bool IsTimeFound;
		/// <summary>
		/// UTC offset if it was found within the string
		/// </summary>
		readonly public TimeSpan UtcOffset;
		/// <summary>
		/// True if UTC offset was found in the string
		/// </summary>
		readonly public bool IsUtcOffsetFound;
		/// <summary>
		/// Utc gotten from DateTime if IsUtcOffsetFound is True
		/// </summary>
		public DateTime UtcDateTime;

		internal ParsedDateTime(int index_of_date, int length_of_date, int index_of_time, int length_of_time, DateTime date_time)
		{
			IndexOfDate = index_of_date;
			LengthOfDate = length_of_date;
			IndexOfTime = index_of_time;
			LengthOfTime = length_of_time;
			DateTime = date_time;
			IsDateFound = index_of_date > -1;
			IsTimeFound = index_of_time > -1;
			UtcOffset = new TimeSpan(25, 0, 0);
			IsUtcOffsetFound = false;
			UtcDateTime = new DateTime(1, 1, 1);
		}

		internal ParsedDateTime(int index_of_date, int length_of_date, int index_of_time, int length_of_time, DateTime date_time, TimeSpan utc_offset)
		{
			IndexOfDate = index_of_date;
			LengthOfDate = length_of_date;
			IndexOfTime = index_of_time;
			LengthOfTime = length_of_time;
			DateTime = date_time;
			IsDateFound = index_of_date > -1;
			IsTimeFound = index_of_time > -1;
			UtcOffset = utc_offset;
			IsUtcOffsetFound = Math.Abs(utc_offset.TotalHours) < 12;
			if (!IsUtcOffsetFound)
				UtcDateTime = new DateTime(1, 1, 1);
			else
			{
				if (index_of_date < 0)//to avoid negative date exception when date is undefined
				{
					TimeSpan ts = date_time.TimeOfDay + utc_offset;
					if (ts < new TimeSpan(0))
						UtcDateTime = new DateTime(1, 1, 2) + ts;
					else
						UtcDateTime = new DateTime(1, 1, 1) + ts;
				}
				else
					UtcDateTime = date_time + utc_offset;
			}
		}
	}

	/// <summary>
	/// Date that is accepted in the following cases:
	/// - no date was parsed by TryParseDateOrTime();
	/// - no year was found by TryParseDate();
	/// It is ignored if DefaultDateIsNow = true was set after DefaultDate 
	/// </summary>
	public static DateTime DefaultDate
	{
		set
		{
			_DefaultDate = value;
			DefaultDateIsNow = false;
		}
		get
		{
			if (DefaultDateIsNow)
				return DateTime.Now;
			else
				return _DefaultDate;
		}
	}
	static DateTime _DefaultDate = DateTime.Now;

	/// <summary>
	/// If true then DefaultDate property is ignored and DefaultDate is always DateTime.Now
	/// </summary>
	public static bool DefaultDateIsNow = true;

	/// <summary>
	/// Defines default date-time format.
	/// </summary>
	public enum DateTimeFormat
	{
		/// <summary>
		/// month number goes before day number
		/// </summary>
		USA_DATE,
		/// <summary>
		/// day number goes before month number
		/// </summary>
		UK_DATE,
		///// <summary>
		///// time is specifed through AM or PM
		///// </summary>
		//USA_TIME,
	}

	#endregion

	#region parsing derived methods for DateTime output

	/// <summary>
	/// Tries to find date and time within the passed string and return it as DateTime structure. 
	/// </summary>
	/// <param name="str">string that contains date and/or time</param>
	/// <param name="default_format">format to be used preferably in ambivalent instances</param>
	/// <param name="date_time">parsed date-time output</param>
	/// <returns>true if both date and time were found, else false</returns>
	static public bool TryParseDateTime(this string str, DateTimeFormat default_format, out DateTime date_time)
	{
		ParsedDateTime parsed_date_time;
		if (!TryParseDateTime(str, default_format, out parsed_date_time))
		{
			date_time = new DateTime(1, 1, 1);
			return false;
		}
		date_time = parsed_date_time.DateTime;
		return true;
	}

	/// <summary>
	/// Tries to find date and/or time within the passed string and return it as DateTime structure. 
	/// If only date was found, time in the returned DateTime is always 0:0:0.
	/// If only time was found, date in the returned DateTime is DefaultDate.
	/// </summary>
	/// <param name="str">string that contains date and(or) time</param>
	/// <param name="default_format">format to be used preferably in ambivalent instances</param>
	/// <param name="date_time">parsed date-time output</param>
	/// <returns>true if date and/or time was found, else false</returns>
	static public bool TryParseDateOrTime(this string str, DateTimeFormat default_format, out DateTime date_time)
	{
		ParsedDateTime parsed_date_time;
		if (!TryParseDateOrTime(str, default_format, out parsed_date_time))
		{
			date_time = new DateTime(1, 1, 1);
			return false;
		}
		date_time = parsed_date_time.DateTime;
		return true;
	}

	/// <summary>
	/// Tries to find time within the passed string and return it as DateTime structure. 
	/// It recognizes only time while ignoring date, so date in the returned DateTime is always 1/1/1.
	/// </summary>
	/// <param name="str">string that contains time</param>
	/// <param name="default_format">format to be used preferably in ambivalent instances</param>
	/// <param name="time">parsed time output</param>
	/// <returns>true if time was found, else false</returns>
	public static bool TryParseTime(this string str, DateTimeFormat default_format, out DateTime time)
	{
		ParsedDateTime parsed_time;
		if (!TryParseTime(str, default_format, out parsed_time, null))
		{
			time = new DateTime(1, 1, 1);
			return false;
		}
		time = parsed_time.DateTime;
		return true;
	}

	/// <summary>
	/// Tries to find date within the passed string and return it as DateTime structure. 
	/// It recognizes only date while ignoring time, so time in the returned DateTime is always 0:0:0.
	/// If year of the date was not found then it accepts the current year. 
	/// </summary>
	/// <param name="str">string that contains date</param>
	/// <param name="default_format">format to be used preferably in ambivalent instances</param>
	/// <param name="date">parsed date output</param>
	/// <returns>true if date was found, else false</returns>
	static public bool TryParseDate(this string str, DateTimeFormat default_format, out DateTime date)
	{
		ParsedDateTime parsed_date;
		if (!TryParseDate(str, default_format, out parsed_date))
		{
			date = new DateTime(1, 1, 1);
			return false;
		}
		date = parsed_date.DateTime;
		return true;
	}

	#endregion

	#region parsing derived methods for ParsedDateTime output

	/// <summary>
	/// Tries to find date and time within the passed string and return it as ParsedDateTime object. 
	/// </summary>
	/// <param name="str">string that contains date-time</param>
	/// <param name="default_format">format to be used preferably in ambivalent instances</param>
	/// <param name="parsed_date_time">parsed date-time output</param>
	/// <returns>true if both date and time were found, else false</returns>
	static public bool TryParseDateTime(this string str, DateTimeFormat default_format, out ParsedDateTime parsed_date_time)
	{
		if (DateTimeRoutines.TryParseDateOrTime(str, default_format, out parsed_date_time)
			&& parsed_date_time.IsDateFound
			&& parsed_date_time.IsTimeFound
			)
			return true;

		parsed_date_time = null;
		return false;
	}

	/// <summary>
	/// Tries to find time within the passed string and return it as ParsedDateTime object. 
	/// It recognizes only time while ignoring date, so date in the returned ParsedDateTime is always 1/1/1
	/// </summary>
	/// <param name="str">string that contains date-time</param>
	/// <param name="default_format">format to be used preferably in ambivalent instances</param>
	/// <param name="parsed_time">parsed date-time output</param>
	/// <returns>true if time was found, else false</returns>
	static public bool TryParseTime(this string str, DateTimeFormat default_format, out ParsedDateTime parsed_time)
	{
		return TryParseTime(str, default_format, out parsed_time, null);
	}

	/// <summary>
	/// Tries to find date and/or time within the passed string and return it as ParsedDateTime object. 
	/// If only date was found, time in the returned ParsedDateTime is always 0:0:0.
	/// If only time was found, date in the returned ParsedDateTime is DefaultDate.
	/// </summary>
	/// <param name="str">string that contains date-time</param>
	/// <param name="default_format">format to be used preferably in ambivalent instances</param>
	/// <param name="parsed_date_time">parsed date-time output</param>
	/// <returns>true if date or time was found, else false</returns>
	static public bool TryParseDateOrTime(this string str, DateTimeFormat default_format, out ParsedDateTime parsed_date_time)
	{
		parsed_date_time = null;

		ParsedDateTime parsed_date;
		ParsedDateTime parsed_time;
		if (!TryParseDate(str, default_format, out parsed_date))
		{
			if (!TryParseTime(str, default_format, out parsed_time, null))
				return false;

			DateTime date_time = new DateTime(DefaultDate.Year, DefaultDate.Month, DefaultDate.Day, parsed_time.DateTime.Hour, parsed_time.DateTime.Minute, parsed_time.DateTime.Second);
			parsed_date_time = new ParsedDateTime(-1, -1, parsed_time.IndexOfTime, parsed_time.LengthOfTime, date_time, parsed_time.UtcOffset);
		}
		else
		{
			if (!TryParseTime(str, default_format, out parsed_time, parsed_date))
			{
				DateTime date_time = new DateTime(parsed_date.DateTime.Year, parsed_date.DateTime.Month, parsed_date.DateTime.Day, 0, 0, 0);
				parsed_date_time = new ParsedDateTime(parsed_date.IndexOfDate, parsed_date.LengthOfDate, -1, -1, date_time);
			}
			else
			{
				DateTime date_time = new DateTime(parsed_date.DateTime.Year, parsed_date.DateTime.Month, parsed_date.DateTime.Day, parsed_time.DateTime.Hour, parsed_time.DateTime.Minute, parsed_time.DateTime.Second);
				parsed_date_time = new ParsedDateTime(parsed_date.IndexOfDate, parsed_date.LengthOfDate, parsed_time.IndexOfTime, parsed_time.LengthOfTime, date_time, parsed_time.UtcOffset);
			}
		}

		return true;
	}

	#endregion

	#region parsing base methods

	/// <summary>
	/// Tries to find time within the passed string (relatively to the passed parsed_date if any) and return it as ParsedDateTime object.
	/// It recognizes only time while ignoring date, so date in the returned ParsedDateTime is always 1/1/1
	/// </summary>
	/// <param name="str">string that contains date</param>
	/// <param name="default_format">format to be used preferably in ambivalent instances</param>
	/// <param name="parsed_time">parsed date-time output</param>
	/// <param name="parsed_date">ParsedDateTime object if the date was found within this string, else NULL</param>
	/// <returns>true if time was found, else false</returns>
	public static bool TryParseTime(this string str, DateTimeFormat default_format, out ParsedDateTime parsed_time, ParsedDateTime parsed_date)
	{
		parsed_time = null;

		string time_zone_r;
		if (default_format == DateTimeFormat.USA_DATE)
			time_zone_r = @"(?:\s*(?'time_zone'UTC|GMT|CST|EST))?";
		else
			time_zone_r = @"(?:\s*(?'time_zone'UTC|GMT))?";

		Match m;
		if (parsed_date != null && parsed_date.IndexOfDate > -1)
		{//look around the found date
		 //look for <date> hh:mm:ss <UTC offset> 
			m = Regex.Match(str.Substring(parsed_date.IndexOfDate + parsed_date.LengthOfDate), @"(?<=^\s*,?\s+|^\s*at\s*|^\s*[T\-]\s*)(?'hour'\d{2})\s*:\s*(?'minute'\d{2})\s*:\s*(?'second'\d{2})\s+(?'offset_sign'[\+\-])(?'offset_hh'\d{2}):?(?'offset_mm'\d{2})(?=$|[^\d\w])", RegexOptions.Compiled);
			if (!m.Success)
				//look for <date> [h]h:mm[:ss] [PM/AM] [UTC/GMT] 
				m = Regex.Match(str.Substring(parsed_date.IndexOfDate + parsed_date.LengthOfDate), @"(?<=^\s*,?\s+|^\s*at\s*|^\s*[T\-]\s*)(?'hour'\d{1,2})\s*:\s*(?'minute'\d{2})\s*(?::\s*(?'second'\d{2}))?(?:\s*(?'ampm'AM|am|PM|pm))?" + time_zone_r + @"(?=$|[^\d\w])", RegexOptions.Compiled);
			if (!m.Success)
				//look for [h]h:mm:ss [PM/AM] [UTC/GMT] <date>
				m = Regex.Match(str.Substring(0, parsed_date.IndexOfDate), @"(?<=^|[^\d])(?'hour'\d{1,2})\s*:\s*(?'minute'\d{2})\s*(?::\s*(?'second'\d{2}))?(?:\s*(?'ampm'AM|am|PM|pm))?" + time_zone_r + @"(?=$|[\s,]+)", RegexOptions.Compiled);
			if (!m.Success)
				//look for [h]h:mm:ss [PM/AM] [UTC/GMT] within <date>
				m = Regex.Match(str.Substring(parsed_date.IndexOfDate, parsed_date.LengthOfDate), @"(?<=^|[^\d])(?'hour'\d{1,2})\s*:\s*(?'minute'\d{2})\s*(?::\s*(?'second'\d{2}))?(?:\s*(?'ampm'AM|am|PM|pm))?" + time_zone_r + @"(?=$|[\s,]+)", RegexOptions.Compiled);
		}
		else//look anywhere within string
		{
			//look for hh:mm:ss <UTC offset> 
			m = Regex.Match(str, @"(?<=^|\s+|\s*T\s*)(?'hour'\d{2})\s*:\s*(?'minute'\d{2})\s*:\s*(?'second'\d{2})\s+(?'offset_sign'[\+\-])(?'offset_hh'\d{2}):?(?'offset_mm'\d{2})?(?=$|[^\d\w])", RegexOptions.Compiled);
			if (!m.Success)
				//look for [h]h:mm[:ss] [PM/AM] [UTC/GMT]
				m = Regex.Match(str, @"(?<=^|\s+|\s*T\s*)(?'hour'\d{1,2})\s*:\s*(?'minute'\d{2})\s*(?::\s*(?'second'\d{2}))?(?:\s*(?'ampm'AM|am|PM|pm))?" + time_zone_r + @"(?=$|[^\d\w])", RegexOptions.Compiled);
		}

		if (!m.Success)
			return false;

		//try
		//{
		int hour = int.Parse(m.Groups["hour"].Value);
		if (hour < 0 || hour > 23)
			return false;

		int minute = int.Parse(m.Groups["minute"].Value);
		if (minute < 0 || minute > 59)
			return false;

		int second = 0;
		if (!string.IsNullOrEmpty(m.Groups["second"].Value))
		{
			second = int.Parse(m.Groups["second"].Value);
			if (second < 0 || second > 59)
				return false;
		}

		if (string.Compare(m.Groups["ampm"].Value, "PM", true) == 0 && hour < 12)
			hour += 12;
		else if (string.Compare(m.Groups["ampm"].Value, "AM", true) == 0 && hour == 12)
			hour -= 12;

		DateTime date_time = new DateTime(1, 1, 1, hour, minute, second);

		if (m.Groups["offset_hh"].Success)
		{
			int offset_hh = int.Parse(m.Groups["offset_hh"].Value);
			int offset_mm = 0;
			if (m.Groups["offset_mm"].Success)
				offset_mm = int.Parse(m.Groups["offset_mm"].Value);
			TimeSpan utc_offset = new TimeSpan(offset_hh, offset_mm, 0);
			if (m.Groups["offset_sign"].Value == "-")
				utc_offset = -utc_offset;
			parsed_time = new ParsedDateTime(-1, -1, m.Index, m.Length, date_time, utc_offset);
			return true;
		}

		if (m.Groups["time_zone"].Success)
		{
			TimeSpan utc_offset;
			switch (m.Groups["time_zone"].Value)
			{
				case "UTC":
				case "GMT":
					utc_offset = new TimeSpan(0, 0, 0);
					break;
				case "CST":
					utc_offset = new TimeSpan(-6, 0, 0);
					break;
				case "EST":
					utc_offset = new TimeSpan(-5, 0, 0);
					break;
				default:
					throw new Exception("Time zone: " + m.Groups["time_zone"].Value + " is not defined.");
			}
			parsed_time = new ParsedDateTime(-1, -1, m.Index, m.Length, date_time, utc_offset);
			return true;
		}

		parsed_time = new ParsedDateTime(-1, -1, m.Index, m.Length, date_time);
		//}
		//catch(Exception e)
		//{
		//    return false;
		//}
		return true;
	}

	/// <summary>
	/// Tries to find date within the passed string and return it as ParsedDateTime object. 
	/// It recognizes only date while ignoring time, so time in the returned ParsedDateTime is always 0:0:0.
	/// If year of the date was not found then it accepts the current year. 
	/// </summary>
	/// <param name="str">string that contains date</param>
	/// <param name="default_format">format to be used preferably in ambivalent instances</param>
	/// <param name="parsed_date">parsed date output</param>
	/// <returns>true if date was found, else false</returns>
	static public bool TryParseDate(this string str, DateTimeFormat default_format, out ParsedDateTime parsed_date)
	{
		parsed_date = null;

		if (string.IsNullOrEmpty(str))
			return false;

		//look for dd/mm/yy
		Match m = Regex.Match(str, @"(?<=^|[^\d])(?'day'\d{1,2})\s*(?'separator'[\\/\.])+\s*(?'month'\d{1,2})\s*\'separator'+\s*(?'year'\d{2}|\d{4})(?=$|[^\d])", RegexOptions.Compiled | RegexOptions.IgnoreCase);
		if (m.Success)
		{
			DateTime date;
			if ((default_format ^ DateTimeFormat.USA_DATE) == DateTimeFormat.USA_DATE)
			{
				if (!convert_to_date(int.Parse(m.Groups["year"].Value), int.Parse(m.Groups["day"].Value), int.Parse(m.Groups["month"].Value), out date))
					return false;
			}
			else
			{
				if (!convert_to_date(int.Parse(m.Groups["year"].Value), int.Parse(m.Groups["month"].Value), int.Parse(m.Groups["day"].Value), out date))
					return false;
			}
			parsed_date = new ParsedDateTime(m.Index, m.Length, -1, -1, date);
			return true;
		}

		//look for [yy]yy-mm-dd
		m = Regex.Match(str, @"(?<=^|[^\d])(?'year'\d{2}|\d{4})\s*(?'separator'[\-])\s*(?'month'\d{1,2})\s*\'separator'+\s*(?'day'\d{1,2})(?=$|[^\d])", RegexOptions.Compiled | RegexOptions.IgnoreCase);
		if (m.Success)
		{
			DateTime date;
			if (!convert_to_date(int.Parse(m.Groups["year"].Value), int.Parse(m.Groups["month"].Value), int.Parse(m.Groups["day"].Value), out date))
				return false;
			parsed_date = new ParsedDateTime(m.Index, m.Length, -1, -1, date);
			return true;
		}

		//look for month dd yyyy
		m = Regex.Match(str, @"(?:^|[^\d\w])(?'month'Jan|Feb|Mar|Apr|May|Jun|Jul|Aug|Sep|Oct|Nov|Dec)[uarychilestmbro]*\s+(?'day'\d{1,2})(?:-?st|-?th|-?rd|-?nd)?\s*,?\s*(?'year'\d{4})(?=$|[^\d\w])", RegexOptions.Compiled | RegexOptions.IgnoreCase);
		if (!m.Success)
			//look for dd month [yy]yy
			m = Regex.Match(str, @"(?:^|[^\d\w:])(?'day'\d{1,2})(?:-?st\s+|-?th\s+|-?rd\s+|-?nd\s+|-|\s+)(?'month'Jan|Feb|Mar|Apr|May|Jun|Jul|Aug|Sep|Oct|Nov|Dec)[uarychilestmbro]*(?:\s*,?\s*|-)'?(?'year'\d{2}|\d{4})(?=$|[^\d\w])", RegexOptions.Compiled | RegexOptions.IgnoreCase);
		if (!m.Success)
			//look for yyyy month dd
			m = Regex.Match(str, @"(?:^|[^\d\w])(?'year'\d{4})\s+(?'month'Jan|Feb|Mar|Apr|May|Jun|Jul|Aug|Sep|Oct|Nov|Dec)[uarychilestmbro]*\s+(?'day'\d{1,2})(?:-?st|-?th|-?rd|-?nd)?(?=$|[^\d\w])", RegexOptions.Compiled | RegexOptions.IgnoreCase);
		if (!m.Success)
			//look for month dd hh:mm:ss MDT|UTC yyyy
			m = Regex.Match(str, @"(?:^|[^\d\w])(?'month'Jan|Feb|Mar|Apr|May|Jun|Jul|Aug|Sep|Oct|Nov|Dec)[uarychilestmbro]*\s+(?'day'\d{1,2})\s+\d{2}\:\d{2}\:\d{2}\s+(?:MDT|UTC)\s+(?'year'\d{4})(?=$|[^\d\w])", RegexOptions.Compiled | RegexOptions.IgnoreCase);
		if (!m.Success)
			//look for  month dd [yyyy]
			m = Regex.Match(str, @"(?:^|[^\d\w])(?'month'Jan|Feb|Mar|Apr|May|Jun|Jul|Aug|Sep|Oct|Nov|Dec)[uarychilestmbro]*\s+(?'day'\d{1,2})(?:-?st|-?th|-?rd|-?nd)?(?:\s*,?\s*(?'year'\d{4}))?(?=$|[^\d\w])", RegexOptions.Compiled | RegexOptions.IgnoreCase);
		if (m.Success)
		{
			int month = -1;
			int index_of_date = m.Index;
			int length_of_date = m.Length;

			switch (m.Groups["month"].Value)
			{
				case "Jan":
				case "JAN":
					month = 1;
					break;
				case "Feb":
				case "FEB":
					month = 2;
					break;
				case "Mar":
				case "MAR":
					month = 3;
					break;
				case "Apr":
				case "APR":
					month = 4;
					break;
				case "May":
				case "MAY":
					month = 5;
					break;
				case "Jun":
				case "JUN":
					month = 6;
					break;
				case "Jul":
					month = 7;
					break;
				case "Aug":
				case "AUG":
					month = 8;
					break;
				case "Sep":
				case "SEP":
					month = 9;
					break;
				case "Oct":
				case "OCT":
					month = 10;
					break;
				case "Nov":
				case "NOV":
					month = 11;
					break;
				case "Dec":
				case "DEC":
					month = 12;
					break;
			}

			int year;
			if (!string.IsNullOrEmpty(m.Groups["year"].Value))
				year = int.Parse(m.Groups["year"].Value);
			else
				year = DefaultDate.Year;

			DateTime date;
			if (!convert_to_date(year, month, int.Parse(m.Groups["day"].Value), out date))
				return false;
			parsed_date = new ParsedDateTime(index_of_date, length_of_date, -1, -1, date);
			return true;
		}

		return false;
	}

	static bool convert_to_date(int year, int month, int day, out DateTime date)
	{
		if (year >= 100)
		{
			if (year < 1000)
			{
				date = new DateTime(1, 1, 1);
				return false;
			}
		}
		else
			if (year > 30)
			year += 1900;
		else
			year += 2000;

		try
		{
			date = new DateTime(year, month, day);
		}
		catch
		{
			date = new DateTime(1, 1, 1);
			return false;
		}
		return true;
	}

	#endregion
}



#endregion