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); }
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); }