/// <summary>
/// Create a new WTRate object.
/// </summary>
/// <param name="id">Initial value of the Id property.</param>
/// <param name="homeCountryId">Initial value of the HomeCountryId property.</param>
/// <param name="hostCountryId">Initial value of the HostCountryId property.</param>
/// <param name="streamArticleId">Initial value of the StreamArticleId property.</param>
/// <param name="treatyTitleId">Initial value of the TreatyTitleId property.</param>
public static WTRate CreateWTRate(global::System.Int32 id, global::System.Int32 homeCountryId, global::System.Int32 hostCountryId, global::System.String streamArticleId, global::System.String treatyTitleId)
{
WTRate wTRate = new WTRate();
wTRate.Id = id;
wTRate.HomeCountryId = homeCountryId;
wTRate.HostCountryId = hostCountryId;
wTRate.StreamArticleId = streamArticleId;
wTRate.TreatyTitleId = treatyTitleId;
return wTRate;
}
private void GetAllWtrRates(string sheetName, List<WTRate> allWtrRates, DataRowCollection rows, List<PublisherStatus> publisherStatus, DataAccess.Country hostCountry)
{
int rowStartCount = Convert.ToInt32(ConfigurationManager.AppSettings["ExcelRowStartIndex"]);
for (var i = rowStartCount+2; i < rows.Count; i++)
{
var partnerCountryData = rows[i].ItemArray[0].ToString().Trim();
if (partnerCountryData == String.Empty) continue;
var partnerCountry = _countries.FirstOrDefault(b => b.Name == partnerCountryData);
if (partnerCountry == null)
{
publisherStatus.Add(new PublisherStatus
{
Message = String.Format("Partner Country [{0}] Is Not Valid", partnerCountryData),
Status = false
});
continue;
}
if (ValidateRow(rows[i], hostCountry.Code, partnerCountry.Code, publisherStatus))
{
var wtrRateForDivPortFolio = new WTRate() { };
wtrRateForDivPortFolio.HostCountryId = hostCountry.Id;
wtrRateForDivPortFolio.HomeCountryId = partnerCountry.Id;
string streamTitleForPortFolio = StreamTitle.StreamTitleForPortFolio;
var streamForPortFolio = _allStreams.FirstOrDefault(b => b.Title == streamTitleForPortFolio);
if (streamForPortFolio == null)
{
publisherStatus.Add(new PublisherStatus
{
Message = String.Format("Stream Title [{0}] Is Not Valid", streamTitleForPortFolio),
Status = false
});
continue;
}
wtrRateForDivPortFolio.StreamId = streamForPortFolio.Id;
wtrRateForDivPortFolio.TreatyTitleId = rows[i].ItemArray[1].ToString().Trim();
wtrRateForDivPortFolio.StreamArticleId = rows[i].ItemArray[8].ToString().Trim();
wtrRateForDivPortFolio.StreamComment = rows[i].ItemArray[9].ToString().Trim();
wtrRateForDivPortFolio.Rate = rows[i].ItemArray[2].ToString().Trim();
wtrRateForDivPortFolio.ArtileLine = rows[i].ItemArray[3].ToString().Trim();
if (isUpdate(wtrRateForDivPortFolio) == null)
{
allWtrRates.Add(wtrRateForDivPortFolio);
}
var wtrRateForSubstantial = new WTRate() { };
wtrRateForSubstantial.HostCountryId = hostCountry.Id;
wtrRateForSubstantial.HomeCountryId = partnerCountry.Id;
string streamTitleForSubstantial = StreamTitle.StreamTitleForSubstantial;
var streamForSubstantial = _allStreams.FirstOrDefault(b => b.Title == streamTitleForSubstantial);
if (streamForSubstantial == null)
{
publisherStatus.Add(new PublisherStatus
{
Message = String.Format("Stream Title [{0}] Is Not Valid", streamTitleForSubstantial),
Status = false
});
continue;
}
wtrRateForSubstantial.StreamId = streamForSubstantial.Id;
wtrRateForSubstantial.TreatyTitleId = rows[i].ItemArray[1].ToString().Trim();
wtrRateForSubstantial.StreamArticleId = rows[i].ItemArray[8].ToString().Trim();
wtrRateForSubstantial.StreamComment = rows[i].ItemArray[9].ToString().Trim();
wtrRateForSubstantial.Rate = rows[i].ItemArray[4].ToString().Trim();
wtrRateForSubstantial.ArtileLine = rows[i].ItemArray[5].ToString().Trim();
if (isUpdate(wtrRateForSubstantial) == null)
{
allWtrRates.Add(wtrRateForSubstantial);
}
var wtrRateForSubstantialRate = new WTRate() { };
wtrRateForSubstantialRate.HostCountryId = hostCountry.Id;
wtrRateForSubstantialRate.HomeCountryId = partnerCountry.Id;
const string streamTitleForSubstantialRate = "DIV-substantial holding rate";
var streamForSubstantialRate = _allStreams.FirstOrDefault(b => b.Title == streamTitleForSubstantialRate);
if (streamForSubstantialRate == null)
{
publisherStatus.Add(new PublisherStatus
{
Message = String.Format("Stream Title [{0}] Is Not Valid", streamTitleForSubstantialRate),
Status = false
});
continue;
}
wtrRateForSubstantialRate.StreamId = streamForSubstantialRate.Id;
wtrRateForSubstantialRate.TreatyTitleId = rows[i].ItemArray[1].ToString().Trim();
wtrRateForSubstantialRate.StreamArticleId = rows[i].ItemArray[8].ToString().Trim();
wtrRateForSubstantialRate.StreamComment = rows[i].ItemArray[9].ToString().Trim();
wtrRateForSubstantialRate.Rate = rows[i].ItemArray[6].ToString().Trim();
wtrRateForSubstantialRate.ArtileLine = rows[i].ItemArray[7].ToString().Trim();
if (isUpdate(wtrRateForSubstantialRate) == null)
{
allWtrRates.Add(wtrRateForSubstantialRate);
}
var wtrRateForIntGen = new WTRate() { };
wtrRateForIntGen.HostCountryId = hostCountry.Id;
wtrRateForIntGen.HomeCountryId = partnerCountry.Id;
const string streamTitleForIntGen = "INT (GEN)";
var streamForIntGen = _allStreams.FirstOrDefault(b => b.Title == streamTitleForIntGen);
if (streamForIntGen == null)
{
publisherStatus.Add(new PublisherStatus
{
Message = String.Format("Stream Title [{0}] Is Not Valid", streamTitleForIntGen),
Status = false
});
continue;
}
wtrRateForIntGen.StreamId = streamForIntGen.Id;
wtrRateForIntGen.TreatyTitleId = rows[i].ItemArray[1].ToString().Trim();
wtrRateForIntGen.StreamArticleId = rows[i].ItemArray[16].ToString().Trim();
wtrRateForIntGen.StreamComment = rows[i].ItemArray[17].ToString().Trim();
wtrRateForIntGen.Rate = rows[i].ItemArray[10].ToString().Trim();
wtrRateForIntGen.ArtileLine = rows[i].ItemArray[11].ToString().Trim();
if (isUpdate(wtrRateForIntGen) == null)
{
allWtrRates.Add(wtrRateForIntGen);
}
var wtrRateForIntCb = new WTRate() { };
wtrRateForIntCb.HostCountryId = hostCountry.Id;
wtrRateForIntCb.HomeCountryId = partnerCountry.Id;
const string streamTitleForIntCb = "INT (C.B.)";
var streamForIntCb = _allStreams.FirstOrDefault(b => b.Title == streamTitleForIntCb);
if (streamForIntCb == null)
{
publisherStatus.Add(new PublisherStatus
{
Message = String.Format("Stream Title [{0}] Is Not Valid", streamTitleForIntCb),
Status = false
});
continue;
}
wtrRateForIntCb.StreamId = streamForIntCb.Id;
wtrRateForIntCb.TreatyTitleId = rows[i].ItemArray[1].ToString().Trim();
wtrRateForIntCb.StreamArticleId = rows[i].ItemArray[16].ToString().Trim();
wtrRateForIntCb.StreamComment = rows[i].ItemArray[17].ToString().Trim();
wtrRateForIntCb.Rate = rows[i].ItemArray[12].ToString().Trim();
wtrRateForIntCb.ArtileLine = rows[i].ItemArray[13].ToString().Trim();
if (isUpdate(wtrRateForIntCb) == null)
{
allWtrRates.Add(wtrRateForIntCb);
}
var wtrRateForIntBank = new WTRate() { };
wtrRateForIntBank.HostCountryId = hostCountry.Id;
wtrRateForIntBank.HomeCountryId = partnerCountry.Id;
const string streamTitleForIntBank = "INT (Bank)";
var streamForIntBank = _allStreams.FirstOrDefault(b => b.Title == streamTitleForIntBank);
if (streamForIntBank == null)
{
publisherStatus.Add(new PublisherStatus
{
Message = String.Format("Stream Title [{0}] Is Not Valid", streamTitleForIntBank),
Status = false
});
continue;
}
wtrRateForIntBank.StreamId = streamForIntBank.Id;
wtrRateForIntBank.TreatyTitleId = rows[i].ItemArray[1].ToString().Trim();
wtrRateForIntBank.StreamArticleId = rows[i].ItemArray[16].ToString().Trim();
wtrRateForIntBank.StreamComment = rows[i].ItemArray[17].ToString().Trim();
wtrRateForIntBank.Rate = rows[i].ItemArray[14].ToString().Trim();
wtrRateForIntBank.ArtileLine = rows[i].ItemArray[15].ToString().Trim();
if (isUpdate(wtrRateForIntBank) == null)
{
allWtrRates.Add(wtrRateForIntBank);
}
var wtrRateForRoyParent = new WTRate() { };
wtrRateForRoyParent.HostCountryId = hostCountry.Id;
wtrRateForRoyParent.HomeCountryId = partnerCountry.Id;
const string streamTitleForRoyParent = "ROY (Patent)";
var streamForRoyParent = _allStreams.FirstOrDefault(b => b.Title == streamTitleForRoyParent);
if (streamForRoyParent == null)
{
publisherStatus.Add(new PublisherStatus
{
Message = String.Format("Stream Title [{0}] Is Not Valid", streamTitleForRoyParent),
Status = false
});
continue;
}
wtrRateForRoyParent.StreamId = streamForRoyParent.Id;
wtrRateForRoyParent.TreatyTitleId = rows[i].ItemArray[1].ToString().Trim();
wtrRateForRoyParent.StreamArticleId = rows[i].ItemArray[26].ToString().Trim();
wtrRateForRoyParent.StreamComment = rows[i].ItemArray[27].ToString().Trim();
wtrRateForRoyParent.Rate = rows[i].ItemArray[18].ToString().Trim();
wtrRateForRoyParent.ArtileLine = rows[i].ItemArray[19].ToString().Trim();
if (isUpdate(wtrRateForRoyParent) == null)
{
allWtrRates.Add(wtrRateForRoyParent);
}
var wtrRateForRoyTm = new WTRate() { };
wtrRateForRoyTm.HostCountryId = hostCountry.Id;
wtrRateForRoyTm.HomeCountryId = partnerCountry.Id;
const string streamTitleForRoyTm = "ROY (T.M.)";
var streamForRoyTm = _allStreams.FirstOrDefault(b => b.Title == streamTitleForRoyTm);
if (streamForRoyTm == null)
{
publisherStatus.Add(new PublisherStatus
{
Message = String.Format("Stream Title [{0}] Is Not Valid", streamTitleForRoyTm),
Status = false
});
continue;
}
wtrRateForRoyTm.StreamId = streamForRoyTm.Id;
wtrRateForRoyTm.TreatyTitleId = rows[i].ItemArray[1].ToString().Trim();
wtrRateForRoyTm.StreamArticleId = rows[i].ItemArray[26].ToString().Trim();
wtrRateForRoyTm.StreamComment = rows[i].ItemArray[27].ToString().Trim();
wtrRateForRoyTm.Rate = rows[i].ItemArray[20].ToString().Trim();
wtrRateForRoyTm.ArtileLine = rows[i].ItemArray[21].ToString().Trim();
if (isUpdate(wtrRateForRoyTm) == null)
{
allWtrRates.Add(wtrRateForRoyTm);
}
var wtrRateForRoyCr = new WTRate() { };
wtrRateForRoyCr.HostCountryId = hostCountry.Id;
wtrRateForRoyCr.HomeCountryId = partnerCountry.Id;
const string streamTitleForRoyCr = "ROY (C.R.)";
var streamForRoyCr = _allStreams.FirstOrDefault(b => b.Title == streamTitleForRoyCr);
if (streamForRoyCr == null)
{
publisherStatus.Add(new PublisherStatus
{
Message = String.Format("Stream Title [{0}] Is Not Valid", streamTitleForRoyCr),
Status = false
});
continue;
}
wtrRateForRoyCr.StreamId = streamForRoyCr.Id;
wtrRateForRoyCr.TreatyTitleId = rows[i].ItemArray[1].ToString().Trim();
wtrRateForRoyCr.StreamArticleId = rows[i].ItemArray[26].ToString().Trim();
wtrRateForRoyCr.StreamComment = rows[i].ItemArray[27].ToString().Trim();
wtrRateForRoyCr.Rate = rows[i].ItemArray[22].ToString().Trim();
wtrRateForRoyCr.ArtileLine = rows[i].ItemArray[23].ToString().Trim();
if (isUpdate(wtrRateForRoyCr) == null)
{
allWtrRates.Add(wtrRateForRoyCr);
}
var wtrRateForRoyLp = new WTRate() { };
wtrRateForRoyLp.HostCountryId = hostCountry.Id;
wtrRateForRoyLp.HomeCountryId = partnerCountry.Id;
const string streamTitleForRoyLp = "ROY (L.P.)";
var streamForRoyLp = _allStreams.FirstOrDefault(b => b.Title == streamTitleForRoyLp);
if (streamForRoyLp == null)
{
publisherStatus.Add(new PublisherStatus
{
Message = String.Format("Stream Title [{0}] Is Not Valid", streamTitleForRoyLp),
Status = false
});
continue;
}
wtrRateForRoyLp.StreamId = streamForRoyLp.Id;
wtrRateForRoyLp.TreatyTitleId = rows[i].ItemArray[1].ToString().Trim();
wtrRateForRoyLp.StreamArticleId = rows[i].ItemArray[26].ToString().Trim();
wtrRateForRoyLp.StreamComment = rows[i].ItemArray[27].ToString().Trim();
wtrRateForRoyLp.Rate = rows[i].ItemArray[24].ToString().Trim();
wtrRateForRoyLp.ArtileLine = rows[i].ItemArray[25].ToString().Trim();
if (isUpdate(wtrRateForRoyLp) == null)
{
allWtrRates.Add(wtrRateForRoyLp);
}
var wtrRateForServiceFeesTech = new WTRate() { };
wtrRateForServiceFeesTech.HostCountryId = hostCountry.Id;
wtrRateForServiceFeesTech.HomeCountryId = partnerCountry.Id;
const string streamTitleForServiceFeesTech = "Service fees (Tech.)";
var streamForServiceFeesTech = _allStreams.FirstOrDefault(b => b.Title == streamTitleForServiceFeesTech);
if (streamForServiceFeesTech == null)
{
publisherStatus.Add(new PublisherStatus
{
Message = String.Format("Stream Title [{0}] Is Not Valid", streamTitleForServiceFeesTech),
Status = false
});
continue;
}
wtrRateForServiceFeesTech.StreamId = streamForServiceFeesTech.Id;
wtrRateForServiceFeesTech.TreatyTitleId = rows[i].ItemArray[1].ToString().Trim();
wtrRateForServiceFeesTech.StreamArticleId = rows[i].ItemArray[32].ToString().Trim();
wtrRateForServiceFeesTech.StreamComment = rows[i].ItemArray[33].ToString().Trim();
wtrRateForServiceFeesTech.Rate = rows[i].ItemArray[28].ToString().Trim();
wtrRateForServiceFeesTech.ArtileLine = rows[i].ItemArray[29].ToString().Trim();
if (isUpdate(wtrRateForServiceFeesTech) == null)
{
allWtrRates.Add(wtrRateForServiceFeesTech);
}
var wtrRateForServiceFeesMan = new WTRate() { };
wtrRateForServiceFeesMan.HostCountryId = hostCountry.Id;
wtrRateForServiceFeesMan.HomeCountryId = partnerCountry.Id;
const string streamTitleForServiceFeesMan = "Service fees (Man.)";
var streamForServiceFeesMan = _allStreams.FirstOrDefault(b => b.Title == streamTitleForServiceFeesMan);
if (streamForServiceFeesMan == null)
{
publisherStatus.Add(new PublisherStatus
{
Message = String.Format("Stream Title [{0}] Is Not Valid", streamTitleForServiceFeesMan),
Status = false
});
continue;
}
wtrRateForServiceFeesMan.StreamId = streamForServiceFeesMan.Id;
wtrRateForServiceFeesMan.TreatyTitleId = rows[i].ItemArray[1].ToString().Trim();
wtrRateForServiceFeesMan.StreamArticleId = rows[i].ItemArray[32].ToString().Trim();
wtrRateForServiceFeesMan.StreamComment = rows[i].ItemArray[33].ToString().Trim();
wtrRateForServiceFeesMan.Rate = rows[i].ItemArray[30].ToString().Trim();
wtrRateForServiceFeesMan.ArtileLine = rows[i].ItemArray[31].ToString().Trim();
if (isUpdate(wtrRateForServiceFeesMan) == null)
{
allWtrRates.Add(wtrRateForServiceFeesMan);
}
var wtrRateForCg = new WTRate() { };
wtrRateForCg.HostCountryId = hostCountry.Id;
wtrRateForCg.HomeCountryId = partnerCountry.Id;
const string streamTitleForCg = "Capital gains";
var streamForCg = _allStreams.FirstOrDefault(b => b.Title == streamTitleForCg);
if (streamForCg == null)
{
publisherStatus.Add(new PublisherStatus
{
Message = String.Format("Stream Title [{0}] Is Not Valid", streamTitleForCg),
Status = false
});
continue;
}
wtrRateForCg.StreamId = streamForCg.Id;
wtrRateForCg.TreatyTitleId = rows[i].ItemArray[1].ToString().Trim();
wtrRateForCg.StreamArticleId = rows[i].ItemArray[36].ToString().Trim();
wtrRateForCg.StreamComment = rows[i].ItemArray[37].ToString().Trim();
wtrRateForCg.Rate = rows[i].ItemArray[34].ToString().Trim();
wtrRateForCg.ArtileLine = rows[i].ItemArray[35].ToString().Trim();
if (isUpdate(wtrRateForCg) == null)
{
allWtrRates.Add(wtrRateForCg);
}
}
}
}
/// <summary>
/// Deprecated Method for adding a new object to the WTRates EntitySet. Consider using the .Add method of the associated ObjectSet<T> property instead.
/// </summary>
public void AddToWTRates(WTRate wTRate)
{
base.AddObject("WTRates", wTRate);
}
private WTRate isUpdate(WTRate wtrRateForDivPortFolio)
{
using (var work = new UnitOfWork())
{
var repo = RepositoryContainer.GetRepository<WTRate>(work);
var stream = _allStreams.FirstOrDefault(c => c.Id == wtrRateForDivPortFolio.StreamId);
var wtrItem =
repo.Filter(
b =>
b.HomeCountryId == wtrRateForDivPortFolio.HomeCountryId &&
b.HostCountryId == wtrRateForDivPortFolio.HostCountryId
&& b.StreamId == stream.Id && b.TreatyTitleId == wtrRateForDivPortFolio.TreatyTitleId).
FirstOrDefault();
if (wtrItem != null)
{
wtrItem.StreamArticleId = wtrRateForDivPortFolio.StreamArticleId;
wtrItem.StreamComment = wtrRateForDivPortFolio.StreamComment;
wtrItem.Rate = wtrRateForDivPortFolio.Rate;
wtrItem.ArtileLine = wtrRateForDivPortFolio.ArtileLine;
work.SaveChanges();
return wtrItem;
}
else return null;
}
return null;
}