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 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 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 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 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 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 <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 <Remittance> List(ListQuery listQuery, object query = null)
{
if (listQuery == null)
{
throw new ArgumentNullException(nameof(listQuery));
}
var incoming = query?.Val <bool?>("Incoming");
var branchId = query?.Val <int?>("BranchId");
var beginDate = query?.Val <DateTime?>("BeginDate");
var endDate = query?.Val <DateTime?>("EndDate");
var status = query?.Val <RemittanceStatusType?>("Status");
if (!incoming.HasValue)
{
throw new ArgumentNullException(nameof(incoming));
}
if (!branchId.HasValue)
{
throw new ArgumentNullException(nameof(branchId));
}
var condition = @"WHERE r.DeleteDate IS NULL";
condition += incoming.Value ? " AND r.ReceiveBranchId = @branchId" : " AND r.SendBranchId = @branchId";
condition += beginDate.HasValue ? " AND r.SendDate >= @beginDate" : string.Empty;
condition += endDate.HasValue ? " AND r.SendDate <= @endDate" : string.Empty;
condition += status.HasValue ? " AND r.Status = @status" : string.Empty;
var order = listQuery.Order(string.Empty, new Sort
{
Name = "r.SendDate",
Direction = SortDirection.Desc
});
var page = listQuery.Page();
return(UnitOfWork.Session.Query <Remittance, Group, User, Group, User, Remittance>($@"
SELECT r.*, sb.*, su.*, rb.*, ru.*
FROM Remittances r
JOIN Groups sb ON r.SendBranchId = sb.Id
JOIN Users su ON r.SendUserId = su.Id
JOIN Groups rb ON r.ReceiveBranchId = rb.Id
LEFT JOIN Users ru ON r.ReceiveUserId = ru.Id
{condition} {order} {page}", (r, sb, su, rb, ru) => {
r.SendBranch = sb;
r.SendUser = su;
r.ReceiveBranch = rb;
r.ReceiveUser = ru;
return r;
}, new
{
beginDate,
endDate,
status,
branchId,
listQuery.Page?.Offset,
listQuery.Page?.Limit
}).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 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 <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 <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 <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 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 List <NotificationLog> List(ListQuery listQuery, object query = null)
{
if (listQuery == null)
{
throw new ArgumentNullException(nameof(listQuery));
}
var notificationReceiverId = query?.Val <int>("NotificationReceiverId");
var condition = "WHERE NotificationReceiverId = @notificationReceiverId";
var order = listQuery.Order(string.Empty, new Sort
{
Name = "CreateDate",
Direction = SortDirection.Asc
});
return(UnitOfWork.Session.Query <NotificationLog>($@"
SELECT *
FROM NotificationLogs
{condition} {order}", new { notificationReceiverId }).ToList());
}
public List <ExpenseType> List(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");
var order = listQuery.Order("eg.OrderBy", new Sort
{
Name = "et.OrderBy",
Direction = SortDirection.Asc
});
var page = listQuery.Page();
return(UnitOfWork.Session.Query <ExpenseType, ExpenseGroup, ExpenseType>($@"
SELECT et.*, eg.*
FROM ExpenseTypes et
JOIN ExpenseGroups eg ON et.ExpenseGroupId = eg.Id
{condition} {order} {page}",
(et, eg) =>
{
et.ExpenseGroup = eg;
return et;
},
new
{
expenseGroupId,
listQuery.Page?.Offset,
listQuery.Page?.Limit,
listQuery.Filter
}).ToList());
}
public List <InnerNotification> List(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, "n.Message");
var order = listQuery.Order(string.Empty, new Sort
{
Name = "n.CreateDate",
Direction = SortDirection.Desc
});
var page = listQuery.Page();
return(UnitOfWork.Session.Query <InnerNotification>($@"
SELECT n.*
FROM InnerNotifications n
WHERE {pre}",
new
{
branchId,
userId,
listQuery.Page?.Offset,
listQuery.Page?.Limit,
listQuery.Filter
}).ToList());
}
public List <LoanPercentSetting> List(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 = "l.OrganizationId = @organizationId";
var condition = listQuery.Like(pre, "g.DisplayName");
var order = listQuery.Order(string.Empty, new Sort
{
Name = "l.CollateralType",
Direction = SortDirection.Asc
});
var page = listQuery.Page();
return(UnitOfWork.Session.Query <LoanPercentSetting, Group, LoanPercentSetting>($@"
SELECT l.*, g.*
FROM LoanPercentSettings l
JOIN Groups g ON l.BranchId = g.Id
{condition} {order} {page}",
(l, g) => { l.Branch = g; return l; },
new
{
organizationId,
listQuery.Page?.Offset,
listQuery.Page?.Limit,
listQuery.Filter
}).ToList());
}
public List <NotificationReceiver> List(ListQuery listQuery, object query = null)
{
if (listQuery == null)
{
throw new ArgumentNullException(nameof(listQuery));
}
var notificationId = query?.Val <int>("NotificationId");
var pre = "nr.NotificationId = @notificationId";
var condition = listQuery.Like(pre, "cl.FullName");
var order = listQuery.Order(string.Empty, new Sort
{
Name = "cl.FullName",
Direction = SortDirection.Asc
});
var page = listQuery.Page();
return(UnitOfWork.Session.Query <NotificationReceiver, Notification, Client, NotificationReceiver>($@"
SELECT nr.*, n.*, cl.*
FROM NotificationReceivers nr
JOIN Notifications n ON nr.NotificationId = n.Id
JOIN Clients cl ON nr.ClientId = cl.Id
{condition} {order} {page}", (nr, n, cl) =>
{
nr.Notification = n;
nr.Client = cl;
return nr;
}, new
{
notificationId,
listQuery.Page?.Offset,
listQuery.Page?.Limit,
listQuery.Filter
}, UnitOfWork.Transaction).ToList());
}
public List <CashOrder> List(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");
var order = listQuery.Order("co.OrderDate DESC", new Sort
{
Name = "co.OrderNumber",
Direction = SortDirection.Desc
});
var page = listQuery.Page();
return(UnitOfWork.Session.Query <CashOrder, Client, User, Account, Account, Group, User, CashOrder>($@"
WITH CashOrderPaged AS (
SELECT co.Id
FROM CashOrders co
LEFT JOIN Clients c ON co.ClientId = c.Id
LEFT JOIN Users u ON co.UserId = u.Id
{condition} {order} {page}
)
SELECT co.*, c.*, u.*, da.*, ca.*, g.*, a.*
FROM CashOrderPaged cop
JOIN CashOrders co ON cop.Id = co.Id
LEFT JOIN Clients c ON co.ClientId = c.Id
LEFT JOIN Users u ON co.UserId = u.Id
JOIN Accounts da ON co.DebitAccountId = da.Id
JOIN Accounts ca ON co.CreditAccountId = ca.Id
JOIN Groups g ON co.BranchId = g.Id
JOIN Users a ON co.AuthorId = a.Id",
(co, c, u, da, ca, g, a) =>
{
co.Client = c;
co.User = u;
co.DebitAccount = da;
co.CreditAccount = ca;
co.Branch = g;
co.Author = a;
return co;
},
new
{
orderType,
beginDate,
endDate,
clientId,
userId,
orderNumber,
accountId,
ownerId,
listQuery.Page?.Offset,
listQuery.Page?.Limit,
listQuery.Filter
}).ToList());
}
public List<Contract> 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 displayStatus = query?.Val<ContractDisplayStatus?>("DisplayStatus");
var clientId = query?.Val<int?>("ClientId");
var ownerIds = query?.Val<int[]>("OwnerIds");
var isTransferred = query?.Val<bool?>("IsTransferred");
var identityNumber = query?.Val<Int64?>("IdentityNumber");
var pre = displayStatus.HasValue && displayStatus.Value == ContractDisplayStatus.Deleted ? "c.DeleteDate IS NOT NULL" : "c.DeleteDate IS NULL";
if (identityNumber.HasValue)
{
pre += " AND JSON_VALUE(c.ContractData, '$.Client.IdentityNumber') = CAST(@identityNumber AS NVARCHAR(MAX))";
}
else
{
pre += ownerIds != null && ownerIds.Length > 0 ? " AND mr.LeftMemberId IN @ownerIds" : string.Empty;
}
pre += collateralType.HasValue ? " AND c.CollateralType = @collateralType" : string.Empty;
pre += clientId.HasValue ? " AND c.ClientId = @clientId" : string.Empty;
if (isTransferred.HasValue && isTransferred.Value) pre += " AND c.TransferDate IS NOT NULL";
else pre += " AND c.TransferDate IS NULL";
var from = "FROM Contracts c";
if (displayStatus.HasValue)
{
switch (displayStatus)
{
case ContractDisplayStatus.New:
pre += " AND c.Status = 0";
pre += beginDate.HasValue ? " AND c.ContractDate >= @beginDate" : string.Empty;
pre += endDate.HasValue ? " AND c.ContractDate <= @endDate" : string.Empty;
break;
case ContractDisplayStatus.Open:
pre += " AND c.Status = 30 AND c.MaturityDate >= CONVERT(DATE, dbo.GETASTANADATE()) AND c.ProlongDate IS NULL";
pre += beginDate.HasValue ? " AND c.ContractDate >= @beginDate" : string.Empty;
pre += endDate.HasValue ? " AND c.ContractDate <= @endDate" : string.Empty;
break;
case ContractDisplayStatus.Overdue:
pre += " AND c.Status = 30 AND c.MaturityDate < CONVERT(DATE, dbo.GETASTANADATE())";
pre += beginDate.HasValue ? " AND c.ContractDate >= @beginDate" : string.Empty;
pre += endDate.HasValue ? " AND c.ContractDate <= @endDate" : string.Empty;
break;
case ContractDisplayStatus.Prolong:
pre += " AND c.Status = 30 AND c.MaturityDate >= CONVERT(DATE, dbo.GETASTANADATE()) AND c.ProlongDate IS NOT NULL";
pre += " AND ca.ActionType = 10";
pre += beginDate.HasValue ? " AND ca.[Date] >= @beginDate" : string.Empty;
pre += endDate.HasValue ? " AND ca.[Date] <= @endDate" : string.Empty;
from = "FROM ContractActions ca JOIN Contracts c ON ca.ContractId = c.Id";
break;
case ContractDisplayStatus.BoughtOut:
pre += " AND c.Status = 40";
pre += " AND ca.ActionType IN (20, 30, 40)";
pre += beginDate.HasValue ? " AND ca.[Date] >= @beginDate" : string.Empty;
pre += endDate.HasValue ? " AND ca.[Date] <= @endDate" : string.Empty;
from = "FROM ContractActions ca JOIN Contracts c ON ca.ContractId = c.Id";
break;
case ContractDisplayStatus.SoldOut:
pre += " AND c.Status = 50";
pre += " AND ca.ActionType = 60";
pre += beginDate.HasValue ? " AND ca.[Date] >= @beginDate" : string.Empty;
pre += endDate.HasValue ? " AND ca.[Date] <= @endDate" : string.Empty;
from = "FROM ContractActions ca JOIN Contracts c ON ca.ContractId = c.Id";
break;
case ContractDisplayStatus.Signed:
pre += " AND c.Status = 30";
pre += beginDate.HasValue ? " AND c.ContractDate >= @beginDate" : string.Empty;
pre += endDate.HasValue ? " AND c.ContractDate <= @endDate" : string.Empty;
break;
default:
break;
}
}
else
{
pre += beginDate.HasValue ? " AND c.ContractDate >= @beginDate" : string.Empty;
pre += endDate.HasValue ? " AND c.ContractDate <= @endDate" : string.Empty;
}
var condition = listQuery.Like(pre, "c.ContractNumber", "JSON_VALUE(c.ContractData, '$.Client.Fullname')", "JSON_VALUE(c.ContractData, '$.Client.IdentityNumber')", "JSON_VALUE(c.ContractData, '$.Client.MobilePhone')");
var order = listQuery.Order("c.ContractDate DESC", new Sort
{
Name = "c.ContractNumber",
Direction = SortDirection.Desc
});
var page = listQuery.Page();
return UnitOfWork.Session.Query<Contract, Group, User, Contract>($@"
WITH ContractPaged AS (
SELECT DISTINCT c.Id,c.ContractNumber,c.ContractDate
{from}
JOIN MemberRelations mr ON mr.RightMemberId = c.OwnerId
{condition} {order} {page}
)
SELECT DISTINCT c.*,
(CASE
WHEN c.DeleteDate IS NOT NULL THEN 60
WHEN c.Status = 0 THEN 0
WHEN c.Status = 30 AND c.MaturityDate >= CONVERT(DATE, dbo.GETASTANADATE()) AND c.ProlongDate IS NULL THEN 10
WHEN c.Status = 30 AND c.MaturityDate < CONVERT(DATE, dbo.GETASTANADATE()) THEN 20
WHEN c.Status = 30 AND c.MaturityDate >= CONVERT(DATE, dbo.GETASTANADATE()) AND c.ProlongDate IS NOT NULL THEN 30
WHEN c.Status = 40 THEN 40
WHEN c.Status = 50 THEN 50
ELSE 0
END) AS DisplayStatus,
g.*,
u.*
FROM ContractPaged cp
JOIN Contracts c ON cp.Id = c.Id
JOIN Groups g ON c.BranchId = g.Id
JOIN Users u ON c.AuthorId = u.Id
{order}",
(c, g, u) =>
{
c.Branch = g;
c.Author = u;
return c;
},
new
{
beginDate,
endDate,
collateralType,
clientId,
ownerIds,
identityNumber,
listQuery.Page?.Offset,
listQuery.Page?.Limit,
listQuery.Filter
}).ToList();
}