//Add Data From Linked Tables for Display
public void EditGroupForDisplay(MeetingPNROutput group)
{
if (group.GDSCode != "")
{
GDSRepository GDSRepository = new GDSRepository();
GDS GDS = GDSRepository.GetGDS(group.GDSCode);
if (GDS != null)
{
group.GDS = GDS;
}
}
if (group.CountryCode != "")
{
CountryRepository countryRepository = new CountryRepository();
Country country = countryRepository.GetCountry(group.CountryCode);
if (country != null)
{
group.Country = country;
}
}
if (group.DefaultLanguageCode != "")
{
LanguageRepository languageRepository = new LanguageRepository();
Language language = languageRepository.GetLanguage(group.DefaultLanguageCode);
if (language != null)
{
group.Language = language;
}
}
}
//Add Data From Linked Tables for Display
public void EditForDisplay(TravelPort travelPort)
{
CountryRepository countryRepository = new CountryRepository();
Country country = new Country();
country = countryRepository.GetCountry(travelPort.CountryCode);
if (country != null)
{
travelPort.CountryName = country.CountryName;
}
CityRepository cityRepository = new CityRepository();
City city = new City();
city = cityRepository.GetCity(travelPort.CityCode);
if (city != null)
{
travelPort.CityName = city.Name;
}
TravelPortTypeRepository travelPortTypeRepository = new TravelPortTypeRepository();
TravelPortType travelPortType = new TravelPortType();
travelPortType = travelPortTypeRepository.GetTravelPortType(travelPort.TravelPortTypeId);
if (travelPortType != null)
{
travelPort.TravelPortTypeDescription = travelPortType.TravelPortTypeDescription;
}
travelPort.LatitudeDecimal = Convert.ToDecimal(travelPort.Latitude);
travelPort.LongitudeDecimal = Convert.ToDecimal(travelPort.Longitude);
}
//Add Data From Linked Tables for Display
public void EditItemForDisplay(PolicyCountryGroupItem policyCountryGroupItem)
{
//PolicyGroupName
PolicyGroupRepository policyGroupRepository = new PolicyGroupRepository();
PolicyGroup policyGroup = policyGroupRepository.GetGroup(policyCountryGroupItem.PolicyGroupId);
policyCountryGroupItem.PolicyGroupName = policyGroup.PolicyGroupName;
//PolicyCountryStatus
if (policyCountryGroupItem.PolicyCountryStatusId != null)
{
int policyCountryStatusId = (int)policyCountryGroupItem.PolicyCountryStatusId;
PolicyCountryStatusRepository policyCountryStatusRepository = new PolicyCountryStatusRepository();
PolicyCountryStatus policyCountryStatus = new PolicyCountryStatus();
policyCountryStatus = policyCountryStatusRepository.GetPolicyCountryStatus(policyCountryStatusId);
if (policyCountryStatus != null)
{
policyCountryGroupItem.PolicyCountryStatusDescription = policyCountryStatus.PolicyCountryStatusDescription;
}
}
//CountryName
CountryRepository countryRepository = new CountryRepository();
Country country = new Country();
country = countryRepository.GetCountry(policyCountryGroupItem.CountryCode);
if (country != null)
{
policyCountryGroupItem.CountryName = country.CountryName;
}
}
//Add Data From Linked Tables for Display
public void EditGroupForDisplay(ClientSubUnit clientSubUnit)
{
//PortraitStatusRepository portraitStatusRepository = new PortraitStatusRepository();
//PortraitStatus portraitStatus = new PortraitStatus();
//portraitStatus = portraitStatusRepository.GetPortraitStatus(clientSubUnit.PortraitStatusId);
//if (portraitStatus != null)
//{
// clientSubUnit.PortraitStatus = portraitStatus.PortraitStatusDescription;
//}
CountryRepository countryRepository = new CountryRepository();
Country country = new Country();
country = countryRepository.GetCountry(clientSubUnit.CountryCode);
if (country != null)
{
clientSubUnit.CountryName = country.CountryName;
}
ClientTopUnitRepository clientTopUnitRepository = new ClientTopUnitRepository();
ClientTopUnit clientTopUnit = new ClientTopUnit();
clientTopUnit = clientTopUnitRepository.GetClientTopUnit(clientSubUnit.ClientTopUnitGuid);
if (clientTopUnit != null)
{
clientSubUnit.ClientTopUnitName = clientTopUnit.ClientTopUnitName;
}
}
//Add Data From Linked Tables for Display
public void EditForDisplay(GSTIdentificationNumber gstIdentificationNumber)
{
CountryRepository countryRepository = new CountryRepository();
Country country = new Country();
country = countryRepository.GetCountry(gstIdentificationNumber.CountryCode);
if (country != null)
{
gstIdentificationNumber.CountryName = country.CountryName;
}
ClientTopUnitRepository clientTopUnitRepository = new ClientTopUnitRepository();
ClientTopUnit clientTopUnit = clientTopUnitRepository.GetClientTopUnit(gstIdentificationNumber.ClientTopUnitGuid);
if (clientTopUnit != null)
{
gstIdentificationNumber.ClientTopUnitName = clientTopUnit.ClientTopUnitName;
}
StateProvinceRepository stateProvinceRepository = new StateProvinceRepository();
StateProvince stateProvince = stateProvinceRepository.GetStateProvinceByCountry(gstIdentificationNumber.CountryCode, gstIdentificationNumber.StateProvinceCode);
if (stateProvince != null)
{
gstIdentificationNumber.StateProvinceName = stateProvince.Name;
}
}
//Add Data From Linked Tables for Display of ClientAccount
public void EditForDisplay(ClientAccount clientAccount)
{
CountryRepository countryRepository = new CountryRepository();
Country country = new Country();
country = countryRepository.GetCountry(clientAccount.CountryCode);
if (country != null)
{
clientAccount.CountryName = country.CountryName;
}
}
//Where Used: ClientWizard.Policies
public Country IsValidCountry(string countryName)
{
HierarchyDC db = new HierarchyDC(Settings.getConnectionString());
CountryRepository supplierRepository = new CountryRepository();
var result = (from n in db.Countries
where n.CountryName.Equals(countryName)
select
n).FirstOrDefault();
return(result);
}
//Get single item
public PolicyLocation GetPolicyLocation(int policyLocationId)
{
HierarchyRepository hierarchyRepository = new HierarchyRepository();
CountryRepository countryRepository = new CountryRepository();
CityRepository cityRepository = new CityRepository();
PolicyLocation policyLocation = new PolicyLocation();
policyLocation = db.PolicyLocations.SingleOrDefault(c => c.PolicyLocationId == policyLocationId);
if (policyLocation != null)
{
if (policyLocation.GlobalFlag)
{
policyLocation.LocationType = "Global";
}
else if (policyLocation.CityCode != null)
{
policyLocation.LocationType = "City";
City city = cityRepository.GetCity(policyLocation.CityCode);
Country country = countryRepository.GetCountry(city.CountryCode);
policyLocation.ParentName = country.CountryName;
}
else if (policyLocation.CountryCode != null)
{
policyLocation.LocationType = "Country";
Country country = countryRepository.GetCountry(policyLocation.CountryCode);
GlobalSubRegion globalSubRegion = hierarchyRepository.GetGlobalSubRegion(country.GlobalSubRegionCode);
policyLocation.ParentName = globalSubRegion.GlobalSubRegionName;
}
else if (policyLocation.GlobalSubRegionCode != null)
{
policyLocation.ParentName = "GlobalSubRegion";
GlobalSubRegion globalSubRegion = hierarchyRepository.GetGlobalSubRegion(policyLocation.GlobalSubRegionCode);
GlobalRegion globalRegion = hierarchyRepository.GetGlobalRegion(globalSubRegion.GlobalRegionCode);
policyLocation.ParentName = globalRegion.GlobalRegionName;
}
else if (policyLocation.GlobalRegionCode != null)
{
policyLocation.LocationType = "GlobalRegion";
GlobalRegion globalRegion = hierarchyRepository.GetGlobalRegion(policyLocation.GlobalRegionCode);
policyLocation.ParentName = globalRegion.Global.GlobalName;
}
}
return(policyLocation);
}
//Get one Item
// public IQueryable<MerchantFee> GetAllItems()
//{
// return db.MerchantFees.OrderBy(c => c.MerchantFeeDescription);
//}
public void EditForDisplay(MerchantFee merchantFee)
{
CountryRepository countryRepository = new CountryRepository();
Country country = new Country();
if (merchantFee.ProductId != null)
{
country = countryRepository.GetCountry(merchantFee.CountryCode);
if (country != null)
{
merchantFee.CountryName = country.CountryName;
}
}
CreditCardVendorRepository creditCardVendorRepository = new CreditCardVendorRepository();
CreditCardVendor creditCardVendor = new CreditCardVendor();
if (merchantFee.ProductId != null)
{
creditCardVendor = creditCardVendorRepository.GetCreditCardVendor(merchantFee.CreditCardVendorCode);
if (creditCardVendor != null)
{
merchantFee.CreditCardVendorName = creditCardVendor.CreditCardVendorName;
}
}
SupplierRepository supplierRepository = new SupplierRepository();
Supplier supplier = new Supplier();
if (merchantFee.ProductId != null)
{
supplier = supplierRepository.GetSupplier(merchantFee.SupplierCode, (int)merchantFee.ProductId);
if (supplier != null)
{
merchantFee.SupplierName = supplier.SupplierName;
}
}
ProductRepository productRepository = new ProductRepository();
Product product = new Product();
if (merchantFee.ProductId != null)
{
product = productRepository.GetProduct((int)merchantFee.ProductId);
if (product != null)
{
merchantFee.ProductName = product.ProductName;
}
}
}
//Add Data From Linked Tables for Display
public void EditForDisplay(Location location)
{
CountryRegionRepository countryRegionRepository = new CountryRegionRepository();
CountryRegion countryRegion = new CountryRegion();
countryRegion = countryRegionRepository.GetCountryRegion(location.CountryRegionId);
CountryRepository countryRepository = new CountryRepository();
Country country = new Country();
country = countryRepository.GetCountry(countryRegion.CountryCode);
location.CountryName = country.CountryName;
location.CountryRegionName = countryRegion.CountryRegionName;
location.CountryCode = country.CountryCode;
}
//Add Data From Linked Tables for Display
public void EditItemForDisplay(FormOfPaymentAdviceMessageGroupItem formOfPaymentAdviceMessageGroupItem)
{
//Supplier / Product
if (formOfPaymentAdviceMessageGroupItem.SupplierCode != null && formOfPaymentAdviceMessageGroupItem.ProductId > 0)
{
SupplierRepository supplierRepository = new SupplierRepository();
Supplier supplier = supplierRepository.GetSupplier(formOfPaymentAdviceMessageGroupItem.SupplierCode, formOfPaymentAdviceMessageGroupItem.ProductId);
if (supplier != null)
{
formOfPaymentAdviceMessageGroupItem.SupplierName = supplier.SupplierName;
}
ProductRepository productRepository = new ProductRepository();
Product product = productRepository.GetProduct(formOfPaymentAdviceMessageGroupItem.ProductId);
if (product != null)
{
formOfPaymentAdviceMessageGroupItem.ProductName = product.ProductName;
}
}
//CountryName
CountryRepository countryRepository = new CountryRepository();
Country country = countryRepository.GetCountry(formOfPaymentAdviceMessageGroupItem.CountryCode);
if (country != null)
{
formOfPaymentAdviceMessageGroupItem.CountryName = country.CountryName;
}
//FormOfPaymentTypeDescription
FormOfPaymentTypeRepository formOfPaymentTypeRepository = new FormOfPaymentTypeRepository();
FormOfPaymentType formOfPaymentType = formOfPaymentTypeRepository.GetFormOfPaymentType(formOfPaymentAdviceMessageGroupItem.FormofPaymentTypeID);
if (formOfPaymentType != null)
{
formOfPaymentAdviceMessageGroupItem.FormOfPaymentTypeDescription = formOfPaymentType.FormOfPaymentTypeDescription;
}
//Set LanguageName to en-gb
LanguageRepository languageRepository = new LanguageRepository();
Language language = languageRepository.GetLanguage("en-GB");
if (language != null)
{
formOfPaymentAdviceMessageGroupItem.LanguageCode = language.LanguageCode;
formOfPaymentAdviceMessageGroupItem.LanguageName = language.LanguageName;
}
}
//Add Data From Linked Tables for Display
public void EditForDisplay(APISCountry apisCountry)
{
CountryRepository countryRepository = new CountryRepository();
Country country = new Country();
country = countryRepository.GetCountry(apisCountry.OriginCountryCode);
if (country != null)
{
apisCountry.OriginCountryName = country.CountryName;
}
country = countryRepository.GetCountry(apisCountry.DestinationCountryCode);
if (country != null)
{
apisCountry.DestinationCountryName = country.CountryName;
}
}
//Where Used: ClientWizard.Policies
public Country IsValidAdminUserCountry(string countryName)
{
HierarchyDC db = new HierarchyDC(Settings.getConnectionString());
CountryRepository supplierRepository = new CountryRepository();
string adminUserGuid = HttpContext.Current.User.Identity.Name.Split(new[] { '|' })[0];
var result = (from n in db.spDesktopDataAdmin_SelectSystemUserCountry_v1(countryName, adminUserGuid)
select
new Country
{
CountryCode = n.CountryCode,
CountryName = n.CountryName
}).FirstOrDefault();
//var result = (from n in db.spDesktopDataAdmin_SelectSystemUserCountry_v1(countryName, adminUserGuid)
// n).FirstOrDefault();
return(result);
}
public PolicyCountryImportStep2VM PreImportCheck(HttpPostedFileBase file, int policyGroupId)
{
//convert file to string so that we can parse
int length = file.ContentLength;
byte[] tempFile = new byte[length];
file.InputStream.Read(tempFile, 0, length);
byte[] array = tempFile.ToArray();
System.Text.UTF8Encoding enc = new System.Text.UTF8Encoding();
string fileToText = fileToText = enc.GetString(array);
// Create the xml document container, this will be used to store the data after the checks
XmlDocument doc = new XmlDocument();
XmlDeclaration dec = doc.CreateXmlDeclaration("1.0", null, null);
doc.AppendChild(dec);
XmlElement root = doc.CreateElement("PAVIGs");
doc.AppendChild(root);
List <string> returnMessages = new List <string>();
string returnMessage;
int i = 0;
//Split the CSV into lines
string[] lines = fileToText.Split(new string[] { "\n", "\r\n" }, StringSplitOptions.RemoveEmptyEntries);
//loop through CSV lines
foreach (string line in lines)
{
i++;
if (i > 1) //ignore first line with titles
{
string[] cells = line.Split(';');
//extract the data items from the file
string CountryCode = cells[0]; //Required: (eg.Preferred)
string PolicyCountryStatusDescription = cells[1]; //Required: (eg.DL)
string enabledFlag = cells[2]; //(True / False)(1, 0)
string enabledDate = cells[3]; //(YYYY / MM / DD)
string expiryDate = cells[4]; //(YYYY / MM / DD)
string travelDateValidFrom = cells[5]; //(YYYY / MM / DD)
string travelDateValidTo = cells[6]; //(YYYY / MM / DD)
//Build the XML Element for items
XmlElement xmlPAVItem = doc.CreateElement("PAVIG");
XmlElement xmlCountryCode = doc.CreateElement("CountryCode");
xmlCountryCode.InnerText = CountryCode;
xmlPAVItem.AppendChild(xmlCountryCode);
XmlElement xmlPolicyCountryStatusDescription = doc.CreateElement("PolicyCountryStatusDescription");
xmlPolicyCountryStatusDescription.InnerText = PolicyCountryStatusDescription;
xmlPAVItem.AppendChild(xmlPolicyCountryStatusDescription);
XmlElement xmlEnabledDate = doc.CreateElement("EnabledDate");
xmlEnabledDate.InnerText = enabledDate;
xmlPAVItem.AppendChild(xmlEnabledDate);
XmlElement xmlExpiryDate = doc.CreateElement("ExpiryDate");
xmlExpiryDate.InnerText = expiryDate;
xmlPAVItem.AppendChild(xmlExpiryDate);
XmlElement xmlTravelDateValidFrom = doc.CreateElement("TravelDateValidFrom");
xmlTravelDateValidFrom.InnerText = travelDateValidFrom;
xmlPAVItem.AppendChild(xmlTravelDateValidFrom);
XmlElement xmlTravelDateValidTo = doc.CreateElement("TravelDateValidTo");
xmlTravelDateValidTo.InnerText = travelDateValidTo;
xmlPAVItem.AppendChild(xmlTravelDateValidTo);
//Validate data
//Validate data country status
if (string.IsNullOrEmpty(PolicyCountryStatusDescription) == true)
{
returnMessage = "Row " + i + ": Policy Country Status Description is missing. Please provide a valid Country Status";
if (!returnMessages.Contains(returnMessage))
{
returnMessages.Add(returnMessage);
}
}
PolicyCountryStatusRepository policyCountryStatusRepository = new PolicyCountryStatusRepository();
PolicyCountryStatus policyCountry = policyCountryStatusRepository.GetPolicyCountryStatusByDescription(PolicyCountryStatusDescription);
if (policyCountry == null)
{
returnMessage = "Row " + i + ": Country Status Description is invalid. Please provide a valid Country Status";
if (!returnMessages.Contains(returnMessage))
{
returnMessages.Add(returnMessage);
}
}
else
{
PolicyCountryStatusDescription = policyCountry.PolicyCountryStatusId.ToString();
}
//Validate data country code
if (string.IsNullOrEmpty(CountryCode) == true)
{
returnMessage = "Row " + i + ": Country Code is missing. Please provide a valid Country Code";
if (!returnMessages.Contains(returnMessage))
{
returnMessages.Add(returnMessage);
}
}
else
{
CountryRepository countryRepository = new CountryRepository();
Country country = countryRepository.GetCountry(CountryCode);
if (country == null)
{
returnMessage = "Row " + i + ": Country Code is invalid. Please provide a valid Country Code";
if (!returnMessages.Contains(returnMessage))
{
returnMessages.Add(returnMessage);
}
}
}
enabledFlag = enabledFlag.ToUpper();
if (enabledFlag != "TRUE" && enabledFlag != "FALSE" && enabledFlag != "1" && enabledFlag != "0" && enabledFlag != "")
{
returnMessage = "Row " + i + ": EnabledFlag value is not valid. Please provide a valid Enabled Flag";
if (!returnMessages.Contains(returnMessage))
{
returnMessages.Add(returnMessage);
}
}
if (enabledFlag == "TRUE" || string.IsNullOrEmpty(enabledFlag))
{
enabledFlag = "1";
}
if (enabledFlag == "FALSE")
{
enabledFlag = "0";
}
XmlElement xmlEnabledFlag = doc.CreateElement("EnabledFlag");
xmlEnabledFlag.InnerText = enabledFlag;
xmlPAVItem.AppendChild(xmlEnabledFlag);
if (enabledDate != "" && enabledDate != "NULL")
{
DateTime enabledDateDT;
if (DateTime.TryParseExact(enabledDate, "yyyy/MM/dd", CultureInfo.InvariantCulture, DateTimeStyles.None, out enabledDateDT) == false)
{
returnMessage = "Row " + i + ": EnabledDate must be in the format YYYY/MM/DD. Please provide a valid date format";
if (!returnMessages.Contains(returnMessage))
{
returnMessages.Add(returnMessage);
}
}
}
if (expiryDate != "" && expiryDate != "NULL")
{
DateTime enabledDateDT;
if (DateTime.TryParseExact(expiryDate, "yyyy/MM/dd", CultureInfo.InvariantCulture, DateTimeStyles.None, out enabledDateDT) == false)
{
returnMessage = "Row " + i + ": ExpiryDate must be in the format YYYY/MM/DD. Please provide a valid date format";
if (!returnMessages.Contains(returnMessage))
{
returnMessages.Add(returnMessage);
}
}
}
if (travelDateValidFrom != "" && travelDateValidFrom != "NULL")
{
DateTime enabledDateDT;
if (DateTime.TryParseExact(travelDateValidFrom, "yyyy/MM/dd", CultureInfo.InvariantCulture, DateTimeStyles.None, out enabledDateDT) == false)
{
returnMessage = "Row " + i + ": TravelDateFrom must be in the format YYYY/MM/DD. Please provide a valid date format";
if (!returnMessages.Contains(returnMessage))
{
returnMessages.Add(returnMessage);
}
}
}
if (travelDateValidTo != "" && travelDateValidTo != "NULL")
{
DateTime enabledDateDT;
if (DateTime.TryParseExact(travelDateValidTo, "yyyy/MM/dd", CultureInfo.InvariantCulture, DateTimeStyles.None, out enabledDateDT) == false)
{
returnMessage = "Row " + i + ": TravelDateTo must be in the format YYYY/MM/DD. Please provide a valid date format";
if (!returnMessages.Contains(returnMessage))
{
returnMessages.Add(returnMessage);
}
}
}
//Attach the XML Element for an item to the Document
root.AppendChild(xmlPAVItem);
}
}
if (i == 0)
{
returnMessage = "There is no data in the file";
returnMessages.Add(returnMessage);
}
PolicyCountryImportStep2VM preImportCheckResult = new PolicyCountryImportStep2VM();
preImportCheckResult.ReturnMessages = returnMessages;
if (returnMessages.Count != 0)
{
//preImportCheckResult.FileBytes = array;
preImportCheckResult.IsValidData = false;
}
else
{
//DB Check
string adminUserGuid = HttpContext.Current.User.Identity.Name.Split(new[] { '|' })[0];
var output = (
from n in dbHierarchyDC.spDesktopDataAdmin_UpdatePolicyCountryGroupItemCount_v1(
policyGroupId,
System.Xml.Linq.XElement.Parse(doc.OuterXml),
adminUserGuid
)
select n).ToList();
foreach (spDesktopDataAdmin_UpdatePolicyCountryGroupItemCount_v1Result message in output)
{
returnMessages.Add(message.MessageText.ToString());
}
preImportCheckResult.FileBytes = array;
preImportCheckResult.IsValidData = true;
}
return(preImportCheckResult);
}
//adds data from linked tables
public void EditForDisplay(PolicyRouting policyRouting)
{
if (policyRouting.FromCityCode != null)
{
CityRepository cityRepository = new CityRepository();
City city = new City();
city = cityRepository.GetCity(policyRouting.FromCityCode);
policyRouting.FromName = city.Name;
}
if (policyRouting.FromCountryCode != null)
{
CountryRepository countryRepository = new CountryRepository();
Country country = new Country();
country = countryRepository.GetCountry(policyRouting.FromCountryCode);
policyRouting.FromName = country.CountryName;
}
if (policyRouting.FromGlobalSubRegionCode != null)
{
HierarchyRepository hierarchyRepository = new HierarchyRepository();
GlobalSubRegion globalSubRegion = new GlobalSubRegion();
globalSubRegion = hierarchyRepository.GetGlobalSubRegion(policyRouting.FromGlobalSubRegionCode);
policyRouting.FromName = globalSubRegion.GlobalSubRegionName;
}
if (policyRouting.FromGlobalRegionCode != null)
{
HierarchyRepository hierarchyRepository = new HierarchyRepository();
GlobalRegion globalRegion = new GlobalRegion();
globalRegion = hierarchyRepository.GetGlobalRegion(policyRouting.FromGlobalRegionCode);
policyRouting.FromName = globalRegion.GlobalRegionName;
}
if (policyRouting.FromGlobalFlag)
{
policyRouting.FromName = "Global";
}
if (policyRouting.ToCityCode != null)
{
CityRepository cityRepository = new CityRepository();
City city = new City();
city = cityRepository.GetCity(policyRouting.ToCityCode);
policyRouting.ToName = city.Name;
}
if (policyRouting.ToCountryCode != null)
{
CountryRepository countryRepository = new CountryRepository();
Country country = new Country();
country = countryRepository.GetCountry(policyRouting.ToCountryCode);
policyRouting.ToName = country.CountryName;
}
if (policyRouting.ToGlobalSubRegionCode != null)
{
HierarchyRepository hierarchyRepository = new HierarchyRepository();
GlobalSubRegion globalSubRegion = new GlobalSubRegion();
globalSubRegion = hierarchyRepository.GetGlobalSubRegion(policyRouting.ToGlobalSubRegionCode);
policyRouting.ToName = globalSubRegion.GlobalSubRegionName;
}
if (policyRouting.ToGlobalRegionCode != null)
{
HierarchyRepository hierarchyRepository = new HierarchyRepository();
GlobalRegion globalRegion = new GlobalRegion();
globalRegion = hierarchyRepository.GetGlobalRegion(policyRouting.ToGlobalRegionCode);
policyRouting.ToName = globalRegion.GlobalRegionName;
}
if (policyRouting.ToGlobalFlag)
{
policyRouting.ToName = "Global";
}
}
public ClientTopUnitImportStep2VM PreImportCheck(HttpPostedFileBase file, string clientTopUnitGuid)
{
//convert file to string so that we can parse
int length = file.ContentLength;
byte[] tempFile = new byte[length];
file.InputStream.Read(tempFile, 0, length);
byte[] array = tempFile.ToArray();
System.Text.UTF8Encoding enc = new System.Text.UTF8Encoding();
string fileToText = fileToText = enc.GetString(array);
// Create the xml document container, this will be used to store the data after the checks
XmlDocument doc = new XmlDocument();
XmlDeclaration dec = doc.CreateXmlDeclaration("1.0", null, null);
doc.AppendChild(dec);
XmlElement root = doc.CreateElement("PHCRIGs");
doc.AppendChild(root);
List <string> returnMessages = new List <string>();
string returnMessage;
int i = 0;
//Split the CSV into lines
string[] lines = fileToText.Split(new string[] { "\n", "\r\n" }, StringSplitOptions.RemoveEmptyEntries);
//loop through CSV lines
foreach (string line in lines)
{
i++;
if (i > 1) //ignore first line with titles
{
Regex csvParser = new Regex(",(?=(?:[^\"]*\"[^\"]*\")*(?![^\"]*\"))");
String[] cells = csvParser.Split(line);
//extract the data items from the file
string addressLocationName = CWTStringHelpers.UnescapeQuotes(cells[0]); //Required
string firstAddressLine = CWTStringHelpers.UnescapeQuotes(cells[1]); //Required
string secondAddressLine = CWTStringHelpers.UnescapeQuotes(cells[2]) ?? "";
string cityName = CWTStringHelpers.UnescapeQuotes(cells[3]); //Required
string postalCode = CWTStringHelpers.UnescapeQuotes(cells[4]); //Required
string countryCode = cells[5]; //Required (e.g.US)
string stateProvinceName = cells[6]; //Required if CountryCode is in the StateProvince table (should contain valid StateProvinceCode)
string latitudeDecimal = cells[7]; //Required
string longitudeDecimal = cells[8]; //Required
string ranking = cells[9] ?? "";
//Build the XML Element for items
XmlElement xmlClientTopUnitLocationItem = doc.CreateElement("PHCRIG");
XmlElement xmlAddressLocationName = doc.CreateElement("AddressLocationName");
xmlAddressLocationName.InnerText = addressLocationName;
xmlClientTopUnitLocationItem.AppendChild(xmlAddressLocationName);
XmlElement xmlFirstAddressLine = doc.CreateElement("FirstAddressLine");
xmlFirstAddressLine.InnerText = firstAddressLine;
xmlClientTopUnitLocationItem.AppendChild(xmlFirstAddressLine);
XmlElement xmlSecondAddressLine = doc.CreateElement("SecondAddressLine");
xmlSecondAddressLine.InnerText = secondAddressLine;
xmlClientTopUnitLocationItem.AppendChild(xmlSecondAddressLine);
XmlElement xmlCityName = doc.CreateElement("CityName");
xmlCityName.InnerText = cityName;
xmlClientTopUnitLocationItem.AppendChild(xmlCityName);
XmlElement xmlPostalCode = doc.CreateElement("PostalCode");
xmlPostalCode.InnerText = postalCode;
xmlClientTopUnitLocationItem.AppendChild(xmlPostalCode);
XmlElement xmlCountryCode = doc.CreateElement("CountryCode");
xmlCountryCode.InnerText = countryCode;
xmlClientTopUnitLocationItem.AppendChild(xmlCountryCode);
XmlElement xmlStateProvinceName = doc.CreateElement("StateProvinceName");
xmlStateProvinceName.InnerText = stateProvinceName;
xmlClientTopUnitLocationItem.AppendChild(xmlStateProvinceName);
XmlElement xmlLatitudeDecimal = doc.CreateElement("LatitudeDecimal");
xmlLatitudeDecimal.InnerText = latitudeDecimal;
xmlClientTopUnitLocationItem.AppendChild(xmlLatitudeDecimal);
XmlElement xmlLongitudeDecimal = doc.CreateElement("LongitudeDecimal");
xmlLongitudeDecimal.InnerText = longitudeDecimal;
xmlClientTopUnitLocationItem.AppendChild(xmlLongitudeDecimal);
//Validate data
if (string.IsNullOrEmpty(addressLocationName) == true)
{
returnMessage = "Row " + i + ": AddressLocationName is missing. Please provide a Client Location Name";
if (!returnMessages.Contains(returnMessage))
{
returnMessages.Add(returnMessage);
}
}
if (string.IsNullOrEmpty(firstAddressLine) == true)
{
returnMessage = "Row " + i + ": FirstAddressLine is missing. Please provide a First Address Line";
if (!returnMessages.Contains(returnMessage))
{
returnMessages.Add(returnMessage);
}
}
if (string.IsNullOrEmpty(cityName) == true)
{
returnMessage = "Row " + i + ": CityName is missing. Please provide a City Name";
if (!returnMessages.Contains(returnMessage))
{
returnMessages.Add(returnMessage);
}
}
if (string.IsNullOrEmpty(postalCode) == true)
{
returnMessage = "Row " + i + ": PostalCode is missing. Please provide a Postal Code";
if (!returnMessages.Contains(returnMessage))
{
returnMessages.Add(returnMessage);
}
}
if (string.IsNullOrEmpty(countryCode) == true)
{
returnMessage = "Row " + i + ": CountryCode is missing. Please provide a valid Country Code";
if (!returnMessages.Contains(returnMessage))
{
returnMessages.Add(returnMessage);
}
}
if (string.IsNullOrEmpty(latitudeDecimal) == true)
{
returnMessage = "Row " + i + ": Latitude is missing. Please provide a valid Latitude";
if (!returnMessages.Contains(returnMessage))
{
returnMessages.Add(returnMessage);
}
}
if (string.IsNullOrEmpty(longitudeDecimal) == true)
{
returnMessage = "Row " + i + ": Longitude is missing. Please provide a valid Longitude";
if (!returnMessages.Contains(returnMessage))
{
returnMessages.Add(returnMessage);
}
}
//AddressLocationName is freeform text and will allow up to 150 alphanumeric and allowable special characters.
if (addressLocationName.Length > 150)
{
returnMessage = "Row " + i + ": AddressLocationName contains more than 150 characters. Please provide a valid Address Location Name";
if (!returnMessages.Contains(returnMessage))
{
returnMessages.Add(returnMessage);
}
}
//AddressLocationName allowable special characters are: space, dash, underscore, right and left parentheses, period, apostrophe (O’Reily), ampersand and accented characters.
string addressLocationNamePattern = @"^[À-ÿ\w\s-()\.\&\'\’_]+$";
if (!Regex.IsMatch(addressLocationName, addressLocationNamePattern, RegexOptions.IgnoreCase))
{
returnMessage = "Row " + i + ": AddressLocationName contains invalid special characters. Please provide a Address Location Name";
if (!returnMessages.Contains(returnMessage))
{
returnMessages.Add(returnMessage);
}
}
//FirstAddressLine is freeform text and will allow up to 150 alphanumeric and allowable special characters.
if (firstAddressLine.Length > 150)
{
returnMessage = "Row " + i + ": FirstAddressLine contains more than 150 characters. Please provide a valid First Address Line";
if (!returnMessages.Contains(returnMessage))
{
returnMessages.Add(returnMessage);
}
}
//FirstAddressLine allowable special characters are: space, dash, underscore, right and left parentheses, period, apostrophe (O’Reily), ampersand and accented characters.
string firstAddressLinePattern = @"^([À-ÿ\w\s-()\*\.\&\'\’_\,\:\,\°\#\\\/]+)$";
if (!Regex.IsMatch(firstAddressLine, firstAddressLinePattern, RegexOptions.IgnoreCase))
{
returnMessage = "Row " + i + ": FirstAddressLine contains invalid special characters. Please provide a valid First Address Line";
if (!returnMessages.Contains(returnMessage))
{
returnMessages.Add(returnMessage);
}
}
//SecondAddressLine is freeform text and will allow up to 150 alphanumeric and allowable special characters.
if (secondAddressLine.Length > 150)
{
returnMessage = "Row " + i + ": SecondAddressLine contains more than 150 characters. Please provide a valid Second Address Line";
if (!returnMessages.Contains(returnMessage))
{
returnMessages.Add(returnMessage);
}
}
//SecondAddressLine allowable special characters are: space, dash, underscore, right and left parentheses, period, apostrophe (O’Reily), ampersand and accented characters.
string secondAddressLinePattern = @"^([À-ÿ\w\s-()\*\.\&\'\’_\,\:\,\°\#\\\/]+)$";
if (!string.IsNullOrEmpty(secondAddressLine) && !Regex.IsMatch(secondAddressLine, secondAddressLinePattern, RegexOptions.IgnoreCase))
{
returnMessage = "Row " + i + ": SecondAddressLine contains invalid special characters. Please provide a valid Second Address Line";
if (!returnMessages.Contains(returnMessage))
{
returnMessages.Add(returnMessage);
}
}
//CityName is freeform text and will allow up to 40 alphanumeric and allowable special characters.
if (cityName.Length > 40)
{
returnMessage = "Row " + i + ": CityName contains more than 40 characters. Please provide a valid City Name";
if (!returnMessages.Contains(returnMessage))
{
returnMessages.Add(returnMessage);
}
}
//CityName allowable special characters are: space, dash, underscore, right and left parentheses, period, apostrophe (O’Reily), ampersand and accented characters.
string cityNamePattern = @"^[À-ÿ\w\s-()\.\&\'\’_]+$";
if (!Regex.IsMatch(cityName, cityNamePattern, RegexOptions.IgnoreCase))
{
returnMessage = "Row " + i + ": CityName contains invalid special characters. Please provide a valid City Name";
if (!returnMessages.Contains(returnMessage))
{
returnMessages.Add(returnMessage);
}
}
//PostalCode is freeform text and will allow up to 30 alphanumeric and allowable special characters.
if (postalCode.Length > 30)
{
returnMessage = "Row " + i + ": CityName contains more than 30 characters. Please provide a valid Postal Code";
if (!returnMessages.Contains(returnMessage))
{
returnMessages.Add(returnMessage);
}
}
//PostalCode allowable special characters are: space, dash, underscore, right and left parentheses, period, apostrophe (O’Reily), ampersand and accented characters.
string postalCodePattern = @"^[À-ÿ\w\s-()\.\&\'\’_]+$";
if (!Regex.IsMatch(postalCode, postalCodePattern, RegexOptions.IgnoreCase))
{
returnMessage = "Row " + i + ": CityName contains invalid special characters. Please provide a valid Postal Code";
if (!returnMessages.Contains(returnMessage))
{
returnMessages.Add(returnMessage);
}
}
//CountryCode must contain any one value from the CountryCode column of the Country table
CountryRepository countryRepository = new CountryRepository();
Country country = countryRepository.GetCountry(countryCode);
if (country == null)
{
returnMessage = "Row " + i + ": CountryCode " + countryCode + " is invalid. Please provide a valid Country Code";
if (!returnMessages.Contains(returnMessage))
{
returnMessages.Add(returnMessage);
}
}
//Where the CountryCode value in the row exists in the StateProvince table then the StateProvinceName column
//in the data import file must contain a StateProvinceCode value for the relevant CountryCode.
StateProvinceRepository stateProvinceRepository = new StateProvinceRepository();
List <StateProvince> stateProvinces = stateProvinceRepository.GetStateProvincesByCountryCode(countryCode);
StateProvince stateProvince = stateProvinceRepository.GetStateProvinceByCountry(countryCode, stateProvinceName);
if ((stateProvinces.Count == 0 && !string.IsNullOrEmpty(stateProvinceName)) || (stateProvinces.Count > 0 && stateProvince == null))
{
returnMessage = "Row " + i + ": StateProvinceName " + stateProvinceName + " is invalid. Please provide a valid State/Province Code";
if (!returnMessages.Contains(returnMessage))
{
returnMessages.Add(returnMessage);
}
}
//LatitudeDecimal must be numerical
float validLatitudeDecimal;
if (!float.TryParse(latitudeDecimal, out validLatitudeDecimal))
{
returnMessage = "Row " + i + ": Latitude must be numerical. Please provide a valid Latitude";
if (!returnMessages.Contains(returnMessage))
{
returnMessages.Add(returnMessage);
}
}
//LatitudeDecimal positive and negative numerical values of up to two places prior to the decimal and seven places following the decimal
//E.g -999.9999999 and 999.9999999
string latitudeDecimalPattern = @"^[-+]?(\d{1,3}\.\d{1,7}|\d{1,3})?$";
if (!Regex.IsMatch(latitudeDecimal, latitudeDecimalPattern, RegexOptions.IgnoreCase))
{
returnMessage = "Row " + i + ": Latitude must be between -999.9999999 and 999.9999999. Please provide a valid Latitude";
if (!returnMessages.Contains(returnMessage))
{
returnMessages.Add(returnMessage);
}
}
//LongitudeDecimal must be numerical
float validLongitudeDecimal;
if (!float.TryParse(longitudeDecimal, out validLongitudeDecimal))
{
returnMessage = "Row " + i + ": Longitude must be numerical. Please provide a valid Latitude";
if (!returnMessages.Contains(returnMessage))
{
returnMessages.Add(returnMessage);
}
}
//LongitudeDecimal positive and negative numerical values of up to three places prior to the decimal and seven places following the decimal
//E.g -999.9999999 and 999.9999999
string longitudeDecimalPattern = @"^[-+]?(\d{1,3}\.\d{1,7}|\d{1,3})?$";
if (!Regex.IsMatch(longitudeDecimal, longitudeDecimalPattern, RegexOptions.IgnoreCase))
{
returnMessage = "Row " + i + ": Longitude must be between -999.9999999 and 999.9999999. Please provide a valid Longitude";
if (!returnMessages.Contains(returnMessage))
{
returnMessages.Add(returnMessage);
}
}
//Ranking may be empty or NULL or contain an integer from 1 to 9 inclusive
string rankingPattern = @"\d{1,9}?";
if (ranking.ToLower() != "null" && !string.IsNullOrEmpty(ranking) && !Regex.IsMatch(ranking, rankingPattern, RegexOptions.IgnoreCase))
{
returnMessage = "Row " + i + ": Ranking is from 1 to 9. Please provide a valid Rank";
if (!returnMessages.Contains(returnMessage))
{
returnMessages.Add(returnMessage);
}
}
int validRanking;
bool isRankingNumerical = Int32.TryParse(ranking, out validRanking);
if (isRankingNumerical && ranking.ToLower() != "null" && !string.IsNullOrEmpty(ranking))
{
XmlElement xmlRanking = doc.CreateElement("Ranking");
xmlRanking.InnerText = ranking;
xmlClientTopUnitLocationItem.AppendChild(xmlRanking);
}
//Attach the XML Element for an item to the Document
root.AppendChild(xmlClientTopUnitLocationItem);
}
}
if (i == 0)
{
returnMessage = "There is no data in the file";
returnMessages.Add(returnMessage);
}
ClientTopUnitImportStep2VM preImportCheckResult = new ClientTopUnitImportStep2VM();
preImportCheckResult.ReturnMessages = returnMessages;
if (returnMessages.Count != 0)
{
preImportCheckResult.IsValidData = false;
}
else
{
//DB Check
string adminUserGuid = HttpContext.Current.User.Identity.Name.Split(new[] { '|' })[0];
var output = (
from n in db.spDesktopDataAdmin_UpdateClientTopUnitClientLocationCount_v1(
clientTopUnitGuid,
System.Xml.Linq.XElement.Parse(doc.OuterXml),
adminUserGuid
)
select n).ToList();
foreach (spDesktopDataAdmin_UpdateClientTopUnitClientLocationCount_v1Result message in output)
{
returnMessages.Add(message.MessageText.ToString());
}
preImportCheckResult.FileBytes = array;
preImportCheckResult.IsValidData = true;
}
return(preImportCheckResult);
}
