public static SortedDictionary <DateTime, Dictionary <int, Dictionary <string, HashSet <string> > > > BuildDateShiftLineDictionary(DataTable smtRectods)
{
SortedDictionary <DateTime, Dictionary <int, Dictionary <string, HashSet <string> > > > result = new SortedDictionary <DateTime, Dictionary <int, Dictionary <string, HashSet <string> > > >();
foreach (DataRow row in smtRectods.Rows)
{
DateTime endDate = DateTime.Parse(row["DataCzasKoniec"].ToString());
string line = row["LiniaSMT"].ToString();
dateShiftNo endDateShiftInfo = dateTools.whatDayShiftIsit(endDate);
int shiftNumer = endDateShiftInfo.shift;
if (!result.ContainsKey(endDateShiftInfo.fixedDate.Date))
{
result.Add(endDateShiftInfo.fixedDate.Date, new Dictionary <int, Dictionary <string, HashSet <string> > >());
}
if (!result[endDateShiftInfo.fixedDate.Date].ContainsKey(shiftNumer))
{
result[endDateShiftInfo.fixedDate.Date].Add(shiftNumer, new Dictionary <string, HashSet <string> >());
}
if (!result[endDateShiftInfo.fixedDate.Date][shiftNumer].ContainsKey(line))
{
result[endDateShiftInfo.fixedDate.Date][shiftNumer].Add(line, new HashSet <string>());
}
string model = row["Model"].ToString();
result[endDateShiftInfo.fixedDate.Date][shiftNumer][line].Add(model);
}
return(result);
}
public static SortedDictionary <DateTime, SortedDictionary <int, DataTable> > sortTableByDayAndShift(DataTable sqlTable, string dateColumnName)
{
//DataCzasStart,DataCzasKoniec,LiniaSMT,OperatorSMT,NrZlecenia,Model,IloscWykonana,NGIlosc,ScrapIlosc
SortedDictionary <DateTime, SortedDictionary <int, DataTable> > summaryDic = new SortedDictionary <DateTime, SortedDictionary <int, DataTable> >();
foreach (DataRow row in sqlTable.Rows)
{
string dateString = row[dateColumnName].ToString();
if (dateString == "")
{
continue;
}
//DateTime endDate = DateTime.ParseExact(dateString, "dd.MM.yyyy HH:mm:ss", CultureInfo.InvariantCulture);
DateTime endDate = DateTime.Parse(dateString);
dateShiftNo endDateShiftInfo = whatDayShiftIsit(endDate);
if (!summaryDic.ContainsKey(endDateShiftInfo.fixedDate.Date))
{
summaryDic.Add(endDateShiftInfo.fixedDate.Date, new SortedDictionary <int, DataTable>());
}
if (!summaryDic[endDateShiftInfo.fixedDate.Date].ContainsKey(endDateShiftInfo.shift))
{
summaryDic[endDateShiftInfo.fixedDate.Date].Add(endDateShiftInfo.shift, new DataTable());
summaryDic[endDateShiftInfo.fixedDate.Date][endDateShiftInfo.shift] = sqlTable.Clone();
}
summaryDic[endDateShiftInfo.fixedDate.Date][endDateShiftInfo.shift].Rows.Add(row.ItemArray);
}
return(summaryDic);
}
private static Dictionary <DateTime, Dictionary <int, List <ReworkOperationDetails> > > SortSqlTableBydateShiftPcb(DataTable sqlTable)
{
Dictionary <DateTime, Dictionary <int, List <ReworkOperationDetails> > > result = new Dictionary <DateTime, Dictionary <int, List <ReworkOperationDetails> > >();
foreach (DataRow row in sqlTable.Rows)
{
string pcbId = row["SerialNo"].ToString();
DateTime realDate = DateTime.Parse(row["Data"].ToString());
DateTime fixedDate = ViDataLoader.FixedShiftDate(realDate);
string operation = row["OpisNaprawy"].ToString();
string compRef = row["NaprawianyKomponent"].ToString();
string oper = row["Operator"].ToString();
string model = row["Model"].ToString();
bool repairResult = true;
if (row["WynikNaprawy"].ToString() != "OK")
{
repairResult = false;
}
if (!result.ContainsKey(fixedDate.Date))
{
result.Add(fixedDate.Date, new Dictionary <int, List <ReworkOperationDetails> >());
}
dateShiftNo shiftInfo = dateTools.whatDayShiftIsit(realDate);
if (!result[fixedDate.Date].ContainsKey(shiftInfo.shift))
{
result[fixedDate.Date].Add(shiftInfo.shift, new List <ReworkOperationDetails>());
}
PcbReworkData reworkItem = new PcbReworkData();
reworkItem.operations = new List <ReworkOperationDetails>();
reworkItem.id = pcbId;
ReworkOperationDetails newOperation = new ReworkOperationDetails();
newOperation.fixedDate = fixedDate;
newOperation.realDate = realDate;
newOperation.operation = operation;
newOperation.model = model;
newOperation.compRef = compRef;
newOperation.oper = oper;
newOperation.result = repairResult;
newOperation.pcbId = pcbId;
reworkItem.operations.Add(newOperation);
result[fixedDate.Date][shiftInfo.shift].Add(newOperation);
}
return(result);
}
public static Dictionary <DateTime, SortedDictionary <int, Dictionary <string, DataTable> > > GetBoxing(int daysAgo)
{
DataTable sqlTable = new DataTable();
DateTime untilDay = DateTime.Now.Date.AddDays(daysAgo * (-1)).AddHours(6);
SqlConnection conn = new SqlConnection();
conn.ConnectionString = @"Data Source=MSTMS010;Initial Catalog=MES;User Id=mes;Password=mes;";
SqlCommand command = new SqlCommand();
command.Connection = conn;
command.CommandText = String.Format(@"SELECT Boxing_Date,NC12_wyrobu,Wysylki_Nr FROM v_WyrobLG_opakowanie_all WHERE Boxing_Date>@until order by Boxing_Date;");
command.Parameters.AddWithValue("@until", untilDay);
SqlDataAdapter adapter = new SqlDataAdapter(command);
adapter.Fill(sqlTable);
sqlTable.Columns["Boxing_Date"].ColumnName = "Data";
sqlTable.Columns["NC12_wyrobu"].ColumnName = "Model";
Dictionary <DateTime, SortedDictionary <int, Dictionary <string, DataTable> > > result = new Dictionary <DateTime, SortedDictionary <int, Dictionary <string, DataTable> > >();
foreach (DataRow row in sqlTable.Rows)
{
DateTime inspTime = DateTime.Parse(row["Data"].ToString());
dateShiftNo shiftInfo = dateTools.whatDayShiftIsit(inspTime);
string model = row["Model"].ToString().Replace("LLFML", "");
if (!result.ContainsKey(shiftInfo.fixedDate.Date))
{
result.Add(shiftInfo.fixedDate.Date, new SortedDictionary <int, Dictionary <string, DataTable> >());
}
if (!result[shiftInfo.fixedDate.Date].ContainsKey(shiftInfo.shift))
{
result[shiftInfo.fixedDate.Date].Add(shiftInfo.shift, new Dictionary <string, DataTable>());
}
if (!result[shiftInfo.fixedDate.Date][shiftInfo.shift].ContainsKey(model))
{
result[shiftInfo.fixedDate.Date][shiftInfo.shift].Add(model, sqlTable.Clone());
}
result[shiftInfo.fixedDate.Date][shiftInfo.shift][model].Rows.Add(row.ItemArray);
}
return(result);
}
public static Color GetShiftColor(DateTime date)
{
dateShiftNo shiftInfo = Tools.whatDayShiftIsit(date);
if (shiftInfo.shift == 1)
{
return(Color.Plum);
}
if (shiftInfo.shift == 2)
{
return(Color.SpringGreen);
}
if (shiftInfo.shift == 3)
{
return(Color.LightGoldenrodYellow);
}
return(Color.Black);
}
///<summary>
///<para>returns shift number and shift start date and time</para>
///</summary>
public static dateShiftNo whatDayShiftIsit(DateTime inputDate)
{
int hourNow = inputDate.Hour;
DateTime resultDate = new DateTime();
int resultShift = 0;
if (hourNow < 6)
{
resultDate = new DateTime(inputDate.Date.AddDays(-1).Year, inputDate.Date.AddDays(-1).Month, inputDate.Date.AddDays(-1).Day, 22, 0, 0);
resultShift = 3;
}
else if (hourNow < 14)
{
resultDate = new DateTime(inputDate.Date.Year, inputDate.Date.Month, inputDate.Date.Day, 6, 0, 0);
resultShift = 1;
}
else if (hourNow < 22)
{
resultDate = new DateTime(inputDate.Date.Year, inputDate.Date.Month, inputDate.Date.Day, 14, 0, 0);
resultShift = 2;
}
else
{
resultDate = new DateTime(inputDate.Date.Year, inputDate.Date.Month, inputDate.Date.Day, 22, 0, 0);
resultShift = 3;
}
dateShiftNo result = new dateShiftNo();
result.fixedDate = resultDate;
result.shift = resultShift;
return(result);
}
public override bool Equals(object obj)
{
dateShiftNo dateItem = obj as dateShiftNo;
return(dateItem.fixedDate == this.fixedDate);
}
public static Dictionary <DateTime, SortedDictionary <int, Dictionary <string, Dictionary <string, DataTable> > > > GetTestMeasurements(int daysAgo)
{
Dictionary <DateTime, SortedDictionary <int, Dictionary <string, Dictionary <string, DataTable> > > > result = new Dictionary <DateTime, SortedDictionary <int, Dictionary <string, Dictionary <string, DataTable> > > >();
DataTable sqlTable = new DataTable();
string untilDay = DateTime.Now.Date.AddDays(daysAgo * (-1)).AddHours(6).ToString("yyyy-MM-dd");
SqlConnection conn = new SqlConnection();
conn.ConnectionString = @"Data Source=MSTMS010;Initial Catalog=MES;User Id=mes;Password=mes;";
SqlCommand command = new SqlCommand();
command.Connection = conn;
command.CommandText = String.Format(@"SELECT serial_no,inspection_time,wip_entity_name,tester_id FROM MES.dbo.tb_tester_measurements WHERE inspection_time>@until and tester_id<>'0' order by inspection_time;");
command.Parameters.AddWithValue("@until", untilDay);
Debug.WriteLine("allala");
SqlDataAdapter adapter = new SqlDataAdapter(command);
try
{
adapter.Fill(sqlTable);
}
catch (SqlException ex)
{
MessageBox.Show(ex.ErrorCode + Environment.NewLine + ex.Message + Environment.NewLine + ex.HResult);
return(result);
}
sqlTable.Columns["inspection_time"].ColumnName = "Data";
sqlTable.Columns["tester_id"].ColumnName = "Tester";
sqlTable.Columns["serial_no"].ColumnName = "PCB";
sqlTable.Columns["wip_entity_name"].ColumnName = "LOT";
foreach (DataRow row in sqlTable.Rows)
{
string lineID = row["Tester"].ToString();
string testerID = TestOperations.testerIdToName(lineID);
if (testerID == "")
{
continue;
}
DateTime inspTime = DateTime.Parse(row["Data"].ToString());
dateShiftNo shiftInfo = dateTools.whatDayShiftIsit(inspTime);
string lot = row["LOT"].ToString();
if (!result.ContainsKey(shiftInfo.fixedDate.Date))
{
result.Add(shiftInfo.fixedDate.Date, new SortedDictionary <int, Dictionary <string, Dictionary <string, DataTable> > >());
}
if (!result[shiftInfo.fixedDate.Date].ContainsKey(shiftInfo.shift))
{
result[shiftInfo.fixedDate.Date].Add(shiftInfo.shift, new Dictionary <string, Dictionary <string, DataTable> >());
}
if (!result[shiftInfo.fixedDate.Date][shiftInfo.shift].ContainsKey(testerID))
{
result[shiftInfo.fixedDate.Date][shiftInfo.shift].Add(testerID, new Dictionary <string, DataTable>());
}
if (!result[shiftInfo.fixedDate.Date][shiftInfo.shift][testerID].ContainsKey(lot))
{
result[shiftInfo.fixedDate.Date][shiftInfo.shift][testerID].Add(lot, sqlTable.Clone());
}
result[shiftInfo.fixedDate.Date][shiftInfo.shift][testerID][lot].Rows.Add(row.ItemArray);
}
return(result);
}
