xdmy/doc/iolupload/importDiesel.sql

 CREATE DEFINER=`eq`@`%` PROCEDURE `importDiesel`(
  pastureIN VARCHAR (100),
  cardNumberIN VARCHAR (50),
  eqNameIN VARCHAR (50),
  eqCodeIN VARCHAR (50),
  NowTimeIN VARCHAR (255),
--   NowAmountIN DECIMAL(10,2) ,
--   priceIN DECIMAL(10,2),
  NowAmountIN VARCHAR (255) ,
  priceIN VARCHAR (255),

  empIN VARCHAR (100),
  empLIN VARCHAR (100),
  oliClassIN VARCHAR (100),
  noteIN varchar (255),
  userNameIN VARCHAR (255)
)
    SQL SECURITY INVOKER
label:BEGIN
  DECLARE employeeIdS,
  employeeLIdS,
  pastureIdS,
  measureIdS,
  meterIdS,
  lastendAmountS INT (11);
  DECLARE _lastdate,
  measureDateS,
  deptIdS,
  eqIdS,
  eqIdx,
  inputTime varchar (100);

  DECLARE measureAmountS DECIMAL (10, 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;

  #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 (NowAmountIN is null or NowAmountIN='' )
	 then
		select '0' into NowAmountIN;
	 end if ;
     #获取牧场id
  SELECT
    p.id INTO pastureIdS
  FROM
    pasture p
  WHERE p.`name` = pastureIN;

  IF (pastureIdS IS NULL)
  THEN
  SELECT
    '牧场不存在' msg;
    LEAVE label;
  END IF ;
  #获取油卡id
  SELECT
    O.id,O.eqId
    INTO measureIdS,eqIdx
  FROM
    oilcard O
  WHERE O.cardCode = cardNumberIN and O.pastureId = pastureIdS LIMIT 1;

  IF (measureIdS IS NULL)
  THEN
  SELECT
    '油卡不存在' msg;
    LEAVE label;
  END IF ;



  #获取设备id
  SELECT
    e.id,e.departmentId
    INTO eqIdS,deptIdS
  FROM
    equipment e
  WHERE e.eqCode = eqCodeIN and e.pastureId = pastureIdS and e.eqname =eqNameIN;

  IF (eqIdS IS NULL)
  THEN
  SELECT
    '设备不存在' msg;
    LEAVE label;
  END IF ;


 IF (eqIdS <> eqIdX)
  THEN
  SELECT
    '设备与油卡不匹配' msg;
    LEAVE label;
  END IF ;
 #获取录入人id
  SELECT
    e.id INTO employeeIdS
  FROM
    emp e
  WHERE e.`empname` = empIN AND e.pastureId = pastureIdS;

  IF(LENGTH(NowTimeIN)=8)
 THEN
   SELECT CONCAT('20',SUBSTRING(NowTimeIN,7,2),'-',LEFT(NowTimeIN,2),'-',SUBSTRING(NowTimeIN,4,2)) INTO NowTimeIN;
 END IF;

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





  INSERT INTO diesel (
    pastureId,
    deptId,
    eqId,
    eqCode,
    oilcardId,
    cardNumber,
    oilClass,
    oilAmount,
    nowPrice,
    price,
    empId,
    inputId,
    selTime,
    note
  )
  VALUES
    (
      pastureIdS,
      deptIdS,
      eqIdS,
      eqCodeIN,
      measureIdS,
      cardNumberIN,
      oliClassIN,
			    CAST( NowAmountIN as decimal(20,2) ),
     CAST( NowAmountIN*priceIN as decimal(10,2))  ,
--       NowAmountIN,
--       NowAmountIN*priceIN,
      priceIN,
      empLIN,
      employeeIdS,
      NowTimeIN,
      noteIN
    )
    ON DUPLICATE KEY
    UPDATE
      note = noteIN;

  #修改计量设备表中的上次值
  UPDATE
    oilcard
  SET
    lastcardAmount = cardAmount,
    cardAmount = cardAmount-CAST( NowAmountIN*priceIN as decimal(10,2))
  WHERE id = measureIdS;

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