/// <summary> /// QueryDLBuffer /// </summary> /// <param name="Parameter"></param> /// <returns>Datatable 轉 Json</returns> public static string GetHC_QueryDLBuffer(GetHCDLBuffer_Input Parameter) { string Time = Cache_Helper.CheckIsExistByCache("GetHC_DLBuffer", Parameter.BU, Parameter.DEPT_ID); if (!string.IsNullOrEmpty(Time) && DateTime.Compare(DateTime.Now, DateTime.Parse(Time)) < 0) //存在缓存则抓取缓存中的数据 { DataTable dt = Cache_Helper.GetAPICache("GetHC_DLBuffer", Parameter.BU, Parameter.DEPT_ID); DataRow dr = dt.Rows[0]; //string Update_Time = dr["Update_Time"].ToString();//缓存中的时间 string JsonResult = dr["Value3"].ToString(); return(JsonResult); } else { opc.Clear(); string Date = DateTime.Today.AddDays(-1).ToString("yyyy-MM-dd"); opc.Add(DataPara.CreateProcParameter("@P_BU", SqlDbType.VarChar, 10, ParameterDirection.Input, Parameter.BU)); opc.Add(DataPara.CreateProcParameter("@P_DATE", SqlDbType.VarChar, 10, ParameterDirection.Input, Date)); opc.Add(DataPara.CreateProcParameter("@P_DEPTID", SqlDbType.VarChar, 10, ParameterDirection.Input, Parameter.DEPT_ID)); DataTable dt = sdb.RunProc2("P_DailyReprot_QueryDLBuffer_API", opc); Cache_Helper.InsertCacheData("GetHC_DLBuffer", Parameter.BU, Parameter.DEPT_ID, JsonConvert.SerializeObject(dt), DateTime.Now.AddDays(1).ToString("yyyy-MM-dd") + " 16:00:00"); return(JsonConvert.SerializeObject(dt)); } }
/// <summary> /// 抓取每天/每月的用電量 累計的數據 /// </summary> /// <param name="Parameter"></param> /// <returns>Datatable 轉 Json</returns> public static string GetMeterKWH(SmartMeterKWH_Input Parameter) { string Time = Cache_Helper.CheckIsExistByCache("SmartMeter", Parameter.functiontype, Parameter.did); if (!string.IsNullOrEmpty(Time) && Cache_Helper.CalcTimeDifference(DateTime.Now, DateTime.Parse(Time)) < 60) //一小时更新数据 { DataTable dt = Cache_Helper.GetAPICache("SmartMeter", Parameter.functiontype, Parameter.did); DataRow dr = dt.Rows[0]; //string Update_Time = dr["Update_Time"].ToString();//缓存中的时间 string JsonResult = dr["Value3"].ToString(); return(JsonResult); } else { StringBuilder sb = new StringBuilder(); sb.Append(@" select T3.Line, T1.did, SUM(convert(int,T1.total)) as ActValue, Convert(int,T2.VALUE1) as TargetValue from dbo.KWH T1, TB_APPLICATION_PARAM T2, dbo.TB_Line_Param T3 where T1.type = 'R' and T2.PARAME_ITEM = 'upper' "); sb.Append(" AND T2.VALUE2 = @VALUE2 and T1.did = @did and T3.LineCode = @LineCode"); opc.Clear(); switch (Parameter.functiontype) { case "Day": sb.Append(" and DateDiff(dd,dt,getdate())=0 and T2.VALUE5 = 'Day'"); break; case "Month": sb.Append(" and DateDiff(MM,dt,getdate())=0 and T2.VALUE5 = 'Month'"); break; default: sb.Append(" T1.did = ''"); break; } sb.Append(" group by did, VALUE1, PARAME_NAME, Line"); switch (Parameter.did) { //OSD車間 case "190160": case "190161": case "190174": case "190175": opc.Add(DataPara.CreateDataParameter("@VALUE2", SqlDbType.NVarChar, "190160")); opc.Add(DataPara.CreateDataParameter("@did", SqlDbType.NVarChar, Parameter.did)); opc.Add(DataPara.CreateDataParameter("@LineCode", SqlDbType.NVarChar, "190160")); break; default: opc.Add(DataPara.CreateDataParameter("@VALUE2", SqlDbType.NVarChar, Parameter.did)); opc.Add(DataPara.CreateDataParameter("@did", SqlDbType.NVarChar, Parameter.did)); opc.Add(DataPara.CreateDataParameter("@LineCode", SqlDbType.NVarChar, Parameter.did)); break; } DataTable dt = sdb.GetDataTable(sb.ToString(), opc); Cache_Helper.InsertCacheData("SmartMeter", Parameter.functiontype, Parameter.did, JsonConvert.SerializeObject(dt), DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")); return(JsonConvert.SerializeObject(dt)); } }
/// <summary> /// GetNSB /// </summary> /// <param name="Parameter"></param> /// <returns>Datatable 轉 Json</returns> public static string GetNSB(GetNSB_Input Parameter) { string Time = Cache_Helper.CheckIsExistByCache("GetNSB", Parameter.WERKS, Parameter.PRODH); if (!string.IsNullOrEmpty(Time) && DateTime.Compare(DateTime.Now, DateTime.Parse(Time)) < 0) //存在缓存则抓取缓存中的数据 { DataTable dt = Cache_Helper.GetAPICache("GetNSB", Parameter.WERKS, Parameter.PRODH); DataRow dr = dt.Rows[0]; //string Update_Time = dr["Update_Time"].ToString();//缓存中的时间 string JsonResult = dr["Value3"].ToString(); return(JsonResult); } else { StringBuilder sb = new StringBuilder(); switch (Parameter.PRODH.ToUpper()) { case "ALL": sb.Append(@" SELECT T1.SPMON,'ALL' AS PRODH, Sum(convert(bigint,T1.NETWR)) as currentNSB, Convert(bigint,T2.LineCode) as targetNSB FROM [SAPTEST].[dbo].[ZTVDSS913] T1,ICM657.GreenPower.dbo.TB_Line_Param T2 where T1.SPMON = REPLACE(convert(varchar(7),GETDATE(),121),'-','') and T1.WERKS = @WERKS and T2.Line = 'NSB' AND T2.Value1 = 'ALL' group by T2.LineCode,T1.SPMON"); opc.Clear(); break; default: sb.Append(@"SELECT T1.SPMON, T1.PRODH,Sum(convert(bigint,T1.NETWR)) as currentNSB, Convert(bigint,T2.LineCode) as targetNSB FROM [SAPTEST].[dbo].[ZTVDSS913] T1,ICM657.GreenPower.dbo.TB_Line_Param T2 where T1.SPMON = REPLACE(convert(varchar(7),GETDATE(),121),'-','') and T1.WERKS = @WERKS and T2.Line = 'NSB' and T2.Value1 = @PRODH "); opc.Clear(); sb.Append(" and T1.PRODH = @PRODH"); sb.Append(" Group by T2.LineCode,T1.SPMON,T1.PRODH"); opc.Add(DataPara.CreateDataParameter("@PRODH", SqlDbType.NVarChar, Parameter.PRODH)); break; } opc.Add(DataPara.CreateDataParameter("@WERKS", SqlDbType.NVarChar, Parameter.WERKS)); DataTable dt = sdb.GetDataTable(sb.ToString(), opc); Cache_Helper.InsertCacheData("GetNSB", Parameter.WERKS, Parameter.PRODH, JsonConvert.SerializeObject(dt), DateTime.Now.AddDays(1).ToString("yyyy-MM-dd") + " 02:30:00"); return(JsonConvert.SerializeObject(dt)); } }
/// <summary> /// GetTECO /// </summary> /// <param name="Parameter"></param> /// <returns></returns> public static string GetTECO(TECO_Input Parameter) { string Time = Cache_Helper.CheckIsExistByCache("GetTECO", Parameter.PlantNo, Parameter.Line); if (!string.IsNullOrEmpty(Time) && DateTime.Compare(DateTime.Now, DateTime.Parse(Time)) < 0) //存在缓存则抓取缓存中的数据 { DataTable dt = Cache_Helper.GetAPICache("GetTECO", Parameter.PlantNo, Parameter.Line); DataRow dr = dt.Rows[0]; //string Update_Time = dr["Update_Time"].ToString();//缓存中的时间 string JsonResult = dr["Value3"].ToString(); return(JsonResult); } else { var FuncTeco = new DP_TECO(); DataTable dt = FuncTeco.ReturnTECOByCZOPS(Parameter.PlantNo, Parameter.Line); Cache_Helper.InsertCacheData("GetTECO", Parameter.PlantNo, Parameter.Line, JsonConvert.SerializeObject(dt), DateTime.Now.AddDays(1).ToString("yyyy-MM-dd") + " 07:00:00"); return(JsonConvert.SerializeObject(dt)); } }