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