protected void btnNew_Click(object sender, EventArgs e)
{
string name = 级单位.SelectedText;
int 用户二级部门ID = Convert.ToInt32(Session["二级部门ID"]);
string str盘点类型 = 盘点类型.SelectedText;
int 盘点主表ID = Convert.ToInt32(盘点名称.SelectedValue);
List <Model录入盘点信息> moList = bll.查询已盘点数据(盘点主表ID, 用户二级部门ID, "", "全部", str盘点类型, Grid1.PageIndex, Grid1.PageSize);
DataTable dt = ToDataTable(moList);
NpoiHelper1.DownloadExcel(dt, NpoiHelper1.ExcelType.xls);
}
protected void ExcelOutBtn_Click(object sender, EventArgs e)
{
string Company = Company_Name.SelectedValue;
DateTime dtime = DateTime.Now;
int a = Convert.ToInt32(dtime.ToString("yyyy"));
int b = a - 2015;
int c = 0;
int lastyear = a - 1;
StringBuilder Tiaojian1 = new StringBuilder();
StringBuilder Tiaojian2 = new StringBuilder();
StringBuilder Tiaojian3 = new StringBuilder();
StringBuilder Tiaojian4 = new StringBuilder();
StringBuilder Tiaojian5 = new StringBuilder();
StringBuilder Tiaojian6 = new StringBuilder();
StringBuilder Tiaojian7 = new StringBuilder();
StringBuilder Tiaojian8 = new StringBuilder();
Tiaojian8.AppendFormat("case when bc.y{0}>bc.y{1} THEN bc.y{0}*bc.newPrice ELSE bc.y{1}*bc.newPrice END AS 新总价,case when bc.y{0} > bc.y{1} THEN bc.y{0} ELSE bc.y{1} END AS 新数量", lastyear, a);
int year = 0;
for (int k = 0; k <= b; k++)
{
if (k == b)
{
year = 2015 + k;
Tiaojian1.AppendFormat("{0}", "y" + year);
Tiaojian2.AppendFormat("ISNULL(sum(sa.{0}),0)", "y" + year);
Tiaojian3.AppendFormat("py.{0} as price{1}", "y" + year, year);
Tiaojian4.AppendFormat("ISNULL(sum(sa.{0}),0) as {0}", "y" + year);
Tiaojian5.AppendFormat("ypc.{0}", "price" + year);
Tiaojian6.AppendFormat("sum(ypc.price{0}) as {1}", year, "price" + year);
Tiaojian7.AppendFormat("ISNULL(sum(ypc.price{0}),0)", year);
}
else
{
year = 2015 + k;
Tiaojian1.AppendFormat("{0},", "y" + year);
Tiaojian2.AppendFormat("ISNULL(sum(sa.{0}),0)+", "y" + year);
Tiaojian3.AppendFormat("py.{0} as price{1},", "y" + year, year);
Tiaojian4.AppendFormat("ISNULL(sum(sa.{0}),0) as {0},", "y" + year);
Tiaojian5.AppendFormat("ypc.{0},", "price" + year);
Tiaojian6.AppendFormat("sum(ypc.price{0}) as {1},", year, "price" + year);
Tiaojian7.AppendFormat("ISNULL(sum(ypc.price{0}),0)+", year);
}
}
string StrWhere1 = Tiaojian1.ToString();
string StrWhere2 = Tiaojian2.ToString();
string StrWhere3 = Tiaojian3.ToString();
string StrWhere4 = Tiaojian4.ToString();
string StrWhere5 = Tiaojian5.ToString();
string StrWhere6 = Tiaojian6.ToString();
string StrWhere7 = Tiaojian7.ToString();
string StrWhere8 = Tiaojian8.ToString();
string Sql = @"WITH mytabCte AS (
SELECT SUM(b.操作数量) AS 总操作数量,
a.价格,
'y' + CONVERT(VARCHAR(50), YEAR(b.操作日期)) AS 年份,
a.物料号,
a.提报单位,
c.备件名称,
(
SELECT TOP 1 c.价格
FROM b_备件_导入日志表 AS c
WHERE c.物料号 = a.物料号
ORDER BY
c.[发料日期] DESC
) AS newPrice
FROM b_备件_导入日志表 a,
b_备件_记录表 b,
b_备件_信息表 c
WHERE a.ID = b.日志ID AND a.物料号=c.物料号
AND a.提报单位 = '" + Company + @"'
AND YEAR(b.操作日期) > 2014
GROUP BY
YEAR(b.操作日期),
a.物料号,
a.价格,
a.提报单位,
c.备件名称
),
cte AS (
SELECT YEAR(b.操作日期) yearNum,
b.操作数量 * a.价格 价格,
a.物料号,
a.提报单位,
c.备件名称
FROM b_备件_导入日志表 a,
b_备件_记录表 b,
b_备件_信息表 c
WHERE a.ID = b.日志ID AND a.物料号=c.物料号
AND a.提报单位 = '" + Company + @"'
AND YEAR(b.操作日期) > 2014
),
yearCte AS (
SELECT SUM(价格) AS 总价格,
'y' + CONVERT(VARCHAR(50), yearNum) AS 年份,
物料号,
提报单位,
备件名称
FROM cte
GROUP BY
yearNum,
物料号,
提报单位,
备件名称
),
yearPIVOTCte AS (
SELECT py.物料号,
py.提报单位,
py.备件名称,
" + StrWhere3 + @"
FROM yearCte
PIVOT(
SUM(yearCte.总价格) FOR yearCte.年份 IN (" + StrWhere1 + @")
) AS py
),
baseCte AS (
SELECT sa.物料号,
sa.提报单位,
Convert(DECIMAL(13,2),(
" + StrWhere7 + @"
) / (
" + StrWhere2 + @"
) ) as newPrice,
sa.备件名称,
" + StrWhere4 + @",
(
" + StrWhere2 + @"
) AS 总操作数量,
" + StrWhere6 + @",
(
" + StrWhere7 + @"
) AS 总价
FROM mytabCte
PIVOT(
SUM(mytabCte.总操作数量) FOR mytabCte.年份 IN (" + StrWhere1 + @")
) AS sa
LEFT JOIN yearPIVOTCte ypc
ON ypc.物料号 = sa.物料号
GROUP BY
sa.物料号,
sa.newPrice,
sa.提报单位,
sa.备件名称
)
SELECT bc.*," + StrWhere8 + @"
FROM baseCte AS bc
ORDER BY
物料号";
DataSet ds = bll.返回DataSet(Sql);
DataTable dt = ds.Tables[0];
NpoiHelper1.DownloadExcel(dt, NpoiHelper1.ExcelType.xls);
}