2 using System.Collections.Generic;
10 namespace sage.ew.serie
28 DataTable ldtDataTable = null;
30 DataTable ldtLtstini =
new DataTable();
31 DataTable ldtLtregul =
new DataTable();
32 DataTable ldtInventaris =
new DataTable();
33 DataTable ldtDatosRevisados =
new DataTable();
35 DataTable ldtLtalbco =
new DataTable();
36 DataTable ldtLtalbve =
new DataTable();
37 DataTable ldtLtalbre =
new DataTable();
38 DataTable ldtLtalbtrS =
new DataTable();
39 DataTable ldtLtalbtrE =
new DataTable();
40 DataTable ldtLtdepco =
new DataTable();
41 DataTable ldtLtdepcoT =
new DataTable();
42 DataTable ldtLtdepve =
new DataTable();
43 DataTable ldtLtdepveT =
new DataTable();
44 DataTable ldtLtcprod =
new DataTable();
45 DataTable ldtLtdprod =
new DataTable();
46 DataTable ldtLtctran =
new DataTable();
47 DataTable ldtLtdtran =
new DataTable();
49 DataTable ldtCostemod =
new DataTable();
54 ldtLtdepve = _StockSerie_Depser(tcWhere,
false);
55 ldtLtdepveT = _StockSerie_Depser_Trasp(tcWhere,
false, tdFecha);
59 ldtLtdepco = _StockSerie_Depcser(tcWhere);
60 ldtLtdepcoT = _StockSerie_Depcser_Trasp(tcWhere, tdFecha);
64 ldtLtstini = _StockSerie_Stockinser(tcWhere);
65 ldtLtregul = _StockSerie_Regulariser(tcWhere);
68 ldtInventaris = _StockSerie_Unir_Inventarios(ldtLtstini, ldtLtregul);
70 ldtLtalbco = _StockSerie_Comser(tcWhere);
71 ldtLtalbre = _StockSerie_Reguser(tcWhere);
72 ldtLtalbve = _StockSerie_Venser(tcWhere);
73 ldtLtalbtrS = _StockSerie_Traspser_Salidas(tcWhere);
74 ldtLtalbtrE = _StockSerie_Traspser_Entradas(tcWhere);
75 ldtLtcprod = _StockSerie_Prodser(tcWhere);
76 ldtLtdprod = _StockSerie_Produser(tcWhere);
77 ldtLtctran = _StockSerie_Transser(tcWhere);
78 ldtLtdtran = _StockSerie_Transer(tcWhere);
79 ldtCostemod = _StockSerie_Montajes(tcWhere);
84 ldtLtstini = _StockSerie_Stockinser(tcWhere);
85 ldtLtregul = _StockSerie_Regulariser(tcWhere);
88 ldtInventaris = _StockSerie_Unir_Inventarios(ldtLtstini, ldtLtregul);
90 ldtLtalbco = _StockSerie_Comser(tcWhere);
91 ldtLtalbre = _StockSerie_Reguser(tcWhere);
92 ldtLtalbve = _StockSerie_Venser(tcWhere);
93 ldtLtalbtrS = _StockSerie_Traspser_Salidas(tcWhere);
94 ldtLtalbtrE = _StockSerie_Traspser_Entradas(tcWhere);
95 ldtLtcprod = _StockSerie_Prodser(tcWhere);
96 ldtLtdprod = _StockSerie_Produser(tcWhere);
97 ldtLtctran = _StockSerie_Transser(tcWhere);
98 ldtLtdtran = _StockSerie_Transer(tcWhere);
99 ldtCostemod = _StockSerie_Montajes(tcWhere);
101 ldtLtdepve = _StockSerie_Depser(tcWhere);
102 ldtLtdepveT = _StockSerie_Depser_Trasp(tcWhere,
true, tdFecha);
103 ldtLtdepco = _StockSerie_Depcser(tcWhere);
104 ldtLtdepcoT = _StockSerie_Depcser_Trasp(tcWhere, tdFecha);
111 _StockSerie_Acumular(ref ldtDataTable, ldtLtalbco);
112 _StockSerie_Acumular(ref ldtDataTable, ldtLtalbre);
113 _StockSerie_Acumular(ref ldtDataTable, ldtLtalbve);
115 _StockSerie_Acumular(ref ldtDataTable, ldtLtalbtrS);
116 _StockSerie_Acumular(ref ldtDataTable, ldtLtalbtrE);
117 _StockSerie_Acumular(ref ldtDataTable, ldtLtcprod);
118 _StockSerie_Acumular(ref ldtDataTable, ldtLtdprod);
119 _StockSerie_Acumular(ref ldtDataTable, ldtLtctran);
120 _StockSerie_Acumular(ref ldtDataTable, ldtLtdtran);
122 _StockSerie_Acumular(ref ldtDataTable, ldtCostemod);
126 _StockSerie_Acumular(ref ldtDataTable, ldtLtdepco);
127 _StockSerie_Acumular(ref ldtDataTable, ldtLtdepcoT);
128 _StockSerie_Acumular(ref ldtDataTable, ldtLtdepve);
129 _StockSerie_Acumular(ref ldtDataTable, ldtLtdepveT);
132 ldtDatosRevisados = _StockSerie_Revisar_Stock(ref ldtDataTable, ldtInventaris);
135 ldtDataTable = _StockSerie_Estructura(tcWhere);
137 _StockSerie_Acumular(ref ldtDataTable, ldtInventaris);
138 _StockSerie_Acumular(ref ldtDataTable, ldtDatosRevisados);
141 _StockSerie_Ordenar(ref ldtDataTable);
147 private DataTable _StockSerie_Estructura(
string tcWhere)
149 String lcSql =
string.Empty;
150 DataTable ldtCompras =
new DataTable();
152 tcWhere +=
" AND 1=2 ";
154 lcSql =
" SELECT d.articulo, d.talla, d.color, s.serie, c.almacen, c.fecha, (CASE WHEN d.unidades>0 THEN 1.00 ELSE -1.00 END) AS unidades ";
155 lcSql +=
" FROM " + DB.SQLDatabase(
"GESTION",
"c_albcom") +
" c ";
156 lcSql +=
" INNER JOIN " + DB.SQLDatabase(
"GESTION",
"d_albcom") +
" d ON c.empresa = d.empresa AND c.proveedor = d.proveedor AND c.numero = d.numero ";
157 lcSql +=
" INNER JOIN " + DB.SQLDatabase(
"GESTION",
"comser") +
" s ON d.empresa = s.empresa AND d.proveedor = s.proveedor AND d.numero = s.albaran AND d.linia = s.linea ";
158 lcSql +=
" INNER JOIN " + DB.SQLDatabase(
"GESTION",
"ARTICULO") +
" a ON d.articulo = a.codigo AND a.stock = 0 ";
159 lcSql +=
" WHERE " + _EmpStock(
"c.Empresa") + tcWhere;
160 lcSql +=
" ORDER BY c.almacen, d.articulo, d.talla, d.color, s.serie ";
162 DB.SQLExec(lcSql, ref ldtCompras);
167 private DataTable _StockSerie_Comser(
string tcWhere)
169 String lcSql =
string.Empty;
170 DataTable ldtCompras =
new DataTable();
172 lcSql =
" SELECT d.articulo, d.talla, d.color, s.serie, c.almacen, c.fecha, (CASE WHEN d.unidades>0 THEN 1.00 ELSE -1.00 END) AS unidades ";
173 lcSql +=
" FROM " + DB.SQLDatabase(
"GESTION",
"c_albcom") +
" c ";
174 lcSql +=
" INNER JOIN " + DB.SQLDatabase(
"GESTION",
"d_albcom") +
" d ON c.empresa = d.empresa AND c.proveedor = d.proveedor AND c.numero = d.numero ";
175 lcSql +=
" INNER JOIN " + DB.SQLDatabase(
"GESTION",
"comser") +
" s ON d.empresa = s.empresa AND d.proveedor = s.proveedor AND d.numero = s.albaran AND d.linia = s.linea ";
176 lcSql +=
" INNER JOIN " + DB.SQLDatabase(
"GESTION",
"ARTICULO") +
" a ON d.articulo = a.codigo AND a.stock = 0 ";
177 lcSql +=
" WHERE " + _EmpStock(
"c.Empresa") + tcWhere;
178 lcSql +=
" ORDER BY c.almacen, d.articulo, d.talla, d.color, s.serie ";
180 DB.SQLExec(lcSql, ref ldtCompras);
185 private DataTable _StockSerie_Venser(
string tcWhere)
187 String lcSql =
string.Empty;
188 DataTable ldtVentas =
new DataTable();
190 lcSql =
" SELECT d.articulo, d.talla, d.color, s.serie, c.almacen, c.fecha, (CASE WHEN d.unidades<0 THEN 1.00 ELSE -1.00 END) AS unidades ";
191 lcSql +=
" FROM " + DB.SQLDatabase(
"GESTION",
"c_albven") +
" c ";
192 lcSql +=
" INNER JOIN " + DB.SQLDatabase(
"GESTION",
"d_albven") +
" d ON c.empresa = d.empresa AND c.numero = d.numero AND c.letra = d.letra ";
193 lcSql +=
" INNER JOIN " + DB.SQLDatabase(
"GESTION",
"venser") +
" s ON d.empresa = s.empresa AND d.numero = s.albaran AND d.letra = s.letra AND d.linia = s.linea ";
194 lcSql +=
" INNER JOIN " + DB.SQLDatabase(
"GESTION",
"ARTICULO") +
" a ON d.articulo = a.codigo AND a.stock = 0 ";
195 lcSql +=
" WHERE " + _EmpStock(
"c.Empresa") + tcWhere;
196 lcSql +=
" ORDER BY c.almacen, d.articulo, d.talla, d.color, s.serie ";
198 DB.SQLExec(lcSql, ref ldtVentas);
203 private DataTable _StockSerie_Reguser(
string tcWhere)
205 String lcSql =
string.Empty;
206 DataTable ldtAlbaregu =
new DataTable();
208 lcSql =
" SELECT d.articulo, d.talla, d.color, s.serie, c.almacen, c.fecha, (CASE WHEN d.unidades<0 THEN 1.00 ELSE -1.00 END) AS unidades ";
209 lcSql +=
" FROM " + DB.SQLDatabase(
"GESTION",
"c_albare") +
" c ";
210 lcSql +=
" INNER JOIN " + DB.SQLDatabase(
"GESTION",
"d_albare") +
" d ON c.empresa = d.empresa AND c.numero = d.numero ";
211 lcSql +=
" INNER JOIN " + DB.SQLDatabase(
"GESTION",
"reguser") +
" s ON d.empresa = s.empresa AND d.numero = s.albaran AND d.linia = s.linea ";
212 lcSql +=
" INNER JOIN " + DB.SQLDatabase(
"GESTION",
"ARTICULO") +
" a ON d.articulo = a.codigo AND a.stock = 0 ";
213 lcSql +=
" WHERE " + _EmpStock(
"c.Empresa") + tcWhere;
214 lcSql +=
" ORDER BY c.almacen, d.articulo, d.talla, d.color, s.serie ";
216 DB.SQLExec(lcSql, ref ldtAlbaregu);
221 private DataTable _StockSerie_Traspser_Entradas(
string tcWhere)
223 String lcSql =
string.Empty;
224 DataTable ldtTraspasEnt =
new DataTable();
227 tcWhere = tcWhere.Replace(
"almacen",
"almdest");
229 lcSql =
" SELECT d.articulo, d.talla, d.color, s.serie, c.almdest as almacen, c.fecha, (CASE WHEN d.unidades>0 THEN 1.00 ELSE -1.00 END) AS unidades ";
230 lcSql +=
" FROM " + DB.SQLDatabase(
"GESTION",
"c_albatr") +
" c ";
231 lcSql +=
" INNER JOIN " + DB.SQLDatabase(
"GESTION",
"d_albatr") +
" d ON c.empresa = d.empresa AND c.numero = d.numero ";
232 lcSql +=
" INNER JOIN " + DB.SQLDatabase(
"GESTION",
"traspser") +
" s ON d.empresa = s.empresa AND d.numero = s.albaran AND d.linia = s.linea ";
233 lcSql +=
" INNER JOIN " + DB.SQLDatabase(
"GESTION",
"ARTICULO") +
" a ON d.articulo = a.codigo AND a.stock = 0 ";
234 lcSql +=
" WHERE " + _EmpStock(
"c.Empresa") + tcWhere;
235 lcSql +=
" ORDER BY c.almdest, d.articulo, d.talla, d.color, s.serie ";
237 DB.SQLExec(lcSql, ref ldtTraspasEnt);
239 return ldtTraspasEnt;
242 private DataTable _StockSerie_Traspser_Salidas(
string tcWhere)
244 String lcSql =
string.Empty;
245 DataTable ldtTraspasSal =
new DataTable();
248 tcWhere = tcWhere.Replace(
"almacen",
"almorig");
250 lcSql =
" SELECT d.articulo, d.talla, d.color, s.serie, c.almorig as almacen, c.fecha, (CASE WHEN d.unidades<0 THEN 1.00 ELSE -1.00 END) AS unidades ";
251 lcSql +=
" FROM " + DB.SQLDatabase(
"GESTION",
"c_albatr") +
" c ";
252 lcSql +=
" INNER JOIN " + DB.SQLDatabase(
"GESTION",
"d_albatr") +
" d ON c.empresa = d.empresa AND c.numero = d.numero ";
253 lcSql +=
" INNER JOIN " + DB.SQLDatabase(
"GESTION",
"traspser") +
" s ON d.empresa = s.empresa AND d.numero = s.albaran AND d.linia = s.linea ";
254 lcSql +=
" INNER JOIN " + DB.SQLDatabase(
"GESTION",
"ARTICULO") +
" a ON d.articulo = a.codigo AND a.stock = 0 ";
255 lcSql +=
" WHERE " + _EmpStock(
"c.Empresa") + tcWhere;
256 lcSql +=
" ORDER BY c.almorig, d.articulo, d.talla, d.color, s.serie ";
258 DB.SQLExec(lcSql, ref ldtTraspasSal);
260 return ldtTraspasSal;
263 private DataTable _StockSerie_Depcser(
string tcWhere)
265 String lcSql =
string.Empty;
266 DataTable ldtDepoCom1 =
new DataTable();
268 lcSql =
" SELECT d.articulo, d.talla, d.color, s.serie, c.almacen, c.fecha, (CASE WHEN d.unidades>0 THEN 1.00 ELSE -1.00 END) AS unidades ";
269 lcSql +=
" FROM " + DB.SQLDatabase(
"GESTION",
"c_depcom") +
" c ";
270 lcSql +=
" INNER JOIN " + DB.SQLDatabase(
"GESTION",
"d_depcom") +
" d ON c.empresa = d.empresa AND c.numero = d.numero ";
271 lcSql +=
" INNER JOIN " + DB.SQLDatabase(
"GESTION",
"depcser") +
" s ON d.empresa = s.empresa AND d.numero = s.albaran AND d.linia = s.linea ";
272 lcSql +=
" INNER JOIN " + DB.SQLDatabase(
"GESTION",
"ARTICULO") +
" a ON d.articulo = a.codigo AND a.stock = 0 ";
273 lcSql +=
" WHERE " + _EmpStock(
"c.Empresa") + tcWhere;
274 lcSql +=
" AND s.traspaso = 0 ";
275 lcSql +=
" ORDER BY c.almacen, d.articulo, d.talla, d.color, s.serie ";
277 DB.SQLExec(lcSql, ref ldtDepoCom1);
282 private DataTable _StockSerie_Depcser_Trasp(
string tcWhere, DateTime? tdFecha = null)
284 String lcSql =
string.Empty;
285 DataTable ldtDepoComTrasp =
new DataTable();
287 lcSql =
" SELECT d.articulo, d.talla, d.color, s.serie, c.almacen, c.fecha, (CASE WHEN b.doc_unid>0 THEN 1.00 ELSE -1.00 END) AS unidades ";
288 lcSql +=
" FROM " + DB.SQLDatabase(
"GESTION",
"c_depcom") +
" c ";
289 lcSql +=
" INNER JOIN " + DB.SQLDatabase(
"GESTION",
"d_depcom") +
" d ON c.empresa = d.empresa AND c.numero = d.numero ";
290 lcSql +=
" INNER JOIN " + DB.SQLDatabase(
"GESTION",
"d_albcom") +
" b ON d.empresa = b.empresa AND d.numero = b.doc_num AND d.proveedor = b.proveedor AND d.linia = b.doc_lin AND b.doc = 3 ";
291 lcSql +=
" INNER JOIN " + DB.SQLDatabase(
"GESTION",
"depcser") +
" s ON d.empresa = s.empresa AND d.numero = s.albaran AND d.linia = s.linea ";
292 lcSql +=
" INNER JOIN " + DB.SQLDatabase(
"GESTION",
"ARTICULO") +
" a ON d.articulo = a.codigo AND a.stock = 0 ";
293 lcSql +=
" WHERE " + _EmpStock(
"c.Empresa") + tcWhere + (tdFecha != null ?
" AND b.fecha > " + DB.SQLString(tdFecha) :
"") +
" ";
294 lcSql +=
" AND s.traspaso = 1 ";
295 lcSql +=
" ORDER BY c.almacen, d.articulo, d.talla, d.color, s.serie ";
297 DB.SQLExec(lcSql, ref ldtDepoComTrasp);
299 return ldtDepoComTrasp;
302 private DataTable _StockSerie_Depser(
string tcWhere,
bool tlNegativo =
true)
304 String lcSql =
string.Empty;
305 DataTable ldtDepoVen1 =
new DataTable();
307 string lcNegativo =
string.Empty;
311 lcNegativo =
" (CASE WHEN d.unidades<0 THEN 1.00 ELSE -1.00 END) AS unidades ";
315 lcNegativo =
" (CASE WHEN d.unidades>0 THEN 1.00 ELSE -1.00 END) AS unidades ";
318 lcSql =
" SELECT d.articulo, d.talla, d.color, s.serie, c.almacen, c.fecha, " + lcNegativo;
319 lcSql +=
" FROM " + DB.SQLDatabase(
"GESTION",
"c_albdep") +
" c ";
320 lcSql +=
" INNER JOIN " + DB.SQLDatabase(
"GESTION",
"d_albdep") +
" d ON c.empresa = d.empresa AND c.numero = d.numero AND c.letra = d.letra ";
321 lcSql +=
" INNER JOIN " + DB.SQLDatabase(
"GESTION",
"depser") +
" s ON d.empresa = s.empresa AND d.numero = s.deposito AND c.letra = d.letra AND d.linia = s.linea ";
322 lcSql +=
" INNER JOIN " + DB.SQLDatabase(
"GESTION",
"ARTICULO") +
" a ON d.articulo = a.codigo AND a.stock = 0 ";
323 lcSql +=
" WHERE " + _EmpStock(
"c.Empresa") + tcWhere;
324 lcSql +=
" AND s.traspaso = 0 ";
325 lcSql +=
" ORDER BY c.almacen, d.articulo, d.talla, d.color, s.serie ";
327 DB.SQLExec(lcSql, ref ldtDepoVen1);
332 private DataTable _StockSerie_Depser_Trasp(
string tcWhere,
bool tlNegativo =
true, DateTime? tdFecha = null)
334 String lcSql =
string.Empty;
335 DataTable ldtDepoVenTrasp =
new DataTable();
337 string lcNegativo =
string.Empty;
341 lcNegativo =
" (CASE WHEN b.doc_unid<0 THEN 1.00 ELSE -1.00 END) AS unidades ";
345 lcNegativo =
" (CASE WHEN b.doc_unid>0 THEN 1.00 ELSE -1.00 END) AS unidades ";
348 lcSql =
" SELECT d.articulo, d.talla, d.color, s.serie, c.almacen, c.fecha, " + lcNegativo;
349 lcSql +=
" FROM " + DB.SQLDatabase(
"GESTION",
"c_albdep") +
" c ";
350 lcSql +=
" INNER JOIN " + DB.SQLDatabase(
"GESTION",
"d_albdep") +
" d ON c.empresa = d.empresa AND c.numero = d.numero AND c.letra = d.letra ";
351 lcSql +=
" INNER JOIN " + DB.SQLDatabase(
"GESTION",
"d_albven") +
" b ON d.empresa = b.empresa AND d.numero+d.letra = b.doc_num AND d.linia = b.doc_lin AND b.doc = 2 ";
352 lcSql +=
" INNER JOIN " + DB.SQLDatabase(
"GESTION",
"depser") +
" s ON d.empresa = s.empresa AND d.numero = s.deposito AND c.letra = d.letra AND d.linia = s.linea ";
353 lcSql +=
" INNER JOIN " + DB.SQLDatabase(
"GESTION",
"ARTICULO") +
" a ON d.articulo = a.codigo AND a.stock = 0 ";
354 lcSql +=
" WHERE " + _EmpStock(
"c.Empresa") + tcWhere + (tdFecha != null ?
" AND b.fecha > " + DB.SQLString(tdFecha) :
"") +
" ";
355 lcSql +=
" AND s.traspaso = 1 ";
356 lcSql +=
" ORDER BY c.almacen, d.articulo, d.talla, d.color, s.serie ";
358 DB.SQLExec(lcSql, ref ldtDepoVenTrasp);
360 return ldtDepoVenTrasp;
363 private DataTable _StockSerie_Prodser(
string tcWhere)
365 String lcSql =
string.Empty;
366 DataTable ldtCprod =
new DataTable();
368 lcSql =
" SELECT d.articulo, d.talla, d.color, s.serie, c.almacen, c.fecha, (CASE WHEN d.entrada>0 THEN 1.00 ELSE -1.00 END) AS unidades ";
369 lcSql +=
" FROM " + DB.SQLDatabase(
"GESTION",
"c_prod") +
" c ";
370 lcSql +=
" INNER JOIN " + DB.SQLDatabase(
"GESTION",
"c_prod") +
" d ON c.empresa = d.empresa AND c.numero = d.numero ";
371 lcSql +=
" INNER JOIN " + DB.SQLDatabase(
"GESTION",
"produserc") +
" s ON d.empresa = s.empresa AND d.numero = s.albaran ";
372 lcSql +=
" INNER JOIN " + DB.SQLDatabase(
"GESTION",
"ARTICULO") +
" a ON d.articulo = a.codigo AND a.stock = 0 ";
373 lcSql +=
" WHERE " + _EmpStock(
"c.Empresa") + tcWhere;
374 lcSql +=
" ORDER BY c.almacen, d.articulo, d.talla, d.color, s.serie ";
376 DB.SQLExec(lcSql, ref ldtCprod);
381 private DataTable _StockSerie_Produser(
string tcWhere)
383 String lcSql =
string.Empty;
384 DataTable ldtDprod =
new DataTable();
386 lcSql =
" SELECT d.articulo, d.talla, d.color, s.serie, c.almacen, c.fecha, (CASE WHEN d.salida<0 THEN 1.00 ELSE -1.00 END) AS unidades ";
387 lcSql +=
" FROM " + DB.SQLDatabase(
"GESTION",
"c_prod") +
" c ";
388 lcSql +=
" INNER JOIN " + DB.SQLDatabase(
"GESTION",
"d_prod") +
" d ON c.empresa = d.empresa AND c.numero = d.numero ";
389 lcSql +=
" INNER JOIN " + DB.SQLDatabase(
"GESTION",
"produser") +
" s ON d.empresa = s.empresa AND d.numero = s.albaran AND d.linea = s.linea ";
390 lcSql +=
" INNER JOIN " + DB.SQLDatabase(
"GESTION",
"ARTICULO") +
" a ON d.articulo = a.codigo AND a.stock = 0 ";
391 lcSql +=
" WHERE " + _EmpStock(
"c.Empresa") + tcWhere;
392 lcSql +=
" ORDER BY c.almacen, d.articulo, d.talla, d.color, s.serie ";
394 DB.SQLExec(lcSql, ref ldtDprod);
399 private DataTable _StockSerie_Transser(
string tcWhere)
401 String lcSql =
string.Empty;
402 DataTable ldtCtran =
new DataTable();
404 lcSql =
" SELECT d.articulo, d.talla, d.color, s.serie, c.almacen, c.fecha, (CASE WHEN d.salida<0 THEN 1.00 ELSE -1.00 END) AS unidades ";
405 lcSql +=
" FROM " + DB.SQLDatabase(
"GESTION",
"c_trans") +
" c ";
406 lcSql +=
" INNER JOIN " + DB.SQLDatabase(
"GESTION",
"c_trans") +
" d ON c.empresa = d.empresa AND c.numero = d.numero ";
407 lcSql +=
" INNER JOIN " + DB.SQLDatabase(
"GESTION",
"transser") +
" s ON d.empresa = s.empresa AND d.numero = s.albaran ";
408 lcSql +=
" INNER JOIN " + DB.SQLDatabase(
"GESTION",
"ARTICULO") +
" a ON d.articulo = a.codigo AND a.stock = 0 ";
409 lcSql +=
" WHERE " + _EmpStock(
"c.Empresa") + tcWhere;
410 lcSql +=
" ORDER BY c.almacen, d.articulo, d.talla, d.color, s.serie ";
412 DB.SQLExec(lcSql, ref ldtCtran);
418 private DataTable _StockSerie_Transer(
string tcWhere)
420 String lcSql =
string.Empty;
421 DataTable ldtDtran =
new DataTable();
423 lcSql =
" SELECT d.articulo, d.talla, d.color, s.serie, c.almacen, c.fecha, (CASE WHEN d.entrada>0 THEN 1.00 ELSE -1.00 END) AS unidades ";
424 lcSql +=
" FROM " + DB.SQLDatabase(
"GESTION",
"c_trans") +
" c ";
425 lcSql +=
" INNER JOIN " + DB.SQLDatabase(
"GESTION",
"d_trans") +
" d ON c.empresa = d.empresa AND c.numero = d.numero ";
426 lcSql +=
" INNER JOIN " + DB.SQLDatabase(
"GESTION",
"transer") +
" s ON d.empresa = s.empresa AND d.numero = s.albaran AND d.linea = s.linea ";
427 lcSql +=
" INNER JOIN " + DB.SQLDatabase(
"GESTION",
"ARTICULO") +
" a ON d.articulo = a.codigo AND a.stock = 0 ";
428 lcSql +=
" WHERE " + _EmpStock(
"c.Empresa") + tcWhere;
429 lcSql +=
" ORDER BY c.almacen, d.articulo, d.talla, d.color, s.serie ";
431 DB.SQLExec(lcSql, ref ldtDtran);
437 private DataTable _StockSerie_Stockinser(
string tcWhere)
439 string lcSql =
string.Empty;
440 DataTable ldtInicial =
new DataTable();
455 lcSql =
" SELECT d.articulo, d.talla, d.color, s.serie, c.almacen, c.fecha, (CASE WHEN s.baja = 0 THEN 1.00 ELSE -1.00 END) AS unidades " + Environment.NewLine;
456 lcSql +=
" FROM " + DB.SQLDatabase(
"GESTION",
"stockini") +
" c " + Environment.NewLine;
457 lcSql +=
" INNER JOIN " + DB.SQLDatabase(
"GESTION",
"stockini") +
" d " +
458 "ON c.empresa = d.empresa AND c.almacen = d.almacen AND c.fecha = d.fecha AND c.linia = d.linia AND c.articulo = d.articulo " + Environment.NewLine;
459 lcSql +=
" INNER JOIN " + DB.SQLDatabase(
"GESTION",
"inicialser") +
" s " +
460 "ON d.almacen = s.almacen AND d.articulo = s.articulo and s.deposito = 0 " + Environment.NewLine;
462 lcSql +=
" INNER JOIN " + DB.SQLDatabase(
"GESTION",
"ARTICULO") +
" a " +
463 "ON d.articulo = a.codigo AND a.stock = 0 " + Environment.NewLine;
464 lcSql +=
" WHERE " + _EmpStock(
"c.Empresa") + tcWhere;
465 lcSql +=
" AND c.fecha >= " + DB.SQLString(Convert.ToDateTime(EW_GLOBAL._GetVariable(
"wd_periodoini")));
466 lcSql +=
" AND c.fecha <= " + DB.SQLString(Convert.ToDateTime(EW_GLOBAL._GetVariable(
"wd_periodofin"))) + Environment.NewLine;
467 lcSql +=
" ORDER BY c.almacen, d.articulo, d.talla, d.color, s.serie ";
470 DB.SQLExec(lcSql, ref ldtInicial);
475 private DataTable _StockSerie_Regulariser(
string tcWhere)
477 string lcSql =
string.Empty;
478 DataTable ldtRegul1 =
new DataTable();
479 DataTable ldtRegul2 =
new DataTable();
481 lcSql =
" SELECT d.articulo, d.talla, d.color, s.serie, c.almacen, c.fecha, (CASE WHEN d.unidades>0 THEN 1.00 ELSE -1.00 END) AS unidades ";
482 lcSql +=
" FROM " + DB.SQLDatabase(
"GESTION",
"regulari") +
" c ";
483 lcSql +=
" INNER JOIN " + DB.SQLDatabase(
"GESTION",
"regulari") +
" d ON c.empresa = d.empresa AND c.almacen = d.almacen AND c.fecha = d.fecha AND c.linia = d.linia AND c.documento = d.documento ";
484 lcSql +=
" INNER JOIN " + DB.SQLDatabase(
"GESTION",
"regulariser") +
" s ON d.empresa = s.empresa AND d.almacen = s.almacen AND d.fecha = s.fecha AND d.linia = s.linea AND d.documento = s.documento and s.baja = 0 ";
485 lcSql +=
" INNER JOIN " + DB.SQLDatabase(
"GESTION",
"ARTICULO") +
" a ON d.articulo = a.codigo AND a.stock = 0 ";
486 lcSql +=
" WHERE " + _EmpStock(
"c.Empresa") + tcWhere;
487 lcSql +=
" AND c.fecha >= " + DB.SQLString(Convert.ToDateTime(EW_GLOBAL._GetVariable(
"wd_periodoini")));
488 lcSql +=
" AND c.fecha <= " + DB.SQLString(Convert.ToDateTime(EW_GLOBAL._GetVariable(
"wd_periodofin")));
489 lcSql +=
" UNION ALL ";
490 lcSql +=
" SELECT d.articulo, d.talla, d.color, s.serie, c.almacen, c.fecha, 0.00 AS unidades ";
491 lcSql +=
" FROM " + DB.SQLDatabase(
"GESTION",
"regulari") +
" c ";
492 lcSql +=
" INNER JOIN " + DB.SQLDatabase(
"GESTION",
"regulari") +
" d ON c.empresa = d.empresa AND c.almacen = d.almacen AND c.fecha = d.fecha AND c.linia = d.linia AND c.documento = d.documento ";
493 lcSql +=
" INNER JOIN " + DB.SQLDatabase(
"GESTION",
"regulariser") +
" s ON d.empresa = s.empresa AND d.almacen = s.almacen AND d.fecha = s.fecha AND d.linia = s.linea AND d.documento = s.documento and s.baja = 1 ";
494 lcSql +=
" INNER JOIN " + DB.SQLDatabase(
"GESTION",
"ARTICULO") +
" a ON d.articulo = a.codigo AND a.stock = 0 ";
495 lcSql +=
" WHERE " + _EmpStock(
"c.Empresa") + tcWhere;
496 lcSql +=
" AND c.fecha >= " + DB.SQLString(Convert.ToDateTime(EW_GLOBAL._GetVariable(
"wd_periodoini")));
497 lcSql +=
" AND c.fecha <= " + DB.SQLString(Convert.ToDateTime(EW_GLOBAL._GetVariable(
"wd_periodofin")));
499 DB.SQLExec(lcSql, ref ldtRegul1);
501 lcSql =
" SELECT MAX(d.articulo) as articulo, MAX(d.talla) as talla, MAX(d.color) as color, MAX(s.serie) as serie, c.almacen, MAX(c.fecha) as fecha ";
502 lcSql +=
" FROM " + DB.SQLDatabase(
"GESTION",
"regulari") +
" c ";
503 lcSql +=
" INNER JOIN " + DB.SQLDatabase(
"GESTION",
"regulari") +
" d ON c.empresa = d.empresa AND c.almacen = d.almacen AND c.fecha = d.fecha AND c.linia = d.linia AND c.documento = d.documento ";
504 lcSql +=
" INNER JOIN " + DB.SQLDatabase(
"GESTION",
"regulariser") +
" s ON d.empresa = s.empresa AND d.almacen = s.almacen AND d.fecha = s.fecha AND d.linia = s.linea AND d.documento = s.documento ";
505 lcSql +=
" INNER JOIN " + DB.SQLDatabase(
"GESTION",
"ARTICULO") +
" a ON d.articulo = a.codigo AND a.stock = 0 ";
506 lcSql +=
" WHERE " + _EmpStock(
"c.Empresa") + tcWhere;
507 lcSql +=
" GROUP BY c.almacen, d.articulo, d.talla, d.color, s.serie ";
509 DB.SQLExec(lcSql, ref ldtRegul2);
512 var ldtRegul = (from a in ldtRegul1.AsEnumerable()
513 join b in ldtRegul2.AsEnumerable()
516 almacen = a.Field<
string>(
"almacen"),
517 articulo = a.Field<
string>(
"articulo"),
518 talla = a.Field<
string>(
"talla"),
519 color = a.Field<
string>(
"color"),
520 serie = a.Field<
string>(
"serie"),
521 fecha = a.Field<DateTime>(
"fecha")
525 almacen = b.Field<
string>(
"almacen"),
526 articulo = b.Field<
string>(
"articulo"),
527 talla = b.Field<
string>(
"talla"),
528 color = b.Field<
string>(
"color"),
529 serie = b.Field<
string>(
"serie"),
530 fecha = b.Field<DateTime>(
"fecha")
534 almacen = a.Field<
string>(
"almacen"),
535 articulo = a.Field<
string>(
"articulo"),
536 talla = a.Field<
string>(
"talla"),
537 color = a.Field<
string>(
"color"),
538 serie = a.Field<
string>(
"serie")
548 fecha = g.Max(x => x.Field<DateTime>(
"fecha")),
549 unidades = g.Sum(x => x.Field<decimal>(
"unidades"))
550 }).__CopyToDataTable();
555 private DataTable _StockSerie_Montajes(
string tcWhere)
557 String lcSql =
string.Empty;
558 DataTable ldtMontajes =
new DataTable();
561 tcWhere = tcWhere.Replace(
"fecha",
"montaje");
563 lcSql =
" SELECT c.articulo, c.talla, c.color, c.codigo as serie, c.almacen, c.montaje as fecha, 1.00 AS unidades ";
564 lcSql +=
" From " + DB.SQLDatabase(
"COMUNES",
"modelo") +
" c ";
565 lcSql +=
" INNER JOIN " + DB.SQLDatabase(
"COMUNES",
"modelo") +
" d ON c.codigo = d.codigo ";
566 lcSql +=
" INNER JOIN " + DB.SQLDatabase(
"COMUNES",
"costemod") +
" S ON S.modelo = C.codigo ";
567 lcSql +=
" INNER JOIN " + DB.SQLDatabase(
"GESTION",
"ARTICULO") +
" A On C.ARTICULO = A.CODIGO AND a.stock = 0 ";
568 lcSql +=
" WHERE " + EW_GLOBAL._Consolida(
"c.stockemp") + tcWhere;
569 lcSql +=
" AND c.montaje >= " + DB.SQLString(Convert.ToDateTime(EW_GLOBAL._GetVariable(
"wd_periodoini")));
570 lcSql +=
" AND c.montaje <= " + DB.SQLString(Convert.ToDateTime(EW_GLOBAL._GetVariable(
"wd_periodofin")));
571 lcSql +=
" ORDER BY c.almacen, c.articulo, c.talla, c.color, c.codigo ";
573 DB.SQLExec(lcSql, ref ldtMontajes);
578 private void _StockSerie_Acumular(ref DataTable tdtDataTable1, DataTable tdtDataTable2)
580 bool llOrdenar =
false;
583 if (tdtDataTable2 != null && tdtDataTable2.Rows.Count > 0)
585 if (tdtDataTable1 == null)
587 tdtDataTable1 = tdtDataTable2;
592 if (tdtDataTable2.Rows.Count > 0)
594 DBfunctions.SQLUnionDatatable(ref tdtDataTable1, tdtDataTable2);
602 tdtDataTable1.DefaultView.Sort =
"almacen ASC, articulo ASC, serie ASC ";
603 tdtDataTable1 = tdtDataTable1.DefaultView.ToTable();
608 private void _StockSerie_Ordenar(ref DataTable tdtDataTable1)
612 if (tdtDataTable1 == null)
615 var ldtDades = (from item in tdtDataTable1.AsEnumerable()
618 almacen = item.Field<
string>(
"almacen"),
619 articulo = item.Field<
string>(
"articulo"),
620 talla = item.Field<
string>(
"talla"),
621 color = item.Field<
string>(
"color"),
622 serie = item.Field<
string>(
"serie")
625 where g.Sum(x => x.Field<decimal>(
"unidades")) > 0
633 fecha = g.Max(x => x.Field<DateTime>(
"fecha")),
634 unidades = g.Sum(x => x.Field<decimal>(
"unidades"))
635 }).__CopyToDataTable();
637 tdtDataTable1 = ldtDades;
641 tdtDataTable1.DefaultView.Sort =
"almacen ASC, articulo ASC, serie ASC ";
642 tdtDataTable1 = tdtDataTable1.DefaultView.ToTable();
645 private DataTable _StockSerie_Revisar_Stock(ref DataTable tdtDataTable1, DataTable tdtInventaris)
648 if (tdtDataTable1 == null)
649 return tdtDataTable1;
651 var ldtDatosRevisados = (from a in tdtDataTable1.AsEnumerable()
652 join b in tdtInventaris.AsEnumerable()
655 almacen = a.Field<
string>(
"almacen"),
656 articulo = a.Field<
string>(
"articulo"),
657 talla = a.Field<
string>(
"talla"),
658 color = a.Field<
string>(
"color"),
659 serie = a.Field<
string>(
"serie")
663 almacen = b.Field<
string>(
"almacen"),
664 articulo = b.Field<
string>(
"articulo"),
665 talla = b.Field<
string>(
"talla"),
666 color = b.Field<
string>(
"color"),
667 serie = b.Field<
string>(
"serie")
670 from c in leftJoin.DefaultIfEmpty()
671 where (c != null ? a.Field<DateTime>(
"fecha") > c.Field<DateTime>(
"fecha") :
true)
674 articulo = a.Field<
string>(
"articulo"),
675 talla = a.Field<
string>(
"talla"),
676 color = a.Field<
string>(
"color"),
677 serie = a.Field<
string>(
"serie"),
678 almacen = a.Field<
string>(
"almacen"),
679 fecha = a.Field<DateTime>(
"fecha"),
680 unidades = a.Field<decimal>(
"unidades")
681 }).__CopyToDataTable();
684 return ldtDatosRevisados;
687 private DataTable _StockSerie_Unir_Inventarios(DataTable tdtInicial, DataTable tdtRegulari)
689 if (tdtInicial != null && tdtInicial.Rows.Count > 0 && tdtRegulari != null && tdtRegulari.Rows.Count == 0)
690 DBfunctions.SQLUnionDatatable(ref tdtRegulari, tdtInicial);
693 var ldtInventaris = (from a in tdtRegulari.AsEnumerable()
694 join b in tdtInicial.AsEnumerable()
697 almacen = a.Field<
string>(
"almacen"),
698 articulo = a.Field<
string>(
"articulo"),
699 talla = a.Field<
string>(
"talla"),
700 color = a.Field<
string>(
"color"),
701 serie = a.Field<
string>(
"serie")
705 almacen = b.Field<
string>(
"almacen"),
706 articulo = b.Field<
string>(
"articulo"),
707 talla = b.Field<
string>(
"talla"),
708 color = b.Field<
string>(
"color"),
709 serie = b.Field<
string>(
"serie")
712 from c in leftJoin.DefaultIfEmpty()
713 where (c != null ? a.Field<DateTime>(
"fecha") >= c.Field<DateTime>(
"fecha") :
true)
716 articulo = a.Field<
string>(
"articulo"),
717 talla = a.Field<
string>(
"talla"),
718 color = a.Field<
string>(
"color"),
719 serie = a.Field<
string>(
"serie"),
720 almacen = a.Field<
string>(
"almacen"),
721 fecha = a.Field<DateTime>(
"fecha"),
722 unidades = a.Field<decimal>(
"unidades")
723 }).__CopyToDataTable();
725 return ldtInventaris;
738 string lcSql =
string.Empty;
739 DataTable ldtCompras =
new DataTable();
741 if (
string.IsNullOrWhiteSpace(tcEjercicio))
742 tcEjercicio = Convert.ToString(EW_GLOBAL._GetVariable(
"wc_any"));
744 string lcOrigenEnDeposito =
string.Empty;
745 if (incluirOrigenDeposito)
746 lcOrigenEnDeposito =
", (CASE WHEN d.doc = 3 THEN Cast(1 as bit) ELSE Cast(0 as bit) END) AS origendeposito ";
748 lcSql =
" SELECT c.almacen, d.articulo, d.talla, d.color, s.serie, (CASE WHEN d.unidades>0 THEN 1.00 ELSE -1.00 END) AS unidades " + lcOrigenEnDeposito;
749 lcSql +=
" FROM " + DB.SQLDatabase(
"",
"C_ALBCOM") +
" c ";
750 lcSql +=
" INNER JOIN " + DB.SQLDatabase(
"",
"D_ALBCOM") +
" d ON c.empresa = d.empresa AND c.numero = d.numero AND c.proveedor = d.proveedor ";
751 lcSql +=
" INNER JOIN " + DB.SQLDatabase(
"",
"COMSER") +
" s ON d.empresa = s.empresa AND d.numero = s.albaran AND d.proveedor = s.proveedor AND d.linia = s.linea ";
752 lcSql +=
" INNER JOIN " + DB.SQLDatabase(
"",
"ARTICULO") +
" a ON d.articulo = a.codigo AND a.stock = 0 ";
753 lcSql +=
" WHERE " + _EmpStock(
"c.Empresa") + tcWhere;
755 DB.SQLExecEjer(lcSql, ref ldtCompras,
new string[] { tcEjercicio });
768 public DataTable
_InventaSeries_Venser(
string tcWhere,
string tcEjercicio,
bool tlNegativo =
true,
bool incluirOrigenDeposito =
false)
770 string lcSql =
string.Empty;
771 DataTable ldtVentas =
new DataTable();
773 if (
string.IsNullOrWhiteSpace(tcEjercicio))
774 tcEjercicio = Convert.ToString(EW_GLOBAL._GetVariable(
"wc_any"));
776 string lcNegativo =
string.Empty;
777 string lcOrigenEnDeposito =
string.Empty;
781 lcNegativo =
" , (CASE WHEN d.unidades<0 THEN 1.00 ELSE -1.00 END) AS unidades ";
785 lcNegativo =
" , (CASE WHEN d.unidades>0 THEN 1.00 ELSE -1.00 END) AS unidades ";
788 if (incluirOrigenDeposito)
789 lcOrigenEnDeposito =
", (CASE WHEN d.doc = 2 THEN Cast(1 as bit) ELSE Cast(0 as bit) END) AS origendeposito ";
791 lcSql =
" SELECT c.almacen, d.articulo, d.talla, d.color, s.serie" + lcOrigenEnDeposito + lcNegativo;
792 lcSql +=
" FROM " + DB.SQLDatabase(
"",
"C_ALBVEN") +
" c ";
793 lcSql +=
" INNER JOIN " + DB.SQLDatabase(
"",
"D_ALBVEN") +
" d ON c.empresa = d.empresa AND c.numero = d.numero AND c.letra = d.letra ";
794 lcSql +=
" INNER JOIN " + DB.SQLDatabase(
"",
"VENSER") +
" s ON d.empresa = s.empresa AND d.numero = s.albaran AND c.letra = s.letra AND d.linia = s.linea ";
795 lcSql +=
" INNER JOIN " + DB.SQLDatabase(
"",
"ARTICULO") +
" a ON d.articulo = a.codigo AND a.stock = 0 ";
796 lcSql +=
" WHERE " + _EmpStock(
"c.Empresa") + tcWhere;
798 DB.SQLExecEjer(lcSql, ref ldtVentas,
new string[] { tcEjercicio } );
810 String lcSql =
string.Empty;
811 string lcFiltro =
string.Empty;
812 DataTable ldtMontajes =
new DataTable();
815 lcFiltro = tcWhere.Replace(
"c.fecha",
"e.montaje").Replace(
"c.empresa",
"e.stockemp").Replace(
"d.articulo",
"c.articulo");
816 lcFiltro = lcFiltro.Replace(
"d.talla",
"''").Replace(
"d.color",
"''");
818 lcSql =
" (SELECT e.almacen, c.articulo, '' AS talla, '' AS color, c.serie, (CASE WHEN c.unidades < 0 THEN 1.00 ELSE - 1.00 END) AS unidades ";
819 lcSql +=
" FROM " + DB.SQLDatabase(
"COMUNES",
"modelo") +
" e ";
820 lcSql +=
" INNER JOIN " + DB.SQLDatabase(
"COMUNES",
"costemod") +
" c ON e.codigo = c.modelo ";
821 lcSql +=
" INNER JOIN " + DB.SQLDatabase(
"GESTION",
"ARTICULO") +
" a ON c.articulo = a.codigo AND a.stock = 0 ";
822 lcSql +=
" WHERE " + _EmpStock(
"e.stockemp") + lcFiltro +
" ) ";
824 lcSql +=
" UNION ALL ";
826 lcFiltro = tcWhere.Replace(
"c.fecha",
"e.montaje").Replace(
"c.empresa",
"e.stockemp").Replace(
"d.articulo",
"e.articulo");
827 lcFiltro = lcFiltro.Replace(
"d.talla",
"e.talla").Replace(
"d.color",
"e.color").Replace(
"c.modelo",
"e.codigo");
829 lcSql +=
" (SELECT DISTINCT e.almacen, e.articulo, e.talla, e.color, e.codigo as serie, 1.00 AS unidades ";
830 lcSql +=
" FROM " + DB.SQLDatabase(
"COMUNES",
"modelo") +
" e ";
831 lcSql +=
" INNER JOIN " + DB.SQLDatabase(
"GESTION",
"ARTICULO") +
" a ON e.articulo = a.codigo AND a.stock = 0 ";
832 lcSql +=
" WHERE " + _EmpStock(
"e.stockemp") + lcFiltro +
" ) ";
834 DB.SQLExec(lcSql, ref ldtMontajes);
836 ldtMontajes.DefaultView.Sort =
"almacen ASC, articulo ASC, talla ASC, color ASC, serie ASC ";
837 ldtMontajes = ldtMontajes.DefaultView.ToTable();
851 String lcSql =
string.Empty;
852 DataTable ldtAlbaregu =
new DataTable();
854 if (
string.IsNullOrWhiteSpace(tcEjercicio))
855 tcEjercicio = Convert.ToString(EW_GLOBAL._GetVariable(
"wc_any"));
857 string lcNegativo =
string.Empty;
861 lcNegativo =
" , (CASE WHEN d.unidades<0 THEN 1.00 ELSE -1.00 END) AS unidades ";
865 lcNegativo =
" , (CASE WHEN d.unidades>0 THEN 1.00 ELSE -1.00 END) AS unidades ";
868 lcSql =
" SELECT c.almacen, d.articulo, d.talla, d.color, s.serie " + lcNegativo;
869 lcSql +=
" FROM " + DB.SQLDatabase(
"",
"C_ALBARE") +
" c ";
870 lcSql +=
" INNER JOIN " + DB.SQLDatabase(
"",
"D_ALBARE") +
" d ON c.empresa = d.empresa AND c.numero = d.numero ";
871 lcSql +=
" INNER JOIN " + DB.SQLDatabase(
"",
"REGUSER") +
" s ON d.empresa = s.empresa AND d.numero = s.albaran AND d.linia = s.linea ";
872 lcSql +=
" INNER JOIN " + DB.SQLDatabase(
"",
"ARTICULO") +
" a ON d.articulo = a.codigo AND a.stock = 0 ";
873 lcSql +=
" WHERE " + this._EmpStock(
"c.Empresa") + tcWhere;
875 DB.SQLExecEjer(lcSql, ref ldtAlbaregu,
new string[] { tcEjercicio });
888 String lcSql =
string.Empty;
889 DataTable ldtTraspasEnt =
new DataTable();
891 if (
string.IsNullOrWhiteSpace(tcEjercicio))
892 tcEjercicio = Convert.ToString(EW_GLOBAL._GetVariable(
"wc_any"));
895 tcWhere = tcWhere.Replace(
"almacen",
"almdest");
898 lcSql =
" SELECT c.almdest as almacen, d.articulo, d.talla, d.color, s.serie, (CASE WHEN d.unidades>0 THEN 1.00 ELSE -1.00 END) AS unidades ";
899 lcSql +=
" FROM " + DB.SQLDatabase(
"",
"C_ALBATR") +
" c ";
900 lcSql +=
" INNER JOIN " + DB.SQLDatabase(
"",
"D_ALBATR") +
" d ON c.empresa = d.empresa AND c.numero = d.numero ";
901 lcSql +=
" INNER JOIN " + DB.SQLDatabase(
"",
"TRASPSER") +
" s ON d.empresa = s.empresa AND d.numero = s.albaran AND d.linia = s.linea ";
902 lcSql +=
" INNER JOIN " + DB.SQLDatabase(
"",
"ARTICULO") +
" a ON d.articulo = a.codigo AND a.stock = 0 ";
903 lcSql +=
" WHERE " + _EmpStock(
"c.Empresa") + tcWhere;
905 DB.SQLExecEjer(lcSql, ref ldtTraspasEnt,
new string[] { tcEjercicio });
907 return ldtTraspasEnt;
919 string lcSql =
string.Empty;
920 DataTable ldtTraspasSal =
new DataTable();
922 if (
string.IsNullOrWhiteSpace(tcEjercicio))
923 tcEjercicio = Convert.ToString(EW_GLOBAL._GetVariable(
"wc_any"));
926 tcWhere = tcWhere.Replace(
"almacen",
"almorig");
928 string lcNegativo =
string.Empty;
932 lcNegativo =
" , (CASE WHEN d.unidades<0 THEN 1.00 ELSE -1.00 END) AS unidades ";
936 lcNegativo =
" , (CASE WHEN d.unidades>0 THEN 1.00 ELSE -1.00 END) AS unidades ";
939 lcSql =
" SELECT c.almorig as almacen, d.articulo, d.talla, d.color, s.serie " + lcNegativo;
940 lcSql +=
" FROM " + DB.SQLDatabase(
"",
"C_ALBATR") +
" c ";
941 lcSql +=
" INNER JOIN " + DB.SQLDatabase(
"",
"D_ALBATR") +
" d ON c.empresa = d.empresa AND c.numero = d.numero ";
942 lcSql +=
" INNER JOIN " + DB.SQLDatabase(
"",
"TRASPSER") +
" s ON d.empresa = s.empresa AND d.numero = s.albaran AND d.linia = s.linea ";
943 lcSql +=
" INNER JOIN " + DB.SQLDatabase(
"",
"ARTICULO") +
" a ON d.articulo = a.codigo AND a.stock = 0 ";
944 lcSql +=
" WHERE " + _EmpStock(
"c.Empresa") + tcWhere;
946 DB.SQLExecEjer(lcSql, ref ldtTraspasSal,
new string[] { tcEjercicio });
948 return ldtTraspasSal;
959 string lcSql =
string.Empty;
960 DataTable ldtDepoCom =
new DataTable();
962 if (
string.IsNullOrWhiteSpace(tcEjercicio))
963 tcEjercicio = Convert.ToString(EW_GLOBAL._GetVariable(
"wc_any"));
965 lcSql =
" SELECT c.almacen, d.articulo, d.talla, d.color, s.serie, (CASE WHEN d.unidades>0 THEN 1.00 ELSE -1.00 END) AS unidades ";
966 lcSql +=
" FROM " + DB.SQLDatabase(
"",
"C_DEPCOM") +
" c ";
967 lcSql +=
" INNER JOIN " + DB.SQLDatabase(
"",
"D_DEPCOM") +
" d ON c.empresa = d.empresa AND c.numero = d.numero ";
968 lcSql +=
" INNER JOIN " + DB.SQLDatabase(
"",
"DEPCSER") +
" s ON d.empresa = s.empresa AND d.numero = s.albaran AND d.linia = s.linea ";
969 lcSql +=
" INNER JOIN " + DB.SQLDatabase(
"",
"ARTICULO") +
" a ON d.articulo = a.codigo AND a.stock = 0 ";
970 lcSql +=
" WHERE " + _EmpStock(
"c.Empresa") + tcWhere;
973 DB.SQLExecEjer(lcSql, ref ldtDepoCom,
new string[] { tcEjercicio });
985 private DataTable _InventaSeries_Depcser_Trasp(
string tcWhere,
string tcEjercicio, DateTime? tdFecha = null)
987 string lcSql =
string.Empty;
988 DataTable ldtDepoComTrasp =
new DataTable();
990 if (
string.IsNullOrWhiteSpace(tcEjercicio))
991 tcEjercicio = Convert.ToString(EW_GLOBAL._GetVariable(
"wc_any"));
993 lcSql =
" SELECT d.articulo, d.talla, d.color, s.serie, c.almacen, c.fecha, (CASE WHEN b.doc_unid>0 THEN 1.00 ELSE -1.00 END) AS unidades ";
994 lcSql +=
" FROM " + DB.SQLDatabase(
"",
"c_depcom") +
" c ";
995 lcSql +=
" INNER JOIN " + DB.SQLDatabase(
"",
"d_depcom") +
" d ON c.empresa = d.empresa AND c.numero = d.numero ";
996 lcSql +=
" INNER JOIN " + DB.SQLDatabase(
"",
"d_albcom") +
" b ON d.empresa = b.empresa AND d.numero = b.doc_num AND d.proveedor = b.proveedor AND d.linia = b.doc_lin AND b.doc = 3 ";
997 lcSql +=
" INNER JOIN " + DB.SQLDatabase(
"",
"depcser") +
" s ON d.empresa = s.empresa AND d.numero = s.albaran AND d.linia = s.linea ";
998 lcSql +=
" INNER JOIN " + DB.SQLDatabase(
"",
"ARTICULO") +
" a ON d.articulo = a.codigo AND a.stock = 0 ";
999 lcSql +=
" WHERE " + _EmpStock(
"c.Empresa") + tcWhere + (tdFecha != null ?
" AND b.fecha > " + DB.SQLString(tdFecha) :
"") +
" ";
1000 lcSql +=
" AND s.traspaso = 1 ";
1001 lcSql +=
" ORDER BY c.almacen, d.articulo, d.talla, d.color, s.serie ";
1003 DB.SQLExecEjer(lcSql, ref ldtDepoComTrasp,
new string[] { tcEjercicio });
1005 return ldtDepoComTrasp;
1017 string lcSql =
string.Empty;
1018 DataTable ldtDepoVen =
new DataTable();
1020 if (
string.IsNullOrWhiteSpace(tcEjercicio))
1021 tcEjercicio = Convert.ToString(EW_GLOBAL._GetVariable(
"wc_any"));
1023 string lcNegativo =
string.Empty;
1027 lcNegativo =
" , (CASE WHEN d.unidades<0 THEN 1.00 ELSE -1.00 END) AS unidades ";
1031 lcNegativo =
" , (CASE WHEN d.unidades>0 THEN 1.00 ELSE -1.00 END) AS unidades ";
1034 lcSql =
" SELECT c.almacen, d.articulo, d.talla, d.color, s.serie " + lcNegativo;
1035 lcSql +=
" FROM " + DB.SQLDatabase(
"",
"C_ALBDEP") +
" c ";
1036 lcSql +=
" INNER JOIN " + DB.SQLDatabase(
"",
"D_ALBDEP") +
" d ON c.empresa = d.empresa AND c.numero = d.numero AND c.letra = d.letra ";
1037 lcSql +=
" INNER JOIN " + DB.SQLDatabase(
"",
"DEPSER") +
" s ON d.empresa = s.empresa AND d.numero = s.deposito AND c.letra = d.letra AND d.linia = s.linea ";
1038 lcSql +=
" INNER JOIN " + DB.SQLDatabase(
"",
"ARTICULO") +
" a ON d.articulo = a.codigo AND a.stock = 0 ";
1039 lcSql +=
" WHERE " + _EmpStock(
"c.Empresa") + tcWhere;
1042 DB.SQLExecEjer(lcSql, ref ldtDepoVen,
new string[] { tcEjercicio });
1047 private DataTable _InventaSeries_Depser_Trasp(
string tcWhere,
string tcEjercicio,
bool tlNegativo =
true, DateTime? tdFecha = null)
1049 string lcSql =
string.Empty;
1050 DataTable ldtDepoVenTrasp =
new DataTable();
1052 if (
string.IsNullOrWhiteSpace(tcEjercicio))
1053 tcEjercicio = Convert.ToString(EW_GLOBAL._GetVariable(
"wc_any"));
1055 string lcNegativo =
string.Empty;
1059 lcNegativo =
" (CASE WHEN b.doc_unid<0 THEN 1.00 ELSE -1.00 END) AS unidades ";
1063 lcNegativo =
" (CASE WHEN b.doc_unid>0 THEN 1.00 ELSE -1.00 END) AS unidades ";
1066 lcSql =
" SELECT d.articulo, d.talla, d.color, s.serie, c.almacen, c.fecha, " + lcNegativo;
1067 lcSql +=
" FROM " + DB.SQLDatabase(
"",
"c_albdep") +
" c ";
1068 lcSql +=
" INNER JOIN " + DB.SQLDatabase(
"",
"d_albdep") +
" d ON c.empresa = d.empresa AND c.numero = d.numero AND c.letra = d.letra ";
1069 lcSql +=
" INNER JOIN " + DB.SQLDatabase(
"",
"d_albven") +
" b ON d.empresa = b.empresa AND d.numero+d.letra = b.doc_num AND d.linia = b.doc_lin AND b.doc = 2 ";
1070 lcSql +=
" INNER JOIN " + DB.SQLDatabase(
"",
"depser") +
" s ON d.empresa = s.empresa AND d.numero = s.deposito AND c.letra = d.letra AND d.linia = s.linea ";
1071 lcSql +=
" INNER JOIN " + DB.SQLDatabase(
"",
"ARTICULO") +
" a ON d.articulo = a.codigo AND a.stock = 0 ";
1072 lcSql +=
" WHERE " + _EmpStock(
"c.Empresa") + tcWhere + (tdFecha != null ?
" AND b.fecha > " + DB.SQLString(tdFecha) :
"") +
" ";
1073 lcSql +=
" AND s.traspaso = 1 ";
1074 lcSql +=
" ORDER BY c.almacen, d.articulo, d.talla, d.color, s.serie ";
1076 DB.SQLExecEjer(lcSql, ref ldtDepoVenTrasp,
new string[] { tcEjercicio });
1078 return ldtDepoVenTrasp;
1089 string lcSql =
string.Empty;
1090 DataTable ldtCprod =
new DataTable();
1092 if (
string.IsNullOrWhiteSpace(tcEjercicio))
1093 tcEjercicio = Convert.ToString(EW_GLOBAL._GetVariable(
"wc_any"));
1095 tcWhere +=
" AND c.acabado = 1 ";
1097 lcSql =
" SELECT c.almacen, d.articulo, d.talla, d.color, s.serie, (CASE WHEN d.entrada>0 THEN 1.00 ELSE -1.00 END) AS unidades ";
1098 lcSql +=
" FROM " + DB.SQLDatabase(
"",
"C_PROD") +
" c ";
1099 lcSql +=
" INNER JOIN " + DB.SQLDatabase(
"",
"C_PROD") +
" d ON c.empresa = d.empresa AND c.numero = d.numero ";
1100 lcSql +=
" INNER JOIN " + DB.SQLDatabase(
"",
"PRODUSERC") +
" s ON d.empresa = s.empresa AND d.numero = s.albaran ";
1101 lcSql +=
" INNER JOIN " + DB.SQLDatabase(
"",
"ARTICULO") +
" a ON d.articulo = a.codigo AND a.stock = 0 ";
1102 lcSql +=
" WHERE " + _EmpStock(
"c.Empresa") + tcWhere;
1104 DB.SQLExecEjer(lcSql, ref ldtCprod,
new string[] { tcEjercicio });
1118 string lcSql =
string.Empty;
1119 DataTable ldtDprod =
new DataTable();
1121 if (
string.IsNullOrWhiteSpace(tcEjercicio))
1122 tcEjercicio = Convert.ToString(EW_GLOBAL._GetVariable(
"wc_any"));
1124 tcWhere +=
" AND c.acabado = 1 ";
1126 string lcNegativo =
string.Empty;
1130 lcNegativo =
" , (CASE WHEN d.salida<0 THEN 1.00 ELSE -1.00 END) AS unidades ";
1134 lcNegativo =
" , (CASE WHEN d.salida>0 THEN 1.00 ELSE -1.00 END) AS unidades ";
1137 lcSql =
" SELECT c.almacen, d.articulo, d.talla, d.color, s.serie " + lcNegativo;
1138 lcSql +=
" FROM " + DB.SQLDatabase(
"",
"C_PROD") +
" c ";
1139 lcSql +=
" INNER JOIN " + DB.SQLDatabase(
"",
"D_PROD") +
" d ON c.empresa = d.empresa AND c.numero = d.numero ";
1140 lcSql +=
" INNER JOIN " + DB.SQLDatabase(
"",
"PRODUSER") +
" s ON d.empresa = s.empresa AND d.numero = s.albaran AND d.linea = s.linea ";
1141 lcSql +=
" INNER JOIN " + DB.SQLDatabase(
"",
"ARTICULO") +
" a ON d.articulo = a.codigo AND a.stock = 0 ";
1142 lcSql +=
" WHERE " + _EmpStock(
"c.Empresa") + tcWhere;
1144 DB.SQLExecEjer(lcSql, ref ldtDprod,
new string[] { tcEjercicio });
1158 String lcSql =
string.Empty;
1159 DataTable ldtCtran =
new DataTable();
1161 if (
string.IsNullOrWhiteSpace(tcEjercicio))
1162 tcEjercicio = Convert.ToString(EW_GLOBAL._GetVariable(
"wc_any"));
1164 string lcNegativo =
string.Empty;
1168 lcNegativo =
" , (CASE WHEN d.salida<0 THEN 1.00 ELSE -1.00 END) AS unidades ";
1172 lcNegativo =
" , (CASE WHEN d.salida>0 THEN 1.00 ELSE -1.00 END) AS unidades ";
1175 lcSql =
" SELECT c.almacen, d.articulo, d.talla, d.color, s.serie " + lcNegativo;
1176 lcSql +=
" FROM " + DB.SQLDatabase(
"",
"C_TRANS") +
" c ";
1177 lcSql +=
" INNER JOIN " + DB.SQLDatabase(
"",
"C_TRANS") +
" d ON c.empresa = d.empresa AND c.numero = d.numero ";
1178 lcSql +=
" INNER JOIN " + DB.SQLDatabase(
"",
"TRANSSER") +
" s ON d.empresa = s.empresa AND d.numero = s.albaran ";
1179 lcSql +=
" INNER JOIN " + DB.SQLDatabase(
"",
"ARTICULO") +
" a ON d.articulo = a.codigo AND a.stock = 0 ";
1180 lcSql +=
" WHERE " + _EmpStock(
"c.Empresa") + tcWhere;
1182 DB.SQLExecEjer(lcSql, ref ldtCtran,
new string[] { tcEjercicio });
1196 string lcSql =
string.Empty;
1197 DataTable ldtDtran =
new DataTable();
1199 if (
string.IsNullOrWhiteSpace(tcEjercicio))
1200 tcEjercicio = Convert.ToString(EW_GLOBAL._GetVariable(
"wc_any"));
1202 lcSql =
" SELECT c.almacen, d.articulo, d.talla, d.color, s.serie, (CASE WHEN d.entrada>0 THEN 1.00 ELSE -1.00 END) AS unidades ";
1203 lcSql +=
" FROM " + DB.SQLDatabase(
"",
"C_TRANS") +
" c ";
1204 lcSql +=
" INNER JOIN " + DB.SQLDatabase(
"",
"D_TRANS") +
" d ON c.empresa = d.empresa AND c.numero = d.numero ";
1205 lcSql +=
" INNER JOIN " + DB.SQLDatabase(
"",
"TRANSER") +
" s ON d.empresa = s.empresa AND d.numero = s.albaran AND d.linea = s.linea ";
1206 lcSql +=
" INNER JOIN " + DB.SQLDatabase(
"",
"ARTICULO") +
" a ON d.articulo = a.codigo AND a.stock = 0 ";
1207 lcSql +=
" WHERE " + _EmpStock(
"c.Empresa") + tcWhere;
1209 DB.SQLExecEjer(lcSql, ref ldtDtran,
new string[] { tcEjercicio });
1223 string lcSql =
string.Empty;
1224 DataTable ldtInicial =
new DataTable();
1226 if (
string.IsNullOrWhiteSpace(tcEjercicio))
1227 tcEjercicio = Convert.ToString(EW_GLOBAL._GetVariable(
"wc_any"));
1229 lcSql =
" SELECT c.almacen, d.articulo, d.talla, d.color, s.serie, (CASE WHEN s.baja = 0 THEN 1.00 ELSE -1.00 END) AS unidades ";
1230 lcSql +=
" FROM " + DB.SQLDatabase(
"",
"STOCKINI") +
" c ";
1231 lcSql +=
" INNER JOIN " + DB.SQLDatabase(
"",
"STOCKINI") +
" d ON c.empresa = d.empresa AND c.almacen = d.almacen AND c.fecha = d.fecha AND c.linia = d.linia AND c.articulo = d.articulo ";
1232 lcSql +=
" INNER JOIN " + DB.SQLDatabase(
"",
"INICIALSER") +
" s ON d.almacen = s.almacen AND d.articulo = s.articulo AND s.baja = 0 ";
1233 lcSql +=
" INNER JOIN " + DB.SQLDatabase(
"",
"ARTICULO") +
" a ON d.articulo = a.codigo AND a.stock = 0 ";
1234 lcSql +=
" WHERE " + _EmpStock(
"c.Empresa") + tcWhere;
1236 DB.SQLExecEjer(lcSql, ref ldtInicial,
new string[] { tcEjercicio });
1249 String lcSql =
string.Empty;
1250 DataTable ldtRegul =
new DataTable();
1252 if (
string.IsNullOrWhiteSpace(tcEjercicio))
1253 tcEjercicio = Convert.ToString(EW_GLOBAL._GetVariable(
"wc_any"));
1255 lcSql =
" SELECT c.almacen, d.articulo, d.talla, d.color, s.serie, (CASE WHEN d.unidades>0 THEN 1.00 ELSE -1.00 END) AS unidades ";
1256 lcSql +=
" FROM " + DB.SQLDatabase(
"",
"REGULARI") +
" c ";
1257 lcSql +=
" INNER JOIN " + DB.SQLDatabase(
"",
"REGULARI") +
" d ON c.empresa = d.empresa AND c.almacen = d.almacen AND c.fecha = d.fecha AND c.linia = d.linia AND c.documento = d.documento ";
1258 lcSql +=
" INNER JOIN " + DB.SQLDatabase(
"",
"REGULARISER") +
" s ON d.empresa = s.empresa AND d.almacen = s.almacen AND d.fecha = s.fecha AND d.linia = s.linea AND d.documento = s.documento AND s.baja = 0 ";
1259 lcSql +=
" INNER JOIN " + DB.SQLDatabase(
"",
"ARTICULO") +
" a ON d.articulo = a.codigo AND a.stock = 0 ";
1260 lcSql +=
" WHERE " + _EmpStock(
"c.Empresa") + tcWhere;
1262 DB.SQLExecEjer(lcSql, ref ldtRegul,
new string[] { tcEjercicio });
1275 string lcResul =
string.Empty;
1277 DataTable ldtResult =
new DataTable();
1279 if (Convert.ToBoolean(EW_GLOBAL._GetVariable(
"wl_normal")))
1281 if (
string.IsNullOrWhiteSpace(Convert.ToString(EW_GLOBAL._GetVariable(
"wc_empstock"))))
1282 DB.SQLExec(
"select empresa from " + DB.SQLDatabase(
"GESTION",
"factucnf") +
" where emp_stock='" + Convert.ToString(EW_GLOBAL._GetVariable(
"wc_EmpStockConsolida")) +
"'", ref ldtResult);
1284 DB.SQLExec(
"select empresa from " + DB.SQLDatabase(
"GESTION",
"factucnf") +
" where emp_stock='" + Convert.ToString(EW_GLOBAL._GetVariable(
"wc_EmpStock")) +
"'", ref ldtResult);
1286 if (ldtResult != null)
1288 lnRegs = ldtResult.Rows.Count;
1291 lcResul = tcCampo +
"=" + DB.SQLString(ldtResult.Rows[0][
"empresa"]);
1293 else if (lnRegs == 0)
1295 lcResul = tcCampo +
"=" + DB.SQLString(Convert.ToString(EW_GLOBAL._GetVariable(
"wc_EmpStock")));
1297 else if (lnRegs > 1)
1300 foreach (DataRow ldr
in ldtResult.Rows)
1302 lcResul +=
" " + tcCampo +
"=" + DB.SQLString(ldr[
"empresa"]) +
" OR";
1304 lcResul = lcResul.Substring(0, lcResul.Length - 2) +
")";
1310 lcResul = EW_GLOBAL._Consolida(tcCampo);
1315 #endregion Stocks series DataTable _InventaSeries_Venser(string tcWhere, string tcEjercicio, bool tlNegativo=true, bool incluirOrigenDeposito=false)
Nos devuelve las series de los albaranes de venta que cumplen la condición
string _EmpStock(string tcCampo)
Sacada del modulo de LOTES : stocklote.emp_stock
DataTable _InventaSeries_Reguser(string tcWhere, string tcEjercicio, bool tlNegativo=true)
Nos devuelve las series de los albaranes de regularización que cumplen la condición ...
DataTable _StockSerie(string tcWhere, Stock.TipoValoracion teValoracion=Stock.TipoValoracion.IncluyendoDepositos, DateTime? tdFecha=null)
Devuelve un datatable con todos los movimientos de los lotes, en funció del parametro ...
Calcula l'stock de tots els articles, segons els filtres Pot calcular - stock normal + PMS i PMC ...
TipoValoracion
Enumeració dels diferents tipos de valoración
DataTable _InventaSeries_Transser(string tcWhere, string tcEjercicio, bool tlNegativo=true)
Nos devuelve las series de la cabecera de las transformaciones que cumplen la condición ...
DataTable _InventaSeries_Transer(string tcWhere, string tcEjercicio, bool tlNegativo=true)
Nos devuelve las series del detalle de las transformaciones que cumplen la condición ...
DataTable _InventaSeries_Inicialser(string tcWhere, string tcEjercicio)
Nos devuelve las series de los inventarios iniciales que cumplen la condición
DataTable _InventaSeries_Comser(string tcWhere, string tcEjercicio, bool incluirOrigenDeposito=true)
Nos devuelve las series de los albaranes de compra que cumplen la condición
DataTable _InventaSeries_Prodser(string tcWhere, string tcEjercicio)
Nos devuelve las series de la cabecera de las producciones que cumplen la condición ...
DataTable _InventaSeries_Regulariser(string tcWhere, string tcEjercicio)
Nos devuelve las series de los inventarios de regularización que cumplen la condición ...
Código stocks para series
DataTable _InventaSeries_Depser(string tcWhere, string tcEjercicio, bool tlNegativo=true)
Nos devuelve las series de los depósitos de venta que cumplen la condición
DataTable _InventaSeries_Montajes(string tcWhere)
Nos devuelve las series de los montajes que cumplen la condición
DataTable _InventaSeries_Traspser_Entradas(string tcWhere, string tcEjercicio)
Nos devuelve las series de los albaranes de traspaso que cumplen la condición
DataTable _InventaSeries_Produser(string tcWhere, string tcEjercicio, bool tlNegativo=true)
Nos devuelve las series del detalle de las producciones que cumplen la condición
DataTable _InventaSeries_Traspser_Salidas(string tcWhere, string tcEjercicio, bool tlNegativo=true)
Nos devuelve las series de los albaranes de traspaso que cumplen la condición
DataTable _InventaSeries_Depcser(string tcWhere, string tcEjercicio)
Nos devuelve las series de los depósitos de compra que cumplen la condición