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