REQUREMENTS(DataBase Logic)
  • Table1-Invoice Table
  • Table2-Credit Notes(Crdeitedamount COls)
  • Table3-Recepiept Table (ReceivedAmount COLs)
Logic For Grid Calculation:
  1. Total Amount=Sum(Invoice AMount -Tot CreditedNotes Amount) group by invoice ID Left join Invoice Table.
  2. Received=Sum(total Received) group by Invoice ID left join Invoice Table.
  3. Outstanding Amount=Total amount-Received.
  1. using System;
  2. using System.Configuration;
  3. using System.Data;
  4. using System.Linq;
  5. using System.Web;
  6. using System.Web.Security;
  7. using System.Web.UI;
  8. using System.Web.UI.HtmlControls;
  9. using System.Web.UI.WebControls;
  10. using System.Web.UI.WebControls.WebParts;
  11. using System.Xml.Linq;
  12. using System.Data.SqlClient;
  13. public partial class _Default: System.Web.UI.Page {
  14. static SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["RealConnectionString"].ConnectionString);
  15. protected void Page_Load(object sender, EventArgs e) {
  16. DataClasses2DataContext db = new DataClasses2DataContext();
  17. db.Connection.ConnectionString = con.ConnectionString;
  18. //from invoice main
  19. var invoicemain = from c in db.Customerinvoices
  20. where c.status == "closed"
  21. select c;
  22. //from Received
  23. var receivedmain = from c in db.invoicereceiveds
  24. where c.status == "posted"
  25. group c by c.invoiceid into g
  26. select new {
  27. receivedid = g.Key,
  28. receivedamount = g.Sum(x => x.recevedamount)
  29. };
  30. //total received amount group by invoiceid
  31. //sum of credited amount group by invoice id
  32. var sumcredited = from x in db.creditnotes
  33. group x by x.invoiceid
  34. into g
  35. select new {
  36. invoiceid = g.Key,
  37. creditedamount = g.Sum(x => x.creditamount)
  38. };
  39. //***** ********* List of totalinvoiceamount**** ***** ************
  40. var totinvoiceamnt = from x1 in invoicemain
  41. join x2 in sumcredited
  42. on x1.invoiceid equals x2.invoiceid
  43. into g
  44. from c in g.DefaultIfEmpty()
  45. join x3 in receivedmain
  46. on x1.invoiceid equals x3.receivedid
  47. into g1
  48. from c1 in g1.DefaultIfEmpty()
  49. select new {
  50. invoiceid = x1.invoiceid,
  51. TotalInvoiceAmount = x1.invoicamount - c.creditedamount,
  52. Recevied = c1.receivedamount,
  53. invoiceno = x1.invoiceno,
  54. invoicedate = x1.invoicedate
  55. };
  56. grid1.Datasourec(totinvoiceamnt);
  57. grid1.databind();
  58. }
  59. }