From d077c84b9a62a6c148198232154591f0cbd578f5 Mon Sep 17 00:00:00 2001
From: yusijie <ysj@hz-kingdee.com>
Date: 星期三, 18 十月 2023 13:35:22 +0800
Subject: [PATCH] Merge branch 'master' of http://101.37.171.70:10101/r/SRM-WEBAPI
---
WebAPI/Controllers/POOrderBillExcelController.cs | 113 ++++++++++++++++++++++++++------------------------------
1 files changed, 53 insertions(+), 60 deletions(-)
diff --git a/WebAPI/Controllers/POOrderBillExcelController.cs b/WebAPI/Controllers/POOrderBillExcelController.cs
index 323e83b..27b8b53 100644
--- a/WebAPI/Controllers/POOrderBillExcelController.cs
+++ b/WebAPI/Controllers/POOrderBillExcelController.cs
@@ -1,4 +1,5 @@
-锘縰sing System;
+锘縰sing 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
+
+
}
}
--
Gitblit v1.9.1