//////////////////////////////////////////////////////////////////////////////////////////////////
/// <summary>
/// Добавляеит нового клиента в базу
/// </summary>
/// <param name="cl"></param>
public void AddClient(ClientOLD cl)
{
try
{
string sql = "";
using (var con = new SqlConnection(strCon.ConnectionString))
{
con.Open();
if (cl.Status == "COMPANY")
{
sql = $"INSERT dbo.Company(" +
"CompName," +
"CreateDay," +
"INN,Finance," +
"BankRating," +
"ClStatus," +
"Adress," +
"ActivLevel," +
"FirstTrans)" +
$"VALUES(N'{cl.FName}'," +
$"'{cl.BrDay.ToString("yyyy-MM-dd")}'," +
$"N'{cl.NumDoc}'," +
$"{cl.Finance.ToInt32()}," +
$"{cl.Rating.ToInt32()}," +
$"N'{cl.Status}'," +
$"N'{cl.Adress}'," +
$"{0}," +
$"N'{DateTime.Now.ToShortTimeString()}')";
}
else
{
sql = $"INSERT dbo.Client(FName, LName, BrthDay, Passport, Finance, BankRating, ClStatus, Adress, WorkPlace, ActivLevel, FrstTrans)" +
$"VALUES(N'{cl.FName}'," +
$"N'{cl.LName}', " +
$"'{cl.BrDay.ToString("yyyy-MM-dd")}', " +
$"N'{cl.NumDoc}', " +
$"{cl.Finance}, " +
$"{cl.Rating.ToInt32()}, " +
$"N'{cl.Status}'," +
$"N'{cl.Adress}'," +
$"N'{cl.WorkPlace}', " +
$"0," +
$"'{DateTime.Now.ToShortTimeString()}')";
}
var command = new SqlCommand(sql, con);
command.ExecuteNonQuery();
MessageBox.Show("Add");
}
}
catch (Exception e) { MessageBox.Show(e.Message); }
}
/// <summary>
/// Получаем клиента из базы данных
/// </summary>
/// <param name="status">Статус клиента</param>
/// <param name="num">номер документа</param>
/// <returns></returns>
public ClientOLD GetClientFromDB(string status, string num)
{
ClientOLD cl = new ClientOLD();
List <string> li = new List <string>();
string sql = "";
try
{
using (var con = new SqlConnection(strCon.ConnectionString))
{
con.Open();
if (status == "COMPANY")
{
sql = $"SELECT * FROM dbo.Company WHERE INN = N'{num}'";
var com = new SqlCommand(sql, con);
var res = com.ExecuteReader();
while (res.Read())
{
cl = new ClientOLD(res[cd.Inn].ToString(), ///string numDoc
res[cd.CompStatus].ToString(), ///string status
res[cd.CompName].ToString(), /// string fName
res[cd.Compfinance].ToString(), /// string finance
res[cd.CompAdress].ToString(), /// string adress
res[cd.CompRating].ToString(), /// string rating
Convert.ToDateTime(res[cd.CompCreateDate])); /// DateTime brDay
}
}
else
{
sql = $"SELECT * FROM dbo.Client WHERE Passport = N'{num}'";
var com = new SqlCommand(sql, con);
var res = com.ExecuteReader();
while (res.Read())
{
cl = new ClientOLD(res[cd.Passport].ToString(), /// string numDoc,
res[cd.Status].ToString(), ///string status,
res[cd.Name].ToString(), ///string fName,
res[cd.Lname].ToString(), ///string lName,
res[cd.Finance].ToString(), ///string finance,
res[cd.Adress].ToString(), ///string adress,
res[cd.Rating].ToString(), ///string rating,
res[cd.WorkPlace].ToString(), ///string workPlace,
Convert.ToDateTime(res[cd.BrhDay])); ///DateTime brDay
}
}
}
}
catch (Exception e) { MessageBox.Show(e.Message); }
return(cl);
}
/// <summary>
/// Изменяет данные клиента
/// </summary>
/// <param name="cl">Клиент с изменнёными данными</param>
/// <param name="oldnum">номер документа до обновления</param>
public void ChangeClient(ClientOLD cl, string oldnum)
{
try
{
string sql = "";
using (var con = new SqlConnection(strCon.ConnectionString))
{
con.Open();
cl.Finance.Replace(',', '.');
if (cl.Status == "COMPANY")
{
sql = $"UPDATE Company SET " +
$"CompName = N'{cl.FName}'," +
$" CreateDay = '{cl.BrDay.ToString("yyyy-MM-dd")}'," +
$" INN = '{cl.NumDoc}', " +
$" Finance = '{cl.Finance.Replace(',', '.')}', " +
$" BankRating = {cl.Rating}, " +
$"ClStatus = '{cl.Status}', " +
$"Adress = N'{cl.Adress}' " +
$" WHERE INN = '{oldnum}'";
}
else
{
sql = $"UPDATE dbo.Client SET FName = N'{cl.FName}'," +
$" LName = N'{cl.LName}'," +
$" BrthDay = '{cl.BrDay.ToString("yyyy-MM-dd")}'," +
$" Passport = '{cl.NumDoc}', " +
$" Finance = '{cl.Finance.Replace(',', '.')}', " +
$" BankRating = {cl.Rating}, " +
$"ClStatus = '{cl.Status}', " +
$"Adress = N'{cl.Adress}', " +
$"WorkPlace = N'{cl.WorkPlace}'" +
$" WHERE Passport = '{oldnum}'";
}
var command = new SqlCommand(sql, con);
command.ExecuteNonQuery();
MessageBox.Show("Change");
}
}
catch (Exception e) { MessageBox.Show(e.Message); }
}
