private IEnumerable <ConnectedItem> GetConnectedItems(ICommenceCursor cur)
{
if (this.CategoryDefinition.Clarified)
{
foreach (List <string> row in cur.ReadAllRows())
{
yield return(new ConnectedItem()
{
DisplayName = row[0] + " " + this.CategoryDefinition.ClarifySeparator + " " + row[1],
ItemName = row[0],
ClarifyValue = row[1],
ClarifySeparator = this.CategoryDefinition.ClarifySeparator
});
}
}
else
{
foreach (List <string> row in cur.ReadAllRows())
{
yield return(new ConnectedItem()
{
DisplayName = row[0],
ItemName = row[0],
});
}
}
}
/// <summary>
/// This method is called by all exports methods, it takes care of all the actual exports.
/// Other export methods are really just preparation routines for calling this method.
/// </summary>
/// <param name="cur">ICommenceCursor</param>
/// <param name="fileName">file name</param>
/// <param name="settings">IExportSettings</param>
internal void ExportCursor(ICommenceCursor cur, string fileName, IExportSettings settings)
{
// exporting may take a long time, and the system may go into power saving mode
// this is annoying, so we tell the system not to go into sleep/hibernation
// this may or may not be a good idea...
PowerSavings ps = new PowerSavings();
try
{
ps.EnableConstantDisplayAndPower(true, "Performing time-consuming Commence export");
cur.MaxFieldSize = this.Settings.MaxFieldSize; // remember setting this size greatly impacts memory usage!
using (_writer = this.GetExportWriter(cur, settings))
{
SubscribeToWriterEvents(_writer);
_writer.WriteOut(fileName);
}
}
finally
{
ps.EnableConstantDisplayAndPower(false); // CODE SMELL, may overwrite things
UnsubscribeToWriterEvents(_writer);
_writer?.Dispose();
}
}
/// <inheritdoc />
public void ExportCategory(string categoryName, IEnumerable <ICursorFilter> filters, string fileName, IExportSettings settings = null)
{
if (settings != null)
{
this.Settings = settings;
} // use custom settings if supplied
CmcOptionFlags flags = this.Settings.UseThids
? CmcOptionFlags.UseThids
: CmcOptionFlags.Default | CmcOptionFlags.IgnoreSyncCondition;
using (var db = new CommenceDatabase())
{
if (this.Settings.SkipConnectedItems && this.Settings.HeaderMode != HeaderMode.CustomLabel)
{
using (ICommenceCursor cur = GetCategoryCursorFieldsOnly(db, categoryName, flags))
{
ApplyFilters(cur, filters);
this.Settings.MaxFieldSize = (int)Math.Pow(2, 15); // 32.768, the built-in Commence max fieldlength (large text) is 30.000
ExportCursor(cur, fileName, this.Settings);
}
}
else
{
using (ICommenceCursor cur = GetCategoryCursorAllFieldsAndConnections(db, categoryName, flags))
{
ApplyFilters(cur, filters);
ExportCursor(cur, fileName, this.Settings);
}
}
}
}
public ExcelWriterUsingOpenXml(ICommenceCursor cursor, IExportSettings settings) : base(cursor, settings)
{
columnDefinitions = new List <ColumnDefinition>(_settings.UseThids ? base.ColumnDefinitions.Skip(1) : base.ColumnDefinitions);
_sheetName = string.IsNullOrEmpty(settings.CustomRootNode) ? Utils.EscapeString(_dataSourceName, "_").Left(MaxSheetNameLength) : settings.CustomRootNode;
settings.ISO8601Format = true; // override custom setting(s)
settings.SplitConnectedItems = false; // override custom setting(s)
}
private ICommenceCursor GetCategoryCursorFieldsOnly(ICommenceDatabase db, string categoryName, CmcOptionFlags flags)
{
ICommenceCursor cur = db.GetCursor(categoryName, CmcCursorType.Category, flags);
string[] fieldNames = db.GetFieldNames(categoryName).ToArray();
cur.Columns.AddDirectColumns(fieldNames);
cur.Columns.Apply();
return(cur);
}
public OriginalCursorProperties(ICommenceCursor cursor)
{
Name = string.IsNullOrEmpty(cursor.View)
? cursor.Category
: cursor.View;
Category = cursor.Category;
Type = string.IsNullOrEmpty(cursor.View)
? CmcCursorType.Category.ToString()
: CmcCursorType.View.ToString();
}
private IEnumerable <Field> GetValuesFromCursor(ICommenceCursor cur)
{
IEnumerable <Field> retval = new List <Field>(); // a cursor cannot have no fields, so we should be safe.
ColumnParser cp = new ColumnParser(cur);
IList <ColumnDefinition> columnDefinitions = cp.ParseColumns();
_db.ClarifyItemNames("true");
_itemName = _db.GetActiveItemName(); // also marks the item for us
IList <Field> fields = new List <Field> {
new Field {
Name = _nameField,
Value = _itemName,
Label = columnDefinitions
.Where(w => !w.IsConnection)
.SingleOrDefault(s => s.FieldName.Equals(_nameField))?.ColumnLabel
}
};
// if the name field isn't in the view the view label will be empty,
// in that casechange it to fieldname instead
// note that this will actually return more information than is showing in Commence
if (string.IsNullOrEmpty(fields[0].Label))
{
fields[0].Label = _nameField;
}
// get a list of all the direct fields except the name field
IEnumerable <ColumnDefinition> directFields = columnDefinitions
.Where(w => !w.IsConnection && w.FieldName != _nameField)
.ToArray();
// get the direct field values using DDE
IEnumerable <Field> directFieldValues = GetDirectFieldValues(directFields);
retval = fields.Concat(directFieldValues);
// get the indirect values
IEnumerable <RelatedColumn> relatedColumns = columnDefinitions
.Where(w => w.IsConnection)
.Select(s => new RelatedColumn(s.Connection,
s.Category,
s.FieldName,
RelatedColumnType.ConnectedField,
s.QualifiedConnection + ' ' + s.FieldName)) // very dirty trick!!!!!!
.ToArray();
IEnumerable <Field> connectedFieldValues = GetConnectedFieldValues(relatedColumns);
retval = retval.Concat(connectedFieldValues);
_db.ClarifyItemNames(clarifyStatus); // restore original setting
return(retval);
} // method
/// <summary>
/// Constructor used when exporting cursor.
/// </summary>
/// <param name="cursor">Database.ICommenceCursor.</param>
/// <param name="settings">ExportSettings object.</param>
/// <exception cref="ArgumentOutOfRangeException"></exception>
/// <exception cref="ArgumentException"></exception>
protected internal BaseWriter(ICommenceCursor cursor,
IExportSettings settings)
{
_cursor = cursor;
_settings = settings;
// this check is very important
if (_settings.HeaderMode == HeaderMode.CustomLabel)
{
if (ValidCustomHeaders(_settings.CustomHeaders.Select(x => x.ToString()).ToArray()))
{
_customColumnHeaders = _settings.CustomHeaders.Select(x => x.ToString()).ToArray();
_customColumnHeaders = Utils.RenameDuplicates(_customColumnHeaders.ToList()).ToArray();
}
}
_dataSourceName = (string.IsNullOrEmpty(_cursor.View)) ? _cursor.Category : _cursor.View;
_cursor.SeekRow(CmcCursorBookmark.Beginning, 0); // put rowpointer on first item
}
internal DataReader(ICommenceCursor cursor, IExportSettings settings, List <ColumnDefinition> columndefinitions)
{
this.cursor = (CommenceCursor)cursor;
totalRows = cursor.RowCount;
this.settings = settings;
numRows = (int)Math.Pow(2, BalanceNumRowsAndFieldSize(settings));
if (settings.NumRows < numRows)
{
numRows = settings.NumRows;
}
this.columnDefinitions = columndefinitions;
regex = new Regex(pattern);
if (this.cursor.Flags.HasFlag(CmcOptionFlags.UseThids))
{
useThids = true;
}
}
private ICommenceCursor GetCategoryCursorAllFieldsAndConnections(ICommenceDatabase db, string categoryName, CmcOptionFlags flags)
{
ICommenceCursor cur = db.GetCursor(categoryName, CmcCursorType.Category, flags);
string[] fieldNames = db.GetFieldNames(categoryName).ToArray();
cur.Columns.AddDirectColumns(fieldNames);
var cons = db.GetConnectionNames(cur.Category);
foreach (var c in cons)
{
//string nameField = db.GetNameField(c.ToCategory);
//cur.Columns.AddRelatedColumn(c.Name, c.ToCategory, nameField); // this is bad. a related column loses the THID flag
cur.Columns.AddDirectColumn(c.Name + ' ' + c.ToCategory); // will respect UseThids flag
}
cur.Columns.Apply();
return(cur);
}
/// <inheritdoc />
public void ExportView(string viewName, string fileName, IExportSettings settings = null)
{
if (settings != null)
{
this.Settings = settings;
} // use user-supplied settings
using (var db = new CommenceDatabase())
{
string _viewName = string.IsNullOrEmpty(viewName) ? GetActiveViewName(db) : viewName;
using (ICommenceCursor cur = db.GetCursor(_viewName, CmcCursorType.View, this.Settings.UseThids
? CmcOptionFlags.UseThids
: CmcOptionFlags.Default))
{
ExportCursor(cur, fileName, this.Settings);
}
}
}
/// <summary>
/// Factory method for creating the required export writer object for a cursor export.
/// </summary>
/// <param name="cursor">Database.ICommenceCursor .</param>
/// <param name="settings">Settings object.</param>
/// <returns>Derived BaseDataWriter object.</returns>
/// <remarks>Defaults to XML.</remarks>
internal BaseWriter GetExportWriter(ICommenceCursor cursor, IExportSettings settings)
{
if (!settings.PreserveAllConnections && settings.NestConnectedItems && settings.ExportFormat == ExportFormat.Json)
{
return(new AdoNetWriter(cursor, settings)); // i think this can be taken out entirely
}
if (settings.PreserveAllConnections)
{
return(new Complex.SQLiteWriter(cursor, settings));
}
switch (settings.ExportFormat)
{
case ExportFormat.Text:
return(new TextWriter(cursor, settings));
case ExportFormat.Html:
return(new HtmlWriter(cursor, settings));
case ExportFormat.Xml:
return(new XmlWriter(cursor, settings));
case ExportFormat.Json:
return(new JsonWriter(cursor, settings));
case ExportFormat.Excel:
//_writer = new ExcelWriterUsingXml(cursor, settings);
//return new ExcelWriterUsingOleDb(cursor, settings);
//return new ExcelWriterUsingOpenXml(cursor, settings);
return(new ExcelWriterUsingEPPlus(cursor, settings));
case ExportFormat.Event:
return(new EventWriter(cursor, settings));
case ExportFormat.GoogleSheets:
// will probably always be too slow
throw new NotImplementedException("Exportformat not yet implemented.");
default:
return(new XmlWriter(cursor, settings));
}
}
internal ExcelWriterUsingEPPlus(ICommenceCursor cursor, IExportSettings settings)
: base(cursor, settings)
{
string s = string.IsNullOrEmpty(settings.CustomRootNode) ? _dataSourceName : settings.CustomRootNode;
_dataTable = PrepareDataTable(s, base.ColumnDefinitions);
_sheetName = Utils.EscapeString(s, "_").Left(MaxSheetNameLength);
settings.Canonical = true; // override custom setting
settings.SplitConnectedItems = false; // override custom setting
// When dealing with very large cursors,
// the default number of rows read may lead to memory issues when dumping to a datatable
// we may have to cap the number of items being read
// This superceeds the check already performed in the datareader.
// I am undecided on this.
// What I do know is that writing 1000 rows of 250 columns of size 30.000
// (i.e. 250 large text fields, fully populated) will fail.
// Maybe we should have some mechanism that collects the size of the fields
// then caps the NumRows count accordingly.
// However, it would not solve issues with EPPlus running out of memory when dealing with huge workbooks.
}
/// <inheritdoc />
public void ExportCategory(string categoryName, string fileName, IExportSettings settings = null)
{
if (settings != null)
{
this.Settings = settings;
} // use custom settings if supplied
CmcOptionFlags flags = this.Settings.UseThids
? CmcOptionFlags.UseThids
: CmcOptionFlags.Default | CmcOptionFlags.IgnoreSyncCondition;
// User requested we skip connections.
// A default cursor on a category contains all fields *and* connections.
// The data receiving routines will ignore them, but they will be read unless we do not include them in our cursor
// We optimize here by only including direct fields in the cursor
using (var db = new CommenceDatabase())
{
if (this.Settings.SkipConnectedItems && this.Settings.HeaderMode != HeaderMode.CustomLabel)
{
using (ICommenceCursor cur = GetCategoryCursorFieldsOnly(db, categoryName, flags))
{
// we can limit MAX_FIELD_SIZE in this case
this.Settings.MaxFieldSize = (int)Math.Pow(2, 15); // 32.768, the built-in Commence max fieldlength (large text) is 30.000
ExportCursor(cur, fileName, this.Settings);
}
}
else
{
using (ICommenceCursor cur = GetCategoryCursorAllFieldsAndConnections(db, categoryName, flags))
{
// You can create a cursor with all fields including connections by just
// supplying CmcOptionFlags.All.
// However, when the cursor is read, connected items are returned as comma-delimited strng,
// which, because Commence does not supply text-qualifiers, makes it impossible to split them.
// We therefore explicitly set the connections which deteriorates performance
// but gains us (more) reliability.
ExportCursor(cur, fileName, this.Settings);
}
}
}
}
internal ICommenceCursor Create(CursorDescriptor cursorParameters)
{
ICommenceCursor retval = null;
if (db == null)
{
throw new InvalidOperationException($"Underlying database was closed.");
}
retval = db.GetCursor(cursorParameters.CategoryOrView, cursorParameters.CursorType, CmcOptionFlags.UseThids | CmcOptionFlags.IgnoreSyncCondition);
retval.SetColumns(cursorParameters.Fields.ToArray());
retval.Columns.Apply();
for (int i = 0; i < cursorParameters.Filters.Count(); i++)
{
ICursorFilterTypeCTCF f = retval.Filters.Create(i + 1, FilterType.ConnectionToCategoryField);
// we already have the filter object defined, copy them
foreach (PropertyInfo property in typeof(ICursorFilterTypeCTCF).GetProperties().Where(p => p.CanWrite))
{
property.SetValue(f, property.GetValue(cursorParameters.Filters[i], null), null);
}
}
retval.Filters.Apply();
retval.MaxFieldSize = cursorParameters.MaxFieldSize;
return(retval);
}
private ICursorFilter CreateFilter(string categoryName, FilterType filterType)
{
using (ICommenceDatabase db = new CommenceDatabase())
{
using (ICommenceCursor cur = db.GetCursor(categoryName))
{
switch (filterType)
{
case FilterType.Field:
return((CursorFilterTypeF)cur.Filters.Add(_model.ClauseNumber, filterType));
case FilterType.ConnectionToItem:
return((CursorFilterTypeCTI)cur.Filters.Add(_model.ClauseNumber, filterType));
case FilterType.ConnectionToCategoryField:
return((CursorFilterTypeCTCF)cur.Filters.Add(_model.ClauseNumber, filterType));
case FilterType.ConnectionToCategoryToItem:
return((CursorFilterTypeCTCTI)cur.Filters.Add(_model.ClauseNumber, filterType));
}
}
}
return(null);
}
internal ExcelWriterUsingOleDb(ICommenceCursor cursor, IExportSettings settings) : base(cursor, settings)
{
columnDefinitions = new List <ColumnDefinition>(_settings.UseThids ? base.ColumnDefinitions.Skip(1) : base.ColumnDefinitions);
}
private void ApplyFilters(ICommenceCursor cur, IEnumerable <ICursorFilter> filters)
{
((CursorFilters)cur.Filters).AddRange(filters); // AddRange is not exposed by interface
cur.Filters.Apply();
}
private IList <ConnectedItem> PopulateConnectedItemNamesList(string searchString)
{
IList <ConnectedItem> retval = new List <ConnectedItem>();
if (string.IsNullOrEmpty(this.SelectedConnectedCategory))
{
return(retval);
}
using (ICommenceDatabase db = new CommenceDatabase())
{
using (ICommenceCursor cur = db.GetCursor(this.SelectedConnectedCategory))
{
string nameField = db.GetNameField(this.SelectedConnectedCategory);
var columns = this.CategoryDefinition.Clarified
? new[] { nameField, this.CategoryDefinition.ClarifyField }
: new[] { nameField };
if (!cur.SetColumns(columns))
{
return(retval);
} // something went wrong bad
var number = cur.RowCount;
if (string.IsNullOrEmpty(searchString))
{
if (number == 0)
{
retval.Add(new ConnectedItem("(No items to display)", null, null, null));
return(retval);
}
else if (number < 1000)
{
return(GetConnectedItems(cur).ToList());
}
else
{
retval.Add(new ConnectedItem("(Too many items to display.)", null, null, null));
return(retval);
}
}
else
{
CursorFilterTypeF f = cur.Filters.Create(1, FilterType.Field);
f.FieldValue = this.ConnectedItemSearchString;
f.FieldName = nameField;
f.Qualifier = FilterQualifier.Contains;
int count = cur.Filters.Apply();
if (count > 1000)
{
retval.Add(new ConnectedItem("(Too many items to display)", null, null, null));
return(retval);
}
else if (count == 0)
{
retval.Add(new ConnectedItem($"(No items contain '{ this.ConnectedItemSearchString }')", null, null, null));
return(retval);
}
else
{
return(GetConnectedItems(cur).ToList());
}
}
}
}
}