小程序视图创建

sql 代码如下：

GO 
CREATE VIEW vcgbillddList
as
SELECT acode,provider, aguid AS guid_m,
       CONVERT(VARCHAR(10),date_yw,120) date_yw,
       ywname AS ywaname, addr, qty, bj, js, checkname,remark,
       CASE WHEN status&2>=2 THEN 2 ELSE 1 END AS status
 FROM dbo.vcgbilldd_m WHERE status<>65536

 GO 

 create VIEW vcgbillddDetailList
 as
 SELECT aguid,pxxh,guid_m,acode,aname,amodel,
        qty,price,sy
  FROM dbo.vcgbilldd_d

GO 

CREATE VIEW vcgbillshList
as
SELECT acode,provider, aguid AS guid_m,
       CONVERT(VARCHAR(10),date_yw,120) date_yw,
       ywaname, addr, qty, bj, js, checkname,remark,
       CASE WHEN status&2>=2 THEN 2 ELSE 1 END AS status
 FROM dbo.vcgbillsh_m WHERE status<>65536

 GO 

 create VIEW vcgbillshDetailList
 as
 SELECT aguid,pxxh,guid_m,acode,aname,amodel,
        qty,price,sy
  FROM dbo.vcgbillsh_d

 GO

 CREATE VIEW vcgbillshreList
as
SELECT acode,provider, aguid AS guid_m,
       CONVERT(VARCHAR(10),date_yw,120) date_yw,
       ywaname, addr, qty, bj, js, checkname,remark,
       CASE WHEN status&2>=2 THEN 2 ELSE 1 END AS status
 FROM dbo.vcgbillshre_m WHERE status<>65536

 GO 

  create VIEW vcgbillshreDetailList
 as
 SELECT aguid,pxxh,guid_m,acode,aname,amodel,
        qty,price,sy
  FROM dbo.vcgbillshre_d

  GO 


  CREATE VIEW vcgproviderReportList
AS 
SELECT acode,aname,aname_long,addr,
       yw_aname, mobile, cwmoney_receive,
	   cwmoney_payup, 
	   cwmoney_pre_sum,cwmoney_debt,cwmoney_discount,cwmoney_refund,
	   CASE WHEN cwmoney_debt<=0 THEN 1 ELSE 2 END status,
	   linkpeo
 FROM dbo.vcgprovider WHERE status<>65536

GO

CREATE VIEW vkcplaceqty
AS 
SELECT a.aguid, a.guid_ckplace, a.guid_sp, a.batchno, 
       a.qty_init, a.qty_in, a.qty_out,  ISNULL(a.qty_init,0)+ISNULL(a.qty_in,0)-ISNULL(a.qty_out,0) qty_end, a.price_in,
       b.aname placeName, c.acode, c.aname, c.amodel, c.pri_pf, c.pri_sp, c.pri_cb, ISNULL(c.qty_kcmax,0) qty_kcmax,
	   ISNULL(c.qty_kcmin,0) qty_kcmin, c.kind_name,
	   CASE WHEN ( 
	   ( (ISNULL(a.qty_init,0)+ISNULL(a.qty_in,0)-ISNULL(a.qty_out,0)) > c.qty_kcMax 
	    OR (ISNULL(a.qty_init,0)+ISNULL(a.qty_in,0)-ISNULL(a.qty_out,0))<qty_kcmin)
	   AND (ISNULL(c.qty_kcmin,0)>0) AND (ISNULL(c.qty_kcmax,0)>0)
	   ) THEN 2 ELSE 1 END status
 FROM kcplaceqty a 
INNER JOIN dbo.kcplace b ON a.guid_ckplace = b.aguid
INNER JOIN dbo.vkcsp c ON a.guid_sp = c.aguid
WHERE c.status<>65536 AND b.status<>65536

GO 

CREATE VIEW vprdbillList
as
SELECT acode, aguid AS guid_m,
       CONVERT(VARCHAR(10),date_yw,120) date_yw,
       workname AS ywaname, ddcode, checkname,remark,
       CASE WHEN status&2>=2 THEN 2 ELSE 1 END AS status,
	   CONVERT(VARCHAR(10),date_end,120) date_end,
	   qty,sy
 FROM dbo.vprdbill_m WHERE status<>65536

 GO 

  create VIEW vprdbillDetailList
 as
 SELECT aguid,pxxh,guid_m,acode,aname,amodel,
        qty,price,sy
  FROM dbo.vprdbill_d

  GO 

  CREATE VIEW vprdbillflList
as
SELECT acode, aguid AS guid_m,
       CONVERT(VARCHAR(10),date_yw,120) date_yw,
       workname AS ywaname,ddcode, checkname,remark,
       CASE WHEN status&2>=2 THEN 2 ELSE 1 END AS status,
	   qty,sy
 FROM dbo.vprdbillfl_m WHERE status<>65536

GO 

 create VIEW vprdbillflDetailList
 as
 SELECT aguid,pxxh,guid_m,acode,aname,amodel,
        qty,price,sy
  FROM dbo.vprdbillfl_d

  GO 

  CREATE VIEW vprdbilloutreList
as
SELECT acode, aguid AS guid_m,
       CONVERT(VARCHAR(10),date_yw,120) date_yw,
       workname AS ywaname, dd_code, checkname,remark,
       CASE WHEN status&2>=2 THEN 2 ELSE 1 END AS status,
	   qty,sy
 FROM dbo.vprdbilloutre_m WHERE status<>65536

 GO

  create VIEW vprdbilloutreDetailList
 as
 SELECT aguid,pxxh,guid_m,acode,aname,amodel,
        qty,price,sy
  FROM dbo.vprdbilloutre_d

  GO

  CREATE VIEW vprdbillinList
as
SELECT acode, aguid AS guid_m,
       CONVERT(VARCHAR(10),date_yw,120) date_yw,
       workname AS ywaname, dd_code, checkname,remark,
       CASE WHEN status&2>=2 THEN 2 ELSE 1 END AS status,
	   qty,sy
 FROM dbo.vprdbillin_m WHERE status<>65536
 GO

  create VIEW vprdbillinDetailList
 as
 SELECT aguid,pxxh,guid_m,acode,aname,amodel,
        qty,price,sy
  FROM dbo.vprdbillin_d

  GO

  CREATE VIEW vxsbillddList
as
SELECT acode,khaname_long, aguid AS guid_m,
       CONVERT(VARCHAR(10),date_yw,120) date_yw,
       ywaname, addr, qty, bj, js, checkname,remark,
       CASE WHEN status&2>=2 THEN 2 ELSE 1 END AS status
 FROM dbo.vxsbilldd_m WHERE status<>65536

 GO
 

  create VIEW vxsbillddDetailList
 as
 SELECT aguid,pxxh,guid_m,acode,aname,amodel,
        qty,price,sy
  FROM dbo.vxsbilldd_d

go
 

 CREATE VIEW vxsbillfhList
as
SELECT acode,khaname_long, aguid AS guid_m,
       CONVERT(VARCHAR(10),date_yw,120) date_yw,
       ywaname, addr, qty, bj, js, checkname,ddcode,remark,
       CASE WHEN status&2>=2 THEN 2 ELSE 1 END AS status
 FROM dbo.vxsbillfh_m WHERE status<>65536
 GO

  create VIEW vxsbillfhDetailList
 as
 SELECT aguid,pxxh,guid_m,acode,aname,amodel,
        qty,price,sy
  FROM dbo.vxsbillfh_d
GO


  CREATE VIEW vxsbillfhreList
as
SELECT acode,khaname_long, aguid AS guid_m,
       CONVERT(VARCHAR(10),date_yw,120) date_yw,
       ywaname, addr, qty, bj, js, checkname,ddcode,remark,
       CASE WHEN status&2>=2 THEN 2 ELSE 1 END AS status
 FROM dbo.vxsbillfhre_m WHERE status<>65536

 GO
 
  create VIEW vxsbillfhreDetailList
 as
 SELECT aguid,pxxh,guid_m,acode,aname,amodel,
        qty,price,sy
  FROM dbo.vxsbillfhre_d


go