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