protected void Page_Load(object sender, EventArgs e) { if (!IsPostBack) { int personId = 0; if (Request.QueryString["id"] != null && int.TryParse(TamperProofString.QueryStringDecode(Request.QueryString["id"]), out personId)) { Ajancy.Kimia_Ajancy db = new Ajancy.Kimia_Ajancy(Public.ConnectionString); DataLoadOptions dlo = new DataLoadOptions(); dlo.LoadWith<Ajancy.Person>(p => p.DrivingLicenses); dlo.LoadWith<Ajancy.Person>(p => p.DriverCertifications); dlo.LoadWith<Ajancy.DriverCertification>(dc => dc.DriverCertificationCars); dlo.LoadWith<Ajancy.DriverCertificationCar>(dcc => dcc.CarPlateNumber); dlo.LoadWith<Ajancy.CarPlateNumber>(cpn => cpn.PlateNumber); dlo.LoadWith<Ajancy.CarPlateNumber>(cpn => cpn.Car); dlo.LoadWith<Ajancy.Car>(c => c.FuelCards); dlo.LoadWith<Ajancy.Car>(c => c.CarType); db.LoadOptions = dlo; SetPerson(db.Persons.FirstOrDefault<Ajancy.Person>(p => p.PersonID == personId)); db.Dispose(); } else { Response.Redirect("~/Default.aspx"); } } }
protected void fluDocument_UploadedComplete(object sender, AjaxControlToolkit.AsyncFileUploadEventArgs e) { if (this.fluDocument.HasFile) { if (this.fluDocument.PostedFile.ContentType.Equals("image/pjpeg") || this.fluDocument.PostedFile.ContentType.Equals("image/x-png")) { byte[] fileByte = this.fluDocument.FileBytes; Binary binaryObj = new Binary(fileByte); Ajancy.Kimia_Ajancy db = new Ajancy.Kimia_Ajancy(Public.ConnectionString); Ajancy.Doument doc = db.Douments.FirstOrDefault<Ajancy.Doument>(d => d.PersonID == Public.ActiveUserRole.User.PersonID && d.DocumentType == 1); if (doc == null) { doc = new Ajancy.Doument { DocumentType = Public.ToByte(this.drpType.SelectedValue), PersonID = Public.ActiveUserRole.User.PersonID, Picture = binaryObj, SubmitDate = DateTime.Now }; db.Douments.InsertOnSubmit(doc); } else { doc.Picture = binaryObj; } db.SubmitChanges(); db.Dispose(); } } }
protected void Page_Load(object sender, EventArgs e) { if (!IsPostBack) { Ajancy.Kimia_Ajancy db = new Ajancy.Kimia_Ajancy(Public.ConnectionString); this.drpAjancies.DataSource = db.Ajancies.Where(aj => aj.AjancyType == (byte)Public.AjancyType.TaxiAjancy && aj.AjancyName != null).OrderBy(j => j.AjancyName).Select(aj => new { aj.AjancyID, aj.AjancyName }); this.drpAjancies.DataBind(); this.drpAjancies.Items.Insert(0, "- همه آژانس ها -"); this.drpCarType.DataSource = db.CarTypes; this.drpCarType.DataBind(); this.drpCarType.Items.Insert(0, "- همه موارد -"); db.Dispose(); } }
protected void drpProvince_SelectedIndexChanged(object sender, EventArgs e) { if (this.drpProvince.SelectedIndex > 0) { Ajancy.Kimia_Ajancy db = new Ajancy.Kimia_Ajancy(Public.ConnectionString); this.drpCity.DataSource = db.Cities.Where<Ajancy.City>(c => c.ProvinceID == Public.ToByte(this.drpProvince.SelectedValue)).Select(c => new { c.CityID, c.Name }); this.drpCity.DataBind(); db.Dispose(); } else { this.drpCity.Items.Clear(); } this.drpCity.Items.Insert(0, "- انتخاب کنید -"); }
protected void btnSave_Click(object sender, EventArgs e) { if (this.Page.IsValid) { Ajancy.Kimia_Ajancy db = new Ajancy.Kimia_Ajancy(Public.ConnectionString); Ajancy.User user = db.Users.FirstOrDefault<Ajancy.User>(u => u.UserName == HttpContext.Current.User.Identity.Name && u.PassWord == FormsAuthentication.HashPasswordForStoringInConfigFile(this.txtOldPassword.Text, "SHA1")); if (user != null) { user.PassWord = System.Web.Security.FormsAuthentication.HashPasswordForStoringInConfigFile(this.txtNewPassword.Text, "SHA1"); db.SubmitChanges(); db.Dispose(); this.lblMessage.Text = "ویرایش گذرواژه انجام گردید"; } else { this.lblMessage.Text = "گذرواژه نادرست میباشد"; } } this.txtOldPassword.Text = null; this.txtNewPassword.Text = null; this.txtRePassword.Text = null; }
protected void btnExcel_Click(object sender, EventArgs e) { Ajancy.Kimia_Ajancy db = new Ajancy.Kimia_Ajancy(Public.ConnectionString); var query = from j in db.Ajancies join bl in db.BusinessLicenses on j.AjancyID equals bl.AjancyID join jp in db.AjancyPartners on j.AjancyID equals jp.AjancyID join ur in db.UsersInRoles on jp.UserRoleID equals ur.UserRoleID join u in db.Users on ur.UserID equals u.UserID join p in db.Persons on u.PersonID equals p.PersonID join ct in db.Cities on j.CityID equals ct.CityID join pv in db.Provinces on ct.ProvinceID equals pv.ProvinceID where jp.LockOutDate == null && j.AjancyType == Public.ToByte(this.drpAjancyType.SelectedValue) && bl.LockOutDate == null orderby pv.Name, ct.Name, j.AjancyName select new { ct.ProvinceID, ct.CityID, Province = pv.Name, City = ct.Name, j.AjancyName, j.Phone, j.PostalCode, j.Address, bl.BusinessLicenseNo, j.BusinessLicenseType, p.NationalCode, p.FirstName, p.LastName, p.Mobile }; if (this.drpProvince.SelectedIndex > 0 && this.drpCity.SelectedIndex == 0) // Just province { query = from q in query where q.ProvinceID == Public.ToByte(this.drpProvince.SelectedValue) select q; } if (this.drpProvince.SelectedIndex > 0 && this.drpCity.SelectedIndex > 0) // province and city { query = from q in query where q.CityID == Public.ToShort(this.drpCity.SelectedValue) select q; } if (!string.IsNullOrEmpty(this.txtAjancyName.Text)) { query = from q in query where q.AjancyName.Contains(this.txtAjancyName.Text.Trim()) select q; } DataTable dtObj = new DataTable(); dtObj.Columns.Add(new DataColumn("FirstName", typeof(string))); dtObj.Columns.Add(new DataColumn("LastName", typeof(string))); dtObj.Columns.Add(new DataColumn("NationalCode", typeof(string))); dtObj.Columns.Add(new DataColumn("Mobile", typeof(string))); dtObj.Columns.Add(new DataColumn("AjancyName", typeof(string))); dtObj.Columns.Add(new DataColumn("BusinessLicenseNo", typeof(string))); dtObj.Columns.Add(new DataColumn("BusinessLicenseType", typeof(string))); dtObj.Columns.Add(new DataColumn("Address", typeof(string))); dtObj.Columns.Add(new DataColumn("PostalCode", typeof(string))); dtObj.Columns.Add(new DataColumn("Phone", typeof(string))); dtObj.Columns.Add(new DataColumn("Province", typeof(string))); dtObj.Columns.Add(new DataColumn("City", typeof(string))); foreach (var item in query) { DataRow row = dtObj.NewRow(); row[0] = item.FirstName; row[1] = item.LastName; row[2] = item.NationalCode; row[3] = item.Mobile; row[4] = item.AjancyName; row[5] = item.BusinessLicenseNo; row[6] = item.BusinessLicenseType == 0 ? "عادی" : "ویژه ایثارگران و جانبازان و شهدا"; row[7] = item.Address; row[8] = item.PostalCode; row[9] = item.Phone; row[10] = item.Province; row[11] = item.City; dtObj.Rows.Add(row); } db.Dispose(); dtObj.TableName = "dt"; Stimulsoft.Report.StiReport report = new Stimulsoft.Report.StiReport(); report.RegData(dtObj); report.Load(HttpContext.Current.Server.MapPath("~/App_Data/Report/mrt/ajancy_list.mrt")); report.Render(); Public.ExportInfo(3, report); report.Dispose(); }
protected void Page_Load(object sender, EventArgs e) { if (!IsPostBack) { Ajancy.Kimia_Ajancy db = new Ajancy.Kimia_Ajancy(Public.ConnectionString); if (Request.QueryString["mode"] != null) { JavaScriptSerializer serializer = new JavaScriptSerializer(); string treeJSON = null; switch (Request.QueryString["mode"]) { case "0": // Province managers var provinceManagers = from prv in db.Provinces join u in db.Users on prv.ProvinceID equals u.ProvinceID join ur in db.UsersInRoles on u.UserID equals ur.UserID where ur.RoleID == (byte)Public.Role.ProvinceManager select new { key = ur.UserRoleID, title = prv.Name, icon = "city.png", selected = ur.LockOutDate == null }; treeJSON = serializer.Serialize(provinceManagers); break; case "1": // City managers var cityManagers = from prv in db.Provinces select new { key = 0, title = prv.Name, icon = "province.png", //selected = false, children = (from cty in db.Cities join u in db.Users on cty.CityID equals u.CityID join ur in db.UsersInRoles on u.UserID equals ur.UserID where ur.RoleID == (byte)Public.Role.CityManager && cty.ProvinceID == prv.ProvinceID orderby prv.Name, cty.Name select new { key = ur.UserRoleID, title = cty.Name, icon = "city.png", selected = ur.LockOutDate == null }) }; treeJSON = serializer.Serialize(cityManagers.Where(q => q.children.Any())); break; case "2": // Academy province managers var academyProvinceManagers = from prv in db.Provinces join u in db.Users on prv.ProvinceID equals u.ProvinceID join ur in db.UsersInRoles on u.UserID equals ur.UserID where ur.RoleID == (byte)Public.Role.AcademyProvince select new { key = ur.UserRoleID, title = prv.Name, icon = "city.png", selected = ur.LockOutDate == null }; treeJSON = serializer.Serialize(academyProvinceManagers); break; case "3": // City managers var academyCityManagers = from prv in db.Provinces select new { key = 0, title = prv.Name, icon = "province.png", //selected = false, children = (from cty in db.Cities join u in db.Users on cty.CityID equals u.CityID join ur in db.UsersInRoles on u.UserID equals ur.UserID where ur.RoleID == (byte)Public.Role.AcademyCity && cty.ProvinceID == prv.ProvinceID orderby prv.Name, cty.Name select new { key = ur.UserRoleID, title = cty.Name, icon = "city.png", selected = ur.LockOutDate == null }) }; treeJSON = serializer.Serialize(academyCityManagers.Where(q => q.children.Any())); break; } db.Dispose(); Response.Clear(); Response.Write(treeJSON); Response.End(); } else if (Request.HttpMethod == "POST" && Request.Params["keys"] != null && Request.Params["status"] != null) // Save changes { string[] userRoleIds = Request.Params["keys"].Split(','); if (bool.Parse(Request.Params["status"])) { foreach (string userRoleId in userRoleIds) { db.UsersInRoles.First<Ajancy.UsersInRole>( role => role.UserRoleID == int.Parse(userRoleId)).LockOutDate = null; } } else { foreach (string userRoleId in userRoleIds) { db.UsersInRoles.First<Ajancy.UsersInRole>( role => role.UserRoleID == int.Parse(userRoleId)).LockOutDate = DateTime.Now; } } db.SubmitChanges(); db.Dispose(); Response.Clear(); Response.Write('1'); Response.End(); } } }
protected void btnSearch_Click(object sender, EventArgs e) { if (!string.IsNullOrEmpty(this.txtNationalCode.Text)) { db = new Ajancy.Kimia_Ajancy(Public.ConnectionString); var driver = (from p in db.Persons join dc in db.DriverCertifications on p.PersonID equals dc.PersonID join u in db.Users on p.PersonID equals u.PersonID where p.NationalCode == this.txtNationalCode.Text.Trim() && dc.CertificationType == (byte)Public.AjancyType.TaxiAjancy select new { p.FirstName, p.LastName, p.BirthCertificateNo, p.Father, u.ProvinceID, u.CityID, dc.DriverCertificationID }).FirstOrDefault(); if (driver != null) { if (Public.ActiveUserRole.RoleID == (short)Public.Role.ProvinceManager && driver.ProvinceID != Public.ActiveUserRole.User.ProvinceID) // Is ProvinceManager { db.Dispose(); Response.Redirect("~/Message.aspx?mode=27"); // This is a driver of an other province } else if (Public.ActiveUserRole.RoleID == (short)Public.Role.CityManager && driver.CityID != Public.ActiveUserRole.User.CityID) { db.Dispose(); Response.Redirect("~/Message.aspx?mode=26"); // This is a driver of an other city } this.LoadDriverCars(driver.DriverCertificationID, db); this.ViewState["DCID"] = driver.DriverCertificationID; this.lblFirstName.Text = driver.FirstName; this.lblLastName.Text = driver.LastName; this.lblFather.Text = driver.Father; this.lblBirthCertificateNo.Text = driver.BirthCertificateNo; this.btnSave.Enabled = true; if (this.drpCarType.Items.Count == 0) { this.drpCarType.DataSource = db.CarTypes.OrderBy(ct => ct.TypeName); this.drpCarType.DataBind(); this.drpCarType.Items.Insert(0, new System.Web.UI.WebControls.ListItem("- انتخاب کنید -", "0")); } return; } } this.ClearControls(true); this.btnSave.Enabled = false; }
protected void btnExcel_Click(object sender, EventArgs e) { DataTable dtObj = new DataTable(); db = new Ajancy.Kimia_Ajancy(Public.ConnectionString); switch (this.drpStatus.SelectedIndex) { case 0: // All var query = (from p in db.Persons join dc in db.DriverCertifications on p.PersonID equals dc.PersonID join dcc in db.DriverCertificationCars on dc.DriverCertificationID equals dcc.DriverCertificationID join jd in db.AjancyDrivers on dcc.DriverCertificationCarID equals jd.DriverCertificationCarID join cpn in db.CarPlateNumbers on dcc.CarPlateNumberID equals cpn.CarPlateNumberID from pln in db.PlateNumbers.Where(number => number.PlateNumberID == cpn.PlateNumberID).DefaultIfEmpty() from zpn in db.ZonePlateNumbers.Where(number => number.ZonePlateNumberID == cpn.ZonePlateNumberID).DefaultIfEmpty() join c in db.Cars on cpn.CarID equals c.CarID join crt in db.CarTypes on c.CarTypeID equals crt.CarTypeID join fc in db.FuelCards on c.CarID equals fc.CarID join j in db.Ajancies on jd.AjancyID equals j.AjancyID join jp in db.AjancyPartners on j.AjancyID equals jp.AjancyID join ct in db.Cities on j.CityID equals ct.CityID join pv in db.Provinces on ct.ProvinceID equals pv.ProvinceID join owp in db.Persons on cpn.OwnerPersonID equals owp.PersonID where j.AjancyType == Public.ToByte(this.drpAjancyType.SelectedValue) orderby pv.Name, ct.Name select new { OWNationalCode = owp.NationalCode, OWFirstName = owp.FirstName, OWLastName = owp.LastName, p.PersonID, p.FirstName, p.LastName, p.Father, p.NationalCode, p.BirthCertificateNo, p.BirthCertificatePlace, p.BirthDate, p.Marriage, p.BirthPlace, p.Mobile, p.Phone, p.PostalCode, p.Address, p.Gender, p.SubmitDate, j.AjancyID, j.AjancyName, ct.CityID, ct.ProvinceID, Province = pv.Name, City = ct.Name, dc.DriverCertificationNo, c.CarTypeID, c.FuelType, c.Model, c.EngineNo, c.ChassisNo, crt.TypeName, c.VIN, ZCityID = (short?)zpn.CityID, ZNumber = zpn.Number, pln.TwoDigits, pln.ThreeDigits, pln.Alphabet, pln.RegionIdentifier, fc.CardType, fc.PAN }).Distinct(); if (this.drpProvince.SelectedIndex > 0 && this.drpCity.SelectedIndex == 0) // Just province { byte provinceId = Public.ToByte(this.drpProvince.SelectedValue); query = from q in query where q.ProvinceID == provinceId select q; } if (this.drpProvince.SelectedIndex > 0 && this.drpCity.SelectedIndex > 0) // province and city { short cityId = Public.ToShort(this.drpCity.SelectedValue); query = from q in query where q.CityID == cityId select q; } if (this.drpAjancies.SelectedIndex > 0) { int ajancyId = Public.ToInt(this.drpAjancies.SelectedValue); query = from q in query where q.AjancyID == ajancyId select q; } if (this.drpDriverCertification.SelectedIndex == 1) { query = from q in query where q.DriverCertificationNo != null select q; } else if (this.drpDriverCertification.SelectedIndex == 2) { query = from q in query where q.DriverCertificationNo == null select q; } if (this.drpDrivingLicenseType.SelectedIndex > 0) { byte drivingLicense = Public.ToByte(this.drpDrivingLicenseType.SelectedValue); query = from q in query join dl in db.DrivingLicenses on q.PersonID equals dl.PersonID where dl.Type == drivingLicense select q; } if (this.drpMarriage.SelectedIndex > 0) { byte marriage = Public.ToByte(this.drpMarriage.SelectedValue); query = from q in query where q.Marriage == marriage select q; } if (this.drpCarType.SelectedIndex > 0) { short carTypeId = Public.ToShort(this.drpCarType.SelectedValue); query = from q in query join ct in db.CarTypes on q.CarTypeID equals ct.CarTypeID where q.CarTypeID == carTypeId select q; } if (this.drpFuelType.SelectedIndex > 0) { byte fuelType = Public.ToByte(this.drpFuelType.SelectedValue); query = from q in query where q.FuelType == fuelType select q; } if (this.drpFuelCardType.SelectedIndex > 0) { byte fuelCardType = Public.ToByte(this.drpFuelCardType.SelectedValue); query = from q in query where q.CardType == fuelCardType select q; } if (!string.IsNullOrEmpty(this.txtFuelCardPAN.Text)) { query = from q in query where q.PAN.Equals(this.txtFuelCardPAN.Text.Trim()) select q; } if (!string.IsNullOrEmpty(this.txtCarVIN.Text)) { query = from q in query where q.VIN.Equals(this.txtCarVIN.Text.Trim()) select q; } if (this.drpGender.SelectedIndex > 0) { byte gender = Public.ToByte(this.drpGender.SelectedValue); query = from q in query where q.Gender == gender select q; } if (!string.IsNullOrEmpty(this.txtFirstName.Text)) { query = from q in query where q.FirstName.Contains(this.txtFirstName.Text.Trim()) select q; } if (!string.IsNullOrEmpty(this.txtLastName.Text)) { query = from q in query where q.LastName.Contains(this.txtLastName.Text.Trim()) select q; } if (!string.IsNullOrEmpty(this.txtNationalCode.Text)) { query = from q in query where q.NationalCode == this.txtNationalCode.Text.Trim() select q; } if (!string.IsNullOrEmpty(this.txtBirthCertificateNo.Text)) { query = from q in query where q.BirthCertificateNo == this.txtBirthCertificateNo.Text.Trim() select q; } if (!string.IsNullOrEmpty(this.txtCarPlateNumber_1.Text) && !string.IsNullOrEmpty(this.txtCarPlateNumber_2.Text) && !string.IsNullOrEmpty(this.txtCarPlateNumber_3.Text)) { query = from q in query where q.TwoDigits == this.txtCarPlateNumber_1.Text.Trim() && q.ThreeDigits == this.txtCarPlateNumber_2.Text.Trim() && q.RegionIdentifier == this.txtCarPlateNumber_3.Text.Trim() && q.Alphabet == this.drpCarPlateNumber.SelectedValue select q; } if (this.txtDateFrom.HasDate && !this.txtDateTo.HasDate) { query = from q in query where q.SubmitDate == this.txtDateFrom.GeorgianDate.Value select q; } else if (this.txtDateFrom.HasDate && this.txtDateTo.HasDate) { query = from q in query where q.SubmitDate >= this.txtDateFrom.GeorgianDate.Value && q.SubmitDate <= this.txtDateTo.GeorgianDate.Value select q; } db.Connection.Open(); dtObj.Load(db.GetCommand(query).ExecuteReader()); db.Connection.Close(); db.Dispose(); break; case 1: // Actives var query1 = (from p in db.Persons join dc in db.DriverCertifications on p.PersonID equals dc.PersonID join dcc in db.DriverCertificationCars on dc.DriverCertificationID equals dcc.DriverCertificationID join jd in db.AjancyDrivers on dcc.DriverCertificationCarID equals jd.DriverCertificationCarID join cpn in db.CarPlateNumbers on dcc.CarPlateNumberID equals cpn.CarPlateNumberID from pln in db.PlateNumbers.Where(number => number.PlateNumberID == cpn.PlateNumberID).DefaultIfEmpty() from zpn in db.ZonePlateNumbers.Where(number => number.ZonePlateNumberID == cpn.ZonePlateNumberID).DefaultIfEmpty() join c in db.Cars on cpn.CarID equals c.CarID join crt in db.CarTypes on c.CarTypeID equals crt.CarTypeID join fc in db.FuelCards on c.CarID equals fc.CarID join j in db.Ajancies on jd.AjancyID equals j.AjancyID join jp in db.AjancyPartners on j.AjancyID equals jp.AjancyID join ct in db.Cities on j.CityID equals ct.CityID join pv in db.Provinces on ct.ProvinceID equals pv.ProvinceID join owp in db.Persons on cpn.OwnerPersonID equals owp.PersonID where j.AjancyType == Public.ToByte(this.drpAjancyType.SelectedValue) && jd.LockOutDate == null && dcc.LockOutDate == null orderby pv.Name, ct.Name select new { OWNationalCode = owp.NationalCode, OWFirstName = owp.FirstName, OWLastName = owp.LastName, p.PersonID, p.FirstName, p.LastName, p.Father, p.NationalCode, p.BirthCertificateNo, p.BirthCertificatePlace, p.BirthDate, p.Marriage, p.BirthPlace, p.Mobile, p.Phone, p.PostalCode, p.Address, p.Gender, p.SubmitDate, j.AjancyID, j.AjancyName, ct.CityID, ct.ProvinceID, Province = pv.Name, City = ct.Name, dc.DriverCertificationNo, c.CarTypeID, c.FuelType, c.Model, c.EngineNo, c.ChassisNo, crt.TypeName, c.VIN, ZCityID = (short?)zpn.CityID, ZNumber = zpn.Number, pln.TwoDigits, pln.ThreeDigits, pln.Alphabet, pln.RegionIdentifier, fc.CardType, fc.PAN }).Distinct(); if (this.drpProvince.SelectedIndex > 0 && this.drpCity.SelectedIndex == 0) // Just province { byte provinceId = Public.ToByte(this.drpProvince.SelectedValue); query1 = from q in query1 where q.ProvinceID == provinceId select q; } if (this.drpProvince.SelectedIndex > 0 && this.drpCity.SelectedIndex > 0) // province and city { short cityId = Public.ToShort(this.drpCity.SelectedValue); query1 = from q in query1 where q.CityID == cityId select q; } if (this.drpAjancies.SelectedIndex > 0) { int ajancyId = Public.ToInt(this.drpAjancies.SelectedValue); query1 = from q in query1 where q.AjancyID == ajancyId select q; } if (this.drpDriverCertification.SelectedIndex == 1) { query1 = from q in query1 where q.DriverCertificationNo != null select q; } else if (this.drpDriverCertification.SelectedIndex == 2) { query1 = from q in query1 where q.DriverCertificationNo == null select q; } if (this.drpDrivingLicenseType.SelectedIndex > 0) { byte drivingLicense = Public.ToByte(this.drpDrivingLicenseType.SelectedValue); query1 = from q in query1 join dl in db.DrivingLicenses on q.PersonID equals dl.PersonID where dl.Type == drivingLicense select q; } if (this.drpMarriage.SelectedIndex > 0) { byte marriage = Public.ToByte(this.drpMarriage.SelectedValue); query1 = from q in query1 where q.Marriage == marriage select q; } if (this.drpCarType.SelectedIndex > 0) { short carTypeId = Public.ToShort(this.drpCarType.SelectedValue); query1 = from q in query1 join ct in db.CarTypes on q.CarTypeID equals ct.CarTypeID where q.CarTypeID == carTypeId select q; } if (this.drpFuelType.SelectedIndex > 0) { byte fuelType = Public.ToByte(this.drpFuelType.SelectedValue); query1 = from q in query1 where q.FuelType == fuelType select q; } if (this.drpFuelCardType.SelectedIndex > 0) { byte fuelCardType = Public.ToByte(this.drpFuelCardType.SelectedValue); query1 = from q in query1 where q.CardType == fuelCardType select q; } if (!string.IsNullOrEmpty(this.txtFuelCardPAN.Text)) { query1 = from q in query1 where q.PAN.Equals(this.txtFuelCardPAN.Text.Trim()) select q; } if (!string.IsNullOrEmpty(this.txtCarVIN.Text)) { query1 = from q in query1 where q.VIN.Equals(this.txtCarVIN.Text.Trim()) select q; } if (this.drpGender.SelectedIndex > 0) { byte gender = Public.ToByte(this.drpGender.SelectedValue); query1 = from q in query1 where q.Gender == gender select q; } if (!string.IsNullOrEmpty(this.txtFirstName.Text)) { query1 = from q in query1 where q.FirstName.Contains(this.txtFirstName.Text.Trim()) select q; } if (!string.IsNullOrEmpty(this.txtLastName.Text)) { query1 = from q in query1 where q.LastName.Contains(this.txtLastName.Text.Trim()) select q; } if (!string.IsNullOrEmpty(this.txtNationalCode.Text)) { query1 = from q in query1 where q.NationalCode == this.txtNationalCode.Text.Trim() select q; } if (!string.IsNullOrEmpty(this.txtBirthCertificateNo.Text)) { query1 = from q in query1 where q.BirthCertificateNo == this.txtBirthCertificateNo.Text.Trim() select q; } if (!string.IsNullOrEmpty(this.txtCarPlateNumber_1.Text) && !string.IsNullOrEmpty(this.txtCarPlateNumber_2.Text) && !string.IsNullOrEmpty(this.txtCarPlateNumber_3.Text)) { query1 = from q in query1 where q.TwoDigits == this.txtCarPlateNumber_1.Text.Trim() && q.ThreeDigits == this.txtCarPlateNumber_2.Text.Trim() && q.RegionIdentifier == this.txtCarPlateNumber_3.Text.Trim() && q.Alphabet == this.drpCarPlateNumber.SelectedValue select q; } if (this.txtDateFrom.HasDate && !this.txtDateTo.HasDate) { query1 = from q in query1 where q.SubmitDate == this.txtDateFrom.GeorgianDate.Value select q; } else if (this.txtDateFrom.HasDate && this.txtDateTo.HasDate) { query1 = from q in query1 where q.SubmitDate >= this.txtDateFrom.GeorgianDate.Value && q.SubmitDate <= this.txtDateTo.GeorgianDate.Value select q; } db.Connection.Open(); dtObj.Load(db.GetCommand(query1).ExecuteReader()); db.Connection.Close(); db.Dispose(); break; case 2: // Inactives var query2 = (from p in db.Persons join dc in db.DriverCertifications on p.PersonID equals dc.PersonID join dcc in db.DriverCertificationCars on dc.DriverCertificationID equals dcc.DriverCertificationID join jd in db.AjancyDrivers on dcc.DriverCertificationCarID equals jd.DriverCertificationCarID join cpn in db.CarPlateNumbers on dcc.CarPlateNumberID equals cpn.CarPlateNumberID from pln in db.PlateNumbers.Where(number => number.PlateNumberID == cpn.PlateNumberID).DefaultIfEmpty() from zpn in db.ZonePlateNumbers.Where(number => number.ZonePlateNumberID == cpn.ZonePlateNumberID).DefaultIfEmpty() join c in db.Cars on cpn.CarID equals c.CarID join crt in db.CarTypes on c.CarTypeID equals crt.CarTypeID join fc in db.FuelCards on c.CarID equals fc.CarID join j in db.Ajancies on jd.AjancyID equals j.AjancyID join jp in db.AjancyPartners on j.AjancyID equals jp.AjancyID join ct in db.Cities on j.CityID equals ct.CityID join pv in db.Provinces on ct.ProvinceID equals pv.ProvinceID join owp in db.Persons on cpn.OwnerPersonID equals owp.PersonID where j.AjancyType == Public.ToByte(this.drpAjancyType.SelectedValue) && jd.LockOutDate != null && dcc.LockOutDate != null orderby pv.Name, ct.Name select new { OWNationalCode = owp.NationalCode, OWFirstName = owp.FirstName, OWLastName = owp.LastName, p.PersonID, p.FirstName, p.LastName, p.Father, p.NationalCode, p.BirthCertificateNo, p.BirthCertificatePlace, p.BirthDate, p.Marriage, p.BirthPlace, p.Mobile, p.Phone, p.PostalCode, p.Address, p.Gender, p.SubmitDate, j.AjancyID, j.AjancyName, ct.CityID, ct.ProvinceID, Province = pv.Name, City = ct.Name, dc.DriverCertificationNo, c.CarTypeID, c.FuelType, c.Model, c.EngineNo, c.ChassisNo, crt.TypeName, c.VIN, ZCityID = (short?)zpn.CityID, ZNumber = zpn.Number, pln.TwoDigits, pln.ThreeDigits, pln.Alphabet, pln.RegionIdentifier, fc.CardType, fc.PAN }).Distinct(); if (this.drpProvince.SelectedIndex > 0 && this.drpCity.SelectedIndex == 0) // Just province { byte provinceId = Public.ToByte(this.drpProvince.SelectedValue); query2 = from q in query2 where q.ProvinceID == provinceId select q; } if (this.drpProvince.SelectedIndex > 0 && this.drpCity.SelectedIndex > 0) // province and city { short cityId = Public.ToShort(this.drpCity.SelectedValue); query2 = from q in query2 where q.CityID == cityId select q; } if (this.drpAjancies.SelectedIndex > 0) { int ajancyId = Public.ToInt(this.drpAjancies.SelectedValue); query2 = from q in query2 where q.AjancyID == ajancyId select q; } if (this.drpDriverCertification.SelectedIndex == 1) { query2 = from q in query2 where q.DriverCertificationNo != null select q; } else if (this.drpDriverCertification.SelectedIndex == 2) { query2 = from q in query2 where q.DriverCertificationNo == null select q; } if (this.drpDrivingLicenseType.SelectedIndex > 0) { byte drivingLicense = Public.ToByte(this.drpDrivingLicenseType.SelectedValue); query2 = from q in query2 join dl in db.DrivingLicenses on q.PersonID equals dl.PersonID where dl.Type == drivingLicense select q; } if (this.drpMarriage.SelectedIndex > 0) { byte marriage = Public.ToByte(this.drpMarriage.SelectedValue); query2 = from q in query2 where q.Marriage == marriage select q; } if (this.drpCarType.SelectedIndex > 0) { short carTypeId = Public.ToShort(this.drpCarType.SelectedValue); query2 = from q in query2 join ct in db.CarTypes on q.CarTypeID equals ct.CarTypeID where q.CarTypeID == carTypeId select q; } if (this.drpFuelType.SelectedIndex > 0) { byte fuelType = Public.ToByte(this.drpFuelType.SelectedValue); query2 = from q in query2 where q.FuelType == fuelType select q; } if (this.drpFuelCardType.SelectedIndex > 0) { byte fuelCardType = Public.ToByte(this.drpFuelCardType.SelectedValue); query2 = from q in query2 where q.CardType == fuelCardType select q; } if (!string.IsNullOrEmpty(this.txtFuelCardPAN.Text)) { query2 = from q in query2 where q.PAN.Equals(this.txtFuelCardPAN.Text.Trim()) select q; } if (!string.IsNullOrEmpty(this.txtCarVIN.Text)) { query2 = from q in query2 where q.VIN.Equals(this.txtCarVIN.Text.Trim()) select q; } if (this.drpGender.SelectedIndex > 0) { byte gender = Public.ToByte(this.drpGender.SelectedValue); query2 = from q in query2 where q.Gender == gender select q; } if (!string.IsNullOrEmpty(this.txtFirstName.Text)) { query2 = from q in query2 where q.FirstName.Contains(this.txtFirstName.Text.Trim()) select q; } if (!string.IsNullOrEmpty(this.txtLastName.Text)) { query2 = from q in query2 where q.LastName.Contains(this.txtLastName.Text.Trim()) select q; } if (!string.IsNullOrEmpty(this.txtNationalCode.Text)) { query2 = from q in query2 where q.NationalCode == this.txtNationalCode.Text.Trim() select q; } if (!string.IsNullOrEmpty(this.txtBirthCertificateNo.Text)) { query2 = from q in query2 where q.BirthCertificateNo == this.txtBirthCertificateNo.Text.Trim() select q; } if (!string.IsNullOrEmpty(this.txtCarPlateNumber_1.Text) && !string.IsNullOrEmpty(this.txtCarPlateNumber_2.Text) && !string.IsNullOrEmpty(this.txtCarPlateNumber_3.Text)) { query2 = from q in query2 where q.TwoDigits == this.txtCarPlateNumber_1.Text.Trim() && q.ThreeDigits == this.txtCarPlateNumber_2.Text.Trim() && q.RegionIdentifier == this.txtCarPlateNumber_3.Text.Trim() && q.Alphabet == this.drpCarPlateNumber.SelectedValue select q; } if (this.txtDateFrom.HasDate && !this.txtDateTo.HasDate) { query2 = from q in query2 where q.SubmitDate == this.txtDateFrom.GeorgianDate.Value select q; } else if (this.txtDateFrom.HasDate && this.txtDateTo.HasDate) { query2 = from q in query2 where q.SubmitDate >= this.txtDateFrom.GeorgianDate.Value && q.SubmitDate <= this.txtDateTo.GeorgianDate.Value select q; } db.Connection.Open(); dtObj.Load(db.GetCommand(query2).ExecuteReader()); db.Connection.Close(); db.Dispose(); break; } dtObj.TableName = "dt"; Stimulsoft.Report.StiReport report = new Stimulsoft.Report.StiReport(); report.Load(HttpContext.Current.Server.MapPath("~/App_Data/Report/mrt/drivers.mrt")); report.RegData(dtObj); report.Render(); Public.ExportInfo(3, report); report.Dispose(); }