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
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
| protected ArrayList ListerFonctionsATraduire(decimal id_guide, decimal id_cycle, OracleConnection con)
{
ArrayList listeLanguesGuide = ListerLanguesGuide(id_guide, con);
ArrayList listeDocsGuide = ListerDocsGuide(id_guide, con);
for (int i = 0; i < listeDocsGuide.Count; i++)
{
ArrayList listeLanguesCycle = ListerLanguesCycle(Convert.ToDecimal(listeDocsGuide[i]), id_cycle, con);
bool retirerDoc;
retirerDoc = ComparerLangues(listeLanguesGuide, listeLanguesCycle);
if (retirerDoc)
{
listeDocsGuide.RemoveAt(i);
}
}
return listeDocsGuide;
}
protected ArrayList ListerLanguesGuide(decimal id_guide, OracleConnection con)
{
OracleCommand cmd;
string req = "select gl.id_language as langue, l.label from guide_lang gl inner join languages l on gl.id_language = l.id_language where id_guide = :id_guide ";
cmd = new OracleCommand(req, con);
cmd.Parameters.Add("id_guide", id_guide);
OracleDataReader oDr = cmd.ExecuteReader();
ArrayList listeLanguesGuide = new ArrayList();
if (oDr != null)
{
while (oDr.Read())
{
listeLanguesGuide.Add(oDr["langue"]);
}
}
cmd.Dispose();
return listeLanguesGuide;
}
protected ArrayList ListerDocsGuide(decimal id_guide, OracleConnection con)
{
OracleCommand cmd;
string req = "SELECT id_document FROM pg_plan WHERE id_guide = :id_guide";
cmd = new OracleCommand(req, con);
cmd.Parameters.Add("id_guide", id_guide);
OracleDataReader oDr = cmd.ExecuteReader();
ArrayList listeDocsGuide = new ArrayList();
if (oDr != null)
{
while (oDr.Read())
{
listeDocsGuide.Add(oDr["id_document"].ToString());
if (listeDocsGuide.Contains(""))
{
listeDocsGuide.Remove("");
}
}
}
cmd.Dispose();
return listeDocsGuide;
}
protected ArrayList ListerLanguesCycle(decimal id_document, decimal id_cycle, OracleConnection con)
{
string req = "SELECT DISTINCT cl.id_language FROM cycle_guide_doc cgd, cycle_lang cl ";
req += "where id_document = :id_document AND cgd.id_cycle = cl.id_cycle AND cl.id_cycle <> :id_cycle ORDER BY cl.id_language";
OracleCommand cmd = new OracleCommand(req, con);
cmd.Parameters.Add("id_document", id_document);
cmd.Parameters.Add("id_cycle", id_cycle);
OracleDataReader oDr = cmd.ExecuteReader();
ArrayList listeLanguesCycle = new ArrayList();
if (oDr != null)
{
while (oDr.Read())
{
listeLanguesCycle.Add(oDr["id_language"]);
}
}
cmd.Dispose();
return listeLanguesCycle;
}
protected bool ComparerLangues(ArrayList LanguesGuide, ArrayList LanguesCycles)
{
for (int i = 0; i < LanguesGuide.Count; i++)
{
if (!LanguesCycles.Contains(LanguesGuide[i]))
{
return false;
}
}
return true;
}
protected DataTable ObtenirFxNonTraduites(int id_cycle, int id_guide)
{
OracleConnection con = dboracle.GetNewConnection();
DataTable oDt = new DataTable();
oDt.Columns.Add("Selected");
oDt.Columns.Add("id_document");
oDt.Columns.Add("label");
oDt.Columns.Add("ident");
oDt.Columns.Add("version");
oDt.Columns.Add("updatedate");
oDt.Columns.Add("status");
oDt.Columns.Add("nom_mere");
oDt.Columns.Add("ident_mere");
string req = "";
req += "SELECT VERSIONS.NUM AS VERSION, VERSIONS.UPDATEDATE, DECODE(CYCLE_GUIDE_DOC.SELECTED, 1, 'True', 0, 'False') AS SELECTED, ";
req += "CYCLE_GUIDE_DOC.ID_DOCUMENT, CYCLE_GUIDE_DOC.ID_GUIDE, CYCLE_GUIDE_DOC.ID_CYCLE, F.LABEL, CONCAT(LPAD(F.NUM, 3, '0'), ";
req += "CONCAT('.', LPAD(F.INDICE, 2, '0'))) AS IDENT, status.label as status, ";
req += "CONCAT(LPAD(fm.NUM, 3, '0'), CONCAT('.', LPAD(fm.INDICE, 2, '0'))) AS ident_mere, fm.label as nom_mere "; // add lgui 9/7/7
req += "FROM CYCLE_GUIDE_DOC INNER JOIN ";
req += "VERSIONS ON CYCLE_GUIDE_DOC.ID_DOCUMENT = VERSIONS.ID_DOCUMENT INNER JOIN ";
req += "DOCUMENTS ON CYCLE_GUIDE_DOC.ID_DOCUMENT = DOCUMENTS.ID_DOCUMENT INNER JOIN ";
req += "FUNCTIONS F ON DOCUMENTS.ID_FUNCTION = F.ID_FUNCTION INNER JOIN ";
req += "status on status.id_status = documents.id_status ";
req += "LEFT OUTER JOIN documents dm ON dm.id_document = DOCUMENTS.id_doc_ori "; // add lgui 9/7/7
req += "LEFT OUTER JOIN functions fm ON fm.id_function = dm.id_function "; // add lgui 9/7/7
req += "WHERE (CYCLE_GUIDE_DOC.ID_GUIDE = :ID_GUIDE) AND (CYCLE_GUIDE_DOC.ID_CYCLE = :id_cycle) ";
req += "AND documents.id_status <> pkg_const.get_status('annuler')";
ArrayList listeDocATrad = ListerFonctionsATraduire(id_guide, id_cycle, con);
try
{
OracleCommand cmd = new OracleCommand(req, con);
cmd.Parameters.Clear();
cmd.Parameters.Add("id_guide", id_guide);
cmd.Parameters.Add("id_cycle", id_cycle);
OracleDataReader oDr = cmd.ExecuteReader();
while (oDr.Read()) // le DataTable contient toutes les fonctions du guide.
{
DataRow ligne = oDt.NewRow();
ligne["Selected"] = oDr["Selected"];
ligne["id_document"] = oDr["id_document"];
ligne["label"] = oDr["label"];
ligne["ident"] = oDr["ident"];
ligne["version"] = oDr["version"];
ligne["updatedate"] = oDr["updatedate"].ToString().Substring(0, 10);
ligne["status"] = oDr["status"];
ligne["nom_mere"] = oDr["nom_mere"];
ligne["ident_mere"] = oDr["ident_mere"];
oDt.Rows.Add(ligne);
}
foreach (DataRow ligne in oDt.Rows)
{
if (!listeDocATrad.Contains(ligne["id_document"]))
{
oDt.Rows.Remove(ligne);
}
}
cmd.Dispose();
}
catch (Exception ex)
{
return oDt;
}
finally
{
con.Close();
con.Dispose();
}
return oDt;
} |
