CREATE DATABASE sifMaster
GO
USE sifsystem
delete dbtables

USE sifmaster
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[account]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [dbo].[account]
GO
CREATE TABLE [dbo].[account](
	[id]		[int] IDENTITY(1,1) NOT NULL,
	[parentid]	[int] NULL,
	[no]		[varchar] (20) NOT NULL,
	[name]		[varchar](250) NOT NULL,
	[ename]		[varchar](250) NULL,
	[isLast]	[tinyint] NOT NULL DEFAULT(0),
	[BSType]	[tinyint] NULL,
	[ChildSeq]	[int] NULL,
	[LevelNo]	[tinyint] NULL,
	[remark]	[text] NULL,
	[AddBy]		[int] NULL,
	[AddWhen]	[datetime] NULL default(getdate()),
	[DelBy]		[int] NULL,
	[DelWhen]	[datetime] NULL,
CONSTRAINT [pkaccount] PRIMARY KEY CLUSTERED ([id]) )
GO

if not exists (select id from sifsystem.dbo.dbtables where name='account'and dbname='sifmaster' and applicationid=1)
insert sifsystem.dbo.dbtables (name,dbname,applicationid) values ('account','sifmaster',1)
go

insert account (no,parentid,name,ename,islast,levelno,addby) values ('1',0,'สินทรัพย์','Assets',0,1,1)
insert account (no,parentid,name,ename,islast,levelno,addby) values ('2',0,'หนี้สิน','Liabilities',0,1,1)
insert account (no,parentid,name,ename,islast,levelno,addby) values ('3',0,'ส่วนของผู้ถือหุ้น','Owners''s Equity',0,1,1)
insert account (no,parentid,name,ename,islast,levelno,addby) values ('4',0,'รายได้','Revenues',0,1,1)
insert account (no,parentid,name,ename,islast,levelno,addby) values ('5',0,'รายจ่าย','Expenses',0,1,1)
insert account (no,parentid,name,ename,islast,levelno,addby) values ('6',0,'ค่าใช้จ่ายในการผลิต','Mfg. Expenses',0,1,1)
insert account (no,parentid,name,ename,islast,levelno,addby) values ('7',0,'รายการอื่น','Others',0,1,1)

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[accdet]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [dbo].[accdet]
GO
CREATE TABLE [dbo].[accdet](
	[id]				[int] IDENTITY(1,1) NOT NULL,
	[AccountID]			[int] NOT NULL,
	[Nature]			[tinyint] NOT NULL,
	[CrossAccountID]	[int] NULL,
	[ControlType]		[tinyint] NULL,	
	[isDue]				[tinyint] NULL,
	[SubLedgerGroupID]	[int] NULL,
	[DrDoc]				[varchar] (150) NULL,
	[CrDoc]				[varchar] (150) NULL,
	[DrName]			[varchar] (150) NULL,
	[CrName]			[varchar] (150) NULL,
	[DrExpGroupID]		[int] NULL,		
	[CrExpGroupID]		[int] NULL,		
	[DrPrint]			[varchar] (30) NULL,
	[CrPrint]			[varchar] (30) NULL,
	[RepSection]		[varchar] (50) NULL,	
	[WhTaxRepID]		[int] NULL,		
	[TaxNoRun]			[tinyint] NULL,		
	[isdepartment]		[tinyint] NULL,		
	[istaxloc]			[tinyint] NULL,		
	[ismulticurrency]	[tinyint] NULL,		
	[forexincome]		[int] NULL,		
	[forexexpense]		[int] NULL,		
	[isVat]				[tinyint] NULL,
	[isunclaimvat]		[tinyint] NULL,		
	[isdefervat]		[tinyint] NULL,		
	[vataccountid]		[int] NULL,		
	[iswhtax]			[tinyint] NULL,		
	[taxrate]			[decimal] (10,2) NULL,
	[taxform]			[char] (5) NULL,	
	[ispv]				[tinyint] NULL,		
	[paidaccountid]		[int] NULL,		
	[paidbookid]		[int] NULL,		
	[auditorindex]		[varchar] (10) NULL,	
	[auditornature]		[tinyint] NULL, 		
	[auditorgroup]		[tinyint] NULL,		
	[auditorsort]		[int] NULL,		
	[auditorname]		[varchar] (200) NULL,	
	[chqPageWidth]		[decimal] (20,4) NULL,
	[chqPageHeight]		[decimal] (20,4) NULL,
	[chqOrientation]	[char] (1) NULL,	
	[chqFontFamily]		[int] NULL,		
	[chqDateSize]		[tinyint] NULL,		
	[chqDateBold]		[tinyint] NULL,		
	[chqDateItalic]		[tinyint] NULL,		
	[chqDateX]			[decimal] (20,4) NULL,
	[chqDateY]			[decimal] (20,4) NULL,
	[chqNameSize]		[tinyint] NULL,		
	[chqNameBold]		[tinyint] NULL,		
	[chqNameItalic]		[tinyint] NULL,		
	[chqNameX]			[decimal] (20,4) NULL,
	[chqNameY]			[decimal] (20,4) NULL,
	[chqAmtSize]		[tinyint] NULL,		
	[chqAmtBold]		[tinyint] NULL,		
	[chqAmtItalic]		[tinyint] NULL,		
	[chqAmtX]			[decimal] (20,4) NULL,
	[chqAmtY]			[decimal] (20,4) NULL,
	[chqWordSize]		[tinyint] NULL,		
	[chqWordBold]		[tinyint] NULL,		
	[chqWordItalic]		[tinyint] NULL,		
	[chqWordX]			[decimal] (20,4) NULL,
	[chqWordY]			[decimal] (20,4) NULL,
	[remark]			[text] NULL,
	[AddBy]				[int] NULL,
	[AddWhen]			[datetime] NULL default(getdate()),
	[DelBy]				[int] NULL,
	[DelWhen]			[datetime] NULL,
CONSTRAINT [pkaccdet] PRIMARY KEY CLUSTERED ([id]) )
GO

if not exists (select id from sifsystem.dbo.dbtables where name='accdet'and dbname='sifmaster' and applicationid=1)
insert sifsystem.dbo.dbtables (name,dbname,applicationid) values ('accdet','sifmaster',1)
go

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[book]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [dbo].[book]
GO
CREATE TABLE [dbo].[book](
	[id]			[int] IDENTITY(1,1) NOT NULL,
	[No]			[varchar] (30) NOT NULL,	
	[name]			[varchar](250) NOT NULL,
	[AccountID]		[int] NULL,		
	[Nature]		[tinyint] NULL,		
	[TrnNoRUN]		[tinyint] NULL,		
	[RepSection]	[varchar] (50) NULL,	
	[type]			[tinyint] NULL,		
	[subtype]		[tinyint] NULL,		
	[isbookacc]		[int] NULL,
	[remark]		[text] NULL,
	[AddBy]			[int] NULL,
	[AddWhen]		[datetime] NULL default(getdate()),
	[DelBy]			[int] NULL,
	[DelWhen]		[datetime] NULL,
CONSTRAINT [pkbook] PRIMARY KEY CLUSTERED ([id]) )
GO
if not exists (select id from sifsystem.dbo.dbtables where name='book'and dbname='sifmaster' and applicationid=1)
insert sifsystem.dbo.dbtables (name,dbname,applicationid) values ('book','sifmaster',1)
go

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[bookaccount]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [dbo].[bookaccount]
GO
CREATE TABLE [dbo].[bookaccount](
	[id]			[int] IDENTITY(1,1) NOT NULL,
	[BookID]		[int] NOT NULL,
	[AccountID]		[int] NOT NULL,
	[remark]		[text] NULL,
	[AddBy]			[int] NULL,
	[AddWhen]		[datetime] NULL default(getdate()),
	[DelBy]			[int] NULL,
	[DelWhen]		[datetime] NULL,
CONSTRAINT [pkbookaccount] PRIMARY KEY CLUSTERED ([id]) )
GO
if not exists (select id from sifsystem.dbo.dbtables where name='bookaccount'and dbname='sifmaster' and applicationid=1)
insert sifsystem.dbo.dbtables (name,dbname,applicationid) values ('bookaccount','sifmaster',1)
go

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[subledgergroup]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [dbo].[subledgergroup]
GO
CREATE TABLE [dbo].[subledgergroup](
	[id]		[int] IDENTITY(1,1) NOT NULL,
	[name]		[varchar](250) NOT NULL,
	[tablename]	[varchar] (30) NULL,
	[remark]	[text] NULL,
	[AddBy]		[int] NULL,
	[AddWhen]	[datetime] NULL default(getdate()),
	[DelBy]		[int] NULL,
	[DelWhen]	[datetime] NULL,
CONSTRAINT [pksubledgergroup] PRIMARY KEY CLUSTERED ([id]) )
GO
if not exists (select id from sifsystem.dbo.dbtables where name='subledgergroup'and dbname='sifmaster' and applicationid=1)
insert sifsystem.dbo.dbtables (name,dbname,applicationid) values ('subledgergroup','sifmaster',1)
go


if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[subledger]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [dbo].[subledger]
GO
CREATE TABLE [dbo].[subledger](
	[id]				[int] IDENTITY(1,1) NOT NULL,
	[subledgergroupid]	[int] NOT NULL,
	[no]				[varchar] (30) NULL,
	[name]				[varchar](250) NOT NULL,
	[remark]			[text] NULL,
	[AddBy]				[int] NULL,
	[AddWhen]			[datetime] NULL default(getdate()),
	[DelBy]				[int] NULL,
	[DelWhen]			[datetime] NULL,
CONSTRAINT [pksubledger] PRIMARY KEY CLUSTERED ([id]) )
GO
if not exists (select id from sifsystem.dbo.dbtables where name='subledger'and dbname='sifmaster' and applicationid=1)
insert sifsystem.dbo.dbtables (name,dbname,applicationid) values ('subledger','sifmaster',1)
go
		
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[expgroup]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [dbo].[expgroup]
GO
CREATE TABLE [dbo].[expgroup](
	[id]		[int] IDENTITY(1,1) NOT NULL,
	[name]		[varchar](250) NOT NULL,
	[remark]	[text] NULL,
	[AddBy]		[int] NULL,
	[AddWhen]	[datetime] NULL default(getdate()),
	[DelBy]		[int] NULL,
	[DelWhen]	[datetime] NULL,
CONSTRAINT [pkexpgroup] PRIMARY KEY CLUSTERED ([id]) )
GO
if not exists (select id from sifsystem.dbo.dbtables where name='expgroup'and dbname='sifmaster' and applicationid=1)
insert sifsystem.dbo.dbtables (name,dbname,applicationid) values ('expgroup','sifmaster',1)
go

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[expname]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [dbo].[expname]
GO
CREATE TABLE [dbo].[expname](
	[id]			[int] IDENTITY(1,1) NOT NULL,
	[expgroupid]	[int] NOT NULL,
	[name]			[varchar](250) NOT NULL,
	[remark]		[text] NULL,
	[AddBy]			[int] NULL,
	[AddWhen]		[datetime] NULL default(getdate()),
	[DelBy]			[int] NULL,
	[DelWhen]		[datetime] NULL,
CONSTRAINT [pkexpname] PRIMARY KEY CLUSTERED ([id]) )
GO
if not exists (select id from sifsystem.dbo.dbtables where name='expname'and dbname='sifmaster' and applicationid=1)
insert sifsystem.dbo.dbtables (name,dbname,applicationid) values ('expname','sifmaster',1)
go

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[whtax]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [dbo].[whtax]
GO
CREATE TABLE [dbo].[whtax](
	[id]				[int] IDENTITY(1,1) NOT NULL,
	[name]				[varchar] (250) NOT NULL,	
	[personrate]		[decimal] (10,2) NULL,
	[corprate]			[decimal] (10,2) NULL,
	[personaccountid]	[int] NULL,		
	[corpaccountid]		[int] NULL,		
	[taxlawno]			[varchar] (30) NULL,
	[formlineno]		[int] NULL,
	[remark]			[text] NULL,
	[AddBy]				[int] NULL,
	[AddWhen]			[datetime] NULL default(getdate()),
	[DelBy]				[int] NULL,
	[DelWhen]			[datetime] NULL,
CONSTRAINT [pkwhtax] PRIMARY KEY CLUSTERED ([id]) )
GO
if not exists (select id from sifsystem.dbo.dbtables where name='whtax'and dbname='sifmaster' and applicationid=1)
insert sifsystem.dbo.dbtables (name,dbname,applicationid) values ('whtax','sifmaster',1)
go

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[taxform20]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [dbo].[taxform20]
GO
CREATE TABLE [dbo].[taxform20](
	[id]				[int] IDENTITY(1,1) NOT NULL,
	[name]				[varchar] (250) NOT NULL,	
	[Address]			[text] NULL,		
	[TaxID]				[varchar] (15) NULL,	
	[isHQ]				[tinyint] NULL,		
	[BranchNo]			[tinyint] NULL,		
	[remark]			[text] NULL,
	[AddBy]				[int] NULL,
	[AddWhen]			[datetime] NULL default(getdate()),
	[DelBy]				[int] NULL,
	[DelWhen]			[datetime] NULL,
CONSTRAINT [pktaxform20] PRIMARY KEY CLUSTERED ([id]) )
GO
if not exists (select id from sifsystem.dbo.dbtables where name='taxform20'and dbname='sifmaster' and applicationid=1)
insert sifsystem.dbo.dbtables (name,dbname,applicationid) values ('taxform20','sifmaster',1)
go

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[nametitle]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [dbo].[nametitle]
GO
CREATE TABLE [dbo].[nametitle](
	[id]				[int] IDENTITY(1,1) NOT NULL,
	[no]				[varchar](15) NOT NULL,
	[FrontTitle]		[varchar](30) NULL,
	[BackTitle]			[varchar](30) NULL,
	[nametype]			[tinyint] NULL default(1),
	[remark]			[text] NULL,
	[AddBy]				[int] NULL,
	[AddWhen]			[datetime] NULL default(getdate()),
	[DelBy]				[int] NULL,
	[DelWhen]			[datetime] NULL,
CONSTRAINT [pknametitle] PRIMARY KEY CLUSTERED ([id]) )
GO
if not exists (select id from sifsystem.dbo.dbtables where name='nametitle'and dbname='sifmaster' and applicationid=1)
insert sifsystem.dbo.dbtables (name,dbname,applicationid) values ('nametitle','sifmaster',1)
go

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[namegroup]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [dbo].[namegroup]
GO
CREATE TABLE [dbo].[namegroup](
	[id]				[int] IDENTITY(1,1) NOT NULL,
	[no]				[varchar](15) NULL,
	[name]				[varchar](100) NOT NULL,
	[remark]			[text] NULL,
	[AddBy]				[int] NULL,
	[AddWhen]			[datetime] NULL default(getdate()),
	[DelBy]				[int] NULL,
	[DelWhen]			[datetime] NULL,
CONSTRAINT [pknamegroup] PRIMARY KEY CLUSTERED ([id]) )
GO
if not exists (select id from sifsystem.dbo.dbtables where name='namegroup'and dbname='sifmaster' and applicationid=1)
insert sifsystem.dbo.dbtables (name,dbname,applicationid) values ('namegroup','sifmaster',1)
go

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[department]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [dbo].[department]
GO
CREATE TABLE [dbo].[department](
	[id]				[int] IDENTITY(1,1) NOT NULL,
	[No]				[varchar](15) NULL,
	[name]				[varchar](100) NOT NULL,
	[ManagerID]			[int] NULL,
	[ParentID]			[int] NULL,
	[costtype]			[tinyint] NULL,
	[remark]			[text] NULL,
	[AddBy]				[int] NULL,
	[AddWhen]			[datetime] NULL default(getdate()),
	[DelBy]				[int] NULL,
	[DelWhen]			[datetime] NULL,
CONSTRAINT [pkdepartment] PRIMARY KEY CLUSTERED ([id]) )
GO
if not exists (select id from sifsystem.dbo.dbtables where name='department'and dbname='sifmaster' and applicationid=1)
insert sifsystem.dbo.dbtables (name,dbname,applicationid) values ('department','sifmaster',1)
go

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[position]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [dbo].[position]
GO
CREATE TABLE [dbo].[position](
	[id]				[int] IDENTITY(1,1) NOT NULL,
	[No]				[varchar](15) NULL,
	[name]				[varchar](100) NOT NULL,
	[isDepartmental]	[tinyint] NULL,
	[remark]			[text] NULL,
	[AddBy]				[int] NULL,
	[AddWhen]			[datetime] NULL default(getdate()),
	[DelBy]				[int] NULL,
	[DelWhen]			[datetime] NULL,
CONSTRAINT [pkposition] PRIMARY KEY CLUSTERED ([id]) )
GO
if not exists (select id from sifsystem.dbo.dbtables where name='position'and dbname='sifmaster' and applicationid=1)
insert sifsystem.dbo.dbtables (name,dbname,applicationid) values ('position','sifmaster',1)
go

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[employee]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [dbo].[employee]
GO
CREATE TABLE [dbo].[employee](
	[id]				[int] IDENTITY(1,1) NOT NULL,
	[No]				[varchar](15) NULL,
	[Name]				[varchar](100) NOT NULL,
	[NickName]			[varchar](20) NULL,
	[NameTitleID]		[int] NULL,
	[DepartmentID]		[int] NULL,
	[SupervisorID]		[int] NULL,
	[PositionID]		[int] NULL,
	[PositionRank]		[tinyint] NULL,
	[TelNo]				[varchar](100) NULL,
	[FaxNo]				[varchar](100) NULL,
	[eMail]				[varchar](100) NULL,
	[Mobile]			[varchar](100) NULL,
	[userid]			[int] NULL,
	[remark]			[text] NULL,
	[AddBy]				[int] NULL,
	[AddWhen]			[datetime] NULL default(getdate()),
	[DelBy]				[int] NULL,
	[DelWhen]			[datetime] NULL,
CONSTRAINT [pkemployee] PRIMARY KEY CLUSTERED ([id]) )
GO
if not exists (select id from sifsystem.dbo.dbtables where name='employee'and dbname='sifmaster' and applicationid=1)
insert sifsystem.dbo.dbtables (name,dbname,applicationid) values ('employee','sifmaster',1)
go

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[market]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [dbo].[market]
GO
CREATE TABLE [dbo].[market](
	[id]				[int] IDENTITY(1,1) NOT NULL,
	[No]				[varchar](15) NULL,
	[name]				[varchar](100) NOT NULL,
	[remark]			[text] NULL,
	[AddBy]				[int] NULL,
	[AddWhen]			[datetime] NULL default(getdate()),
	[DelBy]				[int] NULL,
	[DelWhen]			[datetime] NULL,
CONSTRAINT [pkmarket] PRIMARY KEY CLUSTERED ([id]) )
GO
if not exists (select id from sifsystem.dbo.dbtables where name='market'and dbname='sifmaster' and applicationid=1)
insert sifsystem.dbo.dbtables (name,dbname,applicationid) values ('market','sifmaster',1)
go

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[country]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [dbo].[country]
GO
CREATE TABLE [dbo].[country](
	[id]				[int] IDENTITY(1,1) NOT NULL,
	[No]				[varchar](15) NULL,
	[name]				[varchar](100) NOT NULL,
	[MarketID]			[int] NOT NULL,
	[PaNo]				[char](1) NULL,
	[PaChar]			[char](3) NULL,
	[remark]			[text] NULL,
	[AddBy]				[int] NULL,
	[AddWhen]			[datetime] NULL default(getdate()),
	[DelBy]				[int] NULL,
	[DelWhen]			[datetime] NULL,
CONSTRAINT [pkcountry] PRIMARY KEY CLUSTERED ([id]) )
GO
if not exists (select id from sifsystem.dbo.dbtables where name='country'and dbname='sifmaster' and applicationid=1)
insert sifsystem.dbo.dbtables (name,dbname,applicationid) values ('country','sifmaster',1)
go

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[city]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [dbo].[city]
GO
CREATE TABLE [dbo].[city](
	[id]				[int] IDENTITY(1,1) NOT NULL,
	[No]				[varchar](15) NULL,
	[Name]				[varchar](100) NOT NULL,
	[CountryID]			[int] NOT NULL,
	[PortNames]			[varchar](250) NULL,
	[Freight20]			[money] NULL,
	[Freight40]			[money] NULL,
	[FreightMts]		[money] NULL,
	[Days]				[int] NULL,
	[remark]			[text] NULL,
	[AddBy]				[int] NULL,
	[AddWhen]			[datetime] NULL default(getdate()),
	[DelBy]				[int] NULL,
	[DelWhen]			[datetime] NULL,
CONSTRAINT [pkcity] PRIMARY KEY CLUSTERED ([id]) )
GO
if not exists (select id from sifsystem.dbo.dbtables where name='city'and dbname='sifmaster' and applicationid=1)
insert sifsystem.dbo.dbtables (name,dbname,applicationid) values ('city','sifmaster',1)
go

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[forex]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [dbo].[forex]
GO
CREATE TABLE [dbo].[forex](
	[id]				[int] IDENTITY(1,1) NOT NULL,
	[No]				[varchar](15) NOT NULL,
	[Name]				[varchar](100) NOT NULL,
	[CountryID]			[int] NOT NULL,
	[Amount]			[money] NOT NULL,
	[remark]			[text] NULL,
	[AddBy]				[int] NULL,
	[AddWhen]			[datetime] NULL default(getdate()),
	[DelBy]				[int] NULL,
	[DelWhen]			[datetime] NULL,
CONSTRAINT [pkforex] PRIMARY KEY CLUSTERED ([id]) )
GO
if not exists (select id from sifsystem.dbo.dbtables where name='forex'and dbname='sifmaster' and applicationid=1)
insert sifsystem.dbo.dbtables (name,dbname,applicationid) values ('forex','sifmaster',1)
go

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[forexrate]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [dbo].[forexrate]
GO
CREATE TABLE [dbo].[forexrate](
	[id]				[int] IDENTITY(1,1) NOT NULL,
	[Date]				[datetime] NULL,
	[ForexID]			[int] NOT NULL,
	[BuyRate]			[decimal](20, 5) NULL,
	[SellRate]			[decimal](20, 5) NULL,
	[remark]			[text] NULL,
	[AddBy]				[int] NULL,
	[AddWhen]			[datetime] NULL default(getdate()),
	[DelBy]				[int] NULL,
	[DelWhen]			[datetime] NULL,
CONSTRAINT [pkforexrate] PRIMARY KEY CLUSTERED ([id]) )
GO
if not exists (select id from sifsystem.dbo.dbtables where name='forexrate'and dbname='sifmaster' and applicationid=1)
insert sifsystem.dbo.dbtables (name,dbname,applicationid) values ('forexrate','sifmaster',1)
go


if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[name]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [dbo].[name]
GO
CREATE TABLE [dbo].[name](
	[id]				[int] IDENTITY(1,1) NOT NULL,
	[Status]			[int] NOT NULL,
	[StatusDate]		[datetime] NOT NULL,
	[No]				[varchar](30) NULL,
	[shortcode]			[varchar](4) NULL,
	[Name]				[varchar](250) NULL,
	[ename]				[varchar](250) NULL,
	[PreferName]		[tinyint] null default(1),	--1=Name,2=Ename
	[NameTitleID]		[int] NULL,
	[NameGroupID]		[int] NOT NULL,
	[NameType]			[tinyint] NULL,
	[TypeOthDesc]		[varchar](30) NULL,
	[keywords]			[varchar](30) NULL,
	[Address]			[text] NULL,
	[CityID]			[int] NULL,
	[MarketID]			[int] NULL,
	[EmployeeID]		[int] NULL,
	[zipcode]			[varchar](20) NULL,
	[TelNo]				[varchar](200) NULL,
	[FaxNo]				[varchar](200) NULL,
	[eMail]				[varchar](200) NULL,
	[homepage]			[varchar](200) NULL,
	[TaxID]				[varchar](30) NULL,
	[Contact]			[text] NULL,
	[ParentID]			[int] NULL,
	[BuyTerms]			[int] NULL,
	[Terms]				[int] NULL,
	[Credit]			[decimal](20, 2) NULL,
	[CreditType]		[tinyint] NULL,
	[BillToName]		[varchar](250) NULL,
	[BillToAddress]		[text] NULL,
	[ShipToName]		[varchar](250) NULL,
	[ShipToAddress]		[text] NULL,
	[BillingDate]		[varchar](100) NULL,
	[PayingDate]		[varchar](100) NULL,
	[isicash]			[tinyint] NULL,
	[bankno]			[varchar](20) NULL,
	[bankcode]			[varchar](3) NULL,
	[companycode]		[varchar](20) NULL,
	[remark]			[text] NULL,
	[AddBy]				[int] NULL,
	[AddWhen]			[datetime] NULL default(getdate()),
	[DelBy]				[int] NULL,
	[DelWhen]			[datetime] NULL,
CONSTRAINT [pkname] PRIMARY KEY CLUSTERED ([id]) )
GO
if not exists (select id from sifsystem.dbo.dbtables where name='name'and dbname='sifmaster' and applicationid=1)
insert sifsystem.dbo.dbtables (name,dbname,applicationid) values ('name','sifmaster',1)
go


/*
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[itemtype]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [dbo].[itemtype]
GO
CREATE TABLE [dbo].[itemtype](
	[id]				[int] IDENTITY(1,1) NOT NULL,
	[no]				[char](30) NULL,
	[name]				[varchar](100) NOT NULL,
	[dbPrefix]			[varchar](20) NOT NULL,
	[remark]			[text] NULL,
	[AddBy]				[int] NULL,
	[AddWhen]			[datetime] NULL default(getdate()),
	[DelBy]				[int] NULL,
	[DelWhen]			[datetime] NULL,
CONSTRAINT [pkitemtype] PRIMARY KEY CLUSTERED ([id]) )
GO
if not exists (select id from sifsystem.dbo.dbtables where name='itemtype'and dbname='sifmaster' and applicationid=1)
insert sifsystem.dbo.dbtables (name,dbname,applicationid) values ('itemtype','sifmaster',1)
go
*/

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[itemgroup]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [dbo].[itemgroup]
GO
CREATE TABLE [dbo].[itemgroup](
	[id]				[int] IDENTITY(1,1) NOT NULL,
	[no]				[char](30) NULL,
	[name]				[varchar](200) NOT NULL,
	[EName]				[varchar](200) NULL,
	[Account1ID]		[int] NULL,
	[Account2ID]		[int] NULL,
	[Account3ID]		[int] NULL,
	[remark]			[text] NULL,
	[AddBy]				[int] NULL,
	[AddWhen]			[datetime] NULL default(getdate()),
	[DelBy]				[int] NULL,
	[DelWhen]			[datetime] NULL,
CONSTRAINT [pkitemgroup] PRIMARY KEY CLUSTERED ([id]) )
GO
if not exists (select id from sifsystem.dbo.dbtables where name='itemgroup'and dbname='sifmaster' and applicationid=1)
insert sifsystem.dbo.dbtables (name,dbname,applicationid) values ('itemgroup','sifmaster',1)
go

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[item]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [dbo].[item]
GO
CREATE TABLE [dbo].[item](
	[id]				[int] IDENTITY(1,1) NOT NULL,
	[ItemGroupID]		[int] NOT NULL,
	[no]				[char](50) NULL,
	[name]				[varchar](200) NOT NULL,
	[EName]				[varchar](200) NULL,
	[Account1ID]		[int] NULL,
	[Account2ID]		[int] NULL,
	[Account3ID]		[int] NULL,
	[unitid]			[int] NULL,
	[itemtype]			[int] NULL,
	[percentup]			[int] NULL,
	[minqty]			[decimal](20, 2) NULL,
	[maxqty]			[decimal](20, 2) NULL,
	[remark]			[text] NULL,
	[AddBy]				[int] NULL,
	[AddWhen]			[datetime] NULL default(getdate()),
	[DelBy]				[int] NULL,
	[DelWhen]			[datetime] NULL,
CONSTRAINT [pkitem] PRIMARY KEY CLUSTERED ([id]) )
GO
if not exists (select id from sifsystem.dbo.dbtables where name='item'and dbname='sifmaster' and applicationid=1)
insert sifsystem.dbo.dbtables (name,dbname,applicationid) values ('item','sifmaster',1)
go