From ee9f4215d0750a15031bfb89f0f347dadc8c1b70 Mon Sep 17 00:00:00 2001
From: 王 垚 <1402714037@qq.com>
Date: 星期三, 07 十二月 2022 13:10:46 +0800
Subject: [PATCH] 提交
---
WebAPI/Controllers/BaseSet/Gy_UnitController.cs | 5 +
WebAPI/Controllers/SCGL/日计划管理/DataHelper.cs | 79 +++++++++++++++++++
Pub_Class/CustomerCls/ClsSqlHelper.cs | 72 ++++++++++++-----
WebAPI/WebAPI.csproj.user | 2
WebAPI/Controllers/BaseSet/Gy_WorkDayController.cs | 16 ++-
WebAPI/Models/基础资料/WorkDay.cs | 4 +
WebAPI/Controllers/BaseSet/Gy_WarehouseController.cs | 12 ++-
WebAPI/Models/基础资料/Warehouse.cs | 3
8 files changed, 155 insertions(+), 38 deletions(-)
diff --git a/Pub_Class/CustomerCls/ClsSqlHelper.cs b/Pub_Class/CustomerCls/ClsSqlHelper.cs
index 2420e00..1e8b508 100644
--- a/Pub_Class/CustomerCls/ClsSqlHelper.cs
+++ b/Pub_Class/CustomerCls/ClsSqlHelper.cs
@@ -7,23 +7,23 @@
namespace Pub_Class
{
- public class ClsSqlHelper:IDisposable
+ public class ClsSqlHelper : IDisposable
{
private SqlConnection MainCn;//鍒涘缓杩炴帴瀵硅薄
public string strCon;
SqlTransaction MainTran;
public string sServer;
- public string sDataBase ;
+ public string sDataBase;
public string sUser;
public string sPassword;
//public string sTextBox = "HXErp_Test.ini";
public string sTextBox = "HXErp.ini";
//寮�濮嬩簨鍔�
- public void BeginTran()
+ public void BeginTran()
{
if (!this.CnOpen())
return;
- MainTran=MainCn.BeginTransaction();
+ MainTran = MainCn.BeginTransaction();
}
//缁撴潫浜嬪姟
public void Commit()
@@ -45,7 +45,7 @@
ClsPub.AppPath = System.Environment.CurrentDirectory;
//ClsPub.AppPath = @"C:\Windows\System32";
}
- sServer = ClsIni.ReadIni("Erp", "Server", ClsPub.AppPath + @"\"+sTextBox);
+ sServer = ClsIni.ReadIni("Erp", "Server", ClsPub.AppPath + @"\" + sTextBox);
sDataBase = ClsIni.ReadIni("Erp", "DataBase", ClsPub.AppPath + @"\" + sTextBox);
sUser = ClsIni.ReadIni("Erp", "UserName", ClsPub.AppPath + @"\" + sTextBox);
sPassword = ClsIni.ReadIni("Erp", "PassWord", ClsPub.AppPath + @"\" + sTextBox);
@@ -54,13 +54,13 @@
public bool CheckOpen()
{
- if (sServer == "" || sServer == "娌℃湁鎵惧埌锛�" )
+ if (sServer == "" || sServer == "娌℃湁鎵惧埌锛�")
{
throw new Exception("閿欒鐨勬湇鍔″櫒鍚�!!" + System.Environment.CurrentDirectory);
}
if (sDataBase == "" || sDataBase == "娌℃湁鎵惧埌锛�")
{
- throw new Exception("閿欒鐨勬暟鎹簱鍚�!!" +System.Environment.CurrentDirectory);
+ throw new Exception("閿欒鐨勬暟鎹簱鍚�!!" + System.Environment.CurrentDirectory);
}
SqlConnection oCn = new SqlConnection("Data Source=" + sServer + ";DataBase=master;User ID=" + sUser + ";PWD=" + sPassword + ";max pool size=32767");
try
@@ -78,7 +78,7 @@
}
}
- private bool CnOpen()//OPEN鏁版嵁搴撹繛鎺�
+ private bool CnOpen()//OPEN鏁版嵁搴撹繛鎺�
{
if (sServer == "" || sServer == "娌℃湁鎵惧埌锛�")
{
@@ -88,7 +88,7 @@
{
throw new Exception("閿欒鐨勬暟鎹簱鍚�!");
}
- if (MainCn==null)
+ if (MainCn == null)
{
MainCn = new SqlConnection("Data Source=" + sServer + ";DataBase=" + sDataBase + ";User ID=" + sUser + ";PWD=" + sPassword + ";max pool size=32767");
}
@@ -117,10 +117,10 @@
public void CnDispose()//閲婃斁璧勬簮
{
- if(MainCn!=null)
- {
+ if (MainCn != null)
+ {
MainCn.Dispose();
- MainCn=null;
+ MainCn = null;
}
}
/// <summary>
@@ -133,7 +133,7 @@
/// <returns>鏂扮殑 parameter 瀵硅薄</returns>
public SqlParameter MakeInParam(string ParamName, SqlDbType DbType, int Size, object Value)
{
- return MakeParam(ParamName, DbType, Size, ParameterDirection.Input, Value);
+ return MakeParam(ParamName, DbType, Size, ParameterDirection.Input, Value);
}
/// <summary>
/// 鍒濆鍖栧弬鏁板��
@@ -192,13 +192,13 @@
/// <param name="procName"></param>
/// <param name="sErrMessage"></param>
/// <returns></returns>
- public void RunProc(string procName,ref string sErr)
+ public void RunProc(string procName, ref string sErr)
{
if (!this.CnOpen())
{
sErr = "杩炴帴鏁版嵁搴撳け璐ワ紒";
return;
-
+
}
try
{
@@ -211,7 +211,7 @@
}
catch (Exception e)
{
- sErr =e.Message;
+ sErr = e.Message;
ClsPub.sErrInfo = e.Message;
throw (e);
//return;
@@ -226,7 +226,7 @@
/// <param name="tbName">琛ㄥ悕</param>
/// <param name="sErrMessage">寮傚父淇℃伅</param>
/// <returns></returns>
- public DataSet RunProcReturn(string procName, SqlParameter[] prams, string tbName )
+ public DataSet RunProcReturn(string procName, SqlParameter[] prams, string tbName)
{
SqlDataAdapter dap = CreateDataAdaper(procName, prams);
if (dap == null)
@@ -252,7 +252,7 @@
/// <param name="tbName">琛ㄥ悕</param>
/// <param name="sErrMessage">寮傚父淇℃伅</param>
/// <returns></returns>
- public DataSet RunProcReturn(string procName, string tbName )
+ public DataSet RunProcReturn(string procName, string tbName)
{
SqlDataAdapter dap = CreateDataAdaper(procName, null);
DataSet ds = new DataSet();
@@ -276,7 +276,7 @@
/// <param name="tbName">琛ㄥ悕</param>
/// <param name="sErrMessage">寮傚父淇℃伅</param>
/// <returns></returns>
- public DataSet RunProcReturn(string procName, string tbName,ref string sErr)
+ public DataSet RunProcReturn(string procName, string tbName, ref string sErr)
{
SqlDataAdapter dap = CreateDataAdaper(procName, null);
DataSet ds = new DataSet();
@@ -366,7 +366,7 @@
}
- public SqlDataReader RunReader(string sSQL,string tbName,ref string sErr)
+ public SqlDataReader RunReader(string sSQL, string tbName, ref string sErr)
{
if (!this.CnOpen())
{
@@ -384,12 +384,42 @@
catch (Exception e)
{
ClsPub.sErrInfo = e.Message;
- sErr = e.Message;
+ sErr = e.Message;
return null;
}
}
+ /// <summary>
+ /// 鎵归噺鎻掑叆鏁版嵁搴�
+ /// </summary>
+ /// <param name="TableName">鐩爣琛�</param>
+ /// <param name="dt">婧愭暟鎹�</param>
+ public bool SqlBulkCopyByDatatable(string TableName, DataTable dt)
+ {
+ string connectionString = $"Data Source={sServer};DataBase={sDataBase};User ID={sUser};PWD={sPassword}";
+ using (SqlConnection conn = new SqlConnection(connectionString))
+ {
+ using (SqlBulkCopy sqlbulkcopy = new SqlBulkCopy(connectionString, SqlBulkCopyOptions.UseInternalTransaction))
+ {
+ try
+ {
+ sqlbulkcopy.DestinationTableName = TableName;
+ for (int i = 0; i < dt.Columns.Count; i++)
+ {
+ sqlbulkcopy.ColumnMappings.Add(dt.Columns[i].ColumnName, dt.Columns[i].ColumnName);
+ }
+ sqlbulkcopy.WriteToServer(dt);
+ return true;
+ }
+ catch (System.Exception ex)
+ {
+ //ex.Message;
+ return false;
+ }
+ }
+ }
+ }
#region IDisposable 鎴愬憳
public void Dispose()
diff --git a/WebAPI/Controllers/BaseSet/Gy_UnitController.cs b/WebAPI/Controllers/BaseSet/Gy_UnitController.cs
index fb7252d..dba24d0 100644
--- a/WebAPI/Controllers/BaseSet/Gy_UnitController.cs
+++ b/WebAPI/Controllers/BaseSet/Gy_UnitController.cs
@@ -1,5 +1,7 @@
-锘縰sing Newtonsoft.Json;
+锘縰sing Model;
+using Newtonsoft.Json;
using Newtonsoft.Json.Linq;
+using NPOI.SS.Formula.Functions;
using Pub_Class;
using System;
using System.Collections;
@@ -7,6 +9,7 @@
using System.Data;
using System.Data.SqlClient;
using System.Web.Http;
+using WebAPI.Controllers.SCGL.鏃ヨ鍒掔鐞�;
using WebAPI.Models;
using WebAPI.Service;
diff --git a/WebAPI/Controllers/BaseSet/Gy_WarehouseController.cs b/WebAPI/Controllers/BaseSet/Gy_WarehouseController.cs
index dff852c..4cfa986 100644
--- a/WebAPI/Controllers/BaseSet/Gy_WarehouseController.cs
+++ b/WebAPI/Controllers/BaseSet/Gy_WarehouseController.cs
@@ -1,4 +1,5 @@
-锘縰sing Newtonsoft.Json;
+锘縰sing Model;
+using Newtonsoft.Json;
using Newtonsoft.Json.Linq;
using Pub_Class;
using System;
@@ -7,6 +8,7 @@
using System.Data;
using System.Data.SqlClient;
using System.Web.Http;
+using WebAPI.Controllers.SCGL.鏃ヨ鍒掔鐞�;
using WebAPI.Models;
namespace WebAPI.Controllers
{
@@ -221,7 +223,7 @@
long HParentID = list[0].HParentID;
long HLevel = list[0].HLevel;
bool HEndFlag = list[0].HEndFlag;
- bool HStopflag = list[0].HStopflag;
+ bool HStopflag = list[0].HStopFlag;
string HRemark = list[0].HRemark;
string HBarCode = list[0].HBarCode;
long HEmpID = list[0].HEmpID;
@@ -304,7 +306,7 @@
long HParentID = list[0].HParentID;
long HLevel = list[0].HLevel;
bool HEndFlag = list[0].HEndFlag;
- bool HStopflag = list[0].HStopflag;
+ bool HStopflag = list[0].HStopFlag;
string HRemark = list[0].HRemark;
string HBarCode = list[0].HBarCode;
long HEmpID = list[0].HEmpID;
@@ -411,6 +413,8 @@
_value = "[" + _value.ToString() + "]";
List<Warehouse> list = Newtonsoft.Json.JsonConvert.DeserializeObject<List<Warehouse>>(_value);
string sql = string.Empty;
+ //DataTable dt = DataHelper.ListToDataTable<Warehouse>(list);
+ //oCN.SqlBulkCopyByDatatable("Gy_Warehouse", dt);
//淇濆瓨
oCN.BeginTran();
sql = $"delete Gy_Warehouse where HItemID = {list[0].HItemID}";
@@ -421,7 +425,7 @@
" (HItemID,HNumber,HName,HHelpCode,HShortNumber,HParentID" +
",HLevel,HEndFlag,HStopflag,HRemark,HEmpID,HAddress,HPhone,HUSEORGID) " +
" Values('" + list[0].HItemID + "','" + list[0].HNumber + "','" +list[0].HName + "','" + list[0].HHelpCode + "','" + list[0].HShortNumber + "'," + list[0].HParentID.ToString() +
- "," + list[0].HLevel.ToString() + "," + Convert.ToString(list[0].HEndFlag ? 1 : 0) + "," + Convert.ToString(list[0].HStopflag ? 1 : 0) + ",'" + list[0].HRemark + "'," + list[0].HEmpID.ToString() + ",'" + list[0].HAddress + "','" + list[0].HPhone + "','" + list[0].HUSEORGID + "')", ref DBUtility.ClsPub.sExeReturnInfo);
+ "," + list[0].HLevel.ToString() + "," + Convert.ToString(list[0].HEndFlag ? 1 : 0) + "," + Convert.ToString(list[0].HStopFlag ? 1 : 0) + ",'" + list[0].HRemark + "'," + list[0].HEmpID.ToString() + ",'" + list[0].HAddress + "','" + list[0].HPhone + "','" + list[0].HUSEORGID + "')", ref DBUtility.ClsPub.sExeReturnInfo);
//淇敼涓婄骇涓洪潪鏈骇浠ｇ爜
sql = "set identity_insert Gy_Warehouse off";
oCN.RunProc(sql);
diff --git a/WebAPI/Controllers/BaseSet/Gy_WorkDayController.cs b/WebAPI/Controllers/BaseSet/Gy_WorkDayController.cs
index 2ccd2ff..a0fd6b2 100644
--- a/WebAPI/Controllers/BaseSet/Gy_WorkDayController.cs
+++ b/WebAPI/Controllers/BaseSet/Gy_WorkDayController.cs
@@ -95,16 +95,14 @@
//鍙嶅簭鍒楀寲
msg2 = "[" + msg2.ToString() + "]";
List<WorkDay> list = Newtonsoft.Json.JsonConvert.DeserializeObject<List<WorkDay>>(msg2);
-
long HItemID = list[0].HItemID;
-
//淇濆瓨
//淇濆瓨瀹屾瘯鍚庡鐞�
if (HItemID == 0)
{
oCN.BeginTran();
- string sql = string.Format(@"insert into Gy_WorkDay (HDate,HWorkBegin,HWorkEnd,HNoonbreakBegin,HNoonbreakEnd,HNightbreakBegin,HNightbreakEnd)
-values ('{0}','{1}','{2}','{3}','{4}','{5}','{6}')", list[0].HDate, list[0].HWorkBegin, list[0].HWorkEnd, list[0].HNoonbreakBegin, list[0].HNoonbreakEnd, list[0].HNightbreakBegin, list[0].HNightbreakEnd);
+ string sql = string.Format(@"insert into Gy_WorkDay (HDate,HWorkBegin,HWorkEnd,HNoonbreakBegin,HNoonbreakEnd,HNightbreakBegin,HNightbreakEnd,HNoonbreakBegin2,HNoonbreakEnd2,HNightbreakBegin2,HNightbreakEnd2)
+values ('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}')", list[0].HDate, list[0].HWorkBegin, list[0].HWorkEnd, list[0].HNoonbreakBegin, list[0].HNoonbreakEnd, list[0].HNightbreakBegin, list[0].HNightbreakEnd, list[0].HNoonbreakBegin2, list[0].HNoonbreakEnd2, list[0].HNightbreakBegin2, list[0].HNightbreakEnd2);
oCN.RunProc(sql);
oCN.Commit();
}
@@ -118,9 +116,13 @@
",HWorkBegin='" + list[0].HWorkBegin + "'" +
",HWorkEnd='" + list[0].HWorkEnd + "'" +
",HNoonbreakBegin='" + list[0].HNoonbreakBegin + "'" +
- ",HNoonbreakEnd=" + list[0].HNoonbreakEnd +
- ",HNightbreakBegin=" + list[0].HNightbreakBegin +
- ",HNightbreakEnd=" + list[0].HNightbreakEnd +
+ ",HNoonbreakEnd='" + list[0].HNoonbreakEnd + "'" +
+ ",HNightbreakBegin='" + list[0].HNightbreakBegin + "'" +
+ ",HNightbreakEnd='" + list[0].HNightbreakEnd + "'" +
+ ",HNoonbreakBegin2='" + list[0].HNoonbreakBegin2 + "'" +
+ ",HNoonbreakEnd2='" + list[0].HNoonbreakEnd2 + "'" +
+ ",HNightbreakBegin2='" + list[0].HNightbreakBegin2 + "'" +
+ ",HNightbreakEnd2='" + list[0].HNightbreakEnd2 + "'" +
" Where HItemID=" + HItemID, ref DBUtility.ClsPub.sExeReturnInfo);
//
oCN.Commit();
diff --git "a/WebAPI/Controllers/SCGL/\346\227\245\350\256\241\345\210\222\347\256\241\347\220\206/DataHelper.cs" "b/WebAPI/Controllers/SCGL/\346\227\245\350\256\241\345\210\222\347\256\241\347\220\206/DataHelper.cs"
index b9adbec..ed0e600 100644
--- "a/WebAPI/Controllers/SCGL/\346\227\245\350\256\241\345\210\222\347\256\241\347\220\206/DataHelper.cs"
+++ "b/WebAPI/Controllers/SCGL/\346\227\245\350\256\241\345\210\222\347\256\241\347\220\206/DataHelper.cs"
@@ -1,5 +1,8 @@
-锘縰sing System;
+锘縰sing Newtonsoft.Json;
+using System;
+using System.Collections;
using System.Collections.Generic;
+using System.ComponentModel;
using System.Data;
using System.Linq;
using System.Reflection;
@@ -19,7 +22,7 @@
List<T> list = new List<T>();
if (ds.Tables[0].Rows.Count > 0)
{
- foreach (DataRow row in ds.Tables[0].Rows)
+ foreach (DataRow row in ds.Tables[0].Rows)
{
list.Add(row.Put(new T()));
}
@@ -167,5 +170,77 @@
}
return $"update {table} set {strSet} where 1=1 {condition}";
}
+
+
+
+
+ /// <summary>
+ /// 灏哃ist杞崲鎴怐ataTable
+ /// </summary>
+ /// <typeparam name="T"></typeparam>
+ /// <param name="data"></param>
+ /// <returns></returns>
+ public static DataTable ToDataTable<T>(this IList<T> data)
+ {
+ PropertyDescriptorCollection properties = TypeDescriptor.GetProperties(typeof(T));
+ DataTable dt = new DataTable();
+ for (int i = 0; i < properties.Count; i++)
+ {
+ PropertyDescriptor property = properties[i];
+ dt.Columns.Add(property.Name, property.PropertyType);
+ }
+ object[] values = new object[properties.Count];
+ foreach (T item in data)
+ {
+ for (int i = 0; i < values.Length; i++)
+ {
+ values[i] = properties[i].GetValue(item);
+ }
+ dt.Rows.Add(values);
+ }
+ return dt;
+ }
+
+
+ /// <summary>
+ /// 灏哃ist杞崲涓篋ataTable
+ /// </summary>
+ /// <param name="list">璇锋眰鏁版嵁</param>
+ /// <returns></returns>
+ public static DataTable ListToDataTable<T>(List<T> list)
+ {
+ //鍒涘缓涓�涓悕涓�"tableName"鐨勭┖琛�
+ DataTable dt = new DataTable("tableName");
+
+ //鍒涘缓浼犲叆瀵硅薄鍚嶇О鐨勫垪
+ foreach (var item in list.FirstOrDefault().GetType().GetProperties())
+ {
+ dt.Columns.Add(item.Name);
+ }
+ //寰幆瀛樺偍
+ foreach (var item in list)
+ {
+ //鏂板姞琛�
+ DataRow value = dt.NewRow();
+ //鏍规嵁DataTable涓殑鍊硷紝杩涜瀵瑰簲鐨勮祴鍊�
+ foreach (DataColumn dtColumn in dt.Columns)
+ {
+ int i = dt.Columns.IndexOf(dtColumn);
+ //鍩哄厓鍏冪礌锛岀洿鎺ュ鍒讹紝瀵硅薄绫诲瀷绛夛紝杩涜搴忓垪鍖�
+ if (value.GetType().IsPrimitive)
+ {
+ value[i] = item.GetType().GetProperty(dtColumn.ColumnName).GetValue(item);
+ }
+ else
+ {
+ value[i] = JsonConvert.SerializeObject(item.GetType().GetProperty(dtColumn.ColumnName).GetValue(item));
+ }
+ }
+ dt.Rows.Add(value);
+ }
+ return dt;
+ }
+
}
+
}
diff --git "a/WebAPI/Models/\345\237\272\347\241\200\350\265\204\346\226\231/Warehouse.cs" "b/WebAPI/Models/\345\237\272\347\241\200\350\265\204\346\226\231/Warehouse.cs"
index 813b237..f0af683 100644
--- "a/WebAPI/Models/\345\237\272\347\241\200\350\265\204\346\226\231/Warehouse.cs"
+++ "b/WebAPI/Models/\345\237\272\347\241\200\350\265\204\346\226\231/Warehouse.cs"
@@ -16,12 +16,11 @@
public long HParentID { get; set; }
public string HRemark { get; set; }
public string HShortNumber { get; set; }
- public bool HStopflag { get; set; }
+ public bool HStopFlag { get; set; }
public string HUseFlag { get; set; }
public Int64 HEmpID { get; set; }
public string HAddress { get; set; }
public string HPhone { get; set; }
- public bool HIsStockMgr { get; set; }
public Int64 HSPGroupID { get; set; }
public string HBarCode { get; set; }
public Int64 HUSEORGID { get; set; }
diff --git "a/WebAPI/Models/\345\237\272\347\241\200\350\265\204\346\226\231/WorkDay.cs" "b/WebAPI/Models/\345\237\272\347\241\200\350\265\204\346\226\231/WorkDay.cs"
index 36320f9..00eb1e4 100644
--- "a/WebAPI/Models/\345\237\272\347\241\200\350\265\204\346\226\231/WorkDay.cs"
+++ "b/WebAPI/Models/\345\237\272\347\241\200\350\265\204\346\226\231/WorkDay.cs"
@@ -15,5 +15,9 @@
public DateTime HNoonbreakEnd { get; set; }
public DateTime HNightbreakBegin { get; set; }
public DateTime HNightbreakEnd { get; set; }
+ public DateTime HNoonbreakBegin2 { get; set; }
+ public DateTime HNoonbreakEnd2 { get; set; }
+ public DateTime HNightbreakBegin2 { get; set; }
+ public DateTime HNightbreakEnd2 { get; set; }
}
}
\ No newline at end of file
diff --git a/WebAPI/WebAPI.csproj.user b/WebAPI/WebAPI.csproj.user
index b5bf336..d152f2c 100644
--- a/WebAPI/WebAPI.csproj.user
+++ b/WebAPI/WebAPI.csproj.user
@@ -18,7 +18,7 @@
<IISExpressWindowsAuthentication />
<IISExpressUseClassicPipelineMode />
<UseGlobalApplicationHostFile />
- <ProjectView>ShowAllFiles</ProjectView>
+ <ProjectView>ProjectFiles</ProjectView>
<Controller_SelectedScaffolderID>MvcControllerEmptyScaffolder</Controller_SelectedScaffolderID>
<Controller_SelectedScaffolderCategoryPath>root/Common/MVC/Controller</Controller_SelectedScaffolderCategoryPath>
</PropertyGroup>
--
Gitblit v1.9.1