1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
|
....
....
maRequeteNG = "SELECT COUNT(CASE WHEN PDData01 like '_;[0-9][0-9][0-9][0-9][0-9]%' THEN SUBSTRING(PDData01,3,5) ELSE SUBSTRING(PDData01,1,5) END) AS NombreErrCode, " +
"FROM dbo.TECommandsAll with (NOLOCK) " +
"INNER JOIN dbo.ProcessData with (NOLOCK) " +
"ON ((CASE WHEN PDData01 LIKE '_;[0-9][0-9][0-9][0-9][0-9]%' THEN SUBSTRING(PDData01,3,5) ELSE SUBSTRING(PDData01,1,5) END)=dbo.TECommandsAll.Code) " +
"INNER JOIN dbo.ProcessPassgeNavi with (NOLOCK) " +
"ON (PPPCBSerialNumber=PDPCBSerialNumber and PPProcessNumber=PDProcessNumber and PDTimeStamp=PPTimeStamp) " +
"WHERE " +
"PPProcessNumber= @localprocess " +
"AND PPPostID = @localhost " +
"AND PPQCStatus = 1 " +
"AND PPPassageNumber = 1 " +
"AND PPPCBSerialNumber like @PCBSerialNumber " +
"AND PPTimeStamp >= @dateToStart and PPTimeStamp <= @dateToStop " +
"GROUP BY (CASE WHEN PDData01 like '_;[0-9][0-9][0-9][0-9][0-9]%' THEN SUBSTRING(PDData01,3,5) ELSE SUBSTRING(PDData01,1,5) END) ";
//"ORDER BY COUNT(CASE WHEN PDData01 like '_;[0-9][0-9][0-9][0-9][0-9]%' THEN SUBSTRING(PDData01,3,5) ELSE SUBSTRING(PDData01,1,4) END) DESC";
MessageBox.Show(maRequeteNG);
//Création de la commande SQL
myCommandNG = new SqlCommand(maRequeteNG, maConnexionSQLsrv);
//Création et décalartion des paramètres
myCommandNG.Parameters.Add(new SqlParameter("@localprocess", SqlDbType.VarChar, 6));
myCommandNG.Parameters.Add(new SqlParameter("@localhost", SqlDbType.VarChar, 6));
myCommandNG.Parameters.Add(new SqlParameter("@PCBSerialNumber", SqlDbType.VarChar, 11));
myCommandNG.Parameters.Add(new SqlParameter("@dateToStart", SqlDbType.VarChar, 30));
myCommandNG.Parameters.Add(new SqlParameter("@dateToStop", SqlDbType.VarChar, 30));
//Attribution des valeurs aux paramètres
myCommandNG.Parameters["@localprocess"].Value = localprocess;
myCommandNG.Parameters["@localhost"].Value = localhost;
myCommandNG.Parameters["@PCBSerialNumber"].Value = PCBSerialNumber;
myCommandNG.Parameters["@dateToStart"].Value = dateToStart;
myCommandNG.Parameters["@dateToStop"].Value = dateToStop;
try
{
myCommandNG.Connection.Open();
myCommandNG.ExecuteNonQuery();
myReaderNG = myCommandNG.ExecuteReader();
dtNG.Load(myReaderNG);
myCommandNG.Connection.Close();
}
catch (SqlException ex)
{
MessageBox.Show("catch");
ErrCode = "";
NombreErrCode = "";
Description = "";
process = "";
host = "";
Console.Write(ex.Message);
maConnexionSQLsrv.Close();
return "Error connection";
}
if (dtNG.Rows.Count > 0)
{
MessageBox.Show("collone");
ErrCode = dtNG.Rows[0].ItemArray[0].ToString();
NombreErrCode = dtNG.Rows[0].ItemArray[1].ToString();
Description = dtNG.Rows[0].ItemArray[2].ToString();
process = localprocess;
host = localhost;
}
else
{
MessageBox.Show("pas de collone");
ErrCode = "0";
NombreErrCode = "0";
Description = "0";
process = localprocess;
host = localhost;
}
maConnexionSQLsrv.Close();
return "No Rows";
} |
