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