ayanova7/source/bizobjects/AyaLib/GZTW.AyaNova.BLL/DashboardServiceRateBalanceFetcher.cs

///////////////////////////////////////////////////////////
//  Bool.cs
//  Implementation of Class DashboardServiceRateBalanceFetcher
//  CSLA type: Read-only object
//  Created on: 11-Jan-2012
//  Object design: John 
//	Coded: John 11-Jan-2012
///////////////////////////////////////////////////////////

using System;
using System.Data;
using CSLA.Data;
using GZTW.Data;
using CSLA;
using System.Threading;
using CSLA.Security;



namespace GZTW.AyaNova.BLL 
{
#pragma warning disable 1591
	/// <summary>
	/// Fetch service hours balance for dashboard display
	/// </summary>
	[Serializable]
	public class DashboardServiceRateBalanceFetcher : ReadOnlyBase 
	{

		private decimal mToday=0;
		private decimal mYesterday=0;		
		private decimal mThisWeek=0;
        private decimal mThisMonth = 0;
        private decimal mThisYear = 0;		
      
		
		#region Constructor	

		/// <summary>
		/// Private constructor to prevent direct instantiation
		/// </summary>
		private DashboardServiceRateBalanceFetcher()
		{			
		
		}
		#endregion
		
		#region Business properties
        
		public decimal Today {get{return mToday;}}        
		public decimal Yesterday {get{return mYesterday;}}        
		public decimal ThisWeek {get{return mThisWeek;}}
        public decimal ThisMonth { get { return mThisMonth; } }
        public decimal ThisYear { get { return mThisYear; } }

        #endregion
		
		#region Static methods
		
		/// <summary>
		/// Get service rate balances
		/// </summary>
		/// <param name="UserId"></param>
		/// <param name="PersonalOnly">true - get for user, false - get for entire company or region only if user is regional</param>
		/// <returns></returns>
		public static DashboardServiceRateBalanceFetcher GetItem(Guid UserId, bool PersonalOnly)
		{			
			return (DashboardServiceRateBalanceFetcher)DataPortal.Fetch(new Criteria(UserId,  PersonalOnly));						
		}

		#endregion

		#region DAL DATA ACCESS
		
		protected override void DataPortal_Fetch(object Criteria)
		{
			Criteria crit = (Criteria)Criteria;        

            #region Pre-calculate dates

            //************* Today
            //Between yesterday at midnight and tommorow at midnight
            DateTime dTodayAfter = DBUtil.ToUTC(System.DateTime.Today.AddSeconds(-1));
            DateTime dTodayBefore = DBUtil.ToUTC(System.DateTime.Today.AddDays(1));

            //************* Yesterday
            //Between Day before yesterday at midnight and yesterday at midnight
            DateTime dYesterdayAfter = DBUtil.ToUTC(System.DateTime.Today.AddDays(-1).AddSeconds(-1));
            DateTime dYesterdayBefore = DBUtil.ToUTC(System.DateTime.Today);

            //************* ThisWeek
            //Between Sunday at midnight and Next sunday at midnight
            DateTime dThisWeekAfter = System.DateTime.Today;
            //go backwards to monday
            while (dThisWeekAfter.DayOfWeek != DayOfWeek.Monday)
                dThisWeekAfter = dThisWeekAfter.AddDays(-1);

            //Now go back to sunday last second
            dThisWeekAfter = dThisWeekAfter.AddSeconds(-1);

            DateTime dThisWeekBefore = System.DateTime.Today;
            //go forwards to monday
            if (System.DateTime.Today.DayOfWeek == DayOfWeek.Monday)
            {
                //Monday today? then go to next monday
                dThisWeekBefore = dThisWeekBefore.AddDays(7);
            }
            else
            {
                while (dThisWeekBefore.DayOfWeek != DayOfWeek.Monday)
                    dThisWeekBefore = dThisWeekBefore.AddDays(1);
            }

            dThisWeekAfter = DBUtil.ToUTC(dThisWeekAfter);
            dThisWeekBefore = DBUtil.ToUTC(dThisWeekBefore);

            //************* This Month
            //start with the first day of this month minus one second            
            DateTime dThisMonthAfter = DBUtil.ToUTC(new DateTime(DateTime.Today.Year, DateTime.Today.Month, 1, 00, 00, 00).AddSeconds(-1));
            //add a month plus one second
            DateTime dThisMonthBefore = DBUtil.ToUTC(dThisMonthAfter.AddMonths(1).AddSeconds(1));
            
            //************* This Year
            //start with the first day of this year minus one second            
            DateTime dThisYearAfter = DBUtil.ToUTC(new DateTime(DateTime.Today.Year, 1, 1, 00, 00, 00).AddSeconds(-1));
            //add a year plus one second
            DateTime dThisYearBefore = DBUtil.ToUTC(dThisYearAfter.AddYears(1).AddSeconds(1));
            #endregion

            DBCommandWrapper cm = null;
            if (crit.PersonalOnly)
            {
                //Personal only
                cm = DBUtil.DB.GetSqlStringCommandWrapper("SELECT SUM(ASERVICERATEQUANTITY) AS QUANTSUM  " +
                "FROM AWORKORDERITEMLABOR  " +
                "WHERE AUSERID=@USERID AND  " +
                "ASERVICESTARTDATE BETWEEN @STARTDATE AND @ENDDATE  ");
                cm.AddInParameter("@USERID", DbType.Guid, crit.UserId);
            }
            else
            {
                if (User.IsAdmin || User.CurrentUserIsInDefaultRegion)
                {
                    //Entire company
                    cm = DBUtil.DB.GetSqlStringCommandWrapper("SELECT SUM(ASERVICERATEQUANTITY) AS QUANTSUM  " +
                    "FROM AWORKORDERITEMLABOR  " +
                    "WHERE ASERVICESTARTDATE BETWEEN @STARTDATE AND @ENDDATE  ");

                }
                else
                {
                    //Regional
                    cm = DBUtil.DB.GetSqlStringCommandWrapper("SELECT SUM(ASERVICERATEQUANTITY) AS QUANTSUM " +
                    "FROM AWORKORDERITEMLABOR LEFT OUTER JOIN " +
                    "AWORKORDERITEM LEFT OUTER JOIN " +
                    "AWORKORDER LEFT OUTER JOIN " +
                    "ACLIENT ON AWORKORDER.ACLIENTID = ACLIENT.AID ON AWORKORDERITEM.AWORKORDERID = AWORKORDER.AID ON  " +
                    "AWORKORDERITEMLABOR.AWORKORDERITEMID = AWORKORDERITEM.AID " +
                    "WHERE ACLIENT.AREGIONID=@REGIONID AND  " +
                    "ASERVICESTARTDATE BETWEEN @STARTDATE AND @ENDDATE ");
                    cm.AddInParameter("@REGIONID", DbType.Guid, User.CurrentUserRegionID);
                }
            }


            //Add parameters initially so they can be modified later for each run
            //of the query
            cm.AddInParameter("@STARTDATE", DbType.DateTime, dTodayAfter);
            cm.AddInParameter("@ENDDATE", DbType.DateTime, dTodayBefore);

            //Get the values            
            mToday = GetValue(dTodayAfter, dTodayBefore, cm);
            mYesterday = GetValue(dYesterdayAfter, dYesterdayBefore, cm);
            mThisWeek = GetValue(dThisWeekAfter, dThisWeekBefore, cm);
            mThisMonth = GetValue(dThisMonthAfter, dThisMonthBefore, cm);
            mThisYear = GetValue(dThisYearAfter, dThisYearBefore, cm);
            		
		}

        private decimal GetValue(DateTime dAfter, DateTime dBefore, DBCommandWrapper cm)
        {
            decimal retValue = 0;
            cm.SetParameterValue("@STARTDATE", dAfter);
            cm.SetParameterValue("@ENDDATE", dBefore); 
            SafeDataReader dr = new SafeDataReader(DBUtil.DB.ExecuteReader(cm));
            if (dr.Read())
                retValue = dr.GetDecimal("QUANTSUM");
            dr.Close();
            return decimal.Round(retValue, 2, MidpointRounding.AwayFromZero);
        }

		
		
		#endregion			

		#region criteria
		/// <summary>
		/// Criteria for identifying existing object
		/// </summary>
		[Serializable]
			private class Criteria
		{			 
			public Guid UserId;
            public bool PersonalOnly;			

			public Criteria(Guid _UserId, bool _PersonalOnly)
			{
                UserId = _UserId;
                PersonalOnly = _PersonalOnly;				
			}			
		}
		#endregion
		
	}//end Bool
#pragma warning restore 1591
}//end Boolspace GZTW.AyaNova.BLL