public SearchCustomerForm()
{
InitializeComponent();
InitGridDataset();
this.monthCalendar1.Visible = false;
this.ShowDetail.Text = "查看历史";
#region Check Permission
DDUser user = new DDUser("AccountName", MainForm.AccountName);
DDModule module = new DDModule("ModuleName", "修改客户信息");
Query query = new Query(DDPermission.Schema);
query.AddWhere("RoleID", user.RoleID);
query.AddWhere("ModuleID", module.ModuleID);
DataTable dt = query.ExecuteDataSet().Tables[0];
if (dt.Rows.Count > 0)
{
this.btnDelete.Visible = true;
this.btnUpdate.Visible = true;
this.DataSelect.Visible = true;
}
else
{
this.btnDelete.Visible = false;
this.btnUpdate.Visible = false;
this.DataSelect.Visible = false;
}
#endregion
InitCustomerSearchOption();
}
public void Update(int UserID, string AccountName, string UserPassword, string UserName, int?RoleID, string Email, string PhoneNumber, string CertNumber)
{
DDUser item = new DDUser();
item.MarkOld();
item.IsLoaded = true;
item.UserID = UserID;
item.AccountName = AccountName;
item.UserPassword = UserPassword;
item.UserName = UserName;
item.RoleID = RoleID;
item.Email = Email;
item.PhoneNumber = PhoneNumber;
item.CertNumber = CertNumber;
item.Save(UserName);
}
private void btnPrint_Click(object sender, EventArgs e)
{
DataGridView DataSource = new DataGridView();
List <int> Columns = new List <int>();
string Title = "";
DataSource = dataGridView1;
DataSource.DataSource = dgvPage.GetData();
Title += "查询状态：" + this.cbxSearchRange.Text;
Title += " ";
Title += "查询日期：" + DateTime.Now.ToShortDateString();
Title += " ";
Title += "共计" + DataSource.Rows.Count.ToString() + "张当票";
Title += " ";
DDUser newUser = new DDUser("AccountName", MainForm.AccountName);
Title += "查询人：" + newUser.UserName;
Columns.Add(dataGridView1.Columns["TicketNumber"].Index);
Columns.Add(dataGridView1.Columns["CustomerName"].Index);
Columns.Add(dataGridView1.Columns["StartDate"].Index);
Columns.Add(dataGridView1.Columns["EndDate"].Index);
Columns.Add(dataGridView1.Columns["TotalAmount"].Index);
Columns.Add(dataGridView1.Columns["TotalCount"].Index);
Columns.Add(dataGridView1.Columns["StatusID"].Index);
PrintDataGrid frmPrintDataGrid = new PrintDataGrid(Title, DataSource, Columns);
SetForGridViewPage();
}
private void LoadAccountInfo()
{
DDUser newUser = new DDUser("AccountName", MainForm.AccountName);
this.tbxUserName.Text = newUser.UserName;
this.tbxPhoneNumber.Text = newUser.PhoneNumber;
this.tbxEmail.Text = newUser.Email;
this.tbxCertNumber.Text = newUser.CertNumber;
this.tbxRoleName.Text = newUser.RoleID.ToString();
}
private void UpdateOperation(int iTicketID)
{
try
{
Query query = new Query(DDOperation.Schema);
query.AddWhere("TicketID", iTicketID);
query.AddWhere("OperationType", Comparison.In, new int[] { 1, 3 });
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++)
{
DDOperation oldOperation = new DDOperation("OperationID", Convert.ToInt32(dt.Rows[i]["OperationID"].ToString()));
DDOperation newOperation = new DDOperation();
int dataGridRow = 0;
for (int j = 0; j < dataGridView1.Rows.Count; j++)
{
if (dataGridView1.Rows[j].Cells["PawnageID"].Value.ToString() == oldOperation.PawnageID.ToString())
{
dataGridRow = j;
}
}
newOperation.TicketID = m_TicketID;
newOperation.PawnageID = Convert.ToInt32(dataGridView1.Rows[dataGridRow].Cells["PawnageID"].Value.ToString());
newOperation.OperationType = 2;
newOperation.OperationNumber = m_OperationNumber;
newOperation.ServiceFee = "0";
newOperation.InterestFee = (Convert.ToDouble(dataGridView1.Rows[dataGridRow].Cells["PaidInterestFee"].Value.ToString()) - Convert.ToDouble(dataGridView1.Rows[dataGridRow].Cells["ReturnInterestFee"].Value.ToString())).ToString();
newOperation.ReturnFee = dataGridView1.Rows[dataGridRow].Cells["ReturnFee"].Value.ToString();
newOperation.OverdueFee = dataGridView1.Rows[dataGridRow].Cells["OverdueFee"].Value.ToString();
newOperation.Amount = dataGridView1.Rows[dataGridRow].Cells["Amount"].Value.ToString();
newOperation.ReckonAmount = "0";
newOperation.ServiceFeeRate = dataGridView1.Rows[dataGridRow].Cells["ServiceFeeRate"].Value.ToString();
newOperation.InterestFeeRate = dataGridView1.Rows[dataGridRow].Cells["InterestFeeRate"].Value.ToString();
newOperation.OperationDate = tbxOperationDate.Text;
DDUser newUser = new DDUser("AccountName", MainForm.AccountName);
newOperation.OperaterName = newUser.UserName;
newOperation.PreOperationID = oldOperation.OperationID;
newOperation.NextOperationID = 0;
newOperation.Save();
oldOperation.NextOperationID = newOperation.OperationID;
oldOperation.Save();
DDPawnageInfo newInfo = new DDPawnageInfo("PawnageID", Convert.ToInt32(dataGridView1.Rows[dataGridRow].Cells["PawnageID"].Value.ToString()));
newInfo.StatusID = 2; //赎当
newInfo.Save();
}
}
catch (Exception ex)
{
throw ex;
}
}
private void btnUpdate_Click(object sender, EventArgs e)
{
try
{
DDUser newUser = new DDUser("AccountName", MainForm.AccountName);
newUser.UserName = this.tbxUserName.Text;
newUser.PhoneNumber = this.tbxPhoneNumber.Text;
newUser.Email = this.tbxEmail.Text;
newUser.CertNumber = this.tbxCertNumber.Text;
newUser.Save();
MessageBox.Show(this, "信息修改成功！", "提示信息", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
catch
{
MessageBox.Show(this, "信息修改失败,请检查数据库连接是否正确！", "提示信息", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
private void btnAdd_Click(object sender, EventArgs e)
{
if (CheckAllDate() != DialogResult.OK)
{
return;
}
else
{
Query query = new Query(DDUser.Schema);
query.AddWhere("AccountName", this.tbxAccountName.Text);
DataTable dt = query.ExecuteDataSet().Tables[0];
if (dt.Rows.Count > 0)
{
MessageBox.Show(this, "该帐户已存在，请重新输入！", "提示信息", MessageBoxButtons.OK, MessageBoxIcon.Information);
this.tbxAccountName.Focus();
return;
}
try
{
this.dataGridView1.Rows.Add(1);
dataGridView1.Rows[dataGridView1.Rows.Count - 1].Cells["AccountName"].Value = this.tbxAccountName.Text;
dataGridView1.Rows[dataGridView1.Rows.Count - 1].Cells["UserPassword"].Value = this.tbxPassword.Text;
dataGridView1.Rows[dataGridView1.Rows.Count - 1].Cells["UserName"].Value = this.tbxUserName.Text;
dataGridView1.Rows[dataGridView1.Rows.Count - 1].Cells["PhoneNumber"].Value = this.tbxPhoneNumber.Text;
dataGridView1.Rows[dataGridView1.Rows.Count - 1].Cells["Email"].Value = this.tbxEmail.Text;
dataGridView1.Rows[dataGridView1.Rows.Count - 1].Cells["CertNumber"].Value = this.tbxCertNumber.Text;
dataGridView1.Rows[dataGridView1.Rows.Count - 1].Cells["RoleID"].Value = this.cbxRoleID.SelectedValue;
DDUser newUser = new DDUser();
newUser.AccountName = this.tbxAccountName.Text;
newUser.UserPassword = this.tbxPassword.Text;
newUser.UserName = this.tbxUserName.Text;
newUser.PhoneNumber = this.tbxPhoneNumber.Text;
newUser.Email = this.tbxEmail.Text;
newUser.CertNumber = this.tbxCertNumber.Text;
newUser.RoleID = Convert.ToInt32(this.cbxRoleID.SelectedValue);
newUser.Save();
}
catch
{
MessageBox.Show(this, "数据添加失败,请检查数据库是否连接正确？", "提示信息", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
}
private DialogResult CheckPermission(string strModuleName)
{
DDUser user = new DDUser("AccountName", MainForm.AccountName);
DDModule module = new DDModule("ModuleName", strModuleName);
Query query = new Query(DDPermission.Schema);
query.AddWhere("RoleID", user.RoleID);
query.AddWhere("ModuleID", module.ModuleID);
DataTable dt = query.ExecuteDataSet().Tables[0];
if (dt.Rows.Count > 0)
{
return(DialogResult.OK);
}
else
{
return(DialogResult.No);
}
}
private void btnEditPwd_Click(object sender, EventArgs e)
{
if (this.tbxOldPassword.Text.Trim().Length == 0)
{
MessageBox.Show(this, "请输入原密码！", "提示信息", MessageBoxButtons.OK, MessageBoxIcon.Information);
this.tbxOldPassword.Focus();
return;
}
if (this.tbxNewPassword.Text.Trim().Length == 0)
{
MessageBox.Show(this, "请输入新密码！", "提示信息", MessageBoxButtons.OK, MessageBoxIcon.Information);
this.tbxNewPassword.Focus();
return;
}
if (this.tbxRepeatedPassword.Text.Trim().Length == 0)
{
MessageBox.Show(this, "请重复输入新密码！", "提示信息", MessageBoxButtons.OK, MessageBoxIcon.Information);
this.tbxRepeatedPassword.Focus();
return;
}
if (this.tbxNewPassword.Text.Trim() != this.tbxRepeatedPassword.Text.Trim())
{
MessageBox.Show(this, "两次输入的密码不一致！", "提示信息", MessageBoxButtons.OK, MessageBoxIcon.Information);
this.tbxRepeatedPassword.Focus();
return;
}
DDUser newUser = new DDUser("AccountName", MainForm.AccountName);
if (this.tbxOldPassword.Text.Trim() != newUser.UserPassword)
{
MessageBox.Show(this, "原密码输入错误！", "提示信息", MessageBoxButtons.OK, MessageBoxIcon.Information);
this.tbxOldPassword.Focus();
return;
}
else
{
newUser.UserPassword = this.tbxNewPassword.Text.Trim();
newUser.Save();
MessageBox.Show(this, "密码修改成功！", "提示信息", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
public void Insert(string AccountName, string UserPassword, string UserName, int?RoleID, string Email, string PhoneNumber, string CertNumber)
{
DDUser item = new DDUser();
item.AccountName = AccountName;
item.UserPassword = UserPassword;
item.UserName = UserName;
item.RoleID = RoleID;
item.Email = Email;
item.PhoneNumber = PhoneNumber;
item.CertNumber = CertNumber;
item.Save(UserName);
}
private void btnPrint_Click(object sender, EventArgs e)
{
//if (SetupThePrinting())
//{
// MyPrintDocument.Print();
//}
DataGridView DataSource = new DataGridView();
List <int> Columns = new List <int>();
string Title = "";
DataSource = dataGridView1;
Title += "查询日期：" + DateTime.Now.ToShortDateString();
Title += " ";
DDUser newUser = new DDUser("AccountName", MainForm.AccountName);
Title += "查询人：" + newUser.UserName;
Columns.Add(dataGridView1.Columns["ClassName"].Index);
Columns.Add(dataGridView1.Columns["ServiceFee"].Index);
Columns.Add(dataGridView1.Columns["ReturnFee"].Index);
Columns.Add(dataGridView1.Columns["OverdueFee"].Index);
Columns.Add(dataGridView1.Columns["InterestFee"].Index);
Columns.Add(dataGridView1.Columns["TotalFee"].Index);
Columns.Add(dataGridView1.Columns["Reckoning"].Index);
if (Type == 0)
{
PrintDataGrid frmPrintDataGrid = new PrintDataGrid(Title, DataSource, Columns, "QueryByTaking1.dd");
}
else if (Type == 1)
{
PrintDataGrid frmPrintDataGrid = new PrintDataGrid(Title, DataSource, Columns, "QueryByTaking2.dd");
}
}
private void btnUpdate_Click(object sender, EventArgs e)
{
if (CheckAllDate() != DialogResult.OK)
{
return;
}
else
{
try
{
DataGridViewRow dgr = dataGridView1.CurrentRow;
dgr.Cells["AccountName"].Value = this.tbxAccountName.Text;
dgr.Cells["UserPassword"].Value = this.tbxPassword.Text;
dgr.Cells["UserName"].Value = this.tbxUserName.Text;
dgr.Cells["PhoneNumber"].Value = this.tbxPhoneNumber.Text;
dgr.Cells["Email"].Value = this.tbxEmail.Text;
dgr.Cells["CertNumber"].Value = this.tbxCertNumber.Text;
dgr.Cells["RoleID"].Value = this.cbxRoleID.SelectedValue;
DDUser newUser = new DDUser("UserID", Convert.ToInt32(dgr.Cells["UserID"].Value));
newUser.AccountName = this.tbxAccountName.Text;
newUser.UserPassword = this.tbxPassword.Text;
newUser.UserName = this.tbxUserName.Text;
newUser.PhoneNumber = this.tbxPhoneNumber.Text;
newUser.Email = this.tbxEmail.Text;
newUser.CertNumber = this.tbxCertNumber.Text;
newUser.RoleID = Convert.ToInt32(this.cbxRoleID.SelectedValue);
newUser.Save();
MessageBox.Show("数据修改成功！", "修改数据");
}
catch
{
MessageBox.Show(this, "数据修改失败,请检查数据库是否连接正确！", "提示信息", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
}
private void btnPrint_Click(object sender, EventArgs e)
{
DataGridView DataSource = new DataGridView();
List <int> Columns = new List <int>();
string Title = "";
DataSource = dataGridView1;
DataSource.DataSource = dgvPage.GetData().ToTable();
Title += "查询状态：" + this.cbxSearchRange.Text;
Title += " ";
Title += "查询日期：" + DateTime.Now.ToShortDateString();
Title += " ";
Title += "共计" + DataSource.Rows.Count.ToString() + "件当品";
Title += " ";
DDUser newUser = new DDUser("AccountName", MainForm.AccountName);
Title += "查询人：" + newUser.UserName;
Columns.Add(dataGridView1.Columns["TicketNumber"].Index);
Columns.Add(dataGridView1.Columns["FirstClass"].Index);
Columns.Add(dataGridView1.Columns["SecondClass"].Index);
Columns.Add(dataGridView1.Columns["Description"].Index);
Columns.Add(dataGridView1.Columns["Weight"].Index);
Columns.Add(dataGridView1.Columns["TotalCount"].Index);
Columns.Add(dataGridView1.Columns["Price"].Index);
Columns.Add(dataGridView1.Columns["FeeRate"].Index);
Columns.Add(dataGridView1.Columns["InterestRate"].Index);
Columns.Add(dataGridView1.Columns["Remark"].Index);
PrintDataGrid frmPrintDataGrid = new PrintDataGrid(Title, DataSource, Columns);
SetForGridViewPage();
}
public bool Destroy(object UserID)
{
return(DDUser.Destroy(UserID) == 1);
}
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 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 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;
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 UpdateOperation()
{
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];
try
{
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
{
MessageBox.Show(this, "续当操作失败，请检查数据库是否连接正确！", "提示信息", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
}
}
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();
}
public bool Delete(object UserID)
{
return(DDUser.Delete(UserID) == 1);
}
private void LoadTemplate(int iTicketID, string strOperationNumber)
{
string thePrice;
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\ReceiptTemplate.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;
DDPawnTicket newTicket = new DDPawnTicket("TicketID", iTicketID);
switch (newTicket.StatusID)
{
case 1:
r = ws.get_Range(("B" + 3), ("B" + 3)); r.Value2 = "新当";
break;
case 2:
r = ws.get_Range(("B" + 3), ("B" + 3)); r.Value2 = "赎当";
break;
case 3:
r = ws.get_Range(("B" + 3), ("B" + 3)); r.Value2 = "续当";
break;
default:
break;
}
r = ws.get_Range(("C" + 4), ("C" + 4)); r.Value2 = newTicket.TicketNumber;
//this.lblTicketNumber.Text = newTicket.TicketNumber;
DateTime currentDate = DateTime.Now;
string strYear = currentDate.Year.ToString();
string strMonth = currentDate.Month.ToString();
string strDay = currentDate.Day.ToString();
//this.lblYear.Text = strYear;
//this.lblMonth.Text = strMonth;
//this.lblDay.Text = strDay;
r = ws.get_Range(("V" + 4), ("V" + 4)); r.Value2 = strYear;
r = ws.get_Range(("X" + 4), ("X" + 4)); r.Value2 = strMonth;
r = ws.get_Range(("Z" + 4), ("Z" + 4)); r.Value2 = strDay;
double totalAmount = 0;
string strItem = "";
Query query = new Query(DDOperation.Schema);
query.AddWhere("TicketID", iTicketID);
query.AddWhere("OperationType", 1);
DataTable dt = query.ExecuteDataSet().Tables[0];
if (dt.Rows.Count > 0)
{
for (int i = 0; i < dt.Rows.Count; i++)
{
DDPawnageInfo newInfo = new DDPawnageInfo("PawnageID", Convert.ToInt32(dt.Rows[i]["PawnageID"]));
DDPawnageClass newClass = new DDPawnageClass("ClassID", newInfo.ClassID);
switch (i)
{
case 0:
//this.lblPawnageInfo1.Text = newClass.ClassName + dt.Rows[i]["Amount"].ToString();
strItem = newClass.ClassName;
//r = ws.get_Range(("J" + 6), ("J" + 6)); r.Value2 = newClass.ClassName + dt.Rows[i]["Amount"].ToString();
break;
case 1:
//this.lblPawnageInfo2.Text = newClass.ClassName + dt.Rows[i]["Amount"].ToString();
//r = ws.get_Range(("C" + 7), ("C" + 7)); r.Value2 = newClass.ClassName + dt.Rows[i]["Amount"].ToString();
strItem = strItem + "等";
break;
case 2:
//this.lblPawnageInfo3.Text = newClass.ClassName + dt.Rows[i]["Amount"].ToString();
//r = ws.get_Range(("C" + 8), ("C" + 8)); r.Value2 = newClass.ClassName + dt.Rows[i]["Amount"].ToString();
break;
default:
break;
}
totalAmount += Convert.ToDouble(dt.Rows[i]["Amount"]);
}
r = ws.get_Range(("C" + 6), ("C" + 6)); r.Value2 = strItem + " " + totalAmount;
}
Query queryCurrentOperation = new Query(DDOperation.Schema);
queryCurrentOperation.AddWhere("OperationNumber", strOperationNumber);
DataTable dtCurrentOperation = queryCurrentOperation.ExecuteDataSet().Tables[0];
int currentOperationType = Convert.ToInt32(dtCurrentOperation.Rows[0]["OperationType"]);
double overdueFee = 0;
double returnFee = 0;
double serviceFee = 0;
double interestFee = 0;
string serviceFeeRate = "";
string interestFeeRate = "";
int preOperationID = 0;
if (dtCurrentOperation.Rows.Count > 0)
{
serviceFeeRate = dtCurrentOperation.Rows[0]["ServiceFeeRate"].ToString();
preOperationID = Convert.ToInt32(dtCurrentOperation.Rows[0]["PreOperationID"]);
if (preOperationID != 0)
{
DDOperation preOperation = new DDOperation("OperationID", preOperationID);
//interestFee += Convert.ToDouble(preOperation.InterestFee);
interestFeeRate = preOperation.InterestFeeRate;
}
for (int i = 0; i < dtCurrentOperation.Rows.Count; i++)
{
overdueFee += Convert.ToDouble(dtCurrentOperation.Rows[i]["OverdueFee"]);
returnFee += Convert.ToDouble(dtCurrentOperation.Rows[i]["ReturnFee"]);
serviceFee += Convert.ToDouble(dtCurrentOperation.Rows[i]["ServiceFee"]);
interestFee += Convert.ToDouble(dtCurrentOperation.Rows[i]["interestFee"]);
}
}
if (currentOperationType != 1)
{
r = ws.get_Range(("H" + 8), ("H" + 8)); r.Value2 = "上期利息: " + interestFeeRate.ToString() + "%";
//r = ws.get_Range(("I" + 8), ("I" + 8)); r.Value2 = "%";
if (interestFee != 0)
{
thePrice = interestFee.ToString("0.00");
thePrice = thePrice.Substring(0, thePrice.Length - 3) + thePrice.Substring(thePrice.Length - 2);
}
else
{
thePrice = "000";
}
if (thePrice.Length < 8)
{
for (int j = (8 - thePrice.Length); j > 0; j--)
{
thePrice = " " + thePrice;
}
}
r = ws.get_Range(("M" + 8), ("M" + 8)); r.Value2 = thePrice.Substring(0, 1);
r = ws.get_Range(("N" + 8), ("N" + 8)); r.Value2 = thePrice.Substring(1, 1);
r = ws.get_Range(("O" + 8), ("O" + 8)); r.Value2 = thePrice.Substring(2, 1);
r = ws.get_Range(("P" + 8), ("P" + 8)); r.Value2 = thePrice.Substring(3, 1);
r = ws.get_Range(("Q" + 8), ("Q" + 8)); r.Value2 = thePrice.Substring(4, 1);
r = ws.get_Range(("R" + 8), ("R" + 8)); r.Value2 = thePrice.Substring(5, 1);
r = ws.get_Range(("S" + 8), ("S" + 8)); r.Value2 = thePrice.Substring(6, 1);
r = ws.get_Range(("T" + 8), ("T" + 8)); r.Value2 = thePrice.Substring(7, 1);
}
double paidFee = 0;
if (currentOperationType != 2)
{
if (currentOperationType == 1)
{
r = ws.get_Range(("H" + 10), ("H" + 10)); r.Value2 = "新当服务费: " + serviceFee.ToString("0.00");
paidFee = serviceFee;
}
else
{
r = ws.get_Range(("H" + 10), ("H" + 10)); r.Value2 = "续当服务费: " + serviceFee.ToString("0.00");
paidFee = serviceFee + interestFee;
}
//r = ws.get_Range(("H" + 12), ("H" + 12)); r.Value2 ="收："+ newChinese.toChineseChar(Convert.ToDecimal(serviceFee));
r = ws.get_Range(("H" + 12), ("H" + 12)); r.Value2 = "" + newChinese.toChineseChar(Convert.ToDecimal(paidFee));
if (serviceFee != 0)
{
thePrice = serviceFee.ToString("0.00");
thePrice = thePrice.Substring(0, thePrice.Length - 3) + thePrice.Substring(thePrice.Length - 2);
}
else
{
thePrice = "000";
}
if (thePrice.Length < 8)
{
for (int j = (8 - thePrice.Length); j > 0; j--)
{
thePrice = " " + thePrice;
}
}
r = ws.get_Range(("M" + 10), ("M" + 10)); r.Value2 = thePrice.Substring(0, 1);
r = ws.get_Range(("N" + 10), ("N" + 10)); r.Value2 = thePrice.Substring(1, 1);
r = ws.get_Range(("O" + 10), ("O" + 10)); r.Value2 = thePrice.Substring(2, 1);
r = ws.get_Range(("P" + 10), ("P" + 10)); r.Value2 = thePrice.Substring(3, 1);
r = ws.get_Range(("Q" + 10), ("Q" + 10)); r.Value2 = thePrice.Substring(4, 1);
r = ws.get_Range(("R" + 10), ("R" + 10)); r.Value2 = thePrice.Substring(5, 1);
r = ws.get_Range(("S" + 10), ("S" + 10)); r.Value2 = thePrice.Substring(6, 1);
r = ws.get_Range(("T" + 10), ("T" + 10)); r.Value2 = thePrice.Substring(7, 1);
if (paidFee != 0)
{
thePrice = paidFee.ToString("0.00");
thePrice = thePrice.Substring(0, thePrice.Length - 3) + thePrice.Substring(thePrice.Length - 2);
}
else
{
thePrice = "000";
}
if (thePrice.Length < 8)
{
for (int j = (8 - thePrice.Length); j > 0; j--)
{
thePrice = " " + thePrice;
}
}
r = ws.get_Range(("M" + 12), ("M" + 12)); r.Value2 = thePrice.Substring(0, 1);
r = ws.get_Range(("N" + 12), ("N" + 12)); r.Value2 = thePrice.Substring(1, 1);
r = ws.get_Range(("O" + 12), ("O" + 12)); r.Value2 = thePrice.Substring(2, 1);
r = ws.get_Range(("P" + 12), ("P" + 12)); r.Value2 = thePrice.Substring(3, 1);
r = ws.get_Range(("Q" + 12), ("Q" + 12)); r.Value2 = thePrice.Substring(4, 1);
r = ws.get_Range(("R" + 12), ("R" + 12)); r.Value2 = thePrice.Substring(5, 1);
r = ws.get_Range(("S" + 12), ("S" + 12)); r.Value2 = thePrice.Substring(6, 1);
r = ws.get_Range(("T" + 12), ("T" + 12)); r.Value2 = thePrice.Substring(7, 1);
}
if (overdueFee > 0)
{
//this.lblFee.Text = "逾期服务费" + overdueFee.ToString();
//this.lblTotalFee.Text = "收：" + newChinese.toChineseChar(Convert.ToDecimal(overdueFee));
//this.lblTotalFeeNumber.Text = FormatString(overdueFee.ToString("0.00"));
//this.lblFeeNumber.Text = FormatString(overdueFee.ToString("0.00"));
r = ws.get_Range(("H" + 10), ("H" + 10)); r.Value2 = "逾期服务费: " + overdueFee.ToString("0.00");
//r = ws.get_Range(("H" + 12), ("H" + 12)); r.Value2 = "收：" + newChinese.toChineseChar(Convert.ToDecimal(overdueFee));
paidFee = overdueFee + interestFee;
r = ws.get_Range(("H" + 12), ("H" + 12)); r.Value2 = newChinese.toChineseChar(Convert.ToDecimal(paidFee));
thePrice = overdueFee.ToString("0.00");
thePrice = thePrice.Substring(0, thePrice.Length - 3) + thePrice.Substring(thePrice.Length - 2);
if (thePrice.Length < 8)
{
for (int j = (8 - thePrice.Length); j > 0; j--)
{
thePrice = " " + thePrice;
}
}
r = ws.get_Range(("M" + 10), ("M" + 10)); r.Value2 = thePrice.Substring(0, 1);
r = ws.get_Range(("N" + 10), ("N" + 10)); r.Value2 = thePrice.Substring(1, 1);
r = ws.get_Range(("O" + 10), ("O" + 10)); r.Value2 = thePrice.Substring(2, 1);
r = ws.get_Range(("P" + 10), ("P" + 10)); r.Value2 = thePrice.Substring(3, 1);
r = ws.get_Range(("Q" + 10), ("Q" + 10)); r.Value2 = thePrice.Substring(4, 1);
r = ws.get_Range(("R" + 10), ("R" + 10)); r.Value2 = thePrice.Substring(5, 1);
r = ws.get_Range(("S" + 10), ("S" + 10)); r.Value2 = thePrice.Substring(6, 1);
r = ws.get_Range(("T" + 10), ("T" + 10)); r.Value2 = thePrice.Substring(7, 1);
thePrice = paidFee.ToString("0.00");
thePrice = thePrice.Substring(0, thePrice.Length - 3) + thePrice.Substring(thePrice.Length - 2);
if (thePrice.Length < 8)
{
for (int j = (8 - thePrice.Length); j > 0; j--)
{
thePrice = " " + thePrice;
}
}
r = ws.get_Range(("M" + 12), ("M" + 12)); r.Value2 = thePrice.Substring(0, 1);
r = ws.get_Range(("N" + 12), ("N" + 12)); r.Value2 = thePrice.Substring(1, 1);
r = ws.get_Range(("O" + 12), ("O" + 12)); r.Value2 = thePrice.Substring(2, 1);
r = ws.get_Range(("P" + 12), ("P" + 12)); r.Value2 = thePrice.Substring(3, 1);
r = ws.get_Range(("Q" + 12), ("Q" + 12)); r.Value2 = thePrice.Substring(4, 1);
r = ws.get_Range(("R" + 12), ("R" + 12)); r.Value2 = thePrice.Substring(5, 1);
r = ws.get_Range(("S" + 12), ("S" + 12)); r.Value2 = thePrice.Substring(6, 1);
r = ws.get_Range(("T" + 12), ("T" + 12)); r.Value2 = thePrice.Substring(7, 1);
}
if (returnFee > 0)
{
//this.lblFee.Text = "返回服务费" + returnFee.ToString();
//this.lblTotalFee.Text = "退：" + newChinese.toChineseChar(Convert.ToDecimal(returnFee));
//this.lblTotalFeeNumber.Text = FormatString(returnFee.ToString("0.00"));
//this.lblFeeNumber.Text = FormatString(returnFee.ToString("0.00"));
r = ws.get_Range(("H" + 10), ("H" + 10)); r.Value2 = "返回服务费: " + returnFee.ToString("0.00");
paidFee = interestFee - returnFee;
r = ws.get_Range(("H" + 12), ("H" + 12)); r.Value2 = "退: " + newChinese.toChineseChar(Convert.ToDecimal(paidFee));
thePrice = returnFee.ToString("0.00");
thePrice = thePrice.Substring(0, thePrice.Length - 3) + thePrice.Substring(thePrice.Length - 2);
thePrice = "-" + thePrice;
if (thePrice.Length < 8)
{
for (int j = (8 - thePrice.Length); j > 0; j--)
{
thePrice = " " + thePrice;
}
}
r = ws.get_Range(("M" + 10), ("M" + 10)); r.Value2 = thePrice.Substring(0, 1);
r = ws.get_Range(("N" + 10), ("N" + 10)); r.Value2 = thePrice.Substring(1, 1);
r = ws.get_Range(("O" + 10), ("O" + 10)); r.Value2 = thePrice.Substring(2, 1);
r = ws.get_Range(("P" + 10), ("P" + 10)); r.Value2 = thePrice.Substring(3, 1);
r = ws.get_Range(("Q" + 10), ("Q" + 10)); r.Value2 = thePrice.Substring(4, 1);
r = ws.get_Range(("R" + 10), ("R" + 10)); r.Value2 = thePrice.Substring(5, 1);
r = ws.get_Range(("S" + 10), ("S" + 10)); r.Value2 = thePrice.Substring(6, 1);
r = ws.get_Range(("T" + 10), ("T" + 10)); r.Value2 = thePrice.Substring(7, 1);
thePrice = paidFee.ToString("0.00");
thePrice = thePrice.Substring(0, thePrice.Length - 3) + thePrice.Substring(thePrice.Length - 2);
if (thePrice.Length < 8)
{
for (int j = (8 - thePrice.Length); j > 0; j--)
{
thePrice = " " + thePrice;
}
}
r = ws.get_Range(("M" + 12), ("M" + 12)); r.Value2 = thePrice.Substring(0, 1);
r = ws.get_Range(("N" + 12), ("N" + 12)); r.Value2 = thePrice.Substring(1, 1);
r = ws.get_Range(("O" + 12), ("O" + 12)); r.Value2 = thePrice.Substring(2, 1);
r = ws.get_Range(("P" + 12), ("P" + 12)); r.Value2 = thePrice.Substring(3, 1);
r = ws.get_Range(("Q" + 12), ("Q" + 12)); r.Value2 = thePrice.Substring(4, 1);
r = ws.get_Range(("R" + 12), ("R" + 12)); r.Value2 = thePrice.Substring(5, 1);
r = ws.get_Range(("S" + 12), ("S" + 12)); r.Value2 = thePrice.Substring(6, 1);
r = ws.get_Range(("T" + 12), ("T" + 12)); r.Value2 = thePrice.Substring(7, 1);
}
Query queryAll = new Query(DDOperation.Schema);
queryAll.AddWhere("TicketID", iTicketID);
queryAll.AddWhere("NextOperationID", 0);
queryAll.AddWhere("OperationType", Comparison.In, new int[] { 1, 2, 3 });
DataTable dtAll = queryAll.ExecuteDataSet().Tables[0];
double totalOverdueFee = 0;
double totalReturnFee = 0;
double totalServiceFee = 0;
double totalInterestFee = 0;
if (dtAll.Rows.Count > 0)
{
for (int i = 0; i < dtAll.Rows.Count; i++)
{
totalOverdueFee += Convert.ToDouble(dtAll.Rows[i]["OverdueFee"]);
totalReturnFee += Convert.ToDouble(dtAll.Rows[i]["ReturnFee"]);
totalServiceFee += Convert.ToDouble(dtAll.Rows[i]["ServiceFee"]);
totalInterestFee += Convert.ToDouble(dtAll.Rows[i]["InterestFee"]);
}
}
if (currentOperationType == 2)
{
totalAmount = totalAmount + totalOverdueFee + totalInterestFee - totalReturnFee;
r = ws.get_Range(("C" + 11), ("C" + 11)); r.Value2 = "总计交收金额: " + totalAmount.ToString("0.00");
}
DDUser newUser = new DDUser("AccountName", MainForm.AccountName);
//r = ws.get_Range(("F" + 14), ("F" + 14)); r.Value2 = newUser.UserName;
r = ws.get_Range(("F" + 14), ("F" + 14)); r.Value2 = dtAll.Rows[dtAll.Rows.Count - 1]["OperaterName"].ToString();
//this.lblTotalAmount.Text = totalAmount.ToString();
//this.lblOperater.Text = MainForm.AccountName;
//WdSaveFormat为Excel文档的保存格式
object format = MSExcel.XlFileFormat.xlWorkbookNormal;
//打印文档
excelDoc.PrintOut(Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing);
//将excelDoc文档对象的内容保存为dd文档
//excelDoc.Save();
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("生成成功");
}