/// <summary>
/// Return list of payments by criteria
/// </summary>
/// <param name="familyId"></param>
/// <param name="transactionId"></param>
/// <param name="isReceipt"></param>
/// <param name="dateMin"></param>
/// <param name="dateMax"></param>
/// <param name="amountMin"></param>
/// <param name="amountMax"></param>
/// <param name="categoryId"></param>
/// <param name="solicitorId"></param>
/// <param name="departmentId"></param>
/// <param name="receiptSent"></param>
/// <param name="subcategoryId"></param>
/// <param name="receiptNumberMin"></param>
/// <param name="receiptNumberMax"></param>
/// <param name="isPreview"></param>
/// <param name="usePagination"></param>
/// <param name="offset">start position for pagination</param>
/// <param name="pageSize">count rows on the page</param>
/// <param name="orderBy">Date, Category, Class, Amount, PaymentMethod, ReceiptNo, ReceiptSent</param>
/// <param name="ascDesc">false - ascending, true - descending</param>
/// <param name="searchFamName">Family name</param>
/// <returns></returns>
public IEnumerable <PaymentDto> GetAllPayments(int?familyId = null, int?transactionId = null, bool?isReceipt = null, DateTime?dateMin = null, DateTime?
dateMax = null, decimal?amountMin = null, decimal?amountMax = null, int?categoryId = null, int?solicitorId = null, int?departmentId = null, bool?
receiptSent = null, int?subcategoryId = null, int?receiptNumberMin = null, int?receiptNumberMax = null, bool?isPreview = false,
bool?usePagination = false, int offset = 1, int pageSize = 10, string orderBy = "Date", bool ascDesc = false, string searchFamName = null)
{
var selectPaymentsSp = QueryProvider.CreateQuery <SelectPaymentsStoredProcedure>(_schema);
selectPaymentsSp.FamilyId = familyId;
selectPaymentsSp.TransactionId = transactionId;
selectPaymentsSp.IsReceipt = isReceipt;
selectPaymentsSp.DateMin = dateMin;
selectPaymentsSp.DateMax = dateMax;
selectPaymentsSp.AmountMin = amountMin;
selectPaymentsSp.AmountMax = amountMax;
selectPaymentsSp.CategoryID = categoryId;
selectPaymentsSp.SolicitorID = solicitorId;
selectPaymentsSp.DepartmentID = departmentId;
selectPaymentsSp.ReceiptSent = receiptSent;
selectPaymentsSp.SubcategoryID = subcategoryId;
selectPaymentsSp.ReceiptNumberMin = receiptNumberMin;
selectPaymentsSp.ReceiptNumberMax = receiptNumberMax;
selectPaymentsSp.PreviewOnly = isPreview;
selectPaymentsSp.UsePagination = usePagination;
selectPaymentsSp.Offset = offset;
selectPaymentsSp.PageSize = pageSize;
selectPaymentsSp.OrderBy = orderBy;
selectPaymentsSp.AscDesc = ascDesc;
selectPaymentsSp.SearchFamilyName = searchFamName;
var result = selectPaymentsSp.Execute();
return(result.DataRows != null?result.ResultToArray <PaymentDto>() : ArrayUtils.Empty <PaymentDto>());
}
private IEnumerable <MailingDto> GetMailings(int?mailigID)
{
var selectMailingsSp = QueryProvider.CreateQuery <SelectMailingsStoredProcedure>(_schema);
selectMailingsSp.MailingID = mailigID;
var res = selectMailingsSp.Execute();
return(!res.HasNoDataRows ? res.ResultToArray <MailingDto>() : ArrayUtils.Empty <MailingDto>());
}
List <PdfSettingDto> GetPdfSettings(int?id = null)
{
var selectLettersSp = QueryProvider.CreateQuery <SelectPDFSettingsStoredProcedure>(_schema);
selectLettersSp.PDFSettingID = id;
var result = selectLettersSp.Execute();
return(!result.HasNoDataRows ? result.ResultToArray <PdfSettingDto>().ToList() : new List <PdfSettingDto>());
}
public CategoryDto[] GetCategoryTree()
{
var selectTransactionCategoryTreeSp =
QueryProvider.CreateQuery <SelectTransactionCategoryTreeStoredProcedure>(_schema);
var result = selectTransactionCategoryTreeSp.Execute();
return(result.ResultToArray <CategoryDto>());
}
public List <FamilyAddressDto> GetAddressesByFamilyId(int famId)
{
var query = QueryProvider.CreateQuery <SelectAddressStoredProcedure>(_schema);
query.FamilyId = famId;
var result = query.Execute();
return(result.DataRows != null?result.ResultToArray <FamilyAddressDto>().ToList() : null);
}
public IEnumerable <AppSettingRecord> GetSettings(short?settingID = 0, string setting = null)
{
var spGetSetting = QueryProvider.CreateQuery <SelectSettingsStoredProcedure>(_schema);
spGetSetting.Setting = setting;
spGetSetting.SettingID = settingID;
var res = spGetSetting.Execute();
return(!res.HasNoDataRows ? res.ResultToArray <AppSettingRecord>() : ArrayUtils.Empty <AppSettingRecord>());
}
public IQueryable <TResult> CreateScalarQuery <TResult>(Expression <Func <TResult> > expression)
{
return(QueryProvider.CreateQuery <TResult>(
Expression.Call(
method: GetMethodInfo(() => Queryable.Select <int, TResult>(null, (Expression <Func <int, TResult> >)null)),
arg0: Expression.Call(
method: GetMethodInfo(() => Queryable.AsQueryable <int>(null)),
arg0: Expression.NewArrayInit(typeof(int), Expression.Constant(1))),
arg1: Expression.Lambda(body: expression.Body, parameters: new[] { Expression.Parameter(typeof(int)) }))));
}
private IEnumerable <PaymentMethodDto> GetPaymentMethods(int?paymentMethodId)
{
var selectPaymentMethodsSp = QueryProvider.CreateQuery <SelectPaymentMethodStoredProcedure>(_schema);
selectPaymentMethodsSp.PaymentMethodID = paymentMethodId;
var result = selectPaymentMethodsSp.Execute();
var res = result.ResultToArray <PaymentMethodDto>();
return(result.DataRows != null?result.ResultToArray <PaymentMethodDto>() : null);
}
public bool CreateSetting(AppSettingRecord NewSetting)
{
var query = QueryProvider.CreateQuery <InsertSettingStoredProcedure>(_schema);
// applying parameters
query.SettingID = NewSetting.SettingID;
query.Setting = NewSetting.Setting;
query.Value = NewSetting.Value;
query.ParentID = NewSetting.ParentID;
return(!query.Execute().NonZeroReturnCode);
}
public PaymentDto GetAnyPayment()
{
var selectPaymentsSp = QueryProvider.CreateQuery <SelectPaymentsStoredProcedure>(_schema);
selectPaymentsSp.FirstOnly = true;
var result = selectPaymentsSp.Execute();
return(result.NonZeroReturnCode || result.HasNoDataRows
? new PaymentDto()
: result.ResultToArray <PaymentDto>().FirstOrDefault());
}
private IEnumerable <SolicitorDto> GetSolicitors(int?solicitorId)
{
var selectSolicitorsSp = QueryProvider.CreateQuery <SelectSolicitorsStoredProcedure>(_schema);
selectSolicitorsSp.SolicitorID = solicitorId;
var result = selectSolicitorsSp.Execute();
return(!result.HasNoDataRows
? result.ResultToArray <SolicitorDto>()
: ArrayUtils.Empty <SolicitorDto>());
}
protected override int ExecuteInternal()
{
Expression e = query.Expression.Visit((MethodCallExpression ex) =>
{
var methodInfo = ex.Method;
//rewrite localCollection.Contains(entity.SomeField) -> entity.SomeField.In(localCollection)
if (methodInfo.DeclaringType == typeof(Enumerable) &&
string.Equals(methodInfo.Name, "Contains", StringComparison.Ordinal) &&
ex.Arguments.Count == 2)
{
var localCollection = ex.Arguments[0];//IEnumerable<T>
var valueToCheck = ex.Arguments[1];
var genericInMethod = inMethod.MakeGenericMethod(new[] { valueToCheck.Type });
ex = Expression.Call(genericInMethod, valueToCheck, Expression.Constant(IncludeAlgorithm.ComplexCondition), localCollection);
methodInfo = ex.Method;
}
if (methodInfo.DeclaringType == typeof(QueryableExtensions) &&
string.Equals(methodInfo.Name, "In", StringComparison.Ordinal) &&
ex.Arguments.Count > 1)
{
if (ex.Arguments[1].Type == typeof(IncludeAlgorithm))
{
var algorithm = (IncludeAlgorithm)ex.Arguments[1].Invoke();
if (algorithm == IncludeAlgorithm.TemporaryTable)
{
throw new NotSupportedException("IncludeAlgorithm.TemporaryTable is not supported");
}
if (algorithm == IncludeAlgorithm.Auto)
{
List <Expression> arguments = ex.Arguments.ToList();
arguments[1] = Expression.Constant(IncludeAlgorithm.ComplexCondition);
ex = Expression.Call(methodInfo, arguments);
}
}
else
{
List <Expression> arguments = ex.Arguments.ToList();
arguments.Insert(1, Expression.Constant(IncludeAlgorithm.ComplexCondition));
List <Type> types = methodInfo.GetParameters().Select(a => a.ParameterType).ToList();
types.Insert(1, typeof(IncludeAlgorithm));
ex = Expression.Call(inMethod.MakeGenericMethod(methodInfo.GetGenericArguments()),
arguments.ToArray());
}
}
return(ex);
});
query = QueryProvider.CreateQuery <T>(e);
return(0);
}
public LetterFieldsDto GetLetterFields(int transactionId, int?[] subCategoryIds = null, bool includeSubCats = true, string currency = "$")
{
AppSettingRecord defClasses = _settingsProvider.GetSetting(Settings.DefaultClasses);
var select = QueryProvider.CreateQuery <SelectFieldsForLetterStoredProcedure>(_schema);
select.TransactionId = transactionId;
select.ClassesOn = Convert.ToBoolean(defClasses.Value);
select.SubCategoryIds = subCategoryIds;
select.SubCatInclude = includeSubCats;
LetterFieldsDto fields = select.Execute().FirstRow <LetterFieldsDto>();
ParseXMLFields(ref fields);
MakeFieldsInRegionalStandarts(ref fields, currency);
return(fields);
}
public LetterDto GetLetterById(short id)
{
var selectLetterById = QueryProvider.CreateQuery <SelectLetterByIdStoredProcedure>(_schema);
selectLetterById.LetterId = id;
var result = selectLetterById.Execute();
if (result.HasNoDataRows)
{
return(null);
}
var resp = result.FirstRow <LetterDto>();
if (resp.PDFSettingID != null)
{
resp.PdfSettings = GetPdfSetting(resp.PDFSettingID.Value);
}
return(resp);
}
private IEnumerable <DepartmentDto> GetDepartments(int?departmentId = null, bool?isprimary = null)
{
var selectDepartmentSp = QueryProvider.CreateQuery <SelectDepartmentsStoredProcedure>(_schema);
selectDepartmentSp.DepartmentId = departmentId;
selectDepartmentSp.IsPrimary = isprimary;
var result = selectDepartmentSp.Execute();
if (result.HasNoDataRows)
{
return(ArrayUtils.Empty <DepartmentDto>());
}
var resp = result.ResultToArray <DepartmentDto>();
foreach (DepartmentDto departmentDto in resp)
{
departmentDto.PdfSettings = _letterService.GetPdfSetting(departmentDto.PDFSettingID);
}
return(resp);
}
protected override int ExecuteInternal()
{
Expression e = query.Expression.Visit((MethodCallExpression ex) =>
{
if (ex.Method.DeclaringType == typeof(QueryableExtensions) && ex.Method.Name == "In" &&
ex.Arguments.Count > 1)
{
if (ex.Arguments[1].Type == typeof(IncludeAlgorithm))
{
var v = (IncludeAlgorithm)ex.Arguments[1].Invoke();
if (v == IncludeAlgorithm.TemporaryTable)
{
throw new NotSupportedException("IncludeAlgorithm.TemporaryTable is not supported");
}
if (v == IncludeAlgorithm.Auto)
{
List <Expression> arguments = ex.Arguments.ToList();
arguments[1] = Expression.Constant(IncludeAlgorithm.ComplexCondition);
ex = Expression.Call(ex.Method, arguments);
}
}
else
{
List <Expression> arguments = ex.Arguments.ToList();
arguments.Insert(1, Expression.Constant(IncludeAlgorithm.ComplexCondition));
List <Type> types = ex.Method.GetParameters().Select(a => a.ParameterType).ToList();
types.Insert(1, typeof(IncludeAlgorithm));
ex = Expression.Call(inMethod.MakeGenericMethod(ex.Method.GetGenericArguments()),
arguments.ToArray());
}
}
return(ex);
});
query = QueryProvider.CreateQuery <T>(e);
return(0);
}
/// <summary>
/// Creates <see cref="IQueryable{T}"/> instance from query expression.
/// </summary>
/// <typeparam name="TElement">Query element type.</typeparam>
/// <param name="expression">Query expression.</param>
/// <returns><see cref="IQueryable{T}"/> instance.</returns>
public IQueryable <TElement> CreateQuery <TElement>(Expression expression)
{
return(QueryProvider.CreateQuery <TElement>(expression));
}
/// <summary>
/// Creates <see cref="IQueryable"/> instance from query expression.
/// </summary>
/// <param name="expression">Query expression.</param>
/// <returns><see cref="IQueryable"/> instance.</returns>
public IQueryable CreateQuery(Expression expression)
{
return(QueryProvider.CreateQuery(expression));
}
/// <summary>
/// 'S' is the projected type. If you say 'from e in Employees select e.ID', then type S will be int.
/// If you say 'select new {e.ID}', then type S will be something like Projection.f__1
/// </summary>
IQueryable <S> IQueryProvider.CreateQuery <S>(Expression expr)
{
return(_queryProvider.CreateQuery <S>(expr));
}
