Easily Create Invoice HTML for Procurement with airSlate SignNow

Invoice html for Procurement

hi friends in the part one of the invoice generation demo we have seen how this particular invoice is generated and on confirmation the bill is generated and in the part two we have seen how the product and the prices were taken from the database and this is displayed here in the example here suppose I have changed the product the price is picked up from the table from the from our master table so I am changing this to another so again the price has changed so based on the what product I am selecting power you need whatever I am selecting the price keeps on changing so I need not have to feed manually and the quantity only I will feed it let's say 6 and I will add to the record so ingly this one was working we have seen all this ah let me just add one more and price is there let's say Quantity is 5 now add so all these total tax everything is generated by taking price and the whatever the product I selected but again once I confirm it the bill is generated and all this cell details stores in the database this is a what you have developed in the part two this is the our main script and it collected the data from our uh invoice products thing which connects to the database and see this is the query we have used and here is our product table where three columns we have taken and here before that another query also same thing all the three columns data we have taken and populated our product and based on the product the price is also selected now we'll try to change this query will change this query in such a way this particular one that it also checks some of the whatever material we received and what we sell and the balance stock is beyond the threshold value then only the product will be available that query will change let's see the tables these three tables will use Here There and before we are taking data from this only product table so where the product and the price were taken so now what you will do we are also receiving the product so for that every time you receive we enter it here so there will be multiple products in in this table so monitor will appear more you see it's appearing two times and this price is the purchase price and this gives us the quantity so what happen against each product will get a total quantity we have received similarly this right side one is the based on what you have sold so it's the invoice what details so every product will have here also will appear multiple times based on the how many times we have sold the particular product so any product here multiple and the quantity we have sold against that product is available so some of this also will tell that how much is sold so the receive and receive is taken from here whatever is sold is taken from here the balance is the stock position on whatever is available now in this product now once the stock is available we'll put a threshold of 10 say it is example 10 so minimum stock suppose 10 we have to maintain anything beyond 10 will be available for hours to invoice or sell it in our counter so anything less than 10 will by default it will be filtered out so it will not be available so what you are doing in our final report here we are taking those products for which the stock value or this column value is more than 10 then whatever qualifies we have also taking the price here so the price we are taking from here this is the sale price this is manually decided because this price is not same as the purchase price here so these are different and this price the stock more than 10 and those products these three are available for our application to use it for our easy understanding what you required is the last one the sixth one but will break it into multiple steps so we'll start with small small parts of it so this is the list of products received with total quantity this is from the received side similarly other points are also some are concerned to our whatever we have sold how much what are the quantity we have sold then we will divide we will subtract these two and get the what the stock is available then we will apply a filter what are the Beyond 10 and then collect the price and that will be using visit this place and you will get all the queries and questions and all and you can visit this SQL dump also click this and copy the entire SQL dump to create your sample table we are inside our PHP my admin here you have installed all the our tables with some sample data now we'll write the query one by one we'll try first we will say list of products received with total quantity this is we are working on a single table plus two product receive so for that we'll saying select select a DOT P ID is the product ID now we'll say a h a DOT product from this the two columns we are taking now whatever the quantity we have received sum we are doing a DOT q t y is the quantity we against each product one or what are the quantities we are received now this we are saying as receive now from from now plus two product receive this is the table name and then this we are using Alias as a because all where you can see a DOT p i d a DOT product so here we are saying that sorry I think I have to write a and here I am saying use it as a this table group y because now everywhere o u p group you have to group it across the all the product ID so Group by PID sorry I had to give a DOT p i d why I am because it's a single table I can I can I need not use a also here as Alias of that but because this will be a part of a complex join query so I am using it for the future it will be useful now let me just run this and see how it reads so here it is you can see Mouse we have received 10 keyboards as eight one time only this in this column though in the main table we have multiple Mouse multiple keyboards and all here it is just it sums up and what is the total we received it is given here now let's come to our cell side and find out the total quantities sold and the price against each product so now purchase is over now let's come to the selling site so we'll just up to this select PID then product then sum I am just summing it up summing qty the quantity column some quantity column adds I am giving here I'll write it uppercase because it's a part of the query I am giving a name as quantity now here I am summing up the price also so but I will use the format for that so I am saying before that sum and here I am saying what I will sum is price price is the column so price and here I will use the format so I will say format what I am saying is use why after summing use two places decimal don't go use more than that now this adds total price from because it is our cell table so we'll say plus 2 invoice detail so is row wise each item cell is stored in this table so that is why we are taking but now we have to use Group by Group by again each product so product ID is the identify each product so we will say group y P ID or product ID now let's run this and see ah plus to invoice detail okay p l u s i missed plus two invoice detail now let me run this here it is so I got it against each product what is the quantity and what is the total price I am getting here though I will not be using here the total price because some other query memory which product what is the turnover contribution of product and all that time here what I am more interested is what is the total quantity cell of how many keyboards I have sold how many monitors I have sold all those this figure I want because this is required for my balance stock calculation though I will not be using much but let's find out against each invoice what are the total price and quantity sold so this again we are coming back to our receive site so we'll save our select again based on invoice ID because in EnV ID invoice ID now sum you are saying quantity qty as quantity sold now again we are using format directly I am writing because we know how to write the format because what the output is I wanted to use it up to two decimal points sum inside this actually price now comma 2. so this is the it will sum it up then use format to round it up up to two decimal places as total price total price from plus two invoice detail plus two invoice detail Group by this is this time we are grouping across all the invoice so because every invoice what is the total price what is the total quantity sold so that way so Group by invoice ID or in INB ID sorry sorry here lower case okay let's just run this and see here it is so but but it will not be using in our main query this is the output because against each invoice means each bill what is the total quantity sold and what is the total price now we'll start using join to get data from both the tables find out the total quantity we received find out what is the we have sold then generate the stock value so for that we'll write a complex little complex left join query so for that select a DOT PID now a DOT product now sum a DOT q t y or quantity as received now receive is there now as I am telling how what is the total uh sold what is what is how much is the sold so for that we'll say B dot sold B dot sold again I am writing 0 sorry but before that I will use a function Kola C because that time when the null value is there in place of null it should come zero because otherwise the minus one won't come so for that Co a CO less yes within that and what should be when the null value is there what it should be I am telling use it as treat it as 0. so so minus when I give the null value will be simply shifted with zero value as sold uh got it now as sold then I am saying sum bracket start sum now a quantity a DOT q t y so sum of the quantity minus then B dot sold again the same one I am using it 0 I am giving it closing the bracket as stock now here you see from okay this point it is this at this point we get the stock value from plus to product underscore receive as a here again sorry sorry receive a receive a left join now here next line I will go now you just see this a what it does is here in this I am getting the stock value so what I am getting here is first a I am giving the quantity then b b so far I am not defined whatever b b is the other the cell table I am getting this is a two I am my subtracting here the sum I am getting here then the sum of the whatever I have sold that I am getting when I am these two I am subtracting I am getting the stock value but here if something is null then I am selling treated as zero so now what I have so far I have not written what is B so now I left join now I will come I will Define the B table or the B is the right side my cell table is there so for that I will within the command I will write it so select select PID but product ID product now I will say sum quantity so sum of quantity because I I have to add sold because this is what I am going to use it there as sold from plus to invoice detail because this is what where my cell data is stored Group by Group by PID now here I will close it and say here I say it and this I am giving it as B got it this B I have used it here now next line now I will join every joint required say column here so on a DOT p i d first stable product ID equal to B dot p i d ID now that's that the point but what I will say again now I have to group it so Gro u p Group by a DOT P ID now this is my total query till I get the stock value so far I have not allowed allowed any filter let me just copy this one so I can need not write it fine now let me just run this one then we will try now here you see we have got the all the product from the thing and whatever the sum of the receipt this is the sold and this is the balance of it so stock stock is receive minus sold so here that is the that is what this query is talking so let let's just see here select this one a product a is my receive side and here I am the stock I am calculating as stock here it is a quantity minus B quantity so b b is the our invoice detail side here it is so this value returns me the uh all the details whatever the all the products what are the how many are purchased how many are sold and what is the stock price what is the stock quantity now we'll use this suggests as query and restrict the stocks which are more than 10 only will from this will pick up instead of giving a full list we'll say whatever for that we'll treat this as a table so I am just putting a two entire query within two within the brackets now here I am writing my another query assuming that this is the table so I am saying and finally whatever query we get will take that and integrate into our application to generate our invoice that drop down with matching to the query requirement so select here I am writing select PID then product now all these we are with the names you can see here inside that we have used up we are picking up limited columns from this receive sorry receive then we'll say sold then we are saying stock from from this table so it comes up to this now we are giving ads t this table we are giving the name where stock is greater than 10. so now this is the query which will give us the more than 10 whatever the stocks are available that will say but one more step is left let's see now you see this only these two products qualifies so because the stock is 12 and 19. even you can see here I'll just I copied that time I will paste the again the same query what I will do I will instead of 10 I will make it say 2. so more more products should qualify now you can see here now more products have come up because now the threshold value is changed now the file and final one this one will slightly change this one now I will make it as T1 and here what you will do that means one more table will link because we need the price from the product master table so T1 will say that uh our T1 is this one and wire stock now as T1 here will break it will use the word left join left join against which you to left join left joint plus two product plus two product as T2 as T2 on now where it will be as T2 and Y which column the join will be T1 dot p i d equal to T2 dot p i d now where stock is this will remain same now this uh stock portion remains same but here will give the our column names where from what you are taking now T1 here I am saying sorry T1 dot Pi dot PID we are taken Now product product is anyway it's a unique here receive uh receive is again unique sold is also they are stock T2 price price will include here T2 price that means we are picking up the price from the product table because product table is plus two product the master table and in fact this also we can take it from T2 and here because here it is p name now it is not product so we will can say T2 dot P name product name then rest of the things now this is our query so let's save this and run here you can see these are the products because we have I think reduced the threshold we kept it as two here so it is more products are coming let's just edit this and increase the threshold to 10. so let's go fine here it is this two only are qualified so this is our final query what we got it now this query will carry to our kinter application now in this place of our previous query we will use our new query what we have developed now I am just selected this and pasting the new query here so there are multi lines so I will be giving the our line string continuation so I am this is treated as a continuous string now let's just see that so all these line breaks I am escaping now let's okay now fine this is now a single query with the and the same the entire query is used here now let's just save this and go to our main file and see how it is here it is now let me just select a pen drive and here is the product okay one mistake is because we don't require so many columns are not required we want PID product name and these three things are not required receive sold and stock these three columns are not required this and the price is required so three columns are required product ID product name and the price let me save this now I'll go here run this so here you can see I'll select pen drive so price has come up now I will change it to monitor the price has come up okay let me just add it to this five now add so I have added now let me go to pen drive pen drive I will say five then added it so okay but one thing now this let me just because I am I am having two products now I don't have multiple products let me just decrease the threshold I close this let me go here this stock availability I am instead of 10 I am keeping it 2. so Lord this some more products should come I'll come back to our main file run it so I should get more products now available so all these were earlier not available now it is available because I have reduced the threshold for the stock that's all friend so this is a series of this tutorials we are developing now you have we have seen how the product dynamically can come to our invoice section so we can whatever is available only we can bill it now let's another thing is how when you receive the products how you will enter into the system so for that it should go to the receipt table so for that we need interface again so that will prepare it in our next so where we can add our products to our receive table so suppose a new product comes up then that is not there already in our master table then that will be included one product ID will be picked up and that will be included in the receipt table so that you will learn next so I'll request you please subscribe to our Channel and press the Bell icon so you'll be notified as and when you add new new tutorials we frequently do that also and if you have any doubt suggestion questions use the comment section below to post it please share this with your friends and thank you for watching