db command CommandText String Format select case when state then Резер

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
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 + @"%' ");