public SuratDinasModel GetData(ISuratDinasKey key)
{
SuratDinasModel result = null;
var sql = @"
SELECT
aa.SuratDinasID, aa.TglJamCreate,
aa.PegID, aa.NoSurat, aa.NoKontrak,
aa.TglMulai, aa.TglSelesai, aa.Keperluan,
aa.TransportID, aa.KMAwal,
aa.RSID, aa.JenisBiayaID, aa.KasBon,
ISNULL(bb.PegName, '') PegName,
ISNULL(cc.TransportName, '') TransportName,
ISNULL(dd.RSName, '') RSName,
ISNULL(ee.JenisBiayaName, '') JenisBiayaName
FROM
OFTA_SuratDinas aa
LEFT JOIN OFTA_Peg bb ON aa.PegID = bb.PegID
LEFT JOIN OFTA_Transport cc oN aa.TransportID = cc.TransportID
LEFT JOIN OFTA_RS dd ON aa.RSID = dd.RSID
LEFT JOIN OFTA_JenisBiaya ee ON aa.JenisBiayaID = ee.JenisBiayaID
WHERE
SuratDinasID = @SuratDinasID ";
using (var conn = new SqlConnection(ConnStringHelper.Get()))
using (var cmd = new SqlCommand(sql, conn))
{
cmd.AddParam("@SuratDinasID", key.SuratDinasID, SqlDbType.VarChar);
conn.Open();
using (var dr = cmd.ExecuteReader())
{
if (!dr.HasRows)
{
return(null);
}
dr.Read();
result = new SuratDinasModel
{
SuratDinasID = dr["SuratDinasID"].ToString(),
TglJamCreate = Convert.ToDateTime(dr["TglJamCreate"]),
PegID = dr["PegID"].ToString(),
PegName = dr["PegName"].ToString(),
NoSurat = dr["NoSurat"].ToString(),
NoKontrak = dr["NoKontrak"].ToString(),
TglMulai = Convert.ToDateTime(dr["TglMulai"]),
TglSelesai = Convert.ToDateTime(dr["TglSelesai"]),
Keperluan = dr["Keperluan"].ToString(),
TransportID = dr["TransportID"].ToString(),
TransportName = dr["TransportName"].ToString(),
KMAwal = Convert.ToInt64(dr["KMAwal"]),
RSID = dr["RSID"].ToString(),
RSName = dr["RSName"].ToString(),
JenisBiayaID = dr["JenisBiayaID"].ToString(),
JenisBiayaName = dr["JenisBiayaName"].ToString(),
KasBon = Convert.ToDecimal(dr["KasBon"])
};
}
}
return(result);
}
public SuratDinasModel Propose(SuratDinasAddDto suratDinas)
{
// CHECK ARGUMENT
suratDinas.Empty().Throw("Data Surat Dinas kosong");
// CONVERT DTO >> MODEL
var sd = new SuratDinasModel
{
TglJamCreate = DateTime.Now,
PegID = suratDinas.PegID,
NoSurat = suratDinas.NoSurat,
NoKontrak = suratDinas.NoKontrak,
TglMulai = suratDinas.TglMulai,
TglSelesai = suratDinas.TglSelesai,
Keperluan = suratDinas.Keperluan,
TransportID = suratDinas.TransportID,
KMAwal = suratDinas.KMAwal,
RSID = suratDinas.RSID,
JenisBiayaID = suratDinas.JenisBiayaID,
KasBon = suratDinas.KasBon,
ListApproval =
from c in suratDinas.ListApproval
select new SuratDinasApprovalModel
{
PegID = c.PegID,
ApprovalTypeID = c.ApprovalTypeID,
}
};
// VALIDATE MODEL
Validate(sd);
// UPDATE REPO
using (var trans = TransHelper.NewScope())
{
// generate id
sd.SuratDinasID = _paramNoBL.GenNewID("LG", ParamNoLengthEnum.Code_13);
foreach (var item in sd.ListApproval)
{
item.SuratDinasID = sd.SuratDinasID;
}
// insert ke db
_suratDinasDal.Insert(sd);
_suratDinasApprovalDal.Delete(sd);
foreach (var item in sd.ListApproval)
{
_suratDinasApprovalDal.Insert(item);
}
// commit changes
trans.Complete();
}
return(sd);
}
public IHttpActionResult Update(SuratDinasModel surat)
{
try
{
var result = _suratDinasBL.Revise(surat);
return(Ok(result));
}
catch (ArgumentException ex)
{
return(BadRequest(ex.Message));
}
}
public IHttpActionResult GetData(string suratDinasID)
{
var surat = new SuratDinasModel(suratDinasID);
try
{
var result = _suratDinasBL.GetData(surat);
return(Ok(result));
}
catch (ArgumentException ex)
{
throw new ArgumentException(ex.Message);
}
}
public IHttpActionResult Delete(string suratDinasID)
{
var surat = new SuratDinasModel(suratDinasID);
try
{
_suratDinasBL.Void(surat);
return(Ok());
}
catch (ArgumentException ex)
{
throw new ArgumentException(ex.Message);
}
}
public void Update(SuratDinasModel entity)
{
var sql = @"
UPDATE
OFTA_SuratDinas
SET
TglJamCreate = @TglJamCreate,
PegID = @PegID,
NoSurat = @NoSurat,
NoKontrak = @NoKontrak,
TglMulai = @TglMulai,
TglSelesai = @TglSelesai,
Keperluan = @Keperluan,
TransportID = @TransportID,
KMAwal = @KMAwal,
RSID = @RSID,
JenisBiayaID = @JenisBiayaID,
KasBon = @KasBon
WHERE
SuratDinasID = @SuratDinasID ";
using (var conn = new SqlConnection(ConnStringHelper.Get()))
using (var cmd = new SqlCommand(sql, conn))
{
cmd.AddParam("@SuratDinasID", entity.SuratDinasID, SqlDbType.VarChar);
cmd.AddParam("@TglJamCreate", entity.TglJamCreate, SqlDbType.DateTime);
cmd.AddParam("@PegID", entity.PegID, SqlDbType.VarChar);
cmd.AddParam("@NoSurat", entity.NoSurat, SqlDbType.VarChar);
cmd.AddParam("@NoKontrak", entity.NoKontrak, SqlDbType.VarChar);
cmd.AddParam("@TglMulai", entity.TglMulai, SqlDbType.DateTime);
cmd.AddParam("@TglSelesai", entity.TglSelesai, SqlDbType.DateTime);
cmd.AddParam("@Keperluan", entity.Keperluan, SqlDbType.VarChar);
cmd.AddParam("@TransportID", entity.TransportID, SqlDbType.VarChar);
cmd.AddParam("@KMAwal", entity.KMAwal, SqlDbType.BigInt);
cmd.AddParam("@RSID", entity.RSID, SqlDbType.VarChar);
cmd.AddParam("@JenisBiayaID", entity.JenisBiayaID, SqlDbType.VarChar);
cmd.AddParam("@KasBon", entity.KasBon, SqlDbType.Decimal);
conn.Open();
cmd.ExecuteNonQuery();
}
}
public SuratDinasModel Revise(SuratDinasModel sd)
{
// jika sudah dibuatkan laporan, tidak boleh update data surat dinas
//sd = R14_SuratDinasTerlaporTidakBolehUpdate(sd);
Validate(sd);
// proses simpan
using (var trans = TransHelper.NewScope())
{
// update ke db
_suratDinasDal.Update(sd);
// commit changes
trans.Complete();
}
return(sd);
}
public void Insert(SuratDinasModel entity)
{
var sql = @"
INSERT INTO
OFTA_SuratDinas (
SuratDinasID, TglJamCreate,
PegID, NoSurat, NoKontrak,
TglMulai, TglSelesai, Keperluan,
TransportID, KMAwal,
RSID, JenisBiayaID, KasBon)
VALUES (
@SuratDinasID, @TglJamCreate,
@PegID, @NoSurat, @NoKontrak,
@TglMulai, @TglSelesai, @Keperluan,
@TransportID, @KMAwal,
@RSID, @JenisBiayaID, @KasBon) ";
using (var conn = new SqlConnection(ConnStringHelper.Get()))
using (var cmd = new SqlCommand(sql, conn))
{
cmd.AddParam("@SuratDinasID", entity.SuratDinasID, SqlDbType.VarChar);
cmd.AddParam("@TglJamCreate", entity.TglJamCreate, SqlDbType.DateTime);
cmd.AddParam("@PegID", entity.PegID, SqlDbType.VarChar);
cmd.AddParam("@NoSurat", entity.NoSurat, SqlDbType.VarChar);
cmd.AddParam("@NoKontrak", entity.NoKontrak, SqlDbType.VarChar);
cmd.AddParam("@TglMulai", entity.TglMulai, SqlDbType.DateTime);
cmd.AddParam("@TglSelesai", entity.TglSelesai, SqlDbType.DateTime);
cmd.AddParam("@Keperluan", entity.Keperluan, SqlDbType.VarChar);
cmd.AddParam("@TransportID", entity.TransportID, SqlDbType.VarChar);
cmd.AddParam("@KMAwal", entity.KMAwal, SqlDbType.BigInt);
cmd.AddParam("@RSID", entity.RSID, SqlDbType.VarChar);
cmd.AddParam("@JenisBiayaID", entity.JenisBiayaID, SqlDbType.VarChar);
cmd.AddParam("@KasBon", entity.KasBon, SqlDbType.Decimal);
conn.Open();
cmd.ExecuteNonQuery();
}
}
private SuratDinasModel Validate(SuratDinasModel sd)
{
// mandatory check
sd.JenisBiayaID.Empty().Throw("JENIS BIAYA kosong");
sd.PegID.Empty().Throw("PEGAWAI kosong");
sd.RSID.Empty().Throw("RS TUJUAN kosong");
sd.TransportID.Empty().Throw("TRANSPORT kosong");
sd.Keperluan.Empty().Throw("KEPERLUAN kosong");
// pegawai
var peg = _pegDal.GetData(sd);
peg.Empty().Throw("PEGAWAI invalid");
sd.PegName = peg.PegName;
// transport
var transport = _transportDal.GetData(sd);
transport.Empty().Throw("TRANSPORT invalid");
sd.TransportName = transport.TransportName;
// jenis biaya
var jb = _jenisBiayaDal.GetData(sd);
jb.Empty().Throw("JENIS BIAYA invalid");
sd.JenisBiayaName = jb.JenisBiayaName;
// rumah sakit
var rs = _rsDal.GetData(sd);
rs.Empty().Throw("RS TUJUAN invalid");
sd.RSName = rs.RSName;
// urutan tgl mulai - selesai
sd.TglSelesai.LessThan(sd.TglMulai).Throw("Tgl Mulasi / Selesai invalid");
// km awal => terisi jika mobil kantor, kosong jika sebaliknya
if (sd.TransportID == TRANSPORT_ID_OPERASIONAL)
{
sd.KMAwal.LessOrEqual(0).Throw("KM Awal kosong");
}
if (sd.TransportID != TRANSPORT_ID_OPERASIONAL)
{
sd.KMAwal = 0;
}
// nilai kas bon => terisi jika kasbon, kosongkan jika bukan kasbon
if (sd.JenisBiayaID == JENISBIAYA_ID_KASBON)
{
sd.KasBon.LessOrEqual(0).Throw("Nilai KasBon kosong");
}
if (sd.JenisBiayaID != JENISBIAYA_ID_KASBON)
{
sd.KasBon = 0;
}
// approval
sd.ListApproval.Empty().Throw("List Approval empty");
var listApproval = new List <SuratDinasApprovalModel>();
foreach (var item in sd.ListApproval)
{
peg = _pegDal.GetData(item);
peg.Empty().Throw("PegID Approval invalid");
item.PegName = peg.PegName;
var aprvType = _approvalTypeDal.GetData(item);
aprvType.Empty().Throw("Approval Type invalid");
item.ApprovalTypeName = aprvType.ApprovalTypeName;
var newItem = new SuratDinasApprovalModel
{
PegID = item.PegID,
PegName = peg.PegName,
ApprovalTypeID = item.ApprovalTypeID,
ApprovalTypeName = item.ApprovalTypeName,
};
listApproval.Add(newItem);
}
sd.ListApproval = listApproval;
return(sd);
}
public IEnumerable <SuratDinasModel> ListData(DateTime tgl1, DateTime tgl2)
{
List <SuratDinasModel> result = null;
var sql = @"
SELECT
aa.SuratDinasID, aa.TglJamCreate,
aa.PegID, aa.NoSurat, aa.NoKontrak,
aa.TglMulai, aa.TglSelesai, aa.Keperluan,
aa.TransportID, aa.KMAwal,
aa.RSID, aa.JenisBiayaID, aa.KasBon,
ISNULL(bb.PegName, '') PegName,
ISNULL(cc.TransportName, '') TransportName,
ISNULL(dd.RSName, '') RSName,
ISNULL(ee.JenisBiayaName, '') JenisBiayaName
FROM
OFTA_SuratDinas aa
LEFT JOIN OFTA_Peg bb ON aa.PegID = bb.PegID
LEFT JOIN OFTA_Transport cc oN aa.TransportID = cc.TransportID
LEFT JOIN OFTA_RS dd ON aa.RSID = dd.RSID
LEFT JOIN OFTA_JenisBiaya ee ON aa.JenisBiayaID = ee.JenisBiayaID
WHERE
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 <SuratDinasModel>();
while (dr.Read())
{
var item = new SuratDinasModel
{
SuratDinasID = dr["SuratDinasID"].ToString(),
TglJamCreate = Convert.ToDateTime(dr["TglJamCreate"]),
PegID = dr["PegID"].ToString(),
PegName = dr["PegName"].ToString(),
NoSurat = dr["NoSurat"].ToString(),
NoKontrak = dr["NoKontrak"].ToString(),
TglMulai = Convert.ToDateTime(dr["TglMulai"]),
TglSelesai = Convert.ToDateTime(dr["TglSelesai"]),
Keperluan = dr["Keperluan"].ToString(),
TransportID = dr["TransportID"].ToString(),
TransportName = dr["TransportName"].ToString(),
KMAwal = Convert.ToInt64(dr["KMAwal"]),
RSID = dr["RSID"].ToString(),
RSName = dr["RSName"].ToString(),
JenisBiayaID = dr["JenisBiayaID"].ToString(),
JenisBiayaName = dr["JenisBiayaName"].ToString(),
KasBon = Convert.ToDecimal(dr["KasBon"])
};
result.Add(item);
}
}
}
return(result);
}
