Untitled

using OfficeOpenXml;
using OfficeOpenXml.Style;
using System;
using System.Collections.Generic;
using System.Data.Entity;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using WassProITC.Business;
using WassProITC.Business.Data;
using WassProITC.Models;
using WassProITC.Utilities;

namespace WassProITC.Modules.DMBangGia
{
    public class DMBangGiaController : Controller
    {
        SPContext db = new SPContext();

        public ActionResult Index()
        {
            return View();
        }

        public JsonResult GetDMBangGia()
        {
            var search = "";
            if (!string.IsNullOrEmpty(Request.Form["search[value]"]))
            {
                search = Request.Form["search[value]"];
            }
            BusDMBangGia adapter = new BusDMBangGia();
            var banggia = adapter.GetDMBangGia(db, search).AsEnumerable();

            //sort
            var sortCol = "0";
            string sortDirection = "asc";

            if (!string.IsNullOrEmpty(Request.Form["order[0][column]"]))
            {
                sortCol = Request.Form["order[0][column]"];
            }

            if (!string.IsNullOrEmpty(Request.Form["order[0][dir]"]))
            {
                sortDirection = Request.Form["order[0][dir]"];
            }

            var sortOrder = sortCol + "_" + sortDirection;

            switch (sortOrder)
            {
                case "1_asc":
                    banggia = banggia.OrderBy(n => n.MaGia);
                    break;
                case "1_desc":
                    banggia = banggia.OrderByDescending(n => n.MaGia);
                    break;

                case "2_asc":
                    banggia = banggia.OrderBy(n => n.DonGia);
                    break;
                case "2_desc":
                    banggia = banggia.OrderByDescending(n => n.DonGia);
                    break;
                case "3_asc":
                    banggia = banggia.OrderBy(n => n.DonGia0V);
                    break;
                case "3_desc":
                    banggia = banggia.OrderByDescending(n => n.DonGia0V);
                    break;
            }

            var data = banggia.Select(s => new
            {
                GiaID = s.GiaID,
                MaGia = s.MaGia,
                DonGia = s.DonGia,
                DonGia0V = s.DonGia0V,
                DienGiai = s.DienGiai,
                Tools = " <button type ='button' data-id=" + s.GiaID + " class='btn btn-info btn-edit-dmbanggia btn-xs btn-flat  margin-right-5'><i class='fa fa-edit'></i></button>" + " <button type='button' data-id=" + s.GiaID + " class='btn btn-danger btn-xs btn-flat btn-delete-dmbanggia'><i class='fa fa-trash-o'></i></button>"
            }).ToList();


            //Data length
            var length = Int32.Parse(Request.Form["length"]);
            var start = Int32.Parse(Request.Form["start"]);

            var result = new
            {
                draw = Request.Form["draw"],
                recordsTotal = data.Count(),
                recordsFiltered = data.Count(),
                data = data.Skip(start).Take(length) // total data array
            };


            return Json(result, JsonRequestBehavior.AllowGet);
        }

        //hiển thị modal cho cập nhật
        public ActionResult Modal(decimal GiaID = 0)
        {
            if (HttpContext.Request.IsAjaxRequest())
            {
                BangGia banggia = new BangGia();
                if (GiaID != 0)

                    banggia = db.BangGias.FirstOrDefault(c => c.GiaID == GiaID);

                return PartialView(banggia);
            }
            else
            {
                return HttpNotFound();
            }
        }


        //delete danh mục
        [HttpPost]
        public ActionResult DeleteDMBG(decimal GiaID)
        {
            if (HttpContext.Request.IsAjaxRequest())
            {

                var gia = db.BangGias.FirstOrDefault(c => c.GiaID == GiaID);
                if (gia.GiaID == GiaID)
                {
                    db.BangGias.Remove(gia);
                    db.SaveChanges();
                    return Json(new Result() { Code = ResultCode.Success, Message = "Xóa giá thành công! " }, JsonRequestBehavior.AllowGet);
                }

                else
                {
                    return Json(new Result() { Code = ResultCode.Error, Message = "Dữ liệu đã được sử dụng!" }, JsonRequestBehavior.AllowGet);
                }
            }
            else
            {
                return HttpNotFound();
            }
        }

        //update danh mục
        [HttpPost]
        [ValidateAntiForgeryToken]
        public ActionResult Update(BangGia banggia)
        {
            if (HttpContext.Request.IsAjaxRequest())
            {
                try
                {
                    if (ModelState.IsValid)
                    {

                        if (banggia.GiaID == 0)
                        {

                            db.BangGias.Add(banggia);
                            db.SaveChanges();
                            return Json(new Result()
                            {
                                Code = ResultCode.Success,
                                Message = "Danh mục " + banggia.MaGia + " đã được tạo thành công",
                                Data = true
                            }, JsonRequestBehavior.AllowGet);
                        }
                        else
                        {
                            var old_banggia = db.BangGias.FirstOrDefault(x => x.GiaID == banggia.GiaID);
                            old_banggia.MaGia = banggia.MaGia;
                            old_banggia.DonGia = Convert.ToInt32(banggia.DonGia);
                            old_banggia.DonGia0V = Convert.ToInt32(banggia.DonGia0V);
                            old_banggia.DienGiai = banggia.DienGiai;


                            //db.Entry(banggia).State = EntityState.Modified;
                            db.SaveChanges();
                            return Json(new Result() { Code = ResultCode.Success, Message = "Cập nhật thành công!", Data = true }, JsonRequestBehavior.AllowGet);
                        }

                    }
                    return Json(new Result() { Code = ResultCode.Error, Message = "" }, JsonRequestBehavior.AllowGet);
                }
                catch
                {
                    return Json(new Result() { Code = ResultCode.Error, Message = "Có lỗi trong quá trình thực hiện!" }, JsonRequestBehavior.AllowGet);
                }
            }
            else
            {
                return HttpNotFound();
            }
        }

        //export
        public ActionResult ExportExcel(string search = "")
        {
            byte[] result = null;
            using (ExcelPackage package = new ExcelPackage())
            {
                BusDMBangGia apdater = new BusDMBangGia();
                var dmbgs = apdater.GetDMBangGia(db, search);

                ExcelWorksheet workSheet = package.Workbook.Worksheets.Add(String.Format("{0}", "sheet1"));
                using (ExcelRange cells = workSheet.Cells["A1:E1"])
                {
                    cells.Value = "DANH MỤC BẢNG GIÁ";
                    cells.Merge = true;
                    cells.Style.Font.Size = 15;
                    cells.Style.Font.Name = "Times New Roman";
                    cells.Style.Font.Bold = true;
                    cells.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
                }

                //TIÊU ĐỀ
                string[] valHeaderTable = { "STT", "Mã Giá", "Đơn giá", "Đơn giá 0v", "Diễn giải" };
                int lengthHeaderTable = valHeaderTable.Length;
                int colHeader;

                //check lai gia tri nay neu export loi
                int rowHeader = 3;
                int valBegin = 1;
                for (colHeader = valBegin; colHeader < valBegin + lengthHeaderTable; colHeader++)
                {
                    int j = colHeader - valBegin;
                    workSheet.Cells[rowHeader, colHeader].Value = valHeaderTable[j];
                    workSheet.Cells[rowHeader, colHeader].Style.Font.Name = "Time New Roman";
                    workSheet.Cells[rowHeader, colHeader].Style.Font.Size = 11;
                    workSheet.Cells[rowHeader, colHeader].Style.Font.Bold = true;
                    workSheet.Cells[rowHeader, colHeader].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
                }
                int startRowFrom = rowHeader + 1;

                if (dmbgs.Count() > 0)
                {
                    ExcelExportHelper.SetStyleHeader(workSheet.Cells["A" + rowHeader + ":E" + (rowHeader)]);

                    for (int j = 0; j < dmbgs.Count(); j++)
                    {
                        var item = dmbgs[j];
                        int i = j + 1;


                        //noi dung
                        workSheet.Cells[startRowFrom, 1].Value = i;

                        workSheet.Cells[startRowFrom, 2].Value = item.MaGia;
                        workSheet.Cells[startRowFrom, 3].Value = item.DonGia;
                        workSheet.Cells[startRowFrom, 4].Value = item.DonGia0V;
                        workSheet.Cells[startRowFrom, 5].Value = item.DienGiai;

                        for (int style = 1; style <= lengthHeaderTable; style++)
                        {
                            workSheet.Cells[startRowFrom, style].Style.Font.Name = "Time New Roman";
                            workSheet.Cells[startRowFrom, style].Style.Font.Size = 11;
                        }
                        startRowFrom++;
                    }
                    ExcelExportHelper.SetStyleContent(workSheet.Cells[rowHeader, 1, startRowFrom - 1, lengthHeaderTable]);
                }
                ExcelExportHelper.SetAutoFit(workSheet, lengthHeaderTable);
                result = package.GetAsByteArray();
            }
            return File(result, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", "DanhMucBangGia.xlsx");
        }
    }

}