//Cap nhat
public bool CapNhat()
{
string sql = "update Tb_ChiTietBangChamCong set [1]=N'" + this.N1 + "',[2]=N'" + this.N2 + "',[3]=N'" + this.N3 + "',[4]=N'" + this.N4 + "',[5]=N'" + this.N5 + "',[6]=N'" + this.N6 + "',[7]=N'" + this.N7 + "',[8]=N'" + this.N8 + "',[9]=N'" + this.N9 + "',[10]=N'" + this.N10 + "',[11]=N'" + this.N11 + "',[12]=N'" + this.N12 + "',[13]=N'" + this.N13 + "',[14]=N'" + this.N14 + "',[15]=N'" + this.N15 + "',[16]=N'" + this.N16 + "',[17]=N'" + this.N17 + "',[18]=N'" + this.N18 + "',[19]=N'" + this.N19 + "',[20]=N'" + this.N20 + "',[21]=N'" + this.N21 + "',[22]=N'" + this.N22 + "',[23]=N'" + this.N23 + "',[24]=N'" + this.N24 + "',[25]=N'" + this.N25 + "',[26]=N'" + this.N26 + "',[27]=N'" + this.N27 + "',[28]=N'" + this.N28 + "',[29]=N'" + this.N29 + "',[30]=N'" + this.N30 + "',[31]=N'" + this.N31 + "'" + " where MaBangChamCong='" + this.MaBangChamCong + "'";
ConnectToDB.TruyVan_XuLy(sql);
return(true);
}
public SqlDataAdapter TraCuuKhachHang_DA(string tenKH, string diachi)
{
string sql = "";
if (tenKH.CompareTo("") != 0 && diachi.CompareTo("") != 0)
{
sql = "select * from Tb_KhachHang where TenKH LIKE @tenKH AND DChi LIKE @diachi";
}
else
{
if (tenKH.CompareTo("") != 0)
{
sql = "select * from Tb_KhachHang where TenKH LIKE @tenKH";
}
else
{
sql = "select * from Tb_KhachHang where DChi LIKE @diachi";
}
}
SqlParameter[] thamso = { new SqlParameter("@tenKH", "%" + tenKH + "%"),
new SqlParameter("@diachi", "%" + diachi + "%") };
da = ConnectToDB.Truyvan_TraVe_DataAdapter_co_Thamso(sql, thamso);
return(da);
}
//Update ban cham cong
public bool UpDateChamCong()
{
string sql = "update Tb_ChamCong set SoNgayNghi=" + this.SoNgayNghi + " , SoNgayLamTre=" + this.SoNgayDiLamTre + " where MaNV='" + this.MaNV + "' AND Thang=" + this.Thang + " AND Nam=" + this.Nam;
ConnectToDB.TruyVan_XuLy(sql);
return(true);
}
//Truy van lay ra danh sach nam trong bang cham cong
public DataTable LayRaNam()
{
string sql = "select DISTINCT Nam from Tb_ChamCong";
DataTable dt = ConnectToDB.TruyVan_TraVe_DataTable(sql);
return(dt);
}
//Lay ra bang cham cong ung voi ma cham cong
public static DataTable layBangChamCong(string MaBangChamCong)
{
string sql = "select * from Tb_ChiTietBangChamCong where MaBangChamCong='" + MaBangChamCong + "'";
DataTable tb = ConnectToDB.TruyVan_TraVe_DataTable(sql);
return(tb);
}
public string TimMaMA(string tenMA)
{
string sql = "select MaMonAn from Tb_MonAn where TenMonAn='" + tenMA + "'";
DataTable dt = ConnectToDB.TruyVan_TraVe_DataTable(sql);
return(dt.Rows[0][0].ToString());
}
//Tim MaKH Theo ten KH
public bool TimMaKH(string TenKH)
{
string sql = "select MaKH from Tb_KhachHang where TenKH='" + TenKH + "'";
DataTable dt = ConnectToDB.TruyVan_TraVe_DataTable(sql);
return(true);
}
//Truy van lay ra danh sach thang ung voi nam
public DataTable LayRaThang(string Nam)
{
string sql = "select DISTINCT Thang from Tb_ChamCong where Nam='" + Nam + "'";
DataTable dt = ConnectToDB.TruyVan_TraVe_DataTable(sql);
return(dt);
}
//Truy van lay ra bang mon an
public string GiaMonAn(string MaMA)
{
string sql = "select DonGia from Tb_MonAn where MaMonAn='" + MaMA + "'";
DataTable dt = ConnectToDB.TruyVan_TraVe_DataTable(sql);
return(dt.Rows[0][0].ToString());
}
public static string SinhMaChamCong()
{
string ma = "";
string sql = "SELECT MaChamCong FROM Tb_ChamCong ORDER BY MaChamCong DESC";
DataTable dt = ConnectToDB.TruyVan_TraVe_DataTable(sql);
if (dt.Rows.Count == 0)
{
return("MCC001");
}
string temp = dt.Rows[0][0].ToString();
//Lay ve ID hien tai
//temp = temp.Substring(4,3);
int i = int.Parse(temp.Substring(3, 3));
i += 1;
int so = 3;//So luong sach toi da, vi du: 3 chu so
String so0 = "";
for (int j = 1; j <= so; j++)
{
if (i.ToString().Length == j)
{
for (int k = 0; k < so - j; k++)
{
so0 += "0";
}
break;
}
}
ma = "MCC" + so0 + i.ToString();
return(ma);
}
/// <summary>
/// Fetches the program record in descending order and returns OracleDataAdapter
/// </summary>
/// <returns></returns>
public OracleDataAdapter GetPrograms()
{
OracleConnection conn = ConnectToDB.Connect();
conn.Open();
OracleDataAdapter data = new OracleDataAdapter("select * from ASE.program order by program_id desc", conn);
return(data);
}
//Truy Van Lay ra thanh vien cham cong vao thang nam
public DataTable LayRaChamCongThanhVien(string MaNV, int Thang, int Nam)
{
string sql = "select * from Tb_ChamCong where MaNV=@MaNV AND Thang=@Thang AND Nam=@Nam";
SqlParameter[] thamso = { new SqlParameter("@MaNV", MaNV),
new SqlParameter("@Thang", Thang),
new SqlParameter("@Nam", Nam) };
return(ConnectToDB.Truyvan_TraVe_Table_co_Thamso(sql, thamso));
}
public bool ThemMonAn()
{
//Them vao co so du lieu
string sql = "insert into Tb_MonAn (MaMonAn,TenMonAn,DonGia) values (@MaMA,@TenMA,@Gia)";
SqlParameter[] thamso = { new SqlParameter("@MaMA", this.maMonAn),
new SqlParameter("@TenMA", this.tenMonAn),
new SqlParameter("@Gia", this.DonGia) };
ConnectToDB.Truyvan_Xuly_co_Thamso(sql, thamso);
return(true);
}
//Them vao bang cham cong
public bool ThemChamCong()
{
string sql = "insert into Tb_ChamCong (MaChamCong,MaNV,Thang,Nam,SoNgayNghi,SoNgayLamTre) values (@MaChamCong,@MaNV,@Thang,@Nam,@SoNgayNghi,@SoNgayLamTre)";
SqlParameter[] thamso = { new SqlParameter("@MaChamCong", this.MaChamCong),
new SqlParameter("@MaNV", this.MaNV),
new SqlParameter("@Thang", this.Thang),
new SqlParameter("@Nam", this.Nam),
new SqlParameter("@SoNgayNghi", this.SoNgayNghi),
new SqlParameter("@SoNgayLamTre", this.SoNgayDiLamTre) };
ConnectToDB.Truyvan_Xuly_co_Thamso(sql, thamso);
return(true);
}
public static DynamicModels.RootReport PopulateReport(IConnectToDB _Connect1, DynamicModels.RootReport thisModel, string ReportName, DataTable thisDT)
{
ConnectToDB _Connect = _Connect1.Copy();
_Connect.Schema = "DYNAMIC";
thisModel.ReportName = ReportName;
if (thisDT.Rows.Count > 0)
{
thisModel.ReportFilters = new List <DynamicModels.RootReportFilter>();
// thisModel.ProcedureName = thisDT.Rows[0].Field<string>("PROCEDURE_NAME");
thisModel.Template_ID = thisDT.Rows[0].Field <long>("TEMPLATE_ID");
thisModel.Template = thisDT.Rows[0].Field <string>("TEMPLATE");
thisModel.ReportName = thisDT.Rows[0].Field <string>("REPORT_NAME");
thisModel.ReportFilters = new List <DynamicModels.RootReportFilter>();
for (int i = 0; i < thisDT.Rows.Count; i++)
{
DynamicModels.RootReportFilter thisReport = new DynamicModels.RootReportFilter();
thisReport.FilterName = thisDT.Rows[i].Field <string>("FILTER_NAME");
thisReport.PrettyName = thisDT.Rows[i].Field <string>("PRETTY_NAME");
thisReport.ParamSize = Convert.ToInt32(thisDT.Rows[i].Field <long>("PARAM_SIZE"));
thisReport.SearchParamSize = Convert.ToInt32(thisDT.Rows[i].Field <long>("SEARCH_DB_SIZE"));
thisReport.DBType = GetSqlDbType(thisDT.Rows[i].Field <string>("DB_TYPE"));
thisReport.SearchDBType = GetSqlDbType(thisDT.Rows[i].Field <string>("SEARCH_DB_TYPE"));
thisReport.FilterSelect = thisDT.Rows[i].Field <string>("FILTER_SELECT");
thisReport.FilterType = GetFilterType(thisDT.Rows[i].Field <string>("FILTER_TYPE"));
thisReport.inPickList = thisDT.Rows[i].Field <string>("IN_PICK_LIST") == "T" ? true : false;
thisReport.ParamValue = thisDT.Rows[i].Field <string>("ParamValue");
thisReport.Required = thisDT.Rows[i].Field <string>("REQUIRED") == "T" ? true : false;
thisReport.AlternateTemplate = thisDT.Rows[i].Field <string>("ALTERNATE_TEMPLATE");
thisReport.AlternateReportName = thisDT.Rows[i].Field <string>("ALTERNATE_REPORT_NAME");
thisReport.AlternateSchema = thisDT.Rows[i].Field <string>("ALTERNATE_SCHEMA");
thisReport.AlternateSource = thisDT.Rows[i].Field <string>("ALTERNATE_SOURCE");
thisModel.ReportFilters.Add(thisReport);
}
}
else
{
thisModel.ReportFilters = new List <DynamicModels.RootReportFilter>();
}
return(thisModel);
}
public bool ThemKhachHang()
{
//Them vao co so du lieu
string sql = "insert into Tb_KhachHang (MaKH,TenKH,DChi,SoDT) values (@MaKH,@TenKH,@DChi,@SoDT)";
SqlParameter[] thamso =
{
new SqlParameter("@MaKH", this.maKH),
new SqlParameter("@TenKH", this.tenKH),
new SqlParameter("@DChi", this.Dchi),
new SqlParameter("@SoDT", this.SoDT)
};
ConnectToDB.Truyvan_Xuly_co_Thamso(sql, thamso);
return(true);
}
public bool ThemVaoDonDH()
{
//Them vao co so du lieu
string sql = "insert into Tb_DonDatHang (MaKH,NgayDat,NgayNhan,MaMonAn,SoLuong,DonGia) values (@MaKH,@NgayDat,@NgayNhan,@MaMonAn,@SoLuong,@DonGia)";
SqlParameter[] thamso =
{
new SqlParameter("@MaKH", this.maKH),
new SqlParameter("@NgayDat", this.ngayDat),
new SqlParameter("@NgayNhan", this.ngayNhan),
new SqlParameter("@MaMonAn", this.maMonAn),
new SqlParameter("@SoLuong", this.soLuong),
new SqlParameter("@DonGia", this.donGia)
};
ConnectToDB.Truyvan_Xuly_co_Thamso(sql, thamso);
return(true);
}
public DataTable TraCuuMonAn(String tenMA, String maxcost, String mincost)
{
if (tenMA == "")
{
if (maxcost == "" || int.Parse(maxcost) == 0)
{
MessageBox.Show("Nhập lại giá muốn tìm");
return(null);
}
else
{
string sql = "select TenMonAn,DonGia from Tb_MonAn where DonGia between '" + mincost + "' and '" + maxcost + "'";
DataTable dt = ConnectToDB.TruyVan_TraVe_DataTable(sql);
return(dt);
}
}
else
{
if (maxcost == "" || int.Parse(maxcost) == 0)
{
if (mincost == "" || int.Parse(mincost) == 0)
{
string sql = "";
sql = "select TenMonAn,DonGia from Tb_MonAn where TenMonAn='" + tenMA + "'";
DataTable dt = ConnectToDB.TruyVan_TraVe_DataTable(sql);
//MessageBox.Show(dt.Rows[0][0].ToString());
return(dt);
}
else
{
MessageBox.Show("Nhập lại giá muốn tìm kiếm");
return(null);
}
}
else
{
string sql = "";
sql = "select * from (select TenMonAn,DonGia from Tb_MonAn where TenMonAn='" + tenMA + "') AS tam where tam.DonGia between '" + mincost + "' and '" + maxcost + "'";
DataTable dt = ConnectToDB.TruyVan_TraVe_DataTable(sql);
//MessageBox.Show(dt.Rows[0][0].ToString());
return(dt);
}
}
return(null);
}
//Them chi tiet cham cong
public bool ThemChiTietChamCong()
{
//string sql = "insert into Tb_ChiTietBangChamCong (MaBangChamCong,[1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12],[13],[14],[15],[16],[17],[18],[19],[20],[21],[22],[23],[24],[25],[26],[27],[28],[29],[30],[31]) values (@MaBangChamCong,@[1],@[2],@[3],@[4],@[5],@[6],@[7],@[8],@[9],@[10],@[11],@[12],@[13],@[14],@[15],@[16],@[17],@[18],@[19],@[20],@[21],@[22],@[23],@[24],@[25],@[26],@[27],@[28],@[29],@[30],@[31])";
SqlParameter[] thamso = { new SqlParameter("@MaBangChamCong", this.MaBangChamCong),
new SqlParameter("@[1]", this.N1),
new SqlParameter("@[2]", this.N2),
new SqlParameter("@[3]", this.N3),
new SqlParameter("@[4]", this.N4),
new SqlParameter("@[5]", this.N5),
new SqlParameter("@[6]", this.N6),
new SqlParameter("@[7]", this.N7),
new SqlParameter("@[8]", this.N8),
new SqlParameter("@[9]", this.N9),
new SqlParameter("@[10]", this.N10),
new SqlParameter("@[11]", this.N11),
new SqlParameter("@[12]", this.N12),
new SqlParameter("@[13]", this.N13),
new SqlParameter("@[14]", this.N14),
new SqlParameter("@[15]", this.N15),
new SqlParameter("@[16]", this.N16),
new SqlParameter("@[17]", this.N17),
new SqlParameter("@[18]", this.N18),
new SqlParameter("@[19]", this.N19),
new SqlParameter("@[20]", this.N20),
new SqlParameter("@[21]", this.N21),
new SqlParameter("@[22]", this.N22),
new SqlParameter("@[23]", this.N23),
new SqlParameter("@[24]", this.N24),
new SqlParameter("@[25]", this.N25),
new SqlParameter("@[26]", this.N26),
new SqlParameter("@[27]", this.N27),
new SqlParameter("@[28]", this.N28),
new SqlParameter("@[29]", this.N29),
new SqlParameter("@[30]", this.N30),
new SqlParameter("@[31]", this.N31) };
string sql = "insert into Tb_ChiTietBangChamCong (MaBangChamCong,[1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12],[13],[14],[15],[16],[17],[18],[19],[20],[21],[22],[23],[24],[25],[26],[27],[28],[29],[30],[31]) values ('" + this.MaBangChamCong + "',N'" + this.N1 + "',N'" + this.N2 + "',N'" + this.N3 + "',N'" + this.N4 + "',N'" + this.N5 + "',N'" + this.N6 + "',N'" + this.N7 + "',N'" + this.N8 + "',N'" + this.N9 + "',N'" + this.N10 + "',N'" + this.N11 + "',N'" + this.N12 + "',N'" + this.N13 + "',N'" + this.N14 + "',N'" + this.N15 + "',N'" + this.N16 + "',N'" + this.N17 + "',N'" + this.N18 + "',N'" + this.N19 + "',N'" + this.N20 + "',N'" + this.N21 + "',N'" + this.N22 + "',N'" + this.N23 + "',N'" + this.N24 + "',N'" + this.N25 + "',N'" + this.N26 + "',N'" + this.N27 + "',N'" + this.N28 + "',N'" + this.N29 + "',N'" + this.N30 + "',N'" + this.N31 + "')";
//string sql = "insert into Tb_ChiTietBangChamCong (MaBangChamCong,[1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12],[13],[14],[15],[16],[17],[18],[19],[20],[21],[22],[23],[24],[25],[26],[27],[28],[29],[30],[31]) values ('MCC001','abc','abc','abc','abc','abc','abc','abc','abc','abc','abc','abc','abc','abc','abc','abc','abc','abc','abc','abc','abc','abc','abc','abc','abc','abc','abc','abc','abc','abc','abc','abc')";
//ConnectToDB.Truyvan_Xuly_co_Thamso(sql, thamso);
ConnectToDB.TruyVan_XuLy(sql);
return(true);
}
