735 lines
34 KiB
C#
735 lines
34 KiB
C#
using BLL;
|
||
using System;
|
||
using System.Collections.Generic;
|
||
using System.Data;
|
||
using System.Data.SqlClient;
|
||
using System.Linq;
|
||
|
||
namespace FineUIPro.Web.common
|
||
{
|
||
public partial class mainMenu_HJGL3 : PageBase
|
||
{
|
||
protected void Page_Load(object sender, EventArgs e)
|
||
{
|
||
if (!IsPostBack)
|
||
{
|
||
this.InitTreeMenu();//加载树
|
||
this.txtWeldingDate.Text = string.Format("{0:yyyy-MM-dd}", DateTime.Now);
|
||
BindGrid1();
|
||
BindGrid3();
|
||
}
|
||
}
|
||
|
||
protected void WeldingDateText_OnTextChanged(object sender, EventArgs e)
|
||
{
|
||
BindGrid1();
|
||
BindGrid2(this.tvControlItem.SelectedNodeID);
|
||
BindGrid3();
|
||
}
|
||
|
||
#region 加载树装置-单位-工作区
|
||
/// <summary>
|
||
/// 加载树
|
||
/// 优化版本：解决 N+1 查询问题，一次性获取所有管线统计数据
|
||
/// </summary>
|
||
private void InitTreeMenu()
|
||
{
|
||
this.tvControlItem.Nodes.Clear();
|
||
|
||
TreeNode rootNode1 = new TreeNode();
|
||
rootNode1.NodeID = "1";
|
||
rootNode1.Text = "建筑工程";
|
||
rootNode1.CommandName = "建筑工程";
|
||
rootNode1.Selectable = false;
|
||
this.tvControlItem.Nodes.Add(rootNode1);
|
||
|
||
TreeNode rootNode2 = new TreeNode();
|
||
rootNode2.NodeID = "2";
|
||
rootNode2.Text = "安装工程";
|
||
rootNode2.CommandName = "安装工程";
|
||
rootNode2.Expanded = true;
|
||
this.tvControlItem.Nodes.Add(rootNode2);
|
||
|
||
// 优化：一次性获取所有管线统计数据，避免 N+1 查询
|
||
var pipelineCountByUnitWork = (from x in Funs.DB.View_HJGL_Pipeline
|
||
where x.ProjectId == this.CurrUser.LoginProjectId
|
||
group x by x.UnitWorkId into g
|
||
select new { UnitWorkId = g.Key, Count = g.Count(), TotalDin = g.Sum(x => x.TotalDin) })
|
||
.ToDictionary(x => x.UnitWorkId, x => new { x.Count, x.TotalDin });
|
||
|
||
var pUnits = (from x in Funs.DB.Project_ProjectUnit where x.ProjectId == this.CurrUser.LoginProjectId select x).ToList();
|
||
// 获取当前用户所在单位
|
||
var currUnit = pUnits.FirstOrDefault(x => x.UnitId == this.CurrUser.UnitId);
|
||
|
||
var unitWorkList = (from x in Funs.DB.WBS_UnitWork
|
||
where x.ProjectId == this.CurrUser.LoginProjectId
|
||
&& x.SuperUnitWork == null && x.UnitId != null && x.ProjectType != null
|
||
select x).ToList();
|
||
|
||
List<Model.WBS_UnitWork> unitWork1 = null;
|
||
List<Model.WBS_UnitWork> unitWork2 = null;
|
||
|
||
unitWork1 = (from x in unitWorkList where x.ProjectType == "1" select x).ToList();
|
||
unitWork2 = (from x in unitWorkList where x.ProjectType == "2" select x).ToList();
|
||
|
||
if (unitWork1.Count() > 0)
|
||
{
|
||
foreach (var q in unitWork1)
|
||
{
|
||
// 优化：从内存字典中获取管线数量，避免数据库查询
|
||
int a = pipelineCountByUnitWork.ContainsKey(q.UnitWorkId) ? pipelineCountByUnitWork[q.UnitWorkId].Count : 0;
|
||
var unitNamesUnitIds = BLL.UnitService.getUnitNamesUnitIds(q.UnitId);
|
||
TreeNode tn1 = new TreeNode();
|
||
tn1.NodeID = q.UnitWorkId;
|
||
tn1.Text = q.UnitWorkName + "【" + a.ToString() + "】" + "管线" ;
|
||
tn1.ToolTip = "施工单位：" + unitNamesUnitIds;
|
||
tn1.EnableClickEvent = true;
|
||
rootNode1.Nodes.Add(tn1);
|
||
}
|
||
}
|
||
if (unitWork2.Count() > 0)
|
||
{
|
||
foreach (var q in unitWork2)
|
||
{
|
||
// 优化：从内存字典中获取管线数量，避免数据库查询
|
||
int a = pipelineCountByUnitWork.ContainsKey(q.UnitWorkId) ? pipelineCountByUnitWork[q.UnitWorkId].Count : 0;
|
||
var unitNamesUnitIds = BLL.UnitService.getUnitNamesUnitIds(q.UnitId);
|
||
TreeNode tn2 = new TreeNode();
|
||
tn2.NodeID = q.UnitWorkId;
|
||
tn2.Text = q.UnitWorkName + "【" + a.ToString() + "】" + "管线" ;
|
||
tn2.ToolTip = "施工单位：" + unitNamesUnitIds;
|
||
tn2.EnableClickEvent = true;
|
||
rootNode2.Nodes.Add(tn2);
|
||
}
|
||
}
|
||
}
|
||
#endregion
|
||
|
||
#region 点击TreeView
|
||
/// <summary>
|
||
/// 点击TreeView
|
||
/// </summary>
|
||
/// <param name="sender"></param>
|
||
/// <param name="e"></param>
|
||
protected void tvControlItem_NodeCommand(object sender, TreeCommandEventArgs e)
|
||
{
|
||
if (this.tvControlItem.SelectedNodeID.Length > 1)
|
||
{
|
||
this.BindGrid2(this.tvControlItem.SelectedNodeID);
|
||
}
|
||
}
|
||
#endregion
|
||
|
||
#region 数据绑定
|
||
/// <summary>
|
||
/// 数据绑定 - 项目进度统计
|
||
/// 优化版本：使用 CTE 和 JOIN 替代嵌套子查询，提高性能
|
||
/// </summary>
|
||
private void BindGrid1()
|
||
{
|
||
string strSql = string.Empty;
|
||
List<SqlParameter> listStr = new List<SqlParameter>();
|
||
if (this.rbType.SelectedValue == "1")
|
||
{
|
||
// 优化：使用 CTE 和 LEFT JOIN 替代嵌套子查询
|
||
strSql = @"
|
||
WITH AllWeldJoints AS (
|
||
-- 所有焊口总量
|
||
SELECT ProjectId, SUM(ISNULL(Size, 0)) AS TotalDin
|
||
FROM dbo.HJGL_WeldJoint
|
||
WHERE ProjectId = @ProjectId
|
||
GROUP BY ProjectId
|
||
),
|
||
TodayWelding AS (
|
||
-- 当日焊接记录（返回 WeldingDailyId）
|
||
SELECT wd.WeldingDailyId
|
||
FROM dbo.HJGL_WeldingDaily wd
|
||
WHERE wd.ProjectId = @ProjectId AND wd.WeldingDate = @WeldingDate
|
||
),
|
||
TodayWeldJoints AS (
|
||
-- 当日完成焊口
|
||
SELECT jot.ProjectId, SUM(ISNULL(jot.Size, 0)) AS TodayFinishSize
|
||
FROM dbo.HJGL_WeldJoint jot
|
||
INNER JOIN TodayWelding tw ON jot.WeldingDailyId = tw.WeldingDailyId
|
||
WHERE jot.ProjectId = @ProjectId AND jot.WeldingDailyId IS NOT NULL
|
||
GROUP BY jot.ProjectId
|
||
),
|
||
CumulativeWeldJoints AS (
|
||
-- 累计完成焊口
|
||
SELECT jot.ProjectId, SUM(ISNULL(jot.Size, 0)) AS FinishSize
|
||
FROM dbo.HJGL_WeldJoint jot
|
||
INNER JOIN dbo.HJGL_WeldingDaily wd ON jot.WeldingDailyId = wd.WeldingDailyId
|
||
WHERE jot.ProjectId = @ProjectId AND jot.WeldingDailyId IS NOT NULL AND wd.WeldingDate <= @WeldingDate
|
||
GROUP BY jot.ProjectId
|
||
)
|
||
SELECT b.ProjectId,
|
||
ISNULL(aw.TotalDin, 0) AS TotalDin,
|
||
ISNULL(tw.TodayFinishSize, 0) AS TodayFinishSize,
|
||
ISNULL(cw.FinishSize, 0) AS FinishSize,
|
||
CONVERT(NVARCHAR(10), CAST(CASE ISNULL(aw.TotalDin, 0) WHEN 0 THEN 0
|
||
ELSE 100.0 * ISNULL(cw.FinishSize, 0) / (1.0 * aw.TotalDin) END AS DECIMAL(8, 2))) + '%' AS Rate
|
||
FROM Base_Project b
|
||
LEFT JOIN AllWeldJoints aw ON b.ProjectId = aw.ProjectId
|
||
LEFT JOIN TodayWeldJoints tw ON b.ProjectId = tw.ProjectId
|
||
LEFT JOIN CumulativeWeldJoints cw ON b.ProjectId = cw.ProjectId
|
||
WHERE b.ProjectId = @ProjectId";
|
||
listStr.Add(new SqlParameter("@ProjectId", (CurrUser != null) ? CurrUser.LoginProjectId : ""));
|
||
listStr.Add(new SqlParameter("@WeldingDate", this.txtWeldingDate.Text.Trim()));
|
||
}
|
||
else
|
||
{
|
||
// 优化：使用 CTE 和 LEFT JOIN 替代嵌套子查询（时间段模式）
|
||
strSql = @"
|
||
WITH AllWeldJoints AS (
|
||
SELECT ProjectId, SUM(ISNULL(Size, 0)) AS TotalDin
|
||
FROM dbo.HJGL_WeldJoint
|
||
WHERE ProjectId = @ProjectId
|
||
GROUP BY ProjectId
|
||
),
|
||
PeriodWeldJoints AS (
|
||
SELECT jot.ProjectId, SUM(ISNULL(jot.Size, 0)) AS TodayFinishSize
|
||
FROM dbo.HJGL_WeldJoint jot
|
||
INNER JOIN dbo.HJGL_WeldingDaily wd ON jot.WeldingDailyId = wd.WeldingDailyId
|
||
WHERE jot.ProjectId = @ProjectId AND jot.WeldingDailyId IS NOT NULL
|
||
AND wd.WeldingDate >= @WeldingStartDate AND wd.WeldingDate <= @WeldingEndDate
|
||
GROUP BY jot.ProjectId
|
||
),
|
||
CumulativeWeldJoints AS (
|
||
SELECT jot.ProjectId, SUM(ISNULL(jot.Size, 0)) AS FinishSize
|
||
FROM dbo.HJGL_WeldJoint jot
|
||
INNER JOIN dbo.HJGL_WeldingDaily wd ON jot.WeldingDailyId = wd.WeldingDailyId
|
||
WHERE jot.ProjectId = @ProjectId AND jot.WeldingDailyId IS NOT NULL AND wd.WeldingDate <= @WeldingEndDate
|
||
GROUP BY jot.ProjectId
|
||
)
|
||
SELECT b.ProjectId,
|
||
ISNULL(aw.TotalDin, 0) AS TotalDin,
|
||
ISNULL(pw.TodayFinishSize, 0) AS TodayFinishSize,
|
||
ISNULL(cw.FinishSize, 0) AS FinishSize,
|
||
CONVERT(NVARCHAR(10), CAST(CASE ISNULL(aw.TotalDin, 0) WHEN 0 THEN 0
|
||
ELSE 100.0 * ISNULL(cw.FinishSize, 0) / (1.0 * aw.TotalDin) END AS DECIMAL(8, 2))) + '%' AS Rate
|
||
FROM Base_Project b
|
||
LEFT JOIN AllWeldJoints aw ON b.ProjectId = aw.ProjectId
|
||
LEFT JOIN PeriodWeldJoints pw ON b.ProjectId = pw.ProjectId
|
||
LEFT JOIN CumulativeWeldJoints cw ON b.ProjectId = cw.ProjectId
|
||
WHERE b.ProjectId = @ProjectId";
|
||
listStr.Add(new SqlParameter("@ProjectId", (CurrUser != null) ? CurrUser.LoginProjectId : ""));
|
||
listStr.Add(new SqlParameter("@WeldingStartDate", this.txtWeldingStartDate.Text.Trim()));
|
||
listStr.Add(new SqlParameter("@WeldingEndDate", this.txtWeldingEndDate.Text.Trim()));
|
||
}
|
||
SqlParameter[] parameter = listStr.ToArray();
|
||
DataTable tb = SQLHelper.GetDataTableRunText(strSql, parameter);
|
||
Grid1.RecordCount = tb.Rows.Count;
|
||
tb = GetFilteredTable(Grid1.FilteredData, tb);
|
||
var table = GetPagedDataTable(Grid1, tb);
|
||
Grid1.DataSource = table;
|
||
Grid1.DataBind();
|
||
if (Grid1.Rows.Count > 0)
|
||
{
|
||
decimal totalDin = Funs.GetNewDecimalOrZero(this.Grid1.Rows[0].Values[0].ToString());
|
||
decimal finishSize = Funs.GetNewDecimalOrZero(this.Grid1.Rows[0].Values[2].ToString());
|
||
decimal notCompleteDin = totalDin - finishSize;
|
||
this.lbNotCompleteDinTotal.Text = notCompleteDin.ToString();
|
||
}
|
||
}
|
||
|
||
/// <summary>
|
||
/// 数据绑定 - 单位工程进度统计
|
||
/// 优化版本：使用 CTE 替代复杂嵌套子查询，提高性能
|
||
/// </summary>
|
||
private void BindGrid2(string unitWorkId)
|
||
{
|
||
string strSql = string.Empty;
|
||
List<SqlParameter> listStr = new List<SqlParameter>();
|
||
if (this.rbType.SelectedValue == "1")
|
||
{
|
||
// 优化：使用 CTE 和条件聚合替代 UNION 和嵌套子查询
|
||
strSql = @"
|
||
WITH UnitWorkData AS (
|
||
-- 获取单位工程的管线数据
|
||
SELECT p.PipelineId, p.PipeArea, p.UnitWorkId
|
||
FROM dbo.HJGL_Pipeline p
|
||
WHERE p.UnitWorkId = @UnitWorkId
|
||
),
|
||
JointData AS (
|
||
-- 获取焊口数据并分类
|
||
SELECT
|
||
j.WeldJointId,
|
||
j.Size,
|
||
j.WeldingDailyId,
|
||
j.JointAttribute,
|
||
j.PipelineId,
|
||
p.PipeArea,
|
||
p.UnitWorkId
|
||
FROM dbo.HJGL_WeldJoint j
|
||
INNER JOIN UnitWorkData p ON j.PipelineId = p.PipelineId
|
||
),
|
||
WeldingInfo AS (
|
||
-- 焊接日期信息
|
||
SELECT wd.WeldingDailyId, wd.WeldingDate
|
||
FROM dbo.HJGL_WeldingDaily wd
|
||
WHERE wd.WeldingDate = @WeldingDate
|
||
),
|
||
Stats AS (
|
||
SELECT
|
||
ud.UnitWorkId,
|
||
-- 工厂预制总量
|
||
SUM(CASE WHEN ud.PipeArea = '1' AND jd.JointAttribute = '预制口' THEN ISNULL(jd.Size, 0) ELSE 0 END) AS FactoryTotal,
|
||
-- 现场施工总量 (PipeArea='2' 或 PipeArea='1' 且为安装口)
|
||
SUM(CASE WHEN ud.PipeArea = '2' OR (ud.PipeArea = '1' AND jd.JointAttribute = '安装口') THEN ISNULL(jd.Size, 0) ELSE 0 END) AS SiteTotal,
|
||
-- 工厂预制当日完成
|
||
SUM(CASE WHEN ud.PipeArea = '1' AND jd.JointAttribute = '预制口' AND jd.WeldingDailyId IS NOT NULL AND wi.WeldingDailyId IS NOT NULL THEN ISNULL(jd.Size, 0) ELSE 0 END) AS FactoryToday,
|
||
-- 现场施工当日完成
|
||
SUM(CASE WHEN (ud.PipeArea = '2' OR (ud.PipeArea = '1' AND jd.JointAttribute = '安装口')) AND jd.WeldingDailyId IS NOT NULL AND wi.WeldingDailyId IS NOT NULL THEN ISNULL(jd.Size, 0) ELSE 0 END) AS SiteToday,
|
||
-- 工厂预制累计完成
|
||
SUM(CASE WHEN ud.PipeArea = '1' AND jd.JointAttribute = '预制口' AND jd.WeldingDailyId IS NOT NULL THEN ISNULL(jd.Size, 0) ELSE 0 END) AS FactoryCumulative,
|
||
-- 现场施工累计完成
|
||
SUM(CASE WHEN (ud.PipeArea = '2' OR (ud.PipeArea = '1' AND jd.JointAttribute = '安装口')) AND jd.WeldingDailyId IS NOT NULL THEN ISNULL(jd.Size, 0) ELSE 0 END) AS SiteCumulative
|
||
FROM UnitWorkData ud
|
||
LEFT JOIN JointData jd ON ud.PipelineId = jd.PipelineId
|
||
LEFT JOIN WeldingInfo wi ON jd.WeldingDailyId = wi.WeldingDailyId
|
||
GROUP BY ud.UnitWorkId
|
||
)
|
||
SELECT '工厂预制焊口' AS PipeArea, UnitWorkId,
|
||
CAST(ISNULL(FactoryTotal, 0) AS DECIMAL(12, 3)) AS TotalDin,
|
||
CAST(ISNULL(FactoryToday, 0) AS DECIMAL(12, 3)) AS TodayFinishSize,
|
||
CAST(ISNULL(FactoryCumulative, 0) AS DECIMAL(12, 3)) AS FinishSize,
|
||
CONVERT(NVARCHAR(10), CAST(CASE ISNULL(FactoryTotal, 0) WHEN 0 THEN 0
|
||
ELSE 100.0 * ISNULL(FactoryCumulative, 0) / (1.0 * FactoryTotal) END AS DECIMAL(8, 2))) + '%' AS Rate
|
||
FROM Stats
|
||
WHERE UnitWorkId = @UnitWorkId
|
||
UNION ALL
|
||
SELECT '现场施工焊口' AS PipeArea, UnitWorkId,
|
||
CAST(ISNULL(SiteTotal, 0) AS DECIMAL(12, 3)) AS TotalDin,
|
||
CAST(ISNULL(SiteToday, 0) AS DECIMAL(12, 3)) AS TodayFinishSize,
|
||
CAST(ISNULL(SiteCumulative, 0) AS DECIMAL(12, 3)) AS FinishSize,
|
||
CONVERT(NVARCHAR(10), CAST(CASE ISNULL(SiteTotal, 0) WHEN 0 THEN 0
|
||
ELSE 100.0 * ISNULL(SiteCumulative, 0) / (1.0 * SiteTotal) END AS DECIMAL(8, 2))) + '%' AS Rate
|
||
FROM Stats
|
||
WHERE UnitWorkId = @UnitWorkId";
|
||
listStr.Add(new SqlParameter("@UnitWorkId", unitWorkId));
|
||
listStr.Add(new SqlParameter("@WeldingDate", this.txtWeldingDate.Text.Trim()));
|
||
}
|
||
else
|
||
{
|
||
// 优化：使用 CTE 和条件聚合（时间段模式）
|
||
strSql = @"
|
||
WITH UnitWorkData AS (
|
||
SELECT p.PipelineId, p.PipeArea, p.UnitWorkId
|
||
FROM dbo.HJGL_Pipeline p
|
||
WHERE p.UnitWorkId = @UnitWorkId
|
||
),
|
||
JointData AS (
|
||
SELECT
|
||
j.WeldJointId,
|
||
j.Size,
|
||
j.WeldingDailyId,
|
||
j.JointAttribute,
|
||
j.PipelineId,
|
||
p.PipeArea,
|
||
p.UnitWorkId
|
||
FROM dbo.HJGL_WeldJoint j
|
||
INNER JOIN UnitWorkData p ON j.PipelineId = p.PipelineId
|
||
),
|
||
WeldingInfo AS (
|
||
SELECT wd.WeldingDailyId, wd.WeldingDate
|
||
FROM dbo.HJGL_WeldingDaily wd
|
||
WHERE wd.WeldingDate >= @WeldingStartDate AND wd.WeldingDate <= @WeldingEndDate
|
||
),
|
||
Stats AS (
|
||
SELECT
|
||
ud.UnitWorkId,
|
||
SUM(CASE WHEN ud.PipeArea = '1' AND jd.JointAttribute = '预制口' THEN ISNULL(jd.Size, 0) ELSE 0 END) AS FactoryTotal,
|
||
SUM(CASE WHEN ud.PipeArea = '2' OR (ud.PipeArea = '1' AND jd.JointAttribute = '安装口') THEN ISNULL(jd.Size, 0) ELSE 0 END) AS SiteTotal,
|
||
SUM(CASE WHEN ud.PipeArea = '1' AND jd.JointAttribute = '预制口' AND jd.WeldingDailyId IS NOT NULL AND wi.WeldingDailyId IS NOT NULL THEN ISNULL(jd.Size, 0) ELSE 0 END) AS FactoryToday,
|
||
SUM(CASE WHEN (ud.PipeArea = '2' OR (ud.PipeArea = '1' AND jd.JointAttribute = '安装口')) AND jd.WeldingDailyId IS NOT NULL AND wi.WeldingDailyId IS NOT NULL THEN ISNULL(jd.Size, 0) ELSE 0 END) AS SiteToday,
|
||
SUM(CASE WHEN ud.PipeArea = '1' AND jd.JointAttribute = '预制口' AND jd.WeldingDailyId IS NOT NULL AND wi.WeldingDate <= @WeldingEndDate THEN ISNULL(jd.Size, 0) ELSE 0 END) AS FactoryCumulative,
|
||
SUM(CASE WHEN (ud.PipeArea = '2' OR (ud.PipeArea = '1' AND jd.JointAttribute = '安装口')) AND jd.WeldingDailyId IS NOT NULL AND wi.WeldingDate <= @WeldingEndDate THEN ISNULL(jd.Size, 0) ELSE 0 END) AS SiteCumulative
|
||
FROM UnitWorkData ud
|
||
LEFT JOIN JointData jd ON ud.PipelineId = jd.PipelineId
|
||
LEFT JOIN WeldingInfo wi ON jd.WeldingDailyId = wi.WeldingDailyId
|
||
GROUP BY ud.UnitWorkId
|
||
)
|
||
SELECT '工厂预制焊口' AS PipeArea, UnitWorkId,
|
||
CAST(ISNULL(FactoryTotal, 0) AS DECIMAL(12, 3)) AS TotalDin,
|
||
CAST(ISNULL(FactoryToday, 0) AS DECIMAL(12, 3)) AS TodayFinishSize,
|
||
CAST(ISNULL(FactoryCumulative, 0) AS DECIMAL(12, 3)) AS FinishSize,
|
||
CONVERT(NVARCHAR(10), CAST(CASE ISNULL(FactoryTotal, 0) WHEN 0 THEN 0
|
||
ELSE 100.0 * ISNULL(FactoryCumulative, 0) / (1.0 * FactoryTotal) END AS DECIMAL(8, 2))) + '%' AS Rate
|
||
FROM Stats
|
||
WHERE UnitWorkId = @UnitWorkId
|
||
UNION ALL
|
||
SELECT '现场施工焊口' AS PipeArea, UnitWorkId,
|
||
CAST(ISNULL(SiteTotal, 0) AS DECIMAL(12, 3)) AS TotalDin,
|
||
CAST(ISNULL(SiteToday, 0) AS DECIMAL(12, 3)) AS TodayFinishSize,
|
||
CAST(ISNULL(SiteCumulative, 0) AS DECIMAL(12, 3)) AS FinishSize,
|
||
CONVERT(NVARCHAR(10), CAST(CASE ISNULL(SiteTotal, 0) WHEN 0 THEN 0
|
||
ELSE 100.0 * ISNULL(SiteCumulative, 0) / (1.0 * SiteTotal) END AS DECIMAL(8, 2))) + '%' AS Rate
|
||
FROM Stats
|
||
WHERE UnitWorkId = @UnitWorkId";
|
||
listStr.Add(new SqlParameter("@UnitWorkId", unitWorkId));
|
||
listStr.Add(new SqlParameter("@WeldingStartDate", this.txtWeldingStartDate.Text.Trim()));
|
||
listStr.Add(new SqlParameter("@WeldingEndDate", this.txtWeldingEndDate.Text.Trim()));
|
||
}
|
||
SqlParameter[] parameter = listStr.ToArray();
|
||
DataTable tb = SQLHelper.GetDataTableRunText(strSql, parameter);
|
||
Grid2.RecordCount = tb.Rows.Count;
|
||
tb = GetFilteredTable(Grid2.FilteredData, tb);
|
||
var table = GetPagedDataTable(Grid2, tb);
|
||
Grid2.DataSource = table;
|
||
Grid2.DataBind();
|
||
if (Grid2.RecordCount > 0)
|
||
{
|
||
decimal totalDin1 = Funs.GetNewDecimalOrZero(this.Grid2.Rows[0].Values[1].ToString());
|
||
decimal finishSize1 = Funs.GetNewDecimalOrZero(this.Grid2.Rows[0].Values[3].ToString());
|
||
decimal notCompleteDin1 = totalDin1 - finishSize1;
|
||
this.lbNotCompleteDin1.Text = notCompleteDin1.ToString();
|
||
if (Grid2.Rows.Count > 1)
|
||
{
|
||
decimal totalDin2 = Funs.GetNewDecimalOrZero(this.Grid2.Rows[1].Values[1].ToString());
|
||
decimal finishSize2 = Funs.GetNewDecimalOrZero(this.Grid2.Rows[1].Values[3].ToString());
|
||
decimal notCompleteDin2 = totalDin2 - finishSize2;
|
||
this.lbNotCompleteDin2.Text = notCompleteDin2.ToString();
|
||
}
|
||
}
|
||
}
|
||
|
||
/// <summary>
|
||
/// 数据绑定 - 施工单位进度统计
|
||
/// 优化版本：使用 CTE 和 JOIN 替代嵌套子查询
|
||
/// </summary>
|
||
private void BindGrid3()
|
||
{
|
||
string strSql = string.Empty;
|
||
List<SqlParameter> listStr = new List<SqlParameter>();
|
||
if (this.rbType.SelectedValue == "1")
|
||
{
|
||
// 优化：使用 CTE 和聚合替代嵌套子查询，增加焊工数量、焊工日功效、焊接一次合格率
|
||
strSql = @"
|
||
WITH UnitWeldingDaily AS (
|
||
-- 获取项目的所有焊接日报记录
|
||
SELECT DISTINCT wd.UnitId, u.UnitName
|
||
FROM dbo.HJGL_WeldingDaily wd
|
||
INNER JOIN Base_Unit u ON wd.UnitId = u.UnitId
|
||
WHERE wd.ProjectId = @ProjectId
|
||
),
|
||
TodayStats AS (
|
||
-- 当日完成统计（含焊工数量）
|
||
SELECT wd.UnitId,
|
||
SUM(ISNULL(jot.Size, 0)) AS TodayFinishSize,
|
||
COUNT(DISTINCT CASE WHEN jot.CoverWelderId IS NOT NULL AND jot.CoverWelderId != '' THEN jot.CoverWelderId END) AS CurrentWelders
|
||
FROM dbo.HJGL_WeldingDaily wd
|
||
INNER JOIN dbo.HJGL_WeldJoint jot ON jot.WeldingDailyId = wd.WeldingDailyId
|
||
WHERE wd.ProjectId = @ProjectId AND wd.WeldingDate = @WeldingDate AND jot.WeldingDailyId IS NOT NULL
|
||
GROUP BY wd.UnitId
|
||
),
|
||
CumulativeStats AS (
|
||
-- 累计完成统计
|
||
SELECT wd.UnitId, SUM(ISNULL(jot.Size, 0)) AS FinishSize
|
||
FROM dbo.HJGL_WeldingDaily wd
|
||
INNER JOIN dbo.HJGL_WeldJoint jot ON jot.WeldingDailyId = wd.WeldingDailyId
|
||
WHERE wd.ProjectId = @ProjectId AND wd.WeldingDate <= @WeldingDate AND jot.WeldingDailyId IS NOT NULL
|
||
GROUP BY wd.UnitId
|
||
),
|
||
NDERates AS (
|
||
-- 焊接一次合格率统计（按施工单位）
|
||
SELECT wd.UnitId,
|
||
CAST(CASE WHEN COUNT(*) = 0 THEN 0
|
||
ELSE 100.0 * SUM(CASE WHEN nde.CheckResult != '2' THEN 1 ELSE 0 END) / COUNT(*)
|
||
END AS DECIMAL(8, 2)) AS WeldingFirstPassRate
|
||
FROM dbo.HJGL_Batch_NDEItem nde
|
||
INNER JOIN dbo.HJGL_Batch_BatchTrustItem bt ON nde.TrustBatchItemId = bt.TrustBatchItemId
|
||
INNER JOIN dbo.HJGL_Batch_PointBatchItem pb ON bt.PointBatchItemId = pb.PointBatchItemId
|
||
INNER JOIN dbo.HJGL_Batch_NDE n ON nde.NDEID = n.NDEID
|
||
INNER JOIN dbo.HJGL_WeldJoint wj ON bt.WeldJointId = wj.WeldJointId
|
||
INNER JOIN dbo.HJGL_WeldingDaily wd ON wj.WeldingDailyId = wd.WeldingDailyId
|
||
WHERE pb.PointDate IS NOT NULL AND pb.PointState = '1' AND bt.RepairRecordId IS NULL
|
||
AND n.ProjectId = @ProjectId
|
||
GROUP BY wd.UnitId
|
||
)
|
||
SELECT uwd.UnitName,
|
||
CAST(ISNULL(ts.TodayFinishSize, 0) AS DECIMAL(12, 3)) AS TodayFinishSize,
|
||
CAST(ISNULL(cs.FinishSize, 0) AS DECIMAL(12, 3)) AS FinishSize,
|
||
ISNULL(ts.CurrentWelders, 0) AS CurrentWelders,
|
||
CASE WHEN ISNULL(ts.CurrentWelders, 0) = 0 THEN 0
|
||
ELSE CAST(ISNULL(ts.TodayFinishSize, 0) * 1.0 / ts.CurrentWelders AS DECIMAL(12, 3))
|
||
END AS WelderEfficiency,
|
||
ISNULL(nr.WeldingFirstPassRate, 0) AS WeldingFirstPassRate
|
||
FROM UnitWeldingDaily uwd
|
||
LEFT JOIN TodayStats ts ON uwd.UnitId = ts.UnitId
|
||
LEFT JOIN CumulativeStats cs ON uwd.UnitId = cs.UnitId
|
||
LEFT JOIN NDERates nr ON uwd.UnitId = nr.UnitId";
|
||
listStr.Add(new SqlParameter("@ProjectId", (CurrUser != null) ? CurrUser.LoginProjectId : ""));
|
||
listStr.Add(new SqlParameter("@WeldingDate", this.txtWeldingDate.Text.Trim()));
|
||
}
|
||
else
|
||
{
|
||
// 优化：使用 CTE 和聚合（时间段模式），增加焊工数量、焊工日功效、焊接一次合格率
|
||
strSql = @"
|
||
WITH UnitWeldingDaily AS (
|
||
SELECT DISTINCT wd.UnitId, u.UnitName
|
||
FROM dbo.HJGL_WeldingDaily wd
|
||
INNER JOIN Base_Unit u ON wd.UnitId = u.UnitId
|
||
WHERE wd.ProjectId = @ProjectId
|
||
),
|
||
PeriodStats AS (
|
||
-- 时间段完成统计（含焊工数量）
|
||
SELECT wd.UnitId,
|
||
SUM(ISNULL(jot.Size, 0)) AS TodayFinishSize,
|
||
COUNT(DISTINCT CASE WHEN jot.CoverWelderId IS NOT NULL AND jot.CoverWelderId != '' THEN jot.CoverWelderId END) AS CurrentWelders
|
||
FROM dbo.HJGL_WeldingDaily wd
|
||
INNER JOIN dbo.HJGL_WeldJoint jot ON jot.WeldingDailyId = wd.WeldingDailyId
|
||
WHERE wd.ProjectId = @ProjectId AND wd.WeldingDate >= @WeldingStartDate AND wd.WeldingDate <= @WeldingEndDate AND jot.WeldingDailyId IS NOT NULL
|
||
GROUP BY wd.UnitId
|
||
),
|
||
CumulativeStats AS (
|
||
-- 累计完成统计
|
||
SELECT wd.UnitId, SUM(ISNULL(jot.Size, 0)) AS FinishSize
|
||
FROM dbo.HJGL_WeldingDaily wd
|
||
INNER JOIN dbo.HJGL_WeldJoint jot ON jot.WeldingDailyId = wd.WeldingDailyId
|
||
WHERE wd.ProjectId = @ProjectId AND wd.WeldingDate <= @WeldingEndDate AND jot.WeldingDailyId IS NOT NULL
|
||
GROUP BY wd.UnitId
|
||
),
|
||
NDERates AS (
|
||
-- 焊接一次合格率统计（按施工单位）
|
||
SELECT wd.UnitId,
|
||
CAST(CASE WHEN COUNT(*) = 0 THEN 0
|
||
ELSE 100.0 * SUM(CASE WHEN nde.CheckResult != '2' THEN 1 ELSE 0 END) / COUNT(*)
|
||
END AS DECIMAL(8, 2)) AS WeldingFirstPassRate
|
||
FROM dbo.HJGL_Batch_NDEItem nde
|
||
INNER JOIN dbo.HJGL_Batch_BatchTrustItem bt ON nde.TrustBatchItemId = bt.TrustBatchItemId
|
||
INNER JOIN dbo.HJGL_Batch_PointBatchItem pb ON bt.PointBatchItemId = pb.PointBatchItemId
|
||
INNER JOIN dbo.HJGL_Batch_NDE n ON nde.NDEID = n.NDEID
|
||
INNER JOIN dbo.HJGL_WeldJoint wj ON bt.WeldJointId = wj.WeldJointId
|
||
INNER JOIN dbo.HJGL_WeldingDaily wd ON wj.WeldingDailyId = wd.WeldingDailyId
|
||
WHERE pb.PointDate IS NOT NULL AND pb.PointState = '1' AND bt.RepairRecordId IS NULL
|
||
AND n.ProjectId = @ProjectId
|
||
GROUP BY wd.UnitId
|
||
)
|
||
SELECT uwd.UnitName,
|
||
CAST(ISNULL(ps.TodayFinishSize, 0) AS DECIMAL(12, 3)) AS TodayFinishSize,
|
||
CAST(ISNULL(cs.FinishSize, 0) AS DECIMAL(12, 3)) AS FinishSize,
|
||
ISNULL(ps.CurrentWelders, 0) AS CurrentWelders,
|
||
CASE WHEN ISNULL(ps.CurrentWelders, 0) = 0 THEN 0
|
||
ELSE CAST(ISNULL(ps.TodayFinishSize, 0) * 1.0 / ps.CurrentWelders AS DECIMAL(12, 3))
|
||
END AS WelderEfficiency,
|
||
ISNULL(nr.WeldingFirstPassRate, 0) AS WeldingFirstPassRate
|
||
FROM UnitWeldingDaily uwd
|
||
LEFT JOIN PeriodStats ps ON uwd.UnitId = ps.UnitId
|
||
LEFT JOIN CumulativeStats cs ON uwd.UnitId = cs.UnitId
|
||
LEFT JOIN NDERates nr ON uwd.UnitId = nr.UnitId";
|
||
listStr.Add(new SqlParameter("@ProjectId", (CurrUser != null) ? CurrUser.LoginProjectId : ""));
|
||
listStr.Add(new SqlParameter("@WeldingStartDate", this.txtWeldingStartDate.Text.Trim()));
|
||
listStr.Add(new SqlParameter("@WeldingEndDate", this.txtWeldingEndDate.Text.Trim()));
|
||
}
|
||
SqlParameter[] parameter = listStr.ToArray();
|
||
DataTable tb = SQLHelper.GetDataTableRunText(strSql, parameter);
|
||
Grid3.RecordCount = tb.Rows.Count;
|
||
tb = GetFilteredTable(Grid3.FilteredData, tb);
|
||
var table = GetPagedDataTable(Grid3, tb);
|
||
Grid3.DataSource = table;
|
||
Grid3.DataBind();
|
||
}
|
||
#endregion
|
||
|
||
/// <summary>
|
||
/// 查询
|
||
/// </summary>
|
||
/// <param name="sender"></param>
|
||
/// <param name="e"></param>
|
||
protected void btnQuery1_Click(object sender, EventArgs e)
|
||
{
|
||
if (this.tvControlItem.SelectedNodeID.Length > 1)
|
||
{
|
||
if (string.IsNullOrEmpty(this.txtPlanEndDate1.Text.Trim()))
|
||
{
|
||
ShowNotify("请输入计划完成日期！", MessageBoxIcon.Warning);
|
||
return;
|
||
}
|
||
if (string.IsNullOrEmpty(this.txtWorkEfficiency1.Text.Trim()))
|
||
{
|
||
ShowNotify("请输入日工效！", MessageBoxIcon.Warning);
|
||
return;
|
||
}
|
||
decimal totalDin = Funs.GetNewDecimalOrZero(this.Grid2.Rows[0].Values[1].ToString());
|
||
decimal finishSize = Funs.GetNewDecimalOrZero(this.Grid2.Rows[0].Values[3].ToString());
|
||
decimal notCompleteDin = totalDin - finishSize;
|
||
this.lbNotCompleteDin1.Text = notCompleteDin.ToString();
|
||
TimeSpan ts = (TimeSpan)(Funs.GetNewDateTime(this.txtPlanEndDate1.Text.Trim()) - Funs.GetNewDateTime(this.txtWeldingDate.Text.Trim()));
|
||
decimal leaveDay = Convert.ToDecimal(ts.Days);
|
||
decimal workEfficiency = Funs.GetNewDecimalOrZero(this.txtWorkEfficiency1.Text.Trim());
|
||
if (notCompleteDin > 0)
|
||
{
|
||
if (leaveDay == 0)
|
||
{
|
||
ShowNotify("剩余工期（天）为0！", MessageBoxIcon.Warning);
|
||
return;
|
||
}
|
||
if (workEfficiency == 0)
|
||
{
|
||
ShowNotify("日工效为0！", MessageBoxIcon.Warning);
|
||
return;
|
||
}
|
||
this.lbDayNeedSize1.Text = Convert.ToInt32(Math.Ceiling(notCompleteDin / leaveDay)).ToString();
|
||
this.lbNeedWelderNum1.Text = Convert.ToInt32(Math.Ceiling(notCompleteDin / leaveDay / workEfficiency)).ToString();
|
||
}
|
||
else
|
||
{
|
||
ShowNotify("工厂预制未完成达因为0！", MessageBoxIcon.Warning);
|
||
return;
|
||
}
|
||
}
|
||
else
|
||
{
|
||
ShowNotify("请选择单位工程", MessageBoxIcon.Warning);
|
||
return;
|
||
}
|
||
}
|
||
|
||
/// <summary>
|
||
/// 查询
|
||
/// </summary>
|
||
/// <param name="sender"></param>
|
||
/// <param name="e"></param>
|
||
protected void btnQuery2_Click(object sender, EventArgs e)
|
||
{
|
||
if (this.tvControlItem.SelectedNodeID.Length > 1)
|
||
{
|
||
if (string.IsNullOrEmpty(this.txtPlanEndDate2.Text.Trim()))
|
||
{
|
||
ShowNotify("请输入计划完成日期！", MessageBoxIcon.Warning);
|
||
return;
|
||
}
|
||
if (string.IsNullOrEmpty(this.txtWorkEfficiency2.Text.Trim()))
|
||
{
|
||
ShowNotify("请输入日工效！", MessageBoxIcon.Warning);
|
||
return;
|
||
}
|
||
decimal totalDin = Funs.GetNewDecimalOrZero(this.Grid2.Rows[1].Values[1].ToString());
|
||
decimal finishSize = Funs.GetNewDecimalOrZero(this.Grid2.Rows[1].Values[3].ToString());
|
||
decimal notCompleteDin = totalDin - finishSize;
|
||
this.lbNotCompleteDin2.Text = notCompleteDin.ToString();
|
||
TimeSpan ts = (TimeSpan)(Funs.GetNewDateTime(this.txtPlanEndDate2.Text.Trim()) - Funs.GetNewDateTime(this.txtWeldingDate.Text.Trim()));
|
||
decimal leaveDay = Convert.ToDecimal(ts.Days);
|
||
decimal workEfficiency = Funs.GetNewDecimalOrZero(this.txtWorkEfficiency2.Text.Trim());
|
||
if (notCompleteDin > 0)
|
||
{
|
||
if (leaveDay == 0)
|
||
{
|
||
ShowNotify("剩余工期（天）为0！", MessageBoxIcon.Warning);
|
||
return;
|
||
}
|
||
if (workEfficiency == 0)
|
||
{
|
||
ShowNotify("日工效为0！", MessageBoxIcon.Warning);
|
||
return;
|
||
}
|
||
this.lbDayNeedSize2.Text = Convert.ToInt32(Math.Ceiling(notCompleteDin / leaveDay)).ToString();
|
||
this.lbNeedWelderNum2.Text = Convert.ToInt32(Math.Ceiling(notCompleteDin / leaveDay / workEfficiency)).ToString();
|
||
}
|
||
else
|
||
{
|
||
ShowNotify("现场施工未完成达因为0！", MessageBoxIcon.Warning);
|
||
return;
|
||
}
|
||
}
|
||
else
|
||
{
|
||
ShowNotify("请选择单位工程", MessageBoxIcon.Warning);
|
||
return;
|
||
}
|
||
}
|
||
|
||
/// <summary>
|
||
/// 查询
|
||
/// </summary>
|
||
/// <param name="sender"></param>
|
||
/// <param name="e"></param>
|
||
protected void btnQueryTotal_Click(object sender, EventArgs e)
|
||
{
|
||
if (string.IsNullOrEmpty(this.txtPlanEndDate.Text.Trim()))
|
||
{
|
||
ShowNotify("请输入计划完成日期！", MessageBoxIcon.Warning);
|
||
return;
|
||
}
|
||
if (string.IsNullOrEmpty(this.txtWorkEfficiencyTotal.Text.Trim()))
|
||
{
|
||
ShowNotify("请输入日工效！", MessageBoxIcon.Warning);
|
||
return;
|
||
}
|
||
decimal totalDin = Funs.GetNewDecimalOrZero(this.Grid1.Rows[0].Values[0].ToString());
|
||
decimal finishSize = Funs.GetNewDecimalOrZero(this.Grid1.Rows[0].Values[2].ToString());
|
||
decimal notCompleteDin = totalDin - finishSize;
|
||
TimeSpan ts = (TimeSpan)(Funs.GetNewDateTime(this.txtPlanEndDate.Text.Trim()) - Funs.GetNewDateTime(this.txtWeldingDate.Text.Trim()));
|
||
decimal leaveDay = Convert.ToDecimal(ts.Days);
|
||
decimal workEfficiency = Funs.GetNewDecimalOrZero(this.txtWorkEfficiencyTotal.Text.Trim());
|
||
if (notCompleteDin > 0)
|
||
{
|
||
if (leaveDay == 0)
|
||
{
|
||
ShowNotify("剩余工期（天）为0！", MessageBoxIcon.Warning);
|
||
return;
|
||
}
|
||
if (workEfficiency == 0)
|
||
{
|
||
ShowNotify("日工效为0！", MessageBoxIcon.Warning);
|
||
return;
|
||
}
|
||
this.lbDayNeedSize.Text = Convert.ToInt32(Math.Ceiling(notCompleteDin / leaveDay)).ToString();
|
||
this.lbNeedWelderNumTotal.Text = Convert.ToInt32(Math.Ceiling(notCompleteDin / leaveDay / workEfficiency)).ToString();
|
||
}
|
||
else
|
||
{
|
||
ShowNotify("未完成达因为0！", MessageBoxIcon.Warning);
|
||
return;
|
||
}
|
||
}
|
||
|
||
protected void rbType_SelectedIndexChanged(object sender, EventArgs e)
|
||
{
|
||
if (this.rbType.SelectedValue == "1")
|
||
{
|
||
this.txtWeldingDate.Hidden = false;
|
||
this.txtWeldingStartDate.Hidden = true;
|
||
this.txtWeldingEndDate.Hidden = true;
|
||
this.Grid1.Columns[1].HeaderText = "当日完成达因数";
|
||
this.Grid2.Columns[2].HeaderText = "当日完成达因数";
|
||
this.Grid3.Columns[1].HeaderText = "当日完成达因数";
|
||
this.Grid3.Columns[3].HeaderText = "当日焊工数量";
|
||
this.BtnAnalyse.Hidden = true;
|
||
BindGrid1();
|
||
BindGrid2(this.tvControlItem.SelectedNodeID);
|
||
BindGrid3();
|
||
}
|
||
else
|
||
{
|
||
this.txtWeldingDate.Hidden = true;
|
||
this.txtWeldingStartDate.Hidden = false;
|
||
this.txtWeldingEndDate.Hidden = false;
|
||
this.Grid1.Columns[1].HeaderText = "当期完成达因数";
|
||
this.Grid2.Columns[2].HeaderText = "当期完成达因数";
|
||
this.Grid3.Columns[1].HeaderText = "当期完成达因数";
|
||
this.Grid3.Columns[3].HeaderText = "当期焊工数量";
|
||
this.BtnAnalyse.Hidden = false;
|
||
}
|
||
}
|
||
|
||
/// <summary>
|
||
/// 统计
|
||
/// </summary>
|
||
/// <param name="sender"></param>
|
||
/// <param name="e"></param>
|
||
protected void BtnAnalyse_Click(object sender, EventArgs e)
|
||
{
|
||
if (!string.IsNullOrEmpty(this.txtWeldingStartDate.Text.Trim()) && !string.IsNullOrEmpty(this.txtWeldingEndDate.Text.Trim()))
|
||
{
|
||
BindGrid1();
|
||
BindGrid2(this.tvControlItem.SelectedNodeID);
|
||
BindGrid3();
|
||
}
|
||
else
|
||
{
|
||
ShowNotify("请选择开始日期和结束日期！", MessageBoxIcon.Warning);
|
||
}
|
||
}
|
||
}
|
||
} |