func_gen_receipt_no 26
- BEGIN
-
- DECLARE pReceiptNo VARCHAR(50);
- DECLARE pBranchCode VARCHAR(50);
- DECLARE pLastReceiptNo VARCHAR(50);
- DECLARE pRunning INT;
- IF receipt_type = 4 THEN
- SET pReceiptNo = "BP";
- ELSE
- SET pReceiptNo = "CV";
- END IF;
- select b.code into pBranchCode from branches b
- where b.id = branch_id
- limit 1;
- SET pReceiptNo = CONCAT(pReceiptNo, pBranchCode);
- SET pReceiptNo = CONCAT(pReceiptNo,SUBSTRING(YEAR(receipt_date) + 543,LENGTH(YEAR(receipt_date) + 543) -1,2));
- SET pReceiptNo = CONCAT(pReceiptNo,SUBSTRING(MONTH(receipt_date),LENGTH(MONTH(receipt_date)) -1,2));
- select r.receipt_no into pLastReceiptNo from receipts r
- where r.receipt_no like CONCAT("%", pReceiptNo, "%")
- order by r.receipt_no DESC
- if pLastReceiptNo is null THEN
- set pLastReceiptNo = CONCAT(pReceiptNo, "0000");
- SET pRunning = CAST(REPLACE(pLastReceiptNo, pReceiptNo, "") AS UNSIGNED);
- SET pRunning = pRunning + 1;
- RETURN CONCAT(pReceiptNo, LPAD(pRunning, 4, "0"));
- END
|
func_gen_receipt_no 26
- BEGIN
-
- DECLARE pReceiptNo VARCHAR(50);
- DECLARE pBranchCode VARCHAR(50);
- DECLARE pLastReceiptNo VARCHAR(50);
- DECLARE pRunning INT;
- IF receipt_type = 4 THEN
- SET pReceiptNo = "BP";
- ELSE
- SET pReceiptNo = "CV";
- END IF;
- select b.code into pBranchCode from branches b
- where b.id = branch_id
- limit 1;
- SET pReceiptNo = CONCAT(pReceiptNo, pBranchCode);
- SET pReceiptNo = CONCAT(pReceiptNo,SUBSTRING(YEAR(receipt_date) + 543,LENGTH(YEAR(receipt_date) + 543) -1,2));
- SET pReceiptNo = CONCAT(pReceiptNo,SUBSTRING(MONTH(receipt_date),LENGTH(MONTH(receipt_date)) -1,2));
- select r.receipt_no into pLastReceiptNo from receipts r
- where r.receipt_no like CONCAT("%", pReceiptNo, "%")
- order by r.receipt_no DESC
- if pLastReceiptNo is null THEN
- set pLastReceiptNo = CONCAT(pReceiptNo, "0000");
- SET pRunning = CAST(REPLACE(pLastReceiptNo, pReceiptNo, "") AS UNSIGNED);
- SET pRunning = pRunning + 1;
- RETURN CONCAT(pReceiptNo, LPAD(pRunning, 4, "0"));
- END
|