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