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.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 TicketController : 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

    /// <summary>
    /// CMS app login
    /// </summary>
    /// <param name="TicketId"></param>
    /// <param name="UserName"></param>
    /// <param name="password"></param>
    /// <returns></returns>
    public List<CustomerOpenTicketFullDetailTicketWise> Get([FromUri] string TicketId, [FromUri] string UserName, [FromUri] string password)
    {
      List<CustomerOpenTicketFullDetailTicketWise> obj = new List<CustomerOpenTicketFullDetailTicketWise>();
      VECV_WebApi.Models.User.UserModel objUserModel = new Models.User.UserModel();
      try
      {
        if (ModelState.IsValid)
        {
          UserClient.UserClient oUserClient = new UserClient.UserClient();
          var userDetail = oUserClient.validateLoginDetails(UserName.Trim(), password, _appName);

          if (userDetail.userId != Guid.Empty && userDetail.Role.Count() > 0)
          {
            TicketRepository objTicket = new TicketRepository(_connStr);
            obj = objTicket.GetCustomerOpenadnCloseTicketDetail(TicketId);

          }
          else
          {
            objLog.ErrorLogFile("Get Ticket Detail" + UserName + "Invalid User name Or pasword", DateTime.Now.ToString(ConfigurationManager.AppSettings["DateTimeFormat"]), path, logtf);
          }
        }
      }
      catch (Exception ex)
      {
        objLog.ErrorLogFile("GetCustomerDetails_Controller", ex.Message, path, errorlogtf);

      }
      return obj;
    }




    /// <summary>
    /// To get customer details
    /// </summary>
    /// <param name="model">customer info</param>
    /// <returns>customer details</returns>
    public List<CustomerVehicleModel> Post([FromBody] CustomerVehicleModel model)
    {
      // write data log into file
      List<CustomerVehicleModel> objListVehicle = new List<CustomerVehicleModel>();
      CustomerVehicleModel objCutomerVehicle = new CustomerVehicleModel();
      try
      {
        TicketRepository objTicket = new TicketRepository(_connStr);
        objListVehicle = objTicket.GetCustomerDetails(model);
        return objListVehicle;

      }
      catch (Exception Ex)
      {
        // write error log into file
        objLog.ErrorLogFile("GetCustomerDetails_Controller", Ex.Message, path, errorlogtf);

        objCutomerVehicle = new CustomerVehicleModel();
        objCutomerVehicle.Status = 0;
        objCutomerVehicle.Message = ConfigurationManager.AppSettings["PwdErrorMsg"].ToString() + Ex.Message;
        return objListVehicle;
      }
    }

    /// <summary>
    /// To get customer open ticket history
    /// </summary>
    /// <param name="TicketOpen">extra param to idenify the api</param>
    /// <param name="model">customer info</param>
    /// <returns>customer open ticket history</returns>
    public List<TicketOpenModel> Post([FromUri] string TicketOpen, [FromBody] CustomerVehicleModel model)
    {
      // write data log into file
      List<TicketOpenModel> objList = new List<TicketOpenModel>();
      try
      {
        TicketRepository objTicket = new TicketRepository(_connStr);
        objList = objTicket.GetCustomerOpenTicketHistory(model);

        return objList;
      }
      catch (Exception Ex)
      {
        // write  error log into file
        objLog.ErrorLogFile("GetCustomerOpenTicketHistory_Controller", Ex.Message, path, errorlogtf);
        throw Ex;
      }
    }

    /// <summary>
    /// To get closed/open ticket report.
    /// </summary>
    /// <param name="TicketType">extra param to idenify the api</param>
    /// <param name="model">ticket info</param>
    /// <returns>closed/open ticket report</returns>
    public TicketRequestModel Post([FromUri] string TicketType, [FromBody] TicketRequestModel model)

    {
      // write data log into file
      TicketRequestModel ObjModel = new TicketRequestModel();
      try
      {
        if (model.TicketType == null)
        {
          model.TicketType = TicketType;
        }
        else {
          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;
            }
          }
        }
        TicketRepository objTicket = new TicketRepository(_connStr);
        //model.TicketType = TicketType;
       
        ObjModel = objTicket.GetOpenCloseTicketDetail(model);

      }
      catch (Exception Ex)
      {
        // write error log into file
        objLog.ErrorLogFile("GetOpenCloseTicketDetail", Ex.Message, path, errorlogtf);
        throw Ex;
      }
      return ObjModel;
    }

    #region KAM related APIs

    /// <summary>
    /// To delete KAM dummy ticket
    /// </summary>
    /// <param name="TicketType">extra param to idenify the api</param>
    /// <param name="model">ticket info</param>
    /// <returns>closed/open ticket report</returns>
    public TicketRequestModel Post([FromUri] int deleteKamTicket, [FromBody] TicketRequestModel model)
    {
      // write data log into file
      TicketRequestModel ObjModel = new TicketRequestModel();
      try
      {
        TicketRepository objTicket = new TicketRepository(_connStr);
        ObjModel = objTicket.DeleteKamDummyTicket(model);
      }
      catch (Exception Ex)
      {
        // write error log into file
        objLog.ErrorLogFile("GetOpenCloseTicketDetail", Ex.Message, path, errorlogtf);
        throw Ex;
      }
      return ObjModel;
    }

    /// <summary>
    /// To get closed/open KAM ticket report.
    /// </summary>
    /// <param name="TicketType">extra param to idenify the api</param>
    /// <param name="model">ticket info</param>
    /// <returns>closed/open ticket report</returns>
    public TicketRequestModel Post([FromUri] int kam, [FromUri] string TicketType, [FromBody] TicketRequestModel model)
    {
      TicketRequestModel ObjModel = new TicketRequestModel();
      try
      {
        TicketRepository objTicket = new TicketRepository(_connStr);
        model.TicketType = TicketType;  // assigning ticket type to model
        ObjModel = objTicket.GetOpenCloseTicketDetailForKAM(model);
      }
      catch (Exception Ex)
      {
        throw Ex;
      }
      return ObjModel;
    }

    /// <summary>
    /// To get van and dealer list state wise for KAM User.
    /// </summary>
    /// <param name="kam">extra param to identify the api</param>
    /// <param name="ticket">extra param to identify the api</param>
    /// <param name="ticket1">extra param to identify the api</param>
    /// <param name="ticket2">extra param to identify the api</param>
    /// <param name="ticket3">extra param to identify the api</param>
    /// <param name="model">state info</param>
    /// <returns>van and dealer list state wise</returns>
    public List<VanDealerListStateWise> Post([FromUri] int kam, [FromUri] string ticket, [FromUri] string ticket1, [FromUri] string ticket2, [FromUri] string ticket3, [FromBody] VanDealerListStateWise model)
    {
      List<VanDealerListStateWise> objList = new List<VanDealerListStateWise>();
      try
      {
        TicketRepository objTicket = new TicketRepository(_connStr);
        objList = objTicket.GetVanDealerListStateWiseForKam(model);
        return objList;
      }
      catch (Exception Ex)
      {
        throw Ex;
      }
    }


    #endregion

    /// <summary>
    /// To get consolidated report.
    /// </summary>
    /// <param name="TicketType">type of data to get like(count or records)</param>
    /// <param name="ConsolidatedData">extra param to identify the api</param>
    /// <param name="model">ticket info</param>
    /// <returns>consolidated report data</returns>
    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.ConsolidatedDataReport(model);

      }
      catch (Exception Ex)
      {
        // write error log into file
        objLog.ErrorLogFile("ConsolidatedDataReport", Ex.Message, path, errorlogtf);
        throw Ex;
      }
      return ObjModel;
    }


    /// <summary>
    /// Method to get consolidated report (modified function) 07-10-2020
    /// </summary>
    /// <param name="TicketType"></param>
    /// <param name="ConsolidatedData"></param>
    /// <param name="isCount"></param>
    /// <param name="model"></param>
    /// <returns></returns>
    public string Post([FromUri] string TicketType, [FromUri] string ConsolidatedData, [FromUri] string isCount, [FromBody] TicketRequestModel model)
    {
      // write data log into file
      TicketRequestModel ObjModel = new TicketRequestModel();
      try
      {
        TicketRepository objTicket = new TicketRepository(_connStr);
        model.TicketType = TicketType;
        ObjModel = objTicket.ConsolidatedDataReport(model);
      }
      catch (Exception Ex)
      {
        // write error log into file
        objLog.ErrorLogFile("ConsolidatedDataReport", Ex.Message, path, errorlogtf);
        throw Ex;
      }
      string output = JsonConvert.SerializeObject(ObjModel);
      return output;
    }
    /// <summary>
    /// Method to get consolidated report (modified function) 07-10-2020
    /// </summary>
    /// <param name="TicketType"></param>
    /// <param name="ConsolidatedData"></param>
    /// <param name="isCount"></param>
    /// <param name="model"></param>
    /// <returns></returns>
    public TicketRequestModel Post([FromUri] string TicketType, [FromUri] string Download, [FromUri] string ConsolidatedData, [FromUri] string isCount, [FromBody] TicketRequestModel model)
    {
      // write data log into file
      TicketRequestModel ObjModel = new TicketRequestModel();
      try
      {
        TicketRepository objTicket = new TicketRepository(_connStr);
        model.TicketType = TicketType;
        ObjModel = objTicket.ConsolidatedDataReport(model);
      }
      catch (Exception Ex)
      {
        // write error log into file
        objLog.ErrorLogFile("ConsolidatedDataReport", Ex.Message, path, errorlogtf);
        throw Ex;
      }
      return ObjModel;
    }

    /// <summary>
    /// To get customer Close ticket history
    /// </summary>
    /// <param name="Ticket">extra param to idenify the api</param>
    /// <param name="TicketClose">extra param to idenify the api</param>
    /// <param name="model">customer info</param>
    /// <returns>customer Close ticket history</returns>
    public List<TicketCloseModel> Post([FromUri] string Ticket, [FromUri] string TicketClose, [FromBody] CustomerVehicleModel model)
    {
      // write data log into file
      List<TicketCloseModel> objList = new List<TicketCloseModel>();
      try
      {
        TicketRepository objTicket = new TicketRepository(_connStr);
        objList = objTicket.GetCustomerCloseTicketHistory(model);
        return objList;
      }
      catch (Exception Ex)
      {
        // write error log into file
        objLog.ErrorLogFile("GetCustomerCloseTicketHistory_Controller", Ex.Message, path, errorlogtf);
        throw Ex;
      }
    }

    /// <summary>
    /// To get customer all ticket history
    /// </summary>
    /// <param name="Ticket">extra param to idenify the api</param>
    /// <param name="Ticket1">extra param to idenify the api</param>
    /// <param name="Ticket2">extra param to idenify the api</param>
    /// <param name="model">customer info</param>
    /// <returns>customer all ticket history</returns>
    public List<TicketOpenModel> post([FromUri] string Ticket, [FromUri] string Ticket1, [FromUri] string Ticket2, [FromBody] CustomerVehicleModel model)
    {
      // write data log into file
      CustomerTicketHistory objCustomer = new CustomerTicketHistory();
      try
      {
        List<TicketOpenModel> objModel = new List<TicketOpenModel>();
        TicketRepository objTicket = new TicketRepository(_connStr);
        // Get customer close ticket
        objCustomer.ListCloseTicket = objTicket.GetCustomerCloseTicketHistory(model);
        //get customer open ticket
        objCustomer.ListOpenTicket = objTicket.GetCustomerOpenTicketHistory(model);
        // get customer open ticket history
        objModel = objTicket.GetCustomerOpenTicketHistory(model);

        foreach (TicketCloseModel obj in objCustomer.ListCloseTicket)
        {
          TicketOpenModel objOpenModel = new TicketOpenModel();
          objOpenModel.AssignedTo = obj.AssignedTo;
          objOpenModel.AssignedToUserId = obj.AssignedToUserId;
          objOpenModel.AssignedToUserLatitude = obj.AssignedToUserLatitude;
          objOpenModel.AssignedToUserLongitude = obj.AssignedToUserLongitude;
          objOpenModel.BreackDownLatitude = obj.BreackDownLatitude;
          objOpenModel.BreackDownLongitude = obj.BreackDownLongitude;
          objOpenModel.BreakDownLocation = obj.BreakDownLocation;
          objOpenModel.CreatedBy = obj.CreatedBy;
          objOpenModel.CreationTime = obj.CreationTime;
          objOpenModel.CutomerScore = obj.CutomerScore;
          objOpenModel.Description = obj.Description;
          objOpenModel.EstimatedTimeForJobComplition = obj.EstimatedTimeForJobComplition;
          objOpenModel.EstimatedTimeForJobComplitionSubmitTime = obj.EstimatedTimeForJobComplitionSubmitTime;
          objOpenModel.Id = obj.Id;
          objOpenModel.IsDeclined = obj.IsDeclined;
          objOpenModel.LastModifiedBy = obj.LastModifiedBy;
          objOpenModel.LastModifiedTime = obj.LastModifiedTime;
          objOpenModel.Message = obj.Message;
          objOpenModel.Priority = obj.Priority;
          objOpenModel.ReportedVia = obj.ReportedVia;
          objOpenModel.Status = obj.Status;
          objOpenModel.TicketStatus = obj.TicketStatus;
          objOpenModel.TotalTicketLifeCycleTimeSlab = obj.TotalTicketLifeCycleTimeSlab;
          objOpenModel.VehicleRegistrationNo = obj.VehicleRegistrationNo;


          objOpenModel.EstimatedTimeForJobCompletionSubmitTime = obj.EstimatedTimeForJobCompletionSubmitTime;
          objOpenModel.BreakdownLocationLandmark = obj.BreakdownLocationLandmark;
          objOpenModel.ActivityTicketsOpenCreatedBy = obj.ActivityTicketsOpenCreatedBy;
          objOpenModel.ActivityTicketsOpenCreationTime = obj.ActivityTicketsOpenCreationTime;
          objOpenModel.Remarks = obj.Remarks;
          objOpenModel.TicketActivityStatus = obj.TicketActivityStatus;
          objOpenModel.TicketStatusAlias = obj.TicketStatusAlias;
          objOpenModel.TicketStatusSequenceOrder = obj.TicketStatusSequenceOrder;
          objOpenModel.TicketStatusStatusName = obj.TicketStatusStatusName;
          objOpenModel.UserId = obj.UserId;
          objOpenModel.AuthEngineId = obj.AuthEngineId;
          objOpenModel.UserName = obj.UserName;
          objOpenModel.Designation = obj.Designation;
          objOpenModel.ObjectId = obj.ObjectId;
          objOpenModel.OrganizationId = obj.OrganizationId;
          objOpenModel.City = obj.City;
          objOpenModel.State = obj.State;
          objOpenModel.Region = obj.Region;
          objOpenModel.Language = obj.Language;
          objOpenModel.TimezoneId = obj.TimezoneId;
          objOpenModel.DisplayName = obj.DisplayName;
          objOpenModel.OffsetInMinutes = obj.OffsetInMinutes;
          objOpenModel.DisplayId = obj.DisplayId;
          objOpenModel.UserOrganizationName = obj.UserOrganizationName;
          objOpenModel.UserOrganizationAlias = obj.UserOrganizationAlias;
          objOpenModel.UserOrganizationCity = obj.UserOrganizationCity;
          objOpenModel.UserOrganizationState = obj.UserOrganizationState;
          objOpenModel.UserAsorganizationCountry = obj.UserAsorganizationCountry;
          objOpenModel.UserOrganizationDescription = obj.UserOrganizationDescription;
          objOpenModel.UserOrgPath = obj.UserOrgPath;
          objOpenModel.UserOrgDepth = obj.UserOrgDepth;
          objOpenModel.UserOrgColor = obj.UserOrgColor;
          objOpenModel.UserOrganizationRegion = obj.UserOrganizationRegion;
          objOpenModel.DealerId = obj.DealerId;
          objOpenModel.DealerOrganizationId = obj.DealerOrganizationId;
          objOpenModel.DealerDealerName = obj.DealerDealerName;
          objOpenModel.DealerCity = obj.DealerCity;
          objOpenModel.DealerState = obj.DealerState;
          objOpenModel.DealerDealerDefaultLattitude = obj.DealerDealerDefaultLattitude;
          objOpenModel.DealerDealerDefaultLongitude = obj.DealerDealerDefaultLongitude;
          objOpenModel.VanId = obj.VanId;
          objOpenModel.VanAlias = obj.VanAlias;
          objOpenModel.VanVanDefaultLattitude = obj.VanVanDefaultLattitude;
          objOpenModel.VanVanDefaultLongitude = obj.VanVanDefaultLongitude;
          objOpenModel.VanCity = obj.VanCity;
          objOpenModel.VanState = obj.VanState;
          objOpenModel.VanVanModel = obj.VanVanModel;
          objOpenModel.VanRegistrationNumber = obj.VanRegistrationNumber;
          objOpenModel.VanVanRegisteredAs = obj.VanVanRegisteredAs;
          objOpenModel.DealerVanOrganizationName = obj.DealerVanOrganizationName;
          objOpenModel.DealerVanOrganizationOrganizationAlias = obj.DealerVanOrganizationOrganizationAlias;
          objOpenModel.DealerVanOrganizationOrganizationDescription = obj.DealerVanOrganizationOrganizationDescription;
          objOpenModel.DealerVanOrganizationOrganizationPath = obj.DealerVanOrganizationOrganizationPath;
          objOpenModel.DealerVanOrganizationOrganizationDepth = obj.DealerVanOrganizationOrganizationDepth;
          objOpenModel.DealerVanOrganizationOrganizationColor = obj.DealerVanOrganizationOrganizationColor;
          objOpenModel.DealerVanOrganizationOrganizationRegion = obj.DealerVanOrganizationOrganizationRegion;
          objOpenModel.ServiceEngineerId = obj.ServiceEngineerId;
          objOpenModel.ServiceEngineerName = obj.ServiceEngineerName;
          objOpenModel.VanVanDefaultLattitude = obj.VanVanDefaultLattitude;
          objOpenModel.VanVanDefaultLongitude = obj.VanVanDefaultLongitude;
          objOpenModel.ServiceEngineerAlias = obj.ServiceEngineerAlias;
          objOpenModel.ServiceEngineerImeiNumber = obj.ServiceEngineerImeiNumber;
          objOpenModel.ServiceEngineerContactNumber = obj.ServiceEngineerContactNumber;
          objOpenModel.ServiceEngineerApplicationLicenseKey = obj.ServiceEngineerApplicationLicenseKey;
          objOpenModel.ServiceEngineerMobileApplicationBlocked = obj.ServiceEngineerMobileApplicationBlocked;
          objOpenModel.ServiceEngineerDeviceAlias = obj.ServiceEngineerDeviceAlias;
          objOpenModel.ServiceEngineerPinPassword = obj.ServiceEngineerPinPassword;

          objModel.Add(objOpenModel);
        }
        // objCustomer.Status = "1";
        return objModel;
      }
      catch (Exception Ex)
      {
        // write error log into file
        objLog.ErrorLogFile("GetCustomerOpenTicketHistory and GetCustomerCloseTicketHistory_Controller", Ex.Message, path, errorlogtf);
        throw Ex;
      }
    }

    /// <summary>
    /// To get van and dealer list state wise.
    /// </summary>
    /// <param name="ticket">extra param to identify the api</param>
    /// <param name="ticket1">extra param to identify the api</param>
    /// <param name="ticket2">extra param to identify the api</param>
    /// <param name="ticket3">extra param to identify the api</param>
    /// <param name="model">state info</param>
    /// <returns>van and dealer list state wise</returns>
    public List<VanDealerListStateWise> Post([FromUri] string ticket, [FromUri] string ticket1, [FromUri] string ticket2, [FromUri] string ticket3, [FromBody] VanDealerListStateWise model)
    {
      List<VanDealerListStateWise> objList = new List<VanDealerListStateWise>();
      try
      {
        TicketRepository objTicket = new TicketRepository(_connStr);
        objList = objTicket.GetVanDealerListStateWise(model);
        return objList;
      }
      catch (Exception Ex)
      {
        // write error log into file
        objLog.ErrorLogFile("GetVanDealerListStateWise_Controller Ticket controller ticket3", Ex.Message, path, errorlogtf);
        throw Ex;
      }
    }




    /// <summary>
    /// To get escalation level data
    /// </summary>
    /// <param name="operation">operation which is to be performed</param>
    /// <param name="model">escalation level input information</param>
    /// <returns>escalation level data and status</returns>
    public EscalationLevelOutputModel Post([FromUri] string operation, [FromBody] EscalationLevelInputModel model)
    {
      // write data log into file
      EscalationLevelOutputModel oEscalationLevelOutputModel = new EscalationLevelOutputModel();
      try
      {
        if (operation.ToLower() == "getescalationlevels")
        {
          TicketRepository objTicket = new TicketRepository(_connStr);
          oEscalationLevelOutputModel = objTicket.GetEscalationLevels(model);
          return oEscalationLevelOutputModel;
        }
        oEscalationLevelOutputModel.Status = "0";
        return oEscalationLevelOutputModel;
      }
      catch (Exception Ex)
      {
        // write  error log into file
        objLog.ErrorLogFile("GetEscalationLevels_Controller", Ex.Message, path, errorlogtf);
        throw Ex;
      }
    }

    /// <summary>
    /// To get open Dummy ticket report.
    /// </summary>
    /// <param name="TicketType">extra param to idenify the api</param>
    /// <param name="model">ticket info</param>
    /// <returns>open dummy ticket report</returns>
    public TicketRequestModel Post([FromUri] int dummyticket, [FromUri] string Ticket, [FromUri] string TicketType, [FromBody] TicketRequestModel model)
    {
      TicketRequestModel ObjModel = new TicketRequestModel();
      try
      {
        TicketRepository objTicket = new TicketRepository(_connStr);
        model.TicketType = TicketType;  // assigning ticket type to model
        ObjModel = objTicket.GetDummyOpenTicketDetailUserWise(model);
      }
      catch (Exception Ex)
      {
        throw Ex;
      }
      return ObjModel;
    }


    /// <summary>
    /// To create Dummy Ticket
    /// </summary>
    /// <param name="model">customer info</param>
    /// <returns>customer details</returns>
    public DraftTicketResponseModel Post([FromUri] string createDraftTicket, [FromUri] string createDummyTicket1, [FromUri] string createDummyTicket2, [FromBody] InsertOpenTicket model)
    {
      try
      {

        InsertOpenTicket objModel = new InsertOpenTicket();
        DraftTicketResponseModel objDraftTicketResponseModel = new DraftTicketResponseModel();
        objTicketRepository = new TicketRepository(_connStr);
        if (ModelState.IsValid && model.VehicleRegistrationNumber != null && model.VehicleRegistrationNumber != "")
        {
          model.CreationTime = DateTime.Now;
          model.LastModifiedTime = DateTime.Now;
          model.Token = "teramatrix";
          model.ReportedVia = "Mobile";

          objModel = objTicketRepository.InsertDraftTicket(model);
          objDraftTicketResponseModel.TicketId = objModel.TicketId;

          if (objModel.Status == "0")
          {
            objDraftTicketResponseModel.Message = "Ticket already created for this registration number";
          }

          else
          {
            objDraftTicketResponseModel.Message = "Ticket successfully Saved to Draft";

          }
          return objDraftTicketResponseModel;
        }

        objDraftTicketResponseModel.Message = "DriverContactNo number and VehicleRegistrationNumber can not be null";
        return objDraftTicketResponseModel;
      }
      catch (Exception Ex)
      {
        throw Ex;
      }
    }


    /// <summary>
    /// To get all draft tickets
    /// </summary>
    /// <param name="state">extra param to identify the api</param>
    /// <param name="model">extra param to identify the api</param>
    /// <returns>all states List</returns>
    public List<GetDummyTicket> post([FromUri] string dummy, [FromBody] GetDummyTicket model)
    {
      // write data log into file

      List<GetDummyTicket> objList = new List<GetDummyTicket>();
      try
      {
        objCustomerRepository = new CustomerRepository(_connStr);
        objList = objCustomerRepository.GetAllDummyTicketsByMobileList(model);
        return objList;
      }
      catch (Exception Ex)
      {
        // write error log into file
        objLog.ErrorLogFile("GetAllStateList_Controller", Ex.Message, path, errorlogtf);
        throw Ex;
      }
    }


    /// <summary>
    /// To create Dummy Ticket
    /// </summary>
    /// <param name="model">customer info</param>
    /// <returns>customer details</returns>
    public GetVanReachDetails Post([FromUri] string trackVanReachStatus, [FromBody] GetVanReachDetails model)
    {
      try
      {



        GetVanReachDetails objModel = new GetVanReachDetails();
        objTicketRepository = new TicketRepository(_connStr);
        model.Token = "teramatrix";
        objModel = objTicketRepository.getVanReachStatus(model);

        if (objModel.Message == null)
        {
          objModel.Message = "TicketId not found";

        }

        return objModel;

      }
      catch (Exception Ex)
      {
        throw Ex;
      }
    }

    //------- added on 19-01-2021 start ---------------------

    public List<DraftTicketDetailModel> post([FromUri] string dummyTicket, [FromUri] string dummyTicket1, [FromBody] DraftTicketDetailModel model)
    {
      // write data log into file

      List<DraftTicketDetailModel> objList = new List<DraftTicketDetailModel>();
      try
      {
        objTicketRepository = new TicketRepository(_connStr);
        objList = objTicketRepository.GetAllDraftTicketCancelResonList(model);
        return objList;
      }
      catch (Exception Ex)
      {
        // write error log into file
        objLog.ErrorLogFile("GetAllDummyTicketCancelReasonList_Controller", Ex.Message, path, errorlogtf);
        throw Ex;
      }
    }

    public DraftTicketDetailModel post([FromUri] string updateDraftTicketCancelReason, [FromUri] string updateDraftTicketCancelReason1, [FromUri] string updateDraftTicketCancelReason2, [FromBody] DraftTicketDetailModel model)
    {
      DraftTicketDetailModel oDraftTicketDetailModel = new DraftTicketDetailModel();
      try
      {
        objTicketRepository = new TicketRepository(_connStr);
        oDraftTicketDetailModel = objTicketRepository.SaveDraftTicketCancelReson(model);
        return oDraftTicketDetailModel;
      }
      catch (Exception Ex)
      {
        // write error log into file
        objLog.ErrorLogFile("GetAllDummyTicketCancelReasonList_Controller", Ex.Message, path, errorlogtf);
        throw Ex;
      }


    }

    //------------ added on 19-01-2021 end ------------------------

    //------------ added on 09-03-2021 start ------------------------

    public List<CancelledDraftTicketModel> post([FromUri] string cancelDummyTickets, [FromUri] string cancelDummyTickets1, [FromBody] CancelledDraftTicketModel model)
    {
      // write data log into file

      List<CancelledDraftTicketModel> objList = new List<CancelledDraftTicketModel>();
      try
      {
        objCustomerRepository = new CustomerRepository(_connStr);
        objList = objCustomerRepository.GetAllCancelledDummyTicketsByMobileList(model);
        return objList;
      }
      catch (Exception Ex)
      {
        // write error log into file
        objLog.ErrorLogFile("GetAllCancelledDummyTicketList", Ex.Message, path, errorlogtf);
        throw Ex;
      }
    }


    public CancelledDraftTicketReportModel Post([FromUri] string dummycancelledData, [FromUri] string dummycancelledData1, [FromUri] string dummycancelledData2, [FromBody] CancelledDraftTicketReportModel model)
    {
      // write data log into file
      CancelledDraftTicketReportModel ObjModel = new CancelledDraftTicketReportModel();
      try
      {
        TicketRepository objTicket = new TicketRepository(_connStr);
        ObjModel = objTicket.DummyCancelledDataReport(model);
      }
      catch (Exception Ex)
      {
        // write error log into file
        objLog.ErrorLogFile("ConsolidatedDataReport", Ex.Message, path, errorlogtf);
        throw Ex;
      }

      return ObjModel;
    }

    // Added By priya for consolidate report

    /// <summary>
    /// Method to get consolidated report (modified function) 07-10-2020
    /// </summary>
    /// <param name="TicketType"></param>
    /// <param name="ConsolidatedData"></param>
    /// <param name="isCount"></param>
    /// <param name="model"></param>
    /// <returns></returns>
    public string Post([FromUri] string TicketType, [FromUri] string ConsolidatedData, [FromUri] string isCount, [FromUri] string isajax, [FromUri] string isajaxcount, [FromBody] TicketRequestModel model)
    {
      // write data log into file
      TicketRequestModel ObjModel = new TicketRequestModel();
      try
      {
        TicketRepository objTicket = new TicketRepository(_connStr);
        model.TicketType = TicketType;
        ObjModel = objTicket.ConsolidatedDataReport(model);
        // var t = client.Execute<List<string>>(ObjModel);

        // var outputcopunt = JsonConvert.DeserializeObject<dynamic>(ObjModel[0]);
        return ObjModel.TicketCount.ToString();
      }
      catch (Exception Ex)
      {
        // write error log into file
        objLog.ErrorLogFile("ConsolidatedDataReport", Ex.Message, path, errorlogtf);
        throw Ex;
      }
      //string output = JsonConvert.SerializeObject(ObjModel);
      //return output;
    }
    public string Post([FromUri] string TicketType, [FromUri] string Download, [FromUri] string ConsolidatedData, [FromUri] string isCount, [FromUri] string isajaxcount, [FromUri] string isajaxdatacount, [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;
        string startdate = model.StartDate + " " + DateTime.Now.ToString(ConfigurationManager.AppSettings["startTimeFormat"]);
        string enddate = model.EndDate + " " + DateTime.Now.ToString(ConfigurationManager.AppSettings["endTimeFormat"]);
        string _dateTimeFormat = ConfigurationManager.AppSettings["dateTimeFormat"].ToString();
        model.FromDate = Convert.ToDateTime(startdate);
        model.ToDate = Convert.ToDateTime(enddate);

        ReportOpenTicketModel oConsolidatedReportList = objTicket.ConsolidatedDataReportNew(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;

    }


    public string Post([FromUri] string TicketType, [FromUri] string Download, [FromUri] string ConsolidatedData, [FromUri] string isCount, [FromUri] string isajaxcount, [FromUri] string isajaxdatacount, [FromUri] string isticketopen, [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);
        //del.TicketType = TicketType;
        string startdate = model.StartDate + " " + DateTime.Now.ToString(ConfigurationManager.AppSettings["startTimeFormat"]);
        string enddate = model.EndDate + " " + DateTime.Now.ToString(ConfigurationManager.AppSettings["endTimeFormat"]);
        string _dateTimeFormat = ConfigurationManager.AppSettings["dateTimeFormat"].ToString();
        model.FromDate = Convert.ToDateTime(startdate);
        model.ToDate = Convert.ToDateTime(enddate);
        //  ObjModel = objTicket.ConsolidatedDataReport(model);

        //List<ConsolidatedReportModel> oList = new List<ConsolidatedReportModel>();

        // oList = ObjModel.ConsolidatedReportModelList;

        ReportOpenTicketModel oConsolidatedReportList = objTicket.GetOpenCloseTicketDetailNew(model);

        List<TicketListOpenTickets> oList = new List<TicketListOpenTickets>();

        oList = oConsolidatedReportList.TicketopenList;

        string ticketCreationDateTime = "", ticketCreationTime = "";
        foreach (var item in oList)
        {
          ticketCreationDateTime = item.PreClosureTime;
          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")))
            if (ticketCreationDateTime == "01 Jan 0001 05:30:00")
            {
              item.PreClosureTime = "";// (2 * count) + ":00 - " + ((2 * count) + 2) + ":00";
              break;
            }
          }
          // item.ChassisNo = item.chassis_number;
        }

        DataTable OpenTicketTable = oList.ToDataTable();
        Int32 OpenTicketTableColumnCount = OpenTicketTable.Columns.Count - 1;


        DataSet ds = new DataSet();
        ds.Tables.Add(OpenTicketTable);

        ds.Tables[0].Columns[0].ColumnName = "Ticket Id";
        ds.Tables[0].Columns[1].ColumnName = "Created BY";
        ds.Tables[0].Columns[2].ColumnName = "Ticket Status";
        ds.Tables[0].Columns[3].ColumnName = "Ticket Creation Time";
        ds.Tables[0].Columns[4].ColumnName = "Language";
        ds.Tables[0].Columns[5].ColumnName = "Nature of problem";
        ds.Tables[0].Columns[6].ColumnName = "Vehicle Registration No.";
        ds.Tables[0].Columns[7].ColumnName = "Chassis Number";
        ds.Tables[0].Columns[8].ColumnName = "Vehicle Type";
        ds.Tables[0].Columns[9].ColumnName = "Vehicle Emission Norms";
        ds.Tables[0].Columns[10].ColumnName = "Device Status";
        ds.Tables[0].Columns[11].ColumnName = "Odometer Reading";
        ds.Tables[0].Columns[12].ColumnName = "Customer Name";
        ds.Tables[0].Columns[13].ColumnName = "Customer Contact No.";
        ds.Tables[0].Columns[14].ColumnName = "region";
        ds.Tables[0].Columns[15].ColumnName = "dealer 1scode";
        ds.Tables[0].Columns[16].ColumnName = "Dealer Name";
        ds.Tables[0].Columns[17].ColumnName = "Dealer Contact No.";
        ds.Tables[0].Columns[18].ColumnName = "EOS Team Member/Van Reg No.";
        ds.Tables[0].Columns[19].ColumnName = "CSM Name";
        ds.Tables[0].Columns[20].ColumnName = "Csm Contact No";
        ds.Tables[0].Columns[21].ColumnName = "ASM Name";
        ds.Tables[0].Columns[22].ColumnName = "Asm Contact No";
        ds.Tables[0].Columns[23].ColumnName = "RSM Name";
        ds.Tables[0].Columns[24].ColumnName = "RSM Contact No";
        ds.Tables[0].Columns[25].ColumnName = "remarks";
        ds.Tables[0].Columns[25].ColumnName = "Default SLA Time (In Min)";
        ds.Tables[0].Columns[27].ColumnName = "Response(In min)";
        ds.Tables[0].Columns[28].ColumnName = "Pre Closure Time";
        ds.Tables[0].Columns[29].ColumnName = "Van Reach By Call Center";  //added on 19-01-2021
        ds.Tables[0].Columns[30].ColumnName = "Assigned To";
        ds.Tables[0].Columns[31].ColumnName = "Fuel Type";
        ds.Tables[0].Columns[32].ColumnName = "Vehicle Model Number";
        ds.Tables[0].Columns[33].ColumnName = "Battery Voltage";
        ds.Tables[0].Columns[34].ColumnName = "Fuel Level (In L)";
        ds.Tables[0].Columns[35].ColumnName = "DEF Level (In L)";
        ds.Tables[0].Columns[36].ColumnName = "Pre Closure Reason";
        //  ds.Tables[0].Columns[30].ColumnName = "";
        ExcelUtility objExcelUtility = new ExcelHelper.ExcelUtility();
        List<WorkbookMappingModel> workbokkMapping = new List<WorkbookMappingModel>();

        string _excelExportPathOnServer = ConfigurationManager.AppSettings["excelExportPathOnServer"].ToString();
        fileName = "OpenTicket_" + DateTime.Now.ToString("ddMMMyyyyhhmmss") + ".xlsx";

        //  filename = "CloseTicket_" + DateTime.Now.ToString("ddMMMyyyyhhmmss") + ".xlsx";
        workbokkMapping.Add(new WorkbookMappingModel { DataTableName = ds.Tables[0].TableName, WorkSheetName = "Open Report", StartColumnName = "A", StartRowNumber = 2, WorkSheetNumber = 1, AutoFit = false });
        string templatePath = HttpContext.Current.Server.MapPath(ConfigurationManager.AppSettings["OpenTicketReportTemplate"].ToString());
        string _exportLocation = ConfigurationManager.AppSettings["excelExportPath"].ToString();

        string pathToExcelFile = _exportLocation + fileName;

        //string saveAs = Server.MapPath(_excelExportPathOnServer + fileName);

        string SaveCsvAs = HttpContext.Current.Server.MapPath(_excelExportPathOnServer + fileName);


        //commented on 24-07-2020(excel download same as consolidate report)
        isCreated = CreateExcelFile.CreateExcelDocument(ds, SaveCsvAs);
        if (isCreated == true) { return _exportLocation + fileName; }
        else { return "error"; }



      }
      catch (Exception Ex)
      {
        // write error log into file
        objLog.ErrorLogFile("ConsolidatedDataReport", Ex.Message, path, errorlogtf);
        throw Ex;
      }
      //  return _excelExportPath + fileName;

    }

    public string Post([FromUri] string TicketType, [FromUri] string Download, [FromUri] string ConsolidatedData, [FromUri] string isCount, [FromUri] string isajaxcount, [FromUri] string isajaxdatacount, [FromUri] string isticketclose, [FromUri] string closereport, [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;
        string startdate = model.StartDate + " " + DateTime.Now.ToString(ConfigurationManager.AppSettings["startTimeFormat"]);
        string enddate = model.EndDate + " " + DateTime.Now.ToString(ConfigurationManager.AppSettings["endTimeFormat"]);
        string _dateTimeFormat = ConfigurationManager.AppSettings["dateTimeFormat"].ToString();
        model.FromDate = Convert.ToDateTime(startdate);
        model.ToDate = Convert.ToDateTime(enddate);


        ReportOpenTicketModel oConsolidatedReportList = objTicket.GetReportCloseTicketDetailNew(model);

        List<TicketListCloseTickets> oList = new List<TicketListCloseTickets>();

        oList = oConsolidatedReportList.TicketcloseList;

        DataTable OpenTicketTable = oList.ToDataTable();
        Int32 OpenTicketTableColumnCount = OpenTicketTable.Columns.Count - 1;

        for (var count = OpenTicketTableColumnCount; count >= 42; count--) //modified count on 25-11-2020  //modified count from 34 to 35 on 23-12-2020
        {
          OpenTicketTable.Columns.RemoveAt(count);
        }
        OpenTicketTable.Columns.RemoveAt(17);
        OpenTicketTable.Columns.RemoveAt(2);
        DataSet ds = new DataSet();
        ds.Tables.Add(OpenTicketTable);

        //----------------------------------- *** changed code ***--------------------------------------
        ds.Tables[0].Columns[0].ColumnName = "Ticket Id";
        ds.Tables[0].Columns[1].ColumnName = "Ticket Status";
        ds.Tables[0].Columns[2].ColumnName = "Customer Name";
        ds.Tables[0].Columns[3].ColumnName = "Customer Contact No.";
        ds.Tables[0].Columns[4].ColumnName = "Vehicle Registration No.";
        ds.Tables[0].Columns[5].ColumnName = "Vehicle Type";
        ds.Tables[0].Columns[6].ColumnName = "Default SLA Time (In Min)";
        ds.Tables[0].Columns[7].ColumnName = "Response(In min)";
        ds.Tables[0].Columns[8].ColumnName = "EOS Team Member/Van Reg No.";
        ds.Tables[0].Columns[9].ColumnName = "Dealer Name";
        ds.Tables[0].Columns[10].ColumnName = "Dealer Contact No.";
        ds.Tables[0].Columns[11].ColumnName = "Language";
        ds.Tables[0].Columns[12].ColumnName = "Pre Closure Time";
        ds.Tables[0].Columns[16].ColumnName = "Chassis No";
        ds.Tables[0].Columns[17].ColumnName = "Odometer Reading";
        ds.Tables[0].Columns[18].ColumnName = "Van Reach By Call Center";     //added on 19-01-2021


        // added van/breakdown lat lng
        ds.Tables[0].Columns[19].ColumnName = "Van Latitude At Trip Start";
        ds.Tables[0].Columns[20].ColumnName = "Van Longitude At Trip Start";
        ds.Tables[0].Columns[21].ColumnName = "Van Latitude At Van Reached";
        ds.Tables[0].Columns[22].ColumnName = "Van Longitude At Van Reached";
        ds.Tables[0].Columns[23].ColumnName = "Van Latitude At Trip End";
        ds.Tables[0].Columns[24].ColumnName = "Van Longitude At Trip End";
        ds.Tables[0].Columns[25].ColumnName = "Breakdown Location Latitude";
        ds.Tables[0].Columns[26].ColumnName = "Breakdown Location Longitude";

        //------------------- added on 23-12-2020 start----------------------------------                            
        ds.Tables[0].Columns[27].ColumnName = "Vehicle Warranty";
        ds.Tables[0].Columns[28].ColumnName = "Vehicle Amc";
        ds.Tables[0].Columns[29].ColumnName = "Vehicle Emission Norms";
        ds.Tables[0].Columns[30].ColumnName = "Vehicle Sales Date";
        ds.Tables[0].Columns[31].ColumnName = "Ticket Closed Reason";       //change the column name on 25-11-2020
                                                                            // ds.Tables[0].Columns[33].ColumnName = "Van Lattitude At Ticket Assignment";       //change the column name on 25-11-2020

        //ds.Tables[0].Columns[34].ColumnName = "Van Longitude At Ticket Assignment";       //change the column name on 25-11-2020

        ds.Tables[0].Columns[33].ColumnName = "Estimated Van Reach Time";       //change the column name on 25-11-2020
        ds.Tables[0].Columns[34].ColumnName = "Fuel Type";
        ds.Tables[0].Columns[35].ColumnName = "Vehicle Model Number";
        ds.Tables[0].Columns[36].ColumnName = "Battery Voltage";
        ds.Tables[0].Columns[37].ColumnName = "Fuel Level (In L)";
        ds.Tables[0].Columns[38].ColumnName = "DEF Level (In L)";
        ds.Tables[0].Columns[39].ColumnName = "Trip End By";
        ds.Tables[0].Columns.RemoveAt(1);
        //  ds.Tables[0].Columns.RemoveAt(33);
        // ds.Tables[0].Columns.RemoveAt(34);

        //------------------- added on 23-12-2020 end----------------------------------

        //----------------------------------- *** changed code ***--------------------------------------
        ExcelUtility objExcelUtility = new ExcelHelper.ExcelUtility();
        List<WorkbookMappingModel> workbokkMapping = new List<WorkbookMappingModel>();

        string _excelExportPathOnServer = ConfigurationManager.AppSettings["excelExportPathOnServer"].ToString();
        //  fileName = "OpenTicket_" + DateTime.Now.ToString("ddMMMyyyyhhmmss") + ".xlsx";
        fileName = "CloseTicket_" + DateTime.Now.ToString("ddMMMyyyyhhmmss") + ".xlsx";

        //  filename = "CloseTicket_" + DateTime.Now.ToString("ddMMMyyyyhhmmss") + ".xlsx";
        workbokkMapping.Add(new WorkbookMappingModel { DataTableName = ds.Tables[0].TableName, WorkSheetName = "Close Ticket", StartColumnName = "A", StartRowNumber = 2, WorkSheetNumber = 1, AutoFit = false });
        string templatePath = HttpContext.Current.Server.MapPath(ConfigurationManager.AppSettings["CloseTicketReportTemplate"].ToString());
        string _exportLocation = ConfigurationManager.AppSettings["excelExportPath"].ToString();

        string pathToExcelFile = _exportLocation + fileName;

        //string saveAs = Server.MapPath(_excelExportPathOnServer + fileName);

        string SaveCsvAs = HttpContext.Current.Server.MapPath(_excelExportPathOnServer + fileName);


        //commented on 24-07-2020(excel download same as consolidate report)
        isCreated = CreateExcelFile.CreateExcelDocument(ds, SaveCsvAs);
        if (isCreated == true) { return _exportLocation + fileName; }
        else { return "error"; }


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

    }


    public string Post([FromUri] string VECVReport, [FromUri] string VECVUser, [FromUri] string isdealer, [FromUri] string TicketType, [FromUri] string dealer, [FromUri] string isajaxcount, [FromUri] string Download, [FromUri] string isCount, [FromUri] string isticketclose, [FromBody] VECVReportRequestModel model)
    {   // write data log into file

      // write data log into file
      VECVReportRequestModel ObjModel = new VECVReportRequestModel();
      try
      {
        string startdate = model.StartDate + " " + DateTime.Now.ToString(ConfigurationManager.AppSettings["startTimeFormat"]);
        string enddate = model.EndDate + " " + DateTime.Now.ToString(ConfigurationManager.AppSettings["endTimeFormat"]);
        string _dateTimeFormat = ConfigurationManager.AppSettings["dateTimeFormat"].ToString();
        model.FromDate = Convert.ToDateTime(startdate);
        model.ToDate = Convert.ToDateTime(enddate);
        TicketRepository objTicket = new TicketRepository(_connStr);      // model.TicketType = TicketType;
        ObjModel = objTicket.GetTicketDetailVECVCalculation(model);

        List<VECVCalculation> oList = new List<VECVCalculation>();
        string fileName = null;
        bool isCreated = false;
        oList = ObjModel.TicketList;


        string ticketCreationDateTime = "", ticketCreationTime = "", lastmodifieddatetime = "", lastmodifiedtime = "", vanReachDateTime = "", vanReachTime = "", ticketPrecloseDateTime = "", ticketPrecloseTime = "", ticketCallCloseDateTime = "", ticketCallCloseTime = "", ticketAcceptedDateTime = "", ticketAcceptedTime = "", ticketReassignmentDateTime = "", ticketReassignmentTime = "";
        foreach (var item in oList)
        {
          ticketCreationDateTime = item.CreationTime;
          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")))
            if (ticketCreationDateTime == "01 Jan 0001 00:00:00")
            {
              item.CreationTime = "";// (2 * count) + ":00 - " + ((2 * count) + 2) + ":00";
              break;
            }
          }

          lastmodifieddatetime = item.LastModifiedTime;
          string[] split_lastmodifieddatetime = lastmodifieddatetime.Split(' ');
          lastmodifiedtime = split_lastmodifieddatetime[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")))
            if (lastmodifieddatetime == "01 Jan 0001 00:00:00")
            {
              item.LastModifiedTime = "";// (2 * count) + ":00 - " + ((2 * count) + 2) + ":00";
              break;
            }
          }

          //  vanReachDateTime = "", vanReachTime = "",
          vanReachDateTime = item.VanReachedTime;

          string[] split_ticketvanReachTime = vanReachDateTime.Split(' ');
          vanReachTime = split_ticketvanReachTime[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")))
            if (vanReachDateTime == "01 Jan 0001 00:00:00")
            {
              item.VanReachedTime = "";// (2 * count) + ":00 - " + ((2 * count) + 2) + ":00";
              break;
            }
          }


          //if (item.VanReachedTime != null)
          //{

          //    vanReachDateTime = item.VanReachedTime;
          //    string[] split_ticketvanReachedTime = vanReachDateTime.Split(' ');
          //    vanReachTime = split_ticketvanReachedTime[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")))
          //        if (vanReachDateTime == "01 Jan 0001 00:00:00")
          //        {
          //            item.VanReachedTime = "";// (2 * count) + ":00 - " + ((2 * count) + 2) + ":00";
          //            break;
          //        }
          //    }
          //}

          vanReachDateTime = item.Van_Reached;

          string[] split_ticketvanReachedTime = vanReachDateTime.Split(' ');
          vanReachTime = split_ticketvanReachedTime[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")))
            if (vanReachDateTime == "01 Jan 0001 00:00:00")
            {
              item.Van_Reached = "";// (2 * count) + ":00 - " + ((2 * count) + 2) + ":00";
              break;
            }
          }
          ticketPrecloseDateTime = item.Call_Preclosed;
          string[] split_ticketticketPrecloseDateTime = ticketPrecloseDateTime.Split(' ');
          ticketPrecloseTime = split_ticketticketPrecloseDateTime[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")))
            if (ticketPrecloseDateTime == "01 Jan 0001 00:00:00")
            {
              item.Call_Preclosed = "";// (2 * count) + ":00 - " + ((2 * count) + 2) + ":00";
              break;
            }
          }

          ticketCallCloseDateTime = item.Call_Closure;
          string[] split_ticketCallCloseDateTime = ticketCallCloseDateTime.Split(' ');
          ticketCallCloseTime = split_ticketCallCloseDateTime[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")))
            if (ticketCallCloseDateTime == "01 Jan 0001 00:00:00")
            {
              item.Call_Closure = "";// (2 * count) + ":00 - " + ((2 * count) + 2) + ":00";
              break;
            }
          }



          ticketReassignmentDateTime = item.Reassignment_Time;
          string[] split_ticketReassignmentDateTime = ticketReassignmentDateTime.Split(' ');
          ticketReassignmentTime = split_ticketReassignmentDateTime[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")))
            if (ticketReassignmentDateTime == "01 Jan 0001 00:00:00")
            {
              item.Reassignment_Time = "";// (2 * count) + ":00 - " + ((2 * count) + 2) + ":00";
              break;
            }
          }

          ticketAcceptedDateTime = item.Ticket_Accepted;
          string[] split_ticketAcceptedDateTime = ticketAcceptedDateTime.Split(' ');
          ticketAcceptedTime = split_ticketAcceptedDateTime[3];

          for (var count = 0; count < 12; count++)
          {
            //01 Jan 0001 00:00:00
            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")))
              if (ticketAcceptedDateTime == "01 Jan 0001 00:00:00")
              {
                item.Ticket_Accepted = "";// (2 * count) + ":00 - " + ((2 * count) + 2) + ":00";
                break;
              }
          }
          // item.ChassisNo = item.chassis_number;
        }
        DataTable OpenTicketTable = oList.ToDataTable();
        Int32 OpenTicketTableColumnCount = OpenTicketTable.Columns.Count - 1;


        DataSet ds = new DataSet();
        ds.Tables.Add(OpenTicketTable);
        ds.Tables[0].Columns[0].ColumnName = "Ticket Id";
        ds.Tables[0].Columns[1].ColumnName = "AssignedTo";
        ds.Tables[0].Columns[3].ColumnName = "Ticket Status";
        ds.Tables[0].Columns[4].ColumnName = "Ticket Creation Time";
        ds.Tables[0].Columns[5].ColumnName = "Last Modified Time";
        ds.Tables[0].Columns[6].ColumnName = "Assigned to User Id";
        ds.Tables[0].Columns[7].ColumnName = "Van Reached Time";
        ds.Tables[0].Columns[8].ColumnName = "Ticket Accepted";
        ds.Tables[0].Columns[9].ColumnName = "Call Preclosed";
        ds.Tables[0].Columns[10].ColumnName = "Call Closure";
        ds.Tables[0].Columns[11].ColumnName = "Reassignment Time";
        ds.Tables[0].Columns[12].ColumnName = "Dealer Code";
        ds.Tables[0].Columns[13].ColumnName = "Dealer Name";
        ds.Tables[0].Columns.RemoveAt(2);
        ds.Tables[0].Columns.RemoveAt(6);
        // ds.Tables[0].Columns.RemoveAt(12);
        //  ds.Tables[0].Columns[30].ColumnName = "";
        ExcelUtility objExcelUtility = new ExcelHelper.ExcelUtility();
        List<WorkbookMappingModel> workbokkMapping = new List<WorkbookMappingModel>();

        string _excelExportPathOnServer = ConfigurationManager.AppSettings["excelExportPathOnServer"].ToString();
        fileName = "VECVReport_" + DateTime.Now.ToString("ddMMMyyyyhhmmss") + ".xlsx";

        //  filename = "CloseTicket_" + DateTime.Now.ToString("ddMMMyyyyhhmmss") + ".xlsx";
        workbokkMapping.Add(new WorkbookMappingModel { DataTableName = ds.Tables[0].TableName, WorkSheetName = "Open Report", StartColumnName = "A", StartRowNumber = 2, WorkSheetNumber = 1, AutoFit = false });
        string templatePath = HttpContext.Current.Server.MapPath(ConfigurationManager.AppSettings["OpenTicketReportTemplate"].ToString());
        string _exportLocation = ConfigurationManager.AppSettings["excelExportPath"].ToString();

        string pathToExcelFile = _exportLocation + fileName;

        //string saveAs = Server.MapPath(_excelExportPathOnServer + fileName);

        string SaveCsvAs = HttpContext.Current.Server.MapPath(_excelExportPathOnServer + fileName);


        //commented on 24-07-2020(excel download same as consolidate report)
        isCreated = CreateExcelFile.CreateExcelDocument(ds, SaveCsvAs);
        if (isCreated == true) { return _exportLocation + fileName; }
        else { return "error"; }

      }
      catch (Exception Ex)
      {
        // write error log into file
        objLog.ErrorLogFile("GetOpenCloseTicketDetail", Ex.Message, path, errorlogtf);
        throw Ex;
      }
      // return ObjModel;
      //  objList = objDealerRepository.GetDealerOpenTicketDetail(model);


    }


    public string Post([FromUri] string paymentReport, [FromUri] string User, [FromUri] string isdealer, [FromUri] string TicketType, [FromUri] string dealer, [FromUri] string isajaxcount, [FromUri] string Download, [FromUri] string isCount, [FromUri] string isticketclose, [FromUri] string paymentdetailall, [FromBody] VECVReportRequestModel model)
    {   // write data log into file

      // write data log into file
      VECVReportRequestModel ObjModel = new VECVReportRequestModel();
      try
      {
        string startdate = model.StartDate + " " + DateTime.Now.ToString(ConfigurationManager.AppSettings["startTimeFormat"]);
        string enddate = model.EndDate + " " + DateTime.Now.ToString(ConfigurationManager.AppSettings["endTimeFormat"]);
        string _dateTimeFormat = ConfigurationManager.AppSettings["dateTimeFormat"].ToString();
        model.FromDate = Convert.ToDateTime(startdate);
        model.ToDate = Convert.ToDateTime(enddate);
        TicketRepository objTicket = new TicketRepository(_connStr);      // model.TicketType = TicketType;
        ObjModel = objTicket.GetTicketDetailVECVCalculation(model);

        List<VECVCalculation> oList = new List<VECVCalculation>();
        string fileName = null;
        bool isCreated = false;
        oList = ObjModel.TicketList;


        string ticketCreationDateTime = "", ticketCreationTime = "", lastmodifieddatetime = "", lastmodifiedtime = "", vanReachDateTime = "", vanReachTime = "", ticketPrecloseDateTime = "", ticketPrecloseTime = "", ticketCallCloseDateTime = "", ticketCallCloseTime = "", ticketAcceptedDateTime = "", ticketAcceptedTime = "", ticketReassignmentDateTime = "", ticketReassignmentTime = "";
        foreach (var item in oList)
        {
          ticketCreationDateTime = item.CreationTime;
          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")))
            if (ticketCreationDateTime == "01 Jan 0001 00:00:00")
            {
              item.CreationTime = "";// (2 * count) + ":00 - " + ((2 * count) + 2) + ":00";
              break;
            }
          }

          lastmodifieddatetime = item.LastModifiedTime;
          string[] split_lastmodifieddatetime = lastmodifieddatetime.Split(' ');
          lastmodifiedtime = split_lastmodifieddatetime[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")))
            if (lastmodifieddatetime == "01 Jan 0001 00:00:00")
            {
              item.LastModifiedTime = "";// (2 * count) + ":00 - " + ((2 * count) + 2) + ":00";
              break;
            }
          }

          //  vanReachDateTime = "", vanReachTime = "",
          vanReachDateTime = item.VanReachedTime;

          string[] split_ticketvanReachTime = vanReachDateTime.Split(' ');
          vanReachTime = split_ticketvanReachTime[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")))
            if (vanReachDateTime == "01 Jan 0001 00:00:00")
            {
              item.VanReachedTime = "";// (2 * count) + ":00 - " + ((2 * count) + 2) + ":00";
              break;
            }
          }


          //if (item.VanReachedTime != null)
          //{

          //    vanReachDateTime = item.VanReachedTime;
          //    string[] split_ticketvanReachedTime = vanReachDateTime.Split(' ');
          //    vanReachTime = split_ticketvanReachedTime[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")))
          //        if (vanReachDateTime == "01 Jan 0001 00:00:00")
          //        {
          //            item.VanReachedTime = "";// (2 * count) + ":00 - " + ((2 * count) + 2) + ":00";
          //            break;
          //        }
          //    }
          //}

          vanReachDateTime = item.Van_Reached;

          string[] split_ticketvanReachedTime = vanReachDateTime.Split(' ');
          vanReachTime = split_ticketvanReachedTime[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")))
            if (vanReachDateTime == "01 Jan 0001 00:00:00")
            {
              item.Van_Reached = "";// (2 * count) + ":00 - " + ((2 * count) + 2) + ":00";
              break;
            }
          }
          ticketPrecloseDateTime = item.Call_Preclosed;
          string[] split_ticketticketPrecloseDateTime = ticketPrecloseDateTime.Split(' ');
          ticketPrecloseTime = split_ticketticketPrecloseDateTime[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")))
            if (ticketPrecloseDateTime == "01 Jan 0001 00:00:00")
            {
              item.Call_Preclosed = "";// (2 * count) + ":00 - " + ((2 * count) + 2) + ":00";
              break;
            }
          }

          ticketCallCloseDateTime = item.Call_Closure;
          string[] split_ticketCallCloseDateTime = ticketCallCloseDateTime.Split(' ');
          ticketCallCloseTime = split_ticketCallCloseDateTime[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")))
            if (ticketCallCloseDateTime == "01 Jan 0001 00:00:00")
            {
              item.Call_Closure = "";// (2 * count) + ":00 - " + ((2 * count) + 2) + ":00";
              break;
            }
          }



          ticketReassignmentDateTime = item.Reassignment_Time;
          string[] split_ticketReassignmentDateTime = ticketReassignmentDateTime.Split(' ');
          ticketReassignmentTime = split_ticketReassignmentDateTime[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")))
            if (ticketReassignmentDateTime == "01 Jan 0001 00:00:00")
            {
              item.Reassignment_Time = "";// (2 * count) + ":00 - " + ((2 * count) + 2) + ":00";
              break;
            }
          }

          ticketAcceptedDateTime = item.Ticket_Accepted;
          string[] split_ticketAcceptedDateTime = ticketAcceptedDateTime.Split(' ');
          ticketAcceptedTime = split_ticketAcceptedDateTime[3];

          for (var count = 0; count < 12; count++)
          {
            //01 Jan 0001 00:00:00
            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")))
              if (ticketAcceptedDateTime == "01 Jan 0001 00:00:00")
              {
                item.Ticket_Accepted = "";// (2 * count) + ":00 - " + ((2 * count) + 2) + ":00";
                break;
              }
          }
          // item.ChassisNo = item.chassis_number;
        }
        DataTable OpenTicketTable = oList.ToDataTable();
        Int32 OpenTicketTableColumnCount = OpenTicketTable.Columns.Count - 1;


        DataSet ds = new DataSet();
        ds.Tables.Add(OpenTicketTable);
        ds.Tables[0].Columns[0].ColumnName = "Ticket Id";
        ds.Tables[0].Columns[1].ColumnName = "AssignedTo";
        ds.Tables[0].Columns[3].ColumnName = "Ticket Status";
        ds.Tables[0].Columns[4].ColumnName = "Ticket Creation Time";
        ds.Tables[0].Columns[5].ColumnName = "Last Modified Time";
        ds.Tables[0].Columns[6].ColumnName = "Assigned to User Id";
        ds.Tables[0].Columns[7].ColumnName = "Van Reached Time";
        ds.Tables[0].Columns[8].ColumnName = "Ticket Accepted";
        ds.Tables[0].Columns[9].ColumnName = "Call Preclosed";
        ds.Tables[0].Columns[10].ColumnName = "Call Closure";
        ds.Tables[0].Columns[11].ColumnName = "Reassignment Time";
        ds.Tables[0].Columns[12].ColumnName = "Dealer Code";
        ds.Tables[0].Columns[13].ColumnName = "Dealer Name";
        ds.Tables[0].Columns.RemoveAt(2);
        ds.Tables[0].Columns.RemoveAt(6);
        // ds.Tables[0].Columns.RemoveAt(12);
        //  ds.Tables[0].Columns[30].ColumnName = "";
        ExcelUtility objExcelUtility = new ExcelHelper.ExcelUtility();
        List<WorkbookMappingModel> workbokkMapping = new List<WorkbookMappingModel>();

        string _excelExportPathOnServer = ConfigurationManager.AppSettings["excelExportPathOnServer"].ToString();
        fileName = "VECVReport_" + DateTime.Now.ToString("ddMMMyyyyhhmmss") + ".xlsx";

        //  filename = "CloseTicket_" + DateTime.Now.ToString("ddMMMyyyyhhmmss") + ".xlsx";
        workbokkMapping.Add(new WorkbookMappingModel { DataTableName = ds.Tables[0].TableName, WorkSheetName = "Open Report", StartColumnName = "A", StartRowNumber = 2, WorkSheetNumber = 1, AutoFit = false });
        string templatePath = HttpContext.Current.Server.MapPath(ConfigurationManager.AppSettings["OpenTicketReportTemplate"].ToString());
        string _exportLocation = ConfigurationManager.AppSettings["excelExportPath"].ToString();

        string pathToExcelFile = _exportLocation + fileName;

        //string saveAs = Server.MapPath(_excelExportPathOnServer + fileName);

        string SaveCsvAs = HttpContext.Current.Server.MapPath(_excelExportPathOnServer + fileName);


        //commented on 24-07-2020(excel download same as consolidate report)
        isCreated = CreateExcelFile.CreateExcelDocument(ds, SaveCsvAs);
        if (isCreated == true) { return _exportLocation + fileName; }
        else { return "error"; }

      }
      catch (Exception Ex)
      {
        // write error log into file
        objLog.ErrorLogFile("GetOpenCloseTicketDetail", Ex.Message, path, errorlogtf);
        throw Ex;
      }
      // return ObjModel;
      //  objList = objDealerRepository.GetDealerOpenTicketDetail(model);


    }


    #endregion
  }
}