if exists (select * from master..sysdatabases where name='company')
drop database company
go
create database company
go
use company
go
sp_addlogin company,''
go
sp_defaultdb company,company
go
sp_changedbowner company,company
go
sp_addsrvrolemember company,sysadmin
go

if exists (select * from sysobjects where name='stkbillsub')
drop table stkbillsub
if exists (select * from sysobjects where name='stkbillmain')
drop table stkbillmain
if exists (select * from sysobjects where name='ordbillsub')
drop table ordbillsub
if exists (select * from sysobjects where name='ordbillmain')
drop table ordbillmain
if exists (select * from sysobjects where name='comcustomer')
drop table comcustomer
if exists (select * from sysobjects where name='comwareamount')
drop table comwareamount
if exists (select * from sysobjects where name='comwarehouse')
drop table comwarehouse
if exists (select * from sysobjects where name='comproduct')
drop table comproduct
if exists (select * from sysobjects where name='comperson')
drop table comperson
if exists (select * from sysobjects where name='comdepartment')
drop table comdepartment

create table comdepartment(
  id varchar(6) constraint PK_comdepartment_id primary key,
  name varchar(10) not null,
  memo varchar(400)
  )
create table comperson(
  id varchar(10) constraint PK_comperson_id primary key,
  name varchar(20) not null,
  departid varchar(6) not null constraint FK_comperson_comdepartment_departid references comdepartment(id),
  birthday datetime,
  assumedate datetime,
  telephone varchar(20),
  sex bit,
  memo varchar(400)
  )
create table comproduct(
  productid varchar(20) constraint PK_comproduct_productid primary key,
  productname varchar(40) not null,
  unit varchar(4) not null,
  suggprice float,
  stdbuying float,
  earlycost float,
  presentcost float,
  stdcost float,
  taxrate float,
  memo varchar(400)
  )
create table comwarehouse(
  id varchar(10) constraint PK_comwarehouse_id primary key,
  name varchar (40) not null,
  memo varchar(400)
  )
create table comwareamount(
  productid varchar(20) constraint FK_comwareamount_comproduct_productid foreign key references comproduct(productid),
  warehouseid varchar(10) constraint FK_comwareamount_comwarehouse_warehouseid foreign key references comwarehouse(id)
  earlyamount float,
  presentamount float,
  constraint PK_comwareamount_productid_warehouseid primary key(productid,warehouseid)
  )
create table comcustomer(
  flag smallint,
  id varchar(10),
  name varchar(40),
  shortname varchar(10),
  address varchar(150),
  telephone1 varchar(20),
  telephone2 varchar(20),
  boss varchar(20),
  bosstitle varchar(10),
  limitcredit float,
  earlyfunds float,
  earlyfundspaid float,
  presentfunds float,
  memo varchar(400),
  constraint PK_comcustomer_flag_id primary key(flag,id)
  )
create table ordbillmain(
  flag smallint,
  billno varchar(12),
  targetid varchar(10) not null,
  deliveryaddr varchar(150),
  personid varchar(10) not null constraint FK_ordbillmain_comperson_personid references comperson(id),
  totalmoney float,
  totaltax float,
  memo varchar(400),
  constraint PK_ordbillmain_flag_billno primary key(flag,billno)
  )
create table ordbillsub(
  flag smallint,
  billno varchar(12),
  serialno smallint,
  productid varchar(20) not null constraint FK_ordbillsub_comproduct_productid foreign key references comproduct(productid),
  amount float,
  price float,
  money as amount*price,
  taxrate float,
  tax as amount*price*taxrate,
  memo varchar(400),
  translated bit,
  constraint PK_ordbillsub_flag_billno_serialno primary key(flag,billno,serialno),
  constraint FK_ordbillsub_ordbillmain_flag_billno foreign key(flag,billno) references ordbillmain(flag,billno)
  )
create table stkbillmain(
  flag smallint,
  billno varchar(12),
  targetid varchar(10),
  deliveryaddr varchar(150),
  personid varchar(10) not null constraint FK_stkbillmain_comperson_personid foreign key references comperson(id),
  warehouseid varchar(10) not null constraint FK_stkbillmain_comwarehouse_warehouseid foreign key references comwarehouse(id),
  totalmoney float,
  totaltax float,
  cash float,
  paidmoney float,
  memo varchar(400),
  constraint PK_stkbillmain_flag_billno primary key(flag,billno)
  )
create table stkbillsub(
  flag smallint,
  billno varchar(12),
  serialno smallint,
  productid varchar(20) not null constraint FK_stkbillsub_comproduct_productid foreign key references comproduct(productid),
  amount float,
  price float,
  money as amount*price,
  taxrate float,
  tax as amount*price*taxrate,
  costs float,
  memo varchar(400),
  constraint PK_stkbillsub_flag_billno_serialno primary key(flag,billno,serialno),
  constraint FK_stkbillsub_stkbillmain__flag_billno foreign key(flag,billno) references stkbillmain(flag,billno)
  )

select * from comdepartment
select * from comperson
select * from comproduct
select * from comwarehouse
select * from comwareamount
select * from comcustomer
select * from ordbillmain
select * from ordbillsub
select * from stkbillmain
select * from stkbillsub