namespace MMDAL.Query.Packing { public static class PackingQB { public const string GET_SELECTLIST_PACKING_SQL = @" WITH Paged AS ( SELECT DISTINCT packing0_.PACKINGID AS PackingPicklistId, packing0_.PACKINGNUMBER AS PackingPicklistPackingNumber, packing0_.PACKINGDATE AS PackingPicklistPackingDate, packing0_.REFERENCENUMBER AS PackingPicklistReferenceNumber, packing0_.REFERENCEDATE AS PackingPicklistReferenceDate, user1_.USERNAME AS ModifiedByName, ISNULL(pdtl.DOCUMENTNUMBER, '') AS DocumentNumber, ISNULL(pdtl.PARTYCODE, '') AS PartyCode, ISNULL(pdtl.PARTYNAME, '') AS PartyName, ROW_NUMBER() OVER (ORDER BY packing0_.PACKINGID DESC) AS RowNum FROM TPACKING packing0_ OUTER APPLY ( SELECT TOP(1) c.DOCUMENTNUMBER, d.PARTYID, d.PARTYCODE, d.PARTYNAME FROM TPACKDETAIL pdtl INNER JOIN TMMDETAIL b ON pdtl.DOCUMENTDETAILID = b.DOCUMENTDETAILID INNER JOIN TMMHEAD c ON b.DOCUMENTID = c.DOCUMENTID INNER JOIN MPARTY d ON c.PARTYID = d.PARTYID WHERE pdtl.OBJECTHEADERTYPEID = -1399999916 AND pdtl.OBJECTHEADERID = packing0_.PACKINGID ) pdtl INNER JOIN MUSER user1_ ON packing0_.MODIFIEDBYID = user1_.USERID INNER JOIN TPACKNUMBER packnumber2_ ON packing0_.PACKNUMBERID = packnumber2_.PACKNUMBERID INNER JOIN MBIZTRANSACTIONTYPE biztype ON packing0_.BIZTRANSACTIONTYPEID = biztype.BIZTRANSACTIONTYPEID ) SELECT PackingPicklistId, PackingPicklistPackingNumber, PackingPicklistPackingDate, PackingPicklistReferenceNumber, PackingPicklistReferenceDate, ModifiedByName, DocumentNumber, PartyCode, PartyName FROM Paged WHERE (@firstnumber = -1 AND @maxresult = -1) OR (RowNum BETWEEN @firstnumber AND @maxresult); "; public const string GET_SELECTLIST_PACKING_PG = @" WITH Paged AS ( SELECT p.PACKINGID AS ""PackingPicklistId"", p.PACKINGNUMBER AS ""PackingPicklistPackingNumber"", p.PACKINGDATE AS ""PackingPicklistPackingDate"", p.REFERENCENUMBER AS ""PackingPicklistReferenceNumber"", p.REFERENCEDATE AS ""PackingPicklistReferenceDate"", NULL::text AS ""ModifiedByName"", NULL::text AS ""DocumentNumber"", NULL::text AS ""PartyCode"", NULL::text AS ""PartyName"", ROW_NUMBER() OVER (ORDER BY p.PACKINGID DESC) AS ""RowNum"" FROM TPACKING p ) SELECT ""PackingPicklistId"", ""PackingPicklistPackingNumber"", ""PackingPicklistPackingDate"", ""PackingPicklistReferenceNumber"", ""PackingPicklistReferenceDate"", ""ModifiedByName"", ""DocumentNumber"", ""PartyCode"", ""PartyName"" FROM Paged WHERE (:firstnumber = -1 AND :maxresult = -1) OR (""RowNum"" BETWEEN :firstnumber AND :maxresult); "; public const string GET_SELECTLIST_PACKING_ORACLE = @";"; public const string GET_SELECTLIST_PACKING_MYSQL = @";"; // GB4 parity: mms/Packing.svc/PackingProductionSummary — // PackingBLL.GetPackingProductionSummary -> PackingDAL.GetPackingProductionSummary // -> PackingQueryBuilder.GET_PACKING_PRODUCTION_SUMMARY. // Item/SKU/pack-type grouped aggregate of packed quantity, gross/net weight over // TPACKING. Filters (all optional, -1/NULL = not applied): ItemId, SKUId, OUId, // PeriodFromDate/PeriodToDate (packing date range), BIZTransactionClassId, // BIZTransactionSubClassId, BIZTransactionId, BIZTransactionTypeId, PackType. // Required index: TPACKING(ITEMID, SKUID, OUID, BIZTRANSACTIONTYPEID, PACKINGDATE, PACKTYPE) public const string GET_PACKING_PRODUCTION_SUMMARY_SQL = @" WITH Paged AS ( SELECT packing.ITEMID AS ItemId, item.ITEMCODE AS ItemCode, item.ITEMNAME AS ItemName, packing.SKUID AS SKUId, sku.SKUCODE AS SKUCode, sku.SKUNAME AS SKUName, mgroup.ITEMGROUPCODE AS ItemGroupCode, mgroup.ITEMGROUPNAME AS ItemGroupName, SUM(packing.NUMBEROFPACKS) AS NumberOfPacks, SUM(packing.TOTALGROSSWEIGHT) AS GrossWeight, SUM(packing.TOTALNETWEIGHT) AS NetWeight, CASE WHEN packing.PACKTYPE = 0 THEN 'Single Item' ELSE 'Assorted Item' END AS PackType, ROW_NUMBER() OVER (ORDER BY item.ITEMCODE, packing.SKUID) AS RowNum FROM TPACKING packing JOIN MITEM item ON packing.ITEMID = item.ITEMID JOIN MSKU sku ON packing.SKUID = sku.SKUID JOIN MBIZTRANSACTIONTYPE biztrans ON packing.BIZTRANSACTIONTYPEID = biztrans.BIZTRANSACTIONTYPEID JOIN MITEMGROUP mgroup ON mgroup.ITEMGROUPID = item.ITEMGROUPID WHERE 1 = 1 AND (@itemid = -1 OR packing.ITEMID = @itemid) AND (@skuid = -1 OR packing.SKUID = @skuid) AND (@ouid = -1 OR packing.OUID = @ouid) AND (@periodfromdate IS NULL OR packing.PACKINGDATE >= @periodfromdate) AND (@periodtodate IS NULL OR packing.PACKINGDATE <= @periodtodate) AND (@biztransactionclassid = -1 OR biztrans.BIZTRANSACTIONCLASSID = @biztransactionclassid) AND (@biztransactionsubclassid = -1 OR biztrans.BIZTRANSACTIONSUBCLASSID = @biztransactionsubclassid) AND (@biztransactionid = -1 OR biztrans.BIZTRANSACTIONID = @biztransactionid) AND (@biztransactiontypeid = -1 OR biztrans.BIZTRANSACTIONTYPEID = @biztransactiontypeid) AND (@packtype = -1 OR packing.PACKTYPE = @packtype) GROUP BY packing.ITEMID, item.ITEMCODE, item.ITEMNAME, packing.SKUID, sku.SKUCODE, sku.SKUNAME, mgroup.ITEMGROUPCODE, mgroup.ITEMGROUPNAME, CASE WHEN packing.PACKTYPE = 0 THEN 'Single Item' ELSE 'Assorted Item' END ) SELECT ItemId, ItemCode, ItemName, SKUId, SKUCode, SKUName, ItemGroupCode, ItemGroupName, NumberOfPacks, GrossWeight, NetWeight, PackType FROM Paged WHERE (@firstnumber = -1 AND @maxresult = -1) OR (RowNum BETWEEN @firstnumber AND @maxresult);"; public const string GET_PACKING_PRODUCTION_SUMMARY_PG = @" WITH Paged AS ( SELECT packing.""ITEMID"" AS ""ItemId"", item.""ITEMCODE"" AS ""ItemCode"", item.""ITEMNAME"" AS ""ItemName"", packing.""SKUID"" AS ""SKUId"", sku.""SKUCODE"" AS ""SKUCode"", sku.""SKUNAME"" AS ""SKUName"", mgroup.""ITEMGROUPCODE"" AS ""ItemGroupCode"", mgroup.""ITEMGROUPNAME"" AS ""ItemGroupName"", SUM(packing.""NUMBEROFPACKS"") AS ""NumberOfPacks"", SUM(packing.""TOTALGROSSWEIGHT"") AS ""GrossWeight"", SUM(packing.""TOTALNETWEIGHT"") AS ""NetWeight"", CASE WHEN packing.""PACKTYPE"" = 0 THEN 'Single Item' ELSE 'Assorted Item' END AS ""PackType"", ROW_NUMBER() OVER (ORDER BY item.""ITEMCODE"", packing.""SKUID"") AS ""RowNum"" FROM ""TPACKING"" packing JOIN ""MITEM"" item ON packing.""ITEMID"" = item.""ITEMID"" JOIN ""MSKU"" sku ON packing.""SKUID"" = sku.""SKUID"" JOIN ""MBIZTRANSACTIONTYPE"" biztrans ON packing.""BIZTRANSACTIONTYPEID"" = biztrans.""BIZTRANSACTIONTYPEID"" JOIN ""MITEMGROUP"" mgroup ON mgroup.""ITEMGROUPID"" = item.""ITEMGROUPID"" WHERE 1 = 1 AND (:itemid = -1 OR packing.""ITEMID"" = :itemid) AND (:skuid = -1 OR packing.""SKUID"" = :skuid) AND (:ouid = -1 OR packing.""OUID"" = :ouid) AND (:periodfromdate IS NULL OR packing.""PACKINGDATE"" >= :periodfromdate) AND (:periodtodate IS NULL OR packing.""PACKINGDATE"" <= :periodtodate) AND (:biztransactionclassid = -1 OR biztrans.""BIZTRANSACTIONCLASSID"" = :biztransactionclassid) AND (:biztransactionsubclassid = -1 OR biztrans.""BIZTRANSACTIONSUBCLASSID"" = :biztransactionsubclassid) AND (:biztransactionid = -1 OR biztrans.""BIZTRANSACTIONID"" = :biztransactionid) AND (:biztransactiontypeid = -1 OR biztrans.""BIZTRANSACTIONTYPEID"" = :biztransactiontypeid) AND (:packtype = -1 OR packing.""PACKTYPE"" = :packtype) GROUP BY packing.""ITEMID"", item.""ITEMCODE"", item.""ITEMNAME"", packing.""SKUID"", sku.""SKUCODE"", sku.""SKUNAME"", mgroup.""ITEMGROUPCODE"", mgroup.""ITEMGROUPNAME"", CASE WHEN packing.""PACKTYPE"" = 0 THEN 'Single Item' ELSE 'Assorted Item' END ) SELECT ""ItemId"", ""ItemCode"", ""ItemName"", ""SKUId"", ""SKUCode"", ""SKUName"", ""ItemGroupCode"", ""ItemGroupName"", ""NumberOfPacks"", ""GrossWeight"", ""NetWeight"", ""PackType"" FROM Paged WHERE (:firstnumber = -1 AND :maxresult = -1) OR (""RowNum"" BETWEEN :firstnumber AND :maxresult);"; public const string GET_PACKING_PRODUCTION_SUMMARY_ORACLE = @";"; public const string GET_PACKING_PRODUCTION_SUMMARY_MYSQL = @";"; } }