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