public static bool PushSetting(SettingsDBData data)
{
var connected = Extensions.CheckForInternetConnection();
if (!connected)
{
return(false);
}
SQL_Request req = new SQL_Request().OpenConnection();
//barcode was scanned or manually inputted
var sql_settings = GetSetting(data.Appname);
if (sql_settings == null)
{
var result = req.SettingsAdd(data.Appname, data.XmlData, data.XmlData2, data.XmlData3, data.XmlData4, data.XmlData5);
return(true);
}
try
{
var result = req.SettingsUpdate(data.Appname, data.XmlData, data.XmlData2, data.XmlData3, data.XmlData4, data.XmlData5);
}
catch
{
return(false);
}
finally { req.CloseConnection(); }
return(true);
}
protected void DeleteSQL_Click(object sender, EventArgs e)
{
SQL_Request req = new SQL_Request().OpenConnection();
try
{
//request all assets
req.GetAllData(false);
if (req.Tag != null)
{
var cloud = req.Tag as List <Asset>;
foreach (var asset in cloud)
{
try
{
req.DeleteData(asset.AssetNumber, false);
}
catch { Notification.Text = "SQL Erase Error"; }
}
req.CloseConnection();
Notification.Text = "SQL Erase Success";
}
}
catch
{
Notification.Text = "SQL Erase Error";
}
finally
{
req.CloseConnection();
}
}
protected void SendSQL_Click(object sender, EventArgs e)
{
SQL_Request req = new SQL_Request().OpenConnection();
//request all assets
req.GetAllData(false);
//merge all assets
//post merged assets as new master DB
if (req.Tag != null)
{
var cloud = req.Tag as List <Asset>;
foreach (Asset a in Global._Library.Assets)
{
try
{
var lookup = cloud.FindAssetByNumber(a.AssetNumber);
if (lookup == null)
{
req.AddAsset(a, false);
}
else
if (lookup.AssetNumber == a.AssetNumber)
{
req.UpdateData(a, false);
}
}
catch { Notification.Text = "SQL-LINK Error"; }
}
Notification.Text = "SQL-LINK Complete";
}
req.CloseConnection();
}
public static Asset GetHistory(string num, string date)
{
try
{
num = ParseBarCode(num);
var connected = Extensions.CheckForInternetConnection();
if (!connected)
{
var a = from ass in Global.Library.Assets where ass.AssetNumber == num select ass;
var al = a.ToList();
var asset = al.FirstOrDefault();
asset.Images = asset.Images.Replace(",,,", "<@#$>").Replace(",", "").Replace("<@#$>", ",").Replace("Images", "").Replace("\\", "");
HttpContext.Current.Session["CurrentAsset"] = asset;
var ret = GetAssetByDateShipped(asset, date);
return(ret);
}
//barcode was scanned or manually inputted
num = ParseScanInput(num);
SQL_Request req = new SQL_Request().OpenConnection();
req.GetAsset(num);
if (req.Tag is Asset)
{
(req.Tag as Asset).Images = (req.Tag as Asset).Images.Replace(",,,", "<@#$>").Replace(",", "").Replace("<@#$>", ",").Replace("Images", "").Replace("\\", "");
HttpContext.Current.Session["CurrentAsset"] = req.Tag as Asset;
var ret = GetAssetByDateShipped((req.Tag as Asset), date);
return(ret);
}
else
{
return(new Asset());
}
}
catch { return(new Asset()); }
}
public static bool AssetOnHold(string num, string b)
{
num = ParseBarCode(num);
foreach (var asset in GetAllAssets())
{
if (asset.AssetNumber == num)
{
try
{
asset.OnHold = Convert.ToBoolean(b);
SQL_Request req = new SQL_Request().OpenConnection();
req.UpdateAsset(asset);
if (req.Success == true)
{
return(true);
}
else
{
return(false);
}
}
catch
{
return(false);
}
}
}
return(false);
}
public static List <Asset> GetAllAssets()
{
//check internet for online offlne access
var connected = Extensions.CheckForInternetConnection();
if (!connected)
{
//load local
var file = HttpContext.Current.Server.MapPath("/App_Data/library.xml");
Extensions.ImportXmlLibraryFile(file);
return(Global.Library.Assets);
}
SQL_Request req = new SQL_Request().OpenConnection();
//request all assets
req.GetAllAssets(true);
if (req.Tag != null)
{
Global.Library.Assets = req.Tag as List <Asset>;
}
else
{
var file = HttpContext.Current.Server.MapPath("/App_Data/library.xml");
Extensions.ImportXmlLibraryFile(file);
return(Global.Library.Assets);
}
return(Global.Library.Assets);
}
public static bool PushSettings(Settings settings, string AppName = "AWP_STARRAG_US")
{
string xmldata = settings.Serialize();
var connected = Extensions.CheckForInternetConnection();
if (!connected)
{
return(false);
}
SQL_Request req = new SQL_Request().OpenConnection();
//barcode was scanned or manually inputted
var sql_settings = GetSettings();
if (sql_settings == null)
{
var result = req.SettingsAdd(AppName, xmldata);
return(true);
}
try
{
var result = req.SettingsUpdate(AppName, xmldata);
}
catch
{
return(false);
}
finally { req.CloseConnection(); }
return(true);
}
public static SettingsDBData GetSetting(string guid)
{
var connected = Extensions.CheckForInternetConnection();
if (!connected)
{
return(null);
}
//barcode was scanned or manually inputted
SQL_Request req = new SQL_Request().OpenConnection();
req.SettingsGet(guid);
if (req.Tag is SettingsDBData)
{
try
{
var dbdata = req.Tag as SettingsDBData;
return(dbdata);
}
catch { return(null); }
}
else
{
return(null);
}
}
public static bool PushSetting(string xml, string guid)
{
string xmldata = xml;
var connected = Extensions.CheckForInternetConnection();
if (!connected)
{
return(false);
}
SQL_Request req = new SQL_Request().OpenConnection();
//barcode was scanned or manually inputted
var sql_settings = GetSetting(guid);
if (sql_settings == null)
{
var result = req.SettingsAdd(guid, xmldata);
return(true);
}
try
{
var result = req.SettingsUpdate(guid, xmldata);
}
catch
{
return(false);
}
finally { req.CloseConnection(); }
return(true);
}
public static Settings GetSettings(string AppName = "AWP_STARRAG_US")
{
var connected = Extensions.CheckForInternetConnection();
if (!connected)
{
return(Global.Library.Settings);
}
//barcode was scanned or manually inputted
SQL_Request req = new SQL_Request().OpenConnection();
req.SettingsGet(AppName);
if (req.Tag is SettingsDBData)
{
try
{
var dbdata = req.Tag as SettingsDBData;
var settings = new Settings().DeserializeFromXmlString <Settings>(dbdata.XmlData);
return(settings);
}
catch { return(new Settings()); }
}
else
{
return(null);
}
}
protected void PullSQL_Click(object sender, EventArgs e)
{
try
{
SQL_Request req = new SQL_Request().OpenConnection();
//request all assets
req.GetAllAssets(true);
if (req.Tag != null)
{
//pull assets
Global.Library.Assets = req.Tag as List <Asset>;
//pull settings
//pull notifications
//pull calibrations
//pull history
}
}
catch
{
//PopNotify("Error", "Error Pulling Library From SQL");
}
//PopNotify("Complete", "Library Pulled From SQL");
UpdateAssetAdmin();
}
protected void DeleteSettingsSQL_Click(object sender, EventArgs e)
{
try
{
SQL_Request req = new SQL_Request().OpenConnection();
req.SettingsDelete();
Global.Library.Settings = new Settings();
}
catch { }
}
public static async Task <SQL_Request> OpenConnection(string connectionString = null)
{
if (connectionString == null)
{
SQL_Request request = new SQL_Request();
return(await Task.FromResult <SQL_Request>(request.OpenConnection(SQLfunc._ConnectionString)));
}
else
{
SQL_Request request = new SQL_Request();
return(await Task.FromResult <SQL_Request>(request.OpenConnection(connectionString)));
}
}
//Settings AWP_STARRAG_US
public static SQL_Request SettingsAdd(this SQL_Request request, string AppName, string XmlData, string XmlData2 = "", string XmlData3 = "", string XmlData4 = "", string XmlData5 = "", bool close = true)
{
if (request.Connection.State == ConnectionState.Closed)
{
request.Connection.Open();
}
request.Command = new SqlCommand();
try
{
if (request.Connection.State == ConnectionState.Closed)
{
request.Connection.Open();
}
request.Command = new SqlCommand();
request.Command.Connection = request.Connection; //Pass the connection object to Command
request.Command.CommandType = CommandType.StoredProcedure; // We will use stored procedure.
request.Command.CommandText = "SettingsInsert"; //Stored Procedure Name
request.Command.Parameters.Add("@AppName", SqlDbType.NVarChar).Value = AppName;
request.Command.Parameters.Add("@XmlData", SqlDbType.NVarChar).Value = XmlData;
request.Command.Parameters.Add("@XmlData2", SqlDbType.NVarChar).Value = XmlData2;
request.Command.Parameters.Add("@XmlData3", SqlDbType.NVarChar).Value = XmlData3;
request.Command.Parameters.Add("@XmlData4", SqlDbType.NVarChar).Value = XmlData4;
request.Command.Parameters.Add("@XmlData5", SqlDbType.NVarChar).Value = XmlData5;
request.Command.ExecuteNonQuery();
request.Success = true;
request.Message = "success";
}
catch (Exception ex)
{
request.Error.Ex = ex;
request.Success = false;
request.Message = "error";
return(request);
}
finally
{
if (close)
{
request.Connection.Close();
}
}
request.Message = "success";
request.Success = true;
return(request);
}
public static SQL_Request CloseConnection(this SQL_Request request)
{
try
{
request.Connection.Close();
request.Success = true;
}
catch (Exception ex)
{
request.Error.Ex = ex;
request.Success = false;
}
return(request);
}
protected void SendSQL_Click(object sender, EventArgs e)
{
try
{
SQL_Request req = new SQL_Request().OpenConnection();
//request all assets
req.GetAllAssets(false);
//merge all assets
//post merged assets as new master DB
if (req.Tag != null)
{
var cloud = req.Tag as List <Asset>;
//upload assets
foreach (Asset a in Global.Library.Assets)
{
try
{
var lookup = cloud.FindAssetByNumber(a.AssetNumber);
if (lookup == null)
{
req.AddAsset(a, false);
}
else
if (lookup.AssetNumber == a.AssetNumber)
{
req.UpdateAsset(a, false);
}
}
catch { //PopNotify("Error", "Error Pushing Library To SQL");
}
}
//upload settings
//upload history
//upload notifications
//upload Calibrations
}
req.CloseConnection();
}
catch
{
//PopNotify("Error", "Error Pushing Library To SQL");
}
//PopNotify("Complete", "Library Pushed To SQL");
}
public static Task <bool> LibraryAsync()
{
return(Task.Run(() =>
{
try
{
SQL_Request req = new SQL_Request().OpenConnection();
//request all assets
req.GetAssets(false);
//merge all assets
//post merged assets as new master DB
if (req.Tag != null)
{
var cloud = req.Tag as List <Asset>;
//upload assets
foreach (Asset a in Global.AssetCache)
{
try
{
var lookup = cloud.FindAssetByNumber(a.AssetNumber);
if (lookup == null)
{
req.AddAsset(a, false);
}
else
if (lookup.AssetNumber == a.AssetNumber)
{
req.UpdateAsset(a, false);
}
}
catch
{ //PopNotify("Error", "Error Pushing Library To SQL");
}
}
}
req.CloseConnection();
return true;
}
catch { return false; }
}));
}
protected void PullSQL_Click(object sender, EventArgs e)
{
try {
SQL_Request req = new SQL_Request().OpenConnection();
//request all assets
req.GetAllData(true);
if (req.Tag != null)
{
Global._Library.Assets = req.Tag as List <Asset>;
Notification.Text = "SQL Pull Success";
}
}
catch
{
Notification.Text = "SQL Pull Error";
}
}
public static List <Asset> GetAllAssets()
{
SQL_Request req = new SQL_Request().OpenConnection();
req.GetAllData();
if (req.Success)
{
List <Asset> assets = new List <Asset>();
foreach (DataRow row in req.Data.Tables[0].Rows)
{
Asset a = new Asset();
a.ItemName = row.Field <string>("ItemName");
assets.Add(a);
}
return(assets);
}
return(null);
}
public static Asset GetAsset(string num)
{
//barcode was scanned or manually inputted
num = ParseScanInput(num);
SQL_Request req = new SQL_Request().OpenConnection();
req.GetData(num);
if (req.Tag is Asset)
{
(req.Tag as Asset).Images = (req.Tag as Asset).Images.Replace(",,,", "<@#$>").Replace(",", "").Replace("<@#$>", ",").Replace("Images", "").Replace("\\", "");
HttpContext.Current.Session["CurrentAsset"] = req.Tag as Asset;
return(req.Tag as Asset);
}
else
{
return(new Asset());
}
}
public static string _ConnectionString = @"Data Source=lastsoul.database.windows.net;Initial Catalog=db;Integrated Security=False;User ID=yuriebasuta;Password=Gh0stbust3r;Connect Timeout=60;Encrypt=True;TrustServerCertificate=True;ApplicationIntent=ReadWrite;MultiSubnetFailover=False"; // @"Data Source=lastsoul.database.windows.net;Initial Catalog=db;Integrated Security=False;User ID=yuriebasuta;Password=********;Connect Timeout=60;Encrypt=False;TrustServerCertificate=True;ApplicationIntent=ReadWrite;MultiSubnetFailover=False";
public static SQL_Request OpenConnection(this SQL_Request request, string connectionString = null)
{
try
{
if (connectionString == null)
{
connectionString = _ConnectionString;
}
request.Connection = new SqlConnection(connectionString);
request.Success = true;
request.Connection.Open();
}
catch (Exception ex)
{
request.Error.Ex = ex;
request.Success = false;
}
return(request);
}
public static bool UpdateAsset(Asset asset)
{
try
{
SQL_Request req = new SQL_Request().OpenConnection();
req.UpdateAsset(asset);
if (req.Success == true)
{
return(true);
}
else
{
return(false);
}
}
catch
{
return(false);
}
}
public static SQL_Request SettingsDelete(this SQL_Request request, string AppName = "AWP_STARRAG_US", bool close = true)
{
request.Command = new SqlCommand();
try
{
if (request.Connection.State == ConnectionState.Closed)
{
request.Connection.Open();
}
// Create a object of SqlCommand class
request.Command.Connection = request.Connection; //Pass the connection object to Command
request.Command.CommandType = CommandType.StoredProcedure; // We will use stored procedure.
request.Command.CommandText = "SettingsDelete";
request.Command.Parameters.Add("@AppName", SqlDbType.NVarChar).Value = AppName;
request.Command.ExecuteNonQuery();
request.Message = "success:deletedata";
request.Success = true;
}
catch (Exception ex)
{
request.Error.Ex = ex;
request.Success = false;
request.Message = "error:deletedata";
return(request);
}
finally
{
if (close)
{
request.Connection.Close();
}
}
return(request);
}
protected void DeleteSQL_Click(object sender, EventArgs e)
{
SQL_Request req = new SQL_Request().OpenConnection();
try
{
//request all assets
req.GetAllAssets(false);
if (req.Tag != null)
{
var cloud = req.Tag as List <Asset>;
foreach (var asset in cloud)
{
try
{
req.DeleteAsset(asset.AssetNumber, false);
}
catch { //PopNotify("Error", "Error Deleting SQL Library");
}
}
req.CloseConnection();
Global.Library.Assets = new List <Asset>();
}
}
catch
{
//PopNotify("Error", "Error Deleting SQL Library");
}
finally
{
req.CloseConnection();
//PopNotify("Complete", "SQL Library Deleted");
}
}
//Asset
public static SQL_Request AddAsset(this SQL_Request request, Asset asset, bool close = true)
{
if (request.Connection.State == ConnectionState.Closed)
{
request.Connection.Open();
}
request.Command = new SqlCommand();
try
{
string imgs = "";
imgs = asset.Images;
if (request.Connection.State == ConnectionState.Closed)
{
request.Connection.Open();
}
request.Command = new SqlCommand();
request.Command.Connection = request.Connection; //Pass the connection object to Command
request.Command.CommandType = CommandType.StoredProcedure; // We will use stored procedure.
request.Command.CommandText = "AssetInsert"; //Stored Procedure Name
// @AssetName,@AssetNumber,@CalibratedAsset,@Damaged,@OnHold,@BarcodeImage,
//@CalibrationCompany,@CalibrationHistory,@CalibrationPeriod,@DateReturned,
//@DateShipped,@AssetDescription,@LastCalibrated,@OrderNumber,@PersonShipping,
//@PackingSlip,@ReturnReport,@UPSlabel,@Images,@ImageLinks,@ServiceEngineer,@ShipTo,@AssetWeight
request.Command.Parameters.Add("@AssetName", SqlDbType.NVarChar).Value = asset.AssetName;
request.Command.Parameters.Add("@AssetNumber", SqlDbType.NVarChar).Value = asset.AssetNumber;
request.Command.Parameters.Add("@CalibratedAsset", SqlDbType.Bit).Value = asset.IsCalibrated;
request.Command.Parameters.Add("@Damaged", SqlDbType.Bit).Value = asset.IsDamaged;
request.Command.Parameters.Add("@OnHold", SqlDbType.Bit).Value = asset.OnHold;
request.Command.Parameters.Add("@IsOut", SqlDbType.Bit).Value = asset.IsOut;
request.Command.Parameters.Add("@BarcodeImage", SqlDbType.NVarChar).Value = asset.BarcodeImage; //FIX
request.Command.Parameters.Add("@CalibrationCompany", SqlDbType.NVarChar).Value = asset.CalibrationCompany;
string calXml = new CalibrationLibrary().SerializeToXmlString(new CalibrationLibrary());
try
{
calXml = asset.CalibrationHistory.SerializeToXmlString(asset.CalibrationHistory);
}
catch { }
request.Command.Parameters.Add("@CalibrationHistory", SqlDbType.NVarChar).Value = calXml;
request.Command.Parameters.Add("@CalibrationPeriod", SqlDbType.NVarChar).Value = asset.CalibrationPeriod;
request.Command.Parameters.Add("@DateReturned", SqlDbType.NVarChar).Value = asset.DateRecieved.ToString();
request.Command.Parameters.Add("@DateShipped", SqlDbType.NVarChar).Value = asset.DateShipped.ToString();
request.Command.Parameters.Add("@AssetDescription", SqlDbType.NVarChar).Value = asset.Description;
request.Command.Parameters.Add("@LastCalibrated", SqlDbType.NVarChar).Value = asset.LastCalibrated.ToString();
request.Command.Parameters.Add("@OrderNumber", SqlDbType.NVarChar).Value = asset.OrderNumber.ToString();
request.Command.Parameters.Add("@PersonShipping", SqlDbType.NVarChar).Value = asset.PersonShipping;
request.Command.Parameters.Add("@Images", SqlDbType.NVarChar).Value = imgs;
request.Command.Parameters.Add("@ImageLinks", SqlDbType.NVarChar).Value = imgs;
request.Command.Parameters.Add("@ServiceEngineer", SqlDbType.NVarChar).Value = asset.ServiceEngineer;
request.Command.Parameters.Add("@ShipTo", SqlDbType.NVarChar).Value = asset.ShipTo;
request.Command.Parameters.Add("@AssetWeight", SqlDbType.NVarChar).Value = asset.weight.ToString();
try
{
foreach (var ii in asset.History.History)
{
ii.IsHistoryItem = true;
}
}
catch { }
var histxml = asset.History.Serialize();
request.Command.Parameters.Add("@History", SqlDbType.NVarChar).Value = histxml;
request.Command.Parameters.Add("@PackingSlip", SqlDbType.NVarChar).Value = asset.PackingSlip;
request.Command.Parameters.Add("@ReturnReport", SqlDbType.NVarChar).Value = asset.ReturnReport;
request.Command.Parameters.Add("@UPSlabel", SqlDbType.NVarChar).Value = asset.UpsLabel;
request.Command.ExecuteNonQuery();
request.Success = true;
request.Message = "success:addAsset";
}
catch (Exception ex)
{
request.Error.Ex = ex;
request.Success = false;
request.Message = "error:addAsset";
return(request);
}
finally
{
if (close)
{
request.Connection.Close();
}
}
request.Message = "success:addAsset";
request.Success = true;
return(request);
}
public static async Task <SQL_Request> RemoveAsync(this SQL_Request request, string AppName)
{
return(await request.SettingsRemoveAsync(AppName));
}
public static async Task <SQL_Request> UpdateAsync(this SQL_Request request, string AppName, string XmlData, string XmlData2 = "", string XmlData3 = "", string XmlData4 = "", string XmlData5 = "", bool close = true)
{
return(await request.SettingsUpdateAsync(AppName, XmlData, XmlData2, XmlData3, XmlData4, XmlData5, close));
}
public static async Task <SQL_Request> CloseConnection(this SQL_Request request)
{
return(await request.CloseConnection());
}
public static async Task <SQL_Request> GetAsync(this SQL_Request request, string AppName, bool close = true)
{
return(await request.SettingsGetAsync(AppName, close));
}
public static async Task <SQL_Request> GetAllAsync(this SQL_Request request, bool close = true)
{
return(await request.SettingsGetAllAsync(close));
}
