using System;
using System.Collections.Generic;
using Microsoft.AspNetCore.Hosting;
using Microsoft.AspNetCore.Mvc;
using Microsoft.Extensions.Configuration;
using SuperCms.Extensions;
using SuperAdmin.Source;
using Microsoft.AspNetCore.Http;
using System.IO;
using ReportWeb.Models;
using ReportWeb;
using System.Data;
using Newtonsoft.Json;
using ReportWeb.Source;
using OfficeOpenXml;
using ClosedXML.Excel;

namespace SuperAdmin.Controllers
{
    public class AdminController : BaseController
    {
        private static readonly log4net.ILog log = log4net.LogManager.GetLogger(typeof(Program));
        DbConnector db = new DbConnector();
        List<Services> listService;
        String sdf = "dd/MM/yyyy HH:mm";
        String df = "dd/MM/yyyy";

        public static int counter = 0;
        public static bool counterStart = false;

        public AdminController(IConfiguration _configuration, IWebHostEnvironment hostEnvironment) : base(_configuration, hostEnvironment)
        {
            // init
            subDomain = _configuration["subDomain"];

        }

        public ActionResult Index(String nameSearch)
        {
            // get list service
            if (HttpContext.Session.GetComplexData<List<Services>>("listService") == null)
            {
                listService = db.LoadListServices();
                HttpContext.Session.SetComplexData("listService", listService);
            }

            Users user = HttpContext.Session.GetComplexData<Users>("user");

            if (user == null || (user.role <= 0))
            {
                return Redirect(subDomain + "/Home/Login");
            }

            if (user.role == Common.UserRole.Agent)
            {
                return View("SearchHistory");
            }

            ReportWeb.Models.DashboardModel model = new ReportWeb.Models.DashboardModel();
            model.listDailyReport = db.GetCommonReport(DateTime.Now.AddDays(-11), DateTime.Now.AddDays(-1));
            model.listDailyReportTotal = db.GetTotalReport(DateTime.Now.AddDays(-11), DateTime.Now.AddDays(-1));
            model.listHourlyReportTotal = db.GetTotalHourlyReport(DateTime.Now.AddDays(-1), DateTime.Now.AddDays(-1));
            //model.todayReport = db.GetCommonReport(DateTime.Now.AddDays(-1), DateTime.Now.AddDays(-1));
            //model.yesterdayReport = db.GetCommonReport(DateTime.Now.AddDays(-2), DateTime.Now.AddDays(-2));
            model.todayReport = new List<DailyReport>();
            model.yesterdayReport = new List<DailyReport>();
            String today = DateTime.Now.AddDays(-1).ToString(df);
            String yesterday = DateTime.Now.AddDays(-2).ToString(df);
            if (model.listDailyReport != null)
            {
                foreach (DailyReport dailyReport in model.listDailyReport)
                {
                    if (dailyReport.report_date == today)
                    {
                        model.todayReport.Add(dailyReport);
                    }
                    if (dailyReport.report_date == yesterday)
                    {
                        model.yesterdayReport.Add(dailyReport);
                    }

                    model.avg7NewSubs += dailyReport.count_reg;
                    model.avg7TotalSubs += dailyReport.count_active;
                    model.avg7TotalRevenue += dailyReport.revenue;
                }
            }
            model.avg7NewSubs = model.avg7NewSubs / 10;
            model.avg7TotalSubs = model.avg7TotalSubs / 10;
            model.avg7TotalRevenue = model.avg7TotalRevenue / 10;
            // total
            foreach (DailyReport dailyReport in model.todayReport)
            {
                model.totalSubs += dailyReport.count_active;
                model.newSubs += dailyReport.count_reg;
                model.totalRevenue += dailyReport.revenue;
            }
            model.totalSubs = model.totalSubs;
            model.newSubs = model.newSubs;
            model.totalRevenue = model.totalRevenue;


            return View(model);
        }

        public ActionResult SearchHistory()
        {
            Users user = HttpContext.Session.GetComplexData<Users>("user");

            if (user == null || (user.role <= 0))
            {
                return Redirect(subDomain + "/Home/Login");
            }
            else
            {
                return View();
            }
        }

        [HttpPost]
        public JsonResult SearchHistoryAction(String msisdn, String service_id, String fromDate, String toDate)
        {
            Users user = HttpContext.Session.GetComplexData<Users>("user");

            if (user == null || user.role <= 0)
            {
                ViewBag.username = "Welcome!";
                return Json(new
                {
                    error = "10",
                    content = "Timeout"
                });
            }

            msisdn = validateMsisdn(msisdn);
            if (msisdn == null || msisdn == "")
            {
                return Json(new
                {
                    error = "1",
                    content = "Enter msisdn"
                });
            }

            Services sv = GetServiceById(int.Parse(service_id));

            if (sv == null)
            {
                return Json(new
                {
                    error = "2",
                    content = "Not found service"
                });
            }
            DateTime startTime = DateTime.ParseExact(fromDate, df, null);
            DateTime endTime = DateTime.ParseExact(toDate, df, null);
            sv = db.GetServiceRpBySvCode(sv.sv_code);
            List<RegInfo> listRegInfo = db.GetRegisterInfo(sv, msisdn, startTime, endTime);

            List<ChargeLog> listChargeLog = db.GetChargeLog(sv, msisdn, startTime, endTime);

            return Json(new
            {
                error = "0",
                listRegInfo = listRegInfo,
                listChargeLog = listChargeLog
            });
        }

        // SPIN
        public ActionResult SearchSpin()
        {
            Users user = HttpContext.Session.GetComplexData<Users>("user");

            if (user == null || (user.role <= 0))
            {
                return Redirect(subDomain + "/Home/Login");
            }
            else
            {
                return View();
            }
        }

        [HttpPost]
        public JsonResult SearchSpinAction(String msisdn, String fromDate, String toDate)
        {
            Users user = HttpContext.Session.GetComplexData<Users>("user");

            if (user == null || user.role <= 0)
            {
                ViewBag.username = "Welcome!";
                return Json(new
                {
                    error = "10",
                    content = "Timeout"
                });
            }

            msisdn = validateMsisdn(msisdn);
            if (msisdn == null || msisdn == "")
            {
                return Json(new
                {
                    error = "1",
                    content = "Enter msisdn"
                });
            }

            DateTime startTime = DateTime.ParseExact(fromDate, df, null);
            DateTime endTime = DateTime.ParseExact(toDate, df, null);
            List<SpinLog> listSpin = db.GetLuckySpin(msisdn, startTime, endTime);
            SearchModel model = new SearchModel();
            model.listSpin = listSpin;
            model.remainSpin = db.CountLuckySpin(msisdn);
            return Json(new
            {
                error = "0",
                model = model
            });
        }

        [HttpPost]
        public IActionResult HistorySpinExport(String msisdn, String fromDate, String toDate)
        {
            if (!CheckAuthToken())
            {
                return Json(new
                {
                    error = "-1",
                    content = "Not allow"
                });
            }

            return ExportSpin(msisdn, fromDate, toDate);
        }

        private FileContentResult ExportSpin(String msisdn, String fromDate, String toDate)
        {
            try
            {
                ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
                msisdn = validateMsisdn(msisdn);
                DateTime startTime = DateTime.ParseExact(fromDate, df, null);
                DateTime endTime = DateTime.ParseExact(toDate, df, null);
                List<SpinLog> list = db.GetLuckySpin(msisdn, startTime, endTime);

                string contentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
                string fileName = "report_spin.xlsx";
                using (var workbook = new XLWorkbook())
                {
                    int i = 1;
                    IXLWorksheet worksheet = workbook.Worksheets.Add("Report Spin");
                    worksheet.Column(i).Width = 15;
                    worksheet.Cell(1, i++).Value = "No.";
                    worksheet.Column(i).Width = 20;
                    worksheet.Cell(1, i++).Value = "Phone number";
                    worksheet.Column(i).Width = 25;
                    worksheet.Cell(1, i++).Value = "Receive Time";
                    worksheet.Column(i).Width = 15;
                    worksheet.Cell(1, i++).Value = "Quantity";
                    worksheet.Column(i).Width = 15;
                    worksheet.Cell(1, i++).Value = "Used";
                    worksheet.Column(i).Width = 25;
                    worksheet.Cell(1, i++).Value = "Expire Time";
                    worksheet.Column(i).Width = 15;
                    worksheet.Cell(1, i++).Value = "Status";
                    worksheet.Column(i).Width = 15;
                    worksheet.Cell(1, i++).Value = "Note";

                    // make color
                    worksheet.Row(1).Style.Font.Bold = true;
                    worksheet.Row(1).Style.Fill.BackgroundColor = XLColor.Yellow;
                    //worksheet.Cell(1, i++).Value = "BetId";
                    if (list != null && list.Count > 0)
                    {
                        for (int index = 1; index <= list.Count; index++)
                        {
                            i = 1;
                            var news = list[index - 1];
                            worksheet.Cell(index + 1, i++).Value = index;
                            worksheet.Cell(index + 1, i++).SetValue(news.msisdn);
                            worksheet.Cell(index + 1, i++).SetValue(news.insert_time.Value.ToString("dd/MM/yyyy HH:mm:ss"));
                            worksheet.Cell(index + 1, i++).SetValue(news.added);
                            worksheet.Cell(index + 1, i++).SetValue(news.used);
                            worksheet.Cell(index + 1, i++).SetValue(news.expire_time.Value.ToString("dd/MM/yyyy HH:mm:ss"));
                            worksheet.Cell(index + 1, i++).SetValue(news.expire_time.Value > DateTime.Now ? "Valid" : "Expired");
                            worksheet.Cell(index + 1, i++).SetValue(news.channel_add);
                        }
                    }
                    using (var stream = new MemoryStream())
                    {
                        workbook.SaveAs(stream);
                        var content = stream.ToArray();
                        return File(content, contentType, fileName);
                    }
                }
            }
            catch (Exception ex)
            {
                return null;
            }
        }


        // REPORT
        public ActionResult Report(String serviceId)
        {
            Users user = HttpContext.Session.GetComplexData<Users>("user");

            if (user == null || (user.role <= 0))
            {
                return Redirect(subDomain + "/Home/Login");
            }
            else
            {
                ViewBag.ServiceId = serviceId;
                return View();
            }
        }


        public ActionResult ReportHourly()
        {
            Users user = HttpContext.Session.GetComplexData<Users>("user");

            if (user == null || (user.role <= 0))
            {
                return Redirect(subDomain + "/Home/Login");
            }
            else
            {
                return View();
            }
        }

        public ActionResult ReportRevenue()
        {
            Users user = HttpContext.Session.GetComplexData<Users>("user");

            if (user == null || (user.role <= 0))
            {
                return Redirect(subDomain + "/Home/Login");
            }
            else
            {
                return View();
            }
        }

        public List<DailyReport> GetReportData(String fromDate, String toDate, String service_id)
        {
            DateTime startTime = DateTime.ParseExact(fromDate, df, null);
            DateTime endTime = DateTime.ParseExact(toDate, df, null);

            Services sv = GetServiceById(int.Parse(service_id));

            List<DailyReport> res = db.GetDailyReport(sv.sv_code, startTime, endTime);
            return res;
        }

        [HttpPost]
        public JsonResult GetReport(String fromDate, String toDate, String service_id)
        {
            Users user = HttpContext.Session.GetComplexData<Users>("user");

            if (user == null || user.role <= 0)
            {
                ViewBag.username = "Welcome!";
                return Json(new
                {
                    error = "10",
                    content = "Timeout"
                });
            }
            Services sv = GetServiceById(int.Parse(service_id));

            if (sv == null)
            {
                return Json(new
                {
                    error = "2",
                    content = "Not found service"
                });
            }
            List<DailyReport> res = GetReportData(fromDate, toDate, service_id);

            return Json(new
            {
                error = "0",
                dailyReport = res
            });
        }


        [HttpPost]
        public IActionResult ReportExport(String fromDate, String toDate, String service_id)
        {
            Users user = HttpContext.Session.GetComplexData<Users>("user");

            if (user == null || user.role <= 0)
            {
                ViewBag.username = "Welcome!";
                return Json(new
                {
                    error = "10",
                    content = "Timeout"
                });
            }
            Services sv = GetServiceById(int.Parse(service_id));

            if (sv == null)
            {
                return Json(new
                {
                    error = "2",
                    content = "Not found service"
                });
            }

            return ExportReport(fromDate, toDate, service_id);
        }

        private FileContentResult ExportReport(String fromDate, String toDate, String service_id)
        {
            try
            {
                ExcelPackage.LicenseContext = LicenseContext.NonCommercial;

                List<DailyReport> list = GetReportData(fromDate, toDate, service_id);

                string contentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
                string fileName = "report_total.xlsx";
                using (var workbook = new XLWorkbook())
                {
                    int i = 1;
                    IXLWorksheet worksheet = workbook.Worksheets.Add("Report Spin");
                    worksheet.Column(i).Width = 15;
                    worksheet.Cell(1, i++).Value = "No.";
                    worksheet.Column(i).Width = 20;
                    worksheet.Cell(1, i++).Value = "Report date";
                    worksheet.Column(i).Width = 20;
                    worksheet.Cell(1, i++).Value = "Revenue";
                    worksheet.Column(i).Width = 20;
                    worksheet.Cell(1, i++).Value = "Revenue Buy";
                    worksheet.Column(i).Width = 20;
                    worksheet.Cell(1, i++).Value = "Paid money";
                    worksheet.Column(i).Width = 20;
                    worksheet.Cell(1, i++).Value = "Count sub";
                    worksheet.Column(i).Width = 20;
                    worksheet.Cell(1, i++).Value = "Register new";
                    worksheet.Column(i).Width = 20;
                    worksheet.Cell(1, i++).Value = "Deactive";

                    // make color
                    worksheet.Row(1).Style.Font.Bold = true;
                    worksheet.Row(1).Style.Fill.BackgroundColor = XLColor.Yellow;
                    //worksheet.Cell(1, i++).Value = "BetId";
                    if (list != null && list.Count > 0)
                    {
                        for (int index = 1; index <= list.Count; index++)
                        {
                            i = 1;
                            var news = list[index - 1];
                            worksheet.Cell(index + 1, i++).Value = index;
                            worksheet.Cell(index + 1, i++).SetValue(news.report_date);
                            worksheet.Cell(index + 1, i++).SetValue(news.revenue);
                            worksheet.Cell(index + 1, i++).SetValue(news.revenue_buy);
                            worksheet.Cell(index + 1, i++).SetValue(news.paid_money);
                            worksheet.Cell(index + 1, i++).SetValue(news.count_active);
                            worksheet.Cell(index + 1, i++).SetValue(news.count_reg);
                            worksheet.Cell(index + 1, i++).SetValue(news.count_deactive);
                        }
                    }
                    using (var stream = new MemoryStream())
                    {
                        workbook.SaveAs(stream);
                        var content = stream.ToArray();
                        return File(content, contentType, fileName);
                    }
                }
            }
            catch (Exception ex)
            {
                return null;
            }
        }

        [HttpPost]
        public JsonResult GetReportHourly(String fromDate, String toDate, String service_id)
        {
            Users user = HttpContext.Session.GetComplexData<Users>("user");

            if (user == null || user.role <= 0)
            {
                ViewBag.username = "Welcome!";
                return Json(new
                {
                    error = "10",
                    content = "Timeout"
                });
            }
            DateTime startTime = DateTime.ParseExact(fromDate, df, null);
            DateTime endTime = DateTime.ParseExact(toDate, df, null);


            Services sv = GetServiceById(int.Parse(service_id));

            if (sv == null)
            {
                return Json(new
                {
                    error = "2",
                    content = "Not found service"
                });
            }
            List<DailyReport> res = db.GetHourlyReport(sv.sv_code, startTime, endTime);

            return Json(new
            {
                error = "0",
                dailyReport = res
            });
        }

        [HttpPost]
        public JsonResult GetRevenueHourly(String fromDate, String toDate, String service_id)
        {
            Users user = HttpContext.Session.GetComplexData<Users>("user");

            if (user == null || user.role <= 0)
            {
                ViewBag.username = "Welcome!";
                return Json(new
                {
                    error = "10",
                    content = "Timeout"
                });
            }
            DateTime startTime = DateTime.ParseExact(fromDate, df, null);
            DateTime endTime = DateTime.ParseExact(toDate, df, null);


            Services sv = GetServiceById(int.Parse(service_id));

            if (sv == null)
            {
                return Json(new
                {
                    error = "2",
                    content = "Not found service"
                });
            }
            DataTable dt = db.GetRevenueHourly(sv.sv_code, startTime, endTime);

            return Json(new
            {
                data = JsonConvert.SerializeObject(dt),
                error = "0"
            });
        }


        public ActionResult ServiceManagement()
        {

            Users user = HttpContext.Session.GetComplexData<Users>("user");

            if (user == null)
            {
                ViewBag.username = "Welcome!";
                return Redirect(subDomain + "/Home/Login");
            }

            if (user.role != Common.UserRole.Admin)
            {
                HttpContext.Session.Clear();
                ViewBag.username = "Welcome!";
                return Redirect(subDomain + "/Home/Login");
            }

            List<Services> listService = db.LoadListServices();

            return View(listService);
        }

        public ActionResult ServiceRpManagement()
        {
            Users user = HttpContext.Session.GetComplexData<Users>("user");

            if (user == null)
            {
                ViewBag.username = "Welcome!";
                return Redirect(subDomain + "/Home/Login");
            }
            if (user.role != Common.UserRole.Admin)
            {
                HttpContext.Session.Clear();
                ViewBag.username = "Welcome!";
                return Redirect(subDomain + "/Home/Login");
            }
            List<Services> listService = db.LoadListServicesReport();

            return View(listService);
        }

        public ActionResult ServiceAdvManagement()
        {
            Users user = HttpContext.Session.GetComplexData<Users>("user");

            if (user == null)
            {
                ViewBag.username = "Welcome!";
                return Redirect(subDomain + "/Home/Login");
            }
            if (user.role != Common.UserRole.Admin)
            {
                HttpContext.Session.Clear();
                ViewBag.username = "Welcome!";
                return Redirect(subDomain + "/Home/Login");
            }
            List<Services> listService = db.LoadListServicesAdv(-1);

            return View(listService);
        }

        [HttpPost]
        public JsonResult SearchService()
        {
            Users user = HttpContext.Session.GetComplexData<Users>("user");
            if (user == null)
            {
                return Json(new
                {
                    error = "10",
                    content = "Timeout"
                });
            }
            if (user.role != Common.UserRole.Admin)
            {
                return Json(new
                {
                    error = "10",
                    content = "Timeout"
                });
            }
            List<Services> listService = db.LoadListServices();

            return Json(new
            {
                error = "0",
                content = "Success",
                listService = listService
            });
        }

        [HttpPost]
        public JsonResult SearchServiceRp()
        {
            Users user = HttpContext.Session.GetComplexData<Users>("user");
            if (user == null)
            {
                return Json(new
                {
                    error = "10",
                    content = "Timeout"
                });
            }
            if (user.role != Common.UserRole.Admin)
            {
                return Json(new
                {
                    error = "10",
                    content = "Timeout"
                });
            }
            List<Services> listService = db.LoadListServicesReport();

            return Json(new
            {
                error = "0",
                content = "Success",
                listService = listService
            });
        }


        [HttpPost]
        public JsonResult SearchServiceAdv(string status)
        {
            Users user = HttpContext.Session.GetComplexData<Users>("user");
            if (user == null)
            {
                return Json(new
                {
                    error = "10",
                    content = "Timeout"
                });
            }
            if (user.role != Common.UserRole.Admin)
            {
                return Json(new
                {
                    error = "10",
                    content = "Timeout"
                });
            }
            int st = status == null ? -1 : int.Parse(status);
            List<Services> listService = db.LoadListServicesAdv(st);

            return Json(new
            {
                error = "0",
                content = "Success",
                listService = listService
            });
        }

        [HttpPost]
        public JsonResult GetServiceRpBySvCode(String svCode)
        {
            Users user = HttpContext.Session.GetComplexData<Users>("user");
            if (user == null)
            {
                return Json(new
                {
                    error = "10",
                    content = "Timeout"
                });
            }
            if (user.role != Common.UserRole.Admin)
            {
                return Json(new
                {
                    error = "10",
                    content = "Timeout"
                });
            }
            Services service = db.GetServiceRpBySvCode(svCode);

            return Json(new
            {
                error = "0",
                content = "Success",
                service = service
            });
        }


        [HttpPost]
        public JsonResult GetServiceAdvById(String advId)
        {
            Users user = HttpContext.Session.GetComplexData<Users>("user");
            if (user == null)
            {
                return Json(new
                {
                    error = "10",
                    content = "Timeout"
                });
            }
            if (user.role != Common.UserRole.Admin)
            {
                return Json(new
                {
                    error = "10",
                    content = "Timeout"
                });
            }
            Services service = db.GetServiceAdvById(advId);

            return Json(new
            {
                error = "0",
                content = "Success",
                service = service
            });
        }

        [HttpPost]
        public JsonResult GetServiceById(String serviceId)
        {
            Users user = HttpContext.Session.GetComplexData<Users>("user");
            if (user == null)
            {
                return Json(new
                {
                    error = "10",
                    content = "Timeout"
                });
            }
            if (user.role != Common.UserRole.Admin)
            {
                return Json(new
                {
                    error = "10",
                    content = "Timeout"
                });
            }
            Services service = db.GetServiceById(serviceId);

            return Json(new
            {
                error = "0",
                content = "Success",
                service = service
            });
        }


        [HttpPost]
        public ActionResult AddService()
        {
            Users user = HttpContext.Session.GetComplexData<Users>("user");
            if (user == null)
            {
                return Json(new
                {
                    error = "10",
                    content = "Timeout"
                });
            }
            if (user.role != Common.UserRole.Admin)
            {
                return Json(new
                {
                    error = "10",
                    content = "Timeout"
                });
            }
            try
            {
                String serviceName = Request.Form["serviceName"];
                String serviceCode = Request.Form["serviceCode"];

                // call ws
                db.InsertService(serviceCode, serviceName);

                // Returns message that successfully uploaded  
                return Json(new
                {
                    error_code = "0"
                });
            }
            catch (Exception ex)
            {
                return Json(new { error_code = "2", content = "Error occurred. Error details: " + ex.Message });
            }
        }

        [HttpPost]
        public ActionResult AddServiceRp()
        {
            Users user = HttpContext.Session.GetComplexData<Users>("user");
            if (user == null)
            {
                return Json(new
                {
                    error = "10",
                    content = "Timeout"
                });
            }
            if (user.role != Common.UserRole.Admin)
            {
                return Json(new
                {
                    error = "10",
                    content = "Timeout"
                });
            }
            try
            {
                //formData.append('dbName', $("#dbName").val());
                //formData.append('svCode', $("#serviceCode").val());
                //formData.append('connectionString', $("#connectionString").val());
                //formData.append('dbUserName', $("#dbUserName").val());
                //formData.append('dbPassword', $("#dbPassword").val());
                //formData.append('sqlReportDaily', $("#sqlReportDaily").val());
                //formData.append('sqlReportWeekly', $("#sqlReportWeekly").val());

                String svCode = Request.Form["svCode"];
                String dbName = Request.Form["dbName"];
                String connectionString = Request.Form["connectionString"];
                String dbUserName = Request.Form["dbUserName"];
                String dbPassword = Request.Form["dbPassword"];
                String sqlReportDaily = Request.Form["sqlReportDaily"];
                String sqlReportHourly = Request.Form["sqlReportHourly"];

                // call ws
                db.InsertSvReport(svCode, dbName, connectionString, dbUserName, dbPassword, sqlReportDaily, sqlReportHourly);

                // Returns message that successfully uploaded  
                return Json(new
                {
                    error_code = "0"
                });
            }
            catch (Exception ex)
            {
                return Json(new { error_code = "2", content = "Error occurred. Error details: " + ex.Message });
            }

        }


        [HttpPost]
        public ActionResult AddServiceAdv()
        {
            Users user = HttpContext.Session.GetComplexData<Users>("user");
            if (user == null)
            {
                return Json(new
                {
                    error = "10",
                    content = "Timeout"
                });
            }
            if (user.role != Common.UserRole.Admin)
            {
                return Json(new
                {
                    error = "10",
                    content = "Timeout"
                });
            }
            try
            {
                //formData.append('svCode', $("#serviceCode").val());
                //formData.append('advName', $("#advName").val());
                //formData.append('msgAdv', $("#msgAdv").val());
                //formData.append('channelAdv', $("#channelAdv").val());
                //formData.append('messageType', $("#messageType").val());
                //formData.append('fromMoney', $("#fromMoney").val());

                String svCode = Request.Form["svCode"];
                String advName = Request.Form["advName"];
                String msgAdv = Request.Form["msgAdv"];
                String channelAdv = Request.Form["channelAdv"];
                String messageType = Request.Form["messageType"];
                String activeHour = Request.Form["activeHour"];
                String maxNumberMsg = Request.Form["maxNumberMsg"];
                if (activeHour != null)
                {
                    activeHour = activeHour.Replace(" ", "");
                }
                String fromMoney = Request.Form["fromMoney"];

                // call ws
                bool isSuccess = db.InsertSvAdv(svCode, msgAdv, advName, channelAdv, messageType, activeHour, maxNumberMsg, fromMoney);
                //db.InsertSvAdvHistory(svCode, messageType);

                // Returns message that successfully uploaded  
                if (isSuccess)
                {
                    return Json(new { error_code = "0", content = "Add service adv success" });
                }
                else
                {
                    return Json(new { error_code = "2", content = "Add service adv failed! DB error." });
                }
            }
            catch (Exception ex)
            {
                return Json(new { error_code = "2", content = "Error occurred. Error details: " + ex.Message });
            }

        }

        [HttpPost]
        public ActionResult UpdateServiceInfo()
        {
            Users user = HttpContext.Session.GetComplexData<Users>("user");
            if (user == null)
            {
                return Json(new
                {
                    error = "10",
                    content = "Timeout"
                });
            }
            if (user.role != Common.UserRole.Admin)
            {
                return Json(new
                {
                    error = "10",
                    content = "Timeout"
                });
            }
            try
            {
                String serviceId = Request.Form["serviceId"];
                String serviceName = Request.Form["serviceName"];
                String serviceCode = Request.Form["serviceCode"];

                // call ws
                db.UpdateServiceInfo(serviceId, serviceCode, serviceName);

                return Json(new
                {
                    error_code = "0"
                });
            }
            catch (Exception ex)
            {
                return Json(new { error_code = "2", content = "Error occurred. Error details: " + ex.Message });
            }

        }

        [HttpPost]
        public ActionResult UpdateServiceStatus()
        {
            Users user = HttpContext.Session.GetComplexData<Users>("user");
            if (user == null)
            {
                return Json(new
                {
                    error = "10",
                    content = "Timeout"
                });
            }
            if (user.role != Common.UserRole.Admin)
            {
                return Json(new
                {
                    error = "10",
                    content = "Timeout"
                });
            }
            try
            {
                String serviceId = Request.Form["serviceId"];
                String status = Request.Form["status"];

                // call ws
                db.UpdateServiceStatus(serviceId, status);

                return Json(new
                {
                    error_code = "0"
                });

            }
            catch (Exception ex)
            {
                return Json(new { error_code = "2", content = "Error occurred. Error details: " + ex.Message });
            }

        }


        [HttpPost]
        public ActionResult UpdateServiceRp()
        {
            Users user = HttpContext.Session.GetComplexData<Users>("user");
            if (user == null)
            {
                return Json(new
                {
                    error = "10",
                    content = "Timeout"
                });
            }
            if (user.role != Common.UserRole.Admin)
            {
                return Json(new
                {
                    error = "10",
                    content = "Timeout"
                });
            }
            try
            {
                String svCode = Request.Form["svCode"];
                String dbName = Request.Form["dbName"];
                String connectionString = Request.Form["connectionString"];
                String dbUserName = Request.Form["dbUserName"];
                String dbPassword = Request.Form["dbPassword"];
                String sqlReportDaily = Request.Form["sqlReportDaily"];
                String sqlReportHourly = Request.Form["sqlReportHourly"];

                db.UpdateServiceRpInfo(svCode, dbName, connectionString, dbUserName, dbPassword, sqlReportDaily, sqlReportHourly);

                return Json(new
                {
                    error_code = "0"
                });

            }
            catch (Exception ex)
            {
                return Json(new { error_code = "2", content = "Error occurred. Error details: " + ex.Message });
            }

        }


        [HttpPost]
        public ActionResult UpdateServiceRpStatus(String svCode, String status)
        {
            Users user = HttpContext.Session.GetComplexData<Users>("user");
            if (user == null)
            {
                return Json(new
                {
                    error = "10",
                    content = "Timeout"
                });
            }
            if (user.role != Common.UserRole.Admin)
            {
                return Json(new
                {
                    error = "10",
                    content = "Timeout"
                });
            }
            try
            {
                db.UpdateServiceRpStatus(svCode, status);
                return Json(new
                {
                    error_code = "0"
                });
            }
            catch (Exception ex)
            {
                return Json(new { error_code = "2", content = "Error occurred. Error details: " + ex.Message });
            }

        }



        [HttpPost]
        public ActionResult UpdateServiceAdv()
        {
            Users user = HttpContext.Session.GetComplexData<Users>("user");
            if (user == null)
            {
                return Json(new
                {
                    error = "10",
                    content = "Timeout"
                });
            }
            if (user.role != Common.UserRole.Admin)
            {
                return Json(new
                {
                    error = "10",
                    content = "Timeout"
                });
            }
            try
            {
                String advId = Request.Form["advId"];
                String svCode = Request.Form["svCode"];
                String advName = Request.Form["advName"];
                String msgAdv = Request.Form["msgAdv"];
                String channelAdv = Request.Form["channelAdv"];
                String messageType = Request.Form["messageType"];
                String activeHour = Request.Form["activeHour"];
                String maxNumberMsg = Request.Form["maxNumberMsg"];
                if (activeHour != null)
                {
                    activeHour = activeHour.Replace(" ", "");
                }
                String fromMoney = Request.Form["fromMoney"];

                bool isSuccess = db.UpdateServiceAdvInfo(svCode, msgAdv, advName, channelAdv, messageType, activeHour, maxNumberMsg, fromMoney, advId);

                if (isSuccess)
                {
                    return Json(new { error_code = "0", content = "Update service adv success" });
                }
                else
                {
                    return Json(new { error_code = "2", content = "Update service adv failed! DB error." });
                }
            }
            catch (Exception ex)
            {
                return Json(new { error_code = "2", content = "Error occurred. Error details: " + ex.Message });
            }

        }


        [HttpPost]
        public ActionResult UpdateServiceAdvStatus(String advId, String status)
        {
            Users user = HttpContext.Session.GetComplexData<Users>("user");
            if (user == null)
            {
                return Json(new
                {
                    error = "10",
                    content = "Timeout"
                });
            }
            if (user.role != Common.UserRole.Admin)
            {
                return Json(new
                {
                    error = "10",
                    content = "Timeout"
                });
            }
            try
            {
                // get adv info
                Services sv = db.GetServiceAdvById(advId);
                if (status == "0")
                {
                    // update end time adv
                    //db.UpdateServiceAdvEndTime(sv.sv_code);
                }
                else
                {
                    // insert new adv
                    //db.InsertSvAdvHistory(sv.sv_code, sv.message_type);
                }


                db.UpdateServiceAdvStatus(advId, status);

                return Json(new
                {
                    error_code = "0"
                });
            }
            catch (Exception ex)
            {
                return Json(new { error_code = "2", content = "Error occurred. Error details: " + ex.Message });
            }

        }

        //
        // BROADCAST
        //
        [HttpPost]
        public JsonResult SearchBroadcast(String fromDate, String toDate)
        {
            Users user = HttpContext.Session.GetComplexData<Users>("user");
            if (user == null)
            {
                return Json(new
                {
                    error = "10",
                    content = "Timeout"
                });
            }
            if (user.role != Common.UserRole.Admin)
            {
                return Json(new
                {
                    error = "10",
                    content = "Timeout"
                });
            }
            List<Broadcast> listBroadcast = GetBroadcast(fromDate, toDate);

            return Json(new
            {
                error = "0",
                listBroadcast = listBroadcast
            });
        }


        private List<Broadcast> GetBroadcast(String fromdate, String todate)
        {
            DateTime? fr = null;
            if (fromdate != null) fr = DateTime.ParseExact(fromdate, "dd/MM/yyyy", null);
            DateTime? td = null;
            if (todate != null) DateTime.ParseExact(todate, "dd/MM/yyyy", null);
            return db.LoadListBroadcast(fr, td);
        }

        public ActionResult Broadcast()
        {
            Users user = HttpContext.Session.GetComplexData<Users>("user");
            if (user == null)
            {
                ViewBag.username = "Welcome!";
                return Redirect(subDomain + "/Home/Login");
            }
            if (user.role != Common.UserRole.Admin)
            {
                HttpContext.Session.Clear();
                ViewBag.username = "Welcome!";
                return Redirect(subDomain + "/Home/Login");
            }
            List<Broadcast> res = GetBroadcast(DateTime.Now.AddDays(-30).ToString("dd/MM/yyyy"), DateTime.Now.ToString("dd/MM/yyyy"));

            return View(res);
        }

        //-------------
        // SMSGW
        //--------------
        public ActionResult Smsgw()
        {
            Users user = HttpContext.Session.GetComplexData<Users>("user");
            if (user == null)
            {
                ViewBag.username = "Welcome!";
                return Redirect(subDomain + "/Home/Login");
            }
            if (user.role != Common.UserRole.Admin)
            {
                HttpContext.Session.Clear();
                ViewBag.username = "Welcome!";
                return Redirect(subDomain + "/Home/Login");
            }
            List<WebserviceObj> res = GetSmsgw();

            return View(res);
        }

        [HttpPost]
        public JsonResult SearchSmsgw()
        {
            Users user = HttpContext.Session.GetComplexData<Users>("user");
            if (user == null)
            {
                return Json(new
                {
                    error = "10",
                    content = "Timeout"
                });
            }
            if (user.role != Common.UserRole.Admin)
            {
                return Json(new
                {
                    error = "10",
                    content = "Timeout"
                });
            }
            List<WebserviceObj> listSmsgw = GetSmsgw();

            return Json(new
            {
                error = "0",
                listSmsgw = listSmsgw
            });
        }

        private List<WebserviceObj> GetSmsgw()
        {
            return db.LoadListWebserivce("sendmt");
        }


        [HttpPost]
        public ActionResult AddSmsgw(IFormFile msgTemplate)
        {
            Users user = HttpContext.Session.GetComplexData<Users>("user");
            if (user == null)
            {
                return Json(new
                {
                    error = "10",
                    content = "Timeout"
                });
            }
            if (user.role != Common.UserRole.Admin)
            {
                return Json(new
                {
                    error = "10",
                    content = "Timeout"
                });
            }
            try
            {
                //int countSub = 0;
                //string importFile = "";
                String wsCode = Request.Form["wsCode"];
                String wsName = Request.Form["wsName"];
                String wsdl = Request.Form["wsdl"];
                String msgTemplateStr = "";// Request.Form["msgTemplate"];

                // insert DB
                WebserviceObj wsObj = new WebserviceObj();
                wsObj.ws_code = wsCode;
                wsObj.ws_name = wsName;
                wsObj.wsdl = wsdl;
                //  Get all files from Request object   
                if (msgTemplate != null)
                {
                    using (StreamReader inputStreamReader = new StreamReader(msgTemplate.OpenReadStream()))
                    {
                        msgTemplateStr = inputStreamReader.ReadToEnd();
                    }
                }
                wsObj.msg_template = msgTemplateStr;
                db.InsertWebservice(wsObj);

                // Returns message that successfully uploaded  
                return Json(new
                {
                    error = "0",
                    id = wsObj.ws_id
                });
            }
            catch (Exception ex)
            {
                return Json(new { error = "2", content = "Error occurred. Error details: " + ex.Message });
            }
        }

        [HttpPost]
        public ActionResult UpdateSmsgw(IFormFile msgTemplate)
        {
            Users user = HttpContext.Session.GetComplexData<Users>("user");
            if (user == null)
            {
                return Json(new
                {
                    error = "10",
                    content = "Timeout"
                });
            }
            if (user.role != Common.UserRole.Admin)
            {
                return Json(new
                {
                    error = "10",
                    content = "Timeout"
                });
            }
            try
            {
                String wsCode = Request.Form["wsCode"];
                String wsName = Request.Form["wsName"];
                String wsdl = Request.Form["wsdl"];
                String msgTemplateStr = "";// Request.Form["msgTemplate"];
                String id = Request.Form["id"];

                // insert DB
                WebserviceObj wsObj = new WebserviceObj();
                wsObj.ws_code = wsCode;
                wsObj.ws_name = wsName;
                wsObj.wsdl = wsdl;
                //  Get all files from Request object  
                if (msgTemplate != null)
                {
                    using (StreamReader inputStreamReader = new StreamReader(msgTemplate.OpenReadStream()))
                    {
                        msgTemplateStr = inputStreamReader.ReadToEnd();
                    }
                }
                wsObj.msg_template = msgTemplateStr;
                wsObj.ws_id = int.Parse(id);
                db.UpdateWebservice(wsObj);

                return Json(new
                {
                    error = "0",
                    content = "Success"
                });
            }
            catch (Exception ex)
            {
                return Json(new { error = "2", content = "Error occurred. Error details: " + ex.Message });
            }
        }

        [HttpPost]
        public ActionResult UpdateSmsgwStatus()
        {
            Users user = HttpContext.Session.GetComplexData<Users>("user");
            if (user == null)
            {
                return Json(new
                {
                    error = "10",
                    content = "Timeout"
                });
            }
            if (user.role != Common.UserRole.Admin)
            {
                return Json(new
                {
                    error = "10",
                    content = "Timeout"
                });
            }
            try
            {
                String status = Request.Form["status"];
                int id = int.Parse(Request.Form["id"]);

                //WebserviceObj wsObj = db.GetWebserviceById(id)[0];

                db.UpdateWebserviceStatus(id, status);
                return Json(new
                {
                    error = "0",
                    content = "Success"
                });
            }
            catch (Exception ex)
            {
                return Json(new { error = "2", content = "Error occurred. Error details: " + ex.Message });
            }
        }


        [HttpPost]
        public JsonResult GetSmsgwById(String id)
        {
            Users user = HttpContext.Session.GetComplexData<Users>("user");
            if (user == null)
            {
                return Json(new
                {
                    error = "10",
                    content = "Timeout"
                });
            }
            if (user.role != Common.UserRole.Admin)
            {
                return Json(new
                {
                    error = "10",
                    content = "Timeout"
                });
            }
            WebserviceObj wsObj = db.GetWebserviceById(int.Parse(id))[0];
            return Json(new
            {
                error = "0",
                smsgw = wsObj
            });
        }

        //----------------
        // SERVICE ADV SHCEDULE
        //----------------
        public ActionResult ServiceAdvScheduler(int? month, int? year, int? codeType)
        {
            Users user = HttpContext.Session.GetComplexData<Users>("user");
            if (user == null)
            {
                ViewBag.username = "Welcome!";
                return Redirect(subDomain + "/Home/Login");
            }
            if (user.role != Common.UserRole.Admin)
            {
                HttpContext.Session.Clear();
                ViewBag.username = "Welcome!";
                return Redirect(subDomain + "/Home/Login");
            }
            if (month == null)
            {
                month = DateTime.Now.Month;
            }
            if (year == null)
            {
                year = DateTime.Now.Year;
            }
            List<SvAdvSchedule> listService = db.LoadListSvAdvSchedule(month.Value, year.Value, codeType ?? LuckyCardUtils.CodeType.LUCKY_CARD);
            ViewBag.codeType = codeType;
            return View(listService);
        }

        [HttpPost]
        public JsonResult SearchSvAdvSchedule(int month, int year, int? codeType)
        {
            Users user = HttpContext.Session.GetComplexData<Users>("user");
            if (user == null)
            {
                return Json(new
                {
                    error = "10",
                    content = "Timeout"
                });
            }
            if (user.role != Common.UserRole.Admin)
            {
                return Json(new
                {
                    error = "10",
                    content = "Timeout"
                });
            }
            List<SvAdvSchedule> listService = db.LoadListSvAdvSchedule(month, year, codeType ?? LuckyCardUtils.CodeType.LUCKY_CARD);

            return Json(new
            {
                error = "0",
                content = "Success",
                listService = listService
            });
        }

        [HttpPost]
        public JsonResult GetSvAdvScheduleById(String scheduleId, int? codeType)
        {
            Users user = HttpContext.Session.GetComplexData<Users>("user");
            if (user == null)
            {
                return Json(new
                {
                    error = "10",
                    content = "Timeout"
                });
            }
            if (user.role != Common.UserRole.Admin)
            {
                return Json(new
                {
                    error = "10",
                    content = "Timeout"
                });
            }
            SvAdvSchedule service = db.GetSvAdvScheduleById(scheduleId, codeType ?? LuckyCardUtils.CodeType.LUCKY_CARD);

            return Json(new
            {
                error = "0",
                content = "Success",
                service = service
            });
        }
        [HttpPost]
        public ActionResult AddSvAdvSchedule()
        {
            Users user = HttpContext.Session.GetComplexData<Users>("user");
            if (user == null)
            {
                return Json(new
                {
                    error = "10",
                    content = "Timeout"
                });
            }
            if (user.role != Common.UserRole.Admin)
            {
                return Json(new
                {
                    error = "10",
                    content = "Timeout"
                });
            }
            try
            {

                String scheduleName = Request.Form["scheduleName"];
                String maxTotalMsg = Request.Form["maxTotalMsg"];
                String fromDate = Request.Form["fromDate"];
                String toDate = Request.Form["toDate"];
                String listAdv = Request.Form["listAdv"];
                String codeType = Request.Form["codeType"];

                Root myDeserializedClass = JsonConvert.DeserializeObject<Root>(listAdv);

                SvAdvSchedule advSchedule = new SvAdvSchedule();
                advSchedule.from_date = DateTime.ParseExact(fromDate, "dd/MM/yyyy HH:mm:ss", null);
                advSchedule.to_date = DateTime.ParseExact(toDate, "dd/MM/yyyy HH:mm:ss", null);
                advSchedule.schedule_name = scheduleName;
                if (maxTotalMsg != null)
                {
                    advSchedule.max_total_msg = int.Parse(maxTotalMsg);
                }
                else
                {
                    advSchedule.max_total_msg = 0;
                }

                advSchedule.list_adv = new List<SvAdv>();
                foreach (ListAdv adv in myDeserializedClass.listAdv)
                {
                    SvAdv svAdv = new SvAdv();
                    svAdv.id = int.Parse(adv.id);
                    svAdv.percent = int.Parse(adv.percent);
                    if (svAdv.percent > 0)
                    {
                        advSchedule.list_adv.Add(svAdv);
                    }
                }
                // insert
                if (db.InsertSvAdvSchedule(advSchedule, int.Parse(codeType)))
                {
                    // Returns message that successfully uploaded  
                    return Json(new
                    {
                        error_code = "0"
                    });
                }
                else
                {
                    // Returns message that successfully uploaded  
                    return Json(new
                    {
                        error_code = "1",
                        error_content = "Error database"
                    });
                }


            }
            catch (Exception ex)
            {
                return Json(new { error_code = "2", content = "Error occurred. Error details: " + ex.Message });
            }

        }
        [HttpPost]
        public ActionResult UpdateSvAdvSchedule()
        {
            Users user = HttpContext.Session.GetComplexData<Users>("user");
            if (user == null)
            {
                return Json(new
                {
                    error = "10",
                    content = "Timeout"
                });
            }
            if (user.role != Common.UserRole.Admin)
            {
                return Json(new
                {
                    error = "10",
                    content = "Timeout"
                });
            }
            try
            {
                String scheduleId = Request.Form["scheduleId"];
                String scheduleName = Request.Form["scheduleName"];
                String maxTotalMsg = Request.Form["maxTotalMsg"];
                String fromDate = Request.Form["fromDate"];
                String toDate = Request.Form["toDate"];
                String listAdv = Request.Form["listAdv"];
                String codeType = Request.Form["codeType"];

                //JavaScriptSerializer json_serializer = new JavaScriptSerializer();
                //SvAdvJson[] svAdvs = (SvAdvJson[])json_serializer.DeserializeObject(listAdv);

                Root myDeserializedClass = JsonConvert.DeserializeObject<Root>(listAdv);

                SvAdvSchedule advSchedule = new SvAdvSchedule();
                advSchedule.from_date = DateTime.ParseExact(fromDate, "dd/MM/yyyy HH:mm:ss", null);
                advSchedule.to_date = DateTime.ParseExact(toDate, "dd/MM/yyyy HH:mm:ss", null);
                advSchedule.schedule_name = scheduleName;
                if (maxTotalMsg != null)
                {
                    advSchedule.max_total_msg = int.Parse(maxTotalMsg);
                }
                else
                {
                    advSchedule.max_total_msg = 0;
                }
                advSchedule.id = int.Parse(scheduleId);
                advSchedule.list_adv = new List<SvAdv>();
                foreach (ListAdv adv in myDeserializedClass.listAdv)
                {
                    SvAdv svAdv = new SvAdv();
                    svAdv.id = int.Parse(adv.id);
                    svAdv.percent = int.Parse(adv.percent);
                    if (svAdv.percent > 0)
                    {
                        advSchedule.list_adv.Add(svAdv);
                    }
                }
                // insert
                if (db.UpdateSvAdvScheduleInfo(advSchedule, int.Parse(codeType)))
                {
                    return Json(new
                    {
                        error_code = "0"
                    });
                }
                else
                {
                    return Json(new
                    {
                        error_code = "1",
                        error_content = "Error database"
                    });
                }



            }
            catch (Exception ex)
            {
                return Json(new { error_code = "2", content = "Error occurred. Error details: " + ex.Message });
            }

        }

        [HttpPost]
        public ActionResult UpdateSvAdvScheduleStatus(String scheduleid, String status, String codeType)
        {
            Users user = HttpContext.Session.GetComplexData<Users>("user");
            if (user == null)
            {
                return Json(new
                {
                    error = "10",
                    content = "Timeout"
                });
            }
            if (user.role != Common.UserRole.Admin)
            {
                return Json(new
                {
                    error = "10",
                    content = "Timeout"
                });
            }
            try
            {
                db.UpdateSvAdvScheduleStatus(scheduleid, status, int.Parse(codeType));

                return Json(new
                {
                    error_code = "0"
                });
            }
            catch (Exception ex)
            {
                return Json(new { error_code = "2", content = "Error occurred. Error details: " + ex.Message });
            }

        }

        public ActionResult GetServiceAdvPartial(String scheduleId, int? codeType)
        {
            ViewBag.codeType = codeType;
            if (scheduleId == null)
            {
                List<SvAdv> listAdv = db.LoadListServicesAdv();
                SvAdvSchedule scheduler = new SvAdvSchedule();
                scheduler.list_adv = listAdv;
                return PartialView("_ScheduleDetail", scheduler);
            }
            else
            {
                SvAdvSchedule scheduler = db.GetSvAdvScheduleById(scheduleId, codeType ?? LuckyCardUtils.CodeType.LUCKY_CARD);
                return PartialView("_ScheduleDetail", scheduler);
            }

        }

        // counter

        public ActionResult ServiceAdvCounter(int? month, int? year, int? codeType)
        {
            Users user = HttpContext.Session.GetComplexData<Users>("user");
            if (user == null)
            {
                ViewBag.username = "Welcome!";
                return Redirect(subDomain + "/Home/Login");
            }
            if (user.role != Common.UserRole.Admin)
            {
                HttpContext.Session.Clear();
                ViewBag.username = "Welcome!";
                return Redirect(subDomain + "/Home/Login");
            }
            if (month == null)
            {
                month = DateTime.Now.Month;
            }
            if (year == null)
            {
                year = DateTime.Now.Year;
            }
            List<SvAdv> listService = db.LoadListSvAdvCounter(month.Value, year.Value, codeType ?? LuckyCardUtils.CodeType.LUCKY_CARD);

            ViewBag.codeType = codeType;
            return View(listService);
        }

        [HttpPost]
        public JsonResult SearchSvAdvCounter(int month, int year, int? codeType)
        {
            Users user = HttpContext.Session.GetComplexData<Users>("user");
            if (user == null)
            {
                return Json(new
                {
                    error = "10",
                    content = "Timeout"
                });
            }
            if (user.role != Common.UserRole.Admin)
            {
                return Json(new
                {
                    error = "10",
                    content = "Timeout"
                });
            }
            List<SvAdv> listService = db.LoadListSvAdvCounter(month, year, codeType ?? LuckyCardUtils.CodeType.LUCKY_CARD);

            return Json(new
            {
                error = "0",
                content = "Success",
                listService = listService
            });
        }
    }
}