public int addUpdateUsersDetails(BEL obj_d)
{
try
{
SqlParameter[] p = new SqlParameter[17];
p[0] = new SqlParameter("@ShipFname", obj_d.ShipFname);
p[1] = new SqlParameter("@ShipLname", obj_d.ShipLname);
p[2] = new SqlParameter("@ShipEmail", obj_d.ShipEmail);
p[3] = new SqlParameter("@ShipMobile", obj_d.ShipMobile);
p[4] = new SqlParameter("@ShipAddress", obj_d.ShipAddress);
p[5] = new SqlParameter("@ShipNearby", obj_d.ShipNearby);
p[6] = new SqlParameter("@ShipState", obj_d.ShipState);
p[7] = new SqlParameter("@ShipZip", obj_d.ShipZip);
p[8] = new SqlParameter("@BillFname", obj_d.BillFname);
p[9] = new SqlParameter("@BillLname", obj_d.BillLname);
p[10] = new SqlParameter("@BillEmailid", obj_d.BillEmailid);
p[11] = new SqlParameter("@BillContact", obj_d.BillContact);
p[12] = new SqlParameter("@BillAddress", obj_d.BillAddress);
p[13] = new SqlParameter("@BillNearby", obj_d.BillNearby);
p[14] = new SqlParameter("@BillState", obj_d.BillState);
p[15] = new SqlParameter("@BillZip", obj_d.BillZip);
p[16] = new SqlParameter("@TransactionId", obj_d.TransId);
return(MySqlDataAccess.ExecuteNonQuery(MySqlDataAccess.ConnectionString, CommandType.StoredProcedure, "ProaddUpdateUserDetails", p));
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
public IDatabaseHandler CreateDatabase()
{
IDatabaseHandler database = null;
switch (this._databaseConnectionParams.GetProvider())
{
case DatabaseConenctionProviders.SQLServerProvider:
database = new SqlDataAccess(this._databaseConnectionParams.GetConnectionString());
break;
case DatabaseConenctionProviders.MySQLProvider:
database = new MySqlDataAccess(this._databaseConnectionParams.GetConnectionString());
break;
case DatabaseConenctionProviders.PostgreSQLProvider:
database = new PostgresDataAccess(this._databaseConnectionParams.GetConnectionString());
break;
default:
database = new SqlDataAccess(this._databaseConnectionParams.GetConnectionString());
break;
}
return(database);
}
public DataTable getOrderDetailsByDate(string date1, string date2)
{
try
{
string query = "";
if (date1 != "" && date2 != "")
{
query = @"SELECT OrderId, ProductId, TransId, Userid, ShipFname, ShipLname, ShipEmail, ShipCompany,
ShipMobile, ShipAddress, ShipNearby, ShipCountry, ShipState, ShipCity, ShipZip,
(BillFname+' '+BillLname)as Name, BillEmailid, BillCompay, BillContact, BillAddress,
BillNearby, BillCountry, BillState, BillCity, BillZip, Orderdate, Productname, Quantity,
ItemPrice, Thumbnail, Orderdetailid, Currency, Paymentmode, ItemSubTotal, ShippingCharge,
Orderstatus, OrderCancel, UseridentifyNo FROM t_OrderDetail
where Cast(Orderdate AS DATE) >='" + date1 + "' ANd Cast(Orderdate AS DATE)<= '" + date2 + "' order by Orderdate";
}
else
{
query = @"SELECT OrderId, ProductId, TransId, Userid, ShipFname, ShipLname, ShipEmail, ShipCompany,
ShipMobile, ShipAddress, ShipNearby, ShipCountry, ShipState, ShipCity, ShipZip,
(BillFname+' '+BillLname)as Name, BillEmailid, BillCompay, BillContact, BillAddress,
BillNearby, BillCountry, BillState, BillCity, BillZip, Orderdate, Productname, Quantity,
ItemPrice, Thumbnail, Orderdetailid, Currency, Paymentmode, ItemSubTotal, ShippingCharge,
Orderstatus, OrderCancel, UseridentifyNo FROM t_OrderDetail
where Cast(Orderdate AS DATE)='" + date1 + "' order by Orderdate";
}
return(MySqlDataAccess.ExecuteDataTable(MySqlDataAccess.ConnectionString, CommandType.Text, query));
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
public static List <AlpacaKeyModel> LoadAlpacaKeys(string userID)
{
string sql = $"select LiveApiKey, LiveApiSecretKey, PaperApiKey, PaperApiSecretKey from users where Id='{userID}';";
return(MySqlDataAccess.LoadData <AlpacaKeyModel>(sql));
}
public DataTable getShoppingCartItemsOrderOvr(string TransId)
{
try
{
string query = @"select s.CartId, s.TransId, s.ProductId, s.Price, s.Quantity, s.Discount,
s.TotalPrice, s.ShippingCharges, convert(decimal(10,2), ShippingCharges *10 / 65)
as usdshippingCharges, s.Name, s.Thumbnail, s.Capacity, s.UserId,
convert(decimal(10,2), Price - discount)as inprice, convert(decimal(10,2),
(Price - discount) / 65) as usdprice, convert(decimal(10,2), TotalPrice) as
totalinprice, convert(decimal(10,2), TotalPrice/65)as totalusdprice,
s.Capacity, (select FlavourName from Flavour where FlavourID= s.Flavour)as pflavour,
u.ShipFname, u.ShipLname, u.ShipEmail, u.ShipCompany, u.ShipMobile, u.ShipAddress,
u.ShipNearby, (select ContryName from Country where CountryCode= u.ShipCountry)
as Country, (select StateName from State where StateCode= u.ShipState) as State,
u.ShipCity, u.ShipZip, u.BillFname, u.BillLname, u.BillEmailid, u.BillCompay,
u.BillContact, u.BillAddress, u.BillNearby, (select ContryName from Country
where CountryCode= u.BillCountry) as BCountry, (select StateName from State
where StateCode= u.BillState) as BState, u.BillCity, u.BillZip, u.UserIdentifyNo
FROM ShopingCart s inner join t_User u on s.UserId=u.UserId WHERE s.TransId='" + TransId + "'";
return(MySqlDataAccess.ExecuteDataTable(MySqlDataAccess.ConnectionString, CommandType.Text, query));
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
public DataTable getQuantityfromTable(Int32 productid, string variantname)
{
//string query = "Select Quantity from t_Quantity where ProductId='" + productid + "' AND Variantname='"+ variantname + "'";
string query = "Select Quantity, Variantname from t_Quantity where ProductId='" + productid + "'";
return(MySqlDataAccess.ExecuteDataTable(MySqlDataAccess.ConnectionString, CommandType.Text, query));
}
public void AgregarLugar(string lugar)
{
MySqlDataAccess mysqlAccess = new MySqlDataAccess();
OrderedDictionary listParam = new OrderedDictionary();
listParam.Add("nombreLugar", lugar);
var data = mysqlAccess.ExecuteProcedure("AgregarLugar", listParam);
}
public void AgregarColonia(string colonia, string municipio)
{
MySqlDataAccess mysqlAccess = new MySqlDataAccess();
OrderedDictionary listParam = new OrderedDictionary();
listParam.Add("nombreColonia", colonia);
listParam.Add("nombreMunicipio", municipio);
var data = mysqlAccess.ExecuteProcedure("AgregarColonia", listParam);
}
//Insert Order Details
public int InsertProductDetails(BEL obj_d)
{
try
{
SqlParameter[] p = new SqlParameter[43];
p[0] = new SqlParameter("@ProductId", obj_d.ProductId);
p[1] = new SqlParameter("@TransId", obj_d.TransId);
p[2] = new SqlParameter("@Userid", obj_d.Userid);
p[3] = new SqlParameter("@ShipFname", obj_d.ShipFname);
p[4] = new SqlParameter("@ShipLname", obj_d.ShipLname);
p[5] = new SqlParameter("@ShipEmail", obj_d.ShipEmail);
p[6] = new SqlParameter("@ShipCompany", obj_d.ShipCompany);
p[7] = new SqlParameter("@ShipMobile", obj_d.ShipMobile);
p[8] = new SqlParameter("@ShipAddress", obj_d.ShipAddress);
p[9] = new SqlParameter("@ShipNearby", obj_d.ShipNearby);
p[10] = new SqlParameter("@ShipCountry", obj_d.ShipCountry);
p[11] = new SqlParameter("@ShipState", obj_d.ShipState);
p[12] = new SqlParameter("@ShipCity", obj_d.ShipCity);
p[13] = new SqlParameter("@ShipZip", obj_d.ShipZip);
p[14] = new SqlParameter("@BillFname", obj_d.BillFname);
p[15] = new SqlParameter("@BillLname", obj_d.BillLname);
p[16] = new SqlParameter("@BillEmailid", obj_d.BillEmailid);
p[17] = new SqlParameter("@BillCompay", obj_d.BillCompay);
p[18] = new SqlParameter("@BillContact", obj_d.BillContact);
p[19] = new SqlParameter("@BillAddress", obj_d.BillAddress);
p[20] = new SqlParameter("@BillNearby", obj_d.BillNearby);
p[21] = new SqlParameter("@BillCountry", obj_d.BillCountry);
p[22] = new SqlParameter("@BillState", obj_d.BillState);
p[23] = new SqlParameter("@BillCity", obj_d.BillCity);
p[24] = new SqlParameter("@BillZip", obj_d.BillZip);
p[25] = new SqlParameter("@Productname", obj_d.Productname);
p[26] = new SqlParameter("@Quantity", obj_d.Quantity);
p[27] = new SqlParameter("@Thumbnail", obj_d.Thumbnail);
p[28] = new SqlParameter("@Orderdetailid", obj_d.Orderdetailid);
p[29] = new SqlParameter("@Currency", obj_d.Currency);
p[30] = new SqlParameter("@Paymentmode", obj_d.Paymentmode);
p[31] = new SqlParameter("@ItemSubTotal", obj_d.ItemSubtotal);
p[32] = new SqlParameter("@ShippingCharge", obj_d.ShippinCharge);
p[33] = new SqlParameter("@ItemPrice", obj_d.ItemPrice);
p[34] = new SqlParameter("@Orderstatus", obj_d.Orderstatus);
p[35] = new SqlParameter("@OrderCancel", obj_d.OrderCancel);
p[36] = new SqlParameter("@Useridentifyno", obj_d.UserIdetifyNo);
p[37] = new SqlParameter("@Capacity", obj_d.Capacity);
p[38] = new SqlParameter("@Flavour", obj_d.Flavour);
p[39] = new SqlParameter("@VariantName", obj_d.Variantname);
p[40] = new SqlParameter("@VariantQuantity", obj_d.Variantquantity);
p[41] = new SqlParameter("@CouponPrice", obj_d.finalCouponPrice);
p[42] = new SqlParameter("@FinalShippingCharges", obj_d.FinalShippingCharges);
return(MySqlDataAccess.ExecuteNonQuery(MySqlDataAccess.ConnectionString, CommandType.StoredProcedure, "ProAddOrderDetails", p));
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
public DataTable getWishIdOnTransId(string transid)
{
try
{
return(MySqlDataAccess.ExecuteDataTable(MySqlDataAccess.ConnectionString, CommandType.Text, "select WishId, UserId from t_Wishlist where TransId='" + transid + "'"));
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
public DataTable getProductByProductId(string Productid)
{
try
{
return(MySqlDataAccess.ExecuteDataTable(MySqlDataAccess.ConnectionString, CommandType.StoredProcedure, "ProGetMiniById", new SqlParameter("@ProductId", Productid)));
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
public DataTable GetSubCategoryById(string subcategoryid)
{
try
{
return(MySqlDataAccess.ExecuteDataTable(MySqlDataAccess.ConnectionString, CommandType.StoredProcedure, "ProGetMiniCategoryById", new SqlParameter("@SubCategoryId", subcategoryid)));
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
public DataTable getUserInformation(string userid)
{
try
{
string query = "Select UserId, (ShipFname+' '+ShipLname)as Shippingname, ShipEmail, ShipCompany, ShipMobile, ShipAddress, ShipNearby, ShipCountry, (select ContryName from Country where CountryCode= t_User.ShipCountry) as Country, ShipState, (select StateName from State where StateCode= t_User.ShipState)as State, ShipCity, ShipZip, (BillFname+' '+BillLname)as Billingname, BillEmailid, BillCompay, BillContact, BillAddress, BillNearby, BillCountry, (select ContryName from Country where CountryCode= t_User.BillCountry)as BCountry, BillState, (select StateName from State where StateCode= t_User.BillState)as BState, BillCity, BillZip from t_User where UserId='" + userid + "'";
return(MySqlDataAccess.ExecuteDataTable(MySqlDataAccess.ConnectionString, CommandType.Text, query));
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
public DataTable getAllCategory()
{
try
{
string query = "select CategoryId, CategoryName from t_Category";
return(MySqlDataAccess.ExecuteDataTable(MySqlDataAccess.ConnectionString, CommandType.Text, query));
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
public DataTable getUserIdetifyNumber(string userid)
{
try
{
string query = @"select UserIdentifyNo from t_User where UserId='" + userid + "'";
return(MySqlDataAccess.ExecuteDataTable(MySqlDataAccess.ConnectionString, CommandType.Text, query));
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
public DataTable getProductId(string transactionid)
{
try
{
string query = @"select ProductId from ShopingCart where TransId='" + transactionid + "'";
return(MySqlDataAccess.ExecuteDataTable(MySqlDataAccess.ConnectionString, CommandType.Text, query));
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
public DataTable getOrderCompletetoUser(string TransId)
{
try
{
string query = @"Select OrderId, ProductId,(select Name from t_Product where ProductId= t_OrderDetail.ProductId)as Name, TransId, Userid, Orderdate, Productname, Quantity, ItemPrice, Thumbnail, Orderdetailid, Currency, Paymentmode, ItemSubTotal, ShippingCharge, Orderstatus, OrderCancel, VariantName FROM t_OrderDetail WHERE TransId='" + TransId + "' AND Orderstatus='Order'";
return(MySqlDataAccess.ExecuteDataTable(MySqlDataAccess.ConnectionString, CommandType.Text, query));
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
public DataTable getState()
{
try
{
string query = @"Select StateID, StateCode, StateName, CountryCode from State";
return(MySqlDataAccess.ExecuteDataTable(MySqlDataAccess.ConnectionString, CommandType.Text, query));
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
public DataTable getCategoryOnBrand(string brandname)
{
try
{
string query = @"select c.CategoryId,c.CategoryName from t_Category c inner join t_ProductCategory pc on c.CategoryId = pc.CategoryId inner join t_Product p on pc.ProductId= p.ProductId where p.Brand='" + brandname + "'";
return(MySqlDataAccess.ExecuteDataTable(MySqlDataAccess.ConnectionString, CommandType.Text, query));
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
//User Account
public DataTable getUserShippingAddress(string userid)
{
try
{
string query = @"select UserId, (ShipFname+' '+ShipLname)as Shippingname, ShipAddress, ShipNearby, (select ContryName from Country where CountryCode= t_User.ShipCountry) as Country, (select StateName from State where StateCode= t_User.ShipState)as State, ShipCity, ShipZip from t_User where UserId='" + userid + "'";
return(MySqlDataAccess.ExecuteDataTable(MySqlDataAccess.ConnectionString, CommandType.Text, query));
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
public DataTable getWishListCurrentNo(string transid)
{
try
{
string query = "select COUNT(WishId) as wishNum from t_Wishlist where TransId='" + transid + "'";
return(MySqlDataAccess.ExecuteDataTable(MySqlDataAccess.ConnectionString, CommandType.Text, query));
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
public DataTable getMenuSubCategory(string categoryid)
{
try
{
string query = "SELECT SubCategoryId, SubName FROM t_SubCategory WHERE CategoryId='" + categoryid + "'";
return(MySqlDataAccess.ExecuteDataTable(MySqlDataAccess.ConnectionString, CommandType.Text, query));
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
public DataTable getShippingCharges(string transid)
{
try
{
string query = "Select ShippingCharges, convert(decimal(10,2), ShippingCharges *10 /65) as usdshipping, Quantity From ShopingCart where TransId='" + transid + "'";
return(MySqlDataAccess.ExecuteDataTable(MySqlDataAccess.ConnectionString, CommandType.Text, query));
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
public DataTable getCartItemNum(string transid)
{
try
{
string query = "select COUNT(TransId) as Items from ShopingCart where TransId='" + transid + "'";
return(MySqlDataAccess.ExecuteDataTable(MySqlDataAccess.ConnectionString, CommandType.Text, query));
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
public DataTable getBrands()
{
try
{
string query = "select BrandId, Brandname,(select CategoryName from t_Category where CategoryId= t_Brand.Categoryid)as Category, Categoryid, BrandImage from t_Brand Order by BrandId";
return(MySqlDataAccess.ExecuteDataTable(MySqlDataAccess.ConnectionString, CommandType.Text, query));
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
public DataTable getOldOrderCompletetoUser(string userid)
{
try
{
string query = @"Select top 20 OrderId, ProductId,(select Name from t_Product where ProductId= t_OrderDetail.ProductId)as Name, TransId, Userid, Orderdate, Productname, Quantity, ItemPrice, Thumbnail, Orderdetailid, Currency, Paymentmode, ItemSubTotal, ShippingCharge, Orderstatus, OrderCancel, Orderdate, convert(varchar(11), Orderdate, 106) AS [Order Placed], VariantName FROM t_OrderDetail WHERE Userid='" + userid + "' order by OrderId DESC";
return(MySqlDataAccess.ExecuteDataTable(MySqlDataAccess.ConnectionString, CommandType.Text, query));
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
public DataTable getproductQuantityById(Int32 productId)
{
try
{
string query = @"Select Quantity, ProductId, FlavourID FROM t_Quantity WHERE ProductId='" + productId + "'";
return(MySqlDataAccess.ExecuteDataTable(MySqlDataAccess.ConnectionString, CommandType.Text, query));
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
//Shopping Cart
public DataTable getDataInShoppingCart(string TransId)
{
try
{
string query = @"Select *, (ShippingCharges)as sprice, convert(decimal(10,2), Price - discount)as iprice, convert(decimal(10,2), (Price - discount) / 65) as usdprice, convert(decimal(10,2), TotalPrice) as totalprice1, convert(decimal(10,2), TotalPrice/65)as totalusdprice FROM ShopingCart WHERE TransId='" + TransId + "'";
return(MySqlDataAccess.ExecuteDataTable(MySqlDataAccess.ConnectionString, CommandType.Text, query));
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
public DataTable getCompareItemsNo(string transid)
{
try
{
string query = "select COUNT(CompareId) as compare from CompareProduct where ComSession='" + transid + "'";
return(MySqlDataAccess.ExecuteDataTable(MySqlDataAccess.ConnectionString, CommandType.Text, query));
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
public DataTable getTotalPriceShoppingCart(string TransId)
{
try
{
string query = @"Select sum(cast(TotalPrice as decimal(18,2)))as totalprice2 FROM ShopingCart WHERE TransId='" + TransId + "'";
return(MySqlDataAccess.ExecuteDataTable(MySqlDataAccess.ConnectionString, CommandType.Text, query));
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
public DataTable getProductBrand()
{
try
{
string query = "select Distinct Brand from t_Product Order by Brand ASC";
return(MySqlDataAccess.ExecuteDataTable(MySqlDataAccess.ConnectionString, CommandType.Text, query));
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
public DataTable getUserBillingAddress2(string transid)
{
try
{
string query = @"select UserId, (BillFname+' '+BillLname)as Billingname, BillAddress, BillNearby, (select ContryName from Country where CountryCode= t_User.BillCountry) as Country, (select StateName from State where StateCode= t_User.BillState)as State, BillCity, BillZip from t_User where Transid='" + transid + "'";
return(MySqlDataAccess.ExecuteDataTable(MySqlDataAccess.ConnectionString, CommandType.Text, query));
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
public void AgregarGrabacion(string lugar, string brigada, string colonia, string municipio, string nombre, DateTime edad)
{
MySqlDataAccess mysqlAccess = new MySqlDataAccess();
OrderedDictionary listParam = new OrderedDictionary();
listParam.Add("pLugar", lugar);
listParam.Add("pBrigada", brigada);
listParam.Add("pColonia", colonia);
listParam.Add("pMunicipio", municipio);
listParam.Add("pNombre", nombre);
listParam.Add("pEdad", edad);
var data = mysqlAccess.ExecuteProcedure("AgregarGrabacion", listParam);
}
public List<Reporte> GetReporte( string edad, string brigada, string lugar, string municipio, string colonia,
DateTime fechaInicial, DateTime fechaHasta)
{
try
{
List<Reporte> list = new List<Reporte>();
MySqlDataAccess mysqlAccess = new MySqlDataAccess();
OrderedDictionary listParam = new OrderedDictionary();
listParam.Add("pEdad", int.Parse(edad));
listParam.Add("pBrigada", brigada);
listParam.Add("pLugar", lugar);
listParam.Add("pMunicipio", municipio);
listParam.Add("pColonia", colonia);
listParam.Add("pFechaInicial", fechaInicial.Date);
listParam.Add("pFechaFinal", fechaHasta.Date);
var data = mysqlAccess.ExecuteProcedure("ObtenerReporte", listParam);
foreach (DataRow row in data.Rows)
{
Reporte itemReporte = new Reporte();
itemReporte.Brigada = row["brigada"].ToString();
itemReporte.Colonia = row["colonia"].ToString();
DateTime edadD = Convert.ToDateTime(row["edad"].ToString());
itemReporte.Edad = (DateTime.Now.Year - edadD.Year).ToString();
itemReporte.Lugar = row["lugar"].ToString();
itemReporte.Municipio = row["municipio"].ToString();
itemReporte.Nombre = row["nombre"].ToString();
itemReporte.FechaInicial = Convert.ToDateTime(row["creado"].ToString());
list.Add(itemReporte);
}
return list;
}
catch (Exception ex)
{
throw ex;
}
// return ParseDataTableComboBox(data);
}
public bool ValidateUser(string userName, string pass)
{
bool respu = false;
MySqlDataAccess mysqlAccess = new MySqlDataAccess();
OrderedDictionary listParam = new OrderedDictionary();
listParam.Add("userName", userName);
listParam.Add("pass", pass);
var data = mysqlAccess.ExecuteProcedure("ValidarUsuario", listParam);
if (data.Rows.Count > 0)
{
respu = true;
}
return respu;
}
public List<ComboBoxD> GetLugares()
{
List<ComboBoxD> list = new List<ComboBoxD>();
MySqlDataAccess mysqlAccess = new MySqlDataAccess();
var data = mysqlAccess.ExecuteProcedure("ObtenerLugares", null);
return ParseDataTableComboBox(data);
}
