private Student_FeePlan_Installment UpdateStudentFeeplanInstallment(
Student_FeePlan_Installment detail, ExcelWorksheet ws, int row, int col, int year)
{
var Amount_Actual = GetCellValue_Int(ws, row, col + 1);
if (Amount_Actual > 0)
{
var month = 8;
var day = 1;
var MONTH_ROW = 6;
var month_row = GetCellValue(ws, MONTH_ROW, col);
month_row = month_row.Replace("T", "").Replace("-Plan", "").Trim();
month = int.Parse(month_row);
Console.WriteLine("Month = " + month);
var Amount_Planning = GetCellValue_Int(ws, row, col);
var Date_Actual = GetCellValue_DateTime(ws, row, col + 2);
var Date_Planning = new DateTime(year, month, day);
detail.Amount_Planning = Amount_Planning;
detail.Amount_Actual = Amount_Actual;
detail.Date_Planning = Date_Planning;
detail.Date_Actual = Date_Actual;
detail.Installment_Status = (int)InstallmentStatusEnum.Finished;
}
return(detail);
}
public ActionResult ImportFeePlan(HttpPostedFileBase file)
{
if ((file != null) && (file.ContentLength > 0) && !string.IsNullOrEmpty(file.FileName))
{
if (!file.FileName.EndsWith("xlsx"))
{
return(Error("File import phải là .xlsx!"));
}
using (var package = new ExcelPackage(file.InputStream))
{
var currentSheet = package.Workbook.Worksheets;
var ws = currentSheet.First();
var noOfCol = ws.Dimension.End.Column;
var noOfRow = ws.Dimension.End.Row;
var DEFAULT_FEEPLAN = IoCConfig.Service <IFeePlanService>().FindAllValid().FirstOrDefault();
if (DEFAULT_FEEPLAN == null)
{
return(null);// Error();
}
var YEAR_ROW = 2;
var MONTH_ROW = 7;
var START_DATA_ROW = 8;
var START_FEEPLAN_COL = 94; //CP
var START_YEAR = 2016; //2012
//data row
var STUDENT_ID = 4;
var NORMINAL_COURSE_FEE = 13;
var INVOICE_DATE = 6;
var REMARKS = 20;
var ACTUAL_COURSE_FEE = 16;
var DISCOUNT = 15;
//var company = IoCConfig.Service<ICompany_TypeService>().FindById(COMPANY_TYPE);
var center = IoCConfig.Service <ICompanyService>().FindById(CURRENT_COMPANY);
var company_feeplans = center.Company_Type.FeePlans.Valid();
//test feeplan
//ExcelPackage pck = new ExcelPackage();
//var ws_out = pck.Workbook.Worksheets.Add("FeePlan List");
var feeplan_list = new List <MyDictionary>();
var count = 0;
for (int row = START_DATA_ROW; row <= noOfRow; row++)
{
//debug
//if (row == 196)
//{
// var ii = 0;
//}
try
{
var student_enroll = GetCellValue(ws, row, STUDENT_ID).ToLower();//Student Id
var student = IoCConfig.Service <IStudentService>().FindByCriteria(s => s.Student_EnrollNumber.ToLower() == student_enroll);
if (IsValidModel(student) && student.Student_FeePlan_List.Count() == 0)
{
var nominal_course_fee = GetCellValue_Int(ws, row, NORMINAL_COURSE_FEE); //Nominal Course Fee
var remarks = GetCellValue(ws, row, REMARKS); //Remarks
feeplan_list.Add(new MyDictionary {
Count = nominal_course_fee, Name = remarks
});
var feeplan_type = remarks == "Lumpsum" ? FeePlanTypeEnum.Lumpsum : FeePlanTypeEnum.Installment;
var feeplan = GetFeePlanByPriceAndType(company_feeplans, nominal_course_fee, feeplan_type);
if (IsValidModel(feeplan))
{
var invoice_date = GetCellValue_DateTime(ws, row, INVOICE_DATE);//invoice_date
if (!invoice_date.HasValue)
{
invoice_date = student.Student_Application_Date;
}
var actual_fee = GetCellValue_Int(ws, row, ACTUAL_COURSE_FEE); //actual_fee
var discount = GetCellValue_Int(ws, row, DISCOUNT); //discount
var student_feeplan = new Student_FeePlan
{
Student_Id = student.Id,
FeePlan_Id = feeplan.Id,
FeePlan_StartDate = invoice_date.Value,
Nominal_Course_Fee = nominal_course_fee,
Actual_Course_Fee = actual_fee,
Discount_Amount = discount,
Created_Date = DateTime.Now,
Status = (int)EntityStatus.Visible,
};
//student_feeplan.SetupFeePlanInstallments(feeplan);
//feeplan installments
var i = 0;
var FeePlan_Detail_Id = feeplan.FeePlan_Details.First().Id;
var current_year = START_YEAR;
//var max_i = student_feeplan.Student_FeePlan_Installments.Count;
var Amount_Planning = 0;
var Amount_Actual = 0;
//DateTime Date_Planning = DateTime.Today;
//DateTime? Date_Actual = DateTime.Today;
Student_FeePlan_Installment current_install = null;
for (int col = START_FEEPLAN_COL; col <= noOfCol; col++)
{
var year_row = GetCellValue(ws, YEAR_ROW, col);
if (!string.IsNullOrEmpty(year_row))
{
year_row = year_row.Replace("Năm", "").Trim();
current_year = int.Parse(year_row);
Console.WriteLine("Year = " + current_year);
}
try
{
var data = GetCellValue(ws, row, col);
if (!string.IsNullOrWhiteSpace(data))
{
var amount = GetCellValue_Int(ws, row, col);
if (amount > 0)
{
var date = GetCellValue(ws, MONTH_ROW, col);
if (!string.IsNullOrEmpty(date))
{
date = date.Trim().ToLower().Remove_Khoang_Trang();
if (date.Contains("plan"))
{
Amount_Planning = amount;
var this_month = date.Replace("-plan", "").Replace("t", "").Trim();
var Date_Planning = new DateTime(current_year, int.Parse(this_month), 1);
//add install
if (current_install != null)
{
student_feeplan.Student_FeePlan_Installments.Add(current_install);
current_install = null;
}
current_install = new Student_FeePlan_Installment
{
FeePlan_Detail_Id = FeePlan_Detail_Id,
Amount_Planning = Amount_Planning,
Date_Planning = Date_Planning,
Installment_Status = (int)InstallmentStatusEnum.Planned,
Status = (int)EntityStatus.Visible,
};
}
else if (date.Contains("actual"))
{
Amount_Actual = amount;
//AMMHN
//var Date_Actual = GetCellValue_DateTime_Full(ws, row, col + 1);
//if (Date_Actual == null)
//{
// var this_month = date.Replace("-actual", "").Replace("t", "").Trim();
// Date_Actual = new DateTime(current_year, int.Parse(this_month), 1);
//}
//AMMHCM
var this_month = date.Replace("-actual", "").Replace("t", "").Trim();
var Date_Actual = new DateTime(current_year, int.Parse(this_month), 1);
if (current_install == null)
{
current_install = new Student_FeePlan_Installment
{
FeePlan_Detail_Id = FeePlan_Detail_Id,
Amount_Planning = Amount_Actual,
Date_Planning = Date_Actual,
Installment_Status = (int)InstallmentStatusEnum.Planned,
Status = (int)EntityStatus.Visible,
};
}
else
{
if (current_install.Amount_Planning == 0)
{
current_install.Amount_Planning = Amount_Actual;
current_install.Date_Planning = Date_Actual;
}
}
current_install.Amount_Actual = Amount_Actual;
current_install.Date_Actual = Date_Actual;
current_install.Installment_Status = (int)InstallmentStatusEnum.Finished;
//add install
if (current_install != null)
{
student_feeplan.Student_FeePlan_Installments.Add(current_install);
current_install = null;
}
}
}
}
}
}
catch (Exception ex)
{
//throw ex;
return(Error(ex.Message));
}
}
//add install
if (current_install != null)
{
student_feeplan.Student_FeePlan_Installments.Add(current_install);
current_install = null;
}
try
{
//check feeplan
if (student_feeplan.IsFinishAllFees)
{
foreach (var ins in student_feeplan.Student_FeePlan_Installments.Valid())
{
if (ins.Installment_Status != (int)InstallmentStatusEnum.Finished)
{
ins.Status = (int)EntityStatus.Invisible;
//student_feeplan.Student_FeePlan_Installments.Remove(ins);
}
}
}
IoCConfig.Service <IStudent_FeePlanService>().Add(student_feeplan);
count++;
}
catch (Exception ex)
{
//throw ex;
return(Error(ex.Message));
}
}
}
}
catch (Exception ex)
{
//throw ex;
return(Error(ex.Message));
}
}
//test feeplan
//var r = 1;
feeplan_list = feeplan_list.DistinctBy(f => f.Count).ToList();
//foreach (var f in feeplan_list)
//{
// ws_out.Cells[r, 1].Value = f.Count;
// ws_out.Cells[r, 2].Value = f.Name;
// r++;
//}
//var stream = new MemoryStream(pck.GetAsByteArray());
//return File(stream,
// "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",
// "FeePlanList.xlsx");
return(MyContent("Upload successfully! = " + count));
}
}
return(Error());
}