| | |
| | | using System.Collections.Generic; |
| | | using System.Data; |
| | | using System.Data.SqlClient; |
| | | using System.IO; |
| | | using System.Web; |
| | | using System.Web.Http; |
| | | using WebAPI.Controllers.SCGL.æ¥è®¡å管ç; |
| | | using WebAPI.Models; |
| | | using WebAPI.Service; |
| | | |
| | |
| | | return model; |
| | | } |
| | | |
| | | #region çäº§èµæº æä»¶ä¸ä¼ |
| | | [Route("Gy_Source/Gy_Source_Excel")] |
| | | [HttpPost] |
| | | public object Gy_Source_Excel() |
| | | { |
| | | 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()); |
| | | } |
| | | |
| | | //模æ¿ç¼ºå°å ä½éè¦ä»æ°æ®åºä¸æ¥è¯¢åºæ¥æ¾ç¤ºå¨é¡µé¢çåæ®µ |
| | | tb2.Columns.Add("HOrgID", typeof(Int32));//ç»ç»ID |
| | | tb2.Columns.Add("HDeptID", typeof(Int32));//é¨é¨ID |
| | | tb2.Columns.Add("HEmpID", typeof(Int32));//è´è´£äººID |
| | | tb2.Columns.Add("HWorkCenterID", typeof(Int32));//å·¥ä½ä¸å¿ID |
| | | |
| | | //æ·»å æ°æ® |
| | | 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 (!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 - 1; i++) |
| | | { |
| | | string HSourceName = ""; |
| | | string HSourceNum = ""; |
| | | string HORGNumber = ""; |
| | | string HORGName = ""; |
| | | string HDeptNum = ""; |
| | | string HDeptName = ""; |
| | | string HEmpNum = ""; |
| | | string HEmpName = ""; |
| | | string HCentNum = ""; |
| | | string HCentName = ""; |
| | | string HWorkTime = ""; |
| | | |
| | | HSourceName = DBUtility.ClsPub.isStrNull(tb2.Rows[i]["çäº§èµæºåç§°"].ToString()); |
| | | HSourceNum = DBUtility.ClsPub.isStrNull(tb2.Rows[i]["çäº§èµæºä»£ç "].ToString()); |
| | | HORGNumber = DBUtility.ClsPub.isStrNull(tb2.Rows[i]["ç»ç»ä»£ç "].ToString()); |
| | | HORGName = DBUtility.ClsPub.isStrNull(tb2.Rows[i]["ç»ç»åç§°"].ToString()); |
| | | HDeptNum = DBUtility.ClsPub.isStrNull(tb2.Rows[i]["é¨é¨ä»£ç "].ToString()); |
| | | HDeptName = DBUtility.ClsPub.isStrNull(tb2.Rows[i]["é¨é¨åç§°"].ToString()); |
| | | HEmpNum = DBUtility.ClsPub.isStrNull(tb2.Rows[i]["è´è´£äººä»£ç "].ToString()); |
| | | HEmpName = DBUtility.ClsPub.isStrNull(tb2.Rows[i]["è´è´£äºº"].ToString()); |
| | | HCentNum = DBUtility.ClsPub.isStrNull(tb2.Rows[i]["å·¥ä½ä¸å¿ä»£ç "].ToString()); |
| | | HCentName = DBUtility.ClsPub.isStrNull(tb2.Rows[i]["å·¥ä½ä¸å¿åç§°"].ToString()); |
| | | HWorkTime = DBUtility.ClsPub.isStrNull(tb2.Rows[i]["åçå·¥æ¶"].ToString()); |
| | | |
| | | //æ£æ¥ç©æ |
| | | int index = i + 1; |
| | | |
| | | if (HORGNumber != "") |
| | | { |
| | | |
| | | |
| | | //æ¥è¯¢ç»ç» |
| | | ds = oCN.RunProcReturn("select * from Xt_ORGANIZATIONS where HNumber='" + HORGNumber + "' and Hname='" + HORGName + "'", "Xt_ORGANIZATIONS"); |
| | | |
| | | if (ds.Tables[0].Rows.Count == 0) |
| | | { |
| | | objJsonResult.code = "0"; |
| | | objJsonResult.count = 0; |
| | | objJsonResult.Message = "第" + index + "è¡,ç»ç»ä¸åå¨ï¼"; |
| | | objJsonResult.data = null; |
| | | return objJsonResult; |
| | | } |
| | | else |
| | | { |
| | | tb2.Rows[i]["HOrgID"] = ds.Tables[0].Rows[0]["HItemID"].ToString(); |
| | | } |
| | | string HORGid = ds.Tables[0].Rows[0]["HItemID"].ToString(); |
| | | |
| | | |
| | | //æ¥è¯¢é¨é¨ |
| | | ds = oCN.RunProcReturn("select * from Gy_Department where HNumber='" + HDeptNum + "' and HUSEORGID=" + HORGid + "", "Gy_Department"); |
| | | |
| | | if (ds.Tables[0].Rows.Count == 0) |
| | | { |
| | | objJsonResult.code = "0"; |
| | | objJsonResult.count = 0; |
| | | objJsonResult.Message = "第" + index + "è¡,该" + HORGName + "ç»ç»,é¨é¨:" + HDeptName + ",ä¸åå¨ï¼"; |
| | | objJsonResult.data = null; |
| | | return objJsonResult; |
| | | } |
| | | else |
| | | { |
| | | tb2.Rows[i]["HDeptID"] = ds.Tables[0].Rows[0]["HItemID"].ToString(); |
| | | } |
| | | |
| | | //æ¥è¯¢è´è´£äºº |
| | | ds = oCN.RunProcReturn("select * from Gy_Employee where HNumber='" + HEmpNum + "' and HUSEORGID=" + HORGid + "", "Gy_Employee"); |
| | | |
| | | if (ds.Tables[0].Rows.Count == 0) |
| | | { |
| | | objJsonResult.code = "0"; |
| | | objJsonResult.count = 0; |
| | | objJsonResult.Message = "第" + index + "è¡,该" + HORGName + "ç»ç»,è´è´£äºº:" + HEmpName + ",ä¸åå¨ï¼"; |
| | | objJsonResult.data = null; |
| | | return objJsonResult; |
| | | } |
| | | else |
| | | { |
| | | tb2.Rows[i]["HEmpID"] = ds.Tables[0].Rows[0]["HItemID"].ToString(); |
| | | } |
| | | |
| | | //æ¥è¯¢å·¥ä½ä¸å¿ |
| | | ds = oCN.RunProcReturn("select * from Gy_WorkCenter where HNumber='" + HCentNum + "' and HUSEORGID=" + HORGid + "", "Gy_WorkCenter"); // |
| | | |
| | | if (ds.Tables[0].Rows.Count == 0) |
| | | { |
| | | objJsonResult.code = "0"; |
| | | objJsonResult.count = 0; |
| | | objJsonResult.Message = "第" + index + "è¡,该" + HORGName + "ç»ç»,å·¥ä½ä¸å¿:" + HCentName + ",ä¸åå¨ï¼"; |
| | | objJsonResult.data = null; |
| | | return objJsonResult; |
| | | } |
| | | else |
| | | { |
| | | tb2.Rows[i]["HWorkCenterID"] = ds.Tables[0].Rows[0]["HItemID"].ToString(); |
| | | } |
| | | |
| | | //åçå·¥æ¶ |
| | | if (HWorkTime == "") |
| | | { |
| | | objJsonResult.code = "0"; |
| | | objJsonResult.count = 0; |
| | | objJsonResult.Message = "第" + index + "è¡,åçå·¥æ¶ä¸è½ä¸ºç©ºï¼"; |
| | | objJsonResult.data = null; |
| | | return objJsonResult; |
| | | } |
| | | |
| | | //çäº§èµæºåç§° |
| | | if (HSourceName == "") |
| | | { |
| | | objJsonResult.code = "0"; |
| | | objJsonResult.count = 0; |
| | | objJsonResult.Message = "第" + index + "è¡,çäº§èµæºåç§°ä¸è½ä¸ºç©ºï¼"; |
| | | objJsonResult.data = null; |
| | | return objJsonResult; |
| | | } |
| | | |
| | | //çäº§èµæºä»£ç |
| | | if (HSourceNum == "") |
| | | { |
| | | objJsonResult.code = "0"; |
| | | objJsonResult.count = 0; |
| | | objJsonResult.Message = "第" + index + "è¡,çäº§èµæºä»£ç ä¸è½ä¸ºç©ºï¼"; |
| | | objJsonResult.data = null; |
| | | return objJsonResult; |
| | | } |
| | | } |
| | | else |
| | | { |
| | | objJsonResult.code = "1"; |
| | | objJsonResult.count = 1; |
| | | objJsonResult.Message = "第" + index + "è¡,ç»ç»ä»£ç 为空"; |
| | | objJsonResult.data = null; |
| | | return objJsonResult; |
| | | } |
| | | } |
| | | |
| | | 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; |
| | | } |
| | | } |
| | | #endregion |
| | | |
| | | #region çäº§èµæº 导å
¥(ä¿å) |
| | | [Route("Gy_Source/Gy_Source_btnSave")] |
| | | [HttpPost] |
| | | public object Gy_Source_btnSave([FromBody] JObject sMainSub) |
| | | { |
| | | var _value = sMainSub["sMainSub"].ToString(); |
| | | string msg1 = _value.ToString(); |
| | | string[] sArray = msg1.Split(new string[] { "&å" }, StringSplitOptions.RemoveEmptyEntries); |
| | | string msg2 = sArray[0].ToString(); |
| | | string user = sArray[1].ToString(); |
| | | try |
| | | { |
| | | if (!DBUtility.ClsPub.Security_Log("Gy_Source_Edit", 1, false, user)) |
| | | { |
| | | objJsonResult.code = "0"; |
| | | objJsonResult.count = 0; |
| | | objJsonResult.Message = "æ ä¿åæéï¼"; |
| | | objJsonResult.data = null; |
| | | return objJsonResult; |
| | | } |
| | | |
| | | List<object> Excel = Newtonsoft.Json.JsonConvert.DeserializeObject<List<object>>(msg2); |
| | | List<Dictionary<string, string>> list = new List<Dictionary<string, string>>(); |
| | | |
| | | foreach (JObject item in Excel) |
| | | { |
| | | Dictionary<string, string> dic = new Dictionary<string, string>(); |
| | | foreach (var itm in item.Properties()) |
| | | { |
| | | dic.Add(itm.Name, itm.Value.ToString()); |
| | | } |
| | | list.Add(dic); |
| | | } |
| | | |
| | | oCN.BeginTran(); |
| | | int i = 1; |
| | | foreach (Dictionary<string, string> item in list) |
| | | { |
| | | string HDeptID = item["HDeptID"].ToString();//é¨é¨ |
| | | string HEmpID = item["HEmpID"].ToString();//è´è´£äºº |
| | | string HOrgID = item["HOrgID"].ToString();//ç»ç»ID |
| | | string HWorkCenterID = item["HWorkCenterID"].ToString();//å·¥ä½ä¸å¿ |
| | | string HsouceName = item["çäº§èµæºåç§°"].ToString(); |
| | | string HsouceNum = item["çäº§èµæºä»£ç "].ToString(); |
| | | string HWorkTime = item["åçå·¥æ¶"].ToString(); |
| | | string sShortNumber; |
| | | sShortNumber = DBUtility.ClsPub.GetShortNumber(HsouceName);//ç代ç |
| | | if (sShortNumber.Trim() == "") |
| | | { |
| | | objJsonResult.code = "0"; |
| | | objJsonResult.count = 0; |
| | | objJsonResult.Message = "ä¿å失败ï¼ç代ç 为空ï¼"; |
| | | objJsonResult.data = 1; |
| | | return objJsonResult; |
| | | } |
| | | int HEndFlag = 1;//æ«çº§æ å¿ |
| | | int HLevel = DBUtility.ClsPub.GetLevel(HsouceName); //ç级 |
| | | |
| | | |
| | | if (!DBUtility.ClsPub.AllowNumber(HsouceNum.Trim())) |
| | | { |
| | | objJsonResult.code = "0"; |
| | | objJsonResult.count = 0; |
| | | objJsonResult.Message = "ä¿å失败ï¼ä»£ç ä¸ä¸è½åºç°è¿ç»â.âå¹¶ä¸é¦ä½æ«ä½ä¸è½ä¸ºâ.âï¼"; |
| | | objJsonResult.data = 1; |
| | | return objJsonResult; |
| | | } |
| | | |
| | | ds = oCN.RunProcReturn("select * from Gy_Source where HNumber='" + HsouceNum + "'", "Gy_Source"); |
| | | |
| | | if (ds.Tables[0].Rows.Count == 0) |
| | | { |
| | | |
| | | string sql = "insert into Gy_Source(HName,HNumber,HDeptID,HEmpID,HUSEORGID,HCenterID,HWorkTimes,HShortNumber,HEndFlag,HLevel,HHelpCode,HParentID,HStopflag,HRemark,HType)" + |
| | | $"values('{HsouceName}', '{HsouceNum}',{HDeptID}, {HEmpID}, '{HOrgID}', '{HWorkCenterID}', {HWorkTime},'{sShortNumber}',{HEndFlag},{HLevel},'',0,0,'','')"; |
| | | oCN.RunProc(sql); |
| | | } |
| | | else |
| | | { |
| | | oCN.RunProc(" update Gy_Source set HWorkTimes=" + HWorkTime + ",HDeptID="+ HDeptID + ",HEmpID="+ HEmpID + ",HCenterID="+HWorkCenterID+" where HNumber='" + HsouceNum +"'"); |
| | | } |
| | | |
| | | i++; |
| | | } |
| | | |
| | | oCN.Commit(); |
| | | |
| | | objJsonResult.code = "1"; |
| | | objJsonResult.count = 1; |
| | | objJsonResult.Message = "导å
¥æå!"; |
| | | objJsonResult.data = null; |
| | | return objJsonResult; |
| | | } |
| | | catch (Exception e) |
| | | { |
| | | LogService.Write(e); |
| | | objJsonResult.code = "0"; |
| | | objJsonResult.count = 0; |
| | | objJsonResult.Message = "Exceptionï¼" + e.ToString(); |
| | | objJsonResult.data = null; |
| | | return objJsonResult; |
| | | } |
| | | } |
| | | #endregion |
| | | |
| | | #region çäº§èµæº æ å½¢å¾ |
| | | public class TreeModel |
| | | { |