private double[] GetAmountByStatus(int[] iStatus) { double[] Result = new double[2]; Result[0] = 0; Result[1] = 0; Query queryTicket = new Query(DDPawnTicket.Schema); queryTicket.AddWhere("CustomerID", m_CustomerID); queryTicket.AddWhere("StatusID", Comparison.In, iStatus); DataTable dtTicket = queryTicket.ExecuteDataSet().Tables[0]; int[] TicketIDList = new int[dtTicket.Rows.Count]; if (dtTicket.Rows.Count > 0) { for (int j = 0; j < dtTicket.Rows.Count; j++) { TicketIDList[j] = Convert.ToInt32(dtTicket.Rows[j]["TicketID"].ToString()); Result[1]++; } Query queryOperation = new Query(DDOperation.Schema); queryOperation.AddWhere("TicketID", Comparison.In, TicketIDList); queryOperation.AddWhere("NextOperationID", 0); DataTable dtOperation = queryOperation.ExecuteDataSet().Tables[0]; for (int j = 0; j < dtOperation.Rows.Count; j++) { Result[0] = Result[0] + Convert.ToInt32(dtOperation.Rows[j]["Amount"].ToString()); } Result[0] = DianDangFunction.myRound(Result[0], MainForm.AmountAccuracy); } return(Result); }
private void CaculateAmount() { LoadingStatus = 1; PawnSpan Span = DianDangFunction.GetPawnSpan(DateTime.Parse(tbxStartDate.Text), DateTime.Parse(tbxEndDate.Text)); double amount = 0, totalAmount = 0, serviceFee = 0, totalServiceFee = 0, interestFee = 0, totalInterestFee = 0, CustomerPaid = 0; for (int i = dataGridView1.Rows.Count - 1; i > -1; i--) { amount = Convert.ToDouble(dataGridView1.Rows[i].Cells["Amount"].Value); serviceFee = (Span.Months + Span.CountDays / 30) * amount * Convert.ToDouble(dataGridView1.Rows[i].Cells["FeeRate"].Value) / 100; interestFee = (Span.Months + Span.CountDays / 30) * amount * Convert.ToDouble(dataGridView1.Rows[i].Cells["InterestRate"].Value) / 100; serviceFee = DianDangFunction.myRound(serviceFee, MainForm.AmountAccuracy); interestFee = DianDangFunction.myRound(interestFee, MainForm.AmountAccuracy); dataGridView1.Rows[i].Cells["ServiceFee"].Value = serviceFee; totalAmount += amount; totalServiceFee += serviceFee; totalInterestFee += interestFee; } CustomerPaid = totalServiceFee + Convert.ToDouble(this.tbxLastInterestFee.Text); CustomerPaid = DianDangFunction.myRound(CustomerPaid, MainForm.AmountAccuracy); this.tbxTotalAmount.Text = totalAmount.ToString(); this.tbxServiceFee.Text = totalServiceFee.ToString(); this.tbxInterestFee.Text = totalInterestFee.ToString(); this.tbxCustomerPaid.Text = CustomerPaid.ToString(); this.lblTotalAmount.Text = newChinese.toChineseChar(Convert.ToDecimal(totalAmount)); this.lblServiceFee.Text = newChinese.toChineseChar(Convert.ToDecimal(totalServiceFee)); this.lblInterestFee.Text = newChinese.toChineseChar(Convert.ToDecimal(totalInterestFee)); this.lblCustomerPaid.Text = newChinese.toChineseChar(Convert.ToDecimal(CustomerPaid)); LoadingStatus = 0; }
private void btnAddCustomer_Click(object sender, EventArgs e) { DialogResult res = CheckAllMessage(); if (res == DialogResult.OK) { try { DDCustomerInfo newCustomer = new DDCustomerInfo(); newCustomer.CustomerName = this.tbxName.Text; newCustomer.PhoneNumber = this.tbxPhone.Text; newCustomer.Address = this.tbxAddress.Text; newCustomer.ContactPerson = this.tbxContactPerson.Text; newCustomer.CertTypeID = Convert.ToInt32(this.cbxCertType.SelectedValue); newCustomer.CertNumber = this.tbxCertNum.Text; newCustomer.CreatDate = DianDangFunction.ChangeDateFormat(DateTime.Now.ToShortDateString()); newCustomer.Save(); MessageBox.Show("添加成功!", "添加客户"); } catch { MessageBox.Show("添加失败,请检查数据库是否连接正确!", "添加客户"); } } }
private void LoadTicketInfo(int intTicketID) { m_TicketID = intTicketID; double TotalLastInterestFee = 0; double LastInterestFee = 0; DDPawnTicket newTicket = new DDPawnTicket("TicketID", intTicketID); this.lblOldTicketNum.Text = newTicket.TicketNumber; this.tbxStartDate.Text = newTicket.EndDate; this.tbxEndDate.Text = newTicket.EndDate; m_EndDate = newTicket.EndDate; DateTime operationDate = DateTime.Now; this.tbxOperationDate.Text = operationDate.Year.ToString() + "-" + operationDate.Month.ToString() + "-" + operationDate.Day.ToString(); Query query = new Query(DDOperation.Schema); query.AddWhere("TicketID", intTicketID); query.AddWhere("NextOperationID", 0); query.AddWhere("OperationType", Comparison.NotEquals, 6); DataTable dt = query.ExecuteDataSet().Tables[0]; for (int i = 0; i < dt.Rows.Count; i++) { DDPawnageInfo pawnageInfo = new DDPawnageInfo("PawnageID", dt.Rows[i]["PawnageID"]); this.dataGridView1.Rows.Add(1); dataGridView1.Rows[dataGridView1.Rows.Count - 1].Cells["OperationID"].Value = dt.Rows[i]["OperationID"]; dataGridView1.Rows[dataGridView1.Rows.Count - 1].Cells["PawnageID"].Value = pawnageInfo.PawnageID; dataGridView1.Rows[dataGridView1.Rows.Count - 1].Cells["ParentClassID"].Value = pawnageInfo.ParentID; DDPawnageClass parentClass = new DDPawnageClass("ClassID", pawnageInfo.ParentID); dataGridView1.Rows[dataGridView1.Rows.Count - 1].Cells["ParentClass"].Value = parentClass.ClassName; dataGridView1.Rows[dataGridView1.Rows.Count - 1].Cells["ChildClassID"].Value = pawnageInfo.ClassID; DDPawnageClass childClass = new DDPawnageClass("ClassID", pawnageInfo.ClassID); dataGridView1.Rows[dataGridView1.Rows.Count - 1].Cells["ChildClass"].Value = childClass.ClassName; dataGridView1.Rows[dataGridView1.Rows.Count - 1].Cells["FeeRate"].Value = dt.Rows[i]["ServiceFeeRate"].ToString(); dataGridView1.Rows[dataGridView1.Rows.Count - 1].Cells["InterestRate"].Value = dt.Rows[i]["InterestFeeRate"].ToString(); dataGridView1.Rows[dataGridView1.Rows.Count - 1].Cells["Description"].Value = pawnageInfo.Description; dataGridView1.Rows[dataGridView1.Rows.Count - 1].Cells["CountNumber"].Value = pawnageInfo.CountNumber; dataGridView1.Rows[dataGridView1.Rows.Count - 1].Cells["Weight"].Value = pawnageInfo.Weight; dataGridView1.Rows[dataGridView1.Rows.Count - 1].Cells["DiscountPercent"].Value = pawnageInfo.DiscountPercent; dataGridView1.Rows[dataGridView1.Rows.Count - 1].Cells["Amount"].Value = dt.Rows[i]["Amount"]; dataGridView1.Rows[dataGridView1.Rows.Count - 1].Cells["Remark"].Value = pawnageInfo.Remark; //计算上期利息 DateTime StartDate = DateTime.Parse(dt.Rows[i]["StartDate"].ToString()); DateTime EndDate = DateTime.Parse(dt.Rows[i]["EndDate"].ToString()); PawnSpan LastInterestSpan = DianDangFunction.GetPawnSpan(StartDate, EndDate, FeeType.InterestFee, PeridType.Within); LastInterestFee = (LastInterestSpan.Months + LastInterestSpan.CountDays / 30) * Convert.ToDouble(dt.Rows[i]["InterestFeeRate"].ToString()) * Convert.ToDouble(dt.Rows[i]["Amount"].ToString()) / 100; LastInterestFee = DianDangFunction.myRound(LastInterestFee, MainForm.AmountAccuracy); dataGridView1.Rows[dataGridView1.Rows.Count - 1].Cells["InterestFee"].Value = LastInterestFee; TotalLastInterestFee += LastInterestFee; } CaculateAmount(); this.tbxLastInterestFee.Text = TotalLastInterestFee.ToString(); this.lblLastInterestFee.Text = newChinese.toChineseChar(Convert.ToDecimal(TotalLastInterestFee)); }
private void CaculateDatagridAmount() { LoadingStatus = 1; double PaidFee = 0; double totalServiceFee = 0, totalInterestFee = 0, totalReturnFee = 0, totalOverdueFee = 0, totalPaidInterestFee = 0, totalAmount = 0, totalReturnInterestFee = 0; for (int i = 0; i < dataGridView1.Rows.Count; i++) { try { dataGridView1.Rows[i].Cells["ServiceFee"].Value = DianDangFunction.myRound(Convert.ToDouble(dataGridView1.Rows[i].Cells["ChargedServiceFee"].Value.ToString()) + Convert.ToDouble(dataGridView1.Rows[i].Cells["OverdueFee"].Value.ToString()) - Convert.ToDouble(dataGridView1.Rows[i].Cells["ReturnFee"].Value.ToString()), MainForm.AmountAccuracy); dataGridView1.Rows[i].Cells["InterestFee"].Value = DianDangFunction.myRound(Convert.ToDouble(dataGridView1.Rows[i].Cells["ChargedInterestFee"].Value.ToString()) + Convert.ToDouble(dataGridView1.Rows[i].Cells["PaidInterestFee"].Value.ToString()) - Convert.ToDouble(dataGridView1.Rows[i].Cells["ReturnInterestFee"].Value.ToString()), MainForm.AmountAccuracy); totalAmount += Convert.ToDouble(dataGridView1.Rows[i].Cells["Amount"].Value); totalServiceFee += Convert.ToDouble(dataGridView1.Rows[i].Cells["ServiceFee"].Value); totalInterestFee += Convert.ToDouble(dataGridView1.Rows[i].Cells["InterestFee"].Value); totalReturnFee += Convert.ToDouble(dataGridView1.Rows[i].Cells["ReturnFee"].Value); totalOverdueFee += Convert.ToDouble(dataGridView1.Rows[i].Cells["OverdueFee"].Value); totalPaidInterestFee += Convert.ToDouble(dataGridView1.Rows[i].Cells["PaidInterestFee"].Value); totalReturnInterestFee += Convert.ToDouble(dataGridView1.Rows[i].Cells["ReturnInterestFee"].Value); } catch { MessageBox.Show("请输入正确的金额", "警告!!"); } } totalAmount = DianDangFunction.myRound(totalAmount, MainForm.AmountAccuracy); totalServiceFee = DianDangFunction.myRound(totalServiceFee, MainForm.AmountAccuracy); totalInterestFee = DianDangFunction.myRound(totalInterestFee, MainForm.AmountAccuracy); totalReturnFee = DianDangFunction.myRound(totalReturnFee, MainForm.AmountAccuracy); totalOverdueFee = DianDangFunction.myRound(totalOverdueFee, MainForm.AmountAccuracy); totalPaidInterestFee = DianDangFunction.myRound(totalPaidInterestFee, MainForm.AmountAccuracy); totalReturnInterestFee = DianDangFunction.myRound(totalReturnInterestFee, MainForm.AmountAccuracy); this.tbxAmount.Text = totalAmount.ToString(); this.tbxServiceFee.Text = totalServiceFee.ToString(); this.tbxInterestFee.Text = totalInterestFee.ToString(); this.tbxReturnFee.Text = totalReturnFee.ToString(); this.tbxOverdueFee.Text = totalOverdueFee.ToString(); PaidFee = (totalAmount + totalPaidInterestFee + totalOverdueFee - totalReturnFee - totalReturnInterestFee); if (PaidFee >= 0) { this.lblFinalString.Text = "客户应付:"; this.tbxPaidFee.Text = DianDangFunction.myRound(PaidFee, MainForm.AmountAccuracy).ToString(); } else { this.lblFinalString.Text = "典当行应付:"; this.tbxPaidFee.Text = DianDangFunction.myRound((0 - PaidFee), MainForm.AmountAccuracy).ToString(); } this.tbxPaidInterestFee.Text = totalPaidInterestFee.ToString(); this.tbxReturnInterestFee.Text = totalReturnInterestFee.ToString(); LoadingStatus = 0; }
private double CaculateReturnFee(double amount, double feeRate, double interestRate) { double returnFee = 0; /* * double TotalServiceFee = 0; * double TotalInterestFee = 0; * * Query queryOperation = new Query(DDOperation.Schema); * queryOperation.AddWhere("TicketID", m_TicketID); * queryOperation.AddWhere("OperationType", Comparison.In, new int[] { 1, 3 }); * queryOperation.AddWhere("Deleted", Comparison.NotEquals, 1); * DataTable dtOperation = queryOperation.ExecuteDataSet().Tables[0]; * if (dtOperation.Rows.Count > 0) * { * for (int i = 0; i < dtOperation.Rows.Count; i++) * { * TotalServiceFee += Convert.ToDouble(dtOperation.Rows[i]["ServiceFee"]); * TotalInterestFee += Convert.ToDouble(dtOperation.Rows[i]["InterestFee"]); * } * } * * feeRate++; */ TimeSpan returnTimeSpan = m_EndDate.Subtract(m_OperationDate); int returnDays = returnTimeSpan.Days; int months = returnDays / 30; returnDays = returnDays - 30 * months; if (returnDays < 16) { returnFee = (feeRate + interestRate) * amount * months / 100; } else if (returnDays < 26) { returnFee = (feeRate + interestRate) * amount * (15 + months * 30) / 100 / 30; } else { returnFee = (feeRate + interestRate) * amount * (25 + months * 30) / 100 / 30; } returnFee = DianDangFunction.myRound(returnFee, MainForm.AmountAccuracy); return(returnFee); }
private double CaculateOverdueFee(double amount, double feeRate, double interestRate) { double overdueFee = 0; TimeSpan overdueTimeSpan = m_OperationDate.Subtract(m_EndDate); //TimeSpan termTimeSpan = m_EndDate.Subtract(m_StartDate); //int termDays = termTimeSpan.Days; int overdueDays = overdueTimeSpan.Days; if (overdueDays > 0) { overdueFee = (feeRate + interestRate) * amount * overdueDays / 30 / 100; } overdueFee = DianDangFunction.myRound(overdueFee, MainForm.AmountAccuracy); return(overdueFee); }
private void monthCalendar1_DateSelected(object sender, DateRangeEventArgs e) { string iDate = ""; iDate = DianDangFunction.ChangeDateFormat(e.Start.ToShortDateString()); switch (DateSelector) { case 1: this.tbxStartDate.Text = iDate; break; case 2: this.tbxEndDate.Text = iDate; break; default: break; } this.monthCalendar1.Visible = false; }
void dataGridView1_CellValueChanged(object sender, System.Windows.Forms.DataGridViewCellEventArgs e) { if (LoadingStatus == 0 && e.ColumnIndex != -1) { if (e.ColumnIndex == dataGridView1.Columns["FeeRate"].Index) { try { PawnSpan ServiceSpan = DianDangFunction.GetPawnSpan(DateTime.Parse(tbxStartDate.Text), DateTime.Parse(tbxEndDate.Text), FeeType.ServiceFee, PeridType.Within); double amount = Convert.ToDouble(dataGridView1.Rows[e.RowIndex].Cells["Amount"].Value.ToString()); double serviceFeeRate = Convert.ToDouble(dataGridView1.Rows[e.RowIndex].Cells["FeeRate"].Value.ToString()); dataGridView1.Rows[e.RowIndex].Cells["ServiceFee"].Value = (ServiceSpan.Months + ServiceSpan.CountDays / 30) * amount * serviceFeeRate / 100; CaculateDataGridAmount(); } catch { dataGridView1.Rows[e.RowIndex].Cells[e.ColumnIndex].Value = OldValue; MessageBox.Show("请输入正确的费率", "提示信息"); } } else { try { Convert.ToDouble(dataGridView1.Rows[e.RowIndex].Cells[e.ColumnIndex].Value.ToString()); CaculateDataGridAmount(); } catch { dataGridView1.Rows[e.RowIndex].Cells[e.ColumnIndex].Value = OldValue; MessageBox.Show("请输入正确的金额", "提示信息"); } } } //throw new System.NotImplementedException(); }
private double[] GetTotalFee() { double[] Result = new double[2]; Result[0] = 0; Result[1] = 0; Query queryTicket = new Query(DDPawnTicket.Schema); queryTicket.AddWhere("StatusID", Comparison.NotEquals, 6); queryTicket.AddWhere("CustomerID", m_CustomerID); 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("OperationType", Comparison.In, new int[] { 1, 2, 3 }); queryOperation.AddWhere("TicketID", Comparison.In, TicketIDList); DataTable dtOperation = queryOperation.ExecuteDataSet().Tables[0]; for (int i = 0; i < dtOperation.Rows.Count; i++) { Result[0] = Result[0] + Convert.ToDouble(dtOperation.Rows[i]["ServiceFee"].ToString()) + Convert.ToDouble(dtOperation.Rows[i]["OverdueFee"].ToString()) - Convert.ToDouble(dtOperation.Rows[i]["ReturnFee"].ToString()); Result[1] = Result[1] + Convert.ToDouble(dtOperation.Rows[i]["InterestFee"].ToString()); } Result[0] = DianDangFunction.myRound(Result[0], MainForm.AmountAccuracy); Result[1] = DianDangFunction.myRound(Result[1], MainForm.AmountAccuracy); return(Result); }
private void CaculateDataGridAmount() { LoadingStatus = 1; double totalServiceFee = 0; double totalLastInterestFee = 0; double totalInterestFee = 0; if (dataGridView1.Rows.Count > 0) { for (int i = 0; i < dataGridView1.Rows.Count; i++) { PawnSpan InterestSpan = DianDangFunction.GetPawnSpan(DateTime.Parse(tbxStartDate.Text), DateTime.Parse(tbxEndDate.Text), FeeType.InterestFee, PeridType.Within); double amount = Convert.ToDouble(dataGridView1.Rows[i].Cells["Amount"].Value.ToString()); double interestFeeRate = Convert.ToDouble(dataGridView1.Rows[i].Cells["InterestRate"].Value.ToString()); double interestFee = (InterestSpan.Months + InterestSpan.CountDays / 30) * amount * interestFeeRate / 100; interestFee = DianDangFunction.myRound(interestFee, MainForm.AmountAccuracy); totalInterestFee += interestFee; totalServiceFee += Convert.ToDouble(dataGridView1.Rows[i].Cells["ServiceFee"].Value); totalLastInterestFee += Convert.ToDouble(dataGridView1.Rows[i].Cells["InterestFee"].Value); } } tbxServiceFee.Text = DianDangFunction.myRound(totalServiceFee, MainForm.AmountAccuracy).ToString(); lblServiceFee.Text = newChinese.toChineseChar(Convert.ToDecimal(totalServiceFee)); tbxLastInterestFee.Text = DianDangFunction.myRound(totalLastInterestFee, MainForm.AmountAccuracy).ToString(); lblLastInterestFee.Text = newChinese.toChineseChar(Convert.ToDecimal(totalLastInterestFee)); tbxInterestFee.Text = DianDangFunction.myRound(totalInterestFee, MainForm.AmountAccuracy).ToString(); lblInterestFee.Text = newChinese.toChineseChar(Convert.ToDecimal(totalInterestFee)); tbxCustomerPaid.Text = DianDangFunction.myRound((totalServiceFee + totalLastInterestFee), MainForm.AmountAccuracy).ToString(); lblCustomerPaid.Text = newChinese.toChineseChar(Convert.ToDecimal(tbxCustomerPaid.Text)); LoadingStatus = 0; }
private void Summery() { double[] Result = new double[2]; double InHouseAmount = 0; int InHouseCount = 0; double RedeenAmout = 0; int RedeenCount = 0; double CloseAmount = 0; int CloseCount = 0; double TotalAmount = 0; double TotalCount = 0; //在库金额 Result = GetAmountByStatus(new int[] { 1, 3, 4, 5 }); InHouseAmount = Result[0]; InHouseCount = Convert.ToInt32(Result[1]); //赎回金额 Result = GetAmountByStatus(new int[] { 2 }); RedeenAmout = Result[0]; RedeenCount = Convert.ToInt32(Result[1]); //绝当金额 Result = GetAmountByStatus(new int[] { 4, 7 }); CloseAmount = Result[0]; CloseCount = Convert.ToInt32(Result[1]); //全部金额 Result = GetAmountByStatus(new int[] { 1, 2, 3, 4, 5, 7 }); TotalAmount = Result[0]; TotalCount = Convert.ToInt32(Result[1]); //输出 lblTotalAmount.Text = TotalAmount.ToString(); lblTotalCount.Text = TotalCount.ToString(); lblInHouseAmount.Text = InHouseAmount.ToString(); lblInHouseAmountPercent.Text = DianDangFunction.myRound((InHouseAmount / TotalAmount * 100), MainForm.AmountAccuracy).ToString() + "%"; lblInHouseCount.Text = InHouseCount.ToString(); lblInHouseCountPercent.Text = DianDangFunction.myRound((InHouseCount / TotalCount * 100), MainForm.AmountAccuracy).ToString() + "%"; lblRedeenAmount.Text = RedeenAmout.ToString(); lblRedeenAmountPercent.Text = DianDangFunction.myRound((RedeenAmout / TotalAmount * 100), MainForm.AmountAccuracy).ToString() + "%"; lblRedeenCount.Text = RedeenCount.ToString(); lblRedeenCountPercent.Text = DianDangFunction.myRound((RedeenCount / TotalCount * 100), MainForm.AmountAccuracy).ToString() + "%"; lblCloseAmount.Text = CloseAmount.ToString(); lblCloseAmountPercent.Text = DianDangFunction.myRound((CloseAmount / TotalAmount * 100), MainForm.AmountAccuracy).ToString() + "%"; lblCloseCount.Text = CloseCount.ToString(); lblCloseCountPercent.Text = DianDangFunction.myRound((CloseCount / TotalCount * 100), MainForm.AmountAccuracy).ToString() + "%"; Result = GetTotalFee(); lblTotalServiceFee.Text = Result[0].ToString(); lblTotalInterestFee.Text = Result[1].ToString(); DDCustomerInfo newInfo = new DDCustomerInfo("CustomerID", m_CustomerID); this.lblStartDate.Text = DianDangFunction.ChangeDateFormat(newInfo.CreatDate); Query queryTicket = new Query(DDPawnTicket.Schema); queryTicket.AddWhere("CustomerID", m_CustomerID); queryTicket.AddWhere("StatusID", Comparison.In, new int[] { 1, 2, 3 }); DataTable dtTicket = queryTicket.ExecuteDataSet().Tables[0]; int[] TicketIDList = new int[dtTicket.Rows.Count]; for (int j = 0; j < dtTicket.Rows.Count; j++) { TicketIDList[j] = Convert.ToInt32(dtTicket.Rows[j]["TicketID"].ToString()); } Query queryOperation = new Query(DDOperation.Schema); queryOperation.AddWhere("TicketID", Comparison.In, TicketIDList); queryOperation.AddWhere("NextOperationID", 0); queryOperation.ORDER_BY("OperationDate", "desc"); DataTable dtOperation = queryOperation.ExecuteDataSet().Tables[0]; lblLastDate.Text = DianDangFunction.ChangeDateFormat(dtOperation.Rows[0]["OperationDate"].ToString()); }
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 LoadTemplate(int iTicketID, string strOperationNumber) { double TotalAmount = 0; double TotalServiceFee = 0; double TotalLastInterestFee = 0; DDPawnTicket newTicket = new DDPawnTicket("TicketID", m_TicketID); Query queryOperation = new Query(DDOperation.Schema); queryOperation.AddWhere("TicketID", m_TicketID); queryOperation.AddWhere("OperationNumber", strOperationNumber); queryOperation.AddWhere("OperationType", Comparison.NotEquals, 6); DataTable dtOperation = queryOperation.ExecuteDataSet().Tables[0]; for (int i = 0; i < dtOperation.Rows.Count; i++) { TotalAmount += Convert.ToDouble(dtOperation.Rows[i]["Amount"].ToString()); TotalServiceFee += Convert.ToDouble(dtOperation.Rows[i]["ServiceFee"].ToString()); TotalLastInterestFee += Convert.ToDouble(dtOperation.Rows[i]["InterestFee"].ToString()); } TotalAmount = DianDangFunction.myRound(TotalAmount, MainForm.AmountAccuracy); TotalServiceFee = DianDangFunction.myRound(TotalServiceFee, MainForm.AmountAccuracy); TotalLastInterestFee = DianDangFunction.myRound(TotalLastInterestFee, MainForm.AmountAccuracy); MSExcel.Application excelApp; //Excel应用程序变量 MSExcel.Workbook excelDoc; //Excel文档变量 excelApp = new MSExcel.ApplicationClass(); //初始化 //由于使用的是COM库,因此有许多变量需要用Nothing代替 Object Nothing = Missing.Value; excelDoc = excelApp.Workbooks._Open(Application.StartupPath + @"\Pram\RenewTemplate.dd", Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value); //使用第一个工作表作为插入数据的工作表 MSExcel.Worksheet ws = (MSExcel.Worksheet)excelDoc.Sheets[1]; //声明一个MSExcel.Range 类型的变量r MSExcel.Range r; DDCompanyInfo company = new DDCompanyInfo("CompanyID", 1); //this.lblCompanyName.Text = company.CompanyName; //this.lblOldTicketNumber.Text = frmRenewPawnOperation.lblOldTicketNum.Text; r = ws.get_Range(("F" + 4), ("F" + 4)); r.Value2 = company.CompanyName; r = ws.get_Range(("M" + 4), ("M" + 4)); r.Value2 = newTicket.TicketNumber.ToString(); ; DDCustomerInfo newCustomer = new DDCustomerInfo("CustomerID", newTicket.CustomerID); //this.lblCustomerName.Text = newCustomer.CustomerName; //this.lblContactPerson.Text = newCustomer.ContactPerson; r = ws.get_Range(("F" + 5), ("F" + 5)); r.Value2 = newCustomer.CustomerName; r = ws.get_Range(("M" + 5), ("M" + 5)); r.Value2 = newCustomer.ContactPerson; //this.lblAmount.Text = frmRenewPawnOperation.tbxTotalAmount.Text; //this.lblAmountChinese.Text = newChinese.toChineseChar(Convert.ToDecimal(frmRenewPawnOperation.tbxTotalAmount.Text)); //this.lblServiceFee.Text = frmRenewPawnOperation.tbxServiceFee.Text; //this.lblServiceFeeChinese.Text = newChinese.toChineseChar(Convert.ToDecimal(frmRenewPawnOperation.tbxServiceFee.Text)); //this.lblPaidInterestFee.Text = frmRenewPawnOperation.lblPaidInterest.Text; //this.lblPaidInterestFeeChinese.Text = newChinese.toChineseChar(Convert.ToDecimal(frmRenewPawnOperation.lblPaidInterest.Text)); r = ws.get_Range(("M" + 6), ("M" + 6)); r.Value2 = TotalAmount.ToString("0.00"); r = ws.get_Range(("H" + 6), ("H" + 6)); r.Value2 = newChinese.toChineseChar(Convert.ToDecimal(TotalAmount)); r = ws.get_Range(("M" + 7), ("M" + 7)); r.Value2 = TotalServiceFee.ToString("0.00"); r = ws.get_Range(("H" + 7), ("H" + 7)); r.Value2 = newChinese.toChineseChar(Convert.ToDecimal(TotalServiceFee)); r = ws.get_Range(("M" + 8), ("M" + 8)); r.Value2 = TotalLastInterestFee.ToString("0.00"); r = ws.get_Range(("H" + 8), ("H" + 8)); r.Value2 = newChinese.toChineseChar(Convert.ToDecimal(TotalLastInterestFee)); double totalPaidFee = TotalServiceFee + TotalLastInterestFee; //this.lblPaidFee.Text = totalPaidFee.ToString(); //this.lblPaidFeeChinese.Text = newChinese.toChineseChar(Convert.ToDecimal(totalPaidFee)); r = ws.get_Range(("H" + 9), ("H" + 9)); r.Value2 = newChinese.toChineseChar(Convert.ToDecimal(totalPaidFee)); r = ws.get_Range(("M" + 9), ("M" + 9)); r.Value2 = totalPaidFee.ToString("0.00"); DateTime startDate = Convert.ToDateTime(dtOperation.Rows[0]["StartDate"].ToString()); DateTime endDate = Convert.ToDateTime(dtOperation.Rows[0]["EndDate"].ToString()); DateTime operationDate = Convert.ToDateTime(dtOperation.Rows[0]["OperationDate"].ToString()); //this.lblStartYear.Text = startDate.Year.ToString(); //this.lblStartMonth.Text = startDate.Month.ToString(); //this.lblStartDay.Text = startDate.Day.ToString(); //this.lblEndYear.Text = endDate.Year.ToString(); //this.lblEndMonth.Text = endDate.Month.ToString(); //this.lblEndDay.Text = endDate.Day.ToString(); //this.lblOperationYear.Text = operationDate.Year.ToString(); //this.lblOperationMonth.Text = operationDate.Month.ToString(); //this.lblOperationDay.Text = operationDate.Day.ToString(); r = ws.get_Range(("F" + 10), ("F" + 10)); r.Value2 = startDate.Year.ToString(); r = ws.get_Range(("I" + 10), ("I" + 10)); r.Value2 = startDate.Month.ToString(); r = ws.get_Range(("J" + 10), ("J" + 10)); r.Value2 = startDate.Day.ToString(); r = ws.get_Range(("K" + 10), ("K" + 10)); r.Value2 = endDate.Year.ToString(); r = ws.get_Range(("L" + 10), ("L" + 10)); r.Value2 = endDate.Month.ToString(); r = ws.get_Range(("M" + 10), ("M" + 10)); r.Value2 = endDate.Day.ToString(); r = ws.get_Range(("M" + 13), ("M" + 13)); r.Value2 = operationDate.Year.ToString(); r = ws.get_Range(("O" + 13), ("O" + 13)); r.Value2 = operationDate.Month.ToString(); r = ws.get_Range(("Q" + 13), ("Q" + 13)); r.Value2 = operationDate.Day.ToString(); double serviceFeeRate = Convert.ToDouble(dtOperation.Rows[0]["ServiceFeeRate"].ToString()); double interestRate = Convert.ToDouble(dtOperation.Rows[0]["InterestFeeRate"].ToString()); //this.lblMonthFeeRate.Text = serviceFeeRate.ToString(); //this.lblInterestFeeRate.Text = interestRate.ToString(); //this.lblOperater.Text = MainForm.AccountName; r = ws.get_Range(("D" + 11), ("D" + 11)); r.Value2 = serviceFeeRate.ToString(); r = ws.get_Range(("D" + 12), ("D" + 12)); r.Value2 = interestRate.ToString(); DDUser newUser = new DDUser("AccountName", MainForm.AccountName); r = ws.get_Range(("I" + 13), ("I" + 13)); r.Value2 = newUser.UserName; //WdSaveFormat为Excel文档的保存格式 object format = MSExcel.XlFileFormat.xlWorkbookNormal; //将excelDoc文档对象的内容保存为dd文档 //excelDoc.Save(); //excelDoc.SaveAs(path, format, Nothing, Nothing, Nothing, Nothing, MSExcel.ddaveAsAccessMode.xlExclusive, Nothing, Nothing, Nothing, Nothing, Nothing); excelDoc.PrintOut(Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing); if (File.Exists(Application.StartupPath + @"\Pram\dump.dd")) { try { File.Delete(Application.StartupPath + @"\Pram\dump.dd"); } catch (FieldAccessException e) { MessageBox.Show("记录文件正在被使用,请退出Excel\n" + e.ToString()); } } excelDoc.SaveAs(Application.StartupPath + @"\Pram\dump.dd", format, Nothing, Nothing, Nothing, Nothing, MSExcel.XlSaveAsAccessMode.xlExclusive, Nothing, Nothing, Nothing, Nothing, Nothing); //关闭excelDoc文档对象 excelDoc.Close(Nothing, Nothing, Nothing); //关闭excelApp组件对象 excelApp.Quit(); //MessageBox.Show("生成成功"); //PrintReceipt(); }
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); } } } }
private void UpdateOperation() { try { if (dataGridView1.Rows.Count > 0) { DDPawnTicket newTicket = new DDPawnTicket("TicketID", m_TicketID); newTicket.StatusID = 3; //3:续当 newTicket.EndDate = this.tbxEndDate.Text; newTicket.Save(); int iPawnageID = 0; int preOperationID = 0; PawnSpan Span = new PawnSpan(); Span = DianDangFunction.GetPawnSpan(DateTime.Parse(tbxStartDate.Text), DateTime.Parse(tbxEndDate.Text)); for (int i = dataGridView1.Rows.Count - 1; i > -1; i--) { DataGridViewRow dgr = dataGridView1.Rows[i]; iPawnageID = Convert.ToInt32(dgr.Cells["PawnageID"].Value); DDPawnageInfo newInfo = new DDPawnageInfo("PawnageID", iPawnageID); preOperationID = Convert.ToInt32(dgr.Cells["OperationID"].Value); DDOperation newOperation = new DDOperation(); newOperation.TicketID = m_TicketID; newOperation.PawnageID = iPawnageID; newOperation.OperationType = 3; //3 :续当 newOperation.OperationNumber = m_OperationNumber; newOperation.ServiceFee = dataGridView1.Rows[i].Cells["ServiceFee"].Value.ToString(); newOperation.InterestFee = dataGridView1.Rows[i].Cells["InterestFee"].Value.ToString(); newOperation.ReturnFee = "0"; newOperation.OverdueFee = "0"; newOperation.Amount = dgr.Cells["Amount"].Value.ToString(); newOperation.ReckonAmount = "0"; newOperation.OperationDate = this.tbxOperationDate.Text; newOperation.StartDate = this.tbxStartDate.Text; newOperation.EndDate = this.tbxEndDate.Text; DDUser newOperater = new DDUser("AccountName", MainForm.AccountName); newOperation.OperaterName = newOperater.UserName; newOperation.PreOperationID = preOperationID; newOperation.ServiceFeeRate = dgr.Cells["FeeRate"].Value.ToString(); newOperation.InterestFeeRate = dgr.Cells["InterestRate"].Value.ToString(); newOperation.NextOperationID = 0; newOperation.Deleted = 0; newOperation.Save(); DDOperation oldOperation = new DDOperation("OperationID", preOperationID); oldOperation.NextOperationID = newOperation.OperationID; oldOperation.Save(); newInfo.FeeRate = dgr.Cells["FeeRate"].Value.ToString(); newInfo.InterestRate = dgr.Cells["InterestRate"].Value.ToString(); newInfo.EndDate = this.tbxEndDate.Text; newInfo.StatusID = 3; //续当 newInfo.Save(); } } } catch (Exception ex) { throw ex; } }
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 LoadTemplate(int iTicketID, string strOperationNumber) { double TotalAmount = 0; double TotalServiceFee = 0; double TotalLastInterestFee = 0; double TotalReturnFee = 0; double TotalOverdueFee = 0; double FirstTotalServiceFee = 0; double FirstTotalAmount = 0; List <string> strList = new List <string>(); DDPawnTicket newTicket = new DDPawnTicket("TicketID", m_TicketID); DDCustomerInfo newCustomer = new DDCustomerInfo("CustomerID", newTicket.CustomerID); Query queryOperation = new Query(DDOperation.Schema); queryOperation.AddWhere("TicketID", m_TicketID); queryOperation.AddWhere("OperationNumber", strOperationNumber); queryOperation.AddWhere("OperationType", Comparison.NotEquals, 6); DataTable dtOperation = queryOperation.ExecuteDataSet().Tables[0]; for (int i = 0; i < dtOperation.Rows.Count; i++) { TotalAmount += Convert.ToDouble(dtOperation.Rows[i]["Amount"].ToString()); TotalServiceFee += Convert.ToDouble(dtOperation.Rows[i]["ServiceFee"].ToString()); TotalLastInterestFee += Convert.ToDouble(dtOperation.Rows[i]["InterestFee"].ToString()); TotalReturnFee += Convert.ToDouble(dtOperation.Rows[i]["ReturnFee"].ToString()); TotalOverdueFee += Convert.ToDouble(dtOperation.Rows[i]["OverdueFee"].ToString()); } TotalAmount = DianDangFunction.myRound(TotalAmount, MainForm.AmountAccuracy); TotalServiceFee = DianDangFunction.myRound(TotalServiceFee, MainForm.AmountAccuracy); TotalLastInterestFee = DianDangFunction.myRound(TotalLastInterestFee, MainForm.AmountAccuracy); TotalReturnFee = DianDangFunction.myRound(TotalReturnFee, MainForm.AmountAccuracy); TotalOverdueFee = DianDangFunction.myRound(TotalOverdueFee, MainForm.AmountAccuracy); queryOperation = new Query(DDOperation.Schema); queryOperation.AddWhere("TicketID", m_TicketID); queryOperation.AddWhere("PreOperationID", 0); queryOperation.AddWhere("OperationType", Comparison.NotEquals, 6); dtOperation = queryOperation.ExecuteDataSet().Tables[0]; for (int i = 0; i < dtOperation.Rows.Count; i++) { FirstTotalAmount += Convert.ToDouble(dtOperation.Rows[i]["Amount"].ToString()); FirstTotalServiceFee += Convert.ToDouble(dtOperation.Rows[i]["ServiceFee"].ToString()); } FirstTotalAmount = DianDangFunction.myRound(FirstTotalAmount, MainForm.AmountAccuracy); FirstTotalServiceFee = DianDangFunction.myRound(FirstTotalServiceFee, MainForm.AmountAccuracy); MSExcel.Application excelApp; //Excel应用程序变量 MSExcel.Workbook excelDoc; //Excel文档变量 excelApp = new MSExcel.ApplicationClass(); //初始化 //由于使用的是COM库,因此有许多变量需要用Nothing代替 Object Nothing = Missing.Value; excelDoc = excelApp.Workbooks._Open(Application.StartupPath + @"\Pram\RedeemTemplate_TongBao.dd", Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value); //使用第一个工作表作为插入数据的工作表 MSExcel.Worksheet ws = (MSExcel.Worksheet)excelDoc.Sheets[1]; //声明一个MSExcel.Range 类型的变量r MSExcel.Range r; DDCompanyInfo company = new DDCompanyInfo("CompanyID", 1); //r = ws.get_Range(("F" + 4), ("F" + 4)); r.Value2 = company.CompanyName; r = ws.get_Range(("K" + 5), ("K" + 5)); r.Value2 = newTicket.TicketNumber.ToString(); r = ws.get_Range(("D" + 5), ("D" + 5)); r.Value2 = newCustomer.CustomerName; //TotalAmount: string strTotalMount = TotalAmount.ToString("0.00"); r = ws.get_Range(("D" + 7), ("D" + 7)); r.Value2 = newChinese.toChineseChar(strTotalMount); r = ws.get_Range(("M" + 7), ("M" + 7)); r.Value2 = strTotalMount; //Fee string strFirstGive = (FirstTotalAmount - FirstTotalServiceFee).ToString("0.00"); r = ws.get_Range(("E" + 13), ("E" + 13)); r.Value2 = newChinese.toChineseChar(strFirstGive); r = ws.get_Range(("L" + 13), ("L" + 13)); r.Value2 = strFirstGive; string strFirstServiceFee = FirstTotalServiceFee.ToString("0.00"); r = ws.get_Range(("E" + 15), ("E" + 15)); r.Value2 = newChinese.toChineseChar(strFirstServiceFee); r = ws.get_Range(("L" + 15), ("L" + 15)); r.Value2 = strFirstServiceFee; string strOverdue = (Math.Abs(TotalLastInterestFee + TotalOverdueFee - TotalReturnFee + TotalServiceFee)).ToString("0.00"); r = ws.get_Range(("E" + 17), ("E" + 17)); r.Value2 = newChinese.toChineseChar(strOverdue); r = ws.get_Range(("L" + 17), ("L" + 17)); r.Value2 = strOverdue; string strAll = (TotalAmount + TotalLastInterestFee + TotalOverdueFee + TotalServiceFee - TotalReturnFee).ToString("0.00"); strList = newChinese.toStrList(strAll, 5, 2); r = ws.get_Range(("D" + 19), ("D" + 19)); r.Value2 = strList[0]; r = ws.get_Range(("E" + 19), ("E" + 19)); r.Value2 = strList[1]; r = ws.get_Range(("G" + 19), ("G" + 19)); r.Value2 = strList[2]; r = ws.get_Range(("H" + 19), ("H" + 19)); r.Value2 = strList[3]; r = ws.get_Range(("I" + 19), ("I" + 19)); r.Value2 = strList[4]; r = ws.get_Range(("J" + 19), ("J" + 19)); r.Value2 = strList[5]; r = ws.get_Range(("L" + 19), ("L" + 19)); r.Value2 = strList[6]; r = ws.get_Range(("N" + 19), ("N" + 19)); r.Value2 = strAll; //日期 DDOperation ThisOperation = new DDOperation("OperationNumber", strOperationNumber); DDOperation LastOperation = new DDOperation("OperationID", ThisOperation.PreOperationID); DateTime operationDate = Convert.ToDateTime(ThisOperation.OperationDate); DateTime lastStartDate = Convert.ToDateTime(LastOperation.StartDate); DateTime lastEndDate = Convert.ToDateTime(LastOperation.EndDate); r = ws.get_Range(("C" + 9), ("C" + 9)); r.Value2 = lastStartDate.Year.ToString(); r = ws.get_Range(("E" + 9), ("E" + 9)); r.Value2 = lastStartDate.Month.ToString(); r = ws.get_Range(("G" + 9), ("G" + 9)); r.Value2 = lastStartDate.Day.ToString(); r = ws.get_Range(("I" + 9), ("I" + 9)); r.Value2 = lastEndDate.Year.ToString(); r = ws.get_Range(("J" + 9), ("J" + 9)); r.Value2 = lastEndDate.Month.ToString(); r = ws.get_Range(("L" + 9), ("L" + 9)); r.Value2 = lastEndDate.Day.ToString(); r = ws.get_Range(("B" + 11), ("B" + 11)); r.Value2 = operationDate.Year.ToString(); r = ws.get_Range(("D" + 11), ("D" + 11)); r.Value2 = operationDate.Month.ToString(); r = ws.get_Range(("G" + 11), ("G" + 11)); r.Value2 = operationDate.Day.ToString(); r = ws.get_Range(("L" + 3), ("L" + 3)); r.Value2 = operationDate.Year.ToString(); r = ws.get_Range(("N" + 3), ("N" + 3)); r.Value2 = operationDate.Month.ToString(); r = ws.get_Range(("O" + 3), ("O" + 3)); r.Value2 = operationDate.Day.ToString(); //double serviceFeeRate = Convert.ToDouble(dtOperation.Rows[0]["ServiceFeeRate"].ToString()); //double interestRate = Convert.ToDouble(dtOperation.Rows[0]["InterestFeeRate"].ToString()); //r = ws.get_Range(("D" + 11), ("D" + 11)); r.Value2 = serviceFeeRate.ToString(); //r = ws.get_Range(("D" + 12), ("D" + 12)); r.Value2 = interestRate.ToString(); DDUser newUser = new DDUser("AccountName", MainForm.AccountName); r = ws.get_Range(("K" + 21), ("K" + 21)); r.Value2 = newUser.UserName; //WdSaveFormat为Excel文档的保存格式 object format = MSExcel.XlFileFormat.xlWorkbookNormal; //将excelDoc文档对象的内容保存为dd文档 //excelDoc.Save(); //excelDoc.SaveAs(path, format, Nothing, Nothing, Nothing, Nothing, MSExcel.ddaveAsAccessMode.xlExclusive, Nothing, Nothing, Nothing, Nothing, Nothing); excelDoc.PrintOut(Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing); if (File.Exists(Application.StartupPath + @"\Pram\dump.dd")) { try { File.Delete(Application.StartupPath + @"\Pram\dump.dd"); } catch (FieldAccessException e) { MessageBox.Show("记录文件正在被使用,请退出Excel\n" + e.ToString()); } } excelDoc.SaveAs(Application.StartupPath + @"\Pram\dump.dd", format, Nothing, Nothing, Nothing, Nothing, MSExcel.XlSaveAsAccessMode.xlExclusive, Nothing, Nothing, Nothing, Nothing, Nothing); //关闭excelDoc文档对象 excelDoc.Close(Nothing, Nothing, Nothing); //关闭excelApp组件对象 excelApp.Quit(); //MessageBox.Show("生成成功"); //PrintReceipt(); }