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