public static List <LessonSessionsModel> get(HttpSessionStateBase Session, Guid?Id, string FILTER_Keyword, string FILTER_InvoiceNo,
bool?FILTER_chkDateFrom, DateTime?FILTER_DateFrom, bool?FILTER_chkDateTo, DateTime?FILTER_DateTo,
int?Cancelled)
{
Guid Branches_Id = Helper.getActiveBranchId(Session);
if (FILTER_chkDateFrom == null || !(bool)FILTER_chkDateFrom)
{
FILTER_DateFrom = null;
}
if (FILTER_chkDateTo == null || !(bool)FILTER_chkDateTo)
{
FILTER_DateTo = null;
}
string ShowOnlyOwnUserDataClause = "";
if (UserAccountsController.getShowOnlyUserData(Session))
{
ShowOnlyOwnUserDataClause = string.Format(" AND (Student_UserAccounts.Id = '{0}' OR Tutor_UserAccounts.Id = '{0}')", UserAccountsController.getUserId(Session));
}
string sql = string.Format(@"
SELECT LessonSessions.*,
SaleInvoices.No AS SaleInvoices_No,
SaleInvoiceItems.Description AS SaleInvoiceItems_Description,
Student_UserAccounts.Fullname AS Student_UserAccounts_Fullname,
Student_UserAccounts.No AS Student_UserAccounts_No,
Tutor_UserAccounts.Fullname AS Tutor_UserAccounts_Fullname,
Tutor_UserAccounts.No AS Tutor_UserAccounts_No,
ROW_NUMBER() OVER (ORDER BY LessonSessions.Timestamp DESC) AS InitialRowNumber
FROM LessonSessions
LEFT JOIN SaleInvoiceItems ON SaleInvoiceItems.Id = LessonSessions.SaleInvoiceItems_Id
LEFT JOIN SaleInvoices ON SaleInvoices.Id = SaleInvoiceItems.SaleInvoices_Id
LEFT JOIN UserAccounts Student_UserAccounts ON Student_UserAccounts.Id = SaleInvoices.Customer_UserAccounts_Id
LEFT JOIN UserAccounts Tutor_UserAccounts ON Tutor_UserAccounts.Id = LessonSessions.Tutor_UserAccounts_Id
WHERE 1=1
AND (@Id IS NULL OR LessonSessions.Id = @Id)
AND (@Id IS NOT NULL OR (
LessonSessions.Branches_Id = @Branches_Id
AND (@FILTER_Keyword IS NULL OR (
LessonSessions.No LIKE '%'+@FILTER_Keyword+'%'
))
AND (@FILTER_DateFrom IS NULL OR LessonSessions.Timestamp >= @FILTER_DateFrom)
AND (@FILTER_DateTo IS NULL OR LessonSessions.Timestamp <= @FILTER_DateTo)
AND (@Cancelled IS NULL OR LessonSessions.Cancelled = @Cancelled)
AND (@FILTER_InvoiceNo IS NULL OR (LessonSessions.SaleInvoiceItems_Id IN (
SELECT SaleInvoiceItems.Id
FROM SaleInvoiceItems
LEFT JOIN SaleInvoices ON Saleinvoices.Id = SaleInvoiceItems.SaleInvoices_Id
WHERE SaleInvoices.No = @FILTER_InvoiceNo
)))
{0}
))
ORDER BY LessonSessions.Timestamp DESC
", ShowOnlyOwnUserDataClause);
return(new DBContext().Database.SqlQuery <LessonSessionsModel>(sql,
DBConnection.getSqlParameter(LessonSessionsModel.COL_Id.Name, Id),
DBConnection.getSqlParameter(LessonSessionsModel.COL_Branches_Id.Name, Branches_Id),
DBConnection.getSqlParameter(LessonSessionsModel.COL_Cancelled.Name, Cancelled),
DBConnection.getSqlParameter("FILTER_Keyword", FILTER_Keyword),
DBConnection.getSqlParameter("FILTER_InvoiceNo", FILTER_InvoiceNo),
DBConnection.getSqlParameter("FILTER_DateFrom", FILTER_DateFrom),
DBConnection.getSqlParameter("FILTER_DateTo", Util.getAsEndDate(FILTER_DateTo))
).ToList());
}
public static List <SaleInvoicesModel> get(HttpSessionStateBase Session, Guid?Id, string SaleInvoiceItemIdList,
string FILTER_Keyword, string FILTER_PaymentNo, bool?FILTER_chkDateFrom, DateTime?FILTER_DateFrom, bool?FILTER_chkDateTo, DateTime?FILTER_DateTo,
int?Cancelled, int?Approved, int?FILTER_HasDueAmount)
{
Guid Branches_Id = Helper.getActiveBranchId(Session);
if (FILTER_chkDateFrom == null || !(bool)FILTER_chkDateFrom)
{
FILTER_DateFrom = null;
}
if (FILTER_chkDateTo == null || !(bool)FILTER_chkDateTo)
{
FILTER_DateTo = null;
}
string SaleInvoiceItemIdListClause = "";
if (!string.IsNullOrEmpty(SaleInvoiceItemIdList))
{
SaleInvoiceItemIdListClause = string.Format("AND SaleInvoices.Id IN ({0})", UtilWebMVC.convertToSqlIdList(SaleInvoiceItemIdList));
}
string ShowOnlyOwnUserDataClause = "";
if (UserAccountsController.getShowOnlyUserData(Session))
{
ShowOnlyOwnUserDataClause = string.Format(" AND Customer_UserAccounts.Id = '{0}' ", UserAccountsController.getUserId(Session));
}
string sql = string.Format(@"
SELECT SaleInvoices.*,
Branches.Name AS Branches_Name,
Customer_UserAccounts.Fullname AS Customer_UserAccounts_Name,
Customer_UserAccounts.No AS Customer_UserAccounts_No,
ROW_NUMBER() OVER (ORDER BY SaleInvoices.No DESC) AS InitialRowNumber
FROM SaleInvoices
LEFT JOIN Branches ON Branches.Id = SaleInvoices.Branches_Id
LEFT JOIN UserAccounts Customer_UserAccounts ON Customer_UserAccounts.Id = SaleInvoices.Customer_UserAccounts_Id
WHERE 1=1
AND (@Id IS NULL OR SaleInvoices.Id = @Id)
AND (@Id IS NOT NULL OR (
(@FILTER_Keyword IS NULL OR (
SaleInvoices.No LIKE '%'+@FILTER_Keyword+'%'
OR Customer_UserAccounts.Fullname LIKE '%'+@FILTER_Keyword+'%'
))
AND (@FILTER_PaymentNo IS NULL OR (SaleInvoices.Id IN (
SELECT SaleInvoices.Id
FROM PaymentItems
LEFT JOIN Payments ON Payments.Id = PaymentItems.Payments_Id
LEFT JOIN SaleInvoices ON Saleinvoices.Id = PaymentItems.ReferenceId
WHERE Payments.No = @FILTER_PaymentNo
)))
AND (@FILTER_DateFrom IS NULL OR SaleInvoices.Timestamp >= @FILTER_DateFrom)
AND (@FILTER_DateTo IS NULL OR SaleInvoices.Timestamp <= @FILTER_DateTo)
AND (@Cancelled IS NULL OR SaleInvoices.Cancelled = @Cancelled)
AND (@Approved IS NULL OR SaleInvoices.Approved = @Approved)
AND (@FILTER_HasDueAmount IS NULL OR ((@FILTER_HasDueAmount = 0 AND SaleInvoices.Due = 0) OR (@FILTER_HasDueAmount = 1 AND SaleInvoices.Due > 0)))
AND (@Branches_Id IS NULL OR SaleInvoices.Branches_Id = @Branches_Id)
{0}{1}
))
ORDER BY SaleInvoices.No DESC
", SaleInvoiceItemIdListClause, ShowOnlyOwnUserDataClause);
return(new DBContext().Database.SqlQuery <SaleInvoicesModel>(sql,
DBConnection.getSqlParameter(SaleInvoicesModel.COL_Id.Name, Id),
DBConnection.getSqlParameter("FILTER_Keyword", FILTER_Keyword),
DBConnection.getSqlParameter("FILTER_PaymentNo", FILTER_PaymentNo),
DBConnection.getSqlParameter("FILTER_DateFrom", FILTER_DateFrom),
DBConnection.getSqlParameter("FILTER_DateTo", Util.getAsEndDate(FILTER_DateTo)),
DBConnection.getSqlParameter("FILTER_HasDueAmount", FILTER_HasDueAmount),
DBConnection.getSqlParameter(SaleInvoicesModel.COL_Branches_Id.Name, Branches_Id),
DBConnection.getSqlParameter(SaleInvoicesModel.COL_Cancelled.Name, Cancelled),
DBConnection.getSqlParameter(SaleInvoicesModel.COL_Approved.Name, Approved)
).ToList());
}
