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");
}
}
public static DataTable FuncLoad(string dateFrom, string dateTo)
{
DataTable womens = new DataTable();
try
{
string query = "SELECT DISTINCT e.client_id client_id," +
" z_getClientFIO(e.client_id) fio," +
" z_getClientRodDocument(e.client_id) rodCertif," +
" z_getClientRodDocumentDate(e.client_id) dateCertif," +
" z_getClientRodDocumentWhoGive(e.client_id) whoGiveCertif" +
" FROM Event e" +
" WHERE e.deleted = 0" +
" AND (e.execDate IS NULL OR e.execDate >= DATE_ADD(NOW(), INTERVAL -1 MONTH))" +
$" AND e.setDate BETWEEN '{dateFrom} 00:00:00' AND '{dateTo} 23:59:59'" +
" AND e.eventType_id = 60";
using (MySqlConnection sqlConnection = ConnectionClass.GetStringConnection())
{
if (sqlConnection.State == ConnectionState.Closed)
{
sqlConnection.Open();
}
MySqlCommand sqlCommand = new MySqlCommand(query, sqlConnection);
womens.Load(sqlCommand.ExecuteReader());
}
return(womens);
}
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 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);
}
}
private void FuncMINIInsertChild(int i)
{
try
{
string 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");
}
}
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 FuncINSRT(int client_id)
{
DataTable woman = new DataTable();
try
{
string query = "SELECT IF(lastName is NULL, '', lastName) LNAME," +
" IF(firstName is null, '', firstName) FNAME," +
" IF(patrName is null, '', patrName) MNAME," +
" IF(birthDate is null, CURRENT_DATE(), birthDate) BDATE," +
" IF(getClientRegAddress(c.id) is null, '', IF ( getClientRegAddress(c.id) LIKE '% обл, %' OR getClientRegAddress(c.id) LIKE '% край, %' OR getClientRegAddress(c.id) LIKE '% респ, %', " +
" CONCAT(" +
" LEFT(UPPER(getClientRegAddress(c.id)), 1)," +
" SUBSTRING(UPPER(getClientRegAddress(c.id))," +
" LOCATE(' ', getClientRegAddress(c.id)) + 1, 1)," +
" ', '," +
" SUBSTRING(getClientRegAddress(c.id), LOCATE(',', getClientRegAddress(c.id)) + 1, LENGTH(getClientRegAddress(c.id))))," +
" IF(getClientRegAddress(c.id) LIKE '% ао, %' AND getClientRegAddress(c.id) LIKE '%-%'," +
" CONCAT(" +
" LEFT(UPPER(getClientRegAddress(c.id)), 1)," +
" SUBSTRING(UPPER(getClientRegAddress(c.id)), LOCATE('-', getClientRegAddress(c.id)) + 1, 1)," +
" SUBSTRING(UPPER(getClientRegAddress(c.id))," +
" LOCATE(' ', getClientRegAddress(c.id)) + 1, 2)," +
" ', '," +
" SUBSTRING(getClientRegAddress(c.id), LOCATE(',', getClientRegAddress(c.id)) + 1, LENGTH(getClientRegAddress(c.id))))," +
" IF(getClientRegAddress(c.id) LIKE '% ао, %'," +
" CONCAT(" +
" LEFT(UPPER(getClientRegAddress(c.id)), 1)," +
" SUBSTRING(UPPER(getClientRegAddress(c.id))," +
" LOCATE(' ', getClientRegAddress(c.id)) + 1, 2)," +
" ', '," +
" SUBSTRING(getClientRegAddress(c.id), LOCATE(',', getClientRegAddress(c.id)) + 1, LENGTH(getClientRegAddress(c.id)))), getClientRegAddress(c.id))))) address," +
" IF(c.SNILS is null, '', c.SNILS) SNILS," +
" IF(cp.serial is null, '', cp.serial) SPOLICY," +
" IF(cp.number is null, '', cp.number) NPOLICY," +
" IF(dt.regionalCode is null, 0, dt.regionalCode) TDOC," +
" IF(cd.serial is null, '', cd.serial) SDOC," +
" IF(cd.number is null, '', cd.number) NDOC," +
" IF(date_FORMAT(cd.date, '%d.%m.%Y') IS NULL, CURRENT_DATE(), date_FORMAT(cd.date, '%d.%m.%Y')) DDOC," +
" IF(cd.origin is null, '', cd.origin) ODOC," +
" IF(rc.serial is null, '', rc.serial) SCERTIF," +
" IF(rc.number is null, '', rc.number) NCERTIF," +
" IF(date_FORMAT(rc.date, '%d.%m.%Y') IS NULL, CURRENT_DATE(), date_FORMAT(rc.date, '%d.%m.%Y')) DCERTIF," +
" IF(ti.serial IS NULL, '', ti.serial) SLEAF," +
" IF(ti.number IS NULL, '', ti.number) NLEAF," +
" IF(date_FORMAT(ti.issueDate,'%d.%m.%Y') IS NULL, CURRENT_DATE(), date_FORMAT(ti.issueDate,'%d.%m.%Y')) DLEAF" +
" FROM Client c" +
" LEFT JOIN ClientPolicy cp ON cp.id = getClientPolicyID(c.id, 1)" +
" LEFT JOIN ClientDocument cd ON cd.id = getClientDocumentId(c.id)" +
" LEFT JOIN ClientDocument rc ON c.id = rc.client_id AND rc.documentType_id = 22 AND rc.id = (SELECT MAX(rc1.id) FROM ClientDocument rc1 WHERE rc1.client_id = c.id AND rc1.documentType_id = 22)" +
" left JOIN rbDocumentType dt ON cd.documentType_id = dt.id" +
" LEFT JOIN TempInvalid ti ON c.id = ti.client_id AND ti.doctype = 0" +
$" WHERE c.id = {client_id}";
using (MySqlConnection sqlConnection = ConnectionClass.GetStringConnection())
{
if (sqlConnection.State == ConnectionState.Closed)
{
sqlConnection.Open();
}
MySqlCommand sqlCommand = new MySqlCommand(query, sqlConnection);
woman.Load(sqlCommand.ExecuteReader());
}
return(woman);
}
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");
}
}
private void FuncLoad()
{
try
{
tableBerem = new DataTable();
query_str = "";
if (lastName != "")
{
query_str += lastName;
}
if (firstName != "")
{
query_str += firstName;
}
if (patrName != "")
{
query_str += patrName;
}
if (strDBorn != "")
{
query_str += strDBorn;
}
if (snils != "")
{
if (maskSNILS.Text.Length < 11)
{
MessageBox.Show("Проверьте корректность номера СНИЛС", "Внимание", MessageBoxButtons.OK, MessageBoxIcon.Warning);
return;
}
else
{
query_str += snils;
}
}
string query = "SELECT DISTINCT c.id," +
" c.lastName LName," +
" c.firstName FName," +
" c.patrName MName," +
" DATE_FORMAT(c.birthDate, '%d.%m.%Y') dateBorn," +
" IF(c.SNILS = '', '', CONCAT(SUBSTRING(c.SNILS, 1, 3), '-', SUBSTRING(c.SNILS, 4, 3), '-', SUBSTRING(c.SNILS, 7, 3), ' ', SUBSTRING(c.SNILS, 10, 2))) SNILS," +
" z_getClientRodDocument(c.id) rodCertif," +
" z_getClientRodDocumentDate(c.id) dateCertif," +
" z_getClientRodDocumentWhoGive(c.id) whoGiveCertif" +
" FROM Client c" +
" JOIN Event e ON c.id = e.client_id" +
" JOIN ClientIdentification ci ON c.id = ci.client_id" +
" WHERE c.sex = 2" +
" AND e.deleted = 0" +
$" AND e.eventType_id in ({Properties.Settings.Default.eventType_id})" +
" AND ci.accountingSystem_id = 11" +
$" {query_str}";
/*string query = "SELECT c.id," +
* " c.lastName LName," +
* " c.firstName FName," +
* " c.patrName MName," +
* " DATE_FORMAT(c.birthDate, '%d.%m.%Y') dateBorn," +
* " IF(c.SNILS = '', '', CONCAT(SUBSTRING(c.SNILS, 1, 3), '-', SUBSTRING(c.SNILS, 4, 3), '-', SUBSTRING(c.SNILS, 7, 3), ' ', SUBSTRING(c.SNILS, 10, 2))) SNILS," +
* " CONCAT(cd.serial, ' ', cd.number) rodCertif," +
* " date_FORMAT(cd.date, '%d.%m.%Y') dateCertif," +
* " cd.origin whoGiveCertif," +
* " age(c.birthDate, CURRENT_DATE()) age_" +
* " FROM Client c" +
* " LEFT JOIN ClientDocument cd ON cd.client_id = c.id AND cd.documentType_id = 22 AND cd.deleted = 0" +
* $" WHERE c.sex = {sex}" +
* $" {query_str}" +
* " HAVING age_ BETWEEN 10 AND 60";*/
using (MySqlConnection sqlConnection = ConnectionClass.GetStringConnection())
{
if (sqlConnection.State == ConnectionState.Closed)
{
sqlConnection.Open();
}
MySqlCommand sqlCommand = new MySqlCommand(query, sqlConnection);
tableBerem.Load(sqlCommand.ExecuteReader());
}
dataBerem.AutoGenerateColumns = false;
dataBerem.DataSource = tableBerem;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Ошибка", MessageBoxButtons.OK, MessageBoxIcon.Error);
logger.Debug("\n/--------------------------------------------------------------------/\n" + ex.StackTrace + "\n//----------------------------//\n" + ex.Message + "\n\n");
}
}
