· 7 years ago · Jan 18, 2019, 04:34 PM
1 private void frmBills_Load(object sender, EventArgs e)
2 {
3 try
4 {
5 sqlDataManager.LoadBillData("SELECT * FROM Bill");
6 sqlDataManager.LoadChildData("SELECT * FROM Child WHERE Status = 'A'");
7 sqlDataManager.LoadClientData();
8 FillCombo(cbxChild, sqlDataManager.dtChild, "ChildID");
9 //Need to add data to table before I can sort. Sort is commented out for now.
10 //dv.Sort = "DateBill ASC";
11 dgvBillRecords.DataSource = sqlDataManager.dtBill;
12
13
14 //Autogenerate bills depending on plan
15 string plan = "";
16 DataRow newBill = sqlDataManager.dtBill.NewRow();
17 bool generated = false;
18
19 foreach (DataRow r in sqlDataManager.dtChild.Rows)
20 {
21 /*What I want to do: Autogenerate one bill for each client.
22 ///Searching Bill won't work well for new users as they won't have a previous bill to identify them by.
23 ///Using Child might work better.
24 What do I have to do for that? Find the cost and the date for each client.
25 For each Child ID, get the last bill's date.
26 Get the Child ID's payment plan.
27 If it's Monthly and today is one month after the last bill's date, add 1 month to the date.
28 If it's Weekly and today is one week after the last bill's date, add 1 week to the date.
29 If it's 6-Monthly and today is 6 months after the last bill's date, add 6 months to the date.
30 For the cost, add up the amount of sessions per week the child is in through Daily Attendance.
31 Thus, cost is found for Weekly. Multiply by 4 for Monthly, multiply by 24 for 6-Monthly.
32 */
33
34
35 int noSessions = 0;
36
37
38 //Get number of sessions a child is in crèche for
39 //I.E. count number of AM/PM bits that have a value of 1.
40 foreach (DataColumn c in r.Table.Columns)
41 {
42 if (c.ColumnName.Contains("AM") || c.ColumnName.Contains("PM"))
43 {
44 if ((bool)r[c] == true)
45 {
46 noSessions++;
47 }
48 }
49 }
50
51 //Get payment plan for parent of this child
52 //Doesn't seem to work if the Bills table is empty.
53 //Maybe the problem doesn't come from here. Either way, no bills get generated if the table is empty.
54 foreach (DataRow r2 in sqlDataManager.dtClient.Rows)
55 {
56 if ((int)r2["ClientID"]==(int)r["ClientID"])
57 {
58 plan = r2["PaymentPlan"].ToString();
59 }
60 }
61
62
63 //Begin generating next bill
64 //ChildID - same as ChildID of bill currently being evaluated in parent Foreach
65 newBill["ChildID"] = r["ChildID"];
66
67 //DateBill and TotalExVat
68 //Should probably ratify the cost of a session with GN - right now it's £3 ex vat.
69 //Also for the sake of simplicity, I take 1 month to be 4.5 weeks and 6 months to be 27 weeks.
70 //Need to ensure that the Total values are all Decimals in the SQL table.
71 decimal totalIncVat = 0;
72 newBill["TotalExVat"] = 3 * noSessions;
73 Math.Round((decimal)newBill["TotalExVat"], 2);
74
75 newBill["Vat"] = 0.2m * (decimal)newBill["TotalExVat"];
76 Math.Round((decimal)newBill["Vat"], 2);
77 //To prevent a casting error, Vat is converted to string, the "M" is removed from the end and it is re-cast as a decimal.
78 string strVat = newBill["Vat"].ToString();
79 strVat = strVat.TrimEnd('M');
80 decimal vat = Convert.ToDecimal(strVat);
81 //Might need to find a way to specify this.
82 newBill["Vat"] = vat;
83
84 //This line throws a 'Specified cast is not valid' sometimes.
85 totalIncVat = (decimal)newBill["Vat"] + (decimal)newBill["TotalExVat"];
86 Math.Round(totalIncVat, 2);
87 newBill["TotalIncVat"] = totalIncVat;
88
89 //This bool ensures that bills are not generated unnecessarily
90 bool requireNewBill = true;
91 //If there exists a previous bill for this child, generate a bill corresponding to this previous bill.
92 foreach (DataRow r2 in sqlDataManager.dtBill.Rows)
93 {
94 if ((int)r["ChildID"] == (int)r2["ChildID"])
95 {
96 DateTime previousDateBill = (DateTime)r2["DateBill"];
97 //Only create the DateBill if today is that date.
98 switch (plan)
99 {
100 case "W":
101 if (previousDateBill.AddDays(7).Date == DateTime.Today.Date)
102 {
103 newBill["DateBill"] = previousDateBill.AddDays(7);
104 generated = true;
105 }
106 else
107 {
108 requireNewBill = false;
109 }
110 break;
111
112 case "M":
113 if (previousDateBill.AddMonths(1).Date == DateTime.Today.Date)
114 {
115 newBill["DateBill"] = previousDateBill.AddMonths(1);
116 newBill["TotalExVat"] = (decimal)newBill["TotalExVat"] * 4.5m;
117 generated = true;
118 }
119 else
120 {
121 requireNewBill = false;
122 }
123
124 break;
125
126 case "6":
127 if (previousDateBill.AddMonths(6).Date == DateTime.Today.Date)
128 {
129 newBill["DateBill"] = previousDateBill.AddMonths(6);
130 newBill["TotalExVat"] = (decimal)newBill["TotalExVat"] * 4.5m * 6m;
131 generated = true;
132 }
133 else
134 {
135 requireNewBill = false;
136 }
137 break;
138 }
139
140 }
141
142 }
143
144 if (generated == false && requireNewBill == true)
145 {
146 newBill["DateBill"] = DateTime.Now;
147 newBill["Paid"] = false;
148 generated = true;
149 }
150
151 //Now that new bills have been entirely generated, add them to the loop. Only if a new bill is actually generated though.
152 //Keep in mind that it is unnecessary to update the table once after every entry, so this code is after the Foreach.
153 if (generated == true)
154 {
155 newBill["Paid"] = false;
156 sqlDataManager.dtBill.Rows.Add(newBill);
157 sqlDataManager.daBill.Update(sqlDataManager.dsFullDataSet.Tables["Bill"]);
158 }
159 }
160 }
161 catch (Exception ex)
162 {
163 MessageBox.Show("Unable to load Bills table \n" + ex.Message);
164 }
165 }