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
| SELECT
dbo.articles.art_id,
dbo.articles.art_ref_client,
dbo.articles.art_libelle,
dbo.stocksclients.stkclt_nom,
dbo.familles.fam_libelle,
dbo.fournisseurs.frn_nom,
dbo.operations.ope_id,
dbo.operations.ope_libelle,
uc_libelle = uc.uc_libelle,
dbo.articles.art_qte_par_uc,
ud_libelle = ud.uc_libelle,
dbo.articles.art_qte_par_ud,
dbo.typesarticles.tart_libelle,
dbo.articles.art_prix_vente,
dbo.articles.art_actif,
dbo.operations.ope_nbr_reference,
dbo.stocksclients.stkclt_id,
dbo.stocksclients.stkclt_id_clt,
dbo.fournisseurs.frn_id,
dbo.typesarticles.tart_id,
dbo.familles.fam_id,
dbo.articles.art_date_creation,
art_id_stk = (case
when exists(select top 1 0 from activites with(nolock) where(act_actif = 1) and act_id_ope in(stkclt_id_clt, (select ope_id from operations with(nolock) where act_id_ope = ope_id and ope_id_clt = stkclt_id_clt)))
then ag_qte_disponible
else (select sum(dbo.detailsmouvements.dmvt_qte) as sum_stock
from dbo.mouvements with(nolock)
inner join dbo.detailsmouvements with(nolock) on dbo.mouvements.mvt_id = dbo.detailsmouvements.dmvt_id_mvt
where(isnull(dbo.mouvements.mvt_statut, 3) = 3)
and(dbo.mouvements.mvt_id_tmvt <> 1)
and(dbo.mouvements.mvt_id_tmvt <> 6)
and dmvt_id_art = dbo.articles.art_id and dmvt_id_gam = gammes.gam_id)
end),
nullif(dbo.articles.art_poids_unite, 0) as art_poids_unite,
dbo.gammes.gam_id as art_id_gam,
dbo.articles.art_photo,
dbo.articles.art_prix_achat,
cast(case
when art_photo is null
then 0
when len(art_photo) = 0
then 0
else 1
end as bit) as art_blnphoto,
dbo.gammes.gam_id,
dbo.gammes.gam_libelle,
art_extranet,
art_seuil_alerte,
art_delai_reapro,
art_qte_max_palette
FROM dbo.articles with (nolock)
inner join dbo.articles_gam with(nolock) on dbo.articles_gam.ag_id_art = dbo.articles.art_id
inner join dbo.gammes with(nolock) on dbo.gammes.gam_id = dbo.articles_gam.ag_id_gam
inner join dbo.stocksclients with(nolock) on dbo.gammes.gam_id_stk = dbo.stocksclients.stkclt_id
inner join dbo.familles with(nolock) on dbo.articles.art_id_fam = dbo.familles.fam_id
inner join dbo.typesarticles with(nolock) on dbo.articles.art_id_tart = dbo.typesarticles.tart_id
inner join dbo.unitesconditionnements uc with(nolock) on dbo.articles.art_id_uc = uc.uc_id
left join dbo.unitesconditionnements ud with(nolock) on dbo.articles.art_id_ud = ud.uc_id
left outer join dbo.operations with(nolock) on dbo.articles.art_id_ope = dbo.operations.ope_id
left outer join dbo.fournisseurs with(nolock) on dbo.articles.art_id_frn = dbo.fournisseurs.frn_id
inner join(select clt_id
from clients with(nolock)
inner join collaborateurs with(nolock) on clients.clt_id_clb = collaborateurs.clb_id
where collaborateurs.clb_id_site = 9
) clt on stkclt_id_clt = clt.clt_id
WHERE art_actif = 1 |
