private void load_button_Click(object sender, EventArgs e) { LoadReportToDB.LoadReport(textBox1, textBox2, textBox3, textBox4, textBox5, textBox6, textBox7, textBox8, textBox9, textBox10, textBox11, textBox12, textBox13, textBox14, textBox15, textBox16, textBox17, textBox18, comboBox1, false, 0); }
public static void GetOutputTable(TextBox textBox20) { string date = ""; date = textBox20.Text; SqlCommand sqlcom = new SqlCommand(); List <int> numbers = new List <int> { }; //Делаем запрос всех Id с указанной датой SqlConnection conn = DBUtils.GetDBConnection(); conn.Open(); try { string sql = "Select Id from InputTable where date = " + date; System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand(); cmd.Connection = conn; cmd.CommandText = sql; using (DbDataReader reader = cmd.ExecuteReader()) { if (reader.HasRows) { while (reader.Read()) { int colIdIndex = reader.GetOrdinal("Id"); int colId = reader.GetInt32(colIdIndex); numbers.Add(colId); } } } } catch (Exception e) { MessageBox.Show("Error: " + e); MessageBox.Show(e.StackTrace); } finally { conn.Close(); conn.Dispose(); } Console.Read(); //Заполнение выходной таблицы данными decimal dec1 = 0, dec2 = 0, dec3 = 0; int n, j = 0; string s = ""; int[] id = numbers.ToArray <int>(); SqlCommand.Query("Delete from OutputTable where Id != 0"); SqlCommand.Query("DBCC CHECKIDENT('OutputTable', RESEED, " + 0 + ")"); for (int i = 0; i < id.Length; i++) { LoadReportToDB.LoadReport(textBox20, textBox20, textBox20, textBox20, textBox20, textBox20, textBox20, textBox20, textBox20, textBox20, textBox20, textBox20, textBox20, textBox20, textBox20, textBox20, textBox20, textBox20, comboBox2, true, id[i]); SqlCommand.Query("Insert into OutputTable(OrderNum) values (" + (i + 1) + ")"); n = sqlcom.SelectInt("OrderNum", "InputTable", "Id = " + id[i]); SqlCommand.Query("Update OutputTable set OrderNum = " + n + " where Id = " + (i + 1)); n = sqlcom.SelectInt("OtdelId", "InputTable", "Id = " + id[i]); s = sqlcom.SelectStr("Name", "Otdel", "Id = " + n); SqlCommand.Query("Update OutputTable set OtdelId = '" + s + "' where Id = " + (i + 1)); s = sqlcom.SelectStr("WorkName", "InputTable", "Id = " + id[i]); SqlCommand.Query("Update OutputTable set WorkName = '" + s + "' where Id = " + (i + 1)); n = sqlcom.SelectInt("ObFact", "InputTable", "Id = " + id[i]); dec1 = (decimal)n; SqlCommand.Query("Update OutputTable set Vol = " + dec1 + " where Id = " + (i + 1)); n = sqlcom.SelectInt("Tiraj", "InputTable", "Id = " + id[i]); dec1 = (decimal)n; SqlCommand.Query("Update OutputTable set Tiraj = " + dec1 + " where Id = " + (i + 1)); dec1 = sqlcom.SelectSum("Sum", "PrintOnOfset"); dec2 = sqlcom.SelectSum("Cost", "TirajOnColPrint"); dec3 = dec1 + dec2; dec1 = sqlcom.SelectSum("Cost", "TirajOnKseroks"); dec2 = sqlcom.SelectSum("Cost", "TirajOnRizograph"); dec3 = dec3 + dec1 + dec2; s = dec3.ToString(); s = s.Replace(",", "."); SqlCommand.Query("Update OutputTable set CostofDoneWork = " + s + " where Id = " + (i + 1)); dec1 = sqlcom.Select("Sum", "PaperExpense", "Id = 1"); s = dec1.ToString(); s = s.Replace(",", "."); SqlCommand.Query("Update OutputTable set PaperOfset65 = " + s + " where Id = " + (i + 1)); dec1 = sqlcom.Select("Sum", "PaperExpense", "Id = 2"); s = dec1.ToString(); s = s.Replace(",", "."); SqlCommand.Query("Update OutputTable set PaperOfset80 = " + s + " where Id = " + (i + 1)); dec1 = sqlcom.Select("Sum", "PaperExpense", "Id = 3"); s = dec1.ToString(); s = s.Replace(",", "."); SqlCommand.Query("Update OutputTable set PaperOfset120 = " + s + " where Id = " + (i + 1)); dec1 = sqlcom.Select("Sum", "PaperExpense", "Id = 4"); s = dec1.ToString(); s = s.Replace(",", "."); SqlCommand.Query("Update OutputTable set PaperMag48 = " + s + " where Id = " + (i + 1)); dec1 = sqlcom.Select("AmountPages", "PaperExpense", "Id = 5"); s = dec1.ToString(); s = s.Replace(",", "."); SqlCommand.Query("Update OutputTable set PaperMel200 = " + s + " where Id = " + (i + 1)); dec1 = sqlcom.Select("AmountPages", "PaperExpense", "Id = 6"); s = dec1.ToString(); s = s.Replace(",", "."); SqlCommand.Query("Update OutputTable set PaperMel250 = " + s + " where Id = " + (i + 1)); dec1 = sqlcom.Select("AmountPages", "PaperExpense", "Id = 7"); s = dec1.ToString(); s = s.Replace(",", "."); SqlCommand.Query("Update OutputTable set PaperMel115 = " + s + " where Id = " + (i + 1)); dec1 = sqlcom.Select("AmountPages", "PaperExpense", "Id = 8"); s = dec1.ToString(); s = s.Replace(",", "."); SqlCommand.Query("Update OutputTable set PaperMelKart = " + s + " where Id = " + (i + 1)); dec1 = sqlcom.Select("AmountPages", "PaperExpense", "Id = 9"); s = dec1.ToString(); s = s.Replace(",", "."); SqlCommand.Query("Update OutputTable set ColorPaper = " + s + " where Id = " + (i + 1)); j = i; } //Суммирование расходов и вывод в ИТОГ SqlCommand.Query("Insert into OutputTable(WorkName) values ('Итого:')"); dec1 = sqlcom.SelectSum("CostOfDoneWork", "OutputTable"); s = dec1.ToString(); s = s.Replace(",", "."); SqlCommand.Query("Update OutputTable set CostofDoneWork = " + s + " where Id = " + (j + 2)); dec1 = sqlcom.SelectSum("PaperOfset65", "OutputTable"); s = dec1.ToString(); s = s.Replace(",", "."); SqlCommand.Query("Update OutputTable set PaperOfset65 = " + s + " where Id = " + (j + 2)); dec1 = sqlcom.SelectSum("PaperOfset80", "OutputTable"); s = dec1.ToString(); s = s.Replace(",", "."); SqlCommand.Query("Update OutputTable set PaperOfset80 = " + s + " where Id = " + (j + 2)); dec1 = sqlcom.SelectSum("PaperOfset120", "OutputTable"); s = dec1.ToString(); s = s.Replace(",", "."); SqlCommand.Query("Update OutputTable set PaperOfset120 = " + s + " where Id = " + (j + 2)); dec1 = sqlcom.SelectSum("PaperMag48", "OutputTable"); s = dec1.ToString(); s = s.Replace(",", "."); SqlCommand.Query("Update OutputTable set PaperMag48 = " + s + " where Id = " + (j + 2)); dec1 = sqlcom.SelectSum("PaperMel200", "OutputTable"); s = dec1.ToString(); s = s.Replace(",", "."); SqlCommand.Query("Update OutputTable set PaperMel200 = " + s + " where Id = " + (j + 2)); dec1 = sqlcom.SelectSum("PaperMel250", "OutputTable"); s = dec1.ToString(); s = s.Replace(",", "."); SqlCommand.Query("Update OutputTable set PaperMel250 = " + s + " where Id = " + (j + 2)); dec1 = sqlcom.SelectSum("PaperMel115", "OutputTable"); s = dec1.ToString(); s = s.Replace(",", "."); SqlCommand.Query("Update OutputTable set PaperMel115 = " + s + " where Id = " + (j + 2)); dec1 = sqlcom.SelectSum("PaperMelKart", "OutputTable"); s = dec1.ToString(); s = s.Replace(",", "."); SqlCommand.Query("Update OutputTable set PaperMelKart = " + s + " where Id = " + (j + 2)); dec1 = sqlcom.SelectSum("ColorPaper", "OutputTable"); s = dec1.ToString(); s = s.Replace(",", "."); SqlCommand.Query("Update OutputTable set ColorPaper = " + s + " where Id = " + (j + 2)); }