CREATE procedure gh_query(
@gh1 varchar(200)= '% ',@gh2 varchar(200)= '% ')
AS
set nocount on
CREATE TABLE [dbo].[ghtemp] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[FID] [int] NULL ,
[finterid] [int] NOT NULL ,
[客户名称 (Client)] [varchar] (80) COLLATE Chinese_PRC_CI_AS NULL ,
[发票号码 (Inv. No)] [nvarchar] (255) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[发票日期 (Inv. Date)] [datetime] NULL ,
[ 货物净值 (Net value)] [decimal](38, 4) NULL ,
[税 额 (VAT)] [decimal](38, 4) NULL ,
[ 运 费 (Transport cost)] [varchar] (255) COLLATE Chinese_PRC_CI_AS NULL ,
[ 价税合计 (Total Amount)] [decimal](38, 10) NULL ,
[应回款日 (Due date)] [varchar] (255) COLLATE Chinese_PRC_CI_AS NULL ,
[实际回款金额 (Amount cashed)] [money] NULL ,
[实际回款日期 (Date of cash)] [datetime] NULL
) ON [PRIMARY]
--GO
insert ghtemp (
[FID] ,
[finterid] ,
[客户名称 (Client)] ,
[发票号码 (Inv. No)] ,
[发票日期 (Inv. Date)] ,
[ 货物净值 (Net value)] ,
[税 额 (VAT)] ,
[ 运 费 (Transport cost)] ,
[ 价税合计 (Total Amount)] ,
[应回款日 (Due date)] ,
[实际回款金额 (Amount cashed)] ,
[实际回款日期 (Date of cash)]
)
select
D.FID AS FID,
A.finterid AS finterid,
C.Fname as [客户名称 (Client)],
A.Fbillno as [发票号码 (Inv. No)],
A.Fdate as [发票日期 (Inv. Date)],
Sum(B.FAmount) AS [ 货物净值 (Net value)],
Sum(B.FTaxAmount) AS [税 额 (VAT)],
A.FHeadselfi0453 as [ 运 费 (Transport cost)],
sum(B.FAmountincludetax) as [ 价税合计 (Total Amount)],
A.FHeadselfi0464 as [应回款日 (Due date)],
D.FcheckamountFor as [实际回款金额 (Amount cashed)] ,
D.fcheckdate as [实际回款日期 (Date of cash)]
from
ICsale A
JOIN
ICsaleentry B
on A.finterid =B.finterid
JOIN
t_Organization C
on A.FcustID =C.FitemID
left join
t_RP_NewCheckInfo D
on A.finterid=D.Fbillid and D.ftype=3