示例#1
0
        public int Count(ListQuery listQuery, object query = null)
        {
            if (listQuery == null)
            {
                throw new ArgumentNullException(nameof(listQuery));
            }

            var organizationId = query?.Val <int?>("OrganizationId");

            if (!organizationId.HasValue)
            {
                throw new ArgumentNullException(nameof(organizationId));
            }

            var pre       = "OrganizationId = @organizationId";
            var condition = listQuery.Like(pre, "g.DisplayName");

            return(UnitOfWork.Session.ExecuteScalar <int>($@"
SELECT COUNT(*)
FROM LoanPercentSettings l
JOIN Groups g ON l.BranchId = g.Id
{condition}", new
            {
                organizationId,
                listQuery.Filter
            }));
        }
示例#2
0
        public List <Company> List(ListQuery listQuery, object query = null)
        {
            if (listQuery == null)
            {
                throw new ArgumentNullException(nameof(listQuery));
            }

            var condition = listQuery.Like("Clients.DeleteDate IS NULL", "Fullname", "IdentityNumber", "DocumentNumber", "ContractNumber");
            var order     = listQuery.Order(string.Empty, new Sort
            {
                Name      = "Fullname",
                Direction = SortDirection.Asc
            });
            var page = listQuery.Page();

            return(UnitOfWork.Session.Query <Company>($@"
SELECT DISTINCT Companies.*, Clients.*
FROM Companies
JOIN Clients ON Clients.Id = Companies.Id
LEFT JOIN Contracts ON Clients.Id = Contracts.ClientId
{condition} {order} {page}", new
            {
                listQuery.Page?.Offset,
                listQuery.Page?.Limit,
                listQuery.Filter
            }).ToList());
        }
示例#3
0
        public int Count(ListQuery listQuery, object query = null)
        {
            if (listQuery == null)
            {
                throw new ArgumentNullException(nameof(listQuery));
            }

            var branchId = query?.Val <int?>("BranchId");
            var userId   = query?.Val <int?>("UserId");

            var pre = "n.DeleteDate IS NULL";

            if (branchId.HasValue)
            {
                pre += " AND (n.ReceiveBranchId = @branchId OR n.ReceiveUserId = @userId)";
            }

            var condition = listQuery.Like(pre);
            var page      = listQuery.Page();

            return(UnitOfWork.Session.ExecuteScalar <int>($@"
SELECT COUNT(*)
FROM InnerNotifications n
WHERE {pre}",
                                                          new
            {
                branchId,
                userId
            }));
        }
示例#4
0
        public List <Position> List(ListQuery listQuery, object query = null)
        {
            if (listQuery == null)
            {
                throw new ArgumentNullException(nameof(listQuery));
            }

            var pre       = "CollateralType = @collateralType";
            var condition = listQuery.Like(pre, "Name");
            var order     = listQuery.Order(string.Empty, new Sort
            {
                Name      = "Name",
                Direction = SortDirection.Asc
            });
            var page = listQuery.Page();

            return(UnitOfWork.Session.Query <Position>($@"
SELECT *
  FROM Positions
{condition} {order} {page}", new
            {
                collateralType = (short)CollateralType.Gold,
                listQuery.Page?.Offset,
                listQuery.Page?.Limit,
                listQuery.Filter
            }).ToList());
        }
示例#5
0
        public int Count(ListQuery listQuery, object query = null)
        {
            if (listQuery == null)
            {
                throw new ArgumentNullException(nameof(listQuery));
            }

            var pre            = "et.DeleteDate IS NULL AND eg.DeleteDate IS NULL";
            var expenseGroupId = query?.Val <int?>("ExpenseGroupId");

            if (expenseGroupId.HasValue)
            {
                pre += " AND et.ExpenseGroupId = @expenseGroupId";
            }

            var condition = listQuery.Like(pre, "et.Name");

            return(UnitOfWork.Session.ExecuteScalar <int>($@"
SELECT COUNT(*)
FROM ExpenseTypes et
JOIN ExpenseGroups eg ON et.ExpenseGroupId = eg.Id
{condition}", new
            {
                expenseGroupId,
                listQuery.Filter
            }));
        }
示例#6
0
        public List <Purity> List(ListQuery listQuery, object query = null)
        {
            if (listQuery == null)
            {
                throw new ArgumentNullException(nameof(listQuery));
            }

            var condition = listQuery.Like(string.Empty, "Name");
            var order     = listQuery.Order(string.Empty, new Sort
            {
                Name      = "Name",
                Direction = SortDirection.Asc
            });
            var page = listQuery.Page();

            return(UnitOfWork.Session.Query <Purity>($@"
SELECT *
FROM Purities
{condition} {order} {page}", new
            {
                listQuery.Page?.Offset,
                listQuery.Page?.Limit,
                listQuery.Filter
            }).ToList());
        }
示例#7
0
        public int Count(ListQuery listQuery, object query = null)
        {
            if (listQuery == null)
            {
                throw new ArgumentNullException(nameof(listQuery));
            }

            var branchId = query?.Val <int?>("BranchId");
            var disposal = query?.Val <bool?>("Disposal");

            if (!branchId.HasValue)
            {
                throw new ArgumentNullException(nameof(branchId));
            }

            var pre = "a.DeleteDate IS NULL";

            pre += branchId.HasValue ? " AND a.BranchId = @branchId" : string.Empty;
            pre += disposal.HasValue && disposal.Value ? " AND a.DisposalDate IS NOT NULL" : string.Empty;

            var condition = listQuery.Like(pre, "a.Number", "a.Name");

            return(UnitOfWork.Session.ExecuteScalar <int>($@"
SELECT a.*, m.*, b.*, u.*
FROM Assets a
JOIN Users m ON a.ManagerId = m.Id
JOIN Groups b ON a.BranchId = b.Id
JOIN Users u ON a.UserId = u.Id
{condition}", new
            {
                branchId,
                listQuery.Filter
            }));
        }
示例#8
0
        public List <ExpenseGroup> List(ListQuery listQuery, object query = null)
        {
            if (listQuery == null)
            {
                throw new ArgumentNullException(nameof(listQuery));
            }

            var pre       = "DeleteDate IS NULL";
            var condition = listQuery.Like(pre, "Name");
            var order     = listQuery.Order(string.Empty, new Sort
            {
                Name      = "OrderBy",
                Direction = SortDirection.Asc
            });
            var page = listQuery.Page();

            return(UnitOfWork.Session.Query <ExpenseGroup>($@"
SELECT *
FROM ExpenseGroups
{condition} {order} {page}", new
            {
                listQuery.Page?.Offset,
                listQuery.Page?.Limit,
                listQuery.Filter
            }).ToList());
        }
示例#9
0
        public int Count(ListQuery listQuery, object query = null)
        {
            if (listQuery == null)
            {
                throw new ArgumentNullException(nameof(listQuery));
            }

            var organizationId = query.Val <int?>("OrganizationId");
            var locked         = query.Val <bool?>("Locked");

            var condition = listQuery.Like(
                BuildCondition(organizationId, locked),
                "Fullname", "Login");

            return(UnitOfWork.Session.ExecuteScalar <int>($@"
SELECT COUNT(*)
  FROM Users
  JOIN Members ON Members.Id = Users.Id
{condition}", new
            {
                organizationId,
                locked,
                listQuery.Filter
            }));
        }
示例#10
0
        public int Count(ListQuery listQuery, object query = null)
        {
            if (listQuery == null)
            {
                throw new ArgumentNullException(nameof(listQuery));
            }

            var beginDate = query?.Val <DateTime?>("BeginDate");
            var endDate   = query?.Val <DateTime?>("EndDate");
            var ownerId   = query?.Val <int>("OwnerId");

            var pre = "i.DeleteDate IS NULL";

            pre += beginDate.HasValue ? " AND DATEFROMPARTS(YEAR(dbo.GETASTANADATE()), MONTH(dbo.GETASTANADATE()), i.RepaymentDay) >= @beginDate" : string.Empty;
            pre += endDate.HasValue ? " AND DATEFROMPARTS(YEAR(dbo.GETASTANADATE()), MONTH(dbo.GETASTANADATE()), i.RepaymentDay) <= @endDate" : string.Empty;
            pre += " AND mr.LeftMemberId = @ownerId";

            var condition = listQuery.Like(pre, "c.Fullname");

            return(UnitOfWork.Session.ExecuteScalar <int>($@"
SELECT COUNT(*)
FROM Investments i
JOIN MemberRelations mr ON mr.RightMemberId = i.OwnerId
JOIN Clients c ON i.ClientId = c.Id
JOIN Groups g ON i.BranchId = g.Id
JOIN Users u ON i.AuthorId = u.Id
{condition}", new {
                beginDate,
                endDate,
                ownerId,
                listQuery.Filter
            }));
        }
示例#11
0
        public int Count(ListQuery listQuery, object query = null)
        {
            if (listQuery == null) throw new ArgumentNullException(nameof(listQuery));

            var beginDate = query?.Val<DateTime?>("BeginDate");
            var endDate = query?.Val<DateTime?>("EndDate");
            var collateralType = query?.Val<CollateralType?>("CollateralType");
            var status = query?.Val<SellingStatus?>("Status");
            var ownerId = query?.Val<int>("OwnerId");

            var pre = "Sellings.DeleteDate IS NULL";
            pre += beginDate.HasValue ? " AND Sellings.CreateDate >= @beginDate" : string.Empty;
            pre += endDate.HasValue ? " AND Sellings.CreateDate <= @endDate" : string.Empty;
            pre += collateralType.HasValue ? " AND Sellings.CollateralType = @collateralType" : string.Empty;
            pre += status.HasValue ? " AND Sellings.Status = @status" : string.Empty;
            pre += " AND MemberRelations.LeftMemberId = @ownerId";

            var condition = listQuery.Like(pre, "Positions.Name");

            return UnitOfWork.Session.ExecuteScalar<int>($@"
SELECT COUNT(DISTINCT Sellings.Id)
FROM Sellings
JOIN MemberRelations ON MemberRelations.RightMemberId = Sellings.OwnerId
JOIN Positions ON Sellings.PositionId = Positions.Id
{condition}", new
            {
                beginDate,
                endDate,
                collateralType,
                status,
                ownerId,
                listQuery.Filter
            });
        }
示例#12
0
        public List <Category> List(ListQuery listQuery, object query = null)
        {
            if (listQuery == null)
            {
                throw new ArgumentNullException(nameof(listQuery));
            }

            var collateralType = query?.Val <CollateralType?>("CollateralType");
            var pre            = collateralType.HasValue ? "CollateralType = @collateralType" : string.Empty;

            var condition = listQuery.Like(pre, "Name");
            var order     = listQuery.Order(string.Empty, new Sort
            {
                Name      = "Name",
                Direction = SortDirection.Asc
            });
            var page = listQuery.Page();

            return(UnitOfWork.Session.Query <Category>($@"
SELECT *
  FROM Categories
{condition} {order} {page}", new
            {
                collateralType,
                listQuery.Page?.Offset,
                listQuery.Page?.Limit,
                listQuery.Filter
            }).ToList());
        }
示例#13
0
        public int Count(ListQuery listQuery, object query = null)
        {
            if (listQuery == null)
            {
                throw new ArgumentNullException(nameof(listQuery));
            }

            var contractId = query?.Val <int?>("ContractId");
            var clientId   = query?.Val <int?>("ClientId");

            if (!contractId.HasValue && !clientId.HasValue)
            {
                throw new ArgumentNullException($"{nameof(contractId)}, {nameof(clientId)}");
            }

            var pre = contractId.HasValue
                ? "ContractNotes.ContractId = @contractId"
                : "Contracts.ClientId = @clientId";

            var condition = listQuery.Like(pre, "ContractNotes.Note");

            return(UnitOfWork.Session.ExecuteScalar <int>($@"
SELECT COUNT(*)
FROM ContractNotes
JOIN Contracts ON ContractNotes.ContractId = Contracts.Id
{condition}", new
            {
                contractId,
                clientId,
                listQuery.Filter
            }));
        }
示例#14
0
        public List <User> List(ListQuery listQuery, object query = null)
        {
            if (listQuery == null)
            {
                throw new ArgumentNullException(nameof(listQuery));
            }

            var organizationId = query.Val <int?>("OrganizationId");
            var locked         = query.Val <bool?>("Locked");

            var condition = listQuery.Like(
                BuildCondition(organizationId, locked),
                "Fullname", "Login");
            var order = listQuery.Order(string.Empty, new Sort
            {
                Name      = "Fullname",
                Direction = SortDirection.Asc
            });
            var page = listQuery.Page();

            return(UnitOfWork.Session.Query <User>($@"
SELECT *
  FROM Users
  JOIN Members ON Members.Id = Users.Id
{condition} {order} {page}", new
            {
                organizationId,
                locked,
                listQuery.Page?.Offset,
                listQuery.Page?.Limit,
                listQuery.Filter
            }).ToList());
        }
示例#15
0
        public List <Client> List(ListQuery listQuery, object query = null)
        {
            if (listQuery == null)
            {
                throw new ArgumentNullException(nameof(listQuery));
            }

            var condition = listQuery.Like("DeleteDate IS NULL", "Fullname", "IdentityNumber");
            var order     = listQuery.Order(string.Empty, new Sort
            {
                Name      = "Fullname",
                Direction = SortDirection.Asc
            });
            var page = listQuery.Page();

            return(UnitOfWork.Session.Query <Client>($@"
SELECT *
  FROM Clients
{condition} {order} {page}", new
            {
                listQuery.Page?.Offset,
                listQuery.Page?.Limit,
                listQuery.Filter
            }).ToList());
        }
        public List <RemittanceSetting> List(ListQuery listQuery, object query = null)
        {
            if (listQuery == null)
            {
                throw new ArgumentNullException(nameof(listQuery));
            }

            var condition = listQuery.Like(string.Empty, "sb.DisplayName");
            var order     = "ORDER BY sb.DisplayName, rb.DisplayName";
            var page      = listQuery.Page();

            return(UnitOfWork.Session.Query <RemittanceSetting, Group, Group, Account, Account, Account, Account, RemittanceSetting>($@"
SELECT rs.*, sb.*, rb.*, cod.*, coc.*, cid.*, cic.*
FROM RemittanceSettings rs
JOIN Groups sb ON rs.SendBranchId = sb.Id
JOIN Groups rb ON rs.ReceiveBranchId = rb.Id
JOIN Accounts cod ON rs.CashOutDebitId = cod.Id
JOIN Accounts coc ON rs.CashOutCreditId = coc.Id
JOIN Accounts cid ON rs.CashInDebitId = cid.Id
JOIN Accounts cic ON rs.CashInCreditId = cic.Id
{condition} {order} {page}", (rs, sb, rb, cod, coc, cid, cic) => {
                rs.SendBranch = sb;
                rs.ReceiveBranch = rb;
                rs.CashOutDebit = cod;
                rs.CashOutCredit = coc;
                rs.CashInDebit = cid;
                rs.CashInCredit = cic;
                return rs;
            }, new
            {
                listQuery.Page?.Offset,
                listQuery.Page?.Limit,
                listQuery.Filter
            }).ToList());
        }
示例#17
0
        public List<Selling> List(ListQuery listQuery, object query = null)
        {
            if (listQuery == null) throw new ArgumentNullException(nameof(listQuery));

            var beginDate = query?.Val<DateTime?>("BeginDate");
            var endDate = query?.Val<DateTime?>("EndDate");
            var collateralType = query?.Val<CollateralType?>("CollateralType");
            var status = query?.Val<SellingStatus?>("Status");
            var ownerId = query?.Val<int>("OwnerId");

            var pre = "Sellings.DeleteDate IS NULL";
            pre += beginDate.HasValue ? " AND Sellings.CreateDate >= @beginDate" : string.Empty;
            pre += endDate.HasValue ? " AND Sellings.CreateDate <= @endDate" : string.Empty;
            pre += collateralType.HasValue ? " AND Sellings.CollateralType = @collateralType" : string.Empty;
            pre += status.HasValue ? " AND Sellings.Status = @status" : string.Empty;
            pre += " AND MemberRelations.LeftMemberId = @ownerId";

            var condition = listQuery.Like(pre, "Positions.Name");
            var order = listQuery.Order(string.Empty, new Sort
            {
                Name = "Sellings.CreateDate",
                Direction = SortDirection.Desc
            });
            var page = listQuery.Page();

            return UnitOfWork.Session.Query<Selling, Position, Group, User, Selling>($@"
SELECT Sellings.*, Positions.*, Groups.*, Users.* FROM (
SELECT DISTINCT Sellings.*
FROM Sellings
JOIN Positions ON Sellings.PositionId = Positions.Id
JOIN MemberRelations ON MemberRelations.RightMemberId = Sellings.OwnerId
{condition}
) as Sellings
JOIN Positions ON Sellings.PositionId = Positions.Id
JOIN Groups ON Sellings.BranchId = Groups.Id
JOIN Users ON Sellings.AuthorId = Users.Id
{order} {page}",
                (s, p, g, u) =>
                {
                    s.Position = p;
                    s.Branch = g;
                    s.Author = u;
                    return s;
                },
                new
                {
                    beginDate,
                    endDate,
                    collateralType,
                    status,
                    ownerId,
                    listQuery.Page?.Offset,
                    listQuery.Page?.Limit,
                    listQuery.Filter
                }).ToList();
        }
示例#18
0
        public List <Notification> List(ListQuery listQuery, object query = null)
        {
            if (listQuery == null)
            {
                throw new ArgumentNullException(nameof(listQuery));
            }

            var branchId    = query?.Val <int>("BranchId");
            var beginDate   = query?.Val <DateTime?>("BeginDate");
            var endDate     = query?.Val <DateTime?>("EndDate");
            var messageType = query?.Val <MessageType?>("MessageType");
            var status      = query?.Val <NotificationStatus?>("Status");

            var pre = "n.BranchId = @branchId AND n.DeleteDate IS NULL";

            if (beginDate.HasValue)
            {
                pre += " AND n.CreateDate >= @beginDate";
            }
            if (endDate.HasValue)
            {
                pre += " AND n.CreateDate <= @endDate";
            }
            if (messageType.HasValue)
            {
                pre += " AND n.MessageType = @messageType";
            }
            if (status.HasValue)
            {
                pre += " AND n.Status = @status";
            }

            var condition = listQuery.Like(pre, "n.Message");
            var order     = listQuery.Order(string.Empty, new Sort
            {
                Name      = "n.CreateDate",
                Direction = SortDirection.Desc
            });
            var page = listQuery.Page();

            return(UnitOfWork.Session.Query <Notification>($@"
SELECT n.*
FROM Notifications n
{condition} {order} {page}",
                                                           new
            {
                branchId,
                beginDate,
                endDate,
                messageType,
                status,
                listQuery.Page?.Offset,
                listQuery.Page?.Limit,
                listQuery.Filter
            }).ToList());
        }
示例#19
0
        public int Count(ListQuery listQuery, object query = null)
        {
            if (listQuery == null)
            {
                throw new ArgumentNullException(nameof(listQuery));
            }

            var orderType   = query?.Val <OrderType?>("OrderType");
            var beginDate   = query?.Val <DateTime?>("BeginDate");
            var endDate     = query?.Val <DateTime?>("EndDate");
            var clientId    = query?.Val <int?>("ClientId");
            var userId      = query?.Val <int?>("UserId");
            var accountId   = query?.Val <int?>("AccountId");
            var orderNumber = query?.Val <int?>("OrderNumber");
            var isDelete    = query?.Val <bool?>("IsDelete");
            var ownerId     = query?.Val <int>("OwnerId");
            var isApproved  = query?.Val <bool?>("IsApproved");

            var pre = isDelete.HasValue && isDelete.Value ? "co.DeleteDate IS NOT NULL" : "co.DeleteDate IS NULL";

            pre += orderType.HasValue ? " AND co.OrderType = @orderType" : string.Empty;
            pre += beginDate.HasValue ? " AND co.OrderDate >= @beginDate" : string.Empty;
            pre += endDate.HasValue ? " AND co.OrderDate <= @endDate" : string.Empty;
            pre += clientId.HasValue ? " AND co.ClientId = @clientId" : string.Empty;
            pre += userId.HasValue ? " AND co.UserId = @userId" : string.Empty;
            pre += accountId.HasValue
                ? " AND (co.DebitAccountId = @accountId OR co.CreditAccountId = @accountId)"
                : string.Empty;
            pre += orderNumber.HasValue ? " AND co.OrderNumber = @orderNumber" : string.Empty;
            pre += " AND (@ownerId = -1 OR co.OwnerId = @ownerId)";
            pre += isApproved.HasValue && isApproved.Value ? " AND ApproveStatus!=10" : " AND ApproveStatus=10";


            var condition = listQuery.Like(pre, "co.OrderNumber", "c.Fullname", "u.Fullname");

            return(UnitOfWork.Session.ExecuteScalar <int>($@"
SELECT COUNT(DISTINCT co.Id)
FROM CashOrders co
LEFT JOIN MemberRelations mr ON mr.RightMemberId = co.OwnerId
LEFT JOIN Clients c ON co.ClientId = c.Id
LEFT JOIN Users u ON co.UserId = u.Id
{condition}", new
            {
                orderType,
                beginDate,
                endDate,
                clientId,
                userId,
                orderNumber,
                accountId,
                ownerId,
                listQuery.Filter
            }));
        }
示例#20
0
        public int Count(ListQuery listQuery, object query = null)
        {
            if (listQuery == null) throw new ArgumentNullException(nameof(listQuery));

            var condition = listQuery.Like(string.Empty, "Name");

            return UnitOfWork.Session.ExecuteScalar<int>($@"
SELECT COUNT(*)
  FROM Roles
{condition}", new
            {
                listQuery.Filter
            });
        }
示例#21
0
        public List <Insurance> List(ListQuery listQuery, object query = null)
        {
            if (listQuery == null)
            {
                throw new ArgumentNullException(nameof(listQuery));
            }

            var beginDate = query?.Val <DateTime?>("BeginDate");
            var endDate   = query?.Val <DateTime?>("EndDate");
            var status    = query?.Val <InsuranceStatus?>("Status");
            var ownerId   = query?.Val <int>("OwnerId");

            var pre = "i.DeleteDate IS NULL";

            pre += beginDate.HasValue ? " AND i.BeginDate >= @beginDate" : string.Empty;
            pre += endDate.HasValue ? " AND i.BeginDate <= @endDate" : string.Empty;
            pre += status.HasValue ? " AND i.Status = @status" : string.Empty;
            pre += " AND mr.LeftMemberId = @ownerId";

            var condition = listQuery.Like(pre, "i.InsuranceNumber", "c.ContractNumber", "JSON_VALUE(c.ContractData, '$.Client.Fullname')", "JSON_VALUE(c.ContractData, '$.Client.IdentityNumber')");
            var order     = listQuery.Order(string.Empty, new Sort
            {
                Name      = "i.BeginDate",
                Direction = SortDirection.Desc
            });
            var page = listQuery.Page();

            return(UnitOfWork.Session.Query <Insurance, Contract, Group, Insurance>($@"
SELECT i.*, c.*, b.*
FROM Insurances i
JOIN MemberRelations mr ON mr.RightMemberId = i.OwnerId
JOIN Contracts c ON i.ContractId = c.Id
JOIN Groups b ON i.BranchId = b.Id
{condition} {order} {page}", (i, c, b) =>
            {
                i.Contract = c;
                i.Branch = b;
                return i;
            }, new
            {
                beginDate,
                endDate,
                status,
                ownerId,
                listQuery.Page?.Offset,
                listQuery.Page?.Limit,
                listQuery.Filter
            }).ToList());
        }
示例#22
0
        public List <Investment> List(ListQuery listQuery, object query = null)
        {
            if (listQuery == null)
            {
                throw new ArgumentNullException(nameof(listQuery));
            }

            var beginDate = query?.Val <DateTime?>("BeginDate");
            var endDate   = query?.Val <DateTime?>("EndDate");
            var ownerId   = query?.Val <int>("OwnerId");

            var pre = "i.DeleteDate IS NULL";

            pre += beginDate.HasValue ? " AND DATEFROMPARTS(YEAR(dbo.GETASTANADATE()), MONTH(dbo.GETASTANADATE()), i.RepaymentDay) >= @beginDate" : string.Empty;
            pre += endDate.HasValue ? " AND DATEFROMPARTS(YEAR(dbo.GETASTANADATE()), MONTH(dbo.GETASTANADATE()), i.RepaymentDay) <= @endDate" : string.Empty;
            pre += " AND mr.LeftMemberId = @ownerId";

            var condition = listQuery.Like(pre, "c.Fullname");
            var order     = listQuery.Order(string.Empty, new Sort
            {
                Name      = "c.Fullname",
                Direction = SortDirection.Desc
            });
            var page = listQuery.Page();

            return(UnitOfWork.Session.Query <Investment, Client, Group, User, Investment>($@"
SELECT i.*, c.*, g.*, u.*
FROM Investments i
JOIN MemberRelations mr ON mr.RightMemberId = i.OwnerId
JOIN Clients c ON i.ClientId = c.Id
JOIN Groups g ON i.BranchId = g.Id
JOIN Users u ON i.AuthorId = u.Id
{condition} {order} {page}", (i, c, g, u) =>
            {
                i.Client = c;
                i.Branch = g;
                i.Author = u;
                return i;
            }, new
            {
                beginDate,
                endDate,
                ownerId,
                listQuery.Page?.Offset,
                listQuery.Page?.Limit,
                listQuery.Filter
            }).ToList());
        }
示例#23
0
        public int Count(ListQuery listQuery, object query = null)
        {
            if (listQuery == null)
            {
                throw new ArgumentNullException(nameof(listQuery));
            }

            var branchId    = query?.Val <int>("BranchId");
            var beginDate   = query?.Val <DateTime?>("BeginDate");
            var endDate     = query?.Val <DateTime?>("EndDate");
            var messageType = query?.Val <MessageType?>("MessageType");
            var status      = query?.Val <NotificationStatus?>("Status");

            var pre = "n.BranchId = @branchId AND n.DeleteDate IS NULL";

            if (beginDate.HasValue)
            {
                pre += " AND n.CreateDate >= @beginDate";
            }
            if (endDate.HasValue)
            {
                pre += " AND n.CreateDate <= @endDate";
            }
            if (messageType.HasValue)
            {
                pre += " AND n.MessageType = @messageType";
            }
            if (status.HasValue)
            {
                pre += " AND n.Status = @status";
            }

            var condition = listQuery.Like(pre, "n.Message");

            return(UnitOfWork.Session.ExecuteScalar <int>($@"
SELECT COUNT(*)
FROM Notifications n
{condition}",
                                                          new
            {
                branchId,
                beginDate,
                endDate,
                messageType,
                status,
                listQuery.Filter
            }));
        }
示例#24
0
        public List <ContractNote> List(ListQuery listQuery, object query = null)
        {
            if (listQuery == null)
            {
                throw new ArgumentNullException(nameof(listQuery));
            }

            var contractId = query?.Val <int?>("ContractId");
            var clientId   = query?.Val <int?>("ClientId");

            if (!contractId.HasValue && !clientId.HasValue)
            {
                throw new ArgumentNullException($"{nameof(contractId)}, {nameof(clientId)}");
            }

            var pre = contractId.HasValue
                ? "ContractNotes.ContractId = @contractId"
                : "Contracts.ClientId = @clientId";

            var condition = listQuery.Like(pre, "ContractNotes.Note");

            var order = listQuery.Order(string.Empty, new Sort
            {
                Name      = "ContractNotes.NoteDate",
                Direction = SortDirection.Asc
            });
            var page = listQuery.Page();

            return(UnitOfWork.Session.Query <ContractNote, User, ContractNote>($@"
SELECT ContractNotes.*, Users.*
FROM ContractNotes
JOIN Contracts ON ContractNotes.ContractId = Contracts.Id
JOIN Users ON ContractNotes.AuthorId = Users.Id
{condition} {order} {page}",
                                                                               (cn, u) =>
            {
                cn.Author = u;
                return cn;
            },
                                                                               new
            {
                contractId,
                clientId,
                listQuery.Page?.Offset,
                listQuery.Page?.Limit,
                listQuery.Filter
            }).ToList());
        }
示例#25
0
        public List <EventLogItem> List(ListQuery listQuery, object query = null)
        {
            if (listQuery == null)
            {
                throw new ArgumentNullException(nameof(listQuery));
            }

            var branchId   = query?.Val <int?>("BranchId");
            var eventCode  = query?.Val <EventCode?>("EventCode");
            var beginDate  = query?.Val <DateTime?>("BeginDate");
            var endDate    = query?.Val <DateTime?>("EndDate");
            var entityType = query?.Val <EntityType?>("EntityType");
            var entityId   = query?.Val <int?>("EntityId");

            var pre = "Id <> 0";

            pre += branchId.HasValue ? " AND BranchId = @branchId" : string.Empty;
            pre += eventCode.HasValue ? " AND EventCode = @eventCode" : string.Empty;
            pre += beginDate.HasValue ? " AND CreateDate >= @beginDate" : string.Empty;
            pre += endDate.HasValue ? " AND CreateDate <= @endDate" : string.Empty;
            pre += entityType.HasValue ? " AND EntityType = @entityType" : string.Empty;
            pre += entityId.HasValue ? " AND EntityId = @entityId" : string.Empty;

            var condition = listQuery.Like(pre, "EventCode", "UserName", "BranchName");
            var order     = listQuery.Order(string.Empty, new Sort
            {
                Name      = "CreateDate",
                Direction = SortDirection.Desc
            });
            var page = listQuery.Page();

            return(UnitOfWork.Session.Query <EventLogItem>($@"
SELECT *
FROM EventLogItems
{condition} {order} {page}", new
            {
                branchId,
                eventCode,
                beginDate,
                endDate,
                entityType,
                entityId,
                listQuery.Page?.Offset,
                listQuery.Page?.Limit,
                listQuery.Filter
            }).ToList());
        }
示例#26
0
        public List <Asset> List(ListQuery listQuery, object query = null)
        {
            if (listQuery == null)
            {
                throw new ArgumentNullException(nameof(listQuery));
            }

            var branchId = query?.Val <int?>("BranchId");
            var disposal = query?.Val <bool?>("Disposal");

            if (!branchId.HasValue)
            {
                throw new ArgumentNullException(nameof(branchId));
            }

            var pre = "a.DeleteDate IS NULL";

            pre += branchId.HasValue ? " AND a.BranchId = @branchId" : string.Empty;
            pre += disposal.HasValue && disposal.Value ? string.Empty : " AND a.DisposalDate IS NULL";

            var condition = listQuery.Like(pre, "a.Number", "a.Name");
            var order     = listQuery.Order(string.Empty, new Sort
            {
                Name      = "a.Name",
                Direction = SortDirection.Asc
            });
            var page = listQuery.Page();

            return(UnitOfWork.Session.Query <Asset, User, Group, User, Asset>($@"
SELECT a.*, m.*, b.*, u.*
FROM Assets a
JOIN Users m ON a.ManagerId = m.Id
JOIN Groups b ON a.BranchId = b.Id
JOIN Users u ON a.UserId = u.Id
{condition} {order} {page}", (a, m, b, u) => {
                a.Manager = m;
                a.Branch = b;
                a.User = u;
                return a;
            }, new {
                branchId,
                listQuery.Page?.Offset,
                listQuery.Page?.Limit,
                listQuery.Filter
            }).ToList());
        }
示例#27
0
        public int Count(ListQuery listQuery, object query = null)
        {
            if (listQuery == null)
            {
                throw new ArgumentNullException(nameof(listQuery));
            }

            var condition = listQuery.Like("DeleteDate IS NULL", "Fullname", "IdentityNumber");

            return(UnitOfWork.Session.ExecuteScalar <int>($@"
SELECT COUNT(*)
  FROM Clients
{condition}", new
            {
                listQuery.Filter
            }));
        }
示例#28
0
        public int Count(ListQuery listQuery, object query = null)
        {
            if (listQuery == null)
            {
                throw new ArgumentNullException(nameof(listQuery));
            }

            var pre       = "DeleteDate IS NULL";
            var condition = listQuery.Like(pre, "Name");

            return(UnitOfWork.Session.ExecuteScalar <int>($@"
SELECT COUNT(*)
FROM ExpenseGroups
{condition}", new
            {
                listQuery.Filter
            }));
        }
示例#29
0
        public int Count(ListQuery listQuery, object query = null)
        {
            if (listQuery == null)
            {
                throw new ArgumentNullException(nameof(listQuery));
            }

            var condition = listQuery.Like(string.Empty, "Name", "Mark", "Model");

            return(UnitOfWork.Session.ExecuteScalar <int>($@"
SELECT COUNT(*)
FROM Machineries
JOIN Positions ON Positions.Id = Machineries.Id
{condition}", new
            {
                listQuery.Filter
            }));
        }
示例#30
0
        public int Count(ListQuery listQuery, object query = null)
        {
            if (listQuery == null)
            {
                throw new ArgumentNullException(nameof(listQuery));
            }

            var pre       = "CollateralType = @collateralType";
            var condition = listQuery.Like(pre, "Name");

            return(UnitOfWork.Session.ExecuteScalar <int>($@"
SELECT COUNT(*)
  FROM Positions
{condition}", new
            {
                collateralType = (short)CollateralType.Gold,
                listQuery.Filter
            }));
        }