private void ValidateDataManual(PBankRow ARow)
{
TVerificationResultCollection VerificationResultCollection = FPetraUtilsObject.VerificationResultCollection;
TSharedPartnerValidation_Partner.ValidatePartnerBankManual(this, ARow, ref VerificationResultCollection,
FValidationControlsDict);
}
private void ValidateDataManual(PBankRow ARow)
{
TVerificationResultCollection VerificationResultCollection = FPetraUtilsObject.VerificationResultCollection;
TSharedPartnerValidation_Partner.ValidatePartnerBankManual(this, ARow, ref VerificationResultCollection,
FValidationControlsDict);
}
public static Int64 FindOrCreateBank(string ABIC, string ABankName)
{
Int64 PartnerKey = -1;
TDBTransaction Transaction = new TDBTransaction();
DBAccess.ReadTransaction(ref Transaction,
delegate
{
string sql = "SELECT p_partner_key_n FROM PUB_p_bank WHERE p_bic_c = ?";
OdbcParameter[] parameters = new OdbcParameter[1];
parameters[0] = new OdbcParameter("bic", OdbcType.VarChar);
parameters[0].Value = ABIC;
DataTable result = Transaction.DataBaseObj.SelectDT(sql, "bank", Transaction, parameters);
if (result.Rows.Count >= 1)
{
PartnerKey = Convert.ToInt64(result.Rows[0][0]);
}
});
if (PartnerKey != -1)
{
return(PartnerKey);
}
// we need to create a bank with this BIC
List <string> Subscriptions;
List <string> PartnerTypes;
string DefaultEmailAddress;
string DefaultPhoneMobile;
string DefaultPhoneLandline;
PartnerEditTDS MainDS = CreateNewPartner(MPartnerConstants.PARTNERCLASS_BANK,
out Subscriptions,
out PartnerTypes,
out DefaultEmailAddress,
out DefaultPhoneMobile,
out DefaultPhoneLandline);
PBankRow bankRow = MainDS.PBank[0];
bankRow.Bic = ABIC;
bankRow.BranchName = ABankName;
try
{
PartnerEditTDSAccess.SubmitChanges(MainDS);
return(bankRow.PartnerKey);
}
catch (Exception e)
{
TLogging.Log(e.ToString());
}
return(-1);
}
/// create a new bank
public static PPartnerRow CreateNewBankPartner(PartnerEditTDS AMainDS)
{
PPartnerRow PartnerRow = CreateNewPartner(AMainDS);
PartnerRow.PartnerClass = MPartnerConstants.PARTNERCLASS_BANK;
PartnerRow.PartnerShortName = PartnerRow.PartnerKey.ToString() + ", TestBank";
PBankRow BankRow = AMainDS.PBank.NewRowTyped();
BankRow.PartnerKey = PartnerRow.PartnerKey;
BankRow.BranchName = "TestBank";
AMainDS.PBank.Rows.Add(BankRow);
return(PartnerRow);
}
/// <summary>
/// generate the banks
/// </summary>
public static void GenerateBanks(string ABankCSVFile)
{
if (!File.Exists(ABankCSVFile))
{
TLogging.Log("there is no bank file " + ABankCSVFile);
return;
}
TLogging.Log("creating banks from file " + ABankCSVFile);
XmlDocument doc = TCsv2Xml.ParseCSV2Xml(ABankCSVFile, ",");
XmlNode RecordNode = doc.FirstChild.NextSibling.FirstChild;
PartnerImportExportTDS PartnerDS = new PartnerImportExportTDS();
Int32 NumberOfPartnerKeysReserved = 100;
Int64 NextPartnerKey = TNewPartnerKey.ReservePartnerKeys(-1, ref NumberOfPartnerKeysReserved);
while (RecordNode != null)
{
PBankRow BankRow = PartnerDS.PBank.NewRowTyped();
if (NumberOfPartnerKeysReserved == 0)
{
NumberOfPartnerKeysReserved = 100;
NextPartnerKey = TNewPartnerKey.ReservePartnerKeys(-1, ref NumberOfPartnerKeysReserved);
}
BankRow.PartnerKey = NextPartnerKey;
NextPartnerKey++;
NumberOfPartnerKeysReserved--;
BankRow.BranchName = TXMLParser.GetAttribute(RecordNode, "Branchname");
BankRow.BranchCode = TXMLParser.GetAttribute(RecordNode, "Branchcode");
BankRow.Bic = TXMLParser.GetAttribute(RecordNode, "Bic");
PartnerDS.PBank.Rows.Add(BankRow);
if (PartnerDS.PBank.Rows.Count % 1000 == 0)
{
TLogging.Log("created bank " + PartnerDS.PBank.Rows.Count.ToString() + " " + BankRow.BranchName);
}
PPartnerRow PartnerRow = PartnerDS.PPartner.NewRowTyped();
PartnerRow.PartnerKey = BankRow.PartnerKey;
PartnerRow.PartnerShortName = BankRow.BranchName;
PartnerRow.PartnerClass = MPartnerConstants.PARTNERCLASS_BANK;
PartnerRow.StatusCode = MPartnerConstants.PARTNERSTATUS_ACTIVE;
PartnerDS.PPartner.Rows.Add(PartnerRow);
// add empty location so that the partner can be found in the Partner Find screen
PPartnerLocationRow PartnerLocationRow = PartnerDS.PPartnerLocation.NewRowTyped();
PartnerLocationRow.PartnerKey = BankRow.PartnerKey;
PartnerLocationRow.LocationKey = 0;
PartnerLocationRow.SiteKey = 0;
PartnerDS.PPartnerLocation.Rows.Add(PartnerLocationRow);
RecordNode = RecordNode.NextSibling;
}
PartnerDS.ThrowAwayAfterSubmitChanges = true;
PartnerImportExportTDSAccess.SubmitChanges(PartnerDS);
TLogging.Log("after saving banks");
}
/// <summary>
/// Validates the Partner Detail data of a Partner of PartnerClass BANK.
/// </summary>
/// <param name="AContext">Context that describes where the data validation failed.</param>
/// <param name="ARow">The <see cref="DataRow" /> which holds the the data against which the validation is run.</param>
/// <param name="AVerificationResultCollection">Will be filled with any <see cref="TVerificationResult" /> items if
/// data validation errors occur.</param>
/// <param name="AValidationControlsDict">A <see cref="TValidationControlsDict" /> containing the Controls that
/// display data that is about to be validated.</param>
/// <returns>void</returns>
public static void ValidatePartnerBankManual(object AContext, PBankRow ARow,
ref TVerificationResultCollection AVerificationResultCollection, TValidationControlsDict AValidationControlsDict)
{
DataColumn ValidationColumn;
TValidationControlsData ValidationControlsData;
TVerificationResult VerificationResult;
// Don't validate deleted DataRows
if (ARow.RowState == DataRowState.Deleted)
{
return;
}
// 'BIC' (Bank Identifier Code) must be valid
ValidationColumn = ARow.Table.Columns[PBankTable.ColumnBicId];
if (AValidationControlsDict.TryGetValue(ValidationColumn, out ValidationControlsData))
{
if (CommonRoutines.CheckBIC(ARow.Bic) == false)
{
VerificationResult = new TScreenVerificationResult(new TVerificationResult(AContext,
ErrorCodes.GetErrorInfo(PetraErrorCodes.ERR_BANKBICSWIFTCODEINVALID, StrBICSwiftCodeInvalid)),
ValidationColumn, ValidationControlsData.ValidationControl);
}
else
{
VerificationResult = null;
}
// Handle addition/removal to/from TVerificationResultCollection
AVerificationResultCollection.Auto_Add_Or_AddOrRemove(AContext, VerificationResult, ValidationColumn);
}
// For information only: 'Branch Code' format matches the format of a BIC
ValidationColumn = ARow.Table.Columns[PBankTable.ColumnBranchCodeId];
if (AValidationControlsDict.TryGetValue(ValidationColumn, out ValidationControlsData))
{
if ((ARow.BranchCode != null)
&& (ARow.BranchCode != String.Empty))
{
if (CommonRoutines.CheckBIC(ARow.BranchCode) == true)
{
VerificationResult = new TScreenVerificationResult(new TVerificationResult(AContext,
ErrorCodes.GetErrorInfo(PetraErrorCodes.ERR_BRANCHCODELIKEBIC, String.Empty,
new String[] {
ValidationControlsData.ValidationControlLabel,
ValidationControlsData.SecondValidationControlLabel,
ValidationControlsData.ValidationControlLabel,
ValidationControlsData.ValidationControlLabel
},
new String[] { ValidationControlsData.ValidationControlLabel })),
ValidationColumn, ValidationControlsData.ValidationControl);
}
else
{
VerificationResult = null;
}
// Handle addition/removal to/from TVerificationResultCollection
AVerificationResultCollection.Auto_Add_Or_AddOrRemove(AContext, VerificationResult, ValidationColumn);
}
}
}
// when cmbBankCode is changed
private void BankCodeChanged(System.Object sender, EventArgs e)
{
if (string.IsNullOrEmpty(cmbBankCode.GetSelectedString()) && (FCurrentBankRow == null))
{
return;
}
else if ((string.IsNullOrEmpty(cmbBankCode.GetSelectedString()) && !string.IsNullOrEmpty(FCurrentBankRow.BranchCode))
|| ((cmbBankCode.GetSelectedString() == SharedConstants.INACTIVE_VALUE_WITH_QUALIFIERS + " ")
&& (FCurrentBankRow.BranchCode != SharedConstants.INACTIVE_VALUE_WITH_QUALIFIERS + " ")))
{
// if "<INACTIVE>" has been selected change it to blank
cmbBankCode.SelectedIndex = -1;
txtBankKey.Text = "0";
}
else if ((FCurrentBankRow == null) || (FCurrentBankRow.BranchCode != cmbBankCode.GetSelectedString()))
{
FCurrentBankRow = GetCurrentRow(Convert.ToInt64(cmbBankCode.GetSelectedDescription()));
// update partner key in txtBankKey
txtBankKey.Text = FCurrentBankRow.PartnerKey.ToString();
PartnerKeyChanged(FCurrentBankRow.PartnerKey, "", true);
}
}
// when cmbBankName is changed
private void BankNameChanged(System.Object sender, EventArgs e)
{
// if a blank name has just been selected
if (string.IsNullOrEmpty(cmbBankName.GetSelectedString())
&& ((FCurrentBankRow == null) || string.IsNullOrEmpty(FCurrentBankRow.BranchName)))
{
txtBankKey.Text = "0";
}
// cmbBankName.ContainsFocus is needed because the combobox automatically changes the selection
// to the first row with that name when the focus is left. This was a problem with multiple banks with the same name.
else if (((FCurrentBankRow == null) || (FCurrentBankRow.PartnerKey.ToString() != cmbBankName.GetSelectedString()))
&& (cmbBankName.GetSelectedString() != "")
&& cmbBankName.ContainsFocus)
{
FCurrentBankRow = GetCurrentRow(Convert.ToInt64(cmbBankName.GetSelectedString()));
// update partner key in txtBankKey
if (FCurrentBankRow != null)
{
txtBankKey.Text = FCurrentBankRow.PartnerKey.ToString();
PartnerKeyChanged(FCurrentBankRow.PartnerKey, "", true);
}
else
{
txtBankKey.Text = "0";
PartnerKeyChanged(0, "", true);
}
}
}
// called when FindBank dialog is accepted
private void PartnerKeyChanged(long APartnerKey, String APartnerShortName, bool AValidSelection)
{
if (!FComboBoxesCreated)
{
return;
}
if (FBankDataset == null)
{
// populate the comboboxes for Bank Name and Bank Code if not done already (this should never actually happen!)
PopulateBankComboBoxes();
}
FCurrentBankRow = GetCurrentRow(APartnerKey);
// set search criteria
FFindCriteriaDataTable.Rows[0]["BankKey"] = APartnerKey;
// change the BankName combo (if it was not the control used to change the bank)
if ((FCurrentBankRow != null) && (cmbBankName.GetSelectedString() != FCurrentBankRow.PartnerKey.ToString()))
{
// temporarily remove event
cmbBankName.SelectedValueChanged -= BankNameChanged;
cmbBankName.SetSelectedString(FCurrentBankRow.BranchName);
// If other banks have the same name then we must iterate through all banks to select the one we want
while (cmbBankName.GetSelectedString() != FCurrentBankRow.BranchName
&& cmbBankName.GetSelectedDescription() != FCurrentBankRow.BranchCode)
{
cmbBankName.cmbCombobox.SelectedIndex += 1;
}
cmbBankName.SelectedValueChanged += new System.EventHandler(this.BankNameChanged);
}
else if ((FCurrentBankRow == null) && (cmbBankName.GetSelectedString() != ""))
{
// temporarily remove event
cmbBankName.SelectedValueChanged -= BankNameChanged;
cmbBankName.SetSelectedString("");
cmbBankName.SelectedValueChanged += new System.EventHandler(this.BankNameChanged);
}
// change the BankCode combo (if it was not the control used to change the bank)
if ((FCurrentBankRow != null) && (cmbBankCode.GetSelectedString() != FCurrentBankRow.BranchCode))
{
cmbBankCode.SetSelectedString(FCurrentBankRow.BranchCode);
}
else if ((FCurrentBankRow == null) && (cmbBankCode.GetSelectedString() != ""))
{
// temporarily remove event
cmbBankCode.SelectedValueChanged -= BankCodeChanged;
cmbBankCode.SetSelectedString("");
cmbBankCode.SelectedValueChanged += new System.EventHandler(this.BankCodeChanged);
}
}
/// <summary>
/// return an XmlDocument with all partner info;
/// the partners are grouped by class, country, status, and sitekey
/// </summary>
/// <returns></returns>
public static string ExportPartners()
{
PartnerEditTDS MainDS = new PartnerEditTDS();
LoadDataFromDB(ref MainDS);
// Group partners into categories.
//
// A partner's category is defined by his: class, country, status, and sitekey
// It is stored as a string e.g. "FAMILY,DE,ACTIVE,0".
//
SortedList <string, List <long> > PartnerCategories = GroupPartnersIntoCategories(MainDS);
// create XML structure for each category
XmlDocument PartnerData = TYml2Xml.CreateXmlDocument();
XmlNode rootNode = PartnerData.FirstChild.NextSibling;
Int32 groupCounter = 0;
foreach (string category in PartnerCategories.Keys)
{
// get category data
groupCounter++;
XmlElement groupNode = PartnerData.CreateElement("PartnerGroup" + groupCounter.ToString());
rootNode.AppendChild(groupNode);
Int32 partnerCounter = 0;
string[] categoryDetails = category.Split(new char[] { ',' });
// may want to skip the categories with sitekey = -1
// right now, we still export them and ignore the partners 0 and 1000000 later
groupNode.SetAttribute("class", categoryDetails[0]);
groupNode.SetAttribute("Country", categoryDetails[1]);
groupNode.SetAttribute("status", categoryDetails[2]);
groupNode.SetAttribute("SiteKey", categoryDetails[3]);
List <long> partnerKeys = PartnerCategories[category];
foreach (long partnerKey in partnerKeys)
{
if ((partnerKey != 0) && (partnerKey != 1000000)) // skip organization root and the 0 when exporting
{
MainDS.PPartner.DefaultView.RowFilter = PPartnerTable.GetPartnerKeyDBName() + "=" + partnerKey.ToString();
PPartnerRow partnerRow = (PPartnerRow)MainDS.PPartner.DefaultView[0].Row;
PFamilyRow familyRow = null;
if (partnerRow.PartnerClass == MPartnerConstants.PARTNERCLASS_FAMILY)
{
MainDS.PFamily.DefaultView.RowFilter = PFamilyTable.GetPartnerKeyDBName() + "=" + partnerKey.ToString();
familyRow = (PFamilyRow)MainDS.PFamily.DefaultView[0].Row;
}
PPersonRow personRow = null;
if (partnerRow.PartnerClass == MPartnerConstants.PARTNERCLASS_PERSON)
{
MainDS.PPerson.DefaultView.RowFilter = PPersonTable.GetPartnerKeyDBName() + "=" + partnerKey.ToString();
personRow = (PPersonRow)MainDS.PPerson.DefaultView[0].Row;
}
POrganisationRow organisationRow = null;
if (partnerRow.PartnerClass == MPartnerConstants.PARTNERCLASS_ORGANISATION)
{
MainDS.POrganisation.DefaultView.RowFilter = POrganisationTable.GetPartnerKeyDBName() + "=" + partnerKey.ToString();
organisationRow = (POrganisationRow)MainDS.POrganisation.DefaultView[0].Row;
}
PUnitRow unitRow = null;
UmUnitStructureRow unitStructureRow = null;
if (partnerRow.PartnerClass == MPartnerConstants.PARTNERCLASS_UNIT)
{
MainDS.PUnit.DefaultView.RowFilter = PUnitTable.GetPartnerKeyDBName() + "=" + partnerKey.ToString();
unitRow = (PUnitRow)MainDS.PUnit.DefaultView[0].Row;
MainDS.UmUnitStructure.DefaultView.RowFilter = UmUnitStructureTable.GetChildUnitKeyDBName() + " = " + partnerKey.ToString();
long numParents = MainDS.UmUnitStructure.DefaultView.Count;
if (numParents == 1)
{
unitStructureRow = (UmUnitStructureRow)MainDS.UmUnitStructure.DefaultView[0].Row;
}
else
{
throw new Exception(
"Units must have exactly one ParentUnit. " +
"The unit with partnerKey " + partnerKey.ToString() + " has " +
numParents.ToString() + ".");
}
}
PBankRow BankRow = null;
if (partnerRow.PartnerClass == MPartnerConstants.PARTNERCLASS_BANK)
{
MainDS.PBank.DefaultView.RowFilter = PBankTable.GetPartnerKeyDBName() + "=" + partnerKey.ToString();
BankRow = (PBankRow)MainDS.PBank.DefaultView[0].Row;
}
partnerCounter++;
XmlElement partnerNode = PartnerData.CreateElement("Partner" + partnerCounter.ToString());
groupNode.AppendChild(partnerNode);
partnerNode.SetAttribute("PartnerKey", partnerRow.PartnerKey.ToString());
//groupNode.SetAttribute("ShortName", partnerRow.PartnerShortName.ToString());
if (personRow != null)
{
partnerNode.SetAttribute("FirstName", personRow.FirstName.ToString());
partnerNode.SetAttribute("LastName", personRow.FamilyName.ToString());
partnerNode.SetAttribute("Title", personRow.Title.ToString());
}
else if (familyRow != null)
{
partnerNode.SetAttribute("FirstName", familyRow.FirstName.ToString());
partnerNode.SetAttribute("LastName", familyRow.FamilyName.ToString());
partnerNode.SetAttribute("Title", familyRow.Title.ToString());
}
else if (organisationRow != null)
{
partnerNode.SetAttribute("Name", organisationRow.OrganisationName.ToString());
}
else if (unitRow != null)
{
partnerNode.SetAttribute("Name", unitRow.UnitName.ToString());
partnerNode.SetAttribute("UnitTypeCode", unitRow.UnitTypeCode.ToString());
if (unitStructureRow != null)
{
partnerNode.SetAttribute("ParentUnitKey", unitStructureRow.ParentUnitKey.ToString());
}
}
if (BankRow != null)
{
partnerNode.SetAttribute("BranchName", BankRow.BranchName);
partnerNode.SetAttribute("BranchCode", BankRow.BranchCode);
partnerNode.SetAttribute("BranchBic", BankRow.Bic);
partnerNode.SetAttribute("EpFormatFile", BankRow.EpFormatFile);
}
partnerNode.SetAttribute("CreatedAt", partnerRow.DateCreated.Value.ToString("yyyy-MM-dd HH:mm:ss"));
// special types
string specialTypes = "";
MainDS.PPartnerType.DefaultView.RowFilter = PPartnerTypeTable.GetPartnerKeyDBName() + "=" + partnerKey.ToString();
foreach (DataRowView rv in MainDS.PPartnerType.DefaultView)
{
if (specialTypes.Length > 0)
{
specialTypes += ", ";
}
specialTypes += ((PPartnerTypeRow)rv.Row).TypeCode;
}
if (specialTypes.Length > 0)
{
partnerNode.SetAttribute("SpecialTypes", specialTypes);
}
// addresses
DataView partnerLocationView = MainDS.PPartnerLocation.DefaultView;
partnerLocationView.RowFilter =
PPartnerLocationTable.GetPartnerKeyDBName() + " = " + partnerRow.PartnerKey.ToString() +
"AND " + PPartnerLocationTable.GetLocationKeyDBName() + " <> 0 "; // ignore invalid addresses
Int32 addressCounter = 0;
foreach (DataRowView rv in partnerLocationView)
{
XmlElement addressNode = PartnerData.CreateElement("Address" + (addressCounter > 0 ? addressCounter.ToString() : ""));
addressCounter++;
partnerNode.AppendChild(addressNode);
PPartnerLocationRow partnerLocationRow = (PPartnerLocationRow)rv.Row;
DataView locationView = MainDS.PLocation.DefaultView;
locationView.RowFilter =
PLocationTable.GetSiteKeyDBName() + "=" + partnerLocationRow.SiteKey.ToString() + " AND " +
PLocationTable.GetLocationKeyDBName() + "=" + partnerLocationRow.LocationKey.ToString();
if (locationView.Count > 0)
{
PLocationRow locationRow = (PLocationRow)locationView[0].Row;
addressNode.SetAttribute("Street", locationRow.StreetName);
addressNode.SetAttribute("City", locationRow.City);
addressNode.SetAttribute("PostCode", locationRow.PostalCode);
}
addressNode.SetAttribute("Email", partnerLocationRow.EmailAddress);
addressNode.SetAttribute("Phone", partnerLocationRow.TelephoneNumber);
addressNode.SetAttribute("MobilePhone", partnerLocationRow.MobileNumber);
}
// TODO: notes
// TODO: This doesn't export as much data as it should?
}
}
}
return(TXMLParser.XmlToString(PartnerData));
}
private static void ParseFinancialDetails(PartnerImportExportTDS AMainDS,
XmlNode AFinancialDetailsNode,
Int64 APartnerKey,
TDBTransaction ATransaction)
{
if (AFinancialDetailsNode != null)
{
string BankAccountNumber = TYml2Xml.GetAttributeRecursive(AFinancialDetailsNode, "AccountNumber");
string BankSortCode = TYml2Xml.GetAttributeRecursive(AFinancialDetailsNode, "BankSortCode");
// do we already have a bank with this sort code?
Int64 bankPartnerKey = 0;
AMainDS.PBank.DefaultView.Sort = PBankTable.GetBranchCodeDBName();
int bankIndex = AMainDS.PBank.DefaultView.Find(BankSortCode);
if (bankIndex != -1)
{
bankPartnerKey = ((PBankRow)AMainDS.PBank.DefaultView[bankIndex].Row).PartnerKey;
}
if (bankPartnerKey == 0)
{
string sqlFindBankBySortCode =
String.Format("SELECT * FROM PUB_{0} WHERE {1}=?",
PBankTable.GetTableDBName(),
PBankTable.GetBranchCodeDBName());
OdbcParameter param = new OdbcParameter("branchcode", OdbcType.VarChar);
param.Value = BankSortCode;
PBankTable bank = new PBankTable();
DBAccess.GDBAccessObj.SelectDT(bank, sqlFindBankBySortCode, ATransaction, new OdbcParameter[] {
param
}, -1, -1);
if (bank.Count > 0)
{
bankPartnerKey = bank[0].PartnerKey;
}
}
if (bankPartnerKey == 0)
{
// create a new bank record
PBankRow bankRow = AMainDS.PBank.NewRowTyped(true);
bankRow.PartnerKey = TImportExportYml.NewPartnerKey;
TImportExportYml.NewPartnerKey--;
bankRow.BranchCode = BankSortCode;
bankRow.BranchName = BankSortCode;
AMainDS.PBank.Rows.Add(bankRow);
bankPartnerKey = bankRow.PartnerKey;
}
PBankingDetailsRow bankingDetailsRow = AMainDS.PBankingDetails.NewRowTyped(true);
bankingDetailsRow.BankingDetailsKey = (AMainDS.PBankingDetails.Rows.Count + 1) * -1;
bankingDetailsRow.BankingType = 0;
bankingDetailsRow.BankAccountNumber = BankAccountNumber;
bankingDetailsRow.BankKey = bankPartnerKey;
AMainDS.PBankingDetails.Rows.Add(bankingDetailsRow);
PPartnerBankingDetailsRow partnerBankingDetailsRow = AMainDS.PPartnerBankingDetails.NewRowTyped(true);
partnerBankingDetailsRow.PartnerKey = APartnerKey;
partnerBankingDetailsRow.BankingDetailsKey = bankingDetailsRow.BankingDetailsKey;
AMainDS.PPartnerBankingDetails.Rows.Add(partnerBankingDetailsRow);
}
}
