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;
}
}
