cs.sql.sql

代码齐全

ALTER TABLE [dbo].[储运出库明细] WITH NOCHECK ADD 
	CONSTRAINT [DF__储运出库明细__表单号__31B762FC] DEFAULT (0) FOR [表单号],
	CONSTRAINT [DF__储运出库明细__商品编码__32AB8735] DEFAULT (0) FOR [商品编码],
	CONSTRAINT [DF__储运出库明细__商品名称__339FAB6E] DEFAULT (0) FOR [商品名称],
	CONSTRAINT [DF__储运出库明细__单位__3493CFA7] DEFAULT (0) FOR [单位],
	CONSTRAINT [DF__储运出库明细__出库数量__3587F3E0] DEFAULT (0) FOR [出库数量],
	CONSTRAINT [DF__储运出库明细__规格__367C1819] DEFAULT (0) FOR [规格],
	CONSTRAINT [DF__储运出库明细__备注__37703C52] DEFAULT (0) FOR [备注]
GO

ALTER TABLE [dbo].[储运库存表头] WITH NOCHECK ADD 
	CONSTRAINT [DF__储运库存表头__表单号__2180FB33] DEFAULT (0) FOR [表单号],
	CONSTRAINT [DF__储运库存表头__部门__22751F6C] DEFAULT (0) FOR [部门],
	CONSTRAINT [DF__储运库存表头__日期__236943A5] DEFAULT (0) FOR [日期],
	CONSTRAINT [DF__储运库存表头__审核人__245D67DE] DEFAULT (0) FOR [审核人],
	CONSTRAINT [DF__储运库存表头__录入员__25518C17] DEFAULT (0) FOR [录入员],
	CONSTRAINT [DF__储运库存表头__入库人__2645B050] DEFAULT (0) FOR [入库人],
	CONSTRAINT [DF__储运库存表头__出入库标志__2739D489] DEFAULT (0) FOR [出入库标志],
	CONSTRAINT [DF__储运库存表头__确认标志__282DF8C2] DEFAULT (0) FOR [确认标志]
GO

ALTER TABLE [dbo].[储运库存主档] WITH NOCHECK ADD 
	CONSTRAINT [DF__储运库存主档__商品编码__395884C4] DEFAULT (0) FOR [商品编码],
	CONSTRAINT [DF__储运库存主档__商品名称__3A4CA8FD] DEFAULT (0) FOR [商品名称],
	CONSTRAINT [DF__储运库存主档__单位__3B40CD36] DEFAULT (0) FOR [单位],
	CONSTRAINT [DF__储运库存主档__日期__3C34F16F] DEFAULT (0) FOR [日期],
	CONSTRAINT [DF__储运库存主档__昨日库存__3D2915A8] DEFAULT (0) FOR [昨日库存],
	CONSTRAINT [DF__储运库存主档__今日入库__3E1D39E1] DEFAULT (0) FOR [今日入库],
	CONSTRAINT [DF__储运库存主档__今日出库__3F115E1A] DEFAULT (0) FOR [今日出库],
	CONSTRAINT [DF__储运库存主档__今日库存__40058253] DEFAULT (0) FOR [今日库存],
	CONSTRAINT [DF__储运库存主档__规格__40F9A68C] DEFAULT (0) FOR [规格],
	CONSTRAINT [DF__储运库存主档__确认标志__41EDCAC5] DEFAULT (0) FOR [确认标志]
GO

ALTER TABLE [dbo].[储运入库明细] WITH NOCHECK ADD 
	CONSTRAINT [DF__储运入库明细__表单号__2A164134] DEFAULT (0) FOR [表单号],
	CONSTRAINT [DF__储运入库明细__商品编码__2B0A656D] DEFAULT (0) FOR [商品编码],
	CONSTRAINT [DF__储运入库明细__商品名称__2BFE89A6] DEFAULT (0) FOR [商品名称],
	CONSTRAINT [DF__储运入库明细__单位__2CF2ADDF] DEFAULT (0) FOR [单位],
	CONSTRAINT [DF__储运入库明细__入库数量__2DE6D218] DEFAULT (0) FOR [入库数量],
	CONSTRAINT [DF__储运入库明细__规格__2EDAF651] DEFAULT (0) FOR [规格],
	CONSTRAINT [DF__储运入库明细__备注__2FCF1A8A] DEFAULT (0) FOR [备注]
GO

ALTER TABLE [dbo].[合同档案] WITH NOCHECK ADD 
	CONSTRAINT [DF__合同档案__编号__6A30C649] DEFAULT (0) FOR [编号],
	CONSTRAINT [DF__合同档案__客户编码__6B24EA82] DEFAULT (0) FOR [客户编码],
	CONSTRAINT [DF__合同档案__签订日期__6C190EBB] DEFAULT (0) FOR [签订日期],
	CONSTRAINT [DF__合同档案__签订地点__6D0D32F4] DEFAULT (0) FOR [签订地点],
	CONSTRAINT [DF__合同档案__商品规格__6E01572D] DEFAULT (0) FOR [商品规格],
	CONSTRAINT [DF__合同档案__包装__6EF57B66] DEFAULT (0) FOR [包装],
	CONSTRAINT [DF__合同档案__交货日期__6FE99F9F] DEFAULT (0) FOR [交货日期],
	CONSTRAINT [DF__合同档案__交货地点__70DDC3D8] DEFAULT (0) FOR [交货地点],
	CONSTRAINT [DF__合同档案__费用负担__71D1E811] DEFAULT (0) FOR [费用负担],
	CONSTRAINT [DF__合同档案__质量标准__72C60C4A] DEFAULT (0) FOR [质量标准],
	CONSTRAINT [DF__合同档案__付款方式__73BA3083] DEFAULT (0) FOR [付款方式],
	CONSTRAINT [DF__合同档案__终止日期__74AE54BC] DEFAULT (0) FOR [终止日期],
	CONSTRAINT [DF__合同档案__确认标志__75A278F5] DEFAULT (0) FOR [确认标志],
	CONSTRAINT [DF__合同档案__终止标志__76969D2E] DEFAULT (0) FOR [终止标志]
GO

ALTER TABLE [dbo].[合同商品明细] WITH NOCHECK ADD 
	CONSTRAINT [DF__合同商品明细__合同编号__787EE5A0] DEFAULT (0) FOR [合同编号],
	CONSTRAINT [DF__合同商品明细__商品编码__797309D9] DEFAULT (0) FOR [商品编码],
	CONSTRAINT [DF__合同商品明细__商品名称__7A672E12] DEFAULT (0) FOR [商品名称],
	CONSTRAINT [DF__合同商品明细__数量__7B5B524B] DEFAULT (0) FOR [数量],
	CONSTRAINT [DF__合同商品明细__单位__7C4F7684] DEFAULT (0) FOR [单位],
	CONSTRAINT [DF__合同商品明细__单价__7D439ABD] DEFAULT (0) FOR [单价],
	CONSTRAINT [DF__合同商品明细__税率__7E37BEF6] DEFAULT (0) FOR [税率]
GO

ALTER TABLE [dbo].[客户明细表] WITH NOCHECK ADD 
	CONSTRAINT [DF_客户明细表_表单号] DEFAULT (0) FOR [表单号],
	CONSTRAINT [DF__客户明细表__地区__4AB81AF0] DEFAULT (0) FOR [地区],
	CONSTRAINT [DF_客户明细表_客户编码] DEFAULT (0) FOR [客户编码],
	CONSTRAINT [DF_客户明细表_客户全称] DEFAULT (0) FOR [客户全称],
	CONSTRAINT [DF_客户明细表_所在地区] DEFAULT (0) FOR [所在地区],
	CONSTRAINT [DF_客户明细表_详细地址] DEFAULT (0) FOR [详细地址],
	CONSTRAINT [DF_客户明细表_邮编] DEFAULT (0) FOR [邮编],
	CONSTRAINT [DF_客户明细表_开户行] DEFAULT (0) FOR [开户行],
	CONSTRAINT [DF_客户明细表_帐号] DEFAULT (0) FOR [帐号],
	CONSTRAINT [DF_客户明细表_税号] DEFAULT (0) FOR [税号],
	CONSTRAINT [DF_客户明细表_法人] DEFAULT (0) FOR [法人],
	CONSTRAINT [DF_客户明细表_电话] DEFAULT (0) FOR [电话],
	CONSTRAINT [DF_客户明细表_传真] DEFAULT (0) FOR [传真],
	CONSTRAINT [DF_客户明细表_电报挂号] DEFAULT (0) FOR [电报挂号],
	CONSTRAINT [DF_客户明细表_联系人] DEFAULT (0) FOR [联系人],
	CONSTRAINT [DF_客户明细表_联系电话] DEFAULT (0) FOR [联系电话],
	CONSTRAINT [DF_客户明细表_传呼] DEFAULT (0) FOR [传呼],
	CONSTRAINT [DF_客户明细表_手机] DEFAULT (0) FOR [手机],
	CONSTRAINT [DF_客户明细表_电子信箱] DEFAULT (0) FOR [电子信箱]
GO

ALTER TABLE [dbo].[客户审批单] WITH NOCHECK ADD 
	CONSTRAINT [DF_客户审批单_表单号] DEFAULT (0) FOR [表单号],
	CONSTRAINT [DF_客户审批单_制表日期] DEFAULT (0) FOR [制表日期],
	CONSTRAINT [DF_客户审批单_部门编码] DEFAULT (0) FOR [部门编码],
	CONSTRAINT [DF_客户审批单_审批人] DEFAULT (0) FOR [审批人],
	CONSTRAINT [DF_客户审批单_申报人] DEFAULT (0) FOR [申报人],
	CONSTRAINT [DF_客户审批单_录入员] DEFAULT (0) FOR [录入员],
	CONSTRAINT [DF_客户审批单_确认状态] DEFAULT (0) FOR [确认状态]
GO

ALTER TABLE [dbo].[客户帐户表头] WITH NOCHECK ADD 
	CONSTRAINT [DF__客户帐户表头__客户编码__628FA481] DEFAULT (0) FOR [客户编码],
	CONSTRAINT [DF__客户帐户表头__建立日期__6383C8BA] DEFAULT (0) FOR [建立日期],
	CONSTRAINT [DF__客户帐户表头__审核人__6477ECF3] DEFAULT (0) FOR [审核人],
	CONSTRAINT [DF__客户帐户表头__录入员__656C112C] DEFAULT (0) FOR [录入员],
	CONSTRAINT [DF__客户帐户表头__确认状态__66603565] DEFAULT (0) FOR [确认状态]
GO

ALTER TABLE [dbo].[客户帐户明细] WITH NOCHECK ADD 
	CONSTRAINT [DF_客户帐户明细_客户编码] DEFAULT (0) FOR [客户编码],
	CONSTRAINT [DF_客户帐户明细_信誉度等级] DEFAULT (0) FOR [信誉度等级],
	CONSTRAINT [DF_客户帐户明细_放帐额度] DEFAULT (0) FOR [放帐额度],
	CONSTRAINT [DF_客户帐户明细_放帐期限] DEFAULT (0) FOR [放帐期限],
	CONSTRAINT [DF_客户帐户明细_折扣率] DEFAULT (0) FOR [折扣率]
GO

ALTER TABLE [dbo].[客户主档] WITH NOCHECK ADD 
	CONSTRAINT [DF_客户主档_客户编码] DEFAULT (0) FOR [客户编码],
	CONSTRAINT [DF__客户主档__地区__30F848ED] DEFAULT (0) FOR [地区],
	CONSTRAINT [DF_客户主档_客户全称] DEFAULT (0) FOR [客户全称],
	CONSTRAINT [DF_客户主档_所在地区] DEFAULT (0) FOR [所在地区],
	CONSTRAINT [DF_客户主档_详细地址] DEFAULT (0) FOR [详细地址],
	CONSTRAINT [DF_客户主档_邮编] DEFAULT (0) FOR [邮编],
	CONSTRAINT [DF_客户主档_开户行] DEFAULT (0) FOR [开户行],
	CONSTRAINT [DF_客户主档_帐号] DEFAULT (0) FOR [帐号],
	CONSTRAINT [DF_客户主档_税号] DEFAULT (0) FOR [税号],
	CONSTRAINT [DF_客户主档_法人] DEFAULT (0) FOR [法人],
	CONSTRAINT [DF_客户主档_电话] DEFAULT (0) FOR [电话],
	CONSTRAINT [DF_客户主档_传真] DEFAULT (0) FOR [传真],
	CONSTRAINT [DF_客户主档_电报挂号] DEFAULT (0) FOR [电报挂号],
	CONSTRAINT [DF_客户主档_联系人] DEFAULT (0) FOR [联系人],
	CONSTRAINT [DF_客户主档_联系电话] DEFAULT (0) FOR [联系电话],
	CONSTRAINT [DF_客户主档_传呼] DEFAULT (0) FOR [传呼],
	CONSTRAINT [DF_客户主档_手机] DEFAULT (0) FOR [手机],
	CONSTRAINT [DF_客户主档_电子信箱] DEFAULT (0) FOR [电子信箱]
GO

ALTER TABLE [dbo].[人员档案] WITH NOCHECK ADD 
	CONSTRAINT [DF_权限分配_姓名] DEFAULT (0) FOR [姓名],
	CONSTRAINT [DF_权限分配_部门编码] DEFAULT (0) FOR [部门编码],
	CONSTRAINT [DF_权限分配_职务] DEFAULT (0) FOR [职务],
	CONSTRAINT [DF_权限分配_权限] DEFAULT (0) FOR [权限],
	CONSTRAINT [DF_权限分配_口令] DEFAULT (0) FOR [口令],
	CONSTRAINT [PK_权限分配] PRIMARY KEY  NONCLUSTERED 
	(
		[编码]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[商品编码表] WITH NOCHECK ADD 
	CONSTRAINT [DF__商品编码表__表单号] DEFAULT (0) FOR [表单号],
	CONSTRAINT [DF__商品编码表__日期] DEFAULT (0) FOR [日期],
	CONSTRAINT [DF__商品编码表__部门编码] DEFAULT (0) FOR [部门编码],
	CONSTRAINT [DF__商品编码表__审报人] DEFAULT (0) FOR [审报人],
	CONSTRAINT [DF__商品编码表__审批人] DEFAULT (0) FOR [审批人],
	CONSTRAINT [DF__商品编码表__录入员] DEFAULT (0) FOR [录入员],
	CONSTRAINT [DF__商品编码表__确认状态] DEFAULT (0) FOR [确认状态]
GO

ALTER TABLE [dbo].[商品编码明细] WITH NOCHECK ADD 
	CONSTRAINT [DF__商品编码明细__表单号] DEFAULT (0) FOR [表单号],
	CONSTRAINT [DF__商品编码明细__商品编码] DEFAULT (0) FOR [商品编码],
	CONSTRAINT [DF__商品编码明细__商品名称] DEFAULT (0) FOR [商品名称],
	CONSTRAINT [DF__商品编码明细__商品简称] DEFAULT (0) FOR [商品简称],
	CONSTRAINT [DF__商品编码明细__商品条码] DEFAULT (0) FOR [商品条码],
	CONSTRAINT [DF__商品编码明细__规格__114A936A] DEFAULT (0) FOR [规格],
	CONSTRAINT [DF__商品编码明细__型号__123EB7A3] DEFAULT (0) FOR [型号],
	CONSTRAINT [DF__商品编码明细__确认日期__1332DBDC] DEFAULT (0) FOR [确认日期],
	CONSTRAINT [DF__商品编码明细__修改日期__14270015] DEFAULT (0) FOR [修改日期],
	CONSTRAINT [DF__商品编码明细__单位] DEFAULT (0) FOR [单位],
	CONSTRAINT [DF__商品编码明细__零售价] DEFAULT (0) FOR [零售价],
	CONSTRAINT [DF__商品编码明细__税率__17036CC0] DEFAULT (0) FOR [税率],
	CONSTRAINT [DF__商品编码明细__批发价] DEFAULT (0) FOR [批发价]
GO

ALTER TABLE [dbo].[商品主档] WITH NOCHECK ADD 
	CONSTRAINT [DF__商品主档__商品编码] DEFAULT (0) FOR [商品编码],
	CONSTRAINT [DF__商品主档__商品名称] DEFAULT (0) FOR [商品名称],
	CONSTRAINT [DF__商品主档__商品简称] DEFAULT (0) FOR [商品简称],
	CONSTRAINT [DF__商品主档__商品条码] DEFAULT (0) FOR [商品条码],
	CONSTRAINT [DF__商品主档__规格__03F0984C] DEFAULT (0) FOR [规格],
	CONSTRAINT [DF__商品主档__型号__04E4BC85] DEFAULT (0) FOR [型号],
	CONSTRAINT [DF__商品主档__确认日期__05D8E0BE] DEFAULT (0) FOR [确认日期],
	CONSTRAINT [DF__商品主档__修改日期__06CD04F7] DEFAULT (0) FOR [修改日期],
	CONSTRAINT [DF__商品主档__单位] DEFAULT (0) FOR [单位],
	CONSTRAINT [DF__商品主档__零售价] DEFAULT (0) FOR [零售价],
	CONSTRAINT [DF__商品主档__税率__09A971A2] DEFAULT (0) FOR [税率],
	CONSTRAINT [DF__商品主档__批发价] DEFAULT (0) FOR [批发价]
GO

SET QUOTED_IDENTIFIER  OFF    SET ANSI_NULLS  ON 
GO

/****** Object:  View dbo.CKBill    Script Date: 00-7-31 16:13:30 ******/
CREATE VIEW dbo.ckbill
AS
SELECT Ckhd.BillNO, Ckhd.BillType, Ckhd.theBillNO, Ckhd.Depot, Ckhd.BillDate, 
      Ckhd.Total, Ckhd.ClientNO, Ckhd.CheckerNO, Ckhd.SHR_P, Ckhd.YWY_P, 
      Ckhd.InputerNO, Ckhd.ValiFlag, Ckbd.InCode, Ckbd.ShortName, Ckbd.Unit, 
      Ckbd.Specs, Ckbd.Price, Ckbd.Qty, Ckbd.Saleroom
FROM Ckbd RIGHT OUTER JOIN
      Ckhd ON Ckbd.BillNO = Ckhd.BillNO
WHERE (DATEPART(YEAR, Ckhd.BillDate) = DATEPART(YEAR, GETDATE())) AND 
      (DATEPART(MONTH, Ckhd.BillDate) = DATEPART(MONTH, GETDATE())) AND 
      (DATEPART(DAY, Ckhd.BillDate) = DATEPART(DAY, GETDATE()))

GO

SET QUOTED_IDENTIFIER  OFF    SET ANSI_NULLS  ON 
GO

SET QUOTED_IDENTIFIER  OFF    SET ANSI_NULLS  ON 
GO

/****** Object:  View dbo.RKBill    Script Date: 00-7-31 16:13:30 ******/
CREATE VIEW dbo.RKbill
AS
SELECT Rkhd.BillNO, Rkhd.BillDate, Rkhd.Depot, Rkhd.SupplierNO, Rkhd.CheckerNO, 
      Rkhd.YSY_P, Rkhd.InputerNO, Rkhd.Total, Rkhd.Payed, Rkhd.ValiFlag, Rkbd.InCode, 
      Rkbd.ShortName, Rkbd.Unit, Rkbd.InPrice, Rkbd.Price, Rkbd.Cost, Rkbd.Saleroom, 
      Rkbd.Specs, Rkbd.Qty, Rkbd.AvaiQty, Rkbd.DateAllSale, Rkbd.DateAllPay, 
      Rkbd.PayedQty, Rkbd.SaledQty, Rkbd.FlagAllSale, Rkbd.FlagAllPay
FROM Rkbd RIGHT OUTER JOIN
      Rkhd ON Rkbd.BillNO = Rkhd.BillNO
WHERE (DATEPART(YEAR, Rkhd.BillDate) = DATEPART(YEAR, GETDATE())) AND 
      (DATEPART(MONTH, Rkhd.BillDate) = DATEPART(MONTH, GETDATE())) AND 
      (DATEPART(DAY, Rkhd.BillDate) = DATEPART(DAY, GETDATE()))

GO

SET QUOTED_IDENTIFIER  OFF    SET ANSI_NULLS  ON 
GO

SET QUOTED_IDENTIFIER  OFF    SET ANSI_NULLS  ON 
GO

/****** Object:  View dbo.VR_Stock    Script Date: 00-7-31 16:13:30 ******/
CREATE VIEW dbo.VR_Stock
AS
SELECT R_stock.*
FROM R_stock
WHERE DATEPART(Year, R_stock.thedate) = DATEPART(Year, GETDATE()) AND 
      DATEPART(Month, R_stock.thedate) = DATEPART(Month, GETDATE()) AND 
      DATEPART(Day, R_stock.thedate) = DATEPART(Day, GETDATE())

GO

SET QUOTED_IDENTIFIER  OFF    SET ANSI_NULLS  ON 
GO

SET QUOTED_IDENTIFIER  OFF    SET ANSI_NULLS  ON 
GO

/****** Object:  View dbo.回款明细    Script Date: 00-7-31 16:13:30 ******/

/****** Object:  View dbo.回款明细    Script Date: 00-7-21 10:20:41 ******/
CREATE VIEW dbo.回款明细
AS
SELECT a.BillNO, a.BillType, a.BillDate, a.ClientNO, a.Client, a.Total, a.Receive, 
      b.Total AS favourable
FROM Sendhd a LEFT OUTER JOIN
      Vouhd b ON a.BillNO = b.BillNO_Send
WHERE (a.ValiFlag = 1) AND (b.ValiFlag = 1)


GO

SET QUOTED_IDENTIFIER  OFF    SET ANSI_NULLS  ON 
GO

SET QUOTED_IDENTIFIER  OFF    SET ANSI_NULLS  ON 
GO

/****** Object:  View dbo.商品主档View    Script Date: 00-7-31 16:13:30 ******/

/****** Object:  View dbo.商品主档View    Script Date: 00-7-21 10:20:41 ******/
CREATE VIEW dbo.商品主档View
AS
SELECT 商品编码, 商品名称, 规格, 型号, 单位, 确认日期
FROM 商品主档

GO

SET QUOTED_IDENTIFIER  OFF    SET ANSI_NULLS  ON 
GO

SET QUOTED_IDENTIFIER  OFF    SET ANSI_NULLS  ON 
GO

/****** Object:  View dbo.销售明细    Script Date: 00-7-31 16:13:30 ******/

/****** Object:  View dbo.销售明细    Script Date: 00-7-21 10:20:41 ******/
CREATE VIEW dbo.销售明细
AS
SELECT '01' AS 部门编码, a.BillDate, a.YWY_P, a.InvNO, b.BillNO, b.InCode, 
      b.ShortName, b.Unit, b.Specs, b.Price, b.Saleroom, b.Qty, a.BillType, a.ClientNO, 
      a.Client
FROM Sendhd a LEFT OUTER JOIN
      Sendbd b ON a.BillNO = b.BillNO
WHERE (a.ValiFlag = 1)


GO

SET QUOTED_IDENTIFIER  OFF    SET ANSI_NULLS  ON 
GO

SET QUOTED_IDENTIFIER  OFF    SET ANSI_NULLS  ON 
GO

/****** Object:  View dbo.V库存日报    Script Date: 00-7-31 16:13:30 ******/
CREATE VIEW dbo.V库存日报
AS
SELECT Stock.InCode AS 商品编码, Stock.ShortName AS 商品名称, Stock.Unit AS 单位, 
      Stock.Qty AS 本日结存, CKBill.Qty AS 出库数量, RKBill.Qty AS 入库数量, 
      VR_Stock.Qty_Y AS 昨日结存, CONVERT(CHAR(10), GETDATE(), 111) 
      AS 创建日期
FROM Stock LEFT OUTER JOIN
      VR_Stock ON Stock.InCode = VR_Stock.Incode LEFT OUTER JOIN
      RKBill ON Stock.InCode = RKBill.InCode LEFT OUTER JOIN
      CKBill ON Stock.InCode = CKBill.InCode

GO

SET QUOTED_IDENTIFIER  OFF    SET ANSI_NULLS  ON 
GO

SET QUOTED_IDENTIFIER  OFF    SET ANSI_NULLS  ON 
GO

/****** Object:  View dbo.V销售明细    Script Date: 00-7-31 16:13:30 ******/
create view V销售明细
as

SELECT * from 销售明细 where DatePart(year,BillDate)=DatePart(year,GetDate())
 and DatePart(Month,BillDate)=DatePart(Month,GetDate()) 
 and DatePart(Day,BillDate)=DatePart(Day,GetDate())




GO

SET QUOTED_IDENTIFIER  OFF    SET ANSI_NULLS  ON 
GO