xdmy/doc/elec/insert_electricity.sql

CREATE DEFINER=`eq`@`%` PROCEDURE `insert_electricity`(
  pastureIN VARCHAR (100),
  meterNameIN VARCHAR (50),
  meterNumberIN VARCHAR (50),
  lastTimeIN VARCHAR (100),
  lastAmountINs VARCHAR (50),
  NowTimeIN VARCHAR (255),
  useAmounts VARCHAR (255),
  NowAmountINs VARCHAR(50) ,
  priceIN DECIMAL(10,2),
  empIN VARCHAR (100),
  noteIN VARCHAR (255),
  jwt_username VARCHAR (255)
)
    SQL SECURITY INVOKER
label:BEGIN
  DECLARE employeeIdS,
  pastureIdS,
  measureIdS,
  meterIdS,
  lastendAmountS INT (11);
  DECLARE _lastdate,measureAmountS,lastAmountIN,
  measureDateS,
  inputTime,rates varchar (100);

 DECLARE useAmount,NowAmountIN DECIMAL (15, 2);
 DECLARE msg1 TEXT;
   DECLARE code1 CHAR(5) DEFAULT '00000';
   DECLARE t_error INTEGER DEFAULT 0;
  DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
  BEGIN
      -- 获取异常code,异常信息
      GET DIAGNOSTICS CONDITION 1
      code1 = RETURNED_SQLSTATE, msg1 = MESSAGE_TEXT;
   END;

  #开启事务
   START TRANSACTION;
     #日期格式化
   IF(LENGTH(NowTimeIN)=8)
 THEN
   SELECT CONCAT('20',SUBSTRING(NowTimeIN,7,2),'-',LEFT(NowTimeIN,2),'-',SUBSTRING(NowTimeIN,4,2)) INTO NowTimeIN;
 END IF;
 IF(LENGTH(lastTimeIN)=8)
   THEN
   SELECT CONCAT('20',SUBSTRING(lastTimeIN,7,2),'-',LEFT(lastTimeIN,2),'-',SUBSTRING(lastTimeIN,4,2)) INTO lastTimeIN;
  END IF;
  #SELECT CONCAT('20',SUBSTRING(NowTimeIN,7,2),'-',LEFT(NowTimeIN,2),'-',SUBSTRING(NowTimeIN,4,2)) INTO NowTimeIN;
  #SELECT CONCAT('20',SUBSTRING(lastTimeIN,7,2),'-',LEFT(lastTimeIN,2),'-',SUBSTRING(lastTimeIN,4,2)) INTO lastTimeIN;

  IF (useAmounts='' )
   THEN
   SELECT '用量必须输入' msg;
    LEAVE label;
   END IF ;


  #判断输入的数据

  IF (DATE_FORMAT(NowTimeIN, '%Y-%m-%d') > DATE_FORMAT(NOW(), '%Y-%m-%d') )
  THEN
  SELECT
    '本次抄表日期不可大于当前日期' msg;
    LEAVE label;
  END IF ;


  #获取牧场id
  SELECT
    p.id INTO pastureIdS
  FROM
    pasture p
  WHERE p.`name` = pastureIN LIMIT 1;

  #获取计量表id
  SELECT
    m.id , DATE_FORMAT(m.endDate, '%Y-%m-%d'),m.endAmount,m.Multiple
    INTO measureIdS,measureDateS,measureAmountS,rates
  FROM
    measure m
    left JOIN electricity w ON w.measureId = m.id
  WHERE m.meterNumber = meterNumberIN AND m.meterType = '电表' and m.meterName=meterNameIN and m.pastureId=pastureIdS AND m.isDel = '0' LIMIT 1;

  IF (measureIdS IS NULL)
  THEN
  SELECT
    '计量表不存在' msg;
    LEAVE label;
  END IF ;


 #获取录入人id
  SELECT
    e.id INTO employeeIdS
  FROM
    emp e
  WHERE e.`empname` = empIN limit 1;





  INSERT INTO electricity (
    elecNumber,
    elecName,
    elecConsumption,
    price,
    sumPrice,
    employeId,
    `DATE`,
    note,
    pastureId,
    measureId
  )
  VALUES
    (
      meterNumberIN,
      meterNameIN,
      useAmounts,
      priceIN,
      (useAmounts * priceIN*rates),
      employeeIdS,
      NowTimeIN,
      noteIN,
      pastureIdS,
      measureIdS
    )
    ON DUPLICATE KEY
    UPDATE
      elecConsumption = useAmounts, sumPrice= (useAmounts * priceIN*rates),note=noteIN,
      employeId = employeeIdS,price=priceIN;

      /*SELECT id INTO meterIdS FROM electricity WHERE measureId = measureIdS AND `DATE` = NowTimeIN limit 1;

   if (meterIdS is null)
   then
   SELECT
    '插入水表记录失败，请重试' msg;
    LEAVE label;
   end if; */
  #修改计量设备表中的上次值
  UPDATE
    measure
  SET
    lastDate = endDate,
    lastAmount = endAmount,
    endDate = NowTimeIN,
    endAmount = NowAmountIN,
    lastRecodeId = meterIdS
  WHERE id = measureIdS;

  IF code1 = '00000' THEN
    COMMIT;
   SELECT
    'success' msg;
  ELSE
     ROLLBACK;
   SELECT
    msg1 msg;
  END IF;
END