public string[] SearchPurchaseCount(SearchPurchase purchaseData) { string[] returnValue = new string[2]; returnValue[0] = "0"; returnValue[1] = "0"; DataBase Base = new DataBase(); string ConditionReturn = this.SearchPurchaseConditionReturn(purchaseData); 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 COUNT(*) FROM PropertyPurchase "+ "INNER JOIN PropertyDatabase AS s1 ON PropertyPurchase.PropertyID=s1.PropertyID AND s1.isDeleted=0 " + "WHERE PropertyPurchase.isDeleted=0 " + ConditionReturn; SqlCommand cmd = new SqlCommand(sql, Sqlconn); cmd.Parameters.Add("@txtpurchaseID", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(purchaseData.txtpurchaseID); cmd.Parameters.Add("@txtfirmName", SqlDbType.NVarChar).Value = "%" + Chk.CheckStringFunction(purchaseData.txtfirmName) + "%"; cmd.Parameters.Add("@txtpurchaseDateStart", SqlDbType.Date).Value = Chk.CheckStringtoDateFunction(purchaseData.txtpurchaseDateStart); cmd.Parameters.Add("@txtpurchaseDateEnd", SqlDbType.Date).Value = Chk.CheckStringtoDateFunction(purchaseData.txtpurchaseDateEnd); cmd.Parameters.Add("@txtstationeryID", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(purchaseData.txtstationeryID); returnValue[0] = cmd.ExecuteScalar().ToString(); Sqlconn.Close(); } catch (Exception e) { returnValue[0] = "-1"; returnValue[1] = e.Message.ToString(); } } return returnValue; }
public List<CreatePurchase> SearchPurchase(int indexpage, SearchPurchase purchaseData) { List<CreatePurchase> returnValue = new List<CreatePurchase>(); DataBase Base = new DataBase(); string ConditionReturn = this.SearchPurchaseConditionReturn(purchaseData); 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 PropertyPurchase.InputDate DESC, PropertyPurchase.ID DESC) " + "AS RowNum, PropertyPurchase.*,s1.PropertyName,s1.ItemUnit FROM PropertyPurchase " + "INNER JOIN PropertyDatabase AS s1 ON PropertyPurchase.PropertyID=s1.PropertyID AND s1.isDeleted=0 " + "WHERE PropertyPurchase.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("@txtpurchaseID", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(purchaseData.txtpurchaseID); cmd.Parameters.Add("@txtfirmName", SqlDbType.NVarChar).Value = "%" + Chk.CheckStringFunction(purchaseData.txtfirmName) + "%"; cmd.Parameters.Add("@txtpurchaseDateStart", SqlDbType.Date).Value = Chk.CheckStringtoDateFunction(purchaseData.txtpurchaseDateStart); cmd.Parameters.Add("@txtpurchaseDateEnd", SqlDbType.Date).Value = Chk.CheckStringtoDateFunction(purchaseData.txtpurchaseDateEnd); cmd.Parameters.Add("@txtstationeryID", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(purchaseData.txtstationeryID); SqlDataReader dr = cmd.ExecuteReader(); while (dr.Read()) { CreatePurchase addValue = new CreatePurchase(); addValue.pID = dr["ID"].ToString(); addValue.purchaseID = dr["InputID"].ToString(); addValue.purchaseDate = DateTime.Parse(dr["InputDate"].ToString()).ToString("yyyy-MM-dd"); addValue.firmName = dr["CompanyName"].ToString(); addValue.firmTel = dr["CompanyTel"].ToString(); addValue.stationeryID = dr["PropertyID"].ToString(); addValue.Unit = dr["Unit"].ToString(); addValue.stationeryQuantity = dr["Quantity"].ToString(); addValue.stationeryPrice = dr["Price"].ToString(); addValue.stationeryName = dr["PropertyName"].ToString(); addValue.stationeryUnit = dr["ItemUnit"].ToString(); returnValue.Add(addValue); } dr.Close(); Sqlconn.Close(); } catch (Exception e) { CreatePurchase addValue = new CreatePurchase(); addValue.checkNo = "-1"; addValue.errorMsg = e.Message; returnValue.Add(addValue); } } return returnValue; }
public string[] SearchPurchaseDataCount(SearchPurchase purchaseData) { OtherDataBase sDB = new OtherDataBase(); if (int.Parse(sDB._StaffhaveRoles[3]) == 1) { return sDB.SearchPurchaseCount(purchaseData); } else { return new string[2] { _noRole, _errorMsg }; } }
private string SearchPurchaseConditionReturn(SearchPurchase purchaseData) { string ConditionReturn = ""; if (purchaseData.txtpurchaseID != null) { ConditionReturn += " AND PropertyPurchase.InputID = @txtpurchaseID "; } if (purchaseData.txtfirmName != null) { ConditionReturn += " AND PropertyPurchase.CompanyName like @txtfirmName "; } if (purchaseData.txtpurchaseDateStart != null && purchaseData.txtpurchaseDateEnd != null) { ConditionReturn += " AND PropertyPurchase.InputDate BETWEEN @txtpurchaseDateStart AND @txtpurchaseDateEnd"; } if (purchaseData.txtstationeryID != null) { ConditionReturn += " AND PropertyPurchase.PropertyID = @txtstationeryID"; } StaffDataBase sDB = new StaffDataBase(); List<string> UserFile = sDB.getStaffDataName(HttpContext.Current.User.Identity.Name); if (int.Parse(_StaffhaveRoles[4]) == 0 && UserFile[1].Length > 0) { ConditionReturn += " AND PropertyPurchase.Unit =" + UserFile[2] + " "; } return ConditionReturn; }
public List<CreatePurchase> SearchPurchaseData(int index, SearchPurchase purchaseData) { OtherDataBase sDB = new OtherDataBase(); return sDB.SearchPurchase(index, purchaseData); }