///------------------------------------------------------------------
/// <summary>
/// 勤務時間取得 </summary>
/// <param name="dt">
/// 日付</param>
/// <param name="sNum">
/// 個人コード</param>
/// <returns>
/// 休日勤務時間・分</returns>
///------------------------------------------------------------------
private static int getHolWorkTime(mailReceiveDataSet dts, DateTime dt, int sNum)
{
if (!dts.T_勤怠.Any(a => a.日付 == dt && a.社員ID == sNum))
{
return(0);
}
var s = dts.T_勤怠.Single(a => a.日付 == dt && a.社員ID == sNum);
// 勤務時間を取得する
double spanMin = Utility.getWorkTime(s);
return((int)spanMin);
}
///----------------------------------------------------------------------------
/// <summary>
/// 対象年月の休日代休時間、法定休日代休時間取得 【工事指定】</summary>
/// <param name="dts">
/// ryowaDataSet</param>
/// <param name="holDTM">
/// 休日代休時間</param>
/// <param name="houteiDTM">
/// 法定休日代休時間</param>
/// <param name="sYY">
/// 対象年</param>
/// <param name="sMM">
/// 対象月</param>
/// <param name="sNum">
/// 個人コード</param>
/// <param name="pID">
/// 工事コード</param>
///----------------------------------------------------------------------------
public static void getdaikyuTime(mailReceiveDataSet dts, out int holDTM, out int houteiDTM, int sYY, int sMM, int sNum, int pID)
{
holDTM = 0;
houteiDTM = 0;
var s = dts.T_勤怠.Where(a => a.日付.Year == sYY && a.日付.Month == sMM &&
a.社員ID == sNum && a.代休対象日 != string.Empty);
if (s.Count() == 0)
{
return;
}
foreach (var t in s)
{
DateTime dt;
if (DateTime.TryParse(t.代休対象日.Substring(0, 4) + "/" + t.代休対象日.Substring(4, 2) + "/" + t.代休対象日.Substring(6, 2), out dt))
{
// 該当する工事の休日勤務か？
if (dts.T_勤怠.Any(a => a.社員ID == sNum && a.日付 == dt && a.工事ID == pID))
{
// 休日か？
if (dts.M_休日.Any(a => a.日付 == dt))
{
// 休日勤務時間を取得
int wTM = getHolWorkTime(dts, dt, sNum);
if (wTM > Properties.Settings.Default.workTime)
{
wTM = Properties.Settings.Default.workTime;
}
var d = dts.M_休日.Single(a => a.日付 == dt);
if (d.法定休日 == global.flgOn)
{
// 法定休日代休のとき
houteiDTM += wTM;
}
else
{
// 休日代休のとき
holDTM += wTM;
}
}
}
}
}
}
///------------------------------------------------------------------
/// <summary>
/// 出勤簿・車両走行報告書作成 </summary>
///------------------------------------------------------------------
private string sReport(int pYY, int pMM, int pNum)
{
string xlsName = string.Empty;
mailReceiveDataSet dts = new mailReceiveDataSet();
mailReceiveDataSetTableAdapters.T_勤怠TableAdapter adp = new mailReceiveDataSetTableAdapters.T_勤怠TableAdapter();
mailReceiveDataSetTableAdapters.M_社員TableAdapter sAdp = new mailReceiveDataSetTableAdapters.M_社員TableAdapter();
mailReceiveDataSetTableAdapters.M_休日TableAdapter hAdp = new mailReceiveDataSetTableAdapters.M_休日TableAdapter();
mailReceiveDataSetTableAdapters.M_工事TableAdapter pAdp = new mailReceiveDataSetTableAdapters.M_工事TableAdapter();
// 勤怠テーブル読み込み
//adp.Fill(dts.T_勤怠); // 該当社員に絞り込み可能 2018/12/05
adp.FillBySCode(dts.T_勤怠, pNum); // 該当社員に絞り込み 2018/12/14
//sAdp.Fill(dts.M_社員); // 2018/12/15 コメント化
hAdp.Fill(dts.M_休日);
pAdp.Fill(dts.M_工事);
// 該当勤怠データがないときは終わる
if (dts.T_勤怠.Count(a => a.日付.Year == pYY && a.日付.Month == pMM && a.社員ID == pNum) == 0)
{
return(string.Empty);
}
//エクセルファイル日付明細開始行
const int S_GYO = 5;
const int S_GYO2 = 38;
int eRow = 0;
try
{
string sAppPath = System.AppDomain.CurrentDomain.BaseDirectory;
Excel.Application oXls = new Excel.Application();
// 勤務報告書テンプレートシート
Excel.Workbook oXlsBook = (Excel.Workbook)(oXls.Workbooks.Open(Properties.Settings.Default.sxlsPath,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing));
Excel.Worksheet oxlsPrintSheet = null; // 印刷用ワークシート
Excel.Range[] rng = new Microsoft.Office.Interop.Excel.Range[2];
string Category = string.Empty;
try
{
// カレントのシートを設定
oxlsPrintSheet = (Excel.Worksheet)oXlsBook.Sheets[1];
// 年月
//oxlsPrintSheet.Cells[2, 5] = Properties.Settings.Default.gengou; // 2018/07/18 コメント化
//oxlsPrintSheet.Cells[2, 6] = (pYY - Properties.Settings.Default.rekiHosei).ToString();
oxlsPrintSheet.Cells[2, 6] = pYY.ToString(); // 和暦から西暦へ
oxlsPrintSheet.Cells[2, 7] = "年";
oxlsPrintSheet.Cells[2, 8] = pMM.ToString();
oxlsPrintSheet.Cells[2, 9] = "月分";
// 個人コード
oxlsPrintSheet.Cells[3, 4] = pNum.ToString();
// 該当月を取得
DateTime dt = DateTime.Parse(pYY.ToString() + "/" + pMM.ToString() + "/01").AddMonths(1).AddDays(-1);
for (int i = 0; i < dt.Day; i++)
{
DateTime sDt = DateTime.Parse(pYY.ToString() + "/" + pMM.ToString() + "/" + (i + 1).ToString());
// 勤怠登録されている日付を対象とする
if (!dts.T_勤怠.Any(a => a.日付 == sDt && a.社員ID == pNum))
{
continue;
}
// 勤怠データを取得
var t = dts.T_勤怠.Single(a => a.日付 == sDt && a.社員ID == pNum);
// 氏名
if (t.M_社員Row != null)
{
oxlsPrintSheet.Cells[2, 2] = t.M_社員Row.氏名;
}
else
{
oxlsPrintSheet.Cells[2, 2] = "";
}
// 印字行
eRow = S_GYO + i;
oxlsPrintSheet.Cells[eRow, 1] = (i + 1).ToString(); // 日付
// 休日か？
if (t.M_休日Row != null)
{
// 網掛け
rng[0] = (Excel.Range)oxlsPrintSheet.Cells[eRow, 1];
rng[1] = (Excel.Range)oxlsPrintSheet.Cells[eRow, 1];
oxlsPrintSheet.get_Range(rng[0], rng[1]).Interior.Color = System.Drawing.Color.LightGray;
}
// 工事名
string pName = "";
if (t.M_工事Row != null)
{
pName = t.M_工事Row.称;
}
oxlsPrintSheet.Cells[eRow, 2] = pName;
if (t.出勤印 == global.flgOn)
{
oxlsPrintSheet.Cells[eRow, 3] = "出勤";
}
else if (t.休日出勤 == global.flgOn)
{
oxlsPrintSheet.Cells[eRow, 3] = "休出";
}
else if (t.代休 == global.flgOn)
{
oxlsPrintSheet.Cells[eRow, 3] = "代休";
}
else if (!t.Is休日Null() && t.休日 == global.flgOn)
{
oxlsPrintSheet.Cells[eRow, 3] = "休日";
}
else if (!t.Is欠勤Null() && t.勤 == global.flgOn)
{
oxlsPrintSheet.Cells[eRow, 3] = pName + "休み";
}
if (t.出勤印 == global.flgOn || t.休日出勤 == global.flgOn)
{
oxlsPrintSheet.Cells[eRow, 4] = t.出社時刻時 + ":" + t.出社時刻分.PadLeft(2, '0');
oxlsPrintSheet.Cells[eRow, 5] = t.開始時刻時 + ":" + t.開始時刻分.PadLeft(2, '0');
oxlsPrintSheet.Cells[eRow, 6] = t.終了時刻時 + ":" + t.終了時刻分.PadLeft(2, '0');
oxlsPrintSheet.Cells[eRow, 7] = t.退出時刻時 + ":" + t.退出時刻分.PadLeft(2, '0');
if (t.休憩 > 0)
{
oxlsPrintSheet.Cells[eRow, 8] = Utility.intToHhMM(t.休憩);
}
else
{
oxlsPrintSheet.Cells[eRow, 8] = string.Empty;
}
// 2018/07/18 コメント化
//if (t.普通残業 > 0)
//{
// oxlsPrintSheet.Cells[eRow, 9] = Utility.intToHhMM(t.普通残業);
//}
//else
//{
// oxlsPrintSheet.Cells[eRow, 9] = string.Empty;
//}
// 早出残業＋普通残業 2018/07/18
int zanTotal = hayadeZanTotal(Utility.nulltoStr(t.早出残業), Utility.nulltoStr(t.普通残業));
if (zanTotal > 0)
{
oxlsPrintSheet.Cells[eRow, 9] = Utility.intToHhMM(zanTotal);
}
else
{
oxlsPrintSheet.Cells[eRow, 9] = string.Empty;
}
if (t.深夜残業 > 0)
{
oxlsPrintSheet.Cells[eRow, 10] = Utility.intToHhMM(t.深夜残業);
}
else
{
oxlsPrintSheet.Cells[eRow, 10] = string.Empty;
}
}
if (t.宿泊 == global.flgOn)
{
oxlsPrintSheet.Cells[eRow, 11] = "◯";
}
else
{
oxlsPrintSheet.Cells[eRow, 11] = "";
}
if (!t.Is備考Null())
{
oxlsPrintSheet.Cells[eRow, 12] = t.備考;
}
else
{
oxlsPrintSheet.Cells[eRow, 12] = string.Empty;
}
oxlsPrintSheet.Cells[eRow, 14] = getKmAll(dts, pNum, sDt); // 2018/12/15
oxlsPrintSheet.Cells[eRow, 15] = t.通勤業務走行.ToString();
oxlsPrintSheet.Cells[eRow, 16] = t.私用走行.ToString();
// 確認印
if (t.確認印 != global.flgOff)
{
//oxlsPrintSheet.Cells[eRow, 17] = "✔";
// 2018/12/15 以下、コメント化
//// 確認欄に社員名を表示 : 2016/04/08
//if (dts.M_社員.Any(a => a.ID == t.確認印))
//{
// var s = dts.M_社員.Single(a => a.ID == t.確認印);
// oxlsPrintSheet.Cells[eRow, 17] = s.氏名;
//}
//else
//{
// oxlsPrintSheet.Cells[eRow, 17] = "";
//}
// 確認欄に社員名を表示 : 2016/04/08
// FillBySCodeに変更：2018/12/15
if (sAdp.FillBySCode(dts.M_社員, t.確認印) > 0)
{
var s = dts.M_社員.Single(a => a.ID == t.確認印);
oxlsPrintSheet.Cells[eRow, 17] = s.氏名;
}
else
{
oxlsPrintSheet.Cells[eRow, 17] = "";
}
}
else
{
oxlsPrintSheet.Cells[eRow, 17] = "";
}
}
// 工事別集計欄
var ss = dts.T_勤怠
.Where(a => a.社員ID == pNum && a.日付.Year == pYY && a.日付.Month == pMM)
.GroupBy(a => a.工事ID)
.Select(gr => new
{
pID = gr.Key,
pHayade = gr.Sum(a => a.早出残業),
pZan = gr.Sum(a => a.普通残業),
pSinya = gr.Sum(a => a.深夜残業),
pkm = gr.Sum(a => a.通勤業務走行),
pkmShiyou = gr.Sum(a => a.私用走行)
});
int iX = 0;
foreach (var t in ss)
{
var m = dts.M_工事.Single(a => a.ID == t.pID);
oxlsPrintSheet.Cells[S_GYO2 + iX, 1] = m.称;
oxlsPrintSheet.Cells[S_GYO2 + iX, 4] = m.勤務地名;
// 出勤日数
int s = dts.T_勤怠.Count(a => a.工事ID == t.pID && a.日付.Year == pYY &&
a.日付.Month == pMM && a.社員ID == pNum &&
(a.出勤印 == global.flgOn || a.休日出勤 == global.flgOn));
oxlsPrintSheet.Cells[S_GYO2 + iX, 5] = s.ToString();
// 代休日数
s = dts.T_勤怠.Count(a => a.工事ID == t.pID && a.日付.Year == pYY &&
a.日付.Month == pMM && a.社員ID == pNum &&
a.代休 == global.flgOn);
oxlsPrintSheet.Cells[S_GYO2 + iX, 6] = s.ToString();
// 工事部署ごとの休日勤務時間・法定休日勤務時間を求める
int hol = 0;
int hotei = 0;
Utility.getHolTime(dts.T_勤怠, out hol, out hotei, t.pID, pYY, pMM, pNum);
// 工事部署ごとの休日代休時間・法定休日時間取得を求める
int holD = 0;
int hoteiD = 0;
Utility.getdaikyuTime(dts, out holD, out hoteiD, pYY, pMM, pNum, t.pID);
// 代休取得した時間を差し引く
hol -= holD;
hotei -= hoteiD;
oxlsPrintSheet.Cells[S_GYO2 + iX, 7] = Utility.intToHhMM(hol);
oxlsPrintSheet.Cells[S_GYO2 + iX, 8] = Utility.intToHhMM(hotei);
// 宿泊日数
s = dts.T_勤怠.Count(a => a.工事ID == t.pID && a.日付.Year == pYY &&
a.日付.Month == pMM && a.社員ID == pNum &&
a.宿泊 == global.flgOn);
oxlsPrintSheet.Cells[S_GYO2 + iX, 11] = s.ToString();
// 早出残業＋普通残業 2018/07/18
int zanTotal = hayadeZanTotal(Utility.nulltoStr(t.pHayade), Utility.nulltoStr(t.pZan));
if (zanTotal > 0)
{
oxlsPrintSheet.Cells[S_GYO2 + iX, 9] = Utility.intToHhMM(zanTotal);
}
else
{
oxlsPrintSheet.Cells[S_GYO2 + iX, 9] = string.Empty;
}
//oxlsPrintSheet.Cells[S_GYO2 + iX, 9] = Utility.intToHhMM(t.pZan); // 2018/07/18 コメント化
oxlsPrintSheet.Cells[S_GYO2 + iX, 10] = Utility.intToHhMM(t.pSinya);
//oxlsPrintSheet.Cells[eRow, 15] = t.pkm.ToString();
//oxlsPrintSheet.Cells[eRow, 16] = t.pkmShiyou.ToString();
iX++;
// 印字は5行まで
if (iX > 5)
{
break;
}
}
// 今月末走行距離
//int sYY = pYY + Properties.Settings.Default.rekiHosei;
int sYY = pYY; // 和暦から西暦へ 2018/07/13
DateTime dt2 = DateTime.Parse(sYY.ToString() + "/" + pMM.ToString() + "/01");
dt2 = dt.AddMonths(1).AddDays(-1);
oxlsPrintSheet.Cells[39, 15] = getKmAll(dts, pNum, dt); // 2018/12/15
// 前月末走行距離
dt = dt.AddMonths(-1);
oxlsPrintSheet.Cells[40, 15] = getKmAll(dts, pNum, dt); // 2018/12/15
// 確認のためのウィンドウを表示する
//oXls.Visible = true;
//印刷
//oXlsBook.PrintPreview(true);
//oXlsBook.PrintOut();
//保存処理
oXls.DisplayAlerts = false;
xlsName = Properties.Settings.Default.xlsOutPath + pNum.ToString() + " " + pYY.ToString() + "年" + pMM.ToString() + "月 出勤簿・車両走行報告書.xlsx";
oXlsBook.SaveAs(xlsName);
//Bookをクローズ
oXlsBook.Close(Type.Missing, Type.Missing, Type.Missing);
//Excelを終了
oXls.Quit();
}
catch (Exception e)
{
//MessageBox.Show(e.Message, "印刷処理", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
// ウィンドウを非表示にする
oXls.Visible = false;
//保存処理
oXls.DisplayAlerts = false;
//Bookをクローズ
oXlsBook.Close(Type.Missing, Type.Missing, Type.Missing);
//Excelを終了
oXls.Quit();
}
finally
{
// COM オブジェクトの参照カウントを解放する
System.Runtime.InteropServices.Marshal.ReleaseComObject(oxlsPrintSheet);
System.Runtime.InteropServices.Marshal.ReleaseComObject(oXlsBook);
System.Runtime.InteropServices.Marshal.ReleaseComObject(oXls);
}
}
catch (Exception e)
{
//MessageBox.Show(e.Message, "印刷処理", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
}
return(xlsName);
}
///-------------------------------------------------------------------------
/// <summary>
/// 走行距離取得 </summary>
/// <param name="sNum">
/// 個人コード</param>
/// <param name="dt">
/// 日付</param>
/// <returns>
/// 当日までの走行距離</returns>
///-------------------------------------------------------------------------
private int getKmAll(mailReceiveDataSet dts, int sNum, DateTime dt)
{
int sKm = 0;
DateTime kDt = DateTime.Parse("1900/01/01");
//if (dts.M_社員.Any(a => a.ID == sNum))
//{
// var s = dts.M_社員.Single(a => a.ID == sNum);
// if (!s.Is走行起点Null())
// {
// sKm = s.走行起点;
// if (s.走行起点日付 != null && s.走行起点日付 != string.Empty)
// {
// kDt = DateTime.Parse(s.走行起点日付);
// }
// }
// else
// {
// sKm = 0;
// }
//}
// FillBySCodeに変更：2018/12/15
if (sAdp.FillBySCode(dts.M_社員, sNum) > 0)
{
var s = dts.M_社員.Single(a => a.ID == sNum);
if (!s.Is走行起点Null())
{
sKm = s.走行起点;
if (s.走行起点日付 != null && s.走行起点日付 != string.Empty)
{
kDt = DateTime.Parse(s.走行起点日付);
}
}
else
{
sKm = 0;
}
}
if (dt < kDt)
{
// 走行起点日付以前のとき
var sss = dts.T_勤怠.Where(a => a.社員ID == sNum && a.日付 <= dt)
.GroupBy(a => a.社員ID)
.Select(g => new
{
pid = g.Key,
pKm1 = g.Sum(a => a.通勤業務走行),
pKm2 = g.Sum(a => a.私用走行)
});
foreach (var t in sss)
{
if (t.pid == sNum)
{
sKm = t.pKm1 + t.pKm2;
}
}
}
else
{
// 走行起点日の翌日以降のとき走行起点Kmに加算する
var ss = dts.T_勤怠.Where(a => a.社員ID == sNum && a.日付 <= dt && a.日付 > kDt)
.GroupBy(a => a.社員ID)
.Select(g => new
{
pid = g.Key,
pKm1 = g.Sum(a => a.通勤業務走行),
pKm2 = g.Sum(a => a.私用走行)
});
foreach (var t in ss)
{
if (t.pid == sNum)
{
sKm += t.pKm1;
sKm += t.pKm2;
}
}
}
return(sKm);
}
