private void importExcelThuoc(string dir, string file) { DateTime NgayAn = DateTime.MinValue; decimal KhoiLuong = 0; int SoLuongCa = 0; int SoLuongTT = 0; string columnName = ""; decimal value = 0; int idVatTu = 0; int SoCaAn = 0; string StrChuong = ""; string StrKL = ""; decimal kl = 0; int LoaiCa = 0; string[] aChuongOnly = null; string[] aChuongExcept = null; IFormatProvider culture = new System.Globalization.CultureInfo("vi-VN", true); string Excelstrcon = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + dir + file + @";Extended Properties=""Excel 8.0;HDR=YES;"""; int numSheet = int.Parse(txtNumSheet.Text); for (int num = 1; num <= numSheet; num++) { string SQLstr = "SELECT * FROM [Sheet" + num.ToString() + "$]"; OleDbConnection ExcelCon = new OleDbConnection(Excelstrcon); ExcelCon.Open(); OleDbCommand dbCommand = new OleDbCommand(SQLstr, ExcelCon); OleDbDataAdapter dataAdapter = new OleDbDataAdapter(dbCommand); DataTable dTable = new DataTable(); try { dataAdapter.Fill(dTable); int idx = 2; ArrayList colNames = new ArrayList(); for (int i = 4; i < dTable.Columns.Count; i++) { columnName = dTable.Columns[i].ColumnName; colNames.Add(columnName); if (columnName.StartsWith("Column")) { int cot = i + 1; lblMessage.Text += "<br/>Cột thứ " + cot.ToString() + " sheet " + num.ToString() + " có tên không hợp lệ (" + columnName + ")"; } } foreach (DataRow r in dTable.Rows) { DateTime NgayChoAn = DateTime.Parse(r["Ngay"].ToString() + "/" + r["Thang"].ToString() + "/" + r["Nam"].ToString() + " 23:00:00", culture); DateTime NgayChoAnOutput = DateTime.MinValue; int IDCaSauAn = csCont.CaSauAn_GetCaSauAnByNgay(NgayChoAn, out NgayChoAnOutput); for (int i = 4; i < dTable.Columns.Count; i++) { columnName = colNames[i - 4].ToString(); if (columnName.StartsWith("LoaiCa")) { LoaiCa = Convert.ToInt32(r[i]); aChuongOnly = null; aChuongExcept = null; } else if (columnName.StartsWith("Chuong-Only")) { string sChuongOnly = r[i].ToString(); sChuongOnly = sChuongOnly.Substring(1, sChuongOnly.Length - 2); aChuongOnly = sChuongOnly.Split(new string[] { "!!" }, StringSplitOptions.RemoveEmptyEntries); } else if (columnName.StartsWith("Chuong-Except")) { string sChuongExcept = r[i].ToString(); sChuongExcept = sChuongExcept.Substring(1, sChuongExcept.Length - 2); aChuongExcept = sChuongExcept.Split(new string[] { "!!" }, StringSplitOptions.RemoveEmptyEntries); } else if (columnName.StartsWith("Column")) { } else { value = 0; if (r[i] != DBNull.Value) value = Convert.ToDecimal(r[i]); if (value != 0) { idVatTu = int.Parse(columnName.Substring(columnName.IndexOf('(') + 1, columnName.IndexOf(')') - columnName.IndexOf('(') - 1)); if (IDCaSauAn == 0) IDCaSauAn = csCont.CaSauAn_ThemMoi(NgayChoAn, UserId, ""); DataTable tblChuong = csCont.CaSauAn_GetChuongByThuocByLoaiCa(IDCaSauAn, idVatTu, LoaiCa, out KhoiLuong, out SoLuongCa, out SoLuongTT, out NgayAn); if (aChuongOnly != null) { for (int j = tblChuong.Rows.Count - 1; j > -1; j--) { int k = 0; for (k = 0; k < aChuongOnly.Length; k++) { if (tblChuong.Rows[j]["Chuong"].ToString().Contains(aChuongOnly[k])) { break; } } if (k == aChuongOnly.Length) tblChuong.Rows.RemoveAt(j); } } if (aChuongExcept != null) { for (int j = tblChuong.Rows.Count - 1; j > -1; j--) { for (int k = 0; k < aChuongExcept.Length; k++) { if (tblChuong.Rows[j]["Chuong"].ToString().Contains(aChuongExcept[k])) { tblChuong.Rows.RemoveAt(j); break; } } } } SoCaAn = 0; StrChuong = ""; StrKL = ""; foreach (DataRow rC in tblChuong.Rows) { SoCaAn += Convert.ToInt32(rC["SoLuong"]); } foreach (DataRow rC in tblChuong.Rows) { StrChuong += "@" + rC["IDChuong"].ToString() + "@"; kl = value * Convert.ToDecimal(rC["SoLuong"]) / Convert.ToDecimal(SoCaAn); StrKL += "@" + String.Format("{0:0.#####}", kl).Replace(',', '.') + "@"; } csCont.CaSauAn_InsertUpdateThuoc_UpdateChuong(IDCaSauAn, idVatTu, LoaiCa, SoCaAn, StrChuong, StrKL); } } } idx++; } // dispose used objects dTable.Dispose(); dataAdapter.Dispose(); dbCommand.Dispose(); ExcelCon.Close(); ExcelCon.Dispose(); lblMessage.Text += "<br/>Đã import xong Sheet " + num.ToString() + "!"; } catch (Exception ex) { // dispose used objects dTable.Dispose(); dataAdapter.Dispose(); dbCommand.Dispose(); ExcelCon.Close(); ExcelCon.Dispose(); Response.Write(ex.ToString()); } } }
protected void btnSaveCaSauAn_Click(object sender, EventArgs e) { try { System.Globalization.CultureInfo ci = System.Globalization.CultureInfo.CreateSpecificCulture("vi-VN"); if (txtThoiDiem.Text == "") { Page.ClientScript.RegisterStartupScript(typeof(string), "ngaychoanrong", "alert('Bạn phải nhập ngày cho ăn!');", true); return; } else if (!txtThoiDiem.Text.Contains(" 23:00:00")) { Page.ClientScript.RegisterStartupScript(typeof(string), "ngaychoansai", "alert('Giờ cho ăn phải là 23:00:00!');", true); return; } DateTime dtNgayChoAn = DateTime.Parse(txtThoiDiem.Text, ci); if (dtNgayChoAn < Config.NgayKhoaSo()) { Page.ClientScript.RegisterStartupScript(typeof(string), "insertupdatefail", "alert('Ngày cho ăn không được trước ngày khóa sổ');", true); return; } if (hdCaSauAn.Value != "") { int res = csCont.CaSauAn_Update(int.Parse(hdCaSauAn.Value), dtNgayChoAn, UserId); if (res == 0) { Page.ClientScript.RegisterStartupScript(typeof(string), "ngaykhonghople", "alert('Ngày cho ăn không hợp lệ!');", true); } else { UpdateThucAn(int.Parse(hdCaSauAn.Value)); UpdateThuoc(int.Parse(hdCaSauAn.Value)); LoadThuoc(); Page.ClientScript.RegisterStartupScript(typeof(string), "ngayhople", "alert('Đã cập nhật ngày cho ăn!');", true); } } else { int res = csCont.CaSauAn_ThemMoi(dtNgayChoAn, UserId, ""); if (res != 0) { hdCaSauAn.Value = res.ToString(); tblThuocChuong.Visible = true; lnkThucAn.NavigateUrl = DotNetNuke.Common.Globals.NavigateURL(PortalSettings.ActiveTab.TabID, "casauan_edit_thucan", "csaid/" + hdCaSauAn.Value, "mid/" + this.ModuleId.ToString()); DotNetNuke.Entities.Tabs.TabController t = new DotNetNuke.Entities.Tabs.TabController(); int ThuocMultiPage = t.GetTabByName(ConfigurationManager.AppSettings["QLCS_CaSauAnThuocMulti"], PortalId).TabID; lnkThuocMulti.Attributes["onclick"] = "openwindow('" + DotNetNuke.Common.Globals.NavigateURL(ThuocMultiPage, "", "csaid/" + hdCaSauAn.Value) + "','CaSauAn',900,600);"; //lnkThuocMulti.Attributes["onclick"] = "openwindow('" + DotNetNuke.Common.Globals.NavigateURL(PortalSettings.ActiveTab.TabID, "casauan_thuoc_multi", "csaid/" + hdCaSauAn.Value, "mid/" + this.ModuleId.ToString()) + "','CaSauAn',900,600);"; BindThuocControls(dtNgayChoAn); ddlThuoc_SelectedIndexChanged(null, null); if (ddlLoaiCa.SelectedValue == "1") { scaleCT = scaleCT + 2; } Page.ClientScript.RegisterStartupScript(typeof(string), "initthapphan", "$('#txtThapPhan').val('" + scaleCT.ToString() + "');", true); } else { Page.ClientScript.RegisterStartupScript(typeof(string), "ngaykhonghople", "alert('Ngày cho ăn không hợp lệ!');", true); return; } } } catch (Exception ex) { Response.Write(ex.Message); } }
private void importExcelThucAn(string dir, string file) { string NguoiChoAn = ""; decimal KhoiLuong = 0; int SoLuongCa = 0; int SoLuongTT = 0; DateTime NgayAn = DateTime.MinValue; decimal value = 0; int idVatTu = 0; int SoCaAn = 0; int SoCaTT = 0; string StrChuong = ""; string StrSoLuongChuong = ""; string StrSoLuongChuongTT = ""; string StrPhanCachKhuChuong = ""; string khuchuong = ""; string currkhuchuong = ""; int index = 0; string StrKL = ""; decimal kl = 0; int LoaiCa = 0; string[] aChuongOnly = null; string[] aChuongExcept = null; decimal TongDoiChieu = 0; string f = "{0:0."; for (int i = 0; i < int.Parse(txtThapPhanTA.Text); i++) { f += "#"; } f += "}"; IFormatProvider culture = new System.Globalization.CultureInfo("vi-VN", true); string Excelstrcon = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + dir + file + @";Extended Properties=""Excel 8.0;HDR=YES;"""; int numSheet = int.Parse(txtNumSheet.Text); for (int num = 1; num <= numSheet; num++) { string SQLstr = "SELECT * FROM [Sheet" + num.ToString() + "$]"; OleDbConnection ExcelCon = new OleDbConnection(Excelstrcon); ExcelCon.Open(); OleDbCommand dbCommand = new OleDbCommand(SQLstr, ExcelCon); OleDbDataAdapter dataAdapter = new OleDbDataAdapter(dbCommand); DataTable dTable = new DataTable(); try { dataAdapter.Fill(dTable); int idx = 2; foreach (DataRow r in dTable.Rows) { DateTime NgayChoAn = DateTime.Parse(r["Ngay"].ToString() + "/" + r["Thang"].ToString() + "/" + r["Nam"].ToString() + " 23:00:00", culture); DateTime NgayChoAnOutput = DateTime.MinValue; int IDCaSauAn = csCont.CaSauAn_GetCaSauAnByNgay(NgayChoAn, out NgayChoAnOutput); if (IDCaSauAn != 0 && NgayChoAn != NgayChoAnOutput) { csCont.CaSauAn_Update(IDCaSauAn, NgayChoAn, UserId); } if (idx == 2) { LoaiCa = Convert.ToInt32(r["LoaiCa"]); aChuongOnly = null; aChuongExcept = null; string sChuongOnly = r["Chuong-Only"].ToString(); if (sChuongOnly != "") { sChuongOnly = sChuongOnly.Substring(1, sChuongOnly.Length - 2); aChuongOnly = sChuongOnly.Split(new string[] { "!!" }, StringSplitOptions.RemoveEmptyEntries); } string sChuongExcept = r["Chuong-Except"].ToString(); if (sChuongExcept != "") { sChuongExcept = sChuongExcept.Substring(1, sChuongExcept.Length - 2); aChuongExcept = sChuongExcept.Split(new string[] { "!!" }, StringSplitOptions.RemoveEmptyEntries); } } if (r["SL"] != DBNull.Value && r["SL"].ToString() != "" && Convert.ToDecimal(r["SL"]) != 0) { string NV = ""; if (r["NV"] != DBNull.Value) { NV = r["NV"].ToString(); } if (NV != "") { NV = "@" + NV.Replace(" ", "").Replace(",", "@@") + "@"; } value = Convert.ToDecimal(r["SL"]); string VT = r["VT"].ToString(); idVatTu = int.Parse(VT.Substring(VT.IndexOf('(') + 1, VT.IndexOf(')') - VT.IndexOf('(') - 1)); if (IDCaSauAn == 0) { IDCaSauAn = csCont.CaSauAn_ThemMoi(NgayChoAn, UserId, ""); } DataTable tblChuong = csCont.CaSauAn_GetChuongByThucAnByLoaiCa(IDCaSauAn, idVatTu, LoaiCa, out KhoiLuong, out SoLuongCa, out SoLuongTT, out NguoiChoAn, out NgayAn); if (aChuongOnly != null) { for (int j = tblChuong.Rows.Count - 1; j > -1; j--) { int k = 0; for (k = 0; k < aChuongOnly.Length; k++) { if (tblChuong.Rows[j]["Chuong"].ToString().Contains(aChuongOnly[k])) { break; } } if (k == aChuongOnly.Length) { tblChuong.Rows.RemoveAt(j); } } } if (aChuongExcept != null) { for (int j = tblChuong.Rows.Count - 1; j > -1; j--) { for (int k = 0; k < aChuongExcept.Length; k++) { if (tblChuong.Rows[j]["Chuong"].ToString().Contains(aChuongExcept[k])) { tblChuong.Rows.RemoveAt(j); break; } } } } SoCaAn = 0; SoCaTT = 0; StrChuong = ""; StrSoLuongChuong = ""; StrSoLuongChuongTT = ""; StrKL = ""; foreach (DataRow rC in tblChuong.Rows) { SoCaAn += Convert.ToInt32(rC["SoLuong"]); SoCaTT += Convert.ToInt32(rC["SoLuongTT"]); } TongDoiChieu = 0; int h = 0; foreach (DataRow rC in tblChuong.Rows) { StrChuong += "@" + rC["IDChuong"].ToString() + "@"; kl = value * Convert.ToDecimal(rC["SoLuong"]) / Convert.ToDecimal(SoCaAn); //StrKL += "@" + String.Format("{0:0.#####}", kl).Replace(',', '.') + "@"; TongDoiChieu += Convert.ToDecimal(String.Format(f, kl)); if (h == tblChuong.Rows.Count - 1) { if (TongDoiChieu != value) { decimal temp = Convert.ToDecimal(String.Format(f, kl)) + value - TongDoiChieu; StrKL += "@" + String.Format(f, temp).Replace(',', '.') + "@"; } else { StrKL += "@" + String.Format(f, kl).Replace(',', '.') + "@"; } } else { StrKL += "@" + String.Format(f, kl).Replace(',', '.') + "@"; } h++; StrSoLuongChuong += "@" + rC["SoLuong"].ToString() + "@"; StrSoLuongChuongTT += "@" + rC["SoLuongTT"].ToString() + "@"; currkhuchuong = rC["Chuong"].ToString().Substring(0, 2); if (currkhuchuong != khuchuong) { StrPhanCachKhuChuong += "@" + index + "@"; khuchuong = currkhuchuong; } index++; } StrPhanCachKhuChuong = StrPhanCachKhuChuong.Substring(3) + "@" + index.ToString() + "@"; int res = csCont.CaSauAn_InsertUpdateThucAn(IDCaSauAn, idVatTu, value, LoaiCa, SoCaAn, SoCaTT, StrSoLuongChuong, StrSoLuongChuongTT, StrChuong, StrKL, StrPhanCachKhuChuong, NV, UserId, (!chkValueAdd.Checked)); if (res != 1) { lblMessage.Text += "<br/>Sheet " + num.ToString() + ",dòng " + idx.ToString() + ", thức ăn " + r["VT"].ToString() + " không import được do số lượng hoặc thời điểm biến động không hợp lệ."; } } idx++; } // dispose used objects dTable.Dispose(); dataAdapter.Dispose(); dbCommand.Dispose(); ExcelCon.Close(); ExcelCon.Dispose(); lblMessage.Text += "<br/>Đã import xong Sheet " + num.ToString() + "!"; } catch (Exception ex) { // dispose used objects dTable.Dispose(); dataAdapter.Dispose(); dbCommand.Dispose(); ExcelCon.Close(); ExcelCon.Dispose(); Response.Write(ex.ToString()); } } }