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