private void DDCompanyInfo()
{
string SetupDate = CompanyInfo_SetupDate.ToShortDateString();
SetupDate = SetupDate.Replace('/', '-');
int SqlExecResult = 0;
string SqlExecString = "";
DBFConnection.SetFile(this.DBFPath + "CONFIG.DBF");
DBFConnection.Connect();
DBFResult = DBFConnection.Query("select CO_NAME, PHONE, XKZ_ID, ADDRESS, M_STATION, M_NAME, FIX, CONTENTS from CONFIG.DBF");
DBFData = DBFResult.GetResult();
DBFData.Read();
SqlExecString = "insert into DDCompanyInfo (CompanyName, AmountAccuracy, PhoneNumber, LicenseCode, Address, SubCompanyNumber, SubCompanyName, Postalcode, ShopHours, StartTicketNumber, SetupDate, CountDayMode)"
+ "VALUES ("
+ "'" + CutSpace(DBFData[0].ToString()) + "', "
+ "'" + this.numericUpDown1.Value.ToString() + "', "
+ "'" + CutSpace(DBFData[1].ToString()) + "', "
+ "'" + CutSpace(DBFData[2].ToString()) + "', "
+ "'" + CutSpace(DBFData[3].ToString()) + "', "
+ "'" + CutSpace(DBFData[4].ToString()) + "', "
+ "'" + CutSpace(DBFData[5].ToString()) + "', "
+ "'" + CutSpace(DBFData[6].ToString()) + "', "
+ "'" + CutSpace(DBFData[7].ToString()) + "', "
+ "'" + CompanyInfo_StartTicketNumber + "', "
+ "'" + SetupDate + "', "
+ "'" + "0" + "')";
SqlExecResult = SqlConnection.Exec(SqlExecString);
DBFData.Close();
DBFConnection.DisConnect();
}
private void ReadDBFUserInfo()
{
DBFConnection.SetFile(this.DBFPath + "OPERATOR.DBF");
DBFConnection.Connect();
DBFResult = DBFConnection.Query("select OPERATOR, NAME from OPERATOR.DBF");
DBFData = DBFResult.GetResult();
while (DBFData.Read())
{
UserInfo theUser = new UserInfo();
theUser.AccountName = CutSpace(DBFData[0].ToString());
theUser.UserName = CutSpace(DBFData[1].ToString());
UserList.Add(theUser);
}
DBFData.Close();
DBFConnection.DisConnect();
}
private void DDMesureUnit()
{
int SqlExecResult = 0;
string SqlExecString = "";
DBFConnection.SetFile(this.DBFPath + "UNITTYPE.DBF");
DBFConnection.Connect();
DBFResult = DBFConnection.Query("select UNITNAME from UNITTYPE.DBF");
DBFData = DBFResult.GetResult();
while (DBFData.Read())
{
SqlExecString = "insert into DDMesureUnit (UnitName) VALUES ('" + CutSpace(DBFData[0].ToString()) + "')";
SqlExecResult = SqlConnection.Exec(SqlExecString);
}
DBFData.Close();
DBFConnection.DisConnect();
SqlExecResult = SqlConnection.Exec("Insert into DDMesureUnit (UnitName) VALUES ('其它')");
}
private void DDStatisticClass()
{
int SqlExecResutl = 0;
string SqlExecString = "";
DBFConnection.SetFile(this.DBFPath + "D1_CLASS.DBF");
DBFConnection.Connect();
DBFResult = DBFConnection.Query("select D1_Name from D1_CLASS.DBF");
DBFData = DBFResult.GetResult();
while (DBFData.Read())
{
SqlExecString = "Insert into DDStatisticClass (ClassName) VALUES ('" + CutSpace(DBFData[0].ToString()) + "')";
SqlExecResutl = SqlConnection.Exec(SqlExecString);
}
DBFData.Close();
DBFConnection.DisConnect();
SqlExecString = "Insert into DDStatisticClass (ClassName) VALUES ('其它')";
SqlExecResutl = SqlConnection.Exec(SqlExecString);
}
private void SaveUserInfo()
{
double TotalAmount = 0;
DateTime StartDate = new DateTime();
DateTime EndDate = new DateTime();
#region 保存信息
File.Copy(Application.StartupPath + @"\User.xls", Application.StartupPath + @"\UU.xls");
File.Delete(Application.StartupPath + @"\User.xls");
MSExcel.Application excelApp; //Excel应用程序变量
MSExcel.Workbook excelDoc; //Excel文档变量
excelApp = new MSExcel.ApplicationClass(); //初始化
//由于使用的是COM库，因此有许多变量需要用Nothing代替
Object Nothing = Missing.Value;
excelDoc = excelApp.Workbooks._Open(Application.StartupPath + @"\UU.xls", 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;
int ExcelLine = 1;
r = ws.get_Range(("A" + 1), ("A" + 1));
while (r.Text.ToString() != "")
{
ExcelLine++;
r = ws.get_Range(("A" + ExcelLine), ("A" + ExcelLine));
}
SqlResult = SqlConnection.Query("select CustomerName, PhoneNumber, Address, ContactPerson, CertTypeID, CertNumber from DDCustomerInfo");
SqlData = SqlResult.GetResult();
while (SqlData.Read())
{
TotalAmount = 0;
r = ws.get_Range(("A" + ExcelLine), ("A" + ExcelLine)); r.Value2 = SqlData[0].ToString();
r = ws.get_Range(("B" + ExcelLine), ("B" + ExcelLine)); r.Value2 = SqlData[1].ToString();
r = ws.get_Range(("C" + ExcelLine), ("C" + ExcelLine)); r.Value2 = SqlData[2].ToString();
r = ws.get_Range(("D" + ExcelLine), ("D" + ExcelLine)); r.Value2 = SqlData[3].ToString();
r = ws.get_Range(("E" + ExcelLine), ("E" + ExcelLine)); r.Value2 = GetCertTypeNameByTypeID(Convert.ToInt32(SqlData[4].ToString()));
r = ws.get_Range(("F" + ExcelLine), ("F" + ExcelLine)); r.Value2 = SqlData[5].ToString();
DBFConnection.SetFile(this.DBFPath + @"CUST_RCD.DBF");
DBFConnection.Connect();
DBFResult = DBFConnection.Query("select D_JE, DATE, END_DATE from CUST_RCD.DBF where NAME='" + SqlData[0].ToString() + "'");
DBFData = DBFResult.GetResult();
while (DBFData.Read())
{
TotalAmount = TotalAmount + Convert.ToDouble(DBFData[0].ToString());
StartDate = DateTime.Parse(DBFData[1].ToString());
EndDate = DateTime.Parse(DBFData[2].ToString());
}
DBFData.Close();
DBFConnection.DisConnect();
r = ws.get_Range(("G" + ExcelLine), ("G" + ExcelLine)); r.Value2 = TotalAmount;
r = ws.get_Range(("H" + ExcelLine), ("H" + ExcelLine)); r.Value2 = StartDate.ToShortDateString();
r = ws.get_Range(("I" + ExcelLine), ("I" + ExcelLine)); r.Value2 = EndDate.ToShortDateString();
ExcelLine++;
}
SqlData.Close();
object format = MSExcel.XlFileFormat.xlWorkbookNormal;
excelDoc.SaveAs(Application.StartupPath + @"\User.xls", format, Nothing, Nothing, Nothing, Nothing, MSExcel.XlSaveAsAccessMode.xlExclusive, Nothing, Nothing, Nothing, Nothing, Nothing);
excelDoc.Close(Nothing, Nothing, Nothing);
excelApp.Quit();
File.Delete(Application.StartupPath + @"\UU.xls");
#endregion
}
private void DDCustomerInfo()
{
int SqlExecResult = 0;
string SqlExecString = "";
#region 从CUST_RCD当票信息中插入客户
DBFConnection.SetFile(this.DBFPath + @"CUST_RCD.DBF");
DBFConnection.Connect();
DBFResult = DBFConnection.Query("select NAME, MARK, ADDRESS, S_NO, SFZ_ID, DATE from CUST_RCD.DBF order by DATE");
DBFData = DBFResult.GetResult();
while (DBFData.Read())
{
if (isExistCustomerInSQL(CutSpace(DBFData[0].ToString()), CutSpace(DBFData[4].ToString())) == false)
{
SqlExecString = "insert into DDCustomerInfo (CustomerName, PhoneNumber, Address, CertTypeID, CertNumber, CreatDate) "
+ "VALUES ( "
+ "'" + CutSpace(DBFData[0].ToString()) + "', "
+ "'" + CutSpace(DBFData[1].ToString()) + "', "
+ "'" + CutSpace(DBFData[2].ToString()) + "', "
+ GetSqlCertIDbyDBFCertID(CutSpace(DBFData[3].ToString())) + ", "
+ "'" + CutSpace(DBFData[4].ToString()) + "', "
+ "'" + ChangeDateFormat(DateTime.Parse(DBFData[5].ToString()).ToShortDateString()) + "')";
SqlExecResult = SqlConnection.Exec(SqlExecString);
}
}
DBFData.Close();
DBFConnection.DisConnect();
#endregion
#region 从CUST_CNT中插入身份证客户
DBFConnection.SetFile(this.DBFPath + @"CUST_CNT.DBF");
DBFConnection.Connect();
DBFResult = DBFConnection.Query("select distinct NAME, MARK, ADDRESS, SFZ_ID, ST_DATE from CUST_CNT.DBF order by ST_DATE");
DBFData = DBFResult.GetResult();
while (DBFData.Read())
{
if (isExistCustomerInSQL(CutSpace(DBFData[0].ToString()), CutSpace(DBFData[3].ToString())) == false)
{
int CertID = GetCertIDByName("身份证");
SqlExecString = "insert into DDCustomerInfo (CustomerName, PhoneNumber, Address, CertTypeID, CertNumber) "
+ "VALUES ( "
+ "'" + CutSpace(DBFData[0].ToString()) + "', "
+ "'" + CutSpace(DBFData[1].ToString()) + "', "
+ "'" + CutSpace(DBFData[2].ToString()) + "', "
+ CertID.ToString() + ", "
+ "'" + CutSpace(DBFData[3].ToString()) + "', "
+ "'" + ChangeDateFormat(DateTime.Parse(DBFData[4].ToString()).ToShortDateString()) + "')";
SqlExecResult = SqlConnection.Exec(SqlExecString);
}
}
DBFData.Close();
DBFConnection.DisConnect();
#endregion
#region 插入公司信息
DBFConnection.SetFile(this.DBFPath + "UNITINFO.DBF");
DBFConnection.Connect();
DBFResult = DBFConnection.Query("select distinct NAME, PHONE, ADDRESS, UNIT_ID, B_NO from UNITINFO.DBF");
DBFData = DBFResult.GetResult();
while (DBFData.Read())
{
if (isExistCustomerInSQL(CutSpace(DBFData[0].ToString()), CutSpace(DBFData[3].ToString())) == false)
{
SqlExecString = "insert into DDCustomerInfo (CustomerName, PhoneNumber, Address, ContactPerson, CertTypeID, CertNumber) "
+ "VALUES ( "
+ "'" + CutSpace(DBFData[0].ToString()) + "', "
+ "'" + CutSpace(DBFData[1].ToString()) + "', "
+ "'" + CutSpace(DBFData[2].ToString()) + "', "
+ "'" + GetContactNamebyB_NO(CutSpace(DBFData[4].ToString())) + "', "
+ "'" + GetCertIDByName("营业执照") + "', "
+ "'" + CutSpace(DBFData[3].ToString()) + "')";
SqlExecResult = SqlConnection.Exec(SqlExecString);
}
}
DBFData.Close();
DBFConnection.DisConnect();
#endregion
}
private void PawnOperatTicket()
{
//ReadTicket
int SqlExecResult = 0;
string SqlExecString = "";
int NumofTickets = 0;
int TicketNow = 0;
string DateNone = "1899/12/300:00:00";
#region Tick参数定义区域
int TicketID = 0;
int CustomerID = 0;
string TicketNumber = "";
string TicketStartDate = "";
string TicketEndDate = "";
int TicketStatusID = 0;
double TicketFeeRate = 0;
double TicketInterestRate = 0;
string TicketRemark = "";
double TicketAmount = 0;
int TicketOperationStep = 0;
#endregion
#region 当品参数区域
MyDBFConnection DBFConnectionP = new MyDBFConnection();
DBFQueryResult DBFResultP = new DBFQueryResult();
OleDbDataReader DBFDataP;
PawnInfo thePawnage;
List <PawnInfo> PawnList = new List <PawnInfo>();
#endregion
#region 操作参数区域
MyDBFConnection DBFConnectionO = new MyDBFConnection();
DBFQueryResult DBFResultO = new DBFQueryResult();
OleDbDataReader DBFDataO;
int OperationType = 0;
int NextOperationType = 0;
double OperationServiceFee = 0;
double NextOperationServiceFee = 0;
double OperationInterestFee = 0;
double NextOperationInterestFee = 0;
double OperationReturnFee = 0;
double NextOperationReturnFee = 0;
double OperationOverdueFee = 0;
double NextOperationOverdueFee = 0;
double ReckonAmount = 0;
double NextReckonAmount = 0;
string OperationDate = "";
string NextOperationDate = "";
string OperationStartDate = "";
string NextOperationStartDate = "";
string OperationEndDate = "";
string NextOperationEndDate = "";
string OperatorName = "";
string NextOperatorName = "";
double Fee = 0;
double NextFee = 0;
int LastOperationID = 0;
int NewOperationID = 0;
double RestOperationServiceFee = 0;
double RestOperationInterestFee = 0;
double RestOperationReturnFee = 0;
double RestOperationOverdueFee = 0;
#endregion
#region 读取当票总数
DBFConnection.SetFile(this.DBFPath + "CUST_RCD.DBF");
DBFConnection.Connect();
DBFResult = DBFConnection.Query("select count(Name) from CUST_RCD.DBF");
DBFData = DBFResult.GetResult();
while (DBFData.Read())
{
NumofTickets = Convert.ToInt32(DBFData[0].ToString());
}
DBFData.Close();
DBFConnection.DisConnect();
this.MainProgressBar.Maximum = NumofTickets;
#endregion
DBFConnection.SetFile(this.DBFPath + "CUST_RCD.DBF");
DBFConnection.Connect();
DBFResult = DBFConnection.Query("select NAME, SFZ_ID, B_NO, DATE, END_DATE, FREE1, FREE2, ABSTRACT, D_JE from CUST_RCD.DBF");
DBFData = DBFResult.GetResult();
while (DBFData.Read())
{
int stop = 0;
if (DBFData[2].ToString() == "11025189")
{
stop++;
}
#region 存取当票信息
TicketOperationStep = 0;
CustomerID = GetSqlCustomerIDbyNameCertNumber(CutSpace(DBFData[0].ToString()), CutSpace(DBFData[1].ToString()));
TicketNumber = CutSpace(DBFData[2].ToString());
TicketStartDate = ChangeDateFormat(DateTime.Parse(DBFData[3].ToString()).ToShortDateString());
TicketEndDate = ChangeDateFormat(DateTime.Parse(DBFData[4].ToString()).ToShortDateString());
TicketStatusID = GetSqlTicketStatusIDbyDBFB_NO(CutSpace(DBFData[2].ToString()));
TicketFeeRate = Convert.ToDouble(CutSpace(DBFData[5].ToString()));
TicketInterestRate = Convert.ToDouble(CutSpace(DBFData[6].ToString()));
TicketRemark = CutSpace(DBFData[7].ToString());
TicketAmount = Convert.ToDouble(CutSpace(DBFData[8].ToString()));
SqlExecString = "insert into DDPawnTicket (CustomerID, TicketNumber, StartDate, EndDate, StatusID) VALUES("
+ "'" + CustomerID.ToString() + "', "
+ "'" + TicketNumber + "', "
+ "'" + TicketStartDate + "', "
+ "'" + TicketEndDate + "', "
+ "'" + TicketStatusID.ToString() + "')";
SqlExecResult = SqlConnection.Exec(SqlExecString);
SqlResult = SqlConnection.Query("select TicketID from DDPawnTicket where "
+ "CustomerID='" + CustomerID.ToString() + "' and "
+ "TicketNumber='" + TicketNumber + "' and "
+ "StartDate='" + TicketStartDate + "' and "
+ "EndDate='" + TicketEndDate + "'");
SqlData = SqlResult.GetResult();
SqlData.Read();
TicketID = Convert.ToInt32(SqlData[0].ToString());
SqlData.Close();
#endregion
TicketNow++;
this.MainProgressBar.Value = TicketNow;
#region 存取当品信息表
DBFConnectionP.SetFile(this.DBFPath + "DDRECORD.DBF");
DBFConnectionP.Connect();
DBFResultP = DBFConnectionP.Query("select TYPE_NO, SL, ZL, D_JE, ABSTRACT from DDRECORD.DBF where B_NO='" + TicketNumber + "'");
DBFDataP = DBFResultP.GetResult();
while (DBFDataP.Read())
{
thePawnage = new PawnInfo();
thePawnage.ClassID = GetClassIDByDD_TYPE(CutSpace(DBFDataP[0].ToString()));
thePawnage.ParentID = GetParentIDByDD_TYPE(CutSpace(DBFDataP[0].ToString()));
thePawnage.StatusID = TicketStatusID;
thePawnage.CountNumber = Convert.ToInt32(CutSpace(DBFDataP[1].ToString()));
thePawnage.Weight = Convert.ToDouble(CutSpace(DBFDataP[2].ToString()));
thePawnage.Price = Convert.ToDouble(CutSpace(DBFDataP[3].ToString()));
thePawnage.FeeRate = TicketFeeRate;
thePawnage.InterestRate = TicketInterestRate;
if (thePawnage.Weight != 0)
{
thePawnage.Description = thePawnage.Weight.ToString() + GetUnitNameByClassID(thePawnage.ClassID);
}
else
{
thePawnage.Description = GetUnitNameByClassID(thePawnage.ClassID);
}
thePawnage.Remark = CutSpace(DBFDataP[4].ToString());
thePawnage.StartDate = TicketStartDate;
thePawnage.EndDate = TicketEndDate;
SqlExecString = "insert into DDPawnageInfo (ClassID, ParentID, StatusID, CountNumber, Weight, Price, FeeRate, InterestRate, DiscountPercent, Description, Remark, StartDate, EndDate) VALUES ("
+ "'" + thePawnage.ClassID.ToString() + "', "
+ "'" + thePawnage.ParentID.ToString() + "', "
+ "'" + thePawnage.StatusID.ToString() + "', "
+ "'" + thePawnage.CountNumber.ToString() + "', "
+ "'" + thePawnage.Weight.ToString() + "', "
+ "'" + thePawnage.Price.ToString() + "', "
+ "'" + thePawnage.FeeRate.ToString() + "', "
+ "'" + thePawnage.InterestRate.ToString() + "', "
+ "'" + "0" + "', "
+ "'" + thePawnage.Description + "', "
+ "'" + thePawnage.Remark + "', "
+ "'" + thePawnage.StartDate + "', "
+ "'" + thePawnage.EndDate + "')";
SqlExecResult = SqlConnection.Exec(SqlExecString);
SqlResult = SqlConnection.Query("select PawnageID from DDPawnageInfo where "
+ "ClassID='" + thePawnage.ClassID.ToString() + "' and "
+ "ParentID='" + thePawnage.ParentID.ToString() + "' and "
+ "StatusID='" + thePawnage.StatusID.ToString() + "' and "
+ "CountNumber='" + thePawnage.CountNumber.ToString() + "' and "
+ "Weight='" + thePawnage.Weight + "' and "
+ "Price='" + thePawnage.Price + "' and "
+ "FeeRate='" + thePawnage.FeeRate + "' and "
+ "InterestRate='" + thePawnage.InterestRate + "' and "
+ "Description='" + thePawnage.Description + "' and "
+ "Remark='" + thePawnage.Remark + "' and "
+ "StartDate='" + thePawnage.StartDate + "' and "
+ "EndDate='" + thePawnage.EndDate + "'"
);
SqlData = SqlResult.GetResult();
while (SqlData.Read())
{
thePawnage.PawnageID = Convert.ToInt32(CutSpace(SqlData[0].ToString()));
}
SqlData.Close();
thePawnage.PrecentByTicket = (thePawnage.Price) / (TicketAmount);
PawnList.Add(thePawnage);
}
DBFDataP.Close();
DBFConnectionP.DisConnect();
#endregion
#region 操作记录表
DBFConnectionO.SetFile(this.DBFPath + "OP_RCD.DBF");
DBFConnectionO.Connect();
DBFResultO = DBFConnectionO.Query("select OP_TYPE, F_JE, LX_JE, F_JE_1, DATE, SQ_DATE, END_DATE, OPERATOR from OP_RCD.DBF where B_NO='" + TicketNumber + "'");
DBFDataO = DBFResultO.GetResult();
#region 读取当前数据
if (DBFDataO.Read())
{
OperationType = GetSqlOperationTypebyDBFOP_TYPE(CutSpace(DBFDataO[0].ToString()));
if (OperationType == GetSqlOperationTypebyDBFOP_TYPE("6"))
{
ReckonAmount = Convert.ToDouble(CutSpace(DBFDataO[1].ToString()));
OperationServiceFee = 0;
}
else
{
OperationServiceFee = Convert.ToDouble(CutSpace(DBFDataO[1].ToString()));
}
OperationInterestFee = Convert.ToDouble(CutSpace(DBFDataO[2].ToString()));
Fee = Convert.ToDouble(CutSpace(DBFDataO[3].ToString()));
if (Fee > 0)
{
OperationOverdueFee = Fee;
OperationReturnFee = 0;
}
else
{
OperationOverdueFee = 0;
OperationReturnFee = 0 - Fee;
}
if (CutSpace(DBFDataO[4].ToString()) != DateNone)
{
OperationDate = ChangeDateFormat(DateTime.Parse(DBFDataO[4].ToString()).ToShortDateString());
}
else
{
OperationDate = "";
}
if (CutSpace(DBFDataO[5].ToString()) != DateNone)
{
OperationStartDate = ChangeDateFormat(DateTime.Parse(DBFDataO[5].ToString()).ToShortDateString());
}
else
{
OperationStartDate = "";
}
if (CutSpace(DBFDataO[6].ToString()) != DateNone)
{
OperationEndDate = ChangeDateFormat(DateTime.Parse(DBFDataO[6].ToString()).ToShortDateString());
}
else
{
OperationEndDate = "";
}
if (OperationStartDate == "")
{
OperationStartDate = TicketStartDate;
}
if (OperationEndDate == "")
{
OperationEndDate = TicketEndDate;
}
OperationStartDate = TicketStartDate;
OperationEndDate = TicketEndDate;
OperatorName = GetOperatorNameByDBFOperator(CutSpace(DBFDataO[7].ToString()));
}
#endregion
while (DBFDataO.Read())
{
#region 读取Next数据
NextOperationType = GetSqlOperationTypebyDBFOP_TYPE(CutSpace(DBFDataO[0].ToString()));
if (NextOperationType == GetSqlOperationTypebyDBFOP_TYPE("6"))
{
NextReckonAmount = Convert.ToDouble(CutSpace(DBFDataO[1].ToString()));
NextOperationServiceFee = 0;
}
else
{
NextOperationServiceFee = Convert.ToDouble(CutSpace(DBFDataO[1].ToString()));
}
NextOperationInterestFee = Convert.ToDouble(CutSpace(DBFDataO[2].ToString()));
NextFee = Convert.ToDouble(CutSpace(DBFDataO[3].ToString()));
if (NextFee > 0)
{
NextOperationOverdueFee = NextFee;
NextOperationReturnFee = 0;
}
else
{
NextOperationOverdueFee = 0;
NextOperationReturnFee = 0 - NextFee;
}
if (CutSpace(DBFDataO[4].ToString()) != DateNone)
{
NextOperationDate = ChangeDateFormat(DateTime.Parse(DBFDataO[4].ToString()).ToShortDateString());
}
else
{
NextOperationDate = "";
}
if (CutSpace(DBFDataO[5].ToString()) != DateNone)
{
NextOperationStartDate = ChangeDateFormat(DateTime.Parse(DBFDataO[5].ToString()).ToShortDateString());
}
else
{
NextOperationStartDate = "";
}
if (CutSpace(DBFDataO[6].ToString()) != DateNone)
{
NextOperationEndDate = ChangeDateFormat(DateTime.Parse(DBFDataO[6].ToString()).ToShortDateString());
}
else
{
NextOperationEndDate = "";
}
NextOperatorName = GetOperatorNameByDBFOperator(CutSpace(DBFDataO[7].ToString()));
#endregion
#region 当前数据与Next数据整合
//后一次又日期立即补齐
if (NextOperationStartDate != "")
{
OperationStartDate = NextOperationStartDate;
}
else
{
//如果是续当操作，则开始时间即为结束时间
if (OperationType == GetSqlOperationTypebyDBFOP_TYPE("2"))
{
OperationStartDate = OperationEndDate;
}
}
if (NextOperationEndDate != "")
{
OperationEndDate = NextOperationEndDate;
}
else
{
if (OperationType == GetSqlOperationTypebyDBFOP_TYPE("2"))
{
OperationEndDate = TicketEndDate;
}
}
#endregion
#region 将每个当品插入当前操作，存入数据库
RestOperationInterestFee = OperationInterestFee;
RestOperationOverdueFee = OperationOverdueFee;
RestOperationReturnFee = OperationReturnFee;
RestOperationServiceFee = OperationServiceFee;
for (int i = 0; i < PawnList.Count; i++)
{
LastOperationID = GetLastOperationIDbyPawnageID(PawnList[i].PawnageID);
if (i != PawnList.Count - 1)
{
SqlExecString = "insert into DDOperation (TicketID, PawnageID, OperationTYPE, OperationNumber, ServiceFee, InterestFee, ReturnFee, OverdueFee, Amount, ReckonAmount, OperationDate, StartDate, EndDate, OperaterName, PreOperationID, NextOperationID, ServiceFeeRate, InterestFeeRate) VALUES ("
+ "'" + TicketID.ToString() + "', "
+ "'" + PawnList[i].PawnageID + "', "
+ "'" + OperationType + "', "
+ "'" + GetNewOperationNumber() + "', "
+ "'" + FormatDigital((OperationServiceFee * PawnList[i].PrecentByTicket), Convert.ToInt32(numericUpDown1.Value)) + "', "
+ "'" + FormatDigital((OperationInterestFee * PawnList[i].PrecentByTicket), Convert.ToInt32(numericUpDown1.Value)) + "', "
+ "'" + FormatDigital((OperationReturnFee * PawnList[i].PrecentByTicket), Convert.ToInt32(numericUpDown1.Value)) + "', "
+ "'" + FormatDigital((OperationOverdueFee * PawnList[i].PrecentByTicket), Convert.ToInt32(numericUpDown1.Value)) + "', "
+ "'" + PawnList[i].Price + "', "
+ "'" + ReckonAmount.ToString() + "', "
+ "'" + OperationDate + "', "
+ "'" + OperationStartDate + "', "
+ "'" + OperationEndDate + "', "
+ "'" + OperatorName + "', "
+ "'" + LastOperationID + "', "
+ "'" + "0', "
+ "'" + TicketFeeRate + "', "
+ "'" + TicketInterestRate + "')";
RestOperationServiceFee = RestOperationServiceFee - FormatDigital((OperationServiceFee * PawnList[i].PrecentByTicket), Convert.ToInt32(numericUpDown1.Value));
RestOperationInterestFee = RestOperationInterestFee - FormatDigital((OperationInterestFee * PawnList[i].PrecentByTicket), Convert.ToInt32(numericUpDown1.Value));
RestOperationReturnFee = RestOperationReturnFee - FormatDigital((OperationReturnFee * PawnList[i].PrecentByTicket), Convert.ToInt32(numericUpDown1.Value));
RestOperationOverdueFee = RestOperationOverdueFee - FormatDigital((OperationOverdueFee * PawnList[i].PrecentByTicket), Convert.ToInt32(numericUpDown1.Value));
}
else
{
SqlExecString = "insert into DDOperation (TicketID, PawnageID, OperationTYPE, OperationNumber, ServiceFee, InterestFee, ReturnFee, OverdueFee, Amount, ReckonAmount, OperationDate, StartDate, EndDate, OperaterName, PreOperationID, NextOperationID, ServiceFeeRate, InterestFeeRate) VALUES ("
+ "'" + TicketID.ToString() + "', "
+ "'" + PawnList[i].PawnageID + "', "
+ "'" + OperationType + "', "
+ "'" + GetNewOperationNumber() + "', "
+ "'" + FormatDigital(RestOperationServiceFee, Convert.ToInt32(numericUpDown1.Value)) + "', "
+ "'" + FormatDigital(RestOperationInterestFee, Convert.ToInt32(numericUpDown1.Value)) + "', "
+ "'" + FormatDigital(RestOperationReturnFee, Convert.ToInt32(numericUpDown1.Value)) + "', "
+ "'" + FormatDigital(RestOperationOverdueFee, Convert.ToInt32(numericUpDown1.Value)) + "', "
+ "'" + PawnList[i].Price + "', "
+ "'" + ReckonAmount.ToString() + "', "
+ "'" + OperationDate + "', "
+ "'" + OperationStartDate + "', "
+ "'" + OperationEndDate + "', "
+ "'" + OperatorName + "', "
+ "'" + LastOperationID + "', "
+ "'" + "0', "
+ "'" + TicketFeeRate + "', "
+ "'" + TicketInterestRate + "')";
}
SqlExecResult = SqlConnection.Exec(SqlExecString);
//临时区域注意注意！！！！！
/*
* SqlResult = SqlConnection.Query("select OperationID from DDOperation where "
+ "TicketID='" + TicketID.ToString() + "' and "
+ "PawnageID='" + PawnList[i].PawnageID + "' and "
+ "OperationTYPE='" + OperationType + "' and "
+ "OperationNumber='" + GetNewOperationNumber() + "' and "
+ "ServiceFee='" + (OperationServiceFee * PawnList[i].PrecentByTicket) + "' and "
+ "InterestFee='" + (OperationInterestFee * PawnList[i].PrecentByTicket) + "' and "
+ "ReturnFee='" + (OperationReturnFee * PawnList[i].PrecentByTicket) + "' and "
+ "OverdueFee='" + (OperationOverdueFee * PawnList[i].PrecentByTicket) + "' and "
+ "Amount='" + PawnList[i].Price + "' and "
+ "ReckonAmount='" + ReckonAmount.ToString() + "' and "
+ "OperationDate='" + OperationDate + "' and "
+ "StartDate='" + OperationStartDate + "' and "
+ "EndDate='" + OperationEndDate + "' and "
+ "OperaterName='" + OperatorName + "' and "
+ "PreOperationID='" + LastOperationID + "' and "
+ "NextOperationID='" + "0'"
+ );
+
+ SqlData = SqlResult.GetResult();
+ while (SqlData.Read())
+ {
+ NewOperationID = Convert.ToInt32(CutSpace(SqlData[0].ToString()));
+ }
*/
//Replace
SqlResult = SqlConnection.Query("select OperationID from DDOperation where TicketID=" + TicketID.ToString());
SqlData = SqlResult.GetResult();
while (SqlData.Read())
{
NewOperationID = Convert.ToInt32(CutSpace(SqlData[0].ToString()));
}
SqlData.Close();
SqlExecString = "update DDOperation set NextOperationID='" + NewOperationID + "' where OperationID='" + LastOperationID + "'";
SqlExecResult = SqlConnection.Exec(SqlExecString);
}
TicketOperationStep++;
UpdateOperationNumber();
#endregion
#region Next变为当前
OperationType = NextOperationType;
OperationServiceFee = NextOperationServiceFee;
OperationInterestFee = NextOperationInterestFee;
OperationReturnFee = NextOperationReturnFee;
OperationOverdueFee = NextOperationOverdueFee;
ReckonAmount = NextReckonAmount;
OperationDate = NextOperationDate;
OperationStartDate = NextOperationStartDate;
OperationEndDate = NextOperationEndDate;
OperatorName = NextOperatorName;
Fee = NextFee;
#endregion
}
#region 将最后一次读取记录存入数据库
//如果为续当，则起始时间为上一次结束时间，结束时间为当票结束时间
if (OperationType == GetSqlOperationTypebyDBFOP_TYPE("2"))
{
OperationStartDate = OperationEndDate;
OperationEndDate = TicketEndDate;
}
RestOperationInterestFee = OperationInterestFee;
RestOperationOverdueFee = OperationOverdueFee;
RestOperationReturnFee = OperationReturnFee;
RestOperationServiceFee = OperationServiceFee;
for (int i = 0; i < PawnList.Count; i++)
{
LastOperationID = GetLastOperationIDbyPawnageID(PawnList[i].PawnageID);
if (i != PawnList.Count - 1)
{
SqlExecString = "insert into DDOperation (TicketID, PawnageID, OperationTYPE, OperationNumber, ServiceFee, InterestFee, ReturnFee, OverdueFee, Amount, ReckonAmount, OperationDate, StartDate, EndDate, OperaterName, PreOperationID, NextOperationID, ServiceFeeRate, InterestFeeRate) VALUES ("
+ "'" + TicketID.ToString() + "', "
+ "'" + PawnList[i].PawnageID + "', "
+ "'" + OperationType + "', "
+ "'" + GetNewOperationNumber() + "', "
+ "'" + FormatDigital((OperationServiceFee * PawnList[i].PrecentByTicket), Convert.ToInt32(numericUpDown1.Value)) + "', "
+ "'" + FormatDigital((OperationInterestFee * PawnList[i].PrecentByTicket), Convert.ToInt32(numericUpDown1.Value)) + "', "
+ "'" + FormatDigital((OperationReturnFee * PawnList[i].PrecentByTicket), Convert.ToInt32(numericUpDown1.Value)) + "', "
+ "'" + FormatDigital((OperationOverdueFee * PawnList[i].PrecentByTicket), Convert.ToInt32(numericUpDown1.Value)) + "', "
+ "'" + PawnList[i].Price + "', "
+ "'" + ReckonAmount.ToString() + "', "
+ "'" + OperationDate + "', "
+ "'" + OperationStartDate + "', "
+ "'" + OperationEndDate + "', "
+ "'" + OperatorName + "', "
+ "'" + LastOperationID + "', "
+ "'" + "0', "
+ "'" + TicketFeeRate + "', "
+ "'" + TicketInterestRate + "')";
RestOperationServiceFee = RestOperationServiceFee - FormatDigital((OperationServiceFee * PawnList[i].PrecentByTicket), Convert.ToInt32(numericUpDown1.Value));
RestOperationInterestFee = RestOperationInterestFee - FormatDigital((OperationInterestFee * PawnList[i].PrecentByTicket), Convert.ToInt32(numericUpDown1.Value));
RestOperationReturnFee = RestOperationReturnFee - FormatDigital((OperationReturnFee * PawnList[i].PrecentByTicket), Convert.ToInt32(numericUpDown1.Value));
RestOperationOverdueFee = RestOperationOverdueFee - FormatDigital((OperationOverdueFee * PawnList[i].PrecentByTicket), Convert.ToInt32(numericUpDown1.Value));
}
else
{
SqlExecString = "insert into DDOperation (TicketID, PawnageID, OperationTYPE, OperationNumber, ServiceFee, InterestFee, ReturnFee, OverdueFee, Amount, ReckonAmount, OperationDate, StartDate, EndDate, OperaterName, PreOperationID, NextOperationID, ServiceFeeRate, InterestFeeRate) VALUES ("
+ "'" + TicketID.ToString() + "', "
+ "'" + PawnList[i].PawnageID + "', "
+ "'" + OperationType + "', "
+ "'" + GetNewOperationNumber() + "', "
+ "'" + FormatDigital(RestOperationServiceFee, Convert.ToInt32(numericUpDown1.Value)) + "', "
+ "'" + FormatDigital(RestOperationInterestFee, Convert.ToInt32(numericUpDown1.Value)) + "', "
+ "'" + FormatDigital(RestOperationReturnFee, Convert.ToInt32(numericUpDown1.Value)) + "', "
+ "'" + FormatDigital(RestOperationOverdueFee, Convert.ToInt32(numericUpDown1.Value)) + "', "
+ "'" + PawnList[i].Price + "', "
+ "'" + ReckonAmount.ToString() + "', "
+ "'" + OperationDate + "', "
+ "'" + OperationStartDate + "', "
+ "'" + OperationEndDate + "', "
+ "'" + OperatorName + "', "
+ "'" + LastOperationID + "', "
+ "'" + "0', "
+ "'" + TicketFeeRate + "', "
+ "'" + TicketInterestRate + "')";
}
SqlExecResult = SqlConnection.Exec(SqlExecString);
//临时区域注意注意！！！！！
/*
* SqlResult = SqlConnection.Query("select OperationID from DDOperation where "
+ "TicketID='" + TicketID.ToString() + "' and "
+ "PawnageID='" + PawnList[i].PawnageID + "' and "
+ "OperationTYPE='" + OperationType + "' and "
+ "OperationNumber='" + GetNewOperationNumber() + "' and "
+ "ServiceFee='" + (OperationServiceFee * PawnList[i].PrecentByTicket) + "' and "
+ "InterestFee='" + (OperationInterestFee * PawnList[i].PrecentByTicket) + "' and "
+ "ReturnFee='" + (OperationReturnFee * PawnList[i].PrecentByTicket) + "' and "
+ "OverdueFee='" + (OperationOverdueFee * PawnList[i].PrecentByTicket) + "' and "
+ "Amount='" + PawnList[i].Price + "' and "
+ "ReckonAmount='" + ReckonAmount + "' and "
+ "OperationDate='" + OperationDate + "' and "
+ "StartDate='" + OperationStartDate + "' and "
+ "EndDate='" + OperationEndDate + "' and "
+ "OperaterName='" + OperatorName + "' and "
+ "PreOperationID='" + LastOperationID + "' and "
+ "NextOperationID='" + "0'"
+ );
+ SqlData = SqlResult.GetResult();
+ while (SqlData.Read())
+ {
+ NewOperationID = Convert.ToInt32(CutSpace(SqlData[0].ToString()));
+ }
+ SqlData.Close();
*/
SqlResult = SqlConnection.Query("select OperationID from DDOperation where TicketID=" + TicketID.ToString());
SqlData = SqlResult.GetResult();
while (SqlData.Read())
{
NewOperationID = Convert.ToInt32(CutSpace(SqlData[0].ToString()));
}
SqlData.Close();
SqlExecString = "update DDOperation set NextOperationID='" + NewOperationID + "' where OperationID='" + LastOperationID + "'";
SqlExecResult = SqlConnection.Exec(SqlExecString);
}
UpdateOperationNumber();
#endregion
#region 清空参数
OperationType = 0;
NextOperationType = 0;
OperationServiceFee = 0;
NextOperationServiceFee = 0;
OperationInterestFee = 0;
NextOperationInterestFee = 0;
OperationReturnFee = 0;
NextOperationReturnFee = 0;
OperationOverdueFee = 0;
NextOperationOverdueFee = 0;
ReckonAmount = 0;
NextReckonAmount = 0;
OperationDate = "";
NextOperationDate = "";
OperationStartDate = "";
NextOperationStartDate = "";
OperationEndDate = "";
NextOperationEndDate = "";
OperatorName = "";
NextOperatorName = "";
Fee = 0;
NextFee = 0;
LastOperationID = 0;
NewOperationID = 0;
#endregion
PawnList.Clear();
DBFDataO.Close();
DBFConnectionO.DisConnect();
#endregion
}
DBFData.Close();
DBFConnection.DisConnect();
}
void DDHouseInfo()
{
int SqlExecResult = 0;
string SqlExecString = "";
int theTicketID = 0;
int thePawnageID = 0;
int i = 0;
string DateNone = "1899/12/300:00:00";
string theRegisterDate = "";
string theInsuranceDate = "";
string theNotarizationDate = "";
DBFConnection.SetFile(this.DBFPath + @"\1009.DBF");
DBFConnection.Connect();
DBFResult = DBFConnection.Query("select D1009_03, D1009_02, D1009_04, D1009_05, D1009_06, D1009_07, B_NO, NO from 1009.DBF");
DBFData = DBFResult.GetResult();
while (DBFData.Read())
{
theTicketID = GetTicketIDbyTicketNumber(CutSpace(DBFData[6].ToString()));
if (theTicketID != 0)
{
SqlResult = SqlConnection.Query("select distinct PawnageID from DDOperation where TicketID='" + theTicketID + "'");
SqlData = SqlResult.GetResult();
i = 0;
while (i < Convert.ToInt32(CutSpace(DBFData[7].ToString())))
{
SqlData.Read();
i++;
}
thePawnageID = Convert.ToInt32(CutSpace(SqlData[0].ToString()));
SqlData.Close();
if (CutSpace(DBFData[3].ToString()) != DateNone)
{
theRegisterDate = ChangeDateFormat(DateTime.Parse(DBFData[3].ToString()).ToShortDateString());
}
else
{
theRegisterDate = "";
}
if (CutSpace(DBFData[4].ToString()) != DateNone)
{
theInsuranceDate = ChangeDateFormat(DateTime.Parse(DBFData[4].ToString()).ToShortDateString());
}
else
{
theInsuranceDate = "";
}
if (CutSpace(DBFData[5].ToString()) != DateNone)
{
theNotarizationDate = ChangeDateFormat(DateTime.Parse(DBFData[5].ToString()).ToShortDateString());
}
else
{
theNotarizationDate = "";
}
SqlExecString = "insert into DDHouseInfo (Address, CompactNumber, Area, RegisterDate, InsuranceDate, NotarizationDate, PawnageID) "
+ "VALUES ("
+ "'" + CutSpace(DBFData[0].ToString()) + "', "
+ "'" + CutSpace(DBFData[1].ToString()) + "', "
+ "'" + CutSpace(DBFData[2].ToString()) + "', "
+ "'" + theRegisterDate + "', "
+ "'" + theInsuranceDate + "', "
+ "'" + theNotarizationDate + "', "
+ "'" + thePawnageID.ToString() + "')";
SqlExecResult = SqlConnection.Exec(SqlExecString);
theTicketID = 0;
thePawnageID = 0;
theRegisterDate = "";
theInsuranceDate = "";
theNotarizationDate = "";
}
}
DBFData.Close();
DBFConnection.DisConnect();
}
private void DDPawnageClass()
{
int SqlExecResult = 0;
string SqlExecString = "";
double MaxFeeRate = 0;
double MinFeeRate = 0;
double MonthFeeRate = 0;
#region Get System MonthFeeRate & InterestRate
double SysMonthFeeRate = 0;
double SysInterestRate = 0;
DBFConnection.SetFile(this.DBFPath + "CONFIG.DBF");
DBFConnection.Connect();
DBFResult = DBFConnection.Query("select SYS_FW, SYS_LX from CONFIG.DBF");
DBFData = DBFResult.GetResult();
while (DBFData.Read())
{
SysMonthFeeRate = Convert.ToDouble(CutSpace(DBFData[0].ToString()));
SysInterestRate = Convert.ToDouble(CutSpace(DBFData[1].ToString()));
}
DBFData.Close();
DBFConnection.DisConnect();
#endregion
#region Insert NoUpper
int NoUpperID = 0;
SqlExecString = "Insert into DDPawnageClass (ClassName, ParentID, UnitID, MonthFeeRate, InterestRate, IsRoot, StatisticClassID) VALUES ('无上级分类', '1', '0','0','0','1','0')";
SqlExecResult = SqlConnection.Exec(SqlExecString);
SqlResult = SqlConnection.Query("select ClassID from DDPawnageClass where ClassName = '无上级分类'");
SqlData = SqlResult.GetResult();
SqlData.Read();
NoUpperID = Convert.ToInt32(CutSpace(SqlData[0].ToString()));
SqlData.Close();
SqlExecResult = SqlConnection.Exec("update DDPawnageClass set ParentID = '" + NoUpperID + "' where ClassID='" + NoUpperID.ToString() + "'");
#endregion
#region Insert Root
DBFConnection.SetFile(this.DBFPath + "DD_CLASS.DBF");
DBFConnection.Connect();
DBFResult = DBFConnection.Query("select CLASS_NAME, UNITTYPE, FW_RATE, LX_RATE, D1_CLASS from DD_CLASS.DBF");
DBFData = DBFResult.GetResult();
while (DBFData.Read())
{
MonthFeeRate = GetRate(CutSpace(DBFData[2].ToString()), SysMonthFeeRate.ToString());
MaxFeeRate = MonthFeeRate * 1.1;
MinFeeRate = MonthFeeRate * 0.9;
SqlExecString = "Insert into DDPawnageClass (ClassName, ParentID, UnitID, MonthFeeRate, MaxFeeRate, MinFeeRate, InterestRate, IsRoot, StatisticClassID) VALUES ("
+ "'" + CutSpace(DBFData[0].ToString()) + "', "
+ "'0', "
+ "'" + GetMesureUnitIDbByUNITTYPE(CutSpace(DBFData[1].ToString())) + "', "
+ "'" + MonthFeeRate.ToString() + "', "
+ "'" + MaxFeeRate.ToString() + "', "
+ "'" + MinFeeRate.ToString() + "', "
+ "'" + GetRate(CutSpace(DBFData[3].ToString()), SysInterestRate.ToString()) + "', "
+ "'1', "
+ "'" + GetStatisticClassIDByD1_CLASS(CutSpace(DBFData[4].ToString())) + "')";
SqlExecResult = SqlConnection.Exec(SqlExecString);
}
DBFData.Close();
DBFConnection.DisConnect();
#endregion
#region InsertRootOther
SqlResult = SqlConnection.Query("select ClassID from DDPawnageClass where ClassName='其它'");
SqlData = SqlResult.GetResult();
if (!SqlData.Read())
{
SqlData.Close();
SqlExecString = "Insert into DDPawnageClass (ClassName, ParentID, UnitID, MonthFeeRate, InterestRate, IsRoot, StatisticClassID) VALUES ("
+ "'其它', "
+ "'0', "
+ "'" + GetMesureUnitIDbByUNITTYPE("0") + "', "
+ "'" + SysMonthFeeRate + "', "
+ "'" + SysInterestRate + "', "
+ "'1', "
+ "'" + GetStatisticClassIDByD1_CLASS("0") + "')";
SqlExecResult = SqlConnection.Exec(SqlExecString);
}
SqlData.Close();
int OtherID = 0;
SqlResult = SqlConnection.Query("select ClassID from DDPawnageClass where ClassName='其它' and ParentID=0");
SqlData = SqlResult.GetResult();
while (SqlData.Read())
{
OtherID = Convert.ToInt32(SqlData[0].ToString());
}
SqlData.Close();
SqlExecString = "Insert into DDPawnageClass (ClassName, ParentID, UnitID, MonthFeeRate, InterestRate, IsRoot, StatisticClassID) VALUES ("
+ "'其它', "
+ "'" + OtherID.ToString() + "', "
+ "'" + GetMesureUnitIDbByUNITTYPE("0") + "', "
+ "'" + SysMonthFeeRate + "', "
+ "'" + SysInterestRate + "', "
+ "'0', "
+ "'" + GetStatisticClassIDByD1_CLASS("0") + "')";
SqlExecResult = SqlConnection.Exec(SqlExecString);
#endregion
#region Insert Detail
int ParentID = 0;
DBFConnection.SetFile(this.DBFPath + "DD_TYPE.DBF");
DBFConnection.Connect();
DBFResult = DBFConnection.Query("select DD_TYPE, DD_NAME from DD_TYPE.DBF");
DBFData = DBFResult.GetResult();
while (DBFData.Read())
{
ParentID = GetParentIDByDD_TYPE(CutSpace(DBFData[0].ToString()));
SqlResultTemp = SqlTemp.Query("select UnitID, MonthFeeRate, InterestRate from DDPawnageClass where ClassID='" + ParentID + "'");
SqlDataTemp = SqlResultTemp.GetResult();
SqlDataTemp.Read();
MaxFeeRate = Convert.ToDouble(SqlDataTemp[1].ToString()) * 1.1;
MinFeeRate = Convert.ToDouble(SqlDataTemp[1].ToString()) * 0.9;
SqlExecString = "insert into DDPawnageClass (ClassName, ParentID, UnitID, MonthFeeRate, MaxFeeRate, MinFeeRate, InterestRate, IsRoot, StatisticClassID) VALUES ("
+ "'" + CutSpace(DBFData[1].ToString()) + "', "
+ "'" + ParentID + "', "
+ "'" + SqlDataTemp[0].ToString() + "', "
+ "'" + SqlDataTemp[1].ToString() + "', "
+ "'" + MaxFeeRate.ToString() + "', "
+ "'" + MinFeeRate.ToString() + "', "
+ "'" + SqlDataTemp[2].ToString() + "', "
+ "'0', "
+ "'0')";
SqlDataTemp.Close();
SqlExecResult = SqlConnection.Exec(SqlExecString);
}
DBFData.Close();
DBFConnection.DisConnect();
#endregion
#region OtherDetail
#endregion
}