protected static DataTable getBookedByIDAndDates(int jalanID, DateTime tarikhMula, DateTime tarikhTamat)
{
DataTable dt = getTableStructure();
PPJDBDataContext PPJdb = new PPJDBDataContext();
//var queryJalanTempah = from s in PPJdb.JalanTempahs
// where (s.TarikhMula >= tarikhMula && s.TarikhTamat <= tarikhTamat && s.NoRujukanJalan==jalanID)
// select s;
//var queryJalanTempah = (from t1 in PPJdb.JalanTempahs
// where (tarikhMula >= t1.TarikhMula && tarikhTamat <= t1.TarikhTamat && t1.NoRujukanJalan==jalanID)
// select t1);
var queryJalanTempah = PPJdb.JalanTempahs.Where(i => i.TarikhMula >= tarikhMula && i.TarikhTamat <= tarikhTamat && i.NoRujukanJalan == jalanID)
.Select(row => row);
int Counter = 0;
foreach (var c in queryJalanTempah)
{
DataRow dr = dt.NewRow();
dr["No"] = Counter++;
dr["NoRujukanTempah"] = c.NoRujukanTempah;
dr["NoRujukanPermohonan"] = c.NoRujukanPermohonan;
dr["NoRujukanJalan"] = c.NoRujukanJalan;
dr["JumlahTiangTempah"] = c.JumlahTiangTempah;
dr["TarikhMula"] = c.TarikhMula;
dr["TarikhTamat"] = c.TarikhTamat;
dr["HargaSeunit"] = c.HargaSeunit;
dr["Jumlah"] = c.Jumlah;
dt.Rows.Add(dr);
}
return(dt);
}
internal static DataTable getAllRoadInfo()
{
DataTable output = getTableStructture();
PPJDBDataContext PPJdb = new PPJDBDataContext();
var jalanInfo = PPJdb.JalanInfos.Select(row => row).Where(rec => rec.Aktif == true);
int counter = 0;
foreach (var item in jalanInfo)
{
counter++;
DataRow dr = output.NewRow();
dr["No"] = counter;
dr["NoRujukanJalan"] = item.NoRujukanJalan;
dr["NamaJalan"] = item.NamaJalan;
dr["JumlahTiang"] = item.JumlahTiang;
dr["Rosak"] = item.Rosak;
dr["Tersedia"] = item.Tersedia;
dr["JumlahArm"] = item.JumlahArm;
dr["SaizGegantung"] = item.SaizGegantung;
dr["HargaKosSeunit"] = item.HargaKosSeunit;
dr["HargaSeunit"] = item.HargaSeunit;
output.Rows.Add(dr);
}
//output = null;
return(output);
}
protected static DataTable getBookedByIDAndMonth(string selectedRoad, string month, string year)
{
int SelectedMonth = AppsCont.getMonthValue(month.ToUpper());
DataTable dt = getTableStructure();
PPJDBDataContext PPJdb = new PPJDBDataContext();
//var jalanTempah= PPJdb.JalanTempahs.Select(row => row).Where(rec=>rec.NoRujukanJalan==Convert.ToInt32(selectedRoad)
//&& Convert.ToDateTime(rec.TarikhMula).Month==Convert.ToInt32(month) && Convert.ToDateTime(rec.TarikhTamat).Month == Convert.ToInt32(month));
var queryJalanTempah =
from a in PPJdb.GetTable <JalanTempah>()
where Convert.ToInt32(Convert.ToDateTime(a.TarikhMula).Month) == SelectedMonth &&
Convert.ToInt32(Convert.ToDateTime(a.TarikhTamat).Month) == SelectedMonth &&
a.NoRujukanJalan == Convert.ToInt32(selectedRoad)
select a;
int Counter = 0;
foreach (var c in queryJalanTempah)
{
DataRow dr = dt.NewRow();
dr["No"] = Counter++;
dr["NoRujukanTempah"] = c.NoRujukanTempah;
dr["NoRujukanPermohonan"] = c.NoRujukanPermohonan;
dr["NoRujukanJalan"] = c.NoRujukanJalan;
dr["JumlahTiangTempah"] = c.JumlahTiangTempah;
dr["TarikhMula"] = c.TarikhMula;
dr["TarikhTamat"] = c.TarikhTamat;
dr["HargaSeunit"] = c.HargaSeunit;
dr["Jumlah"] = c.Jumlah;
dt.Rows.Add(dr);
}
return(dt);
}
private static string getNoRujukanTempah()
{
string NoRujukan = "";
using (PPJDBDataContext PPJdb = new PPJDBDataContext())
{
//var NoRujukanQuery = (from t in PPJdb.JalanTempahs
// orderby t.TarikhData descending
// select t.NoRujukanPermohonan).First();
//int LastDigit = 0;
//int NewDigit = 0;
//foreach (var item in NoRujukanQuery)
//{
// LastDigit = Convert.ToInt32(item.ToString().Substring(item.ToString().Length - 1, 1));
// NewDigit = LastDigit + 1;
//}
//return NoRujukan = NoRujukan.Substring(0, NoRujukan.Length - 1) + NewDigit.ToString();
var q = (from a in PPJdb.GetTable <JalanTempah>()
orderby a.NoRujukanTempah descending
select a).First();
int LastDigit = 0;
int NewDigit = 0;
q.NoRujukanPermohonan = q.NoRujukanPermohonan.Replace("\r\n4", "");
NoRujukan = q.NoRujukanPermohonan;
string temp = q.NoRujukanPermohonan.Substring(q.NoRujukanPermohonan.Length - 1, 1);
LastDigit = Convert.ToInt32(temp);
NewDigit = LastDigit + 1;
return(NoRujukan = NoRujukan.Substring(0, NoRujukan.Length - 1) + NewDigit.ToString());
}
}
internal static int getAllTiangTersediaByDates(DateTime tarikhMula, DateTime tarikhTamat, string jalanID)
{
int AvailableCounted = 0;
using (PPJDBDataContext PPJdb = new PPJDBDataContext())
{
var queryJalanTempah = PPJdb.JalanTempahs.Where(i => i.TarikhMula >= tarikhMula &&
i.TarikhTamat <= tarikhTamat && i.NoRujukanJalan == Convert.ToInt32(jalanID)).Select(rec => rec.JumlahTiangTempah);
}
return(AvailableCounted);
}
internal static void InactiveJalanByID(int RoadID)
{
using (PPJDBDataContext PPJdb = new PPJDBDataContext())
{
JalanInfo jalanInfo = PPJdb.JalanInfos.SingleOrDefault(rec => rec.NoRujukanJalan == RoadID);
if (jalanInfo != null)
{
jalanInfo.Aktif = false;
PPJdb.SubmitChanges();
}
}
}
internal static void InsertNewJalan(JalanInfomation JalanInfo)
{
using (PPJDBDataContext PPJdb = new PPJDBDataContext())
{
JalanInfo jalanInfo = new JalanInfo();
jalanInfo.Aktif = true;
jalanInfo.HargaKosSeunit = JalanInfo.HargaKosSeunit;
jalanInfo.JumlahTiang = JalanInfo.JumlahTiang;
jalanInfo.HargaSeunit = JalanInfo.HargaSeunit;
jalanInfo.NamaJalan = JalanInfo.NamaJalan;
jalanInfo.JumlahArm = JalanInfo.JumlahArm;
jalanInfo.Rosak = JalanInfo.Rosak;
jalanInfo.Tersedia = JalanInfo.Tersedia;
jalanInfo.SaizGegantung = JalanInfo.SaizGegantung;
PPJdb.JalanInfos.InsertOnSubmit(jalanInfo);
PPJdb.SubmitChanges();
}
}
internal static void AddTempah(DataTable jalan, JalanTempahInfo tempah, out string NoRujukanPermohonan)
{
NoRujukanPermohonan = getNoRujukanTempah();
using (PPJDBDataContext PPJdb = new PPJDBDataContext())
{
JalanTempah tempahInfo = new JalanTempah();
tempahInfo.NoRujukanPermohonan = NoRujukanPermohonan;
tempahInfo.NoRujukanJalan = Convert.ToInt32(jalan.Rows[0]["NoRujukanJalan"].ToString());
tempahInfo.JumlahTiangTempah = tempah.JumlahTiangTempah;
tempahInfo.HargaSeunit = tempah.HargaSeunit;
tempahInfo.Jumlah = tempah.Jumlah;
tempahInfo.TarikhMula = tempah.TarikhMula;
tempahInfo.TarikhTamat = tempah.TarikhTamat;
tempahInfo.TarikhData = DateTime.Now;
PPJdb.JalanTempahs.InsertOnSubmit(tempahInfo);
PPJdb.SubmitChanges();
}
}
internal static void UpdateJalan(JalanInfomation JalanInfo, int RoadID)
{
using (PPJDBDataContext PPJdb = new PPJDBDataContext())
{
JalanInfo jalanInfo = PPJdb.JalanInfos.SingleOrDefault(rec => rec.NoRujukanJalan == RoadID);
if (jalanInfo != null)
{
jalanInfo.HargaKosSeunit = JalanInfo.HargaKosSeunit;
jalanInfo.JumlahTiang = JalanInfo.JumlahTiang;
jalanInfo.HargaSeunit = JalanInfo.HargaSeunit;
jalanInfo.NamaJalan = JalanInfo.NamaJalan;
jalanInfo.JumlahArm = JalanInfo.JumlahArm;
jalanInfo.Rosak = JalanInfo.Rosak;
jalanInfo.Tersedia = JalanInfo.Tersedia;
jalanInfo.SaizGegantung = JalanInfo.SaizGegantung;
PPJdb.SubmitChanges();
}
}
}
internal static DataTable getRoadByID(int RoadID)
{
DataTable output = getTableStructture();
//output.Columns.Add("NoRujukanJalan", typeof(int));
//output.Columns.Add("NamaJalan", typeof(string));
PPJDBDataContext PPJdb = new PPJDBDataContext();
JalanInfo jalanInfo = PPJdb.JalanInfos.SingleOrDefault(rec => rec.NoRujukanJalan == RoadID);
DataRow dr = output.NewRow();
dr["No"] = 1;
dr["NoRujukanJalan"] = jalanInfo.NoRujukanJalan;
dr["NamaJalan"] = jalanInfo.NamaJalan;
dr["JumlahTiang"] = jalanInfo.JumlahTiang;
dr["Rosak"] = jalanInfo.Rosak;
dr["Tersedia"] = jalanInfo.Tersedia;
dr["JumlahArm"] = jalanInfo.JumlahArm;
dr["SaizGegantung"] = jalanInfo.SaizGegantung;
dr["HargaKosSeunit"] = jalanInfo.HargaKosSeunit;
dr["HargaSeunit"] = jalanInfo.HargaSeunit;
output.Rows.Add(dr);
//output = null;
return(output);
}
