public void update_Cancelled(Guid Id, string CancelNotes)
{
WebDBConnection.Update(db.Database, "Payments",
DBConnection.getSqlParameter(PaymentsModel.COL_Id.Name, Id),
DBConnection.getSqlParameter(PaymentsModel.COL_Cancelled.Name, 1),
DBConnection.getSqlParameter(PaymentsModel.COL_CancelNotes.Name, CancelNotes)
);
ActivityLogsController.AddEditLog(db, Session, Id, string.Format(PaymentsModel.COL_CancelNotes.LogDisplay, CancelNotes));
//Adjust sale invoice due amount
List <PaymentItemsModel> paymentItems = PaymentItemsController.get(null, Id);
List <SaleInvoicesModel> saleInvoices;
foreach (PaymentItemsModel paymentitem in paymentItems)
{
saleInvoices = SaleInvoicesController.get(Session, paymentitem.ReferenceId.ToString());
if (saleInvoices.Count > 0)
{
WebDBConnection.Update(db.Database, "SaleInvoices",
DBConnection.getSqlParameter(SaleInvoicesModel.COL_Id.Name, saleInvoices[0].Id),
DBConnection.getSqlParameter(SaleInvoicesModel.COL_Due.Name, saleInvoices[0].Due + paymentitem.Amount)
);
}
}
db.SaveChanges();
}
public static void update_OnlineToken(DBContext db, string Id, string OnlineToken)
{
WebDBConnection.Update(db.Database, "UserAccounts",
DBConnection.getSqlParameter(UserAccountsModel.COL_Id.Name, new Guid(Id)),
DBConnection.getSqlParameter(UserAccountsModel.COL_OnlineToken.Name, OnlineToken)
);
}
public Task <T> GetAsync <T>(string key)
{
if (string.IsNullOrEmpty(key))
{
throw new ArgumentException("Key MUST have a value");
}
TaskCompletionSource <T> taskCompletionSource = new TaskCompletionSource <T>();
string token = WebDBConnection.GetValues(db.Database, string.Format(@"
SELECT @{0} = {3}
FROM {1}
WHERE [{2}]='{4}'
", WebDBConnection.PARAM_RETURNVALUE, TABLENAME, COL_DB_Id, COL_DB_Token, key)
);
if (string.IsNullOrWhiteSpace(token))
{
taskCompletionSource.SetResult(default(T));
}
else
{
try
{
taskCompletionSource.SetResult(NewtonsoftJsonSerializer.Instance.Deserialize <T>(token));
}
catch (Exception ex) { taskCompletionSource.SetException(ex); }
}
return(taskCompletionSource.Task);
}
public void update_Approved(Guid Id, bool value)
{
WebDBConnection.Update(db.Database, "Payments",
DBConnection.getSqlParameter(PaymentsModel.COL_Id.Name, Id),
DBConnection.getSqlParameter(PaymentsModel.COL_Approved.Name, value)
);
ActivityLogsController.AddEditLog(db, Session, Id, string.Format(PaymentsModel.COL_Approved.LogDisplay, null, value));
db.SaveChanges();
}
public static void update_Due(HttpSessionStateBase Session, DBContext db, Guid Id, int originalValue, int newValue)
{
WebDBConnection.Update(db.Database, "SaleInvoices",
DBConnection.getSqlParameter(SaleInvoicesModel.COL_Id.Name, Id),
DBConnection.getSqlParameter(SaleInvoicesModel.COL_Due.Name, newValue)
);
ActivityLogsController.AddEditLog(db, Session, Id, string.Format(SaleInvoicesModel.COL_Due.LogDisplay, originalValue, newValue));
db.SaveChanges();
}
public void updatePassword(UserAccountsModel model, string log)
{
WebDBConnection.Update(db.Database, "UserAccounts",
DBConnection.getSqlParameter(UserAccountsModel.COL_Id.Name, model.Id),
DBConnection.getSqlParameter(UserAccountsModel.COL_Password.Name, model.Password),
DBConnection.getSqlParameter(UserAccountsModel.COL_ResetPassword.Name, model.ResetPassword)
);
ActivityLogsController.AddEditLog(db, Session, model.Id, log);
db.SaveChanges();
}
public void add(VouchersModel model)
{
WebDBConnection.Insert(db.Database, "Vouchers",
DBConnection.getSqlParameter(VouchersModel.COL_Id.Name, model.Id),
DBConnection.getSqlParameter(VouchersModel.COL_Active.Name, model.Active),
DBConnection.getSqlParameter(VouchersModel.COL_Code.Name, model.Code),
DBConnection.getSqlParameter(VouchersModel.COL_Description.Name, model.Description),
DBConnection.getSqlParameter(VouchersModel.COL_Amount.Name, model.Amount)
);
ActivityLogsController.AddCreateLog(db, Session, model.Id);
db.SaveChanges();
}
public void update(VouchersModel model, string log)
{
WebDBConnection.Update(db.Database, "Vouchers",
DBConnection.getSqlParameter(VouchersModel.COL_Id.Name, model.Id),
DBConnection.getSqlParameter(VouchersModel.COL_Active.Name, model.Active),
DBConnection.getSqlParameter(VouchersModel.COL_Code.Name, model.Code),
DBConnection.getSqlParameter(VouchersModel.COL_Description.Name, model.Description),
DBConnection.getSqlParameter(VouchersModel.COL_Amount.Name, model.Amount)
);
ActivityLogsController.AddEditLog(db, Session, model.Id, log);
db.SaveChanges();
}
public void update(FilesModel model, string log)
{
WebDBConnection.Update(db.Database, "Files",
DBConnection.getSqlParameter(FilesModel.COL_Id.Name, model.Id),
DBConnection.getSqlParameter(FilesModel.COL_Branches_Id.Name, model.Branches_Id),
DBConnection.getSqlParameter(FilesModel.COL_DirectoryName.Name, model.DirectoryName),
DBConnection.getSqlParameter(FilesModel.COL_ParentId.Name, model.ParentId),
DBConnection.getSqlParameter(FilesModel.COL_Notes.Name, model.Notes)
);
ActivityLogsController.AddEditLog(db, Session, model.Id, log);
db.SaveChanges();
}
public Task DeleteAsync <T>(string key)
{
if (string.IsNullOrEmpty(key))
{
throw new ArgumentException("Key MUST have a value");
}
WebDBConnection.Delete(db.Database, TABLENAME,
DBConnection.getSqlParameter(COL_DB_Id, key)
);
return(Task.Delay(0));
}
public void update(LessonSessionsModel model, string log)
{
WebDBConnection.Update(db.Database, "LessonSessions",
DBConnection.getSqlParameter(LessonSessionsModel.COL_Id.Name, model.Id),
DBConnection.getSqlParameter(LessonSessionsModel.COL_HourlyRates_Rate.Name, model.HourlyRates_Rate),
DBConnection.getSqlParameter(LessonSessionsModel.COL_TravelCost.Name, model.TravelCost),
DBConnection.getSqlParameter(LessonSessionsModel.COL_TutorTravelCost.Name, model.TutorTravelCost),
DBConnection.getSqlParameter(LessonSessionsModel.COL_Review.Name, model.Review),
DBConnection.getSqlParameter(LessonSessionsModel.COL_InternalNotes.Name, model.InternalNotes)
);
ActivityLogsController.AddEditLog(db, Session, model.Id, log);
db.SaveChanges();
}
public void update(HourlyRatesModel model, string log)
{
WebDBConnection.Update(db.Database, "HourlyRates",
DBConnection.getSqlParameter(HourlyRatesModel.COL_Id.Name, model.Id),
DBConnection.getSqlParameter(HourlyRatesModel.COL_Branches_Id.Name, model.Branches_Id),
DBConnection.getSqlParameter(HourlyRatesModel.COL_LessonPackages_Id.Name, model.LessonPackages_Id),
DBConnection.getSqlParameter(HourlyRatesModel.COL_UserAccounts_Id.Name, model.UserAccounts_Id),
DBConnection.getSqlParameter(HourlyRatesModel.COL_Rate.Name, model.Rate),
DBConnection.getSqlParameter(HourlyRatesModel.COL_FullTimeTutorPayrate.Name, model.FullTimeTutorPayrate),
DBConnection.getSqlParameter(HourlyRatesModel.COL_Notes.Name, model.Notes)
);
ActivityLogsController.AddEditLog(db, Session, model.Id, log);
db.SaveChanges();
}
public static void add(PayrollPaymentItemsModel model)
{
WebDBConnection.Insert(new DBContext().Database, "PayrollPaymentItems",
DBConnection.getSqlParameter(PayrollPaymentItemsModel.COL_Id.Name, model.Id),
DBConnection.getSqlParameter(PayrollPaymentItemsModel.COL_PayrollPayments_Id.Name, model.PayrollPayments_Id),
DBConnection.getSqlParameter(PayrollPaymentItemsModel.COL_Timestamp.Name, model.Timestamp),
DBConnection.getSqlParameter(PayrollPaymentItemsModel.COL_Description.Name, model.Description),
DBConnection.getSqlParameter(PayrollPaymentItemsModel.COL_Hour.Name, model.Hour),
DBConnection.getSqlParameter(PayrollPaymentItemsModel.COL_HourlyRate.Name, model.HourlyRate),
DBConnection.getSqlParameter(PayrollPaymentItemsModel.COL_TutorTravelCost.Name, model.TutorTravelCost),
DBConnection.getSqlParameter(PayrollPaymentItemsModel.COL_Amount.Name, model.Amount),
DBConnection.getSqlParameter(PayrollPaymentItemsModel.COL_UserAccounts_Id.Name, model.UserAccounts_Id),
DBConnection.getSqlParameter(PayrollPaymentItemsModel.COL_CancelNotes.Name, model.CancelNotes),
DBConnection.getSqlParameter(PayrollPaymentItemsModel.COL_Branches_Id.Name, model.Branches_Id),
DBConnection.getSqlParameter(PayrollPaymentItemsModel.COL_IsFullTime.Name, model.IsFullTime)
);
}
public void add(HourlyRatesModel model)
{
model.Id = Guid.NewGuid();
WebDBConnection.Insert(db.Database, "HourlyRates",
DBConnection.getSqlParameter(HourlyRatesModel.COL_Id.Name, model.Id),
DBConnection.getSqlParameter(HourlyRatesModel.COL_Branches_Id.Name, model.Branches_Id),
DBConnection.getSqlParameter(HourlyRatesModel.COL_LessonPackages_Id.Name, model.LessonPackages_Id),
DBConnection.getSqlParameter(HourlyRatesModel.COL_UserAccounts_Id.Name, model.UserAccounts_Id),
DBConnection.getSqlParameter(HourlyRatesModel.COL_Rate.Name, model.Rate),
DBConnection.getSqlParameter(HourlyRatesModel.COL_FullTimeTutorPayrate.Name, model.FullTimeTutorPayrate),
DBConnection.getSqlParameter(HourlyRatesModel.COL_Notes.Name, model.Notes)
);
ActivityLogsController.AddCreateLog(db, Session, model.Id);
db.SaveChanges();
}
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 Task StoreAsync <T>(string key, T value)
{
if (string.IsNullOrEmpty(key))
{
throw new ArgumentException("Key MUST have a value");
}
var serialized = NewtonsoftJsonSerializer.Instance.Serialize(value);
WebDBConnection.Execute(db.Database, string.Format(@"
IF NOT EXISTS (SELECT * FROM {0} WHERE [{1}] = @{1})
INSERT INTO {0} ([{1}],{2}) VALUES (@{1},@{2})
ELSE
UPDATE {0} SET {2} = @{2} WHERE [{1}] = @{1}
", TABLENAME, COL_DB_Id, COL_DB_Token),
DBConnection.getSqlParameter(COL_DB_Id, key),
DBConnection.getUnsanitizedSqlParameter(COL_DB_Token, serialized)
);
return(Task.Delay(0));
}
public static void update(DBContext db, HttpSessionStateBase Session, PayrollPaymentItemsModel model)
{
PayrollPaymentItemsModel originalModel = get(Session, model.Id);
string log = string.Empty;
log = Helper.append(log, originalModel.HourlyRate, model.HourlyRate, PayrollPaymentItemsModel.COL_HourlyRate.LogDisplay);
log = Helper.append(log, originalModel.TutorTravelCost, model.TutorTravelCost, PayrollPaymentItemsModel.COL_TutorTravelCost.LogDisplay);
log = Helper.append(log, originalModel.Amount, model.Amount, PayrollPaymentItemsModel.COL_Amount.LogDisplay);
if (!string.IsNullOrEmpty(log))
{
WebDBConnection.Update(db.Database, "PayrollPaymentItems",
DBConnection.getSqlParameter(PayrollPaymentItemsModel.COL_Id.Name, model.Id),
DBConnection.getSqlParameter(PayrollPaymentItemsModel.COL_HourlyRate.Name, model.HourlyRate),
DBConnection.getSqlParameter(PayrollPaymentItemsModel.COL_TutorTravelCost.Name, model.TutorTravelCost),
DBConnection.getSqlParameter(PayrollPaymentItemsModel.COL_Amount.Name, model.Amount)
);
ActivityLogsController.AddEditLog(db, Session, model.Id, log);
db.SaveChanges();
}
}
public void update(UserAccountsModel model, string log)
{
if (model.Roles_List != null)
{
model.Roles = string.Join(",", model.Roles_List.ToArray());
}
if (model.Interest_List != null)
{
model.Interest = string.Join(",", model.Interest_List.ToArray());
}
if (model.Branches_List != null)
{
model.Branches = string.Join(",", model.Branches_List.ToArray());
}
WebDBConnection.Update(db.Database, "UserAccounts",
DBConnection.getSqlParameter(UserAccountsModel.COL_Id.Name, model.Id),
DBConnection.getSqlParameter(UserAccountsModel.COL_Username.Name, model.Username),
DBConnection.getSqlParameter(UserAccountsModel.COL_Fullname.Name, model.Fullname),
DBConnection.getSqlParameter(UserAccountsModel.COL_Birthday.Name, model.Birthday),
DBConnection.getSqlParameter(UserAccountsModel.COL_Branches_Id.Name, model.Branches_Id),
DBConnection.getSqlParameter(UserAccountsModel.COL_Branches.Name, model.Branches),
DBConnection.getSqlParameter(UserAccountsModel.COL_Active.Name, model.Active),
DBConnection.getSqlParameter(UserAccountsModel.COL_ResetPassword.Name, model.ResetPassword),
DBConnection.getSqlParameter(UserAccountsModel.COL_Email.Name, model.Email),
DBConnection.getSqlParameter(UserAccountsModel.COL_Address.Name, model.Address),
DBConnection.getSqlParameter(UserAccountsModel.COL_Phone1.Name, model.Phone1),
DBConnection.getSqlParameter(UserAccountsModel.COL_Phone2.Name, model.Phone2),
DBConnection.getSqlParameter(UserAccountsModel.COL_Notes.Name, model.Notes),
DBConnection.getSqlParameter(UserAccountsModel.COL_Interest.Name, model.Interest),
DBConnection.getSqlParameter(UserAccountsModel.COL_PromotionEvents_Id.Name, model.PromotionEvents_Id),
DBConnection.getSqlParameter(UserAccountsModel.COL_Roles.Name, model.Roles)
);
updateLoginSession(Session);
ActivityLogsController.AddEditLog(db, Session, model.Id, log);
db.SaveChanges();
}
public void add(PaymentsModel model)
{
WebDBConnection.Insert(db.Database, "Payments",
DBConnection.getSqlParameter(PaymentsModel.COL_Id.Name, model.Id),
DBConnection.getSqlParameter(PaymentsModel.COL_No.Name, model.No),
DBConnection.getSqlParameter(PaymentsModel.COL_Timestamp.Name, model.Timestamp),
DBConnection.getSqlParameter(PaymentsModel.COL_CashAmount.Name, model.CashAmount),
DBConnection.getSqlParameter(PaymentsModel.COL_DebitAmount.Name, model.DebitAmount),
DBConnection.getSqlParameter(PaymentsModel.COL_DebitBank.Name, model.DebitBank),
DBConnection.getSqlParameter(PaymentsModel.COL_DebitOwnerName.Name, model.DebitOwnerName),
DBConnection.getSqlParameter(PaymentsModel.COL_DebitNumber.Name, model.DebitNumber),
DBConnection.getSqlParameter(PaymentsModel.COL_DebitRefNo.Name, model.DebitRefNo),
DBConnection.getSqlParameter(PaymentsModel.COL_Consignments_Id.Name, model.Consignments_Id),
DBConnection.getSqlParameter(PaymentsModel.COL_ConsignmentAmount.Name, model.ConsignmentAmount),
DBConnection.getSqlParameter(PaymentsModel.COL_Notes.Name, model.Notes),
DBConnection.getSqlParameter(PaymentsModel.COL_Cancelled.Name, model.Cancelled),
DBConnection.getSqlParameter(PaymentsModel.COL_Approved.Name, model.Approved),
DBConnection.getSqlParameter(PaymentsModel.COL_IsTransfer.Name, model.IsTransfer),
DBConnection.getSqlParameter(PaymentsModel.COL_CancelNotes.Name, model.CancelNotes)
);
ActivityLogsController.AddCreateLog(db, Session, model.Id);
db.SaveChanges();
}
public Task ClearAsync()
{
WebDBConnection.DeleteAllRows(db.Database, TABLENAME);
return(Task.Delay(0));
}
