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;
}
