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 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) "
);
}
/// <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.");
}
}
/// <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>
/// </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>
/// 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);
}