public static void ReCalculatePeriod()
{
using (var db = new RentBikeEntities())
{
var contracts = db.Contracts.Where(c => c.CONTRACT_STATUS == true).ToList();
foreach (var contract in contracts)
{
List <PayPeriod> lstPay = db.PayPeriods.Where(c => c.CONTRACT_ID == contract.ID).ToList();
int div = lstPay.Count % 3;
if (div > 0)
{
div = 3 - div;
PayPeriod lastPay = lstPay.LastOrDefault();
for (int i = 1; i <= div; i++)
{
PayPeriod pp = new PayPeriod();
pp.CONTRACT_ID = contract.ID;
pp.PAY_DATE = lastPay.PAY_DATE.AddDays(i * 10);
pp.AMOUNT_PER_PERIOD = lastPay.AMOUNT_PER_PERIOD;
pp.STATUS = true;
pp.ACTUAL_PAY = 0;
db.PayPeriods.Add(pp);
}
}
}
db.SaveChanges();
}
}
public static void SaveSummaryPayFeeDaily()
{
using (var db = new RentBikeEntities())
{
try
{
var stores = db.Stores.Where(c => c.ACTIVE == true).ToList();
foreach (var store in stores)
{
List <CONTRACT_FULL_VW> listContract = GetWarningData(DateTime.Today.ToString(), string.Empty, store.ID).Where(c => c.OVER_DATE <= 50).ToList();
foreach (var contract in listContract)
{
if (db.SummaryPayFeeDailies.Any(c => c.PERIOD_DATE == DateTime.Today &&
c.STORE_ID == store.ID))
{
continue;
}
SummaryPayFeeDaily sum = new SummaryPayFeeDaily();
sum.CONTRACT_ID = contract.ID;
sum.CONTRACT_NO = contract.CONTRACT_NO;
sum.CUSTOMER_NAME = contract.CUSTOMER_NAME;
sum.PHONE = contract.PHONE;
sum.RENT_TYPE_ID = contract.RENT_TYPE_ID;
sum.RENT_TYPE_NAME = contract.RENT_TYPE_NAME;
sum.PERIOD_DATE = DateTime.Today;
sum.PAY_FEE = contract.FEE_PER_DAY;
sum.PAY_TIME = contract.PAYED_TIME;
sum.PAY_MESSAGE = contract.PERIOD_MESSAGE;
sum.STORE_ID = contract.STORE_ID;
sum.STORE_NAME = contract.STORE_NAME;
sum.NOTE = contract.NOTE;
sum.SEARCH_TEXT = contract.SEARCH_TEXT;
sum.CREATED_DATE = DateTime.Now;
sum.UPDATED_DATE = DateTime.Now;
db.SummaryPayFeeDailies.Add(sum);
}
}
db.SaveChanges();
}
catch (System.Data.Entity.Validation.DbEntityValidationException e)
{
foreach (var eve in e.EntityValidationErrors)
{
Logger.Log(string.Format("Entity of type \"{0}\" in state \"{1}\" has the following validation errors:",
eve.Entry.Entity.GetType().Name, eve.Entry.State));
foreach (var ve in eve.ValidationErrors)
{
Logger.Log(string.Format("- Property: \"{0}\", Error: \"{1}\"",
ve.PropertyName, ve.ErrorMessage));
}
}
}
catch (Exception ex)
{
Logger.Log(ex.Message + Environment.NewLine + ex.StackTrace);
}
}
}
public static void BackUp()
{
try
{
using (var db = new RentBikeEntities())
{
string dataTime = db.Database.Connection.Database + "_" + DateTime.Now.ToString("yyyyMMddHHmm");
string directory = HttpContext.Current.Server.MapPath("~/") + "/backups/";
string fileName = directory + dataTime + ".bak";
if (!System.IO.Directory.Exists(directory))
{
System.IO.Directory.CreateDirectory(directory);
}
System.IO.DirectoryInfo directoryInfo = new System.IO.DirectoryInfo(directory);
var files = directoryInfo.GetFiles();
if (files.Any() && files.Count() >= 7)
{
files.OrderBy(c => c.Name).FirstOrDefault().Delete();
}
// Here the procedure is called and executes successfully
db.Database.ExecuteSqlCommand(System.Data.Entity.TransactionalBehavior.DoNotEnsureTransaction, "EXEC [dbo].[BackUp] @path = N'" + fileName + "'");
//db.BackUp(fileName);
}
}
catch (Exception ex)
{
Logger.Log(ex.Message + Environment.NewLine + ex.StackTrace);
}
}
public static void CreateDummyInout()
{
using (var db = new RentBikeEntities())
{
var stores = db.Stores.Where(c => c.ACTIVE == true).ToList();
foreach (var store in stores)
{
if (db.InOuts.Any(c => c.INOUT_DATE == DateTime.Today && c.STORE_ID == store.ID))
{
continue;
}
InOut io = new InOut();
io.IN_AMOUNT = 0;
io.OUT_AMOUNT = 0;
io.CONTRACT_ID = 1;
io.PERIOD_ID = 1;
io.RENT_TYPE_ID = 3;
io.INOUT_TYPE_ID = 23;
io.PERIOD_DATE = DateTime.Today;
io.MORE_INFO = Constants.DUMMY_INOUT;
io.STORE_ID = store.ID;
io.SEARCH_TEXT = Constants.DUMMY_INOUT;
io.INOUT_DATE = DateTime.Now;
io.CREATED_BY = Constants.DUMMY_USER;
io.CREATED_DATE = DateTime.Now;
io.UPDATED_BY = Constants.DUMMY_USER;
io.UPDATED_DATE = DateTime.Now;
io.IS_DUMMY = true;
db.InOuts.Add(io);
}
db.SaveChanges();
}
}
public static List <SummaryPayFeeDaily> GetSummaryPayFeeDailyData(DateTime startDate, DateTime endDate, string searchText, int storeId)
{
using (var db = new RentBikeEntities())
{
IQueryable <SummaryPayFeeDaily> sumList = db.SummaryPayFeeDailies;
if (storeId != 0)
{
sumList = sumList.Where(c => c.STORE_ID == storeId);
}
if (startDate != null && endDate > startDate)
{
sumList = sumList.Where(c => c.PERIOD_DATE >= startDate && c.PERIOD_DATE <= endDate);
}
else
{
sumList = sumList.Where(c => c.PERIOD_DATE == startDate);
}
if (!string.IsNullOrEmpty(searchText))
{
sumList = sumList.Where(s => s.SEARCH_TEXT.ToLower().Contains(searchText.ToLower()) ||
s.CUSTOMER_NAME.ToLower().Contains(searchText.ToLower()));
}
return(sumList.ToList());
}
}
public static void LoadStore(DropDownList ddlSt)
{
using (var db = new RentBikeEntities())
{
var rt = from s in db.Stores
where s.ACTIVE == true
select s;
foreach (Store store in rt)
{
ddlSt.Items.Add(new ListItem(store.NAME, store.ID.ToString()));
}
}
}
public static void CreatePayPeriod(RentBikeEntities db, int contractId, PayPeriod lastPeriod, bool bFirstCreate)
{
var contract = db.Contracts.FirstOrDefault(c => c.CONTRACT_STATUS == true && c.ID == contractId);
if (contract != null)
{
int multipleFee;
decimal increateFeeCar;
decimal increateFeeEquip;
decimal increateFeeOther;
CalculatePeriodFee(db, contract, out multipleFee, out increateFeeCar, out increateFeeEquip, out increateFeeOther, bFirstCreate);
CreateOneMorePayPeriod(db, contract, lastPeriod, multipleFee, increateFeeCar, increateFeeEquip, increateFeeOther, bFirstCreate);
}
}
public static Contract GetContractByLicenseNo(string licenseNo)
{
using (var db = new RentBikeEntities())
{
var customer = db.Customers.FirstOrDefault(c => c.LICENSE_NO == licenseNo);
if (customer != null)
{
Contract contract = db.Contracts.FirstOrDefault(c => c.CUSTOMER_ID == customer.ID && c.CONTRACT_STATUS == true);
if (contract != null)
{
return(contract);
}
}
}
return(null);
}
public static void LoadCity(DropDownList ddlCt)
{
List <City> lst;
using (var db = new RentBikeEntities())
{
var ct = from s in db.Cities
select s;
lst = ct.ToList <City>();
}
ddlCt.DataSource = lst;
ddlCt.DataTextField = "NAME";
ddlCt.DataValueField = "ID";
ddlCt.DataBind();
}
public static void LoadRentType(DropDownList ddlRt)
{
List <RentType> lst;
using (var db = new RentBikeEntities())
{
var rt = from s in db.RentTypes
select s;
lst = rt.ToList <RentType>();
}
ddlRt.DataSource = lst;
ddlRt.DataTextField = "NAME";
ddlRt.DataValueField = "ID";
ddlRt.DataBind();
}
public static void WriteLog(string userName, string storeName, string action, bool isCrashed)
{
Log lg = new Log();
lg.ACCOUNT = userName;
lg.STORE = storeName;
lg.LOG_ACTION = action;
lg.LOG_DATE = DateTime.Now;
lg.IS_CRASH = isCrashed;
lg.LOG_MSG = string.Format("Tài khoản {0} cửa hàng {1} thực hiện {2} vào lúc {3}", lg.ACCOUNT, storeName, lg.LOG_ACTION, lg.LOG_DATE);
lg.SEARCH_TEXT = lg.LOG_MSG;
using (var db = new RentBikeEntities())
{
db.Logs.Add(lg);
db.SaveChanges();
}
}
public static void UpdatePeriodAmount()
{
using (var db = new RentBikeEntities())
{
var contracts = db.Contracts.Where(c => c.CONTRACT_STATUS == true).ToList();
foreach (var contract in contracts)
{
List <PayPeriod> payList = db.PayPeriods.Where(c => c.CONTRACT_ID == contract.ID).ToList();
foreach (PayPeriod pp in payList)
{
var inOutList = db.InOuts.Where(c => c.PERIOD_ID == pp.ID);
decimal total = inOutList.Select(c => c.IN_AMOUNT).DefaultIfEmpty(0).Sum();
pp.ACTUAL_PAY = total;
}
}
db.SaveChanges();
}
}
public static void UpdateAllPeriodHavingOverFee()
{
using (var db = new RentBikeEntities())
{
var contracts = db.Contracts.Where(c => c.CONTRACT_STATUS == true).ToList();
foreach (var contract in contracts)
{
List <PayPeriod> payList = db.PayPeriods.Where(c => c.CONTRACT_ID == contract.ID).ToList();
decimal totalActualPay = payList.Select(c => c.ACTUAL_PAY).DefaultIfEmpty(0).Sum();
decimal totalPlanPay = payList.Select(c => c.AMOUNT_PER_PERIOD).DefaultIfEmpty(0).Sum();
if (totalActualPay > totalPlanPay)
{
CommonList.CreatePayPeriod(db, contract.ID, payList.LastOrDefault(), false);
}
}
}
}
public static void WriteLog(string userName, string storeName, string action, string message, bool isCrashed)
{
Log lg = new Log();
lg.ACCOUNT = userName;
lg.STORE = storeName;
lg.LOG_ACTION = action;
lg.LOG_DATE = DateTime.Now;
lg.IS_CRASH = isCrashed;
lg.LOG_MSG = message;
lg.SEARCH_TEXT = lg.LOG_MSG;
using (var db = new RentBikeEntities())
{
db.Logs.Add(lg);
db.SaveChanges();
}
}
public static void AutoUpdateAmountPayPeriod()
{
using (var db = new RentBikeEntities())
{
var contracts = db.Contracts.Where(c => c.CONTRACT_STATUS == true).ToList();
foreach (var contract in contracts)
{
List <PayPeriod> listPayPeriod = db.PayPeriods.Where(c => c.CONTRACT_ID == contract.ID).ToList();
foreach (PayPeriod pp in listPayPeriod)
{
var inoutList = db.InOuts.Where(s => s.PERIOD_ID == pp.ID);
var sumPay = inoutList.Select(s => s.IN_AMOUNT).DefaultIfEmpty(0).Sum();
pp.ACTUAL_PAY = sumPay;
}
}
db.SaveChanges();
}
}
public static void UpdatePeriodAmountToZero()
{
using (var db = new RentBikeEntities())
{
var contracts = db.Contracts.Where(c => c.CONTRACT_STATUS == true).ToList();
foreach (var contract in contracts)
{
if (contract.FEE_PER_DAY == 0)
{
List <PayPeriod> payList = db.PayPeriods.Where(c => c.CONTRACT_ID == contract.ID && c.AMOUNT_PER_PERIOD > 0).ToList();
foreach (PayPeriod pp in payList)
{
pp.AMOUNT_PER_PERIOD = 0;
}
}
}
db.SaveChanges();
}
}
public static void AutoExtendContract()
{
try
{
using (var db = new RentBikeEntities())
{
//db.Configuration.AutoDetectChangesEnabled = false;
//db.Configuration.ValidateOnSaveEnabled = false;
var contracts = db.Contracts.Where(c => c.CONTRACT_STATUS == true).ToList();
foreach (var contract in contracts)
{
CommonList.AutoExtendPeriod(db, contract.ID);
}
}
}
catch (Exception ex)
{
Logger.Log(ex.Message + Environment.NewLine + ex.StackTrace);
}
}
private static void CalculatePeriodFee(RentBikeEntities db, Contract contract, out int multipleFee, out decimal increateFeeCar, out decimal increateFeeEquip, out decimal increateFeeOther, bool bFirstCreate)
{
multipleFee = Convert.ToInt32(Decimal.Floor(contract.CONTRACT_AMOUNT / 100000));
if (contract.FEE_PER_DAY == 0)
{
increateFeeCar = increateFeeEquip = increateFeeOther = 0;
}
else
{
if (bFirstCreate)
{
increateFeeCar = increateFeeEquip = increateFeeOther = contract.FEE_PER_DAY * 10;
}
else
{
increateFeeCar = increateFeeEquip = (contract.FEE_PER_DAY * 10) + (multipleFee * 50 * 10);
//increateFeeEquip = (contract.FEE_PER_DAY * 10) + (multipleFee * 100 * 10);
increateFeeOther = (contract.FEE_PER_DAY * 10);
}
}
}
public static void AutoExtendContract()
{
try
{
using (var db = new RentBikeEntities())
{
//db.Configuration.AutoDetectChangesEnabled = false;
//db.Configuration.ValidateOnSaveEnabled = false;
var contracts = db.Contracts.Where(c => c.CONTRACT_STATUS == true).ToList();
foreach (var contract in contracts)
{
CommonList.AutoExtendPeriod(db, contract.ID);
}
}
}
catch (Exception ex)
{
Logger.Log(ex.Message + Environment.NewLine + ex.StackTrace);
}
}
public static bool IsBadContract(RentBikeEntities db, int contractId)
{
var contract = db.Contracts.FirstOrDefault(c => c.ID == contractId && c.CONTRACT_STATUS == true);
if (contract != null)
{
var lstPeriod = db.PayPeriods.Where(c => c.CONTRACT_ID == contractId);
decimal totalPayed = lstPeriod.Select(c => c.ACTUAL_PAY).DefaultIfEmpty(0).Sum();
foreach (PayPeriod pp in lstPeriod)
{
if (pp.AMOUNT_PER_PERIOD > totalPayed)
{
if (DateTime.Today.Subtract(pp.PAY_DATE).Days > 50)
{
return(true);
}
}
totalPayed -= pp.AMOUNT_PER_PERIOD;
}
}
return(false);
}
public static void RemoveRedundantPeriod()
{
using (var db = new RentBikeEntities())
{
var contracts = db.Contracts.Where(c => c.CONTRACT_STATUS == true).ToList();
foreach (var contract in contracts)
{
List <PayPeriod> lstPay = db.PayPeriods.Where(c => c.CONTRACT_ID == contract.ID).ToList();
int div = lstPay.Count % 3;
if (div > 0)
{
int index = lstPay.Count - 1;
for (int i = 1; i <= div; i++)
{
db.PayPeriods.Remove(lstPay[index]);
index -= 1;
}
}
}
db.SaveChanges();
}
}
public static void AutoExtendPeriod(RentBikeEntities db, int contractId)
{
Contract contract = db.Contracts.FirstOrDefault(c => c.ID == contractId && c.CONTRACT_STATUS == true);
if (contract != null)
{
var listPayPeriod = db.PayPeriods.Where(c => c.CONTRACT_ID == contract.ID).ToList();
PayPeriod lastPayPeriod = listPayPeriod.LastOrDefault();
DateTime extendEndDate = (contract.EXTEND_END_DATE == null || contract.EXTEND_END_DATE == contract.END_DATE) ? contract.END_DATE.AddDays(-10) : lastPayPeriod.PAY_DATE;
int overDate = DateTime.Today.Subtract(extendEndDate).Days;
if (overDate >= 0)
{
int percentDate = overDate / 30;
DateTime endDateUpdated = extendEndDate.AddDays(30 * (percentDate + 1));
contract.EXTEND_END_DATE = endDateUpdated;
int multipleFee;
decimal increateFeeCar;
decimal increateFeeEquip;
decimal increateFeeOther;
CalculatePeriodFee(db, contract, out multipleFee, out increateFeeCar, out increateFeeEquip, out increateFeeOther, false);
for (int i = 0; i <= percentDate; i++)
{
if (lastPayPeriod.PAY_DATE.Subtract(contract.EXTEND_END_DATE.Value.AddDays(-10)).Days > 0)
{
break;
}
lastPayPeriod = CreateOneMorePayPeriod(db, contract, lastPayPeriod, multipleFee, increateFeeCar, increateFeeEquip, increateFeeOther, false);
}
contract.EXTEND_END_DATE = lastPayPeriod.PAY_DATE;
db.SaveChanges();
}
}
}
public static void AutoExtendPeriod(RentBikeEntities db, int contractId)
{
Contract contract = db.Contracts.FirstOrDefault(c => c.ID == contractId && c.CONTRACT_STATUS == true);
if (contract != null)
{
var listPayPeriod = db.PayPeriods.Where(c => c.CONTRACT_ID == contract.ID).ToList();
PayPeriod lastPayPeriod = listPayPeriod.LastOrDefault();
DateTime extendEndDate = (contract.EXTEND_END_DATE == null || contract.EXTEND_END_DATE == contract.END_DATE) ? contract.END_DATE.AddDays(-10) : lastPayPeriod.PAY_DATE;
int overDate = DateTime.Today.Subtract(extendEndDate).Days;
if (overDate >= 0)
{
int percentDate = overDate / 30;
DateTime endDateUpdated = extendEndDate.AddDays(30 * (percentDate + 1));
contract.EXTEND_END_DATE = endDateUpdated;
int multipleFee;
decimal increateFeeCar;
decimal increateFeeEquip;
decimal increateFeeOther;
CalculatePeriodFee(db, contract, out multipleFee, out increateFeeCar, out increateFeeEquip, out increateFeeOther, false);
for (int i = 0; i <= percentDate; i++)
{
if (lastPayPeriod.PAY_DATE.Subtract(contract.EXTEND_END_DATE.Value.AddDays(-10)).Days > 0)
{
break;
}
lastPayPeriod = CreateOneMorePayPeriod(db, contract, lastPayPeriod, multipleFee, increateFeeCar, increateFeeEquip, increateFeeOther, false);
}
contract.EXTEND_END_DATE = lastPayPeriod.PAY_DATE;
db.SaveChanges();
}
}
}
public static void AutoUpdateAndRemovePeriod()
{
using (var db = new RentBikeEntities())
{
var contracts = db.Contracts.Where(c => c.CONTRACT_STATUS == true).ToList();
foreach (var contract in contracts)
{
List <PayPeriod> lstPay = db.PayPeriods.Where(c => c.CONTRACT_ID == contract.ID).ToList();
DateTime extendDate = contract.END_DATE.AddDays(-10);
if (!contract.EXTEND_END_DATE.HasValue || contract.EXTEND_END_DATE == contract.END_DATE)
{
contract.EXTEND_END_DATE = contract.END_DATE;
extendDate = contract.EXTEND_END_DATE.Value.AddDays(-10);
}
else
{
extendDate = contract.EXTEND_END_DATE.Value;
}
db.PayPeriods.RemoveRange(lstPay.Where(c => c.PAY_DATE > extendDate));
}
db.SaveChanges();
}
}
public static void UpdateAllPeriodHavingOverFee()
{
using (var db = new RentBikeEntities())
{
var contracts = db.Contracts.Where(c => c.CONTRACT_STATUS == true).ToList();
foreach (var contract in contracts)
{
List<PayPeriod> payList = db.PayPeriods.Where(c => c.CONTRACT_ID == contract.ID).ToList();
decimal totalActualPay = payList.Select(c => c.ACTUAL_PAY).DefaultIfEmpty(0).Sum();
decimal totalPlanPay = payList.Select(c => c.AMOUNT_PER_PERIOD).DefaultIfEmpty(0).Sum();
if (totalActualPay > totalPlanPay)
{
CommonList.CreatePayPeriod(db, contract.ID, payList.LastOrDefault(), false);
}
}
}
}
public static List<CONTRACT_FULL_VW> GetWarningData(string date, string searchText, int storeId)
{
using (var db = new RentBikeEntities())
{
//int totalRecord = 0;
List<CONTRACT_FULL_VW> dataList = new List<CONTRACT_FULL_VW>();
var st = db.CONTRACT_FULL_VW.Where(c => c.CONTRACT_STATUS == true && c.ACTIVE == true && c.IS_LOW_RECOVERABILITY == false);
if (storeId != 0)
{
st = st.Where(c => c.STORE_ID == storeId);
}
st = st.OrderByDescending(c => c.ID);
string searchDate = string.Empty;
if (!string.IsNullOrEmpty(date))
{
searchDate = Convert.ToDateTime(date).ToString("yyyyMMdd");
}
var lstPeriod = db.PayPeriods.Where(s => s.STATUS == true).ToList();
foreach (CONTRACT_FULL_VW c in st)
{
var inOutList = db.InOuts.Where(s => s.CONTRACT_ID == c.ID).ToList();
c.PAYED_TIME = 0;
c.PAY_DATE = c.RENT_DATE;
c.DAY_DONE = DateTime.Now.Subtract(c.PAY_DATE).Days;
DateTime nowDate = DateTime.Today;
if (!string.IsNullOrEmpty(date))
{
nowDate = Convert.ToDateTime(date);
}
string contactId = c.ID.ToString();
var tmpLstPeriod = lstPeriod.Where(s => s.CONTRACT_ID == c.ID).ToList();
if (tmpLstPeriod != null)
{
decimal paidAmount = tmpLstPeriod.Where(s => s.ACTUAL_PAY > 0).Select(s => s.ACTUAL_PAY).DefaultIfEmpty(0).Sum();
int paidNumberOfFee = 0;
bool paidFull = false;
foreach (PayPeriod pp in tmpLstPeriod)
{
c.PERIOD_MESSAGE = GetPeriodMessage(tmpLstPeriod, nowDate);
if (pp.AMOUNT_PER_PERIOD == 0)
{
c.OVER_DATE = 0;
break;
}
paidAmount -= pp.AMOUNT_PER_PERIOD;
if (paidAmount >= 0)
paidNumberOfFee += 1;
if (paidAmount <= 0)
{
c.OVER_DATE = nowDate.Subtract(pp.PAY_DATE).Days;
if (paidAmount < 0 && tmpLstPeriod.Any(s => s.PAY_DATE == pp.PAY_DATE.AddDays(9)))
{
c.OVER_DATE = nowDate.Subtract(pp.PAY_DATE).Days + 2;
}
c.PAY_DATE = pp.PAY_DATE;
c.PERIOD_ID = pp.ID;
if (paidAmount == 0 || c.OVER_DATE <= 0)
paidFull = true;
break;
}
}
c.PAYED_TIME = paidNumberOfFee;
c.DAY_DONE = DateTime.Now.Subtract(c.RENT_DATE).Days + 1;
if (string.IsNullOrEmpty(date) || DateTime.Now.Subtract(Convert.ToDateTime(date)).Days <= 0)
{
if (paidFull && c.OVER_DATE <= 0)
{
c.CSS_CLASS = "background-green";
}
else if (c.OVER_DATE > 10)
{
c.CSS_CLASS = "background-red";
}
}
else
{
if (c.OVER_DATE <= 0)
{
var inout = inOutList.Where(s => s.PERIOD_DATE.ToString("yyyyMMdd").Equals(nowDate.ToString("yyyyMMdd"))).OrderByDescending(s => s.INOUT_DATE).FirstOrDefault();
if (inout != null && inout.INOUT_DATE.HasValue && inout.INOUT_DATE.Value.Subtract(nowDate).Days > 0)
{
c.CSS_CLASS = "background-amber";
}
else
{
c.CSS_CLASS = "background-green";
}
}
else
{
c.CSS_CLASS = "background-red";
}
}
if (c.FEE_PER_DAY == 0)
c.CSS_CLASS = "background-green";
c.RENT_TYPE_NAME = ReBuildRentTypeName(c);
if (!string.IsNullOrEmpty(searchDate))
{
if (tmpLstPeriod.Any(s => s.PAY_DATE.ToString("yyyyMMdd").Equals(searchDate)))
{
c.FEE_PER_DAY = tmpLstPeriod.FirstOrDefault(s => s.PAY_DATE.ToString("yyyyMMdd").Equals(searchDate)).AMOUNT_PER_PERIOD;
dataList.Add(c);
}
}
else if (tmpLstPeriod.Any(s => s.PAY_DATE.ToString("yyyyMMdd").Equals(DateTime.Now.ToString("yyyyMMdd"))))
{
c.FEE_PER_DAY = tmpLstPeriod.FirstOrDefault(s => s.PAY_DATE.ToString("yyyyMMdd").Equals(DateTime.Now.ToString("yyyyMMdd"))).AMOUNT_PER_PERIOD;
dataList.Add(c);
}
}
}
if (!string.IsNullOrEmpty(searchText))
{
dataList = dataList.Where(s => s.SEARCH_TEXT.ToLower().Contains(searchText.ToLower())
|| s.CUSTOMER_NAME.ToLower().Contains(searchText.ToLower())).ToList();
}
return dataList.OrderBy(c => c.DAY_DONE).ToList();
}
}
public static void GetSummuryFeeMonthly(out decimal totalSlowFee, out decimal totalDebtFee, int storeId)
{
totalSlowFee = 0;
totalDebtFee = 0;
using (var db = new RentBikeEntities())
{
var lstPeriod = db.PayPeriods.Where(s => s.STATUS == true).ToList();
List<CONTRACT_FULL_VW> dataSlowList = new List<CONTRACT_FULL_VW>();
List<CONTRACT_FULL_VW> dataDebtList = new List<CONTRACT_FULL_VW>();
var st = db.CONTRACT_FULL_VW.Where(c => c.CONTRACT_STATUS == true && c.ACTIVE == true);
if (storeId != 0)
{
st = st.Where(c => c.STORE_ID == storeId);
}
foreach (CONTRACT_FULL_VW c in st)
{
c.PAYED_TIME = 0;
c.PAY_DATE = c.RENT_DATE;
c.DAY_DONE = DateTime.Now.Subtract(c.PAY_DATE).Days;
var tmpLstPeriod = lstPeriod.Where(s => s.CONTRACT_ID == c.ID);
if (tmpLstPeriod != null)
{
decimal totalAmountPeriod = tmpLstPeriod.Where(s => s.PAY_DATE <= DateTime.Today).Select(s => s.AMOUNT_PER_PERIOD).DefaultIfEmpty(0).Sum();
decimal totalAmountPaid = tmpLstPeriod.Where(s => s.PAY_DATE <= DateTime.Today).Select(s => s.ACTUAL_PAY).DefaultIfEmpty(0).Sum();
c.AMOUNT_LEFT = totalAmountPeriod - totalAmountPaid <= 0 ? 0 : totalAmountPeriod - totalAmountPaid;
decimal paidAmount = tmpLstPeriod.Where(s => s.ACTUAL_PAY > 0).Select(s => s.ACTUAL_PAY).DefaultIfEmpty(0).Sum();
foreach (PayPeriod pp in tmpLstPeriod)
{
if (pp.AMOUNT_PER_PERIOD == 0)
{
c.OVER_DATE = 0;
break;
}
paidAmount -= pp.AMOUNT_PER_PERIOD;
if (paidAmount <= 0)
{
if (paidAmount < 0)
{
c.OVER_DATE = DateTime.Today.Subtract(pp.PAY_DATE).Days;
}
else
{
if (tmpLstPeriod.Any(s => s.PAY_DATE == pp.PAY_DATE.AddDays(9)))
{
c.OVER_DATE = DateTime.Today.Subtract(pp.PAY_DATE.AddDays(9)).Days;
}
else
{
c.OVER_DATE = DateTime.Today.Subtract(pp.PAY_DATE.AddDays(10)).Days;
}
}
c.PERIOD_ID = pp.ID;
if (c.OVER_DATE >= 0)
{
if (c.OVER_DATE <= 50)
dataSlowList.Add(c);
if (c.OVER_DATE >= 11 && c.IS_LOW_RECOVERABILITY == false)
dataDebtList.Add(c);
}
break;
}
}
}
}
totalSlowFee = dataSlowList.Select(c => c.AMOUNT_LEFT).DefaultIfEmpty(0).Sum();
totalDebtFee = dataDebtList.Select(c => c.AMOUNT_LEFT).DefaultIfEmpty(0).Sum();
}
}
public static List<CONTRACT_FULL_VW> GetReportData(int storeId)
{
List<CONTRACT_FULL_VW> dataList = new List<CONTRACT_FULL_VW>();
using (var db = new RentBikeEntities())
{
var st = db.CONTRACT_FULL_VW.Where(c => c.CONTRACT_STATUS == true && c.ACTIVE == true);
if (storeId != 0)
{
st = st.Where(c => c.STORE_ID == storeId);
}
st = st.OrderByDescending(c => c.ID);
var lstPeriod = db.PayPeriods.Where(s => s.STATUS == true).ToList();
foreach (CONTRACT_FULL_VW c in st)
{
var inOutList = db.InOuts.Where(s => s.CONTRACT_ID == c.ID).ToList();
c.PAYED_TIME = 0;
c.PAY_DATE = c.RENT_DATE;
c.DAY_DONE = DateTime.Now.Subtract(c.PAY_DATE).Days;
var tmpLstPeriod = lstPeriod.Where(s => s.CONTRACT_ID == c.ID);
if (tmpLstPeriod != null)
{
decimal totalAmountPeriod = tmpLstPeriod.Where(s => s.PAY_DATE <= DateTime.Today).Select(s => s.AMOUNT_PER_PERIOD).DefaultIfEmpty(0).Sum();
decimal totalAmountPaid = tmpLstPeriod.Where(s => s.PAY_DATE <= DateTime.Today).Select(s => s.ACTUAL_PAY).DefaultIfEmpty(0).Sum();
c.AMOUNT_LEFT = totalAmountPeriod - totalAmountPaid <= 0 ? 0 : totalAmountPeriod - totalAmountPaid;
decimal paidAmount = tmpLstPeriod.Where(s => s.ACTUAL_PAY > 0).Select(s => s.ACTUAL_PAY).DefaultIfEmpty(0).Sum();
int paidNumberOfFee = 0;
foreach (PayPeriod pp in tmpLstPeriod)
{
if (pp.AMOUNT_PER_PERIOD == 0)
{
c.OVER_DATE = 0;
break;
}
paidAmount -= pp.AMOUNT_PER_PERIOD;
if (paidAmount >= 0)
paidNumberOfFee += 1;
if (paidAmount <= 0)
{
if (paidAmount < 0)
{
c.OVER_DATE = DateTime.Today.Subtract(pp.PAY_DATE).Days;
c.PAY_DATE = pp.PAY_DATE;
}
else
{
if (tmpLstPeriod.Any(s => s.PAY_DATE == pp.PAY_DATE.AddDays(9)))
{
c.OVER_DATE = DateTime.Today.Subtract(pp.PAY_DATE.AddDays(9)).Days;
c.PAY_DATE = pp.PAY_DATE.AddDays(9);
}
else
{
c.OVER_DATE = DateTime.Today.Subtract(pp.PAY_DATE.AddDays(10)).Days;
c.PAY_DATE = pp.PAY_DATE.AddDays(10);
}
}
c.PERIOD_ID = pp.ID;
}
}
dataList.Add(c);
}
}
}
return dataList;
}
public static List<SummaryPayFeeDaily> GetSummaryPayFeeDailyData(DateTime startDate, DateTime endDate, string searchText, int storeId)
{
using (var db = new RentBikeEntities())
{
IQueryable<SummaryPayFeeDaily> sumList = db.SummaryPayFeeDailies;
if (storeId != 0)
{
sumList = sumList.Where(c => c.STORE_ID == storeId);
}
if (startDate != null && endDate > startDate)
{
sumList = sumList.Where(c => c.PERIOD_DATE >= startDate && c.PERIOD_DATE <= endDate);
}
else
{
sumList = sumList.Where(c => c.PERIOD_DATE == startDate);
}
if (!string.IsNullOrEmpty(searchText))
{
sumList = sumList.Where(s => s.SEARCH_TEXT.ToLower().Contains(searchText.ToLower())
|| s.CUSTOMER_NAME.ToLower().Contains(searchText.ToLower()));
}
return sumList.ToList();
}
}
private static PayPeriod CreateOneMorePayPeriod(RentBikeEntities db, Contract contract, PayPeriod lastPeriod, decimal multipleFee, decimal increateFeeCar, decimal increateFeeEquip, decimal increateFeeOther, bool bFirstCreated)
{
PayPeriod pp1 = new PayPeriod();
pp1.CONTRACT_ID = contract.ID;
if (bFirstCreated)
{
pp1.PAY_DATE = lastPeriod.PAY_DATE;
pp1.AMOUNT_PER_PERIOD = increateFeeCar;
}
else
{
pp1.PAY_DATE = lastPeriod.PAY_DATE.AddDays(10);
if (contract.FEE_PER_DAY > 0)
{
switch (contract.RENT_TYPE_ID)
{
case 1:
if (((contract.FEE_PER_DAY / multipleFee)) < RentCarFeePerDay)
{
var countPeriod = db.PayPeriods.Where(c => c.CONTRACT_ID == contract.ID).Count();
if (countPeriod <= 3)
increateFeeCar = (contract.FEE_PER_DAY * 10) + (multipleFee * 50 * 10);
else if (countPeriod > 3)
increateFeeCar = (contract.FEE_PER_DAY * 10) + (multipleFee * 100 * 10);
pp1.AMOUNT_PER_PERIOD = increateFeeCar;
}
else
{
if (contract.FEE_PER_DAY < (lastPeriod.AMOUNT_PER_PERIOD / 10))
pp1.AMOUNT_PER_PERIOD = lastPeriod.AMOUNT_PER_PERIOD;
else
{
if (((contract.FEE_PER_DAY / multipleFee) * 10) < 4000)
pp1.AMOUNT_PER_PERIOD = increateFeeCar;
else
pp1.AMOUNT_PER_PERIOD = increateFeeOther;
}
}
break;
case 2:
if (contract.FEE_PER_DAY < (lastPeriod.AMOUNT_PER_PERIOD / 10))
pp1.AMOUNT_PER_PERIOD = lastPeriod.AMOUNT_PER_PERIOD;
else
{
if (((contract.FEE_PER_DAY / multipleFee) * 10) < 4000)
pp1.AMOUNT_PER_PERIOD = increateFeeCar;
else
pp1.AMOUNT_PER_PERIOD = increateFeeOther;
}
break;
//case 2:
// if (((contract.FEE_PER_DAY / multipleFee) * 10) < 6000)
// pp1.AMOUNT_PER_PERIOD = increateFeeEquip;
// else
// pp1.AMOUNT_PER_PERIOD = increateFeeOther;
// break;
default:
pp1.AMOUNT_PER_PERIOD = increateFeeOther;
break;
}
}
else
pp1.AMOUNT_PER_PERIOD = increateFeeCar;
}
pp1.STATUS = true;
pp1.ACTUAL_PAY = 0;
PayPeriod pp2 = new PayPeriod();
pp2.CONTRACT_ID = contract.ID;
if (bFirstCreated)
pp2.PAY_DATE = pp1.PAY_DATE.AddDays(9);
else
pp2.PAY_DATE = pp1.PAY_DATE.AddDays(10);
pp2.AMOUNT_PER_PERIOD = pp1.AMOUNT_PER_PERIOD;
pp2.STATUS = true;
pp2.ACTUAL_PAY = 0;
PayPeriod pp3 = new PayPeriod();
pp3.CONTRACT_ID = contract.ID;
pp3.PAY_DATE = pp2.PAY_DATE.AddDays(10);
pp3.AMOUNT_PER_PERIOD = pp1.AMOUNT_PER_PERIOD;
pp3.STATUS = true;
pp3.ACTUAL_PAY = 0;
db.PayPeriods.Add(pp1);
db.PayPeriods.Add(pp2);
db.PayPeriods.Add(pp3);
db.SaveChanges();
return pp3;
}
public static List <CONTRACT_FULL_VW> GetReportData(int storeId)
{
List <CONTRACT_FULL_VW> dataList = new List <CONTRACT_FULL_VW>();
using (var db = new RentBikeEntities())
{
var st = db.CONTRACT_FULL_VW.Where(c => c.CONTRACT_STATUS == true && c.ACTIVE == true);
if (storeId != 0)
{
st = st.Where(c => c.STORE_ID == storeId);
}
st = st.OrderByDescending(c => c.ID);
var lstPeriod = db.PayPeriods.Where(s => s.STATUS == true).ToList();
foreach (CONTRACT_FULL_VW c in st)
{
var inOutList = db.InOuts.Where(s => s.CONTRACT_ID == c.ID).ToList();
c.PAYED_TIME = 0;
c.PAY_DATE = c.RENT_DATE;
c.DAY_DONE = DateTime.Now.Subtract(c.PAY_DATE).Days;
var tmpLstPeriod = lstPeriod.Where(s => s.CONTRACT_ID == c.ID);
if (tmpLstPeriod != null)
{
decimal totalAmountPeriod = tmpLstPeriod.Where(s => s.PAY_DATE <= DateTime.Today).Select(s => s.AMOUNT_PER_PERIOD).DefaultIfEmpty(0).Sum();
decimal totalAmountPaid = tmpLstPeriod.Where(s => s.PAY_DATE <= DateTime.Today).Select(s => s.ACTUAL_PAY).DefaultIfEmpty(0).Sum();
c.AMOUNT_LEFT = totalAmountPeriod - totalAmountPaid <= 0 ? 0 : totalAmountPeriod - totalAmountPaid;
decimal paidAmount = tmpLstPeriod.Where(s => s.ACTUAL_PAY > 0).Select(s => s.ACTUAL_PAY).DefaultIfEmpty(0).Sum();
int paidNumberOfFee = 0;
foreach (PayPeriod pp in tmpLstPeriod)
{
if (pp.AMOUNT_PER_PERIOD == 0)
{
c.OVER_DATE = 0;
break;
}
paidAmount -= pp.AMOUNT_PER_PERIOD;
if (paidAmount >= 0)
{
paidNumberOfFee += 1;
}
if (paidAmount <= 0)
{
if (paidAmount < 0)
{
c.OVER_DATE = DateTime.Today.Subtract(pp.PAY_DATE).Days;
c.PAY_DATE = pp.PAY_DATE;
}
else
{
if (tmpLstPeriod.Any(s => s.PAY_DATE == pp.PAY_DATE.AddDays(9)))
{
c.OVER_DATE = DateTime.Today.Subtract(pp.PAY_DATE.AddDays(9)).Days;
c.PAY_DATE = pp.PAY_DATE.AddDays(9);
}
else
{
c.OVER_DATE = DateTime.Today.Subtract(pp.PAY_DATE.AddDays(10)).Days;
c.PAY_DATE = pp.PAY_DATE.AddDays(10);
}
}
c.PERIOD_ID = pp.ID;
}
}
dataList.Add(c);
}
}
}
return(dataList);
}
public static bool IsBadContract(RentBikeEntities db, int contractId)
{
var contract = db.Contracts.FirstOrDefault(c => c.ID == contractId && c.CONTRACT_STATUS == true);
if (contract != null)
{
var lstPeriod = db.PayPeriods.Where(c => c.CONTRACT_ID == contractId);
decimal totalPayed = lstPeriod.Select(c => c.ACTUAL_PAY).DefaultIfEmpty(0).Sum();
foreach (PayPeriod pp in lstPeriod)
{
if (pp.AMOUNT_PER_PERIOD > totalPayed)
{
if (DateTime.Today.Subtract(pp.PAY_DATE).Days > 50)
return true;
}
totalPayed -= pp.AMOUNT_PER_PERIOD;
}
}
return false;
}
private static PayPeriod CreateOneMorePayPeriod(RentBikeEntities db, Contract contract, PayPeriod lastPeriod, decimal multipleFee, decimal increateFeeCar, decimal increateFeeEquip, decimal increateFeeOther, bool bFirstCreated)
{
PayPeriod pp1 = new PayPeriod();
pp1.CONTRACT_ID = contract.ID;
if (bFirstCreated)
{
pp1.PAY_DATE = lastPeriod.PAY_DATE;
pp1.AMOUNT_PER_PERIOD = increateFeeCar;
}
else
{
pp1.PAY_DATE = lastPeriod.PAY_DATE.AddDays(10);
if (contract.FEE_PER_DAY > 0)
{
switch (contract.RENT_TYPE_ID)
{
case 1:
if (((contract.FEE_PER_DAY / multipleFee)) < RentCarFeePerDay)
{
var countPeriod = db.PayPeriods.Where(c => c.CONTRACT_ID == contract.ID).Count();
if (countPeriod <= 3)
{
increateFeeCar = (contract.FEE_PER_DAY * 10) + (multipleFee * 50 * 10);
}
else if (countPeriod > 3)
{
increateFeeCar = (contract.FEE_PER_DAY * 10) + (multipleFee * 100 * 10);
}
pp1.AMOUNT_PER_PERIOD = increateFeeCar;
}
else
{
if (contract.FEE_PER_DAY < (lastPeriod.AMOUNT_PER_PERIOD / 10))
{
pp1.AMOUNT_PER_PERIOD = lastPeriod.AMOUNT_PER_PERIOD;
}
else
{
if (((contract.FEE_PER_DAY / multipleFee) * 10) < 4000)
{
pp1.AMOUNT_PER_PERIOD = increateFeeCar;
}
else
{
pp1.AMOUNT_PER_PERIOD = increateFeeOther;
}
}
}
break;
case 2:
if (contract.FEE_PER_DAY < (lastPeriod.AMOUNT_PER_PERIOD / 10))
{
pp1.AMOUNT_PER_PERIOD = lastPeriod.AMOUNT_PER_PERIOD;
}
else
{
if (((contract.FEE_PER_DAY / multipleFee) * 10) < 4000)
{
pp1.AMOUNT_PER_PERIOD = increateFeeCar;
}
else
{
pp1.AMOUNT_PER_PERIOD = increateFeeOther;
}
}
break;
//case 2:
// if (((contract.FEE_PER_DAY / multipleFee) * 10) < 6000)
// pp1.AMOUNT_PER_PERIOD = increateFeeEquip;
// else
// pp1.AMOUNT_PER_PERIOD = increateFeeOther;
// break;
default:
pp1.AMOUNT_PER_PERIOD = increateFeeOther;
break;
}
}
else
{
pp1.AMOUNT_PER_PERIOD = increateFeeCar;
}
}
pp1.STATUS = true;
pp1.ACTUAL_PAY = 0;
PayPeriod pp2 = new PayPeriod();
pp2.CONTRACT_ID = contract.ID;
if (bFirstCreated)
{
pp2.PAY_DATE = pp1.PAY_DATE.AddDays(9);
}
else
{
pp2.PAY_DATE = pp1.PAY_DATE.AddDays(10);
}
pp2.AMOUNT_PER_PERIOD = pp1.AMOUNT_PER_PERIOD;
pp2.STATUS = true;
pp2.ACTUAL_PAY = 0;
PayPeriod pp3 = new PayPeriod();
pp3.CONTRACT_ID = contract.ID;
pp3.PAY_DATE = pp2.PAY_DATE.AddDays(10);
pp3.AMOUNT_PER_PERIOD = pp1.AMOUNT_PER_PERIOD;
pp3.STATUS = true;
pp3.ACTUAL_PAY = 0;
db.PayPeriods.Add(pp1);
db.PayPeriods.Add(pp2);
db.PayPeriods.Add(pp3);
db.SaveChanges();
return(pp3);
}
public static void SaveSummaryPayFeeDaily()
{
using (var db = new RentBikeEntities())
{
try
{
var stores = db.Stores.Where(c => c.ACTIVE == true).ToList();
foreach (var store in stores)
{
List<CONTRACT_FULL_VW> listContract = GetWarningData(DateTime.Today.ToString(), string.Empty, store.ID).Where(c => c.OVER_DATE <= 50).ToList();
foreach (var contract in listContract)
{
if (db.SummaryPayFeeDailies.Any(c => c.PERIOD_DATE == DateTime.Today
&& c.STORE_ID == store.ID))
continue;
SummaryPayFeeDaily sum = new SummaryPayFeeDaily();
sum.CONTRACT_ID = contract.ID;
sum.CONTRACT_NO = contract.CONTRACT_NO;
sum.CUSTOMER_NAME = contract.CUSTOMER_NAME;
sum.PHONE = contract.PHONE;
sum.RENT_TYPE_ID = contract.RENT_TYPE_ID;
sum.RENT_TYPE_NAME = contract.RENT_TYPE_NAME;
sum.PERIOD_DATE = DateTime.Today;
sum.PAY_FEE = contract.FEE_PER_DAY;
sum.PAY_TIME = contract.PAYED_TIME;
sum.PAY_MESSAGE = contract.PERIOD_MESSAGE;
sum.STORE_ID = contract.STORE_ID;
sum.STORE_NAME = contract.STORE_NAME;
sum.NOTE = contract.NOTE;
sum.SEARCH_TEXT = contract.SEARCH_TEXT;
sum.CREATED_DATE = DateTime.Now;
sum.UPDATED_DATE = DateTime.Now;
db.SummaryPayFeeDailies.Add(sum);
}
}
db.SaveChanges();
}
catch (System.Data.Entity.Validation.DbEntityValidationException e)
{
foreach (var eve in e.EntityValidationErrors)
{
Logger.Log(string.Format("Entity of type \"{0}\" in state \"{1}\" has the following validation errors:",
eve.Entry.Entity.GetType().Name, eve.Entry.State));
foreach (var ve in eve.ValidationErrors)
{
Logger.Log(string.Format("- Property: \"{0}\", Error: \"{1}\"",
ve.PropertyName, ve.ErrorMessage));
}
}
}
catch (Exception ex)
{
Logger.Log(ex.Message + Environment.NewLine + ex.StackTrace);
}
}
}
public static void ReCalculatePeriod()
{
using (var db = new RentBikeEntities())
{
var contracts = db.Contracts.Where(c => c.CONTRACT_STATUS == true).ToList();
foreach (var contract in contracts)
{
List<PayPeriod> lstPay = db.PayPeriods.Where(c => c.CONTRACT_ID == contract.ID).ToList();
int div = lstPay.Count % 3;
if (div > 0)
{
div = 3 - div;
PayPeriod lastPay = lstPay.LastOrDefault();
for (int i = 1; i <= div; i++)
{
PayPeriod pp = new PayPeriod();
pp.CONTRACT_ID = contract.ID;
pp.PAY_DATE = lastPay.PAY_DATE.AddDays(i * 10);
pp.AMOUNT_PER_PERIOD = lastPay.AMOUNT_PER_PERIOD;
pp.STATUS = true;
pp.ACTUAL_PAY = 0;
db.PayPeriods.Add(pp);
}
}
}
db.SaveChanges();
}
}
public static void LoadRentType(DropDownList ddlRt)
{
List<RentType> lst;
using (var db = new RentBikeEntities())
{
var rt = from s in db.RentTypes
select s;
lst = rt.ToList<RentType>();
}
ddlRt.DataSource = lst;
ddlRt.DataTextField = "NAME";
ddlRt.DataValueField = "ID";
ddlRt.DataBind();
}
public static void SaveSummaryPayFeeMonthly()
{
decimal totalSlowFee, totalDebtFee;
using (var db = new RentBikeEntities())
{
try
{
var stores = db.Stores.Where(c => c.ACTIVE == true).ToList();
foreach (var store in stores)
{
if (db.SummaryPayFeeMonthlies.Any(c => c.SUMMURY_DATE == DateTime.Today &&
c.STORE_ID == store.ID))
{
continue;
}
GetSummuryFeeMonthly(out totalSlowFee, out totalDebtFee, store.ID);
SummaryPayFeeMonthly sum1 = new SummaryPayFeeMonthly();
sum1.STORE_ID = store.ID;
sum1.STORE_NAME = store.NAME;
sum1.SUMMURY_FEE = totalSlowFee;
sum1.SUMMURY_FEE_TYPE = (int)SUMMURY_FEE_TYPE.SLOW_FEE;
sum1.SUMMURY_DATE = DateTime.Today;
sum1.NOTE = Constants.SUMMURY_SLOW_FEE;
sum1.CREATED_DATE = DateTime.Now;
sum1.CREATED_BY = Constants.DUMMY_USER;
sum1.UPDATED_DATE = DateTime.Now;
sum1.UPDATED_BY = Constants.DUMMY_USER;
db.SummaryPayFeeMonthlies.Add(sum1);
SummaryPayFeeMonthly sum2 = new SummaryPayFeeMonthly();
sum2.STORE_ID = store.ID;
sum2.STORE_NAME = store.NAME;
sum2.SUMMURY_FEE = totalDebtFee;
sum2.SUMMURY_FEE_TYPE = (int)SUMMURY_FEE_TYPE.DEBT_FEE;
sum2.SUMMURY_DATE = DateTime.Today;
sum2.NOTE = Constants.SUMMURY_DEBT_FEE;
sum2.CREATED_DATE = DateTime.Now;
sum2.CREATED_BY = Constants.DUMMY_USER;
sum2.UPDATED_DATE = DateTime.Now;
sum2.UPDATED_BY = Constants.DUMMY_USER;
db.SummaryPayFeeMonthlies.Add(sum2);
}
db.SaveChanges();
}
catch (System.Data.Entity.Validation.DbEntityValidationException e)
{
foreach (var eve in e.EntityValidationErrors)
{
Logger.Log(string.Format("Entity of type \"{0}\" in state \"{1}\" has the following validation errors:",
eve.Entry.Entity.GetType().Name, eve.Entry.State));
foreach (var ve in eve.ValidationErrors)
{
Logger.Log(string.Format("- Property: \"{0}\", Error: \"{1}\"",
ve.PropertyName, ve.ErrorMessage));
}
}
}
catch (Exception ex)
{
Logger.Log(ex.Message + Environment.NewLine + ex.StackTrace);
}
}
}
public static void GetSummuryFeeMonthly(out decimal totalSlowFee, out decimal totalDebtFee, int storeId)
{
totalSlowFee = 0;
totalDebtFee = 0;
using (var db = new RentBikeEntities())
{
var lstPeriod = db.PayPeriods.Where(s => s.STATUS == true).ToList();
List <CONTRACT_FULL_VW> dataSlowList = new List <CONTRACT_FULL_VW>();
List <CONTRACT_FULL_VW> dataDebtList = new List <CONTRACT_FULL_VW>();
var st = db.CONTRACT_FULL_VW.Where(c => c.CONTRACT_STATUS == true && c.ACTIVE == true);
if (storeId != 0)
{
st = st.Where(c => c.STORE_ID == storeId);
}
foreach (CONTRACT_FULL_VW c in st)
{
c.PAYED_TIME = 0;
c.PAY_DATE = c.RENT_DATE;
c.DAY_DONE = DateTime.Now.Subtract(c.PAY_DATE).Days;
var tmpLstPeriod = lstPeriod.Where(s => s.CONTRACT_ID == c.ID);
if (tmpLstPeriod != null)
{
decimal totalAmountPeriod = tmpLstPeriod.Where(s => s.PAY_DATE <= DateTime.Today).Select(s => s.AMOUNT_PER_PERIOD).DefaultIfEmpty(0).Sum();
decimal totalAmountPaid = tmpLstPeriod.Where(s => s.PAY_DATE <= DateTime.Today).Select(s => s.ACTUAL_PAY).DefaultIfEmpty(0).Sum();
c.AMOUNT_LEFT = totalAmountPeriod - totalAmountPaid <= 0 ? 0 : totalAmountPeriod - totalAmountPaid;
decimal paidAmount = tmpLstPeriod.Where(s => s.ACTUAL_PAY > 0).Select(s => s.ACTUAL_PAY).DefaultIfEmpty(0).Sum();
foreach (PayPeriod pp in tmpLstPeriod)
{
if (pp.AMOUNT_PER_PERIOD == 0)
{
c.OVER_DATE = 0;
break;
}
paidAmount -= pp.AMOUNT_PER_PERIOD;
if (paidAmount <= 0)
{
if (paidAmount < 0)
{
c.OVER_DATE = DateTime.Today.Subtract(pp.PAY_DATE).Days;
}
else
{
if (tmpLstPeriod.Any(s => s.PAY_DATE == pp.PAY_DATE.AddDays(9)))
{
c.OVER_DATE = DateTime.Today.Subtract(pp.PAY_DATE.AddDays(9)).Days;
}
else
{
c.OVER_DATE = DateTime.Today.Subtract(pp.PAY_DATE.AddDays(10)).Days;
}
}
c.PERIOD_ID = pp.ID;
if (c.OVER_DATE >= 0)
{
if (c.OVER_DATE <= 50)
{
dataSlowList.Add(c);
}
if (c.OVER_DATE >= 11 && c.IS_LOW_RECOVERABILITY == false)
{
dataDebtList.Add(c);
}
}
break;
}
}
}
}
totalSlowFee = dataSlowList.Select(c => c.AMOUNT_LEFT).DefaultIfEmpty(0).Sum();
totalDebtFee = dataDebtList.Select(c => c.AMOUNT_LEFT).DefaultIfEmpty(0).Sum();
}
}
public static void LoadCity(DropDownList ddlCt)
{
List<City> lst;
using (var db = new RentBikeEntities())
{
var ct = from s in db.Cities
select s;
lst = ct.ToList<City>();
}
ddlCt.DataSource = lst;
ddlCt.DataTextField = "NAME";
ddlCt.DataValueField = "ID";
ddlCt.DataBind();
}
public static void UpdatePeriodAmountToZero()
{
using (var db = new RentBikeEntities())
{
var contracts = db.Contracts.Where(c => c.CONTRACT_STATUS == true).ToList();
foreach (var contract in contracts)
{
if (contract.FEE_PER_DAY == 0)
{
List<PayPeriod> payList = db.PayPeriods.Where(c => c.CONTRACT_ID == contract.ID && c.AMOUNT_PER_PERIOD > 0).ToList();
foreach (PayPeriod pp in payList)
{
pp.AMOUNT_PER_PERIOD = 0;
}
}
}
db.SaveChanges();
}
}
public static void LoadStore(DropDownList ddlSt)
{
using (var db = new RentBikeEntities())
{
var rt = from s in db.Stores
where s.ACTIVE == true
select s;
foreach (Store store in rt)
{
ddlSt.Items.Add(new ListItem(store.NAME, store.ID.ToString()));
}
}
}
public static void AutoUpdateAndRemovePeriod()
{
using (var db = new RentBikeEntities())
{
var contracts = db.Contracts.Where(c => c.CONTRACT_STATUS == true).ToList();
foreach (var contract in contracts)
{
List<PayPeriod> lstPay = db.PayPeriods.Where(c => c.CONTRACT_ID == contract.ID).ToList();
DateTime extendDate = contract.END_DATE.AddDays(-10);
if (!contract.EXTEND_END_DATE.HasValue || contract.EXTEND_END_DATE == contract.END_DATE)
{
contract.EXTEND_END_DATE = contract.END_DATE;
extendDate = contract.EXTEND_END_DATE.Value.AddDays(-10);
}
else
{
extendDate = contract.EXTEND_END_DATE.Value;
}
db.PayPeriods.RemoveRange(lstPay.Where(c => c.PAY_DATE > extendDate));
}
db.SaveChanges();
}
}
public static void RemoveRedundantPeriod()
{
using (var db = new RentBikeEntities())
{
var contracts = db.Contracts.Where(c => c.CONTRACT_STATUS == true).ToList();
foreach (var contract in contracts)
{
List<PayPeriod> lstPay = db.PayPeriods.Where(c => c.CONTRACT_ID == contract.ID).ToList();
int div = lstPay.Count % 3;
if (div > 0)
{
int index = lstPay.Count - 1;
for (int i = 1; i <= div; i++)
{
db.PayPeriods.Remove(lstPay[index]);
index -= 1;
}
}
}
db.SaveChanges();
}
}
public static void BackUp()
{
try
{
using (var db = new RentBikeEntities())
{
string dataTime = db.Database.Connection.Database + "_" + DateTime.Now.ToString("yyyyMMddHHmm");
string directory = HttpContext.Current.Server.MapPath("~/") + "/backups/";
string fileName = directory + dataTime + ".bak";
if (!System.IO.Directory.Exists(directory))
System.IO.Directory.CreateDirectory(directory);
System.IO.DirectoryInfo directoryInfo = new System.IO.DirectoryInfo(directory);
var files = directoryInfo.GetFiles();
if (files.Any() && files.Count() >= 7)
{
files.OrderBy(c => c.Name).FirstOrDefault().Delete();
}
// Here the procedure is called and executes successfully
db.Database.ExecuteSqlCommand(System.Data.Entity.TransactionalBehavior.DoNotEnsureTransaction, "EXEC [dbo].[BackUp] @path = N'" + fileName + "'");
//db.BackUp(fileName);
}
}
catch (Exception ex)
{
Logger.Log(ex.Message + Environment.NewLine + ex.StackTrace);
}
}
public static void SaveSummaryPayFeeMonthly()
{
decimal totalSlowFee, totalDebtFee;
using (var db = new RentBikeEntities())
{
try
{
var stores = db.Stores.Where(c => c.ACTIVE == true).ToList();
foreach (var store in stores)
{
if (db.SummaryPayFeeMonthlies.Any(c => c.SUMMURY_DATE == DateTime.Today
&& c.STORE_ID == store.ID))
continue;
GetSummuryFeeMonthly(out totalSlowFee, out totalDebtFee, store.ID);
SummaryPayFeeMonthly sum1 = new SummaryPayFeeMonthly();
sum1.STORE_ID = store.ID;
sum1.STORE_NAME = store.NAME;
sum1.SUMMURY_FEE = totalSlowFee;
sum1.SUMMURY_FEE_TYPE = (int)SUMMURY_FEE_TYPE.SLOW_FEE;
sum1.SUMMURY_DATE = DateTime.Today;
sum1.NOTE = Constants.SUMMURY_SLOW_FEE;
sum1.CREATED_DATE = DateTime.Now;
sum1.CREATED_BY = Constants.DUMMY_USER;
sum1.UPDATED_DATE = DateTime.Now;
sum1.UPDATED_BY = Constants.DUMMY_USER;
db.SummaryPayFeeMonthlies.Add(sum1);
SummaryPayFeeMonthly sum2 = new SummaryPayFeeMonthly();
sum2.STORE_ID = store.ID;
sum2.STORE_NAME = store.NAME;
sum2.SUMMURY_FEE = totalDebtFee;
sum2.SUMMURY_FEE_TYPE = (int)SUMMURY_FEE_TYPE.DEBT_FEE;
sum2.SUMMURY_DATE = DateTime.Today;
sum2.NOTE = Constants.SUMMURY_DEBT_FEE;
sum2.CREATED_DATE = DateTime.Now;
sum2.CREATED_BY = Constants.DUMMY_USER;
sum2.UPDATED_DATE = DateTime.Now;
sum2.UPDATED_BY = Constants.DUMMY_USER;
db.SummaryPayFeeMonthlies.Add(sum2);
}
db.SaveChanges();
}
catch (System.Data.Entity.Validation.DbEntityValidationException e)
{
foreach (var eve in e.EntityValidationErrors)
{
Logger.Log(string.Format("Entity of type \"{0}\" in state \"{1}\" has the following validation errors:",
eve.Entry.Entity.GetType().Name, eve.Entry.State));
foreach (var ve in eve.ValidationErrors)
{
Logger.Log(string.Format("- Property: \"{0}\", Error: \"{1}\"",
ve.PropertyName, ve.ErrorMessage));
}
}
}
catch (Exception ex)
{
Logger.Log(ex.Message + Environment.NewLine + ex.StackTrace);
}
}
}
public static void CreateDummyInout()
{
using (var db = new RentBikeEntities())
{
var stores = db.Stores.Where(c => c.ACTIVE == true).ToList();
foreach (var store in stores)
{
if (db.InOuts.Any(c => c.INOUT_DATE == DateTime.Today && c.STORE_ID == store.ID))
continue;
InOut io = new InOut();
io.IN_AMOUNT = 0;
io.OUT_AMOUNT = 0;
io.CONTRACT_ID = 1;
io.PERIOD_ID = 1;
io.RENT_TYPE_ID = 3;
io.INOUT_TYPE_ID = 23;
io.PERIOD_DATE = DateTime.Today;
io.MORE_INFO = Constants.DUMMY_INOUT;
io.STORE_ID = store.ID;
io.SEARCH_TEXT = Constants.DUMMY_INOUT;
io.INOUT_DATE = DateTime.Now;
io.CREATED_BY = Constants.DUMMY_USER;
io.CREATED_DATE = DateTime.Now;
io.UPDATED_BY = Constants.DUMMY_USER;
io.UPDATED_DATE = DateTime.Now;
io.IS_DUMMY = true;
db.InOuts.Add(io);
}
db.SaveChanges();
}
}
public static void UpdatePeriodAmount()
{
using (var db = new RentBikeEntities())
{
var contracts = db.Contracts.Where(c => c.CONTRACT_STATUS == true).ToList();
foreach (var contract in contracts)
{
List<PayPeriod> payList = db.PayPeriods.Where(c => c.CONTRACT_ID == contract.ID).ToList();
foreach (PayPeriod pp in payList)
{
var inOutList = db.InOuts.Where(c => c.PERIOD_ID == pp.ID);
decimal total = inOutList.Select(c => c.IN_AMOUNT).DefaultIfEmpty(0).Sum();
pp.ACTUAL_PAY = total;
}
}
db.SaveChanges();
}
}
public static void CreatePayPeriod(RentBikeEntities db, int contractId, PayPeriod lastPeriod, bool bFirstCreate)
{
var contract = db.Contracts.FirstOrDefault(c => c.CONTRACT_STATUS == true && c.ID == contractId);
if (contract != null)
{
int multipleFee;
decimal increateFeeCar;
decimal increateFeeEquip;
decimal increateFeeOther;
CalculatePeriodFee(db, contract, out multipleFee, out increateFeeCar, out increateFeeEquip, out increateFeeOther, bFirstCreate);
CreateOneMorePayPeriod(db, contract, lastPeriod, multipleFee, increateFeeCar, increateFeeEquip, increateFeeOther, bFirstCreate);
}
}
private static void CalculatePeriodFee(RentBikeEntities db, Contract contract, out int multipleFee, out decimal increateFeeCar, out decimal increateFeeEquip, out decimal increateFeeOther, bool bFirstCreate)
{
multipleFee = Convert.ToInt32(Decimal.Floor(contract.CONTRACT_AMOUNT / 100000));
if (contract.FEE_PER_DAY == 0)
{
increateFeeCar = increateFeeEquip = increateFeeOther = 0;
}
else
{
if (bFirstCreate)
{
increateFeeCar = increateFeeEquip = increateFeeOther = contract.FEE_PER_DAY * 10;
}
else
{
increateFeeCar = increateFeeEquip = (contract.FEE_PER_DAY * 10) + (multipleFee * 50 * 10);
//increateFeeEquip = (contract.FEE_PER_DAY * 10) + (multipleFee * 100 * 10);
increateFeeOther = (contract.FEE_PER_DAY * 10);
}
}
}
public static Contract GetContractByLicenseNo(string licenseNo)
{
using (var db = new RentBikeEntities())
{
var customer = db.Customers.FirstOrDefault(c => c.LICENSE_NO == licenseNo);
if (customer != null)
{
Contract contract = db.Contracts.FirstOrDefault(c => c.CUSTOMER_ID == customer.ID && c.CONTRACT_STATUS == true);
if (contract != null)
{
return contract;
}
}
}
return null;
}
public static void AutoUpdateAmountPayPeriod()
{
using (var db = new RentBikeEntities())
{
var contracts = db.Contracts.Where(c => c.CONTRACT_STATUS == true).ToList();
foreach (var contract in contracts)
{
List<PayPeriod> listPayPeriod = db.PayPeriods.Where(c => c.CONTRACT_ID == contract.ID).ToList();
foreach (PayPeriod pp in listPayPeriod)
{
var inoutList = db.InOuts.Where(s => s.PERIOD_ID == pp.ID);
var sumPay = inoutList.Select(s => s.IN_AMOUNT).DefaultIfEmpty(0).Sum();
pp.ACTUAL_PAY = sumPay;
}
}
db.SaveChanges();
}
}
public static List <CONTRACT_FULL_VW> GetWarningData(string date, string searchText, int storeId)
{
using (var db = new RentBikeEntities())
{
//int totalRecord = 0;
List <CONTRACT_FULL_VW> dataList = new List <CONTRACT_FULL_VW>();
var st = db.CONTRACT_FULL_VW.Where(c => c.CONTRACT_STATUS == true && c.ACTIVE == true && c.IS_LOW_RECOVERABILITY == false);
if (storeId != 0)
{
st = st.Where(c => c.STORE_ID == storeId);
}
st = st.OrderByDescending(c => c.ID);
string searchDate = string.Empty;
if (!string.IsNullOrEmpty(date))
{
searchDate = Convert.ToDateTime(date).ToString("yyyyMMdd");
}
var lstPeriod = db.PayPeriods.Where(s => s.STATUS == true).ToList();
foreach (CONTRACT_FULL_VW c in st)
{
var inOutList = db.InOuts.Where(s => s.CONTRACT_ID == c.ID).ToList();
c.PAYED_TIME = 0;
c.PAY_DATE = c.RENT_DATE;
c.DAY_DONE = DateTime.Now.Subtract(c.PAY_DATE).Days;
DateTime nowDate = DateTime.Today;
if (!string.IsNullOrEmpty(date))
{
nowDate = Convert.ToDateTime(date);
}
string contactId = c.ID.ToString();
var tmpLstPeriod = lstPeriod.Where(s => s.CONTRACT_ID == c.ID).ToList();
if (tmpLstPeriod != null)
{
decimal paidAmount = tmpLstPeriod.Where(s => s.ACTUAL_PAY > 0).Select(s => s.ACTUAL_PAY).DefaultIfEmpty(0).Sum();
int paidNumberOfFee = 0;
bool paidFull = false;
foreach (PayPeriod pp in tmpLstPeriod)
{
c.PERIOD_MESSAGE = GetPeriodMessage(tmpLstPeriod, nowDate);
if (pp.AMOUNT_PER_PERIOD == 0)
{
c.OVER_DATE = 0;
break;
}
paidAmount -= pp.AMOUNT_PER_PERIOD;
if (paidAmount >= 0)
{
paidNumberOfFee += 1;
}
if (paidAmount <= 0)
{
c.OVER_DATE = nowDate.Subtract(pp.PAY_DATE).Days;
if (paidAmount < 0 && tmpLstPeriod.Any(s => s.PAY_DATE == pp.PAY_DATE.AddDays(9)))
{
c.OVER_DATE = nowDate.Subtract(pp.PAY_DATE).Days + 2;
}
c.PAY_DATE = pp.PAY_DATE;
c.PERIOD_ID = pp.ID;
if (paidAmount == 0 || c.OVER_DATE <= 0)
{
paidFull = true;
}
break;
}
}
c.PAYED_TIME = paidNumberOfFee;
c.DAY_DONE = DateTime.Now.Subtract(c.RENT_DATE).Days + 1;
if (string.IsNullOrEmpty(date) || DateTime.Now.Subtract(Convert.ToDateTime(date)).Days <= 0)
{
if (paidFull && c.OVER_DATE <= 0)
{
c.CSS_CLASS = "background-green";
}
else if (c.OVER_DATE > 10)
{
c.CSS_CLASS = "background-red";
}
}
else
{
if (c.OVER_DATE <= 0)
{
var inout = inOutList.Where(s => s.PERIOD_DATE.ToString("yyyyMMdd").Equals(nowDate.ToString("yyyyMMdd"))).OrderByDescending(s => s.INOUT_DATE).FirstOrDefault();
if (inout != null && inout.INOUT_DATE.HasValue && inout.INOUT_DATE.Value.Subtract(nowDate).Days > 0)
{
c.CSS_CLASS = "background-amber";
}
else
{
c.CSS_CLASS = "background-green";
}
}
else
{
c.CSS_CLASS = "background-red";
}
}
if (c.FEE_PER_DAY == 0)
{
c.CSS_CLASS = "background-green";
}
c.RENT_TYPE_NAME = ReBuildRentTypeName(c);
if (!string.IsNullOrEmpty(searchDate))
{
if (tmpLstPeriod.Any(s => s.PAY_DATE.ToString("yyyyMMdd").Equals(searchDate)))
{
c.FEE_PER_DAY = tmpLstPeriod.FirstOrDefault(s => s.PAY_DATE.ToString("yyyyMMdd").Equals(searchDate)).AMOUNT_PER_PERIOD;
dataList.Add(c);
}
}
else if (tmpLstPeriod.Any(s => s.PAY_DATE.ToString("yyyyMMdd").Equals(DateTime.Now.ToString("yyyyMMdd"))))
{
c.FEE_PER_DAY = tmpLstPeriod.FirstOrDefault(s => s.PAY_DATE.ToString("yyyyMMdd").Equals(DateTime.Now.ToString("yyyyMMdd"))).AMOUNT_PER_PERIOD;
dataList.Add(c);
}
}
}
if (!string.IsNullOrEmpty(searchText))
{
dataList = dataList.Where(s => s.SEARCH_TEXT.ToLower().Contains(searchText.ToLower()) ||
s.CUSTOMER_NAME.ToLower().Contains(searchText.ToLower())).ToList();
}
return(dataList.OrderBy(c => c.DAY_DONE).ToList());
}
}