public ExcelFile CreateReport(String path, String NamLamViec, String ThangLamViec, String TrangThai)
{
XlsFile Result = new XlsFile(true);
Result.Open(path);
String NgayThangNam = "";
FlexCelReport fr = new FlexCelReport();
fr = ReportModels.LayThongTinChuKy(fr, "rptKeToan_BangCanDoiSoDu");
LoadData(fr, NamLamViec, ThangLamViec, TrangThai);
NgayThangNam = ReportModels.Ngay_Thang_Nam_HienTai();
String ThangNam = "Tháng "+ThangLamViec+" năm " + NamLamViec;
String NamTruoc = "Số năm " + (Convert.ToInt16(NamLamViec) - 1) + " chuyển sang";
String NamNay = "Số trong năm " + NamLamViec;
String NamSau = "Số trong năm " + (Convert.ToInt16(NamLamViec) + 1);
String QuanKhu = ReportModels.CauHinhTenDonViSuDung(2);
String BoQuocPhong = ReportModels.CauHinhTenDonViSuDung(1);
fr.SetValue("NgayThangNam", NgayThangNam);
fr.SetValue("ThangNam", ThangNam);
fr.SetValue("QuanKhu", QuanKhu);
fr.SetValue("BoQuocPhong", BoQuocPhong);
fr.SetValue("NamTruoc", NamTruoc);
fr.SetValue("NamNay", NamNay);
fr.SetValue("NamSau", NamSau);
fr.Run(Result);
return Result;
}
public WebConnectThread(FlexCelReport aReportStart, string aCityName, string aDataPath, bool aUseOfflineData)
{
CityName = aCityName;
DataPath = aDataPath;
UseOfflineData = aUseOfflineData;
ReportStart = aReportStart;
}
public ExcelFile CreateReport(String path, String iID_MaChungTu, String iID_MaDonVi, String iThang, String iNam, String iID_MaThongTri, String sSoCT, String iLoai)
{
Thang = iThang + "/" + iNam;
XlsFile Result = new XlsFile(true);
Result.Open(path);
FlexCelReport fr = new FlexCelReport();
fr = ReportModels.LayThongTinChuKy(fr, "rptThuNopThongTri");
LoadData(fr, iID_MaChungTu, iID_MaDonVi, sSoCT, iLoai);
////lay ten thong tri
if (!String.IsNullOrEmpty(iID_MaThongTri))
{
DataTable dtLoaiThongTri;
String SQL = "SELECT sLoaiThongTri,sTenLoaiNS FROM KT_LoaiThongTri WHERE iID_MaThongTri=@iID_MaThongTri";
SqlCommand cmd = new SqlCommand(SQL);
cmd.Parameters.AddWithValue("@iID_MaThongTri", iID_MaThongTri);
dtLoaiThongTri = Connection.GetDataTable(cmd);
cmd.Dispose();
if (dtLoaiThongTri != null && dtLoaiThongTri.Rows.Count > 0)
{
LoaiTT = Convert.ToString(dtLoaiThongTri.Rows[0]["sLoaiThongTri"]);
LoaiNS = Convert.ToString(dtLoaiThongTri.Rows[0]["sTenLoaiNS"]);
dtLoaiThongTri.Dispose();
}
}
else
{
LoaiTT = "Thu nộp Ngân sách";
LoaiNS = "Ngân sách...";
}
String NoiDung = "", sNgayLap = "", Nam = "", dNgayChungTu="";
DataTable dt = ThuNop_ChungTuModels.GetChungTu(iID_MaChungTu);
if (dt.Rows.Count > 0)
{
NoiDung = dt.Rows[0]["sNoiDung"].ToString();
dNgayChungTu = dt.Rows[0]["dNgayChungTu"].ToString();
Nam = dt.Rows[0]["iNamLamViec"].ToString();
dt.Dispose();
}
if (!String.IsNullOrEmpty(dNgayChungTu))
{
sNgayLap = " năm " + Nam;
}
fr.SetValue("NgayThangNam", "Ngày "+dNgayChungTu.Substring(0, 2)+ " tháng " + dNgayChungTu.Substring(3, 2) + " năm " + dNgayChungTu.Substring(6, 4));
fr.SetValue("NoiDung", NoiDung);
fr.SetValue("Loai", LoaiTT);
fr.SetValue("DonVi", DonViModels.Get_TenDonVi(iID_MaDonVi));
fr.SetValue("dNgayLap", sNgayLap);
fr.SetValue("LNS", LoaiNS);
fr.SetValue("sL", "");
fr.SetValue("sK", "");
fr.SetValue("Nam", Nam);
fr.SetValue("BoQuocPhong", ReportModels.CauHinhTenDonViSuDung(1).ToUpper());
fr.SetValue("QuanKhu", ReportModels.CauHinhTenDonViSuDung(2).ToUpper());
fr.Run(Result);
return Result;
}
public void AutoRun()
{
using (FlexCelReport ordersReport = SharedData.CreateReport())
{
ordersReport.SetValue("Date", DateTime.Now);
ordersReport.SetValue("ReportCaption", "Sales by year and country");
using (DataSet ds = new DataSet())
{
SharedData.Fill(ds, @"SELECT Employees.Country, SUM([Order Details].UnitPrice * [Order Details].Quantity) AS Sales, COUNT([Order Details].Quantity) AS OrderCount, DatePart(yyyy, Orders.OrderDate) AS SaleYear, DatePart(q, Orders.OrderDate) AS Quarter FROM ((Employees INNER JOIN Orders ON Employees.EmployeeID = Orders.EmployeeID) INNER JOIN [Order Details] ON Orders.OrderID = [Order Details].OrderID) GROUP BY Employees.Country, DatePart(yyyy, Orders.OrderDate), DatePart(q, Orders.OrderDate)", "Data");
ordersReport.AddTable(ds);
string DataPath = Path.Combine(Path.Combine(Path.GetDirectoryName(Assembly.GetExecutingAssembly().Location), ".."), "..") + Path.DirectorySeparatorChar;
if (saveFileDialog1.ShowDialog() == DialogResult.OK)
{
ordersReport.Run(DataPath + "Master Detail on one Table.template.xls", saveFileDialog1.FileName);
if (MessageBox.Show("Do you want to open the generated file?", "Confirm", MessageBoxButtons.YesNo) == DialogResult.Yes)
{
Process.Start(saveFileDialog1.FileName);
}
}
}
}
}
public ExcelFile CreateReport(String path, String iID_MaTrangThaiDuyet, String Thang_Quy, String LoaiThang_Quy)
{
String MaND = User.Identity.Name;
DataTable dt = NguoiDungCauHinhModels.LayCauHinh(MaND);
String DK = "", iNamLamViec = DateTime.Now.Year.ToString(), iID_MaNamNganSach = "1", iID_MaNguonNganSach = "1";
if (dt.Rows.Count > 0)
{
iNamLamViec = Convert.ToString(dt.Rows[0]["iNamLamViec"]);
iID_MaNamNganSach = Convert.ToString(dt.Rows[0]["iID_MaNamNganSach"]);
iID_MaNguonNganSach = Convert.ToString(dt.Rows[0]["iID_MaNguonNganSach"]);
}
XlsFile Result = new XlsFile(true);
Result.Open(path);
String Thang = "tháng";
if (LoaiThang_Quy == "1")
{
Thang = "quý";
}
FlexCelReport fr = new FlexCelReport();
fr = ReportModels.LayThongTinChuKy(fr, "rptCapPhat_TongHopChiTieuCapNganSach_81");
String NgayThangNam = ReportModels.Ngay_Thang_Nam_HienTai();
String QuanKhu = ReportModels.CauHinhTenDonViSuDung(2);
String BoQuocPhong = ReportModels.CauHinhTenDonViSuDung(1);
LoadData(fr, iID_MaTrangThaiDuyet, Thang_Quy, LoaiThang_Quy,MaND);
fr.SetValue("Nam", iNamLamViec);
fr.SetValue("Thang", Thang);
fr.SetValue("Thang_Quy", Thang_Quy);
fr.SetValue("QuanKhu", QuanKhu);
fr.SetValue("BoQuocPhong", BoQuocPhong);
fr.SetValue("NgayThangNam", NgayThangNam);
fr.Run(Result);
fr.Dispose();
return Result;
}
/// <summary>
/// hàm khởi tạo báo cáo
/// </summary>
/// <param name="path"></param>
/// <param name="NamLamViec"></param>
/// <returns></returns>
public ExcelFile CreateReport(String path, String iNamLamViec, String iQuy, String iID_MaLoaiDoanhNghiep)
{
XlsFile Result = new XlsFile(true);
Result.Open(path);
String NgayThang = ReportModels.Ngay_Thang_Nam_HienTai();
FlexCelReport fr = new FlexCelReport();
fr = ReportModels.LayThongTinChuKy(fr, "rptTCDN_BaoCaoTongHop");
LoadData(fr, iNamLamViec,iQuy,iID_MaLoaiDoanhNghiep);
String Thang = "";
if (iQuy == "1")
Thang = "3";
else if (iQuy == "2")
{
Thang = "6";
}
else if (iQuy == "3")
{
Thang = "9";
}
else
{
Thang = "cả năm";
}
fr.SetValue("Thang", Thang);
fr.SetValue("Ngay", NgayThang);
fr.SetValue("Nam", iNamLamViec);
fr.SetValue("NamTruoc", Convert.ToInt16(iNamLamViec)-1);
fr.Run(Result);
return Result;
}
public ExcelFile CreateReport(String path,String iID_MaChungTu)
{
XlsFile Result = new XlsFile(true);
Result.Open(path);
String BoQuocPhong = ReportModels.CauHinhTenDonViSuDung(1);
String CucTaiChinh = ReportModels.CauHinhTenDonViSuDung(2);
FlexCelReport fr = new FlexCelReport();
String ngay = ReportModels.Ngay_Thang_Nam_HienTai();
DateTime dt = new System.DateTime(DateTime.Now.Year, DateTime.Now.Month, DateTime.Now.Day);
LoadData(fr,iID_MaChungTu);
fr = ReportModels.LayThongTinChuKy(fr, "rptKTKB_ThongTriTongHop");
//fr.SetValue("Nam", iNamLamViec);
fr.SetValue("So", iID_MaChungTu);
//fr.SetValue("Thang", iThang);
fr.SetValue("Ngay", String.Format("{0:dd}", dt));
fr.SetValue("Thangs", String.Format("{0:MM}", dt));
fr.SetValue("Nams", DateTime.Now.Year);
fr.SetValue("BoQuocPhong", BoQuocPhong);
fr.SetValue("CucTaiChinh", CucTaiChinh);
fr.SetValue("ngay", ngay);
fr.SetValue("ThangCT", CommonFunction.LayTruong("KTKB_ChungTuChiTiet", "iID_MaChungTu", iID_MaChungTu, "iThangCT"));
fr.SetValue("NgayCT", CommonFunction.LayTruong("KTKB_ChungTuChiTiet", "iID_MaChungTu", iID_MaChungTu, "iNgayCT"));
fr.Run(Result);
return Result;
}
/// <summary>
/// Khởi tạo báo cáo
/// </summary>
/// <param name="path"> đường dẫn</param>
/// <param name="NamLamViec">Năm làm việc</param>
/// <param name="iID_MaDonVi">Mã đơn vị</param>
/// <param name="DotPhanBo">Mã đợt phân bổ</param>
/// <returns></returns>
public ExcelFile CreateReport(String path, String iID_MaDonVi, String DotPhanBo, String iID_MaTrangThaiDuyet)
{
String MaND = User.Identity.Name;
XlsFile Result = new XlsFile(true);
Result.Open(path);
String TenDV = "";
DataTable dtDonVi = PhanBo_ReportModels.DanhSachDonVi2(MaND,iID_MaTrangThaiDuyet, DotPhanBo);
for (int i = 0; i < dtDonVi.Rows.Count;i++ )
{
if (iID_MaDonVi == dtDonVi.Rows[i]["iID_MaDonVi"].ToString())
{
TenDV = dtDonVi.Rows[i]["TenHT"].ToString();
break;
}
}
String NgayThangNam = ReportModels.Ngay_Thang_Nam_HienTai();
String BoQuocPhong = ReportModels.CauHinhTenDonViSuDung(1);
String QuanKhu = ReportModels.CauHinhTenDonViSuDung(2);
String Dot = ReportModels.Get_STTDotPhanBo(MaND, iID_MaTrangThaiDuyet, DotPhanBo).ToString();
//set các thông số
FlexCelReport fr = new FlexCelReport();
fr = ReportModels.LayThongTinChuKy(fr, "rptThongBaoBoSungNganSach");
LoadData(fr, MaND, iID_MaTrangThaiDuyet, iID_MaDonVi, DotPhanBo);
fr.SetValue("Nam", ReportModels.LayNamLamViec(MaND));
fr.SetValue("TenDv", TenDV);
fr.SetValue("NgayThangNam", NgayThangNam);
fr.SetValue("BoQuocPhong", BoQuocPhong);
fr.SetValue("QuanKhu", QuanKhu);
fr.SetValue("Dot", Dot);
fr.SetValue("Tien", CommonFunction.TienRaChu(Tong));
fr.Run(Result);
return Result;
}
public void AutoRun()
{
using (FlexCelReport ordersReport = SharedData.CreateReport())
{
ordersReport.GetInclude += new GetIncludeEventHandler(ordersReport_GetInclude);
ordersReport.SetValue("Date", DateTime.Now);
if (saveFileDialog1.ShowDialog() == DialogResult.OK)
{
Assembly a = Assembly.GetExecutingAssembly();
using (Stream InStream = a.GetManifestResourceStream("TemplatesOnTheExe.Templates.Templates On The Exe.template.xls"))
{
using (FileStream OutStream = new FileStream(saveFileDialog1.FileName, FileMode.Create))
{
ordersReport.Run(InStream, OutStream);
}
}
if (MessageBox.Show("Do you want to open the generated file?", "Confirm", MessageBoxButtons.YesNo) == DialogResult.Yes)
{
Process.Start(saveFileDialog1.FileName);
}
}
}
}
public void AutoRun()
{
string DataPath = Path.Combine(Path.Combine(Path.GetDirectoryName(Assembly.GetExecutingAssembly().Location), ".."), "..") + Path.DirectorySeparatorChar;
if (saveFileDialog1.ShowDialog() == DialogResult.OK)
{
object[][] SimpleData = LoadDataSet(Path.Combine(DataPath, "Countries.txt"));
SimpleVirtualArrayDataSource SimpleTable = new SimpleVirtualArrayDataSource(null, SimpleData, new string[] { "Rank", "Country", "Area", "Date" }, "SimpleTable");
using (FlexCelReport genericReport = new FlexCelReport(true))
{
genericReport.AddTable("SimpleData", SimpleTable);
object[][] Complex1 = LoadDataSet(Path.Combine(DataPath, "Countries.txt"));
ComplexVirtualArrayDataSource ComplexAreas = new ComplexVirtualArrayDataSource(null, Complex1, new string[] { "Rank", "Country", "Area", "Date" }, "ComplexAreas");
object[][] Complex2 = LoadDataSet(Path.Combine(DataPath, "Populations.txt"));
ComplexVirtualArrayDataSource ComplexPopulations = new ComplexVirtualArrayDataSource(null, Complex2, new string[] { "Rank", "Country", "Population", "Date" }, "ComplexPopulations");
genericReport.AddTable("ComplexAreas", ComplexAreas, TDisposeMode.DisposeAfterRun);
genericReport.AddTable("ComplexPopulations", ComplexPopulations, TDisposeMode.DisposeAfterRun);
genericReport.Run(Path.Combine(DataPath, "Virtual Datasets.template.xls"), saveFileDialog1.FileName);
}
if (MessageBox.Show("Do you want to open the generated file?", "Confirm", MessageBoxButtons.YesNo) == DialogResult.Yes)
{
Process.Start(saveFileDialog1.FileName);
}
}
}
public FlexCelReport CreateAndConfigureReport()
{
var report = new FlexCelReport(true);
report.SetValue("TaxType", PurchaseOrder.TaxType);
report.SetValue("PoNumber", PurchaseOrder.PoNumber);
report.SetValue("InvoiceNumber", PurchaseOrder.InvoiceNumber);
report.SetValue("Requestor", PurchaseOrder.Requestor);
var fpo = PurchaseOrder as FreightPurchaseOrder;
if (fpo != null)
{
report.AddTable("States", fpo.States);
report.SetValue("SubTotal", fpo.LineAmountSubtotal);
report.SetValue("TaxTotal", fpo.TaxAmountSubtotal);
}
var ppo = PurchaseOrder as ProductPurchaseOrder;
if (ppo == null)
{
return(report);
}
report.AddTable("States", ppo.States);
report.SetValue("PickPackTotal", ppo.PickPackCharge);
report.SetValue("BoxTotal", ppo.BoxCharge);
return(report);
}
internal static FlexCel.Report.FlexCelReport CreateReport()
{
FlexCelReport Result = new FlexCelReport(true);
Result.AddTable(GetDataTable());
return(Result);
}
public ExcelFile CreateReport(String path, String iID_MaTaiKhoan)
{
XlsFile Result = new XlsFile(true);
Result.Open(path);
String NgayThangNam = "";
FlexCelReport fr = new FlexCelReport();
fr = ReportModels.LayThongTinChuKy(fr, "rptKeToanTongHop_InTaiKhoanChiTiet");
LoadData(fr, iID_MaTaiKhoan);
NgayThangNam = ReportModels.Ngay_Thang_Nam_HienTai();
String QuanKhu = ReportModels.CauHinhTenDonViSuDung(2);
String BoQuocPhong = ReportModels.CauHinhTenDonViSuDung(1);
fr.SetValue("NgayThangNam", NgayThangNam);
fr.SetValue("QuanKhu", QuanKhu);
fr.SetValue("BoQuocPhong", BoQuocPhong);
fr.SetValue("Ngay", DateTime.Now.Day);
fr.SetValue("Thang", DateTime.Now.Month);
fr.SetValue("Nam", DateTime.Now.Year);
if (iID_MaTaiKhoan == Guid.Empty.ToString())
{
fr.SetValue("TaiKhoan", "");
}
else
{
fr.SetValue("TaiKhoan", iID_MaTaiKhoan);
}
fr.Run(Result);
return Result;
}
/// <summary>
/// Tạo file PDF xuất dữ liệu
/// </summary>
/// <param name="path"></param>
/// <param name="MaND"></param>
/// <param name="sLNS"></param>
/// <param name="iThang_Quy"></param>
/// <param name="iID_MaDonVi"></param>
/// <param name="iID_MaPhongBan"></param>
/// <returns></returns>
public ExcelFile CreateReport(String path, String MaND, String sLNS, String iThang_Quy, String iID_MaDonVi, String iID_MaPhongBan)
{
XlsFile Result = new XlsFile(true);
Result.Open(path);
FlexCelReport fr = new FlexCelReport();
fr = ReportModels.LayThongTinChuKy(fr, "rptQuyetToan_PhongBan");
LoadData(fr, MaND, sLNS, iThang_Quy, iID_MaDonVi, iID_MaPhongBan);
String Nam = ReportModels.LayNamLamViec(MaND);
if (iThang_Quy == "-1")
{
iThang_Quy = "Tất cả các Quý ";
}
if (iThang_Quy == "5")
{
iThang_Quy = "Bổ Sung ";
}
fr.SetValue("NgayThang", ReportModels.Ngay_Thang_Nam_HienTai());
fr.SetValue("Nam", Nam);
fr.SetValue("Quy", iThang_Quy);
fr.Run(Result);
return Result;
}
public void AutoRun()
{
using (FlexCelReport ordersReport = new FlexCelReport(true))
{
string DataPath = Path.Combine(Path.Combine(Path.GetDirectoryName(Assembly.GetExecutingAssembly().Location), ".."), "..") + Path.DirectorySeparatorChar;
ordersReport.SetValue("Date", DateTime.Now);
using (northwndEntities Northwind = new northwndEntities())
{
ordersReport.AddTable("Categories", Northwind.Categories);
ordersReport.AddTable("Products", Northwind.Products);
if (saveFileDialog1.ShowDialog() == DialogResult.OK)
{
TransactionOptions transactionOptions = new TransactionOptions();
transactionOptions.IsolationLevel = System.Transactions.IsolationLevel.Serializable; //it would be better to sue Snapshot here, but it isn't supported by SQL Sever CE
using (TransactionScope transactionScope = new TransactionScope(TransactionScopeOption.Required, transactionOptions))
{
ordersReport.Run(DataPath + "Entity Framework.template.xls", saveFileDialog1.FileName);
transactionScope.Complete();
}
if (MessageBox.Show("Do you want to open the generated file?", "Confirm", MessageBoxButtons.YesNo) == DialogResult.Yes)
{
Process.Start(saveFileDialog1.FileName);
}
}
}
}
}
public ExcelFile CreateReport(String path, String NamLamViec, String TuThang, String DenThang, String TrangThai)
{
XlsFile Result = new XlsFile(true);
Result.Open(path);
String NgayThangNam = "";
FlexCelReport fr = new FlexCelReport();
fr = ReportModels.LayThongTinChuKy(fr, "rptKeToan_SoCaiTaiKhoan");
LoadData(fr, NamLamViec, TuThang, DenThang, TrangThai);
NgayThangNam = ReportModels.Ngay_Thang_Nam_HienTai();
String ThangNam = "Từ tháng " + TuThang + " đến tháng " + DenThang + " Năm " + NamLamViec;
if (TuThang == DenThang)
ThangNam = "Tháng " + TuThang + " Năm " + NamLamViec;
String QuanKhu = ReportModels.CauHinhTenDonViSuDung(2);
String BoQuocPhong = ReportModels.CauHinhTenDonViSuDung(1);
fr.SetValue("NgayThangNam", NgayThangNam);
fr.SetValue("ThangNam", ThangNam);
fr.SetValue("QuanKhu", QuanKhu);
fr.SetValue("BoQuocPhong", BoQuocPhong);
fr.SetValue("Ngay", DateTime.Now.Day);
fr.SetValue("Thang", DateTime.Now.Month);
fr.SetValue("Nam", DateTime.Now.Year);
fr.Run(Result);
return Result;
}
public override ReportRunOutcome Run(IDbConnectionFactory connectionFactory, XlsFile excelFile, StringBuilder stringOutput)
{
if (excelFile == null)
{
throw new ArgumentNullException("excelFile");
}
using (var dataSet = GetDataSet(connectionFactory))
{
var orgUnit = dataSet.Tables["OrgUnit"];
var jobAd = dataSet.Tables["JobAd"];
if (jobAd.Rows.Count == 0)
{
return(ReportRunOutcome.NoResults); // No point in generating a report when there are no job ads.
}
using (var flexCelReport = new FlexCelReport(true))
{
SetProperties(flexCelReport);
SetCreditReportProperties(flexCelReport);
SetJobAdReportProperties(flexCelReport);
flexCelReport.SetValue("ShowGrandTotal", orgUnit.Rows.Count > 1);
flexCelReport.AddTable("OrgUnit", orgUnit);
flexCelReport.AddTable("JobAd", jobAd);
flexCelReport.Run(excelFile);
}
return(ReportRunOutcome.FileResult);
}
}
public override ReportRunOutcome Run(IDbConnectionFactory connectionFactory, XlsFile excelFile, StringBuilder stringOutput)
{
using (var dataSet = GetDataSet(connectionFactory))
{
var orgUnit = dataSet.Tables["OrgUnit"];
var user = dataSet.Tables["User"];
var activeUser = new DataView(user, ActiveUserCondition, SortUsersBy, DataViewRowState.CurrentRows);
var inactiveUser = new DataView(user, "NOT " + ActiveUserCondition, SortUsersBy, DataViewRowState.CurrentRows);
var haveActivity = (user.Select(HaveActivityCondition).Length > 0);
using (var flexCelReport = new FlexCelReport(true))
{
SetProperties(flexCelReport);
SetCreditReportProperties(flexCelReport);
flexCelReport.SetValue("ShowGrandTotal", orgUnit.Rows.Count > 1);
flexCelReport.AddTable("OrgUnit", orgUnit);
flexCelReport.AddTable("ActiveUser", activeUser);
flexCelReport.AddTable("InactiveUser", inactiveUser);
flexCelReport.Run(excelFile);
}
return(haveActivity ? ReportRunOutcome.FileResult : ReportRunOutcome.NoResults);
}
}
public ExcelFile CreateReport(String path, String iNamLamViec, String iID_MaChungTu, String sSoChungTuChiTiet, String iID_MaNamNganSach, String iID_MaNguonNganSach)
{
XlsFile Result = new XlsFile(true);
Result.Open(path);
FlexCelReport fr = new FlexCelReport();
String BoQuocPhong = ReportModels.CauHinhTenDonViSuDung(1);
String CucTaiChinh = ReportModels.CauHinhTenDonViSuDung(2);
String ngay = ReportModels.Ngay_Thang_Nam_HienTai();
DateTime dt = new System.DateTime(DateTime.Now.Year, DateTime.Now.Month, DateTime.Now.Day);
LoadData(fr, iNamLamViec, iID_MaChungTu, sSoChungTuChiTiet, iID_MaNamNganSach, iID_MaNguonNganSach);
//Lấy thông tin đơn vị nhận
DataTable dtDVN = dtDanhSachDonVi_Nhan(iNamLamViec, "", iID_MaChungTu);
String DonVi = "";
if (dtDVN.Rows.Count > 0)
{
DonVi=dtDVN.Rows[0]["sTenDonVi_Nhan"].ToString();
}
fr = ReportModels.LayThongTinChuKy(fr, "rptThongTriLoaiNS");
fr.SetValue("Nam", iNamLamViec);
fr.SetValue("So", sSoChungTuChiTiet);
fr.SetValue("Thang", String.Format("{0:MM}", dt));
fr.SetValue("Ngay", ngay);
fr.SetValue("Ngay", String.Format("{0:dd}", dt));
fr.SetValue("Thangs", String.Format("{0:MM}", dt));
fr.SetValue("Nams", DateTime.Now.Year);
fr.SetValue("ThangCT", CommonFunction.LayTruong("KTKB_ChungTuChiTiet", "sSoChungTuChiTiet", sSoChungTuChiTiet, "iThangCT"));
fr.SetValue("DonVi", DonVi);
//fr.SetValue("LoaiThangQuy", TenLoaiThangQuy);
fr.SetValue("CucTaiChinh", CucTaiChinh);
fr.SetValue("BoQuocPhong", BoQuocPhong);
fr.SetValue("Tien", CommonFunction.TienRaChu(tong));
fr.Run(Result);
return Result;
}
/// <summary>
/// tạo báo cáo
/// </summary>
/// <param name="path"></param>
/// <param name="NgoaiTe"></param>
/// <param name="Quy"></param>
/// <param name="Nam"></param>
/// <returns></returns>
public ExcelFile CreateReport(String path, String sDeAn, String NgoaiTe, String Quy, String iNamLamViec, String iCapTongHop)
{
XlsFile Result = new XlsFile(true);
Result.Open(path);
String BoQuocPhong = ReportModels.CauHinhTenDonViSuDung(1);
String QuanKhu = ReportModels.CauHinhTenDonViSuDung(2);
DataTable dtDVT = QLDA_ReportModel.getdtTien();
String DVT = " triệu đồng";
for (int i = 1; i < dtDVT.Rows.Count; i++)
{
if (NgoaiTe == dtDVT.Rows[i]["iID_MaNgoaiTe"].ToString())
{
DVT = dtDVT.Rows[i]["sTen"].ToString();
}
}
dtDVT.Dispose();
//set các thông số
FlexCelReport fr = new FlexCelReport();
//Toan lục luong
fr = ReportModels.LayThongTinChuKy(fr, "rptKHV_02DT_DTQuy");
LoadData(fr, sDeAn, NgoaiTe, Quy, iNamLamViec);
fr.SetValue("Nam", iNamLamViec);
fr.SetValue("Quy", Quy);
fr.SetValue("Ngay", "Ngày " + DateTime.Now.Day.ToString() + " tháng " + DateTime.Now.Month.ToString() + " năm " + DateTime.Now.Year.ToString());
fr.SetValue("DonVi", DVT);
fr.SetValue("BoQuocPhong", BoQuocPhong);
fr.SetValue("QuanKhu", QuanKhu);
fr.Run(Result);
return Result;
}
/// <summary>
/// Tạo file PDF xuất dữ liệu của quyết toán từng đơn vị
/// </summary>
/// <param name="path"></param>
/// <param name="MaND">Mã người dùng</param>
/// <param name="sLNS">Loại ngân sách</param>
/// <param name="iThang_Quy">Quý</param>
/// <param name="iID_MaDonVi">Mã đơn vị</param>
/// <param name="iID_MaNamNganSach">Năm ngân sách</param>
/// <param name="MaPhongBan">Mã phòng ban</param>
/// <returns></returns>
public ExcelFile CreateReport(String path, String MaND, String sLNS, String iThang_Quy, String iID_MaDonVi, String iID_MaNamNganSach, String MaPhongBan)
{
XlsFile Result = new XlsFile(true);
Result.Open(path);
FlexCelReport fr = new FlexCelReport();
fr = ReportModels.LayThongTinChuKy(fr, "rptQuyetToan_DonVi_LNS");
LoadData(fr, MaND, sLNS, iThang_Quy, iID_MaDonVi, iID_MaNamNganSach, MaPhongBan);
String Nam = ReportModels.LayNamLamViec(MaND);
String NamNganSach = "";
if (iID_MaNamNganSach == "1")
NamNganSach = "QUYẾT TOÁN NĂM TRƯỚC";
else if (iID_MaNamNganSach == "2")
NamNganSach = "QUYẾT TOÁN NĂM NAY";
else
{
NamNganSach = "TỔNG HỢP";
}
String sTenDonVi = DonViModels.Get_TenDonVi(iID_MaDonVi);
fr.SetValue("BoQuocPhong", ReportModels.CauHinhTenDonViSuDung(1));
fr.SetValue("QuanKhu", ReportModels.CauHinhTenDonViSuDung(2));
fr.SetValue("NgayThang", ReportModels.Ngay_Thang_Nam_HienTai());
fr.SetValue("Nam", Nam);
fr.SetValue("Quy", iThang_Quy);
fr.SetValue("NamNganSach", NamNganSach);
fr.SetValue("sTenDonVi", sTenDonVi);
fr.Run(Result);
return Result;
}
public void AutoRun()
{
using (FlexCelReport Report = new FlexCelReport(true))
{
TMyData[] Data = new TMyData[1010];
for (int i = 0; i < Data.Length; i++)
{
Data[i] = new TMyData("Customer " + i.ToString());
}
Report.AddTable("data", Data);
Report.SetValue("split", 40);
string DataPath = Path.Combine(Path.Combine(Path.GetDirectoryName(Assembly.GetExecutingAssembly().Location), ".."), "..") + Path.DirectorySeparatorChar;
if (saveFileDialog1.ShowDialog() == DialogResult.OK)
{
Report.Run(DataPath + "Overflow Sheets.template.xlsx", saveFileDialog1.FileName);
if (MessageBox.Show("Do you want to open the generated file?", "Confirm", MessageBoxButtons.YesNo) == DialogResult.Yes)
{
Process.Start(saveFileDialog1.FileName);
}
}
}
}
public ExcelFile CreateReport(String path, String NamLamViec, String ThangLamViec, String iID_MaDonVi, String TrangThai)
{
XlsFile Result = new XlsFile(true);
Result.Open(path);
String NgayThangNam = "";
FlexCelReport fr = new FlexCelReport();
fr = ReportModels.LayThongTinChuKy(fr, "rptKeToan_QuyetToanNam_DonVi");
LoadData(fr, NamLamViec, ThangLamViec, iID_MaDonVi, TrangThai);
String sTenDonVi = Convert.ToString(CommonFunction.LayTruong("NS_DonVi", "iID_MaDonVi", iID_MaDonVi, "sTen"));
NgayThangNam = ReportModels.Ngay_Thang_Nam_HienTai();
String ThangNam ="Tháng "+ ThangLamViec+"/năm " + NamLamViec;
String NamTruoc = "Số năm " + (Convert.ToInt16(NamLamViec) - 1) + " chuyển sang";
String NamNay = "Số trong năm " + NamLamViec;
String NamSau = "Số trong năm " + (Convert.ToInt16(NamLamViec) + 1);
String QuanKhu = ReportModels.CauHinhTenDonViSuDung(2);
String BoQuocPhong = ReportModels.CauHinhTenDonViSuDung(1);
fr.SetValue("NgayThangNam", NgayThangNam);
fr.SetValue("ThangNam", ThangNam);
fr.SetValue("QuanKhu", QuanKhu);
fr.SetValue("BoQuocPhong", BoQuocPhong);
fr.SetValue("NamTruoc", NamTruoc);
fr.SetValue("NamNay", NamNay);
fr.SetValue("NamSau", NamSau);
fr.SetValue("DonVi", sTenDonVi);
fr.SetValue("Ngay", DateTime.Now.Day);
fr.SetValue("Thang", DateTime.Now.Month);
fr.SetValue("Nam", DateTime.Now.Year);
fr.Run(Result);
return Result;
}
/// <summary>
/// Tạo báo cáo
/// </summary>
/// <param name="path">Đường dẫn tới file báo cáo</param>
/// <param name="iTuNgay">Từ ngày</param>
/// <param name="iDenNgay">Đến ngày</param>
/// <param name="iTuThang">Từ tháng</param>
/// <param name="iDenThang">Đến tháng</param>
/// <param name="iNam">Năm</param>
/// <param name="iDVTinh">Đơn vị tính</param>
/// <returns></returns>
public ExcelFile CreateReport(String path, String iTuNgay, String iDenNgay, String iTuThang, String iDenThang, String iNam, String iDVTinh, String iSoTo, String iReport, String UserID, String iID_MaTrangThaiDuyet)
{
XlsFile Result = new XlsFile(true);
Result.Open(path);
FlexCelReport fr = new FlexCelReport();
//Thêm chữ ký vào báo cáo
fr = ReportModels.LayThongTinChuKy(fr, "rptKT_TongHop_RutDuToan");
LoadData(fr, iTuNgay, iDenNgay, iTuThang, iDenThang, iNam, iDVTinh,iSoTo,iReport,UserID,iID_MaTrangThaiDuyet);
fr.SetValue("cap1", ReportModels.CauHinhTenDonViSuDung(1).ToUpper());
fr.SetValue("cap2", ReportModels.CauHinhTenDonViSuDung(2).ToUpper());
fr.SetValue("NgayThang", ReportModels.Ngay_Thang_Nam_HienTai());
DataTable dt = sLNS(GetLNS(iTuNgay, iDenNgay, iTuThang, iDenThang, iNam,UserID,iID_MaTrangThaiDuyet),iSoTo,iReport);
if (dt.Rows.Count > 0)
for (int i = 0; i < dt.Rows.Count; i++)
fr.SetValue("LNS" + (i + 1), dt.Rows[i]["sMoTa"]);
if (iDVTinh.Equals("rD"))
fr.SetValue("DVT", "Đồng");
else if (iDVTinh.Equals("rND"))
fr.SetValue("DVT", "Nghìn đồng");
else if (iDVTinh.Equals("rTrD"))
fr.SetValue("DVT", "Triệu đồng");
fr.SetValue("NgayThangTK", "Từ ngày " + iTuNgay + "/ " + iTuThang + " Đến ngày " + iDenNgay + " /" + iDenThang + "/" + iNam);
if (iReport.Equals("A4Ngang"))
fr.SetValue("SoTo", "Tờ số: "+iSoTo);
fr.Run(Result);
return Result;
}
/// <summary>
/// tạo báo cáo
/// </summary>
/// <param name="path"></param>
/// <param name="Province"></param>
/// <returns></returns>
public ExcelFile CreateReport(String path, string Province)
{
XlsFile Result = new XlsFile(true);
Result.Open(path);
//tính tổng tiền
if (Province == null) Province = "0";
DataTable dt = LayDanhSach(Province);
String BoQuocPhong = ReportModels.CauHinhTenDonViSuDung(1);
String QuanKhu = ReportModels.CauHinhTenDonViSuDung(2);
//set các thông số
FlexCelReport fr = new FlexCelReport();
fr = ReportModels.LayThongTinChuKy(fr, "rptNhanSu_DanhSachNghiHuu");
LoadData(fr, Province);
if (Province != "0" & Province != "")
{
string sProvinceName = Province_Get_Ten(Province);
fr.SetValue("Province", "ĐANG SINH SỐNG TẠI " + sProvinceName.ToUpper());
}
else
fr.SetValue("Province", "");
fr.SetValue("BoQuocPhong", BoQuocPhong);
fr.SetValue("QuanKhu", QuanKhu);
fr.Run(Result);
return Result;
}
internal TFormulaConvertTextWithTagsToInternal(ExcelFile aXls, string aFormulaText, TBand aCurrentBand, FlexCelReport afr, TStackData aStack) :
base(aXls, aXls.ActiveSheet, false, aFormulaText, true)
{
MaxFormulaLen = 0xFFFF;
CurrentBand = aCurrentBand;
fr = afr;
Stack = aStack;
}
public WebConnectThread(FlexCelReport aReportStart, string aCityName, string aDataPath, bool aUseOfflineData, Dictionary <string, LatLong> aCities)
{
CityName = aCityName;
DataPath = aDataPath;
UseOfflineData = aUseOfflineData;
ReportStart = aReportStart;
Cities = aCities;
}
protected void SetProperties(FlexCelReport flexCelReport)
{
flexCelReport.SetValue("ReportedOrgName", _organisation.FullName);
flexCelReport.SetValue("FromDate", _dateRange.Start.Value.ToString(DateFormat));
flexCelReport.SetValue("ToDate", _dateRange.End.Value.ToString(DateFormat));
flexCelReport.SetValue("AccountManagerName", _accountManager.FullName);
flexCelReport.SetValue("AccountManagerPhone", Constants.PhoneNumbers.FreecallText);
flexCelReport.SetValue("AccountManagerEmail", _accountManager.EmailAddress.Address);
}
private void Export(SaveFileDialog SaveDialog, bool ToPdf)
{
using (FlexCelReport reportStart = new FlexCelReport(true))
{
if (cbOffline.Checked && edCity.Text != "london")
{
MessageBox.Show("Offline mode is selected, so we will show the data of london. The actual city you wrote will not be used unless you select online mode.", "Warning");
}
try
{
string DataPath = Path.Combine(Path.Combine(Path.GetDirectoryName(Assembly.GetExecutingAssembly().Location), ".."), "..") + Path.DirectorySeparatorChar;
string OfflineDataPath = Path.Combine(DataPath, "OfflineData") + Path.DirectorySeparatorChar;
//We will use a thread to connect, to avoid "freezing" the GUI
WebConnectThread MyWebConnect = new WebConnectThread(reportStart, edCity.Text, OfflineDataPath, cbOffline.Checked);
Thread WebConnect = new Thread(new ThreadStart(MyWebConnect.LoadData));
WebConnect.Start();
using (ProgressDialog Pg = new ProgressDialog())
{
Pg.ShowProgress(WebConnect);
if (MyWebConnect != null && MyWebConnect.MainException != null)
{
throw MyWebConnect.MainException;
}
}
if (SaveDialog.ShowDialog() == DialogResult.OK)
{
if (ToPdf)
{
XlsFile xls = new XlsFile();
xls.Open(DataPath + "HTML.template.xls");
reportStart.Run(xls);
using (FlexCelPdfExport PdfExport = new FlexCelPdfExport(xls, true))
{
PdfExport.Export(SaveDialog.FileName);
}
}
else
{
reportStart.Run(DataPath + "HTML.template.xls", SaveDialog.FileName);
}
if (MessageBox.Show("Do you want to open the generated file?", "Confirm", MessageBoxButtons.YesNo) == DialogResult.Yes)
{
Process.Start(SaveDialog.FileName);
}
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
}
ExcelFile RunReport(LangDataList langs)
{
ExcelFile Result = new XlsFile(Path.Combine(NSBundle.MainBundle.BundlePath, "report.template.xls"), true);
using (FlexCelReport fr = new FlexCelReport(true))
{
fr.AddTable("lang", langs.items);
fr.Run(Result);
}
return(Result);
}
/// <summary>
/// tạo báo cáo
/// </summary>
/// <param name="path"></param>
/// <param name="NamLamViec"></param>
/// <param name="ThangLamViec"></param>
/// <param name="iID_MaDonVi"></param>
/// <param name="DoTuoi_Tu"> tuoi tu</param>
/// <param name="DoTuoi_Den">tuoi den</param>
/// <returns></returns>
public ExcelFile CreateReport(String path, String DoiTuong, string LoaiIn, string Cap,string Nam, int DoTuoi_Tu, int DoTuoi_Den)
{
XlsFile Result = new XlsFile(true);
Result.Open(path);
//tính tổng tiền
if (Nam == null) Nam = "0";
DataTable dt = PhanTichDuToanNS_52(DoiTuong, LoaiIn,Cap,Nam, DoTuoi_Tu, DoTuoi_Den);
String BoQuocPhong = ReportModels.CauHinhTenDonViSuDung(1);
String QuanKhu = ReportModels.CauHinhTenDonViSuDung(2);
//set các thông số
FlexCelReport fr = new FlexCelReport();
if (LoaiIn.Equals("00000000-0000-0000-0000-000000000000"))
{
//Toan lục luong
fr = ReportModels.LayThongTinChuKy(fr, "rptNhanSu_DoTuoi");
}
else if (LoaiIn.Equals("00000000-0000-0000-0000-000000000001"))
{
//Theo don vi
fr = ReportModels.LayThongTinChuKy(fr, "rptNhanSu_DoTuoiTheoDonVi");
}
else
{
//Loai don vi: khoi du an va khoi doanh nghiep
fr = ReportModels.LayThongTinChuKy(fr, "rptNhanSu_DoTuoi_TheoLoaiDonVi");
}
LoadData(fr, DoiTuong, LoaiIn, Cap, Nam, DoTuoi_Tu, DoTuoi_Den);
if (Nam != "0" & Nam != "")
fr.SetValue("Nam", "NĂM " + Nam.ToUpper());
else
fr.SetValue("Nam", "");
if (Cap != "0" & Cap != "")
fr.SetValue("Cap", " - CÁN BỘ CẤP " + Cap.ToUpper());
else
fr.SetValue("Cap","");
string sDoiTuong = "";
if (DoiTuong == "0" || DoiTuong == "")
{
sDoiTuong = "TOÀN QUÂN";
}
else
{
sDoiTuong = "Đối tượng " + L_DanhMucNgachLuong_Get_Ten(DoiTuong);
}
fr.SetValue("DoiTuong", sDoiTuong.ToUpper());
fr.SetValue("LoaiIn", LoaiIn);
fr.SetValue("BoQuocPhong", BoQuocPhong);
fr.SetValue("QuanKhu", QuanKhu);
fr.SetValue("DoTuoi", "ĐỘ TUỔI TỪ " + DoTuoi_Tu.ToString() + " ĐẾN " + DoTuoi_Den.ToString());
fr.Run(Result);
return Result;
}
public ExcelFile CreateReport(String path, String iNamLamViec, String iID_MaChungTu, String sSoChungTuChiTiet, String iID_MaNamNganSach, String iID_MaNguonNganSach,String UserID,String iID_MaThongTri)
{
XlsFile Result = new XlsFile(true);
Result.Open(path);
FlexCelReport fr = new FlexCelReport();
String BoQuocPhong = ReportModels.CauHinhTenDonViSuDung(1);
String CucTaiChinh = ReportModels.CauHinhTenDonViSuDung(2);
String ngay = ReportModels.Ngay_Thang_Nam_HienTai();
LoadData(fr, iNamLamViec, iID_MaChungTu, sSoChungTuChiTiet, iID_MaNamNganSach, iID_MaNguonNganSach);
//Lấy thông tin đơn vị nhận
DataTable dtDVN = dtDanhSachDonVi_Nhan(iNamLamViec,"",iID_MaChungTu);
String DVN = "";
if (dtDVN.Rows.Count > 0)
{
DVN = dtDVN.Rows[0]["sTenDonVi_Nhan"].ToString();
}
fr = ReportModels.LayThongTinChuKy(fr, "rptThongTriChuyenTien");
fr.SetValue("Nams", iNamLamViec);
fr.SetValue("So", sSoChungTuChiTiet);
fr.SetValue("Ngay", ngay);
fr.SetValue("ThangCT", DanhMucModels.ThangLamViec(UserID));
fr.SetValue("DonVi", DVN);
String sCT = "";
sCT = TachLaySoChungTu_DonViNhan(sSoChungTuChiTiet, 0);
fr.SetValue("RDT", "RDT-" + sCT);
fr.SetValue("CucTaiChinh", CucTaiChinh);
fr.SetValue("BoQuocPhong", BoQuocPhong);
DataTable dtTong = ThongTriChuyenTien(iNamLamViec, iID_MaChungTu, sSoChungTuChiTiet, iID_MaNamNganSach, iID_MaNguonNganSach);
long tong = 0;
if (dtTong.Rows.Count > 0)
{
for (int i = 0; i < dtTong.Rows.Count; i++)
{
tong += String.IsNullOrEmpty(dtTong.Rows[i]["rDTRut"].ToString()) ? 0 : long.Parse(dtTong.Rows[i]["rDTRut"].ToString());
}
}
//lay ten thong tri
DataTable dtLoaiThongTri;
String SQL = "SELECT * FROM KT_LoaiThongTri WHERE iID_MaThongTri=@iID_MaThongTri";
SqlCommand cmd = new SqlCommand(SQL);
cmd.Parameters.AddWithValue("@iID_MaThongTri", iID_MaThongTri);
dtLoaiThongTri = Connection.GetDataTable(cmd);
cmd.Dispose();
LoaiTT = Convert.ToString(dtLoaiThongTri.Rows[0]["sLoaiKhoan"]);
LoaiNS = Convert.ToString(dtLoaiThongTri.Rows[0]["sTenLoaiNS"]);
fr.SetValue("LoaiTT", LoaiTT.ToUpper());
fr.SetValue("LoaiNS", LoaiNS.ToUpper());
fr.SetValue("Tien", CommonFunction.TienRaChu(tong));
fr.Run(Result);
return Result;
}
public ExcelFile CreateReport(String path, String MaBL)
{
XlsFile Result = new XlsFile(true);
Result.Open(path);
using (FlexCelReport fr = new FlexCelReport())
{
LoadData(fr, MaBL);
fr.SetValue("NgayThang", ReportModels.Ngay_Thang_Nam_HienTai());
fr.Run(Result);
return Result;
}
}
public ExcelFile CreateReport(String path, String iNamLamViec,String KhoGiay)
{
XlsFile Result = new XlsFile(true);
Result.Open(path);
FlexCelReport fr = new FlexCelReport();
fr = ReportModels.LayThongTinChuKy(fr, "rptCongSan_PhuLuc3");
LoadData(fr, iNamLamViec);
fr.Run(Result);
return Result;
}
private void SetupMines(FlexCelReport MinesReport)
{
DataSet ds = new DataSet();
DataTable dtrows = ds.Tables.Add("datarow");
dtrows.Columns.Add("position", typeof(int));
DataTable dtcols = ds.Tables.Add("datacol");
dtcols.Columns.Add("position", typeof(int));
dtcols.Columns.Add("value", typeof(int));
ds.Relations.Add(dtrows.Columns["position"], dtcols.Columns["position"]);
//let's create 10 mines.
ArrayList mines = new ArrayList();
Random rnd = new Random();
while (mines.Count < 10)
{
int nextMine = rnd.Next(9 * 9 - 1);
int minepos = mines.BinarySearch(nextMine);
if (minepos >= 0)
{
continue; //the value already exists
}
mines.Insert(~minepos, nextMine);
}
//Fill the tables on master detail
for (int r = 0; r < 9; r++)
{
dtrows.Rows.Add(new object[] { r });
for (int c = 0; c < 9; c++)
{
object[] values = new object[2];
values[0] = r;
if (mines.BinarySearch(r * 9 + c) >= 0)
{
values[1] = 1;
}
else
{
values[1] = DBNull.Value;
}
dtcols.Rows.Add(values);
}
}
//finally, add the tables to the report.
MinesReport.ClearTables();
MinesReport.AddTable(ds, TDisposeMode.DisposeAfterRun); //leave to Flexcel to delete the dataset.
}
async Task <ExcelFile> RunReport(LangDataList langs)
{
ExcelFile Result = new XlsFile(true);
await Result.OpenAsync(await StorageFile.GetFileFromApplicationUriAsync(new Uri("ms-appx:///Assets/Templates/report.template.xls")));
using (FlexCelReport fr = new FlexCelReport(true))
{
fr.AddTable("lang", langs.items);
fr.Run(Result);
}
return(Result);
}
/// <summary>
/// Tạo báo cáo
/// </summary>
/// <param name="path">Đường dẫn đến file mẫu Excel</param>
/// <param name="Nam">Năm lương</param>
/// <param name="Thang">Tháng lương</param>
/// <param name="TrangThai">Trạng thái duyệt</param>
/// <param name="MaDV">Mã đơn vị</param>
/// <returns></returns>
public ExcelFile CreateReport(String Nam, String Thang, String TrangThai, String MaDV, String LoaiBieu)
{
XlsFile Result = new XlsFile(true);
DataTable data = new DataTable();
FlexCelReport fr = new FlexCelReport();
fr = ReportModels.LayThongTinChuKy(fr, "rptLuong_DanhSachChiTra");
switch (LoaiBieu)
{
case "rTheoDV":
data = Ghep_Data(Get_DV(Nam, Thang, TrangThai), DanhSachChiTra(Nam, Thang, TrangThai, MaDV), "Donvi");
data = Ghep_Data(GetLuong_Donvi(Nam, Thang, TrangThai, MaDV), data, "Tong");
data.Columns.Remove("iID_MaDonVi");
data.Columns.Remove("TenCB");
data.Columns.Remove("sSoSoLuong_CanBo");
data.Columns.Remove("TT_HT");
Result = TaoTieuDe(Result);
FillData(Result, data, "TT_DV", "sSoTaiKhoan_CanBo", "LCB");
break;
case "rLietKe":
data = Ghep_Data(Get_DV(Nam, Thang, TrangThai), DanhSachChiTra(Nam, Thang, TrangThai, MaDV), "Donvi");
data.Columns.Remove("iID_MaDonVi");
data.Columns.Remove("TenCB");
data.Columns.Remove("sSoSoLuong_CanBo");
data.Columns.Remove("TT_HT");
Result = TaoTieuDe(Result);
FillData(Result, data, "TT_DV", "sSoTaiKhoan_CanBo", "LCB");
break;
case "rCoHoTen":
data = Ghep_Data(Get_DV(Nam, Thang, TrangThai), DanhSachChiTra(Nam, Thang, TrangThai, MaDV), "Donvi");
data = Ghep_Data(GetLuong_Donvi(Nam, Thang, TrangThai, MaDV), data, "Tong");
data.Columns.Remove("iID_MaDonVi");
data.Columns.Remove("TT_DV");
data.Columns.Remove("sSoSoLuong_CanBo");
data.Columns.Remove("TT_HT");
Result = TaoTieuDe_HoTen(Result);
FillData(Result, data, "TenCB", "sSoTaiKhoan_CanBo", "LCB");
break;
}
data.Dispose();
tong=TongLuong(DanhSachChiTra(Nam, Thang, TrangThai, MaDV),tong);
//fr.SetValue("cap1", ReportModels.CauHinhTenDonViSuDung(1).ToUpper());
//fr.SetValue("cap2", ReportModels.CauHinhTenDonViSuDung(2).ToUpper());
fr.SetValue("Thang", Thang);
fr.SetValue("Nam", Nam);
int count=DanhSachChiTra(Nam,Thang,TrangThai,MaDV).Rows.Count>0? DanhSachChiTra(Nam,Thang,TrangThai,MaDV).Rows.Count:0;
fr.SetValue("SoNguoi", count);
fr.SetValue("SoTien", String.Format("{0:0,0}",tong));
fr.SetValue("TienRaChu", CommonFunction.TienRaChu(tong));
fr.SetValue("NgayThang", ReportModels.Ngay_Thang_Nam_HienTai());
fr.Run(Result);
return Result;
}
/// <summary>
/// tạo báo cáo
/// </summary>
/// <param name="path"></param>
/// <param name="NamLamViec"></param>
/// <param name="ThangLamViec"></param>
/// <param name="iID_MaDonVi"></param>
/// <returns></returns>
public ExcelFile CreateReport(String path, String ChucVu, string LoaiIn, string Cap,string Nam)
{
XlsFile Result = new XlsFile(true);
Result.Open(path);
//tính tổng tiền
if (Nam == null) Nam = "0";
DataTable dt = PhanTichDuToanNS_52(ChucVu, LoaiIn,Cap,Nam);
String BoQuocPhong = ReportModels.CauHinhTenDonViSuDung(1);
String QuanKhu = ReportModels.CauHinhTenDonViSuDung(2);
//set các thông số
FlexCelReport fr = new FlexCelReport();
if (LoaiIn.Equals("00000000-0000-0000-0000-000000000000"))
{
//Toan lục luong
fr = ReportModels.LayThongTinChuKy(fr, "rptNhanSu_ChucVu");
}
else if (LoaiIn.Equals("00000000-0000-0000-0000-000000000001"))
{
//Theo don vi
fr = ReportModels.LayThongTinChuKy(fr, "rptNhanSu_ChucVuTheoDonVi");
}
else
{
//Loai don vi: khoi du an va khoi doanh nghiep
fr = ReportModels.LayThongTinChuKy(fr, "rptNhanSu_ChucVu_TheoLoaiDonVi");
}
LoadData(fr, ChucVu, LoaiIn,Cap,Nam);
if (Nam != "0" & Nam != "")
fr.SetValue("Nam", "NĂM " + Nam.ToUpper());
else
fr.SetValue("Nam", "");
//if (Cap != "0" & Cap != "")
// fr.SetValue("Cap", " - CÁN BỘ CẤP " + Cap.ToUpper());
//else
// fr.SetValue("Cap","");
string sChucVu = "";
if (ChucVu == "0" || ChucVu == "")
{
sChucVu = "(TOÀN QUÂN)";
}
else
{
sChucVu = "(Chức vụ " + L_DanhMucChucVu_Get_Ten(ChucVu) + ")";
}
fr.SetValue("ChucVu", sChucVu.ToUpper());
fr.SetValue("BoQuocPhong", BoQuocPhong);
fr.SetValue("QuanKhu", QuanKhu);
fr.Run(Result);
return Result;
}
/// <summary>
/// Tạo file PDF xuất dữ liệu của cấp phát thông tri
/// </summary>
/// <param name="path">Đường dẫn tới file excel</param>
/// <param name="MaND">Mã người dùng</param>
/// <param name="iNamCapPhat">Đợt cấp phát</param>
/// <param name="sLNS">Loại ngân sách</param>
/// <param name="iID_MaDonVi">Mã đơn vị</param>
/// <param name="LoaiTongHop">Loại báo cáo tổng hợp hay chi tiết</param>
/// <param name="LoaiCapPhat">Loại cấp phát</param>
/// <param name="LoaiThongTri">Loại thông tri</param>
/// <returns></returns>
/// VungNV: 2015/11/12
public ExcelFile CreateReport(String path, String MaND, String sLNS, String iNamCapPhat, String iID_MaDonVi,
String LoaiTongHop, String LoaiCapPhat, String LoaiThongTri)
{
XlsFile Result = new XlsFile(true);
Result.Open(path);
FlexCelReport fr = new FlexCelReport();
fr = ReportModels.LayThongTinChuKy(fr, "rptCapPhat_ThongTri");
//Lấy dữ liệu chi tiết
LoadData(fr, MaND, sLNS, iNamCapPhat, iID_MaDonVi, LoaiTongHop);
//Lấy tháng của đợt cấp phát
DateTime dNamCapPhat = Convert.ToDateTime(iNamCapPhat);
String Thang = dNamCapPhat.Month.ToString();
String Nam = ReportModels.LayNamLamViec(MaND);
//Lấy tên đơn vị
String sTenDonVi = DonViModels.Get_TenDonVi(iID_MaDonVi, MaND);
//Lấy tên loại cấp phát
if (String.IsNullOrWhiteSpace(LoaiCapPhat))
{
DataTable dtLoaiThongTri = CommonFunction.Lay_dtDanhMuc("LoaiCapPhat");
foreach (DataRow row in dtLoaiThongTri.Rows)
{
string sMaLoai = row["iID_MaDanhMuc"].ToString();
if (sMaLoai == LoaiThongTri)
{
LoaiCapPhat = row["sTen"].ToString();
break;
}
}
}
if (!String.IsNullOrEmpty(LoaiCapPhat))
{
LoaiCapPhat.Trim();
}
fr.SetValue("BoQuocPhong", ReportModels.CauHinhTenDonViSuDung(1, MaND));
fr.SetValue("QuanKhu", ReportModels.CauHinhTenDonViSuDung(2, MaND));
fr.SetValue("NgayThang", ReportModels.Ngay_Thang_Nam_HienTai());
fr.SetValue("Nam", Nam);
fr.SetValue("NgayLap", "tháng " + Thang + " năm " + Nam);
fr.SetValue("sTenDonVi", sTenDonVi);
fr.SetValue("LoaiCapPhat", LoaiCapPhat);
fr.Run(Result);
return Result;
}
internal TRPNExpression(string Condition, ExcelFile Xls, TBand CurrentBand, FlexCelReport fr, TStackData Stack)
{
if (Condition != null && !Condition.StartsWith(TFormulaMessages.TokenString(TFormulaToken.fmStartFormula)))
{
Condition = TFormulaMessages.TokenString(TFormulaToken.fmStartFormula) + Condition;
}
TFormulaConvertTextWithTagsToInternal Parser = new TFormulaConvertTextWithTagsToInternal(Xls, Condition, CurrentBand, fr, Stack);
wi = new TWorkbookInfo(Xls, Xls.ActiveSheet, 0, 0, 0, 0, 0, 0, false);
Parser.Parse();
Fmla = Parser.GetTokens();
}
public ExcelFile CreateReport(String path, String TuThang, String DenThang, String NamLuong, String TrangThai)
{
XlsFile Result = new XlsFile(true);
Result.Open(path);
FlexCelReport fr = new FlexCelReport();
fr = ReportModels.LayThongTinChuKy(fr, "rptLuong_TongHopThuThueDauVao");
LoadData(fr, TuThang, DenThang,NamLuong,TrangThai);
fr.SetValue("tuThang",TuThang);
fr.SetValue("denThang", DenThang);
fr.SetValue("NgayThang", ReportModels.Ngay_Thang_Nam_HienTai());
fr.Run(Result);
return Result;
}
/// <summary>
/// hàm khởi tạo báo cáo
/// </summary>
/// <param name="path"></param>
/// <param name="NamLamViec"></param>
/// <returns></returns>
public ExcelFile CreateReport(String path, String NamLamViec)
{
XlsFile Result = new XlsFile(true);
Result.Open(path);
String NgayThang = ReportModels.Ngay_Thang_Nam_HienTai();
FlexCelReport fr = new FlexCelReport();
fr = ReportModels.LayThongTinChuKy(fr, "rptTCDN_BaoCaoTaiChinhNam");
LoadData(fr, NamLamViec);
fr.SetValue("Ngay", NgayThang);
fr.SetValue("Nam", NamLamViec);
fr.Run(Result);
return Result;
}
private XlsFile Export(DataSet data)
{
using (FlexCelReport Report = new FlexCelReport(true))
{
Report.AddTable(data);
Report.SetUserFunction("Images", new ImagesImp());
XlsFile Xls = new XlsFile(true);
Xls.Open(Path.Combine(DataPath, "Features Page.template.xls"));
Report.Run(Xls);
return(Xls);
}
}
/// <summary>
/// Tạo file PDF xuất dữ liệu của quyết toán tổng hợp dữ liệu
/// </summary>
/// <param name="path">Đường dẫn tới file excel</param>
/// <param name="MaND">Mã người dùng</param>
/// <param name="iThang_Quy">Quý</param>
/// <param name="iID_MaNamNganSach">Mã năm ngân sách</param>
/// <param name="iID_MaPhongBan">Mã phòng ban</param>
/// <returns></returns>
public ExcelFile CreateReport(String path, String MaND, String iThang_Quy, String iID_MaNamNganSach, String iID_MaPhongBan)
{
XlsFile Result = new XlsFile(true);
Result.Open(path);
FlexCelReport fr = new FlexCelReport();
fr = ReportModels.LayThongTinChuKy(fr, "rptQuyetToan_LNS_DonVi");
//lấy dữ liệu chi tiết
LoadData(fr, MaND, iThang_Quy, iID_MaNamNganSach, iID_MaPhongBan);
String Nam = "";
//lấy năm ngân sách
String NamNganSach = "";
if (iID_MaNamNganSach == "1")
{
NamNganSach = "NGÂN SÁCH NĂM TRƯỚC";
Nam = Convert.ToString(Int32.Parse(ReportModels.LayNamLamViec(MaND)) - 1);
}
else if (iID_MaNamNganSach == "2")
{
NamNganSach = "NGÂN SÁCH NĂM NAY";
Nam = ReportModels.LayNamLamViec(MaND);
}
else
{
NamNganSach = "NGÂN SÁCH TỔNG HỢP";
Nam = Convert.ToString(Int32.Parse(ReportModels.LayNamLamViec(MaND)) - 1) + "," + ReportModels.LayNamLamViec(MaND);
}
fr.SetValue("NgayThang", ReportModels.Ngay_Thang_Nam_HienTai());
fr.SetValue("Nam", Nam);
fr.SetValue("Quy", iThang_Quy);
fr.SetValue("NamNganSach", NamNganSach);
//lấy tên phòng ban
if (iID_MaPhongBan != "-1")
{
String sTenPhongBan = "B" + iID_MaPhongBan;
fr.SetValue("sTenPhongBan", sTenPhongBan);
}
else
{
String sTenPhongBan = "Tất cả các B";
fr.SetValue("sTenPhongBan", sTenPhongBan);
}
ViewData["iID_MaPhongBan"] = iID_MaPhongBan;
fr.Run(Result);
return Result;
}
private ExcelFile CreateReport()
{
XlsFile Result = new XlsFile(true);
Result.Open(MapPath("~/App_Data/template.xls"));
using (FlexCelReport fr = new FlexCelReport())
{
LoadData(fr);
fr.SetValue("ReportCaption", "Hello from FlexCel!");
fr.Run(Result);
return(Result);
}
}
public ExcelFile CreateReport(String path, String Quy, String Nam)
{
XlsFile Result = new XlsFile(true);
Result.Open(path);
FlexCelReport fr = new FlexCelReport();
fr = ReportModels.LayThongTinChuKy(fr, "rptQLDA_DuToan_Quy");
LoadData(fr, Quy, Nam);
fr.SetValue("quy", Quy);
fr.SetValue("nam", Nam);
fr.SetValue("cap2", ReportModels.CauHinhTenDonViSuDung(2).ToUpper());
fr.SetValue("cap3", ReportModels.CauHinhTenDonViSuDung(3).ToUpper());
fr.Run(Result);
return Result;
}
/// <summary>
/// hàm khởi tạo báo cáo
/// </summary>
/// <param name="path"></param>
/// <param name="iID_MaDonVi"></param>
/// <param name="iID_MaTrangThaiDuyet"></param>
/// <returns></returns>
public ExcelFile CreateReport(String path, String iID_MaDonVi, String iID_MaTrangThaiDuyet, String iID_MaSanPham, String iID_MaChiTietGia)
{
XlsFile Result = new XlsFile(true);
Result.Open(path);
String NgayThang = ReportModels.Ngay_Thang_Nam_HienTai();
FlexCelReport fr = new FlexCelReport();
fr = ReportModels.LayThongTinChuKy(fr, "rptGia_4");
LoadData(fr, iID_MaDonVi, iID_MaTrangThaiDuyet, iID_MaSanPham, iID_MaChiTietGia);
fr.SetValue("PhuLuc", "Biểu số 4");
fr.SetValue("Ngay", NgayThang);
fr.SetValue("QuanKhu", ReportModels.CauHinhTenDonViSuDung(2));
fr.Run(Result);
return Result;
}
private void LoadData(FlexCelReport fr)
{
DataSet1 Data = new DataSet1();
DataSet1TableAdapters.ProductTableAdapter ProductAdapter = new DataSet1TableAdapters.ProductTableAdapter();
ProductAdapter.Fill(Data.Product);
DataSet1TableAdapters.ProductPhotoTableAdapter ProductPhotoAdapter = new DataSet1TableAdapters.ProductPhotoTableAdapter();
ProductPhotoAdapter.Fill(Data.ProductPhoto);
DataSet1TableAdapters.ProductProductPhotoTableAdapter ProductProductPhotoAdapter = new DataSet1TableAdapters.ProductProductPhotoTableAdapter();
ProductProductPhotoAdapter.Fill(Data.ProductProductPhoto);
fr.AddTable(Data);
}
/// <summary>
/// Tạo báo cáo
/// </summary>
/// <param name="path">Đường dẫn tới file Excel mẫu</param>
/// <param name="Thang">Tháng lương</param>
/// <param name="Nam">Năm lương</param>
/// <param name="TrangThai">Trạng thái duyệt</param>
/// <param name="iMaDV">Mã đơn vị</param>
/// <returns></returns>
public ExcelFile CreateReport(String path, String Thang, String Nam, String TrangThai, String iMaDV)
{
XlsFile Result = new XlsFile(true);
Result.Open(path);
FlexCelReport fr = new FlexCelReport();
//Thêm chữ ký vào báo cáo
fr = ReportModels.LayThongTinChuKy(fr, "rptLuong_TongHopQuanSo");
LoadData(fr, Thang,Nam,TrangThai,iMaDV);
fr.SetValue("cap1", ReportModels.CauHinhTenDonViSuDung(1));
fr.SetValue("cap2", ReportModels.CauHinhTenDonViSuDung(2));
fr.SetValue("NgayThang", ReportModels.Ngay_Thang_Nam_HienTai());
fr.SetValue("ThangNam", "Tháng " + Thang + " / " + Nam);
fr.Run(Result);
return Result;
}
/// <summary>
/// Hàm khởi tạo báo cáo
/// </summary>
/// <param name="path"></param>
/// <param name="NamLamViec"></param>
/// <returns></returns>
public ExcelFile CreateReport(String path, String NamLamViec)
{
string ngay = ReportModels.Ngay_Thang_Nam_HienTai();
XlsFile Result = new XlsFile(true);
Result.Open(path);
FlexCelReport fr = new FlexCelReport();
fr = ReportModels.LayThongTinChuKy(fr, "rptQLDA_01DT");
LoadData(fr, NamLamViec);
fr.SetValue("Nam", NamLamViec);
fr.SetValue("cuctaichinh", "Cục Tài Chính");
fr.SetValue("ngay", ngay);
fr.SetValue("Phong", "Phòng QLNS Các DA");
fr.Run(Result);
return Result;
}
/// <summary>
/// 生成报表
/// </summary>
public void CreateExcelFile(DataSet dataSource)
{
string fileName = SysContext.CommonService.CreateUniqueNameForFile(TemplatePath);
string physicalDirectory = SysContext.Config.TempDirectory_Physical;
string fileSavePath = physicalDirectory + "\\" + fileName;
FlexCelReport cellReport = new FlexCelReport(true);
cellReport.AddTable(dataSource);
using (FlexCelReport ordersReport = cellReport)
{
ordersReport.SetValue("Date", DateTime.Now);
ordersReport.Run(TemplatePath, fileSavePath);
}
FilePhysicalPath = fileSavePath;
}
async Task <ExcelFile> RunReport(LangDataList langs)
{
ExcelFile Result = new XlsFile(true);
var template = await StorageFile.GetFileFromApplicationUriAsync(new Uri("ms-appx:///Assets/Templates/report.template.xlsx"));
using (var templateStream = await template.OpenStreamForReadAsync())
{
Result.Open(templateStream);
using (FlexCelReport fr = new FlexCelReport(true))
{
fr.AddTable("lang", langs.items);
fr.Run(Result);
}
}
return(Result);
}
public static void Main(string[] args)
{
var Customers = new List <Customer>();
Customers.Add(new Customer {
Name = "Bill", Address = "555 demo line"
});
Customers.Add(new Customer {
Name = "Joe", Address = "556 demo line"
});
using (FlexCelReport fr = new FlexCelReport(true))
{
fr.AddTable("Customer", Customers);
fr.Run("templates\\report.template.xlsx", "result.xlsx");
}
}
public static void LoadData(FlexCelReport reportStart, string CityName, string DataPath, bool UseOfflineData)
{
reportStart.SetValue("Date", DateTime.Now);
DataSet ds = new DataSet();
ds.Locale = CultureInfo.InvariantCulture;
ds.EnforceConstraints = false;
ds.ReadXmlSchema(Path.Combine(DataPath, "TripSearchResponse.xsd"));
ds.Tables["Result"].Columns.Add("ImageData", typeof(byte[])); //Add a column for the actual images.
if (UseOfflineData)
{
ds.ReadXml(Path.Combine(DataPath, "OfflineData.xml"));
}
else
{
// Create the web request
string url = String.Format("http://travel.yahooapis.com/TripService/V1.1/tripSearch?appid=YahooDemo&query={0}&results=20", CityName);
UriBuilder uri = new UriBuilder(url);
HttpWebRequest request = WebRequest.Create(uri.Uri.AbsoluteUri) as HttpWebRequest;
// Get response
using (HttpWebResponse response = request.GetResponse() as HttpWebResponse)
{
// Load data into a dataset
ds.ReadXml(response.GetResponseStream());
}
}
if (ds.Tables["ResultSet"].Rows.Count <= 0)
{
throw new Exception("Error loading the data.");
}
if (Convert.ToInt32(ds.Tables["ResultSet"].Rows[0]["totalResultsReturned"]) <= 0)
{
throw new Exception("There are no travel plans for this location");
}
LoadImageData(ds, UseOfflineData, DataPath);
/* Uncomment this code to create an offline image of the data.*/
#if (CreateOffline)
ds.WriteXml(Path.Combine(DataPath, "OfflineData.xml"));
#endif
reportStart.AddTable(ds);
}
/// <summary>
/// 生成报表
/// </summary>
public void CreateExcelFile(DataSet dataSource)
{
string fileName = SysContext.CommonService.CreateUniqueNameForFile(TemplatePath);
string physicalDirectory = SysContext.Config.TempDirectory_Physical;
string fileSavePath = physicalDirectory + "\\" + fileName;
FlexCelReport cellReport = new FlexCelReport(true);
cellReport.AddTable(dataSource);
using (FlexCelReport ordersReport = cellReport)
{
ordersReport.SetValue("Date", DateTime.Now);
ordersReport.Run(TemplatePath, fileSavePath);
}
FilePhysicalPath = fileSavePath;
}
public void AutoRun()
{
using (FlexCelReport report = SharedData.CreateReport())
{
string DataPath = Path.Combine(Path.Combine(Path.GetDirectoryName(Assembly.GetExecutingAssembly().Location), ".."), "..") + Path.DirectorySeparatorChar;
if (saveFileDialog1.ShowDialog() == DialogResult.OK)
{
report.Run(DataPath + "Balanced Columns.template.xls", saveFileDialog1.FileName);
if (MessageBox.Show("Do you want to open the generated file?", "Confirm", MessageBoxButtons.YesNo) == DialogResult.Yes)
{
Process.Start(saveFileDialog1.FileName);
}
}
}
}
public void Dispose()
{
if (MainBand != null)
{
MainBand.Dispose();
}
if (Report != null)
{
Report.Unload();
#if (!COMPACTFRAMEWORK)
((IDisposable)Report).Dispose();
#endif
}
Report = null;
MainBand = null;
GC.SuppressFinalize(this);
}
private void SetJobAdReportProperties(FlexCelReport flexCelReport)
{
if (_includeCredits)
{
var posterIds = GetPosterIds();
var jobAdReport = _jobAdReportsQuery.GetJobAdReport(posterIds, _dateRange);
var jobAdTotalsReport = _jobAdReportsQuery.GetJobAdTotalsReport(jobAdReport.ClosedJobAds);
flexCelReport.SetValue("OpenedJobAds", jobAdReport.OpenedJobAds.Count);
flexCelReport.SetValue("ClosedJobAds", jobAdReport.ClosedJobAds.Count);
flexCelReport.SetValue("TotalApplications", jobAdReport.Totals.Applications);
flexCelReport.SetValue("TotalViews", jobAdReport.Totals.Views);
flexCelReport.SetValue("TotalClosedJobAdApplications", jobAdTotalsReport.Applications);
flexCelReport.SetValue("TotalClosedJobAdViews", jobAdTotalsReport.Views);
}
}
private void Export(DataSet data)
{
using (FlexCelReport Report = new FlexCelReport(true))
{
Report.AddTable(data);
Report.SetValue("FeedName", ((FeedData)cbFeeds.SelectedValue).Name);
Report.SetValue("FeedUrl", ((FeedData)cbFeeds.SelectedValue).Url);
Report.SetValue("ShowCount", cbShowFeedCount.Checked);
using (FileStream fs = new FileStream(Path.Combine(Path.Combine(DataPath, "logos"), ((FeedData)cbFeeds.SelectedValue).Logo), FileMode.Open))
{
byte[] b = new byte[fs.Length];
fs.Read(b, 0, b.Length);
Report.SetValue("Logo", b);
}
Report.Run(DataPath + "Meta Templates.template.xls", saveFileDialog1.FileName);
}
}
public void AutoRun()
{
using (FlexCelReport ordersReport = SharedData.CreateReport())
{
ordersReport.SetValue("Date", DateTime.Now);
string DataPath = Path.Combine(Path.Combine(Path.GetDirectoryName(Assembly.GetExecutingAssembly().Location), ".."), "..") + Path.DirectorySeparatorChar;
if (saveFileDialog1.ShowDialog() == DialogResult.OK)
{
ordersReport.Run(DataPath + "Tables As Datasources.template.xlsx", saveFileDialog1.FileName);
if (MessageBox.Show("Do you want to open the generated file?", "Confirm", MessageBoxButtons.YesNo) == DialogResult.Yes)
{
Process.Start(saveFileDialog1.FileName);
}
}
}
}