private void LoadFromBase string _barcode очищаем все данные списке эл

  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
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
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();
}