DS7/DSWeb/Areas/TruckMng/Controllers/MsCustTruckFeeTemplateController.cs

using System;
using System.Linq;
using System.Web.Mvc;
using DSWeb.TruckMng.DAL.MsCustTruckFeeTemplate;
using DSWeb.TruckMng.Models.MsCustTruckFeeTemplate;
using DSWeb.MvcShipping.Helper;
using DSWeb.MvcShipping.Comm.Cookie;
using System.Collections.Generic;
using HcUtility.Comm;
using HcUtility.Core;
using System.Data;
using System.Data.OleDb;
using System.IO;
using System.Web.Configuration;
using DSWeb.SoftMng.Filter;

namespace DSWeb.Areas.TruckMng.Controllers
{
    [JsonRequestBehavior]
    public class MsCustTruckFeeTemplateController : Controller
    {
        //
        // GET: 
        public ActionResult Index()
        {
            return View();
        }

        //
        // GET: /
        public ActionResult Edit()
        {
            return View();
        }

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


        //
        // GET：
        [SqlKeyWordsFilter(Type = "Action")]//sql 防注入过滤器
        public ContentResult GetDataList(int start, int limit, string sort, string condition)
        {
            var dataList = MsCustTruckFeeTempldateDAL.GetDataList(condition, Convert.ToString(Session["USERID"]), CookieConfig.GetCookie_UserCode(Request), Convert.ToString(Session["COMPANYID"]), sort);

            var json = JsonConvert.Serialize(new { Success = true, Message = "查询成功", totalCount = dataList.Count, data = dataList.ToList() });
            return new ContentResult() { Content = json };
        }
        [SqlKeyWordsFilter(Type = "Action")]//sql 防注入过滤器
        public ContentResult GetAuditDataList(int start, int limit, string sort, string condition, string isaudit)
        {
            var dataList = MsCustTruckFeeTempldateDAL.GetAuditDataList(condition, isaudit, Convert.ToString(Session["USERID"]), Convert.ToString(Session["SHOWNAME"]), Convert.ToString(Session["COMPANYID"]), sort);
            var list = dataList.Skip(start).Take(limit);
            var json = JsonConvert.Serialize(new { Success = true, Message = "查询成功", totalCount = dataList.Count, data = list.ToList() });
            return new ContentResult() { Content = json };
        }

        //
        // GET：/TruckMng/MsWlTyreAcc/GetData/
        [SqlKeyWordsFilter(Type = "Action")]//sql 防注入过滤器
        public ContentResult GetData(string handle, string condition)
        {
            CustTruckFeeTemplate head = null;

            if (handle == "edit")
            {
                var list = MsCustTruckFeeTempldateDAL.GetDataList(condition, Convert.ToString(Session["USERID"]), CookieConfig.GetCookie_UserCode(Request), Convert.ToString(Session["COMPANYID"]));
                if (list.Count > 0)
                    head = list[0];
            }

            if (head == null)
            {
                head = new CustTruckFeeTemplate();
                head.BSSTATUS = "新增";
                head.CREATEUSER = Convert.ToString(Session["USERID"]);
                head.CREATEUSERREF = Convert.ToString(Session["SHOWNAME"]);
            }

            var json = JsonConvert.Serialize(
               new { Success = true, Message = "查询成功", data = head });
            return new ContentResult() { Content = json };
        }

        [SqlKeyWordsFilter(Type = "Action")]//sql 防注入过滤器
        public ContentResult GetBodyList(int start, int limit, string sort, string condition)
        {
            var dataList = MsCustTruckFeeTempldateDAL.GetBodyList(condition, sort);


            var json = JsonConvert.Serialize(new { Success = true, Message = "查询成功", totalCount = dataList.Count, data = dataList.ToList() });
            return new ContentResult() { Content = json };
        }


        public ContentResult Save(string opstatus, string data, string body)
        {
            var headData = JsonConvert.Deserialize<CustTruckFeeTemplate>(data);
            var bodyList = JsonConvert.Deserialize<List<CustTruckFeeTemplateDetail>>(body);

   
            if (opstatus == "add")
            {
                headData.DbOperationType = DbOperationType.DbotIns;
                headData.CREATEUSER = Convert.ToString(Session["USERID"]);
                headData.CORPID = Convert.ToString(Session["COMPANYID"]);
                headData.CREATETIME = DateTime.Now;

            }
            else if (opstatus == "edit")
            {
                headData.DbOperationType = DbOperationType.DbotUpd;
                headData.ModelUIStatus = "E";
            }
            else
            {
                headData.DbOperationType = DbOperationType.DbotDel;
            }


            var isPost = true;
            var errorstr = "";
            if (headData.CONTRACTNO!="") {
                var ct = MsCustTruckFeeTempldateDAL.GetRdCount("GID<>'" + headData.GID + "' AND CONTRACTNO='" + headData.CONTRACTNO + "' ");
                if (ct != 0)
                {
                    isPost = false;
                    errorstr = "客户合同号" + headData.CONTRACTNO + "重复,不允许保存！";
                }

            }
            if (isPost)
            {
                var modb = new ModelObjectDB();
                modb.Save(headData);
                var GID = headData.GID;
                DBResult result = MsCustTruckFeeTempldateDAL.SaveDetail(headData, bodyList, Convert.ToString(Session["USERID"]));



                var jsonRespose = new JsonResponse
                {
                    Success = result.Success,
                    Message = result.Message,
                    Data = MsCustTruckFeeTempldateDAL.GetData("GID='" + GID + "'")
                };

                return new ContentResult() { Content = JsonConvert.Serialize(jsonRespose) };
            }
            else {
                var jsonRespose = new JsonResponse { Success = false, Message = errorstr };
                return new ContentResult() { Content = JsonConvert.Serialize(jsonRespose) };

            }
        }

    
       

        public ContentResult Delete(string data)
        {
            var headData = JsonConvert.Deserialize<CustTruckFeeTemplate>(data);

            DBResult result = MsCustTruckFeeTempldateDAL.DeleteDetail(headData);
       

            var jsonRespose = new JsonResponse { Success = result.Success, Message = result.Message };
            return new ContentResult() { Content = JsonConvert.Serialize(jsonRespose) };
        }

        public ContentResult DeleteDetail(string data)
        {
            var head = JsonConvert.Deserialize<CustTruckFeeTemplateDetail>(data);
            DBResult result = MsCustTruckFeeTempldateDAL.DeleteTemplateDetail(head);
            var jsonRespose = new JsonResponse { Success = result.Success, Message = result.Message };
            return new ContentResult() { Content = JsonConvert.Serialize(jsonRespose) };
        }
        #region  审核、撤销审核

        public ContentResult SubmitAudit(string opstatus, string data, string body)
        {
            var headData = JsonConvert.Deserialize<CustTruckFeeTemplate>(data);
            var bodyList = JsonConvert.Deserialize<List<CustTruckFeeTemplateDetail>>(body);

            if (opstatus == "add")
            {
                headData.DbOperationType = DbOperationType.DbotIns;
                headData.CREATEUSER = Convert.ToString(Session["USERID"]);
                headData.CORPID = Convert.ToString(Session["COMPANYID"]);
                headData.CREATETIME = DateTime.Now;

            }
            else if (opstatus == "edit")
            {
                headData.DbOperationType = DbOperationType.DbotUpd;
                headData.ModelUIStatus = "E";
            }
            else
            {
                headData.DbOperationType = DbOperationType.DbotDel;
            }
            var isPost = true;
            var errorstr = "";
            if (headData.CONTRACTNO != "")
            {
                var ct = MsCustTruckFeeTempldateDAL.GetRdCount("GID<>'" + headData.GID + "' AND CONTRACTNO='" + headData.CONTRACTNO + "' ");
                if (ct != 0)
                {
                    isPost = false;
                    errorstr = "客户合同号" + headData.CONTRACTNO + "重复,不允许保存！";
                }

            }
            if (isPost)
            {
                var modb = new ModelObjectDB();
                modb.Save(headData);
                var GID = headData.GID;
                DBResult result = MsCustTruckFeeTempldateDAL.SaveDetail(headData, bodyList, Convert.ToString(Session["USERID"]));
                if (result.Success == true)
                {
                    var headList = new List<CustTruckFeeTemplate>();
                    headList.Add(headData);
                    result = MsCustTruckFeeTempldateDAL.SubmitAudit(Convert.ToString(Session["USERID"]), headList);

                }


                var jsonRespose = new JsonResponse
                {
                    Success = result.Success,
                    Message = result.Message,
                    Data = MsCustTruckFeeTempldateDAL.GetData("GID='" + GID + "'")
                };

                return new ContentResult() { Content = JsonConvert.Serialize(jsonRespose) };
            }
            else
            {
                var jsonRespose = new JsonResponse { Success = false, Message = errorstr };
                return new ContentResult() { Content = JsonConvert.Serialize(jsonRespose) };

            }
        }


        public ContentResult SubmitAuditList(string data)
        {
            var headList = JsonConvert.Deserialize<List<CustTruckFeeTemplate>>(data);
            var modb = new ModelObjectDB();
            DBResult result = MsCustTruckFeeTempldateDAL.SubmitAudit(Convert.ToString(Session["USERID"]), headList);
            var jsonRespose = new JsonResponse { Success = result.Success, Message = result.Message };
            return new ContentResult() { Content = JsonConvert.Serialize(jsonRespose) };
        }

        public ContentResult SubmitAuditBack(string data)
        {
            if (Convert.ToString(Session["COMPANYID"]).ToString().Trim() == "" || Convert.ToString(Session["USERID"]).ToString().Trim() == "" || Convert.ToString(Session["CODENAME"]).ToString().Trim() == "" || Convert.ToString(Session["SHOWNAME"]).ToString().Trim() == "" || Convert.ToString(Session["DEPTNAME"]).ToString().Trim() == "")
            {
                var jsonRespose2 = new JsonResponse { Success = false, Message = "登录超时，请退出系统重新登录！" };
                return new ContentResult() { Content = JsonConvert.Serialize(jsonRespose2) };
            }
            //

            var headData = JsonConvert.Deserialize<CustTruckFeeTemplate>(data);
            var headList = new List<CustTruckFeeTemplate>();
            headList.Add(headData);
            var result = MsCustTruckFeeTempldateDAL.SubmitAuditBack(Convert.ToString(Session["USERID"]), headList);

            var jsonRespose = new JsonResponse
            {
                Success = result.Success,
                Message = result.Message,
                Data = MsCustTruckFeeTempldateDAL.GetData("GID='" + headData.GID + "'")
            };
            return new ContentResult() { Content = JsonConvert.Serialize(jsonRespose) };
        }

        public ContentResult SubmitAuditBackList(string data)
        {
            var headList = JsonConvert.Deserialize<List<CustTruckFeeTemplate>>(data);
            var modb = new ModelObjectDB();
            DBResult result = MsCustTruckFeeTempldateDAL.SubmitAuditBack(Convert.ToString(Session["USERID"]), headList);
            var jsonRespose = new JsonResponse { Success = result.Success, Message = result.Message };
            return new ContentResult() { Content = JsonConvert.Serialize(jsonRespose) };
        }

        public ContentResult Audit(string data)
        {
            if (Convert.ToString(Session["COMPANYID"]).ToString().Trim() == "" || Convert.ToString(Session["USERID"]).ToString().Trim() == "" || Convert.ToString(Session["CODENAME"]).ToString().Trim() == "" || Convert.ToString(Session["SHOWNAME"]).ToString().Trim() == "" || Convert.ToString(Session["DEPTNAME"]).ToString().Trim() == "")
            {
                var jsonRespose2 = new JsonResponse { Success = false, Message = "登录超时，请退出系统重新登录！" };
                return new ContentResult() { Content = JsonConvert.Serialize(jsonRespose2) };
            }
            //

            var headData = JsonConvert.Deserialize<CustTruckFeeTemplate>(data);
            var headList = new List<CustTruckFeeTemplate>();
            headList.Add(headData);
            var result = MsCustTruckFeeTempldateDAL.AuditList(headList, Convert.ToString(Session["USERID"]));

            var jsonRespose = new JsonResponse
            {
                Success = result.Success,
                Message = result.Message,
                Data = MsCustTruckFeeTempldateDAL.GetData("GID='" + headData.GID + "'")
            };
            return new ContentResult() { Content = JsonConvert.Serialize(jsonRespose) };
        }

        public ContentResult AuditList(string data)
        {
            var headList = JsonConvert.Deserialize<List<CustTruckFeeTemplate>>(data);
            var modb = new ModelObjectDB();
            DBResult result = MsCustTruckFeeTempldateDAL.AuditList(headList, Convert.ToString(Session["USERID"]));
            var jsonRespose = new JsonResponse { Success = result.Success, Message = result.Message };
            return new ContentResult() { Content = JsonConvert.Serialize(jsonRespose) };
        }

        public ContentResult AuditBack(string data, string reason)
        {
            if (Convert.ToString(Session["COMPANYID"]).ToString().Trim() == "" || Convert.ToString(Session["USERID"]).ToString().Trim() == "" || Convert.ToString(Session["CODENAME"]).ToString().Trim() == "" || Convert.ToString(Session["SHOWNAME"]).ToString().Trim() == "" || Convert.ToString(Session["DEPTNAME"]).ToString().Trim() == "")
            {
                var jsonRespose2 = new JsonResponse { Success = false, Message = "登录超时，请退出系统重新登录！" };
                return new ContentResult() { Content = JsonConvert.Serialize(jsonRespose2) };
            }
            //

            var headData = JsonConvert.Deserialize<CustTruckFeeTemplate>(data);
            var headList = new List<CustTruckFeeTemplate>();
            headList.Add(headData);
            var result = MsCustTruckFeeTempldateDAL.AuditBackList(headList, Convert.ToString(Session["USERID"]), reason);

            var jsonRespose = new JsonResponse
            {
                Success = result.Success,
                Message = result.Message,
                Data = MsCustTruckFeeTempldateDAL.GetData("GID='" + headData.GID + "'")
            };
            return new ContentResult() { Content = JsonConvert.Serialize(jsonRespose) };
        }

        public ContentResult AuditBackList(string data, string reason)
        {
            var headList = JsonConvert.Deserialize<List<CustTruckFeeTemplate>>(data);
            var modb = new ModelObjectDB();
            DBResult result = MsCustTruckFeeTempldateDAL.AuditBackList(headList, Convert.ToString(Session["USERID"]), reason);
            var jsonRespose = new JsonResponse { Success = result.Success, Message = result.Message };
            return new ContentResult() { Content = JsonConvert.Serialize(jsonRespose) };
        }


        [HttpPost]
        public ContentResult ImportDetailData()
        {
            var jsonRespose = new JsonResponse { Success = false, Message = "" };

            if (Request.Files.Count != 1)
            {
                jsonRespose.Success = false;
                jsonRespose.Message = "请选择上传的文件";
                return new ContentResult() { Content = JsonConvert.Serialize(jsonRespose) };
            }

            var file = Request.Files["LoadExcel"];
            if (file == null)
            {
                jsonRespose.Success = false;
                jsonRespose.Message = "上传文件发生未知错误，请重新上传";
                return new ContentResult() { Content = JsonConvert.Serialize(jsonRespose) };
            }
            if (Convert.ToString(Session["COMPANYID"]).ToString().Trim() == "" || Convert.ToString(Session["USERID"]).ToString().Trim() == "" || Convert.ToString(Session["CODENAME"]).ToString().Trim() == "" || Convert.ToString(Session["SHOWNAME"]).ToString().Trim() == "" || Convert.ToString(Session["DEPTNAME"]).ToString().Trim() == "")
            {
                var jsonRespose2 = new JsonResponse { Success = false, Message = "登录超时，请退出系统重新登录！" };
                return new ContentResult() { Content = JsonConvert.Serialize(jsonRespose2) };
            }
            string ext = Path.GetExtension(file.FileName).ToLower();
            if (ext == ".asp" || ext == ".aspx")
            {
                jsonRespose.Success = false;
                jsonRespose.Message = "不允许上传ASP或ASPX文件";
                return new ContentResult() { Content = JsonConvert.Serialize(jsonRespose) };
            }

            var path = Server.MapPath("../../UploadFiles/BulkDetail");

            if (!Directory.Exists(path))
            {
                Directory.CreateDirectory(path);
            }

            var size = file.ContentLength;
            var name = Path.GetFileName(file.FileName);
            var bsno = Request.Form["bsno"];

            var usercode = CookieConfig.GetCookie_UserCode(Request);
            string filename = path + "\\" + usercode + DateTime.Now.ToString("yyyyMMddHHmmssfff") + name;

            if (System.IO.File.Exists(filename))
            {
                System.IO.File.Delete(filename);
            }
            file.SaveAs(filename);

            if (!System.IO.File.Exists(filename))
            {
                jsonRespose.Success = false;
                jsonRespose.Message = "上传的Excel不包含数据01";
                return new ContentResult() { Content = JsonConvert.Serialize(jsonRespose) };
            }

            var ExcelExt = "";
            try
            {
                ExcelExt = WebConfigurationManager.AppSettings["ExcelExt"].ToString();
            }
            catch
            {

            }
            if (ExcelExt == "" || ExcelExt == null) ExcelExt = "Excel 8.0;HDR=Yes;IMEX=1";

            List<string> sheets = ExcelSheetName(filename, ExcelExt);
            if (sheets.Count == 0)
            {
                jsonRespose.Success = false;
                jsonRespose.Message = "上传的Excel不包含数据02";
                return new ContentResult() { Content = JsonConvert.Serialize(jsonRespose) };
            }

            try
            {
                var sheetname = sheets[0];
                string excelConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filename +
                                   ";Extended Properties="+ ExcelExt;
                if (filename.ToLower().IndexOf(".xlsx") > 0)
                {
                    excelConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filename +
                                                       ";Extended Properties=\"Excel 12.0 Xml;IMEX=1;HDR=Yes\"";

                }
                OleDbDataAdapter oada = new OleDbDataAdapter("select * from [" + sheetname + "A1:AA]", excelConn);
                DataSet ds = new DataSet();
                oada.Fill(ds);

                if (ds.Tables.Count == 0)
                {
                    jsonRespose.Success = false;
                    jsonRespose.Message = "上传的Excel不包含数据03";
                    return new ContentResult() { Content = JsonConvert.Serialize(jsonRespose) };
                }

                var table = ds.Tables[0];
                //if (table.Rows.Count == 0 || table.Rows.Count == 1)
                //{
                //    jsonRespose.Success = false;
                //    jsonRespose.Message = "上传的Excel不包含数据04";
                //    return new ContentResult() { Content = JsonConvert.Serialize(jsonRespose) };
                //}



                var message = string.Empty;
                List<CustTruckFeeTemplateDetail> headList;

                bool isSucess = false;
                isSucess = MsCustTruckFeeTempldateDAL.ImportDetailData(Request, table, Convert.ToString(Session["USERID"]), bsno, out message, out headList);


                if (!isSucess)
                {
                    jsonRespose.Success = false;
                    jsonRespose.Message = message;
                    return new ContentResult() { Content = JsonConvert.Serialize(jsonRespose) };

                }



                var json = JsonConvert.Serialize(new { success = true, Message = "上传成功，共生成" + Convert.ToString(headList.Count) + "个费用方案明细", data = headList.ToList() });
                return new ContentResult() { Content = json };
            }
            catch (Exception e)
            {

                jsonRespose.Success = false;
                jsonRespose.Message = "读取Excel文件出错，请确认文件正确性" + e.Message;
                return new ContentResult() { Content = JsonConvert.Serialize(jsonRespose) };
            }
        }

        public List<string> ExcelSheetName(string filepath,string ExcelExt)
        {
            var al = new List<string>();

            try
            {
                string strConn;
                strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filepath + ";Extended Properties="+ ExcelExt;
                if (filepath.ToLower().IndexOf(".xlsx") > 0 || filepath.ToLower().IndexOf(".xlsm") > 0)
                {
                    strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filepath +
                                                       ";Extended Properties=\"Excel 12.0 Xml;HDR=Yes\"";

                }
                OleDbConnection conn = new OleDbConnection(strConn);
                conn.Open();
                System.Data.DataTable sheetNames = conn.GetOleDbSchemaTable
                    (System.Data.OleDb.OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
                conn.Close();

                foreach (DataRow dr in sheetNames.Rows)
                {
                    al.Add(dr[2].ToString());
                }
            }
            catch (Exception)
            {
                return new List<string>();
            }

            return al;
        }

        #endregion

        #region 参照部分

        #endregion

    }
}