public static DataTable GetFJCCamperReport(CamperOrgType CamperOrg, ProgramType Program, int FedID, int CampYearID, int CampID, string StatusID_List)
{
var db = new SQLDBAccess("CIPMS");
// Camp and Syang are different
if (CamperOrg == CamperOrgType.EnrollmentConfirmationFJC || CamperOrg == CamperOrgType.EnrollmentConfirmationPartner)
{
db.AddParameter("@Action", "GetFJCCamperReportPerCamp");
}
else if (CamperOrg == CamperOrgType.Synagogue)
{
db.AddParameter("@Action", "GetFJCCamperReportPerSynag");
}
if (Program != ProgramType.NoUse)
{
db.AddParameter("@Program", Program);
}
if (FedID >= 0)
{
db.AddParameter("@FedID", FedID);
}
db.AddParameter("@CampYearID", CampYearID);
db.AddParameter("@CampID", CampID);
db.AddParameter("@StatusID_List", StatusID_List);
return(db.FillDataTable("usprsCamperApplications_Select"));
}
/// <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"));
}
/// <summary>
/// Get Camps that have data using the input parameters as filters
/// There are two versions of this method
/// </summary>
/// <param name="CampYearID"></param>
/// <param name="CampID_List"></param>
/// <param name="StatusID_List"></param>
/// <param name="FedID"></param>
/// <returns></returns>
private static DataTable GetCampsListThatHaveDataDA(int CampYearID, ProgramType Program, string CampID_List, string StatusID_List, int FedID, int TimesReceivedGrant)
{
SQLDBAccess db = new SQLDBAccess("CIPMS");
db.AddParameter("@Action", "GetCampsThatHaveData");
db.AddParameter("@CampYearID", CampYearID);
db.AddParameter("@CampID_List", CampID_List);
db.AddParameter("@StatusID_List", StatusID_List);
if (Program != ProgramType.NoUse)
{
db.AddParameter("@Program", Program);
}
if (FedID > 0)
{
db.AddParameter("@FedID", FedID);
}
if (TimesReceivedGrant > 0)
{
db.AddParameter("@TimesReceivedGrant", TimesReceivedGrant);
}
return(db.FillDataTable("usprsCamperApplications_Select"));
}
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 GetAllMovement()
{
var db = new SQLDBAccess("CIPConnectionString");
db.AddParameter("@Action", "All");
return(db.FillDataTable("usp_Movement_Select"));
}
public static DataTable GetMostUsedStatus()
{
SQLDBAccess db = new SQLDBAccess("CIPMS");
db.AddParameter("@Action", "GetMostUsedStatus");
return(db.FillDataTable("usprsStatus_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 DataTable GetAllYearsWithoutCurrentYear()
{
SQLDBAccess db = new SQLDBAccess("CIPMS");
db.AddParameter("@Action", "GetAllYearsWithoutCurrentYear");
return(db.FillDataTable("usprsCampYear_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(string zipCode)
{
var db = new SQLDBAccess("CIPConnectionString");
db.AddParameter("@Action", "ByZipCode");
db.AddParameter("@ZipCode", zipCode);
return(db.FillDataTable("usp_FedZipCodes_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 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 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 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 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 GetStatusUsedByFedID(int FedID)
{
SQLDBAccess db = new SQLDBAccess("CIPMS");
db.AddParameter("@Action", "ByFedID");
db.AddParameter("@FederationID", FedID);
return(db.FillDataTable("usprsStatus_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 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 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"));
}
/// <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 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 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 DataTable GetCamperSummaryReport(int CampYearID, int FedID, string CampID_List, string StatusID_List)
{
SQLDBAccess db = new SQLDBAccess("CIPMS");
db.AddParameter("@Action", "GetCamperSummaryReport");
db.AddParameter("@CampYearID", CampYearID);
db.AddParameter("@CampID_List", CampID_List);
db.AddParameter("@StatusID_List", StatusID_List);
db.AddParameter("@FedID", FedID);
return(db.FillDataTable("usprsCamperApplications_Select"));
}
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"));
}
public static DataTable GetDataExtract(int CampYearID, string FedID_List, string CampID_List, string StatusID_List, int TimesReceivedGrant)
{
SQLDBAccess db = new SQLDBAccess("CIPMS");
db.AddParameter("@Action", "DataExtract");
db.AddParameter("@CampYearID", CampYearID);
db.AddParameter("@FedID_List", FedID_List);
db.AddParameter("@CampID_List", CampID_List);
db.AddParameter("@StatusID_List", StatusID_List);
db.AddParameter("@TimesReceivedGrant", TimesReceivedGrant);
return(db.FillDataTable("ViewDumpForAllYears_Select"));
}
