public IActionResult getListContainerDetail(ParamContainerDetail data) { MonContainerAvailableDetailDL dal = new MonContainerAvailableDetailDL(); MonContainerAvailableDetailModel hasil = new MonContainerAvailableDetailModel(); /** * 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"; } if (!string.IsNullOrEmpty(data.kd_regional) && data.kd_regional != "string") { IEnumerable <ContainerDetailData> result = dal.getContainerAvailableDetailData(data); hasil.message = "Success"; hasil.status = "S"; hasil.count = result.Cast <Object>().Count(); hasil.data = new PagedList <ContainerDetailData>(result.ToList(), Convert.ToInt32(data.page), Convert.ToInt32(data.limit)); } else { hasil.message = "Kode Regional Null !!!"; hasil.status = "E"; hasil.count = 0; } return(Ok(hasil)); }
public IEnumerable <ContainerDetailData> getContainerAvailableDetailData(ParamContainerDetail paramContainerDetail) { IEnumerable <ContainerDetailData> result = null; using (IDbConnection connection = Extension.GetConnection(1)) { try { string paramKodeRegional = ""; if (!string.IsNullOrEmpty(paramContainerDetail.kd_regional) && paramContainerDetail.kd_regional != "string") { paramKodeRegional = " AND T_STORAGE_CONTAINER_BOX_DETAIL.KD_REGIONAL ='" + paramContainerDetail.kd_regional + "'"; } string paramKodeCabang = ""; if (!string.IsNullOrEmpty(paramContainerDetail.kode_cabang) && paramContainerDetail.kode_cabang != "string") { paramKodeCabang = " AND T_STORAGE_CONTAINER_BOX_DETAIL.KD_CABANG ='" + paramContainerDetail.kode_cabang + "'"; } string paramKodeTerminal = ""; if (!string.IsNullOrEmpty(paramContainerDetail.kd_terminal) && paramContainerDetail.kd_terminal != "string") { paramKodeCabang = " AND T_STORAGE_CONTAINER_BOX_DETAIL.KD_TERMINAL ='" + paramContainerDetail.kd_terminal + "'"; } string paramArea = ""; if (!string.IsNullOrEmpty(paramContainerDetail.area) && paramContainerDetail.area != "string") { paramKodeCabang = " AND T_STORAGE_CONTAINER_BOX_DETAIL.AREA ='" + paramContainerDetail.area + "'"; } string paramSort = ""; if (!string.IsNullOrEmpty(paramContainerDetail.order_by_column) && paramContainerDetail.order_by_column != "string" && !string.IsNullOrEmpty(paramContainerDetail.order_by_sort) && paramContainerDetail.order_by_sort != "string") { paramSort = " ORDER BY T_STORAGE_CONTAINER_BOX_DETAIL." + paramContainerDetail.order_by_column + " " + paramContainerDetail.order_by_sort; } string paramSearch = ""; if (paramContainerDetail.is_searching == true && !string.IsNullOrEmpty(paramContainerDetail.search_key) && paramContainerDetail.search_key != "string") { paramSearch = " WHERE NAMA_PELANGGAN LIKE '" + paramContainerDetail.search_key + "%' OR VOYAGE_NO LIKE '" + paramContainerDetail.search_key + "%' OR VES_NAME LIKE '" + paramContainerDetail.search_key + "%' OR CONTAINER_NO LIKE '" + paramContainerDetail.search_key + "%'"; } string paramTransactDate = ""; if (!string.IsNullOrEmpty(paramContainerDetail.transact_date) && paramContainerDetail.transact_date != "string") { paramTransactDate = " AND TRANSACT_DATE IS NOT NULL AND TO_CHAR(TRANSACT_DATE, 'YYYY-MM-DD HH24:MI') = '" + paramContainerDetail.transact_date + "'"; } var sql = @"SELECT * FROM (" + "SELECT " + "T_STORAGE_CONTAINER_BOX_DETAIL.KD_CABANG, " + "T_STORAGE_CONTAINER_BOX_DETAIL.KD_TERMINAL, " + "T_STORAGE_CONTAINER_BOX_DETAIL.KD_REGIONAL, " + "T_STORAGE_CONTAINER_BOX_DETAIL.AREA, " + "T_STORAGE_CONTAINER_BOX_DETAIL.NAMA_PELANGGAN, " + "T_STORAGE_CONTAINER_BOX_DETAIL.VOYAGE_NO, " + "T_STORAGE_CONTAINER_BOX_DETAIL.CTR_SIZE, " + "T_STORAGE_CONTAINER_BOX_DETAIL.VES_NAME, " + "T_STORAGE_CONTAINER_BOX_DETAIL.TRANSACT_DATE, " + "T_STORAGE_CONTAINER_BOX_DETAIL.TGL_PENUMPUKAN_DISC, " + "COUNT(T_STORAGE_CONTAINER_BOX_DETAIL.VOYAGE_NO) JUMLAH_CONTAINER, " + "T_STORAGE_CONTAINER_BOX_DETAIL.CONTAINER_NO, " + "APP_REGIONAL.REGIONAL_NAMA NAMA_REGIONAL " + "FROM " + "T_STORAGE_CONTAINER_BOX_DETAIL JOIN APP_REGIONAL ON " + "T_STORAGE_CONTAINER_BOX_DETAIL.KD_REGIONAL=APP_REGIONAL.ID AND " + "APP_REGIONAL.PARENT_ID IS NULL AND " + "APP_REGIONAL.ID NOT IN (12300000,20300001)" + paramKodeRegional + paramKodeCabang + paramKodeTerminal + paramArea + " GROUP BY NAMA_PELANGGAN, AREA, VES_NAME, KD_CABANG, KD_TERMINAL, KD_REGIONAL, VOYAGE_NO, CTR_SIZE, TRANSACT_DATE, TGL_PENUMPUKAN_DISC, CONTAINER_NO, REGIONAL_NAMA" + ")" + paramSearch + paramTransactDate; result = connection.Query <ContainerDetailData>(sql, new { KD_REGIONAL = paramContainerDetail.kd_regional }); } catch (Exception) { result = null; } } return(result); }