protected void OnClick_LinkButton2(object sender, EventArgs e) { GridViewRow tabla = (GridViewRow)(((LinkButton)sender).Parent.Parent); //IdProducto = ((Label)tabla.FindControl("Label1")).Text.ToString(); SqlDataSource10.SelectParameters["ID_Existencia"].DefaultValue = ((Label)tabla.FindControl("Label8")).Text.ToString(); SqlDataSource10.DataSourceMode = SqlDataSourceMode.DataReader; SqlDataReader PRECIOPROD; PRECIOPROD = (SqlDataReader)SqlDataSource10.Select(DataSourceSelectArguments.Empty); if (PRECIOPROD.Read()) { precioProducto = PRECIOPROD["PrecioUnitario"].ToString(); } SqlDataSource10.InsertParameters["Cantidad"].DefaultValue = ((Label)tabla.FindControl("Label4")).Text.ToString(); SqlDataSource10.InsertParameters["Precio"].DefaultValue = precioProducto; SqlDataSource10.InsertParameters["Total"].DefaultValue = (Convert.ToDecimal(precioProducto) * Convert.ToDecimal(((Label)tabla.FindControl("Label4")).Text.ToString())).ToString(); SqlDataSource10.InsertParameters["ID_Existencia"].DefaultValue = ((Label)tabla.FindControl("Label8")).Text.ToString(); SqlDataSource10.InsertParameters["ID_Compra"].DefaultValue = ((Label)tabla.FindControl("Label10")).Text.ToString(); SqlDataSource10.Insert(); //SqlDataSource10.UpdateParameters["Cantidad"].DefaultValue = ((Label)tabla.FindControl("Label8")).Text.ToString(); //SqlDataSource10.UpdateParameters["ID_Existencia"].DefaultValue = ((Label)tabla.FindControl("Label4")).Text.ToString(); //SqlDataSource10.Update(); SqlDataSource4.UpdateParameters["ID_DevProPro"].DefaultValue = ((Label)tabla.FindControl("Label1")).Text.ToString(); SqlDataSource4.UpdateParameters["Cantidad"].DefaultValue = ((Label)tabla.FindControl("Label4")).Text.ToString(); SqlDataSource4.UpdateParameters["idStock"].DefaultValue = ((Label)tabla.FindControl("Label8")).Text.ToString(); SqlDataSource4.Update(); Response.Redirect("DevProPro.aspx"); }
protected void serch_Click1(object sender, EventArgs e) { cdf.Open(); SqlCommand del1 = new SqlCommand("delete from help_rep_cus", cdf); del1.ExecuteScalar(); SqlCommand del2 = new SqlCommand("delete from help_repcus_tarikh", cdf); del2.ExecuteScalar(); SqlCommand del3 = new SqlCommand("delete from help_repcus2", cdf); del3.ExecuteScalar(); SqlCommand del4 = new SqlCommand("delete from help_repcus_now", cdf); del4.ExecuteScalar(); SqlCommand del5 = new SqlCommand("delete from help_3", cdf); del5.ExecuteScalar(); cdf.Close(); yearst = yerstart.SelectedValue; mounthst = monstart.SelectedValue; dayst = daystart.SelectedValue; datestart = yearst + "/" + mounthst + "/" + dayst; lstart.Text = datestart; lstartprint.Text = lstart.Text; lfinishprintstart.Text = lstart.Text; yearfin = yerfin.SelectedValue; mounthfin = monfin.SelectedValue; dayfini = dayfin.SelectedValue; datefinish = yearfin + "/" + mounthfin + "/" + dayfini; lfinish.Text = datefinish; lfinishprint.Text = lfinish.Text; cdf.Open(); SqlDataSource10.SelectCommand = "SELECT TOP (100) PERCENT flow_info.nam_f, flow_info.cod_f, flow_info.date_first_come, flow_info.customer_f, SUM(arrange_gol.remain_barg) AS input, SUM(arrange_gol.remain_barg) AS remain, flow_info.id, flow_info.color_f, flow_info.type_color, flow_info.typ_f FROM flow_info INNER JOIN arrange_gol ON flow_info.id = arrange_gol.id_gol WHERE (flow_info.date_first_come BETWEEN '1375/01/01' AND '" + lstart.Text + "') AND (flow_info.customer_f_id = " + ddlcustomer.SelectedValue + ") GROUP BY flow_info.nam_f, flow_info.cod_f, flow_info.date_first_come, flow_info.customer_f, flow_info.id, flow_info.color_f, flow_info.type_color, flow_info.typ_f ORDER BY flow_info.date_first_come,flow_info.nam_f"; dv = (System.Data.DataView)SqlDataSource10.Select(DataSourceSelectArguments.Empty); count = dv.Count; if (dv.Count != 0) { for (int i = 0; i < count; i++) { idgol = Convert.ToInt32(dv.ToTable().Rows[i]["id"]); cdf.Close(); cdf.Open(); SqlCommand selectid = new SqlCommand("INSERT INTO help_rep_cus(idgol,namgol,colorgol,typegol,firstcom,remain,a10070,a2535,a4060,a5070,a5271,a6080,a6585,a7182,a4) " + " SELECT (" + idgol + ") AS 'idgol', " + " (SELECT nam_f AS namf FROM dbo.flow_info AS flow_info_10 " + " WHERE (customer_f_id = " + ddlcustomer.SelectedValue + ") AND (id = " + idgol + ")) AS 'nam_gol' " + " , (SELECT color_f AS color FROM dbo.flow_info WHERE (customer_f_id = " + ddlcustomer.SelectedValue + ") AND (id = " + idgol + ")) AS 'color_gol', " + " (SELECT typ_f AS AStype FROM dbo.flow_info AS flow_info_12 WHERE (customer_f_id = " + ddlcustomer.SelectedValue + ") AND (id = " + idgol + ")) AS 'type_gol' " + " ,(SELECT date_first_come AS come FROM dbo.flow_info AS flow_info_11 WHERE (customer_f_id = " + ddlcustomer.SelectedValue + ") " + " AND (id = " + idgol + ")) AS 'first_come',(SELECT case when SUM(remain_barg)>0 then SUM(remain_barg) else 0 " + " end AS remain FROM dbo.arrange_gol WHERE (id_gol = " + idgol + ")) AS 'remain_barg', " + " (SELECT case when SUM(arrange_gol_9.remain_barg)>0 then SUM(arrange_gol_9.remain_barg) else 0 end AS Expr1 " + " FROM dbo.flow_info AS flow_info_9 INNER JOIN dbo.arrange_gol AS arrange_gol_9 ON flow_info_9.id = arrange_gol_9.id_gol " + " WHERE (flow_info_9.customer_f_id = " + ddlcustomer.SelectedValue + ") AND (arrange_gol_9.dim_arr_f = 1) " + " AND (flow_info_9.id = " + idgol + ")) AS '100*70',(SELECT case when SUM(arrange_gol_1.remain_barg)>0 then " + " SUM(arrange_gol_1.remain_barg) else 0 end AS Expr1 FROM dbo.flow_info AS flow_info_1 INNER JOIN dbo.arrange_gol " + " AS arrange_gol_1 ON flow_info_1.id = arrange_gol_1.id_gol WHERE (flow_info_1.customer_f_id = " + ddlcustomer.SelectedValue + ") AND (arrange_gol_1.dim_arr_f = 2) " + " AND (flow_info_1.id = " + idgol + ")) AS '25*35', (SELECT case when SUM(arrange_gol_2.remain_barg)>0 " + " then SUM(arrange_gol_2.remain_barg) else 0 end AS Expr1 FROM dbo.flow_info AS flow_info_2 INNER JOIN dbo.arrange_gol " + " AS arrange_gol_2 ON flow_info_2.id = arrange_gol_2.id_gol WHERE (flow_info_2.customer_f_id = " + ddlcustomer.SelectedValue + ") AND " + " (arrange_gol_2.dim_arr_f = 3) AND (flow_info_2.id = " + idgol + ")) AS '40*60',(SELECT case when " + " SUM(arrange_gol_8.remain_barg)>0 then SUM(arrange_gol_8.remain_barg) else 0 end AS Expr1 FROM dbo.flow_info " + " AS flow_info_8 INNER JOIN dbo.arrange_gol AS arrange_gol_8 ON flow_info_8.id = arrange_gol_8.id_gol WHERE (flow_info_8.customer_f_id = " + ddlcustomer.SelectedValue + ") AND (arrange_gol_8.dim_arr_f = 4) " + " AND (flow_info_8.id = " + idgol + ")) AS '50*70', (SELECT case when SUM(arrange_gol_7.remain_barg)>0 then " + " SUM(arrange_gol_7.remain_barg) else 0 end AS Expr1 FROM dbo.flow_info AS flow_info_7 INNER JOIN dbo.arrange_gol " + " AS arrange_gol_7 ON flow_info_7.id = arrange_gol_7.id_gol WHERE (flow_info_7.customer_f_id = " + ddlcustomer.SelectedValue + ") AND " + " (arrange_gol_7.dim_arr_f = 5) AND (flow_info_7.id = " + idgol + ")) AS '52*71', " + " (SELECT case when SUM(arrange_gol_6.remain_barg)>0 then SUM(arrange_gol_6.remain_barg) else 0 end AS Expr1 " + " FROM dbo.flow_info AS flow_info_6 INNER JOIN dbo.arrange_gol AS arrange_gol_6 ON flow_info_6.id = arrange_gol_6.id_gol WHERE " + " (flow_info_6.customer_f_id = " + ddlcustomer.SelectedValue + ") AND (arrange_gol_6.dim_arr_f = 6) AND " + " (flow_info_6.id = " + idgol + ")) AS '60*80',(SELECT case when SUM(arrange_gol_5.remain_barg)>0 then " + " SUM(arrange_gol_5.remain_barg) else 0 end AS Expr1 FROM dbo.flow_info AS flow_info_5 INNER JOIN dbo.arrange_gol AS " + " arrange_gol_5 ON flow_info_5.id = arrange_gol_5.id_gol WHERE (flow_info_5.customer_f_id = " + ddlcustomer.SelectedValue + ") AND (arrange_gol_5.dim_arr_f = 7) AND " + " (flow_info_5.id = " + idgol + ")) AS '65*85',(SELECT case when SUM(arrange_gol_4.remain_barg)>0 then " + " SUM(arrange_gol_4.remain_barg) else 0 end AS Expr1 FROM dbo.flow_info AS flow_info_4 INNER JOIN dbo.arrange_gol AS " + " arrange_gol_4 ON flow_info_4.id = arrange_gol_4.id_gol WHERE (flow_info_4.customer_f_id = " + ddlcustomer.SelectedValue + ") AND (arrange_gol_4.dim_arr_f = 8) AND " + " (flow_info_4.id = " + idgol + ")) AS '71*82',(SELECT case when SUM(arrange_gol_3.remain_barg)>0 then " + " SUM(arrange_gol_3.remain_barg) else 0 end AS Expr1 FROM dbo.flow_info AS flow_info_3 INNER JOIN dbo.arrange_gol AS arrange_gol_3 " + " ON flow_info_3.id = arrange_gol_3.id_gol WHERE (flow_info_3.customer_f_id = " + ddlcustomer.SelectedValue + ") AND (arrange_gol_3.dim_arr_f = 9) AND " + " (flow_info_3.id = " + idgol + ")) AS 'A4' ", cdf); selectid.ExecuteScalar(); } } cdf.Close(); cdf.Open(); SqlCommand inser = new SqlCommand("INSERT INTO help_repcus_tarikh ([a10070],[a2535],[a4060],[a5070],[a5271],[a6080],[a6585],[a7182],[a4]) " + " SELECT CASE WHEN SUM(a10070) IS NULL THEN 0 ELSE SUM(a10070) END ,CASE WHEN SUM(a2535) IS NULL THEN 0 ELSE SUM(a2535) END " + " ,CASE WHEN SUM(a4060) IS NULL THEN 0 ELSE SUM(a4060) END ,CASE WHEN SUM(a5070) IS NULL THEN 0 ELSE SUM(a5070) END " + " ,CASE WHEN SUM(a5271) IS NULL THEN 0 ELSE SUM(a5271) END ,CASE WHEN SUM(a6080) IS NULL THEN 0 ELSE SUM(a6080) END " + " ,CASE WHEN SUM(a6585) IS NULL THEN 0 ELSE SUM(a6585) END ,CASE WHEN SUM(a7182) IS NULL THEN 0 ELSE SUM(a7182) END " + " ,CASE WHEN SUM(a4) IS NULL THEN 0 ELSE SUM(a4) END FROM help_rep_cus ", cdf); inser.ExecuteScalar(); SqlCommand inserttt = new SqlCommand("INSERT INTO [depogol].[dbo].[help_repcus2] " + " ([a10070] ,[a2535] ,[a4060] ,[a5070] ,[a5271] ,[a6080] ,[a6585] ,[a7182] ,[a4]) " + " SELECT (SELECT CASE WHEN subsum >= 0 THEN (amount - subsum) ELSE amount + (- subsum) END AS total FROM (SELECT (SELECT a10070 " + " FROM dbo.help_repcus_tarikh) AS amount,(SELECT CASE WHEN SUM(sub_arrange_8.remain) IS NULL THEN 0 ELSE SUM(sub_arrange_8.remain) END AS summ " + " FROM dbo.sub_arrange AS sub_arrange_8 INNER JOIN dbo.arrange_gol AS arrange_gol_8 ON " + " sub_arrange_8.arrange_cod_f = arrange_gol_8.id INNER JOIN dbo.flow_info AS flow_info_8 ON arrange_gol_8.id_gol = flow_info_8.id " + " WHERE (sub_arrange_8.datee BETWEEN '" + lstartprint.Text + "' AND '" + lfinishprint.Text + "') AND (arrange_gol_8.dim_arr_f = 1) AND (flow_info_8.customer_f_id = " + ddlcustomer.SelectedValue + ")) AS subsum) AS total) AS a10070, " + " (SELECT CASE WHEN subsum >= 0 THEN (amount - subsum) ELSE amount + (- subsum) END AS total FROM (SELECT (SELECT a2535 " + " FROM dbo.help_repcus_tarikh AS help_repcus_tarikh_8) AS amount,(SELECT CASE WHEN SUM(sub_arrange_8.remain) IS NULL THEN 0 ELSE " + " SUM(sub_arrange_8.remain) END AS summ FROM dbo.sub_arrange AS sub_arrange_8 INNER JOIN " + " dbo.arrange_gol AS arrange_gol_8 ON sub_arrange_8.arrange_cod_f = arrange_gol_8.id INNER JOIN " + " dbo.flow_info AS flow_info_8 ON arrange_gol_8.id_gol = flow_info_8.id WHERE (sub_arrange_8.datee BETWEEN '" + lstartprint.Text + "' AND '" + lfinishprint.Text + "') AND (arrange_gol_8.dim_arr_f = 2) " + " AND (flow_info_8.customer_f_id = " + ddlcustomer.SelectedValue + ")) AS subsum) AS total_8) AS a2535,(SELECT CASE WHEN subsum >= 0 THEN (amount - subsum) ELSE amount + (- subsum) END AS total " + " FROM (SELECT (SELECT a4060 FROM dbo.help_repcus_tarikh AS help_repcus_tarikh_8) AS amount, " + " (SELECT CASE WHEN SUM(sub_arrange_8.remain) IS NULL THEN 0 ELSE SUM(sub_arrange_8.remain) END AS summ FROM dbo.sub_arrange AS sub_arrange_8 INNER JOIN " + " dbo.arrange_gol AS arrange_gol_8 ON sub_arrange_8.arrange_cod_f = arrange_gol_8.id INNER JOIN " + " dbo.flow_info AS flow_info_8 ON arrange_gol_8.id_gol = flow_info_8.id " + " WHERE (sub_arrange_8.datee BETWEEN '" + lstartprint.Text + "' AND '" + lfinishprint.Text + "') AND (arrange_gol_8.dim_arr_f = 3) AND (flow_info_8.customer_f_id = " + ddlcustomer.SelectedValue + ")) AS subsum) AS total_8_1) " + " AS a4060, (SELECT CASE WHEN subsum >= 0 THEN (amount - subsum) ELSE amount + (- subsum) END AS total FROM (SELECT (SELECT a5070 " + " FROM dbo.help_repcus_tarikh AS help_repcus_tarikh_8) AS amount, " + " (SELECT CASE WHEN SUM(sub_arrange_8.remain) IS NULL THEN 0 ELSE SUM(sub_arrange_8.remain) END AS summ " + " FROM dbo.sub_arrange AS sub_arrange_8 INNER JOIN dbo.arrange_gol AS arrange_gol_8 ON sub_arrange_8.arrange_cod_f = arrange_gol_8.id INNER JOIN " + " dbo.flow_info AS flow_info_8 ON arrange_gol_8.id_gol = flow_info_8.id " + " WHERE (sub_arrange_8.datee BETWEEN '" + lstartprint.Text + "' AND '" + lfinishprint.Text + "') AND (arrange_gol_8.dim_arr_f = 4) AND (flow_info_8.customer_f_id = " + ddlcustomer.SelectedValue + ")) AS subsum) AS total_8_1_6) " + " AS a5070, (SELECT CASE WHEN subsum >= 0 THEN (amount - subsum) ELSE amount + (- subsum) END AS total FROM (SELECT (SELECT a5271 " + " FROM dbo.help_repcus_tarikh AS help_repcus_tarikh_8) AS amount,(SELECT CASE WHEN SUM(sub_arrange_8.remain) IS NULL THEN 0 ELSE SUM(sub_arrange_8.remain) END AS summ " + " FROM dbo.sub_arrange AS sub_arrange_8 INNER JOIN dbo.arrange_gol AS arrange_gol_8 ON sub_arrange_8.arrange_cod_f = arrange_gol_8.id INNER JOIN " + " dbo.flow_info AS flow_info_8 ON arrange_gol_8.id_gol = flow_info_8.id " + " WHERE (sub_arrange_8.datee BETWEEN '" + lstartprint.Text + "' AND '" + lfinishprint.Text + "') AND (arrange_gol_8.dim_arr_f = 5) AND (flow_info_8.customer_f_id = " + ddlcustomer.SelectedValue + ")) AS subsum) AS total_8_1_5) " + " AS a5271, (SELECT CASE WHEN subsum >= 0 THEN (amount - subsum) ELSE amount + (- subsum) END AS total FROM (SELECT (SELECT a6080 " + " FROM dbo.help_repcus_tarikh AS help_repcus_tarikh_8) AS amount, " + " (SELECT CASE WHEN SUM(sub_arrange_8.remain) IS NULL THEN 0 ELSE SUM(sub_arrange_8.remain) END AS summ FROM dbo.sub_arrange AS sub_arrange_8 INNER JOIN " + " dbo.arrange_gol AS arrange_gol_8 ON sub_arrange_8.arrange_cod_f = arrange_gol_8.id INNER JOIN dbo.flow_info AS flow_info_8 ON arrange_gol_8.id_gol = flow_info_8.id " + " WHERE (sub_arrange_8.datee BETWEEN '" + lstartprint.Text + "' AND '" + lfinishprint.Text + "') AND (arrange_gol_8.dim_arr_f = 6) AND (flow_info_8.customer_f_id = " + ddlcustomer.SelectedValue + ")) AS subsum) AS total_8_1_4) " + " AS a6080,(SELECT CASE WHEN subsum >= 0 THEN (amount - subsum) ELSE amount + (- subsum) END AS total FROM (SELECT (SELECT a6585 " + " FROM dbo.help_repcus_tarikh AS help_repcus_tarikh_8) AS amount, " + " (SELECT CASE WHEN SUM(sub_arrange_8.remain) IS NULL THEN 0 ELSE SUM(sub_arrange_8.remain) END AS summ FROM dbo.sub_arrange AS sub_arrange_8 INNER JOIN " + " dbo.arrange_gol AS arrange_gol_8 ON sub_arrange_8.arrange_cod_f = arrange_gol_8.id INNER JOIN dbo.flow_info AS flow_info_8 ON arrange_gol_8.id_gol = flow_info_8.id " + " WHERE (sub_arrange_8.datee BETWEEN '" + lstartprint.Text + "' AND '" + lfinishprint.Text + "') AND (arrange_gol_8.dim_arr_f = 7) AND (flow_info_8.customer_f_id = " + ddlcustomer.SelectedValue + ")) AS subsum) AS total_8_1_3) " + " AS a6585,(SELECT CASE WHEN subsum >= 0 THEN (amount - subsum) ELSE amount + (- subsum) END AS total FROM (SELECT (SELECT a7182 " + " FROM dbo.help_repcus_tarikh AS help_repcus_tarikh_8) AS amount,(SELECT CASE WHEN SUM(sub_arrange_8.remain) IS NULL THEN 0 ELSE SUM(sub_arrange_8.remain) END AS summ " + " FROM dbo.sub_arrange AS sub_arrange_8 INNER JOIN dbo.arrange_gol AS arrange_gol_8 ON sub_arrange_8.arrange_cod_f = arrange_gol_8.id INNER JOIN " + " dbo.flow_info AS flow_info_8 ON arrange_gol_8.id_gol = flow_info_8.id " + " WHERE (sub_arrange_8.datee BETWEEN '" + lstartprint.Text + "' AND '" + lfinishprint.Text + "') AND (arrange_gol_8.dim_arr_f = 8) AND (flow_info_8.customer_f_id = " + ddlcustomer.SelectedValue + ")) AS subsum) AS total_8_1_2) " + " AS a7182,(SELECT CASE WHEN subsum >= 0 THEN (amount - subsum) ELSE amount + (- subsum) END AS total FROM (SELECT (SELECT a4 " + " FROM dbo.help_repcus_tarikh AS help_repcus_tarikh_8) AS amount,(SELECT CASE WHEN SUM(sub_arrange_8.remain) IS NULL THEN 0 ELSE SUM(sub_arrange_8.remain) END AS summ " + " FROM dbo.sub_arrange AS sub_arrange_8 INNER JOIN dbo.arrange_gol AS arrange_gol_8 ON sub_arrange_8.arrange_cod_f = arrange_gol_8.id INNER JOIN " + " dbo.flow_info AS flow_info_8 ON arrange_gol_8.id_gol = flow_info_8.id WHERE (sub_arrange_8.datee BETWEEN '" + lstartprint.Text + "' AND '" + lfinishprint.Text + "') AND (arrange_gol_8.dim_arr_f = 9) AND (flow_info_8.customer_f_id = " + ddlcustomer.SelectedValue + ")) AS subsum) AS total_8_1_1) AS a4", cdf); inserttt.ExecuteScalar(); SqlCommand insertha = new SqlCommand("INSERT INTO [depogol].[dbo].[help_repcus_now] " + " ([a10070] ,[a2535] ,[a4060] ,[a5070] ,[a5271] ,[a6080] ,[a6585] ,[a7182] ,[a4]) " + " SELECT (SELECT CASE WHEN SUM(sub_arrange_8.remain) IS NULL THEN 0 ELSE SUM(sub_arrange_8.remain) END AS summ " + " FROM dbo.sub_arrange AS sub_arrange_8 INNER JOIN dbo.arrange_gol AS arrange_gol_8 ON sub_arrange_8.arrange_cod_f = arrange_gol_8.id INNER JOIN " + " dbo.flow_info AS flow_info_8 ON arrange_gol_8.id_gol = flow_info_8.id " + " WHERE (sub_arrange_8.datee BETWEEN '" + lstartprint.Text + "' AND '" + lfinishprint.Text + "') AND (arrange_gol_8.dim_arr_f = 1) AND (flow_info_8.customer_f_id = " + ddlcustomer.SelectedValue + ")) AS a10070, " + " (SELECT CASE WHEN SUM(sub_arrange_8.remain) IS NULL THEN 0 ELSE SUM(sub_arrange_8.remain) END AS summ " + " FROM dbo.sub_arrange AS sub_arrange_8 INNER JOIN dbo.arrange_gol AS arrange_gol_8 ON sub_arrange_8.arrange_cod_f = arrange_gol_8.id INNER JOIN " + " dbo.flow_info AS flow_info_8 ON arrange_gol_8.id_gol = flow_info_8.id " + " WHERE (sub_arrange_8.datee BETWEEN '" + lstartprint.Text + "' AND '" + lfinishprint.Text + "') AND (arrange_gol_8.dim_arr_f = 2) AND (flow_info_8.customer_f_id = " + ddlcustomer.SelectedValue + ")) AS a2535, " + " (SELECT CASE WHEN SUM(sub_arrange_8.remain) IS NULL THEN 0 ELSE SUM(sub_arrange_8.remain) END AS summ " + " FROM dbo.sub_arrange AS sub_arrange_8 INNER JOIN dbo.arrange_gol AS arrange_gol_8 ON sub_arrange_8.arrange_cod_f = arrange_gol_8.id INNER JOIN " + " dbo.flow_info AS flow_info_8 ON arrange_gol_8.id_gol = flow_info_8.id " + " WHERE (sub_arrange_8.datee BETWEEN '" + lstartprint.Text + "' AND '" + lfinishprint.Text + "') AND (arrange_gol_8.dim_arr_f = 3) AND (flow_info_8.customer_f_id = " + ddlcustomer.SelectedValue + ")) AS a4060, " + " (SELECT CASE WHEN SUM(sub_arrange_8.remain) IS NULL THEN 0 ELSE SUM(sub_arrange_8.remain) END AS summ " + " FROM dbo.sub_arrange AS sub_arrange_8 INNER JOIN " + " dbo.arrange_gol AS arrange_gol_8 ON sub_arrange_8.arrange_cod_f = arrange_gol_8.id INNER JOIN " + " dbo.flow_info AS flow_info_8 ON arrange_gol_8.id_gol = flow_info_8.id " + " WHERE (sub_arrange_8.datee BETWEEN '" + lstartprint.Text + "' AND '" + lfinishprint.Text + "') AND (arrange_gol_8.dim_arr_f = 4) AND (flow_info_8.customer_f_id = " + ddlcustomer.SelectedValue + ")) AS a5070, " + " (SELECT CASE WHEN SUM(sub_arrange_8.remain) IS NULL THEN 0 ELSE SUM(sub_arrange_8.remain) END AS summ " + " FROM dbo.sub_arrange AS sub_arrange_8 INNER JOIN " + " dbo.arrange_gol AS arrange_gol_8 ON sub_arrange_8.arrange_cod_f = arrange_gol_8.id INNER JOIN " + " dbo.flow_info AS flow_info_8 ON arrange_gol_8.id_gol = flow_info_8.id " + " WHERE (sub_arrange_8.datee BETWEEN '" + lstartprint.Text + "' AND '" + lfinishprint.Text + "') AND (arrange_gol_8.dim_arr_f = 5) AND (flow_info_8.customer_f_id = " + ddlcustomer.SelectedValue + ")) AS a5271, " + " (SELECT CASE WHEN SUM(sub_arrange_8.remain) IS NULL THEN 0 ELSE SUM(sub_arrange_8.remain) END AS summ " + " FROM dbo.sub_arrange AS sub_arrange_8 INNER JOIN " + " dbo.arrange_gol AS arrange_gol_8 ON sub_arrange_8.arrange_cod_f = arrange_gol_8.id INNER JOIN " + " dbo.flow_info AS flow_info_8 ON arrange_gol_8.id_gol = flow_info_8.id " + " WHERE (sub_arrange_8.datee BETWEEN '" + lstartprint.Text + "' AND '" + lfinishprint.Text + "') AND (arrange_gol_8.dim_arr_f = 6) AND (flow_info_8.customer_f_id = " + ddlcustomer.SelectedValue + ")) AS a6080, " + " (SELECT CASE WHEN SUM(sub_arrange_8.remain) IS NULL THEN 0 ELSE SUM(sub_arrange_8.remain) END AS summ " + " FROM dbo.sub_arrange AS sub_arrange_8 INNER JOIN " + " dbo.arrange_gol AS arrange_gol_8 ON sub_arrange_8.arrange_cod_f = arrange_gol_8.id INNER JOIN " + " dbo.flow_info AS flow_info_8 ON arrange_gol_8.id_gol = flow_info_8.id " + " WHERE (sub_arrange_8.datee BETWEEN '" + lstartprint.Text + "' AND '" + lfinishprint.Text + "') AND (arrange_gol_8.dim_arr_f = 7) AND (flow_info_8.customer_f_id = " + ddlcustomer.SelectedValue + ")) AS a6585, " + " (SELECT CASE WHEN SUM(sub_arrange_8.remain) IS NULL THEN 0 ELSE SUM(sub_arrange_8.remain) END AS summ FROM dbo.sub_arrange AS sub_arrange_8 INNER JOIN " + " dbo.arrange_gol AS arrange_gol_8 ON sub_arrange_8.arrange_cod_f = arrange_gol_8.id INNER JOIN dbo.flow_info AS flow_info_8 ON arrange_gol_8.id_gol = flow_info_8.id " + " WHERE (sub_arrange_8.datee BETWEEN '" + lstartprint.Text + "' AND '" + lfinishprint.Text + "') AND (arrange_gol_8.dim_arr_f = 8) AND (flow_info_8.customer_f_id = " + ddlcustomer.SelectedValue + ")) AS a7182, " + " (SELECT CASE WHEN SUM(sub_arrange_8.remain) IS NULL THEN 0 ELSE SUM(sub_arrange_8.remain) END AS summ FROM dbo.sub_arrange AS sub_arrange_8 INNER JOIN " + " dbo.arrange_gol AS arrange_gol_8 ON sub_arrange_8.arrange_cod_f = arrange_gol_8.id INNER JOIN dbo.flow_info AS flow_info_8 ON arrange_gol_8.id_gol = flow_info_8.id " + " WHERE (sub_arrange_8.datee BETWEEN '" + lstartprint.Text + "' AND '" + lfinishprint.Text + "') AND (arrange_gol_8.dim_arr_f = 9) AND (flow_info_8.customer_f_id = " + ddlcustomer.SelectedValue + ")) AS a4", cdf); insertha.ExecuteScalar(); SqlCommand inserthaa = new SqlCommand("INSERT INTO help_3 SELECT * FROM help_repcus_tarikh", cdf); inserthaa.ExecuteScalar(); SqlCommand updaaat = new SqlCommand("UPDATE [depogol].[dbo].[help_repcus_now] SET [a10070] = case when a10070 < 0 then 0 else a10070 end " + " ,[a2535] = case when a2535 < 0 then 0 else a2535 end ,[a4060] = case when a4060 < 0 then 0 else a4060 end ,[a5070] = case when a5070 < 0 then 0 else a5070 end " + " ,[a5271] = case when a5271 < 0 then 0 else a5271 end ,[a6080] = case when a6080 < 0 then 0 else a6080 end ,[a6585] = case when a6585 < 0 then 0 else a6585 end " + " ,[a7182] = case when a7182 < 0 then 0 else a7182 end ,[a4] = case when a4 < 0 then 0 else a4 end ", cdf); updaaat.ExecuteScalar(); /* * * SqlCommand updat = new SqlCommand("UPDATE hrt SET hrt.a10070 = case when hr2.a10070 < 0 then hrt.a10070 + hr2.a10070 else hrt.a10070 end " + * " ,hrt.a2535 = case when hr2.a2535 < 0 then hrt.a2535 + hr2.a2535 else hrt.a2535 + hr2.a2535 end " + * " ,hrt.a4060 = case when hr2.a4060 < 0 then hrt.a4060 + hr2.a4060 else hrt.a4060 + hr2.a4060 end " + * " ,hrt.a5070 = case when hr2.a5070 < 0 then hrt.a5070 + hr2.a5070 else hrt.a5070 + hr2.a5070 end " + * " ,hrt.a5271 = case when hr2.a5271 < 0 then hrt.a5271 + hr2.a5271 else hrt.a5271 + hr2.a5271 end " + * " ,hrt.a6080 = case when hr2.a6080 < 0 then hrt.a6080 + hr2.a6080 else hrt.a6080 + hr2.a6080 end " + * " ,hrt.a6585 = case when hr2.a6585 < 0 then hrt.a6585 + hr2.a6585 else hrt.a6585 + hr2.a6585 end " + * " ,hrt.a7182 = case when hr2.a7182 < 0 then hrt.a7182 + hr2.a7182 else hrt.a7182 + hr2.a7182 end " + * " ,hrt.a4 = case when hr2.a4 < 0 then hrt.a4 + hr2.a4 else hrt.a4 + hr2.a4 end " + * " from help_repcus_tarikh hrt , help_repcus2 hr2 ", cdf); * updat.ExecuteScalar(); * SqlCommand updata = new SqlCommand("UPDATE help_repcus2 SET a10070 = case when a10070 < 0 then 0 else a10070 end " + * " ,a2535 = case when a2535 < 0 then 0 else a2535 end ,a4060 = case when a4060 < 0 then 0 else a4060 end " + * " ,a5070 = case when a5070 < 0 then 0 else a5070 end ,a5271 = case when a5271 < 0 then 0 else a5271 end " + * " ,a6080 = case when a6080 < 0 then 0 else a6080 end ,a6585 = case when a6585 < 0 then 0 else a6585 end " + * " ,a7182 = case when a7182 < 0 then 0 else a7182 end ,a4 = case when a4 < 0 then 0 else a4 end " + * " from help_repcus2 ", cdf); * updata.ExecuteScalar(); */ /* SqlCommand inserttt = new SqlCommand("INSERT INTO [depogol].[dbo].[help_repcus2] "+ * " ([a10070] ,[a2535] ,[a4060] ,[a5070] ,[a5271] ,[a6080] ,[a6585] ,[a7182] ,[a4]) "+ * " SELECT (SELECT CASE WHEN subsum >= 0 THEN (amount + subsum) ELSE CASE WHEN (- subsum) > amount THEN amount ELSE amount - (- 1 * subsum) END END AS total "+ * " FROM (SELECT (SELECT a10070 " + * " FROM dbo.help_repcus_tarikh) AS amount,(SELECT CASE WHEN SUM(sub_arrange_8.remain) IS NULL THEN 0 ELSE SUM(sub_arrange_8.remain) END AS summ " + * " FROM dbo.sub_arrange AS sub_arrange_8 INNER JOIN dbo.arrange_gol AS arrange_gol_8 ON sub_arrange_8.arrange_cod_f = arrange_gol_8.id INNER JOIN " + * " dbo.flow_info AS flow_info_8 ON arrange_gol_8.id_gol = flow_info_8.id " + * " WHERE (sub_arrange_8.datee BETWEEN '"+lstartprint.Text+"' AND '"+lfinishprint.Text+"') AND (arrange_gol_8.dim_arr_f = 1) AND (flow_info_8.customer_f_id = "+ddlcustomer.SelectedValue+")) AS subsum) AS total) " + * " AS a10070, (SELECT CASE WHEN subsum >= 0 THEN (amount + subsum) ELSE CASE WHEN (- subsum) > amount THEN amount ELSE amount - (- 1 * subsum) END END AS total "+ * " FROM (SELECT (SELECT a2535 FROM dbo.help_repcus_tarikh AS help_repcus_tarikh_8) AS amount, " + * " (SELECT CASE WHEN SUM(sub_arrange_8.remain) IS NULL THEN 0 ELSE SUM(sub_arrange_8.remain) END AS summ FROM dbo.sub_arrange AS sub_arrange_8 INNER JOIN " + * " dbo.arrange_gol AS arrange_gol_8 ON sub_arrange_8.arrange_cod_f = arrange_gol_8.id INNER JOIN " + * " dbo.flow_info AS flow_info_8 ON arrange_gol_8.id_gol = flow_info_8.id " + * " WHERE (sub_arrange_8.datee BETWEEN '"+lstartprint.Text+"' AND '"+lfinishprint.Text+"') AND (arrange_gol_8.dim_arr_f = 2) AND (flow_info_8.customer_f_id = "+ddlcustomer.SelectedValue+")) AS subsum) AS total_8) " + * " AS a2535, (SELECT CASE WHEN subsum >= 0 THEN (amount + subsum) ELSE CASE WHEN (- subsum) > amount THEN amount ELSE amount - (- 1 * subsum) END END AS total "+ * " FROM (SELECT (SELECT a4060 " + * " FROM dbo.help_repcus_tarikh AS help_repcus_tarikh_7) AS amount, (SELECT CASE WHEN SUM(sub_arrange_8.remain) IS NULL THEN 0 ELSE SUM(sub_arrange_8.remain) END AS summ " + * " FROM dbo.sub_arrange AS sub_arrange_8 INNER JOIN dbo.arrange_gol AS arrange_gol_8 ON sub_arrange_8.arrange_cod_f = arrange_gol_8.id INNER JOIN " + * " dbo.flow_info AS flow_info_8 ON arrange_gol_8.id_gol = flow_info_8.id WHERE (sub_arrange_8.datee BETWEEN '"+lstartprint.Text+"' AND '"+lfinishprint.Text+"') AND (arrange_gol_8.dim_arr_f = 3) " + * " AND (flow_info_8.customer_f_id = "+ddlcustomer.SelectedValue+")) AS subsum) AS total_7) AS a4060, (SELECT CASE WHEN subsum >= 0 THEN (amount + subsum) ELSE CASE WHEN (- subsum) > amount THEN amount ELSE amount - (- 1 * subsum) END END AS total "+ * " FROM (SELECT (SELECT a5070 FROM dbo.help_repcus_tarikh AS help_repcus_tarikh_6) AS amount, " + * " (SELECT CASE WHEN SUM(sub_arrange_8.remain) IS NULL THEN 0 ELSE SUM(sub_arrange_8.remain) END AS summ " + * " FROM dbo.sub_arrange AS sub_arrange_8 INNER JOIN dbo.arrange_gol AS arrange_gol_8 ON sub_arrange_8.arrange_cod_f = arrange_gol_8.id INNER JOIN " + * " dbo.flow_info AS flow_info_8 ON arrange_gol_8.id_gol = flow_info_8.id " + * " WHERE (sub_arrange_8.datee BETWEEN '"+lstartprint.Text+"' AND '"+lfinishprint.Text+"') AND (arrange_gol_8.dim_arr_f = 4) AND (flow_info_8.customer_f_id = "+ddlcustomer.SelectedValue+")) AS subsum) AS total_6) " + * " AS a5070, (SELECT CASE WHEN subsum >= 0 THEN (amount + subsum) ELSE CASE WHEN (- subsum) > amount THEN amount ELSE amount - (- 1 * subsum) END END AS total "+ * " FROM (SELECT (SELECT a5271 " + * " FROM dbo.help_repcus_tarikh AS help_repcus_tarikh_5) AS amount, (SELECT CASE WHEN SUM(sub_arrange_8.remain) IS NULL THEN 0 ELSE SUM(sub_arrange_8.remain) END AS summ " + * " FROM dbo.sub_arrange AS sub_arrange_8 INNER JOIN dbo.arrange_gol AS arrange_gol_8 ON sub_arrange_8.arrange_cod_f = arrange_gol_8.id INNER JOIN " + * " dbo.flow_info AS flow_info_8 ON arrange_gol_8.id_gol = flow_info_8.id WHERE (sub_arrange_8.datee BETWEEN '"+lstartprint.Text+"' AND '"+lfinishprint.Text+"') AND (arrange_gol_8.dim_arr_f = 5) AND " + * " (flow_info_8.customer_f_id = "+ddlcustomer.SelectedValue+")) AS subsum) AS total_5) AS a5271, (SELECT CASE WHEN subsum >= 0 THEN (amount + subsum) ELSE CASE WHEN (- subsum) > amount THEN amount ELSE amount - (- 1 * subsum) END END AS total "+ * " FROM (SELECT (SELECT a6080 FROM dbo.help_repcus_tarikh AS help_repcus_tarikh_4) AS amount, " + * " (SELECT CASE WHEN SUM(sub_arrange_8.remain) IS NULL THEN 0 ELSE SUM(sub_arrange_8.remain) END AS summ " + * " FROM dbo.sub_arrange AS sub_arrange_8 INNER JOIN dbo.arrange_gol AS arrange_gol_8 ON sub_arrange_8.arrange_cod_f = arrange_gol_8.id INNER JOIN " + * " dbo.flow_info AS flow_info_8 ON arrange_gol_8.id_gol = flow_info_8.id " + * " WHERE (sub_arrange_8.datee BETWEEN '"+lstartprint.Text+"' AND '"+lfinishprint.Text+"') AND (arrange_gol_8.dim_arr_f = 6) AND (flow_info_8.customer_f_id = "+ddlcustomer.SelectedValue+")) AS subsum) AS total_4) " + * " AS a6080, (SELECT CASE WHEN subsum >= 0 THEN (amount + subsum) ELSE CASE WHEN (- subsum) > amount THEN amount ELSE amount - (- 1 * subsum) END END AS total "+ * " FROM (SELECT (SELECT a6585 FROM dbo.help_repcus_tarikh AS help_repcus_tarikh_3) AS amount, " + * " (SELECT CASE WHEN SUM(sub_arrange_3.remain) IS NULL THEN 0 ELSE SUM(sub_arrange_3.remain) END AS summ " + * " FROM dbo.sub_arrange AS sub_arrange_3 INNER JOIN dbo.arrange_gol AS arrange_gol_3 ON sub_arrange_3.arrange_cod_f = arrange_gol_3.id INNER JOIN " + * " dbo.flow_info AS flow_info_3 ON arrange_gol_3.id_gol = flow_info_3.id " + * " WHERE (sub_arrange_3.datee BETWEEN '"+lstartprint.Text+"' AND '"+lfinishprint.Text+"') AND (arrange_gol_3.dim_arr_f = 7) AND (flow_info_3.customer_f_id = "+ddlcustomer.SelectedValue+")) AS subsum) AS total_3) " + * " AS a6585, (SELECT CASE WHEN subsum >= 0 THEN (amount + subsum) ELSE CASE WHEN (- subsum) > amount THEN amount ELSE amount - (- 1 * subsum) END END AS total "+ * " FROM (SELECT (SELECT a7182 FROM dbo.help_repcus_tarikh AS help_repcus_tarikh_2) AS amount, " + * " (SELECT CASE WHEN SUM(sub_arrange_2.remain) IS NULL THEN 0 ELSE SUM(sub_arrange_2.remain) END AS summ " + * " FROM dbo.sub_arrange AS sub_arrange_2 INNER JOIN dbo.arrange_gol AS arrange_gol_2 ON sub_arrange_2.arrange_cod_f = arrange_gol_2.id INNER JOIN " + * " dbo.flow_info AS flow_info_2 ON arrange_gol_2.id_gol = flow_info_2.id " + * " WHERE (sub_arrange_2.datee BETWEEN '"+lstartprint.Text+"' AND '"+lfinishprint.Text+"') AND (arrange_gol_2.dim_arr_f = 8) AND (flow_info_2.customer_f_id = "+ddlcustomer.SelectedValue+")) AS subsum) AS total_2) " + * " AS a7182,(SELECT CASE WHEN subsum >= 0 THEN (amount + subsum) ELSE CASE WHEN (- subsum) > amount THEN amount ELSE amount - (- 1 * subsum) END END AS total "+ * " FROM (SELECT (SELECT a4 " + * " FROM dbo.help_repcus_tarikh AS help_repcus_tarikh_1) AS amount,(SELECT CASE WHEN SUM(sub_arrange_1.remain) IS NULL THEN 0 ELSE SUM(sub_arrange_1.remain) END AS summ " + * " FROM dbo.sub_arrange AS sub_arrange_1 INNER JOIN dbo.arrange_gol AS arrange_gol_1 ON sub_arrange_1.arrange_cod_f = arrange_gol_1.id INNER JOIN " + * " dbo.flow_info AS flow_info_1 ON arrange_gol_1.id_gol = flow_info_1.id " + * " WHERE (sub_arrange_1.datee BETWEEN '"+lstartprint.Text+"' AND '"+lfinishprint.Text+"') AND (arrange_gol_1.dim_arr_f = 9) AND (flow_info_1.customer_f_id = "+ddlcustomer.SelectedValue+")) AS subsum) AS total_1) AS a4", cdf); * inserttt.ExecuteScalar();*/ cdf.Close(); SqlDataSource1.DataBind(); SqlDataSource2.DataBind(); SqlDataSource3.DataBind(); gridrepcust.DataBind(); gridrepcusttarikh.DataBind(); staynow.DataBind(); gridrepcust.Visible = true; lcustomer.Text = Convert.ToString(ddlcustomer.SelectedItem); lcustomer2.Text = lcustomer.Text; lcustomer3.Text = lcustomer.Text; cdf.Open(); SqlCommand del6 = new SqlCommand("delete from help_rep_cus", cdf); del6.ExecuteScalar(); SqlCommand del7 = new SqlCommand("delete from help_repcus_tarikh", cdf); del7.ExecuteScalar(); SqlCommand del8 = new SqlCommand("delete from help_repcus2", cdf); del8.ExecuteScalar(); SqlCommand del9 = new SqlCommand("delete from help_repcus_now", cdf); del9.ExecuteScalar(); SqlCommand del10 = new SqlCommand("delete from help_3", cdf); del10.ExecuteScalar(); cdf.Close(); }
protected void serch_Click1(object sender, EventArgs e) { cdf.Open(); SqlCommand dele = new SqlCommand("delete from help_rep_cus", cdf); dele.ExecuteScalar(); cdf.Close(); yearst = yerstart.SelectedValue; mounthst = monstart.SelectedValue; dayst = daystart.SelectedValue; datestart = yearst + "/" + mounthst + "/" + dayst; lstart.Text = datestart; lstartprint.Text = lstart.Text; yearfin = yerfin.SelectedValue; mounthfin = monfin.SelectedValue; dayfini = dayfin.SelectedValue; datefinish = yearfin + "/" + mounthfin + "/" + dayfini; lfinish.Text = datefinish; lfinishprint.Text = lfinish.Text; SqlDataSource1.DataBind(); gridrepcust.DataBind(); cdf.Open(); SqlDataSource10.SelectCommand = "SELECT TOP (100) PERCENT flow_info.nam_f, flow_info.cod_f, flow_info.date_first_come, flow_info.customer_f, SUM(arrange_gol.count_in_barg) AS input, SUM(arrange_gol.remain_barg) AS remain, flow_info.id, flow_info.color_f, flow_info.type_color, flow_info.typ_f FROM flow_info INNER JOIN arrange_gol ON flow_info.id = arrange_gol.id_gol WHERE (flow_info.date_first_come BETWEEN '" + lstart.Text + "' AND '" + lfinish.Text + "') AND (flow_info.customer_f_id = " + ddlcustomer.SelectedValue + ") GROUP BY flow_info.nam_f, flow_info.cod_f, flow_info.date_first_come, flow_info.customer_f, flow_info.id, flow_info.color_f, flow_info.type_color, flow_info.typ_f ORDER BY flow_info.date_first_come,flow_info.nam_f"; dv = (System.Data.DataView)SqlDataSource10.Select(DataSourceSelectArguments.Empty); count = dv.Count; if (dv.Count != 0) { for (int i = 0; i < count; i++) { idgol = Convert.ToInt32(dv.ToTable().Rows[i]["id"]); cdf.Close(); cdf.Open(); SqlCommand selectid = new SqlCommand("SELECT (SELECT nam_f AS namf FROM dbo.flow_info AS flow_info_10 WHERE(date_first_come BETWEEN '" + lstart.Text + "' AND '" + lfinish.Text + "') AND (customer_f_id = " + ddlcustomer.SelectedValue + ") AND (id = " + idgol + ")) AS 'nam_gol' , (SELECT color_f AS color FROM dbo.flow_info WHERE (date_first_come BETWEEN '" + lstart.Text + "' AND '" + lfinish.Text + "') AND (customer_f_id = " + ddlcustomer.SelectedValue + ") AND (id = " + idgol + ")) AS 'color_gol',(SELECT typ_f AS AStype FROM dbo.flow_info AS flow_info_12 WHERE (date_first_come BETWEEN '" + lstart.Text + "' AND '" + lfinish.Text + "') AND (customer_f_id = " + ddlcustomer.SelectedValue + ") AND (id = " + idgol + ")) AS 'type_gol',(SELECT date_first_come AS come FROM dbo.flow_info AS flow_info_11 WHERE (date_first_come BETWEEN '" + lstart.Text + "' AND '" + lfinish.Text + "') AND (customer_f_id = " + ddlcustomer.SelectedValue + ") AND (id = " + idgol + ")) AS 'first_come',(SELECT case when SUM(remain_barg)>0 then SUM(remain_barg) else 0 end AS remain FROM dbo.arrange_gol WHERE (id_gol = " + idgol + ")) AS 'remain_barg',(SELECT case when SUM(arrange_gol_9.remain_barg)>0 then SUM(arrange_gol_9.remain_barg) else 0 end AS Expr1 FROM dbo.flow_info AS flow_info_9 INNER JOIN dbo.arrange_gol AS arrange_gol_9 ON flow_info_9.id = arrange_gol_9.id_gol WHERE (flow_info_9.date_first_come BETWEEN '" + lstart.Text + "' AND '" + lfinish.Text + "') AND (flow_info_9.customer_f_id = " + ddlcustomer.SelectedValue + ") AND (arrange_gol_9.dim_arr_f = 1) AND (flow_info_9.id = " + idgol + ")) AS '100*70',(SELECT case when SUM(arrange_gol_1.remain_barg)>0 then SUM(arrange_gol_1.remain_barg) else 0 end AS Expr1 FROM dbo.flow_info AS flow_info_1 INNER JOIN dbo.arrange_gol AS arrange_gol_1 ON flow_info_1.id = arrange_gol_1.id_gol WHERE (flow_info_1.date_first_come BETWEEN '" + lstart.Text + "' AND '" + lfinish.Text + "') AND (flow_info_1.customer_f_id = " + ddlcustomer.SelectedValue + ") AND (arrange_gol_1.dim_arr_f = 2) AND (flow_info_1.id = " + idgol + ")) AS '25*35', (SELECT case when SUM(arrange_gol_2.remain_barg)>0 then SUM(arrange_gol_2.remain_barg) else 0 end AS Expr1 FROM dbo.flow_info AS flow_info_2 INNER JOIN dbo.arrange_gol AS arrange_gol_2 ON flow_info_2.id = arrange_gol_2.id_gol WHERE (flow_info_2.date_first_come BETWEEN '" + lstart.Text + "' AND '" + lfinish.Text + "') AND (flow_info_2.customer_f_id = " + ddlcustomer.SelectedValue + ") AND (arrange_gol_2.dim_arr_f = 3) AND (flow_info_2.id = " + idgol + ")) AS '40*60',(SELECT case when SUM(arrange_gol_8.remain_barg)>0 then SUM(arrange_gol_8.remain_barg) else 0 end AS Expr1 FROM dbo.flow_info AS flow_info_8 INNER JOIN dbo.arrange_gol AS arrange_gol_8 ON flow_info_8.id = arrange_gol_8.id_gol WHERE (flow_info_8.date_first_come BETWEEN '" + lstart.Text + "' AND '" + lfinish.Text + "') AND (flow_info_8.customer_f_id = " + ddlcustomer.SelectedValue + ") AND (arrange_gol_8.dim_arr_f = 4) AND (flow_info_8.id = " + idgol + ")) AS '50*70', (SELECT case when SUM(arrange_gol_7.remain_barg)>0 then SUM(arrange_gol_7.remain_barg) else 0 end AS Expr1 FROM dbo.flow_info AS flow_info_7 INNER JOIN dbo.arrange_gol AS arrange_gol_7 ON flow_info_7.id = arrange_gol_7.id_gol WHERE (flow_info_7.date_first_come BETWEEN '" + lstart.Text + "' AND '" + lfinish.Text + "') AND (flow_info_7.customer_f_id = " + ddlcustomer.SelectedValue + ") AND (arrange_gol_7.dim_arr_f = 5) AND (flow_info_7.id = " + idgol + ")) AS '52*71',(SELECT case when SUM(arrange_gol_6.remain_barg)>0 then SUM(arrange_gol_6.remain_barg) else 0 end AS Expr1 FROM dbo.flow_info AS flow_info_6 INNER JOIN dbo.arrange_gol AS arrange_gol_6 ON flow_info_6.id = arrange_gol_6.id_gol WHERE (flow_info_6.date_first_come BETWEEN '" + lstart.Text + "' AND '" + lfinish.Text + "') AND (flow_info_6.customer_f_id = " + ddlcustomer.SelectedValue + ") AND (arrange_gol_6.dim_arr_f = 6) AND (flow_info_6.id = " + idgol + ")) AS '60*80',(SELECT case when SUM(arrange_gol_5.remain_barg)>0 then SUM(arrange_gol_5.remain_barg) else 0 end AS Expr1 FROM dbo.flow_info AS flow_info_5 INNER JOIN dbo.arrange_gol AS arrange_gol_5 ON flow_info_5.id = arrange_gol_5.id_gol WHERE (flow_info_5.date_first_come BETWEEN '" + lstart.Text + "' AND '" + lfinish.Text + "') AND (flow_info_5.customer_f_id = " + ddlcustomer.SelectedValue + ") AND (arrange_gol_5.dim_arr_f = 7) AND (flow_info_5.id = " + idgol + ")) AS '65*85',(SELECT case when SUM(arrange_gol_4.remain_barg)>0 then SUM(arrange_gol_4.remain_barg) else 0 end AS Expr1 FROM dbo.flow_info AS flow_info_4 INNER JOIN dbo.arrange_gol AS arrange_gol_4 ON flow_info_4.id = arrange_gol_4.id_gol WHERE (flow_info_4.date_first_come BETWEEN '" + lstart.Text + "' AND '" + lfinish.Text + "') AND (flow_info_4.customer_f_id = " + ddlcustomer.SelectedValue + ") AND (arrange_gol_4.dim_arr_f = 8) AND (flow_info_4.id = " + idgol + ")) AS '71*82',(SELECT case when SUM(arrange_gol_3.remain_barg)>0 then SUM(arrange_gol_3.remain_barg) else 0 end AS Expr1 FROM dbo.flow_info AS flow_info_3 INNER JOIN dbo.arrange_gol AS arrange_gol_3 ON flow_info_3.id = arrange_gol_3.id_gol WHERE (flow_info_3.date_first_come BETWEEN '" + lstart.Text + "' AND '" + lfinish.Text + "') AND (flow_info_3.customer_f_id = " + ddlcustomer.SelectedValue + ") AND (arrange_gol_3.dim_arr_f = 9) AND (flow_info_3.id = " + idgol + ")) AS 'A4' ", cdf); SqlDataReader dr = selectid.ExecuteReader(); if (dr.Read()) { if (Convert.ToString(dr["nam_gol"]) != null) { namgol = Convert.ToString(dr["nam_gol"]); } if (Convert.ToString(dr["color_gol"]) != null) { colorgol = Convert.ToString(dr["color_gol"]); } if (Convert.ToString(dr["type_gol"]) != null) { typegol = Convert.ToString(dr["type_gol"]); } if (Convert.ToString(dr["first_come"]) != null) { firscom = Convert.ToString(dr["first_come"]); } if (Convert.ToString(dr["remain_barg"]) != null) { remain = Convert.ToString(dr["remain_barg"]); } a10070 = Convert.ToInt32(dr["100*70"]); a2535 = Convert.ToInt32(dr["25*35"]); a4060 = Convert.ToInt32(dr["40*60"]); a5070 = Convert.ToInt32(dr["50*70"]); a5271 = Convert.ToInt32(dr["52*71"]); a6080 = Convert.ToInt32(dr["60*80"]); a6585 = Convert.ToInt32(dr["65*85"]); a7182 = Convert.ToInt32(dr["71*82"]); a4 = Convert.ToInt32(dr["A4"]); } cdf.Close(); cdf.Open(); SqlCommand insertt = new SqlCommand("insert into help_rep_cus(idgol,namgol,colorgol,typegol,firstcom,remain,a10070,a2535,a4060,a5070,a5271,a6080,a6585,a7182,a4) values(" + idgol + ",N'" + namgol + "',N'" + colorgol + "',N'" + typegol + "',N'" + firscom + "'," + remain + "," + a10070 + "," + a2535 + "," + a4060 + "," + a5070 + "," + a5271 + "," + a6080 + "," + a6585 + "," + a7182 + "," + a4 + ")", cdf); insertt.ExecuteScalar(); cdf.Close(); } } SqlDataSource1.DataBind(); gridrepcust.DataBind(); gridrepcust.Visible = true; lcustomer.Text = Convert.ToString(ddlcustomer.SelectedItem); }
protected void Button1_Click(object sender, EventArgs e) { DataView dv = (DataView)SqlDataSource1.Select(DataSourceSelectArguments.Empty); DataRowView drv = dv[0]; Table1.Rows[1].Cells[0].Text = drv["BUGS"].ToString(); DataView dv1 = (DataView)SqlDataSource2.Select(DataSourceSelectArguments.Empty); DataRowView drv1 = dv1[0]; Table1.Rows[1].Cells[1].Text = drv1["BUGS"].ToString(); DataView dv2 = (DataView)SqlDataSource3.Select(DataSourceSelectArguments.Empty); DataRowView drv2 = dv2[0]; Table1.Rows[1].Cells[2].Text = drv2["BUGS"].ToString(); DataView dv3 = (DataView)SqlDataSource4.Select(DataSourceSelectArguments.Empty); DataRowView drv3 = dv3[0]; Table1.Rows[1].Cells[3].Text = drv3["BUGS"].ToString(); DataView dv5 = (DataView)SqlDataSource5.Select(DataSourceSelectArguments.Empty); DataRowView drv5 = dv5[0]; Table2.Rows[1].Cells[0].Text = drv5["BUGS"].ToString(); DataView dv6 = (DataView)SqlDataSource6.Select(DataSourceSelectArguments.Empty); DataRowView drv6 = dv6[0]; Table2.Rows[1].Cells[1].Text = drv6["BUGS"].ToString(); DataView dv7 = (DataView)SqlDataSource7.Select(DataSourceSelectArguments.Empty); DataRowView drv7 = dv7[0]; Table2.Rows[1].Cells[2].Text = drv7["BUGS"].ToString(); DataView dv8 = (DataView)SqlDataSource8.Select(DataSourceSelectArguments.Empty); DataRowView drv8 = dv8[0]; Table2.Rows[1].Cells[3].Text = drv8["BUGS"].ToString(); DataView dv9 = (DataView)SqlDataSource9.Select(DataSourceSelectArguments.Empty); DataRowView drv9 = dv9[0]; Table3.Rows[1].Cells[0].Text = drv9["BUGS"].ToString(); DataView dv10 = (DataView)SqlDataSource10.Select(DataSourceSelectArguments.Empty); DataRowView drv10 = dv10[0]; Table3.Rows[1].Cells[1].Text = drv10["BUGS"].ToString(); DataView dv11 = (DataView)SqlDataSource11.Select(DataSourceSelectArguments.Empty); DataRowView drv11 = dv11[0]; Table3.Rows[1].Cells[2].Text = drv11["BUGS"].ToString(); DataView dv12 = (DataView)SqlDataSource12.Select(DataSourceSelectArguments.Empty); DataRowView drv12 = dv12[0]; Table3.Rows[1].Cells[3].Text = drv12["BUGS"].ToString(); }
protected void Page_Load(object sender, EventArgs e) { String y = "select DISTINCT [Year] from SchoolTable"; SqlDataSource9.SelectCommand = y; DataView dv = (DataView)SqlDataSource9.Select(new DataSourceSelectArguments()); for (int i = 0; i < dv.Count; i++) { if (dv[i][0] != DBNull.Value) { String year = (String)dv[i][0]; String sql1 = "select count(UnitName) from SchoolTable where UnitType='Cub' AND Year='" + year + "' AND Status='Activated'"; SqlDataSource1.SelectCommand = sql1; DataView dv1 = (DataView)SqlDataSource1.Select(new DataSourceSelectArguments()); int cub = (int)dv1[0][0]; String sql2 = "select count(UnitName) from SchoolTable where UnitType='Scout' AND Year='" + year + "' AND Status='Activated'"; SqlDataSource2.SelectCommand = sql2; DataView dv2 = (DataView)SqlDataSource2.Select(new DataSourceSelectArguments()); int scout = (int)dv2[0][0]; String sql3 = "select count(UnitName) from SchoolTable where UnitType='Rover' AND Year='" + year + "' AND Status='Activated'"; SqlDataSource3.SelectCommand = sql3; DataView dv3 = (DataView)SqlDataSource3.Select(new DataSourceSelectArguments()); int rover = (int)dv3[0][0]; String sql4 = "select count(UnitName) from SchoolTable where UnitType='Bulbul' AND Year='" + year + "' AND Status='Activated'"; SqlDataSource4.SelectCommand = sql4; DataView dv4 = (DataView)SqlDataSource4.Select(new DataSourceSelectArguments()); int bulbul = (int)dv4[0][0]; String sql5 = "select count(UnitName) from SchoolTable where UnitType='Guide' AND Year='" + year + "' AND Status='Activated'"; SqlDataSource5.SelectCommand = sql5; DataView dv5 = (DataView)SqlDataSource5.Select(new DataSourceSelectArguments()); int guide = (int)dv5[0][0]; String sql6 = "select count(UnitName) from SchoolTable where UnitType='Ranger' AND Year='" + year + "' AND Status='Activated'"; SqlDataSource6.SelectCommand = sql6; DataView dv6 = (DataView)SqlDataSource6.Select(new DataSourceSelectArguments()); int ranger = (int)dv6[0][0]; String sql7 = "select count(UnitName) from SchoolTable where UnitType='Bunny' AND Year='" + year + "' AND Status='Activated'"; SqlDataSource7.SelectCommand = sql7; DataView dv7 = (DataView)SqlDataSource7.Select(new DataSourceSelectArguments()); int bunny = (int)dv7[0][0]; int total = cub + scout + rover + bulbul + guide + ranger + bunny; String sql9 = "select count(StudFullName) from StudentTable where UnitType='Cub' AND Year='" + year + "' AND Status='Activated'"; SqlDataSource9.SelectCommand = sql9; DataView dv9 = (DataView)SqlDataSource9.Select(new DataSourceSelectArguments()); int cub1 = (int)dv9[0][0]; String sql10 = "select count(StudFullName) from StudentTable where UnitType='Scout' AND Year='" + year + "' AND Status='Activated'"; SqlDataSource10.SelectCommand = sql10; DataView dv10 = (DataView)SqlDataSource10.Select(new DataSourceSelectArguments()); int scout1 = (int)dv10[0][0]; String sql11 = "select count(StudFullName) from StudentTable where UnitType='Rover' AND Year='" + year + "' AND Status='Activated'"; SqlDataSource11.SelectCommand = sql11; DataView dv11 = (DataView)SqlDataSource11.Select(new DataSourceSelectArguments()); int rover1 = (int)dv11[0][0]; String sql12 = "select count(StudFullName) from StudentTable where UnitType='Bulbul' AND Year='" + year + "' AND Status='Activated'"; SqlDataSource12.SelectCommand = sql12; DataView dv12 = (DataView)SqlDataSource12.Select(new DataSourceSelectArguments()); int bulbul1 = (int)dv12[0][0]; String sql13 = "select count(StudFullName) from StudentTable where UnitType='Guide' AND Year='" + year + "' AND Status='Activated'"; SqlDataSource13.SelectCommand = sql13; DataView dv13 = (DataView)SqlDataSource13.Select(new DataSourceSelectArguments()); int guide1 = (int)dv13[0][0]; String sql14 = "select count(StudFullName) from StudentTable where UnitType='Ranger' AND Year='" + year + "' AND Status='Activated'"; SqlDataSource14.SelectCommand = sql14; DataView dv14 = (DataView)SqlDataSource14.Select(new DataSourceSelectArguments()); int ranger1 = (int)dv14[0][0]; String sql15 = "select count(StudFullName) from StudentTable where UnitType='Bunny' AND Year='" + year + "' AND Status='Activated'"; SqlDataSource15.SelectCommand = sql15; DataView dv15 = (DataView)SqlDataSource15.Select(new DataSourceSelectArguments()); int bunny1 = (int)dv7[0][0]; int total1 = cub1 + scout1 + rover1 + bulbul1 + guide1 + ranger1 + bunny1; String sql16 = "select count(UnitName) from SchoolTable where Year='" + year + "' AND Status='Activated'"; SqlDataSource16.SelectCommand = sql16; DataView dv16 = (DataView)SqlDataSource16.Select(new DataSourceSelectArguments()); int unit = (int)dv16[0][0]; String sql17 = "select SUM(khTotalContri) from SchoolTable where Year='" + year + "'"; SqlDataSource17.SelectCommand = sql17; DataView dv17 = (DataView)SqlDataSource17.Select(new DataSourceSelectArguments()); int amt; if (dv17[0][0] != DBNull.Value) { amt = (int)dv17[0][0]; } else { amt = 0; } String sql18 = "select count(SchoolName) from SchoolTable where Year='" + year + "' AND Status='Activated'"; SqlDataSource18.SelectCommand = sql18; DataView dv18 = (DataView)SqlDataSource18.Select(new DataSourceSelectArguments()); int sch = (int)dv18[0][0]; String sql19 = "select count(SchoolName) from SchoolTable where SchoolType='PrimarySchool' AND Year='" + year + "' AND Status='Activated'"; SqlDataSource19.SelectCommand = sql19; DataView dv19 = (DataView)SqlDataSource19.Select(new DataSourceSelectArguments()); int sch1 = (int)dv19[0][0]; String sql20 = "select count(SchoolName) from SchoolTable where SchoolType='SecondarySchool' AND Year='" + year + "' AND Status='Activated'"; SqlDataSource20.SelectCommand = sql20; DataView dv20 = (DataView)SqlDataSource20.Select(new DataSourceSelectArguments()); int sch2 = (int)dv20[0][0]; int total3 = sch1 + sch2; String update = "Select * from StatisticalData where Year='" + year + "'"; SqlDataSource21.SelectCommand = update; DataView dv21 = (DataView)SqlDataSource21.Select(new DataSourceSelectArguments()); if (dv21.Count != 0) { String query = "update StatisticalData set Cub=" + cub + ", Scout=" + scout + ", Rover=" + rover + ", Bulbul=" + bulbul + ", Guide=" + guide + ", Ranger=" + ranger + ", Bunny=" + bunny + ", Total=" + total + ", Cub1=" + cub1 + ", Scout1=" + scout1 + ", Rover1=" + rover1 + ", Bulbul1=" + bulbul1 + ", Guide1=" + guide1 + ", Ranger1=" + ranger1 + ", Bunny1=" + bunny1 + ", Total1=" + total1 + ", TotalUnits=" + unit + ", CollectedFees=" + amt + ", TotalParticipatedSchools=" + sch + ", PrimarySchool=" + sch1 + ", SecondarySchool=" + sch2 + ", TotalMembers=" + total3 + " where Year='" + year + "'"; SqlDataSource22.UpdateCommand = query; int n = SqlDataSource22.Update(); } else { String insert1 = "insert into StatisticalData (Year, Cub, Scout, Rover, Bulbul, Guide, Ranger, Bunny, Total, Cub1, Scout1, Rover1, Bulbul1, Guide1, Ranger1, Bunny1, Total1, TotalUnits, CollectedFees, TotalParticipatedSchools, PrimarySchool, SecondarySchool, TotalMembers) Values('" + year + "'," + cub + "," + scout + "," + rover + "," + bulbul + "," + guide + "," + ranger + "," + bunny + "," + total + "," + cub1 + "," + scout1 + "," + rover1 + "," + bulbul1 + "," + guide1 + "," + ranger1 + "," + bunny1 + "," + total1 + "," + unit + "," + amt + "," + sch + "," + sch1 + "," + sch2 + "," + total3 + ")"; SqlDataSource8.InsertCommand = insert1; int n1 = SqlDataSource8.Insert(); } } } Server.Transfer("Statistical2.aspx"); }