SELECT * FROM TGoods


SELECT * FROM TImpGoods 


 INSERT INTO TGoods(FId,FNo,FName,FNoOfPad,FTypeId,FBriefCode,FUnitId,FSalePrice,FPurCostPrice,FSaleCostPrice,FConsCostPrice,FValuationMode,FBarCode,FEnabled,FSpecification,FPurUnitId,FConsUnitId,FSupplierId,FStoreId,FConsStoreId,FSaleStoreId,FMaxStock,FMinStock,FPeriodEnabled,FShelfLife,FDctId,FPointMode,FPointValue,FDeptId,FIsSale,FUnitLock,FDescription,FCustom1,FCustom2,FCustom3)
 SELECT dbo.FNNewId(),FNo,FName,FNo,FTypeId,dbo.FNGetFirstPinYin(FName),
 20006,FSalePrice,FPurCostPrice = 0,FSaleCostPrice = 0,FConsCostPrice = 0,
 FValuationMode = 0,FBarCode = 0,FEnabled = 1,FSpecification = '',
 FPurUnitId = 0,FConsUnitId = 0,FSupplierId = 0,FStoreId = 0,FConsStoreId= 0,FSaleStoreId= 0,
 FMaxStock= 0,FMinStock= 0,FPeriodEnabled= 0,FShelfLife= 0,FDctId= 0,FPointMode= 0,FPointValue= 0,
 FDeptId= 0,FIsSale,FUnitLock= 0,FDescription= '',FCustom1 = '',FCustom2 = '',FCustom3 = ''
 FROM TImpGoods
 
  INSERT INTO TGoodsMultiUnit(FId,FGoodsId,FUnitType,FUnitId,FSequence,FConvDir,FConvRate,FSalePrice,FPurCostPrice,FSaleCostPrice,FConsCostPrice,FBarCode,FUnitLock)
  SELECT dbo.FNNewId(),FId,1,FUnitId,1,1,1,FSalePrice,FPurCostPrice,FSaleCostPrice,FConsCostPrice,FBarCode,FUnitLock
   FROM TGoods 
   DELETE TGoodsMultiUnit
   
 
 SELECT * FROM TGoodsMultiUnit
 
 SELECT * FROM TBasicType WHERE FKey = 'GoodsUnit'
 
 ALTER TABLE TImpGoods ADD FTypeId BIGINT
 
 UPDATE A
    SET A.FTypeId = B.FId
   FROM TImpGoods AS A,TBasicType AS B
  WHERE REPLACE(A.FTypeName,' ','') LIKE '%' + B.FName + '%'
  AND  FKey = 'GoodsType'
  
  UPDATE TImpGoods SET FTypeName = '其他系列' where FTypeName = '其它系列'
  
  UPDATE TImpGoods SET FIsSale = 0 WHERE FIsSale IS NULL 
    UPDATE TImpGoods SET FIsSale = 1 WHERE FIsSale = '√'
    
    
  
   SELECT * FROM TImpItem
   
   delete TImpItem WHERE FNo IS NULL OR FNo = '项目数：'
   
   ALTER TABLE TImpItem ADD FTypeId BIGINT
   
   UPDATE A 
      SET A.FTypeId = B.FId
    FROM TImpItem AS A,TBasicType AS B
   WHERE A.专业类型= B.FName
  AND B.FKey = 'ItemType'
  


   ALTER TABLE TImpItem ADD FId BIGINT 
   ALTER TABLE TImpItem ADD FNo VARCHAR(30) 
   ALTER TABLE TImpItem ADD FName VARCHAR(30) 
   ALTER TABLE TImpItem ADD FTypeId BIGINT
   ALTER TABLE TImpItem ADD FPrice DECIMAL(18,6) 
   
   UPDATE TImpItem SET FName = XMMC00
   UPDATE TImpItem SET FId = XMBM00
   UPDATE TImpItem SET FNo = XMNBBM
   UPDATE TImpItem SET FTypeId = 0
   UPDATE TImpItem SET FPrice = XMJG00

  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 dbo.FNNewId(),FNo,FNo,FName,FName,FTypeId,FBriefCode = dbo.FNGetFirstPinYin(FName),
  FDescription = '',FPriceMode = 0,FIsService = 1,FStatTimes = 1,FCustom1 = '',FCustom2 = '',
  FCustom3 = '',FEnabled = 1,FPrice ,FVipPrice = Fprice ,FTime = 0,FDctId = 0 ,FDisplayTypeId = 0,
  FPointMode = 0,FPointValue = 0,FDeptId = 0,FAllowBooking = 1
  
  FROM TImpItem


  
  SELECT * FROM TImpEmployee
  
  ALTER TABLE TImpEmployee ADD FNo VARCHAR(1000)
  ALTER TABLE TImpEmployee ADD FName VARCHAR(1000)
  ALTER TABLE TImpEmployee ADD FPosId BIGINT
  ALTER TABLE TImpEmployee ADD FDeptId BIGINT
  ALTER TABLE TImpEmployee ADD FId BIGINT
  ALTER TABLE TImpEmployee ADD FGender INT
  ALTER TABLE TIMPEmployee ADD FBirthday DATETIME
  ALTER TABLE TIMPEmployee ADD FCreateDate DATETIME
  ALTER TABLE TIMPEmployee ADD FAddress VARCHAR(1000)
  ALTER TABLE TIMPEmployee ADD FSerialNo VARCHAR(1000)
  
  
  
  UPDATE TImpEmployee SET FNo = 员工序号   
  UPDATE TImpEmployee SET FName = 姓名
  UPDATE TImpEmployee SET FIdCard = 身份证号
  UPDATE TImpEmployee SET FAddress = 户籍地址
  UPDATE TImpEmployee SET FGender = CASE WHEN 性别 = '男' then 1 ELSE '0' END 
  UPDATE TImpEmployee SET FCreateDate = 入职时间
  UPDATE TImpEmployee SET FSerialNo = 员工编号



UPDATE TImpEmployee SET FId = dbo.FNNewId() WHERE FId IS NULL

  SELECT * FROM TIMPEmployee WHERE FPosId IS NULL
  SELECT * FROM TIMPEmployee WHERE FDeptId IS NULL
  
  SELECT * FROM TBasicType WHERE FName = '拓专员'
  
 -- UPDATE TImpEmployee SET FBirthday = RYCS00 WHERE FBirthday IS NULL
  
  UPDATE A
     SET A.FPosId = B.FId
    FROM TBasicType AS B,TImpEmployee AS A
   WHERE (A.职位 = B.FName OR A.职位 = B.FName)
    AND B.FKey = 'Position'
    
  UPDATE A
     SET A.FPosId = B.FId
    FROM TBasicType AS B,TImpEmployee AS A
   WHERE (A.职位 = '顾问' AND '美容顾问' = B.FName)
    AND B.FKey = 'Position'

  UPDATE A
     SET A.FPosId = B.FId
    FROM TBasicType AS B,TImpEmployee AS A
   WHERE (A.职位 = '拓专员' AND '拓客员' = B.FName)
    AND B.FKey = 'Position'


  UPDATE A
     SET A.FPosId = B.FId
    FROM TBasicType AS B,TImpEmployee AS A
   WHERE (A.职位 = '店长' AND '美容店长' = B.FName)
    AND B.FKey = 'Position'
    

  UPDATE A
     SET A.FDeptId = B.FId
    FROM TBasicType AS B,TImpEmployee AS A
   WHERE (A.所属部门 = B.FName OR A.所属部门 + '部' = B.FName)
    AND B.FKey = 'Department'

SELECT * FROM TEMployeeSub



UPDATE A
   SET A.FCompId = C.FId
  FROM TCompany AS C,TImpEmployee AS A
 WHERE A.RYSZBM = C.FZDName
 
 
 SELECT * FROM TCompany WHERE FName LIKE '%阳光%'
 SELECT * FROM TImpEmployee 
    
     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,FCardPW,FPW,FBank,FBankCardNo,FSSNo,FHCNo,FLinkMan,FLinkManTel,FLinkManAddr,FIntroducer,FAllowSign,FAllowBooking,FContractType,FState,FCreatorId,FCreateDate,FEntryDate,FExpiredDate,FLeaveDate,FDescription,FCustom1,FCustom2,FCustom3,FPrice,FTags)
   SELECT dbo.FNNewId(),FNo,FName,FEnLname = '',FEnFname = '',FNickname = FName,FDeptId = 0,
   FPosId,FTechTypeId = 0,FBriefCode = DBO.FNGetFirstPinYin(FName),FGender = 0,
   FStature = 0,FWeight = 0,FNationPlace = 0,FNation = 0,FEducation = '',FMarital = '',FAddress = '',
   FTelephone = '',FMobilePhone = '',FQQ = '',FEmail = '',FIdCard = '',FBirthdayType = 'L',FBirthday = NULL,
   FCardNo = '',FCardPW = '',FPW ='',FBank = '',FBankCardNo = '',FSSNo = '',FHCNo ='',FLinkMan = '',
   FLinkManTel = '',FLinkManAddr = '',FIntroducer = '' ,FAllowSign = 0,FAllowBooking = 1,FContractType = '',
   FState = 1,FCreatorId = 0,FCreateDate = GETDATE(),FEntryDate = null,FExpiredDate = null,FLeaveDate = null,
   FDescription = '',FCustom1 = '',FCustom2 = '',FCustom3 ='',FPrice = 0,FTags = ''
   FROM TImpEmployee
   
   WHERE FNo NOT IN(SELECT FNo FROM TEmployee)
   
   UPDATE A SET A.FId = B.FId
   FROM TImpEmployee AS A,TEmployee AS B
   WHERE A.FNo = b.fnO 
   
   SELECT * FROM TEmployeeSub
   
   INSERT TEmployeeSub(FEmpId,FCompId,FSerialNo)
   SELECT A.FId,A.FCompId,A.FNo 
     FROM TImpEmployee AS A
    WHERE FCompId = 4907116693846607985
    
    WHERE FId NOT IN(SELECT FEmpId FROM TEmployeeSub)
    
   AND FCompNAME is not null
   
   SELECT * FROM TImpEmployee WHERE FCompId IS NULL
   
   SELECT * FROM TImpCardType
   
   
   
   UPDATE TImpCardType SET FAllowRchg = 0 WHERE FAllowRchg IS NULL
   UPDATE TImpCardType SET FAllowRchg = 1 WHERE FAllowRchg = '√'
 
      UPDATE TImpCardType SET FAllowSale = 0 WHERE FAllowSale IS NULL
   UPDATE TImpCardType SET FAllowSale = 1 WHERE FAllowSale = '√'
   
    INSERT INTO TMemberCardType(FId,FNo,FName,FValueType,FMaterial,FPrice,FCardFeeAmt,FAcctId,FDeposit,FAcctId2,FDeposit2,FAcctId3,FDeposit3,FTimes,FAlertBalance,FMinRchgAmt,FSalePointMode,FSalePointValue,FRchgPointMode,FRchgPointValue,FPaySmsFee,FTimeCard,FValidityPeriod,FValidityPeriodUnit,FExpiredDate,FDefaultMemo,FCreateTime,FAllowSale,FAllowRchg,FDeptId,FEnabled,FOrder)
	SELECT FId = dbo.FNNewId(),FNo,FName,1,FMaterial = 2,FPrice,FCardFeeAmt = 0,FAcctId = 3,FDeposit = FPrice,FAcctId2 = 0,FDeposit2 = 0 ,FAcctId3 = 0,FDeposit3 = 0,
	FTimes = 0,FAlertBalance = 0,FMinRchgAmt = 0,FSalePointMode = 0,FSalePointValue = 0 ,FRchgPointMode = 0,FRchgPointValue = 0,FPaySmsFee = 0,FTimeCard = 0,FValidityPeriod = 100,FValidityPeriodUnit = 1,
	FExpiredDate = NULL,FDefaultMemo = '',FCreateTime = GETDATE(),FAllowSale,FAllowRchg,FDeptId = 0,FEnabled =1,FOrder = 1
	FROM TImpCardType
	where FNO IS NOT NULL