public ActionResult Read([DataSourceRequest] DataSourceRequest request) { using (IDbConnection dbConn = Helpers.OrmliteConnection.openConn()) { var data = new DataSourceResult(); var query = @"SELECT Detail.*, P.nha_cung_cap_id, V.ten_nha_cung_Cap, V.dien_thoai, P.so_hop_dong, V.email, P.ghi_chu FROM ProductPriceHeader P LEFT JOIN Vendor V ON P.nha_cung_cap_id = V.nha_cung_cap_id LEFT JOIN dbo.ProductPriceDetail Detail ON Detail.ma_chinh_sach_gia = P.ma_chinh_sach_gia "; if (accessDetail.xem) { if (request.Filters.Any()) { var where = KendoApplyFilter.ApplyFilter(request.Filters[0]); data = KendoApplyFilter.KendoDataByQuery <ProductPriceReport>(request, query, where); } else { data = KendoApplyFilter.KendoDataByQuery <ProductPriceReport>(request, query, ""); } } return(Json(data, JsonRequestBehavior.AllowGet)); } }
public ActionResult Read([DataSourceRequest] DataSourceRequest request) { using (var dbConn = Helpers.OrmliteConnection.openConn()) { var data = new List <PRequestHeader>(); if (accessDetail.xem) { if (request.Filters.Any()) { var where = KendoApplyFilter.ApplyFilter(request.Filters[0], "a."); data = dbConn.Select <PRequestHeader>(@"Select distinct a.* From PRequestHeader a Inner Join UsersBranch b on b.ma_chi_nhanh = a.ma_chi_nhanh Where b.ma_nguoi_dung = '" + currentUser.ma_nguoi_dung + "' and " + where); } else { data = dbConn.Select <PRequestHeader>(@"Select distinct a.* From PRequestHeader a Inner Join UsersBranch b on b.ma_chi_nhanh = a.ma_chi_nhanh Where b.ma_nguoi_dung = '" + currentUser.ma_nguoi_dung + "'"); } } else { return(RedirectToAction("NoAccessRights", "Error")); } return(Json(data.ToDataSourceResult(request))); } }
public ActionResult Read([DataSourceRequest] DataSourceRequest request) { using (var dbConn = Helpers.OrmliteConnection.openConn()) { var data = new DataSourceResult(); string str = @" SELECT d.id , h.ma_phieu , h.ma_chi_nhanh , h.ten_phieu , h.ngay_tao_yeu_cau , h.y_kien_cua_don_vi , h.ngay_duyet_TDV , h.nguoi_duyet_TDV , h.y_kien_HCQT , h.ngay_duyet_HCQT , h.nguoi_duyet_HCQT , h.y_kien_TTCNTT_NHDT , h.ngay_duyet_TTCNTT_NHDT , h.nguoi_duyet_TTCNTT_NHDT , h.y_kien_QLDVKH_NQT , h.ngay_duyet_QLDVKH_NQT , h.nguoi_duyet_QLDVKH_NQT , h.y_kien_khac_HO , h.ngay_duyet_khac_HO , h.nguoi_duyet_khac_HO , d.ma_san_pham , d.so_luong , d.ma_nha_cung_cap , d.don_gia_vat , d.thong_so_ky_thuat , d.chuc_danh_nguoi_su_dung , d.ke_hoach_nam , d.trang_thai FROM PRequestHeader h INNER JOIN PRequestDetail d ON h.ma_phieu = d.ma_phieu "; if (request.Sorts == null) { request.Sorts = new List <Kendo.Mvc.SortDescriptor>(); } request.Sorts.Add(new Kendo.Mvc.SortDescriptor("ma_phieu", System.ComponentModel.ListSortDirection.Ascending)); if (request.Filters == null) { request.Filters = new List <Kendo.Mvc.IFilterDescriptor>(); } data = KendoApplyFilter.KendoDataByQuery <ReportPR>(request, str, ""); return(Json(data)); } }
public ActionResult Read([DataSourceRequest] DataSourceRequest request, string ma_gian_hang = "") { if (accessDetail != null && (accessDetail.access["all"] || accessDetail.access["view"])) { using (IDbConnection db = OrmliteConnection.openConn(AppConfigs.MCCConnectionString)) { var data = new DataSourceResult(); if (!isAdmin) { data = KendoApplyFilter.KendoData <Merchant_Catalog>(request, "ma_gian_hang={0}".Params(currentUser.ma_gian_hang)); //return Json(db.Select<Merchant_Catalog>(s => s.ma_gian_hang == currentUser.ma_gian_hang).ToDataSourceResult(request), JsonRequestBehavior.AllowGet); } else { if (!string.IsNullOrEmpty(ma_gian_hang.Trim())) { //return Json(db.Select<Merchant_Catalog>(s => s.ma_gian_hang == ma_gian_hang).ToDataSourceResult(request), JsonRequestBehavior.AllowGet); data = KendoApplyFilter.KendoData <Merchant_Catalog>(request, " ma_gian_hang={0}".Params(currentUser.ma_gian_hang)); } else { //return Json(db.Select<Merchant_Catalog>().ToDataSourceResult(request), JsonRequestBehavior.AllowGet); data = KendoApplyFilter.KendoData <Merchant_Catalog>(request); } } return(Json(data, JsonRequestBehavior.AllowGet)); } } else { return(RedirectToAction("NoAccessRights", "Error")); } }
public ActionResult Read([DataSourceRequest] DataSourceRequest request) { if (asset.View) { using (IDbConnection dbConn = OrmliteConnection.openConn()) { string sqlQuery = @"SELECT req.* ,SUBSTRING(c_code,3, 10) AS BookCode ,ISNULL(l.PKBookLocation,0) AS PKBookLocation ,ISNULL(l.FKBookReq,'') AS FKBookReq ,ISNULL(l.Website,'') AS Website ,ISNULL(l.Category,'') AS Category ,ISNULL(l.Location,'') AS Location ,ISNULL(l.Nature,'') AS Nature ,ISNULL(l.NgayLen,'') AS NgayLen ,ISNULL(l.NgayXuong ,'') AS NgayXuong ,ISNULL(l.InputDate,'') AS InputDate ,ISNULL(l.AccountStatus,'') AS AccountStatus ,ISNULL(cus.CustomerName,'') AS CustomerName FROM CRM_Book_Req req INNER JOIN CRM_Book_Location l on l.FKBookReq = req.pk_book_req LEFT JOIN ERPAPD_Customer cus on cus.CustomerCode = req.c_customer_code "; return(Json(KendoApplyFilter.KendoDataByQuery <BookBannerViewModel>(request, sqlQuery, ""))); } } else { return(RedirectToAction("NoAccessRights", "Error")); } }
public ActionResult Read([DataSourceRequest] DataSourceRequest request) { using (IDbConnection dbConn = MCC.Helpers.OrmliteConnection.openConn()) { var strWhere = ""; var listgroup = currentUser.listUserGroup; if (listgroup.Count > 0) { foreach (var item in listgroup) { //GroupAdmin System if (item.id == 1) { strWhere = " 1=1"; break; } else { strWhere = " ma_gian_hang = '" + currentUser.ma_gian_hang + "' "; break; } } } var data = new DataSourceResult(); data = KendoApplyFilter.KendoData <tw_User>(request, strWhere); return(Json(data)); } }
public ActionResult Read([DataSourceRequest] DataSourceRequest request) { using (IDbConnection dbConn = Helpers.OrmliteConnection.openConn()) { var data = new DataSourceResult(); if (asset.View) { string strQuery = @"SELECT oh.* FROM Deca_Order_Header oh LEFT JOIN Deca_Order_CancelTracking ct ON oh.OrderID = ct.OrderID LEFT JOIN Deca_Bank_Transactions bt ON oh.OrderID = bt.OrderID lEFT JOIN Deca_Order_Detail od ON oh.OrderID = od.OrderID LEFT JOIN DC_SKU_Property b On od.ProductID = b.productId And od.PriceID = b.productPriceId Where oh.PaymentStatus = 0 AND (oh.Status = 0 OR oh.Status = 1 OR oh.Status = 2 OR oh.Status = 9) AND ((b.ISACTIVE = 0) OR (b.IsActive = 1 AND od.UnitPrice <> b.sellingprice) OR (od.PriceID not in(Select productPriceId from DC_SKU_Property )) OR (od.PriceID IN (Select Distinct c.PKproductpriceID from DecaInsight..DC_OCM_Product_Price c where c.StockOnHand <= 0))) "; data = KendoApplyFilter.KendoDataByQuery <Deca_Order_Header>(request, strQuery, ""); } return(Json(data)); } }
public ActionResult Read([DataSourceRequest] DataSourceRequest request) { using (var dbConn = Helpers.OrmliteConnection.openConn()) { var data = new DataSourceResult(); string str = @" select b.ten_chi_nhanh ,h.nam_ke_hoach ,isnull(d.so_luong_du_kien, 0) so_luong_du_kien ,isnull(d.ten_san_pham, '') ten_san_pham ,isnull(d.total_tien_du_kien, 0) total_tien_du_kien ,isnull(d.ke_hoach_nam_truoc, 0) ke_hoach_nam_truoc ,isnull(d.thuc_hien_nam_truoc, 0) thuc_hien_nam_truoc ,isnull(d.chech_lech, 0) chech_lech from PlanHeader h left join Branch b on b.ma_chi_nhanh = h.don_vi_phu_trach left join ( select d.nam_ke_hoach ,p.ten_san_pham ,sum(so_luong_du_kien) as so_luong_du_kien ,sum(total_tien_du_kien) as total_tien_du_kien ,sum(ke_hoach_nam_truoc) as ke_hoach_nam_truoc ,sum(thuc_hien_nam_truoc) as thuc_hien_nam_truoc ,sum(chech_lech) as chech_lech from PlanDetail d left join Product p on p.id = d.ma_san_pham group by ten_san_pham, nam_ke_hoach ) d on d.nam_ke_hoach = h.id "; data = KendoApplyFilter.KendoDataByQuery <ReportPlan>(request, str, ""); return(Json(data)); } }
public ActionResult CallHistoryRead([DataSourceRequest] DataSourceRequest request, string CustomerID) { string queryStr = "CustomerID = '" + CustomerID + "'"; return Json(KendoApplyFilter.KendoData<CRM_Telesale_History_Call>(request, queryStr )); //return Json(KendoApplyFilter.KendoData<CRM_Telesale_History_Call>(request, "")); }
public ActionResult PaymentProccessRead([DataSourceRequest] DataSourceRequest request) { string strQuery = @"SELECT pa.* , c.c_customer_code AS c_customer_code, c.c_code AS c_contract_code, cu.CustomerName as c_customer_name, ca.c_ngay_du_kien_thu as c_ngay_du_kien_thu, ca.c_ngay_tt_theo_hd as c_ngay_tt_theo_hd, pp.Note as c_ghi_chu, pp.PaymentForm as c_payment_form, pp.NumberReceipt as c_number_receipt, li.Name AS c_bank_name, DATEDIFF(day,ca.c_ngay_du_kien_thu,pa.c_ngay_thanh_toan) AS c_so_ngay_qua_han, (CASE WHEN DATEDIFF(day,GETDATE(),ca.c_ngay_du_kien_thu) > 0 THEN 2 ELSE (CASE WHEN DATEDIFF(day,DateAdd(DD,-5,GETDATE()),ca.c_ngay_du_kien_thu) < 0 THEN 4 WHEN DATEDIFF(day,DateAdd(DD,-5,GETDATE()),ca.c_ngay_du_kien_thu) >= 0 THEN 3 END) END) as c_trang_thai_no FROM CRM_Payment pa LEFT JOIN CRM_PaymentProgress pp ON pp.PKPayment = pa.c_ma_thanh_toan LEFT JOIN CRM_GET_MONEY_MONTH_NEXT ca ON ca.pk_gmoney_next = pa.c_ma_lich_thanh_toan LEFT JOIN CRM_Contract c ON c.pk_contract = pa.c_ma_hop_dong LEFT JOIN ERPAPD_Customer cu ON cu.CustomerCode = c.c_customer_code LEFT JOIN ERPAPD_List li ON li.Code = pp.BankCode"; var data = KendoApplyFilter.KendoDataByQuery <CRM_Payment>(request, strQuery, ""); return(Json(data)); }
public ActionResult EmulationRead([DataSourceRequest] DataSourceRequest request, string Type) { string value = Type == "Emulation" ? "Type='" + Type + "'" : "Type<>'Emulation'"; var data = KendoApplyFilter.KendoData <CRM_CS_ManageInfo>(request, value + " AND IsActive=1 AND ExpiredDate > GETDATE()"); return(Json(data)); }
public ActionResult Read([DataSourceRequest] DataSourceRequest request) { using (IDbConnection dbConn = OrmliteConnection.openConn()) { var data = new DataSourceResult(); if (asset.View) { string strQuery = @"SELECT cus.* ,ISNULL(ps1.Value,'N/A') AS StatusName ,ISNULL(source.Value,'N/A') AS SourceName ,ISNULL(ps2.Value,'N/A') AS TypeName ,ISNULL(emp.Name,'N/A') AS EmployeeName ,ISNULL(team.TeamName,'N/A') AS TeamName ,ISNULL(unit.Value,'N/A') AS RegionName FROM ERPAPD_Customer cus LEFT JOIN [Parameters] ps1 ON ps1.ParamID = cus.Status AND ps1.Type = 'CustomerStatus' LEFT JOIN [Parameters] source ON source.ParamID = cus.Source AND source.Type = 'CustomerSource' LEFT JOIN [ERPAPD_MasterData_Customer] ps2 ON ps2.Code = cus.CustomerType AND ps2.Type = 'CustomerType' LEFT JOIN [ERPAPD_Employee] emp ON cus.StaffId = emp.RefEmployeeID LEFT JOIN [CRM_Team] team ON team.TeamID = cus.GroupId LEFT JOIN [CRM_Hierarchy] unit ON unit.HierarchyID = cus.UnitId "; data = KendoApplyFilter.KendoDataByQuery <ERPAPD_Customer>(request, strQuery, ""); return(Json(data)); } return(RedirectToAction("NoAccessRights", "Error")); } }
public ActionResult Read([DataSourceRequest] DataSourceRequest request) { using (IDbConnection dbConn = OrmliteConnection.openConn()) { var data = new DataSourceResult(); if (asset.View) { string strQuery = @"SELECT product.* ,ISNULL(ProductType.Name,'') AS ProductTypeName ,ISNULL(pageType.Name,'') AS PageTypeName ,ISNULL(webSite.Name,'') AS WebsiteName ,ISNULL(cate.Name,'') AS CategoryName ,ISNULL(location.Name,'') AS LocationName ,ISNULL(nature.Name,'') AS NatureName FROM [ERPAPD_Product] product LEFT JOIN ( SELECT a.Code,a.Name FROM [ERPAPDDev].[dbo].[ERPAPD_List] a INNER JOIN [ERPAPDDev].[dbo].[ERPAPD_ListType] b on b.PKListType = a.FKListtype AND b.PKListType = 19 ) productType On productType.Code = product.ProductType LEFT JOIN ( SELECT a.Code,a.Name FROM [ERPAPDDev].[dbo].[ERPAPD_List] a INNER JOIN [ERPAPDDev].[dbo].[ERPAPD_ListType] b on b.PKListType = a.FKListtype AND b.PKListType = 20 ) webSite On webSite.Code = product.Website LEFT JOIN ( SELECT a.Code,a.Name FROM [ERPAPDDev].[dbo].[ERPAPD_List] a INNER JOIN [ERPAPDDev].[dbo].[ERPAPD_ListType] b on b.PKListType = a.FKListtype AND b.PKListType = 21 ) pageType On pageType.Code = product.PageType LEFT JOIN ( SELECT a.Code,a.Name FROM [ERPAPDDev].[dbo].[ERPAPD_List] a INNER JOIN [ERPAPDDev].[dbo].[ERPAPD_ListType] b on b.PKListType = a.FKListtype AND b.PKListType = 22 ) cate On cate.Code = product.Category LEFT JOIN ( SELECT a.Code,a.Name FROM [ERPAPDDev].[dbo].[ERPAPD_List] a INNER JOIN [ERPAPDDev].[dbo].[ERPAPD_ListType] b on b.PKListType = a.FKListtype AND b.PKListType = 23 ) location On location.Code = product.Location LEFT JOIN ( SELECT a.Code,a.Name FROM [ERPAPDDev].[dbo].[ERPAPD_List] a INNER JOIN [ERPAPDDev].[dbo].[ERPAPD_ListType] b on b.PKListType = a.FKListtype AND b.PKListType = 25 ) nature On nature.Code = product.Nature "; data = KendoApplyFilter.KendoDataByQuery <ERPAPD_Product>(request, strQuery, ""); return(Json(data)); } return(RedirectToAction("NoAccessRights", "Error")); } }
public ActionResult ExportData([DataSourceRequest] DataSourceRequest request) { using (var dbConn = Helpers.OrmliteConnection.openConn()) { //using (ExcelPackage excelPkg = new ExcelPackage()) FileInfo fileInfo = new FileInfo(Server.MapPath(@"~\ExportExcelFile\NCC.xlsx")); var excelPkg = new ExcelPackage(fileInfo); string fileName = "NCC_" + DateTime.Now.ToString("yyyyMMdd_HHmmss") + ".xlsx"; string contentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"; var data = new List <Vendor>(); if (accessDetail.xuat) { if (request.Filters.Any()) { var where = KendoApplyFilter.ApplyFilter(request.Filters[0], ""); data = dbConn.Select <Vendor>(where).ToList(); } else { data = dbConn.Select <Vendor>().ToList(); } } ExcelWorksheet Sheet = excelPkg.Workbook.Worksheets["data"]; int rowData = 7; int stt = 0; foreach (var item in data) { var contact = dbConn.SingleOrDefault <ContactInfor>("nha_cung_cap_id={0} and nguoi_lien_he_chinh = 1", item.nha_cung_cap_id); int i = 1; stt++; rowData++; Sheet.Cells[rowData, i++].Value = stt; Sheet.Cells[rowData, i++].Value = item.ten_nha_cung_cap; Sheet.Cells[rowData, i++].Value = item.ten_thuong_goi; Sheet.Cells[rowData, i++].Value = item.ma_so_thue; Sheet.Cells[rowData, i++].Value = item.dia_chi; Sheet.Cells[rowData, i++].Value = item.von_dieu_le; Sheet.Cells[rowData, i++].Value = item.quy_mo; Sheet.Cells[rowData, i++].Value = item.pham_vi_cung_ung; //Mat hang kinh doanh Sheet.Cells[rowData, i++].Value = ""; Sheet.Cells[rowData, i++].Value = item.thoi_gian_cung_ung; Sheet.Cells[rowData, i++].Value = item.khach_hang_cung_cap; Sheet.Cells[rowData, i++].Value = item.nha_cung_cap_cua_hdbank; Sheet.Cells[rowData, i++].Value = contact != null ? contact.ten_nguoi_lien_he : ""; Sheet.Cells[rowData, i++].Value = contact != null ? contact.so_dien_thoai : ""; Sheet.Cells[rowData, i++].Value = contact != null ? contact.email : ""; Sheet.Cells[rowData, i++].Value = item.file_dinh_kem; Sheet.Cells[rowData, i++].Value = item.ghi_chu; } MemoryStream output = new MemoryStream(); excelPkg.SaveAs(output); output.Position = 0; return(File(output.ToArray(), contentType, fileName)); } }
public ActionResult Read([DataSourceRequest] DataSourceRequest request) { using (IDbConnection dbConn = OrmliteConnection.openConn()) { var data = KendoApplyFilter.KendoData <cms_BoxTin>(request); return(Json(data)); } }
public ActionResult ReadLocation([DataSourceRequest] DataSourceRequest request, string ma_kho) { using (IDbConnection dbConn = Helpers.OrmliteConnection.openConn()) { var data = new DataSourceResult(); data = KendoApplyFilter.KendoData <Merchant_LocationWarehouse>(request, "ma_gian_hang={0} and ma_kho={1}".Params(currentUser.ma_gian_hang, ma_kho)); return(Json(data)); } }
public ActionResult Read([DataSourceRequest] DataSourceRequest request) { using (IDbConnection dbConn = Helpers.OrmliteConnection.openConn()) { var data = new DataSourceResult(); data = KendoApplyFilter.KendoData <Core.Entities.Menu>(request, "id_cha = -1"); return(Json(data)); } }
public ActionResult Read([DataSourceRequest] DataSourceRequest request) { using (IDbConnection dbConn = Helpers.OrmliteConnection.openConn()) { var data = new DataSourceResult(); data = KendoApplyFilter.KendoData <UserGroup>(request); return(Json(data)); } }
public ActionResult ReadProductPrice([DataSourceRequest] DataSourceRequest request, string nha_cung_cap_id) { using (var dbConn = Helpers.OrmliteConnection.openConn()) { var data = new DataSourceResult(); data = KendoApplyFilter.KendoData <ProductPriceHeader>(request, "nha_cung_cap_id={0}".Params(nha_cung_cap_id)); return(Json(data)); } }
public ActionResult SMS_Template_Read([DataSourceRequest] DataSourceRequest request) { using (var dbConn = OrmliteConnection.openConn()) { var list = KendoApplyFilter.KendoData <Deca_SMS_Template>(request); //var listGroup = UserGroup.GetAllUserGroups(); return(Json(list, JsonRequestBehavior.AllowGet)); } }
public ActionResult Read([DataSourceRequest] DataSourceRequest request) { using (IDbConnection dbConn = MCC.Helpers.OrmliteConnection.openConn(AppConfigs.MCCConnectionString)) { var data = new DataSourceResult(); data = KendoApplyFilter.KendoData <BIBIAM.Core.Entities.Merchant_Product_Price>(request); return(Json(data)); } }
public ActionResult HistoryRemindRead([DataSourceRequest] DataSourceRequest request, string CustomerCode, string Type) { string strQuery = @"SELECT d.*, (select Top 1 FullName from EmployeeInfo where RefStaffId = d.StaffID) AS StaffName FROM CRM_Debt_History d WHERE d.CustomerCode = '" + CustomerCode + "' AND d.Type = '" + Type + "'"; var data = KendoApplyFilter.KendoDataByQuery <CRM_Debt_History>(request, strQuery, ""); return(Json(data)); }
public ActionResult ReadDetail([DataSourceRequest] DataSourceRequest request, int order_id_header) { using (var dbConn = Helpers.OrmliteConnection.openConn()) { var data = new DataSourceResult(); data = KendoApplyFilter.KendoData <OrderDetail>(request, "order_id_header={0}".Params(order_id_header)); return(Json(data)); } }
public ActionResult Read([DataSourceRequest] DataSourceRequest request) { using (IDbConnection dbConn = Helpers.OrmliteConnection.openConn()) { var data = new DataSourceResult(); string sqlquery = @"select a.*,b.ten_website, c.hashtagname from cms_PostNotify a left join cms_Websites b on a.ma_website=b.ma_website left join Hashtag c on a.hashtagcode=c.hashtagcode"; data = KendoApplyFilter.KendoDataByQuery <cms_PostNotify>(request, sqlquery, ""); return(Json(data)); } }
public ActionResult Read([DataSourceRequest] DataSourceRequest request) { using (IDbConnection dbConn = Helpers.OrmliteConnection.openConn()) { var data = new DataSourceResult(); string sqlquery = @"select a.*,b.ten_website,c.ten_chuyen_muc,d.ten_vi_tri,e.ngay_gio_xuat_ban,e.ghi_chu from cms_News a left join cms_Websites b on a.ma_website=b.ma_website left join cms_Categorys c on a.ma_chuyen_muc=c.ma_chuyen_muc left join cms_Positions d on a.ma_vi_tri=d.ma_vi_tri left join cms_Schedule_News e on a.ma_bai_viet=e.ma_bai_viet"; data = KendoApplyFilter.KendoDataByQuery <cms_News>(request, sqlquery, ""); return(Json(data, JsonRequestBehavior.AllowGet)); } }
//CanhLV public ActionResult Read([DataSourceRequest] DataSourceRequest request) { using (var dbConn = Helpers.OrmliteConnection.openConn()) { var data = new List <User>(); if (accessDetail.xem) { if (request.Filters.Any()) { var where = KendoApplyFilter.ApplyFilter(request.Filters[0]); data = dbConn.Select <User>(@" Select * From ( Select * ,ISNULL(STUFF (( Select DISTINCT ',' + a.ma_chi_nhanh From UsersBranch a Inner Join Branch b ON a.ma_chi_nhanh = b.ma_chi_nhanh Where a.ma_nguoi_dung = usr.ma_nguoi_dung For XML Path('') ), 1, 1, ''),'') AS ma_chi_nhanh ,ISNULL(STUFF (( Select DISTINCT ',' + b.ten_chi_nhanh From UsersBranch a Inner Join Branch b ON a.ma_chi_nhanh = b.ma_chi_nhanh Where a.ma_nguoi_dung = usr.ma_nguoi_dung For XML Path('') ), 1, 1, ''),'') AS ten_chi_nhanh From [User] usr ) data Where " + where ); } else { data = dbConn.Select <User>(@"Select * ,ISNULL(STUFF (( Select DISTINCT ',' + a.ma_chi_nhanh From UsersBranch a Inner Join Branch b ON a.ma_chi_nhanh = b.ma_chi_nhanh Where a.ma_nguoi_dung = usr.ma_nguoi_dung For XML Path('') ), 1, 1, ''),'') AS ma_chi_nhanh ,ISNULL(STUFF (( Select DISTINCT ',' + b.ten_chi_nhanh From UsersBranch a Inner Join Branch b ON a.ma_chi_nhanh = b.ma_chi_nhanh Where a.ma_nguoi_dung = usr.ma_nguoi_dung For XML Path('') ), 1, 1, ''),'') AS ten_chi_nhanh From [User] usr" ); } } return(Json(data.ToDataSourceResult(request))); } }
public ActionResult ExportData([DataSourceRequest] DataSourceRequest request) { if (asset.Export) { using (var dbConn = Helpers.OrmliteConnection.openConn()) { //using (ExcelPackage excelPkg = new ExcelPackage()) FileInfo fileInfo = new FileInfo(Server.MapPath(@"~\ExportExcelFile\Deca_Telesales_Agent_KPI.xlsx")); var excelPkg = new ExcelPackage(fileInfo); string fileName = "Deca_Telesales_Agent_KPI" + DateTime.Now.ToString("yyyyMMdd_HHmmss") + ".xlsx"; string contentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"; var data = new List <Deca_Telesales_Agent_KPI>(); if (request.Filters.Any()) { var where = KendoApplyFilter.ApplyFilter(request.Filters[0], ""); data = dbConn.Select <Deca_Telesales_Agent_KPI>(where).ToList(); } else { data = dbConn.Select <Deca_Telesales_Agent_KPI>().ToList(); } ExcelWorksheet dataSheet = excelPkg.Workbook.Worksheets["TelesalesAgentKPI"]; int rowData = 1; foreach (var item in data) { int i = 1; rowData++; dataSheet.Cells[rowData, i++].Value = item.Agent; dataSheet.Cells[rowData, i++].Value = item.Revenue; dataSheet.Cells[rowData, i++].Value = item.Order; dataSheet.Cells[rowData, i++].Value = item.Call; dataSheet.Cells[rowData, i++].Value = item.TalkTime; dataSheet.Cells[rowData, i++].Value = item.CreatedAt; dataSheet.Cells[rowData, i++].Value = item.CreatedBy; dataSheet.Cells[rowData, i++].Value = item.UpdatedAt; dataSheet.Cells[rowData, i++].Value = item.UpdatedBy; } MemoryStream output = new MemoryStream(); excelPkg.SaveAs(output); output.Position = 0; return(File(output.ToArray(), contentType, fileName)); } } else { return(Json(new { success = false })); } }
public ActionResult ExportData([DataSourceRequest] DataSourceRequest request) { if (asset.Export) { using (var dbConn = Helpers.OrmliteConnection.openConn()) { //using (ExcelPackage excelPkg = new ExcelPackage()) FileInfo fileInfo = new FileInfo(Server.MapPath(@"~\ExportExcelFile\Deca_CallHistory.xlsx")); var excelPkg = new ExcelPackage(fileInfo); string fileName = "Deca_CallHistory_" + DateTime.Now.ToString("yyyyMMdd_HHmmss") + ".xlsx"; string contentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"; string query = ""; request.PageSize = 50000; var data = KendoApplyFilter.KendoData <DC_Telesales_CallHistory>(request); ExcelWorksheet expenseSheet = excelPkg.Workbook.Worksheets["CallHistory"]; var dataResult = dbConn.Select <DC_Telesales_ResultList>(); int rowData = 1; foreach (DC_Telesales_CallHistory item in data.Data) { int i = 1; rowData++; expenseSheet.Cells[rowData, i++].Value = item.CustomerID; expenseSheet.Cells[rowData, i++].Value = item.CustomerName; expenseSheet.Cells[rowData, i++].Value = item.Phone; expenseSheet.Cells[rowData, i++].Value = item.PhysicalID; expenseSheet.Cells[rowData, i++].Value = item.CustRule; expenseSheet.Cells[rowData, i++].Value = dataResult.FirstOrDefault(a => a.Id == item.ResultID) == null ? "" : dataResult.FirstOrDefault(a => a.Id == item.ResultID).Description; expenseSheet.Cells[rowData, i++].Value = dataResult.FirstOrDefault(a => a.Id == item.SubResultID) == null ? "" : dataResult.FirstOrDefault(a => a.Id == item.SubResultID).Description; expenseSheet.Cells[rowData, i++].Value = item.Content; expenseSheet.Cells[rowData, i++].Value = item.RecallTime; expenseSheet.Cells[rowData, i++].Value = item.isDone.ToString(); expenseSheet.Cells[rowData, i++].Value = item.Source; expenseSheet.Cells[rowData, i++].Value = item.OrderID; expenseSheet.Cells[rowData, i++].Value = item.RefType; expenseSheet.Cells[rowData, i++].Value = item.RefID; expenseSheet.Cells[rowData, i++].Value = item.CreatedAt; expenseSheet.Cells[rowData, i++].Value = item.CreatedBy; expenseSheet.Cells[rowData, i++].Value = item.UpdatedAt; expenseSheet.Cells[rowData, i++].Value = item.UpdatedBy; } MemoryStream output = new MemoryStream(); excelPkg.SaveAs(output); output.Position = 0; return(File(output.ToArray(), contentType, fileName)); } } else { return(Json(new { success = false })); } }
public ActionResult Read([DataSourceRequest] DataSourceRequest request) { using (IDbConnection dbConn = Helpers.OrmliteConnection.openConn()) { var data = new DataSourceResult(); if (asset.View) { data = KendoApplyFilter.KendoData <Deca_SMSMO>(request); } return(Json(data)); } }
public ActionResult Read_DeliveryFeeTerritory([DataSourceRequest] DataSourceRequest request) { string whereCondition = ""; if (request.Filters.Count > 0) { whereCondition = new KendoApplyFilter().ApplyFilter(request.Filters[0]); } var data = new DC_LG_DeliveryFee_Territory().GetPage(request.Page, request.PageSize, whereCondition); return(Json(data)); }
public ActionResult Read_DeliveryFeeTerritory([DataSourceRequest]DataSourceRequest request) { string whereCondition = ""; if (request.Filters.Count > 0) { whereCondition = new KendoApplyFilter().ApplyFilter(request.Filters[0]); } var data = new DC_LG_DeliveryFee_Territory().GetPage(request.Page, request.PageSize, whereCondition); return Json(data); }
public ActionResult Read([DataSourceRequest]DataSourceRequest request) { var dbConn = new OrmliteConnection().openConn(); log4net.Config.XmlConfigurator.Configure(); string whereCondition = ""; if (request.Filters.Count > 0) { whereCondition = new KendoApplyFilter().ApplyFilter(request.Filters[0]); } var data = dbConn.Select<Products>(whereCondition).ToList(); return Json(data.ToDataSourceResult(request)); }
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 FileResult Export([DataSourceRequest]DataSourceRequest request) { ExcelPackage pck = new ExcelPackage(new FileInfo(Server.MapPath("~/ExportTemplate/DeliveryUOM.xlsx"))); ExcelWorksheet ws = pck.Workbook.Worksheets["Data"]; if (userAsset["Export"]) { string whereCondition = ""; if (request.Filters.Count > 0) { whereCondition = new KendoApplyFilter().ApplyFilter(request.Filters[0]); } IDbConnection db = new OrmliteConnection().openConn(); var lstResult = db.Select<DC_LG_Transporter>(whereCondition); int rowNum = 2; foreach (var item in lstResult) { ws.Cells["A" + rowNum].Value = item.TransporterID; ws.Cells["B" + rowNum].Value = item.TransporterName; ws.Cells["C" + rowNum].Value = item.Weight; ws.Cells["D" + rowNum].Value = item.Note; ws.Cells["E" + rowNum].Value = item.CreatedBy; ws.Cells["F" + rowNum].Value = Convert.ToDateTime(item.CreatedAt).ToString("dd/MM/yyyy"); ws.Cells["G" + rowNum].Value = item.Status ? "Đang hoạt động" : "Ngưng hoạt động"; rowNum++; } db.Close(); } else { ws.Cells["A2:E2"].Merge = true; ws.Cells["A2"].Value = "You don't have permission to export data."; } MemoryStream output = new MemoryStream(); pck.SaveAs(output); return File(output.ToArray(), //The binary data of the XLS file "application/vnd.ms-excel", //MIME type of Excel files "DanhSachDonViVanChuyen" + DateTime.Now.ToString("yyyyMMdd_HHmmss") + ".xlsx"); //Suggested file name in the "Save as" dialog which will be displayed to the end user }
// // GET: /DeliveryManage/Details/5 public ActionResult Read([DataSourceRequest]DataSourceRequest request) { var dbConn = new OrmliteConnection().openConn(); string whereCondition = ""; if (request.Filters.Count > 0) { whereCondition = new KendoApplyFilter().ApplyFilter(request.Filters[0]); } var data = dbConn.Select<DC_LG_Transporter>(whereCondition).OrderBy(p=>p.Weight); return Json(data.ToDataSourceResult(request)); }
public ActionResult ReadHeader([DataSourceRequest] DataSourceRequest request) { var dbConn = new OrmliteConnection().openConn(); var data = new List<SOHeader>(); if(request.Filters.Any()) { var where = new KendoApplyFilter().ApplyFilter(request.Filters[0]); data = dbConn.Select<SOHeader>(where); } else{ data = dbConn.Select<SOHeader>(); } return Json(data.ToDataSourceResult(request)); }
public ActionResult Read([DataSourceRequest]DataSourceRequest request) { var dbConn = new OrmliteConnection().openConn(); string whereCondition = ""; if (request.Filters.Count > 0) { whereCondition = new KendoApplyFilter().ApplyFilter(request.Filters[0]); } var data = dbConn.Select<DC_RuleCheck>(whereCondition); return Json(data.ToDataSourceResult(request)); }
public ActionResult ExportWH([DataSourceRequest]DataSourceRequest request) { if (userAsset["Export"]) { using (var dbConn = new OrmliteConnection().openConn()) { //using (ExcelPackage excelPkg = new ExcelPackage()) FileInfo fileInfo = new FileInfo(Server.MapPath(@"~\ExportTemplate\ThongTinKho.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<WareHouse>(); if (request.Filters.Any()) { var where = new KendoApplyFilter().ApplyFilter(request.Filters[0]); data = dbConn.Select<WareHouse>(where); } else { data = dbConn.Select<WareHouse>(); } ExcelWorksheet expenseSheet = excelPkg.Workbook.Worksheets["Data"]; int rowData = 1; foreach (var item in data) { int i = 1; rowData++; expenseSheet.Cells[rowData, i++].Value = item.WHID; expenseSheet.Cells[rowData, i++].Value = item.WHName; expenseSheet.Cells[rowData, i++].Value = item.Address; expenseSheet.Cells[rowData, i++].Value = item.WHKeeper; expenseSheet.Cells[rowData, i++].Value = item.Note; 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; } MemoryStream output = new MemoryStream(); excelPkg.SaveAs(output); output.Position = 0; return File(output.ToArray(), contentType, fileName); } } else { return Json(new { success = false }); } }