public Dictionary <int, CompanyProjectModel> GetCompanyProjectModels(int companyId, int projectId) { int comId = GetCompanyId("Sunnet"); StringBuilder sqlStr = new StringBuilder(); sqlStr.Append("SELECT DISTINCT "); sqlStr.Append("Companys.ComID, "); sqlStr.Append("Companys.CompanyName, "); sqlStr.Append("Projects.ProjectID, "); sqlStr.Append("Projects.Title "); sqlStr.Append("FROM dbo.Companys AS Companys "); sqlStr.Append("INNER JOIN dbo.Projects AS Projects ON Projects.CompanyID = Companys.ComID "); sqlStr.Append("INNER JOIN dbo.TimeSheets AS TS ON TS.ProjectID = Projects.ProjectID "); sqlStr.Append("WHERE "); sqlStr.Append("NOT EXISTS (SELECT * FROM dbo.TSInvoiceRelation AS TIR WHERE TIR.TSId = TS.ID) "); sqlStr.Append("AND Companys.ComID != " + comId + ""); if (companyId > 0 && projectId == 0) { sqlStr.AppendFormat("AND Companys.ComID={0} ", companyId); } else if (projectId > 0 && companyId == 0) { sqlStr.AppendFormat("AND Projects.ProjectID={0} ", projectId); } else if (companyId > 0 && projectId > 0) { sqlStr.AppendFormat("AND Projects.ProjectID={0} AND Companys.ComID={1} ", projectId, companyId); } Database db = DatabaseFactory.CreateDatabase(); Dictionary <int, CompanyProjectModel> dicModels = new Dictionary <int, CompanyProjectModel>(); using (DbCommand dbCommand = db.GetSqlStringCommand(sqlStr.ToString())) { try { using (IDataReader dataReader = db.ExecuteReader(dbCommand)) { while (dataReader.Read()) { int comid = 0; object obj = new object(); obj = dataReader["ComID"]; if (obj != null && obj != DBNull.Value) { comid = (int)obj; } if (comid > 0 && !dicModels.ContainsKey(comid)) { CompanyProjectModel model = new CompanyProjectModel(); model.CompanyId = comid; model.CompanyName = dataReader["CompanyName"].ToString(); model.Projects = new List <ProjectSelectModel>(); dicModels.Add(comid, model); } if (comid > 0) { ProjectSelectModel projectModel = new ProjectSelectModel(); obj = dataReader["ProjectID"]; if (obj != null && obj != DBNull.Value) { projectModel.ProjectId = (int)obj; } projectModel.ProjectTitle = dataReader["Title"].ToString(); dicModels[comid].Projects.Add(projectModel); } } } } catch (Exception ex) { WebLogAgent.Write(string.Format("[SQLText:{0},{1}Messages:\r\n{2}]", sqlStr, base.FormatParameters(dbCommand.Parameters), ex.Message)); } } return(dicModels); }
/// <summary> /// TO DO page use this method /// </summary> /// <param name="request"></param> /// <returns></returns> public SearchInvoiceResponse SearchProposalInvoice(SearchInvoiceRequest request) { StringBuilder sqlStr = new StringBuilder(); sqlStr.Append(@"select P.ProjectId,P.Title as ProjectTitle,PT.Title as ProposalTrackerTitle ,PT.PONo, Invoices.Milestone,Invoices.InvoiceNo, Invoices.Status, PT.ProposalTrackerID,Invoices.ID as invoiceId from ProposalTracker PT LEFT JOIN Projects P on PT.ProjectID = p.ProjectId LEFT JOIN Invoices ON Invoices.ProposalId = PT.ProposalTrackerID "); sqlStr.Append("WHERE 1=1 "); sqlStr.Append("AND ("); sqlStr.Append("P.CompanyID != (select ComID from Companys where CompanyName='Sunnet')"); sqlStr.Append(")"); if (request.Keywords != "") { sqlStr.Append("AND ("); sqlStr.Append("Invoices.InvoiceNo LIKE @Keywords "); sqlStr.Append("OR P.Title LIKE @Keywords "); sqlStr.Append("OR PT.Title LIKE @Keywords "); sqlStr.Append(") "); } if (request.ProjectId != 0) { sqlStr.Append("AND P.ProjectID=@ProjectId "); } if (request.OrderExpression != "") { sqlStr.AppendFormat("ORDER BY {0} ", request.OrderExpression); } else { sqlStr.Append("ORDER BY P.Title "); } if (request.OrderDirection != "") { sqlStr.Append(request.OrderDirection); } else { sqlStr.Append("DESC;"); } List <ProposalToDoModel> list; SearchInvoiceResponse response = new SearchInvoiceResponse(); Database db = DatabaseFactory.CreateDatabase(); using (DbCommand dbCommand = db.GetSqlStringCommand(sqlStr.ToString())) { try { db.AddInParameter(dbCommand, "Keywords", DbType.String, string.Format("%{0}%", request.Keywords.FilterSqlString())); db.AddInParameter(dbCommand, "ProjectId", DbType.Int32, request.ProjectId); using (IDataReader dataReader = db.ExecuteReader(dbCommand)) { list = new List <ProposalToDoModel>(); while (dataReader.Read()) { list.Add(ProposalToDoModel.ReaderBind(dataReader)); } response.ProposalList = list; response.ResultCount = list.Count; } } catch (Exception ex) { WebLogAgent.Write(string.Format("[SQLText:{0},{1}Messages:\r\n{2}]", sqlStr.ToString(), base.FormatParameters(dbCommand.Parameters), ex.Message)); } } return(response); }
public void ProcessRequest(HttpContext context) { context.Response.ContentType = "text/plain"; try { if (IdentityContext.UserID <= 0) { return; } int result = 0; #region getValue int pId = Convert.ToInt32(context.Request["pId"]); string tType = context.Request["tType"]; string ckbEn = context.Request["ckbEn"]; string pty = context.Request["pty"]; string title = context.Request["title"]; string url = context.Request["url"]; string description = context.Request["descr"]; string imageList = context.Request["imageList"]; string imageSizeList = context.Request["imageSizeList"]; string StartDate = context.Request["StartDate"]; string DeliveryDate = context.Request["DeliveryDate"]; string satus = context.Request["satus"]; string IsSunnet = context.Request["isSunnet"]; string userlist = context.Request["userlist"]; #endregion UsersEntity entity = userApp.GetUser(IdentityContext.UserID); ProjectsEntity projectsEntity = projectApp.Get(pId); #region add ticket TicketsEntity ticketEntity = new TicketsEntity(); pty = string.IsNullOrEmpty(pty) ? "1" : pty; ticketEntity.ProjectID = pId; ticketEntity.CompanyID = projectsEntity.CompanyID; ticketEntity.Priority = (PriorityState)Convert.ToInt32(pty); ticketEntity.TicketType = (TicketsType)Convert.ToInt32(tType); ticketEntity.Title = title.NoHTML(); ticketEntity.URL = context.Server.UrlEncode(url); ticketEntity.FullDescription = description.NoHTML(); ticketEntity.CreatedBy = IdentityContext.UserID; ticketEntity.CreatedOn = DateTime.Now; ticketEntity.ModifiedOn = DateTime.Now; ticketEntity.IsEstimates = ckbEn == "checked" ? true : false; ticketEntity.TicketCode = new TicketsApplication().ConvertTicketTypeToTicketCode(ticketEntity.TicketType); ticketEntity.IsInternal = IsSunnet == "true" ? true : false; ticketEntity.ModifiedBy = 0; ticketEntity.PublishDate = UtilFactory.Helpers.CommonHelper.GetDefaultMinDate(); ticketEntity.ConvertDelete = CovertDeleteState.Normal; ticketEntity.Source = entity.Role; if (IsSunnet == "true") { ticketEntity.StartDate = !string.IsNullOrEmpty(StartDate.ToString()) ? DateTime.Parse(StartDate).Date : UtilFactory.Helpers.CommonHelper.GetDefaultMinDate(); ticketEntity.DeliveryDate = !string.IsNullOrEmpty(DeliveryDate.ToString()) ? DateTime.Parse(DeliveryDate).Date : UtilFactory.Helpers.CommonHelper.GetDefaultMinDate(); if (entity.Role == RolesEnum.Supervisor) { ticketEntity.Status = TicketsState.Submitted; } else { ticketEntity.Status = TicketsState.PM_Reviewed; } } else { ticketEntity.StartDate = UtilFactory.Helpers.CommonHelper.GetDefaultMinDate(); ticketEntity.DeliveryDate = UtilFactory.Helpers.CommonHelper.GetDefaultMinDate(); ticketEntity.Status = satus == "0" ? TicketsState.Draft : TicketsState.Submitted; } result = ticketAPP.AddTickets(ticketEntity); if (result > 0) { TicketUsersEntity ticketUserEntity = new TicketUsersEntity(); //add pm user ticketUserEntity.Type = TicketUsersType.PM; ticketUserEntity.TicketID = result; ProjectsEntity projectEntity = projectApp.Get(ticketEntity.ProjectID); if (projectEntity != null) { ticketUserEntity.UserID = projectEntity.PMID; ticketAPP.AddTicketUser(ticketUserEntity); } else { WebLogAgent.Write(string.Format("Add Pm To Ticket User Error:Project :{0},Ticket:{1},CreateDate:{2}", ticketEntity.ProjectID, ticketEntity.TicketID, DateTime.Now)); } //add create user ticketUserEntity.Type = TicketUsersType.Create; ticketUserEntity.TicketID = result; ticketUserEntity.UserID = ticketEntity.CreatedBy; ticketAPP.AddTicketUser(ticketUserEntity); } #endregion #region send email TicketStatusManagerApplication ex = new TicketStatusManagerApplication(); if (!ticketEntity.IsInternal) { ex.SendEmailToPMWhenTicketAdd(result, ticketEntity.TicketType); } #endregion #region add file FilesEntity fileEntity = new FilesEntity(); if (null != projectsEntity) { FolderName = projectsEntity.ProjectID.ToString(); } string sNewFileName = ""; tempPath = System.Configuration.ConfigurationManager.AppSettings["FolderPath"]; string[] listStringName = imageList.Split(','); string[] listStringSize = imageSizeList.Split(','); foreach (string Name in listStringName) { if (Name.Length == 0) { break; } string sExtension = Path.GetExtension(Name).Replace(".", "").Trim(); foreach (string Size in listStringSize) { sNewFileName = FolderName + Name; fileEntity.ContentType = "." + sExtension.ToLower(); fileEntity.CreatedBy = entity.UserID; fileEntity.FilePath = tempPath.Substring(2) + FolderName + @"/" + sNewFileName; fileEntity.FileSize = Convert.ToDecimal(Size.ToLower().Replace("kb", "")); fileEntity.FileTitle = Name.Substring(0, Name.LastIndexOf('.')); fileEntity.IsPublic = !ticketEntity.IsInternal; fileEntity.ProjectId = pId; fileEntity.TicketId = result; fileEntity.CreatedOn = DateTime.Now.Date; fileEntity.FeedbackId = 0; fileEntity.SourceType = (int)FileSourceType.Ticket; fileEntity.ThumbPath = context.Server.MapPath(tempPath) + FolderName + sNewFileName;;// fileEntity.CompanyID = IdentityContext.CompanyID; int response = fileApp.AddFile(fileEntity); if (response <= 0) { HasFileMsG = false; stringErrorMsg.Add(fileEntity.FileTitle); } break; } } #endregion #region response msg if (result > 0) { if (HasFileMsG) { context.Response.Write("The ticket has been added."); } else { string error = ""; foreach (string item in stringErrorMsg) { error += item + "File Upload Failed!"; } context.Response.Write(error); } } else { context.Response.Write("Add Fail!"); } #endregion #region assign user and send email TicketUsersEntity tuEntity = new TicketUsersEntity();; string[] userWithRoleList = userlist.TrimEnd(',').Split(','); int assignResult = 0; if (userWithRoleList.Length > 0) { foreach (string item in userWithRoleList) { if (item.Length > 0) { string[] userWithRole = item.Split('-'); if (userWithRole.Length > 0) { tuEntity.TicketID = result; tuEntity.UserID = Convert.ToInt32(userWithRole[0]); tuEntity.Type = GetUserTypeByRoleID(userWithRole[1]); //Convert.ToInt32(userWithRole[0]); assignResult = ticketAPP.AddTicketUser(tuEntity); if (assignResult > 0) { ex.SendEmailToAssignedUser(tuEntity); } } } } } #endregion } catch (Exception ex) { context.Response.Write("Input special symbol is not allowed,please check title and description!"); WebLogAgent.Write(string.Format("Error Ashx:DoAddTicketHandler.ashx Messages:\r\n{0}", ex)); return; } }
public SearchShareResponse GetShares(SearchShareRequest request) { string strWhere = ""; if (request.CreatedBy > 0) { strWhere += " AND CreatedBy = @CreatedBy "; } if (request.Type > 0) { strWhere += " AND Type = @Type "; } if (!string.IsNullOrEmpty(request.Keyword)) { int tmpTicketId; int.TryParse(request.Keyword, out tmpTicketId); strWhere += string.Format(" AND( Note like '%{0}%' OR TicketID ={1})", request.Keyword.FilterSqlString(), tmpTicketId); } if (request.StartDate > MinDate) { strWhere += " And CreatedOn >= @StartDate "; } else { request.StartDate = MinDate; } if (request.EndDate > MinDate) { strWhere += " And CreatedOn <= @EndDate "; } else { request.EndDate = MinDate; } string strSql = string.Format(@"SELECT BASICFILTER.*, TypeID = ST.ID, TypeTitle = ST.[Title], TypeCreatedBy = ST.[CreatedBy], TypeCreatedOn = ST.[CreatedOn],TypeType = ST.[Type] FROM ( SELECT * ,Files = (select CAST(FileID AS NVARCHAR)+ '_' + FileTitle + [ContentType]+'|' from [Files] WHERE [SourceType] = 7 AND [SourceID] = S.ID AND IsDelete = 0 FOR XML PATH('') ) ,Row_Number() OVER(ORDER BY {0} {1}) AS INDEX_ FROM [Share] S Where [IsDeleted] = 0 {2} ) AS BASICFILTER LEFT JOIN [ShareType] ST ON BASICFILTER.[Type] = ST.ID ", request.OrderExpression, request.OrderDirection, strWhere); if (request.IsPageModel) { strSql += string.Format("WHERE BASICFILTER.INDEX_ BETWEEN {0} AND {1} ;", (request.CurrentPage - 1) * request.PageCount + 1, request.CurrentPage * request.PageCount) ; strSql += string.Format(@"SELECT Count(1) as [Count] FROM [Share] Where [IsDeleted] = 0 {0}", strWhere); } var response = new SearchShareResponse(); Database db = DatabaseFactory.CreateDatabase(); using (DbCommand dbCommand = db.GetSqlStringCommand(strSql)) { try { db.AddInParameter(dbCommand, "CreatedBy", DbType.Int32, request.CreatedBy); db.AddInParameter(dbCommand, "Type", DbType.Int32, request.Type); db.AddInParameter(dbCommand, "StartDate", DbType.DateTime, request.StartDate); db.AddInParameter(dbCommand, "EndDate", DbType.DateTime, request.EndDate); using (IDataReader dataReader = db.ExecuteReader(dbCommand)) { while (dataReader.Read()) { response.Dataset.Add(ShareEntity.ReaderBind(dataReader)); } if (dataReader.NextResult() && dataReader.Read()) { response.Count = dataReader.GetInt32(0); } } } catch (Exception ex) { WebLogAgent.Write(string.Format("[SQLText:{0},{1}Messages:\r\n{2},\r\nSourceCode:{3}]" , strSql, base.FormatParameters(dbCommand.Parameters), ex.Message, ex.StackTrace)); return(null); } } return(response); }
/// <summary> /// /// </summary> /// <param name="roles">查询的用户角色</param> /// <param name="userIds">要排除的用户Id</param> /// <returns></returns> public List <UserTicketModel> SearchUserWithRole(List <RolesEnum> roles, string hideUserIds) { string strUserSql = ""; StringBuilder strUserSelect = new StringBuilder(); strUserSelect.Append("SELECT "); strUserSelect.Append("U.UserID, "); strUserSelect.Append("U.FirstName, "); strUserSelect.Append("U.LastName, "); strUserSelect.Append("R.RoleID, "); strUserSelect.Append("R.RoleName "); strUserSelect.Append("FROM dbo.Users AS U "); strUserSelect.Append("LEFT JOIN dbo.Roles AS R ON U.RoleID = R.RoleID "); StringBuilder strWhere = new StringBuilder(); strWhere.Append("WHERE "); strWhere.Append("U.Status='ACTIVE' "); if (roles.Count > 0) { strWhere.AppendFormat("AND U.RoleID IN({0}) ", string.Join(",", roles.Select(x => (int)x).ToList())); } if (hideUserIds.Length > 0) { strWhere.Append("AND U.UserID NOT IN( " + hideUserIds + ") "); } StringBuilder strUserOrder = new StringBuilder(); strUserOrder.Append("ORDER BY U.RoleID ,U.FirstName; "); strUserSql = strUserSelect.ToString() + strWhere.ToString() + strUserOrder.ToString(); DateTime now = DateTime.Now; DateTime lastMonthStart = now.AddDays(1 - now.Day).AddMonths(-1).Date; DateTime currentMonthStart = now.AddDays(1 - now.Day).Date; StringBuilder strTicketCountSql = new StringBuilder(); strTicketCountSql.Append("SELECT TH.ModifiedBy,COUNT(DISTINCT TH.TicketID) AS TicketCount "); strTicketCountSql.Append("FROM dbo.TicketHistorys AS TH "); strTicketCountSql.Append("WHERE TH.ModifiedBy IN ("); strTicketCountSql.Append("SELECT U.UserID FROM dbo.Users AS U "); strTicketCountSql.Append(strWhere.ToString()); strTicketCountSql.Append(") "); strTicketCountSql.Append("{0} "); strTicketCountSql.Append("GROUP BY TH.ModifiedBy;"); StringBuilder strPreviousSql = new StringBuilder(); StringBuilder strPreviousWhereSql = new StringBuilder(); strPreviousWhereSql.Append("AND TH.ModifiedOn >= '" + lastMonthStart + "' "); strPreviousWhereSql.Append("AND TH.ModifiedOn < '" + currentMonthStart + "' "); strPreviousSql.AppendFormat(strTicketCountSql.ToString(), strPreviousWhereSql.ToString()); StringBuilder strCurrentSql = new StringBuilder(); StringBuilder strCurrentWhereSql = new StringBuilder(); strCurrentWhereSql.Append("AND TH.ModifiedOn >= '" + currentMonthStart + "' "); strCurrentSql.AppendFormat(strTicketCountSql.ToString(), strCurrentWhereSql.ToString()); StringBuilder strTicketSql = new StringBuilder(); strTicketSql.Append("SELECT T.ResponsibleUser,T.TicketID,T.Title,T.ProjectID,P.Title AS ProjectName,T.Priority "); strTicketSql.Append("FROM dbo.Tickets AS T LEFT JOIN dbo.Projects AS P ON T.ProjectID=P.ProjectID "); strTicketSql.Append("WHERE T.ResponsibleUser IN ( "); strTicketSql.Append("SELECT U.UserID FROM dbo.Users AS U "); strTicketSql.Append(strWhere.ToString()); strTicketSql.Append(") "); strTicketSql.Append("AND T.ProjectID IN (SELECT ProjectID FROM ProjectUsers PU WHERE PU.UserID IN ("); strTicketSql.Append("SELECT U.UserID FROM dbo.Users AS U "); strTicketSql.Append(strWhere.ToString()); strTicketSql.Append(") "); strTicketSql.Append(") "); List <TicketsState> status = new List <TicketsState>(); status.AddRange(TicketsStateHelper.SunnetSHAllowShowStatus); string strStatus = string.Join(",", status.Select(x => (int)x).ToList()); strTicketSql.Append("AND T.Status IN (1, 4, 9, 3, 5, 7, 10, 13, 17, 6, 12, 15, 11, 14, 8, 18, 16, 20, 31, 32) "); strTicketSql.Append("ORDER BY T.ModifiedOn DESC;"); string strSql = strTicketSql.ToString() + strPreviousSql.ToString() + strCurrentSql.ToString() + strUserSql; Database db = DatabaseFactory.CreateDatabase(); List <UserTicketModel> userTickets = new List <UserTicketModel>(); using (DbCommand dbCommand = db.GetSqlStringCommand(strSql)) { try { using (IDataReader sdr = db.ExecuteReader(dbCommand)) { List <TicketListModel> tickets = new List <TicketListModel>(); while (sdr.Read()) { TicketListModel ticket = new TicketListModel { ResponsibleUserID = int.Parse(sdr["ResponsibleUser"].ToString()), TicketID = int.Parse(sdr["TicketID"].ToString()), Title = sdr["Title"].ToString(), ProjectID = int.Parse(sdr["ProjectID"].ToString()), ProjectName = sdr["ProjectName"].ToString(), Priority = (PriorityState)sdr["Priority"] }; tickets.Add(ticket); } bool isNext = sdr.NextResult(); List <TicketHistoryModel> previousList = new List <TicketHistoryModel>(); while (sdr.Read() && isNext) { TicketHistoryModel model = new TicketHistoryModel { UserID = int.Parse(sdr[0].ToString()), TicketCount = int.Parse(sdr[1].ToString()) }; previousList.Add(model); } isNext = sdr.NextResult(); List <TicketHistoryModel> currentList = new List <TicketHistoryModel>(); while (sdr.Read() && isNext) { TicketHistoryModel model = new TicketHistoryModel { UserID = int.Parse(sdr[0].ToString()), TicketCount = int.Parse(sdr[1].ToString()) }; currentList.Add(model); } isNext = sdr.NextResult(); while (sdr.Read() && isNext) { UserTicketModel model = new UserTicketModel { UserID = int.Parse(sdr[0].ToString()), FirstName = sdr[1].ToString(), LastName = sdr[2].ToString(), RoleID = int.Parse(sdr[3].ToString()), RoleName = sdr[4].ToString() }; model.Tickets = tickets.Where(t => t.ResponsibleUserID == model.UserID).Take(10).ToList(); model.TicketCount = tickets.Count(t => t.ResponsibleUserID == model.UserID); if (previousList.Count(p => p.UserID == model.UserID) > 0) { model.Previous = previousList.First(p => p.UserID == model.UserID).TicketCount; } else { model.Previous = 0; } if (currentList.Count(p => p.UserID == model.UserID) > 0) { model.Current = currentList.First(p => p.UserID == model.UserID).TicketCount; } else { model.Current = 0; } userTickets.Add(model); } } return(userTickets); } catch (Exception ex) { WebLogAgent.Write(string.Format("[SQLText:{0},{1}Messages:\r\n{2}]", strSql, base.FormatParameters(dbCommand.Parameters), ex.Message)); } } return(userTickets); }
/// <summary> /// 获取指定UserID 创建的与别人邀请的 ,并且ticket 的 Project 是 指定 currentUserId可见的 /// UserId 为 零时,表示所有,使用 allUser 参数 /// </summary> public List <EventEntity> GetEvents(int currentUserId, DateTime startDate, int userId, string allUser, int projectID, int pageSize, int pageNo, out int recordCount) { recordCount = 0; List <EventEntity> list = new List <EventEntity>(); StringBuilder strSql = new StringBuilder(); strSql.Append("SELECT COUNT(ID) FROM dbo.Events where 1=1") .AppendFormat(" and FromDay >= '{0}' ", startDate); //显示指定 project 的event if (projectID > 0) { strSql.AppendFormat(" and ProjectID ={0} ", projectID); } else { strSql.AppendFormat(" and ProjectID in (select ProjectID from ProjectUsers where userid ={0}) ", currentUserId); } if (userId == 0) { strSql.AppendFormat(" and (CreatedBy in ({0}) or ID in (select EventID from EventInvites where UserID in ({0})))", allUser); } else { strSql.AppendFormat(" and (CreatedBy={0} or ID in (select EventID from EventInvites where UserID = {0}))", userId); } strSql.Append(";SELECT * FROM ( SELECT ROW_NUMBER() OVER (ORDER BY FromDay asc) AS RowNumber, * FROM dbo.Events") .AppendFormat(" where FromDay >= '{0}' ", startDate); //显示指定 project 的event if (projectID > 0) { strSql.AppendFormat(" and ProjectID ={0} ", projectID); } else { strSql.AppendFormat(" and ProjectID in (select ProjectID from ProjectUsers where userid ={0}) ", currentUserId); } if (userId == 0) { strSql.AppendFormat(" and (CreatedBy in ({0}) or ID in (select EventID from EventInvites where UserID in ({0})))", allUser); } else { strSql.AppendFormat(" and (CreatedBy={0} or ID in (select EventID from EventInvites where UserID = {0}))", userId); } strSql.Append(") AS TB") .AppendFormat(" WHERE RowNumber BETWEEN {0} AND {1} ", (pageNo - 1) * pageSize + 1, pageNo * pageSize); Database db = DatabaseFactory.CreateDatabase(); using (DbCommand dbCommand = db.GetSqlStringCommand(strSql.ToString())) { try { IDataReader reader = db.ExecuteReader(dbCommand); if (reader.Read()) { recordCount = (int)reader[0]; } if (reader.NextResult()) { while (reader.Read()) { list.Add(new EventEntity(reader)); } } } catch (Exception ex) { WebLogAgent.Write(string.Format("[SQLText:{0},{1}Messages:\r\n{2}]", strSql.ToString(), base.FormatParameters(dbCommand.Parameters), ex.Message)); } } return(list); }
/// <summary> /// save : 1:save ; 2:draft ;3 save and new /// </summary> /// <param name="save"></param> private void SaveTicket(int save) { #region add ticket TicketsEntity ticketsEntity = new TicketsEntity(); ticketsEntity.Title = txtTitle.Value.NoHTML(); ticketsEntity.FullDescription = txtDesc.Value.NoHTML(); ticketsEntity.URL = txtUrl.Value; ticketsEntity.ProjectID = int.Parse(ddlProject.SelectedValue); ticketsEntity.TicketType = (TicketsType)int.Parse(ddlType.SelectedValue); ticketsEntity.TicketCode = new TicketsApplication().ConvertTicketTypeToTicketCode(ticketsEntity.TicketType); ticketsEntity.Priority = (PriorityState)int.Parse(this.radioPriority.SelectedValue); ProjectsEntity projectsEntity = new ProjectApplication().Get(ticketsEntity.ProjectID); ticketsEntity.CompanyID = projectsEntity.CompanyID; ticketsEntity.IsEstimates = chkEN.Checked; ticketsEntity.IsInternal = false; ticketsEntity.CreatedBy = UserInfo.UserID; ticketsEntity.CreatedOn = DateTime.Now; ticketsEntity.ModifiedBy = UserInfo.UserID; ticketsEntity.ModifiedOn = DateTime.Now; ticketsEntity.PublishDate = UtilFactory.Helpers.CommonHelper.GetDefaultMinDate(); ticketsEntity.ConvertDelete = CovertDeleteState.Normal; ticketsEntity.StartDate = UtilFactory.Helpers.CommonHelper.GetDefaultMinDate(); ticketsEntity.DeliveryDate = UtilFactory.Helpers.CommonHelper.GetDefaultMinDate(); if (UserInfo.Role == RolesEnum.PM || UserInfo.Role == RolesEnum.ADMIN) { ticketsEntity.Source = (RolesEnum)Enum.Parse(typeof(RolesEnum), ddlSource.SelectedValue); } else { ticketsEntity.Source = UserInfo.Role; } if (save == 2) { ticketsEntity.Status = TicketsState.Draft; } else { if (UserInfo.Role == RolesEnum.PM || UserInfo.Role == RolesEnum.ADMIN) { ticketsEntity.Status = TicketsState.PM_Reviewed; } else { ticketsEntity.Status = TicketsState.Submitted; } } int result = new TicketsApplication().AddTickets(ticketsEntity); if (result > 0) { TicketUsersEntity ticketUserEntity = new TicketUsersEntity(); //add pm user ticketUserEntity.Type = TicketUsersType.PM; ticketUserEntity.TicketID = result; ProjectsEntity projectEntity = new ProjectApplication().Get(ticketsEntity.ProjectID); if (projectEntity != null) { ticketUserEntity.UserID = projectEntity.PMID; new TicketsApplication().AddTicketUser(ticketUserEntity); } else { WebLogAgent.Write(string.Format("Add Pm To Ticket User Error:Project :{0},Ticket:{1},CreateDate:{2}", ticketsEntity.ProjectID, ticketsEntity.TicketID, DateTime.Now)); } //add create user ticketUserEntity.Type = TicketUsersType.Create; ticketUserEntity.TicketID = result; ticketUserEntity.UserID = ticketsEntity.CreatedBy; new TicketsApplication().AddTicketUser(ticketUserEntity); if (UserInfo.Role == RolesEnum.PM || UserInfo.Role == RolesEnum.ADMIN) { //添加当前Project中的Leader到此ticket下. List <ProjectUsersEntity> ProjectUsers = new ProjectApplication().GetProjectSunnetUserList(projectsEntity.ID); if (ProjectUsers != null) { List <ProjectUsersEntity> leaders = ProjectUsers.FindAll(r => r.Role == RolesEnum.Leader); foreach (ProjectUsersEntity leader in leaders) { ticketUserEntity = new TicketUsersEntity(); ticketUserEntity.Type = TicketUsersType.Dev; ticketUserEntity.TicketID = result; ticketUserEntity.UserID = leader.UserID; new TicketsApplication().AddTicketUser(ticketUserEntity); } } } #endregion #region send email TicketStatusManagerApplication ex = new TicketStatusManagerApplication(); if (!ticketsEntity.IsInternal) { ex.SendEmailToPMWhenTicketAdd(result, ticketsEntity.TicketType); } #endregion #region add file if (fileupload.PostedFile.ContentLength > 0) { string FolderName = string.Empty; if (null != projectsEntity) { FolderName = projectsEntity.ProjectID.ToString(); } string filderPath = System.Configuration.ConfigurationManager.AppSettings["FolderPath"]; //~/path string savepath = Server.MapPath(filderPath) + FolderName; string filename = fileupload.PostedFile.FileName; string fileExtension = Path.GetExtension(filename); if (!Directory.Exists(savepath)) { Directory.CreateDirectory(savepath); } string sNewFileName = string.Format("{0}_{1}{2}", result, DateTime.Now.ToString("yyMMddssmm"), fileExtension); fileupload.PostedFile.SaveAs(savepath + @"\" + sNewFileName); FilesEntity fileEntity = new FilesEntity(); fileEntity.ContentType = fileExtension.ToLower(); fileEntity.CreatedBy = ticketsEntity.CreatedBy; fileEntity.FilePath = filderPath.Substring(2) + FolderName + @"/" + sNewFileName; fileEntity.FileSize = fileupload.PostedFile.ContentLength; fileEntity.FileTitle = filename.Substring(0, filename.LastIndexOf('.')); fileEntity.IsPublic = !ticketsEntity.IsInternal; fileEntity.ProjectId = ticketsEntity.ProjectID; fileEntity.TicketId = result; fileEntity.CreatedOn = DateTime.Now.Date; fileEntity.FeedbackId = 0; fileEntity.SourceType = (int)FileSourceType.Ticket; fileEntity.ThumbPath = ""; fileEntity.CompanyID = ticketsEntity.CompanyID; int response = new FileApplication().AddFile(fileEntity); } #endregion switch (save) { case 1: ShowMessageAndRedirect("The ticket has been added.", "/sunnet/Clients/ListTicket.aspx"); break; case 2: ShowMessageAndRedirect("The ticket has been added.", "/sunnet/Clients/ListTicketDrafted.aspx"); break; case 3: ShowMessageAndRedirect("The ticket has been added.", "/sunnet/Clients/AddBug.aspx"); break; } } else { ShowFailMessageToClient(); } }
public void Generate(List <TimeSheetTicket> tss, UsersEntity user, string projectTitle, DateTime eDate) { string fileName = string.Empty; string outputFileName = string.Empty; if (user == null) { if (!string.IsNullOrEmpty(projectTitle) && projectTitle == "All") { fileName = GenerateSearchResultReport(tss); } else { foreach (TimeSheetTicket timeSheetTicket in tss) { decimal hours = timeSheetTicket.Hours; decimal integerPart = Decimal.Floor(hours); if (!Decimal.Equals(integerPart, hours)) { timeSheetTicket.Hours = integerPart + 1; } } fileName = GenerateSearchProjectResultReport(tss, projectTitle); } outputFileName = "TimeSheet.xlsx"; } else { //if (!string.IsNullOrEmpty(projectTitle) && projectTitle == "All") // fileName = GenerateReport(tss, user); //else // fileName = GenerateUserProjectReport(tss, user,projectTitle); fileName = GenerateReport(tss, user); outputFileName = GetOutputFileName(tss, user, projectTitle, eDate);//user.FirstName + " " + user.LastName + "'s TimeSheetTicket.xlsx"; } int appInt = GC.GetGeneration(app); System.Runtime.InteropServices.Marshal.ReleaseComObject(app); System.Runtime.InteropServices.Marshal.ReleaseComObject(ws); System.Runtime.InteropServices.Marshal.ReleaseComObject(wb); ws = null; wb = null; app = null; GC.Collect(appInt); GC.Collect(); GC.WaitForPendingFinalizers(); MemoryStream ms = new MemoryStream(File.ReadAllBytes(fileName)); try { File.Delete(fileName); } catch (Exception ex) { WebLogAgent.Write(ex); } HttpContext.Current.Response.Clear(); HttpContext.Current.Response.Charset = "utf-8"; HttpContext.Current.Response.Buffer = false; HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.UTF8; HttpContext.Current.Response.AppendHeader("Content-Disposition", "attachment;filename=" + outputFileName); HttpContext.Current.Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"; byte[] bytes = ms.ToArray(); HttpContext.Current.Response.AddHeader("Content-Length", bytes.Length.ToString()); HttpContext.Current.Response.OutputStream.Write(bytes, 0, bytes.Length); HttpContext.Current.Response.OutputStream.Flush(); HttpContext.Current.Response.End(); }
public List <ProposalTrackerEntity> GetProposalTrackers(string keyword, int projectId, int status, int payment, int userId, string order, string dir) { StringBuilder strSql = new StringBuilder(); strSql.Append(@"SELECT w.*,p.Title as ProjectName,ROW_NUMBER() OVER (order by workrequestid desc) as rowno"); strSql.Append(@" FROM ProposalTracker w "); strSql.Append(@"left join Projects p on w.ProjectID= p.ProjectID"); strSql.Append(@" where 1=1 "); if (projectId > 0) { strSql.Append(@" AND w.projectId=@projectId "); } if (status > 0) { strSql.Append(@" AND w.status=@status "); } if (payment > 0) { strSql.Append(@" AND w.payment=@payment "); } if (!string.IsNullOrEmpty(keyword)) { strSql.Append(@" AND (w.requestNo LIKE @keyword OR w.Title LIKE @keyword OR InvoiceNo LIKE @keyword )"); } if (userId > 0) { strSql.Append(@" AND w.projectid in (select projectid from projectusers where userid = @userId) "); } strSql.Append(@" order by @order+ ' '+ @dir "); List <ProposalTrackerEntity> list = new List <ProposalTrackerEntity>(); Database db = DatabaseFactory.CreateDatabase(); using (DbCommand dbCommand = db.GetSqlStringCommand(strSql.ToString())) { try { db.AddInParameter(dbCommand, "keyword", DbType.String, string.Format("%{0}%", keyword.FilterSqlString())); db.AddInParameter(dbCommand, "Status", DbType.String, status); db.AddInParameter(dbCommand, "projectId", DbType.Int32, projectId); db.AddInParameter(dbCommand, "payment", DbType.Int32, payment); db.AddInParameter(dbCommand, "userId", DbType.Int32, userId); db.AddInParameter(dbCommand, "order", DbType.String, order); db.AddInParameter(dbCommand, "dir", DbType.String, dir); using (IDataReader dataReader = db.ExecuteReader(dbCommand)) while (dataReader.Read()) { list.Add(ProposalTrackerEntity.ReaderBind(dataReader)); } } catch (Exception ex) { WebLogAgent.Write(string.Format("[SQLText:{0},{1}Messages:\r\n{2}]" , strSql.ToString(), base.FormatParameters(dbCommand.Parameters), ex.Message)); return(null); } } return(list); }
/// <summary> /// 查询Project ongoing tickets /// </summary> /// <param name="internalProject">是否查询sunnet内部项目</param> /// <returns></returns> public List <ProjectTicketModel> GetProjectTicketList(bool internalProject, int userId) { List <ProjectStatus> projectStatus = new List <ProjectStatus>(); projectStatus.Add(ProjectStatus.Open); projectStatus.Add(ProjectStatus.Scheduled); projectStatus.Add(ProjectStatus.InProcess); projectStatus.Add(ProjectStatus.Other); List <TicketsState> ticketStatus = TicketsStateHelper.SunnetUSAllowShowStatus; ticketStatus.Remove(TicketsState.Cancelled); ticketStatus.Remove(TicketsState.Completed); ticketStatus.Remove(TicketsState.Internal_Cancel); StringBuilder strProject = new StringBuilder(); strProject.Append("SELECT P.ProjectID,P.ProjectCode,P.Title AS ProjectName, P.Status AS ProjectStatus, P.PMID, C.ComID, C.CompanyName "); StringBuilder strProjectWhere = new StringBuilder(); strProjectWhere.Append("FROM dbo.Projects AS P "); strProjectWhere.Append("LEFT JOIN dbo.Companys AS C ON C.ComID = P.CompanyID "); if (internalProject) { strProjectWhere.Append("WHERE C.CompanyName = 'Sunnet' "); } else { strProjectWhere.Append("WHERE C.CompanyName != 'Sunnet' "); } strProjectWhere.AppendFormat("AND P.Status IN ({0}) ", string.Join(",", projectStatus.Select(x => (int)x).ToList())); strProjectWhere.Append("AND P.ProjectID IN (SELECT DISTINCT PU.ProjectID FROM dbo.ProjectUsers AS PU WHERE PU.UserID = @UserID ) "); strProject.Append(strProjectWhere.ToString()); strProject.Append("ORDER BY P.Title "); StringBuilder strTicket = new StringBuilder(); strTicket.Append("SELECT T.TicketID , T.ProjectID , T.Status AS TicketStatus, T.Title, T.ResponsibleUser AS ResponsibleUserID, T.Priority "); strTicket.Append("FROM dbo.Tickets AS T "); strTicket.AppendFormat("WHERE T.Status IN ({0}) ", string.Join(",", ticketStatus.Select(x => (int)x).ToList())); strTicket.Append("AND T.ProjectID IN (SELECT P.ProjectID "); strTicket.Append(strProjectWhere.ToString()); strTicket.Append(") "); strTicket.Append("ORDER BY T.ModifiedOn DESC;"); string strSql = strTicket.ToString() + strProject.ToString(); Database db = DatabaseFactory.CreateDatabase(); List <ProjectTicketModel> modelList = new List <ProjectTicketModel>(); using (DbCommand dbCommand = db.GetSqlStringCommand(strSql)) { try { db.AddInParameter(dbCommand, "UserID", DbType.Int32, userId); using (IDataReader dr = db.ExecuteReader(dbCommand)) { List <TicketStatusModel> ticketList = new List <TicketStatusModel>(); while (dr.Read()) { TicketStatusModel ticket = new TicketStatusModel { TicketID = int.Parse(dr["TicketID"].ToString()), ProjectID = int.Parse(dr["ProjectID"].ToString()), TicketStatus = (TicketsState)dr["TicketStatus"], Title = dr["Title"].ToString(), ResponsibleUserID = int.Parse(dr["ResponsibleUserID"].ToString()), Priority = (PriorityState)dr["Priority"] }; ticketList.Add(ticket); } bool isNext = dr.NextResult(); while (dr.Read() && isNext) { ProjectTicketModel project = new ProjectTicketModel { ProjectID = int.Parse(dr["ProjectID"].ToString()), ProjectCode = dr["ProjectCode"].ToString(), ProjectName = dr["ProjectName"].ToString(), ProjectStatus = (ProjectStatus)dr["ProjectStatus"], PMID = int.Parse(dr["PMID"].ToString()), CompanyID = int.Parse(dr["ComID"].ToString()), CompanyName = dr["CompanyName"].ToString() }; project.Tickets = ticketList.Where(t => t.ProjectID == project.ProjectID).Take(10).ToList(); project.OngoingTickets = ticketList.Count(t => t.ProjectID == project.ProjectID); modelList.Add(project); } } } catch (Exception ex) { WebLogAgent.Write(string.Format("[SQLText:{0},{1}Messages:\r\n{2}]", strSql, base.FormatParameters(dbCommand.Parameters), ex.Message)); } } return(modelList); }
/// <summary> /// Update a record /// </summary> public bool Update(ProjectsEntity model) { StringBuilder strSql = new StringBuilder(); strSql.Append("update Projects set "); strSql.Append("CompanyID=@CompanyID,"); strSql.Append("ProjectCode=@ProjectCode,"); strSql.Append("Title=@Title,"); strSql.Append("Description=@Description,"); strSql.Append("StartDate=@StartDate,"); strSql.Append("EndDate=@EndDate,"); strSql.Append("Status=@Status,"); strSql.Append("CreatedBy=@CreatedBy,"); strSql.Append("CreatedOn=@CreatedOn,"); strSql.Append("ModifiedBy=@ModifiedBy,"); strSql.Append("ModifiedOn=@ModifiedOn,"); strSql.Append("PMID=@PMID,"); strSql.Append("Priority=@Priority,"); strSql.Append("Billable=@Billable,"); strSql.Append("TestLinkURL=@TestLinkURL,"); strSql.Append("TestUserName=@TestUserName,"); strSql.Append("TestPassword=@TestPassword,"); strSql.Append("FreeHour=@FreeHour,"); strSql.Append("BugNeedApproved=@BugNeedApproved,"); strSql.Append("RequestNeedApproved=@RequestNeedApproved,"); strSql.Append("IsOverFreeTime=@IsOverFreeTime,"); strSql.Append("TotalHours=@TotalHours,"); strSql.Append("MaintenancePlanOption=@MaintenancePlanOption"); strSql.Append(" where ProjectID=@ProjectID ;"); strSql.Append(" update [" + Config.NearForumsDataBase + "].[dbo].[Forums] set [ForumName]=@Title,[ForumDescription]=@Title,"); strSql.Append("[Active]=(case when @Status=5 then 0 else 1 end) where ProjectID=@ProjectID"); Database db = DatabaseFactory.CreateDatabase(); using (DbCommand dbCommand = db.GetSqlStringCommand(strSql.ToString())) { try { db.AddInParameter(dbCommand, "ProjectID", DbType.Int32, model.ProjectID); db.AddInParameter(dbCommand, "CompanyID", DbType.Int32, model.CompanyID); db.AddInParameter(dbCommand, "ProjectCode", DbType.String, model.ProjectCode); db.AddInParameter(dbCommand, "Title", DbType.String, model.Title); db.AddInParameter(dbCommand, "Description", DbType.String, model.Description); db.AddInParameter(dbCommand, "StartDate", DbType.DateTime, model.StartDate); db.AddInParameter(dbCommand, "EndDate", DbType.DateTime, model.EndDate); db.AddInParameter(dbCommand, "Status", DbType.Int32, model.Status); db.AddInParameter(dbCommand, "CreatedBy", DbType.Int32, model.CreatedBy); db.AddInParameter(dbCommand, "CreatedOn", DbType.DateTime, model.CreatedOn); db.AddInParameter(dbCommand, "ModifiedBy", DbType.Int32, model.ModifiedBy); db.AddInParameter(dbCommand, "ModifiedOn", DbType.DateTime, model.ModifiedOn); db.AddInParameter(dbCommand, "PMID", DbType.Int32, model.PMID); db.AddInParameter(dbCommand, "Priority", DbType.String, model.Priority); db.AddInParameter(dbCommand, "Billable", DbType.Boolean, model.Billable); db.AddInParameter(dbCommand, "TestLinkURL", DbType.String, model.TestLinkURL); db.AddInParameter(dbCommand, "TestUserName", DbType.String, model.TestUserName); db.AddInParameter(dbCommand, "TestPassword", DbType.String, model.TestPassword); db.AddInParameter(dbCommand, "FreeHour", DbType.Int32, model.FreeHour); db.AddInParameter(dbCommand, "BugNeedApproved", DbType.Boolean, model.BugNeedApproved); db.AddInParameter(dbCommand, "RequestNeedApproved", DbType.Boolean, model.RequestNeedApproved); db.AddInParameter(dbCommand, "IsOverFreeTime", DbType.Boolean, model.IsOverFreeTime); db.AddInParameter(dbCommand, "MaintenancePlanOption", DbType.String, model.MaintenancePlanOption); db.AddInParameter(dbCommand, "TotalHours", DbType.String, model.TotalHours); int rows = db.ExecuteNonQuery(dbCommand); if (rows > 0) { return(true); } else { return(false); } } catch (Exception ex) { WebLogAgent.Write(string.Format("[SQLText:{0},{1}Messages:\r\n{2}]", strSql.ToString(), base.FormatParameters(dbCommand.Parameters), ex.Message)); return(false); } } }
public SearchProjectsResponse SearchProjects(SearchProjectsRequest request) { int start = request.CurrentPage * request.PageCount + 1 - request.PageCount; int end = request.CurrentPage * request.PageCount; string strSelCount = " SELECT COUNT(1) FROM Projects p"; string strOrderby = string.Format(" ({0}) {1} ", request.OrderExpression, request.OrderDirection); string strSelAttrs = @" SELECT p.*, (SELECT UserName FROM Users WHERE UserID =P.ModifiedBy) AS ModifiedByUserName, (SELECT UserName FROM Users WHERE UserID =P.CreatedBy) AS CreatedByUserName, (SELECT UserName FROM Users WHERE UserID =P.PMID) AS PMUserName, (SELECT FirstName FROM Users WHERE UserID =P.PMID) AS PMFirstName, (SELECT LastName FROM Users WHERE UserID =P.PMID) AS PMLastName, (select CompanyName from Companys where ComID=p.CompanyID ) as CompanyName FROM Projects p "; string strSelAttrsOrderBy = string.Format(@" Order BY {0} ", strOrderby); string strSelPageModel = string.Format(@"SELECT * FROM( SELECT ROW_NUMBER() OVER( Order BY {0}) as INDEX_ID,p.* , (SELECT UserName FROM Users WHERE UserID =P.ModifiedBy) AS ModifiedByUserName, (SELECT UserName FROM Users WHERE UserID =P.CreatedBy) AS CreatedByUserName, (SELECT UserName FROM Users WHERE UserID =P.PMID) AS PMUserName , (SELECT FirstName FROM Users WHERE UserID =P.PMID) AS PMFirstName, (SELECT LastName FROM Users WHERE UserID =P.PMID) AS PMLastName, (select CompanyName from Companys where ComID=p.CompanyID ) as CompanyName FROM Projects p ", strOrderby); string strWherePageModel = @") NEW_TB WHERE INDEX_ID BETWEEN @Strat AND @End;"; StringBuilder strWhere = new StringBuilder(); strWhere.Append(" WHERE 1=1 "); switch (request.SearchType) { case SearchProjectsType.All: break; case SearchProjectsType.AllExceptAssigned: strWhere.Append(" AND p.[ProjectID] not in (Select ProjectID from ProjectUsers pu where pu.UserID=@UserID)"); strWhere.Append(" AND (p.Title LIKE @Keywords OR p.ProjectCode LIKE @Keywords )"); break; case SearchProjectsType.List: strWhere.Append(" AND (p.Title LIKE @Keywords OR p.ProjectCode LIKE @Keywords )"); if (request.CompanyID != 0) { strWhere.Append(" AND CompanyID=@CompanyID"); } break; case SearchProjectsType.Company: if (request.CompanyID != 0) { strWhere.Append(" AND p.[CompanyID] = @CompanyID"); } strWhere.Append(" AND (p.Title LIKE @Keywords OR p.ProjectCode LIKE @Keywords )"); break; case SearchProjectsType.CompanyExceptAssigned: if (request.CompanyID != 0) { strWhere.Append(" AND p.[CompanyID] = @CompanyID"); } strWhere.Append(" AND (p.Title LIKE @Keywords OR p.ProjectCode LIKE @Keywords )"); strWhere.Append(" AND p.[ProjectID] not in (Select ProjectID from ProjectUsers pu where pu.UserID=@UserID)"); break; case SearchProjectsType.SingleInstance: strWhere.Append(" AND p.[ProjectID] = @ProjectID"); break; case SearchProjectsType.ListByUserID: strWhere.Append(" AND p.[ProjectID] in (Select ProjectID from ProjectUsers pu where pu.UserID=@UserID)"); break; case SearchProjectsType.Ticket: strWhere.Append(" AND p.[ProjectID] IN (SELECT [ProjectID] FROM [Tickets] WHERE [TicketID]=@TicketID)"); break; default: break; } StringBuilder strSql = new StringBuilder(); if (request.IsPageModel) { strSql.Append(strSelCount); strSql.Append(strWhere); strSql.Append(";"); strSql.Append(strSelPageModel); strSql.Append(strWhere); strSql.Append(strWherePageModel); } else { strSql.Append(strSelAttrs); strSql.Append(strWhere); strSql.Append(strSelAttrsOrderBy); strSql.Append(";"); } List <ProjectDetailDTO> list; SearchProjectsResponse response = new SearchProjectsResponse(); Database db = DatabaseFactory.CreateDatabase(); using (DbCommand dbCommand = db.GetSqlStringCommand(strSql.ToString())) { try { db.AddInParameter(dbCommand, "UserID", DbType.Int32, request.UserID); db.AddInParameter(dbCommand, "TicketID", DbType.Int32, request.TicketID); db.AddInParameter(dbCommand, "ProjectID", DbType.Int32, request.ProjectID); db.AddInParameter(dbCommand, "CompanyID", DbType.Int32, request.CompanyID); db.AddInParameter(dbCommand, "Keywords", DbType.String, string.Format("%{0}%", request.Keywords.FilterSqlString())); db.AddInParameter(dbCommand, "Strat", DbType.Int32, start); db.AddInParameter(dbCommand, "End", DbType.Int32, end); using (IDataReader dataReader = db.ExecuteReader(dbCommand)) { list = new List <ProjectDetailDTO>(); if (request.IsPageModel) { if (dataReader.Read()) { response.ResultCount = dataReader.GetInt32(0); dataReader.NextResult(); } } while (dataReader.Read()) { list.Add(ProjectDetailDTO.ReaderBind(dataReader)); } response.ResultList = list; } } catch (Exception ex) { WebLogAgent.Write(string.Format("[SQLText:{0},{1}Messages:\r\n{2}]", strSql.ToString(), base.FormatParameters(dbCommand.Parameters), ex.Message)); } } return(response); }
public int Report(int type, int targetID, int reason, string additionalInfo, int systemID, int appSource, int reporterID, string reporterEmail, long timeStamp, string sign) { try { ISystemRepository systemRepository = ObjectFactory.GetInstance <ISystemRepository>(); SystemEntity systemEntity = systemRepository.Get(systemID); string md5Key = systemEntity.MD5Key; // "MFBUY#!982015" if (additionalInfo == null) { additionalInfo = ""; } if (reporterEmail == null) { reporterEmail = ""; } string seed = "" + type + targetID + reason + additionalInfo + systemID + appSource + reporterID + reporterEmail + timeStamp; string localSign = UtilFactory.GetEncryptProvider(EncryptType.MD5).Encrypt(seed + md5Key); localSign = localSign.Replace("-", ""); //Log seed and Local Sign WebLogAgent.Write(string.Format("[Complaint Seed: {0},\r\nLocalSign: {1}\r\nSign:{2}]", seed, localSign, sign)); if (localSign == sign.ToUpper()) { //Insert to dababas ComplaintEntity complaintEntity = new ComplaintEntity(); complaintEntity.Type = type; complaintEntity.TargetID = targetID; complaintEntity.Reason = reason; complaintEntity.AdditionalInfo = additionalInfo; complaintEntity.SystemID = systemID; complaintEntity.AppSrc = appSource; complaintEntity.ReporterID = reporterID; complaintEntity.ReporterEmail = reporterEmail; RealSystemDateTime time = new RealSystemDateTime(); complaintEntity.CreatedOn = time.Now; complaintEntity.Status = 1; ComplaintApplication complaintApp = new ComplaintApplication(); int newComID = complaintApp.AddComplaint(complaintEntity); try { //Send email IEmailSender sender = ObjectFactory.GetInstance <IEmailSender>(); string emailTitle = string.Format("Complaint Received from {0} ", systemEntity.SystemName); string emailContent = "Please check this URL: \r\n " + Config.AppDomain + "/OA/Complaints/ComplaintReview.aspx?ComplaintID=" + newComID + "\r\n\r\n"; sender.SendMail(Config.ComplainNotifyList, Config.DefaultSendEmail, emailTitle, emailContent); } catch (Exception ex) { WebLogAgent.Write(string.Format("[Email Sending Exception]: {0}", ex.Message)); } return(1); //Accepted, Successed } return(2); //Invalid } catch (Exception ex) { //log this excption WebLogAgent.Write(string.Format("[Exception]: {0}", ex.Message)); return(3);//System Error } }
public void ProcessRequest(HttpContext context) { context.Response.ContentType = "text/plain"; try { if (IdentityContext.UserID <= 0) { return; } String statusValue = context.Request["statusValue"]; int tid = Convert.ToInt32(context.Request["tid"]); TicketsEntity ticketEntity = new TicketsEntity(); ticketEntity = ticketAPP.GetTickets(tid); TicketsState originalStatus = ticketEntity.Status; bool Update = true; bool isCompleteMsgInfo = false; if (statusValue == "pReview") { // if (HasDevOrQaUnderTicket(ticketEntity)) { ticketEntity.Status = TicketsState.PM_Reviewed; } else { context.Response.Write("696"); //context.Response.Write("Please assign user before you change status."); return; } } else if (statusValue == "pmReviewMaintenanceValidate") { ProjectApplication projectApplication = new ProjectApplication(); ProjectsEntity projectsEntity = projectApplication.Get(ticketEntity.ProjectID); if ((projectsEntity.MainPlanOption == UserMaintenancePlanOption.NO || projectsEntity.MainPlanOption == UserMaintenancePlanOption.NEEDAPPROVAL || projectsEntity.MainPlanOption == UserMaintenancePlanOption.ALLOWME) && ticketEntity.IsEstimates == false) { context.Response.Write("-1"); } else { context.Response.Write("1"); } return; } else if (statusValue == "approve") { isCompleteMsgInfo = true; ticketEntity.Status = TicketsState.Completed; } else if (statusValue == "deny") { ticketEntity.Status = TicketsState.Not_Approved; } else if (statusValue == "estApp") { ticketEntity.Status = TicketsState.Estimation_Approved; } else if (statusValue == "estDeny") { ticketEntity.Status = TicketsState.Estimation_Fail; } else if (statusValue == "notBug") { ticketEntity.ConvertDelete = CovertDeleteState.NotABug; } else if (statusValue == "isBug") { ticketEntity.ConvertDelete = CovertDeleteState.Normal; notSendEmail = true; } else if (statusValue == "toEs") { ticketEntity.IsEstimates = true; ChangeToEs = false; } else if (statusValue == "toNotEs") { ticketEntity.IsEstimates = false; } bool IsPass = true; #region //validate if (statusArray.Contains(statusValue)) { if (!BaseValidate(originalStatus, ticketEntity.Status)) { IsPass = false; } } else { if (!NostatusArray.Contains(statusValue)) { if (BaseValidate(originalStatus, (TicketsState)Enum.Parse(typeof(TicketsState), statusValue))) { ticketEntity.Status = (TicketsState)Enum.Parse(typeof(TicketsState), statusValue); } else { IsPass = false; } } } if (!IsPass) { context.Response.Write("same"); return; } #endregion ticketEntity.ModifiedOn = DateTime.Now; ticketEntity.ModifiedBy = IdentityContext.UserID; ticketEntity.PublishDate = DateTime.Now.Date; Update = ticketAPP.UpdateTickets(ticketEntity); #region send email if (!notSendEmail) { if (ChangeToEs) { ticketStatusMgr.SendEmailToSalerWithStatusToEs(ticketEntity); } else { if (statusValue == "notBug") { ticketStatusMgr.SendEmailToAllUserUnderProjectWithNotABug(ticketEntity); } else if (ticketEntity.Status == TicketsState.Ready_For_Review) { ticketStatusMgr.SendEmailtoClientForVerify(ticketEntity); } else if (ticketEntity.Status == TicketsState.Not_Approved) { ticketStatusMgr.SendEmailWithClientNotApp(ticketEntity); } else if (ticketEntity.Status == TicketsState.Waiting_For_Estimation) { ticketStatusMgr.SendEmailToAssignedUserTs(ticketEntity); } else if (ticketEntity.Status == TicketsState.Waiting_Sales_Confirm) { ticketStatusMgr.SendEmailToSalerWithStatusToEs(ticketEntity); } else if (ticketEntity.Status == TicketsState.Tested_Fail_On_Local || ticketEntity.Status == TicketsState.Testing_On_Local || ticketEntity.Status == TicketsState.Tested_Success_On_Local || ticketEntity.Status == TicketsState.Tested_Fail_On_Client || ticketEntity.Status == TicketsState.Testing_On_Client || ticketEntity.Status == TicketsState.Tested_Success_On_Client || ticketEntity.Status == TicketsState.Cancelled) { ticketStatusMgr.SendEmailToUserWithSpecStatus(ticketEntity); if (ticketEntity.Status == TicketsState.Tested_Success_On_Client) { ticketStatusMgr.SendEmailToUserWithTestRSuccToPm(ticketEntity); } } else { if (ticketEntity.Status != TicketsState.Developing) { ticketStatusMgr.SendEmailToQaAndDevWhenStatusChanged(ticketEntity); } } } } #endregion if (Update) { if (statusValue == "notBug") { context.Response.Write("Question sent, please wait for PM to verify."); } else { if (isCompleteMsgInfo) { context.Response.Write("The ticket has been approved."); } else { context.Response.Write("The ticket’s status has been updated."); } } } else { context.Response.Write("Update ticket’s status fail."); } } catch (Exception ex) { context.Response.Write("para error!" + ex.Message); WebLogAgent.Write(string.Format("Error Ashx:DoUpdateTicketStatus.ashx Messages:\r\n{0}", ex)); return; } }
/// <summary> /// Update a record /// </summary> public bool Update(CompanysEntity model) { StringBuilder strSql = new StringBuilder(); strSql.Append("update Companys set "); strSql.Append("CompanyName=@CompanyName,"); strSql.Append("Phone=@Phone,"); strSql.Append("Fax=@Fax,"); strSql.Append("Website=@Website,"); strSql.Append("AssignedSystemUrl=@AssignedSystemUrl,"); strSql.Append("Address1=@Address1,"); strSql.Append("Address2=@Address2,"); strSql.Append("City=@City,"); strSql.Append("State=@State,"); strSql.Append("Logo=@Logo,"); strSql.Append("Status=@Status,"); strSql.Append("CreatedOn=@CreatedOn,"); strSql.Append("CreatedBy=@CreatedBy,"); strSql.Append("CreateUserName=@CreateUserName,"); strSql.Append("ModifiedOn=@ModifiedOn,"); strSql.Append("ModifiedBy=@ModifiedBy"); strSql.Append(" where ComID=@ComID "); Database db = DatabaseFactory.CreateDatabase(); using (DbCommand dbCommand = db.GetSqlStringCommand(strSql.ToString())) { try { db.AddInParameter(dbCommand, "ComID", DbType.Int32, model.ComID); db.AddInParameter(dbCommand, "CompanyName", DbType.String, model.CompanyName); db.AddInParameter(dbCommand, "Phone", DbType.String, model.Phone); db.AddInParameter(dbCommand, "Fax", DbType.String, model.Fax); db.AddInParameter(dbCommand, "Website", DbType.String, model.Website); db.AddInParameter(dbCommand, "AssignedSystemUrl", DbType.String, model.AssignedSystemUrl); db.AddInParameter(dbCommand, "Address1", DbType.String, model.Address1); db.AddInParameter(dbCommand, "Address2", DbType.String, model.Address2); db.AddInParameter(dbCommand, "City", DbType.String, model.City); db.AddInParameter(dbCommand, "State", DbType.String, model.State); db.AddInParameter(dbCommand, "Logo", DbType.String, model.Logo); db.AddInParameter(dbCommand, "Status", DbType.String, model.Status); db.AddInParameter(dbCommand, "CreatedOn", DbType.DateTime, model.CreatedOn); db.AddInParameter(dbCommand, "CreatedBy", DbType.Int32, model.CreatedBy); db.AddInParameter(dbCommand, "CreateUserName", DbType.String, model.CreateUserName); db.AddInParameter(dbCommand, "ModifiedOn", DbType.DateTime, model.ModifiedOn); db.AddInParameter(dbCommand, "ModifiedBy", DbType.Int32, model.ModifiedBy); int rows = db.ExecuteNonQuery(dbCommand); if (rows > 0) { return(true); } else { return(false); } } catch (Exception ex) { WebLogAgent.Write(string.Format("[SQLText:{0},{1}Messages:\r\n{2}]", strSql.ToString(), base.FormatParameters(dbCommand.Parameters), ex.Message)); return(false); } } }
public SearchProposalTrackerRequest GetProposalTrackers(string keyword, int projectId, int status, int companyId, int payment, int userId, DateTime?beginTime, DateTime?endTime, string order, string dir, int pageCount, int pageIndex) { string strOrderby = string.Format(" {0} {1} ", order, dir); int start = pageIndex * pageCount + 1 - pageCount; int end = pageIndex * pageCount; StringBuilder strSql = new StringBuilder(); strSql.Append(@"SELECT count(1)"); strSql.Append(@" from (SELECT w.RequestNo,w.Title,w.ProjectID,w.status,w.payment,p.CompanyID,"); strSql.Append(@"(SELECT STUFF(( SELECT ',' + PP.InvoiceNo FROM Invoices AS PP WHERE PP.Proposalid = w.ProposalTrackerID AND PP.InvoiceNo != '' FOR XML PATH('') ), 1, 1, '')) AS InvoiceNo"); strSql.Append(@" FROM (ProposalTracker w "); strSql.Append(@"left join Projects p on w.ProjectID= p.ProjectID LEFT JOIN dbo.Companys C ON C.ComID = p.CompanyID))NT"); strSql.Append(@" where 1=1 "); if (projectId > 0) { strSql.Append(@" AND NT.projectId=@projectId "); } if (status > 0) { strSql.Append(@" AND NT.status=@status "); } if (companyId > 0) { strSql.Append(@" AND NT.CompanyID=@companyId "); } if (payment > 0) { strSql.Append(@" AND NT.payment=@payment "); } if (!string.IsNullOrEmpty(keyword)) { strSql.Append(@" AND (NT.requestNo LIKE @keyword OR NT.Title LIKE @keyword OR NT.InvoiceNo LIKE @keyword)"); } if (userId > 0) { strSql.Append(@" AND NT.projectid in (select projectid from projectusers where userid = @userId); "); } strSql.Append(@"select * from "); strSql.Append(@"(select *,"); strSql.Append(string.Format(@"ROW_NUMBER() OVER (order by {0} )rownumber ", strOrderby)); strSql.Append(@"FROM(SELECT "); strSql.Append(@"w.ProposalTrackerID ,"); strSql.Append(@"w.ProjectID ,"); strSql.Append(@"p.CompanyID ,"); strSql.Append(@"c.CompanyName ,"); strSql.Append(@"w.Status ,"); strSql.Append(@"w.Title ,"); strSql.Append(@"w.Description ,"); strSql.Append(@"w.ProposalSentTo ,"); strSql.Append(@"w.ProposalSentOn ,"); strSql.Append(@"w.PONo ,"); strSql.Append(@"w.ApprovedBy ,"); strSql.Append(@"w.ApprovedOn ,"); strSql.Append(@"w.InvoiceSentOn ,"); strSql.Append(@"w.WorkScope ,"); strSql.Append(@"w.WorkScopeDisplayName ,"); strSql.Append(@"w.RequestNo ,"); strSql.Append(@"w.Payment ,"); strSql.Append(@"w.DueDate ,"); strSql.Append(@"w.CreatedOn ,"); strSql.Append(@"w.CreatedBy ,"); strSql.Append(@"w.ModifyOn ,"); strSql.Append(@"w.ModifyBy ,"); strSql.Append(@"w.Reminded ,"); strSql.Append(@"w.RemindTime ,"); strSql.Append(@"w.PoTotalLessThenProposalTotal ,"); strSql.Append(@"p.Title as ProjectName,"); strSql.Append(@"(SELECT STUFF(( SELECT ',' + PP.InvoiceNo FROM Invoices AS PP "); strSql.Append(@" WHERE PP.Proposalid = w.ProposalTrackerID AND PP.InvoiceNo != '' FOR XML PATH('') ), 1, 1, '')) AS InvoiceNo "); strSql.Append(@" FROM (ProposalTracker w "); strSql.Append(@"left join Projects p on w.ProjectID= p.ProjectID"); strSql.Append(@" LEFT JOIN dbo.Companys C ON C.ComID = p.CompanyID))NK "); strSql.Append(@" where 1=1 "); if (!string.IsNullOrEmpty(keyword)) { strSql.Append(@" AND (NK.InvoiceNo LIKE @keyword or NK.requestNo LIKE @keyword OR NK.Title LIKE @keyword )"); } if (projectId > 0) { strSql.Append(@" AND NK.projectId=@projectId "); } if (status > 0) { strSql.Append(@" AND NK.status=@status "); } if (companyId > 0) { strSql.Append(@" AND NK.CompanyID=@companyId "); } if (payment > 0) { strSql.Append(@" AND NK.payment=@payment "); } if (userId > 0) { strSql.Append(@" AND w.projectid in (select projectid from projectusers where userid = @userId) "); } if (beginTime != null) { strSql.Append(@" AND w.ApprovedOn>=@approvedOnBegin "); } if (endTime != null) { strSql.Append(@" AND w.ApprovedOn<=@approvedOnEnd "); } strSql.Append(@" )NEWTT where rownumber between @start and @end "); List <ProposalTrackerEntity> list; SearchProposalTrackerRequest response = new SearchProposalTrackerRequest(); Database db = DatabaseFactory.CreateDatabase(); using (DbCommand dbCommand = db.GetSqlStringCommand(strSql.ToString())) { try { db.AddInParameter(dbCommand, "keyword", DbType.String, string.Format("%{0}%", keyword.FilterSqlString())); db.AddInParameter(dbCommand, "Status", DbType.String, status); db.AddInParameter(dbCommand, "companyId", DbType.Int32, companyId); db.AddInParameter(dbCommand, "projectId", DbType.Int32, projectId); db.AddInParameter(dbCommand, "payment", DbType.Int32, payment); db.AddInParameter(dbCommand, "userId", DbType.Int32, userId); if (beginTime != null) { db.AddInParameter(dbCommand, "approvedOnBegin", DbType.DateTime, beginTime); } if (endTime != null) { db.AddInParameter(dbCommand, "approvedOnEnd", DbType.DateTime, endTime); } db.AddInParameter(dbCommand, "order", DbType.String, order); db.AddInParameter(dbCommand, "dir", DbType.String, dir); db.AddInParameter(dbCommand, "start", DbType.Int32, start); db.AddInParameter(dbCommand, "end", DbType.Int32, end); using (IDataReader dataReader = db.ExecuteReader(dbCommand)) { list = new List <ProposalTrackerEntity>(); if (dataReader.Read()) { response.ResultCount = dataReader.GetInt32(0); dataReader.NextResult(); } while (dataReader.Read()) { list.Add(ProposalTrackerEntity.ReaderBind(dataReader)); } response.ResultList = list; } } catch (Exception ex) { WebLogAgent.Write(string.Format("[SQLText:{0},{1}Messages:\r\n{2}]" , strSql.ToString(), base.FormatParameters(dbCommand.Parameters), ex.Message)); return(null); } } return(response); }
public void ProcessRequest(HttpContext context) { context.Response.ContentType = "text/plain"; try { if (IdentityContext.UserID <= 0) { return; } #region get value int tid = Convert.ToInt32(context.Request["tid"]); int pId = Convert.ToInt32(context.Request["pId"]); String tType = context.Request["tType"]; String ckbEn = context.Request["ckbEn"]; String pty = context.Request["pty"]; String title = context.Request["title"].NoHTML(); String url = context.Server.UrlEncode(context.Request["url"]); String descr = context.Request["descr"].NoHTML(); String imageList = context.Request["imageList"]; String imageSizeList = context.Request["imageSizeList"]; String StartDate = context.Request["StartDate"]; String DeliveryDate = context.Request["DeliveryDate"]; String satus = context.Request["satus"]; String IsSunnet = context.Request["isSunnet"]; #endregion bool update = true; TicketsEntity ticketEntity = TicketApp.GetTickets(tid);//get original model #region set original value { originalDesc = ticketEntity.FullDescription; originalState = ticketEntity.Status.ToString(); originalTitle = ticketEntity.Title; } #endregion #region set satus value switch (satus) { case "save": ticketEntity.Status = ticketEntity.Status; break; case "cancle": ticketEntity.Status = TicketsState.Cancelled; break; case "submit": IsSend = true; ticketEntity.Status = TicketsState.Submitted; break; } #endregion #region ticket ticketEntity.ProjectID = pId; ticketEntity.URL = url; ticketEntity.FullDescription = descr; ticketEntity.Title = title.NoHTML(); ticketEntity.TicketType = tType == "0" ? TicketsType.Bug : TicketsType.Request; ticketEntity.IsEstimates = ckbEn == "checked" ? true : false; ticketEntity.Priority = (PriorityState)Convert.ToInt32(pty); ticketEntity.ModifiedBy = IdentityContext.UserID; if (IsSunnet == "true") { ticketEntity.StartDate = !string.IsNullOrEmpty(StartDate.ToString()) ? DateTime.Parse(StartDate).Date : UtilFactory.Helpers.CommonHelper.GetDefaultMinDate(); ticketEntity.DeliveryDate = !string.IsNullOrEmpty(DeliveryDate.ToString()) ? DateTime.Parse(DeliveryDate).Date : UtilFactory.Helpers.CommonHelper.GetDefaultMinDate(); } else { ticketEntity.StartDate = UtilFactory.Helpers.CommonHelper.GetDefaultMinDate(); ticketEntity.DeliveryDate = UtilFactory.Helpers.CommonHelper.GetDefaultMinDate(); } #region record,when title or descr or status changed if (originalTitle != title || originalDesc != descr) { ticketEntity.ModifiedOn = DateTime.Now; } if ((TicketsState)Enum.Parse(typeof(TicketsState), originalState) != ticketEntity.Status) { ticketEntity.PublishDate = DateTime.Now; } #endregion #region record history, when descr changed if (originalDesc != descr && ticketEntity.Status != TicketsState.Draft) { TicketHistorysEntity history = new TicketHistorysEntity(); history.TicketID = ticketEntity.TicketID; history.ModifiedBy = IdentityContext.UserID; history.ModifiedOn = DateTime.Now; history.Description = originalDesc; TicketApp.AddTicketHistory(history); } #endregion update = TicketApp.UpdateTickets(ticketEntity); #region send email if (IsSend) { TicketStatusManagerApplication EX = new TicketStatusManagerApplication(); EX.SendEmailToQaAndDevWhenStatusChanged(ticketEntity); } #endregion #endregion #region file FilesEntity fileEntity = new FilesEntity(); FolderName = ticketEntity.ProjectID.ToString(); string sNewFileName = ""; tempPath = System.Configuration.ConfigurationManager.AppSettings["FolderPath"]; string[] listStringName = imageList.Split(','); string[] listStringSize = imageSizeList.Split(','); foreach (string Name in listStringName) { if (Name.Length == 0) { break; } string sExtension = Path.GetExtension(Name).Replace(".", "").Trim(); foreach (string Size in listStringSize) { sNewFileName = FolderName + Name; fileEntity.ContentType = "." + sExtension.ToLower(); fileEntity.CreatedBy = IdentityContext.UserID;; fileEntity.FilePath = tempPath.Substring(2) + FolderName + @"/" + sNewFileName; fileEntity.FileSize = Convert.ToDecimal(Size.ToLower().Replace("kb", "")); fileEntity.FileTitle = Name.Substring(0, Name.LastIndexOf('.')); fileEntity.IsPublic = true; fileEntity.TicketId = tid; //ticketID fileEntity.ProjectId = pId; //ticketID fileEntity.SourceType = (int)FileSourceType.Ticket; fileEntity.ThumbPath = context.Server.MapPath(tempPath) + FolderName + sNewFileName;; // fileEntity.CreatedOn = DateTime.Now; int responseFile = fileApp.AddFile(fileEntity); if (responseFile <= 0) { HasFileMsG = false; stringErrorMsg.Add(fileEntity.FileTitle); } break; } } #endregion #region response if (update) { if (HasFileMsG) { context.Response.Write("Update Ticket Success!"); } else { string error = ""; foreach (string item in stringErrorMsg) { error += item + "File Upload Failed!"; } context.Response.Write(error); } } else { context.Response.Write("Update Fail!"); } #endregion } catch (Exception ex) { context.Response.Write("para error!"); WebLogAgent.Write(string.Format("Error Ashx:DoEditTicketHandler.ashx Messages:\r\n{0}", ex)); return; } }
public bool Update(ProposalTrackerEntity model) { StringBuilder strSql = new StringBuilder(); strSql.Append("update ProposalTracker set "); strSql.Append("ProjectID=@ProjectID,"); strSql.Append("InvoiceNo=@InvoiceNo,"); strSql.Append("Payment=@Payment,"); strSql.Append("Status=@Status,"); strSql.Append("DueDate=@DueDate,"); strSql.Append("Title=@Title,"); strSql.Append("Description=@Description,"); strSql.Append("WorkScope=@WorkScope,"); strSql.Append("WorkScopeDisplayName=@WorkScopeDisplayName,"); strSql.Append("ModifyOn=@ModifyOn,"); strSql.Append("ModifyBy=@ModifyBy,"); strSql.Append("ProposalSentTo=@ProposalSentTo,"); strSql.Append("ProposalSentOn=@ProposalSentOn,"); strSql.Append("PONo=@PONo,"); strSql.Append("ApprovedBy=@ApprovedBy,"); strSql.Append("ApprovedOn=@ApprovedOn,"); strSql.Append("InvoiceSentOn=@InvoiceSentOn,"); strSql.Append("Reminded=@Reminded,"); strSql.Append("PoTotalLessThenProposalTotal=@PoTotalLessThenProposalTotal,"); strSql.Append("RemindTime=@RemindTime "); strSql.Append(" where ProposalTrackerID=@ProposalTrackerID "); Database db = DatabaseFactory.CreateDatabase(); using (DbCommand dbCommand = db.GetSqlStringCommand(strSql.ToString())) { try { db.AddInParameter(dbCommand, "ProposalTrackerID", DbType.Int32, model.ProposalTrackerID); db.AddInParameter(dbCommand, "ProjectID", DbType.Int32, model.ProjectID); db.AddInParameter(dbCommand, "InvoiceNo", DbType.String, model.InvoiceNo); db.AddInParameter(dbCommand, "Payment", DbType.Int32, model.Payment); db.AddInParameter(dbCommand, "Status", DbType.Int32, model.Status); db.AddInParameter(dbCommand, "DueDate", DbType.DateTime, model.DueDate); db.AddInParameter(dbCommand, "Title", DbType.String, model.Title); db.AddInParameter(dbCommand, "Description", DbType.String, model.Description); db.AddInParameter(dbCommand, "WorkScope", DbType.String, model.WorkScope); db.AddInParameter(dbCommand, "WorkScopeDisplayName", DbType.String, model.WorkScopeDisplayName); db.AddInParameter(dbCommand, "ModifyOn", DbType.DateTime, model.ModifyOn); db.AddInParameter(dbCommand, "ModifyBy", DbType.Int32, model.ModifyBy); db.AddInParameter(dbCommand, "ProposalSentTo", DbType.String, model.ProposalSentTo); db.AddInParameter(dbCommand, "ProposalSentOn", DbType.DateTime, model.ProposalSentOn); db.AddInParameter(dbCommand, "PONo", DbType.String, model.PONo); db.AddInParameter(dbCommand, "ApprovedBy", DbType.String, model.ApprovedBy); db.AddInParameter(dbCommand, "ApprovedOn", DbType.DateTime, model.ApprovedOn); db.AddInParameter(dbCommand, "InvoiceSentOn", DbType.DateTime, model.InvoiceSentOn); db.AddInParameter(dbCommand, "Reminded", DbType.Int32, model.Reminded); db.AddInParameter(dbCommand, "RemindTime", DbType.DateTime, model.RemindTime); db.AddInParameter(dbCommand, "PoTotalLessThenProposalTotal", DbType.Boolean, model.PoTotalLessThenProposalTotal); int rows = db.ExecuteNonQuery(dbCommand); if (rows > 0) { return(true); } else { return(false); } } catch (Exception ex) { WebLogAgent.Write(string.Format("[SQLText:{0},{1}Messages:\r\n{2}]", strSql.ToString(), base.FormatParameters(dbCommand.Parameters), ex.Message)); return(false); } } }
public DataTable QueryReportDetailsByProject(int projectID, int userID, DateTime startDate, DateTime endDate, string orderBy, string orderDirectioin) { StringBuilder strSql = new StringBuilder(); strSql.Append(@"Select p.Title,tk.ProjectID,tk.CreatedBy,tk.Name,tk.AllDay,u.UserID,u.FirstName,u.LastName,u.Office,u.PTOHoursOfYear,tk.FromDay,tk.FromTime,tk.FromTimeType,tk.ToDay,tk.ToTime,tk.ToTimeType from dbo.Events tk join Users u on tk.CreatedBy=u.UserID join Projects p on tk.ProjectID=p.ProjectID"); strSql.Append(@" Where 1=1 "); if (projectID > 0) { strSql.Append(" AND tk.ProjectID=@ProjectID "); } if (userID > 0) { strSql.Append(" AND tk.CreatedBy=@UserID "); } if (startDate != null && startDate > DateTime.MinValue) { strSql.Append(" AND tk.FromDay >=@StartDate "); } if (endDate != null && endDate > DateTime.MinValue) { strSql.Append(" AND tk.ToDay <=@EndDate "); } string orderby = string.Empty; if (orderBy.ToLower() == "All".ToLower()) { orderby = " p.Title,u.FirstName asc,u.LastName asc "; } else { if (orderBy.ToLower() == "ProjectTitle".ToLower()) { orderBy = "p.Title"; } orderby = string.Format(" {0} {1} ", orderBy, orderDirectioin); } strSql.AppendFormat(@" Order by {0}", orderby); Database db = DatabaseFactory.CreateDatabase(); using (DbCommand dbCommand = db.GetSqlStringCommand(strSql.ToString())) { try { db.AddInParameter(dbCommand, "ProjectID", DbType.Int32, projectID); db.AddInParameter(dbCommand, "UserID", DbType.Int32, userID); db.AddInParameter(dbCommand, "StartDate", DbType.DateTime, startDate); db.AddInParameter(dbCommand, "EndDate", DbType.DateTime, endDate); DataTable dt = new DataTable(); dt = db.ExecuteDataSet(dbCommand).Tables[0]; return(dt); } catch (Exception ex) { WebLogAgent.Write(string.Format("[SQLText:{0},{1}Messages:\r\n{2}]", strSql.ToString(), base.FormatParameters(dbCommand.Parameters), ex.Message)); return(null); } } }
public string GetComItem(string connStr, string spName, string type, int id) { Regex regex = new Regex("(?<=database=).*(?=;uid)"); string dbName = regex.Match(connStr).Value; //Add a connectionString if not existed Configuration config = WebConfigurationManager.OpenWebConfiguration("~"); ConnectionStringsSection csSection = config.ConnectionStrings; if (csSection.ConnectionStrings[dbName] == null) { ConnectionStringSettings connection = new ConnectionStringSettings(dbName, connStr, "System.Data.SqlClient"); csSection.ConnectionStrings.Add(connection); config.Save(ConfigurationSaveMode.Modified); ConfigurationManager.RefreshSection("connectionStrings"); } ComplaintItem com = new ComplaintItem(); Database db = new SqlDatabase(connStr); using (DbCommand dbCommand = db.GetStoredProcCommand(spName)) { try { db.AddInParameter(dbCommand, "Action", DbType.String, "View"); db.AddInParameter(dbCommand, "Type", DbType.String, type); db.AddInParameter(dbCommand, "ID", DbType.Int32, id); IDataReader reader = db.ExecuteReader(dbCommand); if (reader.Read()) { switch (type) { case "Photo": case "Video": com.Path = (string)reader["Path"]; break; case "User": com.UserName = (string)reader["UserName"]; com.UserEmail = (string)reader["UserEmail"]; break; case "Group": com.UserName = (string)reader["UserName"]; com.GroupName = (string)reader["GroupName"]; break; case "Post": com.UserName = (string)reader["UserName"]; com.Message = (string)reader["Message"]; break; } } JavaScriptSerializer jss = new JavaScriptSerializer(); return(jss.Serialize(com)); } catch (Exception ex) { WebLogAgent.Write(string.Format("[SQLText:{0},{1}Messages:\r\n{2}]", "GetTimesheetList", base.FormatParameters(dbCommand.Parameters), ex.Message)); return(""); } } }
public List <FileDetailDto> GetFileList(SearchFilesRequest request) { int start = request.CurrentPage * request.PageCount + 1 - request.PageCount; int end = request.CurrentPage * request.PageCount; string selPageModel = string.Format(@"SELECT * FROM( Select ROW_NUMBER() OVER( ORDER BY {0} {1}) AS INDEX_ID ", request.OrderByExpression, request.OrderByDirection) + @",SearchResult.* From ({0}) as SearchResult ) as New_TB Where INDEX_ID between @Start and @End "; // string selPageModel = @"SELECT * FROM( // Select ROW_NUMBER() OVER( ORDER BY FileTitle ASC) AS INDEX_ID,SearchResult.* From ({0}) as SearchResult // // // ) as New_TB // Where INDEX_ID between @Start and @End // "; string selCount = @"SELECT Count(1) FROM ({0}) as SearchResult ;"; StringBuilder strNoPageModal = new StringBuilder(); //strNoPageModal.Append(@"SELECT f.*,c.CompanyName,p.Title as ProjectTitle,t.Title as TicketTitle ,t.TicketCode + cast(t.TicketID as varchar) as TicketCode,fb.Title as FeedBackTitle // ,u.FirstName,u.LastName // FROM [Files] f left join Companys c on c.ComID=f.CompanyID // left join Projects p on f.ProjectId=p.ProjectID // left join Tickets t on f.TicketId=t.TicketID // left join WorkRequest wr on f.WorkRequestId=wr.WorkRequestId // left join FeedBacks fb on f.FeedbackId=fb.FeedBackID // left join Users u on f.CreatedBy=u.UserID"); strNoPageModal.Append(@"SELECT f.*,c.CompanyName,p.Title as ProjectTitle,t.Title as TicketTitle ,t.TicketCode + cast(t.TicketID as varchar) as TicketCode,fb.Title as FeedBackTitle ,u.FirstName,u.LastName FROM [Files] f left join Companys c on c.ComID=f.CompanyID left join Projects p on f.ProjectId=p.ProjectID left join Tickets t on f.TicketId=t.TicketID left join FeedBacks fb on f.FeedbackId=fb.FeedBackID left join Users u on f.CreatedBy=u.UserID"); strNoPageModal.Append(@" Where 1=1 AND f.Isdelete=0 AND ( f.TicketID=0 or t.Status>0 ) "); if (request.IsPublic) { strNoPageModal.Append(@" And f.IsPublic=@IsPublic "); } switch (request.SearchType) { case SearchFileType.Company: { if (request.CompanyID > 0) { strNoPageModal.Append(" And f.CompanyID=@ComapnyID"); } else { strNoPageModal.Append(" And f.CompanyID>1 "); } strNoPageModal.AppendFormat(" And f.SourceType={0} ", ((int)FileSourceType.Company).ToString()); if (!string.IsNullOrEmpty(request.Keyword) && request.Keyword.Trim() != "") { strNoPageModal.Append(" AND f.FileTitle like @Keyword "); } } break; case SearchFileType.Project: { if (request.CompanyID > 0) { strNoPageModal.Append(" And f.CompanyID=@ComapnyID"); } if (request.ProjectID > 0) { strNoPageModal.Append(" and f.ProjectId=@ProjectID "); } strNoPageModal.AppendFormat(" And f.SourceType={0} ", ((int)FileSourceType.Project).ToString()); if (!string.IsNullOrEmpty(request.Keyword) && request.Keyword.Trim() != "") { strNoPageModal.Append(" AND f.FileTitle like @Keyword "); } } break; case SearchFileType.WorkRequest: { if (request.CompanyID > 0) { strNoPageModal.Append(" And f.CompanyID=@ComapnyID"); } if (request.ProjectID > 0) { strNoPageModal.Append(" and f.ProjectId=@ProjectID "); } strNoPageModal.AppendFormat(" And f.SourceType in ({0},{1}) ", ((int)FileSourceType.WorkRequest).ToString(), ((int)FileSourceType.WorkRequestScope).ToString()); //if (request.UserID != 0) //{ // strNoPageModal.AppendFormat(" AND wr.ProjectID IN (select ProjectID from ProjectUsers where UserId={0})", request.UserID); //} if (!string.IsNullOrEmpty(request.Keyword) && request.Keyword.Trim() != "") { strNoPageModal.Append(" AND f.Tags like @Keyword "); } } break; case SearchFileType.TicketAndFeedback: { if (request.CompanyID > 0) { strNoPageModal.Append(" And f.CompanyID=@ComapnyID"); } if (request.ProjectID > 0) { strNoPageModal.Append(" and f.ProjectId=@ProjectID "); } strNoPageModal.AppendFormat(" And (f.SourceType={0} or f.SourceType={1}) ", ((int)FileSourceType.Ticket).ToString(), ((int)FileSourceType.FeedBack).ToString()); strNoPageModal.AppendFormat(" AND f.ProjectId in (select ProjectId from ProjectUsers h where h.UserID= {0} )", request.UserID); if (request.KeywordType == SearchKeywordType.TicketCode) { if (!string.IsNullOrEmpty(request.Keyword) && request.Keyword.Trim() != "") { int ticketcode = 0; int.TryParse(request.Keyword, out ticketcode); if (ticketcode > 0) { strNoPageModal.AppendFormat(@" AND t.TicketID = {0} ", ticketcode); } } } else if (request.KeywordType == SearchKeywordType.TicketTitle) { if (!string.IsNullOrEmpty(request.Keyword) && request.Keyword.Trim() != "") { strNoPageModal.Append(@" AND t.Title like @Keyword "); } } else if (request.KeywordType == SearchKeywordType.FileName) { if (!string.IsNullOrEmpty(request.Keyword) && request.Keyword.Trim() != "") { strNoPageModal.Append(" AND f.FileTitle like @Keyword "); } } else if (request.KeywordType == SearchKeywordType.All) { if (!string.IsNullOrEmpty(request.Keyword) && request.Keyword.Trim() != "") { strNoPageModal.Append(@" AND ( t.Title like @Keyword or f.FileTitle like @Keyword "); int ticketcode = 0; int.TryParse(request.Keyword, out ticketcode); if (ticketcode > 0) { strNoPageModal.AppendFormat(@" or t.TicketID = {0} ", ticketcode); } strNoPageModal.Append(")"); } } } break; default: break; } StringBuilder strSql = new StringBuilder(); if (request.IsPageModel) { strSql.AppendFormat(selCount, strNoPageModal.ToString()); strSql.AppendFormat(selPageModel, strNoPageModal.ToString()); } else { strSql.Append(strNoPageModal); } Database db = DatabaseFactory.CreateDatabase(); using (DbCommand dbCommand = db.GetSqlStringCommand(strSql.ToString())) { try { db.AddInParameter(dbCommand, "Keyword", DbType.String, string.Format("%{0}%", request.Keyword.FilterSqlString())); db.AddInParameter(dbCommand, "ComapnyID", DbType.Int32, request.CompanyID); db.AddInParameter(dbCommand, "ProjectID", DbType.Int32, request.ProjectID); db.AddInParameter(dbCommand, "IsPublic", DbType.Boolean, request.IsPublic); db.AddInParameter(dbCommand, "Start", DbType.Int32, start); db.AddInParameter(dbCommand, "End", DbType.Int32, end); List <FileDetailDto> list = new List <FileDetailDto>(); using (IDataReader dataReader = db.ExecuteReader(dbCommand)) { if (request.IsPageModel) { if (dataReader.Read()) { request.RecordCount = dataReader.GetInt32(0); } dataReader.NextResult(); } while (dataReader.Read()) { list.Add(FileDetailDto.ReaderBind(dataReader)); } return(list); } } catch (Exception ex) { WebLogAgent.Write(string.Format("[SQLText:GetFileList = {0},{1}Messages:\r\n{2}]", strSql.ToString(), base.FormatParameters(dbCommand.Parameters), ex.Message)); return(null); } } }
public List <ComplaintEntity> SearchComplaints(ComplaintSearchEntity request, out int recordCount) { recordCount = 0; int startID = request.CurrentPage * request.PageCount + 1 - request.PageCount; int endID = request.CurrentPage * request.PageCount; StringBuilder strSql = new StringBuilder(); StringBuilder strSqlBody = new StringBuilder(); StringBuilder strSelAll = new StringBuilder(); StringBuilder strSelCnt = new StringBuilder(); StringBuilder strOrderBy = new StringBuilder(); StringBuilder strFitPage = new StringBuilder(); strSelCnt.Append("SELECT Count(*) FROM "); strSelAll.Append(String.Format("SELECT * from(SELECT *, Row_Number() Over(Order By {0} {1}) RowNum FROM ", request.OrderExpression, request.OrderDirection)); strSqlBody.Append("(SELECT c.*, s.SystemName, '' AS UpdatedByName "); strSqlBody.Append("FROM Complaints c, Systems s "); strSqlBody.Append("WHERE c.SystemID=s.SystemID AND c.UpdatedByID is NULL "); strSqlBody.Append("UNION "); strSqlBody.Append("SELECT c.*, s.SystemName, u.FirstName+' '+u.LastName AS UpdatedByName "); strSqlBody.Append("FROM Complaints c, Systems s, Users u "); strSqlBody.Append("WHERE c.SystemID=s.SystemID AND c.UpdatedByID=u.UserID) combine "); strSqlBody.Append("WHERE combine.ComplaintID>0 "); strFitPage.Append(") bigCombine where RowNum BETWEEN @StartID AND @EndID "); if (request.Type >= 0) { strSqlBody.Append("AND combine.Type=@Type "); } if (request.Reason >= 0) { strSqlBody.Append("AND combine.Reason=@Reason "); } if (request.SystemID >= 0) { strSqlBody.Append("AND combine.SystemID=@SystemID "); } if (request.AppSrc >= 0) { strSqlBody.Append("AND combine.AppSrc=@AppSrc "); } if (request.Status >= 0) { strSqlBody.Append("AND combine.Status=@Status "); } if (request.Keyword.Length > 0) { strSqlBody.Append("AND (combine.AdditionalInfo like @Keywords OR combine.SystemName like @Keywords) "); } if (request.UpdatedByName.Length > 0) { strSqlBody.Append("AND combine.UpdatedByName like @UpdatedByName "); } strSql.Append(strSelCnt); strSql.Append(strSqlBody); strSql.Append(";"); strSql.Append(strSelAll); strSql.Append(strSqlBody); strSql.Append(strFitPage); // Execute List <ComplaintEntity> list = new List <ComplaintEntity>(); Database db = DatabaseFactory.CreateDatabase(); using (DbCommand dbCommand = db.GetSqlStringCommand(strSql.ToString())) { try { db.AddInParameter(dbCommand, "Type", DbType.Int32, request.Type); db.AddInParameter(dbCommand, "Reason", DbType.Int32, request.Reason); db.AddInParameter(dbCommand, "SystemID", DbType.Int32, request.SystemID); db.AddInParameter(dbCommand, "AppSrc", DbType.Int32, request.AppSrc); db.AddInParameter(dbCommand, "Status", DbType.Int32, request.Status); db.AddInParameter(dbCommand, "Keywords", DbType.String, request.Keyword); db.AddInParameter(dbCommand, "UpdatedByName", DbType.String, request.UpdatedByName); db.AddInParameter(dbCommand, "StartID", DbType.Int32, startID); db.AddInParameter(dbCommand, "EndID", DbType.Int32, endID); using (IDataReader dataReader = db.ExecuteReader(dbCommand)) { if (dataReader.Read()) { recordCount = dataReader.GetInt32(0); dataReader.NextResult(); } while (dataReader.Read()) { list.Add(ComplaintEntity.ReaderBind(dataReader)); } } } catch (Exception ex) { WebLogAgent.Write(string.Format("[SQLText:{0},{1}Messages:\r\n{2}]", strSqlBody.ToString(), base.FormatParameters(dbCommand.Parameters), ex.Message)); } return(list); } }
public SearchUserResponse SearchUsers(SearchUsersRequest request) { int start = request.CurrentPage * request.PageCount + 1 - request.PageCount; int end = request.CurrentPage * request.PageCount; string strSelCount = " SELECT COUNT(1) FROM [Users] u "; if (request.OrderExpression.ToLower().Equals("status")) { request.OrderExpression = " u.Status "; } string strOrderby = string.Format(" {0} {1} ", request.OrderExpression, request.OrderDirection); string strSelAttrs = " SELECT u.*,c.CompanyName FROM [Users] u left join Companys c on u.CompanyID=c.ComID "; string strSelAttrsOrderBy = string.Format(@" Order BY {0} ", strOrderby); string strSelPageModel = string.Format(@"SELECT * FROM( SELECT ROW_NUMBER() OVER( Order BY {0}) as INDEX_ID,u.*,c.CompanyName FROM [Users] u left join Companys c on u.CompanyID=c.ComID ", strOrderby); string strWherePageModel = @") NEW_TB WHERE INDEX_ID BETWEEN @Strat AND @End;"; StringBuilder strWhere = new StringBuilder(); strWhere.Append(" WHERE IsDelete=0 "); switch (request.SearchType) { case SearchUsersType.All: if (request.IsSunnet) { strWhere.Append(" AND [UserType] = 'SUNNET'"); } if (request.IsClient) { strWhere.Append(" AND [UserType] = 'CLIENT'"); } //if (!string.IsNullOrEmpty(request.Status) && request.Status.ToUpper() != "ALL") //{ // strWhere.Append(" AND u.Status=@Status "); //} strWhere.Append(" AND u.Status='ACTIVE'"); break; case SearchUsersType.List: strWhere.Append(" AND ( UserName like @Keywords OR FirstName like @Keywords OR LastName like @Keywords )"); if (request.CompanyID != 0) { strWhere.AppendFormat(" AND (CompanyID={0})", request.CompanyID); } if (!string.IsNullOrEmpty(request.Status) && request.Status.ToUpper() != "All".ToUpper()) { strWhere.Append(" AND u.Status=@Status "); } break; case SearchUsersType.Company: strWhere.Append(" AND u.Status='ACTIVE'"); strWhere.Append(" AND u.CompanyID =@CompanyID"); break; case SearchUsersType.CompanyByProject: strWhere.Append(" AND u.Status='ACTIVE'"); strWhere.Append(" AND u.CompanyID IN (SELECT CompanyID FROM Projects WHERE ProjectID = @ProjectID )"); break; case SearchUsersType.Project: strWhere.Append(" AND u.Status='ACTIVE'"); strWhere.Append(" AND u.UserID IN (SELECT UserID FROM ProjectUsers WHERE ProjectID = @ProjectID)"); break; case SearchUsersType.Role: strWhere.Append(" AND u.RoleID =@RoleID "); strWhere.Append(" AND u.Status='ACTIVE'"); break; case SearchUsersType.Ticket: strWhere.Append(" AND u.UserID in ( SELECT [UserID] FROM [TicketUsers] WHERE [TicketID]=@TicketID)"); break; default: break; } StringBuilder strSql = new StringBuilder(); if (request.IsPageModel) { strSql.Append(strSelCount); strSql.Append(strWhere); strSql.Append(";"); strSql.Append(strSelPageModel); strSql.Append(strWhere); strSql.Append(strWherePageModel); } else { strSql.Append(strSelAttrs); strSql.Append(strWhere); strSql.Append(strSelAttrsOrderBy); strSql.Append(";"); } SearchUserResponse response = new SearchUserResponse(); List <UsersEntity> list; Database db = DatabaseFactory.CreateDatabase(); using (DbCommand dbCommand = db.GetSqlStringCommand(strSql.ToString())) { try { db.AddInParameter(dbCommand, "UserID", DbType.Int32, request.UserID); db.AddInParameter(dbCommand, "Keywords", DbType.String, string.Format("%{0}%", request.Keywords.FilterSqlString())); db.AddInParameter(dbCommand, "Status", DbType.String, request.Status); db.AddInParameter(dbCommand, "CompanyID", DbType.Int32, request.CompanyID); db.AddInParameter(dbCommand, "ProjectID", DbType.Int32, request.ProjectID); db.AddInParameter(dbCommand, "RoleID", DbType.Int32, (int)request.Role); db.AddInParameter(dbCommand, "TicketID", DbType.Int32, request.TicketID); db.AddInParameter(dbCommand, "Strat", DbType.Int32, start); db.AddInParameter(dbCommand, "End", DbType.Int32, end); using (IDataReader dataReader = db.ExecuteReader(dbCommand)) { list = new List <UsersEntity>(); if (request.IsPageModel) { if (dataReader.Read()) { response.ResultCount = dataReader.GetInt32(0); dataReader.NextResult(); } } while (dataReader.Read()) { list.Add(UsersEntity.ReaderBind(dataReader)); } response.ResultList = list; } } catch (Exception ex) { WebLogAgent.Write(string.Format("[SQLText:{0},{1}Messages:\r\n{2}]", strSql.ToString(), base.FormatParameters(dbCommand.Parameters), ex.Message)); } } return(response); }
public string AddFeedback(int projectID, int ticketID, string content, bool isPublic, bool isWaitClient, bool isWaitSunnetFeedback, string file, string clientsIds, HttpServerUtility Server) { try { ProjectsEntity ProjectEntity = projectApp.Get(projectID); FeedBacksEntity feedbacksEntity = GetFeedbacksEntity(ticketID, content, isPublic, isWaitClient, isWaitSunnetFeedback); TicketsEntity ticket = ticketApp.GetTickets(ticketID); var canAddWaiting = ticketApp.CanFeedbackWaiting(ticketID); if (!canAddWaiting) { feedbacksEntity.WaitPMFeedback = feedbacksEntity.WaitClientFeedback = FeedbackReplyStatus.Normal; } string fileuploadErrMsg = string.Empty; feedbacksEntity.ID = fbAPP.AddFeedBacks(feedbacksEntity); if (feedbacksEntity.ID <= 0) { var msg = fbAPP.BrokenRuleMessages != null && fbAPP.BrokenRuleMessages.Count > 0 ? fbAPP.BrokenRuleMessages[0].Message : ""; return(ResponseMessage.GetResponse(false, msg)); } // 产生气泡提示 if (UserInfo.Role == RolesEnum.CLIENT || isPublic) { ticketApp.CreateNotification(ticketID, UserInfo.UserID, false); } else { ticketApp.CreateNotification(ticketID, UserInfo.UserID); } var extraStatus = new Dictionary <string, bool>(); // 在添加FeedbackMessage时要更新Ticket 对相关人员的状态, 并且不能覆盖状态 if (ticket.Status != TicketsState.Wait_Sunnet_Feedback && ticket.Status != TicketsState.Wait_Client_Feedback && canAddWaiting) { if ((UserInfo.Role == RolesEnum.PM || UserInfo.Role == RolesEnum.Sales) && isWaitClient) { ticketApp.UpdateTicketStatus(ticketID, UserTicketStatus.WaitClientFeedback, UserID); extraStatus.Add("waitSunnet", ticketApp.UpdateTicketStatus(ticketID, UserTicketStatus.WaitClientFeedback, TicketUsersType.Create)); new SendHandler(() => ticketStatusMgr.SendEmailtoClientForFeedBack(feedbacksEntity)) .BeginInvoke(null, null); ProcessOtherClients(clientsIds, ticketID); } else if (UserInfo.Role == RolesEnum.CLIENT) { if (isWaitSunnetFeedback) { ticketApp.UpdateTicketStatus(ticketID, UserTicketStatus.WaitSunnetFeedback, UserID); extraStatus.Add("waitClient", ticketApp.UpdateTicketStatus(ticketID, UserTicketStatus.WaitSunnetFeedback, TicketUsersType.PM)); } new SendHandler(() => ticketStatusMgr.SendEmailtoPMForFeedBack(feedbacksEntity)) .BeginInvoke(null, null); } } else { // 相关状态人员可以通过回复清除自己的状态标识 if ((UserInfo.Role == RolesEnum.PM || UserInfo.Role == RolesEnum.Sales) && ticket.Status == TicketsState.Wait_Sunnet_Feedback && isPublic) { // Sunnet 直接清除PM的标识 if (ticketApp.UpdateTicketStatus(ticketID, UserTicketStatus.Normal, TicketUsersType.PM) && fbAPP.ReplyFeedback(ticketID, true, false)) { ticketApp.UpdateTicketStatus(ticketID, UserTicketStatus.Normal, TicketUsersType.Create, TicketUsersType.Client); ticket.ResponsibleUser = PmReplyClient(ProjectEntity, ticket); extraStatus.Add("clearSunnet", true); var createrId = ticketApp.GetTicketCreateUser(ticketID); var creater = _userApp.GetUser(createrId.UserID); new SendHandler(() => ticketStatusMgr.SendEmailFeedbackReplied(UserInfo, creater, ticketID)) .BeginInvoke(null, null); } } if (UserInfo.Role == RolesEnum.CLIENT && ticket.Status == TicketsState.Wait_Client_Feedback) { // 客户方面只能清除自己的标识,因为可能需要多个客户回复 if (ticketApp.TryClearWaiting(ticketID, UserID, TicketUsersType.Create, TicketUsersType.Client) && fbAPP.ReplyFeedback(ticketID, false, true)) { ticketApp.UpdateTicketStatus(ticketID, UserTicketStatus.Normal, TicketUsersType.PM); extraStatus.Add("clearClient", true); var pmId = ticketApp.GetTicketPM(ticketID); var pm = _userApp.GetUser(pmId.UserID); new SendHandler(() => ticketStatusMgr.SendEmailFeedbackReplied(UserInfo, pm, ticketID)) .BeginInvoke(null, null); ticketApp.DeleteUserFromTicket(ticketID, TicketUsersType.Client); } } } ticket.ModifiedOn = DateTime.Now; if (UserInfo.Role == RolesEnum.CLIENT && isWaitSunnetFeedback) { ChangeResponsUserToProjectManager(ProjectEntity, ticket); } ticketApp.UpdateTickets(ticket, false); #region add file int fileID = 0; string realFileName = ""; string[] files = file.Split("|".ToCharArray(), StringSplitOptions.RemoveEmptyEntries); Dictionary <string, int> uploadedFiles = new Dictionary <string, int>(); foreach (var f in files) { if ((fileID = InsertFile(f, ProjectEntity.ProjectID, ticketID, feedbacksEntity.IsPublic, feedbacksEntity.ID, Server, out realFileName)) < 1) { fileuploadErrMsg += realFileName + " Upload failed."; } uploadedFiles.Add(realFileName, fileID); } #endregion if (string.IsNullOrEmpty(fileuploadErrMsg)) { ResponseMessage response = new ResponseMessage(); response.Success = true; object attachement = null; object attachementBasicInfo = null; if (uploadedFiles.Count > 0) { attachement = uploadedFiles; string[] imgFormat = ".gif,.jpg,.jpeg,.bmp,.png,.svg,.raw".Split(new char[] { ',' }); attachementBasicInfo = uploadedFiles.Select(it => new { Key = it.Key, Value = imgFormat.Any(format => format == Path.GetExtension(it.Key.ToLower())) ? fileApp.Get(it.Value).FilePath : "/Images/icons/file.png" }).ToDictionary(it => it.Key, it => it.Value); } feedbacksEntity.Description = new HtmlHelper().ReplaceUrl(feedbacksEntity.Description); response.Data = new { content = feedback.FormatHTML(feedbacksEntity.Description, Server).Replace("\n", "<br/>"), id = feedbacksEntity.ID, date = feedbacksEntity.CreatedOn.ToString("MM/dd/yyyy"), time = feedbacksEntity.CreatedOn.ToString("hh:mm tt"), order = feedbacksEntity.Order, createdBy = feedbacksEntity.CreatedBy, firstname = UserInfo.FirstName, lastname = UserInfo.LastName, file = attachement, basicInfo = attachementBasicInfo, extraStatus = extraStatus, tdCss = feedbacksEntity.IsPublic && UserInfo.Role == RolesEnum.PM ? "fdcontentBox3" : "fdcontentBox2" }; return(response.ToString()); } else { return(ResponseMessage.GetResponse(false, fileuploadErrMsg)); } } catch (Exception ex) { WebLogAgent.Write(string.Format("Error Ascx:FeedbackList.ascx Messages:\r\n{0}", ex)); return(ResponseMessage.GetResponse(false, ex.Message)); } }
/// <summary> /// Update a record /// </summary> public bool Update(UsersEntity model) { StringBuilder strSql = new StringBuilder(); strSql.Append("update Users set "); strSql.Append("CompanyID=@CompanyID,"); strSql.Append("RoleID=@RoleID,"); strSql.Append("FirstName=@FirstName,"); strSql.Append("LastName=@LastName,"); strSql.Append("UserName=@UserName,"); strSql.Append("Email=@Email,"); strSql.Append("PassWord=@PassWord,"); strSql.Append("Title=@Title,"); strSql.Append("Phone=@Phone,"); strSql.Append("EmergencyContactFirstName=@EmergencyContactFirstName,"); strSql.Append("EmergencyContactLastName=@EmergencyContactLastName,"); strSql.Append("EmergencyContactPhone=@EmergencyContactPhone,"); strSql.Append("EmergencyContactEmail=@EmergencyContactEmail,"); strSql.Append("MaintenancePlanOption=@MaintenancePlanOption,"); strSql.Append("CreatedOn=@CreatedOn,"); strSql.Append("AccountStatus=@AccountStatus,"); strSql.Append("ForgotPassword=@ForgotPassword,"); strSql.Append("IsDelete=@IsDelete,"); strSql.Append("Status=@Status,"); strSql.Append("UserType=@UserType,"); strSql.Append("Skype=@Skype,"); strSql.Append("Office=@Office,"); strSql.Append("PTOHoursOfYear=@PTOHoursOfYear,"); strSql.Append("[IsNotice]=@IsNotice"); strSql.Append(" where UserID=@UserID "); Database db = DatabaseFactory.CreateDatabase(); using (DbCommand dbCommand = db.GetSqlStringCommand(strSql.ToString())) { try { db.AddInParameter(dbCommand, "UserID", DbType.Int32, model.UserID); db.AddInParameter(dbCommand, "CompanyID", DbType.Int32, model.CompanyID); db.AddInParameter(dbCommand, "RoleID", DbType.Int32, model.RoleID); db.AddInParameter(dbCommand, "FirstName", DbType.String, model.FirstName); db.AddInParameter(dbCommand, "LastName", DbType.String, model.LastName); db.AddInParameter(dbCommand, "UserName", DbType.String, model.UserName); db.AddInParameter(dbCommand, "Email", DbType.String, model.Email); db.AddInParameter(dbCommand, "PassWord", DbType.String, model.PassWord); db.AddInParameter(dbCommand, "Title", DbType.String, model.Title); db.AddInParameter(dbCommand, "Phone", DbType.String, model.Phone); db.AddInParameter(dbCommand, "EmergencyContactFirstName", DbType.String, model.EmergencyContactFirstName); db.AddInParameter(dbCommand, "EmergencyContactLastName", DbType.String, model.EmergencyContactLastName); db.AddInParameter(dbCommand, "EmergencyContactPhone", DbType.String, model.EmergencyContactPhone); db.AddInParameter(dbCommand, "EmergencyContactEmail", DbType.String, model.EmergencyContactEmail); db.AddInParameter(dbCommand, "MaintenancePlanOption", DbType.String, model.MaintenancePlanOption); db.AddInParameter(dbCommand, "CreatedOn", DbType.DateTime, model.CreatedOn); db.AddInParameter(dbCommand, "AccountStatus", DbType.Int32, model.AccountStatus); db.AddInParameter(dbCommand, "ForgotPassword", DbType.Int32, model.ForgotPassword); db.AddInParameter(dbCommand, "IsDelete", DbType.Boolean, model.IsDelete); db.AddInParameter(dbCommand, "Status", DbType.String, model.Status); db.AddInParameter(dbCommand, "UserType", DbType.String, model.UserType); db.AddInParameter(dbCommand, "Skype", DbType.String, model.Skype); db.AddInParameter(dbCommand, "Office", DbType.String, model.Office); db.AddInParameter(dbCommand, "PTOHoursOfYear", DbType.Double, model.PTOHoursOfYear); db.AddInParameter(dbCommand, "IsNotice", DbType.Boolean, model.IsNotice); int rows = db.ExecuteNonQuery(dbCommand); if (rows > 0) { return(true); } else { return(false); } } catch (Exception ex) { WebLogAgent.Write(string.Format("[SQLText:{0},{1}Messages:\r\n{2}]", strSql.ToString(), base.FormatParameters(dbCommand.Parameters), ex.Message)); return(false); } } }
public SearchCompaniesResponse SearchCompanies(SearchCompaniesRequest request) { int start = request.CurrentPage * request.PageCount + 1 - request.PageCount; int end = request.CurrentPage * request.PageCount; string strSelCount = " SELECT COUNT(1) FROM [Companys] "; string strOrderby = string.Format(" {0} {1} ", request.OrderExpression, request.OrderDirection); string strSelAttrs = @" SELECT * ,(SELECT COUNT(1) FROM Projects WHERE CompanyID = COM.ComID) AS ProjectsCount ,(SELECT COUNT(1) FROM Users WHERE CompanyID = COM.ComID) AS ClientsCount FROM [Companys] COM "; string strSelAttrsOrderBy = string.Format(@" Order BY {0} ", strOrderby); string strSelPageModel = string.Format(@"SELECT * FROM( SELECT ROW_NUMBER() OVER( Order BY {0}) as INDEX_ID,* ,(SELECT COUNT(1) FROM Projects WHERE CompanyID = COM.ComID) AS ProjectsCount ,(SELECT COUNT(1) FROM Users WHERE CompanyID = COM.ComID) AS ClientsCount FROM [Companys] COM ", strOrderby); string strWherePageModel = @") NEW_TB WHERE INDEX_ID BETWEEN @Strat AND @End;"; StringBuilder strWhere = new StringBuilder(); strWhere.Append(" WHERE 1=1 "); switch (request.SearchType) { case SearchCompanyType.SingleCompany: strWhere.Append(" AND [ComID]= @ComID "); break; case SearchCompanyType.Project: strWhere.Append(" AND [ComID] IN (SELECT CompanyID FROM Projects WHERE ProjectID =@ProjectID) "); break; case SearchCompanyType.User: strWhere.Append(" AND [ComID] IN (SELECT CompanyID FROM Users WHERE UserID =@UserID) "); break; case SearchCompanyType.List: strWhere.Append(" AND (CompanyName like @CompanyName or State like @CompanyName or City like @CompanyName ) "); break; case SearchCompanyType.All: break; default: break; } StringBuilder strSql = new StringBuilder(); if (request.IsPageModel) { strSql.Append(strSelCount); strSql.Append(strWhere); strSql.Append(";"); strSql.Append(strSelPageModel); strSql.Append(strWhere); strSql.Append(strWherePageModel); } else { strSql.Append(strSelAttrs); strSql.Append(strWhere); strSql.Append(strSelAttrsOrderBy); strSql.Append(";"); } SearchCompaniesResponse response = new SearchCompaniesResponse(); Database db = DatabaseFactory.CreateDatabase(); using (DbCommand dbCommand = db.GetSqlStringCommand(strSql.ToString())) { try { db.AddInParameter(dbCommand, "UserID", DbType.Int32, request.UserID); db.AddInParameter(dbCommand, "ProjectID", DbType.Int32, request.ProjectID); db.AddInParameter(dbCommand, "ComID", DbType.Int32, request.ComID); db.AddInParameter(dbCommand, "CompanyName", DbType.String, string.Format("%{0}%", request.CompanyName.FilterSqlString())); db.AddInParameter(dbCommand, "Strat", DbType.Int32, start); db.AddInParameter(dbCommand, "End", DbType.Int32, end); using (IDataReader dataReader = db.ExecuteReader(dbCommand)) { List <CompanysEntity> list = new List <CompanysEntity>(); if (request.IsPageModel) { if (dataReader.Read()) { response.ResultCount = dataReader.GetInt32(0); dataReader.NextResult(); } } while (dataReader.Read()) { list.Add(CompanysEntity.ReaderBind(dataReader, true)); } response.ResultList = list; } } catch (Exception ex) { WebLogAgent.Write(string.Format("[SQLText:{0},{1}Messages:\r\n{2}]", strSql.ToString(), base.FormatParameters(dbCommand.Parameters), ex.Message)); } } return(response); }
public SearchInvoiceResponse SearchTimesheetInvoice(SearchInvoiceRequest request) { StringBuilder sqlStr = new StringBuilder(); sqlStr.Append("SELECT ts.ID,[SheetDate] "); sqlStr.Append(" ,c.CompanyName,p.Title AS ProjectTitle,ts.[TicketID]"); sqlStr.Append(" ,t.Title AS TicketTitle,u.FirstName,[Hours]"); sqlStr.Append(" FROM [PM].[dbo].[TimeSheets] ts "); sqlStr.Append(" LEFT JOIN dbo.Tickets t ON ts.TicketID =t.TicketID "); sqlStr.Append(" LEFT JOIN dbo.Users u ON u.UserID=ts.CreatedBy "); sqlStr.Append(" LEFT JOIN dbo.Companys c ON c.ComID=t.CompanyID "); sqlStr.Append(" LEFT JOIN dbo.Projects p ON ts.ProjectID=p.ProjectID "); sqlStr.Append("WHERE 1=1 and p.ProjectID IN (" + request.ProjectIds + ") " + " and not exists (select * from TSInvoiceRelation where TSId=ts.id)"); if (request.timeTsheetIDs != "" && request.timeTsheetIDs != null) { sqlStr.Append("and ts.ID IN (" + request.timeTsheetIDs + ")"); } if (request.CompanyId > 0) { sqlStr.Append("and c.ComID = @CompanyID "); } if (request.OrderExpression != "" && request.OrderExpression != null) { sqlStr.AppendFormat("ORDER BY {0} ", request.OrderExpression); } else { sqlStr.Append("ORDER BY CompanyName "); } if (request.OrderDirection != "" && request.OrderDirection != null) { sqlStr.Append(request.OrderDirection); } else { sqlStr.Append("DESC;"); } List <TimesheetInvoiceModel> list; SearchInvoiceResponse response = new SearchInvoiceResponse(); Database db = DatabaseFactory.CreateDatabase(); using (DbCommand dbCommand = db.GetSqlStringCommand(sqlStr.ToString())) { try { db.AddInParameter(dbCommand, "OrderExpression", DbType.String, request.OrderExpression); db.AddInParameter(dbCommand, "OrderDirection", DbType.String, request.OrderDirection); // db.AddInParameter(dbCommand, "ProjectIDs", DbType.String, "("+request.ProjectIds+")"); db.AddInParameter(dbCommand, "CompanyID", DbType.Int32, request.CompanyId); using (IDataReader dataReader = db.ExecuteReader(dbCommand)) { list = new List <TimesheetInvoiceModel>(); while (dataReader.Read()) { list.Add(TimesheetInvoiceModel.ReaderBind(dataReader)); } response.TimesheetList = list; response.TimesheetCount = list.Count; } } catch (Exception ex) { WebLogAgent.Write(string.Format("[SQLText:{0},{1}Messages:\r\n{2}]", sqlStr.ToString(), base.FormatParameters(dbCommand.Parameters), ex.Message)); } } return(response); }
public void ProcessRequest(HttpContext context) { context.Response.ContentType = "text/plain"; try { if (IdentityContext.UserID <= 0) { return; } String checkboxList = context.Request["checkboxList"]; String tid = context.Request["tid"]; String type = context.Request["type"]; List <string> userArray = new List <string>(); List <string> userids = new List <string>(); List <string> userTypes = new List <string>(); if (!string.IsNullOrEmpty(checkboxList)) { userArray = checkboxList.Split(',').ToList(); } foreach (string user in userArray) { string[] values = user.Split('|'); userids.Add(values[0]); userTypes.Add(values[1]); } IEnumerable <string> distinctedUserTypes = userTypes.Distinct(); ticketApp.GetTicketUserId(Convert.ToInt32(tid));//remove all ticket user TicketUsersEntity entity = new TicketUsersEntity(); entity.TicketID = Convert.ToInt32(tid); int result = 0; bool HasError = false; for (int i = 0; i < userids.Count; i++) { if (userids[i].Length > 0) { userID = Convert.ToInt32(userids[i]); entity.UserID = userID; entity.Type = (TicketUsersType)int.Parse(userTypes[i] + ""); result = ticketApp.AddTicketUser(entity); if (result > 0) { if (enableEmail) { ticketStatusMgr.SendEmailToAssignedUser(entity); } } else { HasError = true; } } } if (!HasError) { context.Response.Write("User Assign Successful!"); } else { context.Response.Write("User Assign Fail!"); } } catch (Exception ex) { context.Response.Write("para error!"); WebLogAgent.Write(string.Format("Error Ashx:DoAssignUserHandler.ashx Messages:\r\n{0}", ex)); return; } }