using System;
using System.Data.AccessClient;
using System.Data.OleDb;
using Medivators.Common;

namespace Medivators.Data.Queries
{
	public class ArchiveCycleReportLogRecords
	{
		private DateTime _date = DateTime.MinValue;

		public ArchiveCycleReportLogRecords(DateTime ArchiveDate)
		{
			_date = ArchiveDate;
		}
		public int Insert()
		{
			int recordsEffected = 0;
			string sql = string.Empty;
			sql += " insert into ArchiveCycleReportLogRecords ( CycleReportKey, LogRecordKey ) " + 
				   " select		 CycleReportLogRecords.CycleReportKey, CycleReportLogRecords.LogRecordKey  " +
				   " from	     CycleReportLogRecords  " +
				   " where		 CycleReportKey in ( " + 
								 " select	[Key] " +
								 " from		CycleReports " + 
								 " where    StartTimestamp < " + Constants.DateDelimiter + _date + Constants.DateDelimiter + ")" +
				   " and         LogRecordKey not in ( Select LogRecordKey from ArchiveCycleReportLogRecords)";	
			
			
			sql = sql.Replace("\t", " ");

			AccessConnection cnn = Connection.GetInstance();
			OleDbTransaction trn = null;
			
			try
			{
				cnn.Open();
				trn = (OleDbTransaction)cnn.BeginTransaction();
				using (AccessCommand cmd = new AccessCommand())
				{
					cmd.Connection = cnn;
					cmd.CommandType = System.Data.CommandType.Text;
					cmd.CommandText = sql;
					cmd.Transaction = trn;
					recordsEffected = cmd.ExecuteNonQuery();
				}				
			}
			catch (Exception ex)
			{
				trn.Rollback();
				ErrorHandler.GetInstance().LogException("ArchiveCycleReportLogRecords Insert Error: ", ex);
				throw;
			}

			if (recordsEffected > 0)
			{
				sql = " delete from	 CycleReportLogRecords  " +
					  " where		 CycleReportKey in ( " +
									 " select	[Key] " +
									 " from		CycleReports " +
									 " where    StartTimestamp < " + Constants.DateDelimiter + _date + Constants.DateDelimiter + ")";
				sql = sql.Replace("\t", " ");

				try
				{
					using (AccessCommand cmd = new AccessCommand())
					{
						cmd.Connection = cnn;
						cmd.CommandType = System.Data.CommandType.Text;
						cmd.CommandText = sql;
						cmd.Transaction = trn;
						recordsEffected = cmd.ExecuteNonQuery();
					}
				}
				catch (Exception ex)
				{
					trn.Rollback();
					ErrorHandler.GetInstance().LogException("ArchiveCycleReportLogRecords Delete Error: ", ex);
					throw;
				}
			}
			if (recordsEffected > 0)
				trn.Commit();
			else
				trn.Rollback();
			trn.Dispose();
			return recordsEffected;
		}
	}
}

