JTAPI.git - Gitblit

			@@ -28,32 +28,21 @@
			{
			// --------------生产总览--------------
			var sql = @"select
			sum(CASE AA.clmname WHEN '计划数量' THEN AA.clmqty ELSE 0 END) as 'plan_qty',
			sum(CASE AA.clmname WHEN '完工数量' THEN AA.clmqty ELSE 0 END) as 'end_qty',
			sum(CASE AA.clmname WHEN '合格产出' THEN AA.clmqty ELSE 0 END) as 'good_qty',
			sum(CASE AA.clmname WHEN '缺陷产出' THEN AA.clmqty ELSE 0 END) as 'defect_qty'
			from(
			select isnull(sum(A.plan_qty),0) as clmqty,'计划数量'as clmname from TK_Wrk_Man A
			where A.lm_date between @startopendate and @endclosedate
			union all
			select (isnull(sum(S.report_qty),0)+isnull(sum(S.ng_qty),0)+isnull(sum(S.bad_qty),0)) as endqty,'完工数量' as 完工数量 from TK_Wrk_Man A
			inner join TK_Wrk_Record R on A.wo_code=R.wo_code
			inner join TK_Wrk_RecordSub S on R.id=S.m_id
			inner join TK_Wrk_Step P on A.wo_code=P.wo_code and R.step_code=P.step_code and P.isend='Y'
			where A.lm_date between @startopendate and @endclosedate
			union all
			select isnull(sum(S.report_qty),0) as report_qty,'合格产出' as 合格产出 from TK_Wrk_Man A
			inner join TK_Wrk_Record R on A.wo_code=R.wo_code
			inner join TK_Wrk_RecordSub S on R.id=S.m_id
			inner join TK_Wrk_Step P on A.wo_code=P.wo_code and R.step_code=P.step_code and P.isend='Y'
			where A.lm_date between @startopendate and @endclosedate
			union all
			select (isnull(sum(S.ng_qty),0)+isnull(sum(S.bad_qty),0)) as ngqty,'缺陷产出' as 缺陷产出 from TK_Wrk_Man A
			inner join TK_Wrk_Record R on A.wo_code=R.wo_code
			inner join TK_Wrk_RecordSub S on R.id=S.m_id
			inner join TK_Wrk_Step P on A.wo_code=P.wo_code and R.step_code=P.step_code
			where A.lm_date between @startopendate and @endclosedate
			) as AA";
			sum(CASE AA.clmname WHEN '计划数量' THEN AA.clmqty ELSE 0 END) as 'plan_qty',
			sum(CASE AA.clmname WHEN '合格产出' THEN AA.clmqty ELSE 0 END) as 'good_qty',
			sum(CASE AA.clmname WHEN '缺陷产出' THEN AA.clmqty ELSE 0 END) as 'defect_qty'
			from(
			select isnull(sum(A.plan_qty),0) as clmqty,'计划数量'as clmname from TK_Wrk_Man A
			where A.lm_date between @startopendate and @endclosedate
			union all
			select isnull(sum(P.good_qty),0) as report_qty,'合格产出' as 合格产出 from TK_Wrk_Man A
			inner join TK_Wrk_Step P on A.wo_code=P.wo_code and P.isend='Y'
			where A.lm_date between @startopendate and @endclosedate
			union all
			select (isnull(sum(P.ng_qty),0)+isnull(sum(P.bad_qty),0)) as ngqty,'缺陷产出' as 缺陷产出 from TK_Wrk_Man A
			inner join TK_Wrk_Step P on A.wo_code=P.wo_code
			where A.lm_date between @startopendate and @endclosedate
			) as AA";
			dynamicParams.Add("@startopendate", startopendate + " 00:00:00");
			dynamicParams.Add("@endclosedate", endclosedate + " 23:59:59");
			var data0 = DapperHelper.selectdata(sql, dynamicParams);
			@@ -73,19 +62,23 @@
			switch (defecttype)
			{
			case "prt": //按产品统计
			sql2 = @"select top 5 isnull(sum(A.plan_qty),0) as plan_qty,M.partname,isnull(sum(B.defect_qty),0) as defect_qty from TK_Wrk_Man A
			inner join CSR_WorkRecord_Defect B on A.wo_code=B.wo_code and A.materiel_code=B.partnumber
			sql2 = @"select top 5 sum(AA.plan_qty) as plan_qty,AA.partname,sum(AA.ng_qty)+sum(AA.bad_qty) as defect_qty from (
			select A.plan_qty,M.partname,S.ng_qty,S.bad_qty from TK_Wrk_Man A
			inner join (select wo_code, isnull(sum(ng_qty),0) as ng_qty,isnull(sum(bad_qty),0) as bad_qty from TK_Wrk_Step where ng_qty>0 or bad_qty>0 group by wo_code) S on A.wo_code=S.wo_code
			inner join TMateriel_Info M on A.materiel_code=M.partcode
			where A.lm_date between @startopendate and @endclosedate and B.defect_qty>0
			group by M.partname
			where A.lm_date between @startopendate and @endclosedate and S.ng_qty>0 or S.bad_qty>0
			) as AA
			group by AA.partname
			order by defect_qty desc";
			break;
			case "stp"://按工序统计
			sql2 = @"select top 5 isnull(sum(A.plan_qty),0) as plan_qty,S.stepname,isnull(sum(B.defect_qty),0) as defect_qty from TK_Wrk_Step A
			inner join CSR_WorkRecord_Defect B on A.wo_code=B.wo_code and A.step_code=B.step_code
			inner join TStep S on A.step_code=S.stepcode
			where A.lm_date between @startopendate and @endclosedate and B.defect_qty>0
			group by S.stepname
			sql2 = @"select top 5 sum(AA.plan_qty) as plan_qty,AA.stepname,sum(AA.ng_qty)+sum(AA.bad_qty) as defect_qty from (
			select A.plan_qty,T.stepname,S.ng_qty,S.bad_qty from TK_Wrk_Man A
			inner join (select wo_code,step_code, isnull(sum(ng_qty),0) as ng_qty,isnull(sum(bad_qty),0) as bad_qty from TK_Wrk_Step where ng_qty>0 or bad_qty>0 group by wo_code,step_code) S on A.wo_code=S.wo_code
			inner join TStep T on S.step_code=T.stepcode
			where A.lm_date between @startopendate and @endclosedate and S.ng_qty>0 or S.bad_qty>0
			) as AA
			group by AA.stepname
			order by defect_qty desc";
			break;
			default: