public FileResult ExportExcelForZalo()
{
ShopDataContex db = new ShopDataContex();
DataTable dt = new DataTable("Grid");
dt.Columns.AddRange(new DataColumn[2]
{
new DataColumn("Tên sản phẩm"),
new DataColumn("Link")
});
var Menus = Shop.Web.Model.YKienKhachHangModel.GetDataforZalo();
//var Menus = db.Menu.Where(o => o.idControl == 11 && o.ok && o.HasValue && o.HasOnHand && o.DungSai && o.TenNgan != null && o.MoTaNgan != null &&o.sDate>= "2017-07-18 17:00:00.000" && o.sDate<= '2017-08-08 08:24:22.222').OrderByDescending(o => o.sDateOk).ToList();
foreach (var product in Menus)
{
string tensp = product.TenNgan;
string link = "https://beautygarden.vn/" + product.Link + ".html";
dt.Rows.Add(tensp, link);
}
using (XLWorkbook wb = new XLWorkbook())
{
wb.Worksheets.Add(dt);
using (MemoryStream stream = new MemoryStream())
{
wb.SaveAs(stream);
return(File(stream.ToArray(), "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", "ExcelforZalo.xlsx"));
}
}
}
public JsonResult DeleteBarcode(int id, int IdMenu)
{
//khi xoa ma vach chia ra 2 TH
//1. neu ma vach do co 1 dong: tuc la khong co gi thi cap nhat lai trang thai ben bang Menu
//2. neu ma vach co nhieu dong: tuc la mau hoac mui thi khi nao xoa het tat ca cac dong moi cap nhat trang thai hasvalue ben bang Menu
var idxoa = _menuOptionRepository.GetById(id);
//kiem tra san pham do co bao nhieu dong
int sodongtrongSP = _menuOptionRepository.LaySoLuongBarcodeInMenuOption(IdMenu);
_menuOptionRepository.Delete(idxoa);
_unitOfWork.Commit();
if (sodongtrongSP == 1) //tuc la ma vach khong co gi
{
//update san pham do thanh san pham chua co ma vach
using (var context = new ShopDataContex())
{
context.Database.ExecuteSqlCommand("update Menu set HasValue='False' where id_ ={0}", IdMenu);
}
}
return(Json(new
{
ok = true,
message = "Xóa mã vạch thành công!"
}, JsonRequestBehavior.AllowGet));
}
public FileResult ExportExcel()
{
ShopDataContex db = new ShopDataContex();
DataTable dt = new DataTable("Grid");
dt.Columns.AddRange(new DataColumn[1]
{
new DataColumn("Email")
});
var Menus = _emailRepository.GetAll().ToList();
foreach (var product in Menus)
{
string email = product.Emails;
dt.Rows.Add(email);
}
using (XLWorkbook wb = new XLWorkbook())
{
wb.Worksheets.Add(dt);
using (MemoryStream stream = new MemoryStream())
{
wb.SaveAs(stream);
return(File(stream.ToArray(), "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", "NhanTinKhuyenMai.xlsx"));
}
}
}
public FileResult ExportExcel()
{
ShopDataContex db = new ShopDataContex();
DataTable dt = new DataTable("Grid");
dt.Columns.AddRange(new DataColumn[3]
{
new DataColumn("Họ tên"),
new DataColumn("Số điện thoại"),
new DataColumn("Email")
});
var Menus = _accMember298Repository.GetAll().ToList();
foreach (var product in Menus)
{
string hoten = product.Fullname;
string std = product.Phone;
string email = product.Email;
dt.Rows.Add(hoten, std, email);
}
using (XLWorkbook wb = new XLWorkbook())
{
wb.Worksheets.Add(dt);
using (MemoryStream stream = new MemoryStream())
{
wb.SaveAs(stream);
return File(stream.ToArray(), "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", "ThanhVienBG.xlsx");
}
}
}
public FileResult ExportExcel(ThemTinTucModel obj)
{
string tacvu = obj.Content;
ShopDataContex db = new ShopDataContex();
DataTable dt = new DataTable("Grid");
dt.Columns.AddRange(new DataColumn[15]
{
new DataColumn("ID"),
new DataColumn("ID2"),
new DataColumn("Item title"),
new DataColumn("Final URL"),
new DataColumn("Image URL"),
new DataColumn("Item subtitle"),
new DataColumn("Item description"),
new DataColumn("Item category"),
new DataColumn("Price"),
new DataColumn("Sale price"),
new DataColumn("Contextual keywords"),
new DataColumn("Item address"),
new DataColumn("Tracking template"),
new DataColumn("Custom parameter"),
new DataColumn("Action")
});
var Menus = db.Menu.Where(o => o.idControl == 11 && o.ok && o.HasValue && o.HasOnHand && o.DungSai && o.TenNgan != null && o.MoTaNgan != null).OrderByDescending(o => o.sDateOk).ToList();
foreach (var product in Menus)
{
string ID = "BG" + product.id_;
string ID2 = "";
string Itemtitle = product.TenNgan;
string FinalURL = "https://beautygarden.vn/" + product.Link + ".html";
string ImageURL = "https://beautygarden.vn/Upload/Files/" + product.Img;
string Itemsubtitle = "beautygarden.vn";
string Itemdescription = product.MoTaNgan;
string Itemcategory = "";
string Price = product.PricePro + " VND";
string Saleprice = "";
string Contextualkeywords = "";
string Itemaddress = "";
string Trackingtemplate = "";
string Customparameter = "";
string Action = tacvu;
dt.Rows.Add(ID, ID2, Itemtitle, FinalURL, ImageURL, Itemsubtitle, Itemdescription, Itemcategory, Price, Saleprice, Contextualkeywords,
Itemaddress, Trackingtemplate, Customparameter, Action);
}
using (XLWorkbook wb = new XLWorkbook())
{
wb.Worksheets.Add(dt);
using (MemoryStream stream = new MemoryStream())
{
wb.SaveAs(stream);
return(File(stream.ToArray(), "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", "CSVgoogle.xlsx"));
}
}
}
public static List <Menu> GetproductIdsList()
{
using (var context = new ShopDataContex())
{
var data = context.Database.SqlQuery <Menu>("select * from menu where HasValue=1 and ok=1 and HasOnHand=1").ToList();
return(data);
}
}
public static double GetRatingbyLink(string splink)
{
using (var context = new ShopDataContex())
{
string sql = string.Format("SELECT ISNULL(Round(SUM(Rating)/COUNT(Id), 1),1) FROM UserRatings where IdSanPham = (select id_ from Menu where Link='{0}')", splink);
return(context.Database.SqlQuery <double>(sql).First());
}
}
public static string GetslHinhAnhThuongHieu(string link)
{
using (var context = new ShopDataContex())
{
string sql = string.Format("select MenuAdwords from Menu where Menu.Style='thuong-hieu' and Menu.Link='{0}'", link);
return(context.Database.SqlQuery <string>(sql).First());
}
}
//public bool Bestseller { get; set; } // lay chu bestseller gan vao chi tiet cho a Hoang
public static IList <Menu> GetDsTagsFromDanhMuc(int id)
{
using (var context = new ShopDataContex())
{
string sql = string.Format("select * from Menu where id_ in (select idMenuCatelogy from MenuProAdd where idMenuProAdded={0})", id);
return(context.Database.SqlQuery <Menu>(sql).ToList());
}
}
public static int GetslHientai(int idanhmuc)
{
using (var context = new ShopDataContex())
{
string sql = string.Format("select COUNT(*) from Menu where Menu.idControl={0}", idanhmuc);
return(context.Database.SqlQuery <int>(sql).First());
}
}
public static int GetSlCautraloi(int idcmt)
{
using (var context = new ShopDataContex())
{
string sql = string.Format("select Count(*) from TraLoiComments where CommentId={0}", idcmt);
return(context.Database.SqlQuery <int>(sql).FirstOrDefault());
}
}
public static string GetThuongHieuForFacebook(int idSP)
{
using (var context = new ShopDataContex())
{
var data = context.Database.SqlQuery <string>("select NameProduct from Menu where Menu.id_ = (select IdNhaCungCap from Menu where id_ = {0})", idSP).FirstOrDefault();
return(data);
}
}
public static string GetDanhMucForFacebook(int idSP)
{
using (var context = new ShopDataContex())
{
var data = context.Database.SqlQuery <string>("select NameProduct from Menu where Menu.id_ = (select top 1 idMenuCatelogy from MenuProAdd where idMenuProAdded = {0})", idSP).FirstOrDefault();
return(data);
}
}
public static string GetLinkTheoDanhMuccon3(int idSP)
{
using (var context = new ShopDataContex())
{
var data = context.Database.SqlQuery <string>("select Link from Menu where Menu.id_ ={0}", idSP).FirstOrDefault();
return(data);
}
}
public static string Gettendanhmucconcap3(int iddanhmuc)
{
using (var context = new ShopDataContex())
{
var data = context.Database.SqlQuery <string>("select NameProduct from Menu where Menu.id_ ={0}", iddanhmuc).FirstOrDefault();
return(data);
}
}
public static List <TuKhoaTimKiem> GetListTop10()
{
using (var context = new ShopDataContex())
{
var data = context.Database.SqlQuery <TuKhoaTimKiem>("select top 10 * from TuKhoaTimKiems order by CountRow desc").ToList();
return(data);
}
}
public static string GetDanhMucCap1(int idsanpham)
{
using (var context = new ShopDataContex())
{
var data = context.Database.SqlQuery <string>("select NameProduct from Menu where id_=(select top 1 idControl from Menu where id_ in (select idMenuCatelogy from MenuProAdd where idMenuProAdded={0}))", idsanpham).FirstOrDefault();
return(data);
}
}
public static int GetRatingCountbyLink(string splink)
{
using (var context = new ShopDataContex())
{
string sql = string.Format("SELECT COUNT(*) FROM UserRatings where IdSanPham = (select id_ from Menu where Link='{0}')", splink);
return(context.Database.SqlQuery <int>(sql).First());
}
}
public static int GetslHientai(string link)
{
using (var context = new ShopDataContex())
{
string sql = String.Format("select COUNT(*) from Menu where Menu.IdNhaCungCap=(select id_ from Menu where Menu.Link='{0}') and HasValue=1 and HasOnHand=1 and ok=1", link);
return(context.Database.SqlQuery <int>(sql).First());
}
}
public static List <Menu> GetAllLinkforsitemap()
{
using (var context = new ShopDataContex())
{
var data = context.Database.SqlQuery <Menu>("select * from menu where ok=1 and id_ not in(19,21,23,24,25,27,31,32,59,60,61)").ToList();
return(data);
}
}
public static string GetLinkByIdSanPham(int idsanpham)
{
using (var context = new ShopDataContex())
{
string sql = string.Format("select Link from Menu where id_ = {0}", idsanpham);
return(context.Database.SqlQuery <string>(sql).FirstOrDefault());
}
}
public static List <Menu> GetThuongHieuList()
{
using (var context = new ShopDataContex())
{
var data = context.Database.SqlQuery <Menu>("select * from Menu where Menu.style='thuong-hieu' and idControl<>0 and ok=1").ToList();
return(data);
}
}
public static bool CheckSPBestSeller(int productId)
{
using (var context = new ShopDataContex())
{
string sql = string.Format("select Bestseller from Menu where id_ = {0}", productId);
return(context.Database.SqlQuery <bool>(sql).FirstOrDefault());
}
}
public static List <Menu> GetDanhMucList()
{
using (var context = new ShopDataContex())
{
var data = context.Database.SqlQuery <Menu>("select * from Menu where Menu.style='danh-muc-san-pham' and idControl<>0 and ok=1 and ShowMenuTop=1").ToList();
return(data);
}
}
public static int GetslHientai(int id)
{
using (var context = new ShopDataContex())
{
string sql = string.Format("select COUNT(*) from BannerDanhSachs where IdDanhMuc={0}", id);
return(context.Database.SqlQuery <int>(sql).First());
}
}
public static List <DetailMenu> GetchitietBQLDs()
{
using (var context = new ShopDataContex())
{
var data = context.Database.SqlQuery <DetailMenu>("select * from DetailMenu where DetailMenu.id_menu=20 and ok=1 and ShowMenu=1 and TinhTrangSP=0 and ShowKhuyenMai=1").ToList();
return(data);
}
}
public static string GetBannerByDanhMuc(int id)
{
using (var context = new ShopDataContex())
{
string sql = string.Format("select MenuAdwords from Menu where Menu.Style='thuong-hieu' and Menu.id_={0}", id);
return(context.Database.SqlQuery <string>(sql).FirstOrDefault());
}
}
public static List <Review> GetchitietReviewSPs()
{
using (var context = new ShopDataContex())
{
var data = context.Database.SqlQuery <Review>("select * from Reviews").ToList();
return(data);
}
}
public static List <Redirect301> GetLinkForredirect()
{
using (var context = new ShopDataContex())
{
var data = context.Database.SqlQuery <Redirect301>("select * from redirect301s ").ToList();
return(data);
}
}
public static Menu GetMenuByBarcode(string barcode)
{
using (var context = new ShopDataContex())
{
string sql = string.Format("select * from Menu where id_ = (select IdMenu from MenuOption where Barcode='{0}')", barcode);
return(context.Database.SqlQuery <Menu>(sql).FirstOrDefault());
}
}
