source: db_tsh/Controllers/HomeController.cs@ 705d6f5

main
Last change on this file since 705d6f5 was 705d6f5, checked in by ardit <ardit@…>, 2 days ago

Commiting all files of project - 20250224
  • Property mode set to 100644
File size: 42.3 KB
Line 
1using db_tsh.Models;
2using Microsoft.AspNetCore.Authorization;
3using Microsoft.AspNetCore.Http;
4using Microsoft.AspNetCore.Mvc;
5using Microsoft.AspNetCore.Mvc.Rendering;
6using Microsoft.Extensions.Configuration;
7using Microsoft.Extensions.Logging;
8using Npgsql;
9using OfficeOpenXml;
10using Renci.SshNet;
11using System;
12using System.Collections.Generic;
13using System.Data;
14using System.Data.SqlClient;
15using System.Diagnostics;
16using System.IO;
17using System.Linq;
18using System.Security.Claims;
19using System.Threading.Tasks;
20
21namespace db_tsh.Controllers
22{
23 //[Authorize]
24 public class HomeController : Controller
25 {
26 private readonly IConfiguration _configuration;
27
28 public HomeController(IConfiguration configuration)
29 {
30 _configuration = configuration;
31 ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
32 }
33
34 private async Task<NpgsqlConnection> OpenDatabaseConnectionAsync()
35 {
36 var dbPort = 9999;
37 var dbUser = _configuration["ConnectionStrings:DefaultConnection"].Split(';')[2].Split('=')[1];
38 var dbPassword = _configuration["ConnectionStrings:DefaultConnection"].Split(';')[3].Split('=')[1];
39 var dbName = _configuration["ConnectionStrings:DefaultConnection"].Split(';')[4].Split('=')[1];
40
41 var connectionString = $"Host=localhost;Port={dbPort};Username={dbUser};Password={dbPassword};Database={dbName}";
42
43 var conn = new NpgsqlConnection(connectionString);
44 await conn.OpenAsync();
45 return conn;
46 }
47
48
49 public IActionResult GetLoggedInUserInfo()
50 {
51 if (User.Identity.IsAuthenticated)
52 {
53 // User is authenticated, you can retrieve information about the user
54 string userName = User.Identity.Name;
55 // You can also access other user properties like roles, claims, etc.
56
57 // Example: Display the logged-in user's name
58 ViewData["UserName"] = userName;
59
60 return View();
61 }
62 else
63 {
64 // User is not authenticated, handle accordingly
65 return RedirectToAction("Login", "Account");
66 }
67 }
68
69
70 [HttpGet]
71 [Authorize]
72 public async Task<IActionResult> IndexAsync(int? page)
73 {
74 string connectionString = _configuration.GetConnectionString("DefaultConnection");
75
76 string userwhere = "";
77 if (User.Identity.IsAuthenticated)
78 {
79 // Retrieve the user email
80 string userEmail = User.Identity.Name;
81 userwhere = string.Format("WHERE c.email=''{0}''", userEmail);
82
83 // Special case for the admin user
84 if (userEmail == "a@trustshield.com")
85 userwhere = ""; // No filtering for admin
86 }
87
88 if (User.Identity.Name == "a@trustshield.com")
89 ViewBag.isadmin = "Yes";
90
91 using (var con = await OpenDatabaseConnectionAsync())
92 {
93 // Prepare the SQL query to call the GetPolicyData function
94 string query = string.Format("SELECT * FROM project.GetPolicyData('{0}')", userwhere);
95
96 // Create a command object
97 NpgsqlCommand com = new NpgsqlCommand(query, con);
98
99 // Add the userwhere parameter to the command
100 com.Parameters.AddWithValue("@UserWhere", userwhere);
101
102 // Execute the command and fill the results into a DataSet
103 NpgsqlDataAdapter sqlda = new NpgsqlDataAdapter(com);
104 DataSet ds = new DataSet();
105 sqlda.Fill(ds);
106
107 List<Policy> policies = new List<Policy>();
108
109 if (ds.Tables[0].Rows.Count > 0)
110 {
111 foreach (DataRow dr in ds.Tables[0].Rows)
112 {
113 policies.Add(new Policy
114 {
115 P_id = Convert.ToInt32(dr["p_id"]),
116 PolicyType = Convert.ToString(dr["PolicyType"]),
117 CustomerName = Convert.ToString(dr["CustomerName"]),
118 Sdate = Convert.ToDateTime(dr["StartDate"]),
119 Edate = Convert.ToDateTime(dr["EndDate"]),
120 Package = Convert.ToInt32(dr["PackageCode"]),
121 PackageTitle = Convert.ToString(dr["PackageTitle"]),
122 PackageTotal = Convert.ToDecimal(dr["PackageTotal"])
123 });
124 }
125 }
126 else
127 {
128 ViewBag.Error = "Nuk ka te dhena ne baze!"; // No data found
129 }
130
131 int pageNumber = page ?? 1; // Default page number is 1
132 int pageSize = 5; // Number of items to display per page
133
134 // Apply paging
135 List<Policy> pagedPolicies = policies.Skip((pageNumber - 1) * pageSize).Take(pageSize).ToList();
136
137 ViewBag.TotalPages = (int)Math.Ceiling(policies.Count / (double)pageSize);
138 ViewBag.CurrentPage = pageNumber;
139
140 ModelState.Clear();
141
142 return View(pagedPolicies);
143 //return View();
144 }
145
146
147 }
148
149 public async Task<IActionResult> PrivacyAsync()
150 {
151 string connectionString = _configuration.GetConnectionString("DefaultConnection");
152 NpgsqlConnection sqlcon = await OpenDatabaseConnectionAsync();
153 //sqlcon.Open();
154 string query = "";
155
156 // Check if the user is "a@trustshield.com"
157 if (User.Identity.Name == "a@trustshield.com")
158 {
159 // If the user is "a@trustshield.com", select all employees
160 query = "SELECT email, name FROM project.Customer";
161 }
162 else
163 {
164 // If the user is not "a@trustshield.com", select only the current user
165 query = string.Format("SELECT email, name FROM project.Customer WHERE email='{0}'", User.Identity.Name);
166 }
167
168 using (NpgsqlCommand command = new NpgsqlCommand(query, sqlcon))
169 {
170 // Execute the command and retrieve the data
171 using (NpgsqlDataReader reader = command.ExecuteReader())
172 {
173 // Create a list to store the data
174 List<Customer> items = new List<Customer>();
175
176 if (User.Identity.Name == "a@trustshield.com")
177 {
178 // If the user is "a@trustshield.com", add a row for "ALL"
179 string email = ""; // Define the customer ID for "ALL"
180 Customer allCustomer = new Customer
181 {
182 Email = email,
183 Name = "ALL"
184 };
185
186 items.Add(allCustomer);
187 }
188
189 // Read the data and add it to the list
190 while (reader.Read())
191 {
192 string email = (string)reader["email"];
193 string val = (string)reader["name"];
194 items.Add(new Customer { Email = email, Name = val });
195 }
196
197 // Pass the list to the view
198 ViewBag.Items = items;
199 }
200 }
201 return View();
202 }
203
204
205 [HttpPost]
206 public async Task<ActionResult> PrivacyAsync(string datef, string datem, string dropdown)
207 {
208 string connectionString = _configuration.GetConnectionString("DefaultConnection");
209 NpgsqlConnection sqlcon = await OpenDatabaseConnectionAsync();
210 //sqlcon.Open();
211 string dropdown_params = string.Empty;
212 if (dropdown != null)
213 dropdown_params = string.Format(" and c.email = '{0}'", dropdown);
214
215 string query = string.Format(@"SELECT p.p_id,
216 CASE
217 WHEN v.pol_id IS NOT NULL THEN 'Auto Policy'
218 WHEN t.pol_id IS NOT NULL THEN 'Travel Health'
219 ELSE 'Property Policy'
220 END AS PolicyType,
221 c.name AS CustomerName,
222 p.sdate AS StartDate,
223 p.edate AS EndDate,
224 p.package AS PackageCode,
225 pkg.title AS PackageTitle,
226 pkg.total AS PackageTotal
227 FROM project.policy p
228 LEFT JOIN project.Auto_pol v ON p.p_id = v.pol_id
229 LEFT JOIN project.Travel_pol t ON p.p_id = t.pol_id
230 LEFT JOIN project.property_pol pp ON p.p_id = pp.pr_id
231 left join project.pol_dog pd on p.p_id =pd.policy
232 LEFT JOIN project.customer c ON pd.c_id = c.c_id--OR t.o_embg = c.c_id
233 LEFT JOIN project.package pkg ON p.package = pkg.code
234 where p.sdate between '{0}' and '{1}' {2}", datef, datem, dropdown_params);
235
236 DataTable dataTable = await GetDataFromSqlServerAsync(connectionString, query);
237
238 if (dataTable.Rows.Count == 0)
239 {
240 TempData["Nodata"] = "Nuk ka te dhena per kete periudhe!!";
241 return RedirectToAction("Privacy");
242 }
243
244 string fileName = "template.xlsx";
245 return GenerateExcelFile(fileName, dataTable);
246 }
247 public async Task<DataTable> GetDataFromSqlServerAsync(string connectionString, string query)
248 {
249 DataTable dataTable = new DataTable();
250
251 using (NpgsqlConnection connection = await OpenDatabaseConnectionAsync())
252 {
253 NpgsqlCommand command = new NpgsqlCommand(query, connection);
254 NpgsqlDataAdapter adapter = new NpgsqlDataAdapter(command);
255
256 adapter.Fill(dataTable);
257 connection.Close();
258 }
259
260 return dataTable;
261 }
262
263 public FileResult GenerateExcelFile(string fileName, DataTable dataTable)
264 {
265 using (ExcelPackage excelPackage = new ExcelPackage())
266 {
267 ExcelWorksheet worksheet = excelPackage.Workbook.Worksheets.Add("Sheet1");
268
269 // Add column headers
270 int colIndex = 1;
271 foreach (DataColumn column in dataTable.Columns)
272 {
273 worksheet.Cells[1, colIndex].Value = column.ColumnName;
274 colIndex++;
275 }
276
277 // Add data rows
278 int rowIndex = 2;
279 foreach (DataRow row in dataTable.Rows)
280 {
281 colIndex = 1;
282 foreach (DataColumn column in dataTable.Columns)
283 {
284 object value = row[column];
285
286 // Format date values explicitly
287 if (column.DataType == typeof(DateTime))
288 {
289 DateTime dateValue = (DateTime)value;
290 worksheet.Cells[rowIndex, colIndex].Value = dateValue.ToString("dd/MM/yyyy");
291 }
292 else
293 {
294 worksheet.Cells[rowIndex, colIndex].Value = value;
295 }
296
297 colIndex++;
298 }
299 rowIndex++;
300 }
301
302 // Write the file to the response stream
303 MemoryStream memoryStream = new MemoryStream();
304 excelPackage.SaveAs(memoryStream);
305
306 // Return the Excel file as a byte array
307 byte[] fileBytes = memoryStream.ToArray();
308
309 // Set the response headers for file download
310 //string fileName = "YourFileName.xlsx";
311 string contentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
312 return File(fileBytes, contentType, fileName);
313 }
314 }
315
316 public IActionResult Auto()//typepolicy = 3
317 {
318 return View();
319 }
320
321 [HttpPost]
322 public async Task<IActionResult> AutoAsync(Vehicle veh)
323 {
324 if (ModelState.IsValid)
325 {
326 NpgsqlTransaction transaction = null;
327 try
328 {
329 string connectionString = _configuration.GetConnectionString("DefaultConnection");
330 using (NpgsqlConnection con = await OpenDatabaseConnectionAsync())
331 {
332 transaction = (NpgsqlTransaction)await con.BeginTransactionAsync();
333 DateTime startDate = DateTime.Parse(Request.Form["startDate"]);
334 DateTime enddate = startDate.AddYears(1);
335
336 // Insert data into Policy table and get p_id (use RETURNING to get the inserted ID in PostgreSQL)
337 string insertPolicyQuery = "INSERT INTO project.Policy (sdate, edate, package) " +
338 "VALUES (@Sdate, @Edate, 4) " +
339 "RETURNING p_id";
340 using (NpgsqlCommand insertPolicyCmd = new NpgsqlCommand(insertPolicyQuery, con))
341 {
342 insertPolicyCmd.Parameters.AddWithValue("@Sdate", startDate);
343 insertPolicyCmd.Parameters.AddWithValue("@Edate", enddate);
344 int p_id = (int)insertPolicyCmd.ExecuteScalar();
345
346 // Insert data into Auto_pol table and get a_id (again using RETURNING)
347 string insertPolAutoQuery = "INSERT INTO project.Auto_pol (pol_id) " +
348 "VALUES (@Pol_Id) " +
349 "RETURNING a_id";
350 using (NpgsqlCommand insertPolAutoCmd = new NpgsqlCommand(insertPolAutoQuery, con))
351 {
352 insertPolAutoCmd.Parameters.AddWithValue("@Pol_Id", p_id);
353 int a_id = (int)insertPolAutoCmd.ExecuteScalar();
354
355 // Insert data into Vehicle table
356 string insertVehicleQuery = "INSERT INTO project.Vehicle (policy, type, marka, model, license_plate) " +
357 "VALUES (@Policy, @Type, @Marka, @Model, @LicensePlate)";
358 using (NpgsqlCommand insertVehicleCmd = new NpgsqlCommand(insertVehicleQuery, con))
359 {
360 insertVehicleCmd.Parameters.AddWithValue("@Policy", a_id);
361 insertVehicleCmd.Parameters.AddWithValue("@Type", veh.Type);
362 insertVehicleCmd.Parameters.AddWithValue("@Marka", veh.Marka);
363 insertVehicleCmd.Parameters.AddWithValue("@Model", veh.Model);
364 insertVehicleCmd.Parameters.AddWithValue("@LicensePlate", veh.License_Plate);
365 insertVehicleCmd.ExecuteNonQuery();
366 }
367
368 // Insert data into pol_dog table
369 string insertDogQuery = @"INSERT INTO project.pol_dog (d_embg, c_id, name, policy, birthdate)
370 SELECT @a_id, c_id, name, @Policy, CURRENT_DATE
371 FROM project.Customer WHERE email = @Email";
372 using (NpgsqlCommand insertDogCmd = new NpgsqlCommand(insertDogQuery, con))
373 {
374 insertDogCmd.Parameters.AddWithValue("@Policy", p_id);
375 insertDogCmd.Parameters.AddWithValue("@Email", User.Identity.Name);
376 insertDogCmd.Parameters.AddWithValue("@a_id", p_id); // a_id + 1 as per your logic
377 insertDogCmd.ExecuteNonQuery();
378 }
379 await transaction.CommitAsync();
380 return RedirectToAction("Payment", new { policyId = p_id, package = 4 });
381 }
382 }
383 }
384 }
385 catch (Exception ex)
386 {
387 if (transaction != null)
388 {
389 await transaction.RollbackAsync();
390 }
391 ModelState.AddModelError(string.Empty, "An error occurred while creating the auto policy.");
392 // Log the exception if needed
393 }
394 }
395 return View();
396 }
397
398
399 [HttpGet]
400 public async Task<IActionResult> TravelAsync()
401 {
402 List<Package> packages = new List<Package>();
403 using (NpgsqlConnection con = await OpenDatabaseConnectionAsync()) // Replace NpgsqlConnection with your database connection type
404 {
405 string query = "SELECT code, title FROM project.Package WHERE type_pol = 1";
406 using (NpgsqlCommand cmd = new NpgsqlCommand(query, con))
407 {
408 using (NpgsqlDataReader reader = cmd.ExecuteReader())
409 {
410 while (reader.Read())
411 {
412 int code = reader.GetInt32(0);
413 string title = reader.GetString(1);
414 packages.Add(new Package { Code = code, Title = title }); // Replace Package with your actual model class
415 }
416 }
417 }
418 }
419
420 // Store packages data in ViewBag
421 ViewBag.Packages = packages;
422
423 // Return the view
424 return View();
425 }
426
427
428 [HttpPost]
429 public async Task<IActionResult> TravelAsync(Osi polOsi)
430 {
431 if (ModelState.IsValid)
432 {
433 try
434 {
435 string connectionString = _configuration.GetConnectionString("DefaultConnection");
436 using (NpgsqlConnection con = await OpenDatabaseConnectionAsync())
437 {
438 int packageId = int.Parse(Request.Form["package"]);
439
440 // Calculate end date based on the selected start date and number of days
441 DateTime startDate = DateTime.Parse(Request.Form["startDate"]);
442 int numberOfDays = int.Parse(Request.Form["numberOfDays"]);
443 DateTime endDate = startDate.AddDays(numberOfDays);
444
445 // Insert data into Policy table with automatic ID generation and returning the p_id
446 string insertPolicyQuery = "INSERT INTO project.Policy (sdate, edate, package) " +
447 "VALUES (@Sdate, @Edate, @Package) " +
448 "RETURNING p_id";
449 using (NpgsqlCommand insertPolicyCmd = new NpgsqlCommand(insertPolicyQuery, con))
450 {
451 insertPolicyCmd.Parameters.AddWithValue("@Sdate", startDate);
452 insertPolicyCmd.Parameters.AddWithValue("@Edate", endDate);
453 insertPolicyCmd.Parameters.AddWithValue("@Package", packageId);
454 int p_id = (int)insertPolicyCmd.ExecuteScalar();
455
456 // Insert data into PolTravel table and return tr_id
457 string insertPolTravelQuery = "INSERT INTO project.Travel_pol (pol_id) " +
458 "VALUES (@Pol_Id) " +
459 "RETURNING tr_id";
460 using (NpgsqlCommand insertPolTravelCmd = new NpgsqlCommand(insertPolTravelQuery, con))
461 {
462 insertPolTravelCmd.Parameters.AddWithValue("@Pol_Id", p_id);
463 int tr_id = (int)insertPolTravelCmd.ExecuteScalar();
464
465 // Insert data into PolOsi table
466 string insertPolOsiQuery = "INSERT INTO project.Pol_osi (o_embg, policy, name, surname, birthdate, kontakt) " +
467 "VALUES (@O_Embg, @Policy, @Name, @Surname, @Birthdate, @Kontakt)";
468 using (NpgsqlCommand insertPolOsiCmd = new NpgsqlCommand(insertPolOsiQuery, con))
469 {
470 insertPolOsiCmd.Parameters.AddWithValue("@O_Embg", polOsi.OEmbg);
471 insertPolOsiCmd.Parameters.AddWithValue("@Policy", tr_id);
472 insertPolOsiCmd.Parameters.AddWithValue("@Name", polOsi.Name);
473 insertPolOsiCmd.Parameters.AddWithValue("@Surname", polOsi.Surname);
474 insertPolOsiCmd.Parameters.AddWithValue("@Birthdate", polOsi.Birthdate);
475 insertPolOsiCmd.Parameters.AddWithValue("@Kontakt", polOsi.Kontakt);
476 insertPolOsiCmd.ExecuteNonQuery();
477 }
478
479 // Insert data into pol_dog table
480 string insertDogQuery = @"INSERT INTO project.pol_dog (d_embg, c_id, name, policy, birthdate)
481 SELECT @tr_id, c_id, name, @Policy, CURRENT_DATE
482 FROM project.Customer
483 WHERE email = @email";
484 using (NpgsqlCommand insertDogCmd = new NpgsqlCommand(insertDogQuery, con))
485 {
486 insertDogCmd.Parameters.AddWithValue("@Policy", p_id);
487 insertDogCmd.Parameters.AddWithValue("@email", User.Identity.Name);
488 insertDogCmd.Parameters.AddWithValue("@tr_id", p_id); // tr_id + 3 as per your logic
489 insertDogCmd.ExecuteNonQuery();
490 }
491
492 // Redirect to Payment action with policyId and packageId
493 return RedirectToAction("Payment", new { policyId = p_id, package = packageId });
494 }
495 }
496 }
497 }
498 catch (Exception ex)
499 {
500 ModelState.AddModelError(string.Empty, "An error occurred while creating the travel policy.");
501 // Log the exception if needed
502 }
503 }
504
505 return View(polOsi);
506 }
507
508 [HttpGet]
509 public async Task<IActionResult> Property()
510 {
511 List<Package> packages = new List<Package>();
512 using (NpgsqlConnection con = await OpenDatabaseConnectionAsync())
513 {
514 string query = "SELECT code, title FROM project.Package WHERE type_pol = 2";
515 using (NpgsqlCommand cmd = new NpgsqlCommand(query, con))
516 {
517 using (NpgsqlDataReader reader = cmd.ExecuteReader())
518 {
519 while (reader.Read())
520 {
521 int code = reader.GetInt32(0);
522 string title = reader.GetString(1);
523 packages.Add(new Package { Code = code, Title = title });
524 }
525 }
526 }
527 }
528 ViewBag.Packages = packages;
529 return View();
530 }
531
532
533 [HttpPost]
534 public async Task<IActionResult> Property(Property property)
535 {
536 try
537 {
538 string connectionString = _configuration.GetConnectionString("DefaultConnection");
539 int packageId = int.Parse(Request.Form["package"]);
540
541 using (NpgsqlConnection con = await OpenDatabaseConnectionAsync())
542 {
543 DateTime startDate = DateTime.Parse(Request.Form["startDate"]);
544 int numberOfDays = int.Parse(Request.Form["numberOfDays"]);
545 DateTime endDate = startDate.AddDays(numberOfDays);
546
547 string insertPolicyQuery = "INSERT INTO project.Policy (sdate, edate, package) " +
548 "VALUES (@Sdate, @Edate, @Package) " +
549 "RETURNING p_id";
550 int p_id;
551 using (NpgsqlCommand insertPolicyCmd = new NpgsqlCommand(insertPolicyQuery, con))
552 {
553 insertPolicyCmd.Parameters.AddWithValue("@Sdate", startDate);
554 insertPolicyCmd.Parameters.AddWithValue("@Edate", endDate);
555 insertPolicyCmd.Parameters.AddWithValue("@Package", packageId);
556 p_id = (int)insertPolicyCmd.ExecuteScalar(); // Get the policy ID (p_id)
557 }
558
559 int pr_id = 0;
560 string policyQuery = "INSERT INTO project.Property_pol (pol_id) " +
561 "VALUES (@pol_id) " +
562 "RETURNING pr_id";
563 using (NpgsqlCommand cmd = new NpgsqlCommand(policyQuery, con))
564 {
565 cmd.Parameters.AddWithValue("@pol_id", p_id); // Use p_id from Policy table
566 pr_id = (int)cmd.ExecuteScalar(); // Get the generated pr_id for Property_pol
567 }
568
569 string query = "INSERT INTO project.Property (policy, address, floor, year_build, security) " +
570 "VALUES (@policy, @address, @floor, @year_build, @security)";
571
572 using (NpgsqlCommand cmd = new NpgsqlCommand(query, con))
573 {
574 cmd.Parameters.AddWithValue("@policy", pr_id); // Use pr_id from Property_pol
575 cmd.Parameters.AddWithValue("@address", property.Address);
576 cmd.Parameters.AddWithValue("@floor", property.Floor);
577 cmd.Parameters.AddWithValue("@year_build", property.YearBuild);
578 cmd.Parameters.AddWithValue("@security", true);
579
580 cmd.ExecuteNonQuery(); // Insert into Property table
581 }
582
583 string insertdog = @"INSERT INTO project.pol_dog (d_embg, c_id, name, policy, birthdate)
584 SELECT @a_id, c_id, name, @Policy, CURRENT_DATE
585 FROM project.Customer WHERE email=@email";
586 using (NpgsqlCommand insertDogCmd = new NpgsqlCommand(insertdog, con))
587 {
588 insertDogCmd.Parameters.AddWithValue("@Policy", p_id);
589 insertDogCmd.Parameters.AddWithValue("@email", User.Identity.Name);
590 insertDogCmd.Parameters.AddWithValue("@a_id", p_id); // pr_id + 1 as per your logic
591 insertDogCmd.ExecuteNonQuery();
592 }
593
594 return RedirectToAction("Payment", new { policyId = p_id, package = packageId });
595 }
596 }
597 catch (Exception ex)
598 {
599 ViewData["ErrorMessage"] = $"Error: {ex.Message}";
600 return View(property);
601 }
602 }
603
604
605
606
607
608 [HttpGet]
609 public async Task<IActionResult> Package()
610 {
611 try
612 {
613 string connectionString = _configuration.GetConnectionString("DefaultConnection");
614
615 List<Package> packages = new List<Package>();
616
617 using (NpgsqlConnection con = await OpenDatabaseConnectionAsync())
618 {
619 string query = "SELECT * FROM project.package"; // Adjust query to fetch all packages
620 using (NpgsqlCommand cmd = new NpgsqlCommand(query, con))
621 {
622 NpgsqlDataReader reader = await cmd.ExecuteReaderAsync();
623 while (await reader.ReadAsync())
624 {
625 Package package = new Package
626 {
627 Code = (int)reader["Code"],
628 Title = (string)reader["Title"],
629 Total = reader["Total"] as decimal?,
630 Valuet = (string)reader["Valuet"],
631 TypePol = (int)reader["type_pol"] // Read the policy type
632 };
633 packages.Add(package);
634 }
635 }
636 }
637
638 return View(packages);
639 }
640 catch (Exception ex)
641 {
642 ViewData["ErrorMessage"] = $"Error: {ex.Message}";
643 return View();
644 }
645 }
646
647 // POST: Insert or Update package
648 [HttpPost]
649 public async Task<IActionResult> Package(Package package)
650 {
651 if (ModelState.IsValid)
652 {
653 try
654 {
655 using (NpgsqlConnection con = await OpenDatabaseConnectionAsync())
656 {
657 if (package.Code == 0) // New package (insert)
658 {
659 string insertQuery = "INSERT INTO project.package (Title, Total, Valuet, Type_Pol) " +
660 "VALUES (@Title, @Total, @Valuet, @TypePol)";
661
662 using (NpgsqlCommand cmd = new NpgsqlCommand(insertQuery, con))
663 {
664 cmd.Parameters.AddWithValue("@Title", package.Title);
665 cmd.Parameters.AddWithValue("@Total", package.Total);
666 cmd.Parameters.AddWithValue("@Valuet", package.Valuet);
667 cmd.Parameters.AddWithValue("@TypePol", package.TypePol);
668
669 await cmd.ExecuteNonQueryAsync();
670 }
671 }
672 else
673 {
674 string updateQuery = "UPDATE project.package SET Title = @Title, Total = @Total, Valuet = @Valuet " +
675 "WHERE Code = @Code";
676
677 string typepolquery = string.Format("select type_pol from project.Package where code={0}", package.Code);
678 using (NpgsqlCommand cmd1 = new NpgsqlCommand(typepolquery, con))
679 {
680 int type = (int)cmd1.ExecuteScalar();
681 package.TypePol = type;
682 }
683
684 using (NpgsqlCommand cmd = new NpgsqlCommand(updateQuery, con))
685 {
686 cmd.Parameters.AddWithValue("@Title", package.Title);
687 cmd.Parameters.AddWithValue("@Total", package.Total);
688 cmd.Parameters.AddWithValue("@Valuet", package.Valuet);
689 cmd.Parameters.AddWithValue("@TypePol", package.TypePol);
690 cmd.Parameters.AddWithValue("@Code", package.Code);
691
692 await cmd.ExecuteNonQueryAsync();
693 }
694 }
695 }
696
697 return RedirectToAction("Package"); // Redirect back to the package list after saving
698 }
699 catch (Exception ex)
700 {
701 ModelState.AddModelError(string.Empty, "An error occurred while saving the package.");
702 }
703 }
704
705 return View("Package", package); // Stay on the same view in case of errors
706 }
707
708 [HttpGet]
709 public async Task<IActionResult> Covers()
710 {
711 try
712 {
713 // Get the connection string
714 string connectionString = _configuration.GetConnectionString("DefaultConnection");
715
716 List<SelectListItem> packages = new List<SelectListItem>();
717 List<Covers> covers = new List<Covers>();
718
719 using (NpgsqlConnection con = await OpenDatabaseConnectionAsync())
720 {
721 // Query to fetch packages
722 string query = "SELECT code, title FROM project.Package";
723 using (NpgsqlCommand cmd = new NpgsqlCommand(query, con))
724 {
725 NpgsqlDataReader reader = await cmd.ExecuteReaderAsync();
726 while (await reader.ReadAsync())
727 {
728 packages.Add(new SelectListItem
729 {
730 Value = reader["code"].ToString(),
731 Text = reader["title"].ToString()
732 });
733 }
734 }
735 }
736
737 using (NpgsqlConnection con = await OpenDatabaseConnectionAsync())
738 {
739 // Query to fetch packages
740 string query = "SELECT (select title from project.Package where code=CAST(pc.package AS INTEGER)) as package_name,pc.* FROM project.covers pc";
741 using (NpgsqlCommand cmd = new NpgsqlCommand(query, con))
742 {
743 NpgsqlDataReader reader = await cmd.ExecuteReaderAsync();
744 while (await reader.ReadAsync())
745 {
746 Covers cover = new Covers
747 {
748 cov_id = (int)reader["cov_id"],
749 cov_amount = (string)reader["cov_amount"],
750 cov_type = (string)reader["cov_type"],
751 PackageName = (string)reader["package_name"]
752 };
753 covers.Add(cover);
754 }
755 }
756 }
757
758 // Pass the packages to the view for the dropdown
759 ViewData["Packages"] = packages;
760
761 return View(covers);
762 }
763 catch (Exception ex)
764 {
765 ViewData["ErrorMessage"] = $"Error: {ex.Message}";
766 return View();
767 }
768 }
769
770 [HttpPost]
771 public async Task<IActionResult> Covers(Covers cover)
772 {
773 try
774 {
775 // Get the connection string
776 string connectionString = _configuration.GetConnectionString("DefaultConnection");
777
778 using (NpgsqlConnection con = await OpenDatabaseConnectionAsync())
779 {
780
781 string package = null;
782 if (cover.cov_id > 0)
783 {
784 package = string.Format("select code from project.Package where title='{0}'", cover.package_code);
785 NpgsqlCommand cmd1 = new NpgsqlCommand(package, con);
786 object result = cmd1.ExecuteScalar();
787 cover.package_code = result.ToString();
788 }
789
790 // If the cover has a valid id, we're updating an existing cover, otherwise, it's a new cover (insert)
791 string query;
792
793 if (cover.cov_id > 0) // Update existing cover
794 {
795 query = "UPDATE project.Covers SET cov_amount = @cov_amount, package = @package, cov_type = @cov_type WHERE cov_id = @cov_id";
796 }
797 else // Insert new cover
798 {
799 query = "INSERT INTO project.Covers (cov_amount, package, cov_type) VALUES (@cov_amount, @package, @cov_type)";
800 }
801
802 using (NpgsqlCommand cmd = new NpgsqlCommand(query, con))
803 {
804 // Add parameters to prevent SQL injection
805 cmd.Parameters.AddWithValue("@cov_amount", cover.cov_amount);
806 cmd.Parameters.AddWithValue("@package", cover.package_code); // Use the selected package_code
807 cmd.Parameters.AddWithValue("@cov_type", cover.cov_type);
808
809 // If updating, include the ID in the parameters
810 if (cover.cov_id > 0)
811 {
812 cmd.Parameters.AddWithValue("@cov_id", cover.cov_id);
813 }
814
815 // Execute the query
816 int result = await cmd.ExecuteNonQueryAsync();
817 if (result > 0)
818 {
819 return RedirectToAction("Covers"); // Redirect after success
820 }
821 else
822 {
823 ViewData["ErrorMessage"] = "An error occurred while creating/updating the cover.";
824 return View(cover); // Return to the form with an error message
825 }
826 }
827 }
828 }
829 catch (Exception ex)
830 {
831 ViewData["ErrorMessage"] = $"Error: {ex.Message}";
832 return View(cover);
833 }
834 }
835
836
837 [HttpGet]
838 public async Task<IActionResult> PaymentAsync(int policyId, int package = 0)
839 {
840 string connectionString = _configuration.GetConnectionString("DefaultConnection");
841 int total = 0;
842 if (package != 0)
843 {
844 // SQL query to get total amount for the specified package
845 string query = "SELECT total FROM project.Package WHERE code = @package";
846
847 try
848 {
849 using (NpgsqlConnection conn = await OpenDatabaseConnectionAsync())
850 {
851
852 // Create and configure the SQL command
853 using (NpgsqlCommand cmd = new NpgsqlCommand(query, conn))
854 {
855 cmd.Parameters.AddWithValue("@package", package);
856
857 // Execute the query and retrieve the result as a single value (ExecuteScalar)
858 object result = cmd.ExecuteScalar();
859
860 if (result != null)
861 {
862 total = Convert.ToInt32(result);
863 }
864 else
865 {
866 // Handle the case when no result is found, if necessary
867 total = 0;
868 }
869 }
870 }
871 }
872 catch (Exception ex)
873 {
874 // Handle exceptions (e.g., log them)
875 // You can return an error page or return a view with an error message
876 return View("Error", new { message = ex.Message });
877 }
878 }
879 var model = new Payment
880 {
881 PolicyId = policyId,
882 PAmount = total
883 // You can populate the model with any additional data you need for the payment form
884 };
885
886 return View(model);
887 }
888
889 [HttpPost]
890 public async Task<IActionResult> PaymentAsync(Payment model)
891 {
892 if (ModelState.IsValid)
893 {
894 try
895 {
896 // Connection string from appsettings.json
897 string connectionString = _configuration.GetConnectionString("DefaultConnection");
898
899 // SQL query to insert payment data
900 string query = "INSERT INTO project.Payment (policy, p_date, p_amount, visa_number) " +
901 "VALUES (@PolicyId, @PaymentDate, @PaymentAmount, @VisaNumber);";
902
903 using (NpgsqlConnection conn = await OpenDatabaseConnectionAsync())
904 {
905 // Create and configure the SQL command
906 using (NpgsqlCommand cmd = new NpgsqlCommand(query, conn))
907 {
908 cmd.Parameters.AddWithValue("@PolicyId", model.PolicyId);
909 cmd.Parameters.AddWithValue("@PaymentDate", model.PDate);
910 cmd.Parameters.AddWithValue("@PaymentAmount", model.PAmount);
911 cmd.Parameters.AddWithValue("@VisaNumber", model.VisaNumber);
912
913 // Execute the query
914 int rowsAffected = (int)cmd.ExecuteNonQuery();
915
916 if (rowsAffected > 0)
917 {
918 // If the payment was inserted successfully, redirect or return a success message
919 return View("PaymentSuccess");
920 }
921 else
922 {
923 // Handle failure case
924 ModelState.AddModelError("", "Error occurred while processing the payment.");
925 }
926 }
927 }
928 }
929 catch (Exception ex)
930 {
931 // Log exception or handle accordingly
932 ModelState.AddModelError("", $"An error occurred: {ex.Message}");
933 }
934 }
935
936 // Return the view with error if model validation fails or something went wrong
937 return View(model);
938 }
939
940 [ResponseCache(Duration = 0, Location = ResponseCacheLocation.None, NoStore = true)]
941 public IActionResult Error()
942 {
943 return View(new ErrorViewModel { RequestId = Activity.Current?.Id ?? HttpContext.TraceIdentifier });
944 }
945 }
946}
Note: See TracBrowser for help on using the repository browser.