public bool checkPreRequisites()
{
AdoNetManager db = new AdoNetManager("DaysInMonth");
int result;
db.objCmd.Parameters.Add(
"@dtmYearMonth", SqlDbType.DateTime).Value = parseDate(lblMonth.Text);
result = Convert.ToInt32(db.executeScalar(
" SELECT count(*) FROM DaysInMonth " +
" WHERE DATEPART(MONTH, YearMonth) = DATEPART(MONTH, @dtmYearMonth) " +
" AND DATEPART(YEAR, YearMonth)= DATEPART(YEAR, @dtmYearMonth) "
));
if (result == 0)
{
Response.Write("<script language='javascript' type='text/javascript'>" +
"window.location.href='EnterNumberOfLessons.aspx?month='+escape('" + lblMonth.Text + "');" +
"</script>");
return false;
}
else
{
return true;
}
}
protected void btnSave_Click(object sender, EventArgs e)
{
AdoNetManager db=new AdoNetManager("DaysInMonth");
int result;
db.objCmd.Parameters.Add(
"@dtmYearMonth",SqlDbType.DateTime).Value=parseDate(lblMonth.Text);
result=Convert.ToInt32(db.executeScalar(
" SELECT count(*) FROM DaysInMonth "+
" WHERE DATEPART(MONTH, YearMonth) = DATEPART(MONTH, @dtmYearMonth) " +
" AND DATEPART(YEAR, YearMonth)= DATEPART(YEAR, @dtmYearMonth) "
));
db.objCmd.Parameters.Clear();
db.objCmd.Parameters.Add(
"@dtmYearMonth", SqlDbType.DateTime).Value = parseDate(lblMonth.Text);
db.objCmd.Parameters.Add(
"@mon", SqlDbType.Int).Value = Convert.ToInt32(txtMonday.Text);
db.objCmd.Parameters.Add(
"@tues",SqlDbType.Int).Value=Convert.ToInt32(txtTuesday.Text);
db.objCmd.Parameters.Add(
"@wed",SqlDbType.Int).Value=Convert.ToInt32(txtWednesday.Text);
db.objCmd.Parameters.Add(
"@thurs",SqlDbType.Int).Value=Convert.ToInt32(txtThursday.Text);
db.objCmd.Parameters.Add(
"@fri",SqlDbType.Int).Value=Convert.ToInt32(txtFriday.Text);
db.objCmd.Parameters.Add(
"@sat",SqlDbType.Int).Value=Convert.ToInt32(txtSaturday.Text);
db.objCmd.Parameters.Add(
"@sun",SqlDbType.Int).Value=Convert.ToInt32(txtSunday.Text);
if(result==0)
{
db.executeCommand(
" INSERT INTO DaysInMonth " +
" (YearMonth, NumOfMonday, NumOfTuesday, NumOfWednesday, "+
" NumOfThursday, NumOfFriday, NumOfSaturday, NumOfSunday) "+
" VALUES (@dtmYearMonth,@mon,@tues,@wed, @thurs, @fri,@sat,@sun) ");
}else
{
db.queryDataSet
("UPDATE DaysInMonth SET " +
" NumOfMonday=@mon, " +
" NumOfTuesday=@tues, " +
" NumOfWednesday=@wed, " +
" NumOfThursday=@thurs, " +
" NumOfFriday=@fri, " +
" NumOfSaturday=@sat, " +
" NumOfSunday=@sun " +
" WHERE DATEPART(MONTH, YearMonth) = DATEPART(MONTH, @dtmYearMonth) " +
" AND DATEPART(YEAR, YearMonth)= DATEPART(YEAR, @dtmYearMonth) "
);
}
Response.Redirect(
"OpenPaymentAdvice.aspx?month=" + Server.UrlEncode(lblMonth.Text)
);
}
public void LoadData()
{
base.LoadData();
ApasConfigurationManager cfg=new ApasConfigurationManager();
AdoNetManager db = new AdoNetManager("APAS_Regular");
db.ClearParameters();
db.objCmd.Parameters.Clear();
db.objCmd.Parameters.Add(
"@id", SqlDbType.Int).Value = Id;
DataTable tbl = db.queryTable(
" SELECT * FROM APAS_Regular " +
" WHERE intIdRegular = @id ");
if (tbl.Rows.Count > 0)
{
ColorCode = (string)tbl.Rows[0]["strColorCodeRegular"];
}
else
{
//if base regular exists and extended APAS_Regular does not
//exist, assume null for ColorCode
ColorCode = cfg.getConfigValue("DefaultClassColor");
//throw new ApasDatabaseException
// ("Requested Regular record is not found.");
}
}
/// <summary>
/// Break PA records (force holds on PA regardless of previous owner)
/// matched either by Id or by a sql where clause
/// </summary>
/// <param name="idPA">Id of the PA to Lock</param>
public void Break(long idPA)
{
AdoNetManager db = new AdoNetManager("table");
db.objCmd.Parameters.Clear();
db.objCmd.Parameters.Add
("@id", SqlDbType.BigInt).Value = idPA;
db.objCmd.Parameters.Add
("@lockKey", SqlDbType.VarChar, 100).Value = LockKey;
db.objCmd.Parameters.Add
("@lockHolder", SqlDbType.Int).Value = IdLockHolder;
db.objCmd.Parameters.Add
("@lockTime", SqlDbType.DateTime).Value = DateTime.Now;
db.executeCommand(
" UPDATE APAS_PaymentAdvice " +
" SET intIdLockHolderPA = @lockHolder , " +
" strLockKeyPA = @lockKey, " +
" LastAction = 'Break Lock', " +
" LastModifiedBy = @lockHolder, " +
" LastModifiedTime = @lockTime " +
" WHERE intIdPA = @id "
);
}
/// <summary>
/// Will only save the ApasRegular part
/// </summary>
public void Save()
{
//will use AdoNetManager to directly Access the Database
AdoNetManager db = new AdoNetManager("APAS_Regular");
//check if the extended ApasRegular record already exists
db.ClearParameters();
db.objCmd.Parameters.Clear();
db.objCmd.Parameters.Add(
"@id", SqlDbType.Int).Value = Id;
int result = Convert.ToInt32(db.executeScalar(
" SELECT COUNT(*) FROM APAS_Regular " +
" WHERE intIdRegular = @id "));
if (result > 0)
{
//exists
db.objCmd.Parameters.Clear();
db.objCmd.Parameters.Add(
"@id", SqlDbType.Int).Value = Id;
db.objCmd.Parameters.Add(
"@cc", SqlDbType.VarChar, 50).Value = ColorCode;
result = db.executeCommand(
" UPDATE APAS_Regular " +
" SET strColorCodeRegular= @cc " +
" WHERE intIdRegular = @id ");
if (result == 0)
throw new ApasDatabaseException(
"A database update failed for unknown reason.");
}
else
{
//not exists
db.objCmd.Parameters.Clear();
db.objCmd.Parameters.Add(
"@id", SqlDbType.Int).Value = Id;
db.objCmd.Parameters.Add(
"@cc", SqlDbType.VarChar, 50).Value = ColorCode;
result = db.executeCommand(
" INSERT INTO APAS_Regular " +
" (intIdRegular, strColorCodeRegular) " +
" VALUES (@id, @cc) ");
if (result == 0)
throw new ApasDatabaseException(
"A database update failed for unknown reason.");
}
}
public void initConfigCollection()
{
AdoNetManager db = new AdoNetManager("APAS_Configuration");
string strSQL =
" SELECT * " +
" FROM APAS_Configuration ";
DataTable result = db.queryTable(strSQL);
configCollection = new Dictionary<string, string>();
if (!(result == null) && result.Rows.Count > 0)
{
foreach (DataRow row in result.Rows)
{
configCollection.Add(
Convert.ToString(row["strConfigKey"]),
Convert.ToString(row["strConfigValue"])
);
}
}
}
public bool changeConfigValue(string configKey, string configValue)
{
AdoNetManager db = new AdoNetManager("APAS_Configuration");
string strSQL =
" UPDATE APAS_Configuration " +
" SET strConfigValue=@configValue " +
" WHERE strConfigKey=@configKey ";
db.Parameters.Add("@configKey", SqlDbType.VarChar, 100).Value = configKey;
db.Parameters.Add("@configValue", SqlDbType.VarChar, 500).Value = configValue;
int rowsAffected = db.executeCommand(strSQL);
if (rowsAffected>0)
{
return true;
}
else
{
return false;
}
}
/// <summary>
/// soft delete. set IsDeleted to zero and delete corresponding DaysInMonth record
/// </summary>
public void DeletePAList()
{
if (CurrentPAList == null)
{
throw new ApasInvaidOperationException(
"Invalid Operation: Please select a payment advice list before " +
"performing delete function.");
}
CurrentPAList.IsDeleted = 1;
CurrentPAList.LastAction = "Delete";
CurrentPAList.LastModifiedBy =
ApasAccessControlManager.GetCurrentInstance().LogonUser.Id;
CurrentPAList.LastModifiedTime = DateTime.Now;
CurrentPAList.Save();
//DaysinMonth is not used anywhere else as of july 2008
//so, will delete the DaysInMonth record as well
AdoNetManager db = new AdoNetManager("DaysInMonth");
db.objCmd.Parameters.Add(
"@dtmYearMonth", SqlDbType.DateTime).Value = CurrentPAList.Month;
db.executeCommand(
" DELETE FROM DaysInMonth " +
" WHERE DATEPART(MONTH, YearMonth) = DATEPART(MONTH, @dtmYearMonth) " +
" AND DATEPART(YEAR, YearMonth)= DATEPART(YEAR, @dtmYearMonth) "
);
}
//no more time to consider design problems
//directly query from database using AdoNetManager
//reconsider later
public virtual void SyncNextMonthCredits(DateTime curMonth)
{
AdoNetManager db = new AdoNetManager("DaysInMonth");
db.objCmd.Parameters.Clear();
db.objCmd.Parameters.Add(
"@inputDate", SqlDbType.DateTime
).Value = curMonth;
string strSQL = " SELECT * FROM DaysInMonth " +
" WHERE DATEPART(MONTH, YearMonth) = DATEPART(MONTH, @inputDate) " +
" AND DATEPART(YEAR, YearMonth)= DATEPART(YEAR, @inputDate) ";
DataTable tbl = db.queryTable(strSQL);
if (tbl.Rows.Count == 0)
throw new Apas.ApasException("Cannot find number of lessons for " +
curMonth.ToString("MMMM yyyy"));
BCRegular regular = GetAssociatedClass();
DataRow row = tbl.Rows[0];
switch (regular.Day.ToLower())
{
case "monday":
NextMonthCredit = (int)row["NumOfMonday"];
break;
case "tuesday":
NextMonthCredit = (int)row["NumOfTuesday"];
break;
case "wednesday":
NextMonthCredit = (int)row["NumOfWednesday"];
break;
case "thursday":
NextMonthCredit = (int)row["NumOfThursday"];
break;
case "friday":
NextMonthCredit = (int)row["NumOfFriday"];
break;
case "saturday":
NextMonthCredit = (int)row["NumOfSaturday"];
break;
case "sunday":
NextMonthCredit = (int)row["NumOfSunday"];
break;
default:
NextMonthCredit = 0;
throw new ApasException("Invalid Day Name");
break;
}
}
//This method tightly couples with CORE classes. maybe, reconsider later
//Nhibernate does not suppport bidirectional association with
//list collections. No choice but have to ask the parent for studId
public virtual void SyncDerivedFields(PaymentAdvice parent)
{
wsvAttendanceLn.AttendanceLn DBAttln =
new wsvAttendanceLn.AttendanceLn();
wsvAttendanceLn.CAttendanceLn[] aryAttln =
DBAttln.GetAttendanceByStudentClass(parent.IdStudent, IdRegular);
Absence = 0;
Prorate = 0;
if (aryAttln != null)
{
foreach (wsvAttendanceLn.CAttendanceLn attln in aryAttln)
{
if (attln.isPresent == 0)
Absence++;
else if (attln.isPresent == 2)
Prorate++;
}
}
//check log for number of forfeits. Very unnatural....
AdoNetManager db = new AdoNetManager("ForfeitLog");
String strSQL =
" SELECT ISNULL(SUM(intCreditForfeitLog),0) FROM ForfeitLog " +
" WHERE intIdUserForfeitLog=@idStud " +
" AND intIdClassScheduleForfeitLog=@idClass ";
db.ClearParameters();
db.Parameters.Add("@idClass", SqlDbType.Int).Value = IdRegular;
db.Parameters.Add("@idStud", SqlDbType.Int).Value = parent.IdStudent;
Forfeit =Convert.ToInt32(db.executeScalar(strSQL));
wsvMakeupAttendance.MakeupAttendance dbMakeup
= new wsvMakeupAttendance.MakeupAttendance();
wsvMakeupAttendance.CMakeupAttendance[] aryMakeup
= dbMakeup.getMakeupAttendanceByStudent(parent.IdStudent);
if (aryMakeup == null)
Makeup = 0;
else
{
foreach (wsvMakeupAttendance.CMakeupAttendance objMakeup in aryMakeup)
{
if (objMakeup.IdAttendanceRegular == IdRegular)
Makeup++;
}
}
ApasRegular regClass = GetAssociatedClass();
PerLessonFee = Convert.ToDecimal(regClass.Fee);
//get credit from student classshedule
wsvStudentClassSchedule.StudentClassSchedule dbSC =
new wsvStudentClassSchedule.StudentClassSchedule();
wsvStudentClassSchedule.CStudentClassSchedule objSC =
dbSC.getStudentClassSchedule(parent.IdStudent, IdRegular);
//offset credit with number of attendances unmarked in this month
DateTime month = parent.GetParentPAList().Month.AddMonths(-1);
wsvAttendance.Attendance dbAtt = new wsvAttendance.Attendance();
wsvAttendance.CAttendance[] aryAtt =
dbAtt.getAttendancesBetween(regClass.Id,
new DateTime(month.Year, month.Month, 1),
new DateTime(month.Year, month.Month, DateTime.DaysInMonth(month.Year, month.Month))
);
int dayCount = GetDayCountOf(regClass.Day, regClass.ClassStartDate, month);
int attCount = (aryAtt == null) ? 0 : aryAtt.Length;
int unmarkedCount = (dayCount-attCount) < 0 ? 0 : dayCount-attCount;
Credit = objSC.Credit-unmarkedCount;
}
/// <summary>
/// </summary>
/// <param name="whereClause">A complete, executable where clause
/// to match PaymentAdvices. An empty string means match all records.</param>
/// <param name="sqlParams">An Ilist collection populated with
/// sqlparameters used in the where clause. @lockKey_LM, @lockHolder_LM and @lockTime_LM
/// are reserved by PALockManager and must not be used in whereClause.</param>
public void Release(string whereClause, IList<SqlParameter> sqlParams)
{
AdoNetManager db = new AdoNetManager("table");
db.objCmd.Parameters.Clear();
foreach (SqlParameter sqlParam in sqlParams)
db.objCmd.Parameters.Add(sqlParam);
whereClause = whereClause.ToLower();
//just a simple code to enclose all predicates of given where clause with parenthesis ()
//caller must be careful not to have the word "where" in unexpected places like inside a string literal
if (whereClause.Contains("where"))
{
whereClause = whereClause.Replace("where", "where ( ");
whereClause = whereClause + " ) and ";
}
else
{
whereClause = " where ";
}
whereClause +=
" ( strLockKeyPA is null OR " +
" CAST(strLockKeyPA as VARBINARY(100)) = " +
" CAST(@lockKey_LM as VARBINARY(100)) ) ";
db.objCmd.Parameters.Add
("@lockKey_LM", SqlDbType.VarChar, 100).Value = LockKey;
db.objCmd.Parameters.Add
("@lockHolder_LM", SqlDbType.Int).Value = IdLockHolder;
db.objCmd.Parameters.Add
("@lockTime_LM", SqlDbType.DateTime).Value = DateTime.Now;
db.executeCommand(
" UPDATE APAS_PaymentAdvice " +
" SET intIdLockHolderPA = null, " +
" strLockKeyPA = null, " +
" LastAction = 'Release Lock', " +
" LastModifiedBy = @lockHolder_LM, " +
" LastModifiedTime = @lockTime_LM " +
whereClause
);
}
/// <summary>
/// Release PA records matched either by Id or by a sql where clause
/// </summary>
/// <param name="idPA">Id of the PA to Lock</param>
public void Release(long idPA)
{
AdoNetManager db = new AdoNetManager("table");
db.objCmd.Parameters.Clear();
db.objCmd.Parameters.Add
("@id", SqlDbType.BigInt).Value = idPA;
db.objCmd.Parameters.Add
("@lockKey", SqlDbType.VarChar, 100).Value = LockKey;
db.objCmd.Parameters.Add
("@lockHolder", SqlDbType.Int).Value = IdLockHolder;
db.objCmd.Parameters.Add
("@lockTime", SqlDbType.DateTime).Value = DateTime.Now;
db.executeCommand(
" UPDATE APAS_PaymentAdvice " +
" SET intIdLockHolderPA = NULL , " +
" strLockKeyPA = NULL, " +
" LastAction = 'Release Lock', " +
" LastModifiedBy = @lockHolder, " +
" LastModifiedTime = @lockTime " +
" WHERE intIdPA = @id " +
" AND (strLockKeyPA is null OR " +
" CAST(strLockKeyPA as VARBINARY(100)) = " +
" CAST(@lockKey as VARBINARY(100)) ) "
);
}
/// <summary>
/// Obtain exclusive hold of all PAs within the given PAList.
/// If no PA is currently held by any other user, operation is successful and
/// an empty list is returned.
/// If there are users currently holding some PAs, operation is not successful and
/// the list of users who are holding PAs is returned.
/// </summary>
/// <returns></returns>
public List<BCAspenUser> HoldPAListExclusive(long idPAList)
{
//Hold all PAs within this PAlist
string whereClause =
" WHERE intIdPAList = @idPAList ";
List<SqlParameter> pCol = new List<SqlParameter>();
SqlParameter param = new SqlParameter("@idPAList", SqlDbType.BigInt);
param.Value = idPAList;
pCol.Add(param);
Hold(whereClause, pCol);
//check if any PA remains held by others
AdoNetManager db = new AdoNetManager("table");
db.objCmd.Parameters.Clear();
db.objCmd.Parameters.Add
("@idPAList", SqlDbType.BigInt).Value = idPAList;
db.objCmd.Parameters.Add
("@paLockKey", SqlDbType.VarChar).Value = LockKey;
DataTable tbl = db.queryTable(
" SELECT DISTINCT intIdLockHolderPA FROM APAS_PaymentAdvice " +
" WHERE strLockKeyPA IS NOT NULL " +
" AND strLockKeyPA != '' " +
" AND strLockKeyPA != @paLockKey " +
" AND intIdPAList = @idPAList "
);
List<BCAspenUser> result = new List<BCAspenUser>();
if (tbl != null && tbl.Rows.Count > 0)
{
//has PAs held by others
//hold unsuccessful
//Release PAs
pCol = new List<SqlParameter>();
whereClause =
" WHERE intIdPAList = @idPAList ";
param = new SqlParameter("@idPAList", SqlDbType.BigInt);
param.Value = idPAList;
pCol.Add(param);
Release(whereClause, pCol);
foreach (DataRow row in tbl.Rows)
{
BCAspenUser objStaff = new BCAspenUser();
objStaff.Id = Convert.ToInt32(row["intIdLockHolderPA"]);
objStaff.loadData();
result.Add(objStaff);
}
return result;
}
else
{
//has no PA held by others
//hold successful
return result;
}
}
/// <summary>
/// Clear locks (but don't hold) PAs matched by given where clause
/// </summary>
/// <param name="whereClause">A complete, executable where clause
/// to match PaymentAdvices. An empty string means match all records.</param>
/// <param name="sqlParams">An Ilist collection populated with
/// sqlparameters used in the where clause. </param>
public void Clear(string whereClause, IList<SqlParameter> sqlParams)
{
AdoNetManager db = new AdoNetManager("table");
db.objCmd.Parameters.Clear();
foreach (SqlParameter sqlParam in sqlParams)
db.objCmd.Parameters.Add(sqlParam);
db.executeCommand(
" UPDATE APAS_PaymentAdvice " +
" SET intIdLockHolderPA = NULL , " +
" strLockKeyPA = NULL " +
whereClause);
}
