private void setExhibitListBox() { try { DataTable exhibitions = dataset.Exhibition; DataTable exhibits = dataset.Exhibit; EnumerableRowCollection <DataRow> query = from e_ex in (dataset.Exhibit_Exhibition as DataTable).AsEnumerable() where e_ex.Field <Int32>("ExhibitionID") == (int)exhibitionListBox.SelectedValue select e_ex; string getExhibitsFilter = "[ID] IN ("; for (int i = 0; i < query.Count(); i++) { getExhibitsFilter += "'" + query.ElementAt(i).Field <Int32>("ExhibitID") + "'"; if (i + 1 != query.Count()) { getExhibitsFilter += ","; } } getExhibitsFilter += ")"; DataView exhibitView = new DataView(dataset.Exhibit); exhibitView.RowFilter = getExhibitsFilter; exhibitListBox.DataSource = exhibitView; exhibitListBox.DisplayMember = "Name"; exhibitListBox.ValueMember = "ID"; } catch { } }
}//end default constructor public JobModel(EnumerableRowCollection<DataRow> job) { Id = job.ElementAt(0).Field<int>("Job_Id"); Company = job.ElementAt(0).Field<string>("Name"); City = job.ElementAt(0).Field<string>("City"); State = job.ElementAt(0).Field<string>("State"); JobTitle = job.ElementAt(0).Field<string>("Title"); StartDate = job.ElementAt(0).Field<DateTime>("StartDate"); var endDate = job.ElementAt(0)["EndDate"]; EndDate = (endDate.GetType() != typeof(DBNull) ? job.ElementAt(0).Field<DateTime>("EndDate") : new DateTime()); JobDescription = job.Select(x => x.Field<string>("Description")).ToList<string>(); }//end One argument constructor
private string GetColumnDataType(string columnName) { EnumerableRowCollection <DataRow> columnInfo = tableSchema.AsEnumerable() .Where(row => row.Field <string>("COLUMN_NAME") .Equals(columnName)); if (columnInfo.Count() == 1) { return((string)columnInfo.ElementAt(0)["data_type"]); } else { return(null); } }
//only removes if the path is in the database public void removeTrack(string path) { bool exists = false; using (SqlConnection sqlConnection1 = new SqlConnection(dbConnectionStr)) using (SqlDataAdapter adapter = new SqlDataAdapter("SELECT * FROM Song where full_path='" + path + "';", sqlConnection1)) { sqlConnection1.Open(); DataTable songTable = new DataTable(); adapter.Fill(songTable); EnumerableRowCollection <DataRow> dataRows = songTable.AsEnumerable(); for (int i = 0; i < dataRows.Count(); i++) { object[] row = dataRows.ElementAt(i).ItemArray; string ts = (string)row[1]; if (ts.Equals(path)) { exists = true; break; } } sqlConnection1.Close(); } if (exists) { string query = "DELETE FROM Song WHERE full_path='" + path + "';"; using (SqlConnection sqlConnection = new SqlConnection(dbConnectionStr)) using (SqlCommand sqlCmd = new SqlCommand(query, sqlConnection)) { sqlConnection.Open(); Console.WriteLine("rows affected:" + sqlCmd.ExecuteNonQuery() + " executed query:" + sqlCmd.CommandText); sqlConnection.Close(); } } else { //Console.WriteLine("already in db or file doesnt exist"); } }
public List <Track> getAllTracks() { List <Track> tracks = new List <Track>(); using (SqlConnection sqlConnection = new SqlConnection(dbConnectionStr)) using (SqlDataAdapter adapter = new SqlDataAdapter("SELECT * FROM Song", sqlConnection)) { sqlConnection.Open(); DataTable songTable = new DataTable(); adapter.Fill(songTable); EnumerableRowCollection <DataRow> dataRows = songTable.AsEnumerable(); for (int i = 0; i < dataRows.Count(); i++) { object[] row = dataRows.ElementAt(i).ItemArray; try { if (!File.Exists((string)row[1])) { throw new FileNotFoundException(); } Track t = new Track((string)row[1]); t.BPM = (double)row[2]; t.ZCR = (double)row[3]; t.SpectralIrregularity = (double)row[4]; tracks.Add(t); } catch (Exception e) { Console.WriteLine(e.Message); Console.WriteLine("DB ADAPTER REMOVING SONG:" + (string)row[1]); removeTrack((string)row[1]); } } sqlConnection.Close(); } return(tracks); }
public Track getTrack(string path) //returns null if track not found { path = strToSafeSqlFormat(path); using (SqlConnection sqlConnection = new SqlConnection(dbConnectionStr)) using (SqlDataAdapter adapter = new SqlDataAdapter("SELECT * FROM Song where full_path='" + path + "';", sqlConnection)) { sqlConnection.Open(); DataTable songTable = new DataTable(); adapter.Fill(songTable); EnumerableRowCollection <DataRow> dataRows = songTable.AsEnumerable(); for (int i = 0; i < dataRows.Count(); i++) { object[] row = dataRows.ElementAt(i).ItemArray; try { if (!File.Exists((string)row[1])) { throw new FileNotFoundException(); } Track t = new Track((string)row[1]); t.BPM = (double)row[2]; t.ZCR = (double)row[3]; t.SpectralIrregularity = (double)row[4]; return(t); } catch (Exception e) { Console.WriteLine("DB ADAPTER REMOVING from get track:" + (string)row[1]); removeTrack((string)row[1]); sqlConnection.Close(); return(null); } } sqlConnection.Close(); } return(null); }
private void btSelect_Click(object sender, EventArgs e) { if (decimal.Parse(tbMoney.Text.ToString()) == 0) { MessageBox.Show("Необходимо ввести сумму!", "Информирование", MessageBoxButtons.OK, MessageBoxIcon.Information); return; } decimal summa = decimal.Parse(tbMoney.Text.Trim()); DataTable dtHistory = Config.hCntMain.getHistoryOrderAndReturn(id_ServiceRecords); decimal maxSumma = decimal.Parse(dtHistory.Rows[0]["maxSumma"].ToString()); decimal balanceGet = decimal.Parse(dtHistory.Rows[0]["balanceGet"].ToString()); decimal balanceGetInValuta = decimal.Parse(dtHistory.Rows[0]["balanceGetInValuta"].ToString()); decimal balanceReturn = decimal.Parse(dtHistory.Rows[0]["balanceReturn"].ToString()); decimal debtReport = decimal.Parse(dtHistory.Rows[0]["debtReport"].ToString()); //decimal oldDebt = decimal.Parse(dtHistory.Rows[0]["oldDebt"].ToString()); // для ежемесячногй СЗ string valuta = dtHistory.Rows[0]["Valuta"].ToString(); decimal sumGet = decimal.Parse(dtHistory.Rows[0]["sumGet"].ToString()); decimal sumOrderGetInValuta = decimal.Parse(dtHistory.Rows[0]["sumOrderGetInValuta"].ToString()); int monthDateCreateReport = DateTime.Parse(dtHistory.Rows[0]["DateCreateReport"].ToString()).Month; int yearDateCreateReport = DateTime.Parse(dtHistory.Rows[0]["DateCreateReport"].ToString()).Year; bool isTodayMonthAbdYear = nowTime.Month.Equals(monthDateCreateReport) && nowTime.Year.Equals(yearDateCreateReport); //int typeSROnTime = (int)dtHistory.Rows[0]["typeSROnTime"]; if (idOrder != 0) { balanceGet += oldSumma; balanceReturn += oldSumma; } if (summa > maxSumma && (type == 1 || (type == 2 && valuta == "RUB"))) { MessageBox.Show("Сумма превышает \"Сумму СЗ\"!", "Информирование", MessageBoxButtons.OK, MessageBoxIcon.Information); return; } decimal summaInValuta = 0; if (tbSumInRub.Text.Length > 0 ? decimal.Parse(tbSumInRub.Text) > 0 ? true: false: false) { summa = decimal.Parse(tbSumInRub.Text.Trim()); summaInValuta = decimal.Parse(tbMoney.Text.Trim()); } if (checkSumInRub && double.Parse(tbCourse.Text) <= 0) { MessageBox.Show("Заполните курс валюты!", "Информирование", MessageBoxButtons.OK, MessageBoxIcon.Information); return; } // decimal balanceSumma = type == 1? getHistoryOrder(id_ServiceRecords, maxSumma) : getHistoryReturn(id_ServiceRecords, maxSumma); if (type == 1 && (balanceGet - (checkSumInRub ? summaInValuta : summa) < 0) && valuta == "RUB") // - (checkSumInRub ? summaInValuta : summa) < 0) { MessageBox.Show("Вы можете получить не больше " + balanceGet, "Информирование", MessageBoxButtons.OK, MessageBoxIcon.Information); return; } else if (type == 1 && summaInValuta > balanceGetInValuta && valuta != "RUB") // - (checkSumInRub ? summaInValuta : summa) < 0) { MessageBox.Show("Вы можете получить не больше " + balanceGetInValuta, "Информирование", MessageBoxButtons.OK, MessageBoxIcon.Information); return; } else if (type == 2 && valuta == "RUB" && (checkSumInRub ? summaInValuta : summa) > balanceReturn) { MessageBox.Show("Вы можете вернуть не больше " + balanceReturn, "Информирование", MessageBoxButtons.OK, MessageBoxIcon.Information); return; } else if (type == 2 && valuta != "RUB" && summa > balanceReturn) { MessageBox.Show("Вы можете вернуть не больше " + balanceReturn, "Информирование", MessageBoxButtons.OK, MessageBoxIcon.Information); return; } else if (type == 2 && (checkSumInRub ? summaInValuta : summa) > balanceReturn && balanceReturn > 0 && !isTodayMonthAbdYear) { MessageBox.Show(Config.centralText("Вы можете вернуть по старому долгу\nне больше " + balanceReturn + "\n"), "Информирование", MessageBoxButtons.OK, MessageBoxIcon.Information); return; } if (DialogResult.Yes == MessageBox.Show("Вы заказываете на имя " + cmbDirector.Text, "Заказ", MessageBoxButtons.YesNo, MessageBoxIcon.Question, MessageBoxDefaultButton.Button2)) { int idMoneyRecipient; idMoneyRecipient = (int)cmbDirector.SelectedValue; DataTable dtTMP = new DataTable(); DataTable dtTMPUpdate = new DataTable(); if (idOrder == 0) { dtTMP = Config.hCntMain.setPayments(id_ServiceRecords, dtpDate.Value, summa, type, UserSettings.User.Id, idMoneyRecipient, status, summaInValuta, valuta //, //oldDebt ); } else { dtTMPUpdate = Config.hCntMain.updatePayments(idOrder, dtpDate.Value, summa, type, UserSettings.User.Id, idMoneyRecipient, status, summaInValuta, isChangeUserMoneyTake); } if ((dtTMP == null || dtTMP.Rows.Count == 0) && idOrder == 0) { MessageBox.Show("Ошибка добавления.", "Ошибка", MessageBoxButtons.OK, MessageBoxIcon.Error); return; } else if (dtTMPUpdate != null ? dtTMPUpdate.Rows.Count > 0 ? true: false: false) { if (dtTMPUpdate.Rows[0][0].ToString().Equals("ошибка")) { MessageBox.Show("Ошибка редактирования.", "Ошибка", MessageBoxButtons.OK, MessageBoxIcon.Error); return; } this.DialogResult = DialogResult.OK; } if (isEdit) { DataTable dtTmpData = Config.hCntMain.getServiceRecordsBody(id_ServiceRecords); Logging.StartFirstLevel(1503); Logging.Comment("Произведено редактирование операции по ДС"); Logging.Comment("Тип операции:" + (type == 1 ? "Получение" : "Возврат")); //Logging.Comment("ID: " + idOrder); Logging.Comment("Id СЗ: " + id_ServiceRecords); Logging.Comment("Номер СЗ: " + dtTmpData.Rows[0]["Number"].ToString()); Logging.Comment("Подномер: " + tbNumberSub.Text); //Logging.VariableChange("Сумма", tbMoney.Text, oldSumma); Logging.Comment("Сумма: " + tbMoney.Text); Logging.Comment("Валюта: " + tbValuta.Text); Logging.Comment("Тип СЗ по времени: " + ((int)dtTmpData.Rows[0]["TypeServiceRecordOnTime"] == 1 ? "разовая" : ((int)dtTmpData.Rows[0]["TypeServiceRecordOnTime"] == 2 ? "ежемесячная" : "Фонд"))); int?idFond = dtTmpData.Rows[0]["id_ServiceRecordsFond"] == DBNull.Value ? null : (int?)dtTmpData.Rows[0]["id_ServiceRecordsFond"]; if (idFond != null) { DataTable dtTmpFond = Config.hCntMain.getFondInfo(idFond, id_ServiceRecords); if (dtTmpFond != null && dtTmpFond.Rows.Count > 0) { Logging.Comment($"№{dtTmpFond.Rows[0]["Number"].ToString()} на {dtTmpFond.Rows[0]["sumString"].ToString()} от {((DateTime)dtTmpFond.Rows[0]["DateConfirmationD"]).ToShortDateString()}"); } } else { Logging.Comment((int)dtTmpData.Rows[0]["TypeServiceRecordOnTime"] == 3 ? "Доп.фонд не выбран" : "Фонд не выбран"); } if (dtTmpData.Rows[0]["inType"] != DBNull.Value) { DataTable dtTypicalWorks = Config.hCntMain.getTypicalWorks(false); if (dtTypicalWorks != null && dtTypicalWorks.Rows.Count > 0) { EnumerableRowCollection <DataRow> rowType = dtTypicalWorks.AsEnumerable().Where(r => r.Field <int>("id") == (int)dtTmpData.Rows[0]["inType"]); if (rowType.Count() > 0) { Logging.Comment($"Тип работ ID:{rowType.First()["id"]}; Наименование:{rowType.First()["cName"]}"); } } } Logging.VariableChange("Получатель ID: ", cmbDirector.SelectedValue, oldIdDirector, typeLog._int); Logging.VariableChange("Получатель ФИО: ", cmbDirector.Text, oldDirector == null ? "" : oldDirector); Logging.Comment("Предполагаемая дата: " + dtpDate.Value.ToShortDateString()); if (!valuta.Equals("RUB") && type != 2) { Logging.Comment("Курс валюты: " + tbCourse.Text); Logging.Comment("Сумму в рублях: " + tbSumInRub.Text); } if (dtTMPUpdate != null && dtTMPUpdate.Rows.Count > 0 && !dtTMPUpdate.Columns.Contains("error")) { Logging.Comment("ФИО автора СЗ ID: " + dtTMPUpdate.Rows[0]["id_Creator"].ToString() + "; ФИО: " + dtTMPUpdate.Rows[0]["FIO"].ToString()); } if (dtTMP != null && dtTMP.Rows.Count > 0 && !dtTMP.Columns.Contains("error")) { Logging.Comment("Статус ДО ID: " + dtTMP.Rows[0]["id_prev"].ToString() + "; Наименование: " + dtTMP.Rows[0]["cName_prev"].ToString()); Logging.Comment("Статус После ID: " + dtTMP.Rows[0]["id"].ToString() + "; Наименование: " + dtTMP.Rows[0]["cName"].ToString()); } Logging.Comment("Операцию выполнил: ID:" + Nwuram.Framework.Settings.User.UserSettings.User.Id + " ; ФИО:" + Nwuram.Framework.Settings.User.UserSettings.User.FullUsername); Logging.StopFirstLevel(); } else { setLog(id_ServiceRecords, "", 0, type); } //тут запись в ДО ДЗ if (!isChangeUserMoneyTake) { DataTable dtTmpMemo = Config.hCntMain.getMemorandums(nowTime, nowTime, id_ServiceRecords, false); if (dtTmpMemo != null && dtTmpMemo.Rows.Count > 0) { foreach (DataRow row in dtTmpMemo.Rows) { int id_doc = (int)row["id_doc"]; Config.hCntDocumentsDZ.setMoveDocument(id_doc); } } } //if (id_doc != null) //Config.hCntDocumentsDZ.setMoveDocument(id_doc); // if (inType == 4 && UserSettings.User.StatusCode.Equals("РКВ")) { if (MessageBox.Show("Вы хотите распечатать табель?", "Печать табеля", MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.Yes) { DataTable dtTrialTable = Config.hCntMain.getTrialTablePayICServiceRecordLink(id_ServiceRecords); DataTable dtTmpIC = dtTrialTable.Clone(); var groupKadr = dtTrialTable.AsEnumerable().GroupBy(g => new { id_Kadr = g.Field <int>("id_Kadr") }).Select(s => new { s.Key.id_Kadr }); foreach (var gKadr in groupKadr) { EnumerableRowCollection <DataRow> rowCollect = dtTrialTable.AsEnumerable().Where(r => r.Field <int>("id_Kadr") == gKadr.id_Kadr).OrderBy(r => r.Field <DateTime>("TimeIn")); string periodPay = ""; DateTime tmpDate = (DateTime)rowCollect.First()["TimeIn"]; bool isNextTrue = false; for (int i = 0; i < rowCollect.Count(); i++) { DataRow row = rowCollect.ElementAt(i); TimeSpan span = ((DateTime)row["TimeIn"]).Date - tmpDate.Date; if (span.Days == 0) { periodPay += (periodPay.Length == 0 ? "" : ",") + $"{tmpDate.ToShortDateString()}"; } else if (span.Days == 1) { isNextTrue = true; if (i == rowCollect.Count() - 1) { periodPay += $"-{((DateTime)row["TimeIn"]).ToShortDateString()}"; } } else { if (isNextTrue) { periodPay += $"-{tmpDate.ToShortDateString()},{((DateTime)row["TimeIn"]).ToShortDateString()}"; } else { periodPay += $",{((DateTime)row["TimeIn"]).ToShortDateString()}"; } isNextTrue = false; } tmpDate = (DateTime)row["TimeIn"]; } DataRow newRow = dtTmpIC.NewRow(); newRow["FIO"] = rowCollect.First()["FIO"]; newRow["namePost"] = rowCollect.First()["namePost"]; newRow["OldSalary"] = rowCollect.First()["OldSalary"]; newRow["MinSalary"] = rowCollect.First()["MinSalary"]; newRow["MaxSalary"] = rowCollect.First()["MaxSalary"]; newRow["nowSalary"] = rowCollect.First()["nowSalary"]; newRow["id_Kadr"] = rowCollect.First()["id_Kadr"]; newRow["periodPay"] = periodPay; newRow["minuteWork"] = dtTrialTable.AsEnumerable().Where(r => r.Field <int>("id_Kadr") == gKadr.id_Kadr).Sum(r => r.Field <decimal>("WorkedHours")); newRow["hourWorkOnDay"] = dtTrialTable.AsEnumerable().Where(r => r.Field <int>("id_Kadr") == gKadr.id_Kadr).Sum(r => r.Field <decimal>("hourWorkOnDay")); newRow["payment"] = dtTrialTable.AsEnumerable().Where(r => r.Field <int>("id_Kadr") == gKadr.id_Kadr).Sum(r => r.Field <decimal>("payment")).ToString("0.00"); dtTmpIC.Rows.Add(newRow); } dtTrialTable = dtTmpIC.Copy(); DataTable dtToReport = dtTrialTable.Copy(); for (int i = dtToReport.Columns.Count - 1; i >= 0; i--) //foreach (DataColumn col in dtToReport.Columns) { DataColumn col = dtToReport.Columns[i]; if (new List <string>() { "FIO", "namePost", "nowSalary", "periodPay", "minuteWork" /*, "hourWorkOnDay"*/, "Payment" }.Contains(col.ColumnName)) { continue; } removeColumn(dtToReport, col.ColumnName); } dtToReport.Columns.Add("sing", typeof(string)).DefaultValue = ""; dtToReport.Columns["FIO"].SetOrdinal(0); dtToReport.Columns["namePost"].SetOrdinal(1); dtToReport.Columns["nowSalary"].SetOrdinal(2); dtToReport.Columns["periodPay"].SetOrdinal(3); dtToReport.Columns["minuteWork"].SetOrdinal(4); //dtToReport.Columns["hourWorkOnDay"].SetOrdinal(5); dtToReport.Columns["Payment"].SetOrdinal(5); dtToReport.Columns["sing"].SetOrdinal(6); Nwuram.Framework.ToWord.HandmadeReport report = new Nwuram.Framework.ToWord.HandmadeReport(Application.StartupPath + @"\Templates\tamplateIC.dotx"); report.CurrentTable = report.GetTable(1); report.SetCellText(1, 1, $"Отдел: {nameDep}"); report.SetCellText(1, 2, $"Выгрузил: {UserSettings.User.FullUsername}"); report.SetCellText(2, 1, $"№ СЗ на ДС: {NumSZ}"); report.SetCellText(2, 2, $"Дата выгрузки: {DateTime.Now.ToShortDateString()} {DateTime.Now.ToShortTimeString()}"); report.CurrentTable = report.GetTable(2); report.ExportDataToTable(2, dtToReport, true); report.CurrentTable = report.GetTable(3); report.SetCellText(1, 6, $"{dtToReport.AsEnumerable().Sum(r => r.Field<decimal>("Payment")).ToString("0.00")}"); report.SetPageOrientation(Microsoft.Office.Interop.Word.WdOrientation.wdOrientLandscape); report.Show(); } } this.DialogResult = DialogResult.OK; } else { return; } }