public ActionResult ExportListStatical(string text, string strDateUp, string strDateDown) { if (asset.Export) { using (var dbConn = OrmliteConnection.openConn()) { var customer = dbConn.Select <ERPAPD_Customer>(@" SELECT CustomerCode FROM ERPAPD_Customer WHERE CustomerName COLLATE Latin1_General_CI_AI LIKE N'%" + text + "%'" ).FirstOrDefault(); var dateUp = DateTime.Parse(strDateUp); var dateDown = DateTime.Parse(strDateDown); FileInfo fileInfo = new FileInfo(Server.MapPath(@"~\ExportExcelFile\temp_bangke.xlsx")); var excelPkg = new ExcelPackage(fileInfo); string fileName = "BANGKE_" + DateTime.Now.ToString("yyyyMMdd_HHmmss") + ".xlsx"; string contentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"; int rowData = 6; ExcelWorksheet expenseSheet = excelPkg.Workbook.Worksheets["Report"]; var list = new CRM_Contract().Export_BangKe(customer.CustomerCode, dateUp, dateDown); foreach (var item in list) { int i = 1; expenseSheet.Cells[rowData, i++].Value = rowData; // STT expenseSheet.Cells[rowData, i++].Value = item.c_code; expenseSheet.Cells[rowData, i++].Value = item.c_website_name + "/" + item.c_category_name; expenseSheet.Cells[rowData, i++].Value = item.c_customer_name; expenseSheet.Cells[rowData, i++].Value = ""; expenseSheet.Cells[rowData, i++].Value = ""; expenseSheet.Cells[rowData, i++].Value = item.c_don_gia_public; expenseSheet.Cells[rowData, i++].Value = item.c_don_gia_tt * item.c_number; expenseSheet.Cells[rowData, i++].Value = item.c_don_gia_theo_ngay; expenseSheet.Cells[rowData, i++].Value = item.c_ngay_bu; rowData++; } expenseSheet.Cells.AutoFitColumns(); MemoryStream output = new MemoryStream(); excelPkg.SaveAs(output); output.Position = 0; return(File(output.ToArray(), contentType, fileName)); } } else { return(Json(new { success = false, message = "Không có quyền cập nhật" })); } }
public ActionResult Read([DataSourceRequest] DataSourceRequest request) { using (IDbConnection dbConn = OrmliteConnection.openConn()) { if (asset.View) { //string whereCondition = ""; //if (request.Filters.Count > 0) //{ // whereCondition = " AND " + KendoApplyFilter.ApplyFilter(request.Filters[0], ""); //} //var data = new CRM_Contract().GetPage(request, whereCondition); //var jsonResult = Json(data, JsonRequestBehavior.AllowGet); //jsonResult.MaxJsonLength = int.MaxValue; //return jsonResult; string where = ""; if (request.Filters.Count > 0) { where = " and " + KendoApplyFilter.ApplyFilter(request.Filters[0], ""); } //Debug.Write(DateTime.Now); var data = CRM_Contract.GetPage(request.Page, request.PageSize, where); //Debug.Write(DateTime.Now); request.Page = 1; request.Filters = null; var result = data.ToDataSourceResult(request); if (data.Rows.Count > 0) { result.Total = Convert.ToInt32(data.Rows[0]["RowCount"]); } var jsonResult = Json(result); jsonResult.MaxJsonLength = int.MaxValue; return(jsonResult); } return(RedirectToAction("NoAccessRights", "Error")); } }
//===============================================Import export================================================== public FileResult ExportContract([DataSourceRequest] DataSourceRequest request) { ExcelPackage pck = new ExcelPackage(new FileInfo(Server.MapPath("~/ExportExcelFile/24HCRM_CONTRACT.xlsx"))); ExcelWorksheet ws = pck.Workbook.Worksheets["Data"]; if (asset.Export) { using (var dbConn = OrmliteConnection.openConn()) { string whereCondition = ""; if (request.Filters.Count > 0) { whereCondition = " AND " + KendoApplyFilter.ApplyFilter(request.Filters[0], ""); } var data = CRM_Contract.Export(request.Page, request.PageSize, whereCondition); int rowData = 3; foreach (DataRow row in data.Rows) { int i = 1; rowData++; ws.Cells[rowData, i++].Value = row["c_week"].ToString(); // Tuần ws.Cells[rowData, i++].Value = row["c_month"].ToString(); // Tháng ws.Cells[rowData, i++].Value = row["c_contract_code"].ToString(); // Số HĐ ws.Cells[rowData, i++].Value = row["c_customer_name"].ToString(); // Tên KH ws.Cells[rowData, i++].Value = row["c_service_type"].ToString(); // Loại Dịch Vụ ws.Cells[rowData, i++].Value = row["c_labels"].ToString(); // Nhãn ws.Cells[rowData, i++].Value = row["c_teamname"].ToString(); // Nhóm ws.Cells[rowData, i++].Value = row["c_staff_name"].ToString(); // NVKD ws.Cells[rowData, i++].Value = !row.IsNull("c_dt_da_qc_den_hom_nay") ? Convert.ToDouble(row["c_dt_da_qc_den_hom_nay"]) : 0; // DS đã QC đến ngày hôm nay ws.Cells[rowData, i++].Value = !row.IsNull("c_dt_da_xuat_ban") ? Convert.ToDouble(row["c_dt_da_xuat_ban"]) : 0; // DS Đã xuất bản ws.Cells[rowData, i++].Value = !row.IsNull("c_payment_money") ? Convert.ToDouble(row["c_payment_money"]) : 0; // DS Đã Thu ws.Cells[rowData, i++].Value = !row.IsNull("c_balance") ? Convert.ToDouble(row["c_balance"]) : 0; // Còn nợ ws.Cells[rowData, i++].Value = !row.IsNull("dang_quang_cao") ? row["dang_quang_cao"].ToString() : ""; // Đăng QC //ws.Cells[rowData, i++].Value = row["c_payment_date"].ToString() : 0; // Hạn TT //ws.Cells[rowData, i++].Value = row["c_note"].ToString() : 0; // Ghi chú ws.Cells[rowData, i++].Value = !row.IsNull("c_total_value") ? Convert.ToDouble(row["c_total_value"].ToString()) : 0; // Phải thu ws.Cells[rowData, i++].Value = !row.IsNull("c_total_vat") ? Convert.ToDouble(row["c_total_vat"]) : 0; // VAT ws.Cells[rowData, i++].Value = !row.IsNull("c_total_money") ? Convert.ToDouble(row["c_total_money"]) : 0; // DS ký ws.Cells[rowData, i++].Value = !row.IsNull("c_total_money_in_year") ? Convert.ToDouble(row["c_total_money_in_year"]) : 0; // DS thực hiện ws.Cells[rowData, i++].Value = !row.IsNull("c_total_money_next_year") ? Convert.ToDouble(row["c_total_money_next_year"]) : 0; // DS Chuyển ws.Cells[rowData, i++].Value = !row.IsNull("c_ds_huy") ? Convert.ToDouble(row["c_ds_huy"]) : 0; // DS Chờ hủy ws.Cells[rowData, i++].Value = !row.IsNull("c_tien_khong_tinh") ? Convert.ToDouble(row["c_tien_khong_tinh"]) : 0; // DS tiền không tính ws.Cells[rowData, i++].Value = !row.IsNull("tien_xuat_hoa_don") ? Convert.ToDouble(row["tien_xuat_hoa_don"]) : 0; // Xuất HĐ ws.Cells[rowData, i++].Value = !row.IsNull("tien_chua_xuat_hoa_don") ? Convert.ToDouble(row["tien_chua_xuat_hoa_don"]) : 0; // Chưa Xuất HĐ ws.Cells[rowData, i++].Value = !row.IsNull("c_status_name") ? row["c_status_name"].ToString() : ""; // Trạng thái } ws.Cells[3, 9].Formula = "=Sum(" + ws.Cells[4, 9].Address + ":" + ws.Cells[rowData, 9].Address + ")"; // Sum DS ĐÃ QC ĐẾN NGÀY HÔM NAY ws.Cells[3, 10].Formula = "=Sum(" + ws.Cells[4, 10].Address + ":" + ws.Cells[rowData, 10].Address + ")"; ws.Cells[3, 11].Formula = "=Sum(" + ws.Cells[4, 11].Address + ":" + ws.Cells[rowData, 11].Address + ")"; ws.Cells[3, 12].Formula = "=Sum(" + ws.Cells[4, 12].Address + ":" + ws.Cells[rowData, 12].Address + ")"; ws.Cells[3, 14].Formula = "=Sum(" + ws.Cells[4, 14].Address + ":" + ws.Cells[rowData, 14].Address + ")"; ws.Cells[3, 15].Formula = "=Sum(" + ws.Cells[4, 15].Address + ":" + ws.Cells[rowData, 15].Address + ")"; ws.Cells[3, 16].Formula = "=Sum(" + ws.Cells[4, 16].Address + ":" + ws.Cells[rowData, 16].Address + ")"; ws.Cells[3, 17].Formula = "=Sum(" + ws.Cells[4, 17].Address + ":" + ws.Cells[rowData, 17].Address + ")"; ws.Cells[3, 18].Formula = "=Sum(" + ws.Cells[4, 18].Address + ":" + ws.Cells[rowData, 18].Address + ")"; ws.Cells[3, 19].Formula = "=Sum(" + ws.Cells[4, 19].Address + ":" + ws.Cells[rowData, 19].Address + ")"; ws.Cells[3, 20].Formula = "=Sum(" + ws.Cells[4, 20].Address + ":" + ws.Cells[rowData, 20].Address + ")"; ws.Cells[3, 21].Formula = "=Sum(" + ws.Cells[4, 21].Address + ":" + ws.Cells[rowData, 21].Address + ")"; ws.Cells[3, 22].Formula = "=Sum(" + ws.Cells[4, 22].Address + ":" + ws.Cells[rowData, 22].Address + ")"; } } else { ws.Cells["A2:E2"].Merge = true; ws.Cells["A2"].Value = "You don't have permission to export data."; } ws.Column(1).AutoFit(); 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 "24HCRM_CONTRACT_" + DateTime.Now.ToString("yyyyMMdd_HHmmss") + ".xlsx")); //Suggested file name in the "Save as" dialog which will be displayed to the end user }
public ActionResult Export([DataSourceRequest] DataSourceRequest request) { if (asset.Export) { using (var dbConn = OrmliteConnection.openConn()) { FileInfo fileInfo = new FileInfo(Server.MapPath(@"~\ExportExcelFile\HopDong.xlsx")); var excelPkg = new ExcelPackage(fileInfo); string fileName = "HopDong_" + DateTime.Now.ToString("yyyyMMdd_HHmmss") + ".xlsx"; string contentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"; int rowData = 3; ExcelWorksheet expenseSheet = excelPkg.Workbook.Worksheets["Report"]; var listEmployee = dbConn.Select <EmployeeInfo>(@"SELECT A.Id,A.UserName, ISNULL(A.FullName,'') AS FullName ,ISNULL(A.Team,'') AS Team, ISNULL(B.TeamName,'') AS TeamName,ISNULL(A.RefStaffId,0) AS RefStaffId FROM EmployeeInfo A LEFT JOIN CRM_Team B ON A.Team=B.TeamID "); string whereCondition = ""; if (request.Filters.Count > 0) { whereCondition = " AND " + KendoApplyFilter.ApplyFilter(request.Filters[0], ""); } request.PageSize = 100; var data = new CRM_Contract().GetExport(request, whereCondition); foreach (var item in data) { int i = 1; expenseSheet.Cells[rowData, i++].Value = item.c_week; expenseSheet.Cells[rowData, i++].Value = item.c_month; expenseSheet.Cells[rowData, i++].Value = item.c_contract_code; expenseSheet.Cells[rowData, i++].Value = item.c_customer_name; expenseSheet.Cells[rowData, i++].Value = item.c_labels; expenseSheet.Cells[rowData, i++].Value = item.c_staffid; expenseSheet.Cells[rowData, i++].Value = item.c_staffid; expenseSheet.Cells[rowData, i++].Value = item.c_dt_da_qc_den_hom_nay; // DS Đã quảng cáo expenseSheet.Cells[rowData, i++].Value = item.c_dt_da_xuat_ban; // DS Đã xuất bản expenseSheet.Cells[rowData, i++].Value = item.c_payment_money; // DS Đã Thu expenseSheet.Cells[rowData, i++].Value = item.c_balance; // Còn nợ expenseSheet.Cells[rowData, i++].Value = item.c_total_money; // DS ký expenseSheet.Cells[rowData, i++].Value = item.c_name; // DS Thực hiện expenseSheet.Cells[rowData, i++].Value = item.c_total_vat; // VAT expenseSheet.Cells[rowData, i++].Value = item.c_get_money_next_week; // DS Chuyển expenseSheet.Cells[rowData, i++].Value = item.c_ds_huy; // DS Hủy rowData++; } expenseSheet.Cells.AutoFitColumns(); MemoryStream output = new MemoryStream(); excelPkg.SaveAs(output); output.Position = 0; return(File(output.ToArray(), contentType, fileName)); } } else { return(Json(new { success = false, message = "Không có quyền cập nhật" })); } }
public ActionResult Contract_Save(CRM_Contract itemContract) { var rs = CRM_Contract.Save(itemContract, currentUser.UserName); return(Json(rs)); }