370 lines
16 KiB
C#
370 lines
16 KiB
C#
namespace VECV_WebApi.Controllers.Ticket
|
|
{
|
|
using ExcelHelper;
|
|
#region Namespaces
|
|
|
|
using LoggingHelper;
|
|
using Newtonsoft.Json;
|
|
using System;
|
|
using System.Collections.Generic;
|
|
using System.Configuration;
|
|
using System.Data;
|
|
using System.Linq;
|
|
using System.Web;
|
|
using System.Web.Http;
|
|
using System.Web.Mvc;
|
|
using VECV_WebApi.Common;
|
|
using VECV_WebApi.CommonAuthorization;
|
|
using VECV_WebApi.Models.Customer;
|
|
using VECV_WebApi.Models.EmailServices;
|
|
using VECV_WebApi.Models.Ticket;
|
|
using VECV_WebApi.Models.Vehicle;
|
|
|
|
|
|
#endregion
|
|
|
|
/// <summary>
|
|
/// This controller contain ticket related api
|
|
/// </summary>
|
|
public class AddOnReportController : ApiController
|
|
{
|
|
#region Global Variable
|
|
|
|
/// <summary>
|
|
/// making object of LoggingUtility class available to this class
|
|
/// </summary>
|
|
LoggingUtility objLog = new LoggingUtility();
|
|
|
|
/// <summary>
|
|
/// making the data-log file path available to this class
|
|
/// </summary>
|
|
string path = HttpContext.Current.Server.MapPath(ConfigurationManager.AppSettings["PathLog"]);
|
|
|
|
/// <summary>
|
|
/// making data log file path available to this class
|
|
/// </summary>
|
|
string logtf = (ConfigurationManager.AppSettings["Log"]);
|
|
|
|
/// <summary>
|
|
/// making error log file path available to this class
|
|
/// </summary>
|
|
string errorlogtf = (ConfigurationManager.AppSettings["ErrorLog"]);
|
|
|
|
string _appName = (ConfigurationManager.AppSettings["ApplicationName"]);
|
|
|
|
/// <summary>
|
|
/// making the Database connection string available to this class
|
|
/// </summary>
|
|
private string _connStr = ConfigurationManager.ConnectionStrings["Vecv_GoData"].ToString();
|
|
|
|
/// <summary>
|
|
/// making Global Repository object available to this class
|
|
/// </summary>
|
|
/// GlobalRepository objGlobalRepository;
|
|
|
|
/// <summary>
|
|
/// making Customer Repository object available to this class
|
|
/// </summary>
|
|
CustomerRepository objCustomerRepository;
|
|
|
|
/// <summary>
|
|
/// making Ticket Repository object available to this class
|
|
/// </summary>
|
|
TicketRepository objTicketRepository;
|
|
|
|
|
|
#endregion
|
|
|
|
|
|
#region APIs
|
|
|
|
public string Post([FromUri] string ConsolidateReport, [FromBody] TicketRequestModel model)
|
|
{
|
|
string _excelExportPath = ConfigurationManager.AppSettings["excelExportPath"].ToString();
|
|
// write data log into file
|
|
string fileName = null;
|
|
bool isCreated = false;
|
|
TicketRequestModel ObjModel = new TicketRequestModel();
|
|
try
|
|
{
|
|
TicketRepository objTicket = new TicketRepository(_connStr);
|
|
//model.TicketType = TicketType;
|
|
|
|
ReportOpenTicketModel oConsolidatedReportList = objTicket.ConsolidatedDataReportActualKM(model);
|
|
|
|
List<ConsolidatedReportModelNew> oList = new List<ConsolidatedReportModelNew>();
|
|
|
|
oList = oConsolidatedReportList.ConsolidatedReportModelList;
|
|
|
|
string ticketCreationDateTime = "", ticketCreationTime = "";
|
|
foreach (var item in oList)
|
|
{
|
|
ticketCreationDateTime = item.creation_time;
|
|
string[] split_ticketCreationDateTime = ticketCreationDateTime.Split(' ');
|
|
ticketCreationTime = split_ticketCreationDateTime[3];
|
|
|
|
for (var count = 0; count < 12; count++)
|
|
{
|
|
if ((DateTime.Parse("01/01/0001 " + ticketCreationTime) >= DateTime.Parse("01/01/0001 " + (2 * count) + ":00:00")) && (DateTime.Parse("01/01/0001 " + ticketCreationTime) <= DateTime.Parse("01/01/0001 " + ((2 * count) + 1) + ":59:59")))
|
|
{
|
|
item.time_slot = (2 * count) + ":00 - " + ((2 * count) + 2) + ":00";
|
|
break;
|
|
}
|
|
}
|
|
item.ChassisNo = item.chassis_number;
|
|
}
|
|
DataTable ConsolidatedDataTable = oList.ToDataTable();
|
|
DataSet ds = new DataSet();
|
|
ds.Tables.Add(ConsolidatedDataTable);
|
|
ds.Tables[0].Columns[67].SetOrdinal(14);
|
|
//Give column names
|
|
ds.Tables[0].Columns[0].ColumnName = "Ticket Id";
|
|
ds.Tables[0].Columns[1].ColumnName = "Assigned To";
|
|
ds.Tables[0].Columns[2].ColumnName = "Created By";
|
|
ds.Tables[0].Columns[3].ColumnName = "Creation Time";
|
|
ds.Tables[0].Columns[4].ColumnName = "Time Slot";
|
|
ds.Tables[0].Columns[5].ColumnName = "Customer Name";
|
|
ds.Tables[0].Columns[6].ColumnName = "Customer Contact No.";
|
|
ds.Tables[0].Columns[7].ColumnName = "Organization";
|
|
ds.Tables[0].Columns[8].ColumnName = "State";
|
|
ds.Tables[0].Columns[9].ColumnName = "City";
|
|
ds.Tables[0].Columns[10].ColumnName = "Language";
|
|
ds.Tables[0].Columns[11].ColumnName = "Vehicle Owner Name";
|
|
ds.Tables[0].Columns[12].ColumnName = "Vehicle Owner Contact No.";
|
|
ds.Tables[0].Columns[13].ColumnName = "Vehicle Reg No.";
|
|
ds.Tables[0].Columns[14].ColumnName = "Chassis Number";
|
|
ds.Tables[0].Columns[15].ColumnName = "Vehicle Model";
|
|
ds.Tables[0].Columns[16].ColumnName = "Vehicle Type";
|
|
ds.Tables[0].Columns[17].ColumnName = "Vehicle Chassis No.";
|
|
ds.Tables[0].Columns[18].ColumnName = "Vehicle Installation Date";
|
|
ds.Tables[0].Columns[19].ColumnName = "Vehicle Breakdown Location";
|
|
ds.Tables[0].Columns[20].ColumnName = "Landmark";
|
|
ds.Tables[0].Columns[21].ColumnName = "Warranty";
|
|
ds.Tables[0].Columns[22].ColumnName = "Source of Toll Free No.";
|
|
ds.Tables[0].Columns[23].ColumnName = "Type of Load Carrying";
|
|
ds.Tables[0].Columns[24].ColumnName = "Nature of Problem";
|
|
ds.Tables[0].Columns[25].ColumnName = "Estimated Cost";
|
|
ds.Tables[0].Columns[26].ColumnName = "Estimated Time (In minutes)";
|
|
ds.Tables[0].Columns[27].ColumnName = "Dealer SCode";
|
|
ds.Tables[0].Columns[28].ColumnName = "Dealer Name";
|
|
ds.Tables[0].Columns[29].ColumnName = "WM Name";
|
|
ds.Tables[0].Columns[30].ColumnName = "Dealer Contact No.";
|
|
ds.Tables[0].Columns[31].ColumnName = "EOS VAN Registration Number";
|
|
ds.Tables[0].Columns[32].ColumnName = "Call Open (HH:MM:SS)";
|
|
ds.Tables[0].Columns[34].ColumnName = "Van Reached";
|
|
ds.Tables[0].Columns[35].ColumnName = "Call Preclosed";
|
|
ds.Tables[0].Columns[36].ColumnName = "Call Closure";
|
|
ds.Tables[0].Columns[37].ColumnName = "Escalation - Van Not Moving";
|
|
ds.Tables[0].Columns[38].ColumnName = "Escalation - Ticket Closure";
|
|
ds.Tables[0].Columns[39].ColumnName = "Call Closed < 24 Hrs";
|
|
ds.Tables[0].Columns[40].ColumnName = "KM Covered Breakdown's Vehicle";
|
|
ds.Tables[0].Columns[41].ColumnName = "Feedback";
|
|
ds.Tables[0].Columns[42].ColumnName = "Feedback Score - Ease of Getting Call Centre";
|
|
ds.Tables[0].Columns[43].ColumnName = "Feedback Score - Response of Call Centre";
|
|
ds.Tables[0].Columns[44].ColumnName = "Feedback Score - Timely Updation by Dealer";
|
|
ds.Tables[0].Columns[45].ColumnName = "Feedback Score - Total Repair Time";
|
|
ds.Tables[0].Columns[46].ColumnName = "Feedback Score - EOS Charges";
|
|
ds.Tables[0].Columns[47].ColumnName = "Feedback Score - Over All Experience";
|
|
ds.Tables[0].Columns[48].ColumnName = "Closure Remarks";
|
|
ds.Tables[0].Columns[49].ColumnName = "Response(In min)";
|
|
ds.Tables[0].Columns[50].ColumnName = "Feedback Count";
|
|
ds.Tables[0].Columns[51].ColumnName = "Feedback Call Status";
|
|
ds.Tables[0].Columns[52].ColumnName = "Feedback Complaints";
|
|
ds.Tables[0].Columns[53].ColumnName = "Feedback Suggestions";
|
|
ds.Tables[0].Columns[54].ColumnName = "Feedback Agent";
|
|
ds.Tables[0].Columns[55].ColumnName = "Reason for Ticket assigned to Dealer";
|
|
ds.Tables[0].Columns[56].ColumnName = "Other Reason for Ticket assigned to Dealer";
|
|
ds.Tables[0].Columns[57].ColumnName = "Estimated Distance (In KM)";
|
|
ds.Tables[0].Columns[58].ColumnName = "EOS Van Actual Distance Traveled (In KM)";
|
|
ds.Tables[0].Columns[59].ColumnName = "EOS Van Trip Start Time";
|
|
ds.Tables[0].Columns[60].ColumnName = "EOS Van Trip End Time";
|
|
ds.Tables[0].Columns[61].ColumnName = "Eicher Promise";
|
|
ds.Tables[0].Columns[62].ColumnName = "Opportunity Loss";
|
|
ds.Tables[0].Columns[63].ColumnName = "Opportunity Loss Reason";
|
|
ds.Tables[0].Columns[64].ColumnName = "Opportunity Loss Remark";
|
|
ds.Tables[0].Columns[65].ColumnName = "Delayed Reason";
|
|
ds.Tables[0].Columns[66].ColumnName = "Delayed Remark";
|
|
ds.Tables[0].Columns[67].ColumnName = "Re Open Count";
|
|
ds.Tables[0].Columns[68].ColumnName = "Odometer Reading";
|
|
ds.Tables[0].Columns[74].ColumnName = "Ticket Accepted";
|
|
ds.Tables[0].Columns[102].ColumnName = "Reassignment Date and Time";
|
|
ds.Tables[0].Columns[106].ColumnName = "Response time(Reassignment)";
|
|
//added on 14-12-2020
|
|
ds.Tables[0].Columns[107].ColumnName = "Vehicle Warranty";
|
|
ds.Tables[0].Columns[108].ColumnName = "Vehicle Amc";
|
|
ds.Tables[0].Columns[109].ColumnName = "Vehicle Emission Norms ";
|
|
ds.Tables[0].Columns[110].ColumnName = "Vehicle Sales Date";
|
|
ds.Tables[0].Columns[112].ColumnName = "Ticket closed reason";
|
|
|
|
ds.Tables[0].Columns[113].ColumnName = "Location Change Time";
|
|
ds.Tables[0].Columns[114].ColumnName = "Is Location change";
|
|
ds.Tables[0].Columns[115].ColumnName = "Dealer Change Reason";
|
|
ds.Tables[0].Columns[116].ColumnName = "Dealer Change Reason Remarks";
|
|
ds.Tables[0].Columns[117].ColumnName = "Vehicle Status";
|
|
ds.Tables[0].Columns[118].ColumnName = "Fuel Type";
|
|
//Remove columns
|
|
ds.Tables[0].Columns.Remove("Vehicle Chassis No.");
|
|
ds.Tables[0].Columns.Remove("total_outbound_calls");
|
|
//added on 14-12-2020
|
|
ds.Tables[0].Columns.Remove("time_stamps_status_1");
|
|
ds.Tables[0].Columns.Remove("escalation_level_van_not_move_2");
|
|
ds.Tables[0].Columns.Remove("escalation_level_van_not_move_3");
|
|
ds.Tables[0].Columns.Remove("escalation_level_van_not_move_4");
|
|
ds.Tables[0].Columns.Remove("escalation_level_van_not_move_5");
|
|
ds.Tables[0].Columns.Remove("escalation_level_van_not_move_6");
|
|
ds.Tables[0].Columns.Remove("escalation_level_van_not_move_7");
|
|
ds.Tables[0].Columns.Remove("escalation_level_van_not_move_8");
|
|
ds.Tables[0].Columns.Remove("escalation_level_van_not_move_9");
|
|
ds.Tables[0].Columns.Remove("escalation_level_van_not_move_10");
|
|
ds.Tables[0].Columns.Remove("escalation_level_ticket_closer_2");
|
|
ds.Tables[0].Columns.Remove("escalation_level_ticket_closer_3");
|
|
ds.Tables[0].Columns.Remove("escalation_level_ticket_closer_4");
|
|
ds.Tables[0].Columns.Remove("escalation_level_ticket_closer_5");
|
|
ds.Tables[0].Columns.Remove("escalation_level_ticket_closer_6");
|
|
ds.Tables[0].Columns.Remove("escalation_level_ticket_closer_7");
|
|
ds.Tables[0].Columns.Remove("escalation_level_ticket_closer_8");
|
|
ds.Tables[0].Columns.Remove("escalation_level_ticket_closer_9");
|
|
ds.Tables[0].Columns.Remove("escalation_level_ticket_closer_10");
|
|
ds.Tables[0].Columns.Remove("month_name");
|
|
ds.Tables[0].Columns.Remove("default_sla_time");
|
|
ds.Tables[0].Columns.Remove("region");
|
|
ds.Tables[0].Columns.Remove("vehicle_model");
|
|
ds.Tables[0].Columns.Remove("CloseTicketCountInTimeSlot");
|
|
ds.Tables[0].Columns.Remove("estimated_time");
|
|
ds.Tables[0].Columns.Remove("response");
|
|
ds.Tables[0].Columns.Remove("estimated_cost");
|
|
ds.Tables[0].Columns.Remove("ticket_id");
|
|
ds.Tables[0].Columns.Remove("are_you_satisfied");
|
|
ds.Tables[0].Columns.Remove("not_satisfied_option");
|
|
ds.Tables[0].Columns.Remove("not_satisfied_reason");
|
|
ds.Tables[0].Columns.Remove("van_reached_punch_time");
|
|
//ds.Tables[0].Columns.Remove("Ticket closed reason"); //added on 20-01-2021
|
|
ds.Tables[0].Columns.Remove("_call_closed_within_24_hours");
|
|
|
|
string _excelExportPathOnServer = ConfigurationManager.AppSettings["excelExportPathOnServer"].ToString();
|
|
fileName = "ConsolidatedReport_" + DateTime.Now.ToString("ddMMMyyyyhhmmss") + ".xlsx";
|
|
string SaveCsvAs = HttpContext.Current.Server.MapPath(_excelExportPathOnServer + fileName);
|
|
// string SaveCsvAs = "";
|
|
isCreated = CreateExcelFile.CreateExcelDocument(ds, SaveCsvAs);
|
|
|
|
return _excelExportPath + fileName;
|
|
}
|
|
catch (Exception Ex)
|
|
{
|
|
// write error log into file
|
|
objLog.ErrorLogFile("ConsolidatedDataReport", Ex.Message, path, errorlogtf);
|
|
throw Ex;
|
|
}
|
|
// return _excelExportPath + fileName;
|
|
|
|
}
|
|
|
|
// [JwtAuthentication]
|
|
//[System.Web.Http.Authorize]
|
|
public List<ConsolidatedReportModelClosed> Post([FromBody] TicketRequestModel model)
|
|
{
|
|
TicketRequestModel ObjModel = new TicketRequestModel();
|
|
List<ConsolidatedReportModelClosed> oList = new List<ConsolidatedReportModelClosed>();
|
|
try
|
|
{
|
|
|
|
TicketRepository objTicket = new TicketRepository(_connStr);
|
|
//model.TicketType = TicketType;
|
|
string _dateTimeFormat = ConfigurationManager.AppSettings["dateTimeFormat"].ToString();
|
|
DateTime passedDate = Convert.ToDateTime(model.StartDate); // your input
|
|
DateTime today = DateTime.Today;
|
|
|
|
// Check if the date is within the last 5 days (inclusive)
|
|
if ((today - passedDate).TotalDays <= 5 && passedDate <= today)
|
|
{
|
|
|
|
|
|
string fromDate = model.StartDate + " " + DateTime.Now.ToString(ConfigurationManager.AppSettings["startTimeFormat"]);
|
|
string toDate = model.EndDate + " " + DateTime.Now.ToString(ConfigurationManager.AppSettings["endTimeFormat"]);
|
|
model.FromDate = Convert.ToDateTime(Convert.ToDateTime(fromDate).ToString(_dateTimeFormat));
|
|
model.ToDate = Convert.ToDateTime(Convert.ToDateTime(toDate).ToString(_dateTimeFormat));
|
|
|
|
model.TicketType = "data,all";
|
|
|
|
oList = objTicket.ClosedConsolidatedDataReport(model);
|
|
|
|
|
|
|
|
// oList = ObjModel.ConsolidatedReportModelList;
|
|
|
|
string ticketCreationDateTime = "", ticketCreationTime = "";
|
|
foreach (var item in oList)
|
|
{
|
|
ticketCreationDateTime = item.creation_time;
|
|
string[] split_ticketCreationDateTime = ticketCreationDateTime.Split(' ');
|
|
ticketCreationTime = split_ticketCreationDateTime[3];
|
|
|
|
for (var count = 0; count < 12; count++)
|
|
{
|
|
if ((DateTime.Parse("01/01/0001 " + ticketCreationTime) >= DateTime.Parse("01/01/0001 " + (2 * count) + ":00:00")) && (DateTime.Parse("01/01/0001 " + ticketCreationTime) <= DateTime.Parse("01/01/0001 " + ((2 * count) + 1) + ":59:59")))
|
|
{
|
|
item.time_slot = (2 * count) + ":00 - " + ((2 * count) + 2) + ":00";
|
|
break;
|
|
}
|
|
}
|
|
item.ChassisNo = item.chassis_number;
|
|
}
|
|
|
|
}
|
|
else
|
|
{
|
|
|
|
}
|
|
}
|
|
catch (Exception Ex)
|
|
{
|
|
// write error log into file
|
|
objLog.ErrorLogFile("case mamagement api", Ex.Message, path, errorlogtf);
|
|
|
|
|
|
}
|
|
return oList;
|
|
}
|
|
|
|
|
|
public TicketRequestModel Post([FromUri] string TicketType, [FromUri] string ConsolidatedData, [FromBody] TicketRequestModel model)
|
|
{
|
|
// write data log into file
|
|
TicketRequestModel ObjModel = new TicketRequestModel();
|
|
try
|
|
{
|
|
TicketRepository objTicket = new TicketRepository(_connStr);
|
|
if (TicketType.ToLower() == "count")
|
|
{
|
|
string[] filterType = model.TicketType.ToString().Split(',');
|
|
int cout = filterType.Length;
|
|
|
|
if (cout == 2)
|
|
{
|
|
{
|
|
model.TicketType = TicketType;
|
|
string filterval = filterType[1];
|
|
model.TicketType = TicketType + "," + filterType[1];
|
|
}
|
|
}
|
|
else
|
|
{
|
|
model.TicketType = TicketType;
|
|
}
|
|
}
|
|
// model.TicketType = TicketType;
|
|
ObjModel = objTicket.ConsolidatedDataReportActualKMLatest(model);
|
|
|
|
}
|
|
catch (Exception Ex)
|
|
{
|
|
// write error log into file
|
|
objLog.ErrorLogFile("ConsolidatedDataReport", Ex.Message, path, errorlogtf);
|
|
throw Ex;
|
|
}
|
|
return ObjModel;
|
|
}
|
|
|
|
#endregion
|
|
}
|
|
} |