use jinan_simei


--员工

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.级别 =  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(员工编号,'号',''),姓名,FEnLname = '',FEnFname = '',FNickname = 姓名,FDeptId = 0,
   FPosId,FTechTypeId = 0,FBriefCode = DBO.FNGetFirstPinYin(姓名),FGender = CASE WHEN 性别 = '男' 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 员工编号 != ''
   ORDER BY 员工编号
   
   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 = 定价
    
   UPDATE TImpItem SET FName = LEFT(名称,CHARINDEX('【',名称)-1) 
   UPDATE TImpItem SET FTypeName = REPLACE( SUBSTRING(名称,CHARINDEX('】【',名称) + 2,100) ,'】','')
   
    UPDATE TImpItem
       SET FNo = LEFT(编号,CHARINDEX('改',编号)-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