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.
212.
213.
214.
215.
216.
217.
218.
219.
220.
221.
222.
223.
224.
225.
226.
227.
228.
229.
230.
231.
232.
233.
234.
235.
236.
237.
238.
239.
240.
241.
242.
243.
244.
245.
246.
247.
248.
249.
250.
251.
252.
253.
254.
255.
256.
257.
258.
259.
260.
261.
262.
263.
264.
265.
266.
267.
268.
269.
270.
271.
272.
273.
274.
275.
276.
277.
278.
279.
280.
281.
282.
283.
284.
285.
286.
287.
288.
289.
290.
291.
292.
293.
294.
295.
296.
297.
298.
299.
300.
301.
302.
303.
304.
305.
306.
307.
308.
309.
310.
311.
312.
313.
314.
315.
316.
317.
318.
319.
320.
321.
322.
323.
324.
325.
326.
327.
328.
329.
330.
331.
332.
333.
334.
335.
336.
337.
338.
339.
340.
341.
342.
343.
344.
345.
346.
347.
348.
349.
350.
351.
352.
353.
354.
355.
356.
357.
358.
359.
360.
361.
362.
363.
364.
365.
366.
367.
368.
369.
370.
371.
372.
373.
374.
375.
376.
377.
378.
379.
380.
381.
382.
383.
384.
385.
386.
387.
388.
389.
390.
391.
392.
393.
394.
395.
396.
397.
398.
399.
400.
401.
402.
403.
404.
405.
406.
407.
408.
409.
410.
411.
412.
413.
414.
415.
416.
417.
418.
419.
420.
421.
422.
423.
424.
425.
426.
427.
428.
429.
430.
431.
432.
433.
434.
435.
436.
437.
438.
439.
440.
441.
442.
443.
444.
445.
446.
447.
448.
449.
450.
451.
452.
453.
454.
455.
456.
457.
458.
459.
460.
461.
462.
463.
464.
465.
466.
467.
468.
469.
470.
471.
472.
473.
474.
475.
476.
477.
478.
479.
480.
481.
482.
483.
484.
485.
486.
487.
488.
489.
490.
491.
492.
493.
494.
495.
496.
497.
498.
499.
500.
501.
502.
503.
504.
505.
506.
507.
508.
509.
510.
511.
512.
513.
514.
515.
516.
517.
518.
519.
520.
521.
522.
523.
524.
525.
526.
527.
528.
529.
530.
531.
532.
533.
534.
535.
536.
537.
538.
539.
540.
541.
542.
543.
544.
545.
546.
547.
548.
549.
550.
551.
552.
553.
554.
555.
556.
557.
558.
559.
560.
561.
562.
563.
564.
565.
566.
567.
568.
569.
570.
571.
572.
573.
574.
575.
576.
577.
578.
579.
580.
581.
582.
583.
584.
585.
586.
587.
588.
589.
590.
591.
592.
593.
594.
<script runat="server">
void Page_Load(Object sender, EventArgs e)
{
byte mmm1;//я писал здесь и int.
mmm1=8;
string d1=Page.Request.QueryString["d1"];
string d2;string d3;
//d3=""; - если убрать комментарий не ругается, пустое значение не изменяется
d2=Page.Request.QueryString["d2"];
int result = Convert.ToInt32(d2)+1; string d2n=Convert.ToString(result);
result = Convert.ToInt32(d2)-1; string d2n1=Convert.ToString(result);
switch (mmm1)
{
case 1:
d1="'"+d2+"0101' and "+"'"+d2+"0201'";
d3="'"+d2n1+"1001' and "+"'"+d2+"0101'";
break;
case 2:
d1="'"+d2+"0201' and "+"'"+d2+"0301'";
d3="'"+d2+"0101' and "+"'"+d2+"0201'";
break;
case 3:
d1="'"+d2+"0301' and "+"'"+d2+"0401'";
d3="'"+d2+"0201' and "+"'"+d2+"0301'";
break;
case 4:
d1="'"+d2+"0401' and "+"'"+d2+"0501'";
d3="'"+d2+"0301' and "+"'"+d2+"0401'";
break;
case 5:
d1="'"+d2+"0501' and "+"'"+d2+"0601'";
d3="'"+d2+"0401' and "+"'"+d2+"0501'";
break;
case 6:
d1="'"+d2+"0601' and "+"'"+d2+"0701'";
d3="'"+d2+"0501' and "+"'"+d2+"0601'";
break;
case 7:
d1="'"+d2+"0701' and "+"'"+d2+"0801'";
d3="'"+d2+"0601' and "+"'"+d2+"0701'";
break;
case 8:
d1="'"+d2+"0801' and "+"'"+d2+"0901'";
d3="'"+d2+"0701' and "+"'"+d2+"0801'";
break;
case 9:
d1="'"+d2+"0901' and "+"'"+d2+"1001'";
d3="'"+d2+"0801' and "+"'"+d2+"0901'";
break;
case 10:
d1="'"+d2+"1001' and "+"'"+d2+"1101'";
d3="'"+d2+"0901' and "+"'"+d2+"1001'";
break;
case 11:
d1="'"+d2+"1101' and "+"'"+d2+"1201'";
d3="'"+d2+"1001' and "+"'"+d2+"1101'";
break;
case 12:
d1="'"+d2+"1201 and "+d2n+"0101";
d3="'"+d2+"1101' and "+"'"+d2+"1201'";
break;
}
int BIDN;
BIDN = 0;
BIDN = Convert.ToInt32((Page.Request.QueryString["B1DN"]));
int B2DN;
B2DN = 0;
B2DN = Convert.ToInt32((Page.Request.QueryString["B2DN"]));
//int OdbcDbType = Convert.ToInt32(Request.Form["BID"]);
OdbcConnection conn = new OdbcConnection();
conn.ConnectionString =
//"DRIVER={MySQL ODBC 5.1 Driver};"+
//"DRIVER={MySQL ODBC 3.51 Driver};"+
//"DRIVER={MySQL ODBC SQL Server Driver};"+
"DRIVER={SQL Server};"+
@"Server=SPRAVKA\SRVSQLNKC;" +
@"Database=db_nkc;" +
"Trusted_Connection=Yes;"+
"Integrated Security=SSPI;"
//"user=;" +
//"pass=;"
//USE db_Matrix select dbo.tblMatrix
;
//conn.Open();
// OdbcCommand command = new OdbcCommand();
using (SqlConnection connection = new SqlConnection(
@"Server=SPRAVKA\SRVSQLNKC;" +
@"Database=db_nkc;" +
"Trusted_Connection=Yes;"+
"context connection=false;"+
// "Connection Timeout=1200;"+
"Integrated Security=SSPI;")) {
//if (B2DN==1){
connection.Open();
//string queryString ="SELECT vwpartn_1.PT_SHORTNAME as 'Магазины',[zakaz_date] as 'Дата создания',dbn.[zakaz_id] as 'Номер заказа',max(isnull(CHG_DI.paramval,'')) as 'ФИО 1 продавца',max(isnull(CHG_DI_1.paramval,'')) as 'ФИО 2 продавца',SUM([qty]) as 'Кол-во заказанных экземпляров',SUM([qty_case]) as 'Кол-во доставленных экземпляров',SUM([qty_shop]) as 'Кол-во выкупленных экземпляров',dbstat.Status_Name as 'Статус заказа',SUM([zakaz_summa]) as 'Сумма заказа',dbtype.[type_name] as 'Тип заказа',vwpartn.PT_SHORTNAME as 'Магазин доставки',comment FROM b_user_group as dbc, [B_Zakaz_CHG] as dbn LEFT JOIN b_users dbu on dbn.user_id=dbu.ID LEFT JOIN [B_Zakaz_CHG_Content] dbcont on dbn.zakaz_id=dbcont.zakaz_id LEFT JOIN [B_Zakaz_Status] dbstat on dbstat.Status_ID=dbn.current_state LEFT JOIN [B_Zakaz_Type] dbtype on dbtype.zakaz_type=dbn.zakaz_type LEFT JOIN [B_SHOPS_DI] dbshop on dbshop.delivery_id=dbn.delivery_id LEFT JOIN [B_Zakaz_CHG_DI] CHG_DI on param = 'ФИО Кассира' AND CHG_DI.zakaz_id=dbn.zakaz_id LEFT JOIN [B_Zakaz_CHG_DI] CHG_DI_1 on CHG_DI_1.param= 'ФИО ст.менеджера' AND CHG_DI_1.zakaz_id=dbn.zakaz_id LEFT JOIN vw_GetPartners vwpartn on vwpartn.PT_ID=dbshop.p_id LEFT JOIN vw_GetPartners vwpartn_1 on vwpartn_1.email=dbu.EMAIL WHERE dbn.[user_id] in (SELECT dbc.[USER_ID] FROM db_nkc.dbo.[b_user_group] WHERE dbc.GROUP_ID=19) AND dbn.zakaz_date between '"+d1+"' AND '"+d2+"' GROUP by dbu.EMAIL,[zakaz_date],dbn.[zakaz_id],dbstat.Status_Name,dbtype.[type_name],dbshop.p_id,comment,vwpartn.PT_SHORTNAME,vwpartn_1.PT_SHORTNAME order by dbn.[zakaz_id]";
string sql="select";
sql+=" sum(cnt_cur) 'всего заказов за месяц',";
sql+=" sum(sum_cur) 'сумма заказов',";
sql+=" sum(cnt_cur_sal) 'Всего заказов выкуплено за отчетный месяц',";
sql+=" sum(sum_cur_sal) 'Сумма выкупленных заказов',";
sql+=" sum(cnt_prev) 'Всего заказов (сделанных в предыдущем месяце) выкуплено в отчетном месяце',";
sql+=" sum(sum_prev) 'Сумма выкупленных заказов (сделанных в предыдущем месяце) ',";
sql+=" sum(cnt_cur_im) 'Всего заказов сделанных в ИМ за отчетный месяц',";
sql+=" sum(sum_cur_im) 'сумма заказов в ИМ',";
sql+=" sum(cnt_cur_im_sal) 'ИМ-заказов выкуплено за отчетный месяц',";
sql+=" sum(sum_cur_im_sal) 'Сумма выкупленных ИМ-заказов',";
sql+=" sum(cnt_prev_im_sal) 'Всего ИМ-заказов (сделанных в предыдущем месяце) выкуплено в отчетном месяце',";
sql+=" sum(sum_prev_im_sal) 'Сумма выкупленных ИМ-заказов',";
sql+=" sum(cnt_cur_rm) 'Всего заказов сделанных в ИМ через магазины за отчетный месяц',";
sql+=" sum(sum_cur_rm) 'Сумма заказов сделанных в ИМ ',";
sql+=" sum(cnt_cur_rm_sal) 'ИМ-заказов выкуплено за отчетный месяц',";
sql+=" sum(sum_cur_rm_sal) 'Сумма выкупленных ИМ-заказов ',";
sql+=" sum(cnt_prev_rm) 'Всего магазинных ИМ-заказов (сделанных в предыдущем месяце) выкуплено в отчетном месяце',";
sql+=" sum(sum_prev_rm) 'Сумма выкупленных ИМ-заказов (сделанных в предыдущем месяце) ', ";
sql+=" sum(cnt_cur_rez) 'Всего резервов сделано за отчетный месяц',";
sql+=" sum(sum_cur_rez) 'Сумма резервов',";
sql+=" sum(cnt_cur_sel_rez) 'Резервов выкуплено за отчетный месяц',";
sql+=" sum(sum_cur_sel_rez) 'Сумма выкупленных резервов ',";
sql+=" sum(cnt_prev_sel_rez) 'Всего резервов (сделанных в предыдущем месяце) выкуплено в отчетном месяце',";
sql+=" sum(sum_prev_sel_rez) 'Сумма выкупленных резервов '";
sql+="from (";
sql+=" select";
sql+=" count(dbn.zakaz_id) cnt_cur,";
sql+=" sum(dbn.zakaz_summa) sum_cur,";
sql+=" 0 cnt_prev,";
sql+=" 0 sum_prev, ";
sql+=" 0 cnt_cur_sal, ";
sql+=" 0 sum_cur_sal, ";
sql+=" 0 cnt_cur_im, ";
sql+=" 0 sum_cur_im, ";
sql+=" 0 cnt_prev_im_sal,";
sql+=" 0 sum_prev_im_sal, ";
sql+=" 0 cnt_cur_im_sal, ";
sql+=" 0 sum_cur_im_sal, ";
sql+=" 0 cnt_cur_rm, ";
sql+=" 0 sum_cur_rm, ";
sql+=" 0 cnt_cur_rm_sal, ";
sql+=" 0 sum_cur_rm_sal, ";
sql+=" 0 cnt_prev_rm, ";
sql+=" 0 sum_prev_rm, ";
sql+=" 0 cnt_cur_rez, ";
sql+=" 0 sum_cur_rez, ";
sql+=" 0 cnt_cur_sel_rez, ";
sql+=" 0 sum_cur_sel_rez, ";
sql+=" 0 cnt_prev_sel_rez,";
sql+=" 0 sum_prev_sel_rez";
sql+=" from db_nkc.dbo.B_Zakaz_CHG dbn ";
sql+=" where date_create between "+d1+" union all";
//sql+=" union all";
sql+=" select ";
sql+=" 0 cnt_cur,";
sql+=" 0 sum_cur, ";
sql+=" count(dbn.zakaz_id) cnt_prev,";
sql+=" sum(dbn.zakaz_summa) sum_prev,";
sql+=" 0 cnt_cur_sal, ";
sql+=" 0 sum_cur_sal, ";
sql+=" 0 cnt_cur_im, ";
sql+=" 0 sum_cur_im, ";
sql+=" 0 cnt_prev_im_sal, ";
sql+=" 0 sum_prev_im_sal, ";
sql+=" 0 cnt_cur_im_sal, ";
sql+=" 0 sum_cur_im_sal, ";
sql+=" 0 cnt_cur_rm, ";
sql+=" 0 sum_cur_rm, ";
sql+=" 0 cnt_cur_rm_sal, ";
sql+=" 0 sum_cur_rm_sal, ";
sql+=" 0 cnt_prev_rm, ";
sql+=" 0 sum_prev_rm, ";
sql+=" 0 cnt_cur_rez, ";
sql+=" 0 sum_cur_rez, ";
sql+=" 0 cnt_cur_sel_rez, ";
sql+=" 0 sum_cur_sel_rez, ";
sql+=" 0 cnt_prev_sel_rez, ";
sql+=" 0 sum_prev_sel_rez ";
sql+=" from db_nkc.dbo.B_Zakaz_CHG dbn";
sql+=" where date_create between "+d3;
sql+=" and dbn.current_state=8";
sql+=" and dbn.zakaz_id in (SELECT [ZakazID] FROM [B_Zakaz_Hist] WHERE Date_State between "+d1+")";
sql+=" union all ";
sql+=" select";
sql+=" 0 cnt_cur,";
sql+=" 0 sum_cur,";
sql+=" 0 cnt_prev,";
sql+=" 0 sum_prev,";
sql+=" count(dbn.zakaz_id) cnt_cur_sal,";
sql+=" sum(dbn.zakaz_summa) sum_cur_sal,";
sql+=" 0 cnt_cur_im,";
sql+=" 0 sum_cur_im,";
sql+=" 0 cnt_prev_im_sal,";
sql+=" 0 sum_prev_im_sal, ";
sql+=" 0 cnt_cur_im_sal, ";
sql+=" 0 sum_cur_im_sal, ";
sql+=" 0 cnt_cur_rm, ";
sql+=" 0 sum_cur_rm, ";
sql+=" 0 cnt_cur_rm_sal, ";
sql+=" 0 sum_cur_rm_sal, ";
sql+=" 0 cnt_prev_rm, ";
sql+=" 0 sum_prev_rm, ";
sql+=" 0 cnt_cur_rez, ";
sql+=" 0 sum_cur_rez, ";
sql+=" 0 cnt_cur_sel_rez,";
sql+=" 0 sum_cur_sel_rez,";
sql+=" 0 cnt_prev_sel_rez,";
sql+=" 0 sum_prev_sel_rez ";
sql+=" from db_nkc.dbo.B_Zakaz_CHG dbn";
sql+=" where date_create between "+d1;
sql+=" and current_state=8";
sql+=" and zakaz_id in (SELECT [ZakazID] FROM [B_Zakaz_Hist] WHERE Date_State between "+d1+")";
sql+=" union all";
sql+=" select ";
sql+=" 0 cnt_cur, ";
sql+=" 0 sum_cur, ";
sql+=" 0 cnt_prev, ";
sql+=" 0 sum_prev, ";
sql+=" 0 cnt_cur_sal, ";
sql+=" 0 sum_cur_sal, ";
sql+=" count(dbn.zakaz_id) cnt_cur_im,";
sql+=" sum(dbn.zakaz_summa) sum_cur_im, ";
sql+=" 0 cnt_prev_im_sal, ";
sql+=" 0 sum_prev_im_sal, ";
sql+=" 0 cnt_cur_im_sal, ";
sql+=" 0 sum_cur_im_sal, ";
sql+=" 0 cnt_cur_rm, ";
sql+=" 0 sum_cur_rm, ";
sql+=" 0 cnt_cur_rm_sal, ";
sql+=" 0 sum_cur_rm_sal, ";
sql+=" 0 cnt_prev_rm, ";
sql+=" 0 sum_prev_rm, ";
sql+=" 0 cnt_cur_rez, ";
sql+=" 0 sum_cur_rez, ";
sql+=" 0 cnt_cur_sel_rez, ";
sql+=" 0 sum_cur_sel_rez, ";
sql+=" 0 cnt_prev_sel_rez, ";
sql+=" 0 sum_prev_sel_rez ";
sql+=" from db_nkc.dbo.B_Zakaz_CHG dbn ";
sql+=" where date_create between "+d1;
sql+=" and zakaz_type=1";
sql+=" and user_id not in (SELECT [b_user_group].[USER_ID] FROM [b_user_group] WHERE [b_user_group].GROUP_ID=19)";
sql+=" union all";
sql+=" select";
sql+=" 0 cnt_cur,";
sql+=" 0 sum_cur,";
sql+=" 0 cnt_prev,";
sql+=" 0 sum_prev,";
sql+=" 0 cnt_cur_sal,";
sql+=" 0 sum_cur_sal,";
sql+=" 0 cnt_cur_im,";
sql+=" 0 sum_cur_im, ";
sql+=" count(dbn.zakaz_id) cnt_prev_im_sal,";
sql+=" sum(dbn.zakaz_summa) sum_prev_im_sal,";
sql+=" 0 cnt_cur_im_sal,";
sql+=" 0 sum_cur_im_sal,";
sql+=" 0 cnt_cur_rm, ";
sql+=" 0 sum_cur_rm, ";
sql+=" 0 cnt_cur_rm_sal, ";
sql+=" 0 sum_cur_rm_sal, ";
sql+=" 0 cnt_prev_rm, ";
sql+=" 0 sum_prev_rm, ";
sql+=" 0 cnt_cur_rez, ";
sql+=" 0 sum_cur_rez, ";
sql+=" 0 cnt_cur_sel_rez, ";
sql+=" 0 sum_cur_sel_rez, ";
sql+=" 0 cnt_prev_sel_rez, ";
sql+=" 0 sum_prev_sel_rez ";
sql+=" from db_nkc.dbo.B_Zakaz_CHG dbn ";
sql+=" where date_create between "+d3;
sql+=" and zakaz_type=1 ";
sql+=" and user_id not in (SELECT [b_user_group].[USER_ID] FROM [b_user_group] WHERE [b_user_group].GROUP_ID=19)";
sql+=" and dbn.current_state=8 ";
sql+=" and dbn.zakaz_id in (SELECT [ZakazID] FROM [B_Zakaz_Hist] WHERE Date_State between "+d1+")";
sql+="union all ";
sql+=" select ";
sql+=" 0 cnt_cur, ";
sql+=" 0 sum_cur, ";
sql+=" 0 cnt_prev, ";
sql+=" 0 sum_prev, ";
sql+=" 0 cnt_cur_sal, ";
sql+=" 0 sum_cur_sal, ";
sql+=" 0 cnt_cur_im, ";
sql+=" 0 sum_cur_im, ";
sql+=" 0 cnt_prev_im_sal, ";
sql+=" 0 sum_prev_im_sal, ";
sql+=" count(dbn.zakaz_id) cnt_cur_im_sal, ";
sql+=" sum(dbn.zakaz_summa) sum_cur_im_sal, ";
sql+=" 0 cnt_cur_rm, ";
sql+=" 0 sum_cur_rm, ";
sql+=" 0 cnt_cur_rm_sal, ";
sql+=" 0 sum_cur_rm_sal, ";
sql+=" 0 cnt_prev_rm, ";
sql+=" 0 sum_prev_rm, ";
sql+=" 0 cnt_cur_rez, ";
sql+=" 0 sum_cur_rez, ";
sql+=" 0 cnt_cur_sel_rez, ";
sql+=" 0 sum_cur_sel_rez, ";
sql+=" 0 cnt_prev_sel_rez, ";
sql+=" 0 sum_prev_sel_rez ";
sql+=" from db_nkc.dbo.B_Zakaz_CHG dbn ";
sql+=" where date_create between "+d1;
sql+=" and zakaz_type=1 ";
sql+=" and user_id not in (SELECT [b_user_group].[USER_ID] FROM [b_user_group] WHERE [b_user_group].GROUP_ID=19) ";
sql+=" and dbn.current_state=8 ";
sql+=" and dbn.zakaz_id in (SELECT [ZakazID] FROM [B_Zakaz_Hist] WHERE Date_State between "+d1+")";
sql+="union all ";
sql+=" select ";
sql+=" 0 cnt_cur, 0 sum_cur, 0 cnt_prev, 0 sum_prev, 0 cnt_cur_sal, 0 sum_cur_sal, 0 cnt_cur_im, 0 sum_cur_im,";
sql+=" 0 cnt_prev_im_sal, 0 sum_prev_im_sal, 0 cnt_cur_im_sal, 0 sum_cur_im_sal, count(dbn.zakaz_id) cnt_cur_rm, ";
sql+=" sum(dbn.zakaz_summa) sum_cur_rm, 0 cnt_cur_rm_sal, 0 sum_cur_rm_sal, 0 cnt_prev_rm, ";
sql+=" 0 sum_prev_rm, 0 cnt_cur_rez, 0 sum_cur_rez, 0 cnt_cur_sel_rez, 0 sum_cur_sel_rez, ";
sql+=" 0 cnt_prev_sel_rez, 0 sum_prev_sel_rez ";
sql+=" from db_nkc.dbo.B_Zakaz_CHG dbn ";
sql+=" where date_create between "+d1;
sql+=" and user_id in (SELECT [b_user_group].[USER_ID] FROM [b_user_group] WHERE [b_user_group].GROUP_ID=19) ";
sql+="union all ";
sql+=" select ";
sql+=" 0 cnt_cur, 0 sum_cur, 0 cnt_prev, 0 sum_prev, 0 cnt_cur_sal, ";
sql+=" 0 sum_cur_sal, 0 cnt_cur_im, 0 sum_cur_im, 0 cnt_prev_im_sal, ";
sql+=" 0 sum_prev_im_sal, 0 cnt_cur_im_sal, 0 sum_cur_im_sal, 0 cnt_cur_rm, ";
sql+=" 0 sum_cur_rm, count(dbn.zakaz_id) cnt_cur_rm_sal, sum(dbn.zakaz_summa) sum_cur_rm_sal, ";
sql+=" 0 cnt_prev_rm, 0 sum_prev_rm, 0 cnt_cur_rez, 0 sum_cur_rez, 0 cnt_cur_sel_rez, ";
sql+=" 0 sum_cur_sel_rez, 0 cnt_prev_sel_rez, 0 sum_prev_sel_rez ";
sql+=" from db_nkc.dbo.B_Zakaz_CHG dbn ";
sql+=" where date_create between "+d1;
sql+=" and user_id in (SELECT [b_user_group].[USER_ID] FROM [b_user_group] WHERE [b_user_group].GROUP_ID=19) ";
sql+=" and dbn.current_state=8 ";
sql+=" and dbn.zakaz_id in (SELECT [ZakazID] FROM [B_Zakaz_Hist] WHERE Date_State between "+d1+") ";
sql+="union all ";
sql+=" select ";
sql+=" 0 cnt_cur, 0 sum_cur, 0 cnt_prev, 0 sum_prev, 0 cnt_cur_sal, ";
sql+=" 0 sum_cur_sal, 0 cnt_cur_im, 0 sum_cur_im, 0 cnt_prev_im_sal, 0 sum_prev_im_sal, ";
sql+=" 0 cnt_cur_im_sal, 0 sum_cur_im_sal, 0 cnt_cur_rm, 0 sum_cur_rm, 0 cnt_cur_rm_sal, ";
sql+=" 0 sum_cur_rm_sal, count(dbn.zakaz_id) cnt_prev_rm, sum(dbn.zakaz_summa) sum_prev_rm, ";
sql+=" 0 cnt_cur_rez, 0 sum_cur_rez, 0 cnt_cur_sel_rez, 0 sum_cur_sel_rez, ";
sql+=" 0 cnt_prev_sel_rez, 0 sum_prev_sel_rez ";
sql+=" from db_nkc.dbo.B_Zakaz_CHG dbn ";
sql+=" where date_create between "+d3;
sql+=" and user_id in (SELECT [b_user_group].[USER_ID] FROM [b_user_group] WHERE [b_user_group].GROUP_ID=19) ";
sql+=" and dbn.current_state=8 ";
sql+=" and dbn.zakaz_id in (SELECT [ZakazID] FROM [B_Zakaz_Hist] WHERE Date_State between "+d1+") ";
sql+=" union all ";
sql+=" select ";
sql+=" 0 cnt_cur, 0 sum_cur, 0 cnt_prev, 0 sum_prev, 0 cnt_cur_sal, 0 sum_cur_sal, ";
sql+=" 0 cnt_cur_im, 0 sum_cur_im, 0 cnt_prev_im_sal, 0 sum_prev_im_sal, 0 cnt_cur_im_sal, ";
sql+=" 0 sum_cur_im_sal, 0 cnt_cur_rm, 0 sum_cur_rm, 0 cnt_cur_rm_sal, 0 sum_cur_rm_sal, ";
sql+=" 0 cnt_prev_rm, 0 sum_prev_rm, count(dbn.zakaz_id) cnt_cur_rez, sum(dbn.zakaz_summa) sum_cur_rez, ";
sql+=" 0 cnt_cur_sel_rez, 0 sum_cur_sel_rez, 0 cnt_prev_sel_rez, 0 sum_prev_sel_rez ";
sql+=" from db_nkc.dbo.B_Zakaz_CHG dbn ";
sql+=" where date_create between "+d1;
sql+=" and zakaz_type=2 ";
sql+=" union all ";
sql+=" select ";
sql+=" 0 cnt_cur, ";
sql+=" 0 sum_cur, ";
sql+=" 0 cnt_prev, ";
sql+=" 0 sum_prev, ";
sql+=" 0 cnt_cur_sal, ";
sql+=" 0 sum_cur_sal, ";
sql+=" 0 cnt_cur_im, ";
sql+=" 0 sum_cur_im, ";
sql+=" 0 cnt_prev_im_sal, ";
sql+=" 0 sum_prev_im_sal, ";
sql+=" 0 cnt_cur_im_sal, ";
sql+=" 0 sum_cur_im_sal, ";
sql+=" 0 cnt_cur_rm, ";
sql+=" 0 sum_cur_rm, ";
sql+=" 0 cnt_cur_rm_sal, ";
sql+=" 0 sum_cur_rm_sal, ";
sql+=" 0 cnt_prev_rm, ";
sql+=" 0 sum_prev_rm, ";
sql+=" 0 cnt_cur_rez, ";
sql+=" 0 sum_cur_rez, ";
sql+=" count(dbn.zakaz_id) cnt_cur_sel_rez, ";
sql+=" sum(dbn.zakaz_summa) sum_cur_sel_rez, ";
sql+=" 0 cnt_prev_sel_rez, ";
sql+=" 0 sum_prev_sel_rez ";
sql+=" from db_nkc.dbo.B_Zakaz_CHG dbn ";
sql+=" where date_create between "+d1;
sql+=" and zakaz_type=2 ";
sql+=" and dbn.current_state=8 or dbn.current_state=18 ";
sql+=" and dbn.zakaz_id in (SELECT [ZakazID] FROM [B_Zakaz_Hist] WHERE Date_State between "+d1+") ";
sql+=" union all ";
sql+=" select ";
sql+=" 0 cnt_cur, 0 sum_cur, 0 cnt_prev, 0 sum_prev, 0 cnt_cur_sal,0 sum_cur_sal, ";
sql+=" 0 cnt_cur_im, 0 sum_cur_im, 0 cnt_prev_im_sal,0 sum_prev_im_sal, 0 cnt_cur_im_sal, ";
sql+=" 0 sum_cur_im_sal,0 cnt_cur_rm,0 sum_cur_rm,0 cnt_cur_rm_sal, 0 sum_cur_rm_sal, ";
sql+=" 0 cnt_prev_rm,0 sum_prev_rm,0 cnt_cur_rez,0 sum_cur_rez,0 cnt_cur_sel_rez,0 sum_cur_sel_rez, ";
sql+=" count(dbn.zakaz_id) cnt_prev_sel_rez,sum(dbn.zakaz_summa) sum_prev_sel_rez ";
sql+=" from db_nkc.dbo.B_Zakaz_CHG dbn ";
sql+=" where date_create between "+d3;
sql+=" and zakaz_type=2 ";
sql+=" and dbn.current_state=8 or dbn.current_state=18 ";
sql+=" and dbn.zakaz_id in (SELECT [ZakazID] FROM [B_Zakaz_Hist] WHERE Date_State between "+d1+") ";
sql+=") z";
//string queryString ="SELECT vwpartn_1.PT_SHORTNAME as 'Магазины',dbu.EMAIL, dbu.[Group_shop] as 'Группа', [zakaz_date] as 'Дата создания', dbn.[zakaz_id] as 'Номер заказа', max(isnull(CHG_DI.paramval,'')) as 'ФИО 1 продавца', max(isnull(CHG_DI_1.paramval,'')) as 'ФИО 2 продавца', SUM([qty]) as 'Кол-во заказанных экземпляров', SUM([qty_case]) as 'Кол-во доставленных экземпляров', SUM([qty_shop]) as 'Кол-во выкупленных экземпляров', dbstat.Status_Name as 'Статус заказа', SUM([zakaz_summa]) as 'Сумма заказа', dbtype.[type_name] as 'Тип заказа', vwpartn.PT_SHORTNAME as 'Магазин доставки', comment FROM b_user_group as dbc, [B_Zakaz_CHG] as dbn LEFT JOIN b_users dbu on dbn.user_id=dbu.ID LEFT JOIN [B_Zakaz_CHG_Content] dbcont on dbn.zakaz_id=dbcont.zakaz_id LEFT JOIN [B_Zakaz_Status] dbstat on dbstat.Status_ID=dbn.current_state LEFT JOIN [B_Zakaz_Type] dbtype on dbtype.zakaz_type=dbn.zakaz_type LEFT JOIN [B_SHOPS_DI] dbshop on dbshop.delivery_id=dbn.delivery_id LEFT JOIN [B_Zakaz_CHG_DI] CHG_DI on param = 'ФИО Кассира' AND CHG_DI.zakaz_id=dbn.zakaz_id LEFT JOIN [B_Zakaz_CHG_DI] CHG_DI_1 on CHG_DI_1.param= 'ФИО ст.менеджера' AND CHG_DI_1.zakaz_id=dbn.zakaz_id LEFT JOIN vw_GetPartners vwpartn on vwpartn.PT_ID=dbshop.p_id LEFT JOIN vw_GetPartners vwpartn_1 on vwpartn_1.email=dbu.EMAIL WHERE dbn.[user_id] in (SELECT dbc.[USER_ID] FROM db_nkc.dbo.[b_user_group] WHERE dbc.GROUP_ID=19) AND dbn.zakaz_date between '"+d1+"' AND '"+d2+"' AND dbn.zakaz_type<>2 AND dbu.EMAIL <> 'balashiha@bookcentre.ru' AND dbu.EMAIL <> 'pilugina.a@bookcentre.ru' GROUP by dbu.EMAIL,[zakaz_date],dbn.[zakaz_id],dbstat.Status_Name,dbtype.[type_name],dbshop.p_id,comment,vwpartn.PT_SHORTNAME,vwpartn_1.PT_SHORTNAME,dbu.[Group_shop] order by dbn.[zakaz_id]";
SqlCommand command = new SqlCommand(sql, connection);
//command.CommandTimeout = connection.ConnectionTimeout;
command.CommandTimeout = 600;
//command.ExecuteNonQuery ();
command.CommandType = CommandType.Text;
//OdbcDataAdapter da = new OdbcDataAdapter("select [zakaz_id],[zakaz_date], from B_Zakaz_CHG,b_users,b_user_group WHERE [zakaz_date] between '"+d1+"' and '"+d2+"'", conn);
//dbcDataAdapter da = new OdbcDataAdapter("SELECT dbu.EMAIL,[zakaz_id],dbn.[date_create],dbn.[zakaz_date],dbn.[zakaz_summa],dbn.[delivery_id],dbn.[zakaz_summa_0],dbn.[zakaz_type] FROM [b_user_group] as dbc, [B_Zakaz_CHG] as dbn JOIN b_users dbu on dbn.user_id=dbu.ID WHERE dbn.user_id in (SELECT dbc.[USER_ID] FROM [b_user_group] WHERE dbc.GROUP_ID=19) AND [zakaz_date] between '"+d1+"' and '"+d2+"' order by dbn.zakaz_id", conn);
SqlDataAdapter da = new SqlDataAdapter(sql, connection);
//da.CommandTimeout = 0;
DataSet ds = new DataSet();
da.SelectCommand.CommandTimeout = 600;
da.Fill(ds, "Количество_заказов");
// grid.DataSource = ds.Tables["Количество_заказов"];
// grid.DataBind();
if (BIDN > 0)
{
DataTable dt = ds.Tables["Количество_заказов"];
string attachment = "attachment; filename=zak_"+DateTime.Now.ToString("yyyyMMddHHmm")+".csv";
Response.ClearContent();
Response.AddHeader("content-disposition", attachment);
Response.ContentType = "application/vnd.ms-excel";
Response.ContentEncoding = System.Text.Encoding.GetEncoding(1251);
//Response.Charset= "windows-1251";
string tab = "";
foreach (DataColumn dc in dt.Columns)
{
Response.Write(";" + dc.ColumnName);
tab = "\t";
}
Response.Write("\n");
int i;
foreach (DataRow dr in dt.Rows)
{
tab = "";
for (i = 0; i < dt.Columns.Count; i++)
{
Response.Write(";" + dr[i].ToString());
tab = "\t";
}
Response.Write("\n");
}
Response.End();
}
else { grid.DataSource = ds.Tables["Количество_заказов"];
grid.DataMember = "Количество_заказов";
grid.DataBind();
}
connection.Close();
//}
/*############################
on error resume next
Session.LCID = 1033
Response.Buffer = true
dim strDBType, strConnString,
'#######################################################
'## Коннект на любую из Баз данных (access, sqlserver or mysql)
'#######################################################
'strDBType = "sqlserver"
strDBType = "access"
'strDBType = "mysql"
'## Создай коннект одстранением апострофа strConnString ! на драйвер который надо
'strConnString = "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=c:\inetpub\dbroot\db2000.mdb" '## Коннект на MS Access 97
'strConnString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath("database/db2000.mdb") '## Коннект на MS Access 2000 using virtual path
strConnString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\Inetpub\database\db2000.mdb;" '## Коннект на MS Access 2000
'strConnString = "driver={SQL Server};server=SERVER_NAME;uid=SQL_USER;pwd=PASSWORD;database=DATABASE_NAME" '## MS SQL Server 7
'strConnString = "driver=MySQL;server=SERVER_NAME;uid=MYSQL_USER;pwd=PASSWORD;database=DATABASE_NAME" '## Коннект на MySQL
###################################*/
}
}
/*
public DataTable GetData()
{
int BID;
BID = Convert.ToInt32(Request.Form["BID"]);
OdbcConnection conn = new OdbcConnection();
conn.ConnectionString =
//"DRIVER={MySQL ODBC 5.1 Driver};"+
//"DRIVER={MySQL ODBC 3.51 Driver};"+
//"DRIVER={MySQL ODBC SQL Server Driver};"+
"DRIVER={SQL Server};"+
@"Server=BETA\NEWBOOKSQL;" +
@"Database=db_Matrix;" +
// "Trusted_Connection=Yes;"
"Integrated Security=SSPI;"
//"UID=webstat;" +
//"Pwd=webstat;"
//USE db_Matrix select dbo.tblMatrix
;
conn.Open();
OdbcDataAdapter da = new OdbcDataAdapter("procBookInfoNE '"+BID+"'", conn);
DataSet ds = new DataSet();
da.Fill(ds, "BookID");
grid.DataSource = ds.Tables["BookID"];
grid.DataMember = "BookID";
grid.DataBind();
conn.Close();
return ds.Tables["BookID"];
}
protected void Button3_Click(object sender, EventArgs e)
{
int BID;
BID = Convert.ToInt32(Request.Form["BID"]);
DataTable dt = GetData();
string attachment = "attachment; filename=Employee"+DateTime.Now.ToString("yyyyMMddHHmm")+".xls";
Response.ClearContent();
Response.AddHeader("content-disposition", attachment);
Response.ContentType = "application/vnd.ms-excel";
string tab = "";
foreach (DataColumn dc in dt.Columns)
{
Response.Write(tab + dc.ColumnName);
tab = "\t";
}
Response.Write("\n");
int i;
foreach (DataRow dr in dt.Rows)
{
tab = "";
for (i = 0; i < dt.Columns.Count; i++)
{
Response.Write(tab + dr[i].ToString());
tab = "\t";
}
Response.Write("\n");
}
Response.End();
}*/
private void ChangeTimeout(Component component, int timeout)
{
if (!component.GetType().Name.Contains("TableAdapter"))
{
return;
}
PropertyInfo adapterProp = component.GetType().GetProperty("CommandCollection", BindingFlags.NonPublic | BindingFlags.GetProperty | BindingFlags.Instance);
if (adapterProp == null)
{
return;
}
SqlCommand[] command = adapterProp.GetValue(component, null) as SqlCommand[];
if (command == null)
{
return;
}
command[0].CommandTimeout = timeout;
}
</script>
