Exemple #1
0
        public ActionResult Index()
        {
            TeamSummary p = new TeamSummary();
            
            List<string> l = new List<string>();
            l.Add("Customer Experience");
            l.Add("");

            List<string> l2 = new List<string>();
            l2.Add("/Home/Index");
            l2.Add("/Home/Index");

            p.Header = getHeaderInfo(l, l2, "Customer Experience", "");

            return View(p);
        }
Exemple #2
0
        public ActionResult BackOrdersTeam(int Id)
        {
            TeamSummary p = new TeamSummary();

            Database d = new Database();
            d.openConnection();

            string sql69 = "select team_name from bbs_cs_team where teamid = " + Id + ";";
            var result11 = d.getDataset(sql69);

            string csTeam = result11.Tables[0].Rows[0]["team_name"].ToString();

            List<string> l = new List<string>();
            l.Add("Customer Experience");
            l.Add("Back Orders Team Summary");
            l.Add(csTeam);

            List<string> l2 = new List<string>();
            l2.Add("/Home/Index");
            l2.Add("/Home/BackOrders");
            l2.Add("/Home/BackOrdersTeam/" + Id);

            p.Header = getHeaderInfo(l, l2, "Back Orders", csTeam);


            string sql1 = "select h.compno , h.custno, h.order_ref, c.cust_name, l.create_date, l.order_date, l.due_date, l.orderno , " +
                          " l.line_no , l.stock_no , l.stock_desc, l.order_qty, l.desp_qty," +
                          "      l.order_status, l.order_qty - l.desp_qty as outstanding_qty, l.non_stocked, l.back_orderno, l.back_lineno, l.wareno " +
                          "  from ordhead h" +
                          "      inner join ordline l on h.compno = l.compno and h.orderno = l.orderno" +
                          "      inner join customer c on h.compno = c.compno and h.custno = c.custno" +
                          "  where h.compno = 1" +
                          "  and h.wareno IN (select wareno from waredesc)" +
                          "  and h.order_date > today - 10" +
                          "  and h.order_status IN (1,2)" +
                          "  and h.due_date <= today" +
                          "  and l.due_date <= today" +
                          "  and h.next_pick_date <= today" +
                          "  and l.order_status IN (1,2)" +
                          "  into temp backOrders with no log";

            string sql2 = " select b.* , promise_date, cs.team_name, teamid" +
                          "      from backOrders b" +
                          "          left outer join mrppurhed m on m.compno = 1 and b.back_orderno = m.order_no" +
                          "          left outer join mrppurdet d on m.compno = d.compno and m.order_no = d.order_no and b.back_lineno = d.line_no" +
                          "          left outer join bbs_cs_team cs" +
                          "              on cs.teamid = get_team(b.compno , b.custno, b.wareno)" +
                          "      into temp backOrders2 with no log";


            string sql4 = "select * from backOrders2 where teamid = " + Id + " order by orderno , line_no; ";

            d.executeSQL(sql1);
            d.executeSQL(sql2);
       
            var result2 = d.getDataset(sql4);

            p.Data = result2;

            d.closeConnection();

            return View(p);
        }
Exemple #3
0
        public ActionResult EDITeam(int Id)
        {
            TeamSummary p = new TeamSummary();

            Database d = new Database();
            d.openConnection();

            string sql69 = "select team_name from bbs_cs_team where teamid = " + Id + ";";
            var result11 = d.getDataset(sql69);

            string csTeam = result11.Tables[0].Rows[0]["team_name"].ToString();

            List<string> l = new List<string>();
            l.Add("Customer Experience");
            l.Add("EDI Rejections Team Summary");
            l.Add(csTeam);

            List<string> l2 = new List<string>();
            l2.Add("/Home/Index");
            l2.Add("/Home/EDI");
            l2.Add("/Home/EDITeam/" + Id);

            p.Header = getHeaderInfo(l, l2, "EDI Rejections", csTeam);

            string s1 = "set isolation to dirty read;";
            string s2 = "set lock mode to wait;";

            string sql1 = "select genno , a.error_ind, a.supplier, a.reference, a.message, a.lineno, a.date_created, a.time_created " +
                    "from swedierrlog a " +
                    "where message_type = 11 " +
                    "and genno > 15000000 " +
                    " and error_ind is not null " +
                    "and message_date = today " +
                    "and a.message not like 'WARNING%' " +
                    "and a.message not like '%Invoice%' " +
                    "into temp rejections_mn with no log; ";

            string sql3 = "select  cs.team_name, cs.teamid ," +
                          "          a.genno, " +
                          "          a.error_ind,  " +
                          " a.supplier,a.reference,a.message,b.contract_no,b.delivery_name_1,b.delivery_name_2,b.delivery_addr_1,b.delivery_addr_2,b.delivery_addr_3,b.delivery_addr_4, "+
                          " b.delivery_post_code,b.orderno,b.wareno,c.line_number,c.hmso_product_code,c.qty_ordered,c.price,c.item_desc_1,c.item_desc_2,c.item_desc_3,c.item_desc_4, "+
                          " c.item_desc_5,cs.team_name,a.lineno,c.line_no , a.date_created, a.time_created" +        
                          "  from rejections_mn a " +
                          "      inner join edisop_head b " +
                          "          on b.doc_no = a.genno " +
                          "      left outer join edisop_line c " +
                          "          on a.genno = c.doc_no " +
                          "          and a.error_ind = c.line_no " +
                          "      left outer join bbs_cs_team cs " +
                          "          on cs.teamid = get_team(b.compno , b.cust_acc_code, b.wareno) " +
                          "  where cs.teamid = " + Id +
                          " order by a.date_created, a.time_created,a.supplier,a.reference ;";

            d.executeSQL(s1);
            d.executeSQL(s2);

            bool res = d.executeSQL(sql1);

            DataSet result1 = new DataSet(); ;

            if(res)
            {
                result1 = d.getDataset(sql3);
            }

            p.Data = result1;

            d.closeConnection();

            return View(p);
        }
Exemple #4
0
        public ActionResult HeldOrdersTeam(int Id)
        {
            TeamSummary p = new TeamSummary();

            Database d = new Database();
            d.openConnection();

            string sql69 = "select team_name from bbs_cs_team where teamid = " + Id + ";";
            var result11 = d.getDataset(sql69);

            string csTeam = result11.Tables[0].Rows[0]["team_name"].ToString();

            List<string> l = new List<string>();
            l.Add("Customer Experience");
            l.Add("Held Orders Team Sumary");
            l.Add(csTeam);

            List<string> l2 = new List<string>();
            l2.Add("/Home/Index");
            l2.Add("/Home/HeldOrders");
            l2.Add("/Home/HeldOrdersTeam/" + Id);

            p.Header = getHeaderInfo(l, l2, "Held Orders", "");

            string s1 = "set isolation to dirty read;";

            string sql1 = "select h.custno, c.cust_name , h.orderno , l.line_no , l.stock_no , l.stock_desc, " +
                          "  l.order_qty , h.order_status as headerStatus , l.order_status as lineStatus, h.wareno, h.compno " +
                          "  from ordhead h inner join ordline l " +
                          "      on h.compno = l.compno " +
                          "      and h.orderno = l.orderno " +
                          "  inner join customer c " +
                          "      on h.compno = c.compno " +
                          "      and h.custno = c.custno " +
                          "  where h.compno = 1 " +
                          "  and h.wareno in (select wareno from waredesc) " +
                          "  and h.order_date > today - 2 " +
                          "  and l.order_status = 0 " +
                          "  into temp heldOrders with no log; ";


            string sql3 =  "select * from heldOrders h "+
                           "       left outer join bbs_cs_team cs " +
                           "             on cs.teamid = get_team(h.compno , h.custno, h.wareno) " +
                           " where teamid = " + Id;

            

            d.executeSQL(s1);
            d.executeSQL(sql1);
            var result2 = d.getDataset(sql3);

            p.Data = result2;

            d.closeConnection();

            return View(p);
        }
Exemple #5
0
        public ActionResult PPOTeam(int Id)
        {
            TeamSummary p = new TeamSummary();
            Database d = new Database();
            d.openConnection();

            string sql69 = "select team_name from bbs_cs_team where teamid = " + Id + ";";
            var result11 = d.getDataset(sql69);

            string csTeam = result11.Tables[0].Rows[0]["team_name"].ToString();

            List<string> l = new List<string>();
            l.Add("Customer Experience");
            l.Add("PPO Awaiting Authorisation Team Summary");
            l.Add(csTeam);

            List<string> l2 = new List<string>();
            l2.Add("/Home/Index");
            l2.Add("/Home/PPO");
            l2.Add("/Home/PPOTeam/"+Id);

            p.Header = getHeaderInfo(l, l2, "PPO Awaiting Authorisation", csTeam);


            string sql1 = "select  p.sales_orderno , p.sales_lineno , p.stock_no , p.suppno , s.supp_name, p.order_qty, p.unit_price, " +
                  "  p.order_qty * p.unit_price as value," +
                  "  p.order_date , p.due_date, p.authority_code, p.user_status5, h.custno ,c.team_name, c.teamid " +
                  "  from  mrppurprop p" +
                  "      inner join ordhead h" +
                  "          on p.compno = h.compno" +
                  "          and p.sales_orderno = h.orderno" +
                  "      inner join supplier s" +
                  "          on  p.compno = s.compno" +
                  "          and p.suppno = s.suppno" +
                  "      left outer join bbs_cs_team c" +
                  "          on c.teamid = get_team(h.compno , h.custno, h.wareno)" +
                  "  where p.index_fld3 = '0'" +
                  "  and   p.compno = 1" +
                  "  and   p.req_status = 4" +
                  "  and   p.sales_orderno != ''" +
                  "  into temp ppos with no log";


            string sql3 = "select * from ppos where teamid = " + Id + ";";

            d.executeSQL(sql1);
            var result2 = d.getDataset(sql3);

            d.closeConnection();

            p.Data = result2;

            return View(p);
        }
Exemple #6
0
        public ActionResult EDI()
        {
            TeamSummary p = new TeamSummary();

            List<string> l = new List<string>();
            l.Add("Customer Experience");
            l.Add("EDI Rejections Team Summary");

            List<string> l2 = new List<string>();
            l2.Add("/Home/Index");
            l2.Add("/Home/EDI");

            p.Header = getHeaderInfo(l, l2, "EDI Rejections", "");

            string s1 = "set isolation to dirty read;";
            string s2 = "set lock mode to wait;";

            string sql1 = "select genno , a.error_ind " +
                    "from swedierrlog a " +
                    "where message_type = 11 " +
                    "and genno > 10000000 " +
                    " and error_ind is not null " +
                    "and message_date = today " +
                    "and a.message not like 'WARNING%' " +
                    "and a.message not like '%Invoice%' " +
                    "into temp rejections_mn with no log; ";
                    
            string sql3 = "select  cs.team_name, cs.teamid ," +
                          "          count(genno) as rejections, " +
                          "          count(distinct c.doc_no) as lines " +
                          "  from rejections_mn a " +
                          "      inner join edisop_head b " +
                          "          on b.doc_no = a.genno " +
                          "      left outer join edisop_line c " +
                          "          on a.genno = c.doc_no " +
                          "          and a.error_ind = c.line_no " +
                          "      left outer join bbs_cs_team cs " +
                          "          on cs.teamid = get_team(b.compno , b.cust_acc_code, b.wareno) " +
                          "  group by team_name, teamid " +
                          "  ;";
            
            Database d = new Database();
            d.openConnection();

            d.executeSQL(s1);
            d.executeSQL(s2);

            d.executeSQL(sql1);
            
            var result1 = d.getDataset(sql3);
            

            p.Data = result1;

            d.closeConnection();

            return View(p);
        }
Exemple #7
0
        public ActionResult HeldOrders()
        {
            TeamSummary p = new TeamSummary();
            
            List<string> l = new List<string>();
            l.Add("Customer Experience");
            l.Add("Held Orders Team Summary");

            List<string> l2 = new List<string>();
            l2.Add("/Home/Index");
            l2.Add("/Home/HeldOrders");

            p.Header = getHeaderInfo(l, l2, "Held Orders", "");

            string s1 = "set isolation to dirty read;";
            string s2 = "set lock mode to wait;";

            string sql1 = "select h.custno, c.cust_name , h.orderno , l.line_no , l.stock_no , l.stock_desc, "+
                          "  l.order_qty , h.order_status as headerStatus , l.order_status as lineStatus, h.wareno, h.compno "+
                          "  from ordhead h inner join ordline l "+
                          "      on h.compno = l.compno "+
                          "      and h.orderno = l.orderno "+
                          "  inner join customer c "+
                          "      on h.compno = c.compno "+
                          "      and h.custno = c.custno "+
                          "  where h.compno = 1 "+
                          "  and h.wareno in (select wareno from waredesc) "+
                          "  and h.order_date > today - 2 "+
                          "  and l.order_status = 0 "+
                          "  into temp heldOrders with no log; ";
                   

            string sql2 =  "select team_name , teamid, count(distinct orderno) as orders,  count(*) as lines "+
                           "     from heldOrders h "+
                           "         left outer join bbs_cs_team cs "+
                           "             on cs.teamid = get_team(h.compno , h.custno, h.wareno) "+
                           "     group by team_name, teamid";


            string sql3 = "select * from heldOrders";

            Database d = new Database();
            d.openConnection();

            d.executeSQL(s1);
            d.executeSQL(s2);

            d.executeSQL(sql1);
            var result1 = d.getDataset(sql2);

            p.Data = result1;

            d.closeConnection();

            return View(p);
        }
Exemple #8
0
        public ActionResult PPO()
        {
            TeamSummary p = new TeamSummary();

            List<string> l = new List<string>();
            l.Add("Customer Experience");
            l.Add("PPO Awaiting Authorisation Team Summary");

            List<string> l2 = new List<string>();
            l2.Add("/Home/Index");
            l2.Add("/Home/PPO");

            p.Header = getHeaderInfo(l, l2, "PPO Awaiting Authorisation", "Team Summary");

            string s1 = "set isolation to dirty read;";
            string s2 = "set lock mode to wait;";

            string sql1 = "select  p.sales_orderno , p.sales_lineno , p.stock_no , p.suppno , s.supp_name, p.order_qty, p.unit_price, " +
                  "  p.order_qty * p.unit_price as value," +
                  "  p.order_date , p.due_date, p.authority_code, p.user_status5, h.custno ,c.team_name, c.teamid " +
                  "  from  mrppurprop p" +
                  "      inner join ordhead h" +
                  "          on p.compno = h.compno" +
                  "          and p.sales_orderno = h.orderno" +
                  "      inner join supplier s" +
                  "          on  p.compno = s.compno" +
                  "          and p.suppno = s.suppno" +
                  "      left outer join bbs_cs_team c" +
                  "          on c.teamid = get_team(h.compno , h.custno, h.wareno)" +
                  "  where p.index_fld3 = '0'" +
                  "  and   p.compno = 1" +
                  "  and   p.req_status = 4" +
                  "  and   p.sales_orderno != ''" +
                  "  into temp ppos with no log";


            string sql2 = "select team_name , teamid , count(distinct sales_orderno) as orders , count(*) as lines" +
                           "  from ppos" +
                           "   group by team_name, teamid";


            Database d = new Database();
            d.openConnection();

            d.executeSQL(s1);
            d.executeSQL(s2);

            d.executeSQL(sql1);
            var result1 = d.getDataset(sql2);
            

            d.closeConnection();

            p.Data = result1;

            return View(p);
        }