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 WareHouseL 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 });
}
}
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);
}
}