//
// Put Methods for the validation of Personnel Module WebConnectors in this code file.
//
static partial void ValidatePersonnelStaffManual(ref TVerificationResultCollection AVerificationResult,
TTypedDataTable ASubmitTable)
{
TValidationControlsDict ValidationControlsDict = new TValidationControlsDict();
ValidationControlsDict.Add(ASubmitTable.Columns[PmStaffDataTable.ColumnReceivingFieldId],
new TValidationControlsData(null, PmStaffDataTable.GetReceivingFieldDBName()));
ValidationControlsDict.Add(ASubmitTable.Columns[PmStaffDataTable.ColumnStartOfCommitmentId],
new TValidationControlsData(null, PmStaffDataTable.GetStartOfCommitmentDBName()));
ValidationControlsDict.Add(ASubmitTable.Columns[PmStaffDataTable.ColumnEndOfCommitmentId],
new TValidationControlsData(null, PmStaffDataTable.GetEndOfCommitmentDBName(),
null, PmStaffDataTable.GetStartOfCommitmentDBName()));
ValidationControlsDict.Add(ASubmitTable.Columns[PmStaffDataTable.ColumnStatusCodeId],
new TValidationControlsData(null, PmStaffDataTable.GetStatusCodeDBName()));
ValidationControlsDict.Add(ASubmitTable.Columns[PmStaffDataTable.ColumnHomeOfficeId],
new TValidationControlsData(null, PmStaffDataTable.GetHomeOfficeDBName()));
ValidationControlsDict.Add(ASubmitTable.Columns[PmStaffDataTable.ColumnOfficeRecruitedById],
new TValidationControlsData(null, PmStaffDataTable.GetOfficeRecruitedByDBName()));
for (int Counter = 0; Counter < ASubmitTable.Rows.Count; Counter++)
{
TSharedPersonnelValidation_Personnel.ValidateCommitmentManual("TPersonnelWebConnector" +
" (Error in Row #" + Counter.ToString() + ")", // No translation of message text since the server's messages should be all in English
(PmStaffDataRow)ASubmitTable.Rows[Counter], ref AVerificationResult,
ValidationControlsDict);
}
}
/// <summary>
/// Gives the user the option to update Gift Destination records if commitments have been added
/// </summary>
/// <param name="AInspectDS"></param>
public bool UpdateGiftDestination(ref PartnerEditTDS AInspectDS)
{
PmStaffDataTable EligibleCommitments = new PmStaffDataTable();
FIndividualDataDS = new IndividualDataTDS();
FInspectDS = AInspectDS;
FIndividualDataDS.Merge(AInspectDS);
// return if no changes have been made to commitments
if ((FIndividualDataDS.PmStaffData == null) || (FIndividualDataDS.PmStaffData.Rows.Count == 0))
{
return false;
}
// load original (currently saved) PmStaffData
FOriginalCommitments = TRemote.MPersonnel.WebConnectors.LoadPersonellStaffData(AInspectDS.PPartner[0].PartnerKey).PmStaffData;
if (!GetEligibleRowsForUpdatingGiftDestination(ref EligibleCommitments))
{
// no eligible records
return false;
}
// iterate through each eligible row
foreach (PmStaffDataRow EligibleCommitmentRow in EligibleCommitments.Rows)
{
UpdateGiftDestinationForSingleCommitment(EligibleCommitmentRow);
}
return true;
}
/// <summary>
/// Gives the user the option to update Gift Destination records if commitments have been added
/// </summary>
/// <param name="AInspectDS"></param>
public bool UpdateGiftDestination(ref PartnerEditTDS AInspectDS)
{
PmStaffDataTable EligibleCommitments = new PmStaffDataTable();
FIndividualDataDS = new IndividualDataTDS();
FInspectDS = AInspectDS;
FIndividualDataDS.Merge(AInspectDS);
// return if no changes have been made to commitments
if ((FIndividualDataDS.PmStaffData == null) || (FIndividualDataDS.PmStaffData.Rows.Count == 0))
{
return(false);
}
// load original (currently saved) PmStaffData
FOriginalCommitments = TRemote.MPersonnel.WebConnectors.LoadPersonellStaffData(AInspectDS.PPartner[0].PartnerKey).PmStaffData;
if (!GetEligibleRowsForUpdatingGiftDestination(ref EligibleCommitments))
{
// no eligible records
return(false);
}
// iterate through each eligible row
foreach (PmStaffDataRow EligibleCommitmentRow in EligibleCommitments.Rows)
{
UpdateGiftDestinationForSingleCommitment(EligibleCommitmentRow);
}
return(true);
}
// check if a person has on going commitments
private static bool PersonHasCommitments(PPersonTable ATable, TDBTransaction ATransaction)
{
foreach (PPersonRow Row in ATable.Rows)
{
PmStaffDataTable StaffDataTable = PmStaffDataAccess.LoadViaPPerson(Row.PartnerKey, ATransaction);
foreach (PmStaffDataRow StaffRow in StaffDataTable.Rows)
{
if (((StaffRow.EndOfCommitment >= DateTime.Now) || (StaffRow.EndOfCommitment == null)) &&
(StaffRow.StartOfCommitment <= DateTime.Now))
{
return(true);
}
}
}
return(false);
}
/// Determine which (if any) new or modified rows are eligible to update the gift destination
private bool GetEligibleRowsForUpdatingGiftDestination(ref PmStaffDataTable AEligibleCommitments)
{
PmStaffDataRow OriginalCommitmentRow = null;
foreach (PmStaffDataRow Row in FIndividualDataDS.PmStaffData.Rows)
{
// Commitments only trigger changes to Gift Destination if they are new and apply to future dates.
if (Row.RowState == DataRowState.Deleted)
{
// need to temporarily undelete row
Row.RejectChanges();
if (Row.IsEndOfCommitmentNull() || (Row.EndOfCommitment >= DateTime.Today))
{
AEligibleCommitments.LoadDataRow(Row.ItemArray, true);
}
Row.Delete();
}
else
{
OriginalCommitmentRow = (PmStaffDataRow)FOriginalCommitments.Rows.Find(
new object[] { Row.SiteKey, Row.Key });
if ((Row.RowState != DataRowState.Unchanged) && (Row.IsEndOfCommitmentNull() || (Row.EndOfCommitment >= DateTime.Today) ||
((OriginalCommitmentRow != null) &&
(OriginalCommitmentRow.IsEndOfCommitmentNull() ||
(OriginalCommitmentRow.EndOfCommitment >= DateTime.Today)))))
{
AEligibleCommitments.LoadDataRow(Row.ItemArray, true);
}
}
}
if (AEligibleCommitments.Rows.Count == 0)
{
return(false);
}
return(true);
}
public static TSubmitChangesResult SavePersonnelTDS(ref PersonnelTDS AInspectDS,
out TVerificationResultCollection AVerificationResult)
{
TSubmitChangesResult SubmissionResult = TSubmitChangesResult.scrError;
bool AllDataValidationsOK = true;
AVerificationResult = new TVerificationResultCollection();
// TODO: calculate debit and credit sums for journal and batch?
if (AInspectDS.Tables.Contains(PmStaffDataTable.GetTableName()))
{
if (AInspectDS.PmStaffData.Rows.Count > 0)
{
ValidatePersonnelStaff(ref AVerificationResult, AInspectDS.PmStaffData);
ValidatePersonnelStaffManual(ref AVerificationResult, AInspectDS.PmStaffData);
if (!TVerificationHelper.IsNullOrOnlyNonCritical(AVerificationResult))
{
AllDataValidationsOK = false;
}
}
}
if (AllDataValidationsOK)
{
PersonnelTDSAccess.SubmitChanges(AInspectDS);
SubmissionResult = TSubmitChangesResult.scrOK;
}
else if (AVerificationResult.Count > 0)
{
// Downgrade TScreenVerificationResults to TVerificationResults in order to allow
// Serialisation (needed for .NET Remoting).
TVerificationResultCollection.DowngradeScreenVerificationResults(AVerificationResult);
}
return(SubmissionResult);
}
/// Determine which (if any) new or modified rows are eligible to update the gift destination
private bool GetEligibleRowsForUpdatingGiftDestination(ref PmStaffDataTable AEligibleCommitments)
{
PmStaffDataRow OriginalCommitmentRow = null;
foreach (PmStaffDataRow Row in FIndividualDataDS.PmStaffData.Rows)
{
// Commitments only trigger changes to Gift Destination if they are new and apply to future dates.
if (Row.RowState == DataRowState.Deleted)
{
// need to temporarily undelete row
Row.RejectChanges();
if (Row.IsEndOfCommitmentNull() || (Row.EndOfCommitment >= DateTime.Today))
{
AEligibleCommitments.LoadDataRow(Row.ItemArray, true);
}
Row.Delete();
}
else
{
OriginalCommitmentRow = (PmStaffDataRow)FOriginalCommitments.Rows.Find(
new object[] { Row.SiteKey, Row.Key });
if ((Row.RowState != DataRowState.Unchanged) && (Row.IsEndOfCommitmentNull() || (Row.EndOfCommitment >= DateTime.Today)
|| ((OriginalCommitmentRow != null)
&& (OriginalCommitmentRow.IsEndOfCommitmentNull()
|| (OriginalCommitmentRow.EndOfCommitment >= DateTime.Today)))))
{
AEligibleCommitments.LoadDataRow(Row.ItemArray, true);
}
}
}
if (AEligibleCommitments.Rows.Count == 0)
{
return false;
}
return true;
}
public void TestDeletePerson()
{
DataSet ResponseDS = new PartnerEditTDS();
TVerificationResultCollection VerificationResult;
String TextMessage;
Boolean CanDeletePartner;
PPartnerRow FamilyPartnerRow;
PPartnerRow UnitPartnerRow;
PPersonRow PersonRow;
TSubmitChangesResult result;
Int64 PartnerKey;
TPartnerEditUIConnector connector = new TPartnerEditUIConnector();
PartnerEditTDS MainDS = new PartnerEditTDS();
// create new family, location and person
FamilyPartnerRow = TCreateTestPartnerData.CreateNewFamilyPartner(MainDS);
TCreateTestPartnerData.CreateNewLocation(FamilyPartnerRow.PartnerKey, MainDS);
PersonRow = TCreateTestPartnerData.CreateNewPerson(MainDS,
FamilyPartnerRow.PartnerKey,
MainDS.PLocation[0].LocationKey,
"Mike",
"Mr",
0);
result = connector.SubmitChanges(ref MainDS, ref ResponseDS, out VerificationResult);
Assert.AreEqual(TSubmitChangesResult.scrOK, result, "create family and person record");
// check if Family partner can be deleted (still needs to be possible at this point)
CanDeletePartner = TPartnerWebConnector.CanPartnerBeDeleted(PersonRow.PartnerKey, out TextMessage);
if (TextMessage.Length > 0)
{
TLogging.Log(TextMessage);
}
Assert.IsTrue(CanDeletePartner);
// add a commitment for the person which means the person is not allowed to be deleted any longer
UnitPartnerRow = TCreateTestPartnerData.CreateNewUnitPartner(MainDS);
PmStaffDataTable CommitmentTable = new PmStaffDataTable();
PmStaffDataRow CommitmentRow = CommitmentTable.NewRowTyped();
CommitmentRow.Key = Convert.ToInt32(TSequenceWebConnector.GetNextSequence(TSequenceNames.seq_staff_data));
CommitmentRow.PartnerKey = PersonRow.PartnerKey;
CommitmentRow.StartOfCommitment = DateTime.Today.Date;
CommitmentRow.EndOfCommitment = DateTime.Today.AddDays(90).Date;
CommitmentRow.OfficeRecruitedBy = UnitPartnerRow.PartnerKey;
CommitmentRow.HomeOffice = UnitPartnerRow.PartnerKey;
CommitmentRow.ReceivingField = UnitPartnerRow.PartnerKey;
CommitmentTable.Rows.Add(CommitmentRow);
result = connector.SubmitChanges(ref MainDS, ref ResponseDS, out VerificationResult);
Assert.AreEqual(TSubmitChangesResult.scrOK, result, "create unit to be used in commitment");
PmStaffDataAccess.SubmitChanges(CommitmentTable, DBAccess.GDBAccessObj.Transaction);
// this should now not be allowed since person record has a commitment linked to it
CanDeletePartner = TPartnerWebConnector.CanPartnerBeDeleted(PersonRow.PartnerKey, out TextMessage);
if (TextMessage.Length > 0)
{
TLogging.Log(TextMessage);
}
Assert.IsTrue(!CanDeletePartner);
// now test actual deletion of Person partner
FamilyPartnerRow = TCreateTestPartnerData.CreateNewFamilyPartner(MainDS);
TCreateTestPartnerData.CreateNewLocation(FamilyPartnerRow.PartnerKey, MainDS);
PersonRow = TCreateTestPartnerData.CreateNewPerson(MainDS,
FamilyPartnerRow.PartnerKey,
MainDS.PLocation[0].LocationKey,
"Mary",
"Mrs",
0);
PartnerKey = PersonRow.PartnerKey;
result = connector.SubmitChanges(ref MainDS, ref ResponseDS, out VerificationResult);
Assert.AreEqual(TSubmitChangesResult.scrOK, result, "create family and person record to be deleted");
// check if Family record is being deleted
Assert.IsTrue(TPartnerWebConnector.DeletePartner(PartnerKey, out VerificationResult));
// check that Family record is really deleted
Assert.IsTrue(!TPartnerServerLookups.VerifyPartner(PartnerKey));
}
public void TestDeletePerson()
{
DataSet ResponseDS = new PartnerEditTDS();
TVerificationResultCollection VerificationResult;
String TextMessage;
Boolean CanDeletePartner;
PPartnerRow FamilyPartnerRow;
PPartnerRow UnitPartnerRow;
PPersonRow PersonRow;
TSubmitChangesResult result;
Int64 PartnerKey;
TPartnerEditUIConnector connector = new TPartnerEditUIConnector();
PartnerEditTDS MainDS = new PartnerEditTDS();
// create new family, location and person
FamilyPartnerRow = TCreateTestPartnerData.CreateNewFamilyPartner(MainDS);
TCreateTestPartnerData.CreateNewLocation(FamilyPartnerRow.PartnerKey, MainDS);
PersonRow = TCreateTestPartnerData.CreateNewPerson(MainDS,
FamilyPartnerRow.PartnerKey,
MainDS.PLocation[0].LocationKey,
"Mike",
"Mr",
0);
result = connector.SubmitChanges(ref MainDS, ref ResponseDS, out VerificationResult);
Assert.AreEqual(TSubmitChangesResult.scrOK, result, "create family and person record");
// check if Family partner can be deleted (still needs to be possible at this point)
CanDeletePartner = TPartnerWebConnector.CanPartnerBeDeleted(PersonRow.PartnerKey, out TextMessage);
if (TextMessage.Length > 0)
{
TLogging.Log(TextMessage);
}
Assert.IsTrue(CanDeletePartner);
// add a commitment for the person which means the person is not allowed to be deleted any longer
UnitPartnerRow = TCreateTestPartnerData.CreateNewUnitPartner(MainDS);
PmStaffDataTable CommitmentTable = new PmStaffDataTable();
PmStaffDataRow CommitmentRow = CommitmentTable.NewRowTyped();
CommitmentRow.Key = Convert.ToInt32(TSequenceWebConnector.GetNextSequence(TSequenceNames.seq_staff_data));
CommitmentRow.PartnerKey = PersonRow.PartnerKey;
CommitmentRow.StartOfCommitment = DateTime.Today.Date;
CommitmentRow.EndOfCommitment = DateTime.Today.AddDays(90).Date;
CommitmentRow.OfficeRecruitedBy = UnitPartnerRow.PartnerKey;
CommitmentRow.HomeOffice = UnitPartnerRow.PartnerKey;
CommitmentRow.ReceivingField = UnitPartnerRow.PartnerKey;
CommitmentTable.Rows.Add(CommitmentRow);
result = connector.SubmitChanges(ref MainDS, ref ResponseDS, out VerificationResult);
Assert.AreEqual(TSubmitChangesResult.scrOK, result, "create unit to be used in commitment");
PmStaffDataAccess.SubmitChanges(CommitmentTable, DBAccess.GDBAccessObj.Transaction);
// this should now not be allowed since person record has a commitment linked to it
CanDeletePartner = TPartnerWebConnector.CanPartnerBeDeleted(PersonRow.PartnerKey, out TextMessage);
if (TextMessage.Length > 0)
{
TLogging.Log(TextMessage);
}
Assert.IsTrue(!CanDeletePartner);
// now test actual deletion of Person partner
FamilyPartnerRow = TCreateTestPartnerData.CreateNewFamilyPartner(MainDS);
TCreateTestPartnerData.CreateNewLocation(FamilyPartnerRow.PartnerKey, MainDS);
PersonRow = TCreateTestPartnerData.CreateNewPerson(MainDS,
FamilyPartnerRow.PartnerKey,
MainDS.PLocation[0].LocationKey,
"Mary",
"Mrs",
0);
PartnerKey = PersonRow.PartnerKey;
result = connector.SubmitChanges(ref MainDS, ref ResponseDS, out VerificationResult);
Assert.AreEqual(TSubmitChangesResult.scrOK, result, "create family and person record to be deleted");
// check if Family record is being deleted
Assert.IsTrue(TPartnerWebConnector.DeletePartner(PartnerKey, out VerificationResult));
// check that Family record is really deleted
Assert.IsTrue(!TPartnerServerLookups.VerifyPartner(PartnerKey));
}
public static void FillFormDataFromPersonnel(Int64 APartnerKey,
TFormDataPerson AFormDataPerson,
TFormLetterInfo AFormLetterInfo,
TDBTransaction AReadTransaction,
Int64 ASiteKey = 0,
Int32 ALocationKey = 0)
{
TPartnerClass PartnerClass;
String ShortName;
TStdPartnerStatusCode PartnerStatusCode;
if (AFormDataPerson == null)
{
return;
}
if (MCommonMain.RetrievePartnerShortName(APartnerKey, out ShortName, out PartnerClass, out PartnerStatusCode, AReadTransaction))
{
// first retrieve all partner information
// we cannot reference MPartner connect because of SimplePartnerEdit, cyclic dependencies
#if DISABLED_TP_20180623
TFormLettersWebConnector.FillFormDataFromPerson(APartnerKey,
AFormDataPerson,
AFormLetterInfo,
AReadTransaction,
ASiteKey,
ALocationKey);
#endif
// retrieve Special Needs information
if (AFormLetterInfo.IsRetrievalRequested(TFormDataRetrievalSection.eSpecialNeeds))
{
PmSpecialNeedTable SpecialNeedTable;
PmSpecialNeedRow SpecialNeedRow;
SpecialNeedTable = PmSpecialNeedAccess.LoadViaPPerson(APartnerKey, AReadTransaction);
if (SpecialNeedTable.Count > 0)
{
SpecialNeedRow = (PmSpecialNeedRow)SpecialNeedTable.Rows[0];
AFormDataPerson.DietaryNeeds = SpecialNeedRow.DietaryComment;
AFormDataPerson.MedicalNeeds = SpecialNeedRow.MedicalComment;
AFormDataPerson.OtherNeeds = SpecialNeedRow.OtherSpecialNeed;
AFormDataPerson.Vegetarian = SpecialNeedRow.VegetarianFlag;
}
}
// retrieve Personal Data information
if (AFormLetterInfo.IsRetrievalRequested(TFormDataRetrievalSection.ePersonalData))
{
PmPersonalDataTable PersonalDataTable;
PmPersonalDataRow PersonalDataRow;
PersonalDataTable = PmPersonalDataAccess.LoadViaPPerson(APartnerKey, AReadTransaction);
if (PersonalDataTable.Count > 0)
{
PersonalDataRow = (PmPersonalDataRow)PersonalDataTable.Rows[0];
if (!PersonalDataRow.IsBelieverSinceYearNull() &&
(PersonalDataRow.BelieverSinceYear != 0))
{
AFormDataPerson.YearsBeliever = (DateTime.Today.Year - PersonalDataRow.BelieverSinceYear).ToString();
}
AFormDataPerson.CommentBeliever = PersonalDataRow.BelieverSinceComment;
}
}
// retrieve Passport information
if (AFormLetterInfo.IsRetrievalRequested(TFormDataRetrievalSection.ePassport))
{
PmPassportDetailsTable PassportTable;
TFormDataPassport PassportRecord;
PassportTable = PmPassportDetailsAccess.LoadViaPPerson(APartnerKey, AReadTransaction);
Boolean MainPassportFound = false;
foreach (PmPassportDetailsRow PassportRow in PassportTable.Rows)
{
// only list "full" passports that have not expired yet
if ((PassportRow.IsDateOfExpirationNull() ||
(PassportRow.DateOfExpiration >= DateTime.Today)) &&
(PassportRow.PassportDetailsType == "P"))
{
PassportRecord = new TFormDataPassport();
PassportRecord.IsMainPassport = PassportRow.MainPassport;
PassportRecord.Number = PassportRow.PassportNumber;
PassportRecord.PassportName = PassportRow.FullPassportName;
PassportRecord.NationalityCode = PassportRow.PassportNationalityCode;
// retrieve country name from country table
TCacheable CachePopulator = new TCacheable();
PCountryTable CountryTable =
(PCountryTable)CachePopulator.GetCacheableTable(TCacheableCommonTablesEnum.CountryList);
PCountryRow CountryRow =
(PCountryRow)CountryTable.Rows.Find(new object[] { PassportRow.PassportNationalityCode });
if (CountryRow != null)
{
PassportRecord.NationalityName = CountryRow.CountryName;
}
PassportRecord.TypeCode = PassportRow.PassportDetailsType;
// retrieve passport type name from type table
TPersonnelCacheable PersonnelCachePopulator = new TPersonnelCacheable();
PtPassportTypeTable TypeTable =
(PtPassportTypeTable)PersonnelCachePopulator.GetCacheableTable(TCacheablePersonTablesEnum.
PassportTypeList);
PtPassportTypeRow TypeRow =
(PtPassportTypeRow)TypeTable.Rows.Find(new object[] { PassportRow.PassportDetailsType });
if (TypeRow != null)
{
PassportRecord.TypeDescription = TypeRow.Description;
}
PassportRecord.DateOfIssue = PassportRow.DateOfIssue;
PassportRecord.PlaceOfIssue = PassportRow.PlaceOfIssue;
PassportRecord.DateOfExpiry = PassportRow.DateOfExpiration;
PassportRecord.PlaceOfBirth = PassportRow.PlaceOfBirth;
// set number and nationality in main record (only for main passport or if there is just one)
if (PassportRow.MainPassport || (PassportTable.Count == 1))
{
AFormDataPerson.PassportNumber = PassportRecord.Number;
AFormDataPerson.PassportNationality = PassportRecord.NationalityName;
AFormDataPerson.PassportNationalityCode = PassportRecord.NationalityCode;
AFormDataPerson.PassportName = PassportRecord.PassportName;
AFormDataPerson.PassportDateOfIssue = PassportRecord.DateOfIssue;
AFormDataPerson.PassportPlaceOfIssue = PassportRecord.PlaceOfIssue;
AFormDataPerson.PassportDateOfExpiry = PassportRecord.DateOfExpiry;
AFormDataPerson.PassportPlaceOfBirth = PassportRecord.PlaceOfBirth;
MainPassportFound = true;
}
// If the PassportName has not been set yet then make sure it is set on Person level from at least one passport
// (this will not be necessary any longer once the tick box for "Main Passport" is implemented)
if (!MainPassportFound)
{
AFormDataPerson.PassportName = PassportRecord.PassportName;
}
AFormDataPerson.AddPassport(PassportRecord);
}
}
}
// retrieve Language information
if (AFormLetterInfo.IsRetrievalRequested(TFormDataRetrievalSection.eLanguage))
{
PmPersonLanguageTable PersonLanguageTable;
TFormDataLanguage PersonLanguageRecord;
PersonLanguageTable = PmPersonLanguageAccess.LoadViaPPerson(APartnerKey, AReadTransaction);
foreach (PmPersonLanguageRow PersonLanguageRow in PersonLanguageTable.Rows)
{
PersonLanguageRecord = new TFormDataLanguage();
PersonLanguageRecord.Code = PersonLanguageRow.LanguageCode;
// retrieve language name from language table
TCacheable CachePopulator = new TCacheable();
PLanguageTable LanguageTable =
(PLanguageTable)CachePopulator.GetCacheableTable(TCacheableCommonTablesEnum.LanguageCodeList);
PLanguageRow LanguageRow = (PLanguageRow)LanguageTable.Rows.Find(new object[] { PersonLanguageRow.LanguageCode });
if (LanguageRow != null)
{
PersonLanguageRecord.Name = LanguageRow.LanguageDescription;
}
PersonLanguageRecord.Level = PersonLanguageRow.LanguageLevel.ToString();
// retrieve language level name from language level table
TPersonnelCacheable CachePopulatorPersonnel = new TPersonnelCacheable();
PtLanguageLevelTable LanguageLevelTable =
(PtLanguageLevelTable)CachePopulatorPersonnel.GetCacheableTable(TCacheablePersonTablesEnum.LanguageLevelList);
PtLanguageLevelRow LanguageLevelRow =
(PtLanguageLevelRow)LanguageLevelTable.Rows.Find(new object[] { PersonLanguageRow.LanguageLevel });
if (LanguageLevelRow != null)
{
PersonLanguageRecord.LevelDesc = LanguageLevelRow.LanguageLevelDescr;
}
PersonLanguageRecord.Comment = PersonLanguageRow.Comment;
AFormDataPerson.AddLanguage(PersonLanguageRecord);
}
}
// retrieve Skill information
if (AFormLetterInfo.IsRetrievalRequested(TFormDataRetrievalSection.eSkill))
{
PmPersonSkillTable PersonSkillTable;
TFormDataSkill PersonSkillRecord;
TFormDataDegree PersonDegreeRecord;
PersonSkillTable = PmPersonSkillAccess.LoadViaPPerson(APartnerKey, AReadTransaction);
foreach (PmPersonSkillRow PersonSkillRow in PersonSkillTable.Rows)
{
PersonSkillRecord = new TFormDataSkill();
PersonSkillRecord.Category = PersonSkillRow.SkillCategoryCode;
PersonSkillRecord.Description = PersonSkillRow.DescriptionEnglish;
// if no description in local language then use english
PersonSkillRecord.DescriptionLocalOrDefault = PersonSkillRow.DescriptionLocal;
if (PersonSkillRow.DescriptionLocal != "")
{
PersonSkillRecord.DescriptionLocalOrDefault = PersonSkillRow.DescriptionEnglish;
}
PersonSkillRecord.Level = PersonSkillRow.SkillLevel;
// retrieve skill level name from skill level table
TPersonnelCacheable CachePopulatorPersonnel = new TPersonnelCacheable();
PtSkillLevelTable SkillLevelTable =
(PtSkillLevelTable)CachePopulatorPersonnel.GetCacheableTable(TCacheablePersonTablesEnum.SkillLevelList);
PtSkillLevelRow SkillLevelRow =
(PtSkillLevelRow)SkillLevelTable.Rows.Find(new object[] { PersonSkillRow.SkillLevel });
if (SkillLevelRow != null)
{
PersonSkillRecord.LevelDesc = SkillLevelRow.Description;
}
PersonSkillRecord.YearsExp = PersonSkillRow.YearsOfExperience;
PersonSkillRecord.Professional = PersonSkillRow.ProfessionalSkill;
PersonSkillRecord.Degree = PersonSkillRow.Degree;
PersonSkillRecord.Comment = PersonSkillRow.Comment;
AFormDataPerson.AddSkill(PersonSkillRecord);
// now add a degree record if a degree is mentioned
if (!PersonSkillRow.IsDegreeNull() &&
(PersonSkillRow.Degree.Length > 0))
{
PersonDegreeRecord = new TFormDataDegree();
PersonDegreeRecord.Name = PersonSkillRow.Degree;
PersonDegreeRecord.Year = PersonSkillRow.YearOfDegree.ToString();
AFormDataPerson.AddDegree(PersonDegreeRecord);
}
}
}
// retrieve past work experience information
if (AFormLetterInfo.IsRetrievalRequested(TFormDataRetrievalSection.eWorkExperience))
{
TFormDataWorkExperience PersonExpRecord;
/*
* currently we don't include application records in the work experience data
*
* String UnitShortName;
* TPartnerClass UnitClass;
*
* // retrieve applications for short term events
* String SqlStmt = TDataBase.ReadSqlFile("Personnel.FormLetters.GetAppTravelDates.sql");
*
* OdbcParameter[] parameters = new OdbcParameter[1];
* parameters[0] = new OdbcParameter("PartnerKey", OdbcType.BigInt);
* parameters[0].Value = APartnerKey;
*
* DataTable travelData = DBAccess.GDBAccessObj.SelectDT(SqlStmt, "TravelDates", ReadTransaction, parameters);
*
* for (int i = 0; i < travelData.Rows.Count; i++)
* {
* PersonExpRecord = new TFormDataWorkExperience();
*
* if ((travelData.Rows[i][0]).GetType() == typeof(DateTime))
* {
* PersonExpRecord.StartDate = (DateTime?)travelData.Rows[i][0];
* }
*
* if ((travelData.Rows[i][1]).GetType() == typeof(DateTime))
* {
* PersonExpRecord.EndDate = (DateTime?)travelData.Rows[i][1];
* }
*
* PersonExpRecord.Organisation = "";
* PersonExpRecord.Role = "";
* PersonExpRecord.Category = "";
* PersonExpRecord.SameOrg = true;
* PersonExpRecord.SimilarOrg = true;
* PersonExpRecord.Comment = "";
*
* // check if unit exists and use unit name as location
* if (TPartnerServerLookups.GetPartnerShortName((Int64)travelData.Rows[i][2], out UnitShortName, out UnitClass))
* {
* PersonExpRecord.Location = UnitShortName;
* }
* else
* {
* PersonExpRecord.Location = travelData.Rows[i][3].ToString();
* }
*
* AFormDataPerson.AddWorkExperience(PersonExpRecord);
* }
*/
// retrieve actual past experience records
PmPastExperienceTable PersonExpTable;
PersonExpTable = PmPastExperienceAccess.LoadViaPPerson(APartnerKey, AReadTransaction);
PUnitTable UnitTable;
PUnitRow UnitRow;
foreach (PmPastExperienceRow PersonExpRow in PersonExpTable.Rows)
{
PersonExpRecord = new TFormDataWorkExperience();
PersonExpRecord.StartDate = PersonExpRow.StartDate;
PersonExpRecord.EndDate = PersonExpRow.EndDate;
PersonExpRecord.Location = PersonExpRow.PrevLocation;
PersonExpRecord.Organisation = PersonExpRow.OtherOrganisation;
PersonExpRecord.Role = PersonExpRow.PrevRole;
PersonExpRecord.Category = PersonExpRow.Category;
PersonExpRecord.SameOrg = PersonExpRow.PrevWorkHere;
PersonExpRecord.SimilarOrg = PersonExpRow.PrevWork;
PersonExpRecord.Comment = PersonExpRow.PastExpComments;
// check if previous experience came from an event with location set to event code
// -> in this case don't set event code as location but rather the event name
PUnitRow template = new PUnitTable().NewRowTyped(false);
template.OutreachCode = PersonExpRow.PrevLocation;
UnitTable = PUnitAccess.LoadUsingTemplate(template, AReadTransaction);
if (UnitTable.Rows.Count > 0)
{
UnitRow = (PUnitRow)UnitTable.Rows[0];
PersonExpRecord.Location = UnitRow.UnitName;
}
AFormDataPerson.AddWorkExperience(PersonExpRecord);
}
}
// retrieve Commitment information
if (AFormLetterInfo.IsRetrievalRequested(TFormDataRetrievalSection.eCommitment) ||
AFormLetterInfo.IsRetrievalRequested(TFormDataRetrievalSection.eFutureCommitment))
{
String FieldName;
TPartnerClass FieldPartnerClass;
PmStaffDataTable PersonCommitmentTable;
PmStaffDataRow PersonCommitmentRow;
TFormDataCommitment PersonCommitmentRecord;
PersonCommitmentTable = PmStaffDataAccess.LoadViaPPerson(APartnerKey, AReadTransaction);
PersonCommitmentTable.DefaultView.Sort = PmStaffDataTable.GetStartOfCommitmentDBName() + " DESC";
if (AFormLetterInfo.IsRetrievalRequested(TFormDataRetrievalSection.eCommitment))
{
foreach (DataRowView rv in PersonCommitmentTable.DefaultView)
{
PersonCommitmentRow = (PmStaffDataRow)rv.Row;
PersonCommitmentRecord = new TFormDataCommitment();
PersonCommitmentRecord.StartDate = PersonCommitmentRow.StartOfCommitment;
PersonCommitmentRecord.EndDate = PersonCommitmentRow.EndOfCommitment;
PersonCommitmentRecord.Status = PersonCommitmentRow.StatusCode;
PersonCommitmentRecord.ReceivingFieldKey = PersonCommitmentRow.ReceivingField.ToString("0000000000");
TPartnerServerLookups.GetPartnerShortName(PersonCommitmentRow.ReceivingField, out FieldName,
out FieldPartnerClass);
PersonCommitmentRecord.ReceivingFieldName = FieldName;
PersonCommitmentRecord.SendingFieldKey = PersonCommitmentRow.HomeOffice.ToString("0000000000");
TPartnerServerLookups.GetPartnerShortName(PersonCommitmentRow.HomeOffice, out FieldName,
out FieldPartnerClass);
PersonCommitmentRecord.SendingFieldName = FieldName;
PersonCommitmentRecord.RecruitingFieldKey = PersonCommitmentRow.OfficeRecruitedBy.ToString("0000000000");
TPartnerServerLookups.GetPartnerShortName(PersonCommitmentRow.OfficeRecruitedBy, out FieldName,
out FieldPartnerClass);
PersonCommitmentRecord.RecruitingFieldName = FieldName;
PersonCommitmentRecord.Comment = PersonCommitmentRow.StaffDataComments;
AFormDataPerson.AddCommitment(PersonCommitmentRecord);
}
}
if (AFormLetterInfo.IsRetrievalRequested(TFormDataRetrievalSection.eFutureCommitment))
{
foreach (DataRowView rv in PersonCommitmentTable.DefaultView)
{
PersonCommitmentRow = (PmStaffDataRow)rv.Row;
if (PersonCommitmentRow.StartOfCommitment >= DateTime.Today)
{
TPartnerServerLookups.GetPartnerShortName(PersonCommitmentRow.ReceivingField, out FieldName,
out FieldPartnerClass);
AFormDataPerson.FutureFieldName = FieldName;
AFormDataPerson.FutureCommitStartDate = PersonCommitmentRow.StartOfCommitment;
AFormDataPerson.FutureCommitEndDate = PersonCommitmentRow.EndOfCommitment;
// only use the first commitment (list is sorted by start date)
break;
}
}
}
}
}
}
public static TSubmitChangesResult SubmitChangesServerSide(ref IndividualDataTDS AInspectDS,
ref PartnerEditTDS APartnerEditInspectDS,
TDBTransaction ASubmitChangesTransaction,
out TVerificationResultCollection AVerificationResult)
{
TSubmitChangesResult SubmissionResult;
PmJobAssignmentTable PmJobAssignmentTableSubmit;
AVerificationResult = new TVerificationResultCollection();
if (AInspectDS != null)
{
SubmissionResult = TSubmitChangesResult.scrOK;
// Job Assignments: make sure that jobs exist for assignments
if (AInspectDS.Tables.Contains(PmJobAssignmentTable.GetTableName()) &&
(AInspectDS.PmJobAssignment.Rows.Count > 0))
{
UmJobTable JobTableTemp = new UmJobTable();
UmJobTable JobTableSubmit = new UmJobTable();
UmJobRow JobRow;
PmJobAssignmentTableSubmit = AInspectDS.PmJobAssignment;
// every job_assignment_row needs to have a row that it references in um_job
foreach (PmJobAssignmentRow JobAssignmentRow in PmJobAssignmentTableSubmit.Rows)
{
if (JobAssignmentRow.RowState != DataRowState.Deleted)
{
JobTableTemp = UmJobAccess.LoadByPrimaryKey(JobAssignmentRow.UnitKey,
JobAssignmentRow.PositionName,
JobAssignmentRow.PositionScope,
JobAssignmentRow.JobKey,
ASubmitChangesTransaction);
// if no corresponding job record found then we need to create one
// (job key was already set on client side to new value so merging back to the
// client does not cause problems because of primary key change)
if (JobTableTemp.Count == 0)
{
JobRow = (UmJobRow)JobTableSubmit.NewRow();
JobRow.UnitKey = JobAssignmentRow.UnitKey;
JobRow.PositionName = JobAssignmentRow.PositionName;
JobRow.PositionScope = JobAssignmentRow.PositionScope;
JobRow.JobKey = JobAssignmentRow.JobKey;
JobRow.FromDate = JobAssignmentRow.FromDate;
JobRow.ToDate = JobAssignmentRow.ToDate;
JobRow.CommitmentPeriod = "None";
JobRow.TrainingPeriod = "None";
// Need to update the JobKey field in job assignment table record from job record
JobAssignmentRow.JobKey = JobRow.JobKey;
JobTableSubmit.Rows.Add(JobRow);
}
else
{
// job record exists: in this case we need to update JobKey in
// the Job Assignment Record from Job Row
JobAssignmentRow.JobKey = ((UmJobRow)JobTableTemp.Rows[0]).JobKey;
}
}
}
// submit table with newly created jobs
if (JobTableSubmit.Rows.Count > 0)
{
UmJobAccess.SubmitChanges(JobTableSubmit, ASubmitChangesTransaction);
}
}
// now submit the whole dataset at once
IndividualDataTDSAccess.SubmitChanges(AInspectDS);
// Need to merge tables back into APartnerEditInspectDS so the updated s_modification_id_t is returned
// correctly to the Partner Edit.
// Unfortunately this can't be done simply by using merge method of the dataset since they are two different
// types but has to be done per table.
if (AInspectDS.Tables.Contains(PmSpecialNeedTable.GetTableName()) &&
(AInspectDS.PmSpecialNeed.Rows.Count > 0))
{
APartnerEditInspectDS.Tables[PmSpecialNeedTable.GetTableName()].Merge(AInspectDS.PmSpecialNeed);
}
if (AInspectDS.Tables.Contains(PmPersonAbilityTable.GetTableName()) &&
(AInspectDS.PmPersonAbility.Rows.Count > 0))
{
APartnerEditInspectDS.Tables[PmPersonAbilityTable.GetTableName()].Merge(AInspectDS.PmPersonAbility);
}
if (AInspectDS.Tables.Contains(PmPassportDetailsTable.GetTableName()) &&
(AInspectDS.PmPassportDetails.Rows.Count > 0))
{
APartnerEditInspectDS.Tables[PmPassportDetailsTable.GetTableName()].Merge(AInspectDS.PmPassportDetails);
}
if (AInspectDS.Tables.Contains(PmPersonalDataTable.GetTableName()) &&
(AInspectDS.PmPersonalData.Rows.Count > 0))
{
APartnerEditInspectDS.Tables[PmPersonalDataTable.GetTableName()].Merge(AInspectDS.PmPersonalData);
}
if (AInspectDS.Tables.Contains(PmPersonLanguageTable.GetTableName()) &&
(AInspectDS.PmPersonLanguage.Rows.Count > 0))
{
APartnerEditInspectDS.Tables[PmPersonLanguageTable.GetTableName()].Merge(AInspectDS.PmPersonLanguage);
}
if (AInspectDS.Tables.Contains(PmPersonEvaluationTable.GetTableName()) &&
(AInspectDS.PmPersonEvaluation.Rows.Count > 0))
{
APartnerEditInspectDS.Tables[PmPersonEvaluationTable.GetTableName()].Merge(AInspectDS.PmPersonEvaluation);
}
if (AInspectDS.Tables.Contains(PmStaffDataTable.GetTableName()) &&
(AInspectDS.PmStaffData.Rows.Count > 0))
{
APartnerEditInspectDS.Tables[PmStaffDataTable.GetTableName()].Merge(AInspectDS.PmStaffData);
}
if (AInspectDS.Tables.Contains(PmPersonSkillTable.GetTableName()) &&
(AInspectDS.PmPersonSkill.Rows.Count > 0))
{
APartnerEditInspectDS.Tables[PmPersonSkillTable.GetTableName()].Merge(AInspectDS.PmPersonSkill);
}
if (AInspectDS.Tables.Contains(PmPastExperienceTable.GetTableName()) &&
(AInspectDS.PmPastExperience.Rows.Count > 0))
{
APartnerEditInspectDS.Tables[PmPastExperienceTable.GetTableName()].Merge(AInspectDS.PmPastExperience);
}
if (AInspectDS.Tables.Contains(PmDocumentTable.GetTableName()) &&
(AInspectDS.PmDocument.Rows.Count > 0))
{
APartnerEditInspectDS.Tables[PmDocumentTable.GetTableName()].Merge(AInspectDS.PmDocument);
}
if (AInspectDS.Tables.Contains(PmJobAssignmentTable.GetTableName()) &&
(AInspectDS.PmJobAssignment.Rows.Count > 0))
{
APartnerEditInspectDS.Tables[PmJobAssignmentTable.GetTableName()].Merge(AInspectDS.PmJobAssignment);
}
if (AInspectDS.Tables.Contains(PmGeneralApplicationTable.GetTableName()) &&
(AInspectDS.PmGeneralApplication.Rows.Count > 0))
{
APartnerEditInspectDS.Tables[PmGeneralApplicationTable.GetTableName()].Merge(AInspectDS.PmGeneralApplication);
}
if (AInspectDS.Tables.Contains(PmShortTermApplicationTable.GetTableName()) &&
(AInspectDS.PmShortTermApplication.Rows.Count > 0))
{
APartnerEditInspectDS.Tables[PmShortTermApplicationTable.GetTableName()].Merge(AInspectDS.PmShortTermApplication);
}
if (AInspectDS.Tables.Contains(PmYearProgramApplicationTable.GetTableName()) &&
(AInspectDS.PmYearProgramApplication.Rows.Count > 0))
{
APartnerEditInspectDS.Tables[PmYearProgramApplicationTable.GetTableName()].Merge(AInspectDS.PmYearProgramApplication);
}
}
else
{
TLogging.LogAtLevel(8, "TIndividualDataWebConnector.SubmitChangesServerSide: AInspectDS = nil!");
SubmissionResult = TSubmitChangesResult.scrNothingToBeSaved;
}
return(SubmissionResult);
}
/// <summary>
/// Evaluates if a partner has a receiving field assigned.
/// If there is a receiving field the FInvisibleRowCounter is increased because in the Receiving
/// Field Report this partner is not displayed in the "NO FIELD" list.
/// </summary>
/// <param name="ASituation"></param>
/// <param name="APartnerKey"></param>
/// <param name="ATargetFieldList"></param>
/// <returns>True if there is a receiving field. Otherwise false.</returns>
public bool HasAttendeeReceivingField(ref TRptSituation ASituation, long APartnerKey,
String ATargetFieldList)
{
PPersonTable PersonTable;
bool ReturnValue = false;
PmStaffDataTable StaffDataTable = new PmStaffDataTable();
PmStaffDataRow TemplateRow = (PmStaffDataRow)StaffDataTable.NewRow();
TemplateRow.PartnerKey = APartnerKey;
// 1. check the staff data if this partner has the same target / receiving field
StaffDataTable = PmStaffDataAccess.LoadUsingTemplate(TemplateRow, ASituation.GetDatabaseConnection().Transaction);
foreach (PmStaffDataRow StaffDataRow in StaffDataTable.Rows)
{
if ((StaffDataRow.StartOfCommitment < DateTime.Today)
&& (StaffDataRow.IsEndOfCommitmentNull()
|| (!StaffDataRow.IsEndOfCommitmentNull()
&& (StaffDataRow.EndOfCommitment > DateTime.Today)))
&& !StaffDataRow.IsReceivingFieldNull())
{
if (ATargetFieldList.Contains(StaffDataRow.ReceivingField.ToString()))
{
ReturnValue = true;
}
}
}
// 2. check the person gift destination
PersonTable = PPersonAccess.LoadByPrimaryKey(APartnerKey, ASituation.GetDatabaseConnection().Transaction);
if ((ReturnValue == false)
&& (PersonTable.Rows.Count > 0))
{
PPersonRow PersonRow = (PPersonRow)PersonTable.Rows[0];
PPartnerGiftDestinationTable GiftDestinationTable =
PPartnerGiftDestinationAccess.LoadViaPPartner(PersonRow.FamilyKey, ASituation.GetDatabaseConnection().Transaction);
if (GiftDestinationTable.Rows.Count > 0)
{
foreach (PPartnerGiftDestinationRow Row in GiftDestinationTable.Rows)
{
// check if the gift destination is currently active
if ((Row.DateEffective <= DateTime.Today)
&& (Row.IsDateExpiresNull() || ((Row.DateExpires >= DateTime.Today) && (Row.DateExpires != Row.DateEffective))))
{
ReturnValue = true;
}
}
}
}
if (ReturnValue == true)
{
// Hide the line from the output
FInvisibleRowCounter++;
ASituation.GetParameters().Add("DONTDISPLAYROW", new TVariant(true), -1, -1, null, null, ReportingConsts.CALCULATIONPARAMETERS);
}
else
{
ASituation.GetParameters().Add("DONTDISPLAYROW", new TVariant(false), -1, -1, null, null, ReportingConsts.CALCULATIONPARAMETERS);
}
return ReturnValue;
}
/// <summary>
/// export all details of workers in this year
/// </summary>
public static void Export(string AOutputPath,
char ACSVSeparator,
string ANewLine,
Int64 SiteKey,
Int32 AYear)
{
string filename = Path.GetFullPath(Path.Combine(AOutputPath, "angestellte.csv"));
Console.WriteLine("Writing file: " + filename);
StringBuilder sb = new StringBuilder();
PmStaffDataTable staffdata = new PmStaffDataTable();
PPartnerTable partners = new PPartnerTable();
partners.Constraints.Clear();
PPersonTable persons = new PPersonTable();
persons.Constraints.Clear();
TDBTransaction Transaction = new TDBTransaction();
DBAccess.ReadTransaction(ref Transaction,
delegate
{
TDataBase db = Transaction.DataBaseObj;
// get all partners with a commitment period for this date range
// ignore non-native workers. field must be home office, or receiving field
string sql =
String.Format(
"SELECT * FROM PUB_pm_staff_data s, PUB_p_partner p, PUB_p_person per " +
"WHERE p.p_partner_key_n = s.p_partner_key_n " +
"AND (s.pm_home_office_n = ? OR pm_receiving_field_office_n = ? OR pm_receiving_field_n = ?) " +
"AND per.p_partner_key_n = s.p_partner_key_n " +
// start of commitment during this year
"AND ((pm_start_of_commitment_d BETWEEN ? AND ?) " +
// start of commitment before this year, end of commitment null or during/after this year
" OR (pm_start_of_commitment_d < ? AND (pm_start_of_commitment_d IS NULL OR pm_start_of_commitment_d >= ?)))");
List <OdbcParameter> Parameters = new List <OdbcParameter>();
OdbcParameter param;
param = new OdbcParameter("field", OdbcType.Numeric);
param.Value = SiteKey;
Parameters.Add(param);
param = new OdbcParameter("field", OdbcType.Numeric);
param.Value = SiteKey;
Parameters.Add(param);
param = new OdbcParameter("field", OdbcType.Numeric);
param.Value = SiteKey;
Parameters.Add(param);
param = new OdbcParameter("startdate", OdbcType.DateTime);
param.Value = new DateTime(AYear, 1, 1);
Parameters.Add(param);
param = new OdbcParameter("enddate", OdbcType.DateTime);
param.Value = new DateTime(AYear, 12, 31);
Parameters.Add(param);
param = new OdbcParameter("startdate", OdbcType.DateTime);
param.Value = new DateTime(AYear, 1, 1);
Parameters.Add(param);
param = new OdbcParameter("startdate", OdbcType.DateTime);
param.Value = new DateTime(AYear, 1, 1);
Parameters.Add(param);
db.SelectDT(staffdata, sql.Replace("SELECT *", "SELECT s.*"), Transaction, Parameters.ToArray(), 0, 0);
db.SelectDT(partners, sql.Replace("SELECT *", "SELECT p.*"), Transaction, Parameters.ToArray(), 0, 0);
db.SelectDT(persons, sql.Replace("SELECT *", "SELECT per.*"), Transaction, Parameters.ToArray(), 0, 0);
});
foreach (PmStaffDataRow staff in staffdata.Rows)
{
partners.DefaultView.Sort = "p_partner_key_n";
persons.DefaultView.Sort = "p_partner_key_n";
PPartnerRow partner = (PPartnerRow)partners.DefaultView.FindRows(new object[] { staff.PartnerKey })[0].Row;
PPersonRow person = (PPersonRow)persons.DefaultView.FindRows(new object[] { staff.PartnerKey })[0].Row;
sb.Append(StringHelper.StrMerge(
new string[] {
partner.PartnerKey.ToString(),
partner.PartnerShortName.ToString(),
person.DateOfBirth.HasValue?person.DateOfBirth.Value.ToString("yyyyMMdd"):String.Empty,
partner.PartnerKey.ToString()
}, ACSVSeparator));
sb.Append(ANewLine);
}
StreamWriter sw = new StreamWriter(filename, false, Encoding.GetEncoding(1252));
sw.Write(sb.ToString());
sw.Close();
}
/// <summary>
/// export all details of workers in this year
/// </summary>
public static void Export(string AOutputPath,
char ACSVSeparator,
string ANewLine,
Int64 SiteKey,
Int32 AYear)
{
string filename = Path.GetFullPath(Path.Combine(AOutputPath, "angestellte.csv"));
Console.WriteLine("Writing file: " + filename);
StringBuilder sb = new StringBuilder();
PmStaffDataTable staffdata = new PmStaffDataTable();
PPartnerTable partners = new PPartnerTable();
partners.Constraints.Clear();
PPersonTable persons = new PPersonTable();
persons.Constraints.Clear();
TDBTransaction Transaction = null;
DBAccess.GDBAccessObj.BeginAutoReadTransaction(IsolationLevel.ReadCommitted, ref Transaction,
delegate
{
// get all partners with a commitment period for this date range
// ignore non-native workers. field must be home office, or receiving field
string sql =
String.Format(
"SELECT * FROM PUB_pm_staff_data s, PUB_p_partner p, PUB_p_person per " +
"WHERE p.p_partner_key_n = s.p_partner_key_n " +
"AND (s.pm_home_office_n = ? OR pm_receiving_field_office_n = ? OR pm_receiving_field_n = ?) " +
"AND per.p_partner_key_n = s.p_partner_key_n " +
// start of commitment during this year
"AND ((pm_start_of_commitment_d BETWEEN ? AND ?) " +
// start of commitment before this year, end of commitment null or during/after this year
" OR (pm_start_of_commitment_d < ? AND (pm_start_of_commitment_d IS NULL OR pm_start_of_commitment_d >= ?)))");
List <OdbcParameter>Parameters = new List <OdbcParameter>();
OdbcParameter param;
param = new OdbcParameter("field", OdbcType.Numeric);
param.Value = SiteKey;
Parameters.Add(param);
param = new OdbcParameter("field", OdbcType.Numeric);
param.Value = SiteKey;
Parameters.Add(param);
param = new OdbcParameter("field", OdbcType.Numeric);
param.Value = SiteKey;
Parameters.Add(param);
param = new OdbcParameter("startdate", OdbcType.DateTime);
param.Value = new DateTime(AYear, 1, 1);
Parameters.Add(param);
param = new OdbcParameter("enddate", OdbcType.DateTime);
param.Value = new DateTime(AYear, 12, 31);
Parameters.Add(param);
param = new OdbcParameter("startdate", OdbcType.DateTime);
param.Value = new DateTime(AYear, 1, 1);
Parameters.Add(param);
param = new OdbcParameter("startdate", OdbcType.DateTime);
param.Value = new DateTime(AYear, 1, 1);
Parameters.Add(param);
DBAccess.GDBAccessObj.SelectDT(staffdata, sql.Replace("SELECT *", "SELECT s.*"), Transaction, Parameters.ToArray(), 0, 0);
DBAccess.GDBAccessObj.SelectDT(partners, sql.Replace("SELECT *", "SELECT p.*"), Transaction, Parameters.ToArray(), 0, 0);
DBAccess.GDBAccessObj.SelectDT(persons, sql.Replace("SELECT *", "SELECT per.*"), Transaction, Parameters.ToArray(), 0, 0);
});
foreach (PmStaffDataRow staff in staffdata.Rows)
{
partners.DefaultView.Sort = "p_partner_key_n";
persons.DefaultView.Sort = "p_partner_key_n";
PPartnerRow partner = (PPartnerRow)partners.DefaultView.FindRows(new object[] { staff.PartnerKey })[0].Row;
PPersonRow person = (PPersonRow)persons.DefaultView.FindRows(new object[] { staff.PartnerKey })[0].Row;
sb.Append(StringHelper.StrMerge(
new string[] {
partner.PartnerKey.ToString(),
partner.PartnerShortName.ToString(),
person.DateOfBirth.HasValue?person.DateOfBirth.Value.ToString("yyyyMMdd"):String.Empty,
partner.PartnerKey.ToString()
}, ACSVSeparator));
sb.Append(ANewLine);
}
StreamWriter sw = new StreamWriter(filename, false, Encoding.GetEncoding(1252));
sw.Write(sb.ToString());
sw.Close();
}
