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