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