//get the certifacte status from the ca
public int retrieveStatus(int requestID, string hostname)
{
int iDisposition;
string strCAConfig;
CCertConfig objCertConfig = new CCertConfig();
CCertRequest objCertRequest = new CCertRequest();
try
{
SqlLite sql = new SqlLite();
/*Cheking if host name and req is belong to each other*/
if (sql.checkHostnameWithreqID(requestID, hostname))
{
return(-6);
}
if (sql.checkcertFlag(requestID)) //checking if the client allreay consumed the certificate
{
return(-3);
}
strCAConfig = objCertConfig.GetConfig(CC_DEFAULTCONFIG); //connect to the ca
iDisposition = objCertRequest.RetrievePending(requestID, strCAConfig); //retrive the certifcate status from the ca
sql.updateTable(iDisposition, requestID); //updat certificate table with more information about the cert
return(iDisposition); //return cert status
}
catch (Exception ex)
{
Console.Write(ex.Message);
return(-2);
}
}
private void btnExit_Click(object sender, RoutedEventArgs e)
{
SqlLite sq = new SqlLite();
sq.CloseConnection();
this.Close();
}
public void Configuration(IAppBuilder app)
{
SqlLite sql = new SqlLite();
sql.createNewDatabase();
ConfigureAuth(app);
}
private void btnLogin_Click(object sender, RoutedEventArgs e)
{
DB.SetConn(); // !!!! временно, пока не все перешли на CRUD
SqlLite sq = new SqlLite();
if (sq.SetConnection("Data Source=../../ShopDB.db") == false)
{
MessageBox.Show("Ошибка соединения с базой данных. Попробуйте войти позже.");
return;
}
if (Login.CheckUser(txtEmail.Text, txtPW.Password) == true)
{
MainWindow MW = new MainWindow();
MW.Show();
MW.FillGrid();
this.Close();
}
else
{
MessageBox.Show("Данный пользователь не найден.");
txtPW.Password = "";
}
}
/// <summary>
/// admin使用
/// </summary>
/// <param name="company"></param>
/// <param name="errormsg"></param>
/// <returns></returns>
public static bool SaveCompany(Foresight.DataAccess.Company company, out string errormsg)
{
errormsg = string.Empty;
try
{
//if (EncryptHelper.IsInternalSys(out errormsg, false))
//{
// errormsg = "内部系统，无须审核";
// return true;
//}
Encript.CompanyModel my_company = GetSqlLiteCompany(company);
if (my_company == null)
{
errormsg = "公司不存在";
return(false);
}
my_company.CompanyName = company.CompanyName;
SqlLite.SaveCompany(my_company);
errormsg = "成功";
return(true);
}
catch (Exception ex)
{
LogHelper.WriteError("SqlLiteHelper", "SaveCompany", ex);
errormsg = "内部异常";
return(false);
}
}
//submit the request that created in the createCertifcate to the CA
public int submitRequest(string certrequest, string hostname)
{
CCertConfig objCertConfig = new CCertConfig();
CCertRequest objCertRequest = new CCertRequest();
CCertAdmin objCertAdmin = new CCertAdmin();
string strCAConfig;
int iDisposition;
int requestID;
string errorStatus;
try
{
strCAConfig = objCertConfig.GetConfig(CC_DEFAULTCONFIG); //connect to the ca
iDisposition = objCertRequest.Submit(CR_IN_BASE64, certrequest, null, strCAConfig); //submit the certiface request to the ca
requestID = objCertRequest.GetRequestId(); //get the requestid that was created -the certifacte is in pending status
SqlLite sql = new SqlLite();
sql.insertTable(hostname, iDisposition, requestID); //insert first certificate information
// objCertAdmin.ResubmitRequest(strCAConfig, requestID);
return(requestID); //return the reqid that was created for the certificate request in the pending queue
}
catch (Exception ex)
{
errorStatus = ex.Message;
return(0);
}
}
public void TestAnonymous([IncludeDataSources(TestProvName.AllSQLite)] string context)
{
using (var db = new DataConnection(context))
{
db.Execute(@"
DROP TABLE IF EXISTS dataFTS;
CREATE VIRTUAL TABLE dataFTS USING fts4(`ID` INTEGER, `FirstName` TEXT, `LastName` TEXT, `MidName` TEXT )");
try
{
var data = db.GetTable <DtaFts>()
.Select(result =>
new
{
result.FirstName,
result.MidName,
result.LastName,
});
var query = data.Where(arg => SqlLite.MatchFts <DtaFts>("John*"));
TestContext.WriteLine(query.ToString());
var _ = query.ToList();
}
finally
{
// cleanup
db.Execute("DROP TABLE dataFTS");
}
}
}
public void FillGrid()
{
SqlLite sq = new SqlLite();
sq.SetDataGridSourse(ref Dg1, "Select t1.orderid,t2.prodid,t2.qty from orders t1 join ordersD t2 on t1.orderid=t2.orderid where t1.userid = '" + orderUser + "' and orderstate = 1 and t1.orderid in (select max(orderid) from orders where userid = '" + orderUser + "' and orderstate = 1) ");
// если не указать имя - возьмет с sql, никаких варнингов.
}
public void sqlLiteTest()
{
string PN = "LZ6AAAAAA";
string strSql = "select frequency,TDR,general from vna32port where LuxsharePN='" + PN + "'";
//DataTable dt= DbHelperOleDb.Query(strSql,DB_HTPSDBConnectionString).Tables[0];
DataTable dt = SqlLite.ExecuteDataTable(strSql);
if (dt.Rows.Count != 1)
{
}
}
public void FillGrid()
{
SqlLite sq = new SqlLite();
sq.SetDataGridSourse(ref DG, "Select * from Products order by ProdID");
// если не указать имя - возьмет с sql, никаких варнингов.
DG.Columns[0].Header = "ProdID";
DG.Columns[1].Header = "ProdName";
DG.Columns[2].Header = "Quantity";
DG.Columns[3].Header = "price";
}
private void btnRegister_Click(object sender, RoutedEventArgs e)
{
SqlLite sq = new SqlLite();
if (sq.SetConnection("Data Source=../../ShopDB.db") == false)
{
MessageBox.Show("Ошибка соединения с базой данных. Попробуйте войти позже.");
return;
}
Registration NW = new Registration();
NW.Show();
this.Close(); // hide?
}
public void TestDirect(string context)
{
using (var db = new DataConnection(context))
{
db.Execute(@"
DROP TABLE IF EXISTS dataFTS;
CREATE VIRTUAL TABLE dataFTS USING fts4(`ID` INTEGER, `FirstName` TEXT, `LastName` TEXT, `MidName` TEXT )" );
try
{
var data = db.GetTable <DtaFts>()
.Where(arg => SqlLite.MatchFts <DtaFts>("John*"))
.Select(result =>
new
{
result.FirstName,
result.MidName,
result.LastName,
});
var list = data.ToList(); // <=THROWS EXCEPTION
Assert.AreEqual(0, list.Count);
db.GetTable <DtaFts>()
.Insert(() => new DtaFts()
{
FirstName = "JohnTheRipper"
});
db.GetTable <DtaFts>()
.Insert(() => new DtaFts()
{
FirstName = "DoeJohn"
});
list = data.ToList(); // <=THROWS EXCEPTION
Assert.AreEqual(1, list.Count);
Assert.AreEqual("JohnTheRipper", list[0].FirstName);
}
finally
{
// cleanup
db.Execute("DROP TABLE dataFTS");
}
}
}
//unlock certificate in stauts consumed that client can get it one more time
public int unlockCert(string hostname)
{
SqlLite sql = new SqlLite();
string status;
try
{
sql.updateCertFlag(hostname); //unlock certificate
return(0);
}
catch (Exception ex)
{
status = ex.Message;
return(-1);
}
}
private void btnCompleteOrder_Click(object sender, RoutedEventArgs e)
{
string sql;
string orderid;
SqlLite sq = new SqlLite();
if (Dg1.SelectedItems.Count == 0)
{
return;
}
orderid = ((DataRowView)Dg1.SelectedItems[0]).Row["OrderId"].ToString();
sql = "update orders set orderstate = 2 where orderid = " + orderid + "";
sq.GetSingleSet(sql);
}
public int recreateCertifcate(string hostname)
{
SqlLite sql = new SqlLite();
Certificate cert = new Certificate();
try
{
revokCertifcate(hostname);
return(CreateCertifcate(hostname));
}
catch (Exception ex)
{
Console.Write(ex.Message);
return(0);
}
}
///api/User/InsertMachineInfo?userName=asaf&password=1234&clientid=1234&hash=1234
public HttpResponseMessage insertMachineInfo(string userName, string password, string clientid, string hash)
{
var resp = new HttpResponseMessage(HttpStatusCode.OK);
PrincipalContext pc = new PrincipalContext(ContextType.Domain, "YOURDOMAIN"); //connect to the AD server
bool isValid = pc.ValidateCredentials(userName, password); //validate the credentials from the Active Directory
if (isValid) //if the user and password valid insert the new machine info
{
SqlLite sql = new SqlLite();
sql.insertTableHost(clientid, hash);
resp.Content = new StringContent("Success", System.Text.Encoding.UTF8, "application/xml");
return(resp);
}
else //the user and password not valid return error and dont insert the machine info
{
resp.Content = new StringContent("Error", System.Text.Encoding.UTF8, "application/xml");
return(resp);
}
}
public override Task ValidateClientAuthentication(OAuthValidateClientAuthenticationContext context)
{
string clientId;
string clientSecret;
context.TryGetFormCredentials(out clientId, out clientSecret);
SqlLite sql = new SqlLite();
if (clientId != null & sql.checkClientWithHash(clientSecret, clientId))
{
context.SetError("invalid_grant", "Error The ClientId Is Not Recognize");
}
else
{
context.Validated(clientId);
}
return(base.ValidateClientAuthentication(context));
}
public HttpResponseMessage renewAllCerts()
{
int reqid;
var jsonObject = new JObject();
var resp = new HttpResponseMessage(HttpStatusCode.OK);
SqlLite sql = new SqlLite();
List <string> list = sql.certExpired();
for (var i = 0; i < list.Count; i++)
{
jsonObject.Add("Host" + i, list[i]);
reqid = renewCert(list[i]);
jsonObject.Add("reqid" + i, reqid);
}
resp.Content = new StringContent(Newtonsoft.Json.JsonConvert.SerializeObject(jsonObject), System.Text.Encoding.UTF8, "application/json");
return(resp);
}
//rennew certficiate that expired
public int RenewCert(string Cert, int reqid)
{
int iDisposition;
string CertifcateStr;
string status;
string HostName;
CX509CertificateRequestPkcs10 objPkcs10 = new CX509CertificateRequestPkcs10();
CX509Enrollment objEnroll = new CX509Enrollment();
CCertConfig objCertConfig = new CCertConfig();
CX500DistinguishedName objDN = new CX500DistinguishedName();
CCertAdmin objCertAdmin = new CCertAdmin();
string strCAConfig;
var inheritOptions = X509RequestInheritOptions.InheritPrivateKey | X509RequestInheritOptions.InheritSubjectFlag | X509RequestInheritOptions.InheritExtensionsFlag | X509RequestInheritOptions.InheritSubjectAltNameFlag;
try
{
strCAConfig = objCertConfig.GetConfig(CC_DEFAULTCONFIG); //connect to the ca
InstallCert(Cert);
objPkcs10.InitializeFromCertificate(X509CertificateEnrollmentContext.ContextUser, Cert, EncodingType.XCN_CRYPT_STRING_BASE64HEADER, inheritOptions); //create new cert request from exists expired cert
objDN = objPkcs10.Subject; //getting old cert subject (hostname)
HostName = objDN.Name.ToString().Substring(3);
objEnroll.InitializeFromRequest(objPkcs10); //create enroll rquest
CertifcateStr = objEnroll.CreateRequest(EncodingType.XCN_CRYPT_STRING_BASE64); //crearte new cert request
iDisposition = submitRequest(CertifcateStr, HostName); //submit cert to the ca
objCertAdmin.ResubmitRequest(strCAConfig, iDisposition); //issue the Certificate
if (iDisposition > 0) //if cert was created delete the old cert from the table
{
SqlLite sql = new SqlLite();
sql.deleteCertRecord(reqid);
deleteFromStore(objDN.Name.ToString());
return(iDisposition);
}
return(0);
}
catch (Exception ex)
{
status = ex.Message;
return(1);
}
}
// public static string Email;
static public bool CheckUser(string email, string PW)
{
string sql;
SqlLite sq = new SqlLite();
sql = " select ifnull(UserID,0) as UserID from UsersLogin where email = '" + email + "' and PW = '" + PW + "' ";
UserID = Convert.ToString(sq.GetSingleSet(sql));
if (UserID == "")
{
return(false);
}
else
{
return(true);
}
}
private void btnChangeQty_Click(object sender, RoutedEventArgs e)
{
string sql;
string prodid;
string orderid;
string qty;
SqlLite sq = new SqlLite();
if (Dg1.SelectedItems.Count == 0)
{
return;
}
prodid = ((DataRowView)Dg1.SelectedItems[0]).Row["ProdID"].ToString();
orderid = ((DataRowView)Dg1.SelectedItems[0]).Row["OrderId"].ToString();
qty = ((DataRowView)Dg1.SelectedItems[0]).Row["qty"].ToString();
//clientID = (int)((DataRowView)DG.SelectedItems[0]).Row["ID"];
sql = "update ordersd set qty = " + qty + " where prodid = " + prodid + " and orderid = " + orderid + " ";
sq.GetSingleSet(sql);
}
public string revokCertifcate(string hostname)
{
int requestID;
string serialnumber;
SqlLite sql = new SqlLite();
Certificate cert = new Certificate();
try
{
requestID = sql.returnCertInfo(hostname);
serialnumber = sql.returnCertSerialnumber(hostname);
sql.deleteCertRecord(requestID);
cert.revokeCert(serialnumber);
return("SUCCESS");
}
catch (Exception ex)
{
return(ex.Message);
}
}
public int renewCert(string hostname)
{
int reqid;
string cerificate;
try
{
SqlLite sql = new SqlLite();
reqid = sql.returnCertInfo(hostname);
Certificate cert = new Certificate();
cerificate = cert.getCertificate(reqid);
JObject obj = JObject.Parse(cerificate);
string name = (string)obj["CertValue"];
reqid = cert.RenewCert(name, reqid);
return(reqid);
}
catch (Exception ex)
{
Console.Write(ex.Message);
return(0);
}
}
//get the issue Certificate from the ca
public string getCertificate(int requestID)
{
int iDisposition;
int status;
string strCAConfig;
string pstrCertificate;
SqlLite sql = new SqlLite();
pstrCertificate = null;
CCertConfig objCertConfig = new CCertConfig();
CCertRequest objCertRequest = new CCertRequest();
try
{
strCAConfig = objCertConfig.GetConfig(CC_DEFAULTCONFIG); //connect to the ca
iDisposition = objCertRequest.RetrievePending(requestID, strCAConfig); //getting certificate stauts must before getting the cert
pstrCertificate = objCertRequest.GetCertificate(CR_OUT_BASE64); //retrive the Certificate
status = sql.updateCertInfo(pstrCertificate, requestID); //update cert with more information
if (status == 0)
{
Certificate cert = new Certificate {
CertValue = pstrCertificate
}; //creatre cert with JSON type
string certJson = Newtonsoft.Json.JsonConvert.SerializeObject(cert); //creatre cert with JSON type
return(certJson); //return certificate
}
else
{
return("error Update Certificate Table");
}
}
catch (Exception ex)
{
return("error" + ex.Message);
}
}
private void btnRegister_Click(object sender, RoutedEventArgs e)
{
int NewUserID;
if (checkFields() == false)
{
MessageBox.Show("Поля со * обязательны для заполнения.");
return;
}
if (CheckPassword(txtPW.Password) == "Пароль должен содержать не менее 6 символов!")
{
return;
}
SqlLite sq = new SqlLite();
NewUserID = Convert.ToInt16(sq.GetSingleSet("Select ifnull(max(UserID),0)+1 from UsersLogin"));
sq.Create("UsersLogin", Convert.ToString(NewUserID) + ",'" + txtEmail.Text + "','" + txtPW.Password + "'", "UserID,Email,PW");
if (txtFio.Text != "" || txtPhone.Text != "")
{
sq.Create("Users", Convert.ToString(NewUserID) + ",'" + txtFio.Text + "','" + txtPhone.Text + "'", "UserID,Fio,Phone");
}
// MessageBox.Show("Регистрация прошла успешно, авторизируйтесь в программе.");
login NW = new login();
NW.Show();
NW.txtRegConfirm.Content = "Регистрация прошла успешно, авторизируйтесь в программе.";
NW.txtRegConfirm.Visibility = Visibility.Visible;
this.Close();
}
public static Encript.CompanyModel GetSqlLiteCompany(Foresight.DataAccess.Company company = null, string BaseURL = "", int FromCompanyID = 0)
{
if (company == null && string.IsNullOrEmpty(BaseURL))
{
return(null);
}
BaseURL = company != null ? company.BaseURL : BaseURL;
FromCompanyID = company != null ? company.CompanyID : FromCompanyID;
var my_company = SqlLite.GetMyCompany(BaseURL, FromCompanyID);
if (company == null)
{
return(my_company);
}
if (my_company == null)
{
my_company = new Encript.CompanyModel();
my_company.CompanyID = company.CompanyID;
my_company.BaseURL = company.BaseURL;
SqlLite.InsertCompany(my_company);
}
my_company.CompanyName = company.CompanyName;
my_company.IsActive = company.IsActive;
my_company.BaseURL = company.BaseURL;
my_company.UserCount = company.UserCount;
my_company.ServerStartTime = company.ServerStartTime;
my_company.ServerEndTime = company.ServerEndTime;
my_company.IsPay = company.IsPay;
my_company.IsAdmin = company.IsAdmin;
my_company.IsCustomer = company.IsCustomer;
my_company.Login_LogImg = company.Login_LogImg;
my_company.Login_BodyImg = company.Login_BodyImg;
my_company.Home_LogoImg = company.Home_LogoImg;
my_company.VersionCode = company.VersionCode;
return(my_company);
}
private void btnAdd_Click(object sender, RoutedEventArgs e)
{
string ProdID;
string sql;
string UserID = Login.UserID;
SqlLite sq = new SqlLite();
if (DG.SelectedItems.Count == 0)
{
return;
}
ProdID = ((DataRowView)DG.SelectedItems[0]).Row["ProdID"].ToString();
//clientID = (int)((DataRowView)DG.SelectedItems[0]).Row["ID"];
sql = "select OrderState from Orders where UserID = '" + UserID + "' and OrderState = 1";
if (Convert.ToInt16(sq.GetSingleSet(sql)) == 1)
{
check = true;
}
else
{
check = false;
}
if (check == false)
{
sql = " Insert Into Orders(OrderID, OrderDate, UserID, OrderState) Select (Select IFNull(Max(OrderID)+1,1) From Orders) OrderID, DATETIME() OrderDate, '" + UserID + "' UserID, 1 OrderState";
sq.GetSingleSet(sql);
check = true;
}
sql = "Insert Into OrdersD(OrderID, SrcPosID, ProdID, qty) SELECT (SELECT MAX(OrderID) FROM Orders WHERE UserID = '" + UserID + "' AND OrderState = 1) OrderID, (SELECT IFNull(MAX(SrcPosID)+1,1) FROM OrdersD WHERE OrderID = (SELECT MAX(OrderID) FROM Orders WHERE UserID = '" + UserID + "' AND OrderState = 1)) SrcPosID, " + ProdID + " ProdID, 1 qty";
sq.GetSingleSet(sql);
}
// create the certifcate request
public string createCertifcate(string hostName)
{
// Create all the objects that will be required
CX509CertificateRequestPkcs10 objPkcs10 = new CX509CertificateRequestPkcs10();
CX509PrivateKey objPrivateKey = new CX509PrivateKey();
CCspInformation objCSP = new CCspInformation();
CCspInformations objCSPs = new CCspInformations();
CX500DistinguishedName objDN = new CX500DistinguishedName();
CX509Enrollment objEnroll = new CX509Enrollment();
CObjectIds objObjectIds = new CObjectIds();
CObjectId objObjectId = new CObjectId();
CX509ExtensionKeyUsage objExtensionKeyUsage = new CX509ExtensionKeyUsage();
CX509ExtensionEnhancedKeyUsage objX509ExtensionEnhancedKeyUsage = new CX509ExtensionEnhancedKeyUsage();
string CertifcateStr;
try
{
SqlLite sql = new SqlLite();
/*Check if there is allready request for the hostname so we dont need to create new one*/
if (sql.checkCertExsits(hostName) == 1)
{
return("Exsits");
}
if (sql.checkCertExsits(hostName) == 2)
{
return("Issued");
}
//create the private key (CX509CertificateRequestPkcs10 will initilizae from the private key)
objCSP.InitializeFromName("Microsoft Enhanced Cryptographic Provider v1.0");
objCSPs.Add(objCSP);
objPrivateKey.Length = 1024;
objPrivateKey.KeySpec = X509KeySpec.XCN_AT_SIGNATURE;
objPrivateKey.KeyUsage = X509PrivateKeyUsageFlags.XCN_NCRYPT_ALLOW_ALL_USAGES;
objPrivateKey.MachineContext = false;
objPrivateKey.CspInformations = objCSPs;
objPrivateKey.Create();
//create pkc10 object from the privaet key
objPkcs10.InitializeFromPrivateKey(X509CertificateEnrollmentContext.ContextUser, objPrivateKey, "");
objExtensionKeyUsage.InitializeEncode(CERTENROLLLib.X509KeyUsageFlags.XCN_CERT_DIGITAL_SIGNATURE_KEY_USAGE | CERTENROLLLib.X509KeyUsageFlags.XCN_CERT_NON_REPUDIATION_KEY_USAGE |
CERTENROLLLib.X509KeyUsageFlags.XCN_CERT_KEY_ENCIPHERMENT_KEY_USAGE | CERTENROLLLib.X509KeyUsageFlags.XCN_CERT_DATA_ENCIPHERMENT_KEY_USAGE);
// objPkcs10.X509Extensions.Add((CX509Extension)objExtensionKeyUsage);
// objObjectId.InitializeFromValue("1.3.6.1.5.5.7.3.2");
// objObjectIds.Add(objObjectId);
// objX509ExtensionEnhancedKeyUsage.InitializeEncode(objObjectIds);
// objPkcs10.X509Extensions.Add((CX509Extension)objX509ExtensionEnhancedKeyUsage);
objDN.Encode("CN=" + hostName, X500NameFlags.XCN_CERT_NAME_STR_NONE); //create DistinguishedName
objPkcs10.Subject = objDN; //initial the DistinguishedName
objEnroll.InitializeFromRequest(objPkcs10); //init enrollement request
CertifcateStr = objEnroll.CreateRequest(EncodingType.XCN_CRYPT_STRING_BASE64); //Certifcate Request
return(CertifcateStr);
}
catch (Exception ex)
{
return("Error" + ex.Message);
}
}
