public static DataSet GetDataset(FWTransaction fwTrans, string tableName) { string sql = string.Format("select * from {0}", tableName); var ds = new DataSet(tableName); if(fwTrans.LoadDataSet(ds,sql,tableName)) { return ds; } return null; }
public static bool Insert(DOPhanHoi doPhanhoi) { string strSQL = "insert into YEU_CAU_TRA_LOI values(@id,@yeu_cau_id, @nguoi_gui_id, @nguoi_nhan_id, @ngay_gui, @noi_dung, @nguoi_cap_nhat_id, @ngay_cap_nhat)"; FWDBService db = HelpDB.getDBService(); DbCommand cmd = db.GetSQLStringCommand(strSQL); DbTransaction dbTrans = FWTransaction.BeginTrans(db); FWTransaction fwTrans = new FWTransaction(db, dbTrans); doPhanhoi.ID = HelpGen.DT(); db.AddInParameter(cmd, "@id", DbType.Int64, doPhanhoi.ID); db.AddInParameter(cmd, "@yeu_cau_id", DbType.Int64, doPhanhoi.YEU_CAU_ID); db.AddInParameter(cmd, "@nguoi_gui_id", DbType.Int64, doPhanhoi.NGUOI_GUI_ID); db.AddInParameter(cmd, "@nguoi_nhan_id", DbType.String, doPhanhoi.NGUOI_NHAN_ID); db.AddInParameter(cmd, "@ngay_gui", DbType.DateTime, doPhanhoi.NGAY_GUI); db.AddInParameter(cmd, "@noi_dung", DbType.Binary, doPhanhoi.NOI_DUNG); db.AddInParameter(cmd, "@nguoi_cap_nhat_id", DbType.Int64, doPhanhoi.NGUOI_CAP_NHAT_ID); db.AddInParameter(cmd, "@ngay_cap_nhat", DbType.DateTime, doPhanhoi.NGAY_CAP_NHAT); try { bool flag = false; if (db.ExecuteNonQuery(cmd,dbTrans) > 0) { if (doPhanhoi.DSTapTinDinhKem != null && doPhanhoi.DSTapTinDinhKem.Tables.Count > 0) { DataSet DsLuuTruTapTin = doPhanhoi.DSTapTinDinhKem.Copy(); DataRow[] rows = doPhanhoi.DSTapTinDinhKem.Tables[0].Select("ID is null", "", DataViewRowState.Added); List<long> taptinIDs = db.GetID("G_NGHIEP_VU", dbTrans, rows.Length); int i = 0; foreach (DataRow row in DsLuuTruTapTin.Tables[0].Rows) { if (row.RowState == DataRowState.Deleted) continue; row["OBJECT_ID"] = doPhanhoi.ID; row["TYPE_ID"] = (Int32)10; row["TIEU_DE"] = row["TEN_FILE"]; if (row["ID"] is DBNull) { row["TAP_TIN_ID"] = taptinIDs[i]; row["ID"] = row["TAP_TIN_ID"]; i++; } } DataSet DsObjectTapTin = DsLuuTruTapTin.Copy(); DsObjectTapTin.Tables[0].TableName = "OBJECT_TAP_TIN"; DataSet DsLuuTruTapTinSource = new DataSet(); string sqlTaptin = string.Format(@"SELECT LT.* FROM LUU_TRU_TAP_TIN LT INNER JOIN OBJECT_TAP_TIN OBJ ON OBJ.TAP_TIN_ID=LT.ID AND OBJ.TYPE_ID=10 AND OBJ.OBJECT_ID={0}", doPhanhoi.ID); if (fwTrans.LoadDataSet(DsLuuTruTapTinSource, sqlTaptin, "LUU_TRU_TAP_TIN") == false) { FWTransaction.Rollback(dbTrans); return false; } HelpDataSet.MergeDataSet(new string[] { "ID" }, DsLuuTruTapTinSource, DsLuuTruTapTin, true); DataSet DsObjectTapTinSource = new DataSet(); string sqlObj = string.Format(@"SELECT OBJ.* FROM OBJECT_TAP_TIN OBJ WHERE OBJ.TYPE_ID=10 AND OBJ.OBJECT_ID={0}", doPhanhoi.ID); if (fwTrans.LoadDataSet(DsObjectTapTinSource, sqlObj, "OBJECT_TAP_TIN") == false) { FWTransaction.Rollback(dbTrans); return false; } HelpDataSet.MergeTable(new string[] { "TAP_TIN_ID" }, DsObjectTapTinSource.Tables[0], DsObjectTapTin.Tables[0], true, true); flag = db.UpdateDataSet(DsLuuTruTapTinSource, dbTrans); if (flag == true) { foreach (DataRow r in DsObjectTapTinSource.Tables[0].Rows) { DbCommand cmd1 = null; if (r.RowState == DataRowState.Added) { cmd1 = db.GetSQLStringCommand(string.Format(@"INSERT INTO OBJECT_TAP_TIN(TAP_TIN_ID,OBJECT_ID,TYPE_ID) VALUES({0},{1},10)", r["TAP_TIN_ID"], r["OBJECT_ID"], r["TYPE_ID"])); } if (cmd1 != null) { if (db.ExecuteNonQuery(cmd1, dbTrans) <= 0) { FWTransaction.Rollback(dbTrans); return false; } } } } } if (flag) { FWTransaction.Commit(dbTrans); return true; } else { FWTransaction.Rollback(dbTrans); return false; } } return false; } catch { FWTransaction.Rollback(dbTrans); return false; } }
public static bool Update(DOPhanHoi doPhanhoi) { DataSet ds = HelpDB.getDatabase().LoadDataSet(string.Format(@"SELECT * FROM YEU_CAU_TRA_LOI WHERE ID = {0}", doPhanhoi.ID)); ds.Tables[0].TableName = "YEU_CAU_TRA_LOI"; FWDBService db = HelpDB.getDBService(); DbTransaction dbTrans = FWTransaction.BeginTrans(db); FWTransaction fwTrans = new FWTransaction(db, dbTrans); ds.Tables[0].Rows[0]["NOI_DUNG"] = doPhanhoi.NOI_DUNG; ds.Tables[0].Rows[0]["NGAY_GUI"] = doPhanhoi.NGAY_GUI; ds.Tables[0].Rows[0]["NGUOI_GUI_ID"] = doPhanhoi.NGUOI_GUI_ID; ds.Tables[0].Rows[0]["NGUOI_NHAN_ID"] = doPhanhoi.NGUOI_NHAN_ID; try { if (db.UpdateTable(ds, "YEU_CAU_TRA_LOI", dbTrans) <= 0) return false; else { bool flag = false; if (doPhanhoi.DSTapTinDinhKem != null && doPhanhoi.DSTapTinDinhKem.Tables.Count > 0) { //DataSet DsLuuTruTapTin = doPhanhoi.DSTapTinDinhKem.Copy();//không copy được DataSet DsLuuTruTapTin = doPhanhoi.DSTapTinDinhKem; DataRow[] rows = doPhanhoi.DSTapTinDinhKem.Tables[0].Select("ID is null", "", DataViewRowState.Added); List<long> taptinIDs = db.GetID("G_NGHIEP_VU", dbTrans, rows.Length); int i = 0; foreach (DataRow row in DsLuuTruTapTin.Tables[0].Rows) { if (row.RowState == DataRowState.Deleted) continue; row["OBJECT_ID"] = doPhanhoi.ID; row["TYPE_ID"] = (Int32)10; row["TIEU_DE"] = row["TEN_FILE"]; if (row["ID"] is DBNull) { row["TAP_TIN_ID"] = taptinIDs[i]; row["ID"] = row["TAP_TIN_ID"]; i++; } } DataSet DsObjectTapTin = DsLuuTruTapTin.Copy(); DsObjectTapTin.Tables[0].TableName = "OBJECT_TAP_TIN"; DataSet DsLuuTruTapTinSource = new DataSet(); string sqlTaptin = string.Format(@"SELECT LT.* FROM LUU_TRU_TAP_TIN LT INNER JOIN OBJECT_TAP_TIN OBJ ON OBJ.TAP_TIN_ID=LT.ID AND OBJ.TYPE_ID=10 AND OBJ.OBJECT_ID={0}", doPhanhoi.ID); if (fwTrans.LoadDataSet(DsLuuTruTapTinSource, sqlTaptin, "LUU_TRU_TAP_TIN") == false) { FWTransaction.Rollback(dbTrans); return false; } HelpDataSet.MergeDataSet(new string[] { "ID" }, DsLuuTruTapTinSource, DsLuuTruTapTin, true); DataSet DsObjectTapTinSource = new DataSet(); string sqlObj = string.Format(@"SELECT OBJ.* FROM OBJECT_TAP_TIN OBJ WHERE OBJ.TYPE_ID=10 AND OBJ.OBJECT_ID={0}", doPhanhoi.ID); if (fwTrans.LoadDataSet(DsObjectTapTinSource, sqlObj, "OBJECT_TAP_TIN") == false) { FWTransaction.Rollback(dbTrans); return false; } HelpDataSet.MergeTable(new string[] { "TAP_TIN_ID" }, DsObjectTapTinSource.Tables[0], DsObjectTapTin.Tables[0], true, true); flag = db.UpdateDataSet(DsLuuTruTapTinSource, dbTrans); if (flag == true) { foreach (DataRow r in DsObjectTapTinSource.Tables[0].Rows) { DbCommand cmd1 = null; if (r.RowState == DataRowState.Added) { cmd1 = db.GetSQLStringCommand(string.Format(@"INSERT INTO OBJECT_TAP_TIN(TAP_TIN_ID,OBJECT_ID,TYPE_ID) VALUES({0},{1},10)", r["TAP_TIN_ID"], r["OBJECT_ID"], r["TYPE_ID"])); } if (cmd1 != null) { if (db.ExecuteNonQuery(cmd1, dbTrans) <= 0) { FWTransaction.Rollback(dbTrans); return false; } } } } } if (flag) { FWTransaction.Commit(dbTrans); return true; } else { FWTransaction.Rollback(dbTrans); return false; } } } catch { FWTransaction.Rollback(dbTrans); return false; } }
//Chỉ chuyển dữ liệu thời gian làm việc private void MoveTimeInOut(List<object> ids) { bool bFlag = true; if (ids != null) { DataSet forDBTime = new DataSet(); DataSet forDBChamCongAuto = new DataSet(); FWDBService db = null; DbTransaction trans = null; FWTransaction fwTrans = null; try { db = HelpDB.getDBService(); trans = FWTransaction.BeginTrans(db); fwTrans = new FWTransaction(db, trans); fwTrans.LoadDataSet(forDBTime, "SELECT * FROM " + DATimeInOut.TABLE_MAP, DATimeInOut.TABLE_MAP); fwTrans.LoadDataSet(forDBChamCongAuto, "SELECT * FROM " + DAChamCongAuto.TABLE_MAP, DAChamCongAuto.TABLE_MAP); foreach (DataRow row in ((DataView)gridViewMaster.DataSource).Table.Rows) { bool IsChamCong = (row["IS_CHAM_CONG"].ToString() != "Y"); if ((HelpNumber.ParseInt64(row["LOAI"]) == (Int32)TimeInOutType.GhiThoiGian && IsChamCong)) { long NV_ID = HelpNumber.ParseInt64(row["NV_ID"]); DOChamCongAuto phieu = DAChamCongAuto.Instance.LoadAll(fwTrans, NV_ID, (DateTime)row["NGAY_LAM_VIEC"]); if (phieu.DetailDataSet.Tables[0].Select("NV_ID=" + NV_ID + " and NGAY='" + System.Convert.ToDateTime(row["NGAY_LAM_VIEC"]) + "'").Length == 0) { phieu.NV_ID = NV_ID; phieu.NGAY = System.Convert.ToDateTime(row["NGAY_LAM_VIEC"]); } if (HelpNumber.ParseInt64(row["LOAI"]) == (Int32)TimeInOutType.GhiThoiGian) { phieu.SANG = ""; phieu.CHIEU = ""; if (row["THOI_GIAN_SANG"] != null && row["THOI_GIAN_SANG"].ToString() != string.Empty) { string[] TGSang = row["THOI_GIAN_SANG"].ToString().Split(':'); phieu.SANG = HelpNumber.RoundDecimal(HelpNumber.ParseDecimal((System.Convert.ToInt32(TGSang[0]) + (System.Convert.ToInt32(TGSang[1]) / 60.0))), 2).ToString(); } if (row["THOI_GIAN_CHIEU"] != null && row["THOI_GIAN_CHIEU"].ToString() != string.Empty) { string[] TGChieu = row["THOI_GIAN_CHIEU"].ToString().Split(':'); phieu.CHIEU = HelpNumber.RoundDecimal(HelpNumber.ParseDecimal((System.Convert.ToInt32(TGChieu[0]) + (System.Convert.ToInt32(TGChieu[1]) / 60.0))), 2).ToString(); } } bool bflag = DAChamCongAuto.Instance.MergeDataSet(forDBChamCongAuto, phieu); #region Cập nhật tình trạng chấm công tự động if (bFlag) { DOTimeInOut doPhieu = DATimeInOut.Instance.LoadAll(HelpNumber.ParseInt64(row["ID"])); doPhieu.IS_CHAM_CONG = "Y"; bFlag = DATimeInOut.Instance.MergeDataSet(forDBTime, doPhieu); } #endregion } } bFlag = db.UpdateDataSet(forDBChamCongAuto, trans); bFlag = db.UpdateDataSet(forDBTime, trans); FWTransaction.Commit(trans); } catch (Exception ex) { PLException.AddException(ex); FWTransaction.Rollback(trans); } } }
//Chỉ chuyển dữ liệu xin phép private void MoveNghiPhep(List<object> ids) { bool bFlag = true; if (ids != null) { DataSet forDBTime = new DataSet();//DABase.getDatabase().LoadTable(DATimeInOut.TABLE_MAP); DataSet forDBChamCongAuto = new DataSet();//DABase.getDatabase().LoadTable(DAChamCongAuto.TABLE_MAP); FWDBService db = null; DbTransaction trans = null; FWTransaction fwTrans = null; try { db = HelpDB.getDBService(); trans = FWTransaction.BeginTrans(db); fwTrans = new FWTransaction(db, trans); fwTrans.LoadDataSet(forDBTime, "SELECT * FROM " + DATimeInOut.TABLE_MAP, DATimeInOut.TABLE_MAP); fwTrans.LoadDataSet(forDBChamCongAuto, "SELECT * FROM " + DAChamCongAuto.TABLE_MAP, DAChamCongAuto.TABLE_MAP); foreach (DataRow row in ((DataView)gridViewMaster.DataSource).Table.Rows) { bool IsChamCong = (row["IS_CHAM_CONG"].ToString() != "Y"); bool Duyet = (row["DUYET"] != null && row["DUYET"].ToString() == "2"); if ((HelpNumber.ParseInt64(row["LOAI"]) == (Int32)TimeInOutType.NghiPhep && IsChamCong && Duyet)) { long NV_ID = HelpNumber.ParseInt64(row["NV_ID"]); DOChamCongAuto phieu = DAChamCongAuto.Instance.LoadAll(fwTrans, NV_ID, (DateTime)row["NGAY_LAM_VIEC"]); if (phieu.DetailDataSet.Tables[0].Select("NV_ID=" + NV_ID + " and NGAY='" + System.Convert.ToDateTime(row["NGAY_LAM_VIEC"]) + "'").Length == 0) { phieu.NV_ID = NV_ID; phieu.NGAY = System.Convert.ToDateTime(row["NGAY_LAM_VIEC"]); } if (row["NGHI_BUOI_SANG"] != null && row["NGHI_BUOI_SANG"].ToString() != string.Empty && row["NGHI_BUOI_SANG"].ToString() == "Y") phieu.SANG = string.Compare(row["NGHI_PHEP_NAM"].ToString(), "Y") == 0 ? "4" : "N"; if (row["NGHI_BUOI_CHIEU"] != null && row["NGHI_BUOI_CHIEU"].ToString() != string.Empty && row["NGHI_BUOI_CHIEU"].ToString() == "Y") phieu.CHIEU = string.Compare(row["NGHI_PHEP_NAM"].ToString(), "Y") == 0 ? "4" : "N"; bool bflag = DAChamCongAuto.Instance.MergeDataSet(forDBChamCongAuto, phieu); #region Cập nhật tình trạng chấm công tự động if (bFlag) { DOTimeInOut doPhieu = DATimeInOut.Instance.LoadAll(HelpNumber.ParseInt64(row["ID"])); doPhieu.IS_CHAM_CONG = "Y"; bFlag = DATimeInOut.Instance.MergeDataSet(forDBTime, doPhieu); } #endregion } } bFlag = db.UpdateDataSet(forDBChamCongAuto, trans); bFlag = db.UpdateDataSet(forDBTime, trans); FWTransaction.Commit(trans); } catch (Exception ex) { PLException.AddException(ex); FWTransaction.Rollback(trans); } finally { if (FrameworkParams.wait != null) FrameworkParams.wait.Finish(); } } }
public static bool Update(long ID,string Nguoi_nhan_ID, long Tinh_trang, long Loai_yeu_cau, long Muc_uu_tien, string Chu_de, byte[] Noi_dung, DataSet dsFile) { string Sql = @"Update YEU_CAU SET NGUOI_NHAN_ID=@NGUOI_NHAN_ID,TINH_TRANG=@TINH_TRANG,LOAI_YEU_CAU_ID=@LOAI_YEU_CAU_ID, MUC_UU_TIEN=@MUC_UU_TIEN,CHU_DE=@CHU_DE,NOI_DUNG=@NOI_DUNG Where ID=@ID "; FWDBService db = HelpDB.getDBService(); DbCommand cmd = db.GetSQLStringCommand(Sql); DbTransaction dbTrans = FWTransaction.BeginTrans(db); FWTransaction fwTrans = new FWTransaction(db, dbTrans); try { db.AddInParameter(cmd, "@NGUOI_NHAN_ID", DbType.String, Nguoi_nhan_ID); db.AddInParameter(cmd, "@TINH_TRANG", DbType.Int64, Tinh_trang); db.AddInParameter(cmd, "@LOAI_YEU_CAU_ID", DbType.Int64, Loai_yeu_cau); db.AddInParameter(cmd, "@MUC_UU_TIEN", DbType.Int64, Muc_uu_tien); db.AddInParameter(cmd, "@CHU_DE", DbType.String, Chu_de); db.AddInParameter(cmd, "@NOI_DUNG", DbType.Binary, Noi_dung); db.AddInParameter(cmd, "@ID", DbType.Int64, ID); bool flag = false; if (db.ExecuteNonQuery(cmd, dbTrans) > 0) { if (dsFile != null && dsFile.Tables.Count > 0) { DataSet DsLuuTruTapTin = dsFile.Copy(); DataRow[] rows = dsFile.Tables[0].Select("ID is null", "", DataViewRowState.Added); List<long> taptinIDs = db.GetID("G_NGHIEP_VU", dbTrans, rows.Length); int i = 0; foreach (DataRow row in DsLuuTruTapTin.Tables[0].Rows) { if (row.RowState == DataRowState.Deleted) continue; row["OBJECT_ID"] = ID; row["TYPE_ID"] = (Int32)10; row["TIEU_DE"] = Chu_de; if (row["ID"] is DBNull) { row["TAP_TIN_ID"] = taptinIDs[i]; row["ID"] = row["TAP_TIN_ID"]; i++; } } DataSet DsObjectTapTin = DsLuuTruTapTin.Copy(); DsObjectTapTin.Tables[0].TableName = "OBJECT_TAP_TIN"; DataSet DsLuuTruTapTinSource = new DataSet(); string sqlTaptin = string.Format(@"SELECT LT.* FROM LUU_TRU_TAP_TIN LT INNER JOIN OBJECT_TAP_TIN OBJ ON OBJ.TAP_TIN_ID=LT.ID AND OBJ.TYPE_ID=10 AND OBJ.OBJECT_ID={0}", ID); if (fwTrans.LoadDataSet(DsLuuTruTapTinSource, sqlTaptin, "LUU_TRU_TAP_TIN") == false) { FWTransaction.Rollback(dbTrans); return false; } HelpDataSet.MergeDataSet(new string[] { "ID" }, DsLuuTruTapTinSource, DsLuuTruTapTin, true); DataSet DsObjectTapTinSource = new DataSet(); string sqlObj = string.Format(@"SELECT OBJ.* FROM OBJECT_TAP_TIN OBJ WHERE OBJ.TYPE_ID=10 AND OBJ.OBJECT_ID={0}", ID); if (fwTrans.LoadDataSet(DsObjectTapTinSource, sqlObj, "OBJECT_TAP_TIN") == false) { FWTransaction.Rollback(dbTrans); return false; } HelpDataSet.MergeTable(new string[] { "TAP_TIN_ID" }, DsObjectTapTinSource.Tables[0], DsObjectTapTin.Tables[0], true, true); flag = db.UpdateDataSet(DsLuuTruTapTinSource, dbTrans); if (flag == true) { foreach (DataRow r in DsObjectTapTinSource.Tables[0].Rows) { DbCommand cmd1 = null; if (r.RowState == DataRowState.Added) { cmd1 = db.GetSQLStringCommand(string.Format(@"INSERT INTO OBJECT_TAP_TIN(TAP_TIN_ID,OBJECT_ID,TYPE_ID) VALUES({0},{1},10)", r["TAP_TIN_ID"], r["OBJECT_ID"], r["TYPE_ID"])); } if (cmd1 != null) { if (db.ExecuteNonQuery(cmd1, dbTrans) <= 0) { FWTransaction.Rollback(dbTrans); return false; } } } } } if (flag) { FWTransaction.Commit(dbTrans); return true; } else { FWTransaction.Rollback(dbTrans); return false; } } return false; } catch { FWTransaction.Rollback(dbTrans); return false; } }