USE [main1]
GO

/****** Object:  StoredProcedure [dbo].[radin_scale]    Script Date: 11/17/2020 10:54:01 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO








CREATE PROCEDURE [dbo].[radin_scale]
(
@st int=0,
@scale_cod int=0,
@scale_ip nvarchar(50)='',
@scale_rx int=0,
@scale_tx int=0,
@group_id int=0,
@group_nam nvarchar(50)='',
@id int=0,
@sstr nvarchar(200)='',
@id_prc int=0,
@acc  int=0,
@regester nvarchar(130)='',

@fish_no_out nvarchar(4)='',
@tarazo_no nvarchar(3)='',
@sum_kala nvarchar(7)='',
@idcast int=0,
@idtot int=0,
@utype int=0,
@model int=0,
@err int =0 output





)

AS
declare @price1 decimal(18,3),@price2 decimal(18,3),@cod1 int,@cod2 int,@numb1 decimal(18,3),@numb2 decimal(18,3),@tim nvarchar(10),@dat nvarchar(10),@scale_id int,@fish_no int
declare @count int,@counter int,@reg nvarchar(130),@id_scale_tmp int,@count_rec int,@idgroup int,@sumkala2 decimal
declare @chk_is_kala int,@b nvarchar(700),@ut1 int,@ut2 int,@c int
BEGIN

------------------------------------------ذخیره یک ترازوی جدید

if(@st=1)
begin
insert into scales(scale_no,scale_ip,scale_rx,scale_tx,scale_groupid,model)
values (@scale_cod,@scale_ip,@scale_rx,@scale_tx,@group_id,@model)
end
----------------------------------------- نمایش ترازوهای یک گروه ترازو
if(@st=2)
begin
SELECT     scales.id, scales.scale_no, scales.scale_ip, scales.scale_tx, scales.scale_rx, scales.scale_groupid, scale_group.group_nam
FROM         scales INNER JOIN
                      scale_group ON scales.scale_groupid = scale_group.id
end
-----------------------------------------حذف یک ترازو

if(@st=3)
begin
delete from scales where id=@id

SELECT     scales.id, scales.scale_no, scales.scale_ip, scales.scale_tx, scales.scale_rx, scales.scale_groupid, scale_group.group_nam
FROM         scales INNER JOIN
                      scale_group ON scales.scale_groupid = scale_group.id
end


---------------------------------------------------گروه ترازوی جدید
if(@st=4)
begin
insert into  scale_group(group_nam)
        values(@group_nam)
end


----------------------------------------------------نمایش تمامی کالاهای یک گروه بر روی صفحه

if(@st=5)
begin

SELECT     scale_acc_tmp.id, prc.nam, scale_acc_tmp.acc_cod,scale_acc_tmp.utype,convert(decimal, prc.price)  as price
FROM         prc RIGHT OUTER JOIN
                      scale_acc_tmp ON prc.id = scale_acc_tmp.id_prc
ORDER BY scale_acc_tmp.id

end
-------------------------------------------------لیست گروهای ترازو
if(@st=6)
begin
select id,group_nam from scale_group order by group_nam
end

------------------------------------------------لیست کالاها در لیست کوچک انتخاب کالا

if(@st=7)
begin
SELECT     TOP (100) kalagroup.nam AS group_nam, prc.nam, prc.barcod, prc.id, prc.idanbar, prc.stut
FROM         prc INNER JOIN
                      kalagroup ON prc.groupid = kalagroup.id
WHERE     (prc.unam LIKE @sstr) AND (prc.idanbar = 1) AND (prc.stut = 0)
ORDER BY prc.nam
end

------------------------------------------------ثبت یک کالا
if(@st=8)
begin

update scale_acc_tmp set id_prc=@id_prc where id=@id

end
------------------------------------------------ثبت کالاهای یک گروه
if(@st=9)
begin

-------کالا با قیمت صفر
select @c=count(*) 

FROM         scale_acc_tmp INNER JOIN
prc ON scale_acc_tmp.id_prc = prc.id
WHERE     (prc.idanbar = 1)  AND (prc.price = 0)

if(@c>0)
begin
SELECT    
scale_acc_tmp.id as scale_cod,
1 as err_cod,
scale_acc_tmp.acc_cod AS Directkey,
ISNULL(REPLACE(prc.nam, NCHAR(1740), NCHAR(1610)), '') AS nam,
CONVERT(decimal, ISNULL(prc.price, 0)) AS price,
ISNULL(prc.unit, '') AS Type,
scale_acc_tmp.utype,
prc.barcod,
prc.idanbar
FROM         scale_acc_tmp INNER JOIN
prc ON scale_acc_tmp.id_prc = prc.id
WHERE     (prc.idanbar = 1)  AND (prc.price = 0)

return
end
-------کالای تکراری
set @c=0

select @c=count(*) 
FROM         scale_acc_tmp INNER JOIN
prc ON scale_acc_tmp.id_prc = prc.id
WHERE  id_prc in (
SELECT     id_prc
FROM         scale_acc_tmp
GROUP BY id_prc
HAVING      (COUNT(*) > 1)) 

set @c=isnull(@c,0)

if(@c>0)
begin


SELECT    
scale_acc_tmp.id as scale_cod, 
2 as err_cod,
scale_acc_tmp.acc_cod AS Directkey, 
ISNULL(REPLACE(prc.nam, NCHAR(1740), NCHAR(1610)), '') AS nam, 
CONVERT(decimal, ISNULL(prc.price, 0)) AS price, 
ISNULL(prc.unit, '') AS Type, 
scale_acc_tmp.utype, 
prc.barcod, 
prc.idanbar
FROM         scale_acc_tmp INNER JOIN
prc ON scale_acc_tmp.id_prc = prc.id
WHERE  id_prc in (
SELECT     id_prc
FROM         scale_acc_tmp
GROUP BY id_prc
HAVING      (COUNT(*) > 1)) 

return 
end


delete from scale_acc where idgroup=@group_id

INSERT INTO scale_acc
                      (id_prc, acc_cod, scale_cod, idgroup,send,utype)
SELECT     id_prc, id, id, @group_id AS Expr1,0,utype
FROM         scale_acc_tmp

--truncate table SADR_Item

--INSERT INTO SADR_Item
--                      (ItemClassCode, ID, PluNo, ItemCode, PluName, UnitPrice, DeleteFlag, PrintFormat, IndexBarcode, BarFormat, PluUnit)
--SELECT     0 AS Expr6, prc.id AS Expr1, scale_acc.acc_cod, scale_acc.scale_cod,left( prc.nam,100), prc.price, 0 AS Expr2, 1 AS Expr3, left(prc.barcod,50), 1 AS Expr4, 
--                      1 - scale_acc.utype AS Expr5
--FROM         scale_acc INNER JOIN
--                      prc ON scale_acc.id_prc = prc.id
--WHERE     (scale_acc.acc_cod NOT IN
--                          (SELECT     PluNo
--                             FROM         SADR_Item AS SADR_Item_1))

end


--------------------------------------------------نمایش لیست کالاهای یک گروه
if(@st=10)
begin

update dbo.scale_acc_tmp set id_prc=0,acc_cod=0,utype=0

update dbo.scale_acc_tmp set id_prc=(select id_prc from  scale_acc where idgroup=@group_id and scale_acc.scale_cod=scale_acc_tmp.id)
update dbo.scale_acc_tmp set acc_cod=(select acc_cod from  scale_acc where idgroup=@group_id and scale_acc.scale_cod=scale_acc_tmp.id)
update dbo.scale_acc_tmp set utype=isnull((select utype from  scale_acc where idgroup=@group_id and scale_acc.scale_cod=scale_acc_tmp.id),0)

update dbo.scale_acc_tmp set id_prc=0 where id_prc is null
update dbo.scale_acc_tmp set acc_cod=0 where acc_cod is null
end


--------------------------------------------------------------------------------------------------------------
if(@st=11)
begin

update scale_acc_tmp set acc_cod=@acc where id=@id

end

-----------------------------------------------------------------------------------------------------------------------

if(@st=12)
begin

select count(id) as c from  scale_acc_tmp  where acc_cod=@acc and acc_cod<>0

end

---------------------------------------------------------------------------------------------------

if(@st=13)
begin

SELECT      scale_no as ExMasterAddress, scale_ip as ExIpAddress,  scale_rx as ExReceptionPortRx,scale_tx as ExSendPortTx,'500RANGE'  as Model
FROM         scales as  Scales where scale_groupid=@group_id

SELECT       scale_ip as IpAddress, '' as Result 
FROM          scales as result where scale_groupid=@group_id

end
----------------------------ارسال دستی
if(@st=14)
begin


SELECT     scale_acc.scale_cod AS code, 
scale_acc.acc_cod AS Directkey, 
left(REPLACE((ISNULL(REPLACE(prc.nam+space(27), NCHAR(1740), NCHAR(1610)), '')), NCHAR(1705), NCHAR(1603)),27) AS Name, 
CONVERT(decimal, ISNULL(prc.price, 0)) AS price, 
ISNULL(prc.unit, '') AS Type, 
scale_acc.utype, 
ltrim(rtrim(prc.barcod)) as barcod, 
prc.idanbar, 
scale_acc.idgroup AS scale_groupid
FROM         scale_acc INNER JOIN
                      prc ON scale_acc.id_prc = prc.id
WHERE     (prc.idanbar = 1) AND (scale_acc.idgroup = @group_id)  and scale_acc.scale_cod<=1000 order by scale_acc.scale_cod

-------کالا با قیمت صفر
SELECT     
scale_acc.scale_cod ,
 scale_acc.acc_cod AS Directkey,
  ISNULL(REPLACE(prc.nam, NCHAR(1740), NCHAR(1610)), '') AS nam,
 CONVERT(decimal,ISNULL(prc.price, 0)) AS price, 
 ISNULL(prc.unit, '') AS Type, 
 scale_acc.utype, prc.barcod, 
 prc.idanbar, 
 scale_acc.idgroup AS scale_groupid

FROM         scale_acc INNER JOIN
                      prc ON scale_acc.id_prc = prc.id
WHERE     (prc.idanbar = 1) AND (scale_acc.idgroup = @group_id) AND (prc.price = 0) and scale_acc.scale_cod<=1000


-------کالای تکراری
SELECT     scale_acc.scale_cod, scale_acc.acc_cod AS Directkey, ISNULL(REPLACE(prc.nam, NCHAR(1740), NCHAR(1610)), '') AS nam, CONVERT(decimal, ISNULL(prc.price, 0)) 
                      AS price, ISNULL(prc.unit, '') AS Type, scale_acc.utype, prc.barcod, prc.idanbar, scale_acc.idgroup AS scale_groupid
FROM         scale_acc INNER JOIN
                      prc ON scale_acc.id_prc = prc.id
WHERE     (scale_acc.id_prc IN
                          (SELECT     id_prc
                             FROM         scale_acc AS scale_acc_1
                             WHERE     (idgroup = @group_id)
                             GROUP BY id_prc
                             HAVING      (COUNT(id) > 1))) AND (scale_acc.idgroup = @group_id) AND (scale_acc.scale_cod <= 1000)



end
-------------------------------------
if(@st=15)
begin

SELECT      scale_no as ExMasterAddress, scale_ip as ExIpAddress,  scale_rx as ExReceptionPortRx,scale_tx as ExSendPortTx
FROM         scales   Scales
end
-------------------------------------
if(@st=16)
begin

insert into scale_regester(reg)
values(@regester)
end

------------------------------------------------حذف کالا از گروه

if(@st=17)
begin 

update scale_acc_tmp set id_prc=0,acc_cod=0,utype=0 where id=@id

end
--------------------------------------------------------------------------------------
if(@st=18)

begin
print 'dibal'

end

--------------------------------------------------------------------------dibal show_prc_on_wait
if(@st=19)
begin
SELECT     prc.nam AS kala_nam, CONVERT(float, scale_get_kala_tmp.numb / 100) AS numb, scale_get_kala_tmp.price, scale_get_kala_tmp.tim, scale_get_kala_tmp.dat, 
                      scale_get_kala_tmp.scale_id AS scale_no, scale_get_kala_tmp.fish_no, prc.idanbar
FROM         scale_get_kala_tmp INNER JOIN
                      prc ON scale_get_kala_tmp.cod = prc.id

end
------------------------------------------------------------------------------------------------------------------

if(@st=20)
begin

SELECT     scale_acc_tmp.id, prc.nam, scale_acc_tmp.acc_cod
FROM         prc INNER JOIN
                      scale_acc_tmp ON prc.id = scale_acc_tmp.id_prc
ORDER BY scale_acc_tmp.id

end
----------------------------------------------------------------------------------------------------------------

if(@st=21)
begin 
 --insert into t2(item) values(@acc)
select @id= id_prc from  dbo.scale_acc where scale_cod=@acc
set @id=isnull(@id,0)
select @id as idk 
end
-----------------------------------------------------------------------------------------------------------------------

if(@st=22)
begin 

select @id= id_prc from  dbo.scale_acc where scale_cod=@acc

set @id=isnull(@id,0)

select @b=unam from fn_prc() where id=@id 

set @b=isnull(@b,'')

select @b as barcod


end

--------------------------------------------------------------بررسی نیاز به ارسال اتوماتیک
if (@st=23)
begin
SELECT     COUNT(scale_acc.id) AS countid
FROM         scale_acc INNER JOIN
                      prc ON scale_acc.id_prc = prc.id
WHERE     (ABS(scale_acc.s_price - prc.price) > 1) AND (scale_acc.idgroup = @group_id)

end
----------------------------------------------------------------ارسال اتوماتیک
if(@st=24)
begin

SELECT     scale_acc.scale_cod AS code, scale_acc.acc_cod AS Directkey, LEFT(REPLACE(ISNULL(REPLACE(prc.nam + SPACE(27), NCHAR(1740), NCHAR(1610)), ''), 
                      NCHAR(1705), NCHAR(1603)), 27) AS Name, CONVERT(decimal, ISNULL(prc.price, 0)) AS price, ISNULL(prc.unit, '') AS Type, scale_acc.utype, 
                      LTRIM(RTRIM(prc.barcod)) AS barcod, prc.idanbar, scale_acc.idgroup AS scale_groupid
FROM         scale_acc INNER JOIN
                      prc ON scale_acc.id_prc = prc.id
WHERE     (prc.idanbar = 1) AND (scale_acc.idgroup = @group_id) AND (scale_acc.scale_cod <= 1000)
ORDER BY code





-------کالا با قیمت صفر
SELECT     
scale_acc.scale_cod ,
 scale_acc.acc_cod AS Directkey,
  ISNULL(REPLACE(prc.nam, NCHAR(1740), NCHAR(1610)), '') AS nam,
 CONVERT(decimal,ISNULL(prc.price, 0)) AS price, 
 ISNULL(prc.unit, '') AS Type, 
 scale_acc.utype,
  prc.barcod, 
 prc.idanbar, 
 scale_acc.idgroup AS scale_groupid

FROM         scale_acc INNER JOIN
                      prc ON scale_acc.id_prc = prc.id
WHERE     (prc.idanbar = 1) AND (scale_acc.idgroup = @group_id) AND (prc.price = 0) and scale_acc.scale_cod<=1000


-------کالای تکراری
SELECT     scale_acc.scale_cod, scale_acc.acc_cod AS Directkey, ISNULL(REPLACE(prc.nam, NCHAR(1740), NCHAR(1610)), '') AS nam, CONVERT(decimal, ISNULL(prc.price, 0)) 
                      AS price, ISNULL(prc.unit, '') AS Type, scale_acc.utype, prc.barcod, prc.idanbar, scale_acc.idgroup AS scale_groupid
FROM         scale_acc INNER JOIN
                      prc ON scale_acc.id_prc = prc.id
WHERE     (scale_acc.id_prc IN
                          (SELECT     id_prc
                             FROM         scale_acc AS scale_acc_1
                             WHERE     (idgroup = @group_id)
                             GROUP BY id_prc
                             HAVING      (COUNT(id) > 1))) AND (scale_acc.idgroup = @group_id) AND (scale_acc.scale_cod <= 1000)






end

------------------------------------------------------------------آپ دیت لیست کالاهای یک گروه پس از ارسال به ترازو
if(@st=25)
begin

update scale_acc set
 s_price=isnull((select price from prc where prc.id=scale_acc.id_prc and idanbar=1),0) 
 where idgroup = @group_id
end

---------------------------------------------------------------------------------------------------------------------
if(@st=26)
begin
update scale_acc set s_price=0 where s_price is null
select id,group_nam from scale_group order by group_nam
end

-----------------------------------------------------------------------------------------------------------------------

if(@st=27)
begin
update  scale_acc_tmp set utype=@acc where id=@id
end


-------------------------------------------------------------------------------------------


if(@st=28)
begin


---6000seris
SELECT     id, scale_no, scale_ip, scale_tx, scale_rx, scale_groupid, model
FROM         scales
WHERE     (model = 0) AND (scale_groupid = @group_id)
ORDER BY scale_no

---MKII
SELECT     scales.*
FROM         scales
WHERE     (scales.model=1 and  scales.scale_groupid = @group_id)
ORDER BY scale_no

end
----------------------------------------------------------ایجاد لیست ترازوها در زمان باز شدن برنامه
if(@st=29)
begin

SELECT     scales.*
FROM         scales
WHERE     (scales.model=1 ) order by scale_no 

end

-----------------------------------------------------چاپ
if(@st=30)
begin

SELECT     scale_acc.acc_cod ,  ISNULL(REPLACE(prc.nam, NCHAR(1740), NCHAR(1610)), '') AS nam, CONVERT(decimal, 
                      ISNULL(prc.price, 0)) AS price,  prc.barcod
FROM         scale_acc INNER JOIN
                      prc ON scale_acc.id_prc = prc.id
WHERE     (scale_acc.idgroup = @group_id) AND (prc.idanbar = 1) and id_prc<>0 and acc_cod<=40 


SELECT     scale_acc.acc_cod ,  ISNULL(REPLACE(prc.nam, NCHAR(1740), NCHAR(1610)), '') AS nam, CONVERT(decimal, 
                      ISNULL(prc.price, 0)) AS price,  prc.barcod
FROM         scale_acc INNER JOIN
                      prc ON scale_acc.id_prc = prc.id
WHERE     (scale_acc.idgroup = @group_id) AND (prc.idanbar = 1) and id_prc<>0 and acc_cod>40  and acc_cod<=100 


SELECT     scale_acc.acc_cod ,  ISNULL(REPLACE(prc.nam, NCHAR(1740), NCHAR(1610)), '') AS nam, CONVERT(decimal, 
                      ISNULL(prc.price, 0)) AS price,  prc.barcod
FROM         scale_acc INNER JOIN
                      prc ON scale_acc.id_prc = prc.id
WHERE     (scale_acc.idgroup = @group_id) AND (prc.idanbar = 1) and id_prc<>0 and acc_cod<=60

end

-----------------------------------------------------بررسی وضعیت بارکدها
if(@st=31)
begin


SELECT     scale_acc.scale_cod , scale_acc.acc_cod AS Directkey, ISNULL(REPLACE(prc.nam, NCHAR(1740), NCHAR(1610)), '') AS nam, CONVERT(decimal, 
                      ISNULL(prc.price, 0)) AS price, ISNULL(prc.unit, '') AS Type, scale_acc.utype, prc.barcod, prc.idanbar, scale_acc.idgroup AS scale_groupid
FROM         scales INNER JOIN
                      scale_acc ON scales.scale_groupid = scale_acc.idgroup INNER JOIN
                      prc ON scale_acc.id_prc = prc.id
WHERE     (prc.idanbar = 1) AND (scale_acc.idgroup = @group_id) and isnull(prc.barcod,'')=''



SELECT    @c=count(prc.id)  FROM         scales INNER JOIN
                      scale_acc ON scales.scale_groupid = scale_acc.idgroup INNER JOIN
                      prc ON scale_acc.id_prc = prc.id
WHERE     (prc.idanbar = 1) AND (scale_acc.idgroup = @group_id) and isnull(prc.barcod,'')=''

set @c=isnull(@c,0)


if(@c=0)
begin
SELECT     scale_acc.scale_cod , scale_acc.acc_cod AS Directkey, ISNULL(REPLACE(prc.nam, NCHAR(1740), NCHAR(1610)), '') AS nam, CONVERT(decimal, 
                      ISNULL(prc.price, 0)) AS price, ISNULL(prc.unit, '') AS Type, scale_acc.utype, prc.barcod, prc.idanbar, scale_acc.idgroup AS scale_groupid
FROM         scales INNER JOIN
                      scale_acc ON scales.scale_groupid = scale_acc.idgroup INNER JOIN
                      prc ON scale_acc.id_prc = prc.id
WHERE     (prc.idanbar = 1) AND (scale_acc.idgroup = @group_id) and isnumeric( isnull(prc.barcod,''))=0
end
else
begin
return
end

set @c=0

SELECT    @c=count(prc.id)  FROM  scales INNER JOIN
                      scale_acc ON scales.scale_groupid = scale_acc.idgroup INNER JOIN
                      prc ON scale_acc.id_prc = prc.id
WHERE     (prc.idanbar = 1) AND (scale_acc.idgroup = @group_id) and isnumeric(isnull(prc.barcod,''))=0

set @c=isnull(@c,0)

if(@c=0)
begin

SELECT     scale_acc.scale_cod , scale_acc.acc_cod AS Directkey, ISNULL(REPLACE(prc.nam, NCHAR(1740), NCHAR(1610)), '') AS nam, CONVERT(decimal, 
                      ISNULL(prc.price, 0)) AS price, ISNULL(prc.unit, '') AS Type, scale_acc.utype, prc.barcod, prc.idanbar, scale_acc.idgroup AS scale_groupid
FROM         scales INNER JOIN
                      scale_acc ON scales.scale_groupid = scale_acc.idgroup INNER JOIN
                      prc ON scale_acc.id_prc = prc.id
WHERE     (prc.idanbar = 1) AND (scale_acc.idgroup = @group_id) and convert(decimal,prc.barcod)<0 and prc.id=-1



end


end

------------------------------------------------------------------چاپ لیست 8 سانتی
if(@st=32)
begin

SELECT     scale_acc.acc_cod, ISNULL(REPLACE(prc.nam, NCHAR(1740), NCHAR(1610)), '') AS nam, CONVERT(decimal, ISNULL(prc.price, 0)) AS price, prc.barcod
FROM         scale_acc INNER JOIN
                      prc ON scale_acc.id_prc = prc.id
WHERE     (scale_acc.idgroup = @group_id) AND (prc.idanbar = 1) AND (scale_acc.id_prc <> 0)
ORDER BY scale_acc.acc_cod


end
--------------------------------------------------------------------

end
GO


