using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Data.OleDb;
using MES.Production.Entity;
using Ant.ORM;
using Ant.Data;
using Ant.Common;
using System.Text.RegularExpressions;
using MES.Production.Entity.Enum;
using System.Transactions;
using ChangFa.Machinery.WebPage.Controllers;
using Central.Control.Domain;
using MES.Production.Entity.Entity;
using Ant.Service.Utility;
using System.Configuration;
using ChangFa.Machinery.WebPage.App_Start;

namespace ChangFa.Machinery.WebPage.Areas.SysManage.Controllers
{
    public class ExcelController : BaseController
    {

        /// <summary>
        /// 
        /// </summary>
        /// <param name="file"></param>
        /// <returns></returns>
        [HttpPost]
        public ActionResult UpLoad(HttpPostedFileBase filebase)
        {

            HttpPostedFileBase file = Request.Files["file"];//获取上传的文件
            string result = string.Empty;
            string strConn;
            strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + file + "; " + "Extended Properties=Excel 8.0;";
            OleDbDataAdapter myCommand = new OleDbDataAdapter("SELECT * FROM [Sheet1$]", strConn);
            DataSet myDataSet = new DataSet();
            myCommand.Fill(myDataSet, "ExcelInfo");
            System.Data.DataTable tab = myDataSet.Tables["ExcelInfo"].DefaultView.ToTable();
            // ...用foreach把tab中数据添加到数据库 省略了如果是多表插入,可以调用存储过程.呵呵
            result = "导入成功！";
            JsonResult json = new JsonResult();
            json.Data = result;
            return json;
        }
        /// <summary>
        /// 导入经销商
        /// </summary>
        /// <param name="filebase"></param>
        /// <returns></returns>
        [HttpPost]
        public JsonResult DataImportDistributor(HttpPostedFileBase filebase)
        {
            HttpPostedFileBase file = Request.Files["file"];//获取上传的文件
            string FileName;
            string savePath;
            var json = new Ant.Service.Common.JsonHelper() { Msg = "导入成功", Status = "y", ReUrl = "/Sys/Home/Index" };
            if (file == null || file.ContentLength <= 0)
            {
                json.Status = "n";
                json.Msg = "文件不能为空!";
                return Json(json);

                //ViewBag.error = "文件不能为空";
                //return View();
            }
            else
            {
                string filename = Path.GetFileName(file.FileName);
                int filesize = file.ContentLength;//获取上传文件的大小单位为字节byte
                string fileEx = System.IO.Path.GetExtension(filename);//获取上传文件的扩展名
                string NoFileName = System.IO.Path.GetFileNameWithoutExtension(filename);//获取无扩展名的文件名
                int Maxsize = 10000 * 1024;//定义上传文件的最大空间大小为10M
                string FileType = ".xls,.xlsx";//定义上传文件的类型字符串
                FileName = NoFileName + DateTime.Now.ToString("yyyyMMddhhmmss") + fileEx;
                if (!FileType.Contains(fileEx))
                {

                    json.Status = "n";
                    json.Msg = "文件类型不对，只能导入xls和xlsx格式的文件";
                    return Json(json);
                }
                if (filesize >= Maxsize)
                {
                    json.Status = "n";
                    json.Msg = "上传文件超过10M，不能上传";
                    return Json(json);
                }
                string path = AppDomain.CurrentDomain.BaseDirectory + "upload\\excel\\";
                savePath = Path.Combine(path, FileName);
                file.SaveAs(savePath);
                string strConn;
                //strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + savePath + "; " + "Extended Properties=Excel 8.0;";

                strConn = "Provider=Microsoft.Ace.OleDb.12.0;" + "data source=" + savePath + ";Extended Properties='Excel 12.0; HDR=Yes; IMEX=1'"; //此连接可以操作.xls与.xlsx文件 (支持Excel2003 和 Excel2007 的连接字符串)
                                                                                                                                                   //备注： "HDR=yes;"是说Excel文件的第一行是列名而不是数据，"HDR=No;"正好与前面的相反。
                                                                                                                                                   //      "IMEX=1 "如果列中的数据类型不一致，使用"IMEX=1"可必免数据类型冲突。

                //OleDbDataAdapter myCommand = new OleDbDataAdapter("select * from [Sheet1$]", strConn);
                DataSet myDataSet = new DataSet();
                try
                {
                    //连接串
                    //string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "DataSource=" + Path + ";" + "ExtendedProperties=Excel8.0;";
                    OleDbConnection conn = new OleDbConnection(strConn);
                    conn.Open();
                    //返回Excel的架构，包括各个sheet表的名称,类型，创建时间和修改时间等　
                    DataTable dtSheetName = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "Table" });
                    //包含excel中表名的字符串数组
                    string[] strTableNames = new string[dtSheetName.Rows.Count];
                    for (int k = 0; k < dtSheetName.Rows.Count; k++)
                    {
                        strTableNames[k] = dtSheetName.Rows[k]["TABLE_NAME"].ToString();
                    }
                    OleDbDataAdapter myCommand = null;
                    DataTable dt = new DataTable();
                    //从指定的表明查询数据,可先把所有表明列出来供用户选择
                    string strExcel = "select * from [" + strTableNames[0] + "]";
                    myCommand = new OleDbDataAdapter(strExcel, strConn);

                    //myCommand.Fill(dt);
                    myCommand.Fill(myDataSet, "ExcelInfo");
                }
                catch (Exception ex)
                {
                    json.Status = "n";
                    json.Msg = ex.Message.ToString();
                    return Json(json);
                    //return View();
                }
                DataTable table = myDataSet.Tables["ExcelInfo"].DefaultView.ToTable();
                if (table.Rows.Count > 1000)
                {
                    json.Status = "n";
                    json.Msg = "导入数据不能大于1000条！";
                    return Json(json);
                }
                using (TransactionScope transaction = new TransactionScope())
                {
                    for (int i = 0; i < table.Rows.Count; i++)
                    {

                        var _entity = new EntSYS_DISTRIBUTORS(); var _userenty = new En_SYS_USER(); //当前经销商和用户
                        string distridutorid = Guid.NewGuid().ToString();
                        using (AntORM orm = new AntORM())
                        {
                            orm.db = DataAccessFactory.CreateDataConnection("CyclingItem");
                            RequestModel res = new RequestModel
                            {
                                newSt = new SqlNote() { Author = "季健国", NewSt = new System.Diagnostics.StackTrace(true), SqlDesc = "获取公告信息" }
                            };
                            string name = table.Rows[i]["经销商名称"].ToString();
                            var distype = table.Rows[i]["经销商类型"].ToString();
                            string discode = table.Rows[i]["经销商编号"].ToString();
                            var proudctline1 = table.Rows[i]["轮拖轮式收条线联系人"].ToString();// 601615
                            var proudctline2 = table.Rows[i]["履带插秧条线联系人"].ToString();//612611
                            var proudctline3 = table.Rows[i]["玉米收花生机条线联系人"].ToString();//622628
                            var phone1 = table.Rows[i]["轮拖轮式收条线手机号"].ToString();
                            var phone2 = table.Rows[i]["履带插秧条线手机号"].ToString();
                            var phone3 = table.Rows[i]["玉米收花生机条线手机号"].ToString();
                            var saleproduct = table.Rows[i]["销售产品"].ToString();


                            var resulut = orm.Queryable<EntSYS_DISTRIBUTORS>().Where(p => p.NAME == name).FirstOrDefault();
                            if (resulut.IsSuccess)//修改
                            {
                                EntSYS_DISTRIBUTORS dismod = resulut.ResultModel;
                                distridutorid = dismod.ID;
                                _entity.NAME = table.Rows[i]["经销商名称"].ToString();
                                _entity.CODE = table.Rows[i]["经销商编号"].ToString();
                                _entity.Address = table.Rows[i]["地址"].ToString();
                                string pro = table.Rows[i]["所在省"].ToString();
                                var resluts = orm.Queryable<MES.Production.Entity.SYS_CODE_AREA>().Where(p => p.ID == pro).FirstOrDefault(res);
                                if (resluts.IsSuccess)
                                {
                                    MES.Production.Entity.SYS_CODE_AREA mod = resluts.ResultModel;
                                    _entity.Province = mod.ID.ToInt32();
                                }
                                string city = table.Rows[i]["所在市"].ToString();
                                resluts = orm.Queryable<MES.Production.Entity.SYS_CODE_AREA>().Where(p => p.ID == city).FirstOrDefault(res);
                                if (resluts.IsSuccess)
                                {
                                    MES.Production.Entity.SYS_CODE_AREA mod = resluts.ResultModel;
                                    _entity.City = mod.ID.ToInt32();
                                }
                                string area = table.Rows[i]["所在市"].ToString();
                                resluts = orm.Queryable<MES.Production.Entity.SYS_CODE_AREA>().Where(p => p.ID == area).FirstOrDefault(res);
                                if (resluts.IsSuccess)
                                {
                                    MES.Production.Entity.SYS_CODE_AREA mod = resluts.ResultModel;
                                    _entity.Area = mod.ID.ToInt32();
                                }
                                _entity.FenceRadius = table.Rows[i]["电子围栏范围"].ToInt32();
                                _entity.Theodolite = table.Rows[i]["经纬度"].ToString().Replace("，", ",");
                                _entity.SaleCar = table.Rows[i]["销售产品"].ToString();
                                //var reslutdevice = orm.UpdateByLambda<EntSYS_DISTRIBUTORS>(p => _entity, a => a.ID == dismod.ID, res);

                            }
                            else//新增一级经销商和仓库
                            {

                                if (distype == "一级经销商")
                                {
                                    _entity.BUSINESSLEVEL = 2;
                                    int sumcount = orm.Queryable<EntSYS_DISTRIBUTORS>().Where(p => p.BUSINESSLEVEL == 2).Count() + 1;
                                    string num = sumcount.ToString("D5");
                                    _entity.CODE = "003" + num;
                                    _entity.NAME = table.Rows[i]["经销商名称"].ToString();
                                    _entity.DealerNumber = table.Rows[i]["经销商编号"].ToString();
                                    _entity.PARENTID = "32f7a4bd-84de-4587-be29-734d65ad6f70";
                                    _entity.ParentName = "常发农机定位锁车";
                                    _entity.PARENTCODE = "003";

                                    Ent_SYS_USER usermod = new Ent_SYS_USER();
                                    usermod.NAME = table.Rows[i]["经销商联系人"].ToString();
                                    usermod.ACCOUNT = table.Rows[i]["经销商手机号"].ToString();
                                    usermod.DPTID = distridutorid;
                                    usermod.PHONE = table.Rows[i]["经销商手机号"].ToString();
                                    var pwd = PasswordUtils.MD5_Encrypt("111111", 32);
                                    usermod.PASSWORD = pwd;
                                    usermod.USERTYPE = 2;
                                    usermod.CREATEPER = "系统导入";
                                    usermod.CREATEDATE = DateTime.Now;
                                    var reslut1 = orm.Save(usermod, res);


                                }
                                else //仓库
                                {
                                    var reslut = orm.Queryable<EntSYS_DISTRIBUTORS>().Where(p => p.DealerNumber == discode && p.BUSINESSLEVEL == 2).FirstOrDefault(res);
                                    if (reslut.IsSuccess)
                                    {
                                        EntSYS_DISTRIBUTORS entSYS_DISTRIBUTORS = reslut.ResultModel;//父级经销商数据;
                                        int sumcount = orm.Queryable<EntSYS_DISTRIBUTORS>().Where(p => p.BUSINESSLEVEL == 3 && p.PARENTCODE == entSYS_DISTRIBUTORS.CODE).Count() + 1;
                                        string num = sumcount.ToString("D3");
                                        _entity.CODE = entSYS_DISTRIBUTORS.CODE + num;
                                        _entity.PARENTID = entSYS_DISTRIBUTORS.ID;
                                        _entity.ParentName = entSYS_DISTRIBUTORS.NAME;
                                        _entity.PARENTCODE = entSYS_DISTRIBUTORS.CODE;
                                    }
                                    _entity.BUSINESSLEVEL = 3;
                                    _entity.NAME = table.Rows[i]["经销商名称"].ToString();
                                    _entity.DealerNumber = table.Rows[i]["经销商编号"].ToString();

                                }



                                _entity.Address = table.Rows[i]["地址"].ToString();
                                //_entity.ProvinceCity = table.Rows[i]["所在省"].ToString() +","+ table.Rows[i]["所在市"].ToString();

                                string pro = table.Rows[i]["所在省"].ToString();
                                var resluts = orm.Queryable<MES.Production.Entity.SYS_CODE_AREA>().Where(p => p.ID == pro).FirstOrDefault(res);
                                if (resluts.IsSuccess)
                                {
                                    MES.Production.Entity.SYS_CODE_AREA mod = resluts.ResultModel;
                                    _entity.Province = mod.ID.ToInt32();
                                }
                                string city = table.Rows[i]["所在市"].ToString();
                                resluts = orm.Queryable<MES.Production.Entity.SYS_CODE_AREA>().Where(p => p.ID == city).FirstOrDefault(res);
                                if (resluts.IsSuccess)
                                {
                                    MES.Production.Entity.SYS_CODE_AREA mod = resluts.ResultModel;
                                    _entity.City = mod.ID.ToInt32();
                                }
                                string area = table.Rows[i]["所在市"].ToString();
                                resluts = orm.Queryable<MES.Production.Entity.SYS_CODE_AREA>().Where(p => p.ID == area).FirstOrDefault(res);
                                if (resluts.IsSuccess)
                                {
                                    MES.Production.Entity.SYS_CODE_AREA mod = resluts.ResultModel;
                                    _entity.Area = mod.ID.ToInt32();
                                }
                                _entity.FenceRadius = table.Rows[i]["电子围栏范围"].ToInt32();
                                _entity.Theodolite = table.Rows[i]["经纬度"].ToString().ToString().Replace("，", ",");
                                _entity.SaleCar = table.Rows[i]["销售产品"].ToString();
                                _entity.CREATEDATE = DateTime.Now;
                                orm.Save(_entity, res); //保存经销商信息

                            }


                            if (!proudctline1.IsEmpty())
                            {
                                var userreslut = orm.Queryable<En_SYS_USER>().Where(p => p.PHONE == phone1).FirstOrDefault(res);
                                if (userreslut.IsSuccess)
                                {
                                    En_SYS_USER user = userreslut.ResultModel;
                                    int struserid = user.ID.ToInt32();
                                    var count = orm.Queryable<EntYW_UserDistributor>().Where(p => p.distributorId == distridutorid && p.userId == struserid).Count();
                                    if (count == 0)
                                    {
                                        EntYW_UserDistributor userdismod = new EntYW_UserDistributor();
                                        userdismod.ID = Guid.NewGuid();
                                        userdismod.userId = struserid;
                                        userdismod.distributorId = distridutorid;
                                        userdismod.ProductLine = 601;
                                        userdismod.ProductLine1 = 615;
                                        userdismod.add_name = "系统导入";
                                        var reslut1 = orm.Save(userdismod, res);
                                    }
                                    else
                                    {
                                        var updatereslut = orm.UpdateByLambda<EntYW_UserDistributor>(p => new EntYW_UserDistributor { ProductLine = 601, ProductLine1 = 615 }, f => f.distributorId == distridutorid && f.userId == struserid, res);
                                    }
                                }
                                else
                                {
                                    En_SYS_USER usermod = new En_SYS_USER();
                                    usermod.NAME = proudctline1;
                                    usermod.ACCOUNT = phone1;
                                    usermod.PHONE = phone1;
                                    var pwd = PasswordUtils.MD5_Encrypt("111111", 32);
                                    usermod.PASSWORD = pwd;
                                    usermod.USERTYPE = 2;
                                    usermod.CREATEPER = "系统导入";
                                    usermod.CREATEDATE = DateTime.Now;
                                    var reslut = orm.SaveHaveNull(usermod, res);
                                    if (reslut.IsSuccess)
                                    {
                                        int struserid = reslut.RecordNum;
                                        var count = orm.Queryable<EntYW_UserDistributor>().Where(p => p.distributorId == distridutorid && p.userId == struserid).Count();
                                        if (count == 0)
                                        {
                                            EntYW_UserDistributor userdismod = new EntYW_UserDistributor();
                                            userdismod.ID = Guid.NewGuid();
                                            userdismod.userId = struserid;
                                            userdismod.distributorId = distridutorid;
                                            userdismod.ProductLine = 601;
                                            userdismod.ProductLine1 = 615;
                                            userdismod.add_name = "系统导入";
                                            var reslut1 = orm.Save(userdismod, res);
                                        }
                                        else
                                        {
                                            var updatereslut = orm.UpdateByLambda<EntYW_UserDistributor>(p => new EntYW_UserDistributor { ProductLine = 601, ProductLine1 = 615 }, f => f.distributorId == distridutorid && f.userId == struserid, res);
                                        }
                                    }
                                }
                            }

                            if (!proudctline2.IsEmpty())
                            {
                                var userreslut = orm.Queryable<En_SYS_USER>().Where(p => p.PHONE == phone2).FirstOrDefault(res);
                                if (userreslut.IsSuccess)
                                {
                                    En_SYS_USER user = userreslut.ResultModel;
                                    int struserid = user.ID.ToInt32();
                                    var count = orm.Queryable<EntYW_UserDistributor>().Where(p => p.distributorId == distridutorid && p.userId == struserid).Count();
                                    if (count == 0)
                                    {
                                        EntYW_UserDistributor userdismod = new EntYW_UserDistributor();
                                        userdismod.ID = Guid.NewGuid();
                                        userdismod.userId = struserid;
                                        userdismod.distributorId = distridutorid;
                                        userdismod.ProductLine2 = 612;
                                        userdismod.ProductLine3 = 611;
                                        userdismod.add_name = "系统导入";
                                        var reslut1 = orm.Save(userdismod, res);
                                    }
                                    else
                                    {
                                        var updatereslut = orm.UpdateByLambda<EntYW_UserDistributor>(p => new EntYW_UserDistributor { ProductLine2 = 612, ProductLine3 = 611 }, f => f.distributorId == distridutorid && f.userId == struserid, res);
                                    }
                                }
                                else
                                {
                                    En_SYS_USER usermod = new En_SYS_USER();
                                    usermod.NAME = proudctline1;
                                    usermod.ACCOUNT = phone1;
                                    usermod.PHONE = phone1;
                                    var pwd = PasswordUtils.MD5_Encrypt("111111", 32);
                                    usermod.PASSWORD = pwd;
                                    usermod.USERTYPE = 2;
                                    usermod.CREATEPER = "系统导入";
                                    usermod.CREATEDATE = DateTime.Now;
                                    var reslut = orm.SaveHaveNull(usermod, res);
                                    if (reslut.IsSuccess)
                                    {
                                        int struserid = reslut.RecordNum;
                                        var count = orm.Queryable<EntYW_UserDistributor>().Where(p => p.distributorId == distridutorid && p.userId == struserid).Count();
                                        if (count == 0)
                                        {
                                            EntYW_UserDistributor userdismod = new EntYW_UserDistributor();
                                            userdismod.ID = Guid.NewGuid();
                                            userdismod.userId = struserid;
                                            userdismod.distributorId = distridutorid;
                                            userdismod.ProductLine2 = 612;
                                            userdismod.ProductLine3 = 611;
                                            userdismod.add_name = "系统导入";
                                            var reslut1 = orm.Save(userdismod, res);
                                        }
                                        else
                                        {
                                            var updatereslut = orm.UpdateByLambda<EntYW_UserDistributor>(p => new EntYW_UserDistributor { ProductLine2 = 612, ProductLine3 = 611 }, f => f.distributorId == distridutorid && f.userId == struserid, res);
                                        }
                                    }
                                }
                            }

                            if (!proudctline3.IsEmpty())
                            {
                                var userreslut = orm.Queryable<En_SYS_USER>().Where(p => p.PHONE == phone3).FirstOrDefault(res);
                                if (userreslut.IsSuccess)
                                {
                                    En_SYS_USER user = userreslut.ResultModel;
                                    int struserid = user.ID.ToInt32();
                                    var count = orm.Queryable<EntYW_UserDistributor>().Where(p => p.distributorId == distridutorid && p.userId == struserid).Count();
                                    if (count == 0)
                                    {
                                        EntYW_UserDistributor userdismod = new EntYW_UserDistributor();
                                        userdismod.ID = Guid.NewGuid();
                                        userdismod.userId = struserid;
                                        userdismod.distributorId = distridutorid;
                                        userdismod.ProductLine4 = 622;
                                        userdismod.ProductLine5 = 628;
                                        userdismod.add_name = "系统导入";
                                        var reslut1 = orm.Save(userdismod, res);
                                    }
                                    else
                                    {
                                        var updatereslut = orm.UpdateByLambda<EntYW_UserDistributor>(p => new EntYW_UserDistributor { ProductLine4 = 622, ProductLine5 = 628 }, f => f.distributorId == distridutorid && f.userId == struserid, res);
                                    }
                                }
                                else
                                {
                                    En_SYS_USER usermod = new En_SYS_USER();
                                    usermod.NAME = proudctline1;
                                    usermod.ACCOUNT = phone1;
                                    usermod.PHONE = phone1;
                                    var pwd = PasswordUtils.MD5_Encrypt("111111", 32);
                                    usermod.PASSWORD = pwd;
                                    usermod.USERTYPE = 2;
                                    usermod.CREATEPER = "系统导入";
                                    usermod.CREATEDATE = DateTime.Now;
                                    var reslut = orm.SaveHaveNull(usermod, res);
                                    if (reslut.IsSuccess)
                                    {
                                        int struserid = reslut.RecordNum;
                                        var count = orm.Queryable<EntYW_UserDistributor>().Where(p => p.distributorId == distridutorid && p.userId == struserid).Count();
                                        if (count == 0)
                                        {
                                            EntYW_UserDistributor userdismod = new EntYW_UserDistributor();
                                            userdismod.ID = Guid.NewGuid();
                                            userdismod.userId = struserid;
                                            userdismod.distributorId = distridutorid;
                                            userdismod.ProductLine4 = 622;
                                            userdismod.ProductLine5 = 628;
                                            userdismod.add_name = "系统导入";
                                            var reslut1 = orm.Save(userdismod, res);
                                        }
                                        else
                                        {
                                            var updatereslut = orm.UpdateByLambda<EntYW_UserDistributor>(p => new EntYW_UserDistributor { ProductLine4 = 622, ProductLine5 = 628 }, f => f.distributorId == distridutorid && f.userId == struserid, res);
                                        }
                                    }
                                }
                            }



                        }
                    }

                    transaction.Complete();
                    json.Status = "y";
                    json.Msg = string.Format("成功导入{0}条数据", table.Rows.Count);
                }
            }
            return Json(json);
        }


        public FileResult ExportSchoolExcel2()
        {
            var schoolInfo = new { RaceName = "2021年武进区迎新春中长跑比赛", SchoolName = "少体校", Leader = "蒋建良", Coacher = "谢川,张晓娜", Contact = "15961169089" };

            List<dynamic> sporterInfo = new List<dynamic>();

            sporterInfo.Add(new
            {
                RaceNo = "2211",
                Name = "徐文彬",
                Sex = "男",
                RegNo = "20192026051",
                IDCard = "32048320090412131X",
                Grade = "小五",
                Group = "小学甲组",
                Project1 = "1500米",
                Project2 = "",
                Project3 = "",
                Project4 = "",
                QRCode = "测试",
                SchoolName = "测试"
            });

            var ms = ExportExcelHelper.ExportSchoolExcel(schoolInfo, sporterInfo);

            return File(ms, "application/vnd.ms-excel", "武进区学校体育教练员统计表.xls");
        }

        /// <summary>
        /// 将Excel导入绑定农机数据库
        /// </summary>
        /// <param name="filebase"></param>
        /// <returns></returns>
        [HttpPost]
        public JsonResult DataImportAll(HttpPostedFileBase filebase)
        {
            HttpPostedFileBase file = Request.Files["file"];//获取上传的文件
            string result = string.Empty;
            string distributorsid = Request["distributorsid"];
            string distributorName = Request["distributorName"];
            string saleid = Request["saleid"];
            string FileName;
            string savePath;
            var json = new Ant.Service.Common.JsonHelper() { Msg = "导入成功", Status = "y", ReUrl = "/Sys/Home/Index" };
            if (file == null || file.ContentLength <= 0)
            {
                json.Status = "n";
                json.Msg = "文件不能为空!";
                return Json(json);

                //ViewBag.error = "文件不能为空";
                //return View();
            }
            else
            {
                string filename = Path.GetFileName(file.FileName);
                int filesize = file.ContentLength;//获取上传文件的大小单位为字节byte
                string fileEx = System.IO.Path.GetExtension(filename);//获取上传文件的扩展名
                string NoFileName = System.IO.Path.GetFileNameWithoutExtension(filename);//获取无扩展名的文件名
                int Maxsize = 10000 * 1024;//定义上传文件的最大空间大小为10M
                string FileType = ".xls,.xlsx";//定义上传文件的类型字符串
                FileName = NoFileName + DateTime.Now.ToString("yyyyMMddhhmmss") + fileEx;
                if (!FileType.Contains(fileEx))
                {

                    json.Status = "n";
                    json.Msg = "文件类型不对，只能导入xls和xlsx格式的文件";
                    return Json(json);
                }
                if (filesize >= Maxsize)
                {
                    json.Status = "n";
                    json.Msg = "上传文件超过10M，不能上传";
                    return Json(json);
                }
                string path = AppDomain.CurrentDomain.BaseDirectory + "upload\\excel\\";
                savePath = Path.Combine(path, FileName);
                file.SaveAs(savePath);
                string strConn;
                strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + savePath + "; " + "Extended Properties=Excel 8.0;";

                //strConn = "Provider=Microsoft.Ace.OleDb.12.0;" + "data source=" + savePath + ";Extended Properties='Excel 12.0; HDR=Yes; IMEX=1'"; //此连接可以操作.xls与.xlsx文件 (支持Excel2003 和 Excel2007 的连接字符串)
                //备注： "HDR=yes;"是说Excel文件的第一行是列名而不是数据，"HDR=No;"正好与前面的相反。
                //      "IMEX=1 "如果列中的数据类型不一致，使用"IMEX=1"可必免数据类型冲突。

                //OleDbDataAdapter myCommand = new OleDbDataAdapter("select * from [Sheet1$]", strConn);
                DataSet myDataSet = new DataSet();
                try
                {
                    //连接串
                    //string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "DataSource=" + Path + ";" + "ExtendedProperties=Excel8.0;";
                    OleDbConnection conn = new OleDbConnection(strConn);
                    conn.Open();
                    //返回Excel的架构，包括各个sheet表的名称,类型，创建时间和修改时间等　
                    DataTable dtSheetName = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "Table" });
                    //包含excel中表名的字符串数组
                    string[] strTableNames = new string[dtSheetName.Rows.Count];
                    for (int k = 0; k < dtSheetName.Rows.Count; k++)
                    {
                        strTableNames[k] = dtSheetName.Rows[k]["TABLE_NAME"].ToString();
                    }
                    OleDbDataAdapter myCommand = null;
                    DataTable dt = new DataTable();
                    //从指定的表明查询数据,可先把所有表明列出来供用户选择
                    string strExcel = "select * from [" + strTableNames[0] + "]";
                    myCommand = new OleDbDataAdapter(strExcel, strConn);

                    //myCommand.Fill(dt);
                    myCommand.Fill(myDataSet, "ExcelInfo");
                }
                catch (Exception ex)
                {
                    json.Status = "n";
                    json.Msg = ex.Message.ToString();
                    return Json(json);
                    //return View();
                }
                DataTable table = myDataSet.Tables["ExcelInfo"].DefaultView.ToTable();
                //if (table.Rows.Count > 1000)
                //{
                //    json.Status = "n";
                //    json.Msg = "导入数据不能大于1000条！";
                //    return Json(json);
                //}
                EntSYS_DISTRIBUTORS entSYS_DISTRIBUTORS = new EntSYS_DISTRIBUTORS();

                using (AntORM orm = new AntORM())
                {
                    orm.db = DataAccessFactory.CreateDataConnection("CyclingItem");
                    var res = new RequestModel
                    {
                        newSt = new SqlNote() { Author = "季健国", NewSt = new System.Diagnostics.StackTrace(true), SqlDesc = "获取经销商信息" }
                    };
                    var reslutdis = orm.Queryable<EntSYS_DISTRIBUTORS>().Where(p => p.ID == distributorsid).FirstOrDefault(res);
                    if (reslutdis.IsSuccess)
                    {
                        entSYS_DISTRIBUTORS = reslutdis.ResultModel;
                    }

                }

                int n = 0; int m = 0;

                foreach (DataRow dr in table.Rows)
                {
                    if (dr["企业编号"].IfNotNull())
                    {
                        string dealercode = dr["企业编号"].ToString().Trim();
                        if (!dealercode.IsEmpty())
                        {
                            using (AntORM orm = new AntORM())
                            {
                                orm.db = DataAccessFactory.CreateDataConnection("CyclingItem");
                                RequestModel res = new RequestModel();
                                res.newSt = new SqlNote() { Author = "季健国", NewSt = new System.Diagnostics.StackTrace(true), SqlDesc = "新增经销商数据" };
                                List<string> userlist = new List<string>();
                                string barcode = dr["条码"].ToString().Trim(); string carid = dr["卫星定位模块"].ToString().Trim(); string dealername = dr["经销单位"].ToString().Trim();
                                var num = orm.Queryable<EntYW_Bicycle>().Where(p => p.carBar == barcode).FirstOrDefault(res);
                                if (num.IsSuccess)
                                {
                                    EntYW_Bicycle carmod = num.ResultModel;
                                    if (carmod.DistributorsID.IsEmpty())
                                    {
                                        var reslut = orm.Queryable<EntSYS_DISTRIBUTORS>().Where(p => p.DealerNumber == dealercode && p.PARENTID == "32f7a4bd-84de-4587-be29-734d65ad6f70").FirstOrDefault(res);
                                        if (reslut.IsSuccess)
                                        {
                                            EntSYS_DISTRIBUTORS entdid = reslut.ResultModel;
                                            var resluts = orm.UpdateByLambda<EntYW_Bicycle>(p => new EntYW_Bicycle() { DistributorsID = entdid.ID, DistributorsName = dealername, CustomerCode = dealercode, CustomerName = entdid.NAME }, a => a.carBar == barcode, res);
                                            n++;
                                        }
                                        else
                                        {
                                            var reslutss = orm.UpdateByLambda<EntYW_Bicycle>(p => new EntYW_Bicycle() { CustomerCode = dealercode, CustomerName = dealername }, a => a.carBar == barcode, res);
                                        }
                                    }
                                }
                                else
                                {
                                    var reslut = orm.Queryable<EntSYS_DISTRIBUTORS>().Where(p => p.DealerNumber == dealercode && p.PARENTID == "32f7a4bd-84de-4587-be29-734d65ad6f70").FirstOrDefault(res);
                                    if (reslut.IsSuccess)
                                    {
                                        EntSYS_DISTRIBUTORS dismod = reslut.ResultModel;
                                        string codebar = barcode.Substring(0, 3);
                                        if (codebar == "628")
                                        {
                                            EntYW_Bicycle mod = new EntYW_Bicycle();
                                            mod.ID = Guid.NewGuid();
                                            mod.carBar = barcode.ToString();
                                            //mod.CarName = dr["productName"].ToString().Trim();
                                            int cartype = 2; string productName = "花生机";
                                            mod.DistributorsID = dismod.ID;
                                            mod.DistributorsName = dismod.NAME;
                                            mod.WarehouseID = dismod.ID;
                                            mod.WarehouseName = dismod.NAME;
                                            mod.ProductLine = 628;
                                            mod.CustomerCode = dealercode;
                                            mod.CustomerName = dealername;
                                            mod.CarID = carid.ToString();
                                            mod.CarName = productName;
                                            mod.CarModel = "CF326";
                                            if (dr["发货日期"] != null)
                                                mod.productionDate = dr["发货日期"].ToDateTime();
                                            mod.FactoryNumber = dr["出厂编号"].ToString().Trim();
                                            mod.OutDate = dr["发货日期"].ToDateTime();
                                            mod.LeaseStatus = 0;
                                            mod.CarSate = CarState.OutStock.GetHashCode();
                                            mod.LockState = false;
                                            mod.carType = cartype;
                                            mod.CreateDate = DateTime.Now;
                                            mod.Createper = "系统导入";
                                            mod.FenceState = 0;
                                            var flag = orm.Save(mod, res);
                                            if (flag.IsSuccess)
                                                m++;
                                        }
                                        else
                                        {
                                          
                                        }
                                    }
                                }
                            }
                        }
                    }

                }

                json.Status = "y";
                json.Msg = string.Format("成功导入{0}条数据,修改数据{1}条数据", m, n);


            }
            return Json(json);
        }
    }
}