public List <RepPos> getPos(int repType)
{
List <RepPos> list = new List <RepPos>();
string query = "";
switch (repType)
{
case 1:
query = String.Format(
"select s.id_stat, s.name, " +
"(case when vo.id_gruz=6 then 0 when vo.id_gruz is null then -1 else 1 end) as is_loaded, " +
"o.abr as details, count(vo.id_vagon) as amount " +
"from STATIONS s " +
"left join VAGON_OPERATIONS vo on s.id_stat=vo.id_stat and vo.is_present=1 and vo.is_hist=0 " +
"left join VAGONS v on vo.id_vagon=v.id_vag " +
"left join OWNERS o on v.id_owner = o.id_owner " +
"where s.is_uz = 0 " +
"group by s.id_stat, s.name, case when vo.id_gruz=6 then 0 when vo.id_gruz is null then -1 else 1 end, o.abr"
);
break;
case 2:
query = String.Format(
"select s.id_stat, s.name, " +
"(case when vo.id_gruz=6 then 0 when vo.id_gruz is null then -1 else 1 end) as is_loaded, " +
"g.name as details, count(vo.id_vagon) as amount " +
"from STATIONS s " +
"left join VAGON_OPERATIONS vo on s.id_stat=vo.id_stat and vo.is_present=1 and vo.is_hist=0 " +
"left join GRUZS g on vo.id_gruz = g.id_gruz " +
"where s.is_uz = 0 " +
"group by s.id_stat, s.name, case when vo.id_gruz=6 then 0 when vo.id_gruz is null then -1 else 1 end, g.name "
);
break;
case 3:
query = String.Format(
"select s.id_stat, s.name, " +
"(case when vo.id_gruz=6 then 0 when vo.id_gruz is null then -1 else 1 end) as is_loaded, " +
"sh.name as details, count(vo.id_vagon) as amount " +
"from STATIONS s " +
"left join VAGON_OPERATIONS vo on s.id_stat=vo.id_stat and vo.is_present=1 and vo.is_hist=0 " +
"left join SHOPS sh on vo.id_shop_gruz_for = sh.id_shop " +
"where s.is_uz = 0 " +
"group by s.id_stat, s.name, case when vo.id_gruz=6 then 0 when vo.id_gruz is null then -1 else 1 end, sh.name "
);
break;
case 4:
query = String.Format(
"select s.id_stat, s.name, " +
"(case when vo.id_gruz=6 then 0 when vo.id_gruz is null then -1 else 1 end) as is_loaded, " +
"c.name as details, count(vo.id_vagon) as amount " +
"from STATIONS s " +
"left join VAGON_OPERATIONS vo on s.id_stat=vo.id_stat and vo.is_present=1 and vo.is_hist=0 " +
"left join VAG_CONDITIONS2 c on vo.id_cond2 = c.id_cond " +
"where s.is_uz = 0 " +
"group by s.id_stat, s.name, case when vo.id_gruz=6 then 0 when vo.id_gruz is null then -1 else 1 end, c.name "
);
break;
}
DataTable table = Conn.executeSelectQuery(query, new SqlParameter[0]).Tables[0];
foreach (DataRow row in table.Rows)
{
RepPos repPos = new RepPos();
repPos.WayOrStatId = Int32.Parse(row["id_stat"].ToString());
repPos.WayOrStatName = row["name"].ToString().Trim();
repPos.IsLoaded = Int32.Parse(row["is_loaded"].ToString());
repPos.DetailsName = row["details"].ToString().Trim();
repPos.Amount = Int32.Parse(row["amount"].ToString());
list.Add(repPos);
}
return(list);
}
public List <RepPos> getPos(Station station, int repType)
{
List <RepPos> list = new List <RepPos>();
string query = "";
switch (repType)
{
case 1:
query = String.Format(
"select w.id_way, w.num as num_way, " +
"(case when vo.id_gruz=6 then 0 when vo.id_gruz is null then -1 else 1 end) as is_loaded, " +
"o.abr as details, count(vo.id_vagon) as amount " +
"from WAYS w " +
"left join VAGON_OPERATIONS vo on w.id_way=vo.id_way and vo.is_present=1 and vo.is_hist=0 " +
"inner join STATIONS s on w.id_stat=s.id_stat " +
"left join VAGONS v on vo.id_vagon=v.id_vag " +
"left join OWNERS o on v.id_owner = o.id_owner " +
"where w.id_stat=@id_stat " +
"group by w.id_way, w.num, w.name, case when vo.id_gruz=6 then 0 when vo.id_gruz is null then -1 else 1 end, o.abr, w.[order] " +
"order by w.[order]"
);
break;
case 2:
query = String.Format(
"select w.id_way, w.num as num_way, " +
"(case when vo.id_gruz=6 then 0 when vo.id_gruz is null then -1 else 1 end) as is_loaded, " +
"g.name as details, count(vo.id_vagon) as amount " +
"from WAYS w " +
"left join VAGON_OPERATIONS vo on w.id_way=vo.id_way and vo.is_present=1 and vo.is_hist=0 " +
"inner join STATIONS s on w.id_stat=s.id_stat " +
"left join GRUZS g on vo.id_gruz = g.id_gruz " +
"where w.id_stat=@id_stat " +
"group by w.id_way, w.num, w.name, case when vo.id_gruz=6 then 0 when vo.id_gruz is null then -1 else 1 end, g.name, w.[order] " +
"order by w.[order]"
);
break;
case 3:
query = String.Format(
"select w.id_way, w.num as num_way, " +
"(case when vo.id_gruz=6 then 0 when vo.id_gruz is null then -1 else 1 end) as is_loaded, " +
"sh.name as details, count(vo.id_vagon) as amount " +
"from WAYS w " +
"left join VAGON_OPERATIONS vo on w.id_way=vo.id_way and vo.is_present=1 and vo.is_hist=0 " +
"inner join STATIONS s on w.id_stat=s.id_stat " +
"left join SHOPS sh on vo.id_shop_gruz_for = sh.id_shop " +
"where w.id_stat=@id_stat " +
"group by w.id_way, w.num, w.name, case when vo.id_gruz=6 then 0 when vo.id_gruz is null then -1 else 1 end, sh.name, w.[order] " +
"order by w.[order] "
);
break;
case 4:
query = String.Format(
"select w.id_way, w.num as num_way, " +
"(case when vo.id_gruz=6 then 0 when vo.id_gruz is null then -1 else 1 end) as is_loaded, " +
"c.name as details, count(vo.id_vagon) as amount " +
"from WAYS w " +
"left join VAGON_OPERATIONS vo on w.id_way=vo.id_way and vo.is_present=1 and vo.is_hist=0 " +
"inner join STATIONS s on w.id_stat=s.id_stat " +
"left join VAG_CONDITIONS2 c on vo.id_cond2 = c.id_cond " +
"where w.id_stat=@id_stat " +
"group by w.id_way, w.num, w.name, case when vo.id_gruz=6 then 0 when vo.id_gruz is null then -1 else 1 end, c.name, w.[order] " +
"order by w.[order] "
);
break;
}
SqlParameter[] sqlParameters = new SqlParameter[1];
sqlParameters[0] = new SqlParameter("@id_stat", station.ID);
DataTable table = Conn.executeSelectQuery(query, sqlParameters).Tables[0];
foreach (DataRow row in table.Rows)
{
RepPos repPos = new RepPos();
repPos.WayOrStatId = Int32.Parse(row["id_way"].ToString());
repPos.WayOrStatName = row["num_way"].ToString().Trim();
repPos.IsLoaded = Int32.Parse(row["is_loaded"].ToString());
repPos.DetailsName = row["details"].ToString().Trim();
repPos.Amount = Int32.Parse(row["amount"].ToString());
list.Add(repPos);
}
return(list);
}