DELIMITER $$
CREATE DEFINER=`sa`@`%` PROCEDURE `UpdateProvinceSalePrice`()
MODIFIES SQL DATA
SQL SECURITY INVOKER
BEGIN
-- 需要定义接收游标数据的变量
DECLARE priceid int(11);
DECLARE pricecode varchar(36);-- PricingCategory.code
DECLARE pricecorpid varchar(36);
DECLARE pricecorpcode varchar(50);
DECLARE pricecustomertypeid int(11);
DECLARE pricegrouptype smallint(6);
DECLARE pricegroupcode varchar(50);
DECLARE pricemethod smallint(6);
DECLARE priceIsForced smallint(6);
DECLARE priceSalePrice decimal(11);
DECLARE pricePrecision int(11);
DECLARE priceRetentionway smallint(6);
DECLARE pricestatus smallint(6);
DECLARE pricecheckprice INT;
DECLARE MapSalePrice varchar(30);
DECLARE MapCategoryCode varchar(30);
DECLARE SalePriceFormat varchar(150);
DECLARE PinGroupCode varchar(30);
DECLARE PinCode varchar(30);
DECLARE CompanyName varchar(30);
DECLARE ExistProductId varchar(38);
DECLARE ExistProductCode varchar(38);
-- 遍历数据结束标志
DECLARE done bool DEFAULT false;
-- 游标
DECLARE cur CURSOR FOR SELECT id,Code, corpid,corpcode,customertypeid, grouptype,groupcode,pricemethods,IsForced,SalePrice,`Precision`,`Retentionway`,`status` FROM ProvincePricingCategory category
join ProvincePricingCategory_SYNC categorysync on category.Id=BillId
join schedulelog log on categorysync.SyncNum> log.syncnum where log.tagbillname='ProvincePricingCategory' and !category.IsForced and id=19474;
-- 将结束标志绑定到游标
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
-- 打开游标
OPEN cur;
-- 开始循环
read_loop: LOOP
-- 提取游标里的数据，这里只有一个，多个的话也一样；
FETCH cur INTO priceid,pricecode, pricecorpid,pricecorpcode,pricecustomertypeid,pricegrouptype,pricegroupcode,pricemethod,priceIsForced,priceSalePrice,pricePrecision,priceRetentionway,pricestatus;
-- 声明结束的时候
IF done THEN
LEAVE read_loop;
END IF;
-- 这里做你想做的循环的事件--
SET SQL_SAFE_UPDATES = 0;
set CompanyName= concat('P',pricecorpcode);
set @pricecode=pricecode; -- 动态拼sql
select salepricefieldmap.PartsSalePrice,salepricefieldmap.PriceCategoryCode from customertype
join salepricefieldmap on customertype.TypeCode =salepricefieldmap.TypeCode where customertype.Id=pricecustomertypeid into MapSalePrice,MapCategoryCode;
select SalePriceFormat(pricemethod,pricePrecision,priceRetentionway,priceSalePrice) into SalePriceFormat; -- 获得销售价计算公式
select pricemethod,pricePrecision,priceRetentionway,priceSalePrice,SalePriceFormat;
CREATE TEMPORARY TABLE if not exists PartsSalePrice_TMP(
ProductId varchar(36),
ProductCode varchar(36)
) ENGINE = MEMORY;
CREATE TEMPORARY TABLE if not exists sparepart_tmp(
ProductId varchar(36),
ProductCode varchar(36),
SupplierCode varchar(36)
) ENGINE = MEMORY;
if(pricegrouptype=3) then
select part.ProductId from sparepart part
inner join partsgroup fz on fz.groupcode = part.groupcode and fz.category=1
inner join partsgroup pp on pp.parentcode = fz.code and pp.groupcode = part.groupcode and pp.category=2
join ProvincePricingCategory category on pp.code =category.Groupcode and category.grouptype=2 and category.corpid = pricecorpid and category.status=2
where part.ProductCode =pricegroupcode and category.IsForced=1 limit 1 into ExistProductId;
if ExistProductId is null
THEN
set ExistProductCode=null;
select pgroup.code from sparepart part
join PartsGroup pgroup on part.groupcode=pgroup.groupcode and pgroup.category=1
where part.ProductCode= pricegroupcode into ExistProductCode;
select * from ProvincePricingCategory category
join PartsGroup pgroup on category.groupcode=pgroup.code and category.corpid = pricecorpid and category.status=2
where FIND_IN_SET(pgroup.code, GetAncestry(ExistProductCode)) and category.grouptype=1 and category.corpid = pricecorpid limit 1 into ExistProductId ;
if(ExistProductId is null)THEN
insert into sparepart_tmp
SELECT part.ProductId, part.ProductCode,cost.SupplierCode FROM sparepart part
join enterprisepartscost cost on cost.productcode=part.productcode where part.productcode=pricegroupcode;
set @insertsql= concat('INSERT INTO PartsSalePrice_TMP
select part.ProductCode,part.ProductId from sparepart_tmp part LEFT join ', CompanyName ,' price on part.ProductCode =price.productcode and part.SupplierCode =price.SupplierCode
WHERE price.productcode is null');
prepare stmpinsert from @insertsql;
execute stmpinsert;
if(exists(select 1 from PartsSalePrice_TMP)) then
set @SalePricesql= concat('insert into ',CompanyName,'(productid,productcode,productname,suppliercode,suppliername,checkprice,',MapSalePrice,',',MapCategoryCode,')
select part.ProductId,part.ProductCode,cost.ProductName,cost.SupplierCode,cost.SupplierName,cost.CheckPrice,',SalePriceFormat,',',' ? from sparepart_tmp part join enterprisepartscost cost
on part.Productcode=cost.Productcode;');
prepare stmp from @SalePricesql;
execute stmp using @pricecode;
end if;
set @SalePricesql=concat('update ',CompanyName,' price join enterprisepartscost cost on cost.productcode= price.productcode and cost.suppliercode=price.suppliercode
set price.',MapSalePrice,'=',SalePriceFormat,',' ,MapCategoryCode,'= ? ',' where price.productcode =cost.productcode and price.productcode=',pricegroupcode);
prepare stmp from @SalePricesql;
execute stmp using @pricecode;
END IF;
END IF;
end if;
if(pricegrouptype=2) then
select fz.code from partsgroup fz
join PartsGroup pgroup on pgroup.parentcode=fz.code and pgroup.category=2
where pgroup.code= pricegroupcode into ExistProductCode;
select part.ProductId from partsgroup fz
join ProvincePricingCategory category on fz.code=category.Groupcode and category.grouptype=2 and category.corpid = pricecorpid
where FIND_IN_SET(fz.code, GetAncestry(ExistProductCode)) and category.IsForced=1 limit 1 into ExistProductCode;
if (ExistProductId is null) THEN
insert into sparepart_tmp
SELECT part.ProductId, part.ProductCode,cost.SupplierCode FROM sparepart part
join PartsGroup parentgroup on parentgroup.groupcode=part.groupcode and parentgroup.category=1
join PartsGroup childrengroup on childrengroup.ParentCode=parentgroup.code and childrengroup.groupcode = part.SpareBrandCode and childrengroup.category=2
left join ProvincePricingCategory category on part.productcode=category.groupcode and category.grouptype=3 and category.corpid = pricecorpid and category.IsForced=1 and status=2
join enterprisepartscost cost on cost.productcode=part.productcode
where category.code is null and childrengroup.groupcode=pricegroupcode;
set @insertsql= concat('INSERT INTO PartsSalePrice_TMP
select part.ProductCode,part.ProductId from sparepart_tmp part LEFT join ', CompanyName ,' price on part.ProductCode =price.productcode and part.SupplierCode =price.SupplierCode
WHERE price.productcode is null');
prepare stmpinsert from @insertsql;
execute stmpinsert;
if(exists(select 1 from PartsSalePrice_TMP)) then
set @SalePricesql= concat('insert into ',CompanyName,'(productid,productcode,productname,suppliercode,suppliername,checkprice,',MapSalePrice,',',MapCategoryCode,')
select part.ProductId,part.ProductCode,cost.ProductName,cost.SupplierCode,cost.SupplierName,cost.CheckPrice,',SalePriceFormat,',',' ? from sparepart_tmp part join enterprisepartscost cost
on part.Productcode=cost.Productcode;');
prepare stmp from @SalePricesql;
execute stmp using @pricecode;
end if;
set @SalePricesql= concat('update ',CompanyName,' price join sparepart_tmp part on part.ProductCode =price.productcode and cost.suppliercode=price.suppliercode
set price.CheckPrice=cost.CheckPrice,price.',MapSalePrice,'=',SalePriceFormat,',' , MapCategoryCode,'= ? ');
prepare stmp from @SalePricesql;
execute stmp using @pricecode;
END IF;
end if;
if(pricegrouptype=1) then
select pgroup.code from PartsGroup pgroup
join ProvincePricingCategory category on pgroup.code=category.Groupcode and category.grouptype=1 and category.corpid = pricecorpid and status=2
where FIND_IN_SET(pgroup.code, GetAncestry(pricegroupcode)) and category.IsForced limit 1 into ExistProductId;
if ExistProductId is null THEN
insert into sparepart_tmp
select distinct fenzu.ProductId,fenzu.ProductCode,fenzu.SupplierCode from
(select distinct part.ProductId,part.ProductCode, b.SupplierCode from PartsGroup pgroup
left join ProvincePricingCategory category on pgroup.code=category.GroupCode and category.grouptype=1 and category.corpid = pricecorpid and category.IsForced=1 and category.status=2 and FIND_IN_SET(parentcode, queryChildrenGroup(pricegroupcode))
join sparepart part on pgroup.groupcode=part.groupcode
join EnterprisePartsCost b on part.productcode = b.productcode
where category.GroupCode is null and pgroup.category=1)fenzu
join(
select distinct part.ProductId ,part.ProductCode, b.SupplierCode from PartsGroup pgroup
join PartsGroup cgroup on pgroup.code=cgroup.parentcode and FIND_IN_SET(cgroup.parentcode, queryChildrenGroup(pricegroupcode)) and cgroup.category=2
left join ProvincePricingCategory category on cgroup.code=category.GroupCode and category.grouptype=2 and category.corpid = pricecorpid and category.IsForced=1 and category.status=2
join sparepart part on cgroup.groupcode=part.SpareBrandCode and part.groupcode = pgroup.groupcode
join EnterprisePartsCost b on part.productcode = b.productcode
where category.GroupCode is null) pinpai
on fenzu.ProductId=pinpai.ProductId
join (
select distinct part.ProductId,part.ProductCode ,b.SupplierCode from PartsGroup pgroup
join sparepart part on pgroup.groupcode=part.groupcode
left join ProvincePricingCategory category on part.productcode=category.GroupCode and category.grouptype=3 and FIND_IN_SET(parentcode, queryChildrenGroup(pricegroupcode)) and category.corpid = pricecorpid and category.IsForced=1 and category.status=2
join EnterprisePartsCost b on part.productcode = b.productcode
where category.GroupCode is null)peijian
on fenzu.ProductId=peijian.ProductId;
set @insertsql= concat('INSERT INTO PartsSalePrice_TMP
select part.ProductCode,part.ProductId from sparepart_tmp part LEFT join ', CompanyName ,' price on part.ProductCode =price.productcode and part.SupplierCode =price.SupplierCode
WHERE price.productcode is null');
prepare stmpinsert from @insertsql;
execute stmpinsert;
if(exists(select 1 from PartsSalePrice_TMP)) then
set @SalePriceInsertsql= concat('insert into ',CompanyName,'(productid,productcode,productname,suppliercode,suppliername,checkprice,',MapSalePrice,',',MapCategoryCode,')
select part.ProductId,part.ProductCode,cost.ProductName,cost.SupplierCode,cost.SupplierName,cost.CheckPrice,',SalePriceFormat,',',' ? from sparepart_tmp part join enterprisepartscost cost
on part.Productcode=cost.Productcode;');
prepare stmpin from @SalePriceInsertsql;
execute stmpin using @pricecode;
end if;
set @SalePricesql= concat('update ',CompanyName,' price join sparepart_tmp part on part.ProductCode =price.productcode
join enterprisepartscost cost on cost.productcode= price.productcode and cost.suppliercode=price.suppliercode
set price.',MapSalePrice,'=',SalePriceFormat, ',' ,MapCategoryCode,'= ? ');
prepare stmp from @SalePricesql;
execute stmp using @pricecode;
end if;
END IF;
drop table PartsSalePrice_TMP;
drop table sparepart_tmp;
END LOOP;
-- 关闭游标
CLOSE cur;
END;$$
use dcs;
call UpdateProvinceSalePrice()
call UpdateSalePrice()
INSERT INTO PartsSalePrice_TMP select part.ProductCode,part.ProductId from sparepart_tmp part LEFT join P1001 price on part.ProductCode =price.productcode and part.SupplierCode =price.SupplierCode WHERE price.productcode is null
INSERT INTO PartsSalePrice_TMP
select part.ProductCode,part.ProductId from sparepart_tmp part LEFT join P1001 price on part.ProductCode =price.productcode and part.SupplierCode =price.SupplierCode
WHERE price.productcode is null
CREATE TABLE if not exists PartsSalePrice_TMP(
ProductId varchar(36),
ProductCode varchar(36)
) ENGINE = MEMORY;
use dcs;
DELIMITER $$
select * from p1001
CREATE DEFINER=`sa`@`%` PROCEDURE `UpdateSalePrice`()
MODIFIES SQL DATA
SQL SECURITY INVOKER
BEGIN
-- 需要定义接收游标数据的变量
DECLARE priceid int(11);-- PricingCategory.id
DECLARE pricecode varchar(36);-- PricingCategory.code
DECLARE pricecorpid varchar(36);-- PricingCategory.corpid
DECLARE pricecorpcode varchar(50);-- PricingCategory.corpcode
DECLARE pricecustomertypeid int(11);-- PricingCategory.customertypeid
DECLARE pricegrouptype smallint(6);-- PricingCategory.grouptype
DECLARE pricegroupcode varchar(50);-- PricingCategory.groupcode
DECLARE pricemethod smallint(6);-- PricingCategory.pricemethods
DECLARE priceIsForced smallint(6);-- PricingCategory.IsForced
DECLARE priceSalePrice decimal(11);-- PricingCategory.SalePrice
DECLARE pricePrecision int(11);-- PricingCategory.Precision
DECLARE priceRetentionway smallint(6);-- PricingCategory.Retentionway
DECLARE pricestatus smallint(6);-- PricingCategory.status
DECLARE SalePriceFormat varchar(150);-- SalePriceFormat()
DECLARE MapSalePrice varchar(30);-- salepricefieldmap.PartsSalePrice
DECLARE MapCategoryCode varchar(30);-- salepricefieldmap.PriceCategoryCode
DECLARE CompanyName varchar(30);-- 'p'+PricingCategory.corpcode
-- 遍历数据结束标志
DECLARE done bool DEFAULT false;
-- 游标
DECLARE cur CURSOR FOR SELECT id,Code, corpid,corpcode,customertypeid,grouptype,groupcode,pricemethods,IsForced,category.SalePrice,`Precision`,`Retentionway`,`status` FROM PricingCategory category
join PricingCategory_SYNC categorysync on category.Id=BillId
join schedulelog log on categorysync.SyncNum> log.syncnum where log.tagbillname='PricingCategory' ;
-- 将结束标志绑定到游标
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
-- 打开游标
OPEN cur;
-- 开始循环
read_loop: LOOP
-- 提取游标里的数据，这里只有一个，多个的话也一样；
FETCH cur INTO priceid,pricecode, pricecorpid,pricecorpcode,pricecustomertypeid,pricegrouptype,pricegroupcode,pricemethod,priceIsForced,priceSalePrice,pricePrecision,priceRetentionway,pricestatus;
-- 声明结束的时候
IF done THEN
LEAVE read_loop;
END IF;
-- 这里做你想做的循环的事件--
SET SQL_SAFE_UPDATES = 0;
set CompanyName= 'PartsSalePrice'; -- 需要更新的销售价表
select salepricefieldmap.PartsSalePrice,salepricefieldmap.PriceCategoryCode from customertype
join salepricefieldmap on customertype.TypeCode =salepricefieldmap.TypeCode where customertype.Id=pricecustomertypeid into MapSalePrice,MapCategoryCode; -- 获得需要更新的销售价具体列
select SalePriceFormat(pricemethod,pricePrecision,priceRetentionway,priceSalePrice) into SalePriceFormat; -- 获得销售价计算公式
set @pricecode=pricecode; -- 动态拼sql
CREATE TEMPORARY TABLE if not exists PartsSalePrice_TMP(
ProductId varchar(50),
ProductCode varchar(36)
) ENGINE = MEMORY; -- 需要新增的销售价临时表
CREATE TEMPORARY TABLE if not exists sparepart_tmp(
ProductId varchar(50),
ProductCode varchar(36),
SupplierCode varchar(36)
) ENGINE = MEMORY; -- 需要更新的动态备件表
if(pricegrouptype=3) then -- 定价原则为备件
insert into sparepart_tmp SELECT part.ProductId, part.ProductCode ,cost.SupplierCode FROM sparepart part join enterprisepartscost cost on cost.productcode=part.productcode where part.productcode=1150;-- 需要更新的 配件临时表
INSERT INTO PartsSalePrice_TMP -- 需要新增的销售价临时表
select part.ProductCode,part.ProductId from sparepart_tmp part LEFT join PartsSalePrice price on part.ProductCode =price.productcode and part.SupplierCode =price.SupplierCode WHERE price.productcode is null;
if(exists(select 1 from PartsSalePrice_TMP)) then -- 新增销售价
set @SalePricesql= concat('insert into ',CompanyName,'(productid,productcode,productname,suppliercode,suppliername,checkprice,',MapSalePrice,',',MapCategoryCode,')
select part.ProductId,part.ProductCode,cost.ProductName,cost.SupplierCode,cost.SupplierName,cost.CheckPrice,',SalePriceFormat,',',' ? from sparepart_tmp part join enterprisepartscost cost
on part.Productcode=cost.Productcode;');
prepare stmp from @SalePricesql;
execute stmp using @pricecode;
end if;
-- Update 销售价
set @SalePricesql=concat('update ',CompanyName,' price join enterprisepartscost cost on cost.productcode= price.productcode and cost.suppliercode=price.suppliercode
set price.CheckPrice=cost.CheckPrice,price.',MapSalePrice,'=',SalePriceFormat,',' ,MapCategoryCode,'= ? where price.productcode =cost.productcode and price.productcode=',pricegroupcode);
prepare stmp from @SalePricesql;
execute stmp using @pricecode;
end if;
if(pricegrouptype=2) then
insert into sparepart_tmp -- 需要新增的销售价临时表
SELECT part.ProductId, part.ProductCode ,cost.SupplierCode FROM sparepart part
join PartsGroup parentgroup on parentgroup.groupcode=part.groupcode and parentgroup.category=1
join PartsGroup childrengroup on childrengroup.ParentCode=parentgroup.code and childrengroup.groupcode = part.SpareBrandCode and childrengroup.category=2
left join PricingCategory category on part.productcode=category.groupcode and category.grouptype=3
join enterprisepartscost cost on cost.productcode=part.productcode
where category.code is null and childrengroup.groupcode=pricegroupcode;
INSERT INTO PartsSalePrice_TMP-- 需要新增的销售价临时表
select part.ProductCode,part.ProductId from sparepart_tmp part LEFT join PartsSalePrice price on part.ProductCode =price.productcode and part.SupplierCode =price.SupplierCode
WHERE price.productcode is null;
if(exists(select 1 from PartsSalePrice_TMP)) then-- 新增销售价
set @SalePricesql= concat('insert into ',CompanyName,'(productid,productcode,productname,suppliercode,suppliername,checkprice,',MapSalePrice,',',MapCategoryCode,')
select part.ProductId,part.ProductCode,cost.ProductName,cost.SupplierCode,cost.SupplierName,cost.CheckPrice,',SalePriceFormat,',',' ? from sparepart_tmp part join enterprisepartscost cost
on part.Productcode=cost.Productcode;');
prepare stmp from @SalePricesql;
execute stmp using @pricecode;
end if;
-- Update 销售价
set @SalePricesql= concat('update ',CompanyName,' price join sparepart_tmp part on part.ProductCode =price.productcode join enterprisepartscost on cost.suppliercode=price.suppliercode
set price.CheckPrice=cost.CheckPrice,price.',MapSalePrice,'=',SalePriceFormat,',' , MapCategoryCode,'= ? ');
prepare stmp from @SalePricesql;
execute stmp using @pricecode;
end if;
if(pricegrouptype=1) then
insert into sparepart_tmp(ProductId,ProductCode,SupplierCode) -- 需要新增的销售价临时表
select distinct fenzu.ProductId,fenzu.ProductCode,fenzu.SupplierCode from
(select distinct part.ProductId,part.ProductCode ,b.SupplierCode from PartsGroup pgroup
left join PricingCategory category on pgroup.code=category.GroupCode and category.grouptype=1 and FIND_IN_SET(parentcode, queryChildrenGroup(pricegroupcode))
join sparepart part on pgroup.groupcode=part.groupcode
join EnterprisePartsCost b on part.productcode = b.productcode
where category.GroupCode is null and pgroup.category=1)fenzu
join(
select distinct part.ProductId ,part.ProductCode ,b.SupplierCode from PartsGroup pgroup
join PartsGroup cgroup on pgroup.code=cgroup.parentcode and FIND_IN_SET(cgroup.parentcode, queryChildrenGroup(pricegroupcode)) and cgroup.category=2
left join PricingCategory category on cgroup.code=category.GroupCode and category.grouptype=2
join sparepart part on cgroup.groupcode=part.SpareBrandCode and part.groupcode = pgroup.groupcode
join EnterprisePartsCost b on part.productcode = b.productcode
where category.GroupCode is null) pinpai
on fenzu.ProductId=pinpai.ProductId
join (
select distinct part.ProductId,part.ProductCode ,b.SupplierCode from PartsGroup pgroup
join sparepart part on pgroup.groupcode=part.groupcode
left join PricingCategory category on part.productcode=category.GroupCode and category.grouptype=3 and FIND_IN_SET(pgroup.parentcode, queryChildrenGroup(pricegroupcode))
join EnterprisePartsCost b on part.productcode = b.productcode
where category.GroupCode is null )peijian
on fenzu.ProductId=peijian.ProductId;
-- 需要新增的销售价临时表
INSERT INTO PartsSalePrice_TMP
select part.ProductId ,part.ProductCode from sparepart_tmp part LEFT join PartsSalePrice price on part.ProductCode =price.productcode and part.SupplierCode =price.SupplierCode
WHERE price.productcode is null and;
-- 新增销售价
if(exists(select 1 from PartsSalePrice_TMP)) then
set @SalePricesql= concat('insert into ',CompanyName,'(productid,productcode,productname,suppliercode,suppliername,checkprice,',MapSalePrice,',',MapCategoryCode,')
select part.ProductId,part.ProductCode,cost.ProductName,cost.SupplierCode,cost.SupplierName,cost.CheckPrice,',SalePriceFormat,',',' ? from sparepart_tmp part join enterprisepartscost cost
on part.Productcode=cost.Productcode;');
prepare stmp from @SalePricesql;
execute stmp using @pricecode;
end if;
-- Update 销售价
set @SalePricesql= concat('update ',CompanyName,' price join sparepart_tmp part on part.ProductCode =price.productcode
join enterprisepartscost cost on cost.productcode= price.productcode and cost.suppliercode=price.suppliercode
set price.',MapSalePrice,'=',SalePriceFormat, ',' ,MapCategoryCode,'= ?');
prepare stmp from @SalePricesql;
execute stmp using @pricecode;
end if;
drop table PartsSalePrice_TMP;
drop table sparepart_tmp;
END LOOP;
-- 关闭游标
CLOSE cur;
END;$$
use security
