public static void PrintOffer(Offer o) { PDFCreators creator = new PDFCreators(true, 15, 15, 15, 15); creator.OpenPDF(); creator.GetContentByte(); creator.SetTitle("Понуда: " + o.OfferNumber); creator.SetTitleLeftWithFontSize10(ConvertToMacedonian.ConvertToMACEDONIAN(o.Client.Name).ToUpper()); creator.SetTitleLeftWithFontSize10(ConvertToMacedonian.ConvertToMACEDONIAN(o.Client.Address).ToUpper()); creator.SetTitleLeftWithFontSize10(ConvertToMacedonian.ConvertToMACEDONIAN(o.Client.Place.Municipality.Name).ToUpper()); creator.SetTitleLeft("Датум на понуда: " + o.OfferDate.ToShortDateString()); string[] headers = { "Ред. бр.", "Осигурителна компанија", "Класа на осигурување", "Подкласа на осигурување", "Износ" }; float[] widthPercentages = { 10, 20, 25, 35, 10 }; creator.CreateTable_Facture(5, headers, widthPercentages); object[] values; int i = 1; foreach (OfferItem oi in o.OfferItems) { values = new object[5]; values[0] = i.ToString(); values[1] = oi.InsuranceCompany.Name; values[2] = oi.InsuranceSubType.InsuranceType.Name; values[3] = oi.InsuranceSubType.Description; values[4] = oi.Cost; creator.AddDataRowForBillWithRigthAlligmentForValues(values, 5); i++; } values = new object[5]; values[0] = ""; values[1] = ""; values[2] = ""; values[3] = "Вкупно:"; values[4] = o.Cost; creator.AddDataRowForBillWithRigthAlligmentForValues(values, 5); creator.AddTable(); creator.FinishPDF_FileName("Ponuda" + o.OfferNumber); }
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 PrintBillInfoForPolicy(PolicyItem pi) { string brokerName = BrokerHouseInformation.GetBrokerHouseName(); PDFCreators creator = new PDFCreators(true, 25, 25, 15, 15); creator.SetDocumentHeaderFooter(); creator.OpenPDF(); creator.GetContentByte(); creator.AddJDBLogoForFactures(10, 775); creator.SetTitleLeft8(" "); creator.SetTitleLeft8(" "); creator.SetTitleLeft8(" "); creator.SetTitleLeft8(" "); creator.SetTitleLeft8(" Жиро-сметка : " + Broker.DataAccess.Parameter.GetByCode(Broker.DataAccess.Parameter.ZIRO_SMETKA).Value); creator.SetTitleLeft8(" Депонент : " + Broker.DataAccess.Parameter.GetByCode(Broker.DataAccess.Parameter.DEPONENT).Value); creator.SetTitleLeft8(" ЕДБ : " + Broker.DataAccess.Parameter.GetByCode(Broker.DataAccess.Parameter.EDB).Value + " " + "Матичен број : " + Broker.DataAccess.Parameter.GetByCode(Broker.DataAccess.Parameter.MATICEN_BROJ).Value); creator.SetTitleLeft8(" "); creator.SetTitle("ПОТВРДА"); creator.SetTitle("(за продадена полиса)"); creator.SetTitle("Полиса бр. " + pi.PolicyNumber); creator.SetTitleLeft8(" "); creator.SetTitleLeftWithFontSize10(ConvertToMacedonian.ConvertToMACEDONIAN(pi.Policy.Client.Name).ToUpper()); creator.SetTitleLeftWithFontSize10(ConvertToMacedonian.ConvertToMACEDONIAN(pi.Policy.Client.Address).ToUpper()); creator.SetTitleLeftWithFontSize10(ConvertToMacedonian.ConvertToMACEDONIAN(pi.Policy.Client.Place.Municipality.Name).ToUpper()); creator.SetTitleLeft10("Датум на полиса: " + pi.Policy.ApplicationDate.ToShortDateString()); string nacinNaPlakanje = ""; if (pi.IsPaidInBrokerHouse) { nacinNaPlakanje = "ЕДНОКРАТНО"; List <Rate> lstRates = Rate.GetByPolicyItemID(pi.ID); if (lstRates.Count > 1) { nacinNaPlakanje = "НА РАТИ"; } } else { nacinNaPlakanje = "ДИРЕКТНО ВО ОСИГУРИТЕЛНА КОМПАНИЈА"; } creator.SetTitleLeft10("Начин на плаќање на полиса: " + nacinNaPlakanje); string[] headers = { "Ред. бр.", "Број на полиса", "Осиг. компанија", "Тип на осигурување", "Премија" }; float[] widthPercentages = { 8, 17, 30, 30, 15 }; TypeCode[] typeCodes = { TypeCode.Int32, TypeCode.String, TypeCode.String, TypeCode.String, TypeCode.Decimal }; creator.CreateTable_Facture(headers.Length, headers, widthPercentages); object[] values; values = new object[headers.Length]; values[0] = "1"; values[1] = pi.PolicyNumber; values[2] = pi.Policy.InsuranceCompany.ShortName; values[3] = pi.InsuranceSubType.ShortDescription; values[4] = String.Format("{0:#,0.00}", pi.PremiumValue) + " ДЕН."; creator.AddDataRowForFactures(values, headers.Length, typeCodes); values = new object[headers.Length]; values[0] = ""; values[1] = ""; values[2] = ""; values[3] = "Вкупно"; values[4] = String.Format("{0:#,0.00}", pi.PremiumValue) + " ДЕН."; creator.AddDataRowForFactures(values, headers.Length, typeCodes); creator.AddTable(); creator.SetTitleLeftBold14(" "); creator.SetTitleLeftItalic10("Со букви: " + NumberToTextController.Konvertiranje(pi.PremiumValue)); creator.SetTitleLeft8(" "); creator.SetTitleLeft10("Согласно со член 23, точка 6 од Законот за ДДВ дејноста осигурување е ослободена од плаќање данок без право на одбиток на претходниот данок."); creator.SetTitleLeft8(" "); creator.SetTitleLeft8(" "); creator.SetTitleLeft8(" "); creator.SetTitleLeft8(" "); creator.SetTitleLeft8(" " + brokerName + " ДОГОВОРУВАЧ"); creator.SetTitleLeft8(" "); creator.SetTitleLeft8("_________________________ ____________________"); creator.FinishPDF_FileName("Potvrda" + pi.PolicyNumber); }
public static void PrintBrokerageSlip(BrokerageSlip bs) { PDFCreators creator = new PDFCreators(true, 15, 15, 15, 15); creator.OpenPDF(); creator.GetContentByte(); creator.AddJDBLogoForFactures(10, 775); creator.SetTitleLeft8(" "); creator.SetTitleLeft8(" "); creator.SetTitleLeft8(" "); creator.SetTitleLeft8(" "); creator.SetTitle("БРОКЕРСКИ СЛИП ЗА ОСИГУРУВАЊЕ"); creator.SetTitle(bs.Number); string[] headers = { "", "" }; float[] widthPercentages = { 30, 70 }; TypeCode[] typeCodes = { TypeCode.String, TypeCode.String }; creator.CreateTable_Facture(headers.Length, headers, widthPercentages); object[] values; values = new object[headers.Length]; values[0] = "ОСИГУРЕНИК"; values[1] = bs.Insurer; creator.AddDataRowForBrokerageSlips(values, headers.Length, typeCodes); values = new object[headers.Length]; values[0] = ""; values[1] = ""; creator.AddDataRowForBrokerageSlips(values, headers.Length, typeCodes); values = new object[headers.Length]; values[0] = "ОСИГУРУВАЧ"; values[1] = bs.InsuranceCompany.Name; creator.AddDataRowForBrokerageSlips(values, headers.Length, typeCodes); values = new object[headers.Length]; values[0] = ""; values[1] = ""; creator.AddDataRowForBrokerageSlips(values, headers.Length, typeCodes); values = new object[headers.Length]; values[0] = "ПЕРИОД НА ИЗДАДЕНИ ПОЛИСИ"; values[1] = bs.FromDate.ToShortDateString() + " до " + bs.ToDate.ToShortDateString(); creator.AddDataRowForBrokerageSlips(values, headers.Length, typeCodes); values = new object[headers.Length]; values[0] = ""; values[1] = ""; creator.AddDataRowForBrokerageSlips(values, headers.Length, typeCodes); values = new object[headers.Length]; values[0] = "КЛАСИ НА ОСИГУРУВАЊЕ"; int ordinalNumber = 1; string nested = ""; foreach (BrokerageSlipInsuranceSubType bsist in bs.BrokerageSlipInsuranceSubTypes) { nested += (ordinalNumber + ". " + bsist.InsuranceSubType.ShortDescription + "\n"); ordinalNumber++; } values[1] = nested; creator.AddDataRowForBrokerageSlips(values, headers.Length, typeCodes); values = new object[headers.Length]; values[0] = ""; values[1] = ""; creator.AddDataRowForBrokerageSlips(values, headers.Length, typeCodes); values = new object[headers.Length]; values[0] = "ОСИГУРЕНИ ОПАСНОСТИ"; values[1] = bs.InsuranceRisks; creator.AddDataRowForBrokerageSlips(values, headers.Length, typeCodes); values = new object[headers.Length]; values[0] = ""; values[1] = ""; creator.AddDataRowForBrokerageSlips(values, headers.Length, typeCodes); values = new object[headers.Length]; values[0] = "ОСИГУРЕНИ СТВАРИ"; values[1] = bs.InsuranceThings; creator.AddDataRowForBrokerageSlips(values, headers.Length, typeCodes); values = new object[headers.Length]; values[0] = ""; values[1] = ""; creator.AddDataRowForBrokerageSlips(values, headers.Length, typeCodes); int ordNumberPerInsSubType = 1; foreach (BrokerageSlipInsuranceSubType bsist in bs.BrokerageSlipInsuranceSubTypes) { object[] valINST = new object[2]; valINST[0] = "ПОДКЛАСА НА ОСИГУРУВАЊЕ"; valINST[1] = ordNumberPerInsSubType + ". " + bsist.InsuranceSubType.ShortDescription; creator.AddDataRowForBrokerageSlips(valINST, headers.Length, typeCodes); decimal totPrem = 0; List <BrokerageSlipPolicyItem> tmpList = BrokerageSlipPolicyItem.GetByBrokerageSlip(bs.ID); List <BrokerageSlipPolicyItem> tmpListPer = tmpList.Where(c => c.PolicyItem.InsuranceSubTypeID == bsist.InsuranceSubTypeID).ToList(); List <PolicyItem> lstPolItems = tmpListPer.Select(c => c.PolicyItem).ToList(); lstPolItems = lstPolItems.OrderBy(c => c.PolicyNumber).ToList(); foreach (PolicyItem pi in lstPolItems) { object[] valPol = new object[2]; string polPremValue = String.Format("{0:#,0.00}", pi.PremiumValue); valPol[0] = ""; valPol[1] = pi.PolicyNumber + " - " + pi.Policy.Client.Name + " - " + polPremValue; creator.AddDataRowForBrokerageSlips(valPol, headers.Length, typeCodes); if (!pi.Policy.Discard) { totPrem += pi.PremiumValue; } } object[] valTotPerIST = new object[2]; valTotPerIST[0] = ""; valTotPerIST[1] = "ВКУПНО ПРЕМИЈА: " + String.Format("{0:#,0.00}", totPrem) + " ДЕНАРИ"; creator.AddDataRowForBrokerageSlips(valTotPerIST, headers.Length, typeCodes); ordNumberPerInsSubType++; } //values = new object[headers.Length]; //values[0] = "ПРЕМИЈА"; //values[1] = ""; creator.AddDataRowForBrokerageSlips(values, headers.Length, typeCodes); values = new object[headers.Length]; values[0] = ""; values[1] = ""; creator.AddDataRowForBrokerageSlips(values, headers.Length, typeCodes); values = new object[headers.Length]; values[0] = "ОСНОВ ЗА ОБЕШТЕТУВАЊЕ"; values[1] = bs.BasisForCompensation; creator.AddDataRowForBrokerageSlips(values, headers.Length, typeCodes); values = new object[headers.Length]; values[0] = ""; values[1] = ""; creator.AddDataRowForBrokerageSlips(values, headers.Length, typeCodes); values = new object[headers.Length]; values[0] = "БРОКЕРАЖА"; int ordNumber = 1; string nestedB = ""; foreach (BrokerageSlipInsuranceSubType bsist in bs.BrokerageSlipInsuranceSubTypes) { nestedB += (ordNumber + ". " + bsist.InsuranceSubType.ShortDescription + " " + bsist.BrokeragePercentagePrivates + "% ЗА ФИЗИЧКИ ЛИЦА, " + bsist.BrokeragePercentageLaws + "% ЗА ПРАВНИ ЛИЦА ОД БРУТО ПРЕМИЈА" + "\n"); ordNumber++; } values[1] = nestedB; creator.AddDataRowForBrokerageSlips(values, headers.Length, typeCodes); values = new object[headers.Length]; values[0] = ""; values[1] = ""; creator.AddDataRowForBrokerageSlips(values, headers.Length, typeCodes); values = new object[headers.Length]; values[0] = "ДРУГИ УСЛОВИ"; values[1] = bs.OtherConditions; creator.AddDataRowForBrokerageSlips(values, headers.Length, typeCodes); values = new object[headers.Length]; values[0] = ""; values[1] = ""; creator.AddDataRowForBrokerageSlips(values, headers.Length, typeCodes); values = new object[headers.Length]; values[0] = "БРОКЕР"; values[1] = BrokerHouseInformation.GetBrokerHouseNameByCode(BrokerHouseInformation.FIRST_ROW); creator.AddDataRowForBrokerageSlips(values, headers.Length, typeCodes); values = new object[headers.Length]; values[0] = ""; values[1] = ""; creator.AddDataRowForBrokerageSlips(values, headers.Length, typeCodes); values = new object[headers.Length]; values[0] = ""; values[1] = Broker.DataAccess.Parameter.GetByCode(Broker.DataAccess.Parameter.MESTO).Value; creator.AddDataRowForBrokerageSlips(values, headers.Length, typeCodes); values = new object[headers.Length]; values[0] = ""; values[1] = bs.Date.ToShortDateString(); creator.AddDataRowForBrokerageSlips(values, headers.Length, typeCodes); values = new object[headers.Length]; values[0] = ""; values[1] = ""; creator.AddDataRowForBrokerageSlips(values, headers.Length, typeCodes); creator.AddTable(); creator.SetTitle(" "); creator.SetTitle(" "); string brokerName = BrokerHouseInformation.GetBrokerHouseName(); creator.SetTitleLeft8(" " + brokerName + " " + bs.InsuranceCompany.Name); creator.SetTitleLeft8(" "); creator.SetTitleLeft8("_________________________ ____________________"); creator.FinishPDF_FileName("BrokerskiSlip" + bs.ID); }
public static void PrintAnex(Policy p) { List <Rate> ratesForPrint = Rate.GetByPolicyItemID(p.PolicyItems[0].ID); Broker.DataAccess.Facture f = PolicyItemFactureItem.GetByPolicyItemID(p.PolicyItems[0].ID); PDFCreators creator = new PDFCreators(true, 25, 25, 15, 15); creator.SetDocumentHeaderFooter(); creator.OpenPDF(); creator.GetContentByte(); creator.AddJDBLogoForFactures(10, 775); creator.SetTitleLeft8(" "); creator.SetTitleLeft8(" "); creator.SetTitleLeft8(" "); creator.SetTitleLeft8(" "); creator.SetTitleLeft8(" Жиро-сметка : " + Broker.DataAccess.Parameter.GetByCode(Broker.DataAccess.Parameter.ZIRO_SMETKA).Value); creator.SetTitleLeft8(" Депонент : " + Broker.DataAccess.Parameter.GetByCode(Broker.DataAccess.Parameter.DEPONENT).Value); creator.SetTitleLeft8(" ЕДБ : " + Broker.DataAccess.Parameter.GetByCode(Broker.DataAccess.Parameter.EDB).Value + " " + "Матичен број : " + Broker.DataAccess.Parameter.GetByCode(Broker.DataAccess.Parameter.MATICEN_BROJ).Value); creator.SetTitleLeft8(" "); creator.SetTitleLeft8("Договорувач " + p.Client.Name + " " + "Експозитура " + p.Branch.Name); creator.SetTitleLeft8(" "); creator.SetTitle("ДОГОВОР " + p.PolicyItems[0].PolicyNumber); creator.SetTitle("за плаќање на должна премија за осигурување"); creator.SetTitle("(плаќање на рати)"); creator.SetTitleLeft8(" "); creator.SetTitleLeft8(" "); creator.SetTitleLeft8("1.Премијата за осигурување ќе се плати на рати, како што следи:"); string[] headersRates = { "Реден број", "Рок за плаќање", "Полиса", "Фактура број", "Износ" }; float[] widthPercentagesRates = { 10, 25, 25, 20, 20 }; TypeCode[] typeCodesRates = { TypeCode.Int32, TypeCode.String, TypeCode.String, TypeCode.String, TypeCode.Decimal }; creator.CreateTable_Facture(headersRates.Length, headersRates, widthPercentagesRates); ratesForPrint = ratesForPrint.OrderBy(c => c.Date).ToList(); int counter = 1; foreach (Rate r in ratesForPrint) { object[] valuesRates = new object[headersRates.Length]; valuesRates[0] = counter; valuesRates[1] = r.Date.ToShortDateString(); valuesRates[2] = r.PolicyItem.PolicyNumber; if (f != null) { valuesRates[3] = f.FactureNumber; } else { valuesRates[3] = ""; } valuesRates[4] = String.Format("{0:#,0.00}", r.Value) + " ДЕН."; creator.AddDataRowForFactures(valuesRates, headersRates.Length, typeCodesRates); counter++; } object[] values = new object[headersRates.Length]; values[0] = ""; values[1] = ""; values[2] = ""; values[3] = "Вкупно"; values[4] = String.Format("{0:#,0.00}", p.PolicyItems[0].PremiumValue) + " ДЕН."; creator.AddDataRowForFactures(values, headersRates.Length, typeCodesRates); creator.AddTable(); creator.SetTitleLeft8("2.Доколку настане осигуран случај - штета, независно од тоа што е договорено плаќање на рати, премијата за осигурување стасува за наплата веднаш до висината на надоместокот што треба да се исплати и ќе се наплати по пат на компензација."); creator.SetTitleLeft8("3.Ако премијата не се плати во договорените рокови од овој Договор, ќе се пресметува камата."); creator.SetTitleLeft8("4.Договорот е составен од три еднообразни примероци, од кои еден за осигуреникот, а два за осигурувачот."); creator.SetTitle(" "); creator.SetTitle(" "); if (f != null) { creator.SetTitleCenter8("Во " + f.User.Branch.Municipality.Name + " на " + f.DateOfCreation.ToShortDateString()); } else { creator.SetTitleCenter8("Во " + p.Branch.Municipality.Name + " на " + p.ApplicationDate.ToShortDateString()); } creator.SetTitle(" "); creator.SetTitle(" "); string brokerName = BrokerHouseInformation.GetBrokerHouseName(); creator.SetTitleLeft8(" " + brokerName + " ДОГОВОРУВАЧ"); creator.SetTitleLeft8(" "); creator.SetTitleLeft8("_________________________ ____________________"); creator.FinishPDF_FileName("Aneks" + p.PolicyItems[0].PolicyNumber); }
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 PrintAccountFacture(AccountFacture f) { if (f.DocumentSubType.Code == DocumentSubType.FAKTURA_PROVIZIJA) { PDFCreators creator = new PDFCreators(true, 15, 15, 15, 15); creator.OpenPDF(); creator.GetContentByte(); creator.AddJDBLogoForFactures(10, 760); creator.SetTitleLeft8(" "); creator.SetTitleLeftBold14(" " + Broker.DataAccess.Parameter.GetByCode(Broker.DataAccess.Parameter.IME).Value); creator.SetTitle(" "); creator.SetTitleLeft8(" "); creator.SetTitleLeft8(" Адреса : " + Broker.DataAccess.Parameter.GetByCode(Broker.DataAccess.Parameter.ADRESA).Value); creator.SetTitleLeft8(" Место : " + Broker.DataAccess.Parameter.GetByCode(Broker.DataAccess.Parameter.MESTO).Value); creator.SetTitleLeft8(" Телефон : " + Broker.DataAccess.Parameter.GetByCode(Broker.DataAccess.Parameter.TELEFON).Value + " " + "Факс : " + Broker.DataAccess.Parameter.GetByCode(Broker.DataAccess.Parameter.FAKS).Value); if (f.DocumentSubType.Code == DocumentSubType.FAKTURA_PROVIZIJA) { creator.SetTitleLeft8(" Жиро-сметка : " + Broker.DataAccess.Parameter.GetByCode(Broker.DataAccess.Parameter.ZIRO_SMETKA_ZA_OSIG_KOMPANII).Value); } else if (f.DocumentSubType.Code == DocumentSubType.FAKTURA_KLIENT) { creator.SetTitleLeft8(" Жиро-сметка : " + Broker.DataAccess.Parameter.GetByCode(Broker.DataAccess.Parameter.ZIRO_SMETKA).Value); } creator.SetTitleLeft8(" Депонент : " + Broker.DataAccess.Parameter.GetByCode(Broker.DataAccess.Parameter.DEPONENT).Value); creator.SetTitleLeft8(" ЕДБ : " + Broker.DataAccess.Parameter.GetByCode(Broker.DataAccess.Parameter.EDB).Value + " " + "Матичен број : " + Broker.DataAccess.Parameter.GetByCode(Broker.DataAccess.Parameter.MATICEN_BROJ).Value); creator.SetTitleLeft8(" Web-страна : " + Broker.DataAccess.Parameter.GetByCode(Broker.DataAccess.Parameter.WEB_STRANA).Value + " " + "E-Mail : " + Broker.DataAccess.Parameter.GetByCode(Broker.DataAccess.Parameter.EMAIL).Value); creator.SetTitleLeft8(" "); if (f.Discard) { creator.SetTitleCenterForFactureNumber("СТОРНИРАНА Ф-ра бр. " + f.FactureNumber); } else { creator.SetTitleCenterForFactureNumber("Фактура бр. " + f.FactureNumber); } creator.SetTitleLeftWithFontSize10(ConvertToMacedonian.ConvertToMACEDONIAN(f.Client.Name).ToUpper()); creator.SetTitleLeftWithFontSize10(ConvertToMacedonian.ConvertToMACEDONIAN(f.Client.Address).ToUpper()); creator.SetTitleLeftWithFontSize10(ConvertToMacedonian.ConvertToMACEDONIAN(f.Client.Place.Municipality.Name).ToUpper()); creator.SetTitleLeft10("Датум на фактура: " + f.DateOfCreation.ToShortDateString()); creator.SetTitleLeft10("Рок на плаќање: " + f.DateOfPayment.ToShortDateString()); //creator.SetTitleLeft8("Период: " + f.FromDate.ToShortDateString() + " - " + f.ToDate.ToShortDateString()); string[] headers = { "Ред. бр.", "Опис", "Количина", "Премија", "Брокеража" }; float[] widthPercentages = { 8, 45, 15, 17, 15 }; TypeCode[] typeCodes = { TypeCode.Int32, TypeCode.String, TypeCode.Int32, TypeCode.Decimal, TypeCode.Decimal }; creator.CreateTable_Facture(headers.Length, headers, widthPercentages); object[] values; foreach (AccountFactureItem fi in f.AccountFactureItems) { values = new object[headers.Length]; values[0] = fi.Number.ToString(); values[1] = fi.Description; values[2] = fi.Count.ToString(); values[3] = String.Format("{0:#,0.00}", fi.PremiumValue); values[4] = String.Format("{0:#,0.00}", fi.BrokerageValue); creator.AddDataRowForFactures(values, headers.Length, typeCodes); } values = new object[headers.Length]; values[0] = ""; values[1] = ""; values[2] = ""; values[3] = "Вкупно"; values[4] = String.Format("{0:#,0.00}", f.BrokerageValue); creator.AddDataRowForFactures(values, headers.Length, typeCodes); creator.AddTable(); creator.SetTitleLeftBold14(" "); creator.SetTitleLeftItalic10("Со букви: " + NumberToTextController.Konvertiranje(f.BrokerageValue)); creator.SetTitleLeft8(" "); creator.SetTitleLeft10("Ве молиме фактурираниот износ да го платите до назначениот рок на плаќање."); creator.SetTitleLeft8(" "); creator.SetTitleLeft10("Доколку износот за фактурата не биде платен до наведениот датум, се пресметува затезна камата од денот на стасаност до денот на плаќањето."); creator.SetTitleLeft8(" "); creator.SetTitleLeft10("Согласно со член 23, точка 6 од Законот за ДДВ дејноста осигурување е ослободена од плаќање данок без право на одбиток на претходниот данок."); creator.SetTitleLeft8(" "); creator.SetTitleLeft8(" "); creator.SetTitleLeft8(" "); creator.SetTitleLeft8(" "); creator.SetTitleLeft10(" Фактурирал Примил Одобрил"); creator.SetTitleLeft10(" _________________ __________________ ____________________"); creator.AddPage(); creator.SetTitleCenterForFactureNumber("Спецификација за фактура бр. " + f.FactureNumber); string[] headersSpecifications = { "Ред. бр.", "Број на полиса", "Почеток", "Истек", "Дата на издавање", "Премија" }; float[] widthPercentagesSpecifications = { 10, 20, 19, 19, 19, 13 }; TypeCode[] typeCodesSpecifications = { TypeCode.Int32, TypeCode.String, TypeCode.String, TypeCode.String, TypeCode.String, TypeCode.Decimal }; object[] valuesSpecifications; foreach (AccountFactureItem fi in f.AccountFactureItems) { creator.SetTitleLeft10Bold(fi.InsuranceSubType.Description); creator.CreateTable_Facture(headersSpecifications.Length, headersSpecifications, widthPercentagesSpecifications); List <AccountFactureItemPolicyItem> pifiList = AccountFactureItemPolicyItem.GetByAccountFactureItem(fi.ID); int ordinalNumber = 1; foreach (AccountFactureItemPolicyItem pifi in pifiList) { valuesSpecifications = new object[6]; valuesSpecifications[0] = ordinalNumber; valuesSpecifications[1] = pifi.PolicyItem.PolicyNumber; valuesSpecifications[2] = pifi.PolicyItem.Policy.StartDate.ToShortDateString(); valuesSpecifications[3] = pifi.PolicyItem.Policy.EndDate.ToShortDateString(); valuesSpecifications[4] = pifi.PolicyItem.Policy.ApplicationDate.ToShortDateString(); valuesSpecifications[5] = pifi.PolicyItem.PremiumValue; creator.AddDataRowForFacturesExtend(valuesSpecifications, headersSpecifications.Length, typeCodesSpecifications, headersSpecifications, widthPercentagesSpecifications); ordinalNumber++; } creator.AddTable(); creator.SetTitleCenterForFactureNumber(" "); } creator.FinishPDF_FileName("Factura" + f.FactureNumber); } //if (f.DocumentSubType.Code == DocumentSubType.FAKTURA_KLIENT) //{ // PDFCreators creator = new PDFCreators(true, 15, 15, 15, 15); // creator.OpenPDF(); // creator.GetContentByte(); // creator.AddJDBLogoForFactures(10, 760); // creator.SetTitleLeft8(" "); // creator.SetTitleLeftBold14(" " + Parameter.GetByCode(Parameter.IME).Value); // creator.SetTitle(" "); // creator.SetTitleLeft8(" "); // creator.SetTitleLeft8(" Адреса : " + Parameter.GetByCode(Parameter.ADRESA).Value); // creator.SetTitleLeft8(" Место : " + Parameter.GetByCode(Parameter.MESTO).Value); // creator.SetTitleLeft8(" Телефон : " + Parameter.GetByCode(Parameter.TELEFON).Value + " " + "Факс : " + Parameter.GetByCode(Parameter.FAKS).Value); // creator.SetTitleLeft8(" Жиро-сметка : " + Parameter.GetByCode(Parameter.ZIRO_SMETKA).Value); // creator.SetTitleLeft8(" Депонент : " + Parameter.GetByCode(Parameter.DEPONENT).Value); // creator.SetTitleLeft8(" ЕДБ : " + Parameter.GetByCode(Parameter.EDB).Value + " " + "Матичен број : " + Parameter.GetByCode(Parameter.MATICEN_BROJ).Value); // creator.SetTitleLeft8(" Web-страна : " + Parameter.GetByCode(Parameter.WEB_STRANA).Value + " " + "E-Mail : " + Parameter.GetByCode(Parameter.EMAIL).Value); // creator.SetTitleLeft8(" "); // creator.SetTitleCenterForFactureNumber("Фактура бр. " + f.FactureNumber); // creator.SetTitleLeftWithFontSize10(ConvertToMacedonian.ConvertToMACEDONIAN(f.Client.Name).ToUpper()); // creator.SetTitleLeftWithFontSize10(ConvertToMacedonian.ConvertToMACEDONIAN(f.Client.Address).ToUpper()); // creator.SetTitleLeftWithFontSize10(ConvertToMacedonian.ConvertToMACEDONIAN(f.Client.Place.Municipality.Name).ToUpper()); // creator.SetTitleLeft10("Датум на фактура: " + f.DateOfCreation.ToShortDateString()); // creator.SetTitleLeft10("Рок на плаќање: " + f.DateOfPayment.ToShortDateString()); // //creator.SetTitleLeft8("Период: " + f.FromDate.ToShortDateString() + " - " + f.ToDate.ToShortDateString()); // string[] headers = { "Ред. бр.", "Опис", "Количина", "Премија" }; // float[] widthPercentages = { 10, 50, 15, 25 }; // TypeCode[] typeCodes = { TypeCode.Int32, TypeCode.String, TypeCode.Int32, TypeCode.Decimal }; // creator.CreateTable_Facture(headers.Length, headers, widthPercentages); // object[] values; // foreach (FactureItem fi in f.FactureItems) // { // values = new object[headers.Length]; // values[0] = fi.Number.ToString(); // values[1] = fi.Description; // values[2] = fi.Count.ToString(); // values[3] = String.Format("{0:#,0.00}", fi.PremiumValue); // creator.AddDataRowForFactures(values, headers.Length, typeCodes); // } // values = new object[headers.Length]; // values[0] = ""; // values[1] = ""; // values[2] = "Вкупно"; // values[3] = String.Format("{0:#,0.00}", f.TotalCost); // creator.AddDataRowForFactures(values, headers.Length, typeCodes); // creator.AddTable(); // creator.SetTitleLeftBold14(" "); // creator.SetTitleLeftItalic10("Со букви: " + NumberToTextController.Konvertiranje(f.TotalCost)); // creator.SetTitleLeft8(" "); // creator.SetTitleLeft10("Ве молиме фактурираниот износ да го платите до назначениот рок на плаќање."); // creator.SetTitleLeft8(" "); // creator.SetTitleLeft10("Доколку износот за фактурата не биде платен до наведениот датум, се пресметува затезна камата од денот на стасаност до денот на плаќањето."); // creator.SetTitleLeft8(" "); // creator.SetTitleLeft10("Согласно со член 23, точка 6 од Законот за ДДВ дејноста осигурување е ослободена од плаќање данок без право на одбиток на претходниот данок."); // creator.SetTitleLeft8(" "); // creator.SetTitleLeft8(" "); // creator.SetTitleLeft8(" "); // creator.SetTitleLeft8(" "); // creator.SetTitleLeft10(" Фактурирал Примил Одобрил"); // creator.SetTitleLeft10(" _________________ __________________ ____________________"); // string facItemDescriptionStart = "Полиса - "; // creator.AddPage(); // foreach (FactureItem fi in f.FactureItems) { // string polNumber = fi.Description.Substring(facItemDescriptionStart.Length); // creator.SetTitleLeft10(facItemDescriptionStart + polNumber); // string[] headersRates = { "Рата бр.", "Дата за плаќање", "Износ" }; // float[] widthPercentagesRates = { 15, 50, 35 }; // TypeCode[] typeCodesRates = { TypeCode.Int32, TypeCode.String, TypeCode.Decimal }; // creator.CreateTable_Facture(headersRates.Length, headersRates, widthPercentagesRates); // PolicyItem pi = PolicyItem.GetByNumberAndInsuranceSubType(polNumber, fi.InsuranceSubTypeID, fi.Facture.InsuranceCompanyID); // List<Rate> rates = Rate.GetByPolicyItemID(pi.ID); // rates = rates.OrderBy(c => c.Number).ToList(); // foreach (Rate r in rates) { // object[] valuesRates = new object[headersRates.Length]; // valuesRates[0] = r.Number; // valuesRates[1] = r.Date.ToShortDateString(); // valuesRates[2] = String.Format("{0:#,0.00}", r.Value); // creator.AddDataRowForFactures(valuesRates, headersRates.Length, typeCodes); // } // creator.AddTable(); // } //creator.FinishPDF_FileName("Factura" + f.FactureNumber); }
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 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 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(); }
public static void PrintFinCardByApplicationDateReport(DateTime fromDate, DateTime toDate, List <int> lstBranhces, List <int> lstInsuranceCompanies, List <int> lstInsuranceSubTypes, List <int> lstUsers, List <int> lstMarketingAgents) { string brokerName = BrokerHouseInformation.GetBrokerHouseName(); PDFCreators creator = new PDFCreators(true, 25, 25, 15, 15); creator.SetDocumentHeaderFooter(); creator.OpenPDF(); creator.GetContentByte(); creator.AddJDBLogoForFactures(10, 775); creator.SetTitleLeft8(" "); creator.SetTitleLeft8(" "); creator.SetTitleLeft8(" "); creator.SetTitle("ФИНАНСОВА КАРТИЦА"); creator.SetTitle("по датум на издавање на полиси"); creator.SetTitle("за период " + fromDate.ToShortDateString() + " - " + toDate.ToShortDateString()); creator.SetTitleLeft10("Датум на печатење: " + DateTime.Today.ToShortDateString()); decimal overallSaldo = 0; decimal overallDebt = 0; decimal overallDemand = 0; 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; //List<Policy> lstPol = Policy.Table.Where(c => c.Discard == false && c.ApplicationDate.Date >= fromDate.Date && c.ApplicationDate.Date <= toDate.Date).ToList(); DataClassesDataContext dc = new DataClassesDataContext(); string query = @"SELECT p.* from policies p, policyitems pi where pi.policyid=p.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 p.applicationdate>='" + y1 + - +m1 + - +d1 + "'" + @" and p.applicationdate<='" + y2 + - +m2 + - +d2 + "'" + @" and p.discard = 0 "; List <Policy> lstPol = dc.ExecuteQuery <Policy>(query).ToList(); foreach (Policy p in lstPol) { creator.SetTitleLeft10("Број на полиса: " + p.PolicyItems[0].PolicyNumber); creator.SetTitleLeft10("Осигурителна компанија: " + p.InsuranceCompany.ShortName); creator.SetTitleLeft10("Подкласа на осигурување: " + p.PolicyItems[0].InsuranceSubType.ShortDescription); creator.SetTitleLeft10("Датум на полиса: " + p.ApplicationDate.ToShortDateString()); //decimal totalSaldo = 0; decimal totalDebtValue = 0; decimal totalDemandValue = 0; List <FinanceCardInfo> lst = new List <FinanceCardInfo>(); lst = FinanceCardController.GetByPolicy(p, 0); string[] headers = { "Дата", "Доспева", "Опис", "Должи", "Побарува", "Салдо" }; float[] widthPercentages = { 10, 10, 35, 15, 15, 15 }; TypeCode[] typeCodes = { TypeCode.String, TypeCode.String, TypeCode.String, TypeCode.Decimal, TypeCode.Decimal, TypeCode.Decimal }; creator.CreateTable_Facture(headers.Length, headers, widthPercentages); foreach (FinanceCardInfo fci in lst) { object[] values; values = new object[headers.Length]; values[0] = fci.DocumentDate.ToShortDateString(); values[1] = fci.PaidDate.ToShortDateString(); values[2] = fci.Description; values[3] = String.Format("{0:#,0.00}", fci.DebtValue); values[4] = String.Format("{0:#,0.00}", fci.DemandValue); values[5] = String.Format("{0:#,0.00}", fci.SaldoValue); totalDebtValue += fci.DebtValue; totalDemandValue += fci.DemandValue; //totalSaldo += fci.SaldoValue; creator.AddDataRowForFactures(values, headers.Length, typeCodes); } object[] valuesTot; valuesTot = new object[headers.Length]; valuesTot[0] = ""; valuesTot[1] = ""; valuesTot[2] = ""; valuesTot[3] = String.Format("{0:#,0.00}", totalDebtValue); valuesTot[4] = String.Format("{0:#,0.00}", totalDemandValue); valuesTot[5] = String.Format("{0:#,0.00}", totalDebtValue - totalDemandValue); overallSaldo += (totalDebtValue - totalDemandValue); overallDemand += totalDemandValue; overallDebt += totalDebtValue; creator.AddDataRowForFactures(valuesTot, headers.Length, typeCodes); creator.AddTable(); } creator.SetTitleRight("Должи: " + String.Format("{0:#,0.00}", overallDebt) + " Побарува: " + String.Format("{0:#,0.00}", overallDemand) + " Салдо: " + String.Format("{0:#,0.00}", overallSaldo)); creator.FinishPDF_FileName("FinCard"); }
public static void PrintFinCardByPaidDates(DateTime fromDate, DateTime toDate, List <int> lstBranhces, List <int> lstInsuranceCompanies, List <int> lstInsuranceSubTypes, List <int> lstUsers, List <int> lstMarketingAgents) { string brokerName = BrokerHouseInformation.GetBrokerHouseName(); PDFCreators creator = new PDFCreators(true, 25, 25, 15, 15); creator.SetDocumentHeaderFooter(); creator.OpenPDF(); creator.GetContentByte(); creator.AddJDBLogoForFactures(10, 775); creator.SetTitleLeft8(" "); creator.SetTitleLeft8(" "); creator.SetTitleLeft8(" "); creator.SetTitle("ФИНАНСОВА КАРТИЦА"); creator.SetTitle("по датум на доспевање и уплата"); creator.SetTitle("за период " + fromDate.ToShortDateString() + " - " + toDate.ToShortDateString()); creator.SetTitleLeft10("Датум на печатење: " + DateTime.Today.ToShortDateString()); decimal overallSaldo = 0; decimal overallDebt = 0; decimal overallDemand = 0; List <FinanceCardInfo> lstFCIByRates = FinanceCardController.GetByRateInPeriod(fromDate, toDate, lstBranhces, lstInsuranceCompanies, lstInsuranceSubTypes, lstUsers, lstMarketingAgents); List <FinanceCardInfo> lstFCIByPayments = FinanceCardController.GetByPaymentInPeriod(fromDate, toDate, lstBranhces, lstInsuranceCompanies, lstInsuranceSubTypes, lstUsers, lstMarketingAgents); HashSet <int> lstPolicyIDs = new HashSet <int>(); foreach (FinanceCardInfo fciR in lstFCIByRates) { if (!lstPolicyIDs.Contains(fciR.PolicyItemID)) { lstPolicyIDs.Add(fciR.PolicyItemID); } } foreach (FinanceCardInfo fciP in lstFCIByPayments) { if (!lstPolicyIDs.Contains(fciP.PolicyItemID)) { lstPolicyIDs.Add(fciP.PolicyItemID); } } foreach (int piID in lstPolicyIDs) { PolicyItem pi = PolicyItem.Get(piID); creator.SetTitleLeft10("Број на полиса: " + pi.Policy.PolicyItems[0].PolicyNumber); creator.SetTitleLeft10("Осигурителна компанија: " + pi.Policy.InsuranceCompany.ShortName); creator.SetTitleLeft10("Подкласа на осигурување: " + pi.Policy.PolicyItems[0].InsuranceSubType.ShortDescription); creator.SetTitleLeft10("Датум на полиса: " + pi.Policy.ApplicationDate.ToShortDateString()); //decimal totalSaldo = 0; decimal totalDebtValue = 0; decimal totalDemandValue = 0; List <FinanceCardInfo> lst = new List <FinanceCardInfo>(); List <FinanceCardInfo> lstRat = lstFCIByRates.Where(c => c.PolicyItemID == piID).ToList(); List <FinanceCardInfo> lstPay = lstFCIByPayments.Where(c => c.PolicyItemID == piID).ToList(); if (lstRat != null) { if (lstRat.Count > 0) { lst.AddRange(lstRat); } } if (lstPay != null) { if (lstPay.Count > 0) { lst.AddRange(lstPay); } } for (int i = 0; i < lst.Count; i++) { if (i == 0) { lst[i].SaldoValue = lst[i].DebtValue - lst[i].DemandValue; } if (i > 0) { lst[i].SaldoValue = lst[i - 1].SaldoValue + (lst[i].DebtValue - lst[i].DemandValue); } } string[] headers = { "Дата", "Доспева", "Опис", "Должи", "Побарува", "Салдо" }; float[] widthPercentages = { 10, 10, 35, 15, 15, 15 }; TypeCode[] typeCodes = { TypeCode.String, TypeCode.String, TypeCode.String, TypeCode.Decimal, TypeCode.Decimal, TypeCode.Decimal }; creator.CreateTable_Facture(headers.Length, headers, widthPercentages); foreach (FinanceCardInfo fci in lst) { object[] values; values = new object[headers.Length]; values[0] = fci.DocumentDate.ToShortDateString(); values[1] = fci.PaidDate.ToShortDateString(); values[2] = fci.Description; values[3] = String.Format("{0:#,0.00}", fci.DebtValue); values[4] = String.Format("{0:#,0.00}", fci.DemandValue); values[5] = String.Format("{0:#,0.00}", fci.SaldoValue); totalDebtValue += fci.DebtValue; totalDemandValue += fci.DemandValue; //totalSaldo += fci.SaldoValue; creator.AddDataRowForFactures(values, headers.Length, typeCodes); } object[] valuesTot; valuesTot = new object[headers.Length]; valuesTot[0] = ""; valuesTot[1] = ""; valuesTot[2] = ""; valuesTot[3] = String.Format("{0:#,0.00}", totalDebtValue); valuesTot[4] = String.Format("{0:#,0.00}", totalDemandValue); valuesTot[5] = String.Format("{0:#,0.00}", totalDebtValue - totalDemandValue); overallSaldo += (totalDebtValue - totalDemandValue); overallDemand += totalDemandValue; overallDebt += totalDebtValue; creator.AddDataRowForFactures(valuesTot, headers.Length, typeCodes); creator.AddTable(); } creator.SetTitleRight("Должи: " + String.Format("{0:#,0.00}", overallDebt) + " Побарува: " + String.Format("{0:#,0.00}", overallDemand) + " Салдо: " + String.Format("{0:#,0.00}", (overallDebt - overallDemand))); creator.FinishPDF_FileName("FinCard"); }
public static void PrintSkadencar(DateTime fromDate, DateTime toDate, List <int> lstBranches, List <int> lstInsuranceCompanies, List <int> lstInsuranceSubTypes) { 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; PDFCreators pdf = new PDFCreators(false, 10, 10, 10, 10); pdf.OpenPDF(); pdf.SetTitle("Скаденцар"); pdf.SetTitle("Истек на полиса од " + fromDate.ToShortDateString() + " до " + toDate.ToShortDateString()); pdf.SetTitleLeft10("Датум и време на печатење: " + DateTime.Now.ToShortDateString() + " " + DateTime.Now.ToShortTimeString()); string query = @"select p.id, pi.id as policyItemID, pi.policynumber, p.applicationdate, ist.shortdescription as insurancesubtypename, ic.shortname as insurancecompanyname, c.name as clientname, o.name as ownername, it.shortname as insurancetypename, p.startdate, p.enddate, ist.id as insuranceSubTypeID from policies p inner join policyitems pi on pi.policyid = p.id inner join clients c on p.clientid = c.id inner join clients o on p.ownerid = o.id inner join insurancecompanies ic on p.insurancecompanyid=ic.id inner join insurancesubtypes ist on pi.insurancesubtypeid=ist.id inner join insurancetypes it on ist.insurancetypeid=it.id where p.branchid in (" + ReportFinPolicies.GetFromIDs(lstBranches) + ") " + @" and p.insurancecompanyid in (" + ReportFinPolicies.GetFromIDs(lstInsuranceCompanies) + ") " + @" and pi.insurancesubtypeid in (" + ReportFinPolicies.GetFromIDs(lstInsuranceSubTypes) + ") " + @" and p.enddate>='" + y1 + - +m1 + - +d1 + "'" + @" and p.enddate<='" + y2 + - +m2 + - +d2 + "'" + @" and p.discard = 0 " + @" order by p.enddate"; //@" --query so marketing agent"; List <SummaryForSkadencar> lst = dcdc.ExecuteQuery <SummaryForSkadencar>(query).ToList(); int counter = 1; foreach (SummaryForSkadencar ss in lst) { string[] headers = new string[5]; headers[0] = "Р.бр.: " + counter.ToString(); headers[1] = "Полиса: " + ss.policynumber; headers[2] = "Класа: " + ss.insurancetypename; headers[3] = "Подкласа: " + ss.insurancesubtypename; headers[4] = "О.компанија: " + ss.insurancecompanyname; TypeCode[] codes = new TypeCode[headers.Length]; codes[0] = TypeCode.String; codes[1] = TypeCode.String; codes[2] = TypeCode.String; codes[3] = TypeCode.String; codes[4] = TypeCode.String; float[] policyColumnsWidths = new float[headers.Length]; policyColumnsWidths[0] = 16; policyColumnsWidths[1] = 20; policyColumnsWidths[2] = 24; policyColumnsWidths[3] = 24; policyColumnsWidths[4] = 16; pdf.CreateTableForSkadencar(headers.Count(), false, headers, "", policyColumnsWidths); object[] vals = new object[headers.Count()]; vals[0] = "Издадена на: " + ss.applicationdate.ToShortDateString(); vals[1] = "Скаденца: " + ss.startdate.ToShortDateString() + " - " + ss.enddate.ToShortDateString(); vals[2] = "Договорувач: " + ss.clientname; vals[3] = "Осигуреник: " + ss.ownername; string regNumber = ""; Broker.DataAccess.Control con = Broker.DataAccess.Control.GetByInsuranceSubTypeAndTexbBoxID(ss.insuranceSubTypeID, Broker.DataAccess.Control.REGISTRATION_NUMBER); if (con != null) { PolicyExtendInformation regNumberPEI = PolicyExtendInformation.GetByPolicyItemAndControl(ss.policyItemID, con.ID); if (regNumberPEI != null) { regNumber = regNumberPEI.Value; } } vals[4] = "Регистрација: " + regNumber; pdf.AddDataRowWithBorder(vals, headers.Count(), codes); pdf.AddTable(); counter++; } pdf.FinishPDF_FileName("Skadencar"); }