⭐ 欢迎来到虫虫下载站！ | 📦 资源下载 📁 资源专辑 ℹ️ 关于我们
⭐ 虫虫下载站
📤 上传资源

📄 new.sql

📁 VB+access+ado
💻 SQL
📖 第 1 页 / 共 3 页
字号:
🔍
123 

CREATE TABLE [dbo].[收款类别信息] (
	[收款类别] [char] (10) NOT NULL ,
	[说明] [char] (10) NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[项目信息] (
	[项目编号] [varchar] (10) NOT NULL ,
	[项目名称] [varchar] (20) NULL ,
	[项目性质] [varchar] (20) NULL ,
	[项目负责人] [varchar] (20) NULL ,
	[项目地区] [varchar] (20) NULL ,
	[签约金额] [money] NULL ,
	[币种] [varchar] (10) NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[销货合同] (
	[公司合同号] [varchar] (10) NOT NULL ,
	[项目编号] [varchar] (10) NULL ,
	[合同号] [varchar] (10) NULL ,
	[甲方] [varchar] (100) NULL ,
	[乙方] [varchar] (100) NULL ,
	[设备总价] [money] NULL ,
	[合同总价] [money] NULL ,
	[优惠百分比] [real] NULL ,
	[优惠后总价] [money] NULL ,
	[签约金额] [money] NULL ,
	[签约币种] [varchar] (10) NULL ,
	[报价时间] [datetime] NULL ,
	[客户编号] [varchar] (10) NULL ,
	[职员编号] [varchar] (10) NULL ,
	[合同正文] [image] NULL 
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

CREATE TABLE [dbo].[销货合同登记表] (
	[ID] [int] IDENTITY (1, 1) NOT NULL ,
	[项目编号] [varchar] (10) NULL ,
	[项目名称] [varchar] (30) NULL ,
	[项目性质] [varchar] (10) NULL ,
	[合同号] [varchar] (10) NULL ,
	[甲方] [varchar] (20) NULL ,
	[乙方] [varchar] (20) NULL ,
	[设备总价] [money] NULL ,
	[合同总价] [money] NULL ,
	[优惠金额] [money] NULL ,
	[优惠后总价] [money] NULL ,
	[签约金额] [money] NULL ,
	[报价时间] [datetime] NULL ,
	[项目负责人] [varchar] (10) NULL ,
	[项目地区] [varchar] (18) NULL ,
	[签约人] [varchar] (10) NULL ,
	[传送合同部] [varchar] (50) NULL ,
	[合同部审核意见] [varchar] (20) NULL ,
	[总经理批示] [varchar] (20) NULL ,
	[签约标志] [varchar] (50) NULL ,
	[合同状态] [varchar] (6) NULL ,
	[备注] [varchar] (255) NULL ,
	[合同摘要] [text] NULL 
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

CREATE TABLE [dbo].[销货合同交货方式] (
	[公司合同号] [varchar] (10) NOT NULL ,
	[交货类型] [varchar] (20) NULL ,
	[日期] [datetime] NULL ,
	[交货地点] [varchar] (50) NULL ,
	[货运方式] [varchar] (10) NULL ,
	[麦头] [varchar] (10) NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[销货合同交货情况] (
	[公司合同号] [varchar] (10) NOT NULL ,
	[设备分类] [varchar] (20) NULL ,
	[日期] [datetime] NULL ,
	[交货数量] [real] NULL ,
	[金额] [money] NULL ,
	[合同数量] [real] NULL ,
	[币种] [varchar] (10) NULL ,
	[合计] [money] NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[销货合同客户资料表] (
	[ID] [int] IDENTITY (1, 1) NOT NULL ,
	[客户编号] [varchar] (10) NULL ,
	[项目名称] [varchar] (10) NULL ,
	[单位名称] [varchar] (20) NULL ,
	[单位地址] [varchar] (20) NULL ,
	[邮政编码] [varchar] (10) NULL ,
	[联系人] [varchar] (10) NULL ,
	[地区] [varchar] (20) NULL ,
	[电话] [varchar] (20) NULL ,
	[寻呼] [varchar] (10) NULL ,
	[银行] [varchar] (10) NULL ,
	[户名] [varchar] (10) NULL ,
	[帐号] [varchar] (10) NULL ,
	[税号] [varchar] (20) NULL ,
	[E_Mail] [varchar] (20) NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[销货合同纳税] (
	[ID] [int] IDENTITY (1, 1) NOT NULL ,
	[公司合同号] [varchar] (10) NOT NULL ,
	[税名] [varchar] (20) NULL ,
	[金额] [money] NULL ,
	[币种] [varchar] (10) NULL ,
	[百分比] [real] NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[销货合同设备] (
	[公司合同号] [varchar] (10) NOT NULL ,
	[序号] [int] IDENTITY (1, 1) NOT NULL ,
	[设备分类] [varchar] (10) NULL ,
	[设备编号] [varchar] (10) NULL ,
	[设备名称] [varchar] (50) NULL ,
	[规格型号] [varchar] (50) NULL ,
	[计量单位] [varchar] (10) NULL ,
	[体积] [real] NULL ,
	[重量] [real] NULL ,
	[单价] [money] NULL ,
	[币种] [varchar] (10) NULL ,
	[数量] [real] NULL ,
	[备注] [text] NULL ,
	[合计金额] [money] NULL ,
	[产地] [varchar] (50) NULL ,
	[供应商编号] [varchar] (10) NULL 
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

CREATE TABLE [dbo].[销货合同收款] (
	[ID] [int] IDENTITY (1, 1) NOT NULL ,
	[公司合同号] [varchar] (10) NOT NULL ,
	[费用名称] [varchar] (20) NULL ,
	[金额] [money] NULL ,
	[币种] [varchar] (10) NULL ,
	[百分比] [real] NULL ,
	[日期] [datetime] NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[销货合同收款情况] (
	[公司合同号] [varchar] (10) NOT NULL ,
	[收款类型] [varchar] (20) NULL ,
	[日期] [datetime] NULL ,
	[金额] [money] NULL ,
	[币种] [varchar] (10) NULL ,
	[汇率] [real] NULL ,
	[百分比] [real] NULL ,
	[付款方式] [varchar] (10) NULL ,
	[帐号] [varchar] (20) NULL ,
	[折算金额] [money] NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[销售合同收款审定单] (
	[ID] [int] IDENTITY (1, 1) NOT NULL ,
	[单号] [varchar] (10) NULL ,
	[合同号] [varchar] (10) NULL ,
	[项目名称] [varchar] (255) NULL ,
	[交货编号] [varchar] (10) NULL ,
	[合同总金额] [money] NULL ,
	[币种] [varchar] (10) NULL ,
	[收款类型] [varchar] (10) NULL ,
	[实收金额] [money] NULL ,
	[实收币种] [varchar] (10) NULL ,
	[汇率] [real] NULL ,
	[比例] [real] NULL ,
	[收款帐号] [varchar] (20) NULL ,
	[税率] [real] NULL ,
	[财务科目] [varchar] (10) NULL ,
	[日期] [datetime] NULL ,
	[核定会计] [varchar] (10) NULL ,
	[入帐出纳] [varchar] (10) NULL ,
	[审核合同] [varchar] (10) NULL ,
	[财务经手人] [varchar] (10) NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[职员信息] (
	[职员编号] [varchar] (10) NOT NULL ,
	[部门] [varchar] (30) NULL ,
	[姓名] [varchar] (30) NULL ,
	[性别] [varchar] (2) NULL ,
	[出生日期] [datetime] NULL ,
	[职务] [varchar] (20) NULL ,
	[职称] [varchar] (20) NULL ,
	[家电话] [varchar] (30) NULL ,
	[手机号] [varchar] (30) NULL ,
	[传呼号] [varchar] (30) NULL ,
	[地址] [varchar] (60) NULL ,
	[邮政编码] [varchar] (30) NULL ,
	[入公司日期] [datetime] NULL 
) ON [PRIMARY]
GO

SET QUOTED_IDENTIFIER  ON    SET ANSI_NULLS  ON 
GO



CREATE VIEW dbo.[VIEW_购货合同]
AS
SELECT 购货合同.公司合同号, 项目信息.项目名称, 购货合同.合同号, 购货合同.甲方, 
      购货合同.乙方, 购货合同.设备总价, 购货合同.合同总价, 购货合同.签约金额, 
      购货合同.签约币种, 购货合同.报价时间, 职员信息.姓名, 供应商信息.单位名称, 
      购货合同.合同正文
FROM 购货合同 INNER JOIN
      供应商信息 ON 购货合同.供应商编号 = 供应商信息.供应商编号 INNER JOIN
      项目信息 ON 购货合同.项目编号 = 项目信息.项目编号 INNER JOIN
      职员信息 ON 购货合同.职员编号 = 职员信息.职员编号


GO
SET QUOTED_IDENTIFIER  OFF    SET ANSI_NULLS  ON 
GO

SET QUOTED_IDENTIFIER  ON    SET ANSI_NULLS  ON 
GO



CREATE VIEW dbo.[VIEW_销货合同]
AS
SELECT 销货合同.公司合同号, 项目信息.项目名称, 销货合同.合同号, 销货合同.甲方, 
      销货合同.乙方, 销货合同.设备总价, 销货合同.合同总价, 销货合同.优惠百分比, 
      销货合同.优惠后总价, 销货合同.签约金额, 销货合同.签约币种, 
      销货合同.报价时间, 客户信息.客户单位名称, 职员信息.姓名, 
      销货合同.合同正文
FROM 客户信息 INNER JOIN
      职员信息 ON 客户信息.职员编号 = 职员信息.职员编号 INNER JOIN
      销货合同 ON 客户信息.客户编号 = 销货合同.客户编号, 项目信息


GO
SET QUOTED_IDENTIFIER  OFF    SET ANSI_NULLS  ON 
GO

SET QUOTED_IDENTIFIER  OFF    SET ANSI_NULLS  ON 
GO



CREATE VIEW dbo.收款情况报表
AS
SELECT 项目信息.项目编号, 项目信息.项目名称, 销货合同.合同号, 
      销货合同.公司合同号, 销货合同.签约金额, 销货合同.签约币种
FROM 项目信息 INNER JOIN
      销货合同 ON 项目信息.项目编号 = 销货合同.项目编号, 销货合同收款情况


GO
SET QUOTED_IDENTIFIER  OFF    SET ANSI_NULLS  ON 
GO

SET QUOTED_IDENTIFIER  ON    SET ANSI_NULLS  ON 
GO



CREATE VIEW dbo.收款情况报表清单
AS
SELECT 销货合同.项目编号, 项目信息.项目名称, 销货合同收款.费用名称, 
      销货合同收款.金额, 销货合同收款.币种, 销货合同收款.日期, 
      销货合同收款情况.收款类型, 销货合同收款情况.日期 AS 已收日期, 
      销货合同收款情况.金额 AS 已收金额, 销货合同收款情况.币种 AS 已收币钟
FROM 销货合同 INNER JOIN
      项目信息 ON 销货合同.项目编号 = 项目信息.项目编号 INNER JOIN
      销货合同收款情况 ON 
      销货合同.公司合同号 = 销货合同收款情况.公司合同号 INNER JOIN
      销货合同收款 ON 销货合同.公司合同号 = 销货合同收款.公司合同号


GO
SET QUOTED_IDENTIFIER  OFF    SET ANSI_NULLS  ON 
GO

SET QUOTED_IDENTIFIER  OFF    SET ANSI_NULLS  ON 
GO



CREATE PROCEDURE sp_购货合同
	@gshth varchar(10),
	@xmbh varchar(10),
	@zybh varchar(10),
	@gysbh varchar(10)
AS
INSERT INTO 购货合同 (公司合同号, 项目编号, 职员编号, 供应商编号) SELECT @gshth AS 公司合同号, @xmbh AS 项目编号, @zybh AS 职员编号, @gysbh AS 供应商编号



GO
SET QUOTED_IDENTIFIER  OFF    SET ANSI_NULLS  ON 
GO

SET QUOTED_IDENTIFIER  OFF    SET ANSI_NULLS  ON 
GO



CREATE PROCEDURE sp_销货合同
	@gshth varchar(10),
	@xmbh varchar(10),
	@zybh varchar(10),
	@khbh varchar(10)
 AS
INSERT INTO 销货合同 (公司合同号, 项目编号, 客户编号, 职员编号) SELECT @gshth AS 公司合同号, @xmbh AS 项目编号, @khbh AS 客户编号, @zybh AS 职员编号



GO
SET QUOTED_IDENTIFIER  OFF    SET ANSI_NULLS  ON 
GO
123

⌨️ 快捷键说明

复制代码 Ctrl + C
搜索代码 Ctrl + F
全屏模式 F11
切换主题 Ctrl + Shift + D
显示快捷键 ?
增大字号 Ctrl + =
减小字号 Ctrl + -