public IActionResult getListPanduAvailable(ParamPandu data)
{
MonPanduAvailableDL dal = new MonPanduAvailableDL();
MonPanduAvailableModel hasil = new MonPanduAvailableModel();
/**
* This params is for pagination function
*/
if (!string.IsNullOrEmpty(data.limit) && data.limit != "string" && !string.IsNullOrEmpty(data.page) && data.page != "string")
{
data.page = data.page;
data.limit = data.limit;
}
else if (!string.IsNullOrEmpty(data.page) && data.page != "string" && string.IsNullOrEmpty(data.limit) && data.limit != "string")
{
data.page = data.page;
data.limit = "10";
}
else if (!string.IsNullOrEmpty(data.limit) && data.limit != "string" && string.IsNullOrEmpty(data.page) && data.page != "string")
{
data.page = "1";
data.limit = data.limit;
}
else
{
data.page = "1";
data.limit = "10";
}
IEnumerable <PanduAvailable> result = dal.getDataPanduAvailabe(data);
hasil.message = "Success";
hasil.status = "S";
hasil.count = result.Cast <Object>().Count();
hasil.data = new PagedList <PanduAvailable>(result.ToList(), Convert.ToInt32(data.page), Convert.ToInt32(data.limit));
return(Ok(hasil));
}
public IEnumerable <PanduAvailable> getDataPanduAvailabe(ParamPandu paramPandu)
{
IEnumerable <PanduAvailable> result = null;
using (IDbConnection connection = Extension.GetConnection(1))
{
try
{
string paramKodeRegional = "";
if (!string.IsNullOrEmpty(paramPandu.kd_regional) && paramPandu.kd_regional != "string")
{
paramKodeRegional = " WHERE KD_REGIONAL='" + paramPandu.kd_regional + "'";
}
string paramIdMasterArea = "";
if (!string.IsNullOrEmpty(paramPandu.id_master_area) && paramPandu.id_master_area != "string")
{
if (paramPandu.is_jamuang != "1" && paramPandu.id_master_area != "5" && paramPandu.id_master_area != "6" && paramPandu.id_master_area != "4")
{
paramIdMasterArea = " AND ID_MASTER_AREA='" + paramPandu.id_master_area + "'";
}
}
string paramNamaPandu = "";
if (!string.IsNullOrEmpty(paramPandu.nama_pandu) && paramPandu.nama_pandu != "string")
{
paramNamaPandu = " AND NAMA ='" + paramPandu.nama_pandu + "'";
}
string paramIsJamuang = "";
if (!string.IsNullOrEmpty(paramPandu.is_jamuang) && paramPandu.is_jamuang != "string")
{
if (paramPandu.id_master_area == "1")
{
paramIsJamuang = " AND IS_JAMUANG='1'";
}
else
{
paramIsJamuang = " AND IS_JAMUANG IN ('1', '0')";
}
}
string paramStatusPandu = "";
if (!string.IsNullOrEmpty(paramPandu.status_pandu) && paramPandu.status_pandu != "string")
{
if (!string.IsNullOrEmpty(paramPandu.kd_regional) && paramPandu.kd_regional != "string")
{
if (paramPandu.status_pandu == "AVAILABLE")
{
paramStatusPandu = " AND STATUS IN ('AVAILABLE', 'AVAILABLE_JAMUANG')";
}
else if (paramPandu.status_pandu == "HISTORY")
{
paramStatusPandu = " AND STATUS='HISTORY'";
}
else if (paramPandu.status_pandu == "WORK")
{
paramStatusPandu = " AND STATUS IN ('DEPARTURE', 'ARRIVAL', 'SHIFTING')";
}
else if (paramPandu.status_pandu == "RENCANA")
{
paramStatusPandu = " AND STATUS IN ('PERMOHONAN', 'PENETAPAN', 'SPK1')";
}
else
{
paramStatusPandu = " AND STATUS='" + paramPandu.status_pandu + "'";
}
}
else
{
if (paramPandu.status_pandu == "AVAILABLE")
{
paramStatusPandu = " WHERE STATUS IN ('AVAILABLE', 'AVAILABLE_JAMUANG')";
}
else if (paramPandu.status_pandu == "HISTORY")
{
paramStatusPandu = " WHERE STATUS='HISTORY'";
}
else if (paramPandu.status_pandu == "WORK")
{
paramStatusPandu = " WHERE STATUS IN ('DEPARTURE', 'ARRIVAL', 'SHIFTING')";
}
else if (paramPandu.status_pandu == "RENCANA")
{
paramStatusPandu = " WHERE STATUS IN ('PERMOHONAN', 'PENETAPAN', 'SPK1')";
}
else
{
paramStatusPandu = " WHERE STATUS='" + paramPandu.status_pandu + "'";
}
}
}
string paramOrderby = "";
if (!string.IsNullOrEmpty(paramPandu.order_by_column) && paramPandu.order_by_column != "string" && !string.IsNullOrEmpty(paramPandu.order_by_sort) && paramPandu.order_by_sort != "string")
{
if (paramPandu.order_by_column == "URUTAN")
{
paramOrderby = " ORDER BY TO_NUMBER(" + paramPandu.order_by_column + ") " + paramPandu.order_by_sort;
}
else
{
paramOrderby = " ORDER BY " + paramPandu.order_by_column + " " + paramPandu.order_by_sort;
}
}
string paramCreatedAt = "";
if (!string.IsNullOrEmpty(paramPandu.start_date) && paramPandu.start_date != "string")
{
paramCreatedAt = " AND TRUNC(TGL_WORK) BETWEEN TO_DATE('" + paramPandu.start_date + "', 'YYYY-MM-DD') AND TO_DATE('" + paramPandu.end_date + "', 'YYYY-MM-DD')";
}
string paramSearch = "";
if (!string.IsNullOrEmpty(paramPandu.search_key) && paramPandu.search_key != "string" && paramPandu.is_search == true)
{
if (!string.IsNullOrEmpty(paramPandu.kd_regional) && paramPandu.kd_regional != "string")
{
paramSearch = " WHERE NAMA LIKE '%" + paramPandu.search_key + "%' OR NAMA_KAPAL LIKE '%" + paramPandu.search_key + "%' OR NO_PPK1='" + paramPandu.search_key + "'";
}
else
{
var one_month_before = DateTime.Now.AddMonths(-1).ToString("yyyy-MM-dd HH:mm:ss");
paramSearch = " WHERE NAMA LIKE '%" + paramPandu.search_key + "' OR NAMA_KAPAL LIKE '%" + paramPandu.search_key + "%' OR NO_PPK1='" + paramPandu.search_key.ToString() + "' AND STATUS NOT IN ('AVAILABLE', 'AVAILABLE_JAMUANG') AND (TGL_OFF >= '" + one_month_before + "' OR TGL_OFF IS NULL)";
}
}
string paramAsalTujuan = "";
if (!string.IsNullOrEmpty(paramPandu.id_master_area) && paramPandu.id_master_area != "string")
{
if (paramPandu.status_pandu == "RENCANA")
{
if (paramPandu.id_master_area == "6")
{
paramAsalTujuan = " AND (FROM_MDMG_NAMA IN('LAMONG CURAH KERING', 'LAMONG NUSANTARA', 'LAMONG OCEAN GOING') OR TO_MDMG_NAMA IN('LAMONG CURAH KERING', 'LAMONG NUSANTARA', 'LAMONG OCEAN GOING'))";
}
if (paramPandu.id_master_area == "5")
{
paramAsalTujuan = " AND (FROM_MDMG_NAMA IN('TPS/ICT OCEAN GOING', 'ICT NUSANTARA') OR TO_MDMG_NAMA IN('TPS/ICT OCEAN GOING', 'ICT NUSANTARA'))";
}
}
}
string paramKawasan = "";
if (!string.IsNullOrEmpty(paramPandu.id_master_area) && paramPandu.id_master_area != "string")
{
if (paramPandu.id_master_area == "4")
{
if (paramPandu.status_pandu != "RENCANA")
{
paramKawasan = " AND KAWASAN='GRESIK'";
}
else
{
paramKawasan = " AND KAWASAN='" + paramPandu.kawasan + "'";
}
}
if (paramPandu.id_master_area == "2")
{
if (paramPandu.status_pandu != "RENCANA")
{
paramKawasan = " AND KAWASAN='SURABAYA'";
}
else
{
paramKawasan = " AND KAWASAN='" + paramPandu.kawasan + "'";
}
}
if (paramPandu.id_master_area == "5")
{
if (paramPandu.status_pandu != "RENCANA")
{
paramKawasan = " AND KAWASAN='TPS'";
}
}
if (paramPandu.id_master_area == "6")
{
if (paramPandu.status_pandu != "RENCANA")
{
paramKawasan = " AND KAWASAN='TTL'";
}
}
}
string paramTglWork = "";
if (!string.IsNullOrEmpty(paramPandu.tgl_work) && paramPandu.tgl_work != "string")
{
paramTglWork = " AND TO_CHAR(TGL_WORK, 'YYYY/MM/DD HH24:MI')='" + paramPandu.tgl_work + "'";
}
string paramTglOff = "";
if (!string.IsNullOrEmpty(paramPandu.tgl_off) && paramPandu.tgl_off != "string")
{
paramTglWork = " AND TO_CHAR(TO_DATE(SUBSTR(TGL_OFF,1,16), 'YYYY-MM-DD HH:MI'), 'YYYY-MM-DD HH:MI')='" + paramPandu.tgl_off + "'";
}
string sql = @"SELECT * FROM (" +
"SELECT * FROM VW_MAGIC_PILOT_INFORMATION " + paramKodeRegional + paramIdMasterArea + paramNamaPandu + paramStatusPandu + paramIsJamuang + paramKawasan + paramAsalTujuan + paramCreatedAt + paramTglWork + paramTglOff + paramOrderby +
")" + paramSearch;
result = connection.Query <PanduAvailable>(sql, new
{
KD_REGIONAL = paramPandu.kd_regional
});
}
catch (Exception)
{
result = null;
}
}
return(result);
}
