public void Update(MutasiKasModel model)
{
var sSql = @"
UPDATE
MutasiKas
SET
Tgl = @Tgl,
Jam = @Jam,
PegawaiID = @PegawaiID,
JenisKasIDAsal = @JenisKasIDAsal,
JenisKasIDTujuan = @JenisKasIDTujuan,
Keterangan = @Keterangan,
NilaiKas = @NilaiKas
WHERE
MutasiKasID = @MutasiKasID ";
using (var conn = new SqlConnection(_connString))
using (var cmd = new SqlCommand(sSql, conn))
{
cmd.AddParam("@MutasiKasID", model.MutasiKasID);
cmd.AddParam("@Tgl", model.Tgl.ToTglYMD());
cmd.AddParam("@Jam", model.Jam);
cmd.AddParam("@PegawaiID", model.PegawaiID);
cmd.AddParam("@JenisKasIDAsal", model.JenisKasIDAsal);
cmd.AddParam("@JenisKasIDTujuan", model.JenisKasIDTujan);
cmd.AddParam("@Keterangan", model.Keterangan);
cmd.AddParam("@NilaiKas", model.NilaiKas);
conn.Open();
cmd.ExecuteNonQuery();
}
}
public void Insert(MutasiKasModel model)
{
var sSql = @"
INSERT INTO
MutasiKas (
MutasiKasID, Tgl, Jam, PegawaiID,
JenisKasIDAsal, JenisKasIDTujuan, Keterangan,
NilaiKas)
VALUES (
@MutasiKasID, @Tgl, @Jam, @PegawaiID,
@JenisKasIDAsal, @JenisKasIDTujuan, @Keterangan,
@NilaiKas) ";
using (var conn = new SqlConnection(_connString))
using (var cmd = new SqlCommand(sSql, conn))
{
cmd.AddParam("@MutasiKasID", model.MutasiKasID);
cmd.AddParam("@Tgl", model.Tgl.ToTglYMD());
cmd.AddParam("@Jam", model.Jam);
cmd.AddParam("@PegawaiID", model.PegawaiID);
cmd.AddParam("@JenisKasIDAsal", model.JenisKasIDAsal);
cmd.AddParam("@JenisKasIDTujuan", model.JenisKasIDTujan);
cmd.AddParam("@Keterangan", model.Keterangan);
cmd.AddParam("@NilaiKas", model.NilaiKas);
conn.Open();
cmd.ExecuteNonQuery();
}
}
public BPKasModel Generate(MutasiKasModel mutasiKas)
{
if (mutasiKas == null)
{
throw new ArgumentNullException(nameof(mutasiKas));
}
var bpKas = (BPKasModel)mutasiKas;
var result = Save(bpKas);
return(result);
}
public IEnumerable <MutasiKasModel> ListData(string tgl1, string tgl2)
{
List <MutasiKasModel> result = null;
var sSql = @"
SELECT
aa.MutasiKasID, aa.Tgl, aa.Jam, aa.PegawaiID,
aa.JenisKasIDAsal, aa.JenisKasIDTujuan, aa.Keterangan,
aa.NilaiKas,
ISNULL(bb.PegawaiName, '') PegawaiName,
ISNULL(cc.JenisKasName, '') JenisKasNameAsal,
ISNULL(dd.JenisKasName, '') JenisKasNameTujuan
FROM
MutasiKas aa
LEFT JOIN Pegawai bb ON aa.PegawaiID = bb.PegawaiID
LEFT JOIN JenisKas cc ON aa.JenisKasIDAsal = cc.JenisKasID
LEFT JOIN JenisKas dd oN aa.JenisKasIDTujuan = dd.JenisKasID
WHERE
Tgl BETWEEN @Tgl1 AND @Tgl2 ";
using (var conn = new SqlConnection(_connString))
using (var cmd = new SqlCommand(sSql, conn))
{
cmd.AddParam("@Tgl1", tgl1.ToTglYMD());
cmd.AddParam("@Tgl2", tgl2.ToTglYMD());
conn.Open();
using (var dr = cmd.ExecuteReader())
{
if (!dr.HasRows)
{
return(null);
}
result = new List <MutasiKasModel>();
while (dr.Read())
{
var item = new MutasiKasModel
{
MutasiKasID = dr["MutasiKasID"].ToString(),
Tgl = dr["Tgl"].ToString().ToTglDMY(),
Jam = dr["Jam"].ToString(),
PegawaiID = dr["PegawaiID"].ToString(),
PegawaiName = dr["PegawaiName"].ToString(),
JenisKasIDAsal = dr["JenisKasIDAsal"].ToString(),
JenisKasNameAsal = dr["JenisKasNameAsal"].ToString(),
JenisKasIDTujan = dr["JenisKasIDTujuan"].ToString(),
JenisKasNameTujuan = dr["JenisKasNameTujuan"].ToString(),
Keterangan = dr["Keterangan"].ToString(),
NilaiKas = Convert.ToDecimal(dr["NilaiKas"])
};
result.Add(item);
}
}
}
return(result);
}
public MutasiKasModel GetData(string id)
{
MutasiKasModel result = null;
var sSql = @"
SELECT
aa.MutasiKasID, aa.Tgl, aa.Jam, aa.PegawaiID,
aa.JenisKasIDAsal, aa.JenisKasIDTujuan, aa.Keterangan,
aa.NilaiKas,
ISNULL(bb.PegawaiName, '') PegawaiName,
ISNULL(cc.JenisKasName, '') JenisKasNameAsal,
ISNULL(dd.JenisKasName, '') JenisKasNameTujuan
FROM
MutasiKas aa
LEFT JOIN Pegawai bb ON aa.PegawaiID = bb.PegawaiID
LEFT JOIN JenisKas cc ON aa.JenisKasIDAsal = cc.JenisKasID
LEFT JOIN JenisKas dd oN aa.JenisKasIDTujuan = dd.JenisKasID
WHERE
MutasiKasID = @MutasiKasID ";
using (var conn = new SqlConnection(_connString))
using (var cmd = new SqlCommand(sSql, conn))
{
cmd.AddParam("@MutasiKasID", id);
conn.Open();
using (var dr = cmd.ExecuteReader())
{
if (!dr.HasRows)
{
return(null);
}
dr.Read();
result = new MutasiKasModel
{
MutasiKasID = id,
Tgl = dr["Tgl"].ToString().ToTglDMY(),
Jam = dr["Jam"].ToString(),
PegawaiID = dr["PegawaiID"].ToString(),
PegawaiName = dr["PegawaiName"].ToString(),
JenisKasIDAsal = dr["JenisKasIDAsal"].ToString(),
JenisKasNameAsal = dr["JenisKasNameAsal"].ToString(),
JenisKasIDTujan = dr["JenisKasIDTujuan"].ToString(),
JenisKasNameTujuan = dr["JenisKasNameTujuan"].ToString(),
Keterangan = dr["Keterangan"].ToString(),
NilaiKas = Convert.ToDecimal(dr["NilaiKas"])
};
}
}
return(result);
}
public void Save()
{
var mutasiKas = new MutasiKasModel
{
MutasiKasID = _view.MutasiKasID,
Tgl = _view.Tgl,
Jam = _view.Jam,
Keterangan = _view.Keterangan,
PegawaiID = _view.PegawaiID,
PegawaiName = _view.PegawaiName,
JenisKasIDAsal = _view.JenisKasIDAsal,
JenisKasNameAsal = _view.JenisKasNameAsal,
JenisKasIDTujan = _view.JenisKasIDTujuan,
NilaiKas = _view.NilaiKas
};
using (var trans = TransHelper.NewScope())
{
_dep.MutasiKasBL.Save(mutasiKas);
_dep.BPKasBL.Generate(mutasiKas);
New();
trans.Complete();
}
}
public MutasiKasModel Save(MutasiKasModel model)
{
if (model == null)
{
throw new ArgumentNullException(nameof(model));
}
// cek pegawai
var peg = dep.PegawaiDal.GetData(model.PegawaiID);
if (peg == null)
{
throw new ArgumentException("PegawaiID invalid");
}
else
{
model.PegawaiName = peg.PegawaiName;
}
// jenis kas asal
var jenisKas = dep.JenisKasDal.GetData(model.JenisKasIDAsal);
if (jenisKas == null)
{
throw new ArgumentException("JenisKas Asal invalid");
}
else
{
model.JenisKasNameAsal = jenisKas.JenisKasName;
}
// jenis kas tujuan
jenisKas = dep.JenisKasDal.GetData(model.JenisKasIDTujan);
if (jenisKas == null)
{
throw new ArgumentException("JenisKas Tujuan invalid");
}
else
{
model.JenisKasNameTujuan = jenisKas.JenisKasName;
}
// jenis kas asal dan tujuan harus beda
if (model.JenisKasIDAsal == model.JenisKasIDTujan)
{
throw new ArgumentException("JenisKas Asal dan Tujuan sama");
}
// nilai kas tidak boleh Nol
if (model.NilaiKas == 0)
{
throw new ArgumentException("Nilai Kas nol");
}
// proses save
using (var trans = TransHelper.NewScope())
{
// generate id baru (jika kosong)
if (model.MutasiKasID.Trim() == "")
{
model.MutasiKasID = GenNewID();
}
// hapus data lama
dep.MutasiKasDal.Delete(model.MutasiKasID);
// insert data lama
dep.MutasiKasDal.Insert(model);
// commit trans
trans.Complete();
}
return(model);
}
