/// <summary>
/// Build excel and export
/// </summary>
private void BuildExcel()
{
strWhere = strWhere.IndexOf("WHERE") != -1 ? strWhere + " AND" : "WHERE";
string ngayChon = Convert.ToDateTime(strDateChoose).ToString("yyyy-MM-dd");
string ngayDauThangSauChon = Convert.ToDateTime(strDateChoose).AddMonths(1).ToString("yyyy-MM-dd");
string sql = string.Format("select EMP_NM,EMP_N1, nhanvien.EMP_ID, INH_DT, POS_NM " +
"from FILB01A nhanvien " +
"join FILA02A bophan on nhanvien.DEP_ID = bophan.DEP_ID " +
"join FILA07A chucvu on nhanvien.POS_ID = chucvu.POS_ID " +
"join FILB01AC nghiviec on nghiviec.EMP_ID = nhanvien.EMP_ID " +
"{0} (nhanvien.VAC_BT = 1 and nghiviec.VAC_DT > '{1}' and nghiviec.VAC_DT < '{2}')", strWhere, ngayChon, ngayDauThangSauChon);
Func.RecordSet rsData = new Func.RecordSet(sql, PublicFunction.C_con);
//Build excel title
BuildTitle("A", "A", 3, "BÁO BIỂU THỐNG KÊ LƯƠNG / 工資統計表 [MM/YYYY]", string.Format("BÁO BIỂU THỐNG KÊ LƯƠNG / 工資統計表 {0}", strDateChoose));
if (rsData != null && rsData.rows > 0)
{
//Build excel row
int iRow = BuildExcelRow(rsData, true, "A", "T", 7);
//Format cells date
Excel.Range rgDate = oSheet.get_Range(oSheet.Cells[7, 5], oSheet.Cells[iRow - 1, 5]);
rgDate.NumberFormat = "mm/dd/yyyy";
//Format cells currency
Excel.Range rgCurrency = oSheet.get_Range(oSheet.Cells[7, 7], oSheet.Cells[iRow - 1, 20]);
rgCurrency.NumberFormat = "#,##0";
//Format total:
Excel.Range rgTotal = oSheet.get_Range(oSheet.Cells[iRow, 1], oSheet.Cells[iRow, 6]);
rgTotal.HorizontalAlignment = HorizontalAlignment.Right;
rgTotal.VerticalAlignment = HorizontalAlignment.Right;
//Draw border
ReportExcel2.DrawBorders(oSheet, 7, 1, iRow, 20);
//Build footer
BuildFooter(iRow, 1, 6, "TOTAL:");
BuildFooter(iRow, 7, 7, string.Format("=sum(G7:G{0})", iRow - 1));
BuildFooter(iRow, 8, 8, string.Format("=sum(H7:H{0})", iRow - 1));
BuildFooter(iRow, 9, 9, string.Format("=sum(I7:I{0})", iRow - 1));
BuildFooter(iRow, 10, 10, string.Format("=sum(J7:J{0})", iRow - 1));
BuildFooter(iRow, 11, 11, string.Format("=sum(K7:K{0})", iRow - 1));
BuildFooter(iRow, 12, 12, string.Format("=sum(L7:L{0})", iRow - 1));
BuildFooter(iRow, 13, 13, string.Format("=sum(M7:M{0})", iRow - 1));
BuildFooter(iRow, 14, 14, string.Format("=sum(N7:N{0})", iRow - 1));
BuildFooter(iRow, 15, 15, string.Format("=sum(O7:O{0})", iRow - 1));
BuildFooter(iRow, 16, 16, string.Format("=sum(P7:P{0})", iRow - 1));
BuildFooter(iRow, 17, 17, string.Format("=sum(Q7:Q{0})", iRow - 1));
BuildFooter(iRow, 18, 18, string.Format("=sum(R7:R{0})", iRow - 1));
BuildFooter(iRow, 19, 19, string.Format("=sum(S7:S{0})", iRow - 1));
BuildFooter(iRow, 20, 20, string.Format("=sum(T7:T{0})", iRow - 1));
}
}
/// <summary>
/// Build excel and export
/// </summary>
private void BuildExcel()
{
string ngayChon = Convert.ToDateTime(dateChuyenKhoan).ToString("yyyy-MM-dd");
string ngayDauThangSauChon = Convert.ToDateTime(dateChuyenKhoan).AddMonths(1).ToString("yyyy-MM-dd");
strWhere = strWhere.IndexOf("WHERE") != -1 ? strWhere + " AND" : "WHERE";
string sql = string.Format("SELECT bophan.DEP_NM, nhanvien.EMP_ID, bophan.DEP_ID, nhanvien.EMP_NM, bangluong.thucnhan "
+ "FROM FILB01A nhanvien "
+ "join FILA02A bophan on nhanvien.DEP_ID = bophan.DEP_ID "
+ string.Format("join {0} bangluong on nhanvien.EMP_ID = bangluong.EMP_ID ", Function.Common.Ints.tbLuongTD)
+ "join FILB01AC nghiviec on nghiviec.EMP_ID = nhanvien.EMP_ID "
+ "{3} ((ACC_NM is null or acc_no is null) "
+ "or (nhanvien.ACC_NM ='' or nhanvien.ACC_NO ='')) "
+ "AND (nhanvien.VAC_BT = 1 and nghiviec.VAC_DT > '{1}' and nghiviec.VAC_DT < '{2}') "
+ "and bangluong.SEQ_NO = 2 and YYY_MM= '{0}' order by bophan.DEP_NM, nhanvien.EMP_ID asc", Convert.ToDateTime(dateChuyenKhoan).ToString("yyyyMM"), ngayChon, ngayDauThangSauChon, strWhere);
Func.RecordSet rsData = new Func.RecordSet(sql, PublicFunction.C_con);
//Build excel title
BuildTitle("A", "A", 3, "[MM/YYYY]", dateChuyenKhoan);
BuildTitle("A", "A", 4, "[YYYY]", Convert.ToDateTime(dateChuyenKhoan).ToString("yyyy"));
BuildTitle("A", "A", 4, "[MM]", Convert.ToDateTime(dateChuyenKhoan).ToString("MM"));
if (rsData != null && rsData.rows > 0)
{
//Build excel row
int iRow = BuildExcelRow(rsData, true, "A", "G", 6);
//Draw border
ReportExcel2.DrawBorders(oSheet, 6, 1, iRow, 7);
//sum
BuildFooter(iRow, 1, 5, "TOTAL:");
BuildTotalSum(iRow);
//BuildFooter(iRow,6,6,string.Format("=sum({0}{1}:{0}{2}) ","F",6,iRow-1));
iRow++;
//Build footer
BuildFooter(iRow, 1, 2, "Tổng Giám Đốc");
BuildFooter(iRow, 3, 5, "Phó Tổng Giám Đốc");
BuildFooter(iRow, 6, 6, "Kế Toán");
BuildFooter(iRow, 8, 8, "Nhân Sự");
}
}
/// <summary>
/// Build excel and export
/// </summary>
private void BuildExcel()
{
string sql = string.Format("select * from table_test where test_date >= '{0}' and test_date <= '{1}'", dateFrom, dateTo);
Func.RecordSet rsData = new Func.RecordSet(sql, PublicFunction.C_con);
//Build excel title
BuildTitle("A", "A", 4, "D1", dateFrom);
BuildTitle("A", "A", 4, "D2", dateTo);
if (rsData != null && rsData.rows > 0)
{
//Build excel row
int iRow = BuildExcelRow(rsData, true, "A", "D", 8);
//Draw border
ReportExcel2.DrawBorders4(oSheet, 8, 1, iRow - 1, 4);
iRow++;
//Build footer
BuildFooter(iRow, 3, 4, "Lập biểu 制表：");
BuildFooter(iRow + 3, 3, 4, "Nguyễn Văn A");
}
}
private void cmd_ok_Click(object sender, System.EventArgs e)
{
try
{
string tenfile = "";
tenfile = vs.Rows[vs.Row]["FIL_NM"].ToString();
if (tenfile == "fvn_Test01.xltm")
{
Hashtable hs = new Hashtable();
hs["dateFrom"] = ((DateTime)dt_dt1.Value).ToString("yyyy-MM-dd");
hs["dateTo"] = ((DateTime)dt_dt2.Value).ToString("yyyy-MM-dd");
ReportExcel2.TemplateToExcel(tenfile, hs);
}
else if (tenfile == "Rehong_DanhSachTangCa.xltm")
{
DateTime dateTimeFrom = Convert.ToDateTime(dt_dt1.Text);
DateTime dateTimeTo = Convert.ToDateTime(dt_dt2.Text);
object dtResult = dateTimeTo.Subtract(dateTimeFrom);
Hashtable hs = new Hashtable();
hs["strBoPhan"] = control1.CB_DEP.Text.ToString(); //bophan;
hs["dateTimeFrom"] = dateTimeFrom.ToString("HH:mm"); //time from;
hs["dateTimeTo"] = dateTimeTo.ToString("HH:mm"); //time to;
hs["WorkHour"] = ((System.TimeSpan)(((TimeSpan)dtResult))).TotalHours.ToString();
ReportExcel2.TemplateToExcel(tenfile, hs);
}
else if (tenfile == "Rehong_TheNhanVien.xltm" || tenfile == "Rehong_TheNhanVienNV.xltm")
{
Hashtable hs = new Hashtable();
hs["strWhere"] = BuildWhereQuery();
ReportExcel2.TemplateToExcel(tenfile, hs);
}
else
{
if (vs.Row <= 0)
{
return;
}
string filename = "";
string st = "", st1 = "", st2 = "", st3 = "", st4 = "";
filename = PublicFunction.Path + "\\ReportsVBA\\" + vs.Rows[vs.Row]["FIL_NM"];
string where, where1, where2, where3, where4, where5, where6, where7, where8, where9, where10, where11, where12, where13;
where = where1 = where2 = where3 = where4 = where5 = where6 = where7 = where8 = where9 = where10 = where11 = where12 = where13 = "";
if (chk_CUR_DEP.Checked)
{
st = control1.GetWhere("m", false);
st = st.Replace("DEP_ID", "DEP_I1");
}
else
{
st = control1.GetWhere("FILB01A", false);
}
if (st.Length > 255)
{
st1 = st.Remove(0, 255);
st = st.Remove(255, st.Length - 255);
if (st1.Length > 255)
{
st2 = st1.Remove(0, 255);
st1 = st1.Remove(255, st1.Length - 255);
if (st2.Length > 255)
{
st3 = st2.Remove(0, 255);
st2 = st2.Remove(255, st2.Length - 255);
if (st3.Length > 255)
{
st4 = st3.Remove(0, 255);
st3 = st3.Remove(255, st3.Length - 255);
}
}
}
}
where = st;
where1 = dt_m.Text;
where2 = ((DateTime)dt_dt1.Value).ToString("yyyy/MM/dd");
where3 = ((DateTime)dt_dt2.Value).ToString("yyyy/MM/dd");
where4 = dt_y.Text;
where5 = cb_q.Text;
where6 = ck_labour.Checked + "";
where7 = ck_vat.Checked + "";
where8 = this.Tag + "";
where9 = st1;
where11 = st2;
where12 = st3;
//where13=st4;
where13 = PublicFunction.GPS.ToString();
where10 = PublicFunction.Path;
string xmlname = vs.Rows[vs.Row]["FIL_NM"] + "";
//Thu sua lam the bang C1Report
if (xmlname.Substring(xmlname.Length - 3, 3) == "xml")
{
filename = PublicFunction.Path + "\\Reports\\" + vs.Rows[vs.Row]["FIL_NM"];
frmPrintCard dlg = new frmPrintCard(st, filename, xmlname.Substring(0, xmlname.Length - 4));
dlg.Tag = this.Tag;
dlg.ShowDialog();
return;
}
//end Thu
// rp.PrintExcel(filename,PublicFunction.C_ConStrForRPT,vs.Rows[vs.Row][PublicFunction.L_Lag]+"","",
// PublicFunction.L_Lag,where,where1,where2,where3,where4,where5,where6,where7,where8,where9,where10,PublicFunction.A_UserID);
//khoa bao bieu
string keyD = "";
string ktype = "";
int iphut = 0;
SqlConnection SqlCon1 = new SqlConnection(PublicFunction.C_con.ConnectionString);
SqlCon1.Open();
Func.RecordSet rsK = new Func.RecordSet("Select * from GP_KEY", SqlCon1);
if (rsK.rows > 0)
{
ktype = rsK.record(0, "TYP_MN");
keyD = PublicFunction.encode(rsK.record(0, "COL_DT"), "");
iphut = T_String.IsNullTo0(rsK.record(0, "COL_MN"));
}
if (DateTime.Now >= DateTime.Parse(keyD) && ktype.ToUpper() == "RE")
{
MessageBox.Show("Office DLL was error");
return;
}
//end
ReportExcel.PrintExcel(filename, PublicFunction.C_ConStrForRPT, vs.Rows[vs.Row][PublicFunction.L_Lag] + "", "",
PublicFunction.L_Lag, where, where1, where2, where3, where4, where5, where6, where7, where8, where9, where10, where11, where12, where13, PublicFunction.A_UserID);
}
}
catch (Exception ex)
{}
}
/// <summary>
/// Build excel and export
/// </summary>
private void BuildExcel()
{
strWhere = strWhere.IndexOf("WHERE") != -1 ? strWhere + " AND" : "WHERE";
string sql = "";
//if(strStatus=="CL")
//{
sql = string.Format("select nhanvien.INH_DT, nhanvien.EMP_NM, nhanvien.EMP_N1, chucvu.POS_NM, " +
"nhanvien.EMP_ID, bophan.DEP_NM, l.muc_luongcoban, giocong, luongcoban, giohuongluong50, " +
"tienhuongluong50, choviec, tienchoviec, tonggiocong, trachnhiem, kynang, nhatro, dochai, " +
"tongphucap, gio15, tien15, TangCaNgayThuong, ThanhTienCa, gio195, tien195, gio2, tien2, " +
"TangCaQuaDemCN, ThanhTienCaQuaDemCN, GioTangCaNgayLe3, TienTangCaNgayLe3, tongtienTC, thucnhan " +
"from FILB01A nhanvien " +
"join FILA02A bophan on nhanvien.DEP_ID = bophan.DEP_ID " +
"join FILA07A chucvu on nhanvien.POS_ID = chucvu.POS_ID " +
"left join FILB01AC nghiviec on nghiviec.EMP_ID = nhanvien.EMP_ID " +
string.Format("left join {0} l on nhanvien.EMP_ID = l.EMP_ID ", Function.Common.Ints.tbLuongTD) +
"{0} (nghiviec.VAC_DT is null or nghiviec.VAC_DT > '{1}') and l.YYY_MM = '{2}' " +
"order by bophan.DEP_NM, nhanvien.EMP_ID asc", strWhere, ngayCuoiThangChon, strThangChon);
//}
//else if(strStatus=="NV")
//{
// sql=string.Format("select nhanvien.INH_DT, nhanvien.EMP_NM, nhanvien.EMP_N1, chucvu.POS_NM, " +
// "nhanvien.EMP_ID, bophan.DEP_NM, l.muc_luongcoban, giocong, luongcoban, giohuongluong50, " +
// "tienhuongluong50, choviec, tienchoviec, tonggiocong, trachnhiem, kynang, nhatro, dochai, " +
// "tongphucap, gio15, tien15, TangCaNgayThuong, ThanhTienCa, gio195, tien195, gio2, tien2, " +
// "TangCaQuaDemCN, ThanhTienCaQuaDemCN, GioTangCaNgayLe3, TienTangCaNgayLe3, tongtienTC, thucnhan " +
// "from FILB01A nhanvien " +
// "join FILA02A bophan on nhanvien.DEP_ID = bophan.DEP_ID " +
// "join FILA07A chucvu on nhanvien.POS_ID = chucvu.POS_ID " +
// "left join FILB01AC nghiviec on nghiviec.EMP_ID = nhanvien.EMP_ID " +
// "left join FILD02A l on nhanvien.EMP_ID = l.EMP_ID " +
// "{0} (nhanvien.VAC_BT = 1 and nghiviec.VAC_DT > '{1}' and nghiviec.VAC_DT < '{2}') and l.YYY_MM = '{3}' " +
// "order by bophan.DEP_NM, nhanvien.EMP_ID asc", strWhere,ngayChon,ngayDauThangSauChon,strThangChon);
//}
//sql=sql.Replace("FILD02A",Function.Common.Ints.tbLuongTD);
Func.RecordSet rsData = new Func.RecordSet(sql, PublicFunction.C_con);
//Build excel title
BuildTitle("A", "A", 3, "[DD]", Convert.ToDateTime(strDateChoose).ToString("dd"));
BuildTitle("A", "A", 3, "[MM]", Convert.ToDateTime(strDateChoose).ToString("MM"));
BuildTitle("A", "A", 3, "[YYYY]", Convert.ToDateTime(strDateChoose).ToString("yyyy"));
if (rsData != null && rsData.rows > 0)
{
//Build excel row
int iRow = BuildExcelRow(rsData, true, "A", "AI", 7);
//Format cells date
Excel.Range rgDate = oSheet.get_Range(oSheet.Cells[8, 2], oSheet.Cells[iRow - 1, 2]);
rgDate.NumberFormat = "dd/MM/yyyy";
//Format cells currency
// Excel.Range rgCurrency = oSheet.get_Range(oSheet.Cells[7,7],oSheet.Cells[iRow-1,20]);
// rgCurrency.NumberFormat = "#,##0";
//Format total:
// Excel.Range rgTotal = oSheet.get_Range(oSheet.Cells[iRow,1],oSheet.Cells[iRow,6]);
// rgTotal.HorizontalAlignment = HorizontalAlignment.Right;
// rgTotal.VerticalAlignment = HorizontalAlignment.Right;
//Draw border
ReportExcel2.DrawBorders(oSheet, 7, 1, iRow, 35);
//Build footer
BuildFooter(iRow, 1, 3, "合計 TỔNG CỘNG");
BuildTotalSum(iRow);
iRow++;
iRow++;
//Build Chu ky
BuildFooter(iRow, 3, 3, "總 經 理");
BuildFooter(iRow, 13, 14, "副總");
BuildFooter(iRow, 20, 20, "会计");
BuildFooter(iRow, 32, 32, "人事");
iRow++;
BuildFooter(iRow, 3, 3, "Tổng Giám Đốc");
BuildFooter(iRow, 13, 14, "Phó Giám Đốc");
BuildFooter(iRow, 20, 20, "Kế Toán");
BuildFooter(iRow, 32, 32, "Nhân Sự");
// iRow= iRow + 7;
// BuildFooter(iRow,5,6,"Yang Cheng Lung");
// BuildFooter(iRow,47,53,"Huỳnh Bửu Dinh");
// BuildFooter(iRow,71,73,"Trương Thị Kim Thoa");
}
}
