/**********************************************************************************************
@author  Ben Friedland
@version 1.0
Name of the File               :  Program.cs
Creation/Modification History  :
								  17-APR-2007     Created

This is the interface for extracting data from Tracy Fire and writing it out to a 
fixed format, fixed length file. 
*********************************************************************************************
*/

using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.IO;
using System.Xml;

namespace TestGrounds {
	class Program {

		private static XmlDocument xdoc;
		private static string masterIncidentNum;

		static void Main(string[] args) {
			XmlNode definitionNode;
			string connStr = Properties.Settings.Default.Connection;
			string incidentID = "185627";
			string sql = "SELECT TOP 1 i.*, v.Response_Number " +
				"FROM Response_Master_Incident i " +
				"LEFT JOIN Response_Vehicles_Assigned v ON " +
				"i.ID = v.Master_Incident_ID " +
				"WHERE i.ID = @incidentID\r\n " +
				"SELECT TOP 100 * FROM Response_Comments " +
				"WHERE Master_Incident_ID = @incidentID\r\n " +
				"SELECT TOP 10 * FROM Response_Transports " +
				"WHERE Master_Incident_ID = @incidentID\r\n " +
				"SELECT * FROM Response_Vehicles_Assigned rva " +
				"LEFT JOIN Response_Vehicles_Personnel rvp ON " +
				"rva.ID = rvp.Veh_Assigned_ID " +
				"LEFT JOIN Response_Transports rt ON " +
				"rva.ID = rt.Vehicle_Assigned_ID " +
				"WHERE rva.Master_Incident_ID = @incidentID\r\n";

			/** OPEN DB **/
			SqlConnection conn = new SqlConnection(connStr);
			conn.Open();
			SqlCommand cmd = new SqlCommand(sql, conn);
			cmd.Parameters.Add(new SqlParameter("incidentID", incidentID));
			SqlDataReader dr = cmd.ExecuteReader();
		
			
			/** START INC01 FILE **/
			string outFile = "c:\\IncOutput\\INC01.txt";
			File.Delete(outFile);
			FileStream fs = new FileStream(outFile, FileMode.CreateNew);
			StreamWriter sw = new StreamWriter(fs);

			/** WRITE HEADER (INC01) **/
			sw.Write(GetHeader("INC01"));
			
			/** BEGIN MASTER INCIDENT **/
			definitionNode = GetDefinitionNode("Response_Master_Incident");
			OutputSection(dr, definitionNode, sw, 1);
			
		
			/** BEGIN COMMENTS **/
			dr.NextResult();
			definitionNode = GetDefinitionNode("Response_Comments");
			int repeatCount = int.Parse(definitionNode.Attributes["Count"].Value);
			OutputSection(dr, definitionNode, sw, repeatCount);

			/** BEGIN TRANSPORTS **/
			dr.NextResult();
			definitionNode = GetDefinitionNode("Response_Transports");
			repeatCount = int.Parse(definitionNode.Attributes["Count"].Value);
			OutputSection(dr, definitionNode, sw, repeatCount);
			
			/** COURTESY FLUSH **/
			sw.Flush();
			
			/** CLOSE FILE **/
			sw.Write(GetChar(CharCodes.ETX));
			sw.Flush();
			sw.Close();
			fs.Close();
			sw.Dispose();
			fs.Dispose();

			/** = = = = = = = = = = = = = = = = = = = = = = = **/
			
			/** START INC02 FILE **/
			outFile = "c:\\IncOutput\\INC02.txt";
			File.Delete(outFile);
			fs = new FileStream(outFile, FileMode.CreateNew);
			sw = new StreamWriter(fs);

			/** WRITE HEADER (INC02) **/
			sw.Write(GetHeader("INC02"));

			/** WRITE AGENCY IDENTIFIER AND INCIDENT NUMBER **/
			//TODO: agency identifier
			sw.Write("??" + GetChar(CharCodes.FS));
			sw.Write(masterIncidentNum + GetChar(CharCodes.FS));
			
			/** BEGIN VEHICLES ASSIGNED **/
			dr.NextResult();
			definitionNode = GetDefinitionNode("Response_Vehicles_Assigned");
			OutputSection(dr, definitionNode, sw, 0);
			
			/** COURTESY FLUSH **/
			sw.Flush();

			/** CLOSE FILE **/
			sw.Write(GetChar(CharCodes.ETX));
			sw.Flush();
			sw.Close();
			fs.Close();
			sw.Dispose();
			fs.Dispose();

			/** CLOSE DATABASE */
			dr.Close();
			dr.Dispose();
			cmd.Dispose();
			conn.Close();
			conn.Dispose();
		}


		/// <summary>
		/// Returns a XMLNode containing the definiton of the specified table as 
		/// specified in the FieldDefinitions.xml file.
		/// </summary>
		/// <param name="p">A string containing the table name for which 
		/// to retrieve the definition.</param>
		/// <returns>A XMLNode containing the definition of the specified
		/// table.</returns>
		private static XmlNode GetDefinitionNode(string p) {
			if (xdoc == null) {
				xdoc = new XmlDocument();
				xdoc.InnerXml = File.ReadAllText("FieldDefinitions.xml");
			}

			return xdoc.DocumentElement.SelectSingleNode(
				"TableDefinition[@Name='"+p+"']"); 
		}


		/// <summary>
		/// Outputs a section of the data stream.
		/// </summary>
		/// <param name="dr">The DataReader, containing the data to output.</param>
		/// <param name="definitionNode">The data definition XMLNode.</param>
		/// <param name="sw">The StreamWriter to write to.</param>
		/// <param name="repeatCount">The number of times to repeat (or fluff). A
		/// value of zero (0) means these records are not fixed length, and should 
		/// be repeated for all results - and not padded.</param>
		private static void OutputSection(SqlDataReader dr, XmlNode defNode, 
			StreamWriter sw, int repeatCount) {
			int i = 0;
			while (dr.Read()) {
				i++;
				
				foreach (XmlNode fieldNode in defNode) {
					// skip comments, etc.
					if (fieldNode.Name != "Field")
						continue;
					string fieldName = fieldNode.Attributes["Name"].Value;
					int fieldLen = int.Parse(fieldNode.Attributes["Length"].Value);

					// PERSONNEL: I have to do this ridiculousness for the personnel
					//   subquery 
					if (fieldName == "Personnel") {
						string personnelFields = GetPersonnel((int)dr["ID"]);
						sw.Write(personnelFields);
						continue;
					}
					
					// store master incident number
					if (fieldName == "Master_Incident_Number") {
						masterIncidentNum = dr[fieldName].ToString();
					}

					string val = FormatValue(dr[fieldName], fieldLen);
					sw.Write(val);
				}
			}

			// For non-fixed-length records, just return. No padding necessary.
			if (repeatCount == 0)
				return;

			// if there were fewer records returned than repeatCount, and these
			// records are fixed-length, we need to add white space to fill 
			// in the rest.
			if (i < repeatCount) {
				// determine the record length
				int recLen = 0;
				foreach (XmlNode fieldNode in defNode) {
					recLen += int.Parse(fieldNode.Attributes["Length"].Value);
				}

				// fluff with spaces for records that weren't written
				int fluffLen = recLen * (repeatCount - i);
				sw.Write("".PadRight(fluffLen, ' '));
			}

		}

		/// <summary>
		/// Returns the top 6 personnel records for the specified Veh_Assigned_ID
		/// separated by a FS
		/// </summary>
		private static string GetPersonnel(int vehAssID) {
			string connStr = Properties.Settings.Default.Connection;
			string sql = "SELECT TOP 6 Personnel_Table_ID " + 
						"FROM Response_Vehicles_Personnel " +
						"WHERE Veh_Assigned_ID  = @vehAssID";

			/** OPEN DB **/
			SqlConnection conn = new SqlConnection(connStr);
			conn.Open();
			SqlCommand cmd = new SqlCommand(sql, conn);
			cmd.Parameters.Add(new SqlParameter("vehAssID", vehAssID));
			SqlDataReader dr = cmd.ExecuteReader();

			string ret = "";
			for (int i = 0; i < 6; i++) {
				if (dr.Read()) {
					ret += dr[0].ToString();
				}
				ret+=GetChar(CharCodes.FS);
			}

			/** CLOSE DATABASE */
			dr.Close();
			dr.Dispose();
			cmd.Dispose();
			conn.Close();
			conn.Dispose();

			return ret;
		}


		/// <summary>
		/// Formats a value, adding padding or truncating values to match 
		/// fieldLen. Also formats dates and converts bools to 1/0. 
		/// </summary>
		/// <param name="val">Object to format.</param>
		/// <param name="fieldLen">Length to pad/truncate. 
		/// If zero (0), appends a FS to the return val.</param>
		/// <returns>A string, formatted and padded or truncated.</returns>
		private static string FormatValue(object val, int fieldLen) {
			string ret = val.ToString();
			
			// DateTime
			if (val is DateTime) {
				DateTime d = (DateTime)val;
				if (fieldLen > 0) {
					ret = d.ToString("MM/dd/yyHH:mm:ss");
				} else {
					ret = d.ToString("MM/dd/yy" + GetChar(CharCodes.FS) +
						"HH:mm:ss" + GetChar(CharCodes.FS));
				}
				return ret;
				
			}

			// Boolean
			if (val is Boolean) {
				ret = ((bool)val)?"1":"0";
				if (fieldLen > 0) {
					ret += GetChar(CharCodes.FS);
				}
				return ret;
			}

			// String
			if (fieldLen > 0) {
				// for fixed width fields, trim or pad them 
				ret = ret.PadRight(fieldLen, ' '); // pad
				if (ret.Length > fieldLen)
					ret = ret.Substring(0, fieldLen); // trim
			} else {
				// non-fixed-width: just append a field separator
				ret = ret + GetChar(CharCodes.FS);
			}
			
			return ret;
		}

		private static string FormatValue(object val) {
			return FormatValue(val, 0);
		}

		static string GetHeader(string msgType) {
			string dateString = FormatValue(DateTime.Now, 16);
			return GetChar(CharCodes.SOH) +
				msgType + GetChar(CharCodes.FS) + // message type
				"1.0" + GetChar(CharCodes.FS) + // message format version
				dateString + GetChar(CharCodes.FS) + // date/time stamp 
				"0" + GetChar(CharCodes.FS) + // supress reply flag
				GetChar(CharCodes.FS) + // group quantity
				GetChar(CharCodes.FS) + // originating id
				GetChar(CharCodes.FS) + // vendor use
				GetChar(CharCodes.FS) + // reserved
				GetChar(CharCodes.FS) + // reserved
				GetChar(CharCodes.STX); // reserved
		}


		/// <summary>
		/// Returns a char corresponding to the specified CharCode
		/// </summary>
		/// <param name="code"></param>
		/// <returns></returns>
		static char GetChar(CharCodes code) {
			return Convert.ToChar((int)code);
		}

		/// <summary>
		/// An enumeration of character codes, and their ASCII decimal value.
		/// </summary>
		enum CharCodes { 
			/// <summary>
			/// Start of Header
			/// </summary>
			SOH = 1,

			/// <summary>
			/// Start of Text
			/// </summary>
			STX = 2, 

			/// <summary>
			/// End of Text
			/// </summary>
			ETX = 3,
 
			/// <summary>
			/// Line Feed
			/// </summary>
			LF = 10, 

			/// <summary>
			/// Carriage Return
			/// </summary>
			CR = 13, 

			/// <summary>
			/// Field Separator
			/// </summary>
			FS = 28
		}
	}
}