public static string SeatBooking(int PayanamID, int BusID, int UserID, int SeatNumber)
{
string Response = string.Empty;
using (VallabaiDataContext db = new VallabaiDataContext())
{
try
{
var seat = from s in db.tab_VA_BUS_BOOKINGs
where s.payanam_ID == PayanamID && s.bus_NUMBER == BusID && s.seat_NUMBER == SeatNumber
select s;
if (seat.Count() == 0)
{
tab_VA_BUS_BOOKING booking = new tab_VA_BUS_BOOKING();
booking.payanam_ID = PayanamID;
booking.bus_NUMBER = BusID;
booking.user_ID = UserID;
booking.seat_NUMBER = SeatNumber;
db.tab_VA_BUS_BOOKINGs.InsertOnSubmit(booking);
db.SubmitChanges();
Response = "Seat is Alloted";
}
else
{
Response = "Selected Seat is not available";
}
}
catch (Exception ex)
{
Response = "Some error occured : " + ex.Message;
}
}
return(Response);
}
//protected void btnBookNow_Click (object sender, EventArgs e)
//{
// var UserID = Convert.ToInt32(ddlNames.SelectedValue);
// var BusNumber = Convert.ToInt32(ddlBusNumber.SelectedValue);
// var SeatNumber = Convert.ToInt32(ddlSeatNumber.SelectedValue);
// var PayanamID = Convert.ToInt32(Request.QueryString["pid"]);
// using (VallabaiDataContext db = new VallabaiDataContext())
// {
// var check = from c in db.tab_VA_BUS_BOOKINGs
// where c.payanam_ID == PayanamID && c.user_ID == UserID
// select c;
// if (check.Count() == 0)
// {
// tab_VA_BUS_BOOKING book = new tab_VA_BUS_BOOKING();
// book.payanam_ID = PayanamID;
// book.user_ID = UserID;
// book.bus_NUMBER = BusNumber;
// book.seat_NUMBER = SeatNumber;
// db.tab_VA_BUS_BOOKINGs.InsertOnSubmit(book);
// db.SubmitChanges();
// Page.ClientScript.RegisterStartupScript(this.GetType(), "ShowStatus", "$(function() { funShowMessage('Seat Booked')});", true);
// BindBookedSeats();
// }
// else
// {
// Page.ClientScript.RegisterStartupScript(this.GetType(), "ShowStatus", "$(function() { funShowMessage('Seat is already alloted to this Swamy')});", true);
// }
// }
//}
protected void gvCancel_Click(object sender, EventArgs e)
{
var PayanamID = Convert.ToInt32(Request.QueryString["pid"]);
string CommandArgument = Convert.ToString(((Button)sender).CommandArgument);
if (CommandArgument.Contains(","))
{
var Values = CommandArgument.Split(',');
var SeatNumber = Convert.ToInt32(Values[0]);
var BusNumber = Convert.ToInt32(Values[1]);
using (VallabaiDataContext db = new VallabaiDataContext())
{
var seat = from s in db.tab_VA_BUS_BOOKINGs
where s.payanam_ID == PayanamID && s.seat_NUMBER == SeatNumber && s.bus_NUMBER == BusNumber
select s;
if (seat.Count() > 0)
{
foreach (var item in seat)
{
db.tab_VA_BUS_BOOKINGs.DeleteOnSubmit(item);
db.SubmitChanges();
}
Page.ClientScript.RegisterStartupScript(this.GetType(), "ShowStatus", "$(function() { funShowMessage('Cancelled selected seat')});", true);
BindBookedSeats();
//LoadSeatNumbers();
ClearFields();
}
}
}
}
//private void LoadSeatNumbers ()
//{
// var BusNumber = Convert.ToInt32(ddlBusNumber.SelectedValue);
// var PayanamID = Convert.ToInt32(Request.QueryString["pid"]);
// //ddlSeatNumber.Items.Clear();
// using (VallabaiDataContext db = new VallabaiDataContext())
// {
// var seat = from pd in db.tab_VA_PAYANAM_BUS_DETAILs
// where pd.bus_ID == BusNumber
// select new { pd.bus_TOTAL_SEATS };
// var TotalSeats = 0;
// if (seat.Count() > 0)
// {
// foreach (var item in seat)
// {
// TotalSeats = item.bus_TOTAL_SEATS;
// break;
// }
// }
// if (TotalSeats> 0)
// {
// for (int i = 1; i <= TotalSeats; i++)
// {
// if (!CheckSeatAvaialbility(i, BusNumber, PayanamID))
// {
// ddlSeatNumber.Items.Add("Seat - " + i.ToString());
// ddlSeatNumber.Items[ddlSeatNumber.Items.Count - 1].Value = Convert.ToString(i);
// }
// else
// {
// continue;
// }
// }
// }
// }
//}
private bool CheckSeatAvaialbility(int SeatNumber, int BusNumber, int PayanamID)
{
var Result = false;
using (VallabaiDataContext db = new VallabaiDataContext())
{
var seat = from s in db.tab_VA_BUS_BOOKINGs
where s.seat_NUMBER == SeatNumber && s.payanam_ID == PayanamID && s.bus_NUMBER == BusNumber
select s;
if (seat.Count() > 0)
{
Result = true;
}
}
return(Result);
}
private void BindPayanam()
{
using (VallabaiDataContext db = new VallabaiDataContext())
{
var payanam = from p in db.tab_VA_PAYANAMs
where p.payanam_STARTDATE.Year == DateTime.Now.Year + 1
orderby p.year descending
select new { p.payanam_ID, p.year, p.payanam_TITLE, p.payanam_STARTDATE, p.payanam_ENDATE };
if (payanam.Count() > 0)
{
gvPayanam.DataSource = payanam;
gvPayanam.DataBind();
}
else
{
}
}
}
public static string GetSwamyCode()
{
var Result = string.Empty;
var CurrentYear = DateTime.Now.Year.ToString();
CurrentYear = CurrentYear.Substring(2);
var TotalSwamies = 0;
var Temp = string.Empty;
using (VallabaiDataContext db = new VallabaiDataContext())
{
var regi = from r in db.tab_VA_SWAMY_REGISTRATIONs
select r;
TotalSwamies = regi.Count() + 1;
Temp = TotalSwamies.ToString().PadLeft(4, '0');
}
Result = "VA" + CurrentYear + "" + Temp;
return(Result);
}
public static int GetLatestRegisterID()
{
var ID = 0;
using (VallabaiDataContext db = new VallabaiDataContext())
{
var reg = from r in db.tab_VA_SWAMY_REGISTRATIONs
orderby r.swamy_ID descending
select r;
if (reg.Count() > 0)
{
foreach (var item in reg)
{
ID = item.swamy_ID;
break;
}
}
}
return(ID);
}
protected void btnSearch_Click(object sender, EventArgs e)
{
var SearchCode = txtCode.Text;
using (VallabaiDataContext db = new VallabaiDataContext())
{
var user = from u in db.tab_VA_SWAMY_REGISTRATIONs
where (u.swamy_CODE == SearchCode || u.swamy_NAME.Contains(SearchCode))
select new { u.swamy_NAME, u.swamy_ID, u.swamy_PLACE, u.swamy_FATHER_SPOUSE_NAME };
if (user.Count() > 0)
{
ddlNames.Items.Clear();
foreach (var item in user)
{
ddlNames.Items.Add(item.swamy_NAME + " - " + item.swamy_FATHER_SPOUSE_NAME + " [" + item.swamy_PLACE + "]");
ddlNames.Items[ddlNames.Items.Count - 1].Value = Convert.ToString(item.swamy_ID);
}
}
}
}
public static string LoadSeats(int PayanamID, int BusID)
{
string Response = string.Empty;
StringBuilder Builder = new StringBuilder();
var VehicleType = "";
var TotalSeats = 0;
using (VallabaiDataContext db = new VallabaiDataContext())
{
var busdetails = from bd in db.tab_VA_PAYANAM_BUS_DETAILs
where bd.bus_ID == BusID
select new { bd.bus_TOTAL_SEATS, bd.vehicle_TYPE };
if (busdetails.Count() > 0)
{
foreach (var item in busdetails)
{
VehicleType = item.vehicle_TYPE;
TotalSeats = item.bus_TOTAL_SEATS;
break;
}
}
if (TotalSeats > 0)
{
Builder.Append(@"<ul class='seat_ul'>");
for (int i = 1; i <= 24; i++)
{
Builder.Append(@"<li class='seat' id='" + i + "' onclick='_SelectSeat(" + i + ")'>" + i + "</li>");
}
Builder.Append(@"</ul>");
Builder.Append(@"<ul class='seat_ul'>");
for (int i = 25; i <= 55; i++)
{
Builder.Append(@"<li class='seat' id='" + i + "' onclick='_SelectSeat(" + i + ")'>" + i + "</li>");
}
Builder.Append(@"</ul>");
}
}
Response = Builder.ToString();
return(Response);
}
protected void btnSaveBusDetails_Click(object sender, EventArgs e)
{
if (Page.IsValid)
{
var PayanamID = Convert.ToInt32(Request.QueryString["pID"]);
var BusType = ddlVehicleType.SelectedValue;
var RegistrationNumber = txtRegistrationNumber.Text;
var BusNumber = Convert.ToInt32(txtBusNumber.Text);
var TotalSeats = Convert.ToInt32(txtTotalSeats.Text);
var DriverName = txtDriverName.Text;
var DriverMobileNumber = txtDriverMobileNumber.Text;
using (VallabaiDataContext db = new VallabaiDataContext())
{
try
{
tab_VA_PAYANAM_BUS_DETAIL detail = new tab_VA_PAYANAM_BUS_DETAIL();
detail.bus_PAYANAM_NUMBER = PayanamID;
detail.vehicle_TYPE = BusType;
detail.bus_REGISTRATION_NUMBER = RegistrationNumber;
detail.bus_NUMBER = BusNumber;
detail.bus_TOTAL_SEATS = TotalSeats;
detail.bus_DRIVER_NAME = DriverName;
detail.bus_DRIVER_MOBILE_NUMBER = DriverMobileNumber;
db.tab_VA_PAYANAM_BUS_DETAILs.InsertOnSubmit(detail);
db.SubmitChanges();
Page.ClientScript.RegisterStartupScript(this.GetType(), "ShowStatus", "$(function() { funShowMessage('User Details updated successfully')});", true);
}
catch (Exception ex)
{
Page.ClientScript.RegisterStartupScript(this.GetType(), "ShowStatus", "$(function() { funShowMessage('" + ex.Message.ToString() + "')});", true);
}
}
}
else
{
Response.Write("Sethu");
}
}
private void BindBookedSeats()
{
var PayanamID = Convert.ToInt32(Request.QueryString["pid"]);
using (VallabaiDataContext db = new VallabaiDataContext())
{
var seats = from s in db.tab_VA_BUS_BOOKINGs
join r in db.tab_VA_SWAMY_REGISTRATIONs on s.user_ID equals r.swamy_ID
join p in db.tab_VA_PAYANAMs on s.payanam_ID equals p.payanam_ID
join bd in db.tab_VA_PAYANAM_BUS_DETAILs on s.bus_NUMBER equals bd.bus_ID
where s.payanam_ID == PayanamID
orderby s.seat_NUMBER
select new { r.swamy_CODE, r.swamy_NAME, r.swamy_FATHER_SPOUSE_NAME, r.swamy_PLACE, r.swamy_MOBILE_NUMBER, bd.vehicle_TYPE, bd.bus_NUMBER, s.seat_NUMBER, p.payanam_TITLE };
if (seats.Count() > 0)
{
gvSwamies.DataSource = seats;
gvSwamies.DataBind();
}
}
}
private void GetBusNumbers()
{
var PayanamID = Convert.ToInt32(Request.QueryString["pid"]);
ddlBusNumber.Items.Clear();
using (VallabaiDataContext db = new VallabaiDataContext())
{
var bus = from b in db.tab_VA_PAYANAM_BUS_DETAILs
join p in db.tab_VA_PAYANAMs on b.bus_PAYANAM_NUMBER equals p.payanam_ID
where b.bus_PAYANAM_NUMBER == PayanamID
select new { b.bus_ID, b.bus_NUMBER, p.payanam_TITLE, b.vehicle_TYPE };
if (bus.Count() > 0)
{
foreach (var item in bus)
{
ddlBus.Items.Add(Convert.ToString(item.vehicle_TYPE + "- " + item.bus_NUMBER));
ddlBus.Items[ddlBus.Items.Count - 1].Value = Convert.ToString(item.bus_ID);
ddlBusNumber.Items.Add(Convert.ToString(item.vehicle_TYPE + " - " + item.bus_NUMBER + " [" + item.payanam_TITLE + "]"));
ddlBusNumber.Items[ddlBusNumber.Items.Count - 1].Value = Convert.ToString(item.bus_ID);
}
}
}
}
public static string GetRecentBirthdays()
{
string Response = string.Empty;
var CurrentMonth = DateTime.Now.Month;
var CurrentYear = DateTime.Now.Year;
StringBuilder builder = new StringBuilder();
using (VallabaiDataContext db = new VallabaiDataContext())
{
var swamies = from s in db.tab_VA_SWAMY_REGISTRATIONs
where s.swamy_DOB.Value.Month == CurrentMonth && s.swamy_DOB.HasValue == true
select s;
if (swamies != null)
{
if (swamies.Count() > 0)
{
foreach (var item in swamies)
{
if (File.Exists(HttpContext.Current.Server.MapPath("~/management/Resources/user/") + item.swamy_ID + ".jpg"))
{
builder.Append(@"<a href='#'><div class='inbox-item'><div class='inbox-item-img'><img src='../../Resources/user/" + item.swamy_ID + ".jpg' class='img-circle' alt=''></div>");
}
else
{
builder.Append(@"<a href='#'><div class='inbox-item'><div class='inbox-item-img'><img src='../../Resources/user/noimage.jpg' class='img-circle' alt=''></div>");
}
builder.Append(@"<p class='inbox-item-author'>" + item.swamy_NAME + "</p>");
builder.Append(@"<p class='inbox-item-text'>" + item.swamy_PLACE + ',' + item.swamy_DISTRICT + "</p>");
builder.Append(@"<p class='inbox-item-date'>" + string.Format("{0:dd-MMM}", item.swamy_DOB) + "</p></div></a>");
}
}
}
}
Response = builder.ToString();
return(Response);
}
private void BindBookedSeats(int PayanamID, int BusID)
{
using (VallabaiDataContext db = new VallabaiDataContext())
{
var seats = from s in db.tab_VA_BUS_BOOKINGs
join r in db.tab_VA_SWAMY_REGISTRATIONs on s.user_ID equals r.swamy_ID
join p in db.tab_VA_PAYANAMs on s.payanam_ID equals p.payanam_ID
where s.payanam_ID == PayanamID && s.bus_NUMBER == BusID
join bd in db.tab_VA_PAYANAM_BUS_DETAILs on s.bus_NUMBER equals bd.bus_NUMBER
orderby s.seat_NUMBER
select new { r.swamy_CODE, r.swamy_NAME, r.swamy_FATHER_SPOUSE_NAME, r.swamy_PLACE, bd.vehicle_TYPE, r.swamy_MOBILE_NUMBER, s.bus_NUMBER, s.seat_NUMBER, p.payanam_TITLE };
if (seats.Count() > 0)
{
gvSwamies.DataSource = seats;
gvSwamies.DataBind();
}
else
{
gvSwamies.DataSource = null;
gvSwamies.DataBind();
}
}
}
protected void btnGo_Click(object sender, EventArgs e)
{
var fileName = flData.FileName;
var fileContentType = flData.PostedFile.ContentType;
var pathUploaded = Uploader.FileUploader(flData.PostedFile);
var connectionString = Uploader.GetExcelConnectionString(fileContentType, pathUploaded);
OleDbConnection OleCon = new OleDbConnection(connectionString);
DataTable tbl = new DataTable("MyTable");
OleCon.Open();
var tableName = OleCon.GetSchema("Tables").Rows[0]["TABLE_NAME"];
OleDbCommand OleCmd = new OleDbCommand(string.Format("Select * from [{0}]", tableName), OleCon);
OleDbDataAdapter adp = new OleDbDataAdapter(OleCmd);
adp.Fill(tbl);
using (VallabaiDataContext db = new VallabaiDataContext())
{
foreach (DataRow row in tbl.Rows)
{
var OldRegID = Convert.ToString(row.ItemArray[0]);
var Code = OldRegID;
var Name = Convert.ToString(row.ItemArray[1]);
var FatherName = Convert.ToString(row.ItemArray[2]);
var Address = Convert.ToString(row.ItemArray[3]);
var Age = Convert.ToString(row.ItemArray[4]);
var MalaiDetail = Convert.ToString(row.ItemArray[5]);
var MalaiCount = 0;
if (MalaiDetail != "")
{
MalaiCount = 0;
}
else if (MalaiDetail == "KANNI")
{
MalaiCount = 1;
}
else
{
MalaiCount = Convert.ToInt32(MalaiDetail);
}
var MobileNumber = Convert.ToString(row.ItemArray[6]);
var Membership = Convert.ToString(row.ItemArray[10]);
var IsMember = false;
if (Membership != "")
{
IsMember = true;
}
tab_VA_SWAMY_REGISTRATION reg = new tab_VA_SWAMY_REGISTRATION();
try
{
reg.swamy_CODE = Code;
reg.swamy_OLD_REG_NUMBER = OldRegID;
reg.swamy_NAME = Name;
reg.swamy_FATHER_SPOUSE_NAME = FatherName;
reg.swamy_GENDER = "Male";
reg.swamy_IS_MEMBER = IsMember;
reg.swamy_PLACE = Address;
reg.swamy_ADDRESS = Address;
reg.swamy_MOBILE_NUMBER = MobileNumber;
reg.swamy_DISTRICT = "Ramanathapuram";
reg.swamy_MALAI_VISIT = MalaiCount;
reg.swamy_MEMBERSHIP_TYPE = Membership;
db.tab_VA_SWAMY_REGISTRATIONs.InsertOnSubmit(reg);
db.SubmitChanges();
}
catch (Exception ex)
{
Response.Write(ex.Message);
}
}
Response.Write("Upload Completed");
}
}
protected void btnSave_Click(object sender, EventArgs e)
{
if (Page.IsValid)
{
var SwamyName = txtName.Text;
var SwamyFatherName = txtFatherName.Text;
var SwamyGender = string.Empty;
if (rdlSwamy.Checked)
{
SwamyGender = rdlSwamy.Value;
}
else
{
SwamyGender = rdlMaaligaipuram.Value;
}
var SwamyDOB = string.Empty;
if (Utilities.isValidDate(txtDOB.Text))
{
SwamyDOB = txtDOB.Text;
}
var SwamyMobileNumber = txtMobileNumber.Text;
var SwamyAlternateMobile = txtAlternateMobile.Text;
var SwamyPlace = txtPlace.Text;
var SwamyAddress = txtAddress.Text;
var SwamyDistrict = ddlDistrict.SelectedValue;
var SwamyBloodGroup = ddlBloodGroup.SelectedValue;
var SwamyMalaiVisit = 0;
if (Utilities.isNumber(txtMalai.Text))
{
SwamyMalaiVisit = Convert.ToInt32(txtMalai.Text);
}
var SwamyKanniPoojaiDate = string.Empty;
if (Utilities.isValidDate(txtKanniPoojaiDate.Text))
{
SwamyKanniPoojaiDate = txtKanniPoojaiDate.Text;
}
var SwamyMemebershipExpiry = string.Empty;
if (Utilities.isValidDate(txtMembershipExpiry.Text))
{
SwamyMemebershipExpiry = txtMembershipExpiry.Text;
}
var SwamyMembership = string.Empty;
if (rdlNonMeber.Checked)
{
SwamyMembership = rdlNonMeber.Value;
}
else if (rdlLifetime.Checked)
{
SwamyMembership = rdlLifetime.Value;
}
else
{
SwamyMembership = rdlShortTermMember.Value;
}
//Get Swamy Code
if (btnSave.Text == "Save")
{
var SwamyCode = Utilities.GetSwamyCode();
using (VallabaiDataContext db = new VallabaiDataContext())
{
tab_VA_SWAMY_REGISTRATION regis = new tab_VA_SWAMY_REGISTRATION();
regis.swamy_CODE = SwamyCode;
regis.swamy_NAME = SwamyName;
regis.swamy_FATHER_SPOUSE_NAME = SwamyFatherName;
regis.swamy_GENDER = SwamyGender;
if (!string.IsNullOrEmpty(SwamyDOB))
{
regis.swamy_DOB = Convert.ToDateTime(SwamyDOB);
}
regis.swamy_PLACE = SwamyPlace;
regis.swamy_ADDRESS = SwamyAddress;
regis.swamy_MOBILE_NUMBER = SwamyMobileNumber;
regis.swamy_ALTERNATE_MOBILE = SwamyAlternateMobile;
regis.swamy_DISTRICT = SwamyDistrict;
regis.swamy_BLOOD_GROUP = SwamyBloodGroup;
regis.swamy_MALAI_VISIT = SwamyMalaiVisit;
if (!string.IsNullOrEmpty(SwamyKanniPoojaiDate))
{
regis.swamy_KANNI_POOJAI_DATE = Convert.ToDateTime(SwamyKanniPoojaiDate);
}
//regis.swamy_IS_MEMBER =
regis.swamy_MEMBERSHIP_TYPE = SwamyMembership;
if (!string.IsNullOrEmpty(SwamyMemebershipExpiry))
{
regis.swamy_MEMBERSHIP_EXPIRY_DATE = Convert.ToDateTime(SwamyMemebershipExpiry);
}
db.tab_VA_SWAMY_REGISTRATIONs.InsertOnSubmit(regis);
db.SubmitChanges();
var SwamyID = Utilities.GetLatestRegisterID();
//Save The Photo against the Swamy
if (flPhoto.PostedFiles != null)
{
if (flPhoto.PostedFile.ContentLength > 0)
{
flPhoto.PostedFile.SaveAs(Server.MapPath("~/management/Resources/temp/" + SwamyID + ".jpg"));
}
}
Page.ClientScript.RegisterStartupScript(this.GetType(), "ShowStatus", "$(function() { funShowMessage('User Details updated successfully')});", true);
ClearFields();
}
}
else
{
if (Request.QueryString["sid"] != null)
{
var SwamyID = Convert.ToInt32(Ciphering.Decrypt(Convert.ToString(Request.QueryString["sid"])));
using (VallabaiDataContext db = new VallabaiDataContext())
{
var swamy = from s in db.tab_VA_SWAMY_REGISTRATIONs
where s.swamy_ID == SwamyID
select s;
if (swamy.Count() > 0)
{
foreach (var regis in swamy)
{
regis.swamy_NAME = SwamyName;
regis.swamy_FATHER_SPOUSE_NAME = SwamyFatherName;
if (!string.IsNullOrEmpty(SwamyDOB))
{
regis.swamy_DOB = Convert.ToDateTime(SwamyDOB);
}
regis.swamy_MOBILE_NUMBER = SwamyMobileNumber;
regis.swamy_ALTERNATE_MOBILE = SwamyAlternateMobile;
regis.swamy_PLACE = SwamyPlace;
regis.swamy_ADDRESS = SwamyAddress;
regis.swamy_DISTRICT = SwamyDistrict;
regis.swamy_BLOOD_GROUP = SwamyBloodGroup;
regis.swamy_MALAI_VISIT = SwamyMalaiVisit;
regis.swamy_MEMBERSHIP_TYPE = SwamyMembership;
if (!string.IsNullOrEmpty(SwamyMemebershipExpiry))
{
regis.swamy_MEMBERSHIP_EXPIRY_DATE = Convert.ToDateTime(SwamyMemebershipExpiry);
}
if (flPhoto.PostedFiles != null)
{
if (flPhoto.PostedFile.ContentLength > 0)
{
flPhoto.PostedFile.SaveAs(Server.MapPath("~/management/Resources/temp/" + SwamyID + ".jpg"));
}
}
db.SubmitChanges();
Page.ClientScript.RegisterStartupScript(this.GetType(), "ShowStatus", "$(function() { funShowMessage('User Details updated successfully')});", true);
ClearFields();
}
}
}
}
}
}
}
protected void btnExport_Click(object sender, EventArgs e)
{
var BusID = Convert.ToInt32(ddlBus.SelectedValue);
var PayanamID = Convert.ToInt32(Request.QueryString["pid"]);
if (BusID > 0)
{
var fileName = "Bus_Seat_Allotment.xlsx";
var outputDirectory = Server.MapPath("~/management/resources/reports/");
var outFileName = outputDirectory + fileName;
if (File.Exists(outFileName))
{
File.Delete(outFileName);
}
var file = new FileInfo(outFileName);
using (var package = new ExcelPackage(file))
{
ExcelWorksheet worksheet = package.Workbook.Worksheets.Add("Bus Allotment");
ExcelRange rHeader;
worksheet.Cells[1, 1, 1, 8].Merge = true;
rHeader = worksheet.Cells[1, 1];
rHeader.Value = "Sree Vallabai Ayyappan - Sabari Yatra Bus Booking Seat Allotment";
rHeader.Style.Font.Size = 18;
rHeader.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
rHeader.Style.Font.Bold = true;
rHeader.Style.Font.Name = "Calibri";
rHeader.Style.Border.Right.Style = ExcelBorderStyle.Thin;
rHeader.Style.Border.Right.Color.SetColor(Color.Gray);
AddSecondHeaderTitle(worksheet, 2, 1, "S No");
AddSecondHeaderTitle(worksheet, 2, 2, "Code");
AddSecondHeaderTitle(worksheet, 2, 3, "Name");
AddSecondHeaderTitle(worksheet, 2, 4, "Father");
AddSecondHeaderTitle(worksheet, 2, 5, "Place");
AddSecondHeaderTitle(worksheet, 2, 6, "Bus Number");
AddSecondHeaderTitle(worksheet, 2, 7, "Seat Number");
AddSecondHeaderTitle(worksheet, 2, 8, "Mobile Number");
var rowIndex = 3;
using (VallabaiDataContext db = new VallabaiDataContext())
{
var seats = from s in db.tab_VA_BUS_BOOKINGs
join r in db.tab_VA_SWAMY_REGISTRATIONs on s.user_ID equals r.swamy_ID
join p in db.tab_VA_PAYANAMs on s.payanam_ID equals p.payanam_ID
join bd in db.tab_VA_PAYANAM_BUS_DETAILs on s.bus_NUMBER equals bd.bus_ID
where s.payanam_ID == PayanamID && s.bus_NUMBER == BusID
orderby s.seat_NUMBER
select new { r.swamy_CODE, r.swamy_NAME, r.swamy_FATHER_SPOUSE_NAME, r.swamy_PLACE, r.swamy_MOBILE_NUMBER,
bd.vehicle_TYPE,
bd.bus_NUMBER,
s.seat_NUMBER,
p.payanam_TITLE };
if (seats.Count() > 0)
{
var Index = 1;
foreach (var item in seats)
{
ExcelRange range;
range = worksheet.Cells[rowIndex, 1];
range.Value = Index;
AddDataRow(worksheet, rowIndex, 1, "S No", false);
range = worksheet.Cells[rowIndex, 2];
range.Value = item.swamy_CODE;
AddDataRow(worksheet, rowIndex, 2, "Code", false);
range = worksheet.Cells[rowIndex, 3];
range.Value = item.swamy_NAME;
AddDataRow(worksheet, rowIndex, 3, "Name", false);
range = worksheet.Cells[rowIndex, 4];
range.Value = item.swamy_FATHER_SPOUSE_NAME;
AddDataRow(worksheet, rowIndex, 4, "Name", false);
range = worksheet.Cells[rowIndex, 5];
range.Value = item.swamy_PLACE;
AddDataRow(worksheet, rowIndex, 5, "Place", false);
range = worksheet.Cells[rowIndex, 6];
range.Value = item.vehicle_TYPE + " - " + item.bus_NUMBER;
AddDataRow(worksheet, rowIndex, 6, "Bus Number", false);
range = worksheet.Cells[rowIndex, 7];
range.Value = item.seat_NUMBER;
AddDataRow(worksheet, rowIndex, 7, "Seat Number", false);
range = worksheet.Cells[rowIndex, 8];
range.Value = item.swamy_MOBILE_NUMBER;
AddDataRow(worksheet, rowIndex, 8, "Mobile", false);
Index++;
rowIndex++;
}
}
else
{
}
}
package.Save();
try
{
WebClient req = new WebClient();
HttpResponse response = HttpContext.Current.Response;
response.Clear();
response.ClearContent();
response.ClearHeaders();
response.Buffer = true;
var displayFileName = string.Empty;
displayFileName = "Bus_SeatAllotment.xlsx";
response.AddHeader("Content-Disposition", "attachment;filename=\"" + displayFileName + "\"");
byte[] data = req.DownloadData(outFileName);
response.BinaryWrite(data);
response.End();
}
catch (Exception ex)
{
if (!(ex is System.Threading.ThreadAbortException))
{
Response.Write(ex.Message.ToString());
}
}
}
}
}
private void DisplayDetails(int SwamyID)
{
if (SwamyID > 0)
{
using (VallabaiDataContext db = new VallabaiDataContext())
{
var swamy = from s in db.tab_VA_SWAMY_REGISTRATIONs
where s.swamy_ID == SwamyID
select s;
if (swamy.Count() > 0)
{
foreach (var item in swamy)
{
txtName.Text = item.swamy_NAME;
txtFatherName.Text = item.swamy_FATHER_SPOUSE_NAME;
if (item.swamy_GENDER == "Maaligaipuram")
{
rdlMaaligaipuram.Checked = true;
}
if (item.swamy_DOB != null)
{
txtDOB.Text = string.Format("{0:yyyy-MM-dd}", Convert.ToDateTime(item.swamy_DOB));
}
txtMobileNumber.Text = item.swamy_MOBILE_NUMBER;
txtAlternateMobile.Text = item.swamy_ALTERNATE_MOBILE;
txtPlace.Text = item.swamy_PLACE;
txtAddress.Text = item.swamy_ADDRESS;
ddlDistrict.SelectedValue = item.swamy_DISTRICT;
ddlBloodGroup.SelectedValue = item.swamy_BLOOD_GROUP;
txtMalai.Text = Convert.ToString(item.swamy_MALAI_VISIT);
if (item.swamy_KANNI_POOJAI_DATE != null)
{
txtKanniPoojaiDate.Text = string.Format("{0:yyyy-MM-dd}", Convert.ToDateTime(item.swamy_KANNI_POOJAI_DATE));
}
if (item.swamy_MEMBERSHIP_TYPE == "Non Member")
{
rdlNonMeber.Checked = true;
}
else if (item.swamy_MEMBERSHIP_TYPE == "Lifetime")
{
rdlLifetime.Checked = true;
}
else
{
rdlShortTermMember.Checked = true;
}
if (item.swamy_MEMBERSHIP_EXPIRY_DATE != null)
{
txtMembershipExpiry.Text = string.Format("{0:yyyy-MM-dd}", Convert.ToDateTime(item.swamy_MEMBERSHIP_EXPIRY_DATE));
}
if (File.Exists(Server.MapPath("~/management/resources/temp/" + SwamyID + ".jpg")))
{
imgPreviewPhoto.ImageUrl = Server.MapPath("~/management/resources/temp/" + SwamyID + ".jpg");
}
else
{
imgPreviewPhoto.ImageUrl = Server.MapPath("~/management/resources/temp/noimage.png");
}
btnSave.Text = "Update";
break;
}
}
}
}
}
