private int GetLastOperationIDbyPawnageID(int PawnageID)
{
int i = 0;
int LastOperationID = 0;
List <int> OpertaionIDList = new List <int>();
List <int> NextOperationIDList = new List <int>();
SqlResultTemp = SqlTemp.Query("select OperationID, NextOperationID from DDOperation where PawnageID='" + PawnageID.ToString() + "'");
SqlDataTemp = SqlResultTemp.GetResult();
while (SqlDataTemp.Read())
{
OpertaionIDList.Add(Convert.ToInt32(CutSpace(SqlDataTemp[0].ToString())));
NextOperationIDList.Add(Convert.ToInt32(CutSpace(SqlDataTemp[1].ToString())));
}
SqlDataTemp.Close();
for (i = 0; i < NextOperationIDList.Count; i++)
{
if (NextOperationIDList[i] == 0)
{
LastOperationID = OpertaionIDList[i];
}
}
return(LastOperationID);
}
private int GetClassIDByDD_TYPE(string DD_TYPE)
{
int ClassID = 0;
string ClassName = "";
//Get Class Name from DBF
DBFTemp.SetFile(this.DBFPath + "DD_CLASS.DBF");
DBFTemp.Connect();
DBFResultTemp = DBFTemp.Query("select DD_NAME from DD_TYPE.DBF where DD_TYPE='" + DD_TYPE + "'");
DBFDataTemp = DBFResultTemp.GetResult();
while (DBFDataTemp.Read())
{
ClassName = CutSpace(DBFDataTemp[0].ToString());
}
DBFDataTemp.Close();
DBFTemp.DisConnect();
//Get ClassID from SQL
SqlResultTemp = SqlTemp.Query("select ClassID from DDPawnageClass where ClassName='" + ClassName + "'");
SqlDataTemp = SqlResultTemp.GetResult();
while (SqlDataTemp.Read())
{
ClassID = Convert.ToInt32(SqlDataTemp[0].ToString());
}
SqlDataTemp.Close();
return(ClassID);
}
private string GetMesureUnitIDbByUNITTYPE(string UNITTYPE)
{
string FinalReturn = "";
string MesureUnitName = "";
//Read MesureUnitName
DBFTemp.SetFile(this.DBFPath + "UNITTYPE.DBF");
DBFTemp.Connect();
DBFResultTemp = DBFTemp.Query("select UNITNAME from UNITTYPE.DBF where UNITTYPE = '" + UNITTYPE + "'");
DBFDataTemp = DBFResultTemp.GetResult();
while (DBFDataTemp.Read())
{
MesureUnitName = CutSpace(DBFDataTemp[0].ToString());
}
DBFDataTemp.Close();
DBFTemp.DisConnect();
if (MesureUnitName == "")
{
SqlResultTemp = SqlTemp.Query("select UnitID from DDMesureUnit where UnitName = '其它'");
SqlDataTemp = SqlResultTemp.GetResult();
SqlDataTemp.Read();
FinalReturn = CutSpace(SqlDataTemp[0].ToString());
SqlDataTemp.Close();
return(FinalReturn);
}
//Get MasureUnitID
SqlResultTemp = SqlTemp.Query("select UnitID from DDMesureUnit where UnitName = '" + MesureUnitName + "'");
SqlDataTemp = SqlResultTemp.GetResult();
SqlDataTemp.Read();
FinalReturn = SqlDataTemp[0].ToString();
SqlDataTemp.Close();
return(FinalReturn);
}
private string GetStatisticClassIDByD1_CLASS(string D1_CLASS)
{
string FinalReturn = "";
string ClassName = "";
//Read ClassName
DBFTemp.SetFile(this.DBFPath + "D1_CLASS.DBF");
DBFTemp.Connect();
DBFResultTemp = DBFTemp.Query("select D1_NAME from D1_CLASS.DBF where D1_CLASS = '" + D1_CLASS + "'");
DBFDataTemp = DBFResultTemp.GetResult();
while (DBFDataTemp.Read())
{
ClassName = CutSpace(DBFDataTemp[0].ToString());
}
DBFDataTemp.Close();
DBFTemp.DisConnect();
if (ClassName == "")
{
SqlResultTemp = SqlTemp.Query("select ClassID from DDStatisticClass where ClassName = '其它'");
SqlDataTemp = SqlResultTemp.GetResult();
SqlDataTemp.Read();
FinalReturn = CutSpace(SqlDataTemp[0].ToString());
SqlDataTemp.Close();
return(FinalReturn);
}
//GetStatisticClassID
SqlResultTemp = SqlTemp.Query("select ClassID from DDStatisticClass where ClassName = '" + ClassName + "'");
SqlDataTemp = SqlResultTemp.GetResult();
SqlDataTemp.Read();
FinalReturn = SqlDataTemp[0].ToString();
SqlDataTemp.Close();
return(FinalReturn);
}
private int GetSqlOperationTypebyDBFOP_TYPE(string theOP_TYPE)
{
int OperationType = 0;
SqlResultTemp = SqlTemp.Query("select StatusID from DDTicketStatus where Description='" + DBFOP_TYPEMapping(theOP_TYPE) + "'");
SqlDataTemp = SqlResultTemp.GetResult();
SqlDataTemp.Read();
OperationType = Convert.ToInt32(CutSpace(SqlDataTemp[0].ToString()));
SqlDataTemp.Close();
return(OperationType);
}
private int GetTicketIDbyTicketNumber(string TicketNumber)
{
int TicketID = 0;
SqlResultTemp = SqlTemp.Query("select TicketID from DDPawnTicket where TicketNumber='" + TicketNumber + "'");
SqlDataTemp = SqlResultTemp.GetResult();
while (SqlDataTemp.Read())
{
TicketID = Convert.ToInt32(CutSpace(SqlDataTemp[0].ToString()));
}
SqlDataTemp.Close();
return(TicketID);
}
private int GetSqlOperatorIDByDBFOperator(string theOperator)
{
int OperatorID = 0;
SqlResultTemp = SqlTemp.Query("select UserID from DDUsers where AccountName='" + theOperator + "'");
SqlDataTemp = SqlResultTemp.GetResult();
while (SqlDataTemp.Read())
{
OperatorID = Convert.ToInt32(SqlDataTemp[0].ToString());
}
SqlDataTemp.Close();
return(OperatorID);
}
private string GetCertTypeNameByTypeID(int theCertType)
{
string TypeName = "";
SqlResultTemp = SqlTemp.Query("select TypeName from DDCertTypes where TypeID='" + theCertType.ToString() + "'");
SqlDataTemp = SqlResultTemp.GetResult();
while (SqlDataTemp.Read())
{
TypeName = SqlDataTemp[0].ToString();
}
SqlDataTemp.Close();
return(TypeName);
}
private int GetSqlCustomerIDbyNameCertNumber(string theName, string theCertNumber)
{
int CustomerID = 0;
SqlResultTemp = SqlTemp.Query("select CustomerID from DDCustomerInfo where CustomerName='" + theName + "' and CertNumber='" + theCertNumber + "'");
SqlDataTemp = SqlResultTemp.GetResult();
if (SqlDataTemp.Read())
{
CustomerID = Convert.ToInt32(SqlDataTemp[0].ToString());
}
SqlDataTemp.Close();
return(CustomerID);
}
private int GetStatusIDbyName(string theName)
{
SqlResultTemp = SqlTemp.Query("select StatusID from DDTicketStatus where Description='" + theName + "'");
SqlDataTemp = SqlResultTemp.GetResult();
if (SqlDataTemp.Read())
{
int StatusID = 0;
StatusID = Convert.ToInt32(SqlDataTemp[0].ToString());
SqlDataTemp.Close();
return(StatusID);
}
SqlDataTemp.Close();
return(0);
}
private bool isExistCustomerInSQL(string Name, string CertNumber)
{
SqlResultTemp = SqlTemp.Query("select * from DDCustomerInfo where CustomerName='" + Name + "' and CertNumber='" + CertNumber + "'");
SqlDataTemp = SqlResultTemp.GetResult();
if (SqlDataTemp.Read())
{
SqlDataTemp.Close();
return(true);
}
else
{
SqlDataTemp.Close();
return(false);
}
}
private int GetCertIDByName(string theName)
{
SqlResultTemp = SqlTemp.Query("select TypeID from DDCertTypes where TypeName='" + theName + "'");
SqlDataTemp = SqlResultTemp.GetResult();
if (SqlDataTemp.Read())
{
int ID = Convert.ToInt32(SqlDataTemp[0]);
SqlDataTemp.Close();
return(ID);
}
else
{
SqlDataTemp.Close();
return(-1);
}
}
private string GetNewOperationNumber()
{
int Value = 0;
string TicketNumber = "";
SqlResultTemp = SqlTemp.Query("select max (ParamValue) from DDGeneralParemeters where ParamName='OperationNumber'");
SqlDataTemp = SqlResultTemp.GetResult();
while (SqlDataTemp.Read())
{
Value = Convert.ToInt32(CutSpace(SqlDataTemp[0].ToString()));
}
Value++;
TicketNumber = FormatParmValue(Value);
SqlDataTemp.Close();
return(TicketNumber);
}
private int GetParentIDByDD_TYPE(string DD_TYPE)
{
string DD_CLASS = "";
string ClassName = "";
string FinalReturn = "";
//GetClassName
DD_CLASS = DD_TYPE.Substring(0, 4);
DBFTemp.SetFile(this.DBFPath + "DD_CLASS.DBF");
DBFTemp.Connect();
DBFResultTemp = DBFTemp.Query("select CLASS_NAME from DD_CLASS.DBF where DD_CLASS='" + DD_CLASS + "'");
DBFDataTemp = DBFResultTemp.GetResult();
while (DBFDataTemp.Read())
{
ClassName = CutSpace(DBFDataTemp[0].ToString());
}
DBFDataTemp.Close();
DBFTemp.DisConnect();
//GetClassId
SqlResultTemp = SqlTemp.Query("select ClassID from DDPawnageClass where ClassName='" + ClassName + "' order by ClassID DESC");
SqlDataTemp = SqlResultTemp.GetResult();
while (SqlDataTemp.Read())
{
FinalReturn = SqlDataTemp[0].ToString();
}
SqlDataTemp.Close();
//if not success
if (FinalReturn == "")
{
SqlResultTemp = SqlTemp.Query("select ClassID from DDPawnageClass where ClassName='其它'");
SqlDataTemp = SqlResultTemp.GetResult();
SqlDataTemp.Read();
FinalReturn = SqlDataTemp[0].ToString();
SqlDataTemp.Close();
return(Convert.ToInt32(FinalReturn));
}
else
{
return(Convert.ToInt32(FinalReturn));
}
}
private void RemoveOperationLine(string iOperationID)
{
string SqlExecString = "";
int SqlExecResult = 0;
string DeleteOperationID = "";
string NextOperationID = "";
string PreOperationID = "";
SqlResultTemp = SqlTemp.Query("select PreOperationID, NextOperationID from DDOperation where OperationID='" + iOperationID + "'");
SqlDataTemp = SqlResultTemp.GetResult();
while (SqlDataTemp.Read())
{
DeleteOperationID = SqlDataTemp[0].ToString();
NextOperationID = SqlDataTemp[1].ToString();
}
SqlDataTemp.Close();
SqlResultTemp = SqlTemp.Query("select PreOperationID, NextOperationID from DDOperation where OperationID='" + DeleteOperationID + "'");
SqlDataTemp = SqlResultTemp.GetResult();
while (SqlDataTemp.Read())
{
PreOperationID = SqlDataTemp[0].ToString();
}
SqlDataTemp.Close();
SqlExecString = "update DDOperation set NextOperationID='" + NextOperationID + "' where OperationID='" + PreOperationID + "'";
SqlExecResult = SqlTemp.Exec(SqlExecString);
SqlExecString = "update DDOperation set OperationType=6 where OperationID='" + DeleteOperationID + "'";
SqlExecResult = SqlTemp.Exec(SqlExecString);
SqlExecString = "update DDOperation set NextOperationID='" + NextOperationID + "' where OperationID='" + DeleteOperationID + "'";
SqlExecResult = SqlTemp.Exec(SqlExecString);
SqlExecString = "delete from DDOperation where OperationID='" + iOperationID + "'";
SqlExecResult = SqlTemp.Exec(SqlExecString);
SqlExecString = "update DDOperation set PreOperationID='" + PreOperationID + "' where OperationID='" + NextOperationID + "'";
SqlExecResult = SqlTemp.Exec(SqlExecString);
}
private int GetSqlTicketStatusIDbyDBFB_NO(string theB_NO)
{
int SqlTicketStatus = 0;
string OP_TYPE = "0";
DBFTemp.SetFile(this.DBFPath + "OP_RCD.DBF");
DBFTemp.Connect();
DBFResultTemp = DBFTemp.Query("select OP_TYPE from OP_RCD.DBF where B_NO='" + theB_NO + "'");
DBFDataTemp = DBFResultTemp.GetResult();
while (DBFDataTemp.Read())
{
OP_TYPE = CutSpace(DBFDataTemp[0].ToString());
}
DBFDataTemp.Close();
SqlResultTemp = SqlTemp.Query("select StatusID from DDTicketStatus where Description='" + DBFTicketStatus_TYPEMapping(OP_TYPE) + "'");
SqlDataTemp = SqlResultTemp.GetResult();
SqlDataTemp.Read();
SqlTicketStatus = Convert.ToInt32(CutSpace(SqlDataTemp[0].ToString()));
SqlDataTemp.Close();
DBFTemp.DisConnect();
return(SqlTicketStatus);
}
private string GetUnitNameByClassID(int theClassID)
{
string UnitName = "";
string UnitID = "";
SqlResultTemp = SqlTemp.Query("select UnitID from DDPawnageClass where ClassID='" + theClassID + "'");
SqlDataTemp = SqlResultTemp.GetResult();
while (SqlDataTemp.Read())
{
UnitID = CutSpace(SqlDataTemp[0].ToString());
}
SqlDataTemp.Close();
SqlResultTemp = SqlTemp.Query("select UnitName from DDMesureUnit where UnitID='" + UnitID + "'");
SqlDataTemp = SqlResultTemp.GetResult();
while (SqlDataTemp.Read())
{
UnitName = CutSpace(SqlDataTemp[0].ToString());
}
SqlDataTemp.Close();
return(UnitName);
}
private int GetSqlCertIDbyDBFCertID(string DBFID)
{
DBFTemp.SetFile(this.DBFPath + "S_TYPE.DBF");
DBFTemp.Connect();
DBFResultTemp = DBFTemp.Query("select S_TYPE from S_TYPE.DBF where S_NO='" + DBFID + "'");
DBFDataTemp = DBFResultTemp.GetResult();
if (DBFDataTemp.Read())
{
string DBFName = CutSpace(DBFDataTemp[0].ToString());
DBFDataTemp.Close();
DBFTemp.DisConnect();
SqlResultTemp = SqlTemp.Query("select TypeID from DDCertTypes where TypeName='" + DBFName + "'");
SqlDataTemp = SqlResultTemp.GetResult();
if (SqlDataTemp.Read())
{
int SqlID = Convert.ToInt32(CutSpace(SqlDataTemp[0].ToString()));
SqlDataTemp.Close();
DBFDataTemp.Close();
DBFTemp.DisConnect();
return(SqlID);
}
else
{
DBFDataTemp.Close();
DBFTemp.DisConnect();
SqlDataTemp.Close();
return(-1);
}
}
else
{
DBFDataTemp.Close();
DBFTemp.DisConnect();
return(-1);
}
}
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
}