אידישע וועלט פארומס

קאוד: וועל אויס אלע

    inner join  postsoitem on soitem.id = postsoitem.soitemid 

קאוד: וועל אויס אלע

 SELECT postso.postdate as DateFulfilled, so.num as SONUM,
-- postso.extrefnumber as Invoice,
sum(case  soitem.typeid when 70 then 0 when 30 then postsoitem.totalprice else(postsoitem.qty*soitem.unitprice) end) as TotalPrice,
bta.info as BlueTarpAuthNumber,
coalesce(case when gggparent.name = 'BlueTarp' then null else gggparent.name end, case when ggparent.name = 'BlueTarp' then null else ggparent.name end, case when gparent.name = 'BlueTarp' then null else gparent.name end, case when parent.name = 'BlueTarp' then null else parent.name end, customer.name) as "Acct Name",
iif(gggparent.name is not null, gggparent.name || ':','') || iif(ggparent.name is not null,ggparent.name || ':','') || iif(gparent.name is not null, gparent.name || ':' ,'') || iif(parent.name is not null,parent.name || ':','') || customer.name as CustomerCombined

FROM soitem
--    inner JOIN so ON postso.soid = so.id
    INNER JOIN so ON soitem.soid = so.id
    inner JOIN postsoitem ON soitem.id = postsoitem.soitemid
    inner JOIN postso ON (postsoitem.postsoid = postso.id )
    left join locationgroup on so.locationgroupid = locationgroup.id
    LEFT JOIN taxrate on so.TAXRATEID = taxrate.id
    INNER JOIN customer ON so.customerid = customer.id
    left join product on soitem.productid= product.id
    left join qbclass on soitem.qbclassid = qbclass.id
    left join stateconst shiptostate on (shiptostate.id = so.shiptostateid)
    left join stateconst billtostate on (billtostate.id = so.billtostateid)
    left join uom on soitem.uomid = uom.id
    left join part on product.partid = part.id
    left join soitemtype on soitem.typeid = soitemtype.id
    left join vendorparts vp on vp.partid = part.id and vp.defaultflag = 1
    left join vendor on vendor.id = vp.vendorid
--  left join asaccount incomeaccount ON incomeaccount.id = COALESCE(product.INCOMEACCOUNTID, (select sysvalue from sysproperties where syskey like 'Acct ID salesRetail') )
--  left join asaccount cogsaccount ON cogsaccount.id = COALESCE(part.cogsACCOUNTID, (select sysvalue from sysproperties where syskey like 'Acct ID cogsAccount') )
--  left join asaccount assetaccount ON assetaccount.id = COALESCE(part.inventoryACCOUNTID, (select sysvalue from sysproperties where syskey like 'Acct ID inventoryAsset') )
    left join customset bterms on bterms.customfieldid = 54 and bterms.recordid = so.id
    left join customvarchar bta on bta.customfieldid = 53 and bta.recordid = so.id
    left join customset ab on ab.customfieldid = 55 and ab.recordid = so.id
    left join customer parent on parent.id = customer.parentid
    left join customer gparent on gparent.id = parent.parentid
    left join customer ggparent on ggparent.id = gparent.parentid
    left join customer gggparent on gggparent.id = ggparent.parentid

 where (postsoitem.id IS NOT NULL OR (soitem.typeid = 70 AND soitem.statusid = 50))
 and cast(postso.postdate as  date) between ?
and ? +.9999

group by
--postsoitem.extrefnumber,
so.num, postso.postdate,
bta.info,
coalesce(case when gggparent.name = 'BlueTarp' then null else gggparent.name end, case when ggparent.name = 'BlueTarp' then null else ggparent.name end, case when gparent.name = 'BlueTarp' then null else gparent.name end, case when parent.name = 'BlueTarp' then null else parent.name end, customer.name),
iif(gggparent.name is not null, gggparent.name || ':','') || iif(ggparent.name is not null,ggparent.name || ':','') || iif(gparent.name is not null, gparent.name || ':' ,'') || iif(parent.name is not null,parent.name || ':','') || customer.name
order by postso.postdate 

קאוד: וועל אויס אלע

 SELECT POSTSO.ID, POSTSO.SOID, POSTSO.POSTDATE, POSTSO.STATUSID, POSTSO.EXTTXNID, POSTSO.EXTTXNHASH, POSTSO.EXTTXNNUMBER, POSTSO.EXTREFNUMBER, POSTSO.DATEPOSTED, POSTSO.DATECREATED, POSTSO.DATELASTMODIFIED, POSTSO.JOURNALTXNID, POSTSO.JOURNALPOSTED
FROM POSTSO POSTSO
WHERE (POSTSO.DATECREATED>'2018-05-25') 

קאוד: וועל אויס אלע

 SELECT postso.postdate as DateFulfilled, so.num as SONUM,
-- postso.extrefnumber as Invoice,
sum(case  soitem.typeid when 70 then 0 when 30 then postsoitem.totalprice else(postsoitem.qty*soitem.unitprice) end) as TotalPrice,
bta.info as BlueTarpAuthNumber,
coalesce(case when gggparent.name = 'BlueTarp' then null else gggparent.name end, case when ggparent.name = 'BlueTarp' then null else ggparent.name end, case when gparent.name = 'BlueTarp' then null else gparent.name end, case when parent.name = 'BlueTarp' then null else parent.name end, customer.name) as "Acct Name",
iif(gggparent.name is not null, gggparent.name || ':','') || iif(ggparent.name is not null,ggparent.name || ':','') || iif(gparent.name is not null, gparent.name || ':' ,'') || iif(parent.name is not null,parent.name || ':','') || customer.name as CustomerCombined

FROM soitem
--    inner JOIN so ON postso.soid = so.id
    INNER JOIN so ON soitem.soid = so.id
    inner JOIN postsoitem ON soitem.id = postsoitem.soitemid
    inner JOIN postso ON (postsoitem.postsoid = postso.id )
    left join locationgroup on so.locationgroupid = locationgroup.id
    LEFT JOIN taxrate on so.TAXRATEID = taxrate.id
    INNER JOIN customer ON so.customerid = customer.id
    left join product on soitem.productid= product.id
    left join qbclass on soitem.qbclassid = qbclass.id
    left join stateconst shiptostate on (shiptostate.id = so.shiptostateid)
    left join stateconst billtostate on (billtostate.id = so.billtostateid)
    left join uom on soitem.uomid = uom.id
    left join part on product.partid = part.id
    left join soitemtype on soitem.typeid = soitemtype.id
    left join vendorparts vp on vp.partid = part.id and vp.defaultflag = 1
    left join vendor on vendor.id = vp.vendorid
--  left join asaccount incomeaccount ON incomeaccount.id = COALESCE(product.INCOMEACCOUNTID, (select sysvalue from sysproperties where syskey like 'Acct ID salesRetail') )
--  left join asaccount cogsaccount ON cogsaccount.id = COALESCE(part.cogsACCOUNTID, (select sysvalue from sysproperties where syskey like 'Acct ID cogsAccount') )
--  left join asaccount assetaccount ON assetaccount.id = COALESCE(part.inventoryACCOUNTID, (select sysvalue from sysproperties where syskey like 'Acct ID inventoryAsset') )
    left join customset bterms on bterms.customfieldid = 54 and bterms.recordid = so.id
    left join customvarchar bta on bta.customfieldid = 53 and bta.recordid = so.id
    left join customset ab on ab.customfieldid = 55 and ab.recordid = so.id
    left join customer parent on parent.id = customer.parentid
    left join customer gparent on gparent.id = parent.parentid
    left join customer ggparent on ggparent.id = gparent.parentid
    left join customer gggparent on gggparent.id = ggparent.parentid

 where (postsoitem.id IS NOT NULL OR (soitem.typeid = 70 AND soitem.statusid = 50))
 and cast(postso.postdate as  date) between ?
and ? +.9999

group by
--postsoitem.extrefnumber,
so.num, postso.postdate,
bta.info,
coalesce(case when gggparent.name = 'BlueTarp' then null else gggparent.name end, case when ggparent.name = 'BlueTarp' then null else ggparent.name end, case when gparent.name = 'BlueTarp' then null else gparent.name end, case when parent.name = 'BlueTarp' then null else parent.name end, customer.name),
iif(gggparent.name is not null, gggparent.name || ':','') || iif(ggparent.name is not null,ggparent.name || ':','') || iif(gparent.name is not null, gparent.name || ':' ,'') || iif(parent.name is not null,parent.name || ':','') || customer.name
order by postso.postdate 

קאוד: וועל אויס אלע

 iif(gggparent.name is not null, gggparent.name || ':','') || iif(ggparent.name is not null,ggparent.name || ':','') || iif(gparent.name is not null, gparent.name || ':' ,'') || iif(parent.name is not null,parent.name || ':','') || customer.name as "Customer Path" 

קאוד: וועל אויס אלע

 select main.[Customer Path] from
(select iif(gggparent.name is not null, gggparent.name || ':','') || iif(ggparent.name is not null,ggparent.name || ':','') || iif(gparent.name is not null, gparent.name || ':' ,'') || iif(parent.name is not null,parent.name || ':','') || customer.name as "Customer Path"
from blabla)main 

קאוד: וועל אויס אלע

 SELECT POSTSO.EXTREFNUMBER as Invoice, SO.NUM as "SO Num", replace(replace(POSTORDERSTATUS.NAME,'For Invoice','Pending'),'Invoice','Posted') as Status, sum(POSTSOITEM.TOTALPRICE) as Amount, cast(postso.datecreated as date) as Fulfilled,
case when iif(gggparent.name is not null, gggparent.name || ':','') || iif(ggparent.name is not null,ggparent.name || ':','') || iif(gparent.name is not null, gparent.name || ':' ,'') || iif(parent.name is not null,parent.name || ':','') || customer.name like 'BlueTarp%' then 'BlueTarp' end as BlueTarp,
bta.info as BTAuth,
coalesce(case when gggparent.name = 'BlueTarp' then null else gggparent.name end, case when ggparent.name = 'BlueTarp' then null else ggparent.name end, case when gparent.name = 'BlueTarp' then null else gparent.name end, case when parent.name = 'BlueTarp' then null else parent.name end, customer.name) as "Account Name",
iif(gggparent.name is not null, gggparent.name || ':','') || iif(ggparent.name is not null,ggparent.name || ':','') || iif(gparent.name is not null, gparent.name || ':' ,'') || iif(parent.name is not null,parent.name || ':','') || customer.name as "Customer Path"

FROM POSTORDERSTATUS POSTORDERSTATUS, POSTSO POSTSO, POSTSOITEM POSTSOITEM, SO SO
    left join customvarchar bta on bta.customfieldid = 53 and bta.recordid = so.id
    INNER JOIN customer ON so.customerid = customer.id
    left join customer parent on parent.id = customer.parentid
    left join customer gparent on gparent.id = parent.parentid
    left join customer ggparent on ggparent.id = gparent.parentid
    left join customer gggparent on gggparent.id = ggparent.parentid

WHERE SO.ID = POSTSO.SOID AND POSTSO.STATUSID = POSTORDERSTATUS.ID AND POSTSOITEM.POSTSOID = POSTSO.ID
and (cast(postso.dateposted as  date) between ? and ? +.9999 OR postso.dateposted is null)

Group By POSTSO.EXTREFNUMBER, SO.NUM, POSTORDERSTATUS.NAME, postso.datecreated,
coalesce(case when gggparent.name = 'BlueTarp' then null else gggparent.name end, case when ggparent.name = 'BlueTarp' then null else ggparent.name end, case when gparent.name = 'BlueTarp' then null else gparent.name end, case when parent.name = 'BlueTarp' then null else parent.name end, customer.name),
bta.info,
iif(gggparent.name is not null, gggparent.name || ':','') || iif(ggparent.name is not null,ggparent.name || ':','') || iif(gparent.name is not null, gparent.name || ':' ,'') || iif(parent.name is not null,parent.name || ':','') || customer.name

Order By POSTORDERSTATUS.NAME