public List<PelayaranDetail> GetVessels()
{
List<PelayaranDetail> listVessel = null;
try
{
using (SqlConnection con = new SqlConnection(VisitaJayaPerkasa.Constant.VisitaJayaPerkasaApplication.connectionString))
{
Constant.VisitaJayaPerkasaApplication.anyConnection = false;
con.Open();
Constant.VisitaJayaPerkasaApplication.anyConnection = true;
using (SqlCommand command = new SqlCommand(
"SELECT DISTINCT pd.vessel_code, pd.vessel_name, pd.status_pinjaman, s.supplier_name, pd.pelayaran_detail_id FROM [Pelayaran_Detail] pd " +
"INNER JOIN [Pelayaran] p ON p.pelayaran_id = pd.pelayaran_id " +
"INNER JOIN [Supplier] s ON s.supplier_id = p.supplier_id " +
"WHERE (pd.deleted is null OR pd.deleted = '0') " +
"ORDER BY pd.vessel_name ASC"
, con))
{
SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
PelayaranDetail pelayaranDetail = new PelayaranDetail();
pelayaranDetail.PelayaranDetailID = Guid.Parse(reader.GetValue(4).ToString());
pelayaranDetail.VesselCode = reader.GetValue(0).ToString();
pelayaranDetail.VesselName = (reader.GetBoolean(2)) ? (reader.GetString(1) + " - " + reader.GetString(3) + " [loan]") : reader.GetString(1) + " - " + reader.GetString(3);
pelayaranDetail.VesselCodeAndPelayaranID = pelayaranDetail.PelayaranDetailID + pelayaranDetail.VesselCode;
if (listVessel == null)
listVessel = new List<PelayaranDetail>();
listVessel.Add(pelayaranDetail);
pelayaranDetail = null;
}
}
}
}
catch (Exception e)
{
Logging.Error("SqlPelayaranRepository.cs - GetVessels() " + e.Message);
}
return listVessel;
}
public List<PelayaranDetail> ListPelayaranDetail(Guid ID)
{
List<PelayaranDetail> listPelayaranDetail = null;
try
{
using (SqlConnection con = new SqlConnection(VisitaJayaPerkasa.Constant.VisitaJayaPerkasaApplication.connectionString))
{
Constant.VisitaJayaPerkasaApplication.anyConnection = false;
con.Open();
Constant.VisitaJayaPerkasaApplication.anyConnection = true;
using (SqlCommand command = new SqlCommand(
"SELECT * FROM [Pelayaran_Detail] WHERE pelayaran_id = '" + ID + "' AND (deleted is null OR deleted = '0')"
, con))
{
SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
PelayaranDetail pelayaranDetail = new PelayaranDetail();
pelayaranDetail.PelayaranDetailID = Utility.Utility.ConvertToUUID(reader.GetValue(0).ToString());
pelayaranDetail.ID = Utility.Utility.ConvertToUUID(reader.GetValue(1).ToString());
pelayaranDetail.VesselCode = (Utility.Utility.IsDBNull(reader.GetValue(2))) ? null : reader.GetString(2);
pelayaranDetail.VesselName = (Utility.Utility.IsDBNull(reader.GetValue(3))) ? null : reader.GetString(3);
pelayaranDetail.StatusPinjaman = Convert.ToInt32(reader.GetBoolean(4));
if (pelayaranDetail.StatusPinjaman == 1)
{
pelayaranDetail.NamaStatusPinjaman = "Pinjaman";
}
else
{
pelayaranDetail.NamaStatusPinjaman = "Milik Sendiri";
}
if (listPelayaranDetail == null)
listPelayaranDetail = new List<PelayaranDetail>();
listPelayaranDetail.Add(pelayaranDetail);
pelayaranDetail = null;
}
}
}
}
catch (Exception e)
{
Logging.Error("SqlPelayaranRepository.cs - ListPelayaranDetail() " + e.Message);
}
return listPelayaranDetail;
}
public List<PelayaranDetail> GetVessels(string destination, DateTime date)
{
List<PelayaranDetail> listVessel = null;
try
{
using (SqlConnection con = new SqlConnection(VisitaJayaPerkasa.Constant.VisitaJayaPerkasaApplication.connectionString))
{
Constant.VisitaJayaPerkasaApplication.anyConnection = false;
con.Open();
Constant.VisitaJayaPerkasaApplication.anyConnection = true;
using (SqlCommand command = new SqlCommand(
"SELECT DISTINCT pd.vessel_code, pd.vessel_name, pd.status_pinjaman, sup.supplier_name, pd.pelayaran_detail_id " +
"FROM [Pelayaran_Detail] pd " +
"INNER JOIN [PELAYARAN] p ON p.pelayaran_id = pd.pelayaran_id " +
"INNER JOIN [SCHEDULE] s ON s.pelayaran_detail_id = pd.pelayaran_detail_id " +
"INNER JOIN [SUPPLIER] sup ON p.supplier_id = sup.supplier_id " +
"WHERE (pd.deleted is null OR pd.deleted = '0') AND (sup.deleted is null OR sup.deleted = '0') " +
"AND cast(s.tgl_closing as date) >= cast('" + Utility.Utility.ConvertDateToString(date) + "' as date) " +
"AND (s.deleted is null OR s.deleted = '0') AND (p.deleted is null OR p.deleted = '0') " +
"AND s.ta is null " +
"AND s.tujuan = '" + Utility.Utility.ConvertToUUID(destination) + "' " +
"AND pd.vessel_code = s.vessel_code ", con))
{
SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
PelayaranDetail pelayaranDetail = new PelayaranDetail();
pelayaranDetail.PelayaranDetailID = Guid.Parse(reader.GetValue(4).ToString());
pelayaranDetail.VesselCode = reader.GetValue(0).ToString();
pelayaranDetail.VesselName = (reader.GetBoolean(2)) ? (reader.GetString(1) + " - " + reader.GetString(3) + " [loan]") : reader.GetString(1) + " - " + reader.GetString(3);
if (listVessel == null)
listVessel = new List<PelayaranDetail>();
listVessel.Add(pelayaranDetail);
pelayaranDetail = null;
}
}
}
}
catch (Exception e)
{
Logging.Error("SqlPelayaranRepository.cs - GetVessels() " + e.Message);
}
return listVessel;
}