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 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);
}
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 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 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);
}
}
}
}
}
internal static FlexCel.Report.FlexCelReport CreateReport()
{
FlexCelReport Result = new FlexCelReport(true);
Result.AddTable(GetDataTable());
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);
}
}
}
}
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);
}
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);
}
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);
}
}
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);
}
private void LoadTables(FlexCelReport report)
{
List <Categories> Categories = new List <Categories>();
Categories Animals = new Categories("Animals");
Animals.Elements.Add(new Elements(1, "Penguin"));
Animals.Elements.Add(new Elements(2, "Cat"));
Animals.Elements.Add(new Elements(3, "Unicorn"));
Categories.Add(Animals);
Categories Flowers = new Categories("Flowers");
Flowers.Elements.Add(new Elements(4, "Daisy"));
Flowers.Elements.Add(new Elements(5, "Rose"));
Flowers.Elements.Add(new Elements(6, "Orchid"));
Categories.Add(Flowers);
report.AddTable("Categories", Categories);
//We don't need to call AddTable for elements since it is already added when we add Categories.
List <ElementName> ElementNames = new List <ElementName>();
ElementNames.Add(new ElementName(1, "Linus"));
ElementNames.Add(new ElementName(1, "Gerard"));
ElementNames.Add(new ElementName(2, "Rover"));
ElementNames.Add(new ElementName(3, "Mike"));
ElementNames.Add(new ElementName(5, "Rosalyn"));
ElementNames.Add(new ElementName(5, "Monica"));
ElementNames.Add(new ElementName(6, "Lisa"));
report.AddTable("ElementName", ElementNames);
//ElementName doesn't have an intrinsic relationship with categories, so we will have to manually add a relationship.
//Non intrinsic relationships should be rare, but we do it here to show how it can be done.
report.AddRelationship("Elements", "ElementName", "ElementID", "ElementID");
}
/// <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 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);
}
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");
}
}
/// <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;
}
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);
}
}
private FlexCelReport CreateReport()
{
FlexCelReport Result = new FlexCelReport(true);
Result.SetValue("test", 3);
Result.SetValue("tagval", 1);
Result.SetValue("refval", "l");
//Here we will add a dummy table with some fantasy values
DataTable dt = new DataTable("testdb");
dt.Columns.Add("key", typeof(int));
dt.Columns.Add("data", typeof(string));
dt.Rows.Add(new object[] { 5, "cat" });
dt.Rows.Add(new object[] { 6, "dog" });
Result.AddTable("testdb", dt, TDisposeMode.DisposeAfterRun);
return(Result);
}
ExcelFile RunReport(LangDataList langs)
{
ExcelFile Result = new XlsFile(true);
using (var template = Assets.Open("report.template.xls"))
{
//we can't load directly from the asset stream, as we need a seekable stream.
using (var memtemplate = new MemoryStream())
{
template.CopyTo(memtemplate);
memtemplate.Position = 0;
Result.Open(memtemplate);
}
}
using (FlexCelReport fr = new FlexCelReport(true))
{
fr.AddTable("lang", langs.items);
fr.Run(Result);
}
return(Result);
}
public static void ReportForExcel(ICollection <CallHistory> collection)
{
FlexCelReport flexCelReport = new FlexCelReport();
string fileName = AssemblyDirectory + "\\Report.xlsx";
XlsFile result = new XlsFile(true);
result.Open(fileName);
IList <CallHistory> list = (IList <CallHistory>)collection;
flexCelReport.AddTable("table", ToDataTable(list));
flexCelReport.Run(result);
String docPath2 = AssemblyDirectory + "\\NewReport.xlsx";
result.Save(docPath2);
Console.WriteLine("Файл выгружен в " + docPath2);
_file = docPath2;
}
private void LoadData(FlexCelReport fr, String iThang, String iID_MaDonVi, String UserName, String iID_MaTrangThaiDuyet)
{
String TenDonVi = DonViModels.Get_TenDonVi(iID_MaDonVi);
DataTable data = dt_PhanHo(iThang, iID_MaDonVi, UserName, iID_MaTrangThaiDuyet);
//DataView dv = data.DefaultView;
//dv.Sort = "iID_MaTaiKhoan";
//data = dv.ToTable();
//for (int i = 0; i < data.Rows.Count; i++)
//{
// DataRow r=data.Rows[i];
// if (String.IsNullOrEmpty(Convert.ToString(r["rSoPhatSinhNo"])) && String.IsNullOrEmpty(Convert.ToString(r["rSoPhatSinhCo"])))
// {
// data.Rows.Remove(r);
// }
//}
for (int i = 0; i < data.Rows.Count; i++)
{
DataRow r = data.Rows[i];
if (String.IsNullOrEmpty(Convert.ToString(r["rSoPhatSinhNo"])) && String.IsNullOrEmpty(Convert.ToString(r["rSoPhatSinhCo"])) && Convert.ToString(r["bLahangCha"]) != "4")
{
data.Rows.Remove(r);
}
}
data.TableName = "ChiTiet";
fr.AddTable("ChiTiet", data);
fr.SetValue("TenDonVi", iID_MaDonVi + "-" + TenDonVi);
data.Dispose();
}
/// <summary>
/// Đẩy dữ liệu xuống báo cáo
/// </summary>
/// <param name="fr">File báo cáo</param>
/// <param name="iThang">Tháng quân số</param>
/// <param name="iNam">Năm quân số</param>
/// <param name="iAll">Tổng hợp tất cả đơn vị</param>
/// <param name="iMaDV">Mã đơn vị</param>
private void LoadData(FlexCelReport fr, String iThang, String iID_MaTrangThaiDuyet, String iAll, String iMaDV,String MaND)
{
//Quân số đến tháng
DataTable data = QuyetToanQuanSo(iThang, iID_MaTrangThaiDuyet, iAll, iMaDV,MaND);
data.TableName = "ChiTiet";
fr.AddTable("ChiTiet", data);
data.Dispose();
}
/// <summary>
/// Đẩy dữ liệu xuống báo cáo
/// </summary>
/// <param name="fr"></param>
/// <param name="NamLamViec"></param>
/// <param name="iID_MaDonVi"></param>
private void LoadData(FlexCelReport fr, String MaND, String iID_MaDonVi, String iID_MaTrangThaiDuyet)
{
DataTable data = DT_ChiNganSachQuocPhongNamTuyVien_DonVi(MaND, iID_MaDonVi, iID_MaTrangThaiDuyet);
data.TableName = "ChiTiet";
fr.AddTable("ChiTiet", data);
DataTable dtTieuMuc;
dtTieuMuc = HamChung.SelectDistinct("TieuMuc", data, "sLNS,sL,sK,sM,sTM", "sLNS,sL,sK,sM,sTM,sMoTa", "sLNS,sL,sK,sTM,sTTM");
fr.AddTable("TieuMuc", dtTieuMuc);
DataTable dtMuc;
dtMuc = HamChung.SelectDistinct("Muc", dtTieuMuc, "sLNS,sL,sK,sM", "sM,sMoTa", "sLNS,sL,sK,sM,sTM");
fr.AddTable("Muc", dtMuc);
data.Dispose();
dtMuc.Dispose();
dtTieuMuc.Dispose();
}
/// <summary>
/// tạo các range trong báo cáo
/// </summary>
/// <param name="fr"></param>
/// <param name="NamLamViec"></param>
/// <param name="ThangQuy"></param>
/// <param name="iID_MaDonVi"></param>
/// <param name="LoaiThangQuy"></param>
/// <param name="sLNS"></param>
/// <param name="TruongTien"></param>
private void LoadData(FlexCelReport fr, String iID_MaPhongBan, String iID_MaTrangThaiDuyet, String Thang_Quy, String iID_MaDonVi, String LoaiThangQuy, String sLNS, String TruongTien, String MaND)
{
DataTable data = QT_NV_36_1(iID_MaPhongBan, iID_MaTrangThaiDuyet, Thang_Quy, iID_MaDonVi, LoaiThangQuy, sLNS, TruongTien, MaND);
data.TableName = "ChiTiet";
fr.AddTable("ChiTiet", data);
DataTable dtTieuMuc;
dtTieuMuc = HamChung.SelectDistinct("TieuMuc", data, "NguonNS,sLNS,sL,sK,sM,sTM", "NguonNS,sLNS,sL,sK,sM,sTM,sMoTa", "sLNS,sL,sK,sM,sTM,sTTM");
fr.AddTable("TieuMuc", dtTieuMuc);
DataTable dtMuc;
dtMuc = HamChung.SelectDistinct("Muc", dtTieuMuc, "NguonNS,sLNS,sL,sK,sM", "NguonNS,sLNS,sL,sK,sM,sMoTa", "sLNS,sL,sK,sM,sTM");
fr.AddTable("Muc", dtMuc);
DataTable dtLoaiNS;
dtLoaiNS = HamChung.SelectDistinct("LoaiNS", dtMuc, "NguonNS,sLNS", "NguonNS,sLNS,sMoTa", "sLNS,sL");
fr.AddTable("LoaiNS", dtLoaiNS);
DataTable dtNguonNS;
dtNguonNS = HamChung.SelectDistinct("NguonNS", dtMuc, "NguonNS", "NguonNS,sMoTa", "sLNS,sL", "NguonNS");
fr.AddTable("NguonNS", dtNguonNS);
data.Dispose();
dtLoaiNS.Dispose();
dtMuc.Dispose();
dtNguonNS.Dispose();
dtTieuMuc.Dispose();
}
/// <summary>
/// hàm hiển thị dữ liệu ra báo cáo
/// </summary>
/// <param name="fr"></param>
/// <param name="NamLamViec"></param>
private void LoadData(FlexCelReport fr, String MaND, String iID_MaDonVi, String iID_MaPhongBan, String iThang)
{
DataRow r;
DataTable data = DT_rptQTQS_THQS_TungDonVi_1(MaND, iID_MaDonVi, iID_MaPhongBan, iThang);
data.TableName = "ChiTiet";
fr.AddTable("ChiTiet", data);
data.Dispose();
}
/// <summary>
/// Đổ dữ liệu xuống file báo cáo
/// </summary>
/// <param name="fr"></param>
/// <param name="NgayCT"></param>
/// <param name="MaDN"></param>
private void LoadData(FlexCelReport fr, String Quy, String Nam, String MaDN)
{
DataTable data = Hoat_Dong_Kinh_Doanh(Quy,Nam, MaDN);
data.TableName = "ChiTiet";
fr.AddTable("ChiTiet", data);
data.Dispose();
}
/// <summary>
/// lấy dữ liệu fill vào báo cáo
/// </summary>
/// <param name="fr"></param>
/// <param name="NgoaiTe"></param>
/// <param name="dNgay"></param>
private void LoadData(FlexCelReport fr, String NgoaiTe, String dNgay)
{
DataTable data = LayDanhSach(NgoaiTe, dNgay);
data.Columns.Add("sTienDo", typeof(String));
data.TableName = "ChiTiet";
//Hạng mục chi tiết
DataTable dtHangMucChiTiet = HamChung.SelectDistinct_QLDA("HMChiTiet", data, "NguonNS,sLNS,sDeAn,sDuAn,sDuAnThanhPhan,sCongTrinh,sHangMucCongTrinh,sHangMucChiTiet", "NguonNS,sLNS,sDeAn,sDuAn,sDuAnThanhPhan,sCongTrinh,sHangMucCongTrinh,sHangMucChiTiet,sTenDuAn,sTienDo");
// Hạng mục công trình
DataTable dtHangMucCongTrinh = HamChung.SelectDistinct_QLDA("HMCT", dtHangMucChiTiet, "NguonNS,sLNS,sDeAn,sDuAn,sDuAnThanhPhan,sCongTrinh,sHangMucCongTrinh", "NguonNS,sLNS,sDeAn,sDuAn,sDuAnThanhPhan,sCongTrinh,sHangMucCongTrinh,sTenDuAn,sTienDo", "sDeAn,sDuAn,sDuAnThanhPhan,sCongTrinh,sHangMucCongTrinh,sHangMucChiTiet");
//Công trình
DataTable dtCongTrinh = HamChung.SelectDistinct_QLDA("CongTrinh", dtHangMucCongTrinh, "NguonNS,sLNS,sDeAn,sDuAn,sDuAnThanhPhan,sCongTrinh", "NguonNS,sLNS,sDeAn,sDuAn,sDuAnThanhPhan,sCongTrinh,sTenDuAn,sTienDo", "sDeAn,sDuAn,sDuAnThanhPhan,sCongTrinh,sHangMucCongTrinh");
//Dự án thành phần
DataTable dtDuAnThanhPhan = HamChung.SelectDistinct_QLDA("DATP", dtCongTrinh, "NguonNS,sLNS,sDeAn,sDuAn,sDuAnThanhPhan", "NguonNS,sLNS,sDeAn,sDuAn,sDuAnThanhPhan,sTenDuAn,sTienDo", "sDeAn,sDuAn,sDuAnThanhPhan,sCongTrinh");
//Dự án
DataTable dtDuAn = HamChung.SelectDistinct_QLDA("DuAn", dtDuAnThanhPhan, "NguonNS,sLNS,sDeAn,sDuAn", "NguonNS,sLNS,sDeAn,sDuAn,sTenDuAn,sTienDo", "sDeAn,sDuAn,sDuAnThanhPhan");
//Đề án
DataTable dtDeAn = HamChung.SelectDistinct_QLDA("DeAn", dtDuAn, "NguonNS,sLNS,sDeAn", "NguonNS,sLNS,sDeAn,sTenDuAn,sTienDo", "sDeAn,sDuAn");
//sLNS
DataTable dtLNS = HamChung.SelectDistinct("sLNS", dtDeAn, "NguonNS,sLNS", "NguonNS,sLNS,sTenDuAn", "", "");
//Nguồn
DataTable dtNguon = HamChung.SelectDistinct("Nguon", dtDeAn, "NguonNS", "NguonNS,sTenDuAn", "", "NguonNS");
//Thêm tên Loại ngân sách của dtLNS
for (int i = 0; i < dtLNS.Rows.Count; i++)
{
String sLNS = Convert.ToString(dtLNS.Rows[i]["sLNS"]);
DataRow r = dtLNS.Rows[i];
String SQL = "SELECT sMoTa FROM NS_MucLucNganSach WHERE iTrangThai=1 AND sL='' AND sLNS=@sLNS";
SqlCommand cmd = new SqlCommand(SQL);
cmd.Parameters.AddWithValue("@sLNS", sLNS);
String sMoTa = "";
sMoTa = Connection.GetValueString(cmd, "");
r["sTenDuAn"] = sMoTa;
}
data.TableName = "Chitiet";
fr.AddTable("Chitiet", data);
fr.AddTable("HMCT", dtHangMucCongTrinh);
fr.AddTable("CongTrinh", dtCongTrinh);
fr.AddTable("DATP", dtDuAnThanhPhan);
fr.AddTable("DuAn", dtDuAn);
fr.AddTable("DeAn", dtDeAn);
fr.AddTable("sLNS", dtLNS);
fr.AddTable("Nguon", dtNguon);
dtDeAn.Dispose();
dtDuAn.Dispose();
dtDuAnThanhPhan.Dispose();
dtCongTrinh.Dispose();
dtNguon.Dispose();
data.Dispose();
}
/// <summary>
/// Lấy dữ liệu chi tiết
/// </summary>
/// <param name="fr"></param>
/// <param name="MaND"></param>
/// <param name="sLNS"></param>
/// <param name="iThang_Quy"></param>
/// <param name="iID_MaDonVi"></param>
/// <param name="iID_MaNamNganSach"></param>
/// <param name="LoaiBaoCao"></param>
/// <param name="iID_MaPhongBan"></param>
/// HungPH: 18/11/2015
private void LoadData(FlexCelReport fr, String MaND, String sLNS, String iThang_Quy, String iID_MaDonVi, String iID_MaNamNganSach, String LoaiBaoCao, String iID_MaPhongBan)
{
DataRow r;
DataTable data = new DataTable();
DataTable dtDonVi = new DataTable();
if (LoaiBaoCao == "ChiTiet")
{
data = QuyetToan_ReportModels.rptQuyetToan_TongHop_Nam_Quy(MaND, sLNS, iThang_Quy, iID_MaDonVi, iID_MaNamNganSach, LoaiBaoCao, iID_MaPhongBan);
}
else
{
dtDonVi = QuyetToan_ReportModels.rptQuyetToan_TongHop_Nam_Quy(MaND, sLNS, iThang_Quy, iID_MaDonVi, iID_MaNamNganSach, LoaiBaoCao, iID_MaPhongBan);
data = HamChung.SelectDistinct("ChiTiet", dtDonVi, "sLNS1,sLNS3,sLNS5,sLNS,sL,sK,sM,sTM,sTTM,sNG", "sLNS1,sLNS3,sLNS5,sLNS,sL,sK,sM,sTM,sTTM,sNG,sMoTa");
fr.AddTable("dtDonVi", dtDonVi);
dtDonVi.Dispose();
}
data.TableName = "ChiTiet";
fr.AddTable("ChiTiet", data);
DataTable dtsTM = HamChung.SelectDistinct("dtsTM", data, "sLNS1,sLNS3,sLNS5,sLNS,sL,sK,sM,sTM", "sLNS1,sLNS3,sLNS5,sLNS,sL,sK,sM,sTM,sMoTa", "sLNS,sL,sK,sM,sTM,sTTM");
DataTable dtsM = HamChung.SelectDistinct("dtsM", dtsTM, "sLNS1,sLNS3,sLNS5,sLNS,sL,sK,sM", "sLNS1,sLNS3,sLNS5,sLNS,sL,sK,sM,sMoTa", "sLNS,sL,sK,sM,sTM");
DataTable dtsL = HamChung.SelectDistinct("dtsL", dtsM, "sLNS1,sLNS3,sLNS5,sLNS,sL,sK", "sLNS1,sLNS3,sLNS5,sLNS,sL,sK,sMoTa", "sLNS,sL,sK,sM");
DataTable dtsLNS = HamChung.SelectDistinct("dtsLNS", dtsL, "sLNS1,sLNS3,sLNS5,sLNS", "sLNS1,sLNS3,sLNS5,sLNS,sMoTa", "sLNS,sL");
DataTable dtsLNS5 = HamChung.SelectDistinct("dtsLNS5", dtsLNS, "sLNS1,sLNS3,sLNS5", "sLNS1,sLNS3,sLNS5,sMoTa");
for (int i = 0; i < dtsLNS5.Rows.Count; i++)
{
r = dtsLNS5.Rows[i];
r["sMoTa"] = ReportModels.LayMoTa(Convert.ToString(r["sLNS5"]));
}
DataTable dtsLNS3 = HamChung.SelectDistinct("dtsLNS3", dtsLNS5, "sLNS1,sLNS3", "sLNS1,sLNS3,sMoTa");
for (int i = 0; i < dtsLNS3.Rows.Count; i++)
{
r = dtsLNS3.Rows[i];
r["sMoTa"] = ReportModels.LayMoTa(Convert.ToString(r["sLNS3"]));
}
DataTable dtsLNS1 = HamChung.SelectDistinct("dtsLNS1", dtsLNS3, "sLNS1", "sLNS1,sMoTa");
for (int i = 0; i < dtsLNS1.Rows.Count; i++)
{
r = dtsLNS1.Rows[i];
r["sMoTa"] = ReportModels.LayMoTa(Convert.ToString(r["sLNS1"]));
}
fr.AddTable("dtsTM", dtsTM);
fr.AddTable("dtsM", dtsM);
fr.AddTable("dtsL", dtsL);
fr.AddTable("dtsLNS", dtsLNS);
fr.AddTable("dtsLNS1", dtsLNS1);
fr.AddTable("dtsLNS3", dtsLNS3);
fr.AddTable("dtsLNS5", dtsLNS5);
data.Dispose();
dtsTM.Dispose();
dtsM.Dispose();
dtsL.Dispose();
dtsLNS.Dispose();
dtsLNS1.Dispose();
dtsLNS3.Dispose();
dtsLNS5.Dispose();
}
/// <summary>
/// hàm hiển thị dữ liệu ra báo cáo
/// </summary>
/// <param name="fr"></param>
/// <param name="iID_MaDonVi"></param>
/// <param name="iID_MaTrangThaiDuyet"></param>
private void LoadData(FlexCelReport fr, String iID_MaTrangThaiDuyet, String iID_MaSanPham, String iID_MaChiTietGia)
{
DataTable data = rptGia_1b(iID_MaTrangThaiDuyet, iID_MaSanPham, iID_MaChiTietGia);
data.TableName = "ChiTiet";
fr.AddTable("ChiTiet", data);
data.Dispose();
DataTable dtSP = SanPham(iID_MaTrangThaiDuyet, iID_MaSanPham, iID_MaChiTietGia);
dtSP.TableName = "SanPham";
fr.AddTable("SanPham", dtSP);
if (dtSP.Rows.Count > 0)
{
switch (dtSP.Rows[0]["iID_MaLoaiHinh"].ToString())
{
case "1":
fr.SetValue("LoaiHinh", "Sửa chữa lớn");
break;
case "2":
fr.SetValue("LoaiHinh", "Sửa chữa vừa");
break;
case "3":
fr.SetValue("LoaiHinh", "Sửa chữa nhỏ");
break;
}
}
dtSP.Dispose();
}
/// <summary>
/// Load data
/// </summary>
/// <param name="fr"></param>
/// <param name="NamLamViec"></param>
/// <param name="iID_MaDonVi"></param>
/// <param name="sLNS"></param>
/// <param name="TongHop"></param>
private void LoadData(FlexCelReport fr, String sLNS, String iID_MaDonVi, String Loai, String iID_MaTrangThaiDuyet, String UserName,String KhoGiay)
{
DataTable data = NCC_TCKK_58_4(sLNS, iID_MaDonVi, Loai, iID_MaTrangThaiDuyet, UserName,KhoGiay);
data.TableName = "ChiTiet";
fr.AddTable("ChiTiet", data);
DataTable dtTieuMuc;
dtTieuMuc = HamChung.SelectDistinct("TieuMuc", data, "NguonNS,sLNS,sL,sK,sM,sTM", "NguonNS,sLNS,sL,sK,sM,sTM,sMoTa", "sLNS,sL,sK,sM,sTM,sTTM");
fr.AddTable("TieuMuc", dtTieuMuc);
DataTable dtMuc;
dtMuc = HamChung.SelectDistinct("Muc", dtTieuMuc, "NguonNS,sLNS,sL,sK,sM", "NguonNS,sLNS,sL,sK,sM,sMoTa", "sLNS,sL,sK,sM,sTM");
fr.AddTable("Muc", dtMuc);
DataTable dtLoaiNS;
dtLoaiNS = HamChung.SelectDistinct("LoaiNS", dtMuc, "NguonNS,sLNS", "NguonNS,sLNS,sMoTa", "sLNS,sL");
fr.AddTable("LoaiNS", dtLoaiNS);
DataTable dtNguonNS;
dtNguonNS = HamChung.SelectDistinct("NguonNS", dtMuc, "NguonNS", "NguonNS,sMoTa", "sLNS,sL", "NguonNS");
fr.AddTable("NguonNS", dtNguonNS);
dtLoaiNS.Dispose();
dtMuc.Dispose();
dtNguonNS.Dispose();
dtTieuMuc.Dispose();
}
/// <summary>
/// hàm hiển thị dữ liệu ra báo cáo
/// </summary>
/// <param name="fr"></param>
/// <param name="NamLamViec"></param>
private void LoadData(FlexCelReport fr, String MaND, String iID_MaDonVi)
{
DataRow r;
DataTable data = DT_rptDuToan_1020100_TungDonVi_DoanhNghiep(MaND, iID_MaDonVi);
data.TableName = "ChiTiet";
fr.AddTable("ChiTiet", data);
DataTable dtsTM = HamChung.SelectDistinct("dtsTM", data, "sM,sTM", "sLNS,sL,sK,sM,sTM,sMoTa", "sLNS,sL,sK,sM,sTM,sTTM");
DataTable dtsM = HamChung.SelectDistinct("dtsM", dtsTM, "sM", "sLNS,sL,sK,sM,sMoTa", "sLNS,sL,sK,sM,sTM");
fr.AddTable("dtsTM", dtsTM);
fr.AddTable("dtsM", dtsM);
data.Dispose();
dtsTM.Dispose();
dtsM.Dispose();
}
/// <summary>
/// Đổ dữ liệu xuống báo cáo
/// </summary>
/// <param name="fr">File báo cáo</param>
/// <param name="NamLamViec">Năm</param>
/// <param name="Thang_Quy">Tháng | Quý</param>
/// <param name="LoaiThang_Quy">1: Quý | 0: Tháng</param>
/// <param name="LoaiBaoCao">Loại báo cáo</param>
/// <param name="inmuc">In đến Loại khoản | Mục | Tiểu mục</param>
/// <param name="iID_MaNguonNganSach">Mã loại ngân sách</param>
/// <param name="iGom">On: Ghép 2 phần trên 1 báo cáo | Off: In 1 phần trên mỗi báo cáo</param>
private void LoadData(FlexCelReport fr, String NamLamViec, String Thang_Quy, String LoaiThang_Quy, String LoaiBaoCao, String inmuc, String iID_MaNguonNganSach, String iGom,String UserID)
{
DataTable data = DoiChieuDuToan(NamLamViec, Thang_Quy, LoaiThang_Quy, LoaiBaoCao, inmuc, iID_MaNguonNganSach,UserID);
data.TableName = "ChiTiet";
fr.AddTable("ChiTiet", data);
DataTable dtMuc;
dtMuc = HamChung.SelectDistinct("Muc", data, "iID_MaNguonNganSach,sL,sK,sM", "iID_MaNguonNganSach,sL,sK,sM");
fr.AddTable("Muc", dtMuc);
DataTable dtLoaiNS;
dtLoaiNS = HamChung.SelectDistinct("Khoan", dtMuc, "iID_MaNguonNganSach,sL,sK", "iID_MaNguonNganSach,sL,sK");
fr.AddTable("Khoan", dtLoaiNS);
if (iGom.Equals("on"))
{
DataTable data1 = DoiChieuDuToan1(NamLamViec, Thang_Quy, LoaiThang_Quy, LoaiBaoCao, inmuc, iID_MaNguonNganSach, UserID);
data1.TableName = "ChiTiet1";
fr.AddTable("ChiTiet1", data1);
}
data.Dispose();
dtLoaiNS.Dispose();
dtMuc.Dispose();
}
/// <summary>
/// Hàm hiển thị dữ liệu ra báo cáo
/// </summary>
/// <param name="fr"></param>
/// <param name="NamLamViec"></param>
/// <param name="iID_MaDonVi"></param>
private void LoadData(FlexCelReport fr, String iID_MaDonVi, String MaND, String iID_MaTrangThaiDuyet)
{
DataTable data = DT_DuToanNganSachNhaNuoc5(iID_MaDonVi, MaND, iID_MaTrangThaiDuyet);
data.TableName = "ChiTiet";
fr.AddTable("ChiTiet", data);
DataTable dtTieuMuc;
dtTieuMuc = HamChung.SelectDistinct("TieuMuc", data, "NguonNS,LoaiNS,sLNS,sM,sTM", "NguonNS,LoaiNS,sLNS,sK,sM,sTM,sL,sMoTa", "sTM,sTTM");
fr.AddTable("TieuMuc", dtTieuMuc);
DataTable dtMuc;
dtMuc = HamChung.SelectDistinct("Muc", dtTieuMuc, "NguonNS,LoaiNS,sLNS,sM", "NguonNS,LoaiNS,sLNS,sM,sK,sL,sMoTa", "sM,sTM");
fr.AddTable("Muc", dtMuc);
DataTable dtLK;
dtLK = HamChung.SelectDistinct("LK", dtMuc, "NguonNS,LoaiNS,sLNS,sL", "NguonNS,LoaiNS,sLNS,sL,sM,sK,sMoTa", "sLNS,sL,sK,sM");
fr.AddTable("LK", dtLK);
DataTable dtLNS;
dtLNS = HamChung.SelectDistinct("LNS", dtLK, "NguonNS,LoaiNS,sLNS", "NguonNS,LoaiNS,sLNS,sMoTa", "sLNS,sL");
fr.AddTable("LNS", dtLNS);
DataTable dtLoaiNS;
dtLoaiNS = HamChung.SelectDistinct("LoaiNS", dtLNS, "NguonNS,LoaiNS", "NguonNS,LoaiNS,sMoTa", "sLNS,sL", "LoaiNS");
fr.AddTable("LoaiNS", dtLoaiNS);
DataTable dtNguonNS;
dtNguonNS = HamChung.SelectDistinct("NguonNS", dtLoaiNS, "NguonNS", "NguonNS,sMoTa", "sLNS", "NguonNS");
fr.AddTable("NguonNS", dtNguonNS);
data.Dispose();
dtNguonNS.Dispose();
dtTieuMuc.Dispose();
dtMuc.Dispose();
dtLK.Dispose();
dtLNS.Dispose();
}
/// <summary>
/// tạo range trong báo cáo
/// </summary>
/// <param name="fr"></param>
/// <param name="NamLamViec"></param>
/// <param name="iID_MaDonVi"></param>
private void LoadData(FlexCelReport fr, String MaND, String iID_MaDonVi, String iID_MaDanhMuc, String sLNS, String iID_MaTrangThaiDuyet, String Check)
{
DataTable data = rptDT_ChiNganSachSD_II_02(MaND, iID_MaDonVi, iID_MaDanhMuc, sLNS, iID_MaTrangThaiDuyet, Check);
data.TableName = "ChiTiet";
fr.AddTable("ChiTiet", data);
DataTable dtTieuMuc;
dtTieuMuc = HamChung.SelectDistinct("TieuMuc", data, "sLNS,sL,sK,sM,sTM" , "sLNS,sL,sK,sM,sTM,sTTM,sNG,sMoTa", "sLNS,sL,sK,sM,sTM,sTTM");
fr.AddTable("TieuMuc", dtTieuMuc);
DataTable dtMuc;
dtMuc = HamChung.SelectDistinct("Muc", dtTieuMuc, "sLNS,sL,sK,sM", "sLNS,sL,sK,sM,sTM,sMoTa", "sLNS,sL,sK,sM,sTM");
fr.AddTable("Muc", dtMuc);
DataTable dtNS;
dtNS = HamChung.SelectDistinct("LNS", dtMuc, "sLNS", "sLNS,sL,sK,sM,sMoTa", "sLNS,sL");
fr.AddTable("LNS", dtNS);
data.Dispose();
dtMuc.Dispose();
dtTieuMuc.Dispose();
dtNS.Dispose();
}
public static void SetupNOAA(FlexCelReport reportStart, string CityName, string DataPath, bool UseOfflineData, Dictionary <string, LatLong> Cities)
{
LatLong CityCoords;
GetCity(Cities, CityName, out CityCoords);
reportStart.SetValue("Date", DateTime.Now);
string forecasts;
DateTime dtStart = DateTime.Now;
if (UseOfflineData)
{
using (StreamReader fs = new StreamReader(Path.Combine(DataPath, "OfflineData.xml")))
{
forecasts = fs.ReadToEnd();
}
}
else
{
ndfdXML nd = new ndfdXML();
forecasts = nd.NDFDgen(CityCoords.Latitude, CityCoords.Longitude, productType.glance, dtStart, dtStart.AddDays(7), unitType.m, new weatherParametersType());
#if (SAVEOFFLINEDATA)
using (StreamWriter sw = new StreamWriter(Path.Combine(DataPath, "OfflineData.xml")))
{
sw.Write(forecasts);
}
#endif
}
if (String.IsNullOrEmpty(forecasts))
{
throw new Exception("Can't find the place " + CityName);
}
DataSet ds = new DataSet();
//Load the data into a dataset. On this web service, we cannot just call DataSet.ReadXml as the data is not on the correct format.
XmlDocument xmlDoc = new XmlDocument();
{
xmlDoc.LoadXml(forecasts);
XmlNodeList HighList = xmlDoc.SelectNodes("/dwml/data/parameters/temperature[@type='maximum']/value/text()");
XmlNodeList LowList = xmlDoc.SelectNodes("/dwml/data/parameters/temperature[@type='minimum']/value/text()");
XmlNodeList IconList = xmlDoc.SelectNodes("/dwml/data/parameters/conditions-icon/icon-link/text()");
DataTable WeatherTable = ds.Tables.Add("Weather");
WeatherTable.Columns.Add("Day", typeof(DateTime));
WeatherTable.Columns.Add("Low", typeof(double));
WeatherTable.Columns.Add("High", typeof(double));
WeatherTable.Columns.Add("Icon", typeof(byte[]));
for (int i = 0; i < Math.Min(Math.Min(HighList.Count, LowList.Count), IconList.Count); i++)
{
WeatherTable.Rows.Add(new object[] {
dtStart.AddDays(i),
Convert.ToDouble(LowList[i].Value),
Convert.ToDouble(HighList[i].Value),
LoadIcon(IconList[i].Value, UseOfflineData, DataPath)
});
}
}
reportStart.AddTable(ds, TDisposeMode.DisposeAfterRun);
reportStart.SetValue("Latitude", CityCoords.Latitude);
reportStart.SetValue("Longitude", CityCoords.Longitude);
reportStart.SetValue("Place", CityName);
}
private void LoadTables(FlexCelReport report)
{
var Countries = new List <Country>();
Countries.Add(new Country("China",
new People(1384688986),
new Geography(
new Area(270550, 9326410))));
var country = Countries[Countries.Count - 1];
country.People.Language.Add(new Language(
new LanguageName("Md", "Mandarin"),
new LanguageSpeakers(0, 66.2)));
country.People.Language.Add(new Language(
new LanguageName("Yue", "Yue"),
new LanguageSpeakers(0, 4.9)));
country.People.Language.Add(new Language(
new LanguageName("Wu", "Wu"),
new LanguageSpeakers(0, 6.1)));
country.People.Language.Add(new Language(
new LanguageName("Mb", "Minbei"),
new LanguageSpeakers(0, 6.2)));
country.People.Language.Add(new Language(
new LanguageName("Mn", "Minnan"),
new LanguageSpeakers(0, 5.2)));
country.People.Language.Add(new Language(
new LanguageName("Xi", "Xiang"),
new LanguageSpeakers(0, 3.0)));
country.People.Language.Add(new Language(
new LanguageName("Gan", "Gan"),
new LanguageSpeakers(0, 4.0)));
Countries.Add(new Country("India",
new People(1296834042),
new Geography(
new Area(314070, 2973193))));
country = Countries[Countries.Count - 1];
country.People.Language.Add(new Language(
new LanguageName("Hi", "Hindi"),
new LanguageSpeakers(0, 43.6)));
country.People.Language.Add(new Language(
new LanguageName("Bg", "Bengali"),
new LanguageSpeakers(0, 8)));
country.People.Language.Add(new Language(
new LanguageName("Ma", "Marath"),
new LanguageSpeakers(0, 6.9)));
country.People.Language.Add(new Language(
new LanguageName("Te", "Telugu"),
new LanguageSpeakers(0, 6.7)));
country.People.Language.Add(new Language(
new LanguageName("Ta", "Tamil"),
new LanguageSpeakers(0, 5.7)));
country.People.Language.Add(new Language(
new LanguageName("Gu", "Gujarati"),
new LanguageSpeakers(0, 4.6)));
country.People.Language.Add(new Language(
new LanguageName("Ur", "Urdu"),
new LanguageSpeakers(0, 4.2)));
country.People.Language.Add(new Language(
new LanguageName("Ka", "Kannada"),
new LanguageSpeakers(0, 3.6)));
country.People.Language.Add(new Language(
new LanguageName("Od", "Odia"),
new LanguageSpeakers(0, 3.1)));
country.People.Language.Add(new Language(
new LanguageName("Ma", "Malayalam"),
new LanguageSpeakers(0, 2.9)));
country.People.Language.Add(new Language(
new LanguageName("Pu", "Punjabi"),
new LanguageSpeakers(0, 2.7)));
country.People.Language.Add(new Language(
new LanguageName("As", "Assamese"),
new LanguageSpeakers(0, 1.3)));
country.People.Language.Add(new Language(
new LanguageName("Mi", "Maithili"),
new LanguageSpeakers(0, 1.1)));
country.People.Language.Add(new Language(
new LanguageName("O", "Other"),
new LanguageSpeakers(0, 5.6)));
Countries.Add(new Country("United States",
new People(329256465),
new Geography(
new Area(685924, 9147593))));
country = Countries[Countries.Count - 1];
country.People.Language.Add(new Language(
new LanguageName("En", "English"),
new LanguageSpeakers(0, 78.2)));
country.People.Language.Add(new Language(
new LanguageName("Sp", "Spanish"),
new LanguageSpeakers(0, 13.4)));
country.People.Language.Add(new Language(
new LanguageName("Ch", "Chinese"),
new LanguageSpeakers(0, 1.1)));
country.People.Language.Add(new Language(
new LanguageName("O", "Other"),
new LanguageSpeakers(0, 7.3)));
report.AddTable("country", Countries);
}
/// <summary>
/// hàm hiển thị dữ liệu ra báo cáo
/// </summary>
/// <param name="fr"></param>
/// <param name="NamLamViec"></param>
private void LoadData(FlexCelReport fr, String MaND, String iID_MaPhongBan)
{
DataRow r;
DataTable data = DT_rptDuToan_2_TongHop(MaND, iID_MaPhongBan);
data.TableName = "ChiTiet";
fr.AddTable("ChiTiet", data);
DataTable dtsTM = HamChung.SelectDistinct("dtsTM", data, "sLNS,sL,sK,sM,sTM", "sLNS,sL,sK,sM,sTM,sMoTa", "sLNS,sL,sK,sM,sTM,sTTM");
DataTable dtsM = HamChung.SelectDistinct("dtsM", dtsTM, "sLNS,sL,sK,sM", "sLNS,sL,sK,sM,sMoTa", "sLNS,sL,sK,sM,sTM");
DataTable dtsL = HamChung.SelectDistinct("dtsL", dtsM, "sLNS,sL,sK", "sLNS,sL,sK,sMoTa", "sLNS,sL,sK,sM");
DataTable dtsLNS = HamChung.SelectDistinct("dtsLNS", dtsL, "sLNS", "sLNS,sMoTa", "sLNS,sL");
fr.AddTable("dtsTM", dtsTM);
fr.AddTable("dtsM", dtsM);
fr.AddTable("dtsL", dtsL);
fr.AddTable("dtsLNS", dtsLNS);
data.Dispose();
dtsTM.Dispose();
dtsM.Dispose();
dtsL.Dispose();
dtsLNS.Dispose();
}
/// <summary>
/// Đổ dư liệu xuống báo cáo
/// </summary>
/// <param name="fr"></param>
/// <param name="NamLamViec"></param>
/// <param name="iID_MaDonVi"></param>
private void LoadData(FlexCelReport fr, String MaND, String iQuy, String bTrongKy, String iID_MaDoanhNghiep, String iLoai, String DVT)
{
DataRow r;
DataTable data = rptTCDN_BaoCaoChiTiet(MaND, iQuy, bTrongKy, iID_MaDoanhNghiep, iLoai, DVT);
data.TableName = "ChiTiet";
fr.AddTable("ChiTiet", data);
if (iLoai == "0")
{
String sTenDonViDauMoi = "", sTenKhoi = "", sTenNhom = "", sTenHinhThucHoatDong = "", sTenLoaiHinhDoanhNghiep = "";
if (data.Rows.Count > 0)
{
DataRow R = data.Rows[0];
sTenDonViDauMoi = DonViModels.Get_TenDonVi(Convert.ToString(R["iID_MaDonVi"]));
sTenKhoi = Convert.ToString(
DanhMucModels.GetRow_DanhMuc(HamChung.ConvertToString(R["iID_MaKhoi"])).Rows[0]["sTen"]);
sTenNhom = Convert.ToString(
DanhMucModels.GetRow_DanhMuc(HamChung.ConvertToString(R["iID_MaNhom"])).Rows[0]["sTen"]);
sTenHinhThucHoatDong = Convert.ToString(
DanhMucModels.GetRow_DanhMuc(HamChung.ConvertToString(R["iID_MaHinhThucHoatDong"])).Rows[0]["sTen"]);
sTenLoaiHinhDoanhNghiep = Convert.ToString(
DanhMucModels.GetRow_DanhMuc(HamChung.ConvertToString(R["iID_MaLoaiHinhDoanhNghiep"])).Rows[0]["sTen"]);
}
fr.SetValue("sTenDonViDauMoi", sTenDonViDauMoi);
fr.SetValue("sTenKhoi", sTenKhoi);
fr.SetValue("sTenNhom", sTenNhom);
fr.SetValue("sTenHinhThucHoatDong", sTenHinhThucHoatDong);
fr.SetValue("sTenLoaiHinhDoanhNghiep", sTenLoaiHinhDoanhNghiep);
//Linh Vuc
String SQL =
String.Format(
"SELECT * FROM TCDN_LinhVuc WHERE iTrangThai=1 AND iID_MaDoanhNghiep=@iID_MaDoanhNghiep ");
SqlCommand cmd = new SqlCommand();
cmd.CommandText = SQL;
cmd.Parameters.AddWithValue("@iID_MaDoanhNghiep", iID_MaDoanhNghiep);
DataTable dtLinhVuc = Connection.GetDataTable(cmd);
fr.AddTable("dtLinhVuc", dtLinhVuc);
dtLinhVuc.Dispose();
//Don vi hach toan doc lap
SQL =
String.Format(
"SELECT * FROM TCDN_DonViThanhVien WHERE iTrangThai=1 AND iLoai=1 AND iID_MaDoanhNghiep=@iID_MaDoanhNghiep ");
cmd = new SqlCommand();
cmd.CommandText = SQL;
cmd.Parameters.AddWithValue("@iID_MaDoanhNghiep", iID_MaDoanhNghiep);
DataTable dtDonVi1 = Connection.GetDataTable(cmd);
fr.AddTable("dtDonVi1", dtDonVi1);
dtDonVi1.Dispose();
//Don vi hach toan phụ thuộc
SQL =
String.Format(
"SELECT * FROM TCDN_DonViThanhVien WHERE iTrangThai=1 AND iLoai=2 AND iID_MaDoanhNghiep=@iID_MaDoanhNghiep ");
cmd = new SqlCommand();
cmd.CommandText = SQL;
cmd.Parameters.AddWithValue("@iID_MaDoanhNghiep", iID_MaDoanhNghiep);
DataTable dtDonVi2 = Connection.GetDataTable(cmd);
fr.AddTable("dtDonVi2", dtDonVi2);
dtDonVi2.Dispose();
//lien doanh lien ket
SQL =
String.Format(
"SELECT * FROM TCDN_CongTyLienDoanhLienKet WHERE iTrangThai=1 AND iID_MaDoanhNghiep=@iID_MaDoanhNghiep ");
cmd = new SqlCommand();
cmd.CommandText = SQL;
cmd.Parameters.AddWithValue("@iID_MaDoanhNghiep", iID_MaDoanhNghiep);
DataTable dtCongTyLienDoanhLienKet = Connection.GetDataTable(cmd);
fr.AddTable("dtCongTyLienDoanhLienKet", dtCongTyLienDoanhLienKet);
dtCongTyLienDoanhLienKet.Dispose();
cmd.Dispose();
}
data.Dispose();
}
private void LoadData(FlexCelReport fr, String NamLamViec, String ThangLamViec, String TrangThai)
{
DataTable data = KeToan_QuyetToanNam(NamLamViec, ThangLamViec, TrangThai);
data.TableName = "ChiTiet";
fr.AddTable("ChiTiet", data);
}
//<summary>
//Đẩy dữ liệu xuống báo cáo
//</summary>
//<param name="fr"></param>
//<param name="NamLamViec"></param>
//<param name="sNG"></param>
private void LoadData(FlexCelReport fr, String NamLamViec, String Thang, String optThu, String optTamUng, String optTra,String KhoGiay)
{
data _data = Get_dataGiaiThich(Thang, NamLamViec, optThu, optTamUng, optTra);
DataTable dtPhaiThu = _data.dtThu;
DataTable dtTamTung = _data.dtTamUng;
DataTable dtPhaiTra = _data.dtPhaiTra;
DataTable dt = new DataTable();
dt.Columns.Add("sNoiDung_ThuTamUng", typeof(String));
dt.Columns.Add("sLyDo_ThuTamUng", typeof(String));
dt.Columns.Add("rSoTien_ThuTamUng", typeof(decimal));
dt.Columns.Add("sNoiDung_PhaiTra", typeof(String));
dt.Columns.Add("sLyDo_PhaiTra", typeof(String));
dt.Columns.Add("rSoTien_PhaiTra", typeof(decimal));
dt.Columns.Add("InDam", typeof(string));
dt.Columns.Add("InDam1", typeof(string));
DataRow R;
decimal tong = 0;
//thu
R = dt.NewRow();
R["sLyDo_ThuTamUng"] = " 1.Các khoản phải thu-TK 311";
R["InDam"]="1";
dt.Rows.Add(R);
for (int i = 0; i < dtPhaiThu.Rows.Count; i++)
{
R = dt.NewRow();
R["sNoiDung_ThuTamUng"] = dtPhaiThu.Rows[i]["sNoiDung"].ToString();
R["sLyDo_ThuTamUng"] = dtPhaiThu.Rows[i]["sLyDo"].ToString();
R["rSoTien_ThuTamUng"] = dtPhaiThu.Rows[i]["rSoTien"].ToString();
tong += Convert.ToDecimal(dtPhaiThu.Rows[i]["rSoTien"]);
dt.Rows.Add(R);
}
R = dt.NewRow();
R["sLyDo_ThuTamUng"] = " Cộng";
R["rSoTien_ThuTamUng"] = tong.ToString();
R["InDam"] = "1";
dt.Rows.Add(R);
//tamung
R = dt.NewRow();
R["sLyDo_ThuTamUng"] = " 2.Các khoản tạm ứng -TK 312";
R["InDam"] = "1";
dt.Rows.Add(R);
tong = 0;
for (int i = 0; i < dtTamTung.Rows.Count; i++)
{
R = dt.NewRow();
R["sNoiDung_ThuTamUng"] = dtTamTung.Rows[i]["sNoiDung"].ToString();
R["sLyDo_ThuTamUng"] = dtTamTung.Rows[i]["sLyDo"].ToString();
R["rSoTien_ThuTamUng"] = dtTamTung.Rows[i]["rSoTien"].ToString();
tong += Convert.ToDecimal(dtTamTung.Rows[i]["rSoTien"]);
dt.Rows.Add(R);
}
R = dt.NewRow();
R["sLyDo_ThuTamUng"] = " Cộng";
R["rSoTien_ThuTamUng"] = tong.ToString();
R["InDam"] = "1";
dt.Rows.Add(R);
int a = dt.Rows.Count;
int b = dtPhaiTra.Rows.Count+1;
if (a < b)
{
for (int i = 0; i < (b - a);i++)
{
R = dt.NewRow();
dt.Rows.Add(R);
}
}
tong = 0;
for (int i = 0; i < dtPhaiTra.Rows.Count; i++)
{
DataRow r = dt.Rows[i];
r["sNoiDung_PhaiTra"] = dtPhaiTra.Rows[i]["sNoiDung"].ToString();
r["sLyDo_PhaiTra"] = dtPhaiTra.Rows[i]["sLyDo"].ToString();
r["rSoTien_PhaiTra"] = dtPhaiTra.Rows[i]["rSoTien"].ToString();
tong += Convert.ToDecimal(dtPhaiTra.Rows[i]["rSoTien"]);
}
//dong tong cong phai tra
DataRow r1 = dt.Rows[dtPhaiTra.Rows.Count];
r1["sLyDo_PhaiTra"] = " Cộng";
r1["rSoTien_PhaiTra"] = tong.ToString();
r1["InDam1"] = "1";
a = dt.Rows.Count;
int sodong;
if (KhoGiay == "1")
//sodong = 30;
sodong = 14;
else sodong = 13;
if (a < sodong)
{
for (int i = 0; i < sodong - a; i++)
{
R = dt.NewRow();
dt.Rows.Add(R);
}
}
int iR = _data.dtGiaiThich.Rows.Count;
if (iR < 10)
{
for (int i = 0; i < 10 - iR; i++)
{
R = _data.dtGiaiThich.NewRow();
_data.dtGiaiThich.Rows.Add(R);
}
}
fr.AddTable("ChiTiet", dt);
fr.AddTable("GiaiThich", _data.dtGiaiThich);
}
/// <summary>
/// Lấy dữ liệu chi tiết của quyết toán tổng hợp dữ liệu
/// </summary>
/// <param name="fr"></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>
private void LoadData(FlexCelReport fr, String MaND, String iThang_Quy, String iID_MaNamNganSach, String iID_MaPhongBan)
{
DataTable data = QuyetToan_ReportModels.rptQuyetToanTongHopNhapSoLieu(MaND, iThang_Quy, iID_MaNamNganSach, iID_MaPhongBan);
data.TableName = "ChiTiet";
fr.AddTable("ChiTiet", data);
DataTable dtDonVi = HamChung.SelectDistinct("dtDonVi", data, "sTenDonVi", "sTenDonVi");
dtDonVi.Columns.Add("STT");
for (int i = 0; i < dtDonVi.Rows.Count; i++)
{
dtDonVi.Rows[i]["STT"] = i + 1;
}
fr.AddTable("dtDonVi", dtDonVi);
data.Dispose();
dtDonVi.Dispose();
}
public async Task <byte[]> GoToViewPhieuTiepNhan(PhieuTiepNhanInputDto input)
{
try
{
byte[] fileTemplate = null;
ExcelFile xls = null;
XlsFile Result = new XlsFile(true);
using (FlexCelReport fr = new FlexCelReport(true))
{
string DuongDan = "/ThuTuc_37/PhieuTiepNhan.xlsx";
var path = _appFolders.PDFTemplate + DuongDan;
var hoso = await _hoSoRepos.GetAsync(input.HoSoId);
fr.SetValue("HoVaTen", hoso.TenNguoiDaiDien);
fr.SetValue("DiaChi", hoso.DiaChi);
fr.SetValue("DienThoai", hoso.SoDienThoai);
fr.SetValue("SoGiayTiepNhan", Utility.GetCodeRandom());
if (input.ListTaiLieuDaNhan.Count > 0)
{
fr.AddTable("ListTaiLieuDaNhan", input.ListTaiLieuDaNhan);
}
fr.SetValue("NgayHen", input.NgayHenCap.Value.ToString("dd/MM/yyyy"));
var listHoSoXyLyBoSung = _hoSoXuLyRepos.GetAll().Where(x => x.HoSoId == hoso.Id && x.IsHoSoBS == true).ToList();
List <TiepNhanBoSungReport37Dto> ListTiepNhanBoSung = new List <TiepNhanBoSungReport37Dto>();
if (listHoSoXyLyBoSung.Count > 0)
{
if (listHoSoXyLyBoSung.Count == 1) // lần đầu nộp bổ xung
{
ListTiepNhanBoSung.Add(new TiepNhanBoSungReport37Dto()
{
SoLanBoSung = listHoSoXyLyBoSung.Count,
NgayTraGiayTiepNhanStr = "ngày " + DateTime.Now.Day + " tháng " + DateTime.Now.Month + " năm " + DateTime.Now.Year
});
}
else
{
for (int i = 0; i < listHoSoXyLyBoSung.Count; i++)
{
var NguoiNhan = _userRepos.Get(listHoSoXyLyBoSung[i].VanThuId.Value);
var tiepNhanBoSungReport37Dto = new TiepNhanBoSungReport37Dto()
{
SoLanBoSung = i + 1,
NgayTraGiayTiepNhanStr = listHoSoXyLyBoSung[i].NgayTraGiayTiepNhan.Value.ToShortDateString()
};
ListTiepNhanBoSung.Add(tiepNhanBoSungReport37Dto);
}
ListTiepNhanBoSung.Add(new TiepNhanBoSungReport37Dto()
{
SoLanBoSung = listHoSoXyLyBoSung.Count + 1,
NgayTraGiayTiepNhanStr = DateTime.Now.ToShortDateString()
});
}
}
fr.AddTable("ListTiepNhanBoSung", ListTiepNhanBoSung);
var currentUser = _userRepos.Get(_session.UserId.Value);
fr.SetValue("NguoiKy", currentUser.Surname + " " + currentUser.Name);
var day = String.Empty;
var month = String.Empty;
if (DateTime.Today.Day <= 9)
{
day = string.Format("0{0}", DateTime.Today.Day);
}
else
{
day = DateTime.Today.Day.ToString();
}
if (DateTime.Today.Month <= 9)
{
month = string.Format("0{0}", DateTime.Today.Month);
}
else
{
month = DateTime.Today.Month.ToString();
}
string NgayKy = "Hà nội, ngày " + day + " tháng " + month + " năm " + DateTime.Today.Year;
if (currentUser.TinhId.HasValue)
{
var tinh = _tinhRepos.Get(currentUser.TinhId.Value);
NgayKy = tinh.Ten + ", ngày " + DateTime.Today.Day + " tháng " + DateTime.Today.Month + " năm " + DateTime.Today.Year;
}
fr.SetValue("NgayKy", NgayKy);
Result.Open(path);
fr.Run(Result);
fr.Dispose();
xls = Result;
var outS = new System.IO.MemoryStream();
using (FlexCel.Render.FlexCelPdfExport pdf = new FlexCel.Render.FlexCelPdfExport(xls, true))
{
pdf.Export(outS);
}
fileTemplate = outS.ToArray();
return(fileTemplate);
}
}
catch (Exception ex)
{
Logger.Fatal(ex.Message);
return(null);
}
}
private void LoadData(FlexCelReport fr, String NamLamViec, String Thang_Quy, String LoaiThang_Quy, String inmuc, String iID_MaNguonNganSach, String iID_MaNamNganSach, String iID_MaTrangThaiDuyet)
{
DataTable data = DoiChieuDuToan(NamLamViec, Thang_Quy, LoaiThang_Quy, inmuc, iID_MaNguonNganSach, iID_MaNamNganSach, iID_MaTrangThaiDuyet);
data.TableName = "ChiTiet";
fr.AddTable("ChiTiet", data);
DataTable dtTieuMuc;
dtTieuMuc = HamChung.SelectDistinct("TM", data, "sL,sK,sM,sTM", "sL,sK,sM,sTM");
fr.AddTable("TM", dtTieuMuc);
DataTable dtMuc;
dtMuc = HamChung.SelectDistinct("Muc", dtTieuMuc, "sL,sK,sM", "sL,sK,sM");
fr.AddTable("Muc", dtMuc);
DataTable dtLoaiNS;
dtLoaiNS = HamChung.SelectDistinct("LoaiNS", dtMuc, "sL,sK", "sL,sK");
fr.AddTable("Khoan", dtLoaiNS);
data.Dispose();
dtLoaiNS.Dispose();
dtMuc.Dispose();
dtTieuMuc.Dispose();
}
/// <summary>
/// Đẩy dữ liệu xuống báo cáo
/// </summary>
/// <param name="fr">File báo cáo</param>
/// <param name="iLoaiThang_Quy">0: Chọn tháng | 1: Chọn quý</param>
/// <param name="iThang">Tháng</param>
/// <param name="iQuy">Quý</param>
/// <param name="iNguonNS">Nguồn ngân sách</param>
/// <param name="iMuc_TieuMuc">In đến mức: Mục | Tiểu mục</param>
/// <param name="iNam">Năm</param>
public void LoadData(FlexCelReport fr, String iLoaiThang_Quy, String iThang, String iQuy, String iNguonNS, String iMuc_TieuMuc, String iNam, String iTrangThai)
{
DataTable dtResult = GetTamUng(iLoaiThang_Quy, iThang, iQuy, iNguonNS, iMuc_TieuMuc, iNam,iTrangThai);
fr.AddTable("ChiTiet", dtResult);
dtResult.Dispose();
}
/// <summary>
/// tạo range báo cáo
/// </summary>
/// <param name="fr"></param>
/// <param name="NamLamViec"></param>
/// <param name="iID_MaDonVi"></param>
/// <param name="dNgayCapPhat"></param>
/// <param name="iDM_MaLoaiCapPhat"></param>
/// <param name="LuyKe"></param>
/// <param name="LoaiBaoCao"></param>
private void LoadData(FlexCelReport fr, String iID_MaTrangThaiDuyet, String iID_MaDonVi, String dNgayCapPhat, String iDM_MaLoaiCapPhat, String LuyKe, String LoaiBaoCao,String MaND)
{
DataTable data = CapPhat_79_3(iID_MaTrangThaiDuyet, iID_MaDonVi, dNgayCapPhat, iDM_MaLoaiCapPhat, LuyKe, LoaiBaoCao,MaND);
data.TableName = "ChiTiet";
fr.AddTable("ChiTiet", data);
DataTable dtTieuMuc;
dtTieuMuc = HamChung.SelectDistinct("TieuMuc", data, "NguonNS,sLNS,sL,sK,sM,sTM", "NguonNS,sLNS,sL,sK,sM,sTM,sMoTa", "sLNS,sL,sK,sM,sTM,sTTM");
fr.AddTable("TieuMuc", dtTieuMuc);
DataTable dtMuc;
dtMuc = HamChung.SelectDistinct("Muc", dtTieuMuc, "NguonNS,sLNS,sL,sK,sM", "NguonNS,sLNS,sL,sK,sM,sMoTa", "sLNS,sL,sK,sM,sTM");
fr.AddTable("Muc", dtMuc);
DataTable dtLoaiNS;
dtLoaiNS = HamChung.SelectDistinct("LoaiNS", dtMuc, "NguonNS,sLNS", "NguonNS,sLNS,sL,sK,sMoTa", "sLNS,sL");
fr.AddTable("LoaiNS", dtLoaiNS);
DataTable dtNguonNS;
dtNguonNS = HamChung.SelectDistinct("NguonNS", dtMuc, "NguonNS", "NguonNS,sMoTa", "sLNS,sL", "NguonNS");
fr.AddTable("NguonNS", dtNguonNS);
data.Dispose();
dtLoaiNS.Dispose();
dtMuc.Dispose();
dtNguonNS.Dispose();
dtTieuMuc.Dispose();
}
/// <summary>
/// Đẩy dữ liệu xuống báo cáo
/// </summary>
/// <param name="fr">File báo cáo</param>
/// <param name="iThang">Tháng</param>
/// <param name="iNam">Năm</param>
/// <param name="iAll">Tổng hợp tất cả đơn vị</param>
/// <param name="iMaDV">Mã đơn vị</param>
private void LoadData(FlexCelReport fr, String iThang, String iID_MaTrangThaiDuyet, String iAll, String iMaDV,String MaND)
{
DataTable dtQuanSo = QuanSo_47(iThang, iID_MaTrangThaiDuyet, iAll, iMaDV,MaND);
//DataTable dtGroup = HamChung.SelectDistinct("Group", dtQuanSo, "TK1", "TK1,TK2,sMoTa,sKyHieu,rThieuUy,rTrungUy,rThuongUy,rDaiUy,rThieuTa,rTrungTa,rThuongTa,rDaiTa,rTuong,rBinhNhi,rBinhNhat,rHaSi,rTrungSi,rThuongSi,rQNCN,rTSQ,rCNVQPCT,rQNVQPHD");
//SplitNULL(dtQuanSo);
//dtGroup.DefaultView.Sort = "sKyHieu ASC";
//dtGroup = dtGroup.DefaultView.ToTable();
fr.AddTable("ChiTiet2", dtQuanSo);
//fr.AddTable("Group", dtGroup);
//dtGroup.Dispose();
dtQuanSo.Dispose();
}
public ActionResult XuatExcel(string tungay, string denngay)
{
IQueryable <SanPham> model = db.SanPhams;
if (!string.IsNullOrEmpty(tungay) || !string.IsNullOrEmpty(denngay))
{
string dateFrom = tungay.Split('-')[1] + "/" + tungay.Split('-')[2] + "/" + tungay.Split('-')[0];
var _tungay = Convert.ToDateTime(dateFrom);
if (!string.IsNullOrEmpty(denngay))
{
string dateTo = denngay.Split('-')[1] + "/" + denngay.Split('-')[2] + "/" + denngay.Split('-')[0];
var _denngay = Convert.ToDateTime(dateTo).AddDays(1).AddMilliseconds(-1);
model = model.Where(n => n.NgayTao >= _tungay && n.NgayTao <= _denngay);
}
else
{
string dateTo = DateTime.Now.ToShortDateString();
var _denngay = Convert.ToDateTime(dateTo).AddDays(1).AddMilliseconds(-1);
model = model.Where(n => n.NgayTao >= _tungay && n.NgayTao <= _denngay);
}
ViewBag.dem = model.Count();
try
{
if (model.Count() >= 1)
{
using (FlexCelReport fr = new FlexCelReport(true))
{
fr.SetValue("tungay", tungay);
fr.SetValue("denngay", denngay);
fr.AddTable("sanpham", model.ToList());
string uploadPath = Server.MapPath("~/Content/Template");
var luufile = @"E:/STYDY/DATN/Export";
//Nếu chưa tồn tại thì tạo ra folder mới
if (!Directory.Exists(luufile))
{
Directory.CreateDirectory(luufile);
}
fr.Run(
Path.Combine(uploadPath + "/Book.xlsx"),
Path.Combine(luufile + "/Sanpham_" + DateTime.Now.ToString("dd_MM_yy_hh_mm_ss") + ".xlsx")
);
}
SetAlert("Xuất thành công " + model.Count() + " sản phẩm!", "success");
logger.Info("Xuất thành công " + model.Count() + " sản phẩm!");
}
else
{
SetAlert("Không có sản phẩm nào được tìm thấy!", "error");
logger.Error("Không có sản phẩm nào được tìm thấy!");
}
}
catch (Exception ex)
{
SetAlert("Xuất thống kê báo cáo thất bại!", "error");
logger.Error("Xuất thống kê báo cáo thất bại!" + ex.Message);
}
}
return(View("XuatExcel"));
}
/// <summary>
/// Đẩy dữ xuống báo cáo
/// </summary>
/// <param name="fr">File báo cao</param>
/// <param name="iQuy">Quý</param>
/// <param name="iNam">Năm</param>
/// <param name="iAll">Tổng hợp theo quý hay theo năm</param>
/// <param name="iMaDN">Mã doanh nghiệp</param>
private void LoadData(FlexCelReport fr, String iQuy, String iNam, String iAll, String iMaDN)
{
DataTable data = BC_MS_ChiTieuTaiChinh(iQuy, iNam, iAll, iMaDN);
data.TableName = "ChiTiet";
fr.AddTable("ChiTiet", data);
data.Dispose();
}
private void LoadData(FlexCelReport fr, String MaND, String ThangQuy, String LoaiThang_Quy, String iID_MaDonVi, String iID_MaNhomDonVi, String LuyKe, String iID_MaTrangThaiDuyet, String KhoGiay, String BaoHiem,String iLoai)
{
if (String.IsNullOrEmpty(iID_MaDonVi))
{
iID_MaDonVi = Guid.Empty.ToString();
}
if (BaoHiem == "0" )
{
DataTable data = rptBH_TongQuyetToan(MaND, ThangQuy, LoaiThang_Quy, iID_MaDonVi, iID_MaNhomDonVi, LuyKe, iID_MaTrangThaiDuyet, KhoGiay, BaoHiem,iLoai);
SortedDictionary<string, Hashtable> DuLieu = LayDuLieu_HasTable(data);
foreach (DataRow row in data.Rows)
{
if (!String.IsNullOrEmpty(Convert.ToString(row["sKyHieu"])))
{
Hashtable hs = DuLieu[Convert.ToString(row["sKyHieu"])];
row["rBHXH_CN"] = hs["rBHXH_CN"];
row["rBHXH_DV"] = hs["rBHXH_DV"];
row["rSoNguoi"] = hs["rSoNguoi"];
}
data.TableName = "ChiTiet";
fr.AddTable("ChiTiet", data);
data.Dispose();
}
}
else if (BaoHiem == "1")
{
DataTable data = rptBH_TongQuyetToan(MaND, ThangQuy, LoaiThang_Quy, iID_MaDonVi, iID_MaNhomDonVi, LuyKe, iID_MaTrangThaiDuyet, KhoGiay, BaoHiem, iLoai);
SortedDictionary<string, Hashtable> DuLieu = LayDuLieu_HasTable(data);
foreach (DataRow row in data.Rows)
{
if (!String.IsNullOrEmpty(Convert.ToString(row["sKyHieu"])))
{
Hashtable hs = DuLieu[Convert.ToString(row["sKyHieu"])];
row["rBHYT_CN"] = hs["rBHYT_CN"];
row["rBHYT_DV"] = hs["rBHYT_DV"];
row["rSoNguoi"] = hs["rSoNguoi"];
}
data.TableName = "ChiTiet";
fr.AddTable("ChiTiet", data);
data.Dispose();
}
}
else if (BaoHiem == "2")
{
DataTable data = rptBH_TongQuyetToan(MaND, ThangQuy, LoaiThang_Quy, iID_MaDonVi, iID_MaNhomDonVi, LuyKe, iID_MaTrangThaiDuyet, KhoGiay, BaoHiem, iLoai);
SortedDictionary<string, Hashtable> DuLieu = LayDuLieu_HasTable(data);
foreach (DataRow row in data.Rows)
{
if (!String.IsNullOrEmpty(Convert.ToString(row["sKyHieu"])))
{
Hashtable hs = DuLieu[Convert.ToString(row["sKyHieu"])];
row["rBHTN_CN"] = hs["rBHTN_CN"];
row["rBHTN_DV"] = hs["rBHTN_DV"];
row["rSoNguoi"] = hs["rSoNguoi"];
}
data.TableName = "ChiTiet";
fr.AddTable("ChiTiet", data);
data.Dispose();
}
}
else if (BaoHiem == "3")
{
DataTable data = rptBH_TongQuyetToan(MaND, ThangQuy, LoaiThang_Quy, iID_MaDonVi, iID_MaNhomDonVi, LuyKe, iID_MaTrangThaiDuyet, KhoGiay, BaoHiem, iLoai);
SortedDictionary<string, Hashtable> DuLieu = LayDuLieu_HasTable(data);
foreach (DataRow row in data.Rows)
{
if (!String.IsNullOrEmpty(Convert.ToString(row["sKyHieu"])))
{
Hashtable hs = DuLieu[Convert.ToString(row["sKyHieu"])];
row["rBHXH_CN"] = hs["rBHXH_CN"];
row["rBHYT_CN"] = hs["rBHYT_CN"];
row["rBHTN_CN"] = hs["rBHTN_CN"];
row["rSoNguoi"] = hs["rSoNguoi"];
}
data.TableName = "ChiTiet";
fr.AddTable("ChiTiet", data);
data.Dispose();
}
}
else if (BaoHiem == "4")
{
DataTable data = rptBH_TongQuyetToan(MaND, ThangQuy, LoaiThang_Quy, iID_MaDonVi, iID_MaNhomDonVi, LuyKe, iID_MaTrangThaiDuyet, KhoGiay, BaoHiem, iLoai);
SortedDictionary<string, Hashtable> DuLieu = LayDuLieu_HasTable(data);
foreach (DataRow row in data.Rows)
{
if (!String.IsNullOrEmpty(Convert.ToString(row["sKyHieu"])))
{
Hashtable hs = DuLieu[Convert.ToString(row["sKyHieu"])];
row["rBHXH_DV"] = hs["rBHXH_DV"];
row["rBHYT_DV"] = hs["rBHYT_DV"];
row["rBHTN_DV"] = hs["rBHTN_DV"];
row["rSoNguoi"] = hs["rSoNguoi"];
}
data.TableName = "ChiTiet";
fr.AddTable("ChiTiet", data);
data.Dispose();
}
}
else
{
DataTable data = rptBH_TongQuyetToan(MaND, ThangQuy, LoaiThang_Quy, iID_MaDonVi, iID_MaNhomDonVi, LuyKe, iID_MaTrangThaiDuyet, KhoGiay, BaoHiem, iLoai);
SortedDictionary<string, Hashtable> DuLieu = LayDuLieu_HasTable(data);
foreach (DataRow row in data.Rows)
{
if (!String.IsNullOrEmpty(Convert.ToString(row["sKyHieu"])))
{
Hashtable hs = DuLieu[Convert.ToString(row["sKyHieu"])];
row["rBHXH_CN"] = hs["rBHXH_CN"];
row["rBHXH_DV"] = hs["rBHXH_DV"];
row["rBHYT_CN"] = hs["rBHYT_CN"];
row["rBHTN_CN"] = hs["rBHTN_CN"];
row["rBHYT_DV"] = hs["rBHYT_DV"];
row["rBHTN_DV"] = hs["rBHTN_DV"];
row["rSoNguoi"] = hs["rSoNguoi"];
}
data.TableName = "ChiTiet";
fr.AddTable("ChiTiet", data);
data.Dispose();
}
}
DataTable data1 = DoanhNghiep(MaND, ThangQuy, LoaiThang_Quy, iID_MaDonVi, iID_MaNhomDonVi, LuyKe, iID_MaTrangThaiDuyet, KhoGiay, BaoHiem, iLoai);
data1.TableName = "ChiTiet1";
fr.AddTable("ChiTiet1", data1);
data1.Dispose();
DataTable data2 = TongCongCNDV(MaND, ThangQuy, LoaiThang_Quy, iID_MaDonVi, iID_MaNhomDonVi, LuyKe, iID_MaTrangThaiDuyet, KhoGiay, BaoHiem, iLoai);
data2.TableName = "ChiTiet2";
fr.AddTable("ChiTiet2", data2);
data2.Dispose();
DataTable data3 = DaNop(MaND, ThangQuy, LoaiThang_Quy, iID_MaDonVi, iID_MaNhomDonVi, LuyKe, iID_MaTrangThaiDuyet, KhoGiay, BaoHiem, iLoai);
data3.TableName = "DaNop";
fr.AddTable("DaNop", data3);
data3.Dispose();
DataTable data4 = DonViDuToan(MaND, ThangQuy, LoaiThang_Quy, iID_MaDonVi, iID_MaNhomDonVi, LuyKe, iID_MaTrangThaiDuyet, KhoGiay, BaoHiem, iLoai);
data4.TableName = "ChiTiet3";
fr.AddTable("ChiTiet3", data4);
data4.Dispose();
}
private void LoadData(FlexCelReport fr, String MaND, String iID_MaTrangThaiDuyet, String iNgay, String iThang, String DonViTinh, String iNamLamViec, String NoiDung)
{
if (NoiDung != "0")// khong la ngoai te
{
DataTable data = dtKTTongHop_ChiTietTamThu_TienViet(iNamLamViec, iThang, iNgay, DonViTinh,
iID_MaTrangThaiDuyet, NoiDung);
data.TableName = "ChiTiet";
fr.AddTable("ChiTiet", data);
data.Dispose();
DataTable dtDonViNew = HamChung.SelectDistinct("ChiTietCha", data, "sKyHieu_2", "sKyHieu_2,sTenCapCha",
"sKyHieu_2", "");
dtDonViNew.TableName = "ChiTietCha";
fr.AddTable("ChiTietCha", dtDonViNew);
dtDonViNew.Dispose();
}
if (NoiDung == "0" || NoiDung == "-1")// la ngoai te
{
DataTable dataNgoaiTe = dtKTTongHop_ChiTietTamThu_NgoaiTe(iNamLamViec, iThang, iNgay, DonViTinh,
iID_MaTrangThaiDuyet,NoiDung);
dataNgoaiTe.TableName = "ChiTietNgoaiTe";
fr.AddTable("ChiTietNgoaiTe", dataNgoaiTe);
dataNgoaiTe.Dispose();
}
}
/// <summary>
/// Lấy dữ liệu chi tiết của dự toán tổng hợp
/// </summary>
/// <param name="fr">Flecel report</param>
/// <param name="maND">Mã người dùng</param>
/// <param name="sLNS">Mã loại ngân sách</param>
/// <param name="maDonVi">Mã đơn vị</param>
/// <param name="maDot">Mã đợt dự toán</param>
/// <param name="maPhongBan">Mã phòng ban</param>
/// <param name="loaiBaoCao">Loại báo cáo tổng hợp hay chi tiết</param>
/// HungPH: 2015/11/16
private void LoadData(FlexCelReport fr, String maND, String sLNS, String maDonVi, String maDot, String maPhongBan, String loaiBaoCao)
{
DataTable dtDonVi = new DataTable();
dtDonVi = DuToan_ReportModels.rptDuToanTongHopPhongBanDonVi(maND, sLNS, maDonVi, maDot, maPhongBan, loaiBaoCao);
DataTable data = HamChung.SelectDistinct("ChiTiet", dtDonVi, "sLNS,sL,sK,sM,sTM,sTTM,sNG", "sLNS,sL,sK,sM,sTM,sTTM,sNG,sMoTa");
data.TableName = "ChiTiet";
DataTable dtsTM = HamChung.SelectDistinct("dtsTM", data, "sLNS,sL,sK,sM,sTM", "sLNS,sL,sK,sM,sTM,sMoTa", "sLNS,sL,sK,sM,sTM,sTTM");
DataTable dtsM = HamChung.SelectDistinct("dtsM", dtsTM, "sLNS,sL,sK,sM", "sLNS,sL,sK,sM,sMoTa", "sLNS,sL,sK,sM,sTM");
DataTable dtsL = HamChung.SelectDistinct("dtsL", dtsM, "sLNS,sL,sK", "sLNS,sL,sK,sMoTa", "sLNS,sL,sK,sM");
DataTable dtsLNS = HamChung.SelectDistinct("dtsLNS", dtsL, "sLNS", "sLNS,sMoTa", "sLNS,sL");
fr.AddTable("dtsTM", dtsTM);
fr.AddTable("dtsM", dtsM);
fr.AddTable("dtsL", dtsL);
fr.AddTable("dtsLNS", dtsLNS);
fr.AddTable("ChiTiet", data);
fr.AddTable("dtDonVi", dtDonVi);
dtDonVi.Dispose();
data.Dispose();
dtsTM.Dispose();
dtsM.Dispose();
dtsL.Dispose();
dtsLNS.Dispose();
}
/// <summary>
/// set các Range trong báo cáo
/// </summary>
/// <param name="fr"></param>
/// <param name="NamLamViec"></param>
/// <param name="iID_MaDonVi"></param>
/// <param name="DotPhanBo"></param>
private void LoadData(FlexCelReport fr, String MaND, String iID_MaTrangThaiDuyet, String iID_MaDonVi, String DotPhanBo)
{
DataTable data = PB_BoSungNganSach(MaND, iID_MaTrangThaiDuyet, iID_MaDonVi, DotPhanBo);
data.TableName = "ChiTiet";
fr.AddTable("ChiTiet", data);
DataTable dtTieuMuc;
dtTieuMuc = HamChung.SelectDistinct("TieuMuc", data, "NguonNS,sLNS,sL,sK,sM,sTM", "NguonNS,sLNS,sL,sK,sM,sTM,sMoTa", "sLNS,sL,sK,sM,sTM,sTTM");
fr.AddTable("TieuMuc", dtTieuMuc);
DataTable dtMuc;
dtMuc = HamChung.SelectDistinct("Muc", dtTieuMuc, "NguonNS,sLNS,sL,sK,sM", "NguonNS,sLNS,sL,sK,sM,sMoTa", "sLNS,sL,sK,sM,sTM");
fr.AddTable("Muc", dtMuc);
DataTable dtLoaiNS;
dtLoaiNS = HamChung.SelectDistinct("LoaiNS", dtMuc, "NguonNS,sLNS", "NguonNS,sLNS,sMoTa", "sLNS,sL");
fr.AddTable("LoaiNS", dtLoaiNS);
DataTable dtNguonNS;
dtNguonNS = HamChung.SelectDistinct("NguonNS", dtMuc, "NguonNS", "NguonNS,sMoTa", "sLNS,sL", "NguonNS");
fr.AddTable("NguonNS", dtNguonNS);
dtLoaiNS.Dispose();
dtMuc.Dispose();
dtNguonNS.Dispose();
dtTieuMuc.Dispose();
}
private void LoadData(FlexCelReport fr, String MaND, String sLNS, String iID_MaDotPhanBo, String iID_MaTrangThaiDuyet, String TruongTien, String KhoGiay, String LuyKe, String ToSo)
{
DataTable data = TongHopChiTieuDonVi(MaND, sLNS, iID_MaDotPhanBo, iID_MaTrangThaiDuyet, TruongTien, KhoGiay, LuyKe, ToSo);
data.TableName = "ChiTiet";
fr.AddTable("ChiTiet", data);
if (LuyKe == "on")
{
DataTable data_LK = dtDenKyNay(MaND, sLNS, iID_MaDotPhanBo, iID_MaTrangThaiDuyet, TruongTien, KhoGiay, LuyKe, ToSo);
if (data_LK.Rows.Count == 0)
{
DataRow dr = data_LK.NewRow();
data_LK.Rows.InsertAt(dr, 0);
}
fr.AddTable("LuyKe", data_LK);
data_LK.Dispose();
}
DataTable dtTieuMuc;
dtTieuMuc = HamChung.SelectDistinct("TieuMuc", data, "NguonNS,sLNS,sL,sK,sM,sTM", "NguonNS,sLNS,sL,sK,sM,sTM,sNG,sMoTa", "sLNS,sL,sK,sM,sTM,sTTM");
fr.AddTable("TieuMuc", dtTieuMuc);
DataTable dtMuc;
dtMuc = HamChung.SelectDistinct("Muc", dtTieuMuc, "NguonNS,sLNS,sL,sK,sM", "NguonNS,sLNS,sL,sK,sM,sMoTa", "sLNS,sL,sK,sM,sTM");
fr.AddTable("Muc", dtMuc);
DataTable dtLoaiNS;
dtLoaiNS = HamChung.SelectDistinct("LoaiNS", dtMuc, "NguonNS,sLNS", "NguonNS,sLNS,sL,sK,sMoTa", "sLNS,sL");
fr.AddTable("LoaiNS", dtLoaiNS);
if (dtLoaiNS.Rows.Count == 0)
{
DataRow dr = dtLoaiNS.NewRow();
dtLoaiNS.Rows.InsertAt(dr, 0);
}
DataTable dtNguonNS;
dtNguonNS = HamChung.SelectDistinct("NguonNS", dtLoaiNS, "NguonNS", "NguonNS,sMoTa", "sLNS,sL", "NguonNS");
if (dtNguonNS.Rows.Count == 0)
{
DataRow dr = dtNguonNS.NewRow();
dtNguonNS.Rows.InsertAt(dr, 0);
}
fr.AddTable("NguonNS", dtNguonNS);
dtLoaiNS.Dispose();
dtMuc.Dispose();
dtNguonNS.Dispose();
dtTieuMuc.Dispose();
}
public void AddTable <T>(string tableName, IEnumerable <T> value)
{
_report.AddTable(tableName, value);
}
