public ActionResult Create(PettyCashRecordsModel model, string FILTER_Keyword, int?FILTER_Approved,
bool?FILTER_chkDateFrom, DateTime?FILTER_DateFrom, bool?FILTER_chkDateTo, DateTime?FILTER_DateTo)
{
if (ModelState.IsValid)
{
model.Id = Guid.NewGuid();
model.Branches_Id = Helper.getActiveBranchId(Session);
model.Timestamp = Helper.getCurrentDateTime();
model.UserAccounts_Id = (Guid)UserAccountsController.getUserId(Session);
add(db, model);
return(RedirectToAction(nameof(Index), new
{
FILTER_Keyword = FILTER_Keyword,
FILTER_Approved = FILTER_Approved,
FILTER_chkDateFrom = FILTER_chkDateFrom,
FILTER_DateFrom = FILTER_DateFrom,
FILTER_chkDateTo = FILTER_chkDateTo,
FILTER_DateTo = FILTER_DateTo
}));
}
setViewBag(FILTER_Keyword, FILTER_Approved, FILTER_chkDateFrom, FILTER_DateFrom, FILTER_chkDateTo, FILTER_DateTo);
return(View());
}
public static void Add(DBContext db, HttpSessionStateBase Session, Guid ReferenceId, string description)
{
db.ActivityLogs.Add(new ActivityLogsModel
{
Id = Guid.NewGuid(),
ReferenceId = ReferenceId,
Timestamp = Helper.getCurrentDateTime(),
Description = description,
UserAccounts_Id = (Guid)UserAccountsController.getUserId(Session),
UserAccounts_Fullname = null
});
}
public void add(FilesModel model)
{
model.UserAccounts_Id = (Guid)UserAccountsController.getUserId(Session);
model.No = WebDBConnection.GetNextHex(db.Database, "Files", "No");
WebDBConnection.Insert(db.Database, "Files",
DBConnection.getSqlParameter(FilesModel.COL_Id.Name, model.Id),
DBConnection.getSqlParameter(FilesModel.COL_OnlineFileId.Name, model.OnlineFileId),
DBConnection.getSqlParameter(FilesModel.COL_ParentId.Name, model.ParentId),
DBConnection.getSqlParameter(FilesModel.COL_Branches_Id.Name, model.Branches_Id),
DBConnection.getSqlParameter(FilesModel.COL_No.Name, model.No),
DBConnection.getSqlParameter(FilesModel.COL_Filename.Name, model.Filename),
DBConnection.getSqlParameter(FilesModel.COL_DirectoryName.Name, model.DirectoryName),
DBConnection.getSqlParameter(FilesModel.COL_Notes.Name, model.Notes),
DBConnection.getSqlParameter(FilesModel.COL_UserAccounts_Id.Name, model.UserAccounts_Id),
DBConnection.getSqlParameter(FilesModel.COL_Timestamp.Name, model.Timestamp),
DBConnection.getSqlParameter(FilesModel.COL_IsDeleted.Name, model.IsDeleted),
DBConnection.getSqlParameter(FilesModel.COL_Approved.Name, model.Approved)
);
}
public static bool hasActiveClubSubscription(HttpSessionStateBase Session)
{
return(hasActiveClubSubscriptions((Guid)UserAccountsController.getUserId(Session), 0).Count > 0);
}
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 ActionResult Create(string id, string JsonPayments)
{
List <SaleInvoicesModel> saleinvoices = SaleInvoicesController.get(Session, id).OrderBy(x => x.Timestamp).ToList();
if (ModelState.IsValid)
{
PaymentsModel payment = JsonConvert.DeserializeObject <PaymentsModel>(JsonPayments);
payment.Id = Guid.NewGuid();
payment.No = Util.incrementHexNumber(getLastNo());
payment.Timestamp = Helper.getCurrentDateTime();
if (payment.DebitAmount == 0)
{
payment.DebitBank = null;
payment.DebitNumber = null;
payment.DebitOwnerName = null;
payment.DebitRefNo = null;
}
if (payment.ConsignmentAmount == 0)
{
payment.Consignments_Id = null;
}
//create payment items and update sale invoice due amount
int RemainingPaymentAmount = payment.DebitAmount + payment.CashAmount + payment.ConsignmentAmount;
int paymentItemAmount = 0;
int dueBefore = 0;
int dueAfter = 0;
foreach (SaleInvoicesModel saleinvoice in saleinvoices)
{
dueBefore = saleinvoice.Due;
dueAfter = saleinvoice.Due;
if (RemainingPaymentAmount == 0)
{
break;
}
else
{
if (RemainingPaymentAmount >= saleinvoice.Due)
{
paymentItemAmount = saleinvoice.Due;
}
else
{
paymentItemAmount = RemainingPaymentAmount;
}
RemainingPaymentAmount -= paymentItemAmount;
dueAfter -= paymentItemAmount;
SaleInvoicesController.update_Due(Session, db, saleinvoice.Id, saleinvoice.Due, saleinvoice.Due - paymentItemAmount);
saleinvoice.Due -= paymentItemAmount;
}
PaymentItemsController.add(db, payment.Id, new PaymentItemsModel {
Id = Guid.NewGuid(),
Payments_Id = payment.Id,
ReferenceId = saleinvoice.Id,
Amount = paymentItemAmount,
DueBefore = dueBefore,
DueAfter = dueAfter
});
}
//create petty cash
if (payment.CashAmount > 0)
{
PettyCashRecordsController.add(db, new PettyCashRecordsModel
{
Id = Guid.NewGuid(),
Branches_Id = Helper.getActiveBranchId(Session),
ReferenceId = payment.Id,
No = "",
Timestamp = payment.Timestamp,
PettyCashRecordsCategories_Id = SettingsController.get().AutoEntryForCashPayments.Value,
Notes = "Cash Payment [" + payment.No + "]",
Amount = payment.CashAmount,
Approved = false,
UserAccounts_Id = (Guid)UserAccountsController.getUserId(Session),
ExpenseCategories_Id = null
});
}
add(payment);
return(RedirectToAction(nameof(Print), new { id = payment.Id }));
}
return(setCreateViewBagsAndReturn(id));
}
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());
}
