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 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 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 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 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 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 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 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 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);
}
}
//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 getShoppingCartItemsGuest(string TransId)
{
try
{
string query = @"Select s.CartId, s.TransId, s.ProductId, s.Price, s.VariantName, s.VariantQuantity, s.Quantity, s.Quantity, s.Discount, s.TotalPrice, s.ShippingCharges, s.FinalShippingCharges, 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, s.Flavour, u.ShipFname, u.ShipLname, u.ShipEmail, u.ShipCompany, u.ShipMobile, u.ShipAddress, u.ShipNearby, u.ShipCountry, u.ShipState, u.ShipCity, u.ShipZip, u.BillFname, u.BillLname, u.BillEmailid, u.BillCompay, u.BillContact, u.BillAddress, u.BillNearby, u.BillCountry, u.BillState, u.BillCity, u.BillZip, u.UserIdentifyNo VariantName, ShippingCharges, CouponPrice, FinalShippingCharges FROM ShopingCart s inner join t_User u on s.TransId=u.TransId WHERE s.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 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 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 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 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);
}
}
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 getCurrencyBySession(string TransId)
{
try
{
string query = @"select Currency FROM t_OrderDetail WHERE TransId='" + TransId + "'";
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 getSubCategoryInCategoery(string categoryid)
{
try
{
string query = "Select MiniCategoryId, SubCategoryId, MiniName from t_MiniCategory where SubCategoryId='" + categoryid + "'";
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 getSubTotalShoppingCart(string TransId, string Id)
{
try
{
string query = @"select CartId, TransId, Price, Quantity, TotalPrice, ShippingCharges FROM ShopingCart WHERE TransId='" + TransId + "' AND CartId='" + Id + "'";
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 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);
}
}
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 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 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);
}
}