public static DataTable GetAllSelfFundingFederationsByUserRole(int CampYearID, Role UserRole, int FedID, int UserID)
{
SQLDBAccess db = new SQLDBAccess("CIPMS");
string spName = "usprsFederations_Select";
var actionName = "AllSelfFunding";
if (UserRole == Role.MovementAdmin)
{
spName = "usp_Movement_Select";
actionName = "GetMovementFedIDsByUserID";
db.AddParameter("@UserID", UserID);
}
else
{
if (UserRole != Role.FJCAdmin)
{
db.AddParameter("@FedID", FedID);
}
db.AddParameter("@CampYearID", CampYearID);
}
db.AddParameter("@Action", actionName);
return(db.FillDataTable(spName));
}
public static DataTable GetReport(int campYearId, int fedId, IList <int> campIdList, bool isFinal)
{
var db = new SQLDBAccess("CIPMS");
var spName = "usp_PaymentProcessingSelfFuncingPreliminary";
if (isFinal)
{
spName = "usp_PaymentProcessingSelfFuncingFinalMode";
db.AddParameter("@UserID", HttpContext.Current.Session["UserID"]);
}
db.AddParameter("@CampYearID", campYearId);
db.AddParameter("@FedID", fedId);
var dt = new DataTable();
dt.Columns.Add("CampID");
foreach (var id in campIdList)
{
var row = dt.NewRow();
row["CampID"] = id;
dt.Rows.Add(row);
}
db.AddParameterWithValue("@CampIDList", dt);
return(db.FillDataTable(spName));
}
public static DataTable GetMovementFedIDsByUserID(int userId)
{
var db = new SQLDBAccess("CIPConnectionString");
db.AddParameter("@Action", "GetMovementFedIDsByUserID");
db.AddParameter("@UserID", userId);
return(db.FillDataTable("usp_Movement_Select"));
}
public static DataTable GetData(int CampYear)
{
SQLDBAccess db = new SQLDBAccess("CIPMS");
db.AddParameter("@Action", "ByDateRange");
db.AddParameter("@CampYear", CampYear);
return(db.FillDataTable("usp_CamperHolding_Select"));
}
public static DataTable GetAllFederations(int CampYearID)
{
var db = new SQLDBAccess("CIPMS");
db.AddParameter("@Action", "All");
db.AddParameter("@CampYearID", CampYearID);
return(db.FillDataTable("usprsFederations_Select"));
}
public static DataTable GetCountryByCampYearID(int CampYearID)
{
SQLDBAccess db = new SQLDBAccess("CIPMS");
db.AddParameter("@Action", "SelectAvailableCountry");
db.AddParameter("@CampYearID", CampYearID);
return(db.FillDataTable("usprsCamperAnswer_Select"));
}
public static DataSet GetFJCAllocationData(int CampYearID)
{
SQLDBAccess db = new SQLDBAccess("CIPMS");
db.AddParameter("@Action", "GetFJCAllocation");
db.AddParameter("@CampYearID", CampYearID);
return(db.FillDataSet("usprsCamperApplications_Select"));
}
public static DataTable GetSummary(int campYearId, int fedId)
{
var db = new SQLDBAccess("CIPMS");
db.AddParameter("@CampYearID", campYearId);
db.AddParameter("@FedID", fedId);
return(db.FillDataTable("usp_PaymentProcessingSummary"));
}
public static DataTable GetZipCodes(int fedId)
{
var db = new SQLDBAccess("CIPConnectionString");
db.AddParameter("@Action", "AllByFedId");
db.AddParameter("@FedID", fedId);
return(db.FillDataTable("usp_FedZipCodes_Select"));
}
public static DataTable GetZipCodes(string zipCode)
{
var db = new SQLDBAccess("CIPConnectionString");
db.AddParameter("@Action", "ByZipCode");
db.AddParameter("@ZipCode", zipCode);
return(db.FillDataTable("usp_FedZipCodes_Select"));
}
public static DataTable GetAllCampsByYearIDAndFedIDList(int CampYearID, string FedIDList)
{
SQLDBAccess db = new SQLDBAccess("CIPMS");
db.AddParameter("@Action", "ByFedIDAndFedIDList");
db.AddParameter("@CampYearID", CampYearID);
db.AddParameter("@FedIDList", FedIDList);
return(db.FillDataTable("usprsCamps_Select"));
}
public static DataTable GetWhoIsInSynagogue(FederationEnum fed)
{
var db = new SQLDBAccess("CIPConnectionString");
db.AddParameter("@Action", "GetWhoIsInSynagogue");
db.AddParameter("@FedID", (int)fed);
return(db.FillDataTable("uspSynagogueMember_Select"));
}
public static bool AuthenticateByUserID(string UserID, out DataTable UserInfo)
{
SQLDBAccess db = new SQLDBAccess("CIPMS");
db.AddParameter("@Action", "GetUserDetailByUserID");
db.AddParameter("@UserID", UserID);
UserInfo = db.FillDataTable("usprsUser_Select");
return(UserInfo.Rows.Count > 0);
}
public static DataTable GetDuplicateCampers(int CampYearID, string StatusID_List)
{
var db = new SQLDBAccess("CIPMS");
db.AddParameter("@Action", "GetDuplicateCampers");
db.AddParameter("@CampYearID", CampYearID);
db.AddParameter("@StatusID_List", StatusID_List);
return(db.FillDataTable("usprsCamperApplications_Select"));
}
public static DataSet GetAllByFedID(int CampYearID, int FedID)
{
var db = new SQLDBAccess("CIPConnectionString");
db.AddParameter("@Action", "ByFedIdAndYearId");
db.AddParameter("@CampYearID", CampYearID);
db.AddParameter("@FedID", FedID);
return(db.FillDataSet("usp_FedCampGrant"));
}
public static DataTable GetCampByCampID(int CampYearID, int UserID)
{
SQLDBAccess db = new SQLDBAccess("CIPMS");
db.AddParameter("@Action", "ByCampID");
db.AddParameter("@CampYearID", CampYearID);
db.AddParameter("@UserID", UserID);
return(db.FillDataTable("usprsCamps_Select"));
}
public static DataTable GetAllSynagByFedID(int CampYearID, int FedID)
{
SQLDBAccess db = new SQLDBAccess("CIPMS");
db.AddParameter("@Action", "ByFedIDCampYearID");
db.AddParameter("@CampYearID", CampYearID);
db.AddParameter("@FedID", FedID);
return(db.FillDataTable("usprsSynagogues_Select"));
}
public static DataTable GetCampByJWestCampDirector(int CampYearID, int UserID)
{
SQLDBAccess db = new SQLDBAccess("CIPConnectionString");
db.AddParameter("@Action", "ByCampDirector");
db.AddParameter("@CampYearID", CampYearID);
db.AddParameter("@UserID", UserID);
return(db.FillDataTable("usprsCamps_Select"));
}
public static DataSet GetSchoolTypeReport(int CampYearID, int FedID)
{
SQLDBAccess db = new SQLDBAccess("CIPMS");
db.AddParameter("@Action", "SchoolTypeReport");
db.AddParameter("@CampYearID", CampYearID);
db.AddParameter("@FedID", FedID);
return(db.FillDataSet("usprsCamperAnswer_Select"));
}
/// <summary>
/// FJC Admin would use this method to see all the data
/// </summary>
/// <param name="CampYearID"></param>
/// <param name="type"></param>
/// <returns></returns>
public static DataTable GetAllCampsByProgram(int CampYearID, ProgramType type)
{
SQLDBAccess db = new SQLDBAccess("CIPConnectionString");
db.AddParameter("@Action", "ByProgram");
db.AddParameter("@CampYearID", CampYearID);
db.AddParameter("@Program", type);
return(db.FillDataTable("usprsCamps_Select"));
}
public static DataTable GetStatusUsedByFedID(int FedID)
{
SQLDBAccess db = new SQLDBAccess("CIPMS");
db.AddParameter("@Action", "ByFedID");
db.AddParameter("@FederationID", FedID);
return(db.FillDataTable("usprsStatus_Select"));
}
public static DataTable GetAllCampsByFedID(int CampYearID, int FedID)
{
var db = new SQLDBAccess("CIPConnectionString");
db.AddParameter("@Action", "ByFedID");
db.AddParameter("@CampYearID", CampYearID);
db.AddParameter("@FedID", FedID);
return(db.FillDataTable("usprsCamps_Select"));
}
public static DataTable GetAllCampsFilterByStatusMinimumOneCamper(int CampYearID, int FedID, int statusID)
{
var db = new SQLDBAccess("CIPConnectionString");
db.AddParameter("@Action", "FilterByStatusWithMinimumOneCamper");
db.AddParameter("@CampYearID", CampYearID);
db.AddParameter("@FedID", FedID);
db.AddParameter("@StatusID", statusID);
return(db.FillDataTable("usprsCamps_Select"));
}
public static bool Authenticate(string EmailID, string Password)
{
SQLDBAccess db = new SQLDBAccess("CIPMS");
db.AddParameter("@EmailID", EmailID);
db.AddParameter("@Password", Password);
IDataReader dr = db.ExecuteReader("usp_ValidateLogin");
return(dr.Read());
}
public static void DeleteZipCode(int ID)
{
if (ID > 0)
{
var db = new SQLDBAccess("CIPConnectionString");
db.AddParameter("@Action", "ByZipCodeID");
db.AddParameter("@ZipCodeID", ID);
db.ExecuteNonQuery("usp_FedZipCodes_Delete");
}
}
/// <summary>
/// People who are not FJC admin (such as Federation Admin) will need the filter of Fed ID
/// </summary>
/// <param name="CampYearID"></param>
/// <param name="type"></param>
/// <param name="RoleID"></param>
/// <param name="UserID"></param>
/// <returns></returns>
public static DataTable GetAllCampsByRoleID(int CampYearID, ProgramType type, Role RoleID, int UserID)
{
SQLDBAccess db = new SQLDBAccess("CIPConnectionString");
if (RoleID == Role.FederationAdmin)
{
db.AddParameter("@Action", "ByUserID");
db.AddParameter("@CampYearID", CampYearID);
db.AddParameter("@UserID", UserID);
}
else if (RoleID == Role.CampDirector)
{
db.AddParameter("@Action", "ByCampDirector");
db.AddParameter("@UserID", UserID);
db.AddParameter("@CampYearID", CampYearID);
}
else
{
db.AddParameter("@Action", "ByProgram");
db.AddParameter("@Program", type);
db.AddParameter("@CampYearID", CampYearID);
}
return(db.FillDataTable("usprsCamps_Select"));
}
public static DataSet GetCamperTimesInCampCountByState(int CampYearID, string FedID_List, string StatusName_List, int TimesReceivedGrant)
{
SQLDBAccess db = new SQLDBAccess("CIPMS");
db.AddParameter("@Action", "CamperTimesInCampCountByState");
db.AddParameter("@CampYearID", CampYearID);
db.AddParameter("@FedID_List", FedID_List);
db.AddParameter("@StatusName_List", StatusName_List);
db.AddParameter("@TimesReceivedGrant", TimesReceivedGrant);
return(db.FillDataSet("usprsCamperApplications_Select"));
}
public static DataTable GetAllActiveFederations(Role UserRole, int FedID)
{
var db = new SQLDBAccess("CIPConnectionString");
db.AddParameter("@Action", "AllActive");
if (UserRole != Role.FJCAdmin)
{
db.AddParameter("@FedID", FedID);
}
return(db.FillDataTable("usprsFederations_Select"));
}
public static void SaveFederationContact(int fedId, string contactName, string phone, string email)
{
var db = new SQLDBAccess("CIPConnectionString");
db.AddParameter("@Action", "UpdateContactInfo");
db.AddParameter("@FedID", fedId);
db.AddParameter("@Contact", contactName);
db.AddParameter("@Phone", phone);
db.AddParameter("@Email", email);
db.ExecuteNonQuery("usp_Federation_Update");
}
public static DataTable GetAllFederationsByMultipleCampYearsAndUserRole(string CampYearID_String, Role UserRole, int FedID)
{
SQLDBAccess db = new SQLDBAccess("CIPMS");
db.AddParameter("@Action", "ByMultipleYears");
db.AddParameter("@CampYearID_String", CampYearID_String);
if (UserRole != Role.FJCAdmin)
{
db.AddParameter("@FedID", FedID);
}
return(db.FillDataTable("usprsFederations_Select"));
}
