/// <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));
}
}