/// <summary> /// 스케줄 테이터 Update /// </summary> /// <param name="phone">입력할 테이블이름</param> /// <param name="wageData">입력할 데이터 구조체</param> /// <returns>영양받은 행수</returns> public int Update(string phone, WageData wageData) { int result = -1; string sql = "OnTime = \"" + wageData.OnTime + "\", OffTime = \"" + wageData.OffTime + "\", Time = \"" + wageData.Time + "\", RestTime = \"" + wageData.RestTime + "\", ExtensionTime = \"" + wageData.ExtensionTime + "\", NightTime = \"" + wageData.NightTime + "\", TotalTime = \"" + wageData.TotalTime + "\", Wage = \"" + wageData.Wage + "\", RestWage = \"" + wageData.RestWage + "\", " + "ExtensionWage = \"" + wageData.ExtensionWage + "\", NightWage = \"" + wageData.NightWage + "\", TotalWage = \"" + wageData.TotalWage + "\" where Date = \"" + wageData.Date + "\""; string year = SplitString(wageData.Date, '-')[0]; sqlliteManager.DBOpen(); result = sqlliteManager.Update(phone + year, sql); sqlliteManager.DBClose(); sql = "OnTime = \"" + wageData.OnTime + "\", OffTime = \"" + wageData.OffTime + "\", Time = \"" + wageData.Time + "\", RestTime = \"" + wageData.RestTime + "\", ExtensionTime = \"" + wageData.ExtensionTime + "\", NightTime = \"" + wageData.NightTime + "\", TotalTime = \"" + wageData.TotalTime + "\", Wage = \"" + wageData.Wage + "\", RestWage = \"" + wageData.RestWage + "\", " + "ExtensionWage = \"" + wageData.ExtensionWage + "\", NightWage = \"" + wageData.NightWage + "\", TotalWage = \"" + wageData.TotalWage + "\" where Date = \"" + wageData.Date + "\" and Phone=\"" + phone + "\""; Thread thread = new Thread(() => MainWindow.runThread(2, MySQLData.TableWage, sql)); thread.Start(); return(result); }
public WageData Select(string tableName, string date) { SQLiteCommand command; SQLiteDataReader reader; WageData scheduleData = new WageData(); string sql = "where Date=\"" + date + "\""; string year = SplitString(date, '-')[0]; sqlliteManager.DBOpen(); command = sqlliteManager.Select(tableName + year, sql); reader = command.ExecuteReader(); while (reader.Read()) { scheduleData.Date = reader["Date"].ToString(); scheduleData.OnTime = reader["OnTime"].ToString(); scheduleData.OffTime = reader["OffTime"].ToString(); scheduleData.Time = reader["Time"].ToString(); scheduleData.RestTime = reader["RestTime"].ToString(); scheduleData.ExtensionTime = reader["ExtensionTime"].ToString(); scheduleData.NightTime = reader["NightTime"].ToString(); scheduleData.TotalTime = reader["TotalTime"].ToString(); scheduleData.Wage = reader["Wage"].ToString(); scheduleData.RestWage = reader["RestWage"].ToString(); scheduleData.ExtensionWage = reader["ExtensionWage"].ToString(); scheduleData.NightWage = reader["NightWage"].ToString(); scheduleData.TotalWage = reader["TotalWage"].ToString(); } sqlliteManager.DBClose(); return(scheduleData); }
/// <summary> /// 스케줄 데이터 Delete /// </summary> /// <param name="phone">핸드폰번호</param> /// <param name="WageData">지울 데이터 구조체</param> /// <returns>영양받은 행수</returns> public int Delete(string phone, WageData WageData) { int result = -1; string sql = "where Date=\"" + WageData.Date + "\""; string year = SplitString(WageData.Date, '-')[0]; sqlliteManager.DBOpen(); result = sqlliteManager.Delete(phone + year, sql); sqlliteManager.DBClose(); sql = "where Date=\"" + WageData.Date + "\" and Phone=\"" + phone + "\""; Thread thread = new Thread(() => MainWindow.runThread(3, MySQLData.TableWage, sql)); thread.Start(); return(result); }
/// <summary> /// 스케줄 데이터 Insert /// </summary> /// <param name="phone">핸드폰번호</param> /// <param name="wageData">입력할 데이터 구조체</param> /// <returns>영양받은 행수</returns> public int Insert(string phone, WageData wageData) { int result = -1; string sql = "values(\"" + wageData.Date + "\", \"" + wageData.OnTime + "\", \"" + wageData.OffTime + "\", \"" + wageData.Time + "\", \"" + wageData.RestTime + "\", \"" + wageData.ExtensionTime + "\", \"" + wageData.NightTime + "\", \"" + wageData.TotalTime + "\", \"" + wageData.Wage + "\", \"" + wageData.RestWage + "\", \"" + wageData.ExtensionWage + "\", \"" + wageData.NightWage + "\", \"" + wageData.TotalWage + "\")"; string year = SplitString(wageData.Date, '-')[0]; sqlliteManager.DBOpen(); sqlliteManager.Insert(phone + year, sql); sqlliteManager.DBClose(); sql = "values(\"" + phone + "\", \"" + wageData.Date + "\", \"" + wageData.OnTime + "\", \"" + wageData.OffTime + "\", \"" + wageData.Time + "\", \"" + wageData.RestTime + "\", \"" + wageData.ExtensionTime + "\", \"" + wageData.NightTime + "\", \"" + wageData.TotalTime + "\", \"" + wageData.Wage + "\", \"" + wageData.RestWage + "\", \"" + wageData.ExtensionWage + "\", \"" + wageData.NightWage + "\", \"" + wageData.TotalWage + "\")"; Thread thread = new Thread(() => MainWindow.runThread(4, MySQLData.TableWage, sql)); thread.Start(); return(result); }
/// <summary> /// 데이터베이스에서 데이터 삭제 /// </summary> /// <param name="dataTable">변경된 데이터테이블</param> public void DeleteDataTable(DataTable dataTable) { //전달받은 데이터테이블중 삭제된 행을 새로운 데이터 테이블에 입력 DataTable dtChanges = dataTable.GetChanges(DataRowState.Deleted); string OnTime, OffTime; //테이블의 값이 있는지 확인 if (dtChanges != null) { foreach (DataRow data in dtChanges.Rows) { //변경된 데이터테이블의 값이 조건에 맞는지 확인 if ((!data["OnTime", DataRowVersion.Original].ToString().Contains(":") && data["OnTime", DataRowVersion.Original].ToString().Length != 4) || (!data["OffTime", DataRowVersion.Original].ToString().Contains(":") && data["OffTime", DataRowVersion.Original].ToString().Length != 4) || (data["OnTime", DataRowVersion.Original].ToString().Contains(":") && data["OnTime", DataRowVersion.Original].ToString().Length > 5) || (data["OffTime", DataRowVersion.Original].ToString().Contains(":") && data["OffTime", DataRowVersion.Original].ToString().Length > 5)) { return; } else { string[] swap = SplitString(data["OnTime", DataRowVersion.Original].ToString(), ':'); int Time, Minute; Time = Convert.ToInt32(swap[0]); Minute = Convert.ToInt32(swap[1]); OnTime = Time.ToString().PadLeft(2, '0') + ":" + Minute.ToString().PadLeft(2, '0'); if (Time < 0 || Time > 29 || Minute > 60 || Minute < 0) { return; } swap = SplitString(data["OffTime", DataRowVersion.Original].ToString(), ':'); Time = Convert.ToInt32(swap[0]); Minute = Convert.ToInt32(swap[1]); OffTime = Time.ToString().PadLeft(2, '0') + ":" + Minute.ToString().PadLeft(2, '0'); if (Time < 0 || Time > 29 || Minute > 60 || Minute < 0) { return; } } string phone = ""; //권한이 일반직원인지 확인 if (MemberData.GetMemberData.AuthorityData.Authority == 3) { phone = MemberData.GetMemberData.Phone; } else { int index = TransitionPage.wageManagement.cbName.SelectedIndex; phone = CommData.GetCommData().getLoginDataList()[index].Phone; } WageData scheduleData = new WageData(); scheduleData.OnTime = OnTime; scheduleData.OffTime = OffTime; scheduleData.Date = data["Date", DataRowVersion.Original].ToString(); Delete(phone, scheduleData); WageCalculation(phone, data["Date", DataRowVersion.Original].ToString()); } } }
public void WageCalculation(string phone, string date) { WageData scheduleData = Select(phone, date); int Wage = SelectWage(phone); //출근,퇴근 시간, 분 분리 int OnTime, OnMinute, OffTime, OffMinute; string[] swap = SplitString(scheduleData.OnTime, ':'); OnTime = Convert.ToInt32(swap[0]); OnMinute = Convert.ToInt32(swap[1]); swap = SplitString(scheduleData.OffTime, ':'); OffTime = Convert.ToInt32(swap[0]); OffMinute = Convert.ToInt32(swap[1]); //시간들 계산 int Time, Minute; //24시 이후 퇴근은 24를 더해서 계산 if (OffTime < 5) { OffTime += 24; } //오전 6시 이후 출근 if (OnTime >= 6) { //일반시간 if (OffTime < 22) { Time = OffTime - OnTime; if (OnMinute <= OffMinute) { Minute = OffMinute - OnMinute; } else { Time -= 1; Minute = (OffMinute + 60) - OnMinute; } scheduleData.Time = Time + ":" + Minute.ToString().PadLeft(2, '0'); } //야간시간 else { if (OnTime >= 22) { Time = 0; Minute = 0; OffMinute -= OnMinute; } else { Time = 22 - OnTime; Time -= 1; Minute = 60 - OnMinute; if (Minute >= 60) { Time += 1; Minute -= 60; } } scheduleData.Time = Time + ":" + Minute.ToString().PadLeft(2, '0'); if (OnTime <= 22) { Time = OffTime - 22; } else { Time = OffTime - OnTime; } Minute = OffMinute; scheduleData.NightTime = Time + ":" + Minute.ToString().PadLeft(2, '0'); } } //오전 6시 이전 출근 else { //일반시간 if (OffTime < 22) { Time = OffTime - 6; Minute = OffMinute; scheduleData.Time = Time + ":" + Minute.ToString().PadLeft(2, '0'); } //야간시간 else { Time = (6 - OnTime) + (OffTime - 22); if (OnMinute <= OffMinute) { Minute = OffMinute - OnMinute; } else { Time -= 1; Minute = (OffMinute + 60) - OnMinute; } scheduleData.NightTime = Time + ":" + Minute.ToString().PadLeft(2, '0'); } } //총 시간 if (!scheduleData.Time.Equals("")) { swap = SplitString(scheduleData.Time, ':'); Time = Convert.ToInt32(swap[0]); Minute = Convert.ToInt32(swap[1]); } if (!scheduleData.NightTime.Equals("")) { swap = SplitString(scheduleData.NightTime, ':'); Time += Convert.ToInt32(swap[0]); Minute += Convert.ToInt32(swap[1]); } if (Minute >= 60) { Time += 1; Minute -= 60; } scheduleData.TotalTime = Time + ":" + Minute.ToString().PadLeft(2, '0'); //휴계시간 if ((Time / 4) > 0) { Minute = (Time / 4) * 30; Time = 0; if (Minute >= 60) { Time += 1; Minute -= 60; } scheduleData.RestTime = Time + ":" + Minute.ToString().PadLeft(2, '0'); } //연장시간 swap = SplitString(scheduleData.TotalTime, ':'); Time = Convert.ToInt32(swap[0]); Minute = Convert.ToInt32(swap[1]); if (Time > 8) { Time -= 8; scheduleData.ExtensionTime = Time + ":" + Minute.ToString().PadLeft(2, '0'); //일반시간에서 연장시간 빼기 int Time1, Minute1; swap = SplitString(scheduleData.Time, ':'); Time = Convert.ToInt32(swap[0]); Minute = Convert.ToInt32(swap[1]); swap = SplitString(scheduleData.ExtensionTime, ':'); Time1 = Convert.ToInt32(swap[0]); Minute1 = Convert.ToInt32(swap[1]); Time -= Time1; if (Minute < Minute1) { Time -= 1; Minute += 60; } Minute -= Minute1; scheduleData.Time = Time + ":" + Minute; } //일반시급 if (!scheduleData.Time.Equals("")) { swap = SplitString(scheduleData.Time, ':'); Time = Convert.ToInt32(swap[0]); Minute = Convert.ToInt32(swap[1]); scheduleData.Wage = ((Wage * Time) + (Wage * Minute) / 60).ToString(); } else { scheduleData.Wage = "0"; } //휴계시급 if (!scheduleData.RestTime.Equals("")) { swap = SplitString(scheduleData.RestTime, ':'); Time = Convert.ToInt32(swap[0]); Minute = Convert.ToInt32(swap[1]); scheduleData.RestWage = ((Wage * Time) + (Wage * Minute) / 60).ToString(); } else { scheduleData.RestWage = "0"; } //연장시급 if (!scheduleData.ExtensionTime.Equals("")) { swap = SplitString(scheduleData.ExtensionTime, ':'); Time = Convert.ToInt32(swap[0]); Minute = Convert.ToInt32(swap[1]); scheduleData.ExtensionWage = string.Format("{0:F0}", ((Wage * Time) + (Wage * Minute) / 60) * 1.5); } else { scheduleData.ExtensionWage = "0"; } //야간시급 if (!scheduleData.NightTime.Equals("")) { swap = SplitString(scheduleData.NightTime, ':'); Time = Convert.ToInt32(swap[0]); Minute = Convert.ToInt32(swap[1]); scheduleData.NightWage = string.Format("{0:F0}", ((Wage * Time) + (Wage * Minute) / 60) * 1.5); } else { scheduleData.NightWage = "0"; } //총 시급 scheduleData.TotalWage = (Convert.ToInt32(scheduleData.Wage) + Convert.ToInt32(scheduleData.ExtensionWage) + Convert.ToInt32(scheduleData.NightWage) - Convert.ToInt32(scheduleData.RestWage)).ToString(); Update(phone, scheduleData); }