sql-tools/Import/丝美/基本资料.sql

use jinan_simei


--Ա<EFBFBD><EFBFBD>

SELECT * FROM TImpEmployee


SELECT * FROM TImpEmployee WHERE FPosId IS NULl

ALTER TABLE TImpEmployee ADD FDeptId BIGINT
ALTER TABLE TImpEmployee ADD FPosId BIGINT
  

 
   
UPDATE A
   SET A.FPosId = B.FId
  FROM TImpEmployee AS A,TBasicType AS B
 WHERE A.<EFBFBD><EFBFBD><EFBFBD><EFBFBD> =  B.FName
   AND B.FKey = 'Position'
 
   
UPDATE A
   SET A.FDeptId = B.FId
  FROM TImpEmployee AS A,TBasicType AS B
 WHERE  1=1
   AND B.FKey = 'department'

     INSERT INTO TEmployee(FId,FNo,FName,FEnLname,FEnFname,FNickname,FDeptId,FPosId,FTechTypeId,FBriefCode,FGender,FStature,FWeight,FNationPlace,FNation,FEducation,FMarital,FAddress,FTelephone,FMobilePhone,FQQ,FEmail,FIdCard,FBirthdayType,FBirthday,FCardNo,FPW,FBank,FBankCardNo,FSSNo,FHCNo,FLinkMan,FLinkManTel,FLinkManAddr,FIsManager,FAllowBooking,FContractType,FState,FCreatorId,FCreateDate,FEntryDate,FExpiredDate,FLeaveDate, FPrice,FTags)
   SELECT dbo.FNNewId(), REPLACE(Ա<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>,'<EFBFBD><EFBFBD>',''),<EFBFBD><EFBFBD><EFBFBD><EFBFBD>,FEnLname = '',FEnFname = '',FNickname = <EFBFBD><EFBFBD><EFBFBD><EFBFBD>,FDeptId = 0,
   FPosId,FTechTypeId = 0,FBriefCode = DBO.FNGetFirstPinYin(<EFBFBD><EFBFBD><EFBFBD><EFBFBD>),FGender = CASE WHEN <EFBFBD>Ա<EFBFBD> = '<EFBFBD><EFBFBD>' THEN 1 ELSE 0 END,
   FStature = 0,FWeight = 0,FNationPlace = 0,FNation = 0,FEducation = '',FMarital = '',FAddress = '',
   FTelephone = '',FMobilePhone = '',FQQ = '',FEmail = '',FIdCard = '',FBirthdayType = 'L',FBirthday = NULL,
   FCardNo = '', FPW ='',FBank = '',FBankCardNo = '',FSSNo = '',FHCNo ='',FLinkMan = '',
   FLinkManTel = '',FLinkManAddr = '',  FIsManager = 0,FAllowBooking = 1,FContractType = '',
   FState = 1,FCreatorId = 0,FCreateDate = GETDATE(),FEntryDate = null,FExpiredDate = null,FLeaveDate = null,
 FPrice = 0,FTags = ''
   FROM TImpEmployee
   WHERE Ա<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD> != ''
   ORDER BY Ա<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>
   
   UPDATE A
   SET A.FDeptId = B.FId
  FROM TEmployee AS A,TBasicType AS B
 WHERE  1=1
   AND B.FKey = 'department'
   
   
   
   --
   drop table TImpItem
   SELECT * FROM TImpItem
   
   DELETE TImpItem
   
   
   ALTER TABLE TImpItem ADD FPrice DECIMAL(18,6)
   ALTER TABLE TImpItem ADD FTypeId BIGINT
   ALTER TABLE TImpItem ADD FId BIGINT
   ALTER TABLE TImpItem ADD FNo VARCHAR(100) NOT NULL DEFAULT ''
   ALTER TABLE TImpItem ADD FName VARCHAR(100) NOT NULL DEFAULT ''
   ALTER TABLE TImpItem ADD FTypeName VARCHAR(100) NOT NULL DEFAULT ''
   
    UPDATE TImpItem SET FId = dbo.FNNewid()
    UPDATE TImpItem SET FPrice = <EFBFBD><EFBFBD><EFBFBD><EFBFBD>
    
   UPDATE TImpItem SET FName = LEFT(<EFBFBD><EFBFBD><EFBFBD><EFBFBD>,CHARINDEX('<EFBFBD><EFBFBD>',<EFBFBD><EFBFBD><EFBFBD><EFBFBD>)-1) 
   UPDATE TImpItem SET FTypeName = REPLACE( SUBSTRING(<EFBFBD><EFBFBD><EFBFBD><EFBFBD>,CHARINDEX('<EFBFBD><EFBFBD><EFBFBD><EFBFBD>',<EFBFBD><EFBFBD><EFBFBD><EFBFBD>) + 2,100) ,'<EFBFBD><EFBFBD>','')
   
    UPDATE TImpItem
       SET FNo = LEFT(<EFBFBD><EFBFBD><EFBFBD><EFBFBD>,CHARINDEX('<EFBFBD><EFBFBD>',<EFBFBD><EFBFBD><EFBFBD><EFBFBD>)-1)
       
    UPDATE A
       SET A.FTypeId = B.FId
      FROM TBasicType AS B,TImpItem AS A
     WHERE A.FTypeName = B.FName
       
   INSERT TItem(FId,FNo,FNoOfPad,FName,FAbbr,FTypeId,FBriefCode,FDescription,FPriceMode,FIsService,FStatTimes,FCustom1,FCustom2,FCustom3,FEnabled,FPrice,FVipPrice,FTime,FDctId,FDisplayTypeId,FPointMode,FPointValue,FDeptId,FAllowBooking)
  SELECT FId,FNo,FNo,FName,FName,FTypeId,FBriefCode = dbo.FNGetFirstPinYin(FName),
  FDescription = '',FPriceMode = 0,FIsService = 1,FStatTimes = 1,FCustom1 = '',FCustom2 = '',
  FCustom3 = '',FEnabled = 1,FPrice ,FVipPrice = 0 ,FTime = 0,FDctId = 0 ,FDisplayTypeId = 0,
  FPointMode = 0,FPointValue = 0,FDeptId = 0,FAllowBooking = 1
    FROM TImpItem 
    
     
        INSERT TItemSub(FId,FCompId,FItemId,FPrice,FVipPrice,FTime,FDctId,FPointMode,FPointValue,FDeptId,FAllowBooking,FEnabled)
    SELECT dbo.FNNewId(),C.FId,I.FId,I.FPrice,I.FVipPrice,I.FTime,I.FDctId,I.FPointMode,I.FPointValue,I.FDeptId,I.FAllowBooking,I.FEnabled
      FROM TItem AS I,TCompany AS C
     WHERE I.FId != 0
     AND C.FId != 0
     AND NOT EXISTS(SELECT TOP 1 1 FROM TItemSub AS B WHERE B.FCompId = C.FId AND B.FItemId = I.FId)
     
     
  
  SELECT FTypeName FROM TImpItem
  GROUP BY FTypeName
  
    INSERT TBasicType(FId,FKey,FNo,FName,FParentId,FDescription,FCustom,FOrder,FAllowEdit,FEnabled)
    SELECT dbo.FNNewId(),'ItemType',FTypeName,FTypeName,0,'','',0,-1,1
    FROM 
TImpItem
  GROUP BY  FTypeName