public static DataRow LookupDM_Thuoc(object id)
{
string sql = string.Format(@"select * from THUOC where IDTHUOC = {0} ", Convert.ToInt32(id));
DataTable dt = KetNoiData.ExecuteToDataTable(sql);
//đã có đữ liệu
if (dt.Rows.Count == 1)
{
return(dt.Rows[0]);
}
sql = "select 1 as ischanged, * from THUOC order by TENTHUOC";
dt = KetNoiData.ExecuteToDataTable(sql);
List <object> arrColumn = new List <object>();
arrColumn.AddRange(new object[] {
"IDTHUOC", "IDTHUOC", 100, "", false,
"MATHUOC", "Mã Thuốc", 100, "", true,
"TENTHUOC", "Tên Thuốc", 100, "", true
});
GPP_DungChung_HL.Form.FrmLookupData frm = new GPP_DungChung_HL.Form.FrmLookupData("Danh mục Thuốc", dt, arrColumn
, string.Format("Contains([IDTHUOC], '{0}')", Convert.ToInt32(id)));
if (frm.ShowDialog() == DialogResult.OK)
{
return(frm.RowResult);
}
return(null);
}
public DataTable DanhMuc_Thuoc()
{
string str_sql = "Select * from THUOC order by TENTHUOC";
KetNoiData.ConnectData();
return(KetNoiData.ExecuteToDataTable(str_sql));
}
public DataTable DanhMuc_HOATCHAT()
{
string str_sql = "Select * from HOATCHAT";
KetNoiData.ConnectData();
return(KetNoiData.ExecuteToDataTable(str_sql));
}
public DataTable DanhMuc_LOAISP()
{
string str_sql = "Select * from LOAISP";
KetNoiData.ConnectData();
return(KetNoiData.ExecuteToDataTable(str_sql));
}
public DataTable DanhMuc_KHACHHANG()
{
string str_sql = "Select * from KHACHHANG";
KetNoiData.ConnectData();
return(KetNoiData.ExecuteToDataTable(str_sql));
}
//Mặc định key nằm ở vi trí đầu
public bool UpdateCommand(DataRow rw)
{
try
{
KetNoiData.UpdateCommand(table,
"IDTHUOC", SqlDbType.Int, rw["IDTHUOC"],
"IDLOAITHUOC", SqlDbType.Int, rw["IDLOAITHUOC"],
"IDSANPHAM", SqlDbType.Int, rw["IDSANPHAM"],
"IDDVT", SqlDbType.Int, rw["IDDVT"],
"MATHUOC", SqlDbType.NVarChar, rw["MATHUOC"],
"TENTHUOC", SqlDbType.NVarChar, rw["TENTHUOC"],
"NHIETDOBQ", SqlDbType.NVarChar, rw["NHIETDOBQ"],
"TRANHAM", SqlDbType.Int, rw["TRANHAM"],
"TRANHANHSANG", SqlDbType.Int, rw["TRANHANHSANG"],
"TRANHNHIETDO", SqlDbType.Int, rw["TRANHNHIETDO"],
"TRANHDONG", SqlDbType.Int, rw["TRANHDONG"],
"idnsx", SqlDbType.Int, rw["idnsx"],
"GhiChu", SqlDbType.NVarChar, rw["GhiChu"],
"ViTriThuoc", SqlDbType.NVarChar, rw["ViTriThuoc"],
"Dongia", SqlDbType.NVarChar, rw["Dongia"]
);
return(true);
}
catch (Exception e)
{
return(false);
throw e;
}
}
public DataTable DanhMuc_DUOCSI()
{
string str_sql = "Select * from DUOCSI";
KetNoiData.ConnectData();
return(KetNoiData.ExecuteToDataTable(str_sql));
}
public DataTable DanhMuc_TOPID(string key, string table)
{
string sql = string.Format("Select top 1 {0} as maxid from {1} order by {0} desc", key, table);
KetNoiData.ConnectData();
return(KetNoiData.ExecuteToDataTable(sql));
}
public DataTable DanhMuc_CTHCTHUOC()
{
string str_sql = "Select * from CTHC_THUOC";
KetNoiData.ConnectData();
return(KetNoiData.ExecuteToDataTable(str_sql));
}
/// <summary>
/// Xóa dữ liệu
/// </summary>
/// <returns></returns>
public DataTable DeleteData(object soid)
{
DataSet ds = KetNoiData.ExecuteToDataSet("sp_kho_Xoa_NhapKho_Xml", CommandType.StoredProcedure
, "@soid", SqlDbType.VarChar, soid
);
return(ds.Tables[0]);
}
public DataTable LaySoTon(object id)
{
string store = string.Format("sp_kho_LayTon_SoLo_Xml");
KetNoiData.ConnectData();
return(KetNoiData.ExecuteToDataSet(store, CommandType.StoredProcedure, "@idThuoc", SqlDbType.Int, id).Tables[0]);
}
/// <summary>
/// đếm từ 1.
/// </summary>
/// <param name="table"></param>
/// <returns></returns>
public int LaySoMaxHD(string table)
{
string sql = string.Format("select count(*) as maxid from {0} where Year(NGAYXUAT) = Year(GETDATE()) and Month(NGAYXUAT) = MONTH(GETDATE())", table);
KetNoiData.ConnectData();
DataTable dt = KetNoiData.ExecuteToDataTable(sql);
return(Convert.ToInt32(dt.Rows[0]["maxid"]));
}
/// <summary>
/// Cập nhật dữ liệu
/// </summary>
/// <returns></returns>
public DataTable UpdateData(object soid, object xml_g, object xml_ct)
{
DataSet ds = KetNoiData.ExecuteToDataSet("sp_kho_Update_NhapKho_Xml", CommandType.StoredProcedure
, "@soid", SqlDbType.Int, soid
, "@XmlData_g", SqlDbType.NVarChar, xml_g.ToString()
, "@XmlData_ct", SqlDbType.NVarChar, xml_ct.ToString()
);
return(ds.Tables[0]);
}
/// <summary>
/// Thêm dữ liệu
/// </summary>
/// <returns></returns>
public DataTable InsertData(object soid, object xml_g, object xml_ct, int rowcount_ct)
{
DataSet ds = KetNoiData.ExecuteToDataSet("sp_kho_Insert_NhapKho_Xml", CommandType.StoredProcedure
, "@soid", SqlDbType.Int, soid
, "@XmlData_g", SqlDbType.NVarChar, xml_g.ToString()
, "@XmlData_ct", SqlDbType.NVarChar, xml_ct.ToString()
, "@rowcount_g", SqlDbType.Int, 1
, "@rowcount_ct", SqlDbType.Int, rowcount_ct
);
return(ds.Tables[0]);
}
public DataTable ConnectDATA_BaoCaoThuoc_CanDate(int soThang)
{
string str = "sp_kho_BaoCaoTon_TheoThuoc_SoLo_Xml";
try
{
DataTable dt = KetNoiData.ExecuteToDataTable(str, CommandType.StoredProcedure,
"@soMonth", SqlDbType.Int, soThang);
return(dt);
}
catch (Exception e)
{
throw e;
}
}
public bool DeleteCommand(int id)
{
try
{
string str = string.Format("Delete {0} where IDLOAITHUOC = {1}", table, id);
KetNoiData.ExecuteNonQuery(str);
return(true);
}
catch (Exception e)
{
return(false);
throw e;
}
}
public bool InsertCommand(DataRow rw)
{
try
{
KetNoiData.InsertCommand(table,
"IDDVT", SqlDbType.Int, rw["IDDVT"],
"TENDVT", SqlDbType.NVarChar, rw["TENDVT"]
);
return(true);
}
catch (Exception e)
{
return(false);
throw e;
}
}
//Mặc định key nằm ở vi trí đầu
public bool UpdateCommand(DataRow rw)
{
try
{
KetNoiData.UpdateCommand(table,
"IDLOAITHUOC", SqlDbType.Int, rw["IDLOAITHUOC"],
"TENLOAI", SqlDbType.NVarChar, rw["TENLOAI"]
);
return(true);
}
catch (Exception e)
{
return(false);
throw e;
}
}
/// <summary>
/// Loại = 0: Solo
/// Loại = 1: Thuốc
/// </summary>
/// <param name="soid"></param>
/// <param name="loai"></param>
/// <returns></returns>
public DataSet ConnectData(string soid, int loai)
{
DataSet ds = new DataSet();
string sql = string.Empty;
if (loai == 0)
{
sql = "sp_XN_TimKiem_TheoIDTHUOC";
ds = KetNoiData.ExecuteToDataSet(sql, CommandType.StoredProcedure
, "@idThuoc", SqlDbType.Int, Convert.ToInt32(soid)
);
ds.Tables[0].TableName = "g";
ds.Tables[1].TableName = "ct";
//Relationship
DataColumn[] parentColumns = { ds.Tables["g"].Columns["IDHDN"], ds.Tables["g"].Columns["SOLO"] };
DataColumn[] childColumns = { ds.Tables["ct"].Columns["IDHDN"], ds.Tables["ct"].Columns["SOLO"] };
ds.Relations.Add("R_ct", parentColumns, childColumns);
return(ds);
}
else
if (loai == 1)
{
sql = "sp_XN_TimKiem_TheoLo";
ds = KetNoiData.ExecuteToDataSet(sql, CommandType.StoredProcedure
, "@solo", SqlDbType.NVarChar, soid.ToString()
);
ds.Tables[0].TableName = "g";
ds.Tables[1].TableName = "ct";
//Relationship
DataColumn[] parentColumns = { ds.Tables["g"].Columns["IDTHUOC"] };
DataColumn[] childColumns = { ds.Tables["ct"].Columns["IDTHUOC"] };
ds.Relations.Add("R_ct", parentColumns, childColumns);
return(ds);
}
return(null);
}
//Mặc định key nằm ở vi trí đầu
public bool UpdateCommand(DataRow rw)
{
try
{
KetNoiData.UpdateCommand(table,
"IDNCC", SqlDbType.Int, rw["IDNCC"],
"TENNCC", SqlDbType.NVarChar, rw["TENNCC"],
"DIACHINCC", SqlDbType.NVarChar, rw["DIACHINCC"],
"DIENTHOAINCC", SqlDbType.NVarChar, rw["DIENTHOAINCC"],
"SoDKDK", SqlDbType.NVarChar, rw["SoDKDK"],
"FAX", SqlDbType.NVarChar, rw["FAX"],
"GHICHU", SqlDbType.NVarChar, rw["GHICHU"]
);
return(true);
}
catch (Exception e)
{
return(false);
throw e;
}
}
public DataTable ConnectDATA_BaoCaoThuoc_DonGiaThuoc()
{
string str = @"
-- lấy đơn giá
SELECT DISTINCT TENTHUOC,MAX(CT.DONGIABAN) AS DONGIA
FROM HOADONNHAP G
INNER JOIN CTHDNHAP_THUOC CT ON CT.IDHDN = G.IDHDN
INNER JOIN THUOC TH ON TH.IDTHUOC = CT.IDTHUOC
GROUP BY CT.IDTHUOC,TENTHUOC
ORDER BY TENTHUOC
";
try
{
DataTable dt = KetNoiData.ExecuteToDataTable(str, CommandType.Text);
return(dt);
}
catch (Exception e)
{
throw e;
}
}
public DataSet ConnectData(DateTime tu_ngay, DateTime den_ngay, object soid = null)
{
if (soid == null)
{
soid = DBNull.Value;
}
DataSet ds = new DataSet();
string sql = "sp_kho_LayDuLieu_NhapKho_Xml";
ds = KetNoiData.ExecuteToDataSet(sql, CommandType.StoredProcedure
, "@soid", SqlDbType.Int, soid
, "@tungay", SqlDbType.DateTime, tu_ngay
, "@denngay", SqlDbType.DateTime, den_ngay);
ds.Tables[0].TableName = "g";
ds.Tables[1].TableName = "ct";
//Relationship
DataColumn[] parentColumns = { ds.Tables["g"].Columns["IDHDN"] };
DataColumn[] childColumns = { ds.Tables["ct"].Columns["IDHDN"] };
ds.Relations.Add("R_ct", parentColumns, childColumns);
return(ds);
}
//Mặc định key nằm ở vi trí đầu
public bool UpdateCommand(DataRow rw)
{
try
{
KetNoiData.UpdateCommand(table,
"IDKHACHHANG", SqlDbType.Int, rw["IDKHACHHANG"],
"MAKHACHHANG", SqlDbType.NVarChar, rw["MAKHACHHANG"],
"TENKH", SqlDbType.NVarChar, rw["TENKH"],
"NGAYSINHKH", SqlDbType.NVarChar, rw["NGAYSINHKH"],
"DIACHIKH", SqlDbType.NVarChar, rw["DIACHIKH"],
"DIENTHOAIKH", SqlDbType.NVarChar, rw["DIENTHOAIKH"],
"QUAN", SqlDbType.NVarChar, rw["QUAN"],
"TINH", SqlDbType.NVarChar, rw["TINH"],
"GioiTinh", SqlDbType.NVarChar, rw["GioiTinh"]
);
return(true);
}
catch (Exception e)
{
return(false);
throw e;
}
}
//Constructor
public Nhap_Kho()
{
KetNoiData.ConnectData();
}
