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