/// <summary> /// Method to Get Succeed Transaction /// </summary> /// <param name="argEn">Accounts Entity is an Input</param> /// <returns>Returns List of Accounts</returns> public List <AccountsDetailsEn> GetItemDetails(AccountsEn argEn) { try { AccountsDetailsDAL loDs = new AccountsDetailsDAL(); return(loDs.GetItemDetails(argEn)); } catch (Exception ex) { throw ex; } }
/// <summary> /// Method to GetStuDentAllocationPocketAmount /// </summary> /// <param name="argEn">AccountDetails Entity is an Input.TransactionID is an Input Property</param> /// <returns>Returns List of AccountsDetails Entities</returns> public List <AccountsDetailsEn> GetStuDentAllocationPocketAmount(AccountsEn argEn) { try { AccountsDetailsDAL loDs = new AccountsDetailsDAL(); return(loDs.GetStuDentAllocationPocketAmount(argEn)); } catch (Exception ex) { throw ex; } }
/// <summary> /// Method to Get Student AccountDetails /// </summary> /// <param name="argEn">AccountsDetails Entity is an Input.TransactionId is an Input Property</param> /// <returns>Returns List Of AccountDetails Entities</returns> //public List<AccountsDetailsEn> GetStudentAccountsDetailsByBatchCode(AccountsEn argEn) //{ // try // { // AccountsDetailsDAL loDs = new AccountsDetailsDAL(); // return loDs.GetStudentAccountsDetailsByBatchCode(argEn); // } // catch (Exception ex) // { // throw ex; // } //} public List <AccountsDetailsEn> GetStudentAccountsDetailsByBatchCode(AccountsEn argEn, String m_no) { try { AccountsDetailsDAL loDs = new AccountsDetailsDAL(); return(loDs.GetStudentAccountsDetailsByBatchCode(argEn, m_no)); } catch (Exception ex) { throw ex; } }
/// <summary> /// Method to Get Student Sponsor Invoice AccountsDetails /// </summary> /// <param name="argEn">AccountsDetails Entity is an Input.TransactionId is an Input Property</param> /// <returns>Returns List Of AccountDetails Entities</returns> public List <AccountsDetailsEn> GetStudentSponsorInvoiceAccountsDetailsByBatchCode(AccountsEn argEn) { try { AccountsDetailsDAL loDs = new AccountsDetailsDAL(); return(loDs.GetStudentSponsorInvoiceAccountsDetailsByBatchCode(argEn)); } catch (Exception ex) { throw ex; } }
public List <AccountsEn> GetDashboardV2Data() { string sqlCmd = String.Empty; List <AccountsEn> loEnList = new List <AccountsEn>(); sqlCmd = "SELECT ledger.Category,SUM(ledger.Debit) AS Outstanding_Amt FROM ( "; sqlCmd += @"(SELECT acc.PostedTimestamp, CASE WHEN acc.Category = 'Receipt' THEN CASE WHEN acc.SourceType = 'FER' THEN acc.batchdate ELSE ( SELECT date_time FROM SAS_workflow_status WHERE workflow_id = (SELECT workflow_id FROM SAS_workflow WHERE batch_code=acc.BatchCode) AND workflow_status=2 ORDER by date_time DESC LIMIT 1 ) END ELSE acc.TransDate END TransDate, CASE WHEN acc.Category = 'Receipt' THEN CASE WHEN acc.SourceType = 'FER' THEN acc.TransCode ELSE CASE WHEN acc.Description LIKE 'CIMB CLICKS%' THEN acc.TransCode ELSE CASE WHEN acc.Subcategory='Loan' THEN acc.TransCode ELSE acc.BankRecNo END END END ELSE CASE WHEN Category = 'Loan' THEN BatchCode ELSE acc.TransCode END END TransCode, CASE WHEN acc.Category = 'SPA' THEN ( SELECT Description FROM sas_accounts WHERE batchcode = acc.BatchCode and category = 'Allocation' and subtype = 'Sponsor' ) ELSE acc.Description END Description, acc.Category , CASE WHEN acc.category = 'Credit Note' or acc.category = 'Debit Note' or acc.category = 'Invoice' THEN CASE WHEN acc.transtype = 'Debit' then SUM(de.TransAmount) ELSE 0 END ELSE CASE WHEN acc.TransType = 'Debit' THEN acc.TransAmount ELSE 0 END END Debit, CASE WHEN acc.category = 'Credit Note' or acc.category = 'Debit Note' or acc.category = 'Invoice' THEN CASE WHEN acc.transtype = 'Credit' Then SUM(de.transamount) ELSE 0 END ELSE CASE WHEN acc.TransType = 'Credit' THEN acc.TransAmount ELSE 0 END END Credit, CASE WHEN acc.category = 'Credit Note' or acc.category = 'Debit Note' or acc.category = 'Invoice' THEN SUM(de.TransAmount) ELSE acc.TransAmount END TransAmount, CASE WHEN TransType = 'Credit' THEN CASE WHEN Category = 'Loan' THEN 'Debit' ELSE 'Credit' END ELSE 'Debit' END TransType, acc.BatchCode FROM SAS_Accounts acc left join sas_accountsdetails de on acc.transid = de.transid WHERE acc.SubType = 'Student' AND acc.PostStatus ='Posted' "; sqlCmd += @"AND acc.Category NOT IN ('Loan') AND acc.TransAmount > 0 GROUP BY acc.TransDate, acc.ReceiptDate, acc.BankRecNo, acc.Description, acc.Category, acc.Subcategory, acc.TransCode, acc.TransType, acc.BatchCode, acc.TransAmount, acc.postedtimestamp,acc.SourceType,acc.BatchDate) "; sqlCmd += "UNION "; sqlCmd += @"(SELECT SAS_StudentLoan.PostedTimestamp, TransDate, TransCode, Description, Category, --debit CASE WHEN Category = 'Receipt' THEN CASE WHEN TransType = 'Debit' THEN TransAmount ELSE 0 END ELSE CASE WHEN TransType = 'Credit' THEN TransAmount ELSE 0 END END Debit, --credit CASE WHEN Category = 'Receipt' THEN CASE WHEN TransType = 'Credit' THEN TransAmount ELSE 0 END ELSE CASE WHEN TransType = 'Debit' THEN TransAmount ELSE 0 END END Credit, TransAmount, CASE WHEN TransType = 'Credit' THEN CASE WHEN Category = 'Loan' THEN 'Debit' ELSE 'Credit' END ELSE 'Debit' END TransType, BatchCode FROM SAS_StudentLoan WHERE SubType = 'Student' AND PostStatus ='Posted' AND Category NOT IN ('Receipt') ) "; sqlCmd += ") ledger WHERE ledger.TransType <> 'Credit' GROUP BY ledger.Category"; try { if (!FormHelp.IsBlank(sqlCmd)) { using (IDataReader loReader = _DatabaseFactory.ExecuteReader(Helper.GetDataBaseType, DataBaseConnectionString, sqlCmd).CreateDataReader()) { while (loReader.Read()) { AccountsEn loItem = new AccountsEn(); loItem.Category = GetValue <string>(loReader, "Category"); loItem.TransactionAmount = GetValue <double>(loReader, "Outstanding_Amt"); loEnList.Add(loItem); } loReader.Close(); } } } catch (Exception ex) { throw ex; } return(loEnList); }