#20100712 Jimmy
create table app_table_counter
(
    pkid integer primary key,
    category varchar(50),
    modulename varchar(50),
    tablename varchar(50),
    entityname varchar(50),
    foreign_table1 varchar(50),
    foreign_key1 varchar(20),
    foreign_table2 varchar(50),
    foreign_key2 varchar(20),
    flag integer, -- to indicate if row table is currently being updated
    state varchar(20),
    running_number bigint,
    value1 varchar(1000),
    value2 varchar(1000),
    value3 varchar(1000),
    value4 varchar(1000),
    value5 varchar(1000),
    binary_data varchar(1000),
    status varchar(20),
    userid_edit integer,
    time_edit timestamp,
  time_effective timestamp
);

# 20100611 Jimmy
ALTER TABLE acc_cash_account MODIFY COLUMN createtime TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00';
ALTER TABLE acc_doclink MODIFY COLUMN lastupdate TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00';
ALTER TABLE acc_payment_voucher_index MODIFY COLUMN date_stmt TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00';
ALTER TABLE acc_payment_voucher_item MODIFY COLUMN date_stmt TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00';
ALTER TABLE cust_account_index MODIFY COLUMN last_edit TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00';
ALTER TABLE cust_invoice_index MODIFY COLUMN time_issued TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00';
ALTER TABLE cust_invoice_item MODIFY COLUMN bom_convert_time TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00';
ALTER TABLE cust_jobsheet_index MODIFY COLUMN time_created TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00';
ALTER TABLE cust_jobsheet_item MODIFY COLUMN bom_convert_time TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00';
ALTER TABLE cust_user_index MODIFY COLUMN dob TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00';
ALTER TABLE inv_item MODIFY COLUMN lastupdate TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00';
ALTER TABLE inv_serial_number_delta MODIFY COLUMN tx_time TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00';
ALTER TABLE inv_stock MODIFY COLUMN lastupdate TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00';
ALTER TABLE pos_sync_timing MODIFY COLUMN time_start TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00';

# 20100503
ALTER TABLE pos_config MODIFY COLUMN content_file LONGBLOB DEFAULT NULL;

# 20100311 Jimmy
ALTER TABLE pos_config ADD COLUMN content_file LONGBLOB NOT NULL;

# 20100226 Jimmy
ALTER TABLE cust_invoice_index ADD membershipcard_pkid BIGINT DEFAULT 0;
ALTER TABLE cust_invoice_index ADD group_discount DECIMAL(20,4) DEFAULT 0;
ALTER TABLE cust_invoice_index ADD consignment_id BIGINT DEFAULT 0;
ALTER TABLE cust_invoice_index ADD pos_id INTEGER DEFAULT 0;
UPDATE cust_invoice_index SET pos_id = (SELECT pos_id FROM pos_doclink WHERE cust_invoice_index.pkid = pos_doclink.invoice_id);

ALTER TABLE cust_invoice_item ADD uom VARCHAR(50) DEFAULT '';
ALTER TABLE cust_invoice_item ADD tax_option VARCHAR(50) DEFAULT '';
ALTER TABLE cust_invoice_item ADD jobsheet_item_id BIGINT DEFAULT 0;

ALTER TABLE cust_jobsheet_item ADD tax_option varchar(50) DEFAULT '';
ALTER TABLE cust_jobsheet_item ADD taxamt decimal(20,4) DEFAULT 0;
ALTER TABLE cust_jobsheet_item ADD taxamt2 decimal(20,4) DEFAULT 0;

ALTER TABLE user_index MODIFY COLUMN password VARCHAR(200);


# 20100126 Jimmy
CREATE TABLE `pos_sync_timing` (
  `pkid` INTEGER UNSIGNED NOT NULL,
  `branch_id` INTEGER UNSIGNED NOT NULL,
  `pos_id` INTEGER UNSIGNED NOT NULL,
  `user_id` INTEGER UNSIGNED NOT NULL,
  `time_start` TIMESTAMP NOT NULL,
  `time_end` TIMESTAMP NOT NULL,
  PRIMARY KEY (`pkid`)
)

# 20091230 Jimmy
ALTER TABLE `acc_receipt_index` MODIFY COLUMN `payment_method` VARCHAR(100);