实习记录：SqlServer开发源码

最新推荐文章于 2023-06-16 11:17:06 发布
最新推荐文章于 2023-06-16 11:17:06 发布
阅读量292 收藏
点赞数
分类专栏： 数据库 文章标签： sqlserver sql 数据库
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/qq_41749451/article/details/120250194
版权 
create table #test (FCUSTID nvarchar(255),CustNumber nvarchar(255),CustName nvarchar(255),Fdate nvarchar(255),ArBillNo nvarchar(255),fremark nvarchar(255),FMATERIALID nvarchar(255),
MaterialNumber nvarchar(255),MaterialName nvarchar(255),FModel nvarchar(255),FSALQTY float,FunitName nvarchar(255),FTAXPRICE float,FRECEIVEAMOUNT float,FPAYAMOUNTFOR float,Ebalance float,sortid nvarchar(255))
insert into #test (FCUSTID ,CustNumber ,CustName ,Fdate ,ArBillNo ,fremark,FMATERIALID,MaterialNumber,MaterialName,FModel,FSALQTY,FunitName,FTAXPRICE ,FRECEIVEAMOUNT )
(SELECT  cid,   cnumb,cname, CONVERT(VARCHAR(19),FDATE,102), T.FBILLNO ,t.FREMARK,wid,wnum,wname, t.FSPECIFICATION ,  round (sum (qty),2) as qty ,danwei ,FTAXPRICE,sum(FRECEIVEAMOUNT )FROM (  
select t0.FCUSTOMERID as cid,tn.FNUMBER as cnumb,tk.FNAME as cname, t0.fdate , T0.FBILLNO ,t0.FREMARK,tm.FMATERIALID as wid,tm.FNUMBER as wnum,t1.FNAME as wname, t1.FSPECIFICATION , t3.FSALQTY QTY  , tw1.FNAME as danwei,t4.FTAXPRICE,t3.FSALQTY*t4.FTAXPRICE as FRECEIVEAMOUNT 
	from t_AR_receivable t0 left join T_BD_CUSTOMER_L tk on (tk.FCUSTID= t0.FCUSTOMERID) left join T_BD_CUSTOMER tn on tn.fcustid = tk.fcustid left join t_AR_receivableEntry      t4  on(t4.fid = t0.fid) left join t_AR_receivableEntry_O       t3  on(t4.FENTRYID = t3.FENTRYID) left join T_BD_MATERIAL_L      t1  on(t4.FMATERIALID = t1.FMATERIALID)left join T_BD_MATERIAL tm on tm.FMATERIALID = t1.FMATERIALID left join 	T_BD_UNIT tw on (tw.FUNITID=t3.FSALUNITID)  left join T_BD_UNIT_L tw1 on (tw1.FUNITID = tw.FUNITID) 
	where  t0.FBILLTYPEID = '180ecd4afd5d44b5be78a6efe4a7e041' 	and fdate >='{2}' and fdate <='{3}' 
union all
select t0.FCUSTOMERID as cid,tn.FNUMBER as cnumb,tk.FNAME as cname, t0.fdate ,T0.FBILLNO ,t0.FREMARK, tm.FMATERIALID as wid,tm.FNUMBER as wnum,t1.FNAME as wname, t1.FSPECIFICATION ,round (te.FNOTAXAMOUNTFOR/te.FTAXPRICE,3) as  QTY ,   tw1.FNAME as danwei,te.FTAXPRICE,te.FNOTAXAMOUNTFOR as FRECEIVEAMOUNT 
	from T_AR_RECEIVABLE t0 left join T_BD_CUSTOMER_L tk on (tk.FCUSTID= t0.FCUSTOMERID)left join T_BD_CUSTOMER tn on tn.fcustid = tk.fcustid left join t_AR_receivableEntry  te  on(te.fid = t0.fid) left join T_BD_MATERIAL_L      t1  on(te.FMATERIALID = t1.FMATERIALID)left join T_BD_MATERIAL tm on tm.FMATERIALID = t1.FMATERIALID left join t_AR_receivableEntry_O       t3  on(te.FENTRYID = t3.FENTRYID) left join T_AR_RECEIVABLEENTRY_LK  arl on te.FENTRYID =  arl.FENTRYID and FSTABLENAME = 't_AR_receivableEntry'left join 	T_BD_UNIT tw on (tw.FUNITID=t3.FSALUNITID) left join T_BD_UNIT_L tw1 on (tw1.FUNITID = tw.FUNITID)
	where  t0.FBILLTYPEID = '5a41e09cdf400e' and fdate >='{2}' and fdate <='{3}' 
) 	
T
Where t.cname is not null  GROUP BY cid,cnumb,cname, CONVERT(VARCHAR(19),FDATE,102), wid,wnum,wname, T.FBILLNO ,t.FREMARK,t.FSPECIFICATION ,  danwei,t.FTAXPRICE
)

delete from #test where FunitName='打' or FunitName='Dozen'

create table #fbill(fbill1 nvarchar(255),fbill2 nvarchar(255))
insert into #fbill (fbill1,fbill2)
(select distinct t0.FBILLNO,t5.FBILLNO from  t_AR_receivable t0 
left join T_AR_RECEIVABLEENTRY t2 on t2.fid=t0.FID
left join T_AR_RECEIVABLEENTRY_LK t3 on t3.FENTRYID=t2.FENTRYID
left join T_AR_RECEIVABLEENTRY t4 on t4.FENTRYID=t3.FSID
left join  t_AR_receivable t5 on t4.FID = t5.FID
where t0.FBILLTYPEID = '5a41e09cdf400e' )

update #test set sortid = fbill2 from #test t1 left join #fbill t2 on t2.fbill1 = t1.ArBillNo 
update #test set sortid = ArBillNo where sortid is null
update #test set fsalqty =abs(fsalqty)                                  --数量取正
update #test set FTAXPRICE =  freceiveamount/FSALQTY where FSALQTY!=0    --+ -单价


--select * from #test order by CustName,MaterialName


create table #ar (FCUSTID nvarchar(255),CustNumber nvarchar(255),CustName nvarchar(255),Fdate nvarchar(255),ArBillNo nvarchar(255),fremark nvarchar(255),FMATERIALID nvarchar(255),
MaterialNumber nvarchar(255),MaterialName nvarchar(255),FModel nvarchar(255),FSALQTY float,FunitName nvarchar(255),FTAXPRICE float,FRECEIVEAMOUNT float,FPAYAMOUNTFOR float,
Ebalance float,sortid nvarchar(255))
insert into #ar (FCUSTID ,CustNumber ,CustName ,Fdate ,ArBillNo ,fremark,FMATERIALID,MaterialNumber,MaterialName,FModel,FSALQTY,FunitName,FTAXPRICE,FRECEIVEAMOUNT,FPAYAMOUNTFOR,Ebalance ,sortid )
(
	select * from #test where ArBillNo like 'ar%' 
)


create table #tj (FCUSTID nvarchar(255),CustNumber nvarchar(255),CustName nvarchar(255),Fdate nvarchar(255),ArBillNo nvarchar(255),fremark nvarchar(255),FMATERIALID nvarchar(255),
MaterialNumber nvarchar(255),MaterialName nvarchar(255),FModel nvarchar(255),FSALQTY float,FunitName nvarchar(255),FTAXPRICE float,FRECEIVEAMOUNT float,FPAYAMOUNTFOR float,Ebalance float,sortid nvarchar(255))
insert into #tj (FCUSTID ,CustNumber ,CustName ,Fdate ,ArBillNo ,fremark,FMATERIALID,MaterialNumber,MaterialName,FModel,FSALQTY,FunitName,FTAXPRICE,FRECEIVEAMOUNT,FPAYAMOUNTFOR,Ebalance ,sortid )
(
	select * from #test where ArBillNo like 'tj%' 
)

--select * from #ar  --16540

create table #artj (FCUSTID nvarchar(255),CustNumber nvarchar(255),CustName nvarchar(255),Fdate nvarchar(255),ArBillNo nvarchar(255),fremark nvarchar(255),FMATERIALID nvarchar(255),
MaterialNumber nvarchar(255),MaterialName nvarchar(255),FModel nvarchar(255),FSALQTY float,FunitName nvarchar(255),FTAXPRICE float,FRECEIVEAMOUNT float,FPAYAMOUNTFOR float,Ebalance float,sortid nvarchar(255),tjprice float,tjfre float,tjbill nvarchar(255))
insert into #artj (FCUSTID ,CustNumber ,CustName ,Fdate ,ArBillNo ,fremark,FMATERIALID,MaterialNumber,MaterialName,FModel,FSALQTY,FunitName,FTAXPRICE,FRECEIVEAMOUNT,FPAYAMOUNTFOR,Ebalance ,sortid,tjprice,tjfre,tjbill )
(
select ar.*,tj.FTAXPRICE,tj.FRECEIVEAMOUNT,tj.ArBillNo from #ar ar           --16614
left join #tj tj on ar.sortid =tj.sortid and ar.MaterialNumber =tj.MaterialNumber and ar.FSALQTY =tj.FSALQTY
)
create table #dandutj(FCUSTID nvarchar(255),CustNumber nvarchar(255),CustName nvarchar(255),Fdate nvarchar(255),ArBillNo nvarchar(255),fremark nvarchar(255),FMATERIALID nvarchar(255),
MaterialNumber nvarchar(255),MaterialName nvarchar(255),FModel nvarchar(255),FSALQTY float,FunitName nvarchar(255),FTAXPRICE float,FRECEIVEAMOUNT float,FPAYAMOUNTFOR float,Ebalance float,sortid nvarchar(255),tjprice float,tjfre float,tjbill nvarchar(255))
insert into #dandutj (FCUSTID ,CustNumber ,CustName ,Fdate ,ArBillNo ,fremark,FMATERIALID,MaterialNumber,MaterialName,FModel,FSALQTY,FunitName,FTAXPRICE,FRECEIVEAMOUNT,FPAYAMOUNTFOR,Ebalance ,sortid,tjprice,tjfre,tjbill )
(select * from #artj where  tjbill is not null)

create table #savedandutj(FCUSTID int,CustName nvarchar(255), Fdate datetime,ArBillNo nvarchar(255),fremark nvarchar(255),MaterialName nvarchar(255), fmodel nvarchar(255),FSALQTY float,funitname nvarchar(255),FTAXPRICE float,FRECEIVEAMOUNT float,FPAYAMOUNTFOR float,Ebalance float )
insert into #savedandutj(FCUSTID,CustName,Fdate,ArBillNo,fremark,MaterialName,fmodel,FSALQTY,funitname,FTAXPRICE,FRECEIVEAMOUNT,FPAYAMOUNTFOR,Ebalance)
(select tj.FCUSTID,tj.CustName,tj.Fdate,tj.ArBillNo,tj.fremark,tj.MaterialName,tj.FModel,tj.FSALQTY,tj.FunitName,tj.FTAXPRICE,tj.FRECEIVEAMOUNT,tj.FPAYAMOUNTFOR,tj.Ebalance from #tj tj   
left join #dandutj d on d.tjprice = tj.FTAXPRICE and d.tjfre =tj.FRECEIVEAMOUNT and tj.ArBillNo =d.tjbill
where d.ArBillNo is null)

--回位tj单
create table #yiyongtj (tjbill nvarchar(255))
insert into #yiyongtj(tjbill)
(
	select distinct tjbill from #artj  
)

--合并比较tj单
create table #hebingtj (FCUSTID nvarchar(255),CustNumber nvarchar(255),CustName nvarchar(255),Fdate nvarchar(255),ArBillNo nvarchar(255),fremark nvarchar(255),FMATERIALID nvarchar(255),
MaterialNumber nvarchar(255),MaterialName nvarchar(255),FModel nvarchar(255),FSALQTY float,FunitName nvarchar(255),FTAXPRICE float,FRECEIVEAMOUNT float,FPAYAMOUNTFOR float,Ebalance float,sortid nvarchar(255),tjbill nvarchar(255))
insert into #hebingtj (FCUSTID ,CustNumber ,CustName ,Fdate ,ArBillNo ,fremark,FMATERIALID,MaterialNumber,MaterialName,FModel,FSALQTY,FunitName,FTAXPRICE,FRECEIVEAMOUNT,FPAYAMOUNTFOR,Ebalance ,sortid ,tjbill )
(
	select * from #tj tj
	left join #yiyongtj yi on yi.tjbill =tj.ArBillNo
)

--单独行tj
create table #buhuiweitj (FCUSTID nvarchar(255),CustNumber nvarchar(255),CustName nvarchar(255),Fdate nvarchar(255),ArBillNo nvarchar(255),fremark nvarchar(255),FMATERIALID nvarchar(255),
MaterialNumber nvarchar(255),MaterialName nvarchar(255),FModel nvarchar(255),FSALQTY float,FunitName nvarchar(255),FTAXPRICE float,FRECEIVEAMOUNT float,FPAYAMOUNTFOR float,Ebalance float,sortid nvarchar(255),tjpri float,tjfre float)
insert into #buhuiweitj (FCUSTID ,CustNumber ,CustName ,Fdate ,ArBillNo ,fremark,FMATERIALID,MaterialNumber,MaterialName,FModel,FSALQTY,FunitName,FTAXPRICE,FRECEIVEAMOUNT,FPAYAMOUNTFOR,Ebalance ,sortid ,tjpri )
(
select * from #hebingtj where tjbill is null
)


--删除tj单号，方便二调单合并
alter table #artj drop column tjbill

--合并二调单
create table #huiweihebing (FCUSTID nvarchar(255),CustNumber nvarchar(255),CustName nvarchar(255),Fdate nvarchar(255),ArBillNo nvarchar(255),fremark nvarchar(255),FMATERIALID nvarchar(255),
MaterialNumber nvarchar(255),MaterialName nvarchar(255),FModel nvarchar(255),FSALQTY float,FunitName nvarchar(255),FTAXPRICE float,FRECEIVEAMOUNT float,FPAYAMOUNTFOR float,Ebalance float,sortid nvarchar(255),tjpri float,tjfre float)
insert into #huiweihebing (FCUSTID ,CustNumber ,CustName ,Fdate ,ArBillNo ,fremark,FMATERIALID,MaterialNumber,MaterialName,FModel,FSALQTY,FunitName,FTAXPRICE,FRECEIVEAMOUNT,FPAYAMOUNTFOR,Ebalance ,sortid ,tjpri,tjfre )
(
select FCUSTID ,CustNumber ,CustName ,Fdate ,ArBillNo ,fremark,FMATERIALID,MaterialNumber,MaterialName,FModel,FSALQTY,FunitName,FTAXPRICE,FRECEIVEAMOUNT,FPAYAMOUNTFOR,Ebalance ,sortid,sum(tjprice),sum(tjfre)  from #artj group by FCUSTID ,CustNumber ,CustName ,Fdate ,ArBillNo ,fremark,FMATERIALID,MaterialNumber,MaterialName,FModel,FSALQTY,FunitName,FTAXPRICE,FRECEIVEAMOUNT,FPAYAMOUNTFOR,Ebalance ,sortid 
)

--select * from #huiweihebing where  ArBillNo='AR00003295'

--自增字段，方便去重
create table #zizeng (FCUSTID nvarchar(255),CustNumber nvarchar(255),CustName nvarchar(255),Fdate nvarchar(255),ArBillNo nvarchar(255),fremark nvarchar(255),FMATERIALID nvarchar(255),
MaterialNumber nvarchar(255),MaterialName nvarchar(255),FModel nvarchar(255),FSALQTY float,FunitName nvarchar(255),FTAXPRICE float,FRECEIVEAMOUNT float,FPAYAMOUNTFOR float,Ebalance float,sortid nvarchar(255),tjpri float,tjfre float)
insert into #zizeng (FCUSTID ,CustNumber ,CustName ,Fdate ,ArBillNo ,fremark,FMATERIALID,MaterialNumber,MaterialName,FModel,FSALQTY,FunitName,FTAXPRICE,FRECEIVEAMOUNT,FPAYAMOUNTFOR,Ebalance ,sortid ,tjpri,tjfre )
(
select * from #huiweihebing where tjpri is not null
)

--存储自增
create table #savezizeng (FCUSTID nvarchar(255),CustNumber nvarchar(255),CustName nvarchar(255),Fdate nvarchar(255),ArBillNo nvarchar(255),fremark nvarchar(255),FMATERIALID nvarchar(255),
MaterialNumber nvarchar(255),MaterialName nvarchar(255),FModel nvarchar(255),FSALQTY float,FunitName nvarchar(255),FTAXPRICE float,FRECEIVEAMOUNT float,FPAYAMOUNTFOR float,Ebalance float,sortid nvarchar(255),tjpri float,tjfre float,sum int)
insert into #savezizeng (FCUSTID ,CustNumber ,CustName ,Fdate ,ArBillNo ,fremark,FMATERIALID,MaterialNumber,MaterialName,FModel,FSALQTY,FunitName,FTAXPRICE,FRECEIVEAMOUNT,FPAYAMOUNTFOR,Ebalance ,sortid ,tjpri,tjfre,sum )
(
select *,
row_number()over(partition by sortid,materialnumber,fsalqty order by getdate()) as num
from #zizeng
)


--更新非1 的单价，应收为0
update #savezizeng set tjpri=0 ,tjfre=0 where sum !=1
alter table #savezizeng drop column sum
delete from #huiweihebing where tjpri is not null
insert into #huiweihebing (FCUSTID ,CustNumber ,CustName ,Fdate ,ArBillNo ,fremark,FMATERIALID,MaterialNumber,MaterialName,FModel,FSALQTY,FunitName,FTAXPRICE,FRECEIVEAMOUNT,FPAYAMOUNTFOR,Ebalance ,sortid ,tjpri,tjfre )
(select * from #savezizeng)



update #huiweihebing set tjpri = 0 where tjpri is null
update #huiweihebing set tjfre = 0 where tjfre is null
update #huiweihebing set FTAXPRICE =FTAXPRICE+tjpri,FRECEIVEAMOUNT =FRECEIVEAMOUNT+tjfre

--select * from #huiweihebing where fremark ='期初余额'
create table #zhengchang(FCUSTID int,CustName nvarchar(255), Fdate datetime,ArBillNo nvarchar(255),fremark nvarchar(255),MaterialName nvarchar(255), fmodel nvarchar(255),FSALQTY float,funitname nvarchar(255),FTAXPRICE float,FRECEIVEAMOUNT float,FPAYAMOUNTFOR float,Ebalance float )
insert into #zhengchang(FCUSTID,CustName,Fdate,ArBillNo,fremark,MaterialName,fmodel,FSALQTY,funitname,FTAXPRICE,FRECEIVEAMOUNT,FPAYAMOUNTFOR,Ebalance)
(select FCUSTID,CustName,Fdate,ArBillNo,fremark,MaterialName, fmodel,FSALQTY,funitname,FTAXPRICE,FRECEIVEAMOUNT,FPAYAMOUNTFOR,Ebalance from #huiweihebing
union all
select ts.FCONTACTUNIT as FCUSTID,tk.FNAME as CustName, CONVERT(VARCHAR(19),ts.FDATE,102) as Fdate ,ts.FBILLNO as ArBillNo,ts.fremark,
'' as MaterialName, ''as fmodel,
0 as qty,''as funitname,
0 as FTAXPRICE,
0 as FRECEIVEAMOUNT, 
convert( decimal(18,2), ts.FRECEIVEAMOUNTFOR) as FPAYAMOUNTFOR,0 as ebalace
from T_AR_RECEIVEBILL   ts
left join T_BD_CUSTOMER_L tk on (tk.FCUSTID= ts.FCONTACTUNIT) 
where ts.fbilltypeid ='36cf265bd8c3452194ed9c83ec5e73d2'  and ts.FDATE>='{2}' and ts.FDATE<='{3}'
union all
(select tt.FCONTACTUNIT as cid,tk.FNAME as cname, CONVERT(VARCHAR(19),tt.FDATE,102) as Fdate ,tt.FBILLNO as fbillno,tt.FREMARK,
'' as wname,''as fmodel, 0 as QTY,''as funitname,0 as FTAXPRICE,0 as FRECEIVEAMOUNT, -(tt.FREFUNDTOTALAMOUNTFOR)  as FPAYAMOUNTFOR,0 as ebalace
from  T_AR_REFUNDBILL	tt	
left join T_BD_CUSTOMER_L tk on (tk.FCUSTID= tt.FCONTACTUNIT)
where tt.FBILLTYPEID ='ef06f87d394a462d9f96cb2397803372' and tt.FDATE>='{2}' and tt.FDATE<='{3}'
)
union all
select FCUSTID,CustName,Fdate,ArBillNo,fremark,MaterialName,FModel,FSALQTY,FunitName,FTAXPRICE,FRECEIVEAMOUNT,FPAYAMOUNTFOR,Ebalance from  #buhuiweitj
union all
select  * from #savedandutj
)




create table #qichu (fcustid nvarchar(255),fname nvarchar(250),fbeginbalancefor float, fdate1 nvarchar(255))
insert into #qichu (fcustid,fname,fbeginbalancefor ,fdate1)
(select   tk.FCUSTID,FNAME,qichu.FBEGINBALANCEFOR, CONVERT(VARCHAR(19),qichu.FSTARTDATE,102)  from  T_BD_CUSTOMER_L tk 
left join T_AR_CONTACTBAL qichu on (tk.FCUSTID= qichu.FCONTACTUNIT) 
where qichu.FSTARTDATE=qichu.FENDDATE and qichu.FSTARTDATE='2018-01-01 00:00:00.000')




 --不合并
 create table #buhebing 
 (cid int ,cname nvarchar(255),fdate datetime,fbillno nvarchar(255),wname nvarchar(255),
 qty float,ftaxprice float,freceiveamount float,fpayamountfor float ,ebalace float)
 insert into #buhebing(cid ,cname,fdate ,fbillno,wname,qty,ftaxprice,freceiveamount,fpayamountfor,ebalace)
 (
 select    --原单调价单不合并
	t0.FCUSTOMERID as cid,
	tk.FNAME as cname, 
	t0.fdate , 
	T0.FBILLNO ,
	t1.FNAME as wname, 
	t3.FSALQTY as QTY  , 
	t4.FTAXPRICE,
	fsalqty*ftaxprice as FRECEIVEAMOUNT,
	 0 as FPAYAMOUNTFOR,0 as ebalace
	from t_AR_receivable t0
	left join T_BD_CUSTOMER_L tk on (tk.FCUSTID= t0.FCUSTOMERID)
	left join T_BD_CUSTOMER tn on tn.fcustid = tk.fcustid
	left join t_AR_receivableEntry      t4  on(t4.fid = t0.fid) 
	left join t_AR_receivableEntry_O       t3  on(t4.FENTRYID = t3.FENTRYID) 
	left join T_BD_MATERIAL_L      t1  on(t4.FMATERIALID = t1.FMATERIALID)
	left join T_BD_MATERIAL tm on tm.FMATERIALID = t1.FMATERIALID
	left join 	T_BD_UNIT tw on (tw.FUNITID=t3.FSALUNITID)  
	left join T_BD_UNIT_L tw1 on (tw1.FUNITID = tw.FUNITID) 
	where  t0.FBILLTYPEID = '180ecd4afd5d44b5be78a6efe4a7e041'
union all
	select 
	
	t0.FCUSTOMERID as cid,
	tk.FNAME as cname, 
	t0.fdate ,
	T0.FBILLNO ,
	t1.FNAME as wname, 
	round (te.FNOTAXAMOUNTFOR/te.FTAXPRICE,1) as  QTY ,   
	te.FTAXPRICE,
	te.FNOTAXAMOUNTFOR as FRECEIVEAMOUNT,
	 0 as FPAYAMOUNTFOR,0 as ebalace
	from T_AR_RECEIVABLE t0
	left join T_BD_CUSTOMER_L tk on (tk.FCUSTID= t0.FCUSTOMERID)
	left join T_BD_CUSTOMER tn on tn.fcustid = tk.fcustid
	left join t_AR_receivableEntry  te  on(te.fid = t0.fid) 
	left join T_BD_MATERIAL_L      t1  on(te.FMATERIALID = t1.FMATERIALID)
	left join T_BD_MATERIAL tm on tm.FMATERIALID = t1.FMATERIALID
	left join t_AR_receivableEntry_O       t3  on(te.FENTRYID = t3.FENTRYID) 
	left join T_AR_RECEIVABLEENTRY_LK  arl on te.FENTRYID =  arl.FENTRYID and FSTABLENAME = 't_AR_receivableEntry'
	left join 	T_BD_UNIT tw on (tw.FUNITID=t3.FSALUNITID) 
	left join T_BD_UNIT_L tw1 on (tw1.FUNITID = tw.FUNITID)
	where  t0.FBILLTYPEID = '5a41e09cdf400e' 
  --原单调价单不合并 ts.FCONTACTUNIT  ts.FCONTACTUNIT

	union all --收款退款
select ts.FCONTACTUNIT as cid,tk.FNAME as cname, CONVERT(VARCHAR(19),ts.FDATE,102) as Fdate ,ts.FBILLNO as fbillno,
'' as wname, 
0 as qty,
0 as FTAXPRICE,
0 as FRECEIVEAMOUNT, 
convert( decimal(18,2), ts.FRECEIVEAMOUNTFOR) as FPAYAMOUNTFOR,0 as ebalace
from T_AR_RECEIVEBILL   ts
left join T_BD_CUSTOMER_L tk on (tk.FCUSTID= ts.FCONTACTUNIT) 
where ts.fbilltypeid ='36cf265bd8c3452194ed9c83ec5e73d2' 
union all
(select tt.FCONTACTUNIT as cid,tk.FNAME as cname, CONVERT(VARCHAR(19),tt.FDATE,102) as Fdate ,tt.FBILLNO as fbillno,
'' as wname,0 as QTY,0 as FTAXPRICE,0 as FRECEIVEAMOUNT, -(tt.FREFUNDTOTALAMOUNTFOR)  as FPAYAMOUNTFOR,0 as ebalace
from  T_AR_REFUNDBILL	tt	
left join T_BD_CUSTOMER_L tk on (tk.FCUSTID= tt.FCONTACTUNIT)
where tt.FBILLTYPEID ='ef06f87d394a462d9f96cb2397803372' 
)
	--收款退款
union all
select fcustid as cid,fname as cname, CONVERT(datetime,FDATE1,111)  as fdate,''as fbillno,'' as wname,0 as qty,0 as ftaxprice,0 as FRECEIVEAMOUNT,0 as FPAYAMOUNTFOR,
fbeginbalancefor as ebalace from #qichu

)

create table #saveqichu(custid int ,custname nvarchar(255),fdate datetime ,arbill nvarchar(255),fremark nvarchar(255),mater nvarchar(255),fmodel nvarchar(255),fsalqty float,funitname nvarchar(255),ftaxprice float,freceiveamount float,fpay float,ebalace float)
insert into #saveqichu(custid,custname,fdate,arbill,fremark,mater,fmodel,fsalqty,funitname,ftaxprice,freceiveamount,fpay,ebalace)
(select cid,cname as custname,CONVERT(datetime,'{2}'  /*{2}*/,    102)as fdate,''as arbillno,'期初余额'as fremark,''as materialname,''as fmodel,0as fsalqty,
  ''as funitname,0as ftaxprice,0as freceiveamount,0as fpayamountfor,sum(ebalace)+sum(freceiveamount)-sum(fpayamountfor)as ebalance
   from #buhebing 
   where fdate>='2018/1/1 0:00:00' and fdate<'{2}' --2018 {2}
   group by cname,cid
)

create table #guolvqichu (custid int ,custname nvarchar(255),fdate datetime ,arbill nvarchar(255),fremark nvarchar(255),mater nvarchar(255),fmodel nvarchar(255),fsalqty float,funitname nvarchar(255),ftaxprice float,freceiveamount float,fpay float,ebalace float)
insert into #guolvqichu(custid,custname,fdate,arbill,fremark,mater,fmodel,fsalqty,funitname,ftaxprice,freceiveamount,fpay,ebalace)
(select fcustid,fname,fdate1, ''as arbill ,''as fremark,''as mater,''as fmodel,''as fsalqty,''as funitname,''as ftaxprice,''as freceiveamount,''as fpay,fbeginbalancefor as ebalace from #qichu union 
select * from #saveqichu 
)
create table #saveguolv (custid int ,custname nvarchar(255),fdate datetime ,arbill nvarchar(255),fremark nvarchar(255),mater nvarchar(255),fmodel nvarchar(255),fsalqty float,funitname nvarchar(255),ftaxprice float,freceiveamount float,fpay float,ebalace float,num int)
insert into #saveguolv(custid,custname,fdate,arbill,fremark,mater,fmodel,fsalqty,funitname,ftaxprice,freceiveamount,fpay,ebalace,num)
(select *, row_number()over(partition by custid order by fdate desc) as num from #guolvqichu)


delete  from #saveguolv where fremark='' and num =2

create table #qichuzhengchang (custid int ,custname nvarchar(255),fdate datetime ,arbill nvarchar(255),fremark nvarchar(255),mater nvarchar(255),fmodel nvarchar(255),fsalqty float,funitname nvarchar(255),ftaxprice float,freceiveamount float,fpay float,ebalace decimal(18,2))
insert into #qichuzhengchang (custid,custname,fdate,arbill,fremark,mater,fmodel,fsalqty,funitname,ftaxprice,freceiveamount,fpay,ebalace)
(select custid,custname,fdate,arbill,fremark,mater,fmodel,fsalqty,funitname,ftaxprice,freceiveamount,fpay,ebalace from #saveguolv
union 
select * from #zhengchang
)
update #qichuzhengchang set fpay =0 where fpay is null
update #qichuzhengchang set ebalace =0 where ebalace is null


--期初+正常+合计
insert into #qichuzhengchang (custid,custname,fdate,arbill,fremark,mater,fmodel,fsalqty,funitname,ftaxprice,freceiveamount,fpay,ebalace)
(select custid,custname,'{3}'/*{3}*/as fdate,''as arbill,'合计'as fremark,''as mater,''as fmodel,''as fsalqty,''as funitname,''as ftaxprice,sum (freceiveamount) as freceiveamount ,sum(fpay) as fpay,sum(ebalace)+sum(freceiveamount)-sum(fpay)as ebalace from #qichuzhengchang  group by custid,custname 
)


create table #ora (forgid  decimal(18,2),fname nvarchar(255))   --天邦万豪
insert into #ora (forgid,fname)
(select distinct FORGID,FNAME from T_ORG_ORGANIZATIONS_L)

create table #bumen (fcustid nvarchar(255),fdatavalue nvarchar(255),fentryid nvarchar(255)) 
insert into #bumen (fcustid,fdatavalue,fentryid)
(select distinct tke.FCUSTID,tg.FDATAVALUE,tg.FENTRYID from T_BD_CUSTOMER tke
left join T_BAS_ASSISTANTDATAENTRY_L  tg on tke.FCOMPANYCLASSIFY =tg.FENTRYID and tg.FLOCALEID = 2052
)
--select * from #gong
create table #pianqu (fcustid nvarchar(255),fdateavalue nvarchar(255),fdatavalue nvarchar(255),fentryid1 nvarchar(255),fentryid2 nvarchar(255))
insert into #pianqu (fcustid,fdateavalue,fentryid1,fdatavalue,fentryid2)
(select distinct tbu.* ,tg.FDATAVALUE ,tg.FENTRYID from #bumen tbu
left join  T_BD_CUSTOMER tke on tke.FCUSTID = tbu.fcustid
left join T_BAS_ASSISTANTDATAENTRY_L tg on tg.FENTRYID =tke.FCOMPANYNATURE and tg.FLOCALEID = 2052)


create table #gong (fcustid nvarchar(255),fdateavalue nvarchar(255),fdatavalue nvarchar(255),gong nvarchar(255),fentryid1 nvarchar(255),fentryid2 nvarchar(255),fentryid3 nvarchar(255))
insert into #gong (fcustid,fdateavalue,fdatavalue,gong,fentryid1,fentryid2,fentryid3 )
(select distinct pian.fcustid,pian.fdateavalue,pian.fdatavalue ,tg.FDATAVALUE ,pian.fentryid1,pian.fentryid2,tg.FENTRYID from #pianqu pian
left join  T_BD_CUSTOMER tke on tke.FCUSTID = pian.fcustid
left join T_BAS_ASSISTANTDATAENTRY_L tg on tg.FENTRYID =tke.FCOMPANYSCALE and tg.FLOCALEID = 2052)


create table #test2( FCUSTID int,CustName nvarchar(255),Fdate nvarchar(255),ArBillNo nvarchar(255),fremark nvarchar(255),MaterialName nvarchar(255),FModel nvarchar(255),FSALQTY float,FunitName nvarchar(255),FTAXPRICE float,FRECEIVEAMOUNT float,FPAYAMOUNTFOR float,Ebalance float,fshortname nvarchar(255), FCOMPANYCLASSIFY nvarchar(255),FCOMPANYNATURE nvarchar(255),FCOMPANYSCALE nvarchar(255),fname nvarchar(255),sortid nvarchar(255), fdate1 datetime,gsid nvarchar(255),bmid nvarchar(255),pqid nvarchar(255),forid int)
insert into #test2(FCUSTID,CustName ,Fdate ,ArBillNo ,fremark,MaterialName,FModel,FSALQTY,FunitName,FTAXPRICE,FRECEIVEAMOUNT ,FPAYAMOUNTFOR ,Ebalance ,fshortname,FCOMPANYCLASSIFY,FCOMPANYNATURE,FCOMPANYSCALE,fname,sortid,fdate1,gsid,bmid,pqid,forid)
(
select  t7.custid,t7.custname,CONVERT(VARCHAR(19),FDATE,102),t7.arbill,t7.fremark,t7.mater,t7.fmodel,t7.fsalqty,t7.funitname,t7.ftaxprice,t7.freceiveamount,t7.fpay,t7.ebalace, t.fshortname,g.fdateavalue,g.fdatavalue ,g.gong,o.fname,''as sortid ,CONVERT(datetime,fdate,111),g.fentryid1,g.fentryid2,g.fentryid3,o.forgid from  #qichuzhengchang  t7
left join T_BD_CUSTOMER c on c.FCUSTID=t7.custid
left join #ora o on o.FORGID = c.FUSEORGID 
left join #gong g on g.fcustid =t7.custid 
left join   T_BD_CUSTOMER_L t on t.FCUSTID =t7.custid 
)


update #test2 set fremark ='期初余额' where ArBillNo ='' and fremark ='' 
update #test2 set sortid ='a' where fremark='期初余额'
update #test2 set sortid ='z' where fremark='合计'
update #test2 set sortid ='b' where fremark!='合计' and fremark!='期初余额'
update #test2 set FSALQTY = null where FSALQTY =0
update #test2 set FTAXPRICE = null where FTAXPRICE =0
update #test2 set FRECEIVEAMOUNT = null where FRECEIVEAMOUNT =0
update #test2 set FPAYAMOUNTFOR = null where FPAYAMOUNTFOR =0
update #test2 set Ebalance = null where Ebalance =0
update #test2 set FSALQTY = null where ArBillNo like 'tj%'




create table #noqichu (fcustid int, custname nvarchar(255))
insert into #noqichu (fcustid, custname)
(select FCUSTID, custname from #test2 where fremark='期初余额')

create table #allper (fcustid int, custname nvarchar(255))
insert into #allper (fcustid, custname)
(select distinct FCUSTID, custname from #test2 )

create table #noqichusave (fcustid int ,acustname nvarchar(255) ,bcustname nvarchar(255))
insert into #noqichusave(fcustid,acustname,bcustname)
(

select a.fcustid,a.custname, b.custname from #allper a
left join #noqichu b on a.FCUSTID=b.fcustid
)

insert into #test2(FCUSTID,CustName ,Fdate ,ArBillNo ,fremark,MaterialName,FModel,FSALQTY,FunitName,FTAXPRICE,FRECEIVEAMOUNT ,FPAYAMOUNTFOR ,Ebalance ,fshortname,FCOMPANYCLASSIFY,FCOMPANYNATURE,FCOMPANYSCALE,fname,sortid,fdate1,gsid,bmid,pqid,forid)
(
select fcustid,acustname ,''as fdate ,''as arbill,'期初余额' as fremark ,null as materialname,null as fmodel,null as fsalqty ,null as funitname,null ,null as fre,null as fpay ,'0'as eba,''as fsh,'','','','','','','','','','' from #noqichusave where bcustname is null
)

create table #oneqichu (fdate nvarchar(255))
insert into #oneqichu (fdate)
(
select distinct fdate from #test2  where fremark='期初余额' and #test2.Fdate !=''
)

update #test2 set #test2.fdate=#oneqichu.fdate from #test2,#oneqichu where fremark='期初余额' and #test2.Fdate =''








select * 
 ,
 ROW_NUMBER() OVER(ORDER BY CustName,sortid,fdate1) FIDENTITYID 
  into {1}
from #test2
抢我糖还想跑
关注
专栏目录
SQLserver课程源代码
10-18
关于SqlServer数据库源代码，可以供学习参考，进行使用。
SQL Server 2008宝典源代码
04-28
SQL Server 2008宝典源代码，经典源代码 完全开放，学习的利器
SQL server 各章源代码
05-25
USE student GO CREATE TABLE 教材 (教材代码 char(9) CONSTRAINT pk_jcdm PRIMARY KEY, 教材名称 varchar(30), 书号 char(12), 出版社 varchar(30), 版本 char(10), 单价 tinyint ) GO
获取sqlserver数据库存储过程源代码
12-31
获取SQLServer数据库中所有存储过程的源代码，可以对代码进行搜索，也可以把源代码保存，非常实用。 可根据需要修改。
SQLServer 数据库生成工具及源代码
11-19
用C++写的小软件，基于MFC框架，通过该小软件， 我们可以选择数据库文档快速生成指定数据库，只要事先写好数据库结构文档。附：软件/源码/数据库结构文档 。对学习和使用SQLServer有一定帮助，适合新手，高手请无视
大学生就业信息网站(Asp.net+SQLServer).zip
03-23
通过Asp.net的Web应用程序开发，可以创建一个用户友好的界面，结合SQLServer数据库来存储和管理大量的招聘信息和用户数据。 【描述】"源码参考，亲测有效，欢迎下载"表明这个压缩包中包含了完整的源代码，可供学习...
VC+SqlServer实现：学生信息管理系统设计与源码解析
"学生信息管理系统（论文+源代码）是基于Windows平台开发的，采用VC++作为编程语言，结合SQL Server数据库，实现了对学生信息管理的基本功能。这个系统由西北农林科技大学信息工程学院的学生赵凯在计算机科学与技术...
顶岗实习管理系统—源码
05-16
【顶岗实习管理系统—源码】是一个基于.NET技术开发的管理软件，主要用于高校或职业教育机构对学生顶岗实习过程的全面管理。在这个系统中，开发者利用.NET框架的强大功能，结合数据库技术，构建了一个高效、易用的...
基于Java+JSP的实习管理系统毕业设计实现+源码毕业设计实现+源码.rar
04-20
通常会使用MySQL等关系型数据库来存储实习信息、用户数据和操作记录等。通过Java的JDBC（Java Database Connectivity）接口，可以方便地进行数据的增删改查操作，保证数据的准确性和一致性。 在源码结构方面，一个...
Java/JSP实习管理系统源码及文档工具包
资源摘要信息:"毕业设计javajsp本科生实习管理系统sqlserver-qkr源码含文档工具包"是一个完整的软件开发项目，它为本科生提供了一个实习管理的平台。该项目利用Java语言开发，结合了JSP页面技术、SQL Server数据库...
Sql Server 的SQL语句案例源代码
11-27
教程可以参考http://user.qzone.qq.com/824252187/2
sql server 2008 从入门到精通的源代码.zip
03-29
sql sever 2008的关系型数据库对于一些大的网站来说越来越受到青睐。
sql server 2008技术内幕 源代码
08-06
NULL 博文链接：https://pmandy-163-com.iteye.com/blog/2087061
SQLServer范例开发大全（22集）
07-23
教程名称：SQL Server范例开发大全(22集)课程目录：【】SQL Server范例开发大全-数据备份【】SQL Server范例开发大全第1章【】SQL Server范例开发大全第2章【】SQL Server范例开发大全第3章【】SQL Server范例开发大全第4章【】SQL Server范例开发大全第5章【】SQL 资源太大，传百度网盘了，链接在附件中，有需要的同学自取。
SQLServer
03-14
SQLServer
python:实现连接sqlserver数据库 (附完整源码)
希望我的博客，能帮上你解决学习中工作中所遇到的问题
04-11 804
python:实现连接sqlserver数据库 (附完整源码)
asp.net+sqlserver装修管理系统-计算机毕业设计源码10327
最新发布
vx2_Biye_Design的博客
06-16 455
用户：资料修改，装修交流，公告查询，评论，装修风格，评论 管理员：用户管理，资讯管理，交流管理，装修风格，风格分类，装修预约，登录
c++访问SQL SERVER 源代码
11-18
VS2008工具，C++ 访问SQL SERVER 2008的一个Demo 源代码，在访问之前请确保该数据库存在，不然得自己对应去修改数据库名称
c语言连接sqlserver的代码
weixin_35755640的博客
12-18 693
下面是一个示例代码，它使用 ODBC API 在 C 语言中连接到 SQL Server 数据库： ``` #include <stdio.h> #include <sql.h> #include <sqlext.h> int main() { SQLHENV env; SQLHDBC dbc; SQLHSTMT stmt; SQLRETURN ...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值