public IEnumerable <IStokBarangModel> GetStokBarangLogByDate(object date, object endDate = null) { var dataAccessStatus = new DataAccessStatus(); using (var context = new DbContext()) { string queryStr = "SELECT sb.barang_id [id], sb.tanggal, b.nama [barang_nama], SUM(sb.barang_masuk) [barang_masuk], " + "SUM(sb.barang_keluar) [barang_keluar] " + "FROM stok_barang as sb LEFT JOIN barang as b on sb.barang_id = b.id " + "WHERE {WHERE} " + "GROUP BY sb.barang_id " + "ORDER BY sb.barang_id"; object param = null; var dateStr = ((DateTime)date).ToString("yyyy-MM-dd"); if (endDate == null) { queryStr = queryStr.Replace("{WHERE}", "date(sb.tanggal) = @date"); param = new { date = dateStr }; } else { var endDateStr = ((DateTime)endDate).ToString("yyyy-MM-dd"); queryStr = queryStr.Replace("{WHERE}", "date(sb.tanggal) BETWEEN @date and @enddate"); param = new { date = dateStr, enddate = endDateStr }; } return(GetAll(() => context.Conn.Query <StokBarangLogModel>(queryStr, param), dataAccessStatus)); } }
public void Update(IBarangModel barangModel) { DataAccessStatus dataAccessStatus = new DataAccessStatus(); try { RecordExistsCheck((BarangModel)barangModel, TypeOfExistenceCheck.DoesExistInDB, RequestType.Update); } catch (DataAccessException ex) { ex.DataAccessStatusInfo.CustomMessage = "Barang tidak dapat diubah, dikarenakan data barang tidak ditemukan."; ex.DataAccessStatusInfo.ExceptionMessage = string.Copy(ex.Message); ex.DataAccessStatusInfo.StackTrace = string.Copy(ex.StackTrace); throw ex; } try { _context.Conn.Update((BarangModel)barangModel); } catch (SQLiteException ex) { dataAccessStatus.SetValues(status: "Error", operationSucceeded: false, exceptionMessage: ex.Message, customMessage: "Terjadi kesalahan saat menyimpan data Barang.", helpLink: ex.HelpLink, errorCode: ex.ErrorCode, stackTrace: ex.StackTrace); throw new DataAccessException(message: ex.Message, innerException: ex.InnerException, dataAccessStatus: dataAccessStatus); } }
public IUserModel LogIn(string loginID, string password) { var dataAccessStatus = new DataAccessStatus(); using (var context = new DbContext()) { var errorMessage = "Tidak dapat login ke aplikasi.\nLogin ID atau Password salah !!!"; var queryStr = "SELECT * FROM user WHERE login_id = @loginID"; var model = context.Conn.Query <UserModel>(queryStr, new { loginID }).FirstOrDefault(); if (model != null) { if (PasswordHash.ValidatePassword(password, model.password)) { errorMessage = string.Empty; return(model); } } if (!string.IsNullOrWhiteSpace(errorMessage)) { dataAccessStatus.Status = "Login Error"; dataAccessStatus.CustomMessage = errorMessage; throw new DataAccessException(dataAccessStatus); } } return(null); }
public IBarangModel GetById(object id) { var dataAccessStatus = new DataAccessStatus(); using (var context = new DbContext()) { return(GetBy(() => { var model = context.Conn.Get <BarangModel>(id); if (model != null) { var satuanModel = context.Conn.Get <SatuanModel>(model.satuan_id); if (satuanModel != null) { model.Satuan = satuanModel; } } return model; }, dataAccessStatus, () => CheckModelExist(context, id))); } }
public BarangModel GetById(int id) { BarangModel model = null; var dataAccessStatus = new DataAccessStatus(); try { model = _context.Conn.Get <BarangModel>(id); } catch (SQLiteException ex) { dataAccessStatus.SetValues(status: "Error", operationSucceeded: false, exceptionMessage: ex.Message, customMessage: "Tidak dapat mengambil data barang yang sesuai dengan permintaan id.", helpLink: ex.HelpLink, errorCode: ex.ErrorCode, stackTrace: ex.StackTrace); throw new DataAccessException(message: ex.Message, innerException: ex.InnerException, dataAccessStatus: dataAccessStatus); } if (model == null) { dataAccessStatus.SetValues(status: "Error", operationSucceeded: false, exceptionMessage: "", customMessage: "Data tidak ditemukan. " + $"Tidak dapat mengambil data barang yang sesuai dengan permintaan id {id}. " + $"ID {id} tidak ada di database.", helpLink: "", errorCode: 0, stackTrace: ""); throw new DataAccessException(dataAccessStatus: dataAccessStatus); } return(model); }
public IPembelianModel GetByNoNota(object noNota) { var dataAccessStatus = new DataAccessStatus(); using (var context = new DbContext()) { var queryStr = "SELECT * FROM pembelian WHERE no_nota = @noNota"; var model = context.Conn.Query <PembelianModel>(queryStr, new { noNota }).FirstOrDefault(); if (model != null) { var supplierModel = context.Conn.Get <SupplierModel>(model.supplier_id); if (supplierModel != null) { model.Supplier = supplierModel; } var pdRepo = new PembelianDetailRepository(context); model.PembelianDetails = pdRepo.GetAll(model); } return(model); } }
public void Delete(IPenyesuaianStokModel model) { var dataAccessStatus = new DataAccessStatus(); using (var context = new DbContext()) { Delete(model, () => { context.BeginTransaction(); var success = context.Conn.Delete((PenyesuaianStokModel)model, context.Transaction); if (success) { var barang = context.Conn.Get <BarangModel>(model.barang_id, context.Transaction); model.Barang = barang; model.Barang.stok += model.qty; context.Conn.Update((BarangModel)model.Barang, context.Transaction); context.Commit(); } }, dataAccessStatus, () => CheckModelExist(context, model.id)); } }
public void Delete(IPembelianModel model) { var dataAccessStatus = new DataAccessStatus(); using (var context = new DbContext()) { Delete(model, () => { context.BeginTransaction(); model.PembelianDetails = new PembelianDetailRepository(context).GetAll(model, context.Transaction).ToList(); var success = context.Conn.Delete((PembelianModel)model, context.Transaction); if (success) { if (model.PembelianDetails.ToList().Count > 0) { foreach (var pd in model.PembelianDetails) { pd.Barang.stok -= pd.qty; context.Conn.Update((BarangModel)pd.Barang, context.Transaction); } context.Commit(); } } }, dataAccessStatus, () => CheckModelExist(context, model.id)); } }
public IEnumerable <IPaycheckModel> GetByEmployee(int employeeID) { List <PaycheckModel> paychecks = new List <PaycheckModel>(); DataAccessStatus dataAccessStatus = new DataAccessStatus(); bool matchingRecoredFound = false; string selectByEmpQuery = "SELECT ID, Amount, PayrollID, ReceiptionDate " + "FROM Paychecks WHERE EmployeeID = @EmployeeID"; using (SqlConnection sqlConnection = new SqlConnection(this.connectionString)) { try { sqlConnection.Open(); using (SqlCommand cmd = new SqlCommand(selectByEmpQuery, sqlConnection)) { cmd.CommandText = selectByEmpQuery; cmd.Prepare(); cmd.Parameters.Add(new SqlParameter("@EmployeeID", employeeID)); using (SqlDataReader reader = cmd.ExecuteReader()) { matchingRecoredFound = reader.HasRows; while (reader.Read()) { PaycheckModel paycheck = new PaycheckModel(); paycheck.ID = Convert.ToInt32(reader["ID"].ToString()); paycheck.EmployeeID = employeeID; paycheck.PayrollID = Convert.ToInt32(reader["PayrollID"].ToString()); paycheck.Amount = Convert.ToInt32(reader["Amount"].ToString()); paycheck.ReceiptionDate = (DateTime)reader["ReceiptionDate"]; paychecks.Add(paycheck); } } } sqlConnection.Close(); } catch (SqlException e) { dataAccessStatus.setValues(status: "Error", operationSucceeded: false, exceptionMessage: e.Message, customMessage: "Unable to get the paychecks records from database", helpLink: e.HelpLink, errorCode: e.ErrorCode, stackTrace: e.StackTrace); throw new DataAccessException(e.Message, e.InnerException, dataAccessStatus); } if (!matchingRecoredFound) { dataAccessStatus.setValues(status: "Error", operationSucceeded: false, exceptionMessage: "", customMessage: $"Record not found!. Unable to get Paycheck record for with employee id: {employeeID}.It does not exist in the database.", helpLink: "", errorCode: 0, stackTrace: ""); throw new DataAccessException(dataAccessStatus); } return(paychecks); } }
public IEnumerable <IUserModel> GetAll() { var dataAccessStatus = new DataAccessStatus(); using (var context = new DbContext()) { return(GetAll(() => { var listObjs = context.Conn.GetAll <UserModel>(); if (listObjs != null && listObjs.ToList().Count > 0) { listObjs = listObjs.Map(u => { var queryStr = "SELECT * FROM role WHERE kode = @kode"; var roleModel = context.Conn.Query <RoleModel>(queryStr, new { kode = u.role_kode }).FirstOrDefault(); if (roleModel != null) { u.Role = roleModel; } } ); } return listObjs; }, dataAccessStatus)); } }
public IPenjualanModel GetByNoNota(object noNota) { var dataAccessStatus = new DataAccessStatus(); using (var context = new DbContext()) { var queryStr = "SELECT * FROM penjualan WHERE no_nota = @noNota"; var model = context.Conn.Query <PenjualanModel>(queryStr, new { noNota }).FirstOrDefault(); if (model != null) { if (model.pelanggan_id != default(uint)) { var pelangganModel = context.Conn.Get <PelangganModel>(model.pelanggan_id); if (pelangganModel != null) { model.Pelanggan = pelangganModel; } } var pdRepo = new PenjualanDetailRepository(context); model.PenjualanDetails = pdRepo.GetAll(model); } return(model); } }
protected void Update(TDomainModel model, Action updatetMethod, DataAccessStatus dataAccessStatus, Func <bool> checkExist) { try { RecordExistsCheck(model, TypeOfExistenceCheck.DoesExistInDB, RequestType.Update, checkExist: checkExist()); } catch (DataAccessException ex) { SetDataAccessValues(ex, ErrorMessageType.ModelNotFound); throw ex; } try { updatetMethod(); } catch (SqlException ex) { dataAccessStatus = SetDataAccessValues(ex, ErrorMessageType.Update); throw new DataAccessException(message: ex.Message, innerException: ex.InnerException, dataAccessStatus: dataAccessStatus); } }
protected void Insert(TDomainModel model, Action insertMethod, DataAccessStatus dataAccessStatus, Func <bool> checkIAftertInsert) { try { insertMethod(); } catch (SqlException ex) { dataAccessStatus = SetDataAccessValues(ex, ErrorMessageType.Insert); throw new DataAccessException(message: ex.Message, innerException: ex.InnerException, dataAccessStatus: dataAccessStatus); } try { RecordExistsCheck(model, TypeOfExistenceCheck.DoesExistInDB, RequestType.ConfirmInsert, checkAfterInsert: checkIAftertInsert()); } catch (DataAccessException ex) { SetDataAccessValues(ex, ErrorMessageType.AfterInsert); throw ex; } }
public void Delete(IRoleDetailModel model) { var dataAccessStatus = new DataAccessStatus(); try { using (var context = new DbContext()) { string queryStr = "DELETE FROM role_detail WHERE (role_kode = @kode AND menu_name = @parent) " + "OR (role_kode = @kode AND menu_parent = @parent)"; context.Conn.Execute(queryStr, new { kode = model.role_kode, menu = model.menu_parent, parent = model.menu_parent }); } } catch (MySqlException ex) { dataAccessStatus = SetDataAccessValues(ex, ErrorMessageType.Update); throw new DataAccessException(message: ex.Message, innerException: ex.InnerException, dataAccessStatus: dataAccessStatus); } }
public void Update(IPenyesuaianStokModel model) { var dataAccessStatus = new DataAccessStatus(); using (var context = new DbContext()) { context.BeginTransaction(); Update(model, () => { var barang = context.Conn.Get <BarangModel>(model.barang_id, context.Transaction); model.Barang = barang; var qtyOld = context.Conn.Get <PenyesuaianStokModel>(model.id, context.Transaction).qty; model.Barang.stok += qtyOld; context.Conn.Update((PenyesuaianStokModel)model, context.Transaction); CheckBarangStok(model, dataAccessStatus); context.Conn.Update((BarangModel)model.Barang, context.Transaction); context.Commit(); }, dataAccessStatus, () => CheckModelExist(context, model.id)); } }
public DepartmentModel GetByID(int departmentId) { DepartmentModel departmentModel = new DepartmentModel(); DataAccessStatus dataAccessStatus = new DataAccessStatus(); bool matchingRecoredFound = false; string selectByIdQuery = "SELECT [ID], [Name], [PhoneNumber], [ManagerID] " + "FROM [Departments] WHERE [ID] = @DepartmentID"; using (SqlConnection sqlConnection = new SqlConnection(this.connectionString)) { try { sqlConnection.Open(); using (SqlCommand cmd = new SqlCommand(selectByIdQuery, sqlConnection)) { cmd.CommandText = selectByIdQuery; cmd.Prepare(); cmd.Parameters.Add(new SqlParameter("@DepartmentID", departmentId)); using (SqlDataReader reader = cmd.ExecuteReader()) { matchingRecoredFound = reader.HasRows; while (reader.Read()) { departmentModel.DepartmentId = departmentId; departmentModel.DepartmentName = reader["Name"].ToString(); departmentModel.PhoneNumber = reader["PhoneNumber"].ToString(); if (!string.IsNullOrEmpty(reader["ManagerID"].ToString())) { departmentModel.ManagerID = Int32.Parse(reader["ManagerID"].ToString()); } } } } sqlConnection.Close(); } catch (SqlException e) { dataAccessStatus.setValues(status: "Error", operationSucceeded: false, exceptionMessage: e.Message, customMessage: "Unable to get Department Model record from database", helpLink: e.HelpLink, errorCode: e.ErrorCode, stackTrace: e.StackTrace); throw new DataAccessException(e.Message, e.InnerException, dataAccessStatus); } if (!matchingRecoredFound) { dataAccessStatus.setValues(status: "Error", operationSucceeded: false, exceptionMessage: "", customMessage: $"Record not found!. Unable to get Department Model record for department id {departmentId}. Id {departmentId} does not exist in the database.", helpLink: "", errorCode: 0, stackTrace: ""); throw new DataAccessException(dataAccessStatus); } return(departmentModel); } }
public IEnumerable <IPaycheckModel> GetByMonth(DateTime date) { List <PaycheckModel> paychecks = new List <PaycheckModel>(); DataAccessStatus dataAccessStatus = new DataAccessStatus(); bool recordsFound = false; string selectQuery = "Select * FROM Paychecks WHERE Month(ReceiptionDate) = @Month AND YEAR(ReceiptionDate) = @Year "; using (SqlConnection sqlConnection = new SqlConnection(this.connectionString)) { try { sqlConnection.Open(); } catch (SqlException e) { dataAccessStatus.setValues(status: "Error", operationSucceeded: false, exceptionMessage: e.Message, customMessage: "Unable to get the paychecks records from database", helpLink: e.HelpLink, errorCode: e.ErrorCode, stackTrace: e.StackTrace); throw new DataAccessException(e.Message, e.InnerException, dataAccessStatus); } using (SqlCommand cmd = new SqlCommand(selectQuery, sqlConnection)) { cmd.CommandText = selectQuery; cmd.Prepare(); cmd.Parameters.AddWithValue("@Month", date.Month); cmd.Parameters.AddWithValue("@Year", date.Year); using (SqlDataReader reader = cmd.ExecuteReader()) { recordsFound = reader.HasRows; while (reader.Read()) { PaycheckModel paycheck = new PaycheckModel(); paycheck.ID = Convert.ToInt32(reader["ID"].ToString()); paycheck.EmployeeID = Convert.ToInt32(reader["EmployeeID"].ToString());; paycheck.PayrollID = Convert.ToInt32(reader["PayrollID"].ToString()); paycheck.Amount = Convert.ToInt32(reader["Amount"].ToString()); paycheck.ReceiptionDate = (DateTime)reader["ReceiptionDate"]; paychecks.Add(paycheck); } } } sqlConnection.Close(); } if (!recordsFound) { dataAccessStatus.setValues(status: "Error", operationSucceeded: false, exceptionMessage: "", customMessage: $"Record not found!. Unable to get Paycheck records in Month {date.Month}.", helpLink: "", errorCode: 0, stackTrace: ""); throw new DataAccessException(dataAccessStatus); } return(paychecks); }
public IEnumerable <IPengeluaranModel> GetByDate(object startDate, object endDate) { var dataAccessStatus = new DataAccessStatus(); using (var context = new DbContext()) { return(context.Conn.Query <PengeluaranModel>(StringHelper.QueryStringByBetweenDate(_modelName), new { startDate, endDate })); } }
public IEnumerable <IPelangganModel> GetAll() { var dataAccessStatus = new DataAccessStatus(); using (var context = new DbContext()) { return(GetAll(() => { return context.Conn.GetAll <PelangganModel>().ToList(); }, dataAccessStatus)); } }
public void Insert(IPenyesuaianStokModel model) { var dataAccessStatus = new DataAccessStatus(); using (var context = new DbContext()) { context.BeginTransaction(); Insert(model, () => { var barang = context.Conn.Get <BarangModel>(model.barang_id); if (barang != null) { model.Barang = barang; } else { var ex = new DataAccessException(dataAccessStatus); SetDataAccessValues(ex, "Salah satu barang yang ingin ditambahkan ke dalam tabel penyesuaian stok tidak ditemukan."); throw ex; } if (barang.hpp == 0) { var ex = new DataAccessException(dataAccessStatus); SetDataAccessValues(ex, "Salah satu barang yang ingin dimasukan ke dalam tabel penyesuaian stok " + "belum memiliki hpp."); throw ex; } else { context.Conn.Insert((PenyesuaianStokModel)model, context.Transaction); var statusBarangModel = new StatusBarangModel(); var queryStr = "SELECT SUM(stok) FROM barang"; var stokAwal = context.Conn.ExecuteScalar <int>(queryStr); statusBarangModel.stok_awal = stokAwal; statusBarangModel.tanggal = model.tanggal; statusBarangModel.PenyesuaianStok = model; context.Conn.Insert(statusBarangModel, context.Transaction); CheckBarangStok(model, dataAccessStatus); context.Conn.Update((BarangModel)model.Barang, context.Transaction); context.Commit(); } }, dataAccessStatus, () => CheckAfterInsert(context, "SELECT COUNT(1) FROM penyesuaian_stok WHERE barang_id = @barang_id " + "AND id=(SELECT id FROM penyesuaian_stok ORDER BY ID DESC LIMIT 1)", new { model.barang_id })); } }
public IEnumerable <ITipeBarangModel> GetAll() { var dataAccessStatus = new DataAccessStatus(); using (var context = new DbContext()) { return(GetAll(() => context.Conn.GetAll <TipeBarangModel>(), dataAccessStatus)); } }
public IEnumerable <ISubTipeModel> GetAll() { var dataAccessStatus = new DataAccessStatus(); using (var context = new DbContext()) { return(GetAll(() => { return context.Conn.GetAll <SubTipeModel>(); }, dataAccessStatus)); } }
public IEnumerable <IHutangOperasionalModel> GetAll() { var dataAccessStatus = new DataAccessStatus(); using (var context = new DbContext()) { return(GetAll(() => context.Conn.GetAll <HutangOperasionalModel>(), dataAccessStatus)); } }
public AccountModel GetByUsername(string username) { AccountModel account = new AccountModel(); DataAccessStatus dataAccessStatus = new DataAccessStatus(); bool matchingRecoredFound = false; string selectByIdQuery = "SELECT ID, Password, EmployeeID, RoleID " + "FROM Accounts WHERE Username = @User"; using (SqlConnection sqlConnection = new SqlConnection(this.connectionString)) { try { sqlConnection.Open(); using (SqlCommand cmd = new SqlCommand(selectByIdQuery, sqlConnection)) { cmd.CommandText = selectByIdQuery; cmd.Prepare(); cmd.Parameters.Add(new SqlParameter("@User", username)); using (SqlDataReader reader = cmd.ExecuteReader()) { matchingRecoredFound = reader.HasRows; while (reader.Read()) { account.ID = Convert.ToInt32(reader["ID"].ToString()); account.Username = username; account.Password = reader["Password"].ToString(); account.EmployeeID = Convert.ToInt32(reader["EmployeeID"].ToString()); account.RoleID = Convert.ToInt32(reader["RoleID"].ToString()); } } } sqlConnection.Close(); } catch (SqlException e) { dataAccessStatus.setValues(status: "Error", operationSucceeded: false, exceptionMessage: e.Message, customMessage: "Unable to get the account record from database", helpLink: e.HelpLink, errorCode: e.ErrorCode, stackTrace: e.StackTrace); throw new DataAccessException(e.Message, e.InnerException, dataAccessStatus); } if (!matchingRecoredFound) { dataAccessStatus.setValues(status: "Error", operationSucceeded: false, exceptionMessage: "", customMessage: $"Record not found!. Unable to get account record for with username : {username}. {username} does not exist in the database.", helpLink: "", errorCode: 0, stackTrace: ""); throw new DataAccessException(dataAccessStatus); } return(account); } }
public void Delete(IBarangModel model) { var dataAccessStatus = new DataAccessStatus(); using (var context = new DbContext()) { Delete(model, () => context.Conn.Delete((BarangModel)model), dataAccessStatus, () => CheckModelExist(context, model.id)); } }
public void Update(IPengeluaranModel model) { var dataAccessStatus = new DataAccessStatus(); using (var context = new DbContext()) { Update(model, () => context.Conn.Update((PengeluaranModel)model), dataAccessStatus, () => CheckModelExist(context, model.id)); } }
public IPenjualanReturnModel GetById(object id) { var dataAccessStatus = new DataAccessStatus(); using (var context = new DbContext()) { return(GetBy(() => context.Conn.Get <PenjualanReturnModel>(id), dataAccessStatus, () => CheckModelExist(context, id))); } }
public ISubTipeModel GetById(object id) { var dataAccessStatus = new DataAccessStatus(); using (var context = new DbContext()) { return(GetBy(() => { return context.Conn.Get <SubTipeModel>(id); }, dataAccessStatus, () => CheckModelExist(context, id))); } }
public IHutangOperasionalModel GetById(object id) { var dataAccessStatus = new DataAccessStatus(); using (var context = new DbContext()) { return(GetBy(() => context.Conn.Get <HutangOperasionalModel>(id), dataAccessStatus, () => CheckModelExist(context, id))); } }
public void Update(IHutangOperasionalModel model) { var dataAccessStatus = new DataAccessStatus(); using (var context = new DbContext()) { Update(model, () => context.Conn.Update((HutangOperasionalModel)model), dataAccessStatus, () => CheckModelExist(context, model.id)); } }