/// <summary>
/// AvaibleLicense To Generate Filter By SeasonID
/// SELECT *
/// FROM TBL_SCHEDULE s
/// INNER JOIN TBL_LICENSE l ON s.LICENSE_ID = l.LICENSE_ID
/// LEFT JOIN TBL_LICENSE_GENERATE lg ON s.SCHEDULE_ID = lg.SCHEDULE_ID
/// LEFT JOIN TBL_SEASON se ON s.SEASON_ID = se.SEASON_ID
/// WHERE s.SEASON_ID = 1 AND lg.SCHEDULE_ID IS NULL
/// </summary>
/// <param name="SEASON_ID"></param>
/// <returns></returns>
public IQueryable <CLASS_TBL_MIX> AvaibleLicenseToGenerateBySeasonID(int SEASON_ID)
{
// SELECT *
// FROM TBL_SCHEDULE s
// INNER JOIN TBL_LICENSE l ON s.LICENSE_ID = l.LICENSE_ID
// LEFT JOIN TBL_LICENSE_GENERATE lg ON s.SCHEDULE_ID = lg.SCHEDULE_ID
// LEFT JOIN TBL_SEASON se ON s.SEASON_ID = se.SEASON_ID
// WHERE s.SEASON_ID = 1 AND lg.SCHEDULE_ID IS NULL
string current_date = DateTime.Now.ToString("dd - MM - yyy");
_context = new LicenseDataContext();
var all = from l in _context.TBL_LICENSEs
join s in _context.TBL_SCHEDULEs on l.LICENSE_ID equals s.LICENSE_ID
join lg in _context.TBL_LICENSE_GENERATEs on s.SCHEDULE_ID equals lg.SCHEDULE_ID into groupSchedule
from Schedule in groupSchedule.DefaultIfEmpty()
join se in _context.TBL_SEASONs on s.SEASON_ID equals se.SEASON_ID into groupSeason
from Season in groupSeason.DefaultIfEmpty()
where s.SEASON_ID == SEASON_ID && Schedule.SCHEDULE_ID == null
select new CLASS_TBL_MIX
{
SEASON_ID = s.SEASON_ID,
SCHEDULE_ID = s.SCHEDULE_ID,
SEASON_NAME = Season.SEASON_NAME,
NOTE = Season.NOTE,
DATE_FOR = current_date
// TOTAL_LICENSE = Schedule.TOTAL_LICENSE + "នាក់",
// REMAIN_LICENSE = Schedule.REMAIN_LICENSE +"នាក់"
};
return(all);
}
public IQueryable <TBL_USER> ShowUserActive()
{
_context = new LicenseDataContext();
var result = from u in _context.TBL_USERs where u.IS_ACTIVE == true select u;
return(result);
}
private void btnDelete_Click(object sender, EventArgs e)
{
foreach (DataGridViewRow row in dataGridViewLicense.SelectedRows)
{
LICENSE_NUMBER = row.Cells[1].Value.ToString();
}
// MessageBox.Show(LICENSE_NUMBER);
if (LICENSE_NUMBER == string.Empty || LICENSE_NUMBER == null)
{
}
else
{
var result = MetroMessageBox.Show(this, "Are you sure to delete it?", "Delete", MessageBoxButtons.OKCancel);
if (result == System.Windows.Forms.DialogResult.OK)
{
using (LicenseDataContext _context = new LicenseDataContext())
{
TBL_LICENSE license = (from l in _context.TBL_LICENSEs
where l.ACCOUNT_NO == LICENSE_NUMBER
select l).FirstOrDefault();
license.IS_ACTIVE = false;
_context.SubmitChanges();
}
using (TBL_LICENSE_DATA_ACCESS lda = new TBL_LICENSE_DATA_ACCESS())
{
dataGridViewLicense.DataSource = lda.ShowLicense();
}
GC.Collect();
}
}
GC.Collect();
}
public List <RUN_SHOW_GENERATE_BY_CREATE_ONResult> ShowLicenseByCreateOn(DateTime DATE)
{
_context = new LicenseDataContext();
var result = _context.RUN_SHOW_GENERATE_BY_CREATE_ON(DATE);
return(result.ToList());
}
private void btnDelete_Click(object sender, EventArgs e)
{
USER_ID = 0;
try
{
foreach (DataGridViewRow row in dataGridViewUser.SelectedRows)
{
USER_ID = Convert.ToInt32(row.Cells[0].Value);
}
//MessageBox.Show(SEASON_ID.ToString());
if (USER_ID != 0)
{
using (LicenseDataContext _context = new LicenseDataContext())
{
TBL_USER user = (from u in _context.TBL_USERs
where u.USER_ID == USER_ID
select u).FirstOrDefault();
user.IS_ACTIVE = false;
_context.SubmitChanges();
};
}
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString(), "Error", MessageBoxButtons.OKCancel, MessageBoxIcon.Error);
}
finally
{
using (TBL_USER_DATA_ACCESS uda = new TBL_USER_DATA_ACCESS())
{
dataGridViewUser.DataSource = uda.ShowUserAllIncludeInActive();
}
GC.Collect();
}
}
private void FormUpdateLicense_Load(object sender, EventArgs e)
{
using (LicenseDataContext _context = new LicenseDataContext())
{
TBL_LICENSE license = (from l in _context.TBL_LICENSEs
where l.ACCOUNT_NO == ControlLicense.LICENSE_NUMBER
select l).FirstOrDefault();
if (license != null)
{
LICENSE_ID = license.LICENSE_ID;
txtLicenseNumber.Text = license.ACCOUNT_NO;
txtAccountName.Text = license.ACCOUNT_NAME;
txtPhone.Text = license.PHONE;
txtContactName.Text = license.CONTACT_NAME;
txtPhone.Text = license.PHONE;
txtProvince.Text = license.PROVINCE_NAME;
txtDistrict.Text = license.DISTRICT_NAME;
txtCommune.Text = license.COMMUNE_NAME;
txtVillage.Text = license.VILLAGE_NAME;
txtAddress.Text = license.ADDRESS;
txtEmail.Text = license.EMAIL;
}
else
{
}
};
}
private void btnSave_Click(object sender, EventArgs e)
{
if (!invalid())
{
try
{
using (LicenseDataContext _context = new LicenseDataContext())
{
TBL_LICENSE l = new TBL_LICENSE();
l.ACCOUNT_NAME = txtPhone.Text.Trim();
l.ACCOUNT_NO = txtLicenseNumber.Text.Trim();
l.CONTACT_NAME = txtContactName.Text.Trim();
l.PHONE = txtPhone.Text.Trim();
l.EMAIL = txtEmail.Text.Trim();
l.PROVINCE_NAME = txtProvince.Text.Trim();
l.DISTRICT_NAME = txtDistrict.Text.Trim();
l.COMMUNE_NAME = txtCommune.Text.Trim();
l.VILLAGE_NAME = txtVillage.Text.Trim();
l.ADDRESS = txtAddress.Text.Trim();
l.IS_ACTIVE = true;
l.CREATE_ON = Convert.ToDateTime(DateTime.Now.ToString());
lda.SaveLicense(l);
};
this.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString(), "Error", MessageBoxButtons.OKCancel, MessageBoxIcon.Error);
}
}
else
{
}
}
/// <summary>
/// Return Filter of TBL_LICENSE of Class TBL_LICENSE
/// </summary>
/// <returns></returns>
public List <CLASS_TBL_LICENSE> ShowLicense()
{
_context = new LicenseDataContext();
var all = _context.TBL_LICENSEs.GroupJoin(_context.TBL_LICENSE_TYPEs, l => l.LICENSE_ID, t => (int?)t.LICENSE_ID,
(license, type) => new { license.LICENSE_ID, license.ACCOUNT_NAME, license.ACCOUNT_NO, license.PHONE, license.CONTACT_NAME, license.EMAIL, license.ADDRESS, license.PROVINCE_NAME, license.DISTRICT_NAME, license.COMMUNE_NAME, license.VILLAGE_NAME, license.IS_ACTIVE, COLOR_ID = (int?)type.SingleOrDefault().COLOR_ID })
.Select(x => new { x.LICENSE_ID, x.ACCOUNT_NAME, x.ACCOUNT_NO, x.PHONE, x.CONTACT_NAME, x.EMAIL, x.ADDRESS, x.PROVINCE_NAME, x.DISTRICT_NAME, x.COMMUNE_NAME, x.VILLAGE_NAME, x.IS_ACTIVE, x.COLOR_ID })
.Where(i => i.IS_ACTIVE == true)
.Take(24)
.Select(g => new CLASS_TBL_LICENSE
{
LICENSE_ID = g.LICENSE_ID,
ACCOUNT_NO = g.ACCOUNT_NO,
ACCOUNT_NAME = g.ACCOUNT_NAME,
CONTACT_NAME = g.CONTACT_NAME,
PHONE = g.PHONE,
EMAIL = g.EMAIL,
ADDRESS = g.ADDRESS,
PROVINCE_NAME = g.PROVINCE_NAME,
DISTRICT_NAME = g.DISTRICT_NAME,
COMMUNE_NAME = g.COMMUNE_NAME,
VILLAGE_NAME = g.VILLAGE_NAME,
COLOR_ID = g.COLOR_ID.GetValueOrDefault()
}).ToList();
return(all);
}
private void btnSave_Click(object sender, EventArgs e)
{
if (!Invalid())
{
try
{
using (TBL_USER_DATA_ACCESS uda = new TBL_USER_DATA_ACCESS())
{
using (_contex = new LicenseDataContext())
{
TBL_USER user = (from u in _contex.TBL_USERs
where u.USER_ID == ControlUser.USER_ID
select u).FirstOrDefault();
user.PASSWORD = user_data.Encrypt(txtPwdNew.Text.Trim(), true);
_contex.SubmitChanges();
}
}
this.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString(), "Error", MessageBoxButtons.OKCancel, MessageBoxIcon.Error);
}
}
else
{
}
}
public IQueryable <TBL_USER> CheckUniqueUsername(string user)
{
_context = new LicenseDataContext();
var result = from u in _context.TBL_USERs where u.USERNAME == user select u;
return(result);
}
public IQueryable <TBL_SEASON> CheckSeasonWithoutExisting(string SEASON_NAME)
{
_context = new LicenseDataContext();
var all = from s in _context.TBL_SEASONs where s.SEASON_NAME == SEASON_NAME select s;
return(all);
}
public IQueryable <TBL_USER> ShowUserByID(int USER_ID)
{
_context = new LicenseDataContext();
var result = from u in _context.TBL_USERs where u.USER_ID == USER_ID select u;
return(result);
}
public List <RUN_SHOW_NUMBER_AVAILABLEResult> ShowSeason(int SEASON_ID)
{
_context = new LicenseDataContext();
var result = _context.RUN_SHOW_NUMBER_AVAILABLE(SEASON_ID);
return(result.ToList());
}
public IQueryable <TBL_SEASON> ShowAllSeasonIncludeInactive()
{
_context = new LicenseDataContext();
var result = from s in _context.TBL_SEASONs select s;
return(result);
}
public IQueryable <TBL_SEASON> ShowAllSeason()
{
_context = new LicenseDataContext();
var result = from s in _context.TBL_SEASONs where s.IS_ACTIVE == true select s;
return(result);
}
public List <RUN_SHOW_HISTORY_SURVEYResult> ShowSurvey(int SEASON_ID)
{
_context = new LicenseDataContext();
var result = _context.RUN_SHOW_HISTORY_SURVEY(SEASON_ID);
return(result.ToList());
}
/// <summary>
/// Update License No.
/// </summary>
/// <param name="license"></param>
public void UpdateLicense(TBL_LICENSE license)
{
using (_context = new LicenseDataContext())
{
_context.SubmitChanges();
}
}
public DataTable ShowAllSeasonToComboBox()
{
_context = new LicenseDataContext();
var result = from s in _context.TBL_SEASONs
where s.IS_ACTIVE == true
select new CLASS_TBL_SEASON
{
SEASON_ID = s.SEASON_ID,
SEASON_NAME = s.SEASON_NAME
};
DataTable dt = new DataTable();
dt.Columns.Add("SEASON_ID");
dt.Columns.Add("SEASON_NAME");
DataRow row1 = dt.NewRow();
row1["SEASON_ID"] = 0;
row1["SEASON_NAME"] = Properties.Resources.ALL_SEASON;
dt.Rows.Add(row1);
foreach (var q in result)
{
DataRow row = dt.NewRow();
row["SEASON_ID"] = q.SEASON_ID;
row["SEASON_NAME"] = q.SEASON_NAME;
dt.Rows.Add(row);
}
return(dt);
}
public IQueryable <TBL_LICENSE> CheckLicenseNumber(string LICENSE_NUMBER)
{
_context = new LicenseDataContext();
var result = from l in _context.TBL_LICENSEs where l.ACCOUNT_NO == LICENSE_NUMBER select l;
return(result);
}
public IQueryable <TBL_LICENSE> ShowAllLicense()
{
_context = new LicenseDataContext();
var all = from l in _context.TBL_LICENSEs select l;
return(all);
}
public IQueryable <CLASS_TBL_LICENSE_TYPE> Search(string prompt)
{
_context = new LicenseDataContext();
var all = (from l in _context.TBL_LICENSEs
join lt in _context.TBL_LICENSE_TYPEs on l.LICENSE_ID equals lt.LICENSE_ID
where (l.ADDRESS.Contains(prompt) ||
l.CONTACT_NAME.Contains(prompt) ||
l.ACCOUNT_NO.Contains(prompt) ||
l.ACCOUNT_NAME.Contains(prompt) ||
l.PROVINCE_NAME.Contains(prompt) ||
l.PHONE.Contains(prompt)) && lt.IS_ACTIVE == true
select new CLASS_TBL_LICENSE_TYPE
{
LICENSE_TYPE_ID = lt.LICENSE_TYPE_ID,
LICENSE_ID = l.LICENSE_ID,
ACCOUNT_NO = l.ACCOUNT_NO,
ACCOUNT_NAME = l.ACCOUNT_NAME,
CONTACT_NAME = l.CONTACT_NAME,
PHONE = l.PHONE,
ADDRESS = l.ADDRESS,
COLOR_ID = lt.COLOR_ID
}).Take(24);
return(all);
}
public List <RUN_SHOW_NOT_SPECIAL_LICENSEResult> ShowLicenseTypeNotSpecial()
{
_context = new LicenseDataContext();
var all = _context.RUN_SHOW_NOT_SPECIAL_LICENSE();
return(all.ToList());
}
public void SaveSeason(TBL_SEASON season)
{
using (_context = new LicenseDataContext())
{
_context.TBL_SEASONs.InsertOnSubmit(season);
_context.SubmitChanges();
};
}
public void SaveUser(TBL_USER user)
{
using (_context = new LicenseDataContext())
{
_context.TBL_USERs.InsertOnSubmit(user);
_context.SubmitChanges();
}
}
public void SaveLicense(TBL_LICENSE_TYPE license)
{
using (_context = new LicenseDataContext())
{
_context.TBL_LICENSE_TYPEs.InsertOnSubmit(license);
_context.SubmitChanges();
};
}
public void SaveLicenseGenerate(TBL_LICENSE_GENERATE lg)
{
using (_context = new LicenseDataContext())
{
_context.TBL_LICENSE_GENERATEs.InsertOnSubmit(lg);
_context.SubmitChanges();
};
}
public void SaveSchedule(TBL_SCHEDULE schedule)
{
using (_context = new LicenseDataContext())
{
_context.TBL_SCHEDULEs.InsertOnSubmit(schedule);
_context.SubmitChanges();
}
}
public IQueryable <TBL_LICENSE> ShowLicenseByID(int LICENSE_ID)
{
_context = new LicenseDataContext();
var result = from l in _context.TBL_LICENSEs where l.LICENSE_ID == LICENSE_ID select l;
return(result);
}
/// <summary>
/// Delete License NO.
/// </summary>
/// <param name="license"></param>
public void DeleteLicense(TBL_LICENSE license)
{
using (_context = new LicenseDataContext())
{
_context.TBL_LICENSEs.DeleteOnSubmit(license);
_context.SubmitChanges();
}
}
public IQueryable <CLASS_TBL_COMPANY> ShowCompany()
{
_context = new LicenseDataContext();
var resutl = from c in _context.TBL_COMPANies
where c.COMPANY_ID == 1
select new CLASS_TBL_COMPANY
{
COMPANY_ADDRESS = c.ADDRESS,
COMPANY_NAME = c.COMPANY_NAME
};
return(resutl);
}
