public TransactionCollection Load()
{
Query qry = new Query("CSK_Store_Transaction");
foreach (Where where in wheres)
{
qry.AddWhere(where);
}
foreach (BetweenAnd between in betweens)
{
qry.AddBetweenAnd(between);
}
if (orderBy != null)
{
qry.OrderBy = orderBy;
}
IDataReader rdr = qry.ExecuteReader();
this.Load(rdr);
rdr.Close();
return(this);
}
public CategoryCollection Load()
{
Query qry = new Query(Category.Schema);
CheckLogicalDelete(qry);
foreach (Where where in wheres)
{
qry.AddWhere(where);
}
foreach (BetweenAnd between in betweens)
{
qry.AddBetweenAnd(between);
}
if (orderBy != null)
{
qry.OrderBy = orderBy;
}
IDataReader rdr = qry.ExecuteReader();
this.Load(rdr);
rdr.Close();
return(this);
}
private void InitGridSource()
{
m_GridTable.Rows.Clear();
int intStatusID = Convert.ToInt32(this.cbxOperationType.SelectedValue);
string strStatus = this.cbxOperationType.Text;
Query query = new Query(DDOperation.Schema);
query.AddBetweenAnd("OperationDate", Convert.ToDateTime(this.tbxStartDate.Text), Convert.ToDateTime(this.tbxEndDate.Text));
if (strStatus != "全部")
{
query.AddWhere("OperationType", intStatusID);
}
DataTable dt = query.ExecuteDataSet().Tables[0];
if (dt.Rows.Count > 0)
{
double serviceFee = 0, interestFee = 0, overdueFee = 0, returnFee = 0;
double amount = 0, moneyRL = 0;
for (int i = 0; i < dt.Rows.Count; i++)
{
DataRow drow = m_GridTable.NewRow();
DDPawnTicket newTicket = new DDPawnTicket("TicketID", dt.Rows[i]["TicketID"]);
drow["TicketNumber"] = newTicket.TicketNumber;
drow["Amount"] = dt.Rows[i]["Amount"];
drow["OperationType"] = dt.Rows[i]["OperationType"];
drow["OperationDate"] = dt.Rows[i]["OperationDate"];
drow["OperaterName"] = dt.Rows[i]["OperaterName"];
m_GridTable.Rows.Add(drow);
amount = Convert.ToDouble(dt.Rows[i]["Amount"]);
serviceFee = Convert.ToDouble(dt.Rows[i]["ServiceFee"]);
interestFee = Convert.ToDouble(dt.Rows[i]["InterestFee"]);
overdueFee = Convert.ToDouble(dt.Rows[i]["OverdueFee"]);
returnFee = Convert.ToDouble(dt.Rows[i]["ReturnFee"]);
switch (Convert.ToInt32(dt.Rows[i]["OperationType"]))
{
case 1:
moneyRL = moneyRL - amount + serviceFee;
break;
case 2:
moneyRL = moneyRL + amount + overdueFee + interestFee - returnFee;
break;
case 3:
moneyRL = moneyRL + serviceFee;
break;
default:
break;
}
}
this.lblMoneyRL.Text = moneyRL.ToString();
}
}
private void InitGridSource()
{
m_GridTable.Rows.Clear();
SearchStatus.Clear();
Query query = new Query(DDOperation.Schema);
string strStatus = this.cbxSearchRange.Text;
int intStatusID = Convert.ToInt32(this.cbxSearchRange.SelectedValue);
query.AddBetweenAnd("OperationDate", Convert.ToDateTime(this.tbxStartDate.Text), Convert.ToDateTime(this.tbxEndDate.Text));
if (strStatus == "全部")
{
query.AddWhere("OperationType", Comparison.In, new int[] { 1, 2, 3, 4, 5, 6, 7 }); //6--已删除
}
else
{
query.AddWhere("OperationType", intStatusID);
}
DataTable dt = query.ExecuteDataSet().Tables[0];
if (dt.Rows.Count > 0)
{
for (int i = 0; i < dt.Rows.Count; i++)
{
string Item = dt.Rows[i]["OperationNumber"].ToString();
if (!isInSearchStatusList(Item))
{
SearchStatus.Add(Item);
DataRow drow = m_GridTable.NewRow();
drow["TicketID"] = dt.Rows[i]["TicketID"];
Query queryTicket = new Query(DDPawnTicket.Schema);
queryTicket.AddWhere("TicketID", dt.Rows[i]["TicketID"]);
DataTable dtTicket = queryTicket.ExecuteDataSet().Tables[0];
if (dtTicket.Rows.Count > 0)
{
drow["TicketNumber"] = dtTicket.Rows[0]["TicketNumber"];
}
drow["OperationDate"] = dt.Rows[i]["OperationDate"];
drow["StatusID"] = dt.Rows[i]["OperationType"];
DDCustomerInfo customerInfo = new DDCustomerInfo("CustomerID", dtTicket.Rows[0]["CustomerID"].ToString());
drow["CustomerName"] = customerInfo.CustomerName;
drow["OperaterName"] = dt.Rows[i]["OperaterName"];
drow["OperationNumber"] = dt.Rows[i]["OperationNumber"];
m_GridTable.Rows.Add(drow);
}
}
}
}
private void InitGridSource()
{
this.gridDataset.Clear();
Query query = new Query(DDCustomerInfo.Schema);
#region Date
if (tbxStartDate.Text != "" && tbxEndDate.Text != "")
{
try
{
DateTime iStartDate = DateTime.Parse(this.tbxStartDate.Text);
DateTime iEndDate = DateTime.Parse(this.tbxEndDate.Text);
query.AddBetweenAnd("CreatDate", iStartDate, iEndDate);
}
catch
{
}
}
#endregion
string strOption = this.cbxSearchOption.SelectedValue.ToString();
string strKey = "%" + this.tbxKeyWord.Text + "%";
query.AddWhere(strOption, Comparison.Like, strKey);
DataTable dtCustomer = query.ExecuteDataSet().Tables[0];
for (int i = 0; i < dtCustomer.Rows.Count; i++)
{
DataRow dRow = gridDataset.NewRow();
dRow["DataSelect"] = false;
dRow["CustomerID"] = dtCustomer.Rows[i]["CustomerID"];
dRow["CustomerName"] = dtCustomer.Rows[i]["CustomerName"];
dRow["PhoneNumber"] = dtCustomer.Rows[i]["PhoneNumber"];
dRow["CertTypeID"] = dtCustomer.Rows[i]["CertTypeID"];
dRow["CertNumber"] = dtCustomer.Rows[i]["CertNumber"];
dRow["CreatDate"] = dtCustomer.Rows[i]["CreatDate"];
dRow["Address"] = dtCustomer.Rows[i]["Address"];
dRow["ContactPerson"] = dtCustomer.Rows[i]["ContactPerson"];
gridDataset.Rows.Add(dRow);
}
dataGridView1.DataSource = gridDataset;
this.lblSummery.Text = "总计客户" + gridDataset.Rows.Count + "人";
}
internal static Query AddTime(DateTime beginDate, DateTime endDate, string Field, Query query)
{
if (beginDate != DateTime.MinValue && endDate == DateTime.MinValue)
{
//do nothing
}
if (beginDate == DateTime.MinValue && endDate == DateTime.MinValue)
{
//do nothing
}
if (beginDate == DateTime.MinValue && endDate != DateTime.MinValue)
{
query.AddWhere(Field, Comparison.LessThan, endDate);
}
if (beginDate != DateTime.MinValue && endDate != DateTime.MinValue)
{
query.AddBetweenAnd(Field, beginDate, endDate);
}
return query;
}
internal static Query AddTime(DateTime beginDate, DateTime endDate, string Field, Query query)
{
if (beginDate != DateTime.MinValue && endDate == DateTime.MinValue)
{
//do nothing
}
if (beginDate == DateTime.MinValue && endDate == DateTime.MinValue)
{
//do nothing
}
if (beginDate == DateTime.MinValue && endDate != DateTime.MinValue)
{
query.AddWhere(Field, Comparison.LessThan, endDate);
}
if (beginDate != DateTime.MinValue && endDate != DateTime.MinValue)
{
query.AddBetweenAnd(Field, beginDate, endDate);
}
return(query);
}
/// <summary>
/// Loads the collection using any specified conditional operators.
/// </summary>
/// <returns></returns>
public ListType Load()
{
Query qry = new Query(new ItemType().GetSchema());
CheckLogicalDelete(qry);
foreach (Where where in wheres)
{
qry.AddWhere(where);
}
foreach (BetweenAnd between in betweens)
{
qry.AddBetweenAnd(between);
}
qry.OrderByCollection = orderByCollection;
using (IDataReader rdr = qry.ExecuteReader())
{
LoadAndCloseReader(rdr);
return(this as ListType);
}
}
private void InitGridTable()
{
//string CustomerName = "";
//string TicketNumber = "";
string TicketStatus = "";
DateTime startTime = Convert.ToDateTime("1900-1-1");
DateTime endTime = DateTime.Now.AddDays(30);
string strEndTime = endTime.Year.ToString() + "-" + endTime.Month.ToString() + "-" + endTime.Day.ToString();
endTime = Convert.ToDateTime(strEndTime);
Query query = new Query(DDHouseInfo.Schema);
query.AddBetweenAnd("NotarizationDate", startTime, endTime);
DataTable dt = query.ExecuteDataSet().Tables[0];
if (dt.Rows.Count > 0)
{
for (int i = 0; i < dt.Rows.Count; i++)
{
Query queryPawnage = new Query(DDPawnageInfo.Schema);
queryPawnage.AddWhere("PawnageID", dt.Rows[i]["PawnageID"].ToString());
DataTable dtPawnage = queryPawnage.ExecuteDataSet().Tables[0];
if (dtPawnage.Rows.Count > 0)
{
TicketStatus = dtPawnage.Rows[0]["StatusID"].ToString();
if (TicketStatus == "1" || TicketStatus == "3" || TicketStatus == "4")
{
this.dataGridView1.Rows.Add(1);
//GetName
Query queryOperation = new Query(DDOperation.Schema);
queryOperation.AddWhere("PawnageID", dt.Rows[i]["PawnageID"]);
DataTable dtOperation = queryOperation.ExecuteDataSet().Tables[0];
if (dtOperation.Rows.Count > 0)
{
Query queryTicket = new Query(DDPawnTicket.Schema);
queryTicket.AddWhere("TicketID", dtOperation.Rows[0]["TicketID"].ToString());
DataTable dtTicket = queryTicket.ExecuteDataSet().Tables[0];
if (dtTicket.Rows.Count > 0)
{
dataGridView1.Rows[dataGridView1.Rows.Count - 1].Cells["TicketNumber"].Value = dtTicket.Rows[0]["TicketNumber"];
Query queryCustomer = new Query(DDCustomerInfo.Schema);
queryCustomer.AddWhere("CustomerID", dtTicket.Rows[0]["CustomerID"].ToString());
DataTable dtCustomer = queryCustomer.ExecuteDataSet().Tables[0];
if (dtCustomer.Rows.Count > 0)
{
dataGridView1.Rows[dataGridView1.Rows.Count - 1].Cells["CustomerName"].Value = dtCustomer.Rows[0]["CustomerName"];
}
}
}
dataGridView1.Rows[dataGridView1.Rows.Count - 1].Cells["Address"].Value = dt.Rows[i]["Address"];
dataGridView1.Rows[dataGridView1.Rows.Count - 1].Cells["CompactNumber"].Value = dt.Rows[i]["CompactNumber"];
dataGridView1.Rows[dataGridView1.Rows.Count - 1].Cells["Area"].Value = dt.Rows[i]["Area"];
dataGridView1.Rows[dataGridView1.Rows.Count - 1].Cells["RegisterDate"].Value = dt.Rows[i]["RegisterDate"];
dataGridView1.Rows[dataGridView1.Rows.Count - 1].Cells["InsuranceDate"].Value = dt.Rows[i]["InsuranceDate"];
dataGridView1.Rows[dataGridView1.Rows.Count - 1].Cells["NotarizationDate"].Value = dt.Rows[i]["NotarizationDate"];
}
}
}
}
else
{
MessageBox.Show("暂无任何公正到期房产!", "提示信息");
}
}
private void LoadTicketInfo()
{
this.m_GridTable.Rows.Clear();
DDPawnTicket newTicket = new DDPawnTicket("TicketID", m_TicketID);
Query queryOperations = new Query(DDOperation.Schema);
queryOperations.AddWhere("TicketID", m_TicketID);
queryOperations.AddWhere("OperationType", Comparison.In, new int[] { 1, 3 });
queryOperations.AddWhere("OperationType", Comparison.NotEquals, 6);
queryOperations.AddWhere("NextOperationID", 0);
DataTable dtOperations = queryOperations.ExecuteDataSet().Tables[0];
m_StartDate = Convert.ToDateTime(newTicket.StartDate);
m_EndDate = Convert.ToDateTime(newTicket.EndDate);
try
{
m_OperationDate = Convert.ToDateTime(this.tbxOperationDate.Text.Trim());
}
catch
{
MessageBox.Show("请输入正确的日期", "提示信息");
}
if (dtOperations.Rows.Count > 0)
{
for (int i = 0; i < dtOperations.Rows.Count; i++)
{
double amount = 0;
double serviceFee = 0, interestFee = 0, paidInterestFee = 0;
double chargedServiceFee = 0, chargedInterestFee = 0;
double singleChargedServiceFee = 0, singleChargedInterestFee = 0;
double returnServiceFee = 0, returnInterestFee = 0;
double singleReturnServiceFee = 0, singleReturnInterestFee = 0;
double serviceFeeRate = 0, interestFeeRate = 0;
double overdueFee = 0;
amount = Convert.ToDouble(dtOperations.Rows[i]["Amount"]);
#region 原先错误的算法
/*
* Query queryRenewPawn = new Query(DDOperation.Schema);
* queryRenewPawn.AddWhere("TicketID",m_TicketID);
* queryRenewPawn.AddWhere("PawnageID",Convert.ToInt32(dtOperations.Rows[i]["PawnageID"]));
* queryRenewPawn.AddWhere("OperationType",3);
* DataTable dtRenewPawn = queryRenewPawn.ExecuteDataSet().Tables[0];
* if (dtRenewPawn.Rows.Count > 0)
* {
* for (int j = 0; j < dtRenewPawn.Rows.Count; j++)
* {
* serviceFee += Convert.ToDouble(dtRenewPawn.Rows[j]["ServiceFee"]);
* interestFee += Convert.ToDouble(dtRenewPawn.Rows[j]["InterestFee"]);
* }
* }
*
* totalServiceFee += serviceFee;
* totalInterestFee += interestFee;
* //overdueFee = CaculateOverdueFee(amount, Convert.ToDouble(pawnageInfo.FeeRate), Convert.ToDouble(pawnageInfo.InterestRate))
* //returnFee = serviceFee + interestFee - CaculatePaidFee(amount, Convert.ToDouble(pawnageInfo.FeeRate), Convert.ToDouble(pawnageInfo.InterestRate));
* //returnFee = CaculateReturnFee(amount, Convert.ToDouble(pawnageInfo.FeeRate), Convert.ToDouble(pawnageInfo.InterestRate));
* if (returnFee < 0)
* {
* returnFee = 0;
* }
* totalOverdueFee += overdueFee;
* totalReturnFee += returnFee;
*/
#endregion
DDPawnageInfo pawnageInfo = new DDPawnageInfo("PawnageID", Convert.ToInt32(dtOperations.Rows[i]["PawnageID"]));
#region 费用计算
//判断当票状态
int PawnTicketStatus = 0; //0:正好，1:提前，2:过期
//DDPawnTicket theTicket = new DDPawnTicket("TicketID", m_TicketID);
if (m_OperationDate <= DateTime.Parse(dtOperations.Rows[i]["EndDate"].ToString()))
{
PawnSpan Span = DianDangFunction.GetPawnSpan(m_OperationDate, DateTime.Parse(dtOperations.Rows[i]["StartDate"].ToString()));
PawnSpan LastSpan = DianDangFunction.GetPawnSpan(DateTime.Parse(dtOperations.Rows[i]["StartDate"].ToString()), DateTime.Parse(dtOperations.Rows[i]["EndDate"].ToString()));
if (Span.Months == LastSpan.Months && Span.CountDays == LastSpan.CountDays && m_OperationDate >= DateTime.Parse(dtOperations.Rows[i]["StartDate"].ToString()))
{
PawnTicketStatus = 0;
}
else
{
PawnTicketStatus = 1;
}
}
else
{
PawnTicketStatus = 2;
}
if (DateTime.Parse(newTicket.StartDate) == m_OperationDate)//当天建当，当天还
{
int ifReturn = 0;
double InOperationServiceFee = 0, InOperationInterestFee = 0;
double InOPerationChargedServiceFee = 0, InOperationChargedInterestFee = 0;
//总计征收的费用
Query queryPaidOperation = new Query(DDOperation.Schema);
queryPaidOperation.AddWhere("PawnageID", pawnageInfo.PawnageID);
queryPaidOperation.AddWhere("OperationType", Comparison.NotEquals, 6);
queryPaidOperation.AddWhere("Deleted", 0);
DataTable dtPaidOperation = queryPaidOperation.ExecuteDataSet().Tables[0];
for (int j = 0; j < dtPaidOperation.Rows.Count; j++)
{
singleChargedServiceFee = Convert.ToDouble(dtPaidOperation.Rows[j]["ServiceFee"].ToString());
singleChargedServiceFee = DianDangFunction.myRound(singleChargedServiceFee, MainForm.AmountAccuracy);
chargedServiceFee += singleChargedServiceFee;
singleChargedInterestFee = Convert.ToDouble(dtPaidOperation.Rows[j]["InterestFee"].ToString());
singleChargedInterestFee = DianDangFunction.myRound(singleChargedInterestFee, MainForm.AmountAccuracy);
chargedInterestFee += singleChargedInterestFee;
}
//计算争议的服务费
Query queryInOperation = new Query(DDOperation.Schema);
queryInOperation.AddBetweenAnd("EndDate", m_OperationDate, new DateTime(9998, 12, 31));
queryInOperation.AddBetweenAnd("StartDate", new DateTime(1755, 1, 1), m_OperationDate);
queryInOperation.AddWhere("PawnageID", pawnageInfo.PawnageID);
queryInOperation.AddWhere("OperationType", Comparison.NotEquals, 6);
queryInOperation.AddWhere("Deleted", 0);
DataTable dtInOperation = queryInOperation.ExecuteDataSet().Tables[0];
if (dtInOperation.Rows.Count > 0)
{
InOPerationChargedServiceFee = Convert.ToDouble(dtInOperation.Rows[0]["ServiceFee"].ToString());
PawnSpan OperationSpan = DianDangFunction.GetPawnSpan(DateTime.Parse(dtInOperation.Rows[0]["StartDate"].ToString()), DateTime.Parse(dtInOperation.Rows[0]["EndDate"].ToString()));
PawnSpan Span = DianDangFunction.GetPawnSpan(DateTime.Parse(dtInOperation.Rows[0]["StartDate"].ToString()), m_OperationDate);
double DayFeeRate = InOPerationChargedServiceFee / OperationSpan.CountDaysAll / amount * 100;
if (Span.CountDaysAll > OperationSpan.CountDaysAll)
{
Span.CountDaysAll = OperationSpan.CountDaysAll;
}
InOperationServiceFee = (Span.CountDaysAll) * amount * DayFeeRate / 100;
InOperationServiceFee = DianDangFunction.myRound(InOperationServiceFee, MainForm.AmountAccuracy);
}
//计算肯定要返还的费用
Query queryReturnOperation = new Query(DDOperation.Schema);
queryReturnOperation.AddBetweenAnd("StartDate", m_OperationDate.AddDays(1), new DateTime(9998, 12, 31));
queryReturnOperation.AddWhere("PawnageID", pawnageInfo.PawnageID);
queryReturnOperation.AddWhere("OperationType", Comparison.NotEquals, 6);
queryReturnOperation.AddWhere("Deleted", 0);
DataTable dtReturnOperation = queryReturnOperation.ExecuteDataSet().Tables[0];
for (int j = 0; j < dtReturnOperation.Rows.Count; j++)
{
//计算争议的利息费，因为本次收取的利息为上月利息，即为争议利息
if (j == 0 && dtInOperation.Rows.Count > 0)
{
InOperationChargedInterestFee = Convert.ToDouble(dtReturnOperation.Rows[0]["InterestFee"].ToString());
PawnSpan OperationSpan = DianDangFunction.GetPawnSpan(DateTime.Parse(dtInOperation.Rows[0]["StartDate"].ToString()), DateTime.Parse(dtInOperation.Rows[0]["EndDate"].ToString()));
PawnSpan Span = DianDangFunction.GetPawnSpan(DateTime.Parse(dtInOperation.Rows[0]["StartDate"].ToString()), m_OperationDate);
double DayInterestFeeRate = InOperationChargedInterestFee / OperationSpan.CountDaysAll / amount * 100;
if (Span.CountDaysAll > OperationSpan.CountDaysAll)
{
Span.CountDaysAll = OperationSpan.CountDaysAll;
}
InOperationInterestFee = (Span.CountDaysAll) * amount * DayInterestFeeRate / 100;
InOperationInterestFee = DianDangFunction.myRound(InOperationInterestFee, MainForm.AmountAccuracy);
}
ifReturn++;
singleReturnServiceFee = Convert.ToDouble(dtReturnOperation.Rows[j]["ServiceFee"].ToString());
singleReturnServiceFee = DianDangFunction.myRound(singleReturnServiceFee, MainForm.AmountAccuracy);
returnServiceFee += singleReturnServiceFee;
if (j != 0 || dtInOperation.Rows.Count == 0)
{
singleReturnInterestFee = Convert.ToDouble(dtReturnOperation.Rows[j]["InterestFee"].ToString());
singleReturnInterestFee = DianDangFunction.myRound(singleReturnInterestFee, MainForm.AmountAccuracy);
returnInterestFee += singleReturnInterestFee;
}
}
if (m_OperationDate > DateTime.Parse(dtOperations.Rows[i]["StartDate"].ToString())) //操作日期大于最后一次操作开始日期，则收取利息
{
PawnSpan Span = DianDangFunction.GetPawnSpan(DateTime.Parse(dtOperations.Rows[i]["StartDate"].ToString()), m_OperationDate);
paidInterestFee = 0;
paidInterestFee = (Span.Months + Span.Days / 30) * Convert.ToDouble(dtOperations.Rows[i]["InterestFeeRate"].ToString()) * amount / 100;
paidInterestFee = DianDangFunction.myRound(paidInterestFee, MainForm.AmountAccuracy);
}
returnServiceFee = returnServiceFee + InOPerationChargedServiceFee - InOperationServiceFee;
returnServiceFee = DianDangFunction.myRound(returnServiceFee, MainForm.AmountAccuracy);
returnInterestFee = returnInterestFee + InOperationChargedInterestFee - InOperationInterestFee;
returnInterestFee = DianDangFunction.myRound(returnInterestFee, MainForm.AmountAccuracy);
serviceFee = chargedServiceFee - returnServiceFee;
serviceFee = DianDangFunction.myRound(serviceFee, MainForm.AmountAccuracy);
interestFee = chargedInterestFee + paidInterestFee - returnInterestFee;
interestFee = DianDangFunction.myRound(interestFee, MainForm.AmountAccuracy);
}
else
{
if (PawnTicketStatus == 0) //收取上期服务费
{
//计算上期利息
PawnSpan LastSpan = DianDangFunction.GetPawnSpan(DateTime.Parse(dtOperations.Rows[i]["StartDate"].ToString()), DateTime.Parse(dtOperations.Rows[i]["EndDate"].ToString()));
paidInterestFee = (LastSpan.Months + LastSpan.CountDays / 30) * amount * Convert.ToDouble(dtOperations.Rows[i]["InterestFeeRate"].ToString()) / 100;
paidInterestFee = DianDangFunction.myRound(paidInterestFee, MainForm.AmountAccuracy);
//计算已经付出的服务费和利息
Query queryPaidOperation = new Query(DDOperation.Schema);
queryPaidOperation.AddWhere("TicketID", m_TicketID);
queryPaidOperation.AddWhere("PawnageID", pawnageInfo.PawnageID);
queryPaidOperation.AddWhere("OperationType", Comparison.NotEquals, 6);
queryPaidOperation.AddWhere("Deleted", 0);
DataTable dtPaidOperation = queryPaidOperation.ExecuteDataSet().Tables[0];
if (dtPaidOperation.Rows.Count > 0)
{
for (int j = 0; j < dtPaidOperation.Rows.Count; j++)
{
singleChargedServiceFee = Convert.ToDouble(dtPaidOperation.Rows[j]["ServiceFee"].ToString());
singleChargedServiceFee = DianDangFunction.myRound(singleChargedServiceFee, MainForm.AmountAccuracy);
chargedServiceFee += singleChargedServiceFee;
singleChargedInterestFee = Convert.ToDouble(dtPaidOperation.Rows[j]["InterestFee"].ToString());
singleChargedInterestFee = DianDangFunction.myRound(singleChargedInterestFee, MainForm.AmountAccuracy);
chargedInterestFee += singleChargedInterestFee;
}
}
//当品应收的利息等于之前收过的利息+上期利息
interestFee = chargedInterestFee + paidInterestFee;
serviceFee = chargedServiceFee;
}
else if (PawnTicketStatus == 2) //过期状态，收取
{
//计算上期利息+过期利息，计算过期服务费
PawnSpan LastSpan = DianDangFunction.GetPawnSpan(DateTime.Parse(dtOperations.Rows[i]["StartDate"].ToString()), m_OperationDate);
paidInterestFee = (LastSpan.Months + LastSpan.CountDays / 30) * amount * Convert.ToDouble(dtOperations.Rows[i]["InterestFeeRate"].ToString()) / 100;
paidInterestFee = DianDangFunction.myRound(paidInterestFee, MainForm.AmountAccuracy);
PawnSpan OverdueSpan = DianDangFunction.GetPawnSpan(DateTime.Parse(dtOperations.Rows[i]["EndDate"].ToString()), m_OperationDate);
overdueFee = (OverdueSpan.Months + OverdueSpan.CountDays / 30) * amount * Convert.ToDouble(dtOperations.Rows[i]["ServiceFeeRate"].ToString()) / 100;
overdueFee = DianDangFunction.myRound(overdueFee, MainForm.AmountAccuracy);
//计算已经付出的服务费和利息
Query queryPaidOperation = new Query(DDOperation.Schema);
queryPaidOperation.AddWhere("TicketID", m_TicketID);
queryPaidOperation.AddWhere("PawnageID", pawnageInfo.PawnageID);
queryPaidOperation.AddWhere("OperationType", Comparison.NotEquals, 6);
queryPaidOperation.AddWhere("Deleted", 0);
DataTable dtPaidOperation = queryPaidOperation.ExecuteDataSet().Tables[0];
if (dtPaidOperation.Rows.Count > 0)
{
for (int j = 0; j < dtPaidOperation.Rows.Count; j++)
{
singleChargedServiceFee = Convert.ToDouble(dtPaidOperation.Rows[j]["ServiceFee"].ToString());
singleChargedServiceFee = DianDangFunction.myRound(singleChargedServiceFee, MainForm.AmountAccuracy);
chargedServiceFee += singleChargedServiceFee;
singleChargedInterestFee = Convert.ToDouble(dtPaidOperation.Rows[j]["InterestFee"].ToString());
singleChargedInterestFee = DianDangFunction.myRound(singleChargedInterestFee, MainForm.AmountAccuracy);
chargedInterestFee += singleChargedInterestFee;
}
}
//当品应收的利息等于之前收过的利息+应付利息
interestFee = chargedInterestFee + paidInterestFee;
//服务费应该等于所有已经收取的服务费+过期服务费
serviceFee = chargedServiceFee + overdueFee;
}
else if (PawnTicketStatus == 1) //提前赎当
{
int ifReturn = 0;
double InOperationServiceFee = 0, InOperationInterestFee = 0;
double InOPerationChargedServiceFee = 0, InOperationChargedInterestFee = 0;
//总计征收的费用
Query queryPaidOperation = new Query(DDOperation.Schema);
queryPaidOperation.AddWhere("PawnageID", pawnageInfo.PawnageID);
queryPaidOperation.AddWhere("OperationType", Comparison.NotEquals, 6);
queryPaidOperation.AddWhere("Deleted", 0);
DataTable dtPaidOperation = queryPaidOperation.ExecuteDataSet().Tables[0];
for (int j = 0; j < dtPaidOperation.Rows.Count; j++)
{
singleChargedServiceFee = Convert.ToDouble(dtPaidOperation.Rows[j]["ServiceFee"].ToString());
singleChargedServiceFee = DianDangFunction.myRound(singleChargedServiceFee, MainForm.AmountAccuracy);
chargedServiceFee += singleChargedServiceFee;
singleChargedInterestFee = Convert.ToDouble(dtPaidOperation.Rows[j]["InterestFee"].ToString());
singleChargedInterestFee = DianDangFunction.myRound(singleChargedInterestFee, MainForm.AmountAccuracy);
chargedInterestFee += singleChargedInterestFee;
}
//计算争议的服务费
Query queryInOperation = new Query(DDOperation.Schema);
queryInOperation.AddBetweenAnd("EndDate", m_OperationDate.AddDays(1), new DateTime(9998, 12, 31));
queryInOperation.AddBetweenAnd("StartDate", new DateTime(1755, 1, 1), m_OperationDate.AddDays(-1));
queryInOperation.AddWhere("PawnageID", pawnageInfo.PawnageID);
queryInOperation.AddWhere("OperationType", Comparison.NotEquals, 6);
queryInOperation.AddWhere("Deleted", 0);
DataTable dtInOperation = queryInOperation.ExecuteDataSet().Tables[0];
if (dtInOperation.Rows.Count > 0)
{
InOPerationChargedServiceFee = Convert.ToDouble(dtInOperation.Rows[0]["ServiceFee"].ToString());
PawnSpan OperationSpan = DianDangFunction.GetPawnSpan(DateTime.Parse(dtInOperation.Rows[0]["StartDate"].ToString()), DateTime.Parse(dtInOperation.Rows[0]["EndDate"].ToString()));
PawnSpan Span = DianDangFunction.GetPawnSpan(DateTime.Parse(dtInOperation.Rows[0]["StartDate"].ToString()), m_OperationDate);
double DayFeeRate = InOPerationChargedServiceFee / OperationSpan.CountDaysAll / amount * 100;
if (Span.CountDaysAll > OperationSpan.CountDaysAll)
{
Span.CountDaysAll = OperationSpan.CountDaysAll;
}
InOperationServiceFee = (Span.CountDaysAll) * amount * DayFeeRate / 100;
InOperationServiceFee = DianDangFunction.myRound(InOperationServiceFee, MainForm.AmountAccuracy);
}
//计算肯定要返还的费用
Query queryReturnOperation = new Query(DDOperation.Schema);
queryReturnOperation.AddBetweenAnd("StartDate", m_OperationDate, new DateTime(9998, 12, 31));
queryReturnOperation.AddWhere("PawnageID", pawnageInfo.PawnageID);
queryReturnOperation.AddWhere("OperationType", Comparison.NotEquals, 6);
queryReturnOperation.AddWhere("Deleted", 0);
DataTable dtReturnOperation = queryReturnOperation.ExecuteDataSet().Tables[0];
for (int j = 0; j < dtReturnOperation.Rows.Count; j++)
{
//计算争议的利息费，因为本次收取的利息为上月利息，即为争议利息
if (j == 0 && dtInOperation.Rows.Count > 0)
{
InOperationChargedInterestFee = Convert.ToDouble(dtReturnOperation.Rows[0]["InterestFee"].ToString());
PawnSpan OperationSpan = DianDangFunction.GetPawnSpan(DateTime.Parse(dtInOperation.Rows[0]["StartDate"].ToString()), DateTime.Parse(dtInOperation.Rows[0]["EndDate"].ToString()));
PawnSpan Span = DianDangFunction.GetPawnSpan(DateTime.Parse(dtInOperation.Rows[0]["StartDate"].ToString()), m_OperationDate);
double DayInterestFeeRate = InOperationChargedInterestFee / OperationSpan.CountDaysAll / amount * 100;
if (Span.CountDaysAll > OperationSpan.CountDaysAll)
{
Span.CountDaysAll = OperationSpan.CountDaysAll;
}
InOperationInterestFee = (Span.CountDaysAll) * amount * DayInterestFeeRate / 100;
InOperationInterestFee = DianDangFunction.myRound(InOperationInterestFee, MainForm.AmountAccuracy);
}
ifReturn++;
singleReturnServiceFee = Convert.ToDouble(dtReturnOperation.Rows[j]["ServiceFee"].ToString());
singleReturnServiceFee = DianDangFunction.myRound(singleReturnServiceFee, MainForm.AmountAccuracy);
returnServiceFee += singleReturnServiceFee;
if (j != 0 || dtInOperation.Rows.Count == 0)
{
singleReturnInterestFee = Convert.ToDouble(dtReturnOperation.Rows[j]["InterestFee"].ToString());
singleReturnInterestFee = DianDangFunction.myRound(singleReturnInterestFee, MainForm.AmountAccuracy);
returnInterestFee += singleReturnInterestFee;
}
}
if (m_OperationDate > DateTime.Parse(dtOperations.Rows[i]["StartDate"].ToString())) //操作日期大于最后一次操作开始日期，则收取利息
{
PawnSpan Span = DianDangFunction.GetPawnSpan(DateTime.Parse(dtOperations.Rows[i]["StartDate"].ToString()), m_OperationDate);
paidInterestFee = 0;
paidInterestFee = (Span.Months + Span.Days / 30) * Convert.ToDouble(dtOperations.Rows[i]["InterestFeeRate"].ToString()) * amount / 100;
paidInterestFee = DianDangFunction.myRound(paidInterestFee, MainForm.AmountAccuracy);
}
returnServiceFee = returnServiceFee + InOPerationChargedServiceFee - InOperationServiceFee;
returnServiceFee = DianDangFunction.myRound(returnServiceFee, MainForm.AmountAccuracy);
returnInterestFee = returnInterestFee + InOperationChargedInterestFee - InOperationInterestFee;
returnInterestFee = DianDangFunction.myRound(returnInterestFee, MainForm.AmountAccuracy);
serviceFee = chargedServiceFee - returnServiceFee;
serviceFee = DianDangFunction.myRound(serviceFee, MainForm.AmountAccuracy);
interestFee = chargedInterestFee + paidInterestFee - returnInterestFee;
interestFee = DianDangFunction.myRound(interestFee, MainForm.AmountAccuracy);
}
}
//Query queryOperation = new Query(DDOperation.Schema);
//queryOperations.AddWhere("TicketID", m_TicketID);
#endregion
serviceFeeRate = Convert.ToDouble(dtOperations.Rows[i]["ServiceFeeRate"].ToString());
interestFeeRate = Convert.ToDouble(dtOperations.Rows[i]["InterestFeeRate"].ToString());
interestFee = DianDangFunction.myRound(interestFee, MainForm.AmountAccuracy);
serviceFee = DianDangFunction.myRound(serviceFee, MainForm.AmountAccuracy);
amount = DianDangFunction.myRound(amount, MainForm.AmountAccuracy);
returnServiceFee = DianDangFunction.myRound(returnServiceFee, MainForm.AmountAccuracy);
returnInterestFee = DianDangFunction.myRound(returnInterestFee, MainForm.AmountAccuracy);
overdueFee = DianDangFunction.myRound(overdueFee, MainForm.AmountAccuracy);
paidInterestFee = DianDangFunction.myRound(paidInterestFee, MainForm.AmountAccuracy);
chargedServiceFee = DianDangFunction.myRound(chargedServiceFee, MainForm.AmountAccuracy);
chargedInterestFee = DianDangFunction.myRound(chargedInterestFee, MainForm.AmountAccuracy);
DataRow drow = m_GridTable.NewRow();
DDPawnageClass pawnageClass = new DDPawnageClass("ClassID", pawnageInfo.ClassID);
drow["ClassName"] = pawnageClass.ClassName;
drow["Amount"] = amount;
drow["ServiceFee"] = serviceFee;
drow["InterestFee"] = interestFee;
drow["ReturnFee"] = returnServiceFee;
drow["ReturnInterestFee"] = returnInterestFee;
drow["OverdueFee"] = overdueFee;
drow["PaidInterestFee"] = paidInterestFee;
drow["ChargedServiceFee"] = chargedServiceFee;
drow["ChargedInterestFee"] = chargedInterestFee;
drow["PawnageID"] = dtOperations.Rows[i]["PawnageID"].ToString();
drow["ServiceFeeRate"] = serviceFeeRate;
drow["InterestFeeRate"] = interestFeeRate;
m_GridTable.Rows.Add(drow);
}
}
this.dataGridView1.DataSource = m_GridTable;
CaculateDatagridAmount();
}
private void btnSearch_Click(object sender, EventArgs e)
{
m_GridTable.Rows.Clear();
Query queryPawnage = new Query(DDPawnageInfo.Schema);
bool RedyToQuery = true;
double Amount = 0;
string KeyWord = "%" + tbxKeyWord.Text + "%";
#region 加上SearchRange查询限制
switch (cbxSearchRange.Text)
{
case "在库全部":
queryPawnage.AddWhere("StatusID", Comparison.In, new int[] { 1, 3, 4, 5 });
break;
case "在库正常":
queryPawnage.AddWhere("StatusID", Comparison.In, new int[] { 1, 3 });
queryPawnage.AddBetweenAnd("EndDate", DateTime.Now, new DateTime(9999, 12, 23));
break;
case "在库过期":
queryPawnage.AddWhere("StatusID", Comparison.In, new int[] { 1, 3 });
queryPawnage.AddBetweenAnd("EndDate", new DateTime(1900, 1, 1), DateTime.Now);
break;
case "冻结当品":
queryPawnage.AddWhere("StatusID", 5);
break;
case "已绝当":
queryPawnage.AddWhere("StatusID", 4);
break;
case "已赎回":
queryPawnage.AddWhere("StatusID", 2);
break;
case "已清算":
queryPawnage.AddWhere("StatusID", 7);
break;
default:
break;
}
#endregion
#region 加上SearchOption查询限制
Hashtable PawnageIDList = new Hashtable();
if (cbxSearchOption.Text == "当票号")
{
Query queryTicket = new Query(DDPawnTicket.Schema);
queryTicket.AddWhere("TicketNumber", Comparison.Like, KeyWord);
DataTable dtTicket = queryTicket.ExecuteDataSet().Tables[0];
int[] TicketIDList = new int[dtTicket.Rows.Count];
for (int i = 0; i < dtTicket.Rows.Count; i++)
{
TicketIDList[i] = Convert.ToInt32(dtTicket.Rows[i]["TicketID"].ToString());
}
Query queryOperation = new Query(DDOperation.Schema);
queryOperation.AddWhere("TicketID", Comparison.In, TicketIDList);
queryOperation.AddWhere("NextOperationID", 0);
queryOperation.AddWhere("OperationType", Comparison.NotEquals, 6);
DataTable dtOperation = queryOperation.ExecuteDataSet().Tables[0];
for (int i = 0; i < dtOperation.Rows.Count; i++)
{
PawnageIDList.Add(dtOperation.Rows[i]["PawnageID"], 0);
}
}
else if (cbxSearchOption.Text == "客户姓名")
{
Query queryCustomer = new Query(DDCustomerInfo.Schema);
queryCustomer.AddWhere("CustomerName", Comparison.Like, KeyWord);
DataTable dtCustomer = queryCustomer.ExecuteDataSet().Tables[0];
int[] CustomerIDList = new int[dtCustomer.Rows.Count];
for (int i = 0; i < dtCustomer.Rows.Count; i++)
{
CustomerIDList[i] = Convert.ToInt32(dtCustomer.Rows[i]["CustomerID"].ToString());
}
Query queryTicket = new Query(DDPawnTicket.Schema);
queryTicket.AddWhere("CustomerID", Comparison.In, CustomerIDList);
DataTable dtTicket = queryTicket.ExecuteDataSet().Tables[0];
int[] TicketIDList = new int[dtTicket.Rows.Count];
for (int i = 0; i < dtTicket.Rows.Count; i++)
{
TicketIDList[i] = Convert.ToInt32(dtTicket.Rows[i]["TicketID"].ToString());
}
Query queryOperation = new Query(DDOperation.Schema);
queryOperation.AddWhere("TicketID", Comparison.In, TicketIDList);
queryOperation.AddWhere("NextOperationID", 0);
queryOperation.AddWhere("OperationType", Comparison.NotEquals, 6);
DataTable dtOperation = queryOperation.ExecuteDataSet().Tables[0];
for (int i = 0; i < dtOperation.Rows.Count; i++)
{
PawnageIDList.Add(dtOperation.Rows[i]["PawnageID"], 0);
}
}
else if (cbxSearchOption.Text == "当值大于")
{
try
{
Amount = Convert.ToDouble(tbxKeyWord.Text);
queryPawnage.AddWhere("Price", Comparison.GreaterOrEquals, Amount);
}
catch
{
RedyToQuery = false;
MessageBox.Show("请填入正确的金额", "提示信息");
}
}
else if (cbxSearchOption.Text == "当值小于")
{
try
{
Amount = Convert.ToDouble(tbxKeyWord.Text);
queryPawnage.AddWhere("Price", Comparison.LessThan, Amount);
}
catch
{
RedyToQuery = false;
MessageBox.Show("请填入正确的金额", "提示信息");
}
}
else if (cbxSearchOption.Text == "一级分类")
{
queryPawnage.AddWhere("ParentID", cbxClassSelector.SelectedValue);
}
else if (cbxSearchOption.Text == "二级分类")
{
queryPawnage.AddWhere("ClassID", cbxClassSelector.SelectedValue);
}
else
{
queryPawnage.AddWhere(cbxSearchOption.SelectedValue.ToString(), Comparison.Like, KeyWord);
}
#endregion
#region 执行查询
if (RedyToQuery)
{
DataTable dtPawnage = queryPawnage.ExecuteDataSet().Tables[0];
for (int i = 0; i < dtPawnage.Rows.Count; i++)
{
if ((PawnageIDList[dtPawnage.Rows[i]["PawnageID"]] != null && (this.cbxSearchOption.Text == "当票号" || this.cbxSearchOption.Text == "客户姓名")) || (this.cbxSearchOption.Text != "当票号" && this.cbxSearchOption.Text != "客户姓名"))
{
DataRow dRow = m_GridTable.NewRow();
dRow["PawnageID"] = dtPawnage.Rows[i]["PawnageID"].ToString();
dRow["OperationFlag"] = false;
dRow["FirstClass"] = dtPawnage.Rows[i]["ParentID"].ToString();
dRow["SecondClass"] = dtPawnage.Rows[i]["ClassID"].ToString();
dRow["Description"] = dtPawnage.Rows[i]["Description"].ToString();
dRow["Weight"] = dtPawnage.Rows[i]["Weight"].ToString();
dRow["Price"] = dtPawnage.Rows[i]["Price"].ToString();
dRow["TotalCount"] = dtPawnage.Rows[i]["CountNumber"].ToString();
dRow["FeeRate"] = dtPawnage.Rows[i]["FeeRate"].ToString();
dRow["InterestRate"] = dtPawnage.Rows[i]["InterestRate"].ToString();
dRow["Remark"] = dtPawnage.Rows[i]["Remark"].ToString();
dRow["PhotoPath"] = dtPawnage.Rows[i]["PhotoPath"].ToString();
//获取当票号
Query queryOperation = new Query(DDOperation.Schema);
queryOperation.AddWhere("PawnageID", dtPawnage.Rows[i]["PawnageID"]);
queryOperation.AddWhere("NextOperationID", 0);
queryOperation.AddWhere("OperationType", Comparison.NotEquals, 6);
DataTable dtOperation = queryOperation.ExecuteDataSet().Tables[0];
if (dtOperation.Rows.Count > 0)
{
string TicketID = dtOperation.Rows[dtOperation.Rows.Count - 1]["TicketID"].ToString();
DDPawnTicket newTicket = new DDPawnTicket("TicketID", TicketID);
dRow["TicketNumber"] = newTicket.TicketNumber;
}
m_GridTable.Rows.Add(dRow);
}
}
dataGridView1.DataSource = m_GridTable;
if (dtPawnage.Rows.Count > 0)
{
btnPrint.Enabled = true;
}
}
#endregion
SetForGridViewPage();
}
private void InitGridSource()
{
m_GridTable.Rows.Clear();
Query query = new Query(DDPawnTicket.Schema);
string strStatus = this.cbxSearchRange.Text;
int intStatusID = Convert.ToInt32(this.cbxSearchRange.SelectedValue);
if (cbxSearchOption.SelectedValue.ToString() == "TicketNumber")
{
string strOption = this.cbxSearchOption.SelectedValue.ToString();
string strKey = "%" + this.tbxKeyWord.Text + "%";
query.AddWhere(strOption, Comparison.Like, strKey);
}
if (cbxSearchOption.SelectedValue.ToString() == "CustomerName" || cbxSearchOption.SelectedValue.ToString() == "CertNumber")
{
string strOption = this.cbxSearchOption.SelectedValue.ToString();
string strKey = "%" + this.tbxKeyWord.Text + "%";
Query queryCumtomer = new Query(DDCustomerInfo.Schema);
queryCumtomer.AddWhere(strOption, Comparison.Like, strKey);
DataTable dtCustomer = queryCumtomer.ExecuteDataSet().Tables[0];
if (this.tbxKeyWord.Text.Length == 0)
{
}
else if (dtCustomer.Rows.Count > 0)
{
query.AddWhere("CustomerID", dtCustomer.Rows[0]["CustomerID"]);
}
else
{
query.AddWhere("CustomerID", 0);
}
}
if (strStatus == "过期")
{
query.AddWhere("StatusID", Comparison.In, new int[] { 1, 3 });
query.AddBetweenAnd("EndDate", Convert.ToDateTime("1900-1-1"), DateTime.Now);
}
else if (strStatus == "在库")
{
query.AddWhere("StatusID", Comparison.In, new int[] { 1, 3, 4, 5 });
}
else if (strStatus != "全部")
{
query.AddWhere("StatusID", intStatusID);
}
else
{
query.AddWhere("StatusID", Comparison.NotEquals, 6); //6--已删除
}
DataTable dt = query.ExecuteDataSet().Tables[0];
if (dt.Rows.Count > 0)
{
for (int i = 0; i < dt.Rows.Count; i++)
{
//查询当品总价值
double totalAmount = 0;
Query queryOperation = new Query(DDOperation.Schema);
queryOperation.AddWhere("TicketID", dt.Rows[i]["TicketID"]);
queryOperation.AddWhere("NextOperationID", 0);
queryOperation.AddWhere("OperationType", Comparison.NotEquals, 6);
DataTable dtOperation = queryOperation.ExecuteDataSet().Tables[0];
for (int j = 0; j < dtOperation.Rows.Count; j++)
{
totalAmount += Convert.ToInt32(dtOperation.Rows[j]["Amount"].ToString());
}
DataRow drow = m_GridTable.NewRow();
drow["TicketID"] = dt.Rows[i]["TicketID"];
drow["TicketNumber"] = dt.Rows[i]["TicketNumber"];
drow["StartDate"] = dt.Rows[i]["StartDate"];
drow["EndDate"] = dt.Rows[i]["EndDate"];
drow["TotalAmount"] = totalAmount;
drow["StatusID"] = dt.Rows[i]["StatusID"];
DDCustomerInfo customerInfo = new DDCustomerInfo("CustomerID", Convert.ToInt32(dt.Rows[i]["CustomerID"]));
DDOperation operation = new DDOperation("TicketID", Convert.ToInt32(dt.Rows[i]["TicketID"]));
drow["CustomerName"] = customerInfo.CustomerName;
drow["OperaterName"] = operation.OperaterName;
m_GridTable.Rows.Add(drow);
}
}
}
private void InitStandardTakingClassSource()
{
DateTime startDate = Convert.ToDateTime(this.tbxStartDate.Text.Trim());
DateTime endDate = Convert.ToDateTime(this.tbxEndDate.Text.Trim());
Query queryClass=new Query(DDPawnageClass.Schema);
queryClass.AddWhere("ParentID",0);
DataTable dtClass=queryClass.ExecuteDataSet().Tables[0];
if(dtClass.Rows.Count>0)
{
int parentClassID;
string parentClassName;
double serviceFee = 0;
double returnFee = 0;
double overdueFee = 0;
double interestFee = 0;
double totalFee = 0;
double reckonPL = 0;
int operationType=0;
for(int i=0;i<dtClass.Rows.Count;i++)
{
parentClassID=Convert.ToInt32(dtClass.Rows[i]["ClassID"]);
parentClassName=dtClass.Rows[i]["ClassName"].ToString();
serviceFee = 0;
returnFee = 0;
overdueFee = 0;
interestFee = 0;
totalFee = 0;
reckonPL = 0;
Query query = new Query(DDPawnageInfo.Schema);
query.AddWhere("ParentID",parentClassID);
DataTable dt = query.ExecuteDataSet().Tables[0];
if (dt.Rows.Count > 0)
{
for (int j = 0; j < dt.Rows.Count; j++)
{
Query queryOperation = new Query(DDOperation.Schema);
queryOperation.AddBetweenAnd("OperationDate", startDate, endDate);
queryOperation.AddWhere("PawnageID",Convert.ToInt32(dt.Rows[j]["PawnageID"]));
DataTable dtOperation = queryOperation.ExecuteDataSet().Tables[0];
if (dtOperation.Rows.Count > 0)
{
for (int k = 0; k < dtOperation.Rows.Count; k++)
{
operationType=Convert.ToInt32(dtOperation.Rows[k]["OperationType"]);
if (operationType == 1 || operationType == 2 || operationType == 3)
{
serviceFee += Convert.ToDouble(dtOperation.Rows[k]["ServiceFee"]);
returnFee += Convert.ToDouble(dtOperation.Rows[k]["ReturnFee"]);
overdueFee += Convert.ToDouble(dtOperation.Rows[k]["OverdueFee"]);
interestFee += Convert.ToDouble(dtOperation.Rows[k]["InterestFee"]);
}
if (operationType == 7)
{
reckonPL =Convert.ToDouble(dtOperation.Rows[k]["ReckonAmount"])-Convert.ToDouble(dtOperation.Rows[k]["Amount"]);
}
}
}
}
totalFee = serviceFee - returnFee + overdueFee + interestFee;
}
DataRow drow = m_ClassTakingTable.NewRow();
drow["ClassName"] = parentClassName;
drow["ServiceFee"] = serviceFee;
drow["ReturnFee"] = returnFee;
drow["OverdueFee"] = overdueFee;
drow["InterestFee"] = interestFee;
drow["TotalFee"] = totalFee;
drow["ReckoningPL"] = reckonPL;
m_ClassTakingTable.Rows.Add(drow);
}
}
}
private void OperationAmount()
{
DateTime startDate = Convert.ToDateTime(this.tbxStartDate.Text.Trim());
DateTime endDate = Convert.ToDateTime(this.tbxEndDate.Text.Trim());
double newPawnAmount = 0;
double closeOfNewAmount = 0;
double redeemPawnAmount = 0;
double renewPawnAmount = 0;
double closePawnAmount = 0;
double goldOfClosePawnAmount = 0;
double clearPawnAmount = 0;
double clearPawnReckonAmount = 0;
double reckoningPL = 0; //清算盈亏
double unclearPawnAmount = 0;
double newPawnServiceFee = 0; //建当服务费
double renewPawnServiceFee = 0; //续当服务费
double interestFee = 0; //典当利息
double overDueFee = 0; //逾期服务费
double returnFee = 0; //返回服务费
double totalFee = 0; //总计营收
int operationType = 0;
double amount = 0;
Query queryOperations = new Query(DDOperation.Schema);
queryOperations.AddBetweenAnd("OperationDate", startDate, endDate);
DataTable dtOperations = queryOperations.ExecuteDataSet().Tables[0];
if (dtOperations.Rows.Count > 0)
{
for (int i = 0; i < dtOperations.Rows.Count; i++)
{
operationType = Convert.ToInt32(dtOperations.Rows[i]["OperationType"]);
amount = Convert.ToDouble(dtOperations.Rows[i]["Amount"]);
switch (operationType)
{
case 1:
newPawnAmount += amount;
DDPawnTicket newTicket = new DDPawnTicket("TicketID", Convert.ToInt32(dtOperations.Rows[i]["TicketID"]));
if (newTicket.StatusID == 4 || newTicket.StatusID == 7)
{
closeOfNewAmount += amount;
}
newPawnServiceFee += Convert.ToDouble(dtOperations.Rows[i]["ServiceFee"]);
interestFee += Convert.ToDouble(dtOperations.Rows[i]["InterestFee"]);
break;
case 2:
redeemPawnAmount += amount;
overDueFee += Convert.ToDouble(dtOperations.Rows[i]["OverdueFee"]);
returnFee += Convert.ToDouble(dtOperations.Rows[i]["ReturnFee"]);
interestFee += Convert.ToDouble(dtOperations.Rows[i]["InterestFee"]);
break;
case 3:
renewPawnAmount += amount;
renewPawnServiceFee += Convert.ToDouble(dtOperations.Rows[i]["ServiceFee"]);
interestFee += Convert.ToDouble(dtOperations.Rows[i]["InterestFee"]);
break;
case 4:
closePawnAmount += amount;
DDPawnageInfo newPawnage = new DDPawnageInfo("PawnageID", Convert.ToInt32(dtOperations.Rows[i]["PawnageID"]));
if (newPawnage.ParentID == 2) //2:黄金类ClassID
{
goldOfClosePawnAmount += amount;
}
break;
case 7:
clearPawnAmount += amount;
clearPawnReckonAmount += Convert.ToDouble(dtOperations.Rows[i]["ReckonAmount"]);
break;
default:
break;
}
}
reckoningPL = clearPawnReckonAmount - clearPawnAmount; //清算盈亏
unclearPawnAmount = closePawnAmount - clearPawnAmount; //未清算金额
totalFee = newPawnServiceFee + renewPawnServiceFee + interestFee + overDueFee - returnFee;
}
double allNewPawnAmount = 0;
//double closeOfAllNewPawnAmount = 0;
double allRedeemPawnAmount = 0;
double allClosePawnAmount = 0;
DDCompanyInfo company = new DDCompanyInfo("CompanyID", 1);
DateTime setupDate = Convert.ToDateTime(company.SetupDate);
Query queryAllOperations = new Query(DDOperation.Schema);
queryAllOperations.AddBetweenAnd("OperationDate", setupDate, endDate); //所有的操作记录
DataTable dtAllOperations = queryAllOperations.ExecuteDataSet().Tables[0];
if (dtAllOperations.Rows.Count > 0)
{
for (int j = 0; j < dtAllOperations.Rows.Count; j++)
{
operationType = Convert.ToInt32(dtAllOperations.Rows[j]["OperationType"]);
amount = Convert.ToDouble(dtAllOperations.Rows[j]["Amount"]);
if (operationType == 1)
{
allNewPawnAmount += amount;
//DDPawnTicket newTicket = new DDPawnTicket("TicketID", Convert.ToInt32(dtAllOperations.Rows[j]["TicketID"]));
//if (newTicket.StatusID == 4 || newTicket.StatusID == 7)
//{
// closeOfAllNewPawnAmount += amount;
//}
}
if (operationType == 2)
{
allRedeemPawnAmount += amount;
}
if (operationType == 4)
{
allClosePawnAmount += amount;
}
}
}
double startTotalAmount = 0;
double endTotalAmount = 0;
double increAmount = 0;
double totalAmount = 0;
endTotalAmount = allNewPawnAmount - allClosePawnAmount - allRedeemPawnAmount;
increAmount = newPawnAmount - closePawnAmount - redeemPawnAmount;
startTotalAmount = endTotalAmount - increAmount;
totalAmount = endTotalAmount + unclearPawnAmount;
double percentCloseOfNewPawn = 0;
double percentIncreOfStart = 0;
double percentIncreOfEnd = 0;
double percentNewPawnServiceFeeOfTotal = 0;
double percentRenewPawnServiceFeeOfTotal = 0;
double percentInterestFeeOfTotal = 0;
double percentRetureFeeOfTotal = 0;
double percentOverdueFeeOfTotal = 0;
if (newPawnAmount != 0)
{
percentCloseOfNewPawn = closeOfNewAmount * 100 / newPawnAmount;
percentCloseOfNewPawn = DianDangFunction.myRound(percentCloseOfNewPawn, MainForm.AmountAccuracy);
}
if (startTotalAmount != 0)
{
percentIncreOfStart = increAmount * 100 / startTotalAmount;
percentIncreOfStart = DianDangFunction.myRound(percentIncreOfStart, MainForm.AmountAccuracy);
}
if (endTotalAmount != 0)
{
percentIncreOfEnd = increAmount * 100 / endTotalAmount;
percentIncreOfEnd = DianDangFunction.myRound(percentIncreOfEnd, MainForm.AmountAccuracy);
}
if (totalFee != 0)
{
percentNewPawnServiceFeeOfTotal = newPawnServiceFee * 100 / totalFee;
percentNewPawnServiceFeeOfTotal = DianDangFunction.myRound(percentNewPawnServiceFeeOfTotal, MainForm.AmountAccuracy);
percentRenewPawnServiceFeeOfTotal = renewPawnServiceFee * 100 / totalFee;
percentRenewPawnServiceFeeOfTotal = DianDangFunction.myRound(percentRenewPawnServiceFeeOfTotal, MainForm.AmountAccuracy);
percentInterestFeeOfTotal = interestFee * 100 / totalFee;
percentInterestFeeOfTotal = DianDangFunction.myRound(percentInterestFeeOfTotal, MainForm.AmountAccuracy);
percentRetureFeeOfTotal = returnFee * 100 / totalFee;
percentRetureFeeOfTotal = DianDangFunction.myRound(percentRetureFeeOfTotal, MainForm.AmountAccuracy);
percentOverdueFeeOfTotal = overDueFee * 100 / totalFee;
percentOverdueFeeOfTotal = DianDangFunction.myRound(percentOverdueFeeOfTotal, MainForm.AmountAccuracy);
}
this.lblNewPawnAmount.Text = newPawnAmount.ToString();
this.lblCloseOfNewAmount.Text = closeOfNewAmount.ToString();
this.lblRenewPawnAmount.Text = renewPawnAmount.ToString();
this.lblSalesSize.Text = (newPawnAmount + renewPawnAmount).ToString();
this.lblRedeemPawnAmount.Text = redeemPawnAmount.ToString();
this.lblClosePawnAmount.Text = closePawnAmount.ToString();
this.lblGoldOfCloseAmount.Text = goldOfClosePawnAmount.ToString();
this.lblStartTotalAmount.Text = startTotalAmount.ToString();
this.lblEndTotalAmount.Text = endTotalAmount.ToString();
this.lblIncreAmount.Text = increAmount.ToString();
this.lblTotalFee.Text = totalFee.ToString();
newPawnServiceFee = DianDangFunction.myRound(newPawnServiceFee, MainForm.AmountAccuracy);
this.lblNewPawnServiceFee.Text = newPawnServiceFee.ToString();
this.lblRenewPawnServiceFee.Text = renewPawnServiceFee.ToString();
this.lblInterestFee.Text = interestFee.ToString();
this.lblOverDueFee.Text = overDueFee.ToString();
this.lblReturnFee.Text = returnFee.ToString();
this.lblCloseClearAmount.Text = clearPawnAmount.ToString(); //绝当处理金额
this.lblClearAmount.Text = clearPawnReckonAmount.ToString(); //清算金额
this.lblReckoningPL.Text = reckoningPL.ToString();
this.lblUnclearAmount.Text = unclearPawnAmount.ToString();
this.lblTotalAmount.Text = totalAmount.ToString();
//总收入
this.lblTotalIncommingAmount.Text = (totalFee + redeemPawnAmount).ToString();
//总支出
this.lblTotalOutAmount.Text = newPawnAmount.ToString();
//收支余额
this.lblTotalBalance.Text = (totalFee + redeemPawnAmount - newPawnAmount).ToString("0.00");
this.lblCloseOfNewPercent.Text = percentCloseOfNewPawn.ToString();
this.lblIncreOfStartPercent.Text = percentIncreOfStart.ToString();
this.lblIncreOfEndPercent.Text = percentIncreOfEnd.ToString();
this.lblNewServiceFeeOfTotal.Text = percentNewPawnServiceFeeOfTotal.ToString();
this.lblRenewServiceFeeOfTotal.Text = percentRenewPawnServiceFeeOfTotal.ToString();
this.lblInterestFeeOfTotal.Text = percentInterestFeeOfTotal.ToString();
this.lblReturnfeeOfTotal.Text = percentRetureFeeOfTotal.ToString();
this.lblOverdueFeeOfTotal.Text = percentOverdueFeeOfTotal.ToString();
}
private void OperationCount()
{
try
{
DateTime startDate = Convert.ToDateTime(this.tbxStartDate.Text.Trim());
DateTime endDate = Convert.ToDateTime(this.tbxEndDate.Text.Trim());
int newPawnCount = 0;
int closeOfNewCount = 0;
int redeemPawnCount = 0;
int renewPawnCount = 0;
int closePawnCount = 0;
int goldOfClosePawnCount = 0;
int operationType = 0;
string selectList = string.Format("{0},{1},{2},{3},{4}", DDOperation.OperationNumberColumn.ColumnName, DDOperation.OperationTypeColumn.ColumnName, DDOperation.PawnageIDColumn.ColumnName, DDOperation.TicketIDColumn.ColumnName, DDOperation.OperationDateColumn.ColumnName);
Query queryOperations = new Query(DDOperation.Schema).SetSelectList(selectList).AddBetweenAnd("OperationDate", startDate, endDate).ORDER_BY(DDOperation.OperationNumberColumn.ColumnName);
DataTable dtOperations = queryOperations.ExecuteDataSet().Tables[0];
if (dtOperations.Rows.Count > 0)
{
string operationNumber = "0";
string preOperationNumber = "0";
for (int i = 0; i < dtOperations.Rows.Count; i++)
{
operationNumber = dtOperations.Rows[i]["OperationNumber"].ToString();
if (operationNumber != preOperationNumber)
{
operationType = Convert.ToInt32(dtOperations.Rows[i]["OperationType"]);
switch (operationType)
{
case 1:
newPawnCount++;
DDPawnTicket newTicket = new DDPawnTicket("TicketID", Convert.ToInt32(dtOperations.Rows[i]["TicketID"]));
if (newTicket.StatusID == 4 || newTicket.StatusID == 7)
{
closeOfNewCount++;
}
break;
case 2:
redeemPawnCount++;
break;
case 3:
renewPawnCount++;
break;
case 4:
closePawnCount++;
DDPawnageInfo newPawnage = new DDPawnageInfo("PawnageID", Convert.ToInt32(dtOperations.Rows[i]["PawnageID"]));
if (newPawnage.ParentID == 2) //109:黄金类ClassID
{
goldOfClosePawnCount++;
}
break;
default:
break;
}
preOperationNumber = operationNumber;
}
}
}
double allNewPawnCount = 0;
//double closeOfAllNewPawnCount = 0;
double allRedeemPawnCount = 0;
double allClosePawnCount = 0;
DDCompanyInfo company = new DDCompanyInfo("CompanyID", 1);
DateTime setupDate = Convert.ToDateTime(company.SetupDate);
Query queryAllOperations = new Query(DDOperation.Schema).SetSelectList(selectList).DISTINCT().ORDER_BY(DDOperation.OperationNumberColumn.ColumnName);
queryAllOperations.AddBetweenAnd("OperationDate", setupDate, endDate); //所有的操作记录
DataTable dtAllOperations = queryAllOperations.ExecuteDataSet().Tables[0];
if (dtAllOperations.Rows.Count > 0)
{
string operationNumber = "0";
string preOperationNumber = "0";
for (int j = 0; j < dtAllOperations.Rows.Count; j++)
{
operationNumber = dtAllOperations.Rows[j]["OperationNumber"].ToString();
if (operationNumber != preOperationNumber)
{
operationType = Convert.ToInt32(dtAllOperations.Rows[j]["OperationType"]);
if (operationType == 1)
{
allNewPawnCount++;
// DDPawnTicket newTicket = new DDPawnTicket("TicketID", Convert.ToInt32(dtAllOperations.Rows[j]["TicketID"]));
// if (newTicket.StatusID == 4 || newTicket.StatusID == 7)
// {
// closeOfAllNewPawnCount++;
// }
}
if (operationType == 2)
{
allRedeemPawnCount++;
}
if (operationType == 4)
{
allClosePawnCount++;
}
preOperationNumber = operationNumber;
}
}
}
double startTotalCount = 0;
double endTotalCount = 0;
double increCount = 0;
endTotalCount = allNewPawnCount - allRedeemPawnCount - allClosePawnCount;
increCount = newPawnCount - redeemPawnCount - closePawnCount;
startTotalCount = endTotalCount - increCount;
this.lblNewPawnCount.Text = newPawnCount.ToString();
this.lblCloseOfNewCount.Text = closeOfNewCount.ToString();
this.lblRenewPawnCount.Text = renewPawnCount.ToString();
this.lblSalesCount.Text = (newPawnCount + renewPawnCount).ToString();
this.lblRedeemPawnCount.Text = redeemPawnCount.ToString();
this.lblClosePawnCount.Text = closePawnCount.ToString();
this.lblGoldOfCloseCount.Text = goldOfClosePawnCount.ToString();
this.lblStartTotalCount.Text = startTotalCount.ToString();
this.lblEndTotalCount.Text = endTotalCount.ToString();
this.lblIncreCount.Text = increCount.ToString();
}
catch (Exception ex)
{
throw ex;
}
}
private void InitGridSource()
{
double totalAmount = 0;
double totalServiceFee = 0;
double totalInterestFee = 0;
double totalReturnFee = 0;
double totalOverdueFee = 0;
m_GridTable.Rows.Clear();
SearchStatus.Clear();
Query query = new Query(DDOperation.Schema);
string strStatus = this.cbxSearchRange.Text;
int intStatusID = Convert.ToInt32(this.cbxSearchRange.SelectedValue);
query.AddBetweenAnd("OperationDate", Convert.ToDateTime(this.tbxStartDate.Text), Convert.ToDateTime(this.tbxEndDate.Text));
if (strStatus == "全部")
{
query.AddWhere("OperationType", Comparison.In, new int[] { 1, 2, 3, 4, 5, 6, 7 }); //6--已删除
}
else
{
query.AddWhere("OperationType", intStatusID);
}
DataTable dt = query.ExecuteDataSet().Tables[0];
if (dt.Rows.Count > 0)
{
for (int i = 0; i < dt.Rows.Count; i++)
{
string Item = dt.Rows[i]["OperationNumber"].ToString();
if (SearchStatus[Item] == null)
{
SearchStatus.Add(Item, Item);
DataRow drow = m_GridTable.NewRow();
drow["TicketID"] = dt.Rows[i]["TicketID"];
Query queryTicket = new Query(DDPawnTicket.Schema);
DDPawnTicket newTicket = new DDPawnTicket("TicketID", dt.Rows[i]["TicketID"]);
drow["TicketNumber"] = newTicket.TicketNumber;
drow["OperationDate"] = dt.Rows[i]["OperationDate"];
drow["StartDate"] = dt.Rows[i]["StartDate"];
drow["EndDate"] = dt.Rows[i]["EndDate"];
drow["StatusID"] = dt.Rows[i]["OperationType"];
DDCustomerInfo customerInfo = new DDCustomerInfo("CustomerID", newTicket.CustomerID);
drow["CustomerName"] = customerInfo.CustomerName;
#region 计算费用及典当金额
totalAmount = 0;
totalInterestFee = 0;
totalServiceFee = 0;
totalReturnFee = 0;
totalOverdueFee = 0;
Query queryOperation = new Query(DDOperation.Schema);
queryOperation.AddWhere("OperationNumber", Item);
DataTable dtOperation = queryOperation.ExecuteDataSet().Tables[0];
for (int j = 0; j < dtOperation.Rows.Count; j++)
{
totalAmount += Convert.ToDouble(dtOperation.Rows[j]["Amount"]);
totalServiceFee += Convert.ToDouble(dtOperation.Rows[j]["ServiceFee"]);
totalInterestFee += Convert.ToDouble(dtOperation.Rows[j]["InterestFee"]);
totalReturnFee += Convert.ToDouble(dtOperation.Rows[j]["ReturnFee"]);
totalOverdueFee += Convert.ToDouble(dtOperation.Rows[j]["OverdueFee"]);
}
drow["Amount"] = DianDangFunction.myRound(totalAmount, MainForm.AmountAccuracy);
drow["ServiceFee"] = DianDangFunction.myRound(totalServiceFee, MainForm.AmountAccuracy);
drow["InterestFee"] = DianDangFunction.myRound(totalInterestFee, MainForm.AmountAccuracy);
drow["OverdueServiceFee"] = DianDangFunction.myRound(totalOverdueFee, MainForm.AmountAccuracy);
drow["ReturnServiceFee"] = DianDangFunction.myRound(totalReturnFee, MainForm.AmountAccuracy);
#endregion
drow["OperaterName"] = dt.Rows[i]["OperaterName"];
drow["OperationNumber"] = dt.Rows[i]["OperationNumber"];
m_GridTable.Rows.Add(drow);
}
}
}
}
