public VisualizerSettingsDialog(DataQueryResult result, VisualizerSettingsCollection settings)
{
this.result = result;
this.settings = settings;
Initialize();
}
/// <summary>
/// Generates a dictionary containing all the columns from the select that are renamed (using AS)
/// the hashtable has the original values as values and the translated values as keys.
/// </summary>
/// <param name="querySet"></param>
/// <param name="sBuilder"></param>
/// <returns></returns>
private Dictionary <DbField, SelectColumnInfo> CreateDictionaryFromSelect(DataQueryResult querySet, SelectBuilder sBuilder)
{
Dictionary <DbField, SelectColumnInfo> translation = new Dictionary <DbField, SelectColumnInfo> ();
string [] columnNames = sBuilder.SelectClause.Select(cInfo => cInfo.ColumnName).ToArray();
querySet.Columns = fieldsTable.TranslateToColumnInfo(columnNames);
querySet.MarkTranslated();
querySet.MarkHidden();
querySet.MarkPermanent();
for (int i = 0; i < querySet.Columns.Length; i++)
{
if (querySet.Columns [i].Field == null)
{
if (!querySet.Columns [i].IsTranslated)
{
throw new Exception("Unable to translate column " + columnNames [i]);
}
}
else
{
SelectColumnInfo cInfo = sBuilder.SelectClause [i];
if (cInfo.IsRenamed)
{
translation.Add(querySet.Columns [i].Field, cInfo);
}
}
}
return(translation);
}
/// <summary>
/// Generate the final where clause from the already translated conditions in the query set.
/// </summary>
/// <param name="querySet"></param>
/// <param name="sBuilder"></param>
/// <returns></returns>
private static string GenerateConditionString(DataQueryResult querySet, SelectBuilder sBuilder)
{
foreach (DataFilter filter in querySet.Filters)
{
if (!filter.IsValid)
{
continue;
}
if (filter.SQLTranslation.Count == 0)
{
continue;
}
StringBuilder condition = new StringBuilder();
for (int j = 0; j < filter.SQLTranslation.Count; j++)
{
if (j > 0)
{
condition.Append(" OR ");
}
condition.Append(filter.SQLTranslation [j]);
}
sBuilder.AddCondition(filter.CombineLogic, filter.SQLTranslation.Count > 1 ?
string.Format("({0})", condition) : condition.ToString());
}
return(sBuilder.ToString());
}
public override void Initialize(DataQueryResult query, VisualizerSettingsCollection settings, bool preserveSort = true)
{
if (query == null)
{
throw new ArgumentNullException("query");
}
if (settings == null)
{
throw new ArgumentNullException("settings");
}
try {
if (initializing)
{
return;
}
initializing = true;
initialized = false;
currentSettings = settings.GetSettings <TableVisualizerSettings> () ?? new TableVisualizerSettings();
skip = new List <DbField> (currentSettings.SkippedColumns);
if (dataQueryResult != null)
{
dataQueryResult.Result.ListChanged -= OnModelListChanged;
}
dataQueryResult = query;
if (model != null && preserveSort)
{
dataQueryResult.Result.Sort(model.SortColumn);
}
dataQueryResult.Result.ListChanged += OnModelListChanged;
LazyTableModel oldModel = model;
model = dataQueryResult.Result;
model.SortChanged += sortModel_SortChanged;
if (oldModel != null && !ReferenceEquals(oldModel, model))
{
oldModel.SortChanged -= sortModel_SortChanged;
oldModel.Dispose();
}
listReset = true;
listChangedRized = false;
if (!dataQueryResult.Result.Start())
{
// Already started and possibly finished
OnModelListChanged(query.Result, new ListChangedEventArgs(ListChangedType.Reset, query.Result.Count - 1));
}
else
{
SwitchToWidget(imgLoading);
}
} finally {
initializing = false;
}
}
public static string GetReportFieldColumnName(DataQueryResult result, int columnIndex)
{
DbField field = result.Columns [columnIndex].Field;
return(result.Columns [columnIndex].IsTranslated ?
result.Result.Columns [columnIndex] :
Translator.GetReportFieldColumnName(field));
}
private string GetQuery(SqlHelper helper, DataQuery querySet, string query, out DataQueryResult queryResult, bool removeHidden)
{
queryResult = new DataQueryResult(querySet);
SelectBuilder sBuilder = new SelectBuilder(fieldsTable, query);
Dictionary <DbField, SelectColumnInfo> selectDictionary = CreateDictionaryFromSelect(queryResult, sBuilder);
helper.AddParameters(GenerateConditionStatements(queryResult, selectDictionary));
if (removeHidden)
{
sBuilder = RemoveHiddenColumns(queryResult, sBuilder, selectDictionary);
}
return(GenerateConditionString(queryResult, sBuilder));
}
public static bool CheckColumnVisible(DataQueryResult queryResult, int i)
{
ColumnInfo columnInfo = queryResult.Columns [i];
if (columnInfo.IsHidden)
{
return(false);
}
User loggedUser = BusinessDomain.LoggedUser;
if (!loggedUser.IsSaved)
{
return(false);
}
DbField field = columnInfo.Field;
return((!loggedUser.HideItemsPurchasePrice || !PuchasePriceFields.Contains(field)) &&
(!loggedUser.HideItemsAvailability || !AvailabilityFields.Contains(field)));
}
public override bool LoadSettings(DataQueryResult result, VisualizerSettingsCollection settings)
{
if (result == null)
{
throw new ArgumentNullException("result");
}
if (settings == null)
{
throw new ArgumentNullException("settings");
}
if (initialized)
{
throw new ApplicationException("The settings are already initialized");
}
TableVisualizerSettings tableSettings = settings.GetSettings <TableVisualizerSettings> () ?? new TableVisualizerSettings();
chkShowTotals.Active = tableSettings.ShowTotals;
List <DbField> skip = new List <DbField> (tableSettings.SkippedColumns);
for (uint i = 0; i < result.Columns.Length; i++)
{
string columnName = ReportProvider.GetReportFieldColumnName(result, (int)i);
DbField columnField = result.Columns [i].Field;
CheckButton btn = new CheckButton(columnName);
tblColumns.Attach(btn, 0, 1, i, i + 1,
AttachOptions.Expand | AttachOptions.Fill | AttachOptions.Shrink,
AttachOptions.Fill, 0, 0);
btn.Active = !skip.Contains(columnField);
btn.Show();
buttons.Add(new KeyValuePair <CheckButton, DbField> (btn, columnField));
}
currentSettings = tableSettings;
initialized = true;
return(true);
}
/// <summary>
/// Remove all the columns that are marked for removal from the select statement, the group by statement
/// and the Columns array in the data query
/// </summary>
/// <param name="querySet"></param>
/// <param name="sBuilder"></param>
/// <param name="dictionary"></param>
/// <returns></returns>
private SelectBuilder RemoveHiddenColumns(DataQueryResult querySet, SelectBuilder sBuilder, Dictionary <DbField, SelectColumnInfo> dictionary)
{
List <DbField> toRemove = new List <DbField> ();
List <DbField> toRemoveExtension = new List <DbField> ();
SelectColumnInfo cInfo;
int i;
#region Generate a list with all the fields to remove
foreach (DataFilter filter in querySet.Filters)
{
if (filter.ShowColumns)
{
continue;
}
foreach (DbField field in filter.FilteredFields)
{
toRemove.Add(field);
// Add the source of that field using the SELECT dictionary if available
// This is used to remove the column from the GROUP BY statement
DbField foundDbField;
if (dictionary.TryGetValue(field, out cInfo))
{
foundDbField = fieldsTable.GetFieldByAny(cInfo.SourceField);
if (foundDbField != field)
{
toRemoveExtension.Add(foundDbField);
}
}
// Add all the fields that have this field for a source from the SELECT dictionary
foreach (DbField dField in dictionary.Keys)
{
cInfo = dictionary [dField];
foundDbField = fieldsTable.GetFieldByAny(cInfo.SourceField);
if (foundDbField == field)
{
toRemove.Add(dField);
}
}
}
}
#endregion
#region Remove all the hidden columns from the select statement
List <ColumnInfo> newColumns = new List <ColumnInfo> (querySet.Columns);
for (i = sBuilder.SelectClause.Count - 1; i >= 0; i--)
{
DbField field = newColumns [i].Field;
if (field == null || !toRemove.Contains(field))
{
continue;
}
newColumns.RemoveAt(i);
sBuilder.SelectClause.RemoveAt(i);
}
querySet.Columns = newColumns.ToArray();
#endregion
if (sBuilder.GroupByClause.Count > 0)
{
#region Remove all the hidden columns from the group by clause
for (i = sBuilder.GroupByClause.Count - 1; i >= 0; i--)
{
DbField [] fields = fieldsTable.GetFieldsByName(sBuilder.GroupByClause [i]);
if (fields.Length == 0)
{
DbField dbField = fieldsTable.GetFieldByAlias(sBuilder.GroupByClause [i]);
if (dbField != null)
{
fields = new [] { dbField }
}
;
else
{
foreach (KeyValuePair <DbField, SelectColumnInfo> selectColumnInfo in dictionary)
{
if (selectColumnInfo.Value.SourceExpression != sBuilder.GroupByClause [i])
{
continue;
}
fields = new [] { selectColumnInfo.Key };
break;
}
if (fields.Length == 0)
{
throw new Exception(string.Format("Unable to parse column \"{0}\" in GROUP BY statement in the query \"{1}\"", sBuilder.GroupByClause [i], sBuilder));
}
}
}
foreach (DbField field in fields)
{
if (!toRemove.Contains(field) && !toRemoveExtension.Contains(field))
{
continue;
}
sBuilder.GroupByClause.RemoveAt(i);
break;
}
}
#endregion
}
return(sBuilder);
}
/// <summary>
/// Translates the condition statements (filters) inside the query set to a sql and stores them
/// inside the query set.
/// </summary>
/// <param name="querySet"></param>
/// <param name="dictionary"></param>
private DbParam [] GenerateConditionStatements(DataQueryResult querySet, IDictionary <DbField, SelectColumnInfo> dictionary)
{
List <DbParam> pars = new List <DbParam> ();
foreach (DataFilter filter in querySet.Filters)
{
if (!filter.IsValid || filter.Values == null || filter.Values.Length == 0)
{
continue;
}
object [] values = filter.Values;
string templateSource;
switch (filter.Logic)
{
case DataFilterLogic.Less:
templateSource = "({{0}} < @wPar{0})";
break;
case DataFilterLogic.LessOrEqual:
templateSource = "({{0}} <= @wPar{0})";
break;
case DataFilterLogic.Greather:
templateSource = "({{0}} > @wPar{0})";
break;
case DataFilterLogic.GreatherOrEqual:
templateSource = "({{0}} >= @wPar{0})";
break;
case DataFilterLogic.ExactMatch:
templateSource = "({{0}} = @wPar{0})";
if (filter.FilteredFields.Any(f =>
f == DataField.OperationType ||
f == DataField.DocumentOperationType ||
f == DataField.PaymentOperationType) &&
values.Length > 0 && values [0] != null)
{
OperationType operType = (OperationType)values [0];
switch (operType)
{
case OperationType.TransferIn:
case OperationType.TransferOut:
values = new object [] { OperationType.TransferIn, OperationType.TransferOut };
break;
case OperationType.ComplexRecipeMaterial:
case OperationType.ComplexRecipeProduct:
values = new object [] { OperationType.ComplexRecipeMaterial, OperationType.ComplexRecipeProduct };
break;
case OperationType.ComplexProductionMaterial:
case OperationType.ComplexProductionProduct:
values = new object [] { OperationType.ComplexProductionMaterial, OperationType.ComplexProductionProduct };
break;
}
if (values.Length > 1)
{
templateSource = "(({{0}} = @wPar{0}) OR ({{0}} = @wPar{1}))";
}
}
break;
case DataFilterLogic.Contains:
if (values [0] != null)
{
values = values [0].ToString().Split(new [] { ' ' }, StringSplitOptions.RemoveEmptyEntries).Cast <object> ().ToArray();
}
List <string> sources = new List <string> ();
for (int i = 0; i < values.Length; i++)
{
sources.Add(string.Format("({{{{0}}}} LIKE {0})", GetConcatStatement("'%'", string.Format("@wPar{{{0}}}", i), "'%'")));
}
templateSource = sources.Count > 1 ? string.Format("({0})", string.Join(" AND ", sources)) : sources [0];
break;
case DataFilterLogic.In:
templateSource = string.Format("({{{{0}}}} IN ({0}))",
string.Join(", ", values.Select(o => o.ToString())));
break;
case DataFilterLogic.StartsWith:
templateSource = string.Format("({{{{0}}}} LIKE {0})", GetConcatStatement("@wPar{0}", "'%'"));
break;
case DataFilterLogic.EndsWith:
templateSource = string.Format("({{{{0}}}} LIKE {0})", GetConcatStatement("'%'", "@wPar{0}"));
break;
case DataFilterLogic.InRange:
if (values.Length < 2)
{
continue;
}
if (values [0] != null && values [1] != null)
{
templateSource = "({{0}} >= @wPar{0}) AND ({{0}} <= @wPar{1})";
}
else if (values [0] != null)
{
templateSource = "({{0}} >= @wPar{0})";
}
else
{
templateSource = "({{0}} <= @wPar{0})";
}
if (values.Length > 2)
{
values = values.Take(2).ToArray();
}
break;
case DataFilterLogic.InEntityGroup:
// filtering by groups must include the subgroups
// look for groups which code starts with the code of the group with a name that starts with the parameter
string groupsTable = fieldsTable.GetFieldTable(filter.FilteredFields [0]);
string originalTable = groupsTable.TrimEnd('1', '2');
List <string> codes = ExecuteList <string> (string.Format("SELECT {0}.Code FROM {0} WHERE {0}.Name LIKE {1}",
originalTable, GetConcatStatement("'%'", "@Name", "'%'")),
new DbParam("Name", values [0]));
if (codes.Count > 0)
{
StringBuilder temp = new StringBuilder();
foreach (string code in codes)
{
if (temp.Length > 0)
{
temp.Append(" OR ");
}
temp.AppendFormat("({0}.Code LIKE '{1}%')", groupsTable, code);
}
templateSource = codes.Count > 1 ? string.Format("({0})", temp) : temp.ToString();
}
else
{
templateSource = "1 = 0";
}
break;
case DataFilterLogic.MoreThanMinutesAgo:
templateSource = GetMoreThanMinuteAgoFilter();
break;
case DataFilterLogic.LessThanMinutesAgo:
templateSource = GetLessThanMinuteAgoFilter();
break;
default:
continue;
}
TranslateFilter(filter, GetFilterTemplate(templateSource, pars, values), dictionary);
}
return(pars.ToArray());
}
public abstract bool LoadSettings(DataQueryResult result, VisualizerSettingsCollection settings);
public abstract void Initialize(DataQueryResult query, VisualizerSettingsCollection settings, bool preserveSort = true);
public WbpReportResult(DataQueryResult querySet)
{
qSetObject = querySet;
InitializeForm();
}
public KeyValuePair <string, string> [] GetReportLines()
{
DataQueryResult qSet = ExecuteReport();
IList <DataField> dataFields = GetReportFields();
List <KeyValuePair <string, string> > ret = new List <KeyValuePair <string, string> > ();
double total = 0;
Dictionary <DataField, int> dataFieldsIndices = new Dictionary <DataField, int> (dataFields.Count);
foreach (DataField dataField in dataFields)
{
for (int i = 0; i < qSet.Columns.Length; i++)
{
if (qSet.Columns [i].Field.StrongField == dataField)
{
dataFieldsIndices.Add(dataField, i);
break;
}
}
}
foreach (LazyTableDataRow row in qSet.Result)
{
foreach (KeyValuePair <DataField, int> dataFieldIndex in dataFieldsIndices)
{
object value = row [dataFieldIndex.Value];
string text;
string textValue = null;
switch (dataFieldIndex.Key)
{
case DataField.OperationNumber:
text = Translator.GetString("Document:");
textValue = Operation.GetFormattedOperationNumber(Convert.ToInt64(value));
break;
case DataField.OperationDateTime:
case DataField.OperationTimeStamp:
text = Translator.GetString("Date/Time:");
textValue = BusinessDomain.GetFormattedDateTime(Convert.ToDateTime(value));
break;
case DataField.UserName:
case DataField.OperationsOperatorName2:
text = Translator.GetString("Operator:");
break;
case DataField.OperationLocation2:
text = Translator.GetString("Location:");
break;
case DataField.OperationPartner2:
text = Translator.GetString("Client:");
break;
case DataField.OperationTotal:
case DataField.PaymentAmount:
double sum = Convert.ToDouble(value);
text = Translator.GetString("Amount:");
textValue = Currency.ToString(sum, PriceType.Unknown);
total += sum;
break;
case DataField.OperationType:
text = Translator.GetString("Type:");
textValue = Translator.GetOperationTypeName((OperationType)value);
break;
case DataField.PaymentTypesName:
text = Translator.GetString("Type:");
break;
case DataField.PartnerName:
text = Translator.GetString("Partner:");
break;
default:
continue;
}
if (string.IsNullOrEmpty(textValue))
{
textValue = value.ToString();
int index;
if (string.IsNullOrEmpty(textValue) && dataFieldsIndices.TryGetValue(dataFieldIndex.Key, out index))
{
textValue = (row [index] ?? string.Empty).ToString();
}
}
ret.Add(new KeyValuePair <string, string> (text, textValue));
}
ret.Add(new KeyValuePair <string, string> (DriverBase.SEPARATOR, DriverBase.SEPARATOR));
}
if (total > 0)
{
ret.Add(new KeyValuePair <string, string> (Translator.GetString("Total:"), Currency.ToString(total, PriceType.Unknown)));
}
return(ret.ToArray());
}
public override DataQueryResult ReportItemsAvailability(DataQuery querySet, string quantityTranslation)
{
StringBuilder sb = new StringBuilder();
string defLocationName = string.Empty;
string availFormat = string.Format("SUM(CASE WHEN objects.ID = {{1}} THEN store.Qtty ELSE 0 END) AS `{0}`,", quantityTranslation);
string singleLocationName = string.Empty;
// Check if we are filtering by location
foreach (DataFilter filter in querySet.Filters.Where(f => f.IsValid))
{
if (filter.FilteredFields.Any(filterField => filterField == DataField.LocationName))
{
singleLocationName = (string)filter.Values [0];
}
if (!string.IsNullOrEmpty(singleLocationName))
{
break;
}
}
SqlHelper helper = GetSqlHelper();
int i = 0;
DbParam par;
int translatedColNumber = querySet.UseLots ? 16 : 11;
if (string.IsNullOrEmpty(singleLocationName))
{
List <KeyValuePair <string, long> > locations = new List <KeyValuePair <string, long> > ();
using (IDataReader reader = ExecuteReader("SELECT ID, Name FROM objects"))
while (reader.Read())
{
locations.Add(new KeyValuePair <string, long> ((string)reader.GetValue(1), Convert.ToInt64(reader.GetValue(0))));
}
List <string> usedNames = new List <string> ();
for (int j = 0; j < locations.Count; j++)
{
KeyValuePair <string, long> obj = locations [j];
string name = obj.Key;
long id = obj.Value;
int c = 0;
if (usedNames.Contains(name))
{
while (locations.Count(o => o.Key == name) > 1)
{
name = string.Format("{0} # {1}", obj.Key, ++c);
locations [j] = new KeyValuePair <string, long> (name, id);
}
}
usedNames.Add(name);
par = new DbParam("locationId" + i, id);
helper.AddParameters(par);
if (id == 1)
{
defLocationName = name;
}
else
{
sb.AppendFormat(availFormat, name, fieldsTable.GetParameterName(par.ParameterName));
}
querySet.TranslatedColumns.Add(translatedColNumber++);
i++;
}
if (string.IsNullOrEmpty(defLocationName))
{
throw new Exception("Default location not found.");
}
sb.AppendFormat(availFormat, defLocationName, 1);
}
else
{
par = new DbParam("LocationName", singleLocationName);
object locationIdObject = ExecuteScalar("SELECT ID FROM objects WHERE Name = @LocationName", par);
// If there is no such point of sale then return empty result set
if (locationIdObject == null || IsDBNull(locationIdObject))
{
return new DataQueryResult(querySet)
{
Result = new LazyTableModel()
}
}
;
par = new DbParam("locationId", Convert.ToInt64(locationIdObject));
helper.AddParameters(par);
sb.AppendFormat(availFormat, singleLocationName, fieldsTable.GetParameterName(par.ParameterName));
querySet.TranslatedColumns.Add(translatedColNumber);
// If we are showing a single location quantities hide the total quantities column
querySet.Filters.Add(new DataFilter(DataField.StoreAvailableQuantity)
{
ShowColumns = false
});
}
helper.AddParameters(new DbParam("nonInv", (int)ItemType.NonInventory));
string query = string.Format(@"
SELECT goods.ID, goods.Code, goods.Name, goods.BarCode1, goods.BarCode2, goods.BarCode3, goods.Catalog1,
goods.Catalog2, goods.Catalog3, goods.Measure1, goodsgroups.Name,{9}
{0}
SUM(store.Qtty) as {1},
{14} as {2},
SUM(store.Qtty * {14}) as {3},
SUM(store.Qtty * {14}){12} as {4},
goods.PriceOut2 as {5},
(SUM(store.Qtty) * goods.PriceOut2) as {6},
(SUM(store.Qtty) * goods.PriceOut2){13} as {7},
' ' as {8}
FROM (((((goods LEFT JOIN store ON store.GoodID = goods.ID)
LEFT JOIN goodsgroups ON ABS(goods.GroupID) = goodsgroups.ID)
LEFT JOIN objects ON objects.ID = store.ObjectID)
LEFT JOIN objectsgroups ON ABS(objects.GroupID) = objectsgroups.ID)
LEFT JOIN vatgroups ON goods.TaxGroup = vatgroups.ID){10}
WHERE goods.Deleted = 0 AND (goods.Type & @nonInv = 0)
GROUP BY goods.ID, goods.Code, goods.Name, goods.PriceOut2, goods.BarCode1,
goods.BarCode2, goods.BarCode3, goods.Catalog1, goods.Catalog2, goods.Catalog3,
goods.Measure1, goodsgroups.Name, {14}, vatgroups.VATValue{11}",
sb,
fieldsTable.GetFieldAlias(DataField.StoreAvailableQuantity),
fieldsTable.GetFieldAlias(DataField.StorePrice),
fieldsTable.GetFieldAlias(DataField.ItemTradeInSum),
fieldsTable.GetFieldAlias(DataField.ItemTradeInVAT),
fieldsTable.GetFieldAlias(DataField.ItemRegularPrice),
fieldsTable.GetFieldAlias(DataField.ItemTradeSum),
fieldsTable.GetFieldAlias(DataField.ItemTradeVAT),
fieldsTable.GetFieldAlias(DataField.StoreCountedQuantity),
GetReportStoreLotSelect(querySet),
GetReportStoreLotJoin(querySet),
GetReportStoreLotGroup(querySet),
querySet.VATIsIncluded ? " - (SUM(store.Qtty * store.Price) / (1 + vatgroups.VATValue / 100))" : " * vatgroups.VATValue / 100",
querySet.VATIsIncluded ? " - (SUM(store.Qtty * goods.PriceOut2) / (1 + vatgroups.VATValue / 100))" : " * vatgroups.VATValue / 100",
querySet.UseLots ? "store.Price" : "goods.PriceIn");
querySet.SetSimpleId(DbTable.Items, DataField.ItemId, 0);
DataQueryResult result = ExecuteDataQuery(querySet, query, helper.Parameters);
for (i = 0; i < result.Columns.Length; i++)
{
if (result.Columns [i].IsTranslated)
{
result.Columns [i].Field = new DbField(DataField.StoreQtty);
}
}
return(result);
}