public static List <Payment> GetInPeriod(DateTime fromDate, DateTime toDate, List <int> lstBranhces,
List <int> lstInsuranceCompanies, List <int> lstInsuranceSubTypes, List <int> lstUsers, List <int> lstMarketingAgents)
{
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;
DataClassesDataContext dc = new DataClassesDataContext();
string query = @"SELECT pay.* from payments pay, rates r, policyitems pi, policies p
where r.policyitemid=pi.id and pi.policyid=p.id and pay.rateid=r.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 pay.date>='" + y1 + - +m1 + - +d1 + "'" +
@" and pay.date<='" + y2 + - +m2 + - +d2 + "'" +
@" and p.discard = 0 ";
List <Payment> lst = dc.ExecuteQuery <Payment>(query).ToList();
return(lst);
}
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");
}
protected void btnReport_Click(object sender, EventArgs e)
{
DateTime fromDate = Convert.ToDateTime(tbFromDate.Text);
DateTime toDate = Convert.ToDateTime(tbToDate.Text);
List <int> lstBranches = new List <int>();
foreach (ListItem li in cblBranches.Items)
{
if (li.Selected)
{
if (li.Value != "All")
{
lstBranches.Add(Convert.ToInt32(li.Value));
}
}
}
if (lstBranches.Count == 0 && rblGroupType.SelectedValue == "one")
{
RegisterStartupScript("myAlert", "<script>alert('Немате избрано филијала!')</script>");
return;
}
List <int> lstInsuranceCompanies = new List <int>();
foreach (ListItem li in cblInsuranceCompanies.Items)
{
if (li.Selected)
{
if (li.Value != "All")
{
lstInsuranceCompanies.Add(Convert.ToInt32(li.Value));
}
}
}
if (lstInsuranceCompanies.Count == 0 && rblGroupType.SelectedValue == "one")
{
RegisterStartupScript("myAlert", "<script>alert('Немате избрано осигурителна компанија!')</script>");
return;
}
List <int> lstInsuranceSubTypes = new List <int>();
//foreach (ListItem li in cblInsuranceSubTypes.Items) {
// if (li.Selected) {
// if (li.Value != "All") {
// lstInsuranceSubTypes.Add(Convert.ToInt32(li.Value));
// }
// }
//}
foreach (TreeNode tn in tvInsuranceTypes.Nodes)
{
foreach (TreeNode tsn in tn.ChildNodes)
{
if (tsn.Checked)
{
lstInsuranceSubTypes.Add(Convert.ToInt32(tsn.Value));
}
}
}
if (lstInsuranceSubTypes.Count == 0 && rblGroupType.SelectedValue == "one")
{
RegisterStartupScript("myAlert", "<script>alert('Немате избрано подкласа на осигурување!')</script>");
return;
}
List <int> lstUsers = new List <int>();
foreach (ListItem li in cblUsers.Items)
{
if (li.Selected)
{
if (li.Value != "All")
{
lstUsers.Add(Convert.ToInt32(li.Value));
}
}
}
if (lstUsers.Count == 0 && rblGroupType.SelectedValue == "one")
{
RegisterStartupScript("myAlert", "<script>alert('Немате избрано корисник!')</script>");
return;
}
List <int> lstMarketingAgents = new List <int>();
foreach (ListItem li in cblUsers.Items)
{
if (li.Selected)
{
if (li.Value != "All" && li.Value != "NoMarketingAgents" && li.Value != "AllMarketingAgents")
{
lstMarketingAgents.Add(Convert.ToInt32(li.Value));
}
}
}
Dictionary <string, string> printItems = new Dictionary <string, string>();
foreach (ListItem li in cblReportItems.Items)
{
if (li.Selected)
{
printItems.Add(li.Value, li.Text);
}
}
if (printItems.Count == 0 && rblGroupType.SelectedValue == "total")
{
RegisterStartupScript("myAlert", "<script>alert('Изберете барем една ставка!')</script>");
return;
}
if (printItems.Keys.Contains("PaidPremiumValuePercent") && rblGroupType.SelectedValue == "total")
{
RegisterStartupScript("myAlert", "<script>alert('Процентот не е збирна функција!')</script>");
return;
}
ReportFinPolicies.PrintReportFinPolicies(fromDate, toDate, rblSeparatePeriodType.SelectedValue, rblGroupType.SelectedValue,
lstBranches, lstInsuranceCompanies, lstInsuranceSubTypes, lstUsers, lstMarketingAgents, printItems, rblSeparatePeriodType.SelectedIndex);
}
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");
}