Пример #1
0
        /// <summary>
        /// 根据路径获取Excel数据,并转为excel
        /// </summary>
        /// <param name="hpf"></param>
        /// <returns></returns>
        private DataSet GetDataFromExcel(HttpPostedFileBase hpf)
        {
            string uploadFolderPath = Runbow.TWS.Common.Constants.UPLOAD_FOLDER_PATH;
            string targetPath       = Path.Combine(Runbow.TWS.Common.Constants.UPLOAD_FOLDER_PATH, base.UserInfo.ProjectID.ToString(), Runbow.TWS.Common.Constants.TEMPFOLDER);

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

            string fileName = base.UserInfo.ID.ToString() + "_" + DateTime.Now.ToString("yyyyMMddHHmmss") + "_" + Path.GetFileName(hpf.FileName);
            string fullPath = Path.Combine(targetPath, fileName);

            hpf.SaveAs(fullPath);
            hpf.InputStream.Close();

            Runbow.TWS.Common.ExcelHelper excelHelper = new Runbow.TWS.Common.ExcelHelper(fullPath);
            DataSet ds = excelHelper.GetAllDataFromAllSheets();

            excelHelper.Dispose();
            MyFile.Delete(fullPath);


            return(ds);
        }
Пример #2
0
        public ActionResult SettledCompare(SettledCompareViewModel vm)
        {
            vm.Customers = ApplicationConfigHelper.GetProjectUserCustomers(base.UserInfo.ProjectID, base.UserInfo.ID)
                           .Select(c => new SelectListItem()
            {
                Value = c.CustomerID.ToString(), Text = c.CustomerName
            });
            vm.DisplyMessage = vm.SettledType == 0 ? "导入应收结算比对(客户运单号|运费|点费|燃油附加费|其他费用)" : "导入应付结算比对(客户运单号|运费|点费|燃油附加费|其他费用)";

            if (Request.Files.Count > 0)
            {
                HttpPostedFileBase hpf = Request.Files[0] as HttpPostedFileBase;
                if (hpf.ContentLength > 0)
                {
                    string uploadFolderPath = Runbow.TWS.Common.Constants.UPLOAD_FOLDER_PATH;
                    string targetPath       = Path.Combine(Runbow.TWS.Common.Constants.UPLOAD_FOLDER_PATH, base.UserInfo.ProjectID.ToString(), Runbow.TWS.Common.Constants.TEMPFOLDER);
                    if (!Directory.Exists(targetPath))
                    {
                        Directory.CreateDirectory(targetPath);
                    }

                    string fileName = base.UserInfo.ID.ToString() + "_" + DateTime.Now.ToString("yyyyMMddHHmmss") + "_" + Path.GetFileName(hpf.FileName);
                    string fullPath = Path.Combine(targetPath, fileName);
                    hpf.SaveAs(fullPath);
                    hpf.InputStream.Close();

                    Runbow.TWS.Common.ExcelHelper excelHelper = new Runbow.TWS.Common.ExcelHelper(fullPath);
                    DataSet ds = excelHelper.GetAllDataFromAllSheets();
                    excelHelper.Dispose();
                    MyFile.Delete(fullPath);

                    if (ds == null || ds.Tables == null || ds.Tables[0].Rows.Count == 0)
                    {
                        vm.ErrorMessage = "Excel文件内容有误";
                    }
                    else
                    {
                        IList <SettledPodCompare> settledPodCompares = new List <SettledPodCompare>();
                        StringBuilder             errorMessage       = new StringBuilder();
                        for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                        {
                            string  customerOrderNumber = string.Empty;
                            decimal excelShipAmt        = 0;
                            decimal excelBafAmt         = 0;
                            decimal excelPointAmt       = 0;
                            decimal excelOtherAmt       = 0;

                            if (ds.Tables[0].Columns.Contains("客户运单号"))
                            {
                                customerOrderNumber = ds.Tables[0].Rows[i]["客户运单号"].ToString();
                                if (string.IsNullOrEmpty(customerOrderNumber))
                                {
                                    errorMessage.Append("Excel中第").Append(i + 1).Append("行客户运单号列为空,请重新编辑Excel");
                                }
                            }
                            else
                            {
                                errorMessage.Append("Excel中无客户运单号列,请重新编辑").Append("<br />");
                            }

                            if (ds.Tables[0].Columns.Contains("运费"))
                            {
                                decimal tempShipAmt;
                                if (decimal.TryParse(ds.Tables[0].Rows[i]["运费"].ToString().Trim(), out tempShipAmt))
                                {
                                    excelShipAmt = tempShipAmt;
                                }
                            }

                            if (ds.Tables[0].Columns.Contains("点费"))
                            {
                                decimal tempPointAmt;
                                if (decimal.TryParse(ds.Tables[0].Rows[i]["点费"].ToString().Trim(), out tempPointAmt))
                                {
                                    excelPointAmt = tempPointAmt;
                                }
                            }

                            if (ds.Tables[0].Columns.Contains("燃油附加费"))
                            {
                                decimal tempBafAmt;
                                if (decimal.TryParse(ds.Tables[0].Rows[i]["燃油附加费"].ToString().Trim(), out tempBafAmt))
                                {
                                    excelBafAmt = tempBafAmt;
                                }
                            }

                            if (ds.Tables[0].Columns.Contains("其他费用"))
                            {
                                decimal tempOtherAmt;
                                if (decimal.TryParse(ds.Tables[0].Rows[i]["其他费用"].ToString().Trim(), out tempOtherAmt))
                                {
                                    excelOtherAmt = tempOtherAmt;
                                }
                            }

                            settledPodCompares.Add(new SettledPodCompare()
                            {
                                CustomerOrderNumber = customerOrderNumber,
                                CompareShipAmt      = excelShipAmt,
                                CompareBAFAmt       = excelBafAmt,
                                ComparePointAmt     = excelPointAmt,
                                CompareOtherAmt     = excelOtherAmt,
                                CompareTotalAmt     = excelShipAmt + excelBafAmt + excelPointAmt + excelOtherAmt,
                                SettledType         = vm.SettledType,
                                ProjectID           = base.UserInfo.ProjectID,
                                CustomerOrShipperID = vm.SettledType == 0 ? vm.CustomerID : vm.ShipperID,
                                RelatedCustomerID   = vm.CustomerID
                            });
                        }

                        if (errorMessage.Length > 0)
                        {
                            vm.ErrorMessage = errorMessage.ToString();
                            return(View(vm));
                        }

                        var Response = new SettledService().GetSettledPodByCondition(new GetSettledPodByConditionRequest()
                        {
                            CustomerOrderNumberCollection = settledPodCompares.Select(s => s.CustomerOrderNumber),
                            SettledType = vm.SettledType,
                            CustomerID  = vm.CustomerID,
                            ShipperID   = vm.ShipperID
                        });

                        if (Response.IsSuccess)
                        {
                            settledPodCompares.Each((i, s) => {
                                var tempSettledPod   = Response.Result.FirstOrDefault(r => r.CustomerOrderNumber == s.CustomerOrderNumber);
                                s.ActualDeliveryDate = DateTime.MinValue;
                                if (tempSettledPod != null)
                                {
                                    s.SystemNumber          = tempSettledPod.SystemNumber;
                                    s.CustomerOrShipperName = tempSettledPod.CustomerOrShipperName;
                                    s.PodID              = tempSettledPod.PodID;
                                    s.ID                 = tempSettledPod.ID;
                                    s.SettledNumber      = tempSettledPod.SettledNumber;
                                    s.StartCityID        = tempSettledPod.StartCityID;
                                    s.StartCityName      = tempSettledPod.StartCityName;
                                    s.EndCityID          = tempSettledPod.EndCityID;
                                    s.EndCityName        = tempSettledPod.EndCityName;
                                    s.ShipperTypeID      = tempSettledPod.ShipperTypeID;
                                    s.ShipperTypeName    = tempSettledPod.ShipperTypeName;
                                    s.PODTypeID          = tempSettledPod.PODTypeID;
                                    s.PODTypeName        = tempSettledPod.PODTypeName;
                                    s.TtlOrTplID         = tempSettledPod.TtlOrTplID;
                                    s.TtlOrTplName       = tempSettledPod.TtlOrTplName;
                                    s.ActualDeliveryDate = tempSettledPod.ActualDeliveryDate ?? DateTime.MinValue;
                                    s.BoxNumber          = tempSettledPod.BoxNumber;
                                    s.Weight             = tempSettledPod.Weight;
                                    s.GoodsNumber        = tempSettledPod.GoodsNumber;
                                    s.Volume             = tempSettledPod.Volume;
                                    s.Remark             = tempSettledPod.Remark;
                                    s.ShipAmt            = tempSettledPod.ShipAmt;
                                    s.BAFAmt             = tempSettledPod.BAFAmt;
                                    s.PointAmt           = tempSettledPod.PointAmt;
                                    s.OtherAmt           = tempSettledPod.OtherAmt;
                                    s.TotalAmt           = tempSettledPod.TotalAmt;
                                    s.Str1               = tempSettledPod.Str1;
                                    s.Str2               = tempSettledPod.Str2;
                                    s.Str3               = tempSettledPod.Str3;
                                    s.Str4               = tempSettledPod.Str4;
                                    s.Str5               = tempSettledPod.Str5;
                                    s.DateTime1          = tempSettledPod.DateTime1;
                                    s.DateTime2          = tempSettledPod.DateTime2;
                                    s.Creator            = tempSettledPod.Creator;
                                    s.CreateTime         = tempSettledPod.CreateTime;
                                    s.InvoiceID          = tempSettledPod.InvoiceID;
                                    s.IsAudit            = tempSettledPod.IsAudit;
                                }
                            });

                            vm.SettledPodCompareCollection = settledPodCompares;
                        }
                        else
                        {
                            vm.ErrorMessage = "获取系统结算信息失败!";
                        }
                    }
                }
                else
                {
                    vm.ErrorMessage = "Excel文件内容为空";
                }
            }
            else
            {
                vm.ErrorMessage = "请选择excel文件";
            }

            return(View(vm));
        }
Пример #3
0
        public string PodImport(string customer)
        {
            string uploadFolderPath = Runbow.TWS.Common.Constants.UPLOAD_FOLDER_PATH;
            string targetPath = Path.Combine(uploadFolderPath, "AMSTempFile");
            string url = string.Empty, actualNameInServer = string.Empty, ext = string.Empty;

            if (string.IsNullOrEmpty(targetPath) || !Path.IsPathRooted(targetPath))
            {
                return(new { msg = "程序出错!" }.ToJsonString());
            }

            if (Request.Files.Count > 0)
            {
                HttpPostedFileBase hpf = Request.Files[0] as HttpPostedFileBase;
                if (hpf.ContentLength > 0)
                {
                    if (!Directory.Exists(targetPath))
                    {
                        Directory.CreateDirectory(targetPath);
                    }
                    ext = Path.GetExtension(hpf.FileName);
                    if (!ext.ToLower().Equals(".xls") && !ext.ToLower().Equals(".xlsx"))
                    {
                        return(new { msg = "请选择Excel格式的文件" }.ToJsonString());
                    }
                    actualNameInServer = DateTime.Now.ToString("yyyyMMddHHmmss") + ext;
                    url = Path.Combine(targetPath, actualNameInServer);
                    hpf.SaveAs(url);
                    hpf.InputStream.Close();
                    Runbow.TWS.Common.ExcelHelper excelHelper = new Runbow.TWS.Common.ExcelHelper(url);
                    DataSet ds = excelHelper.GetAllDataFromAllSheets();
                    excelHelper.Dispose();
                    MyFile.Delete(url);
                    IEnumerable <Pod> pods = null;
                    List <Pod>        pp   = new List <Pod>();
                    if (ds != null && ds.Tables[0] != null && ds.Tables[0].Rows.Count > 0)
                    {
                        StringBuilder  results = new StringBuilder();
                        BaiXingService bxService = new BaiXingService();
                        string         str1, str37, str4, str5, str36, str10, str11, str38, str2, customerOrderNumber;
                        string         systemNumber = "";
                        long           podID        = 0;
                        Double         weight       = 0;
                        for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                        {
                            DataRow row = ds.Tables[0].Rows[i];
                            //调度运单号,异常收件原因,取件方联系人,取件方联系电话,取件方联系地址,到件方联系人,到件方联系电话,到件方联系地址
                            //到件方联系电话,货物名称,货物重量,订单备注,委托方联系人,委托方客户卡号
                            customerOrderNumber = row["委托方联系人"].ToString().Trim();  //客户订单号
                            if (string.IsNullOrEmpty(customerOrderNumber))
                            {
                                continue;
                            }
                            else
                            {
                                Runbow.TWS.MessageContracts.POD.GetBaiXingRequest bxRequest = new MessageContracts.POD.GetBaiXingRequest()
                                {
                                    CustomerOrderNumber = customerOrderNumber
                                };
                                Response <IEnumerable <Pod> > Podresponse = bxService.GetBaiXingPod(bxRequest);
                                if (Podresponse.IsSuccess && Podresponse.Result.Count() > 0)
                                {
                                    #region pod赋值
                                    //快递公司不是顺丰直接过滤
                                    if (!Podresponse.Result.First().Str17.Contains("顺丰"))
                                    {
                                        continue;
                                    }
                                    //对应的快递信息PODID
                                    if (Podresponse.Result.Count() > 1)
                                    {
                                        podID = Podresponse.Result.LastOrDefault().ID;
                                    }

                                    //PodID = Podresponse.Result.First().ID;
                                    systemNumber = Podresponse.Result.First().SystemNumber;
                                    try
                                    {
                                        weight = Convert.ToDouble(row["货物重量"].ToString().Trim());      //重量(公斤)
                                    }
                                    catch
                                    {
                                    }

                                    str1  = row["调度运单号"].ToString().Trim();   //快递单号
                                    str37 = row["异常收件原因"].ToString().Trim();  //异常说明
                                    str4  = row["取件方联系人"].ToString().Trim();  //发货方联系人
                                    str5  = row["取件方联系电话"].ToString().Trim(); //发货方电话
                                    str36 = row["取件方联系地址"].ToString().Trim(); //发货方详细地址
                                    str10 = row["到件方联系人"].ToString().Trim();  //收货方联系人
                                    str11 = row["到件方联系电话"].ToString().Trim(); //收货方电话
                                    str38 = row["到件方联系地址"].ToString().Trim(); //收货方详细地址
                                    str2  = row["货物名称"].ToString().Trim();    //商品品名
                                    //str1 = row["订单备注"].ToString().Trim();      //备注信息
                                    //str1 = row["委托方客户卡号"].ToString().Trim(); //委托方客户卡号
                                    //p.CustomerID = 40;

                                    Pod p = new Pod()
                                    {
                                        ID                  = podID,
                                        ProjectID           = 1,
                                        SystemNumber        = systemNumber + "-1",
                                        CustomerOrderNumber = customerOrderNumber,
                                        CustomerID          = Podresponse.Result.First().CustomerID, //同一客户,分单机制
                                        CustomerName        = Podresponse.Result.First().CustomerName,
                                        ActualDeliveryDate  = Podresponse.Result.First().ActualDeliveryDate,
                                        StartCityID         = 1, //Podresponse.Result.First().StartCityID,
                                        StartCityName       = Podresponse.Result.First().StartCityName,
                                        EndCityID           = 2, //Podresponse.Result.First().EndCityID,
                                        EndCityName         = Podresponse.Result.First().EndCityName,
                                        PODStateID          = Podresponse.Result.First().PODStateID,
                                        PODStateName        = Podresponse.Result.First().PODStateName,
                                        ShipperTypeID       = 36,
                                        ShipperTypeName     = "快递",
                                        BoxNumber           = Podresponse.Result.First().BoxNumber,
                                        Weight              = weight,
                                        Volume              = Podresponse.Result.First().Volume,
                                        GoodsNumber         = Podresponse.Result.First().GoodsNumber,
                                        Creator             = "bxadmin",
                                        CreateTime          = DateTime.Now,
                                        Str1                = str1,                                                                //快递运单号
                                        Str2                = str2,                                                                //商品品名
                                        Str3                = Podresponse.Result.First().Str3,                                     //交易金额
                                        Str15               = Podresponse.Result.First().Str15,                                    //实际快递费用
                                        Str4                = str4,                                                                //发货联系人
                                        Str5                = str5,                                                                //发货电话
                                        Str6                = Podresponse.Result.First().Str6,                                     //发货省
                                        Str7                = Podresponse.Result.First().Str7,                                     //发货市
                                        Str8                = Podresponse.Result.First().Str8,                                     //发货区
                                        Str36               = str36,                                                               //发货详细地址
                                        Str10               = str10,                                                               //收货联系人
                                        Str11               = str11,                                                               //收货电话
                                        Str12               = Podresponse.Result.First().Str12,                                    //收货省
                                        Str13               = Podresponse.Result.First().Str13,                                    //收货市
                                        Str14               = Podresponse.Result.First().Str14,                                    //收货区
                                        Str38               = str38,                                                               //收货详细地址
                                        Str16               = Podresponse.Result.First().Str16,                                    //投保
                                        Str17               = Podresponse.Result.First().Str17,                                    //podRequest.courier,//快递公司
                                        Str18               = Podresponse.Result.First().Str18,                                    //预估快递费用
                                        Str19               = Podresponse.Result.First().Str19,                                    //来源应用
                                        Str37               = string.IsNullOrEmpty(str37) ? row["订单备注"].ToString().Trim() : str37, //备注信息
                                        PODTypeID           = 7,
                                        PODTypeName         = "出货运单",
                                        TtlOrTplID          = 26,
                                        TtlOrTplName        = "LTL",
                                        Type                = 1,//分单标识(默认为2)
                                        Str20               = null,
                                    };
                                    #endregion ;

                                    pp.Add(p);
                                }
                            }
                        }
                        var response = new BaiXingService().AddPods_BX(new AddPodsRequest()
                        {
                            Pods = pp
                        });
                        if (response.IsSuccess)
                        {
                            StringBuilder orderNumbers = new StringBuilder();
                            foreach (PodKey pk in response.Result)
                            {
                                orderNumbers.Append(pk.CustomerOrderNumber + ",");
                            }
                            results.Append("成功导入" + response.Result.Count() + "条,百姓运单号:" + orderNumbers.ToString());
                        }
                        else
                        {
                            results.Append("导入失败,请联系管理员.");
                        }
                        return(new { result = results.ToString(), IsSuccess = true }.ToJsonString());
                    }
                }
                return(new { result = "文件内容为空", IsSuccess = false }.ToJsonString());
            }
            return(new { result = "请选择文件", IsSuccess = false }.ToJsonString());
        }
Пример #4
0
        public string ExcelCheck(string customer)
        {
            string   uploadFolderPath = Runbow.TWS.Common.Constants.UPLOAD_FOLDER_PATH;
            string   targetPath = Path.Combine(uploadFolderPath, "AMSTempFile");
            string   url = string.Empty, actualNameInServer = string.Empty, ext = string.Empty;
            DateTime amsDate = new DateTime();

            if (string.IsNullOrEmpty(targetPath) || !Path.IsPathRooted(targetPath))
            {
                return(new { msg = "程序出错!" }.ToJsonString());
            }

            if (Request.Files.Count > 0)
            {
                HttpPostedFileBase hpf = Request.Files[0] as HttpPostedFileBase;
                if (hpf.ContentLength > 0)
                {
                    if (!Directory.Exists(targetPath))
                    {
                        Directory.CreateDirectory(targetPath);
                    }
                    ext = Path.GetExtension(hpf.FileName);
                    if (!ext.ToLower().Equals(".xls") && !ext.ToLower().Equals(".xlsx"))
                    {
                        return(new { msg = "请选择Excel格式的文件" }.ToJsonString());
                    }

                    actualNameInServer = DateTime.Now.ToString("yyyyMMddHHmmss") + ext;
                    url = Path.Combine(targetPath, actualNameInServer);
                    hpf.SaveAs(url);
                    hpf.InputStream.Close();
                    Runbow.TWS.Common.ExcelHelper excelHelper = new Runbow.TWS.Common.ExcelHelper(url);
                    DataSet ds = excelHelper.GetAllDataFromAllSheets();
                    excelHelper.Dispose();
                    MyFile.Delete(url);
                    AMSUploadService service = new AMSUploadService();
                    Response <IEnumerable <AMSUpload> > response = service.GetAMSUpload(new AddAMSUploadRequest()
                    {
                        amsUpload = null
                    });
                    if (ds != null && ds.Tables[0] != null && ds.Tables[0].Rows.Count > 0 && response.Result.Count() > 0)
                    {
                        StringBuilder results = new StringBuilder();
                        StringBuilder ids = new StringBuilder();
                        string        projectName, fileName;
                        for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                        {
                            DataRow row = ds.Tables[0].Rows[i];
                            projectName = row[0].ToString().Trim().ToUpper();
                            fileName    = row[1].ToString().Trim().ToUpper();
                            if (projectName == "AMS_永兴东润")
                            {
                                amsDate  = Convert.ToDateTime(row[2].ToString());
                                fileName = amsDate.ToString("yyyy-MM-") + fileName;
                            }

                            bool isExist = false;
                            foreach (AMSUpload ams in response.Result)
                            {
                                if (projectName == ams.ProjectName.Trim().ToUpper() && fileName == ams.FileName.Trim().ToUpper())
                                {
                                    ids.Append("'" + ams.ID.ToString() + "',");
                                    isExist = true;
                                    //if (projectName != "AMS_永兴东润") //AMS_永兴东润
                                    //{
                                    //    break;
                                    //}
                                }
                            }

                            if (!isExist)
                            {
                                results.Append("[" + row[0].ToString() + "]客户中[" + row[1].ToString() + "]运单号  未上传回单图片<br />");
                            }
                        }
                        if (ids.Length > 0)
                        {
                            service.UpdateAMSUploadStatus(new AddAMSUploadRequest()
                            {
                                Ids = ids.ToString()
                            });
                        }
                        if (results.Length <= 0)
                        {
                            results.Append("回单图片都已上传.");
                        }
                        return(new { result = results.ToString(), IsSuccess = true }.ToJsonString());
                    }
                }
                return(new { result = "文件内容为空", IsSuccess = false }.ToJsonString());
            }
            return(new { result = "请选择文件", IsSuccess = false }.ToJsonString());
        }