public MainWindow()
{
InitializeComponent();
_Form1 = this;
var context = new YasunliESContext(conString);
string now = DateTime.Now.ToString("yyyy-MM-dd 00:00:00");
string query = string.Format(@"SELECT MIN(x.id) as ID, -- change to MAX if you want the highest
x.BarcodeID,
x.ScanTime,
x.Type as TypeDetail,
pt.Type,
pt.ScanCount,
i.ItemID,
i.ItemName,
i.Colour
FROM Inventory.PaintingTransactionDetail x
JOIN (SELECT p.BarcodeID,
MAX(ScanTime) AS max_ScanTime
FROM Inventory.PaintingTransactionDetail p
GROUP BY p.BarcodeID) y ON y.BarcodeID = x.BarcodeID
AND y.max_ScanTime = x.ScanTime
JOIN Inventory.PaintingTransaction as pt on pt.BarcodeID = x.BarcodeID
JOIN Inventory.DetailLabelPainting as dlp on dlp.BarcodeID = x.BarcodeID
JOIN Inventory.DetailLabel as dl on dl.BarcodeID = dlp.BarcodeIDRefference
JOIN Inventory.Label as l on l.LabelID = dl.LabelID
JOIN Inventory.Item as i on i.ItemID = l.ItemID
where x.ScanTime >= '{0}'
GROUP BY x.BarcodeID, x.ScanTime,x.Type,pt.Type,pt.ScanCount,i.ItemID,i.ItemName,i.Colour
", now);
// string query = string.Format(@"SELECT MIN(x.id) as ID, -- change to MAX if you want the highest
// x.BarcodeID,
// x.ScanTime,
// x.Type as TypeDetail,
// pt.Type,
// pt.ScanCount,
// i.ItemID,
// i.ItemName,
// i.MainModel
// FROM Inventory.PaintingTransactionDetail x
// JOIN (SELECT p.BarcodeID,
// MAX(ScanTime) AS max_ScanTime
// FROM Inventory.PaintingTransactionDetail p
// GROUP BY p.BarcodeID) y ON y.BarcodeID = x.BarcodeID
// AND y.max_ScanTime = x.ScanTime
// JOIN Inventory.PaintingTransaction as pt on pt.BarcodeID = x.BarcodeID
// JOIN Inventory.DetailLabelPainting as dlp on dlp.BarcodeID = x.BarcodeID
// JOIN Inventory.DetailLabel as dl on dl.BarcodeID = dlp.BarcodeIDRefference
// JOIN Inventory.Label as l on l.LabelID = dl.LabelID
// JOIN Inventory.Item as i on i.ItemID = l.ItemID
//GROUP BY x.BarcodeID, x.ScanTime,x.Type,pt.Type,pt.ScanCount,i.ItemID,i.ItemName,i.MainModel
//", now);
List <MonitoringPainting> getlistRawMP = new List <MonitoringPainting>();
try
{
getlistRawMP = context.Database.SqlQuery <MonitoringPainting>(query).ToList();
}
catch (Exception)
{
}
MonitoringPaintingSource._listMonitoringPainting = new List <MonitoringPainting>();
MonitoringPaintingSource._listMonitoringPainting = getlistRawMP;
var getlistNoDuplicateColour = getlistRawMP.Distinct(new DistinctColour()).ToList();
var getlistNoDuplicateItemName = getlistRawMP.Distinct(new DistinctItemName()).ToList();
List <MonitoringPaintingDetail> _listNODUPE = new List <MonitoringPaintingDetail>();
foreach (var colour in getlistNoDuplicateColour)
{
foreach (var itemname in getlistNoDuplicateItemName)
{
foreach (var itemraw in getlistRawMP)
{
if (itemraw.Colour == colour.Colour && itemraw.ItemName == itemname.ItemName)
{
var checkexistinNoDUPE = _listNODUPE.Where(x => x.Colour == itemraw.Colour && x.ItemName == itemraw.ItemName).FirstOrDefault();
if (checkexistinNoDUPE == null)
{
var a = new MonitoringPaintingDetail();
a.ItemName = itemraw.ItemName;
a.Colour = itemraw.Colour;
_listNODUPE.Add(a);
}
}
}
}
}
//MonitoringPaintingSource._listMonitoringPaintingDetail = new List<MonitoringPaintingDetail>();
foreach (var item in _listNODUPE)
{
MonitoringPaintingDetail mpdet = new MonitoringPaintingDetail();
mpdet.ItemName = item.ItemName;
mpdet.Colour = item.Colour;
foreach (var itemraw in getlistRawMP)
{
if (itemraw.Colour == item.Colour && itemraw.ItemName == item.ItemName)
{
mpdet.RawPart++;
switch (itemraw.Type)
{
case "REPAINT":
switch (itemraw.ScanCount)
{
case 0:
mpdet.R0++;
mpdet.TotalRepaint++;
break;
case 1:
mpdet.R1++;
mpdet.TotalRepaint++;
break;
case 2:
mpdet.R2++;
mpdet.TotalRepaint++;
break;
case 3:
mpdet.R3++;
mpdet.TotalRepaint++;
break;
case 4:
mpdet.R4++;
mpdet.TotalRepaint++;
break;
case 5:
mpdet.R5++;
mpdet.TotalRepaint++;
break;
}
break;
case "OKPAINTING":
switch (itemraw.ScanCount)
{
case 0:
mpdet.OK0++;
mpdet.TotalOK++;
break;
case 1:
mpdet.OK1++;
mpdet.TotalOK++;
break;
case 2:
mpdet.OK2++;
mpdet.TotalOK++;
break;
case 3:
mpdet.OK3++;
mpdet.TotalOK++;
break;
case 4:
mpdet.OK4++;
mpdet.TotalOK++;
break;
case 5:
mpdet.OK5++;
mpdet.TotalOK++;
break;
}
break;
case "NG":
switch (itemraw.ScanCount)
{
case 0:
mpdet.NG0++;
mpdet.TotalNG++;
break;
case 1:
mpdet.NG1++;
mpdet.TotalNG++;
break;
case 2:
mpdet.NG2++;
mpdet.TotalNG++;
break;
case 3:
mpdet.NG3++;
mpdet.TotalNG++;
break;
case 4:
mpdet.NG4++;
mpdet.TotalNG++;
break;
case 5:
mpdet.NG5++;
mpdet.TotalNG++;
break;
}
break;
case "RAWPART":
mpdet.R0++;
mpdet.TotalRepaint++;
break;
}
}
else
{
}
}
MonitoringPaintingSource._listMonitoringPaintingDetail.Add(mpdet);
}
second = Convert.ToInt32(ConfigurationManager.ConnectionStrings["Second"].ConnectionString);
timer_status.Elapsed += new ElapsedEventHandler(OnElapsedTime_Count);
timer_status.Interval = 1000; //number in milliseconds
timer_status.Enabled = true;
MqttClass.MQTT_Connect();
}
public static async Task HandleReceivedApplicationMessage(MqttApplicationMessageReceivedEventArgs eventArgs)
{
var item = $"Timestamp: {DateTime.Now:O} | Topic: {eventArgs.ApplicationMessage.Topic} | Payload: {eventArgs.ApplicationMessage.ConvertPayloadToString()} | QoS: {eventArgs.ApplicationMessage.QualityOfServiceLevel}";
string topic = eventArgs.ApplicationMessage.Topic;
string RecvMsg = eventArgs.ApplicationMessage.ConvertPayloadToString();
if (topic == "MONITORINGPAINTING")
{
try
{
string[] strsplit = RecvMsg.Split(',');
string NEWDATATYPE = strsplit[0];
string NEWDATASCANCOUNT = strsplit[1];
string OLDDATATYPE = strsplit[2];
string OLDDATASCANCOUNT = strsplit[3];
string ITEMNAME = strsplit[4];
string COLOUR = strsplit[5];
await Task.Run(() =>
{
if (string.IsNullOrEmpty(OLDDATATYPE) || string.IsNullOrEmpty(OLDDATASCANCOUNT))
{
var checkexist = MonitoringPaintingSource._listMonitoringPaintingDetail.Where(x => x.ItemName == ITEMNAME && x.Colour == COLOUR).FirstOrDefault();
if (checkexist != null)
{
for (int i = 0; i < MonitoringPaintingSource._listMonitoringPaintingDetail.Count; i++)
{
if (MonitoringPaintingSource._listMonitoringPaintingDetail[i].ItemName == ITEMNAME && MonitoringPaintingSource._listMonitoringPaintingDetail[i].Colour == COLOUR)
{
MonitoringPaintingSource._listMonitoringPaintingDetail[i].RawPart++;
switch (NEWDATATYPE)
{
case "REPAINT":
switch (NEWDATASCANCOUNT)
{
case "0":
MonitoringPaintingSource._listMonitoringPaintingDetail[i].TotalRepaint++;
MonitoringPaintingSource._listMonitoringPaintingDetail[i].R0++;
break;
case "1":
MonitoringPaintingSource._listMonitoringPaintingDetail[i].TotalRepaint++;
MonitoringPaintingSource._listMonitoringPaintingDetail[i].R1++;
break;
case "2":
MonitoringPaintingSource._listMonitoringPaintingDetail[i].TotalRepaint++;
MonitoringPaintingSource._listMonitoringPaintingDetail[i].R2++;
break;
case "3":
MonitoringPaintingSource._listMonitoringPaintingDetail[i].TotalRepaint++;
MonitoringPaintingSource._listMonitoringPaintingDetail[i].R3++;
break;
case "4":
MonitoringPaintingSource._listMonitoringPaintingDetail[i].TotalRepaint++;
MonitoringPaintingSource._listMonitoringPaintingDetail[i].R4++;
break;
case "5":
MonitoringPaintingSource._listMonitoringPaintingDetail[i].TotalRepaint++;
MonitoringPaintingSource._listMonitoringPaintingDetail[i].R5++;
break;
}
break;
case "OKPAINTING":
switch (NEWDATASCANCOUNT)
{
case "0":
MonitoringPaintingSource._listMonitoringPaintingDetail[i].TotalOK++;
MonitoringPaintingSource._listMonitoringPaintingDetail[i].OK0++;
break;
case "1":
MonitoringPaintingSource._listMonitoringPaintingDetail[i].TotalOK++;
MonitoringPaintingSource._listMonitoringPaintingDetail[i].OK1++;
break;
case "2":
MonitoringPaintingSource._listMonitoringPaintingDetail[i].TotalOK++;
MonitoringPaintingSource._listMonitoringPaintingDetail[i].OK2++;
break;
case "3":
MonitoringPaintingSource._listMonitoringPaintingDetail[i].TotalOK++;
MonitoringPaintingSource._listMonitoringPaintingDetail[i].OK3++;
break;
case "4":
MonitoringPaintingSource._listMonitoringPaintingDetail[i].TotalOK++;
MonitoringPaintingSource._listMonitoringPaintingDetail[i].OK4++;
break;
case "5":
MonitoringPaintingSource._listMonitoringPaintingDetail[i].TotalOK++;
MonitoringPaintingSource._listMonitoringPaintingDetail[i].OK5++;
break;
}
break;
case "NG":
switch (NEWDATASCANCOUNT)
{
case "0":
MonitoringPaintingSource._listMonitoringPaintingDetail[i].TotalNG++;
MonitoringPaintingSource._listMonitoringPaintingDetail[i].NG0++;
break;
case "1":
MonitoringPaintingSource._listMonitoringPaintingDetail[i].TotalNG++;
MonitoringPaintingSource._listMonitoringPaintingDetail[i].NG1++;
break;
case "2":
MonitoringPaintingSource._listMonitoringPaintingDetail[i].TotalNG++;
MonitoringPaintingSource._listMonitoringPaintingDetail[i].NG2++;
break;
case "3":
MonitoringPaintingSource._listMonitoringPaintingDetail[i].TotalNG++;
MonitoringPaintingSource._listMonitoringPaintingDetail[i].NG3++;
break;
case "4":
MonitoringPaintingSource._listMonitoringPaintingDetail[i].TotalNG++;
MonitoringPaintingSource._listMonitoringPaintingDetail[i].NG4++;
break;
case "5":
MonitoringPaintingSource._listMonitoringPaintingDetail[i].TotalNG++;
MonitoringPaintingSource._listMonitoringPaintingDetail[i].NG5++;
break;
}
break;
case "RAWPART":
MonitoringPaintingSource._listMonitoringPaintingDetail[i].TotalRepaint++;
MonitoringPaintingSource._listMonitoringPaintingDetail[i].R0++;
break;
}
break;
}
}
}
else
{
MonitoringPaintingDetail mpdet = new MonitoringPaintingDetail();
mpdet.ItemName = ITEMNAME;
mpdet.Colour = COLOUR;
mpdet.RawPart++;
switch (NEWDATATYPE)
{
case "REPAINT":
switch (NEWDATASCANCOUNT)
{
case "0":
mpdet.TotalRepaint++;
mpdet.R0++;
break;
case "1":
mpdet.TotalRepaint++;
mpdet.R1++;
break;
case "2":
mpdet.TotalRepaint++;
mpdet.R2++;
break;
case "3":
mpdet.TotalRepaint++;
mpdet.R3++;
break;
case "4":
mpdet.TotalRepaint++;
mpdet.R4++;
break;
case "5":
mpdet.TotalRepaint++;
mpdet.R5++;
break;
}
break;
case "OKPAINTING":
switch (NEWDATASCANCOUNT)
{
case "0":
mpdet.TotalOK++;
mpdet.OK0++;
break;
case "1":
mpdet.TotalOK++;
mpdet.OK1++;
break;
case "2":
mpdet.TotalOK++;
mpdet.OK2++;
break;
case "3":
mpdet.TotalOK++;
mpdet.OK3++;
break;
case "4":
mpdet.TotalOK++;
mpdet.OK4++;
break;
case "5":
mpdet.TotalOK++;
mpdet.OK5++;
break;
}
break;
case "NG":
switch (NEWDATASCANCOUNT)
{
case "0":
mpdet.TotalNG++;
mpdet.NG0++;
break;
case "1":
mpdet.TotalNG++;
mpdet.NG1++;
break;
case "2":
mpdet.TotalNG++;
mpdet.NG2++;
break;
case "3":
mpdet.TotalNG++;
mpdet.NG3++;
break;
case "4":
mpdet.TotalNG++;
mpdet.NG4++;
break;
case "5":
mpdet.TotalNG++;
mpdet.NG5++;
break;
}
break;
case "RAWPART":
mpdet.TotalRepaint++;
mpdet.R0++;
break;
}
MonitoringPaintingSource._listMonitoringPaintingDetail.Add(mpdet);
}
}
else
{
if (MonitoringPaintingSource._listMonitoringPaintingDetail.Count > 0)
{
for (int i = 0; i < MonitoringPaintingSource._listMonitoringPaintingDetail.Count; i++)
{
if (MonitoringPaintingSource._listMonitoringPaintingDetail[i].ItemName == ITEMNAME)
{
if (MonitoringPaintingSource._listMonitoringPaintingDetail[i].ItemName == ITEMNAME && MonitoringPaintingSource._listMonitoringPaintingDetail[i].Colour == COLOUR)
{
#region NEWDATATYPE
MonitoringPaintingSource._listMonitoringPaintingDetail[i].RawPart++;
switch (NEWDATATYPE)
{
case "REPAINT":
switch (NEWDATASCANCOUNT)
{
case "0":
MonitoringPaintingSource._listMonitoringPaintingDetail[i].TotalRepaint++;
MonitoringPaintingSource._listMonitoringPaintingDetail[i].R0++;
break;
case "1":
MonitoringPaintingSource._listMonitoringPaintingDetail[i].TotalRepaint++;
MonitoringPaintingSource._listMonitoringPaintingDetail[i].R1++;
break;
case "2":
MonitoringPaintingSource._listMonitoringPaintingDetail[i].TotalRepaint++;
MonitoringPaintingSource._listMonitoringPaintingDetail[i].R2++;
break;
case "3":
MonitoringPaintingSource._listMonitoringPaintingDetail[i].TotalRepaint++;
MonitoringPaintingSource._listMonitoringPaintingDetail[i].R3++;
break;
case "4":
MonitoringPaintingSource._listMonitoringPaintingDetail[i].TotalRepaint++;
MonitoringPaintingSource._listMonitoringPaintingDetail[i].R4++;
break;
case "5":
MonitoringPaintingSource._listMonitoringPaintingDetail[i].TotalRepaint++;
MonitoringPaintingSource._listMonitoringPaintingDetail[i].R5++;
break;
}
break;
case "OKPAINTING":
switch (NEWDATASCANCOUNT)
{
case "0":
MonitoringPaintingSource._listMonitoringPaintingDetail[i].TotalOK++;
MonitoringPaintingSource._listMonitoringPaintingDetail[i].OK0++;
break;
case "1":
MonitoringPaintingSource._listMonitoringPaintingDetail[i].TotalOK++;
MonitoringPaintingSource._listMonitoringPaintingDetail[i].OK1++;
break;
case "2":
MonitoringPaintingSource._listMonitoringPaintingDetail[i].TotalOK++;
MonitoringPaintingSource._listMonitoringPaintingDetail[i].OK2++;
break;
case "3":
MonitoringPaintingSource._listMonitoringPaintingDetail[i].TotalOK++;
MonitoringPaintingSource._listMonitoringPaintingDetail[i].OK3++;
break;
case "4":
MonitoringPaintingSource._listMonitoringPaintingDetail[i].TotalOK++;
MonitoringPaintingSource._listMonitoringPaintingDetail[i].OK4++;
break;
case "5":
MonitoringPaintingSource._listMonitoringPaintingDetail[i].TotalOK++;
MonitoringPaintingSource._listMonitoringPaintingDetail[i].OK5++;
break;
}
break;
case "NG":
switch (NEWDATASCANCOUNT)
{
case "0":
MonitoringPaintingSource._listMonitoringPaintingDetail[i].TotalNG++;
MonitoringPaintingSource._listMonitoringPaintingDetail[i].NG0++;
break;
case "1":
MonitoringPaintingSource._listMonitoringPaintingDetail[i].TotalNG++;
MonitoringPaintingSource._listMonitoringPaintingDetail[i].NG1++;
break;
case "2":
MonitoringPaintingSource._listMonitoringPaintingDetail[i].TotalNG++;
MonitoringPaintingSource._listMonitoringPaintingDetail[i].NG2++;
break;
case "3":
MonitoringPaintingSource._listMonitoringPaintingDetail[i].TotalNG++;
MonitoringPaintingSource._listMonitoringPaintingDetail[i].NG3++;
break;
case "4":
MonitoringPaintingSource._listMonitoringPaintingDetail[i].TotalNG++;
MonitoringPaintingSource._listMonitoringPaintingDetail[i].NG4++;
break;
case "5":
MonitoringPaintingSource._listMonitoringPaintingDetail[i].TotalNG++;
MonitoringPaintingSource._listMonitoringPaintingDetail[i].NG5++;
break;
}
break;
case "RAWPART":
MonitoringPaintingSource._listMonitoringPaintingDetail[i].TotalRepaint++;
MonitoringPaintingSource._listMonitoringPaintingDetail[i].R0++;
break;
}
#endregion
#region OLDDATATYPE
MonitoringPaintingSource._listMonitoringPaintingDetail[i].RawPart--;
switch (OLDDATATYPE)
{
case "REPAINT":
switch (OLDDATASCANCOUNT)
{
case "0":
MonitoringPaintingSource._listMonitoringPaintingDetail[i].TotalRepaint--;
MonitoringPaintingSource._listMonitoringPaintingDetail[i].R0--;
break;
case "1":
MonitoringPaintingSource._listMonitoringPaintingDetail[i].TotalRepaint--;
MonitoringPaintingSource._listMonitoringPaintingDetail[i].R1--;
break;
case "2":
MonitoringPaintingSource._listMonitoringPaintingDetail[i].TotalRepaint--;
MonitoringPaintingSource._listMonitoringPaintingDetail[i].R2--;
break;
case "3":
MonitoringPaintingSource._listMonitoringPaintingDetail[i].TotalRepaint--;
MonitoringPaintingSource._listMonitoringPaintingDetail[i].R3--;
break;
case "4":
MonitoringPaintingSource._listMonitoringPaintingDetail[i].TotalRepaint--;
MonitoringPaintingSource._listMonitoringPaintingDetail[i].R4--;
break;
case "5":
MonitoringPaintingSource._listMonitoringPaintingDetail[i].TotalRepaint--;
MonitoringPaintingSource._listMonitoringPaintingDetail[i].R5--;
break;
}
break;
case "OKPAINTING":
switch (OLDDATASCANCOUNT)
{
case "0":
MonitoringPaintingSource._listMonitoringPaintingDetail[i].TotalOK--;
MonitoringPaintingSource._listMonitoringPaintingDetail[i].OK0--;
break;
case "1":
MonitoringPaintingSource._listMonitoringPaintingDetail[i].TotalOK--;
MonitoringPaintingSource._listMonitoringPaintingDetail[i].OK1--;
break;
case "2":
MonitoringPaintingSource._listMonitoringPaintingDetail[i].TotalOK--;
MonitoringPaintingSource._listMonitoringPaintingDetail[i].OK2--;
break;
case "3":
MonitoringPaintingSource._listMonitoringPaintingDetail[i].TotalOK--;
MonitoringPaintingSource._listMonitoringPaintingDetail[i].OK3--;
break;
case "4":
MonitoringPaintingSource._listMonitoringPaintingDetail[i].TotalOK--;
MonitoringPaintingSource._listMonitoringPaintingDetail[i].OK4--;
break;
case "5":
MonitoringPaintingSource._listMonitoringPaintingDetail[i].TotalOK--;
MonitoringPaintingSource._listMonitoringPaintingDetail[i].OK5--;
break;
}
break;
case "NG":
switch (OLDDATASCANCOUNT)
{
case "0":
MonitoringPaintingSource._listMonitoringPaintingDetail[i].TotalNG--;
MonitoringPaintingSource._listMonitoringPaintingDetail[i].NG0--;
break;
case "1":
MonitoringPaintingSource._listMonitoringPaintingDetail[i].TotalNG--;
MonitoringPaintingSource._listMonitoringPaintingDetail[i].NG1--;
break;
case "2":
MonitoringPaintingSource._listMonitoringPaintingDetail[i].TotalNG--;
MonitoringPaintingSource._listMonitoringPaintingDetail[i].NG2--;
break;
case "3":
MonitoringPaintingSource._listMonitoringPaintingDetail[i].TotalNG--;
MonitoringPaintingSource._listMonitoringPaintingDetail[i].NG3--;
break;
case "4":
MonitoringPaintingSource._listMonitoringPaintingDetail[i].TotalNG--;
MonitoringPaintingSource._listMonitoringPaintingDetail[i].NG4--;
break;
case "5":
MonitoringPaintingSource._listMonitoringPaintingDetail[i].TotalNG--;
MonitoringPaintingSource._listMonitoringPaintingDetail[i].NG5--;
break;
}
break;
case "RAWPART":
MonitoringPaintingSource._listMonitoringPaintingDetail[i].TotalRepaint--;
MonitoringPaintingSource._listMonitoringPaintingDetail[i].R0--;
break;
}
#endregion
break;
}
}
}
}
}
MainWindow._Form1.dashboardcontrol1.Dispatcher.Invoke(() =>
{
MainWindow._Form1.dashboardcontrol1.ReloadData();
});
});
}
catch (Exception ex)
{
}
}
}