学习笔记,做个记号

DELIMITER $$

DROP PROCEDURE IF EXISTS `procInsertOrderInfoOrderList` $$
CREATE PROCEDURE `procInsertOrderInfoOrderList`(Dids TEXT,Mids TEXT,Mnums TEXT,Onums INT,Uids INT,Oremarks TEXT,Oflags INT,Nstatus INT)
BEGIN
DECLARE strDids TEXT DEFAULT '';
DECLARE iDid INT DEFAULT 0;
DECLARE strMids TEXT DEFAULT '';
DECLARE strMnums TEXT DEFAULT '';
DECLARE strOremarks TEXT DEFAULT '';
DECLARE sMids VARCHAR(20) DEFAULT '';
DECLARE iMnums INT DEFAULT 1;
DECLARE sOremarks VARCHAR(200) DEFAULT '';
DECLARE iLength INT DEFAULT 0;
DECLARE iCount INT;
DECLARE iNum INT;
DECLARE iFirst INT DEFAULT 0;
DECLARE sOrids VARCHAR(20) DEFAULT '';
DECLARE iOIid INT DEFAULT 0;
DECLARE iMflag INT DEFAULT 0;
DECLARE iMprice DECIMAL(18,2) DEFAULT 0;
DECLARE isNum INT DEFAULT 0;
DECLARE OrderInfo_flag INT DEFAULT 0;
DECLARE ii INT DEFAULT 1;
DECLARE mm INT DEFAULT 1;
DECLARE nn INT DEFAULT 1;
DECLARE iCount1 INT DEFAULT 0;
DECLARE iBLid INT DEFAULT 0;
DECLARE iBnum INT DEFAULT 0;
DECLARE iMid INT DEFAULT 0;
DECLARE sMid TEXT DEFAULT '';
DECLARE strResult TEXT DEFAULT '';
DECLARE strBnum TEXT DEFAULT '';
DECLARE sBnum TEXT DEFAULT '';
SET iCount = 1;
SET iNum = 1;
START TRANSACTION;
IF Oflags = 0 THEN -- not tc
IF Nstatus = 0 THEN -- NOT TT
SELECT COUNT(`Did`) INTO iFirst FROM `OrderInfo` WHERE ( `Did` = Dids ) AND ( `Ostatus` = 0 );
IF iFirst = 0 THEN
SELECT CONCAT(N'P',SUBSTRING(NOW()+0,1,14),SUBSTRING(RAND(),3,3)) INTO sOrids;
UPDATE `DingingTable` SET `Dstatus` = 3 WHERE ( `Did` = Dids ) AND ( `Dstatus` IN (1,2) );
INSERT INTO `OrderInfo`( `Orid`,`Did`,`Onum`,`Uid` ) VALUES( sOrids,Dids,Onums,Uids );
SELECT LAST_INSERT_ID() INTO iOIid FROM `OrderInfo` LIMIT 1;
SELECT ( LENGTH(Mids) - LENGTH(REPLACE(Mids,',','')) + 1 ) INTO iLength;
WHILE iCount <= iLength DO
SELECT ( CONCAT(Mids,',') ) INTO Mids;
SELECT ( CONCAT(Mnums,',') ) INTO Mnums;
SELECT ( CONCAT(Oremarks,',') ) INTO Oremarks;
IF( iCount = 1 ) THEN
SELECT SUBSTRING(Mids,1,POSITION(',' IN Mids) - 1) INTO sMids;
SELECT SUBSTRING(Mnums,1,POSITION(',' IN Mnums) - 1) INTO iMnums;
SELECT SUBSTRING(Oremarks,1,POSITION(',' IN Oremarks) - 1) INTO sOremarks;
SELECT funIsNum(sMids) INTO isNum;
IF( isNum = 1 ) THEN
SELECT `Mprice`,`Mflag` INTO iMprice,iMflag FROM `Menu` WHERE `Mid` = sMids;
SELECT CAST(sMids AS SIGNED) INTO iMid;
ELSE
SELECT `Mid`,`Mprice`,`Mflag` INTO iMid,iMprice,iMflag FROM `Menu` WHERE `Mname1` = LOWER(sMids);
END IF;
IF iMflag != 2 THEN
WHILE ( iMnums >= 1 ) DO
INSERT INTO `OrderList`(`OIid`,`Mid`,`Mprice`,`Uid`,`Oremark`) VALUES(iOIid,iMid,iMprice,Uids,sOremarks);
SELECT ( iMnums - 1 ) INTO iMnums;
END WHILE;
ELSE
INSERT INTO `OrderList`( `OIid`,`Mid`,`Mprice`,`Oratio`,`Uid`,`Oremark`) VALUES(iOIid,iMid,iMprice,iMnums,Uids,sOremarks);
END IF;
ELSEIF ( iCount <= iLength ) THEN
SELECT SUBSTRING(Mids,POSITION(',' IN Mids) + 1,LENGTH(Mids)) INTO strMids;
SELECT SUBSTRING(Mnums,POSITION(',' IN Mnums) + 1,LENGTH(Mnums)) INTO strMnums;
SELECT SUBSTRING(Oremarks,POSITION(',' IN Oremarks) + 1,LENGTH(Oremarks)) INTO strOremarks;
WHILE (iNum < iCount - 1) DO
SELECT SUBSTRING(strMids,POSITION(',' IN strMids) + 1,LENGTH(strMids)) INTO strMids;
SELECT SUBSTRING(strMnums,POSITION(',' IN strMnums) + 1,LENGTH(strMnums)) INTO strMnums;
SELECT SUBSTRING(strOremarks,POSITION(',' IN strOremarks) + 1,LENGTH(strOremarks)) INTO strOremarks;
SELECT (iNum + 1) INTO iNum;
END WHILE;
SET iNum = 1;
SELECT SUBSTRING(strMids,1,POSITION(',' IN strMids) - 1) INTO sMids;
SELECT SUBSTRING(strMnums,1,POSITION(',' IN strMnums) - 1) INTO iMnums;
SELECT SUBSTRING(strOremarks,1,POSITION(',' IN strOremarks) - 1) INTO sOremarks;
SELECT funIsNum(sMids) INTO isNum;
IF( isNum = 1 ) THEN
SELECT `Mprice`,`Mflag` INTO iMprice,iMflag FROM `Menu` WHERE `Mid` = sMids;
SELECT CAST(sMids AS SIGNED) INTO iMid;
ELSE
SELECT `Mid`,`Mprice`,`Mflag` INTO iMid,iMprice,iMflag FROM `Menu` WHERE `Mname1` = LOWER(sMids);
END IF;
IF iMflag <> 2 THEN
WHILE ( iMnums >= 1 ) DO
INSERT INTO `OrderList`(`OIid`,`Mid`,`Mprice`,`Uid`,`Oremark`) VALUES(iOIid,iMid,iMprice,Uids,sOremarks);
SELECT ( iMnums - 1 ) INTO iMnums;
END WHILE;
ELSE
INSERT INTO `OrderList`( `OIid`,`Mid`,`Mprice`,`Oratio`,`Uid`,`Oremark`) VALUES(iOIid,iMid,iMprice,iMnums,Uids,sOremarks);
END IF;
END IF;
SELECT (iCount + 1) INTO iCount;
END WHILE;
ELSEIF iFirst > 0 THEN -- JIA CAI
SELECT `OIid` INTO iOIid FROM `OrderInfo` WHERE ( `Did` = Dids ) AND ( `Ostatus` = 0 );
SELECT ( LENGTH(Mids) - LENGTH(REPLACE(Mids,',','')) + 1 ) INTO iLength;
SET iCount = 1;
WHILE iCount <= iLength DO
SELECT ( CONCAT(Mids,',') ) INTO Mids;
SELECT ( CONCAT(Mnums,',') ) INTO Mnums;
SELECT ( CONCAT(Oremarks,',') ) INTO Oremarks;
IF( iCount = 1 ) THEN
SELECT SUBSTRING(Mids,1,POSITION(',' IN Mids) - 1) INTO sMids;
SELECT SUBSTRING(Mnums,1,POSITION(',' IN Mnums) - 1) INTO iMnums;
SELECT SUBSTRING(Oremarks,1,POSITION(',' IN Oremarks) - 1) INTO sOremarks;
SELECT funIsNum(sMids) INTO isNum;
IF( isNum = 1 ) THEN
SELECT `Mprice`,`Mflag` INTO iMprice,iMflag FROM `Menu` WHERE `Mid` = sMids;
SELECT CAST(sMids AS SIGNED) INTO iMid;
ELSE
SELECT `Mid`,`Mprice`,`Mflag` INTO iMid,iMprice,iMflag FROM `Menu` WHERE `Mname1` = LOWER(sMids);
END IF;
IF iMflag != 2 THEN
WHILE ( iMnums >= 1 ) DO
INSERT INTO `OrderList`(`OIid`,`Mid`,`Mprice`,`Uid`,`Oremark`,`Oflag`) VALUES(iOIid,iMid,iMprice,Uids,sOremarks,1);
SELECT ( iMnums - 1 ) INTO iMnums;
END WHILE;
ELSE
INSERT INTO `OrderList`( `OIid`,`Mid`,`Mprice`,`Oratio`,`Uid`,`Oremark`,`Oflag`) VALUES(iOIid,iMid,iMprice,iMnums,Uids,sOremarks,1);
END IF;
ELSEIF ( iCount <= iLength ) THEN
SELECT SUBSTRING(Mids,POSITION(',' IN Mids) + 1,LENGTH(Mids)) INTO strMids;
SELECT SUBSTRING(Mnums,POSITION(',' IN Mnums) + 1,LENGTH(Mnums)) INTO strMnums;
SELECT SUBSTRING(Oremarks,POSITION(',' IN Oremarks) + 1,LENGTH(Oremarks)) INTO strOremarks;
WHILE (iNum < iCount - 1) DO
SELECT SUBSTRING(strMids,POSITION(',' IN strMids) + 1,LENGTH(strMids)) INTO strMids;
SELECT SUBSTRING(strMnums,POSITION(',' IN strMnums) + 1,LENGTH(strMnums)) INTO strMnums;
SELECT SUBSTRING(strOremarks,POSITION(',' IN strOremarks) + 1,LENGTH(strOremarks)) INTO strOremarks;
SELECT (iNum + 1) INTO iNum;
END WHILE;
SET iNum = 1;
SELECT SUBSTRING(strMids,1,POSITION(',' IN strMids) - 1) INTO sMids;
SELECT SUBSTRING(strMnums,1,POSITION(',' IN strMnums) - 1) INTO iMnums;
SELECT SUBSTRING(strOremarks,1,POSITION(',' IN strOremarks) - 1) INTO sOremarks;
SELECT funIsNum(sMids) INTO isNum;
IF( isNum = 1 ) THEN
SELECT `Mprice`,`Mflag` INTO iMprice,iMflag FROM `Menu` WHERE `Mid` = sMids;
SELECT CAST(sMids AS SIGNED) INTO iMid;
ELSE
SELECT `Mid`,`Mprice`,`Mflag` INTO iMid,iMprice,iMflag FROM `Menu` WHERE `Mname1` = LOWER(sMids);
END IF;
IF iMflag <> 2 THEN
WHILE ( iMnums >= 1 ) DO
INSERT INTO `OrderList`(`OIid`,`Mid`,`Mprice`,`Uid`,`Oremark`,`Oflag`) VALUES(iOIid,iMid,iMprice,Uids,sOremarks,1);
SELECT ( iMnums - 1 ) INTO iMnums;
END WHILE;
ELSE
INSERT INTO `OrderList`( `OIid`,`Mid`,`Mprice`,`Oratio`,`Uid`,`Oremark`,`Oflag`) VALUES(iOIid,iMid,iMprice,iMnums,Uids,sOremarks,1);
END IF;
END IF;
SELECT (iCount + 1) INTO iCount;
END WHILE;
END IF; -- iFirst END
ELSEIF Nstatus = 1 THEN -- TT && NOT TC -- -----------------------------------------TT NOT CT START
SET ii = 1;
SET mm = 1;
SELECT ( LENGTH(Dids) - LENGTH(TRIM(REPLACE(Dids,',',''))) + 1 ) INTO iCount1;
SELECT CONCAT(Dids,',') INTO Dids;
SELECT ( CONCAT(Mids,',') ) INTO Mids;
SELECT ( CONCAT(Mnums,',') ) INTO Mnums;
SELECT ( CONCAT(Oremarks,',') ) INTO Oremarks;
WHILE (mm <= iCount1) DO
IF mm = 1 THEN
SELECT SUBSTRING(Dids,1,POSITION(',' IN Dids) - 1) INTO iDid;
SELECT CONCAT(N'P',SUBSTRING(NOW()+0,1,14),SUBSTRING(RAND(),3,3)) INTO sOrids;
UPDATE `DingingTable` SET `Dstatus` = 3 WHERE ( `Did` = iDid ) AND ( `Dstatus` IN (1,2) );
INSERT INTO `OrderInfo`( `Orid`,`Did`,`Onum`,`Uid` ) VALUES( sOrids,iDid,Onums,Uids );
SELECT LAST_INSERT_ID() INTO iOIid FROM `OrderInfo` LIMIT 1;
SELECT ( LENGTH(Mids) - LENGTH(REPLACE(Mids,',',''))) INTO iLength;
SET iCount = 1;
WHILE iCount <= iLength DO
IF( iCount = 1 ) THEN
SELECT SUBSTRING(Mids,1,POSITION(',' IN Mids) - 1) INTO sMids;
SELECT SUBSTRING(Mnums,1,POSITION(',' IN Mnums) - 1) INTO iMnums;
SELECT SUBSTRING(Oremarks,1,POSITION(',' IN Oremarks) - 1) INTO sOremarks;
SELECT funIsNum(sMids) INTO isNum;
IF( isNum = 1 ) THEN
SELECT `Mprice`,`Mflag` INTO iMprice,iMflag FROM `Menu` WHERE `Mid` = sMids;
SELECT CAST(sMids AS SIGNED) INTO iMid;
ELSE
SELECT `Mid`,`Mprice`,`Mflag` INTO iMid,iMprice,iMflag FROM `Menu` WHERE `Mname1` = LOWER(sMids);
END IF;
IF iMflag != 2 THEN
WHILE ( iMnums >= 1 ) DO
INSERT INTO `OrderList`(`OIid`,`Mid`,`Mprice`,`Uid`,`Oremark`) VALUES(iOIid,iMid,iMprice,Uids,sOremarks);
SELECT ( iMnums - 1 ) INTO iMnums;
END WHILE;
ELSE
INSERT INTO `OrderList`( `OIid`,`Mid`,`Mprice`,`Oratio`,`Uid`,`Oremark`) VALUES(iOIid,iMid,iMprice,iMnums,Uids,sOremarks);
END IF;
ELSEIF ( iCount <= iLength ) THEN
SELECT SUBSTRING(Mids,POSITION(',' IN Mids) + 1,LENGTH(Mids)) INTO strMids;
SELECT TRIM(SUBSTRING(Mnums,POSITION(',' IN Mnums) + 1,LENGTH(Mnums))) INTO strMnums;
SELECT SUBSTRING(Oremarks,POSITION(',' IN Oremarks) + 1,LENGTH(Oremarks)) INTO strOremarks;
WHILE (iNum < iCount - 1) DO
SELECT SUBSTRING(strMids,POSITION(',' IN strMids) + 1,LENGTH(strMids)) INTO strMids;
SELECT TRIM(SUBSTRING(strMnums,POSITION(',' IN strMnums) + 1,LENGTH(strMnums))) INTO strMnums;
SELECT SUBSTRING(strOremarks,POSITION(',' IN strOremarks) + 1,LENGTH(strOremarks)) INTO strOremarks;
SELECT (iNum + 1) INTO iNum;
END WHILE;
SET iNum = 1;
SELECT SUBSTRING(strMids,1,POSITION(',' IN strMids) - 1) INTO sMids;
SELECT SUBSTRING(strMnums,1,POSITION(',' IN strMnums) - 1) INTO iMnums;
SELECT SUBSTRING(strOremarks,1,POSITION(',' IN strOremarks) - 1) INTO sOremarks;
SELECT funIsNum(sMids) INTO isNum;
IF( isNum = 1 ) THEN
SELECT `Mprice`,`Mflag` INTO iMprice,iMflag FROM `Menu` WHERE `Mid` = sMids;
SELECT CAST(sMids AS SIGNED) INTO iMid;
ELSE
SELECT `Mid`,`Mprice`,`Mflag` INTO iMid,iMprice,iMflag FROM `Menu` WHERE `Mname1` = LOWER(sMids);
END IF;
IF iMflag <> 2 THEN
WHILE ( iMnums >= 1 ) DO
INSERT INTO `OrderList`(`OIid`,`Mid`,`Mprice`,`Uid`,`Oremark`) VALUES(iOIid,iMid,iMprice,Uids,sOremarks);
SELECT ( iMnums - 1 ) INTO iMnums;
END WHILE;
ELSE
INSERT INTO `OrderList`( `OIid`,`Mid`,`Mprice`,`Oratio`,`Uid`,`Oremark`) VALUES(iOIid,iMid,iMprice,iMnums,Uids,sOremarks);
END IF;
END IF;
SELECT (iCount + 1) INTO iCount;
END WHILE;
ELSEIF mm <= iCount1 THEN
SELECT TRIM(SUBSTRING(Dids,POSITION(',' IN Dids) + 1,LENGTH(Dids))) INTO strDids;
WHILE ( ii < (mm - 1) ) DO
SELECT TRIM(SUBSTRING(strDids,POSITION(',' IN strDids) + 1,LENGTH(strDids))) INTO strDids;
SELECT (ii + 1) INTO ii;
END WHILE;
SET ii = 1;
SELECT SUBSTRING(strDids,1,POSITION(',' IN strDids) - 1) INTO iDid;
UPDATE `DingingTable` SET `Dstatus` = 3 WHERE ( `Did` = iDid ) AND ( `Dstatus` IN (1,2) );
INSERT INTO `OrderInfo`( `Orid`,`Did`,`Onum`,`Uid` ) VALUES( sOrids,iDid,Onums,Uids ); -- ============///
SELECT LAST_INSERT_ID() INTO iOIid FROM `OrderInfo` LIMIT 1;
SELECT ( LENGTH(Mids) - LENGTH(REPLACE(Mids,',',''))) INTO iLength;
SET iCount = 1;
WHILE iCount <= iLength DO
IF( iCount = 1 ) THEN
SELECT SUBSTRING(Mids,1,POSITION(',' IN Mids) - 1) INTO sMids;
SELECT SUBSTRING(Mnums,1,POSITION(',' IN Mnums) - 1) INTO iMnums;
SELECT SUBSTRING(Oremarks,1,POSITION(',' IN Oremarks) - 1) INTO sOremarks;
SELECT funIsNum(sMids) INTO isNum;
IF( isNum = 1 ) THEN
SELECT `Mprice`,`Mflag` INTO iMprice,iMflag FROM `Menu` WHERE `Mid` = sMids;
SELECT CAST(sMids AS SIGNED) INTO iMid;
ELSE
SELECT `Mid`,`Mprice`,`Mflag` INTO iMid,iMprice,iMflag FROM `Menu` WHERE `Mname1` = LOWER(sMids);
END IF;
IF iMflag != 2 THEN
WHILE ( iMnums >= 1 ) DO
INSERT INTO `OrderList`(`OIid`,`Mid`,`Mprice`,`Uid`,`Oremark`) VALUES(iOIid,iMid,iMprice,Uids,sOremarks);
SELECT ( iMnums - 1 ) INTO iMnums;
END WHILE;
ELSE
INSERT INTO `OrderList`( `OIid`,`Mid`,`Mprice`,`Oratio`,`Uid`,`Oremark`) VALUES(iOIid,iMid,iMprice,iMnums,Uids,sOremarks);
END IF;
ELSEIF ( iCount <= iLength ) THEN
SELECT SUBSTRING(Mids,POSITION(',' IN Mids) + 1,LENGTH(Mids)) INTO strMids;
SELECT TRIM(SUBSTRING(Mnums,POSITION(',' IN Mnums) + 1,LENGTH(Mnums))) INTO strMnums;
SELECT SUBSTRING(Oremarks,POSITION(',' IN Oremarks) + 1,LENGTH(Oremarks)) INTO strOremarks;
WHILE (iNum < iCount - 1) DO
SELECT SUBSTRING(strMids,POSITION(',' IN strMids) + 1,LENGTH(strMids)) INTO strMids;
SELECT TRIM(SUBSTRING(strMnums,POSITION(',' IN strMnums) + 1,LENGTH(strMnums))) INTO strMnums;
SELECT SUBSTRING(strOremarks,POSITION(',' IN strOremarks) + 1,LENGTH(strOremarks)) INTO strOremarks;
SELECT (iNum + 1) INTO iNum;
END WHILE;
SET iNum = 1;
SELECT SUBSTRING(strMids,1,POSITION(',' IN strMids) - 1) INTO sMids;
SELECT SUBSTRING(strMnums,1,POSITION(',' IN strMnums) - 1) INTO iMnums;
SELECT SUBSTRING(strOremarks,1,POSITION(',' IN strOremarks) - 1) INTO sOremarks;
SELECT funIsNum(sMids) INTO isNum;
IF( isNum = 1 ) THEN
SELECT `Mprice`,`Mflag` INTO iMprice,iMflag FROM `Menu` WHERE `Mid` = sMids;
SELECT CAST(sMids AS SIGNED) INTO iMid;
ELSE
SELECT `Mid`,`Mprice`,`Mflag` INTO iMid,iMprice,iMflag FROM `Menu` WHERE `Mname1` = LOWER(sMids);
END IF;
IF iMflag <> 2 THEN
WHILE ( iMnums >= 1 ) DO
INSERT INTO `OrderList`(`OIid`,`Mid`,`Mprice`,`Uid`,`Oremark`) VALUES(iOIid,iMid,iMprice,Uids,sOremarks);
SELECT ( iMnums - 1 ) INTO iMnums;
END WHILE;
ELSE
INSERT INTO `OrderList`( `OIid`,`Mid`,`Mprice`,`Oratio`,`Uid`,`Oremark`) VALUES(iOIid,iMid,iMprice,iMnums,Uids,sOremarks);
END IF;
END IF;
SELECT (iCount + 1) INTO iCount;
END WHILE;
END IF;
SELECT (mm + 1) INTO mm;
END WHILE;
END IF; -- Nstatus end;
ELSEIF Oflags = 1 THEN -- TC --------------------START ================================
IF Nstatus = 0 THEN -- ONE PERSON KAIDAN
SELECT Mids INTO iBLid;
SELECT CONCAT(N'T',SUBSTRING(NOW()+0,1,14),SUBSTRING(RAND(),3,3)) INTO sOrids;
UPDATE `DingingTable` SET `Dstatus` = 3 WHERE ( `Did` = Dids ) AND ( `Dstatus` IN (1,2) );
INSERT INTO `OrderInfo`( `Orid`,`Did`,`Onum`,`BLid`,`Uid`,`Oflag` ) VALUES( sOrids,Dids,Onums,iBLid,Uids,1 );
SELECT LAST_INSERT_ID() INTO iOIid FROM `OrderInfo` LIMIT 1;
-- insert OrderList CT
SELECT funGetMidBnumByBLid(iBLid) INTO strResult;
SELECT SUBSTRING(strResult,1,POSITION(':' IN strResult) - 1) INTO strMids;
SELECT SUBSTRING(strResult,POSITION(':' IN strResult) + 1,LENGTH(strResult)) INTO strBnum;
SELECT ( LENGTH(strMids) - LENGTH(REPLACE(strMids,',',''))) INTO iLength;
SET iCount1 = 1;
WHILE(iCount1 <= iLength) DO
IF iCount1 = 1 THEN
SELECT cast(SUBSTRING(strMids,1,POSITION(',' IN strMids) - 1) as SIGNED) INTO iMid;
SELECT cast(SUBSTRING(strBnum,1,POSITION(',' IN strBnum) - 1) as SIGNED) INTO iBnum;
SELECT `Mprice`,`Mflag` INTO iMprice,iMflag FROM `Menu` WHERE `Mid` = iMid;
IF iMflag <> 2 THEN
WHILE iBnum >= 1 DO
INSERT INTO `OrderList`(`OIid`,`Mid`,`Mprice`,`Uid`) VALUES(iOIid,iMid,iMprice,Uids);
SELECT ( iBnum - 1 ) INTO iBnum;
END WHILE;
ELSE
INSERT INTO `OrderList`( `OIid`,`Mid`,`Mprice`,`Oratio`,`Uid`) VALUES(iOIid,iMid,iMprice,iBnum,Uids);
END IF;
ELSEIF iCount1 <= iLength THEN
SELECT SUBSTRING(strMids,POSITION(',' IN strMids) + 1,LENGTH(strMids)) INTO sMid;
SELECT SUBSTRING(strBnum,POSITION(',' IN strBnum) + 1,LENGTH(strBnum)) INTO sBnum;
WHILE (ii < (iCount1 - 1)) DO
SELECT SUBSTRING(sMid,POSITION(',' IN sMid) + 1,LENGTH(sMid)) INTO sMid;
SELECT SUBSTRING(sBnum,POSITION(',' IN sBnum) + 1,LENGTH(sBnum)) INTO sBnum;
SELECT (ii + 1) INTO ii;
END WHILE;
SET ii = 1;
SELECT cast(SUBSTRING(sMid,1,POSITION(',' IN sMid) - 1) as SIGNED) INTO iMid;
SELECT cast(SUBSTRING(sBnum,1,POSITION(',' IN sBnum) - 1) as SIGNED) INTO iBnum;
SELECT `Mprice`,`Mflag` INTO iMprice,iMflag FROM `Menu` WHERE `Mid` = iMid;
IF iMflag <> 2 THEN
WHILE iBnum >= 1 DO
INSERT INTO `OrderList`(`OIid`,`Mid`,`Mprice`,`Uid`) VALUES(iOIid,iMid,iMprice,Uids);
SELECT ( iBnum - 1 ) INTO iBnum;
END WHILE;
ELSE
INSERT INTO `OrderList`( `OIid`,`Mid`,`Mprice`,`Oratio`,`Uid`) VALUES(iOIid,iMid,iMprice,iBnum,Uids);
END IF;
END IF;
SELECT (iCount1 + 1) INTO iCount1;
END WHILE;
ELSEIF Nstatus = 1 THEN -- TUAN TI KAIDAN
SET nn = 1;
SET mm = 1;
SELECT ( LENGTH(Dids) - LENGTH(REPLACE(Dids,',','')) + 1 ) INTO iCount;
WHILE (mm <= iCount) DO
SELECT CONCAT(Dids,',') INTO Dids;
IF mm = 1 THEN
SELECT SUBSTRING(Dids,1,POSITION(',' IN Dids) - 1) INTO iDid;
SELECT Mids INTO iBLid;
SELECT CONCAT(N'T',SUBSTRING(NOW()+0,1,14),SUBSTRING(RAND(),3,3)) INTO sOrids;
UPDATE `DingingTable` SET `Dstatus` = 3 WHERE ( `Did` = iDid ) AND ( `Dstatus` IN (1,2) );
INSERT INTO `OrderInfo`( `Orid`,`Did`,`Onum`,`BLid`,`Uid`,`Oflag` ) VALUES( sOrids,iDid,Onums,iBLid,Uids,1 );
SELECT LAST_INSERT_ID() INTO iOIid FROM `OrderInfo` LIMIT 1;
-- insert OrderList CT
SELECT funGetMidBnumByBLid(iBLid) INTO strResult;
SELECT SUBSTRING(strResult,1,POSITION(':' IN strResult) - 1) INTO strMids;
SELECT SUBSTRING(strResult,POSITION(':' IN strResult) + 1,LENGTH(strResult)) INTO strBnum;
SELECT ( LENGTH(strMids) - LENGTH(REPLACE(strMids,',',''))) INTO iLength;
SET iCount1 = 1;
WHILE(iCount1 <= iLength) DO
IF iCount1 = 1 THEN
SELECT SUBSTRING(strMids,1,POSITION(',' IN strMids) - 1) INTO iMid;
SELECT SUBSTRING(strBnum,1,POSITION(',' IN strBnum) - 1) INTO iBnum;
SELECT `Mprice`,`Mflag` INTO iMprice,iMflag FROM `Menu` WHERE `Mid` = iMid;
IF iMflag <> 2 THEN
WHILE iBnum >= 1 DO
INSERT INTO `OrderList`(`OIid`,`Mid`,`Mprice`,`Uid`) VALUES(iOIid,iMid,iMprice,Uids);
SELECT ( iBnum - 1 ) INTO iBnum;
END WHILE;
ELSE
INSERT INTO `OrderList`( `OIid`,`Mid`,`Mprice`,`Oratio`,`Uid`) VALUES(iOIid,iMid,iMprice,iBnum,Uids);
END IF;
ELSEIF iCount1 <= iLength THEN
SELECT SUBSTRING(strMids,POSITION(',' IN strMids) + 1,LENGTH(strMids)) INTO sMid;
SELECT SUBSTRING(strBnum,POSITION(',' IN strBnum) + 1,LENGTH(strBnum)) INTO sBnum;
WHILE (ii < (iCount1 - 1)) DO
SELECT SUBSTRING(sMid,POSITION(',' IN sMid) + 1,LENGTH(sMid)) INTO sMid;
SELECT SUBSTRING(sBnum,POSITION(',' IN sBnum) + 1,LENGTH(sBnum)) INTO sBnum;
SELECT (ii + 1) INTO ii;
END WHILE;
SET ii = 1;
SELECT SUBSTRING(sMid,1,POSITION(',' IN sMid) - 1) INTO iMid;
SELECT SUBSTRING(sBnum,1,POSITION(',' IN sBnum) - 1) INTO iBnum;
SELECT `Mprice`,`Mflag` INTO iMprice,iMflag FROM `Menu` WHERE `Mid` = iMid;
IF iMflag <> 2 THEN
WHILE iBnum >= 1 DO
INSERT INTO `OrderList`(`OIid`,`Mid`,`Mprice`,`Uid`) VALUES(iOIid,iMid,iMprice,Uids);
SELECT ( iBnum - 1 ) INTO iBnum;
END WHILE;
ELSE
INSERT INTO `OrderList`( `OIid`,`Mid`,`Mprice`,`Oratio`,`Uid`) VALUES(iOIid,iMid,iMprice,iBnum,Uids);
END IF;
END IF;
SELECT (iCount1 + 1) INTO iCount1;
END WHILE;
ELSEIF mm <= iCount THEN
SELECT SUBSTRING(Dids,POSITION(',' IN Dids) + 1,LENGTH(Dids)) INTO strDids;
WHILE (nn < (mm - 1)) DO
SELECT SUBSTRING(strDids,POSITION(',' IN strDids) + 1,LENGTH(strDids)) INTO strDids;
SELECT (nn + 1) INTO nn;
END WHILE;
SET nn = 1;
SELECT SUBSTRING(strDids,1,POSITION(',' IN strDids) - 1) INTO iDid;
SELECT Mids INTO iBLid;
UPDATE `DingingTable` SET `Dstatus` = 3 WHERE ( `Did` = iDid ) AND ( `Dstatus` IN (1,2) );
INSERT INTO `OrderInfo`( `Orid`,`Did`,`Onum`,`BLid`,`Uid`,`Oflag` ) VALUES( sOrids,iDid,Onums,iBLid,Uids,1 );
SELECT LAST_INSERT_ID() INTO iOIid FROM `OrderInfo` LIMIT 1;
-- insert OrderList CT
SELECT funGetMidBnumByBLid(iBLid) INTO strResult;
SELECT SUBSTRING(strResult,1,POSITION(':' IN strResult) - 1) INTO strMids;
SELECT SUBSTRING(strResult,POSITION(':' IN strResult) + 1,LENGTH(strResult)) INTO strBnum;
SELECT ( LENGTH(strMids) - LENGTH(REPLACE(strMids,',',''))) INTO iLength;
SET iCount1 = 1;
WHILE(iCount1 <= iLength) DO
IF iCount1 = 1 THEN
SELECT SUBSTRING(strMids,1,POSITION(',' IN strMids) - 1) INTO iMid;
SELECT SUBSTRING(strBnum,1,POSITION(',' IN strBnum) - 1) INTO iBnum;
SELECT `Mprice`,`Mflag` INTO iMprice,iMflag FROM `Menu` WHERE `Mid` = iMid;
IF iMflag <> 2 THEN
WHILE iBnum >= 1 DO
INSERT INTO `OrderList`(`OIid`,`Mid`,`Mprice`,`Uid`) VALUES(iOIid,iMid,iMprice,Uids);
SELECT ( iBnum - 1 ) INTO iBnum;
END WHILE;
ELSE
INSERT INTO `OrderList`( `OIid`,`Mid`,`Mprice`,`Oratio`,`Uid`) VALUES(iOIid,iMid,iMprice,iBnum,Uids);
END IF;
ELSEIF iCount1 <= iLength THEN
SELECT SUBSTRING(strMids,POSITION(',' IN strMids) + 1,LENGTH(strMids)) INTO sMid;
SELECT SUBSTRING(strBnum,POSITION(',' IN strBnum) + 1,LENGTH(strBnum)) INTO sBnum;
WHILE (ii < (iCount1 - 1)) DO
SELECT SUBSTRING(sMid,POSITION(',' IN sMid) + 1,LENGTH(sMid)) INTO sMid;
SELECT SUBSTRING(sBnum,POSITION(',' IN sBnum) + 1,LENGTH(sBnum)) INTO sBnum;
SELECT (ii + 1) INTO ii;
END WHILE;
SET ii = 1;
SELECT SUBSTRING(sMid,1,POSITION(',' IN sMid) - 1) INTO iMid;
SELECT SUBSTRING(sBnum,1,POSITION(',' IN sBnum) - 1) INTO iBnum;
SELECT `Mprice`,`Mflag` INTO iMprice,iMflag FROM `Menu` WHERE `Mid` = iMid;
IF iMflag <> 2 THEN
WHILE iBnum >= 1 DO
INSERT INTO `OrderList`(`OIid`,`Mid`,`Mprice`,`Uid`) VALUES(iOIid,iMid,iMprice,Uids);
SELECT ( iBnum - 1 ) INTO iBnum;
END WHILE;
ELSE
INSERT INTO `OrderList`( `OIid`,`Mid`,`Mprice`,`Oratio`,`Uid`) VALUES(iOIid,iMid,iMprice,iBnum,Uids);
END IF;
END IF;
SELECT (iCount1 + 1) INTO iCount1;
END WHILE;
END IF;
SELECT (mm + 1) INTO mm;
END WHILE;
END IF; -- TC:Nstatus
END IF; -- Oflags END;
COMMIT;
END $$

DELIMITER ;