public static string GetCompanyCode(long IDCompany,FWTransaction fwTrans)
{
DatabaseFB db = fwTrans.db;
DbCommand cmd = db.GetSQLStringCommand(string.Format(@"select c.code
from company_info c
where c.id={0}", IDCompany));
object obj = db.ExecuteScalar(cmd, fwTrans.dbTrans);
if (obj != null) return obj.ToString();
return "";
}
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;
}
}
public static bool SetValueGenerator(FWTransaction fwtrans, string gen, long value)
{
var db = fwtrans.db;
var dbTran = fwtrans.dbTrans;
var cmd =db.GetSQLStringCommand(string.Format("ALTER SEQUENCE {0} RESTART WITH {1}", gen, value));
var rs= db.ExecuteNonQuery(cmd, dbTran);
return true;
}
