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 void UpdateSendMessages(string sID)
{
string sql = @"DECLARE RS VARCHAR2(20);BEGIN RS:=SSICT_MSGFLAGUPDATE_F(:sID); END;";
OracleParameter[] para = new OracleParameter[1];
para[0] = new OracleParameter("sID", Int64.Parse(sID));
OracleDataTool.ExecuteNonQuery(ConnectStringOracle.GetInternalTableConnection(), sql, para);
}
public static void DeleteVisitMsg(string visitID)
{
string sql = @"DELETE FROM MOBILEAPP_VISIT WHERE VISITID=:visitID ";
DataSet ds = OracleDataTool.ExecuteDataset(ConnectStringOracle.GetTableConnection(), sql);
OracleParameter[] para = new OracleParameter[1];
para[0] = new OracleParameter("visitID", visitID);
OracleDataTool.ExecuteNonQuery(ConnectStringOracle.GetTableConnection(), sql, para);
}
public static void DeleteFavourite(string fID)
{
string sql = @"DELETE FROM MOBILEAPP_FAVOURITE WHERE ID=:fID ";
DataSet ds = OracleDataTool.ExecuteDataset(ConnectStringOracle.GetTableConnection(), sql);
OracleParameter[] para = new OracleParameter[1];
para[0] = new OracleParameter("fID", fID);
OracleDataTool.ExecuteNonQuery(ConnectStringOracle.GetTableConnection(), sql, para);
}
public static void DeleteLogEvent(string logID)
{
string sql = @"DELETE FROM MOBILEAPP_LOGEVENT WHERE LOGID=:logID ";
DataSet ds = OracleDataTool.ExecuteDataset(ConnectStringOracle.GetTableConnection(), sql);
OracleParameter[] para = new OracleParameter[1];
para[0] = new OracleParameter("logID", logID);
OracleDataTool.ExecuteNonQuery(ConnectStringOracle.GetTableConnection(), sql, para);
}
public static void UpdateLogEvent(string logID, string eventType, string message, string errorStackTrace, DateTime eventDate)
{
string sql = @"UPDATE MOBILEAPP_LOGEVENT SET EventType=:eventType, Message=:message, ErrorStackTrace=:errorStackTrace, EventDate=to_date(:eventDate,'yyyy-MM-DD hh24:mi:ss')
WHERE LOGID=:logID";
OracleParameter[] para = new OracleParameter[5];
para[0] = new OracleParameter("logID", logID);
para[1] = new OracleParameter("eventType", eventType);
para[2] = new OracleParameter("message", message);
para[3] = new OracleParameter("errorStackTrace", errorStackTrace);
para[4] = new OracleParameter("eventDate", eventDate.ToString());
OracleDataTool.ExecuteNonQuery(ConnectStringOracle.GetTableConnection(), sql, para);
}
public static void UpdateVisitMsg(string visitID, string ip, DateTime visitDate, string browser, string mobileUserAgent, string userName)
{
string sql = @"UPDATE MOBILEAPP_VISIT SET IP=:ip ,VISIT_DATE=to_date(:visitDate,'yyyy-MM-DD hh24:mi:ss') ,BROWSER=:browser, MOBILE_USER_AGENT=:mobileUserAgent, USERNAME=:userName
WHERE VISITID=:visitID";
OracleParameter[] para = new OracleParameter[6];
para[0] = new OracleParameter("visitID", visitID);
para[1] = new OracleParameter("ip", ip);
para[2] = new OracleParameter("visitDate", visitDate.ToString());
para[3] = new OracleParameter("browser", browser);
para[4] = new OracleParameter("mobileUserAgent", mobileUserAgent);
para[5] = new OracleParameter("userName", userName);
OracleDataTool.ExecuteNonQuery(ConnectStringOracle.GetTableConnection(), sql, para);
}
public static void InsertVisitMsg(string visitID, string ip, DateTime visitDate, string browser, string mobileUserAgent, string userName)
{
string sql = @"INSERT INTO MOBILEAPP_VISIT
( VISITID ,IP, VISIT_DATE, BROWSER ,MOBILE_USER_AGENT ,USERNAME)
VALUES ( SEQ_MOBILEAPP.Nextval, :ip ,to_date(:visitDate,'yyyy-MM-DD hh24:mi:ss') ,:browser ,:mobileUserAgent ,:userName)";
OracleParameter[] para = new OracleParameter[5];
para[0] = new OracleParameter("ip", ip);
para[1] = new OracleParameter("visitDate", visitDate.ToString());
para[2] = new OracleParameter("browser", browser);
para[3] = new OracleParameter("mobileUserAgent", mobileUserAgent);
para[4] = new OracleParameter("userName", userName);
OracleDataTool.ExecuteNonQuery(ConnectStringOracle.GetTableConnection(), sql, para);
}
public static void InsertLogEvent(string eventType, string message, string errorStackTrace, DateTime eventDate)
{
string sql = @"INSERT INTO MOBILEAPP_LOGEVENT
(LOGID, EventType, Message, ErrorStackTrace, EventDate )
VALUES ( SEQ_MOBILEAPP.Nextval, :eventType ,:message ,:errorStackTrace ,to_date(:eventDate,'yyyy-MM-DD hh24:mi:ss') )";
OracleParameter[] para = new OracleParameter[4];
para[0] = new OracleParameter("eventType", eventType);
para[1] = new OracleParameter("message", message);
para[2] = new OracleParameter("errorStackTrace", errorStackTrace);
para[3] = new OracleParameter("eventDate", eventDate.ToString());
OracleDataTool.ExecuteNonQuery(ConnectStringOracle.GetTableConnection(), sql, para);
}
public static DataTable GetLogEvents()
{
string sql = @"SELECT LOGID, EventType, Message, ErrorStackTrace, EventDate
FROM MOBILEAPP_LOGEVENT ";
DataSet ds = OracleDataTool.ExecuteDataset(ConnectStringOracle.GetTableConnection(), sql);
if (ds.Tables[0].Rows.Count == 0)
{
return(null);
}
else
{
return(ds.Tables[0]);
}
}
public static DataTable GetOperatePlans()
{
string sql = @"SELECT SHIFT_DATE ,SHIFT ,TEAMNAME ,OP_ROUTE ,QB ,HG ,FZ , OTHER ,SHIFT_NUM ,ATD_DIFF
FROM SSICT_APP_OPERATEPLAN";
DataSet ds = OracleDataTool.ExecuteDataset(ConnectStringOracle.GetInternalTableConnection(), sql);
if (ds.Tables[0].Rows.Count == 0)
{
return(null);
}
else
{
return(ds.Tables[0]);
}
}
public static DataTable GetNotices()
{
string sql = @"SELECT NoticeID, NoticeTitle, CreateTime, NoticeContent, IsActive, Remark
FROM MobileApp_Notice ORDER BY CreateTime DESC";
DataSet ds = OracleDataTool.ExecuteDataset(ConnectStringOracle.GetTableConnection(), sql);
if (ds.Tables[0].Rows.Count == 0)
{
return(null);
}
else
{
return(ds.Tables[0]);
}
}
public static DataTable GetVisitMsgs()
{
string sql = @"SELECT VISITID ,IP, VISIT_DATE, BROWSER ,MOBILE_USER_AGENT ,USERNAME
FROM MOBILEAPP_VISIT ";
DataSet ds = OracleDataTool.ExecuteDataset(ConnectStringOracle.GetTableConnection(), sql);
if (ds.Tables[0].Rows.Count == 0)
{
return(null);
}
else
{
return(ds.Tables[0]);
}
}
public static DataTable GetThreeShifts()
{
string sql = @"SELECT SHIFTDATE, SHIFT, SHIFTPLAN, SHIFTACTUAL, round(SHIFTCOMPLETERATE,5)
FROM SSICT_APP_THREESHIFT_VW";
DataSet ds = OracleDataTool.ExecuteDataset(ConnectStringOracle.GetInternalTableConnection(), sql);
if (ds.Tables[0].Rows.Count == 0)
{
return(null);
}
else
{
return(ds.Tables[0]);
}
}
public static DataTable GetTruckStatus()
{
string sql = @"SELECT TRUCKNO ,COMPLETETRUCKNUM ,AVEPERIOD ,CURRENTINSTRUCTION ,TOLOC1,TOLOC2,STATUS,STOPFG
FROM SSICT_APP_TRUCKSTATUS";
DataSet ds = OracleDataTool.ExecuteDataset(ConnectStringOracle.GetInternalTableConnection(), sql);
if (ds.Tables[0].Rows.Count == 0)
{
return null;
}
else
{
return ds.Tables[0];
}
}
public static DataTable GetTwinLifts()
{
string sql = @"SELECT SCD_ID ,VESSELNAME ,IEFG ,TOTALCNT ,CANSTGOPTCNT ,CANSTGOPTRATE ,STORAGECNT ,STORAGERATE ,OPERATECNT ,OPERATERATE, EFFICIENCY ,REPORTDATE
FROM SSICT_DAILYREPORT_TWINLIFT ";
DataSet ds = OracleDataTool.ExecuteDataset(ConnectStringOracle.GetInternalTableConnection(), 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 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 GetYardDensitys()
{
string sql = @"SELECT YD_ID ,YD_CNTR_STATUS ,YD_SAC_SUM ,YD_YARD_SLOT_SUM ,YD_YARD_SLOT_TOTAL ,round(YD_PCT,5) ,round(YD_DES,5)
FROM SSICT_YARD_DENSITY ";
DataSet ds = OracleDataTool.ExecuteDataset(ConnectStringOracle.GetInternalTableConnection(), 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 GetVesselDeparts()
{
string sql = @"SELECT REPORT_DATE ,VSL_CNNAME ,VBT_PDPTDT ,VBT_ADPTDT ,VBT_STATUS ,ISLATER ,VOT_AWKENTM ,WKLATER
FROM SSICT_DAILYREPORT_DEPART_VW";
DataSet ds = OracleDataTool.ExecuteDataset(ConnectStringOracle.GetInternalTableConnection(), 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 GetVesselBerths()
{
string sql = @"SELECT REPORT_DATE,VSL_CNNAME,VBT_PBTHDT,VBT_ABTHDT, VBT_STATUS, VOT_AWKSTTM, ISLATER
FROM SSICT_DAILYREPORT_BERTH_VW ";
DataSet ds = OracleDataTool.ExecuteDataset(ConnectStringOracle.GetInternalTableConnection(), sql);
if (ds.Tables[0].Rows.Count == 0)
{
return(null);
}
else
{
return(ds.Tables[0]);
}
}
public static DataTable GetOnBerths()
{
string sql = @"SELECT VESSELNAME,VESSELTYPE,VOCOPTM ,AMOUNTOFVESSEL ,TARGETVOCTM
FROM SSICT_APP_ONBERTH_EFF";
DataSet ds = OracleDataTool.ExecuteDataset(ConnectStringOracle.GetInternalTableConnection(), sql);
if (ds.Tables[0].Rows.Count == 0)
{
return(null);
}
else
{
return(ds.Tables[0]);
}
}
public static DataTable GetInternalUsers()
{
string sql = @"SELECT SUR_USERACCOUNT ,SUR_PASSWORD ,SUR_DISPLAYNAME ,SUR_DESCRIPTION ,SUR_CREATETIME , SUR_UPDATETIME ,SUR_GROUP ,SUR_STATUS ,SUR_ERRORCOUNT ,SUR_ISLOOKED
FROM SYS_USER";
DataSet ds = OracleDataTool.ExecuteDataset(ConnectStringOracle.GetInternalTableConnection(), sql);
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 GetVesselEfficiencys()
{
string sql = @"SELECT REPORT_DATE, VSL_CNNAME,QCOPTM ,QCNETTM ,AVGEFF ,ABTHNO
FROM SSICT_APP_VESSEL_EFF ";
DataSet ds = OracleDataTool.ExecuteDataset(ConnectStringOracle.GetInternalTableConnection(), sql);
if (ds.Tables[0].Rows.Count == 0)
{
return(null);
}
else
{
return(ds.Tables[0]);
}
}
public static DataTable GetFavourites()
{
string sql = @"SELECT ID, URL, USERNAME, CREATETIME, STATUS, UPDATETIME ,OBJECTID , OBJECTCONTENT ,OBJECTTYPE ,ISACTIVE ,REMARK
FROM MOBILEAPP_FAVOURITE ORDER BY STATUS DESC, UPDATETIME DESC, CREATETIME DESC";
DataSet ds = OracleDataTool.ExecuteDataset(ConnectStringOracle.GetTableConnection(), sql);
if (ds.Tables[0].Rows.Count == 0)
{
return(null);
}
else
{
return(ds.Tables[0]);
}
}
public static DataTable GetSendMessages()
{
string sql = @"SELECT ID, JobNo, SENDMESSAGE, SENDTIME, ERRORTIME, ISSEND, MECHANICALNO, FAULTSTATUS,BEGINTIME,FINISHTIME
FROM SSICT_SENDMSG_VW Order By ISSEND desc ,BEGINTIME desc";
DataSet ds = OracleDataTool.ExecuteDataset(ConnectStringOracle.GetInternalTableConnection(), sql);
if (ds.Tables[0].Rows.Count == 0)
{
return(null);
}
else
{
return(ds.Tables[0]);
}
}
public static DataTable GetFaultMessages()
{
string sql = @"SELECT MECHANICALNO ,SENDMESSAGE ,FaultStatus ,EngERRORTIME ,OptERRORTIME ,REPORTTIME ,RECEIVETIME ,
REPAIRTIME ,CONFIRMREPAIRTIME
FROM SSICT_FAULTLIST_VW ";
DataSet ds = OracleDataTool.ExecuteDataset(ConnectStringOracle.GetInternalTableConnection(), sql);
if (ds.Tables[0].Rows.Count == 0)
{
return(null);
}
else
{
return(ds.Tables[0]);
}
}