Пример #1
0
        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);
        }
Пример #2
0
        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);
        }