public void Insert(LaporanDinasModel entity)
{
var sql = @"
INSERT INTO
OFTA_LaporanDinas (
LaporanDinasID, TglJamCreate,
ReportedPegID, SuratDinasID, TglSelesai,
HasilKerja, KMAkhir, DiketahuiPegID,
IsSignedDiketahui)
VALUES (
@LaporanDinasID, @TglJamCreate,
@ReportedPegID, @SuratDinasID, @TglSelesai,
@HasilKerja, @KMAkhir, @DiketahuiPegID,
@IsSignedDiketahui)";
using (var conn = new SqlConnection(ConnStringHelper.Get()))
using (var cmd = new SqlCommand(sql, conn))
{
cmd.AddParam("@LaporanDinasID", entity.LaporanDinasID, SqlDbType.VarChar);
cmd.AddParam("@TglJamCreate", entity.TglJamCreate, SqlDbType.DateTime);
cmd.AddParam("@ReportedPegID", entity.PegID, SqlDbType.VarChar);
cmd.AddParam("@SuratDinasID", entity.SuratDinasID, SqlDbType.VarChar);
cmd.AddParam("@TglSelesai", entity.TglSelesai, SqlDbType.DateTime);
cmd.AddParam("@HasilKerja", entity.HasilKerja, SqlDbType.VarChar);
cmd.AddParam("@KMAkhir", entity.KMAkhir, SqlDbType.BigInt);
conn.Open();
cmd.ExecuteNonQuery();
}
}
public void Update(LaporanDinasModel entity)
{
var sql = @"
UPDATE
OFTA_LaporanDinas
SET
TglJamCreate = @TglJamCreate,
ReportedPegID = @ReportedPegID,
SuratDinasID = @SuratDinasID,
TglSelesai = @TglSelesai,
HasilKerja = @HasilKerja,
KMAkhir = @KMAkhir,
DiketahuiPegID = @DiketahuiPegID,
IsSignedDiketahui = @IsSignedDiketahui
WHERE
LaporanDinasID = @LaporanDinasID ";
using (var conn = new SqlConnection(ConnStringHelper.Get()))
using (var cmd = new SqlCommand(sql, conn))
{
cmd.AddParam("@LaporanDinasID", entity.LaporanDinasID, SqlDbType.VarChar);
cmd.AddParam("@TglJamCreate", entity.TglJamCreate, SqlDbType.DateTime);
cmd.AddParam("@PegID", entity.PegID, SqlDbType.VarChar);
cmd.AddParam("@SuratDinasID", entity.SuratDinasID, SqlDbType.VarChar);
cmd.AddParam("@TglSelesai", entity.TglSelesai, SqlDbType.DateTime);
cmd.AddParam("@HasilKerja", entity.HasilKerja, SqlDbType.VarChar);
cmd.AddParam("@KMAkhir", entity.KMAkhir, SqlDbType.BigInt);
conn.Open();
cmd.ExecuteNonQuery();
}
}
private LaporanDinasModel R05_HasilKerjaTidakBolehKosong(LaporanDinasModel ld)
{
if (ld.HasilKerja.Length == 0)
{
throw new ArgumentException("'HasilKerja' empty");
}
return(ld);
}
private LaporanDinasModel R04_TglSelesaiSetelahTglMulaiDiSuratDinas(LaporanDinasModel ld)
{
if (ld.TglSelesai <= ld.TglMulai)
{
throw new ArgumentException("'Tgl Selesai/Mulai' invalid");
}
return(ld);
}
private LaporanDinasModel R06_OpsiKendaraanOperasionalKMAkhirHarusTerisi(LaporanDinasModel ld)
{
if (ld.KMAkhir <= 0)
{
throw new ArgumentException("'KM Akhir' empty");
}
return(ld);
}
private LaporanDinasModel R03_PegIDRequestAndReportHarusSama(LaporanDinasModel ld)
{
var sd = _suratDinasDal.GetData(ld);
if (sd.PegID != ld.PegID)
{
throw new ArgumentException("PegID Report berbeda dengan Surat Dinas");
}
return(ld);
}
private LaporanDinasModel R08_DisetujuiAtasanHarusTerdaftarDiDatabase(LaporanDinasModel ld)
{
var peg = _pegDal.GetData(ld);
if (peg is null)
{
throw new ArgumentException("PegID Atasan invalid");
}
ld.PegName = peg.PegName;
return(ld);
}
private LaporanDinasModel R02_SuratDinasIDHarusTerdaftar(LaporanDinasModel ld)
{
var suratdinas = _suratDinasDal.GetData(ld);
if (suratdinas is null)
{
throw new ArgumentException("'suratdinas' invalid");
}
else
{
ld.SuratDinasID = suratdinas.SuratDinasID;
}
return(ld);
}
private LaporanDinasModel R01_PegIDHarusVaid(LaporanDinasModel ld)
{
var peg = _pegDal.GetData(ld);
if (peg is null)
{
throw new ArgumentException("'PegID' invalid");
}
else
{
ld.PegName = peg.PegName;
}
return(ld);
}
public IEnumerable<LaporanDinasModel> ListData(DateTime tgl1, DateTime tgl2)
{
List<LaporanDinasModel> result = null;
var sql = @"
SELECT
aa.LaporanDinasID, aa.TglJamCreate,
aa.PegID, aa.SuratDinasID, aa.TglSelesai,
aa.HasilKerja, aa.KMAkhir,
aa.IsSignedDiketahui,
ISNULL(bb.PegName, '') PegName,
ISNULL(dd.TglMulai, '1900-01-01T00:00:00') TglMulai
FROM
OFTA_LaporanDinas aa
LEFT JOIN SuratDinas dd ON aa.SuratDinasID = dd.SuratDinasID
WHERE
aa.TglJamCreate BETWEEN @Tgl1 AND @Tgl2 ";
using (var conn = new SqlConnection(ConnStringHelper.Get()))
using (var cmd = new SqlCommand(sql, conn))
{
cmd.AddParam("@Tgl1", tgl1, SqlDbType.DateTime);
cmd.AddParam("@Tgl2", tgl2, SqlDbType.DateTime);
conn.Open();
using (var dr = cmd.ExecuteReader())
{
if (!dr.HasRows)
return null;
result = new List<LaporanDinasModel>();
while (dr.Read())
{
var item = new LaporanDinasModel
{
LaporanDinasID = dr["LaporanDinasID"].ToString(),
TglJamCreate = Convert.ToDateTime(dr["TglJamCreate"]),
PegID = dr["ReportedPegID"].ToString(),
PegName = dr["ReportedPegName"].ToString(),
SuratDinasID = dr["SuratDinasID"].ToString(),
TglMulai = Convert.ToDateTime(dr["TglMulai"]),
TglSelesai = Convert.ToDateTime(dr["TglSelesai"]),
HasilKerja = dr["HasilKerja"].ToString(),
KMAkhir = Convert.ToInt64(dr["KMAkhir"]),
};
result.Add(item);
}
}
}
return result;
}
public LaporanDinasModel GetData(ILaporanDinasKey key)
{
LaporanDinasModel result = null;
var sql = @"
SELECT
aa.LaporanDinasID, aa.TglJamCreate,
aa.ReportedPegID, aa.SuratDinasID,
aa.TglSelesai, aa.HasilKerja, aa.KMAkhir,
aa.DiketahuiPegID, aa.IsSignedDiketahui,
ISNULL( bb.PegName,'') ReportedPegName,
ISNULL( cc.PegName, '') DiketahuiPegName,
ISNULL( dd.TglMulai, '1900-01-01T00:00:00') TglMulai
FROM
OFTA_LaporanDinas aa
LEFT JOIN Peg bb ON aa.ReportedPegID = bb.PegID
LEFT JOIN Peg cc ON aa.DiketahuiPegID = cc.PegID
LEFT JOIN SuratDinas dd ON aa.SuratDinasID = dd.SuratDinasID
WHERE
LaporanDinasID = @LaporanDinasID ";
using (var conn = new SqlConnection(ConnStringHelper.Get()))
using (var cmd = new SqlCommand(sql, conn))
{
cmd.AddParam("@LaporanDinasID", key.LaporanDinasID, SqlDbType.VarChar);
conn.Open();
using (var dr = cmd.ExecuteReader())
{
if (!dr.HasRows)
return null;
dr.Read();
result = new LaporanDinasModel
{
LaporanDinasID = dr["LaporanDinasID"].ToString(),
TglJamCreate = Convert.ToDateTime(dr["TglJamCreate"]),
PegID = dr["ReportedPegID"].ToString(),
PegName = dr["ReportedPegName"].ToString(),
SuratDinasID = dr["SuratDinasID"].ToString(),
TglMulai = Convert.ToDateTime(dr["TglMulai"]),
TglSelesai = Convert.ToDateTime(dr["TglSelesai"]),
HasilKerja = dr["HasilKerja"].ToString(),
KMAkhir = Convert.ToInt64(dr["KMAkhir"])
};
}
}
return result;
}
public LaporanDinasModel Add(LaporanDinasAddDto laporanDinas)
{
// validate argument
// convert DTO >> Model
var ld = new LaporanDinasModel
{
TglJamCreate = DateTime.Now,
PegID = laporanDinas.PegID,
SuratDinasID = laporanDinas.SuratDinasID,
TglMulai = laporanDinas.TglMulai,
TglSelesai = laporanDinas.TglSelesai,
HasilKerja = laporanDinas.HasilKerja,
KMAkhir = laporanDinas.KMAkhir
};
if (ld is null)
{
throw new ArgumentNullException(nameof(ld));
}
ld = R01_PegIDHarusVaid(ld);
ld = R02_SuratDinasIDHarusTerdaftar(ld);
ld = R03_PegIDRequestAndReportHarusSama(ld);
ld = R04_TglSelesaiSetelahTglMulaiDiSuratDinas(ld);
ld = R05_HasilKerjaTidakBolehKosong(ld);
ld = R06_OpsiKendaraanOperasionalKMAkhirHarusTerisi(ld);
ld = R07_OtherTransportKMAkhirSet0(ld);
ld = R08_DisetujuiAtasanHarusTerdaftarDiDatabase(ld);
ld = R09_IsSignedDiketahuiDisetFalse(ld);
//proses simpan
using (var trans = TransHelper.NewScope())
{
//generate id
ld.LaporanDinasID = _paramNoBL.GenNewID("LD", ParamNoLengthEnum.Code_13);
//insert ke db
_laporanDinasDal.Insert(ld);
//commit changes
trans.Complete();
}
return(ld);
}
public LaporanDinasModel Update(LaporanDinasModel ld)
{
ld = R01_PegIDHarusVaid(ld);
ld = R02_SuratDinasIDHarusTerdaftar(ld);
ld = R03_PegIDRequestAndReportHarusSama(ld);
ld = R04_TglSelesaiSetelahTglMulaiDiSuratDinas(ld);
ld = R05_HasilKerjaTidakBolehKosong(ld);
ld = R06_OpsiKendaraanOperasionalKMAkhirHarusTerisi(ld);
ld = R07_OtherTransportKMAkhirSet0(ld);
ld = R08_DisetujuiAtasanHarusTerdaftarDiDatabase(ld);
ld = R09_IsSignedDiketahuiDisetFalse(ld);
// proses simpan
using (var trans = TransHelper.NewScope())
{
// update ke db
_laporanDinasDal.Update(ld);
// commit changes
trans.Complete();
}
return(ld);
}
private LaporanDinasModel R09_IsSignedDiketahuiDisetFalse(LaporanDinasModel ld)
{
//ld.IsSignedDiketahui = false;
return(ld);
}
private LaporanDinasModel R07_OtherTransportKMAkhirSet0(LaporanDinasModel ld)
{
ld.KMAkhir = 0;
return(ld);
}