智云SRM-WEBAPI（目前客户通用API）
blame | 历史 | 补丁 | 提交 | 提交对比 | ignore whitespace
yusijie
2023-10-18 d077c84b9a62a6c148198232154591f0cbd578f5 
 WebAPI/Controllers/POOrderBillExcelController.cs


@@ -1,4 +1,5 @@


using System;


using Newtonsoft.Json.Linq;


using System;


using System.Collections.Generic;


using System.Data;


using System.IO;


@@ -25,11 +26,11 @@


            DataTable dt = new DataTable();


            try


            {


                ds = oCn.RunProcReturn("select * from POOrderBill_Excel ", "POOrderBill_Excel");


                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()}'", "POOrderBill_Excel");


                    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)


                    {


@@ -46,10 +47,6 @@


                    }




                }










                objJsonResult.code = "1";


                objJsonResult.count = 1;


                objJsonResult.Message = "";


@@ -83,7 +80,7 @@


                file.SaveAs(ExcelPath);




                NpoiHelper np = new NpoiHelper();


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


                DataSet ExcelDs = np.ReadExcel(ExcelPath, 1, 1, "0");




                //删除文件


                File.Delete(ExcelPath);


@@ -130,6 +127,18 @@


                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";


@@ -144,14 +153,14 @@


                {


                    for (int j = 0; j < tb2.Columns.Count; j++)


                    {


                        if (tb2.Rows[i][j].ToString() == "")


                        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;


                        }


                        }                        


                    }


                }




@@ -167,9 +176,11 @@


                    for (int i = 0; i < tb2.Rows.Count; i++)


                    {


                        oCn.RunProc("insert into POOrderBill_Excel(HItemID,HORGName, SupplierName, Warehouse_Shou," +


                            " MaterialNum, HQty, HPackFlag)values" +


                            " 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()}','{tb2.Rows[i]["物流单号"].ToString()}','{tb2.Rows[i]["物流公司"].ToString()}'," +


                            $"'{tb2.Rows[i]["箱号"].ToString()}')");


                    }




                    objJsonResult.code = "1";


@@ -193,75 +204,52 @@


        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)


                {


                    objJsonResult.code = "0";


                    objJsonResult.count = 0;


                    objJsonResult.Message = $"第{(i + 1)}行,组织为{dt.Rows[i]["组织"].ToString()}不存在!";


                    objJsonResult.data = null;


                    return objJsonResult;


                    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)


                {


                    objJsonResult.code = "0";


                    objJsonResult.count = 0;


                    objJsonResult.Message = $"第{(i + 1)}行,供应商为{dt.Rows[i]["供应商"].ToString()}不存在!";


                    objJsonResult.data = null;


                    return objJsonResult;


                    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)


                {


                    objJsonResult.code = "0";


                    objJsonResult.count = 0;


                    objJsonResult.Message = $"第{(i + 1)}行,收料仓库为{dt.Rows[i]["收料仓库"].ToString()}不存在!";


                    objJsonResult.data = null;


                    return objJsonResult;


                    error = error + $"第{(i + 1)}行,收料仓库为{dt.Rows[i]["收料仓库"].ToString()}不存在!";


                }




                //物料编码


                if (!DBUtility.ClsPub.AllowNumber(dt.Rows[i]["物料编码"].ToString()))


                {


                    objJsonResult.code = "0";


                    objJsonResult.count = 0;


                    objJsonResult.Message = "物料代码中不能出现连续‘.’并且首位末位不能为‘.’！";


                    objJsonResult.data = null;


                    return objJsonResult;


                    error = error + "物料代码中不能出现连续‘.’并且首位末位不能为‘.’！";


                }


                ds = oCn.RunProcReturn("select * from Gy_Material where HNumber='" + dt.Rows[i]["物料编码"].ToString() + "' ", "Gy_Material");


                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)


                {


                    objJsonResult.code = "0";


                    objJsonResult.count = 0;


                    objJsonResult.Message = $"第{(i + 1)}行,物料编码为{dt.Rows[i]["物料编码"].ToString()}不存在!";


                    objJsonResult.data = null;


                    return objJsonResult;


                    error = error + $"第{(i + 1)}行,物料编码为{dt.Rows[i]["物料编码"].ToString()}不存在!";


                }




                //数量


                if (dt.Rows[i]["数量"].ToString() == "0")


                {


                    objJsonResult.code = "0";


                    objJsonResult.count = 0;


                    objJsonResult.Message = $"第{(i + 1)}行,数量不为0!";


                    objJsonResult.data = null;


                    return objJsonResult;


                    error = error + $"第{(i + 1)}行,数量不为0!";


                }




                //判断导入物料的数量是否大于单据物料的总和数量


                ds = oCn.RunProcReturn("select * from h_v_POOrderBill_Excel " +


                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 关闭状态='未关闭' and 审核状态='已审核' ", "h_v_POOrderBill_Excel");


                    $"and 物料编码='{dt.Rows[i]["物料编码"].ToString()}' and 关闭状态='未关闭' ", "h_v_POOrderBill_Excel");


                if (ds.Tables[0].Rows.Count != 0)


                {


                    double NumCount = 0;//总数量


@@ -272,29 +260,34 @@




                    if (NumCount < double.Parse(dt.Rows[i]["数量"].ToString()))


                    {


                        objJsonResult.code = "0";


                        objJsonResult.count = 0;


                        objJsonResult.Message = $"第{(i + 1)}行导入数量大于采购订单合计数量,请找采购员申请做采购订单!";


                        objJsonResult.data = null;


                        return objJsonResult;


                        error = error + $"第{(i + 1)}行,物料编码:{dt.Rows[i]["物料编码"].ToString()},包装标识:{dt.Rows[i]["包装标识"].ToString()},导入数量:{dt.Rows[i]["数量"].ToString()} 大于 采购订单合计数量:{NumCount},请找采购员申请做采购订单!";


                    }


                }


                else


                {


                    objJsonResult.code = "0";


                    objJsonResult.count = 0;


                    objJsonResult.Message = $"第{(i + 1)}行,匹配不到物料信息!";


                    objJsonResult.data = null;


                    return objJsonResult;


                    error = error + $"第{(i + 1)}行,物料编码:{dt.Rows[i]["物料编码"].ToString()},包装标识:{dt.Rows[i]["包装标识"].ToString()},采购订单可用数量:0,小于订单总需求数量:{dt.Rows[i]["数量"].ToString()},匹配不到数据信息!";


                }


            }




            objJsonResult.code = "1";


            objJsonResult.count = 1;


            objJsonResult.Message = "没有问题";


            objJsonResult.data = null;


            return objJsonResult;


            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






    }


}