public void DeleteParts_With_FinalGoods_Test_완성품관련정보_입력시_예상되는_값이_반환되는가(bool useLike) { string exQuery = string.Format("Delete from Parts where FinishedGoodIDX in (select GoodPK as FinishedGoodIDX from Goods where GoodName='test')"); if (useLike) { exQuery = string.Format("Delete from Parts where FinishedGoodIDX in (select GoodPK as FinishedGoodIDX from Goods where GoodName like '%test%')"); } Goods g = new Goods(); g.GoodName = "test"; string query = new SQLDataQueryRepository().DeleteParts_With_FinalGoods(g, useLike); Assert.AreEqual(exQuery, query); }
public string DeleteGoods(Goods goodInfo, bool useLike) { StringBuilder sbWhere = new StringBuilder(); foreach (var prop in typeof(Goods).GetProperties()) { if (goodInfo[prop.Name]==null) { continue; } if (prop.Name.ToLower()=="item" || prop.Name=="RowCount" || prop.Name=="Page") { continue; } if (sbWhere.Length<=0) { sbWhere.Append("where "); } else { sbWhere.Append(" and "); } sbWhere.Append(prop.Name); if (prop.PropertyType==typeof(string)) { if (useLike) { sbWhere.AppendFormat(" like '%{0}%'",goodInfo[prop.Name]); } else { sbWhere.AppendFormat("='{0}'", goodInfo[prop.Name]); } } else { sbWhere.AppendFormat("={0}", goodInfo[prop.Name]); } } if (sbWhere.Length<=0) { return null; } StringBuilder sbGoods = new StringBuilder(); sbGoods.Append("delete from Goods "); sbGoods.Append(sbWhere.ToString()); return sbGoods.ToString(); }
public void DeleteGoods_Test올바른_값을_입력시_예상되는_값이_반환되는가(bool useLike) { //Arrange string exQuery = "delete from Goods where GoodPK=1 and GoodName='test'"; if (useLike) { exQuery = "delete from Goods where GoodPK=1 and GoodName like '%test%'"; } Goods g = new Goods(); g.GoodPK = 1; g.GoodName = "test"; //Act string query = new SQLDataQueryRepository().DeleteGoods(g, useLike); //Assert Assert.AreEqual(exQuery,query); }
public string DeleteParts(Goods PartsInfo, bool useLike) { IDictionary<string, string> PartsDic = new Dictionary<string, string>(); foreach (var prop in typeof(Goods).GetProperties()) { if (PartsInfo[prop.Name] == null || PartsInfo[prop.Name].ToString()==string.Empty) { continue; } PartsDic.Add(prop.Name, PartsInfo[prop.Name].ToString()); } string strWhere = MakeWhereBlock(typeof(Goods), PartsDic, useLike); string InnerView = string.Format("select GoodPK as PartIDX from Goods{0}", strWhere); string query = string.Format("Delete from Parts where PartIDX in ({0})", InnerView.ToString()); return query; }
public void SelectGoodsAndPartsAndUnitCost_Test_매개변수를_올바르게_입력시에_예상되는_쿼리_반환_하는지(bool UseLike) { //Arrange string queryNoLike = @"select * from ( select good.*,null as FinishedGoodIDX ,null as PartIDX,null as amount,ucost.* from (select * from Goods where GoodName='testName1') as good left join (select * from unitcost) as ucost on good.GoodPK=ucost.GoodIDX union all select good.*,part.*,ucost.* from (select * from Goods where GoodName='testName1') as Finished_good left join (select * from parts) as part on Finished_good.goodPK= part.FinishedGoodIDX left join (select * from goods) as good on part.PartIDX=good.goodPK left join (select * from unitcost) as ucost on part.PartIDX=ucost.GoodIDX ) limit 1 offset 2"; string queryLike = @"select * from ( select good.*,null as FinishedGoodIDX ,null as PartIDX,null as amount,ucost.* from (select * from Goods where GoodName like '%testName1%') as good left join (select * from unitcost) as ucost on good.GoodPK=ucost.GoodIDX union all select good.*,part.*,ucost.* from (select * from Goods where GoodName like '%testName1%') as Finished_good left join (select * from parts) as part on Finished_good.goodPK= part.FinishedGoodIDX left join (select * from goods) as good on part.PartIDX=good.goodPK left join (select * from unitcost) as ucost on part.PartIDX=ucost.GoodIDX ) limit 1 offset 2"; Goods g = new Goods(); g.RowCount = 1; g.Page = 3; g.GoodName = "testName1"; //Act string query = new SQLDataQueryRepository().SelectGoodsAndPartsAndUnitCost(g,UseLike); //Assert if (UseLike) { Assert.AreEqual(queryLike,query); } else { Assert.AreEqual(queryNoLike,query); } }
public void InsertGoods_Test_올바른_값을_입력시_예상되는_값이_반환되는가() { //Arrange Goods goods = new Goods(); goods.ETCInfo = "etc"; goods.GoodMaker = "maker"; goods.GoodName = "name"; goods.GoodNickName = "nickname"; goods.GoodSubName = "subname"; goods.GoodUnit = "unit"; goods.ProperStock = "1"; goods.Status = "testStatus"; string exQuery = "insert into goods(GoodName,GoodSubName,GoodMaker,GoodNickName,GoodUnit,ProperStock,ETCInfo,Status) values ('name','subname','maker','nickname','unit','1','etc','testStatus')"; //Act string query = new SQLDataQueryRepository().InsertGoods(goods); //Assert Assert.AreEqual(exQuery,query); }
public void DeleteUnitCost_Test_제품정보와_업체정보_입력시_예상되는_쿼리_반환하는지(bool useLike) { string exQuery = string.Format("Delete from UnitCost where GoodIDX in (select GoodPK as GoodIDX from Goods where GoodName='testGood') and CompanyIDX in (select CompanyPK as CompanyIDX from Companies where CompanyName='testCompany')"); if (useLike) { exQuery = string.Format("Delete from UnitCost where GoodIDX in (select GoodPK as GoodIDX from Goods where GoodName like '%testGood%') and CompanyIDX in (select CompanyPK as CompanyIDX from Companies where CompanyName like '%testCompany%')"); } Companies c = new Companies(); c.CompanyName = "testCompany"; Goods g = new Goods(); g.GoodName = "testGood"; string query = new SQLDataQueryRepository().DeleteUnitCost(g, c, useLike); Assert.AreEqual(exQuery, query); }
public string DeleteUnitCost(Goods goodInfo, Companies companyInfo, bool useLike) { IDictionary<string, string> goodDic = new Dictionary<string, string>(); if (goodInfo!=null) { foreach (var prop in typeof(Goods).GetProperties()) { if (goodInfo[prop.Name] == null || goodInfo[prop.Name].ToString() == string.Empty) { continue; } goodDic.Add(prop.Name, goodInfo[prop.Name].ToString()); } } IDictionary<string, string> companyDic = new Dictionary<string, string>(); if (companyInfo!=null) { foreach (var prop in typeof(Companies).GetProperties()) { if (companyInfo[prop.Name] == null || companyInfo[prop.Name].ToString() == string.Empty) { continue; } companyDic.Add(prop.Name, companyInfo[prop.Name].ToString()); } } StringBuilder sbWhere = new StringBuilder(); if (goodDic.Count>0) { sbWhere.Append(" where GoodIDX in ("); sbWhere.AppendFormat("select GoodPK as GoodIDX from Goods{0}", MakeWhereBlock(typeof(Goods), goodDic, useLike)); sbWhere.Append(")"); } if (companyDic.Count>0) { if (goodDic.Count>0) { sbWhere.Append(" and "); } else { sbWhere.Append(" where "); } sbWhere.Append("CompanyIDX in ("); sbWhere.AppendFormat("select CompanyPK as CompanyIDX from Companies{0}", MakeWhereBlock(typeof(Companies), companyDic, useLike)); sbWhere.Append(")"); } StringBuilder sbQuery = new StringBuilder(); sbQuery.AppendFormat("Delete from UnitCost{0}",sbWhere.ToString()); return sbQuery.ToString(); }
public void SelectGoods_Test_GoodPK를_매개변수로_줬을때_예상되는_쿼리_반환() { //Arrange Goods g = new Goods(); g.GoodPK = 1; //Act string query = new SQLDataQueryRepository().SelectGoods(g, false); //Assert Assert.AreEqual("select * from Goods where GoodPK=1 limit 0 offset 0", query); }
public string UpdateGoods(Goods goodInfo) { StringBuilder sbGoodUpdate = new StringBuilder(); sbGoodUpdate.Append("update Goods set "); bool isFirst = true; foreach (var prop in typeof(Goods).GetProperties()) { if (prop.Name.ToLower() == "item" || prop.Name == "GoodPK" || prop.Name == "Page" || prop.Name == "RowCount" | prop.Name == "orderby") { continue; } if (!isFirst) { sbGoodUpdate.Append(","); } sbGoodUpdate.Append(prop.Name); sbGoodUpdate.Append("="); if (prop.PropertyType==typeof(string)) { sbGoodUpdate.Append("'"); sbGoodUpdate.Append(goodInfo[prop.Name]); sbGoodUpdate.Append("'"); } else { sbGoodUpdate.Append(goodInfo[prop.Name]); } isFirst = false; }//End of foreach sbGoodUpdate.Append(" where GoodPK="); sbGoodUpdate.Append(goodInfo.GoodPK); return sbGoodUpdate.ToString(); }
public string SelectGoodsAndUnitCost(Goods FinishedWork, bool UseLike) { IDictionary<string, string> FinishedDic = new Dictionary<string, string>(); foreach (var prop in typeof(Goods).GetProperties()) { try { FinishedDic.Add(prop.Name, FinishedWork[prop.Name].ToString()); } catch (NullReferenceException) { continue; } } string query_Goods= GetSelectQuery(typeof(Goods),FinishedDic,UseLike,false); StringBuilder sbQuery = new StringBuilder(); sbQuery.Append("select * from (("); sbQuery.Append(query_Goods); sbQuery.Append(") as good inner join (select * from UnitCost) as ucost on good.GoodPK=ucost.GoodIDX) limit "); if (FinishedWork.RowCount!=0) { sbQuery.AppendFormat("{0} offset {1}", FinishedWork.RowCount, (FinishedWork.Page - 1) * FinishedWork.RowCount); } else { sbQuery.AppendFormat("{0} offset {1}", 10, 1); } string query = sbQuery.ToString(); return query; }
public string SelectGoodsAndSellers(Goods FinishedWork,bool useLike) { IDictionary<string, string> GoodData = new Dictionary<string, string>(); foreach (var prop in typeof(Goods).GetProperties()) { if (FinishedWork[prop.Name]==null) { continue; } GoodData.Add(prop.Name,FinishedWork[prop.Name].ToString()); } string query_Goods= GetSelectQuery(typeof(Goods),GoodData,useLike,false); StringBuilder sbQuery = new StringBuilder(); sbQuery.Append("select * from (("); sbQuery.Append(query_Goods); sbQuery.Append(") as good inner join (select * from GoodSeller) as seller on good.GoodPK=seller.GoodIDX inner join (select * from UnitCost) as ucost on seller.GoodIDX=ucost.GoodIDX and seller.SellerIDX=ucost.CompanyIDX) limit "); if (FinishedWork.RowCount != 0) { sbQuery.AppendFormat("{0} offset {1}", FinishedWork.RowCount, (FinishedWork.Page-1)*FinishedWork.RowCount); } else { sbQuery.AppendFormat("{0} offset {1}", 10, 1); } string query = sbQuery.ToString(); return query; }
public string SelectGoodsAndPartsAndUnitCost(Goods FinishedWork,bool UseLike) { IDictionary<string,string> FinalGoodDic= new Dictionary<string,string>(); foreach (var prop in typeof(Goods).GetProperties()) { try { FinalGoodDic.Add(prop.Name, FinishedWork[prop.Name].ToString()); } catch (NullReferenceException) { continue; } } string query_Goods = GetSelectQuery(typeof(Goods), FinalGoodDic, UseLike, false); StringBuilder sbQuery = new StringBuilder(); sbQuery.Append("select * from (select good.*,null as FinishedGoodIDX ,null as PartIDX,null as amount,ucost.* from("); sbQuery.Append(query_Goods); sbQuery.Append(") as good "); sbQuery.Append("left join "); sbQuery.Append("(select * from unitcost) as ucost "); sbQuery.Append("on "); sbQuery.Append("good.GoodPK=ucost.GoodIDX "); sbQuery.Append("union all "); sbQuery.Append("select good.*,part.*,ucost.* from("); sbQuery.Append(query_Goods); sbQuery.Append(") as Finished_good "); sbQuery.Append("(select * from parts) as part "); sbQuery.Append("on "); sbQuery.Append("Finished_good.goodPK= part.FinishedGoodIDX "); sbQuery.Append("left join "); sbQuery.Append("(select * from goods) as good "); sbQuery.Append("on "); sbQuery.Append("part.PartIDX=good.goodPK "); sbQuery.Append("left join "); sbQuery.Append("(select * from unitcost) as ucost "); sbQuery.Append("on "); sbQuery.Append("part.PartIDX=ucost.GoodIDX "); sbQuery.Append(") "); sbQuery.Append("limit "); if (FinishedWork.RowCount != 0) { sbQuery.AppendFormat("{0} offset {1}", FinishedWork.RowCount, (FinishedWork.Page - 1) * FinishedWork.RowCount); } else { sbQuery.AppendFormat("{0} offset {1}", 10, 1); } string query = sbQuery.ToString(); return query; }
public string SelectGoods(Goods goods,bool IsLikeSearch) { IDictionary<string, string> dataDic = null; if (goods!=null) { dataDic = new Dictionary<string, string>(); foreach (var prop in typeof(Goods).GetProperties()) { if (prop.Name.ToLower() == "item" || goods[prop.Name] == null) { continue; } dataDic.Add(prop.Name, goods[prop.Name].ToString()); } } string query= GetSelectQuery(typeof(Goods),dataDic,IsLikeSearch,true); return query; }
/// <summary> /// 상품정보테이블 만 CRUD /// </summary> /// <param name="goodInfo"></param> /// <returns></returns> public string InsertGoods(Goods goodInfo) { //[1] 상품 표준정보 입력 StringBuilder sbGoods1 = new StringBuilder(); StringBuilder sbGoods2 = new StringBuilder(); sbGoods1.Append("insert into goods("); sbGoods2.Append(" values ("); bool isFirstGoods = true; foreach (var good in typeof(Goods).GetProperties()) { if (good.Name.ToLower() == "item" || good.Name == "GoodPK" || good.Name == "Page" || good.Name == "RowCount" | good.Name == "orderby") { continue; } if (!isFirstGoods) { sbGoods1.Append(","); sbGoods2.Append(","); } sbGoods1.Append(good.Name); if (good.PropertyType==typeof(string)) { sbGoods2.Append("'"); sbGoods2.Append(goodInfo[good.Name]); sbGoods2.Append("'"); } isFirstGoods = false; } sbGoods1.Append(")"); sbGoods2.Append(")"); return sbGoods1.ToString()+sbGoods2.ToString(); }
public void SelectGoodsAndUnitCost_Test_매개변수를_올바르게_입력시에_예상되는_쿼리_반환_하는지(bool UseLike) { string exQuery = string.Empty; //Arrange if (!UseLike) { exQuery= "select * from ((select * from Goods where GoodName='testName1') as good inner join (select * from UnitCost) as ucost on good.GoodPK=ucost.GoodIDX) limit 1 offset 1"; } else { exQuery = "select * from ((select * from Goods where GoodName like '%testName1%') as good inner join (select * from UnitCost) as ucost on good.GoodPK=ucost.GoodIDX) limit 1 offset 1"; } Goods g = new Goods(); g.RowCount = 1; g.Page = 2; g.GoodName = "testName1"; //Act string query = new SQLDataQueryRepository().SelectGoodsAndUnitCost(g,UseLike); //Assert Assert.AreEqual(exQuery, query); }
public void SelectGoods_Test_GoodName를_매개변수로_줬을때_예상되는_쿼리_반환(bool isLikeMode) { //Arrange Goods g = new Goods(); g.GoodName = "test"; g.RowCount = 1; g.Page = 3; string expected = string.Empty; if (isLikeMode) { expected = "select * from Goods where GoodName like '%test%' limit 1 offset 2"; } else { expected = "select * from Goods where GoodName='test' limit 1 offset 2"; } //Act string query = new SQLDataQueryRepository().SelectGoods(g, isLikeMode); //Assert Assert.AreEqual(expected, query); }
public void DeleteSeller_Test_업체관련정보와_상품정보_입력시_예상되는_값이_반환되는가(bool useLike) { string exQuery = string.Format("Delete from GoodSeller where GoodIDX in (select GoodPK as GoodIDX from Goods where GoodName='testGood') and SellerIDX in (select CompanyPK as SellerIDX from Companies where CompanyName='testCompany')"); if (useLike) { exQuery = string.Format("Delete from GoodSeller where GoodIDX in (select GoodPK as GoodIDX from Goods where GoodName like '%testGood%') and SellerIDX in (select CompanyPK as SellerIDX from Companies where CompanyName like '%testCompany%')"); } Companies c = new Companies(); c.CompanyName = "testCompany"; Goods g = new Goods(); g.GoodName = "testGood"; string query = new SQLDataQueryRepository().DeleteSeller(g, c, useLike); Assert.AreEqual(exQuery, query); }
public void UpdateGoods_Test올바른_값을_입력시_예상되는_값이_반환되는가() { //Arrange Goods goods = new Goods(); goods.GoodPK = 1; goods.ETCInfo = "etc"; goods.GoodMaker = "maker"; goods.GoodName = "name"; goods.GoodNickName = "nickname"; goods.GoodSubName = "subname"; goods.GoodUnit = "unit"; goods.ProperStock = "1"; goods.Status = "testStatus"; string exGoodQuery = "update Goods set GoodName='name',GoodSubName='subname',GoodMaker='maker',GoodNickName='nickname',GoodUnit='unit',ProperStock='1',ETCInfo='etc',Status='testStatus' where GoodPK=1"; //act string query = new SQLDataQueryRepository().UpdateGoods(goods); //Assert Assert.AreEqual(exGoodQuery,query); }
public void DeleteUnitCost_Test_제품정보만_입력시_예상되는_쿼리_반환하는지(bool useLike) { string exQuery = string.Format("Delete from UnitCost where GoodIDX in (select GoodPK as GoodIDX from Goods where GoodName='testGood')"); if (useLike) { exQuery = string.Format("Delete from UnitCost where GoodIDX in (select GoodPK as GoodIDX from Goods where GoodName like '%testGood%')"); } Goods g = new Goods(); g.GoodName = "testGood"; string query = new SQLDataQueryRepository().DeleteUnitCost(g, null, useLike); Assert.AreEqual(exQuery, query); }
public void DeleteSeller_Test_상품관련정보_입력시_예상되는_값이_반환되는가(bool useLike) { string exQuery = string.Format("Delete from GoodSeller where GoodIDX in (select GoodPK as GoodIDX from Goods where GoodName='test')"); if (useLike) { exQuery = string.Format("Delete from GoodSeller where GoodIDX in (select GoodPK as GoodIDX from Goods where GoodName like '%test%')"); } Goods g = new Goods(); g.GoodName = "test"; string query = new SQLDataQueryRepository().DeleteSeller(g, null, useLike); Assert.AreEqual(exQuery, query); }
public string DeleteSeller(Goods goodInfo, Companies companyInfo,bool useLike) { //[1] 이너뷰 작성 string goodsWhere = string.Empty; string companyWhere = string.Empty; if (goodInfo!=null) { IDictionary<string, string> goodDic = new Dictionary<string, string>(); foreach (var prop in typeof(Goods).GetProperties()) { if (prop.Name.ToLower() == "item" || prop.Name == "RowCount" || prop.Name == "Page") { continue; } if (goodInfo[prop.Name] == null) { continue; } goodDic.Add(prop.Name, goodInfo[prop.Name].ToString()); } goodsWhere = string.Format("select GoodPK as GoodIDX from Goods{0}", MakeWhereBlock(typeof(Goods), goodDic, useLike)); } if (companyInfo!=null) { IDictionary<string, string> comDic = new Dictionary<string, string>(); foreach (var prop in typeof(Companies).GetProperties()) { if (prop.Name.ToLower() == "item" || prop.Name == "RowCount" || prop.Name == "Page") { continue; } if (companyInfo[prop.Name] == null || companyInfo[prop.Name].Replace(" ","") == string.Empty) { continue; } comDic.Add(prop.Name, companyInfo[prop.Name].ToString()); } companyWhere = string.Format("select CompanyPK as SellerIDX from Companies{0}", MakeWhereBlock(typeof(Companies), comDic, useLike)); } //[2] 이너뷰 이용한 where정 작성 StringBuilder sbWhere = new StringBuilder(); if (goodsWhere!=string.Empty) { sbWhere.Append(" where "); sbWhere.AppendFormat("GoodIDX in ({0})",goodsWhere); if (companyWhere!=string.Empty) { sbWhere.Append(" and "); } } if (companyWhere != string.Empty) { if (goodsWhere==string.Empty) { sbWhere.Append(" where "); } sbWhere.AppendFormat("SellerIDX in ({0})", companyWhere); } //[3] 쿼리작성 StringBuilder sbQuery = new StringBuilder(); sbQuery.Append("Delete from GoodSeller"); sbQuery.Append(sbWhere.ToString()); return sbQuery.ToString(); }