public ActionResult GetItem(string text) { using (var dbConn = new OrmliteConnection().openConn()) { var data = new List <Products>(); if (text.Length >= 3) { data = dbConn.Query <Products>("SELECT TOP 5 Name, Code, size FROM DC_AD_Items WHERE Name COLLATE Latin1_General_CI_AI LIKE N'%" + text + "%'"); } return(Json(data, JsonRequestBehavior.AllowGet)); } }
public ActionResult GetMerchant(string text) { using (var dbConn = new OrmliteConnection().openConn()) { var data = new List <DC_OCM_Merchant>(); if (text.Length >= 3) { data = dbConn.Query <DC_OCM_Merchant>("SELECT TOP 5 * FROM DC_OCM_Merchant WHERE MerchantName COLLATE Latin1_General_CI_AI LIKE N'%" + text + "%'"); } return(Json(data, JsonRequestBehavior.AllowGet)); } }
public ActionResult Export([DataSourceRequest] DataSourceRequest request) { if (userAsset["Export"]) { using (var dbConn = new OrmliteConnection().openConn()) { //using (ExcelPackage excelPkg = new ExcelPackage()) FileInfo fileInfo = new FileInfo(Server.MapPath(@"~\ExportTemplate\DanhMucAnPham.xlsx")); var excelPkg = new ExcelPackage(fileInfo); string fileName = "ThongTinKho_" + DateTime.Now.ToString("yyyyMMdd_HHmmss") + ".xlsx"; string contentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"; var data = new List <Products>(); if (request.Filters.Any()) { var where = new KendoApplyFilter().ApplyFilter(request.Filters[0], "data."); //data = dbConn.Select<Products>(where); data = dbConn.Query <Products>("p_SelectDC_AD_Item_Export", new { WhereCondition = where }, commandType: System.Data.CommandType.StoredProcedure).ToList(); } else { //data = dbConn.Select<Products>(); data = dbConn.Query <Products>("p_SelectDC_AD_Item_Export", new { WhereCondition = "1=1" }, commandType: System.Data.CommandType.StoredProcedure).ToList(); } ExcelWorksheet expenseSheet = excelPkg.Workbook.Worksheets["Data"]; int rowData = 1; foreach (var item in data) { int i = 1; rowData++; expenseSheet.Cells[rowData, i++].Value = item.Code; expenseSheet.Cells[rowData, i++].Value = item.Name; expenseSheet.Cells[rowData, i++].Value = item.Size; expenseSheet.Cells[rowData, i++].Value = item.VATPrice; expenseSheet.Cells[rowData, i++].Value = item.Type; expenseSheet.Cells[rowData, i++].Value = item.UnitName + "/" + item.UnitID; expenseSheet.Cells[rowData, i++].Value = item.WHName + "/" + item.WHID; expenseSheet.Cells[rowData, i++].Value = item.WHLName + "/" + item.WHLID; expenseSheet.Cells[rowData, i++].Value = item.ShapeTemplate; if (item.Status == true) { expenseSheet.Cells[rowData, i++].Value = "Đang hoạt động"; } else { expenseSheet.Cells[rowData, i++].Value = "Ngưng hoạt động"; } expenseSheet.Cells[rowData, i++].Value = item.CreatedBy; expenseSheet.Cells[rowData, i++].Value = item.CreatedAt; expenseSheet.Cells[rowData, i++].Value = item.UpdatedBy; if (item.UpdatedAt != DateTime.Parse("1900-01-01")) { expenseSheet.Cells[rowData, i++].Value = item.UpdatedAt; } else { expenseSheet.Cells[rowData, i++].Value = ""; } //expenseSheet.Cells[rowData, i++].Value = item.RowLastUpdatedTime; } expenseSheet = excelPkg.Workbook.Worksheets["Warehouse"]; var listWH = dbConn.Select <WareHouse>("SELECT * FROM WareHouse WHERE Status = 1"); rowData = 1; foreach (var item in listWH) { int i = 1; rowData++; expenseSheet.Cells[rowData, i++].Value = item.WHName + "/" + item.WHID; } expenseSheet = excelPkg.Workbook.Worksheets["Location"]; var listWHL = dbConn.Select <WareHouseLocation>("SELECT * FROM WareHouseLocation WHERE Status = 1"); rowData = 1; foreach (var item in listWHL) { int i = 1; rowData++; expenseSheet.Cells[rowData, i++].Value = item.WHLName + "/" + item.WHLID; } expenseSheet = excelPkg.Workbook.Worksheets["Unit"]; var listUnit = dbConn.Select <DC_AD_Unit>("SELECT * FROM DC_AD_Unit WHERE Status = 1"); rowData = 1; foreach (var item in listUnit) { int i = 1; rowData++; expenseSheet.Cells[rowData, i++].Value = item.UnitName + "/" + item.UnitID; } MemoryStream output = new MemoryStream(); excelPkg.SaveAs(output); output.Position = 0; return(File(output.ToArray(), contentType, fileName)); } } else { return(Json(new { success = false })); } }