//Automatic selection of DB, project and Working Layers
public async Task OnDatabaseSelectedAutomatically()
{
var projectdata = (ViewModels.ProjectData)BindingContext;
CDatabase database = null;
for (int i = 0; i < PickerDatabase.Items.Count; i++)
{
string myDBName = PickerDatabase.Items.ElementAt(i).ToString();
if (myDBName == comos.Constants.defaultDB)
{
PickerDatabase.SelectedIndex = i;
database = PickerDatabase.SelectedItem as CDatabase;
break;
}
}
//var database = PickerDatabase.SelectedItem as CDatabase;
this.ShowSpinner(Services.TranslateExtension.TranslateText("Cargando Base de Datos..."));
if (database != null)
{
await projectdata.OpenDatabase(database);
PickerProject.SelectedItem = null;
projectdata.Layers = new CWorkingLayer[0];
//ListViewLayers.ItemsSource
StackBreadcrumb.Children.Clear();
}
this.HideSpinner();
}
void bgw_Init_DoWork(object sender, DoWorkEventArgs e)
{
/* 如果参数中传入了字符串，说明加载完Sequence Names以后切换到字符串指定的Sequence */
string seq_name_to_display;
if (e.Argument != null)
{
seq_name_to_display = e.Argument.ToString();
}
else
{
seq_name_to_display = "";
}
/* 清空列表，启动Busy进度条 */
bgw_Init.ReportProgress(1);
/* 加载Sequence Names */
using (CDatabase db = new CDatabase())
{
if (db.GetSequenceNames(out seq_names))
{
bgw_Init.ReportProgress(100, seq_name_to_display);
}
else
{
bgw_Init.ReportProgress(0, db.LastError);
}
}
}
public SupplierModel GetInforSupplierByID(string id)
{
SupplierModel obj = new SupplierModel();
try
{
DataSet ds = new CDatabase().GetInforSupplierById(id);
if (ds.Tables.Count > 0)
{
obj.ID = Int32.Parse(ds.Tables[0].Rows[0]["ID"].ToString());
obj.Name = ds.Tables[0].Rows[0]["FullName"].ToString();
obj.ContactName = ds.Tables[0].Rows[0]["UserName"].ToString();
obj.Mobile = ds.Tables[0].Rows[0]["Mobile"].ToString().Trim();
obj.Address = ds.Tables[0].Rows[0]["IdentityNumber"].ToString().Trim();
obj.Email = ds.Tables[0].Rows[0]["Email"].ToString();
obj.Note = ds.Tables[0].Rows[0]["Note"].ToString();
if (ds.Tables[0].Rows[0]["CreateDate"] != null)
{
obj.CreateDate = DateTime.Parse(ds.Tables[0].Rows[0]["CreateDate"].ToString());
}
if (ds.Tables[0].Rows[0]["ModifyDate"] != null)
{
obj.ModifiedDate = DateTime.Parse(ds.Tables[0].Rows[0]["ModifiedDate"].ToString());
}
}
return(obj);
}
catch (Exception ex)
{
return(obj);
}
}
public bool SetPassword(CDatabase db, CUser user, int userID, string password)
{
string strQuery = "";
string strQueryPrefix = "UPDATE Users SET Password = '******' WHERE ID = " + userID + " ";
if (user.CanDo(CUser.ACL.EDIT_ALL_USERS) || (user.m_nID == userID && user.CanDo(CUser.ACL.EDIT)))
{
strQuery = strQueryPrefix;
}
else
if (user.CanDo(CUser.ACL.EDITUSERS))
{
strQuery = strQueryPrefix + " AND ParentUserID = " + user.m_nID;
}
try
{
using (MySqlCommand cmd = new MySqlCommand(strQuery, db.connection))
if (cmd.ExecuteNonQuery() != 0)
{
return(true);
}
}
catch (Exception e) { Debug.Assert(false, e.ToString()); }
return(false);
}
public ACustomer()
{
InitializeComponent();
dt = new DataTable();
kh2 = new CACustomer();
db = new CDatabase();
}
private void insertRecord(CDatabase db, TrackerPacketList pts)
{
if (pts != null)
{
string query = "";
try
{
for (int i = 0; i < pts.Count; i++)
{
long devicetime = pts[i].m_Time;
long forwardTime = (Int32)(DateTime.UtcNow.Subtract(new DateTime(1970, 1, 1))).TotalSeconds;
MySqlCommand mycom = db.connection.CreateCommand();
query = string.Concat("Update forwardrecords SET ForwardTimestamp='", forwardTime, "',RecSentDiff=(ForwardTimestamp-ReceivedTimestamp),TotalDifferance=(RecSentDiff+DevRecDiff) where DeviceTimestamp='", pts[i].m_Time, "' and ForwardTimestamp='0'");
mycom.CommandText = query;
if (!db.connection.State.Equals(System.Data.ConnectionState.Open))
{
db.connection.Open();
}
mycom.ExecuteNonQuery();
db.connection.Close();
}
}
catch (Exception ex)
{
m_Logger.Push(LogLevel.ERROR, 0, query + "\n" + ex.Message + " " + ex.StackTrace);
}
}
}
public List <SupplierModel> ListInforSupplier()
{
List <SupplierModel> list = new List <SupplierModel>();
try
{
DataSet ds = new CDatabase().GetInforSupplier();
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
SupplierModel obj = new SupplierModel();
obj.ID = Int32.Parse(ds.Tables[0].Rows[i]["ID"].ToString());
obj.Name = ds.Tables[0].Rows[i]["FullName"].ToString();
obj.ContactName = ds.Tables[0].Rows[i]["UserName"].ToString();
obj.Mobile = ds.Tables[0].Rows[i]["Mobile"].ToString().Trim();
obj.Address = ds.Tables[0].Rows[i]["IdentityNumber"].ToString().Trim();
obj.Email = ds.Tables[0].Rows[i]["Email"].ToString();
obj.Note = ds.Tables[0].Rows[i]["Note"].ToString();
if (ds.Tables[0].Rows[0]["CreateDate"] != null)
{
obj.CreateDate = DateTime.Parse(ds.Tables[0].Rows[i]["CreateDate"].ToString());
}
if (ds.Tables[0].Rows[0]["ModifyDate"] != null)
{
obj.ModifiedDate = DateTime.Parse(ds.Tables[0].Rows[i]["ModifiedDate"].ToString());
}
}
return(list);
}
catch (Exception)
{
return(list);
}
}
/*TrungND 21/10/16 gán thông tin cho khách sạn*/
public List <SupplierModel> GetAllUser()
{
List <SupplierModel> list = new List <SupplierModel>();
try
{
DataTable dt = new CDatabase().GetAllSupplier().Tables[0];
for (int i = 0; i < dt.Rows.Count; i++)
{
SupplierModel obj = new SupplierModel();
obj.ID = Int32.Parse(dt.Rows[0]["ID"].ToString());
obj.Name = dt.Rows[i]["FullName"].ToString();
obj.ContactName = dt.Rows[i]["UserName"].ToString();
obj.Mobile = dt.Rows[i]["Mobile"].ToString().Trim();
obj.Address = dt.Rows[i]["IdentityNumber"].ToString().Trim();
obj.Email = dt.Rows[i]["Email"].ToString();
obj.Note = dt.Rows[i]["Note"].ToString();
list.Add(obj);
}
return(list);
}
catch (Exception)
{
return(null);
}
}
public virtual bool InsertNew(CDatabase db, CUser user, T obj)
{
Clear();
string strQuery = GetInsertQuery(user, obj);
if (strQuery == "")
{
return(false);
}
int ID = 0;
try
{
using (MySqlCommand cmd = new MySqlCommand(strQuery, db.connection))
cmd.ExecuteNonQuery();
ID = GetLastInsertIndex(db);
}
catch (Exception e)
{
Debug.Assert(false, e.ToString());
return(false);
}
return((ID > 0) && Load(db, ID, user));
}
public bool Load(CDatabase db, string strQuery)
{
Clear();
if (strQuery == "")
{
return(false);
}
try
{
using (MySqlCommand cmd = new MySqlCommand(strQuery, db.connection))
using (MySqlDataReader reader = cmd.ExecuteReader())
while (reader.Read())
{
T obj = new T();
if (obj.Load(reader))
{
Add(obj);
}
}
}
catch (Exception e)
{
Debug.Assert(false, e.ToString());
return(false);
}
return(true);
}
public virtual bool InsertNew(CDatabase db, CUser user, T obj)
{
Clear();
DBQuery _Query = GetInsertQuery(user, obj);
if (_Query == null)
{
return(false);
}
int ID = 0;
try
{
using (MySqlCommand cmd = new MySqlCommand(_Query.m_Query, db.connection))
{
for (int i = 0; i < _Query.m_Parametrs.Length; i++)
{
cmd.Parameters.AddWithValue("?Param" + (i + 1), _Query.m_Parametrs[i]);
}
cmd.ExecuteNonQuery();
}
ID = GetLastInsertIndex(db);
}
catch (Exception e)
{
Debug.Assert(false, e.ToString());
return(false);
}
return((ID > 0) && Load(db, ID, user));
}
public virtual bool Delete(CDatabase db, int ID, CUser user)
{
DBQuery _Query = GetDeleteQuery(ID, user);
if (_Query == null)
{
return(false);
}
try
{
using (MySqlCommand cmd = new MySqlCommand(_Query.m_Query, db.connection))
{
for (int i = 0; i < _Query.m_Parametrs.Length; i++)
{
cmd.Parameters.AddWithValue("?Param" + (i + 1), _Query.m_Parametrs[i]);
}
cmd.ExecuteNonQuery();
}
}
catch (Exception e)
{
Debug.Assert(false, e.ToString());
return(false);
}
return(true);
}
protected bool Load(CDatabase db, DBQuery _Query)
{
Clear();
if (_Query == null)
{
return(false);
}
try
{
using (MySqlCommand cmd = new MySqlCommand(_Query.m_Query, db.connection))
{
for (int i = 0; i < _Query.m_Parametrs.Length; i++)
{
cmd.Parameters.AddWithValue("?Param" + (i + 1), _Query.m_Parametrs[i]);
}
using (MySqlDataReader reader = cmd.ExecuteReader())
while (reader.Read())
{
T obj = new T();
if (obj.Load(reader))
{
Add(obj);
}
}
}
}
catch (Exception e)
{
Debug.Assert(false, e.ToString());
return(false);
}
return(true);
}
public static bool Share2User(CDatabase db, CUser user, int share2UserID, int[] IDs)
{
if (!user.CanDo(CUser.ACL.EDIT) || !user.CanDo(CUser.ACL.EDITUSERS))
{
return(false);
}
try
{
string strIDs = "";
using (MySqlCommand cmd = new MySqlCommand("INSERT IGNORE INTO trackers2users (UserID, TrackerID) VALUES (" + share2UserID + ", ?TrackerID)", db.connection))
{
cmd.Parameters.Add("?TrackerID", MySqlDbType.Int32);
for (int i = 0; i < IDs.Length; i++)
{
strIDs += ((i != 0) ? "," : "") + IDs[i];
cmd.Parameters["?TrackerID"].Value = IDs[i];
cmd.ExecuteNonQuery();
}
}
//удалить неактуальыне
using (MySqlCommand cmd = new MySqlCommand("DELETE FROM trackers2users WHERE UserID=" + share2UserID + ((strIDs.Length > 0) ? " AND TrackerID NOT IN (" + strIDs + ")" : ""), db.connection))
cmd.ExecuteNonQuery();
}
catch (Exception e)
{
Debug.Assert(false, e.ToString());
return(false);
}
return(true);
}
public static bool SetOwner(CDatabase db, CUser user, int newOwnerID, int[] IDs)
{
if (!user.CanDo(CUser.ACL.EDIT) || !user.CanDo(CUser.ACL.EDITUSERS))
{
return(false);
}
try
{
using (MySqlCommand cmd = new MySqlCommand("UPDATE trackers SET UserID = " + newOwnerID + " WHERE ID = ?ID", db.connection))
{
cmd.Parameters.Add("?ID", MySqlDbType.Int32);
for (int i = 0; i < IDs.Length; i++)
{
cmd.Parameters["?ID"].Value = IDs[i];
cmd.ExecuteNonQuery();
}
}
}
catch (Exception e)
{
Debug.Assert(false, e.ToString());
return(false);
}
return(true);
}
void ClearLogsForTracker(CTracker tracker)
{
int now = CTime.GetTime(DateTime.UtcNow);
try
{
using (CDatabase db = Configuration.inst().GetDB())
{
if (!db.IsConnected())
{
m_Logger.Push(LogLevel.ERROR, 0, "Не удалось соединиться с БД");
return;
}
int storedatatime = now - tracker.m_nDaysToStore * 86400;
using (MySqlCommand cmd = new MySqlCommand("DELETE FROM Points WHERE TrackerID = " + tracker.m_nID + " AND Time < " + storedatatime, db.connection))
cmd.ExecuteNonQuery();
/*string types = "";
* int storeunusedeventstime = now - 10 * 86400;
* for (int i = 0; i < CEventList.UsefulEvents.Length; i++)
* types += ((i > 0) ? "," : "") + (int)CEventList.UsefulEvents[i];*/
using (MySqlCommand cmd = new MySqlCommand("DELETE FROM Events WHERE TrackerID = " + tracker.m_nID + " AND Time < " + storedatatime + " AND Status=3", db.connection))
cmd.ExecuteNonQuery();
}
}
catch (Exception e)
{
m_Logger.Push(LogLevel.ERROR, 0, e.ToString());
}
}
protected bool DeletePoint(CDatabase db, TrackerPacketList pts)
{
try
{
using (MySqlCommand cmd = new MySqlCommand("UPDATE Points SET Send = Send | " + (1 << m_config.UID) + " WHERE TrackerID = ?TrackerID AND Time = ?Time", db.connection))
using (MySqlTransaction trans = db.connection.BeginTransaction())
{
cmd.Parameters.Add("?TrackerID", MySqlDbType.UInt32);
cmd.Parameters.Add("?Time", MySqlDbType.UInt32);
for (int i = 0; i < pts.Count; i++)
{
cmd.Parameters["?TrackerID"].Value = pts[i].m_ID;
cmd.Parameters["?Time"].Value = pts[i].m_Time;
cmd.ExecuteNonQuery();
}
trans.Commit();
}
for (int i = 0; i < pts.Count; i++)
{
decimal val = 0;
pts[i].GetInput("ALARM", out val);
m_config.SetAlarmState(m_Tracker, val > 0);
}
return(true);
}
catch (Exception ex)
{
m_Logger.Push(LogLevel.ERROR, 0, "Ошибка удаления точки " + ex.ToString());
}
return(false);
}
public bool UpdateContact(CDatabase db, CUser user)
{
if (!user.CanDo(CUser.ACL.EDIT))
{
return(false);
}
try
{
using (MySqlCommand cmd = new MySqlCommand("UPDATE Users SET OrgName=?OrgName, ContactName=?ContactName, PostAdr=?PostAdr, Phone=?Phone, Fax=?Fax, Email=?Email, Lang=?Lang, TimeZone=?TimeZone, RegionID=?RegionID WHERE ID=" + user.m_nID, db.connection))
{
cmd.Parameters.AddWithValue("?OrgName", user.m_strOrgName);
cmd.Parameters.AddWithValue("?ContactName", user.m_strContactName);
cmd.Parameters.AddWithValue("?PostAdr", user.m_strPostAdr);
cmd.Parameters.AddWithValue("?Phone", user.m_strPhone);
cmd.Parameters.AddWithValue("?Fax", user.m_strFax);
cmd.Parameters.AddWithValue("?Email", user.m_strEmail);
cmd.Parameters.AddWithValue("?Lang", user.m_strLang);
cmd.Parameters.AddWithValue("?TimeZone", user.m_strTimeZone);
cmd.Parameters.AddWithValue("?RegionID", user.m_nRegionID);
cmd.ExecuteNonQuery();
}
}
catch (Exception e)
{
Debug.Assert(false, e.ToString());
return(false);
}
return(true);
}
public List <SysUserModel> ListInforSysUser()
{
List <SysUserModel> list = new List <SysUserModel>();
try
{
DataSet ds = new CDatabase().GetInforSysUser();
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
SysUserModel obj = new SysUserModel();
obj.ID = Int32.Parse(ds.Tables[0].Rows[i]["ID"].ToString());
obj.FullName = ds.Tables[0].Rows[i]["FullName"].ToString();
obj.UserName = ds.Tables[0].Rows[i]["UserName"].ToString();
obj.Mobile = ds.Tables[0].Rows[i]["Mobile"].ToString();
obj.IdentityNumber = ds.Tables[0].Rows[i]["IdentityNumber"].ToString();
obj.Status = Int32.Parse(ds.Tables[0].Rows[i]["Status"].ToString());
obj.IsActive = Int32.Parse(ds.Tables[0].Rows[i]["IsActive"].ToString());
obj.NameSysHotelID = ds.Tables[0].Rows[i]["Name"].ToString();
obj.NameModifyby = ds.Tables[0].Rows[i]["EditName"].ToString();
//obj.NameParentID = ds.Tables[0].Rows[0]["ParentID"].ToString();
obj.NameCreateby = ds.Tables[0].Rows[i]["CreateName"].ToString();
if (obj.IsActive != 0)
{
list.Add(obj);
}
}
return(list);
}
catch (Exception)
{
return(list);
}
}
private void Button_SearchTenSP_Click(object sender, RoutedEventArgs e)
{
string str = TextBox_SearchTenSP.Text;
try
{
//Cách 2
string cnString = Properties.Settings.Default.connString;
SqlConnection cn = new SqlConnection(cnString);
cn = CDatabase.Get_Database_Connection();
string sqlText = "SELECT TENHH as 'Tên hàng hóa', SLTON as 'Số lượng tồn' FROM HANG_HOA WHERE TENHH like '%" + str + "%' ";
DataTable tb = new DataTable();
tb = CDatabase.Get_Data_Table(sqlText);
CDatabase.Execute_SQL(sqlText);
MyDataGrid_SP.DataContext = tb.DefaultView;
CDatabase.Close_Database_Connection();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
/*TrungND 21/10/16 gán thông tin cho khách sạn*/
public List <SysUserModel> GetAllUser()
{
List <SysUserModel> list = new List <SysUserModel>();
try
{
DataTable dt = new CDatabase().GetAllSysUser().Tables[0];
for (int i = 0; i < dt.Rows.Count; i++)
{
SysUserModel obj = new SysUserModel();
obj.ID = Int32.Parse(dt.Rows[i]["ID"].ToString());
obj.FullName = dt.Rows[i]["FullName"].ToString();
obj.Address = dt.Rows[i]["Address"].ToString();
obj.IdentityNumber = dt.Rows[i]["IdentityNumber"].ToString();
obj.Mobile = dt.Rows[i]["Mobile"].ToString();
obj.ParentID = Int32.Parse(dt.Rows[i]["ParentID"].ToString());
obj.Status = Int32.Parse(dt.Rows[i]["Status"].ToString());
obj.Department = Int32.Parse(dt.Rows[i]["Department"].ToString());
obj.IsActive = dt.Rows[i]["IsActive"].ToString() == "" ? 0 : Int32.Parse(dt.Rows[i]["IsActive"].ToString());
//obj.ModifyDate = string.IsNullOrEmpty(dt.Rows[i]["ModifyDate"].ToString()) ? null : DateTime.Parse(dt.Rows[i]["ModifyDate"].ToString());
//obj.Modifyby = Int32.Parse(dt.Rows[i]["Modifyby"].ToString());
//obj.Createby = Int32.Parse(dt.Rows[i]["Createby"].ToString());
//obj.CreateDate = DateTime.Parse(dt.Rows[i]["CreateDate"].ToString());
list.Add(obj);
}
return(list);
}
catch (Exception)
{
return(null);
}
}
public async Task <List <CProject> > GetProjects(CDatabase database)
{
var content = await communicationManager.GetProjects(database, m_Url, m_Session.Id, serverName);
if (content == null)
{
return(null);
}
JArray json = JArray.Parse(content);
if (json == null)
{
return(null);
}
List <CProject> projects = new List <CProject>();
projects = JsonConvert.DeserializeObject <List <CProject> >(json.ToString());
foreach (var project in projects)
{
project.Database = database.Key;
}
return(projects);
}
public static string DocSoTiengAnh(string number)
{
CDatabase db = new CDatabase();
DataTable dt = new DataTable();
dt = db.ExecuteBang(string.Format("SELECT dbo.F_DOCSOTHANHTU({0})", number));
return(dt.Rows[0][0].ToString());
}
public override bool Load(CDatabase db, int ID, CUser user)
{
if (!Load(db, GetLoadQuery(ID, user)))
{
return(false);
}
return(true);
}
public FrmKhachhangmoi()
{
InitializeComponent();
dt = new DataTable();
tab = new CTAOTAB();
kh = new CKhachhangNew();
db = new CDatabase();
}
private void bgWorker_DoWork(object sender, DoWorkEventArgs e)
{
string sMessage = "";
if (!CDatabase.TestConnection(_Connection.ConnectionString, out sMessage))
{
e.Result = sMessage;
}
}
public override bool Load(CDatabase db)
{
if (!Load(db, GetLoadQuery()))
{
return(false);
}
return(true);
}
// boardId에 따라 데이터 삭제해주는 함수 - 삭제하려는 개수가 하나일 경우
public Boolean Delete(String boardId)
{
db = new CDatabase();
string sql = "DELETE Board WHERE board_id =" + boardId;
Boolean result = db.ExecuteSQL(sql);
db.Dispose();
return(result);
}
public bool Update(CDatabase db, CUser user, CUser obj)
{
string strQuery = "";
string strQueryPrefix = "UPDATE Users SET Name=?Name, ParentUserID=?ParentUserID, OrgName=?OrgName, ContactName=?ContactName, PostAdr=?PostAdr, Phone=?Phone, Fax=?Fax, Email=?Email, Lang=?Lang, TimeZone=?TimeZone, Disabled = ?Disabled, ACL=?ACL, Lang=?Lang, TimeZone=?TimeZone, RegionID=?RegionID, Geocoder=?Geocoder, Flags=?Flags WHERE ID=" + obj.m_nID + " ";
if (user.CanDo(CUser.ACL.EDIT_ALL_USERS))
{
strQuery = strQueryPrefix;
}
else
if (user.CanDo(CUser.ACL.EDITUSERS))
{
strQuery = strQueryPrefix + " AND ParentUserID = " + user.m_nID;
}
try
{
using (MySqlCommand cmd = new MySqlCommand(strQuery, db.connection))
{
cmd.Parameters.AddWithValue("?Name", obj.m_strName);
cmd.Parameters.AddWithValue("?OrgName", obj.m_strOrgName);
cmd.Parameters.AddWithValue("?ContactName", obj.m_strContactName);
cmd.Parameters.AddWithValue("?PostAdr", obj.m_strPostAdr);
cmd.Parameters.AddWithValue("?Phone", obj.m_strPhone);
cmd.Parameters.AddWithValue("?Fax", obj.m_strFax);
cmd.Parameters.AddWithValue("?Email", obj.m_strEmail);
cmd.Parameters.AddWithValue("?Disabled", obj.m_bDisabled);
cmd.Parameters.AddWithValue("?ACL", obj.m_ACL & ~(obj.m_ACL ^ user.m_ACL));
cmd.Parameters.AddWithValue("?Lang", obj.m_strLang);
cmd.Parameters.AddWithValue("?TimeZone", obj.m_strTimeZone);
cmd.Parameters.AddWithValue("?Geocoder", obj.m_strGeocoder);
if (obj.m_nRegionID > 0)
{
cmd.Parameters.AddWithValue("?RegionID", obj.m_nRegionID);
}
else
{
cmd.Parameters.AddWithValue("?RegionID", DBNull.Value);
}
cmd.Parameters.AddWithValue("?ParentUserID", obj.m_nParentUser);
cmd.Parameters.AddWithValue("?Flags", obj.m_strFlags);
cmd.ExecuteNonQuery();
}
}
catch (Exception e)
{
Debug.Assert(false, e.ToString());
return(false);
}
return(true);
}
public string UpdateSysUser(SysUserModel obj, ref bool kq)
{
try
{
kq = new CDatabase().UpdateSysUser(obj);
return(kq == true ? "Chỉnh sửa thành công" : "Chỉnh sửa thất bại");
}
catch (Exception)
{
return("Có lỗi hệ thống trong quá trình thêm mới");
}
}
