private static void CalcChain(ExcelWorkbook wb, FormulaParser parser, DependencyChain dc, bool setResultSyle = false)
{
var debug = parser.Logger != null;
foreach (var ix in dc.CalcOrder)
{
var item = dc.List[ix];
try
{
var ws = wb.Worksheets.GetBySheetID(item.SheetID);
var v = parser.ParseCell(item.Tokens, ws == null ? "" : ws.Name, item.Row, item.Column, out DataType dataType);
if (v is IEnumerable enumerable && !(v is string))
{
v = enumerable.Cast <object>().FirstOrDefault();
}
CalculationExtension.SetValue(wb, item, v);
if (setResultSyle)
{
CalculationExtension.SetStyle(wb, item, dataType);
}
if (debug)
{
parser.Logger.LogCellCounted();
}
Thread.Sleep(0);
}
catch (Exception ex) when((ex is OperationCanceledException) == false)
{
parser.Logger?.Log(ex);
var error = ExcelErrorValue.Parse(ExcelErrorValue.Values.Value);
SetValue(wb, item, error);
}
}
}
private static void CalcChain(ExcelWorkbook wb, FormulaParser parser, DependencyChain dc)
{
var debug = parser.Logger != null;
foreach (var ix in dc.CalcOrder)
{
var item = dc.list[ix];
try
{
var ws = wb.Worksheets.GetBySheetID(item.SheetID);
var v = parser.ParseCell(item.Tokens, ws == null ? "" : ws.Name, item.Row, item.Column);
SetValue(wb, item, v);
if (debug)
{
parser.Logger.LogCellCounted();
}
Thread.Sleep(0);
}
catch (FormatException fe)
{
throw (fe);
}
catch (Exception e)
{
var error = ExcelErrorValue.Parse(ExcelErrorValue.Values.Value);
SetValue(wb, item, error);
}
}
}
internal async Task <IList <DependencyChain <AnalyzedPackage> > > FindPackageDependencies(IEnumerable <PackageRange> packages, ICollection <string> searchedRoots)
{
var packagesToResolved = packages
.Select(p => (Package: p, Resolved: this.versionResolver.GetSingleVersion(p)))
.ToList();
List <DependencyChain <AnalyzedPackage> > result = packagesToResolved
.Where(tuple => tuple.Resolved == null)
.Select(tuple => new InvalidDependencyChain(tuple.Package, $"Could not find a package with version {tuple.Package.VersionRange}."))
.Cast <DependencyChain <AnalyzedPackage> >()
.ToList();
var validPackages = packagesToResolved.Select(t => t.Resolved).ToArray();
Task.WaitAll(validPackages);
var valid = validPackages.Where(x => x.Result != null).Select(x => x.Result !);
// Separate the packages into those who have a location and those
// who do not. The former we will look up on disk, the others in
// nuget.
var packagesAndLocations = valid.Select(package => (package, location: packagePolicies.GetLocation(package.Id)));
var packagesWithLocations = packagesAndLocations.Where(p => p.location != null);
var packagesWithoutLocations = packagesAndLocations.Except(packagesWithLocations).Select(p => p.package);
{
var dependencies = await this.registry.FindPackageDependencies(packagesWithoutLocations);
result.AddRange(dependencies);
}
{
foreach (var(package, location) in packagesWithLocations)
{
if (searchedRoots.Contains(location))
{
continue;
}
var analyzedPackage = new AnalyzedPackage(package);
IList <DependencyChain <AnalyzedPackage> > dependencies;
if (this.followLocations)
{
searchedRoots.Add(location);
dependencies = await this.FindPackageDependenciesAux(location !, searchedRoots);
}
else
{
dependencies = DependencyChain <AnalyzedPackage> .EmptyList;
}
var chain = new DependencyChain <AnalyzedPackage>(analyzedPackage, dependencies);
result.Add(chain);
}
}
return(result);
}
private static async Task <int> Execute(CommandArgument url, CommandOption dataFolder)
{
try
{
if (string.IsNullOrEmpty(url.Value))
{
Console.WriteLine("Please specify a module url to install from");
return(1);
}
FoundryDataFolder foundryDataFolder;
if (dataFolder.HasValue())
{
foundryDataFolder = FoundryDataFolder.FromDirectoryPath(dataFolder.Value());
}
else
{
foundryDataFolder = FoundryDataFolder.FromCurrentDirectory();
}
if (foundryDataFolder == null)
{
return(1);
}
var manifestLoader = new ManifestLoader();
await foundryDataFolder.ReadAllManifests(manifestLoader);
var manifest = await manifestLoader.FromUri(new Uri(url.Value));
if (manifest == null)
{
return(1);
}
var dependencyChain = new DependencyChain();
dependencyChain.AddCurrentlyInstalledDependencies(manifestLoader, foundryDataFolder);
await dependencyChain.AddDependenciesFromManifest(manifestLoader, manifest);
foreach (var dependency in dependencyChain.NeededDependencies)
{
Console.WriteLine();
var dependencyManifest = await dependency.GetFullManifest(manifestLoader);
await dependencyManifest.Install(foundryDataFolder);
}
Console.WriteLine();
return(await manifest.Install(foundryDataFolder));
}
catch (Exception e)
{
Console.Write(e.Message);
return(1);
}
}
internal async Task <DependencyChain <AnalyzedPackage> > FindPackageDependencies(Package package, HashSet <string> upstreamDependencies)
{
string resolvedDependenciesPath = string.Format(this.resolvedPathFormatStr, package.Id, package.Version);
if (DiskCache.TryGetValue(resolvedDependenciesPath, this.config.ResolvedDependencies, out DependencyChain <AnalyzedPackage>?cached))
{
return(cached);
}
string path = string.Format(this.detailsPathFormatStr, package.Id, package.Version);
NpmPackage?npmPackage;
if (!DiskCache.TryGetValue(path, this.config.PackageDetails, out npmPackage))
{
npmPackage = await this.npm.GetPackage(package).ContinueWith(CachePackage);
}
if (npmPackage == null)
{
Log.Warning($"Could not find information about {package.Id} {package.Version} from npm");
return(new InvalidDependencyChain(package, "Could not find information from npm"));
}
var analyzedPackage = new AnalyzedPackage(package);
var packageDependencies = npmPackage.Dependencies ?? new Dictionary <string, string>();
var ds = packageDependencies.Select(x => new PackageRange(x.Key, x.Value, package.OriginProject));
var dependencies = await FindPackageDependencies(ds, upstreamDependencies);
var result = new DependencyChain <AnalyzedPackage>(analyzedPackage, dependencies);
CacheDependencies(package, result);
return(result);
NpmPackage?CachePackage(Task <(PackageDetailsResultEnum, NpmPackage?)> package)
{
if (!this.config.PackageDetails.DoCache)
{
return(package.Result.Item2);
}
if (package.Result.Item1 != PackageDetailsResultEnum.Success)
{
return(null);
}
NpmPackage p = package.Result.Item2 !;
string cachePath = string.Format(this.detailsPathFormatStr, p.Id, p.Version);
DiskCache.Cache(cachePath, p);
return(p);
}
}
public async Task GivenInstallationManifestWithOneDependency_WhenChainCalculated_ThenOneDependency()
{
var dependencyManifest = CreateTestManifest("dependency-a", 1, 0, 0);
var manifestLoader = CreateMockManifestLoader(dependencyManifest);
var installManifest = CreateTestManifest("to-install", 1, 0, 0, dependencyManifest);
var dependencyChain = new DependencyChain();
await dependencyChain.AddDependenciesFromManifest(manifestLoader, installManifest);
dependencyChain.NeededDependencies.Count.ShouldBe(1);
}
private void CacheDependencies(IPackage package, DependencyChain <AnalyzedPackage> dependencies)
{
if (!this.config.ResolvedDependencies.DoCache)
{
return;
}
if (dependencies == null)
{
return;
}
DiskCache.Cache(string.Format(this.resolvedPathFormatStr, package.Id, package.Version), dependencies);
}
public async Task GivenTwoManifestWithDependencies_WhenChainCalculated_ThenTwoDependencies()
{
var dependencyManifest1 = CreateTestManifest("dependency-a", 1, 0, 0);
var dependencyManifest2 = CreateTestManifest("dependency-b", 1, 0, 0);
var manifestLoader = CreateMockManifestLoader(dependencyManifest1, dependencyManifest2);
var installManifestA = CreateTestManifest("to-install-a", 1, 0, 0, dependencyManifest1);
var installManifestB = CreateTestManifest("to-install-a", 1, 0, 0, dependencyManifest2);
var dependencyChain = new DependencyChain();
await dependencyChain.AddDependenciesFromManifest(manifestLoader, installManifestA);
await dependencyChain.AddDependenciesFromManifest(manifestLoader, installManifestB);
dependencyChain.NeededDependencies.Count.ShouldBe(2);
}
public async Task GivenSecondManifestWithCompatibleDependencyLoaded_WhenChainCalculated_ThenOneDependency()
{
var dependencyManifest1 = CreateTestManifest("dependency-a", 1, 0, 0);
var dependencyManifest2 = CreateTestManifest("dependency-a", 1, 2, 0);
var manifestLoader = CreateMockManifestLoader(dependencyManifest1, dependencyManifest2);
var installManifestA = CreateTestManifest("to-install-a", 1, 0, 0, dependencyManifest1);
var installManifestB = CreateTestManifest("to-install-b", 1, 0, 0, dependencyManifest2);
var dependencyChain = new DependencyChain();
await dependencyChain.AddDependenciesFromManifest(manifestLoader, installManifestB);
await dependencyChain.AddDependenciesFromManifest(manifestLoader, installManifestA);
dependencyChain.NeededDependencies.Count.ShouldBe(1);
dependencyChain.NeededDependencies.First().Version.ShouldBe(dependencyManifest2.SemanticVersion);
}
public async Task GivenAlreadyInstalledDependency_WhenChainCalculated_ThenOneDependency()
{
var dependencyManifest1 = CreateTestManifest("dependency-a", 1, 2, 0);
var dependencyManifest2 = CreateTestManifest("dependency-a", 1, 0, 0);
var manifestLoader = CreateMockManifestLoader(dependencyManifest1, dependencyManifest2);
var alreadyInstalledManifest = CreateTestManifest("installed", 1, 0, 0, dependencyManifest1);
var installManifest = CreateTestManifest("to-install", 1, 0, 0, dependencyManifest2);
var dependencyChain = new DependencyChain();
await dependencyChain.AddDependenciesFromManifest(manifestLoader, alreadyInstalledManifest);
await dependencyChain.AddDependenciesFromManifest(manifestLoader, installManifest);
dependencyChain.NeededDependencies.Count.ShouldBe(1);
dependencyChain.NeededDependencies.First().Version.ShouldBe(dependencyManifest1.GetSemanticVersion());
}
public async Task GivenManifestsWithSharedDownstreamDependency_WhenChainCalculated_ThenSharedLoadedOnce()
{
var dependencyManifest1 = CreateTestManifest("dependency-a", 1, 0, 0);
var dependencyManifest2 = CreateTestManifest("dependency-b", 1, 0, 0);
var dependencyManifest3 = CreateTestManifest("dependency-c", 1, 0, 0, dependencyManifest1);
var manifestLoader = CreateMockManifestLoader(dependencyManifest1, dependencyManifest2, dependencyManifest3);
var installManifestA = CreateTestManifest("to-install-a", 1, 0, 0, dependencyManifest1);
var installManifestB = CreateTestManifest("to-install-b", 1, 0, 0, dependencyManifest2, dependencyManifest3);
var dependencyChain = new DependencyChain();
await dependencyChain.AddDependenciesFromManifest(manifestLoader, installManifestA);
await dependencyChain.AddDependenciesFromManifest(manifestLoader, installManifestB);
dependencyChain.NeededDependencies.Count.ShouldBe(3);
dependencyChain.NeededDependencies.First().Version.ShouldBe(dependencyManifest2.SemanticVersion);
}
public async Task GivenCircularDependency_WhenChainCalculated_ThenResolved()
{
var dependencyManifest1 = CreateTestManifest("dependency-a", 1, 0, 0);
var dependencyManifest2 = CreateTestManifest("dependency-b", 1, 0, 0, dependencyManifest1);
var dependencyManifest3 = CreateTestManifest("dependency-c", 1, 0, 0, dependencyManifest2);
dependencyManifest1.Dependencies = new List <Dependency> {
dependencyManifest3.ToDependency()
};
var manifestLoader = CreateMockManifestLoader(dependencyManifest1, dependencyManifest2, dependencyManifest3);
var installManifestA = CreateTestManifest("to-install-a", 1, 0, 0, dependencyManifest3);
var dependencyChain = new DependencyChain();
await dependencyChain.AddDependenciesFromManifest(manifestLoader, installManifestA);
dependencyChain.NeededDependencies.Count.ShouldBe(3);
dependencyChain.NeededDependencies.First().Version.ShouldBe(dependencyManifest2.SemanticVersion);
}
public async Task GivenTwoManifestWithIncompatibleDependency_WhenChainCalculated_ThenIncompatible()
{
var dependencyManifest1 = CreateTestManifest("dependency-a", 1, 0, 0);
var dependencyManifest2 = CreateTestManifest("dependency-a", 2, 0, 0);
var manifestLoader = CreateMockManifestLoader(dependencyManifest1, dependencyManifest2);
var installManifestA = CreateTestManifest("to-install-a", 1, 0, 0, dependencyManifest1);
var installManifestB = CreateTestManifest("to-install-b", 1, 0, 0, dependencyManifest2);
var dependencyChain = new DependencyChain();
await dependencyChain.AddDependenciesFromManifest(manifestLoader, installManifestA);
var exception = await Should.ThrowAsync <Exception>(async() =>
{
await dependencyChain.AddDependenciesFromManifest(manifestLoader, installManifestB);
});
exception.Message.ShouldBe("Incompatible dependency chain");
}
public async Task Execute(string buildConfigId, BuildChainView view = BuildChainView.List, BuildChainFilter filter = BuildChainFilter.All)
{
Log.Info("================ Show Build Chain: start ================");
var buildConfig = await _client.BuildConfigs.GetByConfigurationId(buildConfigId);
var dependencyChain = new DependencyChain(_client, buildConfig);
switch (view)
{
case BuildChainView.Tree:
Log.Info(Environment.NewLine + dependencyChain.SketchGraph());
break;
default:
Log.Info(Environment.NewLine + dependencyChain.ToString(filter));
break;
}
Log.Info("---------------------------------------------------------");
var dependencies = dependencyChain.GetDependenciesWithMultipleVersions().ToList();
if (dependencies.Any())
{
Log.Warn("There are some dependencies in the build chain with different versions:");
foreach (var dependency in dependencies)
{
Log.Warn(" - " + dependency.Key);
foreach (var buildNumber in dependency.Value)
{
Log.Warn(" - " + (buildNumber ?? "Same chain"));
}
}
}
else
{
Log.Info(" OK: Each dependency in the build chain has unique version.");
}
}
private async Task <IList <DependencyChain <AnalyzedPackage> > > FindPackageDependenciesAux(IEnumerable <IPackageRange> packages, bool clearCache)
{
if (clearCache)
{
this.alreadySeenPackages.Clear();
}
var result = new List <DependencyChain <AnalyzedPackage> >();
foreach (var package in packages)
{
if (this.packagePolicies.IgnorePackage(package.Id))
{
continue;
}
Package?resolvedPackage = await this.versionResolver.GetSingleVersion(package);
if (resolvedPackage == null)
{
result.Add(new InvalidDependencyChain(package, $"Could not find a package with version {package.VersionRange}."));
alreadySeenPackages.Add(package.ToString());
continue;
}
if (ignoreDuplicatePackages && alreadySeenPackages.Contains(resolvedPackage.ToString()))
{
continue;
}
DependencyChain <AnalyzedPackage> dependencies = await FindPackageDependencies(resolvedPackage).ContinueWith(ds => CacheDependencies(resolvedPackage, ds));
result.Add(dependencies);
alreadySeenPackages.Add(resolvedPackage.ToString());
}
return(result);
}
private static void CalcChain(ExcelWorkbook wb, FormulaParser parser, DependencyChain dc, ExcelCalculationOption options)
{
wb.FormulaParser.Configure(config =>
{
config.AllowCircularReferences = options.AllowCircularReferences;
config.PrecisionAndRoundingStrategy = options.PrecisionAndRoundingStrategy;
});
var debug = parser.Logger != null;
foreach (var ix in dc.CalcOrder)
{
var item = dc.list[ix];
try
{
var ws = wb.Worksheets.GetBySheetID(item.SheetID);
var v = parser.ParseCell(item.Tokens, ws == null ? "" : ws.Name, item.Row, item.Column);
SetValue(wb, item, v);
if (debug)
{
parser.Logger.LogCellCounted();
}
Thread.Sleep(0);
}
catch (FormatException fe)
{
throw (fe);
}
catch (CircularReferenceException cre)
{
throw cre;
}
catch (Exception e)
{
var error = ExcelErrorValue.Parse(ExcelErrorValue.Values.Value);
SetValue(wb, item, error);
}
}
}
private async Task Init(string sourceBuildConfigId, string targetRootBuildConfigId, bool simulate)
{
_simulate = simulate;
_sourceBuildConfig = await _client.BuildConfigs.GetByConfigurationId(sourceBuildConfigId);
_targetRootBuildConfig = await _client.BuildConfigs.GetByConfigurationId(targetRootBuildConfigId);
if (_targetRootBuildConfig.Parameters[ParameterName.ClonedFromBuildId] == null)
{
throw new Exception(
$"Target root Build Config doesn't appear to be cloned. It is missing the \"{ParameterName.ClonedFromBuildId}\" parameter.");
}
_targetBuildChainId = _targetRootBuildConfig.Parameters[ParameterName.BuildConfigChainId].Value;
_newNameSuffix = _targetRootBuildConfig.Parameters[ParameterName.CloneNameSuffix].Value;
_newBranchName = VcsRootHelper.ToValidGitBranchName(_newNameSuffix);
_dependencyChain = new DependencyChain(_client, _targetRootBuildConfig);
if (!_dependencyChain.Contains(_sourceBuildConfig.Id))
{
throw new Exception(
$"Cannot clone Build Config, because requested source Build Config ({_sourceBuildConfig.Id}) " +
$"is not found in the current Build Config chain for target Build Config ({targetRootBuildConfigId}). " +
$"Make sure target Build Config depends on source Build Config." + Environment.NewLine +
$"Currently discovered Build Config chain is: " + Environment.NewLine + "{_dependencyChain}");
}
if (_sourceBuildConfig.Parameters[ParameterName.BuildConfigChainId].Value == _targetBuildChainId)
{
throw new Exception(
$"It appears that Build Config \"{_sourceBuildConfig.Id}\" is already a cloned for target " +
$"Build Config \"{_targetRootBuildConfig.Id}\", because \"{ParameterName.BuildConfigChainId}\" " +
$"parameter is the same \"{_sourceBuildConfig.Parameters[ParameterName.BuildConfigChainId]}\" . " +
$"Create a new clone of root Build Config first");
}
}
private static async Task <int> Execute(CommandOption dataFolder)
{
try
{
FoundryDataFolder foundryDataFolder;
if (dataFolder.HasValue())
{
foundryDataFolder = FoundryDataFolder.FromDirectoryPath(dataFolder.Value());
}
else
{
foundryDataFolder = FoundryDataFolder.FromCurrentDirectory();
}
if (foundryDataFolder == null)
{
return(1);
}
var manifestLoader = new ManifestLoader();
var manifestsToUpdate = new List <Manifest>();
foreach (var manifest in await foundryDataFolder.ReadAllManifests(manifestLoader))
{
Console.WriteLine();
Console.WriteLine($"Checking {manifest.Name} for updates");
var latestManifest = await GetLatestManifest(manifestLoader, manifest);
if (latestManifest == null)
{
continue;
}
if (latestManifest.GetSemanticVersion() > manifest.GetSemanticVersion())
{
Console.WriteLine($"Queuing update for {latestManifest.Name} from {manifest.GetSemanticVersion().ToNormalizedString()} to {latestManifest.GetSemanticVersion().ToNormalizedString()}");
manifestsToUpdate.Add(latestManifest);
}
}
var dependencyChain = new DependencyChain();
dependencyChain.AddCurrentlyInstalledDependencies(manifestLoader, foundryDataFolder);
foreach (var toUpdate in manifestsToUpdate)
{
await dependencyChain.AddDependenciesFromManifest(manifestLoader, toUpdate);
}
foreach (var dependency in dependencyChain.NeededDependencies)
{
Console.WriteLine();
var dependencyManifest = await dependency.GetFullManifest(manifestLoader);
await dependencyManifest.Install(foundryDataFolder);
}
foreach (var toUpdate in manifestsToUpdate)
{
Console.WriteLine();
await toUpdate.Install(foundryDataFolder);
}
return(0);
}
catch (Exception e)
{
Console.WriteLine(e.Message);
return(1);
}
}
private static void CalcChain_V2(ExcelWorkbook wb, FormulaParser parser, DependencyChain dc, ExcelRangeBase range = null)
{
if (dc.CalcOrder.Count == 0)
{
if (range != null && range.Address.ToString().StartsWith("P"))
{
}
}
int fiColValidator = FundingConstants_V2.CLAIMS_WORKSHEET_VALIDATOR_COLUMNS.FI_COLUMN;
int fiRowValidator = FundingConstants_V2.CLAIMS_WORKSHEET_VALIDATOR_COLUMNS.FI_ROW;
int ftpColValidator = FundingConstants_V2.CLAIMS_WORKSHEET_VALIDATOR_COLUMNS.FTP_COLUMN;
int ftpRowValidator = FundingConstants_V2.CLAIMS_WORKSHEET_VALIDATOR_COLUMNS.FTP_ROW;
foreach (var ix in dc.CalcOrder)
{
var item = dc.list[ix];
rows.Add(item.Row);
sheetId = item.SheetID;
try
{
var ws = wb.Worksheets.GetBySheetID(item.SheetID);
bool goDefault = true;
if (ws.Cells[fiRowValidator, fiColValidator] != null &&
ws.Cells[fiRowValidator, fiColValidator].Text != "" &&
ws.Cells[fiRowValidator, fiColValidator].Text.ToLower() == FundingConstants_V2.CLAIMS_WORKSHEET_VALIDATOR_COLUMNS.FI_COLUMN_HEADER.ToLower())
{ // FI Worksheet
#region FI WORKSHEETS
if (range != null &&
item.Column == FundingConstants_V2.CLAIMS_WORKSHEET_COLUMN_INDEXES.FI_WORKSHEET.TOTAL_PROGRAMME_COST &&
range.Address.ToString().StartsWith("P") ||
item.Column == FundingConstants_V2.CLAIMS_WORKSHEET_COLUMN_INDEXES.FI_WORKSHEET.GRANT_AMOUNT_FTS)
{
if (ws.Cells[item.Row, FundingConstants_V2.CLAIMS_WORKSHEET_COLUMN_INDEXES.FI_WORKSHEET.TYPE_OF_SCHEME].Text != null &&
ws.Cells[item.Row, FundingConstants_V2.CLAIMS_WORKSHEET_COLUMN_INDEXES.FI_WORKSHEET.TYPE_OF_SCHEME].Text != "" &&
ws.Cells[item.Row, FundingConstants_V2.CLAIMS_WORKSHEET_COLUMN_INDEXES.FI_WORKSHEET.TYPE_OF_SCHEME].Text.ToLower() == FundingConstants_V2.PROGRAM_TYPE.FTS.ToLower())
{
decimal x1 = 0;
bool isValidX1 = Decimal.TryParse(ws.Cells[item.Row, FundingConstants_V2.CLAIMS_WORKSHEET_COLUMN_INDEXES.FI_WORKSHEET.TOTAL_PROGRAMME_COST].Text.Replace("$", ""), out x1);
if (isValidX1)
{
decimal grantPercentage = .5m;
string nrictype = ws.Cells[item.Row, FundingConstants_V2.CLAIMS_WORKSHEET_COLUMN_INDEXES.FI_WORKSHEET.NRIC_TYPE].Text.ToString();
string progStartDateStr = ws.Cells[item.Row, FundingConstants_V2.CLAIMS_WORKSHEET_COLUMN_INDEXES.FI_WORKSHEET.PROG_START_DATE].Text.ToString();
string scAgeFilterStr = ws.Cells[item.Row, FundingConstants_V2.CLAIMS_WORKSHEET_COLUMN_INDEXES.FI_WORKSHEET.SC_AGE_FILTER].Text.ToString();
bool isSCAgeFilter = !string.IsNullOrEmpty(scAgeFilterStr) && scAgeFilterStr.ToLower() == "yes";
DateTime progStartDate = parseDate(progStartDateStr);
if (nrictype == FundingConstants_V2.CLAIMS_WORKSHEET_CONSTANTS.NRIC_SC &&
progStartDate.CompareTo(FundingConstants_V2.CLAIMS_WORKSHEET_CONSTANTS.SCAGEFILTER_DATE_THRESHOLD) >= 0 &&
isSCAgeFilter
)
{
grantPercentage = .9m;
}
x1 = x1 * grantPercentage;
x1 = Math.Round(x1, 2, MidpointRounding.AwayFromZero);
string formula = string.Format(FundingConstants_V2.CLAIMS_WORKSHEET_FORMULAS.FI_WORKSHEET.GRANT_AMOUNT_FTS, item.Row, item.Row, item.Row, item.Row, item.Row, item.Row, item.Row, item.Row, item.Row, item.Row, item.Row);
if (item.Formula == null)
{
x1 = 0;
}
else if (item.Formula != null && formula != item.Formula.Trim().Replace(" ", ""))
{
x1 = 0;
}
else if (x1 > 2000.00m)
{
x1 = 2000.00m;
}
SetValue(wb, item, x1);
goDefault = false;
}
else
{
SetValue(wb, item, "");
goDefault = false;
}
}
else
{
SetValue(wb, item, "");
goDefault = false;
}
}
else if (range != null &&
item.Column == FundingConstants_V2.CLAIMS_WORKSHEET_COLUMN_INDEXES.FI_WORKSHEET.TOTAL_PROGRAMME_COST &&
range.Address.ToString().StartsWith("P") ||
item.Column == FundingConstants_V2.CLAIMS_WORKSHEET_COLUMN_INDEXES.FI_WORKSHEET.GRANT_AMOUNT_IBFSTS)
{
if (ws.Cells[item.Row, FundingConstants_V2.CLAIMS_WORKSHEET_COLUMN_INDEXES.FI_WORKSHEET.TYPE_OF_SCHEME].Text != null &&
ws.Cells[item.Row, FundingConstants_V2.CLAIMS_WORKSHEET_COLUMN_INDEXES.FI_WORKSHEET.TYPE_OF_SCHEME].Text != "" &&
ws.Cells[item.Row, FundingConstants_V2.CLAIMS_WORKSHEET_COLUMN_INDEXES.FI_WORKSHEET.TYPE_OF_SCHEME].Text.ToLower() == FundingConstants_V2.PROGRAM_TYPE.IBF_STS.ToLower())
{
decimal x1 = 0;
bool isValidX1 = Decimal.TryParse(ws.Cells[item.Row, FundingConstants_V2.CLAIMS_WORKSHEET_COLUMN_INDEXES.FI_WORKSHEET.TOTAL_PROGRAMME_COST].Text.Replace("$", ""), out x1);
//if(isValidX1)
//{
decimal grantPercentage = .7m;
string nrictype = ws.Cells[item.Row, FundingConstants_V2.CLAIMS_WORKSHEET_COLUMN_INDEXES.FI_WORKSHEET.NRIC_TYPE].Text.ToString();
string progStartDateStr = ws.Cells[item.Row, FundingConstants_V2.CLAIMS_WORKSHEET_COLUMN_INDEXES.FI_WORKSHEET.PROG_START_DATE].Text.ToString();
string scAgeFilterStr = ws.Cells[item.Row, FundingConstants_V2.CLAIMS_WORKSHEET_COLUMN_INDEXES.FI_WORKSHEET.SC_AGE_FILTER].Text.ToString();
bool isSCAgeFilter = !string.IsNullOrEmpty(scAgeFilterStr) && scAgeFilterStr.ToLower() == "yes";
DateTime progStartDate = parseDate(progStartDateStr);
if (nrictype == FundingConstants_V2.CLAIMS_WORKSHEET_CONSTANTS.NRIC_SC &&
progStartDate.CompareTo(FundingConstants_V2.CLAIMS_WORKSHEET_CONSTANTS.SCAGEFILTER_DATE_THRESHOLD) >= 0 &&
isSCAgeFilter
)
{
grantPercentage = .9m;
}
x1 = x1 * grantPercentage;
x1 = Math.Round(x1, 2, MidpointRounding.AwayFromZero);
string formula = string.Format(FundingConstants_V2.CLAIMS_WORKSHEET_FORMULAS.FI_WORKSHEET.GRANT_AMOUNT_IBFSTS, item.Row, item.Row, item.Row, item.Row, item.Row, item.Row, item.Row, item.Row, item.Row, item.Row, item.Row);
if (item.Formula == null)
{
x1 = 0;
}
else if (item.Formula != null && formula != item.Formula.Trim().Replace(" ", ""))
{
x1 = 0;
}
else if (x1 > 7000.00m)
{
x1 = 7000.00m;
}
SetValue(wb, item, x1);
goDefault = false;
//}
//else
//{
// SetValue(wb, item, "");
// goDefault = false;
//}
}
else
{
SetValue(wb, item, "");
goDefault = false;
}
}
else if (item.Column == FundingConstants_V2.CLAIMS_WORKSHEET_COLUMN_INDEXES.FI_WORKSHEET.TOTAL_INTERNAL_COST)
{
//if (ws.Cells[item.Row, FundingConstants.CLAIMS_WORKSHEET_COLUMN_INDEXES.FI_WORKSHEET.INHOUSE_COST].Text != "" ||
// ws.Cells[item.Row, FundingConstants.CLAIMS_WORKSHEET_COLUMN_INDEXES.FI_WORKSHEET.INHOUSE_FEECHARGED].Text != "")
//{
decimal x1 = 0;
decimal x2 = 0;
bool isValidX1 = Decimal.TryParse(ws.Cells[item.Row, FundingConstants_V2.CLAIMS_WORKSHEET_COLUMN_INDEXES.FI_WORKSHEET.INHOUSE_COST].Text.Replace("$", ""), out x1);
bool isValidX2 = decimal.TryParse(ws.Cells[item.Row, FundingConstants_V2.CLAIMS_WORKSHEET_COLUMN_INDEXES.FI_WORKSHEET.INHOUSE_FEECHARGED].Text.Replace("$", ""), out x2);
string formula = string.Format(FundingConstants_V2.CLAIMS_WORKSHEET_FORMULAS.FI_WORKSHEET.TOTAL_INTERNAL_COST, item.Row, item.Row);
decimal sum = x1 + x2;
if (item.Formula == null)
{
sum = 0;
}
else if (item.Formula != null && formula != item.Formula.Trim().Replace(" ", ""))
{
sum = 0;
}
else
{
SetValue(wb, item, sum);
goDefault = false;
}
}
else if (item.Column == FundingConstants_V2.CLAIMS_WORKSHEET_COLUMN_INDEXES.FI_WORKSHEET.TOTAL_PROGRAMME_COST)
{
//if (ws.Cells[item.Row, FundingConstants.CLAIMS_WORKSHEET_COLUMN_INDEXES.FI_WORKSHEET.INHOUSE_COST].Text != "" &&
// ws.Cells[item.Row, FundingConstants.CLAIMS_WORKSHEET_COLUMN_INDEXES.FI_WORKSHEET.INHOUSE_FEECHARGED].Text != "")
decimal x1a = 0;
decimal x2a = 0;
bool isValidX1a = Decimal.TryParse(ws.Cells[item.Row, FundingConstants_V2.CLAIMS_WORKSHEET_COLUMN_INDEXES.FI_WORKSHEET.INHOUSE_COST].Text.Replace("$", ""), out x1a);
bool isValidX2a = decimal.TryParse(ws.Cells[item.Row, FundingConstants_V2.CLAIMS_WORKSHEET_COLUMN_INDEXES.FI_WORKSHEET.INHOUSE_FEECHARGED].Text.Replace("$", ""), out x2a);
if ((x1a + x2a) != 0m)
{
decimal x1 = 0;
decimal x2 = 0;
bool isValidX1 = Decimal.TryParse(ws.Cells[item.Row, FundingConstants_V2.CLAIMS_WORKSHEET_COLUMN_INDEXES.FI_WORKSHEET.INHOUSE_COST].Text.Replace("$", ""), out x1);
bool isValidX2 = decimal.TryParse(ws.Cells[item.Row, FundingConstants_V2.CLAIMS_WORKSHEET_COLUMN_INDEXES.FI_WORKSHEET.INHOUSE_FEECHARGED].Text.Replace("$", ""), out x2);
string formula = string.Format(FundingConstants_V2.CLAIMS_WORKSHEET_FORMULAS.FI_WORKSHEET.TOTAL_PROGRAMME_COST, item.Row, item.Row, item.Row);
decimal sum = x1 + x2;
if (item.Formula == null)
{
sum = 0;
}
else if (item.Formula != null && formula != item.Formula.Trim().Replace(" ", ""))
{
sum = 0;
}
else
{
SetValue(wb, item, sum);
goDefault = false;
}
}
else if (ws.Cells[item.Row, FundingConstants_V2.CLAIMS_WORKSHEET_COLUMN_INDEXES.FI_WORKSHEET.EXTERNAL_TOTAL].Text != "")
{
decimal x1 = 0;
bool isValidX1 = Decimal.TryParse(ws.Cells[item.Row, FundingConstants_V2.CLAIMS_WORKSHEET_COLUMN_INDEXES.FI_WORKSHEET.EXTERNAL_TOTAL].Value + "", out x1);
string formula = string.Format(FundingConstants_V2.CLAIMS_WORKSHEET_FORMULAS.FI_WORKSHEET.TOTAL_PROGRAMME_COST, item.Row, item.Row, item.Row);
if (item.Formula == null)
{
x1 = 0;
}
else if (item.Formula != null && formula != item.Formula.Trim().Replace(" ", ""))
{
x1 = 0;
}
else
{
SetValue(wb, item, x1);
goDefault = false;
}
}
}
#endregion
}
else if (ws.Cells[ftpRowValidator, ftpColValidator] != null &&
ws.Cells[ftpRowValidator, ftpColValidator].Text != "" &&
ws.Cells[ftpRowValidator, ftpColValidator].Text.ToLower() == FundingConstants_V2.CLAIMS_WORKSHEET_VALIDATOR_COLUMNS.FTP_COLUMN_HEADER.ToLower())
{ // FTP Worksheet
#region FTP Worksheet
if (range != null &&
item.Column == FundingConstants_V2.CLAIMS_WORKSHEET_COLUMN_INDEXES.FTP_WORKSHEET.GRANT_AMOUNT_IBFSTS &&
range.Address.ToString().StartsWith("M") ||
item.Column == FundingConstants_V2.CLAIMS_WORKSHEET_COLUMN_INDEXES.FTP_WORKSHEET.GRANT_AMOUNT_IBFSTS)
{
decimal x1 = 0;
bool isValidX1 = Decimal.TryParse(ws.Cells[item.Row, FundingConstants_V2.CLAIMS_WORKSHEET_COLUMN_INDEXES.FTP_WORKSHEET.PROGRAMME_FEE].Value.ToString(), out x1);
//if(isValidX1)
//{
decimal grantPercentage = .7m;
string nrictype = ws.Cells[item.Row, FundingConstants_V2.CLAIMS_WORKSHEET_COLUMN_INDEXES.FTP_WORKSHEET.NRIC_TYPE].Text.ToString();
string progStartDateStr = ws.Cells[item.Row, FundingConstants_V2.CLAIMS_WORKSHEET_COLUMN_INDEXES.FTP_WORKSHEET.PROG_START_DATE].Text.ToString();
string scAgeFilterStr = ws.Cells[item.Row, FundingConstants_V2.CLAIMS_WORKSHEET_COLUMN_INDEXES.FTP_WORKSHEET.SC_AGE_FILTER].Text.ToString();
bool isSCAgeFilter = !string.IsNullOrEmpty(scAgeFilterStr) && scAgeFilterStr.ToLower() == "yes";
DateTime progStartDate = parseDate(progStartDateStr);
if (nrictype == FundingConstants_V2.CLAIMS_WORKSHEET_CONSTANTS.NRIC_SC &&
progStartDate.CompareTo(FundingConstants_V2.CLAIMS_WORKSHEET_CONSTANTS.SCAGEFILTER_DATE_THRESHOLD) >= 0 &&
isSCAgeFilter
)
{
grantPercentage = .9m;
}
x1 = x1 * grantPercentage;
x1 = Math.Round(x1, 2, MidpointRounding.AwayFromZero);
//string formula = "IF(EXACT(N"+item.Row+",\"IBF-STS\"),MIN(O"+item.Row+"*0.5,2000),\"\")";
// =ROUND(MIN(K16*0.7,7000),2)
// string formula = "MIN(K"+item.Row+"*0.7,7000)";
string formula = string.Format(FundingConstants_V2.CLAIMS_WORKSHEET_FORMULAS.FTP_WORKSHEET.GRANT_AMOUNT_IBFSTS, item.Row, item.Row, item.Row, item.Row, item.Row, item.Row, item.Row, item.Row, item.Row, item.Row);
if (item.Formula == null)
{
x1 = 0;
}
else if (item.Formula != null && formula != item.Formula.Trim().Replace(" ", ""))
{
x1 = 0;
}
else if (x1 > 7000.00m)
{
x1 = 7000.00m;
}
SetValue(wb, item, x1);
goDefault = false;
//}
//else
//{
// SetValue(wb, item, "");
// goDefault = false;
//}
}
#endregion
}
if (goDefault)
{
var v = parser.ParseCell(item.Tokens, ws == null ? "" : ws.Name, item.Row, item.Column);
SetValue(wb, item, v);
}
}
catch (FormatException fe)
{
throw (fe);
}
catch (Exception e)
{
var error = ExcelErrorValue.Parse(ExcelErrorValue.Values.Value);
SetValue(wb, item, error);
}
}
var worksheet = wb.Worksheets.GetBySheetID(sheetId);
foreach (int row in rows)
{
if (worksheet.Cells[fiRowValidator, fiColValidator] != null &&
worksheet.Cells[fiRowValidator, fiColValidator].Text != "" &&
worksheet.Cells[fiRowValidator, fiColValidator].Text.ToLower() == FundingConstants_V2.CLAIMS_WORKSHEET_VALIDATOR_COLUMNS.FI_COLUMN_HEADER.ToLower())
{
#region FI WORKSHEETS
if (row >= 22)
{
if (String.IsNullOrEmpty(worksheet.Cells[row, FundingConstants_V2.CLAIMS_WORKSHEET_COLUMN_INDEXES.FI_WORKSHEET.GRANT_AMOUNT_FTS].Formula) ||
(!String.IsNullOrEmpty(worksheet.Cells[row, FundingConstants_V2.CLAIMS_WORKSHEET_COLUMN_INDEXES.FI_WORKSHEET.GRANT_AMOUNT_FTS].Formula) &&
worksheet.Cells[row, FundingConstants_V2.CLAIMS_WORKSHEET_COLUMN_INDEXES.FI_WORKSHEET.GRANT_AMOUNT_FTS].Formula != string.Format(FundingConstants_V2.CLAIMS_WORKSHEET_FORMULAS.FI_WORKSHEET.GRANT_AMOUNT_FTS, row, row, row, row, row, row, row, row, row, row, row)))
{
worksheet._values.SetValue(row, FundingConstants_V2.CLAIMS_WORKSHEET_COLUMN_INDEXES.FI_WORKSHEET.GRANT_AMOUNT_FTS, 0);
}
if (String.IsNullOrEmpty(worksheet.Cells[row, FundingConstants_V2.CLAIMS_WORKSHEET_COLUMN_INDEXES.FI_WORKSHEET.GRANT_AMOUNT_IBFSTS].Formula) ||
(!String.IsNullOrEmpty(worksheet.Cells[row, FundingConstants_V2.CLAIMS_WORKSHEET_COLUMN_INDEXES.FI_WORKSHEET.GRANT_AMOUNT_IBFSTS].Formula) &&
worksheet.Cells[row, FundingConstants_V2.CLAIMS_WORKSHEET_COLUMN_INDEXES.FI_WORKSHEET.GRANT_AMOUNT_IBFSTS].Formula != string.Format(FundingConstants_V2.CLAIMS_WORKSHEET_FORMULAS.FI_WORKSHEET.GRANT_AMOUNT_IBFSTS, row, row, row, row, row, row, row, row, row, row, row)))
{
worksheet._values.SetValue(row, FundingConstants_V2.CLAIMS_WORKSHEET_COLUMN_INDEXES.FI_WORKSHEET.GRANT_AMOUNT_IBFSTS, 0);
}
if (String.IsNullOrEmpty(worksheet.Cells[row, FundingConstants_V2.CLAIMS_WORKSHEET_COLUMN_INDEXES.FI_WORKSHEET.TOTAL_INTERNAL_COST].Formula) ||
(!String.IsNullOrEmpty(worksheet.Cells[row, FundingConstants_V2.CLAIMS_WORKSHEET_COLUMN_INDEXES.FI_WORKSHEET.TOTAL_INTERNAL_COST].Formula) &&
worksheet.Cells[row, FundingConstants_V2.CLAIMS_WORKSHEET_COLUMN_INDEXES.FI_WORKSHEET.TOTAL_INTERNAL_COST].Formula != string.Format(FundingConstants_V2.CLAIMS_WORKSHEET_FORMULAS.FI_WORKSHEET.TOTAL_INTERNAL_COST, row, row)))
{
worksheet._values.SetValue(row, FundingConstants_V2.CLAIMS_WORKSHEET_COLUMN_INDEXES.FI_WORKSHEET.TOTAL_INTERNAL_COST, 0);
}
if (String.IsNullOrEmpty(worksheet.Cells[row, FundingConstants_V2.CLAIMS_WORKSHEET_COLUMN_INDEXES.FI_WORKSHEET.TOTAL_PROGRAMME_COST].Formula) ||
(!String.IsNullOrEmpty(worksheet.Cells[row, FundingConstants_V2.CLAIMS_WORKSHEET_COLUMN_INDEXES.FI_WORKSHEET.TOTAL_PROGRAMME_COST].Formula) &&
worksheet.Cells[row, FundingConstants_V2.CLAIMS_WORKSHEET_COLUMN_INDEXES.FI_WORKSHEET.TOTAL_PROGRAMME_COST].Formula != string.Format(FundingConstants_V2.CLAIMS_WORKSHEET_FORMULAS.FI_WORKSHEET.TOTAL_PROGRAMME_COST, row, row, row)))
{
worksheet._values.SetValue(row, FundingConstants_V2.CLAIMS_WORKSHEET_COLUMN_INDEXES.FI_WORKSHEET.TOTAL_PROGRAMME_COST, 0);
}
}
#endregion
}
else if (worksheet.Cells[ftpRowValidator, ftpColValidator] != null &&
worksheet.Cells[ftpRowValidator, ftpColValidator].Text != "" &&
worksheet.Cells[ftpRowValidator, ftpColValidator].Text.ToLower() == FundingConstants_V2.CLAIMS_WORKSHEET_VALIDATOR_COLUMNS.FTP_COLUMN_HEADER.ToLower())
{
#region FTP Worksheets
if (row >= 16)
{
if (String.IsNullOrEmpty(worksheet.Cells[row, FundingConstants_V2.CLAIMS_WORKSHEET_COLUMN_INDEXES.FTP_WORKSHEET.GRANT_AMOUNT_IBFSTS].Formula) ||
(!String.IsNullOrEmpty(worksheet.Cells[row, FundingConstants_V2.CLAIMS_WORKSHEET_COLUMN_INDEXES.FTP_WORKSHEET.GRANT_AMOUNT_IBFSTS].Formula) &&
worksheet.Cells[row, FundingConstants_V2.CLAIMS_WORKSHEET_COLUMN_INDEXES.FTP_WORKSHEET.GRANT_AMOUNT_IBFSTS].Formula != string.Format(FundingConstants_V2.CLAIMS_WORKSHEET_FORMULAS.FTP_WORKSHEET.GRANT_AMOUNT_IBFSTS, row, row, row, row, row, row, row, row, row, row)))
{
worksheet.Cells[row, FundingConstants_V2.CLAIMS_WORKSHEET_COLUMN_INDEXES.FTP_WORKSHEET.GRANT_AMOUNT_IBFSTS].Value = 0;
}
}
#endregion
}
}
}