public IEnumerable <StaffingSummaryForAllCustomers> GetStaffingSummaryDetailsForAllCustomers()
{
var context = new CloudDbContext();
var staffSummaryDetails = context.Database.SqlQuery <StaffingSummaryForAllCustomers>("[dbo].[usp_get_BAS_Staff_Summary_For_AllCustomers]").ToList();
return(staffSummaryDetails);
}
public IEnumerable <CSM_LIST> GetCSMList()
{
var dbContext = new CloudDbContext();
var TemplateDetails = dbContext.Database.SqlQuery <CSM_LIST>("[dbo].[get_CSMList]").ToList();
return(TemplateDetails);
}
public IEnumerable <ProjectResourceByEmpId> ProjectResourceByProjId(string ProjId)
{
var context = new CloudDbContext();
SqlParameter param1 = new SqlParameter("@ProjId", ProjId);
var TemplateDetails = context.Database.SqlQuery <ProjectResourceByEmpId>("[dbo].[csp_get_ProjectResourceByProjId] @ProjId ", param1).ToList();
return(TemplateDetails);
}
public IEnumerable <Projects> CustomerProjects(string EmailId)
{
var context = new CloudDbContext();
SqlParameter param1 = new SqlParameter("@EMAILID", EmailId);
var TemplateDetails = context.Database.SqlQuery <Projects>("[dbo].[usp_get_project_cspcustomer] @EMAILID ", param1).ToList();
return(TemplateDetails);
}
public IEnumerable <CustomerProjectIds> CustomerProjectIds(int EmpId)
{
var context = new CloudDbContext();
SqlParameter param1 = new SqlParameter("@EmpId", EmpId);
var TemplateDetails = context.Database.SqlQuery <CustomerProjectIds>("[dbo].[usp_get_customerprojectids] @EmpId ", param1).ToList();
return(TemplateDetails);
}
public IEnumerable <ProjectTask> GetProjectTasks(string taskId)
{
var context = new CloudDbContext();
SqlParameter param1 = new SqlParameter("@taskId", taskId);
var projTaks = context.Database.SqlQuery <ProjectTask>("[dbo].[usp_get_Proj_Tasks] @taskId ", param1).ToList();
return(projTaks);
}
public IEnumerable <EMP_INFO_FOR_CUSTOMER> RemainderEmail(DateTime Weekenddate)
{
SqlParameter param1 = new SqlParameter("@EntryDate", Weekenddate);
var context = new CloudDbContext();
var TemplateDetails = context.Database.SqlQuery <EMP_INFO_FOR_CUSTOMER>("[dbo].[TimesheetReminderBulkEmail] @EntryDate", param1).ToList();
return(TemplateDetails);
}
public IEnumerable <Token> Token(string EmailId)
{
var context = new CloudDbContext();
SqlParameter param1 = new SqlParameter("@EmailId", EmailId);
var TemplateDetails = context.Database.SqlQuery <Token>("[dbo].[usp_get_token] @EmailId ", param1).ToList();
return(TemplateDetails);
}
public void InsertUserstory(PROJMGT_USERSTORY userstory)
{
var context = new CloudDbContext();
if (userstory.RANK == null)
{
userstory.RANK = 0;
}
if (userstory.RELEASE_ID == null)
{
userstory.RELEASE_ID = 0;
}
if (userstory.ITERATION_ID == null)
{
userstory.ITERATION_ID = 0;
}
if (userstory.PLAN_ESTIMATION == null)
{
userstory.PLAN_ESTIMATION = 0;
}
if (userstory.TASK_ESTIMATION == null)
{
userstory.TASK_ESTIMATION = 0;
}
if (userstory.SUB_PROJ_ID == null)
{
userstory.SUB_PROJ_ID = 0;
}
if (userstory.TODO == null)
{
userstory.TODO = 0;
}
if (userstory.NOTES == null)
{
userstory.NOTES = "";
}
SqlParameter param1 = new SqlParameter("@ID", userstory.ID);
SqlParameter param2 = new SqlParameter("@RANK", userstory.RANK);
SqlParameter param3 = new SqlParameter("@NAME", userstory.NAME);
SqlParameter param4 = new SqlParameter("@RELEASE_ID", userstory.RELEASE_ID);
SqlParameter param5 = new SqlParameter("@ITERATION_ID", userstory.ITERATION_ID);
SqlParameter param6 = new SqlParameter("@STATE", userstory.STATE);
SqlParameter param7 = new SqlParameter("@PLAN_ESTIMATION", userstory.PLAN_ESTIMATION);
SqlParameter param8 = new SqlParameter("@TASK_ESTIMATION", userstory.TASK_ESTIMATION);
SqlParameter param9 = new SqlParameter("@TODO", userstory.TODO);
SqlParameter param10 = new SqlParameter("@OWNER", userstory.OWNER);
SqlParameter param11 = new SqlParameter("@DESCRIPTION", userstory.DESCRIPTION);
SqlParameter param12 = new SqlParameter("@CUST_ID", userstory.CUST_ID);
SqlParameter param13 = new SqlParameter("@PROJ_ID", userstory.PROJ_ID);
SqlParameter param14 = new SqlParameter("@SUB_PROJ_ID", userstory.SUB_PROJ_ID);
SqlParameter param15 = new SqlParameter("@CREATED_BY", userstory.CREATED_BY);
SqlParameter param16 = new SqlParameter("@CREATED_DATE", userstory.CREATED_DATE);
SqlParameter param17 = new SqlParameter("@UPDATED_BY", userstory.UPDATED_BY);
SqlParameter param18 = new SqlParameter("@UPDATED_DATE", userstory.UPDATED_DATE);
SqlParameter param19 = new SqlParameter("@ISACTIVE", userstory.ISACTIVE);
var TemplateDetails = context.Database.SqlQuery <UserInfo>("[dbo].[usp_insert_Userstory] @ID, @RANK, @NAME, @RELEASE_ID, @ITERATION_ID, @STATE, @PLAN_ESTIMATION, @TASK_ESTIMATION, @TODO, @OWNER, @DESCRIPTION, @CUST_ID, @PROJ_ID, @SUB_PROJ_ID, @CREATED_BY, @CREATED_DATE, @UPDATED_BY, @UPDATED_DATE, @ISACTIVE ", param1, param2, param3, param4, param5, param6, param7, param8, param9, param10, param11, param12, param13, param14, param15, param16, param17, param18, param19).ToList();
}
public IEnumerable <EMP_INFO> ResourceDetailsByManager(int EmpId)
{
var context = new CloudDbContext();
SqlParameter param1 = new SqlParameter("@EmpId", EmpId);
var TemplateDetails = context.Database.SqlQuery <EMP_INFO>("[dbo].[csp_get_ResourceDetailsByManager] @EmpId ", param1).ToList();
return(TemplateDetails);
}
public IEnumerable <UserInfo> UserInfo(string EmailId)
{
var context = new CloudDbContext();
SqlParameter param1 = new SqlParameter("@Email", EmailId);
var TemplateDetails = context.Database.SqlQuery <UserInfo>("[dbo].[usp_get_UserInfo] @email ", param1).ToList();
return(TemplateDetails);
}
public IEnumerable <EMP_INFO> GetAuditeeDetails(string ProjectId, int CustomerId)
{
var context = new CloudDbContext();
SqlParameter param1 = new SqlParameter("@PROJECT_ID", ProjectId);
SqlParameter param2 = new SqlParameter("@CUSTOMER_ID", CustomerId);
var TemplateDetails = context.Database.SqlQuery <EMP_INFO>("[dbo].[getauditeesdetails] @PROJECT_ID,@CUSTOMER_ID", param1, param2).ToList <EMP_INFO>();
return(TemplateDetails);
}
public IEnumerable <StaffingProject> GetStaffingAssignedProjectDetails(string projectId)
{
var context = new CloudDbContext();
SqlParameter param1 = new SqlParameter("@ProjId", projectId);
// SqlParameter param2 = new SqlParameter("@ProjectId", ProjectId?? string.Empty);
var staffProjectDetail = context.Database.SqlQuery <StaffingProject>("[dbo].[usp_get_BAS_Staff_Assigned_ProjectDetails] @ProjId ", param1).ToList();
return(staffProjectDetail);
}
public IEnumerable <StaffingProject> GetStaffingProjectSummary(int custId, string ProjectId = null)
{
var context = new CloudDbContext();
SqlParameter param1 = new SqlParameter("@CustId", custId);
// SqlParameter param2 = new SqlParameter("@ProjectId", ProjectId?? string.Empty);
var staffProjectSummary = context.Database.SqlQuery <StaffingProject>("[dbo].[usp_get_BAS_Staff_Project_Summary] @CustId ", param1).ToList();
return(staffProjectSummary);
}
public IEnumerable <PROJECT_RESOURCE> CheckIfResourceAlreadyAllocatedWithinSameTimeRange(DateTime StartDate, DateTime EndDate, string projectid, int empid)
{
var context = new CloudDbContext();
SqlParameter param1 = new SqlParameter("@startdate", StartDate);
SqlParameter param2 = new SqlParameter("@enddate", EndDate);
SqlParameter param3 = new SqlParameter("@projectid", projectid);
SqlParameter param4 = new SqlParameter("@empid", empid);
var TemplateDetails = context.Database.SqlQuery <PROJECT_RESOURCE>("[dbo].[spCheckIfResourceAlreadyInWithinSameTimeRange] @startdate, @enddate,@projectid,@empid", param1, param2, param3, param4).ToList();
return(TemplateDetails);
}
protected void CreateDbContext()
{
// DbContextFactory = new CloudDbContextFactory();
DbContext = new CloudDbContext();
DbContext.Configuration.ProxyCreationEnabled = false;
DbContext.Configuration.LazyLoadingEnabled = true;
DbContext.Configuration.ValidateOnSaveEnabled = false;
}
public IEnumerable <PROJECT_RESOURCE> TimeSheetTaggedProject(DateTime StartDate, DateTime EndDate, int empid)
{
var context = new CloudDbContext();
SqlParameter param1 = new SqlParameter("@startdatefromdd", StartDate);
SqlParameter param2 = new SqlParameter("@enddatefromdd", EndDate);
SqlParameter param3 = new SqlParameter("@empid", empid);
var TemplateDetails = context.Database.SqlQuery <PROJECT_RESOURCE>("[dbo].[spGetTimeSheetTaggedProject] @startdatefromdd, @enddatefromdd,@empid", param1, param2, param3).ToList();
return(TemplateDetails);
}
public void Insert_PROJ_RESRC_TIME_ENTRY_PSA(DataTable records)
{
var dbContext = new CloudDbContext();
SqlParameter param1 = new SqlParameter();
param1.ParameterName = "@Table";
param1.Value = records;
param1.SqlDbType = SqlDbType.Structured;
param1.TypeName = "dbo.PROJ_RESRC_TIME_ENTRY_TYPE_PSA";
param1.Direction = ParameterDirection.Input;
var QueryResult = dbContext.Database.SqlQuery <List <string> >("[dbo].[usp_insert_tbl_PROJ_RESRC_TIME_ENTRY_PSA] @Table", param1).ToList();
}
public IEnumerable <TimesheetWeekly> TimesheetWeekly(int EmpId, int DateId, int TaskYear)
{
var context = new CloudDbContext();
SqlParameter param1 = new SqlParameter("@DateId", DateId);
SqlParameter param2 = new SqlParameter("@TaskYear", TaskYear);
SqlParameter param3 = new SqlParameter("@EmpId", EmpId);
var TemplateDetails = context.Database.SqlQuery <TimesheetWeekly>("[dbo].[usp_get_Mytimesheet] @DateId, @TaskYear, @EmpId ", param1, param2, param3).ToList();
return(TemplateDetails);
}
public void InsertProjectResource(int EmpId, string ProjectId, bool IsBillable, Decimal AllctPct, string CurrIndc, string CreatedBy, DateTime StartDate, DateTime EndDate)
{
var context = new CloudDbContext();
SqlParameter param1 = new SqlParameter("@EmpId", EmpId);
SqlParameter param2 = new SqlParameter("@ProjectId", ProjectId);
SqlParameter param3 = new SqlParameter("@IsBillable", IsBillable);
SqlParameter param4 = new SqlParameter("@AllctPct", AllctPct);
SqlParameter param5 = new SqlParameter("@CurrIndc", CurrIndc);
SqlParameter param6 = new SqlParameter("@CreatedBy", CreatedBy);
SqlParameter param7 = new SqlParameter("@StartDate", StartDate);
SqlParameter param8 = new SqlParameter("@EndDate", EndDate);
var QueryResult = context.Database.SqlQuery <string>("[dbo].[usp_insert_project_resource] @EmpId, @ProjectId, @IsBillable, @AllctPct, @CurrIndc, @CreatedBy,@StartDate,@EndDate ", param1, param2, param3, param4, param5, param6, param7, param8).ToList();
}
public IEnumerable <Projects> ProjectsWithBillingProj(int EmpId, string ProjectId)
{
var context = new CloudDbContext();
if (ProjectId == null)
{
ProjectId = "";
}
SqlParameter param1 = new SqlParameter("@EmpId", EmpId);
SqlParameter param2 = new SqlParameter("@ProjectId", ProjectId);
var TemplateDetails = context.Database.SqlQuery <Projects>("[dbo].[usp_get_project_withBillingProj] @EmpId, @ProjectId ", param1, param2).ToList();
return(TemplateDetails);
}
public IEnumerable <Projects> ProjectList(int CustomerId, string ProjectId)
{
var context = new CloudDbContext();
if (ProjectId == null)
{
ProjectId = "";
}
SqlParameter param1 = new SqlParameter("@ClientID", CustomerId);
SqlParameter param2 = new SqlParameter("@ProjectID", ProjectId);
var TemplateDetails = context.Database.SqlQuery <Projects>("[dbo].[usp_get_project_List] @ClientID, @ProjectId ", param1, param2).ToList();
return(TemplateDetails);
}
public void AddCustomer(CUSTOMER customer)
{
var context = new CloudDbContext();
SqlParameter param1 = new SqlParameter("@CUST_ID", customer.CUST_ID);
SqlParameter param2 = new SqlParameter("@CUST_NM", customer.CUST_NM);
SqlParameter param3 = new SqlParameter("@INDUSTRY_TYPE", customer.INDUSTRY_TYPE);
SqlParameter param4 = new SqlParameter("@URL", customer.URL);
SqlParameter param5 = new SqlParameter("@CREATED_BY", customer.CREATED_BY);
SqlParameter param6 = new SqlParameter("@CREATED_DATE", customer.CREATED_DATE);
SqlParameter param7 = new SqlParameter("@UPDATED_BY", customer.UPDATED_BY);
SqlParameter param8 = new SqlParameter("@UPDATED_DATE", customer.UPDATED_DATE);
var TemplateDetails = context.Database.SqlQuery <EMP_INFO_DETAILED>("[dbo].[usp_insert_customer] @CUST_ID, @CUST_NM, @INDUSTRY_TYPE, @URL, @CREATED_BY, @CREATED_DATE, @UPDATED_BY, @UPDATED_DATE ", param1, param2, param3, param4, param5, param6, param7, param8).ToList();
}
public IEnumerable <Resource> ResourceCount(string ProjectId)
{
var context = new CloudDbContext();
if (ProjectId == null)
{
ProjectId = "";
}
SqlParameter param1 = new SqlParameter("@ProjectId", ProjectId);
var TemplateDetails = context.Database.SqlQuery <Resource>("[dbo].[csp_get_ProjectResource] @ProjectId ", param1).ToList();
return(TemplateDetails);
}
public DateTime GetOldestTimesheetdateAvailableforApproval(string customerid)
{
DateTime oldesttimesheetdate;
var context = new CloudDbContext();
SqlParameter param1 = new SqlParameter("@CustId", int.Parse(customerid));
var TemplateDetails = context.Database.SqlQuery <DateTime>("[dbo].[usp_get_oldestTimesheetAvailableForApproval] @CustId", param1).SingleOrDefault();
if (DateTime.TryParse(TemplateDetails.ToString(), out oldesttimesheetdate))
{
return(oldesttimesheetdate);
}
else
{
return(DateTime.Today.AddDays(-7));
}
}
public IEnumerable <ResourceDetails> ResourceDetails(int EmpId, string ProjectId, int ManagerId)
{
var context = new CloudDbContext();
if (ProjectId == null)
{
ProjectId = "";
}
SqlParameter param1 = new SqlParameter("@ProjectId", ProjectId);
SqlParameter param2 = new SqlParameter("@EmpId", EmpId);
SqlParameter param3 = new SqlParameter("@ManagerId", ManagerId);
var TemplateDetails = context.Database.SqlQuery <ResourceDetails>("[dbo].[usp_get_all_Resource] @ProjectId, @EmpId, @ManagerId ", param1, param2, param3).ToList();
return(TemplateDetails);
}
public IEnumerable <TimesheetDatewise> TimesheetDatewise(DateTime StartDate, DateTime EndDate, int EmpId, string ProjectId)
{
var context = new CloudDbContext();
if (ProjectId == null)
{
ProjectId = "";
}
SqlParameter param1 = new SqlParameter("@EmpId", EmpId);
SqlParameter param2 = new SqlParameter("@StartDate", StartDate.ToString());
SqlParameter param3 = new SqlParameter("@EndDate", EndDate.ToString());
SqlParameter param4 = new SqlParameter("@ProjectId", ProjectId);
var TemplateDetails = context.Database.SqlQuery <TimesheetDatewise>("[dbo].[usp_get_MytimesheetDatewise] @EmpId, @StartDate, @EndDate, @ProjectId ", param1, param2, param3, param4).ToList();
return(TemplateDetails);
}
public DataTable GetTable(string spName, List <REPORTS_PARAMS> lstparams)
{
var dbContext = new CSPDbContext();
var dt = new DataTable();
using (var context = new CloudDbContext())
{
var conn = context.Database.Connection;
var connectionState = conn.State;
try
{
if (connectionState != ConnectionState.Open)
{
conn.Open();
}
using (var cmd = conn.CreateCommand())
{
cmd.CommandText = spName;
cmd.CommandType = CommandType.StoredProcedure;
foreach (REPORTS_PARAMS p in lstparams)
{
cmd.Parameters.Add(new SqlParameter("@" + p.PARAM_NAME, p.PARAM_VALUE));
}
using (var reader = cmd.ExecuteReader())
{
dt.Load(reader);
}
}
}
catch (Exception ex)
{
// error handling
throw;
}
finally
{
if (connectionState != ConnectionState.Closed)
{
conn.Close();
}
}
return(dt);
}
}
public bool UpdateProjectAliasName(string projectid, string projectaliasname)
{
bool oldesttimesheetdate = false;
var context = new CloudDbContext();
SqlParameter param1 = new SqlParameter("@projectid", projectid);
SqlParameter param2 = new SqlParameter("@projectaliasname", projectaliasname);
var TemplateDetails = context.Database.SqlQuery <string>("[dbo].[usp_update_ProjectAliasName] @projectid,@projectaliasname", param1, param2).SingleOrDefault();
if (bool.TryParse(TemplateDetails.ToString(), out oldesttimesheetdate))
{
return(oldesttimesheetdate);
}
else
{
return(oldesttimesheetdate);
}
}
public IEnumerable <PROJECT_RESOURCE> TimeSheetTaggedProjectByProjId(DateTime StartDate, DateTime EndDate, DateTime empStartDate, DateTime empEndDate, string projectid)
{
var context = new CloudDbContext();
SqlParameter param1 = new SqlParameter("@startdatefromdd", StartDate);
SqlParameter param2 = new SqlParameter("@enddatefromdd", EndDate);
//SqlParameter param3 = new SqlParameter("@projectid", projectid);
//SqlParameter param4 = new SqlParameter("@empid", empid);
SqlParameter param3 = new SqlParameter("@empStartDate", empStartDate);
SqlParameter param4 = new SqlParameter("@empEndDate", empEndDate);
SqlParameter param5 = new SqlParameter("@projectid", projectid);
//var TemplateDetails = context.Database.SqlQuery<PROJECT_RESOURCE>("[dbo].[spGetTimeSheetTaggedProjectForTeamPMO] @startdatefromdd, @enddatefromdd,@projectid,@empid,@empStartDate,@empEndDate", param1, param2, param3,param4,param5,param6).ToList();
var TemplateDetails = context.Database.SqlQuery <PROJECT_RESOURCE>("[dbo].[spGetTimeSheetTaggedProjectForTeamPMO] @startdatefromdd, @enddatefromdd,@empStartDate,@empEndDate,@projectid", param1, param2, param3, param4, param5).ToList();
return(TemplateDetails);
}
