public static bool updatePayment(int[] seqNo, int tuitionSeqNo, float payment, int StudID, float mscFee, int semNo, float checkAmount = 0)
{
// query for mscFee
string queryMscFee = "UPDATE Student_Account SET Payment = Amount , Balance = (Amount - ISNULL(Payment,0)) WHERE StudID = " + StudID + " AND SeqNo != " + tuitionSeqNo + " AND SemNo = " + semNo;
// query for tuitionFee
// Some adjustments for check payment
string queryTuitionFee = "UPDATE Student_Account SET Payment = ISNULL(Payment, 0 ) + (" + (payment - mscFee) + ") , Balance = (Amount - (ISNULL(Payment,0) +" + (payment - mscFee) + " ) ) WHERE SeqNo = " + tuitionSeqNo + " AND StudID = " + StudID + " AND SemNo = " + semNo;
// count seqNo
int count = seqNo.Count();
bool result = false;
for (int i = 0; i < count; i++)
{
result = new clsDB().Con().ExecuteSql(queryMscFee);
}
if (result)
{
if( new clsDB().Con().ExecuteSql(queryTuitionFee))
{
return true;
}
}
return result;
}
public static int getNumberOfOP()
{
int NoOfOP = 0;
try
{
string query = "SELECT OPNo From tbl_PayOrder WHERE DateIssued = '" + DateTime.Now.ToShortDateString() + "'";
NoOfOP = new clsDB().Con().countRecord(query);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
return(NoOfOP);
}
public static bool hasDuplicate(string columnName, string colValue, string table)
{
string sql;
if (IsNumeric(colValue))
sql = "SELECT * FROM " + table + " WHERE " + columnName + " = " + colValue ;
else
sql = "SELECT * FROM " + table + " WHERE " + columnName + " = '" + colValue +"'";
bool hasDuplicate = new clsDB().Con().SelectData(sql);
if (hasDuplicate)
{
MessageBox.Show("Duplicate Entry ( " + columnName + ")" );
return true;
}
return false;
}
public bool addOPItem(int orderOfPaymentNo, Dictionary <string, float> amountPerParticular, int OPType = 0, string NSTPType = null)
{
string query = "";
bool isValid = false;
int accountCode = 0;
for (int i = 0; i < amountPerParticular.Count; i++)
{
if (!string.IsNullOrEmpty(NSTPType))
{
if (amountPerParticular.Keys.ToList()[i] == "CWTS/ROTC")
{
// Need to remove a existing key in the dictionary because it is not possible to update a key value
float temp = amountPerParticular[amountPerParticular.Keys.ToList()[i]];
amountPerParticular.Remove("CWTS/ROTC");
amountPerParticular.Add(NSTPType, temp);
accountCode = OrderOfPayment.assessmentID(NSTPType);
}
else
{
accountCode = OrderOfPayment.assessmentID(amountPerParticular.Keys.ToList()[i]);
}
}
else
{
accountCode = OrderOfPayment.assessmentID(amountPerParticular.Keys.ToList()[i]);
}
query = "INSERT INTO tbl_PayOrder_Details (OPSeqNo, AcctCode, particular, amount, OPType) VALUES (" + orderOfPaymentNo + "," + accountCode + ", '" + amountPerParticular.Keys.ToList()[i] + "'," + amountPerParticular[amountPerParticular.Keys.ToList()[i]] + "," + OPType + ")";
isValid = new clsDB().Con().ExecuteSql(query);
}
return(isValid);
}
public static bool hasDuplicate(string columnName, string colValue, string table)
{
string sql;
if (IsNumeric(colValue))
{
sql = "SELECT * FROM " + table + " WHERE " + columnName + " = " + colValue;
}
else
{
sql = "SELECT * FROM " + table + " WHERE " + columnName + " = '" + colValue + "'";
}
bool hasDuplicate = new clsDB().Con().SelectData(sql);
if (hasDuplicate)
{
MessageBox.Show("Duplicate Entry ( " + columnName + ")");
return(true);
}
return(false);
}
// ***************** DAILY REPORT LAYOUT 1 PRINTING *******************
void ePrint_dailyReportLayoutOne(object sender, PrintPageEventArgs e)
{
// ------- VARIABLES -------
string queryMain = "SELECT OP.SeqNo,OP.SemNo,OfRec.Date_Paid, OfRec.ORNumber,OP.Payor,OfRec.Amount as ORAmount,OP.DateIssued,OP.OPNo,OP.Amount as OPAmount,OP.Purpose as Purpose,ISNULL(OfRec.PaymentType,0) as PaymentType FROM Collections as OfRec JOIN tbl_PayOrder as OP ON OP.OPNo = OfRec.OPNumber JOIN Collection_Details as CD ON CD.ORNumber = OfRec.ORNumber WHERE Date_Paid = '" + date + "' AND OPType = " + OPType + " " + pageIdentifier + " ORDER BY OfRec.ORNumber ASC";
string queryTemp = queryMain;
int xInc = 0, lineCounter = 0, recordCount = new clsDB().Con().countRecord(queryMain);
OPTotal = 0; ORTotal = 0;
clsCollection col = new clsCollection();
Graphics graphic = e.Graphics;
Font font = new Font("Arial", 10);
PageNo++;
// *** PRINT HEADER ***
headerPrintLayout1(graphic, e);
// check if For Summary
// SUMMARY
if (forSummary)
{
Dictionary <string, float> dataSummary = col.summaryOfCollection(OPType, date);
// SPACING
string[] space = { "", "", "", "", "", "", "", "", "" };
rowPrinting(graphic, e, xInc, space);
xInc += 21;
// For Summary Heading
string[] summaryOne = { "Summary : ", "", "", "", "", "", "", "", "" };
rowPrinting(graphic, e, xInc, summaryOne);
xInc += 21;
// Main Operation
for (int i = 0; i < dataSummary.Count; i++)
{
string[] summaryTwo = { "", "", dataSummary.Keys.ToList()[i], Convert.ToString(dataSummary[dataSummary.Keys.ToList()[i]]), "", "", "", "", "" };
summaryTotal += dataSummary[dataSummary.Keys.ToList()[i]];
rowPrinting(graphic, e, xInc, summaryTwo);
xInc += 21;
}
// For Summary Total
string[] summaryTotalHeader = { "", "", "Total : ", summaryTotal.ToString(), "", "", "", "", "" };
rowPrinting(graphic, e, xInc, summaryTotalHeader);
// *** PRINT FOOTER ***
footerPrintLayout1(graphic, e);
e.HasMorePages = false;
forSummary = false;
return;
}
// ******* MAIN PROCESS *******
for (int j = 0; j < recordCount; j++)
{
Dictionary <string, string> reportData = new Dictionary <string, string>();
new clsDB().Con().SelectDataDictionary(queryTemp, reportData);
// get Final Particular
if (reportData.Count() > 0)
{
OrderOfPayment OP = new OrderOfPayment();
// Check if Order Of Payment is Cancelled
if (int.Parse(reportData["PaymentType"]) == 9)
{
string[] data = { reportData["ORNumber"], "Code", "Cancelled", "", "", "", "", "", "" };
rowPrinting(graphic, e, xInc, data);
xInc += 21;
}
else
{
string particulars = OP.modConcatOrderOfPaymentItem(col.getCollectionItem(int.Parse(reportData["ORNumber"])), reportData["Purpose"]);
// get Semester
particulars += "-" + Semester.getCurrentSemesterString().Replace("Semester", "Sem").Replace("20", "");
string[] dataA = { reportData["ORNumber"], "Code", reportData["Payor"], particulars, reportData["ORAmount"], reportData["DateIssued"], reportData["OPNo"], reportData["OPAmount"], "" };
// Compute Total
OPTotal += float.Parse(reportData["OPAmount"]);
ORTotal += float.Parse(reportData["ORAmount"]);
rowPrinting(graphic, e, xInc, dataA);
xInc += 21;
}
// quick fix
queryTemp = "SELECT TOP 1 OP.SeqNo,OP.SemNo,OfRec.Date_Paid, OfRec.ORNumber,OP.Payor,OfRec.Amount as ORAmount,OP.DateIssued,OP.OPNo,OP.Amount as OPAmount,OP.Purpose as Purpose,ISNULL(OfRec.PaymentType,0) as PaymentType FROM Collections as OfRec JOIN tbl_PayOrder as OP ON OP.OPNo = OfRec.OPNumber JOIN Collection_Details as CD ON CD.ORNumber = OfRec.ORNumber WHERE Date_Paid = '" + date + "' AND OPType = " + OPType + " AND OPNo >" + reportData["OPNo"];
lineCounter++;
}
if (lineCounter > 19)
{
OPGrandTotal += OPTotal;
ORGrandTotal += ORTotal;
if (recordCount > 20)
{
hasMorePages = true;
pageIdentifier = " AND OPNo >" + reportData["OPNo"];
string[] dataTotal = { "", "", "", "Sub-Total", Convert.ToString(ORTotal), "", "", Convert.ToString(OPTotal), "" };
rowPrinting(graphic, e, xInc, dataTotal);
// *** PRINT FOOTER ***
footerPrintLayout1(graphic, e);
e.HasMorePages = true;
return;
}
}
}
/************ FINAL PRINTING ***************/
if (hasMorePages)
{
OPGrandTotal += OPTotal;
ORGrandTotal += ORTotal;
/*string[] dataTotal = { "", "", "", "Sub-Total", Convert.ToString(ORTotal), "", "", Convert.ToString(OPTotal), "" };
* rowPrinting(graphic, e, xInc, dataTotal);
*
* string[] dataGrandTotal = { "", "", "", "Grand Total", Convert.ToString(ORGrandTotal), "", "", Convert.ToString(OPGrandTotal), "" };
* rowPrinting(graphic, e, xInc + 21, dataGrandTotal);
*/
// *** PRINT FOOTER ***
footerPrintLayout1(graphic, e);
e.HasMorePages = false;
hasMorePages = false;
}
if (hasMorePages == false)
{
if (OPGrandTotal == 0 && ORGrandTotal == 0)
{
OPGrandTotal += OPTotal;
ORGrandTotal += ORTotal;
}
string[] dataTotal = { "", "", "", "Sub-Total", Convert.ToString(ORTotal), "", "", Convert.ToString(OPTotal), "" };
rowPrinting(graphic, e, xInc, dataTotal);
string[] dataGrandTotal = { "", "", "", "Grand Total", Convert.ToString(ORGrandTotal), "", "", Convert.ToString(OPGrandTotal), "" };
rowPrinting(graphic, e, xInc + 21, dataGrandTotal);
// *** PRINT FOOTER ***
footerPrintLayout1(graphic, e);
forSummary = true;
e.HasMorePages = true;
}
}
private void btnSave_Click(object sender, EventArgs e)
{
bool hasPayment = false;
if (float.Parse(tbAmount.Text) < float.Parse(lblAmountToPay.Text))
{
MessageBox.Show(" Invalid Amount");
}
else
{
string query = "SELECT SA.SeqNo, SA.StudID, SA.SemNo, SA.Particular, SA.Amount, ISNULL(SA.Payment,0), ISNULL(SA.Balance,0),OP.Amount FROM Student_Account as SA JOIN tbl_PayOrder as OP ON SA.StudID = OP.StudID WHERE OP.OPNo = ";
int recordCount = new clsDB().Con().countRecord(query + orderOfPaymentNo);
string[][] data = new string[recordCount][];
// if it is for Student Account
if (recordCount > 0)
{
new clsDB().Con().SelectData(query + orderOfPaymentNo, data);
StudentAccount sa = new StudentAccount();
Dictionary<string, string> studentAccountData = sa.getData(data);
int tuitionSeqNo = int.Parse(studentAccountData["tuitionSeqNo"]);
float mFee = float.Parse(studentAccountData["mFee"]);
int StudID = int.Parse(studentAccountData["StudID"]);
int semNo = int.Parse(studentAccountData["SemNo"]);
if (StudentAccount.updatePayment(sa.seqNo, tuitionSeqNo, float.Parse(tbAmount.Text), StudID, mFee, semNo))
{
hasPayment = true;
}
else
{
MessageBox.Show("There was an error!");
}
}
// if for Misc Fees and other payments
else
{
hasPayment = true;
}
}
// Final Procedure
if (hasPayment)
{
string checkAmount = null, checkDate = null, checkNo = null, bankName = null, PaymentType = null ;
OPData.TryGetValue("CheckAmount", out checkAmount);
OPData.TryGetValue("CheckNo", out checkNo);
OPData.TryGetValue("CheckDate", out checkDate);
OPData.TryGetValue("BankName", out bankName);
OPData.TryGetValue("PaymentType", out PaymentType);
clsCollection col = new clsCollection(orderOfPaymentNo, int.Parse(OrderOfPayment.getLastORNo()), dtORDate.Value.ToShortDateString(), float.Parse(tbAmount.Text), OPData["Payor"].Replace("'","''"), checkNo,bankName, checkDate, float.Parse(checkAmount), int.Parse(PaymentType));
if (OP.updateOP(int.Parse(tbORNo.Text), orderOfPaymentNo))
if (col.create())
{
OP.paidOP(orderOfPaymentNo);
col.addCollectionItem();
string[][] particularsAmount = OP.getOrderOfPaymentItem(orderOfPaymentNo);
OPData["collectionDate"] = dtORDate.Value.ToShortDateString();
int currOrNumber = int.Parse(OrderOfPayment.getLastORNo()) - 1;
OPData["ORNumber"] = ""+currOrNumber;
ePrinting print = new ePrinting(OPData, particularsAmount);
// temporary fix for OTHER Payment
if (OPData["Purpose"] == "Other Fees")
print.isOtherPayment = true;
print.ePrint("OR");
Close();
}
}
}
private void btnAddParticular_Click_1(object sender, EventArgs e)
{
string particular = "";
string query = "Select AssessmentID, AssessmentName, ISNULL(Amount,0) from assessment WHERE AssessmentName = '";
if (cmbParticular.SelectedIndex > -1)
particular = cmbParticular.SelectedItem.ToString();
else
particular = cmbParticular.Text;
try
{
if (!string.IsNullOrEmpty(particular))
{
int count = new clsDB().Con().countRecord(query + particular + "'");
string[] column = { "No", "Particulars", "Amount"};
string[][] data = new string[count][];
string[] obj = new string[3];
bool hasAmount = false;
// if has record in the database - get data for transfer
if (count > 0)
{
if (new clsDB().Con().SelectData(query + particular + "'", obj))
{
data[0] = obj;
// check if the amount is zero and ask for input
if (float.Parse(data[0][2]) == 0 || data[0][1] == "Tuition Fee")
{
frmParticularAmountDataEntry f = new frmParticularAmountDataEntry(int.Parse(data[0][0]));
// FLAG for TUITION FEE
if (data[0][1] == "Tuition Fee")
{
f.isTuitionFee = true;
isTuitionFee = true;
}
f.ShowDialog();
data[0][2] = Convert.ToString(f.amount);
hasAmount = f.hasAmount;
}
else
hasAmount = true;
if (hasAmount && new clsDB().Con().listViewTransferData(data, lvDataEntryOP, column))
{
float[] account = Payor.computeAccount(lvDataEntryOP);
// Set TEMPORARY FIX for TUITION PAYMENT - G
lbTotal.Text = Convert.ToString(account[0] + account[1]);
tAmount.Text = Convert.ToString(account[0] + account[1]);
/**
lbTotal.Text = Convert.ToString(account[1]);
tAmount.Text = Convert.ToString(account[1]);
*/
}
}
}
else
{
pNewParticular.Visible = true;
}
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
// ***************** DAILY REPORT LAYOUT 1 PRINTING *******************
void ePrint_dailyReportLayoutOne(object sender, PrintPageEventArgs e)
{
// ------- VARIABLES -------
string queryMain = "SELECT OP.SeqNo,OP.SemNo,OfRec.Date_Paid, OfRec.ORNumber,OP.Payor,OfRec.Amount as ORAmount,OP.DateIssued,OP.OPNo,OP.Amount as OPAmount,OP.Purpose as Purpose,ISNULL(OfRec.PaymentType,0) as PaymentType FROM Collections as OfRec JOIN tbl_PayOrder as OP ON OP.OPNo = OfRec.OPNumber JOIN Collection_Details as CD ON CD.ORNumber = OfRec.ORNumber WHERE Date_Paid = '" + date + "' AND OPType = " + OPType + " " + pageIdentifier + " ORDER BY OfRec.ORNumber ASC";
string queryTemp = queryMain;
int xInc = 0, lineCounter = 0, recordCount = new clsDB().Con().countRecord(queryMain);
OPTotal = 0; ORTotal = 0;
clsCollection col = new clsCollection();
Graphics graphic = e.Graphics;
Font font = new Font("Arial", 10);
PageNo++;
// *** PRINT HEADER ***
headerPrintLayout1(graphic, e);
// check if For Summary
// SUMMARY
if (forSummary)
{
Dictionary<string, float> dataSummary = col.summaryOfCollection(OPType, date);
// SPACING
string[] space = { "", "", "", "", "", "", "", "", "" };
rowPrinting(graphic, e, xInc, space);
xInc += 21;
// For Summary Heading
string[] summaryOne = { "Summary : ", "", "", "", "", "", "", "", "" };
rowPrinting(graphic, e, xInc, summaryOne);
xInc += 21;
// Main Operation
for (int i = 0; i < dataSummary.Count; i++)
{
string[] summaryTwo = { "", "", dataSummary.Keys.ToList()[i], Convert.ToString(dataSummary[dataSummary.Keys.ToList()[i]]), "", "", "", "", ""};
summaryTotal += dataSummary[dataSummary.Keys.ToList()[i]];
rowPrinting(graphic, e, xInc, summaryTwo);
xInc += 21;
}
// For Summary Total
string[] summaryTotalHeader = { "", "", "Total : ", summaryTotal.ToString(), "", "", "", "", "" };
rowPrinting(graphic, e, xInc, summaryTotalHeader);
// *** PRINT FOOTER ***
footerPrintLayout1(graphic, e);
e.HasMorePages = false;
forSummary = false;
return;
}
// ******* MAIN PROCESS *******
for (int j = 0; j < recordCount; j++ )
{
Dictionary<string, string> reportData = new Dictionary<string, string>();
new clsDB().Con().SelectDataDictionary(queryTemp, reportData);
// get Final Particular
if (reportData.Count() > 0)
{
OrderOfPayment OP = new OrderOfPayment();
// Check if Order Of Payment is Cancelled
if (int.Parse(reportData["PaymentType"]) == 9)
{
string[] data = { reportData["ORNumber"], "Code", "Cancelled", "", "", "", "", "", "" };
rowPrinting(graphic, e, xInc, data);
xInc += 21;
}
else
{
string particulars = OP.modConcatOrderOfPaymentItem(col.getCollectionItem(int.Parse(reportData["ORNumber"])), reportData["Purpose"]);
// get Semester
particulars += "-" + Semester.getCurrentSemesterString().Replace("Semester", "Sem").Replace("20", "");
string[] dataA = { reportData["ORNumber"], "Code", reportData["Payor"], particulars, reportData["ORAmount"], reportData["DateIssued"], reportData["OPNo"], reportData["OPAmount"], "" };
// Compute Total
OPTotal += float.Parse(reportData["OPAmount"]);
ORTotal += float.Parse(reportData["ORAmount"]);
rowPrinting(graphic, e, xInc, dataA);
xInc += 21;
}
// quick fix
queryTemp = "SELECT TOP 1 OP.SeqNo,OP.SemNo,OfRec.Date_Paid, OfRec.ORNumber,OP.Payor,OfRec.Amount as ORAmount,OP.DateIssued,OP.OPNo,OP.Amount as OPAmount,OP.Purpose as Purpose,ISNULL(OfRec.PaymentType,0) as PaymentType FROM Collections as OfRec JOIN tbl_PayOrder as OP ON OP.OPNo = OfRec.OPNumber JOIN Collection_Details as CD ON CD.ORNumber = OfRec.ORNumber WHERE Date_Paid = '" + date + "' AND OPType = " + OPType + " AND OPNo >" + reportData["OPNo"];
lineCounter++;
}
if (lineCounter > 19)
{
OPGrandTotal += OPTotal;
ORGrandTotal += ORTotal;
if (recordCount > 20)
{
hasMorePages = true;
pageIdentifier = " AND OPNo >" + reportData["OPNo"];
string[] dataTotal = { "", "", "", "Sub-Total", Convert.ToString(ORTotal), "", "", Convert.ToString(OPTotal), "" };
rowPrinting(graphic, e, xInc, dataTotal);
// *** PRINT FOOTER ***
footerPrintLayout1(graphic, e);
e.HasMorePages = true;
return;
}
}
}
/************ FINAL PRINTING ***************/
if (hasMorePages)
{
OPGrandTotal += OPTotal;
ORGrandTotal += ORTotal;
/*string[] dataTotal = { "", "", "", "Sub-Total", Convert.ToString(ORTotal), "", "", Convert.ToString(OPTotal), "" };
rowPrinting(graphic, e, xInc, dataTotal);
string[] dataGrandTotal = { "", "", "", "Grand Total", Convert.ToString(ORGrandTotal), "", "", Convert.ToString(OPGrandTotal), "" };
rowPrinting(graphic, e, xInc + 21, dataGrandTotal);
*/
// *** PRINT FOOTER ***
footerPrintLayout1(graphic, e);
e.HasMorePages = false;
hasMorePages = false;
}
if(hasMorePages == false)
{
if (OPGrandTotal == 0 && ORGrandTotal == 0)
{
OPGrandTotal += OPTotal;
ORGrandTotal += ORTotal;
}
string[] dataTotal = { "", "", "", "Sub-Total", Convert.ToString(ORTotal), "", "", Convert.ToString(OPTotal), "" };
rowPrinting(graphic, e, xInc, dataTotal);
string[] dataGrandTotal = { "", "", "", "Grand Total", Convert.ToString(ORGrandTotal), "", "", Convert.ToString(OPGrandTotal), "" };
rowPrinting(graphic, e, xInc + 21, dataGrandTotal);
// *** PRINT FOOTER ***
footerPrintLayout1(graphic, e);
forSummary = true;
e.HasMorePages = true;
}
}
private bool hasNoDuplicate(int orderOfPaymentNo)
{
string[] obj = new string[6];
bool isValid = new clsDB().Con().SelectData("Select * FROM tbl_PayOrder WHERE OPNo = " + orderOfPaymentNo, obj);
return isValid;
}
public bool addOPItem(int orderOfPaymentNo, Dictionary<string,float> amountPerParticular, int OPType = 0, string NSTPType = null)
{
string query = "";
bool isValid = false;
int accountCode = 0;
for(int i = 0 ; i < amountPerParticular.Count; i++)
{
if (!string.IsNullOrEmpty(NSTPType))
{
if (amountPerParticular.Keys.ToList()[i] == "CWTS/ROTC")
{
// Need to remove a existing key in the dictionary because it is not possible to update a key value
float temp = amountPerParticular[amountPerParticular.Keys.ToList()[i]];
amountPerParticular.Remove("CWTS/ROTC");
amountPerParticular.Add(NSTPType, temp);
accountCode = OrderOfPayment.assessmentID(NSTPType);
}
else
accountCode = OrderOfPayment.assessmentID(amountPerParticular.Keys.ToList()[i]);
}
else
accountCode = OrderOfPayment.assessmentID(amountPerParticular.Keys.ToList()[i]);
query = "INSERT INTO tbl_PayOrder_Details (OPSeqNo, AcctCode, particular, amount, OPType) VALUES ("+orderOfPaymentNo+","+accountCode+", '"+ amountPerParticular.Keys.ToList()[i] +"',"+ amountPerParticular[amountPerParticular.Keys.ToList()[i]] + "," + OPType +")";
isValid = new clsDB().Con().ExecuteSql(query);
}
return isValid;
}
public static int getNumberOfOP()
{
string query = "SELECT OPNo From tbl_PayOrder WHERE DateIssued = " + DateTime.Now.ToShortDateString();
int NoOfOP = new clsDB().Con().countRecord(query);
return NoOfOP;
}
