4R9MYDRt

1BEGIN
2
3DROP TEMPORARY TABLE IF EXISTS tmp_all_search;
4create temporary table tmp_all_search(id INT(10) NOT NULL AUTO_INCREMENT, docid varchar(50), date varchar(50), Module varchar(50), Typecheque varchar(50), Cycle varchar(50), Bank varchar(50), Remise varchar(50),Document varchar(50), Montant varchar(50), MICR varchar(50), Auteur varchar(50), BranchName varchar(50),NID VARCHAR(50), FullName varchar(50), Numero_compte varchar(50), PRIMARY KEY (`id`));
5 
6
7START TRANSACTION;
8
9 
10
11set @mysql = "insert into tmp_all_search(docid, date, Module, Typecheque, Cycle, Bank, Remise,Document, Montant, MICR, Auteur, BranchName,NID, FullName, Numero_compte)";
12
13set @mysql = concat(@mysql, " SELECT docid, document.date as date, ");
14
15set @mysql = concat(@mysql, " CASE remise.banc WHEN 'IWD' THEN 'Inward' WHEN 'IWDR' THEN 'Inward Return' WHEN 'OWDR' THEN 'Representment' ELSE 'Outward' END AS Module, ");
16
17set @mysql = concat(@mysql, " type_remise.type_cheque As Typecheque, ");
18
19set @mysql = concat(@mysql, " SUBSTR(workflow.libelle,LENGTH(workflow.libelle)-1) AS Cycle, ");
20
21set @mysql = concat(@mysql, " banque.Name AS Bank, ");
22
23set @mysql = concat(@mysql, " remise.remiseid As Remise, document.docid As Document, ");
24
25set @mysql = concat(@mysql, " FORMAT((document.montant)/100, 2) AS Montant, ");
26
27set @mysql = concat(@mysql, " document.micr AS MICR, ");
28
29set @mysql = concat(@mysql, " (select auteur from histo where histoid = (select max(histoid) from histo where histo.remiseid = document.remiseid)) AS Auteur, ");
30
31set @mysql = concat(@mysql, " IFNULL(branche.Nom,'Head Office') as BranchName, ");
32
33 
34
35IF (P_INF_NID = 1 and P_INF_FULLNAME = 1) THEN
36
37set @mysql = concat(@mysql, " Remise.ID_Card as NID, Remise.Nom as FullName, ");     
38
39ELSE
40
41set @mysql = concat(@mysql, " '' as NID, '' as FullName, ");
42
43END IF;        
44
45 
46
47set @mysql = concat(@mysql, " CASE remise.banc ");
48
49set @mysql = concat(@mysql, " WHEN 'IWD' THEN document.original_account_number ");
50
51set @mysql = concat(@mysql, " WHEN 'IWDR' THEN document.original_account_number ");
52
53set @mysql = concat(@mysql, " WHEN 'OWDR' THEN Remise.Numero_compte ");
54
55set @mysql = concat(@mysql, " ELSE Remise.Numero_compte ");
56
57set @mysql = concat(@mysql, " END AS Numero_compte ");
58
59 
60
61set @mysql = concat(@mysql, " FROM document ");
62
63 
64
65set @mysql = concat(@mysql, " INNER JOIN workflow ON document.workflowid = workflow.workflowid ");
66
67set @mysql = concat(@mysql, " INNER JOIN remise ON remise.remiseid = document.remiseid ");
68
69set @mysql = concat(@mysql, " LEFT OUTER JOIN BRANCHE ON BRANCHE.CODE = REMISE.BRANCHEID ");
70
71set @mysql = concat(@mysql, " INNER JOIN banque ON CASE  remise.banc WHEN 'IWD' THEN banque.SwiftCode=remise.numero_compte WHEN 'IWDR' THEN banque.SwiftCode=remise.numero_compte WHEN 'OWDR' THEN banque.PlachCode=SUBSTR(document.micr,10,2) ELSE banque.PlachCode=SUBSTR(document.micr,10,2)  END ");
72
73set @mysql = concat(@mysql, " INNER JOIN histo_doc ON document.docid = histo_doc.documentid ");
74
75set @mysql = concat(@mysql, " LEFT JOIN type_remise ON type_remise.id = remise.typeremise_id ");
76
77 
78
79set @mysql = concat(@mysql, " WHERE document.docid <> '' ");
80
81 
82
83IF P_DateStart <> '' and P_DateEnd <> '' or P_Module <> '' or P_Cycle <> '' or P_Bank <> '' or P_Type <> '' THEN
84
85set @mysql = concat(@mysql, " AND SUBSTR(document.micr,10,2) <> '44' ");
86
87END IF;
88
89 
90
91IF P_DateStart <> '' and P_DateEnd <> '' THEN
92
93  set @mysql = concat(@mysql, " AND document.date BETWEEN  CAST('",P_DateStart,"' AS DATE) AND CAST('",P_DateEnd,"' AS DATE) ");
94
95END IF;
96
97 
98
99IF TRIM(P_Module) <> '' THEN
100
101   IF P_Module = 'IWD' OR P_Module = 'IWDR' OR P_Module = 'OWDR' THEN
102
103    set @mysql = concat(@mysql, " AND remise.banc='",P_Module,"' ");
104
105   ELSE 
106
107    set @mysql = concat(@mysql, " AND remise.banc <>'IWD' AND remise.banc <>'IWDR' AND remise.banc <>'OWDR' ");
108    
109    end if;
110
111END IF;
112
113 
114
115IF TRIM(P_Cycle) <> '' THEN
116
117   set @mysql = concat(@mysql, " AND document.workflowid='",P_Cycle,"' ");
118
119END IF;
120
121 
122
123IF TRIM(P_Bank) <> '' THEN
124
125IF TRIM(P_Type) <> '' OR TRIM(P_Amount) <> '' OR TRIM(P_Account) <> '' THEN
126
127   set @mysql = concat(@mysql, " AND CASE  remise.banc");
128
129   set @mysql = concat(@mysql, " WHEN 'IWD' THEN remise.numero_compte='",P_Bank,"' ");
130
131   set @mysql = concat(@mysql, " WHEN 'IWDR' THEN remise.numero_compte='",P_Bank,"' ");
132
133   set @mysql = concat(@mysql, " WHEN 'OWDR' THEN SUBSTR(document.micr,10,2)='",LPAD(P_CodePlach, 2, '0'),"' ");
134
135   set @mysql = concat(@mysql, " ELSE SUBSTR(document.micr,10,2)='",LPAD(P_CodePlach, 2, '0'),"' ");
136
137   set @mysql = concat(@mysql, " END ");
138
139ELSE
140
141   set @mysql = concat(@mysql, " AND CASE  remise.banc");
142 
143   set @mysql = concat(@mysql, " WHEN 'IWD' THEN remise.numero_compte='",P_Bank,"' ");
144
145   set @mysql = concat(@mysql, " WHEN 'IWDR' THEN remise.numero_compte='",P_Bank,"' ");
146
147   set @mysql = concat(@mysql, " WHEN 'OWDR' THEN SUBSTR(document.micr,10,2)='",LPAD(P_CodePlach, 2, '0'),"' ");
148
149   set @mysql = concat(@mysql, " ELSE SUBSTR(document.micr,10,2)='",LPAD(P_CodePlach, 2, '0'),"' ");
150
151   set @mysql = concat(@mysql, " END ");
152
153END IF;
154
155END IF;
156
157 
158
159IF TRIM(P_Type) <> '' THEN
160
161  set @mysql = concat(@mysql, " AND type_remise.id='",P_Type,"'");
162
163END IF;
164
165 
166
167IF TRIM(P_Amount) <> '' THEN
168
169set @mysql = concat(@mysql, " AND document.montant='",REPLACE (P_Amount, '.', ''),"' ");
170
171END IF;
172
173 
174
175IF TRIM(P_Account) <> '' THEN
176
177set @mysql = concat(@mysql, " AND remise.numero_compte like '%",P_Account, "%' ");
178
179set @mysql = concat(@mysql, " AND ");
180
181set @mysql = concat(@mysql, " CASE  remise.banc ");
182
183set @mysql = concat(@mysql, " WHEN 'IWD' THEN document.original_account_number like '%",P_Account,"%' ");
184
185set @mysql = concat(@mysql, " WHEN 'IWDR' THEN document.original_account_number like '%",P_Account,"%' ");
186
187set @mysql = concat(@mysql, " WHEN 'OWDR' THEN remise.numero_compte like '%",P_Account,"%' ");
188
189set @mysql = concat(@mysql, " ELSE remise.numero_compte like '%",P_Account,"%' ");
190
191set @mysql = concat(@mysql, " END");
192END IF;
193 
194
195IF TRIM(P_NID) <> '' THEN
196
197set @mysql = concat(@mysql, " AND remise.ID_Card like '%",TRIM(P_NID),"%' ");
198
199END IF;
200
201 
202
203IF TRIM(P_FULLNAME) <> '' THEN
204
205set @mysql = concat(@mysql, " AND remise.Nom like '%",TRIM(P_FULLNAME),"%' ");
206
207END IF;
208
209 
210
211IF TRIM(P_MICR) <> '' THEN
212
213set @mysql = concat(@mysql, " AND document.micr like '%",TRIM(P_MICR),"%' ");
214
215END IF;
216
217 
218
219set @mysql = concat(@mysql," ORDER BY document.date,Module, type_remise.type_cheque, Cycle, Bank, remise.remiseid, document.docid");
220
221 
222
223PREPARE stmt FROM @mysql;
224
225execute stmt;
226
227 
228
229commit;
230
231 
232
233DEALLOCATE PREPARE stmt;
234
235select * from tmp_all_search order by tmp_all_search.date, tmp_all_search.Module, tmp_all_search.Typecheque, tmp_all_search.Cycle, tmp_all_search.Bank, tmp_all_search.Remise,  tmp_all_search.Document, tmp_all_search.MICR, tmp_all_search.Auteur, tmp_all_search.BranchName, tmp_all_search.NID, tmp_all_search.FullName, tmp_all_search.Numero_compte;
236
237
238
239END