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