public CreatePropertyRecord getPropertyRecordDataBase(string ID)
{
CreatePropertyRecord returnValue = new CreatePropertyRecord();
DataBase Base = new DataBase();
using (SqlConnection Sqlconn = new SqlConnection(Base.GetConnString()))
{
try
{
Sqlconn.Open();
string sql = "SELECT PropertyRecord.*, StaffDatabase.StaffName FROM PropertyRecord " +
"LEFT JOIN StaffDatabase ON PropertyRecord.Procurement=StaffDatabase.StaffID " +
"WHERE PropertyRecord.isDeleted=0 AND PropertyRecord.RecordID=@RecordID";
SqlCommand cmd = new SqlCommand(sql, Sqlconn);
cmd.Parameters.Add("@RecordID", SqlDbType.BigInt).Value = Chk.CheckStringtoInt64Function(ID);
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
returnValue.repairID = dr["RecordID"].ToString();
returnValue.Unit = dr["Unit"].ToString();
returnValue.fillInDate = DateTime.Parse(dr["WriteDate"].ToString()).ToString("yyyy-MM-dd");
returnValue.propertyStatus = dr["PropertyState"].ToString();
returnValue.propertyChange = dr["PropertyAddImpairment"].ToString();
returnValue.changeStatus = dr["PropertyChangeState"].ToString();
returnValue.propertyID = dr["PropertyID"].ToString();
returnValue.propertyCode = dr["Code"].ToString();
returnValue.applyID = dr["ApplyID"].ToString();
returnValue.propertyCategory = dr["Category"].ToString();
returnValue.propertyName = dr["PropertyName"].ToString();
returnValue.propertyLabel = dr["Label"].ToString();
returnValue.propertyUnit = dr["ItemUnit"].ToString();
returnValue.propertyQuantity = dr["Quantity"].ToString();
returnValue.propertyFitting = dr["Fitting"].ToString();
returnValue.propertyLocation = dr["Location"].ToString();
returnValue.propertyCustody = dr["Custody"].ToString();
returnValue.stopDate = DateTime.Parse(dr["ScrapDate"].ToString()).ToString("yyyy-MM-dd");
returnValue.propertySummons = dr["Summons"].ToString();
returnValue.propertyReceipt = dr["Receipt"].ToString();
returnValue.propertyAccounting = dr["Accounting"].ToString();
returnValue.inputDate = DateTime.Parse(dr["RecordedDate"].ToString()).ToString("yyyy-MM-dd");
returnValue.outputDate = DateTime.Parse(dr["OutDate"].ToString()).ToString("yyyy-MM-dd");
returnValue.fundSource = dr["FundSource"].ToString();
returnValue.fundAssist = dr["FundSourceAssist"].ToString();
returnValue.fundDonate = dr["FundSourceDonate"].ToString();
returnValue.buyDate = DateTime.Parse(dr["PurchaseDate"].ToString()).ToString("yyyy-MM-dd");
returnValue.Remnants = dr["Residual"].ToString();
returnValue.buySource = dr["PurchaseSource"].ToString();
returnValue.userYear = dr["ExpirationDate"].ToString();
returnValue.propertyPrice = dr["Price"].ToString();
returnValue.Depreciation = dr["Depreciation"].ToString();
returnValue.selfFunds = dr["SinceFundraising"].ToString();
returnValue.Purchaser = dr["Procurement"].ToString();
returnValue.PurchaserName = dr["StaffName"].ToString();
returnValue.Grant = dr["Grants"].ToString();
returnValue.Remark = dr["Remark"].ToString();
returnValue.attachment1 = dr["Annex1"].ToString();
returnValue.attachment2 = dr["Annex2"].ToString();
returnValue.attachment3 = dr["Annex3"].ToString();
returnValue.attachment4 = dr["Annex4"].ToString();
returnValue.attachment5 = dr["Annex5"].ToString();
returnValue.attachment6 = dr["Annex6"].ToString();
returnValue.attachment7 = dr["Annex7"].ToString();
returnValue.attachment8 = dr["Annex8"].ToString();
returnValue.ChangesArray = new List<PropertyChangesExplainData>();
//returnValue.checkNo = "1";
}
dr.Close();
sql = "SELECT PropertyChangesExplain.*, StaffDatabase.StaffName FROM PropertyChangesExplain " +
"INNER JOIN StaffDatabase ON PropertyChangesExplain.RelatedPersonnel=StaffDatabase.StaffID " +
"WHERE PropertyChangesExplain.isDeleted=0 AND PropertyChangesExplain.RecordID=@RecordID";
cmd = new SqlCommand(sql, Sqlconn);
cmd.Parameters.Add("@RecordID", SqlDbType.BigInt).Value = Chk.CheckStringtoInt64Function(ID);
dr = cmd.ExecuteReader();
while (dr.Read())
{
PropertyChangesExplainData addValue = new PropertyChangesExplainData();
addValue.cID = dr["ID"].ToString();
addValue.recordID = dr["RecordID"].ToString();
addValue.moveDate = DateTime.Parse(dr["ChangeDate"].ToString()).ToString("yyyy-MM-dd");
addValue.moveAbstract = dr["Summary"].ToString();
addValue.relatedByID = dr["RelatedPersonnel"].ToString();
addValue.relatedBy = dr["StaffName"].ToString();
returnValue.ChangesArray.Add(addValue);
}
dr.Close();
Sqlconn.Close();
}
catch (Exception e)
{
returnValue.checkNo = "-1";
returnValue.errorMsg = e.Message.ToString();
}
}
return returnValue;
}
public string[] setPropertyChangesRecord(PropertyChangesExplainData propertyChangesData)
{
string[] returnValue = new string[2];
returnValue[0] = "0";
returnValue[1] = "0";
DataBase Base = new DataBase();
SqlConnection Sqlconn = new SqlConnection(Base.GetConnString());
using (Sqlconn)
{
try
{
StaffDataBase sDB = new StaffDataBase();
List<string> CreateFileName = sDB.getStaffDataName(HttpContext.Current.User.Identity.Name);
Sqlconn.Open();
string sql = "UPDATE PropertyChangesExplain SET Summary=@Summary, UpFileBy=@UpFileBy, UpFileDate=(getDate()) " +
"WHERE ID=@ID AND isDeleted=0";
SqlCommand cmd = new SqlCommand(sql, Sqlconn);
cmd.Parameters.Add("@ID", SqlDbType.BigInt).Value = Chk.CheckStringtoInt64Function(propertyChangesData.cID);
cmd.Parameters.Add("@Summary", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(propertyChangesData.moveAbstract);
cmd.Parameters.Add("@UpFileBy", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(CreateFileName[0]);
returnValue[0] = cmd.ExecuteNonQuery().ToString();
Sqlconn.Close();
}
catch (Exception e)
{
returnValue[0] = "-1";
returnValue[1] = e.Message;
}
}
return returnValue;
}
public string[] createPropertyChangesRecord(PropertyChangesExplainData propertyChangesData)
{
string[] returnValue = new string[2];
returnValue[0] = "0";
returnValue[1] = "0";
DataBase Base = new DataBase();
SqlConnection Sqlconn = new SqlConnection(Base.GetConnString());
using (Sqlconn)
{
try
{
StaffDataBase sDB = new StaffDataBase();
List<string> CreateFileName = sDB.getStaffDataName(HttpContext.Current.User.Identity.Name);
Sqlconn.Open();
string sql = "INSERT INTO PropertyChangesExplain(RecordID, ChangeDate, Summary, RelatedPersonnel, CreateFileBy, UpFileBy, UpFileDate ) " +
"VALUES (@RecordID, @ChangeDate, @Summary, @RelatedPersonnel, @CreateFileBy, @UpFileBy, (getDate()) )";
SqlCommand cmd = new SqlCommand(sql, Sqlconn);
cmd.Parameters.Add("@RecordID", SqlDbType.BigInt).Value = Chk.CheckStringtoInt64Function(propertyChangesData.recordID);
cmd.Parameters.Add("@ChangeDate", SqlDbType.Date).Value = Chk.CheckStringtoDateFunction(propertyChangesData.moveDate);
cmd.Parameters.Add("@Summary", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(propertyChangesData.moveAbstract);
cmd.Parameters.Add("@RelatedPersonnel", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(CreateFileName[0]);
cmd.Parameters.Add("@CreateFileBy", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(CreateFileName[0]);
cmd.Parameters.Add("@UpFileBy", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(CreateFileName[0]);
returnValue[0] = cmd.ExecuteNonQuery().ToString();
Sqlconn.Close();
}
catch (Exception e)
{
returnValue[0] = "-1";
returnValue[1] = e.Message;
}
}
return returnValue;
}
public string[] setPropertyChangesRecord(PropertyChangesExplainData propertyChangesData)
{
PropertyDataBase pDB = new PropertyDataBase();
pDB.propertyFunction();
if (int.Parse(pDB._StaffhaveRoles[1]) == 1)
{
return pDB.setPropertyChangesRecord(propertyChangesData);
}
else
{
return new string[2] { _noRole, _errorMsg };
}
}
