CODEWITHSUNDEEP
phpmysql
user2669924
user2669924

Reputation: 85

Resetting invoice number automatically when fiscal year changes

I have a script where I generate invoices. I have a field invoice_no in db table invoice (format:INV-yyyymm-0001, here 0001 should increment for every invoice until the next fiscal year), which should reset to 001 after fiscal year changes. eg: If Invoice number is INV-201903-2111 , after 31st March 2019 it should automatically change to INV-201904-0001 instead of INV-201904-2112

I have stored Starting and Ending of Fiscal year in a table with Fyear, fsYear, feYear. Whenever I generate a new invoice, it has to check current year with Fyear and then starting and ending dates, if the current date is withing fsYear and feYear then its has to reset to 001 and then increment till the end of that fiscal year. How exactly I can implement it?

Here is the table

INSERT INTO `fyear` (`id`, `fyYear`, `fyStart`, `fyEnd`) VALUES
(1, 2019, '2019-04-01', '2020-03-31'),
(2, 2020, '2020-04-01', '2021-03-31');

I tried creating a function to get starting and ending date $cdate = date('Y-m-d'); $cyear = date("Y");

function GetFdate($Fyear, $Fstdate, $feddate)
{
$m1 = mysqli_query($con, "SELECT * FROM fyear WHERE fyYear='".$cyear."'");
$m2 = mysqli_fetch_array($m1);

$sYear = $m2['fyStart'];
$eYear = $m2['fyEnd'];  
}

But not getting how to increment the invoice number and then reset.

Here's my schema:

CREATE TABLE `sales_invoice` (
  `invoice_id` int(10) NOT NULL,
  `invoice_no` varchar(50) NOT NULL,
  `order_id` varchar(50) NOT NULL,
  `customer` int(10) NOT NULL,
  `contact_person` int(10) NOT NULL,
  `validity` int(10) NOT NULL,
  `payment` varchar(100) NOT NULL,
  `date_invoiced` date NOT NULL,
  `status` varchar(20) NOT NULL,
  `user_id` int(20) NOT NULL,
  `shipping_address` varchar(100) NOT NULL,
  `billing_address` varchar(100) NOT NULL,
  `reference` varchar(255) NOT NULL,
  `is_complete` varchar(10) NOT NULL DEFAULT 'No'
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

ALTER TABLE `sales_invoice`
  ADD PRIMARY KEY (`invoice_id`);
ALTER TABLE `sales_invoice`
  MODIFY `invoice_id` int(10) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=1;  

  INSERT INTO `sales_invoice` (`invoice_id`, `invoice_no`, `order_id`, `customer`, `contact_person`, `validity`, `payment`, `date_invoiced`, `status`, `user_id`, `shipping_address`, `billing_address`, `reference`, `is_complete`) VALUES
(1, 'INV-201903-2110', 'SO006', 91, 6, 5, 'Immediate', '2019-03-19', 'Paid', 1, '1', 'Company123', 'Webmail', 'No'),
(2, 'INV-201903-2111', 'SO007', 136, 4, 0, 'Immediate', '2019-03-30', 'Open', 1, '1', 'Company456', 'Google mail', 'No');

Upvotes: 2

Views: 1546

Answers (1)

Strawberry
Strawberry

Reputation: 33945

Consider the following:

DROP TABLE IF EXISTS sales_invoice;

CREATE TABLE sales_invoice 
(invoice_id SERIAL PRIMARY KEY
,invoice_no VARCHAR(50) NOT NULL UNIQUE
,date_invoiced date NOT NULL
);

SET @dt = CURDATE();
-- SET @dt = '2019-03-15';


INSERT INTO sales_invoice (invoice_no,date_invoiced)
SELECT CONCAT_WS( '-'
                , 'INV'
                , DATE_FORMAT(@dt,'%Y%m')
                , COALESCE(LPAD(
                      CASE WHEN @dt > DATE_FORMAT(@dt,'%Y-04-01') 
                           THEN SUM(date_invoiced >  DATE_FORMAT(@dt,'%Y-04-01')) 
                           ELSE SUM(date_invoiced BETWEEN DATE_FORMAT(@dt,'%Y-04-01')-INTERVAL 1 YEAR AND DATE_FORMAT(@dt,'%Y-04-01'))
                      END +1,4,0
                      ),LPAD(1,4,0))
                )
     , @dt FROM sales_invoice;

SELECT * FROM sales_invoice;
+------------+-----------------+---------------+
| invoice_id | invoice_no      | date_invoiced |
+------------+-----------------+---------------+
|          1 | INV-201906-0001 | 2019-06-10    |
+------------+-----------------+---------------+

Upvotes: 1

Related Questions