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