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