private void LoadDBDefault() { try { using (MySqlConnection sqlConnection = ConnectionClass.GetStringConnection()) { if (sqlConnection.State == ConnectionState.Closed) { sqlConnection.Open(); } MySqlCommand sqlCommand = new MySqlCommand("SHOW DATABASES", sqlConnection); MySqlDataReader reader = sqlCommand.ExecuteReader(); dbName.Clear(); while (reader.Read()) { dbName.Add(reader.GetString(0)); } cbDB.DataSource = null; cbDB.DataSource = dbName; } } 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 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); } }
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"); } }