--在A8数据库,执行此脚本


--导入的数据库配置

--if exists(select * from tempdb..sysobjects where id=object_id('tempdb..#ImportConfig'))
IF object_id('TImportConfig#') IS NOT  NULL
BEGIN
    DROP TABLE TImportConfig#
END
GO
CREATE TABLE TImportConfig#
(
    FFromServer VARCHAR(200),
    FFromDatabase VARCHAR(200),
    FToServer VARCHAR(200),
    FToDatabase VARCHAR(200)
)
GO

--------------------------------------------------
--生成ID
--------------------------------------------------
IF object_id('dbo.FNNewId') IS NOT NULL
BEGIN
    DROP FUNCTION dbo.FNNewId
END
GO

CREATE FUNCTION FNNewId() 
RETURNS VARCHAR(40) 
AS
BEGIN
    DECLARE @Id BIGINT
    SELECT @Id = Id FROM VNewId
    RETURN @Id
END
GO

--------------------------------------------------
--生成ID
--------------------------------------------------
if exists (select 1
            from  sysobjects
           where  id = object_id('VNewId')
            and   type = 'V')
   drop view VNewId
GO

create view VNewId as
SELECT Id = abs( CAST( CAST(newid() AS BINARY(16)) AS BIGINT))
GO

IF object_id('dbo.FNGetFirstPinYin') IS NOT NULL
BEGIN
    DROP FUNCTION dbo.FNGetFirstPinYin
END
GO

--------------------------------------------------
--生成拼音
--------------------------------------------------

CREATE FUNCTION FNGetFirstPinYin(@chinese varchar(1024)) 
RETURNS varchar(1024) 
AS
BEGIN
    declare @strlen int,@return varchar(500),@ii int  
    declare @n int,@c char(1),@chn nchar(1)  
  
    select @strlen=len(@chinese),@return='',@ii=0  
    set @ii=0  
    while @ii<@strlen  
    begin  
        select @ii=@ii+1,@n=63,@chn=substring(@chinese,@ii,1)  
        if @chn>'z'  
        begin
            select @n = @n +1,
                   @c = case chn when @chn then char(@n) else @c end  
              from
                (select top 27 * from 
                    (select chn = '吖'  
                      union all select '八'  
                      union all select '嚓'  
                      union all select '咑'  
                      union all select '妸'  
                      union all select '发'  
                      union all select '旮'  
                      union all select '铪'  
                      union all select '丌'  --because have no 'i'  
                      union all select '丌'  
                      union all select '咔'  
                      union all select '垃'  
                      union all select '嘸'  
                      union all select '拏'  
                      union all select '噢'  
                      union all select '妑'  
                      union all select '七'  
                      union all select '呥'  
                      union all select '仨'  
                      union all select '他'  
                      union all select '屲'  --no 'u'  
                      union all select '屲'  --no 'v'  
                      union all select '屲'  
                      union all select '夕'  
                      union all select '丫'  
                      union all select '帀'  
                      union all select @chn) as a  
                      order by chn COLLATE Chinese_PRC_CI_AS ) as b  
        end
        else 
        begin
            set @c=@chn  
        end
        set @return=@return+@c  
    end  
    return(@return)  
END
GO


--转换ID
IF object_id('dbo.FNBKId') IS NOT NULL
BEGIN
    DROP FUNCTION dbo.FNBKId
END
GO

CREATE FUNCTION FNBKId 
(
    @Value varchar(5)
)
RETURNS BIGINT
BEGIN
    RETURN cast(cast(@Value as binary(8)) as bigint)
END
GO


--转换公司ID
IF object_id('dbo.FNBKCompId') IS NOT NULL
BEGIN
    DROP FUNCTION dbo.FNBKCompId
END
GO

CREATE FUNCTION FNBKCompId 
(
    @Value varchar(5)
)
RETURNS BIGINT
BEGIN
    IF @Value = '001'
        RETURN 1

    RETURN dbo.FNBKid(@Value);
END
GO

--转换日期
IF object_id('dbo.FNBKDate') IS NOT NULL
BEGIN
    DROP FUNCTION dbo.FNBKDate
END
GO

CREATE FUNCTION FNBKDate 
(
    @FmtDate varchar(10)
)
RETURNS DATETIME
BEGIN
        IF CHARINDEX('-',@FmtDate) > -1
        RETURN @FmtDate
    IF @FmtDate  IS NULL OR ISNULL(@FmtDate,'') = '' OR (LEN(@FmtDate) != 8 AND LEN(@FmtDate) != 4) OR @FMTDATE = '000000'
    BEGIN
        RETURN NULL
    END
    IF LEN(@FmtDate) = 4
    BEGIN
        SELECT @FmtDate = '9999' + @FmtDate
    END

    DECLARE @Year VARCHAR(4)
    DECLARE @Month VARCHAR(2)
    DECLARE @Day VARCHAR(2)
    SELECT @Year = LEFT(@FmtDate,4),@Month = SUBSTRING(@FmtDate,5,2),@Day = RIGHT(@FmtDate,2)


    IF @Year < '1753'
    BEGIN
        SELECT @Year = '9999'
    END
    
    IF  @Month < '01' OR @Month > '12' OR @Day < '01' OR @Day > '31'
    BEGIN
        RETURN NULL
    END
    
    IF @Day > '30' AND @Month IN('04','06','09','11')
    BEGIN
        SET @Day = '30'
    END
    
    --IF @Month = '02' AND @Day = '29'
    --BEGIN
    --   print ''-- SET @Year = '2000'
    --END
    
    select @FmtDate = @Year + '-' + @Month + '-' + @Day
   
    return @FmtDate
END
GO


--转换日期
IF object_id('dbo.FNBKBirthday') IS NOT NULL
BEGIN
    DROP FUNCTION dbo.FNBKBirthday
END
GO

CREATE FUNCTION FNBKBirthday 
(
    @FmtDate varchar(10)
)
RETURNS DATETIME
BEGIN
    SET @FmtDate = REPLACE(@FmtDate,' ','')
    IF ISNULL(@FmtDate,'') = '' OR (LEN(@FmtDate) != 8 AND LEN(@FmtDate) != 4) OR @FMTDATE = '00000000' OR @FMTDATE = '0000' 
    BEGIN
        RETURN NULL
    END
    IF LEN(@FmtDate) = 4
    BEGIN
        SELECT @FmtDate = '9999' + @FmtDate
    END
    IF LEFT(@FmtDate,4) = '0000'
    BEGIN
        SELECT @FmtDate = REPLACE(@FmtDate,'0000','9999')
    END

    DECLARE @Year VARCHAR(4)
    DECLARE @Month VARCHAR(2)
    DECLARE @Day VARCHAR(2)
    SELECT @Year = LEFT(@FmtDate,4),@Month = SUBSTRING(@FmtDate,5,2),@Day = RIGHT(@FmtDate,2)


    IF @Year < '1753'
    BEGIN
        SELECT @Year = '9999'
    END
    
    IF  @Month < '01' OR @Month > '12' OR @Day < '01' OR @Day > '31'
    BEGIN
        RETURN NULL
    END
    
    IF @Day > '30' AND @Month IN('04','06','09','11')
    BEGIN
        SET @Day = '30'
    END
    
    IF @Month = '02' AND @Day >= '29'
    BEGIN
        SET @Day = '29'
        IF @Year % 4 != 0
            SET @Day = '28'
        
    END
    
    select @FmtDate = @Year + '-' + @Month + '-' + @Day
   
    return @FmtDate
END
GO


--转换时间
IF object_id('dbo.FNBKTime') IS NOT NULL
BEGIN
    DROP FUNCTION dbo.FNBKTime
END
GO

CREATE FUNCTION FNBKTime 
(
    @FmtDate varchar(1000),
    @FmtTime varchar(1000)
)
RETURNS DATETIME
BEGIN
    IF ISNULL(@FmtDate,'') = '' OR LEN(@FmtDate) != 8
    BEGIN
        RETURN NULL
    END
    DECLARE @Year VARCHAR(4)
    DECLARE @Month VARCHAR(2)
    DECLARE @Day VARCHAR(2)
    SELECT @Year = LEFT(@FmtDate,4),@Month = SUBSTRING(@FmtDate,5,2),@Day = RIGHT(@FmtDate,2)

    
    IF @Year < '1753'
    BEGIN
        SELECT @Year = '1753'
    END
    
    IF @Month = '02' AND @Day = '29' AND @Year % 4 != 0
    BEGIN
        SET @Day = '28'
    END
    
    IF  @Month < '01' OR @Month > '12' OR @Day < '01' OR @Day > '31'
    BEGIN
        RETURN NULL
    END
    
    select @FmtDate = @Year + '-' + @Month + '-' + @Day
    
    IF ISNULL(@FmtTime,'') = '' OR LEN(@FmtTime) != 6
    BEGIN
        SET @FmtTime = '120000'
    END
    
    select @FmtDate = @FmtDate + ' ' + LEFT(@FmtTime,2) + ':' + SUBSTRING(@FmtTime,3,2) + ':' + RIGHT(@FmtTime,2)
    return @FmtDate
END
GO

--转换积分方式
IF object_id('dbo.FNBKPointMode') IS NOT NULL
BEGIN
    DROP FUNCTION dbo.FNBKPointMode
END
GO

CREATE FUNCTION FNBKPointMode 
(
    @Value varchar(1)
)
RETURNS INT
BEGIN
    IF ISNULL(@Value,'') = '' 
    BEGIN
        RETURN 0
    END
    RETURN CASE @Value 
           WHEN '1' THEN 1 --固定积分
           WHEN '2' THEN 2 --营业额比率
           WHEN '3' THEN 7 --营业额比率
           WHEN '4' THEN 3 --实际营业额比率
           ELSE 0 END
END
GO
  
  
--单据类别
IF object_id('dbo.FNBKBillType') IS NOT NULL
BEGIN
    DROP FUNCTION dbo.FNBKBillType
END
GO

CREATE FUNCTION FNBKBillType 
(
    @Value varchar(20)
)
RETURNS VARCHAR(30)
BEGIN
    return  11
END
GO

--账户操作类别
IF object_id('dbo.FNBKAcctOper') IS NOT NULL
BEGIN
    DROP FUNCTION dbo.FNBKAcctOper
END
GO

CREATE FUNCTION FNBKAcctOper 
(
    @Value varchar(20)
)
RETURNS VARCHAR(30)
BEGIN



    IF @Value = '0'
    BEGIN
        return '充值'
    END
    ELSE IF @Value = '1'
    BEGIN
        RETURN '取款'
    END
    ELSE IF @Value = '2'
    BEGIN
        RETURN '消费'
    END
    ELSE IF @Value = '5'
    BEGIN
        RETURN '欠款'
    END
    ELSE IF @Value = '6'
    BEGIN
        RETURN '欠款返还'
    END
    RETURN ''
END
GO


--项目业绩方式

IF object_id('dbo.FNBKItemPerfMode') IS NOT NULL
BEGIN
    DROP FUNCTION dbo.FNBKItemPerfMode
END
GO

CREATE FUNCTION FNBKItemPerfMode 
(
    @Value INT
)
RETURNS VARCHAR(30)
BEGIN
    RETURN
    CASE @Value
    WHEN 1 THEN 1  --固定
    WHEN 2 THEN 2  --营业额
    WHEN 5 THEN 7  --标准价
    WHEN 9 THEN 3  --实收额
    WHEN 6 THEN 202--营业额扣第二人
    WHEN 7 THEN 203--营业额扣第三人
    WHEN 8 THEN 205--营业额扣二三人
    
    WHEN 61 THEN 302--实收额扣二人
    WHEN 71 THEN 303--实收额扣三人
    WHEN 81 THEN 305--实收额扣二三人
    
    WHEN 62 THEN 212--实收额先扣二人
    WHEN 72 THEN 213--实收额先扣三人
    WHEN 82 THEN 215--实收额先扣二三人
    
    
    ELSE 0 END

END
GO
    
--提成方式
IF object_id('dbo.FNBKItemCommMode') IS NOT NULL
BEGIN
    DROP FUNCTION dbo.FNBKItemCommMode
END
GO

CREATE FUNCTION FNBKItemCommMode 
(
    @Value INT
)
RETURNS VARCHAR(30)
BEGIN
    RETURN
    CASE @Value
    WHEN 1 THEN 1  --固定
    WHEN 2 THEN 2  --营业额
    WHEN 3 THEN 5  --扣成本
    WHEN 4 THEN 4  --业绩
    WHEN 41 THEN 6  --实收业绩
    WHEN 5 THEN 7  --标准价
    
    
    WHEN 7 THEN 333 --实收额_扣第三人提成
    WHEN 8 THEN 335 --实收额_扣第二三人提成
    WHEN 9 THEN 332 --实收额_扣第二人提成

    WHEN 90 THEN 433 --业绩_扣第三人提成
    WHEN 91 THEN 435 --业绩_扣第二三人提成
    WHEN 92 THEN 432 --业绩_扣第二三人提成

    WHEN 93 THEN 533 --实收额_扣第三人提成
    WHEN 94 THEN 535 --实收额_扣第二三人提成
    WHEN 95 THEN 532 --实收额_扣第二人提成
        
    ELSE 4 END
END
GO

     
--项目成本方式
IF object_id('dbo.FNBKItemCostMode') IS NOT NULL
BEGIN
    DROP FUNCTION dbo.FNBKItemCostMode
END
GO

CREATE FUNCTION FNBKItemCostMode 
(
    @Value INT
)
RETURNS VARCHAR(30)
BEGIN
    RETURN
    CASE @Value
    WHEN 1 THEN 2 --营业额比率
    WHEN 2 THEN 7 --原价
    WHEN 3 THEN 1 --固定
    WHEN 4 THEN 11 --按消耗产品的成本
    WHEN 7 THEN 3 --实
    ELSE 0 END
END
GO


--产品业绩方式
IF object_id('dbo.FNBKGoodsPerfMode') IS NOT NULL
BEGIN
    DROP FUNCTION dbo.FNBKGoodsPerfMode
END
GO

CREATE FUNCTION FNBKGoodsPerfMode 
(
    @Value INT
)
RETURNS VARCHAR(30)
BEGIN
    RETURN
    CASE @Value
    WHEN 1 THEN 1  --固定
    WHEN 2 THEN 2  --营业额
    WHEN 10 THEN 2  --    营业额再折扣
    WHEN 11 THEN 2  --    实际营业额再折扣        
    WHEN 20 THEN 7  --    标准价-进货成本
    WHEN 21 THEN 2  --    营业额-进货成本
    WHEN 22 THEN 7  --    标准价-销售成本
    WHEN 23 THEN 2  --    营业额-销售成本
    WHEN 5 THEN 7  --    标准价比率
    WHEN 9 THEN 3  --    实际营业额比率
    ELSE 2 END
END
GO
 
--产品提成方式
IF object_id('dbo.FNBKGoodsCommMode') IS NOT NULL
BEGIN
    DROP FUNCTION dbo.FNBKGoodsCommMode
END
GO

CREATE FUNCTION FNBKGoodsCommMode 
(
    @Value INT
)
RETURNS VARCHAR(30)
BEGIN
    RETURN
    CASE @Value
    WHEN 1 THEN 1  --固定
    WHEN 2 THEN 5  --营业额
    WHEN 3 THEN 5  --扣成本
    WHEN 4 THEN 5  --业绩
    WHEN 41 THEN 6  --实收业绩
    WHEN 5 THEN 7  --标准价
    ELSE 2 END
END
GO


--储值卡业绩方式
IF object_id('dbo.FNBKCardPerfMode') IS NOT NULL
BEGIN
    DROP FUNCTION dbo.FNBKCardPerfMode
END
GO

CREATE FUNCTION FNBKCardPerfMode 
(
    @Value INT
)
RETURNS VARCHAR(30)
BEGIN
    RETURN
    CASE @Value
    WHEN 1 THEN 1  --固定
    WHEN 2 THEN 3  --营业额
    WHEN 10 THEN 2  --    营业额再折扣
    WHEN 11 THEN 2  --    实际营业额再折扣    
    
    
    WHEN 5 THEN 7  --    标准价比率
    WHEN 9 THEN 3  --    实际营业额比率
    ELSE 2 END
END
GO

--储值卡提成方式
IF object_id('dbo.FNBKCardCommMode') IS NOT NULL
BEGIN
    DROP FUNCTION dbo.FNBKCardCommMode
END
GO

CREATE FUNCTION FNBKCardCommMode 
(
    @Value INT
)
RETURNS VARCHAR(30)
BEGIN
    RETURN
    CASE @Value
    WHEN 1 THEN 1  --固定
    WHEN 2 THEN 4  --营业额
    WHEN 3 THEN 4  --扣成本
    WHEN 4 THEN 4  --业绩
    WHEN 41 THEN 6  --实收业绩
    WHEN 5 THEN 7  --标准价
    ELSE 2 END
END
GO

 
 --会员卡状态
IF object_id('dbo.FNBkCardState') IS NOT NULL
BEGIN
    DROP FUNCTION dbo.FNBkCardState
END
GO

CREATE FUNCTION FNBkCardState 
(
    @Value INT
)
RETURNS VARCHAR(30)
BEGIN
    RETURN
    CASE @Value
    
    WHEN 1 THEN 0 --未销售
    WHEN 10 THEN 1 --会籍保留
    WHEN 2 THEN 0 --已销售
    WHEN 3 THEN 0 --已注册
    WHEN 4 THEN 1 --已开卡
    WHEN 5 THEN 1 --正常使用中
    WHEN 6 THEN 3 --已转卡
    WHEN 7 THEN 2 --已退卡
    WHEN 8 THEN 1 --已过期
    WHEN 9 THEN 16 --已挂失
    ELSE 0 END

END
GO


--交易历史类别
IF object_id('dbo.FNBKTransBizType') IS NOT NULL
BEGIN
    DROP FUNCTION dbo.FNBKTransBizType
END
GO

CREATE FUNCTION FNBKTransBizType 
(
    @Value varchar(20)
)
RETURNS VARCHAR(30)
BEGIN



    IF @Value = '1'
    BEGIN
        return 'DR.CardSale'
    END
    ELSE IF @Value = '2'
    BEGIN
        RETURN 'DR.CourseSale'
    END
    ELSE IF @Value = '7' OR @Value = '8'
    BEGIN
        RETURN 'DR.CoursePackSale'
    END
    ELSE IF @Value = '5'
    BEGIN
        RETURN 'DR.CardRchg'
    END
    ELSE IF @Value = '3'
    BEGIN
        RETURN 'DR.Item'
    END
    ELSE IF @Value = '4'
    BEGIN
        RETURN 'DR.Goods'
    END
    RETURN 'DR'
END
GO

--执行语句
IF object_id('dbo.PBKBuildSQL','P') IS NOT NULL
BEGIN
    DROP PROCEDURE dbo.PBKBuildSQL
END
GO
CREATE PROCEDURE PBKBuildSQL 
(
    @InSql VARCHAR(8000),
    @OutSql VARCHAR(8000) OUTPUT
)
AS
BEGIN
    DECLARE @FromServer VARCHAR(200),
            @FromDatabase VARCHAR(200),
            @ToServer VARCHAR(200),
            @ToDatabase VARCHAR(200)
            
    SELECT @FromServer = FFromServer,
           @FromDatabase = FFromDatabase,
           @ToServer = FToServer,
           @ToDatabase = FToDatabase
      FROM TImportConfig#
      
     
    IF ISNULL(@FromDatabase,'') = '' OR ISNULL(@FromDatabase,'') = ''
    BEGIN
        RAISERROR ('配置不正确' , 16, 1, 1, 1, 1)
        RETURN 
    END
    
    SELECT @InSql = REPLACE(@InSql,'  ',' ')
    SELECT @InSql = REPLACE(@InSql,'  ',' ')
    SELECT @InSql = REPLACE(@InSql,'  ',' ')

    DECLARE @From VARCHAR(200)
    SELECT @From = @FromDatabase + '.dbo'
    IF ISNULL(@FromServer,'') != ''
    BEGIN
        SELECT @From = @FromServer + '.' + @From
    END

    DECLARE @To VARCHAR(200)
    SELECT @To = @ToDatabase + '.dbo'
    IF ISNULL(@ToServer,'') != ''
    BEGIN
        SELECT @To = @ToServer + '.' + @To
    END

    SET @OutSql = REPLACE(@InSql,'{FDB}',@From);
    SET @OutSql = REPLACE(@OutSql,'{TDB}',@To);
END
GO


--执行语句
IF object_id('dbo.PExecImportSQL','P') IS NOT NULL
BEGIN
    DROP PROCEDURE dbo.PExecImportSQL
END
GO
CREATE PROCEDURE PExecImportSQL 
(
    @Sql VARCHAR(8000),
    @Sql2 VARCHAR(8000) = ''
)
AS
BEGIN
    

    EXEC PBKBuildSQL @Sql,@Sql OUTPUT
    
    IF @Sql2 != ''
    BEGIN
        EXEC PBKBuildSQL @Sql2,@Sql2 OUTPUT
    END
    
    --EXEC ('USE ' + @ToDatabase)
    EXEC (@Sql +@Sql2 )
END
GO


--初始化链接
IF object_id('dbo.PInitConnection','P') IS NOT NULL
BEGIN
    DROP PROCEDURE dbo.PInitConnection
END
GO

CREATE PROCEDURE PInitConnection 
(
    @FromServer VARCHAR(1024),
    @FromDatabase VARCHAR(200),
    @FromUser VARCHAR(1024),
    @FromPassword VARCHAR(1024),
  --  @ToServer VARCHAR(1024),
    @ToDatabase VARCHAR(200)--,
 --   @ToUser VARCHAR(1024),
  --  @ToPassword VARCHAR(1024)
)
AS
BEGIN
    IF ISNULL(@FromServer,'') != ''
    BEGIN
        if exists(select 1 from master..sysservers where srvname='fromServer') 
        begin 
            exec sp_dropserver 'fromServer','droplogins' 
        end
        exec sp_addlinkedserver 'fromServer','','SQLOLEDB',@FromServer 
        exec sp_addlinkedsrvlogin  'fromServer','false',null,@FromUser,@FromPassword
    END
    
  /*  IF ISNULL(@ToServer,'') != ''
    BEGIN
        if exists(select 1 from master..sysservers where srvname='toServer') 
        begin 
            exec sp_dropserver 'toServer','droplogins' 
        end
        exec sp_addlinkedserver 'toServer','','SQLOLEDB',@ToServer
        exec sp_addlinkedsrvlogin  'toServer','false',null,@ToUser,@ToPassword
    END*/
    
    /*IF @FromDatabase = @ToDatabase
    BEGIN
        RAISERROR ('数据库不能相同' , 16, 1, 1, 1, 1)
        RETURN  
    END*/
    
    DELETE TImportConfig#
    INSERT TImportConfig#(FFromServer,FFromDatabase,FToServer,FToDatabase)
    VALUES ('fromServer',@FromDatabase,'',@ToDatabase)

END
GO