Ejemplo n.º 1
0
        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();
        }
Ejemplo n.º 2
0
        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();
        }
Ejemplo n.º 3
0
        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();
        }