private void button4_Click(object sender, EventArgs e)
{
if (openFileDialog1.ShowDialog().ToString() == "OK")
{
string tarifFile = openFileDialog1.FileName;
AddTarrif at = new AddTarrif();
DataTable tarifKind = at.ReturnDataTable(tarifFile, @"SELECT DISTINCT TariffNetNum, KindPay FROM infTariff ORDER BY TariffNetNum, KindPay");
for (int i = 0; i < tarifKind.Rows.Count; i++)
{
DataTable tarifNet = new DataTable();
tarifNet = at.ReturnDataTable(tarifFile, @"SELECT * FROM infTariff WHERE TariffNetNum = '" + tarifKind.Rows[i]["TariffNetNum"].ToString() + "' AND KindPay = '" + tarifKind.Rows[i]["KindPay"].ToString() + "' ORDER BY TariffNetNum, KindPay, WorkerRate");
ObrabotkaTarrif ot = new ObrabotkaTarrif();
tarifNet = ot.NewTariff
(
double.Parse(tarifNet.Rows[0]["HourCost"].ToString()),
double.Parse(tarifNet.Rows[1]["HourCost"].ToString()),
double.Parse(tarifNet.Rows[2]["HourCost"].ToString()),
double.Parse(tarifNet.Rows[3]["HourCost"].ToString()),
double.Parse(tarifNet.Rows[4]["HourCost"].ToString()),
double.Parse(tarifNet.Rows[5]["HourCost"].ToString()),
tarifNet.Rows[0]["TariffNetNum"].ToString(),
tarifNet.Rows[0]["KindPay"].ToString()
);//в дататейбл заносим новую тарифную сетку
at.deleteFromDB(@"DELETE FROM infTariff WHERE TariffNetNum = '" + tarifKind.Rows[i]["TariffNetNum"].ToString() + "' AND KindPay = '" + tarifKind.Rows[i]["KindPay"].ToString() + "'");
// at.deleteFromDB(tarifFile, @"DELETE FROM infTariff WHERE TariffNetNum = '" + tarifKind.Rows[i]["TariffNetNum"].ToString() + "' AND KindPay = '" + tarifKind.Rows[i]["KindPay"].ToString() + "'");
writteToBD(tarifNet);//записываем все в бд
dataGridViewTariffVuvod.DataSource = tarifNet;
}
}
}
private void backgroundWorker1_DoWork(object sender, DoWorkEventArgs e)
{
AddTarrif at = new AddTarrif(); // создаем обект аддтариф
DataTable dt = new DataTable();
string zapros = " SELECT * FROM LabourNorm ORDER BY ID";//селектим все кроме ID - оно будет автоматически ставится, и Valuation и ValPreparTime заместо них будут новые значения
dt = at.ReturnDataTable(zapros); //Присваеваем дата тейблу содержание таблици
at.deleteFromDB("EXEC sp_rename 'LabourNorm', 'LabourNorm " + dt.Rows[0]["DocNum"] + "'");//переименовуем LabourNorm
int lastID = (int)dt.Rows[dt.Rows.Count - 1]["ID"]; //Последний ID(Самый большой)
for (int id = 0; id != lastID; id++)
{
if (id + 1 != (int)dt.Rows[id]["ID"])
{
DataRow _ravi = dt.NewRow();
_ravi["ID"] = id + 1;
_ravi["infDetID"] = -1;
_ravi["OperNum"] = "---";
_ravi["DepRegion"] = -1;
_ravi["ProfCode"] = -1;
_ravi["NameKindWork"] = "";
_ravi["KindPay"] = "П";
_ravi["TariffNetNum"] = 1;
_ravi["WorkerRate"] = 1;
_ravi["ItemCTN"] = -1;
_ravi["PreparTimeCTN"] = -1;
_ravi["ItemPayNorm"] = -1;
_ravi["PreparTimePayNorm"] = -1;
_ravi["Valuation"] = -1;
_ravi["ValPreparTime"] = -1;
_ravi["CoeffCTN"] = -1;
_ravi["DocNum"] = "---";
_ravi["Date"] = "01.01.1980 00:00:00";
_ravi["TaskNumber"] = "---";
dt.Rows.InsertAt(_ravi, id);
}
}
DataTable dataTableForTariff = new DataTable();
dataTableForTariff = at.ReturnDataTable(" SELECT * FROM infTariff");
for (int i = 0; i < dt.Rows.Count; i++)
{
string tariffNetNum = dt.Rows[i]["TariffNetNum"].ToString();
string kindPay = dt.Rows[i]["KindPay"].ToString();
string workerRate = dt.Rows[i]["WorkerRate"].ToString();
double coef = (double.Parse(dt.Rows[i]["CoeffCTN"].ToString()) == 0) ? 1 : double.Parse(dt.Rows[i]["CoeffCTN"].ToString());
double itemPayNorm = double.Parse(dt.Rows[i]["ItemPayNorm"].ToString());
if (itemPayNorm == 0.4051)
{
}
var results = from DataRow myRow in dataTableForTariff.Rows
where (myRow["TariffNetNum"].ToString() == tariffNetNum) && (myRow["KindPay"].ToString() == kindPay) && (myRow["WorkerRate"].ToString() == workerRate)
select myRow;
double currTarif;
try
{
currTarif = double.Parse(results.CopyToDataTable().Rows[0]["HourCost"].ToString());
}
catch
{
currTarif = 0;
}
double preparTimePayNorm = double.Parse(dt.Rows[i]["PreparTimePayNorm"].ToString());
string g = (itemPayNorm * currTarif).ToString("N" + Properties.Settings.Default.RoundNum);
dt.Rows[i]["Valuation"] = (itemPayNorm * currTarif).ToString("N" + Properties.Settings.Default.RoundNum);
dt.Rows[i]["ValPreparTime"] = (preparTimePayNorm * currTarif).ToString("N" + Properties.Settings.Default.RoundNum);
dt.Rows[i]["DocNum"] = textBoxDataPrikaz.Text;
}
at.deleteFromDB("CREATE TABLE [LabourNorm]([ID] INT NOT NULL IDENTITY (1,1),[infDetID] INT NOT NULL,[OperNum] NVARCHAR(100),[DepRegion] FLOAT NOT NULL,[ProfCode] INT NOT NULL,[NameKindWork] NVARCHAR(50),[KindPay] NVARCHAR(1) NOT NULL,[TariffNetNum] TINYINT NOT NULL,[WorkerRate] FLOAT NOT NULL,[ItemCTN] FLOAT NOT NULL,[PreparTimeCTN] FLOAT NOT NULL,[ItemPayNorm] FLOAT NOT NULL,[PreparTimePayNorm] FLOAT NOT NULL,[Valuation] FLOAT NOT NULL,[ValPreparTime] FLOAT,[CoeffCTN] FLOAT NOT NULL,[DocNum] NVARCHAR(100),[Date] DATETIME NOT NULL,[TaskNumber] NVARCHAR(30));"); //очишаем таблицу перед записью
String connString = Properties.Settings.Default.TrudoyomkostDBConnectionString;
SqlCeBulkCopy bulkInsert = new SqlCeBulkCopy(connString);
bulkInsert.DestinationTableName = "LabourNorm";
bulkInsert.WriteToServer(dt);
at.deleteFromDB("DELETE FROM LabourNorm WHERE infDetID ='-1' AND ProfCode = '-1'");
}
private void deleteFromBD(string variableForTariffNetNum,string variableForKindPay)
{
AddTarrif addTarif = new AddTarrif();
addTarif.deleteFromDB(@"DELETE FROM infTariff WHERE TariffNetNum = '" + variableForTariffNetNum + "' " + "AND" + " KindPay = '" + variableForKindPay + "'");
}
private void button4_Click(object sender, EventArgs e)
{
if (openFileDialog1.ShowDialog().ToString() == "OK")
{
string tarifFile = openFileDialog1.FileName;
AddTarrif at = new AddTarrif();
DataTable tarifKind = at.ReturnDataTable(tarifFile, @"SELECT DISTINCT TariffNetNum, KindPay FROM infTariff ORDER BY TariffNetNum, KindPay");
for (int i = 0; i < tarifKind.Rows.Count; i++)
{
DataTable tarifNet = new DataTable();
tarifNet = at.ReturnDataTable(tarifFile, @"SELECT * FROM infTariff WHERE TariffNetNum = '" + tarifKind.Rows[i]["TariffNetNum"].ToString() + "' AND KindPay = '" + tarifKind.Rows[i]["KindPay"].ToString() + "' ORDER BY TariffNetNum, KindPay, WorkerRate");
ObrabotkaTarrif ot = new ObrabotkaTarrif();
tarifNet = ot.NewTariff
(
double.Parse(tarifNet.Rows[0]["HourCost"].ToString()),
double.Parse(tarifNet.Rows[1]["HourCost"].ToString()),
double.Parse(tarifNet.Rows[2]["HourCost"].ToString()),
double.Parse(tarifNet.Rows[3]["HourCost"].ToString()),
double.Parse(tarifNet.Rows[4]["HourCost"].ToString()),
double.Parse(tarifNet.Rows[5]["HourCost"].ToString()),
tarifNet.Rows[0]["TariffNetNum"].ToString(),
tarifNet.Rows[0]["KindPay"].ToString()
);//в дататейбл заносим новую тарифную сетку
at.deleteFromDB(@"DELETE FROM infTariff WHERE TariffNetNum = '" + tarifKind.Rows[i]["TariffNetNum"].ToString() + "' AND KindPay = '" + tarifKind.Rows[i]["KindPay"].ToString() + "'");
// at.deleteFromDB(tarifFile, @"DELETE FROM infTariff WHERE TariffNetNum = '" + tarifKind.Rows[i]["TariffNetNum"].ToString() + "' AND KindPay = '" + tarifKind.Rows[i]["KindPay"].ToString() + "'");
writteToBD(tarifNet);//записываем все в бд
dataGridViewTariffVuvod.DataSource = tarifNet;
}
}
}
private void backgroundWorker1_DoWork(object sender, DoWorkEventArgs e)
{
AddTarrif at = new AddTarrif(); // создаем обект аддтариф
DataTable dt = new DataTable();
string zapros = " SELECT * FROM LabourNorm ORDER BY ID"; //селектим все кроме ID - оно будет автоматически ставится, и Valuation и ValPreparTime заместо них будут новые значения
dt = at.ReturnDataTable(zapros); //Присваеваем дата тейблу содержание таблици
at.deleteFromDB("EXEC sp_rename 'LabourNorm', 'LabourNorm " + dt.Rows[0]["DocNum"] + "'"); //переименовуем LabourNorm
int lastID = (int)dt.Rows[dt.Rows.Count - 1]["ID"]; //Последний ID(Самый большой)
for (int id = 0; id != lastID; id++)
{
if (id + 1 != (int)dt.Rows[id]["ID"])
{
DataRow _ravi = dt.NewRow();
_ravi["ID"] = id + 1;
_ravi["infDetID"] = -1;
_ravi["OperNum"] = "---";
_ravi["DepRegion"] = -1;
_ravi["ProfCode"] = -1;
_ravi["NameKindWork"] = "";
_ravi["KindPay"] = "П";
_ravi["TariffNetNum"] = 1;
_ravi["WorkerRate"] = 1;
_ravi["ItemCTN"] = -1;
_ravi["PreparTimeCTN"] = -1;
_ravi["ItemPayNorm"] = -1;
_ravi["PreparTimePayNorm"] = -1;
_ravi["Valuation"] = -1;
_ravi["ValPreparTime"] = -1;
_ravi["CoeffCTN"] = -1;
_ravi["DocNum"] = "---";
_ravi["Date"] = "01.01.1980 00:00:00";
_ravi["TaskNumber"] = "---";
dt.Rows.InsertAt(_ravi, id);
}
}
DataTable dataTableForTariff = new DataTable();
dataTableForTariff = at.ReturnDataTable(" SELECT * FROM infTariff");
for (int i = 0; i < dt.Rows.Count; i++)
{
string tariffNetNum = dt.Rows[i]["TariffNetNum"].ToString();
string kindPay = dt.Rows[i]["KindPay"].ToString();
string workerRate = dt.Rows[i]["WorkerRate"].ToString();
double coef = (double.Parse(dt.Rows[i]["CoeffCTN"].ToString()) == 0) ? 1 : double.Parse(dt.Rows[i]["CoeffCTN"].ToString());
double itemPayNorm = double.Parse(dt.Rows[i]["ItemPayNorm"].ToString());
if (itemPayNorm == 0.4051)
{
}
var results = from DataRow myRow in dataTableForTariff.Rows
where (myRow["TariffNetNum"].ToString() == tariffNetNum) && (myRow["KindPay"].ToString() == kindPay) && (myRow["WorkerRate"].ToString() == workerRate)
select myRow;
double currTarif;
try
{
currTarif = double.Parse(results.CopyToDataTable().Rows[0]["HourCost"].ToString());
}
catch
{
currTarif = 0;
}
double preparTimePayNorm = double.Parse(dt.Rows[i]["PreparTimePayNorm"].ToString());
string g = (itemPayNorm * currTarif).ToString("N" + Properties.Settings.Default.RoundNum);
dt.Rows[i]["Valuation"] = (itemPayNorm * currTarif).ToString("N" + Properties.Settings.Default.RoundNum);
dt.Rows[i]["ValPreparTime"] = (preparTimePayNorm * currTarif).ToString("N" + Properties.Settings.Default.RoundNum);
dt.Rows[i]["DocNum"] = textBoxDataPrikaz.Text;
}
at.deleteFromDB("CREATE TABLE [LabourNorm]([ID] INT NOT NULL IDENTITY (1,1),[infDetID] INT NOT NULL,[OperNum] NVARCHAR(100),[DepRegion] FLOAT NOT NULL,[ProfCode] INT NOT NULL,[NameKindWork] NVARCHAR(50),[KindPay] NVARCHAR(1) NOT NULL,[TariffNetNum] TINYINT NOT NULL,[WorkerRate] FLOAT NOT NULL,[ItemCTN] FLOAT NOT NULL,[PreparTimeCTN] FLOAT NOT NULL,[ItemPayNorm] FLOAT NOT NULL,[PreparTimePayNorm] FLOAT NOT NULL,[Valuation] FLOAT NOT NULL,[ValPreparTime] FLOAT,[CoeffCTN] FLOAT NOT NULL,[DocNum] NVARCHAR(100),[Date] DATETIME NOT NULL,[TaskNumber] NVARCHAR(30));"); //очишаем таблицу перед записью
String connString = Properties.Settings.Default.TrudoyomkostDBConnectionString;
SqlCeBulkCopy bulkInsert = new SqlCeBulkCopy(connString);
bulkInsert.DestinationTableName = "LabourNorm";
bulkInsert.WriteToServer(dt);
at.deleteFromDB("DELETE FROM LabourNorm WHERE infDetID ='-1' AND ProfCode = '-1'");
}
private void deleteFromBD(string variableForTariffNetNum, string variableForKindPay)
{
AddTarrif addTarif = new AddTarrif();
addTarif.deleteFromDB(@"DELETE FROM infTariff WHERE TariffNetNum = '" + variableForTariffNetNum + "' " + "AND" + " KindPay = '" + variableForKindPay + "'");
}