public DataTable GetAllPaymentsInMonth(DateTime monthyear, int paytype, MonthlyReportType monthlyReportType)
{
if (DBCon.State == ConnectionState.Open)
{
dbClose();
}
dbOpen();
MySqlCommand cmd = new MySqlCommand();
cmd.Connection = DBCon;
if ((paytype == 1 || paytype == 2) && (monthlyReportType == MonthlyReportType.HSStandard || monthlyReportType == MonthlyReportType.HSNonStandard))
{
cmd.CommandText = "SELECT idpayment as ID, DATE(dateofpayment) as 'DATE', ornumber as 'OR', amount as 'TOTAL' FROM (((payment INNER JOIN assessment ON payment.idassessment=assessment.idassessment) INNER JOIN registration ON assessment.idregistration=registration.idregistration) INNER JOIN gradelevel ON registration.gradelevel=gradelevel.gradename) INNER JOIN department ON gradelevel.iddepartment=department.iddepartment WHERE department.division=1 AND MONTH(dateofpayment)=" + monthyear.Month + " AND YEAR(dateofpayment)=" + monthyear.Year + " AND paymenttype=" + paytype + " ORDER BY DATE(dateofpayment), ornumber";
}
else if ((paytype == 1 || paytype == 2) && (monthlyReportType == MonthlyReportType.SHSStandard || monthlyReportType == MonthlyReportType.SHSNonStandard))
{
cmd.CommandText = "SELECT idpayment as ID, DATE(dateofpayment) as 'DATE', ornumber as 'OR', amount as 'TOTAL' FROM (((payment INNER JOIN assessment ON payment.idassessment=assessment.idassessment) INNER JOIN registration ON assessment.idregistration=registration.idregistration) INNER JOIN gradelevel ON registration.gradelevel=gradelevel.gradename) INNER JOIN department ON gradelevel.iddepartment=department.iddepartment WHERE department.division=2 AND MONTH(dateofpayment)=" + monthyear.Month + " AND YEAR(dateofpayment)=" + monthyear.Year + " AND paymenttype=" + paytype + " ORDER BY DATE(dateofpayment), ornumber";
}
else if (paytype == 1 || paytype == 2 && (monthlyReportType == MonthlyReportType.AllStandard || monthlyReportType == MonthlyReportType.AllNonStandard))
{
cmd.CommandText = "SELECT idpayment as ID, DATE(dateofpayment) as 'DATE', ornumber as 'OR', amount as 'TOTAL' FROM payment WHERE MONTH(dateofpayment)=" + monthyear.Month + " AND YEAR(dateofpayment)=" + monthyear.Year + " AND paymenttype=" + paytype + " ORDER BY DATE(dateofpayment), ornumber";
}
else
{
if (monthlyReportType == MonthlyReportType.HSOtherType)
{
cmd.CommandText = "SELECT idpayment as ID, DATE(dateofpayment) as 'DATE', ornumber as 'OR', amount as 'TOTAL' FROM payment WHERE payment.division=1 AND MONTH(dateofpayment)=" + monthyear.Month + " AND YEAR(dateofpayment)=" + monthyear.Year + " AND (paymenttype=3 OR paymenttype=4 OR paymenttype=5) ORDER BY DATE(dateofpayment), ornumber";
}
else if (monthlyReportType == MonthlyReportType.SHSOtherType)
{
cmd.CommandText = "SELECT idpayment as ID, DATE(dateofpayment) as 'DATE', ornumber as 'OR', amount as 'TOTAL' FROM payment WHERE payment.division=2 AND MONTH(dateofpayment)=" + monthyear.Month + " AND YEAR(dateofpayment)=" + monthyear.Year + " AND (paymenttype=3 OR paymenttype=4 OR paymenttype=5) ORDER BY DATE(dateofpayment), ornumber";
}
else
{
cmd.CommandText = "SELECT idpayment as ID, DATE(dateofpayment) as 'DATE', ornumber as 'OR', amount as 'TOTAL' FROM payment WHERE MONTH(dateofpayment)=" + monthyear.Month + " AND YEAR(dateofpayment)=" + monthyear.Year + " AND (paymenttype=3 OR paymenttype=4 OR paymenttype=5) ORDER BY DATE(dateofpayment), ornumber";
}
}
DataTable table = new DataTable();
using (MySqlDataReader dbReader = cmd.ExecuteReader())
{
table.Load(dbReader);
}
dbClose();
return(table);
}
public MonthlyDetailReport(ReportViewer rv, string report, DateTime dateTime, int paytype, MonthlyReportType monthlyReportType) : base(rv, report)
{
List <LedgerItem> ledgerItems = new List <LedgerItem>();
DataTable fees;
if (paytype == 1)
{
fees = new Fee().GetAllStandardFees();
}
else if (paytype == 2)
{
fees = new MonthlyDetail().GetAllNonStandardFees();
}
else
{
fees = new MonthlyDetail().GetAllOtherFees();
}
DataTable payments = new MonthlyDetail().GetAllPaymentsInMonth(dateTime, paytype, monthlyReportType);
DataTable payDetails = new DataTable();
List <LedgerItem> ledger = new List <LedgerItem>();
foreach (DataRow prow in payments.Rows)
{
long id = Convert.ToInt64(prow["ID"]);
payDetails = new MonthlyDetail().GetPaymentDetailById(id);
List <PaymentDetail> paylist = new List <PaymentDetail>();
foreach (DataRow plist in payDetails.Rows)
{
PaymentDetail pd = new PaymentDetail();
pd.Code = plist["CODE"].ToString();
pd.Amount = Convert.ToDouble(plist["AMOUNT"]);
paylist.Add(pd);
}
//check if item is present in pay type, this is good for fees that have categories transfered
foreach (PaymentDetail pitem in paylist)
{
bool present = false;
foreach (DataRow row in fees.Rows)
{
if (row["CODE"].ToString().Equals(pitem.Code))
{
present = true;
break;
}
}
if (!present)
{
DataRow dr = fees.NewRow();
DataTable dt = new DataTable();
dt = new Fee().GetFeeByCode(pitem.Code);
foreach (DataRow drow in dt.Rows)
{
dr[0] = drow["feecode"].ToString();
dr[1] = drow["feename"].ToString();
dr[2] = drow["shortname"].ToString();
dr[3] = Convert.ToInt16(drow["feetype"]);
}
fees.Rows.Add(dr);
}
}
foreach (DataRow row in fees.Rows)
{
LedgerItem ledgerItem = new LedgerItem();
ledgerItem.FeeCode = row["CODE"].ToString();
ledgerItem.FeeName = row["SHORT NAME"].ToString();
ledgerItem.OrNumber = prow["OR"].ToString();
ledgerItem.PaymentDate = Convert.ToDateTime(prow["DATE"]);
//ledgerItem.AssessmentAmount = Convert.ToDouble(row["AMOUNT"]);
//ledgerItem.AssessmentTotal = Assess.Total;
foreach (PaymentDetail det in paylist)
{
if (ledgerItem.FeeCode.Equals(det.Code))
{
ledgerItem.PaidAmount = det.Amount;
break;
}
else
{
ledgerItem.PaidAmount = 0;
}
}
ledger.Add(ledgerItem);
}
}
MonthlyReportTitle title;
if (monthlyReportType == MonthlyReportType.HSStandard)
{
title = new MonthlyReportTitle("STANDARD SCHOOL FEES: JHS, Elem, Pre-school");
}
else if (monthlyReportType == MonthlyReportType.HSNonStandard)
{
title = new MonthlyReportTitle("NON-STANDARD SCHOOL FEES: JHS, Elem, Pre-school");
}
else if (monthlyReportType == MonthlyReportType.SHSStandard)
{
title = new MonthlyReportTitle("STANDARD SCHOOL FEES: Senior High School");
}
else if (monthlyReportType == MonthlyReportType.SHSNonStandard)
{
title = new MonthlyReportTitle("NON-STANDARD SCHOOL FEES: Senior High School");
}
else if (monthlyReportType == MonthlyReportType.AllStandard)
{
title = new MonthlyReportTitle("STANDARD SCHOOL FEES: All Department");
}
else if (monthlyReportType == MonthlyReportType.AllNonStandard)
{
title = new MonthlyReportTitle("NON-STANDARD SCHOOL FEES: All Department");
}
else if (monthlyReportType == MonthlyReportType.HSOtherType)
{
title = new MonthlyReportTitle("OTHER SCHOOL FEES: JHS, Elem, Pre-school");
}
else if (monthlyReportType == MonthlyReportType.SHSOtherType)
{
title = new MonthlyReportTitle("OTHER SCHOOL FEES: Senior High School");
}
else
{
title = new MonthlyReportTitle("OTHER SCHOOL FEES: All Department");
}
List <MonthlyReportTitle> titles = new List <MonthlyReportTitle>();
titles.Add(title);
ReportDataSource dsReport = new ReportDataSource();
dsReport.Name = "dsPayment";
dsReport.Value = ledger;
RViewer.LocalReport.DataSources.Add(dsReport);
ReportDataSource dsTitle = new ReportDataSource();
dsTitle.Name = "dsTitle";
dsTitle.Value = titles;
RViewer.LocalReport.DataSources.Add(dsTitle);
}
