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