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