public static List<TreatyCountryPair> GetPublishedCountryPairs()
{
using (var work = new UnitOfWork())
{
var repository = RepositoryContainer.GetRepository<TreetyMetadata>(work);
var treatyItems=repository.Filter(c => true).Select(
o => new {HostCountryId = o.HostCountryId, PartnerCountryId = o.PartnerCountryId});
var items = new List<TreatyCountryPair>();
var allCountries = RepositoryContainer.GetRepository<Country>(work).All().ToList();
int i = 0;
foreach (var treatyItem in treatyItems)
{
var countries = GetPairCountries(treatyItem.HostCountryId, treatyItem.PartnerCountryId, allCountries);
items.Add(new TreatyCountryPair {
Serial = ++i,
FirstCountryCode = countries[0].Name,
OtherCountryCode = countries[1].Name,
AsInSheet = string.Format("{0}-{1}", countries[0].Code, countries[1].Code),
AsInSheetAlternate = string.Format("{0}-{1}", countries[1].Code, countries[0].Code)
});
}
return items;
}
}
public TreatyMetadata GetASingleTreety(int id)
{
using (var work = new UnitOfWork())
{
var repository = RepositoryContainer.GetRepository<DataAccess.TreetyMetadata>(work);
return Mapper.ToDataTransferObject(repository.Filter(t => t.Id == id).FirstOrDefault());
}
}
public List<DomesticWhtRate> GetSingleDomesticRateFunc(string countryCode)
{
using (var work = new UnitOfWork())
{
var repository = RepositoryContainer.GetWTRateRepository(work);
return repository.GetSingleDomesticRate(countryCode);
}
}
public List<TpArticle> GetAllTpArticles()
{
using (var work = new UnitOfWork())
{
var repository = RepositoryContainer.GetRepository<DataAccess.TreetyArticleMetadata>(work);
return repository.All().Select(b => new TpArticle {ArticleNo = b.ArticleNo,ArticleTitle = b.ArticleTitle,ConceptId = b.ConceptId,ConceptTitle = b.Concept}).ToList();
}
}
public PublisherBase(string fileSource)
{
_connectionString = string.Format(_connectionString, fileSource);
using (var work = new UnitOfWork())
{
_countries = RepositoryContainer.GetRepository<Country>(work).All().ToList();
}
}
public WTRateWithMetaData GetSingleWTRateFunc(int hostCountryId, int homeCountryId, string streamId)
{
using (var work = new UnitOfWork())
{
var repository = RepositoryContainer.GetWTRateRepository(work);
return repository.GetSingleWTRate(hostCountryId, homeCountryId, streamId);
}
}
public List<TpTreaty> GetTpTreaty(string hostCountryCode, string searchQuery)
{
if (!string.IsNullOrEmpty(searchQuery))
{
return GetTpTreatyByArticleContentSearch(hostCountryCode, searchQuery.Trim());
}
using (var work = new UnitOfWork())
{
var allTpTreaties = new List<TpTreaty>();
var tpTreaties = new TpTreatyRepository(work).GetTpTreaty(hostCountryCode, searchQuery).ToList();
var distinctConceptIds = tpTreaties.Select(b => b.OcdeArt1_ConceptId).Distinct()
.Union(tpTreaties.Select(b => b.OcdeArt2_ConceptId).Distinct())
.Union(tpTreaties.Select(b => b.OcdeArt3_ConceptId).Distinct())
.Union(tpTreaties.Select(b => b.CompetentAuthority_ConceptId).Distinct())
.Union(tpTreaties.Select(b => b.ReliefFromDouble_ConceptId).Distinct())
.Where(b => b!= null).Select(b => b);
var conceptIds = string.Join(",", distinctConceptIds);
var partnerCountryCodes = string.Join(",", tpTreaties.Select(b => b.Country1.Code).ToList());
var articles = new TreetyBusiness().GetArticlesByHostCountry(hostCountryCode, partnerCountryCodes, conceptIds);
var treatyBusiness = new TreetyBusiness();
foreach (var treaty in tpTreaties)
{
var countryPair = treatyBusiness.GetPairCountryCode(hostCountryCode, treaty.Country1.Code);
allTpTreaties.Add(new TpTreaty()
{
Id = treaty.Id,
OcdeArt1 = !string.IsNullOrEmpty(treaty.OcdeArt1) ? treaty.OcdeArt1 : string.Empty,
OcdeArt2 = !string.IsNullOrEmpty(treaty.OcdeArt2) ? treaty.OcdeArt2 : string.Empty,
TreatyPartnerCountry = treaty.Country1.Name,
TreatyPartnerCountryCode = treaty.Country1.Code,
CompetentAuthority = !string.IsNullOrEmpty(treaty.CompetentAuthority) ? treaty.CompetentAuthority : string.Empty,
ReliefFromDouble = !string.IsNullOrEmpty(treaty.ReliefFromDouble) ? treaty.ReliefFromDouble : string.Empty,
OcdeArt1ConceptId = treaty.OcdeArt1_ConceptId,
OcdeArt2ConceptId = treaty.OcdeArt2_ConceptId,
ReliefFromDoubleConceptId = treaty.ReliefFromDouble_ConceptId,
CompetentAuthorityConceptId = treaty.CompetentAuthority_ConceptId,
OcdeArt3 = treaty.OcdeArt3,
OcdeArt3ConceptId = treaty.OcdeArt3_ConceptId,
IsContentAvailbleForCompetentAuthority = articles.Count(b => (b.HostCountryCode == countryPair[0] && b.PartnerCountryCode == countryPair[1])
&& b.ConceptId == treaty.CompetentAuthority_ConceptId && b.ArticleContent.Length > 0) > 0,
IsContentAvailbleForReliefFromDouble = articles.Count(b => (b.HostCountryCode == countryPair[0] && b.PartnerCountryCode == countryPair[1])
&& b.ConceptId == treaty.ReliefFromDouble_ConceptId && b.ArticleContent.Length > 0) > 0,
IsContentAvailbleForOcdeArt3 = articles.Count(b => (b.HostCountryCode == countryPair[0] && b.PartnerCountryCode == countryPair[1])
&& b.ConceptId == treaty.OcdeArt3_ConceptId && b.ArticleContent.Length > 0) > 0,
IsContentAvailbleForOcdeArt2 = articles.Count(b => (b.HostCountryCode == countryPair[0] && b.PartnerCountryCode == countryPair[1])
&& b.ConceptId == treaty.OcdeArt2_ConceptId && b.ArticleContent.Length > 0) > 0,
IsContentAvailbleForOcdeArt1 = articles.Count(b => (b.HostCountryCode == countryPair[0] && b.PartnerCountryCode == countryPair[1])
&& b.ConceptId == treaty.OcdeArt1_ConceptId && b.ArticleContent.Length > 0) > 0
});
}
allTpTreaties = allTpTreaties.OrderBy(a => a.TreatyPartnerCountry).ToList();
return allTpTreaties;
}
}
public List<TreatyArticleMetadata> GetArticlesFunc(int hostCountryId, int partnerCountryId, string concept)
{
using (var work = new UnitOfWork())
{
var repository = RepositoryContainer.GetArticleRepository(work);
// repository.Filter(t => ((t.HostCountryId == hostCountryId && t.PartnerCountryId==partnerCountryId) || (t.PartnerCountryId == hostCountryId && t.HostCountryId==partnerCountryId)) && t.TreetyArticleMetadatas.Any(a => a.Concept == concept))
return Mapper.ToDataTransferObjects(repository.GetArticles(hostCountryId, partnerCountryId, concept));
}
}
public List<TpArticleMeataData> GetArticlesByHostCountry(string hostCountryCode, string partnerCountryCodes, string conceptIds)
{
using (var work = new UnitOfWork())
{
var repository = RepositoryContainer.GetArticleRepository(work);
var articles = repository.GetArticlesByHostCountry(hostCountryCode, partnerCountryCodes, conceptIds).ToList();
return articles;
}
}
public WtrTempData GetAllWTRatesFunc(string hostCountryCode, string[] countryCodeAry)
{
using (var work = new UnitOfWork())
{
var repository = RepositoryContainer.GetWTRateRepository(work);
return repository.GetAllWTRates(hostCountryCode, countryCodeAry);
}
return null;
}
public List<WrapperWTRateContract> GetAllWTRatesFunc(int hostCountryId, int[] countryIdAry)
{
using (var work = new UnitOfWork())
{
var repository = RepositoryContainer.GetWTRateRepository(work);
return repository.GetAllWTRates(hostCountryId, countryIdAry);
}
return null;
}
public List<TpTreaty> GetTpTreatyByArticleContentSearch(string hostCountryCode, string searchQuery)
{
using (var work = new UnitOfWork())
{
var allTpTreaties = new List<TpTreaty>();
var tpTreaties = new TpTreatyRepository(work).GetTpTreaty(hostCountryCode, null).ToList();
var distinctConceptIds = GetDistinctConceptIds(tpTreaties);
var conceptIds = string.Join(",", distinctConceptIds);
if (conceptIds.Length == 0) return allTpTreaties;
var partnerCountryCodes = string.Join(",", tpTreaties.Select(b => string.Format("'{0}'", b.Country1.Code)).ToList());
if (partnerCountryCodes.Length == 0) return allTpTreaties;
var articles = work.UnityDbContext.TreatyArticleContentSearchSp(hostCountryCode, partnerCountryCodes,searchQuery, conceptIds).ToList();
var articlesGroups = (from item in articles
group item by new {item.HostCountryId,item.PartnerCountryId}
into groupedItems
select groupedItems).ToList();
foreach (var itemGroup in articlesGroups)
{
var treaty = tpTreaties.FirstOrDefault(b => (
(b.HostCountryId == itemGroup.Key.HostCountryId && b.PartnerCountryId == itemGroup.Key.PartnerCountryId) ||
(b.HostCountryId == itemGroup.Key.PartnerCountryId && b.PartnerCountryId == itemGroup.Key.HostCountryId)
)
);
if (treaty != null)
{
if (string.IsNullOrEmpty(treaty.OcdeArt1_ConceptId) && string.IsNullOrEmpty(treaty.OcdeArt2_ConceptId) && string.IsNullOrEmpty(treaty.OcdeArt3_ConceptId)
&& string.IsNullOrEmpty(treaty.ReliefFromDouble_ConceptId) && string.IsNullOrEmpty(treaty.CompetentAuthority_ConceptId)) continue;
allTpTreaties.Add(new TpTreaty()
{
Id = treaty.Id,
OcdeArt1 = !string.IsNullOrEmpty(treaty.OcdeArt1) ? treaty.OcdeArt1 : string.Empty,
OcdeArt2 = !string.IsNullOrEmpty(treaty.OcdeArt2) ? treaty.OcdeArt2 : string.Empty,
TreatyPartnerCountry = treaty.Country1.Name,
TreatyPartnerCountryCode = treaty.Country1.Code,
CompetentAuthority = !string.IsNullOrEmpty(treaty.CompetentAuthority) ? treaty.CompetentAuthority : string.Empty,
ReliefFromDouble = !string.IsNullOrEmpty(treaty.ReliefFromDouble) ? treaty.ReliefFromDouble : string.Empty,
OcdeArt1ConceptId = treaty.OcdeArt1_ConceptId,
OcdeArt2ConceptId = treaty.OcdeArt2_ConceptId,
ReliefFromDoubleConceptId = treaty.ReliefFromDouble_ConceptId,
CompetentAuthorityConceptId = treaty.CompetentAuthority_ConceptId,
OcdeArt3 = treaty.OcdeArt3,
OcdeArt3ConceptId = treaty.OcdeArt3_ConceptId,
IsContentAvailbleForCompetentAuthority = itemGroup.Count(b => b.ConceptId == treaty.CompetentAuthority_ConceptId) > 0,
IsContentAvailbleForReliefFromDouble = itemGroup.Count(b => b.ConceptId == treaty.ReliefFromDouble_ConceptId) > 0,
IsContentAvailbleForOcdeArt3 = itemGroup.Count(b => b.ConceptId == treaty.OcdeArt3_ConceptId) > 0,
IsContentAvailbleForOcdeArt2 = itemGroup.Count(b => b.ConceptId == treaty.OcdeArt2_ConceptId) > 0,
IsContentAvailbleForOcdeArt1 = itemGroup.Count(b => b.ConceptId == treaty.OcdeArt1_ConceptId) > 0
});
}
}
return allTpTreaties;
}
}
public static List<DataAccess.Country> GetPairCountries(int firstCountryId, int secondCountryId)
{
using (var work = new UnitOfWork())
{
var allCountry = new Repository<Country>(work).All().ToList();
var countries = new List<DataAccess.Country>();
countries.Add(allCountry.FirstOrDefault(c => c.Id == firstCountryId));
countries.Add(allCountry.FirstOrDefault(c => c.Id == secondCountryId));
countries = countries.OrderBy(c => c.Code).ToList();
return countries;
}
}
private IEnumerable<PublisherStatus> CheckConceptIdInfo(DataRowCollection contentRows)
{
var status = new List<PublisherStatus>();
using (var work = new UnitOfWork())
{
var treatyArticleMetaDataRepo = RepositoryContainer.GetRepository<TreetyArticleMetadata>(work);
var metaInfo = contentRows[0];
var conceptIdForocdeArt1 = metaInfo.ItemArray[1].ToString().Trim();
var ocdeArt1 = treatyArticleMetaDataRepo.Filter(b => b.ConceptId == conceptIdForocdeArt1).FirstOrDefault();
if(ocdeArt1 == null)
{
status.Add(new PublisherStatus
{
Status = false,
Message = "Article Id " + metaInfo.ItemArray[1].ToString().Trim() + "Does Not Exist"
});
}
var conceptIdForocdeArt2 = metaInfo.ItemArray[2].ToString().Trim();
var ocdeArt2 = treatyArticleMetaDataRepo.Filter(b => b.ConceptId == conceptIdForocdeArt2).FirstOrDefault();
if (ocdeArt2 == null)
{
status.Add(new PublisherStatus
{
Status = false,
Message = "Article Id " + metaInfo.ItemArray[2].ToString().Trim() + "Does Not Exist"
});
}
var conceptIdForReliefFromDouble = metaInfo.ItemArray[3].ToString().Trim();
var reliefFromDouble = treatyArticleMetaDataRepo.Filter(b => b.ConceptId == conceptIdForReliefFromDouble).FirstOrDefault();
if (reliefFromDouble == null)
{
status.Add(new PublisherStatus
{
Status = false,
Message = "Article Id " + metaInfo.ItemArray[3].ToString().Trim() + "Does Not Exist"
});
}
var conceptIdForCompetentAuthority = metaInfo.ItemArray[4].ToString().Trim();
var competentAuthority = treatyArticleMetaDataRepo.Filter(b => b.ConceptId == conceptIdForCompetentAuthority).FirstOrDefault();
if (competentAuthority == null)
{
status.Add(new PublisherStatus
{
Status = false,
Message = "Article Id " + metaInfo.ItemArray[4].ToString().Trim() + "Does Not Exist"
});
}
}
return status;
}
public string GetArticleData(string hostCountryCode, string partnerCountryCode, string conceptId)
{
using (var work = new UnitOfWork())
{
var repository = RepositoryContainer.GetArticleRepository(work);
var countryList = new TreetyBusiness().GetPairCountryCode(hostCountryCode, partnerCountryCode);
var reportContent = repository.GetArticlesByHostAndPartnerCountryCode(countryList[0], countryList[1], conceptId).FirstOrDefault();
if(reportContent != null)
{
return reportContent.ArticleContent;
}
else
{
return string.Empty;
}
}
}
private IEnumerable<PublisherStatus> PublishContent(DataRowCollection contentRows, string sheetName)
{
var allStatus = new List<PublisherStatus>();
if (contentRows.Count >= 1)
{
using (var work = new UnitOfWork())
{
var countryRepo = RepositoryContainer.GetRepository<Nest.DataAccess.Country>(work);
var domesticRateRepo = RepositoryContainer.GetRepository<Nest.DataAccess.DomesticWHTRate>(work);
var allCountries = countryRepo.All().ToList();
var dbHostCountry = allCountries.FirstOrDefault(b => b.Code == contentRows[1].ItemArray[0].ToString().Trim());
if (dbHostCountry == null)
{
allStatus.Add(new PublisherStatus() { Message = "Host Country Name Doesn't Exist In DataBase", Status = false });
allStatus.Add(new PublisherStatus
{
Status = false,
Message = "Data uploading failed for sheet " + sheetName
});
return allStatus;
}
//var conceptIdExistStatus = CheckConceptIdInfo(contentRows);
//var idExistStatus = conceptIdExistStatus.ToList();
//allStatus.AddRange(idExistStatus);
//if (idExistStatus.Any(b => b.Status == false))
//{
// allStatus.Add(new PublisherStatus
// {
// Status = false,
// Message = "Data uploading failed for sheet " + sheetName
// });
// return allStatus;
//}
// HostCountry = dbHostCountry;
if (contentRows.Count >= 2)
{
for (var index = 1; index < contentRows.Count; index++)
{
if (string.IsNullOrEmpty(contentRows[index].ItemArray[0].ToString().Trim())) continue;
var dbPartenerCountry = allCountries.FirstOrDefault(b => b.Code == contentRows[index].ItemArray[0].ToString().Trim());
if (dbPartenerCountry == null)
{
allStatus.Add(new PublisherStatus() { Message = "Partner Country Code " + contentRows[index].ItemArray[0].ToString().Trim() + " Doesn't Exist In DataBase", Status = false });
}
if (dbPartenerCountry != null)
{
var dbTpTreaty = domesticRateRepo.Filter(b => b.Code == dbHostCountry.Code).FirstOrDefault();
if (dbTpTreaty == null)
{
dbTpTreaty = new Nest.DataAccess.DomesticWHTRate();
domesticRateRepo.Add(dbTpTreaty);
}
dbTpTreaty.Code = contentRows[index].ItemArray[0].ToString().Trim();
dbTpTreaty.countryId = int.Parse(contentRows[index].ItemArray[1].ToString().Trim());
dbTpTreaty.DividendRate = contentRows[index].ItemArray[2].ToString().Trim();
dbTpTreaty.InterestRate = contentRows[index].ItemArray[3].ToString().Trim();
dbTpTreaty.RoyalityRate=contentRows[index].ItemArray[4].ToString().Trim();
}
}
if (allStatus.Any(b => b.Status == false) == false)
{
work.SaveChanges();
allStatus.Add(new PublisherStatus() { Message = "Data uploaded sucessfully for sheet " + sheetName, Status = true });
}
else
{
allStatus.Add(new PublisherStatus
{
Status = false,
Message = "Data uploading failed for sheet " + sheetName
});
}
}
}
}
return allStatus;
}
private IEnumerable<PublisherStatus> PublishSheetData(string sheetName, IEnumerable<PublisherStatus> currentStatus)
{
var publisherStatus = new List<PublisherStatus>();
try
{
OpenExcelConnection();
using (var work = new UnitOfWork())
{
//Implement The logic
OleDbDataAdapter articleAbapter;
DataSet articleSet;
articleAbapter = new OleDbDataAdapter(string.Format("select * from [{0}]", sheetName), _connection);
articleSet = new DataSet("TestExcel");
articleAbapter.Fill(articleSet, sheetName);
if (articleSet.Tables.Count > 0 && articleSet.Tables[0].Rows.Count > 1)
{
var rows = articleSet.Tables[0].Rows;
if (sheetName.Trim().Equals("Domesticrate$", StringComparison.InvariantCultureIgnoreCase))
{
var status = PublishContent(rows, sheetName);
publisherStatus.AddRange(status);
}
/*else if (sheetName.Equals("Metadata$", StringComparison.InvariantCultureIgnoreCase))
{
if (currentStatus.Any(b => b.Status == false) == false)
{
publisherStatus.AddRange(PublishMetaData(rows, sheetName));
}
else
{
publisherStatus.Add(new PublisherStatus
{
Status = false,
Message = "Data uploading failed for sheet " + sheetName
});
}
}*/
return publisherStatus;
}
}
}
catch (Exception e)
{
publisherStatus.Add(new PublisherStatus
{
Status = false,
Message = string.Format("Error occured for sheet {0}. [{1}]", sheetName, e.Message)
});
return publisherStatus;
}
finally
{
CloseExcelConnection();
}
return publisherStatus;
}
public byte[] GetTreatyContent(int treatyId, out string fileName)
{
using (var work = new UnitOfWork())
{
var repository = RepositoryContainer.GetRepository<DataAccess.TreetyMetadata>(work);
var treatyContent = repository.Filter(t => t.Id == treatyId).FirstOrDefault().TreatyPdf;
fileName = "TreatyPdf";
return treatyContent;
}
}
public List<TreatyWithWTRRates> GetTreatyWithWTRRates(string hostCountryCode)
{
using (var work = new UnitOfWork())
{
var treatyMetadata = new TreatyRepository(work).GetTreaty(hostCountryCode, "").ToList();
var treatyBusiness = new TreetyBusiness();
var conceptIds = "DIV_10" + "," + "INT_11" + "," + "ROY_12";
var partnerCountryCodes = string.Join(",", treatyMetadata.Select(b => b.Country1.Code).ToList());
var partnerCountryCodes2 = string.Join(",", treatyMetadata.Select(b => b.Country.Code).ToList());
partnerCountryCodes = partnerCountryCodes + "," + partnerCountryCodes2;
var articles = new TreetyBusiness().GetArticlesByHostCountry(hostCountryCode, partnerCountryCodes, conceptIds);
List<TreatyWithWTRRates> listWTRRates = new List<TreatyWithWTRRates>();
foreach (var treetyMD in treatyMetadata)
{
var list = new WTRateRepository(work).GetAllWTRatesValue(treetyMD.HostCountryId, treetyMD.PartnerCountryId);
if (list.Count == 0) list = new WTRateRepository(work).GetAllWTRatesValue(treetyMD.PartnerCountryId, treetyMD.HostCountryId);
var divStreamId = ConfigurationManager.AppSettings["DivSubstantialHoldingRateStreamId"].Trim();
var divportfolioStreamId = ConfigurationManager.AppSettings["DIVPortfolioStreamId"].Trim();
var intGeneralStreamId = ConfigurationManager.AppSettings["IntGeneralStreamId"].Trim();
var intCbStreamId = ConfigurationManager.AppSettings["InterestCBRateStreamId"].Trim();
var intBankStreamId = ConfigurationManager.AppSettings["InterestBankRateStreamId"].Trim();
var royPatentStreamId = ConfigurationManager.AppSettings["RoyalityPatentRateStreamId"].Trim();
var royTmStreamId = ConfigurationManager.AppSettings["RoyalityTmRateStreamId"].Trim();
var royCrStreamId = ConfigurationManager.AppSettings["RoyalityCrRateStreamId"].Trim();
var royLpStreamId = ConfigurationManager.AppSettings["RoyalityLpRateStreamId"].Trim();
var divValue = list.Where(b => b.StreamId.Equals(divStreamId)).FirstOrDefault();
var divportfolioRatevalue = list.Where(b => b.StreamId.Equals(divportfolioStreamId)).FirstOrDefault();
var intGeneralValue = list.Where(b => b.StreamId.Equals(intGeneralStreamId)).FirstOrDefault();
var intCbValue = list.Where(b => b.StreamId.Equals(intCbStreamId)).FirstOrDefault();
var intBankValue = list.Where(b => b.StreamId.Equals(intBankStreamId)).FirstOrDefault();
var royPatentValue = list.Where(b => b.StreamId.Equals(royPatentStreamId)).FirstOrDefault();
var royTmValue = list.Where(b => b.StreamId.Equals(royTmStreamId)).FirstOrDefault();
var royLpValue = list.Where(b => b.StreamId.Equals(royLpStreamId)).FirstOrDefault();
var royCrValue = list.Where(b => b.StreamId.Equals(royCrStreamId)).FirstOrDefault();
var countryPair = treatyBusiness.GetPairCountryCode(treetyMD.Country.Code, treetyMD.Country1.Code);
var partnerCountryName = treetyMD.Country1.Code == hostCountryCode ? treetyMD.Country.Name : treetyMD.Country1.Name;
var partnerCountryId = treetyMD.Country1.Code == hostCountryCode ? treetyMD.Country.Id : treetyMD.Country1.Id;
TreatyWithWTRRates trWithWTR = new TreatyWithWTRRates()
{
DivSubstantialHoldingRate = divValue == null ? "" : divValue.Rate,
DivPortfolioRate = divportfolioRatevalue == null ? "" : divportfolioRatevalue.Rate,
InterestGeneralRate = intGeneralValue == null ? "" : intGeneralValue.Rate,
InterestCbRate = intCbValue == null ? "" : intCbValue.Rate,
InterestBankRate = intBankValue == null ? "" : intBankValue.Rate,
RoyaltyPatentRate = royPatentValue == null ? "" : royPatentValue.Rate,
RoyaltyCrRate = royCrValue == null ? "" : royCrValue.Rate,
RoyaltyLpRate = royLpValue == null ? "" : royLpValue.Rate,
RoyaltyTmRate = royTmValue == null ? "" : royTmValue.Rate,
DivSubstantialHoldingRateArticleLine = divValue == null ? "" : divValue.ArticleLine,
DivportfolioRateArticleLine = divportfolioRatevalue == null ? "" : divportfolioRatevalue.ArticleLine,
InterestGeneralRateArticleLine = intGeneralValue == null ? "" : intGeneralValue.ArticleLine,
InterestBankRateArticleLine = intBankValue == null ? "" : intBankValue.ArticleLine,
InterestCbRateArticleLine = intCbValue == null ? "" : intCbValue.ArticleLine,
RoyaltyPatentRateArticleLine = royPatentValue == null ? "" : royPatentValue.ArticleLine,
RoyaltyCrRateArticleLine = royCrValue == null ? "" : royCrValue.ArticleLine,
RoyaltyLpRateArticleLine = royLpValue == null ? "" : royLpValue.ArticleLine,
RoyaltyTmRateArticleLine = royTmValue == null ? "" : royTmValue.ArticleLine,
Country = partnerCountryName,
CountryId = partnerCountryId,
CountryCode = countryPair[0].Equals(hostCountryCode) ? countryPair[1] : countryPair[0],
EffectiveDate = treetyMD.EffectiveDate,
EntryIntoForce = treetyMD.EntryIntoForce,
Id = treetyMD.Id,
IsContentAvailable = (treetyMD.TreatyPdf != null && treetyMD.TreatyPdf.Length > 0),
Status = treetyMD.Status,
Title = treetyMD.Title,
IsContentAvailableForDividendRate = articles.Count(b => (b.HostCountryCode == countryPair[0] && b.PartnerCountryCode == countryPair[1])
&& b.ConceptId == "DIV_10" && b.ArticleContent.Length > 0) > 0,
IsContentAvailableForInterestRate = articles.Count(b => (b.HostCountryCode == countryPair[0] && b.PartnerCountryCode == countryPair[1])
&& b.ConceptId == "INT_11" && b.ArticleContent.Length > 0) > 0,
IsContentAvailableForRoyaltyRate = articles.Count(b => (b.HostCountryCode == countryPair[0] && b.PartnerCountryCode == countryPair[1])
&& b.ConceptId == "ROY_12" && b.ArticleContent.Length > 0) > 0,
};
listWTRRates.Add(trWithWTR);
}
listWTRRates = listWTRRates.OrderBy(b => b.Country).ToList();
var singleDomesticItem = GetsingleDomesticRate(hostCountryCode);
listWTRRates.Insert(0, singleDomesticItem);
return listWTRRates;
}
}
private void InitializeMemberData(string sheetName, UnitOfWork work, List<PublisherStatus> publisherStatus)
{
_hostCountry = GetHostCountry(sheetName);
if (_hostCountry == null)
{
publisherStatus.Add(new PublisherStatus
{
Status = false,
Message = string.Format("Host country not found for sheet {0}.", sheetName)
});
return;
}
_treetyIds =
RepositoryContainer.GetRepository<TreetyMetadata>(work).Filter(b => b.HostCountryId == _hostCountry.Id || b.PartnerCountryId == _hostCountry.Id).ToList();
if (_treetyIds == null)
{
publisherStatus.Add(new PublisherStatus
{
Status = false,
Message = string.Format("No Treaty available for sheet {0}.", sheetName)
});
return;
}
var query =
@"select TRM.ArticleId,TM.hostCountryId AS HostCountryId,TM.partnerCountryId AS PartnerCountryId from dbo.TreetyMetadata TM join dbo.TreetyArticleMetadata TRM on TM.Id = TRM.TreetyMetadataId where TM.HostCountryId = " +
_hostCountry.Id.ToString(CultureInfo.InvariantCulture) + "OR TM.PartnerCountryId = " + _hostCountry.Id.ToString(CultureInfo.InvariantCulture);
_articleIds = work.UnityDbContext.ExecuteStoreQuery<ArticleMetaData>(query).ToList();
_allStreams = RepositoryContainer.GetRepository<Stream>(work).All().ToList();
}
private IEnumerable<PublisherStatus> PublishContent(DataRowCollection contentRows, string sheetName)
{
var allStatus = new List<PublisherStatus>();
if (contentRows.Count >= 1)
{
using (var work = new UnitOfWork())
{
var countryRepo = RepositoryContainer.GetRepository<Nest.DataAccess.Country>(work);
var tpTreatyRepo = RepositoryContainer.GetRepository<Nest.DataAccess.TpTreaty>(work);
var allCountries = countryRepo.All().ToList();
var dbHostCountry = allCountries.FirstOrDefault(b => b.Name == contentRows[0].ItemArray[0].ToString().Trim());
var removeCountryList = tpTreatyRepo.Delete(b => b.HostCountryId == dbHostCountry.Id);
var metaInfo = contentRows[0];
if(dbHostCountry == null)
{
allStatus.Add(new PublisherStatus() { Message = "Host Country Name Doesn't Exist In DataBase" ,Status = false});
allStatus.Add(new PublisherStatus
{
Status = false,
Message = "Data uploading failed for sheet " + sheetName
});
return allStatus;
}
//var conceptIdExistStatus = CheckConceptIdInfo(contentRows);
//var idExistStatus = conceptIdExistStatus.ToList();
//allStatus.AddRange(idExistStatus);
//if (idExistStatus.Any(b => b.Status == false))
//{
// allStatus.Add(new PublisherStatus
// {
// Status = false,
// Message = "Data uploading failed for sheet " + sheetName
// });
// return allStatus;
//}
HostCountry = dbHostCountry;
if (contentRows.Count >= 2)
{
for (var index = 1; index < contentRows.Count; index++)
{
if(string.IsNullOrEmpty(contentRows[index].ItemArray[0].ToString().Trim()))continue;
var dbPartenerCountry = allCountries.FirstOrDefault(b => b.Name == contentRows[index].ItemArray[0].ToString().Trim());
if (dbPartenerCountry == null)
{
allStatus.Add(new PublisherStatus() { Message = "Partner Country Name " + contentRows[index].ItemArray[0].ToString().Trim() + " Doesn't Exist In DataBase", Status = false });
}
if (dbPartenerCountry != null)
{
var dbTpTreaty = tpTreatyRepo.Filter(b => b.HostCountryId == dbHostCountry.Id && b.PartnerCountryId == dbPartenerCountry.Id).FirstOrDefault();
if (dbTpTreaty == null)
{
dbTpTreaty = new Nest.DataAccess.TpTreaty();
tpTreatyRepo.Add(dbTpTreaty);
}
dbTpTreaty.HostCountryId = dbHostCountry.Id;
dbTpTreaty.PartnerCountryId = dbPartenerCountry.Id;
dbTpTreaty.OcdeArt1 = contentRows[index].ItemArray[1].ToString().Trim().Equals("None", StringComparison.InvariantCultureIgnoreCase) ? null : contentRows[index].ItemArray[1].ToString().Trim();
dbTpTreaty.OcdeArt2 = contentRows[index].ItemArray[2].ToString().Trim().Equals("None", StringComparison.InvariantCultureIgnoreCase) ? null : contentRows[index].ItemArray[2].ToString().Trim();
dbTpTreaty.ReliefFromDouble = contentRows[index].ItemArray[3].ToString().Trim().Equals("None", StringComparison.InvariantCultureIgnoreCase) ? null : contentRows[index].ItemArray[3].ToString().Trim();
dbTpTreaty.CompetentAuthority = contentRows[index].ItemArray[4].ToString().Trim().Equals("None", StringComparison.InvariantCultureIgnoreCase) ? null : contentRows[index].ItemArray[4].ToString().Trim();
dbTpTreaty.OcdeArt3 = contentRows[index].ItemArray[5].ToString().Trim().Equals("None", StringComparison.InvariantCultureIgnoreCase) ? null : contentRows[index].ItemArray[5].ToString().Trim();
dbTpTreaty.OcdeArt1_ConceptId = contentRows[index].ItemArray[1].ToString().Trim().Length > 0 && contentRows[index].ItemArray[1].ToString().Trim().ToLower() != "None".ToLower() ? metaInfo.ItemArray[1].ToString().Trim() : null;
dbTpTreaty.OcdeArt2_ConceptId = contentRows[index].ItemArray[2].ToString().Trim().Length > 0 && contentRows[index].ItemArray[2].ToString().Trim().ToLower() != "None".ToLower() ? metaInfo.ItemArray[2].ToString().Trim() : null;
dbTpTreaty.ReliefFromDouble_ConceptId = contentRows[index].ItemArray[3].ToString().Trim().Length > 0 && contentRows[index].ItemArray[3].ToString().Trim().ToLower() != "None".ToLower() ? metaInfo.ItemArray[3].ToString().Trim() : null;
dbTpTreaty.CompetentAuthority_ConceptId = contentRows[index].ItemArray[4].ToString().Trim().Length > 0 && contentRows[index].ItemArray[4].ToString().Trim().ToLower() != "None".ToLower() ? metaInfo.ItemArray[4].ToString().Trim() : null;
dbTpTreaty.OcdeArt3_ConceptId = contentRows[index].ItemArray[5].ToString().Trim().Length > 0 && contentRows[index].ItemArray[5].ToString().Trim().ToLower() != "None".ToLower() ? metaInfo.ItemArray[5].ToString().Trim() : null;
}
}
if(allStatus.Any(b => b.Status == false) == false)
{
work.SaveChanges();
allStatus.Add(new PublisherStatus() { Message = "Data uploaded sucessfully for sheet " + sheetName ,Status = true});
}
else
{
allStatus.Add(new PublisherStatus
{
Status = false,
Message = "Data uploading failed for sheet " + sheetName
});
}
}
}
}
return allStatus;
}
public List<TreatyMetadata> GetTreetyFunc(int hostCountryId, int partnerCountryId)
{
using (var work = new UnitOfWork())
{
var repository = RepositoryContainer.GetRepository<DataAccess.TreetyMetadata>(work);
return Mapper.ToDataTransferObjects(repository.Filter(t => t.HostCountryId == hostCountryId && t.PartnerCountryId == partnerCountryId).ToList());
}
}
private void UpdateConceptAndArticle(UnitOfWork work, DataRow row, TreetyMetadata treatyMetadata, List<PublisherStatus> publisherStatus)
{
//var concept = GetConcept(treatyMetadata, row);
//Add To DB
//var article = GetArticle(treatyMetadata, row);
var treatyArticleConceptMetadata = GetArticleConceptMetadata(treatyMetadata, row);
var repository = RepositoryContainer.GetRepository<TreetyArticleMetadata>(work);
var dbTacMetadata =
repository.Filter(
c => c.TreetyMetadataId == treatyMetadata.Id &&
c.ConceptId == treatyArticleConceptMetadata.ConceptId &&
c.ArticleId == treatyArticleConceptMetadata.ArticleId).FirstOrDefault();
if (dbTacMetadata == null)
{
//Add To DB
if (string.IsNullOrWhiteSpace(treatyArticleConceptMetadata.ArticleId))
{
if(!string.IsNullOrWhiteSpace(treatyArticleConceptMetadata.ConceptId))
{
publisherStatus.Add(new PublisherStatus()
{
Status = false,
Message = string.Format("Article ID not found for Concept [{0}]", treatyArticleConceptMetadata.Concept)
});
}
}
else
{
repository.Add(treatyArticleConceptMetadata);
}
}
else
{
//Update
dbTacMetadata.ArticleContent = treatyArticleConceptMetadata.ArticleContent;
dbTacMetadata.ArticleNo = treatyArticleConceptMetadata.ArticleNo;
dbTacMetadata.ArticleTitle = treatyArticleConceptMetadata.ArticleTitle;
dbTacMetadata.Concept = treatyArticleConceptMetadata.Concept;
}
}
private List<TreatyArticleMetadata> GetArticlesFuncByHostAndPartnerCountryCode(string hostCountryCode, string partnerCountryCode, string concept)
{
using (var work = new UnitOfWork())
{
var repository = RepositoryContainer.GetArticleRepository(work);
return Mapper.ToDataTransferObjects(repository.GetArticlesByHostAndPartnerCountryCode(hostCountryCode, partnerCountryCode, concept));
}
}
private List<PublisherStatus> PublishSheetData(string sheetName)
{
var publisherStatus = new List<PublisherStatus>();
try
{
OpenExcelConnection();
using (var work = new UnitOfWork())
{
InitializeMemberData(sheetName, work, publisherStatus);
if (publisherStatus.Any(x => x.Status == false))
{
return publisherStatus;
}
OleDbDataAdapter da;
DataSet ds;
da = new OleDbDataAdapter(string.Format("select * from [{0}]", sheetName), _connection);
ds = new DataSet("TestExcel");
da.Fill(ds, sheetName);
if (ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 2)
{
var rows = ds.Tables[0].Rows;
var allWtrRates = new List<DataAccess.WTRate>();
GetAllWtrRates(sheetName, allWtrRates, rows, publisherStatus, _hostCountry);
if (publisherStatus.Any(c => c.Status == false) == false)
{
var repo = RepositoryContainer.GetRepository<WTRate>(work);
foreach (var wtrRate in allWtrRates)
{
repo.Add(wtrRate);
}
work.SaveChanges();
}
else
{
return publisherStatus;
}
}
}
}
catch (Exception e)
{
publisherStatus.Add(new PublisherStatus
{
Status = false,
Message = string.Format("Error occured for sheet {0}. [{1}]", sheetName, e.Message)
});
return publisherStatus;
}
finally
{
CloseExcelConnection();
}
return publisherStatus;
}
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;
}
private bool CanPublishTreatyData(int hostCountryId, int partnerCountryId, bool forceUpdate)
{
using (var work = new UnitOfWork())
{
var repository = RepositoryContainer.GetRepository<TreetyMetadata>(work);
var count=repository.CountOfEntity(c => c.HostCountryId == hostCountryId && c.PartnerCountryId == partnerCountryId);
return (count <= 0 || forceUpdate);
}
}
private IEnumerable<PublisherStatus> PublishMetaData(DataRowCollection metaDataRows, string sheetName)
{
var allStatus = new List<PublisherStatus>();
using (var work = new UnitOfWork())
{
var tpTreatyMetaDataRepo = RepositoryContainer.GetRepository<Nest.DataAccess.TpMetaData>(work);
if(HostCountry != null)
{
if (metaDataRows.Count > 1)
{
tpTreatyMetaDataRepo.Delete(b => b.HostCountryId == HostCountry.Id);
for (var index = 1; index < metaDataRows.Count; index++)
{
var tpMetaData = new Nest.DataAccess.TpMetaData()
{
Title = metaDataRows[index].ItemArray[0].ToString().Trim().Length > 1 ? metaDataRows[index].ItemArray[0].ToString().Trim() : null,
HostCountryId = HostCountry.Id,
MetaData = metaDataRows[index].ItemArray[1].ToString().Trim().Length > 1 ? metaDataRows[index].ItemArray[1].ToString().Trim() : null
};
tpTreatyMetaDataRepo.Add(tpMetaData);
}
work.SaveChanges();
allStatus.Add(new PublisherStatus() { Message = "Data uploaded sucessfully for sheet " + sheetName, Status = true });
}
}
}
return allStatus;
}
private List<PublisherStatus> PublishSheetData(string sheetName)
{
var publisherStatus = new List<PublisherStatus>();
try
{
OpenExcelConnection();
using (var work = new UnitOfWork())
{
//Implement The logic
OleDbDataAdapter da;
DataSet ds;
da = new OleDbDataAdapter(string.Format("select * from [{0}]", sheetName), _connection);
ds = new DataSet("TestExcel");
da.Fill(ds, sheetName);
if (ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 8)
{
var rows = ds.Tables[0].Rows;
var treatyMetadata = new DataAccess.TreetyMetadata();
RetriveTreatyMetadata(sheetName, treatyMetadata, rows, publisherStatus);
if (publisherStatus.Any(c => c.Status == false) == false)
{
var repository = RepositoryContainer.GetRepository<TreetyMetadata>(work);
var metadata =
repository.Filter(
c => c.HostCountryId == treatyMetadata.HostCountryId &&
c.PartnerCountryId == treatyMetadata.PartnerCountryId &&
c.TitleId == treatyMetadata.TitleId).FirstOrDefault();
if (metadata == null)
{
//Add To DB
repository.Add(treatyMetadata);
work.SaveChanges();
}
else
{
treatyMetadata.Id = metadata.Id;
//Update
UpdateTreatyMetadata(treatyMetadata, metadata);
work.SaveChanges();
}
}
else
{
return publisherStatus;
}
int articleStartIndex = Convert.ToInt32(ConfigurationManager.AppSettings["ArticleConceptRowStartIndex"]);
int totalRows = rows.Count - articleStartIndex;
for (int i = 0; i < totalRows; i++)
{
var row = rows[articleStartIndex + i];
UpdateConceptAndArticle(work, row, treatyMetadata, publisherStatus);
work.SaveChanges();
}
publisherStatus.Add(new PublisherStatus
{
Status = true,
Message = string.Format("Data successfully published for sheet {0}.", sheetName)
});
return publisherStatus;
}
}
}
catch (Exception e)
{
publisherStatus.Add(new PublisherStatus
{
Status = false,
Message = string.Format("Error occured for sheet {0}. [{1}]", sheetName, e.Message)
});
return publisherStatus;
}
finally
{
CloseExcelConnection();
}
return publisherStatus;
}
public Response<List<Treaty>> GetTreaty(string hostCountryCode, string searchInput)
{
try
{
using (var work = new UnitOfWork())
{
var treatyMetadata = new TreatyRepository(work).GetTreaty(hostCountryCode, searchInput).ToList();
var responseData = treatyMetadata.Select(b => new Treaty()
{
Country = b.Country1.Name,
CountryId = b.Country1.Id,
CountryCode = b.Country1.Code,
EffectiveDate = b.EffectiveDate,
EntryIntoForce = b.EntryIntoForce,
Id = b.Id,
IsContentAvailable = (b.TreatyPdf != null && b.TreatyPdf.Length > 0),
Status = b.Status,
Title = b.Title
}).ToList();
var response = new Response<List<Treaty>>();
response.Data = responseData;
response.Success = true;
return response;
}
}
catch (Exception ex)
{
return new Response<List<Treaty>> { Data = new List<Treaty>(), Success = false, ErrorMessage = "An error occured!" };
}
}