public string[] setApplyPropertyDataBase(CreateApplyProperty applyPropertyData) { 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 PropertyBuyRepair SET FileDate=@FileDate, ApplyType=@ApplyType, Payment=@Payment, "+ "TotalPrice=@TotalPrice, UpFileBy=@UpFileBy, UpFileDate=(getDate()) " + "WHERE BuyRepairID=@BuyRepairID AND isDeleted=0"; SqlCommand cmd = new SqlCommand(sql, Sqlconn); cmd.Parameters.Add("@BuyRepairID", SqlDbType.BigInt).Value = Chk.CheckStringtoInt64Function(applyPropertyData.ID); cmd.Parameters.Add("@FileDate", SqlDbType.Date).Value = Chk.CheckStringtoDateFunction(applyPropertyData.applyDate); cmd.Parameters.Add("@ApplyType", SqlDbType.TinyInt).Value = Chk.CheckStringtoIntFunction(applyPropertyData.applyType); cmd.Parameters.Add("@Payment", SqlDbType.TinyInt).Value = Chk.CheckStringtoIntFunction(applyPropertyData.applyPay); cmd.Parameters.Add("@TotalPrice", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(applyPropertyData.applySum); 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[] createApplyPropertyData(CreateApplyProperty applyPropertyData) { string[] returnValue = new string[2]; returnValue[0] = "0"; returnValue[1] = "0"; DataBase Base = new DataBase(); using (SqlConnection Sqlconn = new SqlConnection(Base.GetConnString())) { try { StaffDataBase sDB = new StaffDataBase(); List<string> CreateFileName = sDB.getStaffDataName(HttpContext.Current.User.Identity.Name); Sqlconn.Open(); string sql = "INSERT INTO PropertyBuyRepair (FileDate,Unit, ApplyType, Payment, Recipients, State, TotalPrice, " + "CreateFileBy, CreateFileDate, UpFileBy, UpFileDate, isDeleted) VALUES " + "(@FileDate, @Unit, @ApplyType, @Payment, @Recipients, 0, @TotalPrice, " + "@CreateFileBy, (getDate()), @UpFileBy, (getDate()), 0)"; SqlCommand cmd = new SqlCommand(sql, Sqlconn); cmd.Parameters.Add("@FileDate", SqlDbType.Date).Value = Chk.CheckStringtoDateFunction(applyPropertyData.applyDate); cmd.Parameters.Add("@Unit", SqlDbType.TinyInt).Value = Chk.CheckStringtoIntFunction(CreateFileName[2]); cmd.Parameters.Add("@ApplyType", SqlDbType.TinyInt).Value = Chk.CheckStringtoIntFunction(applyPropertyData.applyType); cmd.Parameters.Add("@Payment", SqlDbType.TinyInt).Value = Chk.CheckStringtoIntFunction(applyPropertyData.applyPay); cmd.Parameters.Add("@Recipients", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(CreateFileName[0]); cmd.Parameters.Add("@TotalPrice", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(applyPropertyData.applySum); 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(); if (returnValue[0] != "0") { Int64 Column = 0; sql = "select IDENT_CURRENT('PropertyBuyRepair') AS pID"; cmd = new SqlCommand(sql, Sqlconn); SqlDataReader dr = cmd.ExecuteReader(); while (dr.Read()) { Column = Int64.Parse(dr["pID"].ToString()); } dr.Close(); if (Column != 0) { for (int i = 0; i < (applyPropertyData.DetailArray).Count; i++) { sql = "INSERT INTO PropertyApplyDetail (BuyRepairID, PropertyName, ItemUnit, Quantity, Format, EstimatePrice, " + "Explain, Bill, CreateFileBy, CreateFileDate, UpFileBy, UpFileDate, isDeleted) VALUES " + "(@BuyRepairID, @PropertyName, @ItemUnit, @Quantity, @Format, @EstimatePrice, @Explain, @Bill, " + "@CreateFileBy, (getDate()), @UpFileBy, (getDate()), 0)"; cmd = new SqlCommand(sql, Sqlconn); cmd.Parameters.Add("@BuyRepairID", SqlDbType.BigInt).Value = Column; cmd.Parameters.Add("@PropertyName", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(applyPropertyData.DetailArray[i].Name); cmd.Parameters.Add("@ItemUnit", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(applyPropertyData.DetailArray[i].Unit); cmd.Parameters.Add("@Quantity", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(applyPropertyData.DetailArray[i].Quantity); cmd.Parameters.Add("@Format", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(applyPropertyData.DetailArray[i].Format); cmd.Parameters.Add("@EstimatePrice", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(applyPropertyData.DetailArray[i].Price); cmd.Parameters.Add("@Explain", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(applyPropertyData.DetailArray[i].Explain); cmd.Parameters.Add("@Bill", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(applyPropertyData.DetailArray[i].Bill); 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(); } returnValue[1] = Column.ToString(); } } Sqlconn.Close(); } catch (Exception e) { returnValue[0] = "-1"; returnValue[1] = e.Message.ToString(); } } return returnValue; }
public string[] setApplyPropertyDataBase(CreateApplyProperty applyPropertyData) { PropertyDataBase pDB = new PropertyDataBase(); pDB.applyFunction(); if (int.Parse(pDB._StaffhaveRoles[1]) == 1) { return pDB.setApplyPropertyDataBase(applyPropertyData); } else { return new string[2] { _noRole, _errorMsg }; } }
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 CreateApplyProperty getApplyPropertyDataBase(string ID) { PropertyDataBase pDB = new PropertyDataBase(); CreateApplyProperty returnValue = new CreateApplyProperty(); pDB.applyFunction(); if (int.Parse(pDB._StaffhaveRoles[3]) == 1) { returnValue=pDB.getApplyPropertyDataBase(ID); } else { returnValue.checkNo = _noRole; returnValue.errorMsg = _errorMsg; } StaffDataBase sDB = new StaffDataBase(); List<string> UserFile = sDB.getStaffDataName(HttpContext.Current.User.Identity.Name); if (returnValue.Unit != UserFile[2] && int.Parse(pDB._StaffhaveRoles[4]) == 0 && UserFile[1].Length > 0) { returnValue.checkNo = _getcheckNo; returnValue.errorMsg = _errorMsg; } return returnValue; }