public HttpResponseMessage getall()
{
using (QLDX_DB db = new QLDX_DB())
{
var data = db.DX0050.AsEnumerable().Select(p => new
{
p.DX0050_ID,
p.ghiChu,
p.hangTaxi,
p.loaiThe,
p.maThe,
p.ngayHetHan,
p.ngayPhatHanh,
p.soThe,
p.trangThai,
DX0060 = p.DX0060.Select(k => new
{
k.DX0050_ID,
k.DX0060_ID,
k.ghiChu,
k.thoiGian,
DX0061 = k.DX0061.Select(f => new {
f.DX0001_ID,
f.DX0060_ID,
f.DX0061_ID,
f.ghiChu
})
})
});
return(REST.GetHttpResponseMessFromObject(data));
}
}
public HttpResponseMessage getdetail([FromBody] filter filter)
{
string startdate = DateTime.Parse(filter.startdate.ToString()).ToString("yyyy-MM-dd HH:mm:ss");
string enddate = DateTime.Parse(filter.enddate.ToString()).ToString("yyyy-MM-dd HH:mm:ss");
string iptemp = "";
filter.ip.ToList().ForEach(fff =>
{
iptemp += "'" + fff.ip + "'" + ",";
});
using (DB db = new DB())
{
var data = db.Database.SqlQuery <datareturn>(@"select User_ID ,Verify_Date,
CASE
WHEN CONVERT(varchar, Verify_Date,108)>='05:00:00' and CONVERT(varchar, Verify_Date,108)<='09:30:00' THEN N'Bữa sáng'
WHEN CONVERT(varchar, Verify_Date,108)>='10:00:00' and CONVERT(varchar, Verify_Date,108)<='13:00:00' THEN N'Bữa trưa'
WHEN CONVERT(varchar, Verify_Date,108)>='16:30:00' and CONVERT(varchar, Verify_Date,108)<='20:00:00' THEN N'Bữa tối'
ELSE N'Không tính tiền'
END as loaibuaan
from KTX0050 as G where
Verify_Date>='" + startdate + "' and Verify_Date<='" + enddate + "' and ip in (" + iptemp.TrimEnd(',') + ") and User_ID='" + filter.id + "' ");
return(REST.GetHttpResponseMessFromObject(data));
}
}
public HttpResponseMessage getall(string id)
{
using (QLDX_DB db = new QLDX_DB())
{
var data = db.DX0011.Where(p => id == "all" ? true : (p.DX0011_ID == id)).OrderBy(t => t.tenDiemDon).Select(p => new
{
p.DX0011_ID,
p.DX0013_ID,
p.DX0014_ID,
p.maDiemDon,
p.tenDiemDon,
p.ghiChu,
p.trangThai,
p.taxi,
p.gioDon,
p.thuTu,
p.lat,
p.lng,
DX0010D = p.DX0010D.Select(o => new
{
o.DX0010_ID,
o.DX0011_ID,
DX0010 = new { o.DX0010.bienSoXe, o.DX0010.DX0010_ID, o.DX0010.ghiChu, o.DX0010.maTuyenXe, o.DX0010.mobile, o.DX0010.tenLaiXe, o.DX0010.tenTuyenXe, o.DX0010.thuTu, o.DX0010.trangThai }
}),
DX0014 = new { p.DX0014.DX0014_ID, p.DX0014.tenDiaDanh }
});
return(REST.GetHttpResponseMessFromObject(data));
}
}
public HttpResponseMessage GetGroup()
{
DB db = new DB();
var re = from temp in db.MKV9983 select temp;
return(REST.GetHttpResponseMessFromObject(re.ToList()));
}
public HttpResponseMessage kiemtradangkyxebus(string manhansu)
{
using (QLDX_DB db = new QLDX_DB())
{
var check = db.Database.SqlQuery <DX0020>(@"
select * from dx0020 where manhansu='" + manhansu + @"'
").FirstOrDefault();
if (check == null)
{
return(REST.GetHttpResponseMessFromObject(new { value = false, messager = "Bạn chưa đăng ký sử dụng xe bus hằng ngày nên chưa thể yêu cầu sử dụng xe taxi." }));
}
else
{
var check2 = db.Database.SqlQuery <DX0010>(@"
select top(1) * from dx0010 where type=3 and dx0010_ID in (
select dx0010_id from dx0010d where dx0011_id= (select top(1)
dx0011_id from dx0011 where dx0011_ID=(select top(1) dx0011_id from dx0020 where manhansu='" + manhansu + @"')))
").FirstOrDefault();
if (check2 == null)
{
return(REST.GetHttpResponseMessFromObject(new { value = false, messager = "Tuyến xe bus bạn đăng ký đi làm hằng ngày chưa hỗ trợ sử dụng taxi khi làm thêm giờ." }));
}
else
{
return(REST.GetHttpResponseMessFromObject(new { value = true, messager = "" }));
}
}
return(REST.GetHttpResponseMessFromObject(check));
}
}
public HttpResponseMessage CreateMess([FromBody] MKV7001 filter)
{
object fd = MessageManager.CreateMessage(filter);
return(REST.GetHttpResponseMessFromObject(fd));
//return null;
}
public HttpResponseMessage GetAll2(int id)
{
DB db = new DB();
var re = from temp in db.MKV9981 where temp.MKV9980_ID == id select temp;
return(REST.GetHttpResponseMessFromObject(re.ToList()));
}
public HttpResponseMessage getalldiemdonduocditaxi(string DX0070_id)
{
using (QLDX_DB db = new QLDX_DB())
{
// var data = db.Database.SqlQuery<DX0011>("select * from dx0011 as G where DX0013_ID in (select dx0013_id from dx0013) order by DX0013_ID");
var data = db.DX0011.AsEnumerable().Where(p => db.DX0013.Select(f => f.DX0013_ID).Contains(p.DX0013_ID)).Select(p => new
{
p.DX0011_ID,
p.DX0013_ID,
p.DX0014_ID,
p.maDiemDon,
p.tenDiemDon,
p.ghiChu,
p.gioDon,
p.trangThai,
p.taxi,
p.thuTu,
p.lat,
p.lng,
DX0013 = db.DX0013.AsEnumerable().Where(f => f.trangThai == true).OrderBy(f => f.DX0013_ID).Select(f => new
{
f.DX0011_ID,
f.DX0013_ID,
f.ghiChu,
f.lat,
f.lng,
f.maDiemTaxi,
f.tenDiemTaxi,
f.trangThai,
count = p.DX0013_ID == f.DX0013_ID? db.Database.SqlQuery <DX0001>("select * from dx0001 as G where maForm='SF015' and T007C='" + DX0070_id + "' and T002C in (select manhansu from dx0020 where DX0011_ID='" + p.DX0011_ID + "') and ngayTao>=CONVERT(date,getdate(),23)") : null
})
});;
return(REST.GetHttpResponseMessFromObject(data));
}
}
public HttpResponseMessage Getall([FromBody] MessageManager.filter filter)
{
object fd = MessageManager.GetAll(filter);
return(REST.GetHttpResponseMessFromObject(fd));
//return null;
}
public HttpResponseMessage getall()
{
using (QLDX_DB db = new QLDX_DB())
{
var data = db.DX0012.AsEnumerable().Select(p => new
{
p.DX0012_ID,
p.ghiChu,
p.bienSoXe,
p.DX0010_ID,
p.mobile,
p.soLuongGhe,
p.tenLaiXe,
p.trangThai,
DX0010 = p.DX0010 == null?null: new { p.DX0010.bienSoXe,
p.DX0010.DX0010_ID,
p.DX0010.ghiChu,
p.DX0010.maTuyenXe,
p.DX0010.mobile,
p.DX0010.tenLaiXe,
p.DX0010.tenTuyenXe,
p.DX0010.thuTu,
p.DX0010.trangThai,
p.DX0010.type }
});
return(REST.GetHttpResponseMessFromObject(data));
}
}
public HttpResponseMessage Getdondangky(string id)
{
using (QLDX_DB db = new QLDX_DB())
{
var data = db.Database.SqlQuery <DX0001>(@"select * from DX0001 where DX0001_ID='" + id + "'").FirstOrDefault();
return(REST.GetHttpResponseMessFromObject(data));
}
}
public HttpResponseMessage gettuyenbus(string id)
{
using (QLDX_DB db = new QLDX_DB())
{
var check = db.Database.SqlQuery <DX0010>("select top(1) * from dx0010 where type=3 and dx0010_ID in (select dx0010_id from dx0010d where dx0011_id= (select top(1) dx0011_id from dx0011 where dx0011_ID=(select top(1) dx0011_id from dx0020 where manhansu='" + id + "')))").FirstOrDefault();
return(REST.GetHttpResponseMessFromObject(check));
}
}
public HttpResponseMessage getallLocation(string DX0014_ID)
{
using (QLDX_DB db = new QLDX_DB())
{
var data = db.DX0011.Where(x => x.DX0014_ID == DX0014_ID).Select(g => new { g.DX0011_ID, g.DX0013_ID, g.DX0014_ID, g.ghiChu, g.lat, g.lng, g.maDiemDon, g.taxi, g.tenDiemDon, g.thuTu, g.trangThai });
return(REST.GetHttpResponseMessFromObject(data));
}
}
public HttpResponseMessage Getdondangky()
{
using (QLDX_DB db = new QLDX_DB())
{
var data = db.Database.SqlQuery <DX0001>(@"select * from DX0001 where maForm='SF014'");
return(REST.GetHttpResponseMessFromObject(data));
}
}
public HttpResponseMessage GetSQL([FromBody] getcmd cmd)
{
using (DB db = new DB())
{
var data = db.Database.SqlQuery <MKV9999>(cmd.cmd);
return(REST.GetHttpResponseMessFromObject(data));
}
}
public HttpResponseMessage getall([FromBody] filter filter)
{
using (QLDX_DB db = new QLDX_DB())
{
var data = db.DX0060.AsEnumerable().Select(p => getDX00600(p));
return(REST.GetHttpResponseMessFromObject(data));
}
}
public HttpResponseMessage Getall()
{
using (DB db = new DB())
{
var data = db.KTX0053;
return(REST.GetHttpResponseMessFromObject(data.ToList()));
}
}
public HttpResponseMessage Getghichu([FromBody] kk values)
{
using (DB db = new DB())
{
var d = db.RM0010.Where(p => p.CMTND_SO == values.cmt || p.EMAIL == values.email || p.MOBILE == values.sdt).Select(p => p.ghichu).FirstOrDefault();
return(REST.GetHttpResponseMessFromObject(d));
}
}
public HttpResponseMessage getdx0011(string id)
{
using (QLDX_DB db = new QLDX_DB())
{
var data = db.Database.SqlQuery <DX0011>(@"select * from DX0011 where DX0013_ID='" + id + "'");
return(REST.GetHttpResponseMessFromObject(data));
}
}
public HttpResponseMessage getall(string id)
{
using (QLDX_DB db = new QLDX_DB())
{
var data = db.Database.SqlQuery <DX0013>(@"select * from DX0013 where trangthai=1" + (id != "all" ? " and DX0013_ID='" + id + "'" : "") + " order by DX0013_ID");
return(REST.GetHttpResponseMessFromObject(data));
}
}
public HttpResponseMessage GetActionofgroup(int mkv9983_id, int mkv9980_id)
{
DB db = new DB();
var re = from temp in db.MKV9981
where temp.MKV9980_ID == mkv9980_id
select new { temp.TENHANHDONG, temp.CAPMENU, temp.MKV9981_ID, check = ((from t in db.MKV9982 where t.MKV9983_ID == mkv9983_id && t.MKV9981_ID == temp.MKV9981_ID select t.MKV9981_ID)) };
return(REST.GetHttpResponseMessFromObject(re.ToList()));
}
public HttpResponseMessage GetMKV9999_ID(string id)
{
using (DB db = new DB())
{
var temp = db.KTX0001.Where(p => p.KTX0001_ID == db.KTX0020.Where(f => f.MKV9999_ID == db.MKV9999.Where(b => b.manhansu == id).Select(b => b.MKV9999_ID).FirstOrDefault()).Select(f => f.KTX0001_ID).FirstOrDefault()).Select(f => new { f.ten }).FirstOrDefault();
return(REST.GetHttpResponseMessFromObject(temp));
}
}
public HttpResponseMessage Getall2(int id)
{
object fd = getallRM0015(new filter()
{
id = id
});
return(REST.GetHttpResponseMessFromObject(fd));
}
public HttpResponseMessage Get(int id)
{
using (DB db = new DB())
{
var temp = db.KTX0001.SingleOrDefault(p => p.KTX0001_ID == id);
return(REST.GetHttpResponseMessFromObject(temp));
}
}
public HttpResponseMessage getall(string id)
{
using (QLDX_DB db = new QLDX_DB())
{
var data = db.DX0010.AsEnumerable().Where(p => id == "all" ? true : (p.DX0010_ID == id)).Select(p => new
{
p.DX0010_ID,
p.ghiChu,
p.maTuyenXe,
p.tenTuyenXe,
p.thuTu,
p.trangThai,
DX0010D = p.DX0010D.Select(o => new
{
o.DX0010_ID,
o.DX0011_ID,
o.thuTu,
DX0011 = new
{
o.DX0011.DX0011_ID,
o.DX0011.DX0013_ID,
o.DX0011.ghiChu,
o.DX0011.lat,
o.DX0011.lng,
o.DX0011.maDiemDon,
o.DX0011.taxi,
o.DX0011.tenDiemDon,
o.DX0011.thuTu,
o.DX0011.trangThai,
DX0020 = (o.DX0011.DX0020.Count == 0 ? null : o.DX0011.DX0020.Select(h => new
{
h.ban_id,
h.capbac,
h.chucvu,
h.cmtnd_so,
h.diachitamtru,
h.diachithuongtru,
h.dienthoai_didong,
h.DX0011_ID,
h.DX0020_ID,
h.email,
h.gioitinh,
h.hodem,
h.manhansu,
h.ngaysinh,
h.phong_id,
h.ten,
h.thetu_id,
h.trangThai
}))
}
}).OrderBy(f => f.thuTu)
});
return(REST.GetHttpResponseMessFromObject(data));
}
}
public HttpResponseMessage Getall()
{
using (DB db = new DB())
{
var temp = db.KTX0040.Select(p => new
{ p.ghichu,
p.image, p.KTX0040_ID, p.noidung, p.thutu, p.tieude, p.trangthai });
return(REST.GetHttpResponseMessFromObject(temp.ToList()));
}
}
public HttpResponseMessage Getdondangkylamthem([FromBody] filter filter)
{
using (QLDX_DB db = new QLDX_DB())
{
var data = db.Database.SqlQuery <DX0001>(@"select * from DX0001 where maForm='SF015' " + (filter.trangThai != null? ("and trangThai=" + filter.trangThai) : "")
+ (filter.ngaytao == null?"": (filter.ngaytao == true ? "and CONVERT(date, ngaytao, 111)<CONVERT(date, getdate(), 111)" : "and CONVERT(date, ngaytao, 111)>=CONVERT(date, getdate(), 111)"))
);
return(REST.GetHttpResponseMessFromObject(data));
}
}
public HttpResponseMessage getall_tuyenduong()
{
using (QLDX_DB db = new QLDX_DB())
{
var data = db.DX0020.Select(p => new
{
p.ban_id,
p.capbac,
p.chucvu,
p.cmtnd_so,
p.diachitamtru,
p.diachithuongtru,
p.dienthoai_didong,
p.DX0010_ID,
p.DX0011_ID,
p.DX0020_ID,
p.email,
p.gioitinh,
p.hodem,
p.manhansu,
p.ngaysinh,
p.phong_id,
p.ten,
p.thetu_id,
p.thoiGianSuDung,
p.trangThai,
DX0010 = new { p.DX0010.bienSoXe,
p.DX0010.DX0010_ID,
p.DX0010.ghiChu,
p.DX0010.maTuyenXe,
p.DX0010.mobile,
p.DX0010.tenLaiXe,
p.DX0010.tenTuyenXe,
p.DX0010.thuTu,
p.DX0010.trangThai,
p.DX0010.type, },
DX0011 = new
{
p.DX0011.DX0011_ID,
p.DX0011.DX0013_ID,
p.DX0011.DX0014_ID,
p.DX0011.ghiChu,
p.DX0011.lat,
p.DX0011.lng,
p.DX0011.maDiemDon,
p.DX0011.taxi,
p.DX0011.tenDiemDon,
p.DX0011.thuTu,
p.DX0011.trangThai
},
});
return(REST.GetHttpResponseMessFromObject(data));
}
}
public HttpResponseMessage Getdondicongtac(string trangThai)
{
using (QLDX_DB db = new QLDX_DB())
{
var sqlcmd = trangThai == "1" ? " select * from DX0001 where maForm = 'SF013' and trangThai = 1 "
: trangThai == "2" ? "select * from DX0001 where maForm='SF013' and trangThai=2 "
: trangThai == "3" ? "select * from DX0001 where maForm='SF013' and trangThai=3 and CONVERT(date,T012C,23)<GETDATE()"
: "select * from DX0001 where maForm='SF013'";
var data = db.Database.SqlQuery <DX0001>(sqlcmd);
return(REST.GetHttpResponseMessFromObject(data));
}
}
public HttpResponseMessage Check([FromBody] acc value)
{
DB db = new DB();
var acc = (from temp in db.MKV9999 where temp.manhansu == value.ID || temp.cmtnd_so == value.ID select temp).Select(p => new
{
p.MKV9999_ID,
p.manhansu,
p.matkhau,
p.id,
p.hodem,
p.ten,
p.type,
p.ngaysinh,
p.gioitinh,
p.noisinh,
p.quequan,
p.diachithuongtru,
p.diachitamtru,
p.cmtnd_so,
p.cmtnd_ngayhethan,
p.cmtnd_noicap,
p.hochieu_so,
p.hochieu_ngaycap,
p.hochieu_ngayhethan,
p.ngayvaocongty,
p.phong_id,
p.ban_id,
p.congdoan_id,
p.chucvu_id,
p.nganhang_stk,
p.nganhang_id,
p.sosobaohiem,
p.honnhantinhtrang,
p.datnuoc_id,
p.phuongxa,
p.suckhoetinhtrang,
p.dienthoai_nharieng,
p.dienthoai_didong,
p.email,
p.tinhtrangnhansu,
p.thutu,
p.chucvu,
p.capbac,
thetu_id = db.MKV9998.Where(o => p.phong_id == o.phong_id).Select(o => o.bophan_ten).FirstOrDefault(),
KTX0001 = db.KTX0001.Where(l => l.KTX0001_ID == db.KTX0020.Where(j => j.MKV9999_ID == p.MKV9999_ID && j.trangthai2 != true).Select(j => j.MKV9999_ID).FirstOrDefault()).Select(o => new
{
o.capbac, o.ghichu, o.idcha, o.khu, o.KTX0001_ID, o.makhoa, o.slot, o.ten, o.thutu, o.trangthai, o.type
})
}).FirstOrDefault();
return(REST.GetHttpResponseMessFromObject(acc));
}