/*Build the document **/
private void Build()
{
// step 1: creation of a document-object
document = new CreateExcelDoc();
try
{
//Add Header
int row = 1; int col = 1;
AddDocHeader(ref row, ref col);
//Add Body
AddDocBody(ref row, ref col);
//Add Footer
AddDocFooter(ref row, ref col);
}
catch (IOException ioe)
{
this.Message = ioe.Message;
}
catch (Exception ex)
{
Log.WriteToErrorLogFile(ex);
}
}
protected void btnExport_Click(object sender, EventArgs e)
{
DataTable dtNhanVien = DotDanhGiaController.GetAllNhanVien(ConvertUtility.ToInt32(dropTrungTam.SelectedValue), ConvertUtility.ToInt32(dropPhongBan.SelectedValue));
if (dtNhanVien == null)
{
return;
}
object misValue = System.Reflection.Missing.Value;
xlApp = new Excel.ApplicationClass();
xlWorkBook = xlApp.Workbooks.Add(misValue);
int sheet = 1;
foreach (DataRow itemNhanVien in dtNhanVien.Rows)
{
int idNhanVien = ConvertUtility.ToInt32(itemNhanVien["UserID"]);
#region Cong viec thuong xuyen
DataTable dtCongViec = DotDanhGiaController.GetAllCongViecGiaoChoNhanVienKTXVaPS(idNhanVien, 1, ConvertUtility.ToInt32(dropDotDanhGia.SelectedValue));
DataTable dtCongViecNew = dtCongViec.Clone();
dtCongViecNew.Columns.Add("TyTrongCaNhan", typeof(string));
dtCongViecNew.Columns.Add("TyTrongCaNhanSoVoiPhong", typeof(string));
dtCongViecNew.Columns.Add("GioLamViec", typeof(string));
DataSet ds = DotDanhGiaController.GetPTDG(idNhanVien, 0, ConvertUtility.ToInt32(dropDotDanhGia.SelectedValue), ConvertUtility.ToInt32(dropTrungTam.SelectedValue));
DataTable dtSoNhanVien = ds.Tables[1];
DataTable dtTyTrong = ds.Tables[2];
DataTable dtSoNhanVienPhong = ds.Tables[3];
int tongSoNhanVien = 0;
if (dtSoNhanVien != null && dtSoNhanVien.Rows.Count > 0)
{
tongSoNhanVien = ConvertUtility.ToInt32(dtSoNhanVien.Rows[0]["TongSoNhanVien"]);
}
int tyTrongCVPhongVoiTrungTam = 0;
if (dtTyTrong != null && dtTyTrong.Rows.Count > 0)
{
tyTrongCVPhongVoiTrungTam = ConvertUtility.ToInt32(dtTyTrong.Rows[0]["TyTrong"]);
}
if (tyTrongCVPhongVoiTrungTam == 0)
{
tyTrongCVPhongVoiTrungTam = 100;
}
int tongSoNhanVienPhong = 0;
if (dtSoNhanVienPhong != null && dtSoNhanVienPhong.Rows.Count > 0)
{
tongSoNhanVienPhong = ConvertUtility.ToInt32(dtSoNhanVienPhong.Rows[0]["TongSoNhanVien"]);
}
DataRow rowHeader = dtCongViecNew.NewRow();
rowHeader["TenCVCha"] = "-----------------Công việc thường xuyên----------------";
rowHeader["Ten"] = "---------------------------";
rowHeader["TyTrong"] = "0";
rowHeader["KeHoach"] = "";
rowHeader["TyTrongCaNhan"] = "0";
rowHeader["GioLamViec"] = "0";
rowHeader["TyTrongCaNhanSoVoiPhong"] = "0";
dtCongViecNew.Rows.Add(rowHeader);
foreach (DataRow item in dtCongViec.Rows)
{
DataRow row = dtCongViecNew.NewRow();
int idCongViecKPI = ConvertUtility.ToInt32(item["IDCongViecKPI"]);
DataTable dtTyTrongKPI = DotDanhGiaController.GetTyTrongCongViec(idCongViecKPI, ConvertUtility.ToInt32(dropDotDanhGia.SelectedValue), ConvertUtility.ToInt32(dropTrungTam.SelectedValue), ConvertUtility.ToInt32(dropPhongBan.SelectedValue), idNhanVien);
double tyTrongCVDuocGiao = 0;
row["TenCVCha"] = ConvertUtility.ToString(item["TenCVCha"]);
row["Ten"] = item["Ten"];
double tyTrongCVDuocGiaoCaNhan = 0;
double tyTrongCVDuocGiaoCaNhanSoVoiPhong = 0;
if (dtTyTrongKPI != null && dtTyTrongKPI.Rows.Count > 0)
{
tyTrongCVDuocGiaoCaNhan = ConvertUtility.ToDouble(dtTyTrongKPI.Rows[0]["TyTrong"]) * tongSoNhanVien;
double songaylamvieccuaNV = Math.Round(ConvertUtility.ToDouble(dtTyTrongKPI.Rows[0]["TyTrong"]) * tongSoNhanVien * 22 / 100, 3);
double tongsongaylamvieccuaphong = Math.Round(ConvertUtility.ToDouble(tongSoNhanVien) * 22 * tyTrongCVPhongVoiTrungTam / 100, 3);
tyTrongCVDuocGiaoCaNhanSoVoiPhong = Math.Round(songaylamvieccuaNV * 100 / tongsongaylamvieccuaphong, 3);
tyTrongCVDuocGiao = Math.Round(ConvertUtility.ToDouble(dtTyTrongKPI.Rows[0]["TyTrong"]), 3);
row["TyTrong"] = tyTrongCVDuocGiao;
}
else
{
tyTrongCVDuocGiaoCaNhan = ConvertUtility.ToDouble(item["TyTrong"]) * tongSoNhanVien;
double songaylamvieccuaNV = Math.Round(ConvertUtility.ToDouble(item["TyTrong"]) * tongSoNhanVien * 22 / 100, 3);
double tongsongaylamvieccuaphong = Math.Round(ConvertUtility.ToDouble(tongSoNhanVien) * 22 * tyTrongCVPhongVoiTrungTam / 100, 3);
tyTrongCVDuocGiaoCaNhanSoVoiPhong = Math.Round(songaylamvieccuaNV * 100 / tongsongaylamvieccuaphong, 3);
tyTrongCVDuocGiao = Math.Round(ConvertUtility.ToDouble(item["TyTrong"]), 3);
row["TyTrong"] = tyTrongCVDuocGiao;
}
row["KeHoach"] = item["KeHoach"].ToString();
row["TyTrongCaNhan"] = Math.Round(tyTrongCVDuocGiaoCaNhan, 3).ToString().Replace(",", ".");// tyTrongCVDuocGiaoCaNhan.ToString().Replace(",", ".");
row["GioLamViec"] = Math.Round(tyTrongCVDuocGiaoCaNhan * 22 * 8 / 100, 3).ToString().Replace(",", ".");
//if (dtTyTrongKPI != null && dtTyTrongKPI.Rows.Count > 0)
//{
// tyTrongCVDuocGiaoCaNhan = ConvertUtility.ToDouble(dtTyTrongKPI.Rows[0]["TyTrong"]) * tongSoNhanVien;
// double songaylamvieccuaNV = Math.Round(ConvertUtility.ToDouble(dtTyTrongKPI.Rows[0]["TyTrong"]) * tongSoNhanVien * 22 / 100, 2);
// double tongsongaylamvieccuaphong = Math.Round(ConvertUtility.ToDouble(tongSoNhanVien) * 22 * tyTrongCVPhongVoiTrungTam / 100, 2);
// tyTrongCVDuocGiaoCaNhanSoVoiPhong = Math.Round(songaylamvieccuaNV * 100 / tongsongaylamvieccuaphong, 2);
// tyTrongCVDuocGiao = Math.Round(ConvertUtility.ToDouble(dtTyTrongKPI.Rows[0]["TyTrong"]), 2);
// row["TyTrong"] = tyTrongCVDuocGiao.ToString();
//}
//else
//{
// tyTrongCVDuocGiaoCaNhan = ConvertUtility.ToDouble(item["TyTrong"]) * tongSoNhanVien;
// double songaylamvieccuaNV = Math.Round(ConvertUtility.ToDouble(item["TyTrong"]) * tongSoNhanVien * 22 / 100, 2);
// double tongsongaylamvieccuaphong = Math.Round(ConvertUtility.ToDouble(tongSoNhanVien) * 22 * tyTrongCVPhongVoiTrungTam / 100, 2);
// tyTrongCVDuocGiaoCaNhanSoVoiPhong = Math.Round(songaylamvieccuaNV * 100 / tongsongaylamvieccuaphong, 2);
// tyTrongCVDuocGiao = Math.Round(ConvertUtility.ToDouble(item["TyTrong"]), 2);
// row["TyTrong"] = tyTrongCVDuocGiao.ToString();
//}
row["TyTrongCaNhanSoVoiPhong"] = tyTrongCVDuocGiaoCaNhanSoVoiPhong.ToString().Replace(",", ".");
dtCongViecNew.Rows.Add(row);
}
#endregion
rowHeader = dtCongViecNew.NewRow();
rowHeader["TenCVCha"] = "-----------------Công việc kế hoạch tháng----------------";
rowHeader["Ten"] = "---------------------------";
rowHeader["TyTrong"] = "0";
rowHeader["KeHoach"] = "";
rowHeader["TyTrongCaNhan"] = "0";
rowHeader["GioLamViec"] = "0";
rowHeader["TyTrongCaNhanSoVoiPhong"] = "0";
dtCongViecNew.Rows.Add(rowHeader);
#region Cong viec ke hoach thang
dtCongViec = DotDanhGiaController.GetAllCongViecGiaoChoNhanVienKTXVaPS(idNhanVien, 2, ConvertUtility.ToInt32(dropDotDanhGia.SelectedValue));
ds = DotDanhGiaController.GetPTDG(idNhanVien, 0, ConvertUtility.ToInt32(dropDotDanhGia.SelectedValue), ConvertUtility.ToInt32(dropTrungTam.SelectedValue));
dtSoNhanVien = ds.Tables[1];
dtTyTrong = ds.Tables[2];
dtSoNhanVienPhong = ds.Tables[3];
tongSoNhanVien = 0;
if (dtSoNhanVien != null && dtSoNhanVien.Rows.Count > 0)
{
tongSoNhanVien = ConvertUtility.ToInt32(dtSoNhanVien.Rows[0]["TongSoNhanVien"]);
}
tyTrongCVPhongVoiTrungTam = 0;
if (dtTyTrong != null && dtTyTrong.Rows.Count > 0)
{
tyTrongCVPhongVoiTrungTam = ConvertUtility.ToInt32(dtTyTrong.Rows[0]["TyTrong"]);
}
if (tyTrongCVPhongVoiTrungTam == 0)
{
tyTrongCVPhongVoiTrungTam = 100;
}
tongSoNhanVienPhong = 0;
if (dtSoNhanVienPhong != null && dtSoNhanVienPhong.Rows.Count > 0)
{
tongSoNhanVienPhong = ConvertUtility.ToInt32(dtSoNhanVienPhong.Rows[0]["TongSoNhanVien"]);
}
double tyTrongCVKeHoachThang = 0;
foreach (DataRow item in dtCongViec.Rows)
{
DataRow row = dtCongViecNew.NewRow();
int idCongViecKPI = ConvertUtility.ToInt32(item["IDCongViecKPI"]);
DataTable dtTyTrongKPI = DotDanhGiaController.GetTyTrongCongViec(idCongViecKPI, ConvertUtility.ToInt32(dropDotDanhGia.SelectedValue), ConvertUtility.ToInt32(dropTrungTam.SelectedValue), ConvertUtility.ToInt32(dropPhongBan.SelectedValue), idNhanVien);
double tyTrongCVDuocGiao = 0;
row["TenCVCha"] = ConvertUtility.ToString(item["TenCVCha"]);
row["Ten"] = item["Ten"];
//double tyTrongCVDuocGiaoCaNhan = 0;
//double tyTrongCVDuocGiaoCaNhanSoVoiPhong = 0;
//if (dtTyTrongKPI != null && dtTyTrongKPI.Rows.Count > 0)
//{
// tyTrongCVDuocGiaoCaNhan = ConvertUtility.ToDouble(dtTyTrongKPI.Rows[0]["TyTrong"]) * tongSoNhanVien;
// tyTrongCVKeHoachThang += tyTrongCVDuocGiaoCaNhan;
// double songaylamvieccuaNV = Math.Round(ConvertUtility.ToDouble(dtTyTrongKPI.Rows[0]["TyTrong"]) * tongSoNhanVien * 22 / 100, 2);
// double tongsongaylamvieccuaphong = Math.Round(ConvertUtility.ToDouble(tongSoNhanVien) * 22 * tyTrongCVPhongVoiTrungTam / 100, 2);
// tyTrongCVDuocGiaoCaNhanSoVoiPhong = Math.Round(songaylamvieccuaNV * 100 / tongsongaylamvieccuaphong, 2);
// tyTrongCVDuocGiao = Math.Round(ConvertUtility.ToDouble(dtTyTrongKPI.Rows[0]["TyTrong"]), 2);
// row["TyTrong"] = tyTrongCVDuocGiao.ToString();
//}
//else
//{
// tyTrongCVDuocGiaoCaNhan = ConvertUtility.ToDouble(item["TyTrong"]) * tongSoNhanVien;
// tyTrongCVKeHoachThang += tyTrongCVDuocGiaoCaNhan;
// double songaylamvieccuaNV = Math.Round(ConvertUtility.ToDouble(item["TyTrong"]) * tongSoNhanVien * 22 / 100, 2);
// double tongsongaylamvieccuaphong = Math.Round(ConvertUtility.ToDouble(tongSoNhanVien) * 22 * tyTrongCVPhongVoiTrungTam / 100, 2);
// tyTrongCVDuocGiaoCaNhanSoVoiPhong = Math.Round(songaylamvieccuaNV * 100 / tongsongaylamvieccuaphong, 2);
// tyTrongCVDuocGiao = Math.Round(ConvertUtility.ToDouble(item["TyTrong"]), 2);
// row["TyTrong"] = tyTrongCVDuocGiao.ToString();
//}
//row["KeHoach"] = item["KeHoach"].ToString();
//row["TyTrongCaNhan"] = tyTrongCVDuocGiaoCaNhan.ToString().Replace(",", ".");
//row["GioLamViec"] = Math.Round(tyTrongCVDuocGiaoCaNhan * 22 * 8 / 100, 2);
double tyTrongCVDuocGiaoCaNhan = 0;
double tyTrongCVDuocGiaoCaNhanSoVoiPhong = 0;
if (dtTyTrongKPI != null && dtTyTrongKPI.Rows.Count > 0)
{
tyTrongCVDuocGiaoCaNhan = ConvertUtility.ToDouble(dtTyTrongKPI.Rows[0]["TyTrong"]) * tongSoNhanVien;
double songaylamvieccuaNV = Math.Round(ConvertUtility.ToDouble(dtTyTrongKPI.Rows[0]["TyTrong"]) * tongSoNhanVien * 22 / 100, 3);
double tongsongaylamvieccuaphong = Math.Round(ConvertUtility.ToDouble(tongSoNhanVien) * 22 * tyTrongCVPhongVoiTrungTam / 100, 3);
tyTrongCVDuocGiaoCaNhanSoVoiPhong = Math.Round(songaylamvieccuaNV * 100 / tongsongaylamvieccuaphong, 3);
tyTrongCVDuocGiao = Math.Round(ConvertUtility.ToDouble(dtTyTrongKPI.Rows[0]["TyTrong"]), 3);
row["TyTrong"] = tyTrongCVDuocGiao;
}
else
{
tyTrongCVDuocGiaoCaNhan = ConvertUtility.ToDouble(item["TyTrong"]) * tongSoNhanVien;
double songaylamvieccuaNV = Math.Round(ConvertUtility.ToDouble(item["TyTrong"]) * tongSoNhanVien * 22 / 100, 3);
double tongsongaylamvieccuaphong = Math.Round(ConvertUtility.ToDouble(tongSoNhanVien) * 22 * tyTrongCVPhongVoiTrungTam / 100, 3);
tyTrongCVDuocGiaoCaNhanSoVoiPhong = Math.Round(songaylamvieccuaNV * 100 / tongsongaylamvieccuaphong, 3);
tyTrongCVDuocGiao = Math.Round(ConvertUtility.ToDouble(item["TyTrong"]), 3);
row["TyTrong"] = tyTrongCVDuocGiao;
}
row["KeHoach"] = item["KeHoach"].ToString();
row["TyTrongCaNhan"] = Math.Round(tyTrongCVDuocGiaoCaNhan, 3).ToString().Replace(",", ".");// tyTrongCVDuocGiaoCaNhan.ToString().Replace(",", ".");
row["GioLamViec"] = Math.Round(tyTrongCVDuocGiaoCaNhan * 22 * 8 / 100, 3).ToString().Replace(",", ".");
row["TyTrongCaNhanSoVoiPhong"] = tyTrongCVDuocGiaoCaNhanSoVoiPhong.ToString().Replace(",", ".");
dtCongViecNew.Rows.Add(row);
}
#endregion
rowHeader = dtCongViecNew.NewRow();
rowHeader["TenCVCha"] = "-----------------Công việc phát sinh----------------";
rowHeader["Ten"] = "---------------------------";
rowHeader["TyTrong"] = "0";
rowHeader["KeHoach"] = "";
rowHeader["TyTrongCaNhan"] = "0";
rowHeader["GioLamViec"] = "0";
rowHeader["TyTrongCaNhanSoVoiPhong"] = "0";
dtCongViecNew.Rows.Add(rowHeader);
#region Cong viec phat sinh
dtCongViec = DotDanhGiaController.GetAllCongViecGiaoChoNhanVienKTXVaPS(idNhanVien, 3, ConvertUtility.ToInt32(dropDotDanhGia.SelectedValue));
ds = DotDanhGiaController.GetPTDG(idNhanVien, 0, ConvertUtility.ToInt32(dropDotDanhGia.SelectedValue), ConvertUtility.ToInt32(dropTrungTam.SelectedValue));
dtSoNhanVien = ds.Tables[1];
dtTyTrong = ds.Tables[2];
dtSoNhanVienPhong = ds.Tables[3];
tongSoNhanVien = 0;
if (dtSoNhanVien != null && dtSoNhanVien.Rows.Count > 0)
{
tongSoNhanVien = ConvertUtility.ToInt32(dtSoNhanVien.Rows[0]["TongSoNhanVien"]);
}
tyTrongCVPhongVoiTrungTam = 0;
if (dtTyTrong != null && dtTyTrong.Rows.Count > 0)
{
tyTrongCVPhongVoiTrungTam = ConvertUtility.ToInt32(dtTyTrong.Rows[0]["TyTrong"]);
}
if (tyTrongCVPhongVoiTrungTam == 0)
{
tyTrongCVPhongVoiTrungTam = 100;
}
tongSoNhanVienPhong = 0;
if (dtSoNhanVienPhong != null && dtSoNhanVienPhong.Rows.Count > 0)
{
tongSoNhanVienPhong = ConvertUtility.ToInt32(dtSoNhanVienPhong.Rows[0]["TongSoNhanVien"]);
}
tyTrongCVKeHoachThang = 0;
foreach (DataRow item in dtCongViec.Rows)
{
DataRow row = dtCongViecNew.NewRow();
int idCongViecKPI = ConvertUtility.ToInt32(item["IDCongViecKPI"]);
DataTable dtTyTrongKPI = DotDanhGiaController.GetTyTrongCongViec(idCongViecKPI, ConvertUtility.ToInt32(dropDotDanhGia.SelectedValue), ConvertUtility.ToInt32(dropTrungTam.SelectedValue), ConvertUtility.ToInt32(dropPhongBan.SelectedValue), idNhanVien);
double tyTrongCVDuocGiao = 0;
row["TenCVCha"] = ConvertUtility.ToString(item["TenCVCha"]);
row["Ten"] = item["Ten"];
//double tyTrongCVDuocGiaoCaNhan = 0;
//double tyTrongCVDuocGiaoCaNhanSoVoiPhong = 0;
//if (dtTyTrongKPI != null && dtTyTrongKPI.Rows.Count > 0)
//{
// tyTrongCVDuocGiaoCaNhan = Math.Round(ConvertUtility.ToDouble(dtTyTrongKPI.Rows[0]["TyTrong"]) * tongSoNhanVien, 2);
// tyTrongCVKeHoachThang += tyTrongCVDuocGiaoCaNhan;
// double songaylamvieccuaNV = Math.Round(ConvertUtility.ToDouble(dtTyTrongKPI.Rows[0]["TyTrong"]) * tongSoNhanVien * 22 / 100, 2);
// double tongsongaylamvieccuaphong = Math.Round(ConvertUtility.ToDouble(tongSoNhanVien) * 22 * tyTrongCVPhongVoiTrungTam / 100, 2);
// tyTrongCVDuocGiaoCaNhanSoVoiPhong = Math.Round(songaylamvieccuaNV * 100 / tongsongaylamvieccuaphong, 2);
// tyTrongCVDuocGiao = Math.Round(ConvertUtility.ToDouble(dtTyTrongKPI.Rows[0]["TyTrong"]), 2);
// row["TyTrong"] = tyTrongCVDuocGiao.ToString();
//}
//else
//{
// tyTrongCVDuocGiaoCaNhan = Math.Round(ConvertUtility.ToDouble(item["TyTrong"]) * tongSoNhanVien, 2);
// tyTrongCVKeHoachThang += tyTrongCVDuocGiaoCaNhan;
// double songaylamvieccuaNV = Math.Round(ConvertUtility.ToDouble(item["TyTrong"]) * tongSoNhanVien * 22 / 100, 2);
// double tongsongaylamvieccuaphong = Math.Round(ConvertUtility.ToDouble(tongSoNhanVien) * 22 * tyTrongCVPhongVoiTrungTam / 100, 2);
// tyTrongCVDuocGiaoCaNhanSoVoiPhong = Math.Round(songaylamvieccuaNV * 100 / tongsongaylamvieccuaphong, 2);
// tyTrongCVDuocGiao = Math.Round(ConvertUtility.ToDouble(item["TyTrong"]), 2);
// row["TyTrong"] = tyTrongCVDuocGiao.ToString();
//}
//row["KeHoach"] = item["KeHoach"].ToString();
//row["TyTrongCaNhan"] = tyTrongCVDuocGiaoCaNhan.ToString().Replace(",", ".");
//row["GioLamViec"] = Math.Round(tyTrongCVDuocGiaoCaNhan * 22 * 8 / 100, 2);
double tyTrongCVDuocGiaoCaNhan = 0;
double tyTrongCVDuocGiaoCaNhanSoVoiPhong = 0;
if (dtTyTrongKPI != null && dtTyTrongKPI.Rows.Count > 0)
{
tyTrongCVDuocGiaoCaNhan = ConvertUtility.ToDouble(dtTyTrongKPI.Rows[0]["TyTrong"]) * tongSoNhanVien;
double songaylamvieccuaNV = Math.Round(ConvertUtility.ToDouble(dtTyTrongKPI.Rows[0]["TyTrong"]) * tongSoNhanVien * 22 / 100, 3);
double tongsongaylamvieccuaphong = Math.Round(ConvertUtility.ToDouble(tongSoNhanVien) * 22 * tyTrongCVPhongVoiTrungTam / 100, 3);
tyTrongCVDuocGiaoCaNhanSoVoiPhong = Math.Round(songaylamvieccuaNV * 100 / tongsongaylamvieccuaphong, 3);
tyTrongCVDuocGiao = Math.Round(ConvertUtility.ToDouble(dtTyTrongKPI.Rows[0]["TyTrong"]), 3);
row["TyTrong"] = tyTrongCVDuocGiao;
}
else
{
tyTrongCVDuocGiaoCaNhan = ConvertUtility.ToDouble(item["TyTrong"]) * tongSoNhanVien;
double songaylamvieccuaNV = Math.Round(ConvertUtility.ToDouble(item["TyTrong"]) * tongSoNhanVien * 22 / 100, 3);
double tongsongaylamvieccuaphong = Math.Round(ConvertUtility.ToDouble(tongSoNhanVien) * 22 * tyTrongCVPhongVoiTrungTam / 100, 3);
tyTrongCVDuocGiaoCaNhanSoVoiPhong = Math.Round(songaylamvieccuaNV * 100 / tongsongaylamvieccuaphong, 3);
tyTrongCVDuocGiao = Math.Round(ConvertUtility.ToDouble(item["TyTrong"]), 3);
row["TyTrong"] = tyTrongCVDuocGiao;
}
row["KeHoach"] = item["KeHoach"].ToString();
row["TyTrongCaNhan"] = Math.Round(tyTrongCVDuocGiaoCaNhan, 3).ToString().Replace(",", ".");// tyTrongCVDuocGiaoCaNhan.ToString().Replace(",", ".");
row["GioLamViec"] = Math.Round(tyTrongCVDuocGiaoCaNhan * 22 * 8 / 100, 3).ToString().Replace(",", ".");
row["TyTrongCaNhanSoVoiPhong"] = tyTrongCVDuocGiaoCaNhanSoVoiPhong.ToString().Replace(",", ".");
dtCongViecNew.Rows.Add(row);
}
#endregion
XmlDocument doc = BuildXmlDocument(dtCongViecNew);
if (!Directory.Exists(Server.MapPath("/UpLoad/Temp/" + itemNhanVien["UserID"].ToString() + "/")))
{
Directory.CreateDirectory(Server.MapPath("/UpLoad/Temp/" + itemNhanVien["UserID"].ToString() + "/"));
}
////ds.Dispose();
string datetime = DateTime.Now.ToString("yyyyMMddHHmmss");
FileStream downloadFile =
File.Create(Server.MapPath("/UpLoad/Temp/" + itemNhanVien["UserID"].ToString() + "/") + "ReportCongViecNhanVien_" + datetime +
".xls");
// transform
XslTransform xsl = new XslTransform();
xsl.Load(Server.MapPath("/Template/" + "ReportCongViecNhanVien.xsl"));
DateConverter objDateConverter = new DateConverter();
XsltArgumentList args = new XsltArgumentList();
args.AddExtensionObject("urn:ms-kb", objDateConverter);
xsl.Transform(doc, args, downloadFile, null);
downloadFile.Flush();
downloadFile.Close();
HyperLink hplDownload = new HyperLink();
hplDownload.Text = "Công việc của " + itemNhanVien["Username"].ToString() + ", click vào link để Tải file về";
hplDownload.Visible = true;
hplDownload.NavigateUrl = "/UpLoad/Temp/" + itemNhanVien["UserID"].ToString() + "/ReportCongViecNhanVien_" + datetime + ".xls";
Literal lit = new Literal();
lit.Text = "<br />";
pnLink.Controls.Add(hplDownload);
pnLink.Controls.Add(lit);
CreateExcelDoc excell_app = new CreateExcelDoc(sheet, xlWorkBook, itemNhanVien["UserName"].ToString());
//creates the main header
excell_app.createHeaders(1, 1, "Nhóm công việc", "A1", "A1", 0, "YELLOW", true, 40, "");
excell_app.createHeaders(1, 2, "Tên công việc", "B1", "B1", 0, "YELLOW", true, 40, "");
excell_app.createHeaders(1, 3, "Kế hoạch", "C1", "C1", 0, "YELLOW", true, 40, "");
excell_app.createHeaders(1, 4, "Tỷ Trọng so với trung tâm", "D1", "D1", 0, "YELLOW", true, 20, "");
excell_app.createHeaders(1, 5, "Tỷ Trọng so với phòng", "E1", "E1", 0, "YELLOW", true, 20, "");
excell_app.createHeaders(1, 6, "Tỷ Trọng công việc cá nhân trong tháng", "F1", "F1", 0, "YELLOW", true, 20, "");
excell_app.createHeaders(1, 7, "Giờ làm việc", "G1", "G1", 0, "YELLOW", true, 20, "");
int rowIndex = 2;
foreach (DataRow item in dtCongViecNew.Rows)
{
excell_app.addData(rowIndex, 1, item["TenCVCha"].ToString(), "A" + rowIndex.ToString(), "A" + rowIndex.ToString(), "");
excell_app.addData(rowIndex, 2, item["Ten"].ToString(), "B" + rowIndex.ToString(), "B" + rowIndex.ToString(), "");
excell_app.addData(rowIndex, 3, item["KeHoach"].ToString(), "C" + rowIndex.ToString(), "C" + rowIndex.ToString(), "");
excell_app.addData(rowIndex, 4, item["TyTrong"].ToString(), "D" + rowIndex.ToString(), "D" + rowIndex.ToString(), "#,##0");
excell_app.addData(rowIndex, 5, item["TyTrongCaNhanSoVoiPhong"].ToString(), "E" + rowIndex.ToString(), "E" + rowIndex.ToString(), "#,##0");
excell_app.addData(rowIndex, 6, item["TyTrongCaNhan"].ToString(), "F" + rowIndex.ToString(), "F" + rowIndex.ToString(), "#,##0");
excell_app.addData(rowIndex, 7, item["GioLamViec"].ToString(), "G" + rowIndex.ToString(), "G" + rowIndex.ToString(), "");
rowIndex++;
}
sheet++;
}
string datetimeTTPhong = DateTime.Now.ToString("yyyyMMddHHmmss");
string fileName = "/UpLoad/Temp/ReportCongViecNhanVien_" + datetimeTTPhong + ".xls";
CreateExcelDoc.SaveExcel(Server.MapPath(fileName), xlWorkBook);
HyperLink hplDownloadCVPhongTT = new HyperLink();
hplDownloadCVPhongTT.Text = "Công việc của Trung Tâm hoặc phòng, click vào link để Tải file về";
hplDownloadCVPhongTT.Visible = true;
hplDownloadCVPhongTT.NavigateUrl = fileName;
pnLink.Controls.Add(hplDownloadCVPhongTT);
}
private void upload_Click(object sender, EventArgs e)
{
Excel.Application xlApp;
Excel.Workbook xlWorkBook;
Excel.Worksheet xlWorkSheet;
Excel.Range range;
CreateExcelDoc excell_app = new CreateExcelDoc();
string str;
string str1;
int rCnt;
int cCnt;
int rw = 0;
int cl = 0;
int gc = 0;
DateTime now = DateTime.Now;
List <int> fcounts = new List <int>();
xlApp = new Excel.Application();
xlWorkBook = xlApp.Workbooks.Open(@"d:\Test.xlsx", 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
range = xlWorkSheet.UsedRange;
rw = range.Rows.Count;
cl = range.Columns.Count;
for (cCnt = 1; cCnt <= cl; cCnt++)
{
str = (string)(range.Cells[1, cCnt] as Excel.Range).Value2;
excell_app.createHeaders(1, cCnt, str, "Z2", "Z2", 2, "WHITE", true, 10, "n");
}
for (cCnt = 1; cCnt <= cl; cCnt++)
{
for (rCnt = 2; rCnt <= rw; rCnt++)
{
str = (string)(range.Cells[rCnt, cCnt] as Excel.Range).Value2;
if ((string)(range.Cells[rCnt, 3] as Excel.Range).Value2 == (string)(range.Cells[rCnt + 1, 3] as Excel.Range).Value2)
{
gc++;
excell_app.addData(rCnt, cCnt, str, "Z3", "Z3", "#,##0");
}
else if ((string)(range.Cells[rCnt, 3] as Excel.Range).Value2 == (string)(range.Cells[rCnt - 1, 3] as Excel.Range).Value2)
{
gc++;
excell_app.addData(rCnt, cCnt, str, "Z3", "Z3", "#,##0");
if (cCnt == cl)
{
fcounts.Add(gc);
}
gc = 0;
}
}
}
int group_num_st = 0;
int group_num_en = 0;
int start_date = 0;
for (int dif_group = 0; dif_group < fcounts.Count; dif_group++)
{
start_date++;
if (start_date == 4)
{
start_date = 1;
}
//MessageBox.Show(dif_group.ToString());
//start_date = dif_group+1;
if (dif_group == 0)
{
int say = 0;
excell_app.createHeaders(1, cl + 1, "Tarix", "Z2", "Z2", 2, "WHITE", true, 10, "n");
for (int t = 0; t < fcounts[dif_group]; t++)
{
say++;
//Random start = new Random();
//int start_date = start.Next(1, 5);
interval = 30 / fcounts[dif_group];
if (say == 1)
{
excell_app.addData(t + 2, cl + 1, (start_date + "." + now.Month + "." + now.Year).ToString(), "Z3", "Z3", "#,##0");
}
else
{
next = start_date + interval * (say - 1);
int rdate = next;
int month = 0;
if (next > 30)
{
rdate = next % 30;
month = next / 30;
int rmonth = now.Month + month;
excell_app.addData(t + 2, cl + 1, (rdate + "." + rmonth + "." + now.Year).ToString(), "Z3", "Z3", "#,##0");
//MessageBox.Show((rdate + "/" + rmonth + "/" + now.Year).ToString());
}
else
{
excell_app.addData(t + 2, cl + 1, (rdate + "." + now.Month + "." + now.Year).ToString(), "Z3", "Z3", "#,##0");
////MessageBox.Show((rdate + "/" + now.Month + "/" + now.Year).ToString());
}
}
}
//birinci qrup
}
else
{
int say = 0;
//yerde qalan qruplar
group_num_st += fcounts[dif_group - 1];
group_num_en = group_num_st + fcounts[dif_group];
for (int t = group_num_st; t < group_num_en; t++)
{
//excell_app.addData(t + 2, cl + 1, fcounts[dif_group].ToString(), "Z3", "Z3", "#,##0");
say++;
//Random start = new Random();
//int start_date = start.Next(1, 5);
interval = 30 / fcounts[dif_group];
if (say == 1)
{
excell_app.addData(t + 2, cl + 1, (start_date + "." + now.Month + "." + now.Year).ToString(), "Z3", "Z3", "#,##0");
}
else
{
next = start_date + interval * (say - 1);
int rdate = next;
int month = 0;
if (next > 30)
{
rdate = next % 30;
month = next / 30;
int rmonth = now.Month + month;
excell_app.addData(t + 2, cl + 1, (rdate + "." + rmonth + "." + now.Year).ToString(), "Z3", "Z3", "#,##0");
//MessageBox.Show((rdate + "/" + rmonth + "/" + now.Year).ToString());
}
else
{
excell_app.addData(t + 2, cl + 1, (rdate + "." + now.Month + "." + now.Year).ToString(), "Z3", "Z3", "#,##0");
////MessageBox.Show((rdate + "/" + now.Month + "/" + now.Year).ToString());
}
}
}
}
}
xlWorkBook.Close(true, null, null);
xlApp.Quit();
Marshal.ReleaseComObject(xlWorkSheet);
Marshal.ReleaseComObject(xlWorkBook);
Marshal.ReleaseComObject(xlApp);
}
