private const int OCSessionTimeOutSeconds = 60; //10 TIMED OUT OFTEN (SENDING TO 404 PAGE), TRYING 30 SECONDS. CHANGED TO 60 SECS BECAUSE OF TEMPORARY TIME DIFF BETWEEN THE TWO SERVERS
/// <summary>
/// Authenticates the given user.
/// </summary>
/// <param name="usUserName">The username to check.</param>
/// <param name="usPassword">The password to check.</param>
/// <returns>True if the login info is valid, false otherwise.</returns>
public static bool AuthenticateUser(string usUserName, string usPassword)
{
string sUserName = usUserName.SQLEscape();
string sStoredHash = "";
DBConnect connect = new DBConnect();
try
{
connect.Connect(ConnectionsMgr.GetAuthConnInfo());
using (var queryUserAuthInfo = connect.Select(columnPassword, _Table.Users, string.Format("WHERE {0}='{1}'", columnUserName, sUserName)))
{
if (queryUserAuthInfo.AffectedRows <= 0)
{
connect.Close();
return(false);
}
queryUserAuthInfo.Read();
sStoredHash = Encoding.UTF8.GetString((byte[])queryUserAuthInfo.Field2(0));
}
connect.Close();
return(MD5Crypt.Verify(usPassword, sStoredHash));
}
catch (Exception ex)
{
ProgramLog.LogError(null, "Auth", "AuthenticateUser", ex.Message + " " + ex.StackTrace);
connect.Close();
return(false);
}
}
public void addsv(string ma, string hoten, string ngaysinh, string gioitinh
, string diachi, string dienthoai, string malop, string bacdaotao, int khoahoc, string khoa, string cmnd)
{
Sinhvien sv = new Sinhvien();
sv.Ma = ma;
sv.Hoten = hoten;
sv.Ngaysinh = ngaysinh;
sv.Gioitinh = gioitinh;
sv.Diachi = diachi;
sv.Dienthoai = dienthoai;
sv.Malop = malop;
sv.Bacdaotao = bacdaotao;
sv.Khoahoc = khoahoc;
sv.Khoa = khoa;
sv.Cmnd = cmnd;
try
{
dbConnect.Open();
// if (chk.kiemtra_sinhvien(cmnd) == 0)
// {
// chk.kiemtratufile_excel(ref loi, ma, hoten, ngaysinh, gioitinh, diachi, dienthoai.ToString(), malop, bacdaotao, khoahoc.ToString(), khoa, cmnd.ToString());
// if (loi == "")
dbConnect.db.Store(sv);
// }
// else loi += " Sinh viên " + ma + hoten + " này bạn đã thêm vào rồi vì số chứng minh của sinh viên này đã có trong hệ thống " + "\n";
}
finally
{
dbConnect.Close();
}
}
public static Dictionary <string, string> GetReqTypeDict(User user)
{
Dictionary <string, string> ret = new Dictionary <string, string>();
DBConnect connection = new DBConnect();
try
{
connection.Connect(ConnectionsMgr.GetAdminConnInfo());
using (var query = connection.Select(new[] { _Column.TrxType, _Column.TrxDesc }, _Table.TrxInfo))
{
while (query.Read())
{
ret.Add(query.Field(0), query.Field(1));
}
}
connection.Close();
ret.Add("832_A", "Item Attributes");
ret.Add("850_X", "PO Store X-Ref");
ret.Add("856_X", "ASN Release X-Ref");
ret.Add("832_P", "Al Tayer Item Attributes");
return(ret);
}
catch (Exception e)
{
ProgramLog.LogError(user, "ProcessQueue", "GetReqTypeDict", e.Message);
connection.Close();
return(new Dictionary <string, string>());
}
}
public void adduser(string nuser, string pass)
{
User themu = new User();
themu.username = nuser;
themu.Password = pass;
try
{
dbConnect.Open();
User g = (from User p in dbConnect.db
where p.username == nuser
select p).SingleOrDefault();
if (g == null)
{
dbConnect.db.Store(themu); MessageBox.Show(" Đã tạo user thành công");
}
else
{
MessageBox.Show("đã có user này");
}
}
finally
{
dbConnect.Close();
}
}
public void addgroup(string ngroup)
{
Group themg = new Group();
themg.Namegroup = ngroup;
try
{
dbConnect.Open();
Group g = (from Group p in dbConnect.db
where p.Namegroup == ngroup
select p).SingleOrDefault();
if (g == null)
{
dbConnect.db.Store(themg); MessageBox.Show(" Đã tạo group thành công");
}
else
{
MessageBox.Show("đã có group này");
}
}
finally
{
dbConnect.Close();
}
}
public static List <ProcessRecord> GetPreviousRecords(User user)
{
List <ProcessRecord> listRecord = new List <ProcessRecord>();
DBConnect connection = new DBConnect();
try
{
var filter = string.Format("WHERE {0}='{1}' AND {2}='{3}' AND {4}>'{5}'", _Column.Customer, user.Customer.SQLEscape(), _Column.Partner, user.ActivePartner.SQLEscape(), _Column.PReqDate, DateTime.Now.Subtract(new TimeSpan(30, 0, 0, 0)).ToString("yyyy-MM-dd HH:mm:ss"));
connection.Connect(ConnectionsMgr.GetOCConnInfo(user, _Database.Home));
using (var query = connection.Select(new[] { _Column.PReqDate, _Column.PReqType, _Column.Processed }, _Table.ProcessQ, filter))
{
while (query.Read())
{
ProcessRecord pr = new ProcessRecord();
pr.Date = query.Field(0, "");
pr.Type = query.Field(1, "");
pr.Status = query.Field(2, "");
listRecord.Add(pr);
}
}
connection.Close();
return(listRecord);
}
catch (Exception e)
{
ProgramLog.LogError(user, "ProcessQueue", "GetPreviousRecords", e.Message);
connection.Close();
return(new List <ProcessRecord>());
}
}
public Boolean kiemtraquyenuser(string user, string doituong, string quyen)
{
if (user.Trim() != "" && doituong.Trim() != "")
{
try
{
Boolean rbool, add, del, view, up;
rbool = false; add = false; del = false; view = false; up = false;
List <Quyen_user> quser = new List <Quyen_user>();
dbConnect.Open();
quser = (from Quyen_user p in dbConnect.db
where p.User.ToLower() == user.ToLower() && p.doituong.ToLower() == doituong.ToLower()
select p).ToList();
if (quser.Count > 0)
{
for (int i = 0; i < quser.Count; i++)
{
add = add || quser[i].Add;
del = del || quser[i].Del;
up = up || quser[i].Up;
view = view || quser[i].View;
}
switch (quyen)
{
case "add":
rbool = add; break;
case "del":
rbool = del; break;
case "up":
rbool = up; break;
case "view":
rbool = view; break;
case "module":
rbool = add || del || up || view; break;
}
}
return(rbool);
}
finally
{
dbConnect.Close();
}
}
else
{
return(false);
}
}
public static OCUserInfo GetOCRecord(string usToken)
{
OCUserInfo info = new OCUserInfo();
if (string.IsNullOrEmpty(usToken))
{
return(info);
}
string sToken = usToken.SQLEscape();
DBConnect connection = new DBConnect();
DateTime expTime = new DateTime();
try
{
connection.Connect(ConnectionsMgr.GetAdminConnInfo());
using (var queryUserAuthInfo = connection.Select(new[] { columnUserName, columnActivePartner, columnIsTest, columnCreateDate }, tableOCAuth, string.Format("WHERE {0}='{1}'", columnSessionID, sToken)))
{
if (queryUserAuthInfo.AffectedRows <= 0)
{
connection.Close();
return(info);
}
queryUserAuthInfo.Read();
info.UserName = queryUserAuthInfo.Field(0);
info.ActivePartner = queryUserAuthInfo.Field(1);
info.IsTest = queryUserAuthInfo.Field(2) == "1";
expTime = (DateTime)queryUserAuthInfo.Field2(3, DateTime.MinValue);
connection.Delete(tableOCAuth, string.Format("WHERE {0}='{1}'", columnSessionID, sToken));
}
connection.Close();
var authLimit = DateTime.Now.AddSeconds(-OCSessionTimeOutSeconds);
if (expTime >= authLimit) //expire date must be within the last OCSessionTimeOutSeconds seconds
{
info.IsValid = true;
}
else
{
throw new Exception("Authentication time is not within range allowed. Auth Time: " + expTime.ToString("yyyy-MM-dd HH:mm:ss") + ", Auth Limit: " + authLimit.ToString("yyyy-MM-dd HH:mm:ss"));
}
return(info);
}
catch (Exception ex)
{
ProgramLog.LogError(null, "Auth", "GetOCRecord", ex.Message);
connection.Close();
return(info);
}
}
public TrainerModel GetTrainerFromDB(string name)
{
var trainer = new TrainerModel()
{
Handle = name
};
var con = new DBConnect().MyConnection;
con.Open();
var querystring = $"SELECT * FROM sql3346222.userCredentials WHERE(TrainerName = '{name}');";
MySqlCommand cmd = new MySqlCommand(querystring, con);
MySqlDataReader rdr = cmd.ExecuteReader();
while (rdr.Read())
{
trainer.Id = int.Parse(rdr[0].ToString());
trainer.HighScore = int.Parse(rdr[5].ToString());
trainer.Lineups = Lineup.DeserializeLineupList(rdr[4].ToString());
trainer.Team = Lineup.DeserializeLineup(rdr[6].ToString());
}
rdr.Close();
con.Close();
return(trainer);
}
public List <TrainerModel> GetElite4AndChampion()
{
var elite4PlusChampionStrings = new List <string>();
var con = new DBConnect().MyConnection;
con.Open();
var query = "SELECT TrainerName FROM sql3346222.userCredentials ORDER BY HighScore DESC LIMIT 5;";
var rdr = new MySqlCommand(query, con).ExecuteReader();
while (rdr.Read())
{
elite4PlusChampionStrings.Add(rdr[0].ToString());
}
rdr.Close();
con.Close();
var elite4PlusChampion = new List <TrainerModel>();
elite4PlusChampionStrings.ForEach(t =>
{
elite4PlusChampion.Add(GetTrainerFromDB(t));
});
return(elite4PlusChampion);
}
/// <summary>
/// Gets the current list of brand names.
/// </summary>
/// <param name="user">The user making the request.</param>
/// <returns></returns>
public static List <string> GetBrandNameList(User user)
{
List <string> bnList = new List <string>();
try
{
string sCustomer = user.Customer.SQLEscape().ToLower();
DBConnect connection = ConnectionsMgr.GetSalesConnection(user, "sales_" + sCustomer);
{
var query = connection.Query("SELECT brand FROM sl_filters GROUP BY brand ORDER BY brand");
while (query.Read())
{
var bn = query.Field(0, "").Trim();
if (bn != "")
{
bnList.Add(bn);
}
}
}
connection.Close();
}
catch (Exception e)
{
Log(user, nameof(GetBrandNameList), e);
}
return(bnList);
}
/// <summary>
/// Gets the current list of account executives.
/// </summary>
/// <param name="user">The user making the request.</param>
/// <returns></returns>
public static List <string> GetAccountExecutiveList(User user)
{
List <string> aeList = new List <string>();
try
{
DBConnect connection = ConnectionsMgr.GetSharedConnection(user, _Database.Home);
{
var query = connection.Query("SELECT aename FROM deptdesc GROUP BY aename ORDER BY aename");
while (query.Read())
{
var ae = query.Field(0, "").Trim();
if (ae != "")
{
aeList.Add(ae);
}
}
}
connection.Close();
aeList.Sort();
}
catch (Exception e)
{
Log(user, nameof(GetAccountExecutiveList), e);
}
return(aeList);
}
private static StLookupDict GetStoreInfoLookup(User user, string storePartner, IEnumerable <string> storeList)
{
StLookupDict stLookup = new StLookupDict();
string compList = storeList.ToSqlValueList();
try
{
DBConnect connection = ConnectionsMgr.GetSharedConnection(user);
using (var querySt = connection.Select(new[] { BYId, XrefId, STName }, _Table.Stinfo, $"WHERE {Partner}='{storePartner}' AND ({BYId} IN {compList} OR TRIM({XrefId}) IN {compList})"))
{
while (querySt.Read())
{
string xref = querySt.FieldByName(XrefId);
string byid = querySt.FieldByName(BYId);
string stname = querySt.FieldByName(STName);
if (!stLookup.byidDict.ContainsKey(byid))
{
stLookup.byidDict.Add(byid, stname);
}
if (!stLookup.xrefDict.ContainsKey(xref))
{
stLookup.xrefDict.Add(xref, stname);
}
}
}
connection.Close();
}
catch (Exception e)
{
Log(user, nameof(GetStoreInfoLookup), e);
}
return(stLookup);
}
/// <summary>
/// Gets the current list of stores.
/// </summary>
/// <param name="user">The user making the request.</param>
/// <returns></returns>
public static List <Store> GetStoreList(User user)
{
List <Store> stores = new List <Store>();
string sPartner = user.ActivePartner.SQLEscape();
string prtCondition = "";
string storePartner = GetStorePartner(sPartner);
if (sPartner == _Partner.Marines)
{
prtCondition = "AND (LENGTH(xrefid)=3 OR LENGTH(xrefid)=5)"; //EXCLUDE 4 CHAR STORES THAT DON'T END IN 'E' OR 'W' (AKA HAVE 4 CHAR XREFID)
}
try
{
DBConnect connection = ConnectionsMgr.GetSharedConnection(user, _Database.Home);
using (DBResult res = connection.Select(new[] { STName, BYId }, _Table.Stinfo, $"WHERE partner='{storePartner}' and byid!='' {prtCondition} GROUP BY byid ORDER BY byid,upddate DESC"))
{
while (res.Read())
{
var s = new Store(res);
stores.Add(s);
}
}
connection.Close();
}
catch (Exception e)
{
Log(user, nameof(GetStoreList), e);
}
return(stores);
}
public static bool CreateUploadRecord(User user, DateTime reqDate, string reqType, string fileName)
{
// uniquekey, preqdate, customer, partner, preqtype, preqaction, pfilename, pcustomfile, presultfile, presultdate, processed
DBConnect connection = new DBConnect();
bool success = false;
try
{
connection.Connect(ConnectionsMgr.GetOCConnInfo(user, _Database.Home));
var insertVals = new Dictionary <string, string>()
{
{ _Column.UniqueKey, connection.GetNewKey() },
{ _Column.PReqDate, reqDate.ToString("yyyy-MM-dd HH:mm:ss") },
{ _Column.Customer, user.Customer.SQLEscape() },
{ _Column.PReqType, reqType.SQLEscape() },
{ _Column.PFileName, fileName.SQLEscape() },
{ _Column.PCustomFile, "1" },
{ _Column.Partner, user.ActivePartner.SQLEscape() }
};
using (var res = connection.Insert(_Table.ProcessQ, insertVals.ToNameValueCollection()))
{
success = res.AffectedRows != 0;
}
connection.Close();
return(success);
}
catch (Exception e)
{
ProgramLog.LogError(user, "ProcessQueue", "CreateUploadRecord", e.Message);
return(false);
}
}
private static Dictionary <string, ItemInfo> GetItemInfoLookup(User user, string sPartner, string dbSales, IEnumerable <string> vendList, IEnumerable <string> upcList)
{
Dictionary <string, ItemInfo> itemDict = new Dictionary <string, ItemInfo>();
try
{
DBConnect connection = ConnectionsMgr.GetSalesConnection(user, dbSales);
string colVendNum = sPartner == _Partner.Thalia ?
$"SUBSTR(TRIM({VendorNum}),1,11)" :
$"TRIM({VendorNum})";
using (var queryItemInfo = connection.Select("vendornum,upcnum,prodcat,deptname,prodsubcat,classdesc,subcdesc,itemdesc,colorcode,itemcolor", _Table.Sl_MasterCat, $"WHERE {colVendNum} IN {vendList.ToSqlValueList()} AND TRIM({UPCNum}) IN {upcList.ToSqlValueList()} group by vendornum,upcnum"))
{
while (queryItemInfo.Read())
{
string key = queryItemInfo.FieldByName(VendorNum).Trim() + queryItemInfo.FieldByName(UPCNum).Trim();
if (!itemDict.ContainsKey(key))
{
itemDict.Add(key, new ItemInfo(queryItemInfo));
}
}
}
connection.Close();
}
catch (Exception e)
{
Log(user, nameof(GetItemInfoLookup), e);
}
return(itemDict);
}
public List <string> GetAllPokemonNames()
{
var names = new List <string>();
//using (TextFieldParser parser = new TextFieldParser(Environment.CurrentDirectory + @"\Data\PokemonNames.csv"))
//using (TextFieldParser parser = new TextFieldParser(ApplicationDeployment.CurrentDeployment.DataDirectory + @"\Data\PokemonNames.csv"))
//{
// parser.TextFieldType = FieldType.Delimited;
// parser.SetDelimiters(",");
// while (!parser.EndOfData)
// {
// names = new List<string>(parser.ReadFields());
// }
//}
var con = new DBConnect().MyConnection;
con.Open();
var querystring = $"SELECT FileContent FROM sql3346222.Files WHERE FileName='AllPokemonGen1CSV'";
MySqlCommand cmd = new MySqlCommand(querystring, con);
MySqlDataReader rdr = cmd.ExecuteReader();
while (rdr.Read())
{
names = rdr[0].ToString().Split(',').ToList();
}
rdr.Close();
con.Close();
return(names);
}
public static List <ItemInfo> GetItemData(User user)
{
Dictionary <string, QuantityInfo> currentDict = new Dictionary <string, QuantityInfo>();
List <ItemInfo> _itemTable = new List <ItemInfo>();
try
{
DBConnect connection = ConnectionsMgr.GetSharedConnection(user, _Database.ESIC);
{
using (var reader = connection.Query(string.Format(qFetchCurrDist, user.ActivePartner.SQLEscape())))
{
while (reader.Read())
{
string vendor = reader.Field(0, "").ToString();
QuantityInfo cqi = new QuantityInfo();
string min = reader.Field(1, "0");
string max = reader.Field(2, "0");
string reo = reader.Field(3, "0");
cqi.Min = (int)double.Parse(min);
cqi.Max = (int)double.Parse(max);
cqi.Reorder = (int)double.Parse(reo);
currentDict.Add(vendor, cqi);
}
}
using (var reader = connection.Query(qFetchBaseDist))
{
while (reader.Read())
{
ItemInfo info = new ItemInfo();
info.Vendor = reader.Field(0, "").ToString();
info.ItemUPC = reader.Field(1, "").ToString();
info.Description = reader.Field(2, "").ToString();
info.Base = new QuantityInfo()
{
Min = int.Parse(reader.Field(3, "0")),
Max = int.Parse(reader.Field(4, "0")),
Reorder = int.Parse(reader.Field(5, "0"))
};
if (currentDict.ContainsKey(info.Vendor))
{
info.Current = currentDict[info.Vendor];
}
_itemTable.Add(info);
ItemInfo infoCopy = info.Clone();
}
}
}
connection.Close();
return(_itemTable);
}
catch (Exception e)
{
ProgramLog.LogError(user, "ItemTable", "GetItemData", e.Message);
return(new List <ItemInfo>());
}
}
private static ResponseType Cancel860(User user, string sHeadUniqueKey860)
{
DBConnect connection = new DBConnect();
try
{
connection.Connect(ConnectionsMgr.GetSHConnInfo(user, _Database.ECGB));
UpdateAll860ProcFlags(connection, sHeadUniqueKey860, _ProgressFlag.Canceled);
connection.Close();
return(ResponseType.SuccessCPO);
}
catch (Exception e)
{
connection.Close();
ProgramLog.LogError(user, "ChangePOTracker", "Cancel860", e.Message);
return(ResponseType.ErrorCPOUnknown);
}
}
public static bool SetConnIDs(User user, bool isTest)
{
if (isTest)
{
// Use default connections when debugging
user.OCConnID = idTestOC;
user.NPConnID = idTestNP;
user.SHConnID = idTestSH; // Use OC ID
user.SLConnID = idTestSL; // Use OC ID
return(true);
}
DBConnect connection = new DBConnect();
if (!connection.Connect(ConnectionsMgr.GetAdminConnInfo()))
{
return(false);
}
try
{
using (var res = connection.Select(new[] { columnOCConnID, columnNPConnID, columnSHConnID, columnSLConnID }, tableConnectionIDInfo, string.Format("WHERE {0}='{1}'", columnCustomer, user.Customer)))
{
if (!res.Read())
{
// No info for customer?
ProgramLog.LogError(user.UserName, user.Customer, "EDIO", "ConnectionsMgr", "SetConnIDs", string.Format("Unable to find connection info in {0} for customer {1}", tableConnectionIDInfo, user.Customer));
connection.Close();
return(false);
}
user.OCConnID = res.Field(0);
user.NPConnID = res.Field(1);
user.SHConnID = res.Field(2);
user.SLConnID = res.Field(3);
}
connection.Close();
return(true);
}
catch (Exception e)
{
ProgramLog.LogError(user, "ConnectionsMgr", "SetConnIDs", e.Message);
connection.Close();
return(false);
}
}
private void Main_Loaded(object sender, RoutedEventArgs e)
{
Window w = Application.Current.MainWindow;
w.WindowStyle = WindowStyle.None;
w.WindowState = WindowState.Maximized;
w.ResizeMode = ResizeMode.NoResize;
w.Closing += W_Closing;
w.FontSize = 28;
LoadTxt();
MySql.Data.MySqlClient.MySqlConnection conn = DBConnect.Init();
if (conn == null)
{
DisableBtns();
WPopup.s.ShowDialog(Txt.s._[(int)TxI.DB_NOK]);
return;
}
string qry = DBConnect.mkQrySelect("sqz_ver", "id", null);
string emsg;
MySql.Data.MySqlClient.MySqlDataReader reader =
DBConnect.exeQrySelect(conn, qry, out emsg);
if (reader == null)
{
DisableBtns();
WPopup.s.ShowDialog(emsg);
}
else
{
bool bNVer = true;
if (reader.Read())
{
int ver = 0;
if (!reader.IsDBNull(0))
{
ver = reader.GetInt32(0);
}
if (ver == uVer)
{
bNVer = false;
}
}
reader.Close();
if (bNVer)
{
DisableBtns();
WPopup.s.ShowDialog(Txt.s._[(int)TxI.DB_VER_NOK] +
(uVer / 100) + '.' + (uVer % 100 / 10) + '.' + (uVer % 10));
}
}
DBConnect.Close(ref conn);
}
private static DatabaseInfo _GetConnInfo(User user, string connID, string database = "")
{
DatabaseInfo dbInfo = new DatabaseInfo();
DBConnect connection = new DBConnect();
if (!connection.Connect(ConnectionsMgr.GetAdminConnInfo()))
{
ProgramLog.LogError(user, "ConnectionInfo", "_GetConnInfo", "Unable to connect to admin database.");
return(dbInfo);
}
using (var res = connection.Select(new[] { columnServer, columnPort, columnIsTest }, tableAllPorts, string.Format("WHERE {0}='{1}'", columnConnectID, connID.SQLEscape())))
{
if (!res.Read())
{
ProgramLog.LogError(user, "ConnectionInfo", "_GetConnInfo", string.Format("Unable to find info in table \"{0}\" for connection ID \"{1}\".", tableConnectionIDInfo, connID.SQLEscape()));
connection.Close();
return(dbInfo);
}
dbInfo.Server = res.Field(0);
dbInfo.Port = (int)res.Field2(1);
dbInfo.IsTest = (int)res.Field2(2) != 0;
}
connection.Close();
dbInfo.Id = connID;
dbInfo.Database = string.IsNullOrWhiteSpace(database) ? databaseHome : database;
dbInfo.Driver = _DefaultDriver;
if (cred.ContainsKey(dbInfo.Id))
{
dbInfo.Username = cred[dbInfo.Id].UserName;
dbInfo.Password = cred[dbInfo.Id].Password;
}
else
{
dbInfo.Username = defaultCred.UserName;
dbInfo.Password = defaultCred.Password;
}
return(dbInfo);
}
/// <summary>
///
/// </summary>
/// <param name="cuser"></param>
/// <param name="user"></param>
/// <param name="groupname"></param>
/// <returns></returns>
public int kiemtrauser_group(Quyen_user cuser, string user, string groupname)//khi tao moi thi kiem tra
{
try
{
List <Quyen_user> quser = new List <Quyen_user>();
dbConnect.Open();
quser = (from Quyen_user p in dbConnect.db
where p.User == user && p.Group == groupname
select p).ToList();
if (quser.Count > 0)
{
return(0);
}
else
{
dbConnect.db.Store(cuser);; return(1);
}
}
finally
{
dbConnect.Close();
}
}
/// <summary>
/// lấy môn học theo mã
/// </summary>
/// <param name="ma"></param>
/// <returns></returns>
public Diemsv getById(string ma, string mssv)
{
Diemsv obj = null;
try
{
dbConnect.Open();
obj = (from Diemsv o in dbConnect.db
where (o.Mamh == ma) && (o.Masv == mssv)
select o).FirstOrDefault();
dbConnect.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
dbConnect.Close();
}
return(obj);
}
/// <summary>
///
/// </summary>
/// <param name="groupname"></param>
/// <param name="add"></param>
/// <param name="del"></param>
/// <param name="up"></param>
/// <param name="view"></param>
/// <param name="doituong"></param>
/// <returns></returns>
public int addquyen_group(string groupname, Boolean add, Boolean del, Boolean up, Boolean view, string doituong)//khi tao moi thi kiem tra
{
try
{
dbConnect.Open();
Quyen_group listgroup = (from Quyen_group p in dbConnect.db
where p.Group == groupname && p.Doituong == doituong
select p).SingleOrDefault();
if (listgroup != null)
{
listgroup.Add = add;
listgroup.Del = del;
listgroup.Up = up;
listgroup.View = view;
listgroup.Group = groupname;
listgroup.Doituong = doituong;
dbConnect.db.Store(listgroup);
}
else
{
Quyen_group group = new Quyen_group();
group.Add = add;
group.Del = del;
group.Up = up;
group.View = view;
group.Group = groupname;
group.Doituong = doituong;
dbConnect.db.Store(group);
}
return(1);
}
catch { return(0); }
finally
{
dbConnect.Close();
}
}
public static void EditXref(User user, List <XrefRecord> updateRecs)
{
try
{
DBConnect connection = ConnectionsMgr.GetOCConnection(user, Home);
{
List <string> updateCols = new List <string>()
{
UniqueKey,
VendorName,
VendorId,
VendorSeq,
BrandName,
Customer,
Partner
};
List <string> replaceCols = new List <string>()
{
string.Format("{0}=VALUES({0})", VendorName),
string.Format("{0}=VALUES({0})", VendorId),
string.Format("{0}=VALUES({0})", VendorSeq),
string.Format("{0}=VALUES({0})", BrandName),
};
List <string> updateVals = new List <string>();
foreach (var record in updateRecs)
{
List <string> recVals = new List <string>();
recVals.Add(record.Key);
recVals.Add(record.CompanyName.Truncate(60).SQLEscape());
recVals.Add(record.GXSAccount.Truncate(15).SQLEscape());
recVals.Add(record.SelectionCode.Truncate(3).SQLEscape());
recVals.Add(record.BrandName.Truncate(80).SQLEscape());
recVals.Add(user.Customer.SQLEscape());
recVals.Add(user.ActivePartner.SQLEscape());
updateVals.Add(recVals.ToSqlValueList());
}
connection.Query(string.Format("INSERT INTO {0} ({1}) VALUES {2} ON DUPLICATE KEY UPDATE {3}", CatXref, updateCols.ToSqlColumnList(), string.Join(",", updateVals), string.Join(",", replaceCols)));
}
connection.Close();
}
catch (Exception e)
{
ProgramLog.LogError(user, nameof(CatalogXrefManager), nameof(EditXref), e.Message);
}
}
public static PdsOptions GetOptions(User user)
{
try
{
DBConnect connection = ConnectionsMgr.GetOCConnection(user, Home);
var opt = _GetOptions(user, connection);
connection.Close();
return(opt);
}
catch (Exception e)
{
ProgramLog.LogError(user, nameof(PdsManager), nameof(GetOptions), e.Message);
return(new PdsOptions());
}
}
public static void RemoveXref(User user, List <string> keyList)
{
try
{
DBConnect connection = ConnectionsMgr.GetOCConnection(user, Home);
{
connection.Delete(CatXref, $"WHERE {UniqueKey} IN {keyList.ToSqlValueList()}");
}
connection.Close();
}
catch (Exception e)
{
ProgramLog.LogError(user, nameof(CatalogXrefManager), nameof(RemoveXref), e.Message);
}
}
public static List <CPOSummaryHead> GetChangeList(User user)
{
List <CPOSummaryHead> ret = new List <CPOSummaryHead>();
DBConnect connection = new DBConnect();
try
{
connection.Connect(ConnectionsMgr.GetSHConnInfo(user, _Database.ECGB));
var queryCPOHead = connection.Select(new[] { _Column.UniqueKey, _Column.PONumber, _Column.POChangeDate, _Column.Purpose, _Column.TotalItems, _Column.HProcessed }, _Table.Head860, string.Format("WHERE {0}='{1}' AND {2}='{3}' AND ({4}='{5}' OR {4}='{6}')", _Column.Customer, user.Customer, _Column.Partner, user.ActivePartner, _Column.HProcessed, _ProgressFlag.Unprocessed, _ProgressFlag.Error));
while (queryCPOHead.Read())
{
CPOSummaryHead newHead = new CPOSummaryHead();
newHead.UniqueKey = queryCPOHead.Field(0, "");
newHead.PONumber = queryCPOHead.Field(1, "");
newHead.POChangeDate = queryCPOHead.Field(2, "");
newHead.Purpose = ElementLookup.GetDesc(user, _Element.Purpose, queryCPOHead.Field(3, ""));
newHead.Affected = queryCPOHead.Field(4, "");
newHead.Status = queryCPOHead.Field(5, "");
newHead.Details = new List <CPOSummaryDetail>();
var queryCPODetail = connection.Select(new[] { _Column.ChangeType, _Column.Quantity, _Column.ChangeQuantity, _Column.UnitPrice, _Column.RetailPrice, _Column.UPCNum, _Column.VendorNum, _Column.ItemDesc, _Column.PackSize, _Column.Dropship, _Column.Processed }, _Table.Detail860, string.Format("WHERE {0}='{1}'", _Column.UniqueKey, newHead.UniqueKey));
while (queryCPODetail.Read())
{
CPOSummaryDetail newDetail = new CPOSummaryDetail();
newDetail.ChangeType = ElementLookup.GetDesc(user, _Element.ChangeType, queryCPODetail.Field(0, ""));
newDetail.Quantity = decimal.Parse(queryCPODetail.Field(1, "0")).ToString("N0");
newDetail.ChangeQuantity = decimal.Parse(queryCPODetail.Field(2, "0")).ToString("N0");
newDetail.UnitPrice = queryCPODetail.Field(3, "");
newDetail.RetailPrc = queryCPODetail.Field(4, "");
newDetail.UPC = queryCPODetail.Field(5, "");
newDetail.VendorNum = queryCPODetail.Field(6, "");
newDetail.ItemDesc = queryCPODetail.Field(7, "");
newDetail.PackSize = queryCPODetail.Field(8, "");
newDetail.Dropship = queryCPODetail.Field(9, "").Replace('\r', ' ');
newDetail.Status = queryCPODetail.Field(10, "");
newHead.Details.Add(newDetail);
}
ret.Add(newHead);
}
return(ret.OrderBy(h => h.POChangeDate).ThenBy(h => h.PONumber).ThenBy(h => h.Purpose).ToList());
}
catch (Exception e)
{
connection.Close();
ProgramLog.LogError(user, "ChangePOTracker", "GetChangeList", e.Message);
return(new List <CPOSummaryHead>());
}
}
public void SendEmail(string email, [Optional] string code)
{
try
{
var mailMessage = new MimeMessage();
mailMessage.From.Add(new MailboxAddress("Pokemanz", "*****@*****.**"));
mailMessage.To.Add(new MailboxAddress("Trainer", email));
var textpart = new TextPart("plain");
if (!string.IsNullOrEmpty(code))
{
mailMessage.Subject = "PokeMans Verification Code";
textpart.Text = $"Your PokeManz verification code is: \n\n" + code;
}
else // send login creds/forgot password
{
var LoginModel = new LoginModel();
var con = new DBConnect().MyConnection;
string lookupEmailByName = $"SELECT TrainerName, Password FROM sql3346222.userCredentials WHERE(Email='{email}') LIMIT 1;";
con.Open();
MySqlCommand query = new MySqlCommand(lookupEmailByName, con);
MySqlDataReader rdr = query.ExecuteReader();
//reading returned query
while (rdr.Read())
{
LoginModel.Username = rdr[0].ToString(); // username
LoginModel.Password = new Encryption().Decrypt(rdr[1].ToString()); // password hashed + secret
}
rdr.Close();
con.Close();
mailMessage.Subject = "PokeManz Credentials";
textpart.Text = $"Your login credentials are\n\nUsername: {LoginModel.Username}\nPassword: {LoginModel.Password}";
}
mailMessage.Body = textpart;
Task.Run(() => DoSending(mailMessage)); // fire off and forget about it
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
}
