/// <summary> /// 通过时间变更小时数 /// </summary> /// <param name="WorkID">主键</param> /// <returns></returns> public static double GetIDLHour(string WorkID) { double IDLHour = 1; KPI_WorkEntity entity = KPI_WorkDal.GetEntity(WorkID); int nBaseShifts = entity.WorkBaseShifts; string strSequence = entity.WorkSequence; //strSequence = strSequence.Remove(strSequence.LastIndexOf('-')); string[] AllPeriods = strSequence.Split(new string[] { "-" }, StringSplitOptions.RemoveEmptyEntries); //当天班的顺序 //string[] strCurrentPeriod = new string[nBaseShifts]; for (int i = 0; i < nBaseShifts; i++) { string strC = AllPeriods[nBaseShifts + i]; KPI_PeriodEntity pd = KPI_PeriodDal.GetEntity(strC); int nIDL = int.Parse(pd.PeriodIsIDL); if (nIDL == 1) { IDLHour = Convert.ToDouble(pd.PeriodEndHour); break; } } return(IDLHour); }
/// <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="WorkID">主键</param> /// <param name="value">设定值</param> /// <returns></returns> public static bool Update(string WorkID, string WorkCode, string WorkDesc, string WorkIsValid, string WorkNote) { KPI_WorkEntity mEntity = new KPI_WorkEntity(); //mEntity.WorkID = WorkID; ////mEntity.WorkName = strName; //mEntity.WorkCode = WorkCode; //mEntity.WorkDesc = WorkDesc; //mEntity.WorkIsValid = int.Parse(WorkIsValid); //mEntity.WorkNote = WorkNote; ////mEntity.WorkCreateTime = DateTime.Now.ToLocalTime().ToString("yyyy-MM-dd HH:mm:ss"); //mEntity.WorkModifyTime = DateTime.Now.ToLocalTime().ToString("yyyy-MM-dd HH:mm:ss"); return(KPI_WorkDal.Update(mEntity)); }
/// <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="WorkID">主键</param> /// <returns></returns> public static bool GetShiftAndPeriod(string WorkID, string QueryTime, ref string ShiftName, ref string PeriodName, ref string StartTime, ref string EndTime) { ShiftName = ""; PeriodName = ""; StartTime = ""; EndTime = ""; KPI_WorkEntity entity = KPI_WorkDal.GetEntity(WorkID); DateTime dtQueryTime = DateTime.Parse(QueryTime); string strStartTime = entity.WorkStartTime; int nBaseShifts = entity.WorkBaseShifts; int nBaseDays = entity.WorkBaseDays; string strSequence = entity.WorkSequence; string strShift = entity.WorkShift; //判断是否查询时间超出范围 if (DateTime.Parse(strStartTime) > dtQueryTime) { return(false); } //int[] timearray = new int[6] { dtQueryTime.Year, dtQueryTime.Month, dtQueryTime.Day, dtQueryTime.Hour, 0, 0 }; DateTime dtStartTime = dtQueryTime; DateTime dtEndTime = dtQueryTime; //获得值班顺序 //strSequence = strSequence.Remove(strSequence.LastIndexOf('-')); string[] AllPeriods = strSequence.Split(new string[] { "-" }, StringSplitOptions.RemoveEmptyEntries); //strShift = strShift.Remove(strShift.LastIndexOf('-')); string[] AllShifts = strShift.Split(new string[] { "-" }, StringSplitOptions.RemoveEmptyEntries); TimeSpan tspan = DateTime.Parse(QueryTime) - DateTime.Parse(strStartTime); //当天班的序号 int nDay = tspan.Days % nBaseDays; //当天班的顺序 string[] strCurrentPeriod = new string[nBaseShifts]; for (int i = 0; i < nBaseShifts; i++) { strCurrentPeriod[i] = AllPeriods[nDay * nBaseShifts + i]; } decimal CurrentHour = Convert.ToDecimal(dtQueryTime.Hour + dtQueryTime.Minute / 60.0m); //DateTime dt bool bPeriod = false; bool bLastDay = false; int id = 0; for (id = 0; id < nBaseShifts; id++) { bPeriod = false; bLastDay = false; string strP = strCurrentPeriod[id]; KPI_PeriodEntity pe = KPI_PeriodDal.GetEntity(strP); int nIDL = int.Parse(pe.PeriodIsIDL); if (pe.PeriodStartHour == -1) { continue; } if (nIDL == 0 && (CurrentHour >= pe.PeriodStartHour && CurrentHour < pe.PeriodEndHour)) { bPeriod = true; //班名称,开始时间,结束时间 PeriodName = pe.PeriodName; //班开始结束时间的小时、分钟转换,考虑有半点交班的情况。 int sh = (int)Math.Floor(pe.PeriodStartHour); int sm = (int)((pe.PeriodStartHour - sh) * 60); int eh = (int)Math.Floor(pe.PeriodEndHour); int em = (int)((pe.PeriodEndHour - eh) * 60); dtStartTime = new DateTime(dtQueryTime.Year, dtQueryTime.Month, dtQueryTime.Day, sh, sm, 0); dtEndTime = new DateTime(dtQueryTime.Year, dtQueryTime.Month, dtQueryTime.Day, eh, em, 0); } if (nIDL == 1 && (CurrentHour >= pe.PeriodStartHour && CurrentHour < 24)) { bPeriod = true; //班名称,开始时间,结束时间 PeriodName = pe.PeriodName; //班开始结束时间的小时、分钟转换,考虑有半点交班的情况。 int sh = (int)Math.Floor(pe.PeriodStartHour); int sm = (int)((pe.PeriodStartHour - sh) * 60); int eh = (int)Math.Floor(pe.PeriodEndHour); int em = (int)((pe.PeriodEndHour - eh) * 60); dtStartTime = new DateTime(dtQueryTime.Year, dtQueryTime.Month, dtQueryTime.Day, sh, sm, 0); DateTime dtNextTime = dtQueryTime.AddDays(1); dtEndTime = new DateTime(dtNextTime.Year, dtNextTime.Month, dtNextTime.Day, eh, em, 0); } if (nIDL == 1 && (CurrentHour >= 0 && CurrentHour < pe.PeriodEndHour)) { bPeriod = true; bLastDay = true; //班名称,开始时间,结束时间 PeriodName = pe.PeriodName; //班开始结束时间的小时、分钟转换,考虑有半点交班的情况。 int sh = (int)Math.Floor(pe.PeriodStartHour); int sm = (int)((pe.PeriodStartHour - sh) * 60); int eh = (int)Math.Floor(pe.PeriodEndHour); int em = (int)((pe.PeriodEndHour - eh) * 60); DateTime dtLastTime = dtQueryTime.AddDays(-1); dtStartTime = new DateTime(dtLastTime.Year, dtLastTime.Month, dtLastTime.Day, sh, sm, 0); dtEndTime = new DateTime(dtQueryTime.Year, dtQueryTime.Month, dtQueryTime.Day, eh, em, 0); } //已经获得开始时间和结束时间。 if (bPeriod) { break; } } if (bPeriod) { if (bLastDay) { //前一天的序号 int nLastDay = (nDay + (nBaseDays - 1)) % nBaseDays; //前一天班的顺序 string[] strLastPeriod = new string[nBaseShifts]; for (int i = 0; i < nBaseShifts; i++) { strLastPeriod[i] = AllPeriods[nLastDay * nBaseShifts + i]; } for (int i = 0; i < nBaseShifts; i++) { if (strCurrentPeriod[id] == strLastPeriod[i]) { ShiftName = KPI_ShiftDal.GetShiftName(AllShifts[i]); break; } } } else { ShiftName = KPI_ShiftDal.GetShiftName(AllShifts[id]); } StartTime = dtStartTime.ToString("yyy-MM-dd HH:mm:ss"); EndTime = dtEndTime.ToString("yyy-MM-dd HH:mm:ss"); } else { return(false); } return(true); }