/*******************************************************************************
* Copyright(c) 2014 DongkeSoft All rights reserved. / Confidential
* 类的信息：
* 1.程序名称：ReportModuleLogic.cs
* 2.功能描述：生产线报表分析
* 编辑履历：
* 作者 日期 版本 修改内容
* 宋扬 2014/11/08 1.00 新建
*******************************************************************************/
using System;
using System.Collections.Generic;
using System.Data;
using System.Text;
using Curtain.DataAccess;
using Dongke.IBOSS.PRD.Basics.BaseResources;
using Dongke.IBOSS.PRD.Basics.DataAccess;
using Dongke.IBOSS.PRD.Service.DataModels;
using Dongke.IBOSS.PRD.WCF.DataModels;
using Oracle.ManagedDataAccess.Client;
namespace Dongke.IBOSS.PRD.Service.ReportModuleLogic
{
///
/// 明细报表
///
public class ReportDetailedLogic
{
///
/// 取得RPT080109（半成检缺陷对照表）的查询数据
///
/// 登录用户信息
/// 查询条件
/// 查询结果
public static ServiceResultEntity GetRPT080109DData(SUserInfo user, RPT030106_SE se)
{
IDataAccess dataAccess = null;
try
{
dataAccess = DataAccess.Create();
List parameters = new List();
DataTable data = dataAccess.ExecuteDatatable(ReportDetailedLogic.GetRPT080109DSQL(user, se, ref parameters), parameters.ToArray());
ServiceResultEntity sre = new ServiceResultEntity();
if (data == null || data.Rows.Count == 0)
{
sre.Status = Constant.ServiceResultStatus.NoSearchResults;
return sre;
}
sre.Data = new DataSet();
sre.Data.Tables.Add(data);
return sre;
}
catch (Exception ex)
{
throw ex;
}
finally
{
dataAccess?.Close();
}
}
///
/// 获取RPT080109画面（半成检缺陷对照表）的信息查询sql
///
/// sql
private static string GetRPT080109DSQL(SUserInfo user, RPT030106_SE se, ref List parameters)
{
// 质量登记改判时，缺陷的登记时间是当前时间，不是交接或前次登记时间。
// 追加交接时间，改判标识。
string selSql =
"SELECT pmd.productionlinename\n" +
" ,pmd.procedurename\n" +
" ,pmd.DefectProcedureCode\n" +
" ,pmd.DefectProcedureName\n" +
" ,pmd.DefectUserCode\n" +
" ,pmd.defectusername\n" +
" ,jobs.jobsname defectjobsname\n" +
" ,dt.DefectTypeName\n" +
" ,mstd.Remarks DefectRemarks\n" +
" ,pd.Remarks barcodeRemarks\n" +
" ,pmd.defectcode\n" +
" ,d.alldefectcode\n" +
" ,pmd.defectname\n" +
" ,pmd.UserCode\n" +
" ,pmd.username\n" +
" ,gt.Goodstypename\n" +
" ,pmd.goodscode\n" +
" ,pmd.barcode\n" +
" ,pd.groutingdate\n" +
" ,pd.groutingnum\n" +
" ,pd.groutingMouldCode\n" +
" ,pd.GroutingLineCode\n" +
" ,pd.KilnCarCode\n" +
" ,pd.Procedureid\n" +
" ,gl.goodslevelname Goodsleveltype\n" +
" ,pmd.DefectPositionCode\n" +
" ,pmd.DefectPositionName\n" +
" ,pd.GroutingUserCode\n" +
" , CASE\n" +
" WHEN logo.logoid IS NULL THEN\n" +
" NULL\n" +
" ELSE\n" +
" logo.Logoname || '[' || logo.Logocode || ']'\n" +
" END AS LogoCodeName\n" +
//" ,pmd.CreateTime\n" +
" ,pd.CheckTime pdchecktime\n" +
" ,pmd.CheckTime\n" +
" ,mstdf.DefectFineCode\n" +
" ,pmd.DefectDeductionNum\n" +
//" ,to_char(pmd.DefectDeductionNum, '990.00') DefectDeductionNum\n" +
" ,hopd.createtime handovertime\n" +
" ,(SELECT listagg(to_char(pduc.UserCode), ',') within GROUP(ORDER BY pduc.ProductionDataID) AS WorkUserCodes\n" +
" FROM tp_pm_ProductionData pduc\n" +
" WHERE pduc.barcode = pmd.barcode\n" +
" AND pduc.valueflag = '1') AS WorkUserCodes\n" +
" ,(SELECT listagg(to_char(u.UserCode), ',') within GROUP(ORDER BY pdcu.ProductionDataID) AS CreateUserCodes\n" +
" FROM tp_pm_ProductionData pdcu\n" +
" Left join tp_mst_user u on u.userid = pdcu.createuserid\n" +
" WHERE pdcu.barcode = pmd.barcode\n" +
" AND pdcu.valueflag = '1') AS CreateUserCodes\n" +
" ,(SELECT listagg(to_char(s.StaffName), ',') within GROUP(ORDER BY s.Staffid) AS DefectStaffNames\n" +
" FROM TP_PM_DefectResponsible dr\n" +
" Left join TP_HR_Staff s on s.Staffid = dr.Staffid\n" +
" WHERE dr.ProductionDefectID = pmd.ProductionDefectID --dr pdcu.valueflag = '1'\n" +
" ) AS DefectStaffNames\n" +
" , CASE\n" +
" WHEN pd.isrefire <> '0' THEN\n" +
" (select listagg(to_char(d.defectname || '[' || d.defectpositionname || ']')\n" +
" ,',') within group(order by d.productiondefectid) bsqx\n" +
" from tp_pm_productiondata p\n" +
" inner join tp_pm_defect d\n" +
" on d.productiondataid = p.productiondataid\n" +
" where p.valueflag = '1'\n" +
" and d.valueflag = '1'\n" +
" and p.barcode = pd.barcode\n" +
" and p.modeltype = -1\n" +
" and p.isrefire = '0')\n" +
" ELSE\n" +
" null\n" +
" END AS bsqx\n" +
" , CASE\n" +
" WHEN EXISTS (SELECT 1\n" +
" FROM tp_pm_ProductionData pdud\n" +
" WHERE pdud.barcode = pmd.barcode\n" +
" AND pdud.modeltype = -1\n" +
" AND pdud.procedureid = pd.procedureid\n" +
" AND pdud.kilncarbatchno = pd.kilncarbatchno\n" +
" AND pdud.valueflag = '0') THEN\n" +
" 1\n" +
" ELSE\n" +
" 0\n" +
" END AS undoflag\n" +
" ,semid.DEFECTNAME AS 半检缺陷\n" +
//" ,'[' || semid.DEFECTPOSITIONCODE || ']' ||semid.DEFECTPOSITIONNAME AS 半检位置\n" +
" ,semid.DEFECTPOSITIONCODE AS 位置编码\n" +
" ,semid.DEFECTPOSITIONNAME AS 半检位置\n" +
" ,semi.SEMIREWORKUSERCODE AS 半检工号\n" +
" FROM tp_pm_defect pmd\n" +
" LEFT JOIN TP_PC_Procedure p\n" +
" ON p.procedureid = pmd.procedureid\n" +
" LEFT JOIN TP_PC_Procedure pdd\n" +
" ON pdd.procedureid = pmd.defectprocedureid\n" +
" LEFT JOIN TP_MST_Goods g\n" +
" ON pmd.Goodsid = g.Goodsid\n" +
" LEFT JOIN TP_MST_GoodsType gt\n" +
" ON g.GoodsTypeID = gt.GoodsTypeID\n" +
" LEFT JOIN TP_MST_Defect mstd\n" +
" ON pmd.DefectID = mstd.DefectID\n" +
" LEFT JOIN TP_MST_Jobs jobs\n" +
" ON jobs.jobsid = pmd.Defectjobs\n" +
" LEFT JOIN TP_MST_DefectType dt\n" +
" ON mstd.DefectTypeID = dt.DefectTypeID\n" +
" INNER JOIN tp_pm_productiondata pd\n" +
" ON pmd.ProductionDataID = pd.ProductionDataID\n" +
" LEFT JOIN tp_mst_goodslevel gl\n" +
" ON pd.goodslevelid = gl.goodslevelid\n" +
//"-- 交接工序\n" +
//" INNER JOIN tp_pm_productiondata hopd\n" +
//" ON hopd.procedureid = (select procedureid from tp_mst_rptsprocedure t where t.rptprocedureid = :HandOverProcedureID) \n" +
//" AND hopd.valueflag = '1'\n" +
//" AND hopd.barcode = pd.barcode\n" +
//" AND hopd.kilncarbatchno = pd.kilncarbatchno\n" +
" INNER JOIN tp_pm_productiondata hopd\n" +
" ON hopd.kilncarbatchno = pd.kilncarbatchno\n " +
" AND hopd.barcode = pd.barcode\n" +
" AND hopd.valueflag = '1'\n" +
" AND hopd.procedureid = (select procedureid from tp_mst_rptsprocedure t where t.rptprocedureid = @HandOverProcedureID@) \n" +
" LEFT JOIN TP_MST_DefectFine mstdf\n" +
" ON pmd.DefectFine = mstdf.DefectFineID\n" +
" LEFT JOIN tp_mst_logo logo\n" +
" ON pd.logoid = logo.logoid\n" +
" LEFT JOIN (SELECT def.barcode, wmsys.wm_concat( to_char(def.defectcode)) alldefectcode\n" +
" FROM tp_pm_defect def\n" +
" WHERE def.valueflag = '1'\n" +
//def.createtime
" group by def.barcode ) d\n" +
" ON d.barcode = pmd.barcode\n" +
" INNER JOIN TP_PM_SEMIREWORK semi ON semi.GROUTINGDAILYDETAILID = hopd.GROUTINGDAILYDETAILID AND semi.VALUEFLAG = '1' AND semi.BACKOUTFLAG = '0' \n" +
" INNER JOIN TP_PM_SEMIREWORKDEFECT semid ON semi.SEMIREWORKID = semid.SEMIREWORKDEFECTID AND semid.VALUEFLAG = '1' \n" +
" WHERE pmd.valueflag = '1' \n" +
" AND pd.ModelType = -1\n" +
" AND pmd.AccountID = @AccountID@\n";
parameters.Add(new CDAParameter("@AccountID@", user.AccountID));
parameters.Add(new CDAParameter("@HandOverProcedureID@", se.HandOverProcedureID));
//selSql = selSql +
// " Where TP_PM_Defect.valueflag='1' and tp_pm_productiondata.ModelType=-1" +
// " and TP_PM_Defect.AccountID=:AccountID ";
if (!string.IsNullOrEmpty(se.GoodsTypeCode))
{
selSql = selSql + " AND instr(gt.GOODSTYPECode,@GoodsTypeCode@)=1 ";
parameters.Add(new CDAParameter("@GoodsTypeCode@", se.GoodsTypeCode));
}
if (!string.IsNullOrEmpty(se.LineIDS))
{
selSql = selSql + " AND instr(','||@ProductionLineIDS@||',',','||pmd.ProductionLineId||',')>0 ";
parameters.Add(new CDAParameter("@ProductionLineIDS@", se.LineIDS));
}
if (!string.IsNullOrEmpty(se.DefectProcedureIDS))
{
//selSql = selSql + " AND (instr(','||:DefectProcedureIDS||',',','||TP_PM_Defect.DefectProcedureID||',')>0 or TP_PM_Defect.DefectProcedureID is null ) ";
selSql = selSql + " AND (instr(','||@DefectProcedureIDS@||',',','||pmd.DefectProcedureID||',')>0 ) ";
parameters.Add(new CDAParameter("@DefectProcedureIDS@", se.DefectProcedureIDS));
}
else
{
// modify by chenxy ：没有选择时，应全查询出来。
//selSql = selSql + " AND TP_PM_Defect.DefectProcedureID is null ";//因为有无责任工序
}
if (!string.IsNullOrEmpty(se.CheckProcedureIDS))
{
selSql = selSql + " AND instr(','||@ProcedureIDS@||',',','||pmd.ProcedureID||',')>0 ";
parameters.Add(new CDAParameter("@ProcedureIDS@", se.CheckProcedureIDS));
}
if (!string.IsNullOrEmpty(se.KilnCode))
{
selSql = selSql + " AND instr(pd.KilnCode,@KilnCode@) >0 ";
parameters.Add(new CDAParameter("@KilnCode@", se.KilnCode));
}
if (!string.IsNullOrEmpty(se.GoodsIDS))
{
selSql = selSql + " AND instr(','||@GoodsIDS@||',',','||pmd.Goodsid||',')>0 ";
parameters.Add(new CDAParameter("@GoodsIDS@", se.GoodsIDS));
}
if (!string.IsNullOrEmpty(se.GoodsCode))
{
selSql = selSql + " AND instr(pmd.goodsCode,@GoodsCode@) >0 ";
parameters.Add(new CDAParameter("@GoodsCode@", se.GoodsCode));
}
if (!string.IsNullOrEmpty(se.GoodsCodeS2D))
{
selSql = selSql + " AND pmd.goodsCode = @GoodsCodeS2D@ ";
parameters.Add(new CDAParameter("@GoodsCodeS2D@", se.GoodsCodeS2D));
}
if (!string.IsNullOrEmpty(se.DefectUserCode))
{
selSql = selSql + " AND instr(pmd.DefectUserCode,@DefectUserCode@) >0 ";
parameters.Add(new CDAParameter("@DefectUserCode@", se.DefectUserCode));
}
if (!string.IsNullOrEmpty(se.DefectTypeIDS))
{
selSql = selSql + " AND instr(','||@DefectTypeIDS@||',',','||dt.DefectTypeID||',')>0 ";
parameters.Add(new CDAParameter("@DefectTypeIDS@", se.DefectTypeIDS));
}
if (!string.IsNullOrEmpty(se.DefectCode))
{
selSql = selSql + " AND instr(pmd.DefectCode,@DefectCode@) >0 ";
parameters.Add(new CDAParameter("@DefectCode@", se.DefectCode));
}
if (!string.IsNullOrEmpty(se.DefectCodeS2D))
{
selSql = selSql + " AND pmd.DefectCode = @DefectCodeS2D@ ";
parameters.Add(new CDAParameter("@DefectCodeS2D@", se.DefectCodeS2D));
}
if (!string.IsNullOrEmpty(se.DefectName))
{
selSql = selSql + " AND instr(pmd.DefectName,@DefectName@) >0 ";
parameters.Add(new CDAParameter("@DefectName@", se.DefectName));
}
if (!string.IsNullOrEmpty(se.CheckUserCode))
{
selSql = selSql + " AND instr(pmd.UserCode,@UserCode@) >0";
parameters.Add(new CDAParameter("@UserCode@", se.CheckUserCode));
}
if (!string.IsNullOrEmpty(se.CheckUserCodeS2D))
{
selSql = selSql + " AND pmd.UserCode = @CheckUserCodeS2D@ ";
parameters.Add(new CDAParameter("@CheckUserCodeS2D@", se.CheckUserCodeS2D));
}
// 成检登记时间
if (se.CreateTimeStart.HasValue)
{
selSql = selSql + " AND pd.checktime>= @CreateTimeStart@ ";
parameters.Add(new CDAParameter("@CreateTimeStart@", se.CreateTimeStart.Value));
}
if (se.CreateTimeEnd.HasValue)
{
selSql = selSql + " AND pd.checktime<= @CreateTimeEnd@ ";
parameters.Add(new CDAParameter("@CreateTimeEnd@", se.CreateTimeEnd.Value));
}
// 缺陷登记时间
if (se.CheckTimeStart.HasValue)
{
selSql = selSql + " AND pmd.checktime>= @CheckTimeStart@ ";
parameters.Add(new CDAParameter("@CheckTimeStart@", se.CheckTimeStart.Value));
}
if (se.CheckTimeEnd.HasValue)
{
selSql = selSql + " AND pmd.checktime<= @CheckTimeEnd@ ";
parameters.Add(new CDAParameter("@CheckTimeEnd@", se.CheckTimeEnd.Value));
}
// 交接时间
if (se.HandOverTimeStart.HasValue)
{
selSql = selSql + " AND hopd.CreateTime>= @HandOverTimeStart@ ";
parameters.Add(new CDAParameter("@HandOverTimeStart@", se.HandOverTimeStart.Value));
}
if (se.HandOverTimeEnd.HasValue)
{
selSql = selSql + " AND hopd.CreateTime<= @HandOverTimeEnd@ ";
parameters.Add(new CDAParameter("@HandOverTimeEnd@", se.HandOverTimeEnd.Value));
}
//selSql = selSql + " order by pmd.productionlinename,pmd.procedurename,pmd.defectprocedurename,pmd.defectusercode ";
selSql = selSql + " order by pmd.productionlinename,p.DISPLAYNO,pdd.DISPLAYNO,dt.DISPLAYNO,mstd.DISPLAYNO,pmd.defectusercode,pmd.barcode ";
return selSql;
}
}
}