public static DataTable GetContainerDetailByNum(string ContainerNoOrbill)
{
string sql = @"SELECT ID, ContainerNo, CTNSize, CTNType, CTNHeight, CTNStat, SealNO, BLNO, CTNOwner, CTNNetWeight,
RCTemerature, DGType, DGUNNO, CTNIOType, PlanWorkTime, ArriveTime, DepartureTime, ArriveType, DepartureType,
TCTNEmpty ,RCTNEmpty, FirstVVessel, FirstVVoyage, SecondVVessel, SecondVVoyage, LoadingPort, DischargingPort,
DestinationPort, CustomsClearance, Stowagefg, CTNYardSlot, OverCTNType, OverWeight, OverHeight, FOL, BOL, LOW,
RoWidth, IsTurnCTN, IsDamaged ,ArriveISLate
FROM V_CONTAINER_DETAIL
WHERE (ContainerNo = :ContainerNoOrbill) ";
OracleParameter[] para = new OracleParameter[1];
para[0] = new OracleParameter("ContainerNoOrbill", ContainerNoOrbill);
DataSet ds = OracleDataTool.ExecuteDataset(ConnectStringOracle.GetViewConnection(), sql, para);
if (ds.Tables[0].Rows.Count == 0)
{
return(null);
}
else
{
return(ds.Tables[0]);
}
}
public static DataTable GetContainerPlans()
{
string sql = @"SELECT ID, VesselVoyage ,OPERATION ,PLANACCEPT ,PlanTime ,PlanAcceptedTime ,planno ,custom
FROM v_container_plan ORDER BY ID ";
DataSet ds = OracleDataTool.ExecuteDataset(ConnectStringOracle.GetViewConnection(), sql);
if (ds.Tables[0].Rows.Count == 0)
{
return(null);
}
else
{
return(ds.Tables[0]);
}
}
public static DataTable GetTrucks()
{
string sql = @"SELECT ID, Container_Truck_Num, Arrive_Yard_Time, Departure_Yard_Time, is_active, Remark ,Fcontainer ,Acontainer
FROM v_truck ";
DataSet ds = OracleDataTool.ExecuteDataset(ConnectStringOracle.GetViewConnection(), sql);
if (ds.Tables[0].Rows.Count == 0)
{
return(null);
}
else
{
return(ds.Tables[0]);
}
}
public static DataTable GetContainerPlanUsers()
{
string sql = @"SELECT usercd ,username ,userpasswd
FROM v_container_plan_user ";
DataSet ds = OracleDataTool.ExecuteDataset(ConnectStringOracle.GetViewConnection(), sql);
if (ds.Tables[0].Rows.Count == 0)
{
return(null);
}
else
{
return(ds.Tables[0]);
}
}
public static DataTable GetTVDangerPlans()
{
string sql = @"SELECT ID, PLANNO ,CUSTOM ,VESSELVOYAGE ,ARRIVE_PLAN_TIME ,DEPARTURE_PLAN_TIME ,TVDATE ,EXACTTVDATE
FROM V_TVDANGER_PLAN ORDER BY TVDATE Desc ";
DataSet ds = OracleDataTool.ExecuteDataset(ConnectStringOracle.GetViewConnection(), sql);
if (ds.Tables[0].Rows.Count == 0)
{
return(null);
}
else
{
return(ds.Tables[0]);
}
}
public static DataTable GetContainerEloads()
{
string sql = @"SELECT cntrID, VesselName, VoyageNumber, BillOfLadingNum, ContainerNo, ArrivalContainerTime, SendPackingListTime
FROM V_CONTAINER_ELOAD ";
DataSet ds = OracleDataTool.ExecuteDataset(ConnectStringOracle.GetViewConnection(), sql);
if (ds.Tables[0].Rows.Count == 0)
{
return(null);
}
else
{
return(ds.Tables[0]);
}
}
public static DataTable GetPortOfCalls()
{
string sql = @"SELECT ID, CN, EDI ,PortDisplayIndex
FROM v_port_of_call ORDER BY PortDisplayIndex DESC";
DataSet ds = OracleDataTool.ExecuteDataset(ConnectStringOracle.GetViewConnection(), sql);
if (ds.Tables[0].Rows.Count == 0)
{
return(null);
}
else
{
return(ds.Tables[0]);
}
}
public static DataTable GetTVDangerContainers()
{
string sql = @"SELECT CONTAINERNO, PLANNO
FROM v_Tvdanger_Containers ORDER BY CONTAINERNO DESC";
DataSet ds = OracleDataTool.ExecuteDataset(ConnectStringOracle.GetViewConnection(), sql);
if (ds.Tables[0].Rows.Count == 0)
{
return(null);
}
else
{
return(ds.Tables[0]);
}
}
public static DataTable GetVesselPlans()
{
string sql = @"SELECT id, VESSEL_NAME, VESSEL_ENGLISH_NAME, VESSEL_TYPE, VOYAGE_NUMBER, Import_Or_Export_Flag, ARRIVE_PLAN_TIME, ARRIVE_ACTUAL_TIME, DEPARTURE_PLAN_TIME,
DEPARTURE_ACTUAL_TIME, BERTH_PLAN, BERTH_ACTUAL, IS_CUSTOMS_CLOSING, STATUS, CSI, CONTAINER_BEGIN_TIME, Container_Deadline, Agency, Port_Of_Call_ID, Is_Active, Remark
FROM V_VESSEL_PLAN
ORDER BY Container_Begin_Time ASC ,VESSEL_NAME DESC ";
DataSet ds = OracleDataTool.ExecuteDataset(ConnectStringOracle.GetViewConnection(), sql);
if (ds.Tables[0].Rows.Count == 0)
{
return(null);
}
else
{
return(ds.Tables[0]);
}
}
public static DataTable GetContainerMains()
{
string sql = @"SELECT ID, ContainerNo, BillOfLadingNum , IEFG ,ContainerStatus ,VesselName, VoyageNumber, ArriveTime,
DepartureTime, ArriveType, DepartureType, ArrivalContainerTime, CustomsClearanceTime,
StowageTime, VesselTime, Arrivefg, Departurefg, CustomsClearance, Stowagefg, Vesselfg
FROM v_container_main ";
DataSet ds = OracleDataTool.ExecuteDataset(ConnectStringOracle.GetViewConnection(), sql);
if (ds.Tables[0].Rows.Count == 0)
{
return(null);
}
else
{
return(ds.Tables[0]);
}
}
public static DataTable GetTrucksByNum(string truckNo)
{
string sql = @"SELECT ID, Container_Truck_Num, Arrive_Yard_Time, Departure_Yard_Time, is_active, Remark ,Fcontainer ,Acontainer
FROM v_truck Where Container_Truck_Num = :truckNo";
OracleParameter[] para = new OracleParameter[1];
para[0] = new OracleParameter("truckNo", truckNo);
DataSet ds = OracleDataTool.ExecuteDataset(ConnectStringOracle.GetViewConnection(), sql, para);
if (ds.Tables[0].Rows.Count == 0)
{
return(null);
}
else
{
return(ds.Tables[0]);
}
}
public static DataTable GetContainerPlans(string custom)
{
string sql = @"SELECT ID, VesselVoyage ,OPERATION ,PLANACCEPT ,PlanTime ,PlanAcceptedTime ,planno ,custom
FROM v_container_plan
WHERE (custom = :custom) ";
OracleParameter[] para = new OracleParameter[1];
para[0] = new OracleParameter("custom", custom);
DataSet ds = OracleDataTool.ExecuteDataset(ConnectStringOracle.GetViewConnection(), sql, para);
if (ds.Tables[0].Rows.Count == 0)
{
return(null);
}
else
{
return(ds.Tables[0]);
}
}
public static DataTable GetTVDangerPlans(string custom)
{
string sql = @"SELECT ID, PLANNO ,CUSTOM ,VESSELVOYAGE ,ARRIVE_PLAN_TIME ,DEPARTURE_PLAN_TIME ,TVDATE ,EXACTTVDATE
FROM V_TVDANGER_PLAN
WHERE (CUSTOM = :custom) ORDER BY TVDATE Desc";
OracleParameter[] para = new OracleParameter[1];
para[0] = new OracleParameter("custom", custom);
DataSet ds = OracleDataTool.ExecuteDataset(ConnectStringOracle.GetViewConnection(), sql, para);
if (ds.Tables[0].Rows.Count == 0)
{
return(null);
}
else
{
return(ds.Tables[0]);
}
}
public static DataRow GetTVDangerPlanByID(string tID)
{
string sql = @"SELECT ID, PLANNO ,CUSTOM ,VESSELVOYAGE ,ARRIVE_PLAN_TIME ,DEPARTURE_PLAN_TIME ,TVDATE ,EXACTTVDATE
FROM V_TVDANGER_PLAN
WHERE (ID = :tID)";
OracleParameter[] para = new OracleParameter[1];
para[0] = new OracleParameter("tID", tID);
DataSet ds = OracleDataTool.ExecuteDataset(ConnectStringOracle.GetViewConnection(), sql, para);
if (ds.Tables[0].Rows.Count == 0)
{
return(null);
}
else
{
return(ds.Tables[0].Rows[0]);
}
}
public static DataTable GetTVDangerContainerByID(string planNo)
{
string sql = @"SELECT CONTAINERNO, PLANNO
FROM v_Tvdanger_Containers
WHERE (PLANNO = :planNo) order by CONTAINERNO DESC";
OracleParameter[] para = new OracleParameter[1];
para[0] = new OracleParameter("planNo", planNo);
DataSet ds = OracleDataTool.ExecuteDataset(ConnectStringOracle.GetViewConnection(), sql, para);
if (ds.Tables[0].Rows.Count == 0)
{
return(null);
}
else
{
return(ds.Tables[0]);
}
}
public static DataTable GetContainerEloadByContainerNo(string ContainerNo)
{
string sql = @"SELECT cntrID, VesselName, VoyageNumber, BillOfLadingNum, ContainerNo, ArrivalContainerTime, SendPackingListTime
FROM V_CONTAINER_ELOAD
WHERE (ContainerNo = :ContainerNo)
";
OracleParameter[] para = new OracleParameter[1];
para[0] = new OracleParameter("ContainerNo", ContainerNo);
DataSet ds = OracleDataTool.ExecuteDataset(ConnectStringOracle.GetViewConnection(), sql, para);
if (ds.Tables[0].Rows.Count == 0)
{
return(null);
}
else
{
return(ds.Tables[0]);
}
}
public static DataRow GetVesselPlanByID(string vpID)
{
string sql = @"SELECT id, VESSEL_NAME, VESSEL_ENGLISH_NAME, VESSEL_TYPE, VOYAGE_NUMBER, Import_Or_Export_Flag, ARRIVE_PLAN_TIME, ARRIVE_ACTUAL_TIME, DEPARTURE_PLAN_TIME,
DEPARTURE_ACTUAL_TIME, BERTH_PLAN, BERTH_ACTUAL, IS_CUSTOMS_CLOSING, STATUS, CSI, CONTAINER_BEGIN_TIME, Container_Deadline, Agency, Port_Of_Call_ID, Is_Active, Remark
FROM V_VESSEL_PLAN
WHERE (id = :vpID)";
OracleParameter[] para = new OracleParameter[1];
para[0] = new OracleParameter("vpID", vpID);
DataSet ds = OracleDataTool.ExecuteDataset(ConnectStringOracle.GetViewConnection(), sql, para);
if (ds.Tables[0].Rows.Count == 0)
{
return(null);
}
else
{
return(ds.Tables[0].Rows[0]);
}
}
public static DataTable GetContainerEloadByBillOfLadingNum(string BillOfLadingNum)
{
string sql = @"SELECT cntrID, VesselName, VoyageNumber, BillOfLadingNum, ContainerNo, ArrivalContainerTime, SendPackingListTime
FROM V_CONTAINER_ELOAD
WHERE cntrid in (select * from table(ssict_app_pak.STR2varlist(ssict_app_pak.Get_CntridByBillno(:BillOfLadingNum))))
";
OracleParameter[] para = new OracleParameter[1];
para[0] = new OracleParameter("BillOfLadingNum", BillOfLadingNum);
DataSet ds = OracleDataTool.ExecuteDataset(ConnectStringOracle.GetViewConnection(), sql, para);
if (ds.Tables[0].Rows.Count == 0)
{
return(null);
}
else
{
return(ds.Tables[0]);
}
}
public static DataTable GetPortOfCallByID(string pID)
{
string sql = @"SELECT ID, CN, EDI ,PortDisplayIndex
FROM v_port_of_call
WHERE (ID = :pID) order by PortDisplayIndex";
OracleParameter[] para = new OracleParameter[1];
para[0] = new OracleParameter("pID", pID);
DataSet ds = OracleDataTool.ExecuteDataset(ConnectStringOracle.GetViewConnection(), sql, para);
if (ds.Tables[0].Rows.Count == 0)
{
return(null);
}
else
{
return(ds.Tables[0]);
}
}
public static DataRow GetTruckByID(int tID)
{
string sql = @"SELECT ID, TruckNo, ArriveYardTime, DepartureYardTime, IsActive, Remark
FROM Truck
WHERE (ID = :tID)";
OracleParameter[] para = new OracleParameter[1];
para[0] = new OracleParameter("tID", tID);
DataSet ds = OracleDataTool.ExecuteDataset(ConnectStringOracle.GetViewConnection(), sql, para);
if (ds.Tables[0].Rows.Count == 0)
{
return(null);
}
else
{
return(ds.Tables[0].Rows[0]);
}
}
public static DataRow GetContainerPlanUserByID(string username, string userpasswd)
{
string sql = @"SELECT usercd ,username ,userpasswd
FROM v_container_plan_user
WHERE (username = :username) and (userpasswd=:userpasswd)";
OracleParameter[] para = new OracleParameter[2];
para[0] = new OracleParameter("username", username);
para[1] = new OracleParameter("userpasswd", userpasswd);
DataSet ds = OracleDataTool.ExecuteDataset(ConnectStringOracle.GetViewConnection(), sql, para);
if (ds.Tables[0].Rows.Count == 0)
{
return(null);
}
else
{
return(ds.Tables[0].Rows[0]);
}
}
public static DataRow GetContainerMainByID(string cID)
{
string sql = @"SELECT ID, ContainerNo, BillOfLadingNum , IEFG ,ContainerStatus ,VesselName, VoyageNumber, ArriveTime,
DepartureTime, ArriveType, DepartureType, ArrivalContainerTime, CustomsClearanceTime,
StowageTime, VesselTime, Arrivefg, Departurefg, CustomsClearance, Stowagefg, Vesselfg
FROM v_container_main
WHERE (ID = :cID)";
OracleParameter[] para = new OracleParameter[1];
para[0] = new OracleParameter("cID", cID);
DataSet ds = OracleDataTool.ExecuteDataset(ConnectStringOracle.GetViewConnection(), sql, para);
if (ds.Tables[0].Rows.Count == 0)
{
return(null);
}
else
{
return(ds.Tables[0].Rows[0]);
}
}
public static DataTable GetContainerMainByBillno(string Billno)
{
string sql = @"SELECT ID, ContainerNo, BillOfLadingNum , IEFG ,ContainerStatus ,VesselName, VoyageNumber, ArriveTime,
DepartureTime, ArriveType, DepartureType, ArrivalContainerTime, CustomsClearanceTime,
StowageTime, VesselTime, Arrivefg, Departurefg, CustomsClearance, Stowagefg, Vesselfg
FROM v_container_main
WHERE ID IN (SELECT * FROM TABLE(ssict_app_pak.STR2varlist(ssict_app_pak.Get_CntridByBillno(:Billno))) ) ";
OracleParameter[] para = new OracleParameter[1];
para[0] = new OracleParameter("Billno", Billno);
DataSet ds = OracleDataTool.ExecuteDataset(ConnectStringOracle.GetViewConnection(), sql, para);
if (ds.Tables[0].Rows.Count == 0)
{
return(null);
}
else
{
return(ds.Tables[0]);
}
}