public List <SRY09010_Member> SRY09010_GetDataHinList(string s車輌From, string s車輌To, int?[] i車輌List, string p集計期間From, string p集計期間To, int i表示順序)
{
using (TRAC3Entities context = new TRAC3Entities(CommonData.TRAC3_GetConnectionString()))
{
string s集計期間From, s集計期間To;
s集計期間From = p集計期間From.ToString().Substring(0, 4) + "年" + p集計期間From.ToString().Substring(4, 2) + "月度";
s集計期間To = p集計期間To.ToString().Substring(0, 4) + "年" + p集計期間To.ToString().Substring(4, 2) + "月度";
int i集計期間From, i集計期間To;
i集計期間From = Convert.ToInt32(p集計期間From);
i集計期間To = Convert.ToInt32(p集計期間To);
List <SRY09010_Member> retList = new List <SRY09010_Member>();
context.Connection.Open();
try
{
string 車輌ﾋﾟｯｸｱｯﾌﾟ指定 = string.Empty;
var goukei = (from s14c in context.S14_CARSB
where s14c.集計年月 >= i集計期間From && s14c.集計年月 <= i集計期間To
group s14c by s14c.車輌KEY into Group
select new SRY09010g_Member
{
車輌Key = Group.Key,
合計 = Group.Sum(c => c.金額),
}).AsQueryable();
var query = (from m05 in context.M05_CAR.Where(c => c.削除日付 == null)
join s14 in context.S14_CAR.Where(c => c.集計年月 >= i集計期間From && c.集計年月 <= i集計期間To) on m05.車輌KEY equals s14.車輌KEY into Group
from s14Group in Group
join s14g in goukei on s14Group.車輌KEY equals s14g.車輌Key into sg14Group
from sgg14Group in sg14Group
group new { m05, s14Group, sgg14Group } by new { m05.車輌ID, m05.車輌番号, sgg14Group.合計 } into grGroup
select new SRY09010_Member
{
車輌ID = grGroup.Key.車輌ID,
車輌番号 = grGroup.Key.車輌番号,
日数 = grGroup.Sum(c => c.s14Group.稼動日数) == null ? 0 : grGroup.Sum(c => c.s14Group.稼動日数),
拘束H = grGroup.Sum(c => c.s14Group.拘束時間) == null ? 0 : grGroup.Sum(c => c.s14Group.拘束時間),
運転H = grGroup.Sum(c => c.s14Group.運転時間) == null ? 0 : grGroup.Sum(c => c.s14Group.運転時間),
高速H = grGroup.Sum(c => c.s14Group.高速時間) == null ? 0 : grGroup.Sum(c => c.s14Group.高速時間),
作業H = grGroup.Sum(c => c.s14Group.作業時間) == null ? 0 : grGroup.Sum(c => c.s14Group.作業時間),
休憩H = grGroup.Sum(c => c.s14Group.休憩時間) == null ? 0 : grGroup.Sum(c => c.s14Group.休憩時間),
残業H = grGroup.Sum(c => c.s14Group.残業時間) == null ? 0 : grGroup.Sum(c => c.s14Group.残業時間),
深夜H = grGroup.Sum(c => c.s14Group.深夜時間) == null ? 0 : grGroup.Sum(c => c.s14Group.深夜時間),
走行KM = grGroup.Sum(c => c.s14Group.走行ＫＭ) == null ? 0 : grGroup.Sum(c => c.s14Group.走行ＫＭ),
実車KM = grGroup.Sum(c => c.s14Group.実車ＫＭ) == null ? 0 : grGroup.Sum(c => c.s14Group.実車ＫＭ),
輸送屯数 = grGroup.Sum(c => c.s14Group.輸送屯数) == null ? 0 : grGroup.Sum(c => c.s14Group.輸送屯数),
運送収入 = grGroup.Sum(c => c.s14Group.運送収入) == null ? 0 : grGroup.Sum(c => c.s14Group.運送収入),
燃料L = grGroup.Sum(c => c.s14Group.燃料Ｌ) == null ? 0 : grGroup.Sum(c => c.s14Group.燃料Ｌ),
経費合計 = grGroup.Key.合計,
集計年月From = s集計期間From,
集計年月To = s集計期間To,
表示順序 = i表示順序 == 0 ? "ID順" : i表示順序 == 1 ? "車種順" : "運送収入順",
車輌指定 = s車輌From + "～" + s車輌To,
車輌ﾋﾟｯｸｱｯﾌﾟ = 車輌ﾋﾟｯｸｱｯﾌﾟ指定 == "" ? "無" : 車輌ﾋﾟｯｸｱｯﾌﾟ指定,
}).AsQueryable();
//***検索条件***//
if (!(string.IsNullOrEmpty(s車輌From + s車輌To) && i車輌List.Length == 0))
{
//From & ToがNULLだった場合
if (string.IsNullOrEmpty(s車輌From + s車輌To))
{
query = query.Where(c => c.車輌ID >= int.MaxValue);
}
//車輌From処理 Min値
if (!string.IsNullOrEmpty(s車輌From))
{
int i車輌From = AppCommon.IntParse(s車輌From);
query = query.Where(c => c.車輌ID >= i車輌From);
}
//車輌To処理 Max値
if (!string.IsNullOrEmpty(s車輌To))
{
int i車輌TO = AppCommon.IntParse(s車輌To);
query = query.Where(c => c.車輌ID <= i車輌TO);
}
if (i車輌List.Length > 0)
{
var intCause = i車輌List;
query = query.Union(from m05 in context.M05_CAR.Where(c => c.削除日付 == null)
join s14 in context.S14_CAR.Where(c => c.集計年月 >= i集計期間From && c.集計年月 <= i集計期間To) on m05.車輌KEY equals s14.車輌KEY into Group
from s14Group in Group
join s14g in goukei on s14Group.車輌KEY equals s14g.車輌Key into sg14Group
from sgg14Group in sg14Group
group new { m05, s14Group, sgg14Group } by new { m05.車輌ID, m05.車輌番号, sgg14Group.合計 } into grGroup
where intCause.Contains(grGroup.Key.車輌ID)
select new SRY09010_Member
{
車輌ID = grGroup.Key.車輌ID,
車輌番号 = grGroup.Key.車輌番号,
日数 = grGroup.Sum(c => c.s14Group.稼動日数) == null ? 0 : grGroup.Sum(c => c.s14Group.稼動日数),
拘束H = grGroup.Sum(c => c.s14Group.拘束時間) == null ? 0 : grGroup.Sum(c => c.s14Group.拘束時間),
運転H = grGroup.Sum(c => c.s14Group.運転時間) == null ? 0 : grGroup.Sum(c => c.s14Group.運転時間),
高速H = grGroup.Sum(c => c.s14Group.高速時間) == null ? 0 : grGroup.Sum(c => c.s14Group.高速時間),
作業H = grGroup.Sum(c => c.s14Group.作業時間) == null ? 0 : grGroup.Sum(c => c.s14Group.作業時間),
休憩H = grGroup.Sum(c => c.s14Group.休憩時間) == null ? 0 : grGroup.Sum(c => c.s14Group.休憩時間),
残業H = grGroup.Sum(c => c.s14Group.残業時間) == null ? 0 : grGroup.Sum(c => c.s14Group.残業時間),
深夜H = grGroup.Sum(c => c.s14Group.深夜時間) == null ? 0 : grGroup.Sum(c => c.s14Group.深夜時間),
走行KM = grGroup.Sum(c => c.s14Group.走行ＫＭ) == null ? 0 : grGroup.Sum(c => c.s14Group.走行ＫＭ),
実車KM = grGroup.Sum(c => c.s14Group.実車ＫＭ) == null ? 0 : grGroup.Sum(c => c.s14Group.実車ＫＭ),
輸送屯数 = grGroup.Sum(c => c.s14Group.輸送屯数) == null ? 0 : grGroup.Sum(c => c.s14Group.輸送屯数),
運送収入 = grGroup.Sum(c => c.s14Group.運送収入) == null ? 0 : grGroup.Sum(c => c.s14Group.運送収入),
燃料L = grGroup.Sum(c => c.s14Group.燃料Ｌ) == null ? 0 : grGroup.Sum(c => c.s14Group.燃料Ｌ),
経費合計 = grGroup.Key.合計,
集計年月From = s集計期間From,
集計年月To = s集計期間To,
表示順序 = i表示順序 == 0 ? "ID順" : i表示順序 == 1 ? "車種順" : "運送収入順",
車輌指定 = s車輌From + "～" + s車輌To,
車輌ﾋﾟｯｸｱｯﾌﾟ = 車輌ﾋﾟｯｸｱｯﾌﾟ指定 == "" ? "無" : 車輌ﾋﾟｯｸｱｯﾌﾟ指定,
});
}
}
else
{
//車輌FromがNullだった場合
if (string.IsNullOrEmpty(s車輌From))
{
query = query.Where(c => c.車輌ID >= int.MinValue);
}
//車輌ToがNullだった場合
if (string.IsNullOrEmpty(s車輌To))
{
query = query.Where(c => c.車輌ID <= int.MaxValue);
}
}
//乗務員指定の表示
if (i車輌List.Length > 0)
{
for (int i = 0; i < query.Count(); i++)
{
車輌ﾋﾟｯｸｱｯﾌﾟ指定 = 車輌ﾋﾟｯｸｱｯﾌﾟ指定 + i車輌List[i].ToString();
if (i < i車輌List.Length)
{
if (i == i車輌List.Length - 1)
{
break;
}
車輌ﾋﾟｯｸｱｯﾌﾟ指定 = 車輌ﾋﾟｯｸｱｯﾌﾟ指定 + ",";
}
if (i車輌List.Length == 1)
{
break;
}
}
}
//表示順序変更
switch (i表示順序)
{
case 0:
//車輌番号昇順
query = query.OrderBy(c => c.車輌ID);
break;
case 1:
query = query.OrderBy(c => c.車輌番号);
break;
case 2:
//運送収入降順
query = query.OrderByDescending(c => c.運送収入);
break;
}
query = query.Where(c => c.経費合計 != 0 || c.日数 != 0 || c.運送収入 != 0);
//retList = query.ToList();
foreach (var rec in query)
{
// 各時間項目の時分を、分に変換する。
rec.拘束H = (decimal)LinqSub.時間TO分(rec.拘束H);
rec.運転H = (decimal)LinqSub.時間TO分(rec.運転H);
rec.高速H = (decimal)LinqSub.時間TO分(rec.高速H);
rec.作業H = (decimal)LinqSub.時間TO分(rec.作業H);
//rec.待機H = (decimal)LinqSub.時間TO分(rec.待機H);
rec.休憩H = (decimal)LinqSub.時間TO分(rec.休憩H);
rec.残業H = (decimal)LinqSub.時間TO分(rec.残業H);
rec.深夜H = (decimal)LinqSub.時間TO分(rec.深夜H);
retList.Add(rec);
}
return(retList);
}
catch (Exception ex)
{
throw ex;
}
}
}
/// <summary>
/// SRY01010 印刷
/// </summary>
/// <param name="p商品ID">車輌コード</param>
/// <returns>T01</returns>
public List <SRY01010_Member> GetDataList(string p車輌From, string p車輌To, int?[] i車輌List, int p作成締日, DateTime d集計期間From, DateTime d集計期間To, string p作成年度, string sIDList)
{
using (TRAC3Entities context = new TRAC3Entities(CommonData.TRAC3_GetConnectionString()))
{
List <SRY01010_Member> retList = new List <SRY01010_Member>();
context.Connection.Open();
var query = (from t02 in context.T02_UTRN.Where(t02 => t02.労務日 >= d集計期間From && t02.労務日 <= d集計期間To)
join t03 in context.T03_KTRN on t02.明細番号 equals t03.明細番号 into t03Group
join t01 in context.T01_TRN.Where(t01 => (t01.入力区分 != 3) || (t01.入力区分 == 3 && t01.明細行 != 1)) on t02.明細番号 equals t01.明細番号 into t01Group
join m04 in context.M04_DRV on t02.乗務員KEY equals m04.乗務員KEY into m04Group
from m04g in m04Group.DefaultIfEmpty()
join m78 in context.M78_SYK on t02.出勤区分ID equals m78.出勤区分ID into m78Group
from m78g in m78Group.DefaultIfEmpty()
join m05 in context.M05_CAR on t02.車輌KEY equals m05.車輌KEY into m05Group
from m05g in m05Group.DefaultIfEmpty()
where m05g.車輌KEY != null
select new SRY01010_Member
{
出庫日 = t02.実運行日開始,
帰庫日 = t02.実運行日終了,
出庫H = t02.出庫時間,
帰庫H = t02.帰庫時間,
乗務員名 = m04g.乗務員名,
出庫区分 = m78g.出勤区分名,
水揚金額 = t01Group.Sum(s => s.水揚金額),
経費1 = t03Group.Where(w => w.経費項目ID == 601).Sum(s => s.金額),
経費2 = t03Group.Where(w => w.経費項目ID == 602).Sum(s => s.金額),
経費3 = t03Group.Where(w => w.経費項目ID == 603).Sum(s => s.金額),
経費4 = t03Group.Where(w => w.経費項目ID == 604).Sum(s => s.金額),
経費5 = t03Group.Where(w => w.経費項目ID == 605).Sum(s => s.金額),
経費6 = t03Group.Where(w => w.経費項目ID == 606).Sum(s => s.金額),
経費7 = t03Group.Where(w => w.経費項目ID == 607).Sum(s => s.金額),
その他経費 = t03Group.Where(w => w.経費項目ID != 601 && w.経費項目ID != 602 && w.経費項目ID != 603 && w.経費項目ID != 604 &&
w.経費項目ID != 605 && w.経費項目ID != 606 && w.経費項目ID != 607 &&
w.経費項目ID != 401).Sum(s => s.金額),
経費名1 = (from m07 in context.M07_KEI.Where(c => c.経費項目ID == 601) select m07.経費項目名).FirstOrDefault(),
経費名2 = (from m07 in context.M07_KEI.Where(c => c.経費項目ID == 602) select m07.経費項目名).FirstOrDefault(),
経費名3 = (from m07 in context.M07_KEI.Where(c => c.経費項目ID == 603) select m07.経費項目名).FirstOrDefault(),
経費名4 = (from m07 in context.M07_KEI.Where(c => c.経費項目ID == 604) select m07.経費項目名).FirstOrDefault(),
経費名5 = (from m07 in context.M07_KEI.Where(c => c.経費項目ID == 605) select m07.経費項目名).FirstOrDefault(),
経費名6 = (from m07 in context.M07_KEI.Where(c => c.経費項目ID == 606) select m07.経費項目名).FirstOrDefault(),
経費名7 = (from m07 in context.M07_KEI.Where(c => c.経費項目ID == 607) select m07.経費項目名).FirstOrDefault(),
拘束H = t02.拘束時間,
運転H = t02.運転時間,
高速H = t02.高速時間,
作業H = t02.作業時間,
待機H = t02.待機時間,
休憩H = t02.休憩時間,
残業H = t02.残業時間,
深夜H = t02.深夜時間,
燃料L数 = t03Group.Where(w => w.経費項目ID == 401).Sum(s => s.数量),
燃料代 = t03Group.Where(w => w.経費項目ID == 401).Sum(s => s.金額),
走行KM = t02.走行ＫＭ,
実車KM = t02.実車ＫＭ,
輸送屯数 = t02.輸送屯数,
コード = m05g.車輌ID,
車輌番号 = m05g.車輌番号,
期間From = d集計期間From,
期間To = d集計期間To,
IDFrom = p車輌From,
IDTo = p車輌To,
IDList = sIDList,
}).AsQueryable();
if (!(string.IsNullOrEmpty(p車輌From + p車輌To) && i車輌List.Length == 0))
{
//車輌が検索対象に入っていない時全データ取得
if (string.IsNullOrEmpty(p車輌From + p車輌To))
{
query = query.Where(c => c.コード >= int.MaxValue);
}
//車輌From処理 Min値
if (!string.IsNullOrEmpty(p車輌From))
{
int i車輌FROM = AppCommon.IntParse(p車輌From);
query = query.Where(c => c.コード >= i車輌FROM);
}
//車輌To処理 Max値
if (!string.IsNullOrEmpty(p車輌To))
{
int i車輌TO = AppCommon.IntParse(p車輌To);
query = query.Where(c => c.コード <= i車輌TO);
}
if (i車輌List.Length > 0)
{
var intCause = i車輌List;
query = query.Union(from t02 in context.T02_UTRN.Where(t02 => t02.労務日 >= d集計期間From && t02.労務日 <= d集計期間To && t02.車輌KEY != null)
join t03 in context.T03_KTRN on t02.明細番号 equals t03.明細番号 into t03Group
join t01 in context.T01_TRN.Where(t01 => (t01.入力区分 != 3) || (t01.入力区分 == 3 && t01.明細行 != 1)) on t02.明細番号 equals t01.明細番号 into t01Group
join m04 in context.M04_DRV on t02.乗務員KEY equals m04.乗務員KEY into m04Group
from m04g in m04Group.DefaultIfEmpty()
join m78 in context.M78_SYK on t02.出勤区分ID equals m78.出勤区分ID into m78Group
from m78g in m78Group.DefaultIfEmpty()
join m05 in context.M05_CAR on t02.車輌KEY equals m05.車輌KEY into m05Group
from m05g in m05Group.DefaultIfEmpty()
where intCause.Contains(m05g.車輌ID) && m05g.車輌KEY != null
select new SRY01010_Member
{
出庫日 = t02.実運行日開始,
帰庫日 = t02.実運行日終了,
出庫H = t02.出庫時間,
帰庫H = t02.帰庫時間,
乗務員名 = m04g.乗務員名,
出庫区分 = m78g.出勤区分名,
水揚金額 = t01Group.Sum(s => s.水揚金額),
経費1 = t03Group.Where(w => w.経費項目ID == 601).Sum(s => s.金額),
経費2 = t03Group.Where(w => w.経費項目ID == 602).Sum(s => s.金額),
経費3 = t03Group.Where(w => w.経費項目ID == 603).Sum(s => s.金額),
経費4 = t03Group.Where(w => w.経費項目ID == 604).Sum(s => s.金額),
経費5 = t03Group.Where(w => w.経費項目ID == 605).Sum(s => s.金額),
経費6 = t03Group.Where(w => w.経費項目ID == 606).Sum(s => s.金額),
経費7 = t03Group.Where(w => w.経費項目ID == 607).Sum(s => s.金額),
その他経費 = t03Group.Where(w => w.経費項目ID != 601 && w.経費項目ID != 602 && w.経費項目ID != 603 && w.経費項目ID != 604 &&
w.経費項目ID != 605 && w.経費項目ID != 606 && w.経費項目ID != 607 &&
w.経費項目ID != 401).Sum(s => s.金額),
経費名1 = (from m07 in context.M07_KEI.Where(c => c.経費項目ID == 601) select m07.経費項目名).FirstOrDefault(),
経費名2 = (from m07 in context.M07_KEI.Where(c => c.経費項目ID == 602) select m07.経費項目名).FirstOrDefault(),
経費名3 = (from m07 in context.M07_KEI.Where(c => c.経費項目ID == 603) select m07.経費項目名).FirstOrDefault(),
経費名4 = (from m07 in context.M07_KEI.Where(c => c.経費項目ID == 604) select m07.経費項目名).FirstOrDefault(),
経費名5 = (from m07 in context.M07_KEI.Where(c => c.経費項目ID == 605) select m07.経費項目名).FirstOrDefault(),
経費名6 = (from m07 in context.M07_KEI.Where(c => c.経費項目ID == 606) select m07.経費項目名).FirstOrDefault(),
経費名7 = (from m07 in context.M07_KEI.Where(c => c.経費項目ID == 607) select m07.経費項目名).FirstOrDefault(),
拘束H = t02.拘束時間,
運転H = t02.運転時間,
高速H = t02.高速時間,
作業H = t02.作業時間,
待機H = t02.待機時間,
休憩H = t02.休憩時間,
残業H = t02.残業時間,
深夜H = t02.深夜時間,
燃料L数 = t03Group.Where(w => w.経費項目ID == 401).Sum(s => s.数量),
燃料代 = t03Group.Where(w => w.経費項目ID == 401).Sum(s => s.金額),
走行KM = t02.走行ＫＭ,
実車KM = t02.実車ＫＭ,
輸送屯数 = t02.輸送屯数,
コード = m05g.車輌ID,
車輌番号 = m05g.車輌番号,
期間From = d集計期間From,
期間To = d集計期間To,
IDFrom = p車輌From,
IDTo = p車輌To,
IDList = sIDList,
});
}
}
query = query.Distinct();
//結果をリスト化
query = query.OrderBy(c => c.コード).ThenBy(c => c.出庫日);
//retList = query.ToList();
foreach (var rec in query)
{
// 各時間項目の時分を、分に変換する。
rec.拘束H = LinqSub.時間TO分(rec.拘束H);
rec.運転H = LinqSub.時間TO分(rec.運転H);
rec.高速H = LinqSub.時間TO分(rec.高速H);
rec.作業H = LinqSub.時間TO分(rec.作業H);
rec.待機H = LinqSub.時間TO分(rec.待機H);
rec.休憩H = LinqSub.時間TO分(rec.休憩H);
rec.残業H = LinqSub.時間TO分(rec.残業H);
rec.深夜H = LinqSub.時間TO分(rec.深夜H);
retList.Add(rec);
}
return(retList);
}
}
public List <SRY12010_Member> SRY12010_GetData_CSV(string s車種From, string s車種To, int?[] i車種List, string p集計期間From, string p集計期間To, int i表示順序)
{
using (TRAC3Entities context = new TRAC3Entities(CommonData.TRAC3_GetConnectionString()))
{
string s集計期間From, s集計期間To;
s集計期間From = p集計期間From.ToString().Substring(0, 4) + "年" + p集計期間From.ToString().Substring(4, 2) + "月度";
s集計期間To = p集計期間To.ToString().Substring(0, 4) + "年" + p集計期間To.ToString().Substring(4, 2) + "月度";
int i集計期間From, i集計期間To;
i集計期間From = Convert.ToInt32(p集計期間From);
i集計期間To = Convert.ToInt32(p集計期間To);
List <SRY12010_Member> retList = new List <SRY12010_Member>();
context.Connection.Open();
string 車種ﾋﾟｯｸｱｯﾌﾟ指定 = string.Empty;
//車種別経費合計
var goukei = (from s14 in context.S14_CAR.Where(c => c.集計年月 >= i集計期間From && c.集計年月 <= i集計期間To)
join s14c in context.S14_CARSB.Where(c => c.集計年月 >= i集計期間From && c.集計年月 <= i集計期間To) on new { s14.車輌KEY, s14.集計年月 } equals new { s14c.車輌KEY, s14c.集計年月 } into Group
from s14Group in Group
group new { s14, s14Group } by s14.車種ID into Group
select new SRY12010g_Member
{
車種ID = Group.Key,
経費合計 = Group.Sum(c => c.s14Group.金額),
}).AsQueryable();
var query = (from m06 in context.M06_SYA.Where(c => c.削除日付 == null)
join s14 in context.S14_CAR.Where(c => c.集計年月 >= i集計期間From && c.集計年月 <= i集計期間To) on m06.車種ID equals s14.車種ID into Group
from s14Group in Group
join s14g in goukei on s14Group.車種ID equals s14g.車種ID into s14gGroup
from s14GGroup in s14gGroup
group new { m06, s14Group, s14GGroup } by new { m06.車種ID, m06.車種名, s14GGroup.経費合計 } into grGroup
select new SRY12010_Member
{
車種ID = grGroup.Key.車種ID,
車種名 = grGroup.Key.車種名,
台数 = grGroup.Count(),
拘束H = grGroup.Sum(c => c.s14Group.拘束時間) == null ? 0 : grGroup.Sum(c => c.s14Group.拘束時間),
運転H = grGroup.Sum(c => c.s14Group.運転時間) == null ? 0 : grGroup.Sum(c => c.s14Group.運転時間),
高速H = grGroup.Sum(c => c.s14Group.高速時間) == null ? 0 : grGroup.Sum(c => c.s14Group.高速時間),
作業H = grGroup.Sum(c => c.s14Group.作業時間) == null ? 0 : grGroup.Sum(c => c.s14Group.作業時間),
待機H = grGroup.Sum(c => c.s14Group.待機時間) == null ? 0 : grGroup.Sum(c => c.s14Group.待機時間),
休憩H = grGroup.Sum(c => c.s14Group.休憩時間) == null ? 0 : grGroup.Sum(c => c.s14Group.休憩時間),
残業H = grGroup.Sum(c => c.s14Group.残業時間) == null ? 0 : grGroup.Sum(c => c.s14Group.残業時間),
深夜H = grGroup.Sum(c => c.s14Group.深夜時間) == null ? 0 : grGroup.Sum(c => c.s14Group.深夜時間),
走行KM = grGroup.Sum(c => c.s14Group.走行ＫＭ) == null ? 0 : grGroup.Sum(c => c.s14Group.走行ＫＭ),
実車KM = grGroup.Sum(c => c.s14Group.実車ＫＭ) == null ? 0 : grGroup.Sum(c => c.s14Group.実車ＫＭ),
輸送屯数 = grGroup.Sum(c => c.s14Group.輸送屯数) == null ? 0 : grGroup.Sum(c => c.s14Group.輸送屯数),
運送収入 = grGroup.Sum(c => c.s14Group.運送収入) == null ? 0 : grGroup.Sum(c => c.s14Group.運送収入),
燃料L = grGroup.Sum(c => c.s14Group.燃料Ｌ) == null ? 0 : grGroup.Sum(c => c.s14Group.燃料Ｌ),
経費合計 = grGroup.Key.経費合計,
集計年月From = s集計期間From,
集計年月To = s集計期間To,
車種指定 = s車種From + "～" + s車種To,
車種ﾋﾟｯｸｱｯﾌﾟ = 車種ﾋﾟｯｸｱｯﾌﾟ指定 == "" ? "無" : 車種ﾋﾟｯｸｱｯﾌﾟ指定,
表示順序 = i表示順序 == 0 ? "ID順" : i表示順序 == 1 ? "車種名順" : "運送収入順",
}).AsQueryable();
if (!string.IsNullOrEmpty(s車種From + s車種To) && i車種List.Length == 0)
{
//車種Fromで絞込み
if (!string.IsNullOrEmpty(s車種From))
{
int i車種From = AppCommon.IntParse(s車種From);
query = query.Where(c => c.車種ID >= i車種From);
}
//車種Toで絞込み
if (!string.IsNullOrEmpty(s車種To))
{
int i車種To = AppCommon.IntParse(s車種To);
query = query.Where(c => c.車種ID <= i車種To);
}
//車種ﾋﾟｯｸｱｯﾌﾟ
if (i車種List.Length > 0)
{
var intCause = i車種List;
query = query.Union(from m06 in context.M06_SYA.Where(c => c.削除日付 == null)
join s14 in context.S14_CAR.Where(c => c.集計年月 >= i集計期間From && c.集計年月 <= i集計期間To) on m06.車種ID equals s14.車種ID into Group
from s14Group in Group
join s14g in goukei on s14Group.車種ID equals s14g.車種ID into s14gGroup
from s14GGroup in s14gGroup
group new { m06, s14Group, s14GGroup } by new { m06.車種ID, m06.車種名, s14GGroup.経費合計 } into grGroup
where intCause.Contains(grGroup.Key.車種ID)
select new SRY12010_Member
{
車種ID = grGroup.Key.車種ID,
車種名 = grGroup.Key.車種名,
台数 = grGroup.Count(),
拘束H = grGroup.Sum(c => c.s14Group.拘束時間) == null ? 0 : grGroup.Sum(c => c.s14Group.拘束時間),
運転H = grGroup.Sum(c => c.s14Group.運転時間) == null ? 0 : grGroup.Sum(c => c.s14Group.運転時間),
高速H = grGroup.Sum(c => c.s14Group.高速時間) == null ? 0 : grGroup.Sum(c => c.s14Group.高速時間),
作業H = grGroup.Sum(c => c.s14Group.作業時間) == null ? 0 : grGroup.Sum(c => c.s14Group.作業時間),
待機H = grGroup.Sum(c => c.s14Group.待機時間) == null ? 0 : grGroup.Sum(c => c.s14Group.待機時間),
休憩H = grGroup.Sum(c => c.s14Group.休憩時間) == null ? 0 : grGroup.Sum(c => c.s14Group.休憩時間),
残業H = grGroup.Sum(c => c.s14Group.残業時間) == null ? 0 : grGroup.Sum(c => c.s14Group.残業時間),
深夜H = grGroup.Sum(c => c.s14Group.深夜時間) == null ? 0 : grGroup.Sum(c => c.s14Group.深夜時間),
走行KM = grGroup.Sum(c => c.s14Group.走行ＫＭ) == null ? 0 : grGroup.Sum(c => c.s14Group.走行ＫＭ),
実車KM = grGroup.Sum(c => c.s14Group.実車ＫＭ) == null ? 0 : grGroup.Sum(c => c.s14Group.実車ＫＭ),
輸送屯数 = grGroup.Sum(c => c.s14Group.輸送屯数) == null ? 0 : grGroup.Sum(c => c.s14Group.輸送屯数),
運送収入 = grGroup.Sum(c => c.s14Group.運送収入) == null ? 0 : grGroup.Sum(c => c.s14Group.運送収入),
燃料L = grGroup.Sum(c => c.s14Group.燃料Ｌ) == null ? 0 : grGroup.Sum(c => c.s14Group.燃料Ｌ),
経費合計 = grGroup.Key.経費合計,
集計年月From = s集計期間From,
集計年月To = s集計期間To,
表示順序 = i表示順序 == 0 ? "ID順" : i表示順序 == 1 ? "車種名順" : "運送収入順",
車種指定 = s車種From + "～" + s車種To,
車種ﾋﾟｯｸｱｯﾌﾟ = 車種ﾋﾟｯｸｱｯﾌﾟ指定 == "" ? "無" : 車種ﾋﾟｯｸｱｯﾌﾟ指定,
});
}
}
else
{
//** From,To,ﾋﾟｯｸｱｯﾌﾟがNullの場合全件出力**//
query = query.Where(c => c.車種ID >= int.MinValue && c.車種ID <= int.MaxValue);
}
//乗務員指定の表示
if (i車種List.Length > 0)
{
for (int i = 0; i < query.Count(); i++)
{
車種ﾋﾟｯｸｱｯﾌﾟ指定 = 車種ﾋﾟｯｸｱｯﾌﾟ指定 + i車種List[i].ToString();
if (i < i車種List.Length)
{
if (i == i車種List.Length - 1)
{
break;
}
車種ﾋﾟｯｸｱｯﾌﾟ指定 = 車種ﾋﾟｯｸｱｯﾌﾟ指定 + ",";
}
if (i車種List.Length == 1)
{
break;
}
}
}
//表示順序
switch (i表示順序)
{
case 0: //車種IDを昇順
query = query.OrderBy(c => c.車種ID);
break;
case 1: //車種名を昇順
query = query.OrderBy(c => c.車種名);
break;
case 2: //運送収入を降順
query = query.OrderByDescending(c => c.運送収入);
break;
}
//retList = query.ToList();
foreach (var rec in query)
{
// 各時間項目の時分を、分に変換する。
rec.拘束H = (decimal)LinqSub.時間TO分(rec.拘束H);
rec.運転H = (decimal)LinqSub.時間TO分(rec.運転H);
rec.高速H = (decimal)LinqSub.時間TO分(rec.高速H);
rec.作業H = (decimal)LinqSub.時間TO分(rec.作業H);
rec.待機H = (decimal)LinqSub.時間TO分(rec.待機H);
rec.休憩H = (decimal)LinqSub.時間TO分(rec.休憩H);
rec.残業H = (decimal)LinqSub.時間TO分(rec.残業H);
rec.深夜H = (decimal)LinqSub.時間TO分(rec.深夜H);
retList.Add(rec);
}
return(retList);
}
}
/// <summary>
/// JMI03010 印刷
/// </summary>
/// <param name="p商品ID">乗務員コード</param>
/// <returns>T01</returns>
public List <JMI03010_Member> GetDataList(string p乗務員From, string p乗務員To, int?[] i乗務員List, int p作成締日, DateTime d集計期間From, DateTime d集計期間To, string p作成年度)
{
using (TRAC3Entities context = new TRAC3Entities(CommonData.TRAC3_GetConnectionString()))
{
List <JMI03010_Member> retList = new List <JMI03010_Member>();
context.Connection.Open();
var query = (from t02 in context.T02_UTRN.Where(x => x.勤務開始日 >= d集計期間From && x.勤務開始日 <= d集計期間To)
join m04 in context.M04_DRV on t02.乗務員KEY equals m04.乗務員KEY
join m78 in context.M78_SYK on t02.出勤区分ID equals m78.出勤区分ID into sm78
from m78Group in sm78.DefaultIfEmpty()
join t01 in context.T01_TRN.Where(t01 => (t01.入力区分 != 3) || (t01.入力区分 == 3 && t01.明細行 != 1)) on t02.明細番号 equals t01.明細番号 into t01Group
join t03 in context.T03_KTRN on t02.明細番号 equals t03.明細番号 into t03Group
select new JMI03010_Member
{
出庫 = t02.勤務開始日,
帰庫 = t02.勤務終了日,
車番 = t02.車輌番号,
出区 = m78Group.出勤区分名,
出社H = t02.出庫時間,
退社H = t02.帰庫時間,
拘束H = t02.拘束時間 == null ? 0 : t02.拘束時間,
運転H = t02.運転時間 == null ? 0 : t02.運転時間,
高速H = t02.高速時間 == null ? 0 : t02.高速時間,
作業H = t02.作業時間 == null ? 0 : t02.作業時間,
待機H = t02.待機時間 == null ? 0 : t02.待機時間,
休憩H = t02.休憩時間 == null ? 0 : t02.休憩時間,
残業H = t02.残業時間 == null ? 0 : t02.残業時間,
深夜H = t02.深夜時間 == null ? 0 : t02.深夜時間,
走行KM = t02.走行ＫＭ,
実車KM = t02.実車ＫＭ,
輸送屯数 = t02.輸送屯数,
経費合計 = t03Group.Sum(t03 => t03.金額) == null ? 0 : t03Group.Sum(t03 => t03.金額),
明細番号 = t02.明細番号,
コード = m04.乗務員ID,
乗務員名 = m04.乗務員名,
期間From = d集計期間From,
期間To = d集計期間To,
}).AsQueryable();
if (!(string.IsNullOrEmpty(p乗務員From + p乗務員To) && i乗務員List.Length == 0))
{
//乗務員が検索対象に入っていない時全データ取得
if (string.IsNullOrEmpty(p乗務員From + p乗務員To))
{
query = query.Where(c => c.コード >= int.MaxValue);
}
//乗務員From処理 Min値
if (!string.IsNullOrEmpty(p乗務員From))
{
int i乗務員FROM = AppCommon.IntParse(p乗務員From);
query = query.Where(c => c.コード >= i乗務員FROM);
}
//乗務員To処理 Max値
if (!string.IsNullOrEmpty(p乗務員To))
{
int i乗務員TO = AppCommon.IntParse(p乗務員To);
query = query.Where(c => c.コード <= i乗務員TO);
}
if (i乗務員List.Length > 0)
{
var intCause = i乗務員List;
query = query.Union(from t02 in context.T02_UTRN.Where(x => x.勤務開始日 >= d集計期間From && x.勤務開始日 <= d集計期間To)
join m04 in context.M04_DRV on t02.乗務員KEY equals m04.乗務員KEY
join m78 in context.M78_SYK on t02.出勤区分ID equals m78.出勤区分ID into sm78
from m78Group in sm78.DefaultIfEmpty()
join t01 in context.T01_TRN.Where(t01 => (t01.入力区分 != 3) || (t01.入力区分 == 3 && t01.明細行 != 1)) on t02.明細番号 equals t01.明細番号 into t01Group
join t03 in context.T03_KTRN on t02.明細番号 equals t03.明細番号 into t03Group
where intCause.Contains(m04.乗務員ID)
select new JMI03010_Member
{
出庫 = t02.勤務開始日,
帰庫 = t02.勤務終了日,
車番 = t02.車輌番号,
出区 = m78Group.出勤区分名,
出社H = t02.出庫時間,
退社H = t02.帰庫時間,
拘束H = t02.拘束時間 == null ? 0 : t02.拘束時間,
運転H = t02.運転時間 == null ? 0 : t02.運転時間,
高速H = t02.高速時間 == null ? 0 : t02.高速時間,
作業H = t02.作業時間 == null ? 0 : t02.作業時間,
待機H = t02.待機時間 == null ? 0 : t02.待機時間,
休憩H = t02.休憩時間 == null ? 0 : t02.休憩時間,
残業H = t02.残業時間 == null ? 0 : t02.残業時間,
深夜H = t02.深夜時間 == null ? 0 : t02.深夜時間,
走行KM = t02.走行ＫＭ,
実車KM = t02.実車ＫＭ,
輸送屯数 = t02.輸送屯数,
経費合計 = t03Group.Sum(t03 => t03.金額) == null ? 0 : t03Group.Sum(t03 => t03.金額),
明細番号 = t02.明細番号,
コード = m04.乗務員ID,
乗務員名 = m04.乗務員名,
期間From = d集計期間From,
期間To = d集計期間To,
});
}
else
{
query = query.Where(c => c.コード > int.MinValue && c.コード < int.MaxValue);
}
}
query = query.Distinct();
//結果をリスト化
//retList = query.ToList();
foreach (var rec in query)
{
// 各時間項目の時分を、分に変換する。
rec.拘束H = LinqSub.時間TO分(rec.拘束H);
rec.運転H = LinqSub.時間TO分(rec.運転H);
rec.高速H = LinqSub.時間TO分(rec.高速H);
rec.作業H = LinqSub.時間TO分(rec.作業H);
rec.待機H = LinqSub.時間TO分(rec.待機H);
rec.休憩H = LinqSub.時間TO分(rec.休憩H);
rec.残業H = LinqSub.時間TO分(rec.残業H);
rec.深夜H = LinqSub.時間TO分(rec.深夜H);
retList.Add(rec);
}
retList = (retList.OrderBy(c => c.コード).ThenBy(c => c.出庫)).ToList();
return(retList);
}
}
