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