abbashayali
شنبه 17 فروردین 1398, 16:06 عصر
باسلام
من یک استور پراسیجر دارم که میخواهم در لود فرمم ایجاد بشه ازش استفاده کنم و پس از بستن فرم دراپ بشه ممنون میشم راهنمایی بفرمایید کد استورپراسیجر را میزارم
زبان مورد استفاده من سی شارپ بوده و ویندوز فرم می باشد.
IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'dbo.TTMS_Kharid')
DROP PROCEDURE dbo.TTMS_Kharid
USE [IFS_803]
GO
/****** Object: StoredProcedure [dbo].[TTMS_Kharid] Script Date: 01/17/1398 02:51:51 ب.ظ ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create PROCEDURE [dbo].[TTMS_Kharid]
@Fpid int,
@SCODE INT
AS
BEGIN
select ROW_NUMBER()over(order by Price) as Radif,* from
(select * from
(select 0 as Sarjam,0 as IsHagholAmalKari,12 as KalaType
,(select Name from __Merchandise__ where id = b.MerchandiseId and FPId=@Fpid)KalaKhadamatName,0 as KalaCode,case when a.FactorType in (0,10) then 0 else -1 end as BargashtType,
b.AuxAmount as Price,(b.VTax-b.VCharge)as MaliatArzeshAfzoodeh,b.VCharge as AvarezArzeshAfzoodeh,'0' as SayerAvarez, '0' as TakhfifPrice,
MType+1 as HCForoushandeTypeCode,ZipCode as ForoushandePostCode,'0' as ForoushandePerCityCode,PhoneNo as ForoushandeTell,Address as ForoushandeAddress
,Name as ForoushandeName,Family as ForoushandeLastNameSherkatName,EcCode as ForoushandeEconomicNO,c.TRes as ForoushandeNationalCode
,5 as HCForoushandeType1Code,cast((select code from __AccGeo__ where id=Province)as bigint) StateCode
,cast((select code from __AccGeo__ where id=Division)as bigint) CityCode,0 as ArzType,0 as Arz_Price,0 as Arz_MaliatArzeshAfzoodeh,0 as Arz_AvarezArzeshAfzoodeh
,0 as Arz_SayerAvarez,0 as Arz_TakhfifPrice,0 as ArzBarabari_Price,0 as ArzBarabari_TakhfifPrice,0 as ArzBarabari_MaliatArzeshAfzoodeh,0 as ArzBarabari_AvarezArzeshAfzoodeh,0 as ArzBarabari_SayerAvarez,0 as MoadelRialiPrice,0 as MoadelRialiTakhfifPrice,
0 as MoadelRialiMaliatArzeshAfzoodeh,0 as MoadelRialiAvarezArzeshAfzoodeh,0 as MoadelRialiSayerAvarez,
FactorNo,dbo.__MiladiToShamsi__(a.SPDate) as FactorDate,cast(SanadNo as bigint) as SanadNo ,SanadDate,'0' as IsSent
from __SPFactor__ a
inner join __SPArticle__ b
on a.Id=b.SPId
and a.FPId=b.FPId
and a.FPId=@Fpid
and a.FactorType in (0,2,10)
inner join __Customer__ c
on a.CustomerId=c.Id
inner join
(select * from
(select a.InvSPId,b.SanadNo,dbo.__MiladiToShamsi__(b.TDate ) SanadDate,
case when SCode in (1,2,3,4) then '1'
when SCode in (5,6,7) then '2'
when SCode in (8,9,10) then '3'
else '4' end as Scode
from __Article__ a
inner join __Transaction__ b
on a.TransId=b.Id
and a.FPId=b.FPId
and b.Committed<>0
and b.SCode<>0
where b.FPId=@Fpid
and a.InvSPId<>0)T
group by InvSPId,SanadNo,SanadDate,Scode)TT
on a.Id=tt.InvSPId
and tt.Scode=@Scode)t
union all
select -1,0,'','',0,0, (select sum(debit) from
(select sum(Debit)Debit,
case when SCode in (1,2,3,4) then '1'
when SCode in (5,6,7) then '2'
when SCode in (8,9,10) then '3'
else '4' end as Scode
from __Article__ a
inner join __Transaction__ b
on a.TransId=b.Id
and a.FPId=b.FPId
where a.FPId=@Fpid
and a.InvMTId<>0
and a.InvSPId=0
and left(a.AccountId,2)in ('81','22')
group by SCode)t
where Scode=@Scode),cast((select round((sum(debit*6/100)),0) from
(select sum(Debit)Debit,
case when SCode in (1,2,3,4) then '1'
when SCode in (5,6,7) then '2'
when SCode in (8,9,10) then '3'
else '4' end as Scode
from __Article__ a
inner join __Transaction__ b
on a.TransId=b.Id
and a.FPId=b.FPId
where a.FPId=@Fpid
and a.InvMTId<>0
and a.InvSPId=0
and left(a.AccountId,2)in ('81','22')
group by SCode)t
where Scode=@Scode)as nvarchar),cast((select round((sum(debit*3/100)),0) from
(select sum(Debit)Debit,
case when SCode in (1,2,3,4) then '1'
when SCode in (5,6,7) then '2'
when SCode in (8,9,10) then '3'
else '4' end as Scode
from __Article__ a
inner join __Transaction__ b
on a.TransId=b.Id
and a.FPId=b.FPId
where a.FPId=@Fpid
and a.InvMTId<>0
and a.InvSPId=0
and left(a.AccountId,2)in ('81','22')
group by SCode)t
where Scode=@Scode)as nvarchar),'0','0','','','','','','','','','','7',' ','','','','','','','','','','','','','','','','', '','','','0','','0'
)TT
end
من یک استور پراسیجر دارم که میخواهم در لود فرمم ایجاد بشه ازش استفاده کنم و پس از بستن فرم دراپ بشه ممنون میشم راهنمایی بفرمایید کد استورپراسیجر را میزارم
زبان مورد استفاده من سی شارپ بوده و ویندوز فرم می باشد.
IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'dbo.TTMS_Kharid')
DROP PROCEDURE dbo.TTMS_Kharid
USE [IFS_803]
GO
/****** Object: StoredProcedure [dbo].[TTMS_Kharid] Script Date: 01/17/1398 02:51:51 ب.ظ ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create PROCEDURE [dbo].[TTMS_Kharid]
@Fpid int,
@SCODE INT
AS
BEGIN
select ROW_NUMBER()over(order by Price) as Radif,* from
(select * from
(select 0 as Sarjam,0 as IsHagholAmalKari,12 as KalaType
,(select Name from __Merchandise__ where id = b.MerchandiseId and FPId=@Fpid)KalaKhadamatName,0 as KalaCode,case when a.FactorType in (0,10) then 0 else -1 end as BargashtType,
b.AuxAmount as Price,(b.VTax-b.VCharge)as MaliatArzeshAfzoodeh,b.VCharge as AvarezArzeshAfzoodeh,'0' as SayerAvarez, '0' as TakhfifPrice,
MType+1 as HCForoushandeTypeCode,ZipCode as ForoushandePostCode,'0' as ForoushandePerCityCode,PhoneNo as ForoushandeTell,Address as ForoushandeAddress
,Name as ForoushandeName,Family as ForoushandeLastNameSherkatName,EcCode as ForoushandeEconomicNO,c.TRes as ForoushandeNationalCode
,5 as HCForoushandeType1Code,cast((select code from __AccGeo__ where id=Province)as bigint) StateCode
,cast((select code from __AccGeo__ where id=Division)as bigint) CityCode,0 as ArzType,0 as Arz_Price,0 as Arz_MaliatArzeshAfzoodeh,0 as Arz_AvarezArzeshAfzoodeh
,0 as Arz_SayerAvarez,0 as Arz_TakhfifPrice,0 as ArzBarabari_Price,0 as ArzBarabari_TakhfifPrice,0 as ArzBarabari_MaliatArzeshAfzoodeh,0 as ArzBarabari_AvarezArzeshAfzoodeh,0 as ArzBarabari_SayerAvarez,0 as MoadelRialiPrice,0 as MoadelRialiTakhfifPrice,
0 as MoadelRialiMaliatArzeshAfzoodeh,0 as MoadelRialiAvarezArzeshAfzoodeh,0 as MoadelRialiSayerAvarez,
FactorNo,dbo.__MiladiToShamsi__(a.SPDate) as FactorDate,cast(SanadNo as bigint) as SanadNo ,SanadDate,'0' as IsSent
from __SPFactor__ a
inner join __SPArticle__ b
on a.Id=b.SPId
and a.FPId=b.FPId
and a.FPId=@Fpid
and a.FactorType in (0,2,10)
inner join __Customer__ c
on a.CustomerId=c.Id
inner join
(select * from
(select a.InvSPId,b.SanadNo,dbo.__MiladiToShamsi__(b.TDate ) SanadDate,
case when SCode in (1,2,3,4) then '1'
when SCode in (5,6,7) then '2'
when SCode in (8,9,10) then '3'
else '4' end as Scode
from __Article__ a
inner join __Transaction__ b
on a.TransId=b.Id
and a.FPId=b.FPId
and b.Committed<>0
and b.SCode<>0
where b.FPId=@Fpid
and a.InvSPId<>0)T
group by InvSPId,SanadNo,SanadDate,Scode)TT
on a.Id=tt.InvSPId
and tt.Scode=@Scode)t
union all
select -1,0,'','',0,0, (select sum(debit) from
(select sum(Debit)Debit,
case when SCode in (1,2,3,4) then '1'
when SCode in (5,6,7) then '2'
when SCode in (8,9,10) then '3'
else '4' end as Scode
from __Article__ a
inner join __Transaction__ b
on a.TransId=b.Id
and a.FPId=b.FPId
where a.FPId=@Fpid
and a.InvMTId<>0
and a.InvSPId=0
and left(a.AccountId,2)in ('81','22')
group by SCode)t
where Scode=@Scode),cast((select round((sum(debit*6/100)),0) from
(select sum(Debit)Debit,
case when SCode in (1,2,3,4) then '1'
when SCode in (5,6,7) then '2'
when SCode in (8,9,10) then '3'
else '4' end as Scode
from __Article__ a
inner join __Transaction__ b
on a.TransId=b.Id
and a.FPId=b.FPId
where a.FPId=@Fpid
and a.InvMTId<>0
and a.InvSPId=0
and left(a.AccountId,2)in ('81','22')
group by SCode)t
where Scode=@Scode)as nvarchar),cast((select round((sum(debit*3/100)),0) from
(select sum(Debit)Debit,
case when SCode in (1,2,3,4) then '1'
when SCode in (5,6,7) then '2'
when SCode in (8,9,10) then '3'
else '4' end as Scode
from __Article__ a
inner join __Transaction__ b
on a.TransId=b.Id
and a.FPId=b.FPId
where a.FPId=@Fpid
and a.InvMTId<>0
and a.InvSPId=0
and left(a.AccountId,2)in ('81','22')
group by SCode)t
where Scode=@Scode)as nvarchar),'0','0','','','','','','','','','','7',' ','','','','','','','','','','','','','','','','', '','','','0','','0'
)TT
end