public List <SRY24010_Member1> SRY24010_GetData(string p車輌From, string p車輌To, int?[] p車輌List, string s車輌List, string p作成締日, DateTime[] p年月date, int[] p年月, int[] p前年年月, int[] p前月年月)
{
using (TRAC3Entities context = new TRAC3Entities(CommonData.TRAC3_GetConnectionString()))
{
int 年月1 = p年月[0];
int 年月2 = p年月[1];
int 年月3 = p年月[2];
int 年月4 = p年月[3];
int 年月5 = p年月[4];
int 年月6 = p年月[5];
int 年月7 = p年月[6];
int 年月8 = p年月[7];
int 年月9 = p年月[8];
int 年月10 = p年月[9];
int 年月11 = p年月[10];
int 年月12 = p年月[11];
int 前年年月1 = p前年年月[0];
int 前年年月2 = p前年年月[1];
int 前年年月3 = p前年年月[2];
int 前年年月4 = p前年年月[3];
int 前年年月5 = p前年年月[4];
int 前年年月6 = p前年年月[5];
int 前年年月7 = p前年年月[6];
int 前年年月8 = p前年年月[7];
int 前年年月9 = p前年年月[8];
int 前年年月10 = p前年年月[9];
int 前年年月11 = p前年年月[10];
int 前年年月12 = p前年年月[11];
int 前月年月1 = p前月年月[0];
int 前月年月2 = p前月年月[1];
int 前月年月3 = p前月年月[2];
int 前月年月4 = p前月年月[3];
int 前月年月5 = p前月年月[4];
int 前月年月6 = p前月年月[5];
int 前月年月7 = p前月年月[6];
int 前月年月8 = p前月年月[7];
int 前月年月9 = p前月年月[8];
int 前月年月10 = p前月年月[9];
int 前月年月11 = p前月年月[10];
int 前月年月12 = p前月年月[11];
DateTime s年月1 = p年月date[0];
DateTime s年月2 = p年月date[1];
DateTime s年月3 = p年月date[2];
DateTime s年月4 = p年月date[3];
DateTime s年月5 = p年月date[4];
DateTime s年月6 = p年月date[5];
DateTime s年月7 = p年月date[6];
DateTime s年月8 = p年月date[7];
DateTime s年月9 = p年月date[8];
DateTime s年月10 = p年月date[9];
DateTime s年月11 = p年月date[10];
DateTime s年月12 = p年月date[11];
int i年月1;
int i年月2;
int i年月3;
int i年月4;
int i年月5;
int i年月6;
int i年月7;
int i年月8;
int i年月9;
int i年月10;
int i年月11;
int i年月12;
//List<SRY24010_Member1> query = new List<SRY24010_Member1>();
context.Connection.Open();
try
{
#region 集計
///総売上
var query_1 = (from m05 in context.M05_CAR.Where(q => q.削除日付 == null)
join s14_1 in context.S14_CAR.Where(q => q.集計年月 == 年月1) on m05.車輌KEY equals s14_1.車輌KEY into s14Group1
from s14_1G in s14Group1.DefaultIfEmpty()
join s14_2 in context.S14_CAR.Where(q => q.集計年月 == 年月2) on m05.車輌KEY equals s14_2.車輌KEY into s14Group2
from s14_2G in s14Group2.DefaultIfEmpty()
join s14_3 in context.S14_CAR.Where(q => q.集計年月 == 年月3) on m05.車輌KEY equals s14_3.車輌KEY into s14Group3
from s14_3G in s14Group3.DefaultIfEmpty()
join s14_4 in context.S14_CAR.Where(q => q.集計年月 == 年月4) on m05.車輌KEY equals s14_4.車輌KEY into s14Group4
from s14_4G in s14Group4.DefaultIfEmpty()
join s14_5 in context.S14_CAR.Where(q => q.集計年月 == 年月5) on m05.車輌KEY equals s14_5.車輌KEY into s14Group5
from s14_5G in s14Group5.DefaultIfEmpty()
join s14_6 in context.S14_CAR.Where(q => q.集計年月 == 年月6) on m05.車輌KEY equals s14_6.車輌KEY into s14Group6
from s14_6G in s14Group6.DefaultIfEmpty()
join s14_7 in context.S14_CAR.Where(q => q.集計年月 == 年月7) on m05.車輌KEY equals s14_7.車輌KEY into s14Group7
from s14_7G in s14Group7.DefaultIfEmpty()
join s14_8 in context.S14_CAR.Where(q => q.集計年月 == 年月8) on m05.車輌KEY equals s14_8.車輌KEY into s14Group8
from s14_8G in s14Group8.DefaultIfEmpty()
join s14_9 in context.S14_CAR.Where(q => q.集計年月 == 年月9) on m05.車輌KEY equals s14_9.車輌KEY into s14Group9
from s14_9G in s14Group9.DefaultIfEmpty()
join s14_10 in context.S14_CAR.Where(q => q.集計年月 == 年月10) on m05.車輌KEY equals s14_10.車輌KEY into s14Group10
from s14_10G in s14Group10.DefaultIfEmpty()
join s14_11 in context.S14_CAR.Where(q => q.集計年月 == 年月11) on m05.車輌KEY equals s14_11.車輌KEY into s14Group11
from s14_11G in s14Group11.DefaultIfEmpty()
join s14_12 in context.S14_CAR.Where(q => q.集計年月 == 年月12) on m05.車輌KEY equals s14_12.車輌KEY into s14Group12
from s14_12G in s14Group12.DefaultIfEmpty()
//join s14_13 in context.S14_CAR.Where(q => q.集計年月 >= 年月1 && q.集計年月 <= 年月12) on m05.車輌KEY equals s14_13.車輌KEY into s14Group13
//from s14_13G in s14Group13.DefaultIfEmpty()
select new SRY24010_Member1
{
順序ID = 1,
車輌ID = m05.車輌ID,
車輌名 = m05.車輌番号,
月1 = s年月1,
月2 = s年月2,
月3 = s年月3,
月4 = s年月4,
月5 = s年月5,
月6 = s年月6,
月7 = s年月7,
月8 = s年月8,
月9 = s年月9,
月10 = s年月10,
月11 = s年月11,
月12 = s年月12,
営業日数1 = s14_1G == null ? 0 : s14_1G.営業日数,
営業日数2 = s14_2G == null ? 0 : s14_2G.営業日数,
営業日数3 = s14_3G == null ? 0 : s14_3G.営業日数,
営業日数4 = s14_4G == null ? 0 : s14_4G.営業日数,
営業日数5 = s14_5G == null ? 0 : s14_5G.営業日数,
営業日数6 = s14_6G == null ? 0 : s14_6G.営業日数,
営業日数7 = s14_7G == null ? 0 : s14_7G.営業日数,
営業日数8 = s14_8G == null ? 0 : s14_8G.営業日数,
営業日数9 = s14_9G == null ? 0 : s14_9G.営業日数,
営業日数10 = s14_10G == null ? 0 : s14_10G.営業日数,
営業日数11 = s14_11G == null ? 0 : s14_11G.営業日数,
営業日数12 = s14_12G == null ? 0 : s14_12G.営業日数,
//営業日数合計 = s14Group13 == null ? 0 : s14Group13.Select(c => c.営業日数).Sum(),
稼働日数1 = s14_1G.稼動日数 == null ? 0 : s14_1G.稼動日数,
稼働日数2 = s14_2G.稼動日数 == null ? 0 : s14_2G.稼動日数,
稼働日数3 = s14_3G.稼動日数 == null ? 0 : s14_3G.稼動日数,
稼働日数4 = s14_4G.稼動日数 == null ? 0 : s14_4G.稼動日数,
稼働日数5 = s14_5G.稼動日数 == null ? 0 : s14_5G.稼動日数,
稼働日数6 = s14_6G.稼動日数 == null ? 0 : s14_6G.稼動日数,
稼働日数7 = s14_7G.稼動日数 == null ? 0 : s14_7G.稼動日数,
稼働日数8 = s14_8G.稼動日数 == null ? 0 : s14_8G.稼動日数,
稼働日数9 = s14_9G.稼動日数 == null ? 0 : s14_9G.稼動日数,
稼働日数10 = s14_10G.稼動日数 == null ? 0 : s14_10G.稼動日数,
稼働日数11 = s14_11G.稼動日数 == null ? 0 : s14_11G.稼動日数,
稼働日数12 = s14_12G.稼動日数 == null ? 0 : s14_12G.稼動日数,
//稼働日数合計 = s14Group13 == null ? 0 : s14Group13.Select(c => c.稼動日数).Sum(),
////一時的に日数挿入
//前年対比1 = s14_1G.稼動日数 == null ? 0 : s14_1G.稼動日数,
//前年対比2 = s14Group.Where(c => c.集計年月 == 前年年月2).Select(c => c.稼動日数).FirstOrDefault(),
//前年対比3 = s14Group.Where(c => c.集計年月 == 前年年月3).Select(c => c.稼動日数).FirstOrDefault(),
//前年対比4 = s14Group.Where(c => c.集計年月 == 前年年月4).Select(c => c.稼動日数).FirstOrDefault(),
//前年対比5 = s14Group.Where(c => c.集計年月 == 前年年月5).Select(c => c.稼動日数).FirstOrDefault(),
//前年対比6 = s14Group.Where(c => c.集計年月 == 前年年月6).Select(c => c.稼動日数).FirstOrDefault(),
//前年対比7 = s14Group.Where(c => c.集計年月 == 前年年月7).Select(c => c.稼動日数).FirstOrDefault(),
//前年対比8 = s14Group.Where(c => c.集計年月 == 前年年月8).Select(c => c.稼動日数).FirstOrDefault(),
//前年対比9 = s14Group.Where(c => c.集計年月 == 前年年月9).Select(c => c.稼動日数).FirstOrDefault(),
//前年対比10 = s14Group.Where(c => c.集計年月 == 前年年月10).Select(c => c.稼動日数).FirstOrDefault(),
//前年対比11 = s14Group.Where(c => c.集計年月 == 前年年月11).Select(c => c.稼動日数).FirstOrDefault(),
//前年対比12 = s14Group.Where(c => c.集計年月 == 前年年月12).Select(c => c.稼動日数).FirstOrDefault(),
////一時的に日数挿入
//前月対比1 = s14Group.Where(c => c.集計年月 == 前月年月1).Select(c => c.稼動日数).FirstOrDefault(),
//前月対比2 = s14Group.Where(c => c.集計年月 == 前月年月2).Select(c => c.稼動日数).FirstOrDefault(),
//前月対比3 = s14Group.Where(c => c.集計年月 == 前月年月3).Select(c => c.稼動日数).FirstOrDefault(),
//前月対比4 = s14Group.Where(c => c.集計年月 == 前月年月4).Select(c => c.稼動日数).FirstOrDefault(),
//前月対比5 = s14Group.Where(c => c.集計年月 == 前月年月5).Select(c => c.稼動日数).FirstOrDefault(),
//前月対比6 = s14Group.Where(c => c.集計年月 == 前月年月6).Select(c => c.稼動日数).FirstOrDefault(),
//前月対比7 = s14Group.Where(c => c.集計年月 == 前月年月7).Select(c => c.稼動日数).FirstOrDefault(),
//前月対比8 = s14Group.Where(c => c.集計年月 == 前月年月8).Select(c => c.稼動日数).FirstOrDefault(),
//前月対比9 = s14Group.Where(c => c.集計年月 == 前月年月9).Select(c => c.稼動日数).FirstOrDefault(),
//前月対比10 = s14Group.Where(c => c.集計年月 == 前月年月10).Select(c => c.稼動日数).FirstOrDefault(),
//前月対比11 = s14Group.Where(c => c.集計年月 == 前月年月11).Select(c => c.稼動日数).FirstOrDefault(),
//前月対比12 = s14Group.Where(c => c.集計年月 == 前月年月12).Select(c => c.稼動日数).FirstOrDefault(),
コードFrom = p車輌From,
コードTo = p車輌To,
ピックアップ指定 = s車輌List,
//開始日付 = 開始年月1,
//終了日付 = 終了年月12,
}).ToList();
var query_2 = (from m05 in context.M05_CAR.Where(q => q.削除日付 == null)
join s14_1 in context.S14_CAR.Where(q => q.集計年月 == 前年年月1) on m05.車輌KEY equals s14_1.車輌KEY into s14Group1
from s14_1G in s14Group1.DefaultIfEmpty()
join s14_2 in context.S14_CAR.Where(q => q.集計年月 == 前年年月2) on m05.車輌KEY equals s14_2.車輌KEY into s14Group2
from s14_2G in s14Group2.DefaultIfEmpty()
join s14_3 in context.S14_CAR.Where(q => q.集計年月 == 前年年月3) on m05.車輌KEY equals s14_3.車輌KEY into s14Group3
from s14_3G in s14Group3.DefaultIfEmpty()
join s14_4 in context.S14_CAR.Where(q => q.集計年月 == 前年年月4) on m05.車輌KEY equals s14_4.車輌KEY into s14Group4
from s14_4G in s14Group4.DefaultIfEmpty()
join s14_5 in context.S14_CAR.Where(q => q.集計年月 == 前年年月5) on m05.車輌KEY equals s14_5.車輌KEY into s14Group5
from s14_5G in s14Group5.DefaultIfEmpty()
join s14_6 in context.S14_CAR.Where(q => q.集計年月 == 前年年月6) on m05.車輌KEY equals s14_6.車輌KEY into s14Group6
from s14_6G in s14Group6.DefaultIfEmpty()
join s14_7 in context.S14_CAR.Where(q => q.集計年月 == 前年年月7) on m05.車輌KEY equals s14_7.車輌KEY into s14Group7
from s14_7G in s14Group7.DefaultIfEmpty()
join s14_8 in context.S14_CAR.Where(q => q.集計年月 == 前年年月8) on m05.車輌KEY equals s14_8.車輌KEY into s14Group8
from s14_8G in s14Group8.DefaultIfEmpty()
join s14_9 in context.S14_CAR.Where(q => q.集計年月 == 前年年月9) on m05.車輌KEY equals s14_9.車輌KEY into s14Group9
from s14_9G in s14Group9.DefaultIfEmpty()
join s14_10 in context.S14_CAR.Where(q => q.集計年月 == 前年年月10) on m05.車輌KEY equals s14_10.車輌KEY into s14Group10
from s14_10G in s14Group10.DefaultIfEmpty()
join s14_11 in context.S14_CAR.Where(q => q.集計年月 == 前年年月11) on m05.車輌KEY equals s14_11.車輌KEY into s14Group11
from s14_11G in s14Group11.DefaultIfEmpty()
join s14_12 in context.S14_CAR.Where(q => q.集計年月 == 前年年月12) on m05.車輌KEY equals s14_12.車輌KEY into s14Group12
from s14_12G in s14Group12.DefaultIfEmpty()
// join s14_13 in context.S14_CAR.Where(q => q.集計年月 >= 前年年月1 && q.集計年月 <= 前年年月12) on m05.車輌KEY equals s14_13.車輌KEY into s14Group13
select new SRY24010_Member1
{
順序ID = 1,
車輌ID = m05.車輌ID,
車輌名 = m05.車輌番号,
月1 = s年月1,
月2 = s年月2,
月3 = s年月3,
月4 = s年月4,
月5 = s年月5,
月6 = s年月6,
月7 = s年月7,
月8 = s年月8,
月9 = s年月9,
月10 = s年月10,
月11 = s年月11,
月12 = s年月12,
//一時的に日数挿入
前年対比1 = s14_1G.稼動日数 == null ? 0 : s14_1G.稼動日数,
前年対比2 = s14_2G.稼動日数 == null ? 0 : s14_2G.稼動日数,
前年対比3 = s14_3G.稼動日数 == null ? 0 : s14_3G.稼動日数,
前年対比4 = s14_4G.稼動日数 == null ? 0 : s14_4G.稼動日数,
前年対比5 = s14_5G.稼動日数 == null ? 0 : s14_5G.稼動日数,
前年対比6 = s14_6G.稼動日数 == null ? 0 : s14_6G.稼動日数,
前年対比7 = s14_7G.稼動日数 == null ? 0 : s14_7G.稼動日数,
前年対比8 = s14_8G.稼動日数 == null ? 0 : s14_8G.稼動日数,
前年対比9 = s14_9G.稼動日数 == null ? 0 : s14_9G.稼動日数,
前年対比10 = s14_10G.稼動日数 == null ? 0 : s14_10G.稼動日数,
前年対比11 = s14_11G.稼動日数 == null ? 0 : s14_11G.稼動日数,
前年対比12 = s14_12G.稼動日数 == null ? 0 : s14_12G.稼動日数,
//前年対比合計 = s14Group13 == null ? 0 : s14Group13.Select(c => c.稼動日数).Sum(),
コードFrom = p車輌From,
コードTo = p車輌To,
ピックアップ指定 = s車輌List,
//開始日付 = 開始年月1,
//終了日付 = 終了年月12,
}).ToList();
var query_3 = (from m05 in context.M05_CAR.Where(q => q.削除日付 == null)
join s14_1 in context.S14_CAR.Where(q => q.集計年月 == 前月年月1) on m05.車輌KEY equals s14_1.車輌KEY into s14Group1
from s14_1G in s14Group1.DefaultIfEmpty()
join s14_2 in context.S14_CAR.Where(q => q.集計年月 == 前月年月2) on m05.車輌KEY equals s14_2.車輌KEY into s14Group2
from s14_2G in s14Group2.DefaultIfEmpty()
join s14_3 in context.S14_CAR.Where(q => q.集計年月 == 前月年月3) on m05.車輌KEY equals s14_3.車輌KEY into s14Group3
from s14_3G in s14Group3.DefaultIfEmpty()
join s14_4 in context.S14_CAR.Where(q => q.集計年月 == 前月年月4) on m05.車輌KEY equals s14_4.車輌KEY into s14Group4
from s14_4G in s14Group4.DefaultIfEmpty()
join s14_5 in context.S14_CAR.Where(q => q.集計年月 == 前月年月5) on m05.車輌KEY equals s14_5.車輌KEY into s14Group5
from s14_5G in s14Group5.DefaultIfEmpty()
join s14_6 in context.S14_CAR.Where(q => q.集計年月 == 前月年月6) on m05.車輌KEY equals s14_6.車輌KEY into s14Group6
from s14_6G in s14Group6.DefaultIfEmpty()
join s14_7 in context.S14_CAR.Where(q => q.集計年月 == 前月年月7) on m05.車輌KEY equals s14_7.車輌KEY into s14Group7
from s14_7G in s14Group7.DefaultIfEmpty()
join s14_8 in context.S14_CAR.Where(q => q.集計年月 == 前月年月8) on m05.車輌KEY equals s14_8.車輌KEY into s14Group8
from s14_8G in s14Group8.DefaultIfEmpty()
join s14_9 in context.S14_CAR.Where(q => q.集計年月 == 前月年月9) on m05.車輌KEY equals s14_9.車輌KEY into s14Group9
from s14_9G in s14Group9.DefaultIfEmpty()
join s14_10 in context.S14_CAR.Where(q => q.集計年月 == 前月年月10) on m05.車輌KEY equals s14_10.車輌KEY into s14Group10
from s14_10G in s14Group10.DefaultIfEmpty()
join s14_11 in context.S14_CAR.Where(q => q.集計年月 == 前月年月11) on m05.車輌KEY equals s14_11.車輌KEY into s14Group11
from s14_11G in s14Group11.DefaultIfEmpty()
join s14_12 in context.S14_CAR.Where(q => q.集計年月 == 前月年月12) on m05.車輌KEY equals s14_12.車輌KEY into s14Group12
from s14_12G in s14Group12.DefaultIfEmpty()
// join s14_13 in context.S14_CAR.Where(q => q.集計年月 >= 前月年月1 && q.集計年月 <= 前月年月12) on m05.車輌KEY equals s14_13.車輌KEY into s14Group13
select new SRY24010_Member1
{
順序ID = 1,
車輌ID = m05.車輌ID,
車輌名 = m05.車輌番号,
月1 = s年月1,
月2 = s年月2,
月3 = s年月3,
月4 = s年月4,
月5 = s年月5,
月6 = s年月6,
月7 = s年月7,
月8 = s年月8,
月9 = s年月9,
月10 = s年月10,
月11 = s年月11,
月12 = s年月12,
//一時的に日数挿入
前月対比1 = s14_1G.稼動日数 == null ? 0 : s14_1G.稼動日数,
前月対比2 = s14_2G.稼動日数 == null ? 0 : s14_2G.稼動日数,
前月対比3 = s14_3G.稼動日数 == null ? 0 : s14_3G.稼動日数,
前月対比4 = s14_4G.稼動日数 == null ? 0 : s14_4G.稼動日数,
前月対比5 = s14_5G.稼動日数 == null ? 0 : s14_5G.稼動日数,
前月対比6 = s14_6G.稼動日数 == null ? 0 : s14_6G.稼動日数,
前月対比7 = s14_7G.稼動日数 == null ? 0 : s14_7G.稼動日数,
前月対比8 = s14_8G.稼動日数 == null ? 0 : s14_8G.稼動日数,
前月対比9 = s14_9G.稼動日数 == null ? 0 : s14_9G.稼動日数,
前月対比10 = s14_10G.稼動日数 == null ? 0 : s14_10G.稼動日数,
前月対比11 = s14_11G.稼動日数 == null ? 0 : s14_11G.稼動日数,
前月対比12 = s14_12G.稼動日数 == null ? 0 : s14_12G.稼動日数,
// 前月対比合計 = s14Group13 == null ? 0 : s14Group13.Select(c => c.稼動日数).Sum(),
コードFrom = p車輌From,
コードTo = p車輌To,
ピックアップ指定 = s車輌List,
//開始日付 = 開始年月1,
//終了日付 = 終了年月12,
}).ToList();
var query = (from q1 in query_1
join q2 in query_2 on q1.車輌ID equals q2.車輌ID into q2Group
from q2g in q2Group.DefaultIfEmpty()
join q3 in query_3 on q1.車輌ID equals q3.車輌ID into q3Group
from q3g in q3Group.DefaultIfEmpty()
select new SRY24010_Member1
{
順序ID = 1,
車輌ID = q1.車輌ID,
車輌名 = q1.車輌名,
月1 = q1.月1,
月2 = q1.月2,
月3 = q1.月3,
月4 = q1.月4,
月5 = q1.月5,
月6 = q1.月6,
月7 = q1.月7,
月8 = q1.月8,
月9 = q1.月9,
月10 = q1.月10,
月11 = q1.月11,
月12 = q1.月12,
営業日数1 = q1.営業日数1,
営業日数2 = q1.営業日数2,
営業日数3 = q1.営業日数3,
営業日数4 = q1.営業日数4,
営業日数5 = q1.営業日数5,
営業日数6 = q1.営業日数6,
営業日数7 = q1.営業日数7,
営業日数8 = q1.営業日数8,
営業日数9 = q1.営業日数9,
営業日数10 = q1.営業日数10,
営業日数11 = q1.営業日数11,
営業日数12 = q1.営業日数12,
営業日数合計 = q1.営業日数1 + q1.営業日数2 + q1.営業日数3 + q1.営業日数4 + q1.営業日数5 + q1.営業日数6 + q1.営業日数7 + q1.営業日数8 + q1.営業日数9 + q1.営業日数10 + q1.営業日数11 + q1.営業日数12,
稼働日数1 = q1.稼働日数1,
稼働日数2 = q1.稼働日数2,
稼働日数3 = q1.稼働日数3,
稼働日数4 = q1.稼働日数4,
稼働日数5 = q1.稼働日数5,
稼働日数6 = q1.稼働日数6,
稼働日数7 = q1.稼働日数7,
稼働日数8 = q1.稼働日数8,
稼働日数9 = q1.稼働日数9,
稼働日数10 = q1.稼働日数10,
稼働日数11 = q1.稼働日数11,
稼働日数12 = q1.稼働日数12,
稼働日数合計 = q1.稼働日数1 + q1.稼働日数2 + q1.稼働日数3 + q1.稼働日数4 + q1.稼働日数5 + q1.稼働日数6 + q1.稼働日数7 + q1.稼働日数8 + q1.稼働日数9 + q1.稼働日数10 + q1.稼働日数11 + q1.稼働日数12,
前年対比1 = q2g.前年対比1,
前年対比2 = q2g.前年対比2,
前年対比3 = q2g.前年対比3,
前年対比4 = q2g.前年対比4,
前年対比5 = q2g.前年対比5,
前年対比6 = q2g.前年対比6,
前年対比7 = q2g.前年対比7,
前年対比8 = q2g.前年対比8,
前年対比9 = q2g.前年対比9,
前年対比10 = q2g.前年対比10,
前年対比11 = q2g.前年対比11,
前年対比12 = q2g.前年対比12,
前年対比合計 = q2g.前年対比1 + q2g.前年対比2 + q2g.前年対比3 + q2g.前年対比4 + q2g.前年対比5 + q2g.前年対比6 + q2g.前年対比7 + q2g.前年対比8 + q2g.前年対比9 + q2g.前年対比10 + q2g.前年対比11 + q2g.前年対比12,
前月対比1 = q3g.前月対比1,
前月対比2 = q3g.前月対比2,
前月対比3 = q3g.前月対比3,
前月対比4 = q3g.前月対比4,
前月対比5 = q3g.前月対比5,
前月対比6 = q3g.前月対比6,
前月対比7 = q3g.前月対比7,
前月対比8 = q3g.前月対比8,
前月対比9 = q3g.前月対比9,
前月対比10 = q3g.前月対比10,
前月対比11 = q3g.前月対比11,
前月対比12 = q3g.前月対比12,
前月対比合計 = q3g.前月対比1 + q3g.前月対比2 + q3g.前月対比3 + q3g.前月対比4 + q3g.前月対比5 + q3g.前月対比6 + q3g.前月対比7 + q3g.前月対比8 + q3g.前月対比9 + q3g.前月対比10 + q3g.前月対比11 + q3g.前月対比12,
コードFrom = p車輌From,
コードTo = p車輌To,
ピックアップ指定 = s車輌List,
//開始日付 = 開始年月1,
//終了日付 = 終了年月12,
}).AsQueryable();
//query.AsEnumerable().Where(q => q.月1 = )
#endregion
//SRY24010_DATASET dset = new SRY24010_DATASET()
//{
// 売上構成グラフ = query1,
// 得意先上位グラフ = query2,
// 傭車先上位グラフ = query3,
// 損益分岐点グラフ = query4,
//};
var query2 = (from q in query
group q by q.車輌ID into qGroup
select new SRY24010_Member2
{
車輌ID = qGroup.Key,
総売上 = qGroup.Select(q => (q.稼働日数1 + q.稼働日数2 + q.稼働日数3 + q.稼働日数4 + q.稼働日数5 + q.稼働日数6 + q.稼働日数7 + q.稼働日数8 + q.稼働日数9 + q.稼働日数10 + q.稼働日数11 + q.稼働日数12)).Sum(),
}).AsQueryable();
query2 = query2.Where(q => q.総売上 != 0 && q.総売上 != null);
query = (from q in query
let qqlet = from qq in query2 select qq.車輌ID
where qqlet.Contains(q.車輌ID)
select q).AsQueryable();
#region 車輌指定
var query3 = query;
//車輌From処理 Min値
if (!string.IsNullOrEmpty(p車輌From))
{
int i車輌From = AppCommon.IntParse(p車輌From);
query = query.Where(c => c.車輌ID >= i車輌From || c.車輌ID == 999999999);
}
//車輌To処理 Max値
if (!string.IsNullOrEmpty(p車輌To))
{
int i車輌TO = AppCommon.IntParse(p車輌To);
query = query.Where(c => c.車輌ID <= i車輌TO || c.車輌ID == 999999999);
}
if (p車輌List.Length > 0)
{
if ((string.IsNullOrEmpty(p車輌From)) && (string.IsNullOrEmpty(p車輌To)))
{
query = query3.Where(q => p車輌List.Contains(q.車輌ID) || q.車輌ID == 999999999);
}
else
{
query = query.Union(query3.Where(q => p車輌List.Contains(q.車輌ID) || q.車輌ID == 999999999));
}
}
query = query.Distinct();
#endregion
query = query.OrderBy(q => q.車輌ID);
var result = query.ToList();
foreach (SRY24010_Member1 row in result)
{
row.稼働率1 = row.営業日数1 == 0 ? 0 : Math.Round(AppCommon.DecimalParse((AppCommon.DecimalParse(row.稼働日数1.ToString()) / (row.営業日数1) * 100).ToString()), 1, MidpointRounding.AwayFromZero);
row.稼働率2 = row.営業日数2 == 0 ? 0 : Math.Round(AppCommon.DecimalParse((AppCommon.DecimalParse(row.稼働日数2.ToString()) / (row.営業日数2) * 100).ToString()), 1, MidpointRounding.AwayFromZero);
row.稼働率3 = row.営業日数3 == 0 ? 0 : Math.Round(AppCommon.DecimalParse((AppCommon.DecimalParse(row.稼働日数3.ToString()) / (row.営業日数3) * 100).ToString()), 1, MidpointRounding.AwayFromZero);
row.稼働率4 = row.営業日数4 == 0 ? 0 : Math.Round(AppCommon.DecimalParse((AppCommon.DecimalParse(row.稼働日数4.ToString()) / (row.営業日数4) * 100).ToString()), 1, MidpointRounding.AwayFromZero);
row.稼働率5 = row.営業日数5 == 0 ? 0 : Math.Round(AppCommon.DecimalParse((AppCommon.DecimalParse(row.稼働日数5.ToString()) / (row.営業日数5) * 100).ToString()), 1, MidpointRounding.AwayFromZero);
row.稼働率6 = row.営業日数6 == 0 ? 0 : Math.Round(AppCommon.DecimalParse((AppCommon.DecimalParse(row.稼働日数6.ToString()) / (row.営業日数6) * 100).ToString()), 1, MidpointRounding.AwayFromZero);
row.稼働率7 = row.営業日数7 == 0 ? 0 : Math.Round(AppCommon.DecimalParse((AppCommon.DecimalParse(row.稼働日数7.ToString()) / (row.営業日数7) * 100).ToString()), 1, MidpointRounding.AwayFromZero);
row.稼働率8 = row.営業日数8 == 0 ? 0 : Math.Round(AppCommon.DecimalParse((AppCommon.DecimalParse(row.稼働日数8.ToString()) / (row.営業日数8) * 100).ToString()), 1, MidpointRounding.AwayFromZero);
row.稼働率9 = row.営業日数9 == 0 ? 0 : Math.Round(AppCommon.DecimalParse((AppCommon.DecimalParse(row.稼働日数9.ToString()) / (row.営業日数9) * 100).ToString()), 1, MidpointRounding.AwayFromZero);
row.稼働率10 = row.営業日数10 == 0 ? 0 : Math.Round(AppCommon.DecimalParse((AppCommon.DecimalParse(row.稼働日数10.ToString()) / (row.営業日数10) * 100).ToString()), 1, MidpointRounding.AwayFromZero);
row.稼働率11 = row.営業日数11 == 0 ? 0 : Math.Round(AppCommon.DecimalParse((AppCommon.DecimalParse(row.稼働日数11.ToString()) / (row.営業日数11) * 100).ToString()), 1, MidpointRounding.AwayFromZero);
row.稼働率12 = row.営業日数12 == 0 ? 0 : Math.Round(AppCommon.DecimalParse((AppCommon.DecimalParse(row.稼働日数12.ToString()) / (row.営業日数12) * 100).ToString()), 1, MidpointRounding.AwayFromZero);
row.稼働率合計 = row.営業日数合計 == 0 ? 0 : Math.Round(AppCommon.DecimalParse((AppCommon.DecimalParse(row.稼働日数合計.ToString()) / (row.営業日数合計) * 100).ToString()), 1, MidpointRounding.AwayFromZero);
row.前年対比1 = row.前年対比1 == 0 ? 0 : Math.Round(AppCommon.DecimalParse((AppCommon.DecimalParse(row.稼働日数1.ToString()) / (row.前年対比1) * 100).ToString()), 1, MidpointRounding.AwayFromZero);
row.前年対比2 = row.前年対比2 == 0 ? 0 : Math.Round(AppCommon.DecimalParse((AppCommon.DecimalParse(row.稼働日数2.ToString()) / (row.前年対比2) * 100).ToString()), 1, MidpointRounding.AwayFromZero);
row.前年対比3 = row.前年対比3 == 0 ? 0 : Math.Round(AppCommon.DecimalParse((AppCommon.DecimalParse(row.稼働日数3.ToString()) / (row.前年対比3) * 100).ToString()), 1, MidpointRounding.AwayFromZero);
row.前年対比4 = row.前年対比4 == 0 ? 0 : Math.Round(AppCommon.DecimalParse((AppCommon.DecimalParse(row.稼働日数4.ToString()) / (row.前年対比4) * 100).ToString()), 1, MidpointRounding.AwayFromZero);
row.前年対比5 = row.前年対比5 == 0 ? 0 : Math.Round(AppCommon.DecimalParse((AppCommon.DecimalParse(row.稼働日数5.ToString()) / (row.前年対比5) * 100).ToString()), 1, MidpointRounding.AwayFromZero);
row.前年対比6 = row.前年対比6 == 0 ? 0 : Math.Round(AppCommon.DecimalParse((AppCommon.DecimalParse(row.稼働日数6.ToString()) / (row.前年対比6) * 100).ToString()), 1, MidpointRounding.AwayFromZero);
row.前年対比7 = row.前年対比7 == 0 ? 0 : Math.Round(AppCommon.DecimalParse((AppCommon.DecimalParse(row.稼働日数7.ToString()) / (row.前年対比7) * 100).ToString()), 1, MidpointRounding.AwayFromZero);
row.前年対比8 = row.前年対比8 == 0 ? 0 : Math.Round(AppCommon.DecimalParse((AppCommon.DecimalParse(row.稼働日数8.ToString()) / (row.前年対比8) * 100).ToString()), 1, MidpointRounding.AwayFromZero);
row.前年対比9 = row.前年対比9 == 0 ? 0 : Math.Round(AppCommon.DecimalParse((AppCommon.DecimalParse(row.稼働日数9.ToString()) / (row.前年対比9) * 100).ToString()), 1, MidpointRounding.AwayFromZero);
row.前年対比10 = row.前年対比10 == 0 ? 0 : Math.Round(AppCommon.DecimalParse((AppCommon.DecimalParse(row.稼働日数10.ToString()) / (row.前年対比10) * 100).ToString()), 1, MidpointRounding.AwayFromZero);
row.前年対比11 = row.前年対比11 == 0 ? 0 : Math.Round(AppCommon.DecimalParse((AppCommon.DecimalParse(row.稼働日数11.ToString()) / (row.前年対比11) * 100).ToString()), 1, MidpointRounding.AwayFromZero);
row.前年対比12 = row.前年対比12 == 0 ? 0 : Math.Round(AppCommon.DecimalParse((AppCommon.DecimalParse(row.稼働日数12.ToString()) / (row.前年対比12) * 100).ToString()), 1, MidpointRounding.AwayFromZero);
row.前年対比合計 = row.前年対比合計 == 0 ? 0 : Math.Round(AppCommon.DecimalParse((AppCommon.DecimalParse(row.稼働日数合計.ToString()) / (row.前年対比合計) * 100).ToString()), 1, MidpointRounding.AwayFromZero);
row.前月対比1 = row.前月対比1 == 0 ? 0 : Math.Round(AppCommon.DecimalParse(((row.稼働日数1) / (row.前月対比1) * 100).ToString()), 1, MidpointRounding.AwayFromZero);
row.前月対比2 = row.前月対比2 == 0 ? 0 : Math.Round(AppCommon.DecimalParse(((row.稼働日数2) / (row.前月対比2) * 100).ToString()), 1, MidpointRounding.AwayFromZero);
row.前月対比3 = row.前月対比3 == 0 ? 0 : Math.Round(AppCommon.DecimalParse(((row.稼働日数3) / (row.前月対比3) * 100).ToString()), 1, MidpointRounding.AwayFromZero);
row.前月対比4 = row.前月対比4 == 0 ? 0 : Math.Round(AppCommon.DecimalParse(((row.稼働日数4) / (row.前月対比4) * 100).ToString()), 1, MidpointRounding.AwayFromZero);
row.前月対比5 = row.前月対比5 == 0 ? 0 : Math.Round(AppCommon.DecimalParse(((row.稼働日数5) / (row.前月対比5) * 100).ToString()), 1, MidpointRounding.AwayFromZero);
row.前月対比6 = row.前月対比6 == 0 ? 0 : Math.Round(AppCommon.DecimalParse(((row.稼働日数6) / (row.前月対比6) * 100).ToString()), 1, MidpointRounding.AwayFromZero);
row.前月対比7 = row.前月対比7 == 0 ? 0 : Math.Round(AppCommon.DecimalParse(((row.稼働日数7) / (row.前月対比7) * 100).ToString()), 1, MidpointRounding.AwayFromZero);
row.前月対比8 = row.前月対比8 == 0 ? 0 : Math.Round(AppCommon.DecimalParse(((row.稼働日数8) / (row.前月対比8) * 100).ToString()), 1, MidpointRounding.AwayFromZero);
row.前月対比9 = row.前月対比9 == 0 ? 0 : Math.Round(AppCommon.DecimalParse(((row.稼働日数9) / (row.前月対比9) * 100).ToString()), 1, MidpointRounding.AwayFromZero);
row.前月対比10 = row.前月対比10 == 0 ? 0 : Math.Round(AppCommon.DecimalParse(((row.稼働日数10) / (row.前月対比10) * 100).ToString()), 1, MidpointRounding.AwayFromZero);
row.前月対比11 = row.前月対比11 == 0 ? 0 : Math.Round(AppCommon.DecimalParse(((row.稼働日数11) / (row.前月対比11) * 100).ToString()), 1, MidpointRounding.AwayFromZero);
row.前月対比12 = row.前月対比12 == 0 ? 0 : Math.Round(AppCommon.DecimalParse(((row.稼働日数12) / (row.前月対比12) * 100).ToString()), 1, MidpointRounding.AwayFromZero);
row.前月対比合計 = row.前月対比合計 == 0 ? 0 : Math.Round(AppCommon.DecimalParse(((row.稼働日数合計) / (row.前月対比合計) * 100).ToString()), 1, MidpointRounding.AwayFromZero);
}
;
return(result);
}
catch (Exception ex)
{
throw ex;
}
}
}
/// <summary>
/// SRY20010 得意先売上日計表CSV
/// </summary>
/// <param name="p商品ID">得意先コード</param>
/// <returns>S02</returns>
public List <SRY20010_Member_CSV> GetDataList_CSV(string p車輌From, string p車輌To, int?[] i車輌List, string p作成締日, string p作成年, string p作成月, DateTime p集計期間To, DateTime p集計期間From, int?p自社部門コード, string 部門名)
{
using (TRAC3Entities context = new TRAC3Entities(CommonData.TRAC3_GetConnectionString()))
{
//支払先指定 表示用
string 支払先指定表示 = string.Empty;
//query格納LIST
List <SRY20010_Member_CSV> retList = new List <SRY20010_Member_CSV>();
//日付格納LIST
List <SRY20010_Member_day> retList_day = new List <SRY20010_Member_day>();
int i作成年月 = p作成月.Length == 1 ? Convert.ToInt32(p作成年 + "0" + p作成月) : Convert.ToInt32(p作成年 + p作成月);
//日付取得LIST
for (DateTime dDate = p集計期間From; dDate <= p集計期間To; dDate = dDate.AddDays(1))
{
retList_day.Add(new SRY20010_Member_day()
{
日付 = dDate
});
}
context.Connection.Open();
//前回データ取得
var car = (from m05 in context.M05_CAR.Where(c => c.削除日付 == null)
join m06 in context.M06_SYA on m05.車種ID equals m06.車種ID into m06Group
from m06g in m06Group.DefaultIfEmpty()
join m13 in context.M13_MOK.Where(c => c.年月 == i作成年月) on m05.車輌KEY equals m13.車輌KEY into m13Group
from m13g in m13Group.DefaultIfEmpty()
join t03 in context.T03_KTRN.Where(t01 => t01.経費項目ID == 401 && t01.経費発生日 < p集計期間From) on m05.車輌KEY equals t03.車輌ID into t03Group
from t03g in t03Group.DefaultIfEmpty()
select new SRY20010_Member_CAR
{
車輌ID = m05.車輌ID,
車輌KEY = m05.車輌KEY,
車輌番号 = m05.車輌番号,
車種名 = m06g.車種名,
自社部門コード = m05.自社部門ID,
目標燃費 = m13g.目標燃費,
前回メーター = t03g.メーター,
}).AsQueryable();
var carMax = (from cars in car
group cars by new { cars.車輌ID, cars.車輌KEY, cars.車輌番号, cars.車種名, cars.自社部門コード, cars.目標燃費 } into Group
select new
{
車輌ID = Group.Key.車輌ID,
車輌KEY = Group.Key.車輌KEY,
車輌番号 = Group.Key.車輌番号,
車種名 = Group.Key.車種名,
自社部門コード = Group.Key.自社部門コード,
目標燃費 = Group.Key.目標燃費,
前回メーター = Group.Max(c => c.前回メーター),
}).AsQueryable();
//締日集計処理
var query = (from y01 in retList_day
from m05 in car
join t03 in context.T03_KTRN.Where(t01 => t01.経費発生日 >= p集計期間From && t01.経費発生日 <= p集計期間To) on m05.車輌KEY equals t03.車輌ID into t03Group
where t03Group.Where(t03 => t03.車輌ID == m05.車輌KEY).Any() == true
orderby m05.車輌ID
select new SRY20010_Member_CSV
{
日付 = y01.日付,
車輌コード = m05.車輌ID,
車輌番号 = m05.車輌番号 == null ? "" : m05.車輌番号,
車種名 = m05.車種名 == null ? "" : m05.車種名,
給油時メーター = t03Group.Where(t03 => t03.経費発生日 == y01.日付 && t03.車輌ID == m05.車輌KEY).Min(t01 => t01.メーター) == null ? 0
: t03Group.Where(t03 => t03.経費発生日 == y01.日付 && t03.車輌ID == m05.車輌KEY).Min(t01 => t01.メーター),
目標燃費 = m05.目標燃費 == null ? 0 : m05.目標燃費,
前回KM = m05.前回メーター,
燃料L = t03Group.Where(t03 => t03.経費発生日 == y01.日付 && t03.経費項目ID == 401).Sum(t03 => t03.数量),
自社部門コード = m05.自社部門コード,
}).AsQueryable();
if (!(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 (p自社部門コード != null)
{
query = query.Where(c => c.自社部門コード == p自社部門コード);
}
if (i車輌List.Length > 0)
{
var intCause = i車輌List;
////締日集計処理
query = query.Union(from y01 in retList_day
from m05 in car
join t03 in context.T03_KTRN.Where(t01 => t01.経費発生日 >= p集計期間From && t01.経費発生日 <= p集計期間To) on m05.車輌KEY equals t03.車輌ID into t03Group
from Group in t03Group
where t03Group.Where(t03 => t03.車輌ID == m05.車輌KEY).Any() == true && intCause.Contains(m05.車輌ID)
orderby m05.車輌ID
select new SRY20010_Member_CSV
{
日付 = y01.日付,
車輌コード = m05.車輌ID,
車輌番号 = m05.車輌番号 == null ? "" : m05.車輌番号,
車種名 = m05.車種名 == "" ? "" : m05.車種名,
給油時メーター = t03Group.Where(t03 => t03.経費発生日 == y01.日付 && t03.車輌ID == m05.車輌KEY).Min(t01 => t01.メーター) == null ? 0
: t03Group.Where(t03 => t03.経費発生日 == y01.日付 && t03.車輌ID == m05.車輌KEY).Min(t01 => t01.メーター),
目標燃費 = m05.目標燃費 == null ? 0 : m05.目標燃費,
前回KM = m05.前回メーター,
燃料L = t03Group.Where(t03 => t03.経費発生日 == y01.日付 && t03.経費項目ID == 401).Sum(t03 => t03.数量),
自社部門コード = Group.自社部門ID,
});
if (p自社部門コード != null)
{
query = query.Where(c => c.自社部門コード == p自社部門コード);
}
}
}
else
{
//車輌範囲の指定が空の場合の処理
if (!string.IsNullOrEmpty(p車輌From))
{
int?i車輌FROM = AppCommon.IntParse(p車輌From);
query = query.Where(c => c.車輌コード >= i車輌FROM);
}
if (!string.IsNullOrEmpty(p車輌To))
{
int?i車輌TO = AppCommon.IntParse(p車輌To);
query = query.Where(c => c.車輌コード <= i車輌TO);
}
//if (string.IsNullOrEmpty(p車輌From) && string.IsNullOrEmpty(p車輌To))
//{
// query = query.Where(c => c.車輌コード >= int.MinValue && c.車輌コード <= int.MaxValue);
//}
if (p自社部門コード != null)
{
query = query.Where(c => c.自社部門コード == p自社部門コード);
}
}
//支払先指定の表示
if (i車輌List.Length > 0)
{
for (int it = 0; it < query.Count(); it++)
{
支払先指定表示 = 支払先指定表示 + i車輌List[it].ToString();
if (it < i車輌List.Length)
{
if (it == i車輌List.Length - 1)
{
break;
}
支払先指定表示 = 支払先指定表示 + ",";
}
if (i車輌List.Length == 1)
{
break;
}
}
}
query = query.Distinct();
query.OrderBy(q => new { q.車輌コード, q.日付 });
List <SRY20010_Member_CSV> queryLIST = query.ToList();
queryLIST = query.ToList();
int? i車輌コード = 0;
decimal?i走行キロ = 0;
for (int i = 0; i < queryLIST.Count; i++)
{
if (queryLIST[i].車輌コード != i車輌コード)
{
i走行キロ = queryLIST[i].前回KM;
}
i車輌コード = queryLIST[i].車輌コード;
if (queryLIST[i].給油時メーター != null && queryLIST[i].給油時メーター != 0)
{
queryLIST[i].走行KM = queryLIST[i].給油時メーター - (i走行キロ == null ? 0 : i走行キロ);
i走行キロ = queryLIST[i].給油時メーター;
}
if (queryLIST[i].燃料L != null && queryLIST[i].燃料L != 0 && queryLIST[i].走行KM != null)
{
queryLIST[i].燃費 = (queryLIST[i].走行KM / queryLIST[i].燃料L);
if (queryLIST[i].燃費 == null)
{
queryLIST[i].燃費 = Math.Round(AppCommon.DecimalParse((queryLIST[i].走行KM / queryLIST[i].燃料L).ToString()), 0, MidpointRounding.AwayFromZero);
}
queryLIST[i].対目標増減 = queryLIST[i].燃費 - queryLIST[i].目標燃費;
}
}
return(queryLIST);
}
}
/// <summary>
/// SRY05010
/// </summary>
/// <param name="p商品ID">車輌コード</param>
/// <returns>T01</returns>
public List <SRY05010_Member> SYUKEI(string p車輌From, string p車輌To, int?[] i車輌List, int?p作成締日, DateTime d集計期間From, DateTime d集計期間To, int i集計年月, string s車輌List, int i営業日数, int i一般管理費, int i固定再計算, DateTime d集計年月)
{
using (TRAC3Entities context = new TRAC3Entities(CommonData.TRAC3_GetConnectionString()))
{
List <SRY05010_Member> retList = new List <SRY05010_Member>();
List <SRY05010_Date> retList2 = new List <SRY05010_Date>();
context.Connection.Open();
for (DateTime dDate = d集計期間From; dDate <= d集計期間To; dDate = dDate.AddDays(1))
{
retList2.Add(new SRY05010_Date()
{
日付 = dDate
});
}
DataTable dtTest = new DataTable();
dtTest.Columns.Add("日付", typeof(DateTime));
var query2 = (from d in retList2
from m05 in context.M05_CAR.Where(m05 => m05.削除日付 == null)
join t02 in context.T02_UTRN.Where(t02 => t02.出勤区分ID <= 4) on m05.車輌KEY equals t02.車輌KEY into t02Group
select new SRY05010_KADOU
{
車輌KEY = m05.車輌ID,
日付 = d.日付,
回数 = t02Group.Where(t02g => t02g.勤務開始日 <= d.日付 && t02g.勤務終了日 >= d.日付).Any() == true ? 1 : 0,
}).ToList();
//var query3 = (from m05 in context.M05_CAR
// join q in query2 on m05.車輌KEY equals q.車輌KEY into qGroup
// select new SRY05010_KADOU2
// {
// 車輌KEY = m05.車輌KEY,
// 回数 = qGroup.Where(qg => qg.車輌KEY == m05.車輌KEY).Sum(qg => qg.回数),
// }).AsQueryable();
var query = (from m05 in context.M05_CAR.Where(p => p.削除日付 == null)
join t01 in context.T01_TRN.Where(t01 => (t01.請求日付 >= d集計期間From && t01.請求日付 <= d集計期間To) && (t01.入力区分 != 3 || (t01.入力区分 == 3 && t01.明細行 != 1))) on m05.車輌KEY equals t01.車輌KEY into t01Group
from t01g in t01Group.DefaultIfEmpty()
join t02 in context.V_T02_UTRN.Where(t02 => t02.労務日 >= d集計期間From && t02.労務日 <= d集計期間To) on m05.車輌KEY equals t02.車輌KEY into t02Group
from t02g in t02Group.DefaultIfEmpty()
join t03 in context.T03_KTRN.Where(t03 => t03.経費発生日 >= d集計期間From && t03.経費発生日 <= d集計期間To) on m05.車輌KEY equals t03.車輌ID into t03Group
from t03g in t03Group.DefaultIfEmpty()
join s13 in context.S14_CAR.Where(s13 => s13.集計年月 == i集計年月) on m05.車輌KEY equals s13.車輌KEY into s13Group
from s13g in s13Group.DefaultIfEmpty()
join m04 in context.M04_DRV.Where(m04 => m04.削除日付 == null) on m05.乗務員KEY equals m04.乗務員KEY into m04Group
//join q in query2 on m05.車輌KEY equals q.車輌KEY into qGroup
select new SRY05010_Member
{
KEY = m05.車輌KEY,
コード = m05.車輌ID,
集計年月 = i集計年月,
登録年月日 = s13g.登録日時,
自社部門ID = m05.自社部門ID,
車種ID = m05.車種ID,
乗務員KEY = m04Group.Min(m04 => m04.乗務員KEY),
営業日数 = i営業日数,
稼働日数 = 0,
//稼働日数 = qGroup.Where(q => q.車輌KEY == m05.車輌KEY).Sum(q => q.回数),
走行KM = t02Group.Where(t02 => t02.車輌KEY == m05.車輌KEY).Sum(t02 => t02.走行ＫＭ),
実車KM = t02Group.Where(t02 => t02.車輌KEY == m05.車輌KEY).Sum(t02 => t02.実車ＫＭ),
輸送屯数 = t02Group.Where(t02 => t02.車輌KEY == m05.車輌KEY).Sum(t02 => t02.輸送屯数),
運送収入 = t01Group.Where(t01 => t01.車輌KEY == m05.車輌KEY).Sum(t01 => t01.売上金額 + t01.請求割増１ + t01.請求割増２ + t01.通行料),
燃料L = t03Group.Where(t03 => t03.車輌ID == m05.車輌KEY && t03.経費項目ID == 401).Sum(t03 => t03.数量),
一般管理費 = (t01Group.Where(t01 => t01.車輌KEY == m05.車輌KEY).Sum(t01 => (decimal)t01.売上金額 + (decimal)t01.請求割増１ + (decimal)t01.請求割増２ + (decimal)t01.通行料) * (decimal)i一般管理費) / 100,
拘束時間 = t02Group.Where(t02 => t02.車輌KEY == m05.車輌KEY).Sum(t02 => t02.拘束時間),
運転時間 = t02Group.Where(t02 => t02.車輌KEY == m05.車輌KEY).Sum(t02 => t02.運転時間),
高速時間 = t02Group.Where(t02 => t02.車輌KEY == m05.車輌KEY).Sum(t02 => t02.高速時間),
作業時間 = t02Group.Where(t02 => t02.車輌KEY == m05.車輌KEY).Sum(t02 => t02.作業時間),
待機時間 = t02Group.Where(t02 => t02.車輌KEY == m05.車輌KEY).Sum(t02 => t02.待機時間),
休憩時間 = t02Group.Where(t02 => t02.車輌KEY == m05.車輌KEY).Sum(t02 => t02.休憩時間),
残業時間 = t02Group.Where(t02 => t02.車輌KEY == m05.車輌KEY).Sum(t02 => t02.残業時間),
深夜時間 = t02Group.Where(t02 => t02.車輌KEY == m05.車輌KEY).Sum(t02 => t02.深夜時間),
}).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 m05 in context.M05_CAR.Where(p => p.削除日付 == null)
join t01 in context.T01_TRN.Where(t01 => (t01.請求日付 >= d集計期間From && t01.請求日付 <= d集計期間To) && (t01.入力区分 != 3 || (t01.入力区分 == 3 && t01.明細行 != 1))) on m05.車輌KEY equals t01.車輌KEY into t01Group
from t01g in t01Group.DefaultIfEmpty()
join t02 in context.V_T02_UTRN.Where(t02 => t02.労務日 >= d集計期間From && t02.労務日 <= d集計期間To) on m05.車輌KEY equals t02.車輌KEY into t02Group
from t02g in t02Group.DefaultIfEmpty()
join t03 in context.T03_KTRN.Where(t03 => t03.経費発生日 >= d集計期間From && t03.経費発生日 <= d集計期間To) on m05.車輌KEY equals t03.車輌ID into t03Group
from t03g in t03Group.DefaultIfEmpty()
join s13 in context.S14_CAR.Where(s13 => s13.集計年月 == i集計年月) on m05.車輌KEY equals s13.車輌KEY into s13Group
from s13g in s13Group.DefaultIfEmpty()
join m04 in context.M04_DRV.Where(m04 => m04.削除日付 == null) on m05.乗務員KEY equals m04.乗務員KEY into m04Group
//join q in query2 on m05.車輌KEY equals q.車輌KEY into qGroup
where intCause.Contains(m05.車輌ID)
select new SRY05010_Member
{
KEY = m05.車輌KEY,
コード = m05.車輌ID,
集計年月 = i集計年月,
登録年月日 = s13g.登録日時,
自社部門ID = m05.自社部門ID,
車種ID = m05.車種ID,
乗務員KEY = m04Group.Min(m04 => m04.乗務員KEY),
営業日数 = i営業日数,
稼働日数 = 0,
//稼働日数 = qGroup.Where(q => q.車輌KEY == m05.車輌KEY).Sum(q => q.回数),
走行KM = t02Group.Where(t02 => t02.車輌KEY == m05.車輌KEY).Sum(t02 => t02.走行ＫＭ),
実車KM = t02Group.Where(t02 => t02.車輌KEY == m05.車輌KEY).Sum(t02 => t02.実車ＫＭ),
輸送屯数 = t02Group.Where(t02 => t02.車輌KEY == m05.車輌KEY).Sum(t02 => t02.輸送屯数),
運送収入 = t01Group.Where(t01 => t01.車輌KEY == m05.車輌KEY).Sum(t01 => t01.売上金額 + t01.請求割増１ + t01.請求割増２ + t01.通行料),
燃料L = t03Group.Where(t03 => t03.車輌ID == m05.車輌KEY && t03.経費項目ID == 401).Sum(t03 => t03.数量),
一般管理費 = (t01Group.Where(t01 => t01.車輌KEY == m05.車輌KEY).Sum(t01 => (decimal)t01.売上金額 + (decimal)t01.請求割増１ + (decimal)t01.請求割増２ + (decimal)t01.通行料) * (decimal)i一般管理費) / 100,
拘束時間 = t02Group.Where(t02 => t02.車輌KEY == m05.車輌KEY).Sum(t02 => t02.拘束時間),
運転時間 = t02Group.Where(t02 => t02.車輌KEY == m05.車輌KEY).Sum(t02 => t02.運転時間),
高速時間 = t02Group.Where(t02 => t02.車輌KEY == m05.車輌KEY).Sum(t02 => t02.高速時間),
作業時間 = t02Group.Where(t02 => t02.車輌KEY == m05.車輌KEY).Sum(t02 => t02.作業時間),
待機時間 = t02Group.Where(t02 => t02.車輌KEY == m05.車輌KEY).Sum(t02 => t02.待機時間),
休憩時間 = t02Group.Where(t02 => t02.車輌KEY == m05.車輌KEY).Sum(t02 => t02.休憩時間),
残業時間 = t02Group.Where(t02 => t02.車輌KEY == m05.車輌KEY).Sum(t02 => t02.残業時間),
深夜時間 = t02Group.Where(t02 => t02.車輌KEY == m05.車輌KEY).Sum(t02 => t02.深夜時間),
});
//車輌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);
}
//集計期間処理
//query = query.Where(c => c.集計 > d集計期間From && d集計期間To > c.集計);
}
else
{
query = query.Where(c => c.コード > int.MinValue && c.コード < int.MaxValue);
////締日処理
//if (p作成締日 != 0)
//{
// query = query.Where(c => c.締日 == p作成締日);
//}
////集計期間処理
//query = query.Where(c => c.集計 > d集計期間From && d集計期間To > c.集計);
}
}
query = query.Distinct();
//List<SRY05010_Member> queryLIST = query.ToList();
List <SRY05010_KADOU> queryLIST2 = query2.ToList();
//queryLIST = query.ToList();
var queryLIST = (from q in query.ToArray()
select new SRY05010_Member
{
KEY = q.KEY,
コード = q.コード,
集計年月 = q.集計年月,
登録年月日 = q.登録年月日,
自社部門ID = q.自社部門ID,
車種ID = q.車種ID,
乗務員KEY = q.乗務員KEY,
営業日数 = q.営業日数,
稼働日数 = q.稼働日数,
走行KM = q.走行KM,
実車KM = q.実車KM,
輸送屯数 = q.輸送屯数,
運送収入 = q.運送収入,
燃料L = q.燃料L,
一般管理費 = q.一般管理費,
拘束時間 = KyoeiSystem.Framework.Common.LinqSub.分TO時間(q.拘束時間),
運転時間 = KyoeiSystem.Framework.Common.LinqSub.分TO時間(q.運転時間),
高速時間 = KyoeiSystem.Framework.Common.LinqSub.分TO時間(q.高速時間),
作業時間 = KyoeiSystem.Framework.Common.LinqSub.分TO時間(q.作業時間),
待機時間 = KyoeiSystem.Framework.Common.LinqSub.分TO時間(q.待機時間),
休憩時間 = KyoeiSystem.Framework.Common.LinqSub.分TO時間(q.休憩時間),
残業時間 = KyoeiSystem.Framework.Common.LinqSub.分TO時間(q.残業時間),
深夜時間 = KyoeiSystem.Framework.Common.LinqSub.分TO時間(q.深夜時間),
}).ToList();
for (int i = 0; i < queryLIST.Count; i++)
{
queryLIST2 = query2.ToList();
queryLIST2 = queryLIST2.Where(q => q.車輌KEY == queryLIST[i].コード).ToList();
for (int ii = 0; ii < queryLIST2.Count; ii++)
{
if (queryLIST[i].コード == queryLIST2[ii].車輌KEY)
{
if (queryLIST2[ii].回数 != 0)
{
queryLIST[i].稼働日数 += queryLIST2[ii].回数;
}
}
}
}
int[] lst;
lst = (from q in query select q.KEY).ToArray();
//削除行を特定
var ret = from x in context.S14_CAR
where x.集計年月 == i集計年月 && lst.Contains(x.車輌KEY)
select x;
foreach (var r in ret)
{
context.DeleteObject(r);
}
context.SaveChanges();
do
{
}while ((from x in context.S14_CAR
where x.集計年月 == i集計年月 && lst.Contains(x.車輌KEY)
select x).Count() != 0);
//sqlbulukcopy準備
//DataTable dt = new DataTable("S14_CAR");
//dt.Columns.Add("車輌KEY", Type.GetType("System.Int32"));
//dt.Columns.Add("集計年月", Type.GetType("System.Int32"));
//dt.Columns.Add("登録日時", Type.GetType("System.DateTime"));
//dt.Columns.Add("更新日時", Type.GetType("System.DateTime"));
//dt.Columns.Add("自社部門ID", Type.GetType("System.Int32"));
//dt.Columns.Add("車種ID", Type.GetType("System.Int32"));
//dt.Columns.Add("乗務員KEY", Type.GetType("System.Int32"));
//dt.Columns.Add("営業日数", Type.GetType("System.Int32"));
//dt.Columns.Add("稼動日数", Type.GetType("System.Int32"));
//dt.Columns.Add("走行ＫＭ", Type.GetType("System.Int32"));
//dt.Columns.Add("実車ＫＭ", Type.GetType("System.Int32"));
//dt.Columns.Add("輸送屯数", Type.GetType("System.Decimal"));
//dt.Columns.Add("運送収入", Type.GetType("System.Decimal"));
//dt.Columns.Add("燃料Ｌ", Type.GetType("System.Decimal"));
//dt.Columns.Add("一般管理費", Type.GetType("System.Decimal"));
//dt.Columns.Add("拘束時間", Type.GetType("System.Decimal"));
//dt.Columns.Add("運転時間", Type.GetType("System.Decimal"));
//dt.Columns.Add("高速時間", Type.GetType("System.Decimal"));
//dt.Columns.Add("作業時間", Type.GetType("System.Decimal"));
//dt.Columns.Add("待機時間", Type.GetType("System.Decimal"));
//dt.Columns.Add("休憩時間", Type.GetType("System.Decimal"));
//dt.Columns.Add("残業時間", Type.GetType("System.Decimal"));
//dt.Columns.Add("深夜時間", Type.GetType("System.Decimal"));
var properities = typeof(S14_CAR).GetProperties().Where(c => !c.Name.Contains("ChangeTracker"));
DataTable dt = new DataTable(typeof(S14_CAR).Name);
foreach (var p in properities)
{
Type ColType = Nullable.GetUnderlyingType(p.PropertyType) == null ? p.PropertyType : Nullable.GetUnderlyingType(p.PropertyType);
dt.Columns.Add(p.Name, ColType);
}
foreach (var r in queryLIST)
{
DataRow dr = dt.NewRow();
dr[0] = r.KEY;
dr[1] = r.集計年月;
dr[2] = (object)r.登録年月日 ?? DBNull.Value;
dr[3] = DateTime.Now;
dr[4] = (object)r.自社部門ID ?? DBNull.Value;
dr[5] = (object)r.車種ID ?? DBNull.Value;
dr[6] = (object)r.乗務員KEY ?? DBNull.Value;
dr[7] = i営業日数 == null ? 0 : i営業日数;
dr[8] = (int)(queryLIST.Where(c => c.コード == r.コード).Select(c => c.稼働日数)).FirstOrDefault();
dr[9] = r.走行KM == null ? 0 : AppCommon.IntParse(r.走行KM.ToString());
dr[10] = r.実車KM == null ? 0 : AppCommon.IntParse(r.実車KM.ToString());
dr[11] = r.輸送屯数 == null ? 0 : AppCommon.DecimalParse(r.輸送屯数.ToString());
dr[12] = r.運送収入 == null ? 0 : AppCommon.DecimalParse(r.運送収入.ToString());
dr[13] = r.燃料L == null ? 0 : AppCommon.DecimalParse(r.燃料L.ToString());
dr[14] = r.一般管理費 == null ? 0 : Math.Round(AppCommon.DecimalParse(r.一般管理費.ToString()), 0, MidpointRounding.AwayFromZero);
dr[15] = r.拘束時間 == null ? 0 : AppCommon.DecimalParse(r.拘束時間.ToString());
dr[16] = r.運転時間 == null ? 0 : AppCommon.DecimalParse(r.運転時間.ToString());
dr[17] = r.高速時間 == null ? 0 : AppCommon.DecimalParse(r.高速時間.ToString());
dr[18] = r.作業時間 == null ? 0 : AppCommon.DecimalParse(r.作業時間.ToString());
dr[19] = r.待機時間 == null ? 0 : AppCommon.DecimalParse(r.待機時間.ToString());
dr[20] = r.休憩時間 == null ? 0 : AppCommon.DecimalParse(r.休憩時間.ToString());
dr[21] = r.残業時間 == null ? 0 : AppCommon.DecimalParse(r.残業時間.ToString());
dr[22] = r.深夜時間 == null ? 0 : AppCommon.DecimalParse(r.深夜時間.ToString());
dt.Rows.Add(dr);
}
try //SQL_BULK_COPY書込み
{
var connect = CommonData.TRAC3_SQL_GetConnectionString();
using (var bulkCopy = new SqlBulkCopy(connect))
{
bulkCopy.DestinationTableName = dt.TableName; // テーブル名をSqlBulkCopyに教える
bulkCopy.WriteToServer(dt); // bulkCopy実行
}
}
catch (Exception e)
{
}
////データ書き込み
//foreach (var r in queryLIST)
//{
// S14_CAR s13 = new S14_CAR();
// s13.車輌KEY = r.KEY;
// s13.集計年月 = r.集計年月;
// s13.登録日時 = r.登録年月日;
// s13.更新日時 = DateTime.Now;
// s13.自社部門ID = r.自社部門ID;
// s13.車種ID = r.車種ID;
// s13.乗務員KEY = r.乗務員KEY;
// s13.営業日数 = i営業日数 == null ? 0 : i営業日数;
// s13.稼動日数 = (int)(queryLIST.Where(c => c.コード == r.コード).Select(c => c.稼働日数)).FirstOrDefault();
// s13.走行ＫＭ = r.走行KM == null ? 0 : AppCommon.IntParse(r.走行KM.ToString());
// s13.実車ＫＭ = r.実車KM == null ? 0 : AppCommon.IntParse(r.実車KM.ToString());
// s13.輸送屯数 = r.輸送屯数 == null ? 0 : AppCommon.DecimalParse(r.輸送屯数.ToString());
// s13.運送収入 = r.運送収入 == null ? 0 : AppCommon.DecimalParse(r.運送収入.ToString());
// s13.燃料Ｌ = r.燃料L == null ? 0 : AppCommon.DecimalParse(r.燃料L.ToString());
// s13.一般管理費 = r.一般管理費 == null ? 0 : Math.Round( AppCommon.DecimalParse(r.一般管理費.ToString()), 0, MidpointRounding.AwayFromZero);
// s13.拘束時間 = r.拘束時間 == null ? 0 : AppCommon.DecimalParse(r.拘束時間.ToString());
// s13.運転時間 = r.運転時間 == null ? 0 : AppCommon.DecimalParse(r.運転時間.ToString());
// s13.高速時間 = r.高速時間 == null ? 0 : AppCommon.DecimalParse(r.高速時間.ToString());
// s13.作業時間 = r.作業時間 == null ? 0 : AppCommon.DecimalParse(r.作業時間.ToString());
// s13.待機時間 = r.待機時間 == null ? 0 : AppCommon.DecimalParse(r.待機時間.ToString());
// s13.休憩時間 = r.休憩時間 == null ? 0 : AppCommon.DecimalParse(r.休憩時間.ToString());
// s13.残業時間 = r.残業時間 == null ? 0 : AppCommon.DecimalParse(r.残業時間.ToString());
// s13.深夜時間 = r.深夜時間 == null ? 0 : AppCommon.DecimalParse(r.深夜時間.ToString());
// context.S14_CAR.ApplyChanges(s13);
//}
//context.SaveChanges();
//経費項目集計↓↓↓↓↓↓↓↓↓↓↓↓↓↓
lst = (from q in query select q.KEY).ToArray();
DateTime d前月年月 = d集計年月;
d前月年月 = d前月年月.AddMonths(-1);
int i前月年月 = d前月年月.Year * 100 + d前月年月.Month;
//前月データ取得
var query_zen = from s13sb in context.S14_CARSB
where s13sb.集計年月 == i前月年月
select s13sb;
//削除行を特定
var ret_tougetu_del = from x in context.S14_CARSB
where x.集計年月 == i集計年月 && lst.Contains(x.車輌KEY)
select x;
var ret_tougetu = ret_tougetu_del.ToList();
//当月データを削除
foreach (var r in ret_tougetu_del)
{
context.DeleteObject(r);
}
context.SaveChanges();
do
{
}while ((from x in context.S14_CARSB
where x.集計年月 == i集計年月 && lst.Contains(x.車輌KEY)
select x).Count() != 0);
//当月データ集計
var ret_k = (from m05 in context.M05_CAR.Where(m05 => m05.削除日付 == null)
from m07 in context.M07_KEI
join t02 in context.T03_KTRN.Where(t02 => t02.経費発生日 >= d集計期間From && t02.経費発生日 <= d集計期間To) on new { a = m05.車輌KEY, b = m07.経費項目ID } equals new { a = (int)t02.車輌ID, b = (int)t02.経費項目ID } into t02Group
//join s13 in ret_tougetu.Where(s13 => s13.集計年月 == i集計年月) on m05.車輌KEY equals s13.車輌KEY into s13Group
where lst.Contains(m05.車輌KEY)
select new SRY05010_sbMember
{
金額 = m07.固定変動区分 == 1 ? (t02Group.Sum(t02 => t02.金額) == null ? 0 : (decimal)t02Group.Sum(t02 => t02.金額)) : 0,
//金額 = m07.固定変動区分 == 1 ? (t02Group.Sum(t02 => t02.金額) == null ? 0 : (decimal)t02Group.Sum(t02 => t02.金額)) : (s13Group.Where(s13 => s13.車輌KEY == m05.車輌KEY && s13.経費項目ID == m07.経費項目ID).Sum(s13 => s13.金額) == null ? 0 : s13Group.Where(s13 => s13.車輌KEY == m05.車輌KEY && s13.経費項目ID == m07.経費項目ID).Sum(s13 => s13.金額)),
経費項目ID = m07.経費項目ID,
経費項目名 = m07.経費項目名,
固定変動区分 = m07.固定変動区分,
更新日時 = DateTime.Now,
集計年月 = i集計年月,
車輌KEY = m05.車輌KEY,
乗務員KEY = m05.乗務員KEY,
登録日時 = DateTime.Now,
//登録日時 = s13Group.Min(s13 => s13.更新日時),
}).ToList();
try
{
foreach (var row in ret_k)
{
if (ret_tougetu.Where(s13 => s13.集計年月 == i集計年月 && s13.車輌KEY == row.車輌KEY && s13.経費項目ID == row.経費項目ID).Count() == 0)
{
row.登録日時 = DateTime.Now;
}
else
{
row.登録日時 = ret_tougetu.Where(s13 => s13.集計年月 == i集計年月 && s13.車輌KEY == row.車輌KEY && s13.経費項目ID == row.経費項目ID).Select(c => c.登録日時).FirstOrDefault();
}
if (row.経費項目ID == 102)
{
if (ret_tougetu.Where(s13 => s13.集計年月 == i集計年月 && s13.車輌KEY == row.車輌KEY && s13.経費項目ID == row.経費項目ID).Count() == 0 || i固定再計算 == 1)
{
row.金額 = ((context.M05_CAR.Where(m05 => m05.車輌KEY == row.車輌KEY).Select(c => c.固定自動車税)).FirstOrDefault()) == null ? 0 : (decimal)((context.M05_CAR.Where(m05 => m05.車輌KEY == row.車輌KEY).Select(c => c.固定自動車税)).FirstOrDefault());
row.金額 = Math.Round((row.金額 / 12), 0, MidpointRounding.AwayFromZero);
}
else
{
row.金額 = (ret_tougetu.Where(s13 => s13.集計年月 == i集計年月 && s13.車輌KEY == row.車輌KEY && s13.経費項目ID == row.経費項目ID).Select(s13 => s13.金額)).FirstOrDefault();
}
continue;
}
if (row.経費項目ID == 103)
{
if (ret_tougetu.Where(s13 => s13.集計年月 == i集計年月 && s13.車輌KEY == row.車輌KEY && s13.経費項目ID == row.経費項目ID).Count() == 0 || i固定再計算 == 1)
{
row.金額 = ((context.M05_CAR.Where(m05 => m05.車輌KEY == row.車輌KEY).Select(c => c.固定重量税)).FirstOrDefault()) == null ? 0 : (decimal)((context.M05_CAR.Where(m05 => m05.車輌KEY == row.車輌KEY).Select(c => c.固定重量税)).FirstOrDefault());
row.金額 = Math.Round((row.金額 / 12), 0, MidpointRounding.AwayFromZero);
}
else
{
row.金額 = (ret_tougetu.Where(s13 => s13.集計年月 == i集計年月 && s13.車輌KEY == row.車輌KEY && s13.経費項目ID == row.経費項目ID).Select(s13 => s13.金額)).FirstOrDefault();
}
continue;
}
if (row.経費項目ID == 104)
{
if (ret_tougetu.Where(s13 => s13.集計年月 == i集計年月 && s13.車輌KEY == row.車輌KEY && s13.経費項目ID == row.経費項目ID).Count() == 0 || i固定再計算 == 1)
{
row.金額 = ((context.M05_CAR.Where(m05 => m05.車輌KEY == row.車輌KEY).Select(c => c.固定取得税)).FirstOrDefault()) == null ? 0 : (decimal)((context.M05_CAR.Where(m05 => m05.車輌KEY == row.車輌KEY).Select(c => c.固定取得税)).FirstOrDefault());
row.金額 = Math.Round((row.金額 / 12), 0, MidpointRounding.AwayFromZero);
}
else
{
row.金額 = (ret_tougetu.Where(s13 => s13.集計年月 == i集計年月 && s13.車輌KEY == row.車輌KEY && s13.経費項目ID == row.経費項目ID).Select(s13 => s13.金額)).FirstOrDefault();
}
continue;
}
if (row.経費項目ID == 201)
{
if (ret_tougetu.Where(s13 => s13.集計年月 == i集計年月 && s13.車輌KEY == row.車輌KEY && s13.経費項目ID == row.経費項目ID).Count() == 0 || i固定再計算 == 1)
{
row.金額 = ((context.M05_CAR.Where(m05 => m05.車輌KEY == row.車輌KEY).Select(c => c.固定自賠責保険)).FirstOrDefault()) == null ? 0 : (decimal)((context.M05_CAR.Where(m05 => m05.車輌KEY == row.車輌KEY).Select(c => c.固定自賠責保険)).FirstOrDefault());
row.金額 = Math.Round((row.金額 / 12), 0, MidpointRounding.AwayFromZero);
}
else
{
row.金額 = (ret_tougetu.Where(s13 => s13.集計年月 == i集計年月 && s13.車輌KEY == row.車輌KEY && s13.経費項目ID == row.経費項目ID).Select(s13 => s13.金額)).FirstOrDefault();
}
continue;
}
if (row.経費項目ID == 202)
{
if (ret_tougetu.Where(s13 => s13.集計年月 == i集計年月 && s13.車輌KEY == row.車輌KEY && s13.経費項目ID == row.経費項目ID).Count() == 0 || i固定再計算 == 1)
{
row.金額 = ((context.M05_CAR.Where(m05 => m05.車輌KEY == row.車輌KEY).Select(c => c.固定車輌保険)).FirstOrDefault()) == null ? 0 : (decimal)((context.M05_CAR.Where(m05 => m05.車輌KEY == row.車輌KEY).Select(c => c.固定車輌保険)).FirstOrDefault());
row.金額 = Math.Round((row.金額 / 12), 0, MidpointRounding.AwayFromZero);
}
else
{
row.金額 = (ret_tougetu.Where(s13 => s13.集計年月 == i集計年月 && s13.車輌KEY == row.車輌KEY && s13.経費項目ID == row.経費項目ID).Select(s13 => s13.金額)).FirstOrDefault();
}
continue;
}
if (row.経費項目ID == 203)
{
if (ret_tougetu.Where(s13 => s13.集計年月 == i集計年月 && s13.車輌KEY == row.車輌KEY && s13.経費項目ID == row.経費項目ID).Count() == 0 || i固定再計算 == 1)
{
row.金額 = ((context.M05_CAR.Where(m05 => m05.車輌KEY == row.車輌KEY).Select(c => c.固定対人保険)).FirstOrDefault()) == null ? 0 : (decimal)((context.M05_CAR.Where(m05 => m05.車輌KEY == row.車輌KEY).Select(c => c.固定対人保険)).FirstOrDefault());
row.金額 = Math.Round((row.金額 / 12), 0, MidpointRounding.AwayFromZero);
}
else
{
row.金額 = (ret_tougetu.Where(s13 => s13.集計年月 == i集計年月 && s13.車輌KEY == row.車輌KEY && s13.経費項目ID == row.経費項目ID).Select(s13 => s13.金額)).FirstOrDefault();
}
continue;
}
if (row.経費項目ID == 204)
{
if (ret_tougetu.Where(s13 => s13.集計年月 == i集計年月 && s13.車輌KEY == row.車輌KEY && s13.経費項目ID == row.経費項目ID).Count() == 0 || i固定再計算 == 1)
{
row.金額 = ((context.M05_CAR.Where(m05 => m05.車輌KEY == row.車輌KEY).Select(c => c.固定対物保険)).FirstOrDefault()) == null ? 0 : (decimal)((context.M05_CAR.Where(m05 => m05.車輌KEY == row.車輌KEY).Select(c => c.固定対物保険)).FirstOrDefault());
row.金額 = Math.Round((row.金額 / 12), 0, MidpointRounding.AwayFromZero);
}
else
{
row.金額 = (ret_tougetu.Where(s13 => s13.集計年月 == i集計年月 && s13.車輌KEY == row.車輌KEY && s13.経費項目ID == row.経費項目ID).Select(s13 => s13.金額)).FirstOrDefault();
}
continue;
}
if (row.経費項目ID == 205)
{
if (ret_tougetu.Where(s13 => s13.集計年月 == i集計年月 && s13.車輌KEY == row.車輌KEY && s13.経費項目ID == row.経費項目ID).Count() == 0 || i固定再計算 == 1)
{
row.金額 = ((context.M05_CAR.Where(m05 => m05.車輌KEY == row.車輌KEY).Select(c => c.固定貨物保険)).FirstOrDefault()) == null ? 0 : (decimal)((context.M05_CAR.Where(m05 => m05.車輌KEY == row.車輌KEY).Select(c => c.固定貨物保険)).FirstOrDefault());
row.金額 = Math.Round((row.金額 / 12), 0, MidpointRounding.AwayFromZero);
}
else
{
row.金額 = (ret_tougetu.Where(s13 => s13.集計年月 == i集計年月 && s13.車輌KEY == row.車輌KEY && s13.経費項目ID == row.経費項目ID).Select(s13 => s13.金額)).FirstOrDefault();
}
continue;
}
if (row.経費項目ID == 301)
{
if (ret_tougetu.Where(s13 => s13.集計年月 == i集計年月 && s13.車輌KEY == row.車輌KEY && s13.経費項目ID == row.経費項目ID).Count() == 0 || i固定再計算 == 1)
{
row.金額 = ((context.M04_DRV.Where(m04 => m04.乗務員KEY == row.乗務員KEY).Select(c => c.固定給与)).FirstOrDefault()) == null ? 0 : (decimal)((context.M04_DRV.Where(m04 => m04.乗務員KEY == row.乗務員KEY).Select(c => c.固定給与)).FirstOrDefault());
}
else
{
row.金額 = (ret_tougetu.Where(s13 => s13.集計年月 == i集計年月 && s13.車輌KEY == row.車輌KEY && s13.経費項目ID == row.経費項目ID).Select(s13 => s13.金額)).FirstOrDefault();
}
continue;
}
if (row.経費項目ID == 303)
{
if (ret_tougetu.Where(s13 => s13.集計年月 == i集計年月 && s13.車輌KEY == row.車輌KEY && s13.経費項目ID == row.経費項目ID).Count() == 0 || i固定再計算 == 1)
{
row.金額 = ((context.M04_DRV.Where(m04 => m04.乗務員KEY == row.乗務員KEY).Select(c => c.固定賞与積立金)).FirstOrDefault()) == null ? 0 : (decimal)((context.M04_DRV.Where(m04 => m04.乗務員KEY == row.乗務員KEY).Select(c => c.固定賞与積立金)).FirstOrDefault());
}
else
{
row.金額 = (ret_tougetu.Where(s13 => s13.集計年月 == i集計年月 && s13.車輌KEY == row.車輌KEY && s13.経費項目ID == row.経費項目ID).Select(s13 => s13.金額)).FirstOrDefault();
}
continue;
}
if (row.経費項目ID == 304)
{
if (ret_tougetu.Where(s13 => s13.集計年月 == i集計年月 && s13.車輌KEY == row.車輌KEY && s13.経費項目ID == row.経費項目ID).Count() == 0 || i固定再計算 == 1)
{
row.金額 = ((context.M04_DRV.Where(m04 => m04.乗務員KEY == row.乗務員KEY).Select(c => c.固定福利厚生費)).FirstOrDefault()) == null ? 0 : (decimal)((context.M04_DRV.Where(m04 => m04.乗務員KEY == row.乗務員KEY).Select(c => c.固定福利厚生費)).FirstOrDefault());
}
else
{
row.金額 = (ret_tougetu.Where(s13 => s13.集計年月 == i集計年月 && s13.車輌KEY == row.車輌KEY && s13.経費項目ID == row.経費項目ID).Select(s13 => s13.金額)).FirstOrDefault();
}
continue;
}
if (row.経費項目ID == 305)
{
if (ret_tougetu.Where(s13 => s13.集計年月 == i集計年月 && s13.車輌KEY == row.車輌KEY && s13.経費項目ID == row.経費項目ID).Count() == 0 || i固定再計算 == 1)
{
row.金額 = ((context.M04_DRV.Where(m04 => m04.乗務員KEY == row.乗務員KEY).Select(c => c.固定法定福利費)).FirstOrDefault()) == null ? 0 : (decimal)((context.M04_DRV.Where(m04 => m04.乗務員KEY == row.乗務員KEY).Select(c => c.固定法定福利費)).FirstOrDefault());
}
else
{
row.金額 = (ret_tougetu.Where(s13 => s13.集計年月 == i集計年月 && s13.車輌KEY == row.車輌KEY && s13.経費項目ID == row.経費項目ID).Select(s13 => s13.金額)).FirstOrDefault();
}
continue;
}
if (row.経費項目ID == 306)
{
if (ret_tougetu.Where(s13 => s13.集計年月 == i集計年月 && s13.車輌KEY == row.車輌KEY && s13.経費項目ID == row.経費項目ID).Count() == 0 || i固定再計算 == 1)
{
row.金額 = ((context.M04_DRV.Where(m04 => m04.乗務員KEY == row.乗務員KEY).Select(c => c.固定退職引当金)).FirstOrDefault()) == null ? 0 : (decimal)((context.M04_DRV.Where(m04 => m04.乗務員KEY == row.乗務員KEY).Select(c => c.固定退職引当金)).FirstOrDefault());
}
else
{
row.金額 = (ret_tougetu.Where(s13 => s13.集計年月 == i集計年月 && s13.車輌KEY == row.車輌KEY && s13.経費項目ID == row.経費項目ID).Select(s13 => s13.金額)).FirstOrDefault();
}
continue;
}
if (row.経費項目ID == 307)
{
if (ret_tougetu.Where(s13 => s13.集計年月 == i集計年月 && s13.車輌KEY == row.車輌KEY && s13.経費項目ID == row.経費項目ID).Count() == 0 || i固定再計算 == 1)
{
row.金額 = ((context.M04_DRV.Where(m04 => m04.乗務員KEY == row.乗務員KEY).Select(c => c.固定労働保険)).FirstOrDefault()) == null ? 0 : (decimal)((context.M04_DRV.Where(m04 => m04.乗務員KEY == row.乗務員KEY).Select(c => c.固定労働保険)).FirstOrDefault());
}
else
{
row.金額 = (ret_tougetu.Where(s13 => s13.集計年月 == i集計年月 && s13.車輌KEY == row.車輌KEY && s13.経費項目ID == row.経費項目ID).Select(s13 => s13.金額)).FirstOrDefault();
}
continue;
}
//
if (row.固定変動区分 == 0)
{
if (ret_tougetu.Where(s13 => s13.集計年月 == i集計年月 && s13.車輌KEY == row.車輌KEY && s13.経費項目ID == row.経費項目ID).Count() == 0 || i固定再計算 == 1)
{
row.金額 = query_zen == null ? 0 : query_zen.Where(c => c.車輌KEY == row.車輌KEY && c.経費項目ID == row.経費項目ID).Select(c => c.金額).FirstOrDefault();
}
else
{
row.金額 = ret_tougetu == null ? 0 : (ret_tougetu.Where(s13 => s13.集計年月 == i集計年月 && s13.車輌KEY == row.車輌KEY && s13.経費項目ID == row.経費項目ID).Select(s13 => s13.金額)).FirstOrDefault();
}
}
}
}
catch (Exception ex)
{
}
//var car = (from m05 in context.M05_CAR.Where(m05 => m05.削除日付 == null)
// where lst.Contains(m05.車輌KEY)
// select new M05_CAR()
// );
//foreach (var row in car)
//{
// if (row.固定自動車税 != null && row.固定自動車税 != 0)
// {
// ret_k.Add(new SRY05010_sbMember
// {
// 金額 = row.固定自動車税 ?? 0,
// 経費項目ID = 102,
// 経費項目名 = (context.M07_KEI.Where(c => c.経費項目ID == 102).Select(c => c.経費項目名)).FirstOrDefault(),
// 固定変動区分 = 0,
// 更新日時 = DateTime.Now,
// 車輌KEY = row.車輌KEY,
// 集計年月 = i集計年月,
// 登録日時 = DateTime.Now,
// });
// }
// if (row.固定重量税 != null && row.固定重量税 != 0)
// {
// ret_k.Add(new SRY05010_sbMember
// {
// 金額 = row.固定重量税 ?? 0,
// 経費項目ID = 103,
// 経費項目名 = (context.M07_KEI.Where(c => c.経費項目ID == 103).Select(c => c.経費項目名)).FirstOrDefault(),
// 固定変動区分 = 0,
// 更新日時 = DateTime.Now,
// 車輌KEY = row.車輌KEY,
// 集計年月 = i集計年月,
// 登録日時 = DateTime.Now,
// });
// }
// if (row.固定取得税 != null && row.固定取得税 != 0)
// {
// ret_k.Add(new SRY05010_sbMember
// {
// 金額 = row.固定取得税 ?? 0,
// 経費項目ID = 104,
// 経費項目名 = (context.M07_KEI.Where(c => c.経費項目ID == 104).Select(c => c.経費項目名)).FirstOrDefault(),
// 固定変動区分 = 0,
// 更新日時 = DateTime.Now,
// 車輌KEY = row.車輌KEY,
// 集計年月 = i集計年月,
// 登録日時 = DateTime.Now,
// });
// }
// if (row.固定自賠責保険 != null && row.固定自賠責保険 != 0)
// {
// ret_k.Add(new SRY05010_sbMember
// {
// 金額 = row.固定自賠責保険 ?? 0,
// 経費項目ID = 201,
// 経費項目名 = (context.M07_KEI.Where(c => c.経費項目ID == 201).Select(c => c.経費項目名)).FirstOrDefault(),
// 固定変動区分 = 0,
// 更新日時 = DateTime.Now,
// 車輌KEY = row.車輌KEY,
// 集計年月 = i集計年月,
// 登録日時 = DateTime.Now,
// });
// }
// if (row.固定車輌保険 != null && row.固定車輌保険 != 0)
// {
// ret_k.Add(new SRY05010_sbMember
// {
// 金額 = row.固定車輌保険 ?? 0,
// 経費項目ID = 202,
// 経費項目名 = (context.M07_KEI.Where(c => c.経費項目ID == 202).Select(c => c.経費項目名)).FirstOrDefault(),
// 固定変動区分 = 0,
// 更新日時 = DateTime.Now,
// 車輌KEY = row.車輌KEY,
// 集計年月 = i集計年月,
// 登録日時 = DateTime.Now,
// });
// }
// if (row.固定対人保険 != null && row.固定対人保険 != 0)
// {
// ret_k.Add(new SRY05010_sbMember
// {
// 金額 = row.固定対人保険 ?? 0,
// 経費項目ID = 203,
// 経費項目名 = (context.M07_KEI.Where(c => c.経費項目ID == 203).Select(c => c.経費項目名)).FirstOrDefault(),
// 固定変動区分 = 0,
// 更新日時 = DateTime.Now,
// 車輌KEY = row.車輌KEY,
// 集計年月 = i集計年月,
// 登録日時 = DateTime.Now,
// });
// }
// if (row.固定対物保険 != null && row.固定対物保険 != 0)
// {
// ret_k.Add(new SRY05010_sbMember
// {
// 金額 = row.固定対物保険 ?? 0,
// 経費項目ID = 204,
// 経費項目名 = (context.M07_KEI.Where(c => c.経費項目ID == 204).Select(c => c.経費項目名)).FirstOrDefault(),
// 固定変動区分 = 0,
// 更新日時 = DateTime.Now,
// 車輌KEY = row.車輌KEY,
// 集計年月 = i集計年月,
// 登録日時 = DateTime.Now,
// });
// }
// if (row.固定貨物保険 != null && row.固定貨物保険 != 0)
// {
// ret_k.Add(new SRY05010_sbMember
// {
// 金額 = row.固定貨物保険 ?? 0,
// 経費項目ID = 205,
// 経費項目名 = (context.M07_KEI.Where(c => c.経費項目ID == 205).Select(c => c.経費項目名)).FirstOrDefault(),
// 固定変動区分 = 0,
// 更新日時 = DateTime.Now,
// 車輌KEY = row.車輌KEY,
// 集計年月 = i集計年月,
// 登録日時 = DateTime.Now,
// });
// }
//}
//if (i固定再計算 == 1)
//{
// foreach (var r in ret_k)
// {
// if (r.固定変動区分 == 0)
// {
// foreach (var r2 in query_zen)
// {
// if (r.経費項目ID == r2.経費項目ID && (r.経費項目ID != 102 && r.経費項目ID != 103 && r.経費項目ID != 104 && r.経費項目ID != 201 && r.経費項目ID != 202 && r.経費項目ID != 203 && r.経費項目ID != 204 && r.経費項目ID != 205))
// {
// r.金額 = r2.金額;
// }
// }
// }
// }
//}
//sqlbulukcopy準備
DataTable CARSB_dt = new DataTable("S14_CARSB");
CARSB_dt.Columns.Add("車輌KEY", Type.GetType("System.Int32"));
CARSB_dt.Columns.Add("集計年月", Type.GetType("System.Int32"));
CARSB_dt.Columns.Add("経費項目ID", Type.GetType("System.Int32"));
CARSB_dt.Columns.Add("登録日時", Type.GetType("System.DateTime"));
CARSB_dt.Columns.Add("更新日時", Type.GetType("System.DateTime"));
CARSB_dt.Columns.Add("経費項目名", Type.GetType("System.String"));
CARSB_dt.Columns.Add("固定変動区分", Type.GetType("System.Int32"));
CARSB_dt.Columns.Add("金額", Type.GetType("System.Decimal"));
foreach (var r in ret_k)
{
DataRow dr = CARSB_dt.NewRow();
dr[0] = r.車輌KEY;
dr[1] = r.集計年月;
dr[2] = r.経費項目ID == null ? 0 : AppCommon.DecimalParse(r.経費項目ID.ToString());
dr[3] = (object)r.登録日時 ?? DBNull.Value;
dr[4] = (object)r.更新日時 ?? DBNull.Value;
dr[5] = (object)r.経費項目名 ?? DBNull.Value;
dr[6] = r.固定変動区分 == null ? 0 : AppCommon.IntParse(r.固定変動区分.ToString());
dr[7] = r.金額 == null ? 0 : AppCommon.DecimalParse(r.金額.ToString());
CARSB_dt.Rows.Add(dr);
}
try //SQL_BULK_COPY書込み
{
var connect = CommonData.TRAC3_SQL_GetConnectionString();
using (var bulkCopy = new SqlBulkCopy(connect))
{
bulkCopy.DestinationTableName = CARSB_dt.TableName; // テーブル名をSqlBulkCopyに教える
bulkCopy.WriteToServer(CARSB_dt); // bulkCopy実行
}
}
catch (Exception e)
{
}
////データ書き込み
//foreach (var r in ret_k)
//{
// S14_CARSB s13sb = new S14_CARSB();
// s13sb.金額 = r.金額;
// s13sb.経費項目ID = r.経費項目ID;
// s13sb.経費項目名 = r.経費項目名;
// s13sb.固定変動区分 = r.固定変動区分;
// s13sb.更新日時 = r.更新日時;
// s13sb.集計年月 = r.集計年月;
// s13sb.車輌KEY = r.車輌KEY;
// s13sb.登録日時 = r.登録日時;
// context.S14_CARSB.ApplyChanges(s13sb);
//}
//context.SaveChanges();
//結果をリスト化
retList = queryLIST.ToList();
return(retList);
}
}
