public ActionResult Top1in3month()
{
DataTable dt = new DataTable();
DataTable dt_main_menu = new DataTable();
mutility.DbName = Settings.Default["DbName"].ToString();
mutility.UserName = Settings.Default["UserName"].ToString();
mutility.Password = Settings.Default["Password"].ToString();
mutility.ServerName = Settings.Default["ServerName"].ToString();
if (mutility.ServerName == "" || mutility.DbName == "" || mutility.UserName == "")
{
return(RedirectToAction("Index", "Setting_Connection/Index"));
}
else
{
if (Session["ID"] != null)
{
UserAccessRightController UsAccRight = new UserAccessRightController();
string userkey = Session["user_key"].ToString();
dt = UsAccRight.UserAccessRight(userkey);
dt_main_menu = UsAccRight.Main_Menu(userkey);
ViewBag.manin_menu = dt_main_menu;
ViewBag.sub_manin = dt;
return(View());
}
else
{
return(RedirectToAction("Index", "Login/Index"));
}
}
}
public ActionResult Index(int?page)
{
DataTable dt = new DataTable();
DataTable dt_main_menu = new DataTable();
DataTable dt_ManageZone = new DataTable();
DataTable table = new DataTable();
mutility.DbName = Settings.Default["DbName"].ToString();
mutility.UserName = Settings.Default["UserName"].ToString();
mutility.Password = Settings.Default["Password"].ToString();
mutility.ServerName = Settings.Default["ServerName"].ToString();
if (mutility.ServerName == "" || mutility.DbName == "" || mutility.UserName == "")
{
return(RedirectToAction("Index", "Setting_Connection/Index"));
}
else
{
if (Session["ID"] != null)
{
UserAccessRightController UsAccRight = new UserAccessRightController();
string userkey = Convert.ToString(Session["user_key"]);
string branchoption = Convert.ToString(Session["branch_option"]);
dt = UsAccRight.UserAccessRight(userkey);
dt_main_menu = UsAccRight.Main_Menu(userkey);
ViewBag.manin_menu = dt_main_menu;
ViewBag.sub_manin = dt;
string BrZone = Convert.ToString(mutility.dbSingleResult("select branch_zone from users where user_key='" + userkey + "'"));
var zone_array = BrZone.Split(',');
string zonetoarray = string.Join("','", zone_array.Skip(0));
string SQl = "";
if (branchoption.Replace(" ", "") == "T")
{
SQl = "select row_number() over(order by BL.BrCode) as id,* ,1 as checked from BRANCH_LISTS BL inner join VPN V ON V.BrCode=BL.BrCode where BL.flag=1 and BL.BrCode in('" + zonetoarray + "')";
}
else
{
SQl = "select row_number() over(order by BL.BrCode) as id,* ,1 as checked from BRANCH_LISTS BL inner join VPN V ON V.BrCode=BL.BrCode where BL.flag=1 and BL.BrCode in('" + zonetoarray + "') union all " +
"select row_number() over(order by BL.BrCode) as id,*,0 as checked from BRANCH_LISTS BL inner join VPN V ON V.BrCode=BL.BrCode where BL.flag=1 and BL.BrCode not in('" + zonetoarray + "')";
}
DataTable dtpagelist = mutility.dbResult(SQl);
list = dtpagelist.AsEnumerable().ToList();
var pageNumber = page ?? 1;
ViewBag.dt_ManageZone = list.ToPagedList(pageNumber, 10);
ViewBag.dt_status = table;
return(View());
}
else
{
return(RedirectToAction("Index", "Login/Index"));
}
}
}
// GET: Reports
public ActionResult Index()
{
DataTable dt = new DataTable();
DataTable dt_main_menu = new DataTable();
DataTable dt_Report = new DataTable();
DataTable dtRun = new DataTable();
mutility.DbName = Settings.Default["DbName"].ToString();
mutility.UserName = Settings.Default["UserName"].ToString();
mutility.Password = Settings.Default["Password"].ToString();
mutility.ServerName = Settings.Default["ServerName"].ToString();
if (mutility.ServerName == "" || mutility.DbName == "" || mutility.UserName == "")
{
return(RedirectToAction("Index", "Setting_Connection/Index"));
}
else
{
if (Session["ID"] != null)
{
UserAccessRightController UsAccRight = new UserAccessRightController();
string userkey = Convert.ToString(Session["user_key"]);
dt = UsAccRight.UserAccessRight(userkey);
dt_main_menu = UsAccRight.Main_Menu(userkey);
ViewBag.manin_menu = dt_main_menu;
ViewBag.sub_manin = dt;
string theString = Convert.ToString(Session["report_code"]);
var array = theString.Split(',');
string firstElem = array.First();
string restOfArray = string.Join("','", array.Skip(0));
string branchZone = Convert.ToString(mutility.dbSingleResult("select branch_zone from users where user_key='S001'"));
var zone_array = branchZone.Split(',');
string zonetoarray = string.Join("','", zone_array.Skip(0));
string Sql = "select * from Table_Reports where flag=1 and Report_Code in('" + restOfArray + "')";
ViewBag.dt_Report = mutility.dbResult(Sql);
string brlist = "select * from BRANCH_LISTS where flag=1 and BrCode in('" + zonetoarray + "');";
ViewBag.dt_ManageZone = mutility.dbResult(brlist);
ViewBag.dtRun = dtRun;
return(View());
}
else
{
return(RedirectToAction("Index", "Login/Index"));
}
}
}
public ActionResult Sumbymoth()
{
DataTable dt = new DataTable();
DataTable dt_main_menu = new DataTable();
DataTable dtwrittenoff = new DataTable();
DataTable dtColumnTitle = new DataTable();
mutility.DbName = Settings.Default["DbName"].ToString();
mutility.UserName = Settings.Default["UserName"].ToString();
mutility.Password = Settings.Default["Password"].ToString();
mutility.ServerName = Settings.Default["ServerName"].ToString();
if (mutility.ServerName == "" || mutility.DbName == "" || mutility.UserName == "")
{
return(RedirectToAction("Index", "Setting_Connection/Index"));
}
else
{
if (Session["ID"] != null)
{
UserAccessRightController UsAccRight = new UserAccessRightController();
string sql = "select row_number() over(order by BrCode) as id,* from dbo.Rep_WrittenOff where reportdate='2019-04-30' order by BrCode ASC";
dtwrittenoff = wo.Write_Off(sql);
string userkey = Session["user_key"].ToString();
dt = UsAccRight.UserAccessRight(userkey);
dt_main_menu = UsAccRight.Main_Menu(userkey);
dtColumnTitle = wo.CulumnTitleWO();
ViewBag.manin_menu = dt_main_menu;
ViewBag.sub_manin = dt;
ViewBag.writtenoff = dtwrittenoff;
ViewBag.ColumnTitle = dtColumnTitle;
ViewBag.CurrRunDate = CurrRunDate.CurrRunDate(userkey);
return(View());
}
else
{
return(RedirectToAction("Index", "Login/Index"));
}
}
}
public ActionResult SearchReportWrittenOffCompare(WriteOff wo)
{
DataTable dt = new DataTable();
DataTable dt_main_menu = new DataTable();
DataTable dtwrittenoff = new DataTable();
DataTable dtColumnTitle = new DataTable();
mutility.DbName = Settings.Default["DbName"].ToString();
mutility.UserName = Settings.Default["UserName"].ToString();
mutility.Password = Settings.Default["Password"].ToString();
mutility.ServerName = Settings.Default["ServerName"].ToString();
if (mutility.ServerName == "" || mutility.DbName == "" || mutility.UserName == "")
{
return(RedirectToAction("Index", "Setting_Connection/Index"));
}
else
{
if (Session["ID"] != null)
{
UserAccessRightController UsAccRight = new UserAccessRightController();
string sql = "select row_number() over(order by BrCode) as id,* from Rep_WrittenOffMBWIN where reportdate='" + String.Format("{0:yyyy-MM-dd}", wo.date_start) + @"' order by BrCode ASC";
dtwrittenoff = wo.Write_Off(sql);
string userkey = Session["user_key"].ToString();
dt = UsAccRight.UserAccessRight(userkey);
dt_main_menu = UsAccRight.Main_Menu(userkey);
ViewBag.manin_menu = dt_main_menu;
ViewBag.sub_manin = dt;
ViewBag.writtenoff = dtwrittenoff;
ViewBag.date_start = wo.date_start;
ViewBag.ColumnTitle = dtColumnTitle;
ViewBag.CurrRunDate = CurrRunDate.CurrRunDate(userkey);
return(View("GlComTool"));
}
else
{
return(RedirectToAction("Index", "Login/Index"));
}
}
}
public ActionResult SearchReportdatePaidOffSummary(PaidOff piad_off)
{
DataTable dt = new DataTable();
DataTable dt_main_menu = new DataTable();
DataTable dtpaidoff = new DataTable();
mutility.DbName = Settings.Default["DbName"].ToString();
mutility.UserName = Settings.Default["UserName"].ToString();
mutility.Password = Settings.Default["Password"].ToString();
mutility.ServerName = Settings.Default["ServerName"].ToString();
if (mutility.ServerName == "" || mutility.DbName == "" || mutility.UserName == "")
{
return(RedirectToAction("Index", "Setting_Connection/Index"));
}
else
{
if (Session["ID"] != null)
{
UserAccessRightController UsAccRight = new UserAccessRightController();
string userkey = Session["user_key"].ToString();
dt = UsAccRight.UserAccessRight(userkey);
dt_main_menu = UsAccRight.Main_Menu(userkey);
ViewBag.manin_menu = dt_main_menu;
ViewBag.sub_manin = dt;
string concat = "brcode>500 ";
dtpaidoff = paid_off.paidoff(concat);
ViewBag.paidoff = dtpaidoff;
ViewBag.date_start = paid_off.date_start;
return(View());
}
else
{
return(RedirectToAction("Index", "Login/Index"));
}
}
}
public ActionResult SearchReportDateRM(ClientFrequency CF)
{
DataTable dt = new DataTable();
DataTable dt_main_menu = new DataTable();
DataTable dtclientfre = new DataTable();
mutility.DbName = Settings.Default["DbName"].ToString();
mutility.UserName = Settings.Default["UserName"].ToString();
mutility.Password = Settings.Default["Password"].ToString();
mutility.ServerName = Settings.Default["ServerName"].ToString();
if (mutility.ServerName == "" || mutility.DbName == "" || mutility.UserName == "")
{
return(RedirectToAction("Index", "Setting_Connection/Index"));
}
else
{
if (Session["ID"] != null)
{
UserAccessRightController UsAccRight = new UserAccessRightController();
string sql = "select * from Rep_ClientFrequency";
dtclientfre = CF.Client_Freq(sql);
string userkey = Session["user_key"].ToString();
dt = UsAccRight.UserAccessRight(userkey);
dt_main_menu = UsAccRight.Main_Menu(userkey);
ViewBag.manin_menu = dt_main_menu;
ViewBag.sub_manin = dt;
ViewBag.clientfre = dtclientfre;
ViewBag.date_start = CF.date_start;
return(View("OSFreqByRM"));
}
else
{
return(RedirectToAction("Index", "Login/Index"));
}
}
}
//Daily collection
public ActionResult DailyCollection()
{
DataTable dt = new DataTable();
DataTable dt_main_menu = new DataTable();
DataTable dtwrittenoff = new DataTable();
mutility.DbName = Settings.Default["DbName"].ToString();
mutility.UserName = Settings.Default["UserName"].ToString();
mutility.Password = Settings.Default["Password"].ToString();
mutility.ServerName = Settings.Default["ServerName"].ToString();
if (mutility.ServerName == "" || mutility.DbName == "" || mutility.UserName == "")
{
return(RedirectToAction("Index", "Setting_Connection/Index"));
}
else
{
if (Session["ID"] != null)
{
UserAccessRightController UsAccRight = new UserAccessRightController();
string userkey = Session["user_key"].ToString();
dt = UsAccRight.UserAccessRight(userkey);
dt_main_menu = UsAccRight.Main_Menu(userkey);
ViewBag.manin_menu = dt_main_menu;
ViewBag.sub_manin = dt;
ViewBag.dt_ManageZone = this.brlist(userkey);
ViewBag.CurrRunDate = CurrRunDate.CurrRunDate(userkey);
ViewBag.Data = this.dtDaily();
return(View());
}
else
{
return(RedirectToAction("Index", "Login/Index"));
}
}
}
public ActionResult Index(Reports rs)
{
DataTable dt = new DataTable();
DataTable dt_main_menu = new DataTable();
DataTable dtRun = new DataTable();
DataTable dt_Report = new DataTable();
mutility.DbName = Settings.Default["DbName"].ToString();
mutility.UserName = Settings.Default["UserName"].ToString();
mutility.Password = Settings.Default["Password"].ToString();
mutility.ServerName = Settings.Default["ServerName"].ToString();
if (mutility.ServerName == "" || mutility.DbName == "" || mutility.UserName == "")
{
return(RedirectToAction("Index", "Setting_Connection/Index"));
}
else
{
if (Session["ID"] != null)
{
UserAccessRightController UsAccRight = new UserAccessRightController();
string userkey = Convert.ToString(Session["user_key"]);
dt = UsAccRight.UserAccessRight(userkey);
dt_main_menu = UsAccRight.Main_Menu(userkey);
ViewBag.manin_menu = dt_main_menu;
ViewBag.sub_manin = dt;
string theString = Convert.ToString(Session["report_code"]);
var array = theString.Split(',');
string firstElem = array.First();
string restOfArray = string.Join("','", array.Skip(0));
string branchZone = Convert.ToString(Session["branch_zone"]);
var zone_array = branchZone.Split(',');
string zonetoarray = string.Join("','", zone_array.Skip(0));
string Sql = "select * from Table_Reports where Report_Code in('" + restOfArray + "')";
ViewBag.dt_Report = mutility.dbResult(Sql);
string brlist = "select * from BRANCH_LISTS where flag=1 and BrCode in('" + zonetoarray + "');";
ViewBag.dt_ManageZone = mutility.dbResult(brlist);
string dtExcute = "";
DataTable dtAffterEx = new DataTable();
string ReExcuteName = "select StrSql from Table_Reports where Report_Code='" + rs.ReportCode.Trim() + "'";
dtExcute = Convert.ToString(mutility.dbSingleResult(ReExcuteName));
string Run = dtExcute;
ViewBag.dtRun = mutility.dbResult(Run);
ExcelPackage pck = new ExcelPackage();
ExcelWorksheet ws = pck.Workbook.Worksheets.Add("Report");
ws.Cells["A1"].Value = "Communication";
ws.Cells["B1"].Value = "Com1";
ws.Cells["A2"].Value = "Report";
ws.Cells["B2"].Value = "Report1";
ws.Cells["A3"].Value = "Date";
ws.Cells["B3"].Value = string.Format("{0:dd MMMM yyyy} at {0:H: mm tt}", DateTimeOffset.Now);
ws.Cells["A6"].Value = "EmployeeId";
ws.Cells["B6"].Value = "EmployeeName";
ws.Cells["C6"].Value = "Email";
ws.Cells["D6"].Value = "Phone";
ws.Cells["E6"].Value = "Experience";
int rowStart = 7;
foreach (DataRow item in dtRun.Rows)
{
for (int i = 0; i <= 10; i++)
{
ws.Cells[string.Format("A{0}", rowStart)].Value = item[i].ToString();
ws.Cells[string.Format("B{0}", rowStart)].Value = item[i].ToString();
ws.Cells[string.Format("C{0}", rowStart)].Value = item[i].ToString();
ws.Cells[string.Format("D{0}", rowStart)].Value = item[i].ToString();
ws.Cells[string.Format("E{0}", rowStart)].Value = item[i].ToString();
}
rowStart++;
}
ws.Cells["A:AZ"].AutoFitColumns();
Response.Clear();
Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
Response.AddHeader("content-disposition", "attachment: filename=" + "ExcelReport.xlsx");
Response.BinaryWrite(pck.GetAsByteArray());
Response.End();
return(View());
}
else
{
return(RedirectToAction("Index", "Login/Index"));
}
}
}
public ActionResult ExecuteScript(Reports rs)
{
DataTable dt = new DataTable();
DataTable dt_main_menu = new DataTable();
DataTable dt_Report = new DataTable();
DataTable dtRun = new DataTable();
mutility.DbName = Settings.Default["DbName"].ToString();
mutility.UserName = Settings.Default["UserName"].ToString();
mutility.Password = Settings.Default["Password"].ToString();
mutility.ServerName = Settings.Default["ServerName"].ToString();
if (mutility.ServerName == "" || mutility.DbName == "" || mutility.UserName == "")
{
return(RedirectToAction("Index", "Setting_Connection/Index"));
}
else
{
if (Session["ID"] != null)
{
UserAccessRightController UsAccRight = new UserAccessRightController();
string userkey = Convert.ToString(Session["user_key"]);
dt = UsAccRight.UserAccessRight(userkey);
dt_main_menu = UsAccRight.Main_Menu(userkey);
ViewBag.manin_menu = dt_main_menu;
ViewBag.sub_manin = dt;
string theString = Convert.ToString(Session["report_code"]);
var array = theString.Split(',');
string firstElem = array.First();
string restOfArray = string.Join("','", array.Skip(0));
string Sql = "select replace(Report_Code,' ','') as Report_Code, Name from Table_Reports where flag=1 and Report_Code in('" + restOfArray + "')";
ViewBag.dt_Report = mutility.dbResult(Sql);
string branchZone = Convert.ToString(mutility.dbSingleResult("select branch_zone from users where user_key='S001'"));
var zone_array = branchZone.Split(',');
string zonetoarray = string.Join("','", zone_array.Skip(0));
string brlist = "select * from BRANCH_LISTS where flag=1 and BrCode in('" + zonetoarray + "');";
ViewBag.dt_ManageZone = mutility.dbResult(brlist);
string dtExcute = "";
DataTable dtAffterEx = new DataTable();
string ReExcuteName = "select StrSql from Table_Reports where Report_Code='" + rs.ReportCode.Trim() + "'";
dtExcute = Convert.ToString(mutility.dbSingleResult(ReExcuteName));
string Run = dtExcute;
//Get ReportName
string ValueString = "select Name from Table_Reports where Report_Code = '" + rs.ReportCode.Replace(" ", "") + "'";
string ReportName = Convert.ToString(mutility.dbSingleResult(ValueString));
//Get Branch Name
string BrName = Convert.ToString(mutility.dbSingleResult("select BrLetter from BRANCH_LISTS where flag=1 and BrCode='" + rs.BrCode + "'"));
//Selected data
ViewBag.BrCode = rs.BrCode;
ViewBag.ReportCode = rs.ReportCode.Replace(" ", "");
DataTable dtstatus = new DataTable();
string BrZone_loop = Convert.ToString(mutility.dbSingleResult("select branch_zone from users where user_key='S001'"));
var zone_array_loop = BrZone_loop.Split(',');
string zonetoarray_loop = string.Join("','", zone_array_loop.Skip(0));
string SQl_loop = "";
if (rs.BrCode == "ALL")
{
SQl_loop = "select row_number() over(order by BL.BrCode) as id,* ,1 as checked ,'0' as statusconnect from BRANCH_LISTS BL inner join VPN V ON V.BrCode=BL.BrCode where BL.flag=1 and BL.BrCode in('" + zonetoarray_loop + "')";
}
else
{
SQl_loop = "select row_number() over(order by BL.BrCode) as id,* ,1 as checked ,'0' as statusconnect from BRANCH_LISTS BL inner join VPN V ON V.BrCode=BL.BrCode where BL.flag=1 and BL.BrCode='" + rs.BrCode + "'"; //BL.BrCode in('" + zonetoarray_loop + "')
}
dtstatus = mutility.dbResult(SQl_loop);
SqlConnection sqlcon = new SqlConnection();
DataSet ds = new DataSet();
DataTable table = new DataTable();
foreach (DataRow dr in dtstatus.Rows)
{
sqlcon = mutility.BrConnection(dr["FullDbName"].ToString(), "sa", "Sa@#$Mbwin", dr["VPN"].ToString());
if (sqlcon.State != ConnectionState.Open)
{
try
{
dtRun = mutility.dbBranchResult(Run, sqlcon);
ds.Tables.Add(dtRun);
}
catch (Exception) {
}
}
}
DataTable publicdt = new DataTable();
for (int i = 0; i < ds.Tables.Count; i++)
{
publicdt.Merge(ds.Tables[i]);
}
if (rs.BrCode == "ALL")
{
BrName = "All_Branch";
}
ExportDataToExcel(publicdt, BrName + "_" + ReportName);
TempData["sms"] = "You are already donwload reports";
return(View("Index"));
}
else
{
return(RedirectToAction("Index", "Login/Index"));
}
}
}
public ActionResult ExportExcel(string reportcode)
{
DataTable dt = new DataTable();
DataTable dt_main_menu = new DataTable();
DataTable dtRun = new DataTable();
DataTable dt_Report = new DataTable();
mutility.DbName = Settings.Default["DbName"].ToString();
mutility.UserName = Settings.Default["UserName"].ToString();
mutility.Password = Settings.Default["Password"].ToString();
mutility.ServerName = Settings.Default["ServerName"].ToString();
if (mutility.ServerName == "" || mutility.DbName == "" || mutility.UserName == "")
{
return(RedirectToAction("Index", "Setting_Connection/Index"));
}
else
{
if (Session["ID"] != null)
{
UserAccessRightController UsAccRight = new UserAccessRightController();
string userkey = Convert.ToString(Session["user_key"]);
dt = UsAccRight.UserAccessRight(userkey);
dt_main_menu = UsAccRight.Main_Menu(userkey);
ViewBag.manin_menu = dt_main_menu;
ViewBag.sub_manin = dt;
string theString = Convert.ToString(Session["report_code"]);
var array = theString.Split(',');
string firstElem = array.First();
string restOfArray = string.Join("','", array.Skip(0));
string branchZone = Convert.ToString(mutility.dbSingleResult("select branch_zone from users where user_key='S001'"));
var zone_array = branchZone.Split(',');
string zonetoarray = string.Join("','", zone_array.Skip(0));
string Sql = "select * from Table_Reports where Report_Code in('" + restOfArray + "')";
ViewBag.dt_Report = mutility.dbResult(Sql);
string brlist = "select * from BRANCH_LISTS where flag=1 and BrCode in('" + zonetoarray + "');";
ViewBag.dt_ManageZone = mutility.dbResult(brlist);
string dtExcute = "";
DataTable dtAffterEx = new DataTable();
string ReExcuteName = "select StrSql from Table_Reports where Report_Code='" + reportcode + "'";
dtExcute = Convert.ToString(mutility.dbSingleResult(ReExcuteName));
string Run = dtExcute;
dtRun = mutility.dbResult(Run);
ViewBag.dtRun = dtRun;
XLWorkbook wb = new XLWorkbook();
wb.Worksheets.Add(dtRun, "Sheet1");
Response.Clear();
Response.Buffer = true;
Response.Charset = "";
Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
Response.AddHeader("content-disposition", "attachment: filename=" + "ExcelReport.xlsx");
using (MemoryStream MyMemoryStream = new MemoryStream())
{
wb.SaveAs(MyMemoryStream);
MyMemoryStream.WriteTo(Response.OutputStream);
Response.Flush();
Response.End();
}
Response.End();
return(View("index"));
}
else
{
return(RedirectToAction("Index", "Login/Index"));
}
}
}