إلى طلاب منتدى الجلفة

[mouhamad2009]

السلام عليكم اخواني اخواتي اريد منكم مساعدتي اريد شرحا دقيق
1تصميم التساؤلات :
*مواد سند الطلب
SELECT Num_Article,Designation_Article,
FROM Article , Concerne1, Bon_Commande
Where Article.Num_Article = Concerne1.Num_Article and Concerne1.Num_Bon_Commande = Bon_Commande.Num_Bon_Commande

-تصميم رؤية لمعرفة مواد سند طلب بتاريخ معين
CREATE VIEW [dbo].[Articles_Commandées_Dat]
AS
SELECT dbo.Article.Designation_Article, dbo.Concerne1.Quantite_Demande, dbo.Concerne1.Quantite_Accepte, dbo.Bon_Commande.Date_Commande
FROM dbo.Article INNER JOIN
dbo.Concerne1 ON dbo.Article.Num_Article = dbo.Concerne1.Num_Article INNER JOIN
dbo.Bon_Commande ON dbo.Concerne1.Num_Bon_Commande = dbo.Bon_Commande.Num_Bon_Commande
WHERE (dbo.Bon_Commande.Date_Commande = CONVERT(DATETIME, '2013-08-01', 102))
-1-Afficher_Article

Use Gestion_Stock
setANSI_NULLSON
setQUOTED_IDENTIFIERON
go
Create procedure [dbo].[Afficher_Article] (@Num_Article nvarchar(7),@Num_Inventaire nvarchar(7)output,@Num_Facture nvarchar(10)output, @Designation_Article nvarchar(50)output,@Qualite_Article nvarchar(50)output,@Division intoutput,@Quantite_Article intoutput,@Date_Recevoir datetimeoutput,@Nom_Centre nvarchar(50)output)
as
select
@Num_Inventaire=Num_Inventaire ,
@Num_Facture=Num_Facture,
@Designation_Article=Designation_Article ,
@Qualite_Article=Qualite_Article ,
@Division=Division ,
@Quantite_Article=Quantite_Article ,
@Date_Recevoir=Date_Recevoir
from Article
where Num_Article=@Num_Article
إنشاء قادح لإضافة الكمية
Create trigger [Ajout_Quantite]
on [dbo].[Operation_Entree_Sortie]
after insert,update
as
declare @Date_Operation datetime
declare @Date_Recevoir datetime
declare @Quantite_Entree int
declare @Quantite_Sortie int
declare @Num_Article nvarchar(7)
select @Date_Operation=Date_Operation,@Num_Article=Num_Ar ticle from inserted
select @Date_Recevoir=Date_Recevoir from Article where @Num_Article=Num_Article
select @Quantite_Entree=sum(Quantite_Entree),@Quantite_So rtie=sum(Quantite_Sortie)
from Operation_Entree_Sortie
where Num_Article=@Num_Article
if @Quantite_Sortie is null
set @Quantite_Sortie=0
if @Quantite_Entree is null
set @Quantite_Entree=0
update Article set
Quantite_Article=@Quantite_Entree-@Quantite_Sortie
where Num_Article=@Num_Article
end
تصميم الوظائفFonctions
تحويل الأعداد من الأرقام إلى الحروف
Number
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER FUNCTION [dbo].[Number] (@id int)
RETURNS nvarchar(4) AS
begin
declare @Num nvarchar(4)
if len(cast(@id as char))=1
set @Num='000'+cast(@id as char)
else
if len(cast(@id as char))=2
set @Num='00'+cast(@id as char)
else
if len(cast(@id as char))=3
set @Num='0'+cast(@id as char)
else
set @Num=cast(@id as char)
Return @Num
end