public void ValidatePaymentOnDeleting()
{
if (this.IsCashReported)
{
ValidationErrors.Add("PaymentValue", "Не може да се избрише плаќање кое е раздолжено со касов извештај!");
return;
}
if (this.IsFactured)
{
ValidationErrors.Add("PaymentValue", "Не може да се избрише плаќање за кое е фактурирана брокеража!");
return;
}
Rate currentRate = Rate.Get(this.RateID);
string query = "SELECT r.Number FROM Payments p, Rates r WHERE p.RateID = r.ID AND r.PolicyItemID = " + currentRate.PolicyItemID + " ORDER BY r.Number";
DataClassesDataContext dc = new DataClassesDataContext();
List <int> lst = dc.ExecuteQuery <int>(query).ToList();
if (lst != null)
{
if (lst.Count > 0)
{
if (lst.Last() != currentRate.Number)
{
ValidationErrors.Add("PaymentValue", "Не може да се избрише плаќање кое не е последно за полисата!");
return;
}
}
}
}
public static List <FinCardSaldo> GetForFinCard(int clientID, string itemsType)
{
DataClassesDataContext dc = new DataClassesDataContext();
string query = string.Empty;
query = @"select p.id, pi.policynumber, p.applicationdate, pi.premiumvalue as dolzi,
isnull((select sum(pay.value) from policies p1, policyitems pi1, rates r, payments pay
where pi1.policyid=p1.id and r.policyitemid=pi1.id
and pay.rateid=r.id
and p1.id=p.id),0) as pobaruva,
pi.premiumvalue - isnull((select sum(pay.value) from policies p1, policyitems pi1, rates r, payments pay
where pi1.policyid=p1.id and r.policyitemid=pi1.id
and pay.rateid=r.id
and p1.id=p.id),0) as saldo
from policies p, policyitems pi
where pi.policyid=p.id
and p.clientid = " + clientID +
@" order by p.applicationdate desc";
List <FinCardSaldo> lst = dc.ExecuteQuery <FinCardSaldo>(query).ToList();
if (itemsType == "OpenItems")
{
lst = lst.Where(c => c.dolzi != c.pobaruva).OrderBy(c => c.applicationdate).ToList();
}
return(lst);
}
public static List <FactureItemGrouped> GetForCashPayments(int companyID, DateTime fromDate, DateTime toDate)
{
DataClassesDataContext dcdc = new DataClassesDataContext();
DateTime dt1 = toDate;
DateTime dt2 = fromDate;
int d1 = dt1.Day;
int m1 = dt1.Month;
int y1 = dt1.Year;
int d2 = dt2.Day;
int m2 = dt2.Month;
int y2 = dt2.Year;
string query2 = @" SELECT count(p.ID)as PaymentsCount,sum(cpi.value) as TotalValue,ist.ID as InsuranceSubTypeID
FROM CashPayments p
INNER JOIN Policies pol ON p.policyID = pol.ID
INNER JOIN PolicyItems poli ON pol.ID = poli.policyID
INNER JOIN CashPaymentItems cpi ON p.ID= cpi.CashPaymentID
INNER JOIN InsuranceSubTypes ist ON ist.ID = poli.InsuranceSubTypeID
INNER JOIN InsuranceCompanies ic ON ic.ID =pol.InsuranceCompanyID
WHERE p.Discarded = 0 and ic.ID= " + companyID +
@" AND pol.ApplicationDate <= '" + y1 + - +m1 + - +d1 +
@"' AND pol.ApplicationDate >= '" + y2 + - +m2 + - +d2 +
@"'GROUP BY ist.ID";
List <FactureItemGrouped> fit = dcdc.ExecuteQuery <FactureItemGrouped>(query2).ToList();
return(fit);
}
public static decimal GetPaidValueForRateExtend(int rateID)
{
DataClassesDataContext dc = new DataClassesDataContext();
string query = @"SELECT isnull(SUM(Value),0) AS paidValue FROM Payments p
WHERE p.RateID = " + rateID;
decimal paidValue = 0;
paidValue = dc.ExecuteQuery <decimal>(query).SingleOrDefault();
return(paidValue);
}
public void ValidatePayment()
{
if (this.Value <= 0)
{
ValidationErrors.Add("PaymentValue", "Мора да се внесе вредност поголема од 0!");
return;
}
if (this.IsCashReported)
{
ValidationErrors.Add("PaymentValue", "Не може да се менува плаќање кое е раздолжено со касов извештај!");
return;
}
if (this.IsFactured)
{
ValidationErrors.Add("PaymentValue", "Не може да се менува плаќање за кое е фактурирана брокеража!");
return;
}
Rate rate = Rate.Get(this.RateID);
if (this.Value > rate.Value)
{
ValidationErrors.Add("PaymentValue", "Не може да се внесе поголем износ од вредноста на ратата!");
return;
}
decimal oldValuePerPayment = 0;
decimal paidValueForRate = 0;
string queryOldValue = @"SELECT Value FROM Payments WHERE ID = " + this.ID;
DataClassesDataContext dc = new DataClassesDataContext();
oldValuePerPayment = dc.ExecuteQuery <decimal>(queryOldValue).SingleOrDefault();
string queryGetPaidValueForRate = @"SELECT PaidValue FROM Rates WHERE ID = " + this.RateID;
paidValueForRate = dc.ExecuteQuery <decimal>(queryGetPaidValueForRate).SingleOrDefault();
decimal minValue = paidValueForRate - oldValuePerPayment;
decimal maxValuePerPayment = rate.Value - minValue;
if (this.Value > maxValuePerPayment)
{
ValidationErrors.Add("PaymentValue", "Не може да се внесе поголем износ од преостаната вредност за плаќање на ратата!");
return;
}
}
/// <summary>
/// Gi vrakja site funkcii za opredelena rolja
/// </summary>
/// <param name="roleID">int roleID</param>
/// <returns>Vrakja numerirana lista (IEnumerable)</returns>
/// Se povikuva vo EmployeeCreators\EmployeeController.cs i
/// UserManagementControllers\RolesManagementController.cs
public static IEnumerable <Function> GetFucntionsByRole(int roleID)
{
DataClassesDataContext dc = new DataClassesDataContext();
string query = @"SELECT f.ID, f.Name, f.Description
FROM Roles r
INNER JOIN RolesFunctions rs ON r.ID = rs.RoleID
INNER JOIN Functions f ON rs.FunctionId = f.ID
WHERE r.ID= " + roleID;
return(dc.ExecuteQuery <Function>(query));
}
// TODO: Da se preprave u LINQ
/// <summary>
/// Vrakja lista so "deca" ma Userot
/// </summary>
/// <param name="userId">int userID</param>
/// <returns>Vrakja numerirana lista (IEnumerable)</returns>
/// se povikuva vo UsersManagementControllers\RolesManagementController.cs
public static IEnumerable <User> GetChildUsers(int userId)
{
DataClassesDataContext dc = new DataClassesDataContext();
string query = @"SELECT u2.ID, u2.RoleID, u2.Username
FROM Users u1
INNER JOIN UsersParentships up ON u1.ID = up.ParentID
INNER JOIN Users u2 ON up.ChildId= u2.ID
WHERE u1.ID= " + userId;
return(dc.ExecuteQuery <User>(query));
}
// TODO: Da se preprave u LINQ
/// <summary>
/// Gi vrakja funkciite koi gi ima user-ot,a ne se vo roljata
/// </summary>
/// <param name="userId">int userID</param>
/// <returns>Vrakja numerirana lista (IEnumerable)</returns>
/// se povikuva vo UsersManagementControllers\RolesManagementController.cs
public static IEnumerable <Function> GetCustomFunctions(int userId)
{
DataClassesDataContext dc = new DataClassesDataContext();
string query = @"SELECT f.ID, f.Name, f.Description,f.CanBeGiven
FROM Users u
INNER JOIN UsersFunctions uf ON u.ID = uf.UserID
INNER JOIN Functions f ON uf.FunctionId = f.ID
WHERE u.ID= " + userId;
return(dc.ExecuteQuery <Function>(query));
}
public static List <PolicyItem> GetForRollBackFromPreviousMonths(DateTime startDate, int insuranceCompanyID, int branchID)
{
string query = @"select pitems.* from policies p
INNER JOIN policyitems pitems ON p.id = pitems.policyid
where p.applicationdate<'" + startDate.Year + - +startDate.Month + - +startDate.Day +
@"'and pitems.IsRollBacked = 0 and p.insurancecompanyid=" + insuranceCompanyID +
@" and p.BranchID = " + branchID;
DataClassesDataContext dcdc = new DataClassesDataContext();
List <PolicyItem> policyNumbers = dcdc.ExecuteQuery <PolicyItem>(query).ToList();
return(policyNumbers);
}
public static List <PolicyItem> GetForGroupFactures(DateTime startDate, DateTime endDate, int clientID)
{
string query = @"select pi.* from clients c
INNER JOIN policies p ON c.id = p.clientID
INNER JOIN policyitems pi ON pi.policyID = p.ID
WHERE p.discard = 0 and pi.IsFactureCreated = 0 and
p.ApplicationDate >='" + startDate.Year + - +startDate.Month + - +startDate.Day +
@"' and p.applicationdate<='" + endDate.Year + - +endDate.Month + - +endDate.Day +
@"'and c.ID =" + clientID;
DataClassesDataContext dcdc = new DataClassesDataContext();
List <PolicyItem> policyItems = dcdc.ExecuteQuery <PolicyItem>(query).ToList();
return(policyItems);
}
public static List <Client> GetForGroupFactures(DateTime startDate, DateTime endDate)
{
string query = @"select c.name,c.id from clients c
INNER JOIN policies p ON c.id = p.clientID
INNER JOIN policyitems pi ON pi.policyID = p.ID
WHERE p.discard = 0 and pi.IsFactureCreated = 0 and pi.IsPaidInBrokerHouse=1 and
p.ApplicationDate >='" + startDate.Year + - +startDate.Month + - +startDate.Day +
@"' and p.applicationdate<='" + endDate.Year + - +endDate.Month + - +endDate.Day +
@"'group by c.name,c.id";
DataClassesDataContext dcdc = new DataClassesDataContext();
List <Client> clients = dcdc.ExecuteQuery <Client>(query).ToList();
return(clients);
}
public static List <Payment> GetByPolicyItemIDExtend(int policyItemID)
{
DataClassesDataContext dc = new DataClassesDataContext();
string query = "SELECT p.* FROM Payments p, Rates r WHERE p.RateID = r.ID AND r.PolicyItemID = " + policyItemID + " ORDER BY p.ID";
List <Payment> lst = dc.ExecuteQuery <Payment>(query).ToList();
if (lst != null)
{
return(lst);
}
else
{
return(new List <Payment>());
}
}
public static List <Rate> GetByPolicyItemIDExtend(int policyItemID)
{
DataClassesDataContext dc = new DataClassesDataContext();
string query = "SELECT r.* from Rates r WHERE r.PolicyItemID = " + policyItemID + " ORDER BY r.ID";
List <Rate> lstRates = dc.ExecuteQuery <Rate>(query).ToList();
if (lstRates != null)
{
return(lstRates);
}
else
{
return(new List <Rate>());
}
}
public static decimal GetPaidValueForPolicyItemExtend(int policyItemID)
{
decimal retValue = 0;
DataClassesDataContext dc = new DataClassesDataContext();
string query = @"select sum(payments.value) as paidvalue
from payments, rates, policyitems
where payments.rateid=rates.id
and rates.policyitemid=policyitems.id
and policyitems.id = " + policyItemID;
decimal?dQuery = dc.ExecuteQuery <decimal?>(query).SingleOrDefault();
if (dQuery != null)
{
retValue = (decimal)dQuery;
}
return(retValue);
}
public static List <InsuranceCompany> GetForFacturing(DateTime fromDate, DateTime toDate)
{
string query = @"select insuranceCompanyid from policies p
INNER JOIN policyitems pitems ON p.id = pitems.policyid
INNER JOIN cashpayments cp ON cp.policyid = pitems.id
where p.applicationdate >='" + fromDate.Year + - +fromDate.Month + - +fromDate.Day +
@"' and p.applicationdate<='" + toDate.Year + - +toDate.Month + - +toDate.Day +
@"'group by insuranceCompanyid";
DataClassesDataContext dcdc = new DataClassesDataContext();
List <int> idList = dcdc.ExecuteQuery <int>(query).ToList();
List <InsuranceCompany> icList = new List <InsuranceCompany>();
foreach (int id in idList)
{
icList.Add(InsuranceCompany.Get(id));
}
return(icList);
}
public static List <FinCardSaldo> GetForFinCard(int clientID)
{
DataClassesDataContext dc = new DataClassesDataContext();
string query = @"select p.id, pi.policynumber, p.applicationdate, pi.premiumvalue as dolzi,
isnull((select sum(pay.value) from policies p1, policyitems pi1, rates r, payments pay
where pi1.policyid=p1.id and r.policyitemid=pi1.id
and pay.rateid=r.id
and p1.id=p.id),0) as pobaruva,
isnull(pi.premiumvalue - (select sum(pay.value) from policies p1, policyitems pi1, rates r, payments pay
where pi1.policyid=p1.id and r.policyitemid=pi1.id
and pay.rateid=r.id
and p1.id=p.id),0) as saldo
from policies p, policyitems pi
where pi.policyid=p.id
and p.clientid = " + clientID +
@" order by p.id";
List <FinCardSaldo> lst = dc.ExecuteQuery <FinCardSaldo>(query).ToList();
return(lst);
}
public static int GetPolicyIDForPolicyItemsWithMaxCount()
{
string query = @"select policyid
from policyitems
group by policyid
order by count(1)";
//List<int> myList = new List<int>();
DataClassesDataContext db = new DataClassesDataContext();
List <int> highCount = db.ExecuteQuery <int>(query).ToList();
//IEnumerable<int> neso = db.ExecuteQuery<int>(query);
if (highCount.Count == 0)
{
return(0);
}
else
{
return(highCount.Last());
}
}
public static List <PolicyItem> GetFromPreivousMonths(DateTime startDate, int companyID)
{
DataClassesDataContext dcdc = new DataClassesDataContext();
List <PolicyItem> policyItemsList = new List <PolicyItem>();
string queryPayments = @"select pitems.id from policies p
INNER JOIN policyitems pitems ON p.id = pitems.policyid
INNER JOIN rates r ON r.policyitemid=pitems.id
INNER JOIN payments pay ON pay.rateid = r.id
where p.discard = 0 and pay.date<'" + startDate.Year + - +startDate.Month + - +startDate.Day +
@"'and pay.isfactured = 0 and p.insurancecompanyid=" + companyID + " group by pitems.id";
IEnumerable <int> idPaymentsList = dcdc.ExecuteQuery <int>(queryPayments);
foreach (int id in idPaymentsList)
{
policyItemsList.Add(PolicyItem.Get(id));
}
return(policyItemsList);
}
public static void UpdateExtend(Rate oldEntity, Rate newEntity)
{
decimal polValue = PolicyItem.Get(newEntity.PolicyItemID).PremiumValue;
DataClassesDataContext dc = new DataClassesDataContext();
string value = newEntity.Value.ToString();
value = value.Replace(',', '.');
string query = "Update Rates SET Value = " + value + " , Date = '" + newEntity.Date.Year + - +newEntity.Date.Month + - +newEntity.Date.Day + "' WHERE ID = " + newEntity.ID;
dc.ExecuteCommand(query);
//Cascade update rest rates
string selQuery = @"Select r.* FROM Rates r WHERE r.PolicyItemID = " + newEntity.PolicyItemID + " ORDER BY r.Number";
List <Rate> lstAllRates = dc.ExecuteQuery <Rate>(selQuery).ToList();
if (lstAllRates != null)
{
if (lstAllRates.Count > 0)
{
List <Rate> lstPreviousRates = lstAllRates.Where(c => c.Number <= oldEntity.Number).ToList();
decimal totValue = 0;
foreach (Rate rate in lstPreviousRates)
{
totValue += rate.Value;
}
List <Rate> lstRateForUpdates = lstAllRates.Where(c => c.Number > oldEntity.Number).ToList();
if (lstRateForUpdates.Count > 0)
{
decimal rightValue = (polValue - totValue) / lstRateForUpdates.Count;
foreach (Rate rateU in lstRateForUpdates)
{
string valueU = rightValue.ToString();
valueU = valueU.Replace(',', '.');
string queryU = "Update Rates SET Value = " + valueU + " WHERE ID = " + rateU.ID;
dc.ExecuteCommand(queryU);
}
}
}
}
}
public static List <Payment> GetInPeriod(DateTime fromDate, DateTime toDate, List <int> lstBranhces,
List <int> lstInsuranceCompanies, List <int> lstInsuranceSubTypes, List <int> lstUsers, List <int> lstMarketingAgents)
{
DateTime dt1 = fromDate;
DateTime dt2 = toDate;
int d1 = dt1.Day;
int m1 = dt1.Month;
int y1 = dt1.Year;
int d2 = dt2.Day;
int m2 = dt2.Month;
int y2 = dt2.Year;
DataClassesDataContext dc = new DataClassesDataContext();
string query = @"SELECT pay.* from payments pay, rates r, policyitems pi, policies p
where r.policyitemid=pi.id and pi.policyid=p.id and pay.rateid=r.id and p.branchid in (" + ReportFinPolicies.GetFromIDs(lstBranhces) + ") " +
@" and p.userid in (" + ReportFinPolicies.GetFromIDs(lstUsers) + ") " +
@" and p.insurancecompanyid in (" + ReportFinPolicies.GetFromIDs(lstInsuranceCompanies) + ") " +
@" and pi.insurancesubtypeid in (" + ReportFinPolicies.GetFromIDs(lstInsuranceSubTypes) + ") " +
@" and pay.date>='" + y1 + - +m1 + - +d1 + "'" +
@" and pay.date<='" + y2 + - +m2 + - +d2 + "'" +
@" and p.discard = 0 ";
List <Payment> lst = dc.ExecuteQuery <Payment>(query).ToList();
return(lst);
}
