private static double CalculateSDCM(PcbTesterMeasurements testResults, ModelSpecification modelSPecification)
{
double result = 0;
if (modelSPecification.A > 0)
{
double partA = Math.Pow((modelSPecification.Cx - testResults.Cx) * modelSPecification.A - (modelSPecification.Cy - testResults.Cy) * modelSPecification.B, 2) / Math.Pow(modelSPecification.C, 2);
double partB = Math.Pow((modelSPecification.Cx - testResults.Cx) * modelSPecification.B + (modelSPecification.Cy - testResults.Cy) * modelSPecification.A, 2) / Math.Pow(modelSPecification.D, 2);
double AllensFactor = 0.04;
result = Math.Sqrt(partA + partB);
if (result > 0.04)
{
result = result - AllensFactor;
}
}
return(result);
}
public static bool CheckTestMeasurements(Dictionary <string, PcbTesterMeasurements> testResults, Dictionary <string, ModelSpecification> modelSPecification, ref string currentModel, DataGridView grid, Label infoLabel)
{
bool result = true;
DataTable ngResult = new DataTable();
DataTable okResult = new DataTable();
HashSet <string> modelCheck = new HashSet <string>();
ngResult.Columns.Add("serial_No");
Dictionary <string, int[]> indexOfNg = new Dictionary <string, int[]>();
ngResult.Columns.Add("SDCM", typeof(double));
ngResult.Columns.Add("Vf", typeof(double));
ngResult.Columns.Add("lm", typeof(double));
ngResult.Columns.Add("lm_w", typeof(double));
ngResult.Columns.Add("CRI", typeof(double));
ngResult.Columns.Add("CCT", typeof(double));
ngResult.Columns.Add("Cx", typeof(double));
ngResult.Columns.Add("Cy", typeof(double));
ngResult.Columns.Add("WYNIK");
okResult = ngResult.Clone();
foreach (var testedPcb in testResults)
{
string model = testedPcb.Value.Model;
modelCheck.Add(model);
double sdcm = 0;
ModelSpecification modelSpec = null;
int[] ngIndex = new int[] { 0, 0, 0, 0, 0, 0 };
if (modelSPecification.TryGetValue(model, out modelSpec))
{
bool allOK = true;
sdcm = CalculateSDCM(testedPcb.Value, modelSpec);
if (sdcm > modelSPecification[model].MaxSdcm)
{
allOK = false;
}
if (testedPcb.Value.Vf < modelSPecification[model].Vf_Min || testedPcb.Value.Vf > modelSPecification[model].Vf_Max)
{
allOK = false;
}
if (testedPcb.Value.Lm < modelSPecification[model].Lm_Min || testedPcb.Value.Lm > modelSPecification[model].Lm_Max)
{
allOK = false;
}
if (testedPcb.Value.Cct < modelSPecification[model].CctMin || testedPcb.Value.Cct > modelSPecification[model].CctMax)
{
allOK = false;
}
if (testedPcb.Value.LmW < modelSPecification[model].LmW_Min)
{
allOK = false;
}
if (testedPcb.Value.Cri < modelSPecification[model].CRI_Min)
{
allOK = false;
}
string testResult = "OK";
if (!allOK)
{
testResult = "NG";
ngResult.Rows.Add(testedPcb.Key, sdcm, testedPcb.Value.Vf, testedPcb.Value.Lm, testedPcb.Value.LmW, testedPcb.Value.Cri, testedPcb.Value.Cct, testedPcb.Value.Cx, testedPcb.Value.Cy, testResult);
result = false;
}
else
{
okResult.Rows.Add(testedPcb.Key, sdcm, testedPcb.Value.Vf, testedPcb.Value.Lm, testedPcb.Value.LmW, testedPcb.Value.Cri, testedPcb.Value.Cct, testedPcb.Value.Cx, testedPcb.Value.Cy, testResult);
}
//result.Rows.Add(testedPcb.Key, model, modelSPecification[model].Cx + "x" + modelSPecification[model].Cy + " CCT="+ modelSPecification[model].Cct+"K" , testedPcb.Value.Cx, testedPcb.Value.Cy, modelSPecification[model].MaxSdcm, sdcm);
}
else
{
if (File.Exists(@"Y:\Manufacturing_Center\Integral Quality Management\Dane CofC.xlsx"))
{
MessageBox.Show("Brak modelu: " + model + @" w pliku Excel: Y:\Manufacturing_Center\Integral Quality Management\Dane CofC.xlsx");
}
else
{
MessageBox.Show(@"Brak dostępu do pliku Y:\Manufacturing_Center\Integral Quality Management\Dane CofC.xlsx" + Environment.NewLine + "Sprawdź czy podłączone są dyski sieciowe");
}
break;
}
}
if (modelCheck.Count > 1)
{
string msg = "Uwaga wykryto pomeszane modele!" + Environment.NewLine;
foreach (var mdl in modelCheck)
{
msg += mdl + Environment.NewLine;
currentModel = "Kilka modeli!!";
result = false;
}
MessageBox.Show(msg);
}
else if (modelCheck.Count > 0)
{
currentModel = modelCheck.ToList()[0];
}
//result.DefaultView.Sort = "Wynik_SDCM DESC";
grid.DataSource = ngResult;
infoLabel.Text = "Model: " + currentModel + Environment.NewLine + "OK: " + okResult.Rows.Count + Environment.NewLine + "NG: " + ngResult.Rows.Count;
return(result);
}
public static Dictionary <string, ModelSpecification> loadExcel()
{
Dictionary <string, ModelSpecification> result = new Dictionary <string, ModelSpecification>();
string FilePath = @"Y:\Manufacturing_Center\Integral Quality Management\Dane CofC.xlsx";
if (File.Exists(FilePath))
{
var fs = new FileStream(FilePath, FileMode.Open, FileAccess.Read, FileShare.ReadWrite);
var pck = new OfficeOpenXml.ExcelPackage();
try
{
pck = new OfficeOpenXml.ExcelPackage(fs);
}
catch (Exception e) { MessageBox.Show(e.Message); }
if (pck.Workbook.Worksheets.Count != 0)
{
//foreach (OfficeOpenXml.ExcelWorksheet worksheet in pck.Workbook.Worksheets)
{
OfficeOpenXml.ExcelWorksheet worksheet = pck.Workbook.Worksheets[1];
int modelColIndex = -1;
int modelDescrIndex = -1;
int trNumberIndex = -1;
int trDescrIndex = -1;
int sdcmColIndex = -1;
int cxColIndex = -1;
int cyColIndex = -1;
int cctColIndex = -1;
for (int col = 1; col < 11; col++)
{
if (worksheet.Cells[1, col].Value.ToString().Trim().ToUpper().Replace(" ", "") == "MODELNAME")
{
modelColIndex = col;
}
if (worksheet.Cells[1, col].Value.ToString().Trim().ToUpper().Replace(" ", "") == "ARTICLEDESCRIPTIONTRIDONIC")
{
trDescrIndex = col;
}
if (worksheet.Cells[1, col].Value.ToString().Trim().ToUpper().Replace(" ", "") == "ARTICLEDESCRIPTIONLGIT")
{
modelDescrIndex = col;
}
if (worksheet.Cells[1, col].Value.ToString().Trim().ToUpper().Replace(" ", "") == "CUSTOMERPN")
{
trNumberIndex = col;
}
if (worksheet.Cells[1, col].Value.ToString().Trim().ToUpper().Replace(" ", "") == "MACADAM(DS.)")
{
sdcmColIndex = col;
}
if (worksheet.Cells[1, col].Value.ToString().Trim().ToUpper().Replace(" ", "") == "CIEX")
{
cxColIndex = col;
}
if (worksheet.Cells[1, col].Value.ToString().Trim().ToUpper().Replace(" ", "") == "CIEY")
{
cyColIndex = col;
}
if (worksheet.Cells[1, col].Value.ToString().Trim().ToUpper().Replace(" ", "") == "CCT(K)")
{
cctColIndex = col;
}
}
for (int row = 2; row <= worksheet.Dimension.End.Row; row++)
{
if (worksheet.Cells[row, modelColIndex].Value != null)
{
string model = worksheet.Cells[row, modelColIndex].Value.ToString().Replace(" ", "").Trim();
if (result.ContainsKey(model))
{
continue;
}
string sdcmString = worksheet.Cells[row, sdcmColIndex].Value.ToString().Replace(" ", "").Trim();
string cxString = worksheet.Cells[row, cxColIndex].Value.ToString().Replace(" ", "").Trim().Replace(".", ",");
string cyString = worksheet.Cells[row, cyColIndex].Value.ToString().Replace(" ", "").Trim().Replace(".", ",");
string cct = (worksheet.Cells[row, cctColIndex].Value.ToString().Replace(" ", "").Trim());
double[] ellipseShape = GetEllipseParameters(cct);
string modelDescriptionLGIT = worksheet.Cells[row, modelDescrIndex].Value.ToString();
string modelNumberTridonic = worksheet.Cells[row, trNumberIndex].Value.ToString();
string trDescription = worksheet.Cells[row, trDescrIndex].Value.ToString();
double sdcm = Convert.ToDouble(sdcmString, new CultureInfo("pl-PL"));
double cx = Convert.ToDouble(cxString, new CultureInfo("pl-PL"));
double cy = Convert.ToDouble(cyString, new CultureInfo("pl-PL"));
ModelSpecification newModel = new ModelSpecification(sdcm, cx, cy, ellipseShape[0], ellipseShape[1], ellipseShape[2], ellipseShape[3], ellipseShape[4], ellipseShape[5], 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, modelNumberTridonic, trDescription, model, modelDescriptionLGIT, null, "");
result.Add(model, newModel);
//Debug.WriteLine(model + "-" + cct + "-" + ellipseShape[0]);
}
}
}
}
}
else
{
MessageBox.Show("Brak dostępu do pliku: Dane CoC.xlsx");
}
return(result);
}
public static void CreateExcelReport(Dictionary <string, PcbTesterMeasurements> measurements, ModelSpecification spec, string[] boxes, string user, string saveDefaultPath, DateTime shippingDate)
{
Chart vFChart = new Chart();
vFChart.Name = "vFChart";
SetUpChartParams(vFChart);
Charting.DrawHistogramChart(vFChart, measurements.Select(val => val.Value.Vf).ToList(), spec.Vf_Min, spec.Vf_Max);
Bitmap vfChartBmp = Charting.ConvertChartToBmp(vFChart);
Chart lmChart = new Chart();
lmChart.Name = "lmChart";
SetUpChartParams(lmChart);
Charting.DrawHistogramChart(lmChart, measurements.Select(val => val.Value.Lm).ToList(), spec.Lm_Min, spec.Lm_Max);
Bitmap lmChartBmp = Charting.ConvertChartToBmp(lmChart);
Chart lmWChart = new Chart();
lmWChart.Name = "lmWChart";
SetUpChartParams(lmWChart);
Charting.DrawHistogramChart(lmWChart, measurements.Select(val => val.Value.LmW).ToList(), spec.LmW_Min, 0);
Bitmap lmWChartBmp = Charting.ConvertChartToBmp(lmWChart);
Chart criChart = new Chart();
criChart.Name = "criChart";
SetUpChartParams(criChart);
Charting.DrawHistogramChart(criChart, measurements.Select(val => val.Value.Cri).ToList(), spec.CRI_Min, spec.CRI_Max);
Bitmap criChartBmp = Charting.ConvertChartToBmp(criChart);
Chart ellipseChart = new Chart();
ellipseChart.Name = "ellipseChart";
SetUpChartParams(ellipseChart);
Charting.DrawElipse(ellipseChart, measurements, ElipseCalc.CalculateElipseBorder(measurements, spec));
Bitmap ellipseChartBmp = Charting.ConvertChartToBmp(ellipseChart);
string FilePath = @"cocTemplate.xlsx";
string orderNo = string.Join(",", measurements.Values.Select(m => m.OrderNo).Distinct().ToArray());
if (File.Exists(FilePath))
{
var fs = new FileStream(FilePath, FileMode.Open, FileAccess.Read, FileShare.ReadWrite);
var pck = new OfficeOpenXml.ExcelPackage();
try
{
pck = new OfficeOpenXml.ExcelPackage(fs);
}
catch (Exception e) { MessageBox.Show(e.Message); }
if (pck.Workbook.Worksheets.Count != 0)
{
//foreach (OfficeOpenXml.ExcelWorksheet worksheet in pck.Workbook.Worksheets)
{
OfficeOpenXml.ExcelWorksheet worksheet = pck.Workbook.Worksheets[1];
worksheet.Cells[4, 7].Value = spec.TridonicCustomerNumner;
worksheet.Cells[5, 7].Value = spec.TridonicDescription;
worksheet.Cells[6, 7].Value = spec.LgitName;
worksheet.Cells[7, 7].Value = spec.LgitDescription;
worksheet.Cells[8, 2].Value = shippingDate.ToString("dd.MM.yyyy");
worksheet.Cells[9, 1].Value = "Batch no: " + orderNo;
worksheet.Cells[12, 4].Value = measurements.Count;
worksheet.Cells[13, 4].Value = measurements.Count;
worksheet.Cells[10, 6].Value = "If=" + spec.CurrentForward + "mA";
worksheet.Cells[16, 1].Value = "If=" + spec.CurrentForward + "mA";
worksheet.Cells[31, 7].Value = "@" + spec.CurrentForward + "mA";
worksheet.Cells[48, 1].Value = "Date: " + shippingDate.ToString("dd.MM.yyyy");
worksheet.Cells[48, 6].Value = "Signature: " + user;
OfficeOpenXml.Drawing.ExcelPicture vFchartPic = worksheet.Drawings.AddPicture("vfChartBmp", vfChartBmp);
vFchartPic.SetPosition(615, 3);
vFchartPic.SetSize(50);
OfficeOpenXml.Drawing.ExcelPicture lmChartPic = worksheet.Drawings.AddPicture("lmChartBmp", lmChartBmp);
lmChartPic.SetPosition(355, 3);
lmChartPic.SetSize(50);
OfficeOpenXml.Drawing.ExcelPicture lmWChartPic = worksheet.Drawings.AddPicture("lmWChartBmp", lmWChartBmp);
lmWChartPic.SetPosition(355, 200);
lmWChartPic.SetSize(50);
OfficeOpenXml.Drawing.ExcelPicture criChartPic = worksheet.Drawings.AddPicture("criChartBmp", criChartBmp);
criChartPic.SetPosition(615, 200);
criChartPic.SetSize(50);
OfficeOpenXml.Drawing.ExcelPicture ellipseChartPic = worksheet.Drawings.AddPicture("ellipseChartBmp", ellipseChartBmp);
ellipseChartPic.SetPosition(315, 395);
ellipseChartPic.SetSize(60);
}
}
using (SaveFileDialog saveDialog = new SaveFileDialog())
{
saveDialog.DefaultExt = ".xlsx";
saveDialog.FileName = spec.TridonicCustomerNumner + " CofC " + shippingDate.ToString("dd.MM.yyyy") + " " + spec.LgitName + ".xlsx";
saveDialog.InitialDirectory = saveDefaultPath;
if (saveDialog.ShowDialog() == DialogResult.OK)
{
Stream stream = File.Create(saveDialog.FileName);
pck.SaveAs(stream);
stream.Close();
System.Diagnostics.Process.Start(saveDialog.FileName);
}
}
}
}
public static Dictionary <double, Tuple <double, double> > CalculateElipseBorder(Dictionary <string, PcbTesterMeasurements> pcbMeasurements, ModelSpecification spec)
{
Dictionary <double, Tuple <double, double> > result = new Dictionary <double, Tuple <double, double> >();
double thetaRad = (Math.PI / 180) * spec.Theta;
double a = 1 / Math.Pow(spec.C, 2) * Math.Pow(Math.Sin(thetaRad), 2) + 1 / Math.Pow(spec.D, 2) * Math.Pow(Math.Cos(thetaRad), 2);
double g11 = 1 / Math.Pow(spec.C, 2) * Math.Pow(Math.Cos(thetaRad), 2) + 1 / Math.Pow(spec.D, 2) * Math.Pow(Math.Sin(thetaRad), 2);
double xRange = spec.Variable * spec.C * spec.MaxSdcm * Math.Cos(thetaRad);
for (int i = -50; i < 51; i++)
{
double dx = ((i == 50 || i == -50) ? i * xRange / 102 : i * xRange / 100);
double X = spec.Cx + dx;
double b = 2 * dx * (1 / Math.Pow(spec.C, 2) - 1 / Math.Pow(spec.D, 2)) * Math.Sin(thetaRad) * Math.Cos(thetaRad);
double c = g11 * Math.Pow(dx, 2) - Math.Pow(spec.MaxSdcm, 2);
double dYplus = (-b + Math.Sqrt(Math.Pow(b, 2) - 4 * a * c)) / (2 * a);
double dYminus = (-b - Math.Sqrt(Math.Pow(b, 2) - 4 * a * c)) / (2 * a);
double Yplus = spec.Cy + dYplus;
double Yminus = spec.Cy + dYminus;
result.Add(X, new Tuple <double, double>(Yplus, Yminus));
}
return(result);
}
