private void CaculateMachineHoursInRough(ref HoursSpan[] hourSpan)
{
int index = 0;
foreach (var machine in machineGroups[selectedMachineGroup].Machines)
{
hourSpan[index].off = new TimeSpan(0);
hourSpan[index].idle = new TimeSpan(0);
hourSpan[index].alarm = new TimeSpan(0);
hourSpan[index].valid = new TimeSpan(0);
try
{
List <OEETypes.StatusPoint> statusPoint = machine.Status.statusPoints;
DateTime startPoint = statusPoint[0].time;
int startStatus = statusPoint[0].status;
for (int i = 1; i < statusPoint.Count; i++)
{
DateTime endPoint = statusPoint[i].time;
CalculateHours(startPoint, endPoint, startStatus, ref hourSpan[index]);
startPoint = statusPoint[i].time;
startStatus = statusPoint[i].status;
}
if (statusPoint.Count % 2 != 0)
{
DateTime endPoint = OEEUtils.ConvertToEndOfDay(startPoint);
CalculateHours(startPoint, endPoint, startStatus, ref hourSpan[index]);
}
index++;
}
catch (Exception)
{
}
}
}
private void QueryPrepareTime(DateTime startTime, DateTime endTime)
{
DateTime queryStartTime = OEEUtils.ConvertToStartOfDay(startTime);
DateTime queryEndTime = OEEUtils.ConvertToEndOfDay(endTime);
string dataBaseName = getDeviceDatabaseName();
DataTable prepareDataTable = new DataTable();
string commandText = "select startTime, prepareTimePoint from " + "`" + gVariable.dispatchListTableName + "`" +
" where ((prepareTimePoint >= '" + queryStartTime + "' and startTime <= '" + queryEndTime + "')";
prepareDataTable = mySQLClass.queryDataTableAction(dataBaseName, commandText, null);
CalculatePrepareTime(prepareDataTable, queryStartTime, queryEndTime);
}
public DataTable QueryLabourHourDetails(int year, int month, string name, OEEFactory factory)
{
DateTime queryStartTime = OEEUtils.ConvertToDayStartOfMonth(year, month);
DateTime queryEndTime = OEEUtils.ConvertToDayEndOfMonth(year, month);
OEEMachineGroup[] machineGroups = factory.MachineGroups;
DataTable dt = null;
for (int groupIndex = 0; groupIndex < machineGroups.Count(); groupIndex++)
{
foreach (OEEMachine machine in machineGroups[groupIndex].Machines)
{
DataTable temp = new DataTable();
string dataBaseName = machine.getDeviceDatabaseName();
string commandText = "select dispatchCode," +
"productCode," +
"productName," +
"processName," +
"serialNumber," +
"startTime," +
"completeTime," +
"qualifyNum, " +
"unqualifyNum," +
"machineID from " +
"`" + gVariable.dispatchListTableName + "`" +
" where completeTime >= '" + queryStartTime.ToString(OEETypes.QUERY_TIME_FORMAT) +
"' and completeTime <= '" + queryEndTime.ToString(OEETypes.QUERY_TIME_FORMAT) +
"' and reportor = '" + _id + "'";
temp = mySQLClass.queryDataTableAction(dataBaseName, commandText, null);
try
{
object[] obj = new object[temp.Columns.Count];
if (dt == null)
{
dt = temp.Clone();
}
for (int i = 0; i < temp.Rows.Count; i++)
{
temp.Rows[i].ItemArray.CopyTo(obj, 0);
dt.Rows.Add(obj);
}
}
catch (Exception)
{
}
}
}
return(dt);
}
private void QueryMaintaincePeriod(DateTime startTime, DateTime endTime)
{
DateTime queryStartTime = OEEUtils.ConvertToStartOfDay(startTime);
DateTime queryEndTime = OEEUtils.ConvertToEndOfDay(endTime);
string dataBaseName = getDeviceDatabaseName();
string commandText = "select timestamp1, timestamp2 from " + "`" + gVariable.machineWorkingPlanTableName + "`" +
" where ((timestamp1 < '" + queryStartTime + "' and timestamp2 >= '" + queryStartTime + "')" +
" or (timestamp1 <= '" + queryStartTime + "' and timestamp2 <= '" + queryEndTime + "')" +
" or (timeStamp2 <= '" + queryEndTime + "' and timestamp2 > '" + queryEndTime + "')";
maintainanceDataTable = mySQLClass.queryDataTableAction(dataBaseName, commandText, null);
CalculateMaintainance(maintainanceDataTable, queryStartTime, queryEndTime);
}
// Query machineStatusRecord
private void QueryHours(DateTime startTime, DateTime endTime)
{
DateTime queryStartTime = OEEUtils.ConvertToStartOfDay(startTime);
DateTime queryEndTime = OEEUtils.ConvertToEndOfDay(endTime);
string dataBaseName = getDeviceDatabaseName();
DateTime startTimePoint = TimeZone.CurrentTimeZone.ToLocalTime(new DateTime(1970, 1, 1));
System.Console.WriteLine("database name: {0}", this.Name);
double startTime_offset = (queryStartTime - startTimePoint).TotalSeconds;
double endTime_offset = (queryEndTime - startTimePoint).TotalSeconds;
string commandText = "select * from " + "`" + gVariable.machineStatusRecordTableName + "`" +
" where time >= '" + startTime_offset + "' and time <= '" + endTime_offset + "'";
hoursDataTable = mySQLClass.queryDataTableAction(dataBaseName, commandText, null);
CalculateStatus(hoursDataTable);
}
private void BtnStaffQuery_Click(object sender, EventArgs e)
{
int yearStart = Convert.ToInt32(this.CmbYearStart.Text);
int yearEnd = Convert.ToInt32(this.CmbYearEnd.Text);
int monthStart = Convert.ToInt32(this.CmbMonthStart.Text);
int monthEnd = Convert.ToInt32(this.CmbMonthEnd.Text);
DateTime startTime = OEEUtils.ConvertToDayStartOfMonth(yearStart, monthStart);
DateTime endTime = OEEUtils.ConvertToDayEndOfMonth(yearEnd, monthEnd);
totalIndex = 1;
this.LvwMonthView.Items.Clear();
this.LvwDetailView.Items.Clear();
if (DateTime.Compare(startTime, endTime) > 0)
{
MessageBox.Show("起始时间不能大于终止时间", "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
else
{
string id = this.CmbStaffName.Text.Split(' ').First();
selectedStaff = staffs[id];
for (int year = yearStart; year <= yearEnd; year++)
{
if (monthEnd < monthStart)
{
monthEnd = OEETypes.MONTH_END;
}
for (int month = monthStart; month <= monthEnd; month++)
{
OEETypes.OutputInHours outputInHours;
DateTime start = OEEUtils.ConvertToDayStartOfMonth(year, month);
DateTime end = OEEUtils.ConvertToDayEndOfMonth(year, month);
outputInHours = selectedStaff.QueryLabourHour(start, end, factory);
ShowMonthList(outputInHours, selectedStaff.Name, year, month);
}
monthStart = 1;
}
}
}
private gVariable.dispatchSheetStruct[] QueryStartedDispatches(DateTime startTime, DateTime endTime)
{
gVariable.dispatchSheetStruct[] dispatches;
string dataBaseName = getDeviceDatabaseName();
DateTime queryStartTime = OEEUtils.ConvertToStartOfDay(startTime);
DateTime queryEndTime = OEEUtils.ConvertToEndOfDay(endTime);
if (null != dataBaseName)
{
dispatches = mySQLClass.getDispatchListInPeriodOfTime(dataBaseName, gVariable.dispatchListTableName,
queryStartTime.ToString(OEETypes.QUERY_TIME_FORMAT), queryEndTime.ToString(OEETypes.QUERY_TIME_FORMAT),
gVariable.MACHINE_STATUS_DISPATCH_STARTED, gVariable.TIME_CHECK_TYPE_REAL_START);
}
else
{
dispatches = null;
}
return(dispatches);
}
private void QueryCapacity(DateTime startTime, DateTime endTime)
{
string dataBaseName = getDeviceDatabaseName();
DateTime queryStartTime = OEEUtils.ConvertToStartOfDay(startTime);
DateTime queryEndTime = OEEUtils.ConvertToEndOfDay(endTime);
gVariable.dispatchSheetStruct[] dispatches;
if (null != dataBaseName)
{
dispatches = mySQLClass.getDispatchListInPeriodOfTime(dataBaseName, gVariable.dispatchListTableName,
queryStartTime.ToString(OEETypes.QUERY_TIME_FORMAT), queryEndTime.ToString(OEETypes.QUERY_TIME_FORMAT),
gVariable.MACHINE_STATUS_DISPATCH_COMPLETED, gVariable.TIME_CHECK_TYPE_PLANNED_START);
}
else
{
dispatches = null;
}
CalculateCapacity(dispatches);
}
private void QueryMachineStatus()
{
int year = Convert.ToInt32(this.CmbYear.Text);
if (this.CmbQueryPeriod.SelectedIndex == 0)
{
startTime = new DateTime(year, OEETypes.MONTH_START, OEETypes.DAY_START);
endTime = new DateTime(year, OEETypes.MONTH_END, OEETypes.DAY_SOLAR_END);
}
else
{
int month = Convert.ToInt32(this.CmbMonth.Text);
startTime = OEEUtils.ConvertToDayStartOfMonth(year, month);
endTime = OEEUtils.ConvertToDayEndOfMonth(year, month);
}
foreach (OEEMachine machine in machines)
{
machine.QueryMachineStatus(startTime, endTime);
}
}
public OEETypes.OutputInHours QueryLabourHour(DateTime startTime, DateTime endTime, OEEFactory factory)
{
DateTime queryStartTime = OEEUtils.ConvertToStartOfDay(startTime);
DateTime queryEndTime = OEEUtils.ConvertToEndOfDay(endTime);
OEEMachineGroup[] machineGroups = factory.MachineGroups;
DataTable dt = null;
for (int groupIndex = 0; groupIndex < machineGroups.Count(); groupIndex++)
{
foreach (OEEMachine machine in machineGroups[groupIndex].Machines)
{
DataTable temp = new DataTable();
string dataBaseName = machine.getDeviceDatabaseName();
string commandText = "select startTime, completeTime, qualifyNum, unqualifyNum from " + "`" + gVariable.dispatchListTableName + "`" +
" where completeTime >= '" + queryStartTime.ToString(OEETypes.QUERY_TIME_FORMAT) + "' and completeTime <= '" + queryEndTime.ToString(OEETypes.QUERY_TIME_FORMAT) + "' and reportor = '" + _id + "'";
temp = mySQLClass.queryDataTableAction(dataBaseName, commandText, null);
try
{
object[] obj = new object[temp.Columns.Count];
if (dt == null)
{
dt = temp.Clone();
}
for (int i = 0; i < temp.Rows.Count; i++)
{
temp.Rows[i].ItemArray.CopyTo(obj, 0);
dt.Rows.Add(obj);
}
}
catch (Exception)
{
}
}
}
return(CalculateLabourHour(dt));
}
private void ShowCastingHoursList()
{
DataGridView gridView = this.HoursDataGridView;
gridView.Rows.Clear();
AddDataGridViewHeaders();
int index = 0;
for (DateTime date = startDate; date <= endDate; date = date.AddDays(1))
{
int paramsLength = machineGroups[selectedMachineGroup].Count * MACHINEHOUR_LIST_HEADER.Length + MACHINEHOUR_LIST_ADDITION.Length;
string[] value = new string[paramsLength];
int col = 0;
int total = (int)Math.Ceiling((endDate - startDate).TotalMinutes);
DateTime qStart = OEEUtils.ConvertToStartOfDay(date);;
DateTime qEnd = OEEUtils.ConvertToEndOfDay(date);
DateTime startTimePoint = TimeZone.CurrentTimeZone.ToLocalTime(new DateTime(1970, 1, 1));
long startTime_offset = (long)Math.Ceiling((startDate - startTimePoint).TotalSeconds);
long endTime_offset = (long)Math.Ceiling((qEnd - startTimePoint).TotalSeconds);
value[col++] = index.ToString();
value[col++] = date.ToString("MM/dd");
foreach (OEEMachine machine in machineGroups[selectedMachineGroup].Machines)
{
DataRow[] dr;
int off = 0;
int idle = 0;
int work = 0;
int alarm = 0;
int valid = 0;
string cmd;
OEETypes.MachineStatus machineStatus = machine.Status;
DataTable dt = machineStatus.hoursDataTable;
try
{
value[col++] = total.ToString();
cmd = "time>'" + startTime_offset + "' and time<'" + endTime_offset + "' and value1='" + gVariable.MACHINE_STATUS_DOWN + "'";
dr = dt.Select(cmd);
off = dr.Count();
value[col++] = off.ToString();
cmd = "time>'" + startTime_offset + "' and time<'" + endTime_offset + "' and value1='" + gVariable.MACHINE_STATUS_IDLE + "'";
dr = dt.Select(cmd);
idle = dr.Count();
value[col++] = idle.ToString();
cmd = "time>'" + startTime_offset + "' and time<'" + endTime_offset + "' and value1='" + gVariable.MACHINE_STATUS_STARTED + "'";
dr = dt.Select(cmd);
work = dr.Count();
value[col++] = work.ToString();
cmd = "time>'" + startTime_offset + "' and time<'" + endTime_offset + "' and value1>='" + gVariable.MACHINE_STATUS_DEVICE_ALARM + "'";
dr = dt.Select(cmd);
alarm = dr.Count();
value[col++] = alarm.ToString();
valid = total - idle - alarm;
value[col++] = valid.ToString();
}
catch (Exception)
{
off = 0;
value[col++] = off.ToString();
idle = 0;
value[col++] = idle.ToString();
work = 0;
value[col++] = work.ToString();
alarm = 0;
value[col++] = alarm.ToString();
valid = 0;
value[col++] = valid.ToString();
}
}
index++;
gridView.Rows.Add(value);
}
}