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;
}
}
