public static DataTable getListHoaDonReport(string danhba, int rows) { DocSoDataContext db = new DocSoDataContext(); DataSet ds = new DataSet(); string query = " SELECT top(1) ( CASE WHEN hd.KY<10 THEN CONVERT(VARCHAR(20),hd.KY) ELSE CONVERT(VARCHAR(20),hd.KY) END+'/' + CONVERT(VARCHAR(20),hd.NAM)) as NAM , CONVERT(NCHAR(10), hd.DenNgay, 103) AS NGAYDOC, CodeMoi, hd.CSCU, hd.CSMOI, hd.TieuThuMoi as TIEUTHU, 0.0 as ThanhTien "; query += " ,N'Đọc số' AS ThanhToan "; query += " FROM dbo.DocSo hd "; query += " WHERE DANHBA= '" + danhba + "' "; query += " ORDER BY hd.Nam desc,CAST(hd.KY as int) DESC "; SqlDataAdapter adapter = new SqlDataAdapter(query, db.Connection.ConnectionString); adapter.Fill(ds, "TIEUTHU"); query = " SELECT top(" + rows + ") ( CASE WHEN hd.KY<10 THEN '0'+ CONVERT(VARCHAR(20),hd.KY) ELSE CONVERT(VARCHAR(20),hd.KY) END+'/' + CONVERT(VARCHAR(20),hd.NAM)) as NAM , CONVERT(NCHAR(10), hd.DenNgay, 103) AS NGAYDOC, CODE as CodeMoi, cast(hd.CSCU as int) as CSCU, cast(hd.CSMOI as int) as CSMOI,cast(hd.TIEUTHU as int) AS TIEUTHU , (hd.PHI + hd.THUE +hd.GIABAN) as ThanhTien "; query += " ,CASE WHEN NGAYGIAITRACH IS NULL OR NGAYGIAITRACH ='' THEN '' ELSE 'x' END AS ThanhToan "; query += " FROM dbo.HOADON hd "; query += " WHERE DANHBA= '" + danhba + "' "; query += " ORDER BY hd.Nam desc,CAST(hd.KY as int) DESC "; DataTable TB_HD = getDataTableHoaDon(query); ds.Tables["TIEUTHU"].Merge(TB_HD); return(ds.Tables["TIEUTHU"]); }
public static string getNVDS(string may) { try { DocSoDataContext db = new DocSoDataContext(); DataSet ds = new DataSet(); string query = " SELECT NhanVienID "; query += " FROM MayDS "; query += " WHERE May= '" + may + "' "; SqlDataAdapter adapter = new SqlDataAdapter(query, db.Connection.ConnectionString); adapter.Fill(ds, "MayDS"); return(ds.Tables["MayDS"].Rows[0][0].ToString()); } catch (Exception ex) { } return(""); }