public void add(SaleInvoicesModel model, List <SaleInvoiceItemsModel> SaleInvoiceItems)
{
model.Branches_Id = Helper.getActiveBranchId(Session);
model.Timestamp = Helper.getCurrentDateTime();
model.Due = model.Amount;
db.Database.ExecuteSqlCommand(@"
-- INCREMENT LAST HEX NUMBER
DECLARE @HexLength int = 5, @LastHex_String varchar(5), @NewNo varchar(5)
SELECT @LastHex_String = ISNULL(MAX(No),'') From SaleInvoices
DECLARE @LastHex_Int int
SELECT @LastHex_Int = CONVERT(INT, CONVERT(VARBINARY, REPLICATE('0', LEN(@LastHex_String)%2) + @LastHex_String, 2)) --@LastHex_String length must be even number of digits to convert to int
SET @NewNo = RIGHT(CONVERT(NVARCHAR(10), CONVERT(VARBINARY(8), @LastHex_Int + 1), 1),@HexLength)
INSERT INTO SaleInvoices (Id, No, Branches_Id, Timestamp, Notes, Customer_UserAccounts_Id, Amount, Due, Cancelled, Approved)
VALUES(@Id,@NewNo,@Branches_Id,@Timestamp,@Notes,@Customer_UserAccounts_Id,@Amount,@Due,@Cancelled,@Approved);
",
DBConnection.getSqlParameter(SaleInvoicesModel.COL_Id.Name, model.Id),
DBConnection.getSqlParameter(SaleInvoicesModel.COL_Branches_Id.Name, model.Branches_Id),
DBConnection.getSqlParameter(SaleInvoicesModel.COL_Timestamp.Name, model.Timestamp),
DBConnection.getSqlParameter(SaleInvoicesModel.COL_Notes.Name, model.Notes),
DBConnection.getSqlParameter(SaleInvoicesModel.COL_Customer_UserAccounts_Id.Name, model.Customer_UserAccounts_Id),
DBConnection.getSqlParameter(SaleInvoicesModel.COL_Amount.Name, model.Amount),
DBConnection.getSqlParameter(SaleInvoicesModel.COL_Due.Name, model.Due),
DBConnection.getSqlParameter(SaleInvoicesModel.COL_Cancelled.Name, model.Cancelled),
DBConnection.getSqlParameter(SaleInvoicesModel.COL_Approved.Name, model.Approved)
);
ActivityLogsController.AddCreateLog(db, Session, model.Id);
db.SaveChanges();
SaleInvoiceItemsController.add(Session, SaleInvoiceItems, model.Id);
}
/* PRINT **********************************************************************************************************************************************/
// GET: Payments/Print
public ActionResult Print(Guid?id)
{
if (id == null || !UserAccountsController.getUserAccess(Session).Payments_View)
{
return(RedirectToAction(nameof(HomeController.Index), "Home"));
}
PaymentsModel model = get((Guid)id);
ViewBag.InvoiceHeaderText = new BranchesController().get(Helper.getActiveBranchId(Session)).InvoiceHeaderText;
ViewData["SaleInvoiceItems"] = SaleInvoiceItemsController.get(null, null, null, model.Id, null, null, null, null, null, null)
.OrderBy(x => x.SaleInvoices_No)
.ThenBy(x => x.RowNo)
.ToList();
ViewData["PaymentItems"] = PaymentItemsController.get(null, model.Id);
ViewBag.TotalAmount = model.CashAmount + model.ConsignmentAmount + model.DebitAmount;
return(View(model));
}
public ActionResult setCreateViewBagsAndReturn(string saleInvoiceIdList)
{
List <SaleInvoiceItemsModel> SaleInvoiceItems = SaleInvoiceItemsController.get(null, null, saleInvoiceIdList, null, null, null, null, null, null, null)
.OrderBy(x => x.SaleInvoices_No)
.ThenBy(x => x.RowNo)
.ToList();
ViewBag.TotalAmount = SaleInvoiceItems.Sum(x => x.TotalAmount);
List <SaleInvoicesModel> saleinvoices = SaleInvoicesController.get(Session, saleInvoiceIdList);
ViewBag.DueAmount = saleinvoices.Sum(x => x.Due);
ViewBag.id = saleInvoiceIdList;
//BanksController.setDropDownListViewBag(this);
ConsignmentsController.setDropDownListViewBag(this);
return(View(SaleInvoiceItems));
}
public ActionResult Create(string JsonLessonSessions, string FILTER_Keyword, string FILTER_InvoiceNo, int?FILTER_Cancelled,
bool?FILTER_chkDateFrom, DateTime?FILTER_DateFrom, bool?FILTER_chkDateTo, DateTime?FILTER_DateTo)
{
List <LessonSessionsModel> LessonSessions = new List <LessonSessionsModel>();
if (string.IsNullOrEmpty(JsonLessonSessions))
{
return(returnView(LessonSessions, "Please add at least one student"));
}
LessonSessions = JsonConvert.DeserializeObject <List <LessonSessionsModel> >(JsonLessonSessions);
LessonSessionsModel model = LessonSessions[0];
model.Branches_Id = Helper.getActiveBranchId(Session);
if (ModelState.IsValid)
{
Guid PayrollPaymentItems_Id = Guid.NewGuid();
//verify remaining hours is enough to cover the session hours. show error for the first error only
string SaleInvoiceItems_IdList = string.Join(",", LessonSessions.Select(x => x.SaleInvoiceItems_Id.ToString()).ToArray());
List <SaleInvoiceItemsModel> SaleInvoiceItems = SaleInvoiceItemsController.get_by_IdList(SaleInvoiceItems_IdList);
List <SaleInvoiceItemsModel> insufficientRemainingHours = SaleInvoiceItems.Where(x => x.SessionHours_Remaining < model.SessionHours).ToList();
if (insufficientRemainingHours.Count > 0)
{
return(returnView(LessonSessions, string.Format("Insufficient remaining hours for student {0}", insufficientRemainingHours[0].Customer_UserAccounts_Name)));
}
//set tutor pay rate
List <HourlyRatesModel> hourlyRates = HourlyRatesController.get(null, null, model.Tutor_UserAccounts_Id);
bool isFullTimeTutor = false;
foreach (HourlyRatesModel hourlyRate in hourlyRates)
{
if (hourlyRate.FullTimeTutorPayrate > 0)
{
isFullTimeTutor = true;
continue;
}
}
foreach (LessonSessionsModel session in LessonSessions)
{
SaleInvoiceItemsModel saleInvoiceItem = SaleInvoiceItems.Where(x => x.Id == session.SaleInvoiceItems_Id).FirstOrDefault();
session.Id = Guid.NewGuid();
session.Branches_Id = model.Branches_Id;
session.HourlyRates_Rate = 0;
session.SessionHours = session.IsScheduleChange ? 0 : session.SessionHours;
session.TravelCost = session.IsScheduleChange ? 0 : (int)Math.Ceiling((saleInvoiceItem.TravelCost / saleInvoiceItem.SessionHours) * session.SessionHours);
session.TutorTravelCost = session.IsScheduleChange ? 0 : (int)Math.Ceiling((saleInvoiceItem.TutorTravelCost / saleInvoiceItem.SessionHours) * session.SessionHours);
session.PayrollPaymentItems_Id = session.IsScheduleChange ? (Guid?)null : PayrollPaymentItems_Id;
//Calculate tutor payrate
if (!isFullTimeTutor && hourlyRates.Count > 0)
{
foreach (HourlyRatesModel hourlyRate in hourlyRates)
{
session.HourlyRates_Rate = Math.Ceiling(hourlyRate.Rate / LessonSessions.Count);
if (hourlyRate.LessonPackages_Id == saleInvoiceItem.LessonPackages_Id) //rate for the exact lesson package
{
break;
}
}
}
model.TutorTravelCost = session.TutorTravelCost;
add(session);
//adjust remaining session hours
saleInvoiceItem.SessionHours_Remaining -= session.SessionHours;
SaleInvoiceItemsController.update_SessionHours_Remaining(db, Session, saleInvoiceItem.Id, saleInvoiceItem.SessionHours_Remaining,
string.Format("Lesson Session on {0:dd/MM/yy HH:mm} for {1:N2} hours. Remaining hours: {2:N2} hours.", session.Timestamp, session.SessionHours, saleInvoiceItem.SessionHours_Remaining));
}
db.SaveChanges();
//create payrollpaymentitem
if (!model.IsScheduleChange)
{
//Calculate tutor payrate
decimal HourlyRate = 0;
if (!isFullTimeTutor && hourlyRates.Count > 0)
{
foreach (HourlyRatesModel hourlyRate in hourlyRates)
{
HourlyRate = hourlyRate.Rate;
if (hourlyRate.Branches_Id == model.Branches_Id) //rate for the exact lesson package
{
break;
}
}
}
PayrollPaymentItemsController.add(new PayrollPaymentItemsModel()
{
Id = PayrollPaymentItems_Id,
PayrollPayments_Id = null,
Timestamp = model.Timestamp,
Description = null,
Hour = model.IsWaiveTutorFee ? 0 : model.SessionHours,
HourlyRate = HourlyRate,
TutorTravelCost = model.TutorTravelCost,
Amount = PayrollPaymentItemsController.calculateAmount(model.IsWaiveTutorFee, model.SessionHours, HourlyRate, model.TutorTravelCost),
UserAccounts_Id = model.Tutor_UserAccounts_Id,
Branches_Id = model.Branches_Id,
IsFullTime = false
});
}
return(RedirectToAction(nameof(Index), new
{
FILTER_Keyword = FILTER_Keyword,
FILTER_InvoiceNo = FILTER_InvoiceNo,
FILTER_Cancelled = FILTER_Cancelled,
FILTER_chkDateFrom = FILTER_chkDateFrom,
FILTER_DateFrom = FILTER_DateFrom,
FILTER_chkDateTo = FILTER_chkDateTo,
FILTER_DateTo = FILTER_DateTo
}));
}
return(returnView(LessonSessions, null));
}
public JsonResult Ajax_GetDetails(Guid id)
{
UserAccountRolesModel access = UserAccountsController.getUserAccess(Session);
List <SaleInvoiceItemsModel> models = SaleInvoiceItemsController.get_by_SaleInvoices_Id(id);
string content = string.Format(@"
<div class='table-responsive'>
<table class='table table-striped table-bordered'>
<!--<thead>
<tr>
<th>Line</th>
<th>Description</th>
<th></th>
<th></th>
<th></th>
</tr>
</thead>-->
<tbody>
");
foreach (SaleInvoiceItemsModel model in models)
{
int voucher = model.VouchersAmount;
string subtractions = "";
if (voucher != 0)
{
subtractions = Util.append(subtractions, string.Format("Voucher: {0:N0}", -1 * voucher), "<br/>");
}
if (model.DiscountAmount != 0)
{
subtractions = Util.append(subtractions, string.Format("Discount: {0:N0}", -1 * model.DiscountAmount), "<br/>");
}
if (!string.IsNullOrEmpty(subtractions))
{
subtractions += "<br/>";
}
string log = model.RowNo.ToString();
if (access.SaleInvoices_TutorTravelCost_View)
{
log = string.Format("<a href='javascript: void(0)' onclick=\"Log('{0}')\">{1}</a>", model.Id.ToString(), model.RowNo);
}
string remaining = "";
if (!model.IsClubSubscription)
{
remaining = string.Format("<br/><strong>Available Hours:</strong> {0:N2}", model.SessionHours_Remaining);
}
else
{
DateTime expirationDate = model.StartingDate.AddMonths(model.ExpirationMonth).AddSeconds(-1);
int remainingDays = 0;
if (expirationDate > DateTime.Now.Date)
{
remainingDays = (expirationDate - DateTime.Now.Date).Days;
}
remaining = string.Format("<br/><strong>Expired:</strong> {0:dd/MM/yy} ({1:N0} days)", expirationDate, remainingDays);
}
content += string.Format(@"
<tr>
<td class='align-top' style='width:10px;'>{0}</td>
<td class='align-top'>{1}{2}{3}{4}</td>
<td class='align-top text-right'>
Qty: {5:N0} x {6:N0}
{7}{8}
</td>
<td class='align-top text-right'>
{9}
<strong>{10:N0}</strong>
</td>
</tr>
",
log,
model.Description,
model.LessonPackages_Id == null ? "" : remaining,
!string.IsNullOrWhiteSpace(model.VouchersName) ? string.Format("<br/>Vouchers: {0}", model.VouchersName) : "",
!string.IsNullOrWhiteSpace(model.Notes) ? string.Format("<br/>Notes: {0}", model.Notes) : "",
model.Qty,
model.Price,
model.TravelCost > 0 || model.TutorTravelCost > 0 ? string.Format("<br/>Travel: {0:N0}", model.TravelCost) : "",
access.SaleInvoices_TutorTravelCost_View && (model.TravelCost > 0 || model.TutorTravelCost > 0) ? string.Format(" (Tutor: {0:N0})", model.TutorTravelCost) : "",
subtractions,
model.TotalAmount
);
}
SaleInvoicesModel SaleInvoice = get(id);
content += string.Format(@"
</tbody></table></div>
<div class='mt-2'>
<div class='h3 ml-2 float-right font-weight-bold'>TOTAL: {0:N0}</div>
{1}
</div>
",
SaleInvoice.Amount,
!string.IsNullOrWhiteSpace(SaleInvoice.Notes) ? string.Format("<div><strong>Notes: </strong>{0}</div>", SaleInvoice.Notes) : ""
);
return(Json(new { content = content }, JsonRequestBehavior.AllowGet));
}
