From dd651f9ce0156fc7db869da04e0c2004a1811c52 Mon Sep 17 00:00:00 2001
From: yl <ykxkd@outlook.com>
Date: 星期二, 25 十月 2022 21:00:36 +0800
Subject: [PATCH] 安灯报表开发
---
VueWebApi/Tools/ImportExcelData.cs | 207 ++++++++++++++++++++++++++++++++++++++++++++-------
1 files changed, 178 insertions(+), 29 deletions(-)
diff --git a/VueWebApi/Tools/ImportExcelData.cs b/VueWebApi/Tools/ImportExcelData.cs
index 026d75d..6e4984f 100644
--- a/VueWebApi/Tools/ImportExcelData.cs
+++ b/VueWebApi/Tools/ImportExcelData.cs
@@ -381,7 +381,7 @@
else if (excelTable[0].Columns[0].ColumnName != "搴忓彿")
{
code = "300";
- Message = "鐢ㄦ埛鐝粍妯℃澘锛氳〃澶翠俊鎭笉绗﹀悎瑙勮寖,绗�1鍒楀簲涓簕搴忓彿}";
+ Message = "浠撳簱娓呭崟妯℃澘锛氳〃澶翠俊鎭笉绗﹀悎瑙勮寖,绗�1鍒楀簲涓簕搴忓彿}";
return Message;
}
else if (excelTable[0].Columns[1].ColumnName != "浠撳簱缂栧彿(鍞竴)")
@@ -1646,87 +1646,97 @@
if (excelTable.Count != 2)
{
code = "300";
- Message = "妯℃澘涓嶇鍚堣鑼�,缂哄け鐨凷heet";
+ Message = "瀵煎叆妯℃澘涓嶇鍚堣鑼�,璇锋鏌heet鏁�";
}
else if (excelTable[0].Columns.Count != 6)
{
code = "300";
- Message = "妯℃澘涓嶇鍚堣鑼�,璇锋鏌ュ垪鍚嶅瓧娈垫暟";
+ Message = "鐗╂枡娓呭崟妯℃澘(姣嶄欢淇℃伅)涓嶇鍚堣鑼�,璇锋鏌ュ垪鍚嶅瓧娈垫暟";
}
- else if (excelTable[1].Columns.Count != 8)
+ else if (excelTable[1].Columns.Count != 9)
{
code = "300";
- Message = "妯℃澘涓嶇鍚堣鑼�,璇锋鏌ュ垪鍚嶅瓧娈垫暟";
+ Message = "鐗╂枡娓呭崟妯℃澘(瀛愪欢淇℃伅)涓嶇鍚堣鑼�,璇锋鏌ュ垪鍚嶅瓧娈垫暟";
}
else if (excelTable[0].Columns[0].ColumnName != "搴忓彿")
{
code = "300";
- Message = "妯℃澘涓嶇鍚堣鑼�,璇锋鏌ュ垪鍚�";
+ Message = "鐗╂枡娓呭崟妯℃澘(姣嶄欢)锛氳〃澶翠俊鎭笉绗﹀悎瑙勮寖,绗�1鍒楀簲涓簕搴忓彿}";
}
else if (excelTable[0].Columns[1].ColumnName != "姣嶄欢缂栫爜")
{
code = "300";
- Message = "妯℃澘涓嶇鍚堣鑼�,璇锋鏌ュ垪鍚�";
+ Message = "鐗╂枡娓呭崟妯℃澘(姣嶄欢)锛氳〃澶翠俊鎭笉绗﹀悎瑙勮寖,绗�2鍒楀簲涓簕姣嶄欢缂栫爜}";
}
else if (excelTable[0].Columns[2].ColumnName != "姣嶄欢鍚嶇О")
{
code = "300";
- Message = "妯℃澘涓嶇鍚堣鑼�,璇锋鏌ュ垪鍚�";
+ Message = "鐗╂枡娓呭崟妯℃澘(姣嶄欢)锛氳〃澶翠俊鎭笉绗﹀悎瑙勮寖,绗�3鍒楀簲涓簕姣嶄欢鍚嶇О}";
}
else if (excelTable[0].Columns[3].ColumnName != "鍩虹鏁伴噺")
{
code = "300";
- Message = "妯℃澘涓嶇鍚堣鑼�,璇锋鏌ュ垪鍚�";
+ Message = "鐗╂枡娓呭崟妯℃澘(姣嶄欢)锛氳〃澶翠俊鎭笉绗﹀悎瑙勮寖,绗�4鍒楀簲涓簕鍩虹鏁伴噺}";
}
else if (excelTable[0].Columns[4].ColumnName != "鍚敤鐘舵��")
{
code = "300";
- Message = "妯℃澘涓嶇鍚堣鑼�,璇锋鏌ュ垪鍚�";
+ Message = "鐗╂枡娓呭崟妯℃澘(姣嶄欢)锛氳〃澶翠俊鎭笉绗﹀悎瑙勮寖,绗�5鍒楀簲涓簕鍚敤鐘舵�亇";
}
else if (excelTable[0].Columns[5].ColumnName != "鐗堟湰鍙�")
{
code = "300";
- Message = "妯℃澘涓嶇鍚堣鑼�,璇锋鏌ュ垪鍚�";
+ Message = "鐗╂枡娓呭崟妯℃澘(姣嶄欢)锛氳〃澶翠俊鎭笉绗﹀悎瑙勮寖,绗�6鍒楀簲涓簕鐗堟湰鍙穧";
}
else if (excelTable[1].Columns[0].ColumnName != "搴忓彿")
{
code = "300";
- Message = "妯℃澘涓嶇鍚堣鑼�,璇锋鏌ュ垪鍚�";
+ Message = "鐗╂枡娓呭崟妯℃澘(瀛愪欢)锛氳〃澶翠俊鎭笉绗﹀悎瑙勮寖,绗�1鍒楀簲涓簕搴忓彿}";
}
else if (excelTable[1].Columns[1].ColumnName != "涓昏〃搴忓垪鍙�")
{
code = "300";
- Message = "妯℃澘涓嶇鍚堣鑼�,璇锋鏌ュ垪鍚�";
+ Message = "鐗╂枡娓呭崟妯℃澘(瀛愪欢)锛氳〃澶翠俊鎭笉绗﹀悎瑙勮寖,绗�2鍒楀簲涓簕涓昏〃搴忓垪鍙穧";
}
- else if (excelTable[1].Columns[2].ColumnName != "瀛愪欢缂栫爜")
+ else if (excelTable[1].Columns[2].ColumnName != "瀛愪欢椤哄簭鍙�")
{
code = "300";
- Message = "妯℃澘涓嶇鍚堣鑼�,璇锋鏌ュ垪鍚�";
+ Message = "鐗╂枡娓呭崟妯℃澘(瀛愪欢)锛氳〃澶翠俊鎭笉绗﹀悎瑙勮寖,绗�3鍒楀簲涓簕瀛愪欢椤哄簭鍙穧";
}
- else if (excelTable[1].Columns[3].ColumnName != "瀛愪欢鍚嶇О")
+ else if (excelTable[1].Columns[3].ColumnName != "瀛愪欢缂栫爜")
{
code = "300";
- Message = "妯℃澘涓嶇鍚堣鑼�,璇锋鏌ュ垪鍚�";
+ Message = "鐗╂枡娓呭崟妯℃澘(瀛愪欢)锛氳〃澶翠俊鎭笉绗﹀悎瑙勮寖,绗�4鍒楀簲涓簕瀛愪欢缂栫爜}";
}
- else if (excelTable[1].Columns[4].ColumnName != "鍙戞枡浠撳簱")
+ else if (excelTable[1].Columns[4].ColumnName != "瀛愪欢鍚嶇О")
{
code = "300";
- Message = "妯℃澘涓嶇鍚堣鑼�,璇锋鏌ュ垪鍚�";
+ Message = "鐗╂枡娓呭崟妯℃澘(瀛愪欢)锛氳〃澶翠俊鎭笉绗﹀悎瑙勮寖,绗�5鍒楀簲涓簕瀛愪欢鍚嶇О}";
}
- else if (excelTable[1].Columns[5].ColumnName != "鍩烘湰鐢ㄩ噺")
+ else if (excelTable[1].Columns[5].ColumnName != "鍙戞枡浠撳簱")
{
code = "300";
- Message = "妯℃澘涓嶇鍚堣鑼�,璇锋鏌ュ垪鍚�";
+ Message = "鐗╂枡娓呭崟妯℃澘(瀛愪欢)锛氳〃澶翠俊鎭笉绗﹀悎瑙勮寖,绗�6鍒楀簲涓簕鍙戞枡浠撳簱}";
}
- else if (excelTable[1].Columns[6].ColumnName != "鎹熻�楃巼(%)")
+ else if (excelTable[1].Columns[6].ColumnName != "鍩烘湰鐢ㄩ噺")
{
code = "300";
- Message = "妯℃澘涓嶇鍚堣鑼�,璇锋鏌ュ垪鍚�";
+ Message = "鐗╂枡娓呭崟妯℃澘(瀛愪欢)锛氳〃澶翠俊鎭笉绗﹀悎瑙勮寖,绗�7鍒楀簲涓簕鍩烘湰鐢ㄩ噺}";
}
- else if (excelTable[1].Columns[7].ColumnName != "灞炴��")
+ else if (excelTable[1].Columns[7].ColumnName != "鎹熻�楃巼(%)")
{
code = "300";
- Message = "妯℃澘涓嶇鍚堣鑼�,璇锋鏌ュ垪鍚�";
+ Message = "鐗╂枡娓呭崟妯℃澘(瀛愪欢)锛氳〃澶翠俊鎭笉绗﹀悎瑙勮寖,绗�8鍒楀簲涓簕鎹熻�楃巼(%)}";
+ }
+ else if (excelTable[1].Columns[8].ColumnName != "瀹為檯鐢ㄩ噺")
+ {
+ code = "300";
+ Message = "鐗╂枡娓呭崟妯℃澘(瀛愪欢)锛氳〃澶翠俊鎭笉绗﹀悎瑙勮寖,绗�9鍒楀簲涓簕瀹為檯鐢ㄩ噺}";
+ }
+ else if (excelTable[1].Columns[9].ColumnName != "灞炴��")
+ {
+ code = "300";
+ Message = "鐗╂枡娓呭崟妯℃澘(瀛愪欢)锛氳〃澶翠俊鎭笉绗﹀悎瑙勮寖,绗�10鍒楀簲涓簕灞炴�";
}
else
{
@@ -2596,9 +2606,87 @@
StuCode = "";
count = 0;
string sql = "";
+ var dynamicParams = new DynamicParameters();
DataTable dt;
List<ExcelErro> list = new List<ExcelErro>();
-
+ List<DataTable> excelTable = new List<DataTable>();
+ list = ImportExcel.ExcelToTableListErro(FileCode); //楠岃瘉Excel鏁版嵁蹇呭～瀛楁鏄惁涓虹┖銆佸敮涓�瀛楁鏄惁閲嶅
+ excelTable = ImportExcel.ExcelToTableList(FileCode); //鑾峰彇Excel鏁版嵁
+ //Excel鏌ヨ姣嶄欢+鐗堟湰鍙锋槸鍚﹂噸澶�
+ DataTable distinct = excelTable[0].DefaultView.ToTable(true, "姣嶄欢缂栫爜, 鐗堟湰鍙�");
+ if (distinct.Rows.Count != excelTable[0].Rows.Count)
+ {
+ ExcelErro erro = new ExcelErro();
+ erro.RoeNumber = "/";
+ erro.ErrorField = "{鐗堟湰鍙穧";
+ erro.ErrorCont = "Bom姣嶄欢琛�:{姣嶄欢缂栫爜}瀵瑰簲鐨剓鐗堟湰鍙穧鏈夐噸澶�";
+ list.Add(erro);
+ }
+ //鍒ゆ柇瀛愯〃澶栭敭涓嶅瓨鍦ㄤ簬涓昏〃涓婚敭涓殑鏁版嵁
+ var dt3 = from r in excelTable[1].AsEnumerable()
+ where !(
+ from rr in excelTable[0].AsEnumerable()
+ select rr.Field<string>("搴忓彿")
+ ).Contains(r.Field<string>("涓昏〃搴忓垪鍙�"))
+ select r;
+ List<DataRow> listRow = dt3.ToList();
+ if (listRow.Count > 0)
+ {
+ ExcelErro erro = new ExcelErro();
+ erro.RoeNumber = "/";
+ erro.ErrorField = "{涓昏〃搴忓垪鍙穧";
+ erro.ErrorCont = "瀛愪欢淇℃伅:{涓昏〃搴忓垪鍙穧瀛楁涓湁鍊煎湪姣嶄欢淇℃伅:{搴忓彿}涓笉瀛樺湪";
+ list.Add(erro);
+ }
+ //鏁版嵁搴撴煡璇㈡瘝浠�+鐗堟湰鍙锋槸鍚﹀瓨鍦�
+ for (int j = 0; j < excelTable[0].Rows.Count; j++)
+ {
+ if (excelTable[0].Rows[j][1].ToString().Trim() != null && excelTable[0].Rows[j][1].ToString().Trim() != "")
+ {
+ sql = @"select * from TBom_Main where materiel_code=@code and version=@version";
+ dynamicParams.Add("@code", excelTable[0].Rows[j][1].ToString().Trim());
+ dynamicParams.Add("@version", excelTable[0].Rows[j][5].ToString().Trim());
+ dt = DapperHelper.selectdata(sql, dynamicParams);
+ if (dt.Rows.Count > 0)
+ {
+ ExcelErro erro = new ExcelErro();
+ erro.RoeNumber = "/";
+ erro.ErrorField = "{姣嶄欢缂栫爜}";
+ erro.ErrorCont = "Bom姣嶄欢琛�:{姣嶄欢缂栫爜}瀛楁" + excelTable[0].Rows[j][1].ToString().Trim() + "瀵瑰簲鐨勭増鏈彿:"+ excelTable[0].Rows[j][5].ToString() + "宸插瓨鍦�";
+ list.Add(erro);
+ }
+ //鏌ヨ姣嶄欢淇℃伅瀵瑰簲鐨勫瓙浠朵俊鎭�
+ DataTable ds = excelTable[1].AsEnumerable().Where<DataRow>(C => C["涓昏〃搴忓垪鍙�"].ToString() == excelTable[0].Rows[j][0].ToString().Trim()).CopyToDataTable();
+ for (int i = 0; i < ds.Rows.Count; i++)
+ {
+ if (excelTable[0].Rows[j][1].ToString().Trim() == ds.Rows[i]["瀛愪欢缂栫爜"].ToString().Trim())
+ {
+ ExcelErro erro = new ExcelErro();
+ erro.RoeNumber = "/";
+ erro.ErrorField = "{瀛愪欢缂栫爜}";
+ erro.ErrorCont = "瀛愪欢淇℃伅:{瀛愪欢缂栫爜}瀛楁" + ds.Rows[i]["瀛愪欢缂栫爜"].ToString().Trim() + "涓庡搴旂殑{姣嶄欢缂栫爜}瀛楁閲嶅";
+ list.Add(erro);
+ }
+ }
+ }
+ }
+ if (list.Count > 0)
+ {
+ int index = 0;
+ foreach (ExcelErro item in list)
+ {
+ index++;
+ item.Seq = index.ToString();
+ }
+ StuCode = "301";
+ message = "鏁版嵁楠岃瘉澶辫触";
+ }
+ else
+ {
+ StuCode = "200";
+ message = "鏁版嵁楠岃瘉鎴愬姛";
+ count = excelTable[0].Rows.Count + excelTable[1].Rows.Count;
+ }
return list;
}
#endregion
@@ -3560,11 +3648,72 @@
#region銆怑xcel鏁版嵁涓婁紶,鐗╂枡娓呭崟銆�
public static string TwentyOneSubmit(string FileCode, string User, out string StuCode)
{
- string message = "";
- StuCode = "";
+ string message = ""; StuCode = "";
+ string sql = "";
+ int BomID = 0; //鏈�澶d
+ List<object> list = new List<object>();
+ var dynamicParams = new DynamicParameters();
try
{
-
+ list.Clear();
+ List<DataTable> excelTable = new List<DataTable>();
+ excelTable = ImportExcel.ExcelToTableList(FileCode);
+ //鑾峰彇涓昏〃褰撳墠鏈�澶D
+ BomID = DapperHelper.insertReturnId("TBom_Main");
+ //瀵煎叆Bom涓昏〃
+ for (int k = 0; k < excelTable[0].Rows.Count; k++)
+ {
+ BomID = BomID + 1;
+ sql = @"insert into TBom_Main(materiel_code,quantity,status,version,lm_user,lm_date)
+ values(@materiel_code,@quantity,@status,@version,@lm_user,@lm_date)";
+ list.Add(new
+ {
+ str = sql,
+ parm = new
+ {
+ materiel_code = excelTable[0].Rows[k][1].ToString().Trim(),
+ quantity = excelTable[0].Rows[k][3].ToString().Trim(),
+ status = excelTable[0].Rows[k][4].ToString().Trim(),
+ version = excelTable[0].Rows[k][5].ToString().Trim(),
+ CreateDate = DateTime.Now.ToString(),
+ Operator = User
+ }
+ });
+ //鏌ヨ姣嶄欢淇℃伅瀵瑰簲鐨勫瓙浠朵俊鎭�
+ DataTable ds = excelTable[1].AsEnumerable().Where(item => item["涓昏〃搴忓垪鍙�"].ToString().Equals(excelTable[0].Rows[k][0].ToString())).CopyToDataTable();
+ //瀵煎叆Bom瀛愯〃
+ for (int i = 0; i < ds.Rows.Count; i++)
+ {
+ sql = @"insert into TBom_Deta(m_id,seq,smateriel_code,base_quantity,loss_quantity,total_quantity,pn_type)
+ values(@m_id,@seq,@smateriel_code,@base_quantity,@loss_quantity,@total_quantity,@pn_type)";
+ list.Add(new
+ {
+ str = sql,
+ parm = new
+ {
+ m_id = BomID,
+ seq = ds.Rows[i][2].ToString().Trim(),
+ smateriel_code = ds.Rows[i][3].ToString().Trim(),
+ base_quantity = ds.Rows[i][6].ToString().Trim(),
+ loss_quantity = ds.Rows[i][7].ToString().Trim(),
+ total_quantity = ds.Rows[i][8].ToString().Trim(),
+ pn_type = ds.Rows[i][9].ToString().Trim()
+ }
+ });
+ }
+ }
+
+ bool aa = DapperHelper.DoTransaction(list);
+ if (aa)
+ {
+ StuCode = "200";
+ message = "瀵煎叆鎴愬姛锛�";
+ }
+ else
+ {
+ StuCode = "300";
+ message = "瀵煎叆澶辫触锛�";
+ }
}
catch (Exception e)
{
--
Gitblit v1.9.3