using System;
using System.Data;
using System.Text;
using System.Diagnostics;
using System.Globalization;

namespace IndianHealthService.BMXNet.Ado
{
	/*
	 * This class executes a query or RPC against the connected
	 * RPMS database and formats the return value as a RPMSDbResultSet
	 */
    internal class RPMSDb
	{
		private string							m_sSelectCmd = "SELECT ";
		private string							m_sUpdateCmd = "UPDATE ";
		private string							m_sCmd = "";

        private BMXNetSessionConnection m_sessionConnection;

        public BMXNetSessionConnection SessionConnection
        {
            get { return m_sessionConnection; }
            set { m_sessionConnection = value; }
        }

		private string							m_sQueryType = "";
		private RPMSDbResultSet[]				m_aResultSets = null;
		private int								m_nCurrentSet = 0;

		public class RPMSDbResultSet
		{
			public struct MetaData
			{
				public string	name;
				public Type		type;
				public int		maxSize;

				public string	fmFieldID;
				public bool		fmReadOnly;
				public bool		fmKeyField;
			}

			public int			recordsAffected;
			public MetaData[]	metaData;
			public object[,]	data;

			public string	fmKeyField;
			public string	fmFileID;
			public string	fmForeignKey = "";
			public int		fmSeed = 0;
            public string fmParentPath = "";
		}
		
		//Constructor
		public RPMSDb(BMXNetSessionConnection aConnection)
		{
            this.SessionConnection = aConnection;
		}
		
		public void Execute(string sCmd, out RPMSDbResultSet resultset)
		{
			/*
			 * SELECT, UPDATE and RPC operations.
			 */
			if (0 == String.Compare(sCmd, 0, m_sSelectCmd, 0, m_sSelectCmd.Length, true))
			{
				m_sCmd = sCmd;
				m_sQueryType = "SELECT";
				_executeSelect(out resultset);
			}
			else if (0 == String.Compare(sCmd, 0, m_sUpdateCmd, 0, m_sUpdateCmd.Length, true))
			{
				m_sCmd = sCmd;
				m_sQueryType = "UPDATE";
				_executeUpdate(out resultset);
			}
			else //RPC Call
			{
				m_sQueryType = "RPC";
				m_sCmd = sCmd;
				_executeSelect(out resultset);
			}
		}

		private void _executeSelect(out RPMSDbResultSet resultset)
		{
			if (m_nCurrentSet == 0)
				_resultsetCreate();

			// Return the current resultset.
			resultset = m_aResultSets[m_nCurrentSet];
		}

		private void _executeUpdate(out RPMSDbResultSet resultset)
		{
			int nStart = 7;
			m_sCmd = m_sCmd.Substring(nStart);

            String result=this.SessionConnection.TransmitRPC("BMX UPDATE", m_sCmd);
		
            this.AssertValidResult(result);
           
			resultset = new RPMSDbResultSet();
			resultset.recordsAffected = 1; //Make this the return value of the call
		}

        protected void AssertValidResult(String aResult)
        {
            string[] resultPeices = aResult.Split(new char[] { '|' });

            if (resultPeices.Length >= 1)
            {
                if (!("OK".Equals(resultPeices[0]) || "Record deleted".Equals(resultPeices[0])))
                {
                    throw new BMXNetException(aResult);
                }
            }            
        }

		private string CallRPX()
		{
			//Make rpx call
			string sRPC;
			string sParam;
			
            if (m_sQueryType == "SELECT")
			{
            	sRPC = "BMX SQL";
				sParam = m_sCmd;
			}
			else //it's an RPC (stored procedure)
			{
				sRPC = BMXNetBroker.Piece(m_sCmd, "^", 1);
				sParam = BMXNetBroker.Piece(m_sCmd, "^", 2 , 99);
			}

            return this.SessionConnection.TransmitRPC(sRPC, sParam);
		}

		private void ProcessHeader(string sHeader, int nSet, out int numCols)
		{
			//Modifies m_aResultSet[nSet]
			string sFldDelim = "^";
			char[] cFldDelim = sFldDelim.ToCharArray();
			string sZero = "0";
			char[] cZero = sZero.ToCharArray();
			string sBar = "|";
			char[] cBar = sBar.ToCharArray();

			int j;
			if (sHeader.StartsWith("@@@meta@@@") == true)
			{
				//New style -- ColumnHeaderInfo in [1]
				//Parse out data location or update logic here
				//RecordInfo ^ Column1 info ^ column2 info ^ ... ^ column n info
				string sType;
				string sMaxSize;
				string sFieldName;
				string sReadOnly;
				string	sKeyField;
				string	sFileID;
				string	sSeed = "";
				int nMaxSize;
				Type tType;

				int nTemp = 10;
				sHeader = sHeader.Substring(10,(sHeader.Length - nTemp));
				string[] sRecordSetInfo = sHeader.Split(cFldDelim);

				numCols = sRecordSetInfo.GetLength(0)-1;
				m_aResultSets[nSet].metaData = new RPMSDbResultSet.MetaData[numCols];

				//First ^-Piece is recordset-level info: RecordIdentifier|File#
				string[] sRecordInfo = sRecordSetInfo[0].Split(cBar);
				m_aResultSets[nSet].fmFileID = sRecordInfo[1];
                
                String parentPath = "";

				if (sRecordInfo.GetLength(0) > 2)
				{
                    String parentAndSeedInfo = sRecordInfo[2];
                    String seedText = parentAndSeedInfo;
                    int lastCommaIndex = parentAndSeedInfo.LastIndexOf(',');
                    if (lastCommaIndex != -1)
                    {
                        seedText = parentAndSeedInfo.Substring(lastCommaIndex + 1, seedText.Length - lastCommaIndex - 1);
                        parentPath = parentAndSeedInfo.Substring(0, lastCommaIndex);
                    }
               

					//sSeed = sRecordInfo[2];
					sSeed = (seedText == "")?"0":seedText;
					try
					{
						m_aResultSets[nSet].fmSeed = Convert.ToInt32(sSeed);
                        m_aResultSets[nSet].fmParentPath= parentPath;
					}
					catch (Exception ex)
					{
						Debug.Assert(false, "BMXNet.RMSPDb: 'Seed' value failed to convert to integer.");
						Debug.Write(ex.Message + "\n");
					}
				}

				if (sRecordInfo.GetLength(0) > 3)
				{
					m_aResultSets[nSet].fmForeignKey = sRecordInfo[4];

				}

					m_aResultSets[nSet].fmKeyField = "";
				//2nd through nth ^-Pieces are Column info: Fileman File#FileMan Field#|DataType|Field Length|Column Name|IsReadOnly|IsKeyField
				for (j=1; j < sRecordSetInfo.GetLength(0); j++)
				{
					string[] sColumnInfo = sRecordSetInfo[j].Split(cBar);
					//Field 0 = FileID
					//Field 1 = FieldID
					//Field 2 = DataType
					//Field 3 = Length
					//Field 4 = ColumnName
					//Field 5 = IsReadOnly
					//Field 6 = IsKeyField
					sFileID = sColumnInfo[0];
					string sFieldID = sColumnInfo[1];

					
					switch (sFieldID)
					{
						case ".001":
							m_aResultSets[nSet].fmKeyField += "," + sColumnInfo[4];
							break;
						case ".0001":
							m_aResultSets[nSet].fmKeyField = sColumnInfo[4];
							break;
						default:
							break;
					}

					sType = sColumnInfo[2];
					switch (sType)
					{
						case "D":   
							tType = typeof(DateTime);
							break;
						case "I":
							tType = typeof(Int32);
							break;
						case "N":
							tType = typeof(System.Double);
							break;
						default:
							tType = typeof(String);
							break;
					}
					sMaxSize = sColumnInfo[3];
                    nMaxSize = Convert.ToInt32(sMaxSize);
					sFieldName = sColumnInfo[4];

					if (m_aResultSets[nSet].fmForeignKey == sFieldID)
					{
						m_aResultSets[nSet].fmForeignKey = sFieldName;
					}

					sReadOnly = sColumnInfo[5];
					sKeyField = sColumnInfo[6];
					_resultsetFillColumn(nSet, j-1, sFieldName, tType, nMaxSize, sFieldID, sReadOnly, sKeyField);
				}
			}
			else
			{
				//Old style column header info
				
				string sField;
				string sType;
				string sMaxSize;
				string sFieldName;
				int nMaxSize;
				Type tType;

				string[] saHeader = sHeader.Split(cFldDelim);
				numCols = saHeader.GetLength(0);
				m_aResultSets[nSet].metaData = new RPMSDbResultSet.MetaData[numCols];
				for (j=0; j < numCols; j++)
				{
					sField = saHeader[j];
					sType = sField.Substring(0,1);
					if (sType == "D")
					{
						tType = typeof(DateTime);
					}
					else if(sType == "I")
					{
						tType = typeof(Int32);
					}
					else if(sType == "N")
					{
						tType = typeof(System.Double);
					}
					else
					{
						tType = typeof(String);
					}
					sMaxSize = sField.Substring(1,5);
					sMaxSize = sMaxSize.TrimStart(cZero);
					nMaxSize = Convert.ToInt16(sMaxSize);
					sFieldName = sField.Substring(6, sField.Length - 6);
					_resultsetFillColumn(nSet, j, sFieldName, tType, nMaxSize);
				}
			}
		}

		private void ProcessRecords(string[] sResultArray, int nRecords, int nSet, int numCols, int nStartIndex)
		{
            String[] formats = new string[] {"MMM d,yyyy","MMM dd,yyyy","MMM d, yyyy","MMM dd, yyyy", "MMM dd, yyyy@HH:mm", "MMM d, yyyy@HH:mm", "MMM d,yyyy@HH:mm", "MMM d, yyyy@H:mm", "MMM d,yyyy@H:mm", "MMM dd,yyyy@HH:mm" };                        
			char[] cFldDelim = new char[] {'^'};
			m_aResultSets[nSet].data = new object[nRecords-1, numCols];
			string[] saRecord;
			int j;
			for (j = nStartIndex; j < nRecords + nStartIndex -1; j++)
			{
				saRecord = sResultArray[j].Split(cFldDelim);
				for (int k = 0; k< saRecord.GetLength(0); k++)
				{
					if (m_aResultSets[nSet].metaData[k].type == typeof(DateTime))
					{
                        DateTime value;
                        String dateText = saRecord[k].ToString();
                        if (DateTime.TryParseExact(dateText, formats, CultureInfo.InvariantCulture, DateTimeStyles.AllowWhiteSpaces, out value))
                        {
                            m_aResultSets[nSet].data[j - nStartIndex, k] = value;
                        }
                        else if (DateTime.TryParse(dateText, out value))
                        {
                            m_aResultSets[nSet].data[j - nStartIndex, k] = value;
                        }
              		}
                    else if (m_aResultSets[nSet].metaData[k].type == typeof(Int32))
                    {
                        int value = 0;
                        if (int.TryParse(saRecord[k], out value))
                        {
                            m_aResultSets[nSet].data[j - nStartIndex, k] = value;
                        }
                    }
                    else if (m_aResultSets[nSet].metaData[k].type == typeof(Double))
                    {
                        double value = 0.0;
                        if (double.TryParse(saRecord[k], out value))
                        {
                            m_aResultSets[nSet].data[j - nStartIndex, k] = value;
                        }
                    }
                    else //it's a string
                    {
                        m_aResultSets[nSet].data[j - nStartIndex, k] = saRecord[k];
                    }
				}
			}		
		}

		

		private void _resultsetCreate()
		{
			m_nCurrentSet = 0;
			char[] cRecDelim = new char[1];
			cRecDelim[0] = (char) 30;

			char cEof = (char) 31;
			string sEof = cEof.ToString();

			string sFldDelim = "^";
			char[] cFldDelim = sFldDelim.ToCharArray();
			string sZero = "0";
			char[] cZero = sZero.ToCharArray();
			string sBar = "|";
			char[] cBar = sBar.ToCharArray();

			string sResult = this.CallRPX();

			string[] sResultArray = sResult.Split(cRecDelim);
			int nTotalRecords = sResultArray.GetLength(0);

			//Get set count and record count
			int[]	naRecords;			//Count of records for each set
			int[]	naHeaderIndex;		//Location (index) of header records in sResultArray
			int		nRecordSetCount;	//Count of recordsets

			IndexRecords(sResultArray, out naRecords, out naHeaderIndex, out nRecordSetCount);
			m_aResultSets = new RPMSDbResultSet[nRecordSetCount];

			for (int nSet = 0; nSet < nRecordSetCount; nSet++)
			{
				int nHeaderIndex = naHeaderIndex[nSet];
				int nRecords = naRecords[nSet];
				int numCols = 0;
				m_aResultSets[nSet] = new RPMSDbResultSet();
				m_aResultSets[nSet].recordsAffected = 0;

				//process header
				string sHeader = sResultArray[nHeaderIndex];
				ProcessHeader(sHeader, nSet, out numCols);

				//process records
				this.ProcessRecords(sResultArray, nRecords, nSet, numCols, nHeaderIndex+1);

			}
		}

		private void IndexRecords(string[] sResultArray, 
			out int[] naRecordsOut, 
			out int[] naHeaderIndexOut,
			out int nSets)
		{

			int[] naHeaderIndex = new int[999];
			int nTotalRecords = sResultArray.GetLength(0);
			nSets = 0;
			int[] naRecords = new int[999];

            if (sResultArray[0].StartsWith("@@@meta@@@"))
            {
                //Count the number of record sets and record the index of each header record
                for (int k = 0; k < nTotalRecords; k++)
                {
                    if (sResultArray[k].StartsWith("@@@meta@@@"))
                    {
                        naHeaderIndex[nSets] = k;
                        nSets++;
                    }
                }
                //Calculate record count for each set
                for (int k = 0; k < nSets - 1; k++)
                {
                    naRecords[k] = naHeaderIndex[k + 1] - naHeaderIndex[k];// - 1;
                }
                naRecords[nSets - 1] = nTotalRecords - naHeaderIndex[nSets - 1] - 1;
            }
            else
            {
                //this is an old-style header with only one record
                nSets = 1;
                naHeaderIndex[0] = 0;
                naRecords[0] = nTotalRecords - 1;
            }

			naRecordsOut = new int[nSets];
			naHeaderIndexOut = new int[nSets];
			for (int k = 0; k < nSets; k++)
			{
				naRecordsOut[k] = naRecords[k];
				naHeaderIndexOut[k] = naHeaderIndex[k];
			}
		}

		private void _resultsetFillColumn(int nSet, int nIdx, string name, Type type, int maxSize)
		{
			m_aResultSets[nSet].metaData[nIdx].name    = name;
			m_aResultSets[nSet].metaData[nIdx].type    = type;
			m_aResultSets[nSet].metaData[nIdx].maxSize  = maxSize;
		}

		private void _resultsetFillColumn(int nSet, int nIdx, string name, Type type, int maxSize, 
			string sFieldID, string sReadOnly, string sKeyField)
		{
			m_aResultSets[nSet].metaData[nIdx].name    = name;
			m_aResultSets[nSet].metaData[nIdx].type    = type;
			m_aResultSets[nSet].metaData[nIdx].maxSize  = maxSize;

			m_aResultSets[nSet].metaData[nIdx].fmFieldID = sFieldID;
			m_aResultSets[nSet].metaData[nIdx].fmReadOnly = !sReadOnly.Equals("FALSE",StringComparison.InvariantCultureIgnoreCase);
			m_aResultSets[nSet].metaData[nIdx].fmKeyField = sKeyField.Equals("TRUE",StringComparison.InvariantCultureIgnoreCase);

		}

		
		/// <summary>
		/// Returns the array of RMPSResultSets retrieved from RPMS
		/// </summary>
		public RPMSDbResultSet[] ResultSets
		{
			get
			{
				return m_aResultSets;
			}
		}

		/// <summary>
		/// Sets and Returns the current recordset
		/// </summary>
		public int CurrentRecordSet
		{
			get
			{
				return m_nCurrentSet;
			}
			set
			{
				m_nCurrentSet = value;
			}
		}


	}
}