public static DataTable GetPostCodeRegionsAndRanges()
{
List <OdbcParameter> SqlParameterList = new List <OdbcParameter>();
DataTable RegionsRangesTable = new DataTable();
if (TLogging.DL >= 9)
{
Console.WriteLine("GetPostCodeRegionsAndRanges called!");
}
TDBTransaction Transaction = new TDBTransaction();
DBAccess.ReadTransaction(
ref Transaction,
delegate
{
string SqlStmt =
"SELECT " + PPostcodeRegionRangeTable.GetTableDBName() + "." + PPostcodeRegionRangeTable.GetRegionDBName() +
", " + PPostcodeRegionTable.GetDescriptionDBName() + ", " +
PPostcodeRegionRangeTable.GetTableDBName() + "." + PPostcodeRegionRangeTable.GetRangeDBName() + ", " +
PPostcodeRangeTable.GetFromDBName() + ", " + PPostcodeRangeTable.GetToDBName() +
" FROM " + PPostcodeRegionRangeTable.GetTableDBName() + " INNER JOIN " + PPostcodeRangeTable.GetTableDBName() + " ON " +
PPostcodeRegionRangeTable.GetTableDBName() + "." + PPostcodeRegionRangeTable.GetRangeDBName() + " = " +
PPostcodeRangeTable.GetTableDBName() + "." + PPostcodeRangeTable.GetRangeDBName() + " INNER JOIN " +
PPostcodeRegionTable.GetTableDBName() +
" ON " + PPostcodeRegionTable.GetTableDBName() + "." + PPostcodeRegionTable.GetRegionDBName() + " = " +
PPostcodeRegionRangeTable.GetTableDBName() + "." + PPostcodeRegionRangeTable.GetRegionDBName() +
" ORDER BY " + PPostcodeRegionTable.GetRegionDBName();
RegionsRangesTable = Transaction.DataBaseObj.SelectDT(SqlStmt, "events",
Transaction, SqlParameterList.ToArray());
});
return(RegionsRangesTable);
}
// check if a postcode is contained within a region
private static bool PostcodeInRegion(string APostcode, string APostalRegion)
{
// Regions datatable should only be loaded once per extract generation
if (FPostalRegion != APostalRegion)
{
TDBTransaction ReadTransaction = null;
DBAccess.GDBAccessObj.GetNewOrExistingAutoReadTransaction(
MCommonConstants.CACHEABLEDT_ISOLATIONLEVEL, TEnforceIsolationLevel.eilMinimum, ref ReadTransaction,
delegate
{
FPostcodeRangeTable = PPostcodeRangeAccess.LoadViaPPostcodeRegion(APostalRegion, ReadTransaction);
FPostalRegion = APostalRegion;
});
}
foreach (PPostcodeRangeRow Row in FPostcodeRangeTable.Rows)
{
if ((ComparePostcodes(Row.From.ToLower(), APostcode.ToLower()) <= 0) &&
(ComparePostcodes(Row.To.ToLower(), APostcode.ToLower()) >= 0))
{
return(true);
}
}
return(false);
}
// check if a postcode is contained within a region
private bool PostcodeInRegion(string APostcode, string APostalRegion)
{
// Regions datatable should only be loaded once per extract generation
if (FPostalRegion != APostalRegion)
{
TDBTransaction ReadTransaction = new TDBTransaction();
DBAccess.ReadTransaction(
ref ReadTransaction,
delegate
{
FPostcodeRangeTable = PPostcodeRangeAccess.LoadViaPPostcodeRegion(APostalRegion, ReadTransaction);
FPostalRegion = APostalRegion;
});
}
foreach (PPostcodeRangeRow Row in FPostcodeRangeTable.Rows)
{
if ((ComparePostcodes(Row.From.ToLower(), APostcode.ToLower()) <= 0) &&
(ComparePostcodes(Row.To.ToLower(), APostcode.ToLower()) >= 0))
{
return(true);
}
}
return(false);
}
private void InitializeManualCode()
{
Type DataTableType;
DataTable CacheRegionDT =
TDataCache.GetCacheableDataTableFromCache("PostcodeRegionList", String.Empty, null, out DataTableType);
DataTable CacheRegionRangeDT =
TDataCache.GetCacheableDataTableFromCache("PostcodeRegionRangeList", String.Empty, null, out DataTableType);
PPostcodeRangeTable RangeTable =
(PPostcodeRangeTable)TDataCache.GetCacheableDataTableFromCache("PostcodeRangeList", String.Empty, null, out DataTableType);
FMainDS.PPostcodeRegion.Merge(CacheRegionDT);
FMainDS.PPostcodeRegionRange.Merge(CacheRegionRangeDT);
foreach (PostcodeRegionsTDSPPostcodeRegionRangeRow Row in FMainDS.PPostcodeRegionRange.Rows)
{
PPostcodeRangeRow RangeRow = (PPostcodeRangeRow)RangeTable.Rows.Find(new object[] { Row.Range });
Row.From = RangeRow.From;
Row.To = RangeRow.To;
}
FMainDS.AcceptChanges();
FPetraUtilsObject.ApplySecurity(TSecurityChecks.SecurityPermissionsSetupScreensEditingAndSaving);
}
/// <summary>
/// Opens a Modal instance of the Partner Find screen.
/// </summary>
/// <param name="ARegionName">Pass in the selected Region's name.</param>
/// <param name="ARangeName">Name of the found Range.</param>
/// <param name="AFrom">'From' field of the found Partner.</param>
/// <param name="ATo">'To' field of the found Partner.</param>
/// <param name="AParentForm"></param>
/// <returns>True if a Partner was found and accepted by the user,
/// otherwise false.</returns>
public static bool OpenModalForm(String ARegionName,
out String[] ARangeName,
out String[] AFrom,
out String[] ATo,
Form AParentForm)
{
DialogResult dlgResult;
ARangeName = null;
AFrom = null;
ATo = null;
TFrmPostcodeRangeSetup SelectRange = new TFrmPostcodeRangeSetup(AParentForm);
SelectRange.SetParameters(ARegionName);
dlgResult = SelectRange.ShowDialog();
if (dlgResult == DialogResult.OK)
{
DataRowView[] HighlightedRows = SelectRange.GetSelectedRows();
int NumberOfRows = HighlightedRows.Length;
PPostcodeRangeTable CachedRangeTable = (PPostcodeRangeTable)TDataCache.GetCacheableDataTableFromCache("PostcodeRangeList");
bool NoRangesSelected = true;
ARangeName = new string[NumberOfRows];
AFrom = new string[NumberOfRows];
ATo = new string[NumberOfRows];
for (int i = 0; i < NumberOfRows; i++)
{
if (CachedRangeTable.Rows.Find(new object[] { ((PPostcodeRangeRow)HighlightedRows[i].Row).Range }) != null)
{
ARangeName[i] = ((PPostcodeRangeRow)HighlightedRows[i].Row).Range;
AFrom[i] = ((PPostcodeRangeRow)HighlightedRows[i].Row).From;
ATo[i] = ((PPostcodeRangeRow)HighlightedRows[i].Row).To;
NoRangesSelected = false;
}
else
{
ARangeName[i] = null;
AFrom[i] = null;
ATo[i] = null;
}
}
if (NoRangesSelected)
{
MessageBox.Show(String.Format("No valid ranges have been selected."), String.Format("Add Range"),
MessageBoxButtons.OK, MessageBoxIcon.Information);
}
return(true);
}
return(false);
}
private void InitializeManualCode()
{
Type DataTableType;
DataTable CacheRegionDT =
TDataCache.GetCacheableDataTableFromCache("PostcodeRegionList", String.Empty, null, out DataTableType);
DataTable CacheRegionRangeDT =
TDataCache.GetCacheableDataTableFromCache("PostcodeRegionRangeList", String.Empty, null, out DataTableType);
PPostcodeRangeTable RangeTable =
(PPostcodeRangeTable)TDataCache.GetCacheableDataTableFromCache("PostcodeRangeList", String.Empty, null, out DataTableType);
FMainDS.PPostcodeRegion.Merge(CacheRegionDT);
FMainDS.PPostcodeRegionRange.Merge(CacheRegionRangeDT);
foreach (PostcodeRegionsTDSPPostcodeRegionRangeRow Row in FMainDS.PPostcodeRegionRange.Rows)
{
PPostcodeRangeRow RangeRow = (PPostcodeRangeRow)RangeTable.Rows.Find(new object[] { Row.Range });
Row.From = RangeRow.From;
Row.To = RangeRow.To;
}
FMainDS.AcceptChanges();
}
// check if a postcode is contained within a region
private static bool PostcodeInRegion(string APostcode, string APostalRegion)
{
// Regions datatable should only be loaded once per extract generation
if (FPostalRegion != APostalRegion)
{
TDBTransaction ReadTransaction = null;
DBAccess.GDBAccessObj.GetNewOrExistingAutoReadTransaction(
MCommonConstants.CACHEABLEDT_ISOLATIONLEVEL, TEnforceIsolationLevel.eilMinimum, ref ReadTransaction,
delegate
{
FPostcodeRangeTable = PPostcodeRangeAccess.LoadViaPPostcodeRegion(APostalRegion, ReadTransaction);
FPostalRegion = APostalRegion;
});
}
foreach (PPostcodeRangeRow Row in FPostcodeRangeTable.Rows)
{
if ((ComparePostcodes(Row.From.ToLower(), APostcode.ToLower()) <= 0)
&& (ComparePostcodes(Row.To.ToLower(), APostcode.ToLower()) >= 0))
{
return true;
}
}
return false;
}
