public ActionResult InsertNewClientInfo(FormCollection collection)
{
try
{
//update from abd
using (LoanDataDataContext database = new LoanDataDataContext())
{
string accountID = collection.Get("ACCOUNTID");
var clients = from cl in database.CLIENT_PERINFOs where cl.AccountID == accountID select cl;
foreach (var item in clients)
{
CLIENT_INFOUPD upd = new CLIENT_INFOUPD();
long cID = 0;
long.TryParse(collection.Get("TrackID"), out cID);
upd.TRACID = cID;
upd.ADDR1 = collection.Get("ADDR1");
upd.ADDR2 = collection.Get("ADDR2");
upd.ADDR3 = collection.Get("ADDR3");
upd.Another_Address = collection.Get("Another_Address");
upd.Another_Mobile = collection.Get("Another_Mobile");
upd.Another_TEL = collection.Get("Another_TEL");
DateTime rDate = new DateTime();
upd.CLIENTID = item.ID;//long.Parse(collection.Get("CLIENTID"));
upd.DATE_UPD = DateTime.Now;
// track.Tracking_USER = long.Parse(collection.Get("Tracking_USER"));
upd.ADDITIONINFO = collection.Get("ADDITIONINFO");
upd.MOBILE = collection.Get("MOBILE");
upd.TEL = collection.Get("TEL");
upd.USR_REC = long.Parse(TGSession.ClientInfo.UID);
database.CLIENT_INFOUPDs.InsertOnSubmit(upd);
database.SubmitChanges();
item.Another_Address = upd.Another_Address;
item.Another_Mobile = upd.Another_Mobile;
item.Another_TEL = upd.Another_TEL;
database.SubmitChanges();
}
}
var jsonObj = new { Message = "Client Info Updated" };
return(Json(jsonObj, JsonRequestBehavior.AllowGet));
}
catch (Exception ex)
{
var jsonObj = new { Message = ex.Message };
return(Json(jsonObj, JsonRequestBehavior.AllowGet));
}
}
public ActionResult ReceivePapers(FormCollection collection)
{
try
{
using (LoanDataDataContext database = new LoanDataDataContext())
{
long uid = long.Parse(TGSession.ClientInfo.UID);
long cID = 0;
long.TryParse(collection.Get("LegalNoteID"), out cID);
var papers = collection.Get("papers");
// var legalID = collection.Get("papers");
TemplateData[] res = JsonConvert.DeserializeObject <TemplateData[]>(papers);
var papersRec = from p in database.Legal_Papers where p.LegalNoteID == cID select p;
foreach (var item in papersRec)
{
var pap = from r in res where long.Parse(r.id) == item.ID select r;
if (pap.ToList().Count > 0)
{
Legal_Paper updPaper = database.Legal_Papers.FirstOrDefault(p => p.ID == item.ID);
updPaper.IsRecived = true;
database.SubmitChanges();
}
}
var legalNote = database.LegalNotes.FirstOrDefault(l => l.ID == cID);
legalNote.LegalID = uid;
var track = (from t in database.Tracks where t.ID == legalNote.TrakID select t).FirstOrDefault();
if (track != null)
{
track.LegalReceivDate = DateTime.Now;
track.SendLegalState = 3;
track.Status = 3;
database.SubmitChanges();
}
}
var jsonObj = new { Message = "Legal Info Sended" };
return(Json(jsonObj));
}
catch (Exception ex)
{
var jsonObj = new { Message = ex.Message };
return(Json(jsonObj));
}
}
public ActionResult CreateAction(FormCollection collection)
{
try
{
string name = collection.Get("Name");
string code = collection.Get("ACTION_CODE");
int type = int.Parse(collection.Get("ActionType"));
using (LoanDataDataContext udatabase = new LoanDataDataContext())
{
LoanManagement.Models.Action act = new LoanManagement.Models.Action();
act.Name = name;
act.ACTION_CODE = code;
act.ActionType = type;
udatabase.Actions.InsertOnSubmit(act);
udatabase.SubmitChanges();
}
var jsonObj = new { test = "Action Has Been Created" };
return(Json(jsonObj));
}
catch (Exception exc)
{
var jsonObj = new { Error = exc.Message };
return(Json(jsonObj));
}
}
public ActionResult UpdateUser(FormCollection collection)
{
try
{
string UID = collection.Get("UserID");
string password = collection.Get("password");
string name = collection.Get("name");
string sur_name = collection.Get("sur_name");
string Typ = collection.Get("Typ");
string dept = collection.Get("Dept");
string isAdmin = collection.Get("IsDeptAdmin");
string RON = collection.Get("RO_Name");
string Activate = collection.Get("Activate");
using (LoanDataDataContext udatabase = new LoanDataDataContext())
{
Sys_User usr = udatabase.Sys_Users.FirstOrDefault(u => u.id == long.Parse(UID));
usr.password = RmsEncryption.Encrypt(password);
usr.sur_name = sur_name;
usr.name = name;
usr.RO_Name = RON;
if (!string.IsNullOrEmpty(Typ))
{
usr.Typ = short.Parse(Typ);
}
if (!string.IsNullOrEmpty(dept))
{
usr.Dept = int.Parse(dept);
}
if (!string.IsNullOrEmpty(isAdmin))
{
usr.IsDeptAdmin = isAdmin.ToLower() == "true" ? true : false;
}
if (!string.IsNullOrEmpty(Activate))
{
usr.Activate = Activate.ToLower() == "true" ? true : false;
}
udatabase.SubmitChanges();
}
var jsonObj = new { test = "User Has Been Updated" };
return(Json(new { message = jsonObj }, JsonRequestBehavior.AllowGet));
}
catch (Exception exc)
{
var jsonObj = new { Error = exc.Message };
return(Json(new { message = jsonObj }, JsonRequestBehavior.AllowGet));
}
}
public ActionResult UpdateDept(FormCollection collection)
{
try
{
string id = collection.Get("ID");
string name = collection.Get("Name");
string note = collection.Get("Note");
using (LoanDataDataContext udatabase = new LoanDataDataContext())
{
Sys_Dept act = udatabase.Sys_Depts.FirstOrDefault(u => u.ID == int.Parse(id));
act.Name = name;
act.Note = note;
udatabase.SubmitChanges();
}
var jsonObj = new { test = "Department Has Been Updated" };
return(Json(jsonObj));
}
catch (Exception exc)
{
var jsonObj = new { Error = exc.Message };
return(Json(jsonObj));
}
}
public ActionResult CloseLoan(FormCollection collection)
{
try
{
using (LoanDataDataContext database = new LoanDataDataContext())
{
long cID = 0;
long.TryParse(collection.Get("TrackID"), out cID);
Track cl = database.Tracks.FirstOrDefault(c => c.ID == cID);
if (cl != null)
{
cl.Status = 5;
database.SubmitChanges();
}
// track.Tracking_USER = long.Parse(collection.Get("Tracking_USER"));
var noaction = new { Message = "OK" };
return(Json(noaction));
}
}
catch (Exception ex)
{
var jsonObj = new { Error = ex.Message };
return(Json(jsonObj));
}
}
public ActionResult InsertFollowUp(FormCollection collection)
{
try
{
using (LoanDataDataContext database = new LoanDataDataContext())
{
Daily_Follow_Up followUp = new Daily_Follow_Up();
long cID = 0;
long.TryParse(collection.Get("TrackID"), out cID);
followUp.Tracks_ID = cID;
followUp.User_Follow = 1;
followUp.Follow_Date = DateTime.Now;
followUp.Follow_Action = int.Parse(collection.Get("Follow_Action"));
followUp.Follow_Note = collection.Get("Follow_Note");
followUp.Remainder_Date = DateTime.Parse(collection.Get("Remainder_Date"));// DateTime.ParseExact(collection.Get("Remainder_Date"), "dd/MM/yyyy", null);// DateTime.Parse();
followUp.Remainder_Note = collection.Get("Remainder_Note");
// track.Tracking_USER = long.Parse(collection.Get("Tracking_USER"));
database.Daily_Follow_Ups.InsertOnSubmit(followUp);
database.SubmitChanges();
}
var jsonObj = new { Message = "Follow Up inserted suc" };
return(Json(jsonObj));
}
catch (Exception ex)
{
var jsonObj = new { Error = ex.Message };
return(Json(jsonObj));
}
}
public ActionResult CreateDept(FormCollection collection)
{
try
{
string name = collection.Get("Name");
string note = collection.Get("Note");
using (LoanDataDataContext udatabase = new LoanDataDataContext())
{
Sys_Dept act = new Sys_Dept();
act.Name = name;
act.Note = note;
udatabase.Sys_Depts.InsertOnSubmit(act);
udatabase.SubmitChanges();
}
var jsonObj = new { test = "Department Has Been Created" };
return(Json(jsonObj));
}
catch (Exception exc)
{
var jsonObj = new { Error = exc.Message };
return(Json(jsonObj));
}
}
public ActionResult SendDataToLegal(FormCollection collection)
{
try
{
using (LoanDataDataContext database = new LoanDataDataContext())
{
LegalNote upd = new LegalNote();
long cID = 0;
long.TryParse(collection.Get("TrakID"), out cID);
upd.TrakID = cID;
upd.EmpNotes = collection.Get("EmpNotes");
var papers = collection.Get("papers");
TemplateData[] res = JsonConvert.DeserializeObject <TemplateData[]>(papers);
// upd.LegalID = long.Parse(collection.Get("legalID"));
upd.TrakerID = long.Parse(TGSession.ClientInfo.UID);
upd.Legal_Action = collection.Get("Legal_Action");
upd.SendingDate = DateTime.Now;
Track tr = database.Tracks.FirstOrDefault(t => t.ID == cID);
tr.SendLegalState = 2;
database.LegalNotes.InsertOnSubmit(upd);
database.SubmitChanges();
foreach (var item in res)
{
Legal_Paper paper = new Legal_Paper();
paper.LegalNoteID = upd.ID;
paper.paperName = item.text;
database.Legal_Papers.InsertOnSubmit(paper);
database.SubmitChanges();
}
}
var jsonObj = new { Message = "Legal Info Sended" };
return(Json(jsonObj));
}
catch (Exception ex)
{
var jsonObj = new { Message = ex.Message };
return(Json(jsonObj));
}
}
public ActionResult AssignToNewRo(string TrackIDs, int roId)
{
string[] arr = TrackIDs.Split(',');
try
{
foreach (var id in arr)
{
using (LoanDataDataContext database = new LoanDataDataContext())
{
long cID = 0;
long.TryParse(id, out cID);
if (cID != 0)
{
Track track = database.Tracks.FirstOrDefault(t => t.ID == cID);
track.CurrTraker = roId;
Tracker tracker = new Tracker();
tracker.TracksID = track.ID;
tracker.Declared_Date = DateTime.Now;
tracker.Admin_Notes = "";
tracker.Admin_Declare = long.Parse(TGSession.ClientInfo.UID);
tracker.Trackers = roId;
database.Trackers.InsertOnSubmit(tracker);
database.SubmitChanges();
var clientLoanInfos = database.Client_Loan_Infos.Where(x => x.ID == track.ClientLoanID).ToList();
foreach (var clientLoanInfo in clientLoanInfos)
{
clientLoanInfo.Read_Note = false;
database.SubmitChanges();
}
}
}
}
var jsonObj = new { Message = "Success" };
return(Json(jsonObj, JsonRequestBehavior.AllowGet));
}
catch (Exception ex)
{
var jsonObj = new { Error = ex.Message };
return(Json(jsonObj, JsonRequestBehavior.AllowGet));
}
}
public ActionResult InsertTrack(FormCollection collection)
{
try
{
using (LoanDataDataContext database = new LoanDataDataContext())
{
Track track = new Track();
long cID = 0;
long.TryParse(collection.Get("ClientLoanID"), out cID);
track.ClientLoanID = cID;
track.Tracking_Action = int.Parse(collection.Get("Tracking_Action"));
track.Tracking_Date = DateTime.Now;
// track.Tracking_USER = long.Parse(collection.Get("Tracking_USER"));
track.Tracking_Details = collection.Get("Tracking_Details");
// track.Admin_Declare = long.Parse(collection.Get("Admin_Declare"));
track.Status = 2;
track.CurrTraker = long.Parse(collection.Get("Tracking_USER"));;
database.Tracks.InsertOnSubmit(track);
database.SubmitChanges();
Tracker tracker = new Tracker();
tracker.TracksID = track.ID;
tracker.Declared_Date = DateTime.Now;
tracker.Admin_Notes = "";
tracker.Admin_Declare = long.Parse(TGSession.ClientInfo.UID);
tracker.Trackers = long.Parse(collection.Get("Tracking_USER"));
database.Trackers.InsertOnSubmit(tracker);
database.SubmitChanges();
}
var jsonObj = new { Message = "Track inserted suc" };
return(Json(jsonObj));
}
catch (Exception ex)
{
var jsonObj = new { Error = ex.Message };
return(Json(jsonObj));
}
}
public ActionResult RedirectToFollowUp(string clientId)
{
//abd alrahman//
using (var db = new LoanDataDataContext())
{
var clientLoanInfos = db.Client_Loan_Infos.Where(x => x.AccountID == clientId).ToList();
foreach (var cli in clientLoanInfos)
{
cli.Read_Note = true;
}
db.SubmitChanges();
}
return(Json(new { clientId = clientId }, JsonRequestBehavior.AllowGet));
}
public ActionResult UpdateUserTracker(FormCollection collection)
{
try
{
using (LoanDataDataContext database = new LoanDataDataContext())
{
long cID = 0;
long.TryParse(collection.Get("TrackID"), out cID);
Track track = database.Tracks.FirstOrDefault(t => t.ID == cID);
track.CurrTraker = long.Parse(collection.Get("Tracking_USER"));
Tracker tracker = new Tracker();
tracker.TracksID = track.ID;
tracker.Declared_Date = DateTime.Now;
tracker.Admin_Notes = collection.Get("Admin_Notes");
tracker.Admin_Declare = long.Parse(TGSession.ClientInfo.UID);
tracker.Trackers = long.Parse(collection.Get("Tracking_USER"));
database.Trackers.InsertOnSubmit(tracker);
database.SubmitChanges();
var clientLoanInfos = database.Client_Loan_Infos.Where(x => x.ID == track.ClientLoanID).ToList();
foreach (var clientLoanInfo in clientLoanInfos)
{
clientLoanInfo.Read_Note = false;
database.SubmitChanges();
}
}
var jsonObj = new { Message = "Tracker Updated" };
return(Json(jsonObj, JsonRequestBehavior.AllowGet));
}
catch (Exception ex)
{
var jsonObj = new { Error = ex.Message };
return(Json(jsonObj, JsonRequestBehavior.AllowGet));
}
}
public ActionResult DeleteUser(FormCollection collection)
{
try
{
string UID = collection.Get("id");
using (LoanDataDataContext udatabase = new LoanDataDataContext())
{
Sys_User usr = udatabase.Sys_Users.FirstOrDefault(u => u.id == long.Parse(UID));
udatabase.Sys_Users.DeleteOnSubmit(usr);
udatabase.SubmitChanges();
}
return(Json("Success", JsonRequestBehavior.AllowGet));
}
catch (Exception ex)
{
return(Json("Error", JsonRequestBehavior.AllowGet));
}
}
public ActionResult ChangePassword(string userName, string pass)
{
try
{
using (LoanDataDataContext udatabase = new LoanDataDataContext())
{
Sys_User usr = udatabase.Sys_Users.FirstOrDefault(u => u.user_name == userName);
usr.password = RmsEncryption.Encrypt(pass);;
udatabase.SubmitChanges();
}
var jsonObj = new { test = "User Has Been Updated" };
return(Json(new { message = jsonObj }, JsonRequestBehavior.AllowGet));
}
catch (Exception exc)
{
var jsonObj = new { Error = exc.Message };
return(Json(new { message = jsonObj }, JsonRequestBehavior.AllowGet));
}
}
public ActionResult AddLegalNote(FormCollection collection)
{
try
{
using (LoanDataDataContext database = new LoanDataDataContext())
{
long cID = 0;
long.TryParse(collection.Get("LegalNoteID"), out cID);
string legalNote = collection.Get("legalNote");
var LegalN = database.LegalNotes.FirstOrDefault(l => l.ID == cID);
if (LegalN != null)
{
LegalN.LegalNote1 = legalNote;
var track = (from t in database.Tracks where t.ID == LegalN.TrakID select t).FirstOrDefault();
if (track != null)
{
track.LegalReceivDate = DateTime.Now;
track.SendLegalState = 4;
track.Status = 4;
database.SubmitChanges();
}
}
}
var jsonObj = new { Message = "Legal Info Added" };
return(Json(jsonObj));
}
catch (Exception ex)
{
var jsonObj = new { Message = ex.Message };
return(Json(jsonObj));
}
}
public ActionResult InsertSchedule(FormCollection collection)
{
try
{
using (LoanDataDataContext database = new LoanDataDataContext())
{
//long cID = 0;
//long.TryParse(collection.Get("CLID"), out cID);
//Client_Loan_Info cl = database.Client_Loan_Infos.FirstOrDefault(c=>c.ID==cID);
string accountID = collection.Get("ACCOUNTID");
var client_loans = from cl in database.Client_Loan_Infos where cl.AccountID == accountID select cl;
string schedule = collection.Get("Scheduled");
if (!string.IsNullOrEmpty(schedule))
{
foreach (var item in client_loans)
{
Client_Loan_Info cl = database.Client_Loan_Infos.FirstOrDefault(c => c.ID == item.ID);
cl.Scheduled = schedule.ToLower() == "true" ? true : false;
cl.Down_Payment = double.Parse(collection.Get("Down_Payment"));
//Reschedule_Amount Monthly_Payment Duration Schedule_Date
if (!string.IsNullOrEmpty(collection.Get("Reschedule_Amount")))
{
cl.Reschedule_Amount = double.Parse(collection.Get("Reschedule_Amount"));
}
if (!string.IsNullOrEmpty(collection.Get("Monthly_Payment")))
{
cl.Monthly_Payment = double.Parse(collection.Get("Monthly_Payment"));
}
cl.Duration = collection.Get("Duration");
if (!string.IsNullOrEmpty(collection.Get("Schedule_Date")))
{
cl.Schedule_Date = DateTime.Parse(collection.Get("Schedule_Date"));
}
database.SubmitChanges();
}
}
var res = new { LData = client_loans.ToList().FirstOrDefault(), Message = "Scheduled inserted" };
return(Json(res));
//if (cl!=null)
//{
// string schedule = collection.Get("Scheduled");
// if (!string.IsNullOrEmpty(schedule))
// {
// cl.Scheduled = schedule == "true" ? true : false;
// cl.Down_Payment = double.Parse(collection.Get("Down_Payment"));
// database.SubmitChanges();
// var res = new { LData = cl, Message = "Scheduled inserted suc" };
// return Json(res);
// }
//}
// track.Tracking_USER = long.Parse(collection.Get("Tracking_USER"));
}
}
catch (Exception ex)
{
var jsonObj = new { Error = ex.Message };
return(Json(jsonObj));
}
}
public ActionResult InsertFollowUp(FormCollection collection)
{
try
{
using (LoanDataDataContext database = new LoanDataDataContext())
{
string accountid = collection.Get("ACCOUNTID");
var tracks = from tr in database.Client_Loan_Infos
join cl in database.Tracks on tr.ID equals cl.ClientLoanID
where tr.AccountID == accountid select cl;
foreach (var item in tracks)
{
Daily_Follow_Up followUp = new Daily_Follow_Up();
// long cID = 0;
// long.TryParse(collection.Get("TrackID"), out cID);
// followUp.Tracks_ID = cID;
followUp.Tracks_ID = item.ID;
followUp.User_Follow = long.Parse(TGSession.ClientInfo.UID);
followUp.Follow_Date = DateTime.Now;
followUp.Follow_Action = int.Parse(collection.Get("Follow_Action"));
followUp.Follow_Note = collection.Get("Follow_Note");
string[] formats = { "dd/MM/yyyy" };
var dateTime = DateTime.ParseExact(collection.Get("Remainder_Date"), formats, new CultureInfo("en-US"), DateTimeStyles.None);
// followUp.Remainder_Date = DateTime.Parse(collection.Get("Remainder_Date"));// DateTime.ParseExact(collection.Get("Remainder_Date"), "dd/MM/yyyy", null);// DateTime.Parse();
followUp.Remainder_Date = dateTime;
followUp.Remainder_Note = collection.Get("Remainder_Note");
// track.Tracking_USER = long.Parse(collection.Get("Tracking_USER"));
database.Daily_Follow_Ups.InsertOnSubmit(followUp);
database.SubmitChanges();
}
// Daily_Follow_Up followUp = new Daily_Follow_Up();
// long cID = 0;
// long.TryParse(collection.Get("TrackID"), out cID);
// followUp.Tracks_ID = cID;
// followUp.User_Follow = long.Parse(TGSession.ClientInfo.UID);
// followUp.Follow_Date = DateTime.Now;
// followUp.Follow_Action = int.Parse(collection.Get("Follow_Action"));
// followUp.Follow_Note = collection.Get("Follow_Note");
// string[] formats = { "dd/MM/yyyy" };
// var dateTime = DateTime.ParseExact(collection.Get("Remainder_Date"), formats, new CultureInfo("en-US"), DateTimeStyles.None);
//// followUp.Remainder_Date = DateTime.Parse(collection.Get("Remainder_Date"));// DateTime.ParseExact(collection.Get("Remainder_Date"), "dd/MM/yyyy", null);// DateTime.Parse();
// followUp.Remainder_Date = dateTime;
// followUp.Remainder_Note = collection.Get("Remainder_Note");
// // track.Tracking_USER = long.Parse(collection.Get("Tracking_USER"));
// database.Daily_Follow_Ups.InsertOnSubmit(followUp);
// database.SubmitChanges();
}
var jsonObj = new { Message = "Follow Up inserted suc" };
return(Json(jsonObj));
}
catch (Exception ex)
{
var jsonObj = new { Error = ex.Message };
return(Json(jsonObj));
}
}
public string readingExcelFile(string isUpdate)
{
string UploadPath = HttpContext.Request.MapPath("~/FileUploaded/Temp");
DataSet ds = new DataSet();
foreach (string fileName in Directory.GetFiles(UploadPath))
{
string path = fileName;
string extension = Path.GetExtension(fileName).ToLower();
string fn = Path.GetFileName(fileName).Split('.')[0];
//here start Reading
//Now Read files
if (extension == ".xls" || extension == ".xlsx")
{
string excelConnectionString = string.Empty;
// connection String for xls file format.
if (extension == ".xls")
{
excelConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + path + ";Extended Properties=\"Excel 8.0;\"";
}
//connection String for xlsx file format.
else if (extension == ".xlsx")
{
excelConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties='Excel 12.0;IMEX=1;'";
}
//Create Connection to Excel work book and add oledb namespace
OleDbConnection excelConnection = new OleDbConnection(excelConnectionString);
excelConnection.Open();
DataTable dt = new DataTable();
dt = excelConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
if (dt == null)
{
return(null);
}
String[] excelSheets = new String[dt.Rows.Count];
int t = 0;
//excel data saves in temp file here.
foreach (DataRow row in dt.Rows)
{
string sheetName = row["TABLE_NAME"].ToString();
if (sheetName.Contains("{ONE}"))
{
//{ONE}
excelSheets[0] = sheetName;
}
if (sheetName.Contains("_xlnm#_FilterDatabase"))
{
continue;
}
else
{
excelSheets[t] = row["TABLE_NAME"].ToString();
t++;
}
}
OleDbConnection excelConnection1 = new OleDbConnection(excelConnectionString);
string query = string.Format("SELECT * FROM [{0}]", excelSheets[0]);
//if (fn=="risk")
//{
// query = string.Format("SELECT * FROM [{0}]", excelSheets[1]);
//}
using (OleDbDataAdapter dataAdapter = new OleDbDataAdapter(query, excelConnection1))
{
dataAdapter.Fill(ds, fn);
}
excelConnection.Close();
excelConnection1.Close();
}
}
///Start to add client
DataTable client = ds.Tables["SAB"];
//var ff=ds.Tables["SAB"].AsEnumerable().AsEnumerable<SABClass>();
DataTable risk = ds.Tables["risk"];
if (risk != null)
{
var lstrist = ds.Tables["risk"].AsEnumerable().ToList();
}
List <CLIENT_PERINFO> lstClient = new List <CLIENT_PERINFO>();
List <Loan_Info> lstLoans = new List <Loan_Info>();
List <string> notes = new List <string>();
List <string> RO = new List <string>();
if (client != null)
{
//update by abd alrahman
foreach (DataRow row in client.Rows)
{
//Here Is a Client
CLIENT_PERINFO cl = new CLIENT_PERINFO();
cl.FULLNAME = row["Arabic Full Name"].ToString(); //
cl.ADDR1 = row["Adress"].ToString(); //2
//cl.ADDR1 = row["Adress 1"].ToString();
//cl.ADDR2 = row["Adress 2"].ToString();
//cl.ADDR3 = row["Adress 3"].ToString();
cl.TEL = row["Telephone"].ToString(); //3
cl.AccountID = row["Account ID"].ToString(); //4
//Here Is Loan Info
Loan_Info l = new Loan_Info();
l.AccountID = row["Account ID"].ToString(); //4
l.FacilityTyp = row["Type"].ToString(); //5
// l.Branch = row["Branch"].ToString();
l.Charges = row["Charges"].ToString(); //6
l.Unpaid_Amount = row["Total Unpaid"].ToString(); //7
l.Outstanding_Amount = row["Outstanding"].ToString(); //8
l.Monthely_Payment = row["Monthly payment"].ToString();
l.Rate_NPL = row["Risk Rate"].ToString();
l.NOUnpaid = row["No of UNpaid/Real"].ToString();
//here add from risk
// string clientID = item["Account ID"].ToString();
// var cl = lstLoans.Find(l => l.AccountID == clientID);
//var clup = lstClient.Find(l => l.AccountID == clientID);
// string accid = row["Account ID"].ToString() ;
// var it = lstrist.Where(bb => bb.Field<string>(1).ToString() == accid);
if (risk != null)
{
var item = from r in risk.AsEnumerable() where (r.Field <string>("Account ID")) == row["Account ID"].ToString() select r;
if (item.ToList().Count > 0)
{
cl.EnglishName = item.FirstOrDefault()["Name of client"].ToString();
l.Branch = item.FirstOrDefault()["Branch"].ToString();
l.NPL_Amount = item.FirstOrDefault()["Total gross funded exposure NPL"].ToString();
l.Provision_Amount = item.FirstOrDefault()["Provisions balances"].ToString();
l.UID = item.FirstOrDefault()["UID"].ToString();
try
{
string dat = item.FirstOrDefault()["Date downgraded to NPL"].ToString();
DateTime datd;
if (DateTime.TryParse(dat, out datd))
{
l.Date_Downgrading = datd;
}
}
catch
{
}
l.Note = item.FirstOrDefault()["RMD new comments"].ToString();
DateTime rDate;
try
{
if (item.FirstOrDefault()["File receiving date"].ToString() != null)
{
DateTime.TryParse(item.FirstOrDefault()["File receiving date"].ToString(), out rDate);
l.Receiving_Date = rDate;
}
}
catch
{
}
l.Relation_With_Other_Banks = item.FirstOrDefault()["Relation with other Banks"].ToString();
l.Remarks = item.FirstOrDefault()["Remarks"].ToString();
l.Previous_RO = item.FirstOrDefault()["Previous RO"].ToString();
}
}
// var item = from r in risk.AsEnumerable() where (r.Field<string>("Account ID")) == row["Account ID"].ToString() select r;
// var item = from r in risk.AsEnumerable() where (r.Field<string>("Account ID")) == accid select r;
//here did not get data
//string expression="Account ID = '"+row["Account ID"].ToString()+"'";
//var item = risk.Select(expression);
lstClient.Add(cl);
lstLoans.Add(l);
}
}
DataTable guarant = ds.Tables["guarantees"];
List <Guarantees_Info> lstguan = new List <Guarantees_Info>();
if (guarant != null)
{
foreach (DataRow item in guarant.Rows)
{
Guarantees_Info guan = new Guarantees_Info();
guan.AccountID = item["Account ID"].ToString();
guan.Applicant_Account = item["Applicant Account"].ToString();
guan.Applicant_Name = item["Applicant Name"].ToString();
if (!string.IsNullOrEmpty(item["Estimate date"].ToString()))
{
DateTime estimateDate;
if (DateTime.TryParse(item["Estimate date"].ToString(), out estimateDate))
{
guan.Estimate_date = estimateDate;
}
}
guan.Estimated_Amount = item["Estimated Amount"].ToString();
guan.Gurantee_Description = item["Gurantee Description"].ToString();
guan.Gurantee_Type = item["Gurantee Type"].ToString();
guan.Initial_Amount = item["Initial Amount"].ToString();
lstguan.Add(guan);
}
}
//now here legal
DataTable legal = ds.Tables["legal"];
List <Legal_Report> lstRep = new List <Legal_Report>();
if (legal != null)
{
if (isUpdate == "false")
{
foreach (DataRow item in legal.Rows)
{
Legal_Report rep = new Legal_Report();
string accID = item["Account ID"].ToString();
if (accID.Length < 7)
{
rep.AccountID = accID.PadLeft(7, '0');
}
else
{
rep.AccountID = item["Account ID"].ToString();
}
rep.Court = item["المحكمة "].ToString();
rep.lawsuit_typ = item["نوع الدعوى "].ToString();
rep.lawsuitNOFirst = item[6].ToString();
rep.lawsuitNOSec = item[7].ToString();
rep.PrevintingTravel = item["منع سفر"].ToString();
if (!string.IsNullOrEmpty(item["تاريخ منع السفر"].ToString()))
{
DateTime prevTravDate;
if (DateTime.TryParse(item["تاريخ منع السفر"].ToString(), out prevTravDate))
{
rep.PrevTravDate = prevTravDate;
}
}
rep.Lawer = item["المحامي "].ToString();
using (LoanDataDataContext database = new LoanDataDataContext())
{
database.Legal_Reports.InsertOnSubmit(rep);
database.SubmitChanges();
long len = item.ItemArray.Count();
List <Legal_Procedure> lstPro = new List <Legal_Procedure>();
//here should insert legal
for (int i = 12; i < len; i++)
{
Legal_Procedure lp = new Legal_Procedure();
lp.AccountID = rep.AccountID;
lp.LegalRepID = rep.ID;
lp.Legal_Desc = legal.Columns[i].Caption;
lp.Proced = item[i].ToString();
lstPro.Add(lp);
}
database.Legal_Procedures.InsertAllOnSubmit(lstPro);
database.SubmitChanges();
}
// lstRep.Add(rep);
}
}
else if (isUpdate == "true")
{
using (LoanDataDataContext database = new LoanDataDataContext())
{
foreach (DataRow item in legal.Rows)
{
string accID = item["Account ID"].ToString();
if (accID.Length < 7)
{
accID = accID.PadLeft(7, '0');
}
Legal_Report rep = (from l in database.Legal_Reports where l.AccountID == accID select l).FirstOrDefault();
if (rep != null)
{
// rep.AccountID =
rep.Court = item["المحكمة "].ToString();
rep.lawsuit_typ = item["نوع الدعوى "].ToString();
rep.lawsuitNOFirst = item[6].ToString();
rep.lawsuitNOSec = item[7].ToString();
rep.PrevintingTravel = item["منع سفر"].ToString();
if (!string.IsNullOrEmpty(item["تاريخ منع السفر"].ToString()))
{
DateTime prevTravDate;
if (DateTime.TryParse(item["تاريخ منع السفر"].ToString(), out prevTravDate))
{
rep.PrevTravDate = prevTravDate;
}
}
rep.Lawer = item["المحامي "].ToString();
database.SubmitChanges();
long len = item.ItemArray.Count();
List <Legal_Procedure> lstPro = new List <Legal_Procedure>();
//here should insert legal
for (int i = 12; i < len; i++)
{
var legalPr = from p in database.Legal_Procedures where p.Legal_Desc == legal.Columns[i].Caption && p.LegalRepID == rep.ID select p;
if (legalPr.ToList().Count > 0)
{
continue;
}
else
{
Legal_Procedure lp = new Legal_Procedure();
lp.AccountID = rep.AccountID;
lp.LegalRepID = rep.ID;
lp.Legal_Desc = legal.Columns[i].Caption;
lp.Proced = item[i].ToString();
lstPro.Add(lp);
}
}
database.Legal_Procedures.InsertAllOnSubmit(lstPro);
database.SubmitChanges();
}
// lstRep.Add(rep);
}
}
}
}
//now Inser To DB
using (LoanDataDataContext database = new LoanDataDataContext())
{
if (isUpdate == "false")
{
for (int i = 0; i < lstClient.Count; i++)
{
CLIENT_PERINFO cl = lstClient[i];
Loan_Info l = lstLoans[i];
Guarantees_Info guan = lstguan[i];
// l.Receiving_Date = null;
database.CLIENT_PERINFOs.InsertOnSubmit(cl);
database.Loan_Infos.InsertOnSubmit(l);
database.Guarantees_Infos.InsertOnSubmit(guan);
database.SubmitChanges();
Client_Loan_Info cli = new Client_Loan_Info();
cli.AccountID = cl.AccountID;
cli.CDate = DateTime.Now;
cli.ClientID = cl.ID;
cli.Read_Note = false;
cli.Date_Rec = DateTime.Now;
cli.GuaranteesID = guan.ID;
cli.LoanID = l.ID;
// cli.Note = notes[i];
database.Client_Loan_Infos.InsertOnSubmit(cli);
database.SubmitChanges();
//var results = from myRow in myDataTable.AsEnumerable()
// where myRow.Field<int>("RowNo") == 1
// select myRow;
var ro = from r in risk.Select() where (r.Field <string>("Account ID")) == cl.AccountID select r;
if (ro.ToList().Count < 1)
{
continue;
}
else
{
Sys_User usr = database.Sys_Users.FirstOrDefault(u => u.RO_Code == ro.FirstOrDefault().Field <string>("RO Code"));
if (usr != null)
{
Track tr = new Track();
tr.ClientLoanID = cli.ID;
tr.CurrTraker = usr.id;
tr.Status = 2;
tr.Tracking_Action = 1;
tr.Tracking_Date = DateTime.Now;
tr.Tracking_Details = "";
database.Tracks.InsertOnSubmit(tr);
database.SubmitChanges();
Tracker tracker = new Tracker();
tracker.Admin_Declare = long.Parse(TGSession.ClientInfo.UID);
tracker.Declared_Date = DateTime.Now;
tracker.Trackers = usr.id;
tracker.TracksID = tr.ID;
//var not = from r in risk.AsEnumerable() where (r.Field<double>("Account ID")).ToString() == cl.AccountID.ToString() select r;
tracker.Admin_Notes = l.Note;
database.Trackers.InsertOnSubmit(tracker);
database.SubmitChanges();
}
}
}
}
else if (isUpdate == "true")
{
if (risk != null)
{
foreach (DataRow item in risk.Rows)
{
string clientID = item["Account ID"].ToString();
Loan_Info updl = (from up in database.Loan_Infos
where up.AccountID == clientID
select up).FirstOrDefault();
if (updl != null)
{
//risk
if (!string.IsNullOrEmpty(item["Total gross funded exposure NPL"].ToString()))
{
updl.NPL_Amount = item["Total gross funded exposure NPL"].ToString();
}
//risk
if (!string.IsNullOrEmpty(item["Provisions balances"].ToString()))
{
updl.Provision_Amount = item["Provisions balances"].ToString();
}
//risk
if (!string.IsNullOrEmpty(item["Date downgraded to NPL"].ToString()))
{
string dat = item["Date downgraded to NPL"].ToString();
DateTime dateDowngrading;
if (DateTime.TryParse(dat, out dateDowngrading))
{
updl.Date_Downgrading = dateDowngrading;
}
}
//risk
if (!string.IsNullOrEmpty(item["UID"].ToString()))
{
updl.UID = item["UID"].ToString();
}
}
//Here should Update
database.SubmitChanges();
}
}
if (client != null)
{
//now sab
foreach (DataRow item in client.Rows)
{
string clientID = item["Account ID"].ToString();
Loan_Info updl = (from up in database.Loan_Infos
where up.AccountID == clientID
select up).FirstOrDefault();
if (updl != null)
{
//sab
if (!string.IsNullOrEmpty(item["Charges"].ToString()))
{
updl.Charges = item["Charges"].ToString();
}
//sab
if (!string.IsNullOrEmpty(item["Type"].ToString()))
{
updl.FacilityTyp = item["Type"].ToString();
}
//sab
if (!string.IsNullOrEmpty(item["Monthly payment"].ToString()))
{
updl.Monthely_Payment = item["Monthly payment"].ToString();
}
//sab
if (!string.IsNullOrEmpty(item["No of UNpaid/Real"].ToString()))
{
updl.NOUnpaid = item["No of UNpaid/Real"].ToString();
}
//sab
if (!string.IsNullOrEmpty(item["Outstanding"].ToString()))
{
updl.Outstanding_Amount = item["Outstanding"].ToString();
}
//sab
if (!string.IsNullOrEmpty(item["Risk Rate"].ToString()))
{
updl.Rate_NPL = item["Risk Rate"].ToString();
}
//sab
if (!string.IsNullOrEmpty(item["Total Unpaid"].ToString()))
{
updl.Unpaid_Amount = item["Total Unpaid"].ToString();
}
}
//Here should Update
database.SubmitChanges();
}
}
if (lstguan.Count > 0)
{
for (int i = 0; i < lstLoans.Count; i++)
{
Guarantees_Info guan = lstguan[i];
Guarantees_Info updl = (from up in database.Guarantees_Infos
where up.AccountID == guan.AccountID
select up).FirstOrDefault();
if (updl != null)
{
if (!string.IsNullOrEmpty(guan.Applicant_Account))
{
updl.Applicant_Account = guan.Applicant_Account;
}
if (!string.IsNullOrEmpty(guan.Applicant_Name))
{
updl.Applicant_Name = guan.Applicant_Name;
}
if (guan.Estimate_date.HasValue)
{
updl.Estimate_date = guan.Estimate_date;
}
if (!string.IsNullOrEmpty(guan.Estimated_Amount))
{
updl.Estimated_Amount = guan.Estimated_Amount;
}
if (!string.IsNullOrEmpty(guan.Gurantee_Description))
{
updl.Gurantee_Description = guan.Gurantee_Description;
}
if (!string.IsNullOrEmpty(guan.Gurantee_Type))
{
updl.Gurantee_Type = guan.Gurantee_Type;
}
if (!string.IsNullOrEmpty(guan.Initial_Amount))
{
updl.Initial_Amount = guan.Initial_Amount;
}
}
//Here should Update
database.SubmitChanges();
}
}
}
//database.CLIENT_PERINFOs.InsertAllOnSubmit(lstClient);
//database.Loan_Infos.InsertAllOnSubmit(lstLoans);
//database.Guarantees_Infos.InsertAllOnSubmit(lstguan);
//database.SubmitChanges();
//here tracking
///
DataTable tracking = ds.Tables["Tracking"];
if (tracking != null)
{
foreach (DataRow item in tracking.Rows)
{
Tracking_History history = new Tracking_History();
history.Account_ID = item["Client_AC"].ToString();
DateTime trackingDate;
if (DateTime.TryParse(item["Tracking Date"].ToString(), out trackingDate))
{
history.Tacking_Date = trackingDate;
}
history.Tracking_Notes = item["Tracking Details"].ToString();
database.Tracking_Histories.InsertOnSubmit(history);
database.SubmitChanges();
}
}
}
// int i= ds.Tables.Count;
return("");
}
public ActionResult CreateUser(FormCollection collection)
{
try
{
string user_name = collection.Get("user_name");
string password = collection.Get("password");
string name = collection.Get("name");
string sur_name = collection.Get("sur_name");
// string father_name = collection.Get("father_name");
// string mother_name = collection.Get("mother_name");
string Typ = collection.Get("Typ");
string dept = collection.Get("Dept");
string isAdmin = collection.Get("IsDeptAdmin");
string RON = collection.Get("RO_Name");
string ROC = collection.Get("RO_Code");
string Activate = collection.Get("Activate");
using (LoanDataDataContext udatabase = new LoanDataDataContext())
{
Sys_User usr = new Sys_User();
usr.user_name = user_name;
usr.password = RmsEncryption.Encrypt(password);
usr.sur_name = sur_name;
usr.name = name;
// usr.mother_name = mother_name;
// usr.father_name = father_name;
// usr.PRNTID = PRNTDDL;
usr.RO_Name = RON;
usr.RO_Code = ROC;
if (!string.IsNullOrEmpty(Typ))
{
usr.Typ = short.Parse(Typ);
}
if (!string.IsNullOrEmpty(dept))
{
usr.Dept = int.Parse(dept);
}
if (!string.IsNullOrEmpty(isAdmin))
{
usr.IsDeptAdmin = isAdmin.ToLower() == "true" ? true : false;
}
if (!string.IsNullOrEmpty(Activate))
{
usr.Activate = Activate.ToLower() == "true" ? true : false;
}
// usr.IS_ADMIN = IS_ADMIN =="True" ? "Y" : "N";
udatabase.Sys_Users.InsertOnSubmit(usr);
udatabase.SubmitChanges();
}
var jsonObj = new { test = "User Has Been Created" };
return(Json(jsonObj));
}
catch (Exception exc)
{
var jsonObj = new { Error = exc.Message };
return(Json(jsonObj));
}
}