---ͳ������ڼ�ɱ�����ë��


--EXEC [Gold_SalesAn] '2023-07-02'

--IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Gold_SalesAn]') AND type in (N'P', N'PC'))
--DROP PROCEDURE [dbo].[Gold_SalesAn]
--GO

--create PROCEDURE [dbo].[Gold_SalesAn]
--	@begdate date	
--	with encryption
--AS
--set nocount on

declare @begdate CHAR(10)
set @begdate='2023-07-02'


declare @YEAR CHAR(4)
declare @MONTH  CHAR(2)

declare @FirstDay date
declare @LastDay date
declare @OrgId int
declare @AMT decimal
declare @POAMT decimal

set @YEAR=DATEPART(yyyy,@begdate)
set @MONTH=DATEPART(MM,@begdate)
set @OrgId=1

set @FirstDay=@YEAR+'-'+@MONTH+'-01'
select @LastDay=DATEADD(dd, -1, DATEADD(mm, DATEDIFF(mm, 0, @FirstDay)+1, 0))

--set @LastDay=@YEAR+'-'+@MONTH+'-02'
--select @FirstDay  AS firstday, @LastDay AS lastday;

select 
@YEAR+@MONTH as �ڼ�
,SUM(CASE WHEN (oue.F_Gold_Decimal is not null and oue.FREALQTY is not null) THEN oue.F_Gold_Decimal*oue.FREALQTY ELSE 0 END) AS ��۶�
,SUM(FALLAMOUNT) ��˰�ϼ�
,SUM(CASE WHEN (ouef.FCOSTPRICE is not null and oue.FREALQTY is not null) THEN ouef.FCOSTPRICE*oue.FREALQTY ELSE 0 END) AS ʵ�ʳɱ�
,SUM(ouef.FCOSTPRICE) AS �ɱ����ۺϼ�

--FStockOrgId
--,ou.FBILLNO ���ⵥ��
--,CONVERT(varchar(230), ou.FDATE, 23) ����
--,oue.FSEQ ���
--,oue.FREALQTY ʵ�����
--,ouef.FTAXPRICE ��˰����
--,F_Gold_Decimal ʵ�ʵ���
----,FBILLALLAMOUNT ��˰�ϼ�
--,oue.F_Gold_Decimal*oue.FREALQTY ��۶�
--,ouef.FCOSTPRICE  �ɱ���
--,(ouef.FCOSTPRICE*oue.FREALQTY) ʵ�ʳɱ�
--,(oue.F_Gold_Decimal-ouef.FCOSTPRICE)*oue.FREALQTY �
--,CASE
-- WHEN ouef.FTAXPRICE<>0 then (oue.F_Gold_Decimal-ouef.FCOSTPRICE)/ouef.FTAXPRICE
--else  0 end 
--as ���
into #t1
 from T_SAL_OUTSTOCK ou
left join T_SAL_OUTSTOCKENTRY oue on ou.fid=oue.FID --�����ϸ
left join T_SAL_OUTSTOCKENTRY_F ouef on oue.FENTRYID=ouef.FENTRYID--����

where ou.FDOCUMENTSTATUS='C'
and ou.FCANCELSTATUS='A'
and ou.FStockOrgId=@OrgId
and ou.FDATE between @FirstDay and @LastDay
--order by ou.FDATE ,ou.FBILLNO asc




SELECT B.FDIMENSIONID FORGID
,M.FMATERIALID,M.FNUMBER,ST.FSTOCKID
,O.FYEAR,O.FPERIOD
,SUM(CASE WHEN B.FENDINITKEY=1 THEN B.FAMOUNT ELSE 0 END) AS ��ĩ���
into #t2
FROM T_HS_BALANCE_H B  --��ʷ������Ϣ

LEFT JOIN T_HS_OUTACCTG O ON B.FID=O.FID --��������--ȡ����
LEFT JOIN T_HS_STOCKDIMENSION S ON B.FDIMEENTRYID=S.FENTRYID  --���
LEFT JOIN T_BD_MATERIAL M ON S.FMASTERID=M.FMASTERID AND B.FDIMENSIONID=M.FUSEORGID
LEFT JOIN T_BD_STOCK AS ST ON ST.FSTOCKID =S.FSTOCKID

where O.FYEAR=@YEAR
and O.FPERIOD=@MONTH
and B.FDIMENSIONID=@OrgId


GROUP BY B.FDIMENSIONID,M.FMATERIALID,O.FYEAR,O.FPERIOD,M.FNUMBER ,ST.FSTOCKID

select @AMT=sum(��ĩ���) from #t2
where ��ĩ���<>0

drop table #t2



select
--cast(sum(FSTOCKBASEQTY) as decimal(23,2)) 
--por.FREMAINSTOCKINQTY
--,poef.FTAXPRICE
@POAMT=SUM(CASE WHEN (por.FREMAINSTOCKINQTY is not null and poef.FTAXPRICE is not null) THEN por.FREMAINSTOCKINQTY*poef.FTAXPRICE ELSE 0 END)
--,CASE WHEN (por.FREMAINSTOCKINQTY is not null and poef.FTAXPRICE is not null) THEN por.FREMAINSTOCKINQTY*poef.FTAXPRICE ELSE 0 END �ɹ���;
from t_PUR_POOrder po 
left join t_PUR_POOrderEntry poe on po.fid=poe.FID ---�����ϸ
left join T_PUR_POORDERENTRY_F poef on poef.FENTRYID=poe.FENTRYID ---����
left join T_PUR_POORDERENTRY_R por on poe.FENTRYID=por.FENTRYID
where
po.FDOCUMENTSTATUS = 'C'--�����
and po.FCLOSESTATUS='A' --δ�ر�
and po.FCANCELSTATUS<> 'B' --δȡ�
and po.FPURCHASEORGID=1--�ɹ���֯
and po.F_Gold_Assistant<>'643ba62e49d104'--���
and po.F_Gold_Assistant<>''--������״̬
and po.FDATE between @FirstDay and @LastDay





select *
, (��۶�-ʵ�ʳɱ�) as ë��
,CASE
 WHEN ��۶�<>0 then (��۶�-ʵ�ʳɱ�)/��۶�
else  0 end 
as ���

,@AMT �����
,@POAMT �ɹ���;


from #t1



drop table #t1