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 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 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 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 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 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 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 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 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 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 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 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 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]);
}
}
public static DataTable GetDailyReports()
{
string sql = @"SELECT REPORT_DATE,LASTALLDAY_PLAN,LASTALLDAY_ACTUAL,LASTALLDAY_BARGE,LASTALLDAY_SHUTTLE,round(LASTALLDAY_COMPLETERATE,5)
,MONTHLY_PLAN,MONTHLY_TARGET,MONTHLY_ACTUAL,MONTHLY_BARGE,MONTHLY_SHUTTLE,round(MONTHLY_COMPLETERATE,5),round(MONTHLY_PLANCONTAINER,5)
,ANNUAL_PLAN,ANNUAL_ACTUAL,ANNUAL_BARGE,ANNUAL_SHUTTLE,round(ANNUAL_COMPLETERATE,5),round(ANNUAL_PLANCONTAINER,5)
FROM SSICT_DAILY_REPORT_VW ";
DataSet ds = OracleDataTool.ExecuteDataset(ConnectStringOracle.GetInternalTableConnection(), sql);
if (ds.Tables[0].Rows.Count == 0)
{
return(null);
}
else
{
return(ds.Tables[0]);
}
}
public static DataRow GetThreeShiftByID(string sID)
{
string sql = @"SELECT SHIFTDATE, SHIFT, SHIFTPLAN, SHIFTACTUAL, SHIFTCOMPLETERATE
FROM SSICT_APP_THREESHIFT_VW WHERE (ID = :tID)";
OracleParameter[] para = new OracleParameter[1];
para[0] = new OracleParameter("SID", sID);
DataSet ds = OracleDataTool.ExecuteDataset(ConnectStringOracle.GetInternalTableConnection(), sql, para);
if (ds.Tables[0].Rows.Count == 0)
{
return(null);
}
else
{
return(ds.Tables[0].Rows[0]);
}
}
public static DataTable GetUserResourceByUserName(string userName)
{
string sql = @"SELECT SUR_USERACCOUNT ,SUR_RESOURCECODE
FROM SYS_USER_RESOURCE Where SUR_USERACCOUNT = :userName";
OracleParameter[] para = new OracleParameter[1];
para[0] = new OracleParameter("userName", userName);
DataSet ds = OracleDataTool.ExecuteDataset(ConnectStringOracle.GetInternalTableConnection(), sql, para);
if (ds.Tables[0].Rows.Count == 0)
{
return(null);
}
else
{
return(ds.Tables[0]);
}
}
public static DataTable GetInternalUserByUserName(string userAccount)
{
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 Where SUR_USERACCOUNT = :userAccount";
OracleParameter[] para = new OracleParameter[1];
para[0] = new OracleParameter("userAccount", userAccount);
DataSet ds = OracleDataTool.ExecuteDataset(ConnectStringOracle.GetInternalTableConnection(), sql, para);
if (ds.Tables[0].Rows.Count == 0)
{
return(null);
}
else
{
return(ds.Tables[0]);
}
}
public static DataTable GetVesselBerthByDate(DateTime reportDate)
{
string sql = @"SELECT REPORT_DATE,VSL_CNNAME,VBT_PBTHDT,VBT_ABTHDT, VBT_STATUS, VOT_AWKSTTM, ISLATER
FROM SSICT_DAILYREPORT_BERTH_VW WHERE REPORT_DATE=to_date(:reportDate,'yyyy-mm-dd')";
OracleParameter[] para = new OracleParameter[1];
para[0] = new OracleParameter("reportDate", reportDate);
DataSet ds = OracleDataTool.ExecuteDataset(ConnectStringOracle.GetInternalTableConnection(), sql, para);
if (ds.Tables[0].Rows.Count == 0)
{
return(null);
}
else
{
return(ds.Tables[0]);
}
}
public static DataTable GetThreeShiftByDate(DateTime shiftDate)
{
string sql = @"SELECT SHIFTDATE, SHIFT, SHIFTPLAN, SHIFTACTUAL, SHIFTCOMPLETERATE
FROM SSICT_APP_THREESHIFT_VW WHERE SHIFTDATE=to_date(:shiftDate,'yyyy-mm-dd')";
OracleParameter[] para = new OracleParameter[1];
para[0] = new OracleParameter("shiftDate", shiftDate);
DataSet ds = OracleDataTool.ExecuteDataset(ConnectStringOracle.GetInternalTableConnection(), sql, para);
if (ds.Tables[0].Rows.Count == 0)
{
return(null);
}
else
{
return(ds.Tables[0]);
}
}
public static DataRow GetSendMessageByID(string sID)
{
string sql = @"SELECT ID, JobNo, SENDMESSAGE, SENDTIME, ERRORTIME, ISSEND, MECHANICALNO, FAULTSTATUS,BEGINTIME,FINISHTIME
FROM SSICT_SENDMSG_VW WHERE (ID = :tID)";
OracleParameter[] para = new OracleParameter[1];
para[0] = new OracleParameter("SID", sID);
DataSet ds = OracleDataTool.ExecuteDataset(ConnectStringOracle.GetInternalTableConnection(), sql, para);
if (ds.Tables[0].Rows.Count == 0)
{
return(null);
}
else
{
return(ds.Tables[0].Rows[0]);
}
}
public static DataTable GetTwinLiftByDate(DateTime reportDate)
{
string sql = @"SELECT SCD_ID ,VESSELNAME ,IEFG ,TOTALCNT ,CANSTGOPTCNT ,CANSTGOPTRATE ,STORAGECNT ,STORAGERATE ,OPERATECNT ,OPERATERATE ,EFFICIENCY ,REPORTDATE
FROM SSICT_DAILYREPORT_TWINLIFT WHERE REPORTDATE=to_date(:reportDate,'yyyy-mm-dd')";
OracleParameter[] para = new OracleParameter[1];
para[0] = new OracleParameter("reportDate", reportDate);
DataSet ds = OracleDataTool.ExecuteDataset(ConnectStringOracle.GetInternalTableConnection(), sql, para);
if (ds.Tables[0].Rows.Count == 0)
{
return(null);
}
else
{
return(ds.Tables[0]);
}
}
public static DataTable GetVesselEfficiencyByDate(DateTime reportDate)
{
string sql = @"SELECT REPORT_DATE, VSL_CNNAME,QCOPTM ,QCNETTM ,AVGEFF ,ABTHNO
FROM SSICT_APP_VESSEL_EFF
WHERE REPORT_DATE=to_date(:reportDate,'yyyy-mm-dd')";
OracleParameter[] para = new OracleParameter[1];
para[0] = new OracleParameter("reportDate", reportDate);
DataSet ds = OracleDataTool.ExecuteDataset(ConnectStringOracle.GetInternalTableConnection(), sql, para);
if (ds.Tables[0].Rows.Count == 0)
{
return(null);
}
else
{
return(ds.Tables[0]);
}
}
public static DataTable GetDailyReportByDate(DateTime reportDate)
{
string sql = @"SELECT REPORT_DATE,LASTALLDAY_PLAN,LASTALLDAY_ACTUAL,LASTALLDAY_BARGE,LASTALLDAY_SHUTTLE, round(LASTALLDAY_COMPLETERATE,5)
,MONTHLY_PLAN,MONTHLY_TARGET,MONTHLY_ACTUAL,MONTHLY_BARGE,MONTHLY_SHUTTLE,MONTHLY_COMPLETERATE,round(MONTHLY_PLANCONTAINER,5)
,ANNUAL_PLAN,ANNUAL_ACTUAL,ANNUAL_BARGE,ANNUAL_SHUTTLE, round(ANNUAL_COMPLETERATE,5),round(ANNUAL_PLANCONTAINER,5)
FROM SSICT_DAILY_REPORT_VW WHERE REPORT_DATE=to_date(:reportDate,'yyyy-mm-dd')";
OracleParameter[] para = new OracleParameter[1];
para[0] = new OracleParameter("reportDate", reportDate);
DataSet ds = OracleDataTool.ExecuteDataset(ConnectStringOracle.GetInternalTableConnection(), sql, para);
if (ds.Tables[0].Rows.Count == 0)
{
return(null);
}
else
{
return(ds.Tables[0]);
}
}