private static void StoreCommitmentTotal(LengthOfCommitmentReportTDSPmStaffDataRow PreviousRow, Int32 daysServed, DateTime AReportStartDate, DateTime AReportEndDate, DateTime previousStartDate, DateTime?previousEndDate, LengthOfCommitmentReportTDSPmStaffDataTable Anniversaries, List <Int32> ASpecialAnniversaries) { DateTime ThisWorkerEndDate; if (!previousEndDate.HasValue) { daysServed += ElapsedDays(AReportEndDate - previousStartDate); ThisWorkerEndDate = AReportEndDate; } else { if (AReportEndDate < previousEndDate) { daysServed += ElapsedDays(AReportEndDate - previousStartDate); ThisWorkerEndDate = AReportEndDate; } else { daysServed += ElapsedDays(previousEndDate.Value - previousStartDate); ThisWorkerEndDate = previousEndDate.Value; } } // we need the date where an anniversary happens, in the report time int TotalYears = Convert.ToInt32(daysServed / 365); DateTime anniversaryDate = AnniversaryDate(daysServed, ThisWorkerEndDate); bool doStore = ( (anniversaryDate.DayOfYear >= AReportStartDate.DayOfYear) && (anniversaryDate.DayOfYear <= AReportEndDate.DayOfYear)); if (ASpecialAnniversaries.Count > 0) { if (!ASpecialAnniversaries.Contains(TotalYears)) { doStore = false; } } if (doStore) { // this is a special anniversary // store the number of years, and the anniversary date LengthOfCommitmentReportTDSPmStaffDataRow newAnniversary = Anniversaries.NewRowTyped(); newAnniversary.Key = PreviousRow.Key; newAnniversary.SiteKey = PreviousRow.SiteKey; newAnniversary.PartnerKey = PreviousRow.PartnerKey; newAnniversary.TotalYears = TotalYears; newAnniversary.AnniversaryDay = anniversaryDate; newAnniversary.PartnerName = PreviousRow.PartnerName; newAnniversary.FirstName = PreviousRow.FirstName; newAnniversary.Surname = PreviousRow.Surname; newAnniversary.Gender = PreviousRow.Gender; Anniversaries.Rows.Add(newAnniversary); } }
/// <summary> /// get all partners and their commitment details /// </summary> public static DataTable GetLengthOfCommitment(TParameterList AParameters, TResultList AResults) { SortedList <string, string> Defines = new SortedList <string, string>(); List <OdbcParameter> SqlParameterList = new List <OdbcParameter>(); try { SqlParameterList.Add(new OdbcParameter("staffdate", OdbcType.Date) { Value = AParameters.Get("param_dtpCurrentStaff").ToDate() }); SqlParameterList.Add(new OdbcParameter("staffdate2", OdbcType.Date) { Value = AParameters.Get("param_dtpCurrentStaff").ToDate() }); } catch (Exception e) { TLogging.Log("problem while preparing sql statement for length of commitment report: " + e.ToString()); return(null); } string SqlStmt = TDataBase.ReadSqlFile("Personnel.Reports.AllCommitments.sql", Defines); Boolean NewTransaction; TDBTransaction Transaction = DBAccess.GDBAccessObj.GetNewOrExistingTransaction(IsolationLevel.ReadCommitted, out NewTransaction); try { // now run the database query TLogging.Log("Getting the data from the database...", TLoggingType.ToStatusBar); LengthOfCommitmentReportTDSPmStaffDataTable CommitmentTable = new LengthOfCommitmentReportTDSPmStaffDataTable(); DBAccess.GDBAccessObj.SelectDT(CommitmentTable, SqlStmt, Transaction, SqlParameterList.ToArray(), 0, 0); // if this is taking a long time, every now and again update the TLogging statusbar, and check for the cancel button if (AParameters.Get("CancelReportCalculation").ToBool() == true) { return(null); } List <Int32> SpecialAnniversaries = new List <int>(); if (AParameters.Get("param_chkAnniversaries").ToBool() == true) { string[] Anniversaries = AParameters.Get("param_txtAnniversaries").ToString().Split(new char[] { ',', ';' }); foreach (string s in Anniversaries) { SpecialAnniversaries.Add(Convert.ToInt32(s.Trim())); } } return(CalculateLengthOfCommitment( CommitmentTable, AParameters.Get("param_dtpFromDate").ToDate(), AParameters.Get("param_dtpToDate").ToDate(), SpecialAnniversaries)); } catch (Exception e) { TLogging.Log(e.ToString()); return(null); } finally { if (NewTransaction) { DBAccess.GDBAccessObj.RollbackTransaction(); } } }
private static DataTable CalculateLengthOfCommitment( LengthOfCommitmentReportTDSPmStaffDataTable ACommitmentTable, DateTime AReportStartDate, DateTime AReportEndDate, List <Int32> ASpecialAnniversaries) { LengthOfCommitmentReportTDSPmStaffDataTable Anniversaries = new LengthOfCommitmentReportTDSPmStaffDataTable(); // commitments are sorted by start date ACommitmentTable.DefaultView.Sort = LengthOfCommitmentReportTDSPmStaffDataTable.GetPartnerKeyDBName() + "," + LengthOfCommitmentReportTDSPmStaffDataTable.GetStartOfCommitmentDBName(); LengthOfCommitmentReportTDSPmStaffDataRow PreviousRow = null; Int64 CurrentPartnerKey = -1; Int64 PreviousPartnerKey = -1; // add up time, ignore overlaps DateTime previousStartDate = DateTime.MinValue; DateTime?previousEndDate = new Nullable <DateTime>(); // null value: open ended commitment // we need the overall time that someone has worked with us // only count full months int monthsServed = 0; foreach (DataRowView rv in ACommitmentTable.DefaultView) { LengthOfCommitmentReportTDSPmStaffDataRow row = (LengthOfCommitmentReportTDSPmStaffDataRow)rv.Row; CurrentPartnerKey = row.PartnerKey; if (CurrentPartnerKey != PreviousPartnerKey) { if (PreviousPartnerKey != -1) { StoreCommitmentTotal( PreviousRow, monthsServed, AReportStartDate, AReportEndDate, previousStartDate, previousEndDate, Anniversaries, ASpecialAnniversaries); } monthsServed = 0; PreviousPartnerKey = CurrentPartnerKey; PreviousRow = row; previousStartDate = row.StartOfCommitment; previousEndDate = row.IsEndOfCommitmentNull() ? new Nullable <DateTime>() : row.EndOfCommitment; } else { // another commitment for the current person if (!previousEndDate.HasValue) { // previous commitment was open ended continue; } if (row.StartOfCommitment > AReportEndDate) { // outside of current period for this report continue; } // overlapping commitment? if (row.StartOfCommitment <= previousEndDate) { if (!row.IsEndOfCommitmentNull() && (row.EndOfCommitment <= previousEndDate)) { // ignore this commitment, since it is already covered by the previous commitment } else { // modify previous commitment to be longer previousEndDate = row.IsEndOfCommitmentNull() ? new Nullable <DateTime>() : row.EndOfCommitment; } } else { // store previous commitment time, and start a new commitment monthsServed += ElapsedDays(previousEndDate.Value - previousStartDate); previousStartDate = row.StartOfCommitment; previousEndDate = row.IsEndOfCommitmentNull() ? new Nullable <DateTime>() : row.EndOfCommitment; } } } if (PreviousRow != null) { StoreCommitmentTotal( PreviousRow, monthsServed, AReportStartDate, AReportEndDate, previousStartDate, previousEndDate, Anniversaries, ASpecialAnniversaries); } return(Anniversaries); }
private static void StoreCommitmentTotal(LengthOfCommitmentReportTDSPmStaffDataRow PreviousRow, Int32 daysServed, DateTime AReportStartDate, DateTime AReportEndDate, DateTime previousStartDate, DateTime? previousEndDate, LengthOfCommitmentReportTDSPmStaffDataTable Anniversaries, List <Int32>ASpecialAnniversaries) { DateTime ThisWorkerEndDate; if (!previousEndDate.HasValue) { daysServed += ElapsedDays(AReportEndDate - previousStartDate); ThisWorkerEndDate = AReportEndDate; } else { if (AReportEndDate < previousEndDate) { daysServed += ElapsedDays(AReportEndDate - previousStartDate); ThisWorkerEndDate = AReportEndDate; } else { daysServed += ElapsedDays(previousEndDate.Value - previousStartDate); ThisWorkerEndDate = previousEndDate.Value; } } // we need the date where an anniversary happens, in the report time int TotalYears = Convert.ToInt32(daysServed / 365); DateTime anniversaryDate = AnniversaryDate(daysServed, ThisWorkerEndDate); bool doStore = ( (anniversaryDate.DayOfYear >= AReportStartDate.DayOfYear) && (anniversaryDate.DayOfYear <= AReportEndDate.DayOfYear)); if (ASpecialAnniversaries.Count > 0) { if (!ASpecialAnniversaries.Contains(TotalYears)) { doStore = false; } } if (doStore) { // this is a special anniversary // store the number of years, and the anniversary date LengthOfCommitmentReportTDSPmStaffDataRow newAnniversary = Anniversaries.NewRowTyped(); newAnniversary.Key = PreviousRow.Key; newAnniversary.SiteKey = PreviousRow.SiteKey; newAnniversary.PartnerKey = PreviousRow.PartnerKey; newAnniversary.TotalYears = TotalYears; newAnniversary.AnniversaryDay = anniversaryDate; newAnniversary.PartnerName = PreviousRow.PartnerName; newAnniversary.FirstName = PreviousRow.FirstName; newAnniversary.Surname = PreviousRow.Surname; newAnniversary.Gender = PreviousRow.Gender; Anniversaries.Rows.Add(newAnniversary); } }
/// <summary> /// get all partners and their commitment details /// </summary> public static DataTable GetLengthOfCommitment(TParameterList AParameters, TResultList AResults) { SortedList <string, string>Defines = new SortedList <string, string>(); List <OdbcParameter>SqlParameterList = new List <OdbcParameter>(); try { SqlParameterList.Add(new OdbcParameter("staffdate", OdbcType.Date) { Value = AParameters.Get("param_dtpCurrentStaff").ToDate() }); SqlParameterList.Add(new OdbcParameter("staffdate2", OdbcType.Date) { Value = AParameters.Get("param_dtpCurrentStaff").ToDate() }); } catch (Exception e) { TLogging.Log("problem while preparing sql statement for length of commitment report: " + e.ToString()); return null; } string SqlStmt = TDataBase.ReadSqlFile("Personnel.Reports.AllCommitments.sql", Defines); Boolean NewTransaction; TDBTransaction Transaction = DBAccess.GDBAccessObj.GetNewOrExistingTransaction(IsolationLevel.ReadCommitted, out NewTransaction); try { // now run the database query TLogging.Log("Getting the data from the database...", TLoggingType.ToStatusBar); LengthOfCommitmentReportTDSPmStaffDataTable CommitmentTable = new LengthOfCommitmentReportTDSPmStaffDataTable(); DBAccess.GDBAccessObj.SelectDT(CommitmentTable, SqlStmt, Transaction, SqlParameterList.ToArray(), 0, 0); // if this is taking a long time, every now and again update the TLogging statusbar, and check for the cancel button if (AParameters.Get("CancelReportCalculation").ToBool() == true) { return null; } List <Int32>SpecialAnniversaries = new List <int>(); if (AParameters.Get("param_chkAnniversaries").ToBool() == true) { string[] Anniversaries = AParameters.Get("param_txtAnniversaries").ToString().Split(new char[] { ',', ';' }); foreach (string s in Anniversaries) { SpecialAnniversaries.Add(Convert.ToInt32(s.Trim())); } } return CalculateLengthOfCommitment( CommitmentTable, AParameters.Get("param_dtpFromDate").ToDate(), AParameters.Get("param_dtpToDate").ToDate(), SpecialAnniversaries); } catch (Exception e) { TLogging.Log(e.ToString()); return null; } finally { if (NewTransaction) { DBAccess.GDBAccessObj.RollbackTransaction(); } } }
private static DataTable CalculateLengthOfCommitment( LengthOfCommitmentReportTDSPmStaffDataTable ACommitmentTable, DateTime AReportStartDate, DateTime AReportEndDate, List <Int32>ASpecialAnniversaries) { LengthOfCommitmentReportTDSPmStaffDataTable Anniversaries = new LengthOfCommitmentReportTDSPmStaffDataTable(); // commitments are sorted by start date ACommitmentTable.DefaultView.Sort = LengthOfCommitmentReportTDSPmStaffDataTable.GetPartnerKeyDBName() + "," + LengthOfCommitmentReportTDSPmStaffDataTable.GetStartOfCommitmentDBName(); LengthOfCommitmentReportTDSPmStaffDataRow PreviousRow = null; Int64 CurrentPartnerKey = -1; Int64 PreviousPartnerKey = -1; // add up time, ignore overlaps DateTime previousStartDate = DateTime.MinValue; DateTime? previousEndDate = new Nullable <DateTime>(); // null value: open ended commitment // we need the overall time that someone has worked with us // only count full months int monthsServed = 0; foreach (DataRowView rv in ACommitmentTable.DefaultView) { LengthOfCommitmentReportTDSPmStaffDataRow row = (LengthOfCommitmentReportTDSPmStaffDataRow)rv.Row; CurrentPartnerKey = row.PartnerKey; if (CurrentPartnerKey != PreviousPartnerKey) { if (PreviousPartnerKey != -1) { StoreCommitmentTotal( PreviousRow, monthsServed, AReportStartDate, AReportEndDate, previousStartDate, previousEndDate, Anniversaries, ASpecialAnniversaries); } monthsServed = 0; PreviousPartnerKey = CurrentPartnerKey; PreviousRow = row; previousStartDate = row.StartOfCommitment; previousEndDate = row.IsEndOfCommitmentNull() ? new Nullable <DateTime>() : row.EndOfCommitment; } else { // another commitment for the current person if (!previousEndDate.HasValue) { // previous commitment was open ended continue; } if (row.StartOfCommitment > AReportEndDate) { // outside of current period for this report continue; } // overlapping commitment? if (row.StartOfCommitment <= previousEndDate) { if (!row.IsEndOfCommitmentNull() && (row.EndOfCommitment <= previousEndDate)) { // ignore this commitment, since it is already covered by the previous commitment } else { // modify previous commitment to be longer previousEndDate = row.IsEndOfCommitmentNull() ? new Nullable <DateTime>() : row.EndOfCommitment; } } else { // store previous commitment time, and start a new commitment monthsServed += ElapsedDays(previousEndDate.Value - previousStartDate); previousStartDate = row.StartOfCommitment; previousEndDate = row.IsEndOfCommitmentNull() ? new Nullable <DateTime>() : row.EndOfCommitment; } } } if (PreviousRow != null) { StoreCommitmentTotal( PreviousRow, monthsServed, AReportStartDate, AReportEndDate, previousStartDate, previousEndDate, Anniversaries, ASpecialAnniversaries); } return Anniversaries; }