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

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

		public ArchiveCycleReports (DateTime ArchiveDate)
		{
			_date = ArchiveDate;
		}
		public int Insert()
		{
			int recordsEffected = 0;
			string sql = string.Empty;
			sql += " insert into ArchiveCycleReports ( [Key], MachineID, Station, StartTimestamp, EndTimestamp, CycleCount, DisinfectantCycleCount, OperatorID, PatientID, PhysicianID, ScopeID, SSGRatio ) " +
				   " select		 CycleReports.Key, CycleReports.MachineID, CycleReports.Station, CycleReports.StartTimestamp, CycleReports.EndTimestamp, CycleReports.CycleCount, CycleReports.DisinfectantCycleCount, CycleReports.OperatorID, CycleReports.PatientID, CycleReports.PhysicianID, CycleReports.ScopeID, CycleReports.SSGRatio " +
				   " from		 CycleReports " +
				   " where		 CycleReports.StartTimestamp < " + Constants.DateDelimiter + _date + Constants.DateDelimiter +
				   " and         CycleReports.Key not in ( select [Key] from ArchiveCycleReports";
			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("ArchiveCycleReports Insert Error: ", ex);
				throw;
			}

			if (recordsEffected > 0)
			{
				sql = " delete from  CycleReports " +
					  " where        CycleReports.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("ArchiveCycleReports Delete Error: ", ex);
					throw;
				}
			}
			if (recordsEffected > 0)
				trn.Commit();
			else
				trn.Rollback();
			trn.Dispose();
			return recordsEffected;
		}
	}
}
