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