public int getRefundRate(int contractId)
{
SqlConnection connection;
SqlCommand command;
string sql = "dbo.spGetRefundRate";
int result;
try
{
connection = new ConnectDB().connectToSQL();
command = new SqlCommand(sql, connection);
SqlParameter contractIdParameter = new SqlParameter("@contractId", SqlDbType.Int);
SqlParameter outputParameter = new SqlParameter("@refundRate", SqlDbType.TinyInt);
contractIdParameter.Direction = ParameterDirection.Input;
contractIdParameter.Value = contractId;
outputParameter.Direction = ParameterDirection.ReturnValue;
command.Parameters.Add(contractIdParameter);
command.Parameters.Add(outputParameter);
command.CommandType = CommandType.StoredProcedure;
command.ExecuteNonQuery();
result = int.Parse(outputParameter.Value.ToString());
}
catch (Exception)
{
throw;
}
connection.Close();
return(result);
}
public int getAgeDiscout(DateTime DOB)
{
SqlConnection connection;
SqlCommand command;
string sql = "dbo.spGetAgeDiscount";
int result;
try
{
connection = new ConnectDB().connectToSQL();
command = new SqlCommand(sql, connection);
SqlParameter quantityParameter = new SqlParameter("@DOB", SqlDbType.Date);
SqlParameter outputParameter = new SqlParameter("@ageDiscount", SqlDbType.TinyInt);
quantityParameter.Direction = ParameterDirection.Input;
quantityParameter.Value = DOB;
outputParameter.Direction = ParameterDirection.ReturnValue;
command.Parameters.Add(quantityParameter);
command.Parameters.Add(outputParameter);
command.CommandType = CommandType.StoredProcedure;
command.ExecuteNonQuery();
result = int.Parse(outputParameter.Value.ToString());
}
catch (Exception)
{
throw;
}
connection.Close();
return(result);
}
public double getDouble()
{
double SendDbl = 0;
SqlConnection Conn = new ConnectDB().SqlStrCon(this._Conn);
Conn.Open();
SqlCommand Cmd = new SqlCommand(this._strQuery, Conn);
SqlDataReader dr = Cmd.ExecuteReader(CommandBehavior.CloseConnection);
if (dr.HasRows)
{
dr.Read();
if (dr.GetValue(0).ToString() == "")
{
SendDbl = 0;
}
else
{
SendDbl = Convert.ToDouble(dr.GetValue(0));
}
}
else
{
SendDbl = 0;
}
dr.Close();
Conn.Close();
return(SendDbl);
}
public bool insertTour(Tour tour)
{
bool inserted = false;
string sql = "INSERT INTO tbTour VALUES (N'" + tour.StartLocation;
sql += "', N'" + tour.Destination;
sql += "', '" + tour.StartDate + "', ";
sql += "'" + tour.EndDate + "',";
sql += " " + tour.TotalSeat + ", " + tour.EmptySeat + ", " + tour.Price + ")";
SqlConnection connection;
SqlCommand command;
try
{
connection = new ConnectDB().connectToSQL();
command = new SqlCommand(sql, connection);
if (command.ExecuteNonQuery() == 1)
{
inserted = true;
}
}
catch (Exception)
{
throw;
}
connection.Close();
return(inserted);
}
public bool insertMember(Member member)
{
bool inserted = false;
SqlConnection connection;
SqlCommand command;
string sql = "INSERT INTO tbMember(ContractId, Name, DOB, Identification, AgeDiscount, MemberPrice) VALUES(";
sql += member.ContracId;
// sql += ", " + member.MemberId;
sql += ", N'" + member.Name;
sql += "','" + member.Dob + "','" + member.Identification + "',";
sql += member.AgeDiscount + "," + member.MemberPrice + ")";
try
{
connection = new ConnectDB().connectToSQL();
command = new SqlCommand(sql, connection);
if (command.ExecuteNonQuery() >= 1)
{
inserted = true;
}
}
catch (Exception)
{
throw;
}
connection.Close();
return(inserted);
}
public bool insertCustomer(Customer customer)
{
bool inserted = false;
SqlConnection connection;
SqlCommand command;
string sql = "INSERT INTO tbCustomer VALUES (N'" + customer.Name;
sql += "', CONVERT(DATE, '" + customer.Dob + "', 103),";
sql += " '" + customer.PhoneNumber + "', '" + customer.Identification + "', '" + customer.AccountNumber + "', N'" + customer.Address + "')";
try
{
connection = new ConnectDB().connectToSQL();
command = new SqlCommand(sql, connection);
if (command.ExecuteNonQuery() == 1)
{
inserted = true;
}
}
catch (Exception)
{
throw;
}
connection.Close();
return(inserted);
}
public bool updateCustomer(Customer customer)
{
bool updated = false;
SqlConnection connection;
SqlCommand command;
string sql = "UPDATE tbCustomer SET Name = N'" + customer.Name;
sql += "', DOB = CONVERT(DATE, '" + customer.Dob + "', 103),";
sql += " PhoneNumber = '" + customer.PhoneNumber;
sql += "', Identification = '" + customer.Identification;
sql += "', AccountNumber = '" + customer.AccountNumber + "', Address = N'" + customer.Address;
sql += "' WHERE CustomerId = " + customer.CustomerId;
try
{
connection = new ConnectDB().connectToSQL();
command = new SqlCommand(sql, connection);
if (command.ExecuteNonQuery() == 1)
{
updated = true;
}
}
catch (Exception)
{
throw;
}
connection.Close();
return(updated);
}
public bool updateTour(Tour tour)
{
bool updated = false;
SqlConnection connection;
SqlCommand command;
string sql = "UPDATE tbTour SET StartLocation = N'" + tour.StartLocation;
sql += "', Destination = N'" + tour.Destination;
sql += "', StartDate = CONVERT(DATE, '" + tour.StartDate + "', 103)";
sql += ", EndDate = CONVERT(DATE, '" + tour.EndDate + "', 103)";
sql += ", TotalSeat = " + tour.TotalSeat + ", EmptySeat = " + tour.EmptySeat + ", Price = " + tour.Price;
sql += " WHERE TourId = " + tour.TourId;
try
{
connection = new ConnectDB().connectToSQL();
command = new SqlCommand(sql, connection);
if (command.ExecuteNonQuery() == 1)
{
updated = true;
}
}
catch (Exception)
{
throw;
}
connection.Close();
return(updated);
}
public bool insertContract(Contract contract)
{
bool inserted = false;
SqlConnection connection;
SqlCommand command;
string sql = "INSERT INTO dbo.tbContract(CustomerID, TourID, DraftDate, TerminationDate, StartTime, Quantity, QuantityDiscount, TotalMoney, Deposit, Note) VALUES(";
sql += contract.CustomerId;
sql += ", " + contract.TourId + ", ";
sql += "GETDATE(),";
sql += "CONVERT(DATETIME, '" + contract.TemimationDate + "', 103), ";
sql += "CONVERT(DATETIME, '" + contract.StartTime + "', 103), ";
sql += contract.Quantity + ", 0, " + contract.TotalMoney + "," + contract.Deposit + ", N'" + contract.Note + "')";
try
{
connection = new ConnectDB().connectToSQL();
command = new SqlCommand(sql, connection);
if (command.ExecuteNonQuery() >= 1)
{
inserted = true;
}
}
catch (Exception)
{
throw;
}
connection.Close();
return(inserted);
}
public DataTable getDataTable()
{
SqlConnection Conn = new ConnectDB().SqlStrCon(this._Conn);
Conn.Open();
SqlCommand Cmd = new SqlCommand(this._strQuery, Conn);
DataTable dt = new DataTable();
dt.Load(Cmd.ExecuteReader());
Conn.Close();
//Clone DataTable
DataTable dtCloned = dt.Clone();
int y = 0;
foreach (DataColumn col in dt.Columns)
{
dtCloned.Columns[y].DataType = typeof(String);
dtCloned.Columns[y].ReadOnly = false;
y++;
}
foreach (DataRow row in dt.Rows)
{
dtCloned.ImportRow(row);
}
return(dtCloned);
}
public int SqlExecute(string sql)
{
int i;
SqlConnection conn = new ConnectDB().SqlStrCon();
SqlCommand cmd = new SqlCommand(sql, conn);
conn.Open();
i = cmd.ExecuteNonQuery();
conn.Close();
return(i);
}
public static int ExecuteDataReturnID(string sql)
{
int i;
SqlConnection conn = new ConnectDB().connection();
SqlCommand cmd = new SqlCommand(sql, conn);
conn.Open();
i = (int)cmd.ExecuteScalar();
conn.Close();
return(i);
}
public DataSet getDataSet()
{
SqlConnection Conn = new ConnectDB().SqlStrCon(this._Conn);
SqlCommand Cmd = new SqlCommand(this._strQuery, Conn);
SqlDataAdapter da = new SqlDataAdapter(Cmd);
DataSet ds = new DataSet();
Conn.Open();
da.Fill(ds, "setTable");
Conn.Close();
return(ds);
}
public static int ExecuteData(string sql, SqlParameterCollection parameters)
{
int i;
SqlConnection conn = new ConnectDB().connection();
SqlCommand cmd = new SqlCommand(sql, conn);
foreach (SqlParameter param in parameters)
{
cmd.Parameters.AddWithValue(param.ParameterName, param.SqlDbType).Value = param.Value;
}
conn.Open();
i = cmd.ExecuteNonQuery();
conn.Close();
return(i);
}
public DataTable getValidTourWhere(List <string> whereArgs)
{
string sql = "SELECT* FROM tbTour";
if (whereArgs[0].ToLower() == "true")
{
sql += " WHERE StartLocation LIKE N'" + whereArgs[1] + "' AND Destination LIKE N'" + whereArgs[2] + "'";
}
//if (whereArgs[3].ToLower() == "true")
//{
// if (sql.Contains("WHERE"))
// {
// sql += " AND StartDate >= CONVERT(DATE, '" + whereArgs[4] + "', 103) AND EndDate <= CONVERT(DATE, '" + whereArgs[5] + "', 103)";
// }
// else
// {
// sql += " WHERE StartDate >= CONVERT(DATE, '" + whereArgs[4] + "', 103) AND EndDate <= CONVERT(DATE, '" + whereArgs[5] + "', 103)";
// }
//}
if (whereArgs[6].ToLower() == "true")
{
if (sql.Contains("WHERE"))
{
sql += " AND Price >= " + Utils.eraiseComma(whereArgs[7]) + " AND Price <= " + Utils.eraiseComma(whereArgs[8]);
}
else
{
sql += " WHERE Price >= " + Utils.eraiseComma(whereArgs[7]) + " AND Price <= " + Utils.eraiseComma(whereArgs[8]);
}
}
sql += " AND StartDate > CONVERT(DATE, '" + DateTime.Now + "', 103) ORDER BY StartDate ASC";
SqlConnection connection;
SqlDataAdapter adapter = new SqlDataAdapter();
DataSet dataset = new DataSet();
try
{
connection = new ConnectDB().connectToSQL();
adapter.SelectCommand = new SqlCommand(sql, connection);
adapter.Fill(dataset);
}
catch (Exception)
{
throw;
}
connection.Close();
return(dataset.Tables[0]);
}
private void Report2_DoWork(object sender, DoWorkEventArgs e)
{
SortedSet <DateTime> dates = e.Argument as SortedSet <DateTime>;
DataTable Summ = new DataTable(); //итоговая таблица
List <string> Names = new List <string>(); //будет хранить имена счетчиков
foreach (Expense ex in this.exs)
{
if (ex.Type.Equals("Электричество"))
{
Summ.Columns.Add(new DataColumn(ex.Name));
Names.Add(ex.Name);
}
}
ConnectDB.Open();
foreach (DateTime dti in dates)
{
string query = string.Format("SELECT Наименование, Показания, РасходФакт, Расход_план FROM Данные WHERE {0}='{1}'", "Дата", dti.ToString());
OleDbDataAdapter cmd = new OleDbDataAdapter(query, ConnectDB);
DataSet dt = new DataSet();
cmd.Fill(dt, "Данные");//0
DataRow dr = Summ.NewRow();
foreach (DataColumn dc in Summ.Columns)
{
dr[dc.ColumnName] = "";
}
foreach (DataRow row in dt.Tables[0].Rows)
{
string name = row.ItemArray[0].ToString();
if (Names.Contains(name))
{
//расходФАкт, показания, расходплан
dr[name] += row.ItemArray[2] + ";" + row.ItemArray[1] + ";" + row.ItemArray[3];
}
}
Summ.Rows.Add(dr);
}
ConnectDB.Close();
this.Report2_Excel(Summ, dates);
}
public List <hotel> fetchHotels()
{
SqlDataReader reader = this.reader();
var hotelList = new List <hotel>();
while (reader.Read())
{
var hotelMap = new hotel();
hotelMap.id = reader.GetInt32(hotel.COLUMN_ID_INDEX);
hotelMap.name = reader.GetString(hotel.COLUMN_NAME_INDEX);
hotelMap.rating = reader.GetByte(hotel.COLUMN_RATING_INDEX);
hotelMap.status = reader.GetString(hotel.COLUMN_STATUS_INDEX);
hotelList.Add(hotelMap);
}
db.Close();
return(hotelList);
}
//##### RETURN VALUES QUERY #####
public bool runSQL()
{
try
{
SqlConnection Conn = new ConnectDB().SqlStrCon(this._Conn);
SqlCommand Cmd = new SqlCommand(this._strQuery, Conn);
//new Function().msgInfo(this._strQuery);
Conn.Open();
Cmd.ExecuteNonQuery();
Conn.Close();
return(true);
}
catch (Exception ex)
{
;
new Function().msgError("ERROR # " + ex.ToString());
return(false);
}
}
/// <summary>
///
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void Report1_DoWork(object sender, DoWorkEventArgs e)
{
DataTable Summ = new DataTable();//итоговая таблица
Summ.Columns.Add(new DataColumn("Наименование"));
Summ.Columns.Add(new DataColumn("Цена"));
Summ.Columns.Add(new DataColumn("План")); //кол-во по плану
Summ.Columns.Add(new DataColumn("СтП")); //стоимость по плану
Summ.Columns.Add(new DataColumn("Факт")); //кол-во по факту
Summ.Columns.Add(new DataColumn("СтФ")); //стоимость по факту
Summ.Columns.Add(new DataColumn("%")); //процент выполнения
ConnectDB.Open();
foreach (Expense ex in this.exs)
{
if (ex.Type.Equals("Материал"))
{
string query = string.Format("SELECT Дата, РасходФакт, Расход_план FROM Данные WHERE {0}='{1}'", "Наименование", ex.Name);
OleDbDataAdapter cmd = new OleDbDataAdapter(query, ConnectDB);
DataSet dt = new DataSet();
cmd.Fill(dt, "Данные");//0
double fsummary = 0.0, psummary = 0.0;
foreach (DataRow dr in dt.Tables[0].Rows)
{
fsummary += Double.Parse(dr.ItemArray[1].ToString().Replace('.', ','));
if (dr.ItemArray[2].ToString().Length == 0)
{
continue;
}
psummary += Double.Parse(dr.ItemArray[2].ToString().Replace('.', ','));
}
Summ.Rows.Add(ex.Name, ex.Price, psummary, (ex.Price * psummary).ToString(), fsummary, (ex.Price * fsummary).ToString(), "0");
}
}
ConnectDB.Close();
this.Report1_Excel(Summ);
}
public DataTable getValidContract()
{
string sql = "SELECT* FROM tbContract WHERE TerminationDate >= GETDATE() and State = 1";
SqlConnection connection;
SqlDataAdapter adapter = new SqlDataAdapter();
DataSet dataset = new DataSet();
try
{
connection = new ConnectDB().connectToSQL();
adapter.SelectCommand = new SqlCommand(sql, connection);
adapter.Fill(dataset);
}
catch (Exception)
{
throw;
}
connection.Close();
return(dataset.Tables[0]);
}
public DataTable getValidTour()
{
string sql = "SELECT* FROM tbTour WHERE StartDate > GETDATE() AND EmptySeat > 0 ORDER BY StartDate ASC";
SqlConnection connection;
SqlDataAdapter adapter = new SqlDataAdapter();
DataSet dataset = new DataSet();
try
{
connection = new ConnectDB().connectToSQL();
adapter.SelectCommand = new SqlCommand(sql, connection);
adapter.Fill(dataset);
}
catch (Exception)
{
throw;
}
connection.Close();
return(dataset.Tables[0]);
}
public int getPriceTourByContractId(int contractId)
{
SqlConnection connection;
SqlDataAdapter adapter = new SqlDataAdapter();
DataSet dataset = new DataSet();
string sql = "SELECT Price FROM dbo.tbContract INNER JOIN dbo.tbTour ON tbTour.TourId = tbContract.TourId WHERE ContractId = " + contractId;
try
{
connection = new ConnectDB().connectToSQL();
adapter.SelectCommand = new SqlCommand(sql, connection);
adapter.Fill(dataset);
}
catch (Exception)
{
throw;
}
connection.Close();
return(int.Parse(dataset.Tables[0].Rows[0][0].ToString()));
}
public DataTable getAllTour()
{
string sql = "SELECT* FROM tbTour ORDER BY TourId DESC";
SqlConnection connection;
SqlDataAdapter adapter = new SqlDataAdapter();
DataSet dataset = new DataSet();
try
{
connection = new ConnectDB().connectToSQL();
adapter.SelectCommand = new SqlCommand(sql, connection);
adapter.Fill(dataset);
}
catch (Exception)
{
throw;
}
connection.Close();
return(dataset.Tables[0]);
}
public int getInsertedId()
{
SqlConnection connection;
SqlDataAdapter adapter = new SqlDataAdapter();
DataSet dataset = new DataSet();
string sql = "SELECT TOP 1 TourId FROM dbo.tbTour ORDER BY TourId DESC";
try
{
connection = new ConnectDB().connectToSQL();
adapter.SelectCommand = new SqlCommand(sql, connection);
adapter.Fill(dataset);
}
catch (Exception)
{
throw;
}
connection.Close();
return(int.Parse(dataset.Tables[0].Rows[0][0].ToString()));
}
public DataTable getCustomerByPhoneNumber(string phoneNumber)
{
SqlConnection connection;
SqlDataAdapter adapter = new SqlDataAdapter();
DataSet dataset = new DataSet();
string sql = "SELECT* FROM tbCustomer WHERE PhoneNumber = " + phoneNumber;
try
{
connection = new ConnectDB().connectToSQL();
adapter.SelectCommand = new SqlCommand(sql, connection);
adapter.Fill(dataset);
}
catch (Exception)
{
throw;
}
connection.Close();
return(dataset.Tables[0]);
}
public DataTable getAccidentsByContract(int contractId)
{
string sql = "SELECT* FROM tbAccident WHERE ContractId = " + contractId + " ORDER BY AccidentId DESC";
SqlConnection connection;
SqlDataAdapter adapter = new SqlDataAdapter();
DataSet dataset = new DataSet();
try
{
connection = new ConnectDB().connectToSQL();
adapter.SelectCommand = new SqlCommand(sql, connection);
adapter.Fill(dataset);
}
catch (Exception)
{
throw;
}
connection.Close();
return(dataset.Tables[0]);
}
public DataTable getUserByName(User user)
{
string sql = "SELECT* FROM tbUsers WHERE UserName = N'" + user.UserName + "'";
SqlConnection connection;
SqlDataAdapter adapter = new SqlDataAdapter();
DataSet dataset = new DataSet();
try
{
connection = new ConnectDB().connectToSQL();
adapter.SelectCommand = new SqlCommand(sql, connection);
adapter.Fill(dataset);
}
catch (Exception)
{
throw;
}
connection.Close();
return(dataset.Tables[0]);
}
public bool getCheck()
{
bool SendChk = false;
SqlConnection Conn = new ConnectDB().SqlStrCon(this._Conn);
Conn.Open();
SqlCommand Cmd = new SqlCommand(this._strQuery, Conn);
SqlDataReader dr = Cmd.ExecuteReader(CommandBehavior.CloseConnection);
if (dr.HasRows)
{
SendChk = true;
}
else
{
SendChk = false;
}
Conn.Close();
return(SendChk);
}
public bool deleteTour(int tourId)
{
bool deleted = false;
SqlConnection connection;
SqlCommand command;
string sql = "DELETE FROM tbTour WHERE TourId = " + tourId;
try
{
connection = new ConnectDB().connectToSQL();
command = new SqlCommand(sql, connection);
if (command.ExecuteNonQuery() > 1)
{
deleted = true;
}
}
catch (Exception)
{
throw;
}
connection.Close();
return(deleted);
}
//public bool backupSQL()
//{
// try
// {
// SqlConnection Conn = new ConnectDB().SqlStrCon();
// SqlCommand Cmd = new SqlCommand(this._strQuery, Conn);
// //new Function().msgInfo(this._strQuery);
// Conn.Open();
// Cmd.ExecuteNonQuery();
// Conn.Close();
// return true;
// }
// catch (Exception ex)
// {
// new Function().msgError("ERROR # " + ex.ToString());
// return false;
// }
//}
public string getString()
{
string SendStr = "";
SqlConnection Conn = new ConnectDB().SqlStrCon(this._Conn);
Conn.Open();
SqlCommand Cmd = new SqlCommand(this._strQuery, Conn);
SqlDataReader dr = Cmd.ExecuteReader(CommandBehavior.CloseConnection);
if (dr.HasRows)
{
dr.Read();
SendStr = dr.GetValue(0).ToString();
}
else
{
SendStr = "";
}
dr.Close();
Conn.Close();
return(SendStr);
}