private void LoadFromBase(string _barcode)
{
//очищаем все данные в списке элементов заказа
table_rotoxhouse.Clear();
table_rotoxhouse.AcceptChanges();
//состояния (state) ячейки : 0 - резерв, 1 - приход, 2 - комплектация, 3 - отгрузка
#region старый код
// 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
// 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 o.name {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
// 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 o.name {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
// 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 og.deleted IS NULL
// and o.name {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,
// 0 AS orderitem_numpos,
// '' orderitem_name,
// '' orderitem_part,
// '' good_marking,
// '' good_name,
// o.destanation_name, o.customer_name, o.seller_name, o.dtdoc order_dtdoc, o.name order_name
// from rotoxhouse h
// JOIN view_orders o ON o.idorder = h.idorder
// where h.state < 3
// and o.name {0}",
// cbSearchEquals.Checked ? " = '" + textEdit1.Text + "'" : " like '%" + textEdit1.Text + @"%' ");
// cbSearchByAgreeName
#endregion
string barcode = _barcode;
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 + @"%' ");
db.adapter.Fill(table_rotoxhouse);
//проставляем "выбор" для тех позиций, которые в состоянии "комплектация"
foreach (DataRow dr in table_rotoxhouse.Select("state = 2"))
dr["s_"] = true;
table_rotoxhouse.AcceptChanges();
}