CREATE PROC Ap_Proc_Alert
--参数
@iType 		TINYINT=0,	--报警类型：0：信用期，1：折扣期
@iDays 		INT=0,		--提前期
@dCurDate	DATETIME,	--业务日期
@cDwCode1 	VARCHAR(30)='',
@cDwCode2 	VARCHAR(30)='ZZZZZZZZZZZZZZZZZZZZ',
				--客户范围：@cDwCode1--@cDwCode2
@cVouchClass 	VARCHAR(2)='',	--单据种类：销售发票：'2',采购发票：'0',应收单：'R'
,应付单：'P'
@cVouchType 	VARCHAR(2)='',	--单据类型
@cVouchID1 	VARCHAR(30)='',
@cVouchID2 	VARCHAR(30)='',
				--单据编码范围：@cVouchID1--@cVouchID2
@dVouchDate1 	DATETIME='1900-1-1',
@dVouchDate2 	DATETIME='3000-1-1',
				--单据日期范围：@dVouchDate1--@dVouchDate2
@dRegDate1 	DATETIME='1900-1-1',
@dRegDate2 	DATETIME='3000-1-1',
				--审核日期范围：@dRegDate1--@dRegDate2
@cExchName	VARCHAR(8)='',	--币种
@iAmount1	MONEY=NULL,
@iAmount2	MONEY=NULL,
				--金额范围(原币)：@iAmount1--@iAmount2
@iRAmount1	MONEY=NULL,
@iRAmount2	MONEY=NULL,
				--余额范围(原币)：@iRAmount1--@iRAmount2
@cDepCode	VARCHAR(12)='',	--部门编码
@cPersonCode	VARCHAR(8)='',	--业务员编码
@cCode		VARCHAR(15)='',	--科目编码
@cItemCode	VARCHAR(20)='',	--项目编码
@iCheck		TINYINT=3,	--审核标志：包含已审核：1，包含未审核：2(用位运算&)
@iBill		TINYINT=3,	--制单标志：包含已制单：1，包含未制单：2(用位运算&)
@cFlag		VARCHAR(2)='AR'
AS
IF @cDwCode1='' SET @cDwCode1=CHAR(1)
IF @cFlag='AR'
--应收
SELECT 
	cCoVouchType As cVouchType,Max(cCoTypeName) As cTypeName,cCoVouchID As cVouchID
,Min(dVouchDate) As dVouchDate,
	cDwCode,Max(cDwName) As cDwName,Max(cDepName) As cDepName,Max(cPersonName) As c
PersonName,
	Max(cPayName) As cPayName,Max(cItem_Class) As cItem_Class,Max(cItemCode) As cIt
emCode,Max(dExpireDate) As dExpireDate,
	max(cExch_Name) As cExch_Name,Max(iExchRate) As iExchRate,sum(iDAmount_f) As iA
mount_f,
	sum(iDAmount) As iAmount,sum(iDAmount_f-iCAmount_f) As iRAmount_f,sum(iDAmount-
iCAmount) As iRAmount,
	Max(CASE WHEN cProcStyle=cCoVouchType Or cProcStyle='BZ' THEN cDigest Else '' E
nd) As cDigest,Max(iCreLine) as iCreLine
FROM 
	Ap_DetailCust 
WHERE 
	iflag<3 And (cCoVouchType Like'2%' OR cCoVouchType Like'R%') AND cDwCode BETWEE
N @cDwCode1 AND @cDwCode2 And cCoVouchType Like @cVouchClass+'%'
	AND (cCoVouchType=@cVouchType OR @cVouchType='') And (cCoVouchID>=@cVouchID1 OR
 @cVouchID1='')
	AND (cCoVouchID<=@cVouchID2 OR @cVouchID2='') AND (@cExchName='' OR cExch_Name=
@cExchName)
GROUP BY 
	cDwCode,cCoVouchType,cCoVouchID
HAVING 
	sum(iDAmount_f-iCAmount_f)<>0 and CASE WHEN @iType=0 THEN Max(dExpireDate) ELSE
 Max(dFADate) END-@iDays<=@dCurDate
	And max(dVouchDate) BETWEEN @dVouchDate1 AND @dVouchDate2 And Max(CASE WHEN cPr
ocStyle=cCoVouchType THEN dRegDate ELSE 0 END)
	BETWEEN @dRegDate1 AND @dRegDate2 AND (@iAmount1 Is Null OR sum(iDAmount_f)>=@i
Amount1) And (@iAmount2 Is Null OR sum(iDAmount_f)<=@iAmount2)
	AND (@iRAmount1 Is Null OR sum(iDAmount_f-iCAmount_f)>=@iRAmount1) And (@iRAmou
nt2 Is Null OR sum(iDAmount_f-iCAmount_f)<=@iRAmount2)
	AND (@cDepCode='' OR Max(cDeptCode) Like @cDepCode+'%') And (@cPersonCode='' OR
 Max(cPerson)=@cPersonCode) And (@cCode='' OR Max(cCode) Like @cCode+'%')
	AND (@cItemCode='' OR Max(cItemCode)=@cItemCode) And @iCheck & 1<>0 AND Min(CAS
E WHEN cProcStyle=cCoVouchType AND cPZID Is not Null THEN 1 ELSE 2 END) & @iBill<
>0
UNION ALL
SELECT 	cVouchType,Max(cTypeName),cVouchID,Min(dVouchDate),
	cDwCode,Max(cDwName),Max(cDepName),Max(cPersonName),
	Max(cPayName),'','',Max(dExpireDate),
	max(cExch_Name),Max(iExchRate),sum(iAmount_f),
	sum(iAmount),sum(iAmount_f-iFmount_f),sum(iAmount-iFmount),Max(cDigest),Max(iCr
eLine) as iCreLine
FROM
	Ap_SalBillCust
WHERE
	IsNull(cInvalider,'')='' And IsNull(cCheckMan,'')='' And cDwCode BETWEEN @cDwCo
de1 AND @cDwCode2 
	And cVouchType Like @cVouchClass+'%' And (cVouchType=@cVouchType OR @cVouchType
='') 
	And (cVouchID>=@cVouchID1 OR @cVouchID1='') AND (cVouchID<=@cVouchID2 OR @cVouc
hID2='') AND (@cExchName='' OR cExch_Name=@cExchName)
GROUP BY
	cDwCode,cVouchType,cVouchID
HAVING
	sum(iAmount_f-iFmount)<>0 and CASE WHEN @iType=0 THEN Max(dExpireDate) ELSE Max
(dFADate) END-@iDays<=@dCurDate
	And max(dVouchDate) BETWEEN @dVouchDate1 AND @dVouchDate2 AND (@iAmount1 Is Nul
l OR sum(iAmount_f)>=@iAmount1) And (@iAmount2 Is Null OR sum(iAmount_f)<=@iAmoun
t2)
	AND (@iRAmount1 Is Null OR sum(iAmount_f-iFmount_f)>=@iRAmount1) And (@iRAmount
2 Is Null OR sum(iAmount_f-iFmount)<=@iRAmount2)
	AND (@cDepCode='' OR Max(cDeptCode) Like @cDepCode+'%') And (@cPersonCode='' OR
 Max(cPerson)=@cPersonCode)
	And @iCheck & 2<>0
UNION ALL
SELECT 	cVouchType,Max(cTypeName),cVouchID,Min(dVouchDate),
	cDwCode,Max(cDwName),Max(cDepName),Max(cPersonName),
	Max(cPayName),'','',Max(dExpireDate),
	max(cExch_Name),Max(iExchRate),sum(iAmount_f),
	sum(iAmount),sum(iRAmount_f),sum(iRAmount),Max(cDigest),Max(iCreLine) as iCreLi
ne
FROM
	Ap_VouchCust
WHERE
	IsNull(cCheckMan,'')='' And cDwCode BETWEEN @cDwCode1 AND @cDwCode2 
	And cVouchType Like @cVouchClass+'%' And (cVouchType=@cVouchType OR @cVouchType
='') 
	And (cVouchID>=@cVouchID1 OR @cVouchID1='') AND (cVouchID<=@cVouchID2 OR @cVouc
hID2='')
 	AND (@cExchName='' OR cExch_Name=@cExchName)
GROUP BY
	cDwCode,cVouchType,cVouchID
HAVING
	sum(iRAmount_f)<>0 and CASE WHEN @iType=0 THEN Max(dExpireDate) ELSE Max(dFADat
e) END-@iDays<=@dCurDate
	And max(dVouchDate) BETWEEN @dVouchDate1 AND @dVouchDate2 AND (@iAmount1 Is Nul
l OR sum(iAmount_f)>=@iAmount1) And (@iAmount2 Is Null OR sum(iAmount_f)<=@iAmoun
t2)
	AND (@iRAmount1 Is Null OR sum(iRAmount_f)>=@iRAmount1) And (@iRAmount2 Is Null
 OR sum(iRAmount_f)<=@iRAmount2)
	AND (@cDepCode='' OR Max(cDeptCode) Like @cDepCode+'%') And (@cPersonCode='' OR
 Max(cPerson)=@cPersonCode) And (@cCode='' OR Max(cCode) Like @cCode+'%')
	And @iCheck & 2<>0
ORDER BY 
	cVouchType,dVouchDate,cVouchID,cDwCode
ELSE
--应付
SELECT 
	cCoVouchType As cVouchType,Max(cCoTypeName) As cTypeName,cCoVouchID As cVouchID
,Min(dVouchDate) As dVouchDate,
	cDwCode,Max(cDwName) As cDwName,Max(cDepName) As cDepName,Max(cPersonName) As c
PersonName,
	Max(cPayName) As cPayName,Max(cItem_Class) As cItem_Class,Max(cItemCode) As cIt
emCode,Max(dExpireDate) As dExpireDate,
	max(cExch_Name) As cExch_Name,Max(iExchRate) As iExchRate,sum(iCAmount_f) As iA
mount_f,
	sum(iCAmount) As iAmount,sum(iCAmount_f-iDAmount_f) As iRAmount_f,sum(iCAmount-
iDAmount) As iRAmount,
	Max(CASE WHEN cProcStyle=cCoVouchType OR cProcStyle='BZ' THEN cDigest Else '' E
nd) As cDigest,Max(iCreLine) as iCreLine
FROM 
	Ap_DetailVend
WHERE 
	iflag<3 And (cCoVouchType Like'0%' OR cCoVouchType Like'P%') AND cDwCode BETWEE
N @cDwCode1 AND @cDwCode2 And cCoVouchType Like @cVouchClass+'%'
	AND (cCoVouchType=@cVouchType OR @cVouchType='') And (cCoVouchID>=@cVouchID1 OR
 @cVouchID1='')
	AND (cCoVouchID<=@cVouchID2 OR @cVouchID2='') AND (@cExchName='' OR cExch_Name=
@cExchName)
GROUP BY 
	cDwCode,cCoVouchType,cCoVouchID
HAVING 
	sum(iCAmount_f-iDAmount_f)<>0 and CASE WHEN @iType=0 THEN Max(dExpireDate) ELSE
 Max(dFADate) END-@iDays<=@dCurDate
	And max(dVouchDate) BETWEEN @dVouchDate1 AND @dVouchDate2 And Max(CASE WHEN cPr
ocStyle=cCoVouchType THEN dRegDate ELSE 0 END)
	BETWEEN @dRegDate1 AND @dRegDate2 AND (@iAmount1 Is Null OR sum(iCAmount_f)>=@i
Amount1) And (@iAmount2 Is Null OR sum(iCAmount_f)<=@iAmount2)
	AND (@iRAmount1 Is Null OR sum(iCAmount_f-iDAmount_f)>=@iRAmount1) And (@iRAmou
nt2 Is Null OR sum(iCAmount_f-iDAmount_f)<=@iRAmount2)
	AND (@cDepCode='' OR Max(cDeptCode) Like @cDepCode+'%') And (@cPersonCode='' OR
 Max(cPerson)=@cPersonCode) And (@cCode='' OR Max(cCode) Like @cCode+'%')
	AND (@cItemCode='' OR Max(cItemCode)=@cItemCode) And @iCheck & 1<>0 AND Min(CAS
E WHEN cProcStyle=cCoVouchType AND cPZID Is not Null THEN 1 ELSE 2 END) & @iBill<
>0
UNION ALL
SELECT 	cVouchType,Max(cTypeName),cVouchID,Min(dVouchDate),
	cDwCode,Max(cDwName),Max(cDepName),Max(cPersonName),
	Max(cPayName),'','',Max(dExpireDate),
	max(cExch_Name),Max(iExchRate),sum(iAmount_f),
	sum(iAmount),sum(iAmount_f-iFmount_f),sum(iAmount-iFmount),Max(cDigest),Max(iCr
eLine) as iCreLine
FROM
	Ap_PurBillVend
WHERE
	IsNull(cCheckMan,'')='' And cDwCode BETWEEN @cDwCode1 AND @cDwCode2 
	And cVouchType Like @cVouchClass+'%' And (cVouchType=@cVouchType OR @cVouchType
='') 
	And (cVouchID>=@cVouchID1 OR @cVouchID1='') AND (cVouchID<=@cVouchID2 OR @cVouc
hID2='')
 	AND (@cExchName='' OR cExch_Name=@cExchName) 
	And iNetLock=0 And (bOriginal=0 OR bFirst=1)
GROUP BY
	cDwCode,cVouchType,cVouchID
HAVING
	sum(iAmount_f-iFmount)<>0 and CASE WHEN @iType=0 THEN Max(dExpireDate) ELSE Max
(dFADate) END-@iDays<=@dCurDate
	And max(dVouchDate) BETWEEN @dVouchDate1 AND @dVouchDate2 AND (@iAmount1 Is Nul
l OR sum(iAmount_f)>=@iAmount1) And (@iAmount2 Is Null OR sum(iAmount_f)<=@iAmoun
t2)
	AND (@iRAmount1 Is Null OR sum(iAmount_f-iFmount_f)>=@iRAmount1) And (@iRAmount
2 Is Null OR sum(iAmount_f-iFmount)<=@iRAmount2)
	AND (@cDepCode='' OR Max(cDeptCode) Like @cDepCode+'%') And (@cPersonCode='' OR
 Max(cPerson)=@cPersonCode)
	And @iCheck & 2<>0
UNION ALL
SELECT 	cVouchType,Max(cTypeName),cVouchID,Min(dVouchDate),
	cDwCode,Max(cDwName),Max(cDepName),Max(cPersonName),
	Max(cPayName),'','',Max(dExpireDate),
	max(cExch_Name),Max(iExchRate),sum(iAmount_f),
	sum(iAmount),sum(iRAmount_f),sum(iRAmount),Max(cDigest),Max(iCreLine) as iCreLi
ne
FROM
	Ap_VouchVend
WHERE
	IsNull(cCheckMan,'')='' And cDwCode BETWEEN @cDwCode1 AND @cDwCode2 
	And cVouchType Like @cVouchClass+'%' And (cVouchType=@cVouchType OR @cVouchType
='') 
	And (cVouchID>=@cVouchID1 OR @cVouchID1='') AND (cVouchID<=@cVouchID2 OR @cVouc
hID2='')
	AND (@cExchName='' OR cExch_Name=@cExchName)
GROUP BY
	cDwCode,cVouchType,cVouchID
HAVING
	sum(iRAmount_f)<>0 and CASE WHEN @iType=0 THEN Max(dExpireDate) ELSE Max(dFADat
e) END-@iDays<=@dCurDate
	And max(dVouchDate) BETWEEN @dVouchDate1 AND @dVouchDate2 AND (@iAmount1 Is Nul
l OR sum(iAmount_f)>=@iAmount1) And (@iAmount2 Is Null OR sum(iAmount_f)<=@iAmoun
t2)
	AND (@iRAmount1 Is Null OR sum(iRAmount_f)>=@iRAmount1) And (@iRAmount2 Is Null
 OR sum(iRAmount_f)<=@iRAmount2)
	AND (@cDepCode='' OR Max(cDeptCode) Like @cDepCode+'%') And (@cPersonCode='' OR
 Max(cPerson)=@cPersonCode) And (@cCode='' OR Max(cCode) Like @cCode+'%')
	And @iCheck & 2<>0
ORDER BY 
	cVouchType,dVouchDate,cVouchID,cDwCode

GO














CREATE PROCEDURE [AP_PROC_MXZ]
/*查询条件参数表*/
@iType tinyint=0,  --查询对象（0:客户/供应商，1:部门，2:业务员，3:存货）
@cStartCode varchar(20)='', --查询对象编码
@cEndCode varchar(20)='', --范围
@iStartMon tinyint=1,  --查询月份
@iEndMon tinyint=12,  --范围
@cExchName varchar(8)='', --币种
@cFlag varchar(2)='AR'  --应收应付标识（应收:AR,应付:AP）
AS
 
IF @cFlag='AR'
   BEGIN
     SELECT iQcD_s=sum(iDAmount_s),iQcC_s=sum(iCAmount_s),iQcD=sum(iDAmount),iQc
C=sum(iCAmount),iQcD_f=sum(iDAmount_f),iQcC_f=sum(iCAmount_f),iQc_s=sum(iDAmount_
s-iCAmount_s),iQc_f=sum(iDAmount_f-iCAmount_f),iQc=sum(iDAmount-iCAmount)
     FROM Ap_DetailCust
     WHERE iFlag<3 AND case @iType when 0 then cDwCode when 1 then cDeptCode whe
n 2 then cPerson else cInvCode end IS NOT NULL
       AND (@cStartCode='' OR case @iType when 0 then cDwCode when 1 then cDeptC
ode when 2 then cPerson else cInvCode end>=@cStartCode)
       AND (@cEndCode='' OR case @iType when 0 then cDwCode when 1 then cDeptCod
e when 2 then cPerson else cInvCode end<=@cEndCode)
       AND iPeriod<@iStartMon AND (@cExchName='' OR cExch_Name=@cExchName)
 
    Select dDate=Max(dRegDate),cTypeName=Max(cTypeName),cVouchID,cDigest=Max(cDi
gest),iDAmount_s=sum(iDAmount_s),iDAmount_f=sum(iDAmount_f),
       iExchRate=Max(iExchRate),iDAmount=sum(iDAmount),iCAmount_s=sum(iCAmount_s
),iCAmount_f=sum(iCAmount_f),iCAmount=sum(iCAmount)
     FROM Ap_DetailCust 
     WHERE iFlag<3 and cProcStyle <>'9K' and cProcStyle <>'9L' and cProcStyle <>
'9N' AND case @iType when 0 then cDwCode when 1 then cDeptCode when 2 then cPerso
n else cInvCode end IS NOT NULL
       AND (@cStartCode='' OR case @iType when 0 then cDwCode when 1 then cDeptC
ode when 2 then cPerson else cInvCode end>=@cStartCode)
       AND (@cEndCode='' OR case @iType when 0 then cDwCode when 1 then cDeptCod
e when 2 then cPerson else cInvCode end<=@cEndCode)
       AND iPeriod>=@iStartMon AND iPeriod<=@iEndMon AND (@cExchName='' OR cExch
_Name=@cExchName)
     GROUP BY cVouchType,cVouchID,iPeriod,dRegDate,cCancelNo
     ORDER BY iPeriod,dRegDate
     COMPUTE 
       sum(sum(iDAmount_s)),sum(sum(iDAmount_f)),sum(sum(iDAmount)),sum(sum(iCAm
ount_s)),sum(sum(iCAmount_f)),sum(sum(iCAmount))
     BY iPeriod
     COMPUTE 
       sum(sum(iDAmount_s)),sum(sum(iDAmount_f)),sum(sum(iDAmount)),sum(sum(iCAm
ount_s)),sum(sum(iCAmount_f)),sum(sum(iCAmount))
   END
ELSE
   BEGIN
     SELECT iQcD_s=sum(iDAmount_s),iQcC_s=sum(iCAmount_s),iQcD=sum(iDAmount),iQc
C=sum(iCAmount),iQcD_f=sum(iDAmount_f),iQcC_f=sum(iCAmount_f),iQc_s=sum(iCAmount_
s-iDAmount_s),iQc_f=sum(iCAmount_f-iDAmount_f),iQc=sum(iCAmount-iDAmount)
     FROM Ap_DetailVend
     WHERE iFlag<3 AND case @iType when 0 then cDwCode when 1 then cDeptCode whe
n 2 then cPerson else cInvCode end IS NOT NULL
       AND (@cStartCode='' OR case @iType when 0 then cDwCode when 1 then cDeptC
ode when 2 then cPerson else cInvCode end>=@cStartCode)
       AND (@cEndCode='' OR case @iType when 0 then cDwCode when 1 then cDeptCod
e when 2 then cPerson else cInvCode end<=@cEndCode)
       AND iPeriod<@iStartMon AND (@cExchName='' OR cExch_Name=@cExchName)
 
     Select dDate=Max(dRegDate),cTypeName=Max(cTypeName),cVouchID,cDigest=Max(cD
igest),iCAmount_s=sum(iCAmount_s),iCAmount_f=sum(iCAmount_f),
       iExchRate=Max(iExchRate),iCAmount=sum(iCAmount),iDAmount_s=sum(iDAmount_s
),iDAmount_f=sum(iDAmount_f),iDAmount=sum(iDAmount)
     FROM Ap_DetailVend 
     WHERE iFlag<3 and cProcStyle <>'9K' and cProcStyle <>'9L' and cProcStyle <>
'9N' AND case @iType when 0 then cDwCode when 1 then cDeptCode when 2 then cPerso
n else cInvCode end IS NOT NULL
       AND (@cStartCode='' OR case @iType when 0 then cDwCode when 1 then cDeptC
ode when 2 then cPerson else cInvCode end>=@cStartCode)
       AND (@cEndCode='' OR case @iType when 0 then cDwCode when 1 then cDeptCod
e when 2 then cPerson else cInvCode end<=@cEndCode)
       AND iPeriod>=@iStartMon AND iPeriod<=@iEndMon AND (@cExchName='' OR cExch
_Name=@cExchName)
     GROUP BY cVouchType,cVouchID,iPeriod,dRegDate,cCancelNo
     ORDER BY iPeriod,dRegDate
     COMPUTE 
       sum(sum(iCAmount_s)),sum(sum(iCAmount_f)),sum(sum(iCAmount)),sum(sum(iDAm
ount_s)),sum(sum(iDAmount_f)),sum(sum(iDAmount))
     BY iPeriod
     COMPUTE 
       sum(sum(iCAmount_s)),sum(sum(iCAmount_f)),sum(sum(iCAmount)),sum(sum(iDAm
ount_s)),sum(sum(iDAmount_f)),sum(sum(iDAmount))
   END



GO










CREATE PROCEDURE [AP_PROC_YEB]
/*查询条件参数表*/
@iType tinyint=0,  --查询对象（0:客户/供应商，1:部门，2:业务员，3:存货）
@cStartCode varchar(20)='', --查询对象编码
@cEndCode varchar(20)='', --范围
@iStartMon tinyint=1,  --查询月份
@iEndMon tinyint=12,  --范围
@cExchName varchar(8)='', --币种
@iRAmount1 money=null,   --余额
@iRAmount2 money=null,   --范围
@cFlag varchar(2)='AR'  --应收应付标识（应收:AR,应付:AP）
AS
DECLARE @cTmp varchar(2000)
 
IF @cFlag='AR'
   BEGIN
     Select 
       cFLCode=case @iType when 0 then cDwCode when 1 then cDeptCode when 2 then
 cPerson else cInvCode end,
       cFLName=case @iType when 0 then Max(cDwName) when 1 then Max(cDepName) wh
en 2 then Max(cPersonName) else Max(cInvName) end,
       iQc_s=sum(case when iPeriod<@iStartMon then iDAmount_s-iCAmount_s else 0 
end), --期初数量
       iQc_f=sum(case when iPeriod<@iStartMon then iDAmount_f-iCAmount_f else 0 
end), --期初外币
       iQc=sum(case when iPeriod<@iStartMon then iDAmount-iCAmount else 0 end), 
    --期初本币
       imd_s=sum(case when iPeriod>=@iStartMon then iDAmount_s else 0 end),     
      --应收数量
       imd_f=sum(case when iPeriod>=@iStartMon then iDAmount_f else 0 end),     
      --应收外币
       imd=sum(case when iPeriod>=@iStartMon then iDAmount else 0 end),         
     --应收本币
       imc_s=sum(case when iPeriod>=@iStartMon then iCAmount_s else 0 end),     
      --收回数量
       imc_f=sum(case when iPeriod>=@iStartMon then iCAmount_f else 0 end),     
      --收回外币
       imc=sum(case when iPeriod>=@iStartMon then iCAmount else 0 end),         
     --收回本币
       iQm_s=sum(iDAmount_s-iCAmount_s),iQm_f=sum(iDAmount_f-iCAmount_f),   --期
末数量、外币、本币
       iQm=sum(iDAmount-iCAmount)
     FROM Ap_DetailCust 
     WHERE iFlag<3 AND case @iType when 0 then cDwCode when 1 then cDeptCode whe
n 2 then cPerson else cInvCode end IS NOT NULL
       AND (@cStartCode='' OR case @iType when 0 then cDwCode when 1 then cDeptC
ode when 2 then cPerson else cInvCode end>=@cStartCode)
       AND (@cEndCode='' OR case @iType when 0 then cDwCode when 1 then cDeptCod
e when 2 then cPerson else cInvCode end<=@cEndCode)
       AND iPeriod<=@iEndMon AND (@cExchName='' OR cExch_Name=@cExchName)
     GROUP BY case @iType when 0 then cDwCode when 1 then cDeptCode when 2 then 
cPerson else cInvCode end
     HAVING (@iRAmount1 IS NULL OR sum(iDAmount-iCAmount)>=@iRAmount1)
       AND (@iRAmount2 IS NULL OR sum(iDAmount-iCAmount)<=@iRAmount2) 
     COMPUTE sum(sum(case when iPeriod<@iStartMon then iDAmount_s-iCAmount_s els
e 0 end)),
       sum(sum(case when iPeriod<@iStartMon then iDAmount_f-iCAmount_f else 0 en
d)),
       sum(sum(case when iPeriod<@iStartMon then iDAmount-iCAmount else 0 end)),
       sum(sum(case when iPeriod>=@iStartMon then iDAmount_s else 0 end)),
       sum(sum(case when iPeriod>=@iStartMon then iDAmount_f else 0 end)),
       sum(sum(case when iPeriod>=@iStartMon then iDAmount else 0 end)),
       sum(sum(case when iPeriod>=@iStartMon then iCAmount_s else 0 end)),
       sum(sum(case when iPeriod>=@iStartMon then iCAmount_f else 0 end)),
       sum(sum(case when iPeriod>=@iStartMon then iCAmount else 0 end)),
       sum(sum(iDAmount_s-iCAmount_s)),
       sum(sum(iDAmount_f-iCAmount_f)),
       sum(sum(iDAmount-iCAmount))
   END
ELSE
   BEGIN
     Select 
       cFLCode=case @iType when 0 then cDwCode when 1 then cDeptCode when 2 then
 cPerson else cInvCode end,
       cFLName=case @iType when 0 then Max(cDwName) when 1 then Max(cDepName) wh
en 2 then Max(cPersonName) else Max(cInvName) end,
       iQc_s=sum(case when iPeriod<@iStartMon then iCAmount_s-iDAmount_s else 0 
end), --期初数量
       iQc_f=sum(case when iPeriod<@iStartMon then iCAmount_f-iDAmount_f else 0 
end), --期初外币
       iQc=sum(case when iPeriod<@iStartMon then iCAmount-iDAmount else 0 end), 
    --期初本币
       imd_s=sum(case when iPeriod>=@iStartMon then iCAmount_s else 0 end),     
      --应付数量
       imd_f=sum(case when iPeriod>=@iStartMon then iCAmount_f else 0 end),     
      --应付外币
       imd=sum(case when iPeriod>=@iStartMon then iCAmount else 0 end),         
    --应付本币
       imc_s=sum(case when iPeriod>=@iStartMon then iDAmount_s else 0 end),     
      --支出数量
       imc_f=sum(case when iPeriod>=@iStartMon then iDAmount_f else 0 end),     
      --支出外币
       imc=sum(case when iPeriod>=@iStartMon then iDAmount else 0 end),         
    --支出本币
       iQm_s=sum(iCAmount_s-iDAmount_s),iQm_f=sum(iCAmount_f-iDAmount_f),  --期末
数量、外币、本币
       iQm=sum(iCAmount-iDAmount)
     FROM Ap_DetailVend 
     WHERE iFlag<3 AND case @iType when 0 then cDwCode when 1 then cDeptCode whe
n 2 then cPerson else cInvCode end IS NOT NULL
       AND (@cStartCode='' OR case @iType when 0 then cDwCode when 1 then cDeptC
ode when 2 then cPerson else cInvCode end>=@cStartCode)
       AND (@cEndCode='' OR case @iType when 0 then cDwCode when 1 then cDeptCod
e when 2 then cPerson else cInvCode end<=@cEndCode)
       AND iPeriod<=@iEndMon AND (@cExchName='' OR cExch_Name=@cExchName)
     GROUP BY case @iType when 0 then cDwCode when 1 then cDeptCode when 2 then 
cPerson else cInvCode end
     HAVING (@iRAmount1 IS NULL OR sum(iCAmount-iDAmount)>=@iRAmount1)
       AND (@iRAmount2 IS NULL OR sum(iCAmount-iDAmount)<=@iRAmount2) 
     COMPUTE sum(sum(case when iPeriod<@iStartMon then iCAmount_s-iDAmount_s els
e 0 end)),
       sum(sum(case when iPeriod<@iStartMon then iCAmount_f-iDAmount_f else 0 en
d)),
       sum(sum(case when iPeriod<@iStartMon then iCAmount-iDAmount else 0 end)),
       sum(sum(case when iPeriod>=@iStartMon then iCAmount_s else 0 end)),
       sum(sum(case when iPeriod>=@iStartMon then iCAmount_f else 0 end)),
       sum(sum(case when iPeriod>=@iStartMon then iCAmount else 0 end)),
       sum(sum(case when iPeriod>=@iStartMon then iDAmount_s else 0 end)),
       sum(sum(case when iPeriod>=@iStartMon then iDAmount_f else 0 end)),
       sum(sum(case when iPeriod>=@iStartMon then iDAmount else 0 end)),
       sum(sum(iCAmount_s-iDAmount_s)),
       sum(sum(iCAmount_f-iDAmount_f)),
       sum(sum(iCAmount-iDAmount))
   END


GO


















CREATE PROC Ap_Proc_YWMXZ
--参数
@cBaseExch 	VARCHAR(8)='人民币',
@iCXType 	TINYINT=0,	
    /*
	查询对象:
	0:客户( cDwCode ),1:客户分类( cDWCCode ),2:客户总公司( cHDWCode ),
	3:地区( cDWDCode ),4:部门( cDeptCode ),5:主管部门( cHDptCode ),
	6:业务员( cPerson ),7:主管业务员( cHPsnCode ),8:存货( cInvCode ),9:存货分类( cI
nvCCode )
    */
@cCXCode1 	VARCHAR(30)='',
@cCXCode2 	VARCHAR(30)='ZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZ',
@iMXType 	SMALLINT=0,
    /*
	明细对象:
	0:客户( cDwCode ),1:部门( cDeptCode ),2:业务员( cPerson ),3:存货( cInvCode )
    */
@cMXCode1 	VARCHAR(30)='',
@cMXCode2 	VARCHAR(30)='ZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZ',
@cExchName 	VARCHAR(8)='',
@iMonth1 	SMALLINT=1,
@iMonth2 	SMALLINT=12,
@cFlag		VARCHAR(2)='AR'
AS
IF @cCXCode1='' SET @cCXCode1=CHAR(1)
IF @cMXCode1='' SET @cMXCode1=CHAR(1)
IF @cFlag='AR'
--应收
SELECT 
	Max(Auto_ID) As Auto_ID,CASE @iCXType WHEN 0 THEN cDwCode WHEN 1 THEN cDWCCode 
WHEN 2 THEN cHDWCode 
	WHEN 3 THEN cDWDCode WHEN 4 THEN cDeptCode WHEN 5 THEN cHDptCode 
	WHEN 6 THEN cPerson WHEN 7 THEN cHPsnCode WHEN 8 THEN cInvCode WHEN 9 THEN cInv
CCode END as FL,
	CASE @iCXType WHEN 0 THEN MAX(cDwName) WHEN 1 THEN MAX(cDWCName) WHEN 2 THEN MA
X(cHDwName) 
	WHEN 3 THEN MAX(cDCName) WHEN 4 THEN MAX(cDepName) WHEN 5 THEN MAX(cHDepName)
	WHEN 6 THEN MAX(cPersonName) WHEN 7 THEN MAX(cHPersonName) WHEN 8 THEN MAX(cInv
Name) WHEN 9 THEN MAX(cInvCName) END as FL1,
	CASE @iMXType WHEN 0 THEN cDwCode WHEN 1 THEN cDeptCode WHEN 2 THEN cPerson WHE
N 3 THEN cInvCode END as MX,
	CASE @iMXType WHEN 0 THEN MAX(cDwName) WHEN 1 THEN MAX(cDepName) WHEN 2 THEN MA
X(cPersonName) WHEN 3 THEN MAX(cInvName) END as MX1,
	min(dRegDate) as RgDt,'期初余额' as Dgst,'' as VType,'' as VID,
	--借方
	Sum(case when cexch_name=@cBaseExch then 0 else iDAmount_f end) as DA_f,
	Sum(iDAmount) as DA,Sum(iDAmount_s) as DA_s,
	--贷方
	Sum(case when cexch_name=@cBaseExch then 0 else iCAmount_f end) as CA_f,
	Sum(iCAmount) as CA,Sum(iCAmount_s) as CA_s,
	'' as ExchName,0 as Price,0 as rate 
FROM 
	Ap_DetailCust
WHERE 
	iPeriod<@iMonth1 AND iflag<3 
	AND CASE @iCXType WHEN 0 THEN cDwCode WHEN 1 THEN cDWCCode WHEN 2 THEN cHDWCode
 
	WHEN 3 THEN cDWDCode WHEN 4 THEN cDeptCode WHEN 5 THEN cHDptCode 
	WHEN 6 THEN cPerson WHEN 7 THEN cHPsnCode WHEN 8 THEN cInvCode WHEN 9 THEN cInv
CCode END
	BETWEEN @cCXCode1 AND @cCXCode2
	AND CASE @iMXType WHEN 0 THEN cDwCode WHEN 1 THEN cDeptCode WHEN 2 THEN cPerson
 WHEN 3 THEN cInvCode END
	BETWEEN @cMXCode1 AND @cMXCode2
	AND (cExch_Name=@cExchName or @cExchName='')
GROUP BY 
	CASE @iCXType WHEN 0 THEN cDwCode WHEN 1 THEN cDWCCode WHEN 2 THEN cHDWCode 
	WHEN 3 THEN cDWDCode WHEN 4 THEN cDeptCode WHEN 5 THEN cHDptCode 
	WHEN 6 THEN cPerson WHEN 7 THEN cHPsnCode WHEN 8 THEN cInvCode WHEN 9 THEN cInv
CCode END,
	CASE @iMXType WHEN 0 THEN cDwCode WHEN 1 THEN cDeptCode WHEN 2 THEN cPerson WHE
N 3 THEN cInvCode END
HAVING 
	sum(iDAmount-iCAmount)<>0 or sum(iDAmount_f-iCAmount_f)<>0 
UNION ALL
SELECT 
	Max(Auto_ID),CASE @iCXType WHEN 0 THEN cDwCode WHEN 1 THEN cDWCCode WHEN 2 THEN
 cHDWCode 
	WHEN 3 THEN cDWDCode WHEN 4 THEN cDeptCode WHEN 5 THEN cHDptCode 
	WHEN 6 THEN cPerson WHEN 7 THEN cHPsnCode WHEN 8 THEN cInvCode WHEN 9 THEN cInv
CCode END as FL,
	CASE @iCXType WHEN 0 THEN MAX(cDwName) WHEN 1 THEN MAX(cDWCName) WHEN 2 THEN MA
X(cHDwName) 
	WHEN 3 THEN MAX(cDCName) WHEN 4 THEN MAX(cDepName) WHEN 5 THEN MAX(cHDepName)
	WHEN 6 THEN MAX(cPersonName) WHEN 7 THEN MAX(cHPersonName) WHEN 8 THEN MAX(cInv
Name) WHEN 9 THEN MAX(cInvCName) END as FL1,
	CASE @iMXType WHEN 0 THEN cDwCode WHEN 1 THEN cDeptCode WHEN 2 THEN cPerson WHE
N 3 THEN cInvCode END as MX,
	CASE @iMXType WHEN 0 THEN MAX(cDwName) WHEN 1 THEN MAX(cDepName) WHEN 2 THEN MA
X(cPersonName) WHEN 3 THEN MAX(cInvName) END as MX1,
	dRegDate as RgDt,MAX(ISNULL(cDigest,'')) as Dgst,max(cTypeName) as VType,cVouch
ID as VID,
	Sum(case when cexch_name = @cBaseExch then 0 else iDAmount_f end) as DA_f,Sum(i
DAmount) as DA,
	Sum(iDAmount_s) as DA_s,
	Sum(case when cexch_name = @cBaseExch then 0 else iCAmount_f end) as CA_f,Sum(i
CAmount) as CA,
	Sum(iCAmount_s) as CA_s,
	max(cexch_name) as ExchName,max(iPrice) as Price,max(iexchrate) as rate 
FROM 
	Ap_DetailCust
WHERE 
	iPeriod>= @iMonth1 and iPeriod<= @iMonth2
	and iflag<3 and ((cProcStyle<>'9K' and cProcStyle<>'9L' and cProcStyle<>'9N') O
R @iCXType>3)
	AND CASE @iCXType WHEN 0 THEN cDwCode WHEN 1 THEN cDWCCode WHEN 2 THEN cHDWCode
 
	WHEN 3 THEN cDWDCode WHEN 4 THEN cDeptCode WHEN 5 THEN cHDptCode 
	WHEN 6 THEN cPerson WHEN 7 THEN cHPsnCode WHEN 8 THEN cInvCode WHEN 9 THEN cInv
CCode END
	BETWEEN @cCXCode1 AND @cCXCode2
	AND CASE @iMXType WHEN 0 THEN cDwCode WHEN 1 THEN cDeptCode WHEN 2 THEN cPerson
 WHEN 3 THEN cInvCode END
	BETWEEN @cMXCode1 AND @cMXCode2
	AND (cExch_Name=@cExchName or @cExchName='')
GROUP BY 
	CASE @iCXType WHEN 0 THEN cDwCode WHEN 1 THEN cDWCCode WHEN 2 THEN cHDWCode 
	WHEN 3 THEN cDWDCode WHEN 4 THEN cDeptCode WHEN 5 THEN cHDptCode 
	WHEN 6 THEN cPerson WHEN 7 THEN cHPsnCode WHEN 8 THEN cInvCode WHEN 9 THEN cInv
CCode END,
	CASE @iMXType WHEN 0 THEN cDwCode WHEN 1 THEN cDeptCode WHEN 2 THEN cPerson WHE
N 3 THEN cInvCode END,
	dRegDate,cCancelNo,cVouchType,cVouchID
HAVING 
	sum(iDAmount)<>0 or sum(iCAmount)<>0 
ORDER BY
	FL,MX,RgDt,Auto_ID
ELSE
--应付
SELECT 
	Max(Auto_ID) As Auto_ID,CASE @iCXType WHEN 0 THEN cDwCode WHEN 1 THEN cDWCCode 
WHEN 2 THEN cHDWCode 
	WHEN 3 THEN cDWDCode WHEN 4 THEN cDeptCode WHEN 5 THEN cHDptCode 
	WHEN 6 THEN cPerson WHEN 7 THEN cHPsnCode WHEN 8 THEN cInvCode WHEN 9 THEN cInv
CCode END as FL,
	CASE @iCXType WHEN 0 THEN MAX(cDwName) WHEN 1 THEN MAX(cDWCName) WHEN 2 THEN MA
X(cHDwName) 
	WHEN 3 THEN MAX(cDCName) WHEN 4 THEN MAX(cDepName) WHEN 5 THEN MAX(cHDepName)
	WHEN 6 THEN MAX(cPersonName) WHEN 7 THEN MAX(cHPersonName) WHEN 8 THEN MAX(cInv
Name) WHEN 9 THEN MAX(cInvCName) END as FL1,
	CASE @iMXType WHEN 0 THEN cDwCode WHEN 1 THEN cDeptCode WHEN 2 THEN cPerson WHE
N 3 THEN cInvCode END as MX,
	CASE @iMXType WHEN 0 THEN MAX(cDwName) WHEN 1 THEN MAX(cDepName) WHEN 2 THEN MA
X(cPersonName) WHEN 3 THEN MAX(cInvName) END as MX1,
	min(dRegDate) as RgDt,'期初余额' as Dgst,'' as VType,'' as VID,
	--借方
	Sum(case when cexch_name=@cBaseExch then 0 else iDAmount_f end) as DA_f,
	Sum(iDAmount) as DA,Sum(iDAmount_s) as DA_s,
	--贷方
	Sum(case when cexch_name=@cBaseExch then 0 else iCAmount_f end) as CA_f,
	Sum(iCAmount) as CA,Sum(iCAmount_s) as CA_s,
	'' as ExchName,0 as Price,0 as rate 
FROM 
	Ap_DetailVend
WHERE 
	iPeriod<@iMonth1 AND iflag<3 
	AND CASE @iCXType WHEN 0 THEN cDwCode WHEN 1 THEN cDWCCode WHEN 2 THEN cHDWCode
 
	WHEN 3 THEN cDWDCode WHEN 4 THEN cDeptCode WHEN 5 THEN cHDptCode 
	WHEN 6 THEN cPerson WHEN 7 THEN cHPsnCode WHEN 8 THEN cInvCode WHEN 9 THEN cInv
CCode END
	BETWEEN @cCXCode1 AND @cCXCode2
	AND CASE @iMXType WHEN 0 THEN cDwCode WHEN 1 THEN cDeptCode WHEN 2 THEN cPerson
 WHEN 3 THEN cInvCode END
	BETWEEN @cMXCode1 AND @cMXCode2
	AND (cExch_Name=@cExchName or @cExchName='')
GROUP BY 
	CASE @iCXType WHEN 0 THEN cDwCode WHEN 1 THEN cDWCCode WHEN 2 THEN cHDWCode 
	WHEN 3 THEN cDWDCode WHEN 4 THEN cDeptCode WHEN 5 THEN cHDptCode 
	WHEN 6 THEN cPerson WHEN 7 THEN cHPsnCode WHEN 8 THEN cInvCode WHEN 9 THEN cInv
CCode END,
	CASE @iMXType WHEN 0 THEN cDwCode WHEN 1 THEN cDeptCode WHEN 2 THEN cPerson WHE
N 3 THEN cInvCode END
HAVING 
	sum(iDAmount-iCAmount)<>0 or sum(iDAmount_f-iCAmount_f)<>0 
UNION ALL
SELECT 
	Max(Auto_ID),CASE @iCXType WHEN 0 THEN cDwCode WHEN 1 THEN cDWCCode WHEN 2 THEN
 cHDWCode 
	WHEN 3 THEN cDWDCode WHEN 4 THEN cDeptCode WHEN 5 THEN cHDptCode 
	WHEN 6 THEN cPerson WHEN 7 THEN cHPsnCode WHEN 8 THEN cInvCode WHEN 9 THEN cInv
CCode END as FL,
	CASE @iCXType WHEN 0 THEN MAX(cDwName) WHEN 1 THEN MAX(cDWCName) WHEN 2 THEN MA
X(cHDwName) 
	WHEN 3 THEN MAX(cDCName) WHEN 4 THEN MAX(cDepName) WHEN 5 THEN MAX(cHDepName)
	WHEN 6 THEN MAX(cPersonName) WHEN 7 THEN MAX(cHPersonName) WHEN 8 THEN MAX(cInv
Name) WHEN 9 THEN MAX(cInvCName) END as FL1,
	CASE @iMXType WHEN 0 THEN cDwCode WHEN 1 THEN cDeptCode WHEN 2 THEN cPerson WHE
N 3 THEN cInvCode END as MX,
	CASE @iMXType WHEN 0 THEN MAX(cDwName) WHEN 1 THEN MAX(cDepName) WHEN 2 THEN MA
X(cPersonName) WHEN 3 THEN MAX(cInvName) END as MX1,
	min(dRegDate) as RgDt,MAX(ISNULL(cDigest,'')) as Dgst,max(cTypeName) as VType,c
VouchID as VID,
	Sum(case when cexch_name = @cBaseExch then 0 else iDAmount_f end) as DA_f,Sum(i
DAmount) as DA,
	Sum(iDAmount_s) as DA_s,
	Sum(case when cexch_name = @cBaseExch then 0 else iCAmount_f end) as CA_f,Sum(i
CAmount) as CA,
	Sum(iCAmount_s) as CA_s,
	max(cexch_name) as ExchName,max(iPrice) as Price,max(iexchrate) as rate 
FROM 
	Ap_DetailVend
WHERE 
	iPeriod>= @iMonth1 and iPeriod<= @iMonth2
	and iflag<3 and ((cProcStyle<>'9K' and cProcStyle<>'9L' and cProcStyle<>'9N') O
R @iCXType>3) 
	AND CASE @iCXType WHEN 0 THEN cDwCode WHEN 1 THEN cDWCCode WHEN 2 THEN cHDWCode
 
	WHEN 3 THEN cDWDCode WHEN 4 THEN cDeptCode WHEN 5 THEN cHDptCode 
	WHEN 6 THEN cPerson WHEN 7 THEN cHPsnCode WHEN 8 THEN cInvCode WHEN 9 THEN cInv
CCode END
	BETWEEN @cCXCode1 AND @cCXCode2
	AND CASE @iMXType WHEN 0 THEN cDwCode WHEN 1 THEN cDeptCode WHEN 2 THEN cPerson
 WHEN 3 THEN cInvCode END
	BETWEEN @cMXCode1 AND @cMXCode2
	AND (cExch_Name=@cExchName or @cExchName='')
GROUP BY 
	CASE @iCXType WHEN 0 THEN cDwCode WHEN 1 THEN cDWCCode WHEN 2 THEN cHDWCode 
	WHEN 3 THEN cDWDCode WHEN 4 THEN cDeptCode WHEN 5 THEN cHDptCode 
	WHEN 6 THEN cPerson WHEN 7 THEN cHPsnCode WHEN 8 THEN cInvCode WHEN 9 THEN cInv
CCode END,
	CASE @iMXType WHEN 0 THEN cDwCode WHEN 1 THEN cDeptCode WHEN 2 THEN cPerson WHE
N 3 THEN cInvCode END,
	dRegDate,cCancelNo,cVouchType,cVouchID 
HAVING 
	sum(iDAmount)<>0 or sum(iCAmount)<>0 
ORDER BY
	FL,MX,RgDt,Auto_ID

GO











CREATE PROCEDURE [CA_BatchCB_HZB] @Batch Varchar(20),@ProductID Varchar(20),@mod
e tinyint,@Period1 tinyint,@Period2 tinyint, @FinCount float,@DeleteTmp tinyint A
S
--@mode 0 完工产品 1 在产品成本
SET NOCOUNT ON
declare @sql varchar(1000)
-- 先生成目录表 CAT
select @sql='	
    SELECT distinct b.cInvCode as procid,b.cInvName as procname
    INTO ##ca_cat
    FROM ca_amoct a,inventory b
    WHERE a.cppid=b.cInvCode 
    AND a.cBatch =''' + @batch + ''''
    --因为调试的数据所以没有加入实际的批号过滤条件
    select @sql = @sql + ' AND a.cppid=''' + @ProductID + ''''    
exec (@sql)

-- 材料费用
if @mode =0 
   begin
      select @sql='
       SELECT cppid as procid,sum(iTotalAmo-iOnpTotalAmo) as num1
       INTO ##ca_sub1
       FROM ca_amoct
       WHERE cAmoType=''0'' '
       + ' and cBatch = ''' + @Batch + ''''
       + ' and cPPid = ''' + @ProductID + ''''
       + ' and iperiod >=' + convert(varchar(5),@period1)
       + ' and iperiod <=' + convert(varchar(5),@period2)
       + ' GROUP BY cppid'
      exec(@sql)
   end
else
   begin
      select @sql='
       SELECT cppid as procid,sum(iOnpTotalAmo) as num1
       INTO ##ca_sub1
       FROM ca_amoct
       WHERE cAmoType=''0'' '
       + ' and cBatch = ''' + @Batch + ''''
       + ' and cPPid = ''' + @ProductID + ''''
       + ' and iperiod =' + convert(varchar(5),@period2)
       + ' GROUP BY cppid'
      exec(@sql)
   end

-- 人工费用
if @mode=0
   begin
      select @sql='
       SELECT cppid as procid,sum(iTotalAmo-iOnpTotalAmo) as num2
       INTO ##ca_sub2
       FROM ca_amoct
       WHERE cAmoType=''1'' '
       + ' and cBatch = ''' + @Batch + ''''
       + ' and cPPid = ''' + @ProductID + ''''
       + ' and iperiod >=' + convert(varchar(5),@period1)
       + ' and iperiod <=' + convert(varchar(5),@period2)
       + ' GROUP BY cppid'
      exec(@sql)
   end
else
   begin
      select @sql='
       SELECT cppid as procid,sum(iOnpTotalAmo) as num2
       INTO ##ca_sub2
       FROM ca_amoct
       WHERE cAmoType=''1'' '
       + ' and cBatch = ''' + @Batch + ''''
       + ' and cPPid = ''' + @ProductID + ''''
       + ' and iperiod =' + convert(varchar(5),@period2)
       + ' GROUP BY cppid'
      exec(@sql)
   end

-- 制造费用
if @mode=0 
   begin
      select @sql='
       SELECT cppid as procid,sum(iTotalAmo-iOnpTotalAmo) as num3
       INTO ##ca_sub3
       FROM ca_amoct
       WHERE cAmoType=''3'' '
       + ' and cBatch = ''' + @Batch + ''''
       + ' and cPPid = ''' + @ProductID + ''''
       + ' and iperiod >=' + convert(varchar(5),@period1)
       + ' and iperiod <=' + convert(varchar(5),@period2)
       + ' GROUP BY cppid'
      exec(@sql)
   end
else
   begin
      select @sql='
       SELECT cppid as procid,sum(iOnpTotalAmo) as num3
       INTO ##ca_sub3
       FROM ca_amoct
       WHERE cAmoType=''3'' '
       + ' and cBatch = ''' + @Batch + ''''
       + ' and cPPid = ''' + @ProductID + ''''
       + ' and iperiod =' + convert(varchar(5),@period2)
       + ' GROUP BY cppid'
      exec(@sql)
   end

-- 辅助费用
if @mode=0 
   begin
      select @sql='
       SELECT cppid as procid,sum(iTotalAmo-iOnpTotalAmo) as num4
       INTO ##ca_sub4
       FROM ca_amoct
       WHERE cAmoType=''2'' '
       + ' and cBatch = ''' + @Batch + ''''
       + ' and cPPid = ''' + @ProductID + ''''
       + ' and iperiod >=' + convert(varchar(5),@period1)
       + ' and iperiod <=' + convert(varchar(5),@period2)
       + ' GROUP BY cppid'
      exec(@sql)
   end
else
   begin   
      select @sql='
       SELECT cppid as procid,sum(iOnpTotalAmo) as num4
       INTO ##ca_sub4
       FROM ca_amoct
       WHERE cAmoType=''2'' '
       + ' and cBatch = ''' + @Batch + ''''
       + ' and cPPid = ''' + @ProductID + ''''
       + ' and iperiod =' + convert(varchar(5),@period2)
       + ' GROUP BY cppid'
      exec(@sql)
   end

-- 其他费用
if @mode=0 
   begin
      select @sql='
       SELECT cppid as procid,sum(iTotalAmo-iOnpTotalAmo) as num5
       INTO ##ca_sub5
       FROM ca_amoct
       WHERE cAmoType=''4'' '
       + ' and cBatch = ''' + @Batch + ''''
       + ' and cPPid = ''' + @ProductID + ''''
       + ' and iperiod >=' + convert(varchar(5),@period1)
       + ' and iperiod <=' + convert(varchar(5),@period2)
       + ' GROUP BY cppid'
      exec(@sql)
   end
else
   begin
      select @sql='
       SELECT cppid as procid,sum(iOnpTotalAmo) as num5
       INTO ##ca_sub5
       FROM ca_amoct
       WHERE cAmoType=''4'' '
       + ' and cBatch = ''' + @Batch + ''''
       + ' and cPPid = ''' + @ProductID + ''''
       + ' and iperiod =' + convert(varchar(5),@period2)
       + ' GROUP BY cppid'
      exec(@sql)
   end
if @deletetmp = 1
   delete from ca_rpthzbtmp
if @mode=0 
    insert into ca_rpthzbtmp
    select a.procid as [产品编码], a.procname as [产品名称],@fincount,num1 as [材
料费用],num2 as [人工费用],
     num3 as [制造费用],num4 as [辅助费用],num5 as [其他费用],0,0,@period1,1,@mo
de
    from ((((##ca_cat a left join ##ca_sub1 b on a.procid=b.procid )
     left join ##ca_sub2 c on a.procid=c.procid )
     left join ##ca_sub3 d on a.procid=d.procid )
     left join ##ca_sub4 e on a.procid=e.procid )
     left join ##ca_sub5 f on a.procid=f.procid
else
    insert into ca_rpthzbtmp
    select a.procid as [产品编码], a.procname as [产品名称],0,num1 as [材料费用]
,num2 as [人工费用],
     num3 as [制造费用],num4 as [辅助费用],num5 as[其他费用],0,0,@period1,1,@mod
e
    from ((((##ca_cat a left join ##ca_sub1 b on a.procid=b.procid )
     left join ##ca_sub2 c on a.procid=c.procid )
     left join ##ca_sub3 d on a.procid=d.procid )
     left join ##ca_sub4 e on a.procid=e.procid )
     left join ##ca_sub5 f on a.procid=f.procid 

drop table ##ca_cat
drop table ##ca_sub1
drop table ##ca_sub2 
drop table ##ca_sub3
drop table ##ca_sub4
drop table ##ca_sub5

--update ca_rpthzbtmp set 其他费=0 where 其他费 is null
--update ca_rpthzbtmp set 总成本=材料费+人工费+制造费+辅助费+其他费
--update ca_rpthzbtmp set 单位成本=总成本 / 完工数量 where 完工数量<>0

GO








CREATE PROCEDURE [PP_Action] @VouchType varchar(2),@ID varchar (30),@user varcha
r (20),@Action Varchar(1),
			     @checkperson varchar(20)='',@checkday varchar(10)=''	
 AS
	Declare @State int
	Declare @Checker varchar(20)
	Declare @Checkdate datetime
	declare @Period varchar(12)

	If (Select Count(*) from PP_Lock Where cVouchType=@VouchType and cID=@ID and cU
ser<>@user)>0
	    BEGIN		--有其他人操作
		Select '1' as cHave,cUser from PP_Lock Where cVouchType=@VouchType and cID=@ID
 and cUser<>@user
		Return
	    END

SET NOCOUNT ON
------------------------------------------------------------------------采购需求
	If @VouchType='36'	
	    Begin
		if (select count(*) from PP_MPSmain Where cMPID=@ID)=0 	--被删除
			Begin
				SET NOCOUNT OFF
				Select '3' as cHave
				Return
			End
	    	Set @State=(Select iState from PP_MPSmain Where cMPID=@ID)
		If @Action='1'  --审核：iState=1时可做
		   Begin
			If @State=1
			    Begin
				Update PP_MPSmain Set iState=2,cCheckPerson=@checkperson,dCheckDate=@checkda
y Where cMPID=@ID
				SET NOCOUNT OFF
				Select '0' as cHave
				Return
			    End
			Else
			    Goto ReturnState36
      		   End	
		If @Action='2'  --撤消审核：iState=2时可做
		   Begin
			If @State=2
			    Begin
				Update PP_MPSmain Set iState=1,cCheckPerson='',dCheckDate='' Where cMPID=@ID
				SET NOCOUNT OFF
				Select '0' as cHave
				Return
			    End
			Else
			    Goto ReturnState36
      		   End	
		If @Action='3'	--完成	  ：iState=3时可做
		   Begin
			If @State=3
			    Begin
				Update PP_MPSmain Set iState=4 Where cMPID=@ID
				SET NOCOUNT OFF
				Select '0' as cHave
				Return
			    End
			Else
			    Goto ReturnState36
      		   End	
		If @Action='4'	--撤消完成  ：iState=4时可做
		   Begin
			If @State=4
			    Begin
				Update PP_MPSmain Set iState=3 Where cMPID=@ID
				SET NOCOUNT OFF
				Select '0' as cHave
				Return
			    End
			Else
			    Goto ReturnState36
      		   End	
	    End
------------------------------------------------------------------------相关需求
 PP_RMRPmain
	If @VouchType='37'
	    Begin
		if (select count(*) from PP_RMRPmain Where cMPID=@ID)=0 	--被删除
			Begin
				SET NOCOUNT OFF
				Select '3' as cHave
				Return
			End
	    	Set @State=(Select iState from PP_RMRPmain Where cMPID=@ID)
		If @Action='1'  --审核：iState=1时可做
		   Begin
			If @State=1			
			    Begin
				set @Period=(select cPeriod from PP_Periodto where cMID=@ID and nkind=1)
				if (Select count(*) from PP_PPCmain where cPeriod=@Period)>0
					begin
						SET NOCOUNT OFF
						Select '4' as cHave
						Return					
					end 	
				Update PP_RMRPmain Set iState=2,cCheckPerson=@checkperson,dCheckDate=@checkd
ay Where cMPID=@ID
				SET NOCOUNT OFF
				Select '0' as cHave
				Return
			    End
			Else
			    Goto ReturnState37
      		   End	
		If @Action='2'  --撤消审核：iState=2时可做
		   Begin
			If @State=2
			    Begin
				set @Period=(select cPeriod from PP_Periodto where cMID=@ID and nkind=1)
				if (Select count(*) from PP_PPCmain where cPeriod=@Period)>0
					begin
						SET NOCOUNT OFF
						Select '4' as cHave
						Return					
					end 	
				Update PP_RMRPmain Set iState=1,cCheckPerson='',dCheckDate='' Where cMPID=@I
D
				SET NOCOUNT OFF
				Select '0' as cHave
				Return
			    End
			Else
			    Goto ReturnState37
      		   End	
		If @Action='3'	--完成	  ：iState=3时可做
		   Begin
			If @State=2
			    Begin
				Update PP_RMRPmain Set iState=3 Where cMPID=@ID
				SET NOCOUNT OFF
				Select '0' as cHave
				Return
			    End
			Else
			    Goto ReturnState37
      		   End	
		If @Action='4'	--撤消完成  ：iState=4时可做
		   Begin
			If @State=3
			    Begin
				Update PP_RMRPmain Set iState=2 Where cMPID=@ID
				SET NOCOUNT OFF
				Select '0' as cHave
				Return
			    End
			Else
			    Goto ReturnState37
      		   End	
	    End

------------------------------------------------------------------------独立需求
 PP_IMRPmain
	If @VouchType='38' 	    
		Begin
		if (select count(*) from PP_IMRPmain Where cIMRPID=@ID)=0 	--被删除
			Begin
				SET NOCOUNT OFF
				Select '3' as cHave
				Return
			End
	    	Set @State=(Select iState from PP_IMRPmain Where cIMRPID=@ID)
		If @Action='1'  --审核：iState=1时可做
		   Begin
			If @State=1
			    Begin
				set @Period=(select cPeriod from PP_Periodto where cMID=@ID and nkind=2)
				if (Select count(*) from PP_PPCmain where cPeriod=@Period)>0
					begin
						SET NOCOUNT OFF
						Select '4' as cHave
						Return					
					end 	
				Update PP_IMRPmain Set iState=2,cCheckPerson=@checkperson,dCheckDate=@checkd
ay Where cIMRPID=@ID
				SET NOCOUNT OFF
				Select '0' as cHave
				Return
			    End
			Else
			    Goto ReturnState38
      		   End	
		If @Action='2'  --撤消审核：iState=2时可做
		   Begin
			If @State=2
			    Begin
				set @Period=(select cPeriod from PP_Periodto where cMID=@ID and nkind=2)
				if (Select count(*) from PP_PPCmain where cPeriod=@Period)>0
					begin
						SET NOCOUNT OFF
						Select '4' as cHave
						Return					
					end 	
				Update PP_IMRPmain Set iState=1,cCheckPerson='',dCheckDate='' Where cIMRPID=
@ID
				SET NOCOUNT OFF
				Select '0' as cHave
				Return
			    End
			Else
			    Goto ReturnState38
      		   End	
		If @Action='3'	--完成	  ：iState=3时可做
		   Begin
			If @State=2
			    Begin
				Update PP_IMRPmain Set iState=3 Where cIMRPID=@ID
				SET NOCOUNT OFF
				Select '0' as cHave
				Return
			    End
			Else
			    Goto ReturnState38
      		   End	
		If @Action='4'	--撤消完成  ：iState=4时可做
		   Begin
			If @State=3
			    Begin
				Update PP_IMRPmain Set iState=2 Where cIMRPID=@ID
				SET NOCOUNT OFF
				Select '0' as cHave
				Return
			    End
			Else
			    Goto ReturnState38
      		   End	
	    End


------------------------------------------------------------------------物料需求
 PP_Period
	If @VouchType='39'
	    Begin
		if (select count(*) from PP_Period Where cPeriod=@ID)=0 	--被删除
			Begin
				SET NOCOUNT OFF
				Select '3' as cHave
				Return
			End
	    	Set @State=(Select iState from PP_Period Where cPeriod=@ID)
		If @Action='1'  --审核：iState=1时可做
		   Begin
			If @State=1
			    Begin
				Update PP_Period Set iState=2,cCheckPerson=@checkperson,dCheckDate=@checkday
 Where cPeriod=@ID
				SET NOCOUNT OFF
				Select '0' as cHave
				Return
			    End
			Else
			    Goto ReturnState39
      		   End	
		If @Action='2'  --撤消审核：iState=2时可做
		   Begin
			If @State=2
			    Begin
				if (Select count(*) from PP_PPCmain Where cPeriod=@ID) >0
					begin
						SET NOCOUNT OFF
						Select '6' as cHave
						Return					
					end
				else
					begin
						Update PP_Period Set iState=1,cCheckPerson='',dCheckDate='' Where cPeriod=
@ID
						SET NOCOUNT OFF
						Select '0' as cHave
						Return
					end				
			    End
			Else
			    Goto ReturnState39
      		   End	
            End


------------------------------------------------------------------------采购计划
 PP_PPCmain
	If @VouchType='40'
	
	    Declare @StartDate datetime	
	    Declare @Pstate int	

	    Begin
		if (select count(*) from PP_PPCmain Where cPeriod=@ID)=0 	--被删除
			Begin
				SET NOCOUNT OFF
				Select '3' as cHave
				Return
			End
	    	Set @State=(Select iState from PP_PPCmain Where cPeriod=@ID)
		If @Action='1'  --审核：iState=1时可做
		   Begin
			If @State=1
			    Begin
				    Update PP_PPCmain Set iState=2,cCheckPerson=@checkperson,dCheckDate=@che
ckday Where cPeriod=@ID
				    Update PP_Period Set iState=2,cCheckPerson=@checkperson,dCheckDate=@chec
kday Where cPeriod=@ID
				    SET NOCOUNT OFF
				    Select '0' as cHave
				    Return
			    End
			Else
			    Goto ReturnState40
      		   End	
		If @Action='2'  --撤消审核：iState=2时可做
		   Begin
			If @State=2
			    Begin
				if (Select count(*) from PO_Pomain Where cPeriod=@ID)>0		--已被采购定单引用
					begin
						SET NOCOUNT OFF
						Select '7' as cHave
						Return
					end
				Update PP_PPCmain Set iState=1,cCheckPerson='',dCheckDate='' Where cPeriod=@
ID
				Update PP_Period Set iState=1,cCheckPerson='',dCheckDate='' Where cPeriod=@I
D
				SET NOCOUNT OFF
				Select '0' as cHave
				Return
			    End
			Else
			    Goto ReturnState40
      		   End	
		If @Action='3'	--完成	  ：iState=3时可做
		   Begin
			If @State=2
				begin
			    	    Set @Pstate=(Select Top 1 PP_PPCmain.iState from PP_PPCmain,PP_Perio
d Where PP_PPCmain.cPeriod=PP_Period.cPeriod and PP_Period.dStartDate<@StartDate 
order by dStartDate desc)
				    if @Pstate<>3	--上周期未完成	 
					begin
						SET NOCOUNT OFF		
						Select '8' as cHave					
						return
					end
					Update PP_PPCmain Set iState=3 Where cPeriod=@ID
				    	Update PP_Period Set iState=3 Where cPeriod=@ID
					SET NOCOUNT OFF
					Select '0' as cHave
					Return
			    	End
			Else
			    Goto ReturnState40
      		   End	
		If @Action='4'	--撤消完成  ：iState=4时可做 		   
			Begin
			If @State=3
			    Begin
				Update PP_PPCmain Set iState=2 Where cPeriod=@ID
				Update PP_Period Set iState=2 Where cPeriod=@ID
				SET NOCOUNT OFF
				Select '0' as cHave
				Return
			    End
			Else
			    Goto ReturnState40
      		   End	
            End

--------------未执行提示
ReturnState36:
	Set @Checker=(Select Person.cPersonName from Person,PP_MPSmain Where cMPID=@ID 
and Person.cPersonCode=PP_MPSmain.cCheckPerson)
	Set @Checkdate=(Select dCheckdate from PP_MPSmain Where cMPID=@ID)
	SET NOCOUNT OFF
	Select '2' as cHave,@State as iState,@Checker as cCheckPerson,@Checkdate as dCh
eckDate 
	Return 

ReturnState37:
	Set @Checker=(Select Person.cPersonName from Person,PP_RMRPmain Where cMPID=@ID
 and Person.cPersonCode=PP_RMRPmain.cCheckPerson)
	Set @Checkdate=(Select dCheckdate from PP_RMRPmain Where cMPID=@ID)
	SET NOCOUNT OFF
	Select '2' as cHave,@State as iState,@Checker as cCheckPerson,@Checkdate as dCh
eckDate 
	Return 

ReturnState38:
	Set @Checker=(Select Person.cPersonName from Person,PP_IMRPmain Where cIMRPID=@
ID and Person.cPersonCode=PP_IMRPmain.cCheckPerson)
	Set @Checkdate=(Select dCheckdate from PP_IMRPmain Where cIMRPID=@ID)
	SET NOCOUNT OFF
	Select '2' as cHave,@State as iState,@Checker as cCheckPerson,@Checkdate as dCh
eckDate 
	Return 

ReturnState39:
	Set @Checker=(Select Person.cPersonName from Person,PP_Period Where cPeriod=@ID
 and Person.cPersonCode=PP_Period.cCheckPerson)
	Set @Checkdate=(Select dCheckdate from PP_Period Where cPeriod=@ID)
	SET NOCOUNT OFF
	Select '2' as cHave,@State as iState,@Checker as cCheckPerson,@Checkdate as dCh
eckDate 
	Return 

ReturnState40:
	Set @Checker=(Select Person.cPersonName from Person,PP_PPCmain Where cPeriod=@I
D and Person.cPersonCode=PP_PPCmain.cCheckPerson)
	Set @Checkdate=(Select dCheckdate from PP_PPCmain Where cPeriod=@ID)
	SET NOCOUNT OFF
	Select '2' as cHave,@State as iState,@Checker as cCheckPerson,@Checkdate as dCh
eckDate 
	Return


GO