public static void deleteChild(int client_id, int id, string motherCertif)
{
try
{
string Child_query = $"UPDATE CHILD SET deleted = 1" +
$" WHERE mother_id = {client_id} and id = {id} and motherCertif = '{motherCertif}'";
using (MySqlConnection sqlConnection = ConnectionClass.GetStringConnectionTable())
{
if (sqlConnection.State == ConnectionState.Closed)
{
sqlConnection.Open();
}
MySqlCommand sqlCommand = new MySqlCommand(Child_query, sqlConnection);
sqlCommand.ExecuteNonQuery();
sqlConnection.Close();
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Ошибка", MessageBoxButtons.OK, MessageBoxIcon.Error);
logger.Debug("\n/--------------------------------------------------------------------/\n" + ex.StackTrace + "\n//----------------------------//\n" + ex.Message + "\n\n");
}
}
private void deleteBerem()
{
try
{
if (MessageBox.Show("Вы действительно хотите удалить данную запись?", "Удаление выделенной записи", MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.Yes)
{
string delBerem = $"UPDATE BTAL1 SET deleted = 1" +
$" WHERE client_id = {dataCertifRod.CurrentRow.Cells[11].Value.ToString()} and id = {dataCertifRod.CurrentRow.Cells[12].Value.ToString()}";
using (MySqlConnection sqlConnection = ConnectionClass.GetStringConnectionTable())
{
if (sqlConnection.State == ConnectionState.Closed)
{
sqlConnection.Open();
}
MySqlCommand sqlCommand = new MySqlCommand(delBerem, sqlConnection);
sqlCommand.ExecuteNonQuery();
sqlConnection.Close();
}
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Ошибка", MessageBoxButtons.OK, MessageBoxIcon.Error);
logger.Debug("\n/--------------------------------------------------------------------/\n" + ex.StackTrace + "\n//----------------------------//\n" + ex.Message + "\n\n");
}
}
private void FuncInsertChild()
{
try
{
string Child_query = "";
for (int i = 0; i < dgvCHILD.Rows.Count; i++)
{
Child_query = $"insert into CHILD (SEX, WEIGHT, GROWTH, DIAG, mother_id, motherCertif) value ('{dgvCHILD[1,i].Value}', {dgvCHILD[2,i].Value}, {dgvCHILD[3,i].Value}, '{dgvCHILD[4,i].Value}', {client_id}, '{tbSCERTIF.Text + " " + tbNCERTIF.Text}')";
using (MySqlConnection sqlConnection = ConnectionClass.GetStringConnectionTable())
{
if (sqlConnection.State == ConnectionState.Closed)
{
sqlConnection.Open();
}
MySqlCommand sqlCommand = new MySqlCommand(Child_query, sqlConnection);
sqlCommand.ExecuteNonQuery();
sqlConnection.Close();
}
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Ошибка", MessageBoxButtons.OK, MessageBoxIcon.Error);
logger.Debug("\n/--------------------------------------------------------------------/\n" + ex.StackTrace + "\n//----------------------------//\n" + ex.Message + "\n\n");
}
}
public static void deleteBerem(int client_id, int id)
{
try
{
string delBerem = $"UPDATE BTAL1 SET deleted = 1" +
$" WHERE client_id = {client_id} and id = {id}";
using (MySqlConnection sqlConnection = ConnectionClass.GetStringConnectionTable())
{
if (sqlConnection.State == ConnectionState.Closed)
{
sqlConnection.Open();
}
MySqlCommand sqlCommand = new MySqlCommand(delBerem, sqlConnection);
sqlCommand.ExecuteNonQuery();
sqlConnection.Close();
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Ошибка", MessageBoxButtons.OK, MessageBoxIcon.Error);
logger.Debug("\n/--------------------------------------------------------------------/\n" + ex.StackTrace + "\n//----------------------------//\n" + ex.Message + "\n\n");
}
}
private void FuncInsertMother()
{
try
{
string Mother_query = "insert into BTAL2 (DREG, DIAG, BQNT, CHK, SCERTIF, NCERTIF, DCERTIF, SNILS, LNAME, FNAME, MNAME, BDATE, ADDRESS, TDOC, SDOC, NDOC, DDOC, ODOC, SPOLICY, NPOLICY, SLEAF, NLEAF, DLEAF, NCARD, DCARD, client_id) " +
$"VALUE ('{dateDREG.Value.ToString("yyyy-MM-dd")}', '{tbDIAG.Text}', {tbBQNT.Text}, 1, '{tbSCERTIF.Text}', '{tbNCERTIF.Text}', '{(tbSCERTIF.Text == "" && tbNCERTIF.Text == "" ? "" : dateDCERTIF.Value.ToString("yyyy-MM-dd"))}', '{tbSNILS.Text}', '{tbLNAME.Text}', '{tbFNAME.Text}'," +
$" '{tbMNAME.Text}', '{dateBDATE.Value.ToString("yyyy-MM-dd")}', '{tbADDRESS.Text}', {cbTypeDoc.SelectedIndex}, '{tbSDOC.Text}', '{tbNDOC.Text}', '{(cbTypeDoc.SelectedIndex == 0 && tbNDOC.Text == "" && tbSDOC.Text == "" ? "" : dateDDOC.Value.ToString("yyyy-MM-dd"))}', '{tbODOC.Text}', '{tbSPOLICY.Text}', '{tbNPOLICY.Text}'," +
$" '{tbSLEAF.Text}', '{tbNLEAF.Text}', '{(tbSLEAF.Text.Length > 0 || tbNLEAF.Text.Length > 0 ? dateDLEAF.Value.ToString("yyyy-MM-dd") : "")}', '{tbNCARD.Text}', '{(tbNCARD.Text.Length > 0 ? dateDCARD.Value.ToString("yyyy-MM-dd") : "")}', {client_id})";
using (MySqlConnection sqlConnection = ConnectionClass.GetStringConnectionTable())
{
if (sqlConnection.State == ConnectionState.Closed)
{
sqlConnection.Open();
}
MySqlCommand sqlCommand = new MySqlCommand(Mother_query, sqlConnection);
sqlCommand.ExecuteNonQuery();
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Ошибка", MessageBoxButtons.OK, MessageBoxIcon.Error);
logger.Debug("\n/--------------------------------------------------------------------/\n" + ex.StackTrace + "\n//----------------------------//\n" + ex.Message + "\n\n");
}
}
private void FuncMINIUpdateChild(int i)
{
try
{
string Child_query = $"UPDATE CHILD SET SEX = '{dgvCHILD[1, i].Value}', WEIGHT = '{dgvCHILD[2, i].Value}', GROWTH = {dgvCHILD[3, i].Value}, DIAG = '{dgvCHILD[4, i].Value}'" +
$" WHERE mother_id = {client_id} and id = {dgvCHILD[0, i].Value} and deleted = 0";
using (MySqlConnection sqlConnection = ConnectionClass.GetStringConnectionTable())
{
if (sqlConnection.State == ConnectionState.Closed)
{
sqlConnection.Open();
}
MySqlCommand sqlCommand = new MySqlCommand(Child_query, sqlConnection);
sqlCommand.ExecuteNonQuery();
sqlConnection.Close();
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Ошибка", MessageBoxButtons.OK, MessageBoxIcon.Error);
logger.Debug("\n/--------------------------------------------------------------------/\n" + ex.StackTrace + "\n//----------------------------//\n" + ex.Message + "\n\n");
}
}
private void FuncInsertMother(int chk, int phelp)
{
try
{
//tbSNILS.TextMaskFormat = MaskFormat.ExcludePromptAndLiterals;
string Mother_query = "insert into BTAL1 (DREG, QTW, MULP, PREM, CHK, PHELP, SCERTIF, NCERTIF, DCERTIF, SNILS, LNAME, FNAME, MNAME, BDATE, ADDRESS, TDOC, NDOC, SDOC, DDOC, ODOC, SPOLICY, NPOLICY, NCARD, DCARD, SLEAF, NLEAF, DLEAF, client_id) " +
$"VALUE ('{dateDREG.Value.ToString("yyyy-MM-dd")}', {tbQTW.Text}, {(cBMULP.Checked == true ? 1 : 0)}, {(cBPREM.Checked == true ? 1 : 0)}, {chk}, {phelp}, '{tbSCERTIF.Text}', '{tbNCERTIF.Text}', '{(tbSCERTIF.Text == "" && tbNCERTIF.Text == "" ? "" : dateDCERTIF.Value.ToString("yyyy-MM-dd"))}', '{tbSNILS.Text}', '{tbLNAME.Text}', '{tbFNAME.Text}'," +
$" '{tbMNAME.Text}', '{dateBDATE.Value.ToString("yyyy-MM-dd")}', '{tbADDRESS.Text}', {cbTypeDoc.SelectedIndex}, '{tbNDOC.Text}', '{tbSDOC.Text}', '{(cbTypeDoc.SelectedIndex == 0 && tbNDOC.Text == "" && tbSDOC.Text == "" ? "" : dateDDOC.Value.ToString("yyyy-MM-dd"))}', '{tbODOC.Text}', '{tbSPOLICY.Text}', '{tbNPOLICY.Text}'," +
$" '{tbNCARD.Text}', '{(tbNCARD.Text.Length > 0 ? dateDCARD.Value.ToString("yyyy-MM-dd") : "")}', '{tbSLEAF.Text}', '{tbNLEAF.Text}', '{(tbSLEAF.Text.Length > 0 || tbNLEAF.Text.Length > 0 ? dateDLEAF.Value.ToString("yyyy-MM-dd") : "")}', {client_id})";
using (MySqlConnection sqlConnection = ConnectionClass.GetStringConnectionTable())
{
if (sqlConnection.State == ConnectionState.Closed)
{
sqlConnection.Open();
}
MySqlCommand sqlCommand = new MySqlCommand(Mother_query, sqlConnection);
sqlCommand.ExecuteNonQuery();
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Ошибка", MessageBoxButtons.OK, MessageBoxIcon.Error);
logger.Debug("\n/--------------------------------------------------------------------/\n" + ex.StackTrace + "\n//----------------------------//\n" + ex.Message + "\n\n");
}
}
private void FuncUpdateMother(int chk, int phelp)
{
try
{
string Mother_query = $"UPDATE BTAL1 SET DREG = '{dateDREG.Value.ToString("yyyy-MM-dd")}', QTW = {tbQTW.Text}, MULP = {(cBMULP.Checked == true ? 1 : 0)}, PREM = {(cBPREM.Checked == true ? 1 : 0)}, CHK = {chk}, PHELP = {phelp}, SCERTIF = '{tbSCERTIF.Text}', NCERTIF = '{tbNCERTIF.Text}', DCERTIF = '{(tbSCERTIF.Text == "" && tbNCERTIF.Text == "" ? "" : dateDCERTIF.Value.ToString("yyyy-MM-dd"))}'," +
$" SNILS = '{tbSNILS.Text}', LNAME = '{tbLNAME.Text}', FNAME = '{tbFNAME.Text}', MNAME = '{tbMNAME.Text}', BDATE = '{dateBDATE.Value.ToString("yyyy-MM-dd")}', ADDRESS = '{tbADDRESS.Text}', TDOC = {cbTypeDoc.SelectedIndex}, NDOC = '{tbNDOC.Text}', " +
$" SDOC = '{tbSDOC.Text}', DDOC = '{(cbTypeDoc.SelectedIndex == 0 && tbNDOC.Text == "" && tbSDOC.Text == "" ? "" : dateDDOC.Value.ToString("yyyy-MM-dd"))}', ODOC = '{tbODOC.Text}', SPOLICY = '{tbSPOLICY.Text}', NPOLICY = '{tbNPOLICY.Text}', NCARD = '{tbNCARD.Text}', DCARD = '{(tbNCARD.Text.Length > 0 ? dateDCARD.Value.ToString("yyyy-MM-dd") : "")}', " +
$" SLEAF = '{tbSLEAF.Text}', NLEAF = '{tbNLEAF.Text}', DLEAF = '{(tbSLEAF.Text.Length > 0 || tbNLEAF.Text.Length > 0 ? dateDLEAF.Value.ToString("yyyy-MM-dd") : "")}'" +
$" where client_id = {client_id}" +
$" and id = {forUpdate.Rows[0][0].ToString()} and deleted = 0";
using (MySqlConnection sqlConnection = ConnectionClass.GetStringConnectionTable())
{
if (sqlConnection.State == ConnectionState.Closed)
{
sqlConnection.Open();
}
MySqlCommand sqlCommand = new MySqlCommand(Mother_query, sqlConnection);
sqlCommand.ExecuteNonQuery();
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Ошибка", MessageBoxButtons.OK, MessageBoxIcon.Error);
logger.Debug("\n/--------------------------------------------------------------------/\n" + ex.StackTrace + "\n//----------------------------//\n" + ex.Message + "\n\n");
}
}
public static DataTable FuncLoadFromTable(string dateFrom, string dateTo)
{
DataTable womenTable = new DataTable();
try
{
string query = "SELECT CONCAT(SCERTIF, ' ', NCERTIF) CERTIF," +
" DATE_FORMAT(DCERTIF, '%d.%m.%Y') DCERTIF," +
" SNILS," +
" CONCAT(LNAME, ' ', FNAME, ' ', MNAME) FIO_rod," +
" DATE_FORMAT(BDATE, '%d.%m.%Y') BDATE," +
" ADDRESS," +
" CONCAT('Серия ', SDOC, ', номер ', NDOC, ', дата выдачи ', DATE_FORMAT(DDOC, '%d.%m.%Y'), ', выдан ', ODOC) DocType," +
" IF(SPOLICY = '', NPOLICY, CONCAT(SPOLICY, ' ', NPOLICY)) POLICY," +
" IF(SLEAF = '' AND NLEAF = '', '', CONCAT(SLEAF, ' ', NLEAF, ', выдан ', DLEAF)) SICKLIST," +
" DATE_FORMAT(DREG, '%d.%m.%Y') DREG," +
" chld.CNTCHILD," +
" BQNT CNTCHILDALL," +
" chd.CHILD," +
" b2.client_id," +
" b2.id" +
" FROM BTAL2 b2" +
" JOIN (" +
" SELECT mother_id," +
" GROUP_CONCAT(IF(DIAG = '', CONCAT(SEX, ', ', WEIGHT, ', ', GROWTH), CONCAT(SEX, ', ', WEIGHT, ', ', GROWTH, ', ', DIAG)) SEPARATOR '; ') CHILD" +
" FROM CHILD" +
" where deleted = 0" +
" group by 1" +
" )chd ON b2.client_id = chd.mother_id" +
" JOIN (" +
" SELECT mother_id, SUM(1) CNTCHILD" +
" FROM CHILD" +
" where deleted = 0" +
" GROUP BY 1" +
" ) chld ON b2.client_id = chld.mother_id" +
$" WHERE DREG BETWEEN '{dateFrom}' AND '{dateTo}'" +
" and deleted = 0";
using (MySqlConnection sqlConnection = ConnectionClass.GetStringConnectionTable())
{
if (sqlConnection.State == ConnectionState.Closed)
{
sqlConnection.Open();
}
MySqlCommand sqlCommand = new MySqlCommand(query, sqlConnection);
womenTable.Load(sqlCommand.ExecuteReader());
}
return(womenTable);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Ошибка", MessageBoxButtons.OK, MessageBoxIcon.Error);
logger.Debug("\n/--------------------------------------------------------------------/\n" + ex.StackTrace + "\n//----------------------------//\n" + ex.Message + "\n\n");
return(null);
}
}
public static DataTable FuncMotherUPD(int client_id)
{
DataTable forTableUPD = new DataTable();
try
{
string query = "SELECT IF(DREG = '00.00.0000', current_date(), DREG) DREG," + //0
" DIAG," + //1
" BQNT," + //2
" LNAME," + //3
" FNAME," + //4
" MNAME," + //5
" IF(BDATE = '00.00.0000', current_date(), BDATE) BDATE," + //6
" ADDRESS," + //7
" SNILS," + //8
" SPOLICY," + //9
" NPOLICY," + //10
" TDOC," + //11
" SDOC," + //12
" NDOC," + //13
" IF(DDOC = '00.00.0000', current_date(), DDOC) DDOC," + //14
" ODOC," + //15
" SCERTIF," + //16
" NCERTIF," + //17
" IF(DCERTIF = '00.00.0000', current_date(), DCERTIF) DCERTIF," + //18
" SLEAF," + //19
" NLEAF," + //20
" IF(DLEAF = '00.00.0000', current_date(), DLEAF) DLEAF," + //21
" NCARD," + //22
" IF(DCARD = '00.00.0000', current_date(), DCARD) DCARD," + //23
" client_id," + //24
" id" + //25
" FROM BTAL2 b2" +
$" WHERE client_id = {client_id}" +
" and deleted = 0";
using (MySqlConnection sqlConnection = ConnectionClass.GetStringConnectionTable())
{
if (sqlConnection.State == ConnectionState.Closed)
{
sqlConnection.Open();
}
MySqlCommand sqlCommand = new MySqlCommand(query, sqlConnection);
forTableUPD.Load(sqlCommand.ExecuteReader());
sqlConnection.Close();
}
return(forTableUPD);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Ошибка", MessageBoxButtons.OK, MessageBoxIcon.Error);
logger.Debug("\n/--------------------------------------------------------------------/\n" + ex.StackTrace + "\n//----------------------------//\n" + ex.Message + "\n\n");
return(null);
}
}
public static DataTable LoadMotherUPDT_BTAL1(int client_id)
{
DataTable forUpdate = new DataTable();
try
{
string query = "SELECT id," +
" IF(LNAME is NULL, '', LNAME) LNAME," +
" IF(FNAME is null, '', FNAME) FNAME," +
" IF(MNAME is null, '', MNAME) MNAME," +
" IF(BDATE = '00.00.0000', CURRENT_DATE(), BDATE) BDATE," +
" IF(ADDRESS is null, '', ADDRESS) address," +
" IF(SNILS is null, '', SNILS) SNILS," +
" IF(SPOLICY is null, '', SPOLICY) SPOLICY," +
" IF(NPOLICY is null, '', NPOLICY) NPOLICY," +
" IF(TDOC is null, 0, TDOC) TDOC," +
" IF(SDOC is null, '', SDOC) SDOC," +
" IF(NDOC is null, '', NDOC) NDOC," +
" IF(date_FORMAT(DDOC, '%d.%m.%Y') = '00.00.0000', CURRENT_DATE(), date_FORMAT(DDOC, '%d.%m.%Y')) DDOC," +
" IF(ODOC is null, '', ODOC) ODOC," +
" IF(SCERTIF is null, '', SCERTIF) SCERTIF," +
" IF(NCERTIF is null, '', NCERTIF) NCERTIF," +
" IF(date_FORMAT(DCERTIF, '%d.%m.%Y') = '00.00.0000', CURRENT_DATE(), date_FORMAT(DCERTIF, '%d.%m.%Y')) DCERTIF," +
" IF(SLEAF IS NULL, '', SLEAF) SLEAF," +
" IF(NLEAF IS NULL, '', NLEAF) NLEAF," +
" IF(date_FORMAT(DLEAF,'%d.%m.%Y') = '00.00.0000', CURRENT_DATE(), date_FORMAT(DLEAF,'%d.%m.%Y')) DLEAF," +
" CHK," +
" IF(date_FORMAT(DREG,'%d.%m.%Y') = '00.00.0000', CURRENT_DATE(), date_FORMAT(DREG,'%d.%m.%Y')) DREG," +
" QTW," +
" MULP," +
" PREM," +
" PHELP" +
" FROM BTAL1" +
$" WHERE client_id = {client_id}";
using (MySqlConnection sqlConnection = ConnectionClass.GetStringConnectionTable())
{
if (sqlConnection.State == ConnectionState.Closed)
{
sqlConnection.Open();
}
MySqlCommand sqlCommand = new MySqlCommand(query, sqlConnection);
forUpdate.Load(sqlCommand.ExecuteReader());
}
return(forUpdate);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Ошибка", MessageBoxButtons.OK, MessageBoxIcon.Error);
logger.Debug("\n/--------------------------------------------------------------------/\n" + ex.StackTrace + "\n//----------------------------//\n" + ex.Message + "\n\n");
return(null);
}
}
public static DataTable FuncTable(string dateFrom, string dateTo)
{
DataTable ReportTable = new DataTable();
try
{
string query = "SELECT \"В период беременности\" period," +
" sum(CASE when b1.ADDRESS LIKE '% г%' OR b1.ADDRESS LIKE '% г.%' or b1.ADDRESS LIKE '% ,г. %' or b1.ADDRESS LIKE '% ,г %' THEN 0 ELSE 1 END) Popular," +
" COUNT(DISTINCT b1.client_id) AllCNT" +
" FROM BTAL1 b1" +
$" WHERE b1.DCERTIF BETWEEN '{dateFrom}' AND '{dateTo}'" +
" AND b1.ADDRESS <> ''" +
" AND b1.deleted = 0" +
" GROUP BY 1" +
" UNION ALL" +
" SELECT \"В период родов и послеродовой период\" period," +
" sum(CASE when b2.ADDRESS LIKE '% г%' OR b2.ADDRESS LIKE '% г.%' or b2.ADDRESS LIKE '% ,г. %' or b2.ADDRESS LIKE '% ,г %' THEN 0 ELSE 1 END) Popular," +
" COUNT(DISTINCT b2.client_id) AllCNT" +
" FROM BTAL2 b2" +
$" WHERE b2.DREG BETWEEN '{dateFrom}' AND '{dateTo}'" +
" AND b2.ADDRESS <> ''" +
" AND b2.deleted = 0" +
" GROUP BY 1";
using (MySqlConnection sqlConnection = ConnectionClass.GetStringConnectionTable())
{
if (sqlConnection.State == ConnectionState.Closed)
{
sqlConnection.Open();
}
MySqlCommand sqlCommand = new MySqlCommand(query, sqlConnection);
ReportTable.Load(sqlCommand.ExecuteReader());
}
return(ReportTable);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Ошибка", MessageBoxButtons.OK, MessageBoxIcon.Error);
logger.Debug("\n/--------------------------------------------------------------------/\n" + ex.StackTrace + "\n//----------------------------//\n" + ex.Message + "\n\n");
return(null);
}
}
public static DataTable LoadBeremCertif(string dateFrom, string dateTo)
{
DataTable CertifBerem = new DataTable();
try
{
string query = "SELECT CONCAT(SCERTIF, ' ', NCERTIF) CERTIF," +
" IF(DCERTIF = '00.00.0000', '', DATE_FORMAT(DCERTIF, '%d.%m.%Y')) DCERTIF," +
" SNILS," +
" CONCAT(LNAME, ' ', FNAME, ' ', MNAME) FIO_berem," +
" DATE_FORMAT(BDATE, '%d.%m.%Y') BDATE," +
" ADDRESS," +
" CONCAT('Серия ', SDOC, ', номер ', NDOC, ', дата выдачи ', DATE_FORMAT(DDOC, '%d.%m.%Y'), ', выдан ', ODOC) DocType," + //" IF(SDOC <> '' and NDOC <> '' and DDOC <> '00.00.0000' and ODOC <> '', CONCAT('Серия ', SDOC, ', номер ', NDOC, ', дата выдачи ', DATE_FORMAT(DDOC, '%d.%m.%Y'), ', выдан ', ODOC), '') DocType," +
" IF(SPOLICY = '', NPOLICY, CONCAT(SPOLICY, ' ', NPOLICY)) POLICY," +
" IF(SLEAF = '' AND NLEAF = '', '', CONCAT(SLEAF, ' ', NLEAF, ', выдан ', DLEAF)) SICKLIST," +
" DATE_FORMAT(DREG, '%d.%m.%Y') DREG," +
" IF(QTW < 20, 0, QTW - 19) fromQTW," +
" QTW," +
" client_id," +
" id" +
" FROM BTAL1 b1" +
$" where DCERTIF between '{dateFrom}' AND '{dateTo}'" +
" and deleted = 0";
using (MySqlConnection sqlConnection = ConnectionClass.GetStringConnectionTable())
{
if (sqlConnection.State == ConnectionState.Closed)
{
sqlConnection.Open();
}
MySqlCommand sqlCommand = new MySqlCommand(query, sqlConnection);
CertifBerem.Load(sqlCommand.ExecuteReader());
}
return(CertifBerem);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Ошибка", MessageBoxButtons.OK, MessageBoxIcon.Error);
logger.Debug("\n/--------------------------------------------------------------------/\n" + ex.StackTrace + "\n//----------------------------//\n" + ex.Message + "\n\n");
return(null);
}
}
private void FuncUpdateMother()
{
try
{
string Mother_query = $"UPDATE BTAL2 SET DREG = '{dateDREG.Value.ToString("yyyy-MM-dd")}', DIAG = '{tbDIAG.Text}', BQNT = {tbBQNT.Text}, SCERTIF = '{tbSCERTIF.Text}', NCERTIF = '{tbNCERTIF.Text}', DCERTIF = '{(tbSCERTIF.Text == "" && tbNCERTIF.Text == "" ? "" : dateDCERTIF.Value.ToString("yyyy-MM-dd"))}', " +
$" SNILS = '{tbSNILS.Text}', LNAME = '{tbLNAME.Text}', FNAME = '{tbFNAME.Text}', MNAME = '{tbMNAME.Text}', BDATE = '{dateBDATE.Value.ToString("yyyy-MM-dd")}', ADDRESS = '{tbADDRESS.Text}', TDOC = {cbTypeDoc.SelectedIndex}, SDOC = '{tbSDOC.Text}', " +
$" NDOC = '{tbNDOC.Text}', DDOC = '{(cbTypeDoc.SelectedIndex == 0 && tbNDOC.Text == "" && tbSDOC.Text == "" ? "" : dateDDOC.Value.ToString("yyyy-MM-dd"))}', ODOC = '{tbODOC.Text}', SPOLICY = '{tbSPOLICY.Text}', NPOLICY = '{tbNPOLICY.Text}', SLEAF = '{tbSLEAF.Text}', " +
$" NLEAF = '{tbNLEAF.Text}', DLEAF = '{(tbSLEAF.Text.Length > 0 || tbNLEAF.Text.Length > 0 ? dateDLEAF.Value.ToString("yyyy-MM-dd") : "")}', NCARD = '{tbNCARD.Text}', DCARD = '{(tbNCARD.Text.Length > 0 ? dateDCARD.Value.ToString("yyyy-MM-dd") : "")}'" +
$" WHERE client_id = {client_id} and id = {forTableUPD.Rows[0][25].ToString()} and deleted = 0";
using (MySqlConnection sqlConnection = ConnectionClass.GetStringConnectionTable())
{
if (sqlConnection.State == ConnectionState.Closed)
{
sqlConnection.Open();
}
MySqlCommand sqlCommand = new MySqlCommand(Mother_query, sqlConnection);
sqlCommand.ExecuteNonQuery();
}
if (oldCertif != $"{tbSCERTIF.Text + " " + tbNCERTIF.Text}")
{
string Child_query = $"UPDATE CHILD SET motherCertif = '{tbSCERTIF.Text + " " + tbNCERTIF.Text}'" +
$" WHERE mother_id = {client_id} and motherCertif = '{oldCertif}' and deleted = 0";
using (MySqlConnection sqlConnection = ConnectionClass.GetStringConnectionTable())
{
if (sqlConnection.State == ConnectionState.Closed)
{
sqlConnection.Open();
}
MySqlCommand sqlCommand = new MySqlCommand(Child_query, sqlConnection);
sqlCommand.ExecuteNonQuery();
}
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Ошибка", MessageBoxButtons.OK, MessageBoxIcon.Error);
logger.Debug("\n/--------------------------------------------------------------------/\n" + ex.StackTrace + "\n//----------------------------//\n" + ex.Message + "\n\n");
}
}
private void FuncCheckChild()
{
try
{
for (int i = 0; i < dgvCHILD.Rows.Count; i++)
{
string checkChild = $"select exists(select id from CHILD where id = {dgvCHILD[0, i].Value} and mother_id = {client_id} and deleted = 0)"; //SEX = '{dgvCHILD[1, i].Value}' and WEIGHT = '{dgvCHILD[2, i].Value}' and GROWTH = {dgvCHILD[3, i].Value} and DIAG = '{dgvCHILD[4, i].Value}'
using (MySqlConnection sqlConnection = ConnectionClass.GetStringConnectionTable())
{
if (sqlConnection.State == ConnectionState.Closed)
{
sqlConnection.Open();
}
using (MySqlCommand sqlCommand = new MySqlCommand(checkChild, sqlConnection))
{
using (MySqlDataReader reader = sqlCommand.ExecuteReader())
{
reader.Read();
if (reader.GetInt32(0) == 0)
{
FuncMINIInsertChild(i);
}
else
{
FuncMINIUpdateChild(i);
}
}
}
sqlConnection.Close();
}
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Ошибка", MessageBoxButtons.OK, MessageBoxIcon.Error);
logger.Debug("\n/--------------------------------------------------------------------/\n" + ex.StackTrace + "\n//----------------------------//\n" + ex.Message + "\n\n");
}
}
private void FuncMINIUpdateMother()
{
try
{
string Mother_query = $"UPDATE BTAL2 SET BQNT = {tbBQNT.Text}" +
$" WHERE client_id = {client_id} and id = {forTableUPD.Rows[0][25].ToString()} and deleted = 0";
using (MySqlConnection sqlConnection = ConnectionClass.GetStringConnectionTable())
{
if (sqlConnection.State == ConnectionState.Closed)
{
sqlConnection.Open();
}
MySqlCommand sqlCommand = new MySqlCommand(Mother_query, sqlConnection);
sqlCommand.ExecuteNonQuery();
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Ошибка", MessageBoxButtons.OK, MessageBoxIcon.Error);
logger.Debug("\n/--------------------------------------------------------------------/\n" + ex.StackTrace + "\n//----------------------------//\n" + ex.Message + "\n\n");
}
}
public static DataTable FuncChildUPD(int client_id, string motherCertif)
{
DataTable childTable = new DataTable();
try
{
string queryCH = "select id," +
" SEX," +
" WEIGHT," +
" GROWTH," +
" DIAG" +
" FROM CHILD" +
$" where mother_id = {client_id}" +
$" and motherCertif = '{motherCertif}'" +
" and deleted = 0";
using (MySqlConnection sqlConnection = ConnectionClass.GetStringConnectionTable())
{
if (sqlConnection.State == ConnectionState.Closed)
{
sqlConnection.Open();
}
MySqlCommand sqlCommand = new MySqlCommand(queryCH, sqlConnection);
childTable.Load(sqlCommand.ExecuteReader());
sqlConnection.Close();
}
return(childTable);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Ошибка", MessageBoxButtons.OK, MessageBoxIcon.Error);
logger.Debug("\n/--------------------------------------------------------------------/\n" + ex.StackTrace + "\n//----------------------------//\n" + ex.Message + "\n\n");
return(null);
}
}
public static void LoadTableBTAL2(string queryMother, string queryChild)
{
try
{
var defSet = Properties.Settings.Default;
xmlTableM = new DataTable();
xmlTableC = new DataTable();
using (MySqlConnection sqlConnection = ConnectionClass.GetStringConnectionTable())
{
if (sqlConnection.State == ConnectionState.Closed)
{
sqlConnection.Open();
}
MySqlCommand sqlCommand = new MySqlCommand(queryMother, sqlConnection);
xmlTableM.Load(sqlCommand.ExecuteReader());
sqlConnection.Close();
sqlConnection.Open();
sqlCommand = new MySqlCommand(queryChild, sqlConnection);
xmlTableC.Load(sqlCommand.ExecuteReader());
}
using (XmlTextWriter writer = new XmlTextWriter($@"C:\Users\{Environment.UserName}\Desktop\BTAL2_{DateTime.Now.Date.ToShortDateString()}.xml", Encoding.GetEncoding(1251)))
{
writer.Formatting = Formatting.Indented;
writer.Indentation = 1;
writer.IndentChar = '\t';
writer.WriteStartDocument();
writer.WriteProcessingInstruction("BSERTIF2", "version=\"0.1\" povers=\"10\"");
writer.WriteStartElement("BIRTH_SERTIF");
{
//Данные больницы добавить в настройки по умолчанию.
writer.WriteStartElement("HEADER");
writer.WriteAttributeString("FROM", $"{defSet.Name_LPU}");
writer.WriteAttributeString("TO", $"{defSet.Name_FSS}");
writer.WriteAttributeString("DATE", $"{DateTime.Now.ToString("yyyy.MM.dd")}");
writer.WriteAttributeString("INN", $"{defSet.INN_LPU}");
writer.WriteAttributeString("REG_NUM", $"{defSet.RegNumb_LPU}");
writer.WriteAttributeString("KPP", $"{defSet.KPP_LPU}");
writer.WriteAttributeString("OGRN", $"{defSet.OGRN_LPU}");
writer.WriteAttributeString("ADRES", $"{defSet.Address_LPU}");
writer.WriteAttributeString("TYPE", $"{defSet.Type_LPU}");
writer.WriteAttributeString("GDOCTER", $"{defSet.OwnDoc_LPU}");
writer.WriteAttributeString("GBUH", $"{defSet.OwnBookKeep}");
writer.WriteAttributeString("ACC", $"{defSet.RS_LPU}");
writer.WriteAttributeString("BNAME", $"{defSet.BankName_LPU}");
writer.WriteAttributeString("BIC", $"{defSet.BIK_LPU}");
writer.WriteAttributeString("KACC", $"{defSet.KS_LPU}");
writer.WriteAttributeString("NDOC", $"{defSet.DocNumb_FSS}");
writer.WriteAttributeString("DDOC", $"{defSet.DocDate_FSS}");
writer.WriteEndElement();
for (int i = 0; i < xmlTableM.Rows.Count; i++)
{
writer.WriteStartElement("BTAL2");
writer.WriteAttributeString("DREG", $"{xmlTableM.Rows[i][0].ToString()}");
writer.WriteAttributeString("DIAG", $"{xmlTableM.Rows[i][1].ToString()}");
writer.WriteAttributeString("BQNT", $"{xmlTableM.Rows[i][2].ToString()}");
writer.WriteAttributeString("CHK", $"{xmlTableM.Rows[i][3].ToString()}");
{
writer.WriteStartElement("CERTIF");
writer.WriteAttributeString("SCERTIF", $"{xmlTableM.Rows[i][4].ToString()}");
writer.WriteAttributeString("NCERTIF", $"{xmlTableM.Rows[i][5].ToString()}");
writer.WriteAttributeString("DCERTIF", $"{xmlTableM.Rows[i][6].ToString()}");
writer.WriteEndElement();
writer.WriteStartElement("PERSON");
writer.WriteAttributeString("SNILS", $"{xmlTableM.Rows[i][7].ToString()}");
writer.WriteAttributeString("LNAME", $"{xmlTableM.Rows[i][8].ToString()}");
writer.WriteAttributeString("FNAME", $"{xmlTableM.Rows[i][9].ToString()}");
writer.WriteAttributeString("MNAME", $"{xmlTableM.Rows[i][10].ToString()}");
writer.WriteAttributeString("BDATE", $"{xmlTableM.Rows[i][11].ToString()}");
writer.WriteAttributeString("ADDRESS", $"{xmlTableM.Rows[i][12].ToString()}");
writer.WriteEndElement();
writer.WriteStartElement("PASSPORT");
writer.WriteAttributeString("TDOC", $"{xmlTableM.Rows[i][13].ToString()}");
writer.WriteAttributeString("SDOC", $"{xmlTableM.Rows[i][14].ToString()}");
writer.WriteAttributeString("NDOC", $"{xmlTableM.Rows[i][15].ToString()}");
writer.WriteAttributeString("DDOC", $"{xmlTableM.Rows[i][16].ToString()}");
writer.WriteAttributeString("ODOC", $"{xmlTableM.Rows[i][17].ToString()}");
writer.WriteEndElement();
writer.WriteStartElement("POLICY");
writer.WriteAttributeString("SPOLICY", $"{xmlTableM.Rows[i][18].ToString()}");
writer.WriteAttributeString("NPOLICY", $"{xmlTableM.Rows[i][19].ToString()}");
writer.WriteEndElement();
writer.WriteStartElement("SICKLIST");
writer.WriteAttributeString("SLEAF", $"{xmlTableM.Rows[i][20].ToString()}");
writer.WriteAttributeString("NLEAF", $"{xmlTableM.Rows[i][21].ToString()}");
writer.WriteAttributeString("DLEAF", $"{xmlTableM.Rows[i][22].ToString()}");
writer.WriteEndElement();
writer.WriteStartElement("EXCCARD");
writer.WriteAttributeString("NCARD", $"{xmlTableM.Rows[i][23].ToString()}");
writer.WriteAttributeString("DCARD", $"{xmlTableM.Rows[i][24].ToString()}");
writer.WriteEndElement();
for (int j = 0; j < xmlTableC.Rows.Count; j++)
{
if (xmlTableM.Rows[i][25].ToString() == xmlTableC.Rows[j][0].ToString())
{
writer.WriteStartElement("CHILD");
writer.WriteAttributeString("SEX", $"{xmlTableC.Rows[j][1].ToString()}");
writer.WriteAttributeString("WEIGHT", $"{xmlTableC.Rows[j][2].ToString()}");
writer.WriteAttributeString("GROWTH", $"{xmlTableC.Rows[j][3].ToString()}");
writer.WriteAttributeString("DIAG", $"{xmlTableC.Rows[j][4].ToString()}");
writer.WriteEndElement();
}
}
}
writer.WriteEndElement();
}
}
writer.WriteEndElement();
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Ошибка", MessageBoxButtons.OK, MessageBoxIcon.Error);
logger.Debug("\n/--------------------------------------------------------------------/\n" + ex.StackTrace + "\n//----------------------------//\n" + ex.Message + "\n\n");
}
}