idea maven

最新推荐文章于 2023-08-26 18:38:14 发布

446954852

最新推荐文章于 2023-08-26 18:38:14 发布

阅读量446

点赞数

分类专栏： idea

本文链接：https://blog.csdn.net/u014476019/article/details/53562538

版权

idea 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

Maven Runner -DarchetypeCatalog=internal

BEGIN
	DECLARE price_wx double;-- 微镶费wx
  DECLARE price_sh double;-- 损耗sh
  DECLARE price_sf double;-- 碎石费sf
	DECLARE inlaiedId int;-- 镶口id
	DECLARE ringarmId int;-- 戒壁id
	DECLARE materialId int;-- 材料id
	DECLARE maxInlWeight double;-- 镶口最大重
	DECLARE minInlWeight double;-- 镶口最小重
	DECLARE maxRingWeight double;-- 戒壁最大重
	DECLARE minRingWeight double;-- 戒壁最小重
	DECLARE totalInlQuantity int DEFAULT 0;-- 副石总数量
	DECLARE totalInlWeight double DEFAULT 0.000; -- 副石总重量
	DECLARE totalRingQuantity int DEFAULT 0;-- 副石总数量
	DECLARE totalRingWeight double DEFAULT 0.000; -- 副石总重量

	DECLARE currMaterVal double DEFAULT 0.000; -- 当前材料价格
	DECLARE currMaxVal double;-- 当前最大值
	DECLARE currMinVal double;-- 当前最小值
	
	DECLARE queryInlWeight VARCHAR(200);-- 计算当前镶口重量最大最小sql
	DECLARE queryRingWeight VARCHAR(200);-- 计算当前戒壁重量最大最小sql

	DECLARE flag int DEFAULT 1; -- 是否第一次(标记作用 用后马上赋值为0即false)
	DECLARE done int DEFAULT 0;
	DECLARE product_cursor CURSOR FOR select inlaied_id,ringarm_id,material_id from product;
	DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1;

	select price into price_wx from price_cost_param where status = 1 and code = 'wx' and update_by is null;
	select price into price_sh from price_cost_param where status = 1 and code = 'sh' and update_by is null;
	select price into price_sf from price_cost_param where status = 1 and code = 'sf' and update_by is null;

	set maxVal = 0;
	set minVal = 0;

	set queryInlWeight = 'select max(weight), min(weight) into @maxInlWeight, @minInlWeight from product_inlaied_detail where inlaied_id = ? and material_id = ?';
  set queryRingWeight = 'select max(weight), min(weight) into @maxRingWeight, @minRingWeight from product_ringarm_detail where ringarm_id = ? and material_id = ?';


	OPEN product_cursor;
		REPEAT
			fetch product_cursor into inlaiedId,ringarmId,materialId;
		IF not done THEN
			
			set @inlaiedId=inlaiedId,@ringarmId=ringarmId,@materialId=materialId;

			set @exeSql=queryInlWeight;
			PREPARE stmt from @exeSql;
			EXECUTE stmt USING @inlaiedId,@materialId;
			DEALLOCATE PREPARE stmt;

			set @exeSql=queryRingWeight;
			PREPARE stmt from @exeSql;
			EXECUTE stmt USING @ringarmId,@materialId;
			DEALLOCATE PREPARE stmt;

			set maxInlWeight = @maxInlWeight, minInlWeight = @minInlWeight, maxRingWeight = @maxRingWeight, minRingWeight = @minRingWeight;
			
			/* 计算最高最低镶口副石 start */
			IF EXISTS (select 1 FROM product_vice_diamond where parts_id = inlaiedId and parts_type = 1) THEN
				select sum(quantity),sum(quantity * weight) into totalInlQuantity, totalInlWeight 
						from (select quantity,weight from product_vice_diamond where parts_id = inlaiedId and parts_type = 1
						union all select 0, 0) temp;
			ELSE
				set totalInlQuantity = 0;
				set totalInlWeight = 0.000;
			END IF;
			/* 计算最高最低镶口副石 end */

			
			/* 计算最高最低戒壁副石 start */
			IF EXISTS (select 1 FROM product_vice_diamond where parts_id = ringarmId and parts_type = 2) THEN
				select sum(quantity),sum(quantity * weight) into totalRingQuantity, totalRingWeight 
						from (select quantity,weight from product_vice_diamond where parts_id = ringarmId and parts_type = 2
						union all select 0, 0) temp;
			ELSE
				set totalRingQuantity = 0;
				set totalRingWeight = 0.000;
			END IF;
			/* 计算最高最低戒壁副石 end */


			/* 计算当前材料金价 start */
		
			select (pig.price + ppg.increment) into currMaterVal
				from price_internation_gold pig inner join price_platform_gold ppg 
					on pig.id = ppg.internation_gold_id
					and pig.status = 1 and pig.update_by is null and ppg.status = 1 and ppg.update_by is null
					and pig.material_id = materialId and ppg.material_id = materialId;

			/* 计算当前材料金价 end */
		
	-- (maxInlWeight + maxRingWeight) * (100 + price_sh) / 100 * currMaterVal +
  -- (minInlWeight + minRingWeight) * (100 + price_sh) / 100 * currMaterVal +
			set currMaxVal = (maxInlWeight + maxRingWeight) * (100 + price_sh) / 100 * currMaterVal +
											  (totalInlWeight + totalRingWeight) * price_sf + (totalInlQuantity + totalRingQuantity) * price_wx;
			set currMinVal = (minInlWeight + minRingWeight) * (100 + price_sh) / 100 * currMaterVal +
											  (totalInlWeight + totalRingWeight) * price_sf + (totalInlQuantity + totalRingQuantity) * price_wx;

			IF flag THEN
				set maxVal = currMaxVal;
				set minVal = currMinVal;
				set flag = 0;
			ELSE
				IF currMaxVal >= maxVal THEN
					set maxVal = currMaxVal;
				END IF;
				IF currMinVal <= minVal THEN
					set minVal = currMinVal;
				END IF;
			END IF;

		END IF;
			
		UNTIL done END REPEAT;
	CLOSE product_cursor;
	-- set maxVal = currMaterVal;
END