JLH/JLHHJSvr/DBA/ParkDBVersion.cs

using LJLib.DBOption;
//CREATE TABLE st_serial
//( 
//   tubeCode varchar(20) NOT NULL CONSTRAINT DF_st_serial_tubeCode DEFAULT(''),
//   claimCode varchar(20) NOT NULL CONSTRAINT DF_st_serial_claimCode DEFAULT(''),
//   activationCode varchar(20) NULL,
//   innerCode varchar(20) NOT NULL CONSTRAINT DF_st_serial_innerCode DEFAULT(''),
//   ifBP tinyint NOT NULL CONSTRAINT DF_st_serial_ifBP DEFAULT(0),
//   bpLotNumber varchar(20) NOT NULL CONSTRAINT DF_st_serial_bpLotNumber DEFAULT(''),
//   lotNumber varchar(30) NOT NULL CONSTRAINT DF_st_serial_lotNumber DEFAULT(''),
//   solFillDate datetime NULL,
//   returnMailerTrackingNumber varchar(255) NOT NULL CONSTRAINT DF_st_serial_returnMailerTrackingNumber DEFAULT(''),
//   ifFG tinyint NOT NULL CONSTRAINT DF_st_serial_ifFG DEFAULT(0),
//   containerNumber varchar(20) NOT NULL CONSTRAINT DF_st_serial_containerNumber DEFAULT(''),
//   packCartonID varchar(255) NOT NULL CONSTRAINT DF_st_serial_packCartonID DEFAULT(''),
//   FGCarton varchar(255) NOT NULL CONSTRAINT DF_st_serial_FGCarton DEFAULT(''),
//   packPalletID varchar(255) NOT NULL CONSTRAINT DF_st_serial_packPalletID DEFAULT(''),
//   FGPallet varchar(255) NOT NULL CONSTRAINT DF_st_serial_FGPallet DEFAULT(''),
//   FGmanufacturedDate datetime NULL,
//   gNum int NOT NULL CONSTRAINT DF_st_serial_gNum DEFAULT(0),
//   cartonNum int NOT NULL CONSTRAINT DF_st_serial_cartonNum DEFAULT(0),
//   syncBP tinyint NOT NULL CONSTRAINT DF_st_serial_syncBP DEFAULT(0),
//   syncFG tinyint NOT NULL CONSTRAINT DF_st_serial_syncFG DEFAULT(0),
//   syncASN tinyint NOT NULL CONSTRAINT DF_st_serial_syncASN DEFAULT(0),
//   CONSTRAINT PK_ST_SERIAL PRIMARY KEY (tubeCode)
//)
namespace JLHHJSvr.DBA
{
    public sealed class ParkDbVersion : DBVersionSql
    {
        protected override string currentVersion
        {
            get { return "1.0.250923"; }
        }

        protected override string dbname
        {
            get { return "Park"; }
        }

        protected override DBVersionSql.Script[] scripts
        {
            get
            {
                return new Script[]
                {
new Script("1.0.171204", @"CREATE TABLE st_user 
( 
   userid int NOT NULL, 
   usercode varchar(20) NOT NULL CONSTRAINT DF_st_user_usercode DEFAULT(''), 
   username varchar(20) NOT NULL CONSTRAINT DF_st_user_username DEFAULT(''), 
   psw varchar(255) NOT NULL CONSTRAINT DF_st_user_psw DEFAULT(''), 
   sex varchar(10) NOT NULL CONSTRAINT DF_st_user_sex DEFAULT(''), 
   tel varchar(50) NOT NULL CONSTRAINT DF_st_user_tel DEFAULT(''), 
   dscrp varchar(255) NOT NULL CONSTRAINT DF_st_user_dscrp DEFAULT(''), 
   opemp varchar(20) NOT NULL CONSTRAINT DF_st_user_opemp DEFAULT(''), 
   opdate datetime NOT NULL CONSTRAINT DF_st_user_opdate DEFAULT(GETDATE()), 
   modemp varchar(20) NOT NULL CONSTRAINT DF_st_user_modemp DEFAULT(''), 
   moddate datetime NULL, 
   CONSTRAINT PK_ST_USER PRIMARY KEY (userid)
)", ""),

new Script("1.0.171204", @"CREATE UNIQUE NONCLUSTERED INDEX IX_st_user ON st_user 
(
   usercode ASC
)", ""),

new Script("1.0.171204", @"CREATE TABLE st_user_power 
( 
   userid int NOT NULL, 
   funid int NOT NULL, 
   CONSTRAINT PK_ST_USER_POWER PRIMARY KEY (userid, funid)
)", ""),

new Script("1.0.171204", @"CREATE TABLE st_road 
( 
   roadid int NOT NULL, 
   roadname varchar(255) NOT NULL CONSTRAINT DF_st_road_roadname DEFAULT(''), 
   groupname varchar(255) NOT NULL CONSTRAINT DF_st_road_groupname DEFAULT(''), 
   manager varchar(20) NOT NULL CONSTRAINT DF_st_road_manager DEFAULT(''), 
   man_tel varchar(50) NOT NULL CONSTRAINT DF_st_road_man_tel DEFAULT(''), 
   cnt int NOT NULL CONSTRAINT DF_st_road_cnt DEFAULT(0), 
   dscrp varchar(255) NOT NULL CONSTRAINT DF_st_road_dscrp DEFAULT(''), 
   opemp varchar(20) NOT NULL CONSTRAINT DF_st_road_opemp DEFAULT(''), 
   opdate datetime NOT NULL CONSTRAINT DF_st_road_opdate DEFAULT(GETDATE()), 
   modemp varchar(20) NOT NULL CONSTRAINT DF_st_road_modemp DEFAULT(''), 
   moddate datetime NULL, 
   CONSTRAINT PK_ST_ROAD PRIMARY KEY (roadid)
)", ""),

new Script("1.0.171204", @"CREATE UNIQUE NONCLUSTERED INDEX IX_st_road ON st_road 
(
   roadname ASC
)", ""),

new Script("1.0.171204", @"CREATE TABLE st_car 
(
   carnum varchar(20) NOT NULL, 
   OWNER varchar(20) NOT NULL CONSTRAINT DF_st_car_OWNER DEFAULT(''), 
   owner_tel varchar(50) NOT NULL CONSTRAINT DF_st_car_owner_tel DEFAULT(''), 
   dscrp varchar(255) NOT NULL CONSTRAINT DF_st_car_dscrp DEFAULT(''), 
   opemp varchar(20) NOT NULL CONSTRAINT DF_st_car_opemp DEFAULT(''), 
   opdate datetime NOT NULL CONSTRAINT DF_st_car_opdate DEFAULT(GETDATE()), 
   modemp varchar(20) NOT NULL CONSTRAINT DF_st_car_modemp DEFAULT(''), 
   moddate datetime NULL, 
   ifinput tinyint NOT NULL CONSTRAINT DF_st_car_ifinput DEFAULT(0), 
   CONSTRAINT PK_ST_CAR PRIMARY KEY (carnum)
)", ""),

new Script("1.0.171204", @"CREATE TABLE st_permit 
( 
   pid int NOT NULL, 
   carnum varchar(20) NOT NULL CONSTRAINT DF_st_permit_carnum DEFAULT(''), 
   OWNER varchar(20) NOT NULL CONSTRAINT DF_st_permit_OWNER DEFAULT(''), 
   owner_tel varchar(50) NOT NULL CONSTRAINT DF_st_permit_owner_tel DEFAULT(''), 
   begindate datetime NOT NULL CONSTRAINT DF_st_permit_begindate DEFAULT(GETDATE()), 
   enddate datetime NOT NULL CONSTRAINT DF_st_permit_enddate DEFAULT(GETDATE()), 
   iftmp tinyint NOT NULL CONSTRAINT DF_st_permit_iftmp DEFAULT(0), 
   inuse tinyint NOT NULL CONSTRAINT DF_st_permit_inuse DEFAULT(0), 
   roadid int NOT NULL CONSTRAINT DF_st_permit_roadid DEFAULT(0), 
   dscrp varchar(255) NOT NULL CONSTRAINT DF_st_permit_dscrp DEFAULT(''), 
   opemp varchar(20) NOT NULL CONSTRAINT DF_st_permit_opemp DEFAULT(''), 
   opdate datetime NOT NULL CONSTRAINT DF_st_permit_opdate DEFAULT(GETDATE()), 
   modemp varchar(20) NOT NULL CONSTRAINT DF_st_permit_modemp DEFAULT(''), 
   moddate datetime NULL, 
   paytype tinyint NOT NULL CONSTRAINT DF_st_permit_paytype DEFAULT(0), 
   payamt decimal(18,5) NOT NULL CONSTRAINT DF_st_permit_payamt DEFAULT(0), 
   CONSTRAINT PK_ST_PERMIT PRIMARY KEY (pid)
)", ""),

new Script("1.0.171204", @"CREATE TABLE cd_idfactory 
( 
   idkey varchar(255) NOT NULL, 
   idvalue int NOT NULL CONSTRAINT DF_cd_idfactory_idvalue DEFAULT(0), 
   CONSTRAINT PK_CD_IDFACTORY PRIMARY KEY (idkey)
)", ""),

new Script("1.0.171204", @"CREATE TABLE st_bill 
( 
   billid int NOT NULL, 
   carnum varchar(20) NOT NULL CONSTRAINT DF_st_bill_carnum DEFAULT(''), 
   OWNER varchar(20) NOT NULL CONSTRAINT DF_st_bill_OWNER DEFAULT(''), 
   owner_tel varchar(50) NOT NULL CONSTRAINT DF_st_bill_owner_tel DEFAULT(''), 
   dscrp varchar(255) NOT NULL CONSTRAINT DF_st_bill_dscrp DEFAULT(''), 
   flag tinyint NOT NULL CONSTRAINT DF_st_bill_flag DEFAULT(0), 
   auditemp varchar(20) NOT NULL CONSTRAINT DF_st_bill_auditemp DEFAULT(''), 
   auditdate datetime NULL, 
   opemp varchar(20) NOT NULL CONSTRAINT DF_st_bill_opemp DEFAULT(''), 
   opdate datetime NOT NULL CONSTRAINT DF_st_bill_opdate DEFAULT(GETDATE()), 
   modemp varchar(20) NOT NULL CONSTRAINT DF_st_bill_modemp DEFAULT(''), 
   moddate datetime NULL, 
   paytype tinyint NOT NULL CONSTRAINT DF_st_bill_paytype DEFAULT(0), 
   payamt decimal(18,5) NOT NULL CONSTRAINT DF_st_bill_payamt DEFAULT(0), 
   CONSTRAINT PK_ST_BILL PRIMARY KEY (billid)
)", ""),

new Script("1.0.171204", @"CREATE TABLE st_file 
( 
   filemd5 varchar(255) NOT NULL, 
   filedata image NOT NULL, 
   CONSTRAINT PK_ST_FILE PRIMARY KEY (filemd5)
)", ""),

new Script("1.0.171204", @"CREATE TABLE st_bill_mx 
( 
   billid int NOT NULL, 
   printid int NOT NULL, 
   filemd5 varchar(255) NOT NULL CONSTRAINT DF_st_bill_mx_filemd5 DEFAULT(''), 
   CONSTRAINT PK_ST_BILL_MX PRIMARY KEY (billid, printid)
)", ""),
  new Script("1.0.171209", @"CREATE TABLE st_option
(
    optionid int NOT NULL,
    optionname varchar(200) NOT NULL CONSTRAINT DF_st_option_optionname DEFAULT(''),
    optionvalue varchar(200) NOT NULL CONSTRAINT DF_st_option_optionvalue DEFAULT(''),
    optiondscrp varchar(200) NOT NULL CONSTRAINT DF_st_option_optiondscrp DEFAULT(''),
    CONSTRAINT PK_st_option PRIMARY KEY
    (
        optionid
    )
)", ""),
new Script("1.0.171211", @"
ALTER TABLE st_option ADD modemp varchar(20) NOT NULL CONSTRAINT DF_st_option_modemp DEFAULT('')
ALTER TABLE st_option ADD moddate datetime NULL

ALTER TABLE st_file ADD fileType varchar(20) NOT NULL CONSTRAINT DF_st_file_fileType DEFAULT('')
", ""),
 new Script("1.0.171215", @"
ALTER TABLE st_bill ADD roadid int NOT NULL CONSTRAINT DF_st_bill_roadid DEFAULT(0)
", ""),
  new Script("1.0.180104", @"
CREATE TABLE fx_user_dwlayout(
	userid int NOT NULL,
	dwname varchar(50) NOT NULL,
	column_visible text NOT NULL,
	column_width text NOT NULL,
	column_x text NOT NULL,
	column_autosize int NOT NULL,
	column_detail_height int NOT NULL,
 CONSTRAINT PK_fx_user_dwlayout PRIMARY KEY CLUSTERED 
(
	userid ASC,
	dwname ASC
)
)

ALTER TABLE fx_user_dwlayout ADD  CONSTRAINT DF_fx_user_dwlayout_column_visible  DEFAULT ('') FOR column_visible
ALTER TABLE fx_user_dwlayout ADD  CONSTRAINT DF_fx_user_dwlayout_column_width  DEFAULT ('') FOR column_width
ALTER TABLE fx_user_dwlayout ADD  CONSTRAINT DF_fx_user_dwlayout_column_x  DEFAULT ('') FOR column_x
ALTER TABLE fx_user_dwlayout ADD  CONSTRAINT DF_fx_user_dwlayout_column_autosize  DEFAULT (-1) FOR column_autosize
ALTER TABLE fx_user_dwlayout ADD  CONSTRAINT DF_fx_user_dwlayout_column_detail_height  DEFAULT (0) FOR column_detail_height
", ""),
  new Script("1.0.250730", @"
ALTER TABLE u_user_jlhprice ADD whiteIPs varchar(2000) NULL
", ""),
    new Script("1.0.250919", @"
IF NOT EXISTS (SELECT * FROM Sys_scIdentity WHERE tablename = 'u_sys_post' AND columnname = 'postid')
BEGIN
    INSERT INTO Sys_scIdentity (scid, tablename, columnname, ScIdentityno)
    VALUES (0, 'u_sys_post', 'postid', 0);
END

IF NOT EXISTS (SELECT * FROM sys.tables WHERE name = 'u_sys_post')
BEGIN
    CREATE TABLE u_sys_post (
        postid int NOT NULL CONSTRAINT DF_u_sys_post_postid DEFAULT 0,
        scid int NOT NULL CONSTRAINT DF_u_sys_post_scid DEFAULT 0,
        deptid int NOT NULL CONSTRAINT DF_u_sys_post_deptid DEFAULT 0,
        empid int NOT NULL CONSTRAINT DF_u_sys_post_empid DEFAULT 0,
        type int NOT NULL CONSTRAINT DF_u_sys_post_type DEFAULT 0,
        empids char(1024) NOT NULL CONSTRAINT DF_u_sys_post_empids DEFAULT '',
        sdate datetime NULL,
        edate datetime NULL,
        dscrp varchar(4000) NOT NULL CONSTRAINT DF_u_sys_post_dscrp DEFAULT '',
        opemp char(20) NOT NULL CONSTRAINT DF_u_sys_post_opemp DEFAULT '',
        opdate datetime NULL,
        level int NOT NULL CONSTRAINT DF_u_sys_post_level DEFAULT 0,
        CONSTRAINT UK_u_sys_post PRIMARY KEY (postid)
    );
END
", ""),
    new Script("1.0.250920", @"ALTER TABLE u_mtrl_price ADD is_singleqty BIT NOT NULL CONSTRAINT DF_u_lock_table_is_singleqty DEFAULT(0)", ""),
    new Script("1.0.250923", @"
IF NOT EXISTS (SELECT * FROM sys_func_pwr WHERE funcid = 161)
BEGIN
    INSERT INTO sys_func_pwr (funcid, parentid, treename, menuname, if_use)
    VALUES
    (161, 109, '单据解锁', '单据解锁', 1),
    (162, 161, '单据解锁','解锁', 1);
END

IF NOT EXISTS (SELECT * FROM sys.tables WHERE name = 'u_lock_table')
BEGIN
    CREATE TABLE u_lock_table (
        logid int IDENTITY(1,1) NOT NULL,
        keyword VARCHAR(24) NOT NULL CONSTRAINT DF_u_lock_table_keyword DEFAULT(''),
        billid INT NOT NULL CONSTRAINT DF_u_lock_table_billid DEFAULT(0),
        billcode VARCHAR(50) NOT NULL CONSTRAINT DF_u_lock_table_billcode DEFAULT(''),
        is_locked BIT NOT NULL CONSTRAINT DF_u_lock_table_is_locked DEFAULT(1),
        opemp VARCHAR(24) NOT NULL CONSTRAINT DF_u_lock_table_opemp DEFAULT(''),
        lock_time DATETIME NOT NULL CONSTRAINT DF_u_lock_table_lock_time DEFAULT(GETDATE()),
        expire_time DATETIME NULL CONSTRAINT DF_u_lock_table_expire_time DEFAULT(NULL),
        CONSTRAINT UK_u_lock_table PRIMARY KEY (logid),
        CONSTRAINT UQ_u_lock_table UNIQUE (keyword, billid)
    );
END

IF NOT EXISTS (SELECT * FROM sys.procedures WHERE name = 'sp_lock_record')
BEGIN
    EXEC('
    CREATE PROCEDURE sp_lock_record
        @keyword VARCHAR(24),
        @billid INT,
        @billcode VARCHAR(50),
        @opemp VARCHAR(24),
        @lockMinutes INT = 30
    AS
    BEGIN
        SET NOCOUNT ON;
        DECLARE @now DATETIME = GETDATE();
        DECLARE @expire DATETIME = DATEADD(MINUTE, @lockMinutes, @now);

        DELETE FROM u_lock_table
        WHERE keyword = @keyword AND billid = @billid
          AND expire_time IS NOT NULL AND expire_time < @now;

        BEGIN TRY
            INSERT INTO u_lock_table (keyword, billid, billcode, is_locked, opemp, lock_time, expire_time)
            VALUES (@keyword, @billid, @billcode, 1, @opemp, @now, @expire);
            SELECT 1 AS arg_rslt, ''加锁成功'' AS arg_msg;
        END TRY
        BEGIN CATCH
            SELECT 0 AS arg_rslt, ''当前单据已被锁定，不能编辑'' AS arg_msg;
        END CATCH
    END;
    ');
END

IF NOT EXISTS (SELECT * FROM sys.procedures WHERE name = 'sp_unlock_record')
BEGIN
    EXEC('
    CREATE PROCEDURE sp_unlock_record
        @keyword VARCHAR(24),
        @billid INT,
        @opemp VARCHAR(24),
        @is_admin BIT = 0
    AS
    BEGIN
        SET NOCOUNT ON;
        
        IF @is_admin = 1
            DELETE FROM u_lock_table
            WHERE keyword = @keyword AND billid = @billid;
        ELSE
            DELETE FROM u_lock_table
            WHERE keyword = @keyword AND billid = @billid
              AND opemp = @opemp;
        
        IF @@ROWCOUNT > 0
            SELECT 1 AS arg_rslt, ''解锁成功'' AS arg_msg;
        ELSE
            SELECT 0 AS arg_rslt, 
                   CASE 
                       WHEN @is_admin = 1 THEN ''解锁失败，记录不存在或已过期''
                       ELSE ''解锁失败，原因:可能不是本人加的锁或已过期''
                   END AS arg_msg;
    END;
    ');
END
", ""),
                };
            }
        }
    }
}