Пример #1
0
        public ActionResult Index(int pageIndex = 1, int pageSize = 20)
        {
            var AccYear = String.IsNullOrEmpty(Request["accyear"]) ? DateTime.Now.Year : Convert.ToInt32(Request["accyear"]);
            var AccMonth = String.IsNullOrEmpty(Request["accmonth"]) ? DateTime.Now.Month : Convert.ToInt32(Request["accmonth"]);

            if (AccMonth < 0 || AccMonth > 12)
                AccMonth = DateTime.Now.Month;
            if (AccYear < 1970 || AccYear > 3000)
                AccYear = DateTime.Now.Year;

            ViewBag.AccMonth = AccMonth;
            ViewBag.AccYear = AccYear;

            var sealonguserid = Models.Web_UserProfile.GetUserID(ViewBag.UserInfo.ProviderUserKey.ToString());//取雇员ID
            var sealongusername = Models.Web_UserProfile.GetUserName(ViewBag.UserInfo.ProviderUserKey.ToString());//取雇员姓名

            var client = this.SqlClient();
            var parameters = client.CreateParameters();
            parameters.Add(client.CreateParameter("@SALESID", sealonguserid));
            parameters.Add(client.CreateParameter("@STARTDATE", new DateTime(AccYear, AccMonth, 1)));
            parameters.Add(client.CreateParameter("@ENDDATE", new DateTime(AccYear, AccMonth, 1).AddMonths(1).Date.AddMilliseconds(-1)));

            #region 取总利润
            StringBuilder expectProfitClause = new StringBuilder();
            expectProfitClause.AppendLine(@"SELECT SUM(SD.FCYAMT-SC.FCYAMT) EXPECT");
            expectProfitClause.AppendLine(@"FROM SEABILLS S");
            expectProfitClause.AppendLine(@"LEFT JOIN SEABILLSITEMS SD ON SD.SYSID=S.SYSID AND SD.ENTITYID=S.ENTITYID AND SD.DC='D'");
            expectProfitClause.AppendLine(@"LEFT JOIN SEABILLSITEMS SC ON SC.SYSID=S.SYSID AND SC.ENTITYID=S.ENTITYID AND SC.DC='C'");
            expectProfitClause.AppendLine(@"LEFT JOIN ITEMS ID ON ID.SYSID=S.SYSID AND ID.ITEMID=SD.ITEMID");
            expectProfitClause.AppendLine(@"LEFT JOIN ITEMS IC ON IC.SYSID=S.SYSID AND IC.ITEMID=SC.ITEMID");
            expectProfitClause.AppendLine(@"WHERE S.SYSID=1 AND ID.ITEMNO='OF' AND IC.ITEMNO='OF' AND S.SALESID=@SALESID");
            expectProfitClause.AppendLine(@"AND S.BILLDATE BETWEEN @STARTDATE AND @ENDDATE");
            #endregion

            #region 取总箱贴
            StringBuilder expectCntrClause = new StringBuilder();
            expectCntrClause.AppendLine(@"SELECT COALESCE(SUM(SC.FCYAMT) -((MIN(P.V20)* SUM(BQ20.CNTRQTY))");
            expectCntrClause.AppendLine(@"+(MIN(P.V40)* SUM(BQ40.CNTRQTY))");
            expectCntrClause.AppendLine(@"+(MIN(P.V40HQ)* SUM(BQ40HQ.CNTRQTY))),0) EXPECT");
            expectCntrClause.AppendLine(@"FROM PRICEMNGGENLEGITEMS P");
            expectCntrClause.AppendLine(@"LEFT JOIN SEABILLS S ON S.SYSID=P.SYSID AND S.EXSALESID=P.USERID AND S.POLID=S.POLID AND S.PODID=S.PODID AND S.CARRIERID=P.CARRIERID");
            expectCntrClause.AppendLine(@"LEFT JOIN BSCNTRQTY BQ20 ON BQ20.SYSID=S.SYSID AND BQ20.ENTITYID=S.ENTITYID");
            expectCntrClause.AppendLine(@"LEFT JOIN CNTRS CN20 ON CN20.SYSID=S.SYSID AND CN20.CNTRID=BQ20.CNTRTYPEID");
            expectCntrClause.AppendLine(@"LEFT JOIN BSCNTRQTY BQ40 ON BQ40.SYSID=S.SYSID AND BQ40.ENTITYID=S.ENTITYID");
            expectCntrClause.AppendLine(@"LEFT JOIN CNTRS CN40 ON CN40.SYSID=S.SYSID AND CN40.CNTRID=BQ40.CNTRTYPEID");
            expectCntrClause.AppendLine(@"LEFT JOIN BSCNTRQTY BQ40HQ ON BQ40HQ.SYSID=S.SYSID AND BQ40HQ.ENTITYID=S.ENTITYID");
            expectCntrClause.AppendLine(@"LEFT JOIN CNTRS CN40HQ ON CN40HQ.SYSID=S.SYSID AND CN40HQ.CNTRID=BQ40HQ.CNTRTYPEID");
            expectCntrClause.AppendLine(@"LEFT JOIN SEABILLSITEMS SC ON SC.SYSID=S.SYSID AND SC.ENTITYID=S.ENTITYID AND SC.DC='C'");
            expectCntrClause.AppendLine(@"LEFT JOIN ITEMS IC ON IC.SYSID=S.SYSID AND IC.ITEMID=SC.ITEMID");
            expectCntrClause.AppendLine(@"LEFT JOIN WEB_LOGINUSERS U ON U.SYSID=S.SYSID AND U.USERID=S.EXSALESID");
            expectCntrClause.AppendLine(@"WHERE S.SYSID=1 AND IC.ITEMNO='OF' AND P.CREATEDATE<=S.BILLDATE AND P.INUREFROM<=S.BILLDATE AND P.INURETO>=S.BILLDATE");
            expectCntrClause.AppendLine(@"AND U.USERNO=@USERNO AND S.BILLDATE BETWEEN @STARTDATE AND @ENDDATE");

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


            #region 取计提和箱贴明细
            StringBuilder selectClause = new StringBuilder();
            selectClause.AppendLine(@"SELECT SYSID,EXPECT,MBLNO,BILLDATE,ETD,POL,POD,BSSRCID,BILLNO,ACCYEAR,ACCMONTH,SOURCE ");
            selectClause.AppendLine(@"FROM");
            selectClause.AppendLine(@"(SELECT coalesce(SUM(SD0.FCYAMT-SC0.FCYAMT),0) EXPECT");
            selectClause.AppendLine(@",S0.SYSID,S0.MBLNO,S0.BILLDATE,S0.ETD,S0.POL,S0.POD,S0.BSSRCID,S0.BILLNO,S0.ACCYEAR,S0.ACCMONTH,'计提' SOURCE");
            selectClause.AppendLine(@"FROM SEABILLS S0");
            selectClause.AppendLine(@"LEFT JOIN SEABILLSITEMS SD0 ON SD0.SYSID=S0.SYSID AND SD0.ENTITYID=S0.ENTITYID AND SD0.DC='D'");
            selectClause.AppendLine(@"LEFT JOIN SEABILLSITEMS SC0 ON SC0.SYSID=S0.SYSID AND SC0.ENTITYID=S0.ENTITYID AND SC0.DC='C'");
            selectClause.AppendLine(@"LEFT JOIN ITEMS ID0 ON ID0.SYSID=S0.SYSID AND ID0.ITEMID=SD0.ITEMID");
            selectClause.AppendLine(@"LEFT JOIN ITEMS IC0 ON IC0.SYSID=S0.SYSID AND IC0.ITEMID=SC0.ITEMID");
            selectClause.AppendLine(@"WHERE S0.SYSID=1 AND ID0.ITEMNO='OF' AND IC0.ITEMNO='OF' AND S0.SALESID=@SALESID AND S0.BILLDATE BETWEEN @STARTDATE AND @ENDDATE");
            selectClause.AppendLine(@"GROUP BY S0.SYSID,S0.MBLNO,S0.BILLDATE,S0.ETD,S0.POL,S0.POD,S0.BSSRCID,S0.BILLNO,S0.ACCYEAR,S0.ACCMONTH,SOURCE");
            selectClause.AppendLine(@"UNION");
            selectClause.AppendLine(@"SELECT COALESCE(SUM(SC1.FCYAMT) -((MIN(P1.V20)* SUM(BQ201.CNTRQTY))");
            selectClause.AppendLine(@"+(MIN(P1.V40)* SUM(BQ401.CNTRQTY))");
            selectClause.AppendLine(@"+(MIN(P1.V40HQ)* SUM(BQ40HQ1.CNTRQTY))),0) EXPECT");
            selectClause.AppendLine(@",S1.SYSID,S1.MBLNO,S1.BILLDATE,S1.ETD,S1.POL,S1.POD,S1.BSSRCID,S1.BILLNO,S1.ACCYEAR,S1.ACCMONTH,'箱贴' SOURCE");
            selectClause.AppendLine(@"FROM PRICEMNGGENLEGITEMS P1");
            selectClause.AppendLine(@"LEFT JOIN SEABILLS S1 ON S1.SYSID=P1.SYSID AND S1.EXSALESID=P1.USERID AND S1.POLID=S1.POLID AND S1.PODID=S1.PODID AND S1.CARRIERID=P1.CARRIERID");
            selectClause.AppendLine(@"LEFT JOIN BSCNTRQTY BQ201 ON BQ201.SYSID=S1.SYSID AND BQ201.ENTITYID=S1.ENTITYID");
            selectClause.AppendLine(@"LEFT JOIN CNTRS CN201 ON CN201.SYSID=S1.SYSID AND CN201.CNTRID=BQ201.CNTRTYPEID");
            selectClause.AppendLine(@"LEFT JOIN BSCNTRQTY BQ401 ON BQ401.SYSID=S1.SYSID AND BQ401.ENTITYID=S1.ENTITYID");
            selectClause.AppendLine(@"LEFT JOIN CNTRS CN401 ON CN401.SYSID=S1.SYSID AND CN401.CNTRID=BQ401.CNTRTYPEID");
            selectClause.AppendLine(@"LEFT JOIN BSCNTRQTY BQ40HQ1 ON BQ40HQ1.SYSID=S1.SYSID AND BQ40HQ1.ENTITYID=S1.ENTITYID");
            selectClause.AppendLine(@"LEFT JOIN CNTRS CN40HQ1 ON CN40HQ1.SYSID=S1.SYSID AND CN40HQ1.CNTRID=BQ40HQ1.CNTRTYPEID");
            selectClause.AppendLine(@"LEFT JOIN SEABILLSITEMS SC1 ON SC1.SYSID=S1.SYSID AND SC1.ENTITYID=S1.ENTITYID AND SC1.DC='C'");
            selectClause.AppendLine(@"LEFT JOIN ITEMS IC1 ON IC1.SYSID=S1.SYSID AND IC1.ITEMID=SC1.ITEMID");
            selectClause.AppendLine(@"LEFT JOIN WEB_LOGINUSERS U1 ON U1.SYSID=S1.SYSID AND U1.USERID=S1.EXSALESID");
            selectClause.AppendLine(@"WHERE S1.SYSID=1 AND IC1.ITEMNO='OF' AND P1.CREATEDATE<=S1.BILLDATE AND P1.INUREFROM<=S1.BILLDATE AND P1.INURETO>=S1.BILLDATE");
            selectClause.AppendLine(@"AND U1.USERNO=@USERNO AND S1.BILLDATE BETWEEN @STARTDATE AND @ENDDATE");
            selectClause.AppendLine(@"GROUP BY S1.SYSID,S1.MBLNO,S1.BILLDATE,S1.ETD,S1.POL,S1.POD,S1.BSSRCID,S1.BILLNO,S1.ACCYEAR,S1.ACCMONTH,SOURCE)");
            selectClause.AppendLine(@"GROUP BY SYSID,EXPECT,MBLNO,BILLDATE,ETD,POL,POD,BSSRCID,BILLNO,ACCYEAR,ACCMONTH,SOURCE");
            selectClause.AppendLine(@"ORDER BY BILLDATE DESC");
            #endregion

            #region 取计提和箱贴明细的统计数量
            var countClause = selectClause.ToString().Replace("SELECT *", "SELECT COUNT(SYSID) ");
            #endregion

            //由于此页面逻辑较为复杂,这里使用标准ADO.NET方式处理数据查询
            //定义局部变量
            Decimal expectProfit = 0.0M;//预计利润
            Decimal expectCntr = 0.0M;//预计箱贴
            int totalRows = 0;
            //开始计算分页
            Double totalPages = Math.Ceiling((Double)totalRows / pageSize);//总页数,Ceiling向上取整
            int rowStart = (pageIndex - 1) * pageSize + 1;
            int rowEnd = pageSize * pageIndex;
            selectClause.AppendLine("ROWS @ROWSTART TO @ROWEND ");
            parameters.Add(client.CreateParameter("@ROWSTART", rowStart));
            parameters.Add(client.CreateParameter("@ROWEND", rowEnd));
            //准备连接
            System.Data.IDbConnection conn = client.CreateDbConnection(false);
            var expectProfitCommand = client.CreateDbCommand(expectProfitClause.ToString(), parameters, conn);
            var expectCntrCommand = client.CreateDbCommand(expectCntrClause.ToString(), parameters, conn);
            var countCommand = client.CreateDbCommand(countClause.ToString(), parameters, conn);
            var selectCommand = client.CreateDbCommand(selectClause.ToString(), parameters, conn);
            DataSet dataSet = new DataSet();
            conn.Open();//打开连接
            var __expect_profit = expectProfitCommand.ExecuteScalar();
            if (__expect_profit != null)
            {
                try
                {
                    expectProfit = Convert.ToDecimal(__expect_profit);
                }
                catch { expectProfit = 0.0M; }
            }

            var __expect_cntr = expectCntrCommand.ExecuteScalar();
            if (__expect_cntr != null)
            {
                try
                {
                    expectCntr = Convert.ToDecimal(__expect_cntr);
                }
                catch { expectCntr = 0.0M; }
            }

            var __count = countCommand.ExecuteScalar();
            if (__count != null)
            {
                try
                {
                    totalRows = Convert.ToInt32(__count);
                }
                catch { totalRows = 0; }
            }

            var adapter = client.CreateAdapter(selectCommand);
            adapter.Fill(dataSet);

            var entities = dataSet.TryGet().GetDynamicCollection();

            

            if(conn!=null && conn.State!= ConnectionState.Closed)
            {
                conn.Close();
            }
            var pager = Pager.Create(totalPages, "/Performance/Home/Index?pageIndex={{pageIndex}}&pageSize={{pageSize}}", pageIndex, pageSize);

            ViewBag.AccYears = entities.Select(x => x.AccYear).Distinct();
            ViewBag.AccMonths = entities.Select(x => x.AccMonth).Distinct();

            ViewBag.ExpectProfit = expectProfit;
            ViewBag.ExpectCntr = expectCntr;
            ViewBag.SealongUserName = sealongusername;
            ViewBag.Pager = pager;
            return View(entities);
        }