Sto sviluppando un programma con asp.net MVC e mi sento come se stessi sbagliando da queste parti:
-
Sto popolando tutto il processo nel controller, perché non so come inserirlo nel modello (come chiamarlo). Ho letto alcuni riferimenti e la gente dice che dobbiamo inserire il processo nel modello (?)
-
Molte delle mie azioni hanno lo stesso processo (ripetitivo). Ad esempio, molte viste devono caricare i dati dalla stessa tabella SQL, quindi ripeto il processo per chiamare i dati in molte viste.
Qui fa parte del mio controller, puoi dirmi se sto facendo bene o sbagliato ecc.
public class EmployeeController : Controller
{
private UserContext db = new UserContext();
public async Task<ActionResult> Index()
{
string strquery = "Select employee.EmpId,EmpName,OrgName,EmpJobLvl,JobLvlName,EmpJobTtl,JobTtlName,employee.EmpType,"
+ "EmpDateStart,EmpEmail,EmpHandphone,EmpJoinDate,EmpSignDate,EmpNPWP,EmpResAddr,city1.CityName AS CityName1,EmpResZipCode, "
+ "EmpResPhone,EmpResStatus,EmpOriAddr,city2.CityName AS CityName2,EmpOriZipCode,EmpOriPhone,EmpOriStatus,EmpMaritalSt,EmpSex"
+ " FROM employee "
+ " join Organization (nolock) on employee.emporg = organization.orgcode "
+ " join jobtitle (nolock) on employee.empjobttl = jobtitle.jobttlcode "
+ " join JobLevel (nolock) on employee.EmpJobLvl = JobLevel.JobLvlCode"
+ " join EmpType (nolock) on employee.EmpType = EmpType.EmpType"
+ " INNER JOIN City city1 ON employee.EmpResCity = city1.CityCode"
+ " INNER JOIN City city2 ON employee.EmpOriCity = city2.CityCode"
+ " WHERE employee.EmpId = {0}";
string rtsquery = "Select employee.EmpID,EmpFamRelation,EmpFamName,EmpFamDateBrith,CityName"
+ ",EmpFamSex,EmpFamAlive,MaritalSt,EmpFamMaritalDate,EmploymentSt,EmpFamOccupation "
+ ",EmpFamComp,EmpCompAddr,EmpFamEdu,EmpFamEduIns FROM employee "
+ "join EmpFamilys on employee.EmpId = EmpFamilys.EmpId "
+ "join City on City.CityCode = EmpFamilys.EmpFamCityBirth "
+ "WHERE employee.EmpId = {0}";
string srtquery = "Select employee.EmpID,EmpEduStatus,EmpEduLevel,EmpEduName,EmpEduIns,CityName,EmpEduGraduate"
+ ",EmpEduGrade,EmpEduResult,EmpEduFrontTitle,EmpEduEndTitle FROM employee "
+ "join EmpEducations on employee.EmpId = EmpEducations.EmpId "
+ "join City on City.CityCode = EmpEducations.EmpEduCity "
+ "WHERE employee.EmpId = {0}";
var model = new PersonalDataViewModels();
model.A = db.EmpFamilys.SqlQuery(rtsquery, Convert.ToInt32(@User.Identity.Name)).ToList();
model.X = await db.Employee.SqlQuery(strquery, Convert.ToInt32(@User.Identity.Name)).FirstOrDefaultAsync();
model.C = db.EmpEducations.SqlQuery(srtquery, Convert.ToInt32(@User.Identity.Name)).ToList();
return View(model);
}
public ActionResult Details()
{
string strquery = "Select employee.EmpId,EmpName,OrgName,EmpJobLvl,JobLvlName,EmpJobTtl,JobTtlName,employee.EmpType,"
+ "EmpDateStart,EmpEmail,EmpHandphone,EmpJoinDate,EmpSignDate,EmpNPWP,EmpResAddr,city1.CityName AS CityName1,EmpResZipCode, "
+ "EmpResPhone,EmpResStatus,EmpOriAddr,city2.CityName AS CityName2,EmpOriZipCode,EmpOriPhone,EmpOriStatus,EmpMaritalSt,EmpSex"
+ " FROM employee "
+ " join Organization (nolock) on employee.emporg = organization.orgcode "
+ " join jobtitle (nolock) on employee.empjobttl = jobtitle.jobttlcode "
+ " join JobLevel (nolock) on employee.EmpJobLvl = JobLevel.JobLvlCode"
+ " join EmpType (nolock) on employee.EmpType = EmpType.EmpType"
+ " INNER JOIN City city1 ON employee.EmpResCity = city1.CityCode"
+ " INNER JOIN City city2 ON employee.EmpOriCity = city2.CityCode"
+ " WHERE employee.EmpId = {0}";
string srtquery = "Select employee.EmpID,LvAppId,SubmitDate,LvPeriod,LvDesc,Status,Type,Comment"
+ " FROM employee "
+ "join LeaveApps on employee.EmpId = LeaveApps.EmpId "
+ "WHERE employee.EmpId = {0}";
var model = new LeaveApplicationViewModels();
model.X = db.Employee.SqlQuery(strquery, Convert.ToInt32(@User.Identity.Name)).FirstOrDefault();
model.C = db.LeaveApps.SqlQuery(srtquery, Convert.ToInt32(@User.Identity.Name)).ToList();
foreach (var row in model.C)
{
string wwwquery = "Select LvAppId,LvDate FROM LeaveDate WHERE LvAppId= {0}";
row.D = db.LeaveDate.SqlQuery(wwwquery, row.LvAppId).ToList();
}
/*info*/
string sssquery = "Select LeaveDate.LvAppId,LvDate FROM LeaveDate "
+ " join LeaveApps (nolock) on LeaveDate.LvAppId = LeaveApps.LvAppId "
+ " WHERE LeaveDate.EmpId= {0} AND Status <> {1} AND Type = {2} ";
var used =db.LeaveDate.SqlQuery(sssquery, Convert.ToInt32(@User.Identity.Name), "rejected","Annual").ToList().Count;
string maxquery = "Select Max,LeaveType,ID,TypeDetail FROM LeaveRule WHERE LeaveType='Annual'";
var max = db.LeaveRule.SqlQuery(maxquery).FirstOrDefault().Max;
var rem = max - used;
ViewBag.a = used;
ViewBag.b= rem;
ViewBag.c = max;
/*rule*/
int comp = 0;
var pendingtoogle = model.C.Where(x => x.Status == "pending" && x.Type == "Annual").ToList().Count;
if (used < 12 && pendingtoogle == 0)
{ comp = 1; }
else {comp = 0; }
ViewBag.comp = comp;
return View(model);
}