


alter procedure [dbo].[_00000_r_repetitious_lnum_service_in_rec_hdr]
/*
--update14030624  alichid@gmail.com www.needsoft.ir 

گزارش درمان های  تکراری



_00000_r_repetitious_lnum_service_in_rec_hdr
'1','2','3','4','5','6','7',
'1300/07/02','1500/07/02','','',
'','','','','','','','','','','606',
'','','','','','','','','','','','',
'','','','' ,
'1','999000',
'','',''




rollup sample
_5657_r_implant_select_sum_on_ck_imp_brand_size

Fn_is_repetitious_lnum_service_in_reception_in_all






*/
(
@token nvarchar(50), -- 1
@lucp int, -- 2
@shift int, -- 3
@branch int, -- 4
@machine_nm nvarchar(50),-- 5
@machine_ip nvarchar(50),-- 6
@machine_id nvarchar(50),-- 7
@fdate char(10),
@tdate char(10),
@ftime datetime,
@ttime datetime,
@cp int,
@cp_expert int,
@cs int,
@c_pay int,
@c_bnk int,
@c_insura int,
@c_insura_sub int,
@ins_cp int,
@edi_cp int,
@del_cp int,
@descr nvarchar(50),
@cc int,--  بخش
@ccs int,-- زیر بخش
@cp_technician int,  --تکنسین
@c_kind int,  --  نوع عمومی تخصصی
@c_shift int,--  شیفت
@c_branch int,-- شعبه
@cp_str varchar(500),  --  چند شخص
@cp_expert_str varchar(500),  --  چند پزشک
@cs_str varchar(500),  ---  چند سرویس
@c_insura_str varchar(500), -- چند بیمه
@cc_str varchar(500), --- چند بخش
@cp_technician_str varchar(500),  -- چند تکنسین
@c_shift_str varchar(500), -- چند شیفت
@c_branch_str varchar(500),  --  چند شعبه
@cp_den_lab int,
@c_special_filter int,
@from_price money,
@to_price money,
@c_special_date int=0, 
@c_special_insura int=0 ,
@memo_text nvarchar(1000)='' -- متنی که اینتر دارد پیش فرض خالی 
)


AS
BEGIN




DECLARE 
@allow bit = 1,
@s_mess nvarchar(500)='',
@todaye char(10)=dbo.CalculatePersianDate(CONVERT(VARCHAR(10), getdate(), 111)),
@SP_NAME varchar(100)=OBJECT_NAME(@@PROCID), 
@SP_CODE varchar(10)=SUBSTRING(OBJECT_NAME(@@PROCID),2,4)


set @descr=ltrim(rtrim(@descr))

print '@descr='+@descr


IF len(@descr)=0
BEGIN
 SET @allow=0
 SET @s_mess=@s_mess+'  لطفا شماره کد سی ار ام  در فیلد شرح مشخص شود   '
END


IF ISNUMERIC(@descr)=0
BEGIN
 SET @allow=0
 SET @s_mess=@s_mess+' معتبر نیست  کد عددی در فیلد شرح    '
END


DECLARE @id_crm int=CAST(ISNULL(@descr, 0) AS numeric)
print '@id_crm='+ltrim(@id_crm)


/*
SET @fdate=ltrim(RTRIM(@fdate))
SET @tdate=ltrim(RTRIM(@tdate))

IF (LEN(@fdate)=0) OR (LEN(@tdate)=0)
BEGIN
 SET @fdate='0000/00/00'
 SET @tdate='9999/99/99'
END


PRINT '@fdate='+@fdate
PRINT '@tdate='+@tdate

IF @to_price=0 
BEGIN
 --SET @to_price='0000/00/00'
 SET @to_price=999999999999
END

PRINT '@from_price='+format(@from_price,'N0')
PRINT '@to_price='+format(@to_price,'N0')
*/









PRINT ' Finall ...  '
IF (@allow=1)
BEGIN


declare @vt_a table
(
--vo int identity,
cp int,
cs int,
lnum nvarchar(200),
n_count int
)
------------------
declare @vt_b table
(
vo int identity,
cp int,
cs int,
lnum nvarchar(200),
n_count int
)
-------------------------
;WITH CTE_rec_hdr
AS 
( 
SELECT 
id_reception,id_hdr,
(SELECT cp FROM reception_hdr  WHERE id_hdr = re.id_hdr)   AS cp,
(SELECT cp_expert FROM reception_hdr  WHERE id_hdr = re.id_hdr)   AS cp_expert,
(SELECT sdate FROM reception_hdr  WHERE id_hdr = re.id_hdr)   AS sdate,
cs,
c_insura,
c_insura_sub,
onum,LNUM
FROM reception re 
WHERE (del = 0 OR del IS NULL) AND (canceled=0 or canceled is null)
)
insert into @vt_a
SELECT	
cp,
cs,
LNUM,
COUNT(*) as n_count
FROM CTE_rec_hdr 
WHERE 1>0
AND (sdate between @fdate and @tdate)
AND cs not in (238,14535,14538,236)--- برخی در مانهای ویزیت گرافی و غیره نباشد

GROUP BY ROLLUP(cp,cs,LNUM)
order by cp,cs,LNUM 
-------------------------------------------		
insert into @vt_b
select * from @vt_a where (n_count>1)  and (cs is not null) order by cp,cs,lnum
-----------------------
select
vo, 
cp ,
(select np from per where cp=VB.cp) as np,
cs ,
(select ns from ser where cs=VB.cs) as ns,
lnum,
n_count 
from @vt_b VB
order by vo
-----------------------------








/*

select 
vo,
cp,
np ,
mobil,
FORMAT(cost,'N0') AS cost,
FORMAT(payment,'N0') AS payment,
last_date

from @vt_b order by vo

*/




			

END
ELSE
SELECT 0 AS s_result,@s_mess+char(13)+@SP_CODE AS s_message,'' AS s_param
END

/*

WITH CTE_sum_onum_reception_hdr
AS 
( 
SELECT 
id_reception,id_hdr,
(SELECT cp FROM reception_hdr  WHERE id_hdr = re.id_hdr)   AS cp,
(SELECT sdate FROM reception_hdr  WHERE id_hdr = re.id_hdr)   AS sdate,
(SELECT cc FROM ser  WHERE cs = re.cs)   AS cc,
(SELECT ccs FROM ser  WHERE cs = re.cs)   AS ccs,
cs,
c_insura,
c_insura_sub,
onum,LNUM
FROM reception re 
WHERE (del = 0 OR del IS NULL) AND (canceled=0 or canceled is null)
)
SELECT	
@count_cs_for_LNUM = ISNULL(count(*),0)
--@min_sdate=ISNULL(MIN(sdate),''),
--@max_sdate=ISNULL(MAX(sdate),'') 
FROM CTE_sum_onum_reception_hdr 
WHERE  (id_reception<>@id_reception)-- بجز رکورد در حال ویرایش
and (cp = @cp) and  (LNUM like '%'+LTRIM(@LNUM)+'%')



SELECT	
@count_cs_for_LNUM = ISNULL(count(*),0)
--@min_sdate=ISNULL(MIN(sdate),''),
--=ISNULL(MAX(sdate),'') 
FROM reception 
WHERE  (id_reception<>@id_reception) and (del = 0 OR del IS NULL) AND (canceled=0 or canceled is null) 
and (cp = @cp)  and (LNUM like '%'+LTRIM(@LNUM)+'%')




*/


