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