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>
/// this returns the default next available (highest) partner key of the given field
/// </summary>
/// <param name="AFieldPartnerKey">if this is -1, then the sitekey defined in System Parameters is used</param>
/// <returns>void</returns>
public static System.Int64 GetNewPartnerKey(System.Int64 AFieldPartnerKey)
{
PPartnerLedgerTable PartnerLedgerTable = null;
Int64 ReturnValue = -1;
if (AFieldPartnerKey == -1)
{
AFieldPartnerKey = DomainManager.GSiteKey;
}
TDBTransaction ReadTransaction = null;
DBAccess.GDBAccessObj.GetNewOrExistingAutoReadTransaction(IsolationLevel.RepeatableRead,
TEnforceIsolationLevel.eilMinimum, ref ReadTransaction,
delegate
{
PartnerLedgerTable = PPartnerLedgerAccess.LoadByPrimaryKey(AFieldPartnerKey, ReadTransaction);
ReturnValue = PartnerLedgerTable[0].PartnerKey + PartnerLedgerTable[0].LastPartnerId + 1;
// Now check that this does not exist, and increment until we
// find one which does not
while (PPartnerAccess.Exists(ReturnValue, ReadTransaction))
{
ReturnValue = ReturnValue + 1;
}
});
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);
}
/// <summary>
/// this returns the default next available (highest) partner key of the given field
/// </summary>
/// <param name="AFieldPartnerKey">if this is -1, then the sitekey defined in System Parameters is used</param>
/// <returns>void</returns>
public static System.Int64 GetNewPartnerKey(System.Int64 AFieldPartnerKey)
{
Boolean NewTransaction;
TDBTransaction ReadTransaction = DBAccess.GDBAccessObj.GetNewOrExistingTransaction(IsolationLevel.RepeatableRead,
TEnforceIsolationLevel.eilMinimum,
out NewTransaction);
if (AFieldPartnerKey == -1)
{
AFieldPartnerKey = DomainManager.GSiteKey;
}
PPartnerLedgerTable PartnerLedgerTable = PPartnerLedgerAccess.LoadByPrimaryKey(AFieldPartnerKey, ReadTransaction);
if (NewTransaction)
{
DBAccess.GDBAccessObj.CommitTransaction();
if (TLogging.DebugLevel >= TLogging.DEBUGLEVEL_TRACE)
{
Console.WriteLine("TNewPartnerKey.GetNewPartnerKey: committed own transaction.");
}
}
return(PartnerLedgerTable[0].PartnerKey + PartnerLedgerTable[0].LastPartnerId + 1);
}
/// <summary>
/// this returns the default next available (highest) partner key of the given field
/// </summary>
/// <param name="AFieldPartnerKey">if this is -1, then the sitekey defined in System Parameters is used</param>
/// <param name="ADataBase"></param>
/// <returns>void</returns>
public static System.Int64 GetNewPartnerKey(System.Int64 AFieldPartnerKey, TDataBase ADataBase = null)
{
PPartnerLedgerTable PartnerLedgerTable = null;
Int64 ReturnValue = -1;
if (AFieldPartnerKey == -1)
{
AFieldPartnerKey = DomainManager.GSiteKey;
}
TDBTransaction ReadTransaction = new TDBTransaction();
TDataBase db = DBAccess.Connect("GetNewPartnerKey", ADataBase);
db.ReadTransaction(ref ReadTransaction,
delegate
{
PartnerLedgerTable = PPartnerLedgerAccess.LoadByPrimaryKey(AFieldPartnerKey, ReadTransaction);
ReturnValue = PartnerLedgerTable[0].PartnerKey + PartnerLedgerTable[0].LastPartnerId + 1;
// Now check that this does not exist, and increment until we
// find one which does not
while (PPartnerAccess.Exists(ReturnValue, ReadTransaction))
{
ReturnValue = ReturnValue + 1;
}
});
if (ADataBase == null)
{
db.CloseDBConnection();
}
return(ReturnValue);
}
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>
/// reserve a number of partner keys, to be used by the calling function.
/// useful to create many partner at once, eg. for the demodata
/// </summary>
/// <param name="AFieldPartnerKey"></param>
/// <param name="ANumberOfKeys"></param>
/// <param name="ADataBase"></param>
/// <returns>the first valid partner key to use</returns>
public static System.Int64 ReservePartnerKeys(System.Int64 AFieldPartnerKey, ref Int32 ANumberOfKeys, TDataBase ADataBase = null)
{
Int64 NextPartnerKey = -1;
Int32 NumberOfKeys = ANumberOfKeys;
if (AFieldPartnerKey == -1)
{
AFieldPartnerKey = DomainManager.GSiteKey;
}
TDBTransaction ReadWriteTransaction = new TDBTransaction();
TDataBase db = DBAccess.Connect("ReservePartnerKeys", ADataBase);
bool SubmissionOK = true;
db.WriteTransaction(ref ReadWriteTransaction,
ref SubmissionOK,
delegate
{
PPartnerLedgerTable PartnerLedgerDT = PPartnerLedgerAccess.LoadByPrimaryKey(AFieldPartnerKey, ReadWriteTransaction);
NextPartnerKey = PartnerLedgerDT[0].PartnerKey + PartnerLedgerDT[0].LastPartnerId + 1;
Int64 NextUsedKey =
Convert.ToInt64(db.ExecuteScalar("SELECT MIN(p_partner_key_n) FROM PUB_p_partner WHERE p_partner_key_n >= " +
NextPartnerKey.ToString(), ReadWriteTransaction));
if (NextUsedKey < NextPartnerKey + NumberOfKeys)
{
NumberOfKeys = Convert.ToInt32(NextUsedKey - NextPartnerKey);
}
PartnerLedgerDT[0].LastPartnerId = Convert.ToInt32((NextPartnerKey + NumberOfKeys - 1) - PartnerLedgerDT[0].PartnerKey);
PPartnerLedgerAccess.SubmitChanges(PartnerLedgerDT, ReadWriteTransaction);
SubmissionOK = true;
});
if (ADataBase == null)
{
db.CloseDBConnection();
}
if (!SubmissionOK)
{
throw new Exception("ReservePartnerKeys failed");
}
ANumberOfKeys = NumberOfKeys;
return(NextPartnerKey);
}
/// <summary>
/// reserve a number of partner keys, to be used by the calling function.
/// useful to create many partner at once, eg. for the demodata
/// </summary>
/// <param name="AFieldPartnerKey"></param>
/// <param name="ANumberOfKeys"></param>
/// <returns>the first valid partner key to use</returns>
public static System.Int64 ReservePartnerKeys(System.Int64 AFieldPartnerKey, ref Int32 ANumberOfKeys)
{
Int64 NextPartnerKey = -1;
if (AFieldPartnerKey == -1)
{
AFieldPartnerKey = DomainManager.GSiteKey;
}
TDBTransaction ReadWriteTransaction = DBAccess.GDBAccessObj.BeginTransaction(IsolationLevel.Serializable);
try
{
PPartnerLedgerTable PartnerLedgerDT = PPartnerLedgerAccess.LoadByPrimaryKey(AFieldPartnerKey, ReadWriteTransaction);
NextPartnerKey = PartnerLedgerDT[0].PartnerKey + PartnerLedgerDT[0].LastPartnerId + 1;
Int64 NextUsedKey =
Convert.ToInt64(DBAccess.GDBAccessObj.ExecuteScalar("SELECT MIN(p_partner_key_n) FROM PUB_p_partner WHERE p_partner_key_n >= " +
NextPartnerKey.ToString(), ReadWriteTransaction));
if (NextUsedKey < NextPartnerKey + ANumberOfKeys)
{
ANumberOfKeys = Convert.ToInt32(NextUsedKey - NextPartnerKey);
}
PartnerLedgerDT[0].LastPartnerId = Convert.ToInt32((NextPartnerKey + ANumberOfKeys - 1) - PartnerLedgerDT[0].PartnerKey);
PPartnerLedgerAccess.SubmitChanges(PartnerLedgerDT, ReadWriteTransaction);
DBAccess.GDBAccessObj.CommitTransaction();
}
catch (Exception Exc)
{
TLogging.Log("An Exception occured during the reservation of a PartnerKey:" + Environment.NewLine + Exc.ToString());
DBAccess.GDBAccessObj.RollbackTransaction();
throw;
}
return(NextPartnerKey);
}
public static DataTable GetAvailableSites()
{
TDBTransaction ReadTransaction = new TDBTransaction();
DataTable SitesTable = new DataTable();
DataTable UnusedSitesTable = new DataTable();
DataTable UsedSitesTable = new DataTable();
DataRow SitesRow;
string IsPartnerLedger = SharedConstants.SYSMAN_AVAILABLE_SITES_COLUMN_IS_PARTNER_LEDGER;
string SiteKey = PUnitTable.GetPartnerKeyDBName();
string SiteShortName = PUnitTable.GetUnitNameDBName();
Int64 PartnerKey;
SitesTable.Columns.Add(new DataColumn(IsPartnerLedger, typeof(bool)));
SitesTable.Columns.Add(new DataColumn(SiteKey, typeof(Int64)));
SitesTable.Columns.Add(new DataColumn(SiteShortName, typeof(string)));
UnusedSitesTable.Columns.Add(new DataColumn(IsPartnerLedger, typeof(bool)));
UnusedSitesTable.Columns.Add(new DataColumn(SiteKey, typeof(Int64)));
UnusedSitesTable.Columns.Add(new DataColumn(SiteShortName, typeof(string)));
UsedSitesTable.Columns.Add(new DataColumn(IsPartnerLedger, typeof(bool)));
UsedSitesTable.Columns.Add(new DataColumn(SiteKey, typeof(Int64)));
UsedSitesTable.Columns.Add(new DataColumn(SiteShortName, typeof(string)));
DBAccess.ReadTransaction(
ref ReadTransaction,
delegate
{
try
{
// Load data
string SqlStmt = "SELECT pub_" + PUnitTable.GetTableDBName() + "." + PUnitTable.GetPartnerKeyDBName() +
", pub_" + PUnitTable.GetTableDBName() + "." + PUnitTable.GetUnitNameDBName() +
" FROM " + PUnitTable.GetTableDBName() + ", " + PPartnerTable.GetTableDBName() +
" WHERE ((" + PUnitTable.GetUnitTypeCodeDBName() + " = 'F')" +
" OR(" + PUnitTable.GetUnitTypeCodeDBName() + " = 'A'))" +
" AND pub_" + PUnitTable.GetTableDBName() + "." + PUnitTable.GetPartnerKeyDBName() +
" = pub_" + PPartnerTable.GetTableDBName() + "." + PPartnerTable.GetPartnerKeyDBName() +
" AND " + PPartnerTable.GetStatusCodeDBName() + " = 'ACTIVE'";
// sort rows according to name
SqlStmt = SqlStmt + " ORDER BY " + PUnitTable.GetUnitNameDBName();
DataTable sites = ReadTransaction.DataBaseObj.SelectDT(SqlStmt, "fields", ReadTransaction);
foreach (DataRow tempSiteRow in sites.Rows)
{
PartnerKey = Convert.ToInt64(tempSiteRow[0]);
// check if a site is already used in table p_partner_ledger
if (PPartnerLedgerAccess.CountViaPUnit(PartnerKey, ReadTransaction) > 0)
{
SitesRow = UsedSitesTable.NewRow();
SitesRow[IsPartnerLedger] = true;
SitesRow[SiteKey] = PartnerKey;
SitesRow[SiteShortName] = Convert.ToString(tempSiteRow[1]);
UsedSitesTable.Rows.Add(SitesRow);
}
else
{
SitesRow = UnusedSitesTable.NewRow();
SitesRow[IsPartnerLedger] = false;
SitesRow[SiteKey] = PartnerKey;
SitesRow[SiteShortName] = Convert.ToString(tempSiteRow[1]);
UnusedSitesTable.Rows.Add(SitesRow);
}
}
// first add used sites to table
foreach (DataRow tempSiteRow in UsedSitesTable.Rows)
{
SitesRow = SitesTable.NewRow();
SitesRow[IsPartnerLedger] = tempSiteRow[IsPartnerLedger];
SitesRow[SiteKey] = tempSiteRow[SiteKey];
SitesRow[SiteShortName] = tempSiteRow[SiteShortName];
SitesTable.Rows.Add(SitesRow);
}
// and now add unused sites to table
foreach (DataRow tempSiteRow in UnusedSitesTable.Rows)
{
SitesRow = SitesTable.NewRow();
SitesRow[IsPartnerLedger] = tempSiteRow[IsPartnerLedger];
SitesRow[SiteKey] = tempSiteRow[SiteKey];
SitesRow[SiteShortName] = tempSiteRow[SiteShortName];
SitesTable.Rows.Add(SitesRow);
}
}
catch (Exception e)
{
TLogging.Log(e.ToString());
}
});
return(SitesTable);
}
/// <summary>
/// this checks if the new key is still available,
/// and makes sure it will not be used as a default key anymore
/// </summary>
/// <param name="AFieldPartnerKey"></param>
/// <param name="AOriginalDefaultKey">this has been previously retrieved from GetNewPartnerKey</param>
/// <param name="ANewPartnerKey">the user proposes this key for a new partner; the function can change it and return a valid value, or -1</param>
/// <returns>whether or not ANewPartnerKey has a valid new partner key;
/// if it cannot be assigned, the function returns false, and ANewPartnerKey is -1
/// </returns>
public static bool SubmitNewPartnerKey(System.Int64 AFieldPartnerKey, System.Int64 AOriginalDefaultKey, ref System.Int64 ANewPartnerKey)
{
bool ReturnValue = true;
TDBTransaction ReadTransaction1;
TDBTransaction ReadTransaction2;
TDBTransaction WriteTransaction;
Boolean NewTransaction1;
Boolean NewTransaction2;
Boolean NewTransaction3;
PPartnerLedgerTable PartnerLedgerDT;
System.Int64 CurrentDefaultPartnerKey;
if (ANewPartnerKey == AOriginalDefaultKey)
{
// The user has selected the default
ReadTransaction1 = DBAccess.GDBAccessObj.GetNewOrExistingTransaction(IsolationLevel.RepeatableRead,
TEnforceIsolationLevel.eilMinimum, out NewTransaction1);
try
{
// Fetch the partner ledger record to update the last key
PartnerLedgerDT = PPartnerLedgerAccess.LoadByPrimaryKey(AFieldPartnerKey, ReadTransaction1);
CurrentDefaultPartnerKey = PartnerLedgerDT[0].PartnerKey + PartnerLedgerDT[0].LastPartnerId + 1;
if (ANewPartnerKey != CurrentDefaultPartnerKey)
{
// Someone else has updated this since, so we will use the new default
ANewPartnerKey = CurrentDefaultPartnerKey;
}
// Now check that this does not exist, and increment until we
// find one which does not
while (PPartnerAccess.Exists(ANewPartnerKey, ReadTransaction1))
{
ANewPartnerKey = ANewPartnerKey + 1;
}
}
finally
{
if (NewTransaction1)
{
DBAccess.GDBAccessObj.CommitTransaction();
if (TLogging.DebugLevel >= TLogging.DEBUGLEVEL_TRACE)
{
Console.WriteLine("TNewPartnerKey.SubmitNewPartnerKey: committed own transaction.");
}
}
}
PartnerLedgerDT[0].LastPartnerId = (int)(ANewPartnerKey - PartnerLedgerDT[0].PartnerKey);
WriteTransaction = DBAccess.GDBAccessObj.GetNewOrExistingTransaction(IsolationLevel.Serializable,
TEnforceIsolationLevel.eilMinimum, out NewTransaction2);
try
{
PPartnerLedgerAccess.SubmitChanges(PartnerLedgerDT, WriteTransaction);
if (NewTransaction2)
{
DBAccess.GDBAccessObj.CommitTransaction();
}
}
catch (Exception Exc)
{
TLogging.Log("An Exception occured during the submission of a new PartnerKey:" + Environment.NewLine + Exc.ToString());
if (NewTransaction2)
{
DBAccess.GDBAccessObj.RollbackTransaction();
}
throw;
}
}
// end of: The user has selected the default
else
{
ReadTransaction2 = DBAccess.GDBAccessObj.GetNewOrExistingTransaction(IsolationLevel.RepeatableRead,
TEnforceIsolationLevel.eilMinimum, out NewTransaction3);
try
{
// check if the Partner Key is already being used
if (PPartnerAccess.Exists(ANewPartnerKey, ReadTransaction2))
{
ANewPartnerKey = -1;
ReturnValue = false;
}
}
finally
{
if (NewTransaction3)
{
DBAccess.GDBAccessObj.CommitTransaction();
if (TLogging.DebugLevel >= TLogging.DEBUGLEVEL_TRACE)
{
Console.WriteLine("TNewPartnerKey.SubmitNewPartnerKey: committed own transaction.");
}
}
}
}
return(ReturnValue);
}
