2018-05-03

2018 年 5 月 3 日2018 年 5 月 4 日 by 沧海之树 In 工作

with t
as
(
select 1 TimesOfTreatment, isnull(b.Times, 1) TimesOfOneTreatment, Standard, a.ID
from iteminfo a
left join(
select case when Standard like '_次%' then SUBSTRING(REPLACE(Standard, '三', 3), 1, 1)
when Standard like '__次%' then SUBSTRING(Standard,1, 2) end Times, ID
from iteminfo
where Standard like '%次%' and Standard not like '单%'
and Standard <> '全面部单次'
) as b on a.ID=b.ID
)
update iteminfo set TimesOfTreatment=t.TimesOfTreatment, TimesOfOneTreatment=t.TimesOfOneTreatment
from ItemInfo a inner join t on a.ID=t.ID

 

未审核

update MemberCardHandle set Checked=1

更新执行信息

update CustomerConsumptionDetails set CanExecute=0 where ExcutePercent>=1;

 

MERGE INTO CustomerLevelDetails A USING (select id from CustomerBaseInfo) C ON (A.CustomerID=C.id)
WHEN NOT MATCHED THEN
INSERT(CustomerID, CashAmount, ReturnCashAmount, Balance, LevelID, Manual) VALUES(c.id, 0, 0, 0, 0, 0);

 

 

if exists(select * from sysobjects where name = 'fun_getPY')
drop function fun_getPY
go
create function fun_getPY(@str nvarchar(4000))
returns nvarchar(4000)
as
begin
declare @word nchar(1), @PY nvarchar(4000)

set @PY = ''
while len(@str) > 0
begin
set @word = left(@str, 1) --如果非汉字字符，返回原字符
set @PY = @PY +
--简体GBK码汉字UNICODE值从19968开始, WINDOWS多国汉字，UNICODE目前收录汉字共20902个
(case when unicode(@word) between 19968 and 19968 + 20901 then
(select top 1 PY from
( select 'A' as PY, N'驁' as word
union all
select 'B',N'簿'
union all
select 'C',N'錯'
union all
select 'D',N'鵽'
union all
select 'E',N'樲'
union all
select 'F',N'鰒'
union all
select 'G',N'腂'
union all
select 'H',N'夻'
union all
select 'J',N'攈'
union all
select 'K',N'穒'
union all
select 'L',N'鱳'
union all
select 'M',N'旀'
union all
select 'N',N'桛'
union all
select 'O',N'漚'
union all
select 'P',N'曝'
union all
select 'Q',N'囕'
union all
select 'R',N'鶸'
union all
select 'S',N'蜶'
union all
select 'T',N'籜'
union all
select 'W',N'鶩'
union all
select 'X',N'鑂'
union all
select 'Y',N'韻'
union all
select 'Z',N'咗') T
where word >= @word
collate Chinese_PRC_CS_AS_KS_WS order by PY ASC)
else @word
end)
set @str = right(@str, len(@str) - 1)
end
return @PY
end

update ItemInfo set SimpleCode=bh20180420.dbo.fun_getPY(name)

 

 

-- 整形项目次数及单价
with t
as
(
select 1 TimesOfTreatment, isnull(b.Times, 1) TimesOfOneTreatment, Standard, a.ID, UnitPrice
from iteminfo a
left join(
select case when Standard like '_次%' then SUBSTRING(REPLACE(Standard, '三', 3), 1, 1)
when Standard like '__次%' then SUBSTRING(Standard,1, 2) end Times, ID
from iteminfo
where Standard like '%次%' and Standard not like '单%'
and Standard <> '全面部单次'
) as b on a.ID=b.ID
)
update iteminfo set TimesOfTreatment=t.TimesOfTreatment, TimesOfOneTreatment=t.TimesOfOneTreatment,
IsTreatment=(case when t.TimesOfOneTreatment>1 then 1 else 0 end), PriceOfOneTime=t.UnitPrice
from ItemInfo a inner join t on a.ID=t.ID

update iteminfo set TimesOfTreatment=

 

 

--皮肤美容科,皮肤治疗费,脱毛 -----脱毛
update ItemInfo set ExcuteDepartmentID='003',IncomeCategoryID='16',ConsultingItemCategoryID='00025' where categoryID in ('00024','00054');
--整形美容科,手术费,耳部整形 ------耳部整形
update ItemInfo set ExcuteDepartmentID='008',IncomeCategoryID='18',ConsultingItemCategoryID='00008' where categoryID='00008';
--皮肤美容科,皮肤治疗费,皮肤美容项目 ------医学美肤,其他皮肤项目
update ItemInfo set ExcuteDepartmentID='003',IncomeCategoryID='16',ConsultingItemCategoryID='00017' where categoryID in ('00027','00025');
--注射美容科,微整形费,微整形 ------微整形
update ItemInfo set ExcuteDepartmentID='011',IncomeCategoryID='24',ConsultingItemCategoryID='00013' where categoryID='00015';

--皮肤美容科,微整形费,微整形 ------医学纹绣
update ItemInfo set ExcuteDepartmentID='003',IncomeCategoryID='16',ConsultingItemCategoryID='00018' where categoryID='00017';

--检验科,化验费,其他项目 ------化验费
update ItemInfo set ExcuteDepartmentID='024',IncomeCategoryID='09',ConsultingItemCategoryID='00045' where categoryID='00047';

--皮肤美容科,皮肤治疗费,祛斑 ------祛斑
update ItemInfo set ExcuteDepartmentID='003',IncomeCategoryID='16',ConsultingItemCategoryID='00024' where categoryID ='00023';
--皮肤美容科,皮肤治疗费,纹身 ------纹身
update ItemInfo set ExcuteDepartmentID='003',IncomeCategoryID='16',ConsultingItemCategoryID='00031' where categoryID ='00030';

--口腔,口腔,口腔美容项目 ------牙齿美容矫正系列
update ItemInfo set ExcuteDepartmentID='002',IncomeCategoryID='12',ConsultingItemCategoryID='00036' where categoryID in ('00039','00038','00042','00043','00036','00040','00041','00037','00035');

--整形美容科,手术费,眉部整形 ------眉部整形
update ItemInfo set ExcuteDepartmentID='008',IncomeCategoryID='18',ConsultingItemCategoryID='00006' where categoryID='00006';

--整形美容科,手术费,口唇部整形 ------口唇部整形
update ItemInfo set ExcuteDepartmentID='008',IncomeCategoryID='18',ConsultingItemCategoryID='00007' where categoryID='00007';

--整形美容科,手术费,口唇部整形 ------口唇部整形
update ItemInfo set ExcuteDepartmentID='008',IncomeCategoryID='18',ConsultingItemCategoryID='00005' where categoryID='00005';

--整形美容科,手术费,鼻部整形 ------鼻部整形
update ItemInfo set ExcuteDepartmentID='008',IncomeCategoryID='18',ConsultingItemCategoryID='00003' where categoryID='00003';

--整形美容科,手术费,鼻部整形 ------鼻部整形
update ItemInfo set ExcuteDepartmentID='008',IncomeCategoryID='18',ConsultingItemCategoryID='00002' where categoryID='00002';

--整形美容科,手术费,胸部整形 ------胸部整形
update ItemInfo set ExcuteDepartmentID='008',IncomeCategoryID='18',ConsultingItemCategoryID='00004' where categoryID='00004';

--皮肤美容科,皮肤治疗费,祛痘 ------祛痘
update ItemInfo set ExcuteDepartmentID='003',IncomeCategoryID='16',ConsultingItemCategoryID='00023' where categoryID ='00022';

--皮肤美容科,皮肤治疗费,胎记 ------胎记
update ItemInfo set ExcuteDepartmentID='003',IncomeCategoryID='16',ConsultingItemCategoryID='00029' where categoryID ='00028';

--皮肤美容科,皮肤治疗费,胎记 ------祛痣
update ItemInfo set ExcuteDepartmentID='003',IncomeCategoryID='16',ConsultingItemCategoryID='00022' where categoryID ='00021';

--皮肤美容科,皮肤治疗费,疤痕 ------疤痕
update ItemInfo set ExcuteDepartmentID='003',IncomeCategoryID='16',ConsultingItemCategoryID='00030' where categoryID ='00029';

--皮肤美容科,皮肤治疗费,其他 ------其他
update ItemInfo set ExcuteDepartmentID='008',IncomeCategoryID='16',ConsultingItemCategoryID='00046' where categoryID in('00052','00058','00059','00050','00061','00049','00056','00009','00053','00055','00057','00013','00048');

--select * from Department

--select * from IncomeCategory

--select * from ItemForConsultingCategory

--整形美容科,手术费,自体脂肪填充 ------自体脂肪填充
update ItemInfo set ExcuteDepartmentID='008',IncomeCategoryID='18',ConsultingItemCategoryID='00014' where categoryID='00014';

--整形美容科,手术费,手术除皱项目 ------手术除皱项目
update ItemInfo set ExcuteDepartmentID='008',IncomeCategoryID='18',ConsultingItemCategoryID='00011' where categoryID='00012';

--皮肤美容科,皮肤治疗费,疤痕 ------疤痕
update ItemInfo set ExcuteDepartmentID='003',IncomeCategoryID='16',ConsultingItemCategoryID='00021' where categoryID ='00020';

--皮肤美容科,皮肤治疗费,酷塑溶脂 ------酷塑溶脂
update ItemInfo set ExcuteDepartmentID='008',IncomeCategoryID='16',ConsultingItemCategoryID='00032' where categoryID ='00031';

--皮肤美容科,皮肤治疗费,水动力溶脂塑身 ------水动力溶脂塑身
update ItemInfo set ExcuteDepartmentID='003',IncomeCategoryID='16',ConsultingItemCategoryID='00010' where categoryID ='00010';

--皮肤美容科,皮肤治疗费,生活美容项目 ------生活美容项目
update ItemInfo set ExcuteDepartmentID='003',IncomeCategoryID='16',ConsultingItemCategoryID='00020' where categoryID ='00019';

--整形美容科,手术费,专家点名费 ------专家点名费
update ItemInfo set ExcuteDepartmentID='008',IncomeCategoryID='18',ConsultingItemCategoryID='00015' where categoryID='00011';

--皮肤美容科,皮肤治疗费,紧肤除皱 ------紧肤除皱
update ItemInfo set ExcuteDepartmentID='003',IncomeCategoryID='16',ConsultingItemCategoryID='00019' where categoryID ='00018';

--皮肤美容科,皮肤治疗费,紧肤除皱 ------紧肤除皱
update ItemInfo set ExcuteDepartmentID='003',IncomeCategoryID='16',ConsultingItemCategoryID='00027' where categoryID ='00026';

--皮肤美容科,皮肤治疗费,紧肤除皱 ------超声聚焦减脂
update ItemInfo set ExcuteDepartmentID='003',IncomeCategoryID='16',ConsultingItemCategoryID='00034' where categoryID ='00033';

--皮肤美容科,皮肤治疗费,紧肤除皱 ------溶脂塑身
update ItemInfo set ExcuteDepartmentID='003',IncomeCategoryID='16',ConsultingItemCategoryID='00033' where categoryID ='00032';