You can not select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
178 lines
13 KiB
178 lines
13 KiB
select*from tcompany
|
|
|
|
UPDATE TCompany SET FDeleted = 1,FNo = FNo + 'ÒÑɾ' WHERE FNo = '001'
|
|
|
|
|
|
select * from TItem WHERE FId IN(SELECT FItemId FROM TItemSub WHERE FCompId = 8457115557031931866)
|
|
ORDER BY FNo
|
|
|
|
DELETE TItemSub WHERE FItemId IN(
|
|
SELECT FId FROM TItem WHERE FDeleted = 1)
|
|
|
|
DELETE TItem WHERE FDeleted = 1 )
|
|
|
|
SELECT * FROM TGoods
|
|
WHERE FId IN(SELECT FGoodsid FROM TConsumeGoods)
|
|
|
|
EXEC PImportTable 'TMemberCardType','JiNan_ShouXuan.dbo.TMemberCardType',' AND FId IN(SELECT FCardTypeId FROM TMemberCardTypeSub WHERE FCompId = 8457115557031931866)'
|
|
EXEC PImportTable 'TMemberCardTypeSub','JiNan_ShouXuan.dbo.TMemberCardTypeSub',' AND (FCompId = 8457115557031931866)'
|
|
EXEC PImportTable 'TItem','JiNan_ShouXuan.dbo.TItem',' AND FId IN(SELECT FItemId FROM TItemSub WHERE FCompId = 8457115557031931866)'
|
|
EXEC PImportTable 'TItemSub','JiNan_ShouXuan.dbo.TItemSub',' AND FCompId = 8457115557031931866'
|
|
EXEC PImportTable 'TCourse','JiNan_ShouXuan.dbo.TCourse',' AND FItemId IN(SELECT FItemId FROM TItemSub WHERE FCompId = 8457115557031931866)'
|
|
EXEC PImportTable 'TCompany','JiNan_ShouXuan.dbo.TCompany',' AND FId = 8457115557031931866'
|
|
EXEC PImportTable 'TBasicType','JiNan_ShouXuan.dbo.TBasicType',' AND FKey = ''ItemType'''
|
|
EXEC PImportTable 'TBasicType','JiNan_ShouXuan.dbo.TBasicType',' AND FKey = ''GoodsType'''
|
|
EXEC PImportTable 'TBasicType','JiNan_ShouXuan.dbo.TBasicType',' AND FKey = ''Position'''
|
|
EXEC PImportTable 'TBasicType','JiNan_ShouXuan.dbo.TBasicType',' AND FKey LIKE ''Dep%'''
|
|
EXEC PImportTable 'TBasicType','JiNan_ShouXuan.dbo.TBasicType',' AND FKey LIKE ''GoodsDiscount%'''
|
|
EXEC PImportTable 'TBasicType','JiNan_ShouXuan.dbo.TBasicType',' AND FKey LIKE ''GoodsUnit%'''
|
|
EXEC PImportTable 'TBasicTypeSub','JiNan_ShouXuan.dbo.TBasicTypeSub',
|
|
' AND FBasicTypeId IN(SELECT FId FROM JiNan_ShouXuan.dbo.TBasicType) AND NOT EXISTS(SELECT 1 FROM JiNan_ShouXuan.dbo.TBasicTypeSub AS A WHERE A.FCompId = TBasicTypeSub.FCompId AND A.FBasicTypeId = TBasicTypeSub.FBasicTypeId AND A.FCompId = 8457115557031931866) AND FCompId = 8457115557031931866'
|
|
|
|
|
|
|
|
EXEC PImportTable 'TGoods','JiNan_ShouXuan.dbo.TGoods',' AND FId IN(SELECT FGoodsId FROM TGoodsSub WHERE FCompId = 8457115557031931866)'
|
|
EXEC PImportTable 'TGoodsSub','JiNan_ShouXuan.dbo.TGoodsSub',' AND (FCompId = 8457115557031931866)'
|
|
|
|
EXEC PImportTable 'TEmployee','JiNan_ShouXuan.dbo.TEmployee',' AND FId IN(SELECT FEmpId FROM TEmployeeSub WHERE FCompId = 8457115557031931866)'
|
|
EXEC PImportTable 'TEmployeeSub','JiNan_ShouXuan.dbo.TEmployeeSub',' AND (FCompId = 8457115557031931866)'
|
|
|
|
SELECT * FROM TEmployee
|
|
|
|
SELECT 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,FDeleted,FPrice,FTags,FIntrId FROM TEmployee WHERE 1=1 AND FId IN(SELECT FEmpId FROM TEmployeeSub WHERE FCompId = 8457115557031931866)
|
|
AND FNo IN(SELECT FNo FROM JiNan_ShouXuan.dbo.TEmployee)
|
|
|
|
UPDATE TEmployee SET FNo = FNo + '.TN'
|
|
WHERE 1=1 AND FId IN(SELECT FEmpId FROM TEmployeeSub WHERE FCompId = 8457115557031931866)
|
|
AND FNo IN(SELECT FNo FROM JiNan_ShouXuan.dbo.TEmployee)
|
|
|
|
UPDATE TGoods SET FNo = REPLACE(FNo,'TN','')
|
|
UPDATE TGoods SET FNoOfPad = FNo
|
|
|
|
UPDATE TBasicType SET FNo = 'TN' + FNo
|
|
|
|
UPDATE TBasicType SET FNo = REPLACE( FNo,'TN','')
|
|
|
|
|
|
SELECT * FROM TBasicType WHERE FKey = 'ItemType'
|
|
|
|
SELECT * FROM JianMei..TItem
|
|
|
|
|
|
SELECT * FROM TMemberCard WHERE FCompId = 8457115557031931866
|
|
SELECT * FROM TMemberCourse WHERE FCompId = 8457115557031931866
|
|
|
|
|
|
|
|
AND FCardId IN(SELECT FCardId FROM TConsumeBill WHERE FCompId != 8457115557031931866)
|
|
|
|
SELECT * FROM TConsumeBill WHERE FCompId = 8457115557031931866
|
|
|
|
|
|
|
|
EXEC PImportTable 'TMember','JiNan_ShouXuan.dbo.TMember',' AND FMemId IN(SELECT FMemId FROM TMemberCard WHERE FCompId = 8457115557031931866)'
|
|
EXEC PImportTable 'TMember','JiNan_ShouXuan.dbo.TMember',' AND FMemId IN(SELECT FMemId FROM TMemberCourse WHERE FCompId = 8457115557031931866 AND FMemId NOT IN(SELECT FMemId FROM TMemberCard WHERE FCompId = 8457115557031931866))'
|
|
|
|
EXEC PImportTable 'TMemberCard','JiNan_ShouXuan.dbo.TMemberCard',' AND (FCompId = 8457115557031931866)'
|
|
EXEC PImportTable 'TMemberCourse','JiNan_ShouXuan.dbo.TMemberCourse',' AND (FCompId = 8457115557031931866)'
|
|
|
|
SELECT * FROM TMemberArrear
|
|
|
|
SELECT * FROM TMember WHERE FMemId = 4942543034500967219
|
|
|
|
EXEC PImportTable 'TMemberCardAccount','JiNan_ShouXuan.dbo.TMemberCardAccount',' AND FCardId IN(SELECT FCardId FROM TMemberCard WHERE FCompId = 8457115557031931866)'
|
|
EXEC PImportTable 'TMemberCardAcctHist','JiNan_ShouXuan.dbo.TMemberCardAcctHist',' AND FCardId IN(SELECT FCardId FROM TMemberCard WHERE FCompId = 8457115557031931866)'
|
|
EXEC PImportTable 'TMemberTransHist','JiNan_ShouXuan.dbo.TMemberTransHist',' AND FMemId IN(SELECT FMemId FROM TMemberCard WHERE FCompId = 8457115557031931866 UNION ALL SELECT FMemId FROM TMemberCourse WHERE FCompId = 8457115557031931866)'
|
|
|
|
|
|
EXEC PImportTable 'TMemberCourseLog','JiNan_ShouXuan.dbo.TMemberCourseLog',' AND FCardId IN(SELECT FId FROM TMemberCourse WHERE FCompId = 8457115557031931866)'
|
|
|
|
EXEC PImportTable 'TMemberArrear','JiNan_ShouXuan.dbo.TMemberArrear',' AND FMemId IN(SELECT FMemId FROM TMemberCard WHERE FCompId = 8457115557031931866)'
|
|
EXEC PImportTable 'TMemberArrearEmployee','JiNan_ShouXuan.dbo.TMemberArrearEmployee',' AND FMemId IN(SELECT FMemId FROM TMemberCard WHERE FCompId = 8457115557031931866)'
|
|
EXEC PImportTable 'TMemberPhoto','JiNan_ShouXuan.dbo.TMemberPhoto',' AND FMemId IN(SELECT FMemId FROM TMemberCard WHERE FCompId = 8457115557031931866 UNION ALL SELECT FMemId FROM TMemberCourse WHERE FCompId = 8457115557031931866)'
|
|
|
|
|
|
SELECT 'EXEC PImportTable ''' + name + ''',''JiNan_ShouXuan.dbo.' +name +''','' AND FBillId IN(SELECT FBillId FROM TMemberSaleBill WHERE FCompId = 8457115557031931866)'''
|
|
FROM sysobjects WHERE name LIKE 'TMemberSale%'
|
|
|
|
SELECT 'EXEC PImportTable ''' + name + ''',''JiNan_ShouXuan.dbo.' +name +''','' AND FBillId IN(SELECT FBillId FROM '+name+' WHERE FCompId = 8457115557031931866)'''
|
|
FROM sysobjects WHERE name LIKE 'TConsume%'
|
|
|
|
|
|
EXEC PImportTable 'TMemberSaleBill','JiNan_ShouXuan.dbo.TMemberSaleBill',' AND FBillId IN(SELECT FBillId FROM TMemberSaleBill WHERE FCompId = 8457115557031931866)'
|
|
EXEC PImportTable 'TMemberSaleCard','JiNan_ShouXuan.dbo.TMemberSaleCard',' AND FBillId IN(SELECT FBillId FROM TMemberSaleBill WHERE FCompId = 8457115557031931866)'
|
|
EXEC PImportTable 'TMemberSaleCombo','JiNan_ShouXuan.dbo.TMemberSaleCombo',' AND FBillId IN(SELECT FBillId FROM TMemberSaleBill WHERE FCompId = 8457115557031931866)'
|
|
EXEC PImportTable 'TMemberSaleCourse','JiNan_ShouXuan.dbo.TMemberSaleCourse',' AND FBillId IN(SELECT FBillId FROM TMemberSaleBill WHERE FCompId = 8457115557031931866)'
|
|
EXEC PImportTable 'TMemberSaleCourseGoods','JiNan_ShouXuan.dbo.TMemberSaleCourseGoods',' AND FBillId IN(SELECT FBillId FROM TMemberSaleBill WHERE FCompId = 8457115557031931866)'
|
|
EXEC PImportTable 'TMemberSaleCoursePack','JiNan_ShouXuan.dbo.TMemberSaleCoursePack',' AND FBillId IN(SELECT FBillId FROM TMemberSaleBill WHERE FCompId = 8457115557031931866)'
|
|
EXEC PImportTable 'TMemberSaleDetailPayment','JiNan_ShouXuan.dbo.TMemberSaleDetailPayment',' AND FBillId IN(SELECT FBillId FROM TMemberSaleBill WHERE FCompId = 8457115557031931866)'
|
|
EXEC PImportTable 'TMemberSaleEmployee','JiNan_ShouXuan.dbo.TMemberSaleEmployee',' AND FBillId IN(SELECT FBillId FROM TMemberSaleBill WHERE FCompId = 8457115557031931866)'
|
|
EXEC PImportTable 'TMemberSaleExchange','JiNan_ShouXuan.dbo.TMemberSaleExchange',' AND FBillId IN(SELECT FBillId FROM TMemberSaleBill WHERE FCompId = 8457115557031931866)'
|
|
EXEC PImportTable 'TMemberSaleExchangeSource','JiNan_ShouXuan.dbo.TMemberSaleExchangeSource',' AND FBillId IN(SELECT FBillId FROM TMemberSaleBill WHERE FCompId = 8457115557031931866)'
|
|
EXEC PImportTable 'TMemberSaleFreeItem','JiNan_ShouXuan.dbo.TMemberSaleFreeItem',' AND FBillId IN(SELECT FBillId FROM TMemberSaleBill WHERE FCompId = 8457115557031931866)'
|
|
EXEC PImportTable 'TMemberSaleInfo','JiNan_ShouXuan.dbo.TMemberSaleInfo',' AND FBillId IN(SELECT FBillId FROM TMemberSaleBill WHERE FCompId = 8457115557031931866)'
|
|
EXEC PImportTable 'TMemberSalePayment','JiNan_ShouXuan.dbo.TMemberSalePayment',' AND FBillId IN(SELECT FBillId FROM TMemberSaleBill WHERE FCompId = 8457115557031931866)'
|
|
EXEC PImportTable 'TMemberSaleRepay','JiNan_ShouXuan.dbo.TMemberSaleRepay',' AND FBillId IN(SELECT FBillId FROM TMemberSaleBill WHERE FCompId = 8457115557031931866)'
|
|
|
|
|
|
EXEC PImportTable 'TMember','JiNan_ShouXuan.dbo.TMember',' AND FMemId IN(5355197539544682713, 5327119709979962783)'
|
|
EXEC PImportTable 'TMemberCard','JiNan_ShouXuan.dbo.TMemberCard',' AND FCardId IN(5749534491243485609,5453482370543516438)'
|
|
|
|
EXEC PImportTable 'TMember','JiNan_ShouXuan.dbo.TMember',' AND FMemId IN(4861494900138696578, 4803687944749049030)'
|
|
|
|
EXEC PImportTable 'TItemBaseComm','JiNan_ShouXuan.dbo.TItemBaseComm',' AND FCompID= 8457115557031931866'
|
|
EXEC PImportTable 'TGoodsBaseComm','JiNan_ShouXuan.dbo.TGoodsBaseComm',' AND FCompID= 8457115557031931866'
|
|
EXEC PImportTable 'TMemberCardTypeBaseComm','JiNan_ShouXuan.dbo.TMemberCardTypeBaseComm',' AND FCompID= 8457115557031931866'
|
|
EXEC PImportTable 'TCourseSaleBaseComm','JiNan_ShouXuan.dbo.TCourseSaleBaseComm',' AND FCompID= 8457115557031931866'
|
|
|
|
EXEC PImportTable 'TItemCommSchemeMaster','JiNan_ShouXuan.dbo.TItemCommSchemeMaster',' AND FCompID= 8457115557031931866'
|
|
EXEC PImportTable 'TItemCommSchemeSection','JiNan_ShouXuan.dbo.TItemCommSchemeSection',' AND FMasterId IN(SELECT FId FROM TItemCommSchemeMaster WHERE FCompID= 8457115557031931866)'
|
|
EXEC PImportTable 'TItemCommSchemeDetail','JiNan_ShouXuan.dbo.TItemCommSchemeDetail',' AND FMasterId IN(SELECT FId FROM TItemCommSchemeMaster WHERE FCompID= 8457115557031931866)'
|
|
|
|
EXEC PImportTable 'TEmployeeCommScheme','JiNan_ShouXuan.dbo.TEmployeeCommScheme',' AND FCompID= 8457115557031931866'
|
|
|
|
SELECT * F
|
|
|
|
EXEC PSetEmpPerfCacheExpired 1
|
|
|
|
SELECT * FROM TConsumeBill WHERE FCompId = 8457115557031931866 AND FMemId IN(SELECT FMemId FROM TMemberCourse WHERE FCompId != 8457115557031931866)
|
|
AND FMemId != 0
|
|
|
|
SELECT * FROM TConsumeItem WHERE FBillId IN(SELECT FBillId FROM TConsumeBill WHERE FCompId = 8457115557031931866)
|
|
AND FItemId NOT IN(SELECT FId FROM JiNan_ShouXuan.dbo.TItem)
|
|
|
|
SELECT * FROM TConsumeItemEmployee WHERE FBillId IN(SELECT FBillId FROM TConsumeBill WHERE FCompId = 8457115557031931866)
|
|
AND FEmpId NOT IN(SELECT FId FROM JiNan_ShouXuan.dbo.TEmployee)
|
|
|
|
|
|
SELECT * FROM TItem WHERE FId = 4649769009652039160
|
|
EXEC PImportTable 'TItem','JiNan_ShouXuan.dbo.TItem',' AND FId =4649769009652039160 '
|
|
EXEC PImportTable 'TEmployee','JiNan_ShouXuan.dbo.TEmployee',' AND FId =5550355459221313653 '
|
|
|
|
EXEC PImportTable 'TConsumeBill','JiNan_ShouXuan.dbo.TConsumeBill',' AND FBillId IN(SELECT FBillId FROM TConsumeBill WHERE FCompId = 8457115557031931866)'
|
|
EXEC PImportTable 'TConsumeGoods','JiNan_ShouXuan.dbo.TConsumeGoods',' AND FBillId IN(SELECT FBillId FROM TConsumeBill WHERE FCompId = 8457115557031931866)'
|
|
EXEC PImportTable 'TConsumeGoodsEmployee','JiNan_ShouXuan.dbo.TConsumeGoodsEmployee',' AND FBillId IN(SELECT FBillId FROM TConsumeBill WHERE FCompId = 8457115557031931866)'
|
|
EXEC PImportTable 'TConsumeGoodsPayment','JiNan_ShouXuan.dbo.TConsumeGoodsPayment',' AND FBillId IN(SELECT FBillId FROM TConsumeBill WHERE FCompId = 8457115557031931866)'
|
|
EXEC PImportTable 'TConsumeItem','JiNan_ShouXuan.dbo.TConsumeItem',' AND FBillId IN(SELECT FBillId FROM TConsumeBill WHERE FCompId = 8457115557031931866)'
|
|
EXEC PImportTable 'TConsumeReturnVisit','JiNan_ShouXuan.dbo.TConsumeReturnVisit',' AND FBillId IN(SELECT FBillId FROM TConsumeBill WHERE FCompId = 8457115557031931866)'
|
|
EXEC PImportTable 'TConsumeItemCostGoods','JiNan_ShouXuan.dbo.TConsumeItemCostGoods',' AND FBillId IN(SELECT FBillId FROM TConsumeBill WHERE FCompId = 8457115557031931866)'
|
|
EXEC PImportTable 'TConsumeItemEmployee','JiNan_ShouXuan.dbo.TConsumeItemEmployee',' AND FBillId IN(SELECT FBillId FROM TConsumeBill WHERE FCompId = 8457115557031931866)'
|
|
EXEC PImportTable 'TConsumeItemPayment','JiNan_ShouXuan.dbo.TConsumeItemPayment',' AND FBillId IN(SELECT FBillId FROM TConsumeBill WHERE FCompId = 8457115557031931866)'
|
|
|
|
SELECT * FROM TMemberSaleBill WHERE FCompId = 8457115557031931866
|
|
|
|
EXEC PSetEmpPerfCacheExpired 1
|
|
|
|
sp_helptext PImportTable
|
|
|
|
UPDATE TMemberCard SET FState = 4 ,FDeleted = 1 WHERE FCompId = 8457115557031931866
|
|
UPDATE TMemberCourse SET FState = 4 ,FDeleted = 1 WHERE FCompId = 8457115557031931866
|
|
|
|
select * from tuser
|
|
|
|
|
|
SELECT * FROM
|
|
TMemberCourse
|
|
|
|
INSERT JiNan_ShouXuan.dbo.TMemberCourse(FId,FMemId,FCompId,FItemTimes,FItemUsedTimes,FGoodsQuantity,FGoodsUsedQuantity,FAmount,FArrear,FPurchaseDate,FExpiredDate,FBizType,FTransId,FUseUp)
|
|
SELECT FId,FMemId,FCompId,FItemTimes,FItemUsedTimes,FGoodsQuantity,FGoodsUsedQuantity,FAmount,FArrear,FPurchaseDate,FExpiredDate,FBizType,FTransId,FUseUp FROM TMemberCourse WHERE 1=1 AND (FCompId = 8457115557031931866)
|
|
|