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 void Delete(ISuratDinasKey key)
{
var sql = @"
DELETE
OFTA_SuratDinasApproval
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();
cmd.ExecuteNonQuery();
}
}
public void Void(ISuratDinasKey key)
{
var sd = _suratDinasDal.GetData(key);
// jika sudah dibuatkan laporan, tidak boleh update data surat dinas
//sd = R14_SuratDinasTerlaporTidakBolehUpdate(sd);
// proses simpan
using (var trans = TransHelper.NewScope())
{
// update ke db
_suratDinasDal.Delete(sd);
// commit changes
trans.Complete();
}
}
public SuratDinasModel GetData(ISuratDinasKey key)
{
// get header
var result = _suratDinasDal.GetData(key);
if (result is null)
{
return(null);
}
// get detil
var listApproval = _suratDinasApprovalDal.ListData(key);
result.ListApproval = listApproval;
return(result);
}
public LaporanDinasModel GetData(ISuratDinasKey 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
SuratDinasID = @SuratDinasID ";
using (var conn = new SqlConnection(ConnStringHelper.Get()))
using (var cmd = new SqlCommand(sql, conn))
{
cmd.AddParam("@LaporanDinasID", key.SuratDinasID, 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 IEnumerable <SuratDinasApprovalModel> ListData(ISuratDinasKey filter)
{
List <SuratDinasApprovalModel> result = null;
var sql = @"
SELECT
aa.SuratDinasID, aa.PegID, aa.ApprovalTypeID,
ISNULL(bb.ApprovalTypeName, '') ApprovalTypeName
FROM
OFTA_SuratDinasApproval aa
LEFT JOIN OFTA_ApprovalType bb ON aa.ApprovalTypeID = bb.ApprovalTypeID
WHERE
aa.SuratDinasID = @SuratDinasID ";
using (var conn = new SqlConnection(ConnStringHelper.Get()))
using (var cmd = new SqlCommand(sql, conn))
{
cmd.AddParam("@SuratDinasID", filter.SuratDinasID, SqlDbType.VarChar);
conn.Open();
using (var dr = cmd.ExecuteReader())
{
if (!dr.HasRows)
{
return(null);
}
result = new List <SuratDinasApprovalModel>();
while (dr.Read())
{
var item = new SuratDinasApprovalModel
{
SuratDinasID = dr["SuratDinasID"].ToString(),
PegID = dr["PegID"].ToString(),
ApprovalTypeID = dr["ApprovalTypeID"].ToString(),
ApprovalTypeName = dr["ApprovalTypeName"].ToString()
};
result.Add(item);
}
}
}
return(result);
}
