Modifying SQL, the API, and WPF to Add Taxes - A TimCo Retail Manager Video

welcome to another video in the tIMCO retail manager course this course focuses on real-world application development right now we're wrapping up our shopping cart forum in this video we're going to update the database the API and the front end so you can properly calculate sales tax now if you're a patreon supporter at the $5 per month level or higher make sure you head over to get the latest source code either way make sure you subscribe to this channel so you can follow along and finally as you think of suggestions please leave them down in the comments I want to hear your thoughts in the course right now we've got a number of comments that come up that we're gonna I'm gonna bundle into one video prior to the next video that will be more of a hey let's fix this stuff video and that's all from comments that you have given me so it's really turning out great I really appreciate your suggestions keep them coming ok let's get started so we have our timco retail manager solution with our five projects here there are no changes yet because we have not yet changed anything and if we want to sync the same thing there are no changes on the server so let's go over here and get started let's start with data first so in our table which right now we are calling this SP product underscore kit which gives us the product name the description the retail price and the quantity in stock but what we have here in the sales table is we have the tax amount enter the sales table is the the total so this is the overall transaction so you have your the date the subtotal attacks and the total for the transaction and the sales detail page has how much tax was applied to this particular item so you can add those up to get our total for taxes however we don't have anywhere in the this product table but says what the if it says taxable or not this is one of the suggestions that came up that you know I'm gonna do a suggestion video but this one's a pretty big one and that is that not everything is taxable so as some things are and some things that aren't and so we need to do is we need to add in here the ability to say is this a taxable item now there may be cases in some countries or in some areas where some things are taxable at some rates and not others and those sort of things I don't think we're going to go there I think we're going to do is we're going to assume that our audience has if it's taxable as we in taxable the same rate so you know if we have a tax rate of let's just say 8% then if somebody's taxable it gets taxed at 8% not 8% for this 6% for that and 14% for this other thing now if you're following along you say well I really want to have that it's not a huge difference what I'm gonna do is have a flag here and it's gonna say is taxable in fact let's do that is taxable and it's going to be a bit and the default value will be one meaning yes is taxable so we default things are taxed we say we specifically say these things are not taxed so for you if you wanted to tax the different rates what you would do is set the percentage so this would be zero percent for not taxable and then you know 1 percent 2 percent 5 percent 4.5 whatever it is for the percentages the reason I'm not going to do that is because that well it's not much more as far as as typing goes as far as you know storage goes the difference is there is a lot more as far as updating something so if you have if the government says here's we're at a 6% tax rate and then a new law passes and of course he goes up so now it's at 7% well now you have to update every single item to 7% from 6% but they have to reevaluate as every product actually go to 7% and becomes a little confusing so that's a lot more complicated and I don't foresee us needing that for our situation so we're just gonna have an is taxable and we'll have a one static rate and it may even be in a settings file somewhere but this is a tax rate it's you know it's 7.75 well that's the tax rate okay so we had that flag and now in our product get all we need to pass that flag through as well like so and now we have our is taxable going through so that's our store procedure which means that we also need to modify when let's let's wait from it let's first do our our publish to make sure this publishes and make sure that it works we intended to so push these changes to our local database and that's been updated so now I can go a sequel server object Explorer we can look at our sequel server and what is this equals server name let's look at our publish real quick and we call the sequel server database trm data right here so now our table let's go to products and look at the data and now we see is taxable is true for all these things let's set it to false for one of them just so we have a different value so now the large toaster ovens are not taxable okay now we have our stored procedure and our table modified are actually our table and the next or procedure modify so those are taken care of now you can come over here to our our Data Manager library and look at our models we have our product model and now we're add a boolean for is taxable and just like that the API will now return is taxable now I see I didn't really change much the store procedure change what returned I didn't have to add this so when I when I added the the column to the table I didn't have to modify my my product if I didn't want to my store procedure product and I just wouldn't return to that column and then I modify my store procedure and they didn't break anything either I didn't have to modify this model since I did and since I'm calling that store procedure now my API is returning is taxable as well the front-end still doesn't care about is taxable and it won't catch the is kept taxable and that's okay so all on the way now we have as disconnected architecture where even though we add something it doesn't affect anything else until it asks for it now if you modify the name of a column or the type or remove a column yes that will affect things because the fact that they're relying on those things to be there the adding a column it's not a problem to add in one spot not the rest of course if you want it all the way through you have to pass all the way through so now it's in our API we can come over to our desktop UI library go to our model angle a product model and let's go ahead and capture that like so and I've gotten some questions about I've answered it before but I got some question of the fact that I am NOT I'm using a copy essentially of the product model from our our Data Manager library in the desktop UI library so they're the same model it looks like and the reality is these are different models because this is this one is for display it captures with the API and goes right to display the other one is for our back-end to connect to what sequel has and they can be different and that's okay and we want them to be different possibly but even to the same they're not the same thing you have the same properties they're not the same thing therefore we don't create an interface and have you know both of them point that direction we don't want any coupling between the back end and the front end so there are two separate models alright now we have the is taxable in our product model we can come to the UI and look at our view model actually let's do this let's look at our views first and look at our sales view just to kind of get our Burien where we're at right now we have a shopping cart once you add some in the cart it updates a subtotal but now you have figure out the tax for this whole cart and then from there subtotal plus tax equals total so just to refresh ourselves let's go to the view model for the sales view model and look at the subtotal look for subtotal so a subtotal right here what it's doing is it's looping through into a for each for retail price times quantity so I think we can do is do something similar with our taxable so we can basically copy and paste we have here and will change subtotal to be tax amount and rename that ever but instead of retail price times items quieting cart which we want we also want to then multiply it by our tax amount but the problem here is that's that's an additional calculation I might not always want to do and if I multiply by 0 if I put 0 in which is good but if I do my multiplication wrong and say quantity in cart times 0 that's like a 0 so we're good ok they're trying to think through make sure I'll introduce a bug here where I somehow get values in the tax that shouldn't be there so let's just say times item dot product dot is taxable well here's a problem is taxable is a true or false and we don't have a tax rate so that's not gonna work so let's let's do this here's I'm going to do just because I comment that's out I'm gonna come back to this but I don't want to forget to come back to it and then have it run this calculation to be wrong so let's go over to our helpers and see what helpers we have available to us just a password box helper that's not really helpful to me yet besides if I that's a UI helper and down here we have API helpers but that's it so let's create a new folder in our library or desktop UI library and so we go off a screen here run out create folder where I call it let's talk about something like I just helpers and inside here I'm going to add a new item it's gonna class and we'll call this the config helper now eventually we move a.net chords becomes a little easier and a little more straightforward but this is okay for now in a public class config helper we'll make it a singleton eventually we actually add to our dependency injection but for now it's just a there's a class and we'll have one method in here and what's going to do is going to read configuration information specifically it's going to read information from the app settings so we'll set the rate in here this probably won't be the place we'll keep it long-term you know dotnet core app I might put this in a key vault somewhere that is easily accessible and changeable by the same time only through permissions but in our case this will work another option is to put in a database but I think that's overkill for one setting so add key equals tax rate and the value is going to be to be a percentage so let's just say that our tax rate is eight point seven five which means eight point seven five percent which is actually zero point zero eight seven five so just keep that in mind we're thinking through what that number is so now we have a tax rate in our app config which notice is in the UI project or at access it from the library so libraries depend on the UI project having appconfig with this setting direct creates a a method that's public and then return a double so a public double get tax rate and what this will do is let say double output equal zero not output equal to zero there we go and now we're gonna read our value but it's a string right now so string rate text equals configuration manager so using system configuration configuration manager dot app settings and the app setting is going to be our tax rate once by show that's the correct one tax rate and yes it is so tax rate and then that's what all we need to do there so now it goes into tax rate as a string however when you convert this over to a number the question is if we don't have a valid number what we do because if they put in there let's just say test in that in that value well then that's not a valid tax rate we can't calculate taxes if we can't calculate taxes what do we do for an application I think the the answer is we don't if you can't calculate taxes you can't start doing sales otherwise you have a mess if you start giving things tax free then the government comes after you you're on the hook to pay those taxes so that's definitely not something we want to allow so any kind of bug should shut the system down rather than allow it to continue so the only question we have now is do we just say outputs outputs equal Wow output well if I spill it right up here there we go outputs equals in oops double dot parse tacks whoops rate tax text like so what that will do is it will an exception that if it's not a valid number however that's one of our choices the other choice is to do a try parse and then throw a more cohesive of a better message I think that's the way it go because this will just say could not parse value well what does that mean so thing we'll do is we'll do a tryparse but then a try parse what it wants to do is it's gonna return a boolean so bool is valid tax rate and I have a out output like so and what'll happen is it's gonna put in here did this work or not so if is valid tax rate I can leave that alone or I can say no let's just do equals false because only I capture the false here the true will just return output so it's false we round throwing exception otherwise we're gonna return the output because it's a valid tax rate therefore this number is now correct now it is telling it up here that it's green the reason why it's green it's saying the the variable declaration can be inlined what that means is I can say out double output and get rid of this line up here and say double and this line up here becomes redundant and now I've declared it and used it all in one right here but for our is valid tax rate equals false I can say throw new and let's look at our exception list so I have an exception and we have I believe it's let's go invalid it's invalid cast operation program index out of range format exception that's a configuration exception if there's a configuration the system that's incorrect and that's the tax rate so that's our message is the tax rate is not set up properly like so and it's obsolete interesting configuration exception is obsolete create an exception of configuration I'm not quite sure why it says the exclamation point there that doesn't seem right configuration errors exception so I'm not quite sure that's correct okay so we're saying the other one was saying do this not this and it's using the exclamation point to indicate that as all scrunched together I why is because they they deprecated the previous one so now configuration errors exception is the newer exception to use so always use always read your Greek green squigglies they mean something so in this case we did this what says here the message actually says this is deprecated meaning it's still in the dotnet framework but you shouldn't use it anymore there's a newer version or a newer way of doing things and this is in place so that old code doesn't break but we have a newer way of doing things and probably if I did this I could have had an obsolete tag okay so the suggestion is hey if you're using this then put an obsolete a above your entire method saying this method is obsolete and then create a new one to do the same work we're not gonna go there we're going to this is new code we're going to do it the right way which means using the configuration errors exception okay so now we have a method called get tax rate which reads the tax rate in from a configuration manager it tries to parse it if it's not parsable then it throws the configuration errors exception saying the tax rate was not set properly if it is a valid tax rate then we return that tax rate as a double so that's our one method that can feet helper let's go ahead and do our quick actions which is to generate an interface for this I can feel her is fine it says I can feed helper now I can come over to our bootstrapper which we really need to move us out of the bootstrap or and put it somewhere else have a configuration section just for our container that's probably something we gotta put in a list if I don't do that right now I've got a list of the things you suggest I'm an add this to my suggestion as well which is to create a class for container configuration so that's my list now all right so in here I am going to add another singleton I'm gonna add it after logged in user model because that's just easier than adding one that has a cynical at the end so daunt singleton and it's an i config helper control dot to add the user to it and then config helper like so well almost movies the right spot there we go so now I have the I can fake helper in my my bootstrappers container the dependency injection system so now my sales view model I can request that so I can have a I can fee helper config helper like so I can fake helper config helper config helper equals config helper do a control dot here to a be using pivot and now my sales view model has access to that config helper and it's a singleton because we don't need to be adding more than singleton so now down here in my tax rate I can uncomment this I can start modifying this code so double tax rate equals config helper dot get tax rate we can wrap this right here so surround with a snippet surround with if and this is going to be if item dot product dot is taxable then add this amount the retail price times the quantity and cart times the tax rate like so and we get this error the error says you can't multiply a decimal and double because they have different precision rates so what we gonna do here well this is one of those cases where even though it's a procedure and percentages are normally doubles in this case it makes sense to make it a decimal these otherwise we have to cast this to type decimal so therefore our config helper we say decimal and then here we say decimal and in our I couldn't see helper we say decimal and now we have to say decimal here too and that should be yet from that side now we can go over to our sales view model and make this a decimal and now everything is good now we have a decimal for the tax rate and we can do our multiplication and that should give us our taxable amount now this does raise some questions this is going to return a value this right here is your eternal value that is not just two decimal points meaning it's not going to be five dollars and 15 cents it's me five dollar five point one five three two eight seven six kind of thing so where we do the rounding how do we record it you know so do we do we add all these values together and then around the final amount that's a different value than if we do every value rounded to two just two decimal points that's also a different value then if we do just retail price times tax rate rounded and then multiply it by the quantity you see that the complex complexities that come in here I think I'm gonna do is I'm gonna keep the raw value the full amount and it all together and then I just display currency which is just two decimal points I think that's why I mean I go I think you ventually might look into you know how do companies do it has the government request you do it it may be different in different areas so we be careful about that but at the end of day I think that the the raw amount is fine and we can even work with that going down you know moving along to say well we actually want round it this way later so an aware of the the rounding issues but we're gonna leave it alone for now so now the tax rate should be set and if a tax rate is set and the subtotal is set then what we have is an interesting situation we have a total now which normally I would say well let's add a subtotal intact together but they're both strings and that's not terribly ideal but that's okay because it's not terribly ideal to have as whole calculation being done inside your property so let's do this let's make this into a method on its own a private method a copy and right below it gonna have a private decimal this is going to be calculate sub-total and it's gonna return sub-total well now we can just put that right into here calculate subtotal and return and to string we even just do this dot to string see if you wanted to in short it and I kind of like the idea so let's let's do that there we go return calculate subtotals tring like so and now we can do the same thing with tax so if I copy this and we do let's do it above so I can actually move both these at once I want to private decimal calculate tax calculate total tax I think it's a little more clear that we're gonna kill 8 all the tax no let's call it calculate tax because otherwise you're thinking it's a total plus the tax or something weird like that so a return tax amount and now we can take calculate tax put it down here and get rid of all the rest of this so now he has one line here which also means we can now say decimal total equals calculate subtotal plus calculate tax and then we're going to return total to string returning the the currency value of it so that's the total so now we should be look for anywhere we're updating the calling me hey you notify a property change on subtotal we should also update it for the the tax and the total so let's look for the notify property change on subtotal which is right here let's do a ctrl F actually to see if it's anywhere else in this current document remove from cart so add to cart and remove from cart so make sure we get both of those I'll copy this and I'll paste it and we'll say that this is the tax and this is the total I'll copy these come down and move from cards and make sure that these also are gonna notify of the property change now let's see if it's all works now run this guy see if I remember a password so login name and I did okay so remember that the large toaster oven I believe is not taxable let's just check that to be sure that's the one let's go to my sequel table and look at products data and it was a large toaster oven so just just be clear it's a large toaster oven is not taxable so if I add that to the cart notice no tax there is a total let's add two fluffy bath towels and that's way too much tax that's a incredible amount of tax I don't know what country were living in but that ain't right and here's the reason why we made a mistake obviously either that or were evil dictators that we want a latte so let's see if you can spot the mistake in my code if you haven't already it is in our tax calculation so I've calculate tax what's the problem well the problem is that our tax rate is eight point seven five I believe we just modified our retail price times our quantity times eight point seven to five and that ain't right because that without saying is that it's eight times the total amount so if it's if we owe $10 we owe eighty seven dollars and fifty cents in taxes but what we really owe is 8.75% of ten dollars okay there's a big difference which means you have to move the decimal point to press tax rate which essentially is just mean divided by 100 we could also multiply by 0.01 but divided by a hundred works let's try that again and see if we can't get a better more favorable amount let's drag over our window for our API and our day PF we log in and we add our large toaster oven no tax we are too fluffy bath towels and now we have a tax of five dollars and twenty four cents the subtotal 109 and a total of 115 13 if we add 15 skillets up it's not 15 skills in stock how about 10 skillets yep how about 11 skillets no but only 10 skills in stock let's just add nine there we go so now we're calculating taxes based upon our quantity in the cart and the what's taxable and multiplying all out adding all together and getting this value that doesn't look too bad at this point just 8% of whatever we have in the cart now it doesn't hurt to his calculations yourself and I just did so what I did was I said the toaster oven is $49.99 so I took this subtotal amount I subtracted $49.99 I then multiplied the value the resulting value by one point oh eight seven five that's a hundred percent plus eight point seven five percent that gives my total and I added the $49.99 back in and that gives me an overall total which came out to three hundred and fifty three dollars in point zero two one 875 so that running down to 0.02 or two cents so that is correct in our calculation so now we have a system that properly calculates sales tax based upon if something is taxable or not so our large toaster oven if I have two more so now I have three large toaster ovens isn't change my taxes at all if I keep adding more taxes don't change because toaster ovens are not taxable now one of the things has been mentioned which I do like we're going to modify this cart with is the items right now this is ugly and so we're we're going to do is we're going to make these into two row entries so we can't put a little bit of information below things like what's the the total cost for this is it taxable things like that below it so that it's a little more clear what's in the cart and what it means on the Left we'll probably have the quantity in the cart as well as is it taxable so we're kind of expand these out a little bit feel a bit more clear in how our dropdowns work okay but for now that's that's this lesson we've covered how to add taxes all the way through and get them into the system before we go though couple of things to clean up first of all let's go ahead and go to our changes and add taxable to products as the ability to tax or or not individual products close enough and then we'll add everything essentially so I can select everything here or I could've selected the highest level and there's an ad I prefer to add things manually like this for staging that way I don't accidentally add something I shouldn't I've staged them all I've had my commit message I commit the staged items and now I got a sink and I should do a fetch first to make sure it's nothing on the server there's not and I'll push my changes up to github and now we're done so that takes care of this lesson for next lesson what we're going to do is when I come back in here and do a lot of cleanup work a lot of things that you suggested little things tweaks here and there make some things a little better and we had some discussion about the cart updating properly and so we've got some things to look at they're going to hack out and then we're also gonna hopefully wire up that button so we have the be removed from Cart button and we'll make those those list items a little bit more feature-rich as Farwest shows okay it's kind of cleanup lesson next week you'll not want to miss it but in anticipation of that if as anything else you notice that you want to see change or tweaked or things I missed or things I could have done better please let me know I'll try and get that on the list for change to do for next time okay thanks for watching as always I am Tim quarry [Music] you

Channel: IAmTimCorey

Views: 10,818

Keywords: .net, C#, Visual Studio, code, programming, tutorial, course, training, how to, tim corey, C# course, C# training, C# tutorial, C# app start to finish, timco, timco retail manager, wpf, asp.net, .net core, asp.net mvc, autofac, c# solid, c# solid principles, dependency injection, tdd, unit testing, xunit, moq, webapi, sql, ssdt, sql server database tools

Id: 9icY-nTgpyo

Length: 42min 36sec (2556 seconds)

Published: Tue May 28 2019