/// <summary>
/// 得到查询结果
/// </summary>
/// <param name="condition">条件字符串</param>
/// <param name="ecweb"></param>
/// <param name="srvtime"></param>
/// <param name="plantid"></param>
/// <param name="unitid"></param>
/// <returns></returns>
public static DataTable GetSearchList(string ecweb, string srvtime, string plantid = "", string unitid = "")
{
StringBuilder sb = new StringBuilder();
sb.Append(@"WITH KPIRealValueECT AS(
SELECT a.ECID, c.UnitID, c.UnitName, c.WorkID, a.ECName, d.EngunitName, b.ECOptimal, ECValue,
a.ECScore, ECQulity, ECTime, ECShift, ECOptExp,ECIndex,ECNote
FROM KPI_ECSSSnapshot a LEFT JOIN KPI_ECTag b on a.ECID = b.ECID
RIGHT JOIN KPI_Unit c on b.UnitID = c.UnitID
LEFT JOIN KPI_Engunit d on b.EngunitID = d.EngunitID
WHERE b.ECIsValid =1 AND b.ECIsDisplay=1 " );
if (!String.IsNullOrEmpty(plantid))
{
sb.Append("AND c.PlantID ='" + plantid + "' ");
}
if (!String.IsNullOrEmpty(unitid))
{
sb.Append(" AND c.UnitID ='" + unitid + "' ");
}
if (!String.IsNullOrEmpty(ecweb))
{
sb.Append(" AND b.ECWeb='" + ecweb + "' ");
}
sb.Append(")");
List <KPI_UnitEntity> unitList = KPI_UnitDal.GetAllEntity();
string workid = KPI_UnitDal.GetWorkIDByCode(unitList[0].UnitCode);
string ShiftName = "";
string PeriodName = "";
string StartTime = "";
string EndTime = "";
string strTime = KPI_SystemDal.GetKPISrvTime();
bool bGood = KPI_WorkDal.GetShiftAndPeriod(workid, strTime, ref ShiftName, ref PeriodName, ref StartTime, ref EndTime);
DateTime date = DateTime.Parse(StartTime);
date = new DateTime(date.Year, date.Month, 1);
string strMonthStart = date.ToString("yyyy-MM-dd 01:00:00");
date = date.AddMonths(1);
string strMonthEnd = date.ToString("yyyy-MM-dd 01:00:00");
sb.Append(" SELECT A.*,B.ECScoreDay,C.ECScoreMonth FROM KPIRealValueECT A ");
sb.AppendFormat(" LEFT JOIN (SELECT SUM(Score) ECScoreDay, ECID FROM KPI_ECHourData " +
"WHERE Shift='{0}' AND CheckDate BETWEEN '{1}' AND '{2}' " +
"GROUP BY ECID) B ON A.ECID=B.ECID", ShiftName, StartTime, EndTime);
sb.AppendFormat(" LEFT JOIN (SELECT SUM(Score) ECScoreMonth, ECID FROM KPI_ECHourData " +
"WHERE Shift='{0}' AND CheckDate BETWEEN '{1}' AND '{2}' " +
"GROUP BY ECID) C ON A.ECID=C.ECID", ShiftName, strMonthStart, strMonthEnd);
sb.Append(" ORDER BY ECIndex,UNitName");
String sqlText = sb.ToString();
DataTable dt = DBAccess.GetRelation().ExecuteDataset(sqlText).Tables[0];
return(dt);
}
/// <summary>
///
/// </summary>
/// <param name="UnitID"></param>
/// <returns></returns>
public static DataTable GetSearchListS(string UnitID, string StartTime, string EndTime)
{
DataTable dt = null;
string WorkID = KPI_UnitDal.GetWorkIDByID(UnitID);
int nBaseShifts = KPI_WorkDal.GetBaseShifts(WorkID);
string[] AllShifts = KPI_WorkDal.GetShift(WorkID);
if (nBaseShifts != AllShifts.Length)
{
return(dt);
}
string condition = "";
for (int i = 0; i < nBaseShifts; i++)
{
string ShiftID = AllShifts[i];
string ShiftName = KPI_ShiftDal.GetShiftName(ShiftID);
condition += "cast(avg(case TagShift when '" + ShiftID + "' then TagValue else null end) as numeric(18,4)) as '" + ShiftName + "', ";
}
condition = condition.Remove(condition.LastIndexOf(','));
string where = " where TagStartTime>='" + StartTime + "' and TagStartTime<'" + EndTime + "'";
string sql = @"select TagDesc As '运行参数', {0}
from Race_Archive
a left outer join Race_Tag b on a.TagID=b.TagID
{1}
group by TagDesc, TagIndex
order by TagIndex";
sql = string.Format(sql, condition, where);
dt = DBAccess.GetRelation().ExecuteDataset(sql).Tables[0];
return(dt);
}
/// <summary>
///
/// </summary>
/// <param name="UnitID"></param>
/// <returns></returns>
public static DataTable GetSearchListC(string UnitID, string StartTime, string EndTime)
{
DataTable dt = null;
string WorkID = KPI_UnitDal.GetWorkIDByID(UnitID);
DataTable dtItems = Race_TagDal.GetTags(UnitID);
if (dtItems == null && dtItems.Rows.Count <= 0)
{
return(dt);
}
string condition = "";
for (int i = 0; i < dtItems.Rows.Count; i++)
{
string ItemID = dtItems.Rows[i]["TagID"].ToString();
string ItemDesc = dtItems.Rows[i]["TagDesc"].ToString();
string ItemEngunit = dtItems.Rows[i]["TagEngunit"].ToString();
condition += "cast(avg(case TagID when '" + ItemID + "' then TagValue else null end) as numeric(18,4)) as '" + ItemDesc + "[" + ItemEngunit + "]', ";
}
condition = condition.Remove(condition.LastIndexOf(','));
string where = " where UnitID= '" + UnitID + "' and TagStartTime >='" + StartTime + "' and TagStartTime <'" + EndTime + "'";
string sql = @"select TagShift As '运行值', {0}
from Race_Archive
{1}
group by TagShift";
sql = string.Format(sql, condition, where);
dt = DBAccess.GetRelation().ExecuteDataset(sql).Tables[0];
return(dt);
}
/// <summary>
///
/// </summary>
/// <param name="UnitID"></param>
/// <returns></returns>
public static DataTable GetSearchListC(string UnitID)
{
DataTable dt = null;
string WorkID = KPI_UnitDal.GetWorkIDByCode(UnitID);
DataTable dtItems = KPI_RaceTagDal.GetTags(UnitID);
if (dtItems == null && dtItems.Rows.Count <= 0)
{
return(dt);
}
string condition = "";
for (int i = 0; i < dtItems.Rows.Count; i++)
{
string ItemID = dtItems.Rows[i]["TagID"].ToString();
string ItemDesc = dtItems.Rows[i]["TagDesc"].ToString();
string ItemEngunit = dtItems.Rows[i]["TagEngunit"].ToString();
condition += "cast(avg(case TagID when '" + ItemID + "' then TagValue else null end) as numeric(18,4)) as '" + ItemDesc + "[" + ItemEngunit + "]', ";
}
condition = condition.Remove(condition.LastIndexOf(','));
string sql = @"select ShiftName As '运行值', {0}
from Race_Snapshot
a left outer join Race_Shift b on a.TagShift = b.ShiftID
group by ShiftName, ShiftID
order by b.ShiftID";
sql = string.Format(sql, condition);
dt = DBAccess.GetRelation().ExecuteDataset(sql).Tables[0];
return(dt);
}
