// 受訓單結案時作的事情寫在這 private MultiConditions getTrainDML(string signDocID) { var trainData = _rootRepo.QueryForTrainDataBySignDocID(signDocID); var result = new MultiConditions(); string strSQL = null; string tableName = RepositoryFactory.TrainConn["Catelog"] + "..FORM_SIGN"; Conditions conditions = new Conditions() { { "@SignDocID", signDocID }, { "@Edit_Person", "Portal" } }; if (trainData != null) { // update 簽核狀態 strSQL = String.Format( @"Update {0} Set IsSigned = 'True', Edit_Person=@Edit_Person, Edit_Date=getdate() Where SignDocID = @SignDocID ", tableName); result.Add(strSQL, conditions); } return(result); }
/// <summary> /// 本次簽核結果SQL語句 /// </summary> /// <param name="detailModel"></param> /// <returns></returns> public List <MultiConditions> GetSignOffDML(ProcessWorkflowDetailViewModel detailModel) { var result = new List <MultiConditions>(); detailModel.ModifyDate = DateTime.Now; #region 產生更新簽核明細SQL語句 string signDetailStrSQL = @" Update SignForm_Detail Set Status = @Status, Remark = @Remark, Modifier = @Modifier, ModifyDate = @ModifyDate Where SignDocID_FK = @SignDocID_FK and ChiefID_FK = @ChiefID_FK "; Conditions signDetailConditions = new Conditions() { { "@SignDocID_FK", detailModel.SignDocID_FK }, { "@Status", detailModel.Status }, { "@ChiefID_FK", detailModel.ChiefID_FK }, { "@Remark", detailModel.Remark }, { "@Modifier", detailModel.Modifier }, { "@ModifyDate", detailModel.ModifyDate } }; MultiConditions signDetail = new MultiConditions() { { signDetailStrSQL, signDetailConditions } }; #endregion 產生更新簽核明細SQL語句 result.Add(signDetail); detailModel.Creator = (string)null; detailModel.CreateDate = new DateTime(); #region 產生新增SignLog新增SQL語句 Conditions conditions = new Conditions() { { "@DetailSignDocID_FK", detailModel != null ? detailModel.SignDocID_FK : (string)null }, { "@ChiefID_FK", detailModel != null ? detailModel.ChiefID_FK : (string)null }, { "@Remark", detailModel != null ? detailModel.Remark : (string)null }, { "@Status", detailModel != null?detailModel.Status.ToString() : (string)null }, { "@Creator_Detail", detailModel != null ? detailModel.Creator : (string)null }, { "@CreateDate_Detail", detailModel != null && detailModel.CreateDate != DateTime.MinValue ? detailModel.CreateDate.FormatDatetime() : (string)null }, { "@Modifier_Detail", detailModel != null ? detailModel.Modifier : (string)null }, { "@ModifyDate_Detail", detailModel != null && detailModel.ModifyDate != DateTime.MinValue ? detailModel.ModifyDate.Value.FormatDatetime(): (string)null }, { "@LogDatetime", DateTime.Now }, }; // ConstructInsertDML => 產生新增語句 string logDML = _dc.ConstructInsertDML("SignForm_Log", conditions); #endregion 產生新增SignLog新增SQL語句 result.Add(new MultiConditions() { { logDML, conditions } }); return(result); }
public List <MultiConditions> GetDML() { var result = new List <MultiConditions>(); if (String.IsNullOrWhiteSpace(signDocID)) { throw new Exception("簽核編號為空值!"); } MultiConditions multiDML = null; switch (signDocID.Substring(0, 2).ToUpper()) { case "FP": var forgotpunchList = _rootRepo.QueryForForgotPunchDataBySignDocID(signDocID); forgotpunchList.All(row => { var prototypeModel = WebUtils.ViewModelMapping <ForgotPunchViewModel>(row); var dml = getForgotPunchDML(prototypeModel); result.Add(dml); return(true); }); break; case "OT": var overtimeList = _rootRepo.QueryForOvertimeFormDataBySignDocID(signDocID); // AutoInsert 設定為 True 則自動寫入 SmartMan, 反之不自動寫入 if (overtimeList.Any(form => "False".Equals(form["AutoInsert"].ToString(), StringComparison.OrdinalIgnoreCase))) { return(null); } var sampleRow = overtimeList.First(); var payRangeData = _smartRepo.QueryForPayRange(sampleRow["StartDateTime"].ToDateTimeFormateString("yyyyMMdd"), sampleRow["EndDateTime"].ToDateTimeFormateString("yyyyMMdd")); if (payRangeData == null) { throw new Exception(String.Format("查無{0}薪資歸入月份設定!", sampleRow["StartDateTime"].ToDateTimeFormateString("yyyyMMdd"))); } //表單歸入月份 var payRange = ParsePayRange(payRangeData["PayYYYYMM"].ToString()); overtimeList.All(row => { //該員工該月是否已經結算薪資 if (IsSettledAccounts(row["EmployeeID_FK"].ToString(), payRange)) { //已結算薪資,將此筆歸入下月 payRange = payRange.AddMonths(1); //確認是否有下月薪資歸入月份設定 if (_smartRepo.QueryForPayRange(payRange.ToString("yyyyMMdd"), payRange.ToString("yyyyMMdd")) == null) { throw new Exception(String.Format("查無{0}薪資歸入月份設定!", payRange.ToString("yyyyMMdd"))); } } var prototypeModel = WebUtils.ViewModelMapping <OvertimeViewModel>(row); var dailyonoffData = _smartRepo.QueryForDailyOnOff(prototypeModel.StartDateTime.Value.ToString("yyyyMMdd"), prototypeModel.EmployeeID_FK); prototypeModel.WorkType = dailyonoffData["WorkType"].ToString(); prototypeModel.BeginTime = dailyonoffData["BEGINTIME"].ToString(); prototypeModel.EndTime = dailyonoffData["ENDTIME"].ToString(); //20161101修改取得員工即時人事成本部門 //var employeeData = _rootRepo.QueryForEmployeeByEmpID(prototypeModel.EmployeeID_FK); var employeeData = _smartRepo.QueryCostDept(prototypeModel.EmployeeID_FK); prototypeModel.CostDepartmentID = employeeData["UNITCD"].ToString(); var dml = GetOvertimeDML(prototypeModel, payRange); if (dml == null) { return(true); } //skip result.Add(dml); return(true); }); break; case "TR": //var TrainList = _rootRepo.QueryForTrainDataBySignDocID(signDocID); //TrainList.All(row => //{ // var dml = getTrainDML(); // result.Add(dml); // return true; //}); multiDML = getTrainDML(signDocID); result.Add(multiDML); break; } return(result); }
public MultiConditions GetOvertimeDML(OvertimeViewModel overtime, DateTime payRange) { _rootRepo = RepositoryFactory.CreateRootRepo(); var DeptDataLevel = _rootRepo.QueryForDepartmentByDeptID(overtime.SupportDeptID_FK); var dutyWorkData = _smartRepo.QueryForDutyWork(overtime.StartDateTime.Value.ToDateTimeFormateString("yyyyMMdd"), overtime.EmployeeID_FK); overtime.IsHoliday = "Y".Equals(dutyWorkData["HOLIDAY"].ToString()); var HolidayType = dutyWorkData["H_TYPE"].ToString(); string Level = DeptDataLevel != null ? DeptDataLevel["DepartmentLevel"].ToString() : String.Empty; string TrueSupportDept = ""; if (DeptDataLevel["Virtual"].ToString() == "N") { TrueSupportDept = DeptDataLevel["DepartmentID"].ToString(); } else { string UpDept = ""; for (int i = Int32.Parse(Level) - 1; i >= 1; i--) { if (UpDept == "") { var DeptData = _rootRepo.QueryForDepartmentByDeptID(DeptDataLevel["UpperDepartmentID"].ToString()); if (DeptData["Virtual"].ToString() == "N") { TrueSupportDept = DeptData["DepartmentID"].ToString(); break; } else { UpDept = DeptData["UpperDepartmentID"].ToString(); } } else { var DeptData = _rootRepo.QueryForDepartmentByDeptID(UpDept); if (DeptData["Virtual"].ToString() == "N") { TrueSupportDept = DeptData["DepartmentID"].ToString(); break; } else { UpDept = DeptData["UpperDepartmentID"].ToString(); } } }//迴圈END } string remark = ""; if (overtime.Note.Length > 30) { remark = overtime.Note.Substring(0, 30); } else { remark = overtime.Note; } var result = new MultiConditions(); string strSQL = null; var dateTimeNow = DateTime.Now; Conditions conditions = new Conditions() { { "@SYSTEMDATE", dateTimeNow.ToString("yyyyMMdd") }, { "@SYSTEMTIME", dateTimeNow.ToString("HHmm") }, { "@LOGINUSER", "System" }, { "@APPROVEUSER", "System" }, { "@APPROVEYN", "Y" }, { "@COMPANYCD", "A" }, { "@EMPLOYECD", overtime.EmployeeID_FK }, { "@PAYYYYYMM", payRange.ToString("yyyyMM") }, { "@DUTYDATE", overtime.StartDateTime.Value.ToString("yyyyMMdd") }, { "@BEGINTIME", overtime.BeginTime }, { "@ENDTIME", overtime.EndTime }, { "@OVERONWORK", overtime.StartDateTime.Value.ToString("HHmm") }, { "@OVEROFFWORK", overtime.EndDateTime.Value.ToString("HHmm") }, { "@OVERWORKHOURS", overtime.TotalHours }, //20170109 加班說明寫入志元只可30個字 //{ "@REMARK", overtime.Note.Substring(0,30)}, { "@REMARK", remark }, //20161104 修正單號紀錄 //{ "@SEQNOX", String.Concat("OT",dateTimeNow.ToString("yyyyMMdd"), dateTimeNow.ToString("HHmmss"))}, { "@SEQNOX", overtime.SignDocID_FK }, //{ "@HOLIDAY", overtime.IsHoliday }, { "@HOLIDAY", overtime.IsHoliday ? "Y" : "N" }, //20161028 修正寫入志元table部門 //{ "@UNITCD", overtime.DepartmentID_FK}, { "@UNITCD", overtime.CostDepartmentID }, { "@COSTCD", TrueSupportDept }, { "@WORKTYPE", overtime.WorkType }, { "@H_TYPE", HolidayType }, }; string tableName = RepositoryFactory.SmartManConn["DataSource"] + "." + RepositoryFactory.SmartManConn["Catelog"] + ".dbo.DailyOn"; setConditions(overtime, payRange, ref conditions); var dailyOnData = QueryForOvertimeRecord(overtime, tableName); if (dailyOnData == null) { // insert strSQL = _dc.ConstructInsertDML(tableName, conditions); } else { // update var distinglishType = Double.Parse(dailyOnData["ADDHOURS"].ToString()) > 0 ? "OVERTIMELEAVE" : "OVERTIMEPAY"; var overonworkArray = new string[] { overtime.StartDateTime.Value.ToString("HHmm"), (string)dailyOnData["OVERONWORK"] }; var overoffworkArray = new string[] { overtime.EndDateTime.Value.ToString("HHmm"), (string)dailyOnData["OVEROFFWORK"] }; //替換加班申請單 開始與結束的時間 conditions["@OVERONWORK"] = overonworkArray.Min(); conditions["@OVEROFFWORK"] = overoffworkArray.Max(); switch (overtime.PayTypeKey.ToUpper() + distinglishType) { case "OVERTIMEPAYOVERTIMEPAY": strSQL = String.Format( @"Update {0} Set SYSTEMDATE = @SYSTEMDATE, SYSTEMTIME = @SYSTEMTIME, OVERONWORK = @OVERONWORK, OVEROFFWORK = @OVEROFFWORK, OVERWORKone = OVERWORKone + @OVERWORKone, OVERWORKtwo = OVERWORKtwo + @OVERWORKtwo, OVERWORKfour = OVERWORKfour + @OVERWORKfour, OVERWORKHOURS = OVERWORKHOURS + @OVERWORKHOURS, REMARK = REMARK +@REMARK Where EMPLOYECD = @EMPLOYECD and DUTYDATE=@DUTYDATE", tableName); break; case "OVERTIMELEAVEOVERTIMELEAVE": strSQL = String.Format( @"Update {0} Set SYSTEMDATE = @SYSTEMDATE, SYSTEMTIME = @SYSTEMTIME, OVERONWORK = @OVERONWORK, OVEROFFWORK = @OVEROFFWORK, OVERWORKHOURS = OVERWORKHOURS + @OVERWORKHOURS, ADDHOURS = ADDHOURS + @ADDHOURS, REMARK = REMARK +@REMARK Where EMPLOYECD = @EMPLOYECD and DUTYDATE=@DUTYDATE ", tableName); break; default: throw new Exception("報酬型別選擇錯誤!"); } } if (conditions.ContainsKey("@OVERWORKone"))//1.34 { strSQL = strSQL.Replace("OVERWORKone", "OVERWORK1"); conditions.Add("@OVERWORK1", conditions["@OVERWORKone"]); conditions.Remove("@OVERWORKone"); } else { strSQL = Regex.Replace(strSQL, @"(?m)^OVERWORKone.*?,", ""); } if (conditions.ContainsKey("@OVERWORKtwo"))//1.67 { strSQL = strSQL.Replace("OVERWORKtwo", "OVERWORK2"); conditions.Add("@OVERWORK2", conditions["@OVERWORKtwo"]); conditions.Remove("@OVERWORKtwo"); } else { strSQL = Regex.Replace(strSQL, @"(?m)^OVERWORKtwo.*?,", ""); } if (conditions.ContainsKey("@OVERWORKthree"))//2.67 { strSQL = strSQL.Replace("OVERWORKthree", "OVERWORK3"); conditions.Add("@OVERWORK3", conditions["@OVERWORKthree"]); conditions.Remove("@OVERWORKthree"); } else { strSQL = Regex.Replace(strSQL, @"(?m)^OVERWORKthree.*?,", ""); } if (conditions.ContainsKey("@OVERWORKfour")) { strSQL = strSQL.Replace("OVERWORKfour", "OVERWORK4"); conditions.Add("@OVERWORK4", conditions["@OVERWORKfour"]); conditions.Remove("@OVERWORKfour"); } else { strSQL = Regex.Replace(strSQL, @"(?m)^OVERWORKfour.*?,", ""); } result.Add(strSQL, conditions); return(result); }
private MultiConditions getForgotPunchDML(ForgotPunchViewModel forgotPunch) { if (forgotPunch == null || !forgotPunch.AutoInsert) { throw new ArgumentNullException("忘刷資料為空!", new Exception("自動寫入值異常!")); } var result = new MultiConditions(); string tableName = RepositoryFactory.SmartManConn["DataSource"] + "." + RepositoryFactory.SmartManConn["Catelog"] + ".dbo.LostCard"; string strSQL = null; Conditions conditions = new Conditions() { { "@CARDNO", forgotPunch.EmployeeID_FK }, //20161104 志元改版,LostCard無EMPLOYECD欄位,新增Sequenceid紀錄電子簽核單號 //{ "@EMPLOYECD", forgotPunch.EmployeeID_FK}, { "@Sequenceid", forgotPunch.SignDocID_FK }, //20161114 不特地寫入Flag狀態,志元預設為0,才會自動比對刷卡資料 //20170411 因以直接寫入刷卡資料,故直接給1,至元不比對刷卡資料 //{ "@FLAG", "1"}, }; switch (forgotPunch.PeriodType) { // 上班 case 1: conditions.Add("@CARDDATE", forgotPunch.ForgotPunchInDateTime.HasValue ? forgotPunch.ForgotPunchInDateTime.Value.ToString("yyyyMMdd") : (string)null); conditions.Add("@CARDTIME", forgotPunch.ForgotPunchInDateTime.HasValue ? forgotPunch.ForgotPunchInDateTime.Value.ToString("HHmm") : (string)null); conditions.Add("@CARDTYPE", "01"); strSQL = _dc.ConstructInsertDML(tableName, conditions); result.Add(strSQL, conditions); break; // 下班 case 2: conditions.Add("@CARDDATE", forgotPunch.ForgotPunchOutDateTime.HasValue ? forgotPunch.ForgotPunchOutDateTime.Value.ToString("yyyyMMdd") : (string)null); conditions.Add("@CARDTIME", forgotPunch.ForgotPunchOutDateTime.HasValue ? forgotPunch.ForgotPunchOutDateTime.Value.ToString("HHmm") : (string)null); conditions.Add("@CARDTYPE", "02"); strSQL = _dc.ConstructInsertDML(tableName, conditions); result.Add(strSQL, conditions); break; // 全天 case 3: for (int i = 1; i < 3; i++) { conditions = new Conditions() { { "@CARDNO", forgotPunch.EmployeeID_FK }, //20161104 志元改版,LostCard無EMPLOYECD欄位,新增Sequenceid紀錄電子簽核單號 //{ "@EMPLOYECD", forgotPunch.EmployeeID_FK}, { "@Sequenceid", forgotPunch.SignDocID_FK }, //{ "@FLAG", "1"}, { String.Format("@CARDTYPE{0}", i), "0" + i.ToString() } }; if (i == 1) { conditions.Add("@CARDDATE", forgotPunch.ForgotPunchInDateTime.HasValue ? forgotPunch.ForgotPunchInDateTime.Value.ToString("yyyyMMdd") : (string)null); conditions.Add("@CARDTIME", forgotPunch.ForgotPunchInDateTime.HasValue ? forgotPunch.ForgotPunchInDateTime.Value.ToString("HHmm") : (string)null); } else { conditions.Add("@CARDDATE", forgotPunch.ForgotPunchOutDateTime.HasValue ? forgotPunch.ForgotPunchOutDateTime.Value.ToString("yyyyMMdd") : (string)null); conditions.Add("@CARDTIME", forgotPunch.ForgotPunchOutDateTime.HasValue ? forgotPunch.ForgotPunchOutDateTime.Value.ToString("HHmm") : (string)null); } strSQL = _dc.ConstructInsertDML(tableName, conditions); result.Add(strSQL, conditions); } break; default: throw new Exception("無此忘刷型別!"); } //----------start---------- var ForgotDate = new DateTime(); if (forgotPunch.ForgotPunchInDateTime != null) { ForgotDate = forgotPunch.ForgotPunchInDateTime.Value; } else if (forgotPunch.ForgotPunchOutDateTime != null) { ForgotDate = forgotPunch.ForgotPunchOutDateTime.Value; } //抓班表資料 var dutyWorkData = _smartRepo.QueryForDutyWork(ForgotDate.ToDateTimeFormateString("yyyyMMdd"), forgotPunch.EmployeeID_FK); //抓員工刷卡資料 var EmpdailyOnOff = _smartRepo.QueryForDailyOnOff(ForgotDate.ToString("yyyyMMdd"), forgotPunch.EmployeeID_FK); //20170302 add新增dailyOnOff時寫入計薪年月 var payRange = _smartRepo.QueryForPayRange(ForgotDate.ToDateTimeFormateString("yyyyMMdd"), ForgotDate.ToDateTimeFormateString("yyyyMMdd")); //var payYYYYMM = ParsePayRange(payRange["PayYYYYMM"].ToString()); var result_dailyOnOff = new MultiConditions(); string strSQL_dailyOnOff = null; var dateTimeNow = DateTime.Now; Conditions conditions_dailyOnOff = new Conditions() { { "@SYSTEMDATE", dateTimeNow.ToString("yyyyMMdd") }, { "@SYSTEMTIME", dateTimeNow.ToString("HHmm") }, { "@COMPANYCD", "A" }, { "@EMPLOYECD", forgotPunch.EmployeeID_FK }, { "@LOGINUSER", "System" }, { "@APPROVEUSER", "System" }, { "@APPROVEYN", "Y" }, { "@DUTYDATE", ForgotDate.ToDateTimeFormateString("yyyyMMdd") }, //{ "@BEGINTIME", "Y"}, //{ "@ENDTIME", "Y"}, { "@SEQNOX", forgotPunch.SignDocID_FK }, { "@PAYYYYYMM", payRange["PayYYYYMM"].ToString() }, { "@WORKTYPE", dutyWorkData["WORKTYPE"].ToString() }, { "@HOLIDAY", dutyWorkData["HOLIDAY"].ToString() }, { "@H_TYPE", dutyWorkData["H_TYPE"] }, { "@overwork1", 0 }, { "@overwork2", 0 }, { "@overwork3", 0 }, { "@overwork4", 0 }, { "@overwork5", 0 }, { "@recreatedays", 0 }, { "@workhours", 0 }, { "@workdays", 0 }, { "@offwork1", 0 }, { "@offwork2", 0 }, { "@offwork3", 0 }, { "@offwork4", 0 }, { "@offwork5", 0 }, { "@offwork6", 0 }, { "@offwork7", 0 }, { "@offwork8", 0 }, { "@offwork9", 0 }, { "@offwork10", 0 }, { "@offwork5m", 0 }, { "@offwork6m", 0 }, { "@offhours", 0 }, { "@addhours", 0 }, { "@overworkhours", 0 }, { "@mealdelay", 0 }, { "@offwork11", 0 }, { "@offwork12", 0 }, { "@offworkhours", 0 }, { "@taxhours", 0 }, //{ "@losttimes", 0}, { "@FixedPay18", 0 }, { "@FixedPay19", 0 }, { "@FixedPay20", 0 }, { "@FixedPay21", 0 }, { "@FixedPay22", 0 }, { "@RealOnDate", ForgotDate.ToDateTimeFormateString("yyyyMMdd") }, { "@RealOffDate", ForgotDate.ToDateTimeFormateString("yyyyMMdd") }, { "@OffWork14", 0 }, { "@OffWork15", 0 }, }; string tableName_dailyOnOff = RepositoryFactory.SmartManConn["DataSource"] + "." + RepositoryFactory.SmartManConn["Catelog"] + ".dbo.DailyOnOff"; if (dutyWorkData["HOLIDAY"].ToString() == "Y") { //假日忘刷 if (EmpdailyOnOff == null) { // insert dailyOnOff conditions_dailyOnOff.Add("@BEGINTIME", forgotPunch.ForgotPunchInDateTime.HasValue ? forgotPunch.ForgotPunchInDateTime.Value.ToString("HHmm") : (string)null); conditions_dailyOnOff.Add("@ENDTIME", forgotPunch.ForgotPunchOutDateTime.HasValue ? forgotPunch.ForgotPunchOutDateTime.Value.ToString("HHmm") : (string)null); conditions_dailyOnOff.Add("@losttimes", 0); strSQL_dailyOnOff = String.Format(@"INSERT INTO {0} (SYSTEMDATE, SYSTEMTIME, COMPANYCD, EMPLOYECD, LOGINUSER, APPROVEUSER, APPROVEYN, DUTYDATE, SEQNOX, PAYYYYYMM, WORKTYPE, HOLIDAY, H_TYPE, overwork1, overwork2, overwork3, overwork4, overwork5, recreatedays, workhours, workdays, offwork1, offwork2, offwork3, offwork4, offwork5, offwork6, offwork7, offwork8, offwork9, offwork10, offwork5m, offwork6m, offhours, addhours, overworkhours, mealdelay, offwork11, offwork12, offworkhours, taxhours, FixedPay18, FixedPay19, FixedPay20, FixedPay21, FixedPay22, RealOnDate, RealOffDate, OffWork14, OffWork15, BEGINTIME, ENDTIME, losttimes) VALUES (@SYSTEMDATE, @SYSTEMTIME, @COMPANYCD, @EMPLOYECD, @LOGINUSER, @APPROVEUSER, @APPROVEYN, @DUTYDATE, @SEQNOX, @PAYYYYYMM, @WORKTYPE, @HOLIDAY, @H_TYPE, @overwork1, @overwork2, @overwork3, @overwork4, @overwork5, @recreatedays, @workhours, @workdays, @offwork1, @offwork2, @offwork3, @offwork4, @offwork5, @offwork6, @offwork7, @offwork8, @offwork9, @offwork10, @offwork5m, @offwork6m, @offhours, @addhours, @overworkhours, @mealdelay, @offwork11, @offwork12, @offworkhours, @taxhours, @FixedPay18, @FixedPay19, @FixedPay20, @FixedPay21, @FixedPay22, @RealOnDate, @RealOffDate, @OffWork14, @OffWork15, @BEGINTIME, @ENDTIME, @losttimes)", tableName_dailyOnOff); //strSQL_dailyOnOff = _dc.ConstructInsertDML(tableName_dailyOnOff, conditions_dailyOnOff); result.Add(strSQL_dailyOnOff, conditions_dailyOnOff); } else { //update dailyOnOff switch (forgotPunch.PeriodType) { // 上班 case 1: if (EmpdailyOnOff["BEGINTIME"].ToString() == "") { strSQL_dailyOnOff = String.Format( @"Update {0} Set Begintime = '{1}', LOSTTIMES = 0, SEQNOX = '{2}' Where employecd = @employecd and dutydate=@dutydate", tableName_dailyOnOff, forgotPunch.ForgotPunchInDateTime.HasValue ? forgotPunch.ForgotPunchInDateTime.Value.ToString("HHmm") : (string)null, forgotPunch.SignDocID_FK); result.Add(strSQL_dailyOnOff, conditions_dailyOnOff); } //20170411 增加判斷事後補單(HR已先手動作業),只更新單號 else if ((EmpdailyOnOff["BEGINTIME"].ToString() != "") && (EmpdailyOnOff["ENDTIME"].ToString() != "")) { strSQL_dailyOnOff = String.Format( @"Update {0} Set LOSTTIMES= ( CASE WORKTYPE WHEN 'A' THEN 0 ELSE LOSTTIMES END) , REMARKOFF='', OFFWORK3=0, SEQNOX ='{1}' Where employecd = @employecd and dutydate=@dutydate", tableName_dailyOnOff, forgotPunch.SignDocID_FK); result.Add(strSQL_dailyOnOff, conditions_dailyOnOff); } else { strSQL_dailyOnOff = String.Format( @"Update {0} Set Begintime = '{1}', ENDTIME = '{2}', LOSTTIMES = 0, SEQNOX = '{3}' Where employecd = @employecd and dutydate=@dutydate", tableName_dailyOnOff, forgotPunch.ForgotPunchInDateTime.HasValue ? forgotPunch.ForgotPunchInDateTime.Value.ToString("HHmm") : (string)null, EmpdailyOnOff["BEGINTIME"].ToString(), forgotPunch.SignDocID_FK); result.Add(strSQL_dailyOnOff, conditions_dailyOnOff); //conditions.Add("@CARDTIME", forgotPunch.ForgotPunchInDateTime.HasValue ? forgotPunch.ForgotPunchInDateTime.Value.ToString("HHmm") : (string)null); } break; // 下班 case 2: strSQL_dailyOnOff = String.Format( @"Update {0} Set ENDTIME = '{1}', LOSTTIMES = 0, SEQNOX = '{2}' Where employecd = @employecd and dutydate=@dutydate", tableName_dailyOnOff, forgotPunch.ForgotPunchOutDateTime.HasValue ? forgotPunch.ForgotPunchOutDateTime.Value.ToString("HHmm") : (string)null, forgotPunch.SignDocID_FK); result.Add(strSQL_dailyOnOff, conditions_dailyOnOff); break; // 全天 case 3: strSQL_dailyOnOff = String.Format( @"Update {0} Set Begintime = '{1}', ENDTIME = '{2}', LOSTTIMES = 0, SEQNOX = '{3}' Where employecd = @employecd and dutydate=@dutydate", tableName_dailyOnOff, forgotPunch.ForgotPunchInDateTime.HasValue ? forgotPunch.ForgotPunchInDateTime.Value.ToString("HHmm") : (string)null, forgotPunch.ForgotPunchOutDateTime.HasValue ? forgotPunch.ForgotPunchOutDateTime.Value.ToString("HHmm") : (string)null, forgotPunch.SignDocID_FK); result.Add(strSQL_dailyOnOff, conditions_dailyOnOff); break; default: throw new Exception("無此忘刷型別!"); } } } else { //上班日忘刷 switch (forgotPunch.PeriodType) { // 上班 case 1: if ((EmpdailyOnOff["BEGINTIME"].ToString() == "")) { strSQL_dailyOnOff = String.Format( @"Update {0} Set Begintime = '{1}', LOSTTIMES= ( CASE WORKTYPE WHEN 'A' THEN 0 ELSE LOSTTIMES END) , REMARKOFF='', OFFWORK3=0, SEQNOX ='{2}' Where employecd = @employecd and dutydate=@dutydate", tableName_dailyOnOff, forgotPunch.ForgotPunchInDateTime.HasValue ? forgotPunch.ForgotPunchInDateTime.Value.ToString("HHmm") : (string)null, forgotPunch.SignDocID_FK); result.Add(strSQL_dailyOnOff, conditions_dailyOnOff); } //20170411 增加判斷事後補單(HR已先手動作業),只更新單號 else if ((EmpdailyOnOff["BEGINTIME"].ToString() != "") && (EmpdailyOnOff["ENDTIME"].ToString() != "")) { strSQL_dailyOnOff = String.Format( @"Update {0} Set LOSTTIMES= ( CASE WORKTYPE WHEN 'A' THEN 0 ELSE LOSTTIMES END) , REMARKOFF='', OFFWORK3=0, SEQNOX ='{1}' Where employecd = @employecd and dutydate=@dutydate", tableName_dailyOnOff, forgotPunch.SignDocID_FK); result.Add(strSQL_dailyOnOff, conditions_dailyOnOff); } else { strSQL_dailyOnOff = String.Format( @"Update {0} Set Begintime = '{1}', ENDTIME = '{2}', LOSTTIMES= ( CASE WORKTYPE WHEN 'A' THEN 0 ELSE LOSTTIMES END) , REMARKOFF='', OFFWORK3=0, SEQNOX = '{3}' Where employecd = @employecd and dutydate=@dutydate", tableName_dailyOnOff, forgotPunch.ForgotPunchInDateTime.HasValue ? forgotPunch.ForgotPunchInDateTime.Value.ToString("HHmm") : (string)null, EmpdailyOnOff["BEGINTIME"].ToString(), forgotPunch.SignDocID_FK); result.Add(strSQL_dailyOnOff, conditions_dailyOnOff); //conditions.Add("@CARDTIME", forgotPunch.ForgotPunchInDateTime.HasValue ? forgotPunch.ForgotPunchInDateTime.Value.ToString("HHmm") : (string)null); } break; // 下班 case 2: strSQL_dailyOnOff = String.Format( @"Update {0} Set ENDTIME = '{1}', LOSTTIMES= ( CASE WORKTYPE WHEN 'A' THEN 0 ELSE LOSTTIMES END) , REMARKOFF='', OFFWORK3=0, SEQNOX = '{2}' Where employecd = @employecd and dutydate=@dutydate", tableName_dailyOnOff, forgotPunch.ForgotPunchOutDateTime.HasValue ? forgotPunch.ForgotPunchOutDateTime.Value.ToString("HHmm") : (string)null, forgotPunch.SignDocID_FK); result.Add(strSQL_dailyOnOff, conditions_dailyOnOff); break; // 全天 case 3: strSQL_dailyOnOff = String.Format( @"Update {0} Set Begintime = '{1}', ENDTIME = '{2}', LOSTTIMES= ( CASE WORKTYPE WHEN 'A' THEN 0 ELSE LOSTTIMES END) , REMARKOFF='', OFFWORK3=0, SEQNOX = '{3}' Where employecd = @employecd and dutydate=@dutydate", tableName_dailyOnOff, forgotPunch.ForgotPunchInDateTime.HasValue ? forgotPunch.ForgotPunchInDateTime.Value.ToString("HHmm") : (string)null, forgotPunch.ForgotPunchOutDateTime.HasValue ? forgotPunch.ForgotPunchOutDateTime.Value.ToString("HHmm") : (string)null, forgotPunch.SignDocID_FK); result.Add(strSQL_dailyOnOff, conditions_dailyOnOff); break; default: throw new Exception("無此忘刷型別!"); } } //---------end--------- return(result); }