public static void ShowData(DataGridView tempDGV, string tempCName)
{
string sqlSTRING = "";
int iX;
try
{
Control.DataControl Con = new Control.DataControl();
OleDbConnection Cn = new OleDbConnection();
Cn = Con.GetConnection();
tempDGV.RowCount = 0;
//データリーダーを取得する
OleDbDataReader dR;
sqlSTRING = "";
sqlSTRING += "SELECT 受注.ID,受注.チラシ名 ";
sqlSTRING += "from 受注 INNER JOIN 配布エリア ON 受注.ID = 配布エリア.受注ID ";
sqlSTRING += "INNER JOIN 未配布情報 ON 配布エリア.ID = 未配布情報.配布エリアID ";
sqlSTRING += "where 受注.チラシ名 like ? ";
sqlSTRING += "group by 受注.ID,受注.チラシ名 ";
sqlSTRING += "ORDER BY 受注.ID DESC";
OleDbCommand SCom = new OleDbCommand();
SCom.CommandText = sqlSTRING;
SCom.Parameters.AddWithValue("@CName", "%" + tempCName + "%");
SCom.Connection = Cn;
dR = SCom.ExecuteReader();
//グリッドビューに表示する
iX = 0;
while (dR.Read())
{
try
{
tempDGV.Rows.Add();
tempDGV[0, iX].Value = long.Parse(dR["ID"].ToString());
tempDGV[1, iX].Value = dR["チラシ名"].ToString();
iX++;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message.ToString());
}
}
dR.Close();
Con.Close();
Cn.Close();
//if (tempDGV.RowCount <= 27)
//{
// tempDGV.Columns[1].Width = 217;
//}
//else
//{
// tempDGV.Columns[1].Width = 200;
//}
}
catch (Exception e)
{
MessageBox.Show(e.Message, "エラー", MessageBoxButtons.OK);
}
}
public static void ShowData(DataGridView tempDGV, DateTime tempDate1, DateTime tempDate2, int tempID)
{
string sqlSTRING = "";
try
{
if (tempID == 0)
{
tempDGV.Columns[3].Visible = true;
}
else
{
tempDGV.Columns[3].Visible = false;
}
Control.DataControl sdcon = new Control.DataControl();
OleDbConnection cn = new OleDbConnection();
cn = sdcon.GetConnection();
//データリーダーを取得する
OleDbDataReader dR;
sqlSTRING += "select 受注.ID,受注.受注日,受注.チラシ名,受注.単価,受注.枚数,受注.金額,受注.値引額,社員.氏名, ";
sqlSTRING += "受注.外注原価支払, 受注.外注原価支払2, 受注.外注原価支払3, 受注.外注原価営業 ";
sqlSTRING += "from 受注 left join 得意先 on 受注.得意先ID = 得意先.ID ";
sqlSTRING += "left join 社員 on 得意先.担当社員コード = 社員.ID ";
sqlSTRING += "where ";
sqlSTRING += "(受注.受注日 >= ?) and ";
sqlSTRING += "(受注.受注日 <= ?) ";
if (tempID != 0)
{
sqlSTRING += "and (得意先.担当社員コード = ?)";
}
OleDbCommand SCom = new OleDbCommand();
SCom.CommandText = sqlSTRING;
SCom.Parameters.AddWithValue("@Date1", tempDate1);
SCom.Parameters.AddWithValue("@Date2", tempDate2);
if (tempID != 0)
{
SCom.Parameters.AddWithValue("@SID", tempID);
}
SCom.Connection = cn;
dR = SCom.ExecuteReader();
//グリッドビューに表示する
int iX = 0;
int Total = 0;
decimal TotalGai = 0;
decimal TotalArari = 0;
decimal TotalGai2 = 0;
decimal TotalArari2 = 0;
decimal TotalGai3 = 0;
decimal TotalArari3 = 0;
decimal TotalGai4 = 0;
decimal TotalArari4 = 0;
decimal TotalArariSai = 0;
DateTime jDate = DateTime.Today;;
tempDGV.RowCount = 0;
while (dR.Read())
{
tempDGV.Rows.Add();
tempDGV[0, iX].Value = Convert.ToDateTime(dR["受注日"].ToString());
jDate = Convert.ToDateTime(dR["受注日"].ToString());
tempDGV[1, iX].Value = dR["ID"].ToString();
tempDGV[2, iX].Value = dR["チラシ名"].ToString();
tempDGV[3, iX].Value = dR["氏名"].ToString();
tempDGV[4, iX].Value = double.Parse(dR["単価"].ToString());
tempDGV[5, iX].Value = int.Parse(dR["枚数"].ToString(), System.Globalization.NumberStyles.Any);
// 2016/01/04 金額に値引を反映する
tempDGV[6, iX].Value = int.Parse(dR["金額"].ToString(), System.Globalization.NumberStyles.Any) - int.Parse(dR["値引額"].ToString(), System.Globalization.NumberStyles.Any);
// 営業原価 外注費 2015/09/18
//decimal gaichuhi = ((decimal)Utility.strToInt(dR["枚数"].ToString())) * Utility.strToDecimal(dR["外注原価営業"].ToString());
decimal gaichuhi = Utility.strToDecimal(dR["外注原価営業"].ToString()); // 2015/12/06
tempDGV[7, iX].Value = gaichuhi;
// 2016/01/04 金額に値引を反映する
decimal aRari = ((decimal)Utility.strToLong(dR["金額"].ToString())) - ((decimal)Utility.strToLong(dR["値引額"].ToString())) - gaichuhi;
tempDGV[8, iX].Value = aRari;
// 外注費 2015/09/18
decimal gaichuhi2 = Utility.strToDecimal(dR["外注原価支払"].ToString()); // 2015/12/06
tempDGV[9, iX].Value = gaichuhi2;
// 外注費2 2016/10/24
decimal gaichuhi3 = Utility.strToDecimal(dR["外注原価支払2"].ToString());
tempDGV[10, iX].Value = gaichuhi3;
// 外注費3 2016/10/24
decimal gaichuhi4 = Utility.strToDecimal(dR["外注原価支払3"].ToString());
tempDGV[11, iX].Value = gaichuhi4;
// 2016/01/04 金額に値引を反映する
decimal aRari2 = ((decimal)Utility.strToLong(dR["金額"].ToString())) - ((decimal)Utility.strToLong(dR["値引額"].ToString())) - gaichuhi2 - gaichuhi3 - gaichuhi4;
tempDGV[12, iX].Value = aRari2;
// 粗利差異 2015/09/18
tempDGV[13, iX].Value = aRari - aRari2;
Total += (int.Parse(dR["金額"].ToString(), System.Globalization.NumberStyles.Any) - int.Parse(dR["値引額"].ToString(), System.Globalization.NumberStyles.Any));
TotalGai += gaichuhi; // 営業原価 2015/09/18
TotalArari += aRari; // 粗利１合計 2015/09/18
TotalGai2 += gaichuhi2; // 外注費2合計 2015/09/18
TotalGai3 += gaichuhi3; // 外注費3合計 2016/11/08
TotalGai4 += gaichuhi4; // 外注費4合計 2016/11/08
TotalArari2 += aRari2; // 粗利２合計 2015/09/18
TotalArariSai += (aRari -= aRari2); // 差異合計 2016/10/24
iX++;
}
//合計行
if (tempDGV.RowCount == 0)
{
MessageBox.Show("該当するデータがありませんでした", MESSAGE_CAPTION);
}
else
{
tempDGV.Rows.Add();
tempDGV[0, iX].Value = "";
tempDGV[1, iX].Value = "";
tempDGV[2, iX].Value = "合計 : " + tempDGV.RowCount.ToString("#,##0") + " 件";
tempDGV[3, iX].Value = "";
tempDGV[4, iX].Value = "";
tempDGV[5, iX].Value = "";
tempDGV[6, iX].Value = Total;
tempDGV[7, iX].Value = TotalGai; // 2015/09/18
tempDGV[8, iX].Value = TotalArari; // 2015/09/18
tempDGV[9, iX].Value = TotalGai2; // 2015/09/18
tempDGV[10, iX].Value = TotalGai3; // 2015/09/18
tempDGV[11, iX].Value = TotalGai4; // 2016/10/24
tempDGV[12, iX].Value = TotalArari2; // 2016/10/24
tempDGV[13, iX].Value = TotalArariSai; // 2015/09/18
}
//if (tempDGV.RowCount <= 25)
//{
// tempDGV.Columns[2].Width = 318;
//}
//else
//{
// tempDGV.Columns[2].Width = 301;
//}
dR.Close();
sdcon.Close();
cn.Close();
tempDGV.CurrentCell = null;
}
catch (Exception e)
{
MessageBox.Show(e.Message, "エラー", MessageBoxButtons.OK);
}
}
private void NodesShow(int tempYear, int tempMonth)
{
string sqlSTR;
string NodeName1, nDate, nKanryo, nName;
long wID;
int nIndex = 0;
int nCnt = 0;
//ツリービュークリア
treeView1.Nodes.Clear();
OleDbDataReader dR;
Control.DataControl Con = new Control.DataControl();
OleDbConnection cn = new OleDbConnection();
cn = Con.GetConnection();
sqlSTR = "";
sqlSTR += "select 受注.ID, 受注.チラシ名, 受注.枚数, 配布エリア.町名ID, 町名.名称 AS 町名,";
sqlSTR += "配布指示.配布日, 配布員.氏名,配布エリア.予定枚数, 配布エリア.報告枚数, ";
sqlSTR += "配布エリア.報告残数, 配布エリア.完了区分 ";
sqlSTR += "from 受注 left join 配布エリア ";
sqlSTR += "on 受注.ID = 配布エリア.受注ID left join 町名 ";
sqlSTR += "on 配布エリア.町名ID = 町名.ID left join 配布指示 ";
sqlSTR += "on 配布エリア.配布指示ID = 配布指示.ID left join 配布員 ";
sqlSTR += "on 配布指示.配布員ID = 配布員.ID ";
sqlSTR += "where (受注.受注種別ID = 1) and ";
sqlSTR += "(year(受注日) = ?) AND (month(受注日) = ?) ";
sqlSTR += "order by 受注.ID desc,町名ID";
OleDbCommand SCom = new OleDbCommand();
SCom.CommandText = sqlSTR;
SCom.Parameters.AddWithValue("@year", tempYear);
SCom.Parameters.AddWithValue("@month", tempMonth);
SCom.Connection = cn;
dR = SCom.ExecuteReader();
if (dR.HasRows == true)
{
treeView1.Nodes.Add("受注確定書");
treeView1.Nodes[0].ImageIndex = 4; //アイコンの設定
wID = 0;
nIndex = 0;
while (dR.Read())
{
if (wID != long.Parse(dR["ID"].ToString()))
{
//受注確定書毎のエリア数
if (wID != 0)
{
treeView1.Nodes[0].Nodes[nIndex - 1].Text += "(" + nCnt.ToString() + ")";
}
//受注確定書情報
NodeName1 = dR["ID"].ToString() + " " + dR["チラシ名"].ToString() + " " + dR["枚数"].ToString();
treeView1.Nodes[0].Nodes.Add(NodeName1);
treeView1.Nodes[0].Nodes[nIndex].NodeFont = new Font("ＭＳ Ｐゴシック", 10, FontStyle.Regular);
treeView1.Nodes[0].Nodes[nIndex].ImageIndex = 2; //アイコンの設定
nIndex++;
nCnt = 0;
}
//配布エリア情報
if (dR["町名"] != DBNull.Value)
{
if (dR["配布日"] == DBNull.Value)
{
nDate = "----/--/--";
}
else
{
nDate = DateTime.Parse(dR["配布日"].ToString()).ToShortDateString();
}
if (dR["氏名"] == DBNull.Value)
{
nName = "********";
}
else
{
nName = dR["氏名"].ToString();
}
if (dR["完了区分"].ToString() == "0")
{
nKanryo = "";
}
else
{
nKanryo = "完了";
}
NodeName1 = int.Parse(dR["町名ID"].ToString()).ToString("d4") + " " + dR["町名"].ToString() + " " + nDate + " " + nName + " " + dR["予定枚数"].ToString() + " " + nKanryo;
treeView1.Nodes[0].Nodes[nIndex - 1].Nodes.Add(NodeName1);
treeView1.Nodes[0].Nodes[nIndex - 1].Nodes[nCnt].ImageIndex = 1; //アイコンの設定
//未完了は赤表示
if (nKanryo == "")
{
treeView1.Nodes[0].Nodes[nIndex - 1].Nodes[nCnt].ForeColor = Color.Red;
treeView1.Nodes[0].Nodes[nIndex - 1].ForeColor = Color.Red;
}
nCnt++;
}
else
{
treeView1.Nodes[0].Nodes[nIndex - 1].ForeColor = Color.Red;
}
wID = long.Parse(dR["ID"].ToString());
}
treeView1.Nodes[0].Nodes[nIndex - 1].Text += "(" + nCnt.ToString() + ")";
}
else
{
MessageBox.Show("該当する受注確定書がありません", MESSAGE_CAPTION, MessageBoxButtons.OK, MessageBoxIcon.Information);
}
dR.Close();
Con.Close();
if (nIndex > 0)
{
button2.Enabled = true;
button3.Enabled = true;
}
}
public static void ShowData(DataGridView tempDGV, int tempSel, string tempCName)
{
string sqlSTRING = "";
int iX;
try
{
tempDGV.RowCount = 0;
//データリーダーを取得する
Control.DataControl Con = new Control.DataControl();
OleDbConnection cn = new OleDbConnection();
cn = Con.GetConnection();
OleDbDataReader dR;
sqlSTRING = "";
sqlSTRING += "select 受注.ID,社員.氏名,得意先.略称,受注.チラシ名,受注.税込金額,";
sqlSTRING += "受注.枚数 as 納品数,h_tbl.配布件数,";
sqlSTRING += "受注.枚数 - h_tbl.配布件数 as 残枚数 ";
sqlSTRING += "from 受注 left join ";
sqlSTRING += "(select 受注ID,sum(予定枚数) as 配布件数 ";
sqlSTRING += "from 配布エリア ";
sqlSTRING += "where 完了区分 = 1 ";
sqlSTRING += "group by 受注ID ) as h_tbl ";
sqlSTRING += "on 受注.ID = h_tbl.受注ID left join 得意先 ";
sqlSTRING += "on 受注.得意先ID = 得意先.ID left join 社員 ";
sqlSTRING += "on 得意先.担当社員コード = 社員.ID ";
sqlSTRING += "where (受注.受注種別ID = 1) ";
switch (tempSel)
{
case 1:
sqlSTRING += "and (((受注.枚数 - h_tbl.配布件数) > 0) or h_tbl.配布件数 is null) ";
break;
case 2:
sqlSTRING += "and (受注.枚数 - h_tbl.配布件数 = 0) ";
break;
}
if (tempCName.Trim().Length > 0)
{
sqlSTRING += "and (受注.チラシ名 like ?)";
}
sqlSTRING += "order by 受注.ID desc";
OleDbCommand SCom = new OleDbCommand();
SCom.CommandText = sqlSTRING;
if (tempCName.Trim().Length > 0)
{
SCom.Parameters.AddWithValue("@CName", "%" + tempCName + "%");
}
SCom.Connection = cn;
//配布進捗状況のデータリーダーを取得する
//Control.FreeSql fCon = new Control.FreeSql();
//dR = fCon.free_dsReader(sqlSTRING);
dR = SCom.ExecuteReader();
//グリッドビューに表示する
iX = 0;
while (dR.Read())
{
try
{
tempDGV.Rows.Add();
tempDGV[0, iX].Value = long.Parse(dR["ID"].ToString());
tempDGV[1, iX].Value = dR["略称"].ToString() + "";
tempDGV[2, iX].Value = dR["チラシ名"].ToString();
tempDGV[3, iX].Value = dR["氏名"].ToString() + "";
tempDGV[4, iX].Value = int.Parse(dR["税込金額"].ToString(), System.Globalization.NumberStyles.Any);
tempDGV[5, iX].Value = int.Parse(dR["納品数"].ToString(), System.Globalization.NumberStyles.Any);
if (dR["配布件数"] == DBNull.Value)
{
tempDGV[6, iX].Value = 0;
}
else
{
tempDGV[6, iX].Value = int.Parse(dR["配布件数"].ToString(), System.Globalization.NumberStyles.Any);
}
if (dR["残枚数"] == DBNull.Value)
{
tempDGV[7, iX].Value = int.Parse(dR["納品数"].ToString(), System.Globalization.NumberStyles.Any);
}
else
{
tempDGV[7, iX].Value = int.Parse(dR["残枚数"].ToString(), System.Globalization.NumberStyles.Any);
}
//配布完了日
if (int.Parse(tempDGV[7, iX].Value.ToString(), System.Globalization.NumberStyles.Any) == 0)
{
string sqlSTR;
OleDbDataReader r;
Control.FreeSql rCon = new Control.FreeSql();
sqlSTR = "";
sqlSTR += "select max(配布指示.配布日) as 完了日 ";
sqlSTR += "from 配布エリア inner join 配布指示 ";
sqlSTR += "on 配布エリア.配布指示ID = 配布指示.ID ";
sqlSTR += "where ";
sqlSTR += "(配布エリア.受注ID = " + long.Parse(dR["ID"].ToString()) + ") and ";
sqlSTR += "(配布エリア.完了区分 = 1)";
r = rCon.free_dsReader(sqlSTR);
while (r.Read())
{
if (r["完了日"] != DBNull.Value)
{
tempDGV[8, iX].Value = DateTime.Parse(r["完了日"].ToString()).ToShortDateString();
}
else
{
tempDGV[8, iX].Value = "";
}
}
r.Close();
rCon.Close();
}
else
{
tempDGV[8, iX].Value = "";
}
iX++;
//frmP.valueCount = iX;
//frmP.ShowProgress();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message.ToString());
}
}
dR.Close();
Con.Close();
cn.Close();
//fCon.Close();
//frmP.Close();
//frmP.Dispose();
//if (tempDGV.RowCount <= 12)
//{
// tempDGV.Columns[3].Width = 97;
//}
//else
//{
// tempDGV.Columns[3].Width = 80;
//}
}
catch (Exception e)
{
MessageBox.Show(e.Message, "エラー", MessageBoxButtons.OK);
}
}
public static void ShowData(DataGridView tempDGV, int tempYear, int tempMonth, int temprb, string cName, Int64 orderNum)
{
string sqlSTRING = "";
int iX;
try
{
//カーソル表示を待機状態
Cursor.Current = Cursors.WaitCursor;
tempDGV.RowCount = 0;
//データリーダーを取得する
Control.DataControl Con = new Control.DataControl();
OleDbConnection cn = new OleDbConnection();
cn = Con.GetConnection();
OleDbDataReader dR;
sqlSTRING = "";
sqlSTRING += "select 受注.ID,受注.チラシ名,受注.納品予定日,受注.配布開始日,";
sqlSTRING += "受注.配布終了日,受注.枚数,t_tbl.配布数,";
sqlSTRING += "受注.枚数 - t_tbl.配布数 AS 残数,";
sqlSTRING += "k_tbl.完了配布数,";
sqlSTRING += "受注.枚数 - k_tbl.完了配布数 AS 完了残数,";
sqlSTRING += "受注.外注依頼日支払, 受注.外注委託枚数 ";
sqlSTRING += "from 受注 inner join ";
sqlSTRING += "(SELECT 受注_1.ID,SUM(配布エリア.予定枚数) AS 配布数 ";
sqlSTRING += "from 受注 AS 受注_1 INNER JOIN 配布エリア ";
sqlSTRING += "ON 受注_1.ID = 配布エリア.受注ID ";
sqlSTRING += "where (配布エリア.配布指示ID <> 0) ";
sqlSTRING += "GROUP BY 受注_1.ID) AS t_tbl ";
sqlSTRING += "ON 受注.ID = t_tbl.ID ";
sqlSTRING += "left join ";
sqlSTRING += "(SELECT 受注_2.ID,SUM(配布エリア.予定枚数) AS 完了配布数 ";
sqlSTRING += "from 受注 AS 受注_2 INNER JOIN 配布エリア ";
sqlSTRING += "ON 受注_2.ID = 配布エリア.受注ID ";
sqlSTRING += "where (配布エリア.配布指示ID <> 0) and (配布エリア.完了区分 = 1)";
sqlSTRING += "GROUP BY 受注_2.ID) AS k_tbl ";
sqlSTRING += "ON 受注.ID = k_tbl.ID ";
sqlSTRING += "where (受注種別ID = 1) ";
sqlSTRING += "and (受注.外注依頼日支払 is Null) "; // 2015/11/19
if (temprb == 1)
{
sqlSTRING += "and (year(受注.納品予定日) = ?) and (month(受注.納品予定日) = ?) ";
}
//sqlSTRING += "order by 受注.納品予定日 DESC";
// 外注渡し分 2015/11/19
sqlSTRING += "union ";
sqlSTRING += "select 受注.ID,受注.チラシ名,受注.納品予定日,受注.配布開始日,";
sqlSTRING += "受注.配布終了日, 受注.枚数, 0 as 配布数,";
sqlSTRING += "受注.枚数 - 0 AS 残数,";
sqlSTRING += "0 as 完了配布数,";
sqlSTRING += "受注.枚数 - 受注.外注委託枚数 AS 完了残数,";
sqlSTRING += "受注.外注依頼日支払, 受注.外注委託枚数 ";
sqlSTRING += "from 受注 ";
sqlSTRING += "where (受注種別ID = 1) ";
sqlSTRING += "and (受注.外注依頼日支払 > '2000/01/01') ";
if (temprb == 1)
{
sqlSTRING += "and (year(受注.納品予定日) = ?) and (month(受注.納品予定日) = ?) ";
}
sqlSTRING += "order by 受注.納品予定日 DESC";
//配布指示データのデータリーダーを取得する
OleDbCommand SCom = new OleDbCommand();
SCom.CommandText = sqlSTRING;
if (temprb == 1)
{
SCom.Parameters.AddWithValue("@year", tempYear);
SCom.Parameters.AddWithValue("@month", tempMonth);
SCom.Parameters.AddWithValue("@year", tempYear);
SCom.Parameters.AddWithValue("@month", tempMonth);
}
SCom.Connection = cn;
dR = SCom.ExecuteReader();
//グリッドビューに表示する
iX = 0;
while (dR.Read())
{
try
{
// チラシ名検索 2015/09/18
if (cName != string.Empty)
{
if (!dR["チラシ名"].ToString().Contains(cName))
{
continue;
}
}
// 受注番号検索 2015/09/18
if (orderNum != 0)
{
if (!dR["ID"].ToString().Contains(orderNum.ToString()))
{
continue;
}
}
tempDGV.Rows.Add();
tempDGV[0, iX].Value = dR["ID"].ToString();
tempDGV[1, iX].Value = dR["チラシ名"].ToString();
if (dR["納品予定日"] == DBNull.Value)
{
tempDGV[2, iX].Value = "";
}
else
{
tempDGV[2, iX].Value = DateTime.Parse(dR["納品予定日"].ToString()).ToShortDateString();
}
tempDGV[3, iX].Value = DateTime.Parse(dR["配布開始日"].ToString()).ToShortDateString() + "〜" + DateTime.Parse(dR["配布終了日"].ToString()).ToShortDateString();
tempDGV[4, iX].Value = int.Parse(dR["枚数"].ToString());
tempDGV[5, iX].Value = int.Parse(dR["配布数"].ToString());
tempDGV[6, iX].Value = int.Parse(dR["残数"].ToString());
if (dR["完了配布数"] == DBNull.Value)
{
tempDGV[7, iX].Value = 0;
tempDGV[8, iX].Value = int.Parse(dR["枚数"].ToString());
}
else
{
tempDGV[7, iX].Value = int.Parse(dR["完了配布数"].ToString());
tempDGV[8, iX].Value = int.Parse(dR["完了残数"].ToString());
}
// 外注先に渡したとき 2015/08/11
if (dR["外注依頼日支払"] != DBNull.Value)
{
// 委託枚数指定がないとき
if (Utility.strToInt(dR["外注委託枚数"].ToString()) == global.FLGOFF)
{
tempDGV[7, iX].Value = int.Parse(dR["枚数"].ToString()); // 2015/11/19
tempDGV[8, iX].Value = "0";
}
else
{
// 2015/11/19
tempDGV[7, iX].Value = dR["外注委託枚数"].ToString();
// 委託枚数指定のとき、枚数から委託枚数を差し引いた数を完了残数
int m = Utility.strToInt(dR["枚数"].ToString()) - Utility.strToInt(dR["外注委託枚数"].ToString());
tempDGV[8, iX].Value = m;
}
}
iX++;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message.ToString());
}
}
dR.Close();
cn.Close();
Con.Close();
if (tempDGV.RowCount <= 27)
{
tempDGV.Columns[1].Width = 310;
}
else
{
tempDGV.Columns[1].Width = 293;
}
//カーソル表示を戻す
Cursor.Current = Cursors.Default;
}
catch (Exception e)
{
//カーソル表示を戻す
Cursor.Current = Cursors.Default;
MessageBox.Show(e.Message, "エラー", MessageBoxButtons.OK);
}
}
public static void ShowData(DataGridView tempDGV, int temptYear, int temptMonth, int tempzYear, int tempzMonth, int tempofficeID)
{
string sqlSTRING = "";
DateTime sDate;
const int GYOSU = 32;
try
{
Control.DataControl sdcon = new Control.DataControl();
OleDbConnection cn = new OleDbConnection();
cn = sdcon.GetConnection();
//データリーダーを取得する
OleDbDataReader dR;
sqlSTRING += "select 配布日,事業所ID,count(distinct 配布員ID) AS 配布員数,SUM(売上) AS 売上, ";
sqlSTRING += "SUM(原価) AS 原価, SUM(売上) - SUM(原価) AS 収支 ";
sqlSTRING += "from ";
sqlSTRING += "(SELECT TOP (100) PERCENT 配布指示.配布日, 配布指示.配布員ID,";
sqlSTRING += "事業所.ID AS 事業所ID,受注.単価,配布エリア.配布単価,";
sqlSTRING += "配布エリア.実配布枚数,配布エリア.予定枚数,";
sqlSTRING += "受注.単価 * 配布エリア.予定枚数 AS 売上,";
sqlSTRING += "配布エリア.配布単価 * 配布エリア.実配布枚数 AS 原価 ";
sqlSTRING += "from 配布指示 INNER JOIN 配布エリア ";
sqlSTRING += "ON 配布指示.ID = 配布エリア.配布指示ID INNER JOIN 受注 ";
sqlSTRING += "ON 配布エリア.受注ID = 受注.ID INNER JOIN 配布員 ";
sqlSTRING += "ON 配布指示.配布員ID = 配布員.ID LEFT OUTER JOIN 事業所 ";
sqlSTRING += "ON 配布員.事業所コード = 事業所.ID ";
sqlSTRING += "where ";
sqlSTRING += "(YEAR(配布指示.配布日) = ?) AND (MONTH(配布指示.配布日) = ?) AND ";
sqlSTRING += "(事業所.ID = ?) OR ";
sqlSTRING += "(YEAR(配布指示.配布日) = ?) AND (MONTH(配布指示.配布日) = ?) AND ";
sqlSTRING += "(事業所.ID = ?) ";
sqlSTRING += "order by 配布指示.配布日, 配布指示.配布員ID) AS sel_tbl ";
sqlSTRING += "group by 配布日, 事業所ID";
OleDbCommand SCom = new OleDbCommand();
SCom.CommandText = sqlSTRING;
SCom.Parameters.AddWithValue("@year1", temptYear);
SCom.Parameters.AddWithValue("@month1", temptMonth);
SCom.Parameters.AddWithValue("@officeID1", tempofficeID);
SCom.Parameters.AddWithValue("@year2", tempzYear);
SCom.Parameters.AddWithValue("@month2", tempzMonth);
SCom.Parameters.AddWithValue("@officeID2", tempofficeID);
SCom.Connection = cn;
dR = SCom.ExecuteReader();
//グリッドビューに表示する
int iX = 0;
double gzUri = 0;
double gtUri = 0;
double gzShushi = 0;
double gtShushi = 0;
double gzNin = 0;
double gtNin = 0;
//グリッド作成
tempDGV.RowCount = GYOSU;
//初期化(ゼロセット）
foreach (DataGridViewRow iRow in tempDGV.Rows)
{
foreach (DataGridViewColumn iColumn in tempDGV.Columns)
{
if (iColumn.Index == 1)
{
tempDGV[iColumn.Index, iRow.Index].Value = "";
}
else
{
tempDGV[iColumn.Index, iRow.Index].Value = (double)(0);
}
}
}
//日付をセット
for (int i = 0; i < GYOSU; i++)
{
int rDay;
string rDate;
rDay = i + 1;
//対象日付
rDate = temptYear.ToString() + "/" + temptMonth.ToString() + "/" + rDay.ToString();
if (DateTime.TryParse(rDate, out sDate) == true)
{
//日付
tempDGV[0, i].Value = rDay.ToString("00");
//天候
OleDbDataReader dRt;
Control.天候 sTenkou = new Control.天候();
dRt = sTenkou.FillBy("where 日付 = '" + sDate.ToShortDateString() + "'");
while (dRt.Read())
{
tempDGV[1, i].Value = dRt["天候"].ToString() + "";
}
dRt.Close();
sTenkou.Close();
}
}
//データ表示
while (dR.Read())
{
iX = DateTime.Parse(dR["配布日"].ToString()).Day - 1;
tempDGV[0, iX].Value = DateTime.Parse(dR["配布日"].ToString()).Day; //日付
//前月or当月の判断
if (DateTime.Parse(dR["配布日"].ToString()).Month == temptMonth) //当月
{
tempDGV[3, iX].Value = double.Parse(dR["売上"].ToString(), System.Globalization.NumberStyles.Any); //売上
tempDGV[7, iX].Value = double.Parse(dR["収支"].ToString(), System.Globalization.NumberStyles.Any); //収支
tempDGV[11, iX].Value = double.Parse(dR["配布員数"].ToString(), System.Globalization.NumberStyles.Any); //配布員数
//合計
gtUri += double.Parse(dR["売上"].ToString(), System.Globalization.NumberStyles.Any); //売上
gtShushi += double.Parse(dR["収支"].ToString(), System.Globalization.NumberStyles.Any); //収支
gtNin += double.Parse(dR["配布員数"].ToString(), System.Globalization.NumberStyles.Any); //配布員数
}
else //前月
{
tempDGV[2, iX].Value = double.Parse(dR["売上"].ToString(), System.Globalization.NumberStyles.Any); //売上
tempDGV[6, iX].Value = double.Parse(dR["収支"].ToString(), System.Globalization.NumberStyles.Any); //収支
tempDGV[10, iX].Value = double.Parse(dR["配布員数"].ToString(), System.Globalization.NumberStyles.Any); //収支
//合計
gzUri += double.Parse(dR["売上"].ToString(), System.Globalization.NumberStyles.Any); //売上
gzShushi += double.Parse(dR["収支"].ToString(), System.Globalization.NumberStyles.Any); //収支
gzNin += double.Parse(dR["配布員数"].ToString(), System.Globalization.NumberStyles.Any); //配布員数
}
}
//合計行
if (tempDGV.RowCount == 0)
{
MessageBox.Show("該当するデータがありませんでした", MESSAGE_CAPTION);
}
else
{
tempDGV[0, GYOSU - 1].Value = "計";
tempDGV[2, GYOSU - 1].Value = gzUri;
tempDGV[3, GYOSU - 1].Value = gtUri;
tempDGV[6, GYOSU - 1].Value = gzShushi;
tempDGV[7, GYOSU - 1].Value = gtShushi;
tempDGV[10, GYOSU - 1].Value = gzNin;
tempDGV[11, GYOSU - 1].Value = gtNin;
}
//if (tempDGV.RowCount <= 25)
//{
// tempDGV.Columns[2].Width = 318;
//}
//else
//{
// tempDGV.Columns[2].Width = 301;
//}
dR.Close();
sdcon.Close();
cn.Close();
tempDGV.CurrentCell = null;
}
catch (Exception e)
{
MessageBox.Show(e.Message, "エラー", MessageBoxButtons.OK);
}
}
private void HaifuUpdate(int sRow, int eRow)
{
try
{
HaifuShijiData(); //配布指示クラスにデータセット
Control.DataControl Con;
OleDbConnection cn;
OleDbTransaction tran;
OleDbCommand SCom;
//IDを採番
string sqlStr = "";
int gID = (int)(1);
sqlStr = "select max(ID) as ID from 配布指示 ";
OleDbDataReader dR;
Control.FreeSql fCon = new Control.FreeSql();
dR = fCon.free_dsReader(sqlStr);
while (dR.Read())
{
if (dR["ID"] == DBNull.Value)
{
gID = (int)(1);
}
else
{
gID = Int32.Parse(dR["ID"].ToString()) + 1;
}
}
dR.Close();
fCon.Close();
//IDを設定
cMaster.ID = gID;
//登録処理
Con = new Control.DataControl();
cn = new OleDbConnection();
cn = Con.GetConnection();
//トランザクション開始
tran = cn.BeginTransaction();
SCom = new OleDbCommand();
SCom.Connection = cn;
SCom.Transaction = tran;
try
{
//配布指示データ登録処理
sqlStr = "";
sqlStr += "insert into 配布指示 ";
sqlStr += "(ID,配布日,入力日,配布員ID,交通費,交通区間開始,交通区間終了,";
sqlStr += "配布開始時刻,配布終了時刻,終了レポート,未配布区分,未配布理由,";
sqlStr += "登録年月日,変更年月日) ";
sqlStr += "values (";
sqlStr += cMaster.ID + ",";
sqlStr += "'" + cMaster.配布日 + "',";
sqlStr += "'" + cMaster.入力日 + "',";
sqlStr += cMaster.配布員ID + ",";
sqlStr += cMaster.交通費 + ",";
sqlStr += "'" + cMaster.交通区間開始 + "',";
sqlStr += "'" + cMaster.交通区間終了 + "',";
sqlStr += "'" + cMaster.配布開始時刻 + "',";
sqlStr += "'" + cMaster.配布終了時刻 + "',";
sqlStr += "'" + cMaster.終了レポート + "',";
sqlStr += "'" + cMaster.未配布区分 + "',";
sqlStr += "'" + cMaster.未配布理由 + "',";
sqlStr += "'" + cMaster.登録年月日 + "',";
sqlStr += "'" + cMaster.更年月日 + "')";
SCom.CommandText = sqlStr;
SCom.ExecuteNonQuery();
//配布エリアデータ更新
string sID;
const string sSTATUS = "2";
for (int i = sRow; i <= eRow; i++)
{
sID = dataGridView1[1, i].Value.ToString();
sqlStr = "";
sqlStr += "update 配布エリア ";
sqlStr += "set ";
sqlStr += "配布指示ID = " + gID.ToString() + ",";
sqlStr += "実残数 = 予定枚数,";
sqlStr += "報告残数 = 予定枚数,";
sqlStr += "ステータス = " + sSTATUS + ",";
sqlStr += "変更年月日 = '" + DateTime.Today + "' ";
sqlStr += "where (配布エリア.ID = " + sID + ") and ";
sqlStr += "(ステータス <> 0)";
//if (dataGridView1[0, i].Value.ToString() == "2")
//{
// sqlStr += "(ステータス <> 0)";
//}
SCom.CommandText = sqlStr;
SCom.ExecuteNonQuery();
}
tran.Commit();
//更新結果書き込み
UpdateFlg(sRow, eRow, UPDATE_OK);
//MessageBox.Show("新規登録されました", MESSAGE_CAPTION, MessageBoxButtons.OK, MessageBoxIcon.Information);
}
catch (Exception ex)
{
tran.Rollback();
//更新結果書き込み
UpdateFlg(sRow, eRow, UPDATE_NO);
MessageBox.Show(ex.Message, MESSAGE_CAPTION, MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
MessageBox.Show("登録に失敗しました。ロールバックしました", MESSAGE_CAPTION, MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
//未登録配布データのステータスを戻す
//StatusBack();
}
cn.Close();
Con.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message.ToString(), "更新処理", MessageBoxButtons.OK, MessageBoxIcon.Stop);
}
}
private void NodesShow(int tempYear, int tempMonth)
{
string sqlSTR;
string NodeName1, nKanryo;
string nDate = "";
long wID;
int nIndex = 0;
int nCnt = 0;
int PrgMax;
int PrgVal = 0;
//ツリービュークリア
treeView1.Nodes.Clear();
OleDbDataReader dR;
Control.DataControl Con = new Control.DataControl();
OleDbConnection cn = new OleDbConnection();
cn = Con.GetConnection();
//データ件数取得
sqlSTR = "";
sqlSTR += "select count(*) as Cnt ";
sqlSTR += "from 配布指示 LEFT OUTER JOIN 配布エリア ";
sqlSTR += "ON 配布指示.ID = 配布エリア.配布指示ID ";
sqlSTR += "where (year(配布指示.配布日) = " + tempYear.ToString() + ") and (month(配布指示.配布日) = " + tempMonth.ToString() + ")";
OleDbCommand SCom = new OleDbCommand();
SCom.CommandText = sqlSTR;
SCom.Connection = cn;
dR = SCom.ExecuteReader();
dR.Read();
PrgMax = int.Parse(dR["Cnt"].ToString());
dR.Close();
//プログレスバーMAX件数,MIN件数設定
progressBar1.Maximum = PrgMax;
progressBar1.Minimum = 0;
//データ取得
sqlSTR = "";
sqlSTR += "select 配布指示.ID as 配布指示ID, 配布指示.配布日 as 配布指示配布日, ";
sqlSTR += "配布員.氏名, 受注.チラシ名, 町名.ID AS 町名コード,";
sqlSTR += "町名.名称 as 町名名称, 配布エリア.予定枚数, 配布エリア.配布単価, ";
sqlSTR += "配布エリア.報告枚数, 配布エリア.報告残数,配布エリア.完了区分 ";
sqlSTR += "from 配布指示 LEFT OUTER JOIN 配布エリア ";
sqlSTR += "ON 配布指示.ID = 配布エリア.配布指示ID LEFT OUTER JOIN ";
sqlSTR += "受注 ON 配布エリア.受注ID = 受注.ID LEFT OUTER JOIN ";
sqlSTR += "配布員 ON 配布指示.配布員ID = 配布員.ID LEFT OUTER JOIN ";
sqlSTR += "町名 ON 配布エリア.町名ID = 町名.ID ";
sqlSTR += "where (year(配布指示.配布日) = ?) and (month(配布指示.配布日) = ?) ";
sqlSTR += "ORDER BY 配布指示.ID DESC,配布エリア.受注ID";
//OleDbCommand SCom = new OleDbCommand();
SCom.CommandText = sqlSTR;
SCom.Parameters.AddWithValue("@year", tempYear);
SCom.Parameters.AddWithValue("@month", tempMonth);
SCom.Connection = cn;
dR = SCom.ExecuteReader();
if (dR.HasRows == true)
{
treeView1.Nodes.Add("配布指示書");
treeView1.Nodes[0].ImageIndex = 4; //アイコンの設定
wID = 0;
nIndex = 0;
//プログレスバー表示
progressBar1.Visible = true;
//データ読み込み
while (dR.Read())
{
//プログレスバー進行状況表示
PrgVal++;
progressBar1.Value = PrgVal;
if (wID != long.Parse(dR["配布指示ID"].ToString()))
{
//配布指示書毎のエリア数
if (wID != 0)
{
treeView1.Nodes[0].Nodes[nIndex - 1].Text += "(" + nCnt.ToString() + ")";
}
//配布指示書情報
if (dR["配布指示配布日"] == DBNull.Value)
{
nDate = "";
}
else
{
nDate = DateTime.Parse(dR["配布指示配布日"].ToString()).ToShortDateString();
}
NodeName1 = int.Parse(dR["配布指示ID"].ToString()).ToString("d6") + " " + nDate + " " + dR["氏名"].ToString() + "";
treeView1.Nodes[0].Nodes.Add(NodeName1);
treeView1.Nodes[0].Nodes[nIndex].NodeFont = new Font("ＭＳ Ｐゴシック", 10, FontStyle.Regular);
treeView1.Nodes[0].Nodes[nIndex].ImageIndex = 1; //アイコンの設定
nIndex++;
nCnt = 0;
}
//配布エリア情報
if (dR["チラシ名"] != DBNull.Value)
{
if (dR["完了区分"].ToString() == "0")
{
nKanryo = "";
}
else
{
nKanryo = "完了";
}
NodeName1 = dR["チラシ名"].ToString() + " " + int.Parse(dR["町名コード"].ToString()).ToString("d4") + " " + dR["町名名称"].ToString() + " " + double.Parse(dR["配布単価"].ToString(), System.Globalization.NumberStyles.Any).ToString("##0.0") + " " + dR["予定枚数"].ToString() + " " + nKanryo;
treeView1.Nodes[0].Nodes[nIndex - 1].Nodes.Add(NodeName1);
treeView1.Nodes[0].Nodes[nIndex - 1].Nodes[nCnt].ImageIndex = 2; //アイコンの設定
//未完了は赤表示
if (nKanryo == "")
{
treeView1.Nodes[0].Nodes[nIndex - 1].Nodes[nCnt].ForeColor = Color.Red;
treeView1.Nodes[0].Nodes[nIndex - 1].ForeColor = Color.Red;
}
nCnt++;
}
wID = long.Parse(dR["配布指示ID"].ToString());
}
treeView1.Nodes[0].Nodes[nIndex - 1].Text += "(" + nCnt.ToString() + ")";
}
else
{
MessageBox.Show("該当する配布指示書がありません", MESSAGE_CAPTION, MessageBoxButtons.OK, MessageBoxIcon.Information);
}
dR.Close();
Con.Close();
if (nIndex > 0)
{
button2.Enabled = true;
button3.Enabled = true;
}
}
public static void ShowData(DataGridView tempDGV, int tempYear, int tempMonth)
{
string sqlSTRING = "";
int iX;
try
{
tempDGV.RowCount = 0;
//データリーダーを取得する
Control.DataControl Con = new Control.DataControl();
OleDbConnection cn = new OleDbConnection();
cn = Con.GetConnection();
OleDbDataReader dR;
sqlSTRING = "";
sqlSTRING += "SELECT m_tbl.ID,市区町村_1.都道府県,m_tbl.市区町村,SUM(m_tbl.枚数) AS 枚数 ";
sqlSTRING += "from ";
sqlSTRING += "(SELECT 市区町村.ID,市区町村.市区町村,配布エリア.町名ID,町名.名称,MAX(配布エリア.予定枚数) AS 枚数 ";
sqlSTRING += "FROM 配布エリア INNER JOIN ";
sqlSTRING += "配布指示 ON 配布エリア.配布指示ID = 配布指示.ID INNER JOIN ";
sqlSTRING += "町名 ON 配布エリア.町名ID = 町名.ID INNER JOIN ";
sqlSTRING += "市区町村 ON 町名.市区町村コード = 市区町村.ID ";
sqlSTRING += "WHERE (YEAR(配布指示.配布日) = ?) AND (MONTH(配布指示.配布日) = ?) ";
sqlSTRING += "GROUP BY 配布エリア.町名ID,町名.名称,市区町村.ID,市区町村.市区町村) ";
sqlSTRING += "AS m_tbl LEFT OUTER JOIN ";
sqlSTRING += "市区町村 AS 市区町村_1 ON m_tbl.ID = 市区町村_1.ID ";
sqlSTRING += "GROUP BY m_tbl.ID,m_tbl.市区町村,市区町村_1.都道府県 ";
sqlSTRING += "ORDER BY m_tbl.ID";
//配布指示データのデータリーダーを取得する
OleDbCommand SCom = new OleDbCommand();
SCom.CommandText = sqlSTRING;
SCom.Parameters.AddWithValue("@sYear", tempYear);
SCom.Parameters.AddWithValue("@sMonth", tempMonth);
SCom.Connection = cn;
dR = SCom.ExecuteReader();
//グリッドビューに表示する
iX = 0;
while (dR.Read())
{
try
{
tempDGV.Rows.Add();
tempDGV[0, iX].Value = int.Parse(dR["ID"].ToString());
tempDGV[1, iX].Value = dR["都道府県"].ToString() + " " + dR["市区町村"].ToString() + "";
tempDGV[2, iX].Value = int.Parse(dR["枚数"].ToString());
iX++;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message.ToString());
}
}
dR.Close();
cn.Close();
Con.Close();
//if (tempDGV.Rows.Count > 29)
//{
// tempDGV.Columns[1].Width = 333;
//}
//else
//{
// tempDGV.Columns[1].Width = 350;
//}
}
catch (Exception e)
{
MessageBox.Show(e.Message, "エラー", MessageBoxButtons.OK);
}
}
public static void ShowData(DataGridView tempDGV, DateTime tempDate, DateTime tempDateE, string chirashiName)
{
string sqlSTRING = "";
int iX;
try
{
tempDGV.RowCount = 0;
//データリーダーを取得する
Control.DataControl Con = new Control.DataControl();
OleDbConnection cn = new OleDbConnection();
cn = Con.GetConnection();
OleDbDataReader dR;
//sqlSTRING = "";
//sqlSTRING += "select 受注.ID, 受注.チラシ名, sum(配布エリア.予定枚数) as 配布件数,";
//sqlSTRING += "受注.枚数 as 納品数 ";
//sqlSTRING += "from 配布エリア inner join 受注 ";
//sqlSTRING += "on 配布エリア.受注ID = 受注.ID inner join 配布指示 ";
//sqlSTRING += "on 配布エリア.配布指示ID = 配布指示.ID ";
//sqlSTRING += "where (配布指示.配布日 = ?) and (配布エリア.完了区分 = 1) ";
//sqlSTRING += "group by 受注.ID, 受注.チラシ名, 受注.枚数";
sqlSTRING = "";
sqlSTRING += "select 受注.ID,受注.チラシ名,SUM(配布エリア.予定枚数) AS 配布件数, ";
sqlSTRING += "受注.枚数 AS 納品数, x.前回配布件数 ";
sqlSTRING += "from 配布エリア inner join 受注 ";
sqlSTRING += "on 配布エリア.受注ID = 受注.ID inner join 配布指示 ";
sqlSTRING += "on 配布エリア.配布指示ID = 配布指示.ID left join ";
sqlSTRING += "(";
sqlSTRING += "select 配布エリア.受注ID, sum(配布エリア.予定枚数) AS 前回配布件数 ";
sqlSTRING += "from 配布エリア inner join 配布指示 ";
sqlSTRING += "on 配布エリア.配布指示ID = 配布指示.ID ";
sqlSTRING += "where ";
sqlSTRING += "(配布エリア.完了区分 = 1) and ";
sqlSTRING += "(配布指示.配布日 < ?) ";
sqlSTRING += "group by 配布エリア.受注ID";
sqlSTRING += ") AS x ";
sqlSTRING += "on 受注.ID = x.受注ID ";
sqlSTRING += "where ";
sqlSTRING += "(配布指示.配布日 >= ?) and ";
sqlSTRING += "(配布指示.配布日 <= ?) and ";
sqlSTRING += "(配布エリア.完了区分 = 1) and ";
sqlSTRING += "(受注.チラシ名 like ?) ";
sqlSTRING += "group by 受注.ID, 受注.チラシ名, 受注.枚数, x.前回配布件数";
//配布指示データのデータリーダーを取得する
OleDbCommand SCom = new OleDbCommand();
SCom.CommandText = sqlSTRING;
SCom.Parameters.AddWithValue("@sDate", tempDate);
SCom.Parameters.AddWithValue("@sDate2", tempDate);
SCom.Parameters.AddWithValue("@DateE", tempDateE);
SCom.Parameters.AddWithValue("@cName", "" + "%" + "" + chirashiName + "" + "%" + "");
SCom.Connection = cn;
dR = SCom.ExecuteReader();
//グリッドビューに表示する
iX = 0;
while (dR.Read())
{
try
{
tempDGV.Rows.Add();
tempDGV[0, iX].Value = long.Parse(dR["ID"].ToString());
tempDGV[1, iX].Value = dR["チラシ名"].ToString();
tempDGV[2, iX].Value = int.Parse(dR["納品数"].ToString());
//tempDGV[3, iX].Value = 0;
//tempDGV[4, iX].Value = int.Parse(dR["納品数"].ToString());
tempDGV[5, iX].Value = int.Parse(dR["配布件数"].ToString());
//tempDGV[6, iX].Value = int.Parse(dR["納品数"].ToString()) - 0 - int.Parse(dR["配布件数"].ToString());
if (dR["前回配布件数"] == DBNull.Value)
{
tempDGV[3, iX].Value = 0;
tempDGV[4, iX].Value = int.Parse(dR["納品数"].ToString());
tempDGV[6, iX].Value = int.Parse(dR["納品数"].ToString()) - 0 - int.Parse(dR["配布件数"].ToString());
}
else
{
tempDGV[3, iX].Value = int.Parse(dR["前回配布件数"].ToString());
tempDGV[4, iX].Value = int.Parse(dR["納品数"].ToString()) - int.Parse(dR["前回配布件数"].ToString());
tempDGV[6, iX].Value = int.Parse(dR["納品数"].ToString()) - int.Parse(dR["前回配布件数"].ToString()) - int.Parse(dR["配布件数"].ToString());
}
////前回までの配布枚数
//string mySql = "";
//OleDbDataReader dR2;
//mySql += "select sum(配布エリア.予定枚数) as 前回配布件数 ";
//mySql += "from 配布エリア inner join 配布指示 ";
//mySql += "on 配布エリア.配布指示ID = 配布指示.ID ";
//mySql += "where ";
//mySql += "(配布エリア.受注ID = " + dR["ID"].ToString() + ") and ";
//mySql += "(配布エリア.完了区分 = 1) and ";
//mySql += "(配布指示.配布日 < '" + tempDate.ToShortDateString() + "') ";
//mySql += "group by 配布エリア.受注ID";
//Control.FreeSql fCon = new Control.FreeSql();
//dR2 = fCon.free_dsReader(mySql);
//while (dR2.Read())
//{
// tempDGV[3, iX].Value = int.Parse(dR2["前回配布件数"].ToString());
// tempDGV[4, iX].Value = int.Parse(dR["納品数"].ToString()) - int.Parse(dR2["前回配布件数"].ToString());
// tempDGV[6, iX].Value = int.Parse(dR["納品数"].ToString()) - int.Parse(dR2["前回配布件数"].ToString()) - int.Parse(dR["配布件数"].ToString());
//}
//dR2.Close();
//fCon.Close();
iX++;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message.ToString());
}
}
//該当なしのとき
if (tempDGV.RowCount == 0)
{
MessageBox.Show("該当期間に配布実績はありませんでした", "該当なし");
}
dR.Close();
cn.Close();
Con.Close();
//tempDGV.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.Fill;
//if (tempDGV.RowCount <= 27)
//{
// tempDGV.Columns[1].Width = 217;
//}
//else
//{
// tempDGV.Columns[1].Width = 200;
//}
}
catch (Exception e)
{
MessageBox.Show(e.Message, "エラー", MessageBoxButtons.OK);
}
}
private void showData(DataGridView tempDGV)
{
string sqlSTRING = "";
try
{
//データリーダーを取得する
OleDbDataReader dR;
sqlSTRING += "SELECT 配布指示.ID AS 配布指示ID, 配布指示.配布日 AS 配布指示配布日,";
sqlSTRING += "配布指示.配布員ID,配布員.氏名, 配布エリア.受注ID,";
sqlSTRING += "受注.チラシ名, 配布エリア.町名ID AS 町名コード, 町名.名称 AS 町名名称,";
sqlSTRING += "配布エリア.配布単価, 配布エリア.予定枚数,";
sqlSTRING += "配布エリア.実配布枚数, 配布指示.交通費 ";
sqlSTRING += "FROM 配布指示 inner JOIN 配布エリア ";
sqlSTRING += "ON 配布指示.ID = 配布エリア.配布指示ID inner JOIN 受注 ";
sqlSTRING += "ON 配布エリア.受注ID = 受注.ID LEFT OUTER JOIN 配布員 ";
sqlSTRING += "ON 配布指示.配布員ID = 配布員.ID LEFT OUTER JOIN 町名 ";
sqlSTRING += "ON 配布エリア.町名ID = 町名.ID ";
sqlSTRING += "where (配布指示.ID >= ? and 配布指示.ID <= ?) and ";
sqlSTRING += "(配布指示.配布日 >= ? and 配布指示.配布日 <= ?) ";
// 入力日設定 2016/09/19
if (sInputDt.Checked)
{
sqlSTRING += "and 配布指示.入力日 = ? ";
}
//// 町名設定
//if (txtsChome.Text.Trim() != string.Empty)
//{
// sqlSTRING += "and 町名.名称 like '*?*' ";
//}
sqlSTRING += "ORDER BY 配布指示ID DESC, 配布エリア.受注ID ";
Control.DataControl Con = new Control.DataControl();
OleDbConnection Cn = new OleDbConnection();
Cn = Con.GetConnection();
OleDbCommand sCom = new OleDbCommand();
sCom.CommandText = sqlSTRING;
// 指示�ｔﾍ囲設定 2016/09/19
sCom.Parameters.AddWithValue("@sNoS", Utility.strToInt(txtsShijiS.Text));
if (Utility.strToInt(txtsShijiE.Text) == 0)
{
sCom.Parameters.AddWithValue("@sNoE", 2000000000);
}
else
{
sCom.Parameters.AddWithValue("@sNoE", Utility.strToInt(txtsShijiE.Text));
}
// 配布開始日設定 2016/09/19
if (sHaifuDtS.Checked)
{
sCom.Parameters.AddWithValue("@sHaifuDtS", sHaifuDtS.Value.ToShortDateString());
}
else
{
sCom.Parameters.AddWithValue("@sHaifuDtS", "1900/01/01");
}
// 配布終了日設定 2016/09/19
if (sHaifuDtE.Checked)
{
sCom.Parameters.AddWithValue("@sHaifuDtE", sHaifuDtE.Value.ToShortDateString());
}
else
{
sCom.Parameters.AddWithValue("@sHaifuDtE", "2900/01/01");
}
// 入力日設定 2016/09/19
if (sInputDt.Checked)
{
sCom.Parameters.AddWithValue("@sInputDt", sInputDt.Value.ToShortDateString());
}
sCom.Connection = Cn;
dR = sCom.ExecuteReader();
//グリッドビューに表示する
int iX = 0;
tempDGV.RowCount = 0;
while (dR.Read())
{
// 丁目検索設定のとき 2016/09/19
if (txtsChome.Text.Trim() != string.Empty)
{
if (!dR["町名名称"].ToString().Contains(txtsChome.Text.Trim()))
{
continue;
}
}
// グリッドビュー表示
tempDGV.Rows.Add();
tempDGV[0, iX].Value = int.Parse(dR["配布指示ID"].ToString());
tempDGV[1, iX].Value = DateTime.Parse(dR["配布指示配布日"].ToString());
tempDGV[2, iX].Value = dR["配布員ID"].ToString();
tempDGV[3, iX].Value = dR["氏名"].ToString();
tempDGV[4, iX].Value = long.Parse(dR["受注ID"].ToString(), System.Globalization.NumberStyles.Any);
tempDGV[5, iX].Value = dR["チラシ名"].ToString();
tempDGV[6, iX].Value = int.Parse(dR["町名コード"].ToString(), System.Globalization.NumberStyles.Any);
tempDGV[7, iX].Value = dR["町名名称"].ToString();
tempDGV[8, iX].Value = double.Parse(dR["配布単価"].ToString(), System.Globalization.NumberStyles.Any).ToString("#,##0.00");
tempDGV[9, iX].Value = int.Parse(dR["予定枚数"].ToString(), System.Globalization.NumberStyles.Any);
tempDGV[10, iX].Value = int.Parse(dR["実配布枚数"].ToString(), System.Globalization.NumberStyles.Any);
tempDGV[11, iX].Value = int.Parse(dR["交通費"].ToString(), System.Globalization.NumberStyles.Any);
iX++;
}
dR.Close();
Con.Close();
//if (tempDGV.RowCount > 25)
//{
// tempDGV.Columns[1].Width = 198;
//}
//else
//{
// tempDGV.Columns[1].Width = 215;
//}
tempDGV.CurrentCell = null;
// 表示件数表示 2016/09/19
if (tempDGV.Rows.Count > 0)
{
this.Text = MESSAGE_CAPTION + " 【" + tempDGV.Rows.Count.ToString("#,##0") + "件】";
}
else
{
this.Text = MESSAGE_CAPTION;
}
}
catch (Exception e)
{
MessageBox.Show(e.Message, "エラー", MessageBoxButtons.OK);
}
}
public static void ShowData(DataGridView tempDGV, int tempYear, int tempMonth, int tempDays)
{
string sqlSTRING = "";
int iX;
try
{
//カーソル表示を待機状態
Cursor.Current = Cursors.WaitCursor;
tempDGV.RowCount = 0;
//データリーダーを取得する
Control.DataControl Con = new Control.DataControl();
OleDbConnection cn = new OleDbConnection();
cn = Con.GetConnection();
OleDbDataReader dR;
sqlSTRING = "";
sqlSTRING += "select derivedtbl_1.ID, 配布員.氏名,事業所.名称, count(derivedtbl_1.ID) as 稼動数 ";
sqlSTRING += "from ";
sqlSTRING += "(select distinct 配布員ID as ID, 配布日 ";
sqlSTRING += "from 配布指示 ";
sqlSTRING += "where (year(配布日) = ?) AND (month(配布日) = ?) ";
sqlSTRING += "group by 配布員ID, 配布日) as derivedtbl_1 ";
sqlSTRING += "left join 配布員 ";
sqlSTRING += "on derivedtbl_1.ID = 配布員.ID ";
sqlSTRING += "left join 事業所 ";
sqlSTRING += "on 配布員.事業所コード = 事業所.ID ";
sqlSTRING += "group by derivedtbl_1.ID, 配布員.氏名,事業所.名称 ";
sqlSTRING += "having (count(derivedtbl_1.ID) >= ?) ";
sqlSTRING += "order by 稼動数 desc, derivedtbl_1.ID";
//配布指示データのデータリーダーを取得する
OleDbCommand SCom = new OleDbCommand();
SCom.CommandText = sqlSTRING;
SCom.Parameters.AddWithValue("@year", tempYear);
SCom.Parameters.AddWithValue("@month", tempMonth);
SCom.Parameters.AddWithValue("@days", tempDays);
SCom.Connection = cn;
dR = SCom.ExecuteReader();
//グリッドビューに表示する
iX = 0;
while (dR.Read())
{
try
{
tempDGV.Rows.Add();
tempDGV[0, iX].Value = int.Parse(dR["ID"].ToString());
tempDGV[1, iX].Value = dR["氏名"].ToString();
tempDGV[2, iX].Value = dR["名称"].ToString();
tempDGV[3, iX].Value = int.Parse(dR["稼動数"].ToString());
iX++;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message.ToString());
}
}
dR.Close();
cn.Close();
Con.Close();
//if (tempDGV.RowCount <= 27)
//{
// tempDGV.Columns[3].Width = 120;
//}
//else
//{
// tempDGV.Columns[3].Width = 103;
//}
//カーソル表示を戻す
Cursor.Current = Cursors.Default;
}
catch (Exception e)
{
//カーソル表示を戻す
Cursor.Current = Cursors.Default;
MessageBox.Show(e.Message, "エラー", MessageBoxButtons.OK);
}
}
public static void ShowData(DataGridView tempDGV, DateTime tempDate, int tempOfficeID, ComboBox tempCmb)
{
try
{
int iX;
int colno;
int Nin;
string htxt;
string sqlSTRING = "";
DateTime sDate;
DateTime eDate;
const int DKIKAN = 14;
DateTime [] mDate = new DateTime [DKIKAN];
int[] mTotal = new int[DKIKAN];
eDate = tempDate.AddDays(DKIKAN - 1);
//カーソル待機
Cursor.Current = Cursors.WaitCursor;
//期間列削除
if (tempDGV.Columns.Count > 10)
{
for (int i = 0; i < DKIKAN; i++)
{
tempDGV.Columns.RemoveAt(10);
}
}
//期間列追加
for (int i = 0; i < DKIKAN; i++)
{
colno = i + 10;
sDate = tempDate.AddDays(i);
htxt = sDate.Day.ToString() + Environment.NewLine + ("日月火水木金土").Substring(int.Parse(sDate.DayOfWeek.ToString("d")), 1);
tempDGV.Columns.Add("col" + colno.ToString(), htxt);
tempDGV.Columns[colno].Width = 40;
tempDGV.Columns[colno].DefaultCellStyle.Alignment = DataGridViewContentAlignment.BottomRight;
mDate[i] = sDate;
//土日なら背景色表示
if (sDate.DayOfWeek.ToString("d") == "0" || sDate.DayOfWeek.ToString("d") == "6")
{
tempDGV.Columns[colno].DefaultCellStyle.BackColor = Color.LightPink;
}
}
tempDGV.RowCount = 0;
//データリーダーを取得する
Control.DataControl Con = new Control.DataControl();
OleDbConnection cn = new OleDbConnection();
cn = Con.GetConnection();
OleDbDataReader dR;
sqlSTRING = "";
sqlSTRING += "select 受注.ID,受注.チラシ名,社員.氏名,受注.配布開始日,受注.配布終了日,";
sqlSTRING += "配布形態.名称,配布形態.一人当たり枚数,受注.枚数,sum_tbl.配布枚数,事業所.名称 as 事業所名 ";
sqlSTRING += "from 受注 LEFT OUTER JOIN ";
sqlSTRING += "得意先 ON 受注.得意先ID = 得意先.ID LEFT OUTER JOIN ";
sqlSTRING += "社員 ON 得意先.担当社員コード = 社員.ID LEFT OUTER JOIN ";
sqlSTRING += "配布形態 ON 受注.配布形態 = 配布形態.ID LEFT OUTER JOIN ";
sqlSTRING += "(select 配布エリア.受注ID, SUM(配布エリア.予定枚数) AS 配布枚数 ";
sqlSTRING += "from 配布エリア INNER JOIN ";
sqlSTRING += "配布指示 ON 配布エリア.配布指示ID = 配布指示.ID ";
sqlSTRING += "where (配布指示.配布日 < ?) ";
sqlSTRING += "group by 配布エリア.受注ID) AS sum_tbl ON 受注.ID = sum_tbl.受注ID ";
sqlSTRING += "left join ";
sqlSTRING += "事業所 on 受注.事業所ID = 事業所.ID ";
sqlSTRING += "where ";
//事業所指定
if (tempCmb.SelectedIndex != -1)
{
sqlSTRING += "(受注.事業所ID = ?) and ";
}
sqlSTRING += "(((受注.配布開始日 >= ?) and (受注.配布開始日 <= ?)) or ";
sqlSTRING += "((受注.配布終了日 >= ?) and (受注.配布終了日 <= ?)) or ";
sqlSTRING += "((受注.配布開始日 <= ?) and (受注.配布終了日 >= ?))) ";
sqlSTRING += "order by 受注.ID";
//配布指示データのデータリーダーを取得する
OleDbCommand SCom = new OleDbCommand();
SCom.CommandText = sqlSTRING;
SCom.Parameters.AddWithValue("@sDate", tempDate);
//事業所指定
if (tempCmb.SelectedIndex != -1)
{
SCom.Parameters.AddWithValue("@office", tempOfficeID);
}
SCom.Parameters.AddWithValue("@d1", tempDate);
SCom.Parameters.AddWithValue("@d2", eDate);
SCom.Parameters.AddWithValue("@d3", tempDate);
SCom.Parameters.AddWithValue("@d4", eDate);
SCom.Parameters.AddWithValue("@d5", tempDate);
SCom.Parameters.AddWithValue("@d6", eDate);
SCom.Connection = cn;
dR = SCom.ExecuteReader();
//グリッドビューに表示する
iX = 0;
while (dR.Read())
{
try
{
tempDGV.Rows.Add();
tempDGV[0, iX].Value = long.Parse(dR["ID"].ToString());
tempDGV[1, iX].Value = dR["チラシ名"].ToString();
tempDGV[2, iX].Value = dR["事業所名"].ToString() + "";
tempDGV[3, iX].Value = dR["氏名"].ToString();
tempDGV[4, iX].Value = DateTime.Parse(dR["配布開始日"].ToString()).ToShortDateString();
tempDGV[5, iX].Value = DateTime.Parse(dR["配布終了日"].ToString()).ToShortDateString();
tempDGV[6, iX].Value = dR["名称"].ToString() + "";
if (dR["配布枚数"] == DBNull.Value)
{
tempDGV[7, iX].Value = int.Parse(dR["枚数"].ToString());
}
else
{
tempDGV[7, iX].Value = int.Parse(dR["枚数"].ToString()) - int.Parse(dR["配布枚数"].ToString());
}
if (dR["一人当たり枚数"] == DBNull.Value)
{
Nin = (int)0;
}
else
{
Nin = int.Parse(dR["一人当たり枚数"].ToString(), System.Globalization.NumberStyles.Any);
}
if (Nin == 0)
{
tempDGV[8, iX].Value = (double)(0);
}
else
{
tempDGV[8, iX].Value = System.Math.Floor(double.Parse(tempDGV[7, iX].Value.ToString(), System.Globalization.NumberStyles.Any) / Nin + 0.9);
}
TimeSpan tSpan;
tSpan = DateTime.Parse(dR["配布終了日"].ToString()) - DateTime.Parse(dR["配布開始日"].ToString());
tempDGV[9, iX].Value = tSpan.Days + 1;
//スケジュール欄に人数表示
//配布開始日が起点日以前のときは起点日に表示
if (DateTime.Parse(dR["配布開始日"].ToString()) < mDate[0])
{
tempDGV[10, iX].Value = tempDGV[8, iX].Value.ToString();
mTotal[0] += int.Parse(tempDGV[8, iX].Value.ToString(), System.Globalization.NumberStyles.Any);
}
else //配布開始日に表示
{
for (int i = 0; i < mDate.Length; i++)
{
if (DateTime.Parse(dR["配布開始日"].ToString()) == mDate[i])
{
tempDGV[i + 10, iX].Value = tempDGV[8, iX].Value.ToString();
mTotal[i] += int.Parse(tempDGV[8, iX].Value.ToString(), System.Globalization.NumberStyles.Any);
}
}
}
iX++;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message.ToString());
}
}
dR.Close();
cn.Close();
Con.Close();
//if (tempDGV.RowCount <= 27)
//{
// tempDGV.Columns[1].Width = 217;
//}
//else
//{
// tempDGV.Columns[1].Width = 200;
//}
//スケジュールヘッダに合計人数表示
for (int i = 0; i < mTotal.Length; i++)
{
tempDGV.Columns[i + 10].HeaderText += Environment.NewLine + mTotal[i].ToString();
}
//カーソル表示戻す
Cursor.Current = Cursors.Default;
if (iX == 0)
{
MessageBox.Show("該当するデータがありません", MESSAGE_CAPTION);
}
}
catch (Exception e)
{
MessageBox.Show(e.Message, "エラー", MessageBoxButtons.OK);
//カーソル表示戻す
Cursor.Current = Cursors.Default;
}
}
public static void ShowData(DataGridView tempDGV, DateTime tempDate1, DateTime tempDate2, int tempID, Label t1, Label t2)
{
string sqlSTRING = "";
int nTotal = 0;
int Total = 0;
try
{
Control.DataControl sdcon = new Control.DataControl();
OleDbConnection cn = new OleDbConnection();
cn = sdcon.GetConnection();
//データリーダーを取得する
OleDbDataReader dR;
sqlSTRING += "select 入金.*,請求書.税率,得意先.略称 ";
sqlSTRING += "from 入金 inner join 請求書 ";
sqlSTRING += "on 入金.請求書ID = 請求書.ID left join 得意先 ";
sqlSTRING += "on 請求書.得意先ID = 得意先.ID ";
sqlSTRING += "where ";
sqlSTRING += "(入金.入金年月日 >= ?) and ";
sqlSTRING += "(入金.入金年月日 <= ?) and ";
sqlSTRING += "(得意先.担当社員コード = ?) ";
OleDbCommand SCom = new OleDbCommand();
SCom.CommandText = sqlSTRING;
SCom.Parameters.AddWithValue("@Date1", tempDate1);
SCom.Parameters.AddWithValue("@Date2", tempDate2);
SCom.Parameters.AddWithValue("@SID", tempID);
SCom.Connection = cn;
dR = SCom.ExecuteReader();
//グリッドビューに表示する
int iX = 0;
int RT;
double sKin;
RT = Properties.Settings.Default.販促手当率;
tempDGV.RowCount = 0;
while (dR.Read())
{
tempDGV.Rows.Add();
tempDGV[0, iX].Value = Convert.ToDateTime(dR["入金年月日"].ToString());
tempDGV[1, iX].Value = dR["略称"].ToString();
tempDGV[2, iX].Value = int.Parse(dR["金額"].ToString(), System.Globalization.NumberStyles.Any);
sKin = double.Parse(dR["金額"].ToString(), System.Globalization.NumberStyles.Any) / (1 + double.Parse(dR["税率"].ToString(), System.Globalization.NumberStyles.Any) / 100);
sKin = Math.Floor(sKin * RT / 100 + 0.5);
tempDGV[3, iX].Value = int.Parse(sKin.ToString(), System.Globalization.NumberStyles.Any);
//入金額合計
nTotal += int.Parse(dR["金額"].ToString(), System.Globalization.NumberStyles.Any);
//手当合計
Total += int.Parse(sKin.ToString(), System.Globalization.NumberStyles.Any);
iX++;
}
if (tempDGV.RowCount == 0)
{
MessageBox.Show("該当する入金データがありませんでした", MESSAGE_CAPTION);
}
//if (tempDGV.RowCount <= 25)
//{
// tempDGV.Columns[1].Width = 300;
//}
//else
//{
// tempDGV.Columns[1].Width = 293;
//}
dR.Close();
sdcon.Close();
cn.Close();
//合計表示
t1.Text = nTotal.ToString("#,##0");
t2.Text = Total.ToString("#,##0");
tempDGV.CurrentCell = null;
}
catch (Exception e)
{
MessageBox.Show(e.Message, "エラー", MessageBoxButtons.OK);
}
}
