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
}));
}
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());
}
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
}));
}
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());
}
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
}));
}
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());
}
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
}));
}
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());
}
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
}));
}
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
}));
}
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
});
}
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());
}
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
}));
}
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());
}
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());
}
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();
}
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());
}
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
}));
}
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
});
}
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());
}
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());
}
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
}));
}
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());
}
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());
}
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());
}
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
}));
}
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
}));
}
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
}));
}
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
}));
}
