/// <summary> /// 建立簽核檔以及明細歷程 /// </summary> /// <param name="modelList"></param> public void CreateSignData(List <RinnaiForms> modelList) { //建立簽核ID string newSignID = string.Empty; #region 取出view傳來的model TrainViewModel model = modelList.Cast <TrainViewModel>().First(); newSignID = GetSeqIDUtils.GetSignDocID(model.Creator, "TR"); DataRow employeeData = _rootRepo.QueryForEmployeeByEmpID(model.SID); if (String.IsNullOrWhiteSpace(newSignID)) { throw new Exception("無法建立簽核ID,或系統忙碌中。請聯絡系統管理員!"); } model.SignDocID_FK = newSignID; model.DepartmentID_FK = employeeData != null ? employeeData["DepartmentID_FK"].ToString() : String.Empty; //已經帶入登入者的ID //model.ApplyID_FK = employeeData != null ? employeeData["EmployeeID"].ToString() : String.Empty; //基底model成員 #endregion 取出view傳來的model //欲寫入的簽核狀態 string status = ((int)SignTypeEnum.READYSIGN).ToString(); var createDateTime = DateTime.UtcNow.AddHours(8); #region 取得簽屬程序 var procedureData = _rootRepo.QueryForSignProcedureByRuleID(model.RuleID_FK); #endregion 取得簽屬程序 //SignLevel =>需簽核層數 var remainder = procedureData != null?Int32.Parse(procedureData["SignLevel"].ToString()) : -1; //判斷 FlowRule if (!_pwfRepo.IsFollowFlowRule(model.RuleID_FK, model.DepartmentID_FK)) { throw new Exception("不符合簽核規則,無法新增!"); } #region 取得簽核人員部門資料 var deptData = _rootRepo.QueryForDepartmentByDeptID(model.DepartmentID_FK); if (deptData == null) { throw new Exception("查無簽核人員所屬部門資料!"); } var upperDeptData = _pwfRepo.FindUpperDeptData(model.DepartmentID_FK, model.SID); var chiefID = upperDeptData.Keys.Single(); var currentSignLevelDeptID = upperDeptData.Values.Single(); #endregion 取得簽核人員部門資料 //建立多筆SQL Conditons 語句 var manipulationConditions = new List <MultiConditions>(); #region 建立簽核主檔SQL語句 var dic = new Conditions() { { "@SignDocID", model.SignDocID_FK }, { "@FormID_FK", model.FormID_FK }, { "@EmployeeID_FK", model.ApplyID_FK }, { "@CurrentSignLevelDeptID_FK", currentSignLevelDeptID }, { "@FinalStatus", status }, { "@remainder", remainder }, { "@Creator", model.Creator }, { "@CreateDate", createDateTime }, { "@SendDate", createDateTime }, }; // 利用DB Method建立Insert語句 , 並利用靜態字典 兜出資料庫與Table名稱 RinnaiPortal..signform_main var strSQL = _dc.ConstructInsertDML(RepositoryFactory.PotalConn["Catelog"] + "..signform_main", dic); manipulationConditions.Add(new MultiConditions() { { strSQL, dic } }); #endregion 建立簽核主檔SQL語句 #region 建立簽核明細檔SQL語句 dic = new Conditions() { { "@SignDocID_FK", model.SignDocID_FK }, { "@ChiefID_FK", chiefID }, { "@Status", status }, { "@Creator", model.Creator }, { "@CreateDate", createDateTime }, }; strSQL = _dc.ConstructInsertDML(RepositoryFactory.PotalConn["Catelog"] + "..signform_detail", dic); manipulationConditions.Add(new MultiConditions() { { strSQL, dic } }); #endregion 建立簽核明細檔SQL語句 #region 建立簽核歷程檔SQL語句 dic = new Conditions() { { "@SignDocID_FK", model.SignDocID_FK }, { "@FormID_FK", model.FormID_FK }, { "@EmployeeID_FK", model.ApplyID_FK }, { "@SendDate", createDateTime }, { "@CurrentSignLevelDeptID_FK", currentSignLevelDeptID }, { "@FinalStatus", status }, { "@remainder", remainder }, { "@Creator_Main", model.Creator }, { "@CreateDate_Main", createDateTime }, { "@DetailSignDocID_FK", model.SignDocID_FK }, { "@ChiefID_FK", chiefID }, { "@Status", status }, { "@Creator_Detail", model.Creator }, { "@CreateDate_Detail", createDateTime }, { "@LogDatetime", DateTime.Now }, }; strSQL = _dc.ConstructInsertDML(RepositoryFactory.PotalConn["Catelog"] + "..signform_log", dic); manipulationConditions.Add(new MultiConditions() { { strSQL, dic } }); #endregion 建立簽核歷程檔SQL語句 try { if (!_dc.ExecuteMultAndCheck(manipulationConditions)) { throw new Exception("新增受訓心得報告失敗!"); } } catch (Exception ex) { throw new Exception("新增受訓心得報告失敗! system errorMessages :" + ex.Message); } }
/// <summary> /// 新增忘刷單 送出簽核 主要函式 /// </summary> /// <param name="modelList"></param> public void CreateData(List <RinnaiForms> modelList) { var fogotList = modelList.Cast <ForgotPunchViewModel>(); var model = fogotList.First(); model.SignDocID_FK = GetSeqIDUtils.GetSignDocID(model.Creator, "FP"); if (String.IsNullOrWhiteSpace(model.SignDocID_FK)) { throw new Exception("系統忙碌中請稍候再試!"); } var status = "2"; var createDateTime = DateTime.Now; var procedureData = _rootRepo.QueryForSignProcedureByRuleID(model.RuleID_FK); var remainder = procedureData != null?Int32.Parse(procedureData["SignLevel"].ToString()) : -1; //判斷 FlowRule if (!_pwfRepo.IsFollowFlowRule(model.RuleID_FK, model.DepartmentID_FK)) { throw new Exception("不符合簽核規則,無法新增!"); } // 取得簽核人員部門資料 var deptData = _rootRepo.QueryForDepartmentByDeptID(model.DepartmentID_FK); if (deptData == null) { throw new Exception("查無簽核人員所屬部門資料!"); } var upperDeptData = _pwfRepo.FindUpperDeptData(model.DepartmentID_FK, model.EmployeeID_FK); var chiefID = upperDeptData.Keys.Single(); var currentSignLevelDeptID = upperDeptData.Values.Single(); // create SignForm_Main data var manipulationConditions = new List <MultiConditions>(); var dic = new Conditions() { { "@SignDocID", model.SignDocID_FK }, { "@FormID_FK", model.FormID_FK }, { "@EmployeeID_FK", model.ApplyID_FK }, { "@CurrentSignLevelDeptID_FK", currentSignLevelDeptID }, { "@FinalStatus", status }, { "@remainder", remainder }, { "@Creator", model.Creator }, { "@CreateDate", createDateTime }, { "@SendDate", createDateTime }, }; var strSQL = _dc.ConstructInsertDML("signform_main", dic); manipulationConditions.Add(new MultiConditions() { { strSQL, dic } }); // create SignForm_Detail data dic = new Conditions() { { "@SignDocID_FK", model.SignDocID_FK }, { "@ChiefID_FK", chiefID }, { "@Status", status }, { "@Creator", model.Creator }, { "@CreateDate", createDateTime }, }; strSQL = _dc.ConstructInsertDML("signform_detail", dic); manipulationConditions.Add(new MultiConditions() { { strSQL, dic } }); // add log dic = new Conditions() { { "@SignDocID_FK", model.SignDocID_FK }, { "@FormID_FK", model.FormID_FK }, { "@EmployeeID_FK", model.ApplyID_FK }, { "@SendDate", createDateTime }, { "@CurrentSignLevelDeptID_FK", currentSignLevelDeptID }, { "@FinalStatus", status }, { "@remainder", remainder }, { "@Creator_Main", model.Creator }, { "@CreateDate_Main", createDateTime }, { "@DetailSignDocID_FK", model.SignDocID_FK }, { "@ChiefID_FK", chiefID }, { "@Status", status }, { "@Creator_Detail", model.Creator }, { "@CreateDate_Detail", createDateTime }, { "@LogDatetime", DateTime.Now }, }; strSQL = _dc.ConstructInsertDML("signform_log", dic); manipulationConditions.Add(new MultiConditions() { { strSQL, dic } }); // create ForgotPunch data dic = new Conditions() { { "@FormID_FK", model.FormID_FK }, { "@SignDocID_FK", model.SignDocID_FK }, { "@ApplyID_FK", model.ApplyID_FK }, { "@ApplyDateTime", model.ApplyDateTime }, { "@EmployeeID_FK", model.EmployeeID_FK }, { "@DepartmentID_FK", model.DepartmentID_FK }, { "@PeriodType", model.PeriodType }, { "@ForgotPunchInDateTime", model.ForgotPunchInDateTime }, { "@ForgotPunchOutDateTime", model.ForgotPunchOutDateTime }, { "@Note", model.Note }, { "@Creator", model.Creator }, { "@CreateDate", model.CreateDate }, }; strSQL = _dc.ConstructInsertDML("forgotpunchform", dic); manipulationConditions.Add(new MultiConditions() { { strSQL, dic } }); try { if (!_dc.ExecuteMultAndCheck(manipulationConditions)) { throw new Exception("新增忘刷單失敗!"); } } catch (Exception ex) { throw new Exception("新增忘刷單失敗!" + ex.Message); } }
private void createData(RinnaiForms trainForm) { var model = trainForm as TrainViewModel; if (model == null) { throw new Exception("新增錯誤!"); } model.SignDocID_FK = GetSeqIDUtils.GetSignDocID(model.Creator, "TR"); if (String.IsNullOrWhiteSpace(model.SignDocID_FK)) { throw new Exception("系統忙碌中請稍候再試!"); } var status = "2"; var createDateTime = DateTime.Now; var procedureData = _rootRepo.QueryForSignProcedureByRuleID(model.RuleID_FK); var remainder = procedureData != null?Int32.Parse(procedureData["SignLevel"].ToString()) : -1; //判斷 FlowRule if (!_pwfRepo.IsFollowFlowRule(model.RuleID_FK, model.DepartmentID_FK)) { throw new Exception("不符合簽核規則,無法新增!"); } // 取得簽核人員部門資料 var deptData = _rootRepo.QueryForDepartmentByDeptID(model.DepartmentID_FK); if (deptData == null) { throw new Exception("查無簽核人員所屬部門資料!"); } // Cross DB,置換 _dc 為 Portal _dc = ConnectionFactory.GetPortalDC(); var upperDeptData = _pwfRepo.FindUpperDeptData(model.DepartmentID_FK, model.ApplyID_FK); var chiefID = upperDeptData.Keys.Single(); var currentSignLevelDeptID = upperDeptData.Values.Single(); // create SignForm_Main data var manipulationConditions = new List <MultiConditions>(); var dic = new Conditions() { { "@SignDocID", model.SignDocID_FK }, { "@FormID_FK", model.FormID_FK }, { "@EmployeeID_FK", model.ApplyID_FK }, { "@CurrentSignLevelDeptID_FK", currentSignLevelDeptID }, { "@FinalStatus", status }, { "@remainder", remainder }, { "@Creator", model.Creator }, { "@CreateDate", createDateTime }, { "@SendDate", createDateTime }, }; var strSQL = _dc.ConstructInsertDML("signform_main", dic); manipulationConditions.Add(new MultiConditions() { { strSQL, dic } }); // create SignForm_Detail data dic = new Conditions() { { "@SignDocID_FK", model.SignDocID_FK }, { "@ChiefID_FK", chiefID }, { "@Status", status }, { "@Creator", model.Creator }, { "@CreateDate", createDateTime }, }; strSQL = _dc.ConstructInsertDML("signform_detail", dic); manipulationConditions.Add(new MultiConditions() { { strSQL, dic } }); // add log dic = new Conditions() { { "@SignDocID_FK", model.SignDocID_FK }, { "@FormID_FK", model.FormID_FK }, { "@EmployeeID_FK", model.ApplyID_FK }, { "@SendDate", createDateTime }, { "@CurrentSignLevelDeptID_FK", currentSignLevelDeptID }, { "@FinalStatus", status }, { "@remainder", remainder }, { "@Creator_Main", model.Creator }, { "@CreateDate_Main", createDateTime }, { "@DetailSignDocID_FK", model.SignDocID_FK }, { "@ChiefID_FK", chiefID }, { "@Status", status }, { "@Creator_Detail", model.Creator }, { "@CreateDate_Detail", createDateTime }, { "@LogDatetime", DateTime.Now }, }; strSQL = _dc.ConstructInsertDML("signform_log", dic); manipulationConditions.Add(new MultiConditions() { { strSQL, dic } }); // Use Distributed Transaction Coordinator Service // Need Set XACT_ABORT ON manipulationConditions.Add(_handler.GetXACTABORTON()); // 回寫 Trainning System // 需要設定 link server dic = new Conditions() { { "@SIGNDOCID", model.SignDocID_FK }, { "@CLID", model.CLID }, { "@SID", model.ApplyID_FK }, }; strSQL = String.Format( @"UPDATE {0}.{1}.dbo.form_records SET signdocid = @SIGNDOCID, IsSign = 'True' WHERE clid = @CLID AND table_id = '02' AND sid = @SID " , RepositoryFactory.TrainConn["DataSource"], RepositoryFactory.TrainConn["Catalog"]); //@"Select * from soyal.training.dbo.form_records"; manipulationConditions.Add(new MultiConditions() { { strSQL, dic } }); var result = _dc.ExecuteMultAndCheck(manipulationConditions); if (!result) { throw new Exception("發生DML異動0筆資料的異常!"); } }
//新增草稿 public void CreateData(List <RinnaiForms> modelList) { var overtimeList = modelList.Cast <OvertimeViewModel>(); var vmModel = overtimeList.First(); vmModel.SignDocID_FK = GetSeqIDUtils.GetSignDocID(vmModel.Creator, "OT"); if (String.IsNullOrWhiteSpace(vmModel.SignDocID_FK)) { throw new Exception("系統忙碌中請稍候再試"); } //取得簽核人員部門資料 var deptData = _rootRepo.QueryForDepartmentByDeptID(vmModel.SupportDeptID_FK); if (deptData == null) { throw new Exception("查無簽核人員所屬部門資料!"); } //取得當前簽核部門資料,即支援單位主管部門 var upperDeptDate = _pwfRepo.FindUpperDeptData(vmModel.SupportDeptID_FK); var chiefDeptID = upperDeptDate[deptData["ChiefID_FK"].ToString()]; //判斷 FlowRule if (!_pwfRepo.IsFollowFlowRule(vmModel.RuleID_FK, chiefDeptID)) { throw new Exception("不符合簽核規則,無法新增!"); } var status = 1; var procedureData = _rootRepo.QueryForSignProcedureBySignDocID(vmModel.SignDocID_FK); var remainder = procedureData != null?Int32.Parse(procedureData["SignLevel"].ToString()) : -1; var manipulationConditions = new List <MultiConditions>(); var dic = new Conditions() { { "@SignDocID", vmModel.SignDocID_FK }, { "@FormID_FK", vmModel.FormID_FK }, { "@EmployeeID_FK", vmModel.ApplyID_FK }, { "@CurrentSignLevelDeptID_FK", chiefDeptID }, { "@FinalStatus", status }, { "@remainder", remainder }, { "@Creator", vmModel.Creator }, { "@CreateDate", currentDateTime }, }; //產生簽核主表資料 var strSQL = _dc.ConstructInsertDML("signform_main", dic); manipulationConditions.Add(new MultiConditions() { { strSQL, dic } }); //列表細項資料設定 overtimeList.All(overtime => { //根據加班員工ID 設定加班單部門ID var employeeData = _rootRepo.QueryForEmployeeByEmpID(overtime.EmployeeID_FK); overtime.DepartmentID_FK = employeeData != null ? employeeData["DepartmentID_FK"].ToString() : String.Empty; //設定簽核編號 overtime.SignDocID_FK = vmModel.SignDocID_FK; return(true); }); dic = new Conditions() { { "@SignDocID_FK", vmModel.SignDocID_FK }, { "@FormID_FK", vmModel.FormID_FK }, { "@EmployeeID_FK", vmModel.ApplyID_FK }, { "@CurrentSignLevelDeptID_FK", chiefDeptID }, { "@FinalStatus", status }, { "@remainder", remainder }, { "@Creator_Main", vmModel.Creator }, { "@CreateDate_Main", currentDateTime }, { "@LogDatetime", DateTime.Now }, }; //加入 log strSQL = _dc.ConstructInsertDML("signform_log", dic); manipulationConditions.Add(new MultiConditions() { { strSQL, dic } }); int seq = 0; foreach (var overtime in overtimeList) { seq++; dic = new Conditions() { { "@FormID_FK", overtime.FormID_FK }, { String.Format("@SignDocID_FK{0}", seq), overtime.SignDocID_FK }, { "@ApplyID_FK", overtime.ApplyID_FK }, { "@EmployeeID_FK", overtime.EmployeeID_FK }, { "@DepartmentID_FK", overtime.DepartmentID_FK }, { "@StartDateTime", overtime.StartDateTime }, { "@EndDateTime", overtime.EndDateTime }, { "@SupportDeptID_FK", overtime.SupportDeptID_FK }, { "@PayTypeKey", overtime.PayTypeKey }, { "@MealOrderKey", overtime.MealOrderKey }, { "@AutoInsert", false }, { "@Note", overtime.Note }, { "@TotalHours", overtime.TotalHours }, { "@Creator", overtime.Creator }, { "@CreateDate", overtime.CreateDate }, }; //新增 OvertimeForm 資料 strSQL = _dc.ConstructInsertDML("OvertimeForm", dic); manipulationConditions.Add(new MultiConditions() { { String.Format(strSQL, seq), dic } }); } try { if (!_dc.ExecuteMultAndCheck(manipulationConditions)) { throw new Exception("新增加班單失敗!"); } } catch (Exception ex) { throw new Exception("新增加班單失敗!" + ex.Message); } finally { // log each SQL Pair manipulationConditions.ForEach(item => { var sql = item.Single().Key; var valuePair = String.Join("\r\n", item.Single().Value.Select(x => x.Key + " : " + x.Value)); _log.Debug(String.Concat("\r\n", "Create Data Method: \r\n", sql, "\r\n", valuePair, "\r\n")); }); } }