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