protected void btnImport_Click(object sender, EventArgs e) { DataTable dt = new DataTable(); string connectionString = String.Format(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=""Excel 8.0;HDR=YES;IMEX=1;""", Server.MapPath("~/UserFile/Data_2016.xls")); //string query = String.Format("select * from [{0}$]", "Area"); string query = String.Format("select * from [{0}$]", "2014_2015"); OleDbDataAdapter dataAdapter = new OleDbDataAdapter(query, connectionString); DataSet dataSet = new DataSet(); dataAdapter.Fill(dataSet); DataTable myTable = dataSet.Tables[0]; EnterpriseService comBSO = new EnterpriseService(); foreach (DataRow drow in myTable.Rows) { ReportTemp2014 temp = new ReportTemp2014(); temp.Title = drow["Title"].ToString(); if (temp.Title != null && temp.Title != "") { temp.OrgId = Convert.ToInt32(drow["OrgId"]); DataTable dtE = comBSO.GetDNByName(temp.Title, temp.OrgId); if (drow["Address"] != null) { temp.Address = drow["Address"].ToString(); } if (drow["AreaName"] != null && drow["AreaName"].ToString() != "") { temp.AreaName = drow["AreaName"].ToString(); if (drow["AreaName"].ToString() == "Công nghiệp") { temp.AreaId = 5; } else if (drow["AreaName"].ToString() == "Nông nghiệp") { temp.AreaId = 3; } else if (drow["AreaName"].ToString() == "Công trình xây dựng") { temp.AreaId = 6; } else { temp.AreaId = 1; } } //Kiem phan nganh da co trong CSDL chua if (drow["SubAreaName"] != null && drow["SubAreaName"].ToString() != "") { DataTable dtSub = new AreaService().getAreaByName(drow["SubAreaName"].ToString()); if (dtSub != null && dtSub.Rows.Count > 0) { temp.SubAreaId = Convert.ToInt32(dtSub.Rows[0]["Id"]); } else {//Neu khong ton tai thi bo sung them phan nganh Area sub = new Area(); sub.AreaName = drow["SubAreaName"].ToString(); sub.ParentId = temp.AreaId; sub.IsStatus = 1; sub.SortOrder = 0; int subId = new AreaService().Insert(sub); temp.SubAreaId = subId; } temp.SubAreaName = drow["SubAreaName"].ToString(); } //Kiem tra xem doanh nghiep da ton tai if (dtE != null && dtE.Rows.Count > 0) { if (dtE.Rows.Count == 1) { Enterprise area = new Enterprise(); int eId = Convert.ToInt32(dtE.Rows[0]["Id"]); ////area = new EnterpriseService().FindByKey(eId); ////area.SubAreaId = temp.SubAreaId; ////area.AreaId = temp.AreaId; EnterpriseYearService eYService = new EnterpriseYearService(); EnterpriseYear ey = new EnterpriseYear(); ey.EnterpriseId = eId; if (drow["No_TOE"] != null && drow["No_TOE"].ToString().Trim() != "") { ey.No_TOE = Convert.ToDecimal(drow["No_TOE"]); } ey.IsDelete = false; ey.Year = 2016; eYService.Insert(ey);//Insert vao doanh nghiep trong diem nam temp.EnterpriseId = eId; if (drow["Dien_kWh"] != null && drow["Dien_kWh"].ToString().Trim() != "") { temp.Dien_kWh = drow["Dien_kWh"].ToString(); } if (drow["Than_Tan"] != null && drow["Than_Tan"].ToString().Trim() != "") { temp.Than_Tan = drow["Than_Tan"].ToString(); } if (drow["DO_Tan"] != null && drow["DO_Tan"].ToString().Trim() != "") { temp.DO_Tan = drow["DO_Tan"].ToString(); } if (drow["DO_lit"] != null && drow["DO_lit"].ToString().Trim() != "") { temp.DO_lit = drow["DO_lit"].ToString(); } if (drow["FO_Tan"] != null && drow["FO_Tan"].ToString().Trim() != "") { temp.FO_Tan = drow["FO_Tan"].ToString(); } if (drow["FO_lit"] != null && drow["FO_lit"].ToString().Trim() != "") { temp.FO_lit = drow["FO_Tan"].ToString(); } if (drow["Xang_Tan"] != null && drow["Xang_Tan"].ToString().Trim() != "") { temp.Xang_Tan = drow["Xang_Tan"].ToString(); } if (drow["Xang_lit"] != null && drow["Xang_lit"].ToString().Trim() != "") { temp.Xang_lit = drow["Xang_lit"].ToString(); } if (drow["Gas_Tan"] != null && drow["Gas_Tan"].ToString().Trim() != "") { temp.Gas_Tan = drow["Gas_Tan"].ToString(); } if (drow["Khi_m3"] != null && drow["Khi_m3"].ToString().Trim() != "") { temp.Khi_M3 = drow["Khi_m3"].ToString(); } if (drow["LPG_Tan"] != null && drow["LPG_Tan"].ToString().Trim() != "") { temp.LPG_Tan = drow["LPG_Tan"].ToString(); } if (drow["NLPL_Tan"] != null && drow["NLPL_Tan"].ToString().Trim() != "") { temp.NLPL_Tan = drow["NLPL_Tan"].ToString(); } if (drow["Khac_tan"] != null && drow["Khac_tan"].ToString().Trim() != "") { temp.KhacSoDo = drow["Khac_tan"].ToString(); } if (drow["No_TOE"] != null && drow["No_TOE"].ToString().Trim() != "" && Convert.ToDecimal(drow["No_TOE"]) > 0) { temp.No_TOE = Convert.ToDecimal(drow["No_TOE"]); } temp.Year = 2016; int retTemp = new ReportTemp2014Service().Insert(temp);//Them bao cao tam } else { if (drow["Dien_kWh"] != null && drow["Dien_kWh"].ToString().Trim() != "") { temp.Dien_kWh = drow["Dien_kWh"].ToString(); } if (drow["Than_Tan"] != null && drow["Than_Tan"].ToString().Trim() != "") { temp.Than_Tan = drow["Than_Tan"].ToString(); } if (drow["DO_Tan"] != null && drow["DO_Tan"].ToString().Trim() != "") { temp.DO_Tan = drow["DO_Tan"].ToString(); } if (drow["DO_lit"] != null && drow["DO_lit"].ToString().Trim() != "") { temp.DO_lit = drow["DO_lit"].ToString(); } if (drow["FO_Tan"] != null && drow["FO_Tan"].ToString().Trim() != "") { temp.FO_Tan = drow["FO_Tan"].ToString(); } if (drow["FO_lit"] != null && drow["FO_lit"].ToString().Trim() != "") { temp.FO_lit = drow["FO_Tan"].ToString(); } if (drow["Xang_Tan"] != null && drow["Xang_Tan"].ToString().Trim() != "") { temp.Xang_Tan = drow["Xang_Tan"].ToString(); } if (drow["Xang_lit"] != null && drow["Xang_lit"].ToString().Trim() != "") { temp.Xang_lit = drow["Xang_lit"].ToString(); } if (drow["Gas_Tan"] != null && drow["Gas_Tan"].ToString().Trim() != "") { temp.Gas_Tan = drow["Gas_Tan"].ToString(); } if (drow["Khi_m3"] != null && drow["Khi_m3"].ToString().Trim() != "") { temp.Khi_M3 = drow["Khi_m3"].ToString(); } if (drow["LPG_Tan"] != null && drow["LPG_Tan"].ToString().Trim() != "") { temp.LPG_Tan = drow["LPG_Tan"].ToString(); } if (drow["NLPL_Tan"] != null && drow["NLPL_Tan"].ToString().Trim() != "") { temp.NLPL_Tan = drow["NLPL_Tan"].ToString(); } if (drow["Khac_tan"] != null && drow["Khac_tan"].ToString().Trim() != "") { temp.KhacSoDo = drow["Khac_tan"].ToString(); } if (drow["No_TOE"] != null && drow["No_TOE"].ToString().Trim() != "" && Convert.ToDecimal(drow["No_TOE"]) > 0) { temp.No_TOE = Convert.ToDecimal(drow["No_TOE"]); } temp.Year = 2016; if (dtE.Rows.Count > 1) { temp.Note = "Tim thay " + dtE.Rows.Count + " DN"; } else { temp.Note = "Khong tim thay " + dtE.Rows.Count + " DN"; } int retTemp = new ReportTemp2014Service().Insert(temp);//Them bao cao tam } } else { if (drow["Dien_kWh"] != null && drow["Dien_kWh"].ToString().Trim() != "") { temp.Dien_kWh = drow["Dien_kWh"].ToString(); } if (drow["Than_Tan"] != null && drow["Than_Tan"].ToString().Trim() != "") { temp.Than_Tan = drow["Than_Tan"].ToString(); } if (drow["DO_Tan"] != null && drow["DO_Tan"].ToString().Trim() != "") { temp.DO_Tan = drow["DO_Tan"].ToString(); } if (drow["DO_lit"] != null && drow["DO_lit"].ToString().Trim() != "") { temp.DO_lit = drow["DO_lit"].ToString(); } if (drow["FO_Tan"] != null && drow["FO_Tan"].ToString().Trim() != "") { temp.FO_Tan = drow["FO_Tan"].ToString(); } if (drow["FO_lit"] != null && drow["FO_lit"].ToString().Trim() != "") { temp.FO_lit = drow["FO_Tan"].ToString(); } if (drow["Xang_Tan"] != null && drow["Xang_Tan"].ToString().Trim() != "") { temp.Xang_Tan = drow["Xang_Tan"].ToString(); } if (drow["Xang_lit"] != null && drow["Xang_lit"].ToString().Trim() != "") { temp.Xang_lit = drow["Xang_lit"].ToString(); } if (drow["Gas_Tan"] != null && drow["Gas_Tan"].ToString().Trim() != "") { temp.Gas_Tan = drow["Gas_Tan"].ToString(); } if (drow["Khi_m3"] != null && drow["Khi_m3"].ToString().Trim() != "") { temp.Khi_M3 = drow["Khi_m3"].ToString(); } if (drow["LPG_Tan"] != null && drow["LPG_Tan"].ToString().Trim() != "") { temp.LPG_Tan = drow["LPG_Tan"].ToString(); } if (drow["NLPL_Tan"] != null && drow["NLPL_Tan"].ToString().Trim() != "") { temp.NLPL_Tan = drow["NLPL_Tan"].ToString(); } if (drow["Khac_tan"] != null && drow["Khac_tan"].ToString().Trim() != "") { temp.KhacSoDo = drow["Khac_tan"].ToString(); } if (drow["No_TOE"] != null && drow["No_TOE"].ToString().Trim() != "" && Convert.ToDecimal(drow["No_TOE"]) > 0) { temp.No_TOE = Convert.ToDecimal(drow["No_TOE"]); } temp.Year = 2016; temp.Note = "Khong tim thay " + dtE.Rows.Count + " DN"; int retTemp = new ReportTemp2014Service().Insert(temp);//Them bao cao tam } } } }