
Use Config
IF((Select Count(*) From Information_Schema.Columns Where table_schema = 'dbo' And Table_Name='Posconfig')='7')
BEGIN
drop table PosConfig
CREATE TABLE [dbo].[PosConfig](
	[Id] [nvarchar](50) NOT NULL,
	[Address] [nvarchar](50) NULL,
	[TcpPort] [nvarchar](50) NULL,
	[PumpList] [nvarchar](50) NULL,
	[DiscountStatus] [nvarchar](50) NULL,
	[WorkPeriodId] [nvarchar](50) NULL,
	[PosNo] [int] NULL,
	[strPumplist] [nvarchar](500) NULL,
	[CurrentWorkPeriodId] [nvarchar](100) NULL,
	[merchantId] [nvarchar](100) NULL
) ON [PRIMARY]
END
IF((Select Count(*) From Information_Schema.Columns Where table_schema = 'dbo' And Table_Name='Posconfig')<'12')
BEGIN
   alter table dbo.posconfig 
   add password nvarchar(200),
      numoflist int
END


IF((Select Count(*) From Information_Schema.Columns Where table_schema = 'dbo' And Table_Name='Station')<'23')
BEGIN
ALTER TABLE station
ADD S3MIp nvarchar(50),
 S3MPort nvarchar(50),
 mar bigint,
 discountStatus	nvarchar(10)
END


IF((Select Count(*) From Information_Schema.Columns Where table_schema = 'dbo' And Table_Name='Pump')<'18')
BEGIN
Alter TABLE [dbo].[Pump]
ADD
    [ProductCode] [nvarchar](200) NULL,
	[ProductName] [nvarchar](200) NULL
END
IF((Select Count(*) From Information_Schema.Columns Where table_schema = 'dbo' And Table_Name='Pump')<'22')
BEGIN
 Alter TABLE [dbo].[Pump]
 ADD 
    protocoltype int,
    pumptimeout int,
    apiex nvarchar(2000),
    shortname nvarchar(200)
END

IF((Select Count(*) From Information_Schema.Columns Where table_schema = 'dbo' And Table_Name='Station')<'31')
BEGIN
alter table station
  add EgasClientAddress nvarchar(200),
      EgasClientPort nvarchar(50),
      stationId nvarchar(200),
      workShiftType int,
      urlegassynlog nvarchar(500),
      username nvarchar(200),
      password nvarchar(200),
      secretkey nvarchar(500);
END --thlb 250822

IF((Select Count(*) From Information_Schema.Columns Where table_schema = 'dbo' And Table_Name='Station')<'33')
BEGIN
alter table station
  add TimePerSend nvarchar(200) default 600 with values,
      LogPerSend nvarchar(50) default 100 with values;
END --thlb 250822

IF((Select Count(*) From Information_Schema.Columns Where table_schema = 'dbo' And Table_Name='Flag')<'26')
BEGIN
alter table flag
 add SyncLog nvarchar(50)  default '1;0;0' with values,
     timesynclog int  default 90 WITH VALUES
END --thlb 250822
IF((Select Count(*) From Information_Schema.Columns Where table_schema = 'dbo' And Table_Name='Email')<'6')
BEGIN
alter table email 
   add AlarmFinishTime bigint,
       AlarmStartTime bigint,
       AlarmValue numeric(18,4),
       AlarmTypeValue int;
END --email 151022

Use StationService
IF(Select Count(*) From Information_Schema.Columns Where table_schema = 'dbo' And Table_Name='Unitlog')<'22'
BEGIN
ALTER TABLE Unitlog
ADD  [ProductCode] [nvarchar](100) NULL,
	[ProductName] [nvarchar](100) NULL,
	[WorkPeriodId] [nvarchar](100) NULL,
	[txnid] [bigint] NULL,
	[TranType] [int] NULL,
	[discountstatus] [nvarchar](10) NULL,
	[pumptrantype] [nvarchar](10) NULL,
	[AydateKeepLog] [bigint] NULL
END
IF(Select Count(*) From Information_Schema.Columns Where table_schema = 'dbo' And Table_Name='Unitlog')<'23'
BEGIN
ALTER TABLE Unitlog
ADD  
	[aydatepayreq] [numeric](18, 0) NULL
END
IF(Select Count(*) From Information_Schema.Columns Where table_schema = 'dbo' And Table_Name='Unitlog')<'28'
BEGIN
ALTER TABLE Unitlog
ADD  
    TimeStart datetime,
    StartTime bigint,
    WsId bigint,
    Pos bigint,
    WsCode nvarchar(200);
END --thlb 250822
IF(Select Count(*) From Information_Schema.Columns Where table_schema = 'dbo' And Table_Name='Tanklog')<'21'
BEGIN
ALTER TABLE Tanklog
ADD  
     WsId bigint,
     Pos bigint;
END --thlb 250822
IF(Select Count(*) From Information_Schema.Columns Where table_schema = 'dbo' And Table_Name='Totallog')<'12'
BEGIN
ALTER TABLE Totallog
ADD  
     WsId bigint,
     Pos bigint;
END --thlb 250822

IF (NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'dbo' AND  TABLE_NAME = 'Customer'))
BEGIN
CREATE TABLE Customer(
	I bigint PRIMARY KEY NOT NULL,
	C varchar(20) NOT NULL,
	N nvarchar(250),
	ShortN nvarchar(20),
	Tel varchar(20),
	TaxCode varchar(20),
	Address nvarchar(250),
	Nationality varchar(60),
	PlxID varchar(100),
	PlxRating int,
	CONSTRAINT UQ__Customer_C UNIQUE(C)
);
END

IF (NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'dbo' AND  TABLE_NAME = 'P'))
BEGIN
CREATE TABLE P(
	I bigint PRIMARY KEY NOT NULL,
	C varchar(20) NOT NULL,
	N nvarchar(80),
	RelatedProduct bigint,
	Dispenser char(1),
	Grp1 bigint, 
	Grp2 bigint,
	Grp3 bigint,
	UoM bigint,
	IUoM bigint,
	BaseUoM bigint,
	CONSTRAINT UQ_MD__P_C UNIQUE(C)
);
END

IF (NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'dbo' AND  TABLE_NAME = 'PayLog'))
BEGIN
CREATE TABLE PayLog(
	I bigint IDENTITY(1,1) PRIMARY KEY NOT NULL,
	LogId bigint,
	POSId bigint,
	ProductId varchar(20),
	PumpId int,
	TxnId bigint,
	PAYRequestId nvarchar(255),
	PAYRefNo varchar(20),
	PAYReqDate bigint,
	PAYResDate bigint,
	PAYQty numeric(14, 4),
	CalAmt numeric(14, 4),
	PAYAmt numeric(14, 4),
	PayPrice numeric(8, 2),
	TerminalId varchar(30),
	WSId varchar(20),
	CardHolderName nvarchar(120),
	T int,
	CustCode varchar(20),
	AprovalCode nvarchar(100),
	PAYState varchar(20),
	PAYInfo nvarchar(255),
	BankCode varchar(20),
	MerchantId varchar(30),
	ClientId int,
	EgasRef varchar(255),
	PumpTranType int,
	TrType varchar(255),
	EINV varchar(255),
	licenseid varchar(255),
	invoicenumber varchar(50),
	posworkperiod varchar(50),
	sendPLXStatus int,
	RemainingAmount numeric(14, 4),
	PayType int,
	ResultType int,
	SysD bigint,
	SysU bigint,
	CONSTRAINT UQ__S3PayLog_I_POSId UNIQUE (I, POSId)
);
END
IF (SELECT COUNT(*) FROM Information_Schema.Columns WHERE table_schema = 'dbo' AND table_name = 'Paylog') < '47'
BEGIN
ALTER TABLE [dbo].[PayLog]
ADD [CardNumber] [varchar](255) NULL, 
    [ReturnAmount] [numeric](14, 4) NULL,
    [ManualStatus] [varchar](100) NULL,
    [i1] [numeric](14, 4) NULL,
    [i2] [numeric](14, 4) NULL,
    [i3] [numeric](14, 4) NULL,
    [var1] [varchar](100) NULL,
    [var2] [varchar](100) NULL,
    [var3] [varchar](100) NULL
END
IF (SELECT COUNT(*) FROM Information_Schema.Columns WHERE table_schema = 'dbo' AND table_name = 'Paylog') < '49'
BEGIN
ALTER TABLE [dbo].[PayLog]
ADD [sendPLXMsg] [nvarchar](255) NULL,
		[EntryMode] [varchar](255) NULL
END --pilot sdd 151022

IF (NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'dbo' AND  TABLE_NAME = 'PaymentCard'))
BEGIN
CREATE TABLE PaymentCard(
	I bigint PRIMARY KEY NOT NULL,
	C varchar(40) NOT NULL,
	N nvarchar(40), 
	Issuer bigint, 
	Bank bigint, 
	CardType int,
	CType int,
	CONSTRAINT UQ_MD__paymentcard_C UNIQUE(C)
);
END

IF (NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'dbo' AND  TABLE_NAME = 'PaymentCardParent'))
BEGIN
CREATE TABLE PaymentCardParent(
	I bigint PRIMARY KEY, 
	C varchar(20), 
	N nvarchar(100), 
	ShortN nvarchar(100), 
	Remarks nvarchar(500)
);
END

IF (NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'dbo' AND  TABLE_NAME = 'PaymentDiscount'))
BEGIN
CREATE TABLE PaymentDiscount(
	I bigint PRIMARY KEY NOT NULL,
	C varchar(16) NOT NULL,
	N nvarchar(100),
	Product bigint,
	Bank bigint,
	PCard  bigint,
	PType bigint,
	PLXID int,
	Discount numeric(18, 4), 
	dateFrom bigint,
	dateTo bigint,
	POSGrp bigint,
	Remarks nvarchar(1000),
	EStatus int
);
END

IF (NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'dbo' AND  TABLE_NAME = 'PaymentType'))
BEGIN
CREATE TABLE PaymentType(
	I bigint PRIMARY KEY NOT NULL,
	C varchar(16) NOT NULL,
	N nvarchar(100),
	PType int,
	Remarks nvarchar(1000),
	EStatus int,
	CONSTRAINT UQ__POSPaymentType_C UNIQUE(C)
);
END

IF (NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'dbo' AND  TABLE_NAME = 'POSBank'))
BEGIN
CREATE TABLE POSBank(
	I bigint PRIMARY KEY NOT NULL,
	C varchar(16) NOT NULL,
	No varchar(16) NOT NULL,	
	N nvarchar(100),
	MID varchar(100),
	IP varchar(100),
	PORT varchar(100),
	Bank bigint,
	POS bigint NOT NULL,
	Remarks nvarchar(1000),
	EStatus int,
	CONSTRAINT UQ__POSBank_C UNIQUE(C)
);
END

IF (NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'dbo' AND  TABLE_NAME = 'POSBankConfig'))
BEGIN
CREATE TABLE POSBankConfig(
	I bigint PRIMARY KEY NOT NULL,
	C bigint NOT NULL,
	Pump bigint NOT NULL,
	POSBank bigint NOT NULL,
	POS bigint,
	QtyLog int,
	Remarks nvarchar(1000),
	EStatus int
);	
END

IF (NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'dbo' AND  TABLE_NAME = 'S3MErrorLog'))
BEGIN
CREATE TABLE S3MErrorLog(
	Id nvarchar(50) NOT NULL,
	Time datetime,
	Number numeric(18, 4),
	Source nvarchar(50),
	Description nvarchar(1000),
	AyDate bigint,
	Name varchar(255)
);
END

IF (NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'dbo' AND  TABLE_NAME = 'S3MEventLog'))
BEGIN
CREATE TABLE S3MEventLog(
	Id nvarchar(50) NOT NULL,
	Name varchar(255),
	EventTime datetime,
	AyDate bigint,
	Des nvarchar(1000),
	Flag int
);
END

IF (NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'dbo' AND  TABLE_NAME = 'Sysparams'))
BEGIN
CREATE TABLE Sysparams(
	I bigint PRIMARY KEY NOT NULL,
	C varchar(60) NOT NULL,
	sysvalue varchar(500),
	paramClass int, 
	CONSTRAINT UQ_MD__Sysparams_1 UNIQUE (C, paramClass)
);
END

IF (NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'dbo' AND  TABLE_NAME = 'TAXCODES'))
BEGIN
CREATE TABLE TAXCODES(
	I bigint PRIMARY KEY NOT NULL,
	MST varchar(14) NOT NULL,
	Name nvarchar(255),
	Address nvarchar(255),
	Email nvarchar(255),
	Keys nvarchar(1000),
	PLXID varchar(100)
);
END

IF (NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'dbo' AND  TABLE_NAME = 'TPRICE'))
BEGIN
CREATE TABLE TPRICE(
	I bigint PRIMARY KEY NOT NULL,
	B bigint,
	N varchar(20),
	D bigint,
	WaterID bigint,
	i1 numeric(18, 4),
	i3 numeric(18, 4),
	i4 numeric(18, 4),
	i5 numeric(18, 4),
	i7 numeric(18, 4),
	i10 numeric(18, 4)
);
END

IF (NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'dbo' AND  TABLE_NAME = 'TXN_SEQ'))
BEGIN
CREATE TABLE TXN_SEQ(
	ID [bigint] IDENTITY(1,1) PRIMARY KEY NOT NULL,
	iDate datetime
);
END
IF (NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'dbo' AND  TABLE_NAME = 'PumpLog'))
BEGIN
CREATE TABLE PumpLog(
	I bigint PRIMARY KEY NOT Null,
	POS bigint,
	WShift varchar(20),
	PumpTDHID int,
	ProductI varchar(20),
	SalePrice numeric(8, 2),
	cmdAmt numeric(12, 2),
	cmdQty numeric(12, 2),
	cmdTime bigint,
	retAmt numeric(12, 2),
	retQty numeric(12, 4),
	retTime bigint,
	jobStatus int,
	prevStatus int,
	LockStatus int,
	LockTime bigint,
	DiscountStatus int,
	PAYId bigint,
	sysU bigint,
	time datetime,
	refnumber nvarchar(100),
	strDiscountStatus nvarchar(50),
	strProductId nvarchar(50),
	strPOS nvarchar(100),
	strWshift nvarchar(100),
	pumptrantype nvarchar(10)
)
END
IF (NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'dbo' AND  TABLE_NAME = 'TemperatureOnline'))
BEGIN
CREATE TABLE TemperatureOnline(
	Time datetime,
	TankNo nvarchar(50),
	TankName nvarchar(50),
	Temperature nvarchar(50),
	status nvarchar(50)
)
End
IF OBJECT_ID('dbo.v_Customer') IS NULL
BEGIN
	EXECUTE ( 'CREATE VIEW  v_Customer AS SELECT I, C, N, ShortN, Tel, TaxCode, Address, Nationality AS Mail, PlxID, PlxRating FROM Customer')
END

IF NOT EXISTS(SELECT * FROM sys.indexes WHERE name = 'Index_I' AND object_id = OBJECT_ID('UnitLog'))
BEGIN
	CREATE INDEX Index_I ON UnitLog (I DESC);
END
GRANT SELECT ON ErrorLog TO pragas;  
GRANT SELECT, INSERT, UPDATE, DELETE ON Customer TO pragas;  
GRANT SELECT, INSERT, UPDATE, DELETE ON EventLog TO pragas;  
GRANT SELECT, INSERT, UPDATE, DELETE ON P TO pragas;  
GRANT SELECT, INSERT, UPDATE, DELETE ON PaymentCard TO pragas;
GRANT SELECT, INSERT, UPDATE, DELETE ON PaymentCardParent TO pragas;  
GRANT SELECT, INSERT, UPDATE, DELETE ON PaymentDiscount TO pragas;  
GRANT SELECT, INSERT, UPDATE, DELETE ON PaymentType TO pragas;  
GRANT SELECT, INSERT, UPDATE, DELETE ON PosBank TO pragas;  
GRANT SELECT, INSERT, UPDATE, DELETE ON PosBankConfig TO pragas;  
GRANT SELECT, INSERT, UPDATE, DELETE ON PosLog TO pragas;  
GRANT SELECT, INSERT, UPDATE, DELETE ON TaxCodes TO pragas;  
GRANT SELECT, INSERT, UPDATE, DELETE ON PrintLog TO pragas;  
GRANT SELECT, INSERT, UPDATE, DELETE ON PumpLog TO pragas; 
GRANT SELECT, INSERT, UPDATE, DELETE ON Sysparams TO pragas;  
GRANT SELECT, INSERT, UPDATE, DELETE ON TXN_SEQ TO pragas;
GRANT SELECT, INSERT, UPDATE, DELETE ON TPRICE TO pragas;
GRANT SELECT, INSERT, UPDATE, DELETE ON PAYLOG TO pragas;
GRANT SELECT, INSERT, UPDATE, DELETE ON S3MEventLog TO pragas;
GRANT SELECT, INSERT, UPDATE, DELETE ON S3MErrorLog TO pragas;
