public ResponseModel ExportDashBoardSearchToCSV([FromBody] SearchModelDashBoard searchparams) { string strCSV = string.Empty; ResponseModel objResponseModel = new ResponseModel(); DashBoardCaller dbSearchMaster = new DashBoardCaller(); int statusCode = 0; string statusMessage = ""; try { string token = Convert.ToString(Request.Headers["X-Authorized-Token"]); Authenticate authenticate = new Authenticate(); var temp = SecurityService.DecryptStringAES(token); authenticate = SecurityService.GetAuthenticateDataFromTokenCache(Cache, SecurityService.DecryptStringAES(token)); searchparams.TenantID = authenticate.TenantId; // add tenantID to request searchparams.curentUserId = authenticate.UserMasterID; // add currentUserID to request strCSV = dbSearchMaster.DashBoardSearchDataToCSV(new DashBoardService(Cache, Db), searchparams); statusCode = !string.IsNullOrEmpty(strCSV) ? (int)EnumMaster.StatusCode.Success : (int)EnumMaster.StatusCode.InternalServerError; statusMessage = CommonFunction.GetEnumDescription((EnumMaster.StatusCode)statusCode); objResponseModel.Status = true; objResponseModel.StatusCode = statusCode; objResponseModel.Message = statusMessage; objResponseModel.ResponseData = !string.IsNullOrEmpty(strCSV) ? File(new System.Text.UTF8Encoding().GetBytes(strCSV), "text/csv", "ABC.csv") : null; } catch (Exception) { throw; } return(objResponseModel); }
public DashBoardSavedSearch GetDashBoardTicketsOnSavedSearch(int TenantID, int UserID, int SearchParamID) { string jsonSearchParams = string.Empty; DataSet ds = new DataSet(); SearchModelDashBoard searchModel = new SearchModelDashBoard(); DashBoardSavedSearch dbsavedsearch = new DashBoardSavedSearch(); List <SearchResponseDashBoard> objSearchResult = new List <SearchResponseDashBoard>(); try { conn = Db.Connection; MySqlCommand cmd = new MySqlCommand("SP_GetSaveSearchByID_UTSM", conn); cmd.Connection = conn; cmd.Parameters.AddWithValue("@SearchParam_ID", SearchParamID); cmd.Parameters.AddWithValue("@searchFor", 1); cmd.CommandType = CommandType.StoredProcedure; MySqlDataAdapter da = new MySqlDataAdapter(); da.SelectCommand = cmd; da.Fill(ds); if (ds != null && ds.Tables[0] != null) { if (ds.Tables[0].Rows.Count > 0) { jsonSearchParams = ds.Tables[0].Rows[0]["SearchParameters"] == DBNull.Value ? string.Empty : Convert.ToString(ds.Tables[0].Rows[0]["SearchParameters"]); } } if (!string.IsNullOrEmpty(jsonSearchParams)) { searchModel = JsonConvert.DeserializeObject <SearchModelDashBoard>(jsonSearchParams); if (searchModel != null) { searchModel.TenantID = TenantID; searchModel.curentUserId = UserID; objSearchResult = GetDashboardTicketsOnSearch(searchModel); dbsavedsearch.dbsearchParams = jsonSearchParams; dbsavedsearch.DashboardTicketList = objSearchResult; } } } catch (Exception ex) { string message = Convert.ToString(ex.InnerException); //throw ex; } finally { if (ds != null) { ds.Dispose(); } } return(dbsavedsearch); }
/// <summary> /// Export DashBoard search result to CSV /// </summary> public string DashBoardSearchDataToCSV(SearchModelDashBoard searchModel) { List <SearchResponseDashBoard> objSearchResult = new List <SearchResponseDashBoard>(); string csv = string.Empty; try { objSearchResult = GetDashboardTicketsOnSearch(searchModel); if (objSearchResult.Count > 0) { csv = CommonService.ListToCSV(objSearchResult, ""); } } catch (Exception ex) { string message = Convert.ToString(ex.InnerException); throw ex; } return(csv); }
public ResponseModel DashBoardSearchTicket([FromBody] SearchModelDashBoard searchparams) { List <SearchResponseDashBoard> searchResult = null; ResponseModel objResponseModel = new ResponseModel(); int statusCode = 0; string statusMessage = ""; DashBoardCaller dbSearchMaster = new DashBoardCaller(); try { string token = Convert.ToString(Request.Headers["X-Authorized-Token"]); Authenticate authenticate = new Authenticate(); var temp = SecurityService.DecryptStringAES(token); authenticate = SecurityService.GetAuthenticateDataFromTokenCache(Cache, SecurityService.DecryptStringAES(token)); searchparams.TenantID = authenticate.TenantId; // add tenantID to request searchparams.curentUserId = authenticate.UserMasterID; // add currentUserID to request //searchparams.TenantID = 1; // add tenantID to request //searchparams.curentUserId = 9; // add currentUserID to request searchResult = dbSearchMaster.GetDashboardTicketsOnSearch(new DashBoardService(Cache, Db), searchparams); statusCode = searchResult.Count > 0 ? (int)EnumMaster.StatusCode.Success : (int)EnumMaster.StatusCode.RecordNotFound; statusMessage = CommonFunction.GetEnumDescription((EnumMaster.StatusCode)statusCode); objResponseModel.Status = true; objResponseModel.StatusCode = statusCode; objResponseModel.Message = statusMessage; objResponseModel.ResponseData = searchResult.Count > 0 ? searchResult : null; } catch (Exception ex) { throw ex; } return(objResponseModel); }
/// <summary> /// Get tickets on the dashboard /// </summary> public List <SearchResponseDashBoard> GetDashboardTicketsOnSearch(SearchModelDashBoard searchModel) { DataSet ds = new DataSet(); MySqlCommand cmd = new MySqlCommand(); List <SearchResponseDashBoard> objSearchResult = new List <SearchResponseDashBoard>(); List <string> countList = new List <string>(); int rowStart = 0; // searchparams.pageNo - 1) * searchparams.pageSize; try { //if (conn != null && conn.State == ConnectionState.Closed) //{ // conn = Db.Connection; //} if (conn != null && conn.State == ConnectionState.Closed) { conn = Db.Connection; } if (conn != null && conn.State == ConnectionState.Closed) { conn.Open(); } /*Based on active tab stored procedure will call * 1. SP_SearchTicketData_ByDate * 2. SP_SearchTicketData_ByCustomerType * 3. SP_SearchTicketData_ByTicketType * 4. SP_SearchTicketData_ByCategoryType * 5. SP_SearchTicketData_ByAll */ MySqlCommand sqlcmd = new MySqlCommand("", conn); // sqlcmd.Parameters.AddWithValue("HeaderStatus_Id", searchModel.HeaderStatusId); if (searchModel.ActiveTabId == 1)//ByDate { sqlcmd.CommandText = "SP_SearchTicketData_ByDate_ForDashboard"; sqlcmd.Parameters.AddWithValue("Ticket_CreatedOn", string.IsNullOrEmpty(searchModel.searchDataByDate.Ticket_CreatedOn) ? "" : searchModel.searchDataByDate.Ticket_CreatedOn); sqlcmd.Parameters.AddWithValue("Ticket_ModifiedOn", string.IsNullOrEmpty(searchModel.searchDataByDate.Ticket_ModifiedOn) ? "" : searchModel.searchDataByDate.Ticket_ModifiedOn); sqlcmd.Parameters.AddWithValue("SLA_DueON", searchModel.searchDataByDate.SLA_DueON); sqlcmd.Parameters.AddWithValue("Ticket_StatusID", searchModel.searchDataByDate.Ticket_StatusID); } else if (searchModel.ActiveTabId == 2)//ByCustomerType { sqlcmd.CommandText = "SP_SearchTicketData_ByCustomerType_ForDashBoard"; sqlcmd.Parameters.AddWithValue("CustomerMobileNo", string.IsNullOrEmpty(searchModel.searchDataByCustomerType.CustomerMobileNo) ? "" : searchModel.searchDataByCustomerType.CustomerMobileNo); sqlcmd.Parameters.AddWithValue("customerEmail", string.IsNullOrEmpty(searchModel.searchDataByCustomerType.CustomerEmailID) ? "" : searchModel.searchDataByCustomerType.CustomerEmailID); if (string.IsNullOrEmpty(Convert.ToString(searchModel.searchDataByCustomerType.TicketID)) || Convert.ToString(searchModel.searchDataByCustomerType.TicketID) == "") { sqlcmd.Parameters.AddWithValue("TicketID", 0); } else { sqlcmd.Parameters.AddWithValue("TicketID", Convert.ToInt32(searchModel.searchDataByCustomerType.TicketID)); } sqlcmd.Parameters.AddWithValue("TicketStatusID", searchModel.searchDataByCustomerType.TicketStatusID); } else if (searchModel.ActiveTabId == 3)//ByTicketType { sqlcmd.CommandText = "SP_SearchTicketData_ByTicketType_ForDashBoard"; sqlcmd.Parameters.AddWithValue("Priority_Id", searchModel.searchDataByTicketType.TicketPriorityID); sqlcmd.Parameters.AddWithValue("TicketStatusID", searchModel.searchDataByTicketType.TicketStatusID); sqlcmd.Parameters.AddWithValue("channelOfPurchaseIDs", string.IsNullOrEmpty(searchModel.searchDataByTicketType.ChannelOfPurchaseIds) ? "" : searchModel.searchDataByTicketType.ChannelOfPurchaseIds); sqlcmd.Parameters.AddWithValue("ActionTypeIds", searchModel.searchDataByTicketType.ActionTypes); } else if (searchModel.ActiveTabId == 4) //ByCategory { sqlcmd.CommandText = "SP_SearchTicketData_ByCategory_Dashboard"; sqlcmd.Parameters.AddWithValue("Category_Id", searchModel.searchDataByCategoryType.CategoryId); sqlcmd.Parameters.AddWithValue("SubCategory_Id", searchModel.searchDataByCategoryType.SubCategoryId); sqlcmd.Parameters.AddWithValue("IssueType_Id", searchModel.searchDataByCategoryType.IssueTypeId); sqlcmd.Parameters.AddWithValue("Ticket_StatusID", searchModel.searchDataByCategoryType.TicketStatusID); } else if (searchModel.ActiveTabId == 5) { sqlcmd.CommandText = "SP_SearchTicketData_ByAll_ForDashBoard"; /*Column 1 (5)*/ sqlcmd.Parameters.AddWithValue("Ticket_CreatedOn", string.IsNullOrEmpty(searchModel.searchDataByAll.CreatedDate) ? "" : searchModel.searchDataByAll.CreatedDate); sqlcmd.Parameters.AddWithValue("Ticket_ModifiedOn", string.IsNullOrEmpty(searchModel.searchDataByAll.ModifiedDate) ? "" : searchModel.searchDataByAll.ModifiedDate); sqlcmd.Parameters.AddWithValue("Category_Id", searchModel.searchDataByAll.CategoryId); sqlcmd.Parameters.AddWithValue("SubCategory_Id", searchModel.searchDataByAll.SubCategoryId); sqlcmd.Parameters.AddWithValue("IssueType_Id", searchModel.searchDataByAll.IssueTypeId); /*Column 2 (5) */ sqlcmd.Parameters.AddWithValue("TicketSourceType_ID", searchModel.searchDataByAll.TicketSourceTypeID); sqlcmd.Parameters.AddWithValue("TicketIdORTitle", string.IsNullOrEmpty(searchModel.searchDataByAll.TicketIdORTitle) ? "" : searchModel.searchDataByAll.TicketIdORTitle); sqlcmd.Parameters.AddWithValue("Priority_Id", searchModel.searchDataByAll.PriorityId); sqlcmd.Parameters.AddWithValue("Ticket_StatusID", searchModel.searchDataByAll.TicketSatutsID); sqlcmd.Parameters.AddWithValue("SLAStatus", string.IsNullOrEmpty(searchModel.searchDataByAll.SLAStatus) ? "" : searchModel.searchDataByAll.SLAStatus); /*Column 3 (5)*/ sqlcmd.Parameters.AddWithValue("TicketClaim_ID", Convert.ToInt32(searchModel.searchDataByAll.ClaimId)); sqlcmd.Parameters.AddWithValue("InvoiceNumberORSubOrderNo", string.IsNullOrEmpty(searchModel.searchDataByAll.InvoiceNumberORSubOrderNo) ? "" : searchModel.searchDataByAll.InvoiceNumberORSubOrderNo); sqlcmd.Parameters.AddWithValue("OrderItemId", string.IsNullOrEmpty(Convert.ToString(searchModel.searchDataByAll.OrderItemId)) ? 0 : Convert.ToInt32(searchModel.searchDataByAll.OrderItemId)); //sqlcmd.Parameters.AddWithValue("IsVisitedStore", searchModel.searchDataByAll.IsVisitStore == "yes" ? 1 : 0); //sqlcmd.Parameters.AddWithValue("IsWantToVisitStore", searchModel.searchDataByAll.IsWantVistingStore == "yes" ? 1 : 0); /*All for to load all the data*/ if (searchModel.searchDataByAll.IsVisitStore.ToLower() != "all") { sqlcmd.Parameters.AddWithValue("IsVisitedStore", searchModel.searchDataByAll.IsVisitStore == "yes" ? 1 : 0); } else { sqlcmd.Parameters.AddWithValue("IsVisitedStore", -1); } if (searchModel.searchDataByAll.IsWantVistingStore.ToLower() != "all") { sqlcmd.Parameters.AddWithValue("IsWantToVisitStore", searchModel.searchDataByAll.IsWantVistingStore == "yes" ? 1 : 0); } else { sqlcmd.Parameters.AddWithValue("IsWantToVisitStore", -1); } /*Column 4 (5)*/ sqlcmd.Parameters.AddWithValue("Customer_EmailID", searchModel.searchDataByAll.CustomerEmailID); sqlcmd.Parameters.AddWithValue("CustomerMobileNo", string.IsNullOrEmpty(searchModel.searchDataByAll.CustomerMobileNo) ? "" : searchModel.searchDataByAll.CustomerMobileNo); sqlcmd.Parameters.AddWithValue("AssignTo", string.IsNullOrEmpty(searchModel.searchDataByAll.AssignTo) ? 0 : Convert.ToInt32(searchModel.searchDataByAll.AssignTo)); sqlcmd.Parameters.AddWithValue("StoreCodeORAddress", searchModel.searchDataByAll.StoreCodeORAddress); sqlcmd.Parameters.AddWithValue("WantToStoreCodeORAddress", searchModel.searchDataByAll.WantToStoreCodeORAddress); //Row - 2 and Column - 1 (5) sqlcmd.Parameters.AddWithValue("HaveClaim", searchModel.searchDataByAll.HaveClaim); sqlcmd.Parameters.AddWithValue("ClaimStatusId", searchModel.searchDataByAll.ClaimStatusId); sqlcmd.Parameters.AddWithValue("ClaimCategoryId", searchModel.searchDataByAll.ClaimCategoryId); sqlcmd.Parameters.AddWithValue("ClaimSubCategoryId", searchModel.searchDataByAll.ClaimSubCategoryId); sqlcmd.Parameters.AddWithValue("ClaimIssueTypeId", searchModel.searchDataByAll.ClaimIssueTypeId); //Row - 2 and Column - 2 (4) sqlcmd.Parameters.AddWithValue("HaveTask", searchModel.searchDataByAll.HaveTask); sqlcmd.Parameters.AddWithValue("TaskStatus_Id", searchModel.searchDataByAll.TaskStatusId); sqlcmd.Parameters.AddWithValue("TaskDepartment_Id", searchModel.searchDataByAll.TaskDepartment_Id); sqlcmd.Parameters.AddWithValue("TaskFunction_Id", searchModel.searchDataByAll.TaskFunction_Id); } sqlcmd.Parameters.AddWithValue("CurrentUserId", searchModel.curentUserId); sqlcmd.Parameters.AddWithValue("Tenant_ID", searchModel.TenantID); sqlcmd.Parameters.AddWithValue("Assignto_IDs", searchModel.AssigntoId.TrimEnd(',')); sqlcmd.Parameters.AddWithValue("Brand_IDs", searchModel.BrandId.TrimEnd(',')); sqlcmd.CommandType = CommandType.StoredProcedure; MySqlDataAdapter da = new MySqlDataAdapter(); da.SelectCommand = sqlcmd; da.Fill(ds); if (ds != null && ds.Tables != null) { if (ds.Tables[0] != null && ds.Tables[0].Rows.Count > 0) { objSearchResult = ds.Tables[0].AsEnumerable().Select(r => new SearchResponseDashBoard() { ticketID = Convert.ToInt32(r.Field <object>("TicketID")), ticketStatus = Convert.ToString((EnumMaster.TicketStatus)Convert.ToInt32(r.Field <object>("StatusID"))), Message = r.Field <object>("TicketDescription") == System.DBNull.Value ? string.Empty : Convert.ToString(r.Field <object>("TicketDescription")), Category = r.Field <object>("CategoryName") == System.DBNull.Value ? string.Empty : Convert.ToString(r.Field <object>("CategoryName")), subCategory = r.Field <object>("SubCategoryName") == System.DBNull.Value ? string.Empty : Convert.ToString(r.Field <object>("SubCategoryName")), IssueType = r.Field <object>("IssueTypeName") == System.DBNull.Value ? string.Empty : Convert.ToString(r.Field <object>("IssueTypeName")), Priority = r.Field <object>("PriortyName") == System.DBNull.Value ? string.Empty : Convert.ToString(r.Field <object>("PriortyName")), Assignee = r.Field <object>("AssignedName") == System.DBNull.Value ? string.Empty : Convert.ToString(r.Field <object>("AssignedName")), CreatedOn = r.Field <object>("CreatedOn") == System.DBNull.Value ? string.Empty : Convert.ToString(r.Field <object>("CreatedOn")), createdBy = r.Field <object>("CreatedByName") == System.DBNull.Value ? string.Empty : Convert.ToString(r.Field <object>("CreatedByName")), createdago = r.Field <object>("CreatedDate") == System.DBNull.Value ? string.Empty : setCreationdetails(Convert.ToString(r.Field <object>("CreatedDate")), "CreatedSpan"), assignedTo = r.Field <object>("AssignedName") == System.DBNull.Value ? string.Empty : Convert.ToString(r.Field <object>("AssignedName")), assignedago = r.Field <object>("AssignedDate") == System.DBNull.Value ? string.Empty : setCreationdetails(Convert.ToString(r.Field <object>("AssignedDate")), "AssignedSpan"), updatedBy = r.Field <object>("ModifyByName") == System.DBNull.Value ? string.Empty : Convert.ToString(r.Field <object>("ModifyByName")), updatedago = r.Field <object>("ModifiedDate") == System.DBNull.Value ? string.Empty : setCreationdetails(Convert.ToString(r.Field <object>("ModifiedDate")), "ModifiedSpan"), responseTimeRemainingBy = (r.Field <object>("AssignedDate") == System.DBNull.Value || r.Field <object>("PriorityRespond") == System.DBNull.Value) ? string.Empty : setCreationdetails(Convert.ToString(r.Field <object>("PriorityRespond")) + "|" + Convert.ToString(r.Field <object>("AssignedDate")), "RespondTimeRemainingSpan"), responseOverdueBy = (r.Field <object>("AssignedDate") == System.DBNull.Value || r.Field <object>("PriorityRespond") == System.DBNull.Value) ? string.Empty : setCreationdetails(Convert.ToString(r.Field <object>("PriorityRespond")) + "|" + Convert.ToString(r.Field <object>("AssignedDate")), "ResponseOverDueSpan"), resolutionOverdueBy = (r.Field <object>("AssignedDate") == System.DBNull.Value || r.Field <object>("PriorityResolve") == System.DBNull.Value) ? string.Empty : setCreationdetails(Convert.ToString(r.Field <object>("PriorityResolve")) + "|" + Convert.ToString(r.Field <object>("AssignedDate")), "ResolutionOverDueSpan"), TaskStatus = r.Field <object>("TaskDetails") == System.DBNull.Value ? string.Empty : Convert.ToString(r.Field <object>("TaskDetails")), ClaimStatus = r.Field <object>("ClaimDetails") == System.DBNull.Value ? string.Empty : Convert.ToString(r.Field <object>("ClaimDetails")), TicketCommentCount = r.Field <object>("TicketComments") == System.DBNull.Value ? 0 : Convert.ToInt32(r.Field <object>("TicketComments")), isEscalation = r.Field <object>("IsEscalated") == System.DBNull.Value ? 0 : Convert.ToInt32(r.Field <object>("IsEscalated")), ticketSourceType = Convert.ToString(r.Field <object>("TicketSourceType")), IsReassigned = Convert.ToBoolean(r.Field <object>("IsReassigned")), ticketSourceTypeID = Convert.ToInt16(r.Field <object>("TicketSourceTypeID")), IsSLANearBreach = Convert.ToBoolean(r.Field <object>("IsSLANearBreach")) }).ToList(); } } //paging here //if (searchparams.pageSize > 0 && objSearchResult.Count > 0) // objSearchResult[0].totalpages = objSearchResult.Count > searchparams.pageSize ? Math.Round(Convert.ToDouble(objSearchResult.Count / searchparams.pageSize)) : 1; //objSearchResult = objSearchResult.Skip(rowStart).Take(searchparams.pageSize).ToList(); } catch (Exception ex) { string message = Convert.ToString(ex.InnerException); throw ex; } return(objSearchResult); }
public string DashBoardSearchDataToCSV(IDashBoard _dashboard, SearchModelDashBoard searchModel) { _dashboardlist = _dashboard; return(_dashboardlist.DashBoardSearchDataToCSV(searchModel)); }
public List <SearchResponseDashBoard> GetDashboardTicketsOnSearch(IDashBoard _dashboard, SearchModelDashBoard searchModel) { _dashboardlist = _dashboard; return(_dashboardlist.GetDashboardTicketsOnSearch(searchModel)); }