/**
* 条件文の作成
* @state conditions { mainCategory, jan, productName }
*/
private static String createCondition(String[] conditions)
{
String condition = "";
for (int i = 0; i < conditions.Length; i++)
{
if (conditions[i] != null)
{
condition = "\r\nAND ";
switch (i)
{
case 0:
condition += "main_cate_id = " + AdjustForSQL.processingString(conditions[i]);
break;
case 1:
condition += "product_cd = " + AdjustForSQL.processingString(conditions[i]);
break;
case 2:
condition += PRODUCT_NM + " = " + AdjustForSQL.processingString(conditions[i]);
break;
}
}
}
return(condition);
}
/**
* 検索SQLの接続
* @return Dictionary<string, string[]>
* string {"JAN"}
* string[] { main_cate_nm, sub_cate_nm, product_cd, product_nm, sum(quantity) }
*/
private static IDictionary <String, String[]> _select(DBConnection db, String sql, String conditionForCount)
{
Dictionary <String, String[]> shortageData = new Dictionary <String, String[]>();
SqlConnection con = db.getConnection();
SqlDataReader rd = null;
SqlCommand command = new SqlCommand()
{
Connection = con,
CommandText = sql
};
try
{
con.Open();
rd = command.ExecuteReader();
while (rd.Read())
{
String jan = rd["JAN"].ToString();
int num_of_days = dateCount(
db,
conditionForCount + "\r\nAND product_cd = " + AdjustForSQL.processingString(jan)
);
String[] data = { rd["主部門"].ToString(), rd["副部門"].ToString(), jan, rd["商品名"].ToString(), rd["欠品数"].ToString() };
shortageData.Add(jan, data);
}
Console.WriteLine(shortageData.Count);
return(shortageData);
} catch (SqlException e)
{
return(null);
} finally
{
rd.Close();
con.Close();
}
}
/**
* 条件によるデータベース検索 （欠品数 降順）
* @state term { firstDate, lastDate }
* @state conditions { mainCategory, jan, productName }
*
* @return Dictionary<string, string[]>
* string {"JAN"}
* string[] { main_cate_nm, sub_cate_nm, product_cd, product_nm, sum(quantity) }
*/
public static IDictionary <String, String[]> selectForShortage(DBConnection db, String[] term, String[] conditions)
{
String sql = "SELECT " + MAIN_CATE_NM + " \'主部門\', " + SUB_CATE_NM + " \'副部門\', " + SHORTAGE + "." + PRODUCT_CD + " \'JAN\', " + PRODUCT_NM + " \'商品名\', SUM(" + QUANTITY + ") \'欠品数\' FROM " + SHORTAGE +
"\r\nJOIN " + PRODUCT + " ON " + SHORTAGE + "." + PRODUCT_CD + " = " + PRODUCT + "." + PRODUCT_CD +
"\r\nJOIN " + SUB_CATEGORY + " ON " + PRODUCT + "." + SUB_CATE_ID + " = " + SUB_CATEGORY + "." + SUB_CATE_ID +
"\r\nJOIN " + MAIN_CATEGORY + " ON " + SUB_CATEGORY + "." + MAIN_CATE_ID + " = " + MAIN_CATEGORY + "." + MAIN_CATE_ID;
String conditionOfTerm = "\r\nWHERE " + DATE + " BETWEEN " + AdjustForSQL.processingString(term[0]) + " AND " + AdjustForSQL.processingString(term[1]);
sql += conditionOfTerm;
if (conditions != null)
{
sql += createCondition(conditions);
}
sql += "\r\nGROUP BY " + SHORTAGE + "." + PRODUCT_CD +
"\r\nORDER BY SUM(" + QUANTITY + ") desc";
return(_select(db, sql, conditionOfTerm));
}
