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 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"));
}
}
}
