using System;
using System.Collections.Generic;
using System.Linq;
using System.Data;
using System.Data.SqlClient;

namespace BLL
{
   public static class APIMessagePushService
    {
        #region 推送的热处理委托单
        /// <summary>
        /// 推送的热处理委托单
        /// </summary>
        /// <param name="userId">登陆的用户ID</param>
        /// <returns></returns>
        public static List<Model.BaseInfoItem> GetPushHotTrust(string userId)
        {
            // 用户参与的项目
            string projectIds = BLL.Base_ProjectService.GetStrProjectIds(userId, "1");
            string strSql = @"SELECT pro.ProjectCode,t.HotProessTrustNo,t.ProessDate FROM dbo.HotProess_Trust t
                                    LEFT JOIN dbo.Base_Project pro ON pro.ProjectId = t.ProjectId
                              WHERE CHARINDEX(t.ProjectId,@ProjectId)>0 AND 
                                   (SELECT COUNT(*) FROM dbo.HotProess_TrustItem item WHERE item.HotProessTrustId=t.HotProessTrustId )!=
                                   (SELECT COUNT(*) FROM dbo.HotProess_TrustItem item WHERE item.HotProessTrustId=t.HotProessTrustId AND item.IsPass IS NOT NULL)";

            List<SqlParameter> listStr = new List<SqlParameter>();
            listStr.Add(new SqlParameter("@ProjectId", projectIds));

            SqlParameter[] parameter = listStr.ToArray();
            DataTable dt = SQLHelper.GetDataTableRunText(strSql, parameter);

            List<Model.BaseInfoItem> pushTrust = new List<Model.BaseInfoItem>();

            foreach (DataRow row in dt.Rows)
            {
                Model.BaseInfoItem item = new Model.BaseInfoItem();
                item.BaseInfoId= row["ProjectCode"].ToString();
                item.BaseInfoCode = row["HotProessTrustNo"].ToString();
                item.BaseInfoName = row["ProessDate"].ToString();
                pushTrust.Add(item);
            }

            return pushTrust;
        }
        #endregion

        #region 推送的NDE检测委托单
        /// <summary>
        /// 推送的NDE检测委托单
        /// </summary>
        /// <param name="userId">登陆的用户ID</param>
        /// <returns></returns>
        public static List<Model.BaseInfoItem> GetPushNDTTrust(string userId)
        {
            // 用户参与的项目
            string projectIds = BLL.Base_ProjectService.GetStrProjectIds(userId, "1");
            string strSql = @"SELECT pro.ProjectCode,batch.TrustBatchCode,batch.TrustDate FROM dbo.Batch_BatchTrust batch
                                    LEFT JOIN dbo.Base_Project pro ON pro.ProjectId = batch.ProjectId
                              WHERE batch.TrustBatchId NOT IN (SELECT TrustBatchId FROM dbo.Batch_NDE )
                                    AND CHARINDEX(batch.ProjectId,@ProjectId)>0";

            List<SqlParameter> listStr = new List<SqlParameter>();
            listStr.Add(new SqlParameter("@ProjectId", projectIds));

            SqlParameter[] parameter = listStr.ToArray();
            DataTable dt = SQLHelper.GetDataTableRunText(strSql, parameter);

            List<Model.BaseInfoItem> pushTrust = new List<Model.BaseInfoItem>();

            foreach (DataRow row in dt.Rows)
            {
                Model.BaseInfoItem item = new Model.BaseInfoItem();
                item.BaseInfoId = row["ProjectCode"].ToString();
                item.BaseInfoCode = row["TrustBatchCode"].ToString();
                item.BaseInfoName = row["TrustDate"].ToString();
                pushTrust.Add(item);
            }

            return pushTrust;
        }
        #endregion

        #region  焊工一次合格率低于96%预警
        /// <summary>
        /// 焊工一次合格率低于96%预警
        /// </summary>
        /// <param name="projectArea">项目区域</param>
        /// <returns></returns>
        public static List<Model.BaseInfoItem> GetWelderOnePassRateWarning(string projectArea)
        {

            string strSql = @"SELECT  (w.WelderCode+'('+w.WelderName+')') AS Welder,pro.ProjectCode,
	                                 CONVERT(NVARCHAR(10),(CAST((CASE ISNULL(oneCheck.OneCheckJotNum,0) WHEN 0 THEN 0 
		                             ELSE 100.0 * (ISNULL(oneCheck.OneCheckJotNum,0)-ISNULL(oneCheckRepair.oneCheckRepairJotNum,0))/(1.0 * oneCheck.OneCheckJotNum) END) AS DECIMAL(8,1))))+'%' AS passRate
                              FROM dbo.Welder_ProjectWelder AS welder 
							  LEFT JOIN dbo.Welder_Welder w ON w.WelderId = welder.WelderId
							  LEFT JOIN dbo.Base_Project pro ON pro.ProjectId = welder.ProjectId

                              LEFT JOIN (SELECT jot.CoverWelderId,jot.ProjectId,COUNT(ndeItem.NDEItemID) AS OneCheckJotNum
			                             FROM dbo.Batch_NDEItem ndeItem
				                         LEFT JOIN dbo.Batch_BatchTrustItem trustItem ON trustItem.TrustBatchItemId = ndeItem.TrustBatchItemId
				                         LEFT JOIN dbo.Pipeline_WeldJoint jot ON jot.WeldJointId = trustItem.WeldJointId
					                     LEFT JOIN dbo.Pipeline_WeldingDaily daily ON  daily.WeldingDailyId = jot.WeldingDailyId 	
					                     LEFT JOIN dbo.Batch_PointBatchItem pointItem ON pointItem.PointBatchItemId = trustItem.PointBatchItemId
				                         LEFT JOIN dbo.Batch_PointBatch point ON point.PointBatchId = pointItem.PointBatchId
			                             WHERE pointItem.PointDate IS NOT NULL AND pointItem.PointState=1 AND trustItem.RepairRecordId IS NULL	
			                            GROUP BY jot.ProjectId, jot.CoverWelderId) AS oneCheck ON oneCheck.CoverWelderId = welder.WelderId AND oneCheck.ProjectId=welder.ProjectId

                              LEFT JOIN (SELECT jot.CoverWelderId,jot.ProjectId,COUNT(ndeItem.NDEItemID) AS OneCheckRepairJotNum --一次检测返修焊口数
		                                 FROM dbo.Batch_NDEItem ndeItem
				                         LEFT JOIN dbo.Batch_BatchTrustItem trustItem ON trustItem.TrustBatchItemId = ndeItem.TrustBatchItemId
				                         LEFT JOIN dbo.Pipeline_WeldJoint jot ON jot.WeldJointId = trustItem.WeldJointId
					                     LEFT JOIN dbo.Pipeline_WeldingDaily daily ON  daily.WeldingDailyId = jot.WeldingDailyId 	
					                     LEFT JOIN dbo.Batch_PointBatchItem pointItem ON pointItem.PointBatchItemId = trustItem.PointBatchItemId
				                         LEFT JOIN dbo.Batch_PointBatch point ON point.PointBatchId = pointItem.PointBatchId  
			                             WHERE pointItem.PointDate IS NOT NULL AND pointItem.PointState=1 AND trustItem.RepairRecordId IS NULL
				                               AND ndeItem.CheckResult='2' 	
			                             GROUP BY jot.ProjectId, jot.CoverWelderId) AS oneCheckRepair 
										 ON oneCheckRepair.CoverWelderId = welder.WelderId AND oneCheckRepair.ProjectId=welder.ProjectId

                             WHERE (w.WelderCode IS NOT NULL AND w.WelderCode!='') 
							        AND pro.ProjectArea=@ProjectArea
	                                AND ISNULL(oneCheck.OneCheckJotNum,0)>0
	                                AND (CAST((CASE ISNULL(oneCheck.OneCheckJotNum,0) WHEN 0 THEN 0 
		                            ELSE 100.0 * (ISNULL(oneCheck.OneCheckJotNum,0)-ISNULL(oneCheckRepair.oneCheckRepairJotNum,0))/(1.0 * oneCheck.OneCheckJotNum) END) AS DECIMAL(8,1)))<=96";

            List<SqlParameter> listStr = new List<SqlParameter>();

            listStr.Add(new SqlParameter("@ProjectArea", projectArea));

            SqlParameter[] parameter = listStr.ToArray();
            DataTable dt = SQLHelper.GetDataTableRunText(strSql, parameter);

            List<Model.BaseInfoItem> warnWelder = new List<Model.BaseInfoItem>();

            foreach (DataRow row in dt.Rows)
            {
                Model.BaseInfoItem item = new Model.BaseInfoItem();
                item.BaseInfoId= row["ProjectCode"].ToString();
                item.BaseInfoCode = row["Welder"].ToString();
                item.BaseInfoName = "一次合格率：" + row["passRate"].ToString();
                warnWelder.Add(item);
            }

            return warnWelder;
        }
        #endregion

        #region  施工单位一次合格率低于96%预警
        /// <summary>
        /// 施工单位一次合格率低于96%预警
        /// </summary>
        /// <param name="projectArea">项目区域</param>
        /// <returns></returns>
        public static List<Model.BaseInfoItem> GetUnitOnePassRateWarning(string projectArea)
        {

            string strSql = @"SELECT  unit.UnitName,pro.ProjectCode,
	                                 CONVERT(NVARCHAR(10),(CAST((CASE ISNULL(oneCheck.OneCheckJotNum,0) WHEN 0 THEN 0 
		                             ELSE 100.0 * (ISNULL(oneCheck.OneCheckJotNum,0)-ISNULL(oneCheckRepair.oneCheckRepairJotNum,0))/(1.0 * oneCheck.OneCheckJotNum) END) AS DECIMAL(8,1))))+'%' AS passRate
                              FROM dbo.Project_Unit AS pUnit 
							  LEFT JOIN dbo.Base_Unit unit ON unit.UnitId = pUnit.UnitId
							  LEFT JOIN dbo.Base_Project pro ON pro.ProjectId = pUnit.ProjectId

                              LEFT JOIN (SELECT point.UnitId,jot.ProjectId,COUNT(ndeItem.NDEItemID) AS OneCheckJotNum
			                             FROM dbo.Batch_NDEItem ndeItem
				                         LEFT JOIN dbo.Batch_BatchTrustItem trustItem ON trustItem.TrustBatchItemId = ndeItem.TrustBatchItemId
				                         LEFT JOIN dbo.Pipeline_WeldJoint jot ON jot.WeldJointId = trustItem.WeldJointId
					                     LEFT JOIN dbo.Pipeline_WeldingDaily daily ON  daily.WeldingDailyId = jot.WeldingDailyId 	
					                     LEFT JOIN dbo.Batch_PointBatchItem pointItem ON pointItem.PointBatchItemId = trustItem.PointBatchItemId
				                         LEFT JOIN dbo.Batch_PointBatch point ON point.PointBatchId = pointItem.PointBatchId
			                             WHERE pointItem.PointDate IS NOT NULL AND pointItem.PointState=1 AND trustItem.RepairRecordId IS NULL	
			                            GROUP BY jot.ProjectId, point.UnitId) AS oneCheck ON oneCheck.UnitId = pUnit.UnitId AND oneCheck.ProjectId=pUnit.ProjectId

                              LEFT JOIN (SELECT point.UnitId,jot.ProjectId,COUNT(ndeItem.NDEItemID) AS OneCheckRepairJotNum --一次检测返修焊口数
		                                 FROM dbo.Batch_NDEItem ndeItem
				                         LEFT JOIN dbo.Batch_BatchTrustItem trustItem ON trustItem.TrustBatchItemId = ndeItem.TrustBatchItemId
				                         LEFT JOIN dbo.Pipeline_WeldJoint jot ON jot.WeldJointId = trustItem.WeldJointId
					                     LEFT JOIN dbo.Pipeline_WeldingDaily daily ON  daily.WeldingDailyId = jot.WeldingDailyId 	
					                     LEFT JOIN dbo.Batch_PointBatchItem pointItem ON pointItem.PointBatchItemId = trustItem.PointBatchItemId
				                         LEFT JOIN dbo.Batch_PointBatch point ON point.PointBatchId = pointItem.PointBatchId  
			                             WHERE pointItem.PointDate IS NOT NULL AND pointItem.PointState=1 AND trustItem.RepairRecordId IS NULL
				                               AND ndeItem.CheckResult='2' 	
			                             GROUP BY jot.ProjectId, point.UnitId) AS oneCheckRepair 
										 ON oneCheckRepair.UnitId = pUnit.UnitId AND oneCheckRepair.ProjectId=pUnit.ProjectId

                             WHERE  CHARINDEX(pUnit.UnitType,'5')>0
							        AND ISNULL(oneCheck.OneCheckJotNum,0)>0
									AND pro.ProjectArea=@ProjectArea
	                                AND (CAST((CASE ISNULL(oneCheck.OneCheckJotNum,0) WHEN 0 THEN 0 
		                            ELSE 100.0 * (ISNULL(oneCheck.OneCheckJotNum,0)-ISNULL(oneCheckRepair.oneCheckRepairJotNum,0))/(1.0 * oneCheck.OneCheckJotNum) END) AS DECIMAL(8,1)))<=96";

            List<SqlParameter> listStr = new List<SqlParameter>();

            listStr.Add(new SqlParameter("@ProjectArea", projectArea));

            SqlParameter[] parameter = listStr.ToArray();
            DataTable dt = SQLHelper.GetDataTableRunText(strSql, parameter);

            List<Model.BaseInfoItem> warnWelder = new List<Model.BaseInfoItem>();

            foreach (DataRow row in dt.Rows)
            {
                Model.BaseInfoItem item = new Model.BaseInfoItem();
                item.BaseInfoId = row["ProjectCode"].ToString();
                item.BaseInfoCode = row["UnitName"].ToString();
                item.BaseInfoName = "一次合格率：" + row["passRate"].ToString();
                warnWelder.Add(item);
            }

            return warnWelder;
        }
        #endregion
    }
}