public ActionResult DownloadTC(int sr_number, string username, string session, int tc_number, DateTime tc_date)
{
if (tc_format == "CBSE")
{
ExcelCBSE_TC_form tc = new ExcelCBSE_TC_form();
return(File(tc.Download_TC(sr_number, session, tc_number, tc_date), "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", "TC_" + sr_number + ".xlsx"));
}
else
{
ExcelTc_form tc = new ExcelTc_form();
return(File(tc.Download_TC(sr_number, username, session, tc_number, tc_date), "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", "TC_" + sr_number + ".xlsx"));
}
}
public byte[] gate_pass(string session, int gate_pass_no, string image)
{
try
{
using (MySqlConnection con = new MySqlConnection(ConfigurationManager.ConnectionStrings["DefaultConnection"].ToString()))
{
string query = @"SELECT
a.session,
gate_pass_no,
b.sr_number,
date_time,
std_relation,
escorter_name,
escorter_address,
reason,
CONCAT(IFNULL(std_first_name, ''),
' ',
IFNULL(std_last_name, '')) std_name,
CONCAT(IFNULL(class_name, ''),
' ',
IFNULL(section_name, '')) std_class,
std_father_name,
COALESCE(std_contact, std_contact1, std_contact2) contact_no,
g.pickup_point
FROM
std_halfday_log a,
sr_register b,
mst_std_class c,
mst_std_section d,
mst_class e,
mst_section f,
mst_transport g
WHERE
a.session = @session
AND a.session = c.session
AND c.session = d.session
AND d.session = e.session
AND e.session = f.session
AND f.session = g.session
AND a.sr_number = b.sr_number
AND b.sr_number = c.sr_num
AND c.sr_num = d.sr_num
AND c.class_id = e.class_id
AND d.section_id = f.section_id
AND g.pickup_id = b.std_pickup_id
AND a.gate_pass_no = @gate_pass_no";
var result = con.Query <ExcelGatePass>(query, new { session = session, gate_pass_no = gate_pass_no }).SingleOrDefault();
string gatePassNo = gate_pass_no.ToString().PadLeft(4, '0');
ExcelPackage pck = new ExcelPackage();
ExcelWorksheet ws = pck.Workbook.Worksheets.Add("Gate Pass");
ws.PrinterSettings.TopMargin = 1m / 2.54m;
ws.PrinterSettings.BottomMargin = 1m / 2.54m;
ws.PrinterSettings.LeftMargin = 1.8m / 2.54m;
ws.PrinterSettings.RightMargin = 1.8m / 2.54m;
ws.PrinterSettings.HeaderMargin = 0.0m / 2.54m;
ws.PrinterSettings.FooterMargin = 0.0m / 2.54m;
ws.PrinterSettings.HorizontalCentered = false;
ws.Column(1).Width = ExcelTc_form.GetTrueColumnWidth(13.18);
ws.Column(2).Width = ExcelTc_form.GetTrueColumnWidth(11.09);
ws.Column(3).Width = ExcelTc_form.GetTrueColumnWidth(8.09);
ws.Column(4).Width = ExcelTc_form.GetTrueColumnWidth(8.09);
ws.Column(5).Width = ExcelTc_form.GetTrueColumnWidth(9.18);
ws.Column(6).Width = ExcelTc_form.GetTrueColumnWidth(8.09);
ws.Column(7).Width = ExcelTc_form.GetTrueColumnWidth(8.09);
ws.Column(8).Width = ExcelTc_form.GetTrueColumnWidth(14.55);
for (int i = 2; i <= 15; i++)
{
ws.Row(i).Height = 17;
}
ws.Row(1).Height = 108.8;
using (ExcelRange Rng = ws.Cells["A1:H1"])
{
Rng.Merge = true;
Rng.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
Rng.Style.VerticalAlignment = ExcelVerticalAlignment.Center;
Rng.Style.WrapText = true;
ExcelRichTextCollection RichTxtCollection = Rng.RichText;
ExcelRichText RichText = RichTxtCollection.Add("Gate Pass\n");
RichText.Size = 14;
RichText = RichTxtCollection.Add(SchoolName + "\n");
RichText.Size = 28;
RichText.Bold = true;
RichText = RichTxtCollection.Add(Address + "\n");
RichText.Size = 11;
RichText = RichTxtCollection.Add(Affiliation);
RichText.Size = 11;
RichText.Bold = false;
Rng.Style.Border.Bottom.Style = ExcelBorderStyle.Thin;
}
if (image == "../../images/person.png")
{
using (System.Drawing.Image img = System.Drawing.Image.FromFile(HttpContext.Current.Server.MapPath("/images/person.png")))
{
var excelImage = ws.Drawings.AddPicture("Picture", img);
//add the image to row 20, column E
excelImage.SetPosition(3, 0, 4, 0);
excelImage.SetSize(233, 134);
}
}
else
{
var img = Base64StringToBitmap(image.Remove(0, 22));
var excelImage = ws.Drawings.AddPicture("Picture", img);
//add the image to row 20, column E
excelImage.SetPosition(3, 0, 4, 0);
excelImage.SetSize(233, 134);
}
ws.Cells["A2"].Value = "Gate Pass No: ";
ws.Cells["A2"].Style.Font.Name = "Calibri";
ws.Cells["A2"].Style.Font.Size = 11;
ws.Cells["A2"].Style.Font.Bold = true;
ws.Cells["A2"].Style.HorizontalAlignment = ExcelHorizontalAlignment.Left;
ws.Cells["A2"].Style.VerticalAlignment = ExcelVerticalAlignment.Center;
ws.Cells["B2"].Value = session + "/" + gatePassNo.ToString();;
ws.Cells["B2"].Style.Font.Name = "Calibri";
ws.Cells["B2"].Style.Font.Size = 11;
ws.Cells["B2"].Style.Font.UnderLine = true;
ws.Cells["B2"].Style.Font.Italic = true;
ws.Cells["B2"].Style.HorizontalAlignment = ExcelHorizontalAlignment.Left;
ws.Cells["B2"].Style.VerticalAlignment = ExcelVerticalAlignment.Center;
ws.Cells["D2"].Value = "Adm No: ";
ws.Cells["D2"].Style.Font.Name = "Calibri";
ws.Cells["D2"].Style.Font.Size = 11;
ws.Cells["D2"].Style.Font.Bold = true;
ws.Cells["D2"].Style.HorizontalAlignment = ExcelHorizontalAlignment.Left;
ws.Cells["D2"].Style.VerticalAlignment = ExcelVerticalAlignment.Center;
ws.Cells["E2"].Value = result.sr_number.ToString();
ws.Cells["E2"].Style.Font.Name = "Calibri";
ws.Cells["E2"].Style.Font.Size = 11;
ws.Cells["E2"].Style.Font.UnderLine = true;
ws.Cells["E2"].Style.Font.Italic = true;
ws.Cells["E2"].Style.HorizontalAlignment = ExcelHorizontalAlignment.Left;
ws.Cells["E2"].Style.VerticalAlignment = ExcelVerticalAlignment.Center;
ws.Cells["G2"].Value = "Date: ";
ws.Cells["G2"].Style.Font.Name = "Calibri";
ws.Cells["G2"].Style.Font.Size = 11;
ws.Cells["G2"].Style.Font.Bold = true;
ws.Cells["G2"].Style.HorizontalAlignment = ExcelHorizontalAlignment.Left;
ws.Cells["G2"].Style.VerticalAlignment = ExcelVerticalAlignment.Center;
ws.Cells["H2"].Value = result.date_time.ToString("dd/MM/yyyy hh:mm");
ws.Cells["H2"].Style.Font.Name = "Calibri";
ws.Cells["H2"].Style.Font.Size = 11;
ws.Cells["H2"].Style.Font.UnderLine = true;
ws.Cells["H2"].Style.Font.Italic = true;
ws.Cells["H2"].Style.HorizontalAlignment = ExcelHorizontalAlignment.Left;
ws.Cells["H2"].Style.VerticalAlignment = ExcelVerticalAlignment.Center;
ws.Cells["A4"].Value = "Name ";
ws.Cells["A4"].Style.Font.Name = "Calibri";
ws.Cells["A4"].Style.Font.Size = 11;
ws.Cells["A4"].Style.Font.Bold = true;
ws.Cells["A4"].Style.HorizontalAlignment = ExcelHorizontalAlignment.Left;
ws.Cells["A4"].Style.VerticalAlignment = ExcelVerticalAlignment.Center;
ws.Cells["B4"].Value = result.std_name;
ws.Cells["B4"].Style.Font.Name = "Calibri";
ws.Cells["B4"].Style.Font.Size = 11;
ws.Cells["B4"].Style.Font.UnderLine = true;
ws.Cells["B4"].Style.Font.Italic = true;
ws.Cells["B4"].Style.HorizontalAlignment = ExcelHorizontalAlignment.Left;
ws.Cells["B4"].Style.VerticalAlignment = ExcelVerticalAlignment.Center;
ws.Cells["A5"].Value = "Class ";
ws.Cells["A5"].Style.Font.Name = "Calibri";
ws.Cells["A5"].Style.Font.Size = 11;
ws.Cells["A5"].Style.Font.Bold = true;
ws.Cells["A5"].Style.HorizontalAlignment = ExcelHorizontalAlignment.Left;
ws.Cells["A5"].Style.VerticalAlignment = ExcelVerticalAlignment.Center;
ws.Cells["B5"].Value = result.std_class;
ws.Cells["B5"].Style.Font.Name = "Calibri";
ws.Cells["B5"].Style.Font.Size = 11;
ws.Cells["B5"].Style.Font.UnderLine = true;
ws.Cells["B5"].Style.Font.Italic = true;
ws.Cells["B5"].Style.HorizontalAlignment = ExcelHorizontalAlignment.Left;
ws.Cells["B5"].Style.VerticalAlignment = ExcelVerticalAlignment.Center;
ws.Cells["A6"].Value = "Father Name ";
ws.Cells["A6"].Style.Font.Name = "Calibri";
ws.Cells["A6"].Style.Font.Size = 11;
ws.Cells["A6"].Style.Font.Bold = true;
ws.Cells["A6"].Style.HorizontalAlignment = ExcelHorizontalAlignment.Left;
ws.Cells["A6"].Style.VerticalAlignment = ExcelVerticalAlignment.Center;
ws.Cells["B6"].Value = result.std_father_name;
ws.Cells["B6"].Style.Font.Name = "Calibri";
ws.Cells["B6"].Style.Font.Size = 11;
ws.Cells["B6"].Style.Font.UnderLine = true;
ws.Cells["B6"].Style.Font.Italic = true;
ws.Cells["B6"].Style.HorizontalAlignment = ExcelHorizontalAlignment.Left;
ws.Cells["B6"].Style.VerticalAlignment = ExcelVerticalAlignment.Center;
ws.Cells["A7"].Value = "Contact No ";
ws.Cells["A7"].Style.Font.Name = "Calibri";
ws.Cells["A7"].Style.Font.Size = 11;
ws.Cells["A7"].Style.Font.Bold = true;
ws.Cells["A7"].Style.HorizontalAlignment = ExcelHorizontalAlignment.Left;
ws.Cells["A7"].Style.VerticalAlignment = ExcelVerticalAlignment.Center;
ws.Cells["B7"].Value = result.contact_no;
ws.Cells["B7"].Style.Font.Name = "Calibri";
ws.Cells["B7"].Style.Font.Size = 11;
ws.Cells["B7"].Style.Font.UnderLine = true;
ws.Cells["B7"].Style.Font.Italic = true;
ws.Cells["B7"].Style.HorizontalAlignment = ExcelHorizontalAlignment.Left;
ws.Cells["B7"].Style.VerticalAlignment = ExcelVerticalAlignment.Center;
ws.Cells["A8"].Value = "Person Name ";
ws.Cells["A8"].Style.Font.Name = "Calibri";
ws.Cells["A8"].Style.Font.Size = 11;
ws.Cells["A8"].Style.Font.Bold = true;
ws.Cells["A8"].Style.HorizontalAlignment = ExcelHorizontalAlignment.Left;
ws.Cells["A8"].Style.VerticalAlignment = ExcelVerticalAlignment.Center;
ws.Cells["B8"].Value = result.escorter_name;
ws.Cells["B8"].Style.Font.Name = "Calibri";
ws.Cells["B8"].Style.Font.Size = 11;
ws.Cells["B8"].Style.Font.UnderLine = true;
ws.Cells["B8"].Style.Font.Italic = true;
ws.Cells["B8"].Style.HorizontalAlignment = ExcelHorizontalAlignment.Left;
ws.Cells["B8"].Style.VerticalAlignment = ExcelVerticalAlignment.Center;
ws.Cells["A9"].Value = "Relationship ";
ws.Cells["A9"].Style.Font.Name = "Calibri";
ws.Cells["A9"].Style.Font.Size = 11;
ws.Cells["A9"].Style.Font.Bold = true;
ws.Cells["A9"].Style.HorizontalAlignment = ExcelHorizontalAlignment.Left;
ws.Cells["A9"].Style.VerticalAlignment = ExcelVerticalAlignment.Center;
ws.Cells["B9"].Value = result.std_relation;
ws.Cells["B9"].Style.Font.Name = "Calibri";
ws.Cells["B9"].Style.Font.Size = 11;
ws.Cells["B9"].Style.Font.UnderLine = true;
ws.Cells["B9"].Style.Font.Italic = true;
ws.Cells["B9"].Style.HorizontalAlignment = ExcelHorizontalAlignment.Left;
ws.Cells["B9"].Style.VerticalAlignment = ExcelVerticalAlignment.Center;
ws.Cells["A10"].Value = "Person Address ";
ws.Cells["A10"].Style.Font.Name = "Calibri";
ws.Cells["A10"].Style.Font.Size = 11;
ws.Cells["A10"].Style.Font.Bold = true;
ws.Cells["A10"].Style.HorizontalAlignment = ExcelHorizontalAlignment.Left;
ws.Cells["A10"].Style.VerticalAlignment = ExcelVerticalAlignment.Center;
ws.Cells["B10"].Value = result.escorter_address;
ws.Cells["B10"].Style.Font.Name = "Calibri";
ws.Cells["B10"].Style.Font.Size = 11;
ws.Cells["B10"].Style.Font.UnderLine = true;
ws.Cells["B10"].Style.Font.Italic = true;
ws.Cells["B10"].Style.HorizontalAlignment = ExcelHorizontalAlignment.Left;
ws.Cells["B10"].Style.VerticalAlignment = ExcelVerticalAlignment.Center;
ws.Cells["A11"].Value = "Reason ";
ws.Cells["A11"].Style.Font.Name = "Calibri";
ws.Cells["A11"].Style.Font.Size = 11;
ws.Cells["A11"].Style.Font.Bold = true;
ws.Cells["A11"].Style.HorizontalAlignment = ExcelHorizontalAlignment.Left;
ws.Cells["A11"].Style.VerticalAlignment = ExcelVerticalAlignment.Center;
ws.Cells["B11"].Value = result.reason;
ws.Cells["B11"].Style.Font.Name = "Calibri";
ws.Cells["B11"].Style.Font.Size = 11;
ws.Cells["B11"].Style.Font.UnderLine = true;
ws.Cells["B11"].Style.Font.Italic = true;
ws.Cells["B11"].Style.HorizontalAlignment = ExcelHorizontalAlignment.Left;
ws.Cells["B11"].Style.VerticalAlignment = ExcelVerticalAlignment.Center;
ws.Cells["A12"].Value = "Transport ";
ws.Cells["A12"].Style.Font.Name = "Calibri";
ws.Cells["A12"].Style.Font.Size = 11;
ws.Cells["A12"].Style.Font.Bold = true;
ws.Cells["A12"].Style.HorizontalAlignment = ExcelHorizontalAlignment.Left;
ws.Cells["A12"].Style.VerticalAlignment = ExcelVerticalAlignment.Center;
ws.Cells["B12"].Value = result.pickup_point;
ws.Cells["B12"].Style.Font.Name = "Calibri";
ws.Cells["B12"].Style.Font.Size = 11;
ws.Cells["B12"].Style.Font.UnderLine = true;
ws.Cells["B12"].Style.Font.Italic = true;
ws.Cells["B12"].Style.HorizontalAlignment = ExcelHorizontalAlignment.Left;
ws.Cells["B12"].Style.VerticalAlignment = ExcelVerticalAlignment.Center;
ws.Cells["A16"].Value = "Parents Sign ";
ws.Cells["A16"].Style.Font.Name = "Calibri";
ws.Cells["A16"].Style.Font.Size = 11;
ws.Cells["A16"].Style.Font.Bold = true;
ws.Cells["A16"].Style.HorizontalAlignment = ExcelHorizontalAlignment.Left;
ws.Cells["A16"].Style.VerticalAlignment = ExcelVerticalAlignment.Center;
ws.Cells["D16"].Value = "Auth Sign. ";
ws.Cells["D16"].Style.Font.Name = "Calibri";
ws.Cells["D16"].Style.Font.Size = 11;
ws.Cells["D16"].Style.Font.Bold = true;
ws.Cells["D16"].Style.HorizontalAlignment = ExcelHorizontalAlignment.Left;
ws.Cells["D16"].Style.VerticalAlignment = ExcelVerticalAlignment.Center;
ws.Cells["H16"].Value = "Incharge Sign. ";
ws.Cells["H16"].Style.Font.Name = "Calibri";
ws.Cells["H16"].Style.Font.Size = 11;
ws.Cells["H16"].Style.Font.Bold = true;
ws.Cells["H16"].Style.HorizontalAlignment = ExcelHorizontalAlignment.Left;
ws.Cells["H16"].Style.VerticalAlignment = ExcelVerticalAlignment.Center;
return(pck.GetAsByteArray());
}
}
catch (Exception ex)
{
throw ex;
}
}
public ActionResult GenerateTC(int sr_number, string session)
{
using (MySqlConnection con = new MySqlConnection(ConfigurationManager.ConnectionStrings["DefaultConnection"].ToString()))
{
string query = @"select count(*) from tc_register where sr_num = @sr_number";
int check = con.Query <int>(query, new { sr_number = sr_number }).SingleOrDefault();
if (check != 0)
{
ViewData["message"] = "TC already generated.";
return(View("message"));
}
if (System.DateTime.Now.Month >= 4 && System.DateTime.Now.Month <= 12)
{
query = @"SELECT
ifnull(SUM(dues),0)
FROM
(SELECT
SUM(IFNULL(outstd_amount, 0) - IFNULL(rmt_amount, 0)) dues
FROM
out_standing a, sr_register b
WHERE
IFNULL(outstd_amount, 0) - IFNULL(rmt_amount, 0) != 0
AND month_no <= MONTH(CURDATE())
AND session = (SELECT
session
FROM
mst_session
WHERE
session_active = 'Y'
AND session_finalize = 'Y')
AND a.sr_number = b.sr_number
AND month_no BETWEEN 4 AND 12
AND b.sr_number = @sr_number UNION ALL SELECT
SUM(IFNULL(outstd_amount, 0) - IFNULL(rmt_amount, 0)) dues
FROM
out_standing a, sr_register b
WHERE
IFNULL(outstd_amount, 0) - IFNULL(rmt_amount, 0) != 0
AND a.session != (SELECT
session
FROM
mst_session
WHERE
session_active = 'Y'
AND session_finalize = 'Y')
AND a.sr_number = b.sr_number
AND b.sr_number = @sr_number) a";
}
else if (System.DateTime.Now.Month == 1)
{
query = @"SELECT
ifnull(SUM(dues),0)
FROM
(SELECT
SUM(IFNULL(outstd_amount, 0) - IFNULL(rmt_amount, 0)) dues
FROM
out_standing a, sr_register b
WHERE
IFNULL(outstd_amount, 0) - IFNULL(rmt_amount, 0) != 0
AND month_no NOT IN (2 , 3)
AND session = (SELECT
session
FROM
mst_session
WHERE
session_active = 'Y'
AND session_finalize = 'Y')
AND a.sr_number = b.sr_number
AND b.sr_number = @sr_number UNION ALL SELECT
SUM(IFNULL(outstd_amount, 0) - IFNULL(rmt_amount, 0)) dues
FROM
out_standing a, sr_register b
WHERE
IFNULL(outstd_amount, 0) - IFNULL(rmt_amount, 0) != 0
AND a.session != (SELECT
session
FROM
mst_session
WHERE
session_active = 'Y'
AND session_finalize = 'Y')
AND a.sr_number = b.sr_number
AND b.sr_number = @sr_number) a";
}
else if (System.DateTime.Now.Month == 2)
{
query = @"SELECT
ifnull(SUM(dues),0)
FROM
(SELECT
SUM(IFNULL(outstd_amount, 0) - IFNULL(rmt_amount, 0)) dues
FROM
out_standing a, sr_register b
WHERE
IFNULL(outstd_amount, 0) - IFNULL(rmt_amount, 0) != 0
AND month_no != 3
AND session = (SELECT
session
FROM
mst_session
WHERE
session_active = 'Y'
AND session_finalize = 'Y')
AND a.sr_number = b.sr_number
AND b.sr_number = @sr_number UNION ALL SELECT
SUM(IFNULL(outstd_amount, 0) - IFNULL(rmt_amount, 0)) dues
FROM
out_standing a, sr_register b
WHERE
IFNULL(outstd_amount, 0) - IFNULL(rmt_amount, 0) != 0
AND a.session != (SELECT
session
FROM
mst_session
WHERE
session_active = 'Y'
AND session_finalize = 'Y')
AND a.sr_number = b.sr_number
AND b.sr_number = @sr_number) a";
}
else
{
query = @"SELECT
ifnull(SUM(dues),0)
FROM
(SELECT
SUM(IFNULL(outstd_amount, 0) - IFNULL(rmt_amount, 0)) dues
FROM
out_standing a, sr_register b
WHERE
IFNULL(outstd_amount, 0) - IFNULL(rmt_amount, 0) != 0
AND session = (SELECT
session
FROM
mst_session
WHERE
session_active = 'Y'
AND session_finalize = 'Y')
AND a.sr_number = b.sr_number
AND b.sr_number = @sr_number UNION ALL SELECT
SUM(IFNULL(outstd_amount, 0) - IFNULL(rmt_amount, 0)) dues
FROM
out_standing a, sr_register b
WHERE
IFNULL(outstd_amount, 0) - IFNULL(rmt_amount, 0) != 0
AND a.session != (SELECT
session
FROM
mst_session
WHERE
session_active = 'Y'
AND session_finalize = 'Y')
AND a.sr_number = b.sr_number
AND b.sr_number = @sr_number) a";
}
decimal dues = con.Query <decimal>(query, new { sr_number = sr_number }).SingleOrDefault();
if (dues != 0)
{
ViewData["message"] = "Sorry task cannot be completed. First clear all the dues upto current month.";
return(View("message"));
}
if (tc_format == "CBSE")
{
ExcelCBSE_TC_form tc = new ExcelCBSE_TC_form();
return(File(tc.Generate_TC(sr_number, Int32.Parse(Request.Cookies["loginUserId"].Value.ToString())), "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", "TC_" + sr_number + ".xlsx"));
}
else
{
ExcelTc_form tc = new ExcelTc_form();
return(File(tc.Generate_TC(sr_number, Int32.Parse(Request.Cookies["loginUserId"].Value.ToString()), Request.Cookies["loginUserFullName"].Value.ToString()), "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", "TC_" + sr_number + ".xlsx"));
}
}
}
