public List<CreateReturn> SearchReturn(int indexpage, SearchReturn returnData) { List<CreateReturn> returnValue = new List<CreateReturn>(); DataBase Base = new DataBase(); string ConditionReturn = this.SearchReturnConditionReturn(returnData); 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 = "SELECT * FROM (SELECT ROW_NUMBER() OVER (ORDER BY PropertyReturn.ReturnDate DESC, PropertyReturn.ID DESC) " + "AS RowNum, PropertyReturn.*,PropertyDatabase.PropertyName,PropertyDatabase.ItemUnit,StaffDatabase.StaffName AS receiveByName FROM PropertyReturn " + "INNER JOIN PropertyDatabase ON PropertyReturn.PropertyID=PropertyDatabase.PropertyID AND PropertyDatabase.isDeleted=0 " + "INNER JOIN StaffDatabase ON PropertyReturn.OutputTransactor=StaffDatabase.StaffID AND StaffDatabase.isDeleted=0 " + "WHERE PropertyReturn.isDeleted=0 " + ConditionReturn + " ) " + "AS NewTable " + "WHERE RowNum >= (@indexpage-" + PageMinNumFunction() + ") AND RowNum <= (@indexpage)"; SqlCommand cmd = new SqlCommand(sql, Sqlconn); cmd.Parameters.Add("@indexpage", SqlDbType.Int).Value = indexpage; cmd.Parameters.Add("@txtreturnedID", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(returnData.txtreturnedID); cmd.Parameters.Add("@txtreturnedDateStart", SqlDbType.Date).Value = Chk.CheckStringtoDateFunction(returnData.txtreturnedDateStart); cmd.Parameters.Add("@txtreturnedDateeEnd", SqlDbType.Date).Value = Chk.CheckStringtoDateFunction(returnData.txtreturnedDateeEnd); cmd.Parameters.Add("@txtgetgoodsBy", SqlDbType.NVarChar).Value = "%" + Chk.CheckStringFunction(returnData.txtgetgoodsBy) + "%"; cmd.Parameters.Add("@txtrestationeryID", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(returnData.txtrestationeryID); cmd.Parameters.Add("@txtrestationeryName", SqlDbType.NVarChar).Value = "%" + Chk.CheckStringFunction(returnData.txtrestationeryName) + "%"; SqlDataReader dr = cmd.ExecuteReader(); while (dr.Read()) { CreateReturn addValue = new CreateReturn(); addValue.rID = dr["ID"].ToString(); addValue.returnedID = dr["ReturnID"].ToString(); addValue.returnedDate = DateTime.Parse(dr["ReturnDate"].ToString()).ToString("yyyy-MM-dd"); addValue.getgoodsDate = DateTime.Parse(dr["OutputDate"].ToString()).ToString("yyyy-MM-dd"); addValue.getgoodsByID = dr["OutputTransactor"].ToString(); addValue.getgoodsByName = dr["receiveByName"].ToString(); addValue.restationeryID = dr["PropertyID"].ToString(); addValue.Unit = dr["Unit"].ToString(); addValue.returnedQuantity = dr["Quantity"].ToString(); addValue.returnedReason = dr["Reason"].ToString(); addValue.restationeryName = dr["PropertyName"].ToString(); addValue.restationeryUnit = dr["ItemUnit"].ToString(); returnValue.Add(addValue); } dr.Close(); Sqlconn.Close(); } catch (Exception e) { CreateReturn addValue = new CreateReturn(); addValue.checkNo = "-1"; addValue.errorMsg = e.Message.ToString(); returnValue.Add(addValue); } } return returnValue; }
public string[] createReturnDataBase(CreateReturn returnData) { 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 PropertyReturn (Unit, ReturnID, ReturnDate, OutputDate, OutputTransactor, PropertyID, Quantity, Reason, CreateFileBy, CreateFileDate, UpFileBy, UpFileDate, isDeleted) " + "VALUES(@Unit, @ReturnID, @ReturnDate, @OutputDate, @OutputTransactor, @PropertyID, @Quantity, @Reason, @CreateFileBy, (getDate()), @UpFileBy, (getDate()), 0)"; SqlCommand cmd = new SqlCommand(sql, Sqlconn); cmd.Parameters.Add("@Unit", SqlDbType.TinyInt).Value = Chk.CheckStringtoIntFunction(CreateFileName[2]); cmd.Parameters.Add("@ReturnID", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(returnData.returnedID); cmd.Parameters.Add("@ReturnDate", SqlDbType.Date).Value = Chk.CheckStringtoDateFunction(returnData.returnedDate); cmd.Parameters.Add("@OutputDate", SqlDbType.Date).Value = Chk.CheckStringtoDateFunction(returnData.getgoodsDate); cmd.Parameters.Add("@OutputTransactor", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(returnData.getgoodsByID); cmd.Parameters.Add("@PropertyID", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(returnData.restationeryID); cmd.Parameters.Add("@Quantity", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(returnData.returnedQuantity); cmd.Parameters.Add("@Reason", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(returnData.returnedReason); 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('PropertyReturn') AS rID"; cmd = new SqlCommand(sql, Sqlconn); SqlDataReader dr = cmd.ExecuteReader(); while (dr.Read()) { Column = Int64.Parse(dr["rID"].ToString()); } dr.Close(); if (Column != 0) { sql = "SELECT SUM(QCOUNT) FROM (" + "SELECT Count(*) AS QCOUNT FROM PropertyDatabase " + "LEFT JOIN PropertyPurchase ON PropertyDatabase.PropertyID=PropertyPurchase.PropertyID WHERE DATEDIFF(month,PropertyPurchase.CreateFileDate,getdate())=0 " + "UNION ALL " + "SELECT Count(*) AS QCOUNT FROM PropertyDatabase " + "INNER JOIN PropertyUse ON PropertyDatabase.PropertyID=PropertyUse.PropertyID WHERE DATEDIFF(month,PropertyUse.CreateFileDate,getdate())=0 " + "UNION ALL " + "SELECT Count(*) AS QCOUNT FROM PropertyDatabase " + "INNER JOIN PropertyScrap ON PropertyDatabase.PropertyID=PropertyScrap.PropertyID WHERE DATEDIFF(month,PropertyScrap.CreateFileDate,getdate())=0 " + "UNION ALL " + "SELECT Count(*) AS QCOUNT FROM PropertyDatabase " + "INNER JOIN PropertyReturn ON PropertyDatabase.PropertyID=PropertyReturn.PropertyID WHERE DATEDIFF(month,PropertyReturn.CreateFileDate,getdate())=0 " + ") AS NEWTABLE"; cmd = new SqlCommand(sql, Sqlconn); string stuNumber = cmd.ExecuteScalar().ToString(); string tcYear = (DateTime.Now.Year - 1911).ToString(); string tcMonth = (DateTime.Now.Month).ToString(); string stuIDName = CreateFileName[2] + "3" + tcYear.Substring(1, tcYear.Length - 1) + tcMonth.PadLeft(2, '0') + stuNumber.PadLeft(3, '0'); sql = "UPDATE PropertyReturn SET ReturnID=(@ReturnID) WHERE ID=(@TID)"; cmd = new SqlCommand(sql, Sqlconn); cmd.Parameters.Add("@TID", SqlDbType.BigInt).Value = Column; cmd.Parameters.Add("@ReturnID", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(stuIDName); returnValue[0] = cmd.ExecuteNonQuery().ToString(); } } Sqlconn.Close(); } catch (Exception e) { returnValue[0] = "-1"; returnValue[1] = e.Message.ToString(); } } return returnValue; }
public string[] createReturnDataBase(CreateReturn returnData) { OtherDataBase sDB = new OtherDataBase(); if (int.Parse(sDB._StaffhaveRoles[2]) == 1) { return sDB.createReturnDataBase(returnData); } else { return new string[2] { _noRole, _errorMsg }; } }