namespace MMDAL.Query.Production
{
    // GB4 Production.svc ProductionSummaryRegister migration (ProductionDAL.ProductionSummaryRegister,
    // ProductionQueryBuilder.cs). {DynamicFilter} is replaced by the DAL with zero or more "AND ..."
    // clauses built from ProductionSummaryRegisterFilterBuilder (never string-interpolated values —
    // always bound Dapper parameters). {OrderBy} is replaced with the ORDER BY column list GB4 branches
    // on (OrderBy=1 vs default). Table/column names and join logic are kept byte-identical to GB4 — this
    // is a straight port, not a redesign.
    public static class ProductionSummaryRegisterQB
    {
        // ── Type=0: Production Summary (grouped by item/SKU/process/workcenter) ─────────────────────

        public const string PRODUCTION_SUMMARY_REPORT_SQL = @"
select
    b.ITEMID as ItemId,
    c.ITEMCODE as ItemCode,
    c.ITEMNAME as ItemName,
    b.SKUID as SKUId,
    d.SKUCODE as SKUCode,
    d.SKUNAME as SKUName,
    h.PROCESSID as ProcessId,
    f.PROCESSCODE as ProcessCode,
    f.PROCESSNAME as ProcessName,
    b.UOMID as UOMId,
    g.UOMCODE as UOMCode,
    g.UOMNAME as UOMName,
    sum(case when a.TYPE =0 then  ISNULL( b.PRODUCTIONQUANTITY,0) else 0  END )as Produced,
    sum(case when a.TYPE =1 then  ISNULL( b.PRODUCTIONQUANTITY ,0) else 0 END ) as Reprocess,
    sum(b.GOODQUANTITY) as Accepeted,
    sum(b.REWORKQUANTITY )as Rework,
    sum(b.REJECTEDQUANTITY) as Rejected,
    sum(b.PROCESSCOST )as ProcessCost,
    sum(case when a.TYPE =0 then  ISNULL( b.PRODUCTIONQUANTITY,0) else 0  END )*sum(b.PROCESSCOST ) as ProcessValue,
    Max(b.MATERIALCOST) as MaterialCost,
    a.WORKCENTERID as WorkCenterId,I.WORKCENTERCODE as WorkCenterCode,I.WORKCENTERNAME as WorkCenterName
from TPRODUCTION a, TPRODUCTIONDETAIL b, MITEM c, MSKU d,TINDENTDETAIL e, TINDENT h, MPROCESS f, MUOM g
   ,MWORKCENTER I,MDEVICE md,MUSER u
where 1=1
    and a.PRODUCTIONID =b.PRODUCTIONID
    and b.ITEMID =c.ITEMID
    and b.SKUID =d.SKUID
    and e.INDENTDETAILID =b.INDENTDETAILID
    and e.INDENTID =h.INDENTID
    and h.PROCESSID =f.PROCESSID
    and b.UOMID =g.UOMID
    and md.DEVICEID = u.DEFAULTDEVICEID
    and u.userid=a.createdbyid
    and A.WORKCENTERID=I.WORKCENTERID
    and b.PRODUCTIONQUANTITY <>0
    {DynamicFilter}
group by b.ITEMID,c.ITEMCODE, c.ITEMNAME ,b.SKUID,d.SKUCODE ,d.SKUNAME,h.PROCESSID,f.PROCESSCODE,f.PROCESSNAME,b.UOMID,g.UOMCODE,g.UOMNAME,a.TYPE, a.WORKCENTERID,I.WORKCENTERCODE,I.WORKCENTERNAME
order by I.WORKCENTERNAME,c.ITEMCODE";

        public const string PRODUCTION_SUMMARY_REPORT_PG = @"
select
    b.ITEMID as ""ItemId"",
    c.ITEMCODE as ""ItemCode"",
    c.ITEMNAME as ""ItemName"",
    b.SKUID as ""SKUId"",
    d.SKUCODE as ""SKUCode"",
    d.SKUNAME as ""SKUName"",
    h.PROCESSID as ""ProcessId"",
    f.PROCESSCODE as ""ProcessCode"",
    f.PROCESSNAME as ""ProcessName"",
    b.UOMID as ""UOMId"",
    g.UOMCODE as ""UOMCode"",
    g.UOMNAME as ""UOMName"",
    sum(case when a.TYPE =0 then  COALESCE( b.PRODUCTIONQUANTITY,0) else 0  END ) as ""Produced"",
    sum(case when a.TYPE =1 then  COALESCE( b.PRODUCTIONQUANTITY ,0) else 0 END ) as ""Reprocess"",
    sum(b.GOODQUANTITY) as ""Accepeted"",
    sum(b.REWORKQUANTITY ) as ""Rework"",
    sum(b.REJECTEDQUANTITY) as ""Rejected"",
    sum(b.PROCESSCOST ) as ""ProcessCost"",
    sum(case when a.TYPE =0 then  COALESCE( b.PRODUCTIONQUANTITY,0) else 0  END )*sum(b.PROCESSCOST ) as ""ProcessValue"",
    Max(b.MATERIALCOST) as ""MaterialCost"",
    a.WORKCENTERID as ""WorkCenterId"",I.WORKCENTERCODE as ""WorkCenterCode"",I.WORKCENTERNAME as ""WorkCenterName""
from TPRODUCTION a, TPRODUCTIONDETAIL b, MITEM c, MSKU d,TINDENTDETAIL e, TINDENT h, MPROCESS f, MUOM g
   ,MWORKCENTER I,MDEVICE md,MUSER u
where 1=1
    and a.PRODUCTIONID =b.PRODUCTIONID
    and b.ITEMID =c.ITEMID
    and b.SKUID =d.SKUID
    and e.INDENTDETAILID =b.INDENTDETAILID
    and e.INDENTID =h.INDENTID
    and h.PROCESSID =f.PROCESSID
    and b.UOMID =g.UOMID
    and md.DEVICEID = u.DEFAULTDEVICEID
    and u.userid=a.createdbyid
    and A.WORKCENTERID=I.WORKCENTERID
    and b.PRODUCTIONQUANTITY <>0
    {DynamicFilter}
group by b.ITEMID,c.ITEMCODE, c.ITEMNAME ,b.SKUID,d.SKUCODE ,d.SKUNAME,h.PROCESSID,f.PROCESSCODE,f.PROCESSNAME,b.UOMID,g.UOMCODE,g.UOMNAME,a.TYPE, a.WORKCENTERID,I.WORKCENTERCODE,I.WORKCENTERNAME
order by I.WORKCENTERNAME,c.ITEMCODE";

        public const string PRODUCTION_SUMMARY_REPORT_TOTAL_SQL = @"
select
    sum(b.PROCESSCOST )as TotalProcessCost,
    sum(case when a.TYPE =0 then  ISNULL( b.PRODUCTIONQUANTITY,0) else 0  END )*sum(b.PROCESSCOST ) as TotalProcessValue,
    Max(b.MATERIALCOST) as TotalMaterialCost
from TPRODUCTION a,
     TPRODUCTIONDETAIL b,
     MITEM c,
     MSKU d,
     TINDENTDETAIL e,
     TINDENT h,
     MPROCESS f,
     MUOM g
    ,MWORKCENTER I
where 1=1
    and a.PRODUCTIONID =b.PRODUCTIONID
    and b.ITEMID =c.ITEMID
    and b.SKUID =d.SKUID
    and e.INDENTDETAILID =b.INDENTDETAILID
    and e.INDENTID =h.INDENTID
    and h.PROCESSID =f.PROCESSID
    and b.UOMID =g.UOMID
    and A.WORKCENTERID=I.WORKCENTERID
    and b.PRODUCTIONQUANTITY <>0
    {DynamicFilter}";

        public const string PRODUCTION_SUMMARY_REPORT_TOTAL_PG = @"
select
    sum(b.PROCESSCOST ) as ""TotalProcessCost"",
    sum(case when a.TYPE =0 then  COALESCE( b.PRODUCTIONQUANTITY,0) else 0  END )*sum(b.PROCESSCOST ) as ""TotalProcessValue"",
    Max(b.MATERIALCOST) as ""TotalMaterialCost""
from TPRODUCTION a,
     TPRODUCTIONDETAIL b,
     MITEM c,
     MSKU d,
     TINDENTDETAIL e,
     TINDENT h,
     MPROCESS f,
     MUOM g
    ,MWORKCENTER I
where 1=1
    and a.PRODUCTIONID =b.PRODUCTIONID
    and b.ITEMID =c.ITEMID
    and b.SKUID =d.SKUID
    and e.INDENTDETAILID =b.INDENTDETAILID
    and e.INDENTID =h.INDENTID
    and h.PROCESSID =f.PROCESSID
    and b.UOMID =g.UOMID
    and A.WORKCENTERID=I.WORKCENTERID
    and b.PRODUCTIONQUANTITY <>0
    {DynamicFilter}";

        // ── Type=1: Production Register (detail rows) ───────────────────────────────────────────────

        public const string PRODUCTION_REGISTER_REPORT_SQL = @"
select
ma.ALLOCATIONNAME as AllocationName,
ma.ALLOCATIONSHORTNAME as AllocationShortName,
h.INDENTID as IndentId,
h.INDENTDATE as IndentDate,
h.INDENTNUMBER as IndentNumber,
e.INDENTDETAILID as IndentDetailId,
h.COMBINEINDENTID as CombineIndentId,
e.PLANNEDQUANTITY as PlannedQuantity,
a.PRODUCTIONDATE as Date,
a.PRODUCTIONID as ProductionId,
a.PRODUCTIONNUMBER as Number,
case when e.nestingplanid<>-1 then npitem.ITEMID else b.ITEMID end as ItemId,
case when e.nestingplanid<>-1 then npitem.ITEMCODE  else c.ITEMCODE end as ItemCode,
case when e.nestingplanid<>-1 then npitem.ITEMNAME  else c.ITEMNAME end as ItemName,
case when e.nestingplanid<>-1 then npsku .SKUID  else b.SKUID  end as SKUId,
case when e.nestingplanid<>-1 then npsku.SKUCODE   else d.SKUCODE end as SKUCode,
case when e.nestingplanid<>-1 then npsku.SKUNAME   else d.SKUNAME end as SKUName,
h.PROCESSID as ProcessId,
f.PROCESSCODE as ProcessCode,
f.PROCESSNAME as ProcessName,
b.UOMID as UOMId,
g.UOMCODE as UOMCode,
g.UOMNAME as UOMName,
b.starttime as DetailStartTime,
b.endtime as DetailEndTime,
sr.RowFilter as Row,
case when a.TYPE =0 then
case when e.nestingplanid<>-1 then packprod.PackQtyproduced else
( (ISNULL( b.PRODUCTIONQUANTITY,0)) - (ISNULL( b.EXCESSSHORTAGEQUANTITY,0))) end
else 0  END as Produced ,
CASE WHEN ROW_NUMBER() OVER (PARTITION BY h.INDENTID,a.ProductionId  ORDER BY a.ProductionId )=1 then 1 else 0 end*
 case when a.TYPE =0 then
case when e.nestingplanid<>-1 then packprod.PackQtyproduced else
( (ISNULL( b.PRODUCTIONQUANTITY,0)) - (ISNULL( b.EXCESSSHORTAGEQUANTITY,0))) end
else 0  END as SubProduced ,
e.PLANNEDQUANTITY - (case when a.TYPE =0 then case when e.nestingplanid<>-1 then packprod.PackQtyproduced else
( (ISNULL( b.PRODUCTIONQUANTITY,0)) - (ISNULL( b.EXCESSSHORTAGEQUANTITY,0))) end
else 0  END )as BalanceQuantity,
case when a.TYPE =1 then  ISNULL( b.PRODUCTIONQUANTITY ,0) else 0 END as Reprocess ,
b.GOODQUANTITY  as Accepeted,
b.REWORKQUANTITY as Rework,
b.REJECTEDQUANTITY as Rejected,
b.PROCESSCOST as ProcessCost,
(b.PRODUCTIONQUANTITY *b.PROCESSCOST)as ProcessValue,
b.MATERIALCOST as MaterialCost,
(b.MATERIALCOST *b.PRODUCTIONQUANTITY)as MaterialValue,
b.OUTPUTWEIGHT as NetWeight,
A.INCHARGEID as InchargeId,k.EMPLOYEECODE as InchargeCode,k.EMPLOYEENAME as InchargeName,
A.WORKCENTERID as WorkCenterId,I.Workcentercode as WorkCenterCode,I.WorkCenterName as WorkCenterName,
h.NATURE   as LocationType,
e.RELEASESTATUS as ReleaseStatus,
rt.resourcetypecode as ResourceTypeCode,
rr.Duration as ResourceDuration,
isnull (ss.STOPPAGEDURATION,0) as StoppageDuration,
rr.Duration - isnull (ss.STOPPAGEDURATION,0) as ExcludeStoppageResource,
cast ( floor( isnull(ss.STOPPAGEDURATION,0) / 60)+(cast (isnull(ss.STOPPAGEDURATION,0) as Numeric(18,4))-(floor( isnull(ss.STOPPAGEDURATION,0)/ 60)*60))/60 as Numeric(18,4)) StoppageDurationHrs,
cast ( floor( isnull(rr.Duration,0) / 60)+(cast (isnull(rr.Duration,0) as Numeric(18,4))-(floor( isnull(rr.Duration,0)/ 60)*60))/60 as Numeric(18,4)) ResourceDurationHrs,
cast ( floor( isnull(rr.Duration,0) / 60)+(cast (isnull(rr.Duration,0) as Numeric(18,4))-(floor( isnull(rr.Duration,0)/ 60)*60))/60 as Numeric(18,4)) -
cast ( floor( isnull(ss.STOPPAGEDURATION,0) / 60)+(cast (isnull(ss.STOPPAGEDURATION,0) as Numeric(18,4))-(floor( isnull(ss.STOPPAGEDURATION,0)/ 60)*60))/60 as Numeric(18,4)) as ExcludeStoppageResourceHrs,
isnull(cost.cost,0) as ActualResourceCost,
case when a.TYPE =0 then
  case when e.nestingplanid<>-1 then packprod.PackQtyproduced else
  (ISNULL( b.PRODUCTIONQUANTITY,0)) - (ISNULL( b.EXCESSSHORTAGEQUANTITY,0)) end
END as ProcessResourceValue,
rr.STARTDATE as ResourceFromDate,
rr.STARTTIME as ResourceStartTime,
rr.ENDTIME as ResourceEndTime,
rr.ENDDATE as ResourceEndDate,
rr.QUANTITY as ResourceProduced,
rr.EMPLOYEEID as EmployeeId,
ee.EMPLOYEECODE as EmployeeCode,
ee.EMPLOYEENAME as EmployeeName,
rr.MACHINEID as MachineId,
mm.MACHINECODE as MachineCode,
mm.MACHINENAME as MachineName,
rr.RESOURCEID as ResourceId,
res.RESOURCECODE as ResourceCode,
res.RESOURCENAME as ResourceName,
rt.RESOURCENATURE as ResourceNature,
case when e.nestingplanid<>-1 then rr.QUANTITY else b.GOODQUANTITY end as AccepetedQuantity,
case when rr.EMPLOYEEID<>-1 then rr.EMPLOYEEID
when rr.RESOURCEID<>-1 then rr.RESOURCEID
when rr.MACHINEID <>-1 then rr.MACHINEID else -1 End as Id,
case when rr.EMPLOYEEID<>-1 then ee.EMPLOYEECODE
when rr.RESOURCEID<>-1 then res.RESOURCECODE
when rr.MACHINEID <>-1 then mm.MACHINECODE else '' End as Code,
case when rr.EMPLOYEEID<>-1 then ee.EMPLOYEENAME
when rr.RESOURCEID<>-1 then res.RESOURCENAME
when rr.MACHINEID <>-1 then mm.MACHINENAME else '' End as Name
,bom.FROMDATE as FromDate,
bom.TODATE as ToDate,
bom.VERSIONNAME as BomVersion,
routingver.VERSION as RoutingVersion,
a.Remarks as Remarks,
u.USERNAME as CreatedByName,
CONVERT(date, a.CREATEDON) as CreatedonDate ,
convert(char(5), a.CREATEDON, 108) as CreatedTime,
md.devicename as DeviceName,
isnull(pc.PROCESSCOST,0) as PCProcessCost,
isnull(pc.MATERIALCOST ,0) as PCMaterialCost,
isnull(pc.baserate ,0) as PCBaseRate
,tr.PLANDURATION as PlannedDuration
,(isnull(tr.PLANDURATION,0) - isnull(rr.Duration,0)) as EstimatedDuration
,bom.BOMCODE as BomCode
,bom.BOMNAME as BomName ,
case when (isnull(tr.PLANDURATION,0)) <>0 then isnull(rr.Duration,0)/(isnull(tr.PLANDURATION,0))  *100.0 else 1.0 end as Efficiency,
st.shiftdescription as ShiftName
from
TPRODUCTION a CROSS JOIN TPRODUCTIONDETAIL b
 left outer join  (select PRODUCTIONDETAILID,sum(proditem.packquantity) as PackQtyproduced,sum(proditem.GOODQUANTITY) as PackQtyaccepted from TPRODUCTIONITEMS  proditem group by PRODUCTIONDETAILID ) packprod
    on (packprod.PRODUCTIONdetailID=b.PRODUCTIONdetailID)
    left outer join tproductionresource rr on
    (b.PRODUCTIONID =rr.PRODUCTIONID
    and b.PRODUCTIONDETAILID =rr.PRODUCTIONDETAILID)
    left outer join memployee ee on (ee.employeeid=rr.employeeid)
    left outer join mmachine mm on (mm.machineid=rr.machineid)
    left outer join mresource res on (res.resourceid=rr.resourceid)
    left outer join mresourcetype rt on (rr.RESOURCETYPEID =rt.RESOURCETYPEID)
    left outer join  (select PRODUCTIONRESOURCEID ,sum(pss.STOPPAGEDURATION ) as StoppageDuration from TPRODUCTIONSTOPPAGE  pss group by PRODUCTIONRESOURCEID ) ss
    on (ss.PRODUCTIONRESOURCEID=rr.PRODUCTIONRESOURCEID)
    left outer join  (select
    ROW_NUMBER() OVER (PARTITION BY productionid ORDER BY PRODUCTIONRESOURCEID) AS RowFilter,
    PRODUCTIONRESOURCEID as ProductionResourceId from TPRODUCTIONRESOURCE  pss group by PRODUCTIONRESOURCEID,productionid ) sr
    on (sr.PRODUCTIONRESOURCEID=rr.PRODUCTIONRESOURCEID)
    left outer join MRESOURCETYPECOST cost on (rr.RESOURCETYPEID = COST.RESOURCETYPEID and cost.type=2 and COST.PERIODFROM <= a.PRODUCTIONDATE AND COST.PERIODTO >= a.PRODUCTIONDATE)
    outer apply (select top(1) * from MPRODUCTCOST  pc
    where b.itemid=pc.itemid and b.skuid=pc.skuid and pc.type=0
    and pc.ouid = a.OUID
    and pc.todate >= a.PRODUCTIONDATE
   and pc.fromdate<= a.PRODUCTIONDATE   order by pc.TODATE desc ) pc,
    MITEM c, MSKU d,TINDENTDETAIL e
    left outer join TINDENTRESOURCE tr on (tr.INDENTDETAILID=e.INDENTDETAILID)
    Outer apply
    (select
    top(1) bom.* from TINDENTDETAIL  final,
    (select
       ind.COMBINEINDENTID
       from
        TINDENTDETAIL dtl,TINDENT ind
        where dtl.INDENTDETAILID=e.INDENTDETAILID
        and dtl.INDENTID=ind.INDENTID) sub,Mbom bom
        where final.INDENTID=sub.COMBINEINDENTID
    and final.BOMID=bom.BOMID  )bom
    outer apply (select b.* from  MROUTINGVERSIONDETAIL  a, MROUTINGVersion b,TINDENT c where a.ROUTINGDETAILID=e.ROUTINGDETAILID
    and  c.indentid=e.indentid and  a.ROUTINGVERSIONID=b.ROUTINGVERSIONID and b.FROMDATE<=c.INDENTDATE and b.TODATE>=c.INDENTDATE) routingver
    , TINDENT h, MPROCESS f, MUOM g
    ,MEMPLOYEE k,MWORKCENTER I,
    TNESTINGPLAN np,
    MITEM npitem,
    MSKU npsku
   ,MALLOCATION ma ,MUSER u,MDEVICE md ,mshift st
where 1=1
    and a.PRODUCTIONID =b.PRODUCTIONID
    and b.ITEMID =c.ITEMID
    and b.SKUID =d.SKUID
    and e.INDENTDETAILID =b.INDENTDETAILID
    and e.INDENTID =h.INDENTID
    and h.PROCESSID =f.PROCESSID
    and b.UOMID =g.UOMID
    and k.EMPLOYEEID = a.inchargeid
    and A.Workcenterid=I.WorkcenterId
    and np.NESTINGPLANID =e.NESTINGPLANID
    and npitem.ITEMID =np.SHEETITEMID
    and npsku.SKUID =np.SHEETSKUID
    and ma.ALLOCATIONID =e.ALLOCATIONID
    and a.CREATEDBYID =u.USERID
    and a.shiftid=st.shiftid
    and md.DEVICEID = u.DEFAULTDEVICEID
    {ResourceReqFilter}
    {DynamicFilter}
    {SkuTypeFilter}
ORDER BY {OrderBy}
OFFSET @Offset ROWS FETCH NEXT @PageSize ROWS ONLY";

        public const string PRODUCTION_REGISTER_REPORT_COUNT_SQL = @"
select count(*)
from
TPRODUCTION a CROSS JOIN TPRODUCTIONDETAIL b
 left outer join  (select PRODUCTIONDETAILID,sum(proditem.packquantity) as PackQtyproduced,sum(proditem.GOODQUANTITY) as PackQtyaccepted from TPRODUCTIONITEMS  proditem group by PRODUCTIONDETAILID ) packprod
    on (packprod.PRODUCTIONdetailID=b.PRODUCTIONdetailID)
    left outer join tproductionresource rr on
    (b.PRODUCTIONID =rr.PRODUCTIONID
    and b.PRODUCTIONDETAILID =rr.PRODUCTIONDETAILID)
    left outer join memployee ee on (ee.employeeid=rr.employeeid)
    left outer join mmachine mm on (mm.machineid=rr.machineid)
    left outer join mresource res on (res.resourceid=rr.resourceid)
    left outer join mresourcetype rt on (rr.RESOURCETYPEID =rt.RESOURCETYPEID)
    left outer join  (select PRODUCTIONRESOURCEID ,sum(pss.STOPPAGEDURATION ) as StoppageDuration from TPRODUCTIONSTOPPAGE  pss group by PRODUCTIONRESOURCEID ) ss
    on (ss.PRODUCTIONRESOURCEID=rr.PRODUCTIONRESOURCEID)
    left outer join  (select
    ROW_NUMBER() OVER (PARTITION BY productionid ORDER BY PRODUCTIONRESOURCEID) AS RowFilter,
    PRODUCTIONRESOURCEID as ProductionResourceId from TPRODUCTIONRESOURCE  pss group by PRODUCTIONRESOURCEID,productionid ) sr
    on (sr.PRODUCTIONRESOURCEID=rr.PRODUCTIONRESOURCEID)
    ,MITEM c, MSKU d,TINDENTDETAIL e
    , TINDENT h, MPROCESS f, MUOM g
    ,MEMPLOYEE k,MWORKCENTER I
   ,MALLOCATION ma ,MUSER u,MDEVICE md
where 1=1
    and a.PRODUCTIONID =b.PRODUCTIONID
    and b.ITEMID =c.ITEMID
    and b.SKUID =d.SKUID
    and e.INDENTDETAILID =b.INDENTDETAILID
    and e.INDENTID =h.INDENTID
    and h.PROCESSID =f.PROCESSID
    and b.UOMID =g.UOMID
    and k.EMPLOYEEID = a.inchargeid
    and A.Workcenterid=I.WorkcenterId
    and ma.ALLOCATIONID =e.ALLOCATIONID
    and a.CREATEDBYID =u.USERID
    and md.DEVICEID = u.DEFAULTDEVICEID
    {ResourceReqFilter}
    {DynamicFilter}
    {SkuTypeFilter}";

        public const string PRODUCTION_REGISTER_REPORT_TOTAL_SQL = @"
select
    sum(b.PROCESSCOST) as TotalProcessCost,
    sum(b.PRODUCTIONQUANTITY * b.PROCESSCOST) as TotalProcessValue,
    sum(b.MATERIALCOST) as TotalMaterialCost,
    sum(b.MATERIALCOST * b.PRODUCTIONQUANTITY) as TotalMaterialValue,
    sum(isnull(cost.cost,0)) as TotalActualResourceCost
from
TPRODUCTION a CROSS JOIN TPRODUCTIONDETAIL b
    left outer join tproductionresource rr on
    (b.PRODUCTIONID =rr.PRODUCTIONID
    and b.PRODUCTIONDETAILID =rr.PRODUCTIONDETAILID)
    left outer join mmachine mm on (mm.machineid=rr.machineid)
    left outer join MRESOURCETYPECOST cost on (rr.RESOURCETYPEID = COST.RESOURCETYPEID and cost.type=2 and COST.PERIODFROM <= a.PRODUCTIONDATE AND COST.PERIODTO >= a.PRODUCTIONDATE)
    ,MITEM c, MSKU d,TINDENTDETAIL e
    , TINDENT h, MPROCESS f, MUOM g
    ,MWORKCENTER I
   ,MALLOCATION ma ,MUSER u,MDEVICE md
where 1=1
    and a.PRODUCTIONID =b.PRODUCTIONID
    and b.ITEMID =c.ITEMID
    and b.SKUID =d.SKUID
    and e.INDENTDETAILID =b.INDENTDETAILID
    and e.INDENTID =h.INDENTID
    and h.PROCESSID =f.PROCESSID
    and b.UOMID =g.UOMID
    and A.Workcenterid=I.WorkcenterId
    and ma.ALLOCATIONID =e.ALLOCATIONID
    and a.CREATEDBYID =u.USERID
    and md.DEVICEID = u.DEFAULTDEVICEID
    {ResourceReqFilter}
    {DynamicFilter}
    {SkuTypeFilter}";

        // ── Type=2: Machine-Wise Production Summary ─────────────────────────────────────────────────

        public const string MACHINE_WISE_PRODUCTION_SUMMARY_REPORT_SQL = @"
select
    mm.MACHINEID as Id,
    mm.MACHINECODE as Code,
    mm.MACHINENAME as Name,
    b.ITEMID as ItemId,
    c.ITEMCODE as ItemCode,
    c.ITEMNAME as ItemName,
    b.SKUID as SKUId,
    d.SKUCODE as SKUCode,
    d.SKUNAME as SKUName,
    b.UOMID as UOMId,
    g.UOMCODE as UOMCode,
    g.UOMNAME as UOMName,
    sum(case when a.TYPE =0 then  ISNULL( b.PRODUCTIONQUANTITY,0) else 0  END )as Produced ,
    sum(case when a.TYPE =1 then  ISNULL( b.PRODUCTIONQUANTITY ,0) else 0 END ) as Reprocess ,
    sum(b.GOODQUANTITY) as Accepeted,
    sum(b.REWORKQUANTITY )as Rework,
    sum(b.REJECTEDQUANTITY) as Rejected,
    sum(b.PROCESSCOST )as ProcessCost,
    sum(case when a.TYPE =0 then  ISNULL( b.PRODUCTIONQUANTITY,0) else 0  END )*sum(b.PROCESSCOST ) as ProcessValue,
    sum(b.MATERIALCOST) as MaterialCost
from TPRODUCTION a,
    TPRODUCTIONDETAIL b,
    MITEM c,
    MSKU d,
    TINDENTDETAIL e,
    TINDENT h,
    MPROCESS f,
    MUOM g,
    MMACHINE mm,
    TPRODUCTIONRESOURCE tpr
where 1=1
    and a.PRODUCTIONID =b.PRODUCTIONID
    and a.PRODUCTIONID =tpr.PRODUCTIONID
    and b.PRODUCTIONDETAILID =tpr.PRODUCTIONDETAILID
    and tpr.MACHINEID =mm.MACHINEID
    and b.ITEMID =c.ITEMID
    and b.SKUID =d.SKUID
    and e.INDENTDETAILID =b.INDENTDETAILID
    and e.INDENTID =h.INDENTID
    and h.PROCESSID =f.PROCESSID
    and b.UOMID =g.UOMID
    {DynamicFilter}
    {SkuTypeFilter}
group by b.ITEMID,c.ITEMCODE, c.ITEMNAME ,b.SKUID,d.SKUCODE ,d.SKUNAME,h.PROCESSID,f.PROCESSCODE,f.PROCESSNAME,b.UOMID,g.UOMCODE,g.UOMNAME,a.TYPE, mm.MACHINEID,mm.MACHINECODE,mm.MACHINENAME
having sum(case when a.TYPE =0 then  ISNULL( b.PRODUCTIONQUANTITY,0) else 0  END ) <>0
order by mm.MACHINECODE,c.ITEMCODE,d.SKUCODE";

        public const string MACHINE_WISE_PRODUCTION_SUMMARY_REPORT_COUNT_SQL = @"
select count(*)
from TPRODUCTION a,
    TPRODUCTIONDETAIL b,
    MITEM c,
    MSKU d,
    TINDENTDETAIL e,
    TINDENT h,
    MPROCESS f,
    MUOM g,
    MMACHINE mm,
    TPRODUCTIONRESOURCE tpr
where 1=1
    and a.PRODUCTIONID =b.PRODUCTIONID
    and a.PRODUCTIONID =tpr.PRODUCTIONID
    and b.PRODUCTIONDETAILID =tpr.PRODUCTIONDETAILID
    and tpr.MACHINEID =mm.MACHINEID
    and b.ITEMID =c.ITEMID
    and b.SKUID =d.SKUID
    and e.INDENTDETAILID =b.INDENTDETAILID
    and e.INDENTID =h.INDENTID
    and h.PROCESSID =f.PROCESSID
    and b.UOMID =g.UOMID
    {DynamicFilter}
    {SkuTypeFilter}";

        public const string MACHINE_WISE_PRODUCTION_SUMMARY_REPORT_TOTAL_SQL = @"
select
    sum(b.PROCESSCOST )as TotalProcessCost,
    sum(case when a.TYPE =0 then  ISNULL( b.PRODUCTIONQUANTITY,0) else 0  END )*sum(b.PROCESSCOST ) as TotalProcessValue,
    sum(b.MATERIALCOST) as TotalMaterialCost
from TPRODUCTION a,
    TPRODUCTIONDETAIL b,
    MITEM c,
    MSKU d,
    TINDENTDETAIL e,
    TINDENT h,
    MPROCESS f,
    MUOM g,
    MMACHINE mm,
    TPRODUCTIONRESOURCE tpr
where 1=1
    and a.PRODUCTIONID =b.PRODUCTIONID
    and a.PRODUCTIONID =tpr.PRODUCTIONID
    and b.PRODUCTIONDETAILID =tpr.PRODUCTIONDETAILID
    and tpr.MACHINEID =mm.MACHINEID
    and b.ITEMID =c.ITEMID
    and b.SKUID =d.SKUID
    and e.INDENTDETAILID =b.INDENTDETAILID
    and e.INDENTID =h.INDENTID
    and h.PROCESSID =f.PROCESSID
    and b.UOMID =g.UOMID
    {DynamicFilter}
    {SkuTypeFilter}";

        // ── Type=3: Production Register — Scale Reading (production UNION ALL stock transfer) ───────
        // GB4 never paginates this branch (always FirstNumber=-1, MaxResult=-1) — kept unpaginated here.

        public const string PRODUCTION_REGISTER_SCALE_READING_SQL = @"
select
   ma.ALLOCATIONNAME as AllocationName,
   h.INDENTID as Id,
   h.INDENTDATE as Date,
   h.INDENTNUMBER as Number,
   e.PLANNEDQUANTITY as PlannedQuantity,
   case when e.nestingplanid<>-1 then npitem.ITEMID else b.ITEMID end as ItemId,
   case when e.nestingplanid<>-1 then npitem.ITEMCODE  else c.ITEMCODE end as ItemCode,
   case when e.nestingplanid<>-1 then npitem.ITEMNAME  else c.ITEMNAME end as ItemName,
   case when e.nestingplanid<>-1 then npsku .SKUID  else b.SKUID  end as SKUId,
   case when e.nestingplanid<>-1 then npsku.SKUCODE   else d.SKUCODE end as SKUCode,
   case when e.nestingplanid<>-1 then npsku.SKUNAME   else d.SKUNAME end as SKUName,
   h.PROCESSID as ProcessId,
   f.PROCESSCODE as ProcessCode,
   f.PROCESSNAME as ProcessName,
   b.UOMID as UOMId,
   g.UOMCODE as UOMCode,
   g.UOMNAME as UOMName,
   case when a.TYPE =0 then
   case when e.nestingplanid<>-1 then packprod.PackQtyproduced else
   ( (ISNULL( b.PRODUCTIONQUANTITY,0)) - (ISNULL( b.EXCESSSHORTAGEQUANTITY,0))) end
   else 0  END as Produced ,
   b.OUTPUTWEIGHT as NetWeight,
   a.Remarks as Remarks,
   u.USERNAME as CreatedByName,
   CONVERT(date, a.CREATEDON) as CreatedonDate ,
   convert(char(5), a.CREATEDON, 108) as CreatedTime,
   md.devicename as DeviceName,
   -1 as StoreId,
   'NONE' as StoreCode,
   'NONE' as StoreName
from
    TPRODUCTION a, TPRODUCTIONDETAIL b
   left outer join  (select PRODUCTIONDETAILID,sum(proditem.packquantity) as PackQtyproduced,
   sum(proditem.GOODQUANTITY) as PackQtyaccepted from TPRODUCTIONITEMS  proditem group by PRODUCTIONDETAILID ) packprod
   on (packprod.PRODUCTIONdetailID=b.PRODUCTIONdetailID) ,
   MITEM c,
   MSKU d,
   TINDENTDETAIL e,
   TINDENT h,
   MPROCESS f,
   MUOM g ,
   MEMPLOYEE k,
   MWORKCENTER I,
   TNESTINGPLAN np,
   MITEM npitem,
   MSKU npsku,
   MALLOCATION ma,
   MUSER u,
   MDEVICE md,
   MBIZTRANSACTIONTYPE t
   where 1=1
   and a.PRODUCTIONID =b.PRODUCTIONID
   and b.ITEMID =c.ITEMID
   and b.SKUID =d.SKUID
   and e.INDENTDETAILID =b.INDENTDETAILID
   and e.INDENTID =h.INDENTID
   and h.PROCESSID =f.PROCESSID
   and b.UOMID =g.UOMID
   and k.EMPLOYEEID = a.inchargeid
   and A.Workcenterid=I.WorkcenterId
   and np.NESTINGPLANID =e.NESTINGPLANID
   and npitem.ITEMID =np.SHEETITEMID
   and npsku.SKUID =np.SHEETSKUID
   and ma.ALLOCATIONID =e.ALLOCATIONID
   and a.CREATEDBYID =u.USERID
   and md.DEVICEID = u.DEFAULTDEVICEID
   and a.BIZTRANSACTIONTYPEID =t.BIZTRANSACTIONTYPEID
   {DynamicFilter}";

        public const string PRODUCTION_REGISTER_SCALE_READING_STOCK_TRANSFER_SQL = @"
select
       d.ALLOCATIONNAME as AllocationName,
       A.documentid as Id,
       A.documentdate as Date,
       a.DOCUMENTNUMBER as Number,
       a.totalquantity as PlannedQuantity,
       b.ITEMID as ItemId,
       i.ITEMCODE as ItemCode,
       i.ITEMNAME as ItemName,
       b.SKUID as SKUId,
       s.SKUCODE as SKUCode,
       s.SKUNAME as SKUName,
       s.PROCESSID as ProcessId,
       f.PROCESSCODE as ProcessCode,
       f.PROCESSNAME as ProcessName,
       b.TRANSACTIONRATEUOMID as UOMId,
       u.UOMCODE as UOMCode,
       u.UOMNAME as UOMName,
       b.TRANSACTIONACTUALQUANTITY as Produced,
       b.ITEMNETWEIGHT as NetWeight,
       a.REMARKS as Remarks,
       us.USERNAME as CreatedByName,
       a.CREATEDON as CreatedOnDate,
       convert(char(5), a.CREATEDON, 108) as CreatedTime,
       md.DEVICENAME as DeviceName,
       a.TOSTOREID as StoreId,
       ms.storecode as StoreCode,
       ms.storename as StoreName
   from
       TMMHEAD a,TMMDETAIL b,MBIZTRANSACTIONTYPE c,MALLOCATION d,
       MITEM i,MSKU s,MPROCESS f,MUOM u,MUSER us,MDEVICE md,mstore ms
   where 1=1
       and a.DOCUMENTID =b.DOCUMENTID
       and a.BIZTRANSACTIONTYPEID =c.BIZTRANSACTIONTYPEID
       and a.ALLOCATIONID =d.ALLOCATIONID
       and b.ITEMID =i.ITEMID
       and b.SKUID =s.SKUID
       and s.PROCESSID =f.PROCESSID
       and b.TRANSACTIONRATEUOMID =u.UOMID
       and a.CREATEDBYID =us.USERID
       and us.DEFAULTDEVICEID =md.DEVICEID
      and a.TOSTOREID=ms.storeid
       and c.BIZTRANSACTIONCLASSID =-1799999913
      and d.ALLOCATIONID<>-1
      {DynamicFilter}";
    }
}