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