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