private void bindingNavigatorDeleteItem_Click(object sender, EventArgs e)
{
//int SelectedCustomer = CustomersGridView.CurrentCell.RowIndex;
//int ClientID = (int)CustomersGridView.Rows[SelectedCustomer].Cells[0].Value;
var queryMeal = from Meal in db.Meals
where Meal.id_customer == ClientID
select Meal;
foreach (var item in queryMeal.ToList())
{
var queryProd_Meals = from prod_Meal in db.Meals_products
where prod_Meal.id_meal == item.id_meal
select prod_Meal;
foreach (var item2 in queryProd_Meals.ToList())
{
string insertStatement = "Delete From Meals_products where id_meal = " + item2.id_meal;
db.ExecuteQuery <Meals_product>(insertStatement);
}
db.Meals.DeleteOnSubmit(item);
}
var queryClient = from Client in db.Customers
where Client.id_customer == ClientID
select Client;
db.Customers.DeleteOnSubmit(queryClient.First());
db.SubmitChanges();
customersTableAdapter.Fill(this.dataSet.Customers);
}
//获取题目可用数量
public int GetNumberOfTopicBySort(string sortId)
{
string sql = "select count(1) from Topic " +
"where SecondPointId in " +
"(select SecondPointId from SecondPoint where IsChose = 1)" +
"and TopicSourceId in (select TopicSourceId from TopicSource where IsChose = 1)" +
"and SortId = {0}";
return(db.ExecuteQuery <int>(sql, new object[] { int.Parse(sortId) }).First());
}
public static List <VIEW_案例信息_城市表_网站表> 获取当前要整理入库的案例(string 城市名称, string 网站名称, int 个数)
{
List <VIEW_案例信息_城市表_网站表> list = new List <VIEW_案例信息_城市表_网站表>();
try
{
if (string.IsNullOrEmpty(城市名称) || string.IsNullOrEmpty(网站名称) || 个数 < 1)
{
return(list);
}
using (DataClassesDataContext db = new DataClassesDataContext())
{
var result = db.ExecuteQuery <VIEW_案例信息_城市表_网站表>("exec 获取当前要整理入库的案例_根据城市_网站 {0},{1},{2}", new object[] { 城市名称, 网站名称, 个数 });
list = result.ToList <VIEW_案例信息_城市表_网站表>();
}
if (list != null && list.Count > 0)
{
list.ForEach(delegate(VIEW_案例信息_城市表_网站表 obj)
{
obj.来源 = 网站名称;
obj.城市 = 城市名称;
});
}
}
catch (Exception ex)
{
log.Error(string.Format("获取当前要整理入库的案例_异常,(城市名称:{0},网站名称{1},个数:{2})",
城市名称 == null ? "null" : 城市名称, 网站名称 == null ? "null" : 网站名称, 个数),
ex);
}
return(list);
}
public static List <VIEW_案例信息_城市表_网站表> 获取当前要整理入库的案例(int 个数)
{
List <VIEW_案例信息_城市表_网站表> list = new List <VIEW_案例信息_城市表_网站表>();
try
{
using (DataClassesDataContext db = new DataClassesDataContext())
{
var result = db.ExecuteQuery <VIEW_案例信息_城市表_网站表>("exec 获取当前要整理入库的案例 {0}", new object[] { 个数 });
list = result.ToList <VIEW_案例信息_城市表_网站表>();
}
if (list != null && list.Count > 0)
{
list.ForEach(delegate(VIEW_案例信息_城市表_网站表 obj)
{
obj.来源 = obj.网站;
});
}
}
catch (Exception ex)
{
log.Error(string.Format("获取当前要整理入库的案例_异常,(个数:{0})", 个数), ex);
}
return(list);
}
public static List <VIEW_案例信息_城市表_网站表> GetVIEW案例信息_根据城市网站案例日期的区间获取案例(int?cityId, int?webId, DateTime 开始日期, DateTime 结束日期, int start, int pageLength, bool isGetCount, out int count, out string message)
{
string startDate = 开始日期.ToString("yyyy-MM-dd HH:mm:ss");
string endDate = 结束日期.ToString("yyyy-MM-dd HH:mm:ss");
message = "";
count = 0;
List <VIEW_案例信息_城市表_网站表> list = new List <VIEW_案例信息_城市表_网站表>();
using (DataClassesDataContext db = new DataClassesDataContext())
{
StringBuilder sbSql = new StringBuilder("select * from VIEW_案例信息_城市表_网站表 with(nolock) where 1=1");
StringBuilder sbSql2 = new StringBuilder("select count(*) from VIEW_案例信息_城市表_网站表 with(nolock) where 1=1");
try
{
if (cityId != null)
{
sbSql.Append(" and 城市ID=").Append(cityId);
sbSql2.Append(" and 城市ID=").Append(cityId);
}
if (webId != null)
{
sbSql.Append(" and 网站ID=").Append(webId);
sbSql2.Append(" and 网站ID=").Append(webId);
}
sbSql.Append(" and 案例时间>='").Append(startDate).Append("'");
sbSql.Append(" and 案例时间<='").Append(endDate).Append("'");
sbSql2.Append(" and 案例时间>='").Append(startDate).Append("'");
sbSql2.Append(" and 案例时间<='").Append(endDate).Append("'");
if (isGetCount)
{
count = db.ExecuteQuery <int>(sbSql2.ToString()).FirstOrDefault();
}
}
catch (Exception ex)
{
message = "系统异常：sql(" + sbSql2.ToString() + "),异常信息：" + ex.Message;;
return(new List <VIEW_案例信息_城市表_网站表>());
}
list = db.ExecuteQuery <VIEW_案例信息_城市表_网站表>(sbSql.ToString().GetPageSql("ID desc", start, pageLength)).ToList();
}
return(list);
}
protected void Button1_Click(object sender, EventArgs e)
{
if (IsValid)
{
string regno = (string)Session["ID"];
regno = regno.Replace(" ", String.Empty).ToString();
Response.Write("Register Number:" + regno);
string cid = DropDownList1.SelectedValue;
cid = cid.Replace(" ", String.Empty).ToString();;
// var flag = "f";
var ans = "no";
DataClassesDataContext cx3 = new DataClassesDataContext();
var result = cx3.ExecuteQuery <detailCompany>("Select * from detailCompany where companyid={0}", cid);
foreach (var r in result)
{
//Response.Write("true");
using (DataClassesDataContext cx4 = new DataClassesDataContext())
{
var query = cx4.ExecuteQuery <StudentsJobApplied>("Select * from StudentsJobApplied where studentid={0}", regno);
foreach (var s in query)
{
// Response.Write("Company ID: "+s.companyid+" "+cid+" Student ID: "+s.studentid+" "+regno);
if (s.companyid.Contains(cid))
{
if (s.studentid.Contains(regno))
{
lbstatus.Text = "You have Already Registered!";
ans = "yes";
}
}
}
if (ans == "no")
{
StudentsJobApplied job_reg = new StudentsJobApplied
{
companyid = DropDownList1.SelectedValue,
studentid = regno,
};
cx4.StudentsJobApplieds.InsertOnSubmit(job_reg);
cx4.SubmitChanges();
lbstatus.Text = "Successfully Applied";
//flag = "t";
}
}
}
/* if(flag!="t")
* {
* //Response.Write("false");
* lbstatus.Text = "Invalid - Company ID Does not exists";
* }*/
}
}
private void RemoveMealButton_Click(object sender, EventArgs e)
{
int SelectedMeal = CustomerMealsGridView.CurrentCell.RowIndex;
int idOfMeal = (int)CustomerMealsGridView.Rows[SelectedMeal].Cells[0].Value;
var queryProd_Meals = from prod_Meal in db.Meals_products
where prod_Meal.id_meal == idOfMeal
select prod_Meal;
foreach (var item2 in queryProd_Meals.ToList())
{
string insertStatement = "Delete From Meals_products where id_meal = " + item2.id_meal;
db.ExecuteQuery <Meals_product>(insertStatement);
}
try
{
string insertStatement = "Delete From Meals where id_meal = " + idOfMeal;
db.ExecuteQuery <Meals_product>(insertStatement);
ClearProductsOfMeals(sender, e);
}
catch (Exception)
{
MessageBox.Show("First choose Product of Meal Then press to Removed");
}
}
/// <summary>
/// 初始化推荐书籍列表
/// </summary>
protected void InitRecommendTable()
{
//通过数据库得到对应用户id关注的图书id
DataClassesDataContext db = new DataClassesDataContext();
var books = db.ExecuteQuery <Book>("select * from BookWareHouse,Book where BookWareHouse.BookID = Book.BookID"
+ " and BookWareHouse.Type = 'recommend' and BookWareHouse.QuanZiID = '" + Session["QuanZiID"].ToString() + "'");
foreach (Book book in books)
{
TableRow row = new TableRow();
TableCell cell = new TableCell();
ImageButton img = new ImageButton();
img.ImageUrl = book.CoverImagePath.ToString();
img.Width = 120;
img.PostBackUrl = "~/BookWareHouse/CheckBook.aspx?BookID=" + book.BookID.ToString();
cell.Controls.Add(img);
cell.HorizontalAlign = HorizontalAlign.Center;
row.Cells.Add(cell);
TableRow row2 = new TableRow();
TableCell cell2 = new TableCell();
HyperLink link = new HyperLink();
link.NavigateUrl = "~/BookWareHouse/CheckBook.aspx?BookID=" + book.BookID.ToString();
link.Text = book.Title.ToString();
cell2.Controls.Add(link);
cell2.HorizontalAlign = HorizontalAlign.Center;
row2.Cells.Add(cell2);
Table temptable = new Table();
temptable.Rows.Add(row);
temptable.Rows.Add(row2);
TableRow temprow = new TableRow();
TableCell tempcell = new TableCell();
tempcell.Controls.Add(temptable);
tempcell.HorizontalAlign = HorizontalAlign.Center;
temprow.Cells.Add(tempcell);
temprow.CssClass = "s_item";
table1.Rows.Add(temprow);
}
}
//根据考试Id获取某个考生的考试记录
public StuExam GetStuExam(string sId, string examId)
{
try
{
string sql = "select * from StuExam where StudentId = {0} " +
"and PaperId in (select PaperId from Paper where ExamId = {1} )";
List <StuExam> stuExams = db.ExecuteQuery <StuExam>(sql, new Object[] { sId, examId }).ToList();
if (stuExams.Count <= 0)
{
return(null);
}
else
{
return(stuExams.First());
}
}
catch (Exception e)
{
return(null);
}
}
protected void Page_Load(object sender, EventArgs e)
{
DataClassesDataContext cx2 = new DataClassesDataContext();
var result = cx2.ExecuteQuery <StudentsJobApplied>("Select * from StudentsJobApplied");
int count = 0;
foreach (var r in result)
{
count = count + 1;
}
if (count == 0)
{
lbstudentno.Text = "No Students Applied Yet";
}
else
{
lbstudentno.Text = "The Total Number of Students Applied are " + count;
}
}
protected void Button3_Click(object sender, EventArgs e)
{
//display
string regno = string.Empty;
regno = (string)Session["ID"];
DataClassesDataContext dc2 = new DataClassesDataContext();
var result = dc2.ExecuteQuery <StudentDetail>("Select * from StudentDetail where studentid={0}", regno);
foreach (var r in result)
{
string usercheck = r.studentid;
usercheck = usercheck.Replace(" ", String.Empty);
if (usercheck.Equals(regno))
{
txtname.Text = r.name;
txtdob.Text = r.dob;
txtmailid.Text = r.mailid;
txtmobno.Text = r.mobno;
txtaddress.Text = r.address;
DropDownList1.SelectedValue = r.dept;
if (r.degree == "UG")
{
rdegree.SelectedIndex = 0;
txtug.Text = r.ug;
}
else
{
rdegree.SelectedIndex = 1;
txtug.Text = r.ug;
txtpg.Text = r.pg;
}
txt10.Text = r.ssc;
txt12.Text = r.hsc;
Button4.Visible = true;
Button5.Visible = true;
}
else
{
lbstatus.Text = "You Have Not Registered Any Information...Please Register";
}
}
}
protected void btnlogin_Click(object sender, EventArgs e)
{
if (IsValid)
{
string uname = txtname.Text;
string pwd = txtpwd.Text;
if (uname == "admin" && pwd == "admin")
{
Response.Redirect("Admin.aspx");
}
else
{
DataClassesDataContext cx2 = new DataClassesDataContext();
var result = cx2.ExecuteQuery <login>("Select * from login where username={0}", uname);
var f = "f";
foreach (var r in result)
{
f = "t";
string str = r.password;
str = str.Replace(" ", String.Empty);
if (str.Equals(pwd))
{
//lbcheck.Text = "valid";
Session["ID"] = txtname.Text;
Response.Redirect("Student.aspx");
}
else
{
lbcheck.Text = "Invalid Password";
}
}
if (f != "t")
{
lbcheck.Text = "Username does not exist - Please go and Register";
}
}
}
}
protected void btnsubmit_Click(object sender, EventArgs e)
{
if (IsValid)
{
string ans = "no";
string uname = txtname.Text;
uname = uname.Replace(" ", String.Empty);
Response.Write("USERNAME" + uname);
using (DataClassesDataContext cx1 = new DataClassesDataContext())
{
var query = cx1.ExecuteQuery <login>("Select * from login where username={0}", uname);
foreach (var s in query)
{
Response.Write("INSIDE USERNAME" + s.username);
if (s.username.Equals(uname))
{
lbcheck.Text = "You have Already Registered!";
ans = "yes";
}
}
if (ans == "no")
{
login student_reg = new login
{
username = txtname.Text,
password = txtpwd1.Text
};
cx1.logins.InsertOnSubmit(student_reg);
cx1.SubmitChanges();
lbcheck.Text = "Successfully Registered";
}
txtname.Text = "";
txtpwd1.Text = "";
txtpwd2.Text = "";
}
}
}
public Post ExecuteQuery()
{
Step();
return(Linq2SqlContext.ExecuteQuery <Post>("select * from Posts where Id = {0}", i).First());
}
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");
}
/// <summary>
/// 搜索按钮事件
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void searchbotton_Click(object sender, EventArgs e)
{
if (Page.IsValid)
{
int n = sel.SelectedIndex;
string[] type = { "Title", "Author", "Publisher", "ISBN", "Category" };
DataClassesDataContext db = new DataClassesDataContext();
var books = db.ExecuteQuery <Book>("select * from Book where " + type[n] + " like \'%" + inputtextbox.Text + "%\'");
foreach (Book book in books)
{
TableRow row = new TableRow();
row.CssClass = "s_item";
TableCell cell = new TableCell();
cell.CssClass = "tdstyle";
ImageButton img = new ImageButton();
img.ImageUrl = book.CoverImagePath.ToString();
img.Width = 150;
img.PostBackUrl = "~/BookWareHouse/CheckBook.aspx?BookID=" + book.BookID.ToString();
cell.Controls.Add(img);
cell.HorizontalAlign = HorizontalAlign.Right;
row.Cells.Add(cell);
TableCell cell2 = new TableCell();
cell2.CssClass = "td_pad";
cell2.HorizontalAlign = HorizontalAlign.Left;
Table celltable = new Table();
celltable.CssClass = "tbstyle";
celltable.HorizontalAlign = HorizontalAlign.Left;
TableRow rightrow1 = new TableRow();
TableCell rightcell1 = new TableCell();
rightcell1.CssClass = "td_row";
rightcell1.HorizontalAlign = HorizontalAlign.Left;
HyperLink link = new HyperLink();
link.NavigateUrl = "~/BookWareHouse/CheckBook.aspx?BookID=" + book.BookID.ToString();
link.Text = book.Title.ToString();
link.Font.Size = 18;
link.CssClass = "h4";
rightcell1.Controls.Add(link);
rightrow1.Cells.Add(rightcell1);
celltable.Rows.Add(rightrow1);
TableRow rightrow2 = new TableRow();
TableCell rightcell2 = new TableCell();
rightcell2.CssClass = "td_row";
rightcell2.Text = book.Author + "/" + book.Publisher + "/" + book.ISBN;
rightrow2.Cells.Add(rightcell2);
celltable.Rows.Add(rightrow2);
TableRow rightrow3 = new TableRow();
TableCell rightcell3 = new TableCell();
rightcell3.CssClass = "td_row";
rightcell3.Text = "类型：" + book.Category;
rightrow3.Cells.Add(rightcell3);
celltable.Rows.Add(rightrow3);
TableRow rightrow4 = new TableRow();
TableCell rightcell4 = new TableCell();
rightcell4.CssClass = "td_row";
rightcell4.Text = "评分：" + book.Score.ToString();
rightrow4.Cells.Add(rightcell4);
celltable.Rows.Add(rightrow4);
cell2.Controls.Add(celltable);
row.Cells.Add(cell2);
Table1.Rows.Add(row);
}
}
}
/// <summary>
/// 初始化热门图书列表
/// </summary>
protected void InitHotBookTable()
{
//通过数据库得到热门图书的图书id
DataClassesDataContext db = new DataClassesDataContext();
var books = db.ExecuteQuery <Book>("select * from Book order by Score DESC");
int n = 0;
foreach (Book book in books)
{
TableRow row = new TableRow();
row.CssClass = "s_item";
TableCell cell = new TableCell();
cell.CssClass = "tdstyle";
ImageButton img = new ImageButton();
img.ImageUrl = book.CoverImagePath.ToString();
img.Width = 150;
img.PostBackUrl = "~/BookWareHouse/CheckBook.aspx?BookID=" + book.BookID.ToString();
cell.Controls.Add(img);
cell.HorizontalAlign = HorizontalAlign.Left;
row.Cells.Add(cell);
TableCell cell2 = new TableCell();
cell2.CssClass = "td_pad";
cell2.HorizontalAlign = HorizontalAlign.Left;
Table celltable = new Table();
//celltable.CssClass = "tbstyle";
celltable.HorizontalAlign = HorizontalAlign.Left;
TableRow rightrow1 = new TableRow();
TableCell rightcell1 = new TableCell();
//rightcell1.CssClass = "td_row";
rightcell1.HorizontalAlign = HorizontalAlign.Left;
HyperLink link = new HyperLink();
link.NavigateUrl = "~/BookWareHouse/CheckBook.aspx?BookID=" + book.BookID.ToString();
link.Text = book.Title.ToString();
link.Font.Size = 12;
link.CssClass = "h4";
rightcell1.Controls.Add(link);
rightrow1.Cells.Add(rightcell1);
celltable.Rows.Add(rightrow1);
TableRow rightrow2 = new TableRow();
TableCell rightcell2 = new TableCell();
// rightcell2.CssClass = "td_row";
rightcell2.Text = book.Author + "/" + book.Publisher + "/" + book.ISBN;
rightrow2.Cells.Add(rightcell2);
celltable.Rows.Add(rightrow2);
TableRow rightrow3 = new TableRow();
TableCell rightcell3 = new TableCell();
//rightcell3.CssClass = "td_row";
rightcell3.Text = "类型：" + book.Category;
rightrow3.Cells.Add(rightcell3);
celltable.Rows.Add(rightrow3);
TableRow rightrow4 = new TableRow();
TableCell rightcell4 = new TableCell();
//rightcell4.CssClass = "td_row";
rightcell4.Text = "评分：" + book.Score.ToString();
rightrow4.Cells.Add(rightcell4);
celltable.Rows.Add(rightrow4);
cell2.Controls.Add(celltable);
row.Cells.Add(cell2);
TableCell cell3 = new TableCell();
cell3.Width = 200;
row.Cells.Add(cell3);
hotbooktable.Rows.Add(row);
n++;
if (n > 2)
{
break;
}
}
}
public static void PrintReportForASO(int insuranceCompanyID, DateTime startDate, DateTime endDate)
{
DateTime dt1 = startDate;
DateTime dt2 = endDate;
int d1 = dt1.Day;
int m1 = dt1.Month;
int y1 = dt1.Year;
int d2 = dt2.Day;
int m2 = dt2.Month;
int y2 = dt2.Year;
DataClassesDataContext dcdc = new DataClassesDataContext();
PDFCreators pdf = new PDFCreators(false, 17, 17, 25, 25);
pdf.OpenPDF();
pdf.SetTitle(" ");
pdf.SetTitle(" ");
pdf.AddJDBLogo(660, 480);
pdf.SetTitle("Квартален извештај за Брокерските друштва");
pdf.SetTitle("за период " + startDate.ToShortDateString() + " - " + endDate.ToShortDateString());
if (insuranceCompanyID > 0)
{
InsuranceCompany ic = InsuranceCompany.Get(insuranceCompanyID);
pdf.SetTitle("Осигурителна компанија: " + ic.ShortName);
}
pdf.SetTitleLeft("");
string[] headers = new string[6];
headers[0] = "Класа/подкласа на осигурување";
headers[1] = "Шифра";
headers[2] = "Број на договори";
headers[3] = "Премија";
headers[4] = "Реализирана провизија";
headers[5] = "Пресметана провизија";
TypeCode[] codes = new TypeCode[headers.Count()];
codes[0] = TypeCode.String;
codes[1] = TypeCode.String;
codes[2] = TypeCode.Int32;
codes[3] = TypeCode.Decimal;
codes[4] = TypeCode.Decimal;
codes[5] = TypeCode.Decimal;
float[] policyColumnsWidths = new float[headers.Count()];
policyColumnsWidths[0] = 55;
policyColumnsWidths[1] = 5;
policyColumnsWidths[2] = 10;
policyColumnsWidths[3] = 10;
policyColumnsWidths[4] = 10;
policyColumnsWidths[5] = 10;
pdf.CreateTableWithBorder(headers.Count(), false, headers, "", policyColumnsWidths);
int totalNumberOfPolicies = 0;
decimal totalPremiumValue = 0;
decimal totalBrokValue = 0;
decimal totalRealBrokValue = 0;
List <InsuranceType> listInsuranceTypes = InsuranceType.Table.OrderBy(c => c.Code).ToList();
foreach (InsuranceType it in listInsuranceTypes)
{
object[] vals = new object[headers.Count()];
vals[0] = it.Name;
vals[1] = it.Code;
List <InsuranceSubType> listInsuranceSubTypes = InsuranceSubType.GetByInsuranceType(it.ID).OrderBy(c => c.Code).ToList();
int numberOfPolicies = 0;
int premiumValue = 0;
decimal brokValue = 0;
decimal realBrokValue = 0;
decimal tmpBrokValue = 0;
decimal tmpBrokBaseValue = 0;
decimal totFacturedValueInClass = 0;
decimal totFacturedBrokerageValuePerClass = 0;
//decimal tmpBrokValueInOtherClass = 0;
decimal k = 0;
foreach (InsuranceSubType ist in listInsuranceSubTypes)
{
string queryCountAndPremiumDef = string.Empty;
string queryCountAndPremiumFullPremiumValue = string.Empty;
string queryCountAndPremiumInOtherClasses = string.Empty;
string getCalculatedBrokerages = string.Empty;
string queryCalcBrokerageInOtherClases = string.Empty;
string queryRealPaidValues = string.Empty;
string queryGetFacturedBrokeragesPayments = string.Empty;
decimal tmpBrokValueInOtherClass = 0;
decimal tmpBrokBaseValueTot = 0;
decimal tmpBrokValueTot = 0;
if (insuranceCompanyID > 0)
{
queryCountAndPremiumFullPremiumValue = @"select count(pi1.id) as Count,
sum(cast(pi1.premiumvalue as int)) as SumPremiumValue
from policies p1 inner join policyitems pi1 on pi1.policyid=p1.id
where p1.discard=0
and pi1.insurancesubtypeid=" + ist.ID +
" and p1.insurancecompanyid = " + insuranceCompanyID +
@" and p1.applicationdate >='" + y1 + - +m1 + - +d1 + "' and p1.applicationdate<='" + y2 + - +m2 + - +d2 + "'";
queryCountAndPremiumDef = @"select 1, sum(cast(pei.Value as int)) as SumPremiumValue
from policies p ,policyitems pi,
policyextendinformations pei,controls c, controlappropriateinsurancesubtype caist
where p.discard=0
and caist.isactive=1
and pi.policyid=p.id
and caist.controlid=pei.controlid
and c.variabletypeid in (2,3,4)
and c.specialfieldtypeid = 2
and c.insurancesubtypeid=" + ist.ID +
@" and pei.policyitemid=pi.id
and pei.controlid=c.id
and pei.Value<>'0'
and p.insurancecompanyid = " + insuranceCompanyID +
@" and p.applicationdate >='" + y1 + - +m1 + - +d1 + "' and p.applicationdate<='" + y2 + - +m2 + - +d2 + "'";
queryCountAndPremiumInOtherClasses = @"select count(pi.id) as Count, sum(cast(pei.Value as int)) as SumPremiumValue
from policies p ,policyitems pi,
policyextendinformations pei,controls c, controlappropriateinsurancesubtype caist
where p.discard=0
and caist.insurancesubtypeid=" + ist.ID +
@" and pi.policyid=p.id
and caist.controlid=pei.controlid
and caist.isactive=1
and pei.policyitemid=pi.id
and pei.controlid=c.id
and pei.Value<>'0'
and p.insurancecompanyid = " + insuranceCompanyID +
@" and p.applicationdate >='" + y1 + - +m1 + - +d1 + "' and p.applicationdate<='" + y2 + - +m2 + - +d2 + "'";
getCalculatedBrokerages = @"select pi1.id, pi1.insurancesubtypeid,pi1.premiumvalue,
(select sum(convert(int,pei.Value))
from policyitems pi,
policyextendinformations pei,controls c, controlappropriateinsurancesubtype caist
where caist.isactive=1
and caist.controlid=pei.controlid
and c.variabletypeid in (2,3,4)
and c.specialfieldtypeid = 2
and pei.policyitemid=pi.id
and pei.controlid=c.id
and pei.Value<>'0'
and pi.id = pi1.id) as basicvalue,
case when pi1.brokerageid is not null then
(case when c1.islaw=1 then
(select b.percentageforlaws from brokerages b
where b.insurancesubtypeid=pi1.insurancesubtypeid and
b.id=pi1.brokerageid)
else
(select b.percentageforprivates from brokerages b
where b.insurancesubtypeid=pi1.insurancesubtypeid
and b.id=pi1.brokerageid)
end)
else
(case when pi1.packetbrokerageid is not null then
(case when c1.islaw=1 then
(select pist.BrokeragePecentageForPrivates from PacketsInsuranceSubTypes pist
where pist.insurancesubtypeid=pi1.insurancesubtypeid
and pist.id=pi1.packetbrokerageid)
else
(select pist.BrokeragePecentageForLaws from PacketsInsuranceSubTypes pist
where pist.insurancesubtypeid=pi1.insurancesubtypeid
and pist.id=pi1.packetbrokerageid)
end)
else 0
end)
end as brokeragepercentage
from policies p1 inner join policyitems pi1 on pi1.policyid=p1.id
inner join clients c1 on p1.clientid=c1.id
where p1.discard=0
and pi1.insurancesubtypeid=" + ist.ID +
@" and p1.insurancecompanyid = " + insuranceCompanyID +
@" and p1.applicationdate >='" + y1 + - +m1 + - +d1 + "' and p1.applicationdate<='" + y2 + - +m2 + - +d2 + "'";
queryCalcBrokerageInOtherClases = @"select pi.id, pei.Value,
case when pi.brokerageid is not null then
(case when c1.islaw=1 then
(select b.percentageforlaws from brokerages b
where b.insurancesubtypeid=pi.insurancesubtypeid and
b.id=pi.brokerageid)
else
(select b.percentageforprivates from brokerages b
where b.insurancesubtypeid=pi.insurancesubtypeid
and b.id=pi.brokerageid)
end)
else
(case when pi.packetbrokerageid is not null then
(case when c1.islaw=1 then
(select pist.BrokeragePecentageForPrivates from PacketsInsuranceSubTypes pist
where pist.insurancesubtypeid=pi.insurancesubtypeid
and pist.id=pi.packetbrokerageid)
else
(select pist.BrokeragePecentageForLaws from PacketsInsuranceSubTypes pist
where pist.insurancesubtypeid=pi.insurancesubtypeid
and pist.id=pi.packetbrokerageid)
end)
else 0
end)
end as brokeragepercentage
from policies p ,policyitems pi, clients c1,
policyextendinformations pei,controls c, controlappropriateinsurancesubtype caist
where p.discard=0
and p.clientid=c1.id
and caist.insurancesubtypeid=" + ist.ID +
@" and pi.policyid=p.id
and caist.controlid=pei.controlid
and caist.isactive=1
and pei.policyitemid=pi.id
and pei.controlid=c.id
and pei.Value<>'0' " +
@" and p.insurancecompanyid = " + insuranceCompanyID +
@" and p.applicationdate >='" + y1 + - +m1 + - +d1 + "' and p.applicationdate<='" + y2 + - +m2 + - +d2 + "'";
queryRealPaidValues = @"select sum(fcpvpist.paidvalue) as sumpaidvalue
from FacCollPaidValuesPerInsSubTypes fcpvpist,facturecollectedpaidvalues fcpv, factureitems fi, factures f
where fcpvpist.facturecollectedpaidvalueid=fcpv.id
and fcpv.factureitemid=fi.id
and fi.factureid=f.id
and f.discard=0
and fcpv.paiddate>='" + y1 + - +m1 + - +d1 + "' and fcpv.paiddate<='" + y2 + - +m2 + - +d2 + "'" +
@" and fcpvpist.insurancesubtypeid=" + ist.ID +
@" and f.insurancecompanyid=" + insuranceCompanyID;
queryGetFacturedBrokeragesPayments = @"select sum(ppist.brokeragevalue) as tmpValueDecimal from PaymentsPerInsSubTypes ppist, payments pa, rates r,
policyitems pi, policyitemfactureitems pifi, factureitems fi, factures f, policies p, facturepayments fp
where ppist.paymentid=pa.id
and pa.rateid=r.id
and r.policyitemid=pi.id
and pi.policyid=p.id
and pifi.policyitemid=pi.id
and pifi.factureitemid=fi.id
and fi.factureid=f.id
and f.discard=0
and fp.factureid=f.id
and fp.paymentid=pa.id
and p.discard=0
and p.applicationdate>='" + y1 + - +m1 + - +d1 + "'" +
@" and p.applicationdate<='" + y2 + - +m2 + - +d2 + "'" +
@" and p.insurancecompanyid=" + insuranceCompanyID +
@" and ppist.insurancesubtypeid=" + ist.ID;
}
else
{
queryCountAndPremiumFullPremiumValue = @"select count(pi1.id) as Count,
sum(cast(pi1.premiumvalue as int)) as SumPremiumValue
from policies p1 inner join policyitems pi1 on pi1.policyid=p1.id
where p1.discard=0
and pi1.insurancesubtypeid=" + ist.ID +
@" and p1.applicationdate >='" + y1 + - +m1 + - +d1 + "' and p1.applicationdate<='" + y2 + - +m2 + - +d2 + "'";
queryCountAndPremiumDef = @"select 1, sum(cast(pei.Value as int)) as SumPremiumValue
from policies p ,policyitems pi,
policyextendinformations pei,controls c, controlappropriateinsurancesubtype caist
where p.discard=0
and caist.isactive=1
and pi.policyid=p.id
and caist.controlid=pei.controlid
and c.variabletypeid in (2,3,4)
and c.specialfieldtypeid = 2
and c.insurancesubtypeid=" + ist.ID +
@" and pei.policyitemid=pi.id
and pei.controlid=c.id
and pei.Value<>'0'" +
@" and p.applicationdate >='" + y1 + - +m1 + - +d1 + "' and p.applicationdate<='" + y2 + - +m2 + - +d2 + "'";
queryCountAndPremiumInOtherClasses = @"select count(pi.id) as Count, sum(cast(pei.Value as int)) as SumPremiumValue
from policies p ,policyitems pi,
policyextendinformations pei,controls c, controlappropriateinsurancesubtype caist
where p.discard=0
and caist.insurancesubtypeid=" + ist.ID +
@" and pi.policyid=p.id
and caist.controlid=pei.controlid
and caist.isactive=1
and pei.policyitemid=pi.id
and pei.controlid=c.id
and pei.Value<>'0'" +
@" and p.applicationdate >='" + y1 + - +m1 + - +d1 + "' and p.applicationdate<='" + y2 + - +m2 + - +d2 + "'";
getCalculatedBrokerages = @"select pi1.id, pi1.insurancesubtypeid,pi1.premiumvalue,
(select sum(convert(int,pei.Value))
from policyitems pi,
policyextendinformations pei,controls c, controlappropriateinsurancesubtype caist
where caist.isactive=1
and caist.controlid=pei.controlid
and c.variabletypeid in (2,3,4)
and c.specialfieldtypeid = 2
and pei.policyitemid=pi.id
and pei.controlid=c.id
and pei.Value<>'0'
and pi.id = pi1.id) as basicvalue,
case when pi1.brokerageid is not null then
(case when c1.islaw=1 then
(select b.percentageforlaws from brokerages b
where b.insurancesubtypeid=pi1.insurancesubtypeid and
b.id=pi1.brokerageid)
else
(select b.percentageforprivates from brokerages b
where b.insurancesubtypeid=pi1.insurancesubtypeid
and b.id=pi1.brokerageid)
end)
else
(case when pi1.packetbrokerageid is not null then
(case when c1.islaw=1 then
(select pist.BrokeragePecentageForPrivates from PacketsInsuranceSubTypes pist
where pist.insurancesubtypeid=pi1.insurancesubtypeid
and pist.id=pi1.packetbrokerageid)
else
(select pist.BrokeragePecentageForLaws from PacketsInsuranceSubTypes pist
where pist.insurancesubtypeid=pi1.insurancesubtypeid
and pist.id=pi1.packetbrokerageid)
end)
else 0
end)
end as brokeragepercentage
from policies p1 inner join policyitems pi1 on pi1.policyid=p1.id
inner join clients c1 on p1.clientid=c1.id
where p1.discard=0
and pi1.insurancesubtypeid=" + ist.ID +
@" and p1.applicationdate >='" + y1 + - +m1 + - +d1 + "' and p1.applicationdate<='" + y2 + - +m2 + - +d2 + "'";
queryCalcBrokerageInOtherClases = @"select pi.id, pei.Value,
case when pi.brokerageid is not null then
(case when c1.islaw=1 then
(select b.percentageforlaws from brokerages b
where b.insurancesubtypeid=pi.insurancesubtypeid and
b.id=pi.brokerageid)
else
(select b.percentageforprivates from brokerages b
where b.insurancesubtypeid=pi.insurancesubtypeid
and b.id=pi.brokerageid)
end)
else
(case when pi.packetbrokerageid is not null then
(case when c1.islaw=1 then
(select pist.BrokeragePecentageForPrivates from PacketsInsuranceSubTypes pist
where pist.insurancesubtypeid=pi.insurancesubtypeid
and pist.id=pi.packetbrokerageid)
else
(select pist.BrokeragePecentageForLaws from PacketsInsuranceSubTypes pist
where pist.insurancesubtypeid=pi.insurancesubtypeid
and pist.id=pi.packetbrokerageid)
end)
else 0
end)
end as brokeragepercentage
from policies p ,policyitems pi, clients c1,
policyextendinformations pei,controls c, controlappropriateinsurancesubtype caist
where p.discard=0
and p.clientid=c1.id
and caist.insurancesubtypeid=" + ist.ID +
@" and pi.policyid=p.id
and caist.controlid=pei.controlid
and caist.isactive=1
and pei.policyitemid=pi.id
and pei.controlid=c.id
and pei.Value<>'0' " +
@" and p.applicationdate >='" + y1 + - +m1 + - +d1 + "' and p.applicationdate<='" + y2 + - +m2 + - +d2 + "'";
queryRealPaidValues = @"select sum(fcpvpist.paidvalue) as sumpaidvalue
from FacCollPaidValuesPerInsSubTypes fcpvpist,facturecollectedpaidvalues fcpv, factureitems fi, factures f
where fcpvpist.facturecollectedpaidvalueid=fcpv.id
and fcpv.factureitemid=fi.id
and fi.factureid=f.id
and f.discard=0
and fcpv.paiddate>='" + y1 + - +m1 + - +d1 + "' and fcpv.paiddate<='" + y2 + - +m2 + - +d2 + "'" +
@" and fcpvpist.insurancesubtypeid=" + ist.ID;
queryGetFacturedBrokeragesPayments = @"select sum(ppist.brokeragevalue) as tmpValueDecimal from PaymentsPerInsSubTypes ppist, payments pa, rates r,
policyitems pi, policyitemfactureitems pifi, factureitems fi, factures f, policies p, facturepayments fp
where ppist.paymentid=pa.id
and pa.rateid=r.id
and r.policyitemid=pi.id
and pi.policyid=p.id
and pifi.policyitemid=pi.id
and pifi.factureitemid=fi.id
and fi.factureid=f.id
and fp.factureid=f.id
and fp.paymentid=pa.id
and f.discard=0
and p.discard=0
and p.applicationdate>='" + y1 + - +m1 + - +d1 + "'" +
@" and p.applicationdate<='" + y2 + - +m2 + - +d2 + "'" +
@" and ppist.insurancesubtypeid=" + ist.ID;
}
List <SummaryClass> summaryListFullPremValue = dcdc.ExecuteQuery <SummaryClass>(queryCountAndPremiumFullPremiumValue).ToList();
List <SummaryClass> summaryListDef = dcdc.ExecuteQuery <SummaryClass>(queryCountAndPremiumDef).ToList();
List <SummaryClass> summaryListInOtherClasses = dcdc.ExecuteQuery <SummaryClass>(queryCountAndPremiumInOtherClasses).ToList();
List <SummaryCalcBrokerageClass> summaryListBrok = dcdc.ExecuteQuery <SummaryCalcBrokerageClass>(getCalculatedBrokerages).ToList();
List <SummaryCalcBrokerageInOtherClass> summaryListCalcBrokerageInOtherClass = dcdc.ExecuteQuery <SummaryCalcBrokerageInOtherClass>(queryCalcBrokerageInOtherClases).ToList();
//List<SummaryCalcRealBrokerageClass> summaryListCalcRealBrokerageClass = dcdc.ExecuteQuery<SummaryCalcRealBrokerageClass>(queryRealPaidValues).ToList();
decimal facturedBrokValue = 0;
List <SummaryForFacturedBrokerages> summaryForFacturedBrokeragesPayments = dcdc.ExecuteQuery <SummaryForFacturedBrokerages>(queryGetFacturedBrokeragesPayments).ToList();
if (summaryForFacturedBrokeragesPayments.Count > 0)
{
if (summaryForFacturedBrokeragesPayments[0].tmpValueDecimal != null)
{
facturedBrokValue += (decimal)summaryForFacturedBrokeragesPayments[0].tmpValueDecimal;
}
}
totFacturedBrokerageValuePerClass += facturedBrokValue;
foreach (SummaryCalcBrokerageClass scbc in summaryListBrok)
{
if (scbc.basicvalue != null)
{
//tmpBrokBaseValue = RateController.RateController.Scale5((scbc.brokeragepercentage / 100) * (scbc.premiumvalue - Convert.ToInt32(scbc.basicvalue)));
tmpBrokBaseValue = (scbc.brokeragepercentage / 100) * (scbc.premiumvalue - Convert.ToInt32(scbc.basicvalue));
brokValue += tmpBrokBaseValue;
tmpBrokBaseValueTot += tmpBrokBaseValue;
}
else
{
//tmpBrokBaseValue = RateController.RateController.Scale5((scbc.brokeragepercentage / 100) * scbc.premiumvalue);
tmpBrokBaseValue = (scbc.brokeragepercentage / 100) * scbc.premiumvalue;
brokValue += tmpBrokBaseValue;
tmpBrokBaseValueTot += tmpBrokBaseValue;
}
//tmpBrokValue = RateController.RateController.Scale5((scbc.brokeragepercentage / 100) * scbc.premiumvalue);
tmpBrokValue = (scbc.brokeragepercentage / 100) * scbc.premiumvalue;
tmpBrokValueTot += tmpBrokValue;
}
foreach (SummaryCalcBrokerageInOtherClass scbioc in summaryListCalcBrokerageInOtherClass)
{
//tmpBrokValueInOtherClass += RateController.RateController.Scale5((scbioc.brokeragepercentage / 100) * Convert.ToDecimal(scbioc.value));
tmpBrokValueInOtherClass += (scbioc.brokeragepercentage / 100) * Convert.ToDecimal(scbioc.value);
//brokValue += RateController.RateController.Scale5((scbioc.brokeragepercentage / 100) * Convert.ToDecimal(scbioc.value));
brokValue += (scbioc.brokeragepercentage / 100) * Convert.ToDecimal(scbioc.value);
}
numberOfPolicies += summaryListFullPremValue[0].Count + summaryListInOtherClasses[0].Count;
totalNumberOfPolicies += summaryListFullPremValue[0].Count;
int inCiclusPremValue = 0;
if (summaryListFullPremValue[0].SumPremiumValue != null)
{
inCiclusPremValue += (int)summaryListFullPremValue[0].SumPremiumValue;
}
if (summaryListDef[0].SumPremiumValue != null)
{
inCiclusPremValue -= (int)summaryListDef[0].SumPremiumValue;
}
if (summaryListInOtherClasses[0].SumPremiumValue != null)
{
inCiclusPremValue += (int)summaryListInOtherClasses[0].SumPremiumValue;
}
premiumValue += inCiclusPremValue;
//if (summaryListCalcRealBrokerageClass[0].sumpaidvalue != null) {
// realBrokValue += (decimal)summaryListCalcRealBrokerageClass[0].sumpaidvalue;
//}
}
vals[2] = numberOfPolicies;
vals[3] = String.Format("{0:#,0.00}", RateController.RateController.Scale5(premiumValue));
totalPremiumValue += premiumValue;
vals[5] = String.Format("{0:#,0.00}", RateController.RateController.Scale5(brokValue));
totalBrokValue += brokValue;
//vals[4] = String.Format("{0:#,0.00}", RateController.RateController.Scale5(realBrokValue));
//vals[4] = String.Format("{0:#,0.00}", RateController.RateController.Scale5(totFacturedValueInClass));
vals[4] = String.Format("{0:#,0.00}", RateController.RateController.Scale5(totFacturedBrokerageValuePerClass));
//totalRealBrokValue += realBrokValue;
//totalRealBrokValue += totFacturedValueInClass;
totalRealBrokValue += totFacturedBrokerageValuePerClass;
pdf.AddDataRowWithBorder(vals, headers.Count(), codes);
}
object[] valsTotal = new object[headers.Count()];
valsTotal[0] = "ВКУПНО";
valsTotal[1] = "";
valsTotal[2] = totalNumberOfPolicies;
valsTotal[3] = String.Format("{0:#,0.00}", RateController.RateController.Scale5(totalPremiumValue));
valsTotal[5] = String.Format("{0:#,0.00}", RateController.RateController.Scale5(totalBrokValue));
valsTotal[4] = String.Format("{0:#,0.00}", RateController.RateController.Scale5(totalRealBrokValue));
pdf.AddDataRowWithBorder(valsTotal, headers.Count(), codes);
pdf.AddTable();
pdf.FinishPDF();
}
public static void AllPolicies(DateTime fromDate, DateTime toDate, int insuranceTypeID, int insuranceSubTypeID, int userID, int branchID, int companyID)
{
DataClassesDataContext dcdc = new DataClassesDataContext();
DateTime dt1 = fromDate;
DateTime dt2 = toDate;
int d1 = dt1.Day;
int m1 = dt1.Month;
int y1 = dt1.Year;
int d2 = dt2.Day;
int m2 = dt2.Month;
int y2 = dt2.Year;
string query = @"select clients.name ,clients.embg, policyItems.policynumber,policies.startdate, policies.enddate, users.username, policyitems.premiumvalue,
policyitems.realpremiumvalue,
case when policyItems.brokerageid is not null then
(case when clients.islaw=1 then
(select b.percentageforlaws from brokerages b
where b.insurancesubtypeid=policyItems.insurancesubtypeid and
b.id=policyItems.brokerageid)
else
(select b.percentageforprivates from brokerages b
where b.insurancesubtypeid=policyItems.insurancesubtypeid
and b.id=policyItems.brokerageid)
end)
else
(case when policyItems.packetbrokerageid is not null then
(case when clients.islaw=1 then
(select pist.BrokeragePecentageForPrivates from PacketsInsuranceSubTypes pist
where pist.insurancesubtypeid=policyItems.insurancesubtypeid
and pist.id=policyItems.packetbrokerageid)
else
(select pist.BrokeragePecentageForLaws from PacketsInsuranceSubTypes pist
where pist.insurancesubtypeid=policyItems.insurancesubtypeid
and pist.id=policyItems.packetbrokerageid)
end)
else 0
end)
end as brokeragepercentage,
insurancesubtypes.shortdescription as insurancetypename
from clients INNER JOIN policies ON policies.clientid = clients.id
INNER JOIN policyItems ON policyItems.policyid = policies.id
INNER JOIN users ON users.id = policies.userID
INNER JOIN insuranceSubTypes ON policyItems.InsuranceSubTypeID = insuranceSubTypes.ID
INNER JOIN insuranceTypes ON insuranceSubTypes.InsuranceTypeID = insuranceTypes.id
where policies.applicationdate >='" + y1 + - +m1 + - +d1 + "' and policies.applicationdate<='" + y2 + - +m2 + - +d2 + "' and policies.Discard = 0";
if (insuranceTypeID > 0)
{
query += @" and insuranceTypes.ID = " + insuranceTypeID;
}
if (insuranceSubTypeID > 0)
{
query += @" and insuranceSubTypes.ID = " + insuranceSubTypeID;
}
if (userID > 0)
{
query += @" and users.ID = " + userID;
}
if (branchID > 0)
{
query += @" and policies.BranchID = " + branchID;
}
if (companyID > 0)
{
query += @" and policies.InsuranceCompanyID=" + companyID;
}
IEnumerable <Summary> summaryList = dcdc.ExecuteQuery <Summary>(query);
PDFCreators pdf = new PDFCreators(false, 15, 15, 15, 15);
pdf.OpenPDF();
pdf.SetTitle("Продадени полиси за даден период");
string[] headersP = { "РБ", "Број на полиса", "Осигуреник", "Почеток", "Истек", "Премија за наплата", "Брокеража(%)", "Брокеража", "Агент", "Тип" };
float[] policyColumnsWidths = { 6, 9, 22, 9, 9, 9, 9, 9, 9, 9 };
pdf.CreateTable(headersP.Length, false, headersP, "", policyColumnsWidths);
decimal PremValue = 0;
decimal BrokerageValue = 0;
object[] vals;
int i = 0;
foreach (Summary s in summaryList)
{
i++;
vals = new object[headersP.Length];
vals[0] = i.ToString();
vals[1] = s.policynumber;
vals[2] = s.name;
vals[3] = s.startdate.ToShortDateString();
vals[4] = s.enddate.ToShortDateString();
vals[5] = String.Format("{0:#,0.00}", s.premiumvalue);
vals[6] = s.brokeragepercentage;
decimal brokValue = (s.realpremiumvalue * s.brokeragepercentage / 100);
vals[7] = String.Format("{0:#,0.00}", brokValue);
vals[8] = s.username;
vals[9] = s.insurancetypename;
PremValue += s.premiumvalue;
BrokerageValue += brokValue;
pdf.AddDataRow1(vals, headersP.Length);
}
vals = new object[headersP.Length];
vals[0] = ""; //i.ToString();
vals[1] = "";
vals[2] = "";
vals[3] = "";
vals[4] = "Вкупно";
vals[5] = String.Format("{0:#,0.00}", PremValue);
vals[6] = "";
vals[7] = String.Format("{0:#,0.00}", BrokerageValue);
vals[8] = "";
vals[9] = "";
pdf.AddDataRow1(vals, headersP.Length);
pdf.AddTable();
pdf.FinishPDF();
}
public static void 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 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");
}
