public ActionResult IndexExport(int month, int year, string ticketSubject)
{
try
{
DateTime startDate = new DateTime(year, month, 1);
DateTime endDate = startDate.AddMonths(1).AddSeconds(-1);
TicketInfoList list = TicketInfoList.GetReportByDate(startDate, endDate, ticketSubject);
var file = CreateFile(list, month, year, ticketSubject);
if (file != null)
{
return(new FileContentResult(file,
"application/vnd.openxmlformats-officedocument.spreadsheetml.sheet")
{
FileDownloadName = "My Excel File.xlsx"
});
}
}
catch (Exception ex)
{
LogActivity log = LogActivity.NewLogActivity();
log.ControllerName = "ExportController";
log.ActionName = "IndexExport";
log.LogData = Newtonsoft.Json.JsonConvert.SerializeObject(ex.StackTrace);
log.CreatedBy = User.Identity.Name;
log.CreatedDate = DateTime.Now;
log = log.Save();
}
return(View());
}
private static void TestingTicket()
{
//Ticket obj = Ticket.NewTicket();
//obj.TicketNo = "TICKET01";
//obj.TicketSubject = "Testing Create Ticket";
//obj.Requester = "*****@*****.**";
//obj.CallerPosistion = "Provinsi";
//obj.Priority = Priority.Medium.ToString();
//obj.TicketOwner = "agen01";
//obj.TicketType = TicketTypeInfoList.GetTicketTypeInfoList().First().TicketTypeName;
//obj.TicketStatus = TicketStatus.Draft.ToString();
//obj.TicketDescription = "";
//TicketSolutionList list = new TicketSolutionList();
//TicketSolution sol = new TicketSolution()
//{
// HowToSolving = "Rahasia",
// UpdatedBy = obj.TicketOwner,
// UpdatedDate = DateTime.Now
//};
//list.Add(sol);
//sol = new TicketSolution()
//{
// HowToSolving = "Kepo",
// UpdatedBy = obj.TicketOwner,
// UpdatedDate = DateTime.Now
//};
//list.Add(sol);
//string solution = Newtonsoft.Json.JsonConvert.SerializeObject(list);
//list = null;
//list = Newtonsoft.Json.JsonConvert.DeserializeObject<TicketSolutionList>(solution);
//obj.Solution = solution;
//obj.Escalation = obj.TicketOwner;
//obj.CreatedBy = "Admin";
//obj.CreatedDate = DateTime.Now;
//obj.UpdatedBy = obj.CreatedBy;
//obj.UpdatedDate = obj.CreatedDate;
//Ticket obj = Ticket.GetTicket(15);
//TicketSolutionList list = Newtonsoft.Json.JsonConvert.DeserializeObject<TicketSolutionList>(obj.Solution);
//TicketSolution sol = new TicketSolution()
//{
// HowToSolving = "Mau tau aja atau mau tau banget?",
// UpdatedBy = obj.TicketOwner,
// UpdatedDate = DateTime.Now
//};
//list.Add(sol);
//string solution = Newtonsoft.Json.JsonConvert.SerializeObject(list);
//obj.Solution = solution;
//obj.Delete();
//obj = obj.Save();
TicketInfoList infolist = TicketInfoList.GetTicketInfoList();
TicketList list = TicketList.GetTicketList();
}
public JsonResult DayToDay(string ticketType)
{
DateTime baseDate = DateTime.Today;
DateTime startDate = baseDate.AddDays(-(int)baseDate.DayOfWeek);
DateTime endDate = startDate.AddDays(7).AddSeconds(-1);
TicketInfoList list = null;
if (!string.IsNullOrEmpty(ticketType))
{
list = TicketInfoList.GetReportByDateAndTicketType(startDate, endDate, ticketType);
}
else
{
list = TicketInfoList.GetReportByDate(startDate, endDate);
}
List <TicketInfo> source = new List <TicketInfo>();
List <string> agenIdList = new List <string>();
AgentInfo info = AgentInfo.GetAgentInfo(User.Identity.Name);
agenIdList.Add(info.idCustAgent);
if (info.IsSupervisor)
{
var infolist = AgentInfoList.GetAgentByGroup(info.GroupName).Where(x => x.SPVID == info.idCustAgent).ToList();
foreach (var item in infolist)
{
agenIdList.Add(item.idCustAgent);
}
}
source = list.Where(x => agenIdList.Contains(x.CreatedBy)).ToList();
List <MorrisBar> data = new List <MorrisBar>();
for (DateTime i = startDate; i < endDate.AddDays(1); i = i.AddDays(1))
{
var tmpList = source.Where(x => x.CreatedDate.Date == i && agenIdList.Contains(x.TicketOwner)).ToList();
MorrisBar bar = new MorrisBar();
bar.Count = tmpList.Count;
bar.Open = tmpList.Where(x => x.TicketStatus == "Open").ToList().Count;
bar.Closed = tmpList.Where(x => x.TicketStatus == "Closed").ToList().Count;
bar.Tanggal = i.Day;
data.Add(bar);
}
return(Json(data, JsonRequestBehavior.AllowGet));
}
public JsonResult AllCurrentMonthToMonth(string ticketType)
{
DateTime baseDate = DateTime.Today;
DateTime startDate = baseDate.AddDays(1 - baseDate.Day);
DateTime endDate = startDate.AddMonths(1).AddSeconds(-1);
List <TicketInfo> list = new List <TicketInfo>();
if (!string.IsNullOrEmpty(ticketType))
{
list = TicketInfoList.GetReportByDateAndTicketType(startDate, endDate, ticketType).ToList();
}
else
{
list = TicketInfoList.GetReportByDate(startDate, endDate).ToList();
}
List <TicketInfo> source = new List <TicketInfo>();
List <string> agenIdList = new List <string>();
AgentInfo info = AgentInfo.GetAgentInfo(User.Identity.Name);
agenIdList.Add(info.idCustAgent);
if (info.IsSupervisor)
{
var infolist = AgentInfoList.GetAgentByGroup(info.GroupName).Where(x => x.SPVID == info.idCustAgent).ToList();
foreach (var item in infolist)
{
agenIdList.Add(item.idCustAgent);
}
}
source = list.Where(x => agenIdList.Contains(x.TicketOwner)).ToList();
List <MorrisDonut> data = new List <MorrisDonut>();
MorrisDonut m = new MorrisDonut();
m.label = "Open";
m.value = source.Where(x => x.TicketStatus == "Open").Count();
data.Add(m);
m = new MorrisDonut();
m.value = source.Where(x => x.TicketStatus == "Closed").Count();
m.label = "Closed";
data.Add(m);
return(Json(data, JsonRequestBehavior.AllowGet));
}
public JsonResult MonthToMonth(string ticketType)
{
DateTime baseDate = DateTime.Today;
DateTime startDate = baseDate.AddDays(1 - baseDate.Day);
DateTime endDate = startDate.AddMonths(1).AddSeconds(-1);
TicketInfoList list = null;
if (!string.IsNullOrEmpty(ticketType))
{
list = TicketInfoList.GetReportByDateAndTicketType(new DateTime(DateTime.Now.Year, 1, 1), new DateTime(DateTime.Now.Year, 12, 31), ticketType);
}
else
{
list = TicketInfoList.GetReportByDate(new DateTime(DateTime.Now.Year, 1, 1), new DateTime(DateTime.Now.Year, 12, 31));
}
List <TicketInfo> source = new List <TicketInfo>();
List <string> agenIdList = new List <string>();
AgentInfo info = AgentInfo.GetAgentInfo(User.Identity.Name);
agenIdList.Add(User.Identity.Name);
if (info.IsSupervisor)
{
var infolist = AgentInfoList.GetAgentByGroup(info.GroupName).Where(x => x.SPVID == info.idCustAgent).ToList();
foreach (var item in infolist)
{
agenIdList.Add(item.idCustAgent);
}
}
source = list.Where(x => agenIdList.Contains(x.CreatedBy)).ToList();
List <MorrisBar> data = new List <MorrisBar>();
for (int i = 1; i < 13; i++)
{
var tmpList = source.Where(x => x.CreatedDate.Month == i && agenIdList.Contains(x.TicketOwner)).ToList();
MorrisBar m = new MorrisBar();
m.Count = tmpList.Count;
m.Closed = tmpList.Where(x => x.TicketStatus == "Closed").Count();
m.Open = tmpList.Where(x => x.TicketStatus == "Open").Count();
m.Tanggal = i;
data.Add(m);
}
return(Json(data, JsonRequestBehavior.AllowGet));
}
// GET: Inbox
public ActionResult IndexInbox()
{
List <string> agenIdList = new List <string>();
AgentInfo info = AgentInfo.GetAgentInfo(User.Identity.Name);
agenIdList.Add(info.idCustAgent);
if (info.IsSupervisor)
{
var infolist = AgentInfoList.GetAgentByGroup(info.GroupName).Where(x => x.SPVID == info.idCustAgent).ToList();
foreach (var item in infolist)
{
agenIdList.Add(item.idCustAgent);
}
}
/// var obj = TicketInfoList.GetReportOpenTicket().Where(x => agenIdList.Contains(x.TicketOwner)).OrderByDescending(x => x.CreatedDate);
var obj = TicketInfoList.GetReportOpenTicket().OrderByDescending(x => x.CreatedDate);
ViewBag.TicketInfoList = obj;
return(View());
}
public JsonResult GetGata2(string ticketType)
{
try
{
List <TicketUpdate> list = new List <TicketUpdate>();
AgentInfo info = AgentInfo.GetAgentInfo(User.Identity.Name);
List <string> agenIdList = new List <string>();
agenIdList.Add(info.idCustAgent);
if (info.IsSupervisor)
{
var infolist = AgentInfoList.GetAgentByGroup(info.GroupName).Where(x => x.SPVID == info.idCustAgent).ToList();
foreach (var item in infolist)
{
agenIdList.Add(item.idCustAgent);
}
}
var data = TicketInfoList.GetTicketInfoList().Where(x => agenIdList.Contains(x.TicketOwner) && x.TicketType == ticketType).ToList();
var val = from c in data
select new TicketUpdate
{
TicketID = c.TicketNo,
Status = c.TicketStatus,
Owner = c.TicketOwner,
UpdatedDate = c.UpdatedDate.ToString("dd MMM yyyy")
};
return(Json(val.ToArray(), JsonRequestBehavior.AllowGet));
}
catch (Exception)
{
throw;
}
}
private byte[] CreateFile(TicketInfoList list, int month, int year, string ticketSubject)
{
string filename = "Export File.xlsx";
var filetemplate = new FileInfo(ControllerContext.HttpContext.Server.MapPath("~/Content/template/LAPORAN BULAN.xlsx"));
var newfile = new FileInfo(ControllerContext.HttpContext.Server.MapPath("~/Content/Download/" + filename));
string bulan = new DateTime(year, month, 1).ToString("MMM");
string tahun = new DateTime(year, month, 1).ToString("yyyy");
int startrow = 8;
var ms = new MemoryStream();
using (ExcelPackage newexcel = new ExcelPackage(ms))
{
var wb = newexcel.Workbook;
ExcelWorksheet ws = wb.Worksheets.Add("Sheet1");
using (ExcelPackage templateexcel = new ExcelPackage(filetemplate))
{
ExcelWorksheet wsTemplate = templateexcel.Workbook.Worksheets[1];
ws.Cells[2, 1].Value = wsTemplate.Cells[2, 1].Value.ToString().Replace("[TANGGAL_TAHUN]", bulan.ToUpper() + " " + tahun);
ws.Cells[2, 1].Style.Font.Bold = true;
ws.Cells[2, 1, 2, 9].Merge = true;
ws.Cells[2, 1, 2, 9].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
ws.Cells[3, 1].Value = wsTemplate.Cells[3, 1].Value;
ws.Cells[3, 1].Style.Font.Bold = true;
ws.Cells[3, 1, 3, 9].Merge = true;
ws.Cells[3, 1, 3, 9].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
ws.Cells[4, 1].Value = wsTemplate.Cells[4, 1].Value;
ws.Cells[4, 1].Style.Font.Bold = true;
ws.Cells[4, 1, 4, 9].Merge = true;
ws.Cells[4, 1, 4, 9].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
ws.Cells[5, 1].Value = wsTemplate.Cells[5, 1].Value;
ws.Cells[5, 1].Style.Font.Bold = true;
ws.Cells[5, 1, 5, 9].Merge = true;
ws.Cells[5, 1, 5, 9].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
ws.Cells[6, 1].Value = wsTemplate.Cells[6, 1].Value;
ws.Cells[6, 1].Style.Font.Bold = true;
ws.Cells[6, 1, 6, 9].Merge = true;
ws.Cells[6, 1, 6, 9].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
ws.Cells[8, 10].Value = "Status";
ws.Cells[8, 11].Value = "Durasi";
for (int i = 1; i < 10; i++)
{
ws.Cells[startrow, i].Value = wsTemplate.Cells[startrow, i].Value;
}
}
startrow = 9;
foreach (var item in list)
{
ws.Cells[startrow, 1].Value = item.CreatedDate.ToString("dd MMM yyyy");
ws.Cells[startrow, 2].Value = item.TicketNo;
CustomerInfo info = CustomerInfo.GetCustomerInfo(item.Requester);
if (info != null)
{
ws.Cells[startrow, 3].Value = info.CompanyName;
}
ws.Cells[startrow, 4].Value = item.CallerPosistion;
ws.Cells[startrow, 5].Value = item.TicketType;
ws.Cells[startrow, 6].Value = item.Priority;
ws.Cells[startrow, 7].Value = item.TicketDescription;
ws.Cells[startrow, 7].Style.WrapText = true;
List <ISAT.Web.ViewModel.TicketSolution> solutions = Newtonsoft.Json.JsonConvert.DeserializeObject <List <ISAT.Web.ViewModel.TicketSolution> >(item.Solution);
StringBuilder sb = new StringBuilder();
foreach (var sol in solutions)
{
sb.AppendLine(sol.Solution);
}
ws.Cells[startrow, 8].Value = sb.ToString();
ws.Cells[startrow, 8].Style.WrapText = true;
ws.Cells[startrow, 9].Value = item.CreatedBy;
ws.Cells[startrow, 10].Value = item.TicketStatus;
if (item.TicketStatus == TicketStatus.Open.ToString())
{
DateTime currentDate = DateTime.Now;
int days = currentDate.Subtract(item.CreatedDate).Days;
int hours = currentDate.Subtract(item.CreatedDate).Hours;
string durasi = string.Format("{0} hari {1} jam", days, hours);
ws.Cells[startrow, 11].Value = durasi;
}
startrow++;
}
for (int i = 1; i < 11 && i != 8 && i != 7; i++)
{
ws.Column(i).AutoFit();
}
newexcel.Save();
return(ms.ToArray());
}
}
