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();
}
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);
//}
}
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();
}
