/// <summary>
/// First creates an organization and then imports an organization template that contains 2 organizations of which 1 has the same organization name with the previously created organization.
/// Check for duplicate organziation names is made during import
/// </summary>
public static void ImportTemplateWithAnExistingOrganization()
{
var organizationName = "SiEBEN";
NewOrganizationPage.CreateOrganization().WithOrganizationName(organizationName).Create();
if (!NewOrganizationPage.IsOrganizationSavedSuccessfully)
{
return;
}
FirstOrganization.SetFieldValue(OrganizationFields.OrganizationName, organizationName);
ImportPage.ImportFile()
.Containing(ImportFileType.Organizations)
.FromPath(ImportFilePath)
.WithFileName("Organizations16.xls")
.CheckingForDuplicate(ImportField.OrganizationName).Submit();
if (!ImportPage.IsImportWithDuplicatesMessageShown)
{
return;
}
SecondOrganization.SetFieldValue(OrganizationFields.OrganizationName, "SiEBEN");
ThirdOrganization.SetFieldValue(OrganizationFields.OrganizationName, "InEdu");
}
public string ImportPage(HttpListenerContext ctx)
{
List <BankAccount> accounts = model.GetBankAccounts();
ImportPage importPage = new ImportPage {
Accounts = accounts
};
return(importPage.TransformText());
// return GetResource("html.Import.html");
}
/// <summary>
/// Import a file that contains 1 organization which has a contact that does not exist within contact list, as its primary contact
/// </summary>
public static void ImportOrganizationWithPrimaryContactThatDoesNotExist()
{
ImportPage.ImportFile().Containing(ImportFileType.Organizations).FromPath(ImportFilePath).WithFileName("Organizations15.xls").Submit();
if (!ImportPage.IsImportSuccessMessageShown)
{
return;
}
FirstOrganization.SetFieldValue(OrganizationFields.OrganizationName, "SiEBEN");
}
/// <summary>
/// Import a file that contains 1 organization that has value for phone field but no value for organization name
/// </summary>
public static void ImportOrganizationWithoutOrganizationName()
{
ImportPage.ImportFile().Containing(ImportFileType.Organizations).FromPath(ImportFilePath).WithFileName("Organizations4.xls").Submit();
if (!ImportPage.IsImportSuccessMessageShown)
{
return;
}
FirstOrganization.SetFieldValue(OrganizationFields.Phone, "1234567890");
}
static NavigationManager()
{
//SINGLE PAGES
accounts = new AccountPage();
//groups = new GroupsPage();
import = new ImportPage();
//reconcile = new ReconcilePage();
rules = new RulesPage();
modifyRules = new ModifyRules();
}
/// <summary>
/// Import a file that contains 1 organization but the file misses its organization name field column
/// </summary>
public static void ImportTemplateWithoutOrganizationNameColumn()
{
ImportPage.ImportFile().Containing(ImportFileType.Organizations).FromPath(ImportFilePath).WithFileName("Organizations11.xls").Submit();
if (!ImportPage.IsImportSuccessMessageShown)
{
return;
}
FirstOrganization.SetFieldValue(OrganizationFields.Email, "*****@*****.**");
FirstOrganization.SetFieldValue(OrganizationFields.Website, "http://www.sieben.gr");
}
/// <summary>
/// Import a file that contains 1 organization but the file contains more field columns than normal
/// </summary>
public static void ImportTemplateWithMoreColumns()
{
ImportPage.ImportFile().Containing(ImportFileType.Organizations).FromPath(ImportFilePath).WithFileName("Organizations9.xls").Submit();
if (!ImportPage.IsImportSuccessMessageShown)
{
return;
}
FirstOrganization.SetFieldValue(OrganizationFields.OrganizationName, "SiEBEN");
FirstOrganization.SetFieldValue(OrganizationFields.Phone, "2130179000");
}
/// <summary>
/// Import a file that contains 1 organization which has a contact that is linked to another organization, as its primary contact
/// </summary>
public static void ImportOrganizationWithPrimaryContactThatBelongsToAnotherOrganization()
{
ImportPage.ImportFile().Containing(ImportFileType.Organizations).FromPath(ImportFilePath).WithFileName("Organizations14.xls").Submit();
if (!ImportPage.IsImportSuccessMessageShown)
{
return;
}
FirstOrganization.SetFieldValue(OrganizationFields.OrganizationName, "SiEBEN");
FirstOrganization.SetFieldValue(OrganizationFields.PrimaryContact, "Carja Ramona");
}
/// <summary>
/// Import a file that contains 1 organization with organization name and phone values that exceed 50 characters
/// </summary>
public static void ImportOrganizationWithOverflowValues()
{
ImportPage.ImportFile().Containing(ImportFileType.Organizations).FromPath(ImportFilePath).WithFileName("Organizations6.xls").Submit();
if (!ImportPage.IsImportSuccessMessageShown)
{
return;
}
FirstOrganization.SetFieldValue(OrganizationFields.OrganizationName, "qwertyuiopasdfghjklzxcvbnmqwertyuiopasdfghjklzxcvbnmqwertyuiopasdfghjklzxcvbnm");
FirstOrganization.SetFieldValue(OrganizationFields.Phone, "qwertyuiopasdfghjklzxcvbnmqwertyuiopasdfghjklzxcvbnmqwertyuiopasdfghjklzxcvbnm");
}
/// <summary>
/// Import a file that contains 1 organization with organization name and phone values that are nonsense
/// </summary>
public static void ImportOrganizationWithNonsenseValues()
{
ImportPage.ImportFile().Containing(ImportFileType.Organizations).FromPath(ImportFilePath).WithFileName("Organizations5.xls").Submit();
if (!ImportPage.IsImportSuccessMessageShown)
{
return;
}
FirstOrganization.SetFieldValue(OrganizationFields.OrganizationName, "!@#qweQWE123");
FirstOrganization.SetFieldValue(OrganizationFields.Phone, "!@#qweQWE123");
}
/// <summary>
/// Import a file that contains 1 organization but the file has its field columns in random order
/// </summary>
public static void ImportTemplateWithColumnsInRandomOrder()
{
ImportPage.ImportFile().Containing(ImportFileType.Organizations).FromPath(ImportFilePath).WithFileName("Organizations13.xls").Submit();
if (!ImportPage.IsImportSuccessMessageShown)
{
return;
}
FirstOrganization.SetFieldValue(OrganizationFields.OrganizationName, "SiEBEN");
FirstOrganization.SetFieldValue(OrganizationFields.Phone, "2130179000");
FirstOrganization.SetFieldValue(OrganizationFields.Email, "*****@*****.**");
FirstOrganization.SetFieldValue(OrganizationFields.Website, "http://www.sieben.gr");
}
/// <summary>
/// Import an organization template that contains 3 organization that have void lines in between them
/// </summary>
public static void ImportTemplateWithVoidLinesBetweenOrganizations()
{
ImportPage.ImportFile()
.Containing(ImportFileType.Organizations)
.FromPath(ImportFilePath)
.WithFileName("Organizations18.xls")
.Submit();
if (!ImportPage.IsImportSuccessMessageShown)
{
return;
}
FirstOrganization.SetFieldValue(OrganizationFields.OrganizationName, "SiEBEN");
SecondOrganization.SetFieldValue(OrganizationFields.OrganizationName, "InEdu");
ThirdOrganization.SetFieldValue(OrganizationFields.OrganizationName, "Microsoft");
}
/// <summary>
/// Import an organization template that contains 2 organizations with the same organization name. During import, duplicate organization name checkbox is checked
/// </summary>
public static void ImportTemplateWithTwinOrganizations()
{
ImportPage.ImportFile()
.Containing(ImportFileType.Organizations)
.FromPath(ImportFilePath)
.WithFileName("Organizations17.xls")
.CheckingForDuplicate(ImportField.OrganizationName).Submit();
if (!ImportPage.IsImportWithDuplicatesMessageShown)
{
return;
}
FirstOrganization.SetFieldValue(OrganizationFields.OrganizationName, "SiEBEN");
SecondOrganization.SetFieldValue(OrganizationFields.OrganizationName, "InEdu");
ThirdOrganization.SetFieldValue(OrganizationFields.OrganizationName, "SiEBEN");
}
/// <summary>
/// Import an organization template that contains 2 organization of whom, one has invlaid value for a combo field
/// </summary>
public static void ImportTemplateOrganizationWithInvalidComboValues()
{
ImportPage.ImportFile()
.Containing(ImportFileType.Organizations)
.FromPath(ImportFilePath)
.WithFileName("Organizations19.xls")
.Submit();
if (!ImportPage.IsImportSuccessMessageShown)
{
return;
}
FirstOrganization.SetFieldValue(OrganizationFields.OrganizationName, "SiEBEN");
FirstOrganization.SetFieldValue(OrganizationFields.BillingCountry, "Ελλάδα");
SecondOrganization.SetFieldValue(OrganizationFields.OrganizationName, "InEdu");
SecondOrganization.SetFieldValue(OrganizationFields.BillingCountry, "Greece");
}
/// <summary>
/// Import a file that contains 1 organization that has values for all its fields
/// </summary>
public static void ImportOrganizationWithAllValues()
{
ImportPage.ImportFile().Containing(ImportFileType.Organizations).FromPath(ImportFilePath).WithFileName("Organizations2.xls").Submit();
if (!ImportPage.IsImportSuccessMessageShown)
{
return;
}
FirstOrganization.SetFieldValue(OrganizationFields.OrganizationName, "SiEBEN");
FirstOrganization.SetFieldValue(OrganizationFields.Phone, "2130179000");
FirstOrganization.SetFieldValue(OrganizationFields.Email, "*****@*****.**");
FirstOrganization.SetFieldValue(OrganizationFields.Fax, "2130179001");
FirstOrganization.SetFieldValue(OrganizationFields.Website, "http://www.sieben.gr");
FirstOrganization.SetFieldValue(OrganizationFields.AllowSms, "False");
FirstOrganization.SetFieldValue(OrganizationFields.AllowPhones, "False");
FirstOrganization.SetFieldValue(OrganizationFields.AllowEmails, "True");
FirstOrganization.SetFieldValue(OrganizationFields.Industry, "Consulting");
FirstOrganization.SetFieldValue(OrganizationFields.OrganizationType, "Consultant");
FirstOrganization.SetFieldValue(OrganizationFields.Profession, "Informatics");
FirstOrganization.SetFieldValue(OrganizationFields.Comments, "Sieben on the rocks");
FirstOrganization.SetFieldValue(OrganizationFields.BillingStreet, "Aristomenous 3");
FirstOrganization.SetFieldValue(OrganizationFields.BillingCity, "Gerakas");
FirstOrganization.SetFieldValue(OrganizationFields.BillingState, "Attica");
FirstOrganization.SetFieldValue(OrganizationFields.BillingPostalCode, "10442");
FirstOrganization.SetFieldValue(OrganizationFields.BillingCountry, "Greece");
FirstOrganization.SetFieldValue(OrganizationFields.ShippingStreet, "Aristomenous 2");
FirstOrganization.SetFieldValue(OrganizationFields.ShippingCity, "Pallini");
FirstOrganization.SetFieldValue(OrganizationFields.ShippingState, "Thessalonica");
FirstOrganization.SetFieldValue(OrganizationFields.ShippingPostalCode, "10443");
FirstOrganization.SetFieldValue(OrganizationFields.ShippingCountry, "Greece");
FirstOrganization.SetFieldValue(OrganizationFields.OtherStreet, "Armigado 2");
FirstOrganization.SetFieldValue(OrganizationFields.OtherCity, "Valencia");
FirstOrganization.SetFieldValue(OrganizationFields.OtherState, "Deportivo");
FirstOrganization.SetFieldValue(OrganizationFields.OtherPostalCode, "15016");
FirstOrganization.SetFieldValue(OrganizationFields.OtherCountry, "Spain");
}
//GO TO IMPORT PAGE
public static void GoToImport()
{
import = new ImportPage();
Frame.Navigate(import);
}
public static IEnumerable <XVar> IterateSheet(ExcelPackage xlsPack, XVar fieldsData, bool useFirstLine)
{
XVar previewData = null;
foreach (var worksheet in xlsPack.Workbook.Worksheets)
{
for (int row = (useFirstLine? 1 : 2); row <= worksheet.Dimension.End.Row; ++row)
{
XVar arr = XVar.Array();
for (int i = 0; i < worksheet.Dimension.End.Column; i++)
{
if (!fieldsData.KeyExists(i))
{
continue;
}
var cell = worksheet.Cells[row, i + 1];
XVar val = null;
if (cell != null && cell.Value != null)
{
bool dateCorrectlyExtracted = false;
if (cell.Value is DateTime) // sometimes date stored in a DateTime
{
val = ((DateTime)cell.Value).ToString("yyyy-MM-dd H:mm:ss");
dateCorrectlyExtracted = true;
}
else if (Regex.IsMatch(cell.Style.Numberformat.Format, ("([ymdHis])"))) // sometimes date stored in a weird number
{
cell.Style.Numberformat.Format = "yyyy-MM-dd H:mm:ss";
val = cell.Text;
dateCorrectlyExtracted = true;
}
else if (cell.IsRichText)
{
val = cell.RichText.Text;
}
else
{
val = new XVar(cell.Value.ToString());
}
// the following code block looks suspicious (!)
if (fieldsData.KeyExists(i) && fieldsData[i]["dateTimeType"] && // column should have date value,
!dateCorrectlyExtracted && // but in excel it looks awfull
(row != 0 || !useFirstLine)) // also we still dont know actual format and it is not header row
{
previewData = XVar.Array();
// so try to guess format from the value
previewData["dateFormat"] = ImportPage.extractDateFormat(val);
}
}
if (previewData != null)
{
arr.Add(MVCFunctions.runner_htmlspecialchars(val));
}
else
{
arr[fieldsData[i]["fName"]] = val;
}
}
yield return(arr);
}
}
}
public ImportPageViewModel(ImportPage model)
{
this.Model = model;
}
public static XVar ImportDataFromExcel(XVar fileHandle, XVar fieldsData, XVar keys, ImportPage importPageObject, XVar autoinc, XVar useFirstLine)
{
if (fileHandle == null)
{
return(null);
}
XVar metaData = XVar.Array();
metaData["totalRecords"] = 0;
dynamic addedRecords = 0;
dynamic updatedRecords = 0;
XVar errorMessages = XVar.Array();
XVar unprocessedData = XVar.Array();
try
{
ExcelPackage xlsPack = fileHandle.Value as ExcelPackage;
foreach (var record in IterateSheet(xlsPack, fieldsData, useFirstLine))
{
importPageObject.importRecord(record, keys, autoinc, ref addedRecords, ref updatedRecords, errorMessages, unprocessedData);
metaData["totalRecords"] = metaData["totalRecords"] + 1;
}
}
catch (Exception ex)
{
MVCFunctions.import_error_handler(ex);
}
metaData["addedRecords"] = addedRecords;
metaData["updatedRecords"] = updatedRecords;
metaData["errorMessages"] = errorMessages;
metaData["unprocessedData"] = unprocessedData;
return(metaData);
}
