public string GetAttractionInfo(HttpRequestMessage request)
{
try
{
var content = request.Content;
string j = content.ReadAsStringAsync().Result;
Admin admin = new Admin();
admin = JsonConvert.DeserializeObject <Admin>(j);
if (admin.check())
{
SqlConn conn = new SqlConn();
AttractionsInfo attractionsInfo = new AttractionsInfo();
attractionsInfo.GetAttractionsInfo();
if (attractionsInfo.Attractions != null &&
attractionsInfo.AttractionTypes != null)
{
return(JsonConvert.SerializeObject(attractionsInfo));
}
}
return(null);
}
catch (Exception exc)
{
Console.WriteLine(exc.ToString());
logger.Error(exc.ToString());
return(null);
}
}
public string UpdateSoftZoneJule(HttpRequestMessage request)
{
try
{
var content = request.Content;
string j = content.ReadAsStringAsync().Result;
AttractionInfoRequest attractionInfoRequest = new AttractionInfoRequest();
attractionInfoRequest = JsonConvert.DeserializeObject <AttractionInfoRequest>(j);
if (attractionInfoRequest.admin.check())
{
SqlConn conn = new SqlConn();
AttractionsInfoResponce attractionsInfo = new AttractionsInfoResponce();
List <Pair> pairs = new List <Pair>();
pairs.Add(new Pair("attractions", JsonConvert.SerializeObject(attractionInfoRequest.attractions)));
pairs.Add(new Pair("active", attractionInfoRequest.active));
conn.update("sales_info", "sales_name='4sale'", pairs);
attractionsInfo.attractions = conn.select12SaleSalesInfo("sales_info", "sales_name='4sale'").attractions;
attractionsInfo.active = conn.select12SaleSalesInfo("sales_info", "sales_name='4sale'").active;
return(JsonConvert.SerializeObject(attractionsInfo));
}
return(null);
}
catch (Exception exc)
{
Console.WriteLine(exc.ToString());
logger.Error(exc.ToString());
return(null);
}
}
public string GetAdmins(HttpRequestMessage request)
{
try
{
var content = request.Content;
string j = content.ReadAsStringAsync().Result;
Admin admin = new Admin();
admin = JsonConvert.DeserializeObject <Admin>(j);
if (admin.check())
{
SqlConn conn = new SqlConn();
List <Admin> admins = new List <Admin>();
admins = Admin.getAdmins(admin);
if (admins != null)
{
return(JsonConvert.SerializeObject(admins));
}
}
return(null);
}
catch (Exception exc)
{
Console.WriteLine(exc.ToString());
logger.Error(exc.ToString());
return(null);
}
}
public string GetCheckedSoftZoneJule(HttpRequestMessage request)
{
try
{
var content = request.Content;
string j = content.ReadAsStringAsync().Result;
Admin admin = new Admin();
admin = JsonConvert.DeserializeObject <Admin>(j);
if (admin.check())
{
SqlConn conn = new SqlConn();
AttractionsInfoResponce attractionsInfo = new AttractionsInfoResponce();
attractionsInfo.attractions = conn.select12SaleSalesInfo("sales_info", "sales_name='4sale'").attractions;
attractionsInfo.active = conn.select12SaleSalesInfo("sales_info", "sales_name='4sale'").active;
return(JsonConvert.SerializeObject(attractionsInfo));
}
return(null);
}
catch (Exception exc)
{
Console.WriteLine(exc.ToString());
logger.Error(exc.ToString());
return(null);
}
}
public void AddProducts(List <Product> products)
{
try
{
SqlConn.Open();
using (SqlCommand sqlCommand = SqlConn.CreateCommand())
{
sqlCommand.CommandType = CommandType.StoredProcedure;
sqlCommand.CommandText = InsertProductsSp;
DataTable productsDataTable = products.ToDataTable(p => new
{
p.Name,
p.SupplierId,
p.CategoryId,
MeasureUnit = ColumnSettings.Build(() => p.MeasureUnit).Name("Quantity Per Unit"),
UnitPrice = p.Price * 1.5m,
p.UnitsInStock,
p.UnitsOnOrder,
p.ReorderLevel,
p.Discontinued
});
sqlCommand.Parameters.AddWithValue("@Products", productsDataTable);
sqlCommand.ExecuteNonQuery();
}
}
finally
{
SqlConn.Close();
}
}
public List <Product> GetTopExpensiveProducts(int count)
{
List <Product> products = new List <Product>();
try
{
SqlConn.Open();
using (SqlCommand sqlCommand = SqlConn.CreateCommand())
{
sqlCommand.CommandText = GetTopExpensiveProductsSql;
sqlCommand.Parameters.AddWithValue("@Count", count);
SqlDataReader dataReader = sqlCommand.ExecuteReader();
while (dataReader.Read())
{
var product = new Product()
{
Id = dataReader.GetInt32(0),
Price = dataReader.GetDecimal(1)
};
products.Add(product);
}
}
}
finally
{
SqlConn.Close();
}
return(products);
}
public static Usuario BuscarPorId(int id)
{
Usuario usuario;
using (SqlConnection conn = SqlConn.Abrir())
{
using (SqlCommand cmd = new SqlCommand("SELECT Id, Nome, Email FROM Usuario WHERE Id = @id", conn))
{
cmd.Parameters.AddWithValue("@id", id);
using (SqlDataReader reader = cmd.ExecuteReader())
{
if (reader.Read() == true)
{
string nome = reader.GetString(1);
string email = reader.GetString(2);
usuario = new Usuario();
usuario.Id = id;
usuario.Nome = nome;
usuario.Email = email;
}
else
{
usuario = null;
}
}
}
}
return(usuario);
}
private void SetButchersLabels()
{
var sqlConn = new SqlConn(DbConnectionString());
try
{
if (sqlConn.TestConnection())
{
var sqlDatatable = new SqlDataTable();
var dt = new DataTable();
var sqlQuery = string.Empty;
sqlQuery = "SELECT * FROM tblCustomer";
dt = SqlDataTable.GetDatatable(sqlConn.GetSqlConnection(), sqlQuery);
SetControlsLookUpEdit(lookUpEdit_Customer, dt, "CustomerName", "IdCustomer", "Customer");
sqlQuery = "SELECT * FROM tblShift";
dt = SqlDataTable.GetDatatable(sqlConn.GetSqlConnection(), sqlQuery);
SetControlsLookUpEdit(lookUpEdit_Shift, dt, "Shift", "IdShift", "Shift");
lookUpEdit_Product.Enabled = false;
}
else
{
throw new Exception("Unexpected error when tried to connect to server and download data.");
}
}
catch (Exception Ex)
{
var message = string.Format(Ex.Message);
var title = "Database connection";
XtraMessageBox.Show(message, title, MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
protected void Delete(int IdUsuario, int IdPersona)
{
try
{
OpenConnection();
SqlTransaction transaction = SqlConn.BeginTransaction();
SqlCommand cmdDelete;
try
{
cmdDelete = new SqlCommand("DELETE usuarios WHERE id_usuario=@id", SqlConn, transaction);
cmdDelete.Parameters.Add("@id", SqlDbType.Int).Value = IdUsuario;
cmdDelete.ExecuteNonQuery();
cmdDelete = new SqlCommand("DELETE personas WHERE id_persona=@id", SqlConn, transaction);
cmdDelete.Parameters.Add("@id", SqlDbType.Int).Value = IdPersona;
cmdDelete.ExecuteNonQuery();
transaction.Commit();
}
catch (Exception ex)
{
transaction.Rollback();
Exception ExcepcionManejada = new Exception("Error al eliminar usuario y persona", ex);
throw ExcepcionManejada;// Agregar mensaje de error
}
}
catch (Exception)
{
throw;
}
finally
{
CloseConnection();
}
}
public string checkLogin()
{
SqlConn conn = new SqlConn();
Admin admin = conn.selectAdmin("admins", "login='******'");
if (admin != null && admin.id > 0)
{
if ((DateTime.Now.Date != admin.keyTime.Date) ||
(admin.privateKey == "") ||
(admin.publicKey == ""))
{
List <Pair> parameters = new List <Pair>();
parameters.Add(new Pair {
key = "private_key", value = RSA.ToXmlString(true)
});
parameters.Add(new Pair {
key = "public_key", value = RSA.ToXmlString(false)
});
parameters.Add(new Pair {
key = "time_key_gen", value = DateTime.Now
});
conn.update("admins", "login='******'", parameters);
return(RSA.ToXmlString(false));
}
else
{
RSA.FromXmlString(admin.privateKey);
RSA.FromXmlString(admin.privateKey);
return(RSA.ToXmlString(false));
}
}
return(null);
}
public void teamjoinreq()
{
string recipientfna = "";
string recipientlna = "";
SqlConn.uid2name(uid, ref recipientfna, ref recipientlna);
string recipientem = SqlConn.uid2email(uid);
//get user's email and name via user table
string tname = SqlConn.tid2name(tid);
//get team's name from its tid
string cname = "";
SqlConn.uid2name(cuid, ref cname);
//get coach's name from his uid
string subject = "TeamSocl, " + recipientfna + " " + recipientlna
+ " would like to join team " + tname;
string body = "Hello " + cname + ", " + recipientfna + " " + recipientlna
+ " would like to join team " + tname + ". Please login to the "
+ "TeamSocl app and confirm or deny this user's request to "
+ "join your team. Thanks!";
mail.emailer("*****@*****.**", recipientem, subject, body);
}
public void GetAttractionsInfo()
{
SqlConn conn = new SqlConn();
this.Attractions = conn.selectAttractions();
this.AttractionTypes = conn.selectAttractionTypes();
}
public static string connInfo()
{
DataTable dt = GlobalClass.connPool;
int[] cnt = new int[2];
cnt[0] = 0;
cnt[1] = 0;
if (dt == null)
{
return("");
}
string ret = "";
for (int i = 0; i < dt.Rows.Count; i++)
{
DBConn conn = dt.Rows[i]["engine"] as DBConn;
SqlConn sconn = (SqlConn)conn;
ret += sconn._conn.ConnectionString;
if (sconn._cmd != null)
{
ret += "," + GlobalClass.str(sconn._cmd.CommandText).Replace("\r\n", "");
}
ret += ", " + (conn.isOpen() ? "opened" : "closed") + "\r\n";
}
return(ret);
}
public bool addCashierRegister()
{
SqlConn conn = new SqlConn();
IPAddress ipAddress;
int id;
if (IPAddress.TryParse(this.cashierRegisterIP.ToString(), out ipAddress) &&
Int32.TryParse(this.cashierRegisterId.ToString(), out id))
{
if (conn.selectCashierRegister("cashierregister", "cashierregister_id='" + id + "'").cashierRegisterId == null &&
conn.selectCashierRegister("cashierregister", "ip='" + this.cashierRegisterIP.ToString() + "'").cashierRegisterIP == null)
{
List <Pair> parameters = new List <Pair>();
parameters.Add(new Pair {
key = "cashierregister_id", value = this.cashierRegisterId
});
parameters.Add(new Pair {
key = "ip", value = this.cashierRegisterIP.ToString()
});
parameters.Add(new Pair {
key = "time_last_ping", value = DateTime.Now
});
var res = conn.insert("cashierregister", parameters);
if (res)
{
return(true);
}
}
}
return(false);
}
public void GetCashforCardsReport(DateTime fromDate, DateTime toDate)
{
SqlConn sqlConn = new SqlConn();
cashForCardReports = new List <CashForCardReport>();
List <TransactionCashRegister> transactionsCashRegister = new List <TransactionCashRegister>();
transactionsCashRegister = sqlConn.selectTransactionCashRegister("transactions_cashiermashine",
" date BETWEEN '" + fromDate.ToString("dd.MM.yyyy", System.Globalization.CultureInfo.InvariantCulture) + " 0:00:00' AND '" + toDate.ToString("dd.MM.yyyy", System.Globalization.CultureInfo.InvariantCulture) + " 23:59:59'");
if (transactionsCashRegister != null && transactionsCashRegister.Count > 0)
{
TotalSold = 0;
TotalSoldCount = 0;
foreach (TransactionCashRegister transactionCashRegister in transactionsCashRegister)
{
if (transactionCashRegister.operation == 22)
{
Card card = new Card();
card = sqlConn.selectCard("cards", "card_id='" + transactionCashRegister.cardId + "'");
if (card != null && card.id > 0)
{
cashForCardReports.Add(new CashForCardReport(transactionCashRegister.cardId,
card.cardParentName,
transactionCashRegister.cashier_id,
transactionCashRegister.cashier_name,
transactionCashRegister.cashier_register_id,
transactionCashRegister.summ,
transactionCashRegister.date));
TotalSold += transactionCashRegister.summ;
TotalSoldCount++;
}
}
}
}
}
private void SetControlsLookUpEditProductList()
{
var sqlConn = new SqlConn(DbConnectionString());
var sett = Properties.Settings.Default;
int factoryId = sett.Factory;
string customerId = lookUpEdit_Customer.EditValue.ToString();
string sqlQuery = "SELECT * FROM v_ButcherLabelsProductList WHERE IdCustomer=" + customerId + " AND IdFactory=" + factoryId + "";
var dt = new DataTable();
dt = SqlDataTable.GetDatatable(sqlConn.GetSqlConnection(), sqlQuery);
lookUpEdit_Product.Enabled = true;
lblColorLabel.Text = string.Empty;
var control = lookUpEdit_Product.Properties;
control.DataSource = null;
control.DataSource = dt;
control.DisplayMember = "Description";
control.ValueMember = "ProdCode";
control.AppearanceDropDown.FontSizeDelta = 10;
control.AppearanceDropDownHeader.FontSizeDelta = 10;
LookUpColumnInfoCollection columns = control.Columns;
columns.Clear();
columns.Add(new LookUpColumnInfo("ProdCode", 150, "Code"));
columns.Add(new LookUpColumnInfo("Description", 400, "Description"));
columns.Add(new LookUpColumnInfo("CustomerName", 120, "Customer"));
columns.Add(new LookUpColumnInfo("LabelType", 100, "Label"));
}
public void GetReceivedCardReport(DateTime fromDate, DateTime toDate)
{
SqlConn conn = new SqlConn();
List <TransactionCashRegister> transactionsCashRegister = conn.selectTransactionCashRegister("transactions_cashiermashine",
" date BETWEEN '" + fromDate.ToString("dd.MM.yyyy", System.Globalization.CultureInfo.InvariantCulture) + " 0:00:00' AND '"
+ toDate.ToString("dd.MM.yyyy", System.Globalization.CultureInfo.InvariantCulture) + " 23:59:59'");
this.receivedCards = new List <ReceivedCard>();
foreach (TransactionCashRegister transactionCashRegister in transactionsCashRegister)
{
if (((int)transactionCashRegister.operation >= 5 && (int)transactionCashRegister.operation <= 7) || (int)transactionCashRegister.operation == 22)
{
CardStatus cardStatus = conn.selectCardStatus("card_state", "state_id='" + transactionCashRegister.operation + "'");
receivedCards.Add(
new ReceivedCard(
transactionCashRegister.date,
(int)transactionCashRegister.cashier_register_id,
(string)transactionCashRegister.cashier_name,
transactionCashRegister.cardId,
cardStatus.status_message,
(decimal)transactionCashRegister.summ,
(decimal)transactionCashRegister.bonus,
(int)transactionCashRegister.tickets
));
}
}
}
/// <summary>
///
/// </summary>
public void OpenConnection()
{
if (SqlConn.State != ConnectionState.Open)
{
SqlConn.Open();
}
}
public void jointeamack()
{
// send an email off to the player, saying that 'cname' has accepted and you're part of 'teamname'. He/she will now recieve messages related to this team.
string recipientfna = "";
SqlConn.uid2name(uid, ref recipientfna);
string recipientem = SqlConn.uid2email(uid);
//get user's email and name via user table
string tname = SqlConn.tid2name(tid);
//get team's name from its tid
string cname = "";
SqlConn.uid2name(cuid, ref cname);
//get coach's name from his uid
string subject = "TeamSocl, the " + tname + "s have accepted you!";
string body = "Hello " + recipientfna + ", the " + tname + "s have "
+ "accepted you! Please login to the TeamSocl app to review "
+ "up and coming team events. Thanks!";
mail.emailer("*****@*****.**", recipientem, subject, body);
}
/// <summary>
///
/// </summary>
public void BeginTransac()
{
if (SqlConn.State != ConnectionState.Open)
{
SqlConn.Open();
}
SqlTransac = SqlConn.BeginTransaction();
}
public bool changeCashierInfo()
{
try
{
SqlConn conn = new SqlConn();
Cashier cashierOld = conn.selectCashier("cashiers", "cashier_id='" + cashier.cashierId + "'");
if (cashierOld != null)
{
if (cardInfoString != null)
{
var matches = Regex.Matches(cardInfoString, @"([0-9])+");
string cardId = matches[1].ToString();
License license = Server.checkLicenseFile();
if (license.licenseCompanyCode.ToString() == matches[0].ToString())
{
if (Card.licenseCheck(cardInfoString))
{
List <Pair> parameters = new List <Pair>();
if (conn.selectCard("cards", "card_id='" + cardId + "'") == null)
{
cashier.cashierCardId = Card.registerCashierCard(cardInfoString, cashier.cashierName.ToString()).cardId;
parameters.Add(new Pair {
key = "FIO", value = cashier.cashierName
});
parameters.Add(new Pair {
key = "card_id", value = cashier.cashierCardId
});
if (conn.update("cashiers", "cashier_id='" + cashier.cashierId + "'", parameters))
{
return(true);
}
}
}
}
}
else
{
List <Pair> parameters = new List <Pair>();
if (conn.selectCard("cards", "card_id='" + cashier.cashierCardId + "'") != null)
{
parameters.Add(new Pair {
key = "FIO", value = cashier.cashierName
});
if (conn.update("cashiers", "cashier_id='" + cashier.cashierId + "'", parameters))
{
return(true);
}
}
}
}
return(false);
}
catch
{
return(false);
}
}
public static List <Admin> getAdmins(Admin admin)
{
if (admin.check())
{
SqlConn conn = new SqlConn();
return(conn.selectAdmins());
}
return(null);
}
public Sync()
{
SqlConn conn = new SqlConn();
connString = conn.connString;
SqlConnection sqlConnection = new SqlConnection(connString);
provsisonDB(sqlConnection);
}
private void GetDataFromSI(string barCode)
{
var sqlConn = new SqlConn(SIConnectionString());
DataTable dt = new DataTable();
try
{
if (sqlConn.TestConnection())
{
var sqlQuery = string.Empty;
string batchPallet = string.Empty;
switch (barCode.Length)
{
case 14:
batchPallet = barCode.Substring(2);
_batchOrPallet = SqlQueryBatchPallet.PalletBatchField.palletid;
sqlQuery = SqlQueryBatchPallet.SelectPalletBatch(_batchOrPallet, batchPallet);
break;
case 22:
batchPallet = barCode.Substring(0, 12);
_batchOrPallet = SqlQueryBatchPallet.PalletBatchField.batchno;
sqlQuery = SqlQueryBatchPallet.SelectPalletBatch(_batchOrPallet, batchPallet);
break;
default:
batchPallet = barCode;
_batchOrPallet = SqlQueryBatchPallet.PalletBatchField.batchno;
sqlQuery = SqlQueryBatchPallet.SelectPalletBatch(_batchOrPallet, batchPallet);
break;
}
if (_butcherLabelsTable != null)
{
dt = SqlDataTable.GetDatatable(sqlConn.GetSqlConnection(), sqlQuery);
_butcherLabelsTable.Merge(dt);
}
else
{
dt = SqlDataTable.GetDatatable(sqlConn.GetSqlConnection(), sqlQuery);
_butcherLabelsTable = dt;
}
AddDataToGridView(dt);
}
else
{
throw new Exception("Unexpected error when tried to connect to SI database and download data.");
}
}
catch (Exception ex)
{
var message = string.Format(ex.Message);
var title = "SI database connection";
XtraMessageBox.Show(message, title, MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
/// <summary>
/// Gets patient list from the database
/// </summary>
/// <param name="cmdText"></param>
/// <param name="error"></param>
/// <returns></returns>
static private List <PatientData> GetPatientData(string cmdText, out int error)
{
List <PatientData> patList = null;
SqlDataReader reader = null;
error = -1;
try
{
if (OpenConnection())
{
SqlCommand command = new SqlCommand(cmdText, SqlConn);
command.CommandType = CommandType.Text;
reader = command.ExecuteReader();
if (reader != null)
{
patList = new List <PatientData>();
int colPatID = reader.GetOrdinal("ID");
int colMrn = reader.GetOrdinal("MRN");
int colFirstN = reader.GetOrdinal("FIRSTNAME");
int colLastN = reader.GetOrdinal("LASTNAME");
while (reader.Read())
{
PatientData patData = new PatientData();
patData.PatientId = reader.GetValue(colPatID).ToString();
patData.ExternalId = reader.GetValue(colMrn).ToString();
if (!reader.IsDBNull(colFirstN))
{
patData.FirstName = reader.GetValue(colFirstN).ToString();
}
if (!reader.IsDBNull(colFirstN))
{
patData.LastName = reader.GetValue(colLastN).ToString();
}
patList.Add(patData);
}
}
error = 0;
SqlConn.Close();
}
}
catch (Exception)
{
error = -1;
if (SqlConn != null)
{
SqlConn.Close();
}
}
return(patList);
}
/// <summary>
/// Dispose object DBM
/// </summary>
public void Dispose()
{
GC.SuppressFinalize(this);
CloseConnection();
if (null != SqlConn)
{
SqlConn.Dispose();
}
}
/// <summary>
///
/// </summary>
public void CloseConnection()
{
if (SqlTransac == null)
{
if (SqlConn.State != ConnectionState.Closed)
{
SqlConn.Close();
}
}
}
// Формує БД та таблички (Використовується у методі InsertToDataBase)
// conn.ExecuteConnection
// Метод що заповнює данними таблички
// conn.InsertToDataBase
// Метод що повертає всі Id з таблиць tblCity
// GetCountOfCities
// Детальніше про методи можна прочитати при наведенні на них мишкою
static void Main()
{
Console.OutputEncoding = Encoding.Unicode;
Console.InputEncoding = Encoding.Unicode;
string strConn = "Data Source=serverpu816.database.windows.net;Initial Catalog=test;Persist Security Info=True;User ID=pu816;Password=Qwerty1*";
SqlConn conn = new SqlConn(strConn);
conn.InsertToDataBase();
}
private void XmlDataForReport()
{
string sqlQuery = "SELECT ProductionDate, ProdDescription, RawMaterialDescription, Customer, Shift, LabelDescription as Color, Lot, PalletId, Udf2, Udf3, Udf4, KillDate, LabelBatchNumber AS Batch FROM tblButcherLabelsData WHERE ProductionDate = '09/05/2017' AND Customer='Lidl' AND Shift='Dayshift' AND ProdCode='LD5204354' AND FactoryId='1'";
DataTable dt = new DataTable();
var sqlConn = new SqlConn(DbConnectionString());
dt = SqlDataTable.GetDatatable(sqlConn.GetSqlConnection(), sqlQuery);
dt.TableName = "tblPalletBatch";
dt.WriteXmlSchema(@"C:\Users\krzysztof.matyja\Dropbox\Employee365 Projects\Traceability\PalletBatch.xsd");
}
/// <summary>
/// 用于执行增加和删除语句
/// </summary>
/// <param name="sql">sql语句</param>
/// <param name="parameter">参数化查询</param>
/// <returns>有多少语句执行成功</returns>
public int ExecuteNonQuery(string sql, params SqlParameter[] parameter)
{
using (SqlCommand cmd = SqlConn.CreateCommand())
{
SqlConn.Open();
cmd.CommandText = sql;
cmd.Parameters.AddRange(parameter);
return(cmd.ExecuteNonQuery());
}
}
public void showChart()
{
SqlConn sc = new SqlConn();
int roomnum = 0;
string typename;
string cmdText = "select * from V_Typenum";
SqlDataAdapter adapter = new SqlDataAdapter(cmdText,sc.getConn());
DataSet dataSet = new DataSet();
adapter.Fill(dataSet);
for(int i=0;i<dataSet.Tables[0].Rows.Count;i++)
{
typename = dataSet.Tables[0].Rows[i][0].ToString().Trim();
roomnum = int.Parse(dataSet.Tables[0].Rows[i][1].ToString().Trim());
chart.Series[0].Points.AddXY(typename , roomnum);
chart.Series[0].Color = Color.SkyBlue;
chart.Series[0].Label = "#VAL";
}
adapter.Dispose();
}
