/// <summary>
/// Re-Synchronize payment advice list selected.
/// </summary>
public void ResyncPAList()
{
if (_CurrentPAList == null)
throw new ApasInvaidOperationException(
"System Error: No payment advice list exists for synchronization.");
PALockManager lMgr = new PALockManager(
ApasAccessControlManager.GetCurrentInstance().LogonUser.Id,
PALockKey);
//Hold all PAs within this PAlist
string whereClause =
" WHERE intIdPAList = @paListId ";
List<SqlParameter> pCol = new List<SqlParameter>();
SqlParameter param = new SqlParameter("@paListId", SqlDbType.BigInt);
param.Value = _CurrentPAList.Id;
pCol.Add(param);
lMgr.Hold(whereClause, pCol);
NHibernateManager.GetCurrentSession().Evict(_CurrentPAList);//remove it before reloading
_CurrentPAList.Load(_CurrentPAList.Id, false); //reload to get updated records
_CurrentPAList.SyncPaymentAdvices(PALockKey);
//Release PAs
pCol = new List<SqlParameter>();
param = new SqlParameter("@paListId", SqlDbType.BigInt);
param.Value = _CurrentPAList.Id;
pCol.Add(param);
lMgr.Release(whereClause, pCol);
}
public string GenerateSMSSpreadSheet(HttpResponse response)
{
if (_CurrentPAList == null)
throw new ApasInvaidOperationException(
"System Error: No payment advice list exists for SMS text generation.");
lock (criticalSection)
{
BCBranch[] aryBranch = BCBranch.getAllBranches();
ApasSearchManager sMgr = new ApasSearchManager(PALockKey);
//hold all PAs, halt if unsuccessful
writeResponse("lblPreparation", "Acquiring exclusive access...",
"blueMessage", response);
PALockManager lMgr = new PALockManager(
Apas.Security.ApasAccessControlManager.GetCurrentInstance().LogonUser.Id,
PALockKey);
List<BCAspenUser> oUsers = lMgr.HoldPAListExclusive(_CurrentPAList.Id);
if (oUsers.Count > 0)
{
String oUserStr = "";
foreach (BCAspenUser oUser in oUsers)
{
oUserStr += oUser.FullName + ", ";
}
oUserStr = oUserStr.Substring(0,oUserStr.Length - 2);
writeResponse("lblPreparation",
"Failed",
"redMessage", response);
foreach (BCBranch objBranch in aryBranch)
{
writeResponse("lbl" + objBranch.Name.Trim(),
"Canceled",
"redMessage", response);
}
writeResponse("lblFinalization",
"Canceled",
"redMessage", response);
return "Failed to acquire exclusive access. " + oUserStr +
" is/are currently holding some records.";
}
writeResponse("lblPreparation", "Updating records...",
"blueMessage", response);
//resync the PAs
NHibernateManager.GetCurrentSession().Evict(_CurrentPAList);//remove it before reloading
_CurrentPAList.Load(_CurrentPAList.Id, false); //reload to get updated records
if (_CurrentPAList.IsFrozen == 1)
_CurrentPAList.StaticSynchronizePaymentAdvices(PALockKey);
else
_CurrentPAList.SyncPaymentAdvices(PALockKey);
writeResponse("lblPreparation", "Initializing Excel...",
"blueMessage",response);
ApasExcelManager xl=new ApasExcelManager();
xl.InitExcelApplication();
writeResponse("lblPreparation", "Successful",
"blueMessage", response);
String[] aryDay = {"Monday", "Tuesday", "Wednesday", "Thursday",
"Friday", "Saturday", "Sunday"};
//String[] aryDay = { "Monday" };
int classCount;
int completeCount;
foreach (BCBranch objBranch in aryBranch)
{
writeResponse("lbl" + objBranch.Name.Trim(), "Initializing...",
"blueMessage", response);
String strFile = "/APAS/tmp Excel Files/" +
_CurrentPAList.Month.ToString("yyyyMM") +
" SMS " + objBranch.Name.Trim() + ".xls";
String strLocalFile = HttpContext.Current.Server.MapPath("~"+strFile);
xl.CreateNewFile(strLocalFile);
foreach (String day in aryDay)
{
writeResponse("lbl" + objBranch.Name.Trim(),
"Processing "+ day +": Acquiring data...",
"blueMessage", response);
ArrayList result = sMgr.SearchPaymentAdviceByDay(
CurrentPAList.Id,
day, objBranch.Id);
xl.AddNewWorkSheet(day);
xl.WriteHeading(
"Student Id",
"Class Id",
"Student Name",
"Guardian Name",
"Class Description",
"Credit Payable",
"Amount Payable",
"SMS Text",
"Contact Number"
);
classCount = result.Count;
completeCount=1;
foreach (ApasRegularView objReg in result)
{
writeResponse("lbl" + objBranch.Name.Trim(),
"Processing " + day + ": "+ completeCount +" of "+ classCount +" classes...",
"blueMessage", response);
foreach (PaymentAdviceView objPA in objReg.PaymentAdvices)
{
//don't include in SMS if excluced
if (objPA.IsExcluded )
continue;
foreach (ClassFeeView objCF in objPA.ClassFees)
{
if (objCF.IsExcluded )
continue;
xl.WriteRow(
Convert.ToString(objPA.Student.Id),
Convert.ToString(objCF.RegularClass.Id),
objPA.Student.FullName,
objPA.Student.Guardian.Name.Trim(),
String.Format("{0} {1} {2} - {3}",
objCF.RegularClass.Level,
objCF.RegularClass.Subject,
objCF.RegularClass.TimeStart,
objCF.RegularClass.TimeEnd),
Convert.ToString(objCF.PayableCredit),
Convert.ToString(objCF.PayableFee),
objCF.PaymentAdviceText,
String.IsNullOrEmpty(objPA.Student.Guardian.ContactMobile) ?
objPA.Student.ContactMobile : objPA.Student.Guardian.ContactMobile
);
}//objCF
foreach (OtherFeeView objOF in objPA.OtherFees)
{
if (objOF.IsExcluded)
continue;
xl.WriteRow(
Convert.ToString(objPA.Student.Id),
"N.A",
objPA.Student.FullName,
objPA.Student.Guardian.Name.Trim(),
objOF.OtherFeeType,
"N.A",
"N.A",
objOF.PaymentAdviceText,
String.IsNullOrEmpty(objPA.Student.Guardian.ContactMobile) ?
objPA.Student.ContactMobile : objPA.Student.Guardian.ContactMobile
);
}//objOF
}//objPA
completeCount++;
}//objReg
xl.FinalizeWorkSheet();
}//day
writeResponse("lbl" + objBranch.Name.Trim(),
"Finalizing...",
"blueMessage", response);
xl.SaveFile();
writeResponse("lbl" + objBranch.Name.Trim(),
"Successful : <a href=" + MyUtils.EncodeJsString(HttpContext.Current.Request.ApplicationPath+strFile) + " target=\"_blank\">Download Link</a>",
"blueMessage", response);
}//branch
writeResponse("lblFinalization", "Closing excel...",
"blueMessage", response);
xl.CloseExcelApplication();
writeResponse("lblFinalization", "Updating records...",
"blueMessage", response);
//has to reload. Dunno why
NHibernateManager.GetCurrentSession().Evict(_CurrentPAList);//remove it before reloading
_CurrentPAList.Load(_CurrentPAList.Id, false); //reload to get updated records
CurrentPAList.FreezePaymentAdvices(PALockKey);
CurrentPAList.SMSGenTime = DateTime.Now;
CurrentPAList.LastAction = "SMS Text Generated";
CurrentPAList.LastModifiedBy =
ApasAccessControlManager.GetCurrentInstance().LogonUser.Id;
CurrentPAList.LastModifiedTime = DateTime.Now;
CurrentPAList.Save();
lMgr.Release("", new List<SqlParameter>());
writeResponse("lblFinalization", "Successful",
"blueMessage", response);
}//critial section
return "success";
}
/// <summary>
/// release locks held by current user
/// </summary>
/// <returns></returns>
public bool ReleaseLocks()
{
if (CurrentPAList == null)
{
throw new ApasInvaidOperationException(
"Invalid Operation: Please select a payment advice list before " +
"performing release function.");
}
PALockManager lMgr = new PALockManager(
ApasAccessControlManager.GetCurrentInstance().LogonUser.Id,
PALockKey);
lMgr.Release("", new List<SqlParameter>());
return true;
}
/// <summary>
/// Release all locks regardless of who holds them
/// </summary>
/// <returns></returns>
public bool MasterReleaseLocks()
{
if (CurrentPAList == null)
{
throw new ApasInvaidOperationException(
"Invalid Operation: Please select a payment advice list before " +
"performing master release function.");
}
PALockManager lMgr = new PALockManager(0,"");
string whereClause = " WHERE intIdPAList = " + CurrentPAList.Id;
lMgr.Clear(whereClause, new List<SqlParameter>());
return true;
}
private IList<PaymentAdvice> executeQuery(long idPAList, IList<SearchPredicate> criteria)
{
//init NHibernate objects
ISession ses = NHibernateManager.GetCurrentSession();
ITransaction trans;
PALockManager lMgr = new PALockManager(
ApasAccessControlManager.GetCurrentInstance().LogonUser.Id,
PALockKey);
//release all previous locks
lMgr.Release("", new List<SqlParameter>());
//get base statement
ISQLQuery q =(ISQLQuery)
ses.GetNamedQuery
("Apas.Business.ApasSearchManager.BaseStatement");
string strSQL=q.QueryString;
//process search predicates
int paramIndex = 0;
Dictionary<string,string> c=new Dictionary<string,string>(); //a collection of query string parts
c.Add("AspenUser","");
c.Add("Student","");
c.Add("Guardian","");
c.Add("ClassSchedule","");
c.Add("Regular","");
c.Add("ClassFee","");
c.Add("OtherFee","");
c.Add("FeeDeviationPayableFee","");
c.Add("FeeDeviation","");
c.Add("Branch", "");
c.Add("Classroom", "");
string cCurrent;
string alias;
string temp;
foreach (SearchPredicate p in criteria)
{
alias=getAlias(p.TableName);
if(!c.TryGetValue(p.TableName, out cCurrent))
continue;
temp= "( " + p.PredicateTemplate + " ) AND ";
temp = string.Format(temp, alias + "." + p.FieldName, ":" + getParamName(paramIndex));
cCurrent += temp;
p.FieldName=getParamName(paramIndex);
//if (p is StringSearchPredicate)
//{
// StringSearchPredicate pp = (StringSearchPredicate)p;
// //q.SetString(getParamName(paramIndex), pp.Value);
//}
//else if (p is IntegerSearchPredicate)
//{
// IntegerSearchPredicate pp = (IntegerSearchPredicate)p;
// q.SetInt32(getParamName(paramIndex), pp.Value);
//}
//else if (p is DateSearchPredicate)
//{
// DateSearchPredicate pp = (DateSearchPredicate)p;
// q.SetDateTime(getParamName(paramIndex), pp.Value);
//}
c[p.TableName]=cCurrent;
paramIndex++;
}//foreach p in criteria
foreach (KeyValuePair<string,string> pair in c)
{
string key, value;
key = pair.Key;
value = pair.Value;
if (value.Length > 4)
{
//remove the last AND
value = value.Substring(0, value.Length - 4);
//q.SetInt32(key, 1); // to indicate that respective criteria must be factored in during the search
}
else
{
value = "1=1";
//q.SetInt32(key, 0); // to indicate that respective criteria need not be searched
}
strSQL = strSQL.Replace("<" + key + ">", value);
}
//end process search predicates
//set switches true or false, so that unnecessary queries won't be executed
foreach (KeyValuePair<string, string> pair in c)
{
string key, value;
key = pair.Key;
value = pair.Value;
if (value.Equals(""))
{
strSQL = strSQL.Replace("<" + key + "Switch>", " 0=0 ");//switch off
}
else
{
strSQL = strSQL.Replace("<" + key + "Switch>", " 0=1 ");//switch on
}
}
//querystring and parameters are ready
//create query object
q = ses.CreateSQLQuery(strSQL);
q.SetInt64("idPAList", idPAList);
q.AddEntity("pa", "Apas.Business.PaymentAdvice");
//add parameters
foreach (SearchPredicate p in criteria)
{
if (p is StringSearchPredicate)
{
StringSearchPredicate pp = (StringSearchPredicate)p;
q.SetString(p.FieldName, pp.Value);
}
else if (p is IntegerSearchPredicate)
{
IntegerSearchPredicate pp = (IntegerSearchPredicate)p;
q.SetInt32(p.FieldName, pp.Value);
}
else if (p is DateSearchPredicate)
{
DateSearchPredicate pp = (DateSearchPredicate)p;
q.SetDateTime(p.FieldName, pp.Value);
}
}
//execute
trans = ses.BeginTransaction();
IList<PaymentAdvice> pList = q.List<PaymentAdvice>();
trans.Commit();
return pList;
}
//public ArrayList SearchPaymentAdviceByDay
//(long idPAList, string strDay, int branchId){
// try{
// return SearchPaymentAdviceByDayImpl(idPAList, strDay, branchId);
// }catch(Exception e){
// string source = "APAS";
// string log = "Application";
// string strEvent = "Exception";
// string machine = ".";
// if(!EventLog.SourceExists(source,machine)){
// EventLog.CreateEventSource(source, log, machine);
// }
// EventLog eLog = new EventLog(log, machine, source);
// eLog.WriteEntry(e.StackTrace);
// throw e;
// }
//}
public ArrayList SearchPaymentAdviceByDay(long idPAList, string strDay, int branchId)
{
//1. Create criteria to query PAs in the chosen day
//2. Aggregate PAs under their principal RegularView
//2.1. If principal class is not in the chosen day, don't display
//2.2. If non-principal classes are in the chosen day, add a dummy reference
ISession ses = NHibernateManager.GetCurrentSession();
EntityConverter ecov = new EntityConverter();
Dictionary<int, ApasRegularView> result = new Dictionary<int, ApasRegularView>();
ApasRegularView pReg;
ApasRegular tempReg;
int pRegId;
PALockManager lMgr = new PALockManager(
ApasAccessControlManager.GetCurrentInstance().LogonUser.Id,
PALockKey);
List<SearchPredicate> criteria = new List<SearchPredicate>();
StringSearchPredicate sp = new StringSearchPredicate();
sp.FieldName = "strDayRegular";
sp.TableName = "Regular";
sp.Value = strDay;
sp.PredicateTemplate = " {0} = {1} ";
criteria.Add(sp);
IntegerSearchPredicate ip = new IntegerSearchPredicate();
ip.FieldName = "intIdBranch";
ip.TableName = "Branch";
ip.Value = branchId;
ip.PredicateTemplate = " {0} = {1} ";
criteria.Add(ip);
IList<PaymentAdvice> pList = executeQuery(idPAList, criteria);
//if no result, return
if (pList.Count == 0)
return new ArrayList();
//aggregate PAs under respective ApasRegularViews
foreach (PaymentAdvice objPA in pList)
{
//check if chosen day of week is principal day for the objPA.
//Principal day = leftmost day of week start from monday
int[] aryDow = new int[objPA.ClassFees.Count];
ApasRegular[] aryReg = new ApasRegular[aryDow.Length];
int i = 0;
foreach (ClassFee objCF in objPA.ClassFees.Values)
{
tempReg = objCF.GetAssociatedClass();
aryDow[i] = convertDow(tempReg.Day);
aryReg[i] = tempReg;
i++;
}
Array.Sort(aryDow, aryReg);
pRegId = aryReg[0].Id; //principal class Id
//add all the classes in the chosen day to result dictionary
foreach (ApasRegular objAR in aryReg)
{
if (objAR.Day.ToLower() == strDay.ToLower() &&
!result.ContainsKey(objAR.Id))
result.Add(objAR.Id, ecov.ConvertRegular(objAR));
}
//check if principal class is in chosen day
if (result.TryGetValue(pRegId, out pReg))
{
//lock
lMgr.Hold(objPA.Id);
ses.Evict(objPA);
//requery the objPA
PaymentAdvice principalPA = new PaymentAdvice(objPA.Id, false, PALockKey);
//add to class
pReg.PaymentAdvices.Add(ecov.ConvertPaymentAdvice(principalPA));
}
foreach (ClassFee objCF in objPA.ClassFees.Values)
{
//add dummy references
if (objCF.IdRegular != pRegId &&
result.ContainsKey(objCF.IdRegular))
{
result[objCF.IdRegular].PaymentAdviceReferences.Add
(ecov.ConvertPaymentAdviceReference(objPA, ecov.ConvertRegular(aryReg[0])));
}
}
}
//put the result into an arraylist
ArrayList resultArray = new ArrayList();
foreach (ApasRegularView objARV in result.Values)
{
resultArray.Add(objARV);
}
return resultArray;
}
/* OBSOLETE METHOD, new method below */
/*
public ArrayList SearchPaymentAdviceByDay
(long idPAList, string strDay)
{
ISession ses = NHibernateManager.GetCurrentSession();
ITransaction trans;
EntityConverter ecov=new EntityConverter();
//string idsToLock="";
Dictionary<int, ApasRegularView> result = new Dictionary<int, ApasRegularView>();
ApasRegularView pReg;
ApasRegular tempReg;
int pRegId;
PALockManager lMgr = new PALockManager(
ApasAccessControlManager.GetCurrentInstance().LogonUser.Id,
PALockKey);
//release all previous locks
lMgr.Release("", new List<SqlParameter>());
//query
trans = ses.BeginTransaction();
IList<PaymentAdvice> pList =
ses.GetNamedQuery
("Apas.Business.ApasSearchManager.SearchPaymentAdviceByDay")
.SetAnsiString("day", strDay)
.SetInt64("idPAList", idPAList)
.List<PaymentAdvice>();
trans.Commit();
//if no result, return
if (pList.Count == 0)
return new ArrayList();
//dun lock now because not all the PAs queried are principal records
////Lock
//foreach (PaymentAdvice objPA in pList)
//{
// idsToLock += objPA.Id + ",";
//}
//idsToLock = idsToLock.Substring(0, idsToLock.Length - 1);
//idsToLock = "(" + idsToLock + ")";
//lMgr.Hold(" WHERE intIdPA in " + idsToLock, new List<SqlParameter>());
//trans = ses.BeginTransaction();
////re-query for updated "hold" status
//pList =
// ses.GetNamedQuery
// ("Apas.Business.ApasSearchManager.SearchPaymentAdviceByDay")
// .SetAnsiString("day", strDay)
// .SetInt64("idPAList", idPAList)
// .List<PaymentAdvice>();
//trans.Commit();
//do eager fetching, erm.. not necessary
//foreach (PaymentAdvice objPA in pList)
//{
// //do eager fetching on collections
// NHibernateUtil.Initialize(objPA.ClassFees);
// NHibernateUtil.Initialize(objPA.OtherFees);
//}
//aggregate PAs under respective ApasRegularViews
foreach (PaymentAdvice objPA in pList)
{
//check if chosen day of week is principal day for the objPA.
//Principal day = leftmost day of week start from monday
int dow = convertDow(strDay);
int[] aryDow = new int[objPA.ClassFees.Count];
ApasRegular[] aryReg = new ApasRegular[aryDow.Length];
int i = 0;
foreach (ClassFee objCF in objPA.ClassFees.Values)
{
tempReg = objCF.GetAssociatedClass();
aryDow[i] = convertDow(tempReg.Day);
aryReg[i] = tempReg;
i++;
}
Array.Sort(aryDow, aryReg);
pRegId = aryReg[0].Id ; //principal class Id
//add all the classes in the chosen day to result dictionary
foreach (ApasRegular objAR in aryReg)
{
if (objAR.Day.ToLower() == strDay.ToLower() &&
!result.ContainsKey(objAR.Id))
result.Add(objAR.Id, ecov.ConvertRegular(objAR));
}
//check if this PA is in principal class
if (result.TryGetValue(pRegId, out pReg))
{
//lock
lMgr.Hold(objPA.Id);
ses.Evict(objPA);
//requery the objPA
PaymentAdvice principalPA= new PaymentAdvice(objPA.Id, false, PALockKey);
//add to class
pReg.PaymentAdvices.Add(ecov.ConvertPaymentAdvice(principalPA));
}
foreach (ClassFee objCF in objPA.ClassFees.Values)
{
//add dummy references
if (objCF.IdRegular != pRegId &&
result.ContainsKey(objCF.IdRegular))
{
result[objCF.IdRegular].PaymentAdviceReferences.Add
(ecov.ConvertPaymentAdviceReference(objPA, ecov.ConvertRegular(aryReg[0])));
}
}
}
//put the result into an arraylist
ArrayList resultArray = new ArrayList();
foreach (ApasRegularView objARV in result.Values)
{
resultArray.Add(objARV);
}
return resultArray;
}
*/
public ArrayList SearchPaymentAdviceByCriteria(long idPAList, List<SearchPredicate> criteria)
{
//1. execute the criteria to get PAList
//2. Aggregate PAList under respective principal RegularClassView
ISession ses = NHibernateManager.GetCurrentSession();
EntityConverter ecov = new EntityConverter();
Dictionary<int, ApasRegularView> result = new Dictionary<int, ApasRegularView>();
ApasRegularView pReg;
ApasRegular tempReg;
int pRegId;
PALockManager lMgr = new PALockManager(
ApasAccessControlManager.GetCurrentInstance().LogonUser.Id,
PALockKey);
IList<PaymentAdvice> pList = executeQuery(idPAList, criteria);
//if no result, return
if (pList.Count == 0)
return new ArrayList();
//aggregate PAs under respective ApasRegularViews
foreach (PaymentAdvice objPA in pList)
{
//Principal day = leftmost day of week start from monday
int[] aryDow = new int[objPA.ClassFees.Count];
ApasRegular[] aryReg = new ApasRegular[aryDow.Length];
int i = 0;
foreach (ClassFee objCF in objPA.ClassFees.Values)
{
tempReg = objCF.GetAssociatedClass();
aryDow[i] = convertDow(tempReg.Day);
aryReg[i] = tempReg;
i++;
}
Array.Sort(aryDow, aryReg);
pRegId = aryReg[0].Id; //principal class Id
//add principal class to result
if (!result.ContainsKey(pRegId))
result.Add(pRegId, ecov.ConvertRegular(aryReg[0]));
//add PA under its principal class
if (result.TryGetValue(pRegId, out pReg))
{
//lock
lMgr.Hold(objPA.Id);
ses.Evict(objPA);
//requery the objPA
PaymentAdvice principalPA = new PaymentAdvice(objPA.Id, false, PALockKey);
//add to class
pReg.PaymentAdvices.Add(ecov.ConvertPaymentAdvice(principalPA));
}
}
//put the result into an arraylist
ArrayList resultArray = new ArrayList();
foreach (ApasRegularView objARV in result.Values)
{
resultArray.Add(objARV);
}
return resultArray;
}
