public static void CreateFileForPeriodAndBank(int bankID, DateTime startDate, DateTime endDate)
{
DataClassesDataContext dcdc = new DataClassesDataContext();
DateTime dt1 = startDate;
DateTime dt2 = endDate;
int d1 = dt1.Day;
int m1 = dt1.Month;
int y1 = dt1.Year;
int d2 = dt2.Day;
int m2 = dt2.Month;
int y2 = dt2.Year;
PDFCreators pdf = new PDFCreators(false, 25, 25, 25, 25);
pdf.OpenPDF();
pdf.SetTitle(" ");
pdf.SetTitle(" ");
pdf.SetTitle("Слипови во период " + startDate.ToShortDateString() + " - " + endDate.ToShortDateString());
List <Slip> slipList = Slip.GetForPeriodAndBank(startDate, endDate, bankID);
slipList = slipList.OrderBy(c => c.Date).ThenBy(c => c.SlipNumber).ToList();
decimal totalValue = 0;
decimal partialValue = 0;
decimal dayTotalValue = 0;
decimal dayPartialValue = 0;
int orderNumber = 0;
bool differentDays = false;
// int dayOrderNumber = 0;
// int monthOrderNumber = 0;
string[] headersP = { "РБ", "Број на слип", "Број на полиса", "Вредност", "Провизија", "Вредност без провизија", "Дата" };
float[] policyColumnsWidths = { 5, 20, 20, 15, 10, 15, 15 };
TypeCode[] codes = { TypeCode.String, TypeCode.String, TypeCode.String, TypeCode.Decimal, TypeCode.Decimal, TypeCode.Decimal, TypeCode.DateTime };
pdf.CreateTable(7, false, headersP, "", policyColumnsWidths);
DateTime currentDate;
if (slipList.Count > 0)
{
currentDate = slipList[0].Date;
}
else
{
currentDate = DateTime.Today;
}
foreach (Slip s in slipList)
{
if (s.Date != currentDate)
{
differentDays = true;
object[] vals = new object[7];
vals[0] = "";
vals[1] = "Вкупно";
vals[2] = "";
vals[3] = String.Format("{0:#,0.00}", dayTotalValue);
vals[4] = "";
vals[5] = String.Format("{0:#,0.00}", dayPartialValue);
vals[6] = "";
pdf.AddDataRow1(vals, 7, codes);
dayPartialValue = 0;
dayTotalValue = 0;
currentDate = s.Date;
orderNumber = 0;
totalValue += s.TotalValue;
partialValue += s.PartialValue;
}
else
{
orderNumber++;
object[] vals2 = new object[7];
vals2[0] = orderNumber;
vals2[1] = s.SlipNumber;
vals2[2] = s.PolicyNumber;
vals2[3] = String.Format("{0:#,0.00}", s.TotalValue);
vals2[4] = String.Format("{0:#,0.00}", s.ProvisionRate.Provision);
vals2[5] = String.Format("{0:#,0.00}", s.PartialValue);
vals2[6] = s.Date.ToShortDateString();
pdf.AddDataRow1(vals2, 7, codes);
totalValue += s.TotalValue;
partialValue += s.PartialValue;
dayTotalValue += s.TotalValue;
dayPartialValue += s.PartialValue;
}
}
//if (differentDays) {
// object[] vals5 = new object[7];
// vals5[0] = "";
// vals5[1] = "Вкупно";
// vals5[2] = "";
// vals5[3] = String.Format("{0:#,0.00}", dayTotalValue);
// vals5[4] = "";
// vals5[5] = String.Format("{0:#,0.00}", dayPartialValue);
// vals5[6] = "";
// pdf.AddDataRow1(vals5, 7, codes);
//}
object[] vals1 = new object[7];
vals1[0] = "";
vals1[1] = "Вкупно";
vals1[2] = "";
vals1[3] = String.Format("{0:#,0.00}", totalValue);
vals1[4] = "";
vals1[5] = String.Format("{0:#,0.00}", partialValue);
vals1[6] = "";
pdf.AddDataRow1(vals1, 7, codes);
pdf.AddTable();
pdf.FinishPDF();
}
public static void ReportsForPaymentsTypesGroupedByInsuranceCompany(DateTime fromDate, DateTime toDate)
{
List <InsuranceCompany> icList = new List <InsuranceCompany>();
icList = InsuranceCompany.Select().ToList();
PDFCreators pdf = new PDFCreators(false, 25, 25, 25, 25);
pdf.OpenPDF();
pdf.SetTitle(" ");
pdf.SetTitle("ЗБИРНА СТАТИСТИКА ЗА ПРОДАЖБА ПО ТИП НА ПЛАЌАЊЕ КАЈ ОСИГУРИТЕЛНА КОМПАНИЈА");
pdf.SetTitle("ЗА ПЕРИОД " + fromDate.ToShortDateString() + " - " + toDate.ToShortDateString());
pdf.SetTitleLeft("");
string[] headers = new string[4];
headers[0] = "Реден број";
headers[1] = "Тип на плаќање";
headers[2] = "Број на полиси";
headers[3] = "Вкупен износ";
TypeCode[] codes = new TypeCode[headers.Count()];
codes[0] = TypeCode.Int32;
codes[1] = TypeCode.String;
codes[2] = TypeCode.Int32;
codes[3] = TypeCode.Decimal;
float[] policyColumnsWidths = new float[headers.Count()];
policyColumnsWidths[0] = 10;
policyColumnsWidths[1] = 50;
policyColumnsWidths[2] = 20;
policyColumnsWidths[3] = 20;
List <Broker.DataAccess.PaymentType> lstPaymentTypes = Broker.DataAccess.PaymentType.Table.ToList();
foreach (InsuranceCompany ic in icList)
{
int tot = 0;
decimal total = 0;
pdf.SetTitleLeft(ic.Name.ToUpper());
pdf.CreateTableWithBorder(headers.Count(), false, headers, "", policyColumnsWidths);
int counter = 1;
foreach (Broker.DataAccess.PaymentType pt in lstPaymentTypes)
{
int count = 0;
decimal sum = Broker.DataAccess.Payment.GetSumByPaymentTypeAndInsuranceCompanyInPeriod(fromDate, toDate, ic.ID, pt, out count);
object[] vals = new object[headers.Count()];
vals[0] = counter;
vals[1] = pt.Name;
vals[2] = count;
vals[3] = String.Format("{0:#,0.00}", sum);
tot += count;
total += sum;
pdf.AddDataRowWithBorder(vals, headers.Count(), codes);
counter++;
}
object[] valsTot = new object[headers.Count()];
valsTot[0] = "";
valsTot[1] = "ВКУПНО:";
valsTot[2] = tot;
valsTot[3] = String.Format("{0:#,0.00}", total);
pdf.AddDataRowWithBorder(valsTot, headers.Count(), codes);
pdf.AddTable();
pdf.SetTitleLeft(" ");
}
pdf.FinishPDF();
}
public static void GetNumberStatisticsForTimePeriod(DateTime startDate, DateTime endDate)
{
DataClassesDataContext dcdc = new DataClassesDataContext();
DateTime dt1 = startDate;
DateTime dt2 = endDate;
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 s = @"select it.ID as TypeID,ic.ID as CompanyID,sum(pitems.premiumvalue) as TotalSum,count(pitems.id) as TotalCount, it.IsForLive as IsForLive from policyitems
pitems INNER JOIN policies p ON pitems.policyid =p.id
INNER JOIN insurancesubtypes ist ON pitems.InsuranceSubTypeID = ist.ID
INNER JOIN insurancetypes it ON it.ID = ist.insurancetypeID
INNER JOIN insuranceCompanies ic ON ic.ID = p.insuranceCompanyID
where p.applicationdate>='" + y1 + - +m1 + - +d1 + "' and p.applicationdate<='" + y2 + - +m2 + - +d2 +
@"'and p.discard = 0
group by it.ID, ic.ID,it.IsForLive
order by it.ID";
List <ReportClass> summaryList = dcdc.ExecuteQuery <ReportClass>(s).ToList();
List <InsuranceCompany> icList = new List <InsuranceCompany>();
foreach (ReportClass rc in summaryList)
{
InsuranceCompany ic = icList.Where(company => company.ID == rc.CompanyID).SingleOrDefault();
if (ic == null)
{
icList.Add(InsuranceCompany.Get(rc.CompanyID));
}
}
icList = icList.OrderBy(c => c.ID).ToList();
PDFCreators pdf = new PDFCreators(false, 25, 25, 25, 25);
pdf.OpenPDF();
pdf.SetTitle(" ");
pdf.SetTitle(" ");
pdf.AddJDBLogo(660, 480);
pdf.SetTitle("ЗБИРНА СТАТИСТКА ЗА БРОЈ НА ПОЛИСИ ПО КЛАСИ НА ОСИГУРУВАЊЕ");
pdf.SetTitle("ЗА ПЕРИОД " + startDate.ToShortDateString() + " - " + endDate.ToShortDateString());
pdf.SetTitleLeft("");
string[] headers = new string[icList.Count + 3];
headers[0] = "";
headers[1] = "Класи на осигурување";
for (int i = 0; i < icList.Count; i++)
{
headers[2 + i] = icList[i].ShortName;
}
headers[icList.Count + 2] = "Вкупно";
TypeCode[] codes = new TypeCode[headers.Count()];
codes[0] = TypeCode.Int32;
codes[1] = TypeCode.String;
for (int i = 0; i <= icList.Count; i++)
{
codes[2 + i] = TypeCode.Int32;
}
float[] policyColumnsWidths = new float[headers.Count()];
policyColumnsWidths[0] = 2;
policyColumnsWidths[1] = 18;
for (int i = 0; i <= icList.Count; i++)
{
policyColumnsWidths[2 + i] = 80 / (icList.Count + 1);
}
int count = 0;
List <InsuranceType> itList = InsuranceType.Select().OrderBy(c => c.ID).ToList();
pdf.CreateTableWithBorder(headers.Count(), false, headers, "", policyColumnsWidths);
foreach (InsuranceType it in itList)
{
decimal totalTypeCount = 0;
List <ReportClass> subList = summaryList.Where(sl => sl.TypeID == it.ID).ToList();
object[] vals = new object[headers.Count()];
vals[0] = ++count;
vals[1] = it.ShortName;
for (int i = 0; i < icList.Count; i++)
{
ReportClass rcItem = subList.Where(cl => cl.CompanyID == icList[i].ID).SingleOrDefault();
if (rcItem == null)
{
vals[i + 2] = 0;
}
else
{
vals[i + 2] = String.Format("{0:#,0}", rcItem.TotalCount);
totalTypeCount += rcItem.TotalCount;
}
}
vals[headers.Count() - 1] = String.Format("{0:#,0}", totalTypeCount);
pdf.AddDataRowWithBorder(vals, headers.Count(), codes);
}
// za kreiranje na vkupno
object[] totalVals = new object[headers.Count()];
totalVals[0] = "";
totalVals[1] = "Вкупно неживот";
decimal total = 0;
for (int i = 0; i < icList.Count; i++)
{
List <ReportClass> rcSubList = summaryList.Where(c => c.CompanyID == icList[i].ID && c.IsForLive == false).ToList();
decimal totalForCompany = 0;
foreach (ReportClass rc in rcSubList)
{
totalForCompany += rc.TotalCount;
}
totalVals[i + 2] = String.Format("{0:#,0}", totalForCompany);
total += totalForCompany;
}
totalVals[headers.Count() - 1] = String.Format("{0:#,0}", total);
pdf.AddDataRowWithBorder(totalVals, headers.Count(), codes);
object[] totalValsLive = new object[headers.Count()];
totalValsLive[0] = "";
totalValsLive[1] = "Вкупно живот";
decimal totalLive = 0;
for (int i = 0; i < icList.Count; i++)
{
List <ReportClass> rcSubList = summaryList.Where(c => c.CompanyID == icList[i].ID && c.IsForLive == true).ToList();
decimal totalForCompany = 0;
foreach (ReportClass rc in rcSubList)
{
totalForCompany += rc.TotalCount;
}
totalValsLive[i + 2] = String.Format("{0:#,0}", totalForCompany);
totalLive += totalForCompany;
}
totalValsLive[headers.Count() - 1] = String.Format("{0:#,0}", totalLive);
pdf.AddDataRowWithBorder(totalValsLive, headers.Count(), codes);
pdf.AddTable();
pdf.FinishPDF();
}
public static void PrintReportForASO(int insuranceCompanyID, DateTime startDate, DateTime endDate)
{
DateTime dt1 = startDate;
DateTime dt2 = endDate;
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 dcdc = new DataClassesDataContext();
PDFCreators pdf = new PDFCreators(false, 17, 17, 25, 25);
pdf.OpenPDF();
pdf.SetTitle(" ");
pdf.SetTitle(" ");
pdf.AddJDBLogo(660, 480);
pdf.SetTitle("Квартален извештај за Брокерските друштва");
pdf.SetTitle("за период " + startDate.ToShortDateString() + " - " + endDate.ToShortDateString());
if (insuranceCompanyID > 0)
{
InsuranceCompany ic = InsuranceCompany.Get(insuranceCompanyID);
pdf.SetTitle("Осигурителна компанија: " + ic.ShortName);
}
pdf.SetTitleLeft("");
string[] headers = new string[6];
headers[0] = "Класа/подкласа на осигурување";
headers[1] = "Шифра";
headers[2] = "Број на договори";
headers[3] = "Премија";
headers[4] = "Реализирана провизија";
headers[5] = "Пресметана провизија";
TypeCode[] codes = new TypeCode[headers.Count()];
codes[0] = TypeCode.String;
codes[1] = TypeCode.String;
codes[2] = TypeCode.Int32;
codes[3] = TypeCode.Decimal;
codes[4] = TypeCode.Decimal;
codes[5] = TypeCode.Decimal;
float[] policyColumnsWidths = new float[headers.Count()];
policyColumnsWidths[0] = 55;
policyColumnsWidths[1] = 5;
policyColumnsWidths[2] = 10;
policyColumnsWidths[3] = 10;
policyColumnsWidths[4] = 10;
policyColumnsWidths[5] = 10;
pdf.CreateTableWithBorder(headers.Count(), false, headers, "", policyColumnsWidths);
int totalNumberOfPolicies = 0;
decimal totalPremiumValue = 0;
decimal totalBrokValue = 0;
decimal totalRealBrokValue = 0;
List <InsuranceType> listInsuranceTypes = InsuranceType.Table.OrderBy(c => c.Code).ToList();
foreach (InsuranceType it in listInsuranceTypes)
{
object[] vals = new object[headers.Count()];
vals[0] = it.Name;
vals[1] = it.Code;
List <InsuranceSubType> listInsuranceSubTypes = InsuranceSubType.GetByInsuranceType(it.ID).OrderBy(c => c.Code).ToList();
int numberOfPolicies = 0;
int premiumValue = 0;
decimal brokValue = 0;
decimal realBrokValue = 0;
decimal tmpBrokValue = 0;
decimal tmpBrokBaseValue = 0;
decimal totFacturedValueInClass = 0;
decimal totFacturedBrokerageValuePerClass = 0;
//decimal tmpBrokValueInOtherClass = 0;
decimal k = 0;
foreach (InsuranceSubType ist in listInsuranceSubTypes)
{
string queryCountAndPremiumDef = string.Empty;
string queryCountAndPremiumFullPremiumValue = string.Empty;
string queryCountAndPremiumInOtherClasses = string.Empty;
string getCalculatedBrokerages = string.Empty;
string queryCalcBrokerageInOtherClases = string.Empty;
string queryRealPaidValues = string.Empty;
string queryGetFacturedBrokeragesPayments = string.Empty;
decimal tmpBrokValueInOtherClass = 0;
decimal tmpBrokBaseValueTot = 0;
decimal tmpBrokValueTot = 0;
if (insuranceCompanyID > 0)
{
queryCountAndPremiumFullPremiumValue = @"select count(pi1.id) as Count,
sum(cast(pi1.premiumvalue as int)) as SumPremiumValue
from policies p1 inner join policyitems pi1 on pi1.policyid=p1.id
where p1.discard=0
and pi1.insurancesubtypeid=" + ist.ID +
" and p1.insurancecompanyid = " + insuranceCompanyID +
@" and p1.applicationdate >='" + y1 + - +m1 + - +d1 + "' and p1.applicationdate<='" + y2 + - +m2 + - +d2 + "'";
queryCountAndPremiumDef = @"select 1, sum(cast(pei.Value as int)) as SumPremiumValue
from policies p ,policyitems pi,
policyextendinformations pei,controls c, controlappropriateinsurancesubtype caist
where p.discard=0
and caist.isactive=1
and pi.policyid=p.id
and caist.controlid=pei.controlid
and c.variabletypeid in (2,3,4)
and c.specialfieldtypeid = 2
and c.insurancesubtypeid=" + ist.ID +
@" and pei.policyitemid=pi.id
and pei.controlid=c.id
and pei.Value<>'0'
and p.insurancecompanyid = " + insuranceCompanyID +
@" and p.applicationdate >='" + y1 + - +m1 + - +d1 + "' and p.applicationdate<='" + y2 + - +m2 + - +d2 + "'";
queryCountAndPremiumInOtherClasses = @"select count(pi.id) as Count, sum(cast(pei.Value as int)) as SumPremiumValue
from policies p ,policyitems pi,
policyextendinformations pei,controls c, controlappropriateinsurancesubtype caist
where p.discard=0
and caist.insurancesubtypeid=" + ist.ID +
@" and pi.policyid=p.id
and caist.controlid=pei.controlid
and caist.isactive=1
and pei.policyitemid=pi.id
and pei.controlid=c.id
and pei.Value<>'0'
and p.insurancecompanyid = " + insuranceCompanyID +
@" and p.applicationdate >='" + y1 + - +m1 + - +d1 + "' and p.applicationdate<='" + y2 + - +m2 + - +d2 + "'";
getCalculatedBrokerages = @"select pi1.id, pi1.insurancesubtypeid,pi1.premiumvalue,
(select sum(convert(int,pei.Value))
from policyitems pi,
policyextendinformations pei,controls c, controlappropriateinsurancesubtype caist
where caist.isactive=1
and caist.controlid=pei.controlid
and c.variabletypeid in (2,3,4)
and c.specialfieldtypeid = 2
and pei.policyitemid=pi.id
and pei.controlid=c.id
and pei.Value<>'0'
and pi.id = pi1.id) as basicvalue,
case when pi1.brokerageid is not null then
(case when c1.islaw=1 then
(select b.percentageforlaws from brokerages b
where b.insurancesubtypeid=pi1.insurancesubtypeid and
b.id=pi1.brokerageid)
else
(select b.percentageforprivates from brokerages b
where b.insurancesubtypeid=pi1.insurancesubtypeid
and b.id=pi1.brokerageid)
end)
else
(case when pi1.packetbrokerageid is not null then
(case when c1.islaw=1 then
(select pist.BrokeragePecentageForPrivates from PacketsInsuranceSubTypes pist
where pist.insurancesubtypeid=pi1.insurancesubtypeid
and pist.id=pi1.packetbrokerageid)
else
(select pist.BrokeragePecentageForLaws from PacketsInsuranceSubTypes pist
where pist.insurancesubtypeid=pi1.insurancesubtypeid
and pist.id=pi1.packetbrokerageid)
end)
else 0
end)
end as brokeragepercentage
from policies p1 inner join policyitems pi1 on pi1.policyid=p1.id
inner join clients c1 on p1.clientid=c1.id
where p1.discard=0
and pi1.insurancesubtypeid=" + ist.ID +
@" and p1.insurancecompanyid = " + insuranceCompanyID +
@" and p1.applicationdate >='" + y1 + - +m1 + - +d1 + "' and p1.applicationdate<='" + y2 + - +m2 + - +d2 + "'";
queryCalcBrokerageInOtherClases = @"select pi.id, pei.Value,
case when pi.brokerageid is not null then
(case when c1.islaw=1 then
(select b.percentageforlaws from brokerages b
where b.insurancesubtypeid=pi.insurancesubtypeid and
b.id=pi.brokerageid)
else
(select b.percentageforprivates from brokerages b
where b.insurancesubtypeid=pi.insurancesubtypeid
and b.id=pi.brokerageid)
end)
else
(case when pi.packetbrokerageid is not null then
(case when c1.islaw=1 then
(select pist.BrokeragePecentageForPrivates from PacketsInsuranceSubTypes pist
where pist.insurancesubtypeid=pi.insurancesubtypeid
and pist.id=pi.packetbrokerageid)
else
(select pist.BrokeragePecentageForLaws from PacketsInsuranceSubTypes pist
where pist.insurancesubtypeid=pi.insurancesubtypeid
and pist.id=pi.packetbrokerageid)
end)
else 0
end)
end as brokeragepercentage
from policies p ,policyitems pi, clients c1,
policyextendinformations pei,controls c, controlappropriateinsurancesubtype caist
where p.discard=0
and p.clientid=c1.id
and caist.insurancesubtypeid=" + ist.ID +
@" and pi.policyid=p.id
and caist.controlid=pei.controlid
and caist.isactive=1
and pei.policyitemid=pi.id
and pei.controlid=c.id
and pei.Value<>'0' " +
@" and p.insurancecompanyid = " + insuranceCompanyID +
@" and p.applicationdate >='" + y1 + - +m1 + - +d1 + "' and p.applicationdate<='" + y2 + - +m2 + - +d2 + "'";
queryRealPaidValues = @"select sum(fcpvpist.paidvalue) as sumpaidvalue
from FacCollPaidValuesPerInsSubTypes fcpvpist,facturecollectedpaidvalues fcpv, factureitems fi, factures f
where fcpvpist.facturecollectedpaidvalueid=fcpv.id
and fcpv.factureitemid=fi.id
and fi.factureid=f.id
and f.discard=0
and fcpv.paiddate>='" + y1 + - +m1 + - +d1 + "' and fcpv.paiddate<='" + y2 + - +m2 + - +d2 + "'" +
@" and fcpvpist.insurancesubtypeid=" + ist.ID +
@" and f.insurancecompanyid=" + insuranceCompanyID;
queryGetFacturedBrokeragesPayments = @"select sum(ppist.brokeragevalue) as tmpValueDecimal from PaymentsPerInsSubTypes ppist, payments pa, rates r,
policyitems pi, policyitemfactureitems pifi, factureitems fi, factures f, policies p, facturepayments fp
where ppist.paymentid=pa.id
and pa.rateid=r.id
and r.policyitemid=pi.id
and pi.policyid=p.id
and pifi.policyitemid=pi.id
and pifi.factureitemid=fi.id
and fi.factureid=f.id
and f.discard=0
and fp.factureid=f.id
and fp.paymentid=pa.id
and p.discard=0
and p.applicationdate>='" + y1 + - +m1 + - +d1 + "'" +
@" and p.applicationdate<='" + y2 + - +m2 + - +d2 + "'" +
@" and p.insurancecompanyid=" + insuranceCompanyID +
@" and ppist.insurancesubtypeid=" + ist.ID;
}
else
{
queryCountAndPremiumFullPremiumValue = @"select count(pi1.id) as Count,
sum(cast(pi1.premiumvalue as int)) as SumPremiumValue
from policies p1 inner join policyitems pi1 on pi1.policyid=p1.id
where p1.discard=0
and pi1.insurancesubtypeid=" + ist.ID +
@" and p1.applicationdate >='" + y1 + - +m1 + - +d1 + "' and p1.applicationdate<='" + y2 + - +m2 + - +d2 + "'";
queryCountAndPremiumDef = @"select 1, sum(cast(pei.Value as int)) as SumPremiumValue
from policies p ,policyitems pi,
policyextendinformations pei,controls c, controlappropriateinsurancesubtype caist
where p.discard=0
and caist.isactive=1
and pi.policyid=p.id
and caist.controlid=pei.controlid
and c.variabletypeid in (2,3,4)
and c.specialfieldtypeid = 2
and c.insurancesubtypeid=" + ist.ID +
@" and pei.policyitemid=pi.id
and pei.controlid=c.id
and pei.Value<>'0'" +
@" and p.applicationdate >='" + y1 + - +m1 + - +d1 + "' and p.applicationdate<='" + y2 + - +m2 + - +d2 + "'";
queryCountAndPremiumInOtherClasses = @"select count(pi.id) as Count, sum(cast(pei.Value as int)) as SumPremiumValue
from policies p ,policyitems pi,
policyextendinformations pei,controls c, controlappropriateinsurancesubtype caist
where p.discard=0
and caist.insurancesubtypeid=" + ist.ID +
@" and pi.policyid=p.id
and caist.controlid=pei.controlid
and caist.isactive=1
and pei.policyitemid=pi.id
and pei.controlid=c.id
and pei.Value<>'0'" +
@" and p.applicationdate >='" + y1 + - +m1 + - +d1 + "' and p.applicationdate<='" + y2 + - +m2 + - +d2 + "'";
getCalculatedBrokerages = @"select pi1.id, pi1.insurancesubtypeid,pi1.premiumvalue,
(select sum(convert(int,pei.Value))
from policyitems pi,
policyextendinformations pei,controls c, controlappropriateinsurancesubtype caist
where caist.isactive=1
and caist.controlid=pei.controlid
and c.variabletypeid in (2,3,4)
and c.specialfieldtypeid = 2
and pei.policyitemid=pi.id
and pei.controlid=c.id
and pei.Value<>'0'
and pi.id = pi1.id) as basicvalue,
case when pi1.brokerageid is not null then
(case when c1.islaw=1 then
(select b.percentageforlaws from brokerages b
where b.insurancesubtypeid=pi1.insurancesubtypeid and
b.id=pi1.brokerageid)
else
(select b.percentageforprivates from brokerages b
where b.insurancesubtypeid=pi1.insurancesubtypeid
and b.id=pi1.brokerageid)
end)
else
(case when pi1.packetbrokerageid is not null then
(case when c1.islaw=1 then
(select pist.BrokeragePecentageForPrivates from PacketsInsuranceSubTypes pist
where pist.insurancesubtypeid=pi1.insurancesubtypeid
and pist.id=pi1.packetbrokerageid)
else
(select pist.BrokeragePecentageForLaws from PacketsInsuranceSubTypes pist
where pist.insurancesubtypeid=pi1.insurancesubtypeid
and pist.id=pi1.packetbrokerageid)
end)
else 0
end)
end as brokeragepercentage
from policies p1 inner join policyitems pi1 on pi1.policyid=p1.id
inner join clients c1 on p1.clientid=c1.id
where p1.discard=0
and pi1.insurancesubtypeid=" + ist.ID +
@" and p1.applicationdate >='" + y1 + - +m1 + - +d1 + "' and p1.applicationdate<='" + y2 + - +m2 + - +d2 + "'";
queryCalcBrokerageInOtherClases = @"select pi.id, pei.Value,
case when pi.brokerageid is not null then
(case when c1.islaw=1 then
(select b.percentageforlaws from brokerages b
where b.insurancesubtypeid=pi.insurancesubtypeid and
b.id=pi.brokerageid)
else
(select b.percentageforprivates from brokerages b
where b.insurancesubtypeid=pi.insurancesubtypeid
and b.id=pi.brokerageid)
end)
else
(case when pi.packetbrokerageid is not null then
(case when c1.islaw=1 then
(select pist.BrokeragePecentageForPrivates from PacketsInsuranceSubTypes pist
where pist.insurancesubtypeid=pi.insurancesubtypeid
and pist.id=pi.packetbrokerageid)
else
(select pist.BrokeragePecentageForLaws from PacketsInsuranceSubTypes pist
where pist.insurancesubtypeid=pi.insurancesubtypeid
and pist.id=pi.packetbrokerageid)
end)
else 0
end)
end as brokeragepercentage
from policies p ,policyitems pi, clients c1,
policyextendinformations pei,controls c, controlappropriateinsurancesubtype caist
where p.discard=0
and p.clientid=c1.id
and caist.insurancesubtypeid=" + ist.ID +
@" and pi.policyid=p.id
and caist.controlid=pei.controlid
and caist.isactive=1
and pei.policyitemid=pi.id
and pei.controlid=c.id
and pei.Value<>'0' " +
@" and p.applicationdate >='" + y1 + - +m1 + - +d1 + "' and p.applicationdate<='" + y2 + - +m2 + - +d2 + "'";
queryRealPaidValues = @"select sum(fcpvpist.paidvalue) as sumpaidvalue
from FacCollPaidValuesPerInsSubTypes fcpvpist,facturecollectedpaidvalues fcpv, factureitems fi, factures f
where fcpvpist.facturecollectedpaidvalueid=fcpv.id
and fcpv.factureitemid=fi.id
and fi.factureid=f.id
and f.discard=0
and fcpv.paiddate>='" + y1 + - +m1 + - +d1 + "' and fcpv.paiddate<='" + y2 + - +m2 + - +d2 + "'" +
@" and fcpvpist.insurancesubtypeid=" + ist.ID;
queryGetFacturedBrokeragesPayments = @"select sum(ppist.brokeragevalue) as tmpValueDecimal from PaymentsPerInsSubTypes ppist, payments pa, rates r,
policyitems pi, policyitemfactureitems pifi, factureitems fi, factures f, policies p, facturepayments fp
where ppist.paymentid=pa.id
and pa.rateid=r.id
and r.policyitemid=pi.id
and pi.policyid=p.id
and pifi.policyitemid=pi.id
and pifi.factureitemid=fi.id
and fi.factureid=f.id
and fp.factureid=f.id
and fp.paymentid=pa.id
and f.discard=0
and p.discard=0
and p.applicationdate>='" + y1 + - +m1 + - +d1 + "'" +
@" and p.applicationdate<='" + y2 + - +m2 + - +d2 + "'" +
@" and ppist.insurancesubtypeid=" + ist.ID;
}
List <SummaryClass> summaryListFullPremValue = dcdc.ExecuteQuery <SummaryClass>(queryCountAndPremiumFullPremiumValue).ToList();
List <SummaryClass> summaryListDef = dcdc.ExecuteQuery <SummaryClass>(queryCountAndPremiumDef).ToList();
List <SummaryClass> summaryListInOtherClasses = dcdc.ExecuteQuery <SummaryClass>(queryCountAndPremiumInOtherClasses).ToList();
List <SummaryCalcBrokerageClass> summaryListBrok = dcdc.ExecuteQuery <SummaryCalcBrokerageClass>(getCalculatedBrokerages).ToList();
List <SummaryCalcBrokerageInOtherClass> summaryListCalcBrokerageInOtherClass = dcdc.ExecuteQuery <SummaryCalcBrokerageInOtherClass>(queryCalcBrokerageInOtherClases).ToList();
//List<SummaryCalcRealBrokerageClass> summaryListCalcRealBrokerageClass = dcdc.ExecuteQuery<SummaryCalcRealBrokerageClass>(queryRealPaidValues).ToList();
decimal facturedBrokValue = 0;
List <SummaryForFacturedBrokerages> summaryForFacturedBrokeragesPayments = dcdc.ExecuteQuery <SummaryForFacturedBrokerages>(queryGetFacturedBrokeragesPayments).ToList();
if (summaryForFacturedBrokeragesPayments.Count > 0)
{
if (summaryForFacturedBrokeragesPayments[0].tmpValueDecimal != null)
{
facturedBrokValue += (decimal)summaryForFacturedBrokeragesPayments[0].tmpValueDecimal;
}
}
totFacturedBrokerageValuePerClass += facturedBrokValue;
foreach (SummaryCalcBrokerageClass scbc in summaryListBrok)
{
if (scbc.basicvalue != null)
{
//tmpBrokBaseValue = RateController.RateController.Scale5((scbc.brokeragepercentage / 100) * (scbc.premiumvalue - Convert.ToInt32(scbc.basicvalue)));
tmpBrokBaseValue = (scbc.brokeragepercentage / 100) * (scbc.premiumvalue - Convert.ToInt32(scbc.basicvalue));
brokValue += tmpBrokBaseValue;
tmpBrokBaseValueTot += tmpBrokBaseValue;
}
else
{
//tmpBrokBaseValue = RateController.RateController.Scale5((scbc.brokeragepercentage / 100) * scbc.premiumvalue);
tmpBrokBaseValue = (scbc.brokeragepercentage / 100) * scbc.premiumvalue;
brokValue += tmpBrokBaseValue;
tmpBrokBaseValueTot += tmpBrokBaseValue;
}
//tmpBrokValue = RateController.RateController.Scale5((scbc.brokeragepercentage / 100) * scbc.premiumvalue);
tmpBrokValue = (scbc.brokeragepercentage / 100) * scbc.premiumvalue;
tmpBrokValueTot += tmpBrokValue;
}
foreach (SummaryCalcBrokerageInOtherClass scbioc in summaryListCalcBrokerageInOtherClass)
{
//tmpBrokValueInOtherClass += RateController.RateController.Scale5((scbioc.brokeragepercentage / 100) * Convert.ToDecimal(scbioc.value));
tmpBrokValueInOtherClass += (scbioc.brokeragepercentage / 100) * Convert.ToDecimal(scbioc.value);
//brokValue += RateController.RateController.Scale5((scbioc.brokeragepercentage / 100) * Convert.ToDecimal(scbioc.value));
brokValue += (scbioc.brokeragepercentage / 100) * Convert.ToDecimal(scbioc.value);
}
numberOfPolicies += summaryListFullPremValue[0].Count + summaryListInOtherClasses[0].Count;
totalNumberOfPolicies += summaryListFullPremValue[0].Count;
int inCiclusPremValue = 0;
if (summaryListFullPremValue[0].SumPremiumValue != null)
{
inCiclusPremValue += (int)summaryListFullPremValue[0].SumPremiumValue;
}
if (summaryListDef[0].SumPremiumValue != null)
{
inCiclusPremValue -= (int)summaryListDef[0].SumPremiumValue;
}
if (summaryListInOtherClasses[0].SumPremiumValue != null)
{
inCiclusPremValue += (int)summaryListInOtherClasses[0].SumPremiumValue;
}
premiumValue += inCiclusPremValue;
//if (summaryListCalcRealBrokerageClass[0].sumpaidvalue != null) {
// realBrokValue += (decimal)summaryListCalcRealBrokerageClass[0].sumpaidvalue;
//}
}
vals[2] = numberOfPolicies;
vals[3] = String.Format("{0:#,0.00}", RateController.RateController.Scale5(premiumValue));
totalPremiumValue += premiumValue;
vals[5] = String.Format("{0:#,0.00}", RateController.RateController.Scale5(brokValue));
totalBrokValue += brokValue;
//vals[4] = String.Format("{0:#,0.00}", RateController.RateController.Scale5(realBrokValue));
//vals[4] = String.Format("{0:#,0.00}", RateController.RateController.Scale5(totFacturedValueInClass));
vals[4] = String.Format("{0:#,0.00}", RateController.RateController.Scale5(totFacturedBrokerageValuePerClass));
//totalRealBrokValue += realBrokValue;
//totalRealBrokValue += totFacturedValueInClass;
totalRealBrokValue += totFacturedBrokerageValuePerClass;
pdf.AddDataRowWithBorder(vals, headers.Count(), codes);
}
object[] valsTotal = new object[headers.Count()];
valsTotal[0] = "ВКУПНО";
valsTotal[1] = "";
valsTotal[2] = totalNumberOfPolicies;
valsTotal[3] = String.Format("{0:#,0.00}", RateController.RateController.Scale5(totalPremiumValue));
valsTotal[5] = String.Format("{0:#,0.00}", RateController.RateController.Scale5(totalBrokValue));
valsTotal[4] = String.Format("{0:#,0.00}", RateController.RateController.Scale5(totalRealBrokValue));
pdf.AddDataRowWithBorder(valsTotal, headers.Count(), codes);
pdf.AddTable();
pdf.FinishPDF();
}
public static void AllPolicies(DateTime fromDate, DateTime toDate, int insuranceTypeID, int insuranceSubTypeID, int userID, int branchID, int companyID)
{
DataClassesDataContext dcdc = new DataClassesDataContext();
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;
string query = @"select clients.name ,clients.embg, policyItems.policynumber,policies.startdate, policies.enddate, users.username, policyitems.premiumvalue,
policyitems.realpremiumvalue,
case when policyItems.brokerageid is not null then
(case when clients.islaw=1 then
(select b.percentageforlaws from brokerages b
where b.insurancesubtypeid=policyItems.insurancesubtypeid and
b.id=policyItems.brokerageid)
else
(select b.percentageforprivates from brokerages b
where b.insurancesubtypeid=policyItems.insurancesubtypeid
and b.id=policyItems.brokerageid)
end)
else
(case when policyItems.packetbrokerageid is not null then
(case when clients.islaw=1 then
(select pist.BrokeragePecentageForPrivates from PacketsInsuranceSubTypes pist
where pist.insurancesubtypeid=policyItems.insurancesubtypeid
and pist.id=policyItems.packetbrokerageid)
else
(select pist.BrokeragePecentageForLaws from PacketsInsuranceSubTypes pist
where pist.insurancesubtypeid=policyItems.insurancesubtypeid
and pist.id=policyItems.packetbrokerageid)
end)
else 0
end)
end as brokeragepercentage,
insurancesubtypes.shortdescription as insurancetypename
from clients INNER JOIN policies ON policies.clientid = clients.id
INNER JOIN policyItems ON policyItems.policyid = policies.id
INNER JOIN users ON users.id = policies.userID
INNER JOIN insuranceSubTypes ON policyItems.InsuranceSubTypeID = insuranceSubTypes.ID
INNER JOIN insuranceTypes ON insuranceSubTypes.InsuranceTypeID = insuranceTypes.id
where policies.applicationdate >='" + y1 + - +m1 + - +d1 + "' and policies.applicationdate<='" + y2 + - +m2 + - +d2 + "' and policies.Discard = 0";
if (insuranceTypeID > 0)
{
query += @" and insuranceTypes.ID = " + insuranceTypeID;
}
if (insuranceSubTypeID > 0)
{
query += @" and insuranceSubTypes.ID = " + insuranceSubTypeID;
}
if (userID > 0)
{
query += @" and users.ID = " + userID;
}
if (branchID > 0)
{
query += @" and policies.BranchID = " + branchID;
}
if (companyID > 0)
{
query += @" and policies.InsuranceCompanyID=" + companyID;
}
IEnumerable <Summary> summaryList = dcdc.ExecuteQuery <Summary>(query);
PDFCreators pdf = new PDFCreators(false, 15, 15, 15, 15);
pdf.OpenPDF();
pdf.SetTitle("Продадени полиси за даден период");
string[] headersP = { "РБ", "Број на полиса", "Осигуреник", "Почеток", "Истек", "Премија за наплата", "Брокеража(%)", "Брокеража", "Агент", "Тип" };
float[] policyColumnsWidths = { 6, 9, 22, 9, 9, 9, 9, 9, 9, 9 };
pdf.CreateTable(headersP.Length, false, headersP, "", policyColumnsWidths);
decimal PremValue = 0;
decimal BrokerageValue = 0;
object[] vals;
int i = 0;
foreach (Summary s in summaryList)
{
i++;
vals = new object[headersP.Length];
vals[0] = i.ToString();
vals[1] = s.policynumber;
vals[2] = s.name;
vals[3] = s.startdate.ToShortDateString();
vals[4] = s.enddate.ToShortDateString();
vals[5] = String.Format("{0:#,0.00}", s.premiumvalue);
vals[6] = s.brokeragepercentage;
decimal brokValue = (s.realpremiumvalue * s.brokeragepercentage / 100);
vals[7] = String.Format("{0:#,0.00}", brokValue);
vals[8] = s.username;
vals[9] = s.insurancetypename;
PremValue += s.premiumvalue;
BrokerageValue += brokValue;
pdf.AddDataRow1(vals, headersP.Length);
}
vals = new object[headersP.Length];
vals[0] = ""; //i.ToString();
vals[1] = "";
vals[2] = "";
vals[3] = "";
vals[4] = "Вкупно";
vals[5] = String.Format("{0:#,0.00}", PremValue);
vals[6] = "";
vals[7] = String.Format("{0:#,0.00}", BrokerageValue);
vals[8] = "";
vals[9] = "";
pdf.AddDataRow1(vals, headersP.Length);
pdf.AddTable();
pdf.FinishPDF();
}
public static void CreateFileForPeriod(DateTime startDate, DateTime endDate, string reportType)
{
DataClassesDataContext dcdc = new DataClassesDataContext();
PDFCreators pdf = new PDFCreators(true, 25, 25, 25, 25);
pdf.OpenPDF();
pdf.SetTitle(" ");
pdf.SetTitle(" ");
pdf.SetTitle("Финансиски извештај за извршена наплата");
pdf.SetTitle(" за период " + startDate.ToShortDateString() + " - " + endDate.ToShortDateString());
string[] headersP = { "РБ", "Број на полиса", "Тип на плаќање", "Корисник", "Вредност", };
float[] policyColumnsWidths = { 5, 35, 22, 14, 18 };
TypeCode[] codes = { TypeCode.String, TypeCode.String, TypeCode.String, TypeCode.String, TypeCode.Decimal };
pdf.CreateTable(5, false, headersP, "", policyColumnsWidths);
List <Payment> lstPayments = Payment.Table.Where(c => c.Date.Date >= startDate.Date && c.Date.Date <= endDate.Date && c.Rate.PolicyItem.Policy.Discard == false).ToList();
decimal totalValue = 0;
int counter = 0;
if (reportType == "PolicyNumber")
{
lstPayments = lstPayments.OrderBy(c => c.Rate.PolicyItem.PolicyNumber).ToList();
}
else
{
lstPayments = lstPayments.OrderBy(c => c.PaymentTypeID).ToList();
}
Dictionary <int, decimal> dic = new Dictionary <int, decimal>();
foreach (Payment pay in lstPayments)
{
counter++;
object[] vals = new object[6];
vals[0] = counter;
vals[1] = pay.Rate.PolicyItem.PolicyNumber;
vals[2] = pay.PaymentType.Name;
vals[3] = pay.User.UserName;
vals[4] = String.Format("{0:#,0.00}", pay.Value);
totalValue += pay.Value;
if (!dic.Keys.Contains(pay.PaymentTypeID))
{
dic.Add(pay.PaymentTypeID, pay.Value);
}
else
{
dic[pay.PaymentTypeID] += pay.Value;
}
pdf.AddDataRow1(vals, 5, codes);
}
object[] valsTotal = new object[6];
valsTotal[0] = "";
valsTotal[1] = "";
valsTotal[2] = "";
valsTotal[3] = "Вкупно";
valsTotal[4] = String.Format("{0:#,0.00}", totalValue);
pdf.AddDataRow1(valsTotal, 5, codes);
pdf.AddTable();
pdf.NewPage();
pdf.SetTitle("Рекапитулација:");
string[] headersTotalP = new string[3];
headersTotalP[0] = "РБ";
headersTotalP[1] = "Тип на плаќање";
headersTotalP[2] = "Сума";
float[] policyColumnsTotalWidths = new float[3];
policyColumnsTotalWidths[0] = 5;
policyColumnsTotalWidths[1] = 50;
policyColumnsTotalWidths[2] = 40;
TypeCode[] codesTotal = new TypeCode[3];
codesTotal[0] = TypeCode.String;
codesTotal[1] = TypeCode.String;
codesTotal[2] = TypeCode.Decimal;
pdf.CreateTable(3, false, headersTotalP, "", policyColumnsTotalWidths);
int recapCounter = 1;
foreach (KeyValuePair <int, decimal> kvp in dic)
{
object[] valsRecap = new object[3];
valsRecap[0] = recapCounter;
valsRecap[1] = Broker.DataAccess.PaymentType.Get(kvp.Key).Name;
valsRecap[2] = String.Format("{0:#,0.00}", kvp.Value);
pdf.AddDataRow1(valsRecap, 3, codesTotal);
recapCounter++;
}
pdf.AddTable();
pdf.FinishPDF();
}