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