public string[] setApplyPropertyDetail(PropertyDetailData PropertyDetail) { 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 PropertyApplyDetail SET PropertyName=@PropertyName, ItemUnit=@ItemUnit, Quantity=@Quantity, Format=@Format, "+ "EstimatePrice=@EstimatePrice, Explain=@Explain, Bill=@Bill, 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(PropertyDetail.pID); cmd.Parameters.Add("@PropertyName", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(PropertyDetail.Name); cmd.Parameters.Add("@ItemUnit", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(PropertyDetail.Unit); cmd.Parameters.Add("@Quantity", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(PropertyDetail.Quantity); cmd.Parameters.Add("@Format", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(PropertyDetail.Format); cmd.Parameters.Add("@EstimatePrice", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(PropertyDetail.Price); cmd.Parameters.Add("@Explain", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(PropertyDetail.Explain); cmd.Parameters.Add("@Bill", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(PropertyDetail.Bill); cmd.Parameters.Add("@UpFileBy", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(CreateFileName[0]); returnValue[0] = cmd.ExecuteNonQuery().ToString(); sql = "UPDATE PropertyBuyRepair SET TotalPrice=@TotalPrice, UpFileBy=@UpFileBy, UpFileDate=(getDate()) WHERE BuyRepairID=@BuyRepairID "+ "AND isDeleted=0"; cmd = new SqlCommand(sql, Sqlconn); cmd.Parameters.Add("@BuyRepairID", SqlDbType.BigInt).Value = Chk.CheckStringtoInt64Function(PropertyDetail.aID); cmd.Parameters.Add("@TotalPrice", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(PropertyDetail.Sum); 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 CreateApplyProperty getApplyPropertyDataBase(string ID) { CreateApplyProperty returnValue = new CreateApplyProperty(); DataBase Base = new DataBase(); using (SqlConnection Sqlconn = new SqlConnection(Base.GetConnString())) { try { Sqlconn.Open(); string sql = "SELECT PropertyBuyRepair.* , StaffDatabase.StaffName FROM PropertyBuyRepair " + "INNER JOIN StaffDatabase ON PropertyBuyRepair.Recipients=StaffDatabase.StaffID " + "WHERE PropertyBuyRepair.isDeleted=0 AND PropertyBuyRepair.BuyRepairID=@ID"; SqlCommand cmd = new SqlCommand(sql, Sqlconn); cmd.Parameters.Add("@ID", SqlDbType.BigInt).Value = Chk.CheckStringtoInt64Function(ID); SqlDataReader dr = cmd.ExecuteReader(); while (dr.Read()) { returnValue.ID = dr["BuyRepairID"].ToString(); returnValue.Unit = dr["Unit"].ToString(); returnValue.applyDate = DateTime.Parse(dr["FileDate"].ToString()).ToString("yyyy-MM-dd"); returnValue.applyID = dr["ApplyID"].ToString(); returnValue.applyType = dr["ApplyType"].ToString(); returnValue.applyPay = dr["Payment"].ToString(); returnValue.applyByID = dr["Recipients"].ToString(); returnValue.applyBy = dr["StaffName"].ToString(); returnValue.applyStatus = dr["State"].ToString(); returnValue.applySum = dr["TotalPrice"].ToString(); returnValue.DetailArray = new List<PropertyDetailData>(); } dr.Close(); sql = "SELECT * FROM PropertyApplyDetail WHERE isDeleted=0 AND BuyRepairID=@BuyRepairID"; cmd = new SqlCommand(sql, Sqlconn); cmd.Parameters.Add("@BuyRepairID", SqlDbType.BigInt).Value = returnValue.ID; dr = cmd.ExecuteReader(); while (dr.Read()) { PropertyDetailData addValue = new PropertyDetailData(); addValue.pID = dr["ID"].ToString(); addValue.Name = dr["PropertyName"].ToString(); addValue.Unit = dr["ItemUnit"].ToString(); addValue.Quantity = dr["Quantity"].ToString(); addValue.Format = dr["Format"].ToString(); addValue.Price = dr["EstimatePrice"].ToString(); addValue.Explain = dr["Explain"].ToString(); addValue.Bill = dr["Bill"].ToString(); returnValue.DetailArray.Add(addValue); } dr.Close(); Sqlconn.Close(); } catch (Exception e) { returnValue.checkNo = "-1"; returnValue.errorMsg = e.Message; } } return returnValue; }
public List<PropertyDetailData> getPropertyDetailDataBase(string ID) { List<PropertyDetailData> returnValue = new List<PropertyDetailData>(); DataBase Base = new DataBase(); using (SqlConnection Sqlconn = new SqlConnection(Base.GetConnString())) { try { Sqlconn.Open(); string sql = "SELECT * FROM PropertyApplyDetail WHERE isDeleted=0 AND BuyRepairID=@BuyRepairID"; SqlCommand cmd = new SqlCommand(sql, Sqlconn); cmd.Parameters.Add("@BuyRepairID", SqlDbType.BigInt).Value = Chk.CheckStringtoInt64Function(ID); SqlDataReader dr = cmd.ExecuteReader(); while (dr.Read()) { PropertyDetailData addValue = new PropertyDetailData(); addValue.pID = dr["ID"].ToString(); addValue.Name = dr["PropertyName"].ToString(); addValue.Unit = dr["ItemUnit"].ToString(); addValue.Quantity = dr["Quantity"].ToString(); addValue.Format = dr["Format"].ToString(); addValue.Price = dr["EstimatePrice"].ToString(); addValue.Explain = dr["Explain"].ToString(); addValue.Bill = dr["Bill"].ToString(); returnValue.Add(addValue); } dr.Close(); Sqlconn.Close(); } catch (Exception e) { PropertyDetailData addValue = new PropertyDetailData(); addValue.pID = "-1"; addValue.Unit = e.Message.ToString(); returnValue.Add(addValue); } } return returnValue; }
public string[] setApplyPropertyDetail(PropertyDetailData PropertyDetail) { PropertyDataBase pDB = new PropertyDataBase(); pDB.applyFunction(); if (int.Parse(pDB._StaffhaveRoles[1]) == 1) { return pDB.setApplyPropertyDetail(PropertyDetail); } else { return new string[2] { _noRole, _errorMsg }; } }