db.command.CommandText = String.Format(@"select h.* ,
case when state=0 then 'Резерв'
when state=1 then 'Приход'
when state=2 then 'Комплектация'
when state=3 then 'Отгружено'
end state_name,
oi.numpos orderitem_numpos,
oi.name orderitem_name,
oi.part orderitem_part,
oi.good_marking,
oi.good_name,
o.destanation_name,
o.customer_name,
o.seller_name,
o.dtdoc order_dtdoc,
o.name order_name,
o.agreename order_agreename
from rotoxhouse h
JOIN view_orderitem oi ON oi.idorderitem=h.idorderitem
LEFT JOIN good g ON g.idgood = oi.idgood
JOIN view_orders o ON o.idorder = oi.idorder
where h.state < 3
AND oi.deleted IS NULL
AND g.deleted IS NULL
AND h.deleted IS NULL
and h.idordergood is null and h.idmodelcalc is null
{0}
union all
select h.* ,
case when state=0 then 'Резерв'
when state=1 then 'Приход'
when state=2 then 'Комплектация'
when state=3 then 'Отгружено'
end state_name,
oi.numpos orderitem_numpos,
oi.name orderitem_name,
oi.part orderitem_part,
mc.good_marking, mc.good_name,
o.destanation_name,
o.customer_name,
o.seller_name,
o.dtdoc order_dtdoc,
o.name order_name,
o.agreename order_agreename
from rotoxhouse h
JOIN view_modelcalc mc ON mc.idmodelcalc=h.idmodelcalc
JOIN good g ON g.idgood = mc.idgood
JOIN orderitem oi ON oi.idorderitem=mc.idorderitem
JOIN view_orders o ON o.idorder = oi.idorder
where h.state < 3
AND oi.deleted IS NULL
AND g.deleted IS NULL
AND mc.deleted IS NULL
AND h.deleted IS NULL
and h.idmodelcalc is not null
{0}
union all
select h.* ,
case when state=0 then 'Резерв'
when state=1 then 'Приход'
when state=2 then 'Комплектация'
when state=3 then 'Отгружено'
end state_name,
oi.numpos orderitem_numpos,
oi.name orderitem_name,
oi.part orderitem_part,
og.good_marking,
og.good_name,
o.destanation_name,
o.customer_name,
o.seller_name,
o.dtdoc order_dtdoc,
o.name order_name,
o.agreename order_agreename
from rotoxhouse h
JOIN view_ordergood og ON og.idordergood=h.idordergood
JOIN good g ON g.idgood = og.idgood
JOIN orderitem oi ON oi.idorderitem=og.idorderitem
JOIN view_orders o ON o.idorder = oi.idorder
where h.state < 3
AND oi.deleted IS NULL
AND g.deleted IS NULL
AND h.deleted IS NULL
AND og.deleted IS NULL
and h.idordergood is not null
{0} ",
cbSearchByAgreeName.Checked ?
cbSearchEquals.Checked ? " and o.agreename = '" + barcode + "'" : " and o.agreename like '%" + barcode + @"%' " :
cbSearchEquals.Checked ? " and o.name = '" + barcode + "'" : " and o.name like '%" + barcode + @"%' ");