private String PartnerShortName(Int64 partnerKey)
{
if (partnerKey > 0)
{
// Get Partner ShortName
PPartnerTable pt = null;
TDBTransaction Transaction = null;
DBAccess.GDBAccessObj.GetNewOrExistingAutoReadTransaction(IsolationLevel.ReadCommitted,
TEnforceIsolationLevel.eilMinimum,
ref Transaction,
delegate
{
pt =
PPartnerAccess.LoadByPrimaryKey(partnerKey,
StringHelper.InitStrArr(new String[] { PPartnerTable.GetPartnerShortNameDBName() }),
Transaction, null, 0, 0);
});
if (pt.Rows.Count == 1)
{
return(pt[0].PartnerShortName);
}
}
return("");
}
public static bool GetPartnerReceiptingInfo(
Int64 APartnerKey,
out bool AReceiptEachGift,
out String AReceiptLetterFrequency,
out bool AEmailGiftStatement,
out bool AAnonymousDonor)
{
TDBTransaction ReadTransaction = null;
PPartnerTable PartnerTbl = null;
DBAccess.GDBAccessObj.GetNewOrExistingAutoReadTransaction(IsolationLevel.ReadCommitted,
TEnforceIsolationLevel.eilMinimum, ref ReadTransaction,
delegate
{
PartnerTbl = PPartnerAccess.LoadByPrimaryKey(APartnerKey, ReadTransaction);
});
if (PartnerTbl.Rows.Count > 0)
{
PPartnerRow Row = PartnerTbl[0];
AReceiptEachGift = Row.ReceiptEachGift;
AReceiptLetterFrequency = Row.ReceiptLetterFrequency;
AEmailGiftStatement = Row.EmailGiftStatement;
AAnonymousDonor = Row.AnonymousDonor;
return(true);
}
else
{
AReceiptEachGift = false;
AReceiptLetterFrequency = "";
AEmailGiftStatement = false;
AAnonymousDonor = false;
return(false);
}
}
private void LoadEventListData()
{
string CheckedMember = "CHECKED";
string ValueMember = PPartnerTable.GetPartnerKeyDBName();
string DisplayMember = PPartnerTable.GetPartnerShortNameDBName();
string EventCodeMember = PUnitTable.GetOutreachCodeDBName();
DataTable Table;
Table = TRemote.MPartner.Partner.WebConnectors.GetEventUnits
(ucoFilter.IncludeConferenceUnits, ucoFilter.IncludeOutreachUnits,
ucoFilter.NameFilter, false, ucoFilter.CurrentAndFutureEventsOnly);
DataView view = new DataView(Table);
DataTable NewTable = view.ToTable(true, new string[] { DisplayMember, ValueMember, EventCodeMember });
NewTable.Columns.Add(new DataColumn(CheckedMember, typeof(bool)));
clbEvent.Columns.Clear();
clbEvent.AddCheckBoxColumn("", NewTable.Columns[CheckedMember], 17, false);
clbEvent.AddTextColumn(Catalog.GetString("Event Name"), NewTable.Columns[DisplayMember], 240);
clbEvent.AddPartnerKeyColumn(Catalog.GetString("Partner Key"), NewTable.Columns[ValueMember], 100);
// outreach/event code column only needed in case of displaying Outreaches
if (ucoFilter.IncludeOutreachUnits)
{
clbEvent.AddTextColumn(Catalog.GetString("Event Code"), NewTable.Columns[EventCodeMember], 110);
}
clbEvent.DataBindGrid(NewTable, DisplayMember, CheckedMember, ValueMember, false, true, false);
//TODO: only temporarily until settings file exists
clbEvent.SetCheckedStringList("");
}
private DataTable GetInstalledSitesListTable(TDBTransaction AReadTransaction, string ATableName)
{
// Used eg. in New Partner Dialog.
StringCollection RequiredColumns = new StringCollection();
RequiredColumns.Add(PPartnerLedgerTable.GetPartnerKeyDBName());
PPartnerLedgerTable TmpInstalledSitesDT = PPartnerLedgerAccess.LoadAll(RequiredColumns, AReadTransaction, null, 0, 0);
if (TmpInstalledSitesDT.Rows.Count != 0)
{
TmpInstalledSitesDT.Columns.Remove(PPartnerLedgerTable.GetLastPartnerIdDBName());
TmpInstalledSitesDT.Columns.Add(PPartnerTable.GetPartnerShortNameDBName(), System.Type.GetType("System.String"));
RequiredColumns = new StringCollection();
RequiredColumns.Add(PPartnerTable.GetPartnerShortNameDBName());
for (int Counter = 0; Counter <= TmpInstalledSitesDT.Rows.Count - 1; Counter += 1)
{
PPartnerTable PartnerDT = PPartnerAccess.LoadByPrimaryKey(
TmpInstalledSitesDT[Counter].PartnerKey,
RequiredColumns,
AReadTransaction,
null,
0,
0);
TmpInstalledSitesDT[Counter][PPartnerTable.GetPartnerShortNameDBName()] = PartnerDT[0].PartnerShortName;
}
}
return(TmpInstalledSitesDT);
}
/// <summary>
/// returns the site name of the current site key,
/// which is stored in s_system_parameter
///
/// </summary>
/// <returns>void</returns>
private String GetSiteName()
{
String ReturnValue = "";
string strSql;
DataTable tab;
long SiteKey = -1;
PPartnerTable PartnerTable;
strSql = "SELECT PUB_s_system_defaults.s_default_value_c " + "FROM PUB_s_system_defaults " +
"WHERE PUB_s_system_defaults.s_default_code_c = 'SiteKey'";
tab = situation.GetDatabaseConnection().SelectDT(strSql, "table", situation.GetDatabaseConnection().Transaction);
if (tab.Rows.Count > 0)
{
String SiteKeyString = Convert.ToString(tab.Rows[0]["s_default_value_c"]);
try
{
SiteKey = Convert.ToInt64(SiteKeyString);
}
catch (Exception)
{
SiteKey = -1;
}
}
PartnerTable = PPartnerAccess.LoadByPrimaryKey(SiteKey, situation.GetDatabaseConnection().Transaction);
if (PartnerTable.Rows.Count > 0)
{
ReturnValue = (String)PartnerTable.Rows[0][PPartnerTable.GetPartnerShortNameDBName()];
}
return(ReturnValue);
}
private void InitUserControlsManually()
{
// set controls in filter to default values
ucoFilter.InitialiseUserControl();
// Hook up EventFilterChanged Event to be able to react to changed filter
ucoFilter.EventFilterChanged += new TEventHandlerEventFilterChanged(this.EventFilterChanged);
//grdEvent.AutoSizeCells();
// now the filter is initialized we can load the initial data
LoadEventListData();
grdEvent.Columns.Clear();
grdEvent.AddTextColumn("Event Name", FEventTable.Columns[PPartnerTable.GetPartnerShortNameDBName()]);
grdEvent.AddTextColumn("Event Code", FEventTable.Columns[PUnitTable.GetOutreachCodeDBName()]);
grdEvent.AddTextColumn("Country", FEventTable.Columns[PCountryTable.GetCountryNameDBName()]);
grdEvent.AddDateColumn("Start Date", FEventTable.Columns[PPartnerLocationTable.GetDateEffectiveDBName()]);
grdEvent.AddDateColumn("End Date", FEventTable.Columns[PPartnerLocationTable.GetDateGoodUntilDBName()]);
grdEvent.AddPartnerKeyColumn("Event Key", FEventTable.Columns[PPartnerTable.GetPartnerKeyDBName()]);
grdEvent.AddTextColumn("Event Type", FEventTable.Columns[PUnitTable.GetUnitTypeCodeDBName()]);
FEventTable.DefaultView.AllowDelete = false;
FEventTable.DefaultView.AllowEdit = false;
FEventTable.DefaultView.AllowNew = false;
grdEvent.Selection.EnableMultiSelection = false;
grdEvent.AutoResizeGrid();
grdEvent.Selection.SelectRow(1, true);
}
} // ExportAllGiftBatchData
private String PartnerShortName(Int64 partnerKey)
{
if (partnerKey > 0)
{
// Get Partner ShortName
PPartnerTable pt = null;
TDBTransaction Transaction = new TDBTransaction();
TDataBase db = DBAccess.Connect("PartnerShortName");
db.ReadTransaction(
ref Transaction,
delegate
{
pt =
PPartnerAccess.LoadByPrimaryKey(partnerKey,
StringHelper.InitStrArr(new String[] { PPartnerTable.GetPartnerShortNameDBName() }),
Transaction, null, 0, 0);
});
if (pt.Rows.Count == 1)
{
return(pt[0].PartnerShortName);
}
}
return("");
}
private void Filter(System.Object sender, EventArgs e)
{
DataView MyDataView = FMainDS.PcConference.DefaultView;
String Filter = "";
if (!string.IsNullOrEmpty(txtConference.Text))
{
Filter += FMainDS.PcConference.Columns[PPartnerTable.GetPartnerShortNameDBName()] + " LIKE '" + "%" + txtConference.Text + "%'";
}
if (!string.IsNullOrEmpty(txtPrefix.Text))
{
if (Filter != "")
{
Filter += " AND ";
}
Filter += FMainDS.PcConference.Columns[PcConferenceTable.GetOutreachPrefixDBName()] + " LIKE '" + txtPrefix.Text + "%'";
}
MyDataView.RowFilter = Filter;
grdConferences.DataSource = new DevAge.ComponentModel.BoundDataView(MyDataView);
UpdateRecordNumberDisplay();
}
public static PPartnerTable SharedBankAccountPartners(int ABankingDetailsKey, long APartnerKey)
{
PPartnerTable PartnerTable = new PPartnerTable();
TDBTransaction Transaction = null;
DBAccess.GDBAccessObj.BeginAutoReadTransaction(IsolationLevel.ReadCommitted,
ref Transaction,
delegate
{
PPartnerBankingDetailsTable PartnerBankingDetailsTable = PPartnerBankingDetailsAccess.LoadViaPBankingDetails(ABankingDetailsKey,
Transaction);
foreach (PPartnerBankingDetailsRow Row in PartnerBankingDetailsTable.Rows)
{
// if record exists with a different partner key then the Bank Account is shared
if (Row.PartnerKey != APartnerKey)
{
PPartnerRow PartnerRow = (PPartnerRow)PPartnerAccess.LoadByPrimaryKey(Row.PartnerKey, Transaction).Rows[0];
PPartnerRow NewRow = PartnerTable.NewRowTyped(false);
NewRow.PartnerKey = Row.PartnerKey;
NewRow.PartnerShortName = PartnerRow.PartnerShortName;
PartnerTable.Rows.Add(NewRow);
}
}
});
return(PartnerTable);
}
private void LoadDataGrid(bool AFirstTime)
{
FMainDS.PcConference.Clear();
FMainDS.PPartner.Clear();
FMainDS.Merge(TRemote.MConference.WebConnectors.GetConferences("", ""));
if (FMainDS.PcConference.Rows.Count == FMainDS.PPartner.Rows.Count)
{
if (AFirstTime)
{
FMainDS.PcConference.Columns.Add(PPartnerTable.GetPartnerShortNameDBName(), Type.GetType("System.String"));
FMainDS.PcConference.DefaultView.AllowNew = false;
}
for (int Counter = 0; Counter < FMainDS.PcConference.Rows.Count; ++Counter)
{
FMainDS.PcConference.Rows[Counter][PPartnerTable.GetPartnerShortNameDBName()] =
FMainDS.PPartner.Rows[Counter][PPartnerTable.GetPartnerShortNameDBName()];
}
}
// sort order for grid
DataView MyDataView = FMainDS.PcConference.DefaultView;
MyDataView.Sort = "p_partner_short_name_c ASC";
grdConferences.DataSource = new DevAge.ComponentModel.BoundDataView(MyDataView);
UpdateRecordNumberDisplay();
}
/// <summary>
/// Get the name for this Ledger
/// </summary>
public static string GetLedgerName(int ALedgerNumber)
{
#region Validate Arguments
if (ALedgerNumber <= 0)
{
throw new EFinanceSystemInvalidLedgerNumberException(String.Format(Catalog.GetString(
"Function:{0} - The Ledger number must be greater than 0!"),
Utilities.GetMethodName(true)), ALedgerNumber);
}
#endregion Validate Arguments
String ReturnValue = string.Empty;
TDBTransaction ReadTransaction = null;
DBAccess.GDBAccessObj.GetNewOrExistingAutoReadTransaction(IsolationLevel.ReadCommitted,
TEnforceIsolationLevel.eilMinimum,
ref ReadTransaction,
delegate
{
String strSql = "SELECT p_partner_short_name_c FROM PUB_a_ledger, PUB_p_partner WHERE a_ledger_number_i=" +
ALedgerNumber + " AND PUB_a_ledger.p_partner_key_n = PUB_p_partner.p_partner_key_n";
DataTable tab = DBAccess.GDBAccessObj.SelectDT(strSql, "GetLedgerName_TempTable", ReadTransaction);
if (tab.Rows.Count > 0)
{
ReturnValue = Convert.ToString(tab.Rows[0][PPartnerTable.GetPartnerShortNameDBName()]); //"p_partner_short_name_c"
}
});
return(ReturnValue);
}
/// <summary>
/// Checks for the existance of a Partner.
/// </summary>
/// <param name="APartnerKey">PartnerKey of the Partner to check for.</param>
/// <param name="AMustNotBeMergedPartner">Set to true to check whether the Partner
/// must not be a Merged Partner.</param>
/// <returns>An instance of PPartnerRow if the Partner exists (taking AMustNotBeMergedPartner into consideration),
/// otherwise null.</returns>
/// <param name="ADataBase">An instantiated <see cref="TDataBase" /> object, or null (default = null). If null
/// gets passed then the Method executes DB commands with a new Database connection</param>
public static PPartnerRow CheckPartnerExists2(Int64 APartnerKey, bool AMustNotBeMergedPartner,
TDataBase ADataBase = null)
{
PPartnerRow ReturnValue = null;
TDBTransaction ReadTransaction = new TDBTransaction();
PPartnerTable PartnerTable = null;
if (APartnerKey != 0)
{
TDataBase db = DBAccess.Connect("CheckPartnerExists2", ADataBase);
db.ReadTransaction(ref ReadTransaction,
delegate
{
PartnerTable = PPartnerAccess.LoadByPrimaryKey(APartnerKey, ReadTransaction);
});
if (PartnerTable.Rows.Count != 0)
{
if (AMustNotBeMergedPartner)
{
if (SharedTypes.StdPartnerStatusCodeStringToEnum(
PartnerTable[0].StatusCode) != TStdPartnerStatusCode.spscMERGED)
{
ReturnValue = PartnerTable[0];
}
}
else
{
ReturnValue = PartnerTable[0];
}
}
}
return(ReturnValue);
}
private DataTable GetInstalledSitesListTable(TDBTransaction AReadTransaction, string ATableName)
{
// Used eg. in New Partner Dialog.
StringCollection RequiredColumns = new StringCollection();
RequiredColumns.Add(PPartnerLedgerTable.GetPartnerKeyDBName());
PPartnerLedgerTable TmpInstalledSitesDT = PPartnerLedgerAccess.LoadAll(RequiredColumns, AReadTransaction, null, 0, 0);
if (TmpInstalledSitesDT.Rows.Count != 0)
{
TmpInstalledSitesDT.Columns.Remove(PPartnerLedgerTable.GetLastPartnerIdDBName());
TmpInstalledSitesDT.Columns.Add(PPartnerTable.GetPartnerShortNameDBName(), System.Type.GetType("System.String"));
RequiredColumns = new StringCollection();
RequiredColumns.Add(PPartnerTable.GetPartnerShortNameDBName());
for (int Counter = 0; Counter <= TmpInstalledSitesDT.Rows.Count - 1; Counter += 1)
{
PPartnerTable PartnerDT = PPartnerAccess.LoadByPrimaryKey(
TmpInstalledSitesDT[Counter].PartnerKey,
RequiredColumns,
AReadTransaction,
null,
0,
0);
TmpInstalledSitesDT[Counter][PPartnerTable.GetPartnerShortNameDBName()] = PartnerDT[0].PartnerShortName;
}
}
// As part of bug 5556 testing, checked if this DataTable contained any Added rows. It didn't, so there isn't an issue here.
// Findings: DataTable summary: 1 rows; 0 Added, 0 deleted, 0 detached, 1 modified, 0 unchanged. Table name PPartnerLedger
return(TmpInstalledSitesDT);
}
private PPartnerRow RetrieveDonorRow(long APartnerKey)
{
if (APartnerKey == 0)
{
return(null);
}
// find PPartnerRow from dataset
PPartnerRow DonorRow = (PPartnerRow)FMainDS.DonorPartners.Rows.Find(new object[] { APartnerKey });
// if PPartnerRow cannot be found, load it from db
if ((DonorRow == null) || (DonorRow[PPartnerTable.GetReceiptEachGiftDBName()] == DBNull.Value))
{
PPartnerTable PartnerTable = TRemote.MFinance.Gift.WebConnectors.LoadPartnerData(APartnerKey);
if ((PartnerTable == null) || (PartnerTable.Rows.Count == 0))
{
// invalid partner
return(null);
}
else
{
FMainDS.DonorPartners.Merge(PartnerTable);
}
DonorRow = PartnerTable[0];
}
return(DonorRow);
}
public void TestSaveNewPartnerWithExistingLocation()
{
TDataBase db = DBAccess.Connect("TestSaveNewPartnerWithExistingLocation");
TDBTransaction Transaction = db.BeginTransaction(IsolationLevel.Serializable);
TPartnerEditUIConnector connector = new TPartnerEditUIConnector(db);
PartnerEditTDS MainDS = new PartnerEditTDS();
PPartnerRow PartnerRow = TCreateTestPartnerData.CreateNewFamilyPartner(MainDS, db);
TCreateTestPartnerData.CreateNewLocation(PartnerRow.PartnerKey, MainDS);
DataSet ResponseDS = new PartnerEditTDS();
TVerificationResultCollection VerificationResult;
TSubmitChangesResult result = connector.SubmitChanges(ref MainDS, ref ResponseDS, out VerificationResult);
Transaction.Commit();
CommonNUnitFunctions.EnsureNullOrOnlyNonCriticalVerificationResults(VerificationResult,
"There was a critical error when saving:");
Assert.AreEqual(TSubmitChangesResult.scrOK, result, "saving the first partner with a location");
Int32 LocationKey = MainDS.PLocation[0].LocationKey;
MainDS = new PartnerEditTDS();
Transaction = db.BeginTransaction(IsolationLevel.Serializable);
PartnerRow = TCreateTestPartnerData.CreateNewFamilyPartner(MainDS, db);
PPartnerLocationRow PartnerLocationRow = MainDS.PPartnerLocation.NewRowTyped();
PartnerLocationRow.SiteKey = DomainManager.GSiteKey;
PartnerLocationRow.PartnerKey = PartnerRow.PartnerKey;
PartnerLocationRow.LocationKey = LocationKey;
MainDS.PPartnerLocation.Rows.Add(PartnerLocationRow);
ResponseDS = new PartnerEditTDS();
result = connector.SubmitChanges(ref MainDS, ref ResponseDS, out VerificationResult);
Transaction.Commit();
CommonNUnitFunctions.EnsureNullOrOnlyNonCriticalVerificationResults(VerificationResult,
"There was a critical error when saving:");
TDBTransaction ReadTransaction = new TDBTransaction();
db.ReadTransaction(
ref ReadTransaction,
delegate
{
PPartnerTable PartnerAtAddress = PPartnerAccess.LoadViaPLocation(
DomainManager.GSiteKey, LocationKey, ReadTransaction);
Assert.AreEqual(2, PartnerAtAddress.Rows.Count, "there should be two partners at this location");
});
}
/// <summary>
/// Returns the Family Members of a Family.
/// </summary>
/// <param name="AFamilyPartnerKey">PartnerKey of the FAMILY.</param>
/// <param name="AReadTransaction">Open DB Transaction.</param>
/// <returns>An Instance of <see cref="PartnerInfoTDSFamilyMembersTable" />.
/// If there were Family Members, there will be one DataRow for each Family Member.</returns>
private static PartnerInfoTDSFamilyMembersTable GetFamilyMembers(Int64 AFamilyPartnerKey,
TDBTransaction AReadTransaction)
{
OdbcParameter[] ParametersArray;
DataSet TmpDS;
PPersonTable FamilyPersonsDT;
PartnerInfoTDSFamilyMembersRow NewRow;
PartnerInfoTDSFamilyMembersTable FamilyMembersDT;
FamilyMembersDT = new PartnerInfoTDSFamilyMembersTable();
ParametersArray = new OdbcParameter[1];
ParametersArray[0] = new OdbcParameter("", OdbcType.Decimal, 10);
ParametersArray[0].Value = (System.Object)AFamilyPartnerKey;
TmpDS = new DataSet();
FamilyPersonsDT = new PPersonTable();
TmpDS.Tables.Add(FamilyPersonsDT);
AReadTransaction.DataBaseObj.Select(TmpDS,
"SELECT " + "PUB_" + PPartnerTable.GetTableDBName() + '.' +
PPartnerTable.GetPartnerKeyDBName() + ", " +
PPersonTable.GetFamilyNameDBName() + ", " +
PPersonTable.GetTitleDBName() + ", " +
PPersonTable.GetFirstNameDBName() + ", " +
PPersonTable.GetMiddleName1DBName() + ", " +
PPersonTable.GetFamilyIdDBName() + ' ' +
"FROM PUB_" + PPersonTable.GetTableDBName() +
" INNER JOIN " + "PUB_" + PPartnerTable.GetTableDBName() + " ON " +
"PUB_" + PPersonTable.GetTableDBName() + '.' +
PPartnerTable.GetPartnerKeyDBName() + " = " +
"PUB_" + PPartnerTable.GetTableDBName() + '.' +
PPartnerTable.GetPartnerKeyDBName() + ' ' +
"WHERE " + PPersonTable.GetFamilyKeyDBName() + " = ? " +
"AND " + PPartnerTable.GetStatusCodeDBName() + " <> '" +
SharedTypes.StdPartnerStatusCodeEnumToString(TStdPartnerStatusCode.spscMERGED) + "' " + // Make sure we don't load MERGED Partners (shouldn't have a p_family_key_n, but just in case.)
"ORDER BY " + PPersonTable.GetFamilyIdDBName() + " ASC",
PPersonTable.GetTableName(), AReadTransaction, ParametersArray, 0, 0);
// Add Persons to Table
for (Int32 Counter = 0; Counter <= FamilyPersonsDT.Rows.Count - 1; Counter += 1)
{
NewRow = FamilyMembersDT.NewRowTyped(false);
NewRow.PartnerKey = FamilyPersonsDT[Counter].PartnerKey;
NewRow.PartnerShortName =
Calculations.DeterminePartnerShortName(TSaveConvert.StringColumnToString(FamilyPersonsDT.ColumnFamilyName,
FamilyPersonsDT[Counter]), TSaveConvert.StringColumnToString(FamilyPersonsDT.ColumnTitle,
FamilyPersonsDT[Counter]), TSaveConvert.StringColumnToString(FamilyPersonsDT.ColumnFirstName,
FamilyPersonsDT[Counter]),
TSaveConvert.StringColumnToString(FamilyPersonsDT.ColumnMiddleName1, FamilyPersonsDT[Counter]));
NewRow.FamilyId = FamilyPersonsDT[Counter].FamilyId;
FamilyMembersDT.Rows.Add(NewRow);
}
return(FamilyMembersDT);
}
public static bool SaveSiteKeys(List <Int64> ASiteKeysSetUpForUse, List <Int64> ASiteKeysToRemove)
{
TDBTransaction Transaction = new TDBTransaction();
bool SubmissionOK = true;
PPartnerLedgerTable PartnerLedgerTable = new PPartnerLedgerTable();
PPartnerLedgerRow PartnerLedgerRow;
// save site keys that can be used in p_partner_ledger
DBAccess.WriteTransaction(
ref Transaction, ref SubmissionOK,
delegate
{
// create new records in p_partner_ledger if not there yet
foreach (Int64 SiteKey in ASiteKeysSetUpForUse)
{
if (PPartnerLedgerAccess.CountViaPUnit(SiteKey, Transaction) == 0)
{
PartnerLedgerRow = PartnerLedgerTable.NewRowTyped();
PartnerLedgerRow.PartnerKey = SiteKey;
// calculate last partner id, from older uses of this ledger number
object MaxExistingPartnerKeyObj = Transaction.DataBaseObj.ExecuteScalar(
String.Format("SELECT MAX(" + PPartnerTable.GetPartnerKeyDBName() + ") FROM " + PPartnerTable.GetTableDBName() +
" WHERE " + PPartnerTable.GetPartnerKeyDBName() + " > {0} AND " + PPartnerTable.GetPartnerKeyDBName() +
" < {1}",
SiteKey,
SiteKey + 500000), Transaction);
if (MaxExistingPartnerKeyObj.GetType() != typeof(DBNull))
{
// found a partner key for this site already: set it to last used value
PartnerLedgerRow.LastPartnerId = Convert.ToInt32(Convert.ToInt64(MaxExistingPartnerKeyObj) - SiteKey);
}
else
{
// in this case there was no partner key for this site yet
PartnerLedgerRow.LastPartnerId = 0;
}
PartnerLedgerTable.Rows.Add(PartnerLedgerRow);
}
}
// delete records from p_partner_ledger that are no longer needed
foreach (Int64 SiteKey in ASiteKeysToRemove)
{
PPartnerLedgerAccess.DeleteByPrimaryKey(SiteKey, Transaction);
}
PPartnerLedgerAccess.SubmitChanges(PartnerLedgerTable, Transaction);
});
// make sure SitesList will be refreshed when called next time
TPartnerCacheableWebConnector.RefreshCacheableTable(TCacheablePartnerTablesEnum.InstalledSitesList);
return(SubmissionOK);
}
/// <summary>
/// Change subscription for Partners in selected Extract
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
public void ChangeSubscription(System.Object sender, EventArgs e)
{
PSubscriptionTable SubscriptionTable = new PSubscriptionTable();
PSubscriptionRow SubscriptionRow = SubscriptionTable.NewRowTyped();
PPartnerTable PartnersWithoutSubs = new PPartnerTable();
int SubscriptionsChanged;
String MessageText;
List <String> FieldsToChange = new List <string>();
if (!WarnIfNotSingleSelection(Catalog.GetString("Add Subscription")) &&
(GetSelectedDetailRow() != null))
{
TFrmUpdateExtractChangeSubscriptionDialog dialog = new TFrmUpdateExtractChangeSubscriptionDialog(this.FindForm());
dialog.SetExtractName(GetSelectedDetailRow().ExtractName);
if (dialog.ShowDialog() == System.Windows.Forms.DialogResult.OK)
{
if (dialog.GetReturnedParameters(ref SubscriptionRow, ref FieldsToChange))
{
SubscriptionTable.Rows.Add(SubscriptionRow);
// perform update of extract data on server side
TRemote.MPartner.Partner.WebConnectors.ChangeSubscription
(GetSelectedDetailRow().ExtractId, ref SubscriptionTable, FieldsToChange, out PartnersWithoutSubs,
out SubscriptionsChanged);
MessageText =
String.Format(Catalog.GetString(
"Subscription {0} successfully changed for {1} out of {2} Partner(s) in Extract {3}."),
SubscriptionRow.PublicationCode,
SubscriptionsChanged, GetSelectedDetailRow().KeyCount, GetSelectedDetailRow().ExtractName);
if (PartnersWithoutSubs.Rows.Count > 0)
{
MessageText += "\r\n\r\n" +
String.Format(Catalog.GetString(
"See the following Dialog for the {0} Partner(s) that are not subscribed for this Publication and therefore no change was made for them."),
PartnersWithoutSubs.Rows.Count);
}
MessageBox.Show(MessageText,
Catalog.GetString("Change Subscription"),
MessageBoxButtons.OK,
MessageBoxIcon.Information);
if (PartnersWithoutSubs.Rows.Count > 0)
{
TFrmSimplePartnerListDialog partnerDialog = new TFrmSimplePartnerListDialog(this.FindForm());
partnerDialog.SetExplanation("These partners do not have a Subscription for " + SubscriptionRow.PublicationCode,
"The Subscription was therefore not changed for the following Partners:");
partnerDialog.SetPartnerList(PartnersWithoutSubs);
partnerDialog.ShowDialog();
}
}
}
}
}
/// <summary>
/// Create a Partner and a Person having this FamilyKey, living at this address.
/// </summary>
/// <param name="AFamilyKey"></param>
/// <param name="ALocationKey"></param>
/// <param name="ANode"></param>
/// <param name="AMainDS"></param>
/// <param name="ATransaction"></param>
private static Int64 CreateNewPerson(Int64 AFamilyKey, int ALocationKey, XmlNode ANode,
ref PartnerImportExportTDS AMainDS, TDBTransaction ATransaction)
{
AMainDS.PFamily.DefaultView.RowFilter = String.Format("{0}={1}", PFamilyTable.GetPartnerKeyDBName(), AFamilyKey);
PFamilyRow FamilyRow = (PFamilyRow)AMainDS.PFamily.DefaultView[0].Row;
AMainDS.PPartner.DefaultView.RowFilter = String.Format("{0}={1}", PPartnerTable.GetPartnerKeyDBName(), AFamilyKey);
PPartnerRow PartnerRow = (PPartnerRow)AMainDS.PPartner.DefaultView[0].Row;
PPartnerRow newPartner = AMainDS.PPartner.NewRowTyped();
AMainDS.PPartner.Rows.Add(newPartner);
newPartner.PartnerKey = (AMainDS.PPartner.Rows.Count + 1) * -1;
newPartner.PartnerClass = MPartnerConstants.PARTNERCLASS_PERSON;
newPartner.AddresseeTypeCode = PartnerRow.AddresseeTypeCode;
newPartner.PartnerShortName = PartnerRow.PartnerShortName;
newPartner.LanguageCode = PartnerRow.LanguageCode;
newPartner.Comment = TXMLParser.GetAttribute(ANode, MPartnerConstants.PARTNERIMPORT_NOTES);
newPartner.AcquisitionCode = PartnerRow.AcquisitionCode;
newPartner.StatusCode = MPartnerConstants.PARTNERSTATUS_ACTIVE;
PPersonRow newPerson = AMainDS.PPerson.NewRowTyped();
AMainDS.PPerson.Rows.Add(newPerson);
newPerson.PartnerKey = newPartner.PartnerKey;
newPerson.FamilyKey = AFamilyKey;
// When this record is imported, newPerson.FamilyId must be unique for this family!
newPerson.FirstName = FamilyRow.FirstName;
newPerson.FamilyName = FamilyRow.FamilyName;
newPerson.Title = FamilyRow.Title;
newPerson.Gender = GetGenderCode(ANode);
newPerson.MaritalStatus = FamilyRow.MaritalStatus;
PPartnerLocationRow newPartnerLocation = AMainDS.PPartnerLocation.NewRowTyped();
AMainDS.PPartnerLocation.Rows.Add(newPartnerLocation);
newPartnerLocation.LocationKey = ALocationKey; // This person lives at the same address as the family.
newPartnerLocation.SiteKey = 0;
newPartnerLocation.PartnerKey = newPartner.PartnerKey;
newPartnerLocation.DateEffective = DateTime.Now.Date;
newPartnerLocation.LocationType = MPartnerConstants.LOCATIONTYPE_HOME;
newPartnerLocation.SendMail = true;
newPartnerLocation["p_email_address_c"] =
TXMLParser.GetAttribute(ANode, MPartnerConstants.PARTNERIMPORT_EMAIL); // Important: Do not use 'newPartnerLocation.EmailAddress' as this Column will get removed once Contact Details conversion is finished!
newPartnerLocation["p_telephone_number_c"] =
TXMLParser.GetAttribute(ANode, MPartnerConstants.PARTNERIMPORT_PHONE); // Important: Do not use 'newPartnerLocation.TelephoneNumber' as this Column will get removed once Contact Details conversion is finished!
newPartnerLocation["p_mobile_number_c"] =
TXMLParser.GetAttribute(ANode, MPartnerConstants.PARTNERIMPORT_MOBILEPHONE); // Important: Do not use 'newPartnerLocation.MobileNumber' as this Column will get removed once Contact Details conversion is finished!
AddVerificationResult("Person Record Created.", TResultSeverity.Resv_Status);
return(newPerson.PartnerKey);
}
private static bool GetAllReceivingFields(long AConferenceKey, ref DataTable AFieldsTable)
{
TDBTransaction ReadTransaction;
Boolean NewTransaction = false;
TLogging.LogAtLevel(9, "TConferenceOptions.GetAllReceivingFields called!");
TDataBase db = DBAccess.Connect("GetAllReceivingFields");
ReadTransaction = db.GetNewOrExistingTransaction(IsolationLevel.RepeatableRead,
out NewTransaction);
try
{
OdbcParameter[] ParametersArray;
ParametersArray = new OdbcParameter[0];
DataSet TmpDS = ReadTransaction.DataBaseObj.Select(
"SELECT PUB_" + PUnitTable.GetTableDBName() + '.' + PUnitTable.GetPartnerKeyDBName() +
", PUB_" + PUnitTable.GetTableDBName() + '.' + PUnitTable.GetUnitNameDBName() +
" FROM PUB_" + PUnitTable.GetTableDBName() +
", PUB_" + PPartnerTable.GetTableDBName() + ", " +
"PUB_" + PPartnerTypeTable.GetTableDBName() +
" WHERE PUB_" + PUnitTable.GetTableDBName() + '.' + PUnitTable.GetPartnerKeyDBName() +
" = PUB_" + PPartnerTable.GetTableDBName() + '.' + PPartnerTable.GetPartnerKeyDBName() +
" AND PUB_" + PPartnerTypeTable.GetTableDBName() + '.' + PPartnerTypeTable.GetPartnerKeyDBName() +
" = PUB_" + PPartnerTable.GetTableDBName() + '.' + PPartnerTable.GetPartnerKeyDBName() +
" AND PUB_" + PPartnerTable.GetTableDBName() + '.' + PPartnerTable.GetStatusCodeDBName() + " = \"ACTIVE\"" +
" AND PUB_" + PPartnerTypeTable.GetTableDBName() + '.' + PPartnerTypeTable.GetTypeCodeDBName() + " = \"LEDGER\"" +
" ORDER BY PUB_" + PUnitTable.GetTableDBName() + '.' + PUnitTable.GetUnitNameDBName() + " ASC",
"TempTable", ReadTransaction, ParametersArray);
DataTable ResultTale = TmpDS.Tables[0];
for (int Counter = 0; Counter < ResultTale.Rows.Count; ++Counter)
{
DataRow NewRow = AFieldsTable.NewRow();
NewRow[SELECTION] = false;
NewRow[UNIT_KEY] = ResultTale.Rows[Counter][PUnitTable.GetPartnerKeyDBName()];
NewRow[UNIT_NAME] = ResultTale.Rows[Counter][PUnitTable.GetUnitNameDBName()];
NewRow[USED_IN_CONFERENCE] = true;
AFieldsTable.Rows.Add(NewRow);
}
}
finally
{
if (NewTransaction)
{
ReadTransaction.Commit();
TLogging.LogAtLevel(7, "TConferenceOptions.GetReceivingFields: committed own transaction.");
}
}
return(true);
}
/// <summary>
/// assign list of partners to the dialog
/// </summary>
/// <param name="APartnerTable"></param>
public void SetPartnerList(PPartnerTable APartnerTable)
{
grdPartners.Columns.Clear();
grdPartners.AddTextColumn(Catalog.GetString("Partner Name"), APartnerTable.ColumnPartnerShortName, 240);
grdPartners.AddPartnerKeyColumn(Catalog.GetString("Partner Key"), APartnerTable.ColumnPartnerKey, 100);
DataView myDataView = APartnerTable.DefaultView;
myDataView.AllowNew = false;
grdPartners.DataSource = new DevAge.ComponentModel.BoundDataView(myDataView);
}
private static PartnerDetails GetRecipient(Int64 APartnerKey)
{
if (RecipientList.ContainsKey(APartnerKey))
{
return(RecipientList[APartnerKey]);
}
PartnerDetails Ret = new PartnerDetails();
PPartnerTable PartnerTbl = PPartnerAccess.LoadByPrimaryKey(APartnerKey, FTransaction);
if (PartnerTbl.Rows.Count > 0)
{
PPartnerRow PartnerRow = PartnerTbl[0];
Ret.LastName = PartnerRow.PartnerShortName;
if (PartnerRow.PartnerClass == "PERSON")
{
PPersonTable PersonTbl = PPersonAccess.LoadByPrimaryKey(APartnerKey, FTransaction);
if (PersonTbl.Rows.Count > 0)
{
PPersonRow PersonRow = PersonTbl[0];
Ret.FirstName = PersonRow.FirstName;
Ret.LastName = PersonRow.FamilyName;
Ret.Class = "PERSON";
}
}
if (PartnerRow.PartnerClass == "FAMILY")
{
PFamilyTable FamilyTbl = PFamilyAccess.LoadByPrimaryKey(APartnerKey, FTransaction);
if (FamilyTbl.Rows.Count > 0)
{
PFamilyRow FamilyRow = FamilyTbl[0];
Ret.FirstName = FamilyRow.FirstName;
Ret.LastName = FamilyRow.FamilyName;
Ret.Class = "FAMILY";
}
}
PPartnerLocationRow LocationRow;
TLocationPK LocationKey = ServerCalculations.DetermineBestAddress(APartnerKey, out LocationRow);
if (LocationKey.LocationKey != -1)
{
Ret.Email = LocationRow.EmailAddress;
}
}
RecipientList.Add(APartnerKey, Ret);
return(Ret);
}
/// <summary>
/// assign list of partners to the dialog
/// </summary>
/// <param name="APartnerTable"></param>
public void SetPartnerList(PPartnerTable APartnerTable)
{
grdPartners.Columns.Clear();
grdPartners.AddTextColumn(Catalog.GetString("Partner Name"), APartnerTable.ColumnPartnerShortName, 240);
grdPartners.AddPartnerKeyColumn(Catalog.GetString("Partner Key"), APartnerTable.ColumnPartnerKey, 100);
DataView myDataView = APartnerTable.DefaultView;
myDataView.AllowNew = false;
grdPartners.DataSource = new DevAge.ComponentModel.BoundDataView(myDataView);
}
/// <summary>
/// get the number and name of the registration offices that the current user has access for
/// </summary>
/// <returns></returns>
public static PPartnerTable GetRegistrationOffices()
{
bool NewTransaction;
TDataBase db = DBAccess.Connect("GetRegistrationOffices");
TDBTransaction Transaction = db.GetNewOrExistingTransaction(IsolationLevel.ReadCommitted, out NewTransaction);
PPartnerTable result = new PPartnerTable();
try
{
List <Int64> offices = GetRegistrationOfficeKeysOfUser(Transaction);
StringCollection FieldList = new StringCollection();
FieldList.Add(PPartnerTable.GetPartnerKeyDBName());
FieldList.Add(PPartnerTable.GetPartnerShortNameDBName());
// get the short names of the registration offices
foreach (Int64 OfficeKey in offices)
{
PPartnerTable partnerTable = PPartnerAccess.LoadByPrimaryKey(OfficeKey, FieldList, Transaction);
result.Merge(partnerTable);
}
// remove unwanted columns
List <string> ColumnNames = new List <string>();
foreach (DataColumn column in result.Columns)
{
ColumnNames.Add(column.ColumnName);
}
foreach (string columnName in ColumnNames)
{
if (!FieldList.Contains(columnName))
{
result.Columns.Remove(columnName.ToString());
}
}
}
finally
{
if (NewTransaction)
{
Transaction.Rollback();
}
}
result.DefaultView.Sort = PPartnerTable.GetPartnerKeyDBName();
return(result);
}
/// <summary>
/// Retrieves the Partner ShortName, the PartnerClass and PartnerStatus.
/// </summary>
/// <param name="APartnerKey">PartnerKey to identify the Partner.</param>
/// <param name="APartnerShortName">Returns the ShortName.</param>
/// <param name="APartnerClass">Returns the PartnerClass (FAMILY, ORGANISATION, etc).</param>
/// <param name="APartnerStatus">Returns the PartnerStatus (eg. ACTIVE, DIED).</param>
/// <param name="ATransaction">Open DB Transaction.</param>
/// <returns>True if partner was found, otherwise false.</returns>
public static Boolean RetrievePartnerShortName(Int64 APartnerKey,
out String APartnerShortName,
out TPartnerClass APartnerClass,
out TStdPartnerStatusCode APartnerStatus,
TDBTransaction ATransaction)
{
Boolean ReturnValue;
StringCollection RequiredColumns;
PPartnerTable PartnerTable;
// initialise out Arguments
APartnerShortName = "";
// Default. This is not really correct but the best compromise if PartnerKey is 0 or Partner isn't found since we have an enum here.
APartnerClass = TPartnerClass.FAMILY;
// Default. This is not really correct but the best compromise if PartnerKey is 0 or Partner isn't found since we have an enum here.
APartnerStatus = TStdPartnerStatusCode.spscINACTIVE;
if (APartnerKey != 0)
{
// only some fields are needed
RequiredColumns = new StringCollection();
RequiredColumns.Add(PPartnerTable.GetPartnerShortNameDBName());
RequiredColumns.Add(PPartnerTable.GetPartnerClassDBName());
RequiredColumns.Add(PPartnerTable.GetStatusCodeDBName());
PartnerTable = PPartnerAccess.LoadByPrimaryKey(APartnerKey, RequiredColumns, ATransaction, null, 0, 0);
if (PartnerTable.Rows.Count == 0)
{
ReturnValue = false;
}
else
{
// since we loaded by primary key there must just be one partner row
APartnerShortName = PartnerTable[0].PartnerShortName;
APartnerClass = SharedTypes.PartnerClassStringToEnum(PartnerTable[0].PartnerClass);
APartnerStatus = SharedTypes.StdPartnerStatusCodeStringToEnum(PartnerTable[0].StatusCode);
ReturnValue = true;
}
}
else
{
// Return result as valid if Partner Key is 0.
ReturnValue = true;
}
return(ReturnValue);
}
private DataTable GetConferenceListTable(TDBTransaction AReadTransaction, string ATableName)
{
DataTable Table;
DataColumn[] Key = new DataColumn[1];
// Used eg. Select Event Dialog
Table = DBAccess.GetDBAccessObj(AReadTransaction).SelectDT(
"SELECT DISTINCT " +
PPartnerTable.GetPartnerShortNameDBName() +
", " + PPartnerTable.GetPartnerClassDBName() +
", " + PUnitTable.GetOutreachCodeDBName() +
", " + PCountryTable.GetTableDBName() + "." + PCountryTable.GetCountryNameDBName() +
", " + PPartnerLocationTable.GetTableDBName() + "." + PPartnerLocationTable.GetDateEffectiveDBName() +
", " + PPartnerLocationTable.GetTableDBName() + "." + PPartnerLocationTable.GetDateGoodUntilDBName() +
", " + PPartnerTable.GetTableDBName() + "." + PPartnerTable.GetPartnerKeyDBName() +
", " + PUnitTable.GetUnitTypeCodeDBName() +
" FROM PUB." + PPartnerTable.GetTableDBName() +
", PUB." + PUnitTable.GetTableDBName() +
", PUB." + PLocationTable.GetTableDBName() +
", PUB." + PPartnerLocationTable.GetTableDBName() +
", PUB." + PCountryTable.GetTableDBName() +
" WHERE " +
PPartnerTable.GetTableDBName() + "." + PPartnerTable.GetPartnerKeyDBName() + " = " +
PUnitTable.GetTableDBName() + "." + PUnitTable.GetPartnerKeyDBName() + " AND " +
PPartnerTable.GetTableDBName() + "." + PPartnerTable.GetPartnerKeyDBName() + " = " +
PPartnerLocationTable.GetTableDBName() + "." + PPartnerLocationTable.GetPartnerKeyDBName() + " AND " +
PLocationTable.GetTableDBName() + "." + PLocationTable.GetSiteKeyDBName() + " = " +
PPartnerLocationTable.GetTableDBName() + "." + PPartnerLocationTable.GetSiteKeyDBName() + " AND " +
PLocationTable.GetTableDBName() + "." + PLocationTable.GetLocationKeyDBName() + " = " +
PPartnerLocationTable.GetTableDBName() + "." + PPartnerLocationTable.GetLocationKeyDBName() + " AND " +
PCountryTable.GetTableDBName() + "." + PCountryTable.GetCountryCodeDBName() + " = " +
PLocationTable.GetTableDBName() + "." + PLocationTable.GetCountryCodeDBName() + " AND " +
PPartnerTable.GetStatusCodeDBName() + " = 'ACTIVE' AND " +
PPartnerTable.GetPartnerClassDBName() + " = 'UNIT' AND (" +
PUnitTable.GetUnitTypeCodeDBName() + " LIKE '%CONF%' OR " +
PUnitTable.GetUnitTypeCodeDBName() + " LIKE '%CONG%')"
,
ATableName, AReadTransaction);
Key[0] = Table.Columns[PPartnerTable.GetPartnerKeyDBName()];
Table.PrimaryKey = Key;
return(Table);
}
/// <summary>
/// This Procedure will get called when the event filter criteria are changed
/// </summary>
/// <param name="sender">The Object that throws this Event</param>
/// <param name="e">Event Arguments.
/// </param>
/// <returns>void</returns>
private void EventFilterChanged(System.Object sender, System.EventArgs e)
{
DataView view = new DataView(FOutreachUnitsTable);
view.AllowNew = false;
view.RowFilter = ucoFilter.GetFilterCriteria();
clbEvent.DataBindGrid(
view.ToTable(), PPartnerTable.GetPartnerShortNameDBName(), "CHECKED", PPartnerTable.GetPartnerKeyDBName(), false, true, false);
clbEvent.SetCheckedStringList("");
clbEvent.AutoStretchColumnsToFitWidth = true;
clbEvent.AutoResizeGrid();
}
/// <summary>
/// Retrieves the ShortName of a Partner.
/// </summary>
/// <param name="APartnerKey">PartnerKey of the Partner.</param>
/// <param name="ATransaction">Database transaction to use.</param>
/// <returns>ShortName of the specified Partner.</returns>
private static string GetPartnerShortName(long APartnerKey, TDBTransaction ATransaction)
{
// Can't use TPartnerServerLookups from here:
// TPartnerServerLookups.GetPartnerShortName(APartnerKey, out ShortName, out PartnerClass);
PPartnerTable PartnerTable;
var Columns = new StringCollection();
Columns.Add(PPartnerTable.GetPartnerKeyDBName());
Columns.Add(PPartnerTable.GetPartnerShortNameDBName());
PartnerTable = PPartnerAccess.LoadByPrimaryKey(APartnerKey, Columns, ATransaction);
return(PartnerTable[0].PartnerShortName);
}
/// <summary>
/// Loads all available Ledgers and their names into a DataTable
///
/// </summary>
/// <returns>void</returns>
public static DataTable GetData(String ATableName, TDBTransaction AReadTransaction)
{
return(DBAccess.GDBAccessObj.SelectDT(
String.Format(
"SELECT {0} AS LedgerNumber, {1} AS LedgerName FROM pub_{2}, pub_{3} " +
"WHERE pub_{2}.{4} = pub_{3}.{4} " + "AND pub_{2}.{5} = 1",
ALedgerTable.GetLedgerNumberDBName(),
PPartnerTable.GetPartnerShortNameDBName(),
ALedgerTable.GetTableDBName(),
PPartnerTable.GetTableDBName(),
PPartnerTable.GetPartnerKeyDBName(),
ALedgerTable.GetLedgerStatusDBName()),
ATableName,
AReadTransaction));
}
private void AcceptSelection(System.Object sender, EventArgs e)
{
DialogResult = DialogResult.Cancel;
if (grdEvent.SelectedDataRows.Length == 1)
{
this.DialogResult = DialogResult.OK;
FSelectedUnitName = (((DataRowView)grdEvent.SelectedDataRows[0]).Row[PPartnerTable.GetPartnerShortNameDBName()]).ToString();
FSelectedOutreachCode = (((DataRowView)grdEvent.SelectedDataRows[0]).Row[PUnitTable.GetOutreachCodeDBName()]).ToString();
String PartnerKey = ((DataRowView)grdEvent.SelectedDataRows[0]).Row[PPartnerTable.GetPartnerKeyDBName()].ToString();
FSelectedPartnerKey = Convert.ToInt64(PartnerKey);
}
this.Close();
}
/// <summary>
/// todoComment
/// </summary>
/// <returns></returns>
public Int64 DetermineCurrentPartnerKey()
{
DataRow CurrentDR = this.CurrentDataRow;
if (CurrentDR != null)
{
// get PartnerKey of current DataRow
FPartnerKey = Convert.ToInt64(CurrentDR[PPartnerTable.GetPartnerKeyDBName()]);
}
else
{
FPartnerKey = -1;
}
// MessageBox.Show(FPartnerKey.ToString);
return(FPartnerKey);
}
/// <summary>
/// export all posted invoices of other suppliers in this year
/// </summary>
public static void Export(string AOutputPath,
char ACSVSeparator,
string ANewLine,
Int32 ALedgerNumber,
Int32 AFinancialYear,
string ACostCentres)
{
string filename = Path.GetFullPath(Path.Combine(AOutputPath, "accountspayable.csv"));
Console.WriteLine("Writing file: " + filename);
StringBuilder sb = new StringBuilder();
TDBTransaction Transaction = null;
DBAccess.GDBAccessObj.BeginAutoReadTransaction(IsolationLevel.ReadCommitted, ref Transaction,
delegate
{
// get all posted or paid ap_documents by their date issued
string sql =
String.Format(
"SELECT * FROM PUB_{0} " +
"WHERE {1} = {2} AND " +
"({3} = '{4}' OR {3} = '{5}' OR {3} = '{6}') AND " +
"{7} >= ? AND {7} <= ?",
AApDocumentTable.GetTableDBName(),
AApDocumentTable.GetLedgerNumberDBName(),
ALedgerNumber,
AApDocumentTable.GetDocumentStatusDBName(),
MFinanceConstants.AP_DOCUMENT_POSTED,
MFinanceConstants.AP_DOCUMENT_PARTIALLY_PAID,
MFinanceConstants.AP_DOCUMENT_PAID,
AApDocumentTable.GetDateIssuedDBName());
List <OdbcParameter>Parameters = new List <OdbcParameter>();
OdbcParameter param = new OdbcParameter("startdate", OdbcType.DateTime);
param.Value = TAccountingPeriodsWebConnector.GetPeriodStartDate(ALedgerNumber, AFinancialYear, 0, 1);
Parameters.Add(param);
param = new OdbcParameter("enddate", OdbcType.DateTime);
//TODO: Calendar vs Financial Date Handling - Check if this should use financial year start/end and not assume calendar
param.Value = TAccountingPeriodsWebConnector.GetPeriodEndDate(ALedgerNumber, AFinancialYear, 0, 12);
Parameters.Add(param);
AApDocumentTable apDocuments = new AApDocumentTable();
DBAccess.GDBAccessObj.SelectDT(apDocuments, sql, Transaction, Parameters.ToArray(), 0, 0);
// get all ap details
sql =
String.Format(
"SELECT Detail.* FROM PUB_{0} AS Doc, PUB_{8} AS Detail " +
"WHERE Doc.{1} = {2} AND " +
"({3} = '{4}' OR {3} = '{5}' OR {3} = '{6}') AND " +
"{7} >= ? AND {7} <= ? AND " +
"Detail.{1} = Doc.{1} AND Detail.{9} = Doc.{9} AND " +
"Detail.{10} IN ({11})",
AApDocumentTable.GetTableDBName(),
AApDocumentTable.GetLedgerNumberDBName(),
ALedgerNumber,
AApDocumentTable.GetDocumentStatusDBName(),
MFinanceConstants.AP_DOCUMENT_POSTED,
MFinanceConstants.AP_DOCUMENT_PARTIALLY_PAID,
MFinanceConstants.AP_DOCUMENT_PAID,
AApDocumentTable.GetDateIssuedDBName(),
AApDocumentDetailTable.GetTableDBName(),
AApDocumentTable.GetApDocumentIdDBName(),
AApDocumentDetailTable.GetCostCentreCodeDBName(),
"'" + ACostCentres.Replace(",", "','") + "'");
AApDocumentDetailTable apDetails = new AApDocumentDetailTable();
DBAccess.GDBAccessObj.SelectDT(apDetails, sql, Transaction, Parameters.ToArray(), 0, 0);
apDetails.DefaultView.Sort = AApDocumentDetailTable.GetApDocumentIdDBName();
// get all ap payments
sql =
String.Format(
"SELECT DP.{0}, DP.{1}, P.{2} AS {3}, DP.{7}, DP.{15} FROM PUB_{4} AS Doc, PUB_{5} AS DP, PUB_{6} AS P " +
"WHERE Doc.{7} = {8} AND " +
"({9} = '{10}' OR {9} = '{11}' OR {9} = '{12}') AND " +
"{13} >= ? AND {13} <= ? AND " +
"DP.{7} = Doc.{7} AND DP.{14} = Doc.{14} AND " +
"P.{7} = Doc.{7} AND P.{15} = DP.{15}",
AApDocumentPaymentTable.GetApDocumentIdDBName(),
AApDocumentPaymentTable.GetAmountDBName(),
AApPaymentTable.GetPaymentDateDBName(),
AApDocumentPaymentTable.GetDateCreatedDBName(),
AApDocumentTable.GetTableDBName(),
AApDocumentPaymentTable.GetTableDBName(),
AApPaymentTable.GetTableDBName(),
AApDocumentTable.GetLedgerNumberDBName(),
ALedgerNumber,
AApDocumentTable.GetDocumentStatusDBName(),
MFinanceConstants.AP_DOCUMENT_POSTED,
MFinanceConstants.AP_DOCUMENT_PARTIALLY_PAID,
MFinanceConstants.AP_DOCUMENT_PAID,
AApDocumentTable.GetDateIssuedDBName(),
AApDocumentTable.GetApDocumentIdDBName(),
AApPaymentTable.GetPaymentNumberDBName());
AApDocumentPaymentTable apPayments = new AApDocumentPaymentTable();
DBAccess.GDBAccessObj.SelectDT(apPayments, sql, Transaction, Parameters.ToArray(), 0, 0);
apPayments.DefaultView.Sort = AApDocumentPaymentTable.GetApDocumentIdDBName();
// get the analysis attributes for the taxes
sql =
String.Format(
"SELECT Attrib.* FROM PUB_{0} AS Doc, PUB_{8} AS Attrib " +
"WHERE Doc.{1} = {2} AND " +
"({3} = '{4}' OR {3} = '{5}' OR {3} = '{6}') AND " +
"{7} >= ? AND {7} <= ? AND " +
"Attrib.{1} = Doc.{1} AND Attrib.{9} = Doc.{9}",
AApDocumentTable.GetTableDBName(),
AApDocumentTable.GetLedgerNumberDBName(),
ALedgerNumber,
AApDocumentTable.GetDocumentStatusDBName(),
MFinanceConstants.AP_DOCUMENT_POSTED,
MFinanceConstants.AP_DOCUMENT_PARTIALLY_PAID,
MFinanceConstants.AP_DOCUMENT_PAID,
AApDocumentTable.GetDateIssuedDBName(),
AApAnalAttribTable.GetTableDBName(),
AApDocumentTable.GetApDocumentIdDBName());
AApAnalAttribTable apAttrib = new AApAnalAttribTable();
DBAccess.GDBAccessObj.SelectDT(apAttrib, sql, Transaction, Parameters.ToArray(), 0, 0);
apAttrib.DefaultView.Sort = AApAnalAttribTable.GetApDocumentIdDBName() + "," + AApAnalAttribTable.GetDetailNumberDBName();
// get the supplier name
sql =
String.Format(
"SELECT DISTINCT p.* FROM PUB_{0} AS Doc, PUB_{8} AS p " +
"WHERE Doc.{1} = {2} AND " +
"({3} = '{4}' OR {3} = '{5}' OR {3} = '{6}') AND " +
"{7} >= ? AND {7} <= ? AND " +
"p.{9} = Doc.{10}",
AApDocumentTable.GetTableDBName(),
AApDocumentTable.GetLedgerNumberDBName(),
ALedgerNumber,
AApDocumentTable.GetDocumentStatusDBName(),
MFinanceConstants.AP_DOCUMENT_POSTED,
MFinanceConstants.AP_DOCUMENT_PARTIALLY_PAID,
MFinanceConstants.AP_DOCUMENT_PAID,
AApDocumentTable.GetDateIssuedDBName(),
PPartnerTable.GetTableDBName(),
PPartnerTable.GetPartnerKeyDBName(),
AApDocumentTable.GetPartnerKeyDBName());
PPartnerTable suppliers = new PPartnerTable();
DBAccess.GDBAccessObj.SelectDT(suppliers, sql, Transaction, Parameters.ToArray(), 0, 0);
foreach (AApDocumentRow doc in apDocuments.Rows)
{
PPartnerRow supplier = (PPartnerRow)suppliers.Rows.Find(doc.PartnerKey);
DataRowView[] detailsRV = apDetails.DefaultView.FindRows(doc.ApDocumentId);
foreach (DataRowView rv in detailsRV)
{
AApDocumentDetailRow detail = (AApDocumentDetailRow)rv.Row;
if (doc.CreditNoteFlag)
{
detail.Amount *= -1.0m;
}
DataRowView[] payments = apPayments.DefaultView.FindRows(doc.ApDocumentId);
string DatePaid = string.Empty;
if (payments.Length > 1)
{
DatePaid = "Several Payments: ";
foreach (DataRowView rvPayment in payments)
{
AApDocumentPaymentRow payment = ((AApDocumentPaymentRow)rvPayment.Row);
DatePaid += payment.DateCreated.Value.ToString("yyyyMMdd") + " ";
DatePaid += payment.PaymentNumber.ToString() + "; ";
}
// for gdpdu, only write dates to this column
DatePaid = string.Empty;
}
else if (payments.Length == 1)
{
AApDocumentPaymentRow payment = ((AApDocumentPaymentRow)payments[0].Row);
DatePaid = payment.DateCreated.Value.ToString("yyyyMMdd");
}
sb.Append(StringHelper.StrMerge(
new string[] {
doc.ApNumber.ToString(),
detail.DetailNumber.ToString(),
doc.DateIssued.ToString("yyyyMMdd"),
DatePaid,
doc.PartnerKey.ToString(),
supplier.PartnerShortName,
detail.CostCentreCode,
detail.AccountCode,
String.Format("{0:N}", detail.Amount),
detail.Narrative
}, ACSVSeparator));
sb.Append(ANewLine);
}
}
});
StreamWriter sw = new StreamWriter(filename, false, Encoding.GetEncoding(1252));
sw.Write(sb.ToString());
sw.Close();
}
/// <summary>
/// First loads the extractTable needed. Goes throught all the Partners in the extract, If the partner already has the subscription, returns all those partners back to client. If the partner doesn't have the subscription, saves this
/// subscription to those partners.
/// </summary>
/// <returns>void</returns>
private void SubmitChangesInternal()
{
TDBTransaction SubmitChangesTransaction;
TSubmitChangesResult SubmissionResult = TSubmitChangesResult.scrOK;
MExtractTable ExtractDT;
PSubscriptionTable SubscriptionTable;
PPartnerTable PartnerTable;
Int32 RowCounter;
Int32 PartnersInExtract;
StringCollection RequiredColumns;
StringCollection RequiredColumns2;
if (FInspectDT != null)
{
// Initialisations
FVerificationResult = new TVerificationResultCollection();
ExtractDT = new MExtractTable();
SubscriptionTable = new PSubscriptionTable();
FSubmissionDT = SubscriptionTable.Clone();
((TTypedDataTable)FSubmissionDT).InitVars();
PartnerTable = new PPartnerTable();
RequiredColumns = new StringCollection();
RequiredColumns.Add(MExtractTable.GetPartnerKeyDBName());
RequiredColumns2 = new StringCollection();
RequiredColumns2.Add(PPartnerTable.GetPartnerKeyDBName());
RequiredColumns2.Add(PPartnerTable.GetPartnerShortNameDBName());
RowCounter = 0;
// Set up asynchronous execution
FAsyncExecProgress.ProgressState = TAsyncExecProgressState.Aeps_Executing;
FAsyncExecProgress.ProgressInformation = "Checking Partners' Subscriptions...";
try
{
SubmitChangesTransaction = DBAccess.GDBAccessObj.BeginTransaction(IsolationLevel.Serializable);
// TLogging.LogAtLevel(7, "TExtractsAddSubscriptionsUIConnector.SubmitChangesInternal: loading Subscriptions for ExtractID " + FExtractID.ToString() + "...");
ExtractDT = MExtractAccess.LoadViaMExtractMaster(FExtractID, RequiredColumns, SubmitChangesTransaction);
PartnersInExtract = ExtractDT.Rows.Count;
// TLogging.LogAtLevel(7, "TExtractsAddSubscriptionsUIConnector.SubmitChangesInternal: ExtractID has " + PartnersInExtract.ToString() + " Partners.");
// Go throught all the Partners in the extract
foreach (MExtractRow ExtractRow in ExtractDT.Rows)
{
RowCounter = RowCounter + 1;
// Calculate how much Partners we have checked. Let all Partners be a maximum of 70%.
FAsyncExecProgress.ProgressPercentage =
Convert.ToInt16((((double)RowCounter / (double)PartnersInExtract) * 100) * (MAX_PERCENTAGE_CHECKS / 100.0));
TLogging.LogAtLevel(7, "TExtractsAddSubscriptionsUIConnector.SubmitChangesInternal: loadbyPrimaryKey");
SubscriptionTable = PSubscriptionAccess.LoadByPrimaryKey(
FInspectDT[0].PublicationCode,
ExtractRow.PartnerKey,
SubmitChangesTransaction);
// if the Partner does not yet have the subscription, add the subscription to this partner.
if (SubscriptionTable.Rows.Count == 0)
{
TLogging.LogAtLevel(
7,
"TExtractsAddSubscriptionsUIConnector.SubmitChangesInternal: will add Subscription to Partner with PartnerKey " +
ExtractRow.PartnerKey.ToString());
FInspectDT[0].PartnerKey = ExtractRow.PartnerKey;
TLogging.LogAtLevel(7, "TExtractsAddSubscriptionsUIConnector.SubmitChangesInternal: importing Row into FSubmissionDT...");
FSubmissionDT.ImportRow(FInspectDT[0]);
}
else
{
// The partner already has this Subscription: add the partner to the ResponseTable
// TLogging.LogAtLevel(7, "TExtractsAddSubscriptionsUIConnector.SubmitChangesInternal: won't add Subscription to Partner with PartnerKey " + ExtractRow.PartnerKey.ToString());
PartnerTable = PPartnerAccess.LoadByPrimaryKey(ExtractRow.PartnerKey, RequiredColumns2, SubmitChangesTransaction);
if (FResponseDT == null)
{
FResponseDT = PartnerTable.Clone();
}
FResponseDT.ImportRow(PartnerTable[0]);
}
}
TLogging.LogAtLevel(7, "TExtractsAddSubscriptionsUIConnector.SubmitChangesInternal: Finished checking Partner's Subscriptions.");
if (FSubmissionDT.Rows.Count > 0)
{
// Submit the Partners with new Subscriptions to the PSubscription Table.
FAsyncExecProgress.ProgressInformation = "Adding Subscriptions to " + FSubmissionDT.Rows.Count.ToString() + " Partners...";
FAsyncExecProgress.ProgressPercentage = MAX_PERCENTAGE_CHECKS;
// TLogging.LogAtLevel(7, "TExtractsAddSubscriptionsUIConnector.SubmitChangesInternal: " + FAsyncExecProgress.ProgressInformation);
PSubscriptionAccess.SubmitChanges((PSubscriptionTable)FSubmissionDT, SubmitChangesTransaction);
}
else
{
TLogging.LogAtLevel(
7,
"TExtractsAddSubscriptionsUIConnector.SubmitChangesInternal: no Subscriptions were added to Partners because all the Partners in the Extract already had this Subscription.");
}
DBAccess.GDBAccessObj.CommitTransaction();
}
catch (Exception Exp)
{
DBAccess.GDBAccessObj.RollbackTransaction();
TLogging.LogAtLevel(7,
"TExtractsAddSubscriptionsUIConnector.SubmitChangesInternal: Exception occured, Transaction ROLLED BACK. Exception: " +
Exp.ToString());
FSubmitResult = TSubmitChangesResult.scrError;
FSubmitException = Exp;
FAsyncExecProgress.ProgressState = TAsyncExecProgressState.Aeps_Stopped;
return;
}
}
else
{
SubmissionResult = TSubmitChangesResult.scrNothingToBeSaved;
}
// if no values at response table, it needs to be created. If not creates, will raise exeption at client side.
if (FResponseDT == null)
{
FResponseDT = new DataTable();
}
FAsyncExecProgress.ProgressPercentage = 100;
FAsyncExecProgress.ProgressState = TAsyncExecProgressState.Aeps_Finished;
FSubmitResult = SubmissionResult;
}
public static PPartnerTable SharedBankAccountPartners(int ABankingDetailsKey, long APartnerKey)
{
PPartnerTable PartnerTable = new PPartnerTable();
TDBTransaction Transaction = null;
DBAccess.GDBAccessObj.BeginAutoReadTransaction(IsolationLevel.ReadCommitted,
ref Transaction,
delegate
{
PPartnerBankingDetailsTable PartnerBankingDetailsTable = PPartnerBankingDetailsAccess.LoadViaPBankingDetails(ABankingDetailsKey,
Transaction);
foreach (PPartnerBankingDetailsRow Row in PartnerBankingDetailsTable.Rows)
{
// if record exists with a different partner key then the Bank Account is shared
if (Row.PartnerKey != APartnerKey)
{
PPartnerRow PartnerRow = (PPartnerRow)PPartnerAccess.LoadByPrimaryKey(Row.PartnerKey, Transaction).Rows[0];
PPartnerRow NewRow = PartnerTable.NewRowTyped(false);
NewRow.PartnerKey = Row.PartnerKey;
NewRow.PartnerShortName = PartnerRow.PartnerShortName;
PartnerTable.Rows.Add(NewRow);
}
}
});
return PartnerTable;
}
private void SpecialPreSubmitProcessingPartner(PPartnerTable APartnerTableSubmitDT)
{
String CurrentPartnerStatus;
CurrentPartnerStatus = APartnerTableSubmitDT[0].StatusCode;
// New Partner or Partner Status changed?
if ((APartnerTableSubmitDT.Rows[0].RowState == DataRowState.Added)
|| (CurrentPartnerStatus != APartnerTableSubmitDT.Rows[0][PPartnerTable.GetStatusCodeDBName(), DataRowVersion.Original].ToString()))
{
if (CurrentPartnerStatus == SharedTypes.StdPartnerStatusCodeEnumToString(TStdPartnerStatusCode.spscPRIVATE))
{
// Partner Status is PRIVATE > update related fields
APartnerTableSubmitDT[0].Restricted = SharedConstants.PARTNER_PRIVATE_USER;
APartnerTableSubmitDT[0].UserId = UserInfo.GUserInfo.UserID;
}
else
{
// Partner Status is not PRIVATE > update related fields
APartnerTableSubmitDT[0].Restricted = 0;
APartnerTableSubmitDT[0].UserId = "";
}
// Business Rule: StatusChange needs to be set if this is a new Partner,
// or an existing Partner's PartnerStatus has changed
APartnerTableSubmitDT[0].StatusChange = DateTime.Today;
}
}
private void ShowDetailsManual(PBankingDetailsRow ARow)
{
if (ARow != null)
{
btnDelete.Enabled = true;
pnlDetails.Visible = true;
// set chkSavingsAccount
if (ARow.BankingType == MPartnerConstants.BANKINGTYPE_SAVINGSACCOUNT)
{
chkSavingsAccount.Checked = true;
}
else
{
chkSavingsAccount.Checked = false;
}
// BankKey will be 0 for a new bank account
if (ARow.BankKey == 0)
{
cmbBankName.SetSelectedString("");
cmbBankCode.SetSelectedString("");
}
else if (FComboBoxesCreated && ((FCurrentBankRow == null) || (ARow.BankKey != FCurrentBankRow.PartnerKey)))
{
PartnerKeyChanged(ARow.BankKey, "", true);
}
}
if (FPreviouslySelectedDetailRow != null)
{
// Find any Partners that share this bank account
FAccountSharedWith = TRemote.MPartner.Partner.WebConnectors.SharedBankAccountPartners(FPreviouslySelectedDetailRow.BankingDetailsKey,
FMainDS.PPartner[0].PartnerKey);
}
InitAccountSharedWithGrid();
// In theory, the next Method call could be done in Methods NewRowManual; however, NewRowManual runs before
// the Row is actually added and this would result in the Count to be one too less, so we do the Method call here, short
// of a non-existing 'AfterNewRowManual' Method....
DoRecalculateScreenParts();
}
/// <summary>
/// Performs Partner Status change promotion to Family Members.
///
/// </summary>
/// <param name="AInspectDS">the dataset that will be saved later to the database</param>
/// <param name="ANewPartnerStatusCode">The new Partner StatusCode</param>
/// <param name="APartnerTypeChangeFamilyMembersDT">DataTable holding the PartnerKeys of
/// Family Members (Note: These could be retrieved from the DB on-the-fly, but
/// we really want to be sure that the promotion is done to those Family
/// Members that we presented on the UI)</param>
/// <param name="ASubmitChangesTransaction">Running transaction in which the DB commands
/// will be enlisted</param>
/// <returns>true if processing was successful, otherwise false
/// </returns>
private Boolean SpecialSubmitProcessingPartnerStatusChange(
PartnerEditTDS AInspectDS,
String ANewPartnerStatusCode,
PartnerEditTDSFamilyMembersInfoForStatusChangeTable APartnerTypeChangeFamilyMembersDT,
TDBTransaction ASubmitChangesTransaction)
{
PPartnerTable PartnerSaveDT = new PPartnerTable();
// TLogging.LogAtLevel(7, "TPartnerEditUIConnector.SpecialSubmitProcessingPartnerStatusChange: processing " + APartnerTypeChangeFamilyMembersDT.Rows.Count.ToString() + " Partners...");
// Loop over all Family Members that were presented to the user
for (Int16 Counter = 0; Counter <= APartnerTypeChangeFamilyMembersDT.Rows.Count - 1; Counter += 1)
{
PartnerEditTDSFamilyMembersInfoForStatusChangeRow PartnerDR = APartnerTypeChangeFamilyMembersDT[Counter];
// Load Family Member's Partner record
PPartnerTable PartnerDT = PPartnerAccess.LoadByPrimaryKey(PartnerDR.PartnerKey, ASubmitChangesTransaction);
if (PartnerDT[0].StatusCode != ANewPartnerStatusCode)
{
// StatusCode of the Partner is different to the new StatusCode > change it
PPartnerRow PartnerSaveDR = PartnerSaveDT.NewRowTyped(false);
PartnerSaveDR.ItemArray = PartnerDT[0].ItemArray;
// Add Partner DataRow to DataTable that contains the Partners that will be saved
PartnerSaveDT.Rows.Add(PartnerSaveDR);
// Change just added PartnerSaveDR's DataRowState from 'Added' to 'Unchanged'
PartnerSaveDR.AcceptChanges();
// Apply new StatusCode (DataRowState changes to 'Modified')
PartnerSaveDR.StatusCode = ANewPartnerStatusCode;
PartnerSaveDR.StatusChange = DateTime.Now.Date;
}
}
AInspectDS.PPartner.Merge(PartnerSaveDT);
return true;
}
public static void AddSubscription(int AExtractId, ref PSubscriptionTable ATable,
out PPartnerTable AExistingSubscriptionPartners, out int ASubscriptionsAdded)
{
PSubscriptionTable SubscriptionTable = new PSubscriptionTable();
PSubscriptionRow SubscriptionRowTemplate;
PSubscriptionRow SubscriptionRow;
MExtractTable ExtractTable;
PPartnerTable PartnerTable;
PPartnerRow PartnerRow;
// only use first row in table (as rows can't be serialized as parameters)
SubscriptionRowTemplate = (PSubscriptionRow)ATable.Rows[0];
AExistingSubscriptionPartners = new PPartnerTable();
ASubscriptionsAdded = 0;
TDBTransaction Transaction = DBAccess.GDBAccessObj.BeginTransaction(IsolationLevel.Serializable);
try
{
ExtractTable = MExtractAccess.LoadViaMExtractMaster(AExtractId, Transaction);
// query all rows of given extract
foreach (MExtractRow ExtractRow in ExtractTable.Rows)
{
// for each extract row either add subscription or add to list of partners already having one
if (PSubscriptionAccess.Exists(SubscriptionRowTemplate.PublicationCode, ExtractRow.PartnerKey, Transaction))
{
PartnerRow = AExistingSubscriptionPartners.NewRowTyped();
PartnerTable = PPartnerAccess.LoadByPrimaryKey(ExtractRow.PartnerKey, Transaction);
DataUtilities.CopyAllColumnValues(PartnerTable.Rows[0], PartnerRow);
AExistingSubscriptionPartners.Rows.Add(PartnerRow);
}
else
{
SubscriptionRow = SubscriptionTable.NewRowTyped();
DataUtilities.CopyAllColumnValues(SubscriptionRowTemplate, SubscriptionRow);
SubscriptionRow.PartnerKey = ExtractRow.PartnerKey;
SubscriptionTable.Rows.Add(SubscriptionRow);
ASubscriptionsAdded++;
}
}
// now submit changes to the database
PSubscriptionAccess.SubmitChanges(SubscriptionTable, Transaction);
DBAccess.GDBAccessObj.CommitTransaction();
}
catch (Exception Exc)
{
TLogging.Log(
"An Exception occured during the adding of subscriptions for all Partners in an Extract:" + Environment.NewLine + Exc.ToString());
DBAccess.GDBAccessObj.RollbackTransaction();
throw;
}
}
/// <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();
}
public static PPartnerTable SharedBankAccountPartners(int ABankingDetailsKey, long APartnerKey)
{
TDBTransaction ReadTransaction;
Boolean NewTransaction;
PPartnerTable PartnerTable = new PPartnerTable();
ReadTransaction = DBAccess.GDBAccessObj.GetNewOrExistingTransaction(IsolationLevel.ReadCommitted,
TEnforceIsolationLevel.eilMinimum, out NewTransaction);
try
{
PPartnerBankingDetailsTable PartnerBankingDetailsTable = PPartnerBankingDetailsAccess.LoadViaPBankingDetails(ABankingDetailsKey,
ReadTransaction);
foreach (PPartnerBankingDetailsRow Row in PartnerBankingDetailsTable.Rows)
{
// if record exists with a different partner key then the Bank Account is shared
if (Row.PartnerKey != APartnerKey)
{
PPartnerRow PartnerRow = (PPartnerRow)PPartnerAccess.LoadByPrimaryKey(Row.PartnerKey, ReadTransaction).Rows[0];
PPartnerRow NewRow = PartnerTable.NewRowTyped(false);
NewRow.PartnerKey = Row.PartnerKey;
NewRow.PartnerShortName = PartnerRow.PartnerShortName;
PartnerTable.Rows.Add(NewRow);
}
}
}
finally
{
if (NewTransaction)
{
DBAccess.GDBAccessObj.CommitTransaction();
TLogging.LogAtLevel(7, "TPartnerDataReaderWebConnector.IsBankingDetailsRowShared: committed own transaction.");
}
}
return PartnerTable;
}
public static String ExportAllPartnersExt()
{
TDBTransaction ReadTransaction = null;
String ExtText = GetExtFileHeader();
TPartnerFileExport Exporter = new TPartnerFileExport();
PartnerImportExportTDS MainDS;
PPartnerTable Partners = new PPartnerTable();
DBAccess.GDBAccessObj.GetNewOrExistingAutoReadTransaction(IsolationLevel.ReadCommitted, TEnforceIsolationLevel.eilMinimum,
ref ReadTransaction,
delegate
{
Partners = PPartnerAccess.LoadAll(ReadTransaction);
});
foreach (PPartnerRow Partner in Partners.Rows)
{
if ((Partner.PartnerKey != 0) && (Partner.PartnerKey != 1000000)) // skip organization root and 0 when exporting
{
MainDS = TExportAllPartnerData.ExportPartner(Partner.PartnerKey);
ExtText += Exporter.ExportPartnerExt(MainDS, /*ASiteKey*/ 0, /*ALocationKey*/ 0, null);
}
}
ExtText += GetExtFileFooter();
return ExtText;
}
public static Boolean GetRecentlyUsedPartners(int AMaxPartnersCount,
ArrayList APartnerClasses,
out Dictionary <long, string>ARecentlyUsedPartners)
{
TDBTransaction ReadTransaction;
Boolean NewTransaction;
ARecentlyUsedPartners = new Dictionary <long, string>();
TLogging.LogAtLevel(9, "TPartnerServerLookups.GetRecentlyUsedPartner called!");
PRecentPartnersTable RecentPartnersDT = new PRecentPartnersTable();
ReadTransaction = DBAccess.GDBAccessObj.GetNewOrExistingTransaction(IsolationLevel.ReadCommitted,
TEnforceIsolationLevel.eilMinimum,
out NewTransaction);
// Load the recently used partners from this user
try
{
RecentPartnersDT = PRecentPartnersAccess.LoadViaSUser(UserInfo.GUserInfo.UserID, ReadTransaction);
}
finally
{
if (NewTransaction)
{
DBAccess.GDBAccessObj.CommitTransaction();
TLogging.LogAtLevel(7, "TPartnerServerLookups.GetRecentUsedPartners: committed own transaction.");
}
}
PPartnerTable PartnerDT = new PPartnerTable();
// Sort the users by date and time they have been last used
System.Data.DataRow[] RecentPartnerRows = RecentPartnersDT.Select("",
TTypedDataTable.GetLabel(PRecentPartnersTable.TableId, PRecentPartnersTable.ColumnwhenDateId) + " DESC, " +
TTypedDataTable.GetLabel(PRecentPartnersTable.TableId, PRecentPartnersTable.ColumnwhenTimeId) + " DESC");
for (int Counter = 0; Counter < RecentPartnersDT.Rows.Count; ++Counter)
{
PRecentPartnersRow RecentPartnerRow = (PRecentPartnersRow)RecentPartnerRows[Counter];
ReadTransaction = DBAccess.GDBAccessObj.GetNewOrExistingTransaction(
IsolationLevel.ReadCommitted,
TEnforceIsolationLevel.eilMinimum,
out NewTransaction);
// Get the partner name from the recently used partner
try
{
PartnerDT = PPartnerAccess.LoadByPrimaryKey(RecentPartnerRow.PartnerKey, ReadTransaction);
}
finally
{
if (NewTransaction)
{
DBAccess.GDBAccessObj.CommitTransaction();
TLogging.LogAtLevel(7, "TPartnerServerLookups.GetRecentUsedPartners: committed own transaction.");
}
}
if (PartnerDT.Rows.Count > 0)
{
/* Check the partner class.
* If we want this partner then add it to the ARecentlyUsedPartners list
* otherwise skip it.
*/
PPartnerRow PartnerRow = (PPartnerRow)PartnerDT.Rows[0];
foreach (Object CurrentPartnerClass in APartnerClasses)
{
string TmpString = CurrentPartnerClass.ToString();
if ((TmpString == "*")
|| (TmpString == PartnerRow.PartnerClass))
{
// String contains Name and type like this: J. Miller (type FAMILY)
ARecentlyUsedPartners.Add(PartnerRow.PartnerKey,
PartnerRow.PartnerShortName + " (type " + PartnerRow.PartnerClass + ")");
}
}
}
if (ARecentlyUsedPartners.Count >= AMaxPartnersCount)
{
break;
}
}
return true;
}
public static void ChangeSubscription(int AExtractId, ref PSubscriptionTable ATable,
List <String>AFieldsToChange, out PPartnerTable APartnersWithoutSubscription, out int ASubscriptionsChanged)
{
PSubscriptionTable SubscriptionTable = new PSubscriptionTable();
PSubscriptionRow SubscriptionRowTemplate;
PSubscriptionRow SubscriptionRow;
MExtractTable ExtractTable;
PPartnerTable PartnerTable;
PPartnerRow PartnerRow;
int SubscriptionsChanged;
PPartnerTable PartnersWithoutSubscription;
// only use first row in table (as rows can't be serialized as parameters)
SubscriptionRowTemplate = (PSubscriptionRow)ATable.Rows[0];
PartnersWithoutSubscription = new PPartnerTable();
SubscriptionsChanged = 0;
TDBTransaction Transaction = null;
bool SubmissionOK = false;
DBAccess.GDBAccessObj.BeginAutoTransaction(IsolationLevel.Serializable,
ref Transaction,
ref SubmissionOK,
delegate
{
ExtractTable = MExtractAccess.LoadViaMExtractMaster(AExtractId, Transaction);
// query all rows of given extract
foreach (MExtractRow ExtractRow in ExtractTable.Rows)
{
// for each extract row either change subscription or add to list of partners that don't have one
if (PSubscriptionAccess.Exists(SubscriptionRowTemplate.PublicationCode, ExtractRow.PartnerKey, Transaction))
{
SubscriptionTable = PSubscriptionAccess.LoadByPrimaryKey(SubscriptionRowTemplate.PublicationCode,
ExtractRow.PartnerKey,
Transaction);
SubscriptionRow = (PSubscriptionRow)SubscriptionTable.Rows[0];
// change field contents
if (AFieldsToChange.Contains(PSubscriptionTable.GetSubscriptionStatusDBName()))
{
SubscriptionRow.SubscriptionStatus = SubscriptionRowTemplate.SubscriptionStatus;
}
if (AFieldsToChange.Contains(PSubscriptionTable.GetGratisSubscriptionDBName()))
{
SubscriptionRow.GratisSubscription = SubscriptionRowTemplate.GratisSubscription;
}
if (AFieldsToChange.Contains(PSubscriptionTable.GetNumberComplimentaryDBName()))
{
SubscriptionRow.NumberComplimentary = SubscriptionRowTemplate.NumberComplimentary;
}
if (AFieldsToChange.Contains(PSubscriptionTable.GetPublicationCopiesDBName()))
{
SubscriptionRow.PublicationCopies = SubscriptionRowTemplate.PublicationCopies;
}
if (AFieldsToChange.Contains(PSubscriptionTable.GetReasonSubsGivenCodeDBName()))
{
SubscriptionRow.ReasonSubsGivenCode = SubscriptionRowTemplate.ReasonSubsGivenCode;
}
if (AFieldsToChange.Contains(PSubscriptionTable.GetReasonSubsCancelledCodeDBName()))
{
SubscriptionRow.ReasonSubsCancelledCode = SubscriptionRowTemplate.ReasonSubsCancelledCode;
}
if (AFieldsToChange.Contains(PSubscriptionTable.GetGiftFromKeyDBName()))
{
SubscriptionRow.GiftFromKey = SubscriptionRowTemplate.GiftFromKey;
}
if (AFieldsToChange.Contains(PSubscriptionTable.GetStartDateDBName()))
{
SubscriptionRow.StartDate = SubscriptionRowTemplate.StartDate;
}
if (AFieldsToChange.Contains(PSubscriptionTable.GetExpiryDateDBName()))
{
SubscriptionRow.ExpiryDate = SubscriptionRowTemplate.ExpiryDate;
}
if (AFieldsToChange.Contains(PSubscriptionTable.GetSubscriptionRenewalDateDBName()))
{
SubscriptionRow.SubscriptionRenewalDate = SubscriptionRowTemplate.SubscriptionRenewalDate;
}
if (AFieldsToChange.Contains(PSubscriptionTable.GetDateNoticeSentDBName()))
{
SubscriptionRow.DateNoticeSent = SubscriptionRowTemplate.DateNoticeSent;
}
if (AFieldsToChange.Contains(PSubscriptionTable.GetDateCancelledDBName()))
{
SubscriptionRow.DateCancelled = SubscriptionRowTemplate.DateCancelled;
}
if (AFieldsToChange.Contains(PSubscriptionTable.GetNumberIssuesReceivedDBName()))
{
SubscriptionRow.NumberIssuesReceived = SubscriptionRowTemplate.NumberIssuesReceived;
}
if (AFieldsToChange.Contains(PSubscriptionTable.GetFirstIssueDBName()))
{
SubscriptionRow.FirstIssue = SubscriptionRowTemplate.FirstIssue;
}
if (AFieldsToChange.Contains(PSubscriptionTable.GetLastIssueDBName()))
{
SubscriptionRow.LastIssue = SubscriptionRowTemplate.LastIssue;
}
// submit changes to the database after each row
PSubscriptionAccess.SubmitChanges(SubscriptionTable, Transaction);
//SubscriptionTable.Rows.Add(SubscriptionRow);
SubscriptionsChanged++;
}
else
{
// this partner does not have given subscription, therefore it cannot be changed
PartnerRow = PartnersWithoutSubscription.NewRowTyped();
PartnerTable = PPartnerAccess.LoadByPrimaryKey(ExtractRow.PartnerKey, Transaction);
DataUtilities.CopyAllColumnValues(PartnerTable.Rows[0], PartnerRow);
PartnersWithoutSubscription.Rows.Add(PartnerRow);
}
}
SubmissionOK = true;
});
APartnersWithoutSubscription = PartnersWithoutSubscription;
ASubscriptionsChanged = SubscriptionsChanged;
}
/// <summary>
/// Change subscription for Partners in selected Extract
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
public void ChangeSubscription(System.Object sender, EventArgs e)
{
PSubscriptionTable SubscriptionTable = new PSubscriptionTable();
PSubscriptionRow SubscriptionRow = SubscriptionTable.NewRowTyped();
PPartnerTable PartnersWithoutSubs = new PPartnerTable();
int SubscriptionsChanged;
String MessageText;
List <String>FieldsToChange = new List <string>();
if (!WarnIfNotSingleSelection(Catalog.GetString("Add Subscription"))
&& (GetSelectedDetailRow() != null))
{
TFrmUpdateExtractChangeSubscriptionDialog dialog = new TFrmUpdateExtractChangeSubscriptionDialog(this.FindForm());
dialog.SetExtractName(GetSelectedDetailRow().ExtractName);
if (dialog.ShowDialog() == System.Windows.Forms.DialogResult.OK)
{
if (dialog.GetReturnedParameters(ref SubscriptionRow, ref FieldsToChange))
{
SubscriptionTable.Rows.Add(SubscriptionRow);
// perform update of extract data on server side
TRemote.MPartner.Partner.WebConnectors.ChangeSubscription
(GetSelectedDetailRow().ExtractId, ref SubscriptionTable, FieldsToChange, out PartnersWithoutSubs,
out SubscriptionsChanged);
MessageText =
String.Format(Catalog.GetString(
"Subscription {0} successfully changed for {1} out of {2} Partner(s) in Extract {3}."),
SubscriptionRow.PublicationCode,
SubscriptionsChanged, GetSelectedDetailRow().KeyCount, GetSelectedDetailRow().ExtractName);
if (PartnersWithoutSubs.Rows.Count > 0)
{
MessageText += "\r\n\r\n" +
String.Format(Catalog.GetString(
"See the following Dialog for the {0} Partner(s) that are not subscribed for this Publication and therefore no change was made for them."),
PartnersWithoutSubs.Rows.Count);
}
MessageBox.Show(MessageText,
Catalog.GetString("Change Subscription"),
MessageBoxButtons.OK,
MessageBoxIcon.Information);
if (PartnersWithoutSubs.Rows.Count > 0)
{
TFrmSimplePartnerListDialog partnerDialog = new TFrmSimplePartnerListDialog(this.FindForm());
partnerDialog.SetExplanation("These partners do not have a Subscription for " + SubscriptionRow.PublicationCode,
"The Subscription was therefore not changed for the following Partners:");
partnerDialog.SetPartnerList(PartnersWithoutSubs);
partnerDialog.ShowDialog();
}
}
}
}
}
/// <summary>
/// get the number and name of the registration offices that the current user has access for
/// </summary>
/// <returns></returns>
public static PPartnerTable GetRegistrationOffices()
{
bool NewTransaction;
TDBTransaction Transaction = DBAccess.GDBAccessObj.GetNewOrExistingTransaction(IsolationLevel.ReadCommitted, out NewTransaction);
PPartnerTable result = new PPartnerTable();
try
{
List <Int64>offices = GetRegistrationOfficeKeysOfUser(Transaction);
StringCollection FieldList = new StringCollection();
FieldList.Add(PPartnerTable.GetPartnerKeyDBName());
FieldList.Add(PPartnerTable.GetPartnerShortNameDBName());
// get the short names of the registration offices
foreach (Int64 OfficeKey in offices)
{
PPartnerTable partnerTable = PPartnerAccess.LoadByPrimaryKey(OfficeKey, FieldList, Transaction);
result.Merge(partnerTable);
}
// remove unwanted columns
List <string>ColumnNames = new List <string>();
foreach (DataColumn column in result.Columns)
{
ColumnNames.Add(column.ColumnName);
}
foreach (string columnName in ColumnNames)
{
if (!FieldList.Contains(columnName))
{
result.Columns.Remove(columnName.ToString());
}
}
}
finally
{
if (NewTransaction)
{
DBAccess.GDBAccessObj.RollbackTransaction();
}
}
result.DefaultView.Sort = PPartnerTable.GetPartnerKeyDBName();
return result;
}
public static void AddSubscription(int AExtractId, ref PSubscriptionTable ATable,
out PPartnerTable AExistingSubscriptionPartners, out int ASubscriptionsAdded)
{
PSubscriptionTable SubscriptionTable = new PSubscriptionTable();
PSubscriptionRow SubscriptionRowTemplate;
PSubscriptionRow SubscriptionRow;
MExtractTable ExtractTable;
PPartnerTable PartnerTable;
PPartnerRow PartnerRow;
PPartnerTable ExistingSubscriptionPartners;
int SubscriptionsAdded = 0;
// only use first row in table (as rows can't be serialized as parameters)
SubscriptionRowTemplate = (PSubscriptionRow)ATable.Rows[0];
ExistingSubscriptionPartners = new PPartnerTable();
TDBTransaction Transaction = null;
bool SubmissionOK = false;
DBAccess.GDBAccessObj.BeginAutoTransaction(IsolationLevel.Serializable,
ref Transaction,
ref SubmissionOK,
delegate
{
ExtractTable = MExtractAccess.LoadViaMExtractMaster(AExtractId, Transaction);
// query all rows of given extract
foreach (MExtractRow ExtractRow in ExtractTable.Rows)
{
// for each extract row either add subscription or add to list of partners already having one
if (PSubscriptionAccess.Exists(SubscriptionRowTemplate.PublicationCode, ExtractRow.PartnerKey, Transaction))
{
PartnerRow = ExistingSubscriptionPartners.NewRowTyped();
PartnerTable = PPartnerAccess.LoadByPrimaryKey(ExtractRow.PartnerKey, Transaction);
DataUtilities.CopyAllColumnValues(PartnerTable.Rows[0], PartnerRow);
ExistingSubscriptionPartners.Rows.Add(PartnerRow);
}
else
{
SubscriptionRow = SubscriptionTable.NewRowTyped();
DataUtilities.CopyAllColumnValues(SubscriptionRowTemplate, SubscriptionRow);
SubscriptionRow.PartnerKey = ExtractRow.PartnerKey;
SubscriptionTable.Rows.Add(SubscriptionRow);
SubscriptionsAdded++;
}
}
// now submit changes to the database
PSubscriptionAccess.SubmitChanges(SubscriptionTable, Transaction);
SubmissionOK = true;
});
AExistingSubscriptionPartners = ExistingSubscriptionPartners;
ASubscriptionsAdded = SubscriptionsAdded;
}
