sql-tools/Import/郓城首选/导首选.sql

SELECT * FROM info_item where item_id in(select item_id from mem_item where sum_t > pay_t  ) and FCourseId = 0
SELECT * FROM mem_item 
SELECT * FROM card_level 

alter table info_item add FId BIGINT NOT NULL DEFAULT 0
alter table info_item add FIsNew BIT NOT NULL DEFAULT 0
alter table info_item add FCourseId BIGINT NOT NULL DEFAULT 0
alter table info_item alter column FCourseId BIGINT 
alter table mem_item add FCourseId BIGINT 
alter table mem_item add FItemId BIGINT 
alter table mem_item add FIsNewItem BIT 

UPDATE info_item SET FId = 0
UPDATE info_item SET FId = dbo.FNGetNewId()  WHERE FId = 0 

UPDATE A
   SET A.FCourseId = B.FCourseId,
       A.FItemId = B.FId
  FROM info_item AS B,mem_item AS A
 WHERE A.item_id = B.Item_id
 
 update mem_item set FIsNewItem = 1 WHERE FCourseId IS NULL 
 


UPDATE a
   SET A.FCourseId = B.FId 
  FROM info_item AS A,TCourse AS B
 WHERE A.FId = B.FItemId
 AND B.FSequence = 1
 
 update mem_item set FCourseId = 0,FItemId = 0, FIsNewItem = 0
 
 UPDATE a
    set a.FItemId = b.FId,
        a.FCourseId = b.FCourseId
  from mem_item as a,info_item as b
 where a.item_id = b.item_id
  and sum_t > pay_t
  
  update mem_item set FIsNewItem = 1 WHERE  sum_t > pay_t AND FCourseId = 0
  
    select item_id,dbo.FNGetNewId() AS FItemId,dbo.FNGetNewId() AS FCourseId
    into #newItem
from mem_item 
 where  sum_t > pay_t
   and FIsNewItem = 1
   group by item_id
  
  update mem_item
  set mem_item.FItemId = b.FItemId,
      mem_item.FCourseId = b.FCourseId
      FROM mem_item,#newItem AS B
   where mem_item.item_id = b.item_id
    and  sum_t > pay_t
   and FIsNewItem = 1
   
   

 update mem_item
 set FCourseId = dbo.FNGetNewId(),
     FItemId = dbo.FNGetNewid(),
     FIsNewItem = 1
  where  sum_t > pay_t
   and FCourseId = 0  
 
 select * from TCourse
 
 select * from mem_item where sum_t > pay_t
 and FIsNewItem = 1
 

ALTER table card_level add FId BIGINT NOT NULL DEFAULT 0 

update a set a.FId = B.FId 
FROM info_item as a,TItem AS B
where A.item_name = B.FName 
and B.FName = '<EFBFBD><EFBFBD><EFBFBD><EFBFBD>'

update a set a.FId = B.FId 
FROM card_level as a,TMemberCardType AS B
where cast(A.level_id as varchar(111)) = B.FNo 

INSERT TItem(FId,FNo,FNoOfPad,FName,FAbbr,FTypeId,FBriefCode,FDescription,FPriceMode,FIsService,FReturnInterval,FCustom1,FCustom2,FCustom3,FEnabled,FPrice,FVipPrice,FTime,FDctId,FPointMode,FPointValue,FDeptId,FAllowBooking)
select FId,'L' + cast(item_id as varchar(20)),'L' + cast(item_id as varchar(20)),
isnull(item_name,'<EFBFBD><EFBFBD>ϵͳ<EFBFBD><EFBFBD>û<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>'),isnull(item_name,'<EFBFBD><EFBFBD>ϵͳ<EFBFBD><EFBFBD>û<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>'),0,'','<EFBFBD><EFBFBD><EFBFBD>ݼƴ<EFBFBD>' ,0,1,0,'','','',1,price,price,0,0,0,0,0,0
 from info_item where item_id in(select item_id from mem_item where sum_t > pay_t  )
 and FIsNew = 1


delete TItem WHERE FNo = FName AND FTypeId = 0 

alter table TItem ADD FCourseId BIGINT NOT NULL DEFAULT 0

INSERT TItem(FId,FCourseId,FNo,FNoOfPad,FName,FAbbr,FTypeId,FBriefCode,FDescription,FPriceMode,FIsService,FReturnInterval,FCustom1,FCustom2,FCustom3,FEnabled,FPrice,FVipPrice,FTime,FDctId,FPointMode,FPointValue,FDeptId,FAllowBooking)
select distinct FItemId,FCourseId, 'L' + cast(item_id as varchar(20)),'L' + cast(item_id as varchar(20)),
'L' + cast(item_id as varchar(20)),'L' + cast(item_id as varchar(20)),0,'','<EFBFBD><EFBFBD><EFBFBD>ݼƴ<EFBFBD>' ,0,1,0,'','','',1,0,0,0,0,0,0,0,0
 from mem_item where item_id in(select item_id from mem_item where  FIsNewItem = 1  )
 and FIsNewItem = 1
 group by FItemId,item_id,FCourseId
 
 SELECT * FROM TCourse
INSERT INTO TCourse(FId,FNo,FName,FBriefCode,FItemId,FPackId,FSequence,FTypeId,FTimes,FFreeTimes,FDurable,FValidityPeriodUnit,FValidityPeriod,FCreateDate,FExpiredDate,FDescription,FEnabled,FPrice,FAmount,FCalcMode)  
SELECT FCourseId,FNo + 'L1',FName + 'L1','',FId,0,1,5719441825294329890,0,0,0,0,0,getdate(),null,'',1,0,0,1
FROM TItem
WHERE FTypeId = 0 AND FNo = FName 
 
 select * from mem_item where item_id = '1003'


select item_id from mem_item where sum_t > pay_t and FIsNewItem = 1
and 'L' + cast(item_id as varchar(20)) IN (SELECT FNo FROM TItem)

alter table mem_info Add FMemId 

SELECT card_id,count(1) FROM mem_info group by card_id
order by count(1) desc

SELECT * FROM mem_info WHERE card_id = '10001145'

SELECT * FROM TMember
       delete TMember
       
       
       begin tran                                

INSERT INTO TMember(
    FId,
    FNo,
    FName,
    FBriefCode,
    FTypeId,
    FCompId,
    FCreateDate,
    FGender,
    FBirthdayType,
    FBirthday,FTelephone,FMobilePhone,FQQ,FWeChat,FIdCard,FEmail,FAddress,FProId,FCPW,FQPW,
    FChargeEmpId,FPoint,FRecvConsSMS,FRecvBulkSMS,FMemo,FCustom1,FCustom2,FCustom3,FCustom4,FCustom5)
SELECT  replace(mem_id ,' ','') AS FId,
       replace(card_id ,' ','') AS FNo,
       replace(mem_name,' ','') AS FName,
       '' AS FBriefCode,
       0 AS FTypeId,
       1 AS FCompId,
       open_date AS FCreateDate,
       case cast(item3 as varchar(1)) when 2 then 0 else 1 end AS FGender,
       'G' AS FBirthdayType,
       NULL AS FBirthday,
       '' AS FTelephone,
       isnull( replace(tel_no ,' ',''),'') as FMobilePhone,
       '' AS FQQ,
       '' AS FWeChat,
       '' AS FIdCard,
       '' AS FEmail,
       '' AS FAddress,
       0 AS FProId,
       '' AS FCPW,
       '' AS FQPW,
       0 AS FChargeEmpId,
       0 AS FPoint,
       1 AS FRecvConsSMS,
       1 AS FRecvBulkSMS,
       '' AS FMemo,
       '' AS FCustom1,'' AS FCustom2,'' AS FCustom3,'' AS FCustom4,'' AS FCustom5
       from mem_info
       WHERE card_id IS NOT NULL
       AND mem_name not like '%<EFBFBD><EFBFBD><EFBFBD><EFBFBD>%'
       AND card_id not in('3001988','10001145','00142')
       
       rollback tran
       
       
       
       
       
    INSERT INTO TMemberCard(
        FId,
        FNo,
        FFaceNo,
        FTypeId,
        FValueType,
        FCompId,
        FMemId,
        FState,
        FCreateDate,
        FExpiredDate,
        FTotalTimes,
        FUsedTimes,
        FSaleCompId,
        FSaleBillId,
        FMemo)
    SELECT 
        replace(mem_id,' ','') AS FId,
        replace(card_id,' ','') AS FNo,
        replace(card_id,' ','') AS FFaceNo,
        (SELECT FId FROM TMemberCardType WHERE FNo = CAST(level_id as varchar(20))) AS FTypeId,    
        (SELECT FValueType FROM TMemberCardType WHERE FNo = CAST(level_id as varchar(20))) AS FValueType,    
        00 AS FCompId,
        mem_id AS FMemId,
        1 AS FState,
        open_date AS FCreateDate,
        dateadd(y,100,open_date) AS FExpiredDate,
        0 AS FTotalTimes,
        0 AS FUsedTimes,
        0 AS FSaleCompId,
        0 AS FSaleBillId,
        '' AS FMemo
  FROM mem_info 
         WHERE card_id IS NOT NULL
       AND mem_name not like '%<EFBFBD><EFBFBD><EFBFBD><EFBFBD>%'
       AND card_id not in('3001988','10001145','00142')
  
  rollback tran
  
       INSERT TMemberCardAccount(FId,FCardId,FAcctId,FDeposit,FBalance,FArrear,FCreateDate,FExpiredDate,FMemo)
       SELECT dbo.FNGetNewId(),replace(mem_id,' ','') AS FCardId,3,balance,balance,0,open_date,NULL,''
  FROM mem_info 
         WHERE card_id IS NOT NULL
       AND mem_name not like '%<EFBFBD><EFBFBD><EFBFBD><EFBFBD>%'
       AND card_id not in('3001988','10001145','00142')
       
       
    INSERT TMemberCourse(FId,FMemId,FCourseId,FMemPackId,FItemId,FCompId,FPurTimes,FPurAmount,FFreeTimes,FFreeAmount,FUsedPurTimes,
     FPurchaseDate,FExpiredDate,FBizType,FTransId,FArrear,FDurable,FMemo,FCalcMode,FAdjustPrice)
    SELECT dbo.FNGetNewId() AS FId,
        replace(mem_id,' ','') as FMemId,
        FCourseId AS FCourseId,
        0 AS FMemPackId,
        FItemId ,
        1,
        sum_t AS FPurTimes,
        0 AS FPurAmount,
        0 AS FFreeTimes,
        0 AS FFreeAmount,
        pay_t AS FUsedPurTimes,
        getdate() AS FPurchaseDate,
        NULL AS FExpiredDate,
        'DR.Import',
        0,
        0,
        0,
       isnull( replace(mem,' ',''),'') AS FMemo,
        1 AS FCalcMode,
        0 AS FAdjustPrice
        from mem_item
        where mem_id in(select FId FROM TMember)
        
        
    INSERT TMemberCardAcctHist(FId,FMemId,FCardId,FAcctId,FTime,FCardTypeId,FCompId,FBillType,FBizType,FTransId,FTransNo,FInAmount,FOutAmount,FBalance,FArrear,FRecordTime,FMemo)
    select dbo.FNGetNewId(),C.FMemId,A.FCardId,A.FAcctId,getdate(),C.FTypeId,C.FCompId,'DR','Import',0,'',A.FBalance,0,A.FBalance,0,getdate(),''
    from TMemberCardAccount AS A,TMemberCard AS C
    where C.FId = A.FCardId
    
    
    UPDATE TMemberCard SET FCompID = 1
    update TMemberCardAcctHist SET FCompId = 1
    UPDATE TMember SET FCompID = 1
    
    

       select * from info_item where item_Id = '6122'
       
       select * from mem_info where mem_id = 1002000738
       
       delete mem_item  WHERE mem_id not in(select mem_id from mem_info)
       SELECT * FROM mem_item WHERE mem_id not in(select mem_id from mem_info)
       select * from mem_info where mem_id = '1001000037'
       
       select * from info_item WHERE item_id = '5014'
       
       update TMemberCard SET FNo = REPLACE(FNo,' ','' )
       update TMemberCard SET FFaceNo = REPLACE(FFaceNo,' ','' )
       update TMember SET FNo = REPLACE(FNo,' ','' )
       
       SELECT * FROM TMemberCardAccount
       
       Error during serialization or deserialization using the JSON JavaScriptSerializer. The length of the string exceeds the value set on the maxJsonLength property.



SELECT * FROM TUser 
       
       
       SELECT * FROM TMemberSaleBill WHERE FBillNo = '33071'
       SELECT * FROM TMemberSaleCard WHERE FBillId = 4621390305656325003

UPDATE TMemberSaleCard SET FCardFaceNo = FCardNo  WHERE FBillId = 4621390305656325003
SELECT * FROM TMemberCard 
       
       
       UPDATE TItem SET FTypeId =(SELECT FId FROM TBasicType WHERE FKey = 'ItemType' AND FNo ='LS')
       
       SELECT 
       
       select * from TBasicType WHERE FKey = 'ItemType' 
       UPDATE TItem SET FTypeId = 5640554791607784066 WHERE FNo LIKE '0%'
       UPDATE TItem SET FTypeId = 5643920378432925653 WHERE FNo LIKE 'L%' AND FNo = FName
       
      UPDATE TItem SET FTypeId = 4705757715392983268 WHERE FNo = '221241'
      
      select * from mem_item where item_id = '221241'
      
      update TMemberCard SET FExpiredDate =  
        dateadd(yy,100,FCreateDate) 
        
        select FCreateDate,dateadd(yy,100,FCreateDate)  from TMemberCard
        
        
        
SELECT FMachineName FROM TLoginSession WHERE FMachineName != 'SH12182A3A88HW96'
group by FMachineName


select * from TaiYuan_XiMenXuan ..TUser 


   INSERT TItemSub(FId,FCompId,FItemId,FPrice,FVipPrice,FTime,FDctId,FPointMode,FPointValue,FDeptId,FAllowBooking,FEnabled)
    SELECT dbo.FNGetNewId(),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)
     AND C.FId != 1
      
      
      
      
      select * from TMemberSaleBill WHERE FBillNo = '0021507250002'
      
SELECT * FROM TMemberSaleCard


UPDATE A
   SET A.FDeptId = B.FDeptId
  FROM TMemberSaleCard AS A,TMemberCardType AS B
  WHERE A.FCardTypeId = B.FId 
  
  
  INSERT TMemberPointHist(FId,FMemId,FTime,FCardId,FCompId,FBillType,FBizType,FTransId,FTransNo,FInAmount,FOutAmount,FBalance,FMemo,FRecordTime)
  SELECT dbo.FNGetNewId(),A.FId,getdate(),0,1,'DR','Import',0,'',FPoint,0,FPoint,'',getdate()
  FROM TMember  AS A
  
  SELECT * FROM TMemberPointHist