· 7 years ago · Feb 26, 2019, 01:28 PM
1Invoice Number = MC1200001
2
3MC : Company name
412 : current year (Flexibel)
500001 : auto increment (Flexibel - auto increment and will reset to 00001 again if year is 2013)
6
7current year 2012 :
8
9MC1200001
10MC1200002
11MC1200003
12.....
13
14year 2013
15
16MC1300001
17MC1300002
18MC1300003
19.....
20
21<?php
22// after save to database
23$invoiceCode = 'My Company Logo'.date("y").mysql_insert_id() ;
24?>
25
26DROP TABLE IF EXISTS invoces;
27
28CREATE TABLE invoices (
29 id INT NOT NULL UNIQUE AUTO_INCREMENT
30 ,company CHAR(2)
31 ,number INT
32 ,fiscal_year INT
33 , PRIMARY KEY (company, number, fiscal_year)
34);
35
36DROP PROCEDURE IF EXISTS spCreateInvoice;
37DELIMITER $$
38
39CREATE PROCEDURE spCreateInvoice
40(
41 pCompany CHAR(2)
42 ,pFiscalYear INT
43)
44BEGIN
45 INSERT INTO invoices (
46 company, fiscal_year, number
47 ) SELECT
48 pCompany
49 , pFiscalYear
50 , 1+MAX(number)
51 FROM invoices
52 WHERE
53 fiscal_year=pFiscalYear;
54
55 SET @id = LAST_INSERT_ID();
56
57 SELECT CONCAT(i.company, i.number, i.fiscal_year) invoice_number, i.* from invoices i WHERE id = @id;
58END;
59$$
60DELIMITER ;
61
62CALL spCreateInvoice('MC', 12);
63CALL spCreateInvoice('MC', 12);
64CALL spCreateInvoice('MC', 12);
65CALL spCreateInvoice('MC', 12);
66
67CALL spCreateInvoice('MC', 13);
68CALL spCreateInvoice('MC', 13);
69CALL spCreateInvoice('MC', 13);
70CALL spCreateInvoice('MC', 13);
71
72invoice_number id company number fiscal_year
73MC012 1 MC 0 12
74
75invoice_number id company number fiscal_year
76MC112 2 MC 1 12
77
78invoice_number id company number fiscal_year
79MC212 3 MC 2 12
80
81invoice_number id company number fiscal_year
82MC312 4 MC 3 12
83
84invoice_number id company number fiscal_year
85MC013 5 MC 0 13
86
87invoice_number id company number fiscal_year
88MC113 6 MC 1 13
89
90invoice_number id company number fiscal_year
91MC213 7 MC 2 13
92
93invoice_number id company number fiscal_year
94MC313 8 MC 3 13
95
96<?php
97@mysql_connect('localhost','root','') or die('Database error!');
98echo "mysql_connect : Connected!</br>"; // if connected show 'Connected!'
99echo '</br>';
100mysql_select_db("invoice") or die('could not select');
101
102$result4 = mysql_query("SELECT * FROM `order` ORDER BY id desc") or die('could not select'); //Select last transaction
103$row4 = mysql_fetch_array($result4);
104
105$invoice4 = $row4['invoice_number'];
106$inc_number = $row4['inc_number'];
107$year = $row4['year'];
108
109//Setting Invoice Format
110$brand = 'GB';
111$cur_date = date('y'); // date('y')
112$invoice = $brand.$cur_date.'00001';
113$customer_id = rand(5487 , 9854);
114
115if($cur_date == $year) { //if current year equal to last year in transaction
116
117 if($invoice4 == $invoice && $inc_number == '1') {
118 //IF EXIST
119
120 //add inc_number
121 $inc_number_add = $inc_number + 1; // Increment by 1
122
123 //invoice_number
124 $inc = str_pad($inc_number_add, 5, '0', STR_PAD_LEFT); //Format with leading 0 eg: 00001
125 $invoice_number_db = $brand.$cur_date.$inc;
126
127 //create new order
128 mysql_query("INSERT INTO `order` (customer_id,invoice_number,inc_number,year) VALUES ($customer_id, '$invoice_number_db' ,$inc_number_add,$cur_date)")
129 or die('Cannot Insert into database!');
130 echo "<br/>Insert Success!</br>";
131
132 }
133 else {
134 //IF NOT EXIST
135 mysql_query("INSERT INTO `order` (customer_id,year) VALUES ($customer_id,$cur_date)") or die('Cannot Insert into database!');
136 echo "Insert customer_id Success!";
137
138 echo '</br>';
139
140 // Retrieve data again after create
141 $result = mysql_query("SELECT * FROM `order` WHERE customer_id='$customer_id'") or die('could not select');
142 $row = mysql_fetch_array($result);
143
144 //var
145 $id_val = $row['id'];
146 $inc_number_add = $inc_number + 1; // Increment by 1
147 $inc = str_pad($inc_number_add, 5, '0', STR_PAD_LEFT); //Format with leading 0 eg: 00001
148 $invoice = $brand.$cur_date.$inc;
149
150 //Update invoice
151 mysql_query("UPDATE `order` SET invoice_number='$invoice' , inc_number='$inc_number_add' WHERE customer_id=$customer_id") or die('error 89');
152 echo "</br>UPDATE invoice_number Success!</br>";
153 }
154
155}
156 else {
157
158 //IF TODAY IS NEW YEAR RESET INVOICE NUMBER
159 mysql_query("INSERT INTO `order` (customer_id,invoice_number,inc_number,year) VALUES ($customer_id, '$invoice' , '1' ,$cur_date)")
160 or die('Cannot Insert into database!');
161 echo "<br/>Insert customer_id Success!</br>";
162
163 }
164
165 echo "<br/><br/>FINAL OUTPUT <br/>";
166 $result_3 = mysql_query("SELECT * FROM `order` WHERE customer_id='$customer_id' ") or die('could not select');
167 $row_3 = mysql_fetch_array($result_3);
168
169 $id3 = $row_3['id'];
170 $customer_id3 = $row_3['customer_id'];
171 $inc_number = $row_3['inc_number'];
172 $invoice3 = $row_3['invoice_number'];
173
174 echo 'id '.$id3.' - ';
175 echo 'customer_id '.$customer_id3.' - ';
176 echo 'inc_number '.$inc_number.' - ';
177 echo 'invoice '.$invoice3. '<br/>';
178
179?>