private void LoadFile()
{
try
{
//Khai bao bien
FlexCel.Report.FlexCelReport flcReport = new FlexCel.Report.FlexCelReport();
string path = "";
//Xac dinh ten file mau theo loai bao cao
path = GetXlsFileDesign();
//Add du lieu tu ds, bien vao Flexcel report
if (p_rpType < 30)
{
flcReport.AddTable(p_ds); // Gan du lieu vao ds
}
SetValueFlexcel(ref flcReport); // Thiet lap gt cho cac bien object
#region Luu file xls len memory stream
//FlexCelPreview1.CenteredPreview = true;
//_templateStream.Flush();
try
{
_templateStream = new FileStream(path, FileMode.Open);
}
catch
{
}
_xlsMemoryStream = new MemoryStream();
flcReport.Run(_templateStream, _xlsMemoryStream);//Chet o day???????????????????????
#endregion
#region View file tren mem len FlexCelPreview1
xls = new FlexCel.XlsAdapter.XlsFile();
_xlsMemoryStream.Position = 0;
xls.Open(_xlsMemoryStream);
//
SetValuePageSize(ref flexCelImgExport1);
flexCelImgExport1.Workbook = xls;
FlexCelPreview1.InvalidatePreview();
#endregion
//Hien thi so trag, zoom cua page
txtPage.Text = String.Format("{0} / {1}", FlexCelPreview1.StartPage, FlexCelPreview1.TotalPages);
//UpdateZoom();
//Gan gia tri stream cho bien toan cuc cua form
c_XlsStream = _xlsMemoryStream;
_templateStream.Dispose();
}
catch (Exception ex)
{
//Neu loi ko Kiet xuat dc
MessageBox.MessageBoxBA msgDialog = new Taxi.MessageBox.MessageBoxBA();
msgDialog.Show(this, "Xảy ra lỗi trong quá trình lấy dữ liệu và tạo báo cáo!", "Thông báo", Taxi.MessageBox.MessageBoxButtonsBA.OK, Taxi.MessageBox.MessageBoxIconBA.Error);
//this.Close();
}
}
public FileContentResult RacunReport(long racunGlavaId)
{
DataAccessAdapterBase adapter = Helpers.Helper.GetDataAccessAdapter();
long firmaId = UserEntity.GetFirmaId(adapter, User.Identity.Name);
string reportPath = Server.MapPath(string.Format("~/ReportTemplates/{0}/Racun.xls", firmaId));
FlexCel.XlsAdapter.XlsFile xls = new FlexCel.XlsAdapter.XlsFile();
xls.Open(reportPath);
List <RacunReport> racunGlavaList = new List <RacunReport>();
racunGlavaList.Add(new RacunReport(adapter, racunGlavaId));
FlexCel.Report.FlexCelReport report = new FlexCel.Report.FlexCelReport();
report.AddTable("Racun", racunGlavaList);
report.Run(xls);
using (System.IO.MemoryStream ms = new System.IO.MemoryStream())
{
FlexCel.Render.FlexCelPdfExport pdfExport = new FlexCel.Render.FlexCelPdfExport(xls);
pdfExport.BeginExport(ms);
pdfExport.ExportAllVisibleSheets(false, "");
pdfExport.EndExport();
//xls.Save(ms);
ms.Position = 0;
return(File(ms.ToArray(), "application/pdf"));
}
}
public FileContentResult RacunReportCollection(string jqGridFilters)
{
DataAccessAdapterBase adapter = Helpers.Helper.GetDataAccessAdapter();
long firmaId = UserEntity.GetFirmaId(adapter, User.Identity.Name);
string reportPath = Server.MapPath(string.Format("~/ReportTemplates/{0}/Racun.xls", firmaId));
FlexCel.XlsAdapter.XlsFile xls = new FlexCel.XlsAdapter.XlsFile();
xls.Open(reportPath);
short godina = ConfigEntity.GetInstance(adapter, firmaId).AktivnaGodina;
RelationPredicateBucket bucket = RacunGlavaPager.CreateBucket(godina, jqGridFilters);
bucket.PredicateExpression.Add(RacunGlavaFields.FirmaId == firmaId);
IEnumerable <RacunReport> racunReportCollection = CoolJ.DatabaseGeneric.BusinessLogic.RacunReport.GetRacunReportCollection(adapter, bucket, firmaId);
FlexCel.Report.FlexCelReport report = new FlexCel.Report.FlexCelReport();
report.AddTable("Racun", racunReportCollection);
report.Run(xls);
using (System.IO.MemoryStream ms = new System.IO.MemoryStream())
{
FlexCel.Render.FlexCelPdfExport pdfExport = new FlexCel.Render.FlexCelPdfExport(xls);
pdfExport.BeginExport(ms);
pdfExport.ExportAllVisibleSheets(false, "");
pdfExport.EndExport();
//xls.Save(ms);
ms.Position = 0;
return(File(ms.ToArray(), "application/pdf"));
}
}
void Export()
{
try
{
#region Kết xuất
FlexCel.Report.FlexCelReport flcReport = new FlexCel.Report.FlexCelReport();
string _path = CommonData.ExcelDesignFilePath;
string _fileExcelName = "Contract_Report_Ky.xls";
DataSet ds_all = new DataSet();
DataTable _dt = ConvertData.ConvertToDatatable(c_lst);
_dt.Columns.Add("STT");
int index = 1;
foreach (DataRow item in _dt.Rows)
{
item["STT"] = index;
index++;
}
ds_all.Tables.Add(_dt);
ds_all.Tables[0].TableName = "Contract";
if (ds_all.Tables.Count <= 0)
{
Mouse.OverrideCursor = null;
NoteBox.Show("Không có dữ liệu để kết xuất", "Thông báo", NoteBoxLevel.Note);
return;
}
if (ds_all.Tables[0].Rows.Count <= 0)
{
Mouse.OverrideCursor = null;
NoteBox.Show("Không có dữ liệu để kết xuất", "Thông báo", NoteBoxLevel.Note);
return;
}
_path += _fileExcelName;
string _title_report = "BÁO CÁO KỶ Y";
CommonFunction.SetValueExportByDataTable(ref flcReport, ds_all);
CommonFunction.SetValueExportByString(ref flcReport, "title_report", _title_report);
System.Windows.Forms.SaveFileDialog saveReport = new System.Windows.Forms.SaveFileDialog();
saveReport.Filter = "Excel files (*.xls)|*.xls";
if (saveReport.ShowDialog() == System.Windows.Forms.DialogResult.OK)
{
CommonFunction.ExportExcel(flcReport, _path, saveReport);
}
#endregion
}
catch (Exception ex)
{
ErrorLog.log.Error(ex.ToString());
}
}
public static void SetValueExportByDataTable(ref FlexCel.Report.FlexCelReport flcReport, DataSet v_ds)
{
try
{
flcReport.AddTable(v_ds);
}
catch
{
}
}
public static void SetValueExportByString(ref FlexCel.Report.FlexCelReport flcReport, string _ParamName, object _value)
{
try
{
flcReport.SetValue(_ParamName, _value);
}
catch (Exception ex)
{
}
}
public ActionResult BaoCaoHangTonKho(decimal p_warehouse_id, string p_wareHouse_name)
{
try
{
Report_BL _Report_BL = new Report_BL();
string c_err = "";
int is_err = -1;
FlexCel.Report.FlexCelReport flcReport = new FlexCel.Report.FlexCelReport();
string _fileExcelName_Src = "/Content/FlexcelDesignFile/BaoCaoTonKho.xls"; // tên mẫu
string _fileExcelName_Export = "/Content/FlexcelExportFile/Bao_Cao_Ton_Kho.xls"; // tên file đích
//DataSet _ds = _Report_BL.Get_TonKho(p_warehouse_id);
List <TonKho_Info> _lst = _Report_BL.Get_TonKho(p_warehouse_id);
foreach (TonKho_Info item in _lst)
{
item.SoNgayTon = Math.Round((decimal)DateTime.Now.Subtract(item.Register_Date.AddDays((double)item.Period)).TotalDays);
}
DataSet _ds = NaviCommon.ConvertData.ConvertToDataSet <TonKho_Info>(_lst);
//someDateTime.Subtract(otherDateTime),
if (_ds == null)
{
return(Json(new { c_err = "Không có dữ liệu báo cáo", is_err = -1 }));
}
else
{
if (_ds != null && _ds.Tables.Count > 0 && _ds.Tables[0].Rows.Count > 0)
{
_ds.Tables[0].TableName = "Data";
// đẩy dataset vào temp
CommonFunc.SetValueExportByDataTable(ref flcReport, _ds);
CommonFunc.SetValueExportByString(ref flcReport, "TenKho", p_wareHouse_name);
is_err = CommonFunc.ExportExcel(flcReport, Server.MapPath(_fileExcelName_Src), Server.MapPath(_fileExcelName_Export), ref c_err);
if (is_err == 0)// ko có lỗi
{
c_err = _fileExcelName_Export;
}
}
else
{
c_err = "Không có dữ liệu để kết xuất!";
}
}
return(Json(new { c_err = c_err, is_err = is_err }));
}
catch (Exception ex)
{
NaviCommon.Common.log.Error(ex.ToString());
return(Json(new { success = -1 }));
}
}
public static void SetValueExportByString(ref FlexCel.Report.FlexCelReport flcReport, string _ParamName, object _value)
{
try
{
flcReport.SetValue(_ParamName, _value);
}
catch (Exception ex)
{
CommonData.log.Error(ex.ToString());
}
}
public static void SetValueExportByDataTable(ref FlexCel.Report.FlexCelReport flcReport, DataSet v_ds)
{
try
{
flcReport.AddTable(v_ds);
}
catch (Exception ex)
{
CommonData.log.Error(ex.ToString());
}
}
private void SetValueFlexcel(ref FlexCel.Report.FlexCelReport flcReport)
{
try
{
Common.SetValueExportByString(ref flcReport, "ngayht", DateTime.Now);
Common.SetValueExportByString(ref flcReport, "TuNgay", DateTime.Now);
Common.SetValueExportByString(ref flcReport, "DenNgay", DateTime.Now);
if (p_rpType == ConstParam.TongHopPhanAnh)
{
Common.SetValueExportByString(ref flcReport, "KhachHang", p_ds.Tables[0].Rows[_rowIndex][0]);
Common.SetValueExportByString(ref flcReport, "SDT", p_ds.Tables[0].Rows[_rowIndex][1]);
Common.SetValueExportByString(ref flcReport, "NhanVien", p_ds.Tables[0].Rows[_rowIndex][2]);
Common.SetValueExportByString(ref flcReport, "ThoiGianNhan", p_ds.Tables[0].Rows[_rowIndex][3]);
Common.SetValueExportByString(ref flcReport, "ThoiGianXuong", p_ds.Tables[0].Rows[_rowIndex][4]);
Common.SetValueExportByString(ref flcReport, "LoTrinh", p_ds.Tables[0].Rows[_rowIndex]["Tong"].ToString());
}
if (p_rpType == ConstParam.GiaiQuyetPhanAnh)
{
string strDot = ConstParam.DotLine;
Common.SetValueExportByString(ref flcReport, "TenKhachHang", p_ds.Tables[0].Rows[_rowIndex]["TenKH"]);
Common.SetValueExportByString(ref flcReport, "LoaiPhanAnh", p_ds.Tables[0].Rows[_rowIndex]["LoaiPA"]);
Common.SetValueExportByString(ref flcReport, "SoDienThoai", p_ds.Tables[0].Rows[_rowIndex]["SoDT"]);
Common.SetValueExportByString(ref flcReport, "TenDayDu", p_ds.Tables[0].Rows[_rowIndex]["TenDayDu"]);
Common.SetValueExportByString(ref flcReport, "ThoiGianPhanAnh", ((DateTime)p_ds.Tables[0].Rows[_rowIndex]["TGPA"]).ToString("HH:MM - dd/MM/yyyy "));
Common.SetValueExportByString(ref flcReport, "ThoiGianPS", ((DateTime)p_ds.Tables[0].Rows[_rowIndex]["TGPS"]).ToString("HH:MM - dd/MM/yyyy "));
Common.SetValueExportByString(ref flcReport, "LoTrinhDi", p_ds.Tables[0].Rows[_rowIndex]["LTTu"]);
Common.SetValueExportByString(ref flcReport, "LoTrinhDen", p_ds.Tables[0].Rows[_rowIndex]["LTDen"]);
Common.SetValueExportByString(ref flcReport, "TienDH", p_ds.Tables[0].Rows[_rowIndex]["DHT"]);
Common.SetValueExportByString(ref flcReport, "DacDiem", p_ds.Tables[0].Rows[_rowIndex]["DacDiem"]);
Common.SetValueExportByString(ref flcReport, "DoiTuong", p_ds.Tables[0].Rows[_rowIndex]["DoiTuong"]);
Common.SetValueExportByString(ref flcReport, "GiaiQuyet", p_ds.Tables[0].Rows[_rowIndex]["GQ_KQGQ"]);
Common.SetValueExportByString(ref flcReport, "GiaiQuyetYKKH", p_ds.Tables[0].Rows[_rowIndex]["GQ_YKKH"]);
Common.SetValueExportByString(ref flcReport, "NoiDungKN", p_ds.Tables[0].Rows[_rowIndex]["NoiDung"]);
Common.SetValueExportByString(ref flcReport, "DotLine", strDot);
}
}
catch (Exception ex)
{
//ErrorLog.log.Error(ex.ToString());
}
}
public void GerarExcel <T>(IList <T> aLista, string NomeFonte, string aCaminhoTemplate, string acaminhoArquivoSaida)
{
try
{
Report = new FlexCel.Report.FlexCelReport(true);
ReportFile = new FlexCel.XlsAdapter.XlsFile(true);
StreamRelatorio = new MemoryStream();
SetReport(aLista, NomeFonte);
this.CreateFile(ReportFile, aCaminhoTemplate);
Report.Run(ReportFile);
ReportFile.Save(acaminhoArquivoSaida);
}
catch (Exception e)
{
throw e;
}
}
void Export()
{
try
{
#region Kết xuất
FlexCel.Report.FlexCelReport flcReport = new FlexCel.Report.FlexCelReport();
string _path = CommonData.ExcelDesignFilePath;
string _fileExcelName = "Hoa_Don_Ban_Buon.xls";
DataSet ds_all = new DataSet();
DataTable _dt = ConvertData.ConvertToDatatable(c_list_Product_Buy);
_dt.Columns.Add("STT");
int index = 1;
foreach (DataRow item in _dt.Rows)
{
item["STT"] = index;
index++;
}
ds_all.Tables.Add(_dt);
ds_all.Tables[0].TableName = "BanBuon";
if (ds_all.Tables.Count <= 0)
{
Mouse.OverrideCursor = null;
NoteBox.Show("Không có dữ liệu để kết xuất", "Thông báo", NoteBoxLevel.Note);
return;
}
if (ds_all.Tables[0].Rows.Count <= 0)
{
Mouse.OverrideCursor = null;
NoteBox.Show("Không có dữ liệu để kết xuất", "Thông báo", NoteBoxLevel.Note);
return;
}
_path += _fileExcelName;
CommonFunction.SetValueExportByDataTable(ref flcReport, ds_all);
CommonFunction.SetValueExportByString(ref flcReport, "SoHoaDon", txtSaleHeader.Text);
CommonFunction.SetValueExportByString(ref flcReport, "Customer", txtCustomer_Name.Text);
CommonFunction.SetValueExportByString(ref flcReport, "Address", txtAddress.Text);
string _tienBangChu = ConvertData.ConvertMoneyToStr(Convert.ToDouble(txtPayPrice.Text));
CommonFunction.SetValueExportByString(ref flcReport, "TienBangChu", _tienBangChu);
System.Windows.Forms.SaveFileDialog saveReport = new System.Windows.Forms.SaveFileDialog();
saveReport.Filter = "Excel files (*.xls)|*.xls";
if (saveReport.ShowDialog() == System.Windows.Forms.DialogResult.OK)
{
CommonFunction.ExportExcel(flcReport, _path, saveReport);
}
#endregion
}
catch (Exception ex)
{
CommonData.log.Error(ex.ToString());
}
}
public static void ExportExcel(FlexCel.Report.FlexCelReport flcReport, string pathFile, System.Windows.Forms.SaveFileDialog saveReport)
{
FileStream _templateStream = null;
try
{
flcReport.DeleteEmptyRanges = false; string _path = "";
string exepath = Path.GetDirectoryName(Assembly.GetExecutingAssembly().CodeBase).Replace(@"file:\", "");
MemoryStream _xlsMemoryStream = new MemoryStream();
#region Luu file xls len memory stream
_path = exepath + "\\" + pathFile.Replace("//", "\\");
bool ckFileIsNotOpen = true;
//check xem file co ton tai trong duong dan ko?
if (!File.Exists(exepath + "\\" + pathFile.Replace("//", "\\")))
{
//bao loi ko ton tai file;
//NoteBox.Show("File excel thiết kế không tồn tại trong thư mục Run/report. Kết xuất không thành công.", "Thông báo", NoteBoxLevel.Note);
}
else
{
//check file co dang mo hay ko
try
{
var stream = new FileStream(_path, FileMode.Open, FileAccess.Read);
stream.Close();
}
catch
{
//NoteBox.Show("File mẫu đang mở hoặc bị process khác sử dụng, bạn phải đóng file đó mới có thể kết xuất", "Error", NoteBoxLevel.Error);
ckFileIsNotOpen = false;
}
if (ckFileIsNotOpen == true)
{
_templateStream = new System.IO.FileStream(_path, System.IO.FileMode.Open);
flcReport.Run(_templateStream, _xlsMemoryStream);
//luu file
_xlsMemoryStream.Position = 0;
byte[] bytes = new byte[_xlsMemoryStream.Length];
_xlsMemoryStream.Read(bytes, 0, (int)_xlsMemoryStream.Length);
try
{
FileStream OutStream;
OutStream = new FileStream(saveReport.FileName, FileMode.Create);
OutStream.Write(bytes, 0, bytes.Length);
OutStream.Close();
_xlsMemoryStream.Close();
_templateStream.Close();
//Neu chon mo file da luu
//MessageBoxResult result = NoteBox.Show("Bạn có muốn mở file vừa chọn kết xuất không?", "Thông báo", NoteBoxLevel.Question);
//if (result == MessageBoxResult.Yes)
//{
// Process.Start(saveReport.FileName);
//}
}
catch (Exception)
{
_templateStream.Close();
//NoteBox.Show("File mẫu đang mở hoặc bị process khác sử dụng, bạn phải đóng file đó mới có thể kết xuất", "Error", NoteBoxLevel.Error);
//Mouse.OverrideCursor = null;
return;
}
}
}
#endregion
}
catch (Exception ex)
{
_templateStream.Close();
//NoteBox.Show("Kết xuất file excel không thành công", "Thông báo", NoteBoxLevel.Error);
}
}
private void GenerateReportButton_Click(object sender, EventArgs e)
{
SqlConnection conn = new SqlConnection();
SqlCommand cmd = new SqlCommand();
if (!Directory.Exists(Directory.GetCurrentDirectory() + "\\Output\\"))
{
Directory.CreateDirectory(Directory.GetCurrentDirectory() + "\\Output\\");
}
try
{
conn.ConnectionString = ConnectionStringTextBox.Text;
conn.Open();
cmd.Connection = conn;
if (UnknownSummaryMRIActualsRadio.Checked)
{
#region UnknownSummaryMRIActuals
cmd.CommandTimeout = 0;
cmd.CommandText = "stp_S_UnknownSummaryMRIActuals";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("BudgetYear", Convert.ToInt32(BudgetYearParameterTextBox.Text)));
cmd.Parameters.Add(new SqlParameter("BudgetQuater", BudgetQuaterParameterTextBox.Text));
cmd.Parameters.Add(new SqlParameter("DataPriorToDate", Convert.ToDateTime(DataPriorToDateParameterTextBox.Text)));
cmd.Parameters.Add(new SqlParameter("StartPeriod", Convert.ToInt32(StartPeriodParameterTextBox.Text)));
cmd.Parameters.Add(new SqlParameter("EndPeriod", Convert.ToInt32(EndPeriodParameterTextBox.Text)));
//@BudgetYear int,
//@BudgetQuater Varchar(2),
//@DataPriorToDate DateTime,
//@StartPeriod int,
//@EndPeriod int
DataSet result = GetUnknownSummaryMRIActualsDataSet();
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
DataRow newRow = result.Tables[0].NewRow();
//newRow[""] = reader[""];
newRow["SourceCode"] = reader["SourceCode"];
newRow["ProfitabilityActualKey"] = reader["ProfitabilityActualKey"];
newRow["ReferenceCode"] = reader["ReferenceCode"];
newRow["HasActivityTypeUnknown"] = reader["HasActivityTypeUnknown"];
newRow["HasAllocationRegionUnknown"] = reader["HasAllocationRegionUnknown"];
newRow["HasFunctionalDepartmentUnknown"] = reader["HasFunctionalDepartmentUnknown"];
newRow["HasGlAccountUnknown"] = reader["HasGlAccountUnknown"];
newRow["HasGlAccountCategoryUnknown"] = reader["HasGlAccountCategoryUnknown"];
newRow["HasOriginatingRegionUnknown"] = reader["HasOriginatingRegionUnknown"];
newRow["HasPropertyFundUnknown"] = reader["HasPropertyFundUnknown"];
newRow["InValidOriginatingRegionAndFunctionalDepartment"] = reader["InValidOriginatingRegionAndFunctionalDepartment"];
newRow["InValidActivityTypeAndEntity"] = reader["InValidActivityTypeAndEntity"];
newRow["ResolvedBy"] = reader["ResolvedBy"];
newRow["Gr MajorCategoryName"] = reader["Gr MajorCategoryName"];
newRow["Gr MinorCategoryName"] = reader["Gr MinorCategoryName"];
newRow["Gr AccountSubTypeName"] = reader["Gr AccountSubTypeName"];
newRow["Gr FeeOrExpense"] = reader["Gr FeeOrExpense"];
newRow["Gr ReportingEntityName"] = reader["Gr ReportingEntityName"];
newRow["Gr ActivityTypeName"] = reader["Gr ActivityTypeName"];
newRow["Gr OriginatingRegionName"] = reader["Gr OriginatingRegionName"];
newRow["Gr OriginatingSubRegionName"] = reader["Gr OriginatingSubRegionName"];
newRow["Gr AllocationRegionName"] = reader["Gr AllocationRegionName"];
newRow["Gr AllocationSubRegionName"] = reader["Gr AllocationSubRegionName"];
newRow["Gr OverheadName"] = reader["Gr OverheadName"];
newRow["Gr GlAccountCode"] = reader["Gr GlAccountCode"];
newRow["Gr GlAccountName"] = reader["Gr GlAccountName"];
newRow["Gr FunctionalDepartmentName"] = reader["Gr FunctionalDepartmentName"];
newRow["Period"] = reader["Period"];
newRow["Ref"] = reader["Ref"];
newRow["SiteID"] = reader["SiteID"];
newRow["EntityID"] = reader["EntityID"];
newRow["EntityName"] = reader["EntityName"];
newRow["GlAccountCode"] = reader["GlAccountCode"];
newRow["GlAccountName"] = reader["GlAccountName"];
newRow["Department"] = reader["Department"];
newRow["DepartmentDescription"] = reader["DepartmentDescription"];
newRow["JobCode"] = reader["JobCode"];
newRow["JobCodeDescription"] = reader["JobCodeDescription"];
newRow["Amount"] = reader["Amount"];
newRow["Description"] = reader["Description"];
newRow["EnterDate"] = reader["EnterDate"];
newRow["Reversal"] = reader["Reversal"];
newRow["Status"] = reader["Status"];
newRow["Basis"] = reader["Basis"];
newRow["UserId"] = reader["UserId"];
newRow["CorporateDepartmentCode"] = reader["CorporateDepartmentCode"];
newRow["Source"] = reader["Source"];
result.Tables[0].Rows.Add(newRow);
}
//Mapp DataSet to Flexcel
FlexCel.Report.FlexCelReport unknownSummaryMRIActuals = new FlexCel.Report.FlexCelReport();
unknownSummaryMRIActuals.ClearUserFunctions();
unknownSummaryMRIActuals.AddTable(result);
unknownSummaryMRIActuals.SetValue("Date", DateTime.Now);
MemoryStream OutStream = new MemoryStream();
try
{
FileStream InStream = new FileStream(Directory.GetCurrentDirectory() + "\\UnknownSummaryMRIActualsTemplate.xlsx", FileMode.Open, FileAccess.Read, FileShare.ReadWrite);
try
{
unknownSummaryMRIActuals.Run(InStream, OutStream);
}
finally
{
InStream.Close();
}
string filePath = Directory.GetCurrentDirectory() + "\\Output\\" + DateTime.Now.ToString("yyyy-MM-dd HHmmss") + " UnknownSummaryMRIActuals.xlsx";
FileStream streamWriter = new FileStream(filePath, FileMode.CreateNew);
BinaryWriter writeBinay = new BinaryWriter(streamWriter);
writeBinay.Write(OutStream.ToArray());
writeBinay.Close();
}
finally
{
OutStream.Close();
}
#endregion
}
else if (UnknownSummaryAllocatedOverheadRadio.Checked)
{
#region UnknownSummaryAllocatedOverhead
cmd.CommandTimeout = 0;
cmd.CommandText = "stp_S_UnknownSummaryAllocatedOverhead";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("BudgetYear", Convert.ToInt32(BudgetYearParameterTextBox.Text)));
cmd.Parameters.Add(new SqlParameter("BudgetQuater", BudgetQuaterParameterTextBox.Text));
cmd.Parameters.Add(new SqlParameter("DataPriorToDate", Convert.ToDateTime(DataPriorToDateParameterTextBox.Text)));
cmd.Parameters.Add(new SqlParameter("StartPeriod", Convert.ToInt32(StartPeriodParameterTextBox.Text)));
cmd.Parameters.Add(new SqlParameter("EndPeriod", Convert.ToInt32(EndPeriodParameterTextBox.Text)));
//@BudgetYear int,
//@BudgetQuater Varchar(2),
//@DataPriorToDate DateTime,
//@StartPeriod int,
//@EndPeriod int
DataSet result = GetUnknownSummaryAllocatedOverheadDataSet();
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
DataRow newRow = result.Tables[0].NewRow();
//newRow[""] = reader[""];
newRow["SourceCode"] = reader["SourceCode"];
newRow["ProfitabilityActualKey"] = reader["ProfitabilityActualKey"];
newRow["ReferenceCode"] = reader["ReferenceCode"];
newRow["HasActivityTypeUnknown"] = reader["HasActivityTypeUnknown"];
newRow["HasAllocationRegionUnknown"] = reader["HasAllocationRegionUnknown"];
newRow["HasFunctionalDepartmentUnknown"] = reader["HasFunctionalDepartmentUnknown"];
newRow["HasGlAccountUnknown"] = reader["HasGlAccountUnknown"];
newRow["HasGlAccountCategoryUnknown"] = reader["HasGlAccountCategoryUnknown"];
newRow["HasOriginatingRegionUnknown"] = reader["HasOriginatingRegionUnknown"];
newRow["HasPropertyFundUnknown"] = reader["HasPropertyFundUnknown"];
newRow["InValidRegionAndFunctionalDepartment"] = reader["InValidRegionAndFunctionalDepartment"];
newRow["InValidActivityTypeEntity"] = reader["InValidActivityTypeEntity"];
newRow["ResolvedBy"] = reader["ResolvedBy"];
newRow["ExpensePeriod"] = reader["ExpensePeriod"];
newRow["AllocationRegionCode"] = reader["AllocationRegionCode"];
newRow["AllocationRegionName"] = reader["AllocationRegionName"];
newRow["OriginatingRegionCode"] = reader["OriginatingRegionCode"];
newRow["OriginatingRegionSourceCode"] = reader["OriginatingRegionSourceCode"];
newRow["PropertyFundName"] = reader["PropertyFundName"];
newRow["FunctionalDepartmentCode"] = reader["FunctionalDepartmentCode"];
newRow["ActivityTypeCode"] = reader["ActivityTypeCode"];
newRow["ForeignCurrency"] = reader["ForeignCurrency"];
newRow["ForeignActual"] = reader["ForeignActual"];
newRow["GlAccountCode"] = reader["GlAccountCode"];
newRow["EmployeeDisplayName"] = reader["EmployeeDisplayName"];
result.Tables[0].Rows.Add(newRow);
}
//Mapp DataSet to Flexcel
FlexCel.Report.FlexCelReport unknownSummaryAllocatedOverhead = new FlexCel.Report.FlexCelReport();
unknownSummaryAllocatedOverhead.ClearUserFunctions();
unknownSummaryAllocatedOverhead.AddTable(result);
unknownSummaryAllocatedOverhead.SetValue("Date", DateTime.Now);
MemoryStream OutStream = new MemoryStream();
try
{
FileStream InStream = new FileStream(Directory.GetCurrentDirectory() + "\\UnknownSummaryAllocatedOverheadTemplate.xlsx", FileMode.Open, FileAccess.Read, FileShare.ReadWrite);
try
{
unknownSummaryAllocatedOverhead.Run(InStream, OutStream);
}
finally
{
InStream.Close();
}
string filePath = Directory.GetCurrentDirectory() + "\\Output\\" + DateTime.Now.ToString("yyyy-MM-dd HHmmss") + " UnknownSummaryAllocatedOverhead.xlsx";
FileStream streamWriter = new FileStream(filePath, FileMode.CreateNew);
BinaryWriter writeBinay = new BinaryWriter(streamWriter);
writeBinay.Write(OutStream.ToArray());
writeBinay.Close();
}
finally
{
OutStream.Close();
}
#endregion
}
else
{
throw new ApplicationException("Invalid Radio Selection");
}
MessageBox.Show("Export Complete", "Exporter", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
finally
{
if (conn != null)
{
conn.Dispose();
}
}
}
public static int ExportExcel(FlexCel.Report.FlexCelReport flcReport, string pathFileSource, string c_fileExport, ref string _err)//, System.Windows.Forms.SaveFileDialog saveReport)
{
System.IO.FileStream _templateStream = null;
try
{
flcReport.DeleteEmptyRanges = false; string _path_source = "";
//string exepath = Path.GetDirectoryName(Assembly.GetExecutingAssembly().CodeBase).Replace(@"file:\", "");
MemoryStream _xlsMemoryStream = new MemoryStream();
#region Luu file xls len memory stream
_path_source = pathFileSource.Replace("//", "\\");
bool ckFileIsNotOpen = true;
//check xem file co ton tai trong duong dan ko?
if (!System.IO.File.Exists(_path_source))//if (!System.IO.File.Exists(exepath + "\\" + pathFile.Replace("//", "\\")))
{
//bao loi ko ton tai file;
_err = "Không tồn tại file mẫu trong thư mục mẫu!";
return(-3); // không có file mẫu
}
else
{
//check file co dang mo hay ko
try
{
var stream = new FileStream(_path_source, FileMode.Open, FileAccess.Read);
stream.Close();
}
catch
{
//NoteBox.Show("File mẫu đang mở hoặc bị process khác sử dụng, bạn phải đóng file đó mới có thể kết xuất", "Error", NoteBoxLevel.Error);
ckFileIsNotOpen = false;
_err = "File đang được mở trong process khác! Không thể tạo báo cáo!";
return(-2); // file đang mo không làm j đc
}
if (ckFileIsNotOpen == true)
{
_templateStream = new System.IO.FileStream(_path_source, System.IO.FileMode.Open);
flcReport.Run(_templateStream, _xlsMemoryStream);
//luu file
_xlsMemoryStream.Position = 0;
byte[] bytes = new byte[_xlsMemoryStream.Length];
_xlsMemoryStream.Read(bytes, 0, (int)_xlsMemoryStream.Length);
try
{
FileStream OutStream;
OutStream = new FileStream(c_fileExport, FileMode.Create);//new FileStream(saveReport.FileName, FileMode.Create);
OutStream.Write(bytes, 0, bytes.Length);
OutStream.Close();
_xlsMemoryStream.Close();
_templateStream.Close();
return(0);
}
catch (Exception ex)
{
NaviCommon.Common.log.Error(ex.ToString());
_templateStream.Close();
_err = "Lỗi tạo file báo cáo !";
return(-1); // lỗi tạo file
}
}
_err = "File đang được mở trong process khác! Không thể tạo báo cáo!";
return(-2); // file đang mo không làm j đc
}
#endregion
}
catch (Exception ex)
{
NaviCommon.Common.log.Error(ex.ToString());
_templateStream.Close();
_err = "Lỗi tạo file báo cáo !";
return(-1);
}
}
void Export()
{
try
{
#region Kết xuất
FlexCel.Report.FlexCelReport flcReport = new FlexCel.Report.FlexCelReport();
string _path = CommonData.ExcelDesignFilePath;
string _fileExcelName = "Report_Fee.xls";
foreach (Fee_Report_Info item in c_lst)
{
if (item.Currency == (decimal)Enum_Contract_Currency.VND)
{
item.PhiMoiGio_VND = item.PhiMoiGio;
item.TienChuaVe_VND = item.TienChuaVe;
item.TienDaVe_VND = item.TienDaVe;
item.PhiMoiGio_USD = 0;
item.TienChuaVe_USD = 0;
item.TienDaVe_USD = 0;
}
else
{
item.PhiMoiGio_USD = item.PhiMoiGio;
item.TienChuaVe_USD = item.TienChuaVe;
item.TienDaVe_USD = item.TienDaVe;
item.PhiMoiGio_VND = 0;
item.TienChuaVe_VND = 0;
item.TienDaVe_VND = 0;
}
}
DataSet ds_all = new DataSet();
DataTable _dt = ConvertData.ConvertToDatatable(c_lst);
_dt.Columns.Add("STT");
int index = 1;
foreach (DataRow item in _dt.Rows)
{
item["STT"] = index;
index++;
}
ds_all.Tables.Add(_dt);
ds_all.Tables[0].TableName = "Fee";
if (ds_all.Tables.Count <= 0)
{
Mouse.OverrideCursor = null;
NoteBox.Show("Không có dữ liệu để kết xuất", "Thông báo", NoteBoxLevel.Note);
return;
}
if (ds_all.Tables[0].Rows.Count <= 0)
{
Mouse.OverrideCursor = null;
NoteBox.Show("Không có dữ liệu để kết xuất", "Thông báo", NoteBoxLevel.Note);
return;
}
_path += _fileExcelName;
CommonFunction.SetValueExportByDataTable(ref flcReport, ds_all);
System.Windows.Forms.SaveFileDialog saveReport = new System.Windows.Forms.SaveFileDialog();
saveReport.Filter = "Excel files (*.xls)|*.xls";
if (saveReport.ShowDialog() == System.Windows.Forms.DialogResult.OK)
{
CommonFunction.ExportExcel(flcReport, _path, saveReport);
}
#endregion
}
catch (Exception ex)
{
ErrorLog.log.Error(ex.ToString());
}
}
void Export_Payment()
{
try
{
Fee_Report_Info _Fees_Revenue_Info = (Fee_Report_Info)dgrContract.SelectedItem;
if (_Fees_Revenue_Info == null)
{
return;
}
Customer_Info _Customer_Info = _Customer_Controller.Customer_GetById(_Fees_Revenue_Info.Object_Id);
Estate_Object_Info _Estate_Object_Info = _Estate_Object_Controller.Estate_Object_GetById(_Fees_Revenue_Info.Estate_Id, _Fees_Revenue_Info.Contract_Type);
#region Kết xuất
FlexCel.Report.FlexCelReport flcReport = new FlexCel.Report.FlexCelReport();
string _path = CommonData.ExcelDesignFilePath;
string _fileExcelName = "Payment_Request_VND.xls";
if (_Fees_Revenue_Info.Currency == (decimal)Enum_Contract_Currency.USD)
{
_fileExcelName = "Payment_Request_USD.xls";
}
_path += _fileExcelName;
CommonFunction.SetValueExportByString(ref flcReport, "CurrentDate", DateTime.Now.ToString("dd/MM/yyyy"));
CommonFunction.SetValueExportByString(ref flcReport, "Tenat_Name", _Customer_Info.Customer_Name);
CommonFunction.SetValueExportByString(ref flcReport, "Address", _Customer_Info.Address);
CommonFunction.SetValueExportByString(ref flcReport, "Users", _Fees_Revenue_Info.Users);
CommonFunction.SetValueExportByString(ref flcReport, "TaxCode", _Customer_Info.Tax_Code);
CommonFunction.SetValueExportByString(ref flcReport, "Estate_Name", _Estate_Object_Info.Estate_Name);
CommonFunction.SetValueExportByString(ref flcReport, "Contract_FromDate", _Fees_Revenue_Info.Contract_FromDate.ToString("dd/MM/yyyy"));
CommonFunction.SetValueExportByString(ref flcReport, "Contract_ToDate", _Fees_Revenue_Info.Contract_ToDate.ToString("dd/MM/yyyy"));
//decimal _VAT_Fee = Math.Round(_Fees_Revenue_Info.Fee_Expected * 10 / 100);
//decimal _Fee = _Fees_Revenue_Info.Fee_Expected - _VAT_Fee;
//decimal _p = 110 / 100;
//decimal _Fee = _Fees_Revenue_Info.Fee_Expected / _p;
//decimal _VAT_Fee = Math.Round(_Fee * 10 / 100);
decimal _Fee = (_Fees_Revenue_Info.Fee_Expected / 110) * 100;
decimal _VAT_Fee = Math.Round(_Fee * 10 / 100);
CommonFunction.SetValueExportByString(ref flcReport, "Fee", _Fee.ToString("###,##0"));
CommonFunction.SetValueExportByString(ref flcReport, "VAT_Fee", _VAT_Fee.ToString("###,##0"));
CommonFunction.SetValueExportByString(ref flcReport, "Total_Fee", _Fees_Revenue_Info.Fee_Expected.ToString("###,##0"));
if (_Fees_Revenue_Info.Contract_Type == (decimal)Enum_Contract_Type.Tenant)
{
#region Kỳ thanh toán
string _Kytt = "Thanh toán phí MG lần " + _Fees_Revenue_Info.Number_Payment
+ " từ ngày " + _Fees_Revenue_Info.HanChuyenTien.ToString("dd/MM/yyyy") + " đến ngày " +
_Fees_Revenue_Info.HanChuyenTien.AddDays(30).ToString("dd/MM/yyyy");
if (_Fees_Revenue_Info.Term == (decimal)Enum_Fee_Maturity.One)
{
_Kytt = "Thanh toán phí MG lần " + _Fees_Revenue_Info.Number_Payment
+ " từ ngày " + _Fees_Revenue_Info.HanChuyenTien.ToString("dd/MM/yyyy") + " đến ngày " +
_Fees_Revenue_Info.HanChuyenTien.AddDays(30).ToString("dd/MM/yyyy");
}
else if (_Fees_Revenue_Info.Term == (decimal)Enum_Fee_Maturity.One_Month)
{
_Kytt = "Thanh toán phí MG lần " + _Fees_Revenue_Info.Number_Payment
+ " từ ngày " + _Fees_Revenue_Info.HanChuyenTien.ToString("dd/MM/yyyy") + " đến ngày " +
_Fees_Revenue_Info.HanChuyenTien.AddMonths(1).ToString("dd/MM/yyyy");
}
else if (_Fees_Revenue_Info.Term == (decimal)Enum_Fee_Maturity.Three_Month)
{
_Kytt = "Thanh toán phí MG lần " + _Fees_Revenue_Info.Number_Payment
+ " từ ngày " + _Fees_Revenue_Info.HanChuyenTien.ToString("dd/MM/yyyy") + " đến ngày " +
_Fees_Revenue_Info.HanChuyenTien.AddMonths(3).ToString("dd/MM/yyyy");
}
else if (_Fees_Revenue_Info.Term == (decimal)Enum_Fee_Maturity.Six_Month)
{
_Kytt = "Thanh toán phí MG lần " + _Fees_Revenue_Info.Number_Payment
+ " từ ngày " + _Fees_Revenue_Info.HanChuyenTien.ToString("dd/MM/yyyy") + " đến ngày " +
_Fees_Revenue_Info.HanChuyenTien.AddMonths(6).ToString("dd/MM/yyyy");
}
else if (_Fees_Revenue_Info.Term == (decimal)Enum_Fee_Maturity.One_Year)
{
_Kytt = "Thanh toán phí MG lần " + _Fees_Revenue_Info.Number_Payment
+ " từ ngày " + _Fees_Revenue_Info.HanChuyenTien.ToString("dd/MM/yyyy") + " đến ngày " +
_Fees_Revenue_Info.HanChuyenTien.AddYears(1).ToString("dd/MM/yyyy");
}
CommonFunction.SetValueExportByString(ref flcReport, "KyThanhToan", _Kytt);
#endregion
}
else
{
CommonFunction.SetValueExportByString(ref flcReport, "KyThanhToan", "");
}
DateTime _dt_from = DateTime.Now;
DateTime _dt_To = _dt_from.AddDays(30);
CommonFunction.SetValueExportByString(ref flcReport, "From", _dt_from.ToString("dd/MM/yyyy"));
CommonFunction.SetValueExportByString(ref flcReport, "To", _dt_To.ToString("dd/MM/yyyy"));
//CommonFunction.SetValueExportByString(ref flcReport, "From", _Fees_Revenue_Info.HanChuyenTien.ToString("dd/MM/yyyy"));
//CommonFunction.SetValueExportByString(ref flcReport, "To", _Fees_Revenue_Info.HanChuyenTien.AddDays(30).ToString("dd/MM/yyyy"));
if (_Fees_Revenue_Info.Price != 0)
{
CommonFunction.SetValueExportByString(ref flcReport, "Price", _Fees_Revenue_Info.Price.ToString("###,##0"));
}
else
{
CommonFunction.SetValueExportByString(ref flcReport, "Price", "");
}
string _tienBangChu = ConvertData.ConvertMoneyToStr(Convert.ToDouble(_Fees_Revenue_Info.Fee_Expected));
if (_Fees_Revenue_Info.Currency == (decimal)Enum_Contract_Currency.USD)
{
_tienBangChu = c_Contract_Controller.Convert_Dola(_Fees_Revenue_Info.Fee_Expected);
}
if (_Fees_Revenue_Info.Currency == (decimal)Enum_Contract_Currency.USD)
{
CommonFunction.SetValueExportByString(ref flcReport, "VND", _Fees_Revenue_Info.Fee_Vnd.ToString("###,##0"));
}
CommonFunction.SetValueExportByString(ref flcReport, "BangChu", _tienBangChu.ToLower());
System.Windows.Forms.SaveFileDialog saveReport = new System.Windows.Forms.SaveFileDialog();
saveReport.Filter = "Excel files (*.xls)|*.xls";
if (saveReport.ShowDialog() == System.Windows.Forms.DialogResult.OK)
{
CommonFunction.ExportExcel(flcReport, _path, saveReport);
}
#endregion
}
catch (Exception ex)
{
ErrorLog.log.Error(ex.ToString());
}
}
void Export()
{
try
{
#region Kết xuất
FlexCel.Report.FlexCelReport flcReport = new FlexCel.Report.FlexCelReport();
string _path = CommonData.ExcelDesignFilePath;
string _fileExcelName = "Contract_Report.xls";
DataSet ds_all = new DataSet();
DataTable _dt = ConvertData.ConvertToDatatable(c_lst);
_dt.Columns.Add("STT");
int index = 1;
foreach (DataRow item in _dt.Rows)
{
item["STT"] = index;
index++;
}
ds_all.Tables.Add(_dt);
ds_all.Tables[0].TableName = "Contract";
if (ds_all.Tables.Count <= 0)
{
Mouse.OverrideCursor = null;
NoteBox.Show("Không có dữ liệu để kết xuất", "Thông báo", NoteBoxLevel.Note);
return;
}
if (ds_all.Tables[0].Rows.Count <= 0)
{
Mouse.OverrideCursor = null;
NoteBox.Show("Không có dữ liệu để kết xuất", "Thông báo", NoteBoxLevel.Note);
return;
}
_path += _fileExcelName;
string _title_report = "";
string _status = cboStatus.SelectedValue.ToString();
if (_status == ((decimal)Enum_Contract_Status.Het_Han).ToString())
{
_title_report = "BÁO CÁO HỢP ĐỒNG HẾT HẠN";
}
else if (_status == ((decimal)Enum_Contract_Status.Gia_Han).ToString())
{
_title_report = "BÁO CÁO HỢP ĐỒNG RA HẠN";
}
else if (_status == ((decimal)Enum_Contract_Status.Den_Han_Thong_Bao).ToString())
{
_title_report = "BÁO CÁO HỢP ĐỒNG ĐẾN HẠN THÔNG BÁO";
}
else if (_status == ((decimal)Enum_Contract_Status.Dong_Trc_Thoi_Han).ToString())
{
_title_report = "BÁO CÁO HỢP ĐỒNG ĐÓNG TRƯỚC THỜI HẠN";
}
else
{
_title_report = "BÁO CÁO HỢP ĐỒNG";
}
CommonFunction.SetValueExportByDataTable(ref flcReport, ds_all);
CommonFunction.SetValueExportByString(ref flcReport, "title_report", _title_report);
System.Windows.Forms.SaveFileDialog saveReport = new System.Windows.Forms.SaveFileDialog();
saveReport.Filter = "Excel files (*.xls)|*.xls";
if (saveReport.ShowDialog() == System.Windows.Forms.DialogResult.OK)
{
CommonFunction.ExportExcel(flcReport, _path, saveReport);
}
#endregion
}
catch (Exception ex)
{
ErrorLog.log.Error(ex.ToString());
}
}