nothing

王 垚

2022-07-14 1e5ff873b4f05e2932ff1d117f8e6241161096f0

 WebAPI/Controllers/POOrderBillExcelController.cs

New file
@@ -0,0 +1,299 @@
using Newtonsoft.Json.Linq;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Net;
using System.Net.Http;
using System.Web;
using System.Web.Http;
using WebAPI.Code;
using WebAPI.Models;
namespace WebAPI.Controllers
{
    public class POOrderBillExcelController : ApiController
    {
        private JsonResult objJsonResult = new JsonResult();
        SQLHelper.ClsCNSRM oCn = new SQLHelper.ClsCNSRM();

        #region 采购订单 查询
        [Route("POOrderBillExcel/POOrderBillExcelList")]
        [HttpGet]
        public object POOrderBillExcelList(string sqlWhere)
        {
            DataSet ds = new DataSet();
            DataSet ds1 = new DataSet();
            DataTable dt = new DataTable();
            try
            {
                ds = oCn.RunProcReturn("select * from POOrderBill_Excel ", "POOrderBill_Excel");

                for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                {
                    ds1 = oCn.RunProcReturn($"exec h_p_POOrderBill_Excel {ds.Tables[0].Rows[i]["HQty"].ToString()},'{ds.Tables[0].Rows[i]["MaterialNum"].ToString()}','1'", "POOrderBill_Excel");

                    if (i == 0)
                    {
                        dt = ds1.Tables[0];
                    }
                    else
                    {
                        foreach (DataRow item in ds1.Tables[0].Rows)
                        {
                            DataRow dataRow = dt.NewRow();
                            dataRow.ItemArray = item.ItemArray;
                            dt.Rows.Add(dataRow);
                        }
                    }

                }
                objJsonResult.code = "1";
                objJsonResult.count = 1;
                objJsonResult.Message = "";
                objJsonResult.data = dt;
                return objJsonResult;
            }
            catch (Exception e)
            {
                objJsonResult.code = "0";
                objJsonResult.count = 0;
                objJsonResult.Message = "获取失败" + e.ToString();
                objJsonResult.data = null;
                return objJsonResult;
            }

        }
        #endregion

        #region 采购订单 文件上传
        [Route("POOrderBillExcel/POOrderBillExcelImport")]
        [HttpPost]
        public object POOrderBillExcelImport()
        {
            try
            {
                //获取文件名称
                var file = HttpContext.Current.Request.Files[0];
                //获取文件物理路径
                string ExcelPath = HttpContext.Current.Server.MapPath("~/" + file.FileName);
                //保存文件
                file.SaveAs(ExcelPath);

                NpoiHelper np = new NpoiHelper();
                DataSet ExcelDs = np.ReadExcel(ExcelPath, 1, 1, "2");

                //删除文件
                File.Delete(ExcelPath);

                //创建临时表
                DataTable tb2 = new DataTable("dt2");

                //添加列名
                for (int i = 0; i < ExcelDs.Tables[0].Columns.Count; i++)
                {
                    tb2.Columns.Add(ExcelDs.Tables[0].Rows[0][i].ToString());
                }

                //添加数据
                for (int i = 1; i < ExcelDs.Tables[0].Rows.Count; i++)
                {
                    DataRow row = tb2.NewRow();
                    for (int j = 0; j < ExcelDs.Tables[0].Columns.Count; j++)
                    {
                        row[j] = ExcelDs.Tables[0].Rows[i][j].ToString();
                    }
                    tb2.Rows.Add(row);
                }


                var error = "";

                //查询工艺路线没有的列
                if (!tb2.Columns.Contains("组织"))
                    error += "没有找到【组织】的标题,";

                if (!tb2.Columns.Contains("供应商"))
                    error += "没有找到【供应商】的标题,";

                if (!tb2.Columns.Contains("收料仓库"))
                    error += "没有找到【收料仓库】的标题,";

                if (!tb2.Columns.Contains("物料编码"))
                    error += "没有找到【物料编码】的标题,";

                if (!tb2.Columns.Contains("数量"))
                    error += "没有找到【数量】的标题,";

                if (!tb2.Columns.Contains("包装标识"))
                    error += "没有找到【包装标识】的标题,";

                if (error.Length > 0)
                {
                    objJsonResult.code = "0";
                    objJsonResult.count = 0;
                    objJsonResult.Message = $"Excel模板存在错误,{error}\r\n";
                    objJsonResult.data = null;
                    return objJsonResult;
                }

                //判断数据是否为空
                for (int i = 0; i < tb2.Rows.Count; i++)
                {
                    for (int j = 0; j < tb2.Columns.Count; j++)
                    {
                        if (tb2.Rows[i][j].ToString() == ""&& tb2.Columns[i].ToString()!= "包装标识")
                        {
                            objJsonResult.code = "0";
                            objJsonResult.count = 0;
                            objJsonResult.Message = $"Excel模板存在错误,第{(i + 1)}行第{(j + 1)}列值不能为空!\r\n";
                            objJsonResult.data = null;
                            return objJsonResult;
                        }
                    }
                }

                //检查物料 是否存在
                objJsonResult = (JsonResult)Checkdata(tb2);
                if (objJsonResult.code == "0")
                {
                    return objJsonResult;
                }
                else
                {
                    oCn.RunProc("delete from POOrderBill_Excel");
                    for (int i = 0; i < tb2.Rows.Count; i++)
                    {
                        oCn.RunProc("insert into POOrderBill_Excel(HItemID,HORGName, SupplierName, Warehouse_Shou," +
                            " MaterialNum, HQty, HPackFlag)values" +
                            $"({(i + 1)},'{tb2.Rows[i]["组织"].ToString()}', '{tb2.Rows[i]["供应商"].ToString()}', '{tb2.Rows[i]["收料仓库"].ToString()}', " +
                            $"'{tb2.Rows[i]["物料编码"].ToString()}', {tb2.Rows[i]["数量"].ToString()}, '{tb2.Rows[i]["包装标识"].ToString()}')");
                    }

                    objJsonResult.code = "1";
                    objJsonResult.count = 1;
                    objJsonResult.Message = error;
                    objJsonResult.data = tb2;
                    return objJsonResult;
                }
            }
            catch (Exception e)
            {
                objJsonResult.code = "0";
                objJsonResult.count = 0;
                objJsonResult.Message = "Exception！" + e.ToString();
                objJsonResult.data = null;
                return objJsonResult;
            }
        }

        //检查物料 是否存在
        private object Checkdata(DataTable dt)
        {
            DataSet ds = new DataSet();
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                //组织
                ds = oCn.RunProcReturn("select * from Xt_ORGANIZATIONS where Hname='" + dt.Rows[i]["组织"].ToString() + "' ", "Xt_ORGANIZATIONS");
                if (ds.Tables[0].Rows.Count == 0)
                {
                    objJsonResult.code = "0";
                    objJsonResult.count = 0;
                    objJsonResult.Message = $"第{(i + 1)}行,组织为{dt.Rows[i]["组织"].ToString()}不存在!";
                    objJsonResult.data = null;
                    return objJsonResult;
                }

                //供应商
                ds = oCn.RunProcReturn("select * from Gy_Supplier where Hname='" + dt.Rows[i]["供应商"].ToString() + "' ", "Gy_Supplier");
                if (ds.Tables[0].Rows.Count == 0)
                {
                    objJsonResult.code = "0";
                    objJsonResult.count = 0;
                    objJsonResult.Message = $"第{(i + 1)}行,供应商为{dt.Rows[i]["供应商"].ToString()}不存在!";
                    objJsonResult.data = null;
                    return objJsonResult;
                }

                //收料仓库
                ds = oCn.RunProcReturn("select * from Gy_Warehouse where Hname='" + dt.Rows[i]["收料仓库"].ToString() + "' ", "Gy_Warehouse");
                if (ds.Tables[0].Rows.Count == 0)
                {
                    objJsonResult.code = "0";
                    objJsonResult.count = 0;
                    objJsonResult.Message = $"第{(i + 1)}行,收料仓库为{dt.Rows[i]["收料仓库"].ToString()}不存在!";
                    objJsonResult.data = null;
                    return objJsonResult;
                }

                //物料编码
                if (!DBUtility.ClsPub.AllowNumber(dt.Rows[i]["物料编码"].ToString()))
                {
                    objJsonResult.code = "0";
                    objJsonResult.count = 0;
                    objJsonResult.Message = "物料代码中不能出现连续‘.’并且首位末位不能为‘.’！";
                    objJsonResult.data = null;
                    return objJsonResult;
                }
                ds = oCn.RunProcReturn("select * from Gy_Material where HNumber='" + dt.Rows[i]["物料编码"].ToString() + "' ", "Gy_Material");
                if (ds.Tables[0].Rows.Count == 0)
                {
                    objJsonResult.code = "0";
                    objJsonResult.count = 0;
                    objJsonResult.Message = $"第{(i + 1)}行,物料编码为{dt.Rows[i]["物料编码"].ToString()}不存在!";
                    objJsonResult.data = null;
                    return objJsonResult;
                }

                //数量
                if (dt.Rows[i]["数量"].ToString() == "0")
                {
                    objJsonResult.code = "0";
                    objJsonResult.count = 0;
                    objJsonResult.Message = $"第{(i + 1)}行,数量不为0!";
                    objJsonResult.data = null;
                    return objJsonResult;
                }

                //判断导入物料的数量是否大于单据物料的总和数量
                ds = oCn.RunProcReturn("select * from h_v_POOrderBill_Excel " +
                    $"where 采购组织='{dt.Rows[i]["组织"].ToString()}' and 供应商='{dt.Rows[i]["供应商"].ToString()}' " +
                    $"and 包装标识='{dt.Rows[i]["包装标识"].ToString()}'" +
                    $"and 物料编码='{dt.Rows[i]["物料编码"].ToString()}' and 关闭状态='未关闭' ", "h_v_POOrderBill_Excel");
                if (ds.Tables[0].Rows.Count != 0)
                {
                    double NumCount = 0;//总数量
                    foreach (DataRow item in ds.Tables[0].Rows)
                    {
                        NumCount += double.Parse(item["未关联数量"].ToString());
                    }

                    if (NumCount < double.Parse(dt.Rows[i]["数量"].ToString()))
                    {
                        objJsonResult.code = "0";
                        objJsonResult.count = 0;
                        objJsonResult.Message = $"第{(i + 1)}行导入数量大于采购订单合计数量,请找采购员申请做采购订单!";
                        objJsonResult.data = null;
                        return objJsonResult;
                    }
                }
                else
                {
                    objJsonResult.code = "0";
                    objJsonResult.count = 0;
                    objJsonResult.Message = $"第{(i + 1)}行,匹配不到数据信息!";
                    objJsonResult.data = null;
                    return objJsonResult;
                }
            }

            objJsonResult.code = "1";
            objJsonResult.count = 1;
            objJsonResult.Message = "没有问题";
            objJsonResult.data = null;
            return objJsonResult;
        }
        #endregion


    }
}