Beispiel #1
0
    protected void btnZoneReport_Click(object sender, EventArgs e)
    {
        Stimulsoft.Report.StiReport report = new Stimulsoft.Report.StiReport();
        report.Load(HttpContext.Current.Server.MapPath(this.drpReportType.SelectedIndex == 1 ? "~/App_Data/Report/mrt/fc_zone-replace.mrt" : "~/App_Data/Report/mrt/fc_zone-dis.mrt"));
        switch (this.drpReportType.SelectedIndex)
        {
            case 0:
                report.RegData(ZoneDiscards());
                break;

            case 1:
                report.RegData(ZoneReplacement());
                break;

            case 2:
                report.RegData(ZoneDiscardeds());
                break;
        }

        report.Render();
        Public.ExportInfo(3, report);
        report.Dispose();
    }
Beispiel #2
0
    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 btnExcel_Click(object sender, EventArgs e)
    {
        if (Page.IsValid)
        {
            var query = from s in db.Schools
                        join slv in db.SchoolLevels on s.SchoolID equals slv.SchoolID
                        join ssl in db.SchoolSubLevels on slv.SchoolLevelID equals ssl.SchoolLevelID
                        join sc in db.SchoolClothes on ssl.SchoolSubLevelID equals sc.SchoolSubLevelID
                        join st in db.StudentClothes on sc.SchoolClotheID equals st.SchoolClotheID
                        join cc in db.CycleClothes on sc.CycleClotheID equals cc.CycleClotheID
                        join stf in db.REP_Stuffs on cc.StuffID equals stf.StuffID
                        join a in db.Areas on s.AreaCode equals a.AreaCode
                        join lv in db.Levels on slv.LevelID equals lv.LevelID
                        where a.ProvinceID == 71 && cc.CycleID == Public.ToInt(this.drpCycle.SelectedValue) &&
                              slv.LockOutDate == null && cc.CycleClotheID == Public.ToInt(this.drpStuffs.SelectedValue)
                        group ssl by new { s.SchoolCode, s.SchoolName, lv.LevelName, a.AreaCode, a.AreaName, st.ClotheCount, stf.StuffName, cc.CycleClotheID } into grp
                        orderby grp.Key.SchoolCode
                        select new
                        {
                            grp.Key.SchoolCode,
                            grp.Key.SchoolName,
                            grp.Key.LevelName,
                            grp.Key.AreaCode,
                            grp.Key.AreaName,
                            grp.Key.StuffName,
                            ClotheCount = grp.Count()
                        };

            if (!string.IsNullOrEmpty(this.txtAreaCode.Text))
            {
                query = from q in query
                        where q.AreaCode == Public.ToInt(this.txtAreaCode.Text)
                        select q;
            }

            if (!string.IsNullOrEmpty(this.txtSchoolCode.Text))
            {
                query = from q in query
                        where q.SchoolCode == Public.ToInt(this.txtSchoolCode.Text)
                        select q;
            }

            var result = from q in query
                         group q by new { q.SchoolCode, q.SchoolName, q.AreaName, q.StuffName } into grp
                         select new
                         {
                             grp.Key.SchoolCode,
                             grp.Key.SchoolName,
                             grp.Key.AreaName,
                             grp.Key.StuffName,
                             ClotheCount = grp.Sum(s => s.ClotheCount)
                         };

            DataTable dtObj = new DataTable();
            dtObj.Columns.Add(new DataColumn("SchoolCode", typeof(int)));
            dtObj.Columns.Add(new DataColumn("SchoolName", typeof(string)));
            dtObj.Columns.Add(new DataColumn("AreaName", typeof(string)));
            dtObj.Columns.Add(new DataColumn("GoodName", typeof(string)));
            dtObj.Columns.Add(new DataColumn("ClotheCount", typeof(short)));
            dtObj.Columns.Add(new DataColumn("LevelName", typeof(string)));

            foreach (var item in query)
            {
                DataRow row = dtObj.NewRow();
                row[0] = item.SchoolCode;
                row[1] = item.SchoolName;
                row[2] = item.AreaName;
                row[3] = item.StuffName;
                row[4] = item.ClotheCount;
                row[5] = item.LevelName;
                dtObj.Rows.Add(row);
            }

            dtObj.TableName = "dt";
            Stimulsoft.Report.StiReport report = new Stimulsoft.Report.StiReport();
            report.RegData(dtObj);
            report.Load(HttpContext.Current.Server.MapPath("~/App_Data/Report/mrt/sch_clothe.mrt"));
            report.Render();
            Public.ExportInfo(3, report);
            report.Dispose();
        }
    }
    protected void btnExcel_Click(object sender, EventArgs e)
    {
        if (Page.IsValid)
        {
            int cycleFoodId = Public.ToInt(this.drpStuffs.SelectedValue);
            var query = from sf in db.SchoolFoods
                        join cl in db.Calendars on sf.CalendarID equals cl.CalendarID
                        join ssl in db.SchoolSubLevels on sf.SchoolSubLevelID equals ssl.SchoolSubLevelID
                        join slv in db.SchoolLevels on ssl.SchoolLevelID equals slv.SchoolLevelID
                        join lv in db.Levels on slv.LevelID equals lv.LevelID
                        join s in db.Schools on slv.SchoolID equals s.SchoolID
                        join sl in db.SubLevels on ssl.SubLevelID equals sl.SubLevelID
                        join cf in db.CycleFoods on sf.CycleFoodID equals cf.CycleFoodID
                        join st in db.REP_Stuffs on cf.StuffID equals st.StuffID
                        join a in db.Areas on s.AreaCode equals a.AreaCode
                        orderby a.AreaCode, s.SchoolCode, sf.CalendarID
                        where a.ProvinceID == int.Parse(System.Web.Configuration.WebConfigurationManager.AppSettings["DefaultProvinceId"]) &&
                                 sf.CycleFoodID == Public.ToInt(this.drpStuffs.SelectedValue) && slv.LockOutDate == null
                        group ssl by new
                        {
                            s.SchoolID,
                            s.SchoolCode,
                            s.SchoolName,
                            lv.LevelName,
                            a.AreaCode,
                            a.AreaName,
                            st.StuffName,
                            cl.SolarYear,
                            cl.SolarMonth,
                            cl.SolarDay
                        } into grp
                        orderby grp.Key.SchoolCode
                        select new
                        {
                            grp.Key.SchoolCode,
                            grp.Key.SchoolName,
                            grp.Key.AreaCode,
                            grp.Key.AreaName,
                            grp.Key.LevelName,
                            grp.Key.StuffName,
                            grp.Key.SolarYear,
                            grp.Key.SolarMonth,
                            RationDate = string.Concat(grp.Key.SolarYear, "/", grp.Key.SolarMonth, "/", grp.Key.SolarDay),
                            Ration = (from sf2 in db.SchoolFoods
                                      join cl2 in db.Calendars on sf2.CalendarID equals cl2.CalendarID
                                      join ssl2 in db.SchoolSubLevels on sf2.SchoolSubLevelID equals ssl2.SchoolSubLevelID
                                      join slv2 in db.SchoolLevels on ssl2.SchoolLevelID equals slv2.SchoolLevelID
                                      where slv2.SchoolID == grp.Key.SchoolID && slv2.LockOutDate == null &&
                                               sf2.CycleFoodID == cycleFoodId && cl2.SolarYear == grp.Key.SolarYear &&
                                               cl2.SolarMonth == grp.Key.SolarMonth && cl2.SolarDay == grp.Key.SolarDay
                                      select new
                                      {
                                          sf2.FoodCount
                                      }).Sum(fc => fc.FoodCount)
                        };

            if (this.drpMonths.SelectedIndex > 0)
            {
                string[] monthVals = this.drpMonths.SelectedValue.Split('|');
                short solarYear = Public.ToShort(monthVals[0]);
                byte solarMonth = Public.ToByte(monthVals[1]);
                query = from q in query
                        where q.SolarYear == solarYear && q.SolarMonth == solarMonth
                        select q;
            }

            if (!string.IsNullOrEmpty(this.txtAreaCode.Text))
            {
                query = from q in query
                        where q.AreaCode == Public.ToInt(this.txtAreaCode.Text)
                        select q;
            }

            if (!string.IsNullOrEmpty(this.txtSchoolCode.Text))
            {
                query = from q in query
                        where q.SchoolCode == Public.ToInt(this.txtSchoolCode.Text)
                        select q;
            }

            DataTable dtObj = new DataTable();
            db.Connection.Open();
            dtObj.Load(db.GetCommand(query).ExecuteReader());
            db.Connection.Close();
            dtObj.TableName = "dt";
            Stimulsoft.Report.StiReport report = new Stimulsoft.Report.StiReport();
            report.RegData(dtObj);
            report.Load(HttpContext.Current.Server.MapPath("~/App_Data/Report/mrt/sch_ration.mrt"));
            report.Render();
            Public.ExportInfo(3, report);
            report.Dispose();
        }
    }
Beispiel #5
0
    protected void btnExcel_Click(object sender, EventArgs e)
    {
        if (Page.IsValid)
        {
            var query = from s in db.Schools
                        join slv in db.SchoolLevels on s.SchoolID equals slv.SchoolID
                        join lv in db.Levels on slv.LevelID equals lv.LevelID
                        join a in db.Areas on s.AreaCode equals a.AreaCode
                        where slv.LockOutDate == null
                        orderby a.AreaCode, s.SchoolCode
                        select
                        new
                        {
                            s.SchoolID,
                            s.SchoolCode,
                            s.SchoolName,
                            s.Gender,
                            s.SchoolKindID,
                            s.EmployeesCount_Changable,
                            s.EmployeesCount_Fixed,
                            lv.LevelID,
                            lv.LevelName,
                            a.AreaName,
                            a.AreaCode,
                            GirlsCount = GetGirlsCount(s),
                            BoysCount = GetBoysCount(s)
                        };

            if (!string.IsNullOrEmpty(this.txtSchoolCode_From.Text) && string.IsNullOrEmpty(this.txtSchoolCode_To.Text))
            {
                query = from q in query
                        where q.SchoolCode == Public.ToInt(this.txtSchoolCode_From.Text)
                        select q;
            }
            else if (!string.IsNullOrEmpty(this.txtSchoolCode_From.Text) && !string.IsNullOrEmpty(this.txtSchoolCode_To.Text))
            {
                query = from q in query
                        where q.SchoolCode >= Public.ToInt(this.txtSchoolCode_From.Text) && q.SchoolCode <= Public.ToInt(this.txtSchoolCode_To.Text)
                        select q;
            }

            if (!string.IsNullOrEmpty(this.txtSchoolName.Text))
            {
                query = from q in query
                        where q.SchoolName.Contains(this.txtSchoolName.Text)
                        select q;
            }

            if (this.drpSchoolLevel.SelectedIndex > 0)
            {
                query = from q in query
                        where q.LevelID == Public.ToShort(this.drpSchoolLevel.SelectedValue)
                        select q;
            }

            if (this.drpSchoolKind.SelectedIndex > 0)
            {
                query = from q in query
                        where q.SchoolKindID == Public.ToShort(this.drpSchoolKind.SelectedValue)
                        select q;
            }

            if (this.drpGender.SelectedIndex < 3)
            {
                query = from q in query
                        where q.Gender == this.drpGender.SelectedIndex
                        select q;
            }

            if (!string.IsNullOrEmpty(this.txtAreaCode.Text))
            {
                query = from q in query
                        where q.AreaCode == Public.ToInt(this.txtAreaCode.Text)
                        select q;
            }

            DataTable dtObj = new DataTable();
            dtObj.Columns.Add(new DataColumn("SchoolCode", typeof(int)));
            dtObj.Columns.Add(new DataColumn("SchoolName", typeof(string)));
            dtObj.Columns.Add(new DataColumn("LevelName", typeof(string)));
            dtObj.Columns.Add(new DataColumn("AreaName", typeof(string)));
            dtObj.Columns.Add(new DataColumn("BoysCount", typeof(short)));
            dtObj.Columns.Add(new DataColumn("GirlsCount", typeof(short)));
            dtObj.Columns.Add(new DataColumn("StudentsCount", typeof(short)));
            dtObj.Columns.Add(new DataColumn("EmployeesCount_Fixed", typeof(byte)));
            dtObj.Columns.Add(new DataColumn("EmployeesCount_Changable", typeof(byte)));
            dtObj.Columns.Add(new DataColumn("Sum", typeof(short)));

            foreach (var item in query)
            {
                DataRow row = dtObj.NewRow();
                row[0] = item.SchoolCode;
                row[1] = item.SchoolName;
                row[2] = item.LevelName;
                row[3] = item.AreaName;
                row[4] = item.BoysCount;
                row[5] = item.GirlsCount;
                row[6] = item.BoysCount + item.GirlsCount;
                row[7] = item.EmployeesCount_Fixed;
                row[8] = item.EmployeesCount_Changable;
                row[9] = item.BoysCount + item.GirlsCount + item.EmployeesCount_Fixed + item.EmployeesCount_Changable;
                dtObj.Rows.Add(row);
            }

            dtObj.TableName = "dt";
            Stimulsoft.Report.StiReport report = new Stimulsoft.Report.StiReport();
            report.RegData(dtObj);
            report.Load(System.Web.HttpContext.Current.Server.MapPath("~/App_Data/Report/mrt/sch.mrt"));
            report.Render();
            Public.ExportInfo(3, report);
            report.Dispose();
        }
    }
Beispiel #6
0
    protected void btnExcel_Click(object sender, EventArgs e)
    {
        db = new Ajancy.Kimia_Ajancy(Public.ConnectionString);
        var query = from ins in db.Insurances
                    join dc in db.DriverCertifications on ins.DriverCertificationID equals dc.DriverCertificationID
                    join dcc in db.DriverCertificationCars on dc.DriverCertificationID equals dcc.DriverCertificationID
                    join cpn in db.CarPlateNumbers on dcc.CarPlateNumberID equals cpn.CarPlateNumberID
                    join pl in db.PlateNumbers on cpn.PlateNumberID equals pl.PlateNumberID
                    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 p in db.Persons on dc.PersonID equals p.PersonID
                    join u in db.Users on p.PersonID equals u.PersonID
                    join ct in db.Cities on u.CityID equals ct.CityID
                    join pv in db.Provinces on ct.ProvinceID equals pv.ProvinceID
                    where dcc.LockOutDate == null && ins.CancelInsurance == Convert.ToBoolean(this.drpCancelInsurance.SelectedIndex)
                    orderby p.LastName
                    select new
                    {
                        p.PersonID,
                        p.FirstName,
                        p.LastName,
                        p.Father,
                        p.NationalCode,
                        p.BirthCertificateNo,
                        p.BirthDate,
                        u.CityID,
                        u.ProvinceID,
                        City = ct.Name,
                        Province = pv.Name,
                        c.FuelType,
                        crt.TypeName,
                        crt.Capacity,
                        c.VIN,
                        cpn.OwnerPersonID,
                        pl.TwoDigits,
                        pl.ThreeDigits,
                        pl.Alphabet,
                        pl.RegionIdentifier,
                        fc.PAN,
                        ins.InsuranceNo,
                        ins.BranchNo,
                        ins.SubmitDate
                    };

        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 (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;
        }

        if (!string.IsNullOrEmpty(this.txtNationalCode.Text))
        {
            query = from q in query
                    where q.NationalCode == this.txtNationalCode.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("Father", typeof(string)));
        dtObj.Columns.Add(new DataColumn("NationalCode", typeof(string)));
        dtObj.Columns.Add(new DataColumn("BirthCertificateNo", typeof(string)));
        dtObj.Columns.Add(new DataColumn("BirthDate", typeof(string)));
        dtObj.Columns.Add(new DataColumn("InsuranceNo", typeof(string)));
        dtObj.Columns.Add(new DataColumn("BranchNo", typeof(string)));
        dtObj.Columns.Add(new DataColumn("VIN", typeof(string)));
        dtObj.Columns.Add(new DataColumn("PAN", typeof(string)));
        dtObj.Columns.Add(new DataColumn("TypeName", typeof(string)));
        dtObj.Columns.Add(new DataColumn("FuelType", typeof(string)));
        dtObj.Columns.Add(new DataColumn("TwoDigits", typeof(string)));
        dtObj.Columns.Add(new DataColumn("Alphabet", typeof(string)));
        dtObj.Columns.Add(new DataColumn("ThreeDigits", typeof(string)));
        dtObj.Columns.Add(new DataColumn("Iran", typeof(string)));
        dtObj.Columns.Add(new DataColumn("RegionIdentifier", typeof(string)));
        dtObj.Columns.Add(new DataColumn("City", typeof(string)));
        dtObj.Columns.Add(new DataColumn("Province", typeof(string)));
        dtObj.Columns.Add(new DataColumn("Ownership", typeof(string)));
        dtObj.Columns.Add(new DataColumn("WorkType", typeof(string)));
        dtObj.Columns.Add(new DataColumn("Scope", typeof(string)));
        dtObj.Columns.Add(new DataColumn("Capacity", typeof(string)));

        foreach (var item in query)
        {
            DataRow row = dtObj.NewRow();
            row[0] = item.FirstName;
            row[1] = item.LastName;
            row[2] = item.Father;
            row[3] = item.NationalCode;
            row[4] = item.BirthCertificateNo;
            row[5] = Public.ToPersianDate(item.BirthDate);
            row[6] = item.InsuranceNo;
            row[7] = item.BranchNo;
            row[8] = item.VIN;
            row[9] = item.PAN;
            row[10] = item.TypeName;
            row[11] = Public.GetFuelTypeName((Public.FuelType)item.FuelType);
            row[12] = item.TwoDigits;
            row[13] = Public.GetAlphabet(item.Alphabet);
            row[14] = item.ThreeDigits;
            row[15] = "ایران";
            row[16] = item.RegionIdentifier;
            row[17] = item.City;
            row[18] = item.Province;
            row[19] = item.OwnerPersonID == item.PersonID ? "مالک" : "بهره بردار";
            row[20] = "آژانس";
            row[21] = "درون شهری";
            row[22] = item.Capacity;
            dtObj.Rows.Add(row);
        }

        dtObj.TableName = "dt";
        Stimulsoft.Report.StiReport report = new Stimulsoft.Report.StiReport();
        report.RegData(dtObj);
        report.Load(System.Web.HttpContext.Current.Server.MapPath("~/App_Data/Report/mrt/Insurance.mrt"));
        report.Render();
        Public.ExportInfo(3, report);
        report.Dispose();
    }
Beispiel #7
0
    protected void btnExcel_Click(object sender, EventArgs e)
    {
        byte ajancyType = Public.ToByte(this.drpAjancyType.SelectedValue);
        db = new Ajancy.Kimia_Ajancy(Public.ConnectionString);

        var drivers = (from p in db.Persons
                       join u in db.Users on p.PersonID equals u.PersonID
                       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 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 == ajancyType && !(from fcs in db.FuelCardSubstitutions
                                                             join fc2 in db.FuelCards on fcs.AjancyTypeFuelCardID equals fc2.FuelCardID
                                                             join c2 in db.Cars on fc2.CarID equals c2.CarID
                                                             join cpn2 in db.CarPlateNumbers on c2.CarID equals cpn2.CarID
                                                             join dcc2 in db.DriverCertificationCars on cpn2.CarPlateNumberID equals dcc2.CarPlateNumberID
                                                             join dc2 in db.DriverCertifications on dcc2.DriverCertificationID equals dc2.DriverCertificationID
                                                             join jd2 in db.AjancyDrivers on dcc2.DriverCertificationCarID equals jd2.DriverCertificationCarID
                                                             join j2 in db.Ajancies on jd2.AjancyID equals j2.AjancyID
                                                             where fcs.PersonalTypeFuelCardID != null && j2.AjancyType == ajancyType &&
                                                                   (c2.ChassisNo == null && c2.EngineNo == null) &&
                                                                   fcs.SubmitDate < new DateTime(2013, 9, 21) &&
                                                                   ((fc2.DiscardDate == null && dcc2.LockOutDate == null && jd2.LockOutDate == null))
                                                             select dc2.PersonID).Contains(p.PersonID)
                       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,
                           u.CityID,
                           u.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);
            drivers = from q in drivers
                      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);
            drivers = from q in drivers
                      where q.CityID == cityId
                      select q;
        }

        if (this.drpAjancies.SelectedIndex > 0)
        {
            int ajancyId = Public.ToInt(this.drpAjancies.SelectedValue);
            drivers = from q in drivers
                      where q.AjancyID == ajancyId
                      select q;
        }

        if (this.drpDriverCertification.SelectedIndex == 1)
        {
            drivers = from q in drivers
                      where q.DriverCertificationNo != null
                      select q;
        }
        else if (this.drpDriverCertification.SelectedIndex == 2)
        {
            drivers = from q in drivers
                      where q.DriverCertificationNo == null
                      select q;
        }

        if (this.drpCarType.SelectedIndex > 0)
        {
            short carTypeId = Public.ToShort(this.drpCarType.SelectedValue);
            drivers = from q in drivers
                      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);
            drivers = from q in drivers
                      where q.FuelType == fuelType
                      select q;
        }

        if (this.drpFuelCardType.SelectedIndex > 0)
        {
            byte fuelCardType = Public.ToByte(this.drpFuelCardType.SelectedValue);
            drivers = from q in drivers
                      where q.CardType == fuelCardType
                      select q;
        }

        if (!string.IsNullOrEmpty(this.txtFuelCardPAN.Text))
        {
            drivers = from q in drivers
                      where q.PAN.Equals(this.txtFuelCardPAN.Text.Trim())
                      select q;
        }

        if (!string.IsNullOrEmpty(this.txtCarVIN.Text))
        {
            drivers = from q in drivers
                      where q.VIN.Equals(this.txtCarVIN.Text.Trim())
                      select q;
        }

        if (this.drpGender.SelectedIndex > 0)
        {
            byte gender = Public.ToByte(this.drpGender.SelectedValue);
            drivers = from q in drivers
                      where q.Gender == gender
                      select q;
        }

        if (!string.IsNullOrEmpty(this.txtFirstName.Text))
        {
            drivers = from q in drivers
                      where q.FirstName.Contains(this.txtFirstName.Text.Trim())
                      select q;
        }

        if (!string.IsNullOrEmpty(this.txtLastName.Text))
        {
            drivers = from q in drivers
                      where q.LastName.Contains(this.txtLastName.Text.Trim())
                      select q;
        }

        if (!string.IsNullOrEmpty(this.txtNationalCode.Text))
        {
            drivers = from q in drivers
                      where q.NationalCode == this.txtNationalCode.Text.Trim()
                      select q;
        }

        if (!string.IsNullOrEmpty(this.txtBirthCertificateNo.Text))
        {
            drivers = from q in drivers
                      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))
        {
            drivers = from q in drivers
                      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)
        {
            drivers = from q in drivers
                      where q.SubmitDate == this.txtDateFrom.GeorgianDate.Value
                      select q;
        }
        else if (this.txtDateFrom.HasDate && this.txtDateTo.HasDate)
        {
            drivers = from q in drivers
                      where q.SubmitDate >= this.txtDateFrom.GeorgianDate.Value && q.SubmitDate <= this.txtDateTo.GeorgianDate.Value
                      select q;
        }

        DataTable dtObj = new DataTable();
        db.Connection.Open();
        dtObj.Load(db.GetCommand(drivers).ExecuteReader());
        DisposeContext();
        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();
    }
Beispiel #8
0
    protected void btnAjancy_Click(object sender, EventArgs e)
    {
        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
                    where jp.LockOutDate == null &&
                               j.AjancyType == Public.ToByte(this.drpAjancyType.SelectedValue) &&
                               bl.LockOutDate == null
                    orderby j.AjancyName
                    select new
                    {
                        Ajancy = j,
                        Utility = j.AjancyUtilities.Select(ju => new { ju.Utility }),
                        BusinessLicense = j.BusinessLicenses[0],
                        ct.ProvinceID,
                        ct.CityID,
                        City = ct.Name,
                        Manager = string.Format("{0} {1}", p.FirstName, p.LastName)
                    };

        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.ToByte(this.drpCity.SelectedValue)
                    select q;
        }

        if (this.drpAjancies.SelectedIndex > 0)
        {
            query = from q in query
                    where q.Ajancy.AjancyID == Public.ToInt(this.drpAjancies.SelectedValue)
                    select q;
        }

        DataTable dtObj = new DataTable();
        dtObj.Columns.Add(new DataColumn("BusinessLicenseType", typeof(string)));
        dtObj.Columns.Add(new DataColumn("AjancyName", typeof(string)));
        dtObj.Columns.Add(new DataColumn("OfficePosition", typeof(string)));
        dtObj.Columns.Add(new DataColumn("OfficeLevel", typeof(string)));
        dtObj.Columns.Add(new DataColumn("OfficeSpace", typeof(string)));
        dtObj.Columns.Add(new DataColumn("BalconySpace", typeof(string)));
        dtObj.Columns.Add(new DataColumn("BalconyHeight", typeof(string)));
        dtObj.Columns.Add(new DataColumn("ParkingSpace", typeof(string)));
        dtObj.Columns.Add(new DataColumn("ParkingState", typeof(string)));
        dtObj.Columns.Add(new DataColumn("BusinessScope", typeof(string)));
        dtObj.Columns.Add(new DataColumn("PoliceStation", typeof(string)));
        dtObj.Columns.Add(new DataColumn("Mayor", 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("RegisteredPelak", typeof(string)));
        dtObj.Columns.Add(new DataColumn("BluePelak", typeof(string)));
        dtObj.Columns.Add(new DataColumn("EstateType", typeof(string)));
        dtObj.Columns.Add(new DataColumn("DocumentType", typeof(string)));
        dtObj.Columns.Add(new DataColumn("PlaceOwner", typeof(string)));
        dtObj.Columns.Add(new DataColumn("WaterBillSerial", typeof(string)));
        dtObj.Columns.Add(new DataColumn("ElectricityBillSerial", typeof(string)));
        dtObj.Columns.Add(new DataColumn("GasBillSerial", typeof(string)));

        dtObj.Columns.Add(new DataColumn("BusinessLicenseNo", typeof(string)));
        dtObj.Columns.Add(new DataColumn("MemberShipCode", typeof(string)));
        dtObj.Columns.Add(new DataColumn("SerialNo", typeof(string)));
        dtObj.Columns.Add(new DataColumn("ISIC", typeof(string)));
        dtObj.Columns.Add(new DataColumn("CategoryCode", typeof(string)));

        dtObj.Columns.Add(new DataColumn("Water", typeof(string)));
        dtObj.Columns.Add(new DataColumn("Electricity", typeof(string)));
        dtObj.Columns.Add(new DataColumn("Gas", typeof(string)));
        dtObj.Columns.Add(new DataColumn("TelePhone", typeof(string)));
        dtObj.Columns.Add(new DataColumn("WC", typeof(string)));
        dtObj.Columns.Add(new DataColumn("Fax", typeof(string)));
        dtObj.Columns.Add(new DataColumn("Balcon", typeof(string)));
        dtObj.Columns.Add(new DataColumn("Manager", typeof(string)));
        dtObj.Columns.Add(new DataColumn("City", typeof(string)));

        foreach (var item in query)
        {
            DataRow row = dtObj.NewRow();
            row[0] = item.Ajancy.BusinessLicenseType == 0 ? "عادی" : "ویژه ایثارگران و جانبازان و شهدا";
            row[1] = item.Ajancy.AjancyName;
            row[2] = OfficePosition(item.Ajancy.OfficePosition.GetValueOrDefault());
            row[3] = !string.IsNullOrEmpty(item.Ajancy.OfficeLevel) ? item.Ajancy.OfficeLevel : "1";
            row[4] = item.Ajancy.OfficeSpace;
            row[5] = !string.IsNullOrEmpty(item.Ajancy.BalconySpace) ? item.Ajancy.BalconySpace : "0";
            row[6] = !string.IsNullOrEmpty(item.Ajancy.BalconyHeight) ? item.Ajancy.BalconyHeight : "0";
            row[7] = item.Ajancy.ParkingSpace;
            row[8] = item.Ajancy.ParkingState.GetValueOrDefault() ? "بلی" : "خیر";
            row[9] = !string.IsNullOrEmpty(item.Ajancy.BusinessScope) ? item.Ajancy.BusinessScope : "...";
            row[10] = !string.IsNullOrEmpty(item.Ajancy.PoliceStation) ? item.Ajancy.PoliceStation : "...";
            row[11] = Mayor(item.Ajancy.Mayor.GetValueOrDefault());
            row[12] = item.Ajancy.Address;
            row[13] = item.Ajancy.PostalCode;
            row[14] = item.Ajancy.Phone;
            row[15] = item.Ajancy.RegisteredPelak;
            row[16] = !string.IsNullOrEmpty(item.Ajancy.BluePelak) ? item.Ajancy.BluePelak : "...";
            row[17] = item.Ajancy.EstateType.GetValueOrDefault() == 0 ? "ملکی" : "استیجاری";
            row[18] = DocumentType(item.Ajancy.DocumentType.GetValueOrDefault());
            row[19] = !string.IsNullOrEmpty(item.Ajancy.PlaceOwner) ? item.Ajancy.PlaceOwner : "...";
            row[20] = item.Ajancy.WaterBillSerial;
            row[21] = item.Ajancy.ElectricityBillSerial;
            row[22] = item.Ajancy.GasBillSerial;

            row[23] = item.BusinessLicense.BusinessLicenseNo;
            row[24] = item.BusinessLicense.MemberShipCode;
            row[25] = item.BusinessLicense.SerialNo;
            row[26] = item.BusinessLicense.ISIC;
            row[27] = item.BusinessLicense.CategoryCode;

            row[28] = "ندارد";
            row[29] = "ندارد";
            row[30] = "ندارد";
            row[31] = "ندارد";
            row[32] = "ندارد";
            row[33] = "ندارد";
            foreach (var util in item.Utility)
            {
                switch (util.Utility)
                {
                    case 0:
                        row[28] = "دارد";
                        break;

                    case 1:
                        row[29] = "دارد";
                        break;

                    case 2:
                        row[30] = "دارد";
                        break;

                    case 3:
                        row[31] = "دارد";
                        break;

                    case 4:
                        row[32] = "دارد";
                        break;

                    case 5:
                        row[33] = "دارد";
                        break;
                }
            }
            if (!string.IsNullOrEmpty(item.Ajancy.BalconySpace) && item.Ajancy.BalconySpace.ToString() != "0")
            {
                row[34] = "بلی";
            }
            else
            {
                row[34] = "خیر";
            }
            row[35] = item.Manager;
            row[36] = item.City;
            dtObj.Rows.Add(row);
        }

        dtObj.TableName = "dt";
        Stimulsoft.Report.StiReport report = new Stimulsoft.Report.StiReport();
        report.Load(HttpContext.Current.Server.MapPath("~/App_Data/Report/mrt/ajancy_all.mrt"));
        report.RegData(dtObj);
        report.Render();
        Public.ExportInfo(3, report);
        report.Dispose();
    }
Beispiel #9
0
    protected void btnDrivers_Click(object sender, EventArgs e)
    {
        db = new Ajancy.Kimia_Ajancy(Public.ConnectionString);
        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
                     join pn in db.PlateNumbers on cpn.PlateNumberID equals pn.PlateNumberID
                     join c in db.Cars on cpn.CarID equals c.CarID
                     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 ur in db.UsersInRoles on jp.UserRoleID equals ur.UserRoleID
                     join ct in db.Cities on j.CityID equals ct.CityID
                     join owp in db.Persons on cpn.OwnerPersonID equals owp.PersonID
                     where j.AjancyType == Public.ToByte(this.drpAjancyType.SelectedValue) &&
                                ur.RoleID == (short)Public.Role.AjancyManager &&
                                jd.LockOutDate == null &&
                                dcc.LockOutDate == null &&
                                fc.DiscardDate == null
                     orderby p.LastName
                     select new
                     {
                         Driver = p,
                         Owner = new { owp.NationalCode, owp.FirstName, owp.LastName, },
                         DrivingLicense = p.DrivingLicenses.Count > 0 ? p.DrivingLicenses[0] : null,
                         j.AjancyName,
                         City = ct.Name,
                         Car = new
                         {
                             Color = c.Color,
                             Model = c.Model,
                             EngineNo = c.EngineNo,
                             ChassisNo = c.ChassisNo,
                             TypeName = c.CarType.TypeName,
                             FuelType = c.FuelType,
                             VIN = c.VIN,
                             TwoDigits = cpn.PlateNumber.TwoDigits,
                             Alphabet = cpn.PlateNumber.Alphabet,
                             ThreeDigits = cpn.PlateNumber.ThreeDigits,
                             Iran = "ایران",
                             RegionIdentifier = cpn.PlateNumber.RegionIdentifier,
                         },
                         ct.ProvinceID,
                         ct.CityID,
                         dc.DriverCertificationNo,
                         FuelCard = new { fc.CardType, fc.PAN },
                         j.AjancyID
                     });

        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.ToByte(this.drpCity.SelectedValue)
                    select q;
        }

        if (this.drpAjancies.SelectedIndex > 0)
        {
            query = from q in query
                    where q.AjancyID == Public.ToInt(this.drpAjancies.SelectedValue)
                    select q;
        }

        DataTable dtObj = new DataTable();
        dtObj.Columns.Add(new DataColumn("OWNationalCode", typeof(string)));
        dtObj.Columns.Add(new DataColumn("OWFirstName", typeof(string)));
        dtObj.Columns.Add(new DataColumn("OWLastName", typeof(string)));
        dtObj.Columns.Add(new DataColumn("NationalCode", typeof(string)));
        dtObj.Columns.Add(new DataColumn("FirstName", typeof(string)));
        dtObj.Columns.Add(new DataColumn("LastName", typeof(string)));
        dtObj.Columns.Add(new DataColumn("Father", typeof(string)));
        dtObj.Columns.Add(new DataColumn("BirthCertificateNo", typeof(string)));
        dtObj.Columns.Add(new DataColumn("BirthCertificateAlfa", typeof(string)));
        dtObj.Columns.Add(new DataColumn("BirthCertificateSerie", typeof(string)));
        dtObj.Columns.Add(new DataColumn("BirthCertificateSerial", typeof(string)));
        dtObj.Columns.Add(new DataColumn("BirthCertificatePlace", typeof(string)));
        dtObj.Columns.Add(new DataColumn("BirthDate", typeof(string)));
        dtObj.Columns.Add(new DataColumn("BirthPlace", typeof(string)));
        dtObj.Columns.Add(new DataColumn("Gender", typeof(string)));
        dtObj.Columns.Add(new DataColumn("Marriage", typeof(string)));
        dtObj.Columns.Add(new DataColumn("FamilyMembersCount", typeof(string)));
        dtObj.Columns.Add(new DataColumn("Education", typeof(string)));
        dtObj.Columns.Add(new DataColumn("Religion", typeof(string)));
        dtObj.Columns.Add(new DataColumn("Subreligion", typeof(string)));
        dtObj.Columns.Add(new DataColumn("JobStatus", typeof(string)));
        dtObj.Columns.Add(new DataColumn("MilitaryService", typeof(string)));
        dtObj.Columns.Add(new DataColumn("Mobile", typeof(string)));
        dtObj.Columns.Add(new DataColumn("Phone", typeof(string)));
        dtObj.Columns.Add(new DataColumn("PostalCode", typeof(string)));
        dtObj.Columns.Add(new DataColumn("Address", typeof(string)));

        dtObj.Columns.Add(new DataColumn("DrivingLicenseNo", typeof(string)));
        dtObj.Columns.Add(new DataColumn("ExportDate", typeof(string)));
        dtObj.Columns.Add(new DataColumn("Type", typeof(string)));
        dtObj.Columns.Add(new DataColumn("ExportPlace", typeof(string)));

        dtObj.Columns.Add(new DataColumn("VIN", typeof(string)));
        dtObj.Columns.Add(new DataColumn("PAN", typeof(string)));
        dtObj.Columns.Add(new DataColumn("CardType", typeof(string)));
        dtObj.Columns.Add(new DataColumn("TypeName", typeof(string)));
        dtObj.Columns.Add(new DataColumn("Model", typeof(string)));
        dtObj.Columns.Add(new DataColumn("Color", typeof(string)));
        dtObj.Columns.Add(new DataColumn("EngineNo", typeof(string)));
        dtObj.Columns.Add(new DataColumn("ChassisNo", typeof(string)));
        dtObj.Columns.Add(new DataColumn("FuelType", typeof(string)));
        dtObj.Columns.Add(new DataColumn("TwoDigits", typeof(string)));
        dtObj.Columns.Add(new DataColumn("Alphabet", typeof(string)));
        dtObj.Columns.Add(new DataColumn("ThreeDigits", typeof(string)));
        dtObj.Columns.Add(new DataColumn("Iran", typeof(string)));
        dtObj.Columns.Add(new DataColumn("RegionIdentifier", typeof(string)));

        dtObj.Columns.Add(new DataColumn("AjancyName", typeof(string)));
        dtObj.Columns.Add(new DataColumn("City", typeof(string)));
        dtObj.Columns.Add(new DataColumn("DriverCertificationNo", typeof(string)));

        foreach (var item in query)
        {
            DataRow row = dtObj.NewRow();
            row[0] = item.Owner.NationalCode;
            row[1] = item.Owner.FirstName;
            row[2] = item.Owner.LastName;
            row[3] = item.Driver.NationalCode;
            row[4] = item.Driver.FirstName;
            row[5] = item.Driver.LastName;
            row[6] = item.Driver.Father;
            row[7] = item.Driver.BirthCertificateNo;
            row[8] = Public.GetAlphabet(item.Driver.BirthCertificateAlfa);
            row[9] = item.Driver.BirthCertificateSerie;
            row[10] = item.Driver.BirthCertificateSerial;
            row[11] = item.Driver.BirthCertificatePlace;
            row[12] = item.Driver.BirthDate;
            row[13] = item.Driver.BirthPlace;
            row[14] = item.Driver.Gender.GetValueOrDefault() == 0 ? "مرد" : "زن";
            row[15] = item.Driver.Marriage.GetValueOrDefault() == 1 ? "متاهل" : "مجرد";
            row[16] = item.Driver.FamilyMembersCount;
            row[17] = Public.GetEducation(item.Driver.Education.GetValueOrDefault());
            row[18] = Public.GetReligion(item.Driver.Religion.GetValueOrDefault());
            row[19] = item.Driver.Subreligion;
            row[20] = Public.GetJobStatus(item.Driver.JobStatus.GetValueOrDefault());
            row[21] = Public.GetMilitaryService(item.Driver.MilitaryService.GetValueOrDefault());
            row[22] = item.Driver.Mobile;
            row[23] = item.Driver.Phone;
            row[24] = item.Driver.PostalCode;
            row[25] = item.Driver.Address;

            if (item.DrivingLicense != null)
            {
                row[26] = item.DrivingLicense.DrivingLicenseNo;
                row[27] = Public.ToPersianDate(item.DrivingLicense.ExportDate);
                row[28] = Public.GetDrivingLicenseType(item.DrivingLicense.Type);
                row[29] = item.DrivingLicense.ExportPlace;
            }
            else
            {
                row[26] = string.Empty;
                row[27] = string.Empty;
                row[28] = string.Empty;
                row[29] = string.Empty;
            }

            row[30] = item.Car.VIN;
            row[31] = item.FuelCard.PAN;
            row[32] = Public.GetFuelCardTypeName((Public.FuelCardType)item.FuelCard.CardType);
            row[33] = item.Car.TypeName;
            row[34] = item.Car.Model;
            row[35] = item.Car.Color;
            row[36] = item.Car.EngineNo;
            row[37] = item.Car.ChassisNo;
            row[38] = Public.GetFuelTypeName((Public.FuelType)item.Car.FuelType);
            row[39] = item.Car.TwoDigits;
            row[40] = Public.GetAlphabet(item.Car.Alphabet);
            row[41] = item.Car.ThreeDigits;
            row[42] = "ایران";
            row[43] = item.Car.RegionIdentifier;
            row[44] = item.AjancyName;
            row[45] = item.City;
            row[46] = item.DriverCertificationNo;
            dtObj.Rows.Add(row);
        }

        dtObj.TableName = "dt";
        Stimulsoft.Report.StiReport report = new Stimulsoft.Report.StiReport();
        report.Load(HttpContext.Current.Server.MapPath("~/App_Data/Report/mrt/drivers_all.mrt"));
        report.RegData(dtObj);
        report.Render();
        Public.ExportInfo(3, report);
        report.Dispose();
    }
Beispiel #10
0
    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();
    }