/// <summary>
/// Lấy dữ liệu chi tiết của cấp phát thông tri
/// </summary>
/// <param name="fr"></param>
/// <param name="MaND">Mã người dùng</param>
/// <param name="sLNS">Loại ngân sách</param>
/// <param name="iNamCapPhat">Đợt cấp phát</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>
/// VungNV: 2015/11/12
private void LoadData(FlexCelReport fr, String MaND, String sLNS, String iNamCapPhat, String iID_MaDonVi, String LoaiTongHop)
{
int SoDong = 0;
DataRow r;
DataTable data = new DataTable();
DataTable dtDonVi = new DataTable();
//Hiển thị chi tiết
if (LoaiTongHop == "ChiTiet")
{
data = CapPhat_ReportModels.rptCapPhat_ThongTri(MaND, sLNS, iNamCapPhat, iID_MaDonVi, LoaiTongHop);
}
//Hiển thị tổng hợp
else
{
dtDonVi = CapPhat_ReportModels.rptCapPhat_ThongTri(MaND, sLNS, iNamCapPhat, iID_MaDonVi, LoaiTongHop);
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"]));
}
long TongTien = 0;
if (LoaiTongHop == "ChiTiet")
{
for (int i = 0; i < data.Rows.Count; i++)
{
if (data.Rows[i]["rTuChi"].ToString() != "")
{
TongTien += long.Parse(data.Rows[i]["rTuChi"].ToString());
}
}
}
else
{
for (int i = 0; i < dtDonVi.Rows.Count; i++)
{
if (dtDonVi.Rows[i]["rTuChi"].ToString() != "")
{
TongTien += long.Parse(dtDonVi.Rows[i]["rTuChi"].ToString());
}
}
}
//In loại tiền bằng chữ
String Tien = "";
Tien = CommonFunction.TienRaChu(TongTien).ToString();
//Ghi chú
DataTable dt = new DataTable();
dt.Columns.Add("sGhiChu", typeof(String));
int soChu1Trang = 80;
//Lấy giá trị ghi chú của đơn vị
String sGhiChu = CapPhat_ReportModels.LayGhiChu(MaND, iID_MaDonVi);
ArrayList arrDongTong = new ArrayList();
String[] arrDong = Regex.Split(sGhiChu, " ");
for (int i = 0; i < arrDong.Length; i++)
{
if (arrDong[i] != "")
{
int tg = 0;
String s = "";
String[] arrDongCon = arrDong[i].Split(' ');
for (int j = 0; j < arrDongCon.Length; j++)
{
int x = arrDongCon[j].Length;
tg = tg + x + 1;
if (tg > soChu1Trang)
{
arrDongTong.Add(s);
j--;
tg = 0;
s = "";
continue;
}
s += arrDongCon[j].Trim() + " ";
}
if (tg <= soChu1Trang)
arrDongTong.Add(s);
}
}
for (int j = 0; j < arrDongTong.Count; j++)
{
r = dt.NewRow();
r["sGhiChu"] = arrDongTong[j];
dt.Rows.Add(r);
}
SoDong = data.Rows.Count;
for (int i = 0; i < dtsTM.Rows.Count; i++)
{
if (!String.IsNullOrEmpty(Convert.ToString(dtsTM.Rows[i]["sMoTa"])))
SoDong++;
}
for (int i = 0; i < dtsM.Rows.Count; i++)
{
if (!String.IsNullOrEmpty(Convert.ToString(dtsM.Rows[i]["sMoTa"])))
SoDong++;
}
for (int i = 0; i < dtsL.Rows.Count; i++)
{
if (!String.IsNullOrEmpty(Convert.ToString(dtsL.Rows[i]["sMoTa"])))
SoDong++;
}
for (int i = 0; i < dtsLNS.Rows.Count; i++)
{
if (!String.IsNullOrEmpty(Convert.ToString(dtsLNS.Rows[i]["sMoTa"])))
SoDong++;
}
fr.AddTable("dtDongTrang", dt);
fr.SetValue("Tien", Tien);
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);
int KhoanhCachDong = 120;
int SoDongTrang1 = 23;
int SoDongGhiChu = dt.Rows.Count;
//trang 1 voi cỡ chữ 10, số dòng trên trang 23 dòng
if (SoDongGhiChu == 0)
{
SoDongTrang1 = 23;
if (SoDong <= SoDongTrang1 + 3 && SoDong > SoDongTrang1)
KhoanhCachDong = 158 + (SoDongTrang1 - SoDong) * 2;
}
//có ghi chú
else
{
if (SoDongGhiChu <= 10)
{
if (SoDong + SoDongGhiChu > SoDongTrang1 - 3 && SoDong + SoDongGhiChu < SoDongTrang1 + 3)
{
KhoanhCachDong = 200;
}
}
}
fr.SetExpression("test", "<#Row height(Autofit;" + KhoanhCachDong + ")>");
data.Dispose();
dtsTM.Dispose();
dtsM.Dispose();
dtsL.Dispose();
dtsLNS.Dispose();
dtsLNS1.Dispose();
dtsLNS3.Dispose();
dtsLNS5.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)
{
int SoDong = 0;
DataRow r;
DataTable data = new DataTable();
data = rptQuyetToan_ThongTri_ChungTu();
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"] = 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"] = 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"] = LayMoTa(Convert.ToString(r["sLNS1"]));
}
long TongTien = 0;
for (int i = 0; i < data.Rows.Count; i++)
{
if (data.Rows[i]["rTuChi"].ToString() != "")
{
TongTien += long.Parse(data.Rows[i]["rTuChi"].ToString());
}
}
String Tien = "";
Tien = CommonFunction.TienRaChu(TongTien).ToString();
//Ghi chú
DataTable dt = new DataTable();
dt.Columns.Add("sGhiChu", typeof(String));
int soChu1Trang = 80;
String SQL =
String.Format(
@"SELECT sGhiChu FROM QTA_GhiChu WHERE sTen=@sTen");
SqlCommand cmd = new SqlCommand(SQL);
cmd.Parameters.AddWithValue("@sTen", iID_MaChungTu);
String sGhiChu = Connection.GetValueString(cmd, "");
ArrayList arrDongTong = new ArrayList();
String[] arrDong = Regex.Split(sGhiChu, " ");
for (int i = 0; i < arrDong.Length; i++)
{
if (arrDong[i] != "")
{
int tg = 0;
String s = "";
String[] arrDongCon = arrDong[i].Split(' ');
for (int j = 0; j < arrDongCon.Length; j++)
{
int x = arrDongCon[j].Length;
tg = tg + x + 1;
if (tg > soChu1Trang)
{
arrDongTong.Add(s);
j--;
tg = 0;
s = "";
continue;
}
s += arrDongCon[j].Trim() + " ";
}
if (tg <= soChu1Trang) arrDongTong.Add(s);
}
}
for (int j = 0; j < arrDongTong.Count; j++)
{
r = dt.NewRow();
r["sGhiChu"] = arrDongTong[j];
dt.Rows.Add(r);
}
SoDong = data.Rows.Count;
for (int i = 0; i < dtsTM.Rows.Count; i++)
{
if (!String.IsNullOrEmpty(Convert.ToString(dtsTM.Rows[i]["sMoTa"])))
SoDong++;
}
for (int i = 0; i < dtsM.Rows.Count; i++)
{
if (!String.IsNullOrEmpty(Convert.ToString(dtsM.Rows[i]["sMoTa"])))
SoDong++;
}
for (int i = 0; i < dtsL.Rows.Count; i++)
{
if (!String.IsNullOrEmpty(Convert.ToString(dtsL.Rows[i]["sMoTa"])))
SoDong++;
}
for (int i = 0; i < dtsLNS.Rows.Count; i++)
{
if (!String.IsNullOrEmpty(Convert.ToString(dtsLNS.Rows[i]["sMoTa"])))
SoDong++;
}
fr.AddTable("dtDongTrang", dt);
fr.SetValue("Tien", Tien);
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);
int KhoanhCachDong = 120;
int SoDongTrang1 = 23;
int SoDongTrang2 = 47;
int SoDongGhiChu = dt.Rows.Count;
//trang 1 voi cỡ chữ 10, số dòng trên trang 23 dòng
if (SoDongGhiChu == 0)
{
SoDongTrang1 = 23;
if (SoDong <= SoDongTrang1 + 3 && SoDong > SoDongTrang1)
KhoanhCachDong = 158 + (SoDongTrang1 - SoDong) * 2;
}
//có ghi chú
else
{
if (SoDongGhiChu <= 10)
{
if (SoDong + SoDongGhiChu > SoDongTrang1 - 3 && SoDong + SoDongGhiChu < SoDongTrang1 + 3)
{
KhoanhCachDong = 200;
}
}
}
fr.SetExpression("test", "<#Row height(Autofit;" + KhoanhCachDong + ")>");
data.Dispose();
dtsTM.Dispose();
dtsM.Dispose();
dtsL.Dispose();
dtsLNS.Dispose();
dtsLNS1.Dispose();
dtsLNS3.Dispose();
dtsLNS5.Dispose();
}