Beispiel #1
0
        public ActionResult FinishBillList(string mblno, int pol_id = 0, int pod_id = 0, int pageIndex = 1, int pageSize = 15)
        {
           
                var userinfo = ViewBag.UserInfo as Sealong.Web.Security.MembershipUser;
                var UserID = new Membership().GetUserID(userinfo.ProviderUserKey.ToString());


                ViewBag.MblNo = mblno;
                ViewBag.polid = pol_id;
                ViewBag.podid = pod_id;
                ViewBag.PageIndex = pageIndex;
                ViewBag.pageSize = pageSize;

                var factory = Sealong.Data.SqlClientFactory.CreateInstance();
                var client = factory.CreateSqlClient();
                var parameters = client.CreateParameters();
                StringBuilder sql = new StringBuilder();
                String selectClause = "SELECT s.mblno,s.pod,s.pol,s.etd,s.LaneID,s.Placedest,s.CarrierID,s.Voy,s.Vsl,n.SName Lane,c.Sname Carrier";
                selectClause += ",Sum(i.FcyAMT) OFAmt from seabills s ";
                selectClause += @" left join seabillsitems i on (s.sysid=i.sysid and s.EntityID=i.EntityID and i.dc='D' and i.ItemID=145)";
                selectClause += " left join Corps c on (c.sysid=c.sysid and c.CorpID=s.CarrierID)";
                selectClause += " left join Lanes n on (n.sysid=s.sysid and n.LaneID=s.LaneID)";
                sql.AppendLine(selectClause);
                String whereClause = "WHERE s.SysID=1 ";
                if (!String.IsNullOrEmpty(mblno) && !String.IsNullOrWhiteSpace(mblno))
                {
                    whereClause += "and s.mblno containing @mblno";
                    parameters.Add(client.CreateParameter("@mblno", mblno));
                }
                if (pol_id > 0)
                {
                    whereClause += " and s.polid=@polid";
                    parameters.Add(client.CreateParameter("@polid", pol_id));
                }
                if (pod_id > 0)
                {
                    whereClause += " and s.podid=@podid";
                    parameters.Add(client.CreateParameter("@podid", pod_id));
                }

                whereClause += " group by  s.mblno,s.pod,s.pol,s.etd,s.LaneID,s.Placedest,s.CarrierID,s.Voy,s.Vsl,n.SName,c.Sname ";
                sql.AppendLine(whereClause);
                DataTable  DataSource = null;// IEnumerable<dynamic> =可以不用事例化  new dynamic[1]
                ViewBag.Page = Pager.Create(sql.ToString(), "FinishBillList?pageIndex={{pageIndex}}&pageSize={{pageSize}}", out DataSource, parameters, pageIndex, pageSize, pageSize, "上一页", "下一页", "首页", "尾页");

                if (DataSource!= null && !DataSource.IsNullOrEmpty())
                {
                   ViewBag.DataSource = DataSource;
                }
                else
                {
                    ViewBag.DataSource = new DataTable() ;
                }
                

                return View();
        }
Beispiel #2
0
        public ActionResult Freight(String pol, String pod, String carrier, String lane, int pol_id = 0, int pod_id = 0, int carrier_id = 0, int lane_id = 0, int pageIndex = 1, int pageSize = 10)
        {
            ViewBag.POL = pol;
            ViewBag.POD = pod;
            ViewBag.Carrier = carrier;
            ViewBag.Lane = lane;
            ViewBag.PolID = pol_id;
            ViewBag.PodID = pod_id;
            ViewBag.CarrierID = carrier_id;
            ViewBag.LaneID = lane_id;
            ViewBag.PageIndex = pageIndex;
            //var userStatusName = System.Configuration.ConfigurationManager.AppSettings["UserStatusName"];
            //if (String.IsNullOrEmpty(userStatusName)) userStatusName = "memberKey";
            //ViewBag.UserInfo = Session[userStatusName];
            //if (ViewBag.UserInfo != null)
            //{
                #region 判断星期
                List<String> weeks = new List<string>();
                if (Request["w0"] == "on")
                {
                    weeks.Add("周一");
                }
                if (Request["w1"] == "on")
                {
                    weeks.Add("周二");
                }
                if (Request["w2"] == "on")
                {
                    weeks.Add("周三");
                }
                if (Request["w3"] == "on")
                {
                    weeks.Add("周四");
                }
                if (Request["w4"] == "on")
                {
                    weeks.Add("周五");
                }
                if (Request["w5"] == "on")
                {
                    weeks.Add("周六");
                }
                if (Request["w6"] == "on")
                {
                    weeks.Add("周日");
                }

                #endregion

                //#region 取数量
                //var totalCount = ViewModels.Prices.Count(pol_id: pol_id, pod_id: pod_id, carrier_id: carrier_id, lane_id: lane_id, week: weeks.Count > 0 ? weeks.ToArray() : null);
                //var totalPages = (int)Math.Ceiling((double)totalCount / pageSize);
                //ViewBag.TotalPages = totalPages;
                //#endregion

                #region 取运价列表
                int totalCount = 0;
                var entities = ViewModels.Prices.GetEntities(out totalCount, pol_id: pol_id, pod_id: pod_id, carrier_id: carrier_id, lane_id: lane_id, pageIndex: pageIndex, pageSize: pageSize, week: weeks.Count > 0 ? weeks.ToArray() : null);
                var totalPages = (int)Math.Ceiling((double)totalCount / pageSize);
                ViewBag.TotalPages = totalPages;

                #endregion


                var sealongUserName = Models.Web_UserProfile.GetUserName(ViewBag.UserInfo.ProviderUserKey.ToString());
                ViewBag.SealongUserName = sealongUserName;
                var userid = new Membership().GetUserID(ViewBag.UserInfo.ProviderUserKey);
                var selfEntities = ViewModels.Prices.GetEntities(out totalCount, pol_id: pol_id, pod_id: pod_id, carrier_id: carrier_id, lane_id: lane_id, pageIndex: pageIndex, pageSize: pageSize, week: weeks.Count > 0 ? weeks.ToArray() : null, userid: userid);
                ViewBag.selfEntities = selfEntities;

                return View(entities);
            //}
            //else
            //{
            //    return Redirect("/Login?returnUrl=" + Request.Url.AbsolutePath);
            //}
        }
Beispiel #3
0
        public ActionResult BookingList(String bookingdateFrom, String bookingdateTo, string mblno, string status, int pageIndex = 1, int pageSize = 15)
        {

            var userinfo = ViewBag.UserInfo as Sealong.Web.Security.MembershipUser;
            var UserID = new Membership().GetUserID(userinfo.ProviderUserKey.ToString());


            ViewBag.MblNo = mblno;
            ViewBag.bookingdateFrom = bookingdateFrom;
            ViewBag.bookingdateTo = bookingdateTo;
            ViewBag.status = status;
            ViewBag.PageIndex = pageIndex;
            ViewBag.pageSize = pageSize;

            DateTime fromdate = DateTime.MinValue;
            DateTime todate = DateTime.MaxValue;

            var factory = SqlClientFactory.Create();
            var client = factory.CreateSqlClient();
            var parameters = client.CreateParameters();

            ///*  获取登录用户关联的corpid */
            //StringBuilder GetCorpIDSQL = new StringBuilder();
            //GetCorpIDSQL.AppendLine("SELECT CORPID FROM WEB_USERSINCORPS WHERE SYSID=1 AND USERID=@USERID");
            //parameters.Add(client.CreateParameter("@USERID", UserID));
            //int CorpID = client.RunSql(GetCorpIDSQL.ToString(), parameters).TryGet().TryGet().TryGet<int>("CorpID");


            ///*  countSql 获取总行数 */
            //StringBuilder countSql = new StringBuilder();
            //countSql.AppendLine("SELECT COUNT(SYSID) TCOUNT FROM SEABILLS WHERE SYSID=1");
            StringBuilder detailSql = new StringBuilder();

            /*  detailSql 获取列表明细 */
            detailSql.AppendLine("select s.Entityid,s.BillNo,s.mblno,s.etd,s.eta,s.pol,s.pod,s.PLACEDEST,s.containerqty,p.CName POLSName,d.CName PODSName");
            detailSql.AppendLine(",s.vsl,s.voy,s.Teu,v.CName VSLCName,sum(i.FcyAmt) OFAmt");
            detailSql.AppendLine("from seabills s");
            detailSql.AppendLine("left join seabillsitems i on (i.sysid=s.sysid and i.entityid=s.entityid and i.dc='D' and i.ItemID=145 )");
            detailSql.AppendLine("left join ports p on (p.sysid=s.sysid and p.portID=s.PolID)");
            detailSql.AppendLine("left join ports d on (d.sysid=s.sysid and d.portID=s.PodID)");
            detailSql.AppendLine("left join VESSELS v on (v.sysid=s.sysid and v.EName=s.VSL)");
            detailSql.AppendLine(@"LEFT JOIN CORPS C ON C.SYSID=S.SYSID AND C.CORPID=S.CORPID");
            detailSql.AppendLine(@"LEFT JOIN WEB_USERSINCORPS UC ON UC.SYSID=S.SYSID AND UC.CORPID=C.CORPID");
            detailSql.AppendLine(@"LEFT JOIN WEB_LOGINUSERS U ON U.SYSID=S.SYSID AND U.USERID=UC.USERID");

            detailSql.AppendLine("WHERE s.SYSID=1 AND U.USERNO=@USERNO");
            if (DateTime.TryParse(bookingdateFrom, out fromdate) && DateTime.TryParse(bookingdateTo, out todate))
            {
                detailSql.AppendLine("and s.BillDate Between @bookingdateFrom and @bookingdateTo");
                parameters.Add(client.CreateParameter("@bookingdateFrom", fromdate));
                parameters.Add(client.CreateParameter("@bookingdateTo", todate));
            }

            if (!String.IsNullOrEmpty(mblno) && !String.IsNullOrWhiteSpace(mblno))
            {
                detailSql.AppendLine("and s.mblno containing @mblno");
                parameters.Add(client.CreateParameter("@mblno", mblno));
            }
            detailSql.AppendLine("group by s.billno,s.Entityid,s.mblno,s.etd,s.eta,s.pol,s.pod,s.PLACEDEST,s.containerqty,p.CName,d.CName,s.vsl,s.voy,s.Teu,v.CName");

            parameters.Add(client.CreateParameter("@USERNO", ViewBag.UserInfo.ProviderUserKey));

            var dataSource = new DataTable();
            var pager = Pager.Create(detailSql.ToString(), Url.Action("BookingList", new { paheIndex="{{pageIndex}}",pageSize="{{pageSize}}" }), out dataSource, parameters, pageIndex, pageSize);
            ViewBag.DataSource = dataSource;
            ViewBag.Pager = pager;
            return View();
        }