public ActionResult Create([Bind(Include = "CONO,SDATE,COSTAT,FILETYPE,FILELOC,SEALLOC,EDATE,ROWNO,STAMP")] CSCOSTAT cSCOSTAT)
{
if (ModelState.IsValid)
{
int lastRowNo = 0;
try
{
lastRowNo = db.CSCOSTATs.Where(m => m.CONO == cSCOSTAT.CONO).Max(n => n.ROWNO);
}
catch (Exception e) { lastRowNo = -1; }
finally { };
cSCOSTAT.STAMP = 1;
cSCOSTAT.EDATE = cSCOSTAT.SDATE.AddDays(30000);
cSCOSTAT.ROWNO = lastRowNo + 1;
db.CSCOSTATs.Add(cSCOSTAT);
UpdatePreviousRow(cSCOSTAT);
db.SaveChanges();
//return RedirectToAction("Details","CSCOMSTRs", new { id = MyHtmlHelpers.ConvertIdToByteStr(cSCOSTAT.CONO) });
return(new RedirectResult(Url.Action("Details", "CSCOMSTRs", new { id = MyHtmlHelpers.ConvertIdToByteStr(cSCOSTAT.CONO) }) + "#Status"));
}
return(View(cSCOSTAT));
}
public ActionResult EditFileLoc(CSCOSTAT cSCOSTAT)
{
CSSTAT csStat = db.CSSTATs.Find(cSCOSTAT.COSTAT);
//ModelState.Clear(); //apparent this has side-effects and using Remove is preferrable
ModelState.Remove("FILETYPE");
ModelState.Remove("FILELOC");
ModelState.Remove("SEALLOC");
cSCOSTAT.FILETYPE = csStat.FILENOPFIX;
if ((csStat.FILENOFR == 0) && (csStat.FILENOTO == 0) && (csStat.SEALNOFR == 0) && (csStat.SEALNOTO == 0))
{
cSCOSTAT.FILELOC = ""; return(PartialView("Partial/EditNoFileSeal", cSCOSTAT));
}
;
if ((csStat.FILENOFR == 0) && (csStat.FILENOTO == 0))
{
cSCOSTAT.FILELOC = ""; return(PartialView("Partial/EditSealOnly", cSCOSTAT));
}
;
if ((csStat.SEALNOFR == 0) && (csStat.SEALNOTO == 0))
{
cSCOSTAT.SEALLOC = ""; return(PartialView("Partial/EditFileOnly", cSCOSTAT));
}
;
//db.CSCOSTATs.Attach(cSCOSTAT);
//EntityState st = db.Entry(cSCOSTAT).State;
//db.Entry(cSCOSTAT).State = EntityState.Modified;
return(PartialView("Partial/EditFileLoc", cSCOSTAT));
}
public ActionResult SearchPost(CSCOSTAT cSCOSTAT)
{
Session["SearchCOSTATRec"] = cSCOSTAT;
Session["SearchCOSTATSort"] = Request.Params["SORTBY"] ?? "SDATE";
return(Redirect("?page=1"));
//return Index(1);
}
public ActionResult DeleteConfirmed(string id, int row)
{
CSCOSTAT cSCOSTAT = db.CSCOSTATs.Find(MyHtmlHelpers.ConvertByteStrToId(id), row);
db.CSCOSTATs.Remove(cSCOSTAT);
db.SaveChanges();
// return RedirectToAction("Details", "CSCOMSTRs", new { id = MyHtmlHelpers.ConvertIdToByteStr(cSCOSTAT.CONO) });
return(new RedirectResult(Url.Action("Details", "CSCOMSTRs", new { id = MyHtmlHelpers.ConvertIdToByteStr(cSCOSTAT.CONO) }) + "#Status"));
}
// GET: CSCOSTATs/Create
public ActionResult Create(string id)
{
ViewBag.Parent = MyHtmlHelpers.ConvertByteStrToId(id);
CSCOSTAT curRec = new CSCOSTAT();
curRec.CONO = ViewBag.Parent;
curRec.SDATE = DateTime.Now;
ViewBag.COSTAT = new SelectList(db.CSSTATs, "COSTAT", "COSTAT", curRec.COSTAT);
return(View(curRec));
}
private void UpdatePreviousRow(CSCOSTAT cSCOSTAT)
{
CSCOSTAT curRec = db.CSCOSTATs.Where(m => m.CONO == cSCOSTAT.CONO && m.ROWNO < cSCOSTAT.ROWNO).OrderByDescending(n => n.ROWNO).FirstOrDefault();
if (curRec != null)
{
System.DateTime lastDate = (cSCOSTAT.SDATE).AddDays(-1);
curRec.EDATE = lastDate;
curRec.STAMP = curRec.STAMP + 1;
db.Entry(curRec).State = EntityState.Modified;
}
}
public ActionResult Create1(int?page)
{
CSCOSTAT curRec = new CSCOSTAT();
curRec.SDATE = DateTime.Now;
Session["CSCOSTATPage"] = page ?? 1;
ViewBag.CONO = new SelectList(db.CSCOMSTRs.Select(x => new { CONO = x.CONO, CONAME = x.CONAME + " (" + x.CONO + ")" }).OrderBy(y => y.CONAME), "CONO", "CONAME");
ViewBag.COSTAT = new SelectList(db.CSSTATs, "COSTAT", "COSTAT", curRec.COSTAT);
ViewBag.page = page ?? 1;
ViewBag.Title = "Add Company Change Status";
curRec.CSCOMSTR = db.CSCOMSTRs.Find(curRec.CONO);
return(View("Edit1", curRec));
}
// GET: CSCOSTATs/Details/5
public ActionResult Details(string id, int row)
{
if (id == null)
{
return(new HttpStatusCodeResult(HttpStatusCode.BadRequest));
}
CSCOSTAT cSCOSTAT = db.CSCOSTATs.Find(MyHtmlHelpers.ConvertByteStrToId(id), row);
if (cSCOSTAT == null)
{
return(HttpNotFound());
}
return(View(cSCOSTAT));
}
// GET: CSCOSTATs/Edit/5
public ActionResult Edit(string id, int row)
{
if (id == null)
{
return(new HttpStatusCodeResult(HttpStatusCode.BadRequest));
}
CSCOSTAT cSCOSTAT = db.CSCOSTATs.Find(MyHtmlHelpers.ConvertByteStrToId(id), row);
if (cSCOSTAT == null)
{
return(HttpNotFound());
}
;
ViewBag.COSTAT = new SelectList(db.CSSTATs, "COSTAT", "COSTAT", cSCOSTAT.COSTAT);
return(View(cSCOSTAT));
}
private bool UpdateCompany(CSCOSTAT cSCOSTAT)
{
bool result = false;
CSCOMSTR cSCOMSTR = db.CSCOMSTRs.Find(cSCOSTAT.CONO);
if (cSCOMSTR != null)
{
cSCOMSTR.COSTAT = cSCOSTAT.COSTAT;
cSCOMSTR.FILELOC = cSCOSTAT.FILELOC;
cSCOMSTR.FILETYPE = cSCOSTAT.FILETYPE;
cSCOMSTR.COSTATD = cSCOSTAT.SDATE;
cSCOMSTR.SEALLOC = cSCOSTAT.SEALLOC;
cSCOMSTR.STAMP = cSCOMSTR.STAMP + 1;
db.Entry(cSCOMSTR).State = EntityState.Modified;
result = true;
}
return(result);
}
public ActionResult Delete1(string id, int row, int?page)
{
if (id == null)
{
return(new HttpStatusCodeResult(HttpStatusCode.BadRequest));
}
CSCOSTAT cSCOSTAT = db.CSCOSTATs.Find(MyHtmlHelpers.ConvertByteStrToId(id), row);
if (cSCOSTAT == null)
{
return(HttpNotFound());
}
Session["CSCOSTATPage"] = page ?? 1;
Session["CSCOSTAT_OLDCONO"] = cSCOSTAT.CONO; // storing the old no so that can allow change of cono
ViewBag.CONO = new SelectList(db.CSCOMSTRs.Select(x => new { CONO = x.CONO, CONAME = x.CONAME + " (" + x.CONO + ")" }).OrderBy(y => y.CONAME), "CONO", "CONAME", cSCOSTAT.CONO);
ViewBag.COSTAT = new SelectList(db.CSSTATs, "COSTAT", "COSTAT", cSCOSTAT.COSTAT);
ViewBag.page = page ?? 1;
ViewBag.Title = "Delete Company Change Status";
return(View("Edit1", cSCOSTAT));
}
public ActionResult Edit([Bind(Include = "CONO,SDATE,COSTAT,FILETYPE,FILELOC,SEALLOC,EDATE,ROWNO,STAMP")] CSCOSTAT cSCOSTAT)
{
if (ModelState.IsValid)
{
ASIDBConnection newdb = new ASIDBConnection();
try
{
CSCOSTAT curRec = newdb.CSCOSTATs.Find(cSCOSTAT.CONO, cSCOSTAT.ROWNO);
if (curRec.STAMP == cSCOSTAT.STAMP)
{
cSCOSTAT.STAMP = cSCOSTAT.STAMP + 1;
UpdatePreviousRow(cSCOSTAT);
db.Entry(cSCOSTAT).State = EntityState.Modified;
db.SaveChanges();
// return RedirectToAction("Details", "CSCOMSTRs", new { id = MyHtmlHelpers.ConvertIdToByteStr(cSCOSTAT.CONO) });
return(new RedirectResult(Url.Action("Details", "CSCOMSTRs", new { id = MyHtmlHelpers.ConvertIdToByteStr(cSCOSTAT.CONO) }) + "#Status"));
}
else
{
ModelState.AddModelError(string.Empty, "Record is modified");
}
}
catch (Exception e)
{
ModelState.AddModelError(string.Empty, e.Message);
}
finally
{
newdb.Dispose();
}
}
ViewBag.COSTAT = new SelectList(db.CSSTATs, "COSTAT", "COSTAT", cSCOSTAT.COSTAT);
return(View(cSCOSTAT));
}
public ActionResult DeleteConfirmed(string id, int row)
{
CSCOSTAT cSCOSTAT = db.CSCOSTATs.Find(MyHtmlHelpers.ConvertByteStrToId(id), row);
CSCOSTAT curRec = UpdatePreviousRow(cSCOSTAT);
if (curRec != null)
{
try
{
if (db.CSCOSTATs.Where(x => x.CONO == cSCOSTAT.CONO).OrderByDescending(x => x.ROWNO).Select(x => x.ROWNO).FirstOrDefault() == cSCOSTAT.ROWNO)
{
UpdateCompany(curRec);
}
db.CSCOSTATs.Remove(cSCOSTAT);
db.SaveChanges();
// return RedirectToAction("Details", "CSCOMSTRs", new { id = MyHtmlHelpers.ConvertIdToByteStr(cSCOSTAT.CONO) });
return(new RedirectResult(Url.Action("Edit", "CSCOMSTRs", new { id = MyHtmlHelpers.ConvertIdToByteStr(cSCOSTAT.CONO) }) + "#Status"));
}
catch (DbEntityValidationException dbEx)
{
foreach (var validationErrors in dbEx.EntityValidationErrors)
{
foreach (var validationError in validationErrors.ValidationErrors)
{
//string message = string.Format("{0}:{1}",
// validationErrors.Entry.Entity.ToString(),
// validationError.ErrorMessage);
// raise a new exception nesting
// the current instance as InnerException
ModelState.AddModelError(validationError.PropertyName, validationError.ErrorMessage);
}
}
}
catch (DbUpdateException ex)
{
UpdateException updateException = (UpdateException)ex.InnerException;
if (updateException != null)
{
if (updateException.InnerException != null)
{
var sqlException = (FirebirdSql.Data.FirebirdClient.FbException)updateException.InnerException;
foreach (var error in sqlException.Errors)
{
if (error.Message != null)
{
ModelState.AddModelError(string.Empty, error.Message);
}
}
}
else
{
ModelState.AddModelError(string.Empty, updateException.Message);
}
}
else
{
ModelState.AddModelError(string.Empty, updateException.Message);
}
}
}
else
{
ModelState.AddModelError(string.Empty, "Cannot delete the only status record");
}
ViewBag.COSTAT = new SelectList(db.CSSTATs, "COSTAT", "COSTAT", cSCOSTAT.COSTAT);
ViewBag.Title = "Delete Company Change Status";
cSCOSTAT.CSCOMSTR = db.CSCOMSTRs.Find(cSCOSTAT.CONO);
return(View(cSCOSTAT));
}
public PartialViewResult Search()
{
CSCOSTAT searchRec = null;
//searchRec.CONO = pSearchCode;
//searchRec.CONAME = pSearchName;
if (Session["SearchCOSTATRec"] != null)
{
searchRec = (CSCOSTAT)Session["SearchCOSTATRec"];
}
else
{
searchRec = new CSCOSTAT();
searchRec.SDATE = new DateTime(DateTime.Today.Year, DateTime.Today.Month, 1);
searchRec.EDATE = searchRec.SDATE.AddMonths(1).AddDays(-1);
}
if (Session["SearchCOSTATSort"] == null)
{
Session["SearchCOSTATSort"] = "COSTAT";
}
;
List <SelectListItem> listItems = new List <SelectListItem>();
listItems.Add(new SelectListItem
{
Text = "Company Name",
Value = "CONAME",
Selected = (string)Session["SearchCOSTATSort"] == "CONAME"
});
listItems.Add(new SelectListItem
{
Text = "Status",
Value = "COSTAT",
Selected = (string)Session["SearchCOSTATSort"] == "COSTAT"
});
listItems.Add(new SelectListItem
{
Text = "Date",
Value = "SDATE",
Selected = (string)Session["SearchCOSTATSort"] == "SDATE"
});
listItems.Add(new SelectListItem
{
Text = "Date Latest",
Value = "SDATELAST",
Selected = (string)Session["SearchCOSTATSort"] == "SDATELAST"
});
listItems.Add(new SelectListItem
{
Text = "Staff",
Value = "STAFFCODE",
Selected = (string)Session["SearchCOSTATSort"] == "STAFFCODE"
});
listItems.Add(new SelectListItem
{
Text = "Archive",
Value = "ARCHIVE",
Selected = (string)Session["SearchCOSTATSort"] == "ARCHIVE"
});
ViewBag.SORTBY = listItems;
if (Session["HKSTAFFDB"] == null)
{
ViewBag.STAFFCODE = new SelectList(db.HKSTAFFs.Select(x => new { STAFFCODE = x.STAFFCODE, STAFFDESC = x.STAFFDESC + " (" + x.STAFFCODE + ")" }).OrderBy(x => x.STAFFDESC), "STAFFCODE", "STAFFDESC");
}
else
{
ViewBag.STAFFCODE = new SelectList(((IEnumerable <HKSTAFF>)Session["HKSTAFFDB"]).Select(x => new { STAFFCODE = x.STAFFCODE, STAFFDESC = x.STAFFDESC + " (" + x.STAFFCODE + ")" }).OrderBy(x => x.STAFFDESC), "STAFFCODE", "STAFFDESC");
}
ViewBag.COSTAT = new SelectList(db.CSSTATs, "COSTAT", "COSTAT");
return(PartialView("Partial/Search", searchRec));
}
public ActionResult Create([Bind(Include = "CONO,SDATE,COSTAT,FILETYPE,FILELOC,SEALLOC,EDATE,ROWNO,STAMP")] CSCOSTAT cSCOSTAT)
{
if (ModelState.IsValid)
{
int lastRowNo = 0;
try
{
lastRowNo = db.CSCOSTATs.Where(m => m.CONO == cSCOSTAT.CONO).Max(n => n.ROWNO);
}
catch (Exception e) { lastRowNo = 0; }
finally { };
try
{
cSCOSTAT.STAMP = 0;
cSCOSTAT.EDATE = new DateTime(3000, 1, 1);
cSCOSTAT.ROWNO = lastRowNo + 1;
db.CSCOSTATs.Add(cSCOSTAT);
UpdateCompany(cSCOSTAT);
UpdatePreviousRow(cSCOSTAT);
db.SaveChanges();
//return RedirectToAction("Details","CSCOMSTRs", new { id = MyHtmlHelpers.ConvertIdToByteStr(cSCOSTAT.CONO) });
return(new RedirectResult(Url.Action("Edit", "CSCOMSTRs", new { id = MyHtmlHelpers.ConvertIdToByteStr(cSCOSTAT.CONO) }) + "#Status"));
}
catch (DbEntityValidationException dbEx)
{
foreach (var validationErrors in dbEx.EntityValidationErrors)
{
foreach (var validationError in validationErrors.ValidationErrors)
{
//string message = string.Format("{0}:{1}",
// validationErrors.Entry.Entity.ToString(),
// validationError.ErrorMessage);
// raise a new exception nesting
// the current instance as InnerException
ModelState.AddModelError(validationError.PropertyName, validationError.ErrorMessage);
}
}
}
catch (DbUpdateException ex)
{
UpdateException updateException = (UpdateException)ex.InnerException;
if (updateException != null)
{
if (updateException.InnerException != null)
{
var sqlException = (FirebirdSql.Data.FirebirdClient.FbException)updateException.InnerException;
foreach (var error in sqlException.Errors)
{
if (error.Message != null)
{
ModelState.AddModelError(string.Empty, error.Message);
}
}
}
else
{
ModelState.AddModelError(string.Empty, updateException.Message);
}
}
else
{
ModelState.AddModelError(string.Empty, updateException.Message);
}
}
}
cSCOSTAT.CSCOMSTR = db.CSCOMSTRs.Find(cSCOSTAT.CONO);
return(View(cSCOSTAT));
}
public ActionResult Create1([Bind(Include = "CONO,SDATE,COSTAT,FILETYPE,FILELOC,SEALLOC,EDATE,ROWNO,STAMP")] CSCOSTAT cSCOSTAT)
{
int page = (int)Session["CSCOSTATPage"];
if (ModelState.IsValid)
{
int lastRowNo = 0;
try
{
lastRowNo = db.CSCOSTATs.Where(m => m.CONO == cSCOSTAT.CONO).Max(n => n.ROWNO);
}
catch (Exception e) { lastRowNo = -1; }
finally { };
try
{
cSCOSTAT.STAMP = 0;
cSCOSTAT.EDATE = new DateTime(3000, 1, 1);
cSCOSTAT.ROWNO = lastRowNo + 1;
db.CSCOSTATs.Add(cSCOSTAT);
UpdateCompany(cSCOSTAT);
UpdatePreviousRow(cSCOSTAT);
db.SaveChanges();
return(RedirectToAction("Index", new { page = page }));
}
catch (DbEntityValidationException dbEx)
{
foreach (var validationErrors in dbEx.EntityValidationErrors)
{
foreach (var validationError in validationErrors.ValidationErrors)
{
//string message = string.Format("{0}:{1}",
// validationErrors.Entry.Entity.ToString(),
// validationError.ErrorMessage);
// raise a new exception nesting
// the current instance as InnerException
ModelState.AddModelError(validationError.PropertyName, validationError.ErrorMessage);
}
}
}
catch (DbUpdateException ex)
{
UpdateException updateException = (UpdateException)ex.InnerException;
if (updateException != null)
{
if (updateException.InnerException != null)
{
var sqlException = (FirebirdSql.Data.FirebirdClient.FbException)updateException.InnerException;
foreach (var error in sqlException.Errors)
{
if (error.Message != null)
{
ModelState.AddModelError(string.Empty, error.Message);
}
}
}
else
{
ModelState.AddModelError(string.Empty, updateException.Message);
}
}
else
{
ModelState.AddModelError(string.Empty, updateException.Message);
}
}
}
ViewBag.CONO = new SelectList(db.CSCOMSTRs.Select(x => new { CONO = x.CONO, CONAME = x.CONAME + " (" + x.CONO + ")" }).OrderBy(y => y.CONAME), "CONO", "CONAME");
ViewBag.COSTAT = new SelectList(db.CSSTATs, "COSTAT", "COSTAT", cSCOSTAT.COSTAT);
ViewBag.page = page;
ViewBag.Title = "Add Company Change Status";
cSCOSTAT.CSCOMSTR = db.CSCOMSTRs.Find(cSCOSTAT.CONO);
return(View(cSCOSTAT));
}
public ActionResult Edit([Bind(Include = "CONO,SDATE,COSTAT,FILETYPE,FILELOC,SEALLOC,EDATE,ROWNO,STAMP")] CSCOSTAT cSCOSTAT)
{
if (ModelState.IsValid)
{
ASIDBConnection newdb = new ASIDBConnection();
try
{
CSCOSTAT curRec = newdb.CSCOSTATs.Find(cSCOSTAT.CONO, cSCOSTAT.ROWNO);
if (curRec.STAMP == cSCOSTAT.STAMP)
{
cSCOSTAT.STAMP = cSCOSTAT.STAMP + 1;
// only update the latest record
if (db.CSCOSTATs.Where(x => x.CONO == cSCOSTAT.CONO).OrderByDescending(x => x.ROWNO).Select(x => x.ROWNO).FirstOrDefault() == cSCOSTAT.ROWNO)
{
UpdateCompany(cSCOSTAT);
}
UpdatePreviousRow(cSCOSTAT);
db.Entry(cSCOSTAT).State = EntityState.Modified;
db.SaveChanges();
// return RedirectToAction("Details", "CSCOMSTRs", new { id = MyHtmlHelpers.ConvertIdToByteStr(cSCOSTAT.CONO) });
return(new RedirectResult(Url.Action("Edit", "CSCOMSTRs", new { id = MyHtmlHelpers.ConvertIdToByteStr(cSCOSTAT.CONO) }) + "#Status"));
}
else
{
ModelState.AddModelError(string.Empty, "Record is modified");
}
}
catch (DbEntityValidationException dbEx)
{
foreach (var validationErrors in dbEx.EntityValidationErrors)
{
foreach (var validationError in validationErrors.ValidationErrors)
{
//string message = string.Format("{0}:{1}",
// validationErrors.Entry.Entity.ToString(),
// validationError.ErrorMessage);
// raise a new exception nesting
// the current instance as InnerException
ModelState.AddModelError(validationError.PropertyName, validationError.ErrorMessage);
}
}
}
catch (DbUpdateException ex)
{
UpdateException updateException = (UpdateException)ex.InnerException;
if (updateException != null)
{
if (updateException.InnerException != null)
{
var sqlException = (FirebirdSql.Data.FirebirdClient.FbException)updateException.InnerException;
foreach (var error in sqlException.Errors)
{
if (error.Message != null)
{
ModelState.AddModelError(string.Empty, error.Message);
}
}
}
else
{
ModelState.AddModelError(string.Empty, updateException.Message);
}
}
else
{
ModelState.AddModelError(string.Empty, updateException.Message);
}
}
catch (Exception e)
{
ModelState.AddModelError(string.Empty, e.Message);
}
finally
{
newdb.Dispose();
}
}
ViewBag.COSTAT = new SelectList(db.CSSTATs, "COSTAT", "COSTAT", cSCOSTAT.COSTAT);
cSCOSTAT.CSCOMSTR = db.CSCOMSTRs.Find(cSCOSTAT.CONO);
return(View(cSCOSTAT));
}
public ActionResult Edit1([Bind(Include = "CONO,SDATE,COSTAT,FILETYPE,FILELOC,SEALLOC,EDATE,ROWNO,STAMP")] CSCOSTAT cSCOSTAT)
{
if (ModelState.IsValid)
{
ASIDBConnection newdb = new ASIDBConnection();
try
{
string oldKey = (string)Session["CSCOSTAT_OLDCONO"];
CSCOSTAT curRec = db.CSCOSTATs.Find(oldKey, cSCOSTAT.ROWNO); //allowing for key change
if (curRec.STAMP == cSCOSTAT.STAMP)
{
cSCOSTAT.STAMP = cSCOSTAT.STAMP + 1;
if (oldKey != cSCOSTAT.CONO)
{
curRec.SDATE = new DateTime(3000, 1, 2); // make sure the old previous row date gets reversed
UpdatePreviousRow(curRec);
db.CSCOSTATs.Remove(curRec);
int lastRowNo = 0;
try
{
lastRowNo = db.CSCOSTATs.Where(m => m.CONO == cSCOSTAT.CONO).Max(n => n.ROWNO);
}
catch (Exception e) { lastRowNo = -1; }
finally { };
cSCOSTAT.ROWNO = lastRowNo + 1;
db.CSCOSTATs.Add(cSCOSTAT);
}
else
{
db.Entry(cSCOSTAT).State = EntityState.Modified;
}
if (db.CSCOSTATs.Where(x => x.CONO == cSCOSTAT.CONO).OrderByDescending(x => x.ROWNO).Select(x => x.ROWNO).FirstOrDefault() == cSCOSTAT.ROWNO)
{
UpdateCompany(cSCOSTAT);
}
UpdatePreviousRow(cSCOSTAT); // will always do this for the current record
db.SaveChanges();
// return RedirectToAction("Details", "CSCOMSTRs", new { id = MyHtmlHelpers.ConvertIdToByteStr(cSCOSTAT.CONO) });
//return new RedirectResult(Url.Action("Details", "CSCOMSTRs", new { id = MyHtmlHelpers.ConvertIdToByteStr(cSCOSTAT.CONO) }) + "#Status");
int page = (int)Session["CSCOSTATPage"];
return(RedirectToAction("Index", new { page = page }));
}
else
{
ModelState.AddModelError(string.Empty, "Record is modified");
}
}
catch (DbEntityValidationException dbEx)
{
foreach (var validationErrors in dbEx.EntityValidationErrors)
{
foreach (var validationError in validationErrors.ValidationErrors)
{
//string message = string.Format("{0}:{1}",
// validationErrors.Entry.Entity.ToString(),
// validationError.ErrorMessage);
// raise a new exception nesting
// the current instance as InnerException
ModelState.AddModelError(validationError.PropertyName, validationError.ErrorMessage);
}
}
}
catch (DbUpdateException ex)
{
UpdateException updateException = (UpdateException)ex.InnerException;
if (updateException != null)
{
if (updateException.InnerException != null)
{
var sqlException = (FirebirdSql.Data.FirebirdClient.FbException)updateException.InnerException;
foreach (var error in sqlException.Errors)
{
if (error.Message != null)
{
ModelState.AddModelError(string.Empty, error.Message);
}
}
}
else
{
ModelState.AddModelError(string.Empty, updateException.Message);
}
}
else
{
ModelState.AddModelError(string.Empty, updateException.Message);
}
}
catch (Exception e)
{
ModelState.AddModelError(string.Empty, e.Message);
}
finally
{
newdb.Dispose();
}
}
ViewBag.CONO = new SelectList(db.CSCOMSTRs.Select(x => new { CONO = x.CONO, CONAME = x.CONAME + " (" + x.CONO + ")" }).OrderBy(y => y.CONAME), "CONO", "CONAME", cSCOSTAT.CONO);
ViewBag.COSTAT = new SelectList(db.CSSTATs, "COSTAT", "COSTAT", cSCOSTAT.COSTAT);
cSCOSTAT.CSCOMSTR = db.CSCOMSTRs.Find(cSCOSTAT.CONO);
return(View(cSCOSTAT));
}
public ActionResult Index(HttpPostedFileBase postedFile, string dbTableName, string dbKey, string localKey)
{
string filePath = string.Empty;
if (postedFile != null)
{
string path = Server.MapPath("~/Uploads/");
if (!Directory.Exists(path))
{
Directory.CreateDirectory(path);
}
filePath = path + Path.GetFileName(postedFile.FileName);
string extension = Path.GetExtension(postedFile.FileName);
string keyId = "";
string templine = "";
postedFile.SaveAs(filePath);
IEnumerable <dynamic> csRecs = null;
DataTable refTable;
string usefile = "";
if (dbTableName == "CSCOMSTR")
{
csRecs = db.CSCOMSTRs.ToList();
//whereFunc = item => item.CONO == $"{keyId}";
//orderByFunc = item => item.CONAME;
}
else if (dbTableName == "CSCOSTAT")
{
csRecs = db.CSCOSTATs.ToList();
usefile = path + "cs_company1.csv";
}
else if (dbTableName == "CSCOAR")
{
csRecs = db.CSCOARs.ToList();
usefile = path + "cs_company1.csv";
}
//Create a DataTable.
DataTable dt = new DataTable();
//addColumns(dt, companycol);
//dt.Columns.AddRange(new DataColumn[3] { new DataColumn("CaseFeeId", typeof(int)),
// new DataColumn("CaseFeeCode", typeof(string)),
// new DataColumn("CaseFeeDesc",typeof(string)) });
//Read the contents of CSV file.
string csvData = System.IO.File.ReadAllText(filePath);
//Execute a loop over the rows.
bool IsHeader = true;
int rowcnt = 0;
DataRow curRow = null;
foreach (string row in csvData.Split('\n'))
{
if ((!IsHeader) && (!string.IsNullOrEmpty(row)))
{
curRow = dt.Rows.Add();
rowcnt++;
int i = 1;
//Execute a loop over the columns.
//foreach (string cell in row.Split('\t'))
foreach (string cell in row.Split('|'))
{
try
{
dt.Rows[dt.Rows.Count - 1][i] = cell ?? "";
}
catch (Exception e)
{
Console.WriteLine(rowcnt.ToString() + " | " + e.Message);
}
finally
{
i++;
}
}
if ((dbTableName == "CSCOSTAT") || (dbTableName == "CSCOAR"))
{
string myDateStr = (string)curRow["status_date"];
string mySortStr = myDateStr.Split('/')[2] + myDateStr.Split('/')[1] + myDateStr.Split('/')[0];
curRow["sort_date"] = mySortStr;
}
if ((localKey.Length > 0) && (DBNull.Value != curRow[localKey]))
{
keyId = (string)curRow[localKey] ?? "";
if (dbTableName == "CSCOMSTR")
{
CSCOMSTR csRec = null;
Func <CSCOMSTR, bool> whereFunc = item => item.CONO == $"{keyId}";
Func <CSCOMSTR, Object> orderByFunc = item => item.CONAME;
//csRec = ((List<CSCOMSTR>) csRecs).Where(whereFunc).FirstOrDefault();
csRec = ((List <CSCOMSTR>)csRecs).Where(item => item.CONO == keyId).FirstOrDefault();
if (csRec == null)
{
curRow["New"] = "***";
}
else if (csRec.CONAME != (string)curRow["company_name"])
{
curRow["New"] = "**";
}
else if (csRec.INTYPE != (string)curRow["company_intype"])
{
curRow["New"] = "*";
}
else if (csRec.CONSTCODE != (string)curRow["company_constitution"])
{
curRow["New"] = "*";
}
if (DBNull.Value != curRow["company_staffid"])
{
curRow["company_staffid"] =
convertStaff((string)curRow["company_staffid"]);
}
if (DBNull.Value != curRow["company_constitution"])
{
curRow["company_constitution"] =
convertConst((string)curRow["company_constitution"]);
}
if (DBNull.Value != curRow["company_country"])
{
curRow["company_country"] =
convertCountry((string)curRow["company_country"]);
}
}
//SortOrder sortOrder = SortOrder.Descending;
//if (sortOrder == SortOrder.Ascending)
// orderByFunc = item => item.CONO;
//else if (sortOrder == SortOrder.Descending)
// orderByFunc = item => item.CONAME;
//db.CSCOMSTRs.OrderBy(orderByFunc);
}
}
else
{
if (IsHeader)
{
IsHeader = false;
List <string> myCol = new List <string>();
myCol.Add("New");
foreach (string cell in row.Split('|'))
{
try
{
myCol.Add(cell);
}
catch (Exception e)
{
Console.WriteLine(rowcnt.ToString() + " | " + e.Message);
}
finally
{
}
}
if ((dbTableName == "CSCOSTAT") || (dbTableName == "CSCOAR"))
{
myCol.Add("sort_date");
}
string[] myArray = new string[myCol.Count];
for (int c = 0; c < myCol.Count; c++)
{
myArray[c] = myCol[c];
}
addColumns(dt, myArray);
}
}
}
var datarow = dt.AsEnumerable();
if (dbTableName == "CSCOAR")
{
datarow = datarow.OrderBy(x => x.Field <string>("company_id")).ThenBy(n => n.Field <int>("ar_id")).Select(x => x);
refTable = OpenTable(usefile);
var csRefs = refTable.AsEnumerable();
int idx = 0;
int coid = 0;
string cono = "";
DateTime sdate;
CSCOAR csRec = null;
foreach (DataRow row in datarow)
{
templine = (string)row["company_id"];
coid = int.Parse(templine);
cono = null;
try
{
var corec = csRefs.Where(x => x.Field <int?>("company_id") == coid).FirstOrDefault();
if (corec != null)
{
cono = (string)corec["company_no"];
}
else
{
cono = null;
}
if (cono != null)
{
datarow.ElementAt(idx)["New"] = cono;
sdate = DateTime.Parse((string)(datarow.ElementAt(idx)["ar_date"]));
csRec = ((List <CSCOAR>)csRecs).Where(item => item.CONO == cono && item.ARTOFILE == sdate).FirstOrDefault();
if (csRec != null)
{
datarow.ElementAt(idx)[7] = "Exist";
}
else
{
CSCOAR cSCOAR = new CSCOAR();
try
{
ModelState.Clear();
cSCOAR.STAMP = 999;
cSCOAR.CONO = cono;
cSCOAR.ARTOFILE = sdate;
// cSCOAR.SUBMITDATE = DateTime.Parse((string)row["ar_submissiondate"]);
cSCOAR.REMINDER1 = DateTime.Parse((string)row["ar_reminderdate"]);
cSCOAR.FILEDAR = DateTime.Parse((string)row["ar_fileddate"]);
if (ModelState.IsValid)
{
int?lastRowNo = 0;
try
{
lastRowNo = db.CSCOARs.Where(m => m.CONO == cSCOAR.CONO).Max(n => n.ARNO);
}
catch (Exception e) { lastRowNo = -1; }
finally { };
cSCOAR.ARNO = (short)((lastRowNo ?? 0) + 1);
datarow.ElementAt(idx)[6] = cSCOAR.ARNO.ToString();
db.CSCOARs.Add(cSCOAR);
if (cSCOAR.FILEDAR != null) // create next year record if AR is filed
{
string sid = cSCOAR.CONO;
CSCOAR lastRec = db.CSCOARs.Where(m => m.CONO == sid).OrderByDescending(n => n.ARNO).FirstOrDefault();
if (cSCOAR.ARNO == lastRec.ARNO) // add next year record only if editing the last record
{
CSCOAR csRec1 = createNextYearAR(cSCOAR);
db.CSCOARs.Add(csRec1);
}
}
db.SaveChanges();
}
}
catch (Exception e)
{
datarow.ElementAt(idx)[6] = e.Message + "!!!";
db.CSCOARs.Remove(cSCOAR);
}
finally
{; };
};
}
}
catch (Exception e)
{
datarow.ElementAt(idx)["New"] = e.Message;
}
finally
{
idx++;
}
}
datarow = datarow.Where(y => y.Field <string>(7) != "Exist").OrderBy(x => x.Field <string>("New")).ThenBy(y => y.Field <int>("status_id"));
}
else if (dbTableName == "CSCOMSTR")
{
datarow = datarow.Where(x => x.Field <string>("New") == "***" && x.Field <string>("company_no") != "");
datarow = datarow.OrderBy(x => x.Field <int?>("company_id")).Select(x => x);
int idx = 0;
foreach (DataRow row in datarow)
{
templine = (string)row["company_no"];
CSCOMSTR cSCOMSTR = new CSCOMSTR();
try
{
ModelState.Clear();
cSCOMSTR.STAMP = 999;
cSCOMSTR.ARRE = "N";
cSCOMSTR.SPECIALRE = "N";
cSCOMSTR.SEQNO = 1; // set inital seqno
cSCOMSTR.CONO = (string)row["company_no"];
cSCOMSTR.CONAME = (string)row["company_name"];
cSCOMSTR.INTYPE = (string)row["company_intype"];
cSCOMSTR.CONSTCODE = (string)row["company_constitution"];
cSCOMSTR.COSTATD = DateTime.Now;
cSCOMSTR.INCDATE = DateTime.Parse((string)(row["company_incorporation"]));
cSCOMSTR.PRINOBJStr = (string)row["company_objectives"] ?? "";
cSCOMSTR.PINDCODE = (string)row["company_industrya"] ?? "";
cSCOMSTR.SINDCODE = (string)row["company_industryb"] ?? "";
cSCOMSTR.WEB = (string)row["company_website"] ?? "";
cSCOMSTR.STAFFCODE = (string)row["company_staffid"];
cSCOMSTR.CMMTStr = (string)row["company_comment"] ?? "";
cSCOMSTR.INCCTRY = "MY";
cSCOMSTR.COSTAT = "Active";
if (DBNull.Value != row["company_remark"])
{
cSCOMSTR.REM = (string)row["company_remark"] ?? "";
}
if (DBNull.Value != row["company_country"])
{
cSCOMSTR.INCCTRY = (string)row["company_country"];
}
;
if (row["company_activate"] != DBNull.Value)
{
cSCOMSTR.COSTAT = (string)row["company_activate"] == "t" ? "Active" : "NotActive";
}
;
if (ModelState.IsValid)
{
db.CSCOMSTRs.Add(cSCOMSTR);
db.SaveChanges();
}
}
catch (Exception e)
{
datarow.ElementAt(idx)["company_industryb"] = "!!!";
db.CSCOMSTRs.Remove(cSCOMSTR);
}
finally
{
// db.CSCOMSTRs.Add(cSCOMSTR);
idx++;
}
// Console.WriteLine( templine);
}
//db.SaveChanges();
}
else if (dbTableName == "CSCOSTAT")
{
datarow = datarow.OrderBy(x => x.Field <string>("company_id")).ThenBy(n => n.Field <string>("sort_date")).Select(x => x);
refTable = OpenTable(usefile);
var csRefs = refTable.AsEnumerable();
int idx = 0;
int coid = 0;
string cono = "";
DateTime sdate;
CSCOSTAT csRec = null;
foreach (DataRow row in datarow)
{
templine = (string)row["company_id"];
coid = int.Parse(templine);
cono = null;
try
{
var corec = csRefs.Where(x => x.Field <int?>("company_id") == coid).FirstOrDefault();
if (corec != null)
{
cono = (string)corec["company_no"];
}
else
{
cono = null;
}
if (cono != null)
{
datarow.ElementAt(idx)["New"] = cono;
sdate = DateTime.Parse((string)(datarow.ElementAt(idx)["status_date"]));
csRec = ((List <CSCOSTAT>)csRecs).Where(item => item.CONO == cono && item.SDATE == sdate).FirstOrDefault();
if (csRec != null)
{
datarow.ElementAt(idx)[7] = "Exist";
}
else
{
CSCOSTAT cSCOSTAT = new CSCOSTAT();
try
{
ModelState.Clear();
cSCOSTAT.STAMP = 999;
cSCOSTAT.CONO = cono;
cSCOSTAT.SDATE = sdate;
cSCOSTAT.FILETYPE = "A";
cSCOSTAT.COSTAT = (string)row["status_status"];
cSCOSTAT.FILELOC = (string)row["status_fileno"];
cSCOSTAT.SEALLOC = (string)row["status_sealno"];
if (ModelState.IsValid)
{
int?lastRowNo = 0;
try
{
lastRowNo = db.CSCOSTATs.Where(m => m.CONO == cSCOSTAT.CONO).Max(n => n.ROWNO);
}
catch (Exception e) { lastRowNo = -1; }
finally { };
cSCOSTAT.EDATE = cSCOSTAT.SDATE.AddDays(30000);
cSCOSTAT.ROWNO = (lastRowNo ?? 0) + 1;
datarow.ElementAt(idx)[6] = cSCOSTAT.ROWNO.ToString();
db.CSCOSTATs.Add(cSCOSTAT);
UpdatePreviousRow(cSCOSTAT);
db.SaveChanges();
}
}
catch (Exception e)
{
datarow.ElementAt(idx)[6] = e.Message + "!!!";
db.CSCOSTATs.Remove(cSCOSTAT);
}
finally
{
}
}
}
}
catch (Exception e)
{
datarow.ElementAt(idx)["New"] = e.Message;
}
finally
{
idx++;
}
}
datarow = datarow.Where(y => y.Field <string>(7) != "Exist").OrderBy(x => x.Field <string>("New")).ThenBy(y => y.Field <int>("status_id"));
}
DataTable ndt = datarow.CopyToDataTable <DataRow>();
return(View(ndt));
string conString = ConfigurationManager.ConnectionStrings["SirisCS"].ConnectionString;
using (FbConnection con = new FbConnection(conString))
{
//datarow = dt.AsEnumerable();
//newrow = datarow.Where(x => x.Field<int>("Id") == 2);
};
//using (SqlConnection con = new SqlConnection(conString))
//{
// using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(con))
// {
// //Set the database table name.
// sqlBulkCopy.DestinationTableName = "dbo.Customers";
// //[OPTIONAL]: Map the DataTable columns with that of the database table
// sqlBulkCopy.ColumnMappings.Add("Id", "CustomerId");
// sqlBulkCopy.ColumnMappings.Add("Name", "Name");
// sqlBulkCopy.ColumnMappings.Add("Country", "Country");
// con.Open();
// sqlBulkCopy.WriteToServer(dt);
// con.Close();
// }
// }
}
return(View());
}
public ActionResult Delete1Confirmed(string id, int row, int?page)
{
CSCOSTAT cSCOSTAT = db.CSCOSTATs.Find(MyHtmlHelpers.ConvertByteStrToId(id), row);
CSCOSTAT curRec = UpdatePreviousRow(cSCOSTAT);
if (curRec != null)
{
try
{
if (db.CSCOSTATs.Where(x => x.CONO == cSCOSTAT.CONO).OrderByDescending(x => x.ROWNO).Select(x => x.ROWNO).FirstOrDefault() == cSCOSTAT.ROWNO)
{
UpdateCompany(curRec);
}
db.CSCOSTATs.Remove(cSCOSTAT);
db.SaveChanges();
return(RedirectToAction("Index", new { page = page }));
}
catch (DbEntityValidationException dbEx)
{
foreach (var validationErrors in dbEx.EntityValidationErrors)
{
foreach (var validationError in validationErrors.ValidationErrors)
{
//string message = string.Format("{0}:{1}",
// validationErrors.Entry.Entity.ToString(),
// validationError.ErrorMessage);
// raise a new exception nesting
// the current instance as InnerException
ModelState.AddModelError(validationError.PropertyName, validationError.ErrorMessage);
}
}
}
catch (DbUpdateException ex)
{
UpdateException updateException = (UpdateException)ex.InnerException;
if (updateException != null)
{
if (updateException.InnerException != null)
{
var sqlException = (FirebirdSql.Data.FirebirdClient.FbException)updateException.InnerException;
foreach (var error in sqlException.Errors)
{
if (error.Message != null)
{
ModelState.AddModelError(string.Empty, error.Message);
}
}
}
else
{
ModelState.AddModelError(string.Empty, updateException.Message);
}
}
else
{
ModelState.AddModelError(string.Empty, updateException.Message);
}
}
}
else
{
ModelState.AddModelError(string.Empty, "Cannot delete the only status record");
}
Session["CSCOSTATPage"] = page ?? 1;
Session["CSCOSTAT_OLDCONO"] = cSCOSTAT.CONO; // storing the old no so that can allow change of cono
ViewBag.CONO = new SelectList(db.CSCOMSTRs.Select(x => new { CONO = x.CONO, CONAME = x.CONAME + " (" + x.CONO + ")" }).OrderBy(y => y.CONAME), "CONO", "CONAME", cSCOSTAT.CONO);
ViewBag.COSTAT = new SelectList(db.CSSTATs, "COSTAT", "COSTAT", cSCOSTAT.COSTAT);
ViewBag.page = page ?? 1;
ViewBag.Title = "Delete Company Change Status";
cSCOSTAT.CSCOMSTR = db.CSCOMSTRs.Find(cSCOSTAT.CONO);
return(View("Edit1", cSCOSTAT));
}
public IQueryable <CSCOSTAT> CurrentSelection()
{
//return db.CSCOSTATs.Where(x => x.ROWNO == db.CSCOSTATs.Where(y => y.CONO == x.CONO).Max(z => z.ROWNO)).OrderByDescending(x => x.SDATE);
string pSearchCode = "";
string pSearchName = "";
string pSearchCOSTAT = "";
string pSearchStaff = "";
string pSearchStage = "";
DateTime pSearchVdate = DateTime.Parse("01/01/0001");
DateTime pSearchDdate = DateTime.Parse("01/01/0001");
if (Session["SearchCOSTATRec"] != null)
{
CSCOSTAT searchRec = (CSCOSTAT)(Session["SearchCOSTATRec"]);
pSearchCode = searchRec.CSCOMSTR.COREGNO ?? "";
pSearchName = searchRec.CSCOMSTR.CONAME ?? "";
pSearchVdate = searchRec.SDATE;
pSearchDdate = searchRec.EDATE;
pSearchCOSTAT = searchRec.COSTAT ?? "";
pSearchStaff = searchRec.CSCOMSTR.STAFFCODE ?? "";
}
else
{ // start with current month proforma bills instead of entire list
pSearchVdate = new DateTime(DateTime.Today.Year, DateTime.Today.Month, 1);
pSearchDdate = pSearchVdate.AddMonths(1).AddDays(-1);
}
IQueryable <CSCOSTAT> cSCOSTATs = db.CSCOSTATs.Include(c => c.CSCOMSTR);
if ((string)Session["SearchCOSTATSort"] != "ARCHIVE")
{
cSCOSTATs = db.CSCOSTATs.Where(x => x.ROWNO == db.CSCOSTATs.Where(y => y.CONO == x.CONO).Max(z => z.ROWNO));
}
else
{
cSCOSTATs = db.CSCOSTATs.Where(x => x.ROWNO < db.CSCOSTATs.Where(y => y.CONO == x.CONO).Max(z => z.ROWNO));
}
if (pSearchCode != "")
{
cSCOSTATs = cSCOSTATs.Where(x => x.CSCOMSTR.COREGNO.Contains(pSearchCode.ToUpper()));
}
;
if (pSearchName != "")
{
cSCOSTATs = cSCOSTATs.Where(x => x.CSCOMSTR.CONAME.Contains(pSearchName.ToUpper()));
}
;
if (pSearchStaff != "")
{
cSCOSTATs = cSCOSTATs.Where(x => x.CSCOMSTR.STAFFCODE == pSearchStaff);
}
else
{
if (Session["HKSTAFFDB"] != null)
{
var staffdb = (IEnumerable <HKSTAFF>)Session["HKSTAFFDB"];
List <string> tt = new List <string>();
foreach (HKSTAFF item in staffdb)
{
tt.Add(item.STAFFCODE);
}
cSCOSTATs = cSCOSTATs.Where(x => tt.Contains(x.CSCOMSTR.STAFFCODE));
}
};
if (pSearchVdate != DateTime.Parse("01/01/0001"))
{
cSCOSTATs = cSCOSTATs.Where(x => x.SDATE >= pSearchVdate);
}
;
if (pSearchDdate != DateTime.Parse("01/01/0001"))
{
cSCOSTATs = cSCOSTATs.Where(x => x.SDATE <= pSearchDdate);
}
;
if (pSearchCOSTAT != "")
{
cSCOSTATs = cSCOSTATs.Where(x => x.COSTAT == pSearchCOSTAT);
}
if ((string)Session["SearchCOSTATSort"] == "CONAME")
{
cSCOSTATs = cSCOSTATs.OrderBy(n => n.CSCOMSTR.CONAME);
}
else if ((string)Session["SearchCOSTATSort"] == "SDATE")
{
cSCOSTATs = cSCOSTATs.OrderBy(n => n.SDATE);
}
else if ((string)Session["SearchCOSTATSort"] == "SDATELAST")
{
cSCOSTATs = cSCOSTATs.OrderByDescending(n => n.SDATE);
}
else if ((string)Session["SearchCOSTATSort"] == "COSTAT")
{
cSCOSTATs = cSCOSTATs.OrderBy(n => n.COSTAT);
}
else if ((string)Session["SearchCOSTATSort"] == "STAFFCODE")
{
cSCOSTATs = cSCOSTATs.OrderBy(n => n.CSCOMSTR.STAFFCODE);
}
else
{
cSCOSTATs = cSCOSTATs.OrderBy(n => n.COSTAT);
}
DateTime rptStart = pSearchVdate;
DateTime rptEnd = pSearchDdate;
ViewBag.RPT_START = rptStart.ToString("dd/MM/yyyy");
ViewBag.RPT_END = rptEnd.ToString("dd/MM/yyyy");
return(cSCOSTATs);
}
