/// <summary> /// Method to Get DunningLetters /// </summary> /// <param name="argEn">DunningLetters Entity is an Input.</param> /// <returns>Returns List of DunningLetters.</returns> public List <DunningLettersEn> CheckDunningListing(DunningLettersEn argEn, string MatricNo) { DunningLettersDAL lods = new DunningLettersDAL(); List <DunningLettersEn> loEnList = new List <DunningLettersEn>(); string sqlCmd = "SELECT distinct SAS_dunningletterwarning.SAS_MatricNo, SAS_dunningletterwarning.SADL_Semester, " + "SAS_dunningletterwarning.SADL_Warn,SAS_dunningletterwarning.SADL_Code " + "FROM SAS_dunningletterwarning WHERE SAS_dunningletterwarning.SAS_MatricNo = '" + MatricNo + "' Order By SAS_dunningletterwarning.SAS_MatricNo"; try { if (!FormHelp.IsBlank(sqlCmd)) { using (IDataReader loReader = _DatabaseFactory.ExecuteReader(Helper.GetDataBaseType, DataBaseConnectionString, sqlCmd).CreateDataReader()) { while (loReader.Read()) { DunningLettersEn loItem = LoadObject3(loReader); loEnList.Add(loItem); } loReader.Close(); } } } catch (Exception ex) { throw ex; } return(loEnList); }
/// <summary> /// Method to Get DunningLetters /// </summary> /// <param name="argEn">DunningLetters Entity is an Input.</param> /// <returns>Returns List of DunningLetters.</returns> /// Modified by Hafiz @ 08/3/2016 /// Fix errors when SASI_StatusRec LIKE '%' - statusRec=Boolean public List <DunningLettersEn> GetListDunning(DunningLettersEn argEn) { DunningLettersDAL lods = new DunningLettersDAL(); List <DunningLettersEn> loEnList = new List <DunningLettersEn>(); string sqlCmd = "SELECT distinct SAS_Student.SASI_MatricNo, SAS_Student.SASI_CurSemYr, " + "case WHEN SAS_dunningletterwarning.SADL_Warn is null Then '1' " + "when SAS_dunningletterwarning.SADL_Warn = '0' then '1' " + "when SAS_dunningletterwarning.SADL_Warn = '1' then '2' " + "when SAS_dunningletterwarning.SADL_Warn = '2' then '3' " + "else 'overlimit' end SADL_Warn,case when SAS_dunningletterwarning.SADL_Code is null Then 'W1' " + "when SAS_dunningletterwarning.SADL_Code = 'W0' then 'W1' " + "when SAS_dunningletterwarning.SADL_Code = 'W1' then 'W2' " + "when SAS_dunningletterwarning.SADL_Code = 'W2' then 'W3' " + "else 'overlimit' end SADL_Code " + "FROM SAS_Program, SAS_Accounts INNER JOIN SAS_Student ON SAS_Accounts.CreditRef = " + "SAS_Student.SASI_MatricNo left join SAS_dunningletterwarning on " + "(SAS_dunningletterwarning.SAS_MatricNo = SAS_Student.SASI_MatricNo and SAS_dunningletterwarning.SADL_Semester = SAS_Student.SASI_CurSemYr)" + "WHERE SAS_Program.SAPG_Code = " + "SAS_Student.SASI_PgId and SAS_Accounts.SubType = 'Student' and SAS_Accounts.PostStatus = 'Posted' " + "and SAS_Student.SASI_StatusRec IN ('0','1') And ((Select SUM(SAC.TransAmount) as DebitAmount from " + "SAS_Accounts SAC Where SAC.CreditRef = SAS_Accounts.CreditRef and SAC.TransType = 'Debit' And " + "SAC.poststatus = 'Posted') < (Select SUM(SAC.TransAmount) as CreditAmount from " + "SAS_Accounts SAC Where SAC.CreditRef = SAS_Accounts.CreditRef and SAC.TransType = 'Credit' And " + "SAC.poststatus = 'Posted')) "; sqlCmd = sqlCmd + " Order By SAS_Student.SASI_MatricNo"; try { if (!FormHelp.IsBlank(sqlCmd)) { using (IDataReader loReader = _DatabaseFactory.ExecuteReader(Helper.GetDataBaseType, DataBaseConnectionString, sqlCmd).CreateDataReader()) { while (loReader.Read()) { DunningLettersEn loItem = LoadObject2(loReader); loEnList.Add(loItem); lods.InsertDunning(loItem); } loReader.Close(); } } } catch (Exception ex) { throw ex; } return(loEnList); }