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 btnUpdate_Click(object sender, EventArgs e) { for (int i = 0; i < dataGridView1.RowCount; i++) { if (this.dataGridView1.Rows[i].Cells["DataSelect"].Value != null && this.dataGridView1.Rows[i].Cells["DataSelect"].Value.ToString() == "1") { dataGridView1.Rows[i].Selected = true; } else { dataGridView1.Rows[i].Selected = false; } } if (dataGridView1.SelectedRows.Count > 0) { for (int i = dataGridView1.SelectedRows.Count - 1; i > -1; i--) { int intCustomerID = Convert.ToInt32(dataGridView1.SelectedRows[i].Cells["CustomerID"].Value); DDCustomerInfo newCustomer = new DDCustomerInfo("CustomerID", intCustomerID); newCustomer.CustomerName = dataGridView1.SelectedRows[i].Cells["CustomerName"].Value.ToString(); newCustomer.PhoneNumber = dataGridView1.SelectedRows[i].Cells["PhoneNumber"].Value.ToString(); newCustomer.Address = dataGridView1.SelectedRows[i].Cells["Address"].Value.ToString(); newCustomer.ContactPerson = dataGridView1.SelectedRows[i].Cells["ContactPerson"].Value.ToString(); newCustomer.CertTypeID = Convert.ToInt32(dataGridView1.SelectedRows[i].Cells["CertTypeID"].Value); newCustomer.CertNumber = dataGridView1.SelectedRows[i].Cells["CertNumber"].Value.ToString();; newCustomer.Save(); } MessageBox.Show("数据更新成功!"); } else { MessageBox.Show("请选择需要更新的数据!"); } }
private void InitGridSource() { m_GridTable.Rows.Clear(); Query query = new Query(DDPawnTicket.Schema); string strKey = "%" + this.tbxKeyWord.Text + "%"; query.AddWhere("TicketNumber", Comparison.Like, strKey); query.AddWhere("StatusID", Comparison.NotEquals, 6); DataTable dt = query.ExecuteDataSet().Tables[0]; if (dt.Rows.Count > 0) { for (int i = 0; i < dt.Rows.Count; i++) { 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["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 LoadCustomerInfo(int intCustomerID) { DDCustomerInfo newCustomer = new DDCustomerInfo("CustomerID", intCustomerID); this.tbxCustomerName.Text = newCustomer.CustomerName; this.tbxCustomerPhone.Text = newCustomer.PhoneNumber; this.tbxCustomerAdd.Text = newCustomer.Address; this.tbxContactPerson.Text = newCustomer.ContactPerson; this.cbxCertType.SelectedValue = newCustomer.CertTypeID; this.tbxCertNum.Text = newCustomer.CertNumber; }
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 QueryByNumber() { try { DDCustomerInfo newInfo = new DDCustomerInfo("CertNumber", this.tbxCardNumber.Text.Trim()); m_CustomerID = newInfo.CustomerID; InitGridSource(); } catch { MessageBox.Show("不存在改客户", "提示信息"); } }
public void Insert(string CustomerName, string PhoneNumber, string Address, string ContactPerson, int?CertTypeID, string CertNumber) { DDCustomerInfo item = new DDCustomerInfo(); item.CustomerName = CustomerName; item.PhoneNumber = PhoneNumber; item.Address = Address; item.ContactPerson = ContactPerson; item.CertTypeID = CertTypeID; item.CertNumber = CertNumber; item.Save(UserName); }
public void Update(int CustomerID, string CustomerName, string PhoneNumber, string Address, string ContactPerson, int?CertTypeID, string CertNumber) { DDCustomerInfo item = new DDCustomerInfo(); item.MarkOld(); item.IsLoaded = true; item.CustomerID = CustomerID; item.CustomerName = CustomerName; item.PhoneNumber = PhoneNumber; item.Address = Address; item.ContactPerson = ContactPerson; item.CertTypeID = CertTypeID; item.CertNumber = CertNumber; item.Save(UserName); }
private void InitGridSource() { DDCustomerInfo customer = new DDCustomerInfo("CustomerID", m_CustomerID); Query query = new Query(DDPawnTicket.Schema); query.AddWhere("CustomerID", customer.CustomerID); DataTable dt = query.ExecuteDataSet().Tables[0]; this.dataGridView1.Rows.Clear(); if (dt.Rows.Count > 0) { for (int i = 0; i < dt.Rows.Count; i++) { this.dataGridView1.Rows.Add(1); dataGridView1.Rows[dataGridView1.Rows.Count - 1].Cells["CustomerName"].Value = customer.CustomerName; dataGridView1.Rows[dataGridView1.Rows.Count - 1].Cells["TicketID"].Value = dt.Rows[i]["TicketID"]; dataGridView1.Rows[dataGridView1.Rows.Count - 1].Cells["TicketNumber"].Value = dt.Rows[i]["TicketNumber"]; dataGridView1.Rows[dataGridView1.Rows.Count - 1].Cells["StartDate"].Value = dt.Rows[i]["StartDate"]; dataGridView1.Rows[dataGridView1.Rows.Count - 1].Cells["EndDate"].Value = dt.Rows[i]["EndDate"]; dataGridView1.Rows[dataGridView1.Rows.Count - 1].Cells["StatusID"].Value = dt.Rows[i]["StatusID"]; } } }
private int UpdateCustomerInfo() { Query query = new Query(DDCustomerInfo.Schema); query.AddWhere("CertNUmber", this.tbxCertNum.Text); DataTable dt = query.ExecuteDataSet().Tables[0]; if (dt.Rows.Count == 0) { DDCustomerInfo newCustomer = new DDCustomerInfo(); newCustomer.CustomerName = this.tbxCustomerName.Text; newCustomer.PhoneNumber = this.tbxCustomerPhone.Text; newCustomer.Address = this.tbxCustomerAdd.Text; newCustomer.ContactPerson = this.tbxContactPerson.Text; newCustomer.CertTypeID = Convert.ToInt32(this.cbxCertType.SelectedValue); newCustomer.CertNumber = this.tbxCertNum.Text; newCustomer.Save(); return(newCustomer.CustomerID); } else { return(Convert.ToInt32(dt.Rows[0]["CustomerID"])); } }
public bool Destroy(object CustomerID) { return(DDCustomerInfo.Destroy(CustomerID) == 1); }
public bool Delete(object CustomerID) { return(DDCustomerInfo.Delete(CustomerID) == 1); }
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 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 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(); }
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 LoadTemplate(RenewPawnOperationForm frmRenewPawnOperation, int iTicketID) { 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 = frmRenewPawnOperation.lblOldTicketNum.Text; m_TicketID = iTicketID; DDPawnTicket newTicket = new DDPawnTicket("TicketID", iTicketID); 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 = Convert.ToDouble(frmRenewPawnOperation.tbxTotalAmount.Text).ToString("0.00"); r = ws.get_Range(("H" + 6), ("H" + 6)); r.Value2 = newChinese.toChineseChar(Convert.ToDecimal(frmRenewPawnOperation.tbxTotalAmount.Text)); r = ws.get_Range(("M" + 7), ("M" + 7)); r.Value2 = Convert.ToDouble(frmRenewPawnOperation.tbxServiceFee.Text).ToString("0.00"); r = ws.get_Range(("H" + 7), ("H" + 7)); r.Value2 = newChinese.toChineseChar(Convert.ToDecimal(frmRenewPawnOperation.tbxServiceFee.Text)); r = ws.get_Range(("M" + 8), ("M" + 8)); r.Value2 = Convert.ToDouble(frmRenewPawnOperation.tbxLastInterestFee.Text).ToString("0.00"); r = ws.get_Range(("H" + 8), ("H" + 8)); r.Value2 = newChinese.toChineseChar(Convert.ToDecimal(frmRenewPawnOperation.tbxLastInterestFee.Text)); double totalPaidFee = Convert.ToDouble(frmRenewPawnOperation.tbxServiceFee.Text) + Convert.ToDouble(frmRenewPawnOperation.tbxLastInterestFee.Text); //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(frmRenewPawnOperation.tbxStartDate.Text); DateTime endDate = Convert.ToDateTime(frmRenewPawnOperation.tbxEndDate.Text); DateTime operationDate = Convert.ToDateTime(frmRenewPawnOperation.tbxOperationDate.Text); //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(frmRenewPawnOperation.dataGridView1.Rows[0].Cells["FeeRate"].Value); double interestRate = Convert.ToDouble(frmRenewPawnOperation.dataGridView1.Rows[0].Cells["InterestRate"].Value); //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 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()); }