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 HORGName,SupplierName,MaterialNum,HPackFlag,sum(HQty) HQty from POOrderBill_Excel group by HORGName, SupplierName, MaterialNum, HPackFlag ", "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, "0");
|
|
//删除文件
|
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 (!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[j].ToString()!= "包装标识" && tb2.Columns[j].ToString() != "到货日期" && tb2.Columns[j].ToString() != "物流单号" && tb2.Columns[j].ToString() != "物流公司" && tb2.Columns[j].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, HArrivalDate, HExpressNumberSub, HExpressSupplierSub, HBoxNumber)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()}'," +
|
$"'{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();
|
string error = "";
|
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)
|
{
|
error = error+ $"第{(i + 1)}行,组织为{dt.Rows[i]["组织"].ToString()}不存在!";
|
}
|
|
//供应商
|
ds = oCn.RunProcReturn("select * from Gy_Supplier where Hname='" + dt.Rows[i]["供应商"].ToString() + "' ", "Gy_Supplier");
|
if (ds.Tables[0].Rows.Count == 0)
|
{
|
error = error + $"第{(i + 1)}行,供应商为{dt.Rows[i]["供应商"].ToString()}不存在!";
|
}
|
|
//收料仓库
|
ds = oCn.RunProcReturn("select * from Gy_Warehouse where Hname='" + dt.Rows[i]["收料仓库"].ToString() + "' ", "Gy_Warehouse");
|
if (ds.Tables[0].Rows.Count == 0)
|
{
|
error = error + $"第{(i + 1)}行,收料仓库为{dt.Rows[i]["收料仓库"].ToString()}不存在!";
|
}
|
|
//物料编码
|
if (!DBUtility.ClsPub.AllowNumber(dt.Rows[i]["物料编码"].ToString()))
|
{
|
error = error + "物料代码中不能出现连续‘.’并且首位末位不能为‘.’！";
|
}
|
ds = oCn.RunProcReturn("select top 1 HItemID from Gy_Material where HNumber='" + dt.Rows[i]["物料编码"].ToString() + "' ", "Gy_Material");
|
if (ds.Tables[0].Rows.Count == 0)
|
{
|
error = error + $"第{(i + 1)}行,物料编码为{dt.Rows[i]["物料编码"].ToString()}不存在!";
|
}
|
|
//数量
|
if (dt.Rows[i]["数量"].ToString() == "0")
|
{
|
error = error + $"第{(i + 1)}行,数量不为0!";
|
}
|
|
//判断导入物料的数量是否大于单据物料的总和数量
|
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()))
|
{
|
error = error + $"第{(i + 1)}行,物料编码:{dt.Rows[i]["物料编码"].ToString()},包装标识:{dt.Rows[i]["包装标识"].ToString()},导入数量:{dt.Rows[i]["数量"].ToString()} 大于 采购订单合计数量:{NumCount},请找采购员申请做采购订单!";
|
}
|
}
|
else
|
{
|
error = error + $"第{(i + 1)}行,物料编码:{dt.Rows[i]["物料编码"].ToString()},包装标识:{dt.Rows[i]["包装标识"].ToString()},采购订单可用数量:0,小于订单总需求数量:{dt.Rows[i]["数量"].ToString()},匹配不到数据信息!";
|
}
|
}
|
|
if (error == "")
|
{
|
objJsonResult.code = "1";
|
objJsonResult.count = 1;
|
objJsonResult.Message = "没有问题";
|
objJsonResult.data = null;
|
return objJsonResult;
|
}
|
else
|
{
|
objJsonResult.code = "0";
|
objJsonResult.count = 0;
|
objJsonResult.Message = error;
|
objJsonResult.data = null;
|
return objJsonResult;
|
}
|
}
|
#endregion
|
|
|
}
|
}
|
