public static List<string[]> CreateStudentReport(infoStudent selectedStudent, DataTable paymentTable) { List<string[]> reportData = new List<string[]>(); List<string> tempList = new List<string>(); //Folio, Cantidad, Fecha, Completado, Concepto foreach(DataRow payRow in paymentTable.Rows) { tempList = new List<string>(); tempList.Add(selectedStudent.studentID.ToString()); tempList.Add(selectedStudent.studentLastName + " " + selectedStudent.studentLastName2 + " " + selectedStudent.studentFistName); foreach (var item in payRow.ItemArray) { tempList.Add(item.ToString()); } reportData.Add(tempList.ToArray()); } if (reportData.Count > 0) return reportData; return null; }
public static double CalculateOvercharge(infoStudent selectedStudent) { List<infoConcept> expiredConcepts = DAL.getExpiredSchoolConcepts(selectedStudent.studentGroup, selectedStudent.studentLevel, selectedStudent.studentID); List<double> overCharges = DAL.OverchargeAmounts(); double totalCharge = 0.0; if (expiredConcepts.Count > 0) { foreach (infoConcept concept in expiredConcepts) { if (concept.Type == "Mensualidad") { int afterDays = (DateTime.Now - concept.LimitDate).Days; if (afterDays > overCharges[0]) totalCharge = overCharges[1]; if (afterDays > overCharges[2]) totalCharge = overCharges[3]; if (afterDays > overCharges[4]) totalCharge = overCharges[5]; } } } //check if at least one unpayed concept exists //check if it's mensualidad //add value depening on database to all until unpayed concept is payed (none exist) return totalCharge; }
private static void PrintDebtReport() { System.Drawing.Image imageHeader = (System.Drawing.Image)CCMM.Properties.Resources.logo; var logo = iTextSharp.text.Image.GetInstance(imageHeader, System.Drawing.Imaging.ImageFormat.Png); MyPageEventHandler e = new MyPageEventHandler() { ImageHeader = logo }; // Create a Document object var document = new Document(PageSize.A4, 50, 50, 180, 25); double totalEarningsComplete = 0; string file = "ReporteAdeudos" + _checkedDate.ToString("MM-dd-yy") + ".pdf"; // Create a new PdfWriter object, specifying the output stream var output = new MemoryStream(); //var writer = PdfWriter.GetInstance(document, output); var writer = PdfWriter.GetInstance(document, new FileStream(file, FileMode.Create)); writer.PageEvent = e; // Open the Document for writing document.Open(); var fontLevelTitle = FontFactory.GetFont("Arial", 12, Font.BOLD); var subTitleFont = FontFactory.GetFont("Arial", 16); var boldTableFont = FontFactory.GetFont("Arial", 13); var cellTitle = FontFactory.GetFont("Arial", 11, Font.BOLD); var bodyFont = FontFactory.GetFont("Arial", 10); //sid, slvl, concept name, concept amount //[ 0 ],[ 1 ], [ 2 ], [ 3 ] //Table //Go trough each level for (int j = 5; j >= 1; j--) { bool foundPayment = false; //Check if there's at least one payment for X level foreach (var payment in _reportData) { if (int.Parse(payment[1]) == j) foundPayment = true; } //If no payment was found, just skip to next one if (!foundPayment) continue; //Create base table and level table var levelTable = new PdfPTable(6); levelTable.TotalWidth = 475f; levelTable.LockedWidth = true; levelTable.SpacingBefore = 45f; float[] widths = new float[] { 40f, 40f, 60f, 137f, 110f, 67f }; levelTable.SetWidths(widths); string levelTitle = "Nivel Escolar: " + getLevelName(j).ToUpper(); PdfPCell cellLevelName = new PdfPCell(new Phrase(levelTitle, fontLevelTitle)); cellLevelName.Border = PdfPCell.NO_BORDER; cellLevelName.Colspan = 6; cellLevelName.PaddingBottom = 10; levelTable.AddCell(cellLevelName); //Column names PdfPCell cellGrade = new PdfPCell(new Phrase("Grado", cellTitle)); cellGrade.Border = PdfPCell.NO_BORDER; PdfPCell cellGroup = new PdfPCell(new Phrase("Grupo", cellTitle)); cellGroup.Border = PdfPCell.NO_BORDER; PdfPCell cellCuenta = new PdfPCell(new Phrase("Cuenta", cellTitle)); cellCuenta.Border = PdfPCell.NO_BORDER; cellCuenta.PaddingLeft = 5f; PdfPCell cellStudent = new PdfPCell(new Phrase("Alumno", cellTitle)); cellStudent.Border = PdfPCell.NO_BORDER; PdfPCell cellConcept = new PdfPCell(new Phrase("Concepto", cellTitle)); cellConcept.Border = PdfPCell.NO_BORDER; PdfPCell cellCharge = new PdfPCell(new Phrase("A Pagar", cellTitle)); cellCharge.Border = PdfPCell.NO_BORDER; cellCharge.HorizontalAlignment = PdfPCell.ALIGN_RIGHT; levelTable.AddCell(cellGrade); levelTable.AddCell(cellGroup); levelTable.AddCell(cellCuenta); levelTable.AddCell(cellStudent); levelTable.AddCell(cellConcept); levelTable.AddCell(cellCharge); double totalAmount = 0.0; //sid, slvl, concept name, concept amount //[ 0 ],[ 1 ], [ 2 ], [ 3 ] //Go trough each payment that fits the level and print it. foreach (var payment in _reportData) { ////Nivel, Grado, Grupo, SID, Nombre, Folio, Conceptop, Importe if (int.Parse(payment[1]) == j) { totalAmount += double.Parse(payment[3]); //Get student information from ID infoStudent temptStudent = new infoStudent(); temptStudent = DAL.getStudentDetails(Int32.Parse(payment[0])); if (temptStudent == null) continue; cellGrade = new PdfPCell(new Phrase(BAL.getGradeLevel(temptStudent.studentLevel, temptStudent.studentGroup).ToString(), bodyFont)); cellGrade.Border = PdfPCell.BOTTOM_BORDER; cellGroup = new PdfPCell(new Phrase("A", bodyFont)); cellGroup.Border = PdfPCell.BOTTOM_BORDER; cellCuenta = new PdfPCell(new Phrase(temptStudent.studentID.ToString(), bodyFont)); cellCuenta.Border = PdfPCell.BOTTOM_BORDER; cellCuenta.PaddingLeft = 5f; cellStudent = new PdfPCell(new Phrase(temptStudent.studentLastName + " " + temptStudent.studentLastName2 + " " + temptStudent.studentFistName, bodyFont)); cellStudent.Border = PdfPCell.BOTTOM_BORDER; cellConcept = new PdfPCell(new Phrase(payment[2], bodyFont)); cellConcept.Border = PdfPCell.BOTTOM_BORDER; double tempPayment = double.Parse(payment[3]); cellCharge = new PdfPCell(new Phrase(tempPayment.ToString(BAL.MoneyFormat), bodyFont)); cellCharge.Border = PdfPCell.BOTTOM_BORDER; cellCharge.HorizontalAlignment = PdfPCell.ALIGN_RIGHT; levelTable.AddCell(cellGrade); levelTable.AddCell(cellGroup); levelTable.AddCell(cellCuenta); levelTable.AddCell(cellStudent); levelTable.AddCell(cellConcept); levelTable.AddCell(cellCharge); } } string levelFooterText = "Total de DEUDAS en " + getLevelName(j); PdfPCell celllevelTotalText = new PdfPCell(new Phrase(levelFooterText)); celllevelTotalText.Border = PdfPCell.TOP_BORDER; PdfPCell celllevelTotalAmount = new PdfPCell(new Phrase(totalAmount.ToString(BAL.MoneyFormat))); celllevelTotalAmount.Border = PdfPCell.TOP_BORDER; celllevelTotalText.Colspan = 5; celllevelTotalText.HorizontalAlignment = 2; celllevelTotalText.PaddingRight = 30f; celllevelTotalText.Border = 0; celllevelTotalAmount.Border = 0; celllevelTotalAmount.Colspan = 1; celllevelTotalAmount.HorizontalAlignment = 2; levelTable.AddCell(celllevelTotalText); levelTable.AddCell(celllevelTotalAmount); document.Add(levelTable); totalEarningsComplete += totalAmount; } //Print totals string txtTotalEarings = "Total en Deudas en " + _reportLevel; PdfPCell cellTotalEarnings = new PdfPCell(new Phrase(txtTotalEarings)); cellTotalEarnings.Border = PdfPCell.NO_BORDER; cellTotalEarnings.Colspan = 4; PdfPCell cellnumEarnings = new PdfPCell(new Phrase(totalEarningsComplete.ToString(BAL.MoneyFormat))); cellnumEarnings.Border = PdfPCell.NO_BORDER; cellnumEarnings.HorizontalAlignment = PdfPCell.ALIGN_LEFT; cellnumEarnings.Colspan = 2; var totalsTable = new PdfPTable(6); totalsTable.TotalWidth = 475f; totalsTable.LockedWidth = true; totalsTable.SpacingBefore = 45f; float[] totalsWidth = new float[] { 40f, 40f, 60f, 137f, 110f, 67f }; totalsTable.SetWidths(totalsWidth); totalsTable.AddCell(cellTotalEarnings); totalsTable.AddCell(cellnumEarnings); document.Add(totalsTable); document.Close(); System.Diagnostics.Process.Start(file); }
public static List<infoConcept> GetUnpayedConcepts(infoStudent selectedStudent) { List<infoConcept> studentConcepts = DAL.getAvailableConcepts(selectedStudent.studentGroup, selectedStudent.studentLevel); List<int> payedConcepts = DAL.getPayedConceptList(selectedStudent.studentID); for (int i = 0; i < studentConcepts.Count; i++) { foreach (int concept in payedConcepts) { if (int.Parse(studentConcepts[i].Value) == concept) { studentConcepts.RemoveAt(i); i--; break; } } } return studentConcepts; }
private void btnSaveNewStudent_Click(object sender, EventArgs e) { List<string> errorList = new List<string>(); infoStudent testStudent = new infoStudent(); infoStudent newStudent = new infoStudent(); int num; //Check for empty fields //Verify that all fields are filled if (txtbAccNum.Text == "" || !(Int32.TryParse(txtbAccNum.Text, out num))) errorList.Add("Numero de Cuenta"); if (txtbName.Text == "") errorList.Add("Nombre"); if (txtbLastName.Text == "" || txtbLastName2.Text == "") errorList.Add("Apellidos"); if (chkSpecialAcc.Checked) { if (txtbDiscount.Text == "" || !(Int32.TryParse(txtbDiscount.Text, out num))) errorList.Add("Descuento"); } if (errorList.Count > 0) { string errorString = "Verifica los siguientes campos: "; foreach (string fieldName in errorList) errorString += "[" + fieldName + "] "; MessageBox.Show(errorString); return; } //Check that account number isn't being used testStudent = DAL.getStudentDetails(Int32.Parse(txtbAccNum.Text)); if (testStudent != null) { MessageBox.Show("Ese numero de cuenta ya existe, pertenece a: " + testStudent.studentFistName + " " + testStudent.studentLastName); return; } //Try to create new student object and send it to the database try { newStudent.studentID = Int32.Parse(txtbAccNum.Text); newStudent.studentFistName = txtbName.Text; newStudent.studentLastName = txtbLastName.Text; newStudent.studentLastName2 = txtbLastName2.Text; newStudent.studentGroup = cbGrade.SelectedIndex + levelValues[cbSchoolLevel.SelectedIndex] + 1; if (chkSpecialAcc.Checked) { newStudent.paymentDiscount = int.Parse(txtbDiscount.Text); } else { newStudent.paymentDiscount = 0; } newStudent.studentLevel = BAL.getLevelfromGrade(newStudent.studentGroup); newStudent.paymentType = cbAccType.Text; DAL.newStudentRecord(newStudent); } catch (Exception exp) { MessageBox.Show("Ocurrio un error, verificar informacion. Texto de error: " + exp.ToString()); return; } MessageBox.Show("Registro creado"); //Clean txtbAccNum.Clear(); txtbDiscount.Clear(); txtbLastName.Clear(); txtbLastName2.Clear(); txtbName.Clear(); cbAccType.SelectedIndex = 0; cbSchoolLevel.SelectedIndex = 0; chkSpecialAcc.Checked = false; //If(OK) Show confirmation/reload (FALSE) Show what happened }
public static void DeleteAccount(infoStudent toDelete) { DAL.DeletePayments(toDelete.studentID); DAL.DeleteAccount(toDelete.studentID); }
public static string updateStudentRecord(infoStudent editedStudent, Int32 currentAccNum) { SqlCeConnection sqlConnection = new SqlCeConnection(connectionString); sqlConnection.Open(); string response = ""; //AccNum, fName, lastname, lastname2, grade, discount SqlCeCommand updateCommand = new SqlCeCommand("UPDATE Students SET Account_Num = @AccN, First_Name = @fName, Last_Name = @lName, Last_Name_2 = @lName2," + "[Group] = @gNum, School_Level = @sLevel, Discount = @disc, Pay_Type = @payType WHERE Account_Num = @currentStu", sqlConnection); updateCommand.Parameters.AddWithValue("@AccN", editedStudent.studentID); updateCommand.Parameters.AddWithValue("@fName", editedStudent.studentFistName); updateCommand.Parameters.AddWithValue("@lName", editedStudent.studentLastName); updateCommand.Parameters.AddWithValue("@lName2", editedStudent.studentLastName2); updateCommand.Parameters.AddWithValue("@gNum", editedStudent.studentGroup); updateCommand.Parameters.AddWithValue("@sLevel", editedStudent.studentLevel); updateCommand.Parameters.AddWithValue("@disc", editedStudent.paymentDiscount); updateCommand.Parameters.AddWithValue("@payType", editedStudent.paymentType); updateCommand.Parameters.AddWithValue("@currentStu", currentAccNum); updateCommand.CommandType = System.Data.CommandType.Text; try { updateCommand.ExecuteNonQuery(); response = "OK"; } catch (Exception e) { throw e; } sqlConnection.Close(); sqlConnection.Dispose(); return response; }
public static void newStudentRecord(infoStudent newStudent) { SqlCeConnection sqlConnection = new SqlCeConnection(connectionString); sqlConnection.Open(); //AccNum, fName, lastname, lastname2, grade, discount SqlCeCommand newStudentCommand = new SqlCeCommand("INSERT INTO Students VALUES (@accNum, @fistName, @lastName, @lastName2, @stdGroup, @stdLevel, @stdDiscount, @accType)", sqlConnection); newStudentCommand.CommandType = System.Data.CommandType.Text; newStudentCommand.Parameters.AddWithValue("@accNum", newStudent.studentID); newStudentCommand.Parameters.AddWithValue("@fistName", newStudent.studentFistName); newStudentCommand.Parameters.AddWithValue("@lastName", newStudent.studentLastName); newStudentCommand.Parameters.AddWithValue("@lastName2", newStudent.studentLastName2); newStudentCommand.Parameters.AddWithValue("@stdGroup", newStudent.studentGroup); newStudentCommand.Parameters.AddWithValue("@stdLevel", newStudent.studentLevel); newStudentCommand.Parameters.AddWithValue("@stdDiscount", newStudent.paymentDiscount); newStudentCommand.Parameters.AddWithValue("@accType", newStudent.paymentType); try { newStudentCommand.ExecuteNonQuery(); } catch (Exception e) { throw e; } finally { sqlConnection.Close(); sqlConnection.Dispose(); } }
/// <summary> /// Retrieves all information about a specific student /// </summary> /// <param name="studentAccount">The student's account number</param> /// <returns>Student object [infoStudent]</returns> public static infoStudent getStudentDetails(Int32 studentAccount) { //Create student object infoStudent studentDetails = new infoStudent(); //Create connection, command and open connection. SqlCeConnection sqlConnection = new SqlCeConnection(connectionString); SqlCeCommand sqlCommand = sqlConnection.CreateCommand(); sqlConnection.Open(); //Gets concepts that are available depending on group and level of student sqlCommand.CommandText = "SELECT * FROM Students WHERE Account_Num = " + studentAccount; SqlCeDataReader sqlReader = sqlCommand.ExecuteReader(); //Get values from columns, add to student object while (sqlReader.Read()) { studentDetails.studentID = Int32.Parse(sqlReader["Account_Num"].ToString()); studentDetails.studentFistName = sqlReader["First_Name"].ToString(); studentDetails.studentLastName = sqlReader["Last_Name"].ToString(); studentDetails.studentLastName2 = sqlReader["Last_Name_2"].ToString(); studentDetails.studentGroup = int.Parse(sqlReader["Group"].ToString()); studentDetails.studentLevel = int.Parse(sqlReader["School_Level"].ToString()); studentDetails.paymentDiscount = int.Parse(sqlReader["Discount"].ToString()); studentDetails.paymentType = sqlReader["Pay_Type"].ToString(); } if (studentDetails.studentID == 0) return null; return studentDetails; }
public static List<infoStudent> GetAllStudents() { SqlCeConnection sqlConnection = new SqlCeConnection(connectionString); SqlCeCommand sqlCommand = sqlConnection.CreateCommand(); List<infoStudent> allStudents = new List<infoStudent>(); sqlCommand.CommandText = "SELECT Account_Num, First_Name, Last_Name, Last_Name_2, School_Level, [Group] " + "FROM Students ORDER BY School_Level DESC, [Group]"; sqlConnection.Open(); SqlCeDataReader sqlReader = sqlCommand.ExecuteReader(); while (sqlReader.Read()) { infoStudent studentDetails = new infoStudent(); studentDetails.studentID = Int32.Parse(sqlReader["Account_Num"].ToString()); studentDetails.studentFistName = sqlReader["First_Name"].ToString(); studentDetails.studentLastName = sqlReader["Last_Name"].ToString(); studentDetails.studentLastName2 = sqlReader["Last_Name_2"].ToString(); studentDetails.studentLevel = int.Parse(sqlReader["School_Level"].ToString()); studentDetails.studentGroup = int.Parse(sqlReader["Group"].ToString()); allStudents.Add(studentDetails); } sqlConnection.Close(); return allStudents; }
private void txtbAccNum_TextChanged(object sender, EventArgs e) { if (txtbAccNum.Text != "") { //Get the select student details (or try) selectedStudent = DAL.getStudentDetails(Int32.Parse(txtbAccNum.Text)); studentToPay = selectedStudent; //If a student was actually found if (selectedStudent != null) { //Flag to show that loading is in process finishedLoading = false; ConceptList = BAL.GetUnpayedConcepts(selectedStudent); if (selectedStudent.paymentType == "Diferido") { BAL.ModifyDiferidoConcepts(ConceptList); } //Show the payment details box gbxPaymentDetails.Visible = true; //Load selected concept list depending on selected type cbPaymentConcept.ValueMember = "Value"; cbPaymentConcept.DisplayMember = "Name"; cbPaymentConcept.DataSource = ConceptList; //Enable controls and all that cbPaymentConcept.Enabled = true; picAccNumber.Image = (Image)CCMM.Properties.Resources.CheckMark; llinkViewAccDetails.Visible = true; llinkViewAccDetails.Text = "[" + selectedStudent.studentFistName + " " + selectedStudent.studentLastName + "]"; lblAccType.Text = "Tipo de Cuenta: " + selectedStudent.paymentType; //Update amount cbPaymentConcept_SelectedIndexChanged(null, null); finishedLoading = true; return; } else { lblAccType.Text = ""; studentToPay = null; } } gbxPaymentDetails.Visible = false; llinkViewAccDetails.Visible = false; finishedLoading = false; picAccNumber.Image = (Image)Properties.Resources.Warning; }