/// <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"));
});
}
}
