Esempio n. 1
0
        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" }));
            }
        }
Esempio n. 2
0
 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"));
     }
 }
Esempio n. 3
0
        //===============================================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
        }
Esempio n. 4
0
        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" }));
            }
        }
Esempio n. 5
0
        public ActionResult Contract_Save(CRM_Contract itemContract)
        {
            var rs = CRM_Contract.Save(itemContract, currentUser.UserName);

            return(Json(rs));
        }